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.SQL070522121828900
;
CREATE PROCEDURE "GCRR"."CONTRIBUTION_ODS_ENTRIES"
(IN "P_FILE_UID" CHARACTER(8),
IN "P_PLAN_ID" VARCHAR(45),
IN "P_LOCATION_CODE" VARCHAR(45),
IN "P_LAST_MODIFIED_USER_ID" VARCHAR(25),
IN "P_LAST_MODIFIED_TIMESTAMP" TIMESTAMP
)
SPECIFIC "GCRR"."SQL070522121828900"
LANGUAGE SQL
NOT DETERMINISTIC
CALLED ON NULL INPUT
EXTERNAL ACTION
OLD SAVEPOINT LEVEL
MODIFIES SQL DATA
INHERIT SPECIAL REGISTERS
BEGIN
DECLARE V_MASTER_PLAN_NUM 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_COMPONENT_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_AGGREEGATE_ID INTEGER;
DECLARE V_AAC_INDIVIDUAL_ID INTEGER;
DECLARE V_PCR_INDIVIDUAL_ID INTEGER;
DECLARE V_PERSON_TYPE_ID INTEGER;
DECLARE V_COUNT INTEGER;
DECLARE V_PARTICIPANT_NUM VARCHAR (10);
DECLARE V_MONEY_SRC VARCHAR (20);
DECLARE V_MONEY_SRC_CODE 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_NUMBER 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_row 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_row set more_one = 1;
DECLARE GLOBAL TEMPORARY Table TEMP_ALLOCATIONS
(AGREEMENT_ID INTEGER,VENDOR_NUM VARCHAR(20), VENDOR_ID VARCHAR(20),
PRODUCT_NUM VARCHAR(20),PRODUCT_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_REF LIKE AGREEMENT_REQUEST_CROSS_REF ON COMMIT DELETE ROWS;
DECLARE GLOBAL TEMPORARY Table temp_AGREEMENT_REQUEST LIKE AGREEMENT_REQUEST ON COMMIT DELETE ROWS;
DECLARE GLOBAL TEMPORARY Table temp_FINANCIAL_MOVEMENT_REQUEST LIKE FINANCIAL_MOVEMENT_REQUEST ON COMMIT DELETE ROWS;
DECLARE GLOBAL TEMPORARY Table temp_AGREEMENT_REQUEST_RLSHP LIKE AGREEMENT_REQUEST_RLSHP ON COMMIT DELETE ROWS;
DECLARE GLOBAL TEMPORARY Table temp_VENDOR_REMITTANCE_WORK_FILE LIKE VENDOR_REMITTANCE_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_MODIFIED_TIMESTAMP);
SET V_PLAN_ID = CHAR(P_PLAN_ID);
SELECT MASTER_PlAN_NUM,PLAN_NUM,PAYROLL_GROUP_NUM,PAYROLL _DT,TO_CHAR(FILE_DATE,'YYYY-MM-DD HH24:MI:SS')
|| '.' || CHAR(MICROSECOND(FILE_DATE)),
REMITTANCE_AMT_IN,STATUS,OWNER,LAST_MODIFIED_USER_ ID,LAST_MODIFIED_TS
INTO V_MASTER_PLAN_NUM, 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_REMITTANCE_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_COMPONENT_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_ID
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_REQUEST_ID INTO V_PSR_ID
FROM GCRR.AGREEMENT_REQUEST_CROSS_REF a
Where AGREEMENT_REQUEST_TYPE_ID = V_PSR_TYPE_ID
AND LOAD_PROCESS_KEY_PART_01= V_PLAN_ID
AND LOAD_PROCESS_KEY_PART_02= P_LOCATION_CODE
AND LOAD_PROCESS_KEY_PART_03 = V_FILE_DATE
AND DELETE_TS IS NULL WITH UR;
SELECT COUNT(ALLOCATION_ERROR) INTO V_COUNT
FROM GCRR.PARTICIPANT_REMITTANCE pr,
GCRR.ALLOCATION_CHANGE_DATA a
WHERE a.PARTICIPANT_NUM = 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_ERROR = '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.PARTICIPANT_REMITTANCE
WHERE INSTRUCTION_UID = P_FILE_UID
AND DELETED_FL = 'N') WITH UR
DO
SET V_PARTICIPANT_NUM = EACH_SOURCE.PARTICIPANT_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_SRC);
SELECT CHAR(CODE_ID) INTO V_MONEY_SRC_CODE
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_cd')
WITH UR;
SELECT BUSINESS_KEY_PART_01 INTO V_SSN
FROM GCRR.ROLE_PLAYER_CROSS_REF r
WHERE R.ROLE_PLAYER_TYPE_ID = V_PERSON_TYPE_ID
AND SOURCE_SYSTEM_NM = C_SOURCE_SYSTEM_NM
AND LOAD_PROCESS_KEY_PART_01 = V_PARTICIPANT_NUM
AND DELETE_TS is NULL WITH UR;
SET at_end = 0;
If UPPER(V_MONEY_SRC) = '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_NUM
AND A.SOURCE_SYSTEM_NM = C_SOURCE_SYSTEM_NM
AND A.AGREEMENT_TYPE_ID = V_LOAN_COMPONENT_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_VENDOR_REMITTANCE_WORK_FILE
(VENDOR_NUM, PARTICIPANT_NUM, PARTICIPANT_SSN,PLAN_NUM, PRODUCT_NUM, MONEY_SOURCE,
VENDOR_CONTRACT_NUM, REMITTANCE_AMT, UID, STATUS,LAST_MODIFIED_TS,
CREATION_TS,CREATION_USER_ID,LAST_MODIFIED_USER_ID ,LOAN_NUM)
VALUES (V_VENDOR_NUM,V_PARTICIPANT_NUM,V_SSN,V_PLAN_NUM, '', V_MONEY_SRC, V_CONTRACT_NUMBER, V_CONTR_AMT,
P_FILE_UID,'FP',P_LAST_MODIFIED_TIMESTAMP,P_LAST_M ODIFIED_TIMESTAMP,P_LAST_MODIFIED_USER_ID,
P_LAST_MODIFIED_USER_ID,V_LOAN_ID);
Select AGREEMENT_REQUEST_ID INTO V_LOAN_REQUEST_ID
from SESSION.temp_AGREEMENT_REQUEST_CROSS_REF a
Where AGREEMENT_REQUEST_TYPE_ID = V_LOAN_REQUEST_TYPE_ID
And LOAD_PROCESS_KEY_PART_01= V_PLAN_ID
And LOAD_PROCESS_KEY_PART_02= P_LOCATION_CODE
And LOAD_PROCESS_KEY_PART_03 = V_FILE_DATE
AND LOAD_PROCESS_KEY_PART_04 = V_PARTICIPANT_NUM
AND LOAD_PROCESS_KEY_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_REFSEQ INTO V_LOAN_REQUEST_ID
FROM SYSIBM.SYSDUMMY1 s;
INSERT INTO SESSION.temp_AGREEMENT_REQUEST_CROSS_REF (AGREEMENT_REQUEST_ID,
SOURCE_SYSTEM_NM,AGREEMENT_REQUEST_TYPE_ID,
LOAD_PROCESS_KEY_PART_01,LOAD_PROCESS_KEY_PART_02,
LOAD_PROCESS_KEY_PART_03,LOAD_PROCESS_KEY_PART_04,
LOAD_PROCESS_KEY_PART_05,LOAD_PROCESS_KEY_PART_06,
LOAD_PROCESS_KEY_PART_07,LOAD_PROCESS_COMPOSITE_KE Y,
BUSINESS_KEY_PART_01,BUSINESS_KEY_PART_02,
BUSINESS_KEY_PART_03,BUSINESS_KEY_PART_04,
BUSINESS_KEY_PART_05,BUSINESS_KEY_PART_06,
LOAD_TS,CREATION_TS,LAST_MODIFIED_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_NUM, V_CONTRACT_NUMBER, V_LOAN_ID,V_RECORDKEEPER_ID,
V_PLAN_ID||'~'||P_LOCATION_CODE||'~'|| V_FILE_DATE||'~'|| V_PARTICIPANT_NUM ||'~'|| V_CONTRACT_NUMBER
||'~'|| V_LOAN_ID,
V_PAYROLL_GROUP_NUM, V_FILE_DATE, V_PARTICIPANT_NUM,
V_CONTRACT_NUMBER, V_LOAN_ID, V_RECORDKEEPER_ID,
P_LAST_MODIFIED_TIMESTAMP, P_LAST_MODIFIED_TIMESTAMP,
P_LAST_MODIFIED_TIMESTAMP);
INSERT INTO SESSION.temp_AGREEMENT_REQUEST(AGREEMENT_REQUEST_I D,TYPE_ID,AGREEMENT_ID,REQUEST_TS,SOURCE_SYSTEM_TX T,DELETED_FL, CREATION_USER_ID,LOAD_TS,LAST_MODIFIED_USER_ID, CREATION_TS,LAST_MODIFIED_TS,REQUEST_STATUS_CD,REQ UEST_STATUS_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_FINANCIAL_MOVEMENT_REQUEST (
AGREEMENT_REQUEST_ID, TYPE_ID, Request_amt,
PAYROLL_PERIOD_END_DT, SOURCE_SYSTEM_TXT, DELETED_FL,
CREATION_USER_ID, CREATION_TS, LAST_MODIFIED_USER_ID, LAST_MODIFIED_TS)
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_AGREEMENT_REQUEST_RLSHP(AGREEMENT_REQ UEST_RLSHP_ID,LEFT_AGREEMENT_REQUEST_ID,SOURCE_SYS TEM_TXT,RIGHT_AGREEMENT_REQUEST_ID,NATURE_ID,START _TS,RIGHT_AGREEMENT_RQST_TYPE_ID,LEFT_AGREEMENT_RE QUEST_TYPE_ID,DELETED_FL,CREATION_USER_ID,LOAD_TS, LAST_MODIFIED_USER_ID,CREATION_TS,LAST_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_ALLOCATION_COMPONENTs VALUES (V_LCA_ID,V_CONTR_AMT);
SET at_end = 0;
END IF;
ELSE
SET at_end = 0;
Select AGREEMENT_ID INTO V_AAC_AGGREEGATE_ID
from GCRR.AGREEMENT_CROSS_REF a
Where AGREEMENT_TYPE_ID = V_AAC_TYPE_ID
And SOURCE_SYSTEM_NM = C_SOURCE_SYSTEM_NM
And LOAD_PROCESS_KEY_PART_01= V_PLAN_ID
And load_process_key_part_02= P_LOCATION_CODE
And load_process_key_part_03= V_PARTICIPANT_NUM
And load_process_key_part_04 =V_MONEY_SRC_CODE
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_REQUEST_ID INTO V_PCR_AGGREGATE_ID
from SESSION.temp_AGREEMENT_REQUEST_CROSS_REF a
Where AGREEMENT_REQUEST_TYPE_ID = V_PREMIUM_CONTR_TYPE_ID
AND LOAD_PROCESS_KEY_PART_01 = V_PLAN_ID
AND LOAD_PROCESS_KEY_PART_02 = P_LOCATION_CODE
AND LOAD_PROCESS_KEY_PART_03 = V_FILE_DATE
AND LOAD_PROCESS_KEY_PART_04 = V_PARTICIPANT_NUM
AND LOAD_PROCESS_KEY_PART_05 = V_MONEY_SRC_CODE
AND DELETE_TS IS NULL WITH UR;
IF (at_end = 1) THEN
SELECT NEXTVAL FOR GCRR.AGREEMENT_REQUEST_CROSS_REFSEQ INTO V_PCR_AGGREGATE_ID
FROM SYSIBM.SYSDUMMY1 s;
INSERT INTO SESSION.temp_AGREEMENT_REQUEST_CROSS_REF (AGREEMENT_REQUEST_ID, SOURCE_SYSTEM_NM,
AGREEMENT_REQUEST_TYPE_ID, LOAD_PROCESS_KEY_PART_01, LOAD_PROCESS_KEY_PART_02,
LOAD_PROCESS_KEY_PART_03, LOAD_PROCESS_KEY_PART_04, LOAD_PROCESS_KEY_PART_05,
LOAD_PROCESS_COMPOSITE_KEY,
BUSINESS_KEY_PART_01, BUSINESS_KEY_PART_02,
BUSINESS_KEY_PART_03, BUSINESS_KEY_PART_04,
LOAD_TS, CREATION_TS, LAST_MODIFIED_TS)
VALUES
(V_PCR_AGGREGATE_ID, C_SOURCE_SYSTEM_NM, V_PREMIUM_CONTR_TYPE_ID,
RTRIM(V_PLAN_ID), P_LOCATION_CODE, V_FILE_DATE, RTRIM(V_PARTICIPANT_NUM),
V_MONEY_SRC_CODE, RTRIM(V_PLAN_ID)||'~'||P_LOCATION_CODE||'~'|| V_FILE_DATE||'~'|| RTRIM(V_PARTICIPANT_NUM) ||'~'|| V_MONEY_SRC_CODE,
V_PAYROLL_GROUP_NUM, V_FILE_DATE, V_PARTICIPANT_NUM, V_MONEY_SRC_CODE, P_LAST_MODIFIED_TIMESTAMP,
P_LAST_MODIFIED_TIMESTAMP, P_LAST_MODIFIED_TIMESTAMP);
INSERT INTO SESSION.temp_AGREEMENT_REQUEST (AGREEMENT_REQUEST_ID, TYPE_ID, AGREEMENT_ID,
REQUEST_TS, SOURCE_SYSTEM_TXT, DELETED_FL,CREATION_USER_ID, LOAD_TS, LAST_MODIFIED_USER_ID,
CREATION_TS, LAST_MODIFIED_TS, REQUEST_STATUS_CD, REQUEST_STATUS_DT)
VALUES (V_PCR_AGGREGATE_ID, V_PREMIUM_CONTR_TYPE_ID, V_AAC_AGGREEGATE_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_FINANCIAL_MOVEMENT_REQUEST (
AGREEMENT_REQUEST_ID, TYPE_ID, Request_amt,
EXPECTED_DEFERRAL_AMT, PAYROLL_PERIOD_END_DT, SOURCE_SYSTEM_TXT, DELETED_FL,
CREATION_USER_ID, CREATION_TS, LAST_MODIFIED_USER_ID, LAST_MODIFIED_TS)
VALUES (V_PCR_AGGREGATE_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_AGREEMENT_REQUEST_RLSHP(AGREEMENT_REQ UEST_RLSHP_ID,LEFT_AGREEMENT_REQUEST_ID,SOURCE_SYS TEM_TXT,
RIGHT_AGREEMENT_REQUEST_ID,NATURE_ID,START_TS,RIGH T_AGREEMENT_RQST_TYPE_ID,
LEFT_AGREEMENT_REQUEST_TYPE_ID,DELETED_FL,CREATION _USER_ID,LOAD_TS,LAST_MODIFIED_USER_ID,
CREATION_TS,LAST_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_ALLOCATION_COMPONENTs VALUES (V_AAC_AGGREEGATE_ID,V_CONTR_AMT);
SET at_end = 0;
END IF;
DELETE FROM SESSION.TEMP_ALLOCATIONS;
INSERT INTO SESSION.TEMP_ALLOCATIONS
(SELECT AGREEMENT_ID, BUSINESS_KEY_PART_05, LOAD_PROCESS_KEY_PART_05,
BUSINESS_KEY_PART_07, LOAD_PROCESS_KEY_PART_07,
LOAD_PROCESS_KEY_PART_01, ar.ALLOCATION_AMT, ar.ALLOCATION_PCT,ar.PRIORITY_SEQUENCE_NBR
FROM GCRR.AGREEMENT_CROSS_REF a, GCRR.AGREEMENT_RLSHP ar
WHERE a.AGREEMENT_ID = ar.RIGHT_AGREEMENT_ID
AND AGREEMENT_TYPE_ID = V_AAC_TYPE_ID
AND SOURCE_SYSTEM_NM = C_SOURCE_SYSTEM_NM
AND LOAD_PROCESS_KEY_PART_02 = V_MONEY_SRC_CODE
AND LOAD_PROCESS_KEY_PART_03 = V_PLAN_ID
AND LOAD_PROCESS_KEY_PART_04 = P_LOCATION_CODE
AND LOAD_PROCESS_KEY_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_ALLOCATIONS
WHERE PRIORITY = 0 AND AMOUNT > 0 WITH UR;
SELECT COUNT(1) INTO V_O_AMT_COUNT
FROM SESSION.TEMP_ALLOCATIONS
WHERE PRIORITY > 0 AND AMOUNT > 0 WITH UR;
SELECT COUNT(1) INTO V_PCT_COUNT
FROM SESSION.TEMP_ALLOCATIONS
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_ALLOCATIONS 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_ALLOCATIONS
ORDER BY PERCENT ASC, PRIORITY ASC WITH UR
DO
SET V_AAC_INDIVIDUAL_ID = EACH_ALLOC.AGREEMENT_ID;
SET V_VENDOR_NUM = EACH_ALLOC.VENDOR_NUM;
SET V_RECORDKEEPER_ID = EACH_ALLOC.VENDOR_ID;
SET V_CONTRACT_NUMBER = EACH_ALLOC.CONTRACT_NUMBER;
SET V_PRODUCT_NUM = EACH_ALLOC.PRODUCT_NUM;
SET V_PRODUCT_ID = EACH_ALLOC.PRODUCT_ID;
SET V_AMOUNT = EACH_ALLOC.AMOUNT;
SET V_PERCENT = EACH_ALLOC.PERCENT;
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_VENDOR_REMITTANCE_WORK_FILE
(VENDOR_NUM, PARTICIPANT_NUM, PARTICIPANT_SSN,PLAN_NUM, PRODUCT_NUM, MONEY_SOURCE,
VENDOR_CONTRACT_NUM, REMITTANCE_AMT, UID, STATUS,LAST_MODIFIED_TS,
CREATION_TS,CREATION_USER_ID,LAST_MODIFIED_USER_ID )
VALUES (V_VENDOR_NUM,V_PARTICIPANT_NUM,V_SSN,V_PLAN_NUM, V_PRODUCT_NUM, V_MONEY_SRC, V_CONTRACT_NUMBER, V_CAL_AMOUNT,
P_FILE_UID,'FP',P_LAST_MODIFIED_TIMESTAMP,P_LAST_M ODIFIED_TIMESTAMP,P_LAST_MODIFIED_USER_ID,P_LAST_M ODIFIED_USER_ID);
SET at_end = 0;
Select AGREEMENT_REQUEST_ID INTO V_PCR_INDIVIDUAL_ID
from SESSION.temp_AGREEMENT_REQUEST_CROSS_REF a
Where AGREEMENT_REQUEST_TYPE_ID = V_PREMIUM_CONTR_TYPE_ID
AND LOAD_PROCESS_KEY_PART_01 = V_PLAN_ID
AND LOAD_PROCESS_KEY_PART_02 = P_LOCATION_CODE
AND LOAD_PROCESS_KEY_PART_03 = V_FILE_DATE
AND LOAD_PROCESS_KEY_PART_04 = V_PARTICIPANT_NUM
AND LOAD_PROCESS_KEY_PART_05 = V_CONTRACT_NUMBER
AND LOAD_PROCESS_KEY_PART_06 = V_MONEY_SRC_CODE
AND LOAD_PROCESS_KEY_PART_07 = V_RECORDKEEPER_ID
AND DELETE_TS IS NULL;
IF (at_end = 1) THEN
SELECT NEXTVAL FOR GCRR.AGREEMENT_REQUEST_CROSS_REFSEQ INTO V_PCR_INDIVIDUAL_ID
FROM SYSIBM.SYSDUMMY1 s;
INSERT INTO SESSION.temp_AGREEMENT_REQUEST_CROSS_REF (AGREEMENT_REQUEST_ID, SOURCE_SYSTEM_NM,
AGREEMENT_REQUEST_TYPE_ID, LOAD_PROCESS_KEY_PART_01, LOAD_PROCESS_KEY_PART_02,
LOAD_PROCESS_KEY_PART_03, LOAD_PROCESS_KEY_PART_04, LOAD_PROCESS_KEY_PART_05,
LOAD_PROCESS_KEY_PART_06, LOAD_PROCESS_KEY_PART_07,
LOAD_PROCESS_COMPOSITE_KEY,
BUSINESS_KEY_PART_01, BUSINESS_KEY_PART_02,
BUSINESS_KEY_PART_03, BUSINESS_KEY_PART_04,
BUSINESS_KEY_PART_05, BUSINESS_KEY_PART_06,
LOAD_TS, CREATION_TS, LAST_MODIFIED_TS)
VALUES
(V_PCR_INDIVIDUAL_ID, C_SOURCE_SYSTEM_NM, V_PREMIUM_CONTR_TYPE_ID,
RTRIM(V_PLAN_ID), P_LOCATION_CODE, V_FILE_DATE, RTRIM(V_PARTICIPANT_NUM),
V_CONTRACT_NUMBER, V_MONEY_SRC_CODE,V_RECORDKEEPER_ID,
RTRIM(V_PLAN_ID)||'~'||P_LOCATION_CODE||'~'|| V_FILE_DATE||'~'|| RTRIM(V_PARTICIPANT_NUM) ||'~'|| V_CONTRACT_NUMBER ||'~'|| V_MONEY_SRC_CODE,
V_PAYROLL_GROUP_NUM, V_FILE_DATE, V_PARTICIPANT_NUM, V_CONTRACT_NUMBER, V_MONEY_SRC_CODE,V_VENDOR_NUM,
P_LAST_MODIFIED_TIMESTAMP, P_LAST_MODIFIED_TIMESTAMP, P_LAST_MODIFIED_TIMESTAMP);
INSERT INTO SESSION.temp_AGREEMENT_REQUEST (AGREEMENT_REQUEST_ID, TYPE_ID, AGREEMENT_ID,
REQUEST_TS, SOURCE_SYSTEM_TXT, DELETED_FL,CREATION_USER_ID, LOAD_TS, LAST_MODIFIED_USER_ID,
CREATION_TS, LAST_MODIFIED_TS, REQUEST_STATUS_CD, REQUEST_STATUS_DT)
VALUES (V_PCR_INDIVIDUAL_ID, V_PREMIUM_CONTR_TYPE_ID, V_AAC_INDIVIDUAL_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_FINANCIAL_MOVEMENT_REQUEST (
AGREEMENT_REQUEST_ID, TYPE_ID, Request_amt,
EXPECTED_DEFERRAL_AMT, PAYROLL_PERIOD_END_DT, SOURCE_SYSTEM_TXT, DELETED_FL,
CREATION_USER_ID, CREATION_TS, LAST_MODIFIED_USER_ID, LAST_MODIFIED_TS)
VALUES (V_PCR_INDIVIDUAL_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_AGREEMENT_REQUEST_RLSHP(AGREEMENT_REQ UEST_RLSHP_ID,LEFT_AGREEMENT_REQUEST_ID,SOURCE_SYS TEM_TXT,
RIGHT_AGREEMENT_REQUEST_ID,NATURE_ID,START_TS,RIGH T_AGREEMENT_RQST_TYPE_ID,
LEFT_AGREEMENT_REQUEST_TYPE_ID,DELETED_FL,CREATION _USER_ID,LOAD_TS,LAST_MODIFIED_USER_ID,
CREATION_TS,LAST_MODIFIED_TS)
VALUES (1,V_PCR_AGGREGATE_ID, C_SOURCE_SYSTEM_NM, V_PCR_INDIVIDUAL_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_ALLOCATION_COMPONENTs VALUES (V_AAC_INDIVIDUAL_ID,V_CAL_AMOUNT);
SET at_end = 0;
END IF;
END FOR EACH_ALLOC;
IF V_CONTR_AMT > 0 AND V_PCT_COUNT = 0 THEN
UPDATE SESSION.temp_VENDOR_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_ALLOCATIONS
WHERE PRIORITY = (SELECT MIN(PRIORITY) FROM SESSION.TEMP_ALLOCATIONS))
AND MONEY_SOURCE = V_MONEY_SRC
AND PARTICIPANT_NUM = PARTICIPANT_NUM;
UPDATE SESSION.temp_ALLOCATION_COMPONENTs
SET AMOUNT = AMOUNT + V_CONTR_AMT
WHERE AGREEMENT_ID = ( SELECT AGREEMENT_ID
FROM SESSION.TEMP_ALLOCATIONS
WHERE PRIORITY = (SELECT MIN(PRIORITY) FROM SESSION.TEMP_ALLOCATIONS));
END IF;
END IF;
END FOR;
SET at_end = 0;
SELECT COUNT(1) INTO V_INDEX
FROM SESSION.temp_VENDOR_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.INSTRUCTION_FILE_ERRORS(ERROR_CODE, FILE_UID, PARTICIPANT_NUM,
CREATION_TS, LAST_MODIFIED_TS, CREATION_USER_ID, LAST_MODIFIED_USER_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_REMITTANCE_WORK_FILE p
SET STATUS = 'FE'
where file_uid = P_FILE_UID;
ELSE
UPDATE GCRR.PLAN_REMITTANCE_WORK_FILE p
SET STATUS = 'FP'
where file_uid = P_FILE_UID;
UPDATE GCRR.ASSET_ACCUMULATION_COMPONENT A
SET CURRENT_YTD_CONTRIBUTION_AMT = CURRENT_YTD_CONTRIBUTION_AMT +
(SELECT AMOUNT FROM SESSION.temp_ALLOCATION_COMPONENTs ta where a.AGREEMENT_ID = ta.AGREEMENT_ID),
LAST_MODIFIED_USER_ID = P_LAST_MODIFIED_USER_ID,
LAST_MODIFIED_TS = P_LAST_MODIFIED_TIMESTAMP;
UPDATE GCRR.LOAN_COMPONENT A
SET CURRENT_YTD_REPAYMENT_AMT = CURRENT_YTD_REPAYMENT_AMT +
(SELECT AMOUNT FROM SESSION.temp_ALLOCATION_COMPONENTs ta where a.AGREEMENT_ID = ta.AGREEMENT_ID),
LAST_MODIFIED_USER_ID = P_LAST_MODIFIED_USER_ID,
LAST_MODIFIED_TS = P_LAST_MODIFIED_TIMESTAMP ;
INSERT INTO GCRR.AGREEMENT_REQUEST_CROSS_REF (SELECT * FROM SESSION.TEMP_AGREEMENT_REQUEST_CROSS_REF);
INSERT INTO GCRR.AGREEMENT_REQUEST (SELECT * FROM SESSION.TEMP_AGREEMENT_REQUEST);
INSERT INTO GCRR.FINANCIAL_MOVEMENT_REQUEST (SELECT * FROM SESSION.TEMP_FINANCIAL_MOVEMENT_REQUEST);
INSERT INTO GCRR.AGREEMENT_REQUEST_RLSHP(LEFT_AGREEMENT_REQUES T_ID, SOURCE_SYSTEM_TXT,
RIGHT_AGREEMENT_REQUEST_ID, NATURE_ID, START_TS, RIGHT_AGREEMENT_RQST_TYPE_ID,
END_TS, LEFT_AGREEMENT_REQUEST_TYPE_ID, DELETED_FL,
CREATION_USER_ID, LOAD_TS, LAST_MODIFIED_USER_ID, CREATION_TS, LAST_MODIFIED_TS)
SELECT LEFT_AGREEMENT_REQUEST_ID, SOURCE_SYSTEM_TXT,
RIGHT_AGREEMENT_REQUEST_ID, NATURE_ID, START_TS, RIGHT_AGREEMENT_RQST_TYPE_ID,
END_TS, LEFT_AGREEMENT_REQUEST_TYPE_ID, DELETED_FL,
CREATION_USER_ID, LOAD_TS, LAST_MODIFIED_USER_ID, CREATION_TS, LAST_MODIFIED_TS
FROM SESSION.temp_AGREEMENT_REQUEST_RLSHP;
INSERT INTO GCRR.VENDOR_REMITTANCE_WORK_FILE (SELECT * FROM SESSION.TEMP_VENDOR_REMITTANCE_WORK_FILE);
END IF;
END
;