I have a very lengthy stored procedure that I just inherited. Apparently this stored proc performs poorly. I am reviewing it to see where I can add some efficiencies. I would like to know if there are any tips/tools/doc available that could assist me in UDB stored procedure performance. I see a lot out there on SQL Server, but I can't seem to find any tips for UDB. The procedure is as follows:
DROP SPECIFIC PROCEDURE GCRR.SQL0705221 21828900
;
CREATE PROCEDURE "GCRR"."CONTRIB UTION_ODS_ENTRI ES"
(IN "P_FILE_UID " CHARACTER(8),
IN "P_PLAN_ID" VARCHAR(45),
IN "P_LOCATION_COD E" VARCHAR(45),
IN "P_LAST_MODIFIE D_USER_ID" VARCHAR(25),
IN "P_LAST_MODIFIE D_TIMESTAMP" TIMESTAMP
)
SPECIFIC "GCRR"."SQL0705 22121828900"
LANGUAGE SQL
NOT DETERMINISTIC
CALLED ON NULL INPUT
EXTERNAL ACTION
OLD SAVEPOINT LEVEL
MODIFIES SQL DATA
INHERIT SPECIAL REGISTERS
BEGIN
DECLARE V_MASTER_PLAN_N UM VARCHAR(10);
DECLARE V_PLAN_NUM VARCHAR(10);
DECLARE V_PLAN_ID VARCHAR(10);
DECLARE V_PAYROLL_GROUP _NUM VARCHAR (10);
DECLARE V_PAYROLL_DT DATE;
DECLARE V_FILE_DATE VARCHAR (30);
DECLARE V_TOTAL_AMOUNT DECIMAL (13, 2);
DECLARE V_STATUS VARCHAR(2);
DECLARE V_OWNER VARCHAR (10);
DECLARE V_CODE_ID INTEGER;
DECLARE C_SOURCE_SYSTEM _NM VARCHAR (7) DEFAULT 'CMNRMTR';
DECLARE V_PSR_TYPE_ID INTEGER;
DECLARE V_ARC_TYPE_ID INTEGER;
DECLARE V_ARC_IND_TYPE_ ID INTEGER;
DECLARE V_LOAN_REQUEST_ TYPE_ID INTEGER;
DECLARE V_LOAN_COMPONEN T_TYPE_ID INTEGER;
DECLARE V_PREMIUM_CONTR _TYPE_ID INTEGER;
DECLARE V_LOAN_REQUEST_ ID INTEGER;
DECLARE V_AAC_TYPE_ID INTEGER;
DECLARE V_LCA_ID INTEGER;
DECLARE V_PSR_ID INTEGER;
DECLARE V_PCR_AGGREGATE _ID INTEGER;
DECLARE V_AAC_AGGREEGAT E_ID INTEGER;
DECLARE V_AAC_INDIVIDUA L_ID INTEGER;
DECLARE V_PCR_INDIVIDUA L_ID INTEGER;
DECLARE V_PERSON_TYPE_I D INTEGER;
DECLARE V_COUNT INTEGER;
DECLARE V_PARTICIPANT_N UM VARCHAR (10);
DECLARE V_MONEY_SRC VARCHAR (20);
DECLARE V_MONEY_SRC_COD E VARCHAR(10);
DECLARE V_CONTR_AMT DECIMAL (13, 2);
DECLARE V_AMOUNT DECIMAL (13, 2);
DECLARE V_PERCENT DECIMAL (13, 2);
DECLARE V_CAL_AMOUNT DECIMAL (13, 2);
DECLARE V_PCT_AGG_AMT DECIMAL (13, 2);
DECLARE V_LOAN_ID VARCHAR (20);
DECLARE V_CONTRACT_NUMB ER VARCHAR (20);
DECLARE V_RECORDKEEPER_ ID VARCHAR (20);
DECLARE V_PRODUCT_ID VARCHAR (20);
DECLARE V_PRODUCT_NUM VARCHAR (20);
DECLARE V_VENDOR_NUM VARCHAR (20);
DECLARE V_SSN VARCHAR (20);
DECLARE V_REQUEST_DATE DATE;
DECLARE V_UO_AMT_COUNT INTEGER;
DECLARE V_O_AMT_COUNT INTEGER;
DECLARE V_PCT_COUNT INTEGER;
DECLARE V_INDEX INTEGER;
DECLARE more_one INT DEFAULT 0;
DECLARE at_end INT DEFAULT 0;
DECLARE not_found CONDITION FOR '02000';
DECLARE more_than_one_r ow CONDITION FOR '21000';
DECLARE c1 CONDITION FOR SQLSTATE '38001' ;
DECLARE CONTINUE HANDLER FOR not_found SET at_end = 1;
DECLARE CONTINUE HANDLER FOR more_than_one_r ow set more_one = 1;
DECLARE GLOBAL TEMPORARY Table TEMP_ALLOCATION S
(AGREEMENT_ID INTEGER,VENDOR_ NUM VARCHAR(20), VENDOR_ID VARCHAR(20),
PRODUCT_NUM VARCHAR(20),PRO DUCT_ID VARCHAR(20),
CONTRACT_NUMBER VARCHAR(20), AMOUNT DECIMAL (13, 2),
PERCENT DECIMAL (13, 2), PRIORITY INTEGER)
ON COMMIT DELETE ROWS;
DECLARE GLOBAL TEMPORARY Table temp_AGREEMENT_ REQUEST_CROSS_R EF LIKE AGREEMENT_REQUE ST_CROSS_REF ON COMMIT DELETE ROWS;
DECLARE GLOBAL TEMPORARY Table temp_AGREEMENT_ REQUEST LIKE AGREEMENT_REQUE ST ON COMMIT DELETE ROWS;
DECLARE GLOBAL TEMPORARY Table temp_FINANCIAL_ MOVEMENT_REQUES T LIKE FINANCIAL_MOVEM ENT_REQUEST ON COMMIT DELETE ROWS;
DECLARE GLOBAL TEMPORARY Table temp_AGREEMENT_ REQUEST_RLSHP LIKE AGREEMENT_REQUE ST_RLSHP ON COMMIT DELETE ROWS;
DECLARE GLOBAL TEMPORARY Table temp_VENDOR_REM ITTANCE_WORK_FI LE LIKE VENDOR_REMITTAN CE_WORK_FILE ON COMMIT DELETE ROWS;
DECLARE GLOBAL TEMPORARY Table temp_ALLOCATION _COMPONENTs
(AGREEMENT_ID INTEGER, AMOUNT DECIMAL (13, 2) ) ON COMMIT DELETE ROWS;
SET V_REQUEST_DATE = DATE(P_LAST_MOD IFIED_TIMESTAMP );
SET V_PLAN_ID = CHAR(P_PLAN_ID) ;
SELECT MASTER_PlAN_NUM ,PLAN_NUM,PAYRO LL_GROUP_NUM,PA YROLL_DT,TO_CHA R(FILE_DATE,'YY YY-MM-DD HH24:MI:SS')
|| '.' || CHAR(MICROSECON D(FILE_DATE)),
REMITTANCE_AMT_ IN,STATUS,OWNER ,LAST_MODIFIED_ USER_ID,LAST_MO DIFIED_TS
INTO V_MASTER_PLAN_N UM, V_PLAN_NUM, V_PAYROLL_GROUP _NUM, V_PAYROLL_DT,
V_FILE_DATE, V_TOTAL_AMOUNT, V_STATUS, V_OWNER, P_LAST_MODIFIED _USER_ID,
P_LAST_MODIFIED _TIMESTAMP
FROM GCRR.PLAN_REMIT TANCE_WORK_FILE
WHERE FILE_UID = P_FILE_UID WITH UR;
SELECT TYPE_ID INTO V_PSR_TYPE_ID
FROM GCRR.TYPE
WHERE TYPE_NM = 'Common remitter payroll submission request' WITH UR;
SELECT TYPE_ID INTO V_ARC_TYPE_ID
FROM GCRR.TYPE
WHERE TYPE_NM = 'Agreement request composition' WITH UR;
SELECT TYPE_ID INTO V_ARC_IND_TYPE_ ID
FROM GCRR.TYPE
WHERE TYPE_NM = 'Agreement request generation' WITH UR;
SELECT TYPE_ID INTO V_LOAN_REQUEST_ TYPE_ID
FROM GCRR.TYPE WHERE TYPE_NM = 'Loan repayment request' WITH UR;
SELECT TYPE_ID INTO V_LOAN_COMPONEN T_TYPE_ID
FROM GCRR.TYPE WHERE TYPE_NM = 'Loan Component' WITH UR;
SELECT TYPE_ID INTO V_PREMIUM_CONTR _TYPE_ID
FROM GCRR.TYPE
WHERE TYPE_NM = 'Premium contribution request' WITH UR;
SELECT TYPE_ID INTO V_PERSON_TYPE_I D
FROM GCRR.TYPE
WHERE TYPE_NM = 'Person' WITH UR;
SELECT CODE_ID INTO V_CODE_ID
FROM GCRR.CODE WHERE CODE_VALUE_TXT = 'RCVD'
AND CODE_SCHEME_ID = (SELECT CODE_ID FROM GCRR.CODE
WHERE LABEL_TXT = 'Request status code'
AND TYPE_ID= (SELECT TYPE_ID FROM GCRR.TYPE WHERE TYPE_NM = 'Code scheme'))
WITH UR;
Select TYPE_ID INTO V_AAC_TYPE_ID from GCRR.TYPE
Where TYPE_NM like 'Asset Accumulation Component' WITH UR;
Select AGREEMENT_REQUE ST_ID INTO V_PSR_ID
FROM GCRR.AGREEMENT_ REQUEST_CROSS_R EF a
Where AGREEMENT_REQUE ST_TYPE_ID = V_PSR_TYPE_ID
AND LOAD_PROCESS_KE Y_PART_01= V_PLAN_ID
AND LOAD_PROCESS_KE Y_PART_02= P_LOCATION_CODE
AND LOAD_PROCESS_KE Y_PART_03 = V_FILE_DATE
AND DELETE_TS IS NULL WITH UR;
SELECT COUNT(ALLOCATIO N_ERROR) INTO V_COUNT
FROM GCRR.PARTICIPAN T_REMITTANCE pr,
GCRR.ALLOCATION _CHANGE_DATA a
WHERE a.PARTICIPANT_N UM = pr.PARTICIPANT_ NUM
AND a.PLAN_NUM = V_PLAN_NUM
AND a.PAYROLL_GROUP _NUM = V_PAYROLL_GROUP _NUM
AND a.ACTIVE = 'Y'
AND a.ALLOCATION_ER ROR = 'Y'
AND INSTRUCTION_UID = P_FILE_UID
WITH UR;
IF V_COUNT > 0 THEN
SIGNAL SQLSTATE '38001' SET MESSAGE_TEXT ='Unable to Process Contribution File, Participant with Allocation Errors.';
END IF;
EACH_CONTR: FOR EACH_SOURCE AS
(SELECT PARTICIPANT_NUM , MONEY_SOURCE, AMOUNT, LOAN_NUM
FROM GCRR.PARTICIPAN T_REMITTANCE
WHERE INSTRUCTION_UID = P_FILE_UID
AND DELETED_FL = 'N') WITH UR
DO
SET V_PARTICIPANT_N UM = EACH_SOURCE.PAR TICIPANT_NUM;
SET V_MONEY_SRC = EACH_SOURCE. MONEY_SOURCE;
SET V_CONTR_AMT = EACH_SOURCE. AMOUNT;
SET V_LOAN_ID = EACH_SOURCE. LOAN_NUM;
SET V_MONEY_SRC = UPPER(V_MONEY_S RC);
SELECT CHAR(CODE_ID) INTO V_MONEY_SRC_COD E
FROM GCRR.CODE WHERE CODE_VALUE_TXT = V_MONEY_SRC
AND CODE_SCHEME_ID IN (SELECT CODE_ID
FROM GCRR.CODE
WHERE LABEL_TXT = 'Money_Source_c d')
WITH UR;
SELECT BUSINESS_KEY_PA RT_01 INTO V_SSN
FROM GCRR.ROLE_PLAYE R_CROSS_REF r
WHERE R.ROLE_PLAYER_T YPE_ID = V_PERSON_TYPE_I D
AND SOURCE_SYSTEM_N M = C_SOURCE_SYSTEM _NM
AND LOAD_PROCESS_KE Y_PART_01 = V_PARTICIPANT_N UM
AND DELETE_TS is NULL WITH UR;
SET at_end = 0;
If UPPER(V_MONEY_S RC) = 'LP'
THEN
SET more_one = 0;
SELECT AGREEMENT_ID
INTO V_LCA_ID
FROM GCRR.AGREEMENT_ CROSS_REF A
WHERE A.LOAD_PROCESS_ KEY_PART_02= V_LOAN_ID
AND A.LOAD_PROCESS_ KEY_PART_03= V_PLAN_ID
AND A.LOAD_PROCESS_ KEY_PART_04= P_LOCATION_CODE
AND A.LOAD_PROCESS_ KEY_PART_06= V_PARTICIPANT_N UM
AND A.SOURCE_SYSTEM _NM = C_SOURCE_SYSTEM _NM
AND A.AGREEMENT_TYP E_ID = V_LOAN_COMPONEN T_TYPE_ID
AND A.DELETE_TS is null WITH UR;
IF (at_end = 1) THEN
SIGNAL SQLSTATE '38001' SET MESSAGE_TEXT ='Unable to Process Contribution File, Invalid Loan Number.';
END IF;
IF (more_one = 1) THEN
SIGNAL SQLSTATE '38001' SET MESSAGE_TEXT = 'Unable to Process Contribution File, Loan Agreement is duplicate.' ;
END IF;
INSERT INTO SESSION.temp_VE NDOR_REMITTANCE _WORK_FILE
(VENDOR_NUM, PARTICIPANT_NUM , PARTICIPANT_SSN ,PLAN_NUM, PRODUCT_NUM, MONEY_SOURCE,
VENDOR_CONTRACT _NUM, REMITTANCE_AMT, UID, STATUS,LAST_MOD IFIED_TS,
CREATION_TS,CRE ATION_USER_ID,L AST_MODIFIED_US ER_ID,LOAN_NUM)
VALUES (V_VENDOR_NUM,V _PARTICIPANT_NU M,V_SSN,V_PLAN_ NUM, '', V_MONEY_SRC, V_CONTRACT_NUMB ER, V_CONTR_AMT,
P_FILE_UID,'FP' ,P_LAST_MODIFIE D_TIMESTAMP,P_L AST_MODIFIED_TI MESTAMP,P_LAST_ MODIFIED_USER_I D,
P_LAST_MODIFIED _USER_ID,V_LOAN _ID);
Select AGREEMENT_REQUE ST_ID INTO V_LOAN_REQUEST_ ID
from SESSION.temp_AG REEMENT_REQUEST _CROSS_REF a
Where AGREEMENT_REQUE ST_TYPE_ID = V_LOAN_REQUEST_ TYPE_ID
And LOAD_PROCESS_KE Y_PART_01= V_PLAN_ID
And LOAD_PROCESS_KE Y_PART_02= P_LOCATION_CODE
And LOAD_PROCESS_KE Y_PART_03 = V_FILE_DATE
AND LOAD_PROCESS_KE Y_PART_04 = V_PARTICIPANT_N UM
AND LOAD_PROCESS_KE Y_PART_06 = V_LOAN_ID
AND A.DELETE_TS is null WITH UR;
IF (at_end = 1) THEN
SELECT NEXTVAL FOR GCRR.AGREEMENT_ REQUEST_CROSS_R EFSEQ INTO V_LOAN_REQUEST_ ID
FROM SYSIBM.SYSDUMMY 1 s;
INSERT INTO SESSION.temp_AG REEMENT_REQUEST _CROSS_REF (AGREEMENT_REQU EST_ID,
SOURCE_SYSTEM_N M,AGREEMENT_REQ UEST_TYPE_ID,
LOAD_PROCESS_KE Y_PART_01,LOAD_ PROCESS_KEY_PAR T_02,
LOAD_PROCESS_KE Y_PART_03,LOAD_ PROCESS_KEY_PAR T_04,
LOAD_PROCESS_KE Y_PART_05,LOAD_ PROCESS_KEY_PAR T_06,
LOAD_PROCESS_KE Y_PART_07,LOAD_ PROCESS_COMPOSI TE_KEY,
BUSINESS_KEY_PA RT_01,BUSINESS_ KEY_PART_02,
BUSINESS_KEY_PA RT_03,BUSINESS_ KEY_PART_04,
BUSINESS_KEY_PA RT_05,BUSINESS_ KEY_PART_06,
LOAD_TS,CREATIO N_TS,LAST_MODIF IED_TS)
VALUES
(V_LOAN_REQUEST _ID, C_SOURCE_SYSTEM _NM, V_LOAN_REQUEST_ TYPE_ID,
V_PLAN_ID, P_LOCATION_CODE , V_FILE_DATE
, V_PARTICIPANT_N UM, V_CONTRACT_NUMB ER, V_LOAN_ID,V_REC ORDKEEPER_ID,
V_PLAN_ID||'~'| |P_LOCATION_COD E||'~'|| V_FILE_DATE||'~ '|| V_PARTICIPANT_N UM ||'~'|| V_CONTRACT_NUMB ER
||'~'|| V_LOAN_ID,
V_PAYROLL_GROUP _NUM, V_FILE_DATE, V_PARTICIPANT_N UM,
V_CONTRACT_NUMB ER, V_LOAN_ID, V_RECORDKEEPER_ ID,
P_LAST_MODIFIED _TIMESTAMP, P_LAST_MODIFIED _TIMESTAMP,
P_LAST_MODIFIED _TIMESTAMP);
INSERT INTO SESSION.temp_AG REEMENT_REQUEST (AGREEMENT_REQU EST_ID,TYPE_ID, AGREEMENT_ID,RE QUEST_TS,SOURCE _SYSTEM_TXT,DEL ETED_FL, CREATION_USER_I D,LOAD_TS,LAST_ MODIFIED_USER_I D, CREATION_TS,LAS T_MODIFIED_TS,R EQUEST_STATUS_C D,REQUEST_STATU S_DT)
VALUES (V_LOAN_REQUEST _ID, V_LOAN_REQUEST_ TYPE_ID, V_LCA_ID, V_FILE_DATE, C_SOURCE_SYSTEM _NM, 'N', P_LAST_MODIFIED _USER_ID, P_LAST_MODIFIED _TIMESTAMP, P_LAST_MODIFIED _USER_ID, P_LAST_MODIFIED _TIMESTAMP, P_LAST_MODIFIED _TIMESTAMP, V_CODE_ID, V_REQUEST_DATE) ;
INSERT INTO SESSION.temp_FI NANCIAL_MOVEMEN T_REQUEST (
AGREEMENT_REQUE ST_ID, TYPE_ID, Request_amt,
PAYROLL_PERIOD_ END_DT, SOURCE_SYSTEM_T XT, DELETED_FL,
CREATION_USER_I D, CREATION_TS, LAST_MODIFIED_U SER_ID, LAST_MODIFIED_T S)
VALUES (V_LOAN_REQUEST _ID, V_LOAN_REQUEST_ TYPE_ID, V_CONTR_AMT,
V_PAYROLL_DT, C_SOURCE_SYSTEM _NM, 'N',
P_LAST_MODIFIED _USER_ID, P_LAST_MODIFIED _TIMESTAMP, P_LAST_MODIFIED _USER_ID,
P_LAST_MODIFIED _TIMESTAMP);
INSERT INTO SESSION.temp_AG REEMENT_REQUEST _RLSHP(AGREEMEN T_REQUEST_RLSHP _ID,LEFT_AGREEM ENT_REQUEST_ID, SOURCE_SYSTEM_T XT,RIGHT_AGREEM ENT_REQUEST_ID, NATURE_ID,START _TS,RIGHT_AGREE MENT_RQST_TYPE_ ID,LEFT_AGREEME NT_REQUEST_TYPE _ID,DELETED_FL, CREATION_USER_I D,LOAD_TS,LAST_ MODIFIED_USER_I D,CREATION_TS,L AST_MODIFIED_TS )
VALUES (1,V_PSR_ID, C_SOURCE_SYSTEM _NM, V_LOAN_REQUEST_ ID, V_ARC_TYPE_ID, P_LAST_MODIFIED _TIMESTAMP, V_LOAN_REQUEST_ TYPE_ID, V_PSR_TYPE_ID, 'N', P_LAST_MODIFIED _USER_ID, P_LAST_MODIFIED _TIMESTAMP, P_LAST_MODIFIED _USER_ID, P_LAST_MODIFIED _TIMESTAMP, P_LAST_MODIFIED _TIMESTAMP);
INSERT INTO SESSION.temp_AL LOCATION_COMPON ENTs VALUES (V_LCA_ID,V_CON TR_AMT);
SET at_end = 0;
END IF;
ELSE
SET at_end = 0;
Select AGREEMENT_ID INTO V_AAC_AGGREEGAT E_ID
from GCRR.AGREEMENT_ CROSS_REF a
Where AGREEMENT_TYPE_ ID = V_AAC_TYPE_ID
And SOURCE_SYSTEM_N M = C_SOURCE_SYSTEM _NM
And LOAD_PROCESS_KE Y_PART_01= V_PLAN_ID
And load_process_ke y_part_02= P_LOCATION_CODE
And load_process_ke y_part_03= V_PARTICIPANT_N UM
And load_process_ke y_part_04 =V_MONEY_SRC_CO DE
AND DELETE_TS IS NULL WITH UR;
IF (at_end = 1) THEN
SIGNAL SQLSTATE '38001' SET MESSAGE_TEXT ='Unable to Process Contribution File, Asset Accumulation Component does not exists.' ;
END IF;
Select AGREEMENT_REQUE ST_ID INTO V_PCR_AGGREGATE _ID
from SESSION.temp_AG REEMENT_REQUEST _CROSS_REF a
Where AGREEMENT_REQUE ST_TYPE_ID = V_PREMIUM_CONTR _TYPE_ID
AND LOAD_PROCESS_KE Y_PART_01 = V_PLAN_ID
AND LOAD_PROCESS_KE Y_PART_02 = P_LOCATION_CODE
AND LOAD_PROCESS_KE Y_PART_03 = V_FILE_DATE
AND LOAD_PROCESS_KE Y_PART_04 = V_PARTICIPANT_N UM
AND LOAD_PROCESS_KE Y_PART_05 = V_MONEY_SRC_COD E
AND DELETE_TS IS NULL WITH UR;
IF (at_end = 1) THEN
SELECT NEXTVAL FOR GCRR.AGREEMENT_ REQUEST_CROSS_R EFSEQ INTO V_PCR_AGGREGATE _ID
FROM SYSIBM.SYSDUMMY 1 s;
INSERT INTO SESSION.temp_AG REEMENT_REQUEST _CROSS_REF (AGREEMENT_REQU EST_ID, SOURCE_SYSTEM_N M,
AGREEMENT_REQUE ST_TYPE_ID, LOAD_PROCESS_KE Y_PART_01, LOAD_PROCESS_KE Y_PART_02,
LOAD_PROCESS_KE Y_PART_03, LOAD_PROCESS_KE Y_PART_04, LOAD_PROCESS_KE Y_PART_05,
LOAD_PROCESS_CO MPOSITE_KEY,
BUSINESS_KEY_PA RT_01, BUSINESS_KEY_PA RT_02,
BUSINESS_KEY_PA RT_03, BUSINESS_KEY_PA RT_04,
LOAD_TS, CREATION_TS, LAST_MODIFIED_T S)
VALUES
(V_PCR_AGGREGAT E_ID, C_SOURCE_SYSTEM _NM, V_PREMIUM_CONTR _TYPE_ID,
RTRIM(V_PLAN_ID ), P_LOCATION_CODE , V_FILE_DATE, RTRIM(V_PARTICI PANT_NUM),
V_MONEY_SRC_COD E, RTRIM(V_PLAN_ID )||'~'||P_LOCAT ION_CODE||'~'|| V_FILE_DATE||'~ '|| RTRIM(V_PARTICI PANT_NUM) ||'~'|| V_MONEY_SRC_COD E,
V_PAYROLL_GROUP _NUM, V_FILE_DATE, V_PARTICIPANT_N UM, V_MONEY_SRC_COD E, P_LAST_MODIFIED _TIMESTAMP,
P_LAST_MODIFIED _TIMESTAMP, P_LAST_MODIFIED _TIMESTAMP);
INSERT INTO SESSION.temp_AG REEMENT_REQUEST (AGREEMENT_REQU EST_ID, TYPE_ID, AGREEMENT_ID,
REQUEST_TS, SOURCE_SYSTEM_T XT, DELETED_FL,CREA TION_USER_ID, LOAD_TS, LAST_MODIFIED_U SER_ID,
CREATION_TS, LAST_MODIFIED_T S, REQUEST_STATUS_ CD, REQUEST_STATUS_ DT)
VALUES (V_PCR_AGGREGAT E_ID, V_PREMIUM_CONTR _TYPE_ID, V_AAC_AGGREEGAT E_ID,
V_FILE_DATE, C_SOURCE_SYSTEM _NM, 'N', P_LAST_MODIFIED _USER_ID, P_LAST_MODIFIED _TIMESTAMP,
P_LAST_MODIFIED _USER_ID, P_LAST_MODIFIED _TIMESTAMP, P_LAST_MODIFIED _TIMESTAMP, V_CODE_ID, V_REQUEST_DATE) ;
INSERT INTO SESSION.temp_FI NANCIAL_MOVEMEN T_REQUEST (
AGREEMENT_REQUE ST_ID, TYPE_ID, Request_amt,
EXPECTED_DEFERR AL_AMT, PAYROLL_PERIOD_ END_DT, SOURCE_SYSTEM_T XT, DELETED_FL,
CREATION_USER_I D, CREATION_TS, LAST_MODIFIED_U SER_ID, LAST_MODIFIED_T S)
VALUES (V_PCR_AGGREGAT E_ID, V_PREMIUM_CONTR _TYPE_ID, V_CONTR_AMT,
V_CONTR_AMT, V_PAYROLL_DT, C_SOURCE_SYSTEM _NM, 'N',
P_LAST_MODIFIED _USER_ID, P_LAST_MODIFIED _TIMESTAMP,
P_LAST_MODIFIED _USER_ID, P_LAST_MODIFIED _TIMESTAMP);
INSERT INTO SESSION.temp_AG REEMENT_REQUEST _RLSHP(AGREEMEN T_REQUEST_RLSHP _ID,LEFT_AGREEM ENT_REQUEST_ID, SOURCE_SYSTEM_T XT,
RIGHT_AGREEMENT _REQUEST_ID,NAT URE_ID,START_TS ,RIGHT_AGREEMEN T_RQST_TYPE_ID,
LEFT_AGREEMENT_ REQUEST_TYPE_ID ,DELETED_FL,CRE ATION_USER_ID,L OAD_TS,LAST_MOD IFIED_USER_ID,
CREATION_TS,LAS T_MODIFIED_TS)
VALUES (1, V_PSR_ID, C_SOURCE_SYSTEM _NM, V_PCR_AGGREGATE _ID,
V_ARC_TYPE_ID, P_LAST_MODIFIED _TIMESTAMP,
V_PREMIUM_CONTR _TYPE_ID, V_PSR_TYPE_ID, 'N',
P_LAST_MODIFIED _USER_ID, P_LAST_MODIFIED _TIMESTAMP,
P_LAST_MODIFIED _USER_ID, P_LAST_MODIFIED _TIMESTAMP,
P_LAST_MODIFIED _TIMESTAMP);
INSERT INTO SESSION.temp_AL LOCATION_COMPON ENTs VALUES (V_AAC_AGGREEGA TE_ID,V_CONTR_A MT);
SET at_end = 0;
END IF;
DELETE FROM SESSION.TEMP_AL LOCATIONS;
INSERT INTO SESSION.TEMP_AL LOCATIONS
(SELECT AGREEMENT_ID, BUSINESS_KEY_PA RT_05, LOAD_PROCESS_KE Y_PART_05,
BUSINESS_KEY_PA RT_07, LOAD_PROCESS_KE Y_PART_07,
LOAD_PROCESS_KE Y_PART_01, ar.ALLOCATION_A MT, ar.ALLOCATION_P CT,ar.PRIORITY_ SEQUENCE_NBR
FROM GCRR.AGREEMENT_ CROSS_REF a, GCRR.AGREEMENT_ RLSHP ar
WHERE a.AGREEMENT_ID = ar.RIGHT_AGREEM ENT_ID
AND AGREEMENT_TYPE_ ID = V_AAC_TYPE_ID
AND SOURCE_SYSTEM_N M = C_SOURCE_SYSTEM _NM
AND LOAD_PROCESS_KE Y_PART_02 = V_MONEY_SRC_COD E
AND LOAD_PROCESS_KE Y_PART_03 = V_PLAN_ID
AND LOAD_PROCESS_KE Y_PART_04 = P_LOCATION_CODE
AND LOAD_PROCESS_KE Y_PART_06 =V_PARTICIPANT_ NUM
AND DELETE_TS is null
AND DELETEd_FL = 'N' ) WITH UR;
SELECT COUNT(1) INTO V_UO_AMT_COUNT
FROM SESSION.TEMP_AL LOCATIONS
WHERE PRIORITY = 0 AND AMOUNT > 0 WITH UR;
SELECT COUNT(1) INTO V_O_AMT_COUNT
FROM SESSION.TEMP_AL LOCATIONS
WHERE PRIORITY > 0 AND AMOUNT > 0 WITH UR;
SELECT COUNT(1) INTO V_PCT_COUNT
FROM SESSION.TEMP_AL LOCATIONS
WHERE PERCENT > 0 WITH UR;
IF ( ( V_UO_AMT_COUNT > 0 ) AND ( V_O_AMT_COUNT > 0 ) ) OR
( V_PCT_COUNT = 0
AND ( V_UO_AMT_COUNT > 0 )
AND (SELECT SUM(AMOUNT) FROM SESSION.TEMP_AL LOCATIONS WHERE PRIORITY = 0 ) > V_CONTR_AMT)
THEN
SIGNAL SQLSTATE '38001'
SET MESSAGE_TEXT ='Unable to Process Contribution File, Invalid Allocations.' ;
END IF;
SET V_INDEX = 1;
SET V_PCT_AGG_AMT = 0;
EACH_ALLOC: FOR EACH_ALLOC AS SELECT * FROM SESSION.TEMP_AL LOCATIONS
ORDER BY PERCENT ASC, PRIORITY ASC WITH UR
DO
SET V_AAC_INDIVIDUA L_ID = EACH_ALLOC.AGRE EMENT_ID;
SET V_VENDOR_NUM = EACH_ALLOC.VEND OR_NUM;
SET V_RECORDKEEPER_ ID = EACH_ALLOC.VEND OR_ID;
SET V_CONTRACT_NUMB ER = EACH_ALLOC.CONT RACT_NUMBER;
SET V_PRODUCT_NUM = EACH_ALLOC.PROD UCT_NUM;
SET V_PRODUCT_ID = EACH_ALLOC.PROD UCT_ID;
SET V_AMOUNT = EACH_ALLOC.AMOU NT;
SET V_PERCENT = EACH_ALLOC.PERC ENT;
SET V_CAL_AMOUNT = 0;
IF V_AMOUNT > 0 THEN
IF ( V_AMOUNT >= V_CONTR_AMT ) THEN
SET V_CAL_AMOUNT = V_CONTR_AMT;
SET V_CONTR_AMT = 0;
ELSE
SET V_CAL_AMOUNT = V_AMOUNT;
SET V_CONTR_AMT = V_CONTR_AMT - V_AMOUNT;
END IF;
ELSE
IF V_PCT_COUNT = V_INDEX
THEN
SET V_CAL_AMOUNT = V_CONTR_AMT - V_PCT_AGG_AMT;
ELSE
SET V_INDEX = V_INDEX + 1;
SET V_CAL_AMOUNT = (V_CONTR_AMT * V_PERCENT) / 100;
SET V_PCT_AGG_AMT = V_PCT_AGG_AMT + V_CAL_AMOUNT;
END IF;
END IF;
INSERT INTO SESSION.temp_VE NDOR_REMITTANCE _WORK_FILE
(VENDOR_NUM, PARTICIPANT_NUM , PARTICIPANT_SSN ,PLAN_NUM, PRODUCT_NUM, MONEY_SOURCE,
VENDOR_CONTRACT _NUM, REMITTANCE_AMT, UID, STATUS,LAST_MOD IFIED_TS,
CREATION_TS,CRE ATION_USER_ID,L AST_MODIFIED_US ER_ID)
VALUES (V_VENDOR_NUM,V _PARTICIPANT_NU M,V_SSN,V_PLAN_ NUM, V_PRODUCT_NUM, V_MONEY_SRC, V_CONTRACT_NUMB ER, V_CAL_AMOUNT,
P_FILE_UID,'FP' ,P_LAST_MODIFIE D_TIMESTAMP,P_L AST_MODIFIED_TI MESTAMP,P_LAST_ MODIFIED_USER_I D,P_LAST_MODIFI ED_USER_ID);
SET at_end = 0;
Select AGREEMENT_REQUE ST_ID INTO V_PCR_INDIVIDUA L_ID
from SESSION.temp_AG REEMENT_REQUEST _CROSS_REF a
Where AGREEMENT_REQUE ST_TYPE_ID = V_PREMIUM_CONTR _TYPE_ID
AND LOAD_PROCESS_KE Y_PART_01 = V_PLAN_ID
AND LOAD_PROCESS_KE Y_PART_02 = P_LOCATION_CODE
AND LOAD_PROCESS_KE Y_PART_03 = V_FILE_DATE
AND LOAD_PROCESS_KE Y_PART_04 = V_PARTICIPANT_N UM
AND LOAD_PROCESS_KE Y_PART_05 = V_CONTRACT_NUMB ER
AND LOAD_PROCESS_KE Y_PART_06 = V_MONEY_SRC_COD E
AND LOAD_PROCESS_KE Y_PART_07 = V_RECORDKEEPER_ ID
AND DELETE_TS IS NULL;
IF (at_end = 1) THEN
SELECT NEXTVAL FOR GCRR.AGREEMENT_ REQUEST_CROSS_R EFSEQ INTO V_PCR_INDIVIDUA L_ID
FROM SYSIBM.SYSDUMMY 1 s;
INSERT INTO SESSION.temp_AG REEMENT_REQUEST _CROSS_REF (AGREEMENT_REQU EST_ID, SOURCE_SYSTEM_N M,
AGREEMENT_REQUE ST_TYPE_ID, LOAD_PROCESS_KE Y_PART_01, LOAD_PROCESS_KE Y_PART_02,
LOAD_PROCESS_KE Y_PART_03, LOAD_PROCESS_KE Y_PART_04, LOAD_PROCESS_KE Y_PART_05,
LOAD_PROCESS_KE Y_PART_06, LOAD_PROCESS_KE Y_PART_07,
LOAD_PROCESS_CO MPOSITE_KEY,
BUSINESS_KEY_PA RT_01, BUSINESS_KEY_PA RT_02,
BUSINESS_KEY_PA RT_03, BUSINESS_KEY_PA RT_04,
BUSINESS_KEY_PA RT_05, BUSINESS_KEY_PA RT_06,
LOAD_TS, CREATION_TS, LAST_MODIFIED_T S)
VALUES
(V_PCR_INDIVIDU AL_ID, C_SOURCE_SYSTEM _NM, V_PREMIUM_CONTR _TYPE_ID,
RTRIM(V_PLAN_ID ), P_LOCATION_CODE , V_FILE_DATE, RTRIM(V_PARTICI PANT_NUM),
V_CONTRACT_NUMB ER, V_MONEY_SRC_COD E,V_RECORDKEEPE R_ID,
RTRIM(V_PLAN_ID )||'~'||P_LOCAT ION_CODE||'~'|| V_FILE_DATE||'~ '|| RTRIM(V_PARTICI PANT_NUM) ||'~'|| V_CONTRACT_NUMB ER ||'~'|| V_MONEY_SRC_COD E,
V_PAYROLL_GROUP _NUM, V_FILE_DATE, V_PARTICIPANT_N UM, V_CONTRACT_NUMB ER, V_MONEY_SRC_COD E,V_VENDOR_NUM,
P_LAST_MODIFIED _TIMESTAMP, P_LAST_MODIFIED _TIMESTAMP, P_LAST_MODIFIED _TIMESTAMP);
INSERT INTO SESSION.temp_AG REEMENT_REQUEST (AGREEMENT_REQU EST_ID, TYPE_ID, AGREEMENT_ID,
REQUEST_TS, SOURCE_SYSTEM_T XT, DELETED_FL,CREA TION_USER_ID, LOAD_TS, LAST_MODIFIED_U SER_ID,
CREATION_TS, LAST_MODIFIED_T S, REQUEST_STATUS_ CD, REQUEST_STATUS_ DT)
VALUES (V_PCR_INDIVIDU AL_ID, V_PREMIUM_CONTR _TYPE_ID, V_AAC_INDIVIDUA L_ID,
V_FILE_DATE, C_SOURCE_SYSTEM _NM, 'N', P_LAST_MODIFIED _USER_ID, P_LAST_MODIFIED _TIMESTAMP,
P_LAST_MODIFIED _USER_ID, P_LAST_MODIFIED _TIMESTAMP, P_LAST_MODIFIED _TIMESTAMP, V_CODE_ID, V_REQUEST_DATE) ;
INSERT INTO SESSION.temp_FI NANCIAL_MOVEMEN T_REQUEST (
AGREEMENT_REQUE ST_ID, TYPE_ID, Request_amt,
EXPECTED_DEFERR AL_AMT, PAYROLL_PERIOD_ END_DT, SOURCE_SYSTEM_T XT, DELETED_FL,
CREATION_USER_I D, CREATION_TS, LAST_MODIFIED_U SER_ID, LAST_MODIFIED_T S)
VALUES (V_PCR_INDIVIDU AL_ID, V_PREMIUM_CONTR _TYPE_ID, V_CAL_AMOUNT,
V_CAL_AMOUNT, V_PAYROLL_DT, C_SOURCE_SYSTEM _NM, 'N',
P_LAST_MODIFIED _USER_ID, P_LAST_MODIFIED _TIMESTAMP,
P_LAST_MODIFIED _USER_ID, P_LAST_MODIFIED _TIMESTAMP);
INSERT INTO SESSION.temp_AG REEMENT_REQUEST _RLSHP(AGREEMEN T_REQUEST_RLSHP _ID,LEFT_AGREEM ENT_REQUEST_ID, SOURCE_SYSTEM_T XT,
RIGHT_AGREEMENT _REQUEST_ID,NAT URE_ID,START_TS ,RIGHT_AGREEMEN T_RQST_TYPE_ID,
LEFT_AGREEMENT_ REQUEST_TYPE_ID ,DELETED_FL,CRE ATION_USER_ID,L OAD_TS,LAST_MOD IFIED_USER_ID,
CREATION_TS,LAS T_MODIFIED_TS)
VALUES (1,V_PCR_AGGREG ATE_ID, C_SOURCE_SYSTEM _NM, V_PCR_INDIVIDUA L_ID,
V_ARC_IND_TYPE_ ID, P_LAST_MODIFIED _TIMESTAMP,
V_PREMIUM_CONTR _TYPE_ID, V_PSR_TYPE_ID, 'N',
P_LAST_MODIFIED _USER_ID, P_LAST_MODIFIED _TIMESTAMP,
P_LAST_MODIFIED _USER_ID, P_LAST_MODIFIED _TIMESTAMP,
P_LAST_MODIFIED _TIMESTAMP);
INSERT INTO SESSION.temp_AL LOCATION_COMPON ENTs VALUES (V_AAC_INDIVIDU AL_ID,V_CAL_AMO UNT);
SET at_end = 0;
END IF;
END FOR EACH_ALLOC;
IF V_CONTR_AMT > 0 AND V_PCT_COUNT = 0 THEN
UPDATE SESSION.temp_VE NDOR_REMITTANCE _WORK_FILE
SET REMITTANCE_AMT = REMITTANCE_AMT + V_CONTR_AMT
WHERE (V_VENDOR_NUM, VENDOR_CONTRACT _NUM, PRODUCT_NUM)
in (SELECT VENDOR_NUM, CONTRACT_NUMBER , PRODUCT_NUM
FROM SESSION.TEMP_AL LOCATIONS
WHERE PRIORITY = (SELECT MIN(PRIORITY) FROM SESSION.TEMP_AL LOCATIONS))
AND MONEY_SOURCE = V_MONEY_SRC
AND PARTICIPANT_NUM = PARTICIPANT_NUM ;
UPDATE SESSION.temp_AL LOCATION_COMPON ENTs
SET AMOUNT = AMOUNT + V_CONTR_AMT
WHERE AGREEMENT_ID = ( SELECT AGREEMENT_ID
FROM SESSION.TEMP_AL LOCATIONS
WHERE PRIORITY = (SELECT MIN(PRIORITY) FROM SESSION.TEMP_AL LOCATIONS));
END IF;
END IF;
END FOR;
SET at_end = 0;
SELECT COUNT(1) INTO V_INDEX
FROM SESSION.temp_VE NDOR_REMITTANCE _WORK_FILE v
WHERE UID = P_FILE_UID
GROUP BY vENdor_num
HAVING SUM(REMITTANCE_ AMT) < 0;
IF at_end = 0 THEN
INSERT INTO GCRR.INSTRUCTIO N_FILE_ERRORS(E RROR_CODE, FILE_UID, PARTICIPANT_NUM ,
CREATION_TS, LAST_MODIFIED_T S, CREATION_USER_I D, LAST_MODIFIED_U SER_ID)
VALUES ('VNEG', P_FILE_UID, '', P_LAST_MODIFIED _TIMESTAMP, P_LAST_MODIFIED _TIMESTAMP, P_LAST_MODIFIED _USER_ID, P_LAST_MODIFIED _USER_ID);
UPDATE GCRR.PLAN_REMIT TANCE_WORK_FILE p
SET STATUS = 'FE'
where file_uid = P_FILE_UID;
ELSE
UPDATE GCRR.PLAN_REMIT TANCE_WORK_FILE p
SET STATUS = 'FP'
where file_uid = P_FILE_UID;
UPDATE GCRR.ASSET_ACCU MULATION_COMPON ENT A
SET CURRENT_YTD_CON TRIBUTION_AMT = CURRENT_YTD_CON TRIBUTION_AMT +
(SELECT AMOUNT FROM SESSION.temp_AL LOCATION_COMPON ENTs ta where a.AGREEMENT_ID = ta.AGREEMENT_ID ),
LAST_MODIFIED_U SER_ID = P_LAST_MODIFIED _USER_ID,
LAST_MODIFIED_T S = P_LAST_MODIFIED _TIMESTAMP;
UPDATE GCRR.LOAN_COMPO NENT A
SET CURRENT_YTD_REP AYMENT_AMT = CURRENT_YTD_REP AYMENT_AMT +
(SELECT AMOUNT FROM SESSION.temp_AL LOCATION_COMPON ENTs ta where a.AGREEMENT_ID = ta.AGREEMENT_ID ),
LAST_MODIFIED_U SER_ID = P_LAST_MODIFIED _USER_ID,
LAST_MODIFIED_T S = P_LAST_MODIFIED _TIMESTAMP ;
INSERT INTO GCRR.AGREEMENT_ REQUEST_CROSS_R EF (SELECT * FROM SESSION.TEMP_AG REEMENT_REQUEST _CROSS_REF);
INSERT INTO GCRR.AGREEMENT_ REQUEST (SELECT * FROM SESSION.TEMP_AG REEMENT_REQUEST );
INSERT INTO GCRR.FINANCIAL_ MOVEMENT_REQUES T (SELECT * FROM SESSION.TEMP_FI NANCIAL_MOVEMEN T_REQUEST);
INSERT INTO GCRR.AGREEMENT_ REQUEST_RLSHP(L EFT_AGREEMENT_R EQUEST_ID, SOURCE_SYSTEM_T XT,
RIGHT_AGREEMENT _REQUEST_ID, NATURE_ID, START_TS, RIGHT_AGREEMENT _RQST_TYPE_ID,
END_TS, LEFT_AGREEMENT_ REQUEST_TYPE_ID , DELETED_FL,
CREATION_USER_I D, LOAD_TS, LAST_MODIFIED_U SER_ID, CREATION_TS, LAST_MODIFIED_T S)
SELECT LEFT_AGREEMENT_ REQUEST_ID, SOURCE_SYSTEM_T XT,
RIGHT_AGREEMENT _REQUEST_ID, NATURE_ID, START_TS, RIGHT_AGREEMENT _RQST_TYPE_ID,
END_TS, LEFT_AGREEMENT_ REQUEST_TYPE_ID , DELETED_FL,
CREATION_USER_I D, LOAD_TS, LAST_MODIFIED_U SER_ID, CREATION_TS, LAST_MODIFIED_T S
FROM SESSION.temp_AG REEMENT_REQUEST _RLSHP;
INSERT INTO GCRR.VENDOR_REM ITTANCE_WORK_FI LE (SELECT * FROM SESSION.TEMP_VE NDOR_REMITTANCE _WORK_FILE);
END IF;
END
;
DROP SPECIFIC PROCEDURE GCRR.SQL0705221 21828900
;
CREATE PROCEDURE "GCRR"."CONTRIB UTION_ODS_ENTRI ES"
(IN "P_FILE_UID " CHARACTER(8),
IN "P_PLAN_ID" VARCHAR(45),
IN "P_LOCATION_COD E" VARCHAR(45),
IN "P_LAST_MODIFIE D_USER_ID" VARCHAR(25),
IN "P_LAST_MODIFIE D_TIMESTAMP" TIMESTAMP
)
SPECIFIC "GCRR"."SQL0705 22121828900"
LANGUAGE SQL
NOT DETERMINISTIC
CALLED ON NULL INPUT
EXTERNAL ACTION
OLD SAVEPOINT LEVEL
MODIFIES SQL DATA
INHERIT SPECIAL REGISTERS
BEGIN
DECLARE V_MASTER_PLAN_N UM VARCHAR(10);
DECLARE V_PLAN_NUM VARCHAR(10);
DECLARE V_PLAN_ID VARCHAR(10);
DECLARE V_PAYROLL_GROUP _NUM VARCHAR (10);
DECLARE V_PAYROLL_DT DATE;
DECLARE V_FILE_DATE VARCHAR (30);
DECLARE V_TOTAL_AMOUNT DECIMAL (13, 2);
DECLARE V_STATUS VARCHAR(2);
DECLARE V_OWNER VARCHAR (10);
DECLARE V_CODE_ID INTEGER;
DECLARE C_SOURCE_SYSTEM _NM VARCHAR (7) DEFAULT 'CMNRMTR';
DECLARE V_PSR_TYPE_ID INTEGER;
DECLARE V_ARC_TYPE_ID INTEGER;
DECLARE V_ARC_IND_TYPE_ ID INTEGER;
DECLARE V_LOAN_REQUEST_ TYPE_ID INTEGER;
DECLARE V_LOAN_COMPONEN T_TYPE_ID INTEGER;
DECLARE V_PREMIUM_CONTR _TYPE_ID INTEGER;
DECLARE V_LOAN_REQUEST_ ID INTEGER;
DECLARE V_AAC_TYPE_ID INTEGER;
DECLARE V_LCA_ID INTEGER;
DECLARE V_PSR_ID INTEGER;
DECLARE V_PCR_AGGREGATE _ID INTEGER;
DECLARE V_AAC_AGGREEGAT E_ID INTEGER;
DECLARE V_AAC_INDIVIDUA L_ID INTEGER;
DECLARE V_PCR_INDIVIDUA L_ID INTEGER;
DECLARE V_PERSON_TYPE_I D INTEGER;
DECLARE V_COUNT INTEGER;
DECLARE V_PARTICIPANT_N UM VARCHAR (10);
DECLARE V_MONEY_SRC VARCHAR (20);
DECLARE V_MONEY_SRC_COD E VARCHAR(10);
DECLARE V_CONTR_AMT DECIMAL (13, 2);
DECLARE V_AMOUNT DECIMAL (13, 2);
DECLARE V_PERCENT DECIMAL (13, 2);
DECLARE V_CAL_AMOUNT DECIMAL (13, 2);
DECLARE V_PCT_AGG_AMT DECIMAL (13, 2);
DECLARE V_LOAN_ID VARCHAR (20);
DECLARE V_CONTRACT_NUMB ER VARCHAR (20);
DECLARE V_RECORDKEEPER_ ID VARCHAR (20);
DECLARE V_PRODUCT_ID VARCHAR (20);
DECLARE V_PRODUCT_NUM VARCHAR (20);
DECLARE V_VENDOR_NUM VARCHAR (20);
DECLARE V_SSN VARCHAR (20);
DECLARE V_REQUEST_DATE DATE;
DECLARE V_UO_AMT_COUNT INTEGER;
DECLARE V_O_AMT_COUNT INTEGER;
DECLARE V_PCT_COUNT INTEGER;
DECLARE V_INDEX INTEGER;
DECLARE more_one INT DEFAULT 0;
DECLARE at_end INT DEFAULT 0;
DECLARE not_found CONDITION FOR '02000';
DECLARE more_than_one_r ow CONDITION FOR '21000';
DECLARE c1 CONDITION FOR SQLSTATE '38001' ;
DECLARE CONTINUE HANDLER FOR not_found SET at_end = 1;
DECLARE CONTINUE HANDLER FOR more_than_one_r ow set more_one = 1;
DECLARE GLOBAL TEMPORARY Table TEMP_ALLOCATION S
(AGREEMENT_ID INTEGER,VENDOR_ NUM VARCHAR(20), VENDOR_ID VARCHAR(20),
PRODUCT_NUM VARCHAR(20),PRO DUCT_ID VARCHAR(20),
CONTRACT_NUMBER VARCHAR(20), AMOUNT DECIMAL (13, 2),
PERCENT DECIMAL (13, 2), PRIORITY INTEGER)
ON COMMIT DELETE ROWS;
DECLARE GLOBAL TEMPORARY Table temp_AGREEMENT_ REQUEST_CROSS_R EF LIKE AGREEMENT_REQUE ST_CROSS_REF ON COMMIT DELETE ROWS;
DECLARE GLOBAL TEMPORARY Table temp_AGREEMENT_ REQUEST LIKE AGREEMENT_REQUE ST ON COMMIT DELETE ROWS;
DECLARE GLOBAL TEMPORARY Table temp_FINANCIAL_ MOVEMENT_REQUES T LIKE FINANCIAL_MOVEM ENT_REQUEST ON COMMIT DELETE ROWS;
DECLARE GLOBAL TEMPORARY Table temp_AGREEMENT_ REQUEST_RLSHP LIKE AGREEMENT_REQUE ST_RLSHP ON COMMIT DELETE ROWS;
DECLARE GLOBAL TEMPORARY Table temp_VENDOR_REM ITTANCE_WORK_FI LE LIKE VENDOR_REMITTAN CE_WORK_FILE ON COMMIT DELETE ROWS;
DECLARE GLOBAL TEMPORARY Table temp_ALLOCATION _COMPONENTs
(AGREEMENT_ID INTEGER, AMOUNT DECIMAL (13, 2) ) ON COMMIT DELETE ROWS;
SET V_REQUEST_DATE = DATE(P_LAST_MOD IFIED_TIMESTAMP );
SET V_PLAN_ID = CHAR(P_PLAN_ID) ;
SELECT MASTER_PlAN_NUM ,PLAN_NUM,PAYRO LL_GROUP_NUM,PA YROLL_DT,TO_CHA R(FILE_DATE,'YY YY-MM-DD HH24:MI:SS')
|| '.' || CHAR(MICROSECON D(FILE_DATE)),
REMITTANCE_AMT_ IN,STATUS,OWNER ,LAST_MODIFIED_ USER_ID,LAST_MO DIFIED_TS
INTO V_MASTER_PLAN_N UM, V_PLAN_NUM, V_PAYROLL_GROUP _NUM, V_PAYROLL_DT,
V_FILE_DATE, V_TOTAL_AMOUNT, V_STATUS, V_OWNER, P_LAST_MODIFIED _USER_ID,
P_LAST_MODIFIED _TIMESTAMP
FROM GCRR.PLAN_REMIT TANCE_WORK_FILE
WHERE FILE_UID = P_FILE_UID WITH UR;
SELECT TYPE_ID INTO V_PSR_TYPE_ID
FROM GCRR.TYPE
WHERE TYPE_NM = 'Common remitter payroll submission request' WITH UR;
SELECT TYPE_ID INTO V_ARC_TYPE_ID
FROM GCRR.TYPE
WHERE TYPE_NM = 'Agreement request composition' WITH UR;
SELECT TYPE_ID INTO V_ARC_IND_TYPE_ ID
FROM GCRR.TYPE
WHERE TYPE_NM = 'Agreement request generation' WITH UR;
SELECT TYPE_ID INTO V_LOAN_REQUEST_ TYPE_ID
FROM GCRR.TYPE WHERE TYPE_NM = 'Loan repayment request' WITH UR;
SELECT TYPE_ID INTO V_LOAN_COMPONEN T_TYPE_ID
FROM GCRR.TYPE WHERE TYPE_NM = 'Loan Component' WITH UR;
SELECT TYPE_ID INTO V_PREMIUM_CONTR _TYPE_ID
FROM GCRR.TYPE
WHERE TYPE_NM = 'Premium contribution request' WITH UR;
SELECT TYPE_ID INTO V_PERSON_TYPE_I D
FROM GCRR.TYPE
WHERE TYPE_NM = 'Person' WITH UR;
SELECT CODE_ID INTO V_CODE_ID
FROM GCRR.CODE WHERE CODE_VALUE_TXT = 'RCVD'
AND CODE_SCHEME_ID = (SELECT CODE_ID FROM GCRR.CODE
WHERE LABEL_TXT = 'Request status code'
AND TYPE_ID= (SELECT TYPE_ID FROM GCRR.TYPE WHERE TYPE_NM = 'Code scheme'))
WITH UR;
Select TYPE_ID INTO V_AAC_TYPE_ID from GCRR.TYPE
Where TYPE_NM like 'Asset Accumulation Component' WITH UR;
Select AGREEMENT_REQUE ST_ID INTO V_PSR_ID
FROM GCRR.AGREEMENT_ REQUEST_CROSS_R EF a
Where AGREEMENT_REQUE ST_TYPE_ID = V_PSR_TYPE_ID
AND LOAD_PROCESS_KE Y_PART_01= V_PLAN_ID
AND LOAD_PROCESS_KE Y_PART_02= P_LOCATION_CODE
AND LOAD_PROCESS_KE Y_PART_03 = V_FILE_DATE
AND DELETE_TS IS NULL WITH UR;
SELECT COUNT(ALLOCATIO N_ERROR) INTO V_COUNT
FROM GCRR.PARTICIPAN T_REMITTANCE pr,
GCRR.ALLOCATION _CHANGE_DATA a
WHERE a.PARTICIPANT_N UM = pr.PARTICIPANT_ NUM
AND a.PLAN_NUM = V_PLAN_NUM
AND a.PAYROLL_GROUP _NUM = V_PAYROLL_GROUP _NUM
AND a.ACTIVE = 'Y'
AND a.ALLOCATION_ER ROR = 'Y'
AND INSTRUCTION_UID = P_FILE_UID
WITH UR;
IF V_COUNT > 0 THEN
SIGNAL SQLSTATE '38001' SET MESSAGE_TEXT ='Unable to Process Contribution File, Participant with Allocation Errors.';
END IF;
EACH_CONTR: FOR EACH_SOURCE AS
(SELECT PARTICIPANT_NUM , MONEY_SOURCE, AMOUNT, LOAN_NUM
FROM GCRR.PARTICIPAN T_REMITTANCE
WHERE INSTRUCTION_UID = P_FILE_UID
AND DELETED_FL = 'N') WITH UR
DO
SET V_PARTICIPANT_N UM = EACH_SOURCE.PAR TICIPANT_NUM;
SET V_MONEY_SRC = EACH_SOURCE. MONEY_SOURCE;
SET V_CONTR_AMT = EACH_SOURCE. AMOUNT;
SET V_LOAN_ID = EACH_SOURCE. LOAN_NUM;
SET V_MONEY_SRC = UPPER(V_MONEY_S RC);
SELECT CHAR(CODE_ID) INTO V_MONEY_SRC_COD E
FROM GCRR.CODE WHERE CODE_VALUE_TXT = V_MONEY_SRC
AND CODE_SCHEME_ID IN (SELECT CODE_ID
FROM GCRR.CODE
WHERE LABEL_TXT = 'Money_Source_c d')
WITH UR;
SELECT BUSINESS_KEY_PA RT_01 INTO V_SSN
FROM GCRR.ROLE_PLAYE R_CROSS_REF r
WHERE R.ROLE_PLAYER_T YPE_ID = V_PERSON_TYPE_I D
AND SOURCE_SYSTEM_N M = C_SOURCE_SYSTEM _NM
AND LOAD_PROCESS_KE Y_PART_01 = V_PARTICIPANT_N UM
AND DELETE_TS is NULL WITH UR;
SET at_end = 0;
If UPPER(V_MONEY_S RC) = 'LP'
THEN
SET more_one = 0;
SELECT AGREEMENT_ID
INTO V_LCA_ID
FROM GCRR.AGREEMENT_ CROSS_REF A
WHERE A.LOAD_PROCESS_ KEY_PART_02= V_LOAN_ID
AND A.LOAD_PROCESS_ KEY_PART_03= V_PLAN_ID
AND A.LOAD_PROCESS_ KEY_PART_04= P_LOCATION_CODE
AND A.LOAD_PROCESS_ KEY_PART_06= V_PARTICIPANT_N UM
AND A.SOURCE_SYSTEM _NM = C_SOURCE_SYSTEM _NM
AND A.AGREEMENT_TYP E_ID = V_LOAN_COMPONEN T_TYPE_ID
AND A.DELETE_TS is null WITH UR;
IF (at_end = 1) THEN
SIGNAL SQLSTATE '38001' SET MESSAGE_TEXT ='Unable to Process Contribution File, Invalid Loan Number.';
END IF;
IF (more_one = 1) THEN
SIGNAL SQLSTATE '38001' SET MESSAGE_TEXT = 'Unable to Process Contribution File, Loan Agreement is duplicate.' ;
END IF;
INSERT INTO SESSION.temp_VE NDOR_REMITTANCE _WORK_FILE
(VENDOR_NUM, PARTICIPANT_NUM , PARTICIPANT_SSN ,PLAN_NUM, PRODUCT_NUM, MONEY_SOURCE,
VENDOR_CONTRACT _NUM, REMITTANCE_AMT, UID, STATUS,LAST_MOD IFIED_TS,
CREATION_TS,CRE ATION_USER_ID,L AST_MODIFIED_US ER_ID,LOAN_NUM)
VALUES (V_VENDOR_NUM,V _PARTICIPANT_NU M,V_SSN,V_PLAN_ NUM, '', V_MONEY_SRC, V_CONTRACT_NUMB ER, V_CONTR_AMT,
P_FILE_UID,'FP' ,P_LAST_MODIFIE D_TIMESTAMP,P_L AST_MODIFIED_TI MESTAMP,P_LAST_ MODIFIED_USER_I D,
P_LAST_MODIFIED _USER_ID,V_LOAN _ID);
Select AGREEMENT_REQUE ST_ID INTO V_LOAN_REQUEST_ ID
from SESSION.temp_AG REEMENT_REQUEST _CROSS_REF a
Where AGREEMENT_REQUE ST_TYPE_ID = V_LOAN_REQUEST_ TYPE_ID
And LOAD_PROCESS_KE Y_PART_01= V_PLAN_ID
And LOAD_PROCESS_KE Y_PART_02= P_LOCATION_CODE
And LOAD_PROCESS_KE Y_PART_03 = V_FILE_DATE
AND LOAD_PROCESS_KE Y_PART_04 = V_PARTICIPANT_N UM
AND LOAD_PROCESS_KE Y_PART_06 = V_LOAN_ID
AND A.DELETE_TS is null WITH UR;
IF (at_end = 1) THEN
SELECT NEXTVAL FOR GCRR.AGREEMENT_ REQUEST_CROSS_R EFSEQ INTO V_LOAN_REQUEST_ ID
FROM SYSIBM.SYSDUMMY 1 s;
INSERT INTO SESSION.temp_AG REEMENT_REQUEST _CROSS_REF (AGREEMENT_REQU EST_ID,
SOURCE_SYSTEM_N M,AGREEMENT_REQ UEST_TYPE_ID,
LOAD_PROCESS_KE Y_PART_01,LOAD_ PROCESS_KEY_PAR T_02,
LOAD_PROCESS_KE Y_PART_03,LOAD_ PROCESS_KEY_PAR T_04,
LOAD_PROCESS_KE Y_PART_05,LOAD_ PROCESS_KEY_PAR T_06,
LOAD_PROCESS_KE Y_PART_07,LOAD_ PROCESS_COMPOSI TE_KEY,
BUSINESS_KEY_PA RT_01,BUSINESS_ KEY_PART_02,
BUSINESS_KEY_PA RT_03,BUSINESS_ KEY_PART_04,
BUSINESS_KEY_PA RT_05,BUSINESS_ KEY_PART_06,
LOAD_TS,CREATIO N_TS,LAST_MODIF IED_TS)
VALUES
(V_LOAN_REQUEST _ID, C_SOURCE_SYSTEM _NM, V_LOAN_REQUEST_ TYPE_ID,
V_PLAN_ID, P_LOCATION_CODE , V_FILE_DATE
, V_PARTICIPANT_N UM, V_CONTRACT_NUMB ER, V_LOAN_ID,V_REC ORDKEEPER_ID,
V_PLAN_ID||'~'| |P_LOCATION_COD E||'~'|| V_FILE_DATE||'~ '|| V_PARTICIPANT_N UM ||'~'|| V_CONTRACT_NUMB ER
||'~'|| V_LOAN_ID,
V_PAYROLL_GROUP _NUM, V_FILE_DATE, V_PARTICIPANT_N UM,
V_CONTRACT_NUMB ER, V_LOAN_ID, V_RECORDKEEPER_ ID,
P_LAST_MODIFIED _TIMESTAMP, P_LAST_MODIFIED _TIMESTAMP,
P_LAST_MODIFIED _TIMESTAMP);
INSERT INTO SESSION.temp_AG REEMENT_REQUEST (AGREEMENT_REQU EST_ID,TYPE_ID, AGREEMENT_ID,RE QUEST_TS,SOURCE _SYSTEM_TXT,DEL ETED_FL, CREATION_USER_I D,LOAD_TS,LAST_ MODIFIED_USER_I D, CREATION_TS,LAS T_MODIFIED_TS,R EQUEST_STATUS_C D,REQUEST_STATU S_DT)
VALUES (V_LOAN_REQUEST _ID, V_LOAN_REQUEST_ TYPE_ID, V_LCA_ID, V_FILE_DATE, C_SOURCE_SYSTEM _NM, 'N', P_LAST_MODIFIED _USER_ID, P_LAST_MODIFIED _TIMESTAMP, P_LAST_MODIFIED _USER_ID, P_LAST_MODIFIED _TIMESTAMP, P_LAST_MODIFIED _TIMESTAMP, V_CODE_ID, V_REQUEST_DATE) ;
INSERT INTO SESSION.temp_FI NANCIAL_MOVEMEN T_REQUEST (
AGREEMENT_REQUE ST_ID, TYPE_ID, Request_amt,
PAYROLL_PERIOD_ END_DT, SOURCE_SYSTEM_T XT, DELETED_FL,
CREATION_USER_I D, CREATION_TS, LAST_MODIFIED_U SER_ID, LAST_MODIFIED_T S)
VALUES (V_LOAN_REQUEST _ID, V_LOAN_REQUEST_ TYPE_ID, V_CONTR_AMT,
V_PAYROLL_DT, C_SOURCE_SYSTEM _NM, 'N',
P_LAST_MODIFIED _USER_ID, P_LAST_MODIFIED _TIMESTAMP, P_LAST_MODIFIED _USER_ID,
P_LAST_MODIFIED _TIMESTAMP);
INSERT INTO SESSION.temp_AG REEMENT_REQUEST _RLSHP(AGREEMEN T_REQUEST_RLSHP _ID,LEFT_AGREEM ENT_REQUEST_ID, SOURCE_SYSTEM_T XT,RIGHT_AGREEM ENT_REQUEST_ID, NATURE_ID,START _TS,RIGHT_AGREE MENT_RQST_TYPE_ ID,LEFT_AGREEME NT_REQUEST_TYPE _ID,DELETED_FL, CREATION_USER_I D,LOAD_TS,LAST_ MODIFIED_USER_I D,CREATION_TS,L AST_MODIFIED_TS )
VALUES (1,V_PSR_ID, C_SOURCE_SYSTEM _NM, V_LOAN_REQUEST_ ID, V_ARC_TYPE_ID, P_LAST_MODIFIED _TIMESTAMP, V_LOAN_REQUEST_ TYPE_ID, V_PSR_TYPE_ID, 'N', P_LAST_MODIFIED _USER_ID, P_LAST_MODIFIED _TIMESTAMP, P_LAST_MODIFIED _USER_ID, P_LAST_MODIFIED _TIMESTAMP, P_LAST_MODIFIED _TIMESTAMP);
INSERT INTO SESSION.temp_AL LOCATION_COMPON ENTs VALUES (V_LCA_ID,V_CON TR_AMT);
SET at_end = 0;
END IF;
ELSE
SET at_end = 0;
Select AGREEMENT_ID INTO V_AAC_AGGREEGAT E_ID
from GCRR.AGREEMENT_ CROSS_REF a
Where AGREEMENT_TYPE_ ID = V_AAC_TYPE_ID
And SOURCE_SYSTEM_N M = C_SOURCE_SYSTEM _NM
And LOAD_PROCESS_KE Y_PART_01= V_PLAN_ID
And load_process_ke y_part_02= P_LOCATION_CODE
And load_process_ke y_part_03= V_PARTICIPANT_N UM
And load_process_ke y_part_04 =V_MONEY_SRC_CO DE
AND DELETE_TS IS NULL WITH UR;
IF (at_end = 1) THEN
SIGNAL SQLSTATE '38001' SET MESSAGE_TEXT ='Unable to Process Contribution File, Asset Accumulation Component does not exists.' ;
END IF;
Select AGREEMENT_REQUE ST_ID INTO V_PCR_AGGREGATE _ID
from SESSION.temp_AG REEMENT_REQUEST _CROSS_REF a
Where AGREEMENT_REQUE ST_TYPE_ID = V_PREMIUM_CONTR _TYPE_ID
AND LOAD_PROCESS_KE Y_PART_01 = V_PLAN_ID
AND LOAD_PROCESS_KE Y_PART_02 = P_LOCATION_CODE
AND LOAD_PROCESS_KE Y_PART_03 = V_FILE_DATE
AND LOAD_PROCESS_KE Y_PART_04 = V_PARTICIPANT_N UM
AND LOAD_PROCESS_KE Y_PART_05 = V_MONEY_SRC_COD E
AND DELETE_TS IS NULL WITH UR;
IF (at_end = 1) THEN
SELECT NEXTVAL FOR GCRR.AGREEMENT_ REQUEST_CROSS_R EFSEQ INTO V_PCR_AGGREGATE _ID
FROM SYSIBM.SYSDUMMY 1 s;
INSERT INTO SESSION.temp_AG REEMENT_REQUEST _CROSS_REF (AGREEMENT_REQU EST_ID, SOURCE_SYSTEM_N M,
AGREEMENT_REQUE ST_TYPE_ID, LOAD_PROCESS_KE Y_PART_01, LOAD_PROCESS_KE Y_PART_02,
LOAD_PROCESS_KE Y_PART_03, LOAD_PROCESS_KE Y_PART_04, LOAD_PROCESS_KE Y_PART_05,
LOAD_PROCESS_CO MPOSITE_KEY,
BUSINESS_KEY_PA RT_01, BUSINESS_KEY_PA RT_02,
BUSINESS_KEY_PA RT_03, BUSINESS_KEY_PA RT_04,
LOAD_TS, CREATION_TS, LAST_MODIFIED_T S)
VALUES
(V_PCR_AGGREGAT E_ID, C_SOURCE_SYSTEM _NM, V_PREMIUM_CONTR _TYPE_ID,
RTRIM(V_PLAN_ID ), P_LOCATION_CODE , V_FILE_DATE, RTRIM(V_PARTICI PANT_NUM),
V_MONEY_SRC_COD E, RTRIM(V_PLAN_ID )||'~'||P_LOCAT ION_CODE||'~'|| V_FILE_DATE||'~ '|| RTRIM(V_PARTICI PANT_NUM) ||'~'|| V_MONEY_SRC_COD E,
V_PAYROLL_GROUP _NUM, V_FILE_DATE, V_PARTICIPANT_N UM, V_MONEY_SRC_COD E, P_LAST_MODIFIED _TIMESTAMP,
P_LAST_MODIFIED _TIMESTAMP, P_LAST_MODIFIED _TIMESTAMP);
INSERT INTO SESSION.temp_AG REEMENT_REQUEST (AGREEMENT_REQU EST_ID, TYPE_ID, AGREEMENT_ID,
REQUEST_TS, SOURCE_SYSTEM_T XT, DELETED_FL,CREA TION_USER_ID, LOAD_TS, LAST_MODIFIED_U SER_ID,
CREATION_TS, LAST_MODIFIED_T S, REQUEST_STATUS_ CD, REQUEST_STATUS_ DT)
VALUES (V_PCR_AGGREGAT E_ID, V_PREMIUM_CONTR _TYPE_ID, V_AAC_AGGREEGAT E_ID,
V_FILE_DATE, C_SOURCE_SYSTEM _NM, 'N', P_LAST_MODIFIED _USER_ID, P_LAST_MODIFIED _TIMESTAMP,
P_LAST_MODIFIED _USER_ID, P_LAST_MODIFIED _TIMESTAMP, P_LAST_MODIFIED _TIMESTAMP, V_CODE_ID, V_REQUEST_DATE) ;
INSERT INTO SESSION.temp_FI NANCIAL_MOVEMEN T_REQUEST (
AGREEMENT_REQUE ST_ID, TYPE_ID, Request_amt,
EXPECTED_DEFERR AL_AMT, PAYROLL_PERIOD_ END_DT, SOURCE_SYSTEM_T XT, DELETED_FL,
CREATION_USER_I D, CREATION_TS, LAST_MODIFIED_U SER_ID, LAST_MODIFIED_T S)
VALUES (V_PCR_AGGREGAT E_ID, V_PREMIUM_CONTR _TYPE_ID, V_CONTR_AMT,
V_CONTR_AMT, V_PAYROLL_DT, C_SOURCE_SYSTEM _NM, 'N',
P_LAST_MODIFIED _USER_ID, P_LAST_MODIFIED _TIMESTAMP,
P_LAST_MODIFIED _USER_ID, P_LAST_MODIFIED _TIMESTAMP);
INSERT INTO SESSION.temp_AG REEMENT_REQUEST _RLSHP(AGREEMEN T_REQUEST_RLSHP _ID,LEFT_AGREEM ENT_REQUEST_ID, SOURCE_SYSTEM_T XT,
RIGHT_AGREEMENT _REQUEST_ID,NAT URE_ID,START_TS ,RIGHT_AGREEMEN T_RQST_TYPE_ID,
LEFT_AGREEMENT_ REQUEST_TYPE_ID ,DELETED_FL,CRE ATION_USER_ID,L OAD_TS,LAST_MOD IFIED_USER_ID,
CREATION_TS,LAS T_MODIFIED_TS)
VALUES (1, V_PSR_ID, C_SOURCE_SYSTEM _NM, V_PCR_AGGREGATE _ID,
V_ARC_TYPE_ID, P_LAST_MODIFIED _TIMESTAMP,
V_PREMIUM_CONTR _TYPE_ID, V_PSR_TYPE_ID, 'N',
P_LAST_MODIFIED _USER_ID, P_LAST_MODIFIED _TIMESTAMP,
P_LAST_MODIFIED _USER_ID, P_LAST_MODIFIED _TIMESTAMP,
P_LAST_MODIFIED _TIMESTAMP);
INSERT INTO SESSION.temp_AL LOCATION_COMPON ENTs VALUES (V_AAC_AGGREEGA TE_ID,V_CONTR_A MT);
SET at_end = 0;
END IF;
DELETE FROM SESSION.TEMP_AL LOCATIONS;
INSERT INTO SESSION.TEMP_AL LOCATIONS
(SELECT AGREEMENT_ID, BUSINESS_KEY_PA RT_05, LOAD_PROCESS_KE Y_PART_05,
BUSINESS_KEY_PA RT_07, LOAD_PROCESS_KE Y_PART_07,
LOAD_PROCESS_KE Y_PART_01, ar.ALLOCATION_A MT, ar.ALLOCATION_P CT,ar.PRIORITY_ SEQUENCE_NBR
FROM GCRR.AGREEMENT_ CROSS_REF a, GCRR.AGREEMENT_ RLSHP ar
WHERE a.AGREEMENT_ID = ar.RIGHT_AGREEM ENT_ID
AND AGREEMENT_TYPE_ ID = V_AAC_TYPE_ID
AND SOURCE_SYSTEM_N M = C_SOURCE_SYSTEM _NM
AND LOAD_PROCESS_KE Y_PART_02 = V_MONEY_SRC_COD E
AND LOAD_PROCESS_KE Y_PART_03 = V_PLAN_ID
AND LOAD_PROCESS_KE Y_PART_04 = P_LOCATION_CODE
AND LOAD_PROCESS_KE Y_PART_06 =V_PARTICIPANT_ NUM
AND DELETE_TS is null
AND DELETEd_FL = 'N' ) WITH UR;
SELECT COUNT(1) INTO V_UO_AMT_COUNT
FROM SESSION.TEMP_AL LOCATIONS
WHERE PRIORITY = 0 AND AMOUNT > 0 WITH UR;
SELECT COUNT(1) INTO V_O_AMT_COUNT
FROM SESSION.TEMP_AL LOCATIONS
WHERE PRIORITY > 0 AND AMOUNT > 0 WITH UR;
SELECT COUNT(1) INTO V_PCT_COUNT
FROM SESSION.TEMP_AL LOCATIONS
WHERE PERCENT > 0 WITH UR;
IF ( ( V_UO_AMT_COUNT > 0 ) AND ( V_O_AMT_COUNT > 0 ) ) OR
( V_PCT_COUNT = 0
AND ( V_UO_AMT_COUNT > 0 )
AND (SELECT SUM(AMOUNT) FROM SESSION.TEMP_AL LOCATIONS WHERE PRIORITY = 0 ) > V_CONTR_AMT)
THEN
SIGNAL SQLSTATE '38001'
SET MESSAGE_TEXT ='Unable to Process Contribution File, Invalid Allocations.' ;
END IF;
SET V_INDEX = 1;
SET V_PCT_AGG_AMT = 0;
EACH_ALLOC: FOR EACH_ALLOC AS SELECT * FROM SESSION.TEMP_AL LOCATIONS
ORDER BY PERCENT ASC, PRIORITY ASC WITH UR
DO
SET V_AAC_INDIVIDUA L_ID = EACH_ALLOC.AGRE EMENT_ID;
SET V_VENDOR_NUM = EACH_ALLOC.VEND OR_NUM;
SET V_RECORDKEEPER_ ID = EACH_ALLOC.VEND OR_ID;
SET V_CONTRACT_NUMB ER = EACH_ALLOC.CONT RACT_NUMBER;
SET V_PRODUCT_NUM = EACH_ALLOC.PROD UCT_NUM;
SET V_PRODUCT_ID = EACH_ALLOC.PROD UCT_ID;
SET V_AMOUNT = EACH_ALLOC.AMOU NT;
SET V_PERCENT = EACH_ALLOC.PERC ENT;
SET V_CAL_AMOUNT = 0;
IF V_AMOUNT > 0 THEN
IF ( V_AMOUNT >= V_CONTR_AMT ) THEN
SET V_CAL_AMOUNT = V_CONTR_AMT;
SET V_CONTR_AMT = 0;
ELSE
SET V_CAL_AMOUNT = V_AMOUNT;
SET V_CONTR_AMT = V_CONTR_AMT - V_AMOUNT;
END IF;
ELSE
IF V_PCT_COUNT = V_INDEX
THEN
SET V_CAL_AMOUNT = V_CONTR_AMT - V_PCT_AGG_AMT;
ELSE
SET V_INDEX = V_INDEX + 1;
SET V_CAL_AMOUNT = (V_CONTR_AMT * V_PERCENT) / 100;
SET V_PCT_AGG_AMT = V_PCT_AGG_AMT + V_CAL_AMOUNT;
END IF;
END IF;
INSERT INTO SESSION.temp_VE NDOR_REMITTANCE _WORK_FILE
(VENDOR_NUM, PARTICIPANT_NUM , PARTICIPANT_SSN ,PLAN_NUM, PRODUCT_NUM, MONEY_SOURCE,
VENDOR_CONTRACT _NUM, REMITTANCE_AMT, UID, STATUS,LAST_MOD IFIED_TS,
CREATION_TS,CRE ATION_USER_ID,L AST_MODIFIED_US ER_ID)
VALUES (V_VENDOR_NUM,V _PARTICIPANT_NU M,V_SSN,V_PLAN_ NUM, V_PRODUCT_NUM, V_MONEY_SRC, V_CONTRACT_NUMB ER, V_CAL_AMOUNT,
P_FILE_UID,'FP' ,P_LAST_MODIFIE D_TIMESTAMP,P_L AST_MODIFIED_TI MESTAMP,P_LAST_ MODIFIED_USER_I D,P_LAST_MODIFI ED_USER_ID);
SET at_end = 0;
Select AGREEMENT_REQUE ST_ID INTO V_PCR_INDIVIDUA L_ID
from SESSION.temp_AG REEMENT_REQUEST _CROSS_REF a
Where AGREEMENT_REQUE ST_TYPE_ID = V_PREMIUM_CONTR _TYPE_ID
AND LOAD_PROCESS_KE Y_PART_01 = V_PLAN_ID
AND LOAD_PROCESS_KE Y_PART_02 = P_LOCATION_CODE
AND LOAD_PROCESS_KE Y_PART_03 = V_FILE_DATE
AND LOAD_PROCESS_KE Y_PART_04 = V_PARTICIPANT_N UM
AND LOAD_PROCESS_KE Y_PART_05 = V_CONTRACT_NUMB ER
AND LOAD_PROCESS_KE Y_PART_06 = V_MONEY_SRC_COD E
AND LOAD_PROCESS_KE Y_PART_07 = V_RECORDKEEPER_ ID
AND DELETE_TS IS NULL;
IF (at_end = 1) THEN
SELECT NEXTVAL FOR GCRR.AGREEMENT_ REQUEST_CROSS_R EFSEQ INTO V_PCR_INDIVIDUA L_ID
FROM SYSIBM.SYSDUMMY 1 s;
INSERT INTO SESSION.temp_AG REEMENT_REQUEST _CROSS_REF (AGREEMENT_REQU EST_ID, SOURCE_SYSTEM_N M,
AGREEMENT_REQUE ST_TYPE_ID, LOAD_PROCESS_KE Y_PART_01, LOAD_PROCESS_KE Y_PART_02,
LOAD_PROCESS_KE Y_PART_03, LOAD_PROCESS_KE Y_PART_04, LOAD_PROCESS_KE Y_PART_05,
LOAD_PROCESS_KE Y_PART_06, LOAD_PROCESS_KE Y_PART_07,
LOAD_PROCESS_CO MPOSITE_KEY,
BUSINESS_KEY_PA RT_01, BUSINESS_KEY_PA RT_02,
BUSINESS_KEY_PA RT_03, BUSINESS_KEY_PA RT_04,
BUSINESS_KEY_PA RT_05, BUSINESS_KEY_PA RT_06,
LOAD_TS, CREATION_TS, LAST_MODIFIED_T S)
VALUES
(V_PCR_INDIVIDU AL_ID, C_SOURCE_SYSTEM _NM, V_PREMIUM_CONTR _TYPE_ID,
RTRIM(V_PLAN_ID ), P_LOCATION_CODE , V_FILE_DATE, RTRIM(V_PARTICI PANT_NUM),
V_CONTRACT_NUMB ER, V_MONEY_SRC_COD E,V_RECORDKEEPE R_ID,
RTRIM(V_PLAN_ID )||'~'||P_LOCAT ION_CODE||'~'|| V_FILE_DATE||'~ '|| RTRIM(V_PARTICI PANT_NUM) ||'~'|| V_CONTRACT_NUMB ER ||'~'|| V_MONEY_SRC_COD E,
V_PAYROLL_GROUP _NUM, V_FILE_DATE, V_PARTICIPANT_N UM, V_CONTRACT_NUMB ER, V_MONEY_SRC_COD E,V_VENDOR_NUM,
P_LAST_MODIFIED _TIMESTAMP, P_LAST_MODIFIED _TIMESTAMP, P_LAST_MODIFIED _TIMESTAMP);
INSERT INTO SESSION.temp_AG REEMENT_REQUEST (AGREEMENT_REQU EST_ID, TYPE_ID, AGREEMENT_ID,
REQUEST_TS, SOURCE_SYSTEM_T XT, DELETED_FL,CREA TION_USER_ID, LOAD_TS, LAST_MODIFIED_U SER_ID,
CREATION_TS, LAST_MODIFIED_T S, REQUEST_STATUS_ CD, REQUEST_STATUS_ DT)
VALUES (V_PCR_INDIVIDU AL_ID, V_PREMIUM_CONTR _TYPE_ID, V_AAC_INDIVIDUA L_ID,
V_FILE_DATE, C_SOURCE_SYSTEM _NM, 'N', P_LAST_MODIFIED _USER_ID, P_LAST_MODIFIED _TIMESTAMP,
P_LAST_MODIFIED _USER_ID, P_LAST_MODIFIED _TIMESTAMP, P_LAST_MODIFIED _TIMESTAMP, V_CODE_ID, V_REQUEST_DATE) ;
INSERT INTO SESSION.temp_FI NANCIAL_MOVEMEN T_REQUEST (
AGREEMENT_REQUE ST_ID, TYPE_ID, Request_amt,
EXPECTED_DEFERR AL_AMT, PAYROLL_PERIOD_ END_DT, SOURCE_SYSTEM_T XT, DELETED_FL,
CREATION_USER_I D, CREATION_TS, LAST_MODIFIED_U SER_ID, LAST_MODIFIED_T S)
VALUES (V_PCR_INDIVIDU AL_ID, V_PREMIUM_CONTR _TYPE_ID, V_CAL_AMOUNT,
V_CAL_AMOUNT, V_PAYROLL_DT, C_SOURCE_SYSTEM _NM, 'N',
P_LAST_MODIFIED _USER_ID, P_LAST_MODIFIED _TIMESTAMP,
P_LAST_MODIFIED _USER_ID, P_LAST_MODIFIED _TIMESTAMP);
INSERT INTO SESSION.temp_AG REEMENT_REQUEST _RLSHP(AGREEMEN T_REQUEST_RLSHP _ID,LEFT_AGREEM ENT_REQUEST_ID, SOURCE_SYSTEM_T XT,
RIGHT_AGREEMENT _REQUEST_ID,NAT URE_ID,START_TS ,RIGHT_AGREEMEN T_RQST_TYPE_ID,
LEFT_AGREEMENT_ REQUEST_TYPE_ID ,DELETED_FL,CRE ATION_USER_ID,L OAD_TS,LAST_MOD IFIED_USER_ID,
CREATION_TS,LAS T_MODIFIED_TS)
VALUES (1,V_PCR_AGGREG ATE_ID, C_SOURCE_SYSTEM _NM, V_PCR_INDIVIDUA L_ID,
V_ARC_IND_TYPE_ ID, P_LAST_MODIFIED _TIMESTAMP,
V_PREMIUM_CONTR _TYPE_ID, V_PSR_TYPE_ID, 'N',
P_LAST_MODIFIED _USER_ID, P_LAST_MODIFIED _TIMESTAMP,
P_LAST_MODIFIED _USER_ID, P_LAST_MODIFIED _TIMESTAMP,
P_LAST_MODIFIED _TIMESTAMP);
INSERT INTO SESSION.temp_AL LOCATION_COMPON ENTs VALUES (V_AAC_INDIVIDU AL_ID,V_CAL_AMO UNT);
SET at_end = 0;
END IF;
END FOR EACH_ALLOC;
IF V_CONTR_AMT > 0 AND V_PCT_COUNT = 0 THEN
UPDATE SESSION.temp_VE NDOR_REMITTANCE _WORK_FILE
SET REMITTANCE_AMT = REMITTANCE_AMT + V_CONTR_AMT
WHERE (V_VENDOR_NUM, VENDOR_CONTRACT _NUM, PRODUCT_NUM)
in (SELECT VENDOR_NUM, CONTRACT_NUMBER , PRODUCT_NUM
FROM SESSION.TEMP_AL LOCATIONS
WHERE PRIORITY = (SELECT MIN(PRIORITY) FROM SESSION.TEMP_AL LOCATIONS))
AND MONEY_SOURCE = V_MONEY_SRC
AND PARTICIPANT_NUM = PARTICIPANT_NUM ;
UPDATE SESSION.temp_AL LOCATION_COMPON ENTs
SET AMOUNT = AMOUNT + V_CONTR_AMT
WHERE AGREEMENT_ID = ( SELECT AGREEMENT_ID
FROM SESSION.TEMP_AL LOCATIONS
WHERE PRIORITY = (SELECT MIN(PRIORITY) FROM SESSION.TEMP_AL LOCATIONS));
END IF;
END IF;
END FOR;
SET at_end = 0;
SELECT COUNT(1) INTO V_INDEX
FROM SESSION.temp_VE NDOR_REMITTANCE _WORK_FILE v
WHERE UID = P_FILE_UID
GROUP BY vENdor_num
HAVING SUM(REMITTANCE_ AMT) < 0;
IF at_end = 0 THEN
INSERT INTO GCRR.INSTRUCTIO N_FILE_ERRORS(E RROR_CODE, FILE_UID, PARTICIPANT_NUM ,
CREATION_TS, LAST_MODIFIED_T S, CREATION_USER_I D, LAST_MODIFIED_U SER_ID)
VALUES ('VNEG', P_FILE_UID, '', P_LAST_MODIFIED _TIMESTAMP, P_LAST_MODIFIED _TIMESTAMP, P_LAST_MODIFIED _USER_ID, P_LAST_MODIFIED _USER_ID);
UPDATE GCRR.PLAN_REMIT TANCE_WORK_FILE p
SET STATUS = 'FE'
where file_uid = P_FILE_UID;
ELSE
UPDATE GCRR.PLAN_REMIT TANCE_WORK_FILE p
SET STATUS = 'FP'
where file_uid = P_FILE_UID;
UPDATE GCRR.ASSET_ACCU MULATION_COMPON ENT A
SET CURRENT_YTD_CON TRIBUTION_AMT = CURRENT_YTD_CON TRIBUTION_AMT +
(SELECT AMOUNT FROM SESSION.temp_AL LOCATION_COMPON ENTs ta where a.AGREEMENT_ID = ta.AGREEMENT_ID ),
LAST_MODIFIED_U SER_ID = P_LAST_MODIFIED _USER_ID,
LAST_MODIFIED_T S = P_LAST_MODIFIED _TIMESTAMP;
UPDATE GCRR.LOAN_COMPO NENT A
SET CURRENT_YTD_REP AYMENT_AMT = CURRENT_YTD_REP AYMENT_AMT +
(SELECT AMOUNT FROM SESSION.temp_AL LOCATION_COMPON ENTs ta where a.AGREEMENT_ID = ta.AGREEMENT_ID ),
LAST_MODIFIED_U SER_ID = P_LAST_MODIFIED _USER_ID,
LAST_MODIFIED_T S = P_LAST_MODIFIED _TIMESTAMP ;
INSERT INTO GCRR.AGREEMENT_ REQUEST_CROSS_R EF (SELECT * FROM SESSION.TEMP_AG REEMENT_REQUEST _CROSS_REF);
INSERT INTO GCRR.AGREEMENT_ REQUEST (SELECT * FROM SESSION.TEMP_AG REEMENT_REQUEST );
INSERT INTO GCRR.FINANCIAL_ MOVEMENT_REQUES T (SELECT * FROM SESSION.TEMP_FI NANCIAL_MOVEMEN T_REQUEST);
INSERT INTO GCRR.AGREEMENT_ REQUEST_RLSHP(L EFT_AGREEMENT_R EQUEST_ID, SOURCE_SYSTEM_T XT,
RIGHT_AGREEMENT _REQUEST_ID, NATURE_ID, START_TS, RIGHT_AGREEMENT _RQST_TYPE_ID,
END_TS, LEFT_AGREEMENT_ REQUEST_TYPE_ID , DELETED_FL,
CREATION_USER_I D, LOAD_TS, LAST_MODIFIED_U SER_ID, CREATION_TS, LAST_MODIFIED_T S)
SELECT LEFT_AGREEMENT_ REQUEST_ID, SOURCE_SYSTEM_T XT,
RIGHT_AGREEMENT _REQUEST_ID, NATURE_ID, START_TS, RIGHT_AGREEMENT _RQST_TYPE_ID,
END_TS, LEFT_AGREEMENT_ REQUEST_TYPE_ID , DELETED_FL,
CREATION_USER_I D, LOAD_TS, LAST_MODIFIED_U SER_ID, CREATION_TS, LAST_MODIFIED_T S
FROM SESSION.temp_AG REEMENT_REQUEST _RLSHP;
INSERT INTO GCRR.VENDOR_REM ITTANCE_WORK_FI LE (SELECT * FROM SESSION.TEMP_VE NDOR_REMITTANCE _WORK_FILE);
END IF;
END
;