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