[Home] [Help]
PACKAGE BODY: APPS.ARP_PROGRAM_BR_REMIT
Source
1 PACKAGE BODY ARP_PROGRAM_BR_REMIT AS
2 /* $Header: ARBRRMPB.pls 120.11.12010000.3 2008/11/13 15:53:42 pbapna ship $*/
3
4 G_PKG_NAME CONSTANT varchar2(30) := 'ARP_PROGRAM_BR_REMIT';
5
6 pg_Date_Format CONSTANT VARCHAR2(20) := 'DD-MON-RR';
7
8 TYPE CUR_TYP IS REF CURSOR;
9
10 /*
11 bug 1810619 : define process_status, if value is EXCEPTION, process will not
12 continue with the next step
13 */
14
15 process_status varchar2(80);
16 PG_DEBUG varchar2(1) := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
17
18 -- SSA - R12
19 g_org_id AR_SYSTEM_PARAMETERS.org_id%TYPE;
20
21 /*-------------- Private procedures used by the package --------------------*/
22
23 PROCEDURE create_remit_pvt(
24 p_batch_id IN AR_BATCHES.batch_id%TYPE,
25 p_remit_total_low IN AR_BATCHES.control_amount%TYPE,
26 p_remit_total_high IN AR_BATCHES.control_amount%TYPE,
27 p_maturity_date_low IN AR_PAYMENT_SCHEDULES.due_date%TYPE,
28 p_maturity_date_high IN AR_PAYMENT_SCHEDULES.due_date%TYPE,
29 p_br_number_low IN AR_PAYMENT_SCHEDULES.trx_number%TYPE,
30 p_br_number_high IN AR_PAYMENT_SCHEDULES.trx_number%TYPE,
31 p_br_amount_low IN AR_PAYMENT_SCHEDULES.amount_due_remaining%TYPE,
32 p_br_amount_high IN AR_PAYMENT_SCHEDULES.amount_due_remaining%TYPE,
33 p_transaction_type1_id IN AR_PAYMENT_SCHEDULES.cust_trx_type_id%TYPE,
34 p_transaction_type2_id IN AR_PAYMENT_SCHEDULES.cust_trx_type_id%TYPE,
35 p_unsigned_flag IN varchar2,
36 p_signed_flag IN RA_CUST_TRX_TYPES.signed_flag%TYPE,
37 p_drawee_issued_flag IN RA_CUST_TRX_TYPES.drawee_issued_flag%TYPE,
38 p_include_unpaid_flag IN varchar2,
39 p_drawee_id IN AR_PAYMENT_SCHEDULES.customer_id%TYPE,
40 p_drawee_number_low IN HZ_CUST_ACCOUNTS.account_number%TYPE,
41 p_drawee_number_high IN HZ_CUST_ACCOUNTS.account_number%TYPE,
42 p_drawee_class1_code IN HZ_CUST_ACCOUNTS.customer_class_code%TYPE,
43 p_drawee_class2_code IN HZ_CUST_ACCOUNTS.customer_class_code%TYPE,
44 p_drawee_class3_code IN HZ_CUST_ACCOUNTS.customer_class_code%TYPE,
45 p_drawee_bank_name IN ce_bank_branches_v.bank_name%TYPE,
46 p_drawee_bank_branch_id IN ce_bank_branches_v.branch_party_id%TYPE,
47 p_drawee_branch_city IN ce_bank_branches_v.city%TYPE,
48 p_br_sort_criteria IN varchar2,
49 p_br_order IN varchar2,
50 p_drawee_sort_criteria IN varchar2,
51 p_drawee_order IN varchar2);
52
53 PROCEDURE approve_remit_pvt(
54 p_batch_id IN AR_BATCHES.batch_id%TYPE);
55
56 PROCEDURE format_remit_pvt(
57 p_batch_id IN AR_BATCHES.batch_id%TYPE,
58 p_physical_bill IN varchar2);
59
60 PROCEDURE cancel_remit_pvt(
61 p_batch_id IN AR_BATCHES.batch_id%TYPE);
62
63 PROCEDURE print_remit_pvt(
64 p_batch_id IN AR_BATCHES.batch_id%TYPE);
65
66 PROCEDURE print_bills_remit_pvt(
67 p_batch_id IN AR_BATCHES.batch_id%TYPE);
68
69 PROCEDURE process_br_payment(
70 p_batch_id IN AR_BATCHES.batch_id%TYPE);
71
72
73 /*------------------------ Public procedures ------------------------*/
74
75
76 /*===========================================================================+
77 | PROCEDURE |
78 | auto_create_remit_program |
79 | |
80 | DESCRIPTION |
81 | Procedure called during the process create bills receivable |
82 | remittance process to create, approve and/or format the remittance |
83 | |
84 | SCOPE - PUBLIC |
85 | |
86 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED - NONE |
87 | |
88 | ARGUMENTS : IN : |
89 | p_create_flag : Y or N |
90 | this flag is Y when the action Create is selected |
91 | p_approve_flag : Y or N |
92 | this flag is Y when the action Approve is selected |
93 | p_format_flag : Y or N |
94 | this flag is Y when the action Format is selected |
95 | |
96 | : OUT NOCOPY : NONE |
97 | |
98 | RETURNS : NONE |
99 | |
100 | NOTES |
101 | |
102 | MODIFICATION HISTORY |
103 | 14-APR-00 M Flahaut Created |
104 | 22-AUG-01 V Crisostomo Bug 1810619 : check for value in |
105 | process_status prior to proceeding to next |
106 | step |:
107 +===========================================================================*/
108 PROCEDURE auto_create_remit_program(
109 errbuf OUT NOCOPY varchar2,
110 retcode OUT NOCOPY varchar2,
111 p_create_flag IN varchar2 DEFAULT 'N',
112 p_cancel_flag IN varchar2 DEFAULT 'N',
113 p_approve_flag IN varchar2 DEFAULT 'N',
114 p_format_flag IN varchar2 DEFAULT 'N',
115 p_print_flag IN varchar2 DEFAULT 'N',
116 p_print_bills_flag IN varchar2 DEFAULT 'N',
117 p_batch_id IN varchar2 DEFAULT NULL,
118 p_remit_total_low IN varchar2 DEFAULT NULL,
119 p_remit_total_high IN varchar2 DEFAULT NULL,
120 p_maturity_date_low IN varchar2 DEFAULT NULL,
121 p_maturity_date_high IN varchar2 DEFAULT NULL,
122 p_br_number_low IN varchar2 DEFAULT NULL,
123 p_br_number_high IN varchar2 DEFAULT NULL,
124 p_br_amount_low IN varchar2 DEFAULT NULL,
125 p_br_amount_high IN varchar2 DEFAULT NULL,
126 p_transaction_type1_id IN varchar2 DEFAULT NULL,
127 p_transaction_type2_id IN varchar2 DEFAULT NULL,
128 p_unsigned_flag IN varchar2 DEFAULT NULL,
129 p_signed_flag IN varchar2 DEFAULT NULL,
130 p_drawee_issued_flag IN varchar2 DEFAULT NULL,
131 p_include_unpaid_flag IN varchar2 DEFAULT NULL,
132 p_drawee_id IN varchar2 DEFAULT NULL,
133 p_drawee_number_low IN varchar2 DEFAULT NULL,
134 p_drawee_number_high IN varchar2 DEFAULT NULL,
135 p_drawee_class1_code IN varchar2 DEFAULT NULL,
136 p_drawee_class2_code IN varchar2 DEFAULT NULL,
137 p_drawee_class3_code IN varchar2 DEFAULT NULL,
138 p_drawee_bank_name IN varchar2 DEFAULT NULL,
139 p_drawee_bank_branch_id IN varchar2 DEFAULT NULL,
140 p_drawee_branch_city IN varchar2 DEFAULT NULL,
141 p_br_sort_criteria IN varchar2 DEFAULT NULL,
142 p_br_order IN varchar2 DEFAULT NULL,
143 p_drawee_sort_criteria IN varchar2 DEFAULT NULL,
144 p_drawee_order IN varchar2 DEFAULT NULL,
145 p_physical_bill IN varchar2 DEFAULT 'N') IS
146
147 l_batch_id AR_BATCHES.batch_id%TYPE;
148 l_remit_total_low AR_BATCHES.control_amount%TYPE;
149 l_remit_total_high AR_BATCHES.control_amount%TYPE;
150 l_maturity_date_low AR_PAYMENT_SCHEDULES.due_date%TYPE;
151 l_maturity_date_high AR_PAYMENT_SCHEDULES.due_date%TYPE;
152 l_br_amount_low AR_PAYMENT_SCHEDULES.amount_due_remaining%TYPE;
153 l_br_amount_high AR_PAYMENT_SCHEDULES.amount_due_remaining%TYPE;
154 l_transaction_type1_id AR_PAYMENT_SCHEDULES.cust_trx_type_id%TYPE;
155 l_transaction_type2_id AR_PAYMENT_SCHEDULES.cust_trx_type_id%TYPE;
156 l_drawee_id AR_PAYMENT_SCHEDULES.customer_id%TYPE;
157 l_drawee_bank_branch_id CE_BANK_BRANCHES_V.branch_party_id%TYPE;
158
159 BEGIN
160
161 -- SSA - R12 : set global variable
162 select org_id
163 into g_org_id
164 from ar_system_parameters;
165
166 process_status := 'STARTED AUTO_CREATE_REMIT_PROGRAM';
167
168 FND_FILE.PUT_LINE(FND_FILE.LOG,'auto_create_remit_program(+)');
169 FND_FILE.PUT_LINE(FND_FILE.LOG,'----------- ACTIONS ----------------');
170 FND_FILE.PUT_LINE(FND_FILE.LOG,'create_flag ' || p_create_flag);
171 FND_FILE.PUT_LINE(FND_FILE.LOG,'approve_flag ' || p_approve_flag);
172 FND_FILE.PUT_LINE(FND_FILE.LOG,'format_flag ' || p_format_flag);
173 FND_FILE.PUT_LINE(FND_FILE.LOG,'print_flag ' || p_print_flag);
174 FND_FILE.PUT_LINE(FND_FILE.LOG,'print_bills_flag ' || p_print_bills_flag);
175 FND_FILE.PUT_LINE(FND_FILE.LOG,'cancel_flag ' || p_cancel_flag);
176 FND_FILE.PUT_LINE(FND_FILE.LOG,'Batch ID ' || p_batch_id);
177
178 --------------------------------------------------------------
179 -- Convert parameters passed to the appropriate datatype ---
180 --------------------------------------------------------------
181
182 l_batch_id := to_number(p_batch_id);
183 l_remit_total_low := to_number(p_remit_total_low);
184 l_remit_total_high := to_number(p_remit_total_high);
185 l_maturity_date_low := to_date(p_maturity_date_low,pg_Date_Format);
186 l_maturity_date_high := to_date(p_maturity_date_high,pg_Date_Format);
187 l_br_amount_low := to_number(p_br_amount_low);
188 l_br_amount_high := to_number(p_br_amount_high);
189 l_transaction_type1_id := to_number(p_transaction_type1_id);
190 l_transaction_type2_id := to_number(p_transaction_type2_id);
191 l_drawee_id := to_number(p_drawee_id);
192 l_drawee_bank_branch_id := to_number(p_drawee_bank_branch_id);
193
194 ---------------------------------------------------
195 ----- Process -----
196 ---------------------------------------------------
197
198 -- Create remittance
199 IF (p_create_flag = 'Y') THEN
200
201 ARP_PROGRAM_BR_REMIT.create_remit_pvt(
202 l_batch_id,
203 l_remit_total_low,
204 l_remit_total_high,
205 l_maturity_date_low,
206 l_maturity_date_high,
207 p_br_number_low,
208 p_br_number_high,
209 l_br_amount_low,
210 l_br_amount_high,
211 l_transaction_type1_id,
212 l_transaction_type2_id,
213 p_unsigned_flag,
214 p_signed_flag,
215 p_drawee_issued_flag,
216 p_include_unpaid_flag,
217 l_drawee_id,
218 p_drawee_number_low,
219 p_drawee_number_high,
220 p_drawee_class1_code,
221 p_drawee_class2_code,
222 p_drawee_class3_code,
223 p_drawee_bank_name,
224 l_drawee_bank_branch_id,
225 p_drawee_branch_city,
226 p_br_sort_criteria,
227 p_br_order,
228 p_drawee_sort_criteria,
229 p_drawee_order);
230
231 END IF;
232
233 -- Bug 1810619 : For all the following steps, check value in process_status, if it sees
234 -- an EXCEPTION do not proceed
235
236 -- Cancel remittance
237 IF p_cancel_flag = 'Y' and instr(process_status,'EXCEPTION') = 0 THEN
238 ARP_PROGRAM_BR_REMIT.cancel_remit_pvt(l_batch_id);
239 END IF;
240
241 -- Approve remittance
242 IF p_approve_flag = 'Y' and instr(process_status,'EXCEPTION') = 0 THEN
243 ARP_PROGRAM_BR_REMIT.approve_remit_pvt(l_batch_id);
244 END IF;
245
246 -- Process Payment
247 /* Payment uptake call the iby api for AUTH and settlement */
248
249 ARP_PROGRAM_BR_REMIT.process_br_payment(l_batch_id);
250
251 -- Format remittance
252 IF p_format_flag = 'Y' and instr(process_status,'EXCEPTION') = 0 THEN
253 ARP_PROGRAM_BR_REMIT.format_remit_pvt(l_batch_id,p_physical_bill);
254 END IF;
255
256 -- Print remittance
257 IF p_print_flag = 'Y' and instr(process_status,'EXCEPTION') = 0 THEN
258 ARP_PROGRAM_BR_REMIT.print_remit_pvt(l_batch_id);
259 END IF;
260
261 -- Print Remittance 's Bills receivable
262 IF p_print_bills_flag = 'Y' and instr(process_status,'EXCEPTION') = 0 THEN
263 ARP_PROGRAM_BR_REMIT.print_bills_remit_pvt(l_batch_id);
264 END IF;
265
266 FND_FILE.PUT_LINE(FND_FILE.LOG,'auto_create_remit_program(-)');
267
268 process_status := 'COMPLETED AUTO_CREATE_REMIT_PROGRAM';
269
270 EXCEPTION
271 WHEN OTHERS THEN
272 FND_FILE.PUT_LINE(FND_FILE.LOG,'EXCEPTION : ARP_PROGRAM_BR_REMIT.auto_create_remit_program');
273 RAISE;
274
275 END auto_create_remit_program;
276
277
278 /*===========================================================================+
279 | PROCEDURE |
280 | create_remit_pvt |
281 | |
282 | DESCRIPTION |
283 | Procedure called during the process create bills receivable |
284 | remittance process, to select the bills and assign them |
285 | to the remittance |
286 | |
287 | SCOPE - PRIVATE |
288 | |
289 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED - NONE |
290 | |
291 | |
292 | |
293 | ARGUMENTS : IN : |
294 | : OUT NOCOPY : NONE |
295 | |
296 | RETURNS : NONE |
297 | |
298 | NOTES |
299 | |
300 | MODIFICATION HISTORY - Created by Mireille Flahaut - 15/06/2000 |
301 | |
302 | 22-AUG-01 V Crisostomo Bug 1810619 : set process_status |
303 +===========================================================================*/
304 PROCEDURE create_remit_pvt(
305 p_batch_id IN AR_BATCHES.batch_id%TYPE,
306 p_remit_total_low IN AR_BATCHES.control_amount%TYPE,
307 p_remit_total_high IN AR_BATCHES.control_amount%TYPE,
308 p_maturity_date_low IN AR_PAYMENT_SCHEDULES.due_date%TYPE,
309 p_maturity_date_high IN AR_PAYMENT_SCHEDULES.due_date%TYPE,
310 p_br_number_low IN AR_PAYMENT_SCHEDULES.trx_number%TYPE,
311 p_br_number_high IN AR_PAYMENT_SCHEDULES.trx_number%TYPE,
312 p_br_amount_low IN AR_PAYMENT_SCHEDULES.amount_due_remaining%TYPE,
313 p_br_amount_high IN AR_PAYMENT_SCHEDULES.amount_due_remaining%TYPE,
314 p_transaction_type1_id IN AR_PAYMENT_SCHEDULES.cust_trx_type_id%TYPE,
315 p_transaction_type2_id IN AR_PAYMENT_SCHEDULES.cust_trx_type_id%TYPE,
316 p_unsigned_flag IN varchar2,
317 p_signed_flag IN RA_CUST_TRX_TYPES.signed_flag%TYPE,
318 p_drawee_issued_flag IN RA_CUST_TRX_TYPES.drawee_issued_flag%TYPE,
319 p_include_unpaid_flag IN varchar2,
320 p_drawee_id IN AR_PAYMENT_SCHEDULES.customer_id%TYPE,
321 p_drawee_number_low IN HZ_CUST_ACCOUNTS.account_number%TYPE,
322 p_drawee_number_high IN HZ_CUST_ACCOUNTS.account_number%TYPE,
323 p_drawee_class1_code IN HZ_CUST_ACCOUNTS.customer_class_code%TYPE,
324 p_drawee_class2_code IN HZ_CUST_ACCOUNTS.customer_class_code%TYPE,
325 p_drawee_class3_code IN HZ_CUST_ACCOUNTS.customer_class_code%TYPE,
326 p_drawee_bank_name IN CE_BANK_BRANCHES_V.bank_name%TYPE,
327 p_drawee_bank_branch_id IN CE_BANK_BRANCHES_V.branch_party_id%TYPE,
328 p_drawee_branch_city IN CE_BANK_BRANCHES_V.city%TYPE,
329 p_br_sort_criteria IN varchar2,
330 p_br_order IN varchar2,
331 p_drawee_sort_criteria IN varchar2,
332 p_drawee_order IN varchar2) IS
333
334
335 l_batch_rec AR_BATCHES%ROWTYPE;
336
337 l_select_detail varchar2(25000);
338
339 l_control_count AR_BATCHES.control_count%TYPE;
340 l_control_amount AR_BATCHES.control_amount%TYPE;
341
342 BEGIN
343
344 process_status := 'STARTED CREATE_REMIT_PVT';
345
346 FND_FILE.PUT_LINE(FND_FILE.LOG,'create_remit_pvt (+)');
347
348 SAVEPOINT create_remit_PVT;
349
350 -- lock and fetch of the batch row
351 l_batch_rec.batch_id := p_batch_id;
352 ARP_CR_BATCHES_PKG.lock_fetch_p(l_batch_rec);
353
354 -- The action Create is enabled only if the batch status is STARTED_CREATION
355 IF l_batch_rec.batch_applied_status NOT IN ('STARTED_CREATION') THEN
356 FND_MESSAGE.set_name('AR','AR_BR_CANNOT_RECREATE_REMIT');
357 APP_EXCEPTION.raise_exception;
358 END IF;
359
360 -- BR search criteria validation
361 ARP_PROCESS_BR_REMIT.validate_br_search_criteria(
362 p_remit_total_low,
363 p_remit_total_high,
364 p_maturity_date_low,
365 p_maturity_date_high,
366 p_br_number_low,
367 p_br_number_high,
368 p_br_amount_low,
369 p_br_amount_high,
370 p_transaction_type1_id,
371 p_transaction_type2_id,
372 p_unsigned_flag,
373 p_signed_flag,
374 p_drawee_issued_flag,
375 p_include_unpaid_flag,
376 p_drawee_id,
377 p_drawee_number_low,
378 p_drawee_number_high,
379 p_drawee_class1_code,
380 p_drawee_class2_code,
381 p_drawee_class3_code,
382 p_drawee_bank_name,
383 p_drawee_bank_branch_id,
384 p_drawee_branch_city,
385 p_br_sort_criteria,
386 p_br_order,
387 p_drawee_sort_criteria,
388 p_drawee_order);
389
390 -- The necessary BR select statements are built
391 ARP_PROCESS_BR_REMIT.construct_select_br_for_remit (
392 p_transaction_type1_id,
393 p_transaction_type2_id,
394 p_drawee_class1_code,
395 p_drawee_class2_code,
396 p_drawee_class3_code,
397 p_drawee_bank_name,
398 p_drawee_bank_branch_id,
399 p_drawee_branch_city,
400 p_unsigned_flag,
401 p_signed_flag,
402 p_drawee_issued_flag,
403 p_br_sort_criteria,
404 p_br_order,
405 p_drawee_sort_criteria,
406 p_drawee_order,
407 l_select_detail);
408
409 -- The selected BR are assigned to the remittance by updating the reserved columns in the table AR_PAYMENT_SCHEDULES
410 -- until the parameter remittance maximum amount is reached (If it is filled of course).
411 ARP_PROCESS_BR_REMIT.assign_br_to_remit(
412 l_select_detail,
413 l_batch_rec.batch_id,
414 p_remit_total_high,
415 p_include_unpaid_flag,
416 l_batch_rec.batch_date,
417 l_batch_rec.gl_date,
418 l_batch_rec.currency_code,
419 l_batch_rec.remit_bank_acct_use_id,
420 p_maturity_date_low,
421 p_maturity_date_high,
422 p_br_number_low,
423 p_br_number_high,
424 p_br_amount_low,
425 p_br_amount_high,
426 p_unsigned_flag,
427 p_signed_flag,
428 p_drawee_issued_flag,
429 p_drawee_id,
430 p_drawee_number_low,
431 p_drawee_number_high,
432 l_control_count,
433 l_control_amount);
434
435 -- update the batch row with the control count and the control amount
436 l_batch_rec.control_count := l_control_count;
437 l_batch_rec.control_amount := l_control_amount;
438 l_batch_rec.batch_applied_status := 'COMPLETED_CREATION';
439 arp_cr_batches_pkg.update_p(l_batch_rec,l_batch_rec.batch_id);
440
441 FND_FILE.PUT_LINE(FND_FILE.LOG,'-------------- ACTION CREATE --------------');
442 FND_FILE.PUT_LINE(FND_FILE.LOG,'BR Remittance number :'||l_batch_rec.name);
443 FND_FILE.PUT_LINE(FND_FILE.LOG,'Count :'||l_control_count);
444 FND_FILE.PUT_LINE(FND_FILE.LOG,'Amount :'||l_control_amount);
445 FND_FILE.PUT_LINE(FND_FILE.LOG,'-------------------------------------------');
446
447 COMMIT;
448
449 FND_FILE.PUT_LINE(FND_FILE.LOG,'create_remit_pvt (-)');
450
451 process_status := 'COMPLETED CREATE_REMIT_PVT';
452
453 EXCEPTION
454 WHEN OTHERS THEN
455
456 process_status := 'EXCEPTION CREATE_REMIT_PVT';
457
458 FND_FILE.PUT_LINE(FND_FILE.LOG,'EXCEPTION : ARP_PROGRAM_BR_REMIT.create_remit_pvt - ROLLBACK');
459 FND_FILE.PUT_LINE(FND_FILE.LOG,SQLERRM);
460 ROLLBACK TO create_remit_PVT;
461 RAISE;
462
463 END create_remit_pvt;
464
465
466 /*===========================================================================+
467 | PROCEDURE |
468 | cancel_remit_pvt |
469 | |
470 | Procedure called during the process create bills receivable |
471 | remittance process, to cancel the BR remittance batch |
472 | |
473 | SCOPE - PRIVATE |
474 | |
475 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED - NONE |
476 | |
477 | |
478 | ARGUMENTS : IN : |
479 | p_batch_id : remittance batch identifier |
480 | |
481 | : OUT NOCOPY : NONE |
482 | |
483 | RETURNS : NONE |
484 | |
485 | NOTES |
486 | |
487 | MODIFICATION HISTORY - Created by Mireille Flahaut - 18/04/2000 |
488 | 22-AUG-01 V Crisostomo Bug 1810619 : define process_status |
489 | |
490 +===========================================================================*/
491 PROCEDURE cancel_remit_pvt(
492 p_batch_id IN AR_BATCHES.batch_id%TYPE) IS
493
494 l_batch_rec AR_BATCHES%ROWTYPE;
495 l_batch_applied_status AR_BATCHES.batch_applied_status%TYPE;
496
497 BEGIN
498
499 process_status := 'STARTED CANCEL_REMIT_PVT';
500
501 FND_FILE.PUT_LINE(FND_FILE.LOG,'cancel_remit_pvt (+)');
502
503 SAVEPOINT cancel_remit_PVT;
504
505 -- lock and fetch of the batch row
506 l_batch_rec.batch_id := p_batch_id;
507 ARP_CR_BATCHES_PKG.lock_fetch_p(l_batch_rec);
508
509 -- The action Cancel is enabled only if the batch status is STARTED_CANCELLATION
510 IF l_batch_rec.batch_applied_status NOT IN ('STARTED_CANCELLATION') THEN
511 FND_MESSAGE.set_name('AR','AR_BR_CANNOT_CANCEL_REMIT');
512 APP_EXCEPTION.raise_exception;
513 END IF;
514
515 ARP_BR_REMIT_BATCHES.cancel_remit(p_batch_id,l_batch_applied_status);
516
517 -- the batch row is updated in the procedure ARP_BR_REMIT_BATCHES.cancel_remit
518 -- with the status set to 'CL', the control count and the control amount set to zero
519 -- and the batch applied status set to 'completed_cancellation'
520
521 FND_FILE.PUT_LINE(FND_FILE.LOG,'-------------- ACTION CANCEL ---------------');
522 FND_FILE.PUT_LINE(FND_FILE.LOG,'BR Remittance number : ' || l_batch_rec.name);
523 FND_FILE.PUT_LINE(FND_FILE.LOG,'--------------------------------------------');
524
525 FND_FILE.PUT_LINE(FND_FILE.LOG,'cancel_remit_pvt (-)');
526
527 process_status := 'COMPLETED CANCEL_REMIT_PVT';
528
529 EXCEPTION
530 WHEN OTHERS THEN
531
532 process_status := 'EXCEPTION CANCEL_REMIT_PVT';
533
534 FND_FILE.PUT_LINE(FND_FILE.LOG,'EXCEPTION : ARP_PROGRAM_BR_REMIT.cancel_remit_pvt - ROLLBACK');
535 FND_FILE.PUT_LINE(FND_FILE.LOG,SQLERRM);
536 ROLLBACK TO cancel_remit_PVT;
537
538 END cancel_remit_pvt;
539
540
541 /*===========================================================================+
542 | PROCEDURE |
543 | approve_remit_pvt |
544 | |
545 | DESCRIPTION |
546 | Procedure called during the process create bills receivable |
547 | remittance process, to approve the remittance |
548 | |
549 | SCOPE - PRIVATE |
550 | |
551 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED - NONE |
552 | |
553 | |
554 | ARGUMENTS : IN : |
555 | p_batch_id : remittance batch identifier |
556 | |
557 | : OUT NOCOPY : NONE |
558 | |
559 | RETURNS : NONE |
560 | |
561 | NOTES |
562 | |
563 | MODIFICATION HISTORY - Created by Mireille Flahaut - 15/06/2000 |
564 | |
565 | 22-AUG-01 V Crisostomo Bug 1810619 : set process_status |
566 +===========================================================================*/
567 PROCEDURE approve_remit_pvt(
568 p_batch_id IN AR_BATCHES.batch_id%TYPE) IS
569
570 l_batch_rec AR_BATCHES%ROWTYPE;
571 l_ps_id AR_PAYMENT_SCHEDULES.payment_schedule_id%TYPE;
572
573 l_new_status AR_TRANSACTION_HISTORY.status%TYPE;
574
575 CURSOR cur_br IS
576 SELECT payment_schedule_id
577 FROM AR_PAYMENT_SCHEDULES
578 WHERE reserved_type = 'REMITTANCE'
579 AND reserved_value = p_batch_id;
580
581 BEGIN
582
583 process_status := 'STARTED APPROVE_REMIT_PVT';
584
585 FND_FILE.PUT_LINE(FND_FILE.LOG,'approve_remit_pvt (+)');
586
587 SAVEPOINT approve_remit_PVT;
588
589 -- lock and fetch of the batch row
590 l_batch_rec.batch_id := p_batch_id;
591 ARP_CR_BATCHES_PKG.lock_fetch_p(l_batch_rec);
592
593 -- The action Approve is enabled only if the batch status is COMPLETED_CREATION and STARTED_APPROVAL
594 IF l_batch_rec.batch_applied_status NOT IN ('COMPLETED_CREATION','STARTED_APPROVAL') THEN
595 FND_MESSAGE.set_name('AR','AR_BR_CANNOT_APPROVE_REMIT');
596 APP_EXCEPTION.raise_exception;
597 END IF;
598
599 -- The remitted BR are approved
600 OPEN cur_br;
601
602 LOOP
603 FETCH cur_br INTO l_ps_id;
604 EXIT WHEN cur_br%NOTFOUND;
605 AR_BILLS_MAINTAIN_PUB.Approve_BR_Remit(p_batch_id,l_ps_id,l_new_status);
606 END LOOP;
607
608 CLOSE cur_br;
609
610 -- update the batch row with the batch applied status
611 l_batch_rec.status := 'CL';
612 l_batch_rec.batch_applied_status := 'COMPLETED_APPROVAL';
613 arp_cr_batches_pkg.update_p(l_batch_rec,l_batch_rec.batch_id);
614
615 commit;
616
617 FND_FILE.PUT_LINE(FND_FILE.LOG,'-------------- ACTION APPROVE --------------');
618 FND_FILE.PUT_LINE(FND_FILE.LOG,'BR Remittance number :'||l_batch_rec.name);
619 FND_FILE.PUT_LINE(FND_FILE.LOG,'Count :'||l_batch_rec.control_count);
620 FND_FILE.PUT_LINE(FND_FILE.LOG,'Amount :'||l_batch_rec.control_amount);
621 FND_FILE.PUT_LINE(FND_FILE.LOG,'--------------------------------------------');
622
623 FND_FILE.PUT_LINE(FND_FILE.LOG,'approve_remit_pvt (-)');
624
625 process_status := 'COMPLETED APPROVE_REMIT_PVT';
626
627 EXCEPTION
628 WHEN OTHERS THEN
629
630 process_status := 'EXCEPTION APPROVE_REMIT_PVT';
631
632 FND_FILE.PUT_LINE(FND_FILE.LOG,'EXCEPTION : ARP_PROGRAM_BR_REMIT.approve_remit_pvt - ROLLBACK');
633 FND_FILE.PUT_LINE(FND_FILE.LOG,SQLERRM);
634 ROLLBACK TO approve_remit_PVT;
635
636 IF cur_br%ISOPEN THEN
637 CLOSE cur_br;
638 END IF;
639
640 END approve_remit_pvt;
641
642
643 /*===========================================================================+
644 | PROCEDURE |
645 | format_remit_pvt |
646 | |
647 | DESCRIPTION |
648 | Procedure called during the process create bills receivable |
649 | remittance process, to format the remittance |
650 | |
651 | SCOPE - PRIVATE |
652 | |
653 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED - NONE |
654 | TIEN API : update ?? |
655 | |
656 | |
657 | ARGUMENTS : IN : |
658 | p_batch_id : remittance batch identifier |
659 | |
660 | : OUT NOCOPY : NONE |
661 | |
662 | RETURNS : NONE |
663 | |
664 | NOTES |
665 | |
666 | MODIFICATION HISTORY - Created by Mireille Flahaut - 18/04/2000 |
667 | |
668 | 22-AUG-01 V Crisostomo Bug 1810619 : set process_status |
669 | 05-OCT-05 Surendra Rajan Bug 4609222 : Removed the auto trans program |
670 | validation. |
671 +===========================================================================*/
672 PROCEDURE format_remit_pvt(
673 p_batch_id IN AR_BATCHES.batch_id%TYPE,
674 p_physical_bill IN varchar2) IS
675
676 l_batch_rec AR_BATCHES%ROWTYPE;
677
678 format_program AP_PAYMENT_PROGRAMS.program_name%TYPE;
679
680 l_request_id number;
681
682 BEGIN
683
684 process_status := 'STARTED FORMAT_REMIT_PVT';
685
686 FND_FILE.PUT_LINE(FND_FILE.LOG,'format_remit_pvt (+)');
687
688 SAVEPOINT format_remit_PVT;
689
690 -- lock and fetch of the batch row
691 l_batch_rec.batch_id := p_batch_id;
692 ARP_CR_BATCHES_PKG.lock_fetch_p(l_batch_rec);
693
694 -- The action Format is enabled only if the transmission program field is filled
695 /*
696 IF l_batch_rec.auto_trans_program_id IS NULL THEN
697 FND_MESSAGE.set_name('AR','AR_BR_NO_TRANS_PROGRAM');
698 APP_EXCEPTION.raise_exception;
699 END IF;
700 */
701
702 -- The action Format is enabled only if the batch status is COMPLETED_APPROVAL or STARTED_FORMAT
703 IF l_batch_rec.batch_applied_status NOT IN ('COMPLETED_APPROVAL','STARTED_FORMAT') THEN
704 FND_MESSAGE.set_name('AR','AR_BR_CANNOT_FORMAT_REMIT');
705 APP_EXCEPTION.raise_exception;
706 END IF;
707
708 FND_FILE.PUT_LINE(FND_FILE.LOG,'-------------- ACTION FORMAT ---------------');
709 FND_FILE.PUT_LINE(FND_FILE.LOG,'BR Remittance number : ' || l_batch_rec.name);
710
711 ----------------------------------------------------------------------
712 -- Submit the transmission program as a concurrent request
713 ----------------------------------------------------------------------
714
715 -- SSA - R12 : set org id prior to calling submit_request
716 FND_REQUEST.set_org_id(g_org_id);
717
718 IF format_program IN ('ARBRCS32') THEN
719 -- Submit the spanish CSB file format program as a concurrent request
720 l_request_id := FND_REQUEST.submit_request('AR'
721 ,format_program
722 ,NULL
723 ,NULL
724 ,NULL
725 ,'P_BATCH_ID='''||p_batch_id||''''
726 ,'P_PHYSICAL_BILL='''||p_physical_bill||'''');
727
728 ELSIF ( format_program = 'ARBRIBYFMT' ) THEN
729
730 -- Submit the iPayment BR remittance format prg as a concurrent request
731 l_request_id := FND_REQUEST.submit_request('AR'
732 ,format_program
733 ,'iPayment Bills Receivable Remittance'
734 ,NULL
735 ,NULL
736 ,p_batch_id);
737 ELSE
738
739 -- Submit another format program as a concurrent request
740 l_request_id := FND_REQUEST.submit_request('AR'
741 ,format_program
742 ,NULL
743 ,NULL
744 ,NULL
745 ,'P_BATCH_ID='''||p_batch_id||'''');
746
747 END IF;
748
749 IF (l_request_id = 0) THEN
750 FND_MESSAGE.set_name('AR','AR_BR_BATCH_SUBMIT_FAILED');
751 FND_MESSAGE.set_token('PROCEDURE','ARP_PROGRAM_BR_REMIT.format_remit_pvt');
752 APP_EXCEPTION.raise_exception;
753 ELSE
754 FND_FILE.PUT_LINE(FND_FILE.LOG,'Submitted Request -'||format_program||'. Request ID ='||to_char(l_request_id));
755 END IF;
756
757 -- update the batch row with the control count and the control amount
758 l_batch_rec.batch_applied_status := 'COMPLETED_FORMAT';
759 arp_cr_batches_pkg.update_p(l_batch_rec,l_batch_rec.batch_id);
760
761 commit;
762
763 FND_FILE.PUT_LINE(FND_FILE.LOG,'--------------------------------------------');
764
765 FND_FILE.PUT_LINE(FND_FILE.LOG,'format_remit_pvt (-)');
766
767 process_status := 'COMPLETED FORMAT_REMIT_PVT';
768
769 EXCEPTION
770 WHEN OTHERS THEN
771
772 process_status := 'EXCEPTION FORMAT_REMIT_PVT';
773
774 FND_FILE.PUT_LINE(FND_FILE.LOG,'EXCEPTION : ARP_PROGRAM_BR_REMIT.format_remit_pvt - ROLLBACK');
775 FND_FILE.PUT_LINE(FND_FILE.LOG,SQLERRM);
776 ROLLBACK TO format_remit_PVT;
777
778
779 END format_remit_pvt;
780
781 /*===========================================================================+
782 | PROCEDURE |
783 | print_remit_pvt |
784 | |
785 | DESCRIPTION |
786 | Procedure called during the process create bills receivable |
787 | remittance process, to submit the BR remittance report |
788 | |
789 | SCOPE - PRIVATE |
790 | |
791 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED - NONE |
792 | |
793 | |
794 | ARGUMENTS : IN : |
795 | p_batch_id : remittance batch identifier |
796 | |
797 | : OUT NOCOPY : NONE |
798 | |
799 | RETURNS : NONE |
800 | |
801 | NOTES |
802 | |
803 | MODIFICATION HISTORY - Created by Mireille Flahaut - 18/04/2000 |
804 | |
805 +===========================================================================*/
806 PROCEDURE print_remit_pvt(
807 p_batch_id IN AR_BATCHES.batch_id%TYPE) IS
808
809 l_batch_rec AR_BATCHES%ROWTYPE;
810
811 -- Report 'BR Remittance Batch Management Report'
812 print_program AP_PAYMENT_PROGRAMS.program_name%TYPE := 'ARBRRMBM';
813
814 l_request_id number;
815
816 l_sort_by varchar2(30);
817 l_sum_or_det varchar2(30);
818 l_include_formatted varchar2(30);
819 l_remit_bank ce_bank_branches_v.bank_name%TYPE;
820 l_remit_bank_branch ce_bank_branches_v.bank_branch_name%TYPE;
821
822 CURSOR prt_program IS
823 Select program_name
824 From AP_PAYMENT_PROGRAMS
825 Where program_id = l_batch_rec.auto_print_program_id;
826
827 BEGIN
828
829 FND_FILE.PUT_LINE(FND_FILE.LOG,'print_remit_pvt (+)');
830
831 SAVEPOINT print_remit_PVT;
832
833 -- lock and fetch of the batch row
834 l_batch_rec.batch_id := p_batch_id;
835 ARP_CR_BATCHES_PKG.lock_fetch_p(l_batch_rec);
836
837 -- parameter sort_by value must be BATCH NAME
838 select lookup_code
839 into l_sort_by
840 from ar_lookups
841 WHERE LOOKUP_TYPE = 'SORT_BY_ARXAPRMB'
842 AND ENABLED_FLAG = 'Y'
843 AND lookup_code = 'BATCH NAME';
844
845 -- parameter Summary_or_Detailed value must be 'DETAILED'
846 select lookup_code
847 into l_sum_or_det
848 from ar_lookups
849 where lookup_type = 'ARXAPRMB_SD' and lookup_code = 'DETAILED';
850
851 -- parameter include_formatted must be 'Y'
852 select lookup_code
853 into l_include_formatted
854 from fnd_lookups
855 WHERE LOOKUP_TYPE = 'YES_NO' and lookup_code = 'Y';
856
857 -- retrieve the remittance bank name and bank branch name
858 SELECT bank.bank_name,
859 bank.bank_branch_name
860 INTO l_remit_bank,
861 l_remit_bank_branch
862 FROM ce_bank_branches_v bank,
863 ce_bank_accounts cba,
864 ce_bank_acct_uses cbau
865 WHERE cbau.bank_acct_use_id = l_batch_rec.remit_bank_acct_use_id
866 AND cbau.bank_account_id = cba.bank_account_id
867 AND cba.bank_branch_id = bank.branch_party_id;
868
869 OPEN prt_program;
870 FETCH prt_program INTO print_program;
871 IF prt_program%NOTFOUND THEN
872 print_program := 'ARBRRMBM';
873 END IF;
874 CLOSE prt_program;
875
876 FND_FILE.PUT_LINE(FND_FILE.LOG,'----------- ACTION Print Report ------------');
877 FND_FILE.PUT_LINE(FND_FILE.LOG,'BR Remittance number : ' || l_batch_rec.name);
878 FND_FILE.PUT_LINE(FND_FILE.LOG,'Report '||print_program||' parameters');
879 FND_FILE.PUT_LINE(FND_FILE.LOG,'SOB ID '||arp_global.set_of_books_id);
880 FND_FILE.PUT_LINE(FND_FILE.LOG,'SORT BY '||l_sort_by);
881 FND_FILE.PUT_LINE(FND_FILE.LOG,'status '||l_batch_rec.batch_applied_status);
882 FND_FILE.PUT_LINE(FND_FILE.LOG,'SUM OR DET '||l_sum_or_det);
883 FND_FILE.PUT_LINE(FND_FILE.LOG,'BATCH DATE '||fnd_date.date_to_canonical(l_batch_rec.batch_date));
884 FND_FILE.PUT_LINE(FND_FILE.LOG,'DEPOSIT '||l_batch_rec.bank_deposit_number);
885 FND_FILE.PUT_LINE(FND_FILE.LOG,'NAME '||l_batch_rec.name);
886 FND_FILE.PUT_LINE(FND_FILE.LOG,'INCLUDE '||l_include_formatted);
887 FND_FILE.PUT_LINE(FND_FILE.LOG,'REMIT METHOD '||l_batch_rec.remit_method_code);
888 FND_FILE.PUT_LINE(FND_FILE.LOG,'REMIT BANK '||l_remit_bank);
889 FND_FILE.PUT_LINE(FND_FILE.LOG,'REMIT BRANCH '||l_remit_bank_branch);
890 FND_FILE.PUT_LINE(FND_FILE.LOG,'REMIT ACCOUNT USE '||l_batch_rec.remit_bank_acct_use_id);
891
892
893 -- SSA - R12 : set org id prior to calling submit_request
894 FND_REQUEST.set_org_id(g_org_id);
895
896 -- Submit the Standard Print program 'BR Remittance Batch Management Report' as a concurrent request
897
898 --Bug 5391515 as the arguments being passed in the below commented code does not exist in the reports
899 --Bug7246266, handled the call for 'BR Remittance Batch Management Report'
900 IF (print_program = 'ARBRRMBM') Then
901 l_request_id := FND_REQUEST.submit_request('AR'
902 ,print_program
903 ,NULL
904 ,NULL
905 ,NULL
906 ,arp_global.set_of_books_id
907 ,l_sort_by
908 ,l_batch_rec.batch_applied_status
909 ,l_sum_or_det
910 ,fnd_date.date_to_canonical(l_batch_rec.batch_date)
911 ,fnd_date.date_to_canonical(l_batch_rec.batch_date)
912 ,l_batch_rec.bank_deposit_number
913 ,l_batch_rec.bank_deposit_number
914 ,l_batch_rec.name
915 ,l_batch_rec.name
916 ,l_include_formatted
917 ,l_batch_rec.remit_method_code
918 ,l_remit_bank
919 ,l_remit_bank_branch
920 ,l_batch_rec.remit_bank_acct_use_id);
921
922 Else
923 l_request_id := FND_REQUEST.SUBMIT_REQUEST (
924 application=>'AR',
925 program=>print_program,
926 sub_request=>FALSE,
927 argument1=>'P_BATCH_ID='|| p_batch_id
928 ) ;
929 End If;
930
931 IF (l_request_id = 0) THEN
932 FND_MESSAGE.set_name('AR','AR_BR_BATCH_SUBMIT_FAILED');
933 FND_MESSAGE.set_token('PROCEDURE','ARP_PROGRAM_BR_REMIT.print_remit_pvt');
934 APP_EXCEPTION.raise_exception;
935 ELSE
936 FND_FILE.PUT_LINE(FND_FILE.LOG,'Submitted Request - '||print_program||'. Request ID ='||to_char(l_request_id));
937 END IF;
938
939 commit;
940
941 FND_FILE.PUT_LINE(FND_FILE.LOG,'--------------------------------------------');
942
943 FND_FILE.PUT_LINE(FND_FILE.LOG,'print_remit_pvt (-)');
944
945 EXCEPTION
946 WHEN OTHERS THEN
947
948 FND_FILE.PUT_LINE(FND_FILE.LOG,'EXCEPTION : ARP_PROGRAM_BR_REMIT.print_remit_pvt - ROLLBACK');
949 FND_FILE.PUT_LINE(FND_FILE.LOG,SQLERRM);
950 ROLLBACK TO print_remit_PVT;
951
952 END print_remit_pvt;
953
954
955 /*===========================================================================+
956 | PROCEDURE |
957 | print_bills_remit_pvt |
958 | |
959 | DESCRIPTION |
960 | Procedure called during the process create bills receivable |
961 | remittance process, to print the remittance 's bills receivable |
962 | |
963 | SCOPE - PRIVATE |
964 | |
965 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED - NONE |
966 | |
967 | |
968 | ARGUMENTS : IN : |
969 | p_batch_id : remittance batch identifier |
970 | |
971 | : OUT NOCOPY : NONE |
972 | |
973 | RETURNS : NONE |
974 | |
975 | NOTES |
976 | |
977 | MODIFICATION HISTORY - Created by Mireille Flahaut - 15/06/2000 |
978 | |
979 +===========================================================================*/
980 PROCEDURE print_bills_remit_pvt(
981 p_batch_id IN AR_BATCHES.batch_id%TYPE) IS
982
983 l_batch_rec AR_BATCHES%ROWTYPE;
984 l_request_id NUMBER;
985 l_format VARCHAR2(30) := 'REMIT BATCH';
986
987 BEGIN
988
989 FND_FILE.PUT_LINE(FND_FILE.LOG,'print_bills_remit_pvt (+)');
990
991 SAVEPOINT print_bills_remit_PVT;
992
993 -- lock and fetch of the batch row
994 l_batch_rec.batch_id := p_batch_id;
995 ARP_CR_BATCHES_PKG.lock_fetch_p(l_batch_rec);
996
997 FND_FILE.PUT_LINE(FND_FILE.LOG,'----------- ACTION Print Bills -------------');
998 FND_FILE.PUT_LINE(FND_FILE.LOG,'BR Remittance number : ' || l_batch_rec.name);
999 FND_FILE.PUT_LINE(FND_FILE.LOG,'Program ARBRFMTW parameters');
1000 FND_FILE.PUT_LINE(FND_FILE.LOG,'BATCH ID '||p_batch_id);
1001 FND_FILE.PUT_LINE(FND_FILE.LOG,'SOB ID '||arp_global.set_of_books_id);
1002
1003 -- SSA - R12 : set org id prior to calling submit_request
1004 FND_REQUEST.set_org_id(g_org_id);
1005
1006 l_request_id := FND_REQUEST.submit_request('AR'
1007 ,'ARBRFMTW'
1008 ,NULL
1009 ,NULL
1010 ,NULL
1011 ,l_format
1012 ,p_batch_id
1013 ,NULL
1014 ,NULL
1015 ,arp_global.set_of_books_id);
1016
1017 IF (l_request_id = 0) THEN
1018 FND_MESSAGE.set_name('AR','AR_BR_BATCH_SUBMIT_FAILED');
1019 FND_MESSAGE.set_token('PROCEDURE','ARP_PROGRAM_BR_REMIT.print_bills_remit_pvt');
1020 APP_EXCEPTION.raise_exception;
1021 ELSE
1022 FND_FILE.PUT_LINE(FND_FILE.LOG,'Submitted Request - ARBRFMTW. Request ID ='||to_char(l_request_id));
1023 END IF;
1024
1025 commit;
1026
1027 FND_FILE.PUT_LINE(FND_FILE.LOG,'--------------------------------------------');
1028
1029 FND_FILE.PUT_LINE(FND_FILE.LOG,'print_bills_remit_pvt (-)');
1030
1031 EXCEPTION
1032 WHEN OTHERS THEN
1033
1034 FND_FILE.PUT_LINE(FND_FILE.LOG,'EXCEPTION : ARP_PROGRAM_BR_REMIT.print_bills_remit_pvt - ROLLBACK');
1035 FND_FILE.PUT_LINE(FND_FILE.LOG,SQLERRM);
1036 ROLLBACK TO print_bills_remit_PVT;
1037
1038 END print_bills_remit_pvt;
1039
1040 /*===========================================================================+
1041 | PROCEDURE |
1042 | process_br_payment |
1043 | |
1044 | DESCRIPTION |
1045 | Procedure called during the process create bills receivable |
1046 | |
1047 | remittance process, to process the payment |
1048 | |
1049 | SCOPE - PRIVATE |
1050 | |
1051 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED - NONE |
1052 | |
1053 | |
1054 | ARGUMENTS : IN : |
1055 | p_batch_id : remittance batch identifier |
1056 | |
1057 | : OUT NOCOPY : NONE |
1058 | |
1059 | RETURNS : NONE |
1060 | |
1061 | NOTES |
1062 | |
1063 | MODIFICATION HISTORY - Created by Mireille Flahaut - 15/06/2000 |
1064 | |
1065 | 22-AUG-01 V Crisostomo Bug 1810619 : set process_status |
1066 +===========================================================================*/
1067 PROCEDURE process_br_payment(
1068 p_batch_id IN AR_BATCHES.batch_id%TYPE) IS
1069
1070 CURSOR br_rem_info_cur IS
1071 SELECT br.trx_number,
1072 br.customer_trx_id,
1073 br.br_amount,
1074 br.invoice_currency_code,
1075 br.org_id,
1076 party.party_id,
1077 br.drawee_id,
1078 br.drawee_site_use_id,
1079 br.payment_trxn_extension_id
1080 FROM ra_customer_trx br,
1081 hz_cust_accounts hca,
1082 hz_parties party
1083 WHERE br.remittance_batch_id = p_batch_id
1084 and hca.party_id = party.party_id
1085 and hca.cust_account_id = br.drawee_id ;
1086
1087 br_rem_info br_rem_info_cur%ROWTYPE;
1088 l_action VARCHAR2(80);
1089 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
1090 l_msg_count NUMBER;
1091 l_msg_data VARCHAR2(2000);
1092 l_iby_msg_data VARCHAR2(2000);
1093 l_vend_msg_data VARCHAR2(2000);
1094 l_cpy_msg_data VARCHAR2(2000);
1095
1096 /* DECLARE the variables required for the payment engine (CPY ) all the REC TYPES */
1097 p_trxn_entity_id NUMBER;
1098 lc_trxn_entity_id IBY_FNDCPT_COMMON_PUB.Id_tbl_type;
1099
1100 l_auth_flag VARCHAR2(1);
1101 l_auth_id NUMBER;
1102 /* END DECLARE the variables required for the payment engine (CPY ) all the REC TYPES */
1103
1104 /* DECLARE the variables required for the payment engine (CPY AND AUTH) all the REC TYPES */
1105
1106 l_payer_rec IBY_FNDCPT_COMMON_PUB.PayerContext_rec_type;
1107 l_payee_rec IBY_FNDCPT_TRXN_PUB.PayeeContext_rec_type;
1108 l_trxn_entity_id NUMBER;
1109 l_auth_attribs_rec IBY_FNDCPT_TRXN_PUB.AuthAttribs_rec_type;
1110 l_trxn_attribs_rec IBY_FNDCPT_TRXN_PUB.TrxnExtension_rec_type;
1111 l_amount_rec IBY_FNDCPT_TRXN_PUB.Amount_rec_type;
1112 l_authresult_rec IBY_FNDCPT_TRXN_PUB.AuthResult_rec_type; /* OUT AUTH RESULT STRUCTURE */
1113 l_response_rec IBY_FNDCPT_COMMON_PUB.Result_rec_type; /* OUT RESPONSE STRUCTURE */
1114 l_entity_id NUMBER; -- OUT FROM COPY
1115 /* END DECLARE the variables required for the payment engine (AUTH) all the REC TYPES */
1116
1117
1118 /* DECLARE the variables required for the payment engine (SETTLEMENT) all the REC TYPES */
1119 ls_response_rec_tab IBY_FNDCPT_TRXN_PUB.SettlementResult_tbl_type;
1120 ls_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
1121 ls_msg_count NUMBER;
1122 ls_msg_data VARCHAR2(2000);
1123 ls_iby_msg_data VARCHAR2(2000);
1124 l_call_settlement VARCHAR2(1) := 'N';
1125 l_program_application_id NUMBER;
1126
1127
1128 /* END DECLARE the variables required for the payment engine (SETTLEMENT) all the REC TYPES */
1129 x_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
1130 x_msg_count NUMBER;
1131 x_msg_data VARCHAR2(2000);
1132
1133
1134 BEGIN
1135
1136 IF PG_DEBUG in ('Y', 'C') THEN
1137 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Entering payment processing...');
1138 END IF;
1139
1140
1141
1142 FOR br_rem_info in br_rem_info_cur LOOP
1143
1144
1145
1146 -- Step 1: (always performed):
1147
1148 -- set up payee record:
1149 l_payee_rec.org_id := br_rem_info.org_id;
1150 l_payee_rec.org_type := 'OPERATING_UNIT' ; -- ( HR_ORGANIZATION_UNITS )
1151
1152
1153 -- set up payer (=customer) record:
1154
1155 l_payer_rec.Payment_Function := 'CUSTOMER_PAYMENT';
1156 l_payer_rec.Party_Id := br_rem_info.party_id; -- receipt customer party id mandatory
1157 l_payer_rec.org_id := br_rem_info.org_id ;
1158 l_payer_rec.org_type := 'OPERATING_UNIT';
1159 l_payer_rec.Cust_Account_Id := br_rem_info.drawee_id; -- receipt customer account_id
1160 l_payer_rec.Account_Site_Id := br_rem_info.drawee_site_use_id; -- receipt customer site_id
1161
1162
1163 if br_rem_info.drawee_site_use_id is NULL THEN
1164
1165 l_payer_rec.org_id := NULL;
1166 l_payer_rec.org_type := NULL;
1167
1168 end if;
1169
1170 -- set up trxn_attribs record:
1171 l_trxn_attribs_rec.Originating_Application_Id := arp_standard.application_id;
1172 l_trxn_attribs_rec.order_id := br_rem_info.trx_number;
1173 l_trxn_attribs_rec.Trxn_Ref_Number1 := 'BRINVOICE';
1174 l_trxn_attribs_rec.Trxn_Ref_Number2 := br_rem_info.customer_trx_id;
1175
1176 -- set up auth_attribs record:
1177 l_auth_attribs_rec.RiskEval_Enable_Flag := 'N';
1178
1179 -- set up amounts
1180
1181 l_amount_rec.value := br_rem_info.br_amount;
1182 l_amount_rec.currency_code := br_rem_info.invoice_currency_code;
1183
1184
1185 -- assign the value for payment_trxn_extension record
1186
1187 l_trxn_entity_id := br_rem_info.payment_trxn_extension_id;
1188
1189
1190
1191 IF PG_DEBUG in ('Y', 'C') THEN
1192 FND_FILE.PUT_LINE(FND_FILE.LOG, 'check and then call Auth');
1193 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Calling get auth for pmt_trxn_extn_id ');
1194 FND_FILE.PUT_LINE(FND_FILE.LOG, 'l_trxn_entity_id ' || to_char(l_trxn_entity_id ) );
1195
1196 END IF;
1197
1198 IF l_trxn_entity_id IS NOT NULL THEN
1199
1200
1201 l_call_settlement := 'Y';
1202
1203 Begin
1204 SELECT decode(summ.status, NULL, 'N', 'Y') AUTHORIZED_FLAG
1205 into l_auth_flag
1206 FROM iby_trxn_summaries_all summ,
1207 iby_fndcpt_tx_operations op
1208 WHERE summ.transactionid = op.transactionid
1209 AND reqtype = 'ORAPMTREQ'
1210 AND status IN(0, 100)
1211 AND trxntypeid IN(2, 3, 20)
1212 AND op.trxn_extension_id = l_trxn_entity_id
1213 AND summ.trxnmid =
1214 (SELECT MAX(trxnmid)
1215 FROM iby_trxn_summaries_all
1216 WHERE transactionid = summ.transactionid
1217 AND reqtype = 'ORAPMTREQ'
1218 AND status IN(0, 100)
1219 AND trxntypeid IN(2, 3, 20));
1220 Exception
1221 when others then
1222 l_auth_flag := 'N';
1223 End;
1224
1225 arp_standard.debug ( 'the value of auth_flag is = ' || l_auth_flag);
1226
1227 If l_auth_flag = 'Y' then
1228 arp_standard.debug ( 'the value of auth_flag is = ' || l_auth_flag);
1229
1230 select AUTHORIZATION_ID
1231 into l_auth_id
1232 from IBY_TRXN_EXT_AUTHS_V
1233 where TRXN_EXTENSION_ID = l_trxn_entity_id;
1234
1235 update ra_customer_trx
1236 set approval_code = 'AR'||to_char(l_auth_id)
1237 where customer_trx_id = br_rem_info.customer_trx_id ;
1238
1239
1240 end if;
1241
1242 IF l_auth_flag <> 'Y' then
1243 FND_FILE.PUT_LINE(FND_FILE.LOG,'auth needs to called');
1244
1245 IF PG_DEBUG in ('Y', 'C') THEN
1246 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Calling get auth for pmt_trxn_extn_id ');
1247 FND_FILE.PUT_LINE(FND_FILE.LOG, ' l_payee_rec.org_id ' || to_char(l_payee_rec.org_id) );
1248 FND_FILE.PUT_LINE(FND_FILE.LOG, ' l_payee_rec.org_type ' || to_char( l_payee_rec.org_type) );
1249 FND_FILE.PUT_LINE(FND_FILE.LOG, ' l_payer_rec.Payment_Function ' || to_char( l_payer_rec.Payment_Function) );
1250 FND_FILE.PUT_LINE(FND_FILE.LOG, ' l_payer_rec.Party_Id ' || to_char( l_payer_rec.Party_Id) );
1251 FND_FILE.PUT_LINE(FND_FILE.LOG, ' l_payer_rec.org_id ' || to_char(l_payer_rec.org_id) );
1252 FND_FILE.PUT_LINE(FND_FILE.LOG, ' l_payer_rec.org_type ' || to_char( l_payer_rec.org_type) );
1253 FND_FILE.PUT_LINE(FND_FILE.LOG, 'l_payer_rec.Cust_Account_Id ' || to_char(l_payer_rec.Cust_Account_Id) );
1254 FND_FILE.PUT_LINE(FND_FILE.LOG, 'l_payer_rec.Account_Site_Id ' || to_char(l_payer_rec.Account_Site_Id) );
1255 FND_FILE.PUT_LINE(FND_FILE.LOG, 'l_trxn_entity_id ' || to_char(l_trxn_entity_id ) );
1256 FND_FILE.PUT_LINE(FND_FILE.LOG, 'l_amount_rec.value: ' || to_char(l_amount_rec.value) );
1257 FND_FILE.PUT_LINE(FND_FILE.LOG, 'l_amount_rec.currency_code: ' || l_amount_rec.currency_code );
1258 FND_FILE.PUT_LINE(FND_FILE.LOG, 'l_auth_attribs_rec.RiskEval_Enable_Flag: ' || l_auth_attribs_rec.RiskEval_Enable_Flag);
1259
1260 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Calling get_auth for pmt_trxn_extn_id ');
1261 END IF;
1262
1263
1264 IBY_FNDCPT_TRXN_PUB.Create_Authorization(
1265 p_api_version => 1.0,
1266 p_init_msg_list => FND_API.G_TRUE,
1267 x_return_status => l_return_status,
1268 x_msg_count => l_msg_count,
1269 x_msg_data => l_msg_data,
1270 p_payer => l_payer_rec,
1271 p_payer_equivalency => IBY_FNDCPT_COMMON_PUB.G_PAYER_EQUIV_UPWARD,
1272 p_payee => l_payee_rec,
1273 p_trxn_entity_id => l_trxn_entity_id,
1274 p_auth_attribs => l_auth_attribs_rec,
1275 p_amount => l_amount_rec,
1276 x_auth_result => l_authresult_rec, -- out auth result struct
1277 x_response => l_response_rec ); -- out response struct
1278
1279
1280 x_msg_count := l_msg_count;
1281 x_msg_data := l_msg_data;
1282
1283 FND_FILE.PUT_LINE(FND_FILE.LOG,'x_return_status :<' || l_return_status || '>');
1284 FND_FILE.PUT_LINE(FND_FILE.LOG,'x_msg_count :<' || l_msg_count || '>');
1285
1286 FOR i IN 1..l_msg_count LOOP
1287 FND_FILE.PUT_LINE(FND_FILE.LOG,'x_msg #' || TO_CHAR(i) || ' = <' ||
1288 SUBSTR(fnd_msg_pub.get(p_msg_index => i,p_encoded => FND_API.G_FALSE),1,150) || '>');
1289 END LOOP;
1290
1291 IF PG_DEBUG in ('Y', 'C') THEN
1292 FND_FILE.PUT_LINE(FND_FILE.LOG, '-------------------------------------');
1293 FND_FILE.PUT_LINE(FND_FILE.LOG, 'l_response_rec.Result_Code: ' || l_response_rec.Result_Code);
1294 FND_FILE.PUT_LINE(FND_FILE.LOG, 'l_response_rec.Result_Category: ' || l_response_rec.Result_Category);
1295 FND_FILE.PUT_LINE(FND_FILE.LOG, 'l_response_rec.Result_message : ' || l_response_rec.Result_message );
1296 FND_FILE.PUT_LINE(FND_FILE.LOG, 'l_authresult_rec.Auth_Id: ' || l_authresult_rec.Auth_Id);
1297 FND_FILE.PUT_LINE(FND_FILE.LOG, 'l_authresult_rec.Auth_Date: ' || l_authresult_rec.Auth_Date);
1298 FND_FILE.PUT_LINE(FND_FILE.LOG, 'l_authresult_rec.Auth_Code: ' || l_authresult_rec.Auth_Code);
1299 FND_FILE.PUT_LINE(FND_FILE.LOG, 'l_authresult_rec.AVS_Code: ' || l_authresult_rec.AVS_Code);
1300 FND_FILE.PUT_LINE(FND_FILE.LOG, 'l_authresult_rec.PaymentSys_Code: ' || l_authresult_rec.PaymentSys_Code);
1301 FND_FILE.PUT_LINE(FND_FILE.LOG, 'l_authresult_rec.PaymentSys_Msg: ' || l_authresult_rec.PaymentSys_Msg);
1302 -- FND_FILE.PUT_LINE(FND_FILE.LOG, 'l_authresult_rec.Risk_Result: ' || l_authresult_rec.Risk_Result);
1303
1304 END IF;
1305
1306 IF (l_return_status = FND_API.G_RET_STS_SUCCESS) THEN
1307 -- update trx record with authorization code
1308
1309 update ra_customer_trx
1310 set approval_code = l_authresult_rec.Auth_code ||'AR'||to_char(l_authresult_rec.Auth_Id)
1311 where customer_trx_id = br_rem_info.customer_trx_id ;
1312
1313 IF PG_DEBUG in ('Y', 'C') THEN
1314 FND_FILE.PUT_LINE(FND_FILE.LOG,'TRX updated with auth_id and auth code ');
1315 END IF;
1316
1317 END IF;
1318
1319
1320 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1321
1322 FND_FILE.PUT_LINE(FND_FILE.LOG,'in AR AUTH FAILED.');
1323 FND_MESSAGE.set_name('AR', 'AR_CC_AUTH_FAILED');
1324 FND_MSG_PUB.Add;
1325
1326 IF l_response_rec.Result_Code is NOT NULL THEN
1327
1328 ---Raise the PAYMENT error code concatenated with the message
1329
1330 l_iby_msg_data := substrb( l_response_rec.Result_Code || ': '|| l_response_rec.Result_Message , 1, 240);
1331
1332 FND_FILE.PUT_LINE(FND_FILE.LOG, 'l_iby_msg_data: ' || l_iby_msg_data);
1333 FND_MESSAGE.SET_NAME ('AR','GENERIC_MESSAGE');
1334 FND_MESSAGE.SET_TOKEN('GENERIC_TEXT',l_iby_msg_data);
1335
1336 FND_MSG_PUB.Add;
1337
1338 END IF;
1339
1340 IF l_authresult_rec.PaymentSys_Code is not null THEN
1341
1342 ---Raise the VENDOR error code concatenated with the message
1343
1344 l_vend_msg_data := substrb(l_authresult_rec.PaymentSys_Code || ': '||
1345 l_authresult_rec.PaymentSys_Msg , 1, 240 );
1346
1347 FND_MESSAGE.SET_NAME ('AR','GENERIC_MESSAGE');
1348 FND_MESSAGE.SET_TOKEN('GENERIC_TEXT',l_vend_msg_data);
1349
1350 FND_MSG_PUB.Add;
1351
1352 END IF;
1353 FND_MSG_PUB.Count_And_Get( p_encoded => FND_API.G_FALSE,
1354 p_count => x_msg_count,
1355 p_data => x_msg_data );
1356
1357 x_return_status := l_return_status;
1358 RETURN;
1359
1360 END IF; /* End the error handling CREATE */
1361
1362 END IF; /* END if of auth flag N */
1363
1364
1365 END IF ; /* l_trxn_entity_id IS NOT NULL*/
1366
1367 END LOOP ; /* if cursor found */
1368
1369
1370 /* NOW WE HAVE TO CALL BULK SETTLEMENT START */
1371
1372 IF l_call_settlement = 'Y' THEN
1373
1374 l_program_application_id := arp_standard.application_id ;
1375
1376
1377 FND_FILE.PUT_LINE(FND_FILE.LOG, 'CALL THE SETTLEMENT API');
1378 IF PG_DEBUG in ('Y', 'C') THEN
1379 fnd_file.put_line(FND_FILE.LOG, 'Calling bulk settlement');
1380 fnd_file.put_line(FND_FILE.LOG, ' p_calling_app_request_code ' || to_char( p_batch_id ) );
1381 fnd_file.put_line(FND_FILE.LOG, 'p_order_view_name ' || 'ARBR_FUNDS_CAPTURE_ORDERS_V' );
1382 END IF;
1383
1384 IBY_FNDCPT_TRXN_PUB.Create_Settlements(
1385 p_api_version => 1.0,
1386 p_init_msg_list => FND_API.G_TRUE,
1387 p_calling_app_id => l_program_application_id,
1388 p_calling_app_request_code => 'ARBR_'||p_batch_id,
1389 p_order_view_name => 'ARBR_FUNDS_CAPTURE_ORDERS_V',
1390 x_return_status => ls_return_status,
1391 x_msg_count => ls_msg_count,
1392 x_msg_data => ls_msg_data,
1393 x_responses => ls_response_rec_tab );
1394
1395
1396 fnd_file.put_line(FND_FILE.LOG,'x_return_status :<' || ls_return_status || '>');
1397 fnd_file.put_line(FND_FILE.LOG,'x_msg_count :<' || ls_msg_count || '>');
1398
1399 FOR i IN 1..ls_msg_count LOOP
1400 fnd_file.put_line(FND_FILE.LOG,'x_msg #' || TO_CHAR(i) || ' = <' ||
1401 SUBSTR(fnd_msg_pub.get(p_msg_index => i,p_encoded => FND_API.G_FALSE),1,150) || '>');
1402 END LOOP;
1403
1404 IF PG_DEBUG in ('Y' , 'C') THEN
1405
1406 FOR i IN ls_response_rec_tab.FIRST..ls_response_rec_tab.LAST LOOP
1407
1408 fnd_file.put_line(FND_FILE.LOG, '--------- START -----------------');
1409 fnd_file.put_line(FND_FILE.LOG, 'ls_response_rec.Trxn_Extension_Id : ' || ls_response_rec_tab(i).Trxn_extension_id);
1410 fnd_file.put_line (FND_FILE.LOG, 'ls_response_rec.Result.Result_code : ' || ls_response_rec_tab(i).Result.Result_code);
1411 fnd_file.put_line (FND_FILE.LOG, 'ls_response_rec.Result.Result_Category : ' || ls_response_rec_tab(i).Result.Result_Category);
1412 fnd_file.put_line (FND_FILE.LOG, 'ls_response_rec.Result.Result_Message : ' || ls_response_rec_tab(i).Result.Result_Message);
1413 fnd_file.put_line(FND_FILE.LOG, '--------- END -----------------');
1414
1415 END LOOP;
1416
1417 END IF;
1418 IF (l_return_status = FND_API.G_RET_STS_SUCCESS) THEN
1419
1420 FOR i IN ls_response_rec_tab.FIRST..ls_response_rec_tab.LAST LOOP
1421
1422 fnd_file.put_line(FND_FILE.LOG,'the value of ls_response_rec.Trxn_Extension_Id = ' || (ls_response_rec_tab(i).Trxn_Extension_Id ));
1423 END LOOP;
1424
1425 END IF;
1426
1427
1428
1429 FOR i IN ls_response_rec_tab.FIRST..ls_response_rec_tab.LAST LOOP
1430
1431
1432 IF ls_response_rec_tab(i).Result.Result_code in ( 'SETTLEMENT_SUCCESS','SETTLEMENT_PENDING') THEN
1433
1434 fnd_file.put_line(FND_FILE.LOG,'SETTLEMENT SUCCESS FOR Trxn_Extension_Id = '
1435 || (ls_response_rec_tab(i).Trxn_Extension_Id ));
1436
1437
1438 ELSE
1439 ls_iby_msg_data := null; /* initialize here */
1440
1441 FND_MESSAGE.set_name('AR', 'AR_CC_CAPTURE_FAILED');
1442 FND_MSG_PUB.Add;
1443 ---Raise the PAYMENT error code concatenated with the message
1444
1445 ls_iby_msg_data := substrb( ls_response_rec_tab(i).Result.Result_Code || ': '||
1446 ls_response_rec_tab(i).Result.Result_Message , 1, 240);
1447
1448 fnd_file.put_line(FND_FILE.LOG, 'ls_iby_msg_data: ' || ls_iby_msg_data);
1449 FND_MESSAGE.SET_NAME ('AR','GENERIC_MESSAGE');
1450 FND_MESSAGE.SET_TOKEN('GENERIC_TEXT',l_iby_msg_data);
1451
1452 FND_MSG_PUB.Add;
1453
1454 END IF;
1455 END LOOP;
1456
1457 END IF; /* l_call_settlemnt */
1458
1459
1460
1461
1462
1463
1464 /* SETTLEMENT END */
1465
1466 EXCEPTION
1467 WHEN others THEN
1468 IF PG_DEBUG in ('Y', 'C') THEN
1469 arp_util.debug('Exception : process_br_payment() '|| SQLERRM );
1470 END IF;
1471
1472
1473 END process_br_payment;
1474
1475
1476
1477
1478 END ARP_PROGRAM_BR_REMIT;