[Home] [Help]
PACKAGE BODY: APPS.IBY_FD_POST_PICP_PROGS_PVT
Source
1 PACKAGE BODY IBY_FD_POST_PICP_PROGS_PVT AS
2 /* $Header: ibyppicb.pls 120.44.12010000.6 2009/01/08 17:19:46 asarada ship $ */
3
4 g_pkg_name CONSTANT VARCHAR2(30) := 'IBY_FD_POST_PICP_PROGS_PVT';
5
6 ce_sp_access_C NUMBER := 0;
7
8 PROCEDURE Init_Security;
9
10
11 FUNCTION get_accessible_ppr_org_count
12 (
13 p_payment_service_request_id IN NUMBER
14 ) RETURN NUMBER;
15
16
17 PROCEDURE Submit_FV_TS_Report
18 (
19 p_payment_instruction_id IN NUMBER,
20 p_format_code IN VARCHAR2,
21 l_Debug_Module IN VARCHAR2
22 );
23
24
25 PROCEDURE Turn_off_STP_Flag
26 (
27 p_payment_instruction_id IN NUMBER,
28 p_newStatus IN VARCHAR2
29 );
30
31 -- APIs Start
32 PROCEDURE Process_Federal_Summary_Format
33 (
34 p_api_version IN NUMBER,
35 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
36 p_commit IN VARCHAR2 := FND_API.G_FALSE,
37 x_return_status OUT NOCOPY VARCHAR2,
38 x_msg_count OUT NOCOPY NUMBER,
39 x_msg_data OUT NOCOPY VARCHAR2,
40 p_payment_instruction_id IN NUMBER,
41 p_ecs_dos_seq_num IN NUMBER,
42 p_summary_format_code IN VARCHAR2,
43 x_request_id OUT NOCOPY NUMBER
44 )
45 IS
46 l_api_name CONSTANT VARCHAR2(30) := 'Process_Federal_Summary_Format';
47 l_api_version CONSTANT NUMBER := 1.0;
48 l_rollback_point CONSTANT VARCHAR2(30) := 'Process_Federal_Summary_Format';
49 l_Debug_Module VARCHAR2(255):= G_DEBUG_MODULE || l_api_name;
50 l_icx_numeric_characters VARCHAR2(30); -- Bug 6411356
51 l_bool_val boolean; -- Bug 6411356
52
53 BEGIN
54 -- standard start of api savepoint
55 SAVEPOINT l_rollback_point;
56
57
58 -- standard call to check for call compatibility.
59 IF NOT fnd_api.compatible_api_call(l_api_version,
60 p_api_version,
61 l_api_name,
62 g_pkg_name)
63 THEN
64 RAISE fnd_api.g_exc_unexpected_error;
65 END IF;
66
67 -- initialize message list if p_init_msg_list is set to true.
68 IF fnd_api.to_boolean(p_init_msg_list)
69 THEN
70 fnd_msg_pub.initialize;
71 END IF;
72
73 -- initialize api return status to success
74 x_return_status := fnd_api.g_ret_sts_success;
75
76 iby_debug_pub.add(debug_msg => 'Enter: ' || l_Debug_Module,
77 debug_level => FND_LOG.LEVEL_PROCEDURE,
78 module => l_Debug_Module);
79
80 iby_debug_pub.add(debug_msg => 'Input parameters: ',
81 debug_level => FND_LOG.LEVEL_STATEMENT,
82 module => l_Debug_Module);
83
84 iby_debug_pub.add(debug_msg => '============================================',
85 debug_level => FND_LOG.LEVEL_STATEMENT,
86 module => l_Debug_Module);
87
88 iby_debug_pub.add(debug_msg => 'p_payment_instruction_id: ' || p_payment_instruction_id,
89 debug_level => FND_LOG.LEVEL_STATEMENT,
90 module => l_Debug_Module);
91
92 iby_debug_pub.add(debug_msg => 'p_summary_format_code: ' || p_summary_format_code,
93 debug_level => FND_LOG.LEVEL_STATEMENT,
94 module => l_Debug_Module);
95
96 iby_debug_pub.add(debug_msg => 'p_ecs_dos_seq_num: ' || p_ecs_dos_seq_num,
97 debug_level => FND_LOG.LEVEL_STATEMENT,
98 module => l_Debug_Module);
99
100 iby_debug_pub.add(debug_msg => '============================================',
101 debug_level => FND_LOG.LEVEL_STATEMENT,
102 module => l_Debug_Module);
103
104 iby_debug_pub.add(debug_msg => 'Before Calling FND_REQUEST.SUBMIT_REQUEST().',
105 debug_level => FND_LOG.LEVEL_STATEMENT,
106 module => l_Debug_Module);
107
108 --Bug 6411356
109 --below code added to set the current nls character setting
110 --before submitting a child requests.
111 fnd_profile.get('ICX_NUMERIC_CHARACTERS',l_icx_numeric_characters);
112 l_bool_val:= FND_REQUEST.SET_OPTIONS( numeric_characters => l_icx_numeric_characters);
113
114
115 -- submit the extract program
116 x_request_id := FND_REQUEST.SUBMIT_REQUEST
117 (
118 'IBY',
119 'IBY_FD_FEDERAL_SUMMARY',
120 null, -- description
121 null, -- start_time
122 FALSE, -- sub_request
123 p_payment_instruction_id,
124 p_summary_format_code,
125 p_ecs_dos_seq_num,
126 '', '', '', '', '', '',
127 '', '', '', '', '', '', '', '',
128 '', '', '', '', '', '', '', '',
129 '', '', '', '', '', '', '', '',
130 '', '', '', '', '', '', '', '',
131 '', '', '', '', '', '', '', '',
132 '', '', '', '', '', '', '', '',
133 '', '', '', '', '', '', '', '',
134 '', '', '', '', '', '', '', '',
135 '', '', '', '', '', '', '', '',
136 '', '', '', '', '', '', '', '',
137 '', '', '', '', '', '', '', '',
138 '', '', ''
139 );
140
141 iby_debug_pub.add(debug_msg => 'After Calling FND_REQUEST.SUBMIT_REQUEST().',
142 debug_level => FND_LOG.LEVEL_STATEMENT,
143 module => l_Debug_Module);
144
145 iby_debug_pub.add(debug_msg => 'Request id: ' || x_request_id,
146 debug_level => FND_LOG.LEVEL_STATEMENT,
147 module => l_Debug_Module);
148
149 --
150 -- end of api body.
151 --
152
153 -- standard check for p_commit
154 IF fnd_api.to_boolean(p_commit)
155 THEN
156 COMMIT WORK;
157 END IF;
158
159 -- standard call to get message count and if count is 1, get message info.
160 fnd_msg_pub.count_and_get(
161 p_count => x_msg_count,
162 p_data => x_msg_data
163 );
164
165 EXCEPTION
166
167 WHEN FND_API.G_EXC_ERROR THEN
168 ROLLBACK TO l_rollback_point;
169 x_return_status := FND_API.G_RET_STS_ERROR;
170
171 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
172 ROLLBACK TO l_rollback_point;
173 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
174
175 WHEN OTHERS THEN
176 ROLLBACK TO l_rollback_point;
177 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
178 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
179 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
180 END IF;
181
182 END Process_Federal_Summary_Format;
183
184
185 PROCEDURE Run_Post_PI_Programs
186 (
187 p_payment_instruction_id IN NUMBER,
188 p_is_reprint_flag IN VARCHAR2
189 )
190 IS
191 l_request_id NUMBER;
192 l_set_opt_ok BOOLEAN;
193 l_set_print_opt_ok BOOLEAN;
194 l_ins_notfound BOOLEAN;
195 l_run_print BOOLEAN := false;
196 l_printer_name VARCHAR2(255);
197 l_print_immed_flag VARCHAR2(1);
198 l_transmit_immed_flag VARCHAR2(1);
199 l_instr_status VARCHAR2(30);
200 l_processing_type VARCHAR2(30);
201 x_msg_count NUMBER;
202 x_msg_data VARCHAR2(2550);
203 l_return_status VARCHAR2(1);
204 l_Debug_Module VARCHAR2(255) := G_DEBUG_MODULE || '.Run_Post_PI_Programs';
205 l_save_no_output VARCHAR2(1);
206
207 l_icx_numeric_characters VARCHAR2(30); -- Bug 6411356
208 l_bool_val boolean; -- Bug 6411356
209
210 --bug 6898689
211 l_appl_name VARCHAR2(20);
212 l_template_code VARCHAR2(150);
213 l_template_lang VARCHAR2(20);
214 l_template_terr VARCHAR2(20);
215 l_output_format VARCHAR2(15);
216 l_bool boolean;
217 CURSOR l_ins_csr (p_payment_instruction_id IN NUMBER) IS
218 SELECT ins.PRINTER_NAME, ins.payment_instruction_status,
219 ins.TRANSMIT_INSTR_IMMED_FLAG,
220 ins.PRINT_INSTRUCTION_IMMED_FLAG, pp.processing_type
221 FROM iby_pay_instructions_all ins,
222 iby_payment_profiles pp
223 WHERE ins.payment_instruction_id = p_payment_instruction_id
224 AND ins.payment_profile_id = pp.payment_profile_id;
225
226 BEGIN
227
228 iby_debug_pub.add(debug_msg => 'Enter: ' || l_Debug_Module,
229 debug_level => FND_LOG.LEVEL_PROCEDURE,
230 module => l_Debug_Module);
231
232 iby_debug_pub.add(debug_msg => 'Input payment instruction ID: ' || p_payment_instruction_id,
233 debug_level => FND_LOG.LEVEL_STATEMENT,
234 module => l_Debug_Module);
235
236 OPEN l_ins_csr(p_payment_instruction_id);
237 FETCH l_ins_csr INTO l_printer_name, l_instr_status, l_transmit_immed_flag,
238 l_print_immed_flag, l_processing_type;
239 l_ins_notfound := l_ins_csr%NOTFOUND;
240 CLOSE l_ins_csr;
241
242 IF l_ins_notfound THEN
243 -- set error for invalid data
244 fnd_message.set_name('IBY', 'IBY_FD_INVALID_PMT_INSTRUCTION');
245 fnd_message.set_token('PARAM', p_payment_instruction_id);
246 fnd_msg_pub.add;
247
248 RAISE FND_API.G_EXC_ERROR;
249 END IF;
250
251 IF l_instr_status not in ('FORMATTED_READY_FOR_PRINTING',
252 'SUBMITTED_FOR_PRINTING',
253 'CREATED_READY_FOR_PRINTING',
254 'CREATED_READY_FOR_FORMATTING',
255 'CREATED') THEN
256 -- set error for invalid data
257 fnd_message.set_name('IBY', 'IBY_FD_INVALID_PMT_INSTRUCTION');
258 fnd_message.set_token('PARAM', p_payment_instruction_id);
259 fnd_msg_pub.add;
260
261 RAISE FND_API.G_EXC_ERROR;
262 END IF;
263
264 -- the default param values are
265 -- implicit IN varchar2 default NO
266 -- protected IN varchar2 default NO
267 -- language IN varchar2 default NULL,
268 -- territory IN varchar2 default NULL
269 -- it appears that if the Use in SRS is turned off,
270 -- the CM will set the implicit to YES
271 l_set_opt_ok := FND_REQUEST.SET_OPTIONS;
272
273 IF l_set_opt_ok THEN
274 iby_debug_pub.add(debug_msg => 'Set request implicit to NO ',
275 debug_level => FND_LOG.LEVEL_STATEMENT,
276 module => l_Debug_Module);
277 ELSE
278 iby_debug_pub.add(debug_msg => 'Warning: unable to set request options ',
279 debug_level => FND_LOG.LEVEL_STATEMENT,
280 module => l_Debug_Module);
281 END IF;
282
283
284 IF l_instr_status in ('FORMATTED_READY_FOR_PRINTING', 'SUBMITTED_FOR_PRINTING') THEN
285 l_run_print := true;
286 ELSIF l_instr_status = 'CREATED_READY_FOR_PRINTING' AND l_print_immed_flag = 'Y' THEN
287 l_run_print := true;
288 END IF;
289
290 IF l_run_print THEN
291 iby_debug_pub.add(debug_msg => 'The payment instruction format output is to be printed.',
292 debug_level => FND_LOG.LEVEL_STATEMENT,
293 module => l_Debug_Module);
294
295 SELECT nvl(pp.disallow_save_print_flag,'N') into l_save_no_output
296 FROM iby_pay_instructions_all ins,
297 iby_payment_profiles pp
298 WHERE ins.payment_instruction_id = p_payment_instruction_id
299 AND ins.payment_profile_id = pp.payment_profile_id;
300
301 IF l_save_no_output = 'Y' THEN
302 l_set_print_opt_ok := FND_REQUEST.SET_PRINT_OPTIONS
303 (printer => l_printer_name,
304 style => null,
305 copies => 1,
306 save_output => FALSE);
307 ELSE
308 l_set_print_opt_ok := FND_REQUEST.SET_PRINT_OPTIONS
309 (printer => l_printer_name,
310 style => null,
311 copies => 1);
312 END IF;
313 END IF;
314
315
316 IF l_set_print_opt_ok THEN
317 iby_debug_pub.add(debug_msg => 'The printer is to: ' || l_printer_name,
318 debug_level => FND_LOG.LEVEL_STATEMENT,
319 module => l_Debug_Module);
320 ELSE
321 iby_debug_pub.add(debug_msg => 'Warning: unable to set printer',
322 debug_level => FND_LOG.LEVEL_STATEMENT,
323 module => l_Debug_Module);
324 END IF;
325
326 iby_debug_pub.add(debug_msg => 'Before Calling FND_REQUEST.SUBMIT_REQUEST().',
327 debug_level => FND_LOG.LEVEL_STATEMENT,
328 module => l_Debug_Module);
329
330 --Bug 6411356
331 --below code added to set the current nls character setting
332 --before submitting a child requests.
333 fnd_profile.get('ICX_NUMERIC_CHARACTERS',l_icx_numeric_characters);
334 l_bool_val:= FND_REQUEST.SET_OPTIONS( numeric_characters => l_icx_numeric_characters);
335
336
337 SELECT temp.application_short_name,
338 temp.template_code,
339 temp.default_language,
340 temp.default_territory,
341 decode(template_type_code,
342 'RTF','PDF',
343 'ETEXT','ETEXT',
344 'XSL-XML','XML',
345 'PDF','PDF')
346 into
347 l_appl_name,
348 l_template_code,
349 l_template_lang,
350 l_template_terr,
351 l_output_format
352 FROM iby_pay_instructions_all ins,
353 iby_payment_profiles pp,
354 iby_formats_b format,
355 XDO_TEMPLATES_B temp
356 WHERE ins.payment_instruction_id = p_payment_instruction_id
357 AND ins.payment_profile_id = pp.payment_profile_id
358 AND format.FORMAT_CODE = pp.PAYMENT_FORMAT_CODE
359 AND format.FORMAT_TEMPLATE_CODE = temp.template_code
360 AND temp.application_id = 673
361 AND SYSDATE BETWEEN NVL(temp.start_date, SYSDATE) AND NVL(temp.end_date,SYSDATE);
362
363
364
365 /* l_bool := FND_REQUEST.add_layout
366 (
367 l_appl_name,
368 l_template_code,
369 l_template_lang,
370 l_template_terr,
371 l_output_format
372
373 );
374 */
375
376 -- submit the extract program
377
378 IF l_output_format ='PDF' THEN
379
380 l_request_id := FND_REQUEST.SUBMIT_REQUEST
381 (
382 'IBY',
383 'IBY_FD_PAYMENT_FORMAT',
384 null, -- description
385 null, -- start_time
386 FALSE, -- sub_request
387 p_payment_instruction_id,
388 p_is_reprint_flag,
389 '', '', '', '', '', '', '',
390 '', '', '', '', '', '', '', '',
391 '', '', '', '', '', '', '', '',
392 '', '', '', '', '', '', '', '',
393 '', '', '', '', '', '', '', '',
394 '', '', '', '', '', '', '', '',
395 '', '', '', '', '', '', '', '',
396 '', '', '', '', '', '', '', '',
397 '', '', '', '', '', '', '', '',
398 '', '', '', '', '', '', '', '',
399 '', '', '', '', '', '', '', '',
400 '', '', '', '', '', '', '', '',
401 '', '', ''
402 );
403
404 iby_debug_pub.add(debug_msg => 'Submitting request for format payment program for PDF output',
405 debug_level => FND_LOG.LEVEL_STATEMENT,
406 module => l_Debug_Module);
407
408 ELSE
409
410 l_request_id := FND_REQUEST.SUBMIT_REQUEST
411 (
412 'IBY',
413 'IBY_FD_PAYMENT_FORMAT_TEXT',
414 null, -- description
415 null, -- start_time
416 FALSE, -- sub_request
417 p_payment_instruction_id,
418 p_is_reprint_flag,
419 '', '', '', '', '', '', '',
420 '', '', '', '', '', '', '', '',
421 '', '', '', '', '', '', '', '',
422 '', '', '', '', '', '', '', '',
423 '', '', '', '', '', '', '', '',
424 '', '', '', '', '', '', '', '',
425 '', '', '', '', '', '', '', '',
426 '', '', '', '', '', '', '', '',
427 '', '', '', '', '', '', '', '',
428 '', '', '', '', '', '', '', '',
429 '', '', '', '', '', '', '', '',
430 '', '', '', '', '', '', '', '',
431 '', '', ''
432 );
433
434 iby_debug_pub.add(debug_msg => 'Submitting request for format payment program for text/xml output',
435 debug_level => FND_LOG.LEVEL_STATEMENT,
436 module => l_Debug_Module);
437
438 END IF;
439
440 iby_debug_pub.add(debug_msg => 'After Calling FND_REQUEST.SUBMIT_REQUEST().',
441 debug_level => FND_LOG.LEVEL_STATEMENT,
442 module => l_Debug_Module);
443 iby_debug_pub.add(debug_msg => 'Request id: ' || l_request_id,
444 debug_level => FND_LOG.LEVEL_STATEMENT,
445 module => l_Debug_Module);
446
447 IF l_request_id = 0 THEN
448 RAISE FND_API.G_EXC_ERROR;
449 ELSE
450
451 iby_debug_pub.add(debug_msg => 'Calling the lock_pmt_entity() API to lock instruction: ' || p_payment_instruction_id
452 || ' for the extract/formatting/printing/delivery program',
453 debug_level => FND_LOG.LEVEL_STATEMENT,
454 module => l_Debug_Module);
455
456 IBY_DISBURSE_UI_API_PUB_PKG.lock_pmt_entity(
457 p_object_id => p_payment_instruction_id,
458 p_object_type => 'PAYMENT_INSTRUCTION',
459 p_conc_request_id => l_request_id,
460 x_return_status => l_return_status
461 );
462
463 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
464 RAISE FND_API.G_EXC_ERROR;
465 ELSE
466 iby_debug_pub.add(debug_msg => 'lock_pmt_entity() API returned success',
467 debug_level => FND_LOG.LEVEL_STATEMENT,
468 module => l_Debug_Module);
469 END IF;
470
471 END IF;
472
473 iby_debug_pub.add(debug_msg => 'Exit: ' || l_Debug_Module,
474 debug_level => FND_LOG.LEVEL_PROCEDURE,
475 module => l_Debug_Module);
476
477 END Run_Post_PI_Programs;
478
479
480 PROCEDURE Post_Results
481 (
482 p_payment_instruction_id IN NUMBER,
483 p_newStatus IN VARCHAR2,
484 p_is_reprint_flag IN VARCHAR2,
485 x_return_status OUT NOCOPY VARCHAR2
486 )
487 IS
488 l_instruction_ovn NUMBER;
489 l_instr_status VARCHAR2(30);
490 l_proc_type VARCHAR2(30);
491 l_mark_complete_event VARCHAR2(30);
492 l_format_code VARCHAR2(30);
493 l_process_type VARCHAR2(30);
494 l_mark_complete_status VARCHAR2(240);
495 l_pp_prt_immed VARCHAR2(1);
496 l_Debug_Module VARCHAR2(255) := G_DEBUG_MODULE || '.Post_Results';
497 l_msg_data VARCHAR2(2000);
498
499 CURSOR l_instruction_ovn_csr (p_payment_instruction_id IN NUMBER) IS
500 SELECT ins.object_version_number, ins.payment_instruction_status,
501 pp.processing_type, pp.mark_complete_event, pp.payment_format_code,
502 pp.print_instruction_immed_flag, ins.process_type
503 FROM iby_pay_instructions_all ins, iby_payment_profiles pp
504 WHERE ins.payment_profile_id = pp.payment_profile_id
505 AND payment_instruction_id = p_payment_instruction_id;
506
507 CURSOR l_pmt_csr (p_payment_instruction_id IN NUMBER) IS
508 SELECT payment_id, payment_status, object_version_number
509 FROM iby_payments_all
510 WHERE payment_instruction_id = p_payment_instruction_id
511 AND payment_status in ('FORMATTED', 'INSTRUCTION_CREATED');
512
513 CURSOR l_pmt_reprt_csr (p_payment_instruction_id IN NUMBER) IS
514 SELECT payment_id, payment_status, object_version_number
515 FROM iby_payments_all
516 WHERE payment_instruction_id = p_payment_instruction_id
517 AND payment_status in ('READY_TO_REPRINT', 'VOID_BY_SETUP_REPRINT', 'VOID_BY_OVERFLOW_REPRINT');
518
519 BEGIN
520
521 iby_debug_pub.add(debug_msg => 'Enter: ' || l_Debug_Module,
522 debug_level => FND_LOG.LEVEL_PROCEDURE,
523 module => l_Debug_Module);
524
525 iby_debug_pub.add(debug_msg => 'Input payment instruction ID: ' || p_payment_instruction_id,
526 debug_level => FND_LOG.LEVEL_STATEMENT,
527 module => l_Debug_Module);
528
529 iby_debug_pub.add(debug_msg => 'Input instruction status: ' || p_newStatus,
530 debug_level => FND_LOG.LEVEL_STATEMENT,
531 module => l_Debug_Module);
532
533 iby_debug_pub.add(debug_msg => 'Input is_reprint_flag: ' || p_is_reprint_flag,
534 debug_level => FND_LOG.LEVEL_STATEMENT,
535 module => l_Debug_Module);
536
537 x_return_status := FND_API.G_RET_STS_SUCCESS;
538
539 OPEN l_instruction_ovn_csr (p_payment_instruction_id);
540 FETCH l_instruction_ovn_csr INTO l_instruction_ovn, l_instr_status, l_proc_type,
541 l_mark_complete_event, l_format_code, l_pp_prt_immed, l_process_type;
542 CLOSE l_instruction_ovn_csr;
543
544 iby_debug_pub.add(debug_msg => 'Current instruction status is: ' || l_instr_status,
545 debug_level => FND_LOG.LEVEL_STATEMENT,
546 module => l_Debug_Module);
547
548 iby_debug_pub.add(debug_msg => 'Setting instruction to the input status: ' || p_newStatus,
549 debug_level => FND_LOG.LEVEL_STATEMENT,
550 module => l_Debug_Module);
551
552 -- bug 5121763: single payments handling. PICP would mark complete and set
553 -- the complete statuses in its process - i.e., before the post-PICP CP
554 -- as a result we skip the post formatting processing for single payments
555 -- except 'TRANSMISSION_FAILED'
556 IF l_process_type = 'IMMEDIATE' AND p_newStatus <> 'TRANSMISSION_FAILED' THEN
557 RETURN;
558 END IF;
559
560 UPDATE
561 iby_pay_instructions_all
562 SET
563 payment_instruction_status = p_newStatus,
564 object_version_number = l_instruction_ovn + 1,
565 last_updated_by = fnd_global.user_id,
566 last_update_date = SYSDATE,
567 last_update_login = nvl(fnd_global.LOGIN_ID, fnd_global.conc_login_id)
568 WHERE
569 payment_instruction_id = p_payment_instruction_id;
570
571 IF p_newStatus = 'SUBMITTED_FOR_PRINTING' THEN
572
573 IF nvl(p_is_reprint_flag, 'N') = 'N' THEN
574
575 FOR l_payment IN l_pmt_csr(p_payment_instruction_id) LOOP
576
577 IF l_payment.payment_status IN ('FORMATTED', 'INSTRUCTION_CREATED') THEN
578
579 iby_debug_pub.add(debug_msg => 'Payment ' || l_payment.payment_id || ' is in ' || l_payment.payment_status ||
580 ' status. Setting it to SUBMITTED_FOR_PRINTING',
581 debug_level => FND_LOG.LEVEL_STATEMENT,
582 module => l_Debug_Module);
583
584 UPDATE
585 iby_payments_all
586 SET
587 payment_status = 'SUBMITTED_FOR_PRINTING',
588 object_version_number = l_payment.object_version_number + 1,
589 last_updated_by = fnd_global.user_id,
590 last_update_date = SYSDATE,
591 last_update_login = nvl(fnd_global.LOGIN_ID, fnd_global.conc_login_id)
592 WHERE
593 payment_id = l_payment.payment_id;
594 END IF;
595
596 END LOOP;
597
598 ELSE -- reprint (individual or range)
599 FOR l_payment IN l_pmt_reprt_csr(p_payment_instruction_id) LOOP
600
601 IF l_payment.payment_status = 'READY_TO_REPRINT' THEN
602
603 iby_debug_pub.add(debug_msg => 'Payment ' || l_payment.payment_id || ' is in ' || l_payment.payment_status ||
604 ' status. Setting it to SUBMITTED_FOR_PRINTING',
605 debug_level => FND_LOG.LEVEL_STATEMENT,
606 module => l_Debug_Module);
607
608 UPDATE
609 iby_payments_all
610 SET
611 payment_status = 'SUBMITTED_FOR_PRINTING',
612 object_version_number = l_payment.object_version_number + 1,
613 last_updated_by = fnd_global.user_id,
614 last_update_date = SYSDATE,
615 last_update_login = nvl(fnd_global.LOGIN_ID, fnd_global.conc_login_id)
616 WHERE
617 payment_id = l_payment.payment_id;
618
619 ELSIF l_payment.payment_status = 'VOID_BY_SETUP_REPRINT' THEN
620
621 iby_debug_pub.add(debug_msg => 'Payment ' || l_payment.payment_id || ' is in ' || l_payment.payment_status ||
622 ' status. Setting it to VOID_BY_SETUP',
623 debug_level => FND_LOG.LEVEL_STATEMENT,
624 module => l_Debug_Module);
625
626 UPDATE
627 iby_payments_all
628 SET
629 payment_status = 'VOID_BY_SETUP',
630 object_version_number = l_payment.object_version_number + 1,
631 last_updated_by = fnd_global.user_id,
632 last_update_date = SYSDATE,
633 last_update_login = nvl(fnd_global.LOGIN_ID, fnd_global.conc_login_id)
634 WHERE
635 payment_id = l_payment.payment_id;
636
637 ELSIF l_payment.payment_status = 'VOID_BY_OVERFLOW_REPRINT' THEN
638
639 iby_debug_pub.add(debug_msg => 'Payment ' || l_payment.payment_id || ' is in ' || l_payment.payment_status ||
640 ' status. Setting it to VOID_BY_OVERFLOW',
641 debug_level => FND_LOG.LEVEL_STATEMENT,
642 module => l_Debug_Module);
643
644 UPDATE
645 iby_payments_all
646 SET
647 payment_status = 'VOID_BY_OVERFLOW',
648 object_version_number = l_payment.object_version_number + 1,
649 last_updated_by = fnd_global.user_id,
650 last_update_date = SYSDATE,
651 last_update_login = nvl(fnd_global.LOGIN_ID, fnd_global.conc_login_id)
652 WHERE
653 payment_id = l_payment.payment_id;
654
655 END IF;
656
657 END LOOP;
658
659 END IF;
660
661 -- for printing outside Oracle
662 ELSIF p_newStatus = 'FORMATTED' AND l_proc_type = 'PRINTED' THEN
663
664 FOR l_payment IN l_pmt_csr(p_payment_instruction_id) LOOP
665
666 IF l_payment.payment_status = 'INSTRUCTION_CREATED' THEN
667
668 iby_debug_pub.add(debug_msg => 'Payment ' || l_payment.payment_id || ' is in ' || l_payment.payment_status ||
669 ' status. Setting it to FORMATTED',
670 debug_level => FND_LOG.LEVEL_STATEMENT,
671 module => l_Debug_Module);
672
673 UPDATE
674 iby_payments_all
675 SET
676 payment_status = 'FORMATTED',
677 object_version_number = l_payment.object_version_number + 1,
678 last_updated_by = fnd_global.user_id,
679 last_update_date = SYSDATE,
680 last_update_login = nvl(fnd_global.LOGIN_ID, fnd_global.conc_login_id)
681 WHERE
682 payment_id = l_payment.payment_id;
683 END IF;
684
685 END LOOP;
686
687 -- for transmitted outside Oracle
688 -- (electronic and no tranmit config)
689 ELSIF p_newStatus = 'FORMATTED_ELECTRONIC' AND l_proc_type = 'ELECTRONIC' THEN
690
691 FOR l_payment IN l_pmt_csr(p_payment_instruction_id) LOOP
692
693 IF l_payment.payment_status = 'INSTRUCTION_CREATED' THEN
694
695 iby_debug_pub.add(debug_msg => 'Payment ' || l_payment.payment_id || ' is in ' || l_payment.payment_status ||
696 ' status. Setting it to FORMATTED',
697 debug_level => FND_LOG.LEVEL_STATEMENT,
698 module => l_Debug_Module);
699
700 UPDATE
701 iby_payments_all
702 SET
703 payment_status = 'FORMATTED',
704 object_version_number = l_payment.object_version_number + 1,
705 last_updated_by = fnd_global.user_id,
706 last_update_date = SYSDATE,
707 last_update_login = nvl(fnd_global.LOGIN_ID, fnd_global.conc_login_id)
708 WHERE
709 payment_id = l_payment.payment_id;
710 END IF;
711
712 END LOOP;
713
714 IF l_mark_complete_event = 'FORMATTED' THEN
715
716 iby_debug_pub.add(debug_msg => 'Before Calling IBY_DISBURSE_UI_API_PUB_PKG.mark_all_pmts_complete().',
717 debug_level => FND_LOG.LEVEL_STATEMENT,
718 module => l_Debug_Module);
719
720 -- the payment completion point must be set as FORMATTED in the PPP
721 -- call the mark complete API
722 IBY_DISBURSE_UI_API_PUB_PKG.mark_all_pmts_complete (
723 p_instr_id => p_payment_instruction_id,
724 x_return_status => l_mark_complete_status
725 );
726
727 iby_debug_pub.add(debug_msg => 'After Calling IBY_DISBURSE_UI_API_PUB_PKG.mark_all_pmts_complete().',
728 debug_level => FND_LOG.LEVEL_STATEMENT,
729 module => l_Debug_Module);
730
731 IF l_mark_complete_status <> FND_API.G_RET_STS_SUCCESS THEN
732 -- set error for invalid data
733 fnd_message.set_name('IBY', 'IBY_FD_ERR_MARK_COMPLETE');
734 fnd_message.set_token('PARAM', p_payment_instruction_id);
735 fnd_msg_pub.add;
736
737 RAISE FND_API.G_EXC_ERROR;
738 END IF;
739
740 END IF;
741
742 -- for deferred printing with Oracle
743 ELSIF p_newStatus = 'FORMATTED_READY_FOR_PRINTING' THEN
744
745 FOR l_payment IN l_pmt_csr(p_payment_instruction_id) LOOP
746
747 IF l_payment.payment_status = 'INSTRUCTION_CREATED' THEN
748
749 iby_debug_pub.add(debug_msg => 'Payment ' || l_payment.payment_id || ' is in ' || l_payment.payment_status ||
750 ' status. Setting it to FORMATTED',
751 debug_level => FND_LOG.LEVEL_STATEMENT,
752 module => l_Debug_Module);
753
754 UPDATE
755 iby_payments_all
756 SET
757 payment_status = 'FORMATTED',
758 object_version_number = l_payment.object_version_number + 1,
759 last_updated_by = fnd_global.user_id,
760 last_update_date = SYSDATE,
761 last_update_login = nvl(fnd_global.LOGIN_ID, fnd_global.conc_login_id)
762 WHERE
763 payment_id = l_payment.payment_id;
764 END IF;
765
766 END LOOP;
767
768 -- if the printing is deferred due to payment document locking
769 -- (i.e., ppp is set to print immed, but instruction is not)
770 -- in case of multiple payment instructions, we need to set
771 -- the doc level straight through processing flag to N
772 IF l_pp_prt_immed = 'Y' THEN
773 Turn_off_STP_Flag(p_payment_instruction_id, p_newStatus);
774 END IF;
775
776 -- for deferred transmission with Oracle
777 ELSIF p_newStatus = 'FORMATTED_READY_TO_TRANSMIT' THEN
778
779 FOR l_payment IN l_pmt_csr(p_payment_instruction_id) LOOP
780
781 IF l_payment.payment_status = 'INSTRUCTION_CREATED' THEN
782
783 iby_debug_pub.add(debug_msg => 'Payment ' || l_payment.payment_id || ' is in ' || l_payment.payment_status ||
784 ' status. Setting it to FORMATTED',
785 debug_level => FND_LOG.LEVEL_STATEMENT,
786 module => l_Debug_Module);
787
788 UPDATE
789 iby_payments_all
790 SET
791 payment_status = 'FORMATTED',
792 object_version_number = l_payment.object_version_number + 1,
793 last_updated_by = fnd_global.user_id,
794 last_update_date = SYSDATE,
795 last_update_login = nvl(fnd_global.LOGIN_ID, fnd_global.conc_login_id)
796 WHERE
797 payment_id = l_payment.payment_id;
798 END IF;
799
800 END LOOP;
801
802 IF l_mark_complete_event = 'FORMATTED' THEN
803
804 iby_debug_pub.add(debug_msg => 'Before Calling IBY_DISBURSE_UI_API_PUB_PKG.mark_all_pmts_complete().',
805 debug_level => FND_LOG.LEVEL_STATEMENT,
806 module => l_Debug_Module);
807
808 -- call the mark complete API
809 IBY_DISBURSE_UI_API_PUB_PKG.mark_all_pmts_complete (
810 p_instr_id => p_payment_instruction_id,
811 x_return_status => l_mark_complete_status
812 );
813
814 iby_debug_pub.add(debug_msg => 'After Calling IBY_DISBURSE_UI_API_PUB_PKG.mark_all_pmts_complete().',
815 debug_level => FND_LOG.LEVEL_STATEMENT,
816 module => l_Debug_Module);
817
818 IF l_mark_complete_status <> FND_API.G_RET_STS_SUCCESS THEN
819 -- set error for invalid data
820 fnd_message.set_name('IBY', 'IBY_FD_ERR_MARK_COMPLETE');
821 fnd_message.set_token('PARAM', p_payment_instruction_id);
822 fnd_msg_pub.add;
823
824 RAISE FND_API.G_EXC_ERROR;
825 END IF;
826
827 END IF;
828
829 -- transmission success
830 ELSIF p_newStatus = 'TRANSMITTED' THEN
831
832 FOR l_payment IN l_pmt_csr(p_payment_instruction_id) LOOP
833
834 IF l_payment.payment_status IN ('FORMATTED', 'INSTRUCTION_CREATED') THEN
835
836 iby_debug_pub.add(debug_msg => 'Payment ' || l_payment.payment_id || ' is in ' || l_payment.payment_status ||
837 ' status. Setting it to TRANSMITTED',
838 debug_level => FND_LOG.LEVEL_STATEMENT,
839 module => l_Debug_Module);
840
841 UPDATE
842 iby_payments_all
843 SET
844 payment_status = 'TRANSMITTED',
845 object_version_number = l_payment.object_version_number + 1,
846 last_updated_by = fnd_global.user_id,
847 last_update_date = SYSDATE,
848 last_update_login = nvl(fnd_global.LOGIN_ID, fnd_global.conc_login_id)
849 WHERE
850 payment_id = l_payment.payment_id;
851 END IF;
852
853 END LOOP;
854
855 /* Bug 6026231: When payment instruction is set to 'TRANSMITTED', the api
856 mark_all_pmts_complete should be called for both mark completion
857 event status (i.e. 'FORMATTED', 'TRANSMITTED')
858 */
859 IF l_mark_complete_event in ('FORMATTED', 'TRANSMITTED') THEN
860
861 iby_debug_pub.add(debug_msg => 'Before Calling IBY_DISBURSE_UI_API_PUB_PKG.mark_all_pmts_complete().',
862 debug_level => FND_LOG.LEVEL_STATEMENT,
863 module => l_Debug_Module);
864
865 -- call the mark complete API
866 IBY_DISBURSE_UI_API_PUB_PKG.mark_all_pmts_complete (
867 p_instr_id => p_payment_instruction_id,
868 x_return_status => l_mark_complete_status
869 );
870
871 iby_debug_pub.add(debug_msg => 'After Calling IBY_DISBURSE_UI_API_PUB_PKG.mark_all_pmts_complete().',
872 debug_level => FND_LOG.LEVEL_STATEMENT,
873 module => l_Debug_Module);
874
875 IF l_mark_complete_status <> FND_API.G_RET_STS_SUCCESS THEN
876 -- set error for invalid data
877 fnd_message.set_name('IBY', 'IBY_FD_ERR_MARK_COMPLETE');
878 fnd_message.set_token('PARAM', p_payment_instruction_id);
879 fnd_msg_pub.add;
880
881 RAISE FND_API.G_EXC_ERROR;
882 END IF;
883
884 END IF;
885
886 -- transmission failed
887 -- no status change at the payment level
888 -- only update doc level straight through flag
889 ELSIF p_newStatus = 'TRANSMISSION_FAILED' THEN
890
891 -- bug 5630411 for transmission right after formatting
892 -- if transmission fails we need to set the payment status to formatted
893 -- and kick off payment complete if applicable
894 FOR l_payment IN l_pmt_csr(p_payment_instruction_id) LOOP
895
896 IF l_payment.payment_status IN ('INSTRUCTION_CREATED') THEN
897
898 iby_debug_pub.add(debug_msg => 'Payment ' || l_payment.payment_id || ' is in ' || l_payment.payment_status ||
899 ' status. Setting it to FORMATTED',
900 debug_level => FND_LOG.LEVEL_STATEMENT,
901 module => l_Debug_Module);
902
903 UPDATE
904 iby_payments_all
905 SET
906 payment_status = 'FORMATTED',
907 object_version_number = l_payment.object_version_number + 1,
908 last_updated_by = fnd_global.user_id,
909 last_update_date = SYSDATE,
910 last_update_login = nvl(fnd_global.LOGIN_ID, fnd_global.conc_login_id)
911 WHERE
912 payment_id = l_payment.payment_id;
913 END IF;
914
915 END LOOP;
916
917 IF l_mark_complete_event = 'FORMATTED' THEN
918
919 iby_debug_pub.add(debug_msg => 'Before Calling IBY_DISBURSE_UI_API_PUB_PKG.mark_all_pmts_complete().',
920 debug_level => FND_LOG.LEVEL_STATEMENT,
921 module => l_Debug_Module);
922
923 -- call the mark complete API
924 IBY_DISBURSE_UI_API_PUB_PKG.mark_all_pmts_complete (
925 p_instr_id => p_payment_instruction_id,
926 x_return_status => l_mark_complete_status
927 );
928
929 iby_debug_pub.add(debug_msg => 'After Calling IBY_DISBURSE_UI_API_PUB_PKG.mark_all_pmts_complete().',
930 debug_level => FND_LOG.LEVEL_STATEMENT,
931 module => l_Debug_Module);
932
933 IF l_mark_complete_status <> FND_API.G_RET_STS_SUCCESS THEN
934 -- set error for invalid data
935 fnd_message.set_name('IBY', 'IBY_FD_ERR_MARK_COMPLETE');
936 fnd_message.set_token('PARAM', p_payment_instruction_id);
937 fnd_msg_pub.add;
938
939 RAISE FND_API.G_EXC_ERROR;
940 END IF;
941
942 END IF;
943
944 Turn_off_STP_Flag(p_payment_instruction_id, p_newStatus);
945
946 END IF;
947
948 -- finally kick off Federal "Payment Instruction Treasury Symbol Listing Report"
949 -- if its a Federal format
950 Submit_FV_TS_Report(p_payment_instruction_id, l_format_code, l_Debug_Module);
951
952 iby_debug_pub.add(debug_msg => 'Exit: ' || l_Debug_Module,
953 debug_level => FND_LOG.LEVEL_PROCEDURE,
954 module => l_Debug_Module);
955
956 EXCEPTION
957
958 WHEN FND_API.G_EXC_ERROR THEN
959 x_return_status := FND_API.G_RET_STS_ERROR;
960
961 LOOP
962 l_msg_data := FND_MSG_PUB.Get;
963
964 IF l_msg_data IS NULL THEN
965 EXIT;
966 ELSE
967 iby_debug_pub.add(debug_msg => l_msg_data,
968 debug_level => FND_LOG.LEVEL_STATEMENT,
969 module => l_Debug_Module);
970 END IF;
971 END LOOP;
972
973 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
974 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
975
976 LOOP
977 l_msg_data := FND_MSG_PUB.Get;
978
979 IF l_msg_data IS NULL THEN
980 EXIT;
981 ELSE
982 iby_debug_pub.add(debug_msg => l_msg_data,
983 debug_level => FND_LOG.LEVEL_STATEMENT,
984 module => l_Debug_Module);
985 END IF;
986 END LOOP;
987
988 WHEN OTHERS THEN
989 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
990
991 LOOP
992 l_msg_data := FND_MSG_PUB.Get;
993
994 IF l_msg_data IS NULL THEN
995 EXIT;
996 ELSE
997 iby_debug_pub.add(debug_msg => l_msg_data,
998 debug_level => FND_LOG.LEVEL_STATEMENT,
999 module => l_Debug_Module);
1000 END IF;
1001 END LOOP;
1002
1003 END Post_Results;
1004
1005
1006
1007 PROCEDURE Submit_FV_TS_Report
1008 (
1009 p_payment_instruction_id IN NUMBER,
1010 p_format_code IN VARCHAR2,
1011 l_Debug_Module IN VARCHAR2
1012 )
1013 IS
1014 l_fv_ts_req_id NUMBER;
1015 l_fv_ts_req_status VARCHAR2(1);
1016 l_fv_ts_req_msg_cnt NUMBER;
1017 l_fv_ts_req_msg_dt VARCHAR2(2000);
1018
1019 BEGIN
1020
1021 IF SUBSTR(p_format_code, 1, 16) = 'IBY_PAY_EFT_FED_' THEN
1022
1023 FV_FEDERAL_PAYMENT_FIELDS_PKG.submit_pay_instr_ts_report
1024 (p_init_msg_list => NULL,
1025 p_payment_instruction_id => p_payment_instruction_id,
1026 x_request_id => l_fv_ts_req_id,
1027 x_return_status => l_fv_ts_req_status,
1028 x_msg_count => l_fv_ts_req_msg_cnt,
1029 x_msg_data => l_fv_ts_req_msg_dt
1030 );
1031
1032 IF l_fv_ts_req_status = FND_API.G_RET_STS_SUCCESS THEN
1033 iby_debug_pub.add(debug_msg => 'Federal submit_pay_instr_ts_report() returns success.',
1034 debug_level => FND_LOG.LEVEL_STATEMENT,
1035 module => l_Debug_Module);
1036 iby_debug_pub.add(debug_msg => 'Payment Instruction Treasury Symbol Listing Report request id: ' || l_fv_ts_req_id,
1037 debug_level => FND_LOG.LEVEL_STATEMENT,
1038 module => l_Debug_Module);
1039 ELSE
1040 iby_debug_pub.add(debug_msg => 'Federal submit_pay_instr_ts_report() returns error.',
1041 debug_level => FND_LOG.LEVEL_STATEMENT,
1042 module => l_Debug_Module);
1043 END IF;
1044
1045 END IF;
1046
1047 END Submit_FV_TS_Report;
1048
1049
1050 PROCEDURE Insert_Transmission_Error
1051 (
1052 p_payment_instruction_id IN NUMBER,
1053 p_error_code IN VARCHAR2,
1054 p_error_msg IN VARCHAR2
1055 )
1056 IS
1057 l_transaction_error_id NUMBER;
1058 l_Debug_Module VARCHAR2(255) := G_DEBUG_MODULE || '.Insert_Transmission_Error';
1059
1060 BEGIN
1061
1062 iby_debug_pub.add(debug_msg => 'Enter: ' || l_Debug_Module,
1063 debug_level => FND_LOG.LEVEL_PROCEDURE,
1064 module => l_Debug_Module);
1065
1066 iby_debug_pub.add(debug_msg => 'Input payment instruction ID: ' || p_payment_instruction_id,
1067 debug_level => FND_LOG.LEVEL_STATEMENT,
1068 module => l_Debug_Module);
1069
1070 iby_debug_pub.add(debug_msg => 'Error code: ' || p_error_code,
1071 debug_level => FND_LOG.LEVEL_STATEMENT,
1072 module => l_Debug_Module);
1073
1074 iby_debug_pub.add(debug_msg => 'Error message: ' || p_error_msg,
1075 debug_level => FND_LOG.LEVEL_STATEMENT,
1076 module => l_Debug_Module);
1077
1078 select IBY_TRANSACTION_ERRORS_S.nextval into l_transaction_error_id from dual;
1079
1080 insert into
1081 iby_transaction_errors
1082 (transaction_error_id, transaction_type, transaction_id,
1083 error_code, error_date, error_status, created_by,
1084 creation_date, last_updated_by, last_update_date,
1085 object_version_number, override_allowed_on_error_flag,
1086 do_not_apply_error_flag, error_type, error_message)
1087 values
1088 (l_transaction_error_id, 'PAYMENT_INSTRUCTION', p_payment_instruction_id,
1089 p_error_code, sysdate, 'ACTIVE', fnd_global.user_id,
1090 sysdate, fnd_global.user_id, sysdate,
1091 1, 'Y',
1092 'N', 'BANK', p_error_msg);
1093
1094 iby_debug_pub.add(debug_msg => 'Exit: ' || l_Debug_Module,
1095 debug_level => FND_LOG.LEVEL_PROCEDURE,
1096 module => l_Debug_Module);
1097
1098 END Insert_Transmission_Error;
1099
1100
1101 PROCEDURE Turn_off_STP_Flag
1102 (
1103 p_payment_instruction_id IN NUMBER,
1104 p_newStatus IN VARCHAR2
1105 )
1106 IS
1107
1108 CURSOR l_doc_csr (p_payment_instruction_id IN NUMBER) IS
1109 SELECT document_payable_id
1110 FROM iby_docs_payable_all doc, iby_payments_all pmt,
1111 iby_pay_instructions_all ins
1112 WHERE ins.payment_instruction_id = p_payment_instruction_id
1113 AND ins.payment_instruction_id = pmt.payment_instruction_id
1114 AND doc.straight_through_flag = 'Y'
1115 AND pmt.payment_id = doc.payment_id;
1116
1117 l_Debug_Module VARCHAR2(255) := G_DEBUG_MODULE || '.Turn_off_STP_Flag';
1118 BEGIN
1119
1120 iby_debug_pub.add(debug_msg => 'Enter: ' || l_Debug_Module,
1121 debug_level => FND_LOG.LEVEL_PROCEDURE,
1122 module => l_Debug_Module);
1123
1124 iby_debug_pub.add(debug_msg => 'Input payment instruction ID: ' || p_payment_instruction_id,
1125 debug_level => FND_LOG.LEVEL_STATEMENT,
1126 module => l_Debug_Module);
1127
1128 FOR l_doc IN l_doc_csr(p_payment_instruction_id) LOOP
1129
1130 UPDATE
1131 iby_docs_payable_all
1132 SET
1133 straight_through_flag = 'N',
1134 object_version_number = object_version_number + 1,
1135 last_updated_by = fnd_global.user_id,
1136 last_update_date = SYSDATE,
1137 last_update_login = nvl(fnd_global.LOGIN_ID, fnd_global.conc_login_id)
1138 WHERE
1139 document_payable_id = l_doc.document_payable_id;
1140
1141 END LOOP;
1142
1143 iby_debug_pub.add(debug_msg => 'Exit: ' || l_Debug_Module,
1144 debug_level => FND_LOG.LEVEL_PROCEDURE,
1145 module => l_Debug_Module);
1146
1147 END Turn_off_STP_Flag;
1148
1149
1150 PROCEDURE save_last_periodic_seq_nums
1151 (
1152 p_payment_instruction_id IN NUMBER,
1153 p_seq_name1 IN VARCHAR2,
1154 p_last_val1 IN VARCHAR2,
1155 p_seq_name2 IN VARCHAR2,
1156 p_last_val2 IN VARCHAR2,
1157 p_seq_name3 IN VARCHAR2,
1158 p_last_val3 IN VARCHAR2
1159 )
1160 IS
1161 l_payment_profile_id NUMBER;
1162 l_seq_last1 NUMBER;
1163 l_seq_last2 NUMBER;
1164 l_seq_last3 NUMBER;
1165 l_seq_name1 VARCHAR2(80);
1166 l_seq_name2 VARCHAR2(80);
1167 l_seq_name3 VARCHAR2(80);
1168 l_obj_updated BOOLEAN := FALSE;
1169 l_Debug_Module VARCHAR2(255) := G_DEBUG_MODULE || '.save_last_periodic_seq_nums';
1170
1171 CURSOR l_seq_csr (p_payment_instruction_id IN NUMBER) IS
1172 SELECT pp.payment_profile_id,
1173 pp.periodic_sequence_name_1,
1174 pp.periodic_sequence_name_2,
1175 pp.periodic_sequence_name_3,
1176 pp.last_used_number_1,
1177 pp.last_used_number_2,
1178 pp.last_used_number_3
1179 FROM iby_payment_profiles pp, iby_pay_instructions_all ins
1180 WHERE ins.payment_instruction_id = p_payment_instruction_id
1181 AND ins.payment_profile_id = pp.payment_profile_id;
1182
1183 BEGIN
1184
1185 iby_debug_pub.add(debug_msg => 'Enter: ' || l_Debug_Module,
1186 debug_level => FND_LOG.LEVEL_PROCEDURE,
1187 module => l_Debug_Module);
1188
1189 iby_debug_pub.add(debug_msg => 'Input payment instruction ID: ' || p_payment_instruction_id,
1190 debug_level => FND_LOG.LEVEL_STATEMENT,
1191 module => l_Debug_Module);
1192
1193 iby_debug_pub.add(debug_msg => 'p_seq_name1: ' || p_seq_name1,
1194 debug_level => FND_LOG.LEVEL_STATEMENT,
1195 module => l_Debug_Module);
1196
1197 iby_debug_pub.add(debug_msg => 'p_last_val1: ' || p_last_val1,
1198 debug_level => FND_LOG.LEVEL_STATEMENT,
1199 module => l_Debug_Module);
1200
1201 iby_debug_pub.add(debug_msg => 'p_seq_name2: ' || p_seq_name1,
1202 debug_level => FND_LOG.LEVEL_STATEMENT,
1203 module => l_Debug_Module);
1204
1205 iby_debug_pub.add(debug_msg => 'p_last_val2: ' || p_last_val1,
1206 debug_level => FND_LOG.LEVEL_STATEMENT,
1207 module => l_Debug_Module);
1208
1209 iby_debug_pub.add(debug_msg => 'p_seq_name3: ' || p_seq_name1,
1210 debug_level => FND_LOG.LEVEL_STATEMENT,
1211 module => l_Debug_Module);
1212
1213 iby_debug_pub.add(debug_msg => 'p_last_val3: ' || p_last_val1,
1214 debug_level => FND_LOG.LEVEL_STATEMENT,
1215 module => l_Debug_Module);
1216
1217 OPEN l_seq_csr (p_payment_instruction_id);
1218 FETCH l_seq_csr INTO l_payment_profile_id, l_seq_name1, l_seq_name2, l_seq_name3,
1219 l_seq_last1, l_seq_last2, l_seq_last3;
1220 CLOSE l_seq_csr;
1221
1222 -- input seq1
1223 IF l_seq_name1 IS NOT NULL AND l_seq_name1 = trim(p_seq_name1)
1224 AND p_last_val1 IS NOT NULL AND nvl(l_seq_last1, -99) <> p_last_val1 THEN
1225
1226 UPDATE iby_acct_pmt_profiles_b
1227 SET
1228 last_used_number_1 = p_last_val1
1229 WHERE
1230 payment_profile_id = l_payment_profile_id;
1231
1232 l_obj_updated := TRUE;
1233
1234 ELSIF l_seq_name2 IS NOT NULL AND l_seq_name2 = trim(p_seq_name1)
1235 AND p_last_val1 IS NOT NULL AND nvl(l_seq_last2, -99) <> p_last_val1 THEN
1236
1237 UPDATE iby_acct_pmt_profiles_b
1238 SET
1239 last_used_number_2 = p_last_val1
1240 WHERE
1241 payment_profile_id = l_payment_profile_id;
1242
1243 l_obj_updated := TRUE;
1244
1245 ELSIF l_seq_name3 IS NOT NULL AND l_seq_name3 = trim(p_seq_name1)
1246 AND p_last_val1 IS NOT NULL AND nvl(l_seq_last3, -99) <> p_last_val1 THEN
1247
1248 UPDATE iby_acct_pmt_profiles_b
1249 SET
1250 last_used_number_3 = p_last_val1
1251 WHERE
1252 payment_profile_id = l_payment_profile_id;
1253
1254 l_obj_updated := TRUE;
1255
1256 END IF;
1257
1258 -- input seq2
1259 IF l_seq_name1 IS NOT NULL AND l_seq_name1 = trim(p_seq_name2)
1260 AND p_last_val2 IS NOT NULL AND nvl(l_seq_last1, -99) <> p_last_val2 THEN
1261
1262 UPDATE iby_acct_pmt_profiles_b
1263 SET
1264 last_used_number_1 = p_last_val2
1265 WHERE
1266 payment_profile_id = l_payment_profile_id;
1267
1268 l_obj_updated := TRUE;
1269
1270 ELSIF l_seq_name2 IS NOT NULL AND l_seq_name2 = trim(p_seq_name2)
1271 AND p_last_val2 IS NOT NULL AND nvl(l_seq_last2, -99) <> p_last_val2 THEN
1272
1273 UPDATE iby_acct_pmt_profiles_b
1274 SET
1275 last_used_number_2 = p_last_val2
1276 WHERE
1277 payment_profile_id = l_payment_profile_id;
1278
1279 l_obj_updated := TRUE;
1280
1281 ELSIF l_seq_name3 IS NOT NULL AND l_seq_name3 = trim(p_seq_name2)
1282 AND p_last_val2 IS NOT NULL AND nvl(l_seq_last3, -99) <> p_last_val2 THEN
1283
1284 UPDATE iby_acct_pmt_profiles_b
1285 SET
1286 last_used_number_3 = p_last_val2
1287 WHERE
1288 payment_profile_id = l_payment_profile_id;
1289
1290 l_obj_updated := TRUE;
1291
1292 END IF;
1293
1294 -- input seq3
1295 IF l_seq_name1 IS NOT NULL AND l_seq_name1 = trim(p_seq_name3)
1296 AND p_last_val3 IS NOT NULL AND nvl(l_seq_last1, -99) <> p_last_val3 THEN
1297
1298 UPDATE iby_acct_pmt_profiles_b
1299 SET
1300 last_used_number_1 = p_last_val3
1301 WHERE
1302 payment_profile_id = l_payment_profile_id;
1303
1304 l_obj_updated := TRUE;
1305
1306 ELSIF l_seq_name2 IS NOT NULL AND l_seq_name2 = trim(p_seq_name3)
1307 AND p_last_val3 IS NOT NULL AND nvl(l_seq_last2, -99) <> p_last_val3 THEN
1308
1309 UPDATE iby_acct_pmt_profiles_b
1310 SET
1311 last_used_number_2 = p_last_val3
1312 WHERE
1313 payment_profile_id = l_payment_profile_id;
1314
1315 l_obj_updated := TRUE;
1316
1317 ELSIF l_seq_name3 IS NOT NULL AND l_seq_name3 = trim(p_seq_name3)
1318 AND p_last_val3 IS NOT NULL AND nvl(l_seq_last3, -99) <> p_last_val3 THEN
1319
1320 UPDATE iby_acct_pmt_profiles_b
1321 SET
1322 last_used_number_3 = p_last_val3
1323 WHERE
1324 payment_profile_id = l_payment_profile_id;
1325
1326 l_obj_updated := TRUE;
1327
1328 END IF;
1329
1330 IF l_obj_updated THEN
1331
1332 UPDATE iby_acct_pmt_profiles_b
1333 SET
1334 object_version_number = object_version_number + 1,
1335 last_updated_by = fnd_global.user_id,
1336 last_update_date = SYSDATE,
1337 last_update_login = fnd_global.LOGIN_ID
1338 WHERE
1339 payment_profile_id = l_payment_profile_id;
1340
1341 END IF;
1342
1343 iby_debug_pub.add(debug_msg => 'Exit: ' || l_Debug_Module,
1344 debug_level => FND_LOG.LEVEL_PROCEDURE,
1345 module => l_Debug_Module);
1346
1347 END save_last_periodic_seq_nums;
1348
1349
1350
1351 PROCEDURE set_sra_created
1352 (
1353 p_payment_instruction_id IN NUMBER
1354 )
1355 IS
1356 l_instruction_ovn NUMBER;
1357
1358 CURSOR l_instruction_ovn_csr (p_payment_instruction_id IN NUMBER) IS
1359 SELECT object_version_number
1360 FROM iby_pay_instructions_all
1361 WHERE payment_instruction_id = p_payment_instruction_id;
1362
1363 BEGIN
1364
1365 OPEN l_instruction_ovn_csr (p_payment_instruction_id);
1366 FETCH l_instruction_ovn_csr INTO l_instruction_ovn;
1367 CLOSE l_instruction_ovn_csr;
1368
1369 UPDATE
1370 iby_pay_instructions_all
1371 SET
1372 remittance_advice_created_flag = 'Y',
1373 object_version_number = l_instruction_ovn + 1,
1374 last_updated_by = fnd_global.user_id,
1375 last_update_date = SYSDATE,
1376 last_update_login = nvl(fnd_global.LOGIN_ID, fnd_global.conc_login_id)
1377 WHERE
1378 payment_instruction_id = p_payment_instruction_id;
1379
1380 END set_sra_created;
1381
1382
1383 PROCEDURE set_pos_pay_created
1384 (
1385 p_payment_instruction_id IN NUMBER
1386 )
1387 IS
1388 l_instruction_ovn NUMBER;
1389
1390 CURSOR l_instruction_ovn_csr (p_payment_instruction_id IN NUMBER) IS
1391 SELECT object_version_number
1392 FROM iby_pay_instructions_all
1393 WHERE payment_instruction_id = p_payment_instruction_id;
1394
1395 BEGIN
1396
1397 OPEN l_instruction_ovn_csr (p_payment_instruction_id);
1398 FETCH l_instruction_ovn_csr INTO l_instruction_ovn;
1399 CLOSE l_instruction_ovn_csr;
1400
1401 UPDATE
1402 iby_pay_instructions_all
1403 SET
1404 positive_pay_file_created_flag = 'Y',
1405 object_version_number = l_instruction_ovn + 1,
1406 last_updated_by = fnd_global.user_id,
1407 last_update_date = SYSDATE,
1408 last_update_login = nvl(fnd_global.LOGIN_ID, fnd_global.conc_login_id)
1409 WHERE
1410 payment_instruction_id = p_payment_instruction_id;
1411
1412 END set_pos_pay_created;
1413
1414
1415 PROCEDURE set_reg_rpt_created
1416 (
1417 p_payment_instruction_id IN NUMBER
1418 )
1419 IS
1420 l_instruction_ovn NUMBER;
1421
1422 CURSOR l_instruction_ovn_csr (p_payment_instruction_id IN NUMBER) IS
1423 SELECT object_version_number
1424 FROM iby_pay_instructions_all
1425 WHERE payment_instruction_id = p_payment_instruction_id;
1426
1427 BEGIN
1428
1429 OPEN l_instruction_ovn_csr (p_payment_instruction_id);
1430 FETCH l_instruction_ovn_csr INTO l_instruction_ovn;
1431 CLOSE l_instruction_ovn_csr;
1432
1433 UPDATE
1434 iby_pay_instructions_all
1435 SET
1436 regulatory_report_created_flag = 'Y',
1437 object_version_number = l_instruction_ovn + 1,
1438 last_updated_by = fnd_global.user_id,
1439 last_update_date = SYSDATE,
1440 last_update_login = nvl(fnd_global.LOGIN_ID, fnd_global.conc_login_id)
1441 WHERE
1442 payment_instruction_id = p_payment_instruction_id;
1443
1444 END set_reg_rpt_created;
1445
1446
1447 PROCEDURE post_fv_summary_format_status
1448 (
1449 p_payment_instruction_id IN NUMBER,
1450 p_process_status IN VARCHAR2
1451 )
1452 IS
1453 l_return_status VARCHAR2(1);
1454 l_msg_count NUMBER;
1455 l_msg_data VARCHAR2(2000);
1456
1457 BEGIN
1458
1459 FV_FEDERAL_PAYMENT_FIELDS_PKG.Summary_Format_Prog_Completed(
1460 p_api_version => 1.0 ,
1461 p_init_msg_list => NULL,
1462 p_commit => FND_API.G_FALSE,
1463 x_return_status => l_return_status,
1464 x_msg_count => l_msg_count,
1465 x_msg_data => l_msg_data,
1466 p_payment_instruction_id => p_payment_instruction_id,
1467 p_format_complete_status => p_process_status
1468 );
1469
1470 END post_fv_summary_format_status;
1471
1472
1473 FUNCTION get_instruction_format
1474 (
1475 p_payment_instruction_id IN NUMBER,
1476 p_format_type IN VARCHAR2
1477 ) RETURN VARCHAR2
1478 IS
1479 l_format_code varchar2(30);
1480 l_acp_ltr_format_code varchar2(30);
1481 l_pos_pay_format_code varchar2(30);
1482 l_reg_rpt_format_code varchar2(30);
1483
1484 CURSOR l_sra_format_csr (p_payment_instruction_id IN NUMBER) IS
1485 SELECT sra.remittance_advice_format_code
1486 FROM iby_pay_instructions_all ins, iby_payment_profiles pp,
1487 iby_remit_advice_setup sra
1488 WHERE payment_instruction_id = p_payment_instruction_id
1489 AND ins.payment_profile_id = pp.payment_profile_id
1490 AND pp.system_profile_code = sra.system_profile_code;
1491
1492 CURSOR l_aux_format_csr (p_payment_instruction_id IN NUMBER) IS
1493 SELECT pp.pay_file_letter_format_code,
1494 pp.positive_pay_format_code,
1495 pp.declaration_report_name
1496 FROM iby_pay_instructions_all ins, iby_payment_profiles pp
1497 WHERE payment_instruction_id = p_payment_instruction_id
1498 AND ins.payment_profile_id = pp.payment_profile_id;
1499
1500 BEGIN
1501
1502 IF p_format_type = 'REMITTANCE_ADVICE' THEN
1503
1504 OPEN l_sra_format_csr (p_payment_instruction_id);
1505 FETCH l_sra_format_csr INTO l_format_code;
1506 CLOSE l_sra_format_csr;
1507
1508 ELSIF p_format_type = 'DISBURSEMENT_ACCOMPANY_LETTER' OR
1509 p_format_type = 'POSITIVE_PAY_FILE' OR
1510 p_format_type = 'REGULATORY_REPORTING' THEN
1511
1512 OPEN l_aux_format_csr (p_payment_instruction_id);
1513 FETCH l_aux_format_csr INTO l_acp_ltr_format_code, l_pos_pay_format_code, l_reg_rpt_format_code;
1514 CLOSE l_aux_format_csr;
1515
1516 IF p_format_type = 'DISBURSEMENT_ACCOMPANY_LETTER' THEN
1517 l_format_code := l_acp_ltr_format_code;
1518 ELSIF p_format_type = 'POSITIVE_PAY_FILE' THEN
1519 l_format_code := l_pos_pay_format_code;
1520 ELSIF p_format_type = 'REGULATORY_REPORTING' THEN
1521 l_format_code := l_reg_rpt_format_code;
1522 END IF;
1523 END IF;
1524
1525 return l_format_code;
1526
1527 END get_instruction_format;
1528
1529
1530 FUNCTION get_allow_multiple_sra_flag
1531 (
1532 p_payment_instruction_id IN NUMBER
1533 ) RETURN VARCHAR2
1534 IS
1535 l_allow_multiple_sra_flag varchar2(1);
1536
1537 CURSOR l_multi_sra_flag_csr (p_payment_instruction_id IN NUMBER) IS
1538 SELECT sra.allow_multiple_copy_flag
1539 FROM iby_pay_instructions_all ins, iby_payment_profiles pp,
1540 iby_remit_advice_setup sra
1541 WHERE payment_instruction_id = p_payment_instruction_id
1542 AND ins.payment_profile_id = pp.payment_profile_id
1543 AND pp.system_profile_code = sra.system_profile_code;
1544
1545 BEGIN
1546
1547 OPEN l_multi_sra_flag_csr (p_payment_instruction_id);
1548 FETCH l_multi_sra_flag_csr INTO l_allow_multiple_sra_flag;
1549 CLOSE l_multi_sra_flag_csr;
1550
1551 return l_allow_multiple_sra_flag;
1552 END get_allow_multiple_sra_flag;
1553
1554
1555 PROCEDURE Init_Security
1556 IS
1557 l_appl_name VARCHAR2(50);
1558 l_Debug_Module VARCHAR2(255) := G_DEBUG_MODULE || '.Init_Security';
1559
1560 CURSOR l_ce_spgt_csr IS
1561 SELECT organization_type, organization_id, name
1562 FROM ce_security_profiles_v;
1563
1564 BEGIN
1565
1566 IF ce_sp_access_C = 0 THEN
1567
1568 -- can't use the CE function because
1569 -- we can be in a query
1570 -- have to init ourselves
1571 -- can't use the _gt. Have to use _v
1572 --CEP_STANDARD.init_security;
1573
1574 IF MO_GLOBAL.is_mo_init_done = 'N' THEN
1575 select APPLICATION_SHORT_NAME
1576 into l_appl_name
1577 from FND_APPLICATION
1578 where APPLICATION_ID = FND_GLOBAL.resp_appl_id;
1579
1580 -- Set MOAC security
1581 MO_GLOBAL.init(l_appl_name);
1582 END IF;
1583
1584 iby_debug_pub.add(debug_msg => 'Checking accessible orgs',
1585 debug_level => FND_LOG.LEVEL_STATEMENT,
1586 module => l_Debug_Module);
1587
1588 FOR l_ce_spgt IN l_ce_spgt_csr LOOP
1589
1590 iby_debug_pub.add(debug_msg => 'Org type: ' || l_ce_spgt.organization_type ||
1591 ' Org id: ' || l_ce_spgt.organization_id ||
1592 ' Org name: ' || l_ce_spgt.name,
1593 debug_level => FND_LOG.LEVEL_STATEMENT,
1594 module => l_Debug_Module);
1595
1596 END LOOP;
1597
1598 iby_debug_pub.add(debug_msg => 'Done checking accessible orgs',
1599 debug_level => FND_LOG.LEVEL_STATEMENT,
1600 module => l_Debug_Module);
1601
1602 ce_sp_access_C := ce_sp_access_C + 1;
1603
1604 END IF;
1605
1606 END Init_Security;
1607
1608 -- all orgs must be accessible
1609 -- also used to calculate moac data blocking flag
1610 FUNCTION val_instruction_accessible
1611 (
1612 p_payment_instruction_id IN NUMBER
1613 ) RETURN VARCHAR2
1614 IS
1615 l_dummy VARCHAR2(30);
1616 l_Debug_Module VARCHAR2(255) := G_DEBUG_MODULE || '.validate_instruction_accessible';
1617
1618 -- all orgs belonging to the instruction
1619 CURSOR l_ins_org_csr (p_payment_instruction_id IN NUMBER) IS
1620 SELECT po.org_type, po.org_id
1621 FROM iby_pay_instructions_all ins, iby_process_orgs po
1622 WHERE ins.payment_instruction_id = p_payment_instruction_id
1623 AND ins.payment_instruction_id = po.object_id
1624 AND po.object_type = 'PAYMENT_INSTRUCTION';
1625
1626 CURSOR l_validate_org_accessible_csr (p_org_type IN VARCHAR2, p_org_id IN NUMBER) IS
1627 SELECT 'exist'
1628 FROM ce_security_profiles_v
1629 WHERE organization_type = p_org_type
1630 AND organization_id = p_org_id;
1631
1632 BEGIN
1633
1634 iby_debug_pub.add(debug_msg => 'In validate_instruction_accessible(), before Init_Security',
1635 debug_level => FND_LOG.LEVEL_STATEMENT,
1636 module => l_Debug_Module);
1637
1638 Init_Security;
1639
1640 iby_debug_pub.add(debug_msg => 'In validate_instruction_accessible(), after Init_Security',
1641 debug_level => FND_LOG.LEVEL_STATEMENT,
1642 module => l_Debug_Module);
1643
1644 FOR l_ins_org IN l_ins_org_csr(p_payment_instruction_id) LOOP
1645
1646 OPEN l_validate_org_accessible_csr (l_ins_org.org_type, l_ins_org.org_id);
1647 FETCH l_validate_org_accessible_csr INTO l_dummy;
1648
1649 IF l_validate_org_accessible_csr%NOTFOUND THEN
1650
1651 iby_debug_pub.add(debug_msg => 'Org type: ' || l_ins_org.org_type || ' Org id: ' || l_ins_org.org_id ||
1652 ' is not accessible for user',
1653 debug_level => FND_LOG.LEVEL_STATEMENT,
1654 module => l_Debug_Module);
1655
1656 CLOSE l_validate_org_accessible_csr;
1657 return 'N';
1658 END IF;
1659
1660 CLOSE l_validate_org_accessible_csr;
1661
1662 END LOOP;
1663
1664 return 'Y';
1665
1666 END val_instruction_accessible;
1667
1668
1669 FUNCTION val_pmt_reg_instr_accessible
1670 (
1671 p_payment_instruction_id IN NUMBER
1672 ) RETURN VARCHAR2
1673 IS
1674 l_num_accessible_orgs NUMBER;
1675 l_Debug_Module VARCHAR2(255) := G_DEBUG_MODULE || '.val_pmt_reg_instr_accessible';
1676
1677 CURSOR l_accessible_orgs_csr (p_payment_instruction_id IN NUMBER) IS
1678 SELECT count(po.org_id)
1679 FROM iby_pay_instructions_all ins, iby_process_orgs po, ce_security_profiles_v ce_sp
1680 WHERE ins.payment_instruction_id = p_payment_instruction_id
1681 AND ins.payment_instruction_id = po.object_id
1682 AND po.object_type = 'PAYMENT_INSTRUCTION'
1683 AND ce_sp.organization_type = po.org_type
1684 AND ce_sp.organization_id = po.org_id;
1685
1686 BEGIN
1687
1688 Init_Security;
1689
1690 iby_debug_pub.add(debug_msg => 'p_payment_instruction_id: ' || p_payment_instruction_id,
1691 debug_level => FND_LOG.LEVEL_STATEMENT,
1692 module => l_Debug_Module);
1693
1694 OPEN l_accessible_orgs_csr (p_payment_instruction_id);
1695 FETCH l_accessible_orgs_csr INTO l_num_accessible_orgs;
1696 CLOSE l_accessible_orgs_csr;
1697
1698 iby_debug_pub.add(debug_msg => 'Number of orgs accessible for the user: ' || l_num_accessible_orgs,
1699 debug_level => FND_LOG.LEVEL_STATEMENT,
1700 module => l_Debug_Module);
1701
1702 IF l_num_accessible_orgs >= 1 THEN
1703
1704 return 'Y';
1705 END IF;
1706
1707 return 'N';
1708
1709 END val_pmt_reg_instr_accessible;
1710
1711
1712 FUNCTION val_ppr_st_rpt_accessible
1713 (
1714 p_payment_service_request_id IN NUMBER
1715 ) RETURN VARCHAR2
1716 IS
1717 l_num_accessible_orgs NUMBER;
1718 l_Debug_Module VARCHAR2(255) := G_DEBUG_MODULE || '.val_ppr_st_rpt_accessible';
1719
1720 BEGIN
1721
1722 Init_Security;
1723
1724 iby_debug_pub.add(debug_msg => 'p_payment_service_request_id: ' || p_payment_service_request_id,
1725 debug_level => FND_LOG.LEVEL_STATEMENT,
1726 module => l_Debug_Module);
1727
1728 l_num_accessible_orgs := get_accessible_ppr_org_count(p_payment_service_request_id);
1729
1730 iby_debug_pub.add(debug_msg => 'Number of orgs accessible for the user: ' || l_num_accessible_orgs,
1731 debug_level => FND_LOG.LEVEL_STATEMENT,
1732 module => l_Debug_Module);
1733
1734 IF l_num_accessible_orgs >= 1 THEN
1735
1736 return 'Y';
1737 END IF;
1738
1739 return 'N';
1740
1741 END val_ppr_st_rpt_accessible;
1742
1743
1744 FUNCTION get_accessible_ppr_org_count
1745 (
1746 p_payment_service_request_id IN NUMBER
1747 ) RETURN NUMBER
1748 IS
1749 l_num_accessible_orgs NUMBER;
1750
1751 CURSOR l_accessible_orgs_csr (p_payment_service_request_id IN NUMBER) IS
1752 SELECT count(po.org_id)
1753 FROM iby_pay_service_requests ppr, iby_process_orgs po, ce_security_profiles_v ce_sp
1754 WHERE ppr.payment_service_request_id = p_payment_service_request_id
1755 AND ppr.payment_service_request_id = po.object_id
1756 AND po.object_type = 'PAYMENT_REQUEST'
1757 AND ce_sp.organization_type = po.org_type
1758 AND ce_sp.organization_id = po.org_id;
1759
1760 BEGIN
1761
1762 OPEN l_accessible_orgs_csr (p_payment_service_request_id);
1763 FETCH l_accessible_orgs_csr INTO l_num_accessible_orgs;
1764 CLOSE l_accessible_orgs_csr;
1765
1766 RETURN l_num_accessible_orgs;
1767
1768 END get_accessible_ppr_org_count;
1769
1770
1771 FUNCTION check_ppr_moac_blocking
1772 (
1773 p_payment_service_request_id IN NUMBER
1774 ) RETURN VARCHAR2
1775 IS
1776 l_num_accessible_orgs NUMBER;
1777 l_total_orgs NUMBER;
1778
1779 CURSOR l_total_orgs_csr (p_payment_service_request_id IN NUMBER) IS
1780 SELECT count(po.org_id)
1781 FROM iby_pay_service_requests ppr, iby_process_orgs po
1782 WHERE ppr.payment_service_request_id = p_payment_service_request_id
1783 AND ppr.payment_service_request_id = po.object_id
1784 AND po.object_type = 'PAYMENT_REQUEST';
1785
1786 BEGIN
1787
1788 OPEN l_total_orgs_csr (p_payment_service_request_id);
1789 FETCH l_total_orgs_csr INTO l_total_orgs;
1790 CLOSE l_total_orgs_csr;
1791
1792 IF l_total_orgs > get_accessible_ppr_org_count(p_payment_service_request_id) THEN
1793 RETURN 'Y';
1794 END IF;
1795
1796 RETURN 'N';
1797
1798 END check_ppr_moac_blocking;
1799
1800
1801
1802
1803 PROCEDURE Reset_Periodic_Sequence_Value
1804 (
1805 x_errbuf OUT NOCOPY VARCHAR2,
1806 x_retcode OUT NOCOPY VARCHAR2,
1807 p_payment_profile_id IN NUMBER,
1808 p_sequence_number IN NUMBER,
1809 p_reset_value IN NUMBER,
1810 p_arg2 IN VARCHAR2 DEFAULT NULL, p_arg3 IN VARCHAR2 DEFAULT NULL,
1811 p_arg4 IN VARCHAR2 DEFAULT NULL,p_arg5 IN VARCHAR2 DEFAULT NULL,
1812 p_arg6 IN VARCHAR2 DEFAULT NULL, p_arg7 IN VARCHAR2 DEFAULT NULL,
1813 p_arg8 IN VARCHAR2 DEFAULT NULL, p_arg9 IN VARCHAR2 DEFAULT NULL,
1814 p_arg10 IN VARCHAR2 DEFAULT NULL, p_arg11 IN VARCHAR2 DEFAULT NULL,
1815 p_arg12 IN VARCHAR2 DEFAULT NULL, p_arg13 IN VARCHAR2 DEFAULT NULL,
1816 p_arg14 IN VARCHAR2 DEFAULT NULL, p_arg15 IN VARCHAR2 DEFAULT NULL,
1817 p_arg16 IN VARCHAR2 DEFAULT NULL, p_arg17 IN VARCHAR2 DEFAULT NULL,
1818 p_arg18 IN VARCHAR2 DEFAULT NULL, p_arg19 IN VARCHAR2 DEFAULT NULL,
1819 p_arg20 IN VARCHAR2 DEFAULT NULL, p_arg21 IN VARCHAR2 DEFAULT NULL,
1820 p_arg22 IN VARCHAR2 DEFAULT NULL, p_arg23 IN VARCHAR2 DEFAULT NULL,
1821 p_arg24 IN VARCHAR2 DEFAULT NULL, p_arg25 IN VARCHAR2 DEFAULT NULL,
1822 p_arg26 IN VARCHAR2 DEFAULT NULL, p_arg27 IN VARCHAR2 DEFAULT NULL,
1823 p_arg28 IN VARCHAR2 DEFAULT NULL, p_arg29 IN VARCHAR2 DEFAULT NULL,
1824 p_arg30 IN VARCHAR2 DEFAULT NULL, p_arg31 IN VARCHAR2 DEFAULT NULL,
1825 p_arg32 IN VARCHAR2 DEFAULT NULL, p_arg33 IN VARCHAR2 DEFAULT NULL,
1826 p_arg34 IN VARCHAR2 DEFAULT NULL, p_arg35 IN VARCHAR2 DEFAULT NULL,
1827 p_arg36 IN VARCHAR2 DEFAULT NULL, p_arg37 IN VARCHAR2 DEFAULT NULL,
1828 p_arg38 IN VARCHAR2 DEFAULT NULL, p_arg39 IN VARCHAR2 DEFAULT NULL,
1829 p_arg40 IN VARCHAR2 DEFAULT NULL, p_arg41 IN VARCHAR2 DEFAULT NULL,
1830 p_arg42 IN VARCHAR2 DEFAULT NULL, p_arg43 IN VARCHAR2 DEFAULT NULL,
1831 p_arg44 IN VARCHAR2 DEFAULT NULL, p_arg45 IN VARCHAR2 DEFAULT NULL,
1832 p_arg46 IN VARCHAR2 DEFAULT NULL, p_arg47 IN VARCHAR2 DEFAULT NULL,
1833 p_arg48 IN VARCHAR2 DEFAULT NULL, p_arg49 IN VARCHAR2 DEFAULT NULL,
1834 p_arg50 IN VARCHAR2 DEFAULT NULL, p_arg51 IN VARCHAR2 DEFAULT NULL,
1835 p_arg52 IN VARCHAR2 DEFAULT NULL, p_arg53 IN VARCHAR2 DEFAULT NULL,
1836 p_arg54 IN VARCHAR2 DEFAULT NULL, p_arg55 IN VARCHAR2 DEFAULT NULL,
1837 p_arg56 IN VARCHAR2 DEFAULT NULL, p_arg57 IN VARCHAR2 DEFAULT NULL,
1838 p_arg58 IN VARCHAR2 DEFAULT NULL, p_arg59 IN VARCHAR2 DEFAULT NULL,
1839 p_arg60 IN VARCHAR2 DEFAULT NULL, p_arg61 IN VARCHAR2 DEFAULT NULL,
1840 p_arg62 IN VARCHAR2 DEFAULT NULL, p_arg63 IN VARCHAR2 DEFAULT NULL,
1841 p_arg64 IN VARCHAR2 DEFAULT NULL, p_arg65 IN VARCHAR2 DEFAULT NULL,
1842 p_arg66 IN VARCHAR2 DEFAULT NULL, p_arg67 IN VARCHAR2 DEFAULT NULL,
1843 p_arg68 IN VARCHAR2 DEFAULT NULL, p_arg69 IN VARCHAR2 DEFAULT NULL,
1844 p_arg70 IN VARCHAR2 DEFAULT NULL, p_arg71 IN VARCHAR2 DEFAULT NULL,
1845 p_arg72 IN VARCHAR2 DEFAULT NULL, p_arg73 IN VARCHAR2 DEFAULT NULL,
1846 p_arg74 IN VARCHAR2 DEFAULT NULL, p_arg75 IN VARCHAR2 DEFAULT NULL,
1847 p_arg76 IN VARCHAR2 DEFAULT NULL, p_arg77 IN VARCHAR2 DEFAULT NULL,
1848 p_arg78 IN VARCHAR2 DEFAULT NULL, p_arg79 IN VARCHAR2 DEFAULT NULL,
1849 p_arg80 IN VARCHAR2 DEFAULT NULL, p_arg81 IN VARCHAR2 DEFAULT NULL,
1850 p_arg82 IN VARCHAR2 DEFAULT NULL, p_arg83 IN VARCHAR2 DEFAULT NULL,
1851 p_arg84 IN VARCHAR2 DEFAULT NULL, p_arg85 IN VARCHAR2 DEFAULT NULL,
1852 p_arg86 IN VARCHAR2 DEFAULT NULL, p_arg87 IN VARCHAR2 DEFAULT NULL,
1853 p_arg88 IN VARCHAR2 DEFAULT NULL, p_arg89 IN VARCHAR2 DEFAULT NULL,
1854 p_arg90 IN VARCHAR2 DEFAULT NULL, p_arg91 IN VARCHAR2 DEFAULT NULL,
1855 p_arg92 IN VARCHAR2 DEFAULT NULL, p_arg93 IN VARCHAR2 DEFAULT NULL,
1856 p_arg94 IN VARCHAR2 DEFAULT NULL, p_arg95 IN VARCHAR2 DEFAULT NULL,
1857 p_arg96 IN VARCHAR2 DEFAULT NULL, p_arg97 IN VARCHAR2 DEFAULT NULL,
1858 p_arg98 IN VARCHAR2 DEFAULT NULL, p_arg99 IN VARCHAR2 DEFAULT NULL,
1859 p_arg100 IN VARCHAR2 DEFAULT NULL
1860 )
1861 IS
1862
1863 l_payment_profile_name VARCHAR2(100);
1864 l_seq_notfound BOOLEAN;
1865 l_Debug_Module VARCHAR2(255) := G_DEBUG_MODULE || '.Reset_Periodic_Sequence_Value';
1866
1867 CURSOR l_val_seq_csr IS
1868 SELECT payment_profile_name
1869 FROM iby_payment_profiles
1870 WHERE payment_profile_id = p_payment_profile_id;
1871
1872 BEGIN
1873
1874 iby_debug_pub.add(debug_msg => 'Enter: ' || l_Debug_Module,
1875 debug_level => FND_LOG.LEVEL_PROCEDURE,
1876 module => l_Debug_Module);
1877
1878 iby_debug_pub.add(debug_msg => 'Input parameters: ',
1879 debug_level => FND_LOG.LEVEL_STATEMENT,
1880 module => l_Debug_Module);
1881
1882 iby_debug_pub.add(debug_msg => '============================================',
1883 debug_level => FND_LOG.LEVEL_STATEMENT,
1884 module => l_Debug_Module);
1885
1886 iby_debug_pub.add(debug_msg => 'p_payment_profile_id: ' || p_payment_profile_id,
1887 debug_level => FND_LOG.LEVEL_STATEMENT,
1888 module => l_Debug_Module);
1889
1890 iby_debug_pub.add(debug_msg => 'p_sequence_number: ' || p_sequence_number,
1891 debug_level => FND_LOG.LEVEL_STATEMENT,
1892 module => l_Debug_Module);
1893
1894 iby_debug_pub.add(debug_msg => 'p_reset_value: ' || p_reset_value,
1895 debug_level => FND_LOG.LEVEL_STATEMENT,
1896 module => l_Debug_Module);
1897
1898 iby_debug_pub.add(debug_msg => '============================================',
1899 debug_level => FND_LOG.LEVEL_STATEMENT,
1900 module => l_Debug_Module);
1901
1902
1903 OPEN l_val_seq_csr;
1904 FETCH l_val_seq_csr INTO l_payment_profile_name;
1905 l_seq_notfound := l_val_seq_csr%NOTFOUND;
1906 CLOSE l_val_seq_csr;
1907
1908 IF l_seq_notfound THEN
1909 -- set error for invalid data
1910 fnd_message.set_name('IBY', 'IBY_FD_PPP_SEQ_NOT_SAVED');
1911 fnd_msg_pub.add;
1912
1913 RAISE FND_API.G_EXC_ERROR;
1914 END IF;
1915
1916 IF p_sequence_number = 1 THEN
1917
1918 UPDATE iby_acct_pmt_profiles_b
1919 SET
1920 reset_value_1 = nvl(p_reset_value, reset_value_1),
1921 last_used_number_1 = nvl(p_reset_value, reset_value_1),
1922 reset_request_1 = fnd_global.CONC_REQUEST_ID,
1923 object_version_number = object_version_number + 1,
1924 last_updated_by = fnd_global.user_id,
1925 last_update_date = SYSDATE,
1926 last_update_login = nvl(fnd_global.LOGIN_ID, fnd_global.conc_login_id)
1927 WHERE
1928 payment_profile_id = p_payment_profile_id;
1929
1930 ELSIF p_sequence_number = 2 THEN
1931
1932 UPDATE iby_acct_pmt_profiles_b
1933 SET
1934 reset_value_2 = nvl(p_reset_value, reset_value_2),
1935 last_used_number_2 = nvl(p_reset_value, reset_value_2),
1936 reset_request_2 = fnd_global.CONC_REQUEST_ID,
1937 object_version_number = object_version_number + 1,
1938 last_updated_by = fnd_global.user_id,
1939 last_update_date = SYSDATE,
1940 last_update_login = nvl(fnd_global.LOGIN_ID, fnd_global.conc_login_id)
1941 WHERE
1942 payment_profile_id = p_payment_profile_id;
1943
1944 ELSIF p_sequence_number = 3 THEN
1945
1946 UPDATE iby_acct_pmt_profiles_b
1947 SET
1948 reset_value_3 = nvl(p_reset_value, reset_value_3),
1949 last_used_number_3 = nvl(p_reset_value, reset_value_3),
1950 reset_request_3 = fnd_global.CONC_REQUEST_ID,
1951 object_version_number = object_version_number + 1,
1952 last_updated_by = fnd_global.user_id,
1953 last_update_date = SYSDATE,
1954 last_update_login = nvl(fnd_global.LOGIN_ID, fnd_global.conc_login_id)
1955 WHERE
1956 payment_profile_id = p_payment_profile_id;
1957
1958 END IF;
1959
1960 iby_debug_pub.add(debug_msg => 'Exit: ' || l_Debug_Module,
1961 debug_level => FND_LOG.LEVEL_PROCEDURE,
1962 module => l_Debug_Module);
1963
1964 END Reset_Periodic_Sequence_Value;
1965
1966
1967 FUNCTION submit_schedule
1968 (
1969 p_payment_profile_id IN NUMBER,
1970 p_sequence_number IN NUMBER,
1971 p_reset_value IN NUMBER
1972 ) RETURN NUMBER
1973 IS
1974 l_def_sts BOOLEAN;
1975 l_request_id NUMBER;
1976 l_Debug_Module VARCHAR2(255) := G_DEBUG_MODULE || '.submit_schedule';
1977 l_icx_numeric_characters VARCHAR2(30); -- Bug 6411356
1978 l_bool_val boolean; -- Bug 6411356
1979
1980 BEGIN
1981
1982 iby_debug_pub.add(debug_msg => 'Enter: ' || l_Debug_Module,
1983 debug_level => FND_LOG.LEVEL_PROCEDURE,
1984 module => l_Debug_Module);
1985
1986 iby_debug_pub.add(debug_msg => 'p_payment_profile_id: ' || p_payment_profile_id,
1987 debug_level => FND_LOG.LEVEL_STATEMENT,
1988 module => l_Debug_Module);
1989
1990 iby_debug_pub.add(debug_msg => 'p_sequence_number: ' || p_sequence_number,
1991 debug_level => FND_LOG.LEVEL_STATEMENT,
1992 module => l_Debug_Module);
1993
1994 iby_debug_pub.add(debug_msg => 'p_reset_value: ' || p_reset_value,
1995 debug_level => FND_LOG.LEVEL_STATEMENT,
1996 module => l_Debug_Module);
1997
1998 iby_debug_pub.add(debug_msg => 'Setting request to deferred',
1999 debug_level => FND_LOG.LEVEL_STATEMENT,
2000 module => l_Debug_Module);
2001
2002 l_def_sts := fnd_request.set_deferred();
2003
2004 IF l_def_sts = FALSE THEN
2005 iby_debug_pub.add(debug_msg => 'Warning: failed to set request as deferred',
2006 debug_level => FND_LOG.LEVEL_STATEMENT,
2007 module => l_Debug_Module);
2008
2009 END IF;
2010
2011 iby_debug_pub.add(debug_msg => 'Before Calling FND_REQUEST.SUBMIT_REQUEST()',
2012 debug_level => FND_LOG.LEVEL_STATEMENT,
2013 module => l_Debug_Module);
2014
2015
2016 --Bug 6411356
2017 --below code added to set the current nls character setting
2018 --before submitting a child requests.
2019 fnd_profile.get('ICX_NUMERIC_CHARACTERS',l_icx_numeric_characters);
2020 l_bool_val:= FND_REQUEST.SET_OPTIONS( numeric_characters => l_icx_numeric_characters);
2021
2022
2023 -- submit the extract program
2024 l_request_id := FND_REQUEST.SUBMIT_REQUEST
2025 (
2026 'IBY',
2027 'IBY_RESET_PERIODIC_SEQ',
2028 null, -- description
2029 null, -- start_time
2030 FALSE, -- sub_request
2031 p_payment_profile_id,
2032 p_sequence_number,
2033 p_reset_value,
2034 '', '', '', '', '', '',
2035 '', '', '', '', '', '', '', '',
2036 '', '', '', '', '', '', '', '',
2037 '', '', '', '', '', '', '', '',
2038 '', '', '', '', '', '', '', '',
2039 '', '', '', '', '', '', '', '',
2040 '', '', '', '', '', '', '', '',
2041 '', '', '', '', '', '', '', '',
2042 '', '', '', '', '', '', '', '',
2043 '', '', '', '', '', '', '', '',
2044 '', '', '', '', '', '', '', '',
2045 '', '', '', '', '', '', '', '',
2046 '', '', ''
2047 );
2048
2049 iby_debug_pub.add(debug_msg => 'After Calling FND_REQUEST.SUBMIT_REQUEST()',
2050 debug_level => FND_LOG.LEVEL_STATEMENT,
2051 module => l_Debug_Module);
2052 iby_debug_pub.add(debug_msg => 'Request id: ' || l_request_id,
2053 debug_level => FND_LOG.LEVEL_STATEMENT,
2054 module => l_Debug_Module);
2055
2056 iby_debug_pub.add(debug_msg => 'Exit: ' || l_Debug_Module,
2057 debug_level => FND_LOG.LEVEL_PROCEDURE,
2058 module => l_Debug_Module);
2059
2060 RETURN l_request_id;
2061
2062 END submit_schedule;
2063
2064
2065 PROCEDURE submit_acp_ltr
2066 (
2067 p_payment_instruction_id IN NUMBER
2068 )
2069 IS
2070 l_acp_ltr_fmt_code VARCHAR2(30);
2071 l_fmt_notfound BOOLEAN;
2072 l_request_id NUMBER;
2073 l_Debug_Module VARCHAR2(255) := G_DEBUG_MODULE || '.submit_acp_ltr';
2074 l_icx_numeric_characters VARCHAR2(30); -- Bug 6411356
2075 l_return_status boolean; -- Bug 6411356
2076
2077 CURSOR l_acp_ltr_fmt_csr IS
2078 SELECT pay_file_letter_format_code
2079 FROM iby_payment_profiles pp, iby_pay_instructions_all ins
2080 WHERE ins.payment_instruction_id = p_payment_instruction_id
2081 AND ins.payment_profile_id = pp.payment_profile_id;
2082
2083 BEGIN
2084
2085 iby_debug_pub.add(debug_msg => 'Enter: ' || l_Debug_Module,
2086 debug_level => FND_LOG.LEVEL_PROCEDURE,
2087 module => l_Debug_Module);
2088
2089 iby_debug_pub.add(debug_msg => 'Input payment instruction ID: ' || p_payment_instruction_id,
2090 debug_level => FND_LOG.LEVEL_STATEMENT,
2091 module => l_Debug_Module);
2092
2093 OPEN l_acp_ltr_fmt_csr;
2094 FETCH l_acp_ltr_fmt_csr INTO l_acp_ltr_fmt_code;
2095 l_fmt_notfound := l_acp_ltr_fmt_csr%NOTFOUND;
2096 CLOSE l_acp_ltr_fmt_csr;
2097
2098 IF l_fmt_notfound THEN
2099 iby_debug_pub.add(debug_msg => 'The payment process profile for the instruction does not have an ' ||
2100 'accompany letter format. So no action is required. ',
2101 debug_level => FND_LOG.LEVEL_STATEMENT,
2102 module => l_Debug_Module);
2103 RETURN;
2104 END IF;
2105
2106
2107 iby_debug_pub.add(debug_msg => 'Before Calling FND_REQUEST.SUBMIT_REQUEST()',
2108 debug_level => FND_LOG.LEVEL_STATEMENT,
2109 module => l_Debug_Module);
2110
2111
2112 --Bug 6411356
2113 --below code added to set the current nls character setting
2114 --before submitting a child requests.
2115 fnd_profile.get('ICX_NUMERIC_CHARACTERS',l_icx_numeric_characters);
2116 l_return_status:= FND_REQUEST.SET_OPTIONS( numeric_characters => l_icx_numeric_characters);
2117
2118
2119 -- submit the acp ltr program
2120 -- note the format code is not passed
2121 l_request_id := FND_REQUEST.SUBMIT_REQUEST
2122 (
2123 'IBY',
2124 'IBY_FD_ACP_LTR_FORMAT',
2125 null, -- description
2126 null, -- start_time
2127 FALSE, -- sub_request
2128 p_payment_instruction_id,
2129 '', '', '', '', '', '', '', '',
2130 '', '', '', '', '', '', '', '',
2131 '', '', '', '', '', '', '', '',
2132 '', '', '', '', '', '', '', '',
2133 '', '', '', '', '', '', '', '',
2134 '', '', '', '', '', '', '', '',
2135 '', '', '', '', '', '', '', '',
2136 '', '', '', '', '', '', '', '',
2137 '', '', '', '', '', '', '', '',
2138 '', '', '', '', '', '', '', '',
2139 '', '', '', '', '', '', '', '',
2140 '', '', '', '', '', '', '', '',
2141 '', '', ''
2142 );
2143
2144 iby_debug_pub.add(debug_msg => 'After Calling FND_REQUEST.SUBMIT_REQUEST()',
2145 debug_level => FND_LOG.LEVEL_STATEMENT,
2146 module => l_Debug_Module);
2147 iby_debug_pub.add(debug_msg => 'Request id: ' || l_request_id,
2148 debug_level => FND_LOG.LEVEL_STATEMENT,
2149 module => l_Debug_Module);
2150
2151 iby_debug_pub.add(debug_msg => 'Exit: ' || l_Debug_Module,
2152 debug_level => FND_LOG.LEVEL_PROCEDURE,
2153 module => l_Debug_Module);
2154
2155 COMMIT;
2156
2157 END submit_acp_ltr;
2158
2159
2160 PROCEDURE Run_ECE_Formatting
2161 (
2162 p_payment_instruction_id IN NUMBER
2163 )
2164 IS
2165 l_ece_status VARCHAR2(1);
2166 l_post_status VARCHAR2(1);
2167 l_msg_count NUMBER;
2168 l_msg_data VARCHAR2(2000);
2169 l_Debug_Module VARCHAR2(255) := G_DEBUG_MODULE || '.Run_ECE_Formatting';
2170
2171 BEGIN
2172
2173 iby_debug_pub.add(debug_msg => 'Enter: ' || l_Debug_Module,
2174 debug_level => FND_LOG.LEVEL_PROCEDURE,
2175 module => l_Debug_Module);
2176
2177 iby_debug_pub.add(debug_msg => 'Input payment instruction ID: ' || p_payment_instruction_id,
2178 debug_level => FND_LOG.LEVEL_STATEMENT,
2179 module => l_Debug_Module);
2180
2181 iby_debug_pub.add(debug_msg => 'Calling ECE_AP_PAYMENT.Extract_PYO_Outbound() API... ' || l_ece_status,
2182 debug_level => FND_LOG.LEVEL_STATEMENT,
2183 module => l_Debug_Module);
2184
2185 ECE_AP_PAYMENT.Extract_PYO_Outbound ( p_api_version => 1.0,
2186 p_init_msg_list => fnd_api.g_false,
2187 p_commit => fnd_api.g_false,
2188 x_return_status => l_ece_status,
2189 x_msg_count => l_msg_count,
2190 x_msg_data => l_msg_data,
2191 p_payment_instruction_id => p_payment_instruction_id);
2192
2193 iby_debug_pub.add(debug_msg => 'After calling ECE_AP_PAYMENT.Extract_PYO_Outbound() API, return status: ' || l_ece_status,
2194 debug_level => FND_LOG.LEVEL_STATEMENT,
2195 module => l_Debug_Module);
2196
2197 IF l_ece_status = FND_API.G_RET_STS_SUCCESS THEN
2198
2199 iby_debug_pub.add(debug_msg => 'Calling Post_Results()',
2200 debug_level => FND_LOG.LEVEL_STATEMENT,
2201 module => l_Debug_Module);
2202
2203 IBY_FD_POST_PICP_PROGS_PVT.Post_Results
2204 (
2205 p_payment_instruction_id => p_payment_instruction_id,
2206 p_newStatus => 'FORMATTED_ELECTRONIC',
2207 p_is_reprint_flag => 'N',
2208 x_return_status => l_post_status
2209 );
2210
2211 iby_debug_pub.add(debug_msg => 'After calling Post_Results(), return status: ' || l_post_status,
2212 debug_level => FND_LOG.LEVEL_STATEMENT,
2213 module => l_Debug_Module);
2214
2215 IF l_post_status <> FND_API.G_RET_STS_SUCCESS THEN
2216 RAISE FND_API.G_EXC_ERROR;
2217 END IF;
2218
2219 ELSE
2220 RAISE FND_API.G_EXC_ERROR;
2221 END IF;
2222
2223 iby_debug_pub.add(debug_msg => 'Exit: ' || l_Debug_Module,
2224 debug_level => FND_LOG.LEVEL_PROCEDURE,
2225 module => l_Debug_Module);
2226
2227 END Run_ECE_Formatting;
2228
2229
2230 PROCEDURE Test_CP
2231 (
2232 p_payment_instruction_id IN NUMBER,
2233 p_program_short_name IN VARCHAR2
2234 )
2235 IS
2236 l_request_id NUMBER;
2237 l_Debug_Module VARCHAR2(255) := G_DEBUG_MODULE || '.Test_CP';
2238
2239 l_icx_numeric_characters VARCHAR2(30); -- Bug 6411356
2240 l_bool_val boolean; -- Bug 6411356
2241
2242 BEGIN
2243
2244 iby_debug_pub.add(debug_msg => 'Enter: ' || l_Debug_Module,
2245 debug_level => FND_LOG.LEVEL_PROCEDURE,
2246 module => l_Debug_Module);
2247
2248 iby_debug_pub.add(debug_msg => 'Input payment instruction ID: ' || p_payment_instruction_id,
2249 debug_level => FND_LOG.LEVEL_STATEMENT,
2250 module => l_Debug_Module);
2251
2252
2253 iby_debug_pub.add(debug_msg => 'Before Calling FND_REQUEST.SUBMIT_REQUEST()',
2254 debug_level => FND_LOG.LEVEL_STATEMENT,
2255 module => l_Debug_Module);
2256
2257
2258 --Bug 6411356
2259 --below code added to set the current nls character setting
2260 --before submitting a child requests.
2261 fnd_profile.get('ICX_NUMERIC_CHARACTERS',l_icx_numeric_characters);
2262 l_bool_val:= FND_REQUEST.SET_OPTIONS( numeric_characters => l_icx_numeric_characters);
2263
2264 -- submit the extract program
2265 l_request_id := FND_REQUEST.SUBMIT_REQUEST
2266 (
2267 'IBY',
2268 p_program_short_name,
2269 null, -- description
2270 null, -- start_time
2271 FALSE, -- sub_request
2272 p_payment_instruction_id,
2273 '', '', '', '', '', '', '', '',
2274 '', '', '', '', '', '', '', '',
2275 '', '', '', '', '', '', '', '',
2276 '', '', '', '', '', '', '', '',
2277 '', '', '', '', '', '', '', '',
2278 '', '', '', '', '', '', '', '',
2279 '', '', '', '', '', '', '', '',
2280 '', '', '', '', '', '', '', '',
2281 '', '', '', '', '', '', '', '',
2282 '', '', '', '', '', '', '', '',
2283 '', '', '', '', '', '', '', '',
2284 '', '', '', '', '', '', '', '',
2285 '', '', ''
2286 );
2287
2288 iby_debug_pub.add(debug_msg => 'After Calling FND_REQUEST.SUBMIT_REQUEST()',
2289 debug_level => FND_LOG.LEVEL_STATEMENT,
2290 module => l_Debug_Module);
2291 iby_debug_pub.add(debug_msg => 'Request id: ' || l_request_id,
2292 debug_level => FND_LOG.LEVEL_STATEMENT,
2293 module => l_Debug_Module);
2294
2295 IF l_request_id = 0 THEN
2296 RAISE FND_API.G_EXC_ERROR;
2297 END IF;
2298
2299 iby_debug_pub.add(debug_msg => 'Exit: ' || l_Debug_Module,
2300 debug_level => FND_LOG.LEVEL_PROCEDURE,
2301 module => l_Debug_Module);
2302
2303 END Test_CP;
2304
2305
2306 END IBY_FD_POST_PICP_PROGS_PVT;
2307
2308
2309