[Home] [Help]
19: isTruncate IN VARCHAR2
20: )
21: IS
22:
23: TYPE t_id_number_tbl IS TABLE OF FTP_PAYMENT_SCHEDULE_T.ID_NUMBER%TYPE;
24: TYPE t_source_system_disp_cd_tbl IS TABLE OF FTP_PAYMENT_SCHEDULE_T.SOURCE_SYSTEM_DISPLAY_CODE%TYPE;
25: TYPE t_instrument_type_code_tbl IS TABLE OF FTP_PAYMENT_SCHEDULE_T.INSTRUMENT_TYPE_CODE%TYPE;
26: TYPE t_payment_date_tbl IS TABLE OF FTP_PAYMENT_SCHEDULE_T.PAYMENT_DATE%TYPE;
27: TYPE t_amount_tbl IS TABLE OF FTP_PAYMENT_SCHEDULE_T.AMOUNT%TYPE;
20: )
21: IS
22:
23: TYPE t_id_number_tbl IS TABLE OF FTP_PAYMENT_SCHEDULE_T.ID_NUMBER%TYPE;
24: TYPE t_source_system_disp_cd_tbl IS TABLE OF FTP_PAYMENT_SCHEDULE_T.SOURCE_SYSTEM_DISPLAY_CODE%TYPE;
25: TYPE t_instrument_type_code_tbl IS TABLE OF FTP_PAYMENT_SCHEDULE_T.INSTRUMENT_TYPE_CODE%TYPE;
26: TYPE t_payment_date_tbl IS TABLE OF FTP_PAYMENT_SCHEDULE_T.PAYMENT_DATE%TYPE;
27: TYPE t_amount_tbl IS TABLE OF FTP_PAYMENT_SCHEDULE_T.AMOUNT%TYPE;
28:
21: IS
22:
23: TYPE t_id_number_tbl IS TABLE OF FTP_PAYMENT_SCHEDULE_T.ID_NUMBER%TYPE;
24: TYPE t_source_system_disp_cd_tbl IS TABLE OF FTP_PAYMENT_SCHEDULE_T.SOURCE_SYSTEM_DISPLAY_CODE%TYPE;
25: TYPE t_instrument_type_code_tbl IS TABLE OF FTP_PAYMENT_SCHEDULE_T.INSTRUMENT_TYPE_CODE%TYPE;
26: TYPE t_payment_date_tbl IS TABLE OF FTP_PAYMENT_SCHEDULE_T.PAYMENT_DATE%TYPE;
27: TYPE t_amount_tbl IS TABLE OF FTP_PAYMENT_SCHEDULE_T.AMOUNT%TYPE;
28:
29: l_id_number t_id_number_tbl;
22:
23: TYPE t_id_number_tbl IS TABLE OF FTP_PAYMENT_SCHEDULE_T.ID_NUMBER%TYPE;
24: TYPE t_source_system_disp_cd_tbl IS TABLE OF FTP_PAYMENT_SCHEDULE_T.SOURCE_SYSTEM_DISPLAY_CODE%TYPE;
25: TYPE t_instrument_type_code_tbl IS TABLE OF FTP_PAYMENT_SCHEDULE_T.INSTRUMENT_TYPE_CODE%TYPE;
26: TYPE t_payment_date_tbl IS TABLE OF FTP_PAYMENT_SCHEDULE_T.PAYMENT_DATE%TYPE;
27: TYPE t_amount_tbl IS TABLE OF FTP_PAYMENT_SCHEDULE_T.AMOUNT%TYPE;
28:
29: l_id_number t_id_number_tbl;
30: l_source_system_display_code t_source_system_disp_cd_tbl;
23: TYPE t_id_number_tbl IS TABLE OF FTP_PAYMENT_SCHEDULE_T.ID_NUMBER%TYPE;
24: TYPE t_source_system_disp_cd_tbl IS TABLE OF FTP_PAYMENT_SCHEDULE_T.SOURCE_SYSTEM_DISPLAY_CODE%TYPE;
25: TYPE t_instrument_type_code_tbl IS TABLE OF FTP_PAYMENT_SCHEDULE_T.INSTRUMENT_TYPE_CODE%TYPE;
26: TYPE t_payment_date_tbl IS TABLE OF FTP_PAYMENT_SCHEDULE_T.PAYMENT_DATE%TYPE;
27: TYPE t_amount_tbl IS TABLE OF FTP_PAYMENT_SCHEDULE_T.AMOUNT%TYPE;
28:
29: l_id_number t_id_number_tbl;
30: l_source_system_display_code t_source_system_disp_cd_tbl;
31: l_instrument_type_code t_instrument_type_code_tbl;
32: l_payment_date t_payment_date_tbl;
33: l_amount t_amount_tbl;
34: l_retun_status Varchar2(80);
35: inst_retun_status Varchar2(80);
36: l_block CONSTANT VARCHAR2(80) := 'FTP_PAYMENT_SCHEDULE.TransferData';
37: l_source_system_code FTP_PAYMENT_SCHEDULE.SOURCE_SYSTEM_CODE%TYPE;
38: l_rowcount Number :=0;
39: BEGIN
40:
49: END IF;
50:
51: SELECT ID_NUMBER, SOURCE_SYSTEM_DISPLAY_CODE, INSTRUMENT_TYPE_CODE, PAYMENT_DATE, AMOUNT
52: BULK COLLECT INTO l_id_number, l_source_system_display_code,l_instrument_type_code, l_payment_date,l_amount
53: FROM FTP_PAYMENT_SCHEDULE_T ;
54:
55: l_rowcount :=0;
56: FOR i in 1..l_id_number.COUNT LOOP
57: BEGIN
68: CREATED_BY_OBJECT_ID,CREATED_BY_REQUEST_ID,LAST_UPDATED_BY_OBJECT_ID,LAST_UPDATED_BY_REQUEST_ID)
69: VALUES (l_id_number(i), l_source_system_code,l_instrument_type_code(i), l_payment_date(i),l_amount(i),
70: 1,1,1,1);
71:
72: UPDATE FTP_PAYMENT_SCHEDULE_T SET STATUS ='INSERT'
73: WHERE
74: ID_NUMBER = l_id_number(i) AND
75: SOURCE_SYSTEM_DISPLAY_CODE = l_source_system_display_code(i) AND
76: INSTRUMENT_TYPE_CODE = l_instrument_type_code(i) AND
77: PAYMENT_DATE = l_payment_date(i) ;
78: l_rowcount := l_rowcount + 1;
79: EXCEPTION
80: WHEN OTHERS THEN
81: UPDATE FTP_PAYMENT_SCHEDULE_T SET STATUS ='Error Inserting the Data : Unique Constraint Violated'
82: WHERE
83: ID_NUMBER = l_id_number(i) AND
84: SOURCE_SYSTEM_DISPLAY_CODE = l_source_system_display_code(i) AND
85: INSTRUMENT_TYPE_CODE = l_instrument_type_code(i) AND
91: p_msg_text => 'Error Inseting the Data'
92: );
93: END;
94: ELSE
95: UPDATE FTP_PAYMENT_SCHEDULE_T SET STATUS ='INVALID SYSTEM DISPLAY CODE ERROR'
96: WHERE
97: ID_NUMBER = l_id_number(i) AND
98: SOURCE_SYSTEM_DISPLAY_CODE = l_source_system_display_code(i) AND
99: INSTRUMENT_TYPE_CODE = l_instrument_type_code(i) AND
99: INSTRUMENT_TYPE_CODE = l_instrument_type_code(i) AND
100: PAYMENT_DATE = l_payment_date(i);
101: END IF;
102: ELSE
103: UPDATE FTP_PAYMENT_SCHEDULE_T SET STATUS ='INVALID INSTRUMENT TYPE CODE ERROR'
104: WHERE
105: ID_NUMBER = l_id_number(i) AND
106: SOURCE_SYSTEM_DISPLAY_CODE = l_source_system_display_code(i) AND
107: INSTRUMENT_TYPE_CODE = l_instrument_type_code(i) AND
108: PAYMENT_DATE = l_payment_date(i);
109: END IF;
110: EXCEPTION
111: WHEN OTHERS THEN
112: UPDATE FTP_PAYMENT_SCHEDULE_T SET STATUS ='FTP TRANSFER DATA ERROR : OTHERS'
113: WHERE
114: ID_NUMBER = l_id_number(i) AND
115: SOURCE_SYSTEM_DISPLAY_CODE = l_source_system_display_code(i) AND
116: INSTRUMENT_TYPE_CODE = l_instrument_type_code(i) AND
159: IS
160: l_block CONSTANT VARCHAR2(80) := 'FTP_PAYMENT_SCHEDULE_migrate.DeleteData';
161: BEGIN
162:
163: DELETE FROM FTP_PAYMENT_SCHEDULE_T WHERE STATUS IN ('INSERT','UPDATE');
164:
165: FEM_ENGINES_PKG.TECH_MESSAGE(
166: p_severity => C_LOG_LEVEL_2,
167: p_module => l_block,