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