[Home] [Help]
PACKAGE BODY: APPS.IBY_FNDCPT_EXTRACT_GEN_PVT
Source
1 PACKAGE BODY IBY_FNDCPT_EXTRACT_GEN_PVT AS
2 /* $Header: ibyfcxgb.pls 120.48.12020000.7 2013/01/29 12:34:27 gmamidip ship $ */
3
4 G_DEBUG_MODULE CONSTANT VARCHAR2(100) := 'iby.plsql.IBY_FNDCPT_EXTRACT_GEN_PVT';
5
6 G_SRA_DELIVERY_METHOD_ATTR CONSTANT NUMBER := 1;
7 G_SRA_EMAIL_ATTR CONSTANT NUMBER := 2;
8 G_SRA_FAX_ATTR CONSTANT NUMBER := 3;
9 G_SRA_REQ_FLAG_ATTR CONSTANT NUMBER := 4;
10 G_SRA_PS_LANG_ATTR CONSTANT NUMBER := 5;
11 G_SRA_PS_TERRITORY_ATTR CONSTANT NUMBER := 6;
12 G_SRA_EMAIL_SUBJECT_ATTR CONSTANT NUMBER := 7;
13 G_SRA_PN_CONDITION CONSTANT NUMBER := 8;
14 G_SRA_PN_NUM_DOCUMENTS CONSTANT NUMBER := 9;
15 G_PF_FORMAT_ATTR CONSTANT NUMBER := 10;
16
17
18 G_SRA_DELIVERY_METHOD_PRINTED CONSTANT VARCHAR2(30) := 'PRINTED';
19 G_SRA_DELIVERY_METHOD_EMAIL CONSTANT VARCHAR2(30) := 'EMAIL';
20 G_SRA_DELIVERY_METHOD_FAX CONSTANT VARCHAR2(30) := 'FAX';
21
22 G_EXTRACT_MODE_PMT CONSTANT NUMBER := 1;
23 G_EXTRACT_MODE_SRA CONSTANT NUMBER := 2;
24
25 G_Extract_Run_Mode NUMBER := G_EXTRACT_MODE_PMT;
26 G_Extract_Run_Delivery_Method VARCHAR2(30);
27 G_Extract_Run_Payment_id NUMBER;
28
29 --
30 -- Type: Doc_headers_rec_type
31 --
32
33 TYPE Doc_headers_rec_type IS RECORD (
34 CALLING_APP_DOC_REF_NUMBER VARCHAR2(30),
35 DOCUMENT_DATE DATE,
36 CREATION_DATE DATE,
37 DOCUMENT_TYPE CHAR(7),
38 DOCUMENT_DESCRIPTION VARCHAR2(240),
39 DOCUMENT_AMOUNT NUMBER,
40 DOCUMENT_CURRENCY_CODE VARCHAR2(15),
41 SETTLEMENT_AMOUNT NUMBER,
42 DISCOUNT_AMOUNT NUMBER,
43 FREIGHT_AMOUNT VARCHAR2(240),
44 DOCUMENT_DISCOUNT_EARNED NUMBER,
45 LOCAL_TAX_AMOUNT NUMBER,
46 NATIONAL_TAX_AMOUNT VARCHAR2(240),
47 VAT_TAX_AMOUNT NUMBER,
48 ADDRESS1 VARCHAR2(240),
49 ADDRESS2 VARCHAR2(240),
50 ADDRESS3 VARCHAR2(240),
51 CITY VARCHAR2(60),
52 COUNTY VARCHAR2(60),
53 STATE VARCHAR2(60),
54 COUNTRY VARCHAR2(60),
55 POSTAL_CODE VARCHAR2(60),
56 TO_ADDRESS1 VARCHAR2(240),
57 TO_ADDRESS2 VARCHAR2(240),
58 TO_ADDRESS3 VARCHAR2(240),
59 TO_CITY VARCHAR2(60),
60 TO_COUNTY VARCHAR2(60),
61 TO_STATE VARCHAR2(60),
62 TO_COUNTRY VARCHAR2(60),
63 TO_POSTAL_CODE VARCHAR2(60),
64 ORDER_TANGIBLE_ID VARCHAR2(80),
65 TRANSACTIONID NUMBER,
66 INITIATOR_EXTENSION_ID NUMBER(15),
67 DOC_UNIQUE_REF NUMBER(15),
68 BR_SIGNED_FLAG VARCHAR2(1),
69 BR_DRAWEE_ISSUED_FLAG VARCHAR2(1)
70 );
71
72 --
73 -- Type: Doc_lines_rec_type
74 --
75
76 TYPE Doc_lines_rec_type IS RECORD (
77 LINE_NUMBER NUMBER,
78 PO_NUMBER VARCHAR2(50),
79 LINE_TYPE VARCHAR2(20),
80 DESCRIPTION VARCHAR2(240),
81 EXTENDED_AMOUNT NUMBER,
82 INVOICE_CURRENCY_CODE VARCHAR2(15),
83 UNIT_PRICE NUMBER,
84 QUANTITY NUMBER,
85 UNIT_OF_MEASURE VARCHAR2(3),
86 INVENTORY_ITEM_ID NUMBER(15),
87 DISCOUNT_AMOUNT NUMBER,
88 SALES_TAX_AMOUNT NUMBER,
89 TAX_RATE NUMBER,
90 VAT_TAX_AMOUNT NUMBER,
91 DOC_UNIQUE_REF NUMBER(15)
92 );
93
94 --
95 -- Type: Table teyps
96 --
97
98 TYPE DocHeadersTabType IS TABLE OF Doc_headers_rec_type;
99
100 TYPE DocLinesTabType IS TABLE OF Doc_lines_rec_type;
101
102 PROCEDURE Insert_Into_Lines_Gt
103 (
104 p_txn_id IN NUMBER
105 );
106
107 PROCEDURE Insert_Into_Headers_Gt
108 (
109 p_txn_id IN NUMBER
110 );
111
112 PROCEDURE Insert_Into_Gt
113 (
114 p_txn_id IN NUMBER
115 );
116
117 PROCEDURE Setup_for_Extract
118 (
119 p_sys_key IN iby_security_pkg.DES3_KEY_TYPE
120 );
121
122 FUNCTION Get_Payer_Notif_Where_cluase
123 (
124 p_mbatchid IN VARCHAR2,
125 p_fromDate IN VARCHAR2,
126 p_toDate IN VARCHAR2,
127 p_fromPSON IN VARCHAR2,
128 p_toPSON IN VARCHAR2,
129 p_delivery_method IN VARCHAR2,
130 p_format_code IN VARCHAR2,
131 p_debug_module IN VARCHAR2
132 ) RETURN VARCHAR2;
133
134
135 FUNCTION Get_Payer_Notif_Where_cluase
136 (
137 p_mbatchid IN VARCHAR2,
138 p_fromDate IN VARCHAR2,
139 p_toDate IN VARCHAR2,
140 p_fromPSON IN VARCHAR2,
141 p_toPSON IN VARCHAR2,
142 p_delivery_method IN VARCHAR2,
143 p_format_code IN VARCHAR2,
144 p_debug_module IN VARCHAR2
145 ) RETURN VARCHAR2
146 IS
147 l_Debug_Module VARCHAR2(255);
148 l_where_clause VARCHAR2(4000);
149
150 BEGIN
151
152 l_Debug_Module := p_debug_module;
153
154 iby_debug_pub.add(debug_msg => 'p_mbatchid: ' || p_mbatchid,
155 debug_level => FND_LOG.LEVEL_STATEMENT,
156 module => l_Debug_Module);
157
158 iby_debug_pub.add(debug_msg => 'p_fromDate: ' || p_fromDate,
159 debug_level => FND_LOG.LEVEL_STATEMENT,
160 module => l_Debug_Module);
161
162 iby_debug_pub.add(debug_msg => 'p_toDate: ' || p_toDate,
163 debug_level => FND_LOG.LEVEL_STATEMENT,
164 module => l_Debug_Module);
165
166 iby_debug_pub.add(debug_msg => 'p_fromPSON: ' || p_fromPSON,
167 debug_level => FND_LOG.LEVEL_STATEMENT,
168 module => l_Debug_Module);
169
170 iby_debug_pub.add(debug_msg => 'p_toPSON: ' || p_toPSON,
171 debug_level => FND_LOG.LEVEL_STATEMENT,
172 module => l_Debug_Module);
173
174 iby_debug_pub.add(debug_msg => 'p_delivery_method: ' || p_delivery_method,
175 debug_level => FND_LOG.LEVEL_STATEMENT,
176 module => l_Debug_Module);
177
178 iby_debug_pub.add(debug_msg => 'p_format_code: ' || p_format_code,
179 debug_level => FND_LOG.LEVEL_STATEMENT,
180 module => l_Debug_Module);
181
182 -- back out this changes due to performance reasons
183 -- from date is required program parameter
184 -- to date is defaulted to sysdate
185 -- this select assumes if the variables are not passed they are null so the variables should be set to null in that
186 -- case in the module where the dynamic sql is executed.
187 --l_where_clause := l_where_clause||' and txn.reqdate >= nvl(to_date(:p_fromDate, ''YYYY/MM/DD HH24:MI:SS''), SYSDATE) '
188 -- ||' and txn.reqdate <= nvl(to_date(:p_toDate, ''YYYY/MM/DD HH24:MI:SS''), SYSDATE) '
189 -- ||' and NVL(txn.mbatchid, -1) = NVL(:p_mbatchid, NVL(txn.mbatchid,-1)) '
190 -- ||' and txn.tangibleid >= NVL(:p_fromPSON, txn.tangibleid) '
191 -- ||' and txn.tangibleid <= NVL(:p_toPSON, txn.tangibleid) '
192 -- ||' and nvl(iby_fndcpt_extract_gen_pvt.Get_sra_Attribute(txn.trxnmid,1), ''x'') = nvl(:p_delivery_method, ''x'') ';
193
194 -- from date is required program parameter
195 l_where_clause := l_where_clause || ' and txn.reqdate >= nvl(to_date( :p_fromDate, ''YYYY/MM/DD HH24:MI:SS''), SYSDATE) ';
196
197 IF nvl(upper(p_toDate), 'NULL') <> 'NULL' THEN
198 l_where_clause := l_where_clause || ' and txn.reqdate <= nvl(to_date('|| ''''||p_toDate|| ''''||', ''YYYY/MM/DD HH24:MI:SS''), SYSDATE) ';
199 l_where_clause := REPLACE(l_where_clause, 'p_toDate', REPLACE(p_toDate, '00:00:00', '23:59:59'));
200 END IF;
201
202 IF nvl(upper(p_mbatchid), 'NULL') <> 'NULL' THEN
203 l_where_clause := l_where_clause || ' and txn.mbatchid = ' || p_mbatchid;
204 END IF;
205
206 IF nvl(upper(p_fromPSON), 'NULL') <> 'NULL' THEN
207 l_where_clause := l_where_clause || ' and txn.tangibleid >= ' || '''' || p_fromPSON|| '''';
208 END IF;
209
210 IF nvl(upper(p_toPSON), 'NULL') <> 'NULL' THEN
211 l_where_clause := l_where_clause || ' and txn.tangibleid <= ' || '''' || p_toPSON|| '''';
212 END IF;
213
214 -- p_delivery_method must not be null
215 -- we don't create extract if p_delivery_method is null
216 l_where_clause := l_where_clause || ' and nvl(iby_fndcpt_extract_gen_pvt.Get_sra_Attribute(txn.trxnmid,1), ''x'') = nvl (:p_delivery_method, ''x'') ';
217
218 -- p_format_code must not be null
219 -- we don't create extract if p_format_code is null
220 l_where_clause := l_where_clause || ' and iby_fndcpt_extract_gen_pvt.Get_sra_Attribute(txn.trxnmid,10) = :p_format_code ';
221
222 RETURN l_where_clause;
223
224 END Get_Payer_Notif_Where_cluase;
225
226
227 -- bug 5115161: payer notification
228 PROCEDURE Create_Payer_Notif_Extract_1_0
229 (
230 p_mbatchid IN VARCHAR2,
231 p_fromDate IN VARCHAR2,
232 p_toDate IN VARCHAR2,
233 p_fromPSON IN VARCHAR2,
234 p_toPSON IN VARCHAR2,
235 p_delivery_method IN VARCHAR2,
236 p_format_code IN VARCHAR2,
237 p_txn_id IN NUMBER,
238 p_sys_key IN iby_security_pkg.DES3_KEY_TYPE,
239 x_extract_doc OUT NOCOPY CLOB
240 )
241 IS
242 l_Debug_Module VARCHAR2(255) := G_DEBUG_MODULE || '.Create_Payer_Notif_Extract_1_0';
243 l_xml XMLTYPE;
244 l_where_clause VARCHAR2(4000);
245 l_extract_query VARCHAR2(4000) :=
246 'select XMLElement("FundsCapturePayerNotification", ' ||
247 ' XMLElement("FormatProgramRequestID", fnd_global.CONC_REQUEST_ID), ' ||
248 ' XMLAgg(xml_order.FNDCPT_ORDER)) ' ||
249 ' from iby_trxn_summaries_all txn, IBY_XML_FNDCPT_ORDER_PN_1_0_V xml_order ' ||
250 ' where txn.trxnmid = xml_order.trxnmid ' ||
251 ' and nvl(txn.payer_notification_required, ''N'') = ''Y'' ';
252
253 -- l_trxn_id iby_trxn_summaries_all.trxnmid%TYPE;
254 -- l_mbatchid VARCHAR2(100);
255 -- l_toDate VARCHAR2(100);
256 -- l_delivery_method VARCHAR2(30);
257 -- l_fromPSON iby_trxn_summaries_all.tangibleid%TYPE;
258 -- l_toPSON iby_trxn_summaries_all.tangibleid%TYPE;
259
260 BEGIN
261
262 iby_debug_pub.add(debug_msg => 'Enter: ' || l_Debug_Module,
263 debug_level => FND_LOG.LEVEL_PROCEDURE,
264 module => l_Debug_Module);
265
266 CEP_STANDARD.init_security;
267
268 l_where_clause := Get_Payer_Notif_Where_cluase
269 (
270 p_mbatchid => p_mbatchid,
271 p_fromDate => p_fromDate,
272 p_toDate => p_toDate,
273 p_fromPSON => p_fromPSON,
274 p_toPSON => p_toPSON,
275 p_delivery_method => p_delivery_method,
276 p_format_code => p_format_code,
277 p_debug_module => l_Debug_Module
278 );
279
280 l_extract_query := l_extract_query || l_where_clause;
281
282 IF p_txn_id <> -99 THEN
283 l_extract_query := l_extract_query || ' and txn.trxnmid = ' || p_txn_id;
284 END IF;
285
286 -- back out this changes for performance reasons
287 -- this is required since the variables come to the word null to determine
288 -- it is a null value
289 -- IF nvl(upper(p_mbatchid), 'NULL') <> 'NULL' THEN
290 -- l_mbatchid := p_mbatchid;
291 -- END IF;
292
293 -- IF nvl(upper(p_toDate), 'NULL') <> 'NULL' THEN
294 -- IF instr(p_toDate, '00:00:00') <> 0 THEN
295 -- l_toDate := REPLACE(p_toDate, '00:00:00', '23:59:59');
296 -- ELSE
297 -- l_toDate := p_toDate;
298 -- END IF;
299 -- END IF;
300
301 -- IF nvl(upper(p_fromPSON), 'NULL') <> 'NULL' THEN
302 -- l_fromPSON := p_fromPSON;
303 -- END IF;
304
305 -- IF nvl(upper(p_toPSON), 'NULL') <> 'NULL' THEN
306 -- l_toPSON := p_toPSON;
307 -- END IF;
308
309 -- IF nvl(upper(p_delivery_method), 'NULL') <> 'NULL' THEN
310 -- l_delivery_method := p_delivery_method;
311 -- END IF;
312
313 -- IF p_txn_id <> -99 THEN
314 -- l_trxn_id := p_txn_id;
315 -- END IF;
316
317 -- l_extract_query := l_extract_query||' and txn.trxnmid = NVL(:p_txn_id, txn.trxnmid) ';
318
319 G_Extract_Run_Mode := G_EXTRACT_MODE_SRA;
320 G_Extract_Run_Delivery_Method := p_delivery_method;
321 G_Extract_Run_Payment_id := p_txn_id;
322
323 Setup_for_Extract(p_sys_key);
324
325 iby_debug_pub.add(debug_msg => 'After Setup_for_Extract() ',
326 debug_level => FND_LOG.LEVEL_STATEMENT,
327 module => l_Debug_Module);
328
329 -- Calling Insert_Into_Gt procedure to include Document Receivable tag
330 -- in FC Payer Notification Extract.
331 FND_FILE.PUT_LINE(FND_FILE.LOG, l_Debug_Module || 'Call to Insert_Into_Gt start, TimeStamp:' ||systimestamp);
332 Insert_Into_Gt(p_mbatchid);
333 FND_FILE.PUT_LINE(FND_FILE.LOG, l_Debug_Module || 'Call to Insert_Into_Gt end, TimeStamp:' ||systimestamp);
334
335 iby_debug_pub.add(debug_msg => 'Before executing dynamic query.',
336 debug_level => FND_LOG.LEVEL_STATEMENT,
337 module => l_Debug_Module);
338
339 iby_debug_pub.add(debug_msg => 'l_extract_query: ' || l_extract_query,
340 debug_level => FND_LOG.LEVEL_STATEMENT,
341 module => l_Debug_Module);
342
343 --iby_debug_pub.add(debug_msg => 'variables: '||p_fromDate||':'||l_toDate||':'||
344 -- l_mbatchid||':'||l_fromPSON||':'||l_toPSON||':'||
345 -- l_delivery_method||':'||p_format_code||':'||l_trxn_id,
346 -- debug_level => FND_LOG.LEVEL_STATEMENT,
347 -- module => l_Debug_Module);
348
349 EXECUTE IMMEDIATE l_extract_query INTO l_xml USING p_fromDate, p_delivery_method, p_format_code;
350
351 -- EXECUTE IMMEDIATE l_extract_query INTO l_xml
352 -- USING p_fromDate, l_toDate, l_mbatchid,
353 -- l_fromPSON, l_toPSON,
354 -- l_delivery_method, p_format_code,
355 -- l_trxn_id;
356
357 x_extract_doc := XMLType.getClobVal(l_xml);
358
359 iby_debug_pub.add(debug_msg => 'Exit: ' || l_Debug_Module,
360 debug_level => FND_LOG.LEVEL_PROCEDURE,
361 module => l_Debug_Module);
362
363 -- clears out data from global temporary table
364 COMMIT;
365
366 EXCEPTION
367 WHEN OTHERS THEN
368 -- make sure procedure is not exited before a COMMIT
369 -- so as to remove security keys
370 COMMIT;
371 RAISE;
372
373 END Create_Payer_Notif_Extract_1_0;
374
375
376 -- shared. Main entry point for FC accompany letter
377 PROCEDURE Setup_for_Extract
378 (
379 p_sys_key IN iby_security_pkg.DES3_KEY_TYPE
380 )
381 IS
382 lx_err_code VARCHAR2(30);
383 l_xml_base VARCHAR2(255);
384 l_char_extract_mdoe VARCHAR2(255) := 'G_EXTRACT_MODE_PMT';
385 l_Debug_Module VARCHAR2(255) :=
386 G_DEBUG_MODULE || '.Setup_for_Extract [Shared]';
387 BEGIN
388
389 iby_utility_pvt.get_property('IBY_XML_BASE',l_xml_base);
390
391 iby_utility_pvt.set_view_param(G_VP_XML_BASE,NVL(l_xml_base,''));
392
393 IF G_Extract_Run_Mode = G_EXTRACT_MODE_SRA THEN
394 l_char_extract_mdoe := 'G_EXTRACT_MODE_SRA';
395 END IF;
396
397 iby_utility_pvt.set_view_param(G_VP_EXTRACT_MODE, l_char_extract_mdoe);
398
399 iby_debug_pub.add(debug_msg => 'The extract mode is: ' || G_Extract_Run_Mode,
400 debug_level => FND_LOG.LEVEL_STATEMENT,
401 module => l_Debug_Module);
402
403 IF (NOT p_sys_key IS NULL) THEN
404 iby_security_pkg.validate_sys_key(p_sys_key,lx_err_code);
405 IF (NOT lx_err_code IS NULL) THEN
406 raise_application_error(-20000,lx_err_code, FALSE);
407 END IF;
408 iby_utility_pvt.set_view_param(G_VP_SYS_KEY,p_sys_key);
409 END IF;
410
411 END Setup_for_Extract;
412
413 -- shared. Main entry point for FC accompany letter
414 PROCEDURE Create_Extract_1_0
415 (
416 p_instr_type IN VARCHAR2,
417 p_req_type IN VARCHAR2,
418 p_txn_id IN NUMBER,
419 p_sys_key IN iby_security_pkg.DES3_KEY_TYPE,
420 x_extract_doc OUT NOCOPY CLOB
421 )
422 IS
423 l_Debug_Module VARCHAR2(255) :=
424 G_DEBUG_MODULE || '.Create_Extract_1_0 [Shared]';
425 BEGIN
426
427 Setup_for_Extract(p_sys_key);
428
429 IF ((p_req_type = 'ORAPMTCLOSEBATCH')
430 OR (p_req_type = 'ORAPMTEFTCLOSEBATCH')
431 OR (p_req_type = 'ORAPMTPDCCLOSEBATCH')) THEN
432 --14671332 Making the call to headers_gt only in the time of batch closure.
433 FND_FILE.PUT_LINE(FND_FILE.LOG, l_Debug_Module || 'Call to Insert_Into_Gt start, TimeStamp:' ||systimestamp);
434 Insert_Into_Gt(p_txn_id);
435 FND_FILE.PUT_LINE(FND_FILE.LOG, l_Debug_Module || 'Call to Insert_Into_Gt end, TimeStamp:' ||systimestamp);
436
437 FND_FILE.PUT_LINE(FND_FILE.LOG, l_Debug_Module || 'call to iby_xml_batch_fci_1_0_v START' || ':TimeStamp:' ||systimestamp);
438 --Bug# 13640788
439 -- Removed Optimizer hints as per the pef team sugestion
440 SELECT
441 XMLType.getClobVal(instruction)
442 INTO x_extract_doc
443 FROM iby_xml_batch_fci_1_0_v
444 WHERE mbatchid=p_txn_id
445 AND rownum=1;
446 ELSE
447 SELECT
448 XMLType.getClobVal(instruction)
449 INTO x_extract_doc
450 FROM iby_xml_online_fci_1_0_v
451 WHERE trxnmid=p_txn_id
452 AND rownum=1;
453 END IF;
454 FND_FILE.PUT_LINE(FND_FILE.LOG, l_Debug_Module || 'call to iby_xml_batch_fci_1_0_v END' || ':TimeStamp:' ||systimestamp);
455
456 -- clears out data from global temporary table
457 COMMIT;
458
459 EXCEPTION
460 WHEN OTHERS THEN
461 -- make sure procedure is not exited before a COMMIT
462 -- so as to remove security keys
463 COMMIT;
464 RAISE;
465
466 END Create_Extract_1_0;
467
468
469 -- overloaded version with
470 -- p_sec_val for credit card cvv2
471 PROCEDURE Create_Extract_1_0
472 (
473 p_instr_type IN VARCHAR2,
474 p_req_type IN VARCHAR2,
475 p_txn_id IN NUMBER,
476 p_sys_key IN iby_security_pkg.DES3_KEY_TYPE,
477 p_sec_val IN VARCHAR2,
478 x_extract_doc OUT NOCOPY CLOB
479 )
480 IS
481 l_xml_base VARCHAR2(255);
482 lx_err_code VARCHAR2(30);
483 BEGIN
484
485 G_Extract_Run_Mode := G_EXTRACT_MODE_PMT;
486
487 iby_utility_pvt.set_view_param(G_VP_SEC_VAL ,NVL(p_sec_val,' '));
488
489 Create_Extract_1_0
490 (
491 p_instr_type => p_instr_type,
492 p_req_type => p_req_type,
493 p_txn_id => p_txn_id,
494 p_sys_key => p_sys_key,
495 x_extract_doc => x_extract_doc
496 );
497
498 END Create_Extract_1_0;
499
500 -- obselete per bug 5115161
501 FUNCTION Get_Ins_PayeeAcctAgg(p_mbatch_id IN NUMBER)
502 RETURN XMLTYPE
503 IS
504
505 l_payeeacct_agg XMLTYPE;
506 l_Debug_Module VARCHAR2(255) := G_DEBUG_MODULE || '.Get_Ins_PayeeAcctAgg';
507
508 -- the cursors should be kept in-sync with IBY_XML_FNDCPT_ACCT_1_0_V
509 -- electronic payer notification
510 CURSOR l_payeeacct_ele_csr (p_mbatch_id IN NUMBER) IS
511 SELECT
512 XMLElement("PayeeAccount",
513 XMLElement("PaymentSystemAccount",
514 XMLElement("AccountName",txn.bepkey),
515 Extract(XMLAgg(XMLElement("OptionSet",opts.account_options)),
516 'OptionSet[1]/*')
517 ),
518 CASE WHEN (NOT xml_bank.instrid IS NULL) THEN
519 Extract(XMLAgg(XMLElement("BankAccount",xml_bank.bank_account_content)),
520 '/BankAccount[1]')
521 END,
522 XMLElement("Payee",
523 XMLElement("Name",payee.name),
524 XMLElement("Address",
525 XMLElement("AddressLine1",null),
526 XMLForest(null AS "AddressLine2",null AS "AddressLine3"),
527 XMLElement("City",null),
528 XMLElement("State",null),
529 XMLElement("Country",null),
530 XMLElement("PostalCode",null)
531 ),
532 XMLForest(DECODE(payee.mcc_code, -1,null, payee.mcc_code) AS "MCC")
533 ),
534 XMLElement("OrderCount",count(txn.trxnmid)),
535 XMLElement("AccountTotals",
536 XMLElement("AuthorizationsTotal",
537 XMLElement("Value",
538 DECODE(txn.INSTRTYPE, 'PINLESSDEBITCARD', 0,
539 'BANKACCOUNT', 0,
540 SUM(DECODE(txn.trxntypeid, 2,txn.amount, 0)) )),
541 XMLElement("Currency",
542 XMLElement("Code",MAX(txn.currencynamecode))
543 )
544 ),
545 XMLElement("CapturesTotal",
546 XMLElement("Value",
547 DECODE(txn.INSTRTYPE, 'PINLESSDEBITCARD', SUM(DECODE(txn.trxntypeid, 2,txn.amount, 0)),
548 'BANKACCOUNT', SUM(DECODE(txn.REQTYPE, 'ORAPMTREQ',txn.amount, 0)),
549 SUM(DECODE(txn.trxntypeid, 3,txn.amount, 8,txn.amount, 0)) )),
550 XMLElement("Currency",
551 XMLElement("Code",MAX(txn.currencynamecode))
552 )
553 ),
554 XMLElement("CreditsTotal",
555 XMLElement("Value",
556 DECODE(txn.INSTRTYPE, 'PINLESSDEBITCARD', 0,
557 'BANKACCOUNT', SUM(DECODE(txn.REQTYPE, 'ORAPMTCREDIT',txn.amount, 0)),
558 SUM(DECODE(txn.trxntypeid, 5,txn.amount, 11,txn.amount, 0)) )),
559 XMLElement("Currency",
560 XMLElement("Code",MAX(txn.currencynamecode))
561 )
562 )
563 ),
564 XMLAgg(xml_order.fndcpt_order)
565 )--,
566 --txn.mbatchid,
567 --txn.payeeinstrid
568 FROM
569 iby_trxn_summaries_all txn,
570 iby_payee payee,
571 iby_bepkeys keys,
572 iby_xml_fndcpt_bankaccount_v xml_bank,
573 iby_xml_bep_acct_options_v opts,
574 iby_xml_fndcpt_order_1_0_v xml_order
575 WHERE (txn.payeeid = payee.payeeid)
576 AND (txn.payeeinstrid = xml_bank.instrid(+))
577 AND (txn.payeeid = keys.ownerid)
578 AND (txn.bepkey = keys.key)
579 AND (keys.ownertype = 'PAYEE')
580 AND (keys.bep_account_id = opts.bep_account_id(+))
581 AND (txn.trxnmid = xml_order.trxnmid)
582 AND txn.trxnmid = G_Extract_Run_Payment_id
583 AND txn.mbatchid = p_mbatch_id
584 GROUP BY
585 txn.mbatchid, txn.payeeinstrid, txn.instrtype, txn.bepkey,
586 payee.name, payee.mcc_code, opts.bep_account_id,
587 xml_bank.instrid;
588
589 CURSOR l_payeeacct_prt_csr (p_mbatch_id IN NUMBER) IS
590 SELECT
591 XMLElement("PayeeAccount",
592 XMLElement("PaymentSystemAccount",
593 XMLElement("AccountName",txn.bepkey),
594 Extract(XMLAgg(XMLElement("OptionSet",opts.account_options)),
595 'OptionSet[1]/*')
596 ),
597 CASE WHEN (NOT xml_bank.instrid IS NULL) THEN
598 Extract(XMLAgg(XMLElement("BankAccount",xml_bank.bank_account_content)),
599 '/BankAccount[1]')
600 END,
601 XMLElement("Payee",
602 XMLElement("Name",payee.name),
603 XMLElement("Address",
604 XMLElement("AddressLine1",null),
605 XMLForest(null AS "AddressLine2",null AS "AddressLine3"),
606 XMLElement("City",null),
607 XMLElement("State",null),
608 XMLElement("Country",null),
609 XMLElement("PostalCode",null)
610 ),
611 XMLForest(DECODE(payee.mcc_code, -1,null, payee.mcc_code) AS "MCC")
612 ),
613 XMLElement("OrderCount",count(txn.trxnmid)),
614 XMLElement("AccountTotals",
615 XMLElement("AuthorizationsTotal",
616 XMLElement("Value",
617 DECODE(txn.INSTRTYPE, 'PINLESSDEBITCARD', 0,
618 'BANKACCOUNT', 0,
619 SUM(DECODE(txn.trxntypeid, 2,txn.amount, 0)) )),
620 XMLElement("Currency",
621 XMLElement("Code",MAX(txn.currencynamecode))
622 )
623 ),
624 XMLElement("CapturesTotal",
625 XMLElement("Value",
626 DECODE(txn.INSTRTYPE, 'PINLESSDEBITCARD', SUM(DECODE(txn.trxntypeid, 2,txn.amount, 0)),
627 'BANKACCOUNT', SUM(DECODE(txn.REQTYPE, 'ORAPMTREQ',txn.amount, 0)),
628 SUM(DECODE(txn.trxntypeid, 3,txn.amount, 8,txn.amount, 0)) )),
629 XMLElement("Currency",
630 XMLElement("Code",MAX(txn.currencynamecode))
631 )
632 ),
633 XMLElement("CreditsTotal",
634 XMLElement("Value",
635 DECODE(txn.INSTRTYPE, 'PINLESSDEBITCARD', 0,
636 'BANKACCOUNT', SUM(DECODE(txn.REQTYPE, 'ORAPMTCREDIT',txn.amount, 0)),
637 SUM(DECODE(txn.trxntypeid, 5,txn.amount, 11,txn.amount, 0)) )),
638 XMLElement("Currency",
639 XMLElement("Code",MAX(txn.currencynamecode))
640 )
641 )
642 ),
643 XMLAgg(xml_order.fndcpt_order)
644 )--,
645 --txn.mbatchid,
646 --txn.payeeinstrid
647 FROM
648 iby_trxn_summaries_all txn,
649 iby_payee payee,
650 iby_bepkeys keys,
651 iby_xml_fndcpt_bankaccount_v xml_bank,
652 iby_xml_bep_acct_options_v opts,
653 iby_xml_fndcpt_order_1_0_v xml_order
654 WHERE (txn.payeeid = payee.payeeid)
655 AND (txn.payeeinstrid = xml_bank.instrid(+))
656 AND (txn.payeeid = keys.ownerid)
657 AND (txn.bepkey = keys.key)
658 AND (keys.ownertype = 'PAYEE')
659 AND (keys.bep_account_id = opts.bep_account_id(+))
660 AND (txn.trxnmid = xml_order.trxnmid)
661 AND Get_SRA_Attribute(txn.trxnmid, G_SRA_DELIVERY_METHOD_ATTR) = G_SRA_DELIVERY_METHOD_PRINTED
662 AND txn.mbatchid = p_mbatch_id
663 GROUP BY
664 txn.mbatchid, txn.payeeinstrid, txn.instrtype, txn.bepkey,
665 payee.name, payee.mcc_code, opts.bep_account_id,
666 xml_bank.instrid;
667
668 BEGIN
669
670 iby_debug_pub.add(debug_msg => 'Enter: ' || l_Debug_Module,
671 debug_level => FND_LOG.LEVEL_PROCEDURE,
672 module => l_Debug_Module);
673
674 iby_debug_pub.add(debug_msg => 'Extract mode is G_EXTRACT_MODE_SRA. ',
675 debug_level => FND_LOG.LEVEL_STATEMENT,
676 module => l_Debug_Module);
677
678 IF G_Extract_Run_Delivery_Method = G_SRA_DELIVERY_METHOD_PRINTED THEN
679
680 iby_debug_pub.add(debug_msg => 'Delivery method is printed. ',
681 debug_level => FND_LOG.LEVEL_STATEMENT,
682 module => l_Debug_Module);
683
684 OPEN l_payeeacct_prt_csr (p_mbatch_id);
685 FETCH l_payeeacct_prt_csr INTO l_payeeacct_agg;
686 CLOSE l_payeeacct_prt_csr;
687
688 iby_debug_pub.add(debug_msg => 'After fetch from payee account cursor. ',
689 debug_level => FND_LOG.LEVEL_STATEMENT,
690 module => l_Debug_Module);
691
692 ELSIF G_Extract_Run_Delivery_Method = G_SRA_DELIVERY_METHOD_EMAIL OR
693 G_Extract_Run_Delivery_Method = G_SRA_DELIVERY_METHOD_FAX THEN
694
695 iby_debug_pub.add(debug_msg => 'Delivery method is Email/Fax. ',
696 debug_level => FND_LOG.LEVEL_STATEMENT,
697 module => l_Debug_Module);
698
699 OPEN l_payeeacct_ele_csr (p_mbatch_id);
700 FETCH l_payeeacct_ele_csr INTO l_payeeacct_agg;
701 CLOSE l_payeeacct_ele_csr;
702
703 iby_debug_pub.add(debug_msg => 'After fetch from payee account cursor. ',
704 debug_level => FND_LOG.LEVEL_STATEMENT,
705 module => l_Debug_Module);
706 END IF;
707
708 RETURN l_payeeacct_agg;
709
710 iby_debug_pub.add(debug_msg => 'Exit: ' || l_Debug_Module,
711 debug_level => FND_LOG.LEVEL_PROCEDURE,
712 module => l_Debug_Module);
713
714 END Get_Ins_PayeeAcctAgg;
715
716
717 FUNCTION Get_SRA_Attribute(p_trxnmid IN NUMBER, p_attribute_type IN NUMBER)
718 RETURN VARCHAR2
719 IS
720 l_instrument_type VARCHAR2(30);
721 l_sra_delivery_method VARCHAR2(30);
722 l_pf_sra_format VARCHAR2(30);
723 l_override_payer_flag VARCHAR2(1);
724 l_pf_sra_delivery_method VARCHAR2(30);
725 l_ps_lang VARCHAR2(4);
726 l_ps_territory VARCHAR2(60);
727 l_sra_pn_condition VARCHAR2(30);
728 l_sra_pn_document_count VARCHAR2(10);
729 l_settle_ref VARCHAR2(80);
730 l_email_sub VARCHAR2(200);
731
732 CURSOR l_instrument_type_csr (p_trxnmid IN NUMBER) IS
733 SELECT bat.instrument_type
734 FROM iby_trxn_summaries_all txn, iby_batches_all bat
735 WHERE txn.trxnmid = p_trxnmid
736 AND txn.mbatchid = bat.mbatchid;
737
738 CURSOR l_eft_sra_setup_csr (p_trxnmid IN NUMBER) IS
739 SELECT sys_pf.OVERRIDE_PAYER_DELIVERY_FLAG, sys_pf.PAYER_NOTIFICATION_DEL_METHOD,
740 sys_pf.payer_notification_format, sys_pf.PAYER_NOTIFICATION_CONDITION, sys_pf.PN_COND_NUM_OF_RECEIPTS
741 FROM iby_trxn_summaries_all txn, iby_batches_all bat,
742 iby_fndcpt_sys_eft_pf_b sys_pf, iby_fndcpt_user_eft_pf_b user_pf
743 WHERE txn.trxnmid = p_trxnmid
744 AND txn.mbatchid = bat.mbatchid
745 AND bat.process_profile_code = user_pf.user_eft_profile_code
746 AND user_pf.sys_eft_profile_code = sys_pf.sys_eft_profile_code;
747
748 CURSOR l_cc_sra_setup_csr (p_trxnmid IN NUMBER) IS
749 SELECT sys_pf.OVERRIDE_PAYER_DELIVERY_FLAG, sys_pf.PAYER_NOTIFICATION_DEL_METHOD,
750 sys_pf.payer_notification_format, sys_pf.PAYER_NOTIFICATION_CONDITION, sys_pf.PN_COND_NUM_OF_RECEIPTS
751 FROM iby_trxn_summaries_all txn, iby_batches_all bat,
752 iby_fndcpt_sys_cc_pf_b sys_pf, iby_fndcpt_user_cc_pf_b user_pf
753 WHERE txn.trxnmid = p_trxnmid
754 AND txn.mbatchid = bat.mbatchid
755 AND bat.process_profile_code = user_pf.user_cc_profile_code
756 AND user_pf.sys_cc_profile_code = sys_pf.sys_cc_profile_code;
757
758 CURSOR l_dc_sra_setup_csr (p_trxnmid IN NUMBER) IS
759 SELECT sys_pf.OVERRIDE_PAYER_DELIVERY_FLAG, sys_pf.PAYER_NOTIFICATION_DEL_METHOD,
760 sys_pf.payer_notification_format, sys_pf.PAYER_NOTIFICATION_CONDITION, sys_pf.PN_COND_NUM_OF_RECEIPTS
761 FROM iby_trxn_summaries_all txn, iby_batches_all bat,
762 iby_fndcpt_sys_dc_pf_b sys_pf, iby_fndcpt_user_dc_pf_b user_pf
763 WHERE txn.trxnmid = p_trxnmid
764 AND txn.mbatchid = bat.mbatchid
765 AND bat.process_profile_code = user_pf.user_dc_profile_code
766 AND user_pf.sys_dc_profile_code = sys_pf.sys_dc_profile_code;
767
768 CURSOR l_lang_territory_csr (p_trxnmid IN NUMBER) IS
769 SELECT loc.language, loc.country
770 FROM hz_party_sites ps, hz_locations loc,
771 iby_trxn_summaries_all txn, hz_cust_site_uses_all hz_csu,
772 hz_cust_acct_sites_all hz_cs
773 where txn.trxnmid = p_trxnmid
774 and hz_csu.cust_acct_site_id = hz_cs.cust_acct_site_id
775 and hz_cs.party_site_id = ps.party_site_id
776 AND txn.acct_site_use_id = hz_csu.site_use_id(+)
777 AND loc.location_id = ps.location_id;
778
779 CURSOR l_settle_ref_csr(p_trxnmid IN NUMBER) Is
780 SELECT tangibleid
781 FROM IBY_TRXN_SUMMARIES_ALL
782 WHERE TRXNMID = p_trxnmid;
783
784
785 BEGIN
786
787 IF p_attribute_type = G_SRA_DELIVERY_METHOD_ATTR OR
788 p_attribute_type = G_PF_FORMAT_ATTR OR
789 p_attribute_type = G_SRA_PN_CONDITION OR
790 p_attribute_type = G_SRA_PN_NUM_DOCUMENTS OR
791 p_attribute_type = G_SRA_EMAIL_SUBJECT_ATTR THEN
792
793 OPEN l_instrument_type_csr (p_trxnmid);
794 FETCH l_instrument_type_csr INTO l_instrument_type;
795 CLOSE l_instrument_type_csr;
796
797 IF l_instrument_type = 'BANKACCOUNT' THEN
798
799 OPEN l_eft_sra_setup_csr (p_trxnmid);
800 FETCH l_eft_sra_setup_csr INTO l_override_payer_flag, l_pf_sra_delivery_method, l_pf_sra_format, l_sra_pn_condition, l_sra_pn_document_count;
801 CLOSE l_eft_sra_setup_csr;
802
803 ELSIF l_instrument_type = 'CREDITCARD' THEN
804
805 OPEN l_cc_sra_setup_csr (p_trxnmid);
806 FETCH l_cc_sra_setup_csr INTO l_override_payer_flag, l_pf_sra_delivery_method, l_pf_sra_format, l_sra_pn_condition, l_sra_pn_document_count;
807 CLOSE l_cc_sra_setup_csr;
808
809 ELSIF l_instrument_type = 'DEBITCARD' THEN
810
811 OPEN l_dc_sra_setup_csr (p_trxnmid);
812 FETCH l_dc_sra_setup_csr INTO l_override_payer_flag, l_pf_sra_delivery_method, l_pf_sra_format, l_sra_pn_condition, l_sra_pn_document_count;
813 CLOSE l_dc_sra_setup_csr;
814
815 END IF;
816
817 open l_settle_ref_csr(p_trxnmid);
818 fetch l_settle_ref_csr into l_settle_ref;
819 close l_settle_ref_csr;
820
821 IF p_attribute_type = G_SRA_EMAIL_SUBJECT_ATTR THEN
822 fnd_message.set_name('IBY', 'IBY_FC_PYR_NOTIF_EMAIL_SUBJ');
823 fnd_message.set_Token('SETTLE_REF_NUM',l_settle_ref);
824 l_email_sub := FND_MESSAGE.GET;
825 RETURN l_email_sub;
826 END IF;
827
828
829 IF p_attribute_type = G_PF_FORMAT_ATTR THEN
830 RETURN l_pf_sra_format;
831 END IF;
832
833 IF p_attribute_type = G_SRA_PN_CONDITION THEN
834 RETURN l_sra_pn_condition;
835 END IF;
836
837 IF p_attribute_type = G_SRA_PN_NUM_DOCUMENTS THEN
838 RETURN l_sra_pn_document_count;
839 END IF;
840
841 IF l_override_payer_flag = 'Y' THEN
842 l_sra_delivery_method := l_pf_sra_delivery_method;
843
844 ELSE
845 l_sra_delivery_method := Get_Payer_Default_Attribute(p_trxnmid, p_attribute_type);
846
847 IF l_sra_delivery_method is null THEN
848 l_sra_delivery_method := l_pf_sra_delivery_method;
849 END IF;
850 END IF;
851
852 return l_sra_delivery_method;
853
854 ELSIF p_attribute_type = G_SRA_REQ_FLAG_ATTR THEN
855
856 return 'Y';
857
858 ELSIF p_attribute_type = G_SRA_PS_LANG_ATTR OR
859 p_attribute_type = G_SRA_PS_TERRITORY_ATTR THEN
860
861 OPEN l_lang_territory_csr (p_trxnmid);
862 FETCH l_lang_territory_csr INTO l_ps_lang, l_ps_territory;
863 CLOSE l_lang_territory_csr;
864
865 IF p_attribute_type = G_SRA_PS_LANG_ATTR THEN
866 return l_ps_lang;
867 ELSE
868 return l_ps_territory;
869 END IF;
870
871 ELSE
872 return Get_Payer_Default_Attribute(p_trxnmid, p_attribute_type);
873 END IF;
874
875 END Get_SRA_Attribute;
876
877
878 FUNCTION Get_Payer_Default_Attribute(p_trxnmid IN NUMBER, p_attribute_type IN NUMBER)
879 RETURN VARCHAR2
880 IS
881
882 l_attribute_val VARCHAR2(1000);
883
884 CURSOR l_payer_defaulting_cur (p_trxnmid NUMBER) IS
885 SELECT payer.debit_advice_delivery_method,
886 payer.debit_advice_email,
887 payer.debit_advice_fax
888 FROM iby_external_payers_all payer,
889 iby_trxn_summaries_all txn
890 WHERE payer.party_id = txn.payer_party_id
891 AND (payer.org_id is NULL OR (payer.org_id = txn.org_id AND payer.org_type = txn.org_type))
892 AND (payer.cust_account_id is NULL OR payer.cust_account_id = txn.cust_account_id)
893 AND (payer.acct_site_use_id is NULL OR payer.acct_site_use_id = txn.acct_site_use_id)
894 AND txn.trxnmid = p_trxnmid
895 ORDER BY payer.acct_site_use_id, payer.cust_account_id, payer.org_id;
896
897 BEGIN
898
899 FOR l_default_rec in l_payer_defaulting_cur(p_trxnmid) LOOP
900 IF (l_attribute_val is NULL) THEN
901 IF p_attribute_type = G_SRA_DELIVERY_METHOD_ATTR THEN
902 l_attribute_val := l_default_rec.debit_advice_delivery_method;
903 ELSIF p_attribute_type = G_SRA_EMAIL_ATTR THEN
904 l_attribute_val := l_default_rec.debit_advice_email;
905 ELSIF p_attribute_type = G_SRA_FAX_ATTR THEN
906 l_attribute_val := l_default_rec.debit_advice_fax;
907 END IF;
908 END IF;
909 END LOOP;
910
911 return l_attribute_val;
912 END Get_Payer_Default_Attribute;
913
914
915 FUNCTION Get_Batch_Format(p_batchid IN VARCHAR2, p_format_type IN VARCHAR2)
916 RETURN VARCHAR2
917 IS
918
919 CURSOR l_instrument_type_csr (p_batchid IN VARCHAR2) IS
920 SELECT bat.instrument_type
921 FROM iby_batches_all bat
922 WHERE bat.batchid = p_batchid;
923
924 CURSOR l_eft_format_csr (p_batchid IN VARCHAR2) IS
925 SELECT sys_pf.PAYER_NOTIFICATION_FORMAT, sys_pf.ACCOMPANY_LETTER_FORMAT
926 FROM iby_batches_all bat,
927 iby_fndcpt_sys_eft_pf_b sys_pf, iby_fndcpt_user_eft_pf_b user_pf
928 WHERE bat.batchid = p_batchid
929 AND bat.process_profile_code = user_pf.user_eft_profile_code
930 AND user_pf.sys_eft_profile_code = sys_pf.sys_eft_profile_code;
931
932 CURSOR l_cc_format_csr (p_batchid IN VARCHAR2) IS
933 SELECT sys_pf.PAYER_NOTIFICATION_FORMAT
934 FROM iby_batches_all bat,
935 iby_fndcpt_sys_cc_pf_b sys_pf, iby_fndcpt_user_cc_pf_b user_pf
936 WHERE bat.batchid = p_batchid
937 AND bat.process_profile_code = user_pf.user_cc_profile_code
938 AND user_pf.sys_cc_profile_code = sys_pf.sys_cc_profile_code;
939
940 CURSOR l_dc_format_csr (p_batchid IN VARCHAR2) IS
941 SELECT sys_pf.PAYER_NOTIFICATION_FORMAT
942 FROM iby_batches_all bat,
943 iby_fndcpt_sys_dc_pf_b sys_pf, iby_fndcpt_user_dc_pf_b user_pf
944 WHERE bat.batchid = p_batchid
945 AND bat.process_profile_code = user_pf.user_dc_profile_code
946 AND user_pf.sys_dc_profile_code = sys_pf.sys_dc_profile_code;
947
948 l_instr_type VARCHAR2(30);
949 l_acp_ltr_format VARCHAR2(30);
950 l_payer_notif_format VARCHAR2(30);
951
952 BEGIN
953
954 OPEN l_instrument_type_csr (p_batchid);
955 FETCH l_instrument_type_csr INTO l_instr_type;
956 CLOSE l_instrument_type_csr;
957
958 IF l_instr_type = 'BANKACCOUNT' THEN
959
960 OPEN l_eft_format_csr (p_batchid);
961 FETCH l_eft_format_csr INTO l_payer_notif_format, l_acp_ltr_format;
962 CLOSE l_eft_format_csr;
963
964 ELSIF l_instr_type = 'CREDITCARD' THEN
965
966 OPEN l_cc_format_csr (p_batchid);
967 FETCH l_cc_format_csr INTO l_payer_notif_format;
968 CLOSE l_cc_format_csr;
969
970 ELSIF l_instr_type = 'DEBITCARD' THEN
971
972 OPEN l_dc_format_csr (p_batchid);
973 FETCH l_dc_format_csr INTO l_payer_notif_format;
974 CLOSE l_dc_format_csr;
975
976 END IF;
977
978 IF p_format_type = 'PAYER_NOTIFICATION' THEN
979 RETURN l_payer_notif_format;
980 ELSIF p_format_type = 'FUNDS_CAPTURE_ACCOMPANY_LETTER' THEN
981 RETURN l_acp_ltr_format;
982 END IF;
983
984 END Get_Batch_Format;
985
986
987 -- obsolete use Update_Pmt_SRA_Attr_Ele()
988 PROCEDURE Update_Pmt_SRA_Attr_Prt
989 (
990 p_mbatchid IN VARCHAR2,
991 p_fromDate IN VARCHAR2,
992 p_toDate IN VARCHAR2,
993 p_fromPSON IN VARCHAR2,
994 p_toPSON IN VARCHAR2,
995 p_delivery_method IN VARCHAR2,
996 p_format_code IN VARCHAR2
997 )
998 IS
999 l_Debug_Module VARCHAR2(255) := G_DEBUG_MODULE || '.Update_Pmt_SRA_Attr_Prt';
1000
1001 l_where_clause VARCHAR2(4000);
1002 l_update_stmt VARCHAR2(4000) :=
1003 'UPDATE iby_trxn_summaries_all txn SET ' ||
1004 ' debit_advice_delivery_method = ''PRINTED'', ' ||
1005 ' debit_advice_email = null, ' ||
1006 ' debit_advice_fax = null, ' ||
1007 ' payer_notification_created = ''Y'', ' ||
1008 ' object_version_number = object_version_number + 1, ' ||
1009 ' last_updated_by = fnd_global.user_id, ' ||
1010 ' last_update_date = SYSDATE, ' ||
1011 ' last_update_login = nvl(fnd_global.LOGIN_ID, fnd_global.conc_login_id) ' ||
1012 'WHERE nvl(txn.payer_notification_required, ''N'') = ''Y'' ';
1013
1014 BEGIN
1015
1016 iby_debug_pub.add(debug_msg => 'Enter: ' || l_Debug_Module,
1017 debug_level => FND_LOG.LEVEL_PROCEDURE,
1018 module => l_Debug_Module);
1019
1020 l_where_clause := Get_Payer_Notif_Where_cluase
1021 (
1022 p_mbatchid => p_mbatchid,
1023 p_fromDate => p_fromDate,
1024 p_toDate => p_toDate,
1025 p_fromPSON => p_fromPSON,
1026 p_toPSON => p_toPSON,
1027 p_delivery_method => p_delivery_method,
1028 p_format_code => p_format_code,
1029 p_debug_module => l_Debug_Module
1030 );
1031
1032 l_update_stmt := l_update_stmt || l_where_clause;
1033
1034 iby_debug_pub.add(debug_msg => 'Before executing dynamic update statement.',
1035 debug_level => FND_LOG.LEVEL_STATEMENT,
1036 module => l_Debug_Module);
1037
1038 iby_debug_pub.add(debug_msg => 'l_update_stmt: ' || l_update_stmt,
1039 debug_level => FND_LOG.LEVEL_STATEMENT,
1040 module => l_Debug_Module);
1041
1042 EXECUTE IMMEDIATE l_update_stmt;
1043
1044 iby_debug_pub.add(debug_msg => 'Exit: ' || l_Debug_Module,
1045 debug_level => FND_LOG.LEVEL_PROCEDURE,
1046 module => l_Debug_Module);
1047
1048 END Update_Pmt_SRA_Attr_Prt;
1049
1050
1051 PROCEDURE Update_Pmt_SRA_Attr_Ele
1052 (
1053 p_trxnmid IN NUMBER,
1054 p_delivery_method IN VARCHAR2,
1055 p_recipient_email IN VARCHAR2,
1056 p_recipient_fax IN VARCHAR2
1057 )
1058 IS
1059 BEGIN
1060
1061 IF p_delivery_method = 'EMAIL' THEN
1062 UPDATE
1063 iby_trxn_summaries_all
1064 SET
1065 debit_advice_delivery_method = p_delivery_method,
1066 debit_advice_email = p_recipient_email,
1067 debit_advice_fax = null,
1068 payer_notification_created = 'Y',
1069 object_version_number = object_version_number + 1,
1070 last_updated_by = fnd_global.user_id,
1071 last_update_date = SYSDATE,
1072 last_update_login = nvl(fnd_global.LOGIN_ID, fnd_global.conc_login_id)
1073 WHERE trxnmid = p_trxnmid;
1074 ELSIF p_delivery_method = 'FAX' THEN
1075 UPDATE
1076 iby_trxn_summaries_all
1077 SET
1078 debit_advice_delivery_method = p_delivery_method,
1079 debit_advice_email = null,
1080 debit_advice_fax = p_recipient_fax,
1081 payer_notification_created = 'Y',
1082 object_version_number = object_version_number + 1,
1083 last_updated_by = fnd_global.user_id,
1084 last_update_date = SYSDATE,
1085 last_update_login = nvl(fnd_global.LOGIN_ID, fnd_global.conc_login_id)
1086 WHERE trxnmid = p_trxnmid;
1087 ELSIF p_delivery_method = 'PRINTED' THEN
1088 UPDATE
1089 iby_trxn_summaries_all
1090 SET
1091 debit_advice_delivery_method = p_delivery_method,
1092 debit_advice_email = NULL,
1093 debit_advice_fax = NULL,
1094 payer_notification_created = 'Y',
1095 object_version_number = object_version_number + 1,
1096 last_updated_by = fnd_global.user_id,
1097 last_update_date = SYSDATE,
1098 last_update_login = nvl(fnd_global.LOGIN_ID, fnd_global.conc_login_id)
1099 WHERE trxnmid = p_trxnmid;
1100 END IF;
1101
1102 COMMIT;
1103
1104 END Update_Pmt_SRA_Attr_Ele;
1105
1106 FUNCTION submit_payer_notification
1107 (
1108 p_bep_type IN VARCHAR2,
1109 p_settlement_batch IN VARCHAR2 DEFAULT NULL,
1110 p_from_settlement_date IN DATE DEFAULT NULL,
1111 p_to_settlement_date IN DATE DEFAULT NULL,
1112 p_from_PSON IN VARCHAR2 DEFAULT NULL,
1113 p_to_PSON IN VARCHAR2 DEFAULT NULL
1114 ) RETURN NUMBER
1115 IS
1116 l_request_id NUMBER;
1117 l_reqdate DATE;
1118 l_Debug_Module VARCHAR2(255) := G_DEBUG_MODULE || '.submit_payer_notification';
1119 l_icx_numeric_characters VARCHAR2(30); -- Bug 6411356
1120 l_bool_val boolean; -- Bug 6411356
1121
1122 BEGIN
1123
1124 iby_debug_pub.add(debug_msg => 'Enter: ' || l_Debug_Module, debug_level => FND_LOG.LEVEL_PROCEDURE,
1125 module => l_Debug_Module);
1126
1127 iby_debug_pub.add(debug_msg => 'p_bep_type: '||p_bep_type, debug_level => FND_LOG.LEVEL_STATEMENT,
1128 module => l_Debug_Module);
1129
1130 iby_debug_pub.add(debug_msg => 'p_from_settlement_date: '||p_from_settlement_date, debug_level => FND_LOG.LEVEL_STATEMENT,
1131 module => l_Debug_Module);
1132
1133 iby_debug_pub.add(debug_msg => 'p_to_settlement_date: '||p_to_settlement_date, debug_level => FND_LOG.LEVEL_STATEMENT,
1134 module => l_Debug_Module);
1135
1136 iby_debug_pub.add(debug_msg => 'p_settlement_batch: '||p_settlement_batch, debug_level => FND_LOG.LEVEL_STATEMENT,
1137 module => l_Debug_Module);
1138
1139 iby_debug_pub.add(debug_msg => 'p_from_PSON: '||p_from_PSON, debug_level => FND_LOG.LEVEL_STATEMENT,
1140 module => l_Debug_Module);
1141
1142 iby_debug_pub.add(debug_msg => 'p_to_PSON: '||p_to_PSON, debug_level => FND_LOG.LEVEL_STATEMENT,
1143 module => l_Debug_Module);
1144
1145 -- The settlement date is required for the concurrent request, so included
1146 -- logic in API to get the settlement date based on query from concurrent
1147 -- request.
1148 IF p_from_settlement_date IS NULL AND p_bep_type = 'PROCESSOR' THEN
1149 BEGIN
1150 SELECT MIN(reqdate)
1151 INTO l_reqdate
1152 FROM iby_trxn_summaries_all
1153 WHERE batchid = p_settlement_batch
1154 AND NVL(payer_notification_required, 'N') = 'Y';
1155 EXCEPTION
1156 WHEN others THEN NULL;
1157 END;
1158
1159 iby_debug_pub.add(debug_msg => 'Reqdate is not passed and type is PROCESSOR. l_reqdate='||l_reqdate,
1160 debug_level => FND_LOG.LEVEL_STATEMENT, module => l_Debug_Module);
1161
1162 END IF;
1163
1164 iby_debug_pub.add(debug_msg => 'Before Calling FND_REQUEST.SUBMIT_REQUEST()', debug_level => FND_LOG.LEVEL_STATEMENT,
1165 module => l_Debug_Module);
1166
1167 --Bug 6411356
1168 --below code added to set the current nls character setting
1169 --before submitting a child requests.
1170 fnd_profile.get('ICX_NUMERIC_CHARACTERS',l_icx_numeric_characters);
1171 l_bool_val:= FND_REQUEST.SET_OPTIONS( numeric_characters => l_icx_numeric_characters);
1172
1173 -- submit the extract program
1174 l_request_id := FND_REQUEST.SUBMIT_REQUEST
1175 (
1176 'IBY',
1177 'IBY_FC_PAYER_NOTIF_FORMAT',
1178 null, -- description
1179 null, -- start_time
1180 FALSE, -- sub_request
1181 p_settlement_batch,
1182 NVL(p_from_settlement_date, l_reqdate),
1183 p_to_settlement_date,
1184 p_from_PSON,
1185 p_to_PSON,
1186 '', '', '', '',
1187 '', '', '', '', '', '', '', '',
1188 '', '', '', '', '', '', '', '',
1189 '', '', '', '', '', '', '', '',
1190 '', '', '', '', '', '', '', '',
1191 '', '', '', '', '', '', '', '',
1192 '', '', '', '', '', '', '', '',
1193 '', '', '', '', '', '', '', '',
1194 '', '', '', '', '', '', '', '',
1195 '', '', '', '', '', '', '', '',
1196 '', '', '', '', '', '', '', '',
1197 '', '', '', '', '', '', '', '',
1198 '', '', ''
1199 );
1200
1201 -- Added explicit commit in pl/sql. Request id is logged, but the request is not created in FND
1202 COMMIT;
1203
1204 iby_debug_pub.add(debug_msg => 'After Calling FND_REQUEST.SUBMIT_REQUEST()',
1205 debug_level => FND_LOG.LEVEL_STATEMENT,
1206 module => l_Debug_Module);
1207 iby_debug_pub.add(debug_msg => 'Request id: ' || l_request_id,
1208 debug_level => FND_LOG.LEVEL_STATEMENT,
1209 module => l_Debug_Module);
1210
1211 iby_debug_pub.add(debug_msg => 'Exit: ' || l_Debug_Module,
1212 debug_level => FND_LOG.LEVEL_PROCEDURE,
1213 module => l_Debug_Module);
1214
1215 RETURN l_request_id;
1216
1217 END submit_payer_notification;
1218
1219
1220 FUNCTION submit_accompany_letter
1221 (
1222 p_settlement_batch IN VARCHAR2
1223 ) RETURN NUMBER
1224 IS
1225 l_request_id NUMBER;
1226 l_Debug_Module VARCHAR2(255) := G_DEBUG_MODULE || '.submit_accompany_letter';
1227 l_icx_numeric_characters VARCHAR2(30); -- Bug 6411356
1228 l_bool_val boolean; -- Bug 6411356
1229
1230
1231 BEGIN
1232
1233 iby_debug_pub.add(debug_msg => 'Enter: ' || l_Debug_Module, debug_level => FND_LOG.LEVEL_PROCEDURE,
1234 module => l_Debug_Module);
1235
1236 iby_debug_pub.add(debug_msg => 'p_settlement_batch: '||p_settlement_batch, debug_level => FND_LOG.LEVEL_STATEMENT,
1237 module => l_Debug_Module);
1238
1239 iby_debug_pub.add(debug_msg => 'Before Calling FND_REQUEST.SUBMIT_REQUEST()', debug_level => FND_LOG.LEVEL_STATEMENT,
1240 module => l_Debug_Module);
1241
1242
1243 --Bug 6411356
1244 --below code added to set the current nls character setting
1245 --before submitting a child requests.
1246 fnd_profile.get('ICX_NUMERIC_CHARACTERS',l_icx_numeric_characters);
1247 l_bool_val:= FND_REQUEST.SET_OPTIONS( numeric_characters => l_icx_numeric_characters);
1248
1249 -- submit the extract program
1250 l_request_id := FND_REQUEST.SUBMIT_REQUEST
1251 (
1252 'IBY',
1253 'IBY_FC_ACP_LTR_FORMAT',
1254 null, -- description
1255 null, -- start_time
1256 FALSE, -- sub_request
1257 p_settlement_batch,
1258 '', '', '', '', '', '', '', '',
1259 '', '', '', '', '', '', '', '',
1260 '', '', '', '', '', '', '', '',
1261 '', '', '', '', '', '', '', '',
1262 '', '', '', '', '', '', '', '',
1263 '', '', '', '', '', '', '', '',
1264 '', '', '', '', '', '', '', '',
1265 '', '', '', '', '', '', '', '',
1266 '', '', '', '', '', '', '', '',
1267 '', '', '', '', '', '', '', '',
1268 '', '', '', '', '', '', '', '',
1269 '', '', '', '', '', '', '', '',
1270 '', '', ''
1271 );
1272
1273 -- Added explicit commit in pl/sql. Request id is logged, but the request is not created in FND
1274 COMMIT;
1275
1276 iby_debug_pub.add(debug_msg => 'After Calling FND_REQUEST.SUBMIT_REQUEST()',
1277 debug_level => FND_LOG.LEVEL_STATEMENT,
1278 module => l_Debug_Module);
1279 iby_debug_pub.add(debug_msg => 'Request id: ' || l_request_id,
1280 debug_level => FND_LOG.LEVEL_STATEMENT,
1281 module => l_Debug_Module);
1282
1283 iby_debug_pub.add(debug_msg => 'Exit: ' || l_Debug_Module,
1284 debug_level => FND_LOG.LEVEL_PROCEDURE,
1285 module => l_Debug_Module);
1286
1287 RETURN l_request_id;
1288
1289 END submit_accompany_letter;
1290
1291
1292 /*
1293 is_amended: Gives whether the mandate has been amended or not.
1294 */
1295
1296 FUNCTION is_amended
1297 ( p_mandate_id IN iby_debit_authorizations.debit_authorization_id%TYPE )
1298 RETURN varchar2
1299 IS
1300 l_count number(6);
1301 BEGIN
1302
1303 SELECT count(*) INTO l_count
1304 FROM iby_debit_authorizations
1305 WHERE initial_debit_authorization_id = ( SELECT initial_debit_authorization_id
1306 FROM iby_debit_authorizations
1307 WHERE debit_authorization_id = p_mandate_id );
1308
1309 IF (l_count >1)
1310 THEN RETURN 'TRUE';
1311 ELSE RETURN 'FALSE';
1312 END IF;
1313
1314 RETURN 'FALSE';
1315
1316 EXCEPTION WHEN OTHERS THEN
1317 RETURN 'FALSE';
1318
1319 END is_amended;
1320
1321
1322
1323 /*
1324 get_assignment_iban: Returns the IBN Number for an Bank Account Assignment.
1325 */
1326
1327
1328 FUNCTION get_assignment_iban
1329 ( p_assign_id IN iby_debit_authorizations.external_bank_account_use_id%TYPE )
1330 RETURN varchar2
1331 IS
1332 l_iban iby_ext_bank_accounts.iban%TYPE;
1333 BEGIN
1334
1335 IF (p_assign_id IS NULL) THEN
1336 RETURN NULL;
1337 END IF;
1338
1339 SELECT iby_ext_bankacct_pub.Uncipher_Bank_Number (ext_ba.iban, ext_ba.iban_sec_segment_id, iby_utility_pvt.get_view_param('SYS_KEY'),
1340 ibk.subkey_cipher_text, ibs.segment_cipher_text, ibs.encoding_scheme, ext_ba.ba_mask_setting, ext_ba.ba_unmask_length)
1341 INTO l_iban
1342 FROM iby_pmt_instr_uses_all iu
1343 ,iby_ext_bank_accounts ext_ba
1344 ,iby_sys_security_subkeys ibk
1345 ,iby_security_segments ibs
1346 WHERE iu.instrument_payment_use_id = p_assign_id
1347 AND iu.instrument_type = 'BANKACCOUNT'
1348 AND iu.instrument_id = ext_ba.ext_bank_account_id
1349 AND (ext_ba.iban_sec_segment_id = ibs.sec_segment_id(+))
1350 AND (ibs.sec_subkey_id = ibk.sec_subkey_id(+));
1351
1352 RETURN l_iban;
1353
1354 EXCEPTION WHEN others THEN
1355 RETURN Null;
1356
1357 END get_assignment_iban;
1358
1359
1360 /*
1361 get_mandate_details: Returns the Mandate details for an bank account.
1362 */
1363
1364
1365 FUNCTION get_mandate_details
1366 ( p_mandate_id IN iby_debit_authorizations.debit_authorization_id%TYPE )
1367 RETURN XMLType
1368 IS
1369 l_doc_rec XMLType;
1370
1371 CURSOR l_mandate
1372 (c_mandate_id iby_debit_authorizations.debit_authorization_id%TYPE) IS
1373 SELECT MandateDetails from (
1374 SELECT debit_id, XMLElement("MandateDetails", XMLConcat( XMLElement("AuthorizationReference", curr_auth_ref)
1375 , XMLElement("AuthorizationSignDate", curr_sign_date)
1376 , XMLElement("AmendmentIndicator", amend_indicator) , XMLElement("OrgnlAuthReference", prev_auth_ref)
1377 , XMLElement("OrgnlCreditor", prev_cred_name) , XMLElement("OrgnlCreditorId", prev_cred_id)
1378 , XMLElement("IBAN", iban) , XMLElement("CreditorName", curr_cred_name), XMLElement("CreditorIdentifier", curr_cred_id) ) ) MandateDetails
1379 FROM (
1380 SELECT curr_mandate.debit_authorization_id debit_id
1381 , curr_mandate.authorization_reference_number curr_auth_ref, curr_mandate.auth_sign_date curr_sign_date
1382 ,is_amended(curr_mandate.debit_authorization_id) amend_indicator
1383 , prev_mandate.authorization_reference_number prev_auth_ref , prev_mandate.creditor_le_name prev_cred_name
1384 , prev_mandate.creditor_identifier prev_cred_id
1385 ,get_assignment_iban(prev_mandate.external_bank_account_use_id) iban
1386 ,curr_mandate.creditor_le_name curr_cred_name , curr_mandate.creditor_identifier curr_cred_id
1387 FROM iby_debit_authorizations curr_mandate , iby_debit_authorizations prev_mandate
1388 WHERE curr_mandate.initial_debit_authorization_id = prev_mandate.initial_debit_authorization_id(+)
1389 AND curr_mandate.debit_authorization_id <> prev_mandate.debit_authorization_id(+)
1390 ORDER BY prev_mandate.authorization_revision_number DESC )
1391 WHERE debit_id = c_mandate_id
1392 AND ROWNUM < 2 );
1393
1394 BEGIN
1395
1396 IF (p_mandate_id IS NULL) THEN
1397 RETURN NULL;
1398 END IF;
1399
1400 IF (l_mandate%ISOPEN) THEN
1401 CLOSE l_mandate;
1402 END IF;
1403
1404 OPEN l_mandate(p_mandate_id);
1405 FETCH l_mandate INTO l_doc_rec;
1406 IF (l_mandate%NOTFOUND) THEN
1407 l_doc_rec := NULL;
1408 END IF;
1409 CLOSE l_mandate;
1410 RETURN l_doc_rec;
1411
1412 EXCEPTION WHEN OTHERS THEN
1413 RETURN NULL;
1414 END get_mandate_details;
1415
1416 PROCEDURE Insert_Into_Gt
1417 (
1418 p_txn_id IN NUMBER
1419 )
1420 IS
1421 l_Debug_Module VARCHAR2(255) := G_DEBUG_MODULE || '.Insert_Into_Gt';
1422
1423 BEGIN
1424
1425 DELETE FROM IBY_AR_DOC_HEADER_GT;
1426 DELETE FROM IBY_AR_DOC_LINES_GT;
1427
1428 Insert_Into_Headers_Gt(p_txn_id);
1429 Insert_Into_Lines_Gt(p_txn_id);
1430
1431 END Insert_Into_Gt;
1432
1433 PROCEDURE Insert_Into_Headers_Gt
1434 (
1435 p_txn_id IN NUMBER
1436 )
1437 IS
1438 TYPE dyn_header_select IS REF CURSOR;
1439 l_header_cursor dyn_header_select;
1440 l_headers_query VARCHAR2(32767);
1441 rec_limit NUMBER := 1000;
1442 l_doc_headers_rec Doc_headers_rec_type;
1443 l_DocHeadersTab DocHeadersTabType;
1444 l_count NUMBER;
1445 l_payment_channel_code IBY_TRXN_SUMMARIES_ALL.PAYMENT_CHANNEL_CODE%TYPE;
1446 l_Debug_Module VARCHAR2(255) := G_DEBUG_MODULE || '.Insert_Into_Headers_Gt';
1447 BEGIN
1448
1449 IF (l_header_cursor%ISOPEN) THEN CLOSE l_header_cursor; END IF;
1450 FND_FILE.PUT_LINE(FND_FILE.LOG, l_Debug_Module || 'Enter , TimeStamp:' ||systimestamp);
1451 /* The columns from 15 to 22 are null because the view ar_docs_receivables_v
1452 has null for ship_from_address_id
1453 */
1454 SELECT payment_channel_code
1455 INTO l_payment_channel_code
1456 FROM iby_trxn_summaries_all txn
1457 WHERE txn.mbatchid = p_txn_id
1458 AND rownum = 1;
1459
1460 l_headers_query := 'SELECT
1461 inv_header.calling_app_doc_ref_number,
1462 inv_header.document_date,
1463 inv_header.creation_date,
1464 inv_header.document_type,
1465 inv_header.document_description,
1466 inv_header.document_amount,
1467 inv_header.document_currency_code,
1468 inv_header.settlement_amount,
1469 inv_header.discount_amount,
1470 inv_header.freight_amount,';
1471 IF(l_payment_channel_code = 'BILLS_RECEIVABLE') THEN
1472 l_headers_query := l_headers_query || 'null,';
1473 ELSE
1474 l_headers_query := l_headers_query || 'inv_header.document_discount_earned,';
1475 END IF;
1476 l_headers_query := l_headers_query ||
1477 'inv_header.local_tax_amount,
1478 inv_header.national_tax_amount,
1479 inv_header.vat_tax_amount,
1480 null,
1481 null,
1482 null,
1483 null,
1484 null,
1485 null,
1486 null,
1487 null,
1488 to_loc.address1,
1489 to_loc.address2,
1490 to_loc.address3,
1491 to_loc.city,
1492 to_loc.county,
1493 to_loc.state,
1494 to_loc.country,
1495 to_loc.postal_code,
1496 null,
1497 txn.transactionid,
1498 txn.initiator_extension_id,
1499 inv_header.doc_unique_ref,
1500 inv_header.BR_DRAWEE_ISSUED_FLAG,
1501 inv_header.BR_SIGNED_FLAG
1502 FROM ';
1503 IF(l_payment_channel_code = 'BILLS_RECEIVABLE') THEN
1504 l_headers_query := l_headers_query || 'ARBR_DOCS_RECEIVABLES_V inv_header,';
1505 ELSE
1506 l_headers_query := l_headers_query || 'ar_docs_receivables_v inv_header,';
1507 END IF;
1508
1509 l_headers_query := l_headers_query ||'hz_locations to_loc,
1510 iby_trxn_summaries_all txn
1511 WHERE txn.initiator_extension_id = inv_header.order_ext_id
1512 AND(txn.trxntypeid IN(''8'', ''9'', ''100''))
1513 AND(inv_header.ship_to_address_id = to_loc.location_id(+))
1514 AND(txn.mbatchid = :1)';
1515
1516 OPEN l_header_cursor FOR
1517 l_headers_query
1518 USING p_txn_id;
1519
1520 LOOP
1521
1522 FETCH l_header_cursor BULK COLLECT INTO l_DocHeadersTab LIMIT rec_limit;
1523 EXIT WHEN l_DocHeadersTab.COUNT = 0;
1524
1525 IF (l_DocHeadersTab.COUNT > 0) THEN
1526 FORALL j IN l_DocHeadersTab.first..l_DocHeadersTab.last
1527 INSERT INTO IBY_AR_DOC_HEADER_GT VALUES l_DocHeadersTab(j);
1528 END IF;
1529
1530 END LOOP;
1531 select count(*) into l_count from IBY_AR_DOC_HEADER_GT;
1532 FND_FILE.PUT_LINE(FND_FILE.LOG, l_Debug_Module || ' Number of rows in IBY_AR_DOC_HEADER_GT: '||l_count);
1533 FND_FILE.PUT_LINE(FND_FILE.LOG, l_Debug_Module || 'Exit , TimeStamp:' ||systimestamp);
1534
1535 END Insert_Into_Headers_Gt;
1536
1537
1538 PROCEDURE Insert_Into_Lines_Gt
1539 (
1540 p_txn_id IN NUMBER
1541 )
1542 IS
1543 TYPE dyn_lines_select IS REF CURSOR;
1544 l_lines_cursor dyn_lines_select;
1545 l_lines_query VARCHAR2(32767);
1546 rec_limit NUMBER := 1000;
1547 l_doc_lines_rec Doc_lines_rec_type;
1548 l_DocLinesTab DocLinesTabType;
1549 l_count NUMBER;
1550 l_payment_channel_code IBY_TRXN_SUMMARIES_ALL.PAYMENT_CHANNEL_CODE%TYPE;
1551 l_Debug_Module VARCHAR2(255) := G_DEBUG_MODULE || '.Insert_Into_Lines_Gt';
1552 BEGIN
1553
1554 FND_FILE.PUT_LINE(FND_FILE.LOG, l_Debug_Module || 'Enter , TimeStamp:' ||systimestamp);
1555 IF (l_lines_cursor%ISOPEN) THEN CLOSE l_lines_cursor; END IF;
1556
1557 SELECT payment_channel_code
1558 INTO l_payment_channel_code
1559 FROM iby_trxn_summaries_all txn
1560 WHERE txn.mbatchid = p_txn_id
1561 AND rownum = 1;
1562
1563 l_lines_query := 'SELECT
1564 inv_line.line_number,
1565 inv_line.po_number,
1566 inv_line.line_type,
1567 inv_line.description,
1568 inv_line.extended_amount,';
1569 IF(l_payment_channel_code = 'BILLS_RECEIVABLE') THEN
1570 l_lines_query := l_lines_query || 'null,';
1571 ELSE
1572 l_lines_query := l_lines_query || 'inv_line.invoice_currency_code,';
1573 END IF;
1574 l_lines_query := l_lines_query ||
1575 'inv_line.unit_price,
1576 inv_line.quantity,
1577 inv_line.unit_of_measure,
1578 inv_line.inventory_item_id,
1579 inv_line.discount_amount,
1580 inv_line.sales_tax_amount,
1581 inv_line.tax_rate,
1582 inv_line.vat_tax_amount,
1583 inv_line.doc_unique_ref
1584 FROM ';
1585 IF(l_payment_channel_code = 'BILLS_RECEIVABLE') THEN
1586 l_lines_query := l_lines_query || 'arbr_document_lines_v inv_line,';
1587 ELSE
1588 l_lines_query := l_lines_query || 'ar_document_lines_v inv_line,';
1589 END IF;
1590
1591 l_lines_query := l_lines_query || 'ar_docs_receivables_v inv_header,
1592 iby_trxn_summaries_all txn
1593 WHERE(inv_header.doc_unique_ref = inv_line.doc_unique_ref(+))
1594 AND(txn.initiator_extension_id = inv_header.order_ext_id)
1595 AND(txn.trxntypeid IN(''8'', ''9'', ''100''))
1596 AND(txn.mbatchid = :1)';
1597
1598 OPEN l_lines_cursor FOR
1599 l_lines_query
1600 USING p_txn_id;
1601
1602 LOOP
1603
1604 FETCH l_lines_cursor BULK COLLECT INTO l_DocLinesTab LIMIT rec_limit;
1605 EXIT WHEN l_DocLinesTab.COUNT = 0;
1606
1607 IF (l_DocLinesTab.COUNT > 0) THEN
1608 FORALL j IN l_DocLinesTab.first..l_DocLinesTab.last
1609 INSERT INTO IBY_AR_DOC_LINES_GT VALUES l_DocLinesTab(j);
1610 END IF;
1611
1612 END LOOP;
1613 select count(*) into l_count from IBY_AR_DOC_LINES_GT;
1614 FND_FILE.PUT_LINE(FND_FILE.LOG, l_Debug_Module || ' Number of rows in IBY_AR_DOC_LINES_GT: '||l_count);
1615 FND_FILE.PUT_LINE(FND_FILE.LOG, l_Debug_Module || 'Exit , TimeStamp:' ||systimestamp);
1616
1617 END Insert_Into_Lines_Gt;
1618
1619
1620 END IBY_FNDCPT_EXTRACT_GEN_PVT;
1621