CURSOR C1_CURSOR(vACCOUNT varchar2) IS
SELECT NVL(R.CHGOFF_DATE, SYSDATE - 7500) AS COD,
NVL(R.LAST_PAY_DATE, SYSDATE - 7500) AS LPD, NVL(R.CONTRACT_DATE, SYSDATE - 7500)
as CD, NVL(R.DTE_LST_CR_CHG,SYSDATE - 7500) as LCC FROM
RACCOUNT R WHERE ACCOUNT = vACCOUNT and DIVISION_ID='PORT';
C1_RECORD C1_CURSOR%ROWTYPE;
BEGIN
OPEN C1_CURSOR(vACCOUNT);
FETCH C1_CURSOR INTO C1_RECORD;
CASE STATEABBV
--Texas has its own Rules

When 'TX' THEN
IF (C1_RECORD.COD + SOLDAYS) > D_DAY then
RETURN 1;
ELSE
RETURN 0;
END IF;

--Every other State but TX
ELSE
-- Find the best date
IF C1_RECORD.LCC > C1_RECORD.LPD then
USEFUL_DATE := (C1_RECORD.LCC + SOLDAYS);
END IF;
IF C1_RECORD.LPD > (SYSDATE - 7400) then
USEFUL_DATE := (C1_RECORD.LPD + SOLDAYS);
END IF;
IF C1_RECORD.CD > (SYSDATE - 7400) AND USEFUL_DATE IS NULL then
USEFUL_DATE := (C1_RECORD.CD + SOLDAYS);
END IF;
IF C1_RECORD.COD > (SYSDATE - 7400) AND USEFUL_DATE IS NULL then
USEFUL_DATE := (C1_RECORD.COD + SOLDAYS);
End IF;
IF USEFUL_DATE > D_DAY THEN
RETURN 1;
ELSE
RETURN 0;
END IF;
END CASE;

EXCEPTION
WHEN OTHERS THEN
RETURN 0;
END;
/

LOOP

FETCH RULES_R INTO RULES_R_record;
EXIT WHEN RULES_R%NOTFOUND;

d_date2 := SYSDATE + RULES_R_record.SET_POINT;

IF TRIM(RULES_R_record.DATES) = 'Y' THEN

--SECTION TO DEAL WITH THE RULES that contain DATE CRITERIA
IF RULES_R_record.ITEM_NAME = 'DATE_ENTERED' THEN
d_date1 := d_dateentered;
ELSIF RULES_R_record.ITEM_NAME = 'DATE_SCHEDULED' THEN
d_date1 := d_scheduled;
ELSIF RULES_R_record.ITEM_NAME = 'FRST_PMT_DATE' THEN
d_date1 := d_frstpay;
ELSIF RULES_R_record.ITEM_NAME = 'USER_DATE_2' THEN
d_date1 := d_userdate2;
ELSIF RULES_R_record.ITEM_NAME = 'USER_DATE_1' THEN
d_date1 := d_userdate1;
ELSIF RULES_R_record.ITEM_NAME = 'LAST_PAY_DATE' THEN
d_date1 := d_lastpay;
ELSIF RULES_R_record.ITEM_NAME = 'LAST_CONT_DATE' THEN
d_date1 := d_lastcont;
END IF;

IF TRIM(RULES_R_record.CONDITION) = 'LE' THEN
If d_date1 <= d_date2 Then
n_score := n_score + TO_NUMBER(RULES_R_record.MODIFIER,'9999');
END IF;
END IF;

IF TRIM(RULES_R_record.CONDITION) = 'GE' THEN
If d_date1 >= d_date2 Then
n_score := n_score + TO_NUMBER(RULES_R_record.MODIFIER,'9999');
END IF;
END IF;

IF TRIM(RULES_R_record.CONDITION) = 'LT' THEN
If d_date1 < d_date2 Then
n_score := n_score + TO_NUMBER(RULES_R_record.MODIFIER,'9999');
END IF;
END IF;

IF TRIM(RULES_R_record.CONDITION) = 'EQ' THEN
If d_date1 = d_date2 Then
n_score := n_score + TO_NUMBER(RULES_R_record.MODIFIER,'9999');
END IF;
END IF;

IF TRIM(RULES_R_record.CONDITION) = 'GT' THEN
If d_date1 > d_date2 Then
n_score := n_score + TO_NUMBER(RULES_R_record.MODIFIER,'9999');
END IF;
END IF;
ELSE

--SECTION TO DEAL WITH THE RULES that contain NUMERIC CRITERIA
n_val2 := RULES_R_record.SET_POINT;
IF RULES_R_record.ITEM_NAME = 'TOT_DUE_AMT' THEN
n_val1 := n_totamtdue;
ELSIF RULES_R_record.ITEM_NAME = 'USER_NUMBER_2' THEN
n_val1 := n_usernumber2;
ELSIF RULES_R_record.ITEM_NAME = 'USER_NUMBER_4' THEN
n_val1 := n_usernumber4;
END IF;

IF TRIM(RULES_R_record.CONDITION) = 'LE' THEN
If n_val1 <= n_val2 Then
n_score := n_score + TO_NUMBER(RULES_R_record.MODIFIER,'9999');
END IF;
END IF;

IF TRIM(RULES_R_record.CONDITION) = 'GE' THEN
If n_val1 >= n_val2 Then
n_score := n_score + TO_NUMBER(RULES_R_record.MODIFIER,'9999');
END IF;
END IF;

IF TRIM(RULES_R_record.CONDITION) = 'LT' THEN
If n_val1 < n_val2 Then
n_score := n_score + TO_NUMBER(RULES_R_record.MODIFIER,'9999');
END IF;
END IF;

IF TRIM(RULES_R_record.CONDITION) = 'EQ' THEN
If n_val1 = n_val2 Then
n_score := n_score + TO_NUMBER(RULES_R_record.MODIFIER,'9999');
END IF;
END IF;

IF TRIM(RULES_R_record.CONDITION) = 'GT' THEN
If n_val1 > n_val2 Then
n_score := n_score + TO_NUMBER(RULES_R_record.MODIFIER,'9999');
END IF;
END IF;
END IF;

END LOOP;

CLOSE RULES_R;

IF n_score <> n_retro_score THEN

UPDATE RSCHDULE SET PRIORITY= n_score
WHERE ACCOUNT = RECORDS_R_record.ACCOUNT
AND QUEUE_ID = t_queue AND DIVISION_ID='PORT';
--Commit every 50 transactions

IF (n_commitcounter > 50) THEN
n_commitcounter := 0;
COMMIT;
END IF;
n_commitcounter := n_commitcounter + 1;
END IF;
n_score := 0;
END LOOP;

COMMIT;
CLOSE RECORDS_R;
CLOSE RULES_R;

INSERTLOGENTRY ('PRIORITIZATION','STOP' ,'' , '','','sp_PRIORITIZATIONNEW',
'',BAT_ID,'Stored Procedure ran successfully on score table: ' || SCORETABLE );
SUCC := 1;

--Error handing

EXCEPTION
WHEN VALUE_ERROR THEN
INSERTLOGENTRY ('PRIORITIZATION','STOP' ,'' , '','','sp_PRIORITIZATIONNEW'
,'',BAT_ID,'Stored Procedure ran successfully 0 records affected' );
SUCC := 3;
WHEN INVALID_CURSOR THEN
INSERTLOGENTRY ('PRIORITIZATION','STOP' ,'' , '','','sp_PRIORITIZATIONNEW'
,'',BAT_ID,'Stored Procedure ran successfully on score table: ' || SCORETABLE );
SUCC := 1;
WHEN OTHERS THEN
INSERTLOGENTRY ('PRIORITIZATION','' ,'' , '','','sp_PRIORITIZATIONNEW'
,'',BAT_ID,'Error Occured:' ||SQLERRM);
SUCC := 0;
END;

--Error handing

EXCEPTION
WHEN VALUE_ERROR THEN
INSERTLOGENTRY ('PRIORITIZATION','' ,'' , '','','sp_PRIORITIZATIONNEW'
,'',BAT_ID,'Stored Procedure ran successfully affected 0 records');
WHEN OTHERS THEN
INSERTLOGENTRY ('PRIORITIZATION','' ,'' , '','','sp_PRIORITIZATIONNEW'
,'',BAT_ID,'Error Occured: ' ||SQLERRM);
END;
/

Insert / Update Oracle Stored Procedure (PL/SQL)

PROCEDURE sp_20040552MORTGAGENOTE ( BAT_ID IN VARCHAR2, SUCC OUT INTEGER) IS
--KJP
--New Filter Update the 3 phone numbers in RACCTREL
--BAT_ID Input parameter will be populated by the .Net application so it can keep track of the batches
--SUCC keeps track of success or failure which will be monitored by the .Net app. also
BEGIN
DECLARE
vCOMMITCounter INTEGER := 0;
vCounter integer := 0;
vRespUNIT varchar2(20);

CURSOR GOODPHONELIST IS
Select ACCOUNT, BALANCE,PAYMENT FROM MORTGAGENOTE;
GOODPHONERECORD GOODPHONELIST%ROWTYPE;
BEGIN

OPEN GOODPHONELIST;
LOOP
FETCH GOODPHONELIST into GOODPHONERecord;
EXIT WHEN GOODPHONELIST%NOTFOUND;

INSERTRNOTETRN (GOODPHONERecord.ACCOUNT, TO_CHAR(SYSDATE,'DD-MON-YYYY'),TO_CHAR(SYSDATE,'sssss'), 1, 'LTR' , '01/11/2005 CBR PULLED FROM TRANSUNION...N1 HAS ACTIVE MORTGAGE: BALANCE:' || GOODPHONERecord.BALANCE || ' MONTHLY PAYMENT:' || GOODPHONERecord.PAYMENT ,GOODPHONERecord.ACCOUNT, 'N', TO_CHAR(SYSDATE,'DD-MON-YYYY'),TO_CHAR(SYSDATE,'sssss'), 0);
INSERTLOGENTRY (GOODPHONERecord.ACCOUNT,'','','','','sp_MLSPHUP ','',BAT_ID,'Note Added');
Update MORTGAGENOTE set F5 = '1' where ACCOUNT = GOODPHONERecord.ACCOUNT;

vCounter := vCounter + 1;
vCOMMITCounter := vCOMMITCounter + 1;

IF vCOMMITCounter > 40 THEN
COMMIT;
vCOMMITCounter := 0;
END IF;

END LOOP;
--Final commit...
COMMIT;
CLOSE GOODPHONELIST;
SUCC := 1;
INSERTLOGENTRY ('000000000000','','','','','sp_MLSPHUP','',BAT_ID,'Stored procedure ran successfully ' || vCounter || ' records changed');
--Didn't find any records to change...Log it
EXCEPTION
WHEN VALUE_ERROR THEN
INSERTLOGENTRY ('000000000000','','','','','sp_MLSPHUP','',BAT_ID,'Stored procedure ran successfully 0 records changed');
SUCC := 3;
END;
EXCEPTION
WHEN OTHERS THEN
--SP was Unsuccessful (0) this should only happen in a catastrophic event
INSERTLOGENTRY ('000000000000','' ,'' , '','','sp_MLSPHUP','',BAT_ID,'Error Occured: ' ||SQLERRM);
SUCC:= 0;

END;

Insert / Update Oracle Stored Procedure (PL/SQL)

PROCEDURE sp_TUNAPHONES ( BAT_ID IN VARCHAR2, SUCC OUT INTEGER) IS
--KJP
--BAT_ID Input parameter will be populated by the .Net application so it can keep track of the batches
--SUCC keeps track of success or failure which will be monitored by the .Net app. also
BEGIN
DECLARE
vRef varchar2(20) := 'sp_TUNAPHONES';
vSCHEDEDALREADY integer :=0;
vCOMMITCounter integer := 0;
vCounter integer := 0;
vRespUNIT varchar2(20);

CURSOR GOODPHONELIST IS
Select ACCT, AREACODE || '-' || SUBSTR(PHONE,0,3) || '-' || SUBSTR(PHONE,4,4) as formatted_phone FROM TEMP_TUNA;
GOODPHONERECORD GOODPHONELIST%ROWTYPE;
BEGIN

--PPTPLISTRecord PPTPLIST%ROWTYPE;

OPEN GOODPHONELIST;
LOOP
FETCH GOODPHONELIST into GOODPHONERecord;
EXIT WHEN GOODPHONELIST%NOTFOUND;

INSERT INTO rrelhist SELECT * FROM racctrel where account = GOODPHONERecord.ACCT and REL_POS='1';

UPDATE RACCTREL SET
PHONE_HOME = GOODPHONERecord.formatted_phone,
USER_TEXT_6 = 'CBR010505',
UPDATE_DATE = TRUNC(SYSDATE),
UPDATE_TIME = TO_CHAR(SYSDATE,'sssss')
WHERE ACCOUNT = GOODPHONERecord.ACCT and REL_POS='1';

Update raccount set USER_DATE_5 = TRUNC(SYSDATE) WHERE ACCOUNT = GOODPHONERecord.ACCT;

vCounter := vCounter + 1;
vCOMMITCounter := vCOMMITCounter + 1;

IF vCOMMITCounter > 40 THEN
INSERTLOGENTRY (GOODPHONERecord.ACCT,'','','','','sp_TUNAMASS ','',BAT_ID,'H:' ||GOODPHONERecord.FORMATTED_PHONE );
COMMIT;
vCOMMITCounter := 0;
END IF;


-- INSERTRRRESPST (PPTPLISTRecord.ACCOUNT,'PORT','Filter: SPCL','DEAD',TO_CHAR(SYSDATE + 1,'DD-MON-YYYY'));
--INSERTRSCHDULE ('PORT', PPTPLISTRecord.ACCOUNT, 'NEW', TO_CHAR(SYSDATE,'DD-MON-YYYY'),'',TO_CHAR(SYSDATE,'DD-MON-YYYY'), TO_CHAR(SYSDATE,'sssss'),'PORT', 'I', 98, 'NEW', PPTPLISTRecord.RESPONSIBILITY, 'I', 'New Account', 'N', 0,'' );
--INSERTLOGENTRY (GOODPHONERecord.ACCT,'','','','','sp_TUNAMASS ','',BAT_ID,'H:' ||GOODPHONERecord.FORMATTED_PHONE );

END LOOP;
--Final commit...
COMMIT;
CLOSE GOODPHONELIST;
SUCC := 1;
INSERTLOGENTRY ('000000000000','','','','','sp_MLSPHUP','',BAT_ID,'Stored procedure ran successfully ' || vCounter || ' records changed');
--Didn't find any records to change...Log it
EXCEPTION
WHEN VALUE_ERROR THEN
INSERTLOGENTRY ('000000000000','','','','','sp_MLSPHUP','',BAT_ID,'Stored procedure ran successfully 0 records changed');
SUCC := 3;
END;
EXCEPTION
WHEN OTHERS THEN
--SP was Unsuccessful (0) this should only happen in a catastrophic event
INSERTLOGENTRY ('000000000000','' ,'' , '','','sp_MLSPHUP','',BAT_ID,'Error Occured: ' ||SQLERRM);
SUCC:= 0;
END;

Insert / Update Oracle Stored Procedure (PL/SQL)

PROCEDURE sp_100K_RACCTREL_UPD ( BAT_ID IN VARCHAR2, SUCC OUT INTEGER) IS

--KJP
--UPDATE RACCTREL with the latest information from the Credit bureau Pulls
--BAT_ID Input parameter will be populated by the .Net application so it can keep track of the batches
--SUCC keeps track of success or failure which will be monitored by the .Net app. also
BEGIN
DECLARE
vRef varchar2(20) := 'sp_100KCB';
vSCHEDEDALREADY integer :=0;

vCOMMITCounter integer := 0;
vCounter integer := 0;
vRespUNIT varchar2(20);

CURSOR GOODPHONELIST IS
Select * from KPATRICK.MASS_CB_100K_WKD where (ROWNUM < 10001) and BRANCH_NO <> '1';
GOODPHONERECORD GOODPHONELIST%ROWTYPE;
BEGIN

--PPTPLISTRecord PPTPLIST%ROWTYPE;

OPEN GOODPHONELIST;
LOOP
FETCH GOODPHONELIST into GOODPHONERecord;
EXIT WHEN GOODPHONELIST%NOTFOUND;

INSERT INTO rrelhist SELECT * FROM racctrel where account = GOODPHONERecord.ACCOUNT and REL_POS='1';

UPDATE RACCTREL SET
PHONE_HOME = GOODPHONERecord.PHONE_HOME,
TIME_ZONE = GOODPHONERecord.TIME_ZONE,
USER_TEXT_6 = 'CBR032205',
UPDATE_DATE = TRUNC(SYSDATE),
UPDATE_TIME = TO_CHAR(SYSDATE,'sssss')
WHERE ACCOUNT = GOODPHONERecord.ACCOUNT and REL_POS='1' and GOODPHONERecord.PHONE_HOME not like 'BAD%';

UPDATE RACCTREL SET
ADDRESS1 = GOODPHONERecord.ADDRESS1,
CITY = GOODPHONERecord.CITY,
STATE = GOODPHONERecord.STATE,
ZIP_CODE= GOODPHONERecord.ZIP_CODE,
USER_TEXT_6 = 'CBR032205',
UPDATE_DATE = TRUNC(SYSDATE),
UPDATE_TIME = TO_CHAR(SYSDATE,'sssss')
WHERE ACCOUNT = GOODPHONERecord.ACCOUNT and REL_POS='1' and GOODPHONERecord.ZIP_CODE not like 'BAD%';

Update KPATRICK.MASS_CB_100K_WKD set BRANCH_NO = '1' WHERE ACCOUNT = GOODPHONERecord.ACCOUNT;

vCounter := vCounter + 1;
COMMIT;

INSERTLOGENTRY (GOODPHONERecord.ACCOUNT,'','','','','sp_150KCB','',BAT_ID,'Updated Phone number and/or address');
END LOOP;

CLOSE GOODPHONELIST;
SUCC := 1;
INSERTLOGENTRY ('000000000000','','','','','sp_150KCB','',BAT_ID,'Stored procedure ran successfully ' || vCounter || ' records changed');
--Didn't find any records to change...Log it
EXCEPTION
WHEN VALUE_ERROR THEN
INSERTLOGENTRY ('000000000000','','','','','sp_150KCB','',BAT_ID,'Stored procedure ran successfully 0 records changed');
SUCC := 3;
END;
EXCEPTION
WHEN OTHERS THEN
--SP was Unsuccessful (0) this should only happen in a catastrophic event
INSERTLOGENTRY ('000000000000','' ,'' , '','','sp_150KCB','',BAT_ID,'Error Occured: ' ||SQLERRM);
SUCC:= 0;
END;

Select Oracle Materialized View (PL/SQL)

CREATE MATERIALIZED VIEW GPMS.GPMS_ADHOC_MATR_VIEW
--Ken12/09/2005
--need to grant these permissions to GPMS
--CREATE MATERIALIZED VIEW or CREATE ANY MATERIALIZED VIEW
--CREATE TABLE or CREATE ANY TABLE
--CREATE VIEW or CREATE ANY VIEW
NOCACHE
NOLOGGING
NOPARALLEL
BUILD IMMEDIATE
REFRESH COMPLETE
START WITH TO_DATE('08-Dec-2005 10:00:00','dd-mon-yyyy hh24:mi:ss')
NEXT SYSDATE + (1/48)
AS
SELECT a.ID, a.GTAG, a.RECEIVINGDOC, a.QTYRECEIVED, a.qtyonhand, a.QTYONHAND AS qty, a.UNITOFISSUE, a.LINEITEM, a.MANSERIAL, a.PARTNUMBER,
a.MODELNUMBER, a.ITEMCOST, a.TASKNUMBER, a.DESCRIPTION, a.CATEGORY, a.EQUIPCATEGORY, a.DRAWINGNUMBER, a.COMPCERT, a.MSDS,
a.MILCOMSPEC, a.STATUS, a.REMARKS, a.LOCATION, a.RECEIVINGLPA, TO_CHAR( a.RECEIPTDATE,'MM-DD-YYYY') as RECEIPTDATE, a.CONTRACT, a.RECEIVINGDOCTYPE, a.CUSTODIAN,
a.LASTINVENTORIED, a.LOCKED, a.MANUFACTURER, a.CONDITION, a.CHECKEDOUT_TO, a.NSN, a.DRAWINGITEMNUMBER, 0 AS issuegroup,
c.facility, b.location_name
FROM GPMS.ITEMS a, GPMS.LOCATIONS b, GPMS.FACILITIES c
WHERE c.id = b.facility_id and a.location = b.id;
/

commit;