[Home] [Help]
PACKAGE BODY: APPS.ARP_AUTOMATIC_CLEARING_PKG
Source
1 PACKAGE BODY arp_automatic_clearing_pkg AS
2 /* $Header: ARRXACRB.pls 120.19.12020000.3 2013/01/30 18:50:17 ankuagar 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, ' ||
372 'crh.factor_discount_amount, ' ||
373 'cr.currency_code, ' ||
374 'crh.exchange_rate ' ;
375 IF p_total_workers <> 0 THEN
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
495 arp_standard.debug('Exception: ar_automatic_clearing_pkg ');
496 RAISE;
497 --
498 END;
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 --
639 IF ( remitted_or_factored_or_risk = 3) THEN
640 LOOP
641 IF dbms_sql.fetch_rows(c) > 0 THEN
642 dbms_sql.column_value(c, 1, v_cr_id);
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 /*bug 14553712, instead of invoking the refersh_at_risk procedure,
678 store the records in the plsql table and invoke it at at time.
679 invoking the refresh_at_risk here*/
680 ar_bus_event_sub_pvt.refresh_at_risk_value(ARP_CASHBOOK.g_customer_id_tab,
681 ARP_CASHBOOK.g_site_use_id_tab,
682 ARP_CASHBOOK.g_currency_tab,
683 ARP_CASHBOOK.g_org_id_tab);
684
685 ARP_CASHBOOK.g_customer_id_tab.delete;
686 ARP_CASHBOOK.g_site_use_id_tab.delete;
687 ARP_CASHBOOK.g_currency_tab.delete;
688 ARP_CASHBOOK.g_org_id_tab.delete;
689 ARP_CASHBOOK.g_unq_cust_site_cur_org_cmb.delete;
690
691 ELSE
692 LOOP
693 IF dbms_sql.fetch_rows(c) > 0 THEN
694 dbms_sql.column_value(c, 1, v_cr_id);
695 dbms_sql.column_value(c, 2, v_trx_date);
696 dbms_sql.column_value(c, 3, v_gl_date);
697 dbms_sql.column_value(c, 4, v_ex_date);
698 dbms_sql.column_value(c, 5, v_ex_rate_type);
699 dbms_sql.column_value(c, 6, v_ex_rate);
700 dbms_sql.column_value(c, 7, v_currency);
701 dbms_sql.column_value(c, 8, v_amount);
702 dbms_sql.column_value(c, 9, v_fac_disc_amount);
703 dbms_sql.column_value(c, 10, v_mod_name);
704 dbms_sql.column_value(c, 11, v_mod_vers);
705 dbms_sql.column_value(c, 12, v_crh_id);
706 dbms_sql.column_value(c, 13, v_crh_amount);
707 dbms_sql.column_value(c, 14, v_crh_fac_disc_amt);
708 dbms_sql.column_value(c, 15, v_cr_currency);
709 dbms_sql.column_value(c, 16, v_ex_rate_old);
710 --
711 -- Lock rows
712 --
713 BEGIN
714 locked := TRUE;
715 p_cr_rec.cash_receipt_id := v_cr_id;
716 p_crh_rec.cash_receipt_history_id := v_crh_id;
717 arp_cash_receipts_pkg.nowaitlock_fetch_p ( p_cr_rec );
718 arp_cr_history_pkg.nowaitlock_fetch_p ( p_crh_rec );
719 EXCEPTION WHEN OTHERS THEN
720 locked := FALSE;
721 END;
722 --
723 -- Call Clear Handler
724 --
725 /* bug: 3820774 Added the condition for v_ex_rate.
726 Deleted the condition of 'v_ex_rate IS NULL'. */
727
728 IF ( locked AND p_crh_rec.current_record_flag = 'Y'
729 AND nvl(v_ex_rate,1) > 0 ) THEN
730
731 IF (v_currency <> v_cr_currency) THEN
732 -- 785113
733 -- Bank currency not equal to receipt currency. Calculate
734 -- new accounted amounts using the receipt amount and exch.rate.
735 --
736 v_amount := arp_util.functional_amount(
737 v_crh_amount,
738 ARP_GLOBAL.functional_currency,
739 nvl(v_ex_rate,nvl(v_ex_rate_old,1)),
740 NULL,NULL );
741 v_fac_disc_amount := arp_util.functional_amount(
742 nvl(v_crh_fac_disc_amt,0),
743 ARP_GLOBAL.functional_currency,
744 nvl(v_ex_rate,1),
745 NULL,NULL );
746 END IF;
747
748 arp_standard.debug('Before calling arp_cashbook.clear');
749 arp_standard.debug('remitted_or_factored_or_risk :' || to_char(remitted_or_factored_or_risk));
750 arp_standard.debug('v_cr_id :' || to_char(v_cr_id));
751 arp_standard.debug('v_trx_date[clear_date] :' || to_char(v_trx_date));
752 arp_standard.debug('v_ex_date :' || to_char(v_ex_date));
753 arp_standard.debug('v_ex_rate :' || to_char(v_ex_rate));
754 arp_standard.debug('v_ex_rate_type :' || v_ex_rate_type);
755 arp_standard.debug('v_currency :' || v_currency);
756 arp_standard.debug('New v_amount :' || to_char(v_amount));
757 arp_standard.debug('v_fac_disc_amount :' || to_char(v_fac_disc_amount));
758 arp_standard.debug('v_ex_rate_old :' || to_char(v_ex_rate_old));
759 arp_standard.debug('v_cr_currency :' || v_cr_currency);
760
761 arp_cashbook.clear( v_cr_id,
762 v_trx_date,
763 v_gl_date,
764 NULL, -- value date parameter
765 v_ex_date,
766 v_ex_rate_type,
767 v_ex_rate,
768 v_currency,
769 v_amount,
770 v_fac_disc_amount,
771 v_mod_name,
772 v_mod_vers,
773 p_crh_id );
774 /* bug: 3820774 Added the handling of exception rate. */
775 ELSE
776 IF v_ex_rate = -1
777 THEN
778 arp_standard.debug('ar_automatic_clearing_pkg: NO_RATE');
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: NO_RATE');
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 ELSIF v_ex_rate = -2
791 THEN
792 arp_standard.debug('ar_automatic_clearing_pkg: INVALID_CURRENCY');
793 arp_standard.debug('v_cr_id :' || to_char(v_cr_id));
794 arp_standard.debug('v_ex_date :' || to_char(v_ex_date));
795 arp_standard.debug('v_ex_rate_type :' || v_ex_rate_type);
796 arp_standard.debug('v_ex_rate :' || to_char(v_ex_rate));
797
798 fnd_file.put_line(fnd_file.log,'ar_automatic_clearing_pkg: INVALID_CURRENCY');
799 fnd_file.put_line(fnd_file.log,'v_cr_id :' || to_char(v_cr_id));
800 fnd_file.put_line(fnd_file.log,'v_ex_date :' || to_char(v_ex_date));
801 fnd_file.put_line(fnd_file.log,'v_ex_rate_type :' || v_ex_rate_type);
802 fnd_file.put_line(fnd_file.log,'v_ex_rate :' || to_char(v_ex_rate));
803
804 END IF;
805 END IF;
806 ELSE
807 -- no more rows
808 EXIT;
809 END IF;
810 END LOOP;
811 /*bug 14553712, instead of invoking the refersh_at_risk procedure,
812 store the records in the plsql table and invoke it at at time.
813 invoking the refresh_at_risk here*/
814 ar_bus_event_sub_pvt.refresh_at_risk_value(ARP_CASHBOOK.g_customer_id_tab,
815 ARP_CASHBOOK.g_site_use_id_tab,
816 ARP_CASHBOOK.g_currency_tab,
817 ARP_CASHBOOK.g_org_id_tab);
818
819 ARP_CASHBOOK.g_customer_id_tab.delete;
820 ARP_CASHBOOK.g_site_use_id_tab.delete;
821 ARP_CASHBOOK.g_currency_tab.delete;
822 ARP_CASHBOOK.g_org_id_tab.delete;
823 ARP_CASHBOOK.g_unq_cust_site_cur_org_cmb.delete;
824
825 END IF;
826 --
827 dbms_sql.close_cursor(c);
828 --
829 EXCEPTION
830 WHEN OTHERS THEN
831 arp_standard.debug('Exception: ar_automatic_clearing_pkg ');
832 RAISE;
833 --
834 END;
835 --
836 function ar_automatic_clearing
837 (
838 p_clr_remitted_receipts IN VARCHAR2,
839 p_clr_disc_receipts IN VARCHAR2,
840 p_eliminate_bank_risk IN VARCHAR2,
841 p_clear_date IN DATE,
842 p_gl_date IN DATE,
843 p_customer_name_low IN VARCHAR2,
844 p_customer_name_high IN VARCHAR2,
845 p_customer_number_low IN VARCHAR2,
846 p_customer_number_high IN VARCHAR2,
847 p_receipt_number_low IN VARCHAR2,
848 p_receipt_number_high IN VARCHAR2,
849 p_remittance_bank_account_id IN NUMBER,
850 p_payment_method_id IN NUMBER,
851 p_exchange_rate_type IN VARCHAR2,
852 p_batch_id IN NUMBER,
853 p_undo_clearing IN VARCHAR2
854 ) RETURN BOOLEAN IS
855 --
856 remitted_or_factored_or_risk integer := 0;
857 --
858 BEGIN
859 --
860 IF (p_clr_remitted_receipts = 'Y') THEN
861 remitted_or_factored_or_risk := 1;
862 clr_remit_disc_risk_receipts (
863 p_clear_date,
864 p_gl_date,
865 p_customer_name_low,
866 p_customer_name_high,
867 p_customer_number_low,
868 p_customer_number_high,
869 p_receipt_number_low,
870 p_receipt_number_high,
871 p_remittance_bank_account_id,
872 p_payment_method_id,
873 p_exchange_rate_type,
874 p_batch_id,
875 remitted_or_factored_or_risk);
876 END IF;
877 --
878 IF (p_clr_disc_receipts = 'Y') THEN
879 remitted_or_factored_or_risk := 2;
880 clr_remit_disc_risk_receipts (
881 p_clear_date,
882 p_gl_date,
883 p_customer_name_low,
884 p_customer_name_high,
885 p_customer_number_low,
886 p_customer_number_high,
887 p_receipt_number_low,
888 p_receipt_number_high,
889 p_remittance_bank_account_id,
890 p_payment_method_id,
891 p_exchange_rate_type,
892 p_batch_id,
893 remitted_or_factored_or_risk);
894 END IF;
895 --
896 IF (p_eliminate_bank_risk = 'Y') THEN
897 remitted_or_factored_or_risk := 3;
898 clr_remit_disc_risk_receipts (
899 p_clear_date,
900 p_gl_date,
901 p_customer_name_low,
902 p_customer_name_high,
903 p_customer_number_low,
904 p_customer_number_high,
905 p_receipt_number_low,
906 p_receipt_number_high,
907 p_remittance_bank_account_id,
908 p_payment_method_id,
909 p_exchange_rate_type,
910 p_batch_id,
911 remitted_or_factored_or_risk );
912 END IF;
913 --
914 return(TRUE);
915 --
916 EXCEPTION
917 WHEN OTHERS THEN
918 arp_standard.debug('Exception: ar_automatic_clearing_pkg ');
919 RAISE;
920 END;
921 --
922 /*========================================================================+
923 | PROCEDURE ar_automatic_clearing_parallel |
924 | |
925 | DESCRIPTION |
926 | |
927 | This is created to parallelize the Automatic Clearance for Reecipts |
928 | This procedure is called from ar_auto_clearing_in_parallel() |
929 | PSEUDO CODE/LOGIC |
930 | |
931 | PARAMETERS |
932 | |
933 | |
934 | KNOWN ISSUES |
935 | |
936 | NOTES |
937 | |
938 | |
939 | MODIFICATION HISTORY |
940 | Date Author Description of Changes |
941 | 19-FEB-2008 aghoraka Created |
942 *=========================================================================*/
943 procedure ar_automatic_clearing_parallel
944 ( P_ERRBUF OUT NOCOPY VARCHAR2,
945 P_RETCODE OUT NOCOPY NUMBER,
946 p_clr_remitted_receipts IN VARCHAR2,
947 p_clr_disc_receipts IN VARCHAR2,
948 p_eliminate_bank_risk IN VARCHAR2,
949 p_worker_number IN NUMBER DEFAULT 0,
950 p_request_id IN NUMBER DEFAULT 0
951 ) IS
952 --
953 remitted_or_factored_or_risk integer := 0;
954 --
955 BEGIN
956 --
957 IF (p_clr_remitted_receipts = 'Y') THEN
958 remitted_or_factored_or_risk := 1;
959 clr_remit_disc_risk_rcpts_pa (
960 p_worker_number,
961 p_request_id,
962 remitted_or_factored_or_risk );
963 END IF;
964 --
965 IF (p_clr_disc_receipts = 'Y') THEN
966 remitted_or_factored_or_risk := 2;
967 clr_remit_disc_risk_rcpts_pa (
968 p_worker_number,
969 p_request_id,
970 remitted_or_factored_or_risk );
971 END IF;
972 --
973 IF (p_eliminate_bank_risk = 'Y') THEN
974 remitted_or_factored_or_risk := 3;
975 clr_remit_disc_risk_rcpts_pa (
976 p_worker_number,
977 p_request_id,
978 remitted_or_factored_or_risk );
979 END IF;
980 --
981 EXCEPTION
982 WHEN OTHERS THEN
983 arp_standard.debug('Exception: ar_automatic_clearing_pkg ');
984 RAISE;
985 END;
986
987 --
988 /*========================================================================+
989 | FUNCTION ar_auto_clearing_in_parallel |
990 | |
991 | DESCRIPTION |
992 | |
993 | This drives the parallelization of Automatic Clearance process. |
994 | This spawns the AUTOCLEAR( ar_automatic_clearing_parallel) program. |
995 | PSEUDO CODE/LOGIC |
996 | |
997 | PARAMETERS |
998 | |
999 | |
1000 | KNOWN ISSUES |
1001 | |
1002 | NOTES |
1003 | |
1004 | |
1005 | MODIFICATION HISTORY |
1006 | Date Author Description of Changes |
1007 | 19-FEB-2008 aghoraka Created |
1008 *=========================================================================*/
1009 function ar_auto_clearing_in_parallel
1010 ( p_clr_remitted_receipts IN VARCHAR2,
1011 p_clr_disc_receipts IN VARCHAR2,
1012 p_eliminate_bank_risk IN VARCHAR2,
1013 p_clear_date IN DATE,
1014 p_gl_date IN DATE,
1015 p_customer_name_low IN VARCHAR2,
1016 p_customer_name_high IN VARCHAR2,
1017 p_customer_number_low IN VARCHAR2,
1018 p_customer_number_high IN VARCHAR2,
1019 p_receipt_number_low IN VARCHAR2,
1020 p_receipt_number_high IN VARCHAR2,
1021 p_remittance_bank_account_id IN NUMBER,
1022 p_payment_method_id IN NUMBER,
1023 p_exchange_rate_type IN VARCHAR2,
1024 p_batch_id IN NUMBER,
1025 p_undo_clearing IN VARCHAR2,
1026 P_total_workers IN NUMBER)
1027 RETURN BOOLEAN IS
1028 --
1029 TYPE req_status_typ IS RECORD (
1030 request_id NUMBER(15),
1031 dev_phase VARCHAR2(255),
1032 dev_status VARCHAR2(255),
1033 message VARCHAR2(2000),
1034 phase VARCHAR2(255),
1035 status VARCHAR2(255));
1036 l_org_id NUMBER;
1037 l_request_id NUMBER(15);
1038
1039 TYPE req_status_tab_typ IS TABLE OF req_status_typ INDEX BY BINARY_INTEGER;
1040
1041 l_req_status_tab req_status_tab_typ;
1042 l_complete BOOLEAN := FALSE;
1043 P_ERRBUF VARCHAR2(2000);
1044 P_RETCODE NUMBER(1) := 0;
1045 remitted_or_factored_or_risk integer := 0;
1046 l_master_request_id NUMBER(15);
1047 l_count NUMBER;
1048 error_in_child EXCEPTION;
1049
1050 PROCEDURE submit_subrequest (p_worker_number IN NUMBER,
1051 p_org_id IN NUMBER,
1052 p_master_request_id IN NUMBER) IS
1053 BEGIN
1054 fnd_file.put_line( FND_FILE.LOG, 'submit_subrequest()+' );
1055
1056 FND_REQUEST.SET_ORG_ID(p_org_id);
1057
1058 l_request_id := FND_REQUEST.submit_request( 'AR', 'AUTOCLEAR',
1059 '',
1060 SYSDATE,
1061 FALSE,
1062 p_clr_remitted_receipts,
1063 p_clr_disc_receipts,
1064 p_eliminate_bank_risk,
1065 p_worker_number,
1066 p_master_request_id );
1067
1068 IF (l_request_id = 0) THEN
1069 arp_util.debug('Can not start for worker_id: ' ||p_worker_number );
1070 P_ERRBUF := fnd_Message.get;
1071 P_RETCODE := 2;
1072 return;
1073 ELSE
1074 commit;
1075 arp_util.debug('Child request id: ' ||l_request_id || ' started for
1076 worker_id: ' ||p_worker_number );
1077 END IF;
1078
1079 l_req_status_tab(p_worker_number).request_id := l_request_id;
1080
1081 fnd_file.put_line( FND_FILE.LOG, 'submit_subrequest()-');
1082
1083 END submit_subrequest;
1084
1085 BEGIN
1086 fnd_file.put_line( FND_FILE.LOG, 'ar_automatic_clearing_in_parallel()+');
1087
1088 --fetch org id,need to set it for child requests
1089 SELECT org_id
1090 INTO l_org_id
1091 FROM ar_system_parameters;
1092
1093 l_master_request_id := arp_standard.profile.request_id;
1094
1095 IF (p_clr_remitted_receipts = 'Y') THEN
1096 remitted_or_factored_or_risk := 1;
1097 populate_interim_table (
1098 p_clear_date,
1099 p_gl_date,
1100 p_customer_name_low,
1101 p_customer_name_high,
1102 p_customer_number_low,
1103 p_customer_number_high,
1104 p_receipt_number_low,
1105 p_receipt_number_high,
1106 p_remittance_bank_account_id,
1107 p_payment_method_id,
1108 p_exchange_rate_type,
1109 p_batch_id,
1110 remitted_or_factored_or_risk,
1111 l_master_request_id,
1112 p_total_workers );
1113 END IF;
1114 --
1115 IF (p_clr_disc_receipts = 'Y') THEN
1116 remitted_or_factored_or_risk := 2;
1117 populate_interim_table (
1118 p_clear_date,
1119 p_gl_date,
1120 p_customer_name_low,
1121 p_customer_name_high,
1122 p_customer_number_low,
1123 p_customer_number_high,
1124 p_receipt_number_low,
1125 p_receipt_number_high,
1126 p_remittance_bank_account_id,
1127 p_payment_method_id,
1128 p_exchange_rate_type,
1129 p_batch_id,
1130 remitted_or_factored_or_risk,
1131 l_master_request_id,
1132 p_total_workers );
1133 END IF;
1134 --
1135 IF (p_eliminate_bank_risk = 'Y') THEN
1136 remitted_or_factored_or_risk := 3;
1137 populate_interim_table (
1138 p_clear_date,
1139 p_gl_date,
1140 p_customer_name_low,
1141 p_customer_name_high,
1142 p_customer_number_low,
1143 p_customer_number_high,
1144 p_receipt_number_low,
1145 p_receipt_number_high,
1146 p_remittance_bank_account_id,
1147 p_payment_method_id,
1148 p_exchange_rate_type,
1149 p_batch_id,
1150 remitted_or_factored_or_risk,
1151 l_master_request_id,
1152 p_total_workers );
1153 END IF;
1154 BEGIN
1155 select count(*)
1156 into l_count
1157 from ar_autoclear_interim
1158 where request_id = l_master_request_id;
1159 EXCEPTION
1160 WHEN OTHERS THEN
1161 fnd_file.put_line( FND_FILE.LOG, SQLERRM);
1162 l_count := 0;
1163 END;
1164 fnd_file.put_line( FND_FILE.LOG, 'No of Receipts Selected :'||l_count);
1165 IF l_count > 0 THEN
1166 --Invoke the child programs
1167 FOR l_worker_number IN 1..p_total_workers LOOP
1168 fnd_file.put_line(FND_FILE.LOG,'worker # : ' || l_worker_number );
1169 submit_subrequest (l_worker_number,l_org_id,l_master_request_id);
1170 END LOOP;
1171
1172 arp_standard.debug ( 'The Master program waits for child processes');
1173
1174 -- Wait for the completion of the submitted requests
1175 FOR i in 1..p_total_workers LOOP
1176
1177 l_complete := FND_CONCURRENT.WAIT_FOR_REQUEST(
1178 request_id => l_req_status_tab(i).request_id,
1179 interval => 30,
1180 max_wait =>144000,
1181 phase =>l_req_status_tab(i).phase,
1182 status =>l_req_status_tab(i).status,
1183 dev_phase =>l_req_status_tab(i).dev_phase,
1184 dev_status =>l_req_status_tab(i).dev_status,
1185 message =>l_req_status_tab(i).message);
1186
1187 IF l_req_status_tab(i).dev_phase <> 'COMPLETE' THEN
1188 P_RETCODE := 2;
1189 arp_util.debug('Worker # '|| i||' has a phase
1190 '||l_req_status_tab(i).dev_phase);
1191
1192 ELSIF l_req_status_tab(i).dev_phase = 'COMPLETE'
1193 AND l_req_status_tab(i).dev_status <> 'NORMAL' THEN
1194 P_RETCODE := 2;
1195 arp_util.debug('Worker # '|| i||' completed with status
1196 '||l_req_status_tab(i).dev_status);
1197 ELSE
1198 arp_util.debug('Worker # '|| i||' completed successfully');
1199 END IF;
1200
1201 END LOOP;
1202 DELETE FROM ar_autoclear_interim
1203 WHERE request_id = l_master_request_id;
1204 commit;
1205 END IF;
1206
1207 IF P_RETCODE = 2 THEN
1208 fnd_file.put_line( FND_FILE.LOG, 'Master Program completed in Error.');
1209 fnd_file.put_line( FND_FILE.LOG, 'Possibly Child process(es) might have errored out.');
1210 arp_util.debug('Exception: ar_automatic_clearing_pkg '|| SQLERRM);
1211 RAISE error_in_child;
1212 END IF;
1213 fnd_file.put_line( FND_FILE.LOG, 'ar_automatic_clearing_in_parallel()-');
1214 return TRUE;
1215 EXCEPTION
1216 WHEN OTHERS THEN
1217 arp_util.debug('Exception: ar_automatic_clearing_pkg '|| SQLERRM);
1218 RAISE ;
1219 END;
1220 --
1221 procedure populate_interim_table
1222 ( p_clear_date IN DATE,
1223 p_gl_date IN DATE,
1224 p_customer_name_low IN VARCHAR2,
1225 p_customer_name_high IN VARCHAR2,
1226 p_customer_number_low IN VARCHAR2,
1227 p_customer_number_high IN VARCHAR2,
1228 p_receipt_number_low IN VARCHAR2,
1229 p_receipt_number_high IN VARCHAR2,
1230 p_remittance_bank_account_id IN NUMBER,
1231 p_payment_method_id IN NUMBER,
1232 p_exchange_rate_type IN VARCHAR2,
1233 p_batch_id IN NUMBER,
1234 remitted_or_factored_or_risk IN integer,
1235 p_request_id IN NUMBER,
1236 p_total_workers IN NUMBER) IS
1237 --
1238 statement varchar2(5000) := NULL;
1239 c integer;
1240 ignore integer;
1241 l_request_id NUMBER(15);
1242 l_worker_number NUMBER(10);
1243 v_cr_id ar_cash_receipts.cash_receipt_id%TYPE;
1244 v_crh_id ar_cash_receipt_history.cash_receipt_history_id%TYPE;
1245 v_trx_date ar_cash_receipt_history.trx_date%TYPE;
1246 v_gl_date ar_cash_receipt_history.gl_date%TYPE;
1247 v_ex_date ar_cash_receipt_history.exchange_date%TYPE;
1248 v_ex_rate_type ar_cash_receipts.exchange_rate_type%TYPE;
1249 v_ex_rate ar_cash_receipts.exchange_rate%TYPE;
1250 v_currency ce_bank_accounts.currency_code%TYPE;
1251 v_amount ar_cash_receipt_history.amount%TYPE;
1252 v_fac_disc_amount ar_cash_receipt_history.factor_discount_amount%TYPE;
1253 v_mod_name varchar2(30);
1254 v_mod_vers varchar2(5);
1255 p_crh_id ar_cash_receipt_history.cash_receipt_history_id%TYPE;
1256 v_set_of_bks_id ar_system_parameters.set_of_books_id%TYPE;
1257 p_cr_rec ar_cash_receipts%ROWTYPE;
1258 p_crh_rec ar_cash_receipt_history%ROWTYPE;
1259 locked BOOLEAN;
1260
1261 -- 785113: Added to compute new acctd amts based on a different exchange rate.
1262 v_crh_amount ar_cash_receipt_history.amount%TYPE;
1263 v_crh_fac_disc_amt ar_cash_receipt_history.amount%TYPE;
1264 v_ex_rate_old ar_cash_receipts.exchange_rate%TYPE;
1265 v_cr_currency ce_bank_accounts.currency_code%TYPE;
1266
1267 --
1268 BEGIN
1269 --
1270 fnd_file.put_line( FND_FILE.LOG, 'populate_interim_table()+');
1271 select set_of_books_id INTO v_set_of_bks_id from ar_system_parameters;
1272 --
1273 IF ( remitted_or_factored_or_risk = 1) 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_remitted(
1296 statement,
1297 p_total_workers,
1298 p_request_id );
1299 END IF;
1300 --
1301 IF ( remitted_or_factored_or_risk = 2) THEN
1302 statement := 'INSERT INTO ar_autoclear_interim
1303 ( cash_receipt_id,
1304 trx_date,
1305 gl_date,
1306 exchange_date,
1307 exchange_rate_type,
1308 exchange_rate,
1309 currency_code,
1310 amount,
1311 factor_discount_amount,
1312 module_name,
1313 module_version,
1314 cash_receipt_history_id,
1315 crh_amount,
1316 crh_factor_discount_amount,
1317 cr_currency_code,
1318 exchange_rate_old,
1319 current_worker,
1320 request_id,
1321 type
1322 ) ';
1323 main_select_factored(
1324 statement,
1325 p_total_workers,
1326 p_request_id );
1327 END IF;
1328 --
1329 IF ( remitted_or_factored_or_risk = 3) THEN
1330 statement := 'INSERT INTO ar_autoclear_interim
1331 ( cash_receipt_id,
1332 trx_date,
1333 gl_date,
1334 module_name,
1335 module_version,
1336 cash_receipt_history_id,
1337 current_worker,
1338 request_id,
1339 type
1340 ) ';
1341 main_select_risk(
1342 statement,
1343 p_total_workers,
1344 p_request_id );
1345 END IF;
1346 --
1347 c := dbms_sql.open_cursor;
1348 --
1349 -- Build expanded select-statement depending on parameters
1350 --
1351 expand_stmt( p_customer_name_low,
1352 p_customer_name_high,
1353 p_customer_number_low,
1354 p_customer_number_high,
1355 p_receipt_number_low,
1356 p_receipt_number_high,
1357 p_remittance_bank_account_id,
1358 p_payment_method_id,
1359 p_batch_id,
1360 statement );
1361 --
1362 dbms_sql.parse(c, statement, dbms_sql.native);
1363 --
1364 IF PG_DEBUG in ('Y', 'C') THEN
1365 arp_standard.debug('Inside populate_interim_table()');
1366 arp_standard.debug( 'Statement ' || statement);
1367 END IF;
1368 -- Bind variables
1369 --
1370 dbms_sql.bind_variable(c, ':b_clear_date' ,p_clear_date);
1371 dbms_sql.bind_variable(c, ':b_gl_date' ,p_gl_date);
1372 --
1373 IF ( remitted_or_factored_or_risk <> 3) THEN
1374 dbms_sql.bind_variable(c, ':b_exchange_rate_type' ,p_exchange_rate_type);
1375 dbms_sql.bind_variable(c, ':b_set_of_bks_id' ,v_set_of_bks_id);
1376
1377 -- Bug 706935.
1378 -- Removed this line.
1379 -- dbms_sql.bind_variable(c, ':b_batch_id' ,p_batch_id);
1380
1381 END IF;
1382 --
1383 ar_bind_variables( p_customer_name_low,
1384 p_customer_name_high,
1385 p_customer_number_low,
1386 p_customer_number_high,
1387 p_receipt_number_low,
1388 p_receipt_number_high,
1389 p_remittance_bank_account_id,
1390 p_payment_method_id,
1391 p_batch_id,
1392 c );
1393 --
1394 ignore := dbms_sql.execute(c);
1395 IF PG_DEBUG IN ('Y','C') THEN
1396 fnd_file.put_line(fnd_file.LOG, 'No of Records Selected : '|| ignore);
1397 END IF;
1398 --
1399 commit; -- Commit the values inserted into the interim table.
1400 --
1401 dbms_sql.close_cursor(c);
1402 fnd_file.put_line( FND_FILE.LOG, 'populate_interim_table()-');
1403 --
1404 EXCEPTION
1405 WHEN OTHERS THEN
1406 arp_standard.debug('Exception: ar_automatic_clearing_pkg ');
1407 RAISE;
1408 --
1409 END;
1410 --
1411 procedure main_select_risk_parallel
1412 (
1413 statement IN OUT NOCOPY VARCHAR2 ) IS
1414 --
1415 BEGIN
1416 --
1417 /* 07-JUL-2000 J Rautiainen BR Implementation
1418 * Receipts created by the Bills Receivable transaction remittance process
1419 * having BR_REMIT receipt class cannot be risk eliminated */
1420 /*5444413 Index hint added*/
1421 fnd_file.put_line(fnd_file.log, 'main_select_risk_parallel()+');
1422 statement := 'select ' ||
1423 'cash_receipt_id, ' ||
1424 'trx_date, ' ||
1425 'gl_date , ' ||
1426 ' module_name, ' ||
1427 ' module_version, ' ||
1428 'cash_receipt_history_id ' ||
1429 'from ' ||
1430 'ar_autoclear_interim ' ||
1431 'where ' ||
1432 'request_id = :b_request_id ' ||
1433 'and current_worker = :b_worker_number '||
1434 'and type = ''RISK'' ';
1435 IF PG_DEBUG in ('Y', 'C') THEN
1436 arp_standard.debug( 'Statement ' || statement);
1437 END IF;
1438 fnd_file.put_line(fnd_file.log, 'main_select_risk_parallel()-');
1439 --
1440 EXCEPTION
1441 WHEN OTHERS THEN
1442 arp_standard.debug('Exception: ar_automatic_clearing_pkg ');
1443 RAISE;
1444 --
1445 END;
1446 --
1447 procedure main_select_factored_parallel
1448 (
1449 statement IN OUT NOCOPY VARCHAR2 ) IS
1450 --
1451 BEGIN
1452 --
1453 /* Bug 2484984 Modified the call gl_currency_api.get_rate so that
1454 there is an NVL for the exchange_rate_type parameter rather
1455 than an NVL for the function call itself . */
1456
1457 /* Bug 3820774 Replaced the get_rate with get_rate_sql. */
1458 /*Bug5444413 Added hint */
1459 fnd_file.put_line(fnd_file.log, 'main_select_factored_parallel()+');
1460 statement := 'select ' ||
1461 'cash_receipt_id, '||
1462 'trx_date, ' ||
1463 'gl_date, ' ||
1464 'exchange_date, ' ||
1465 'exchange_rate_type, ' ||
1466 'exchange_rate, ' ||
1467 'currency_code, ' ||
1468 'amount, ' ||
1469 'factor_discount_amount, ' ||
1470 ' module_name, ' ||
1471 ' module_version, ' ||
1472 'cash_receipt_history_id, ' ||
1473 'crh_amount, ' ||
1474 'crh_factor_discount_amount, ' ||
1475 'cr_currency_code, ' ||
1476 'exchange_rate_old ' ||
1477 'from ' ||
1478 'ar_autoclear_interim ' ||
1479 'where ' ||
1480 'request_id = :b_request_id ' ||
1481 'and current_worker = :b_worker_number '||
1482 'and type = ''FACTOR'' ';
1483 IF PG_DEBUG in ('Y', 'C') THEN
1484 arp_standard.debug( 'Statement ' || statement);
1485 END IF;
1486 fnd_file.put_line(fnd_file.log, 'main_select_factored_parallel()-');
1487 EXCEPTION
1488 WHEN OTHERS THEN
1489 arp_standard.debug('Exception: ar_automatic_clearing_pkg ');
1490 RAISE;
1491 --
1492 END;
1493 --
1494 procedure main_select_remitted_parallel
1495 (
1496 statement IN OUT NOCOPY VARCHAR2 ) IS
1497 --
1498 BEGIN
1499 --
1500 /* Bug 2484984 Modified the call gl_currency_api.get_rate so that
1501 there is an NVL for the exchange_rate_type parameter rather
1502 than an NVL for the function call itself . */
1503 /* Bug 3820774 Replaced the get_rate with get_rate_sql. */
1504 /*5444413 hint added here*/
1505 fnd_file.put_line(fnd_file.log, 'main_select_remitted_parallel()+');
1506 statement := 'select ' ||
1507 'cash_receipt_id, '||
1508 'trx_date, ' ||
1509 'gl_date, ' ||
1510 'exchange_date, ' ||
1511 'exchange_rate_type, ' ||
1512 'exchange_rate, ' ||
1513 'currency_code, ' ||
1514 'amount, ' ||
1515 'factor_discount_amount, ' ||
1516 ' module_name, ' ||
1517 ' module_version, ' ||
1518 'cash_receipt_history_id, ' ||
1519 'crh_amount, ' ||
1520 'crh_factor_discount_amount, ' ||
1521 'cr_currency_code, ' ||
1522 'exchange_rate_old ' ||
1523 'from ' ||
1524 'ar_autoclear_interim ' ||
1525 'where ' ||
1526 'request_id = :b_request_id ' ||
1527 'and current_worker = :b_worker_number '||
1528 'and type = ''REMIT'' ';
1529 IF PG_DEBUG in ('Y', 'C') THEN
1530 arp_standard.debug( 'Statement ' || statement);
1531 END IF;
1532 fnd_file.put_line(fnd_file.log, 'main_select_remitted_parallel()-');
1533 --
1534 EXCEPTION
1535 WHEN OTHERS THEN
1536 arp_standard.debug('Exception: ar_automatic_clearing_pkg ');
1537 RAISE;
1538 --
1539 END;
1540 --
1541 procedure ar_bind_variables_parallel
1542 (
1543 p_worker_number IN NUMBER,
1544 p_request_id IN NUMBER,
1545 c IN integer ) IS
1546 l_worker_number NUMBER;
1547 --
1548 BEGIN
1549 --
1550 IF ( p_worker_number IS NOT NULL ) THEN
1551 dbms_sql.bind_variable(c, ':b_worker_number' ,p_worker_number);
1552 END IF;
1553 --
1554 IF ( p_request_id IS NOT NULL ) THEN
1555 dbms_sql.bind_variable(c, ':b_request_id' ,p_request_id);
1556 END IF;
1557 --
1558 EXCEPTION
1559 WHEN OTHERS THEN
1560 arp_standard.debug('Exception: ar_automatic_clearing_pkg ');
1561 RAISE;
1562 --
1563 END;
1564 --
1565 procedure clr_remit_disc_risk_rcpts_pa
1566 ( p_worker_number IN NUMBER,
1567 p_request_id IN NUMBER,
1568 remitted_or_factored_or_risk IN NUMBER) IS
1569 --
1570 statement varchar2(3000) := NULL;
1571 c integer;
1572 ignore integer;
1573 v_cr_id ar_cash_receipts.cash_receipt_id%TYPE;
1574 v_crh_id ar_cash_receipt_history.cash_receipt_history_id%TYPE;
1575 v_trx_date ar_cash_receipt_history.trx_date%TYPE;
1576 v_gl_date ar_cash_receipt_history.gl_date%TYPE;
1577 v_ex_date ar_cash_receipt_history.exchange_date%TYPE;
1578 v_ex_rate_type ar_cash_receipts.exchange_rate_type%TYPE;
1579 v_ex_rate ar_cash_receipts.exchange_rate%TYPE;
1580 v_currency ce_bank_accounts.currency_code%TYPE;
1581 v_amount ar_cash_receipt_history.amount%TYPE;
1582 v_fac_disc_amount ar_cash_receipt_history.factor_discount_amount%TYPE;
1583 v_mod_name varchar2(30);
1584 v_mod_vers varchar2(5);
1585 p_crh_id ar_cash_receipt_history.cash_receipt_history_id%TYPE;
1586 v_set_of_bks_id ar_system_parameters.set_of_books_id%TYPE;
1587 p_cr_rec ar_cash_receipts%ROWTYPE;
1588 p_crh_rec ar_cash_receipt_history%ROWTYPE;
1589 locked BOOLEAN;
1590
1591 -- 785113: Added to compute new acctd amts based on a different exchange rate.
1592 v_crh_amount ar_cash_receipt_history.amount%TYPE;
1593 v_crh_fac_disc_amt ar_cash_receipt_history.amount%TYPE;
1594 v_ex_rate_old ar_cash_receipts.exchange_rate%TYPE;
1595 v_cr_currency ce_bank_accounts.currency_code%TYPE;
1596
1597 --
1598 BEGIN
1599 --
1600 fnd_file.put_line(fnd_file.log, 'clr_remit_disc_risk_rcpts_pa()+');
1601 select set_of_books_id INTO v_set_of_bks_id from ar_system_parameters;
1602 --
1603 IF ( remitted_or_factored_or_risk = 1) THEN
1604 main_select_remitted_parallel(
1605 statement );
1606 END IF;
1607 --
1608 IF ( remitted_or_factored_or_risk = 2) THEN
1609 main_select_factored_parallel(
1610 statement );
1611 END IF;
1612 --
1613 IF ( remitted_or_factored_or_risk = 3) THEN
1614 main_select_risk_parallel(
1615 statement );
1616 END IF;
1617 --
1618 c := dbms_sql.open_cursor;
1619 --
1620 dbms_sql.parse(c, statement, dbms_sql.native);
1621 --
1622 -- Bind variables
1623 --
1624 fnd_file.put_line(fnd_file.log, 'Worker No ' ||p_worker_number);
1625 fnd_file.put_line(fnd_file.log, 'Request_id '||p_request_id);
1626 ar_bind_variables_parallel(
1627 p_worker_number,
1628 p_request_id,
1629 c );
1630 --
1631 IF ( remitted_or_factored_or_risk = 3) THEN
1632 dbms_sql.define_column(c, 1, v_cr_id);
1633 dbms_sql.define_column(c, 2, v_trx_date);
1634 dbms_sql.define_column(c, 3, v_gl_date);
1635 dbms_sql.define_column(c, 4, v_mod_name, 30);
1636 dbms_sql.define_column(c, 5, v_mod_vers, 5);
1637 dbms_sql.define_column(c, 6, v_crh_id);
1638 ELSE
1639 dbms_sql.define_column(c, 1, v_cr_id);
1640 dbms_sql.define_column(c, 2, v_trx_date);
1641 dbms_sql.define_column(c, 3, v_gl_date);
1642 dbms_sql.define_column(c, 4, v_ex_date);
1643 dbms_sql.define_column(c, 5, v_ex_rate_type, 30);
1644 dbms_sql.define_column(c, 6, v_ex_rate);
1645 dbms_sql.define_column(c, 7, v_currency, 15);
1646 dbms_sql.define_column(c, 8, v_amount);
1647 dbms_sql.define_column(c, 9, v_fac_disc_amount);
1648 dbms_sql.define_column(c, 10, v_mod_name, 30);
1649 dbms_sql.define_column(c, 11, v_mod_vers, 5);
1650 dbms_sql.define_column(c, 12, v_crh_id);
1651 dbms_sql.define_column(c, 13, v_crh_amount);
1652 dbms_sql.define_column(c, 14, v_crh_fac_disc_amt);
1653 dbms_sql.define_column(c, 15, v_cr_currency, 15);
1654 dbms_sql.define_column(c, 16, v_ex_rate_old);
1655 END IF;
1656 --
1657 ignore := dbms_sql.execute(c);
1658 --
1659 IF ( remitted_or_factored_or_risk = 3) THEN
1660 LOOP
1661 IF dbms_sql.fetch_rows(c) > 0 THEN
1662 dbms_sql.column_value(c, 1, v_cr_id);
1663 dbms_sql.column_value(c, 2, v_trx_date);
1664 dbms_sql.column_value(c, 3, v_gl_date);
1665 dbms_sql.column_value(c, 4, v_mod_name);
1666 dbms_sql.column_value(c, 5, v_mod_vers);
1667 dbms_sql.column_value(c, 6, v_crh_id);
1668 --
1669 -- Lock rows
1670 --
1671 BEGIN
1672 locked := TRUE;
1673 p_cr_rec.cash_receipt_id := v_cr_id;
1674 p_crh_rec.cash_receipt_history_id := v_crh_id;
1675 arp_cash_receipts_pkg.nowaitlock_fetch_p ( p_cr_rec );
1676 arp_cr_history_pkg.nowaitlock_fetch_p ( p_crh_rec );
1677 EXCEPTION WHEN OTHERS THEN
1678 locked := FALSE;
1679 END;
1680 --
1681 -- Call Risk Handler
1682 --
1683 IF ( locked AND p_crh_rec.current_record_flag = 'Y' ) THEN
1684 arp_cashbook.risk_eliminate ( v_cr_id,
1685 v_trx_date,
1686 v_gl_date,
1687 v_mod_name,
1688 v_mod_vers,
1689 p_crh_id );
1690 END IF;
1691 ELSE
1692 -- no more rows
1693 EXIT;
1694 END IF;
1695 --
1696 END LOOP;
1697 /*bug 14553712, instead of invoking the refersh_at_risk procedure,
1698 store the records in the plsql table and invoke it at at time.
1699 invoking the refresh_at_risk here*/
1700 ar_bus_event_sub_pvt.refresh_at_risk_value(ARP_CASHBOOK.g_customer_id_tab,
1701 ARP_CASHBOOK.g_site_use_id_tab,
1702 ARP_CASHBOOK.g_currency_tab,
1703 ARP_CASHBOOK.g_org_id_tab);
1704
1705 ARP_CASHBOOK.g_customer_id_tab.delete;
1706 ARP_CASHBOOK.g_site_use_id_tab.delete;
1707 ARP_CASHBOOK.g_currency_tab.delete;
1708 ARP_CASHBOOK.g_org_id_tab.delete;
1709
1710 ARP_CASHBOOK.g_unq_cust_site_cur_org_cmb.delete;
1711 ELSE
1712 LOOP
1713 IF dbms_sql.fetch_rows(c) > 0 THEN
1714 dbms_sql.column_value(c, 1, v_cr_id);
1715 dbms_sql.column_value(c, 2, v_trx_date);
1716 dbms_sql.column_value(c, 3, v_gl_date);
1717 dbms_sql.column_value(c, 4, v_ex_date);
1718 dbms_sql.column_value(c, 5, v_ex_rate_type);
1719 dbms_sql.column_value(c, 6, v_ex_rate);
1720 dbms_sql.column_value(c, 7, v_currency);
1721 dbms_sql.column_value(c, 8, v_amount);
1722 dbms_sql.column_value(c, 9, v_fac_disc_amount);
1723 dbms_sql.column_value(c, 10, v_mod_name);
1724 dbms_sql.column_value(c, 11, v_mod_vers);
1725 dbms_sql.column_value(c, 12, v_crh_id);
1726 dbms_sql.column_value(c, 13, v_crh_amount);
1727 dbms_sql.column_value(c, 14, v_crh_fac_disc_amt);
1728 dbms_sql.column_value(c, 15, v_cr_currency);
1729 dbms_sql.column_value(c, 16, v_ex_rate_old);
1730 --
1731 -- Lock rows
1732 --
1733 BEGIN
1734 locked := TRUE;
1735 p_cr_rec.cash_receipt_id := v_cr_id;
1736 p_crh_rec.cash_receipt_history_id:= v_crh_id;
1737 arp_cash_receipts_pkg.nowaitlock_fetch_p ( p_cr_rec );
1738 arp_cr_history_pkg.nowaitlock_fetch_p ( p_crh_rec );
1739 EXCEPTION WHEN OTHERS THEN
1740 locked := FALSE;
1741 END;
1742 --
1743 -- Call Clear Handler
1744 --
1745 /* bug: 3820774 Added the condition for v_ex_rate.
1746 Deleted the condition of 'v_ex_rate IS NULL'. */
1747 IF ( locked AND p_crh_rec.current_record_flag = 'Y'
1748 AND v_ex_rate > 0 ) THEN
1749 IF (v_currency <> v_cr_currency) THEN
1750 -- 785113
1751 -- Bank currency not equal to receipt currency. Calculate
1752 -- new accounted amounts using the receipt amount and exch.rate.
1753 --
1754 v_amount := arp_util.functional_amount(
1755 v_crh_amount,
1756 ARP_GLOBAL.functional_currency,
1757 nvl(v_ex_rate,nvl(v_ex_rate_old,1)),
1758 NULL,NULL );
1759 v_fac_disc_amount := arp_util.functional_amount(
1760 nvl(v_crh_fac_disc_amt,0),
1761 ARP_GLOBAL.functional_currency,
1762 nvl(v_ex_rate,1),
1763 NULL,NULL );
1764 END IF;
1765 arp_standard.debug('Before calling arp_cashbook.clear');
1766 arp_standard.debug('remitted_or_factored_or_risk :' || to_char(remitted_or_factored_or_risk));
1767 arp_standard.debug('v_cr_id :' || to_char(v_cr_id));
1768 arp_standard.debug('v_trx_date[clear_date] :' || to_char(v_trx_date));
1769 arp_standard.debug('v_ex_date :' || to_char(v_ex_date));
1770 arp_standard.debug('v_ex_rate :' || to_char(v_ex_rate));
1771 arp_standard.debug('v_ex_rate_type :' || v_ex_rate_type);
1772 arp_standard.debug('v_currency :' || v_currency);
1773 arp_standard.debug('New v_amount :' || to_char(v_amount));
1774 arp_standard.debug('v_fac_disc_amount :' || to_char(v_fac_disc_amount));
1775 arp_standard.debug('v_ex_rate_old :' || to_char(v_ex_rate_old));
1776 arp_standard.debug('v_cr_currency :' || v_cr_currency);
1777
1778 arp_cashbook.clear( v_cr_id,
1779 v_trx_date,
1780 v_gl_date,
1781 NULL, -- value date parameter
1782 v_ex_date,
1783 v_ex_rate_type,
1784 v_ex_rate,
1785 v_currency,
1786 v_amount,
1787 v_fac_disc_amount,
1788 v_mod_name,
1789 v_mod_vers,
1790 p_crh_id );
1791
1792 /* bug: 3820774 Added the handling of exception rate. */
1793 ELSE
1794 IF v_ex_rate = -1
1795 THEN
1796 arp_standard.debug('ar_automatic_clearing_pkg: NO_RATE');
1797 arp_standard.debug('v_cr_id :' || to_char(v_cr_id));
1798 arp_standard.debug('v_ex_date :' || to_char(v_ex_date));
1799 arp_standard.debug('v_ex_rate_type :' || v_ex_rate_type);
1800 arp_standard.debug('v_ex_rate :' || to_char(v_ex_rate));
1801
1802 fnd_file.put_line(fnd_file.log,'ar_automatic_clearing_pkg: NO_RATE');
1803 fnd_file.put_line(fnd_file.log,'v_cr_id :' || to_char(v_cr_id));
1804 fnd_file.put_line(fnd_file.log,'v_ex_date :' || to_char(v_ex_date));
1805 fnd_file.put_line(fnd_file.log,'v_ex_rate_type :' || v_ex_rate_type);
1806 fnd_file.put_line(fnd_file.log,'v_ex_rate :' || to_char(v_ex_rate));
1807
1808 ELSIF v_ex_rate = -2
1809 THEN
1810 arp_standard.debug('ar_automatic_clearing_pkg: INVALID_CURRENCY');
1811 arp_standard.debug('v_cr_id :' || to_char(v_cr_id));
1812 arp_standard.debug('v_ex_date :' || to_char(v_ex_date));
1813 arp_standard.debug('v_ex_rate_type :' || v_ex_rate_type);
1814 arp_standard.debug('v_ex_rate :' || to_char(v_ex_rate));
1815
1816 fnd_file.put_line(fnd_file.log,'ar_automatic_clearing_pkg: INVALID_CURRENCY');
1817 fnd_file.put_line(fnd_file.log,'v_cr_id :' || to_char(v_cr_id));
1818 fnd_file.put_line(fnd_file.log,'v_ex_date :' || to_char(v_ex_date));
1819 fnd_file.put_line(fnd_file.log,'v_ex_rate_type :' || v_ex_rate_type);
1820 fnd_file.put_line(fnd_file.log,'v_ex_rate :' || to_char(v_ex_rate));
1821
1822 END IF;
1823 END IF;
1824 ELSE
1825 -- no more rows
1826 EXIT;
1827 END IF;
1828 END LOOP;
1829 /*bug 14553712, instead of invoking the refersh_at_risk procedure,
1830 store the records in the plsql table and invoke it at at time.
1831 invoking the refresh_at_risk here*/
1832 ar_bus_event_sub_pvt.refresh_at_risk_value(ARP_CASHBOOK.g_customer_id_tab,
1833 ARP_CASHBOOK.g_site_use_id_tab,
1834 ARP_CASHBOOK.g_currency_tab,
1835 ARP_CASHBOOK.g_org_id_tab);
1836
1837 ARP_CASHBOOK.g_customer_id_tab.delete;
1838 ARP_CASHBOOK.g_site_use_id_tab.delete;
1839 ARP_CASHBOOK.g_currency_tab.delete;
1840 ARP_CASHBOOK.g_org_id_tab.delete;
1841 ARP_CASHBOOK.g_unq_cust_site_cur_org_cmb.delete;
1842
1843 END IF;
1844 --
1845 dbms_sql.close_cursor(c);
1846 fnd_file.put_line(fnd_file.log, 'clr_remit_disc_risk_rcpts_pa()-');
1847 --
1848 EXCEPTION
1849 WHEN OTHERS THEN
1850 arp_standard.debug('Exception: ar_automatic_clearing_pkg ');
1851 RAISE;
1852 --
1853 END;
1854 --
1855 END arp_automatic_clearing_pkg;