473,320 Members | 1,719 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

UDB Stored Procedure

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

;
Nov 17 '08 #1
0 2103

Sign in to post your reply or Sign up for a free account.

Similar topics

3
by: dinesh prasad | last post by:
I'm trying to use a servlet to process a form, then send that data to an SQL server stored procedure. I'm using the WebLogic 8 App. server. I am able to retrieve database information, so I know my...
0
by: Nashat Wanly | last post by:
HOW TO: Call a Parameterized Stored Procedure by Using ADO.NET and Visual C# .NET View products that this article applies to. This article was previously published under Q310070 For a Microsoft...
3
by: Rhino | last post by:
I've spent the last couple of hours trying to figure out how to debug a Java stored procedure and am just going in circles. The last straw came when I got "Cannot open input stream for default"...
4
by: Rhino | last post by:
Is it possible for a Java Stored Procedure in DB2 V7.2 (Windows) to pass a Throwable back to the calling program as an OUT parameter? If yes, what datatype should I use when registering the...
8
by: Thomasb | last post by:
With a background in MS SQL Server programming I'm used to temporary tables. Have just started to work with DB2 ver 7 on z/OS and stumbled into the concept of GLOBAL TEMPORARY TABLE. I have...
2
by: Dino L. | last post by:
How can I run stored procedure (MSSQL) ?
0
by: Amber | last post by:
Stored procedures are faster and more efficient than in-line SQL statements. In this article we will look at two SQL Server stored procedures; one using an input parameter and one not, and see how...
3
by: kd | last post by:
Hi All, How to debug a stored procedure? Thanks, kd
7
by: Dabbler | last post by:
I'm using an ObjectDataSource with a stored procedure and am getting the following error when trying to update (ExecuteNonQuery): System.Data.SqlClient.SqlException: Procedure or Function...
2
by: jed | last post by:
I have created this example in sqlexpress ALTER PROCEDURE . @annualtax FLOAT AS BEGIN SELECT begin1,end1,deductedamount,pecentageextra FROM tax
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.