[Home] [Help]
PACKAGE BODY: APPS.AR_AUTOREC_API
Source
1 PACKAGE BODY AR_AUTOREC_API AS
2 /* $Header: ARATRECB.pls 120.133.12020000.4 2013/02/23 12:31:40 vpotti ship $ */
3
4 PG_DEBUG varchar2(1) := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
5
6 PG_PARALLEL varchar2(1) := NVL(FND_PROFILE.value('AR_USE_PARALLEL_HINT'), 'N');
7
8 G_ERROR varchar2(1) := 'N';
9
10 PROCEDURE CONTROL_CHECK ( p_batch_id ar_batches.batch_id%TYPE,p_gt_id NUMBER);
11
12 g_auth_fail varchar2(1) := 'N';
13
14 PROCEDURE SUBMIT_FORMAT ( p_batch_id ar_batches.batch_id%TYPE);
15
16 PROCEDURE process_selected_receipts(
17 p_receipt_method_id in ar_cash_receipts.receipt_method_id%type default null,
18 p_batch_id in ar_batches.batch_id%type,
19 p_approval_mode IN VARCHAR2 DEFAULT 'APPROVE'
20 );
21
22 g_rcpt_info_rec receipt_info_rec;
23
24
25 g_rcpt_creation_rec rcpt_creation_info;
26
27 g_approve_flag ar_cash_receipts.confirmed_flag%TYPE ;
28 g_format_flag ar_cash_receipts.confirmed_flag%TYPE ;
29 g_create_flag ar_cash_receipts.confirmed_flag%TYPE ;
30
31 g_party_id NUMBER;
32 g_pmt_channel_code VARCHAR2(30);
33 g_assignment_id NUMBER;
34 g_auth_flag VARCHAR2(30);
35 g_batch_id NUMBER;
36 /*-----------------------------------------------------------------------+
37 | Default bulk fetch size, and starting index |
38 +-----------------------------------------------------------------------*/
39 MAX_ARRAY_SIZE BINARY_INTEGER := 1000 ;
40 STARTING_INDEX CONSTANT BINARY_INTEGER := 1;
41
42 /* Bug 8903995 */
43 pg_request_id NUMBER;
44 pg_last_updated_by NUMBER;
45 pg_created_by NUMBER;
46 pg_last_update_login NUMBER;
47 pg_program_application_id NUMBER;
48 pg_program_id NUMBER;
49
50
51 PROCEDURE process_events(p_gt_id NUMBER,
52 p_batch_id NUMBER) IS
53
54 l_xla_ev_rec arp_xla_events.xla_events_type;
55 l_from_doc_id NUMBER;
56 l_to_doc_id NUMBER;
57 l_from_ra_doc_id NUMBER;
58 l_to_ra_doc_id NUMBER;
59
60 l_paying_customer_id NUMBER;
61 l_reqid NUMBER;
62
63 BEGIN
64
65 IF PG_DEBUG in ('Y', 'C') THEN
66 arp_debug.debug('process_events()+');
67 END IF;
68
69 update ar_cash_receipts
70 SET creation_date = sysdate,
71 created_by = pg_created_by,
72 last_update_date = sysdate,
73 last_updated_by = pg_created_by,
74 last_update_login = pg_last_update_login,
75 request_id = pg_request_id,
76 program_application_id = pg_program_application_id,
77 program_id = pg_program_id,
78 program_update_date = sysdate
79 WHERE cash_receipt_id in
80 ( select cash_receipt_id
81 from AR_RECEIPTS_GT
82 where gt_id = p_gt_id );
83
84 IF PG_DEBUG in ('Y','C') THEN
85 arp_debug.debug ( 'NO of Receipts updated = '|| to_char(SQL%ROWCOUNT));
86 END IF;
87
88 update ar_cash_receipt_history SET
89 batch_id = p_batch_id,
90 created_by = pg_created_by,
91 last_update_date = sysdate,
92 last_updated_by = pg_created_by,
93 last_update_login = pg_last_update_login,
94 request_id = pg_request_id,
95 program_application_id = pg_program_application_id,
96 program_id = pg_program_id,
97 program_update_date = sysdate
98 WHERE cash_receipt_id in
99 ( select cash_receipt_id
100 from AR_RECEIPTS_GT
101 where gt_id = p_gt_id );
102
103 IF PG_DEBUG in ('Y','C') THEN
104 arp_debug.debug ( 'NO of Receipts updated CRH = '|| to_char(SQL%ROWCOUNT));
105 END IF;
106
107 update AR_payment_schedules SET
108 created_by = pg_created_by,
109 last_update_date = sysdate,
110 last_updated_by = pg_created_by,
111 last_update_login = pg_last_update_login,
112 request_id = pg_request_id,
113 program_application_id = pg_program_application_id,
114 program_id = pg_program_id,
115 program_update_date = sysdate
116 WHERE cash_receipt_id in
117 ( select cash_receipt_id
118 from AR_RECEIPTS_GT
119 where gt_id = p_gt_id );
120
121 IF PG_DEBUG in ('Y','C') THEN
122 arp_debug.debug ( 'NO of Receipts updated PS = '|| to_char(SQL%ROWCOUNT));
123 END IF;
124
125 update ar_receivable_applications SET
126 created_by = pg_created_by,
127 last_update_date = sysdate,
128 last_updated_by = pg_created_by,
129 last_update_login = pg_last_update_login,
130 request_id = pg_request_id,
131 program_application_id = pg_program_application_id,
132 program_id = pg_program_id,
133 program_update_date = sysdate
134 WHERE cash_receipt_id in
135 ( select cash_receipt_id
136 from AR_RECEIPTS_GT
137 where gt_id = p_gt_id );
138
139 /* UPDATING INVOICE PS 7271561*/
140 /* Bug 10137089 : Do not update receipt_batch_id
141 if require confirm flag is checked on receipt class */
142 update ar_payment_schedules
143 set selected_for_receipt_batch_id = NULL
144 where payment_schedule_id in
145 ( select /*+ unnest */ r.payment_schedule_id
146 from ar_receipts_gt r,
147 ar_receivable_applications ra,
148 ra_customer_trx ct,
149 ar_receipt_methods rm,
150 ar_receipt_classes rc
151 where r.gt_id = p_gt_id
152 and ra.applied_customer_trx_id = r.customer_trx_id
153 and ra.request_id = pg_request_id
154 and ra.status = 'APP'
155 and ct.customer_trx_id = r.customer_trx_id
156 and ct.receipt_method_id = rm.receipt_method_id
157 and rm.receipt_class_id = rc.receipt_class_id
158 and rc.confirm_flag = 'N'
159 UNION ALL
160 select r.payment_schedule_id
161 from ar_receipts_gt r,
162 ra_customer_trx ct
163 where r.gt_id = p_gt_id
164 and ct.customer_trx_id = r.customer_trx_id
165 and ct.cc_error_flag = 'Y'
166 );
167
168 IF PG_DEBUG in ('Y','C') THEN
169 arp_debug.debug ( 'NO of RA updated = '|| to_char(SQL%ROWCOUNT));
170 END IF;
171
172 select min(gt.cash_receipt_id),
173 max(gt.cash_receipt_id),
174 min(ra.receivable_application_id),
175 max(ra.receivable_application_id)
176 into l_from_doc_id,
177 l_to_doc_id,
178 l_from_ra_doc_id,
179 l_to_ra_doc_id
180 from AR_RECEIPTS_GT gt,
181 ar_receivable_applications ra
182 where gt.cash_receipt_id = ra.cash_receipt_id
183 and gt_id = p_gt_id;
184
185 IF PG_DEBUG in ('Y','C') THEN
186 arp_debug.debug ( 'Calling XLA event creation procedures for');
187 arp_debug.debug ( 'xla_req_id '||pg_request_id);
188 arp_debug.debug ( 'xla_from_doc_id '||l_from_doc_id);
189 arp_debug.debug ( 'xla_to_doc_id '||l_to_doc_id);
190 END IF;
191
192 /* Create events for the receipts associated to this request id and given range*/
193 l_xla_ev_rec.xla_doc_table := 'CRHAPP';
194 l_xla_ev_rec.xla_req_id := pg_request_id;
195 l_xla_ev_rec.xla_from_doc_id := l_from_doc_id;
196 l_xla_ev_rec.xla_to_doc_id := l_to_doc_id;
197 l_xla_ev_rec.xla_mode := 'B';
198 l_xla_ev_rec.xla_call := 'C';
199
200 arp_xla_events.Create_Events( l_xla_ev_rec );
201
202 l_xla_ev_rec.xla_doc_table := 'CRH';
203 l_xla_ev_rec.xla_req_id := pg_request_id;
204 l_xla_ev_rec.xla_from_doc_id := l_from_doc_id;
205 l_xla_ev_rec.xla_to_doc_id := l_to_doc_id;
206 l_xla_ev_rec.xla_mode := 'B';
207 l_xla_ev_rec.xla_call := 'D';
208
209 arp_xla_events.Create_Events( l_xla_ev_rec );
210
211 l_xla_ev_rec.xla_doc_table := 'APP';
212 l_xla_ev_rec.xla_req_id := pg_request_id;
213 l_xla_ev_rec.xla_from_doc_id := l_from_ra_doc_id;
214 l_xla_ev_rec.xla_to_doc_id := l_to_ra_doc_id;
215 l_xla_ev_rec.xla_mode := 'B';
216 l_xla_ev_rec.xla_call := 'D';
217
218 arp_xla_events.Create_Events( l_xla_ev_rec );
219
220 Select min(ra.receivable_application_id),
221 max(ra.receivable_application_id)
222 into l_from_ra_doc_id,
223 l_to_ra_doc_id
224 from AR_RECEIPTS_GT gt,
225 ar_receivable_applications ra
226 where gt.cash_receipt_id = ra.cash_receipt_id
227 and gt_id = p_gt_id
228 and ra.event_id is null ;
229
230 IF l_from_ra_doc_id IS NOT NULL THEN
231
232 IF PG_DEBUG in ('Y','C') THEN
233 arp_debug.debug ( 'Calling XLA event creation procedure again in O mode');
234 arp_debug.debug ( 'xla_req_id '||pg_request_id);
235 arp_debug.debug ( 'xla_from_doc_id '||l_from_doc_id);
236 arp_debug.debug ( 'xla_to_doc_id '||l_to_doc_id);
237 END IF;
238
239 l_xla_ev_rec.xla_doc_table := 'APP';
240 l_xla_ev_rec.xla_req_id := pg_request_id;
241 l_xla_ev_rec.xla_from_doc_id := l_from_ra_doc_id;
242 l_xla_ev_rec.xla_to_doc_id := l_to_ra_doc_id;
243 l_xla_ev_rec.xla_mode := 'O';
244 l_xla_ev_rec.xla_call := 'B';
245
246 arp_xla_events.Create_Events( l_xla_ev_rec );
247
248 END IF;
249
250 IF PG_DEBUG in ('Y', 'C') THEN
251 arp_debug.debug('process_events()-');
252 END IF;
253
254 EXCEPTION
255 WHEN others THEN
256 arp_debug.debug('Exception : process_events() '|| SQLERRM);
257
258 insert_exceptions( p_batch_id => p_batch_id,
259 p_request_id => pg_request_id,
260 p_exception_code => 'AUTORECERR',
261 p_additional_message => 'process_events() '|| SQLERRM );
262
263 END process_events;
264
265
266 PROCEDURE dump_ar_receipts_gt IS
267 BEGIN
268 FOR rec IN (select * from ar_receipts_gt) LOOP
269 arp_debug.debug( '------------------------------------------------------');
270 arp_debug.debug( 'PAYMENT_SCHEDULE_ID :'|| rec.payment_schedule_id );
271 arp_debug.debug( 'CUSTOMER_TRX_ID :'|| rec.customer_trx_id );
272 arp_debug.debug( 'CASH_RECEIPT_ID :'|| rec.cash_receipt_id);
273 arp_debug.debug( 'PAYING_CUSTOMER_ID :'|| rec.paying_customer_id);
274 arp_debug.debug( 'PAYING_SITE_USE_ID :'|| rec.paying_site_use_id);
275 arp_debug.debug( 'PAYMENT_TRXN_EXTENSION_ID :'|| rec.payment_trxn_extension_id);
276 arp_debug.debug( 'DUE_DATE :'|| rec.due_date);
277 arp_debug.debug( 'AMOUNT_DUE_REMAINING :'|| rec.amount_due_remaining);
278 arp_debug.debug( 'CUSTOMER_BANK_ACCOUNT_ID :'|| rec.customer_bank_account_id);
279 arp_debug.debug( 'CUST_MIN_AMOUNT :'|| rec.cust_min_amount);
280 arp_debug.debug( 'RECEIPT_NUMBER :'|| rec.receipt_number);
281 arp_debug.debug( 'PAYMENT_CHANNEL_CODE :'|| rec.payment_channel_code);
282 arp_debug.debug( 'PAYMENT_INSTRUMENT :'|| rec.payment_instrument);
283 arp_debug.debug( 'AUTHORIZATION_ID :'|| rec.authorization_id);
284 arp_debug.debug( 'GT_ID :'|| rec.gt_id);
285 END LOOP;
286
287 END dump_ar_receipts_gt;
288
289
290 PROCEDURE process_incomplete_receipts(p_batch_id NUMBER) IS
291 l_receipt_method_id ar_batches.receipt_method_id%type;
292
293 BEGIN
294 IF PG_DEBUG in ('Y', 'C') THEN
295 arp_debug.debug('process_incomplete_receipts()+');
296 arp_debug.debug('p_batch_id '|| p_batch_id );
297 END IF;
298
299 select receipt_method_id
300 into l_receipt_method_id
301 from ar_batches
302 where batch_id = p_batch_id;
303
304 --process partial receipts created during previous run
305 process_selected_receipts( p_receipt_method_id => l_receipt_method_id,
306 p_batch_id => p_batch_id,
307 p_approval_mode => 'RE-APPROVAL');
308
309
310 --nullify the batch_id for closed invoices
311 UPDATE ar_payment_schedules
312 SET selected_for_receipt_batch_id = null
313 WHERE selected_for_receipt_batch_id = p_batch_id
314 AND status = 'CL';
315
316 --cleanup gt table
317 delete
318 from ar_receipts_gt;
319
320 IF PG_DEBUG in ('Y', 'C') THEN
321 arp_debug.debug('process_incomplete_receipts()-');
322 END IF;
323 END process_incomplete_receipts;
324
325
326
327 /*========================================================================+
328 | PUBLIC PROCEDURE GET_PARAMETERS |
329 | |
330 | DESCRIPTION |
331 | |
332 | This procedure is used to get the parameters from the Conc program |
333 | and convert them to the type reqd for processing. |
334 | |
335 | PSEUDO CODE/LOGIC |
336 | |
337 | PARAMETERS |
338 | |
339 | |
340 | KNOWN ISSUES |
341 | |
342 | NOTES |
343 | |
344 | |
345 | MODIFICATION HISTORY |
346 | Date Author Description of Changes |
347 | 16-JUL-2005 bichatte Created |
348 | 14-JAN-2010 MRAYMOND 9214328 - problems with
349 | batch locking
350 *=========================================================================*/
351 PROCEDURE get_parameters(
352 P_ERRBUF OUT NOCOPY VARCHAR2,
353 P_RETCODE OUT NOCOPY NUMBER,
354 p_process_type IN VARCHAR2,
355 p_batch_date IN VARCHAR2,
356 p_batch_gl_date IN VARCHAR2,
357 p_create_flag IN VARCHAR2,
358 p_approve_flag IN VARCHAR2,
359 p_format_flag IN VARCHAR2,
360 p_batch_id IN VARCHAR2,
361 p_debug_mode_on IN VARCHAR2,
362 p_batch_currency IN VARCHAR2,
363 p_exchange_date IN VARCHAR2,
364 p_exchange_rate IN VARCHAR2,
365 p_exchange_rate_type IN VARCHAR2,
366 p_remit_method_code IN VARCHAR2,
367 p_receipt_class_id IN VARCHAR2,
368 p_payment_method_id IN VARCHAR2,
369 p_media_reference IN VARCHAR2,
370 p_remit_bank_branch_id IN VARCHAR2,
371 p_remit_bank_account_id IN VARCHAR2,
372 p_remit_bank_deposit_number IN VARCHAR2,
373 p_comments IN VARCHAR2,
374 p_trx_date_l IN VARCHAR2,
375 p_trx_date_h IN VARCHAR2,
376 p_due_date_l IN VARCHAR2,
377 p_due_date_h IN VARCHAR2,
378 p_trx_num_l IN VARCHAR2,
379 p_trx_num_h IN VARCHAR2,
380 p_doc_num_l IN VARCHAR2,
381 p_doc_num_h IN VARCHAR2,
382 p_customer_number_l IN VARCHAR2,
383 p_customer_number_h IN VARCHAR2,
384 p_customer_name_l IN VARCHAR2,
385 p_customer_name_h IN VARCHAR2,
386 p_customer_id IN VARCHAR2,
387 p_site_l IN VARCHAR2,
388 p_site_h IN VARCHAR2,
389 p_site_id IN VARCHAR2,
390 p_remittance_total_from IN VARCHAR2,
391 p_Remittance_total_to IN VARCHAR2,
392 p_billing_number_l IN VARCHAR2,
393 p_billing_number_h IN VARCHAR2,
394 p_customer_bank_acc_num_l IN VARCHAR2,
395 p_customer_bank_acc_num_h IN VARCHAR2,
396 p_current_worker_number IN VARCHAR2 DEFAULT '0',
397 p_total_workers IN VARCHAR2 DEFAULT '0'
398 ) IS
399
400 l_current_worker_number NUMBER;
401 l_total_workers NUMBER;
402 l_request_id ar_cash_receipts.request_id%TYPE;
403 l_gl_date ar_cash_receipt_history.gl_date%TYPE;
404 l_batch_date ar_cash_receipts.receipt_date%TYPE ;
405 l_receipt_class_id ar_receipt_classes.receipt_class_id%TYPE ;
406 l_receipt_method_id ar_cash_receipts.receipt_method_id%TYPE ;
407 l_currency_code ar_cash_receipts.currency_code%TYPE;
408 l_approve_flag ar_cash_receipts.confirmed_flag%TYPE ;
409 l_format_flag ar_cash_receipts.confirmed_flag%TYPE ;
410 l_create_flag ar_cash_receipts.confirmed_flag%TYPE ;
411 o_batch_id NUMBER;
412 l_approve_only_flag VARCHAR2(1);
413 l_batch_app_status VARCHAR2(200);
414
415 l_batch_lock_msg VARCHAR2(200);
416 l_batch_lock_flag BOOLEAN;
417 l_batch_lock_excp EXCEPTION;
418
419 /* selinv variables */
420 op_payment_schedule_id NUMBER;
421 op_customer_trx_id NUMBER;
422 op_cash_receipt_id NUMBER;
423 op_paying_customer_id NUMBER;
424 op_paying_site_use_id NUMBER;
425 op_payment_server_order_num VARCHAR2(200);
426 op_due_date DATE;
427 op_amount_due_remaining VARCHAR2(200);
428 op_cust_bank_account_id NUMBER;
429 op_cust_min_amt VARCHAR2(20);
430 op_return_status VARCHAR2(20);
431 op_payment_trxn_extension_id NUMBER;
432 op_payment_channel_code VARCHAR2(30);
433 op_instrument_type VARCHAR2(30);
434
435 /* selinv variables */
436
437 /* apply variables*/
438 al_return_status VARCHAR2(1);
439 al_msg_count NUMBER;
440 al_msg_data VARCHAR2(240);
441 al_count NUMBER;
442 al_attribute ar_receipt_api_pub.attribute_rec_type;
443 l_called_from VARCHAR2(15);
444
445 CURSOR c2 is
446 select payment_schedule_id,
447 receipt_number rec_num,
448 amount_due_remaining amt
449 from AR_RECEIPTS_GT;
450 /* apply variables */
451
452
453 /* reset variables */
454 l_apply_fail VARCHAR2(1);
455 l_pay_process_fail VARCHAR2(1);
456 l_rec_creation_rule_code ar_receipt_methods.receipt_creation_rule_code%TYPE;
457
458 /* Bug 7639165 - Declaration Begin. */
459 l_instrument_type VARCHAR2(30);
460 /* Bug 7639165 - Declaration End. */
461
462 /* 9214328 */
463 l_error_message fnd_new_messages.message_text%TYPE;
464 BEGIN
465 l_apply_fail := 'N';
466 l_pay_process_fail := 'N';
467
468 IF PG_DEBUG in ('Y', 'C') THEN
469 arp_debug.debug('autorecapi start ()+ ');
470 arp_debug.debug('get_parameters()+ ');
471 arp_debug.debug('p_errbuf ' || P_ERRBUF);
472 arp_debug.debug('p_retcode ' || (P_RETCODE));
473 arp_debug.debug('p_process_type ' || p_process_type);
474 arp_debug.debug('p_create_flag ' || p_create_flag);
475 arp_debug.debug('p_approve_flag ' || p_approve_flag);
476 arp_debug.debug('p_format_flag ' || p_format_flag);
477 arp_debug.debug('p_batch_id ' || (p_batch_id));
478 arp_debug.debug('p_debug_mode_on ' || p_debug_mode_on);
479 arp_debug.debug('p_receipt_class_id ' || p_receipt_class_id);
480 arp_debug.debug('p_payment_method_id ' || p_payment_method_id);
481 arp_debug.debug('p_batch_currency ' || p_batch_currency);
482 arp_debug.debug('p_batch_date ' || p_batch_date);
483 arp_debug.debug('p_batch_gl_date ' || p_batch_gl_date);
484 arp_debug.debug('p_comments ' || p_comments);
485 arp_debug.debug('p_exchnage_date ' || p_exchange_date);
486 arp_debug.debug('p_exchnage_rate ' || p_exchange_rate);
487 arp_debug.debug('p_exchnage_rate_type ' || p_exchange_rate_type);
488 arp_debug.debug('p_media_reference ' || p_media_reference);
489 arp_debug.debug('p_remit_method_code ' || p_remit_method_code);
490 arp_debug.debug('p_remit_bank_branch_id ' || p_remit_bank_branch_id);
491 arp_debug.debug('p_remit_bank_account_id ' || p_remit_bank_account_id);
492 arp_debug.debug('p_remit_bank_deposit_number' || p_remit_bank_deposit_number);
493 arp_debug.debug('p_trx_date_l ' || p_trx_date_l);
494 arp_debug.debug('p_trx_date_h ' || p_trx_date_h);
495 arp_debug.debug('p_due_date_l ' || p_due_date_l);
496 arp_debug.debug('p_due_date_h ' || p_due_date_h);
497 arp_debug.debug('p_trx_num_l ' || p_trx_num_l);
498 arp_debug.debug('p_trx_num_h ' || p_trx_num_h);
499 arp_debug.debug('p_doc_num_l ' || p_doc_num_l);
500 arp_debug.debug('p_doc_num_h ' || p_doc_num_h);
501 arp_debug.debug('p_customer_number_l ' || p_customer_number_l);
502 arp_debug.debug('p_customer_number_h ' || p_customer_number_h);
503 arp_debug.debug('p_customer_name_l ' || p_customer_name_l);
504 arp_debug.debug('p_customer_name_h ' || p_customer_name_h);
505 arp_debug.debug('p_customer_id ' || (p_customer_id));
506 arp_debug.debug('p_site_l ' || p_site_l);
507 arp_debug.debug('p_site_h ' || p_site_h);
508 arp_debug.debug('p_site_id ' || (p_site_id));
509 arp_debug.debug('p_remittance_total_from ' || p_remittance_total_from);
510 arp_debug.debug('p_Remittance_total_to ' || p_Remittance_total_to);
511 arp_debug.debug('p_billing_number_l ' || p_billing_number_l);
512 arp_debug.debug('p_billing_number_h ' || p_billing_number_h);
513 arp_debug.debug('p_customer_bank_acc_num_l ' || p_customer_bank_acc_num_l);
514 arp_debug.debug('p_customer_bank_acc_num_h ' || p_customer_bank_acc_num_h);
515 END IF;
516
517 IF PG_DEBUG in ('Y','C') THEN
518 arp_debug.debug( 'converting the parameters');
519 END IF;
520
521 l_gl_date := fnd_date.canonical_to_date(p_batch_gl_date);
522 l_batch_date := fnd_date.canonical_to_date(p_batch_date);
523 l_receipt_class_id := to_number(p_receipt_class_id);
524 l_receipt_method_id := to_number(p_payment_method_id);
525 l_currency_code := p_batch_currency;
526 l_create_flag := p_create_flag;
527 l_approve_flag := p_approve_flag;
528 l_format_flag := p_format_flag;
529 g_create_flag := p_create_flag;
530 g_approve_flag := p_approve_flag;
531 g_format_flag := p_format_flag;
532
533 -- Start bug 12567069, the Format program will be run if and only if the batch_applied_status = 'COMPLETED_APPROVAL'.
534 -- If not, do not run the format program.
535 IF (l_create_flag = 'Y' AND l_approve_flag = 'N') THEN
536 l_format_flag := 'N';
537 g_format_flag := 'N';
538 END IF;
539 -- End bug 12567069
540
541 l_current_worker_number := to_number(nvl(p_current_worker_number,0));
542 l_total_workers := to_number(nvl(p_total_workers,0));
543
544 /* Bug 8903995 */
545 pg_request_id := arp_standard.profile.request_id;
546 pg_last_updated_by := arp_standard.profile.last_update_login ;
547 pg_created_by := arp_standard.profile.user_id ;
548 pg_last_update_login := arp_standard.profile.last_update_login ;
549 pg_program_application_id := arp_standard.application_id ;
550 pg_program_id := arp_standard.profile.program_id;
551
552 IF PG_DEBUG in ('Y', 'C') THEN
553 arp_debug.debug('pg_request_id ' || pg_request_id);
554 arp_debug.debug('pg_last_updated_by ' || pg_last_updated_by);
555 arp_debug.debug('pg_created_by ' || pg_created_by);
556 arp_debug.debug('pg_last_update_login ' || pg_last_update_login);
557 arp_debug.debug('pg_program_application_id ' || pg_program_application_id);
558 arp_debug.debug('pg_program_id ' || pg_program_id);
559 END IF;
560
561 --fetch the max commit size set for receipts
562 SELECT CASE WHEN (NVL(auto_rec_receipts_per_commit,0) <= 0) THEN 1000
563 ELSE auto_rec_receipts_per_commit END
564 INTO MAX_ARRAY_SIZE
565 FROM ar_system_parameters;
566
567 IF p_batch_id is NULL THEN
568 IF PG_DEBUG in ('Y','C') THEN
569 arp_debug.debug('p_batch_id is null,Calling insert_batch..');
570 END IF;
571
572 /* CALL TO INSERT BATCH FROM MAIN */
573 insert_batch(
574 l_gl_date,
575 l_batch_date,
576 l_receipt_class_id,
577 l_receipt_method_id,
578 l_currency_code,
579 l_approve_flag,
580 l_format_flag,
581 l_create_flag,
582 fnd_date.canonical_to_date(p_exchange_date),
583 to_number(p_exchange_rate),
584 p_exchange_rate_type,
585 o_batch_id
586 );
587
588 IF PG_DEBUG in ('Y','C') THEN
589 arp_debug.debug('Batch got created batch_id:'||o_batch_id);
590 END IF;
591
592 ELSE
593 o_batch_id := p_batch_id;
594
595 IF PG_DEBUG in ('Y','C') THEN
596 arp_debug.debug('Batch id passed,fetching details of batch_id:'|| p_batch_id);
597 END IF;
598
599 select batch_date ,
600 gl_date ,
601 currency_code,
602 receipt_method_id,
603 batch_applied_status
604 into l_batch_date,
605 l_gl_date,
606 l_currency_code,
607 l_receipt_method_id,
608 l_batch_app_status
609 from AR_BATCHES
610 where batch_id = p_batch_id;
611
612 IF PG_DEBUG in ('Y','C') THEN
613 arp_debug.debug('batch_date '|| l_batch_date);
614 arp_debug.debug('gl_date '|| l_gl_date);
615 arp_debug.debug('currency_code '|| l_currency_code);
616 arp_debug.debug('receipt_method_id '|| l_receipt_method_id);
617 arp_debug.debug('l_batch_app_status '|| l_batch_app_status);
618 END IF;
619 END IF;
620
621 g_batch_id := o_batch_id;
622
623 IF o_batch_id is null THEN
624 arp_debug.debug( 'o_batch_id is null This is an error condition');
625
626 insert_exceptions(
627 p_batch_id =>-333,
628 p_request_id =>pg_request_id,
629 p_exception_code => 'NO_BATCH',
630 p_additional_message => 'error during insert batch' );
631
632 IF PG_DEBUG in ('Y','C') THEN
633 arp_debug.debug ( 'calling the report- batch_id ' || -333 );
634 arp_debug.debug ( 'calling the report ' || pg_request_id);
635 END IF;
636
637 submit_report ( p_batch_id => o_batch_id,
638 p_request_id => pg_request_id);
639
640 RETURN;
641 END IF;
642
643 /*lock the batch record to prevent multiple submissions for the same batch while a request
644 is running.In case the request is spanned from master process,master will hold the lock*/
645 IF l_total_workers = 0 AND p_approve_flag = 'Y' THEN
646 l_batch_lock_flag := ARP_RW_BATCHES_PKG.request_lock(o_batch_id ,l_batch_lock_msg);
647
648 IF l_batch_app_status = 'STARTED_APPROVAL' THEN
649 process_incomplete_receipts( o_batch_id );
650 END IF;
651
652 IF l_batch_lock_flag = FALSE THEN
653 RAISE l_batch_lock_excp;
654 END IF;
655 END IF;
656
657 IF p_create_flag = 'Y' THEN
658 l_approve_only_flag := 'N';
659 ELSE
660 l_approve_only_flag := 'A';
661 END IF;
662
663 /**In the case of new batch being created in the current run ,the below logic
664 stamps batch_id on all the selected invoices and populates the data into
665 interim and GT tables.
666 If the batch exists prior to this run then it selects all the invoices
667 associated to the current batch and populates the data into interim and
668 GT tables.
669 This distinction is done based on l_approve_only_flag being passed to
670 procedure select_valid_invoices */
671 IF ( p_create_flag = 'Y' OR
672 p_approve_flag = 'Y' ) AND
673 G_ERROR = 'N' THEN
674
675 IF PG_DEBUG in ('Y','C') THEN
676 arp_debug.debug('l_approve_only_flag :' || l_approve_only_flag);
677 arp_debug.debug('selecting the data for batch_id :' || to_char(o_batch_id));
678 END IF;
679
680 /** If the worker count is other than zero,master program populates the interim
681 * table with the selected data.*/
682 IF l_total_workers = 0 THEN
683 IF PG_DEBUG in ('Y', 'C') THEN
684 arp_debug.debug ( ' l_total_workers :'||l_total_workers);
685 arp_debug.debug ( ' Calling select_valid_invoices..');
686 END IF;
687
688 select_valid_invoices
689 ( p_trx_date_l =>fnd_date.canonical_to_date(p_trx_date_l),
690 p_trx_date_h =>fnd_date.canonical_to_date(p_trx_date_h),
691 p_due_date_l =>fnd_date.canonical_to_date(p_due_date_l),
692 p_due_date_h =>fnd_date.canonical_to_date(p_due_date_h),
693 p_trx_num_l =>p_trx_num_l,
694 p_trx_num_h => p_trx_num_h,
695 p_doc_num_l =>p_doc_num_l,
696 p_doc_num_h => p_doc_num_h,
697 p_customer_number_l => p_customer_number_l,
698 p_customer_number_h => p_customer_number_h,
699 p_customer_name_l => p_customer_name_l,
700 p_customer_name_h => p_customer_name_h,
701 p_batch_id => o_batch_id,
702 p_approve_only_flag => l_approve_only_flag,
703 p_receipt_method_id => l_receipt_method_id,
704 p_total_workers => 1 );
705
706 IF PG_DEBUG in ('Y', 'C') THEN
707 arp_debug.debug ( ' Returned from select_valid_invoices..');
708 END IF;
709 END IF;
710
711 --Populating the data allocated to current worker from interim table to GT
712 insert into ar_receipts_gt(
713 payment_schedule_id,
714 customer_trx_id,
715 cash_receipt_id,
716 paying_customer_id,
717 paying_site_use_id,
718 payment_trxn_extension_id,
719 due_date,
720 amount_due_remaining,
721 customer_bank_account_id,
722 cust_min_amount,
723 receipt_number,
724 payment_channel_code,
725 payment_instrument,
726 authorization_id,
727 gt_id)
728 select distinct payment_schedule_id,
729 customer_trx_id,
730 cash_receipt_id,
731 paying_customer_id,
732 paying_site_use_id,
733 payment_trxn_extension_id,
734 due_date,
735 amount_due_remaining,
736 customer_bank_account_id,
737 cust_min_amount,
738 null,
739 payment_channel_code,
740 payment_instrument,
741 null,
742 null
743 from ar_autorec_interim a
744 where a.worker_id = decode(l_current_worker_number,0,a.worker_id,
745 l_current_worker_number)
746 and a.batch_id = o_batch_id;
747
748 IF PG_DEBUG in ('Y', 'C') THEN
749 arp_debug.debug ( 'NO of rows inserted into ar_receipts_gt :'|| to_char(SQL%ROWCOUNT));
750 END IF;
751
752 IF l_total_workers = 0 THEN
753 IF p_approve_flag = 'Y' THEN
754 UPDATE ar_batches
755 SET batch_applied_status = 'STARTED_APPROVAL'
756 WHERE batch_id = o_batch_id;
757 ELSIF p_create_flag = 'Y' THEN
758 UPDATE ar_batches
759 SET batch_applied_status = 'COMPLETED_CREATION'
760 WHERE batch_id = o_batch_id;
761 END IF;
762
763 --Commit the creation process
764 COMMIT;
765
766 IF PG_DEBUG in ('Y', 'C') THEN
767 arp_debug.debug('Batch status set to COMPLETED_CREATION');
768 arp_debug.debug('Batches updated '||SQL%ROWCOUNT);
769 END IF;
770 END IF;
771
772 END IF;
773
774 --Approval process
775 IF p_approve_flag = 'Y' AND G_ERROR = 'N' THEN
776 IF PG_DEBUG in ('Y','C') THEN
777 arp_debug.debug('Inside approve section ');
778 END IF;
779
780 IF PG_DEBUG in ('Y', 'C') THEN
781 arp_debug.debug('CALLING process_selected_receipts()');
782 END IF;
783
784 process_selected_receipts( p_receipt_method_id => l_receipt_method_id,
785 p_batch_id => o_batch_id,
786 p_approval_mode => 'APPROVAL');
787
788 --If the current run is with only single worker then set the batch status else
789 --this is done in the master program
790 IF l_total_workers = 0 AND G_ERROR = 'N' THEN
791 update ar_batches
792 set batch_applied_status = 'COMPLETED_APPROVAL'
793 where batch_id = o_batch_id;
794
795 IF PG_DEBUG in ('Y', 'C') THEN
796 arp_debug.debug('Batch status set to COMPLETED_APPROVAL ');
797 arp_debug.debug('Batches updated '||SQL%ROWCOUNT);
798 END IF;
799 END IF;
800 END IF;
801
802 /** If the current run is with only single worker then set the batch status and
803 invoke the report */
804 IF l_total_workers = 0 THEN
805 --cleanup the interim table
806 delete
807 from ar_autorec_interim
808 where batch_id = o_batch_id;
809
810 --release the lock on batch record
811 l_batch_lock_flag :=
812 ARP_RW_BATCHES_PKG.release_lock(o_batch_id ,l_batch_lock_msg);
813
814 IF PG_DEBUG in ('Y','C')
815 THEN
816 arp_debug.debug('Releasing lock - ' || l_batch_lock_msg);
817 END IF;
818
819 IF l_format_flag = 'Y' THEN
820 arp_debug.debug('Submitting format report- batch_id :' || o_batch_id);
821 submit_format ( p_batch_id =>o_batch_id);
822 END IF;
823
824 /* SUBMIT THE FINAL REPORT FULL WITH ERRORS AND EXECUTION */
825 IF PG_DEBUG in ('Y', 'C') THEN
826 arp_debug.debug ( 'Submitting report- batch_id :' || o_batch_id);
827 arp_debug.debug ( 'request id : ' || pg_request_id);
828 END IF;
829
830 submit_report ( p_batch_id => o_batch_id,
831 p_request_id => pg_request_id);
832
833 /* Bug 7639165 - Changes Begin. */
834 BEGIN
835 SELECT PC.INSTRUMENT_TYPE
836 INTO l_instrument_type
837 FROM AR_RECEIPT_METHODS RM,
838 IBY_FNDCPT_PMT_CHNNLS_B PC
839 WHERE RECEIPT_METHOD_ID = l_receipt_method_id
840 AND RM.PAYMENT_CHANNEL_CODE = PC.PAYMENT_CHANNEL_CODE;
841
842 EXCEPTION
843 WHEN OTHERS THEN
844 RAISE;
845 END;
846
847 IF nvl(l_instrument_type,'XXXXXX') = 'CREDITCARD' AND
848 nvl(p_approve_flag,'N') = 'Y' THEN
849 arp_debug.debug('Calling ARP_CORRECT_CC_ERRORS.cc_auto_correct_cover');
850 ARP_CORRECT_CC_ERRORS.cc_auto_correct_cover(pg_request_id,'CREATION');
851 END IF;
852 /* Bug 7639165 - Changes End. */
853
854 IF PG_DEBUG in ('Y', 'C') THEN
855 arp_debug.debug ( 'Cleaning interim table Rows deleted:'|| SQL%ROWCOUNT);
856 END IF;
857 END IF;
858
859 arp_debug.debug (' FINALLY COMMITING WORK ');
860 COMMIT;
861
862 arp_debug.debug('get_parameters()-');
863
864 IF G_ERROR = 'Y' THEN
865 P_RETCODE := 1;
866 END IF;
867
868 EXCEPTION
869 WHEN l_batch_lock_excp THEN
870
871 l_error_message := FND_MESSAGE.GET_STRING('AR','AR_UNABLE_LOCK_BATCH');
872
873 IF (FND_CONCURRENT.SET_COMPLETION_STATUS('WARNING', l_error_message) = FALSE)
874 THEN
875 arp_debug.debug('Unable to set WARNING return status');
876 END IF;
877
878 fnd_file.put_line( FND_FILE.LOG,
879 'Not able to lock the batch record, '||
880 'Please try submitting the request again..');
881
882 arp_debug.debug('Exception : Not able to lock the batch record,seems record is '||
883 'already locked by other session ');
884 arp_debug.debug(' batch_id = ' || o_batch_id );
885 arp_debug.debug(' message: ' || l_batch_lock_msg);
886
887 WHEN others THEN
888 --incase of any exception thrown,cleanup the interim table as the next run will
889 --anyway repopulate the needed data
890 IF l_total_workers = 0
891 THEN
892 delete
893 from ar_autorec_interim
894 where batch_id = o_batch_id;
895
896 IF p_approve_flag = 'Y'
897 THEN
898 /* release the lock */
899 l_batch_lock_flag := ARP_RW_BATCHES_PKG.release_lock(o_batch_id ,l_batch_lock_msg);
900
901 IF PG_DEBUG in ('Y','C')
902 THEN
903 arp_debug.debug('Releasing lock - ' || l_batch_lock_msg);
904 END IF;
905 END IF;
906 END IF;
907
908 IF PG_DEBUG in ('Y', 'C') THEN
909 arp_debug.debug('Exception : get_parameters() '|| SQLERRM);
910 END IF;
911 IF G_ERROR = 'Y' THEN
912 P_RETCODE := 1;
913 END IF;
914
915 RAISE;
916 END get_parameters;
917
918
919 /*========================================================================+
920 | PROCEDURE submit_autorec_parallel |
921 | |
922 | DESCRIPTION |
923 | |
924 | Wraper to parallelize the Automatic Receipts creation program |
925 | PSEUDO CODE/LOGIC |
926 | |
927 | PARAMETERS |
928 | |
929 | |
930 | KNOWN ISSUES |
931 | |
932 | NOTES |
933 | |
934 | |
935 | MODIFICATION HISTORY |
936 | Date Author Description of Changes |
937 | 30-NOV-2007 nproddut Created |
938 *=========================================================================*/
939 PROCEDURE submit_autorec_parallel(
940 P_ERRBUF OUT NOCOPY VARCHAR2,
941 P_RETCODE OUT NOCOPY NUMBER,
942 p_process_type IN VARCHAR2,
943 p_batch_date IN VARCHAR2,
944 p_batch_gl_date IN VARCHAR2,
945 p_create_flag IN VARCHAR2,
946 p_approve_flag IN VARCHAR2,
947 p_format_flag IN VARCHAR2,
948 p_batch_id IN VARCHAR2,
949 p_debug_mode_on IN VARCHAR2,
950 p_batch_currency IN VARCHAR2,
951 p_exchange_date IN VARCHAR2,
952 p_exchange_rate IN VARCHAR2,
953 p_exchange_rate_type IN VARCHAR2,
954 p_remit_method_code IN VARCHAR2,
955 p_receipt_class_id IN VARCHAR2,
956 p_payment_method_id IN VARCHAR2,
957 p_media_reference IN VARCHAR2,
958 p_remit_bank_branch_id IN VARCHAR2,
959 p_remit_bank_account_id IN VARCHAR2,
960 p_remit_bank_deposit_number IN VARCHAR2,
961 p_comments IN VARCHAR2,
962 p_trx_date_l IN VARCHAR2,
963 p_trx_date_h IN VARCHAR2,
964 p_due_date_l IN VARCHAR2,
965 p_due_date_h IN VARCHAR2,
966 p_trx_num_l IN VARCHAR2,
967 p_trx_num_h IN VARCHAR2,
968 p_doc_num_l IN VARCHAR2,
969 p_doc_num_h IN VARCHAR2,
970 p_customer_number_l IN VARCHAR2,
971 p_customer_number_h IN VARCHAR2,
972 p_customer_name_l IN VARCHAR2,
973 p_customer_name_h IN VARCHAR2,
974 p_customer_id IN VARCHAR2,
975 p_site_l IN VARCHAR2,
976 p_site_h IN VARCHAR2,
977 p_site_id IN VARCHAR2,
978 p_remittance_total_from IN VARCHAR2,
979 p_Remittance_total_to IN VARCHAR2,
980 p_billing_number_l IN VARCHAR2,
981 p_billing_number_h IN VARCHAR2,
982 p_customer_bank_acc_num_l IN VARCHAR2,
983 p_customer_bank_acc_num_h IN VARCHAR2,
984 p_total_workers IN NUMBER default 1 ) AS
985
986 l_worker_number NUMBER ;
987 l_complete BOOLEAN := FALSE;
988
989 l_request_id ar_cash_receipts.request_id%TYPE;
990 l_gl_date ar_cash_receipt_history.gl_date%TYPE;
991 l_batch_date ar_cash_receipts.receipt_date%TYPE ;
992 l_receipt_class_id ar_receipt_classes.receipt_class_id%TYPE ;
993 l_receipt_method_id ar_cash_receipts.receipt_method_id%TYPE ;
994 l_currency_code ar_cash_receipts.currency_code%TYPE;
995 l_approve_flag ar_cash_receipts.confirmed_flag%TYPE ;
996 l_format_flag ar_cash_receipts.confirmed_flag%TYPE ;
997 l_create_flag ar_cash_receipts.confirmed_flag%TYPE ;
998 l_approve_only_flag ar_cash_receipts.confirmed_flag%TYPE;
999 o_batch_id NUMBER;
1000 l_batch_lock_msg VARCHAR2(200);
1001 l_batch_lock_flag BOOLEAN;
1002 l_batch_lock_excp EXCEPTION;
1003 l_batch_app_status ar_batches.batch_applied_status%TYPE;
1004
1005 /* Bug 7639165 - Declaration Begin. */
1006 l_instrument_type VARCHAR2(30);
1007 /* Bug 7639165 - Declaration End. */
1008
1009 TYPE req_status_typ IS RECORD (
1010 request_id NUMBER(15),
1011 dev_phase VARCHAR2(255),
1012 dev_status VARCHAR2(255),
1013 message VARCHAR2(2000),
1014 phase VARCHAR2(255),
1015 status VARCHAR2(255));
1016 l_org_id NUMBER;
1017
1018 TYPE req_status_tab_typ IS TABLE OF req_status_typ INDEX BY BINARY_INTEGER;
1019
1020 l_req_status_tab req_status_tab_typ;
1021
1022 PROCEDURE submit_subrequest (p_worker_number IN NUMBER,
1023 p_org_id IN NUMBER) IS
1024 l_request_id NUMBER;
1025 BEGIN
1026 arp_debug.debug('submit_subrequest()+');
1027
1028 FND_REQUEST.SET_ORG_ID(p_org_id);
1029
1030 l_request_id := FND_REQUEST.submit_request( 'AR', 'AR_AUTORECAPI',
1031 '',
1032 SYSDATE,
1033 FALSE,
1034 p_process_type ,
1035 p_batch_date ,
1036 p_batch_gl_date ,
1037 p_create_flag ,
1038 p_approve_flag ,
1039 p_format_flag,
1040 to_char(o_batch_id) ,
1041 p_debug_mode_on ,
1042 p_batch_currency,
1043 p_exchange_date,
1044 p_exchange_rate,
1045 p_exchange_rate_type ,
1046 p_remit_method_code,
1047 p_receipt_class_id,
1048 p_payment_method_id,
1049 p_media_reference,
1050 p_remit_bank_branch_id,
1051 p_remit_bank_account_id,
1052 p_remit_bank_deposit_number,
1053 p_comments,
1054 p_trx_date_l,
1055 p_trx_date_h,
1056 p_due_date_l,
1057 p_due_date_h,
1058 p_trx_num_l,
1059 p_trx_num_h,
1060 p_doc_num_l,
1061 p_doc_num_h,
1062 p_customer_number_l,
1063 p_customer_number_h,
1064 p_customer_name_l,
1065 p_customer_name_h,
1066 p_customer_id,
1067 p_site_l,
1068 p_site_h,
1069 p_site_id,
1070 p_remittance_total_from,
1071 p_Remittance_total_to,
1072 p_billing_number_l,
1073 p_billing_number_h,
1074 p_customer_bank_acc_num_l,
1075 p_customer_bank_acc_num_h,
1076 p_worker_number,
1077 p_total_workers );
1078
1079 IF (pg_request_id = 0) THEN
1080 arp_debug.debug('can not start for worker_id: ' ||p_worker_number );
1081 P_ERRBUF := fnd_Message.get;
1082 P_RETCODE := 2;
1083 return;
1084 ELSE
1085 commit;
1086 arp_debug.debug('child request id: ' ||l_request_id || ' started for worker_id: ' ||p_worker_number );
1087 END IF;
1088
1089 l_req_status_tab(p_worker_number).request_id := l_request_id;
1090 arp_debug.debug('submit_subrequest()-');
1091
1092 END submit_subrequest;
1093
1094 BEGIN
1095 fnd_file.put_line( FND_FILE.LOG, 'submit_autorec_parallel()+');
1096 IF PG_DEBUG in ('Y', 'C') THEN
1097 arp_debug.debug('ar_autorec_api.submit_autorec_parallel()+');
1098 END IF;
1099
1100 l_gl_date := fnd_date.canonical_to_date(p_batch_gl_date);
1101 l_batch_date := fnd_date.canonical_to_date(p_batch_date);
1102 l_receipt_class_id := to_number(p_receipt_class_id);
1103 l_receipt_method_id := to_number(p_payment_method_id);
1104 l_currency_code := p_batch_currency;
1105 l_create_flag := p_create_flag;
1106 l_approve_flag := p_approve_flag;
1107 l_format_flag := p_format_flag;
1108
1109 /* Bug 8903995 */
1110 pg_request_id := arp_standard.profile.request_id;
1111 pg_last_updated_by := arp_standard.profile.last_update_login ;
1112 pg_created_by := arp_standard.profile.user_id ;
1113 pg_last_update_login := arp_standard.profile.last_update_login ;
1114 pg_program_application_id := arp_standard.application_id ;
1115 pg_program_id := arp_standard.profile.program_id;
1116
1117 IF PG_DEBUG in ('Y', 'C') THEN
1118 arp_debug.debug('pg_request_id ' || pg_request_id);
1119 arp_debug.debug('pg_last_updated_by ' || pg_last_updated_by);
1120 arp_debug.debug('pg_created_by ' || pg_created_by);
1121 arp_debug.debug('pg_last_update_login ' || pg_last_update_login);
1122 arp_debug.debug('pg_program_application_id ' || pg_program_application_id);
1123 arp_debug.debug('pg_program_id ' || pg_program_id);
1124 arp_debug.debug('l_gl_date ' ||l_gl_date);
1125 arp_debug.debug('l_batch_date ' ||l_batch_date);
1126 arp_debug.debug('l_receipt_class_id ' ||l_receipt_class_id);
1127 arp_debug.debug('l_receipt_method_id ' ||l_receipt_method_id);
1128 arp_debug.debug('l_currency_code ' ||l_currency_code);
1129 arp_debug.debug('l_create_flag ' ||l_create_flag);
1130 arp_debug.debug('l_approve_flag ' ||l_approve_flag);
1131 arp_debug.debug('l_format_flag ' ||l_format_flag);
1132 arp_debug.debug('p_batch_id ' ||p_batch_id);
1133 END IF;
1134
1135 IF p_batch_id IS NOT NULL THEN
1136 l_approve_only_flag := 'A';
1137 ELSIF ( p_create_flag = 'N' AND p_approve_flag = 'Y' AND p_format_flag = 'N') THEN
1138 l_approve_only_flag := 'A';
1139 ELSE
1140 l_approve_only_flag := 'N';
1141 END IF;
1142
1143 --Create a batch if the batch id is null
1144 IF p_batch_id is NULL THEN
1145 insert_batch(l_gl_date,
1146 l_batch_date,
1147 l_receipt_class_id,
1148 l_receipt_method_id,
1149 l_currency_code,
1150 l_approve_flag,
1151 l_format_flag,
1152 l_create_flag,
1153 fnd_date.canonical_to_date(p_exchange_date),
1154 to_number(p_exchange_rate),
1155 p_exchange_rate_type,
1156 o_batch_id
1157 );
1158 ELSE
1159 o_batch_id := p_batch_id;
1160 END IF;
1161
1162 --Error condition
1163 IF o_batch_id IS NULL THEN
1164 arp_debug.debug( 'This is an error condition');
1165 insert_exceptions( p_batch_id => -333,
1166 p_request_id =>pg_request_id,
1167 p_exception_code => 'NO_BATCH',
1168 p_additional_message => 'error during insert batch' );
1169
1170 arp_debug.debug ( 'calling the report- batch_id ' || -333 );
1171 arp_debug.debug ( 'calling the report ' || pg_request_id);
1172
1173 submit_report ( p_batch_id => o_batch_id,
1174 p_request_id => pg_request_id);
1175 RETURN;
1176 END IF;
1177
1178 /*lock the batch record to prevent multiple submissions for the same batch while a request
1179 is running.In case the request is spanned from master process,master will hold the lock*/
1180 l_batch_lock_flag := ARP_RW_BATCHES_PKG.request_lock(o_batch_id ,l_batch_lock_msg);
1181
1182 IF l_batch_lock_flag = FALSE THEN
1183 RAISE l_batch_lock_excp;
1184 END IF;
1185
1186 --fetch org id,need to set it for child requests
1187 l_org_id := mo_global.get_current_org_id;
1188 pg_request_id := arp_standard.profile.request_id;
1189
1190
1191 IF p_approve_flag = 'Y' THEN
1192 BEGIN
1193 SELECT batch_applied_status
1194 INTO l_batch_app_status
1195 FROM ar_batches
1196 WHERE batch_id = o_batch_id;
1197 EXCEPTION
1198 WHEN OTHERS THEN
1199 NULL;
1200 END;
1201
1202 IF PG_DEBUG in ('Y', 'C') THEN
1203 arp_debug.debug('l_batch_app_status :'||l_batch_app_status);
1204 END IF;
1205
1206 IF l_batch_app_status = 'STARTED_APPROVAL' THEN
1207 process_incomplete_receipts( o_batch_id );
1208 END IF;
1209
1210 select_valid_invoices
1211 ( p_trx_date_l =>fnd_date.canonical_to_date(p_trx_date_l),
1212 p_trx_date_h =>fnd_date.canonical_to_date(p_trx_date_h),
1213 p_due_date_l =>fnd_date.canonical_to_date(p_due_date_l),
1214 p_due_date_h =>fnd_date.canonical_to_date(p_due_date_h),
1215 p_trx_num_l =>p_trx_num_l,
1216 p_trx_num_h => p_trx_num_h,
1217 p_doc_num_l =>p_doc_num_l,
1218 p_doc_num_h => p_doc_num_h,
1219 p_customer_number_l => p_customer_number_l,
1220 p_customer_number_h => p_customer_number_h,
1221 p_customer_name_l => p_customer_name_l,
1222 p_customer_name_h => p_customer_name_h,
1223 p_batch_id => o_batch_id,
1224 p_approve_only_flag => l_approve_only_flag,
1225 p_receipt_method_id => l_receipt_method_id,
1226 p_total_workers => p_total_workers
1227 );
1228 END IF;
1229
1230 IF p_approve_flag = 'Y' AND
1231 NVL(l_batch_app_status,'NONE') <> 'STARTED_APPROVAL' THEN
1232 UPDATE ar_batches
1233 SET batch_applied_status = 'STARTED_APPROVAL'
1234 WHERE batch_id = o_batch_id;
1235
1236 ELSIF p_create_flag = 'Y' THEN
1237 UPDATE ar_batches
1238 SET batch_applied_status = 'COMPLETED_CREATION'
1239 WHERE batch_id = o_batch_id;
1240 END IF;
1241
1242 COMMIT;
1243
1244 IF p_approve_flag = 'Y' THEN
1245 --Invoke the child programs
1246 FOR l_worker_number IN 1..p_total_workers LOOP
1247 fnd_file.put_line(FND_FILE.LOG,'worker # : ' || l_worker_number );
1248 submit_subrequest (l_worker_number,l_org_id);
1249 END LOOP;
1250
1251 arp_debug.debug ( 'The Master program waits for child processes');
1252
1253 P_RETCODE := 0;
1254
1255 -- Wait for the completion of the submitted requests
1256 FOR i in 1..p_total_workers LOOP
1257 l_complete := FND_CONCURRENT.WAIT_FOR_REQUEST(
1258 request_id => l_req_status_tab(i).request_id,
1259 interval => 30,
1260 max_wait =>144000,
1261 phase =>l_req_status_tab(i).phase,
1262 status =>l_req_status_tab(i).status,
1263 dev_phase =>l_req_status_tab(i).dev_phase,
1264 dev_status =>l_req_status_tab(i).dev_status,
1265 message =>l_req_status_tab(i).message);
1266
1267 IF l_req_status_tab(i).dev_phase <> 'COMPLETE' THEN
1268 P_RETCODE := 1;
1269 arp_debug.debug('Worker # '|| i||' has a phase '||l_req_status_tab(i).dev_phase);
1270
1271 ELSIF l_req_status_tab(i).dev_phase = 'COMPLETE'
1272 AND l_req_status_tab(i).dev_status <> 'NORMAL' THEN
1273 P_RETCODE := 1;
1274 arp_debug.debug('Worker # '|| i||' completed with status '||l_req_status_tab(i).dev_status);
1275 ELSE
1276 arp_debug.debug('Worker # '|| i||' completed successfully');
1277 END IF;
1278 END LOOP;
1279
1280 IF P_RETCODE = 0 THEN
1281 UPDATE ar_batches
1282 SET batch_applied_status = 'COMPLETED_APPROVAL'
1283 WHERE batch_id = o_batch_id;
1284 END IF;
1285 END IF;
1286
1287 IF l_format_flag = 'Y' THEN
1288 arp_debug.debug('calling the report- batch_id format ' || o_batch_id);
1289 submit_format ( p_batch_id =>o_batch_id);
1290 END IF;
1291
1292 submit_report ( p_batch_id =>o_batch_id,
1293 p_request_id => pg_request_id);
1294
1295 /* Bug 7639165 - Changes Begin. */
1296 BEGIN
1297 SELECT PC.INSTRUMENT_TYPE
1298 INTO l_instrument_type
1299 FROM AR_RECEIPT_METHODS RM,
1300 IBY_FNDCPT_PMT_CHNNLS_B PC
1301 WHERE RECEIPT_METHOD_ID = l_receipt_method_id
1302 AND RM.PAYMENT_CHANNEL_CODE = PC.PAYMENT_CHANNEL_CODE;
1303
1304 EXCEPTION
1305 WHEN OTHERS THEN
1306 RAISE;
1307 END;
1308
1309 IF nvl(l_instrument_type,'XXXXXX') = 'CREDITCARD' AND
1310 nvl(p_approve_flag,'N') = 'Y' THEN
1311 arp_debug.debug('Calling ARP_CORRECT_CC_ERRORS.cc_auto_correct_cover');
1312 ARP_CORRECT_CC_ERRORS.cc_auto_correct_cover(pg_request_id,'CREATION');
1313 END IF;
1314 /* Bug 7639165 - Changes End. */
1315
1316 commit;
1317
1318 --cleanup interim table
1319 delete
1320 from ar_autorec_interim
1321 where batch_id = o_batch_id;
1322
1323 l_batch_lock_flag := ARP_RW_BATCHES_PKG.release_lock( o_batch_id, l_batch_lock_msg);
1324
1325 fnd_file.put_line( FND_FILE.LOG, 'submit_autorec_parallel()-');
1326
1327 EXCEPTION
1328 WHEN OTHERS THEN
1329 --incase of any exception thrown,cleanup the interim table as the next run will
1330 --anyway repopulate the needed data
1331 delete
1332 from ar_autorec_interim
1333 where batch_id = o_batch_id;
1334
1335 --release the lock on batch record
1336 l_batch_lock_flag :=
1337 ARP_RW_BATCHES_PKG.release_lock(o_batch_id ,l_batch_lock_msg);
1338
1339 IF PG_DEBUG in ('Y','C')
1340 THEN
1341 arp_debug.debug('Releasing lock - ' || l_batch_lock_msg);
1342 END IF;
1343
1344 RAISE ;
1345 END submit_autorec_parallel;
1346
1347 /*========================================================================+
1348 | PROCEDURE insert_batch |
1349 | |
1350 | DESCRIPTION |
1351 | |
1352 | This procedure is used to insert the batch record when called from |
1353 | srs. It also gets the other required parameters from sysparm |
1354 | and conc program |
1355 | PSEUDO CODE/LOGIC |
1356 | |
1357 | PARAMETERS |
1358 | |
1359 | |
1360 | KNOWN ISSUES |
1361 | |
1362 | NOTES |
1363 | |
1364 | |
1365 | MODIFICATION HISTORY |
1366 | Date Author Description of Changes |
1367 | 16-JUL-2005 bichatte Created |
1368 *=========================================================================*/
1369
1370
1371 PROCEDURE insert_batch(
1372 p_gl_date IN ar_cash_receipt_history.gl_date%TYPE DEFAULT NULL,
1373 p_batch_date IN ar_cash_receipts.receipt_date%TYPE DEFAULT NULL,
1374 p_receipt_class_id IN ar_receipt_classes.receipt_class_id%TYPE DEFAULT NULL,
1375 p_receipt_method_id IN ar_cash_receipts.receipt_method_id%TYPE DEFAULT NULL,
1376 p_currency_code IN ar_cash_receipts.currency_code%TYPE DEFAULT NULL,
1377 p_approve_flag IN ar_cash_receipts.confirmed_flag%TYPE DEFAULT NULL,
1378 p_format_flag IN ar_cash_receipts.confirmed_flag%TYPE DEFAULT NULL,
1379 p_create_flag IN ar_cash_receipts.confirmed_flag%TYPE DEFAULT NULL,
1380 p_exchange_date IN ar_batches.exchange_date%TYPE DEFAULT NULL,
1381 p_exchange_rate IN ar_batches.exchange_rate%TYPE DEFAULT NULL,
1382 p_exchange_rate_type IN ar_batches.exchange_rate_type%TYPE DEFAULT NULL,
1383 p_batch_id OUT NOCOPY NUMBER
1384 ) IS
1385 l_batch_rec ar_batches%ROWTYPE;
1386 l_row_id VARCHAR2(50);
1387 l_batch_id NUMBER := NULL;
1388 l_request_id NUMBER;
1389 l_batch_name VARCHAR2(30);
1390 l_batch_applied_status VARCHAR2(30);
1391 l_bank_account_id_low NUMBER;
1392 l_bank_account_id_high NUMBER;
1393 l_call_conc_request VARCHAR2(30);
1394 batch_id VARCHAR2(30);
1395 psite_required VARCHAR2(2);
1396 pinvoices_per_commit NUMBER;
1397 preceipts_per_commit NUMBER;
1398 pfunctional_currency VARCHAR2(20);
1399 pacc_method VARCHAR2(20);
1400
1401 BEGIN
1402 IF PG_DEBUG in ('Y', 'C') THEN
1403 arp_debug.debug('insert_batch()+');
1404 arp_debug.debug('p_gl_date '||p_gl_date);
1405 arp_debug.debug('batch_date '||p_batch_date);
1406 arp_debug.debug('p_receipt_method_id '|| to_char(p_receipt_method_id));
1407 arp_debug.debug('p_receipt_class_id '|| to_char(p_receipt_class_id));
1408 arp_debug.debug('p_currency_code '||p_currency_code);
1409 arp_debug.debug('p_approve_flag '||p_approve_flag);
1410 arp_debug.debug('p_format_flag '||p_format_flag);
1411 arp_debug.debug('p_create_flag '||p_create_flag);
1412 arp_debug.debug('p_exchange_date '||p_exchange_date);
1413 arp_debug.debug('p_exchange_rate '||p_exchange_rate);
1414 arp_debug.debug('p_exchange_rate_type'||p_exchange_rate_type);
1415 END IF;
1416
1417 /* insert the batch record here */
1418 IF PG_DEBUG in ('Y', 'C') THEN
1419 arp_debug.debug('autorecapi calling auto_batch ()+');
1420 END IF;
1421
1422 l_batch_rec.receipt_class_id := to_number(p_receipt_class_id);
1423 l_batch_rec.receipt_method_id := to_number(p_receipt_method_id);
1424 l_batch_rec.batch_date := to_date(p_batch_date,'DD/MM/YY');
1425 l_batch_rec.gl_date := to_date(p_gl_date,'DD/MM/YY');
1426 l_batch_rec.currency_code := p_currency_code;
1427 l_batch_rec.comments := null;
1428 l_batch_rec.exchange_date := p_exchange_date;
1429 l_batch_rec.exchange_rate := p_exchange_rate;
1430 l_batch_rec.exchange_rate_type := p_exchange_rate_type;
1431
1432 arp_rw_batches_pkg.insert_auto_batch(
1433 l_row_id,
1434 l_batch_id,
1435 l_batch_rec.batch_date,
1436 l_batch_rec.currency_code,
1437 l_batch_name, --out
1438 l_batch_rec.comments,
1439 l_batch_rec.exchange_date,
1440 l_batch_rec.exchange_rate,
1441 l_batch_rec.exchange_rate_type,
1442 l_batch_rec.gl_date,
1443 l_batch_rec.media_reference,
1444 l_batch_rec.receipt_class_id,
1445 l_batch_rec.receipt_method_id,
1446 l_batch_rec.attribute_category,
1447 l_batch_rec.attribute1,
1448 l_batch_rec.attribute2,
1449 l_batch_rec.attribute3,
1450 l_batch_rec.attribute4,
1451 l_batch_rec.attribute5,
1452 l_batch_rec.attribute6,
1453 l_batch_rec.attribute7,
1454 l_batch_rec.attribute8,
1455 l_batch_rec.attribute9,
1456 l_batch_rec.attribute10,
1457 l_batch_rec.attribute11,
1458 l_batch_rec.attribute12,
1459 l_batch_rec.attribute13,
1460 l_batch_rec.attribute14,
1461 l_batch_rec.attribute15,
1462 l_call_conc_request,
1463 l_batch_applied_status, --Out
1464 l_request_id,--OUT
1465 'AUTORECSRS',
1466 '1.0',
1467 l_bank_account_id_low,
1468 l_bank_account_id_high
1469 );
1470
1471 p_batch_id := to_char(l_batch_id);
1472
1473 IF p_batch_id IS NULL THEN
1474 arp_debug.debug ('WAIT HERE THE VALUE OF BATCH_ID IS NULL ERROR');
1475 G_ERROR := 'Y';
1476 END IF;
1477
1478 IF PG_DEBUG in ('Y', 'C') THEN
1479 arp_debug.debug('value of batch_id '||p_batch_id);
1480 arp_debug.debug('value of l_request_id '||l_request_id);
1481 arp_debug.debug('value of pg_request_id '||pg_request_id);
1482 END IF;
1483
1484 IF PG_DEBUG in ('Y', 'C') THEN
1485 arp_debug.debug('autorecapi calling auto_batch end ()-');
1486 END IF;
1487 /* inserted the batch record end */
1488
1489 /* GET THE VALUES from SYSTEM PARAMETERS */
1490 IF PG_DEBUG in ('Y','C') THEN
1491 arp_debug.debug( 'get info from system parameters');
1492 END IF;
1493
1494 BEGIN
1495 SELECT asp.site_required_flag,
1496 asp.auto_rec_invoices_per_commit,
1497 asp.auto_rec_receipts_per_commit,
1498 gsob.currency_code,
1499 asp.accounting_method
1500 INTO psite_required,
1501 pinvoices_per_commit,
1502 preceipts_per_commit,
1503 pfunctional_currency,
1504 pacc_method
1505 FROM ar_system_parameters asp,
1506 gl_sets_of_books gsob,
1507 ar_batches ab
1508 WHERE ab.batch_id = p_batch_id
1509 AND ab.set_of_books_id = gsob.set_of_books_id
1510 AND gsob.set_of_books_id = asp.set_of_books_id;
1511
1512 EXCEPTION
1513 WHEN no_data_found THEN
1514 arp_debug.debug( 'ERROR NO DATA FOUND IN SYSTEM OPTION');
1515 G_ERROR := 'Y';
1516 END;
1517
1518 IF PG_DEBUG in ('Y', 'C') THEN
1519 arp_debug.debug ( 'site_req_flag ' || psite_required);
1520 arp_debug.debug ( 'the invoices per commit ' || pinvoices_per_commit);
1521 arp_debug.debug ( 'receipts per_commit ' || preceipts_per_commit);
1522 arp_debug.debug ( 'currency code ' || pfunctional_currency);
1523 arp_debug.debug ( 'acc_method ' || pacc_method );
1524 END IF;
1525
1526 /* END FROM SYSTEM PARAMETERS*/
1527 IF PG_DEBUG in ('Y', 'C') THEN
1528 arp_debug.debug('insert_batch()-');
1529 END IF;
1530
1531 EXCEPTION
1532 WHEN others THEN
1533 IF PG_DEBUG in ('Y', 'C') THEN
1534 arp_debug.debug('Exception : insert_batch() ');
1535 END IF;
1536 G_ERROR := 'Y';
1537 END insert_batch;
1538
1539 /*========================================================================+
1540 | PUBLIC PROCEDURE SELECT_VALID_INVOICES |
1541 | |
1542 | DESCRIPTION |
1543 | |
1544 | This procedure is used to select the valied invoices and insert them |
1545 | into the GT table AR_RECEIPTS_GT |
1546 | PSEUDO CODE/LOGIC |
1547 | |
1548 | PARAMETERS |
1549 | |
1550 | |
1551 | KNOWN ISSUES |
1552 | |
1553 | NOTES |
1554 | |
1555 | |
1556 | MODIFICATION HISTORY |
1557 | Date Author Description of Changes |
1558 | 16-JUL-2005 bichatte Created |
1559 *=========================================================================*/
1560
1561 PROCEDURE select_valid_invoices(
1562 p_trx_date_l IN ar_payment_schedules.trx_date%TYPE,
1563 p_trx_date_h IN ar_payment_schedules.trx_date%TYPE,
1564 p_due_date_l IN ar_payment_schedules.due_date%TYPE,
1565 p_due_date_h IN ar_payment_schedules.due_date%TYPE,
1566 p_trx_num_l IN ar_payment_schedules.trx_number%TYPE,
1567 p_trx_num_h IN ar_payment_schedules.trx_number%TYPE,
1568 p_doc_num_l IN ra_customer_trx.doc_sequence_value%TYPE,
1569 p_doc_num_h IN ra_customer_trx.doc_sequence_value%TYPE,
1570 p_customer_number_l IN hz_cust_accounts.account_number%TYPE, --Bug6734688
1571 p_customer_number_h IN hz_cust_accounts.account_number%TYPE, --Bug6734688
1572 p_customer_name_l IN hz_parties.party_name%TYPE, --Bug6734688
1573 p_customer_name_h IN hz_parties.party_name%TYPE, --Bug6734688
1574 p_batch_id IN ar_batches.batch_id%TYPE,
1575 p_approve_only_flag IN VARCHAR2 ,--Bug5344405
1576 p_receipt_method_id IN ar_receipt_methods.receipt_method_id%TYPE,
1577 p_total_workers IN NUMBER DEFAULT 1
1578 ) IS
1579
1580 trx_invoices INTEGER;
1581 l_rows_processed INTEGER;
1582 l_rows_fetched INTEGER;
1583 l_sel_stmt long;
1584 p_lead_days NUMBER;
1585 p_batch_date DATE;
1586 p_creation_rule ar_receipt_methods.receipt_creation_rule_code%TYPE;
1587 p_currency_code VARCHAR2(20);
1588
1589 l_total_workers NUMBER;
1590
1591 -- insert variables
1592 inst_stmt varchar2(1000);
1593 ps_id_array dbms_sql.NUmber_Table;
1594 trx_id_array dbms_sql.NUmber_Table;
1595 cr_id_array dbms_sql.Number_Table;
1596 paying_customer_id_array dbms_sql.Number_Table;
1597 paying_site_use_id_array dbms_sql.Number_Table;
1598 due_date_array dbms_sql.date_Table;
1599 adr_array dbms_sql.Number_Table;
1600 cust_bank_acct_id_array dbms_sql.Number_Table;
1601 cust_min_amt_array dbms_sql.Number_Table;
1602 pmt_trxn_ext_id_array dbms_sql.Number_Table;
1603 pmt_channel_array dbms_sql.varchar2_Table;
1604 pmt_instrument_type_array dbms_sql.varchar2_Table;
1605 rec_t number;
1606 dummy number;
1607 i number;
1608
1609 BEGIN
1610
1611 --- Print the parameters in the debug file
1612 IF PG_DEBUG in ('Y', 'C') THEN
1613 arp_debug.debug('select_valid_invoices start ()+');
1614 arp_debug.debug( 'p_batch_id '||p_batch_id);
1615 arp_debug.debug( 'p_trx_date_l '||p_trx_date_l);
1616 arp_debug.debug( 'p_trx_date_h '||p_trx_date_h);
1617 arp_debug.debug( 'p_due_date_l '||p_due_date_l);
1618 arp_debug.debug( 'p_due_date_h '||p_due_date_h);
1619 arp_debug.debug( 'p_trx_num_l '||p_trx_num_l);
1620 arp_debug.debug( 'p_trx_num_h '||p_trx_num_h);
1621 arp_debug.debug( 'p_doc_num_l '||p_doc_num_l);
1622 arp_debug.debug( 'p_doc_num_h '||p_doc_num_h);
1623 arp_debug.debug( 'p_customer_number_l '||p_customer_number_l);
1624 arp_debug.debug( 'p_customer_number_h '||p_customer_number_h);
1625 arp_debug.debug( 'p_customer_name_l '||p_customer_name_l);
1626 arp_debug.debug( 'p_customer_name_h '||p_customer_name_h);
1627 arp_debug.debug( 'p_approve_only_flag '||p_approve_only_flag);
1628 arp_debug.debug( 'p_receipt_method_id '||p_receipt_method_id);
1629 arp_debug.debug( 'p_total_workers '||p_total_workers);
1630 END IF;
1631
1632 l_total_workers := nvl(p_total_workers,1);
1633
1634 SELECT b.currency_code,
1635 b.batch_date,
1636 r.lead_days,
1637 r.receipt_creation_rule_code
1638 INTO p_currency_code,
1639 p_batch_date,
1640 p_lead_days,
1641 p_creation_rule
1642 from ar_batches b,
1643 ar_receipt_methods r
1644 WHERE b.batch_id = p_batch_id
1645 AND b.receipt_method_id = r.receipt_method_id
1646 AND r.receipt_method_id = p_receipt_method_id;
1647
1648 --- Print the parameters in the debug file
1649 IF PG_DEBUG in ('Y', 'C') THEN
1650 arp_debug.debug( 'p_currency_code '||p_currency_code);
1651 arp_debug.debug( 'p_batch_date '||p_batch_date);
1652 arp_debug.debug( 'p_lead_days '||p_lead_days);
1653 arp_debug.debug( 'p_receipt_method_id '||p_receipt_method_id);
1654 arp_debug.debug( 'p_batch_id '||p_batch_id);
1655 arp_debug.debug( 'p_creation_rule '||p_creation_rule);
1656 arp_debug.debug( 'l_total_workers '||l_total_workers);
1657 END IF;
1658
1659 -- Build the query dynamically based on the input parameters
1660 IF PG_PARALLEL IN ('Y', 'C') THEN
1661 l_sel_stmt := 'INSERT /*+ parallel(a) append */ into ar_autorec_interim a ';
1662 ELSE
1663 l_sel_stmt := 'INSERT into ar_autorec_interim a ';
1664 END IF;
1665
1666 l_sel_stmt := l_sel_stmt ||
1667 ' SELECT /*+ leading(PS1) use_nl(ps,cust_cp,site_cp,cust_cpa,site_cpa,ct,x,u,p) rowid(ps) index_ffs(ps1) parallel_index(ps1) */
1668 '||p_batch_id||',
1669 ps.payment_schedule_id,
1670 ps.customer_trx_id,
1671 ps.cash_receipt_id,
1672 ct.paying_customer_id,
1673 ct.paying_site_use_id,
1674 ct.payment_trxn_extension_id,
1675 ps.due_date,
1676 AR_AUTOREC_API.Get_Invoice_Bal_After_Disc(ps.payment_schedule_id, greatest(:apply_date,ct.trx_date)) amount_due_remaining,
1677 ct.customer_bank_account_id,
1678 DECODE(:creation_rule,
1679 ''PER_CUSTOMER'', NVL(cust_cpa.auto_rec_min_receipt_amount,0),
1680 ''PER_CUSTOMER_DUE_DATE'', NVL(cust_cpa.auto_rec_min_receipt_amount,0),
1681 nvl(nvl(site_cpa.auto_rec_min_receipt_amount,cust_cpa.auto_rec_min_receipt_amount),0)),
1682 p.payment_channel_code,
1683 u.instrument_id,
1684 mod(ct.paying_customer_id,:l_total_workers) + 1 worker_id
1685 FROM hz_customer_profiles cust_cp,
1686 hz_customer_profiles site_cp,
1687 hz_cust_profile_amts cust_cpa,
1688 hz_cust_profile_amts site_cpa,
1689 ra_customer_trx ct,
1690 IBY_FNDCPT_TX_EXTENSIONS X,
1691 IBY_PMT_INSTR_USES_ALL U,
1692 IBY_FNDCPT_PMT_CHNNLS_B P,
1693 ar_payment_schedules ps,
1694 ar_payment_schedules_all ps1 ';
1695
1696 --Bug6734688
1697 IF p_customer_number_l IS NOT NULL OR p_customer_number_h IS NOT NULL
1698 OR p_customer_name_l IS NOT NULL OR p_customer_name_h IS NOT NULL THEN
1699 l_sel_stmt := l_sel_stmt|| ', hz_cust_accounts cust_acct ';
1700
1701 IF p_customer_name_l IS NOT NULL OR p_customer_name_h IS NOT NULL THEN
1702 l_sel_stmt := l_sel_stmt|| ', hz_parties party ';
1703 END IF;
1704 END IF;
1705
1706 l_sel_stmt := l_sel_stmt || --Bug6734688
1707 ' WHERE ps1.status = ''OP''
1708 AND ps1.gl_date_closed = TO_DATE(''4712/12/31'', ''YYYY/MM/DD'')
1709 AND ps1.rowid = ps.rowid ';
1710
1711 --jyothi
1712 if p_approve_only_flag = 'A' then
1713 l_sel_stmt := l_sel_stmt|| ' AND ps.selected_for_receipt_batch_id = :batch_id';
1714 else
1715 l_sel_stmt := l_sel_stmt|| ' AND ps.selected_for_receipt_batch_id IS NULL ';
1716 end if;
1717
1718 l_sel_stmt := l_sel_stmt ||
1719 ' AND ps1.due_date <= :batch_date + TO_NUMBER(:lead_days)
1720 AND ps1.invoice_currency_code = :currency_code
1721 AND ps.customer_trx_id = ct.customer_trx_id
1722 AND ps.reserved_type IS NULL
1723 AND ps.reserved_value IS NULL
1724 AND ct.receipt_method_id = TO_NUMBER(:receipt_method_id)
1725 AND nvl(ct.cc_error_flag,''N'') = ''N''
1726 AND ct.paying_customer_id = cust_cp.cust_account_id
1727 -- AND ct.payment_trxn_extension_id = extn.trxn_extension_id
1728 -- AND extn.trxn_ref_number1 = ''TRANSACTION'' /*bug 5707963*/
1729 -- AND extn.trxn_ref_number2 = ct.customer_trx_id
1730 AND ct.payment_trxn_extension_id = x.trxn_extension_id
1731 AND x.instr_assignment_id = u.instrument_payment_use_id(+)
1732 AND x.payment_channel_code = p.payment_channel_code
1733 AND cust_cp.site_use_id IS NULL
1734 AND cust_cp.cust_account_profile_id = cust_cpa.cust_account_profile_id(+)
1735 AND cust_cpa.currency_code(+) = :currency_code
1736 AND ct.paying_site_use_id = site_cp.site_use_id(+)
1737 AND site_cp.cust_account_profile_id = site_cpa.cust_account_profile_id(+)
1738 AND site_cpa.currency_code(+) = :currency_code
1739 AND ( NVL(ps.amount_in_dispute,0) = 0
1740 OR
1741 ( NVL(ps.amount_in_dispute,0) != 0
1742 AND
1743 NVL(site_cp.auto_rec_incl_disputed_flag,cust_cp.auto_rec_incl_disputed_flag) = ''Y'')
1744 )
1745 ';
1746
1747
1748 IF p_trx_num_l IS NOT NULL THEN
1749 l_sel_stmt := l_sel_stmt || ' and ps1.trx_number >= :trx_num_l ';
1750 END IF;
1751
1752 IF p_trx_num_h IS NOT NULL THEN
1753 l_sel_stmt := l_sel_stmt || ' and ps1.trx_number <= :trx_num_h ';
1754 END IF;
1755
1756 IF p_due_date_l IS NOT NULL THEN
1757 l_sel_stmt := l_sel_stmt || ' and ps1.due_date >= :due_date_l ';
1758 END IF;
1759
1760 IF p_due_date_h IS NOT NULL THEN
1761 l_sel_stmt := l_sel_stmt || ' and ps1.due_date <= :due_date_h ';
1762 END IF;
1763
1764 IF p_doc_num_l IS NOT NULL THEN
1765 l_sel_stmt := l_sel_stmt || ' and ct.doc_sequence_value >= :doc_num_l ';
1766 END IF;
1767
1768 IF p_doc_num_h IS NOT NULL THEN
1769 l_sel_stmt := l_sel_stmt || ' and ct.doc_sequence_value <= :doc_num_h ';
1770 END IF;
1771
1772 IF p_trx_date_l IS NOT NULL THEN
1773 l_sel_stmt := l_sel_stmt || ' and ps1.trx_date >= :trx_date_l ';
1774 END IF;
1775
1776 IF p_trx_date_h IS NOT NULL THEN
1777 l_sel_stmt := l_sel_stmt || ' and ps1.trx_date <= :trx_date_h ';
1778 END IF;
1779
1780 --Bug6734688
1781 IF p_customer_number_l IS NOT NULL OR p_customer_number_h IS NOT NULL
1782 OR p_customer_name_l IS NOT NULL OR p_customer_name_h IS NOT NULL THEN
1783
1784 l_sel_stmt := l_sel_stmt || ' and cust_acct.cust_account_id = ct.paying_customer_id ' ;
1785
1786 IF p_customer_name_l IS NOT NULL OR p_customer_name_h IS NOT NULL THEN
1787 l_sel_stmt := l_sel_stmt || ' and cust_acct.party_id = party.party_id ' ;
1788 END IF;
1789
1790 IF p_customer_number_l IS NOT NULL THEN
1791 l_sel_stmt := l_sel_stmt || ' and cust_acct.account_number >= :customer_number_l ';
1792 END IF ;
1793 IF p_customer_number_h IS NOT NULL THEN
1794 l_sel_stmt := l_sel_stmt || ' and cust_acct.account_number <= :customer_number_h ';
1795 END IF;
1796
1797 IF p_customer_name_l IS NOT NULL THEN
1798 l_sel_stmt := l_sel_stmt || ' and party.party_name >= :customer_name_l ';
1799 END IF ;
1800 IF p_customer_name_h IS NOT NULL THEN
1801 l_sel_stmt := l_sel_stmt || ' and party.party_name <= :customer_name_h ';
1802 END IF;
1803
1804 END IF ;
1805 --Bug6734688, end.
1806 --l_sel_stmt := l_sel_stmt || ' FOR UPDATE OF ps.selected_for_receipt_batch_id ';
1807
1808 trx_invoices := dbms_sql.open_cursor;
1809
1810 dbms_sql.parse (trx_invoices,l_sel_stmt,dbms_sql.v7);
1811
1812 IF p_approve_only_flag = 'A' THEN
1813 dbms_sql.bind_variable (trx_invoices,':batch_id',p_batch_id);
1814 END IF;
1815
1816 dbms_sql.bind_variable (trx_invoices,':batch_date',p_batch_date);
1817 dbms_sql.bind_variable (trx_invoices,':lead_days',p_lead_days);
1818 dbms_sql.bind_variable (trx_invoices,':currency_code',p_currency_code);
1819 dbms_sql.bind_variable (trx_invoices,':currency_code',p_currency_code);
1820 dbms_sql.bind_variable (trx_invoices,':currency_code',p_currency_code);
1821 dbms_sql.bind_variable (trx_invoices,':receipt_method_id',p_receipt_method_id);
1822 dbms_sql.bind_variable (trx_invoices,':creation_rule',p_creation_rule);
1823 dbms_sql.bind_variable (trx_invoices,':apply_date',p_batch_date);
1824
1825 IF p_trx_num_l IS NOT NULL THEN
1826 dbms_sql.bind_variable (trx_invoices,':trx_num_l',p_trx_num_l);
1827 END IF;
1828
1829 IF p_trx_num_h IS NOT NULL THEN
1830 dbms_sql.bind_variable (trx_invoices,':trx_num_h',p_trx_num_h);
1831 END IF;
1832
1833 IF p_due_date_l IS NOT NULL THEN
1834 dbms_sql.bind_variable (trx_invoices,':due_date_l',p_due_date_l);
1835 END IF;
1836 IF p_due_date_h IS NOT NULL THEN
1837 dbms_sql.bind_variable (trx_invoices,':due_date_h',p_due_date_h);
1838 END IF;
1839
1840 IF p_trx_date_l IS NOT NULL THEN
1841 dbms_sql.bind_variable (trx_invoices,':trx_date_l',p_trx_date_l);
1842 END IF;
1843 IF p_trx_date_h IS NOT NULL THEN
1844 dbms_sql.bind_variable (trx_invoices,':trx_date_h',p_trx_date_h);
1845 END IF;
1846
1847 IF p_doc_num_l IS NOT NULL THEN
1848 dbms_sql.bind_variable (trx_invoices,':doc_num_l',p_doc_num_l);
1849 END IF;
1850 IF p_doc_num_h IS NOT NULL THEN
1851 dbms_sql.bind_variable (trx_invoices,':doc_num_h',p_doc_num_h);
1852 END IF;
1853
1854 --Bug6734688
1855 IF p_customer_number_l IS NOT NULL THEN
1856 dbms_sql.bind_variable (trx_invoices,':customer_number_l',p_customer_number_l);
1857 END IF;
1858 IF p_customer_number_h IS NOT NULL THEN
1859 dbms_sql.bind_variable (trx_invoices,':customer_number_h',p_customer_number_h);
1860 END IF;
1861
1862 IF p_customer_name_l IS NOT NULL THEN
1863 dbms_sql.bind_variable (trx_invoices,':customer_name_l',p_customer_name_l);
1864 END IF;
1865 IF p_customer_name_h IS NOT NULL THEN
1866 dbms_sql.bind_variable (trx_invoices,':customer_name_h',p_customer_name_h);
1867 END IF;
1868 --Bug6734688, end.
1869
1870 dbms_sql.bind_variable (trx_invoices,':l_total_workers',l_total_workers);
1871
1872 IF PG_DEBUG in ('Y', 'C') THEN
1873 arp_debug.debug( 'the select statement ' || l_sel_stmt);
1874 END IF;
1875
1876 BEGIN
1877 IF PG_DEBUG in ('Y', 'C') THEN
1878 arp_debug.debug( 'PG_PARALLEL ' || PG_PARALLEL);
1879 END IF;
1880
1881 --Enable parallel DML at the session level if the profile is set
1882 IF PG_PARALLEL IN ('Y', 'C') THEN
1883 COMMIT;
1884 EXECUTE IMMEDIATE 'ALTER SESSION ENABLE PARALLEL DML';
1885 END IF;
1886
1887 l_rows_processed := dbms_sql.execute( trx_invoices );
1888
1889 IF PG_PARALLEL IN ('Y', 'C') THEN
1890 COMMIT; /* Bug 8249909 */
1891 END IF;
1892
1893 IF PG_DEBUG in ('Y', 'C') THEN
1894 arp_debug.debug('Number of invoices selected : '||l_rows_processed);
1895 END IF;
1896
1897 dbms_sql.close_cursor( trx_invoices);
1898
1899 IF p_approve_only_flag = 'N' THEN
1900 IF PG_DEBUG in ('Y', 'C') THEN
1901 arp_debug.debug('Stamping batch_id to PS : '||l_rows_processed);
1902 END IF;
1903 update ar_payment_schedules
1904 set selected_for_receipt_batch_id = p_batch_id
1905 where payment_schedule_id in
1906 ( select /*+ cardinality(a 10) */
1907 payment_schedule_id
1908 from ar_autorec_interim a
1909 where batch_id = p_batch_id);
1910
1911 IF PG_DEBUG in ('Y', 'C') THEN
1912 arp_debug.debug('Number of PS records stamped : '||SQL%ROWCOUNT);
1913 END IF;
1914 END IF;
1915
1916
1917 EXCEPTION
1918 WHEN OTHERS THEN
1919 G_ERROR := 'Y';
1920 dbms_sql.close_cursor( trx_invoices);
1921 IF PG_DEBUG in ('Y', 'C') THEN
1922 arp_debug.debug('Exception : select_valid_invoices() '|| SQLERRM);
1923 arp_debug.debug('Error while executing>' || l_sel_stmt);
1924 END IF;
1925 RAISE; /* Bug 8249909 */
1926 END;
1927
1928 EXCEPTION
1929 WHEN others THEN
1930 G_ERROR := 'Y';
1931 IF PG_DEBUG in ('Y', 'C') THEN
1932 arp_debug.debug(' Exception : select_valid_invoices '|| SQLERRM);
1933 arp_debug.debug( 'the select statement ' || l_sel_stmt);
1934 END IF;
1935 RAISE; -- Changed as per Bug:5331158 for returning the error to environment
1936
1937 END select_valid_invoices;
1938
1939
1940 FUNCTION Get_Invoice_Bal_After_Disc( p_applied_payment_schedule_id IN NUMBER,
1941 p_apply_date IN DATE ) RETURN NUMBER IS
1942
1943 l_return_status VARCHAR2(200);
1944 l_discount_max_allowed NUMBER;
1945 l_discount_earned_allowed NUMBER;
1946 l_discount_earned NUMBER;
1947 l_discount_unearned NUMBER;
1948 l_new_amount_due_remaining NUMBER;
1949 l_amount_to_be_applied NUMBER;
1950 l_discount NUMBER;
1951
1952 l_customer_id NUMBER;
1953 l_bill_to_site_use_id NUMBER;
1954 l_applied_payment_schedule_id NUMBER;
1955 l_term_id NUMBER;
1956 l_installment NUMBER;
1957 l_trx_date DATE;
1958 l_apply_date DATE;
1959 l_amount_due_original NUMBER;
1960 l_amount_due_remaining NUMBER;
1961 l_trx_currency_code VARCHAR2(10);
1962 l_discount_taken_unearned NUMBER;
1963 l_discount_taken_earned NUMBER;
1964 l_trx_exchange_rate NUMBER;
1965 l_allow_overappln_flag VARCHAR2(2);
1966 l_calc_discount_on_lines_flag VARCHAR2(1);
1967 l_amount_line_items_original NUMBER;
1968 l_partial_discount_flag VARCHAR(1);
1969
1970 BEGIN
1971
1972 IF PG_DEBUG in ('Y', 'C') THEN
1973 arp_debug.debug( 'Get_Invoice_Bal_After_Disc()+' );
1974 arp_debug.debug( 'p_applied_payment_schedule_id :-' || p_applied_payment_schedule_id );
1975 arp_debug.debug( 'p_apply_date :-' || p_apply_date );
1976 END IF;
1977
1978 l_applied_payment_schedule_id := p_applied_payment_schedule_id;
1979 l_apply_date := p_apply_date;
1980
1981 /* 9928090/12662193 - made ra_terms an outer join so credit memos
1982 would work properly and not get zeroed out */
1983 select ps.customer_id,
1984 ps.customer_site_use_id,
1985 ps.term_id,
1986 ps.terms_sequence_number,
1987 ps.trx_date,
1988 ps.amount_due_original,
1989 ps.amount_due_remaining,
1990 ps.invoice_currency_code,
1991 ps.discount_taken_unearned,
1992 ps.discount_taken_earned,
1993 ps.exchange_rate,
1994 ctt.allow_overapplication_flag,
1995 rt.calc_discount_on_lines_flag,
1996 ps.amount_line_items_original,
1997 rt.partial_discount_flag
1998 into
1999 l_customer_id,
2000 l_bill_to_site_use_id,
2001 l_term_id,
2002 l_installment,
2003 l_trx_date,
2004 l_amount_due_original,
2005 l_amount_due_remaining,
2006 l_trx_currency_code,
2007 l_discount_taken_unearned,
2008 l_discount_taken_earned,
2009 l_trx_exchange_rate,
2010 l_allow_overappln_flag,
2011 l_calc_discount_on_lines_flag,
2012 l_amount_line_items_original,
2013 l_partial_discount_flag
2014 from ar_payment_schedules ps,
2015 ra_cust_trx_types ctt,
2016 ra_terms rt
2017 where ps.payment_schedule_id = l_applied_payment_schedule_id
2018 AND ps.cust_trx_type_id = ctt.cust_trx_type_id
2019 AND ps.term_id = rt.term_id (+);
2020
2021 ar_receipt_lib_pvt.Default_disc_and_amt_applied(
2022 p_customer_id => l_customer_id,
2023 p_bill_to_site_use_id => l_bill_to_site_use_id,
2024 p_applied_payment_schedule_id => l_applied_payment_schedule_id,
2025 p_term_id => l_term_id,
2026 p_installment => l_installment,
2027 p_trx_date => l_trx_date,
2028 p_apply_date => l_apply_date,
2029 p_amount_due_original => l_amount_due_original,
2030 p_amount_due_remaining => l_amount_due_remaining,
2031 p_trx_currency_code => l_trx_currency_code,
2032 p_allow_overappln_flag => l_allow_overappln_flag,
2033 p_discount_taken_unearned => l_discount_taken_unearned,
2034 p_discount_taken_earned => l_discount_taken_earned,
2035 p_trx_exchange_rate => l_trx_exchange_rate,
2036 p_cr_date => NULL,
2037 p_cr_currency_code => NULL,
2038 p_cr_exchange_rate => NULL,
2039 p_cr_unapp_amount => NULL,
2040 p_calc_discount_on_lines_flag => l_calc_discount_on_lines_flag,
2041 p_partial_discount_flag => l_partial_discount_flag,
2042 p_amount_line_items_original => l_amount_line_items_original,
2043 p_customer_trx_line_id => NULL,
2044 p_trx_line_amount => NULL,
2045 p_llca_type => NULL,
2046 p_amount_applied => l_amount_to_be_applied,
2047 p_discount => l_discount,
2048 p_discount_max_allowed => l_discount_max_allowed,
2049 p_discount_earned_allowed => l_discount_earned_allowed,
2050 p_discount_earned => l_discount_earned,
2051 p_discount_unearned => l_discount_unearned,
2052 p_new_amount_due_remaining => l_new_amount_due_remaining,
2053 p_return_status => l_return_status
2054 );
2055
2056 IF PG_DEBUG in ('Y', 'C') THEN
2057 arp_debug.debug( 'l_amount_to_be_applied :- '|| l_amount_to_be_applied );
2058 arp_debug.debug( 'l_discount :- '|| l_discount );
2059 arp_debug.debug( 'l_discount_max_allowed :- '|| l_discount_max_allowed );
2060 arp_debug.debug( 'l_discount_earned_allowed :- '|| l_discount_earned_allowed );
2061 arp_debug.debug( 'l_discount_earned :- '|| l_discount_earned );
2062 arp_debug.debug( 'l_discount_unearned :- '|| l_discount_unearned );
2063 arp_debug.debug( 'l_new_amount_due_remaining :- '|| l_new_amount_due_remaining );
2064 arp_debug.debug( 'l_return_status :- '|| l_return_status );
2065 END IF;
2066
2067 IF PG_DEBUG in ('Y', 'C') THEN
2068 arp_debug.debug( 'Get_Invoice_Bal_After_Disc()-' );
2069 END IF;
2070
2071 RETURN l_amount_to_be_applied;
2072
2073 EXCEPTION
2074 WHEN OTHERS THEN
2075 l_return_status := 'E';
2076 IF PG_DEBUG in ('Y', 'C') THEN
2077 arp_debug.debug( 'l_return_status :- '|| l_return_status );
2078 arp_debug.debug( 'Exception in Get_Invoice_Bal_After_Disc()!!!' );
2079 END IF;
2080 RAISE;
2081 END Get_Invoice_Bal_After_Disc;
2082
2083
2084 /*========================================================================+
2085 | PROCEDURE insert_exceptions |
2086 | |
2087 | DESCRIPTION |
2088 | |
2089 | This procedure is used to insert the exception record when |
2090 | |
2091 | PSEUDO CODE/LOGIC |
2092 | |
2093 | PARAMETERS |
2094 | |
2095 | |
2096 | KNOWN ISSUES |
2097 | |
2098 | NOTES |
2099 | |
2100 | |
2101 | MODIFICATION HISTORY |
2102 | Date Author Description of Changes |
2103 | 16-JUL-2005 bichatte Created |
2104 *=========================================================================*/
2105 PROCEDURE insert_exceptions(
2106 p_batch_id IN ar_batches.batch_id%TYPE DEFAULT NULL,
2107 p_request_id IN ar_cash_receipts.request_id%TYPE DEFAULT NULL,
2108 p_cash_receipt_id IN ar_cash_receipts.cash_receipt_id%TYPE DEFAULT NULL,
2109 p_payment_schedule_id IN ar_payment_schedules.payment_schedule_id%TYPE DEFAULT NULL,
2110 p_paying_customer_id IN ar_cash_receipts.pay_from_customer%TYPE DEFAULT NULL,
2111 p_paying_site_use_id IN ar_cash_receipts.customer_site_use_id%TYPE DEFAULT NULL,
2112 p_due_date IN ar_payment_schedules.due_date%TYPE DEFAULT NULL,
2113 p_cust_min_rec_amount IN NUMBER DEFAULT NULL,
2114 p_bank_min_rec_amount IN NUMBER DEFAULT NULL,
2115 p_exception_code IN VARCHAR2,
2116 p_additional_message IN VARCHAR2
2117 ) IS
2118
2119 l_paying_customer_id NUMBER;
2120 l_reqid NUMBER;
2121
2122 BEGIN
2123
2124 IF PG_DEBUG in ('Y','C') THEN
2125 arp_debug.debug('insert_exceptions()+');
2126 arp_debug.debug('p_batch_id '|| p_batch_id);
2127 arp_debug.debug('p_request_id '|| p_request_id);
2128 arp_debug.debug('p_cash_receipt_id '|| p_cash_receipt_id);
2129 arp_debug.debug('p_payment_schedule_id '|| p_payment_schedule_id);
2130 arp_debug.debug('p_paying_customer_id '|| p_paying_customer_id);
2131 arp_debug.debug('p_paying_site_use_id '|| p_paying_site_use_id);
2132 arp_debug.debug('p_due_date '|| p_due_date);
2133 arp_debug.debug('p_cust_min_rec_amount '|| p_cust_min_rec_amount);
2134 arp_debug.debug('p_bank_min_rec_amount '|| p_bank_min_rec_amount);
2135 arp_debug.debug('p_exception_code '||p_exception_code);
2136 arp_debug.debug('p_additional_message '||p_additional_message);
2137 END IF;
2138
2139 l_paying_customer_id := p_paying_customer_id;
2140
2141 IF l_paying_customer_id is null and
2142 p_cash_receipt_id is not null THEN
2143 select pay_from_customer
2144 into l_paying_customer_id
2145 from ar_cash_receipts
2146 where cash_receipt_id = p_cash_receipt_id;
2147 END IF;
2148
2149 IF PG_DEBUG in ('Y','C') THEN
2150 arp_debug.debug('l_request_id '|| pg_request_id );
2151 arp_debug.debug('l_last_updated_by '|| pg_last_updated_by );
2152 arp_debug.debug('l_created_by '|| pg_created_by );
2153 arp_debug.debug('l_last_update_login '|| pg_last_update_login );
2154 arp_debug.debug('l_program_application_id '|| to_char(pg_program_application_id) );
2155 arp_debug.debug('l_program_id '|| to_char(pg_program_id) );
2156 END IF;
2157
2158 INSERT INTO ar_autorec_exceptions
2159 (batch_id,
2160 request_id,
2161 cash_receipt_id,
2162 payment_schedule_id,
2163 paying_customer_id,
2164 paying_site_use_id,
2165 due_date,
2166 cust_min_rec_amount,
2167 bank_min_rec_amount,
2168 exception_code,
2169 additional_message,
2170 last_update_date,
2171 last_updated_by,
2172 creation_date,
2173 created_by,
2174 last_update_login,
2175 program_application_id,
2176 program_id,
2177 program_update_date)
2178 SELECT
2179 p_batch_id,
2180 pg_request_id,
2181 p_cash_receipt_id,
2182 p_payment_schedule_id,
2183 l_paying_customer_id,
2184 p_paying_site_use_id,
2185 p_due_date,
2186 p_cust_min_rec_amount,
2187 p_bank_min_rec_amount,
2188 p_exception_code,
2189 substr(p_additional_message, 1, 240),
2190 sysdate,
2191 pg_last_updated_by,
2192 sysdate,
2193 pg_created_by,
2194 pg_last_update_login,
2195 pg_program_application_id,
2196 pg_program_id,
2197 sysdate
2198 FROM DUAL;
2199
2200 IF PG_DEBUG in ('Y','C') THEN
2201 arp_debug.debug ( 'the rows in exceptions = ' || SQL%ROWCOUNT );
2202 arp_debug.debug ( 'insert_exceptions()-');
2203 END IF;
2204
2205 EXCEPTION
2206 WHEN OTHERS THEN
2207
2208 IF PG_DEBUG in ('Y','C') THEN
2209 arp_debug.debug ( 'ERROR IN insert_exceptions '||SQLERRM );
2210 END IF;
2211 END insert_exceptions;
2212
2213
2214 /*========================================================================+
2215 | PUBLIC PROCEDURE SUBMIT_REPORT |
2216 | |
2217 | DESCRIPTION |
2218 | |
2219 | This procedure is used to get the parameters from the Conc program |
2220 | and convert them to the type reqd for processing. |
2221 | |
2222 | PSEUDO CODE/LOGIC |
2223 | |
2224 | PARAMETERS |
2225 | |
2226 | |
2227 | KNOWN ISSUES |
2228 | |
2229 | NOTES |
2230 | |
2231 | |
2232 | MODIFICATION HISTORY |
2233 | Date Author Description of Changes |
2234 | 16-JUL-2005 bichatte Created |
2235 *=========================================================================*/
2236
2237 PROCEDURE SUBMIT_REPORT ( p_batch_id ar_batches.batch_id%TYPE,
2238 p_request_id ar_cash_receipts.request_id%TYPE ) IS
2239
2240 l_reqid NUMBER(15);
2241 l_org_id NUMBER;
2242 l_complete BOOLEAN := FALSE;
2243 l_uphase VARCHAR2(255);
2244 l_dphase VARCHAR2(255);
2245 l_ustatus VARCHAR2(255);
2246 l_dstatus VARCHAR2(255);
2247 l_message VARCHAR2(32000);
2248
2249
2250 BEGIN
2251 IF PG_DEBUG in ('Y', 'C') THEN
2252 arp_debug.debug('SUBMIT_REPORT()+');
2253 END IF;
2254
2255 --l_org_id := TO_NUMBER(FND_PROFILE.value('ORG_ID'));
2256 --setting the Org context before calling the conc prg Bug 5519913
2257 l_org_id := mo_global.get_current_org_id;
2258
2259 if l_org_id is null then
2260 BEGIN
2261 select org_id into l_org_id
2262 from ar_batches_all
2263 where batch_id = p_batch_id;
2264 EXCEPTION
2265 when others then
2266 arp_debug.debug('Submit Report ...OTHERS');
2267 l_org_id := TO_NUMBER(FND_PROFILE.value('ORG_ID'));
2268 END;
2269 end if;
2270
2271 fnd_request.set_org_id(l_org_id);
2272
2273 l_reqid := FND_REQUEST.SUBMIT_REQUEST (
2274 application=>'AR',
2275 program=>'ARZCARPO',
2276 sub_request=>FALSE,
2277 argument1=>'P_PROCESS_TYPE=RECEIPT',
2278 argument2=>'P_BATCH_ID='|| p_batch_id,
2279 argument3=>'P_CREATE_FLAG='||g_create_flag,
2280 argument4=>'P_APPROVE_FLAG='||g_approve_flag,
2281 argument5=>'P_FORMAT_FLAG='||g_format_flag,
2282 argument6=>'P_REQUEST_ID_MAIN=' || p_request_id
2283 ) ;
2284
2285 IF PG_DEBUG in ('Y', 'C') THEN
2286 arp_debug.debug('Request Id :' || l_reqid);
2287 END IF;
2288
2289 arp_debug.debug (' COMMITING WORK ');
2290 commit; -- This is there to commit the conc request.
2291
2292 l_complete := FND_CONCURRENT.WAIT_FOR_REQUEST(
2293 request_id => l_reqid,
2294 interval => 30,
2295 max_wait => 144000,
2296 phase => l_uphase,
2297 status => l_ustatus,
2298 dev_phase => l_dphase,
2299 dev_status => l_dstatus,
2300 message => l_message);
2301
2302
2303 IF PG_DEBUG in ('Y', 'C') THEN
2304 arp_debug.debug('SUBMIT_REPORT()-');
2305 END IF;
2306
2307 EXCEPTION
2308 WHEN OTHERS THEN
2309 IF PG_DEBUG in ('Y', 'C') THEN
2310 arp_debug.debug('Submitting the report.iN ERROR.'||SQLERRM);
2311 END IF;
2312 RAISE;
2313 END SUBMIT_REPORT;
2314
2315
2316
2317 PROCEDURE rec_reset( p_apply_fail IN VARCHAR2,
2318 p_pay_process_fail IN VARCHAR2,
2319 p_gt_id IN NUMBER )IS
2320
2321
2322 BEGIN
2323 IF PG_DEBUG in ('Y', 'C') THEN
2324 arp_debug.debug('rec_reset()+');
2325 END IF;
2326
2327
2328 IF p_pay_process_fail = 'Y' THEN
2329
2330 UPDATE /*+ index(ct ra_customer_trx_u1) */ ra_customer_trx_all ct
2331 SET cc_error_flag = null,
2332 cc_error_code = null,
2333 cc_error_text = null
2334 WHERE customer_trx_id in
2335 (
2336 SELECT /*+ LEADING(R) INDEX (R, AR_RECEIPTS_GT_N1) USE_NL(R, CR) */ r.customer_trx_id
2337 FROM ar_receipts_gt r,
2338 ar_cash_receipts cr,
2339 ar_cash_receipt_history crh,
2340 iby_fndcpt_tx_operations op,
2341 iby_trxn_summaries_all summ
2342 WHERE r.gt_id = p_gt_id
2343 AND cr.cash_receipt_id = r.cash_receipt_id
2344 AND crh.cash_receipt_id = cr.cash_receipt_id
2345 AND crh.status = 'CONFIRMED'
2346 AND crh.current_record_flag = 'Y'
2347 AND cr.payment_trxn_extension_id = op.trxn_extension_id
2348 AND op.transactionid = summ.transactionid
2349 AND summ.reqtype = 'ORAPMTREQ'
2350 AND summ.status IN(0, 100, 111)
2351 AND((trxntypeid IN(2, 3)) OR((trxntypeid = 20)
2352 AND(summ.trxnmid =
2353 (SELECT MAX(trxnmid)
2354 FROM iby_trxn_summaries_all
2355 WHERE transactionid = summ.transactionid
2356 AND(reqtype = 'ORAPMTREQ')
2357 AND(status IN(0, 100, 111))
2358 AND(trxntypeid = 20)))))
2359 )
2360 AND cc_error_flag = 'Y';
2361
2362 IF PG_DEBUG in ('Y', 'C') THEN
2363 fnd_file.put_line(FND_FILE.LOG,'receipt rows updated to reset cc_error_flag : '||sql%rowcount);
2364 END IF;
2365
2366 delete from ar_autorec_exceptions
2367 where cash_receipt_id in
2368 ( SELECT /*+ LEADING(R) INDEX (R, AR_RECEIPTS_GT_N1) USE_NL(R, CR) */ cr.cash_receipt_id
2369 FROM ar_receipts_gt r,
2370 ar_cash_receipts cr,
2371 ar_cash_receipt_history crh,
2372 iby_fndcpt_tx_operations op,
2373 iby_trxn_summaries_all summ
2374 WHERE r.gt_id = p_gt_id
2375 AND cr.cash_receipt_id = r.cash_receipt_id
2376 AND crh.cash_receipt_id = cr.cash_receipt_id
2377 AND crh.status = 'CONFIRMED'
2378 AND crh.current_record_flag = 'Y'
2379 AND cr.payment_trxn_extension_id = op.trxn_extension_id
2380 AND op.transactionid = summ.transactionid
2381 AND summ.reqtype = 'ORAPMTREQ'
2382 AND summ.status IN(0, 100, 111)
2383 AND((trxntypeid IN(2, 3)) OR((trxntypeid = 20)
2384 AND(summ.trxnmid =
2385 (SELECT MAX(trxnmid)
2386 FROM iby_trxn_summaries_all
2387 WHERE transactionid = summ.transactionid
2388 AND(reqtype = 'ORAPMTREQ')
2389 AND(status IN(0, 100, 111))
2390 AND(trxntypeid = 20)))))
2391 )
2392 and request_id = pg_request_id;
2393
2394 IF PG_DEBUG in ('Y', 'C') THEN
2395 fnd_file.put_line(FND_FILE.LOG,'rows deleted from ar_autorec_exceptions: '||sql%rowcount);
2396 END IF;
2397
2398 /* Note here - the apply process was succesful but auth failed so here we have to unapply the
2399 payment_schedule_id before going in for the delete */
2400
2401 /* start unapply */
2402 DECLARE
2403
2404 ul_return_status VARCHAR2(1);
2405 ul_msg_count NUMBER;
2406 ul_msg_data VARCHAR2(240);
2407 ul_count NUMBER;
2408 l_called_from VARCHAR2(15);
2409
2410 CURSOR UNAPP is
2411 select ps.payment_schedule_id ps_id,
2412 ps.trx_number trx_num,
2413 nvl(ps.terms_sequence_number,1) inst_num,
2414 ps.customer_trx_id trx_id,
2415 r.receipt_number rec_num,
2416 r.cash_receipt_id cash_receipt_id,
2417 ps.org_id org_id
2418 from ar_payment_schedules ps,
2419 ra_customer_trx trx,
2420 ar_receipts_gt r
2421 where trx.customer_trx_id = ps.customer_trx_id
2422 and trx.cc_error_flag = 'Y'
2423 and r.payment_schedule_id = ps.payment_schedule_id
2424 and r.gt_id = p_gt_id;
2425
2426 BEGIN
2427
2428 IF PG_DEBUG in ('Y', 'C') THEN
2429 arp_debug.debug('l_called_from'|| l_called_from);
2430 arp_debug.debug('Calling UNAPP ()+');
2431 END IF;
2432
2433 FOR PS in UNAPP LOOP
2434 /* INITILIAZE the OUT variables */
2435 ul_msg_count := 0;
2436 ul_msg_data := NULL;
2437 ul_return_status := NULL;
2438 ul_count := 0;
2439 l_called_from := 'AUTORECAPI';
2440
2441 AR_RECEIPT_API_PUB.unapply
2442 ( p_api_version => 1.0,
2443 p_init_msg_list => FND_API.G_TRUE,
2444 p_commit => FND_API.G_FALSE,
2445 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
2446 x_return_status => ul_return_status,
2447 x_msg_count => ul_msg_count,
2448 x_msg_data => ul_msg_data,
2449 p_cash_receipt_id => PS.cash_receipt_id,
2450 p_customer_trx_id =>PS.trx_id,
2451 p_installment =>PS.inst_num,
2452 p_applied_payment_schedule_id =>PS.ps_id,
2453 p_called_from => l_called_from,
2454 p_org_id => ps.org_id
2455 );
2456
2457 arp_debug.debug('x_return_status: '||ul_return_status);
2458
2459 IF ul_return_status <> 'S' THEN
2460
2461 IF ul_msg_count = 1 Then
2462 arp_debug.debug('ul_msg_data '||ul_msg_data);
2463 ELSIF ul_msg_count > 1 Then
2464 LOOP
2465 IF nvl(ul_count,0) < ul_msg_count THEN
2466 ul_count := nvl(ul_count,0) +1 ;
2467 ul_msg_data :=FND_MSG_PUB.Get(FND_MSG_PUB.G_NEXT,FND_API.G_FALSE);
2468
2469 arp_debug.debug ( 'the number is ' || ul_count );
2470 arp_debug.debug ( 'the message data is ' || ul_msg_data );
2471 ELSE
2472 EXIT;
2473 END IF;
2474 END LOOP;
2475 END IF;
2476 END IF;
2477 END LOOP;
2478
2479 EXCEPTION
2480 WHEN others THEN
2481 IF PG_DEBUG in ('Y', 'C') THEN
2482 arp_debug.debug('Exception :In the UNAPPLY routine '|| SQLERRM);
2483 END IF;
2484
2485 END ;
2486
2487 /* end unapply */
2488 arp_debug.debug('delete the bad receipts');
2489
2490 /* Start of delete XLA events code. Doing this is bulk */
2491 Begin
2492 IF PG_DEBUG in ('Y','C') THEN
2493 arp_debug.debug ( 'Start calling xla delete_bulk_events');
2494 arp_debug.debug ( 'Inserting into xla_events_int_gt...');
2495 END IF;
2496
2497 INSERT INTO xla_events_int_gt
2498 (event_id
2499 ,ledger_id
2500 ,entity_code
2501 ,application_id
2502 ,event_type_code
2503 ,entity_id
2504 ,event_number
2505 ,event_status_code
2506 ,process_status_code
2507 ,event_date
2508 ,transaction_date
2509 ,budgetary_control_flag
2510 ,reference_num_1
2511 ,reference_num_2
2512 ,reference_num_3
2513 ,reference_num_4
2514 ,reference_char_1
2515 ,reference_char_2
2516 ,reference_char_3
2517 ,reference_char_4
2518 ,reference_date_1
2519 ,reference_date_2
2520 ,reference_date_3
2521 ,reference_date_4
2522 ,on_hold_flag)
2523 ( SELECT event_id
2524 ,ledger_id
2525 ,entity_code
2526 ,xte.application_id
2527 ,event_type_code
2528 ,xte.entity_id
2529 ,event_number
2530 ,event_status_code
2531 ,process_status_code
2532 ,TRUNC(event_date)
2533 ,nvl(transaction_date, TRUNC(event_date))
2534 ,'N'
2535 ,reference_num_1
2536 ,reference_num_2
2537 ,reference_num_3
2538 ,reference_num_4
2539 ,reference_char_1
2540 ,reference_char_2
2541 ,reference_char_3
2542 ,reference_char_4
2543 ,reference_date_1
2544 ,reference_date_2
2545 ,reference_date_3
2546 ,reference_date_4
2547 ,on_hold_flag
2548 from xla_transaction_entities_upg xte,
2549 xla_events xe
2550 where xte.application_id = 222
2551 and xte.entity_code = 'RECEIPTS'
2552 and xe.application_id = 222
2553 and xe.event_number > 0
2554 and xe.entity_id = xte.entity_id
2555 and xte.ledger_id = ARP_STANDARD.sysparm.set_of_books_id
2556 and NVL(xte.source_id_int_1, -99) IN
2557 (select distinct cash_receipt_id
2558 from ar_autorec_exceptions
2559 where request_id = pg_request_id));
2560
2561 IF PG_DEBUG in ('Y','C') THEN
2562 arp_debug.debug ( 'rows inserted into xla gt table = '|| sql%rowcount);
2563 arp_debug.debug ( 'Calling xla_events_pub_pkg.delete_bulk_events()');
2564 END IF;
2565
2566 xla_events_pub_pkg.delete_bulk_events(222);
2567
2568 IF PG_DEBUG in ('Y','C') THEN
2569 arp_debug.debug ( 'End calling xla delete_bulk_events');
2570 END IF;
2571
2572 EXCEPTION
2573 WHEN OTHERS THEN
2574 IF PG_DEBUG in ('Y', 'C') THEN
2575 arp_debug.debug('Error in call to xla_events_pub_pkg.delete_bulk_events ' || sqlerrm);
2576 END IF;
2577 END;
2578 /* End of delete XLA events code */
2579
2580 update ar_payment_schedules
2581 set selected_for_receipt_batch_id = null,
2582 gl_date_closed = TO_DATE('4712/12/31', 'YYYY/MM/DD'),
2583 actual_date_closed = TO_DATE('4712/12/31', 'YYYY/MM/DD'),
2584 status = 'OP'
2585 where payment_schedule_id in
2586 ( select ps.payment_schedule_id
2587 from ar_payment_schedules ps,
2588 ra_customer_trx trx,
2589 ar_receipts_gt r
2590 where r.gt_id = p_gt_id
2591 AND r.payment_schedule_id = ps.payment_schedule_id
2592 AND trx.customer_trx_id = ps.customer_trx_id
2593 and trx.cc_error_flag = 'Y');
2594
2595 IF PG_DEBUG in ('Y','C') THEN
2596 arp_debug.debug ( ' rows updated PS = ' || SQL%ROWCOUNT );
2597 END IF;
2598
2599 delete
2600 from ar_payment_schedules
2601 where cash_receipt_id
2602 in ( select distinct ex.cash_receipt_id
2603 from ar_autorec_exceptions ex,
2604 ar_receipts_gt r
2605 where r.gt_id = p_gt_id
2606 AND r.cash_receipt_id = ex.cash_receipt_id);
2607
2608 IF PG_DEBUG in ('Y','C') THEN
2609 arp_debug.debug ( ' rows DELETED PS = ' || SQL%ROWCOUNT );
2610 END IF;
2611
2612 delete
2613 from ar_distributions
2614 where source_table = 'CRH'
2615 and source_id in
2616 ( select cash_receipt_history_id
2617 from ar_cash_receipt_history
2618 where cash_receipt_id in
2619 ( select distinct ex.cash_receipt_id
2620 from ar_autorec_exceptions ex,
2621 ar_receipts_gt r
2622 where r.gt_id = p_gt_id
2623 AND r.cash_receipt_id = ex.cash_receipt_id
2624 )
2625 );
2626
2627 IF PG_DEBUG in ('Y','C') THEN
2628 arp_debug.debug ( ' rows DELETED AR_DIST = ' || SQL%ROWCOUNT );
2629 END IF;
2630
2631 delete
2632 from ar_distributions
2633 where source_table = 'RA'
2634 and source_id in
2635 ( select receivable_application_id
2636 from ar_receivable_applications
2637 where cash_receipt_id in
2638 ( select distinct ex.cash_receipt_id
2639 from ar_autorec_exceptions ex,
2640 ar_receipts_gt r
2641 where r.gt_id = p_gt_id
2642 AND r.cash_receipt_id = ex.cash_receipt_id
2643 )
2644 );
2645
2646 IF PG_DEBUG in ('Y','C') THEN
2647 arp_debug.debug ( ' rows DELETED AR_DIST2 = ' || SQL%ROWCOUNT );
2648 END IF;
2649
2650 delete
2651 from ar_receivable_applications
2652 where cash_receipt_id in
2653 ( select distinct ex.cash_receipt_id
2654 from ar_autorec_exceptions ex,
2655 ar_receipts_gt r
2656 where r.gt_id = p_gt_id
2657 AND r.cash_receipt_id = ex.cash_receipt_id);
2658
2659 IF PG_DEBUG in ('Y','C') THEN
2660 arp_debug.debug ( ' rows DELETED REC_APPS = ' || SQL%ROWCOUNT );
2661 END IF;
2662
2663 delete
2664 from ar_cash_receipt_history
2665 where cash_receipt_id in
2666 ( select distinct ex.cash_receipt_id
2667 from ar_autorec_exceptions ex,
2668 ar_receipts_gt r
2669 where r.gt_id = p_gt_id
2670 AND r.cash_receipt_id = ex.cash_receipt_id);
2671
2672 IF PG_DEBUG in ('Y','C') THEN
2673 arp_debug.debug ( ' rows DELETED CRH = ' || SQL%ROWCOUNT );
2674 END IF;
2675
2676 delete from ar_cash_receipts
2677 where cash_receipt_id in
2678 ( select distinct ex.cash_receipt_id
2679 from ar_autorec_exceptions ex,
2680 ar_receipts_gt r
2681 where r.gt_id = p_gt_id
2682 AND r.cash_receipt_id = ex.cash_receipt_id);
2683
2684 IF PG_DEBUG in ('Y','C') THEN
2685 arp_debug.debug ( ' rows DELETED CR = ' || SQL%ROWCOUNT );
2686 END IF;
2687
2688 END IF;
2689
2690 IF PG_DEBUG in ('Y', 'C') THEN
2691 arp_debug.debug('rec_reset()-');
2692 END IF;
2693
2694 EXCEPTION
2695 WHEN OTHERS THEN
2696 IF PG_DEBUG in ('Y', 'C') THEN
2697 arp_debug.debug('rec_reset .iN ERROR.'||SQLERRM);
2698 END IF;
2699
2700 insert_exceptions( p_batch_id => nvl(g_batch_id, -333),
2701 p_request_id => pg_request_id,
2702 p_exception_code => 'AUTORECERR',
2703 p_additional_message => 'rec_reset() '|| SQLERRM );
2704 END rec_reset;
2705
2706
2707 /* START SUBMIT_FORMAT */
2708 PROCEDURE SUBMIT_FORMAT ( p_batch_id ar_batches.batch_id%TYPE ) IS
2709 l_org_id NUMBER;
2710 l_reqid NUMBER;
2711 dev_phase VARCHAR2(255);
2712 dev_status VARCHAR2(255);
2713 message VARCHAR2(2000);
2714 phase VARCHAR2(255);
2715 status VARCHAR2(255);
2716 l_complete BOOLEAN := FALSE;
2717 l_program_name ap_payment_programs.program_name%TYPE;
2718 l_batch_app_status ar_batches.batch_applied_status%TYPE;
2719 l_xml_output BOOLEAN;
2720 l_iso_language FND_LANGUAGES.iso_language%TYPE;
2721 l_iso_territory FND_LANGUAGES.iso_territory%TYPE;
2722
2723 BEGIN
2724
2725 IF PG_DEBUG in ('Y', 'C') THEN
2726 arp_debug.debug(FND_FILE.LOG,'Submitting the report..');
2727 END IF;
2728
2729 --setting the Org context before calling the conc prg Bug 5519913
2730 l_org_id := mo_global.get_current_org_id;
2731
2732 IF l_org_id IS NULL THEN
2733 BEGIN
2734 select org_id
2735 into l_org_id
2736 from ar_batches_all
2737 where batch_id = p_batch_id;
2738 EXCEPTION
2739 WHEN OTHERS THEN
2740 arp_debug.debug('Submit Format ...OTHERS');
2741 l_org_id := TO_NUMBER(FND_PROFILE.value('ORG_ID'));
2742 END;
2743 END IF;
2744
2745 fnd_request.set_org_id(l_org_id);
2746
2747 /* Modified the code as per SEPA Project. We need to check the
2748 program attached to the receipt method and call it accordingly */
2749 /* l_reqid := FND_REQUEST.SUBMIT_REQUEST (
2750 application=>'AR',
2751 program=>'ARXAPFRC',
2752 sub_request=>FALSE,
2753 argument1=>'P_BATCH_ID='|| p_batch_id
2754 ) ;*/
2755 SELECT bat.batch_applied_status,
2756 app.program_name
2757 INTO l_batch_app_status,
2758 l_program_name
2759 FROM ar_batches bat,
2760 ar_receipt_methods rm,
2761 ap_payment_programs app
2762 WHERE bat.batch_id = p_batch_id
2763 AND bat.receipt_method_id = rm.receipt_method_id
2764 AND rm.auto_print_program_id = app.program_id;
2765
2766 IF ( l_program_name = 'ARSEPADNT') THEN
2767 IF l_batch_app_status <> 'COMPLETED_FORMAT' AND l_batch_app_status <> 'STARTED_FORMAT' THEN
2768 UPDATE ar_cash_receipts
2769 SET seq_type_last = 'Y'
2770 WHERE cash_receipt_id IN (
2771 SELECT crh.cash_receipt_id
2772 FROM ar_cash_receipt_history crh,
2773 ar_receivable_applications ra,
2774 ra_customer_trx ct,
2775 iby_fndcpt_tx_extensions ext
2776 WHERE crh.batch_id = p_batch_id
2777 AND crh.current_record_flag = 'Y'
2778 AND crh.status = 'CONFIRMED'
2779 AND ra.cash_receipt_id = crh.cash_receipt_id
2780 AND ra.application_type = 'CASH'
2781 AND ra.status = 'APP'
2782 AND ct.customer_trx_id = ra.applied_customer_trx_id
2783 AND ext.trxn_extension_id = ct.payment_trxn_extension_id
2784 AND NVL(ext.seq_type_last, 'N') = 'Y');
2785 END IF;
2786 SELECT lower(iso_language),iso_territory
2787 INTO l_iso_language,l_iso_territory
2788 FROM FND_LANGUAGES
2789 WHERE language_code = USERENV('LANG');
2790
2791 l_xml_output:= fnd_request.add_layout(
2792 template_appl_name => 'AR',
2793 template_code => 'ARSEPADNT',
2794 template_language => l_iso_language,
2795 template_territory => l_iso_territory,
2796 output_format => 'RTF'
2797 );
2798 END IF;
2799 l_reqid := FND_REQUEST.SUBMIT_REQUEST (
2800 application=>'AR',
2801 program=>l_program_name,
2802 sub_request=>FALSE,
2803 argument1=>'P_BATCH_ID='|| p_batch_id
2804 ) ;
2805 commit; -- commit the conc request bug6630799.
2806
2807 l_complete := FND_CONCURRENT.WAIT_FOR_REQUEST(
2808 request_id => l_reqid,
2809 interval => 15,
2810 max_wait =>1800,
2811 phase =>phase,
2812 status =>status,
2813 dev_phase =>dev_phase,
2814 dev_status =>dev_status,
2815 message =>message);
2816
2817 IF dev_phase <> 'COMPLETE' THEN
2818 arp_debug.debug('Format Program has a phase '||dev_phase);
2819 update ar_batches
2820 SET batch_applied_status = 'COMPLETED_APPROVAL'
2821 where batch_id = p_batch_id;
2822 ELSIF dev_phase = 'COMPLETE' AND
2823 dev_status <> 'NORMAL' THEN
2824 arp_debug.debug('Format Program completed with status '||dev_status);
2825 update ar_batches
2826 SET batch_applied_status = 'COMPLETED_APPROVAL'
2827 where batch_id = p_batch_id;
2828 ELSE
2829 arp_debug.debug('Format Program completed successfully');
2830 update ar_batches
2831 SET batch_applied_status = 'COMPLETED_FORMAT'
2832 where batch_id = p_batch_id;
2833 END IF;
2834
2835 commit; -- This is there to commit the Format status.
2836
2837 IF PG_DEBUG in ('Y', 'C') THEN
2838 arp_debug.debug('Request Id :' || l_reqid);
2839 END IF;
2840
2841 EXCEPTION
2842 WHEN OTHERS THEN
2843 IF PG_DEBUG in ('Y', 'C') THEN
2844 arp_debug.debug('Exception in SUBMIT_FORMAT '||SQLERRM);
2845 END IF;
2846 RAISE;
2847 END SUBMIT_FORMAT;
2848
2849
2850
2851 PROCEDURE create_receipt ( p_amount IN NUMBER,
2852 p_receipt_number IN VARCHAR2,
2853 p_receipt_date IN DATE,
2854 p_gl_date IN DATE, /*Bug 13146325*/
2855 p_customer_id IN NUMBER,
2856 p_installment IN NUMBER,
2857 p_cust_site_use_id IN NUMBER,
2858 p_receipt_method_id IN ar_cash_receipts.receipt_method_id%TYPE,
2859 p_pmt_trxn_ext_id IN RA_CUSTOMER_TRX.PAYMENT_TRXN_EXTENSION_ID%TYPE,
2860 p_called_from IN VARCHAR2,
2861 p_request_id IN NUMBER,
2862 p_batch_id IN NUMBER,
2863 p_exchange_rate IN ar_batches.exchange_rate%TYPE,
2864 p_exchange_date IN ar_batches.exchange_date%TYPE,
2865 p_exchange_rate_type IN ar_batches.exchange_rate_type%TYPE,
2866 p_currency_code IN VARCHAR2,
2867 p_remittance_bank_account_id IN ar_receipt_method_accounts_all.remit_bank_acct_use_id%TYPE,
2868 p_cash_receipt_id OUT NOCOPY ar_cash_receipts.cash_receipt_id%TYPE,
2869 p_return_status OUT NOCOPY VARCHAR2,
2870 p_msg_count OUT NOCOPY NUMBER,
2871 p_msg_data OUT NOCOPY VARCHAR2 ) IS
2872
2873 l_msg_data VARCHAR2(240);
2874 l_count NUMBER;
2875
2876 BEGIN
2877
2878 IF PG_DEBUG in ('Y', 'C') THEN
2879 arp_debug.debug('create_receipt()+');
2880 arp_debug.debug('l_called_from'|| p_called_from);
2881 arp_debug.debug('Calling create_cash API ()+');
2882 END IF;
2883
2884 AR_RECEIPT_API_PUB.create_cash (p_api_version => 1.0,
2885 p_init_msg_list => FND_API.G_TRUE,
2886 p_commit => FND_API.G_FALSE,
2887 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
2888 x_return_status => p_return_status,
2889 x_msg_count => p_msg_count,
2890 x_msg_data => p_msg_data,
2891 p_currency_code => p_currency_code,
2892 p_exchange_rate_type => p_exchange_rate_type,
2893 p_exchange_rate => p_exchange_rate,
2894 p_exchange_rate_date => p_exchange_date,
2895 p_amount => p_amount,
2896 p_receipt_number => p_receipt_number,
2897 p_receipt_method_id => p_receipt_method_id,
2898 p_receipt_date => p_receipt_date,
2899 p_gl_date => p_gl_date, /*Bug 13146325*/
2900 p_customer_id => p_customer_id,
2901 p_installment => p_installment,
2902 p_customer_site_use_id => p_cust_site_use_id,
2903 p_payment_trxn_extension_id => p_pmt_trxn_ext_id,
2904 p_remittance_bank_account_id => p_remittance_bank_account_id,
2905 p_cr_id => p_cash_receipt_id,
2906 p_called_from => p_called_from );
2907
2908 IF PG_DEBUG in ('Y', 'C') THEN
2909 arp_debug.debug('x_return_status: '||p_return_status);
2910 END IF;
2911
2912 --check for the exceptions raised
2913 IF p_return_status <> 'S' THEN
2914
2915 IF p_msg_count = 1 THEN
2916 arp_debug.debug('p_msg_count: '||p_msg_count);
2917 arp_debug.debug('p_msg_data: '||p_msg_data);
2918
2919 IF p_msg_data IS NULL THEN
2920 p_msg_data := FND_MSG_PUB.Get(FND_MSG_PUB.G_NEXT,FND_API.G_FALSE);
2921 END IF;
2922
2923 insert_exceptions( p_batch_id => p_batch_id,
2924 p_request_id => p_request_id,
2925 p_paying_customer_id => p_customer_id,
2926 p_exception_code => 'AUTORECERR',
2927 p_additional_message => p_msg_count||'.'||p_msg_data );
2928
2929 ELSIF p_msg_count > 1 THEN
2930 arp_debug.debug('p_msg_count: '||p_msg_count);
2931 arp_debug.debug('p_msg_data: '||p_msg_data);
2932 p_msg_data := NULL;
2933 LOOP
2934 IF nvl(l_count, 0) < p_msg_count THEN
2935 l_count := nvl(l_count,0) +1 ;
2936 l_msg_data :=FND_MSG_PUB.Get(FND_MSG_PUB.G_NEXT,FND_API.G_FALSE);
2937
2938 arp_debug.debug ( 'the number is ' || l_count );
2939 arp_debug.debug ( 'the message data is ' || l_msg_data );
2940 IF length(p_msg_data||l_count||'.'||l_msg_data||' ') < 2000 THEN
2941 p_msg_data := p_msg_data||l_count||'.'||l_msg_data||' ';
2942 END IF;
2943
2944 insert_exceptions( p_batch_id => p_batch_id,
2945 p_request_id => p_request_id,
2946 p_paying_customer_id => p_customer_id,
2947 p_exception_code => 'AUTORECERR',
2948 p_additional_message => l_count||'.'||l_msg_data );
2949 ELSE
2950 EXIT;
2951 END IF;
2952 END LOOP;
2953 END IF;
2954 END IF; /* end of return status */
2955
2956 IF PG_DEBUG in ('Y', 'C') THEN
2957 arp_debug.debug('create_receipt()-');
2958 END IF;
2959
2960 EXCEPTION
2961 WHEN OTHERS THEN
2962 arp_debug.debug('create_receipt : error code() '|| to_char(SQLCODE));
2963
2964 insert_exceptions( p_batch_id => p_batch_id,
2965 p_request_id => p_request_id,
2966 p_paying_customer_id => p_customer_id,
2967 p_exception_code => 'AUTORECERR',
2968 p_additional_message => 'create_receipt() '||sqlerrm );
2969 END create_receipt;
2970
2971
2972 PROCEDURE receipt_application( p_cash_receipt_id IN NUMBER,
2973 p_applied_ps_id IN NUMBER,
2974 p_amount_applied IN NUMBER,
2975 p_request_id IN NUMBER,
2976 p_batch_id IN NUMBER,
2977 p_apply_date IN DATE,
2978 p_called_from IN VARCHAR2,
2979 p_return_status OUT NOCOPY VARCHAR2) IS
2980 l_msg_count NUMBER;
2981 l_msg_data VARCHAR2(2000);
2982 l_count NUMBER;
2983 BEGIN
2984
2985 IF PG_DEBUG in ('Y', 'C') THEN
2986 arp_debug.debug('AR_AUTOREC_API.receipt_application()+');
2987 arp_debug.debug('l_called_from '|| p_called_from);
2988 arp_debug.debug('p_cash_receipt_id '|| p_cash_receipt_id);
2989 arp_debug.debug('p_applied_ps_id '|| p_applied_ps_id);
2990 arp_debug.debug('p_amount_applied '|| p_amount_applied);
2991 arp_debug.debug('p_batch_id '|| p_batch_id);
2992 END IF;
2993
2994 AR_RECEIPT_API_PUB.apply ( p_api_version => 1.0,
2995 p_init_msg_list => FND_API.G_TRUE,
2996 p_commit => FND_API.G_FALSE,
2997 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
2998 x_return_status => p_return_status,
2999 x_msg_count => l_msg_count,
3000 x_msg_data => l_msg_data,
3001 p_cash_receipt_id => p_cash_receipt_id,
3002 p_applied_payment_schedule_id => p_applied_ps_id,
3003 p_amount_applied => p_amount_applied,
3004 p_apply_date => p_apply_date,
3005 p_called_from => p_called_from );
3006
3007 IF PG_DEBUG in ('Y', 'C') THEN
3008 arp_debug.debug('x_return_status: '||p_return_status);
3009 END IF;
3010
3011 IF p_return_status <> 'S' THEN
3012
3013 IF l_msg_count = 1 Then
3014 arp_debug.debug('al_msg_data '||l_msg_data);
3015 l_msg_data := 'Application failure. You need to nullify the SELECTED FOR RECEIPT BATCH ID on the invoice'||
3016 ' when the invoice is fixed. Then apply the invoice manually to the receipt'||
3017 ' with receipt id: '||p_cash_receipt_id||', created by automatic receipts for that invoice';
3018
3019 insert_exceptions( p_batch_id => p_batch_id,
3020 p_request_id => p_request_id,
3021 p_payment_schedule_id => p_applied_ps_id,
3022 p_exception_code => 'AUTORECERR',
3023 p_additional_message => l_count||l_msg_data );
3024
3025 ELSIF l_msg_count > 1 THEN
3026 LOOP
3027 IF nvl(l_count,0) < l_msg_count THEN
3028 l_count := nvl(l_count,0) +1 ;
3029
3030 IF l_count = 1 THEN
3031 l_msg_data := 'Application failure. You need to nullify the SELECTED FOR RECEIPT BATCH ID on the invoice'||
3032 ' when the invoice is fixed. Then apply the invoice manually to the receipt'||
3033 ' with receipt id: '||p_cash_receipt_id||', created by automatic receipts for that invoice';
3034 ELSE
3035 l_msg_data :=FND_MSG_PUB.Get(FND_MSG_PUB.G_NEXT,FND_API.G_FALSE);
3036 END IF;
3037 arp_debug.debug ( 'the number is ' || l_count );
3038 arp_debug.debug ( 'the message data is ' || l_msg_data );
3039
3040 insert_exceptions( p_batch_id => p_batch_id,
3041 p_request_id => p_request_id,
3042 p_payment_schedule_id => p_applied_ps_id,
3043 p_exception_code => 'AUTORECERR',
3044 p_additional_message => l_count||l_msg_data );
3045 ELSE
3046 EXIT;
3047 END IF;
3048 END LOOP;
3049 END IF;
3050 END IF; /* end of return status */
3051
3052 IF PG_DEBUG in ('Y', 'C') THEN
3053 arp_debug.debug('p_return_status :'|| p_return_status);
3054 arp_debug.debug('AR_AUTOREC_API.receipt_application()-');
3055 END IF;
3056
3057 EXCEPTION
3058 WHEN OTHERS THEN
3059 arp_debug.debug('Exception OTHERS : AR_AUTOREC_API.receipt_application : error code() '|| to_char(SQLCODE));
3060
3061 insert_exceptions( p_batch_id => p_batch_id,
3062 p_request_id => p_request_id,
3063 p_payment_schedule_id => p_applied_ps_id,
3064 p_exception_code => 'AUTORECERR',
3065 p_additional_message => 'receipt_application() '||sqlerrm );
3066
3067 END receipt_application;
3068
3069
3070 PROCEDURE process_payments( p_cash_receipt_id IN ar_cash_receipts.cash_receipt_id%TYPE,
3071 p_pmt_trxn_id IN RA_CUSTOMER_TRX.PAYMENT_TRXN_EXTENSION_ID%TYPE,
3072 p_request_id IN NUMBER,
3073 p_batch_id IN NUMBER,
3074 p_called_from IN VARCHAR2,
3075 p_cc_error_code OUT NOCOPY VARCHAR2,
3076 p_cc_error_text OUT NOCOPY VARCHAR2,
3077 p_return_status OUT NOCOPY VARCHAR2) IS
3078 l_msg_count NUMBER;
3079 l_msg_data VARCHAR2(240);
3080 l_count NUMBER;
3081 l_response_error_code VARCHAR2(20);
3082 l_pmt_trxn_id RA_CUSTOMER_TRX.PAYMENT_TRXN_EXTENSION_ID%TYPE;
3083
3084 /* Bug 7639165 - Declaration Begin. */
3085 l_first_msg VARCHAR2(240);
3086 /* Bug 7639165 - Declaration End. */
3087 BEGIN
3088 IF PG_DEBUG in ('Y', 'C') THEN
3089 arp_debug.debug('AR_AUTOREC_API.process_payments()+');
3090 arp_debug.debug('p_cash_receipt_id :'||p_cash_receipt_id);
3091 END IF;
3092
3093 select payment_trxn_extension_id
3094 into l_pmt_trxn_id
3095 from ar_cash_receipts
3096 where cash_receipt_id = p_cash_receipt_id;
3097
3098 IF PG_DEBUG in ('Y', 'C') THEN
3099 arp_debug.debug('l_pmt_trxn_id :'||l_pmt_trxn_id);
3100 END IF;
3101
3102 AR_RECEIPT_API_PUB.Process_Payment_1(
3103 p_cash_receipt_id => p_cash_receipt_id,
3104 p_called_from => p_called_from,
3105 p_response_error_code => l_response_error_code,
3106 x_msg_count => l_msg_count,
3107 x_msg_data => l_msg_data,
3108 x_return_status => p_return_status,
3109 p_payment_trxn_extension_id => l_pmt_trxn_id);
3110
3111 IF PG_DEBUG in ('Y', 'C') THEN
3112 arp_debug.debug('Process_payment return status: ' || p_return_status);
3113 END IF;
3114
3115 /*------------------------------------------------------+
3116 | Check the return status from Process_Payment |
3117 +------------------------------------------------------*/
3118 IF p_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3119
3120 IF l_msg_count = 1 Then
3121 arp_debug.debug('l_msg_data '||l_msg_data);
3122
3123 insert_exceptions( p_batch_id => p_batch_id,
3124 p_request_id => p_request_id,
3125 p_cash_receipt_id => p_cash_receipt_id,
3126 p_exception_code => 'AR_CC_AUTH_FAILED',
3127 p_additional_message => l_count||l_msg_data );
3128
3129 p_cc_error_code := l_response_error_code;
3130 p_cc_error_text := l_msg_data;
3131
3132 ELSIF l_msg_count > 1 THEN
3133
3134 LOOP
3135 IF nvl(l_count,0) < l_msg_count THEN
3136 l_count := nvl(l_count,0) +1 ;
3137 l_msg_data :=FND_MSG_PUB.Get(FND_MSG_PUB.G_NEXT,FND_API.G_FALSE);
3138
3139 arp_debug.debug ( 'the number is ' || l_count );
3140 arp_debug.debug ( 'the message data is ' || l_msg_data );
3141
3142 /* Bug 7639165 - Changes Begin. */
3143 IF l_count = 2 THEN
3144 l_first_msg := l_msg_data;
3145 END IF;
3146 /* Bug 7639165 - Chages End. */
3147
3148 insert_exceptions( p_batch_id => p_batch_id,
3149 p_request_id => p_request_id,
3150 p_cash_receipt_id => p_cash_receipt_id,
3151 p_exception_code => 'AR_CC_AUTH_FAILED',
3152 p_additional_message => l_count||l_msg_data );
3153
3154 p_cc_error_code := l_response_error_code;
3155 p_cc_error_text := l_first_msg;
3156 ELSE
3157 EXIT;
3158 END IF;
3159 END LOOP;
3160 END IF;
3161 END IF;
3162
3163 IF PG_DEBUG in ('Y', 'C') THEN
3164 arp_debug.debug('AR_AUTOREC_API.process_payments()-');
3165 END IF;
3166
3167 EXCEPTION
3168 WHEN OTHERS THEN
3169 arp_debug.debug('AR_AUTOREC_API.process_payments : error code() '|| to_char(SQLCODE));
3170 insert_exceptions( p_batch_id => p_batch_id,
3171 p_request_id => p_request_id,
3172 p_cash_receipt_id => p_cash_receipt_id,
3173 p_exception_code => 'AR_CC_AUTH_FAILED',
3174 p_additional_message => 'process_payments() '|| sqlerrm);
3175
3176 END process_payments;
3177
3178
3179
3180 PROCEDURE update_ar_receipts_gt(p_creation_rule IN VARCHAR2,
3181 p_update_stmt IN VARCHAR2,
3182 p_cr_id_array IN DBMS_SQL.NUMBER_TABLE,
3183 p_cr_number_array IN DBMS_SQL.VARCHAR2_TABLE,
3184 p_gt_id_array IN DBMS_SQL.NUMBER_TABLE,
3185 p_ps_id_array IN DBMS_SQL.NUMBER_TABLE,
3186 p_paying_customer_id_array IN DBMS_SQL.NUMBER_TABLE,
3187 p_pmt_instrument_array IN DBMS_SQL.VARCHAR2_TABLE,
3188 p_paying_site_use_id_array IN DBMS_SQL.NUMBER_TABLE,
3189 p_due_date_array IN DBMS_SQL.DATE_TABLE,
3190 p_pmt_channel_code_array IN DBMS_SQL.VARCHAR2_TABLE,
3191 p_cust_bank_acct_id_array IN DBMS_SQL.NUMBER_TABLE,
3192 p_trxn_extension_id_array IN DBMS_SQL.NUMBER_TABLE,
3193 p_authorization_id_array IN DBMS_SQL.NUMBER_TABLE )IS
3194 l_update_cursor NUMBER;
3195 l_dummy NUMBER;
3196
3197 BEGIN
3198 IF PG_DEBUG in ('Y', 'C') THEN
3199 arp_debug.debug('update_ar_receipts_gt()+');
3200 arp_debug.debug('p_creation_rule '||p_creation_rule);
3201 arp_debug.debug('p_update_stmt '||p_update_stmt);
3202 END IF;
3203
3204 l_update_cursor := dbms_sql.open_cursor;
3205
3206 dbms_sql.parse (l_update_cursor, p_update_stmt,dbms_sql.v7);
3207
3208 dbms_sql.bind_array (l_update_cursor,':l_receipt_num_array',p_cr_number_array);
3209 dbms_sql.bind_array (l_update_cursor,':l_receipt_id_array',p_cr_id_array);
3210 dbms_sql.bind_array (l_update_cursor,':l_gt_id_array',p_gt_id_array);
3211
3212 IF p_creation_rule = 'PER_INVOICE' THEN
3213 dbms_sql.bind_array (l_update_cursor,':l_ps_id_array',p_ps_id_array);
3214
3215 ELSIF p_creation_rule = 'PER_CUSTOMER' THEN
3216 dbms_sql.bind_array (l_update_cursor,':l_paying_customer_id_array',p_paying_customer_id_array);
3217 dbms_sql.bind_array (l_update_cursor,':l_pmt_instrument_array',p_pmt_instrument_array);
3218
3219 ELSIF p_creation_rule = 'PER_SITE' THEN
3220 dbms_sql.bind_array (l_update_cursor,':l_paying_customer_id_array',p_paying_customer_id_array);
3221 dbms_sql.bind_array (l_update_cursor,':l_pmt_instrument_array',p_pmt_instrument_array);
3222 dbms_sql.bind_array (l_update_cursor,':l_paying_site_use_id_array',p_paying_site_use_id_array);
3223
3224 ELSIF (p_creation_rule = 'PER_SITE_DUE_DATE') THEN
3225 dbms_sql.bind_array (l_update_cursor,':l_paying_customer_id_array',p_paying_customer_id_array);
3226 dbms_sql.bind_array (l_update_cursor,':l_pmt_instrument_array',p_pmt_instrument_array);
3227 dbms_sql.bind_array (l_update_cursor,':l_paying_site_use_id_array',p_paying_site_use_id_array);
3228 dbms_sql.bind_array (l_update_cursor,':l_due_date_array',p_due_date_array);
3229
3230 ELSIF (p_creation_rule = 'PER_CUSTOMER_DUE_DATE') THEN
3231 dbms_sql.bind_array (l_update_cursor,':l_paying_customer_id_array',p_paying_customer_id_array);
3232 dbms_sql.bind_array (l_update_cursor,':l_pmt_instrument_array',p_pmt_instrument_array);
3233 dbms_sql.bind_array (l_update_cursor,':l_due_date_array',p_due_date_array);
3234
3235 ELSIF (p_creation_rule = 'PAYMENT_CHANNEL_CODE') THEN
3236 dbms_sql.bind_array (l_update_cursor,':l_paying_customer_id_array',p_paying_customer_id_array);
3237 dbms_sql.bind_array (l_update_cursor,':l_trxn_extension_id_array',p_trxn_extension_id_array);
3238 dbms_sql.bind_array (l_update_cursor,':l_pmt_channel_code_array',p_pmt_channel_code_array);
3239 dbms_sql.bind_array (l_update_cursor,':l_cust_bank_acct_id_array',p_cust_bank_acct_id_array);
3240
3241 ELSIF (p_creation_rule = 'PAYMENT_INSTRUMENT') THEN
3242 dbms_sql.bind_array (l_update_cursor,':l_paying_customer_id_array',p_paying_customer_id_array);
3243 dbms_sql.bind_array (l_update_cursor,':l_trxn_extension_id_array',p_trxn_extension_id_array);
3244 dbms_sql.bind_array (l_update_cursor,':l_pmt_instrument_array',p_pmt_instrument_array);
3245
3246 ELSIF (p_creation_rule = 'AUTHORIZATION_ID') THEN
3247 dbms_sql.bind_array (l_update_cursor,':l_paying_customer_id_array',p_paying_customer_id_array);
3248 dbms_sql.bind_array (l_update_cursor,':l_trxn_extension_id_array',p_trxn_extension_id_array);
3249 dbms_sql.bind_array (l_update_cursor,':l_authorization_id_array',p_authorization_id_array);
3250 END IF;
3251
3252 l_dummy := dbms_sql.execute(l_update_cursor);
3253
3254 dbms_sql.close_cursor(l_update_cursor);
3255
3256 IF PG_DEBUG in ('Y', 'C') THEN
3257 arp_debug.debug('Rows updated :'||SQL%ROWCOUNT);
3258 arp_debug.debug('update_ar_receipts_gt()-');
3259 END IF;
3260
3261 EXCEPTION
3262 WHEN OTHERS THEN
3263 if dbms_sql.is_open(l_update_cursor) then
3264 dbms_sql.close_cursor(l_update_cursor);
3265 end if;
3266 arp_debug.debug('update_ar_receipts_gt : error code() '|| to_char(SQLCODE));
3267
3268 insert_exceptions( p_batch_id => NVL(g_batch_id, -333),
3269 p_request_id => arp_standard.profile.request_id,
3270 p_exception_code => 'AUTORECERR',
3271 p_additional_message => 'update_ar_receipts_gt() '|| SQLERRM );
3272
3273 END update_ar_receipts_gt;
3274
3275
3276 PROCEDURE build_queries(p_creation_rule IN ar_receipt_methods.receipt_creation_rule_code%TYPE,
3277 p_approval_mode IN VARCHAR2,
3278 p_create_query OUT NOCOPY VARCHAR2,
3279 p_update_stmt OUT NOCOPY VARCHAR2,
3280 p_inv_rct_mp_qry OUT NOCOPY VARCHAR2 ) IS
3281
3282 l_update_where_clause VARCHAR2(2000);
3283
3284 BEGIN
3285 IF PG_DEBUG in ('Y', 'C') THEN
3286 arp_debug.debug('build_queries()+');
3287 arp_debug.debug('p_creation_rule '||p_creation_rule);
3288 arp_debug.debug('p_approval_mode '||p_approval_mode);
3289 END IF;
3290
3291 p_update_stmt := 'UPDATE /*+INDEX(ar_receipts_gt AR_RECEIPTS_GT_N2)*/ '||
3292 ' ar_receipts_gt '||
3293 ' SET receipt_number = :l_receipt_num_array, '||
3294 ' cash_receipt_id = :l_receipt_id_array, '||
3295 ' gt_id = :l_gt_id_array ';
3296
3297 IF p_approval_mode = 'RE-APPROVAL' THEN
3298 p_inv_rct_mp_qry := '
3299 insert into ar_receipts_gt
3300 ( payment_schedule_id,
3301 customer_trx_id,
3302 cash_receipt_id,
3303 amount_due_remaining,
3304 gt_id
3305 ) ';
3306
3307 p_create_query := '
3308 select paying_customer_id,
3309 null paying_site_use_id,
3310 null due_date,
3311 null payment_instrument,
3312 null payment_channel_code,
3313 null payment_trxn_extension_id,
3314 null authorization_id,
3315 null amount,
3316 payment_schedule_id,
3317 null customer_bank_account_id,
3318 null instr_assignment_id,
3319 null party_id,
3320 null trx_number,
3321 null cust_min_amount,
3322 cash_receipt_id
3323 from ar_receipts_gt gt ';
3324
3325 END IF;
3326
3327 --set 1
3328 IF (p_creation_rule = 'PER_INVOICE') THEN
3329 IF p_approval_mode = 'RE-APPROVAL' THEN
3330 p_inv_rct_mp_qry := p_inv_rct_mp_qry || '
3331 select /*+ leading(crh) */
3332 ps.payment_schedule_id,
3333 ct.customer_trx_id,
3334 cr.cash_receipt_id,
3335 ps.amount_due_remaining,
3336 -1 gt_id
3337 from ar_cash_receipt_history crh,
3338 ar_cash_receipts cr,
3339 ar_payment_schedules ps,
3340 ra_customer_trx ct,
3341 iby_fndcpt_tx_xe_copies cp
3342 where crh.batch_id = :p_batch_id
3343 and cr.status = ''UNAPP''
3344 and ps.customer_trx_id = ct.customer_trx_id
3345 and ps.selected_for_receipt_batch_id = crh.batch_id
3346 and ct.bill_to_customer_id = cr.pay_from_customer
3347 and crh.cash_receipt_id = cr.cash_receipt_id
3348 and cp.source_trxn_extension_id = ct.payment_trxn_extension_id
3349 and cp.copy_trxn_extension_id = cr.payment_trxn_extension_id';
3350 ELSE
3351 p_create_query :=
3352 'select paying_customer_id,
3353 gt.paying_site_use_id,
3354 null due_date,
3355 null payment_instrument,
3356 gt.payment_channel_code,
3357 gt.payment_trxn_extension_id,
3358 null authorization_id,
3359 sum(gt.amount_due_remaining) amount,
3360 gt.payment_schedule_id,
3361 null customer_bank_account_id,
3362 ext.instr_assignment_id,
3363 hca.party_id,
3364 trx_number||''-''||to_char(terms_sequence_number) trx_number,
3365 MAX(gt.cust_min_amount) cust_min_amount,
3366 null cash_receipt_id
3367 from ar_receipts_gt gt,
3368 iby_fndcpt_tx_extensions ext,
3369 hz_cust_accounts hca,
3370 ar_payment_schedules ps
3371 where ext.trxn_extension_id = gt.payment_trxn_extension_id
3372 and hca.cust_account_id = gt.paying_customer_id
3373 and ps.payment_schedule_id = gt.payment_schedule_id
3374 group by gt.customer_trx_id,
3375 gt.payment_schedule_id,
3376 gt.paying_customer_id,
3377 gt.payment_trxn_extension_id,
3378 gt.payment_channel_code,
3379 ext.instr_assignment_id,
3380 hca.party_id,
3381 trx_number||''-''||to_char(terms_sequence_number),
3382 gt.paying_site_use_id';
3383
3384
3385 l_update_where_clause := ' WHERE payment_schedule_id = :l_ps_id_array ';
3386 END IF;
3387
3388 --set 2
3389 ELSIF (p_creation_rule = 'PER_CUSTOMER') THEN
3390 IF p_approval_mode = 'RE-APPROVAL' THEN
3391 p_inv_rct_mp_qry := p_inv_rct_mp_qry || '
3392 select /*+ leading(crh) */
3393 ps.payment_schedule_id,
3394 ct.customer_trx_id,
3395 cr.cash_receipt_id,
3396 ps.amount_due_remaining,
3397 -1 gt_id
3398 from ar_cash_receipt_history crh,
3399 ar_cash_receipts cr,
3400 ar_payment_schedules ps,
3401 ra_customer_trx ct
3402 where crh.batch_id = :p_batch_id
3403 and cr.status = ''UNAPP''
3404 and ps.customer_trx_id = ct.customer_trx_id
3405 and ps.selected_for_receipt_batch_id = crh.batch_id
3406 and ct.bill_to_customer_id = cr.pay_from_customer
3407 and crh.cash_receipt_id = cr.cash_receipt_id ';
3408 ELSE
3409 p_create_query :=
3410 'select gt.paying_customer_id,
3411 null paying_site_use_id,
3412 null due_date,
3413 gt.payment_instrument,
3414 null payment_channel_code,
3415 gt.payment_trxn_extension_id,
3416 null authorization_id,
3417 gt.amount,
3418 null payment_schedule_id,
3419 null customer_bank_account_id,
3420 ext.instr_assignment_id,
3421 hca.party_id,
3422 null trx_number,
3423 gt.cust_min_amount,
3424 null cash_receipt_id
3425 from hz_cust_accounts hca,
3426 iby_fndcpt_tx_extensions ext,
3427 ( select paying_customer_id,
3428 payment_instrument,
3429 MIN(payment_trxn_extension_id) payment_trxn_extension_id,
3430 sum(amount_due_remaining) amount,
3431 MAX(cust_min_amount) cust_min_amount
3432 from ar_receipts_gt
3433 group by paying_customer_id,
3434 payment_instrument ) gt
3435 where ext.trxn_extension_id = gt.payment_trxn_extension_id
3436 and hca.cust_account_id = gt.paying_customer_id ';
3437
3438 l_update_where_clause := ' WHERE paying_customer_id =:l_paying_customer_id_array '||
3439 ' AND payment_instrument =:l_pmt_instrument_array ' ;
3440 END IF;
3441
3442 --set 3
3443 ELSIF (p_creation_rule = 'PER_SITE') THEN
3444 IF p_approval_mode = 'RE-APPROVAL' THEN
3445 p_inv_rct_mp_qry := p_inv_rct_mp_qry || '
3446 select /*+ leading(crh) */
3447 ps.payment_schedule_id,
3448 ct.customer_trx_id,
3449 cr.cash_receipt_id,
3450 ps.amount_due_remaining,
3451 -1 gt_id
3452 from ar_cash_receipt_history crh,
3453 ar_cash_receipts cr,
3454 ar_payment_schedules ps,
3455 ra_customer_trx ct
3456 where crh.batch_id = :p_batch_id
3457 and cr.status = ''UNAPP''
3458 and ps.customer_trx_id = ct.customer_trx_id
3459 and ps.selected_for_receipt_batch_id = crh.batch_id
3460 and ct.bill_to_customer_id = cr.pay_from_customer
3461 and crh.cash_receipt_id = cr.cash_receipt_id
3462 AND cr.customer_site_use_id = ct.bill_to_site_use_id ';
3463 ELSE
3464 p_create_query :=
3465 ' select gt.paying_customer_id,
3466 gt.paying_site_use_id,
3467 null due_date,
3468 gt.payment_instrument,
3469 null payment_channel_code,
3470 gt.payment_trxn_extension_id,
3471 null authorization_id,
3472 gt.amount,
3473 null payment_schedule_id,
3474 null customer_bank_account_id,
3475 ext.instr_assignment_id,
3476 hca.party_id,
3477 null trx_number,
3478 gt.cust_min_amount,
3479 null cash_receipt_id
3480 from hz_cust_accounts hca,
3481 iby_fndcpt_tx_extensions ext,
3482 ( select paying_customer_id,
3483 paying_site_use_id,
3484 payment_instrument,
3485 MIN(payment_trxn_extension_id) payment_trxn_extension_id,
3486 sum(amount_due_remaining) amount,
3487 MAX(cust_min_amount) cust_min_amount
3488 from AR_RECEIPTS_GT
3489 group by paying_customer_id,
3490 paying_site_use_id,
3491 payment_instrument ) gt
3492 where ext.trxn_extension_id = gt.payment_trxn_extension_id
3493 and hca.cust_account_id = gt.paying_customer_id ';
3494
3495 l_update_where_clause := ' WHERE paying_customer_id =:l_paying_customer_id_array '||
3496 ' AND paying_site_use_id =:l_paying_site_use_id_array '||
3497 ' AND payment_instrument =:l_pmt_instrument_array ' ;
3498 END IF;
3499 --set 4
3500 ELSIF (p_creation_rule = 'PER_SITE_DUE_DATE') THEN
3501 IF p_approval_mode = 'RE-APPROVAL' THEN
3502 p_inv_rct_mp_qry := p_inv_rct_mp_qry || '
3503 SELECT payment_schedule_id,
3504 customer_trx_id,
3505 cash_receipt_id,
3506 amount_due_remaining,
3507 -1 gt_id
3508 FROM
3509 (
3510 select /*+ leading(ps) */
3511 ps.payment_schedule_id,
3512 ps.customer_trx_id,
3513 cr.cash_receipt_id,
3514 ps.amount_due_remaining,
3515 RANK( ) OVER (PARTITION BY cr.cash_receipt_id
3516 ORDER BY ps.customer_trx_id, cr.amount) rct_rank,
3517 RANK( ) OVER (PARTITION BY ps.customer_trx_id
3518 ORDER BY cr.cash_receipt_id, cr.amount) inv_rank
3519 from ar_cash_receipt_history crh,
3520 ar_cash_receipts cr,
3521 ( SELECT ps.payment_schedule_id,
3522 ps.selected_for_receipt_batch_id,
3523 ct.bill_to_customer_id,
3524 ps.amount_due_remaining,
3525 SUM( ps.amount_due_remaining )
3526 OVER( PARTITION BY ct.bill_to_site_use_id,ps.due_date) group_amount,
3527 ct.bill_to_site_use_id,
3528 ps.customer_trx_id
3529 FROM ar_payment_schedules ps,
3530 ra_customer_trx ct
3531 WHERE ps.customer_trx_id = ct.customer_trx_id
3532 AND ps.selected_for_receipt_batch_id = :batch_id
3533 ) ps
3534 where crh.batch_id = ps.selected_for_receipt_batch_id
3535 and cr.status = ''UNAPP''
3536 and ps.bill_to_customer_id = cr.pay_from_customer
3537 AND ps.group_amount = cr.amount
3538 and crh.cash_receipt_id = cr.cash_receipt_id
3539 AND cr.customer_site_use_id = ps.bill_to_site_use_id
3540 )
3541 WHERE rct_rank = inv_rank ';
3542 ELSE
3543 p_create_query :=
3544 ' select gt.paying_customer_id,
3545 gt.paying_site_use_id,
3546 gt.due_date,
3547 gt.payment_instrument,
3548 null payment_channel_code,
3549 gt.payment_trxn_extension_id,
3550 null authorization_id,
3551 gt.amount,
3552 null payment_schedule_id,
3553 null customer_bank_account_id,
3554 ext.instr_assignment_id,
3555 hca.party_id,
3556 null trx_number,
3557 gt.cust_min_amount,
3558 null cash_receipt_id
3559 from hz_cust_accounts hca,
3560 iby_fndcpt_tx_extensions ext,
3561 ( select paying_customer_id,
3562 paying_site_use_id,
3563 payment_instrument,
3564 due_date,
3565 MIN(payment_trxn_extension_id) payment_trxn_extension_id,
3566 sum(amount_due_remaining) amount,
3567 MAX(cust_min_amount) cust_min_amount
3568 from AR_RECEIPTS_GT
3569 group by paying_customer_id,
3570 due_date,
3571 paying_site_use_id,
3572 payment_instrument ) gt
3573 where ext.trxn_extension_id = gt.payment_trxn_extension_id
3574 and hca.cust_account_id = gt.paying_customer_id ';
3575
3576 l_update_where_clause := ' WHERE paying_customer_id =:l_paying_customer_id_array '||
3577 ' AND paying_site_use_id =:l_paying_site_use_id_array '||
3578 ' AND due_date =:l_due_date_array'||
3579 ' AND payment_instrument =:l_pmt_instrument_array ' ;
3580 END IF;
3581 --set 5
3582 ELSIF (p_creation_rule = 'PER_CUSTOMER_DUE_DATE') THEN
3583 IF p_approval_mode = 'RE-APPROVAL' THEN
3584 p_inv_rct_mp_qry := p_inv_rct_mp_qry || '
3585 SELECT payment_schedule_id,
3586 customer_trx_id,
3587 cash_receipt_id,
3588 amount_due_remaining,
3589 -1 gt_id
3590 FROM
3591 (
3592 select /*+ leading(ps) */
3593 ps.payment_schedule_id,
3594 ps.customer_trx_id,
3595 cr.cash_receipt_id,
3596 ps.amount_due_remaining,
3597 RANK( ) OVER (PARTITION BY cr.cash_receipt_id
3598 ORDER BY ps.customer_trx_id, cr.amount) rct_rank,
3599 RANK( ) OVER (PARTITION BY ps.customer_trx_id
3600 ORDER BY cr.cash_receipt_id, cr.amount) inv_rank
3601 from ar_cash_receipt_history crh,
3602 ar_cash_receipts cr,
3603 ( SELECT ps.payment_schedule_id,
3604 ps.selected_for_receipt_batch_id,
3605 ct.bill_to_customer_id,
3606 ps.amount_due_remaining,
3607 SUM( ps.amount_due_remaining )
3608 OVER( PARTITION BY ct.bill_to_customer_id,ps.due_date) group_amount,
3609 ps.customer_trx_id
3610 FROM ar_payment_schedules ps,
3611 ra_customer_trx ct
3612 WHERE ps.customer_trx_id = ct.customer_trx_id
3613 AND ps.selected_for_receipt_batch_id = :batch_id
3614 ) ps
3615 where crh.batch_id = ps.selected_for_receipt_batch_id
3616 and cr.status = ''UNAPP''
3617 and ps.bill_to_customer_id = cr.pay_from_customer
3618 AND ps.group_amount = cr.amount
3619 and crh.cash_receipt_id = cr.cash_receipt_id
3620 )
3621 WHERE rct_rank = inv_rank ';
3622 ELSE
3623 p_create_query :=
3624 ' select gt.paying_customer_id,
3625 null paying_site_use_id,
3626 gt.due_date,
3627 gt.payment_instrument,
3628 null payment_channel_code,
3629 gt.payment_trxn_extension_id,
3630 null authorization_id,
3631 gt.amount,
3632 null payment_schedule_id,
3633 null customer_bank_account_id,
3634 ext.instr_assignment_id,
3635 hca.party_id,
3636 null trx_number,
3637 gt.cust_min_amount,
3638 null cash_receipt_id
3639 from hz_cust_accounts hca,
3640 iby_fndcpt_tx_extensions ext,
3641 ( select paying_customer_id,
3642 payment_instrument,
3643 due_date,
3644 MIN(payment_trxn_extension_id) payment_trxn_extension_id,
3645 sum(amount_due_remaining) amount,
3646 MAX(cust_min_amount) cust_min_amount
3647 from AR_RECEIPTS_GT
3648 group by paying_customer_id,
3649 due_date,
3650 payment_instrument ) gt
3651 where ext.trxn_extension_id = gt.payment_trxn_extension_id
3652 and hca.cust_account_id = gt.paying_customer_id ';
3653
3654 l_update_where_clause := ' WHERE paying_customer_id =:l_paying_customer_id_array '||
3655 ' AND due_date =:l_due_date_array'||
3656 ' AND payment_instrument =:l_pmt_instrument_array ' ;
3657 END IF;
3658 --set 6
3659 ELSIF (p_creation_rule = 'PAYMENT_CHANNEL_CODE') THEN
3660 p_create_query :=
3661 ' select gt.paying_customer_id,
3662 null paying_site_use_id,
3663 null due_date,
3664 null payment_instrument,
3665 gt.payment_channel_code,
3666 gt.payment_trxn_extension_id,
3667 null authorization_id,
3668 gt.amount,
3669 null payment_schedule_id,
3670 gt.customer_bank_account_id,
3671 ext.instr_assignment_id,
3672 hca.party_id,
3673 null trx_number,
3674 gt.cust_min_amount,
3675 null cash_receipt_id
3676 from hz_cust_accounts hca,
3677 iby_fndcpt_tx_extensions ext,
3678 ( select paying_customer_id,
3679 payment_trxn_extension_id,
3680 sum(amount_due_remaining) amount,
3681 customer_bank_account_id,
3682 payment_channel_code,
3683 MAX(cust_min_amount) cust_min_amount
3684 from AR_RECEIPTS_GT
3685 group by paying_customer_id,
3686 payment_trxn_extension_id,
3687 customer_bank_account_id,
3688 payment_channel_code) gt
3689 where ext.trxn_extension_id = gt.payment_trxn_extension_id
3690 and hca.cust_account_id = gt.paying_customer_id ';
3691
3692 l_update_where_clause := ' WHERE paying_customer_id =:l_paying_customer_id_array '||
3693 ' AND payment_trxn_extension_id =:l_trxn_extension_id_array'||
3694 ' AND payment_channel_code =:l_pmt_channel_code_array'||
3695 ' AND customer_bank_account_id =:l_cust_bank_acct_id_array' ;
3696 --set 7
3697 ELSIF (p_creation_rule = 'PAYMENT_INSTRUMENT') THEN
3698 p_create_query :=
3699 ' select gt.paying_customer_id,
3700 null paying_site_use_id,
3701 null due_date,
3702 gt.payment_instrument,
3703 null payment_channel_code,
3704 gt.payment_trxn_extension_id,
3705 null authorization_id,
3706 gt.amount,
3707 null payment_schedule_id,
3708 null customer_bank_account_id,
3709 ext.instr_assignment_id,
3710 hca.party_id,
3711 null trx_number,
3712 gt.cust_min_amount,
3713 null cash_receipt_id
3714 from hz_cust_accounts hca,
3715 iby_fndcpt_tx_extensions ext,
3716 ( select paying_customer_id,
3717 payment_trxn_extension_id,
3718 sum(amount_due_remaining) amount,
3719 payment_instrument,
3720 MAX(cust_min_amount) cust_min_amount
3721 from AR_RECEIPTS_GT
3722 group by paying_customer_id,
3723 payment_trxn_extension_id,
3724 payment_instrument) gt
3725 where ext.trxn_extension_id = gt.payment_trxn_extension_id
3726 and hca.cust_account_id = gt.paying_customer_id ';
3727
3728 l_update_where_clause := ' WHERE paying_customer_id =:l_paying_customer_id_array '||
3729 ' AND payment_trxn_extension_id =:l_trxn_extension_id_array'||
3730 ' AND payment_instrument =:l_pmt_instrument_array ' ;
3731 --set 8
3732 ELSIF (p_creation_rule = 'AUTHORIZATION_ID') THEN
3733 p_create_query :=
3734 ' select gt.paying_customer_id,
3735 null paying_site_use_id,
3736 null due_date,
3737 null payment_instrument,
3738 null payment_channel_code,
3739 gt.payment_trxn_extension_id,
3740 gt.authorization_id,
3741 gt.amount,
3742 null payment_schedule_id,
3743 null customer_bank_account_id,
3744 ext.instr_assignment_id,
3745 hca.party_id,
3746 null trx_number,
3747 gt.cust_min_amount,
3748 null cash_receipt_id
3749 from hz_cust_accounts hca,
3750 iby_fndcpt_tx_extensions ext,
3751 ( select paying_customer_id,
3752 payment_trxn_extension_id,
3753 sum(amount_due_remaining) amount,
3754 authorization_id,
3755 MAX(cust_min_amount) cust_min_amount
3756 from AR_RECEIPTS_GT
3757 group by paying_customer_id,
3758 payment_trxn_extension_id,
3759 authorization_id) gt
3760 where ext.trxn_extension_id = gt.payment_trxn_extension_id
3761 and hca.cust_account_id = gt.paying_customer_id ';
3762
3763 l_update_where_clause := ' WHERE paying_customer_id =:l_paying_customer_id_array '||
3764 ' AND payment_trxn_extension_id =:l_trxn_extension_id_array'||
3765 ' AND authorization_id =:l_authorization_id_array' ;
3766 END IF;
3767
3768 p_update_stmt := p_update_stmt || l_update_where_clause;
3769
3770 IF PG_DEBUG in ('Y', 'C') THEN
3771 arp_debug.debug('p_create_query '||p_create_query);
3772 arp_debug.debug('p_update_stmt '||p_update_stmt);
3773 arp_debug.debug('p_inv_rct_mp_qry '||p_inv_rct_mp_qry);
3774 arp_debug.debug('build_queries()-');
3775 END IF;
3776
3777 EXCEPTION
3778 WHEN OTHERS THEN
3779 arp_debug.debug('build_queries : error code() '|| to_char(SQLCODE));
3780 raise;
3781 END build_queries;
3782
3783
3784
3785 --validate and populate the receipt numbers
3786 PROCEDURE populate_receipt_numbers( p_rcpt_method_name IN ar_receipt_methods.name%TYPE,
3787 p_sob_id IN ar_batches.set_of_books_id%TYPE,
3788 p_batch_date IN DATE,
3789 p_receipt_method_id IN NUMBER,
3790 p_receipt_num_array OUT NOCOPY DBMS_SQL.VARCHAR2_TABLE,
3791 p_count IN NUMBER) IS
3792 l_error_flag VARCHAR2(1) := 'N';
3793 l_status NUMBER;
3794 l_doc_sequence_value ra_customer_trx.doc_sequence_value%TYPE;
3795 l_doc_sequence_id ra_customer_trx.doc_sequence_id%TYPE;
3796 BEGIN
3797 IF PG_DEBUG in ('Y', 'C') THEN
3798 arp_debug.debug('populate_receipt_numbers()+');
3799 arp_debug.debug('p_rcpt_method_name :'||p_rcpt_method_name);
3800 arp_debug.debug('p_receipt_method_id :'||p_receipt_method_id);
3801 arp_debug.debug('p_sob_id :'||p_sob_id);
3802 arp_debug.debug('p_batch_date :'||p_batch_date);
3803 END IF;
3804
3805 FOR i IN 1..p_count LOOP
3806 l_status := FND_SEQNUM.GET_SEQ_VAL(
3807 222,
3808 p_rcpt_method_name,
3809 p_sob_id,
3810 'A',
3811 p_batch_date,
3812 l_doc_sequence_value,
3813 l_doc_sequence_id,
3814 'Y',
3815 'Y');
3816
3817 l_error_flag := 'N';
3818
3819 IF PG_DEBUG in ('Y', 'C') THEN
3820 arp_debug.debug(' l_status '||l_status);
3821 END IF;
3822
3823 IF l_status = FND_SEQNUM.SEQSUCC AND
3824 l_doc_sequence_value is NULL THEN
3825 arp_debug.debug ( 'ERROR!!!! l_doc_sequence_value is not generated');
3826 l_error_flag := 'Y';
3827
3828 ELSIF l_status = FND_SEQNUM.NOASSIGN THEN
3829 arp_debug.debug ( 'ERROR THERE ARE NO DOC SEQ ASSIGNMENT!!!!');
3830 l_error_flag := 'Y';
3831 ELSIF l_status <> FND_SEQNUM.SEQSUCC THEN
3832 arp_debug.debug ( 'ERROR ERROR IN FND ROUTINE !!!!');
3833 l_error_flag := 'Y';
3834 END IF;
3835
3836 IF l_error_flag = 'Y' THEN
3837 exit;
3838 END IF;
3839
3840 --Bug 7242683 removed concatenation of receipt_method_id
3841 p_receipt_num_array(i) := l_doc_sequence_value;
3842 END LOOP;
3843
3844 IF PG_DEBUG in ('Y', 'C') THEN
3845 arp_debug.debug('populate_receipt_numbers()-');
3846 END IF;
3847
3848 EXCEPTION
3849 WHEN OTHERS THEN
3850 arp_debug.debug('Exception : populate_receipt_numbers() '|| SQLERRM);
3851 G_ERROR := 'Y';
3852 END populate_receipt_numbers;
3853
3854
3855
3856
3857 PROCEDURE process_selected_receipts( p_receipt_method_id IN ar_cash_receipts.receipt_method_id%TYPE,
3858 p_batch_id IN ar_batches.batch_id%TYPE,
3859 p_approval_mode IN VARCHAR2 DEFAULT 'APPROVE' ) IS
3860
3861 l_set_of_books_id ar_batches.set_of_books_id%TYPE;
3862 l_name ar_receipt_methods.name%TYPE;
3863 l_batch_date ar_batches.batch_date%TYPE;
3864 l_gl_date ar_batches.gl_date%TYPE;
3865 l_currency_code ar_batches.currency_code%TYPE;
3866 l_rec_creation_rule_code ar_receipt_methods.receipt_creation_rule_code%TYPE;
3867 l_receipt_number ar_cash_receipts.receipt_number%TYPE;
3868 l_called_from VARCHAR2(15);
3869 l_cash_receipt_id NUMBER(15);
3870 l_installment NUMBER;
3871 l_create_stmt VARCHAR2(2000);
3872 l_update_stmt VARCHAR2(2000);
3873 l_error_flag VARCHAR2(1) := 'N';
3874 l_return_status VARCHAR2(1);
3875 l_err_code VARCHAR2(240);
3876 l_err_text VARCHAR2(240);
3877 l_process_payment_flag NUMBER;
3878 l_create_stmt_c INTEGER;
3879 l_rows_fetched INTEGER;
3880 l_index INTEGER;
3881 l_gt_id INTEGER;
3882
3883 l_amt_applied NUMBER;
3884 l_cust_site_min_rec_amt NUMBER;
3885 l_rec_method_min_rec_amt NUMBER;
3886 l_min_rec_amount_allowed NUMBER;
3887 l_creation_method_code ar_receipt_classes.creation_method_code%type;
3888 l_remittance_bank_account_id ar_receipt_method_accounts_all.remit_bank_acct_use_id%TYPE;
3889 l_state ar_receipt_classes.creation_status%TYPE;
3890 l_old_cash_receipt_id ar_cash_receipts.cash_receipt_id%TYPE;
3891
3892 l_paying_customer_id_array DBMS_SQL.NUMBER_TABLE;
3893 l_paying_site_use_id_array DBMS_SQL.NUMBER_TABLE;
3894 l_due_date_array DBMS_SQL.DATE_TABLE;
3895 l_pmt_instrument_array DBMS_SQL.VARCHAR2_TABLE;
3896 l_pmt_channel_code_array DBMS_SQL.VARCHAR2_TABLE;
3897 l_pmt_trxn_extn_id_array DBMS_SQL.NUMBER_TABLE;
3898 l_authorization_id_array DBMS_SQL.NUMBER_TABLE;
3899 l_cust_bank_acct_id_array DBMS_SQL.NUMBER_TABLE;
3900 l_amount_array DBMS_SQL.NUMBER_TABLE;
3901 l_receipt_id_array DBMS_SQL.NUMBER_TABLE;
3902 l_receipt_num_array DBMS_SQL.VARCHAR2_TABLE;
3903 l_ps_id_array DBMS_SQL.NUMBER_TABLE;
3904 l_gt_id_array DBMS_SQL.NUMBER_TABLE;
3905
3906 l_err_rcpt_num_array DBMS_SQL.VARCHAR2_TABLE;
3907 l_err_code_array DBMS_SQL.VARCHAR2_TABLE;
3908 l_err_rcpt_index INTEGER;
3909 l_cc_err_code_array DBMS_SQL.VARCHAR2_TABLE;
3910 l_cc_err_text_array DBMS_SQL.VARCHAR2_TABLE;
3911
3912 l_apply_fail VARCHAR2(1) := 'N';
3913 l_pay_process_fail VARCHAR2(1) := 'N';
3914 l_exchange_rate ar_batches.exchange_rate%TYPE;
3915 l_exchange_date ar_batches.exchange_date%TYPE;
3916 l_exchange_rate_type ar_batches.exchange_rate_type%TYPE;
3917 l_rec_inher_inv_num_flag VARCHAR2(1) := 'N';
3918
3919 l_cc_error_code VARCHAR2(30);
3920 l_cc_error_text VARCHAR2(2000);
3921
3922 l_inv_rct_mp_qry VARCHAR2(2000);
3923
3924 TYPE rcpt_info_type IS REF CURSOR;
3925 rcpt_info_cursor rcpt_info_type;
3926
3927 TYPE rcpt_info_rec_type IS RECORD
3928 ( paying_customer_id number,
3929 paying_site_use_id number,
3930 due_date date,
3931 payment_instrument varchar2(30),
3932 payment_channel_code varchar2(30),
3933 payment_trxn_extension_id number,
3934 authorization_id number,
3935 amount number,
3936 payment_schedule_id number,
3937 customer_bank_account_id number,
3938 instr_assignment_id number,
3939 party_id number,
3940 trx_number varchar2(30),
3941 cust_min_amount number,
3942 cash_receipt_id number);
3943
3944 TYPE rcpt_info_rec IS TABLE OF rcpt_info_rec_type INDEX BY BINARY_INTEGER;
3945 l_rcpt_info_tab rcpt_info_rec;
3946
3947 TYPE rcpt_appl_tab IS TABLE OF receipt_info_rec INDEX BY BINARY_INTEGER;
3948 l_rcpt_appl_tab rcpt_appl_tab;
3949
3950 l_from_cr_id NUMBER;
3951 l_to_cr_id NUMBER;
3952 l_cnt NUMBER;
3953 l_msg_data VARCHAR2(2000);
3954 l_msg_count NUMBER;
3955
3956 l_unbal_rcpt_tab ARP_BALANCE_CHECK.unbalanced_receipts;
3957
3958 CURSOR unbal_rec_applns( p_cr_id NUMBER, p_gt_id NUMBER) IS
3959 SELECT ard.source_id, ard.source_id_secondary, trx.upgrade_method,
3960 ra.applied_customer_trx_id, ra.payment_schedule_id, ra.applied_payment_schedule_id
3961 FROM ar_receivable_applications ra,
3962 ar_distributions ard,
3963 ra_customer_trx trx,
3964 ar_receipts_gt rgt
3965 WHERE ra.cash_receipt_id = p_cr_id
3966 AND ra.applied_customer_trx_id IS NOT NULL
3967 AND ard.source_id_secondary = ra.receivable_application_id
3968 AND ard.source_table_secondary = 'RA'
3969 AND ard.source_table = 'RA'
3970 AND trx.customer_trx_id = ra.applied_customer_trx_id
3971 AND rgt.cash_receipt_id = p_cr_id
3972 AND rgt.gt_id = p_gt_id;
3973
3974
3975 CURSOR appl_rec_cur( p_gt_id NUMBER) IS
3976 SELECT /*+INDEX(rgt AR_RECEIPTS_GT_N1) INDEX(inv_ps AR_PAYMENT_SCHEDULES_U1) INDEX(ps AR_PAYMENT_SCHEDULES_U2) */
3977 cr.pay_from_customer customer_id,
3978 crh.gl_date cr_gl_date,
3979 cr.amount cr_amount,
3980 cr.customer_site_use_id cust_site_use_id,
3981 cr.receipt_date ,
3982 cr.currency_code cr_currency_code,
3983 cr.exchange_rate cr_exchange_rate,
3984 ps.payment_schedule_id cr_payment_schedule_id,
3985 cr.remit_bank_acct_use_id remittance_bank_account_id,
3986 cr.receipt_method_id,
3987 cr.cash_receipt_id,
3988 inv_ps.amount_due_remaining inv_bal_amount,
3989 inv_ps.amount_due_original inv_orig_amount,
3990 ctt.allow_overapplication_flag allow_over_app,
3991 rma.unapplied_ccid,
3992 ed.code_combination_id ed_disc_ccid,
3993 uned.code_combination_id uned_disc_ccid,
3994 crh.batch_id,
3995 rgt.customer_trx_id,
3996 (select 'Y' rev_rec_flag
3997 from ra_customer_trx_lines ctl
3998 where ctl.customer_trx_id = rgt.customer_trx_id
3999 and ctl.autorule_complete_flag||'' = 'N'
4000 and rownum = 1 ) rev_rec_flag,
4001 (select 'Y' def_tax_flag
4002 from ra_cust_trx_line_gl_dist gld
4003 where gld.account_class = 'TAX'
4004 and gld.customer_trx_id = rgt.customer_trx_id
4005 and gld.collected_tax_ccid IS NOT NULL
4006 and rownum = 1 ) def_tax_flag,
4007 ot.cust_trx_type_id cust_trx_type_id ,
4008 ot.trx_due_date trx_due_date,
4009 ot.invoice_currency_code trx_currency_code,
4010 ot.trx_exchange_rate trx_exchange_rate,
4011 ot.trx_date trx_date ,
4012 ot.trx_gl_date trx_gl_date,
4013 ot.calc_discount_on_lines_flag calc_discount_on_lines_flag,
4014 ot.partial_discount_flag partial_discount_flag,
4015 ot.allow_overapplication_flag allow_overappln_flag,
4016 ot.natural_application_only_flag natural_appln_only_flag,
4017 ot.creation_sign creation_sign,
4018 ot.payment_schedule_id applied_payment_schedule_id,
4019 greatest(crh.gl_date,ot.trx_gl_date,
4020 decode(ar_receipt_lib_pvt.pg_profile_appln_gl_date_def,
4021 'INV_REC_SYS_DT', sysdate,
4022 'INV_REC_DT', ot.trx_gl_date,
4023 ot.trx_gl_date)) ot_gl_date,
4024 ot.term_id term_id,
4025 ot.amount_due_original amount_due_original,
4026 ot.amount_line_items_original amount_line_items_original,
4027 arp_util.CurrRound(ot.balance_due_curr_unformatted,
4028 ot.invoice_currency_code) amount_due_remaining,
4029 ot.discount_taken_earned discount_taken_earned,
4030 ot.discount_taken_unearned discount_taken_unearned,
4031 ot.amount_line_items_original line_items_original,
4032 ot.amount_line_items_remaining line_items_remaining,
4033 ot.tax_original tax_original,
4034 ot.tax_remaining tax_remaining,
4035 ot.freight_original freight_original,
4036 ot.freight_remaining freight_remaining,
4037 Null rec_charges_charged,
4038 ot.receivables_charges_remaining rec_charges_remaining,
4039 ot.location location,
4040 rgt.amount_due_remaining amount_apply
4041 FROM ar_cash_receipts cr,
4042 ar_cash_receipt_history crh,
4043 ar_payment_schedules ps,
4044 ra_cust_trx_types ctt,
4045 ar_payment_schedules inv_ps,
4046 ar_receipt_method_accounts rma,
4047 ar_receivables_trx ed,
4048 ar_receivables_trx uned,
4049 ar_open_trx_v ot,
4050 ar_receipts_gt rgt
4051 WHERE rgt.cash_receipt_id IS NOT NULL
4052 AND rgt.cash_receipt_id = cr.cash_receipt_id
4053 AND cr.cash_receipt_id = crh.cash_receipt_id
4054 AND cr.cash_receipt_id = ps.cash_receipt_id
4055 AND inv_ps.payment_schedule_id = rgt.payment_schedule_id
4056 AND inv_ps.cust_trx_type_id = ctt.cust_trx_type_id
4057 AND crh.current_record_flag = 'Y'
4058 AND rma.receipt_method_id = cr.receipt_method_id
4059 AND rma.remit_bank_acct_use_id = cr.remit_bank_acct_use_id
4060 AND ot.payment_schedule_id = rgt.payment_schedule_id
4061 AND rgt.gt_id = p_gt_id
4062 AND rma.edisc_receivables_trx_id = ed.receivables_trx_id (+)
4063 AND rma.unedisc_receivables_trx_id = uned.receivables_trx_id (+)
4064 ORDER BY rgt.cash_receipt_id,
4065 rgt.amount_due_remaining;
4066
4067
4068 CURSOR rec_ps_cur(l_rec_num VARCHAR2) IS
4069 SELECT payment_schedule_id,
4070 paying_customer_id,
4071 customer_trx_id,
4072 cash_receipt_id
4073 FROM ar_receipts_gt
4074 WHERE gt_id = l_gt_id
4075 AND receipt_number = l_rec_num;
4076
4077 BEGIN
4078 IF PG_DEBUG in ('Y', 'C') THEN
4079 arp_debug.debug('process_selected_receipts()+');
4080 arp_debug.debug('batch_id '||p_batch_id);
4081 arp_debug.debug('p_receipt_method_id '||p_receipt_method_id);
4082 END IF;
4083
4084 pg_request_id := arp_standard.profile.request_id;
4085
4086 IF PG_DEBUG in ('Y', 'C') THEN
4087 arp_debug.debug('request_id :'||pg_request_id);
4088 END IF;
4089
4090 /** set the called_from value based on receipt method properties.If confirm_flag
4091 value associated to current receipt method is N then l_called_from value is
4092 set to AUTORECAPI2, this will enable us to skip certain flows like accounting
4093 creation and payment processing. */
4094 SELECT DECODE(rc.confirm_flag,'Y','AUTORECAPI','AUTORECAPI2'),
4095 nvl(rm.receipt_creation_rule_code,'MANUAL'),
4096 nvl(rm.receipt_inherit_inv_num_flag,'N')
4097 INTO l_called_from,
4098 l_rec_creation_rule_code,
4099 l_rec_inher_inv_num_flag
4100 FROM ar_receipt_classes rc,
4101 ar_receipt_methods rm
4102 WHERE rm.receipt_method_id = p_receipt_method_id
4103 AND rm.receipt_class_id = rc.receipt_class_id;
4104
4105 SELECT b.set_of_books_id,
4106 r.name,
4107 b.batch_date,
4108 b.gl_date, /*Bug 13146325*/
4109 b.currency_code,
4110 DECODE(exchange_rate_type,'User',exchange_rate,NULL),
4111 exchange_date,
4112 exchange_rate_type
4113 INTO l_set_of_books_id,
4114 l_name,
4115 l_batch_date,
4116 l_gl_date, /*Bug 13146325*/
4117 l_currency_code,
4118 l_exchange_rate,
4119 l_exchange_date,
4120 l_exchange_rate_type
4121 FROM ar_batches b,
4122 ar_receipt_methods r
4123 WHERE b.batch_id = p_batch_id
4124 AND r.receipt_method_id = p_receipt_method_id
4125 AND b.receipt_method_id = r.receipt_method_id;
4126
4127 --set the process payment flag
4128 IF nvl(l_called_from,'NONE') <> 'AUTORECAPI' THEN
4129 l_process_payment_flag := 1;
4130 END IF;
4131
4132 IF PG_DEBUG in ('Y', 'C') THEN
4133 arp_debug.debug('p_creation_rule '|| l_rec_creation_rule_code);
4134 arp_debug.debug('l_called_from '|| l_called_from);
4135 arp_debug.debug('l_process_payment_flag '|| l_process_payment_flag);
4136 arp_debug.debug('l_rec_inher_inv_num_flag '|| l_rec_inher_inv_num_flag);
4137 arp_debug.debug('l_set_of_books_id '|| l_set_of_books_id);
4138 arp_debug.debug('l_name '|| l_name);
4139 arp_debug.debug('l_batch_date '|| l_batch_date);
4140 arp_debug.debug('l_gl_date '|| l_gl_date);
4141 arp_debug.debug('l_currency_code '|| l_currency_code);
4142 arp_debug.debug('l_exchange_rate '|| l_exchange_rate);
4143 arp_debug.debug('l_exchange_date '|| l_exchange_date);
4144 arp_debug.debug('l_exchange_rate_type '|| l_exchange_rate_type);
4145 END IF;
4146
4147 --populate the query string based on creation_rule_code
4148 build_queries( l_rec_creation_rule_code,
4149 p_approval_mode,
4150 l_create_stmt,
4151 l_update_stmt,
4152 l_inv_rct_mp_qry );
4153
4154 IF p_approval_mode = 'RE-APPROVAL' THEN
4155 IF PG_DEBUG in ('Y', 'C') THEN
4156 arp_debug.debug('Executing invoice receipt mapping query for batch '||p_batch_id);
4157 END IF;
4158
4159 EXECUTE IMMEDIATE l_inv_rct_mp_qry USING p_batch_id;
4160 IF PG_DEBUG in ('Y', 'C') THEN
4161 arp_debug.debug('Number of rows inserted '||SQL%ROWCOUNT);
4162 END IF;
4163 END IF;
4164
4165 IF PG_DEBUG in ('Y', 'C') THEN
4166 arp_debug.debug('ar_receipts_gt dump 1');
4167 dump_ar_receipts_gt;
4168 END IF;
4169
4170 OPEN rcpt_info_cursor FOR l_create_stmt;
4171
4172 l_index := 0;
4173
4174 LOOP
4175 FETCH rcpt_info_cursor BULK COLLECT INTO l_rcpt_info_tab LIMIT MAX_ARRAY_SIZE;
4176
4177 IF PG_DEBUG in ('Y', 'C') THEN
4178 arp_debug.debug('current fetch count '|| l_rcpt_info_tab.count);
4179 END IF;
4180
4181 IF l_rcpt_info_tab.count = 0 THEN
4182 EXIT;
4183 END IF;
4184
4185 /**In order to have intermediate commits with in the process(for better performance and
4186 to support processing of large volumes of data),we have devided the data into various
4187 logical batches based on value of MAX_ARRAY_SIZE.Each of these batch gets processed
4188 and committed to the database before continuing the loop for remaining set of data.
4189
4190 The field gt_id is used for logically seperating the data among different batches.*/
4191 l_gt_id := nvl(l_gt_id,1) + 1;
4192
4193 IF PG_DEBUG in ('Y', 'C') THEN
4194 arp_debug.debug('Value of l_gt_id '|| l_gt_id );
4195 END IF;
4196
4197 IF p_approval_mode <> 'RE-APPROVAL' THEN
4198 /* IF l_rec_creation_rule_code <> 'PER_INVOICE' OR
4199 l_rec_inher_inv_num_flag <> 'Y' THEN
4200 --validate and populate the receipt numbers
4201 populate_receipt_numbers( l_name,
4202 l_set_of_books_id,
4203 l_batch_date,
4204 p_receipt_method_id,
4205 l_receipt_num_array,
4206 l_rcpt_info_tab.count );
4207 END IF;
4208
4209 IF PG_DEBUG in ('Y', 'C') THEN
4210 arp_debug.debug('Receipt numbers populated are '|| l_receipt_num_array.count);
4211 arp_debug.debug('l_rcpt_info_tab.FIRST '|| l_rcpt_info_tab.FIRST);
4212 arp_debug.debug('l_rcpt_info_tab.LAST '|| l_rcpt_info_tab.LAST);
4213 END IF;
4214 */
4215 --reset the MIN AMOUNT CHECK array
4216 l_err_rcpt_index := 0;
4217 l_err_rcpt_num_array.DELETE;
4218
4219 l_from_cr_id := NULL;
4220 l_to_cr_id := NULL;
4221 l_cnt := 0;
4222
4223 --loop over the array to create receipts
4224 FOR i IN l_rcpt_info_tab.FIRST..l_rcpt_info_tab.LAST LOOP
4225
4226 l_receipt_id_array(i) := null;
4227
4228 IF l_rec_creation_rule_code = 'PER_INVOICE' AND
4229 l_rec_inher_inv_num_flag = 'Y' THEN
4230 l_receipt_num_array( i ) := l_rcpt_info_tab(i).trx_number;
4231 ELSE
4232 -- Stamp receipt_number with dummy increasing varaible so that
4233 -- l_err_rcpt_num_array will have receipt_number populated
4234 l_index := l_index + 1;
4235 l_receipt_num_array(i) := l_index;
4236 END IF;
4237
4238 IF PG_DEBUG in ('Y', 'C') THEN
4239 arp_debug.debug('Checking minimum receipt amount setup at customer level');
4240 END IF;
4241
4242 /* Set the minimum receipt amount got from main select query */
4243 l_cust_site_min_rec_amt := l_rcpt_info_tab(i).cust_min_amount;
4244
4245 /* Get the minimum receipt amount for per_customer / due_date from site level */
4246 IF l_rec_creation_rule_code = 'PER_CUSTOMER' OR
4247 l_rec_creation_rule_code = 'PER_CUSTOMER_DUE_DATE' THEN
4248
4249 BEGIN
4250
4251 /* Defaulting paying site for per_customer and per_customer_due_date as it is always NULL*/
4252 IF l_rcpt_info_tab(i).paying_site_use_id IS NULL then
4253
4254 IF PG_DEBUG in ('Y', 'C') THEN
4255 arp_debug.debug('Default paying_site_use_id');
4256 END IF;
4257
4258 SELECT site_use.site_use_id
4259 INTO l_rcpt_info_tab(i).paying_site_use_id
4260 FROM hz_cust_site_uses site_use,
4261 hz_cust_acct_sites acct_site
4262 WHERE acct_site.cust_account_id = l_rcpt_info_tab(i).paying_customer_id
4263 AND acct_site.status = 'A'
4264 AND site_use.cust_acct_site_id = acct_site.cust_acct_site_id
4265 AND site_use.site_use_code = nvl('BILL_TO', site_use.site_use_code)
4266 AND site_use.status = 'A'
4267 AND site_use.primary_flag = 'Y';
4268
4269 END IF;
4270 /* Get the minimum receipt amount for per_customer / due_date defined at site level */
4271 IF PG_DEBUG in ('Y', 'C') THEN
4272 arp_debug.debug('Get Minimum Receipt Amount for the paying_site_use_id');
4273 END IF;
4274
4275 Select cpa.auto_rec_min_receipt_amount
4276 into l_cust_site_min_rec_amt
4277 From hz_customer_profiles cp,
4278 hz_cust_profile_amts cpa
4279 WHERE cp.cust_account_profile_id = cpa.cust_account_profile_id
4280 AND cpa.currency_code = l_currency_code
4281 AND cp.site_use_id = l_rcpt_info_tab(i).paying_site_use_id;
4282
4283 IF l_cust_site_min_rec_amt IS NULL THEN
4284
4285 IF PG_DEBUG in ('Y', 'C') THEN
4286 arp_debug.debug('Min Receipt Amount allowed at site is NULL');
4287 arp_debug.debug('Defaulting Min Receipt Amount allowed from Cust Account Level');
4288 END IF;
4289
4290 l_cust_site_min_rec_amt := l_rcpt_info_tab(i).cust_min_amount;
4291 END IF;
4292
4293 EXCEPTION
4294 WHEN NO_DATA_FOUND THEN
4295 l_err_code := NULL;
4296 WHEN OTHERS THEN
4297 null;
4298 END;
4299 END IF;
4300
4301
4302 BEGIN
4303
4304 IF PG_DEBUG in ('Y', 'C') THEN
4305 arp_debug.debug('Checking minimum receipt amount setup at receipt method bank level');
4306 arp_debug.debug('Calling Default_Receipt_Method_Info');
4307 END IF;
4308
4309 ar_receipt_lib_pvt.Default_Receipt_Method_Info(
4310 p_receipt_method_id => p_receipt_method_id,
4311 p_currency_code => l_currency_code,
4312 p_receipt_date => l_batch_date,
4313 p_remittance_bank_account_id => l_remittance_bank_account_id,
4314 p_state => l_state,
4315 p_creation_method_code => l_creation_method_code,
4316 p_called_from => l_called_from,
4317 p_return_status => l_return_status);
4318
4319 Select min_receipt_amount
4320 into l_rec_method_min_rec_amt
4321 from ar_receipt_method_accounts
4322 where receipt_method_id = p_receipt_method_id
4323 and remit_bank_acct_use_id = l_remittance_bank_account_id;
4324
4325 EXCEPTION
4326 WHEN NO_DATA_FOUND THEN
4327 l_err_code := NULL;
4328 WHEN OTHERS THEN
4329 null;
4330 END;
4331
4332 /* Error if the receipt amount is less than greater of the minimum receipt amount
4333 defined at customer site/account level or receipt method bank level.*/
4334 IF PG_DEBUG in ('Y', 'C') THEN
4335 arp_debug.debug('l_cust_site_min_rec_amt '|| l_cust_site_min_rec_amt);
4336 arp_debug.debug('l_rec_method_min_rec_amt '|| l_rec_method_min_rec_amt);
4337 END IF;
4338
4339 IF nvl(l_cust_site_min_rec_amt,0) > nvl(l_rec_method_min_rec_amt,0) THEN
4340 l_min_rec_amount_allowed := l_cust_site_min_rec_amt;
4341 l_err_code := 'ARZCAR_CUST_MIN_AMT';
4342 ELSE
4343 l_min_rec_amount_allowed := l_rec_method_min_rec_amt;
4344 l_err_code := 'ARZCAR_BANK_MIN_AMT';
4345 END IF;
4346
4347 IF l_rcpt_info_tab(i).amount < nvl(l_min_rec_amount_allowed,0) THEN
4348 l_err_rcpt_index := l_err_rcpt_index + 1;
4349 l_err_rcpt_num_array( l_err_rcpt_index ) := l_receipt_num_array(i);
4350 l_err_code_array( l_err_rcpt_index ) := l_err_code;
4351 l_cc_err_text_array( l_err_rcpt_index ) := NULL;
4352 ELSE
4353 l_err_code := NULL;
4354 END IF;
4355
4356
4357 IF PG_DEBUG in ('Y', 'C') THEN
4358 arp_debug.debug('l_err_code '|| l_err_code );
4359 arp_debug.debug('l_rcpt_info_tab(i).amount ' || l_rcpt_info_tab(i).amount);
4360 END IF;
4361
4362 l_ps_id_array(i) := l_rcpt_info_tab(i).payment_schedule_id;
4363 l_gt_id_array(i) := l_gt_id;
4364 l_cust_bank_acct_id_array(i) := l_rcpt_info_tab(i).customer_bank_account_id;
4365 l_paying_customer_id_array(i) := l_rcpt_info_tab(i).paying_customer_id;
4366 l_paying_site_use_id_array(i) := l_rcpt_info_tab(i).paying_site_use_id;
4367 l_due_date_array(i) := l_rcpt_info_tab(i).due_date;
4368 l_pmt_instrument_array(i) := l_rcpt_info_tab(i).payment_instrument;
4369 l_pmt_channel_code_array(i) := l_rcpt_info_tab(i).payment_channel_code;
4370 l_pmt_trxn_extn_id_array(i) := l_rcpt_info_tab(i).payment_trxn_extension_id;
4371 l_authorization_id_array(i) := l_rcpt_info_tab(i).authorization_id;
4372
4373
4374
4375 --if the minimum amount check of customer's profile succeeds then continue with the process
4376 IF nvl(l_err_code,' ') <> 'ARZCAR_CUST_MIN_AMT' AND
4377 nvl(l_err_code,' ') <> 'ARZCAR_BANK_MIN_AMT' THEN
4378
4379 IF l_rcpt_info_tab(i).payment_schedule_id IS NOT NULL THEN
4380 select nvl(terms_sequence_number,1)
4381 into l_installment
4382 from ar_payment_schedules
4383 where payment_schedule_id = l_rcpt_info_tab(i).payment_schedule_id;
4384 END IF;
4385
4386 IF PG_DEBUG in ('Y', 'C') THEN
4387 arp_debug.debug( 'l_installment = '|| l_installment);
4388 END IF;
4389
4390 --set the variables to cache
4391 g_rcpt_creation_rec.party_id := l_rcpt_info_tab(i).party_id;
4392 g_rcpt_creation_rec.pmt_channel_code := l_rcpt_info_tab(i).payment_channel_code;
4393 g_rcpt_creation_rec.assignment_id := l_rcpt_info_tab(i).instr_assignment_id;
4394
4395 IF PG_DEBUG in ('Y', 'C') THEN
4396 arp_debug.debug('Calling create receipt ');
4397 END IF;
4398
4399 -- Stamp receipt_number as null, so that receipt API copies
4400 -- doc seq to receipt_number when inher_inv_num_flag is not set
4401 IF l_rec_creation_rule_code <> 'PER_INVOICE' OR
4402 l_rec_inher_inv_num_flag <> 'Y' THEN
4403 l_receipt_num_array(i) := null;
4404 END IF;
4405
4406 --receipt creation
4407 create_receipt (l_rcpt_info_tab(i).amount,
4408 l_receipt_num_array( i ),
4409 l_batch_date,
4410 l_gl_date, /*Bug 13146325*/
4411 l_rcpt_info_tab(i).paying_customer_id,
4412 l_installment,
4413 l_rcpt_info_tab(i).paying_site_use_id,
4414 p_receipt_method_id,
4415 l_rcpt_info_tab(i).payment_trxn_extension_id,
4416 l_called_from,
4417 pg_request_id,
4418 p_batch_id,
4419 l_exchange_rate,
4420 l_exchange_date,
4421 l_exchange_rate_type,
4422 l_currency_code,
4423 l_remittance_bank_account_id,
4424 l_cash_receipt_id,
4425 l_return_status,
4426 l_msg_count,
4427 l_msg_data);
4428
4429 -- Error handling depends on receipt_number, so stamp back the value
4430 IF l_rec_creation_rule_code <> 'PER_INVOICE' OR
4431 l_rec_inher_inv_num_flag <> 'Y' THEN
4432 l_receipt_num_array(i) := l_index;
4433 END IF;
4434
4435 IF PG_DEBUG in ('Y', 'C') THEN
4436 arp_debug.debug( 'Returning from create_receipt ');
4437 arp_debug.debug( 'l_cash_receipt_id = '|| l_cash_receipt_id);
4438 arp_debug.debug( 'l_return_status = '|| l_return_status);
4439 END IF;
4440
4441 IF nvl(l_return_status,'N') = 'S' THEN
4442 l_receipt_id_array(i) := l_cash_receipt_id;
4443
4444 IF l_cnt = 0 THEN
4445 l_cnt := l_cnt + 1;
4446 l_from_cr_id := l_receipt_id_array(i);
4447 l_to_cr_id := l_receipt_id_array(i);
4448 END IF;
4449
4450 IF l_from_cr_id > l_receipt_id_array(i) THEN
4451 l_from_cr_id := l_receipt_id_array(i);
4452 END IF;
4453
4454 IF l_to_cr_id < l_receipt_id_array(i) THEN
4455 l_to_cr_id := l_receipt_id_array(i);
4456 END IF;
4457
4458 /* need to stamp the batch_id immediately inorder to succesfully execute the reapproval
4459 process in case the program gets errored/terminated before the receipt is processed
4460 completely */
4461 UPDATE ar_cash_receipt_history
4462 SET batch_id = p_batch_id,
4463 created_by = pg_created_by,
4464 last_update_date = sysdate,
4465 last_updated_by = pg_created_by,
4466 last_update_login = pg_last_update_login,
4467 request_id = pg_request_id,
4468 program_application_id = pg_program_application_id,
4469 program_id = pg_program_id,
4470 program_update_date = sysdate
4471 WHERE cash_receipt_id = l_cash_receipt_id;
4472 ELSE
4473 l_err_rcpt_index := l_err_rcpt_index + 1;
4474 l_err_rcpt_num_array( l_err_rcpt_index ) := l_receipt_num_array(i);
4475 l_err_code_array( l_err_rcpt_index ) := 'RCPT_CREATION_FAILED';
4476 l_receipt_id_array( i ) := null;
4477 l_cc_err_text_array( l_err_rcpt_index ) := l_msg_data;
4478 END IF;
4479 END IF;
4480
4481 END LOOP; -- end of create/process receipts loop
4482
4483 l_unbal_rcpt_tab.delete;
4484
4485 arp_standard.debug('Receipt_create: Min receipt id: '||l_from_cr_id);
4486 arp_standard.debug('Receipt_create: Max receipt id: '||l_to_cr_id);
4487
4488 IF l_from_cr_id IS NOT NULL AND l_to_cr_id IS NOT NULL THEN
4489
4490 arp_standard.debug(' Checking Journal Balance in Bulk: for receipt_create');
4491 ARP_BALANCE_CHECK.CHECK_RECP_BALANCE_BULK(
4492 p_cr_id_low => l_from_cr_id,
4493 p_cr_id_high => l_to_cr_id,
4494 p_unbalanced_cr_tbl => l_unbal_rcpt_tab );
4495
4496 arp_standard.debug('Receipt_create: Count of unbalanced receipts:'||l_unbal_rcpt_tab.COUNT);
4497
4498 --loop over the array to mark unbalanced receipts
4499 IF l_unbal_rcpt_tab.COUNT > 0 THEN
4500
4501 IF PG_DEBUG in ('Y', 'C') THEN
4502 arp_standard.debug('Receipt_create: inside, l_unbal_rcpt_tab.FIRST : '||l_unbal_rcpt_tab.FIRST);
4503 arp_standard.debug('Receipt_create: inside, l_unbal_rcpt_tab.LAST : '||l_unbal_rcpt_tab.LAST);
4504 END IF;
4505
4506 FOR i IN l_unbal_rcpt_tab.FIRST..l_unbal_rcpt_tab.LAST LOOP
4507 FOR j IN l_rcpt_info_tab.FIRST..l_rcpt_info_tab.LAST LOOP
4508
4509 IF PG_DEBUG in ('Y', 'C') THEN
4510 arp_standard.debug('Receipt_create: inside, l_unbal_rcpt_tab(i).cash_receipt_id : '||l_unbal_rcpt_tab(i).cash_receipt_id);
4511 arp_standard.debug('Receipt_create: inside, l_receipt_id_array(j) : '||l_receipt_id_array(j));
4512 END IF;
4513
4514 IF l_receipt_id_array(j) = l_unbal_rcpt_tab(i).cash_receipt_id THEN
4515
4516 l_err_text := 'Receipt with cash_receipt_id '||l_unbal_rcpt_tab(i).cash_receipt_id||' is deleted due to unbalanced journal entries.';
4517 l_err_rcpt_index := l_err_rcpt_index + 1;
4518 l_err_rcpt_num_array( l_err_rcpt_index ) := l_receipt_num_array(j);
4519 l_err_code_array( l_err_rcpt_index ) := 'RCPT_CREATION_FAILED';
4520 l_cc_err_text_array( l_err_rcpt_index ) := l_err_text;
4521
4522 IF PG_DEBUG in ('Y','C') THEN
4523 arp_standard.debug('Delete unbalanced receipt with receipt_id : '||l_receipt_id_array(j));
4524 END IF;
4525
4526 delete from ar_payment_schedules
4527 where cash_receipt_id = l_receipt_id_array(j);
4528
4529 IF PG_DEBUG in ('Y','C') THEN
4530 arp_standard.debug ( ' rows DELETED PS = ' || SQL%ROWCOUNT );
4531 END IF;
4532
4533 delete from ar_distributions
4534 where source_table = 'CRH'
4535 and source_id in
4536 ( select cash_receipt_history_id
4537 from ar_cash_receipt_history
4538 where cash_receipt_id = l_receipt_id_array(j));
4539
4540 IF PG_DEBUG in ('Y','C') THEN
4541 arp_standard.debug ( ' rows DELETED AR_DIST = ' || SQL%ROWCOUNT );
4542 END IF;
4543
4544 delete from ar_distributions
4545 where source_table = 'RA'
4546 and source_id in
4547 ( select receivable_application_id
4548 from ar_receivable_applications
4549 where cash_receipt_id = l_receipt_id_array(j));
4550
4551 IF PG_DEBUG in ('Y','C') THEN
4552 arp_standard.debug ( ' rows DELETED AR_DIST2 = ' || SQL%ROWCOUNT );
4553 END IF;
4554
4555 delete from ar_receivable_applications
4556 where cash_receipt_id = l_receipt_id_array(j);
4557
4558 IF PG_DEBUG in ('Y','C') THEN
4559 arp_standard.debug ( ' rows DELETED REC_APPS = ' || SQL%ROWCOUNT );
4560 END IF;
4561
4562 delete from ar_cash_receipt_history
4563 where cash_receipt_id = l_receipt_id_array(j);
4564
4565 IF PG_DEBUG in ('Y','C') THEN
4566 arp_standard.debug ( ' rows DELETED CRH = ' || SQL%ROWCOUNT );
4567 END IF;
4568
4569 delete from ar_cash_receipts
4570 where cash_receipt_id = l_receipt_id_array(j);
4571
4572 IF PG_DEBUG in ('Y','C') THEN
4573 arp_standard.debug ( ' rows DELETED CR = ' || SQL%ROWCOUNT );
4574 END IF;
4575
4576 l_receipt_id_array(j) := null;
4577 EXIT;
4578 END IF;
4579 END LOOP;
4580 END LOOP;
4581 END IF;
4582 END IF;
4583
4584 --loop over the array to do payment processing if needed
4585 FOR i IN l_rcpt_info_tab.FIRST..l_rcpt_info_tab.LAST LOOP
4586
4587 /* IF l_unbal_rcpt_tab.EXISTS(l_receipt_id_array(i)) THEN
4588 l_err_rcpt_index := l_err_rcpt_index + 1;
4589 l_err_rcpt_num_array( l_err_rcpt_index ) := l_receipt_num_array(i);
4590 l_err_code_array( l_err_rcpt_index ) := 'RCPT_CREATION_FAILED';
4591 l_receipt_id_array( i ) := null;
4592 END IF;
4593 */
4594 --payment processing
4595 IF l_process_payment_flag = 1 AND l_receipt_id_array(i) is not null THEN
4596
4597 IF PG_DEBUG in ('Y', 'C') THEN
4598 arp_debug.debug( 'Calling process_payments ');
4599 arp_standard.debug( 'l_receipt_id_array(i)= '|| l_receipt_id_array(i));
4600 END IF;
4601
4602 l_cc_error_code := null;
4603 l_cc_error_text := null;
4604
4605 process_payments(l_receipt_id_array(i),
4606 null,--p_pmt_trxn_id,
4607 pg_request_id,
4608 p_batch_id,
4609 l_called_from,
4610 l_cc_error_code,
4611 l_cc_error_text,
4612 l_return_status);
4613
4614 IF PG_DEBUG in ('Y', 'C') THEN
4615 arp_debug.debug( 'Returning from process_payments ');
4616 arp_debug.debug( 'l_return_status = '|| l_return_status);
4617 END IF;
4618
4619 /*------------------------------------------------------+
4620 | Check the return status from Process_Payment |
4621 +------------------------------------------------------*/
4622 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4623 l_pay_process_fail := 'Y';
4624
4625 l_err_rcpt_index := l_err_rcpt_index + 1;
4626 l_err_rcpt_num_array( l_err_rcpt_index ) := l_receipt_num_array(i);
4627 l_err_code_array( l_err_rcpt_index ) := 'PROCESS_PAYMENT_FAILED';
4628 l_cc_err_code_array( l_err_rcpt_index ) := l_cc_error_code;
4629 l_cc_err_text_array( l_err_rcpt_index ) := l_cc_error_text;
4630 END IF;
4631 END IF;
4632
4633 END LOOP; -- end of process receipts loop
4634
4635 IF PG_DEBUG in ('Y', 'C') THEN
4636 arp_debug.debug( 'Updating ar_receipts_gt with receipt number');
4637 END IF;
4638
4639 /**Stamp receipt_id and receipt number to the gt table*/
4640 update_ar_receipts_gt ( p_creation_rule => l_rec_creation_rule_code,
4641 p_update_stmt => l_update_stmt,
4642 p_cr_id_array => l_receipt_id_array,
4643 p_cr_number_array => l_receipt_num_array,
4644 p_gt_id_array => l_gt_id_array,
4645 p_ps_id_array => l_ps_id_array,
4646 p_paying_customer_id_array => l_paying_customer_id_array,
4647 p_pmt_instrument_array => l_pmt_instrument_array,
4648 p_paying_site_use_id_array => l_paying_site_use_id_array,
4649 p_due_date_array => l_due_date_array,
4650 p_pmt_channel_code_array => l_pmt_channel_code_array,
4651 p_cust_bank_acct_id_array => l_cust_bank_acct_id_array,
4652 p_trxn_extension_id_array => l_pmt_trxn_extn_id_array,
4653 p_authorization_id_array => l_authorization_id_array );
4654
4655 --loop through all the receipts which failed the min amount condition and insert
4656 --them into exceptions table
4657 FOR k IN 1..l_err_rcpt_index LOOP
4658 IF PG_DEBUG in ('Y', 'C') THEN
4659 arp_debug.debug( 'loop through errored receipts '||l_err_rcpt_index);
4660 END IF;
4661
4662 FOR rec IN rec_ps_cur( l_err_rcpt_num_array(k) ) LOOP
4663 IF PG_DEBUG in ('Y', 'C') THEN
4664 arp_debug.debug( 'l_err_rcpt_num_array '||l_err_rcpt_num_array(k));
4665 arp_debug.debug( 'l_err_code_array '||l_err_code_array(k));
4666 END IF;
4667
4668 IF l_err_code_array(k) in( 'ARZCAR_CUST_MIN_AMT', 'ARZCAR_BANK_MIN_AMT',
4669 'RCPT_CREATION_FAILED')
4670 AND rec.cash_receipt_id is null THEN
4671 insert_exceptions(
4672 p_batch_id => p_batch_id,
4673 p_request_id => pg_request_id,
4674 p_payment_schedule_id => rec.payment_schedule_id,
4675 p_paying_customer_id => rec.paying_customer_id,
4676 p_exception_code => l_err_code_array(k),
4677 p_additional_message => l_cc_err_text_array(k) );
4678
4679 --make the errored receipts avaliable for future runs
4680 UPDATE ar_payment_schedules
4681 SET selected_for_receipt_batch_id = NULL
4682 WHERE payment_schedule_id = rec.payment_schedule_id;
4683
4684 ELSIF l_err_code_array(k) = 'PROCESS_PAYMENT_FAILED'
4685 AND rec.cash_receipt_id is not null THEN
4686 /* update the error flag in ra_customer_trx */
4687 UPDATE ra_customer_trx
4688 SET cc_error_flag = 'Y',
4689 cc_error_code = l_cc_err_code_array(k),
4690 cc_error_text = l_cc_err_text_array(k),
4691 last_updated_by = pg_last_updated_by,
4692 last_update_date = sysdate,
4693 last_update_login = pg_last_update_login,
4694 request_id = pg_request_id,
4695 program_application_id= pg_program_application_id,
4696 program_id = pg_program_id,
4697 program_update_date = sysdate
4698 WHERE customer_trx_id = rec.customer_trx_id;
4699
4700 IF PG_DEBUG in ('Y', 'C') THEN
4701 arp_debug.debug( 'Invoice stamped with cc error '||rec.customer_trx_id);
4702 arp_debug.debug( 'l_cc_err_code_array '||l_cc_err_code_array(k));
4703 arp_debug.debug( 'l_cc_err_text_array '||l_cc_err_text_array(k));
4704 END IF;
4705 END IF;
4706
4707 END LOOP;
4708 END LOOP;
4709 ELSE --flow related to Re-approval of receipt batch
4710 FOR i IN l_rcpt_info_tab.FIRST..l_rcpt_info_tab.LAST LOOP
4711 UPDATE ar_receipts_gt
4712 SET gt_id = l_gt_id
4713 WHERE cash_receipt_id = l_rcpt_info_tab(i).cash_receipt_id
4714 AND gt_id = -1 ;
4715
4716 IF l_old_cash_receipt_id <> l_rcpt_info_tab(i).cash_receipt_id THEN
4717 l_old_cash_receipt_id := l_rcpt_info_tab(i).cash_receipt_id;
4718
4719 process_payments(l_rcpt_info_tab(i).cash_receipt_id,
4720 null,--p_pmt_trxn_id,
4721 pg_request_id,
4722 p_batch_id,
4723 l_called_from,
4724 l_cc_error_code,
4725 l_cc_error_text,
4726 l_return_status);
4727
4728 IF PG_DEBUG in ('Y', 'C') THEN
4729 arp_debug.debug( 'Returning from process_payments ');
4730 arp_debug.debug( 'l_return_status = '|| l_return_status);
4731 END IF;
4732
4733 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4734 l_pay_process_fail := 'Y';
4735
4736 UPDATE ra_customer_trx
4737 SET cc_error_flag = 'Y',
4738 cc_error_code = l_cc_error_code,
4739 cc_error_text = l_cc_error_text,
4740 last_updated_by = pg_last_updated_by,
4741 last_update_date = sysdate,
4742 last_update_login = pg_last_update_login,
4743 request_id = pg_request_id,
4744 program_application_id= pg_program_application_id,
4745 program_id = pg_program_id,
4746 program_update_date = sysdate
4747 WHERE customer_trx_id IN
4748 ( SELECT customer_trx_id
4749 FROM ar_payment_schedules
4750 WHERE payment_schedule_id = l_rcpt_info_tab(i).payment_schedule_id
4751 );
4752 END IF;
4753 END IF;
4754 END LOOP;
4755
4756 END IF;
4757
4758 IF PG_DEBUG in ('Y', 'C') THEN
4759 arp_debug.debug('ar_receipts_gt dump 2');
4760 dump_ar_receipts_gt;
4761 END IF;
4762
4763 /*--------------------------------------------------
4764 Set the variable so that ar_open_trx_v will NOT
4765 excecute ps.selected_for_receipt_batch_id is null
4766 ---------------------------------------------------*/
4767 arp_view_constants.set_ps_selected_in_batch('Y');
4768
4769 IF PG_DEBUG in ('Y', 'C') THEN
4770 arp_debug.debug( 'Opening receipt application cursor');
4771 END IF;
4772
4773 --cursor to fetch the application data
4774 OPEN appl_rec_cur( l_gt_id );
4775
4776
4777 FETCH appl_rec_cur BULK COLLECT INTO l_rcpt_appl_tab;
4778
4779 IF PG_DEBUG in ('Y', 'C') THEN
4780 arp_debug.debug('applications fetch count '|| l_rcpt_appl_tab.count);
4781 END IF;
4782
4783 l_amt_applied := 0;
4784 l_old_cash_receipt_id := -99;
4785 IF l_rcpt_appl_tab.count > 0 THEN
4786 --loop over the array and apply receipts
4787 FOR i IN l_rcpt_appl_tab.FIRST..l_rcpt_appl_tab.LAST LOOP
4788
4789 --set it to global variable
4790 IF l_rcpt_appl_tab(i).cash_receipt_id <> l_old_cash_receipt_id then
4791 l_amt_applied := 0;
4792 ELSE
4793 l_rcpt_appl_tab(i).cr_amount := g_rcpt_info_rec.cr_amount;
4794 END IF;
4795
4796 l_rcpt_appl_tab(i).cr_amount := l_rcpt_appl_tab(i).cr_amount - l_amt_applied;
4797 g_rcpt_info_rec := l_rcpt_appl_tab(i);
4798
4799 receipt_application(l_rcpt_appl_tab(i).cash_receipt_id,
4800 l_rcpt_appl_tab(i).applied_payment_schedule_id,
4801 l_rcpt_appl_tab(i).amount_apply,
4802 pg_request_id,
4803 p_batch_id,
4804 greatest(l_rcpt_appl_tab(i).receipt_date,l_rcpt_appl_tab(i).trx_date),
4805 l_called_from,
4806 l_return_status);
4807
4808 l_amt_applied := l_rcpt_appl_tab(i).amount_apply;
4809 l_old_cash_receipt_id := l_rcpt_appl_tab(i).cash_receipt_id;
4810
4811 IF PG_DEBUG in ('Y', 'C') THEN
4812 arp_debug.debug( 'Returing from receipt_application ');
4813 arp_debug.debug( 'l_return_status = '|| l_return_status);
4814 END IF;
4815
4816 IF l_return_status <> 'S' THEN
4817 l_apply_fail := 'Y' ;
4818 END IF;
4819 END LOOP;
4820
4821 l_from_cr_id := NULL;
4822 l_to_cr_id := NULL;
4823 l_unbal_rcpt_tab.delete;
4824
4825 select min(gt.cash_receipt_id),
4826 max(gt.cash_receipt_id)
4827 into l_from_cr_id,
4828 l_to_cr_id
4829 from AR_RECEIPTS_GT gt
4830 where gt.gt_id = l_gt_id;
4831
4832 IF l_from_cr_id IS NOT NULL AND l_to_cr_id IS NOT NULL THEN
4833
4834 arp_standard.debug('Checking Journal Balance in Bulk: After Application');
4835 ARP_BALANCE_CHECK.CHECK_RECP_BALANCE_BULK(
4836 p_cr_id_low => l_from_cr_id,
4837 p_cr_id_high => l_to_cr_id,
4838 p_unbalanced_cr_tbl => l_unbal_rcpt_tab );
4839
4840 IF l_unbal_rcpt_tab.count > 0 THEN
4841 FOR i IN l_unbal_rcpt_tab.FIRST..l_unbal_rcpt_tab.LAST LOOP
4842
4843 arp_standard.debug('Check Journal Balance for each application for balance failed receipts');
4844 FOR unbal_rec_appln IN unbal_rec_applns(l_unbal_rcpt_tab(i).cash_receipt_id, l_gt_id) LOOP
4845
4846 BEGIN
4847 ARP_BALANCE_CHECK.CHECK_APPLN_BALANCE(
4848 p_receivable_application_id1 => unbal_rec_appln.source_id_secondary,
4849 p_receivable_application_id2 => unbal_rec_appln.source_id,
4850 p_request_id => pg_request_id,
4851 p_called_from_api => 'Y' );
4852 EXCEPTION
4853 WHEN OTHERS THEN
4854 arp_standard.debug('Exception: Application Balance Failure');
4855 arp_standard.debug('Exception: Invoice PS ID: '||unbal_rec_appln.applied_payment_schedule_id);
4856 arp_standard.debug('Exception: CR ID: '||l_unbal_rcpt_tab(i).cash_receipt_id);
4857
4858 arp_standard.debug('Update Invoice PS Before deleting Application');
4859 UPDATE AR_PAYMENT_SCHEDULES PS SET (
4860 PS.AMOUNT_DUE_REMAINING,
4861 PS.AMOUNT_APPLIED,
4862 PS.AMOUNT_LINE_ITEMS_REMAINING,
4863 PS.RECEIVABLES_CHARGES_REMAINING,
4864 PS.FREIGHT_REMAINING,
4865 PS.TAX_REMAINING,
4866 PS.ACCTD_AMOUNT_DUE_REMAINING,
4867 PS.STATUS,
4868 PS.GL_DATE_CLOSED,
4869 PS.ACTUAL_DATE_CLOSED,
4870 PS.DISCOUNT_REMAINING,
4871 PS.DISCOUNT_TAKEN_EARNED ) =
4872 ( SELECT
4873 NVL(PS.AMOUNT_DUE_REMAINING, 0)
4874 + NVL(RA1.AMOUNT_APPLIED, 0)
4875 + NVL(RA1.EARNED_DISCOUNT_TAKEN, 0),
4876 NVL(PS.AMOUNT_APPLIED, 0)
4877 - NVL(RA1.AMOUNT_APPLIED, 0),
4878 NVL(PS.AMOUNT_LINE_ITEMS_REMAINING, 0)
4879 + NVL(RA1.LINE_APPLIED, 0)
4880 + NVL(LINE_EDISCOUNTED, 0),
4881 NVL(PS.RECEIVABLES_CHARGES_REMAINING, 0)
4882 + NVL(RA1.RECEIVABLES_CHARGES_APPLIED, 0)
4883 + NVL(CHARGES_EDISCOUNTED, 0),
4884 NVL(PS.FREIGHT_REMAINING, 0)
4885 + NVL(RA1.FREIGHT_APPLIED, 0)
4886 + NVL(FREIGHT_EDISCOUNTED, 0),
4887 NVL(PS.TAX_REMAINING, 0)
4888 + NVL(RA1.TAX_APPLIED, 0)
4889 + NVL(TAX_EDISCOUNTED, 0),
4890 NVL(PS.ACCTD_AMOUNT_DUE_REMAINING, 0)
4891 + NVL(RA1.ACCTD_AMOUNT_APPLIED_TO, 0)
4892 + NVL(RA1.ACCTD_EARNED_DISCOUNT_TAKEN, 0),
4893 DECODE((NVL(PS.AMOUNT_DUE_REMAINING, 0)
4894 + NVL(RA1.AMOUNT_APPLIED, 0)
4895 + NVL(RA1.EARNED_DISCOUNT_TAKEN, 0)), 0, 'CL', 'OP'),
4896 DECODE((NVL(PS.AMOUNT_DUE_REMAINING, 0)
4897 + NVL(RA1.AMOUNT_APPLIED, 0)
4898 + NVL(RA1.EARNED_DISCOUNT_TAKEN, 0)),
4899 0, PS.GL_DATE_CLOSED, to_date('12/31/4712', 'MM/DD/YYYY')),
4900 DECODE((NVL(PS.AMOUNT_DUE_REMAINING, 0)
4901 + NVL(RA1.AMOUNT_APPLIED, 0)
4902 + NVL(RA1.EARNED_DISCOUNT_TAKEN, 0)),
4903 0, PS.ACTUAL_DATE_CLOSED, to_date('12/31/4712', 'MM/DD/YYYY')),
4904 NVL(PS.DISCOUNT_REMAINING, 0)
4905 + NVL(RA1.EARNED_DISCOUNT_TAKEN, 0),
4906 NVL(PS.DISCOUNT_TAKEN_EARNED, 0)
4907 - NVL(RA1.EARNED_DISCOUNT_TAKEN, 0)
4908 FROM AR_RECEIVABLE_APPLICATIONS RA1
4909 WHERE RA1.APPLIED_PAYMENT_SCHEDULE_ID = PS.PAYMENT_SCHEDULE_ID
4910 AND RA1.CASH_RECEIPT_ID = l_unbal_rcpt_tab(i).cash_receipt_id
4911 AND RA1.RECEIVABLE_APPLICATION_ID = unbal_rec_appln.source_id_secondary )
4912 WHERE PS.PAYMENT_SCHEDULE_ID = unbal_rec_appln.applied_payment_schedule_id;
4913
4914 arp_standard.debug('Update Receipt PS Before deleting Application');
4915 UPDATE AR_PAYMENT_SCHEDULES PS SET (
4916 PS.AMOUNT_DUE_REMAINING,
4917 PS.AMOUNT_APPLIED,
4918 PS.ACCTD_AMOUNT_DUE_REMAINING,
4919 PS.STATUS,
4920 PS.GL_DATE_CLOSED,
4921 PS.ACTUAL_DATE_CLOSED ) =
4922 ( SELECT
4923 NVL(PS.AMOUNT_DUE_REMAINING, 0) - NVL(RA1.AMOUNT_APPLIED, 0),
4924 NVL(PS.AMOUNT_APPLIED, 0) + NVL(RA1.AMOUNT_APPLIED, 0),
4925 NVL(PS.ACCTD_AMOUNT_DUE_REMAINING, 0) - NVL(RA1.ACCTD_AMOUNT_APPLIED_TO, 0),
4926 DECODE((NVL(PS.AMOUNT_DUE_REMAINING, 0) - NVL(RA1.AMOUNT_APPLIED, 0)), 0, 'CL', 'OP'),
4927 DECODE((NVL(PS.AMOUNT_DUE_REMAINING, 0) - NVL(RA1.AMOUNT_APPLIED, 0)),
4928 0, PS.GL_DATE_CLOSED, to_date('12/31/4712', 'MM/DD/YYYY')),
4929 DECODE((NVL(PS.AMOUNT_DUE_REMAINING, 0) - NVL(RA1.AMOUNT_APPLIED, 0)),
4930 0, PS.ACTUAL_DATE_CLOSED, to_date('12/31/4712', 'MM/DD/YYYY'))
4931 FROM AR_RECEIVABLE_APPLICATIONS RA1
4932 WHERE RA1.PAYMENT_SCHEDULE_ID = PS.PAYMENT_SCHEDULE_ID
4933 AND RA1.RECEIVABLE_APPLICATION_ID = unbal_rec_appln.source_id_secondary )
4934 WHERE PS.PAYMENT_SCHEDULE_ID = unbal_rec_appln.payment_schedule_id;
4935
4936 arp_standard.debug('Delete Application Distribution Entires');
4937 DELETE FROM AR_DISTRIBUTIONS WHERE SOURCE_ID IN
4938 (unbal_rec_appln.source_id,
4939 unbal_rec_appln.source_id_secondary)
4940 AND SOURCE_TABLE = 'RA';
4941
4942 arp_standard.debug('Delete Application Entries');
4943 DELETE FROM AR_RECEIVABLE_APPLICATIONS WHERE RECEIVABLE_APPLICATION_ID IN
4944 (unbal_rec_appln.source_id,
4945 unbal_rec_appln.source_id_secondary);
4946
4947 arp_standard.debug('Reset Invoice Line Level Balances If maintained on the invoice');
4948 IF unbal_rec_appln.upgrade_method IN ('R12', 'R12_11IMFAR') THEN
4949
4950 UPDATE RA_CUSTOMER_TRX_LINES TL SET (
4951 AMOUNT_DUE_REMAINING,
4952 ACCTD_AMOUNT_DUE_REMAINING,
4953 CHRG_AMOUNT_REMAINING,
4954 CHRG_ACCTD_AMOUNT_REMAINING,
4955 FRT_ADJ_REMAINING,
4956 FRT_ADJ_ACCTD_REMAINING,
4957 FRT_ED_AMOUNT,
4958 FRT_ED_ACCTD_AMOUNT,
4959 FRT_UNED_AMOUNT,
4960 FRT_UNED_ACCTD_AMOUNT) = (
4961 SELECT
4962 TL.AMOUNT_DUE_ORIGINAL + REM_TYPE_LINE,
4963 TL.ACCTD_AMOUNT_DUE_ORIGINAL + ACCTD_REM_TYPE_LINE,
4964 CHRG_ON_REV_LINE,
4965 ACCTD_CHRG_ON_REV_LINE,
4966 FRT_ON_REV_LINE,
4967 ACCTD_FRT_ON_REV_LINE,
4968 ED_FRT_REV_LINE,
4969 ACCTD_ED_FRT_REV_LINE,
4970 UNED_FRT_REV_LINE,
4971 ACCTD_UNED_FRT_REV_LINE
4972 FROM (SELECT
4973 SUM((decode(a.activity_bucket, 'ADJ_CHRG',
4974 amt, 'APP_CHRG', decode(a.line_type,
4975 'LINE', amt, 0) * -1, 0))) chrg_on_rev_line,
4976 SUM((decode(a.activity_bucket, 'ADJ_CHRG',
4977 acctd_amt, 'APP_CHRG', decode(a.line_type,
4978 'LINE', acctd_amt, 0) * -1, 0)))
4979 acctd_chrg_on_rev_line,
4980 SUM((decode(a.activity_bucket, 'ADJ_FRT',
4981 amt, 'APP_FRT', decode(a.line_type, 'LINE',
4982 amt, 0) * -1, 0))) frt_on_rev_line,
4983 SUM((decode(a.activity_bucket, 'ADJ_FRT', amt,
4984 'APP_FRT', decode(a.line_type, 'LINE',
4985 acctd_amt, 0) * -1, 0))) acctd_frt_on_rev_line,
4986 SUM((decode(a.activity_bucket,
4987 'ED_FRT', amt, 0))) ed_frt_rev_line,
4988 SUM((decode(a.activity_bucket, 'ED_FRT',
4989 acctd_amt, 0))) acctd_ed_frt_rev_line,
4990 SUM((decode(a.activity_bucket, 'UNED_FRT',
4991 amt, 0))) uned_frt_rev_line,
4992 SUM((decode(a.activity_bucket, 'UNED_FRT',
4993 acctd_amt, 0))) acctd_uned_frt_rev_line,
4994 SUM((decode(a.activity_bucket, 'ADJ_LINE', amt,
4995 'APP_LINE', (amt * -1), 'ED_LINE', amt,
4996 'UNED_LINE', amt,'ADJ_TAX', amt,
4997 'APP_TAX', (amt * -1), 'ED_TAX', amt,
4998 'UNED_TAX', amt, 'APP_FRT',
4999 (decode(a.line_type, 'FREIGHT', amt, 0) * -1),
5000 'APP_CHRG', (decode(a.line_type, 'CHARGES',
5001 amt, 0) * -1), 0))) rem_type_line,
5002 SUM((decode(a.activity_bucket, 'ADJ_LINE', acctd_amt,
5003 'APP_LINE', (acctd_amt * -1), 'ED_LINE',
5004 acctd_amt, 'UNED_LINE', acctd_amt, 'ADJ_TAX',
5005 acctd_amt, 'APP_TAX', (acctd_amt * -1), 'ED_TAX',
5006 acctd_amt, 'UNED_TAX', acctd_amt,'APP_FRT',
5007 (decode(a.line_type, 'FREIGHT',
5008 acctd_amt, 0) * -1), 'APP_CHRG',
5009 (decode(a.line_type, 'CHARGES',
5010 acctd_amt, 0) * -1), 0))) acctd_rem_type_line,
5011 a.customer_trx_line_id customer_trx_line_id
5012 FROM (
5013 SELECT
5014 SUM(nvl(ard.amount_cr, 0)
5015 - nvl(ard.amount_dr, 0)) amt,
5016 SUM(nvl(ard.acctd_amount_cr, 0)
5017 - nvl(ard.acctd_amount_dr, 0)) acctd_amt,
5018 ctl.customer_trx_line_id,
5019 ard.ref_account_class,
5020 ard.activity_bucket,
5021 ctl.line_type
5022 FROM ar_distributions ard,
5023 ra_customer_trx_lines ctl
5024 WHERE ctl.customer_trx_id =
5025 unbal_rec_appln.applied_customer_trx_id
5026 AND ctl.customer_trx_line_id = ard.ref_customer_trx_line_id (+)
5027 GROUP BY ctl.customer_trx_line_id,
5028 ard.ref_account_class,
5029 ard.activity_bucket,
5030 ctl.line_type) a
5031 GROUP BY a.customer_trx_line_id) bal
5032 WHERE bal.customer_trx_line_id = TL.customer_trx_line_id)
5033 WHERE TL.CUSTOMER_TRX_ID = unbal_rec_appln.applied_customer_trx_id;
5034
5035 END IF;
5036
5037 l_msg_data := 'Application failure. You need to nullify the SELECTED FOR RECEIPT BATCH ID on the invoice.'||
5038 ' When the invoice is fixed, then apply the invoice manually to the receipt with receipt id: '||
5039 l_unbal_rcpt_tab(i).cash_receipt_id||', created by automatic receipts for that invoice';
5040
5041 arp_standard.debug('Inserting Exception');
5042
5043 insert_exceptions(
5044 p_batch_id => p_batch_id,
5045 p_request_id => pg_request_id,
5046 p_payment_schedule_id => unbal_rec_appln.applied_payment_schedule_id,
5047 p_exception_code => 'AUTORECERR',
5048 p_additional_message => l_msg_data );
5049
5050 END;
5051
5052 END LOOP;
5053
5054 END LOOP;
5055 END IF;
5056
5057 END IF;
5058
5059 END IF;
5060
5061 CLOSE appl_rec_cur;
5062
5063 /* reset the variable back to null */
5064 arp_view_constants.set_ps_selected_in_batch( null);
5065
5066 /* CALL TO CONTROL_CHECK to detect bad receipts */
5067 control_check ( p_batch_id => p_batch_id ,
5068 p_gt_id => l_gt_id );
5069
5070 IF g_auth_fail = 'Y' THEN
5071 l_pay_process_fail := 'Y';
5072 END IF;
5073
5074 /* CALL To reset in the case there were failures */
5075 IF l_apply_fail = 'Y' OR l_pay_process_fail = 'Y' THEN
5076
5077 IF PG_DEBUG in ('Y', 'C') THEN
5078 arp_debug.debug('calling rec reset.'|| l_apply_fail || l_pay_process_fail);
5079 END IF;
5080
5081 rec_reset( p_apply_fail => l_apply_fail,
5082 p_pay_process_fail => l_pay_process_fail,
5083 p_gt_id => l_gt_id );
5084
5085 l_apply_fail := 'N';
5086 l_pay_process_fail := 'N';
5087 END IF;
5088
5089 IF PG_DEBUG in ('Y', 'C') THEN
5090 arp_debug.debug( 'End of loop ');
5091 arp_debug.debug( 'Commiting the batch...');
5092 END IF;
5093
5094 /*Update the receipt_number with the value of trx_number,this update is needed
5095 sine we considered concatenated string of trx_number and term_sequence as
5096 receipt_number during receipt creation.*/
5097 IF l_rec_creation_rule_code = 'PER_INVOICE' AND
5098 l_rec_inher_inv_num_flag = 'Y' THEN
5099 update ar_cash_receipts cr
5100 SET receipt_number =
5101 NVL(SUBSTR(RECEIPT_NUMBER, 1, INSTR(RECEIPT_NUMBER,'-', -1) -1), RECEIPT_NUMBER)
5102 WHERE cash_receipt_id in
5103 ( select cr.cash_receipt_id
5104 from ar_cash_receipts cr,
5105 ar_receipts_gt arg
5106 where arg.gt_id = l_gt_id
5107 AND cr.cash_receipt_id = arg.cash_receipt_id
5108 );
5109 arp_debug.debug ( 'NO of Receipts updated = '|| to_char(SQL%ROWCOUNT));
5110
5111 update ar_payment_schedules
5112 SET TRX_NUMBER =
5113 NVL(SUBSTR(TRX_NUMBER, 1, INSTR(TRX_NUMBER,'-', -1) -1), TRX_NUMBER)
5114 WHERE cash_receipt_id in
5115 ( select ps.cash_receipt_id
5116 from ar_payment_schedules ps,
5117 ar_receipts_gt arg
5118 where arg.gt_id = l_gt_id
5119 AND ps.cash_receipt_id = arg.cash_receipt_id
5120 );
5121 arp_debug.debug ( 'NO of Receipts updated = '|| to_char(SQL%ROWCOUNT));
5122
5123 END IF;
5124
5125 --initiate the event processing
5126 process_events( l_gt_id, p_batch_id );
5127
5128 --commit after processing a batch
5129 COMMIT;
5130
5131 -- no more rows to fetch
5132 IF l_rows_fetched < MAX_ARRAY_SIZE THEN
5133 IF( dbms_sql.is_open( l_create_stmt_c) ) THEN
5134 dbms_sql.close_cursor( l_create_stmt_c );
5135 END IF;
5136
5137 IF PG_DEBUG in ('Y', 'C') THEN
5138 arp_debug.debug('l_rows_fetched < MAX_ARRAY_SIZE Cursor closed');
5139 END IF;
5140 EXIT;
5141 END IF; --no more rows to fetch
5142
5143 l_receipt_num_array.delete;
5144 l_receipt_id_array.delete;
5145 l_ps_id_array.delete;
5146
5147 EXIT WHEN rcpt_info_cursor%NOTFOUND;
5148
5149 END LOOP;--main select cursor loop
5150
5151 IF PG_DEBUG in ('Y', 'C') THEN
5152 arp_debug.debug('process_selected_receipts()-');
5153 END IF;
5154
5155 EXCEPTION
5156 WHEN others THEN
5157 G_ERROR := 'Y';
5158 insert_exceptions(
5159 p_batch_id =>p_batch_id,
5160 p_request_id =>pg_request_id,
5161 p_paying_customer_id =>-3,
5162 p_exception_code => 'AUTORECERR',
5163 p_additional_message => SQLERRM
5164 );
5165 IF PG_DEBUG in ('Y', 'C') THEN
5166 arp_debug.debug('Exception : process_selected_receipts() '|| SQLERRM);
5167 END IF;
5168 END process_selected_receipts;
5169
5170
5171 /* START CONTROL_CHECK */
5172 PROCEDURE CONTROL_CHECK ( p_batch_id ar_batches.batch_id%TYPE,
5173 p_gt_id NUMBER ) IS
5174
5175 BEGIN
5176 IF PG_DEBUG in ('Y', 'C') THEN
5177 fnd_file.put_line(FND_FILE.LOG,'control_check()+');
5178 fnd_file.put_line(FND_FILE.LOG,'Detect receipts with Auth failure');
5179 END IF;
5180
5181 UPDATE /*+ index(ct ra_customer_trx_u1) */ ra_customer_trx_all ct
5182 SET cc_error_flag = 'Y',
5183 last_updated_by = pg_last_updated_by,
5184 last_update_date = sysdate,
5185 last_update_login = pg_last_update_login,
5186 request_id = pg_request_id,
5187 program_application_id = pg_program_application_id,
5188 program_id = pg_program_id,
5189 program_update_date = sysdate
5190 WHERE customer_trx_id in
5191 (
5192 SELECT /*+ push_pred(trxn_ext) */ r.customer_trx_id
5193 FROM ar_receipts_gt r,
5194 ar_cash_receipts cr,
5195 ar_cash_receipt_history crh,
5196 (SELECT op.trxn_extension_id, summ.status
5197 FROM iby_trxn_summaries_all summ,
5198 iby_fndcpt_tx_operations op
5199 WHERE(summ.transactionid = op.transactionid)
5200 AND(reqtype = 'ORAPMTREQ')
5201 AND(status IN(0, 100, 111, 31, 32))
5202 AND((trxntypeid IN(2, 3)) OR((trxntypeid = 20)
5203 AND(summ.trxnmid =
5204 (SELECT MAX(trxnmid)
5205 FROM iby_trxn_summaries_all
5206 WHERE transactionid = summ.transactionid
5207 AND(reqtype = 'ORAPMTREQ')
5208 AND(status IN(0, 100, 111, 31, 32))
5209 AND(trxntypeid = 20)))))
5210 ) trxn_ext
5211 WHERE r.gt_id = p_gt_id
5212 AND cr.cash_receipt_id = r.cash_receipt_id
5213 AND crh.cash_receipt_id = cr.cash_receipt_id
5214 AND crh.status = 'CONFIRMED'
5215 AND crh.current_record_flag = 'Y'
5216 AND cr.payment_trxn_extension_id = trxn_ext.trxn_extension_id(+)
5217 AND trxn_ext.status IS NULL
5218 );
5219
5220 if sql%rowcount > 0 then
5221 g_auth_fail := 'Y';
5222 INSERT INTO ar_autorec_exceptions
5223 (batch_id,
5224 request_id,
5225 payment_schedule_id,
5226 cash_receipt_id,
5227 paying_customer_id,
5228 exception_code,
5229 additional_message,
5230 last_update_date,
5231 last_updated_by,
5232 creation_date,
5233 created_by,
5234 last_update_login,
5235 program_application_id,
5236 program_id,
5237 program_update_date)
5238 SELECT /*+ push_pred(trxn_ext) */ p_batch_id,
5239 pg_request_id,
5240 r.payment_schedule_id,
5241 cr.cash_receipt_id,
5242 cr.pay_from_customer,
5243 'AR_CC_AUTH_FAILED',
5244 'Failure in Authorization',
5245 sysdate,
5246 pg_last_updated_by,
5247 sysdate,
5248 pg_created_by,
5249 pg_last_update_login,
5250 pg_program_application_id,
5251 pg_program_id,
5252 sysdate
5253 FROM ar_receipts_gt r,
5254 ar_cash_receipts cr,
5255 ar_cash_receipt_history crh,
5256 (SELECT op.trxn_extension_id, summ.status
5257 FROM iby_trxn_summaries_all summ,
5258 iby_fndcpt_tx_operations op
5259 WHERE(summ.transactionid = op.transactionid)
5260 AND(reqtype = 'ORAPMTREQ')
5261 AND(status IN(0, 100, 111, 31, 32))
5262 AND((trxntypeid IN(2, 3)) OR((trxntypeid = 20)
5263 AND(summ.trxnmid =
5264 (SELECT MAX(trxnmid)
5265 FROM iby_trxn_summaries_all
5266 WHERE transactionid = summ.transactionid
5267 AND(reqtype = 'ORAPMTREQ')
5268 AND(status IN(0, 100, 111, 31, 32))
5269 AND(trxntypeid = 20)))))
5270 ) trxn_ext
5271 WHERE r.gt_id = p_gt_id
5272 AND cr.cash_receipt_id = r.cash_receipt_id
5273 AND crh.cash_receipt_id = cr.cash_receipt_id
5274 AND crh.status = 'CONFIRMED'
5275 AND crh.current_record_flag = 'Y'
5276 AND cr.payment_trxn_extension_id = trxn_ext.trxn_extension_id(+)
5277 AND trxn_ext.status IS NULL;
5278
5279 IF PG_DEBUG in ('Y', 'C') THEN
5280 fnd_file.put_line(FND_FILE.LOG,'insert into autorec_exceptions count : '||sql%rowcount);
5281 END IF;
5282 end if;
5283
5284 IF PG_DEBUG in ('Y', 'C') THEN
5285 fnd_file.put_line(FND_FILE.LOG,'control_check()-');
5286 END IF;
5287
5288 EXCEPTION
5289 WHEN OTHERS THEN
5290 IF PG_DEBUG in ('Y', 'C') THEN
5291 fnd_file.put_line(FND_FILE.LOG,'Error in Control check routine.');
5292 END IF;
5293 END CONTROL_CHECK;
5294
5295
5296
5297 procedure populate_cached_data(p_rcpt_creation_rec OUT NOCOPY rcpt_creation_info) IS
5298 BEGIN
5299 p_rcpt_creation_rec := g_rcpt_creation_rec;
5300 END;
5301
5302 procedure populate_cached_data(p_receipt_info_rec OUT NOCOPY receipt_info_rec) IS
5303 BEGIN
5304 p_receipt_info_rec := g_rcpt_info_rec;
5305 END;
5306
5307
5308 END AR_AUTOREC_API;