[Home] [Help]
PACKAGE BODY: APPS.ARP_AUTOMATIC_CLEARING_PKG
Source
1 PACKAGE BODY arp_automatic_clearing_pkg AS
2 /* $Header: ARRXACRB.pls 120.8.12010000.4 2008/11/18 18:07:31 aghoraka ship $ */
3 PG_DEBUG varchar2(1) := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
4 procedure expand_stmt
5 (
6 p_customer_name_low IN VARCHAR2,
7 p_customer_name_high IN VARCHAR2,
8 p_customer_number_low IN VARCHAR2,
9 p_customer_number_high IN VARCHAR2,
10 p_receipt_number_low IN VARCHAR2,
11 p_receipt_number_high IN VARCHAR2,
12 p_remittance_bank_account_id IN NUMBER,
13 p_payment_method_id IN NUMBER,
14 p_batch_id IN NUMBER,
15 statement IN OUT NOCOPY VARCHAR2 );
16 --
17 procedure main_select_risk
18 (
19 statement IN OUT NOCOPY VARCHAR2,
20 p_total_workers IN NUMBER DEFAULT 0,
21 p_request_id IN NUMBER DEFAULT -1 );
22 --
23 procedure main_select_factored
24 (
25 statement IN OUT NOCOPY VARCHAR2,
26 p_total_workers IN NUMBER DEFAULT 0,
27 p_request_id IN NUMBER DEFAULT -1 );
28 --
29 procedure main_select_remitted
30 (
31 statement IN OUT NOCOPY VARCHAR2,
32 p_total_workers IN NUMBER DEFAULT 0,
33 p_request_id IN NUMBER DEFAULT -1 );
34 --
35 procedure ar_bind_variables
36 (
37 p_customer_name_low IN VARCHAR2,
38 p_customer_name_high IN VARCHAR2,
39 p_customer_number_low IN VARCHAR2,
40 p_customer_number_high IN VARCHAR2,
41 p_receipt_number_low IN VARCHAR2,
42 p_receipt_number_high IN VARCHAR2,
43 p_remittance_bank_account_id IN NUMBER,
44 p_payment_method_id IN NUMBER,
45 p_batch_id IN NUMBER,
46 c IN integer );
47 --
48 procedure clr_remit_disc_risk_receipts
49 ( p_clear_date IN DATE,
50 p_gl_date IN DATE,
51 p_customer_name_low IN VARCHAR2,
52 p_customer_name_high IN VARCHAR2,
53 p_customer_number_low IN VARCHAR2,
54 p_customer_number_high IN VARCHAR2,
55 p_receipt_number_low IN VARCHAR2,
56 p_receipt_number_high IN VARCHAR2,
57 p_remittance_bank_account_id IN NUMBER,
58 p_payment_method_id IN NUMBER,
59 p_exchange_rate_type IN VARCHAR2,
60 p_batch_id IN NUMBER,
61 remitted_or_factored_or_risk IN integer );
62 --
63 procedure clr_remit_disc_risk_rcpts_pa
64 ( p_worker_number IN NUMBER,
65 p_request_id IN NUMBER,
66 remitted_or_factored_or_risk IN NUMBER);
67 --
68 procedure ar_bind_variables_parallel
69 (
70 p_worker_number IN NUMBER,
71 p_request_id IN NUMBER,
72 c IN integer );
73 --
74 procedure main_select_remitted_parallel
75 (
76 statement IN OUT NOCOPY VARCHAR2 );
77 --
78 procedure main_select_factored_parallel
79 (
80 statement IN OUT NOCOPY VARCHAR2 );
81 --
82 procedure main_select_risk_parallel
83 (
84 statement IN OUT NOCOPY VARCHAR2 );
85 --
86 procedure populate_interim_table
87 ( p_clear_date IN DATE,
88 p_gl_date IN DATE,
89 p_customer_name_low IN VARCHAR2,
90 p_customer_name_high IN VARCHAR2,
91 p_customer_number_low IN VARCHAR2,
92 p_customer_number_high IN VARCHAR2,
93 p_receipt_number_low IN VARCHAR2,
94 p_receipt_number_high IN VARCHAR2,
95 p_remittance_bank_account_id IN NUMBER,
96 p_payment_method_id IN NUMBER,
97 p_exchange_rate_type IN VARCHAR2,
98 p_batch_id IN NUMBER,
99 remitted_or_factored_or_risk IN integer,
100 p_request_id IN NUMBER,
101 p_total_workers IN NUMBER);
102 --
103 procedure expand_stmt
104 (
105 p_customer_name_low IN VARCHAR2,
106 p_customer_name_high IN VARCHAR2,
107 p_customer_number_low IN VARCHAR2,
108 p_customer_number_high IN VARCHAR2,
109 p_receipt_number_low IN VARCHAR2,
110 p_receipt_number_high IN VARCHAR2,
111 p_remittance_bank_account_id IN NUMBER,
112 p_payment_method_id IN NUMBER,
113 p_batch_id IN NUMBER,
114 statement IN OUT NOCOPY VARCHAR2 ) IS
115 --
116 BEGIN
117 --
118 IF ( p_customer_number_low IS NOT NULL ) THEN
119 statement := statement ||
120 'and c.account_number >= :b_cust_number_low ';
121 END IF;
122 --
123 IF ( p_customer_number_high IS NOT NULL ) THEN
124 statement := statement ||
125 'and c.account_number <= :b_cust_number_high ';
126 END IF;
127 --
128 IF ( p_customer_name_low IS NOT NULL ) THEN
129 statement := statement ||
130 'and party.party_name >= :b_cust_name_low ';
131 END IF;
132 --
133 IF ( p_customer_name_high IS NOT NULL ) THEN
134 statement := statement ||
135 'and party.party_name <= :b_cust_name_high ';
136 END IF;
137 --
138 IF ( p_receipt_number_low IS NOT NULL ) THEN
139 statement := statement ||
140 'and cr.receipt_number >= :b_receipt_number_low ';
141 END IF;
142 --
143 IF ( p_receipt_number_high IS NOT NULL ) THEN
144 statement := statement ||
145 'and cr.receipt_number <= :b_receipt_number_high ';
146 END IF;
147 --
148 IF ( p_remittance_bank_account_id IS NOT NULL ) THEN
149 statement := statement ||
150 'and cr.remit_bank_acct_use_id = :b_remittance_bank_account_id ';
151 END IF;
152 --
153 IF ( p_payment_method_id IS NOT NULL ) THEN
154 statement := statement ||
155 'and cr.receipt_method_id = :b_payment_method ';
156 END IF;
157 --
158 -- Bug 706935.
159 -- Added these lines.
160
161 IF (p_batch_id IS NOT NULL ) THEN
162 statement := statement ||
163 'and crh.batch_id= :b_batch_id';
164 END IF;
165 --
166 EXCEPTION
167 WHEN OTHERS THEN
168 arp_standard.debug('Exception: ar_automatic_clearing_pkg ');
169 RAISE;
170 --
171 END;
172 --
173 procedure main_select_risk
174 (
175 statement IN OUT NOCOPY VARCHAR2,
176 p_total_workers IN NUMBER DEFAULT 0,
177 p_request_id IN NUMBER DEFAULT -1 ) IS
178 --
179 BEGIN
180 --
181 /* 07-JUL-2000 J Rautiainen BR Implementation
182 * Receipts created by the Bills Receivable transaction remittance process
183 * having BR_REMIT receipt class cannot be risk eliminated */
184 /*5444413 Index hint added*/
185 statement := statement ||
186 'select ' ||
187 'cr.cash_receipt_id, ' ||
188 ':b_clear_date, ' ||
189 'greatest ( crh.gl_date, :b_gl_date ), ' ||
190 ' ''AR_AUTOMATIC_CLEARING'', ' ||
191 ' ''10.6'', ' ||
192 'crh.cash_receipt_history_id ';
193 IF p_total_workers <> 0 THEN
194 statement := statement ||
195 ', MOD(CEIL((DENSE_RANK() over(order by crh.cash_receipt_id))/5000), '
196 || p_total_workers ||') + 1, '||
197 p_request_id ||' , ''RISK'' ';
198 END IF;
199 statement := statement || 'from ' ||
200 'ar_cash_receipts cr, ' ||
201 'ar_cash_receipt_history crh, ' ||
202 'ar_cash_receipt_history crh2, ' ||
203 'ar_payment_schedules ps, ' ||
204 'hz_customer_profiles cps, ' ||
205 'hz_customer_profiles cpc, ' ||
206 'ar_receipt_method_accounts rma, ' ||
207 'ar_receipt_methods rm, ' ||
208 'ar_receipt_classes rc, ' ||
209 'ce_bank_accounts cba, ' ||
210 'ce_bank_acct_uses ba, ' ||
211 'hz_cust_accounts c, ' ||
212 'hz_parties party ' ||
213 'where ' ||
214 'cr.cash_receipt_id = crh.cash_receipt_id ' ||
215 'and crh.prv_stat_cash_receipt_hist_id '||
216 ' = crh2.cash_receipt_history_id(+) ' ||
217 'and cr.cash_receipt_id = ps.cash_receipt_id ' ||
218 'and greatest( nvl(ps.due_date, cr.deposit_date), ' ||
219 ' nvl( crh2.trx_date, ' ||
220 ' nvl(ps.due_date, cr.deposit_date )))'||
221 ' + nvl(rma.risk_elimination_days,0) ' ||
222 ' <= :b_clear_date ' ||
223 'and crh.current_record_flag = ''Y'' ' ||
224 'and crh.status = ''CLEARED'' ' ||
225 'and crh.factor_flag = ''Y'' ' ||
226 'and rm.receipt_method_id = cr.receipt_method_id ' ||
227 'and rc.receipt_class_id = rm.receipt_class_id ' ||
228 'and rc.creation_method_code <> ''BR_REMIT'' ' ||
229 'and rma.receipt_method_id = cr.receipt_method_id ' ||
230 'and rma.remit_bank_acct_use_id = ba.bank_acct_use_id ' ||
231 'and rma.remit_bank_acct_use_id = cr.remit_bank_acct_use_id '||
232 'and ba.bank_account_id = cba.bank_account_id ' ||
233 'and cr.pay_from_customer = c.cust_account_id(+) ' ||
234 'and c.party_id = party.party_id(+) ' ||
235 'and cr.pay_from_customer = cpc.cust_account_id(+) ' ||
236 'and cpc.site_use_id(+) is null ' ||
237 'and cr.pay_from_customer = cps.cust_account_id(+) ' ||
238 'and cr.customer_site_use_id = cps.site_use_id(+) ';
239 IF PG_DEBUG in ('Y', 'C') THEN
240 arp_standard.debug('Inside main_select_risk');
241 arp_standard.debug( 'Statement ' || statement);
242 END IF;
243 --
244 EXCEPTION
245 WHEN OTHERS THEN
246 arp_standard.debug('Exception: ar_automatic_clearing_pkg ');
247 RAISE;
248 --
249 END;
250 --
251 procedure main_select_factored
252 (
253 statement IN OUT NOCOPY VARCHAR2,
254 p_total_workers IN NUMBER DEFAULT 0,
255 p_request_id IN NUMBER DEFAULT -1 ) IS
256 --
257 BEGIN
258 --
259 /* Bug 2484984 Modified the call gl_currency_api.get_rate so that
260 there is an NVL for the exchange_rate_type parameter rather
261 than an NVL for the function call itself . */
262
263 /* Bug 3820774 Replaced the get_rate with get_rate_sql. */
264 /*Bug5444413 Added hint */
265 statement := statement ||
266 'select /*+ INDEX (CRH AR_CASH_RECEIPT_HISTORY_N6) USE_NL(crh,cr,rm,rc,rma,c,party,ba) */ ' ||
267 'cr.cash_receipt_id, '||
268 ':b_clear_date, ' ||
269 'greatest ( crh.gl_date, :b_gl_date ), ' ||
270 'nvl(:b_clear_date,crh.exchange_date), ' ||
271 'nvl(:b_exchange_rate_type,cr.exchange_rate_type), ' ||
272 'decode(nvl(:b_exchange_rate_type,cr.exchange_rate_type), ' ||
273 ' ''User'',cr.exchange_rate, ' ||
274 ' gl_currency_api.get_rate_sql(:b_set_of_bks_id,cr.currency_code,:b_clear_date, ' ||
275 ' nvl(:b_exchange_rate_type,cr.exchange_rate_type))), ' ||
276 'cba.currency_code, ' ||
277 'decode(cr.currency_code,
278 cba.currency_code,crh.amount,crh.acctd_amount), ' ||
279 'decode(cr.currency_code,
280 cba.currency_code,crh.factor_discount_amount,crh.acctd_factor_discount_amount), ' ||
281 ' ''AR_AUTOMATIC_CLEARING'', ' ||
282 ' ''10.6'', ' ||
283 'crh.cash_receipt_history_id, ' ||
284 'crh.amount, ' ||
285 'crh.factor_discount_amount, ' ||
286 'cr.currency_code, ' ||
287 'crh.exchange_rate ' ;
288 IF p_total_workers <> 0 THEN
289 statement := statement ||
290 ', MOD(CEIL((DENSE_RANK() over(order by crh.cash_receipt_id))/5000), '
291 || p_total_workers ||') + 1, '||
292 p_request_id ||' , ''FACTOR'' ';
293 END IF;
294 statement := statement || 'from ' ||
295 'ar_cash_receipts cr, ' ||
296 'ar_cash_receipt_history crh, ' ||
297 'ar_receipt_method_accounts rma, ' ||
298 'ce_bank_accounts cba, ' ||
299 'ce_bank_acct_uses ba, ' ||
300 'ar_receipt_methods rm, ' ||
301 'ar_receipt_classes rc, ' ||
302 'hz_cust_accounts c, ' ||
303 'hz_parties party ' ||
304 'where ' ||
305 'cr.cash_receipt_id = crh.cash_receipt_id ' ||
306 'and crh.trx_date <= :b_clear_date ' ||
307 'and crh.current_record_flag = ''Y'' ' ||
308 'and crh.status = ''REMITTED'' ' ||
309 'and crh.factor_flag = ''Y'' ' ||
310 'and rma.receipt_method_id = cr.receipt_method_id ' ||
311 'and rm.receipt_method_id = cr.receipt_method_id ' ||
312 'and rc.receipt_class_id = rm.receipt_class_id ' ||
313 'and rc.clear_flag = ''S'' ' ||
314 'and rma.remit_bank_acct_use_id = ba.bank_acct_use_id ' ||
315 'and rma.remit_bank_acct_use_id = cr.remit_bank_acct_use_id ' ||
316 'and ba.bank_account_id = cba.bank_account_id ' ||
317 'and cr.pay_from_customer = c.cust_account_id(+) ' ||
318 'and c.party_id = party.party_id(+) ' ||
319 -- Bug 706935.
320 -- ||
321 -- 'and crh.batch_id = nvl(:b_batch_id, crh.batch_id) ';
322 --
323 -- Bug 2132264
324 'and not exists ( ' ||
325 'select ''debit memo reversal'' ' ||
326 'from ar_payment_schedules ps1 ' ||
327 'where ps1.reversed_cash_receipt_id = cr.cash_receipt_id ' ||
328 'and ps1.class = ''DM'') ';
329 IF PG_DEBUG in ('Y', 'C') THEN
330 arp_standard.debug('Inside main_select_factored');
331 arp_standard.debug( 'Statement ' || statement);
332 END IF;
333 EXCEPTION
334 WHEN OTHERS THEN
335 arp_standard.debug('Exception: ar_automatic_clearing_pkg ');
336 RAISE;
337 --
338 END;
339 --
340 procedure main_select_remitted
341 (
342 statement IN OUT NOCOPY VARCHAR2,
343 p_total_workers IN NUMBER DEFAULT 0,
344 p_request_id IN NUMBER DEFAULT -1 ) IS
345 --
346 BEGIN
347 --
348 /* Bug 2484984 Modified the call gl_currency_api.get_rate so that
349 there is an NVL for the exchange_rate_type parameter rather
350 than an NVL for the function call itself . */
351 /* Bug 3820774 Replaced the get_rate with get_rate_sql. */
352 /*5444413 hint added here*/
353 statement := statement || 'select ' ||
354 'cr.cash_receipt_id, ' ||
355 ':b_clear_date, ' ||
356 'greatest ( crh.gl_date, :b_gl_date ), ' ||
357 'nvl(:b_clear_date, crh.exchange_date), ' ||
358 'nvl(:b_exchange_rate_type,cr.exchange_rate_type), ' ||
359 'decode(nvl(:b_exchange_rate_type,cr.exchange_rate_type), ' ||
360 ' ''User'',cr.exchange_rate, ' ||
361 ' gl_currency_api.get_rate_sql(:b_set_of_bks_id,cr.currency_code,:b_clear_date, ' ||
362 ' nvl(:b_exchange_rate_type,cr.exchange_rate_type))), ' ||
363 'cba.currency_code, ' ||
364 'decode(cr.currency_code,
365 cba.currency_code,crh.amount,crh.acctd_amount), ' ||
366 'decode(cr.currency_code,
367 cba.currency_code,crh.factor_discount_amount,crh.acctd_factor_discount_amount), ' ||
368 ' ''AR_AUTOMATIC_CLEARING'', ' ||
369 ' ''10.6'', ' ||
370 'crh.cash_receipt_history_id, ' ||
371 'crh.amount, ' ||
375 IF p_total_workers <> 0 THEN
372 'crh.factor_discount_amount, ' ||
373 'cr.currency_code, ' ||
374 'crh.exchange_rate ' ;
376 statement := statement ||
377 ', MOD(CEIL((DENSE_RANK() over(order by crh.cash_receipt_id))/5000), '
378 || p_total_workers ||') + 1, '||
379 p_request_id ||' , ''REMIT'' ';
380 END IF;
381 statement := statement || 'from ar_cash_receipts cr, ' ||
382 'ar_cash_receipt_history crh, ' ||
383 'ar_payment_schedules ps, ' ||
384 'hz_customer_profiles cpc, ' ||
385 'hz_customer_profiles cps, ' ||
386 'ar_receipt_method_accounts rma, ' ||
387 'ar_receipt_methods rm, ' ||
388 'ar_receipt_classes rc, ' ||
389 'ce_bank_accounts cba, ' ||
390 'ce_bank_acct_uses ba, ' ||
391 'hz_cust_accounts c, ' ||
392 'hz_parties party ' ||
393 'where cr.cash_receipt_id = crh.cash_receipt_id ' ||
394 'and cr.cash_receipt_id = ps.cash_receipt_id ' ||
395 'and greatest( nvl(ps.due_date, cr.deposit_date), ' ||
396 ' crh.trx_date )'||
397 ' + nvl(nvl(cps.clearing_days, ' ||
398 ' nvl(cpc.clearing_days, ' ||
399 ' rma.clearing_days)), ' ||
400 ' 0) ' ||
401 ' <= :b_clear_date ' ||
402 'and crh.current_record_flag = ''Y'' ' ||
403 'and crh.status = ''REMITTED'' ' ||
404 'and crh.factor_flag = ''N'' ' ||
405 'and rma.receipt_method_id = cr.receipt_method_id ' ||
406 'and rm.receipt_method_id = cr.receipt_method_id ' ||
407 'and rc.receipt_class_id = rm.receipt_class_id ' ||
408 'and rc.clear_flag = ''S'' ' ||
409 'and rma.remit_bank_acct_use_id = ba.bank_acct_use_id ' ||
410 'and rma.remit_bank_acct_use_id = cr.remit_bank_acct_use_id ' ||
411 'and ba.bank_account_id = cba.bank_account_id ' ||
412 'and cr.pay_from_customer = c.cust_account_id(+) ' ||
413 'and c.party_id = party.party_id(+) ' ||
414 'and cr.pay_from_customer = cpc.cust_account_id(+) ' ||
415 'and cpc.site_use_id(+) is null ' ||
416 'and cr.pay_from_customer = cps.cust_account_id(+) ' ||
417 'and cr.customer_site_use_id = cps.site_use_id(+) ' ||
418 -- Bug 706935.
419 -- ||
420 -- 'and crh.batch_id = nvl(:b_batch_id, crh.batch_id) ';
421 -- Bug 2132264
422 'and not exists ( ' ||
423 'select ''debit memo reversal'' ' ||
424 'from ar_payment_schedules ps1 ' ||
425 'where ps1.reversed_cash_receipt_id = cr.cash_receipt_id ' ||
426 'and ps1.class = ''DM'') ';
427 IF PG_DEBUG in ('Y', 'C') THEN
428 arp_standard.debug('Inside main_select_remitted');
429 arp_standard.debug( 'Statement ' || statement);
430 END IF;
431 --
432 EXCEPTION
433 WHEN OTHERS THEN
434 arp_standard.debug('Exception: ar_automatic_clearing_pkg ');
435 RAISE;
436 --
437 END;
438 --
439 procedure ar_bind_variables
440 (
441 p_customer_name_low IN VARCHAR2,
442 p_customer_name_high IN VARCHAR2,
443 p_customer_number_low IN VARCHAR2,
444 p_customer_number_high IN VARCHAR2,
445 p_receipt_number_low IN VARCHAR2,
446 p_receipt_number_high IN VARCHAR2,
447 p_remittance_bank_account_id IN NUMBER,
448 p_payment_method_id IN NUMBER,
449 p_batch_id IN NUMBER, -- added for bug 706935.
450 c IN integer ) IS
451 l_worker_number NUMBER;
452 --
453 BEGIN
454 --
455 IF ( p_customer_number_low IS NOT NULL ) THEN
456 dbms_sql.bind_variable(c, ':b_cust_number_low' ,p_customer_number_low);
457 END IF;
458 --
459 IF ( p_customer_number_high IS NOT NULL ) THEN
460 dbms_sql.bind_variable(c, ':b_cust_number_high' ,p_customer_number_high);
461 END IF;
462 --
463 IF ( p_customer_name_low IS NOT NULL ) THEN
464 dbms_sql.bind_variable(c, ':b_cust_name_low' ,p_customer_name_low);
465 END IF;
466 --
467 IF ( p_customer_name_high IS NOT NULL ) THEN
468 dbms_sql.bind_variable(c, ':b_cust_name_high' ,p_customer_name_high);
469 END IF;
470 --
471 IF ( p_receipt_number_low IS NOT NULL ) THEN
472 dbms_sql.bind_variable(c, ':b_receipt_number_low' ,p_receipt_number_low);
473 END IF;
474 --
475 IF ( p_receipt_number_high IS NOT NULL ) THEN
476 dbms_sql.bind_variable(c, ':b_receipt_number_high' ,p_receipt_number_high);
477 END IF;
478 --
479 IF ( p_remittance_bank_account_id IS NOT NULL ) THEN
480 dbms_sql.bind_variable(c, ':b_remittance_bank_account_id' ,p_remittance_bank_account_id);
481 END IF;
482 --
483 IF ( p_payment_method_id IS NOT NULL ) THEN
484 dbms_sql.bind_variable(c, ':b_payment_method' ,p_payment_method_id);
485 END IF;
486
487 -- Added for Bug 706935
488 --
489 IF ( p_batch_id IS NOT NULL ) THEN
490 dbms_sql.bind_variable(c, ':b_batch_id' ,p_batch_id);
491 END IF;
492 --
493 EXCEPTION
494 WHEN OTHERS THEN
498 END;
495 arp_standard.debug('Exception: ar_automatic_clearing_pkg ');
496 RAISE;
497 --
499 --
500 procedure clr_remit_disc_risk_receipts
501 ( p_clear_date IN DATE,
502 p_gl_date IN DATE,
503 p_customer_name_low IN VARCHAR2,
504 p_customer_name_high IN VARCHAR2,
505 p_customer_number_low IN VARCHAR2,
506 p_customer_number_high IN VARCHAR2,
507 p_receipt_number_low IN VARCHAR2,
508 p_receipt_number_high IN VARCHAR2,
509 p_remittance_bank_account_id IN NUMBER,
510 p_payment_method_id IN NUMBER,
511 p_exchange_rate_type IN VARCHAR2,
512 p_batch_id IN NUMBER,
513 remitted_or_factored_or_risk IN integer ) IS
514 --
515 statement varchar2(3000) := NULL;
516 c integer;
517 ignore integer;
518 v_cr_id ar_cash_receipts.cash_receipt_id%TYPE;
519 v_crh_id ar_cash_receipt_history.cash_receipt_history_id%TYPE;
520 v_trx_date ar_cash_receipt_history.trx_date%TYPE;
521 v_gl_date ar_cash_receipt_history.gl_date%TYPE;
522 v_ex_date ar_cash_receipt_history.exchange_date%TYPE;
523 v_ex_rate_type ar_cash_receipts.exchange_rate_type%TYPE;
524 v_ex_rate ar_cash_receipts.exchange_rate%TYPE;
525 v_currency ce_bank_accounts.currency_code%TYPE;
526 v_amount ar_cash_receipt_history.amount%TYPE;
527 v_fac_disc_amount ar_cash_receipt_history.factor_discount_amount%TYPE;
528 v_mod_name varchar2(30);
529 v_mod_vers varchar2(5);
530 p_crh_id ar_cash_receipt_history.cash_receipt_history_id%TYPE;
531 v_set_of_bks_id ar_system_parameters.set_of_books_id%TYPE;
532 p_cr_rec ar_cash_receipts%ROWTYPE;
533 p_crh_rec ar_cash_receipt_history%ROWTYPE;
534 locked BOOLEAN;
535
536 -- 785113: Added to compute new acctd amts based on a different exchange rate.
537 v_crh_amount ar_cash_receipt_history.amount%TYPE;
538 v_crh_fac_disc_amt ar_cash_receipt_history.amount%TYPE;
539 v_ex_rate_old ar_cash_receipts.exchange_rate%TYPE;
540 v_cr_currency ce_bank_accounts.currency_code%TYPE;
541
542 --
543 BEGIN
544 --
545 select set_of_books_id INTO v_set_of_bks_id from ar_system_parameters;
546 --
547 IF ( remitted_or_factored_or_risk = 1) THEN
548 main_select_remitted(
549 statement,
550 0,
551 -1 );
552 END IF;
553 --
554 IF ( remitted_or_factored_or_risk = 2) THEN
555 main_select_factored(
556 statement,
557 0,
558 -1 );
559 END IF;
560 --
561 IF ( remitted_or_factored_or_risk = 3) THEN
562 main_select_risk(
563 statement,
564 0,
565 -1 );
566 END IF;
567 --
568 c := dbms_sql.open_cursor;
569 --
570 -- Build expanded select-statement depending on parameters
571 --
572 expand_stmt( p_customer_name_low,
573 p_customer_name_high,
574 p_customer_number_low,
575 p_customer_number_high,
576 p_receipt_number_low,
577 p_receipt_number_high,
578 p_remittance_bank_account_id,
579 p_payment_method_id,
580 p_batch_id,
581 statement );
582 --
583 dbms_sql.parse(c, statement, dbms_sql.native);
584 --
585 -- Bind variables
586 --
587 dbms_sql.bind_variable(c, ':b_clear_date' ,p_clear_date);
588 dbms_sql.bind_variable(c, ':b_gl_date' ,p_gl_date);
589 --
590 IF ( remitted_or_factored_or_risk <> 3) THEN
591 dbms_sql.bind_variable(c, ':b_exchange_rate_type' ,p_exchange_rate_type);
592 dbms_sql.bind_variable(c, ':b_set_of_bks_id' ,v_set_of_bks_id);
593
594 -- Bug 706935.
595 -- Removed this line.
596 -- dbms_sql.bind_variable(c, ':b_batch_id' ,p_batch_id);
597
598 END IF;
599 --
600 ar_bind_variables( p_customer_name_low,
601 p_customer_name_high,
602 p_customer_number_low,
603 p_customer_number_high,
604 p_receipt_number_low,
605 p_receipt_number_high,
606 p_remittance_bank_account_id,
607 p_payment_method_id,
608 p_batch_id,
609 c );
610 --
611 IF ( remitted_or_factored_or_risk = 3) THEN
612 dbms_sql.define_column(c, 1, v_cr_id);
613 dbms_sql.define_column(c, 2, v_trx_date);
614 dbms_sql.define_column(c, 3, v_gl_date);
615 dbms_sql.define_column(c, 4, v_mod_name, 30);
616 dbms_sql.define_column(c, 5, v_mod_vers, 5);
617 dbms_sql.define_column(c, 6, v_crh_id);
618 ELSE
619 dbms_sql.define_column(c, 1, v_cr_id);
620 dbms_sql.define_column(c, 2, v_trx_date);
621 dbms_sql.define_column(c, 3, v_gl_date);
622 dbms_sql.define_column(c, 4, v_ex_date);
623 dbms_sql.define_column(c, 5, v_ex_rate_type, 30);
624 dbms_sql.define_column(c, 6, v_ex_rate);
625 dbms_sql.define_column(c, 7, v_currency, 15);
626 dbms_sql.define_column(c, 8, v_amount);
627 dbms_sql.define_column(c, 9, v_fac_disc_amount);
628 dbms_sql.define_column(c, 10, v_mod_name, 30);
629 dbms_sql.define_column(c, 11, v_mod_vers, 5);
630 dbms_sql.define_column(c, 12, v_crh_id);
631 dbms_sql.define_column(c, 13, v_crh_amount);
632 dbms_sql.define_column(c, 14, v_crh_fac_disc_amt);
633 dbms_sql.define_column(c, 15, v_cr_currency, 15);
634 dbms_sql.define_column(c, 16, v_ex_rate_old);
635 END IF;
636 --
637 ignore := dbms_sql.execute(c);
638 --
642 dbms_sql.column_value(c, 1, v_cr_id);
639 IF ( remitted_or_factored_or_risk = 3) THEN
640 LOOP
641 IF dbms_sql.fetch_rows(c) > 0 THEN
643 dbms_sql.column_value(c, 2, v_trx_date);
644 dbms_sql.column_value(c, 3, v_gl_date);
645 dbms_sql.column_value(c, 4, v_mod_name);
646 dbms_sql.column_value(c, 5, v_mod_vers);
647 dbms_sql.column_value(c, 6, v_crh_id);
648 --
649 -- Lock rows
650 --
651 BEGIN
652 locked := TRUE;
653 p_cr_rec.cash_receipt_id := v_cr_id;
654 p_crh_rec.cash_receipt_history_id := v_crh_id;
655 arp_cash_receipts_pkg.nowaitlock_fetch_p ( p_cr_rec );
656 arp_cr_history_pkg.nowaitlock_fetch_p ( p_crh_rec );
657 EXCEPTION WHEN OTHERS THEN
658 locked := FALSE;
659 END;
660 --
661 -- Call Risk Handler
662 --
663 IF ( locked AND p_crh_rec.current_record_flag = 'Y' ) THEN
664 arp_cashbook.risk_eliminate ( v_cr_id,
665 v_trx_date,
666 v_gl_date,
667 v_mod_name,
668 v_mod_vers,
669 p_crh_id );
670 END IF;
671 ELSE
672 -- no more rows
673 EXIT;
674 END IF;
675 --
676 END LOOP;
677 ELSE
678 LOOP
679 IF dbms_sql.fetch_rows(c) > 0 THEN
680 dbms_sql.column_value(c, 1, v_cr_id);
681 dbms_sql.column_value(c, 2, v_trx_date);
682 dbms_sql.column_value(c, 3, v_gl_date);
683 dbms_sql.column_value(c, 4, v_ex_date);
684 dbms_sql.column_value(c, 5, v_ex_rate_type);
685 dbms_sql.column_value(c, 6, v_ex_rate);
686 dbms_sql.column_value(c, 7, v_currency);
687 dbms_sql.column_value(c, 8, v_amount);
688 dbms_sql.column_value(c, 9, v_fac_disc_amount);
689 dbms_sql.column_value(c, 10, v_mod_name);
690 dbms_sql.column_value(c, 11, v_mod_vers);
691 dbms_sql.column_value(c, 12, v_crh_id);
692 dbms_sql.column_value(c, 13, v_crh_amount);
693 dbms_sql.column_value(c, 14, v_crh_fac_disc_amt);
694 dbms_sql.column_value(c, 15, v_cr_currency);
695 dbms_sql.column_value(c, 16, v_ex_rate_old);
696 --
697 -- Lock rows
698 --
699 BEGIN
700 locked := TRUE;
701 p_cr_rec.cash_receipt_id := v_cr_id;
702 p_crh_rec.cash_receipt_history_id := v_crh_id;
703 arp_cash_receipts_pkg.nowaitlock_fetch_p ( p_cr_rec );
704 arp_cr_history_pkg.nowaitlock_fetch_p ( p_crh_rec );
705 EXCEPTION WHEN OTHERS THEN
706 locked := FALSE;
707 END;
708 --
709 -- Call Clear Handler
710 --
711 /* bug: 3820774 Added the condition for v_ex_rate.
712 Deleted the condition of 'v_ex_rate IS NULL'. */
713
714 IF ( locked AND p_crh_rec.current_record_flag = 'Y'
715 AND v_ex_rate > 0 ) THEN
716
717 IF (v_currency <> v_cr_currency) THEN
718 -- 785113
719 -- Bank currency not equal to receipt currency. Calculate
720 -- new accounted amounts using the receipt amount and exch.rate.
721 --
722 v_amount := arp_util.functional_amount(
723 v_crh_amount,
724 ARP_GLOBAL.functional_currency,
725 nvl(v_ex_rate,nvl(v_ex_rate_old,1)),
726 NULL,NULL );
727 v_fac_disc_amount := arp_util.functional_amount(
728 nvl(v_crh_fac_disc_amt,0),
729 ARP_GLOBAL.functional_currency,
730 nvl(v_ex_rate,1),
731 NULL,NULL );
732 END IF;
733
734 arp_standard.debug('Before calling arp_cashbook.clear');
735 arp_standard.debug('remitted_or_factored_or_risk :' || to_char(remitted_or_factored_or_risk));
736 arp_standard.debug('v_cr_id :' || to_char(v_cr_id));
737 arp_standard.debug('v_trx_date[clear_date] :' || to_char(v_trx_date));
738 arp_standard.debug('v_ex_date :' || to_char(v_ex_date));
739 arp_standard.debug('v_ex_rate :' || to_char(v_ex_rate));
740 arp_standard.debug('v_ex_rate_type :' || v_ex_rate_type);
741 arp_standard.debug('v_currency :' || v_currency);
742 arp_standard.debug('New v_amount :' || to_char(v_amount));
743 arp_standard.debug('v_fac_disc_amount :' || to_char(v_fac_disc_amount));
744 arp_standard.debug('v_ex_rate_old :' || to_char(v_ex_rate_old));
745 arp_standard.debug('v_cr_currency :' || v_cr_currency);
746
747 arp_cashbook.clear( v_cr_id,
748 v_trx_date,
749 v_gl_date,
750 NULL, -- value date parameter
751 v_ex_date,
752 v_ex_rate_type,
753 v_ex_rate,
754 v_currency,
755 v_amount,
756 v_fac_disc_amount,
757 v_mod_name,
758 v_mod_vers,
759 p_crh_id );
760 /* bug: 3820774 Added the handling of exception rate. */
761 ELSE
762 IF v_ex_rate = -1
763 THEN
767 arp_standard.debug('v_ex_rate_type :' || v_ex_rate_type);
764 arp_standard.debug('ar_automatic_clearing_pkg: NO_RATE');
765 arp_standard.debug('v_cr_id :' || to_char(v_cr_id));
766 arp_standard.debug('v_ex_date :' || to_char(v_ex_date));
768 arp_standard.debug('v_ex_rate :' || to_char(v_ex_rate));
769
770 fnd_file.put_line(fnd_file.log,'ar_automatic_clearing_pkg: NO_RATE');
771 fnd_file.put_line(fnd_file.log,'v_cr_id :' || to_char(v_cr_id));
772 fnd_file.put_line(fnd_file.log,'v_ex_date :' || to_char(v_ex_date));
773 fnd_file.put_line(fnd_file.log,'v_ex_rate_type :' || v_ex_rate_type);
777 THEN
774 fnd_file.put_line(fnd_file.log,'v_ex_rate :' || to_char(v_ex_rate));
775
776 ELSIF v_ex_rate = -2
778 arp_standard.debug('ar_automatic_clearing_pkg: INVALID_CURRENCY');
779 arp_standard.debug('v_cr_id :' || to_char(v_cr_id));
780 arp_standard.debug('v_ex_date :' || to_char(v_ex_date));
781 arp_standard.debug('v_ex_rate_type :' || v_ex_rate_type);
782 arp_standard.debug('v_ex_rate :' || to_char(v_ex_rate));
783
784 fnd_file.put_line(fnd_file.log,'ar_automatic_clearing_pkg: INVALID_CURRENCY');
785 fnd_file.put_line(fnd_file.log,'v_cr_id :' || to_char(v_cr_id));
786 fnd_file.put_line(fnd_file.log,'v_ex_date :' || to_char(v_ex_date));
787 fnd_file.put_line(fnd_file.log,'v_ex_rate_type :' || v_ex_rate_type);
788 fnd_file.put_line(fnd_file.log,'v_ex_rate :' || to_char(v_ex_rate));
789
790 END IF;
791 END IF;
792 ELSE
793 -- no more rows
794 EXIT;
795 END IF;
796 END LOOP;
797 END IF;
798 --
799 dbms_sql.close_cursor(c);
800 --
801 EXCEPTION
802 WHEN OTHERS THEN
803 arp_standard.debug('Exception: ar_automatic_clearing_pkg ');
804 RAISE;
805 --
806 END;
807 --
808 function ar_automatic_clearing
809 (
810 p_clr_remitted_receipts IN VARCHAR2,
811 p_clr_disc_receipts IN VARCHAR2,
812 p_eliminate_bank_risk IN VARCHAR2,
813 p_clear_date IN DATE,
814 p_gl_date IN DATE,
815 p_customer_name_low IN VARCHAR2,
816 p_customer_name_high IN VARCHAR2,
817 p_customer_number_low IN VARCHAR2,
818 p_customer_number_high IN VARCHAR2,
819 p_receipt_number_low IN VARCHAR2,
820 p_receipt_number_high IN VARCHAR2,
821 p_remittance_bank_account_id IN NUMBER,
822 p_payment_method_id IN NUMBER,
823 p_exchange_rate_type IN VARCHAR2,
824 p_batch_id IN NUMBER,
825 p_undo_clearing IN VARCHAR2
826 ) RETURN BOOLEAN IS
827 --
828 remitted_or_factored_or_risk integer := 0;
829 --
830 BEGIN
831 --
832 IF (p_clr_remitted_receipts = 'Y') THEN
833 remitted_or_factored_or_risk := 1;
834 clr_remit_disc_risk_receipts (
835 p_clear_date,
836 p_gl_date,
837 p_customer_name_low,
838 p_customer_name_high,
839 p_customer_number_low,
840 p_customer_number_high,
841 p_receipt_number_low,
842 p_receipt_number_high,
843 p_remittance_bank_account_id,
844 p_payment_method_id,
845 p_exchange_rate_type,
846 p_batch_id,
847 remitted_or_factored_or_risk);
848 END IF;
849 --
850 IF (p_clr_disc_receipts = 'Y') THEN
851 remitted_or_factored_or_risk := 2;
852 clr_remit_disc_risk_receipts (
853 p_clear_date,
854 p_gl_date,
855 p_customer_name_low,
856 p_customer_name_high,
857 p_customer_number_low,
858 p_customer_number_high,
859 p_receipt_number_low,
860 p_receipt_number_high,
861 p_remittance_bank_account_id,
862 p_payment_method_id,
863 p_exchange_rate_type,
864 p_batch_id,
865 remitted_or_factored_or_risk);
866 END IF;
867 --
868 IF (p_eliminate_bank_risk = 'Y') THEN
869 remitted_or_factored_or_risk := 3;
870 clr_remit_disc_risk_receipts (
871 p_clear_date,
872 p_gl_date,
873 p_customer_name_low,
874 p_customer_name_high,
875 p_customer_number_low,
876 p_customer_number_high,
877 p_receipt_number_low,
878 p_receipt_number_high,
879 p_remittance_bank_account_id,
880 p_payment_method_id,
881 p_exchange_rate_type,
882 p_batch_id,
883 remitted_or_factored_or_risk );
884 END IF;
885 --
886 return(TRUE);
887 --
888 EXCEPTION
889 WHEN OTHERS THEN
890 arp_standard.debug('Exception: ar_automatic_clearing_pkg ');
891 RAISE;
892 END;
893 --
894 /*========================================================================+
895 | PROCEDURE ar_automatic_clearing_parallel |
896 | |
897 | DESCRIPTION |
898 | |
899 | This is created to parallelize the Automatic Clearance for Reecipts |
900 | This procedure is called from ar_auto_clearing_in_parallel() |
901 | PSEUDO CODE/LOGIC |
902 | |
903 | PARAMETERS |
904 | |
905 | |
906 | KNOWN ISSUES |
907 | |
908 | NOTES |
909 | |
910 | |
911 | MODIFICATION HISTORY |
915 procedure ar_automatic_clearing_parallel
912 | Date Author Description of Changes |
913 | 19-FEB-2008 aghoraka Created |
914 *=========================================================================*/
916 ( P_ERRBUF OUT NOCOPY VARCHAR2,
917 P_RETCODE OUT NOCOPY NUMBER,
918 p_clr_remitted_receipts IN VARCHAR2,
919 p_clr_disc_receipts IN VARCHAR2,
920 p_eliminate_bank_risk IN VARCHAR2,
921 p_worker_number IN NUMBER DEFAULT 0,
922 p_request_id IN NUMBER DEFAULT 0
923 ) IS
924 --
925 remitted_or_factored_or_risk integer := 0;
926 --
927 BEGIN
928 --
929 IF (p_clr_remitted_receipts = 'Y') THEN
930 remitted_or_factored_or_risk := 1;
931 clr_remit_disc_risk_rcpts_pa (
932 p_worker_number,
933 p_request_id,
934 remitted_or_factored_or_risk );
935 END IF;
936 --
937 IF (p_clr_disc_receipts = 'Y') THEN
938 remitted_or_factored_or_risk := 2;
939 clr_remit_disc_risk_rcpts_pa (
940 p_worker_number,
941 p_request_id,
942 remitted_or_factored_or_risk );
943 END IF;
944 --
945 IF (p_eliminate_bank_risk = 'Y') THEN
946 remitted_or_factored_or_risk := 3;
947 clr_remit_disc_risk_rcpts_pa (
948 p_worker_number,
949 p_request_id,
950 remitted_or_factored_or_risk );
951 END IF;
952 --
953 EXCEPTION
954 WHEN OTHERS THEN
955 arp_standard.debug('Exception: ar_automatic_clearing_pkg ');
956 RAISE;
957 END;
958
959 --
960 /*========================================================================+
961 | FUNCTION ar_auto_clearing_in_parallel |
962 | |
963 | DESCRIPTION |
964 | |
965 | This drives the parallelization of Automatic Clearance process. |
966 | This spawns the AUTOCLEAR( ar_automatic_clearing_parallel) program. |
967 | PSEUDO CODE/LOGIC |
968 | |
969 | PARAMETERS |
970 | |
971 | |
972 | KNOWN ISSUES |
973 | |
974 | NOTES |
975 | |
976 | |
977 | MODIFICATION HISTORY |
978 | Date Author Description of Changes |
979 | 19-FEB-2008 aghoraka Created |
980 *=========================================================================*/
981 function ar_auto_clearing_in_parallel
982 ( p_clr_remitted_receipts IN VARCHAR2,
983 p_clr_disc_receipts IN VARCHAR2,
984 p_eliminate_bank_risk IN VARCHAR2,
985 p_clear_date IN DATE,
986 p_gl_date IN DATE,
987 p_customer_name_low IN VARCHAR2,
988 p_customer_name_high IN VARCHAR2,
989 p_customer_number_low IN VARCHAR2,
990 p_customer_number_high IN VARCHAR2,
991 p_receipt_number_low IN VARCHAR2,
992 p_receipt_number_high IN VARCHAR2,
993 p_remittance_bank_account_id IN NUMBER,
994 p_payment_method_id IN NUMBER,
995 p_exchange_rate_type IN VARCHAR2,
996 p_batch_id IN NUMBER,
997 p_undo_clearing IN VARCHAR2,
998 P_total_workers IN NUMBER)
999 RETURN BOOLEAN IS
1000 --
1001 TYPE req_status_typ IS RECORD (
1002 request_id NUMBER(15),
1003 dev_phase VARCHAR2(255),
1004 dev_status VARCHAR2(255),
1005 message VARCHAR2(2000),
1006 phase VARCHAR2(255),
1007 status VARCHAR2(255));
1008 l_org_id NUMBER;
1009 l_request_id NUMBER(15);
1010
1011 TYPE req_status_tab_typ IS TABLE OF req_status_typ INDEX BY BINARY_INTEGER;
1012
1013 l_req_status_tab req_status_tab_typ;
1014 l_complete BOOLEAN := FALSE;
1015 P_ERRBUF VARCHAR2(2000);
1016 P_RETCODE NUMBER(1) := 0;
1017 remitted_or_factored_or_risk integer := 0;
1018 l_master_request_id NUMBER(15);
1019 l_count NUMBER;
1020 error_in_child EXCEPTION;
1021
1022 PROCEDURE submit_subrequest (p_worker_number IN NUMBER,
1023 p_org_id IN NUMBER,
1024 p_master_request_id IN NUMBER) IS
1025 BEGIN
1026 fnd_file.put_line( FND_FILE.LOG, 'submit_subrequest()+' );
1027
1028 FND_REQUEST.SET_ORG_ID(p_org_id);
1029
1030 l_request_id := FND_REQUEST.submit_request( 'AR', 'AUTOCLEAR',
1031 '',
1032 SYSDATE,
1033 FALSE,
1034 p_clr_remitted_receipts,
1035 p_clr_disc_receipts,
1036 p_eliminate_bank_risk,
1037 p_worker_number,
1038 p_master_request_id );
1039
1040 IF (l_request_id = 0) THEN
1041 arp_util.debug('Can not start for worker_id: ' ||p_worker_number );
1042 P_ERRBUF := fnd_Message.get;
1043 P_RETCODE := 2;
1044 return;
1045 ELSE
1046 commit;
1047 arp_util.debug('Child request id: ' ||l_request_id || ' started for
1051 l_req_status_tab(p_worker_number).request_id := l_request_id;
1048 worker_id: ' ||p_worker_number );
1049 END IF;
1050
1052
1053 fnd_file.put_line( FND_FILE.LOG, 'submit_subrequest()-');
1054
1055 END submit_subrequest;
1056
1057 BEGIN
1058 fnd_file.put_line( FND_FILE.LOG, 'ar_automatic_clearing_in_parallel()+');
1059
1060 --fetch org id,need to set it for child requests
1061 SELECT org_id
1062 INTO l_org_id
1063 FROM ar_system_parameters;
1064
1065 l_master_request_id := arp_standard.profile.request_id;
1066
1067 IF (p_clr_remitted_receipts = 'Y') THEN
1068 remitted_or_factored_or_risk := 1;
1069 populate_interim_table (
1070 p_clear_date,
1071 p_gl_date,
1072 p_customer_name_low,
1073 p_customer_name_high,
1074 p_customer_number_low,
1075 p_customer_number_high,
1076 p_receipt_number_low,
1077 p_receipt_number_high,
1078 p_remittance_bank_account_id,
1079 p_payment_method_id,
1080 p_exchange_rate_type,
1081 p_batch_id,
1082 remitted_or_factored_or_risk,
1083 l_master_request_id,
1084 p_total_workers );
1085 END IF;
1086 --
1087 IF (p_clr_disc_receipts = 'Y') THEN
1088 remitted_or_factored_or_risk := 2;
1089 populate_interim_table (
1090 p_clear_date,
1091 p_gl_date,
1092 p_customer_name_low,
1093 p_customer_name_high,
1094 p_customer_number_low,
1095 p_customer_number_high,
1096 p_receipt_number_low,
1097 p_receipt_number_high,
1098 p_remittance_bank_account_id,
1099 p_payment_method_id,
1100 p_exchange_rate_type,
1101 p_batch_id,
1102 remitted_or_factored_or_risk,
1103 l_master_request_id,
1104 p_total_workers );
1105 END IF;
1106 --
1107 IF (p_eliminate_bank_risk = 'Y') THEN
1108 remitted_or_factored_or_risk := 3;
1109 populate_interim_table (
1110 p_clear_date,
1111 p_gl_date,
1112 p_customer_name_low,
1113 p_customer_name_high,
1114 p_customer_number_low,
1115 p_customer_number_high,
1116 p_receipt_number_low,
1117 p_receipt_number_high,
1118 p_remittance_bank_account_id,
1119 p_payment_method_id,
1120 p_exchange_rate_type,
1121 p_batch_id,
1122 remitted_or_factored_or_risk,
1123 l_master_request_id,
1124 p_total_workers );
1125 END IF;
1126 BEGIN
1127 select count(*)
1128 into l_count
1129 from ar_autoclear_interim
1130 where request_id = l_master_request_id;
1131 EXCEPTION
1132 WHEN OTHERS THEN
1133 fnd_file.put_line( FND_FILE.LOG, SQLERRM);
1134 l_count := 0;
1135 END;
1136 fnd_file.put_line( FND_FILE.LOG, 'No of Receipts Selected :'||l_count);
1137 IF l_count > 0 THEN
1138 --Invoke the child programs
1139 FOR l_worker_number IN 1..p_total_workers LOOP
1140 fnd_file.put_line(FND_FILE.LOG,'worker # : ' || l_worker_number );
1141 submit_subrequest (l_worker_number,l_org_id,l_master_request_id);
1142 END LOOP;
1143
1144 arp_standard.debug ( 'The Master program waits for child processes');
1145
1146 -- Wait for the completion of the submitted requests
1147 FOR i in 1..p_total_workers LOOP
1148
1149 l_complete := FND_CONCURRENT.WAIT_FOR_REQUEST(
1150 request_id => l_req_status_tab(i).request_id,
1151 interval => 30,
1152 max_wait =>144000,
1153 phase =>l_req_status_tab(i).phase,
1154 status =>l_req_status_tab(i).status,
1155 dev_phase =>l_req_status_tab(i).dev_phase,
1156 dev_status =>l_req_status_tab(i).dev_status,
1157 message =>l_req_status_tab(i).message);
1158
1159 IF l_req_status_tab(i).dev_phase <> 'COMPLETE' THEN
1160 P_RETCODE := 2;
1161 arp_util.debug('Worker # '|| i||' has a phase
1162 '||l_req_status_tab(i).dev_phase);
1163
1164 ELSIF l_req_status_tab(i).dev_phase = 'COMPLETE'
1165 AND l_req_status_tab(i).dev_status <> 'NORMAL' THEN
1166 P_RETCODE := 2;
1167 arp_util.debug('Worker # '|| i||' completed with status
1168 '||l_req_status_tab(i).dev_status);
1169 ELSE
1170 arp_util.debug('Worker # '|| i||' completed successfully');
1171 END IF;
1172
1173 END LOOP;
1174 DELETE FROM ar_autoclear_interim
1175 WHERE request_id = l_master_request_id;
1176 commit;
1177 END IF;
1178
1179 IF P_RETCODE = 2 THEN
1180 fnd_file.put_line( FND_FILE.LOG, 'Master Program completed in Error.');
1181 fnd_file.put_line( FND_FILE.LOG, 'Possibly Child process(es) might have errored out.');
1182 arp_util.debug('Exception: ar_automatic_clearing_pkg '|| SQLERRM);
1183 RAISE error_in_child;
1184 END IF;
1185 fnd_file.put_line( FND_FILE.LOG, 'ar_automatic_clearing_in_parallel()-');
1186 return TRUE;
1187 EXCEPTION
1188 WHEN OTHERS THEN
1189 arp_util.debug('Exception: ar_automatic_clearing_pkg '|| SQLERRM);
1190 RAISE ;
1191 END;
1192 --
1193 procedure populate_interim_table
1194 ( p_clear_date IN DATE,
1195 p_gl_date IN DATE,
1196 p_customer_name_low IN VARCHAR2,
1197 p_customer_name_high IN VARCHAR2,
1198 p_customer_number_low IN VARCHAR2,
1199 p_customer_number_high IN VARCHAR2,
1200 p_receipt_number_low IN VARCHAR2,
1201 p_receipt_number_high IN VARCHAR2,
1202 p_remittance_bank_account_id IN NUMBER,
1203 p_payment_method_id IN NUMBER,
1207 p_request_id IN NUMBER,
1204 p_exchange_rate_type IN VARCHAR2,
1205 p_batch_id IN NUMBER,
1206 remitted_or_factored_or_risk IN integer,
1208 p_total_workers IN NUMBER) IS
1209 --
1210 statement varchar2(5000) := NULL;
1211 c integer;
1212 ignore integer;
1213 l_request_id NUMBER(15);
1214 l_worker_number NUMBER(10);
1215 v_cr_id ar_cash_receipts.cash_receipt_id%TYPE;
1216 v_crh_id ar_cash_receipt_history.cash_receipt_history_id%TYPE;
1217 v_trx_date ar_cash_receipt_history.trx_date%TYPE;
1218 v_gl_date ar_cash_receipt_history.gl_date%TYPE;
1219 v_ex_date ar_cash_receipt_history.exchange_date%TYPE;
1220 v_ex_rate_type ar_cash_receipts.exchange_rate_type%TYPE;
1221 v_ex_rate ar_cash_receipts.exchange_rate%TYPE;
1222 v_currency ce_bank_accounts.currency_code%TYPE;
1223 v_amount ar_cash_receipt_history.amount%TYPE;
1224 v_fac_disc_amount ar_cash_receipt_history.factor_discount_amount%TYPE;
1225 v_mod_name varchar2(30);
1226 v_mod_vers varchar2(5);
1227 p_crh_id ar_cash_receipt_history.cash_receipt_history_id%TYPE;
1228 v_set_of_bks_id ar_system_parameters.set_of_books_id%TYPE;
1229 p_cr_rec ar_cash_receipts%ROWTYPE;
1230 p_crh_rec ar_cash_receipt_history%ROWTYPE;
1231 locked BOOLEAN;
1232
1233 -- 785113: Added to compute new acctd amts based on a different exchange rate.
1234 v_crh_amount ar_cash_receipt_history.amount%TYPE;
1235 v_crh_fac_disc_amt ar_cash_receipt_history.amount%TYPE;
1236 v_ex_rate_old ar_cash_receipts.exchange_rate%TYPE;
1237 v_cr_currency ce_bank_accounts.currency_code%TYPE;
1238
1239 --
1240 BEGIN
1241 --
1242 fnd_file.put_line( FND_FILE.LOG, 'populate_interim_table()+');
1243 select set_of_books_id INTO v_set_of_bks_id from ar_system_parameters;
1244 --
1245 IF ( remitted_or_factored_or_risk = 1) THEN
1246 statement := 'INSERT INTO ar_autoclear_interim
1247 ( cash_receipt_id,
1248 trx_date,
1249 gl_date,
1250 exchange_date,
1251 exchange_rate_type,
1252 exchange_rate,
1253 currency_code,
1254 amount,
1255 factor_discount_amount,
1256 module_name,
1257 module_version,
1258 cash_receipt_history_id,
1259 crh_amount,
1260 crh_factor_discount_amount,
1261 cr_currency_code,
1262 exchange_rate_old,
1263 current_worker,
1264 request_id,
1265 type
1266 ) ';
1267 main_select_remitted(
1268 statement,
1269 p_total_workers,
1270 p_request_id );
1271 END IF;
1272 --
1273 IF ( remitted_or_factored_or_risk = 2) THEN
1274 statement := 'INSERT INTO ar_autoclear_interim
1275 ( cash_receipt_id,
1276 trx_date,
1277 gl_date,
1278 exchange_date,
1279 exchange_rate_type,
1280 exchange_rate,
1281 currency_code,
1282 amount,
1283 factor_discount_amount,
1284 module_name,
1285 module_version,
1286 cash_receipt_history_id,
1287 crh_amount,
1288 crh_factor_discount_amount,
1289 cr_currency_code,
1290 exchange_rate_old,
1291 current_worker,
1292 request_id,
1293 type
1294 ) ';
1295 main_select_factored(
1296 statement,
1297 p_total_workers,
1298 p_request_id );
1299 END IF;
1300 --
1301 IF ( remitted_or_factored_or_risk = 3) THEN
1302 statement := 'INSERT INTO ar_autoclear_interim
1303 ( cash_receipt_id,
1304 trx_date,
1305 gl_date,
1306 module_name,
1307 module_version,
1308 cash_receipt_history_id,
1309 current_worker,
1310 request_id,
1311 type
1312 ) ';
1313 main_select_risk(
1314 statement,
1315 p_total_workers,
1316 p_request_id );
1317 END IF;
1318 --
1319 c := dbms_sql.open_cursor;
1320 --
1321 -- Build expanded select-statement depending on parameters
1322 --
1323 expand_stmt( p_customer_name_low,
1324 p_customer_name_high,
1325 p_customer_number_low,
1326 p_customer_number_high,
1327 p_receipt_number_low,
1328 p_receipt_number_high,
1329 p_remittance_bank_account_id,
1330 p_payment_method_id,
1331 p_batch_id,
1332 statement );
1333 --
1334 dbms_sql.parse(c, statement, dbms_sql.native);
1335 --
1336 IF PG_DEBUG in ('Y', 'C') THEN
1337 arp_standard.debug('Inside populate_interim_table()');
1338 arp_standard.debug( 'Statement ' || statement);
1339 END IF;
1340 -- Bind variables
1341 --
1342 dbms_sql.bind_variable(c, ':b_clear_date' ,p_clear_date);
1343 dbms_sql.bind_variable(c, ':b_gl_date' ,p_gl_date);
1344 --
1345 IF ( remitted_or_factored_or_risk <> 3) THEN
1346 dbms_sql.bind_variable(c, ':b_exchange_rate_type' ,p_exchange_rate_type);
1347 dbms_sql.bind_variable(c, ':b_set_of_bks_id' ,v_set_of_bks_id);
1348
1349 -- Bug 706935.
1350 -- Removed this line.
1351 -- dbms_sql.bind_variable(c, ':b_batch_id' ,p_batch_id);
1352
1356 p_customer_name_high,
1353 END IF;
1354 --
1355 ar_bind_variables( p_customer_name_low,
1357 p_customer_number_low,
1358 p_customer_number_high,
1359 p_receipt_number_low,
1360 p_receipt_number_high,
1361 p_remittance_bank_account_id,
1362 p_payment_method_id,
1363 p_batch_id,
1364 c );
1365 --
1366 ignore := dbms_sql.execute(c);
1367 IF PG_DEBUG IN ('Y','C') THEN
1368 fnd_file.put_line(fnd_file.LOG, 'No of Records Selected : '|| ignore);
1369 END IF;
1370 --
1371 commit; -- Commit the values inserted into the interim table.
1372 --
1373 dbms_sql.close_cursor(c);
1374 fnd_file.put_line( FND_FILE.LOG, 'populate_interim_table()-');
1375 --
1376 EXCEPTION
1377 WHEN OTHERS THEN
1378 arp_standard.debug('Exception: ar_automatic_clearing_pkg ');
1379 RAISE;
1380 --
1381 END;
1382 --
1383 procedure main_select_risk_parallel
1384 (
1385 statement IN OUT NOCOPY VARCHAR2 ) IS
1386 --
1387 BEGIN
1388 --
1389 /* 07-JUL-2000 J Rautiainen BR Implementation
1390 * Receipts created by the Bills Receivable transaction remittance process
1391 * having BR_REMIT receipt class cannot be risk eliminated */
1392 /*5444413 Index hint added*/
1393 fnd_file.put_line(fnd_file.log, 'main_select_risk_parallel()+');
1394 statement := 'select ' ||
1395 'cash_receipt_id, ' ||
1396 'trx_date, ' ||
1397 'gl_date , ' ||
1398 ' module_name, ' ||
1399 ' module_version, ' ||
1400 'cash_receipt_history_id ' ||
1401 'from ' ||
1402 'ar_autoclear_interim ' ||
1403 'where ' ||
1404 'request_id = :b_request_id ' ||
1405 'and current_worker = :b_worker_number '||
1406 'and type = ''RISK'' ';
1407 IF PG_DEBUG in ('Y', 'C') THEN
1408 arp_standard.debug( 'Statement ' || statement);
1409 END IF;
1410 fnd_file.put_line(fnd_file.log, 'main_select_risk_parallel()-');
1411 --
1412 EXCEPTION
1413 WHEN OTHERS THEN
1414 arp_standard.debug('Exception: ar_automatic_clearing_pkg ');
1415 RAISE;
1416 --
1417 END;
1418 --
1419 procedure main_select_factored_parallel
1420 (
1421 statement IN OUT NOCOPY VARCHAR2 ) IS
1422 --
1423 BEGIN
1424 --
1425 /* Bug 2484984 Modified the call gl_currency_api.get_rate so that
1426 there is an NVL for the exchange_rate_type parameter rather
1427 than an NVL for the function call itself . */
1428
1429 /* Bug 3820774 Replaced the get_rate with get_rate_sql. */
1430 /*Bug5444413 Added hint */
1431 fnd_file.put_line(fnd_file.log, 'main_select_factored_parallel()+');
1432 statement := 'select ' ||
1436 'exchange_date, ' ||
1433 'cash_receipt_id, '||
1434 'trx_date, ' ||
1435 'gl_date, ' ||
1437 'exchange_rate_type, ' ||
1438 'exchange_rate, ' ||
1439 'currency_code, ' ||
1440 'amount, ' ||
1441 'factor_discount_amount, ' ||
1442 ' module_name, ' ||
1443 ' module_version, ' ||
1444 'cash_receipt_history_id, ' ||
1445 'crh_amount, ' ||
1446 'crh_factor_discount_amount, ' ||
1447 'cr_currency_code, ' ||
1448 'exchange_rate_old ' ||
1449 'from ' ||
1450 'ar_autoclear_interim ' ||
1451 'where ' ||
1452 'request_id = :b_request_id ' ||
1453 'and current_worker = :b_worker_number '||
1454 'and type = ''FACTOR'' ';
1455 IF PG_DEBUG in ('Y', 'C') THEN
1456 arp_standard.debug( 'Statement ' || statement);
1457 END IF;
1458 fnd_file.put_line(fnd_file.log, 'main_select_factored_parallel()-');
1459 EXCEPTION
1460 WHEN OTHERS THEN
1461 arp_standard.debug('Exception: ar_automatic_clearing_pkg ');
1462 RAISE;
1463 --
1464 END;
1465 --
1466 procedure main_select_remitted_parallel
1467 (
1468 statement IN OUT NOCOPY VARCHAR2 ) IS
1469 --
1470 BEGIN
1471 --
1472 /* Bug 2484984 Modified the call gl_currency_api.get_rate so that
1473 there is an NVL for the exchange_rate_type parameter rather
1474 than an NVL for the function call itself . */
1475 /* Bug 3820774 Replaced the get_rate with get_rate_sql. */
1476 /*5444413 hint added here*/
1477 fnd_file.put_line(fnd_file.log, 'main_select_remitted_parallel()+');
1478 statement := 'select ' ||
1479 'cash_receipt_id, '||
1480 'trx_date, ' ||
1481 'gl_date, ' ||
1482 'exchange_date, ' ||
1483 'exchange_rate_type, ' ||
1484 'exchange_rate, ' ||
1485 'currency_code, ' ||
1486 'amount, ' ||
1487 'factor_discount_amount, ' ||
1488 ' module_name, ' ||
1489 ' module_version, ' ||
1490 'cash_receipt_history_id, ' ||
1491 'crh_amount, ' ||
1492 'crh_factor_discount_amount, ' ||
1493 'cr_currency_code, ' ||
1494 'exchange_rate_old ' ||
1495 'from ' ||
1496 'ar_autoclear_interim ' ||
1497 'where ' ||
1498 'request_id = :b_request_id ' ||
1499 'and current_worker = :b_worker_number '||
1500 'and type = ''REMIT'' ';
1501 IF PG_DEBUG in ('Y', 'C') THEN
1502 arp_standard.debug( 'Statement ' || statement);
1503 END IF;
1504 fnd_file.put_line(fnd_file.log, 'main_select_remitted_parallel()-');
1505 --
1506 EXCEPTION
1507 WHEN OTHERS THEN
1508 arp_standard.debug('Exception: ar_automatic_clearing_pkg ');
1509 RAISE;
1510 --
1511 END;
1512 --
1513 procedure ar_bind_variables_parallel
1514 (
1515 p_worker_number IN NUMBER,
1516 p_request_id IN NUMBER,
1517 c IN integer ) IS
1518 l_worker_number NUMBER;
1519 --
1520 BEGIN
1521 --
1522 IF ( p_worker_number IS NOT NULL ) THEN
1523 dbms_sql.bind_variable(c, ':b_worker_number' ,p_worker_number);
1524 END IF;
1525 --
1526 IF ( p_request_id IS NOT NULL ) THEN
1527 dbms_sql.bind_variable(c, ':b_request_id' ,p_request_id);
1528 END IF;
1529 --
1530 EXCEPTION
1531 WHEN OTHERS THEN
1532 arp_standard.debug('Exception: ar_automatic_clearing_pkg ');
1533 RAISE;
1534 --
1535 END;
1536 --
1537 procedure clr_remit_disc_risk_rcpts_pa
1538 ( p_worker_number IN NUMBER,
1539 p_request_id IN NUMBER,
1540 remitted_or_factored_or_risk IN NUMBER) IS
1541 --
1542 statement varchar2(3000) := NULL;
1543 c integer;
1544 ignore integer;
1545 v_cr_id ar_cash_receipts.cash_receipt_id%TYPE;
1546 v_crh_id ar_cash_receipt_history.cash_receipt_history_id%TYPE;
1547 v_trx_date ar_cash_receipt_history.trx_date%TYPE;
1548 v_gl_date ar_cash_receipt_history.gl_date%TYPE;
1549 v_ex_date ar_cash_receipt_history.exchange_date%TYPE;
1550 v_ex_rate_type ar_cash_receipts.exchange_rate_type%TYPE;
1551 v_ex_rate ar_cash_receipts.exchange_rate%TYPE;
1552 v_currency ce_bank_accounts.currency_code%TYPE;
1553 v_amount ar_cash_receipt_history.amount%TYPE;
1554 v_fac_disc_amount ar_cash_receipt_history.factor_discount_amount%TYPE;
1555 v_mod_name varchar2(30);
1556 v_mod_vers varchar2(5);
1557 p_crh_id ar_cash_receipt_history.cash_receipt_history_id%TYPE;
1558 v_set_of_bks_id ar_system_parameters.set_of_books_id%TYPE;
1559 p_cr_rec ar_cash_receipts%ROWTYPE;
1560 p_crh_rec ar_cash_receipt_history%ROWTYPE;
1561 locked BOOLEAN;
1562
1563 -- 785113: Added to compute new acctd amts based on a different exchange rate.
1564 v_crh_amount ar_cash_receipt_history.amount%TYPE;
1565 v_crh_fac_disc_amt ar_cash_receipt_history.amount%TYPE;
1566 v_ex_rate_old ar_cash_receipts.exchange_rate%TYPE;
1567 v_cr_currency ce_bank_accounts.currency_code%TYPE;
1568
1569 --
1570 BEGIN
1571 --
1572 fnd_file.put_line(fnd_file.log, 'clr_remit_disc_risk_rcpts_pa()+');
1573 select set_of_books_id INTO v_set_of_bks_id from ar_system_parameters;
1574 --
1575 IF ( remitted_or_factored_or_risk = 1) THEN
1576 main_select_remitted_parallel(
1580 IF ( remitted_or_factored_or_risk = 2) THEN
1577 statement );
1578 END IF;
1579 --
1581 main_select_factored_parallel(
1582 statement );
1583 END IF;
1584 --
1585 IF ( remitted_or_factored_or_risk = 3) THEN
1586 main_select_risk_parallel(
1587 statement );
1588 END IF;
1589 --
1590 c := dbms_sql.open_cursor;
1591 --
1592 dbms_sql.parse(c, statement, dbms_sql.native);
1593 --
1594 -- Bind variables
1595 --
1596 fnd_file.put_line(fnd_file.log, 'Worker No ' ||p_worker_number);
1597 fnd_file.put_line(fnd_file.log, 'Request_id '||p_request_id);
1598 ar_bind_variables_parallel(
1599 p_worker_number,
1600 p_request_id,
1601 c );
1602 --
1603 IF ( remitted_or_factored_or_risk = 3) THEN
1604 dbms_sql.define_column(c, 1, v_cr_id);
1605 dbms_sql.define_column(c, 2, v_trx_date);
1606 dbms_sql.define_column(c, 3, v_gl_date);
1607 dbms_sql.define_column(c, 4, v_mod_name, 30);
1608 dbms_sql.define_column(c, 5, v_mod_vers, 5);
1609 dbms_sql.define_column(c, 6, v_crh_id);
1610 ELSE
1611 dbms_sql.define_column(c, 1, v_cr_id);
1612 dbms_sql.define_column(c, 2, v_trx_date);
1613 dbms_sql.define_column(c, 3, v_gl_date);
1614 dbms_sql.define_column(c, 4, v_ex_date);
1615 dbms_sql.define_column(c, 5, v_ex_rate_type, 30);
1616 dbms_sql.define_column(c, 6, v_ex_rate);
1617 dbms_sql.define_column(c, 7, v_currency, 15);
1618 dbms_sql.define_column(c, 8, v_amount);
1619 dbms_sql.define_column(c, 9, v_fac_disc_amount);
1620 dbms_sql.define_column(c, 10, v_mod_name, 30);
1621 dbms_sql.define_column(c, 11, v_mod_vers, 5);
1622 dbms_sql.define_column(c, 12, v_crh_id);
1623 dbms_sql.define_column(c, 13, v_crh_amount);
1624 dbms_sql.define_column(c, 14, v_crh_fac_disc_amt);
1625 dbms_sql.define_column(c, 15, v_cr_currency, 15);
1626 dbms_sql.define_column(c, 16, v_ex_rate_old);
1627 END IF;
1628 --
1629 ignore := dbms_sql.execute(c);
1630 --
1631 IF ( remitted_or_factored_or_risk = 3) THEN
1632 LOOP
1633 IF dbms_sql.fetch_rows(c) > 0 THEN
1634 dbms_sql.column_value(c, 1, v_cr_id);
1635 dbms_sql.column_value(c, 2, v_trx_date);
1636 dbms_sql.column_value(c, 3, v_gl_date);
1637 dbms_sql.column_value(c, 4, v_mod_name);
1638 dbms_sql.column_value(c, 5, v_mod_vers);
1639 dbms_sql.column_value(c, 6, v_crh_id);
1640 --
1641 -- Lock rows
1642 --
1643 BEGIN
1644 locked := TRUE;
1645 p_cr_rec.cash_receipt_id := v_cr_id;
1646 p_crh_rec.cash_receipt_history_id := v_crh_id;
1647 arp_cash_receipts_pkg.nowaitlock_fetch_p ( p_cr_rec );
1648 arp_cr_history_pkg.nowaitlock_fetch_p ( p_crh_rec );
1649 EXCEPTION WHEN OTHERS THEN
1650 locked := FALSE;
1651 END;
1652 --
1653 -- Call Risk Handler
1654 --
1655 IF ( locked AND p_crh_rec.current_record_flag = 'Y' ) THEN
1656 arp_cashbook.risk_eliminate ( v_cr_id,
1657 v_trx_date,
1658 v_gl_date,
1659 v_mod_name,
1660 v_mod_vers,
1661 p_crh_id );
1662 END IF;
1663 ELSE
1664 -- no more rows
1665 EXIT;
1666 END IF;
1667 --
1668 END LOOP;
1669 ELSE
1670 LOOP
1671 IF dbms_sql.fetch_rows(c) > 0 THEN
1672 dbms_sql.column_value(c, 1, v_cr_id);
1673 dbms_sql.column_value(c, 2, v_trx_date);
1674 dbms_sql.column_value(c, 3, v_gl_date);
1675 dbms_sql.column_value(c, 4, v_ex_date);
1676 dbms_sql.column_value(c, 5, v_ex_rate_type);
1677 dbms_sql.column_value(c, 6, v_ex_rate);
1678 dbms_sql.column_value(c, 7, v_currency);
1679 dbms_sql.column_value(c, 8, v_amount);
1680 dbms_sql.column_value(c, 9, v_fac_disc_amount);
1681 dbms_sql.column_value(c, 10, v_mod_name);
1682 dbms_sql.column_value(c, 11, v_mod_vers);
1683 dbms_sql.column_value(c, 12, v_crh_id);
1684 dbms_sql.column_value(c, 13, v_crh_amount);
1685 dbms_sql.column_value(c, 14, v_crh_fac_disc_amt);
1686 dbms_sql.column_value(c, 15, v_cr_currency);
1687 dbms_sql.column_value(c, 16, v_ex_rate_old);
1688 --
1689 -- Lock rows
1690 --
1691 BEGIN
1692 locked := TRUE;
1693 p_cr_rec.cash_receipt_id := v_cr_id;
1694 p_crh_rec.cash_receipt_history_id:= v_crh_id;
1695 arp_cash_receipts_pkg.nowaitlock_fetch_p ( p_cr_rec );
1696 arp_cr_history_pkg.nowaitlock_fetch_p ( p_crh_rec );
1697 EXCEPTION WHEN OTHERS THEN
1698 locked := FALSE;
1699 END;
1700 --
1701 -- Call Clear Handler
1702 --
1703 /* bug: 3820774 Added the condition for v_ex_rate.
1704 Deleted the condition of 'v_ex_rate IS NULL'. */
1705 IF ( locked AND p_crh_rec.current_record_flag = 'Y'
1706 AND v_ex_rate > 0 ) THEN
1707 IF (v_currency <> v_cr_currency) THEN
1708 -- 785113
1709 -- Bank currency not equal to receipt currency. Calculate
1710 -- new accounted amounts using the receipt amount and exch.rate.
1711 --
1712 v_amount := arp_util.functional_amount(
1713 v_crh_amount,
1714 ARP_GLOBAL.functional_currency,
1715 nvl(v_ex_rate,nvl(v_ex_rate_old,1)),
1716 NULL,NULL );
1717 v_fac_disc_amount := arp_util.functional_amount(
1718 nvl(v_crh_fac_disc_amt,0),
1719 ARP_GLOBAL.functional_currency,
1720 nvl(v_ex_rate,1),
1721 NULL,NULL );
1722 END IF;
1723 arp_standard.debug('Before calling arp_cashbook.clear');
1724 arp_standard.debug('remitted_or_factored_or_risk :' || to_char(remitted_or_factored_or_risk));
1728 arp_standard.debug('v_ex_rate :' || to_char(v_ex_rate));
1725 arp_standard.debug('v_cr_id :' || to_char(v_cr_id));
1726 arp_standard.debug('v_trx_date[clear_date] :' || to_char(v_trx_date));
1727 arp_standard.debug('v_ex_date :' || to_char(v_ex_date));
1729 arp_standard.debug('v_ex_rate_type :' || v_ex_rate_type);
1730 arp_standard.debug('v_currency :' || v_currency);
1731 arp_standard.debug('New v_amount :' || to_char(v_amount));
1732 arp_standard.debug('v_fac_disc_amount :' || to_char(v_fac_disc_amount));
1733 arp_standard.debug('v_ex_rate_old :' || to_char(v_ex_rate_old));
1734 arp_standard.debug('v_cr_currency :' || v_cr_currency);
1735
1736 arp_cashbook.clear( v_cr_id,
1737 v_trx_date,
1738 v_gl_date,
1739 NULL, -- value date parameter
1740 v_ex_date,
1741 v_ex_rate_type,
1742 v_ex_rate,
1743 v_currency,
1744 v_amount,
1745 v_fac_disc_amount,
1746 v_mod_name,
1747 v_mod_vers,
1748 p_crh_id );
1749
1750 /* bug: 3820774 Added the handling of exception rate. */
1751 ELSE
1752 IF v_ex_rate = -1
1753 THEN
1754 arp_standard.debug('ar_automatic_clearing_pkg: NO_RATE');
1755 arp_standard.debug('v_cr_id :' || to_char(v_cr_id));
1756 arp_standard.debug('v_ex_date :' || to_char(v_ex_date));
1757 arp_standard.debug('v_ex_rate_type :' || v_ex_rate_type);
1758 arp_standard.debug('v_ex_rate :' || to_char(v_ex_rate));
1759
1760 fnd_file.put_line(fnd_file.log,'ar_automatic_clearing_pkg: NO_RATE');
1761 fnd_file.put_line(fnd_file.log,'v_cr_id :' || to_char(v_cr_id));
1762 fnd_file.put_line(fnd_file.log,'v_ex_date :' || to_char(v_ex_date));
1763 fnd_file.put_line(fnd_file.log,'v_ex_rate_type :' || v_ex_rate_type);
1764 fnd_file.put_line(fnd_file.log,'v_ex_rate :' || to_char(v_ex_rate));
1765
1766 ELSIF v_ex_rate = -2
1767 THEN
1768 arp_standard.debug('ar_automatic_clearing_pkg: INVALID_CURRENCY');
1769 arp_standard.debug('v_cr_id :' || to_char(v_cr_id));
1770 arp_standard.debug('v_ex_date :' || to_char(v_ex_date));
1771 arp_standard.debug('v_ex_rate_type :' || v_ex_rate_type);
1772 arp_standard.debug('v_ex_rate :' || to_char(v_ex_rate));
1773
1774 fnd_file.put_line(fnd_file.log,'ar_automatic_clearing_pkg: INVALID_CURRENCY');
1775 fnd_file.put_line(fnd_file.log,'v_cr_id :' || to_char(v_cr_id));
1776 fnd_file.put_line(fnd_file.log,'v_ex_date :' || to_char(v_ex_date));
1777 fnd_file.put_line(fnd_file.log,'v_ex_rate_type :' || v_ex_rate_type);
1778 fnd_file.put_line(fnd_file.log,'v_ex_rate :' || to_char(v_ex_rate));
1779
1780 END IF;
1781 END IF;
1782 ELSE
1783 -- no more rows
1784 EXIT;
1785 END IF;
1786 END LOOP;
1787 END IF;
1788 --
1789 dbms_sql.close_cursor(c);
1790 fnd_file.put_line(fnd_file.log, 'clr_remit_disc_risk_rcpts_pa()-');
1791 --
1792 EXCEPTION
1793 WHEN OTHERS THEN
1794 arp_standard.debug('Exception: ar_automatic_clearing_pkg ');
1795 RAISE;
1796 --
1797 END;
1798 --
1799 END arp_automatic_clearing_pkg;