[Home] [Help]
PACKAGE BODY: APPS.FTP_PAYMENT_SCHEDULE_PKG
Source
1 PACKAGE BODY ftp_payment_schedule_pkg AS
2 /* $Header: ftppayib.pls 120.6 2006/03/06 03:53:38 appldev noship $ */
3
4 /**********************
5 -- Package Constants
6 ************************/
7
8 C_LOG_LEVEL_2 CONSTANT NUMBER := fnd_log.level_procedure;
9 G_RET_STS_ERROR CONSTANT VARCHAR2(1) := FND_API.G_RET_STS_ERROR;
10 G_RET_STS_UNEXP_ERROR CONSTANT VARCHAR2(1) := FND_API.G_RET_STS_UNEXP_ERROR;
11 G_LOG_LEVEL_ERROR CONSTANT NUMBER := FND_LOG.level_error;
12 G_RET_STS_SUCCESS CONSTANT VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
13 c_false CONSTANT VARCHAR2(1) := FND_API.G_FALSE;
14 c_true CONSTANT VARCHAR2(1) := FND_API.G_TRUE;
15
16
17 PROCEDURE TransferData(errbuf OUT NOCOPY VARCHAR2,
18 retcode OUT NOCOPY VARCHAR2,
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;
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
41 FEM_ENGINES_PKG.TECH_MESSAGE(
42 p_severity => C_LOG_LEVEL_2,
43 p_module => l_block ||'Transfer Data',
44 p_msg_text => 'Transfer Begins in Bulk'
45 );
46
47 IF nvl(isTruncate, 'N') = 'Y' THEN
48 EXECUTE IMMEDIATE 'TRUNCATE TABLE FTP.FTP_PAYMENT_SCHEDULE';
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
58 inst_retun_status := G_RET_STS_ERROR;
59 -- Validate Instrument Type. If succes proceed further else log error;
60 Validate_Inst_Type_Code(l_instrument_type_code(i),inst_retun_status);
61 IF inst_retun_status = G_RET_STS_SUCCESS THEN
62 l_retun_status := G_RET_STS_ERROR;
63 Validate_Source_System ( l_source_system_display_code(i),l_source_system_code, l_retun_status );
64 IF l_retun_status = G_RET_STS_SUCCESS THEN
65 BEGIN
66 INSERT INTO FTP_PAYMENT_SCHEDULE
67 (ID_NUMBER, SOURCE_SYSTEM_CODE, INSTRUMENT_TYPE_CODE, PAYMENT_DATE, AMOUNT,
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
86 PAYMENT_DATE = l_payment_date(i);
87
88 FEM_ENGINES_PKG.TECH_MESSAGE(
89 p_severity => C_LOG_LEVEL_2,
90 p_module => l_block ||'Transfer Data',
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
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
117 PAYMENT_DATE = l_payment_date(i);
118
119 FEM_ENGINES_PKG.TECH_MESSAGE(
120 p_severity => C_LOG_LEVEL_2,
121 p_module => l_block ||'Transfer Data',
122 p_msg_text => 'Error Inserting : OTHERS '
123 );
124 END;
125
126 END LOOP;
127
128 FEM_ENGINES_PKG.TECH_MESSAGE(
129 p_severity => C_LOG_LEVEL_2,
130 p_module => l_block,
131 p_msg_text => 'Successfully inserted rows: '||l_id_number.COUNT
132 );
133
134 COMMIT;
135 DeleteData ;
136 COMMIT;
137 retcode := c_true;
138 FEM_ENGINES_PKG.User_Message(
139 p_app_name => 'FTP',
140 p_msg_name => 'FTP_PAY_SCHEDULE_RUN',
141 p_token1 => 'INSCOUNT',
142 p_value1 => l_rowcount,
143 p_token2 => 'RCOUNT',
144 p_value2 => l_id_number.COUNT
145 );
146 EXCEPTION
147 when others then
148 FEM_ENGINES_PKG.Put_Message(
149 p_app_name => 'FTP',
150 p_msg_name => 'FTP_UNEXP_ERR',
151 p_token1 => 'SQLERRM',
152 p_value1 => sqlerrm
153 );
154 retcode := c_false;
155 END TransferData; /* Procedure TransferData */
156
157
158 PROCEDURE DeleteData
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,
168 p_msg_text => 'Successfully deleted rows: '||SQL%ROWCOUNT
169 );
170
171 --commit the data
172 COMMIT;
173
174 EXCEPTION
175 WHEN OTHERS THEN
176 FEM_ENGINES_PKG.TECH_MESSAGE(
177 p_severity => C_LOG_LEVEL_2,
178 p_module => l_block ||'Delete Data',
179 p_msg_text => 'Error Deleteing Data'
180 );
181 END DeleteData;
182
183
184 PROCEDURE Validate_Source_System (
185 p_source_system_code IN VARCHAR2,
186 x_source_system_code OUT NOCOPY NUMBER,
187 x_return_status OUT NOCOPY VARCHAR2
188 )
189 IS
190 C_MODULE CONSTANT FND_LOG_MESSAGES.module%TYPE := 'FTP_PAYMENT_SCHDULDE.validate_source_system';
191
192 BEGIN
193 FEM_ENGINES_PKG.TECH_MESSAGE(
194 p_severity => C_LOG_LEVEL_2,
195 p_module => C_MODULE,
196 p_msg_text => 'Begin Validating Source System Code for'
197 );
198 x_return_status := G_RET_STS_SUCCESS;
199
200 BEGIN
201 SELECT source_system_code
202 INTO x_source_system_code
203 FROM fem_source_systems_b
204 WHERE source_system_display_code = p_source_system_code
205 AND enabled_flag = 'Y'
206 AND personal_flag = 'N';
207 EXCEPTION
208 WHEN no_data_found THEN
209 x_return_status := G_RET_STS_ERROR;
210 FEM_ENGINES_PKG.Tech_Message(
211 p_severity => C_LOG_LEVEL_2,
212 p_module => 'Transfer Data',
213 p_msg_text => 'No Data for Source System Display Code'
214 );
215 END;
216 EXCEPTION
217 WHEN others THEN
218 x_return_status := G_RET_STS_UNEXP_ERROR;
219
220 END Validate_Source_System;
221
222
223 PROCEDURE Validate_Inst_Type_Code(
224 p_inst_type_code IN NUMBER,
225 x_return_status OUT NOCOPY VARCHAR2
226 ) IS
227 C_MODULE CONSTANT FND_LOG_MESSAGES.module%TYPE := 'FTP_PAYMENT_SCHDULDE.validate_Inst_Type_code';
228 x_inst_type_code Varchar2(100);
229 l_inst_type_code Varchar2(100);
230 BEGIN
231 l_inst_type_code := to_char(p_inst_type_code);
232 x_return_status := G_RET_STS_SUCCESS;
233 BEGIN
234 SELECT LOOKUP_CODE
235 INTO x_inst_type_code
236 FROM FTP_LOOKUPS
237 WHERE LOOKUP_TYPE = 'FTP_INST_TYPES_CODE' AND
238 LOOKUP_CODE = trim(l_inst_type_code);
239 EXCEPTION
240 WHEN no_data_found THEN
241 x_return_status := G_RET_STS_ERROR;
242 FEM_ENGINES_PKG.Tech_Message(
243 p_severity => C_LOG_LEVEL_2,
244 p_module => 'Validate_Inst_Type_Code',
245 p_msg_text => 'Error In Instrument Type Code'
246 );
247 END;
248 EXCEPTION
249 WHEN others THEN
250 x_return_status := G_RET_STS_UNEXP_ERROR;
251 END Validate_Inst_Type_Code;
252
253 END ftp_payment_schedule_pkg;
254