[Home] [Help]
PACKAGE BODY: APPS.CE_COPY_TRX_CODES_XML
Source
1 PACKAGE BODY CE_COPY_TRX_CODES_XML AS
2 /* $Header: cecptxcb.pls 120.1.12010000.1 2009/07/31 08:39:23 vnetan noship $ */
3
4 /*========================================================================+
5 | PRIVATE PROCEDURE |
6 | debug_log |
7 | |
8 | DESCRIPTION |
9 | Procedure to print debug messages to logs. |
10 +========================================================================*/
11 PROCEDURE debug_log( p_message IN VARCHAR2 ) IS
12 BEGIN
13 cep_standard.debug(p_message);
14 --vn_debug_proc(p_message);
15 END debug_log;
16
17 /*========================================================================+
18 | PRIVATE PROCEDURE |
19 | debug_log |
20 | |
21 | DESCRIPTION |
22 | Procedure to initialize global variables. |
23 +========================================================================*/
24 PROCEDURE init_variables IS
25 BEGIN
26 debug_log('>>CE_COPY_TRX_CODES_XML.init_variables');
27
28 -- source account
29 IF p_source_acct_id IS NOT NULL
30 THEN
31 SELECT SUBSTR(CBA.bank_account_name,0,50), CBA.bank_account_num
32 INTO g_source_account_name, g_source_account_num
33 FROM ce_bank_accounts CBA
34 WHERE CBA.bank_account_id = p_source_acct_id;
35 ELSE
36 g_source_account_name := NULL;
37 g_source_account_num := NULL;
38 END IF;
39 debug_log('g_source_account_name::'||g_source_account_name);
40 debug_log('g_source_account_num::'||g_source_account_num);
41
42 -- destination bank
43 IF p_dest_bank_id IS NOT NULL
44 THEN
45 SELECT SUBSTR(BNK.party_name,0,50) INTO g_dest_bank_name
46 FROM hz_parties BNK WHERE BNK.party_id = p_dest_bank_id;
47 ELSE
48 g_dest_bank_name := NULL;
52 --destination branch
49 END IF;
50 debug_log('g_dest_bank_name::'||g_dest_bank_name);
51
53 IF p_dest_branch_id IS NOT NULL
54 THEN
55 SELECT SUBSTR(BRN.party_name,0,50) INTO g_dest_branch_name
56 FROM hz_parties BRN WHERE BRN.party_id = p_dest_branch_id;
57 ELSE
58 g_dest_branch_name := NULL;
59 END IF;
60 debug_log('g_dest_branch_name::'||g_dest_branch_name);
61
62 --destination account
63 IF p_dest_acct_id IS NOT NULL
64 THEN
65 SELECT CBA.bank_account_num INTO g_dest_account_num
66 FROM ce_bank_accounts CBA
67 WHERE CBA.bank_account_id = p_dest_acct_id;
68 ELSE
69 g_dest_account_num := NULL;
70 END IF;
71 debug_log('g_dest_account_num::'||g_dest_account_num);
72
73 --destination type
74 IF p_dest_acct_type IS NOT NULL
75 THEN
76 SELECT substr(LKP.meaning,0,50) INTO g_dest_account_type
77 FROM ce_lookups LKP
78 WHERE LKP.lookup_type = 'BANK_ACCOUNT_TYPE'
79 AND LKP.lookup_code = p_dest_acct_type;
80 ElSE
81 g_dest_account_type := NULL;
82 END IF;
83 debug_log('g_dest_account_type::'||g_dest_account_type);
84
85 --request_id
86 g_conc_request_id := FND_GLOBAL.CONC_REQUEST_ID;
87 debug_log('g_conc_request_id::'||g_conc_request_id);
88
89 debug_log('<<CE_COPY_TRX_CODES_XML.init_variables');
90
91 END init_variables;
92
93 /*========================================================================+
94 | PRIVATE FUNCTION |
95 | check_unique_trxn_code |
96 | |
97 | DESCRIPTION |
98 | Checks for uniquness of Transaction codes before insertion. |
99 | |
100 | ARGUMENTS |
101 | X_row_id Rowid of a row |
102 | X_trx_code Transaction code of row to be inserted |
103 | X_bank_account_id Bank Account Id |
104 | X_trx_type DEBIT, CREDIT, MISC_DEBIT, MISC_CREDIT, |
105 | NSF, REJECTED, STOP, SWEEP_IN, SWEEP_OUT|
106 | X_RECONCILE_FLAG null, AR, AP, JE, CE, OI |
107 | X_RECONCILIATION_SEQUENCE null, 1,2,3 ... |
108 | |
109 +========================================================================*/
110 FUNCTION check_unique_trxn_code(
111 X_TRX_CODE IN VARCHAR2,
112 X_BANK_ACCOUNT_ID IN NUMBER,
113 X_ROW_ID IN VARCHAR2,
114 X_TRX_TYPE IN VARCHAR2,
115 X_RECONCILE_FLAG IN VARCHAR2,
116 X_RECONCILIATION_SEQUENCE IN NUMBER
117 ) RETURN VARCHAR2 IS
118 -- each trx_code cannot be used by more than one trx_type
119 -- check Type, Code
120 CURSOR chk_duplicates is
121 SELECT 'Duplicate'
122 FROM ce_transaction_codes tc
123 WHERE tc.trx_code = X_trx_code
124 AND tc. bank_account_id = X_bank_account_id
125 AND (X_Row_id IS NULL OR tc.ROWID <> CHARTOROWID(X_Row_id))
126 AND EXISTS(
127 SELECT 'x'
128 FROM ce_transaction_codes tc2
129 WHERE tc2.trx_code = tc.trx_code
130 AND tc2.bank_account_id = tc.bank_account_id
131 AND tc2.TRX_TYPE <> X_trx_type);
132
133 -- check Code, Priority combiniation
134 CURSOR chk_duplicates2 IS
135 SELECT 'Duplicate'
136 FROM ce_transaction_codes tc
137 WHERE tc.trx_code = X_trx_code
138 AND tc.bank_account_id = X_bank_account_id
139 AND NVL(tc.RECONCILIATION_SEQUENCE,0) = NVL(X_RECONCILIATION_SEQUENCE,0)
140 AND (X_Row_id IS NULL OR tc.ROWID <> CHARTOROWID(X_Row_id));
141
142
143 -- check Code, Transaction Source combiniation
144 CURSOR chk_duplicates3 IS
145 SELECT 'Duplicate'
146 FROM ce_transaction_codes tc
147 WHERE tc.trx_code = X_trx_code
148 AND tc. bank_account_id = X_bank_account_id
149 AND NVL(tc.RECONCILE_FLAG, 'X') = NVL(X_RECONCILE_FLAG, 'X')
150 AND (X_Row_id IS NULL OR tc.ROWID <> CHARTOROWID( X_Row_id ) );
151
152
153 dummy VARCHAR2(100);
154 --
155 BEGIN
156 cep_standard.debug('>>CE_COPY_TRX_CODES_XML.check_unique_trxn_code');
157
158 OPEN chk_duplicates;
159 FETCH chk_duplicates INTO dummy;
160
161 IF chk_duplicates%FOUND THEN
162 cep_standard.debug( 'Duplicate: CE_DUP_BANK_TRX_CODE');
163 CLOSE chk_duplicates;
164 RETURN 'CE_DUP_BANK_TRX_CODE';
165 END IF;
166
167 CLOSE chk_duplicates;
168
169 OPEN chk_duplicates3;
170 FETCH chk_duplicates3 INTO dummy;
171
172 IF chk_duplicates3%FOUND THEN
173 cep_standard.debug( 'Duplicate: CE_DUP_BANK_TRX_CODE_COMB1');
174 CLOSE chk_duplicates3;
178 CLOSE chk_duplicates3;
175 RETURN 'CE_DUP_BANK_TRX_CODE_COMB1';
176 END IF;
177
179
180 OPEN chk_duplicates2;
181 FETCH chk_duplicates2 INTO dummy;
182
183 IF chk_duplicates2%FOUND THEN
184 cep_standard.debug( 'Duplicate: CE_DUP_BANK_TRX_CODE_COMB2');
185 CLOSE chk_duplicates2;
186 RETURN 'CE_DUP_BANK_TRX_CODE_COMB2';
187 END IF;
188
189 CLOSE chk_duplicates2;
190
191 cep_standard.debug('<<CE_COPY_TRX_CODES_XML.check_unique_trxn_code');
192 RETURN 'SUCCESS';
193 EXCEPTION
194 WHEN OTHERS THEN
195 cep_standard.debug('EXCEPTION: CE_COPY_TRX_CODES_XML.check_unique_trxn_code');
196 IF ( chk_duplicates%ISOPEN ) THEN
197 CLOSE chk_duplicates;
198 END IF;
199 IF ( chk_duplicates2%ISOPEN ) THEN
200 CLOSE chk_duplicates2;
201 END IF;
202 IF ( chk_duplicates3%ISOPEN ) THEN
203 CLOSE chk_duplicates3;
204 END IF;
205 RAISE;
206 END check_unique_trxn_code;
207
208 /*========================================================================+
209 | PRIVATE PROCEDURE |
210 | copy_trx_codes |
211 | |
212 | DESCRIPTION |
213 | This procedures copies the bank transaction codes setup for the |
214 | source account to the destination accounts. |
215 | |
216 | ARGUMENTS |
217 | |
218 | CALLS |
219 | CE_TRANSACTION_CODES_PKG.insert_row |
220 | CE_COPY_TRX_CODES_XML.check_unique_trxn_code |
221 +========================================================================*/
222 PROCEDURE copy_trx_codes
223 IS
224 -- cursor to fetch transaction codes
225 CURSOR get_source_trx_codes IS
226 SELECT trx_code,
227 trx_type,
228 description,
229 receivables_trx_id,
230 receipt_method_id,
231 create_misc_trx_flag,
232 reconcile_flag,
233 float_days,
234 matching_against,
235 correction_method,
236 start_date,
237 end_date,
238 attribute_category,
239 attribute1,
240 attribute2,
241 attribute3,
242 attribute4,
243 attribute5,
244 attribute6,
245 attribute7,
246 attribute8,
247 attribute9,
248 attribute10,
249 attribute11,
250 attribute12,
251 attribute13,
252 attribute14,
253 attribute15,
254 payroll_payment_format_id,
255 reconciliation_sequence
256 FROM ce_transaction_codes
257 WHERE bank_account_id = p_source_acct_id;
258
259 --cursor to fetch destination accounts
260 CURSOR get_dest_accounts IS
261 SELECT CBA.bank_account_id
262 FROM ce_bank_accounts_v CBA
263 WHERE CBA.bank_account_id = NVL(p_dest_acct_id, CBA.bank_account_id)
264 AND ((CBA.bank_account_type IS NULL AND p_dest_acct_type IS NULL)
265 OR CBA.bank_account_type = NVL(p_dest_acct_type, CBA.bank_account_type))
266 AND CBA.bank_branch_id = NVL(p_dest_branch_id, CBA.bank_branch_id)
267 AND CBA.bank_id = NVL(p_dest_bank_id, CBA.bank_id)
268 AND CBA.bank_account_id <> p_source_acct_id;
269
270 -- local variables
271 l_row_id VARCHAR2(18);
272 l_transaction_code_id NUMBER(15);
273 l_validate_uniq VARCHAR2(30);
274 l_last_update_login NUMBER;
275 l_user_id NUMBER;
276
277
278 BEGIN
279 debug_log('>>CE_COPY_TRX_CODES_XML.copy_trx_codes');
280
281 --parameter values
282 debug_log('p_source_acct_id::'||p_source_acct_id);
283 debug_log('p_dest_bank_id::'||p_dest_bank_id);
284 debug_log('p_dest_branch_id::'||p_dest_branch_id);
285 debug_log('p_dest_acct_id::'||p_dest_acct_id);
286 debug_log('p_dest_acct_type::'||p_dest_acct_type);
287
288 -- WHO columns
289 l_last_update_login := FND_GLOBAL.CONC_LOGIN_ID;
290 l_user_id := FND_GLOBAL.USER_ID;
291
292 --fetch transaction codes from source account
293 FOR src_trx_code IN get_source_trx_codes
294 LOOP
295 debug_log('TrxCode::'||SRC_TRX_CODE.trx_code||'-----------------');
296 --fetch destination accounts to which codes have to be copied
297 FOR dest_acct IN get_dest_accounts
298 LOOP
299
300 debug_log('..DestAcct::'||DEST_ACCT.bank_account_id);
301 l_validate_uniq := NULL;
302 l_validate_uniq := check_unique_trxn_code(
306 X_trx_type => SRC_TRX_CODE.trx_type,
303 X_trx_code => SRC_TRX_CODE.trx_code,
304 X_bank_account_id => DEST_ACCT.bank_account_id,
305 X_row_id => NULL,
307 X_reconcile_flag => SRC_TRX_CODE.reconcile_flag,
308 X_reconciliation_sequence => SRC_TRX_CODE.reconciliation_sequence);
309 IF l_validate_uniq = 'SUCCESS' THEN
310 --no exception: transaction code can be inserted
311 CE_TRANSACTION_CODES_PKG.insert_row(
312 X_Rowid => l_row_id,
313 X_Transaction_Code_Id => l_Transaction_Code_Id,
314 X_Bank_Account_Id => DEST_ACCT.Bank_Account_Id,
315 X_Trx_Code => SRC_TRX_CODE.Trx_Code,
316 X_Trx_Type => SRC_TRX_CODE.Trx_Type,
317 X_Description => SRC_TRX_CODE.Description,
318 X_Receivables_Trx_Id => SRC_TRX_CODE.Receivables_Trx_Id,
319 X_Receipt_Method_Id => SRC_TRX_CODE.Receipt_Method_Id,
320 X_Create_Misc_Trx_Flag => SRC_TRX_CODE.Create_Misc_Trx_Flag,
321 X_Reconcile_Flag => SRC_TRX_CODE.Reconcile_Flag,
322 X_Float_Days => SRC_TRX_CODE.Float_Days,
323 X_Matching_Against => SRC_TRX_CODE.Matching_Against,
324 X_Correction_Method => SRC_TRX_CODE.Correction_Method,
325 X_Start_Date => SRC_TRX_CODE.Start_Date,
326 X_End_Date => SRC_TRX_CODE.End_Date,
327 X_Attribute_Category => SRC_TRX_CODE.Attribute_Category,
328 X_Attribute1 => SRC_TRX_CODE.Attribute1,
329 X_Attribute2 => SRC_TRX_CODE.Attribute2,
330 X_Attribute3 => SRC_TRX_CODE.Attribute3,
331 X_Attribute4 => SRC_TRX_CODE.Attribute4,
332 X_Attribute5 => SRC_TRX_CODE.Attribute5,
333 X_Attribute6 => SRC_TRX_CODE.Attribute6,
334 X_Attribute7 => SRC_TRX_CODE.Attribute7,
335 X_Attribute8 => SRC_TRX_CODE.Attribute8,
336 X_Attribute9 => SRC_TRX_CODE.Attribute9,
337 X_Attribute10 => SRC_TRX_CODE.Attribute10,
338 X_Attribute11 => SRC_TRX_CODE.Attribute11,
339 X_Attribute12 => SRC_TRX_CODE.Attribute12,
340 X_Attribute13 => SRC_TRX_CODE.Attribute13,
341 X_Attribute14 => SRC_TRX_CODE.Attribute14,
342 X_Attribute15 => SRC_TRX_CODE.Attribute15,
343 X_Last_Updated_By => l_user_id,
344 X_Last_Update_Date => sysdate,
345 X_Last_Update_Login => l_last_update_login,
346 X_Created_By => l_user_id,
347 X_Creation_Date => sysdate,
348 X_payroll_payment_format_Id => SRC_TRX_CODE.payroll_payment_format_Id,
349 X_reconciliation_sequence => SRC_TRX_CODE.reconciliation_sequence,
350 X_request_id => g_conc_request_id
351 );
352 ELSE --uniqness validation failed
353 debug_log('Inserting into error table');
354
355 --insert status into reports table.
356 INSERT INTO ce_trx_codes_util_gt (
357 request_id,
358 bank_account_id,
359 trx_code,
360 trx_type,
361 reconcile_flag,
362 reconciliation_sequence,
363 description,
364 status
365 ) VALUES (
366 g_conc_request_id,
367 DEST_ACCT.bank_account_id,
368 SRC_TRX_CODE.trx_code,
369 SRC_TRX_CODE.trx_type,
370 SRC_TRX_CODE.reconcile_flag,
371 SRC_TRX_CODE.reconciliation_sequence,
372 SRC_TRX_CODE.description,
373 l_validate_uniq
374 );
375 END IF;
376 END LOOP;
377 END LOOP;
378 debug_log('<<CE_COPY_TRX_CODES_XML.copy_trx_codes');
379
380 END copy_trx_codes;
381
382 /*========================================================================+
383 | PUBLIC FUNCTION |
384 | beforeReport |
385 | |
386 | DESCRIPTION |
387 | Before Report trigger for CECPTXCD. |
388 | |
389 | CALLS |
390 | CE_COPY_TRX_CODES_XML.init_variables |
391 | CE_COPY_TRX_CODES_XML.copy_trx_codes |
392 +========================================================================*/
393 FUNCTION beforeReport RETURN BOOLEAN IS
394 BEGIN
395 debug_log('>>CE_COPY_TRX_CODES_XML.beforeReport');
396
397 --initializations
398 cep_standard.init_security;
399 init_variables;
400
401 --copy the transaction codes
402 copy_trx_codes;
403
404 --store count of copied codes
405 SELECT count(*)
406 INTO G_INSERT_COUNT
407 FROM ce_transaction_codes
408 WHERE request_id = G_CONC_REQUEST_ID;
409 debug_log('g_insert_count::'||g_insert_count);
410
411 debug_log('<<CE_COPY_TRX_CODES_XML.beforeReport');
412 RETURN (TRUE);
413 EXCEPTION
414 WHEN OTHERS THEN
415 ROLLBACK;
416 debug_log('EXCEPTION: CE_COPY_TRX_CODES_XML.beforeReport trigger. Error : ' || SUBSTR(SQLERRM, 1, 200));
417 RETURN (FALSE);
418 END beforeReport;
419
420 /*========================================================================+
421 | PUBLIC FUNCTION |
422 | afterReport |
423 | |
424 | DESCRIPTION |
425 | After Report trigger for CECPTXCD. Once processing is done |
426 | commit the changes and purge GT table |
427 | |
428 | ARGUMENTS |
429 +========================================================================*/
430 FUNCTION afterReport RETURN BOOLEAN IS
431 BEGIN
432 debug_log('>>CE_COPY_TRX_CODES_XML.afterReport');
433 DELETE ce_trx_codes_util_gt;
434 COMMIT;
435 debug_log('<<CE_COPY_TRX_CODES_XML.afterReport');
436 RETURN (TRUE);
437 END afterReport;
438
439 END CE_COPY_TRX_CODES_XML;