[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.31.12010000.1 2008/07/28 05:40:44 appldev 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_PF_FORMAT_ATTR CONSTANT NUMBER := 7;
13
14 G_SRA_DELIVERY_METHOD_PRINTED CONSTANT VARCHAR2(30) := 'PRINTED';
15 G_SRA_DELIVERY_METHOD_EMAIL CONSTANT VARCHAR2(30) := 'EMAIL';
16 G_SRA_DELIVERY_METHOD_FAX CONSTANT VARCHAR2(30) := 'FAX';
17
18 G_EXTRACT_MODE_PMT CONSTANT NUMBER := 1;
19 G_EXTRACT_MODE_SRA CONSTANT NUMBER := 2;
20
21 G_Extract_Run_Mode NUMBER := G_EXTRACT_MODE_PMT;
22 G_Extract_Run_Delivery_Method VARCHAR2(30);
23 G_Extract_Run_Payment_id NUMBER;
24
25 PROCEDURE Setup_for_Extract
26 (
27 p_sys_key IN iby_security_pkg.DES3_KEY_TYPE
28 );
29
30 FUNCTION Get_Payer_Notif_Where_cluase
31 (
32 p_mbatchid IN VARCHAR2,
33 p_fromDate IN VARCHAR2,
34 p_toDate IN VARCHAR2,
35 p_fromPSON IN VARCHAR2,
36 p_toPSON IN VARCHAR2,
37 p_delivery_method IN VARCHAR2,
38 p_format_code IN VARCHAR2,
39 p_debug_module IN VARCHAR2
40 ) RETURN VARCHAR2;
41
42
43 FUNCTION Get_Payer_Notif_Where_cluase
44 (
45 p_mbatchid IN VARCHAR2,
46 p_fromDate IN VARCHAR2,
47 p_toDate IN VARCHAR2,
48 p_fromPSON IN VARCHAR2,
49 p_toPSON IN VARCHAR2,
50 p_delivery_method IN VARCHAR2,
51 p_format_code IN VARCHAR2,
52 p_debug_module IN VARCHAR2
53 ) RETURN VARCHAR2
54 IS
55 l_Debug_Module VARCHAR2(255);
56 l_where_clause VARCHAR2(4000);
57
58 BEGIN
59
60 l_Debug_Module := p_debug_module;
61
62 iby_debug_pub.add(debug_msg => 'p_mbatchid: ' || p_mbatchid,
63 debug_level => FND_LOG.LEVEL_STATEMENT,
64 module => l_Debug_Module);
65
66 iby_debug_pub.add(debug_msg => 'p_fromDate: ' || p_fromDate,
67 debug_level => FND_LOG.LEVEL_STATEMENT,
68 module => l_Debug_Module);
69
70 iby_debug_pub.add(debug_msg => 'p_toDate: ' || p_toDate,
71 debug_level => FND_LOG.LEVEL_STATEMENT,
72 module => l_Debug_Module);
73
74 iby_debug_pub.add(debug_msg => 'p_fromPSON: ' || p_fromPSON,
75 debug_level => FND_LOG.LEVEL_STATEMENT,
76 module => l_Debug_Module);
77
78 iby_debug_pub.add(debug_msg => 'p_toPSON: ' || p_toPSON,
79 debug_level => FND_LOG.LEVEL_STATEMENT,
80 module => l_Debug_Module);
81
82 iby_debug_pub.add(debug_msg => 'p_delivery_method: ' || p_delivery_method,
83 debug_level => FND_LOG.LEVEL_STATEMENT,
84 module => l_Debug_Module);
85
86 iby_debug_pub.add(debug_msg => 'p_format_code: ' || p_format_code,
87 debug_level => FND_LOG.LEVEL_STATEMENT,
88 module => l_Debug_Module);
89
90 -- back out this changes due to performance reasons
91 -- from date is required program parameter
92 -- to date is defaulted to sysdate
93 -- this select assumes if the variables are not passed they are null so the variables should be set to null in that
94 -- case in the module where the dynamic sql is executed.
95 --l_where_clause := l_where_clause||' and txn.reqdate >= nvl(to_date(:p_fromDate, ''YYYY/MM/DD HH24:MI:SS''), SYSDATE) '
96 -- ||' and txn.reqdate <= nvl(to_date(:p_toDate, ''YYYY/MM/DD HH24:MI:SS''), SYSDATE) '
97 -- ||' and NVL(txn.mbatchid, -1) = NVL(:p_mbatchid, NVL(txn.mbatchid,-1)) '
98 -- ||' and txn.tangibleid >= NVL(:p_fromPSON, txn.tangibleid) '
99 -- ||' and txn.tangibleid <= NVL(:p_toPSON, txn.tangibleid) '
100 -- ||' and nvl(iby_fndcpt_extract_gen_pvt.Get_sra_Attribute(txn.trxnmid,1), ''x'') = nvl(:p_delivery_method, ''x'') ';
101
102 -- from date is required program parameter
103 l_where_clause := l_where_clause || ' and txn.reqdate >= nvl(to_date( :p_fromDate, ''YYYY/MM/DD HH24:MI:SS''), SYSDATE) ';
104
105 IF nvl(upper(p_toDate), 'NULL') <> 'NULL' THEN
106 l_where_clause := l_where_clause || ' and txn.reqdate <= nvl(to_date('|| ''''||p_toDate|| ''''||', ''YYYY/MM/DD HH24:MI:SS''), SYSDATE) ';
107 l_where_clause := REPLACE(l_where_clause, 'p_toDate', REPLACE(p_toDate, '00:00:00', '23:59:59'));
108 END IF;
109
110 IF nvl(upper(p_mbatchid), 'NULL') <> 'NULL' THEN
111 l_where_clause := l_where_clause || ' and txn.mbatchid = ' || p_mbatchid;
112 END IF;
113
114 IF nvl(upper(p_fromPSON), 'NULL') <> 'NULL' THEN
115 l_where_clause := l_where_clause || ' and txn.tangibleid >= ' || '''' || p_fromPSON|| '''';
116 END IF;
117
118 IF nvl(upper(p_toPSON), 'NULL') <> 'NULL' THEN
119 l_where_clause := l_where_clause || ' and txn.tangibleid <= ' || '''' || p_toPSON|| '''';
120 END IF;
121
122 -- p_delivery_method must not be null
123 -- we don't create extract if p_delivery_method is null
124 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'') ';
125
126 -- p_format_code must not be null
127 -- we don't create extract if p_format_code is null
128 l_where_clause := l_where_clause || ' and iby_fndcpt_extract_gen_pvt.Get_sra_Attribute(txn.trxnmid, 7) = :p_format_code ';
129
130 RETURN l_where_clause;
131
132 END Get_Payer_Notif_Where_cluase;
133
134
135 -- bug 5115161: payer notification
136 PROCEDURE Create_Payer_Notif_Extract_1_0
137 (
138 p_mbatchid IN VARCHAR2,
139 p_fromDate IN VARCHAR2,
140 p_toDate IN VARCHAR2,
141 p_fromPSON IN VARCHAR2,
142 p_toPSON IN VARCHAR2,
143 p_delivery_method IN VARCHAR2,
144 p_format_code IN VARCHAR2,
145 p_txn_id IN NUMBER,
146 p_sys_key IN iby_security_pkg.DES3_KEY_TYPE,
147 x_extract_doc OUT NOCOPY CLOB
148 )
149 IS
150 l_Debug_Module VARCHAR2(255) := G_DEBUG_MODULE || '.Create_Payer_Notif_Extract_1_0';
151 l_xml XMLTYPE;
152 l_where_clause VARCHAR2(4000);
153 l_extract_query VARCHAR2(4000) :=
154 'select XMLElement("FundsCapturePayerNotification", ' ||
155 ' XMLElement("FormatProgramRequestID", fnd_global.CONC_REQUEST_ID), ' ||
156 ' XMLAgg(xml_order.FNDCPT_ORDER)) ' ||
157 ' from iby_trxn_summaries_all txn, IBY_XML_FNDCPT_ORDER_PN_1_0_V xml_order ' ||
158 ' where txn.trxnmid = xml_order.trxnmid ' ||
159 ' and nvl(txn.payer_notification_required, ''N'') = ''Y'' ';
160
161 -- l_trxn_id iby_trxn_summaries_all.trxnmid%TYPE;
162 -- l_mbatchid VARCHAR2(100);
163 -- l_toDate VARCHAR2(100);
164 -- l_delivery_method VARCHAR2(30);
165 -- l_fromPSON iby_trxn_summaries_all.tangibleid%TYPE;
166 -- l_toPSON iby_trxn_summaries_all.tangibleid%TYPE;
167
168 BEGIN
169
170 iby_debug_pub.add(debug_msg => 'Enter: ' || l_Debug_Module,
171 debug_level => FND_LOG.LEVEL_PROCEDURE,
172 module => l_Debug_Module);
173
174 CEP_STANDARD.init_security;
175
176 l_where_clause := Get_Payer_Notif_Where_cluase
177 (
178 p_mbatchid => p_mbatchid,
179 p_fromDate => p_fromDate,
180 p_toDate => p_toDate,
181 p_fromPSON => p_fromPSON,
182 p_toPSON => p_toPSON,
183 p_delivery_method => p_delivery_method,
184 p_format_code => p_format_code,
185 p_debug_module => l_Debug_Module
186 );
187
188 l_extract_query := l_extract_query || l_where_clause;
189
190 IF p_txn_id <> -99 THEN
191 l_extract_query := l_extract_query || ' and txn.trxnmid = ' || p_txn_id;
192 END IF;
193
194 -- back out this changes for performance reasons
195 -- this is required since the variables come to the word null to determine
196 -- it is a null value
197 -- IF nvl(upper(p_mbatchid), 'NULL') <> 'NULL' THEN
198 -- l_mbatchid := p_mbatchid;
199 -- END IF;
200
201 -- IF nvl(upper(p_toDate), 'NULL') <> 'NULL' THEN
202 -- IF instr(p_toDate, '00:00:00') <> 0 THEN
203 -- l_toDate := REPLACE(p_toDate, '00:00:00', '23:59:59');
204 -- ELSE
205 -- l_toDate := p_toDate;
206 -- END IF;
207 -- END IF;
208
209 -- IF nvl(upper(p_fromPSON), 'NULL') <> 'NULL' THEN
210 -- l_fromPSON := p_fromPSON;
211 -- END IF;
212
213 -- IF nvl(upper(p_toPSON), 'NULL') <> 'NULL' THEN
214 -- l_toPSON := p_toPSON;
215 -- END IF;
216
217 -- IF nvl(upper(p_delivery_method), 'NULL') <> 'NULL' THEN
218 -- l_delivery_method := p_delivery_method;
219 -- END IF;
220
221 -- IF p_txn_id <> -99 THEN
222 -- l_trxn_id := p_txn_id;
223 -- END IF;
224
225 -- l_extract_query := l_extract_query||' and txn.trxnmid = NVL(:p_txn_id, txn.trxnmid) ';
226
227 G_Extract_Run_Mode := G_EXTRACT_MODE_SRA;
228 G_Extract_Run_Delivery_Method := p_delivery_method;
229 G_Extract_Run_Payment_id := p_txn_id;
230
231 Setup_for_Extract(p_sys_key);
232
233 iby_debug_pub.add(debug_msg => 'After Setup_for_Extract() ',
234 debug_level => FND_LOG.LEVEL_STATEMENT,
235 module => l_Debug_Module);
236
237 iby_debug_pub.add(debug_msg => 'Before executing dynamic query.',
238 debug_level => FND_LOG.LEVEL_STATEMENT,
239 module => l_Debug_Module);
240
241 iby_debug_pub.add(debug_msg => 'l_extract_query: ' || l_extract_query,
242 debug_level => FND_LOG.LEVEL_STATEMENT,
243 module => l_Debug_Module);
244
245 --iby_debug_pub.add(debug_msg => 'variables: '||p_fromDate||':'||l_toDate||':'||
246 -- l_mbatchid||':'||l_fromPSON||':'||l_toPSON||':'||
247 -- l_delivery_method||':'||p_format_code||':'||l_trxn_id,
248 -- debug_level => FND_LOG.LEVEL_STATEMENT,
249 -- module => l_Debug_Module);
250
251 EXECUTE IMMEDIATE l_extract_query INTO l_xml USING p_fromDate, p_delivery_method, p_format_code;
252
253 -- EXECUTE IMMEDIATE l_extract_query INTO l_xml
254 -- USING p_fromDate, l_toDate, l_mbatchid,
255 -- l_fromPSON, l_toPSON,
256 -- l_delivery_method, p_format_code,
257 -- l_trxn_id;
258
259 x_extract_doc := XMLType.getClobVal(l_xml);
260
261 iby_debug_pub.add(debug_msg => 'Exit: ' || l_Debug_Module,
262 debug_level => FND_LOG.LEVEL_PROCEDURE,
263 module => l_Debug_Module);
264
265 -- clears out data from global temporary table
266 COMMIT;
267
268 EXCEPTION
269 WHEN OTHERS THEN
270 -- make sure procedure is not exited before a COMMIT
271 -- so as to remove security keys
272 COMMIT;
273 RAISE;
274
275 END Create_Payer_Notif_Extract_1_0;
276
277
278 -- shared. Main entry point for FC accompany letter
279 PROCEDURE Setup_for_Extract
280 (
281 p_sys_key IN iby_security_pkg.DES3_KEY_TYPE
282 )
283 IS
284 lx_err_code VARCHAR2(30);
285 l_xml_base VARCHAR2(255);
286 l_char_extract_mdoe VARCHAR2(255) := 'G_EXTRACT_MODE_PMT';
287 l_Debug_Module VARCHAR2(255) :=
288 G_DEBUG_MODULE || '.Setup_for_Extract [Shared]';
289 BEGIN
290
291 iby_utility_pvt.get_property('IBY_XML_BASE',l_xml_base);
292
293 iby_utility_pvt.set_view_param(G_VP_XML_BASE,NVL(l_xml_base,''));
294
295 IF G_Extract_Run_Mode = G_EXTRACT_MODE_SRA THEN
296 l_char_extract_mdoe := 'G_EXTRACT_MODE_SRA';
297 END IF;
298
299 iby_utility_pvt.set_view_param(G_VP_EXTRACT_MODE, l_char_extract_mdoe);
300
301 iby_debug_pub.add(debug_msg => 'The extract mode is: ' || G_Extract_Run_Mode,
302 debug_level => FND_LOG.LEVEL_STATEMENT,
303 module => l_Debug_Module);
304
305 IF (NOT p_sys_key IS NULL) THEN
306 iby_security_pkg.validate_sys_key(p_sys_key,lx_err_code);
307 IF (NOT lx_err_code IS NULL) THEN
308 raise_application_error(-20000,lx_err_code, FALSE);
309 END IF;
310 iby_utility_pvt.set_view_param(G_VP_SYS_KEY,p_sys_key);
311 END IF;
312
313 END Setup_for_Extract;
314
315 -- shared. Main entry point for FC accompany letter
316 PROCEDURE Create_Extract_1_0
317 (
318 p_instr_type IN VARCHAR2,
319 p_req_type IN VARCHAR2,
320 p_txn_id IN NUMBER,
321 p_sys_key IN iby_security_pkg.DES3_KEY_TYPE,
322 x_extract_doc OUT NOCOPY CLOB
323 )
324 IS
325 l_Debug_Module VARCHAR2(255) :=
326 G_DEBUG_MODULE || '.Create_Extract_1_0 [Shared]';
327 BEGIN
328
329 Setup_for_Extract(p_sys_key);
330
331 IF ((p_req_type = 'ORAPMTCLOSEBATCH')
332 OR (p_req_type = 'ORAPMTEFTCLOSEBATCH')
333 OR (p_req_type = 'ORAPMTPDCCLOSEBATCH')) THEN
334
335 SELECT XMLType.getClobVal(instruction)
336 INTO x_extract_doc
337 FROM iby_xml_batch_fci_1_0_v
338 WHERE mbatchid=p_txn_id
339 AND rownum=1;
340 ELSE
341 SELECT XMLType.getClobVal(instruction)
342 INTO x_extract_doc
343 FROM iby_xml_online_fci_1_0_v
344 WHERE trxnmid=p_txn_id
345 AND rownum=1;
346 END IF;
347
348 -- clears out data from global temporary table
349 COMMIT;
350
351 EXCEPTION
352 WHEN OTHERS THEN
353 -- make sure procedure is not exited before a COMMIT
354 -- so as to remove security keys
355 COMMIT;
356 RAISE;
357
358 END Create_Extract_1_0;
359
360
361 -- overloaded version with
362 -- p_sec_val for credit card cvv2
363 PROCEDURE Create_Extract_1_0
364 (
365 p_instr_type IN VARCHAR2,
366 p_req_type IN VARCHAR2,
367 p_txn_id IN NUMBER,
368 p_sys_key IN iby_security_pkg.DES3_KEY_TYPE,
369 p_sec_val IN VARCHAR2,
370 x_extract_doc OUT NOCOPY CLOB
371 )
372 IS
373 l_xml_base VARCHAR2(255);
374 lx_err_code VARCHAR2(30);
375 BEGIN
376
377 G_Extract_Run_Mode := G_EXTRACT_MODE_PMT;
378
379 iby_utility_pvt.set_view_param(G_VP_SEC_VAL ,NVL(p_sec_val,' '));
380
381 Create_Extract_1_0
382 (
383 p_instr_type => p_instr_type,
384 p_req_type => p_req_type,
385 p_txn_id => p_txn_id,
386 p_sys_key => p_sys_key,
387 x_extract_doc => x_extract_doc
388 );
389
390 END Create_Extract_1_0;
391
392 -- obselete per bug 5115161
393 FUNCTION Get_Ins_PayeeAcctAgg(p_mbatch_id IN NUMBER)
394 RETURN XMLTYPE
395 IS
396
397 l_payeeacct_agg XMLTYPE;
398 l_Debug_Module VARCHAR2(255) := G_DEBUG_MODULE || '.Get_Ins_PayeeAcctAgg';
399
400 -- the cursors should be kept in-sync with IBY_XML_FNDCPT_ACCT_1_0_V
401 -- electronic payer notification
402 CURSOR l_payeeacct_ele_csr (p_mbatch_id IN NUMBER) IS
403 SELECT
404 XMLElement("PayeeAccount",
405 XMLElement("PaymentSystemAccount",
406 XMLElement("AccountName",txn.bepkey),
407 Extract(XMLAgg(XMLElement("OptionSet",opts.account_options)),
408 'OptionSet[1]/*')
409 ),
410 CASE WHEN (NOT xml_bank.instrid IS NULL) THEN
411 Extract(XMLAgg(XMLElement("BankAccount",xml_bank.bank_account_content)),
412 '/BankAccount[1]')
413 END,
414 XMLElement("Payee",
415 XMLElement("Name",payee.name),
416 XMLElement("Address",
417 XMLElement("AddressLine1",null),
418 XMLForest(null AS "AddressLine2",null AS "AddressLine3"),
419 XMLElement("City",null),
420 XMLElement("State",null),
421 XMLElement("Country",null),
422 XMLElement("PostalCode",null)
423 ),
424 XMLForest(DECODE(payee.mcc_code, -1,null, payee.mcc_code) AS "MCC")
425 ),
426 XMLElement("OrderCount",count(txn.trxnmid)),
427 XMLElement("AccountTotals",
428 XMLElement("AuthorizationsTotal",
429 XMLElement("Value",
430 DECODE(txn.INSTRTYPE, 'PINLESSDEBITCARD', 0,
431 'BANKACCOUNT', 0,
432 SUM(DECODE(txn.trxntypeid, 2,txn.amount, 0)) )),
433 XMLElement("Currency",
434 XMLElement("Code",MAX(txn.currencynamecode))
435 )
436 ),
437 XMLElement("CapturesTotal",
438 XMLElement("Value",
439 DECODE(txn.INSTRTYPE, 'PINLESSDEBITCARD', SUM(DECODE(txn.trxntypeid, 2,txn.amount, 0)),
440 'BANKACCOUNT', SUM(DECODE(txn.REQTYPE, 'ORAPMTREQ',txn.amount, 0)),
441 SUM(DECODE(txn.trxntypeid, 3,txn.amount, 8,txn.amount, 0)) )),
442 XMLElement("Currency",
443 XMLElement("Code",MAX(txn.currencynamecode))
444 )
445 ),
446 XMLElement("CreditsTotal",
447 XMLElement("Value",
448 DECODE(txn.INSTRTYPE, 'PINLESSDEBITCARD', 0,
449 'BANKACCOUNT', SUM(DECODE(txn.REQTYPE, 'ORAPMTCREDIT',txn.amount, 0)),
450 SUM(DECODE(txn.trxntypeid, 5,txn.amount, 11,txn.amount, 0)) )),
451 XMLElement("Currency",
452 XMLElement("Code",MAX(txn.currencynamecode))
453 )
454 )
455 ),
456 XMLAgg(xml_order.fndcpt_order)
457 )--,
458 --txn.mbatchid,
459 --txn.payeeinstrid
460 FROM
461 iby_trxn_summaries_all txn,
462 iby_payee payee,
463 iby_bepkeys keys,
464 iby_xml_fndcpt_bankaccount_v xml_bank,
465 iby_xml_bep_acct_options_v opts,
466 iby_xml_fndcpt_order_1_0_v xml_order
467 WHERE (txn.payeeid = payee.payeeid)
468 AND (txn.payeeinstrid = xml_bank.instrid(+))
469 AND (txn.payeeid = keys.ownerid)
470 AND (txn.bepkey = keys.key)
471 AND (keys.ownertype = 'PAYEE')
472 AND (keys.bep_account_id = opts.bep_account_id(+))
473 AND (txn.trxnmid = xml_order.trxnmid)
474 AND txn.trxnmid = G_Extract_Run_Payment_id
475 AND txn.mbatchid = p_mbatch_id
476 GROUP BY
477 txn.mbatchid, txn.payeeinstrid, txn.instrtype, txn.bepkey,
478 payee.name, payee.mcc_code, opts.bep_account_id,
479 xml_bank.instrid;
480
481 CURSOR l_payeeacct_prt_csr (p_mbatch_id IN NUMBER) IS
482 SELECT
483 XMLElement("PayeeAccount",
484 XMLElement("PaymentSystemAccount",
485 XMLElement("AccountName",txn.bepkey),
486 Extract(XMLAgg(XMLElement("OptionSet",opts.account_options)),
487 'OptionSet[1]/*')
488 ),
489 CASE WHEN (NOT xml_bank.instrid IS NULL) THEN
490 Extract(XMLAgg(XMLElement("BankAccount",xml_bank.bank_account_content)),
491 '/BankAccount[1]')
492 END,
493 XMLElement("Payee",
494 XMLElement("Name",payee.name),
495 XMLElement("Address",
496 XMLElement("AddressLine1",null),
497 XMLForest(null AS "AddressLine2",null AS "AddressLine3"),
498 XMLElement("City",null),
499 XMLElement("State",null),
500 XMLElement("Country",null),
501 XMLElement("PostalCode",null)
502 ),
503 XMLForest(DECODE(payee.mcc_code, -1,null, payee.mcc_code) AS "MCC")
504 ),
505 XMLElement("OrderCount",count(txn.trxnmid)),
506 XMLElement("AccountTotals",
507 XMLElement("AuthorizationsTotal",
508 XMLElement("Value",
509 DECODE(txn.INSTRTYPE, 'PINLESSDEBITCARD', 0,
510 'BANKACCOUNT', 0,
511 SUM(DECODE(txn.trxntypeid, 2,txn.amount, 0)) )),
512 XMLElement("Currency",
513 XMLElement("Code",MAX(txn.currencynamecode))
514 )
515 ),
516 XMLElement("CapturesTotal",
517 XMLElement("Value",
518 DECODE(txn.INSTRTYPE, 'PINLESSDEBITCARD', SUM(DECODE(txn.trxntypeid, 2,txn.amount, 0)),
519 'BANKACCOUNT', SUM(DECODE(txn.REQTYPE, 'ORAPMTREQ',txn.amount, 0)),
520 SUM(DECODE(txn.trxntypeid, 3,txn.amount, 8,txn.amount, 0)) )),
521 XMLElement("Currency",
522 XMLElement("Code",MAX(txn.currencynamecode))
523 )
524 ),
525 XMLElement("CreditsTotal",
526 XMLElement("Value",
527 DECODE(txn.INSTRTYPE, 'PINLESSDEBITCARD', 0,
528 'BANKACCOUNT', SUM(DECODE(txn.REQTYPE, 'ORAPMTCREDIT',txn.amount, 0)),
529 SUM(DECODE(txn.trxntypeid, 5,txn.amount, 11,txn.amount, 0)) )),
530 XMLElement("Currency",
531 XMLElement("Code",MAX(txn.currencynamecode))
532 )
533 )
534 ),
535 XMLAgg(xml_order.fndcpt_order)
536 )--,
537 --txn.mbatchid,
538 --txn.payeeinstrid
539 FROM
540 iby_trxn_summaries_all txn,
541 iby_payee payee,
542 iby_bepkeys keys,
543 iby_xml_fndcpt_bankaccount_v xml_bank,
544 iby_xml_bep_acct_options_v opts,
545 iby_xml_fndcpt_order_1_0_v xml_order
546 WHERE (txn.payeeid = payee.payeeid)
547 AND (txn.payeeinstrid = xml_bank.instrid(+))
548 AND (txn.payeeid = keys.ownerid)
549 AND (txn.bepkey = keys.key)
550 AND (keys.ownertype = 'PAYEE')
551 AND (keys.bep_account_id = opts.bep_account_id(+))
552 AND (txn.trxnmid = xml_order.trxnmid)
553 AND Get_SRA_Attribute(txn.trxnmid, G_SRA_DELIVERY_METHOD_ATTR) = G_SRA_DELIVERY_METHOD_PRINTED
554 AND txn.mbatchid = p_mbatch_id
555 GROUP BY
556 txn.mbatchid, txn.payeeinstrid, txn.instrtype, txn.bepkey,
557 payee.name, payee.mcc_code, opts.bep_account_id,
558 xml_bank.instrid;
559
560 BEGIN
561
562 iby_debug_pub.add(debug_msg => 'Enter: ' || l_Debug_Module,
563 debug_level => FND_LOG.LEVEL_PROCEDURE,
564 module => l_Debug_Module);
565
566 iby_debug_pub.add(debug_msg => 'Extract mode is G_EXTRACT_MODE_SRA. ',
567 debug_level => FND_LOG.LEVEL_STATEMENT,
568 module => l_Debug_Module);
569
570 IF G_Extract_Run_Delivery_Method = G_SRA_DELIVERY_METHOD_PRINTED THEN
571
572 iby_debug_pub.add(debug_msg => 'Delivery method is printed. ',
573 debug_level => FND_LOG.LEVEL_STATEMENT,
574 module => l_Debug_Module);
575
576 OPEN l_payeeacct_prt_csr (p_mbatch_id);
577 FETCH l_payeeacct_prt_csr INTO l_payeeacct_agg;
578 CLOSE l_payeeacct_prt_csr;
579
580 iby_debug_pub.add(debug_msg => 'After fetch from payee account cursor. ',
581 debug_level => FND_LOG.LEVEL_STATEMENT,
582 module => l_Debug_Module);
583
584 ELSIF G_Extract_Run_Delivery_Method = G_SRA_DELIVERY_METHOD_EMAIL OR
585 G_Extract_Run_Delivery_Method = G_SRA_DELIVERY_METHOD_FAX THEN
586
587 iby_debug_pub.add(debug_msg => 'Delivery method is Email/Fax. ',
588 debug_level => FND_LOG.LEVEL_STATEMENT,
589 module => l_Debug_Module);
590
591 OPEN l_payeeacct_ele_csr (p_mbatch_id);
592 FETCH l_payeeacct_ele_csr INTO l_payeeacct_agg;
593 CLOSE l_payeeacct_ele_csr;
594
595 iby_debug_pub.add(debug_msg => 'After fetch from payee account cursor. ',
596 debug_level => FND_LOG.LEVEL_STATEMENT,
597 module => l_Debug_Module);
598 END IF;
599
600 RETURN l_payeeacct_agg;
601
602 iby_debug_pub.add(debug_msg => 'Exit: ' || l_Debug_Module,
603 debug_level => FND_LOG.LEVEL_PROCEDURE,
604 module => l_Debug_Module);
605
606 END Get_Ins_PayeeAcctAgg;
607
608
609 FUNCTION Get_SRA_Attribute(p_trxnmid IN NUMBER, p_attribute_type IN NUMBER)
610 RETURN VARCHAR2
611 IS
612 l_instrument_type VARCHAR2(30);
613 l_sra_delivery_method VARCHAR2(30);
614 l_pf_sra_format VARCHAR2(30);
615 l_override_payer_flag VARCHAR2(1);
616 l_pf_sra_delivery_method VARCHAR2(30);
617 l_ps_lang VARCHAR2(4);
618 l_ps_territory VARCHAR2(60);
619
620 CURSOR l_instrument_type_csr (p_trxnmid IN NUMBER) IS
621 SELECT bat.instrument_type
622 FROM iby_trxn_summaries_all txn, iby_batches_all bat
623 WHERE txn.trxnmid = p_trxnmid
624 AND txn.mbatchid = bat.mbatchid;
625
626 CURSOR l_eft_sra_setup_csr (p_trxnmid IN NUMBER) IS
627 SELECT sys_pf.OVERRIDE_PAYER_DELIVERY_FLAG, sys_pf.PAYER_NOTIFICATION_DEL_METHOD,
628 sys_pf.payer_notification_format
629 FROM iby_trxn_summaries_all txn, iby_batches_all bat,
630 iby_fndcpt_sys_eft_pf_b sys_pf, iby_fndcpt_user_eft_pf_b user_pf
631 WHERE txn.trxnmid = p_trxnmid
632 AND txn.mbatchid = bat.mbatchid
633 AND bat.process_profile_code = user_pf.user_eft_profile_code
634 AND user_pf.sys_eft_profile_code = sys_pf.sys_eft_profile_code;
635
636 CURSOR l_cc_sra_setup_csr (p_trxnmid IN NUMBER) IS
637 SELECT sys_pf.OVERRIDE_PAYER_DELIVERY_FLAG, sys_pf.PAYER_NOTIFICATION_DEL_METHOD,
638 sys_pf.payer_notification_format
639 FROM iby_trxn_summaries_all txn, iby_batches_all bat,
640 iby_fndcpt_sys_cc_pf_b sys_pf, iby_fndcpt_user_cc_pf_b user_pf
641 WHERE txn.trxnmid = p_trxnmid
642 AND txn.mbatchid = bat.mbatchid
643 AND bat.process_profile_code = user_pf.user_cc_profile_code
644 AND user_pf.sys_cc_profile_code = sys_pf.sys_cc_profile_code;
645
646 CURSOR l_dc_sra_setup_csr (p_trxnmid IN NUMBER) IS
647 SELECT sys_pf.OVERRIDE_PAYER_DELIVERY_FLAG, sys_pf.PAYER_NOTIFICATION_DEL_METHOD,
648 sys_pf.payer_notification_format
649 FROM iby_trxn_summaries_all txn, iby_batches_all bat,
650 iby_fndcpt_sys_dc_pf_b sys_pf, iby_fndcpt_user_dc_pf_b user_pf
651 WHERE txn.trxnmid = p_trxnmid
652 AND txn.mbatchid = bat.mbatchid
653 AND bat.process_profile_code = user_pf.user_dc_profile_code
654 AND user_pf.sys_dc_profile_code = sys_pf.sys_dc_profile_code;
655
656 CURSOR l_lang_territory_csr (p_trxnmid IN NUMBER) IS
657 SELECT loc.language, loc.country
658 FROM hz_party_sites ps, hz_locations loc,
659 iby_trxn_summaries_all txn, hz_cust_site_uses_all hz_csu,
660 hz_cust_acct_sites_all hz_cs
661 where txn.trxnmid = p_trxnmid
662 and hz_csu.cust_acct_site_id = hz_cs.cust_acct_site_id
663 and hz_cs.party_site_id = ps.party_site_id
664 AND txn.acct_site_use_id = hz_csu.site_use_id(+)
665 AND loc.location_id = ps.location_id;
666
667 BEGIN
668
669 IF p_attribute_type = G_SRA_DELIVERY_METHOD_ATTR OR
670 p_attribute_type = G_PF_FORMAT_ATTR THEN
671
672 OPEN l_instrument_type_csr (p_trxnmid);
673 FETCH l_instrument_type_csr INTO l_instrument_type;
674 CLOSE l_instrument_type_csr;
675
676 IF l_instrument_type = 'BANKACCOUNT' THEN
677
678 OPEN l_eft_sra_setup_csr (p_trxnmid);
679 FETCH l_eft_sra_setup_csr INTO l_override_payer_flag, l_pf_sra_delivery_method, l_pf_sra_format;
680 CLOSE l_eft_sra_setup_csr;
681
682 ELSIF l_instrument_type = 'CREDITCARD' THEN
683
684 OPEN l_cc_sra_setup_csr (p_trxnmid);
685 FETCH l_cc_sra_setup_csr INTO l_override_payer_flag, l_pf_sra_delivery_method, l_pf_sra_format;
686 CLOSE l_cc_sra_setup_csr;
687
688 ELSIF l_instrument_type = 'DEBITCARD' THEN
689
690 OPEN l_dc_sra_setup_csr (p_trxnmid);
691 FETCH l_dc_sra_setup_csr INTO l_override_payer_flag, l_pf_sra_delivery_method, l_pf_sra_format;
692 CLOSE l_dc_sra_setup_csr;
693
694 END IF;
695
696 IF p_attribute_type = G_PF_FORMAT_ATTR THEN
697 RETURN l_pf_sra_format;
698 END IF;
699
700 IF l_override_payer_flag = 'Y' THEN
701 l_sra_delivery_method := l_pf_sra_delivery_method;
702
703 ELSE
704 l_sra_delivery_method := Get_Payer_Default_Attribute(p_trxnmid, p_attribute_type);
705
706 IF l_sra_delivery_method is null THEN
707 l_sra_delivery_method := l_pf_sra_delivery_method;
708 END IF;
709 END IF;
710
711 return l_sra_delivery_method;
712
713 ELSIF p_attribute_type = G_SRA_REQ_FLAG_ATTR THEN
714
715 return 'Y';
716
717 ELSIF p_attribute_type = G_SRA_PS_LANG_ATTR OR
718 p_attribute_type = G_SRA_PS_TERRITORY_ATTR THEN
719
720 OPEN l_lang_territory_csr (p_trxnmid);
721 FETCH l_lang_territory_csr INTO l_ps_lang, l_ps_territory;
722 CLOSE l_lang_territory_csr;
723
724 IF p_attribute_type = G_SRA_PS_LANG_ATTR THEN
725 return l_ps_lang;
726 ELSE
727 return l_ps_territory;
728 END IF;
729
730 ELSE
731 return Get_Payer_Default_Attribute(p_trxnmid, p_attribute_type);
732 END IF;
733
734 END Get_SRA_Attribute;
735
736
737 FUNCTION Get_Payer_Default_Attribute(p_trxnmid IN NUMBER, p_attribute_type IN NUMBER)
738 RETURN VARCHAR2
739 IS
740
741 l_attribute_val VARCHAR2(1000);
742
743 CURSOR l_payer_defaulting_cur (p_trxnmid NUMBER) IS
744 SELECT payer.debit_advice_delivery_method,
745 payer.debit_advice_email,
746 payer.debit_advice_fax
747 FROM iby_external_payers_all payer,
748 iby_trxn_summaries_all txn
749 WHERE payer.party_id = txn.payer_party_id
750 AND (payer.org_id is NULL OR (payer.org_id = txn.org_id AND payer.org_type = txn.org_type))
751 AND (payer.cust_account_id is NULL OR payer.cust_account_id = txn.cust_account_id)
752 AND (payer.acct_site_use_id is NULL OR payer.acct_site_use_id = txn.acct_site_use_id)
753 AND txn.trxnmid = p_trxnmid
754 ORDER BY payer.acct_site_use_id, payer.cust_account_id, payer.org_id;
755
756 BEGIN
757
758 FOR l_default_rec in l_payer_defaulting_cur(p_trxnmid) LOOP
759 IF (l_attribute_val is NULL) THEN
760 IF p_attribute_type = G_SRA_DELIVERY_METHOD_ATTR THEN
761 l_attribute_val := l_default_rec.debit_advice_delivery_method;
762 ELSIF p_attribute_type = G_SRA_EMAIL_ATTR THEN
763 l_attribute_val := l_default_rec.debit_advice_email;
764 ELSIF p_attribute_type = G_SRA_FAX_ATTR THEN
765 l_attribute_val := l_default_rec.debit_advice_fax;
766 END IF;
767 END IF;
768 END LOOP;
769
770 return l_attribute_val;
771 END Get_Payer_Default_Attribute;
772
773
774 FUNCTION Get_Batch_Format(p_batchid IN VARCHAR2, p_format_type IN VARCHAR2)
775 RETURN VARCHAR2
776 IS
777
778 CURSOR l_instrument_type_csr (p_batchid IN VARCHAR2) IS
779 SELECT bat.instrument_type
780 FROM iby_batches_all bat
781 WHERE bat.batchid = p_batchid;
782
783 CURSOR l_eft_format_csr (p_batchid IN VARCHAR2) IS
784 SELECT sys_pf.PAYER_NOTIFICATION_FORMAT, sys_pf.ACCOMPANY_LETTER_FORMAT
785 FROM iby_batches_all bat,
786 iby_fndcpt_sys_eft_pf_b sys_pf, iby_fndcpt_user_eft_pf_b user_pf
787 WHERE bat.batchid = p_batchid
788 AND bat.process_profile_code = user_pf.user_eft_profile_code
789 AND user_pf.sys_eft_profile_code = sys_pf.sys_eft_profile_code;
790
791 CURSOR l_cc_format_csr (p_batchid IN VARCHAR2) IS
792 SELECT sys_pf.PAYER_NOTIFICATION_FORMAT
793 FROM iby_batches_all bat,
794 iby_fndcpt_sys_cc_pf_b sys_pf, iby_fndcpt_user_cc_pf_b user_pf
795 WHERE bat.batchid = p_batchid
796 AND bat.process_profile_code = user_pf.user_cc_profile_code
797 AND user_pf.sys_cc_profile_code = sys_pf.sys_cc_profile_code;
798
799 CURSOR l_dc_format_csr (p_batchid IN VARCHAR2) IS
800 SELECT sys_pf.PAYER_NOTIFICATION_FORMAT
801 FROM iby_batches_all bat,
802 iby_fndcpt_sys_dc_pf_b sys_pf, iby_fndcpt_user_dc_pf_b user_pf
803 WHERE bat.batchid = p_batchid
804 AND bat.process_profile_code = user_pf.user_dc_profile_code
805 AND user_pf.sys_dc_profile_code = sys_pf.sys_dc_profile_code;
806
807 l_instr_type VARCHAR2(30);
808 l_acp_ltr_format VARCHAR2(30);
809 l_payer_notif_format VARCHAR2(30);
810
811 BEGIN
812
813 OPEN l_instrument_type_csr (p_batchid);
814 FETCH l_instrument_type_csr INTO l_instr_type;
815 CLOSE l_instrument_type_csr;
816
817 IF l_instr_type = 'BANKACCOUNT' THEN
818
819 OPEN l_eft_format_csr (p_batchid);
820 FETCH l_eft_format_csr INTO l_payer_notif_format, l_acp_ltr_format;
821 CLOSE l_eft_format_csr;
822
823 ELSIF l_instr_type = 'CREDITCARD' THEN
824
825 OPEN l_cc_format_csr (p_batchid);
826 FETCH l_cc_format_csr INTO l_payer_notif_format;
827 CLOSE l_cc_format_csr;
828
829 ELSIF l_instr_type = 'DEBITCARD' THEN
830
831 OPEN l_dc_format_csr (p_batchid);
832 FETCH l_dc_format_csr INTO l_payer_notif_format;
833 CLOSE l_dc_format_csr;
834
835 END IF;
836
837 IF p_format_type = 'PAYER_NOTIFICATION' THEN
838 RETURN l_payer_notif_format;
839 ELSIF p_format_type = 'FUNDS_CAPTURE_ACCOMPANY_LETTER' THEN
840 RETURN l_acp_ltr_format;
841 END IF;
842
843 END Get_Batch_Format;
844
845
846 -- obsolete use Update_Pmt_SRA_Attr_Ele()
847 PROCEDURE Update_Pmt_SRA_Attr_Prt
848 (
849 p_mbatchid IN VARCHAR2,
850 p_fromDate IN VARCHAR2,
851 p_toDate IN VARCHAR2,
852 p_fromPSON IN VARCHAR2,
853 p_toPSON IN VARCHAR2,
854 p_delivery_method IN VARCHAR2,
855 p_format_code IN VARCHAR2
856 )
857 IS
858 l_Debug_Module VARCHAR2(255) := G_DEBUG_MODULE || '.Update_Pmt_SRA_Attr_Prt';
859
860 l_where_clause VARCHAR2(4000);
861 l_update_stmt VARCHAR2(4000) :=
862 'UPDATE iby_trxn_summaries_all txn SET ' ||
863 ' debit_advice_delivery_method = ''PRINTED'', ' ||
864 ' debit_advice_email = null, ' ||
865 ' debit_advice_fax = null, ' ||
866 ' payer_notification_created = ''Y'', ' ||
867 ' object_version_number = object_version_number + 1, ' ||
868 ' last_updated_by = fnd_global.user_id, ' ||
869 ' last_update_date = SYSDATE, ' ||
870 ' last_update_login = nvl(fnd_global.LOGIN_ID, fnd_global.conc_login_id) ' ||
871 'WHERE nvl(txn.payer_notification_required, ''N'') = ''Y'' ';
872
873 BEGIN
874
875 iby_debug_pub.add(debug_msg => 'Enter: ' || l_Debug_Module,
876 debug_level => FND_LOG.LEVEL_PROCEDURE,
877 module => l_Debug_Module);
878
879 l_where_clause := Get_Payer_Notif_Where_cluase
880 (
881 p_mbatchid => p_mbatchid,
882 p_fromDate => p_fromDate,
883 p_toDate => p_toDate,
884 p_fromPSON => p_fromPSON,
885 p_toPSON => p_toPSON,
886 p_delivery_method => p_delivery_method,
887 p_format_code => p_format_code,
888 p_debug_module => l_Debug_Module
889 );
890
891 l_update_stmt := l_update_stmt || l_where_clause;
892
893 iby_debug_pub.add(debug_msg => 'Before executing dynamic update statement.',
894 debug_level => FND_LOG.LEVEL_STATEMENT,
895 module => l_Debug_Module);
896
897 iby_debug_pub.add(debug_msg => 'l_update_stmt: ' || l_update_stmt,
898 debug_level => FND_LOG.LEVEL_STATEMENT,
899 module => l_Debug_Module);
900
901 EXECUTE IMMEDIATE l_update_stmt;
902
903 iby_debug_pub.add(debug_msg => 'Exit: ' || l_Debug_Module,
904 debug_level => FND_LOG.LEVEL_PROCEDURE,
905 module => l_Debug_Module);
906
907 END Update_Pmt_SRA_Attr_Prt;
908
909
910 PROCEDURE Update_Pmt_SRA_Attr_Ele
911 (
912 p_trxnmid IN NUMBER,
913 p_delivery_method IN VARCHAR2,
914 p_recipient_email IN VARCHAR2,
915 p_recipient_fax IN VARCHAR2
916 )
917 IS
918 BEGIN
919
920 IF p_delivery_method = 'EMAIL' THEN
921 UPDATE
922 iby_trxn_summaries_all
923 SET
924 debit_advice_delivery_method = p_delivery_method,
925 debit_advice_email = p_recipient_email,
926 debit_advice_fax = null,
927 payer_notification_created = 'Y',
928 object_version_number = object_version_number + 1,
929 last_updated_by = fnd_global.user_id,
930 last_update_date = SYSDATE,
931 last_update_login = nvl(fnd_global.LOGIN_ID, fnd_global.conc_login_id)
932 WHERE trxnmid = p_trxnmid;
933 ELSIF p_delivery_method = 'FAX' THEN
934 UPDATE
935 iby_trxn_summaries_all
936 SET
937 debit_advice_delivery_method = p_delivery_method,
938 debit_advice_email = null,
939 debit_advice_fax = p_recipient_fax,
940 payer_notification_created = 'Y',
941 object_version_number = object_version_number + 1,
942 last_updated_by = fnd_global.user_id,
943 last_update_date = SYSDATE,
944 last_update_login = nvl(fnd_global.LOGIN_ID, fnd_global.conc_login_id)
945 WHERE trxnmid = p_trxnmid;
946 ELSIF p_delivery_method = 'PRINTED' THEN
947 UPDATE
948 iby_trxn_summaries_all
949 SET
950 debit_advice_delivery_method = p_delivery_method,
951 debit_advice_email = NULL,
952 debit_advice_fax = NULL,
953 payer_notification_created = 'Y',
954 object_version_number = object_version_number + 1,
955 last_updated_by = fnd_global.user_id,
956 last_update_date = SYSDATE,
957 last_update_login = nvl(fnd_global.LOGIN_ID, fnd_global.conc_login_id)
958 WHERE trxnmid = p_trxnmid;
959 END IF;
960
961 COMMIT;
962
963 END Update_Pmt_SRA_Attr_Ele;
964
965 FUNCTION submit_payer_notification
966 (
967 p_bep_type IN VARCHAR2,
968 p_settlement_batch IN VARCHAR2 DEFAULT NULL,
969 p_from_settlement_date IN DATE DEFAULT NULL,
970 p_to_settlement_date IN DATE DEFAULT NULL,
971 p_from_PSON IN VARCHAR2 DEFAULT NULL,
972 p_to_PSON IN VARCHAR2 DEFAULT NULL
973 ) RETURN NUMBER
974 IS
975 l_request_id NUMBER;
976 l_reqdate DATE;
977 l_Debug_Module VARCHAR2(255) := G_DEBUG_MODULE || '.submit_payer_notification';
978 l_icx_numeric_characters VARCHAR2(30); -- Bug 6411356
979 l_bool_val boolean; -- Bug 6411356
980
981 BEGIN
982
983 iby_debug_pub.add(debug_msg => 'Enter: ' || l_Debug_Module, debug_level => FND_LOG.LEVEL_PROCEDURE,
984 module => l_Debug_Module);
985
986 iby_debug_pub.add(debug_msg => 'p_bep_type: '||p_bep_type, debug_level => FND_LOG.LEVEL_STATEMENT,
987 module => l_Debug_Module);
988
989 iby_debug_pub.add(debug_msg => 'p_from_settlement_date: '||p_from_settlement_date, debug_level => FND_LOG.LEVEL_STATEMENT,
990 module => l_Debug_Module);
991
992 iby_debug_pub.add(debug_msg => 'p_to_settlement_date: '||p_to_settlement_date, debug_level => FND_LOG.LEVEL_STATEMENT,
993 module => l_Debug_Module);
994
995 iby_debug_pub.add(debug_msg => 'p_settlement_batch: '||p_settlement_batch, debug_level => FND_LOG.LEVEL_STATEMENT,
996 module => l_Debug_Module);
997
998 iby_debug_pub.add(debug_msg => 'p_from_PSON: '||p_from_PSON, debug_level => FND_LOG.LEVEL_STATEMENT,
999 module => l_Debug_Module);
1000
1001 iby_debug_pub.add(debug_msg => 'p_to_PSON: '||p_to_PSON, debug_level => FND_LOG.LEVEL_STATEMENT,
1002 module => l_Debug_Module);
1003
1004 -- The settlement date is required for the concurrent request, so included
1005 -- logic in API to get the settlement date based on query from concurrent
1006 -- request.
1007 IF p_from_settlement_date IS NULL AND p_bep_type = 'PROCESSOR' THEN
1008 BEGIN
1009 SELECT MIN(reqdate)
1010 INTO l_reqdate
1011 FROM iby_trxn_summaries_all
1012 WHERE batchid = p_settlement_batch
1013 AND NVL(payer_notification_required, 'N') = 'Y';
1014 EXCEPTION
1015 WHEN others THEN NULL;
1016 END;
1017
1018 iby_debug_pub.add(debug_msg => 'Reqdate is not passed and type is PROCESSOR. l_reqdate='||l_reqdate,
1019 debug_level => FND_LOG.LEVEL_STATEMENT, module => l_Debug_Module);
1020
1021 END IF;
1022
1023 iby_debug_pub.add(debug_msg => 'Before Calling FND_REQUEST.SUBMIT_REQUEST()', debug_level => FND_LOG.LEVEL_STATEMENT,
1024 module => l_Debug_Module);
1025
1026 --Bug 6411356
1027 --below code added to set the current nls character setting
1028 --before submitting a child requests.
1029 fnd_profile.get('ICX_NUMERIC_CHARACTERS',l_icx_numeric_characters);
1030 l_bool_val:= FND_REQUEST.SET_OPTIONS( numeric_characters => l_icx_numeric_characters);
1031
1032 -- submit the extract program
1033 l_request_id := FND_REQUEST.SUBMIT_REQUEST
1034 (
1035 'IBY',
1036 'IBY_FC_PAYER_NOTIF_FORMAT',
1037 null, -- description
1038 null, -- start_time
1039 FALSE, -- sub_request
1040 p_settlement_batch,
1041 NVL(p_from_settlement_date, l_reqdate),
1042 p_to_settlement_date,
1043 p_from_PSON,
1044 p_to_PSON,
1045 '', '', '', '',
1046 '', '', '', '', '', '', '', '',
1047 '', '', '', '', '', '', '', '',
1048 '', '', '', '', '', '', '', '',
1049 '', '', '', '', '', '', '', '',
1050 '', '', '', '', '', '', '', '',
1051 '', '', '', '', '', '', '', '',
1052 '', '', '', '', '', '', '', '',
1053 '', '', '', '', '', '', '', '',
1054 '', '', '', '', '', '', '', '',
1055 '', '', '', '', '', '', '', '',
1056 '', '', '', '', '', '', '', '',
1057 '', '', ''
1058 );
1059
1060 -- Added explicit commit in pl/sql. Request id is logged, but the request is not created in FND
1061 COMMIT;
1062
1063 iby_debug_pub.add(debug_msg => 'After Calling FND_REQUEST.SUBMIT_REQUEST()',
1064 debug_level => FND_LOG.LEVEL_STATEMENT,
1065 module => l_Debug_Module);
1066 iby_debug_pub.add(debug_msg => 'Request id: ' || l_request_id,
1067 debug_level => FND_LOG.LEVEL_STATEMENT,
1068 module => l_Debug_Module);
1069
1070 iby_debug_pub.add(debug_msg => 'Exit: ' || l_Debug_Module,
1071 debug_level => FND_LOG.LEVEL_PROCEDURE,
1072 module => l_Debug_Module);
1073
1074 RETURN l_request_id;
1075
1076 END submit_payer_notification;
1077
1078
1079 FUNCTION submit_accompany_letter
1080 (
1081 p_settlement_batch IN VARCHAR2
1082 ) RETURN NUMBER
1083 IS
1084 l_request_id NUMBER;
1085 l_Debug_Module VARCHAR2(255) := G_DEBUG_MODULE || '.submit_accompany_letter';
1086 l_icx_numeric_characters VARCHAR2(30); -- Bug 6411356
1087 l_bool_val boolean; -- Bug 6411356
1088
1089
1090 BEGIN
1091
1092 iby_debug_pub.add(debug_msg => 'Enter: ' || l_Debug_Module, debug_level => FND_LOG.LEVEL_PROCEDURE,
1093 module => l_Debug_Module);
1094
1095 iby_debug_pub.add(debug_msg => 'p_settlement_batch: '||p_settlement_batch, debug_level => FND_LOG.LEVEL_STATEMENT,
1096 module => l_Debug_Module);
1097
1098 iby_debug_pub.add(debug_msg => 'Before Calling FND_REQUEST.SUBMIT_REQUEST()', debug_level => FND_LOG.LEVEL_STATEMENT,
1099 module => l_Debug_Module);
1100
1101
1102 --Bug 6411356
1103 --below code added to set the current nls character setting
1104 --before submitting a child requests.
1105 fnd_profile.get('ICX_NUMERIC_CHARACTERS',l_icx_numeric_characters);
1106 l_bool_val:= FND_REQUEST.SET_OPTIONS( numeric_characters => l_icx_numeric_characters);
1107
1108 -- submit the extract program
1109 l_request_id := FND_REQUEST.SUBMIT_REQUEST
1110 (
1111 'IBY',
1112 'IBY_FC_ACP_LTR_FORMAT',
1113 null, -- description
1114 null, -- start_time
1115 FALSE, -- sub_request
1116 p_settlement_batch,
1117 '', '', '', '', '', '', '', '',
1118 '', '', '', '', '', '', '', '',
1119 '', '', '', '', '', '', '', '',
1120 '', '', '', '', '', '', '', '',
1121 '', '', '', '', '', '', '', '',
1122 '', '', '', '', '', '', '', '',
1123 '', '', '', '', '', '', '', '',
1124 '', '', '', '', '', '', '', '',
1125 '', '', '', '', '', '', '', '',
1126 '', '', '', '', '', '', '', '',
1127 '', '', '', '', '', '', '', '',
1128 '', '', '', '', '', '', '', '',
1129 '', '', ''
1130 );
1131
1132 -- Added explicit commit in pl/sql. Request id is logged, but the request is not created in FND
1133 COMMIT;
1134
1135 iby_debug_pub.add(debug_msg => 'After Calling FND_REQUEST.SUBMIT_REQUEST()',
1136 debug_level => FND_LOG.LEVEL_STATEMENT,
1137 module => l_Debug_Module);
1138 iby_debug_pub.add(debug_msg => 'Request id: ' || l_request_id,
1139 debug_level => FND_LOG.LEVEL_STATEMENT,
1140 module => l_Debug_Module);
1141
1142 iby_debug_pub.add(debug_msg => 'Exit: ' || l_Debug_Module,
1143 debug_level => FND_LOG.LEVEL_PROCEDURE,
1144 module => l_Debug_Module);
1145
1146 RETURN l_request_id;
1147
1148 END submit_accompany_letter;
1149
1150 END IBY_FNDCPT_EXTRACT_GEN_PVT;
1151