1 PACKAGE BODY ar_revenue_management_pvt AS
2 /* $Header: ARXRVMGB.pls 120.134.12020000.10 2012/10/02 03:51:25 vpotti ship $ */
3
4
5 /*=======================================================================+
6 | Declare Package Data Types and Variables
7 +=======================================================================*/
8
9 TYPE RefCurType IS REF CURSOR;
10
11 /*=======================================================================+
12 | Package Global Constants
13 +=======================================================================*/
14
15 SUCCESS CONSTANT VARCHAR2(1) := '0';
16 WARNING CONSTANT VARCHAR2(1) := '1';
17 FAILURE CONSTANT VARCHAR2(1) := '2';
18
19 -- Following global variables are required for caching
20
21 g_credit_class_tbl varchar_table;
22 g_currency_code_f fnd_currencies.currency_code%TYPE;
23 g_precision_f fnd_currencies.precision%TYPE;
24 g_minimum_accountable_unit_f fnd_currencies.minimum_accountable_unit%TYPE;
25 g_source VARCHAR2(30);
26 g_om_context ra_interface_lines.interface_line_context%type;
27
28 pg_debug VARCHAR2(1) := NVL(fnd_profile.value('AFLOG_ENABLED'), 'N');
29
30
31 /*========================================================================
32 | Local Functions and Procedures
33 *=======================================================================*/
34
35 PROCEDURE debug (p_string VARCHAR2) IS
36
37 BEGIN
38
39 arp_debug.debug(p_string);
40
41 END debug;
42
43 /* 4521577 - This logic will default the contingency ID 5
44 on any line for an invoice that has a term with due days
45 greater than the value specified in system options
46 term_threshold. This default will not occur if the
47 deferral_exclusion_flag is set to 'Y'. It will also
48 not occur if there are interface validation errors for
49 the line.
50
51 There are separate INSERT statements for Invoice API,
52 Autoinvoice, and ARXTWMAI */
53
54 PROCEDURE insert_term_contingencies (
55 p_request_id NUMBER,
56 p_customer_trx_line_id NUMBER) IS
57
58 l_user_id NUMBER;
59 l_rows NUMBER;
60 BEGIN
61 debug('insert_term_contingencies()+');
62
63 l_user_id := fnd_global.user_id;
64
65 IF p_request_id IS NOT NULL
66 THEN
67 /* This is either invoice API or autoinvoice */
68 IF (g_source = 'AR_INVOICE_API')
69 THEN
70
71 INSERT INTO ar_line_conts_all
72 (
73 customer_trx_line_id,
74 contingency_id,
75 contingency_code,
76 expiration_date,
77 expiration_days,
78 expiration_event_date,
79 reason_removal_date,
80 completed_flag,
81 defaulted_in_ar_flag,
82 request_id,
83 created_by,
84 creation_date,
85 last_updated_by,
86 last_update_date,
87 last_update_login,
88 org_id
89 )
90 SELECT
91 max(ctl.customer_trx_line_id),
92 5,
93 '5',
94 NULL,
95 NULL,
96 NULL,
97 NULL,
98 'N',
99 'Y',
100 max(ctl.request_id),
101 l_user_id,
102 sysdate,
103 l_user_id,
104 sysdate,
105 l_user_id,
106 max(ctl.org_id)
107 FROM ra_customer_trx_lines_all ctl,
108 ra_customer_trx_all ct,
109 ra_terms_lines tl,
110 ra_cust_trx_types_all ctt,
111 pa_implementations pa
112 WHERE ctl.request_id = p_request_id
113 AND ctl.customer_trx_id = ct.customer_trx_id
114 AND ct.batch_source_id NOT IN (20, 21)
115 AND ct.org_id = pa.org_id (+)
116 AND ct.batch_source_id <> NVL(pa.invoice_batch_source_id, -99)
117 AND ct.batch_source_id <> NVL(pa.cc_ic_ar_batch_source_id, -99)
118 AND nvl(ct.invoicing_rule_id, 0) <> -3 /* 11711172 */
119 AND ct.cust_trx_type_id = ctt.cust_trx_type_id
120 AND ct.org_id = ctt.org_id
121 AND ctt.type = 'INV'
122 AND ctl.line_type = 'LINE'
123 AND ct.term_id = tl.term_id
124 AND NVL(ctl.deferral_exclusion_flag, 'N') = 'N'
125 AND NOT EXISTS
126 (SELECT 'errors'
127 FROM ar_trx_errors_gt teg,
128 ar_trx_lines_gt tlg
129 WHERE teg.trx_header_id = tlg.trx_header_id
130 AND teg.trx_line_id = tlg.trx_line_id
131 AND tlg.customer_trx_line_id = ctl.customer_trx_line_id)
132 AND NOT EXISTS
133 (SELECT 'prevent duplicate contingency'
134 FROM ar_line_conts_all alc
135 WHERE alc.customer_trx_line_id = ctl.customer_trx_line_id
136 AND alc.contingency_id = 5)
137 GROUP BY ctl.customer_trx_line_id, tl.term_id
138 HAVING max(due_days) > arp_standard.sysparm.payment_threshold;
139
140 ELSE /* Autoinvoice */
141 INSERT INTO ar_line_conts_all
142 (
143 customer_trx_line_id,
144 contingency_id,
145 contingency_code,
146 expiration_date,
147 expiration_days,
148 expiration_event_date,
149 reason_removal_date,
150 completed_flag,
151 defaulted_in_ar_flag,
152 request_id,
153 created_by,
154 creation_date,
155 last_updated_by,
156 last_update_date,
157 last_update_login,
158 org_id
159 )
160 SELECT
161 max(ctl.customer_trx_line_id),
162 5,
163 '5',
164 NULL,
165 NULL,
166 NULL,
167 NULL,
168 'N',
169 'Y',
170 max(ctl.request_id),
171 l_user_id,
172 sysdate,
173 l_user_id,
174 sysdate,
175 l_user_id,
176 max(ctl.org_id)
177 FROM ra_customer_trx_lines_all ctl,
178 ra_customer_trx_all ct,
179 ra_terms_lines tl,
180 ra_cust_trx_types_all ctt,
181 pa_implementations pa
182 WHERE ctl.request_id = p_request_id
183 AND ctl.customer_trx_id = ct.customer_trx_id
184 AND ct.batch_source_id NOT IN (20, 21)
185 AND ct.org_id = pa.org_id (+)
186 AND ct.batch_source_id <> NVL(pa.invoice_batch_source_id, -99)
187 AND ct.batch_source_id <> NVL(pa.cc_ic_ar_batch_source_id, -99)
188 AND nvl(ct.invoicing_rule_id, 0) <> -3 /* 11711172 */
189 AND ct.cust_trx_type_id = ctt.cust_trx_type_id
190 AND ct.org_id = ctt.org_id
191 AND ctt.type = 'INV'
192 AND ctl.line_type = 'LINE'
193 AND ct.term_id = tl.term_id
194 AND NVL(ctl.deferral_exclusion_flag, 'N') = 'N'
195 AND NOT EXISTS
196 (SELECT 'errors'
197 FROM ra_interface_errors_all ie
198 WHERE ie.interface_line_id = ctl.customer_trx_line_id)
199 AND NOT EXISTS
200 (SELECT 'prevent duplicate contingency'
201 FROM ar_line_conts_all alc
202 WHERE alc.customer_trx_line_id = ctl.customer_trx_line_id
203 AND alc.contingency_id = 5)
204 GROUP BY ctl.customer_trx_line_id, tl.term_id
205 HAVING max(due_days) > arp_standard.sysparm.payment_threshold;
206 END IF;
207 ELSE /* Manual transaction */
208 INSERT INTO ar_line_conts_all
209 (
210 customer_trx_line_id,
211 contingency_id,
212 contingency_code,
213 expiration_date,
214 expiration_days,
215 expiration_event_date,
216 reason_removal_date,
217 completed_flag,
218 defaulted_in_ar_flag,
219 request_id,
220 created_by,
221 creation_date,
222 last_updated_by,
223 last_update_date,
224 last_update_login,
225 org_id
226 )
227 SELECT
228 ctl.customer_trx_line_id,
229 5,
230 '5',
231 NULL,
232 NULL,
233 NULL,
234 NULL,
235 'N',
236 'Y',
237 NULL,
238 l_user_id,
239 sysdate,
240 l_user_id,
241 sysdate,
242 l_user_id,
243 ctl.org_id
244 FROM ra_customer_trx_lines_all ctl,
245 ra_customer_trx_all ct,
246 ra_terms_lines tl,
247 ra_cust_trx_types_all ctt,
248 pa_implementations pa
249 WHERE ctl.customer_trx_line_id = p_customer_trx_line_id
250 AND ctl.customer_trx_id = ct.customer_trx_id
251 AND ct.batch_source_id NOT IN (20, 21)
252 AND ct.org_id = pa.org_id (+)
253 AND ct.batch_source_id <> NVL(pa.invoice_batch_source_id, -99)
254 AND ct.batch_source_id <> NVL(pa.cc_ic_ar_batch_source_id, -99)
255 AND nvl(ct.invoicing_rule_id, 0) <> -3 /* 11711172 */
256 AND ct.cust_trx_type_id = ctt.cust_trx_type_id
257 AND ct.org_id = ctt.org_id
258 AND ctt.type = 'INV'
259 AND ctl.line_type = 'LINE'
260 AND ct.term_id = tl.term_id
261 AND NVL(ctl.deferral_exclusion_flag, 'N') = 'N'
262 AND NOT EXISTS
263 (SELECT 'prevent duplicate contingency'
264 FROM ar_line_conts_all alc
265 WHERE alc.customer_trx_line_id = ctl.customer_trx_line_id
266 AND alc.contingency_id = 5)
267 GROUP BY ctl.customer_trx_line_id, ctl.org_id,tl.term_id
268 HAVING max(tl.due_days) > arp_standard.sysparm.payment_threshold;
269 END IF;
270
271 l_rows := SQL%ROWCOUNT;
272
273 debug('term contingencies inserted: ' || l_rows);
274 debug('insert_term_contingencies()-');
275
276 END insert_term_contingencies;
277
278 /* 4521577 - This logic will default the contingency ID 3
279 on any line for an invoice that has a customer with
280 a questionable credit classification.
281
282 This default will not occur if the
283 deferral_exclusion_flag is set to 'Y'. It will also
284 not occur if there are interface validation errors for
285 the line.
286
287 There are separate INSERT statements for Invoice API,
288 Autoinvoice, and ARXTWMAI */
289
290 PROCEDURE insert_credit_contingencies (
291 p_request_id NUMBER,
292 p_customer_trx_line_id NUMBER) IS
293
294 l_user_id NUMBER;
295 l_rows NUMBER;
296 BEGIN
297 debug('insert_credit_contingencies()+');
298
299 l_user_id := fnd_global.user_id;
300
301 IF p_request_id IS NOT NULL
302 THEN
303 /* This is either invoice API or autoinvoice */
304 IF (g_source = 'AR_INVOICE_API')
305 THEN
306
307 INSERT INTO ar_line_conts_all
308 (
309 customer_trx_line_id,
310 contingency_id,
311 contingency_code,
312 expiration_date,
313 expiration_days,
314 expiration_event_date,
315 reason_removal_date,
316 completed_flag,
317 defaulted_in_ar_flag,
318 request_id,
319 created_by,
320 creation_date,
321 last_updated_by,
322 last_update_date,
323 last_update_login,
324 org_id
325 )
326 SELECT
327 ctl.customer_trx_line_id,
328 3,
329 '3',
330 NULL,
331 NULL,
332 NULL,
333 NULL,
334 'N',
335 'Y',
336 ctl.request_id,
337 l_user_id,
338 sysdate,
339 l_user_id,
340 sysdate,
341 l_user_id,
342 ctl.org_id
343 FROM ra_customer_trx_lines_all ctl,
344 ra_customer_trx_all ct,
345 ra_cust_trx_types_all ctt,
346 pa_implementations pa
347 WHERE ctl.request_id = p_request_id
348 AND ctl.customer_trx_id = ct.customer_trx_id
349 AND ct.batch_source_id NOT IN (20, 21)
350 AND ct.org_id = pa.org_id (+)
351 AND ct.batch_source_id <> NVL(pa.invoice_batch_source_id, -99)
352 AND ct.batch_source_id <> NVL(pa.cc_ic_ar_batch_source_id, -99)
353 AND nvl(ct.invoicing_rule_id, 0) <> -3 /* 11711172 */
354 AND ct.cust_trx_type_id = ctt.cust_trx_type_id
355 AND ct.org_id = ctt.org_id
356 AND ctt.type = 'INV'
357 AND ctl.line_type = 'LINE'
358 AND NVL(ctl.deferral_exclusion_flag, 'N') = 'N'
359 AND ar_revenue_management_pvt.creditworthy
360 (ct.bill_to_customer_id, ct.bill_to_site_use_id)= 0
361 AND NOT EXISTS
362 (SELECT 'errors'
363 FROM ar_trx_errors_gt teg,
364 ar_trx_lines_gt tlg
365 WHERE teg.trx_header_id = tlg.trx_header_id
366 AND teg.trx_line_id = tlg.trx_line_id
367 AND tlg.customer_trx_line_id = ctl.customer_trx_line_id)
368 AND NOT EXISTS
369 (SELECT 'prevent duplicate contingency'
370 FROM ar_line_conts_all alc
371 WHERE alc.customer_trx_line_id = ctl.customer_trx_line_id
372 AND alc.contingency_id = 3);
373
374 ELSE /* Autoinvoice */
375 INSERT INTO ar_line_conts_all
376 (
377 customer_trx_line_id,
378 contingency_id,
379 contingency_code,
380 expiration_date,
381 expiration_days,
382 expiration_event_date,
383 reason_removal_date,
384 completed_flag,
385 defaulted_in_ar_flag,
386 request_id,
387 created_by,
388 creation_date,
389 last_updated_by,
390 last_update_date,
391 last_update_login,
392 org_id
393 )
394 SELECT
395 ctl.customer_trx_line_id,
396 3,
397 '3',
398 NULL,
399 NULL,
400 NULL,
401 NULL,
402 'N',
403 'Y',
404 ctl.request_id,
405 l_user_id,
406 sysdate,
407 l_user_id,
408 sysdate,
409 l_user_id,
410 ctl.org_id
411 FROM ra_customer_trx_lines_all ctl,
412 ra_customer_trx_all ct,
413 ra_cust_trx_types_all ctt,
414 pa_implementations pa
415 WHERE ctl.request_id = p_request_id
416 AND ctl.customer_trx_id = ct.customer_trx_id
417 AND ct.batch_source_id NOT IN (20, 21)
418 AND ct.org_id = pa.org_id (+)
419 AND ct.batch_source_id <> NVL(pa.invoice_batch_source_id, -99)
420 AND ct.batch_source_id <> NVL(pa.cc_ic_ar_batch_source_id, -99)
421 AND nvl(ct.invoicing_rule_id, 0) <> -3 /* 11711172 */
422 AND ct.cust_trx_type_id = ctt.cust_trx_type_id
423 AND ct.org_id = ctt.org_id
424 AND ctt.type = 'INV'
425 AND ctl.line_type = 'LINE'
426 AND NVL(ctl.deferral_exclusion_flag, 'N') = 'N'
427 AND ar_revenue_management_pvt.creditworthy
428 (ct.bill_to_customer_id, ct.bill_to_site_use_id)= 0
429 AND NOT EXISTS
430 (SELECT 'errors'
431 FROM ra_interface_errors_all ie
432 WHERE ie.interface_line_id = ctl.customer_trx_line_id)
433 AND NOT EXISTS
434 (SELECT 'prevent duplicate contingency'
435 FROM ar_line_conts_all alc
436 WHERE alc.customer_trx_line_id = ctl.customer_trx_line_id
437 AND alc.contingency_id = 3);
438 END IF;
439 ELSE /* Manual transaction */
440 INSERT INTO ar_line_conts_all
441 (
442 customer_trx_line_id,
443 contingency_id,
444 contingency_code,
445 expiration_date,
446 expiration_days,
447 expiration_event_date,
448 reason_removal_date,
449 completed_flag,
450 defaulted_in_ar_flag,
451 request_id,
452 created_by,
453 creation_date,
454 last_updated_by,
455 last_update_date,
456 last_update_login,
457 org_id
458 )
459 SELECT
460 ctl.customer_trx_line_id,
461 3,
462 '3',
463 NULL,
464 NULL,
465 NULL,
466 NULL,
467 'N',
468 'Y',
469 NULL,
470 l_user_id,
471 sysdate,
472 l_user_id,
473 sysdate,
474 l_user_id,
475 ctl.org_id
476 FROM ra_customer_trx_lines_all ctl,
477 ra_customer_trx_all ct,
478 ra_cust_trx_types_all ctt,
479 pa_implementations pa
480 WHERE ctl.customer_trx_line_id = p_customer_trx_line_id
481 AND ctl.customer_trx_id = ct.customer_trx_id
482 AND ct.batch_source_id NOT IN (20, 21)
483 AND ct.org_id = pa.org_id (+)
484 AND ct.batch_source_id <> NVL(pa.invoice_batch_source_id, -99)
485 AND ct.batch_source_id <> NVL(pa.cc_ic_ar_batch_source_id, -99)
486 AND nvl(ct.invoicing_rule_id, 0) <> -3 /* 11711172 */
487 AND ct.cust_trx_type_id = ctt.cust_trx_type_id
488 AND ct.org_id = ctt.org_id
489 AND ctt.type = 'INV'
490 AND ctl.line_type = 'LINE'
491 AND NVL(ctl.deferral_exclusion_flag, 'N') = 'N'
492 AND ar_revenue_management_pvt.creditworthy
493 (ct.bill_to_customer_id, ct.bill_to_site_use_id)= 0
494 AND NOT EXISTS
495 (SELECT 'prevent duplicate contingency'
496 FROM ar_line_conts_all alc
497 WHERE alc.customer_trx_line_id = ctl.customer_trx_line_id
498 AND alc.contingency_id = 3);
499 END IF;
500
501 l_rows := SQL%ROWCOUNT;
502
503 debug('credit contingencies inserted: ' || l_rows);
504 debug('insert_credit_contingencies()-');
505
506 END insert_credit_contingencies;
507
508 PROCEDURE populate_acceptance_rows (
509 p_customer_trx_id NUMBER DEFAULT NULL,
510 p_customer_trx_line_id NUMBER DEFAULT NULL,
511 p_mode VARCHAR2 DEFAULT 'EXPIRE') IS
512
513 l_request_id NUMBER;
514
515 BEGIN
516
517 debug('populate_acceptance_rows()+');
518 debug(' p_customer_trx_id : ' || p_customer_trx_id);
519 debug(' p_customer_trx_line_id : ' || p_customer_trx_line_id);
520 debug(' p_mode : ' || p_mode);
521
522 l_request_id := nvl(p_customer_trx_line_id, nvl(p_customer_trx_id,
523 fnd_global.conc_request_id));
524
525 IF (p_mode = 'RECORD') THEN
526
527 INSERT INTO ar_reviewed_lines_gt
528 (
529 customer_trx_line_id,
530 customer_trx_id,
531 amount_due_original,
532 acctd_amount_due_original,
533 amount_recognized,
534 acctd_amount_recognized,
535 amount_pending,
536 acctd_amount_pending,
537 line_type,
538 so_line_id,
539 request_id
540 )
541 SELECT
542 dl.customer_trx_line_id line_id,
543 max(dl.customer_trx_id) trx_id,
544 max(dl.amount_due_original),
545 max(dl.acctd_amount_due_original),
546 max(dl.amount_recognized),
547 max(dl.acctd_amount_recognized),
548 max(dl.amount_pending),
549 max(dl.acctd_amount_pending),
550 'PARENT',
551 max(interface_line_attribute6),
552 l_request_id
553 FROM ar_deferred_lines dl,
554 ar_line_conts lc,
555 ar_deferral_reasons dr,
556 ra_customer_trx_lines ctl
557 WHERE dl.customer_trx_line_id = lc.customer_trx_line_id
558 AND lc.contingency_id = dr.contingency_id
559 AND ctl.customer_trx_line_id = lc.customer_trx_line_id
560 AND dr.revrec_event_code = 'CUSTOMER_ACCEPTANCE'
561 AND lc.completed_flag = 'N'
562 AND line_collectible_flag = 'N' -- not collectilbe
563 AND manual_override_flag = 'N' -- not manually overridden in
564 -- RAM wizards
565 AND dl.customer_trx_id = nvl(p_customer_trx_id,
566 dl.customer_trx_id)
567 AND dl.customer_trx_line_id = nvl(p_customer_trx_line_id,
568 dl.customer_trx_line_id)
569 GROUP BY dl.customer_trx_line_id;
570
571 debug('acceptance rows inserted: ' || SQL%ROWCOUNT);
572
573 ELSE
574
575 INSERT INTO ar_reviewed_lines_gt
576 (
577 customer_trx_line_id,
578 customer_trx_id,
579 amount_due_original,
580 acctd_amount_due_original,
581 amount_recognized,
582 acctd_amount_recognized,
583 amount_pending,
584 acctd_amount_pending,
585 line_type,
586 so_line_id,
587 request_id
588 )
589 SELECT
590 dl.customer_trx_line_id line_id,
591 max(dl.customer_trx_id) trx_id,
592 max(dl.amount_due_original),
593 max(dl.acctd_amount_due_original),
594 max(dl.amount_recognized),
595 max(dl.acctd_amount_recognized),
596 max(dl.amount_pending),
597 max(dl.acctd_amount_pending),
598 'PARENT',
599 max(interface_line_attribute6),
600 l_request_id
601 FROM ar_deferred_lines dl,
602 ar_line_conts lc,
603 ar_deferral_reasons dr,
604 ra_customer_trx_lines ctl
605 WHERE dl.customer_trx_line_id = lc.customer_trx_line_id
606 AND lc.contingency_id = dr.contingency_id
607 AND ctl.customer_trx_line_id = lc.customer_trx_line_id
608 AND dr.revrec_event_code = 'CUSTOMER_ACCEPTANCE'
609 AND lc.completed_flag = 'N'
610 AND line_collectible_flag = 'N' -- not collectilbe
611 AND manual_override_flag = 'N' -- not manually overridden in
612 -- RAM wizards
613 AND dl.customer_trx_id = nvl(p_customer_trx_id,
614 dl.customer_trx_id)
615 AND dl.customer_trx_line_id = nvl(p_customer_trx_line_id,
616 dl.customer_trx_line_id)
617 AND trunc(lc.expiration_date) <= trunc(sysdate)
618 GROUP BY dl.customer_trx_line_id;
619
620 debug('acceptance rows inserted: ' || SQL%ROWCOUNT);
621
622 END IF;
623
624 /* 9843541 - Exclude lines from acceptance if they are
625 from OM and line status is not POST-BILLING_ACCEPTANCE */
626 DELETE FROM AR_REVIEWED_LINES_GT
627 WHERE (customer_trx_id, customer_trx_line_id) IN
628 (select gt.customer_trx_id, gt.customer_trx_line_id
629 FROM AR_REVIEWED_LINES_GT gt,
630 RA_CUSTOMER_TRX_LINES ctl,
631 OE_ORDER_LINES oel
632 WHERE gt.customer_trx_id = ctl.customer_trx_id
633 AND gt.customer_trx_line_id = ctl.customer_trx_line_id
634 AND ctl.interface_line_context = g_om_context
635 AND to_number(ctl.interface_line_attribute6) =
636 oel.line_id
637 AND oel.flow_status_code <> 'POST-BILLING_ACCEPTANCE'
638 AND oel.CONTINGENCY_ID IS NOT NULL); --BUG 13482797
639
640 debug(' Removed ' || SQL%ROWCOUNT || ' row(s) - wrong OM line status');
641
642 debug('populate_acceptance_rows()-');
643
644 EXCEPTION
645 WHEN NO_DATA_FOUND THEN
646 debug('NO_DATA_FOUND: populate_acceptance_rows');
647 debug(sqlerrm);
648 RAISE;
649
650 WHEN OTHERS THEN
651 debug('OTHERS: populate_acceptance_rows');
652 debug(sqlerrm);
653 RAISE;
654
655 END populate_acceptance_rows;
656
657
658 PROCEDURE populate_no_contingency_rows (
659 p_customer_trx_id NUMBER DEFAULT NULL,
660 p_customer_trx_line_id NUMBER DEFAULT NULL) IS
661
662 l_request_id NUMBER;
663
664 BEGIN
665
666 debug('populate_no_contingency_rows()+');
667 debug(' p_customer_trx_id : ' || p_customer_trx_id);
668 debug(' p_customer_trx_line_id : ' || p_customer_trx_line_id);
669
670 l_request_id := nvl(p_customer_trx_line_id, nvl(p_customer_trx_id,
671 fnd_global.conc_request_id));
672
673 INSERT INTO ar_reviewed_lines_gt
674 (
675 customer_trx_line_id,
676 customer_trx_id,
677 amount_due_original,
678 acctd_amount_due_original,
679 amount_recognized,
680 acctd_amount_recognized,
681 amount_pending,
682 acctd_amount_pending,
683 line_type,
684 so_line_id,
685 request_id
686 )
687 SELECT
688 dl.customer_trx_line_id,
689 dl.customer_trx_id,
690 dl.amount_due_original,
691 dl.acctd_amount_due_original,
692 dl.amount_recognized,
693 dl.acctd_amount_recognized,
694 dl.amount_pending,
695 dl.acctd_amount_pending,
696 'PARENT',
697 interface_line_attribute6,
698 l_request_id
699 FROM ar_deferred_lines dl,
700 ra_customer_trx_lines ctl
701 WHERE dl.customer_trx_line_id = ctl.customer_trx_line_id
702 AND dl.customer_trx_id = nvl(p_customer_trx_id, dl.customer_trx_id)
703 AND dl.customer_trx_line_id = nvl(p_customer_trx_line_id,
704 dl.customer_trx_line_id)
705 AND NOT EXISTS
706 ( SELECT 'already inserted'
707 FROM ar_reviewed_lines_gt rl
708 WHERE rl.customer_trx_line_id = dl.customer_trx_line_id);
709
710 debug('no contingency rows inserted: ' || SQL%ROWCOUNT);
711
712 debug('populate_no_contingency_rows()-');
713
714 EXCEPTION
715 WHEN NO_DATA_FOUND THEN
716 debug('NO_DATA_FOUND: populate_no_contingency_rows');
717 debug(sqlerrm);
718 RAISE;
719
720 WHEN OTHERS THEN
721 debug('OTHERS: populate_no_contingency_rows');
722 debug(sqlerrm);
723 RAISE;
724
725 END populate_no_contingency_rows;
726
727
728 PROCEDURE populate_child_rows (
729 p_customer_trx_id NUMBER DEFAULT NULL,
730 p_customer_trx_line_id NUMBER DEFAULT NULL) IS
731
732 l_request_id NUMBER;
733 l_rows NUMBER;
734 BEGIN
735 IF pg_debug IN ('Y', 'C') THEN
736 debug('populate_child_rows()+');
737 debug(' p_customer_trx_id : ' || p_customer_trx_id);
738 debug(' p_customer_trx_line_id : ' || p_customer_trx_line_id);
739 END IF;
740
741 l_request_id := nvl(p_customer_trx_line_id, nvl(p_customer_trx_id,
742 fnd_global.conc_request_id));
743
744
745 /* 4996493 - modified child to parent for parameter trx and line_ids
746 so that acceptance picks up the parent lines and any child lines
747 associated with that parent */
748
749 /* 5043785 - The sql below contains an ORDERED hint because it must
750 join to RA_CUSTOMER_TRX_LINES before it attempts to join to the
751 child lines via so_line_id. In cases where the line is not from OM,
752 this join will fail with an invalid number (if it is attempted).
753
754 So the ORDERED hint along with the table order prevents the failure
755 and results in no lines being inserted when the interface_line_context
756 does not match the ONT_SOURCE_CODE profile */
757
758 /* 5229211 - Added code to populate so_line_id */
759
760 INSERT INTO ar_reviewed_lines_gt
761 (
762 customer_trx_line_id,
763 customer_trx_id,
764 amount_due_original,
765 acctd_amount_due_original,
766 amount_recognized,
767 acctd_amount_recognized,
768 amount_pending,
769 acctd_amount_pending,
770 line_type,
771 request_id,
772 so_line_id,
773 expiration_date
774 )
775 SELECT /*+ ORDERED */
776 child.customer_trx_line_id line_id,
777 max(child.customer_trx_id) trx_id,
778 max(child.amount_due_original),
779 max(child.acctd_amount_due_original),
780 max(child.amount_recognized),
781 max(child.acctd_amount_recognized),
782 max(child.amount_pending),
783 max(child.acctd_amount_pending),
784 'CHILD',
785 l_request_id,
786 child_line.interface_line_attribute6,
787 max(lc.expiration_date)
788 FROM ar_reviewed_lines_gt parent,
789 ra_customer_trx_lines parent_line,
790 ar_deferred_lines child,
791 ra_customer_trx_lines child_line,
792 ar_line_conts lc,
793 ar_deferral_reasons dr
794 WHERE parent.customer_trx_id = parent_line.customer_trx_id
795 AND parent.customer_trx_line_id = parent_line.customer_trx_line_id
796 AND parent_line.interface_line_context = g_om_context
797 AND to_char(child.parent_line_id) = parent.so_line_id
798 AND child.customer_trx_line_id = child_line.customer_trx_line_id
799 AND child_line.customer_trx_line_id = lc.customer_trx_line_id
800 AND lc.contingency_id = dr.contingency_id
801 AND dr.revrec_event_code = 'CUSTOMER_ACCEPTANCE'
802 AND lc.completed_flag = 'N'
803 AND line_collectible_flag = 'N' -- not collectilbe
804 AND manual_override_flag = 'N' -- not manually overridden in
805 -- RAM wizards
806 AND parent.customer_trx_id = nvl(p_customer_trx_id,
807 parent.customer_trx_id)
808 AND parent.customer_trx_line_id = nvl(p_customer_trx_line_id,
809 parent.customer_trx_line_id)
810 AND trunc(lc.expiration_date) <= trunc(sysdate)
811 GROUP BY child.customer_trx_line_id, child_line.interface_line_attribute6;
812
813 IF pg_debug IN ('Y', 'C') THEN
814 l_rows := SQL%ROWCOUNT;
815 debug(' inserted ' || l_rows || ' row(s)');
816 debug('populate_child_rows()-');
817 END IF;
818
819 EXCEPTION
820 WHEN NO_DATA_FOUND THEN
821 debug('NO_DATA_FOUND: populate_child_rows');
822 debug(sqlerrm);
823 RAISE;
824
825 WHEN OTHERS THEN
826 debug('OTHERS: populate_child_rows');
827 debug(sqlerrm);
828 RAISE;
829
830 END populate_child_rows;
831
832
833 PROCEDURE populate_other_rows (
834 p_customer_trx_id NUMBER DEFAULT NULL,
835 p_customer_trx_line_id NUMBER DEFAULT NULL,
836 p_mode VARCHAR2 DEFAULT 'NORMAL') IS
837
838 l_request_id NUMBER;
839 l_count NUMBER;
840 BEGIN
841
842 debug('populate_other_rows()+');
843 debug(' p_customer_trx_id : ' || p_customer_trx_id);
844 debug(' p_customer_trx_line_id : ' || p_customer_trx_line_id);
845
846 l_request_id := nvl(p_customer_trx_line_id, nvl(p_customer_trx_id,
847 fnd_global.conc_request_id));
848
849 IF p_mode = 'NORMAL' THEN
850
851 INSERT INTO ar_reviewed_lines_gt
852 (
853 customer_trx_line_id,
854 customer_trx_id,
855 amount_due_original,
856 acctd_amount_due_original,
857 amount_recognized,
858 acctd_amount_recognized,
859 amount_pending,
860 acctd_amount_pending,
861 line_type,
862 request_id,
863 expiration_date
864 )
865 SELECT
866 dl.customer_trx_line_id line_id,
867 max(customer_trx_id) trx_id,
868 max(amount_due_original),
869 max(acctd_amount_due_original),
870 max(amount_recognized),
871 max(acctd_amount_recognized),
872 max(amount_pending),
873 max(acctd_amount_pending),
874 'OTHERS',
875 l_request_id,
876 max(lc.expiration_date)
877 FROM ar_deferred_lines dl,
878 ar_line_conts lc,
879 ar_deferral_reasons dr
880 WHERE dl.customer_trx_line_id = lc.customer_trx_line_id
881 AND lc.contingency_id = dr.contingency_id
882 AND lc.completed_flag = 'N'
883 AND line_collectible_flag = 'N' -- not collectilbe
884 AND manual_override_flag = 'N' -- not manually overridden in
885 -- RAM wizards
886 AND dr.revrec_event_code <> 'CUSTOMER_ACCEPTANCE'
887 AND trunc(lc.expiration_date) <= trunc(sysdate)
888 AND dl.customer_trx_id = nvl(p_customer_trx_id, dl.customer_trx_id)
889 AND dl.customer_trx_line_id = nvl(p_customer_trx_line_id,
890 dl.customer_trx_line_id)
891 AND NOT EXISTS
892 ( SELECT 'already inserted'
893 FROM ar_reviewed_lines_gt rl
894 WHERE rl.customer_trx_line_id = dl.customer_trx_line_id)
895 GROUP BY dl.customer_trx_line_id;
896
897 l_count := SQL%ROWCOUNT;
898
899 ELSE
900
901 INSERT INTO ar_reviewed_lines_gt
902 (
903 customer_trx_line_id,
904 customer_trx_id,
905 amount_due_original,
906 acctd_amount_due_original,
907 amount_recognized,
908 acctd_amount_recognized,
909 amount_pending,
910 acctd_amount_pending,
911 line_type,
912 request_id
913 )
914 SELECT
915 dl.customer_trx_line_id line_id,
916 max(customer_trx_id) trx_id,
917 max(amount_due_original),
918 max(acctd_amount_due_original),
919 max(amount_recognized),
920 max(acctd_amount_recognized),
921 max(amount_pending),
922 max(acctd_amount_pending),
923 'UPDATE',
924 l_request_id
925 FROM ar_deferred_lines dl,
926 ar_line_conts lc,
927 ar_deferral_reasons dr
928 WHERE dl.customer_trx_line_id = lc.customer_trx_line_id
929 AND lc.contingency_id = dr.contingency_id
930 AND line_collectible_flag = 'N' -- not collectilbe
931 AND manual_override_flag = 'N' -- not manually overridden in
932 -- RAM wizards
933 AND dl.customer_trx_id = nvl(p_customer_trx_id, dl.customer_trx_id)
934 AND dl.customer_trx_line_id = nvl(p_customer_trx_line_id,
935 dl.customer_trx_line_id)
936 GROUP BY dl.customer_trx_line_id;
937
938 l_count := SQL%ROWCOUNT;
939
940 END IF;
941
942 debug(' Other row(s) inserted : ' || l_count);
943 debug('populate_other_rows()-');
944
945 EXCEPTION
946 WHEN NO_DATA_FOUND THEN
947 debug('NO_DATA_FOUND: populate_other_rows');
948 debug(sqlerrm);
949 RAISE;
950
951 WHEN OTHERS THEN
952 debug('OTHERS: populate_other_rows');
953 debug(sqlerrm);
954 RAISE;
955
956 END populate_other_rows;
957
958
959 PROCEDURE record_acceptance_with_om (
960 p_called_from IN VARCHAR2,
961 p_request_id IN NUMBER DEFAULT NULL,
962 p_customer_trx_id IN NUMBER DEFAULT NULL,
963 p_cust_trx_line_id IN NUMBER DEFAULT NULL,
964 p_date_accepted IN DATE DEFAULT NULL,
965 x_return_status OUT NOCOPY VARCHAR2,
966 x_msg_count OUT NOCOPY NUMBER,
967 x_msg_data OUT NOCOPY VARCHAR2) IS
968
969 CURSOR rev_lines (p_req_id NUMBER) IS
970 SELECT rl.customer_trx_line_id,
971 rl.customer_trx_id,
972 rl.so_line_id
973 FROM ar_reviewed_lines_gt rl,
974 ra_customer_trx_lines tl
975 WHERE rl.request_id = p_req_id
976 AND tl.customer_trx_line_id = rl.customer_trx_line_id
977 AND tl.customer_trx_id = rl.customer_trx_id
978 AND tl.interface_line_context = g_om_context;
979
980 l_last_fetch BOOLEAN;
981 l_customer_trx_id_tbl number_table;
982 l_customer_trx_line_id_tbl number_table;
983 l_so_line_id_tbl number_table;
984
985 l_request_rec oe_order_pub.request_rec_type;
986 l_action_request_tbl oe_order_pub.request_tbl_type;
987 l_init_request_tbl oe_order_pub.request_tbl_type;
988
989 om_error EXCEPTION;
990 l_request_id NUMBER;
991 BEGIN
992
993 debug('record_acceptance_with_om()+');
994
995 l_action_request_tbl := l_init_request_tbl;
996
997 /* 9476475 - Get the lowest common denominator, as line_id, trx_id, or
998 request_id. The request_id column in the GT is populated
999 the same way */
1000 l_request_id := NVL(p_cust_trx_line_id,
1001 NVL(p_customer_trx_id, p_request_id));
1002
1003 debug('using ' || l_request_id || 'as request_id in join to GT');
1004
1005 OPEN rev_lines(l_request_id);
1006
1007 LOOP
1008 FETCH rev_lines BULK COLLECT INTO
1009 l_customer_trx_line_id_tbl,
1010 l_customer_trx_id_tbl,
1011 l_so_line_id_tbl
1012 LIMIT c_max_bulk_fetch_size;
1013
1014 IF rev_lines%NOTFOUND THEN
1015 IF pg_debug IN ('Y', 'C') THEN
1016 debug('header_rows%NOTFOUND');
1017 END IF;
1018 l_last_fetch := TRUE;
1019 END IF;
1020
1021 IF l_customer_trx_line_id_tbl.COUNT = 0 AND l_last_fetch THEN
1022 IF pg_debug IN ('Y', 'C') THEN
1023 debug('No more rows');
1024 END IF;
1025 EXIT;
1026 END IF;
1027
1028 FOR i IN l_customer_trx_line_id_tbl.FIRST ..
1029 l_customer_trx_line_id_tbl.LAST LOOP
1030
1031 -- ACTION REQUEST RECORD for acceptance
1032
1033 l_request_rec.entity_code := oe_globals.g_entity_line;
1034 l_request_rec.entity_id := l_so_line_id_tbl(i);
1035
1036 -- action requested
1037 -- l_request_rec.request_type := oe_globals.g_accept_fulfillment;
1038 l_request_rec.request_type := 'ACCEPT_FULFILLMENT';
1039
1040 IF (p_called_from = 'SWEEPER') THEN
1041 -- implicit
1042 l_request_rec.param4 := 'Y';
1043 l_request_rec.date_param1 := sysdate;
1044 ELSE
1045 -- explicit
1046 l_request_rec.param4 := 'N';
1047 l_request_rec.date_param1 := p_date_accepted;
1048 END IF;
1049
1050
1051 -- inserting request record into action request table
1052 l_action_request_tbl(i) := l_request_rec;
1053
1054 -- dumping request record contents...
1055 debug('Row number: '||i);
1056 debug('entity_code: '||l_action_request_tbl(i).entity_code);
1057 debug('entity_id: '||l_action_request_tbl(i).entity_id);
1058 debug('request_type: '||l_action_request_tbl(i).request_type);
1059 debug('param4: '||l_action_request_tbl(i).param4);
1060 debug('date_param1: '||l_action_request_tbl(i).date_param1);
1061
1062 END LOOP;
1063
1064 debug('Before calling OE_AR_Acceptance_GRP.Process_Acceptance_in_OM....');
1065 OE_AR_Acceptance_GRP.Process_Acceptance_in_OM(
1066 p_action_request_tbl => l_action_request_tbl,
1067 x_return_status => x_return_status,
1068 x_msg_count => x_msg_count,
1069 x_msg_data => x_msg_data);
1070
1071 IF x_return_status <> FND_API.g_ret_sts_success THEN
1072 debug('ERROR....Process_Acceptance_in_OM FAILED..');
1073 debug('OM return status = '||x_return_status);
1074
1075 IF x_msg_count = 1 THEN
1076 debug(x_msg_data);
1077 ELSIF NVL(x_msg_count,0) = 0 THEN
1078 debug('No Messages');
1079 ELSE
1080 FOR i IN 1..x_msg_count LOOP
1081 debug(FND_MSG_PUB.get
1082 (p_msg_index => i,
1083 p_encoded => FND_API.G_FALSE));
1084 END LOOP;
1085 END IF;
1086
1087 RAISE om_error;
1088 END IF;
1089
1090 debug('After calling OE_AR_Acceptance_GRP.Process_Acceptance_in_OM');
1091
1092 END LOOP;
1093
1094 debug('record_acceptance_with_om()-');
1095
1096 EXCEPTION
1097 WHEN om_error THEN
1098 debug('ERROR calling OM: record_acceptance_with_om');
1099 debug(sqlerrm);
1100 RAISE;
1101 WHEN OTHERS THEN
1102 debug('OTHERS: record_acceptance_with_om');
1103 debug(sqlerrm);
1104 RAISE;
1105
1106 END record_acceptance_with_om;
1107
1108 /* Bug 4693399 - added customer_trx_line_id for manual invoices */
1109 /* Bug 5843254 - split manual and batch logic to separate IF conditions.
1110 also added support for REFUND_POLICY as well as REFUND to
1111 the logic for removing unnecessary contingencies. Note that
1112 REFUND is used on the seeded contingency and REFUND_POLICY
1113 is assigned to new ones. */
1114 PROCEDURE delete_unwanted_contingencies (p_request_id NUMBER
1115 ,p_customer_trx_line_id ra_customer_trx_lines.customer_trx_line_id%TYPE) IS
1116
1117 /* debug cursor */
1118 CURSOR alc (p_req_id NUMBER, p_line_id NUMBER) IS
1119 select lc.customer_trx_line_id, lc.contingency_id,
1120 dr.policy_attached
1121 from ar_line_conts lc,
1122 ar_deferral_reasons dr
1123 where lc.contingency_id = dr.contingency_id
1124 and ((p_req_id IS NULL and p_line_id IS NOT NULL AND
1125 lc.customer_trx_line_id = p_line_id) OR
1126 (p_req_id IS NOT NULL AND lc.request_id = p_req_id));
1127
1128 BEGIN
1129
1130 /* DEBUG CODE */
1131 IF PG_DEBUG IN ('Y','C')
1132 THEN
1133 debug('delete_unwanted_contingencies()+');
1134 debug(' p_request_id : ' || p_request_id);
1135 debug(' p_customer_trx_line_id : ' || p_customer_trx_line_id);
1136
1137 for c in alc(p_request_id, p_customer_trx_line_id) LOOP
1138 debug(c.customer_trx_line_id || ':' ||
1139 c.contingency_id || ':' ||
1140 c.policy_attached);
1141 end loop;
1142 END IF;
1143 /* END DEBUG CODE */
1144
1145 -- The existence of refund clause does not necessarily mean
1146 -- the revenue should be deferred. We should check the
1147 -- duration against the refund policy in the revenue policy
1148 -- tabs in the system options form.
1149
1150 IF p_request_id IS NOT NULL
1151 THEN
1152 /* batch process, based on request_id */
1153
1154 DELETE
1155 FROM ar_line_conts lrc
1156 WHERE customer_trx_line_id IN
1157 (SELECT customer_trx_line_id
1158 FROM ra_customer_trx_lines ctl
1159 WHERE ctl.request_id = p_request_id)
1160 AND trunc(expiration_date) - trunc(sysdate) <
1161 NVL(arp_standard.sysparm.standard_refund,0)
1162 AND EXISTS
1163 (SELECT 'its a refund contingency'
1164 FROM ar_deferral_reasons dr
1165 WHERE dr.contingency_id = lrc.contingency_id
1166 AND dr.policy_attached in ('REFUND','REFUND_POLICY'));
1167
1168 debug('refund contingencies deleted: ' || SQL%ROWCOUNT);
1169
1170 -- The existence of customer credit contingency does not necessarily
1171 -- mean the revenue should be deferred. We should check the
1172 -- duration against the refund policy in the revenue policy
1173 -- tabs in the system options form.
1174
1175 DELETE
1176 FROM ar_line_conts lc
1177 WHERE lc.customer_trx_line_id IN
1178 (SELECT customer_trx_line_id
1179 FROM ra_customer_trx_lines ctl,
1180 ra_customer_trx ct
1181 WHERE ctl.customer_trx_id = ct.customer_trx_id
1182 AND ctl.request_id = p_request_id
1183 AND ar_revenue_management_pvt.creditworthy
1184 (ct.bill_to_customer_id, ct.bill_to_site_use_id)= 1)
1185 AND EXISTS
1186 (SELECT 'its a CREDIT_CLASSIFICATION'
1187 FROM ar_deferral_reasons dr
1188 WHERE dr.contingency_id = lc.contingency_id
1189 AND dr.policy_attached = 'CREDIT_CLASSIFICATION');
1190
1191 IF PG_DEBUG IN ('Y','C')
1192 THEN
1193 debug('customer credit contingencies deleted: ' || SQL%ROWCOUNT);
1194 END IF;
1195
1196 -- The existence of payment term contingency does not necessarily mean
1197 -- the revenue should be deferred. We should check the
1198 -- duration against the refund policy in the revenue policy
1199 -- tabs in the system options form.
1200
1201 DELETE
1202 FROM ar_line_conts lc
1203 WHERE lc.customer_trx_line_id IN
1204 (SELECT customer_trx_line_id
1205 FROM ra_customer_trx_lines ctl,
1206 ra_customer_trx ct,
1207 ra_terms_lines tl
1208 WHERE ctl.customer_trx_id = ct.customer_trx_id
1209 AND ct.term_id = tl.term_id
1210 AND ctl.request_id = p_request_id
1211 GROUP BY ctl.customer_trx_line_id, tl.term_id
1212 HAVING NVL(max(due_days),0) <=
1213 NVL(arp_standard.sysparm.payment_threshold,0))
1214 AND EXISTS
1215 (SELECT 'its a PAYMENT_TERM'
1216 FROM ar_deferral_reasons dr
1217 WHERE dr.policy_attached = 'PAYMENT_TERM'
1218 AND dr.contingency_id = lc.contingency_id);
1219
1220 IF PG_DEBUG IN ('Y','C')
1221 THEN
1222 debug('payment term contingencies deleted: ' || SQL%ROWCOUNT);
1223 END IF;
1224
1225 -- Revenue management should ignore lines with deferred accounting rules
1226 -- attached to it. It is possible to add this logic in all the insert
1227 -- statements but that would mean adding an outer join, since not all
1228 -- lines have accounting rules. So, I decided against it and made it
1229 -- simpler by deleting the rows.
1230
1231 /* 5452544 - breaking sql into separate sections for interactive
1232 and batch processing */
1233 DELETE from ar_line_conts
1234 WHERE customer_trx_line_id IN
1235 (SELECT customer_trx_line_id
1236 FROM ra_customer_trx_lines ctl,
1237 ra_rules r
1238 WHERE ctl.request_id = p_request_id
1239 AND ctl.accounting_rule_id IS NOT NULL
1240 AND ctl.accounting_rule_id = r.rule_id
1241 AND r.deferred_revenue_flag = 'Y');
1242
1243 IF PG_DEBUG IN ('Y','C')
1244 THEN
1245 debug('contingencies for lines with deferred rule deleted: ' ||
1246 SQL%ROWCOUNT);
1247 END IF;
1248 ELSE
1249 /* manual process, based on customer_trx_line_id */
1250 DELETE
1251 FROM ar_line_conts lrc
1252 WHERE trunc(expiration_date) - trunc(sysdate) <
1253 NVL(arp_standard.sysparm.standard_refund,0)
1254 AND lrc.customer_trx_line_id = p_customer_trx_line_id
1255 AND EXISTS
1256 (SELECT 'a refund contingency'
1257 FROM ar_deferral_reasons dr
1258 WHERE dr.policy_attached in ('REFUND','REFUND_POLICY')
1259 AND dr.contingency_id = lrc.contingency_id);
1260
1261 debug('refund contingencies deleted: ' || SQL%ROWCOUNT);
1262
1263 -- The existence of customer credit contingency does not necessarily
1264 -- mean the revenue should be deferred. We should check the
1265 -- duration against the refund policy in the revenue policy
1266 -- tabs in the system options form.
1267
1268 DELETE
1269 FROM ar_line_conts lc
1270 WHERE lc.customer_trx_line_id = p_customer_trx_line_id
1271 AND EXISTS
1272 (SELECT 'its a credit_classification contingency'
1273 FROM ar_deferral_reasons dr
1274 WHERE dr.contingency_id = lc.contingency_id
1275 AND dr.policy_attached = 'CREDIT_CLASSIFICATION')
1276 AND EXISTS
1277 (SELECT 'customer is not credit worthy'
1278 FROM ra_customer_trx_lines ctl,
1279 ra_customer_trx ct
1280 WHERE ctl.customer_trx_id = ct.customer_trx_id
1281 AND ctl.customer_trx_line_id = p_customer_trx_line_id
1282 AND ar_revenue_management_pvt.creditworthy
1283 (ct.bill_to_customer_id, ct.bill_to_site_use_id)= 1);
1284
1285 IF PG_DEBUG IN ('Y','C')
1286 THEN
1287 debug('customer credit contingencies deleted: ' || SQL%ROWCOUNT);
1288 END IF;
1289
1290 -- The existence of payment term contingency does not necessarily mean
1291 -- the revenue should be deferred. We should check the
1292 -- duration against the refund policy in the revenue policy
1293 -- tabs in the system options form.
1294
1295 DELETE
1296 FROM ar_line_conts lc
1297 WHERE lc.customer_trx_line_id = p_customer_trx_line_id
1298 AND EXISTS
1299 (SELECT 'it is a term contingency'
1300 FROM ar_deferral_reasons dr
1301 WHERE dr.policy_attached = 'PAYMENT_TERM'
1302 AND dr.contingency_id = lc.contingency_id)
1303 AND EXISTS
1304 (SELECT 'term exceeds threshold'
1305 FROM ra_customer_trx_lines ctl,
1306 ra_customer_trx ct,
1307 ra_terms_lines tl
1308 WHERE ctl.customer_trx_id = ct.customer_trx_id
1309 AND ct.term_id = tl.term_id
1310 AND ctl.customer_trx_line_id = lc.customer_trx_line_id
1311 GROUP BY ctl.customer_trx_line_id, tl.term_id
1312 HAVING NVL(max(due_days),0) <=
1313 NVL(arp_standard.sysparm.payment_threshold,0));
1314
1315 IF PG_DEBUG IN ('Y','C')
1316 THEN
1317 debug('payment term contingencies deleted: ' || SQL%ROWCOUNT);
1318 END IF;
1319
1320 -- Revenue management should ignore lines with deferred accounting rules
1321 -- attached to it. It is possible to add this logic in all the insert
1322 -- statements but that would mean adding an outer join, since not all
1323 -- lines have accounting rules. So, I decided against it and made it
1324 -- simpler by deleting the rows.
1325
1326 /* 5452544 - breaking sql into separate sections for interactive
1327 and batch processing */
1328 DELETE FROM AR_LINE_CONTS A
1329 WHERE A.customer_trx_line_id = p_customer_trx_line_id
1330 AND EXISTS (SELECT 'DEFERRED RULE'
1331 FROM ra_customer_trx_lines ctl,
1332 ra_rules r
1333 WHERE ctl.customer_trx_line_id = A.customer_trx_line_id
1334 AND ctl.accounting_rule_id = r.rule_id
1335 AND r.deferred_revenue_flag = 'Y');
1336
1337 IF PG_DEBUG IN ('Y','C')
1338 THEN
1339 debug('contingencies for lines with deferred rule deleted: ' ||
1340 SQL%ROWCOUNT);
1341 END IF;
1342
1343 END IF;
1344
1345 /* For imported transactions, remove contingencies if
1346 the imported lines are rejected by validations */
1347 IF (g_source = 'AR_INVOICE_API') THEN
1348
1349 DELETE
1350 FROM ar_line_conts
1351 WHERE customer_trx_line_id IN
1352 (SELECT customer_trx_line_id
1353 FROM ar_trx_errors_gt teg,
1354 ar_trx_lines_gt tlg
1355 WHERE teg.trx_header_id = tlg.trx_header_id
1356 AND teg.trx_line_id = tlg.trx_line_id
1357 AND request_id = p_request_id);
1358
1359 ELSIF p_request_id IS NOT NULL THEN
1360
1361 DELETE
1362 FROM ar_line_conts
1363 WHERE customer_trx_line_id IN
1364 (SELECT ie.interface_line_id
1365 FROM ra_interface_errors ie
1366 WHERE request_id = p_request_id);
1367
1368 END IF;
1369
1370 IF PG_DEBUG IN ('Y','C')
1371 THEN
1372 debug('delete_unwanted_contingencies()-');
1373 END IF;
1374
1375 EXCEPTION
1376 WHEN NO_DATA_FOUND THEN
1377 debug('NO_DATA_FOUND: delete_unwanted_contingencies');
1378 debug(sqlerrm);
1379 RAISE;
1380
1381 WHEN OTHERS THEN
1382 debug('OTHERS: delete_unwanted_contingencies');
1383 debug(sqlerrm);
1384 RAISE;
1385
1386 END delete_unwanted_contingencies;
1387
1388 /* This function returns the customer_trx_line_id of the parent line
1389 after fetching the attribute values from OM */
1390 FUNCTION get_line_id(p_so_line_id IN NUMBER) RETURN NUMBER IS
1391 l_line_flex_rec ar_deferral_reasons_grp.line_flex_rec;
1392 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
1393 l_msg_count NUMBER;
1394 l_msg_data VARCHAR2(2000);
1395 l_customer_trx_line_id ra_customer_trx_lines_all.customer_trx_line_id%type;
1396 BEGIN
1397 /* Call OM to get the DFF attributes for the parent line */
1398 OE_AR_Acceptance_GRP.Get_interface_attributes
1399 (p_line_id => p_so_line_id,
1400 x_line_flex_rec => l_line_flex_rec,
1401 x_return_status => l_return_status,
1402 x_msg_count => l_msg_count,
1403 x_msg_data => l_msg_data);
1404
1405 /* Now get the customer_trx_line_id of the parent */
1406 IF l_return_status = FND_API.G_RET_STS_SUCCESS
1407 THEN
1408
1409 /* OM responded with the attributes, use them to go
1410 get the line id of the parent invoice line. Note that
1411 this routine only returns the first 6 even though as
1412 many as 14 are used regularly by OM */
1413
1414 /* 5622095 - limit return to only the row that has
1415 interface_line_attribute11 = '0'. */
1416
1417 /* 9037071 - Handle ORA-1422 by making join intentionally
1418 pick the first line that matches that criteria and
1419 has zeros in attribute11 and 14 */
1420 BEGIN
1421 SELECT MIN(customer_trx_line_id)
1422 INTO l_customer_trx_line_id
1423 FROM RA_CUSTOMER_TRX_LINES
1424 WHERE interface_line_context = l_line_flex_rec.interface_line_context
1425 AND interface_line_attribute1 = l_line_flex_rec.interface_line_attribute1
1426 AND interface_line_attribute2 = l_line_flex_rec.interface_line_attribute2
1427 AND interface_line_attribute3 = l_line_flex_rec.interface_line_attribute3
1428 AND interface_line_attribute4 = l_line_flex_rec.interface_line_attribute4
1429 AND interface_line_attribute5 = l_line_flex_rec.interface_line_attribute5
1430 AND interface_line_attribute6 = l_line_flex_rec.interface_line_attribute6
1431 AND ltrim(interface_line_attribute11) = '0' --13018057
1432 AND ltrim(interface_line_attribute14) = '0'; --13018057
1433
1434 EXCEPTION
1435 WHEN NO_DATA_FOUND THEN
1436 debug('unable to locate matching line in ra_customer_trx_lines');
1437 l_customer_trx_line_id := -98;
1438 END;
1439 ELSE
1440 /* OM responded with an error
1441 return a bogus value so no joins are made */
1442 debug('unable to find parent line for so_line_id=' || p_so_line_id);
1443 l_customer_trx_line_id := -99;
1444 END IF;
1445
1446 RETURN l_customer_trx_line_id;
1447
1448 END get_line_id;
1449
1450 PROCEDURE copy_parent_contingencies (p_request_id NUMBER) IS
1451
1452 l_user_id NUMBER;
1453 l_exists NUMBER := 0;
1454
1455 BEGIN
1456
1457 debug('copy_parent_contingencies()+');
1458 debug(' p_request_id : ' || p_request_id);
1459
1460 l_user_id := fnd_global.user_id;
1461
1462 /* 5513146 - Check for lines in interface table before
1463 executing the INSERT. */
1464 SELECT 1
1465 INTO l_exists
1466 FROM dual
1467 WHERE EXISTS (select 'at least one child'
1468 from RA_INTERFACE_LINES il
1469 where il.request_id = p_request_id
1470 and il.parent_line_id is not null);
1471
1472 IF l_exists <> 0
1473 THEN
1474 INSERT INTO ar_line_conts
1475 (
1476 customer_trx_line_id,
1477 contingency_id,
1478 contingency_code,
1479 expiration_date,
1480 expiration_days,
1481 expiration_event_date,
1482 reason_removal_date,
1483 completed_flag,
1484 defaulted_in_ar_flag,
1485 request_id,
1486 created_by,
1487 creation_date,
1488 last_updated_by,
1489 last_update_date,
1490 last_update_login,
1491 org_id
1492 )
1493 SELECT
1494 ctl.customer_trx_line_id,
1495 plc.contingency_id,
1496 plc.contingency_id,
1497 plc.expiration_date,
1498 plc.expiration_days,
1499 plc.expiration_event_date,
1500 plc.reason_removal_date,
1501 plc.completed_flag,
1502 'C', -- indicates it was copied, not defaulted or imported
1503 p_request_id,
1504 l_user_id,
1505 sysdate,
1506 l_user_id,
1507 sysdate,
1508 l_user_id,
1509 plc.org_id
1510 FROM ra_customer_trx ct,
1511 ra_customer_trx_lines ctl,
1512 ra_cust_trx_types ctt,
1513 ra_interface_lines il,
1514 ar_line_conts plc
1515 WHERE ct.request_id = p_request_id
1516 AND ct.cust_trx_type_id = ctt.cust_trx_type_id
1517 AND nvl(ct.invoicing_rule_id, 0) <> -3 /* 11711172 */
1518 AND ctt.type = 'INV'
1519 AND ct.customer_trx_id = ctl.customer_trx_id
1520 AND ctl.line_type = 'LINE'
1521 AND il.interface_line_id = ctl.customer_trx_line_id
1522 AND il.parent_line_id IS NOT NULL
1523 AND plc.customer_trx_line_id = get_line_id(il.parent_line_id)
1524 AND NOT EXISTS (
1525 SELECT 'contingency already applied'
1526 FROM ar_line_conts clc
1527 WHERE clc.customer_trx_line_id = ctl.customer_trx_line_id
1528 AND clc.contingency_code = plc.contingency_id);
1529
1530 debug('rows copied ar_line_conts: ' || SQL%ROWCOUNT);
1531
1532 END IF; -- end of l_exists condition
1533
1534 debug('copy_parent_contingencies()-');
1535
1536 EXCEPTION
1537 WHEN NO_DATA_FOUND THEN
1538 debug(' No child contingencies to copy');
1539 debug('copy_parrent_contingencies()-');
1540 RETURN;
1541
1542 WHEN OTHERS THEN
1543 debug('OTHERS: copy_parent_contingencies');
1544 debug(sqlerrm);
1545 RAISE;
1546
1547 END copy_parent_contingencies;
1548
1549 /* Bug 4693399 - added customer_trx_line_id for manual invoices */
1550 /* 5236506 - Do not default post or pre billing customer acceptance
1551 contingencies on OM lines */
1552 PROCEDURE default_contingencies (p_request_id NUMBER
1553 ,p_customer_trx_line_id ra_customer_trx_lines.customer_trx_line_id%TYPE) IS
1554
1555 l_user_id NUMBER;
1556
1557 BEGIN
1558
1559 debug('default_contingencies()+');
1560 debug(' p_request_id : ' || p_request_id);
1561 debug(' p_customer_trx_line_id : ' || p_customer_trx_line_id);
1562
1563 /* 4521577 - Payment term and credit classifications need to be set
1564 first. We'll remove them later if they are expired or
1565 not needed */
1566
1567 /* 8889297 - Only call term and creditworthiness if
1568 those values are set in system options table */
1569 IF arp_standard.sysparm.payment_threshold IS NOT NULL
1570 THEN
1571 insert_term_contingencies(p_request_id, p_customer_trx_line_id);
1572 END IF;
1573
1574 IF (arp_standard.sysparm.credit_classification1 IS NOT NULL OR
1575 arp_standard.sysparm.credit_classification2 IS NOT NULL OR
1576 arp_standard.sysparm.credit_classification3 IS NOT NULL)
1577 THEN
1578 insert_credit_contingencies(p_request_id, p_customer_trx_line_id);
1579 END IF;
1580
1581 IF p_request_id IS NULL AND
1582 p_customer_trx_line_id IS NOT NULL THEN
1583 INSERT INTO ar_rdr_parameters_gt
1584 (
1585 source_line_id,
1586 batch_source_id,
1587 profile_class_id,
1588 cust_account_id,
1589 cust_acct_site_id,
1590 cust_trx_type_id,
1591 -- item_category_id, (xportal issue logged)
1592 inventory_item_id,
1593 memo_line_id,
1594 org_id,
1595 accounting_rule_id,
1596 ship_to_cust_acct_id,
1597 ship_to_site_use_id
1598 )
1599 SELECT -- Removed the hint that was added as part of bug 13828621
1600 ctl.customer_trx_line_id,
1601 ct.batch_source_id,
1602 decode(ctl.deferral_exclusion_flag, 'Y','',
1603 decode(hcp.cust_account_id,'','',
1604 decode(hcp.site_use_id,'',hcp.profile_class_id,
1605 hcp.profile_class_id))),--For 9855526
1606 ct.bill_to_customer_id,
1607 ct.bill_to_site_use_id,
1608 ctt.cust_trx_type_id,
1609 -- item_category_id
1610 ctl.inventory_item_id,
1611 ctl.memo_line_id,
1612 ct.org_id,
1613 ctl.accounting_rule_id,
1614 NVL(ctl.ship_to_customer_id,ct.ship_to_customer_id),
1615 NVL(ctl.ship_to_site_use_id,ct.ship_to_site_use_id)
1616 FROM
1617 ra_customer_trx ct,
1618 ra_customer_trx_lines ctl,
1619 hz_customer_profiles hcp,
1620 ra_cust_trx_types ctt
1621 WHERE (ctl.customer_trx_line_id = p_customer_trx_line_id)
1622 AND ct.cust_trx_type_id = ctt.cust_trx_type_id
1623 AND ctt.type = 'INV'
1624 AND ct.customer_trx_id = ctl.customer_trx_id
1625 AND ctl.line_type = 'LINE'
1626 AND ct.bill_to_customer_id = hcp.cust_account_id (+)
1627 AND ct.bill_to_site_use_id = NVL(hcp.site_use_id, ct.bill_to_site_use_id )
1628 AND nvl(ctl.deferral_exclusion_flag, 'N') <> 'Y'
1629 AND nvl(ct.invoicing_rule_id, 0) <> -3; /* 11711172 */
1630
1631 ELSIF p_request_id IS NOT NULL THEN
1632
1633 INSERT INTO ar_rdr_parameters_gt
1634 (
1635 source_line_id,
1636 batch_source_id,
1637 profile_class_id,
1638 cust_account_id,
1639 cust_acct_site_id,
1640 cust_trx_type_id,
1641 -- item_category_id, (xportal issue logged)
1642 inventory_item_id,
1643 memo_line_id,
1644 org_id,
1645 accounting_rule_id,
1646 ship_to_cust_acct_id,
1647 ship_to_site_use_id
1648 )
1649 SELECT /*+ index(ctl ra_customer_trx_lines_n2) */
1650 ctl.customer_trx_line_id,
1651 ct.batch_source_id,
1652 decode(ctl.deferral_exclusion_flag, 'Y','',
1653 decode(hcp.cust_account_id,'','',
1654 decode(hcp.site_use_id,'',hcp.profile_class_id,
1655 hcp.profile_class_id))),--For 9855526
1656 ct.bill_to_customer_id,
1657 ct.bill_to_site_use_id,
1658 ctt.cust_trx_type_id,
1659 -- item_category_id
1660 ctl.inventory_item_id,
1661 ctl.memo_line_id,
1662 ct.org_id,
1663 ctl.accounting_rule_id,
1664 NVL(ctl.ship_to_customer_id,ct.ship_to_customer_id),
1665 NVL(ctl.ship_to_site_use_id,ct.ship_to_site_use_id)
1666 FROM
1667 ra_customer_trx ct,
1668 ra_customer_trx_lines ctl,
1669 hz_customer_profiles hcp,
1670 ra_cust_trx_types ctt
1671 WHERE ct.request_id = p_request_id
1672 AND ct.cust_trx_type_id = ctt.cust_trx_type_id
1673 AND ctt.type = 'INV'
1674 AND ct.customer_trx_id = ctl.customer_trx_id
1675 AND ctl.line_type = 'LINE'
1676 AND ct.bill_to_customer_id = hcp.cust_account_id (+)
1677 AND ct.bill_to_site_use_id = nvl(hcp.site_use_id, ct.bill_to_site_use_id)
1678 AND nvl(ctl.deferral_exclusion_flag, 'N') <> 'Y'
1679 AND nvl(ct.invoicing_rule_id, 0) <> -3; /* 11711172 */
1680
1681 END IF;
1682
1683 debug('rows inserted in rule gt: ' || SQL%ROWCOUNT);
1684 /*
1685 Calling Hook Procedure to populate the attribute columns
1686 */
1687 AR_CUSTOM_PARAMS_HOOK_PKG.populateContingencyAttributes();
1688
1689 fun_rule_pub.apply_rule_bulk (
1690 p_application_short_name => 'AR',
1691 p_rule_object_name => c_rule_object_name,
1692 p_param_view_name => 'AR_RDR_PARAMETERS_GT',
1693 p_additional_where_clause => '1=1',
1694 p_primary_key_column_name => 'SOURCE_LINE_ID'
1695 );
1696
1697 debug('returned after the call to fun_rules_pub.apply_rule_bulk');
1698
1699 l_user_id := fnd_global.user_id;
1700
1701 /* As from R12 contingency_id replaces contingency_code as the unique
1702 identifier along with customer_trx_line_id, but remains part of the key
1703 so to avoid a case change we populate contingency_code with contingency_id
1704 */
1705
1706 /* 5236506 - added where clause condition to exclude the defaulting
1707 of specific contingencies for OM transactions. To do this, we
1708 exclude the insert if the interface_line_context = g_om_context
1709 and the contingency revrec_event_code in (INVOICING or CUSTOMER_ACCE.)
1710 INVOICING is really 'pre-billing customer acceptance' and
1711 CUSTOMER_ACCEPTANCE is 'post-billing customer acceptance'.
1712 */
1713
1714 /* 5222197 - Fix from 5236506 caused problems when transactions had
1715 no context specified. Need to NVL that column to insure that
1716 the condition defaults to false */
1717
1718 /* 5201842 - Added code to populate expiration_date, and
1719 expiration_event_date */
1720
1721 /* 7039838 - conditionally call insert based on parameters */
1722
1723 IF p_request_id IS NOT NULL
1724 THEN
1725 /* Modified logic for autoinvoice */
1726 INSERT INTO ar_line_conts
1727 (
1728 customer_trx_line_id,
1729 contingency_code,
1730 contingency_id,
1731 expiration_date,
1732 expiration_days,
1733 expiration_event_date,
1734 reason_removal_date,
1735 completed_flag,
1736 defaulted_in_ar_flag,
1737 request_id,
1738 created_by,
1739 creation_date,
1740 last_updated_by,
1741 last_update_date,
1742 last_update_login,
1743 org_id
1744 )
1745 SELECT /*+ leading(rbr,ctl) use_hash(ctl)
1746 index(ctl,RA_CUSTOMER_TRX_LINES_N4) */
1747 rbr.id,
1748 dr.contingency_id,
1749 dr.contingency_id,
1750 decode(dr.expiration_event_code,
1751 'TRANSACTION_DATE', trunc(ct.trx_date)
1752 + nvl(dr.expiration_days, 0),
1753 'SHIP_CONFIRM_DATE', trunc(ct.ship_date_actual)
1754 + nvl(dr.expiration_days, 0), NULL),
1755 MAX(expiration_days),
1756 decode(dr.expiration_event_code,
1757 'TRANSACTION_DATE', trunc(ct.trx_date),
1758 'SHIP_CONFIRM_DATE', trunc(ct.ship_date_actual), NULL),
1759 decode(MAX(dr.expiration_event_code), 'INVOICING', sysdate, NULL)
1760 reason_removal_date,
1761 decode(MAX(dr.expiration_event_code), 'INVOICING', 'Y', 'N')
1762 completed_flag,
1763 'Y',
1764 p_request_id,
1765 l_user_id,
1766 sysdate,
1767 l_user_id,
1768 sysdate,
1769 l_user_id,
1770 ct.org_id
1771 FROM fun_rule_bulk_result_gt rbr,
1772 ar_deferral_reasons dr,
1773 ra_customer_trx_lines ctl,
1774 ra_customer_trx ct,
1775 ra_cust_trx_types ctt,
1776 pa_implementations pa
1777 WHERE rbr.result_value = dr.contingency_id
1778 AND rbr.id = ctl.customer_trx_line_id
1779 AND ctl.customer_trx_id = ct.customer_trx_id
1780 AND ctl.request_id = p_request_id -- 7039838
1781 AND ct.cust_trx_type_id = ctt.cust_trx_type_id
1782 AND ctt.type = 'INV'
1783 AND ctl.line_type = 'LINE'
1784 AND ct.batch_source_id NOT IN (20, 21)
1785 AND ct.org_id = pa.org_id (+)
1786 AND ct.batch_source_id <> NVL(pa.invoice_batch_source_id, -99)
1787 AND ct.batch_source_id <> NVL(pa.cc_ic_ar_batch_source_id, -99)
1788 AND nvl(ct.invoicing_rule_id, 0) <> -3 /* 11711172 */
1789 AND sysdate BETWEEN NVL(dr.start_date,SYSDATE) AND
1790 NVL(dr.end_date,SYSDATE)
1791 AND NOT (NVL(ctl.interface_line_context,'##NOT_MATCH##') = g_om_context AND
1792 dr.revrec_event_code in ('INVOICING','CUSTOMER_ACCEPTANCE'))
1793 AND NOT EXISTS
1794 ( SELECT 'contingency exists'
1795 FROM ar_line_conts lc
1796 WHERE lc.customer_trx_line_id = rbr.id
1797 AND lc.contingency_id = rbr.result_value
1798 )
1799 GROUP BY rbr.id, dr.contingency_id, dr.expiration_event_code,
1800 dr.expiration_days, ct.org_id, ct.trx_date, ct.ship_date_actual;
1801
1802 ELSE
1803 /* original logic */
1804 INSERT INTO ar_line_conts
1805 (
1806 customer_trx_line_id,
1807 contingency_code,
1808 contingency_id,
1809 expiration_date,
1810 expiration_days,
1811 expiration_event_date,
1812 reason_removal_date,
1813 completed_flag,
1814 defaulted_in_ar_flag,
1815 request_id,
1816 created_by,
1817 creation_date,
1818 last_updated_by,
1819 last_update_date,
1820 last_update_login,
1821 org_id
1822 )
1823 SELECT
1824 rbr.id,
1825 dr.contingency_id,
1826 dr.contingency_id,
1827 decode(dr.expiration_event_code,
1828 'TRANSACTION_DATE', trunc(ct.trx_date)
1829 + nvl(dr.expiration_days, 0),
1830 'SHIP_CONFIRM_DATE', trunc(ct.ship_date_actual)
1831 + nvl(dr.expiration_days, 0), NULL),
1832 MAX(expiration_days),
1833 decode(dr.expiration_event_code,
1834 'TRANSACTION_DATE', trunc(ct.trx_date),
1835 'SHIP_CONFIRM_DATE', trunc(ct.ship_date_actual), NULL),
1836 decode(MAX(dr.expiration_event_code), 'INVOICING', sysdate, NULL)
1837 reason_removal_date,
1838 decode(MAX(dr.expiration_event_code), 'INVOICING', 'Y', 'N')
1839 completed_flag,
1840 'Y',
1841 p_request_id,
1842 l_user_id,
1843 sysdate,
1844 l_user_id,
1845 sysdate,
1846 l_user_id,
1847 ct.org_id
1848 FROM fun_rule_bulk_result_gt rbr,
1849 ar_deferral_reasons dr,
1850 ra_customer_trx_lines ctl,
1851 ra_customer_trx ct,
1852 ra_cust_trx_types ctt,
1853 pa_implementations pa
1854 WHERE rbr.result_value = dr.contingency_id
1855 AND rbr.id = ctl.customer_trx_line_id
1856 AND ctl.customer_trx_id = ct.customer_trx_id
1857 AND ct.cust_trx_type_id = ctt.cust_trx_type_id
1858 AND ctt.type = 'INV'
1859 AND ctl.line_type = 'LINE'
1860 AND ct.batch_source_id NOT IN (20, 21)
1861 AND ct.org_id = pa.org_id (+)
1862 AND ct.batch_source_id <> NVL(pa.invoice_batch_source_id, -99)
1863 AND ct.batch_source_id <> NVL(pa.cc_ic_ar_batch_source_id, -99)
1864 AND nvl(ct.invoicing_rule_id, 0) <> -3 /* 11711172 */
1865 AND sysdate BETWEEN NVL(dr.start_date,SYSDATE) AND
1866 NVL(dr.end_date,SYSDATE)
1867 AND NOT (NVL(ctl.interface_line_context,'##NOT_MATCH##') = g_om_context AND
1868 dr.revrec_event_code in ('INVOICING','CUSTOMER_ACCEPTANCE'))
1869 AND NOT EXISTS
1870 ( SELECT 'contingency exists'
1871 FROM ar_line_conts lc
1872 WHERE lc.customer_trx_line_id = rbr.id
1873 AND lc.contingency_id = rbr.result_value
1874 )
1875 AND
1876 (DECODE(g_source,null,decode(ct.created_from,'ARXTWMAI',decode(dr.revrec_event_code,'INVOICING',1,0),0),0)
1877 <> 1)
1878 GROUP BY rbr.id, dr.contingency_id, dr.expiration_event_code,
1879 dr.expiration_days, ct.org_id, ct.trx_date, ct.ship_date_actual;
1880
1881 END IF; -- end p_request_id
1882
1883 debug('rows inserted ar_line_conts: ' || SQL%ROWCOUNT);
1884 debug('default_contingencies()-');
1885
1886 EXCEPTION
1887 WHEN NO_DATA_FOUND THEN
1888 debug('NO_DATA_FOUND: default_contingencies');
1889 debug(sqlerrm);
1890 RAISE;
1891
1892 WHEN OTHERS THEN
1893 debug('OTHERS: default_contingencies');
1894 debug(sqlerrm);
1895 RAISE;
1896
1897 END default_contingencies;
1898
1899 PROCEDURE insert_contingencies_from_gt (p_request_id NUMBER) IS
1900
1901 l_user_id NUMBER;
1902
1903 BEGIN
1904
1905 debug('insert_contingencies_from_gt()+');
1906 debug(' p_request_id : ' || p_request_id);
1907
1908 l_user_id := fnd_global.user_id;
1909
1910 -- invoice creation api uses global temporary tables to accept
1911 -- input data as opposed to interface tables.
1912
1913 /* As from R12 contingency_id replaces contingency_code as the unique
1914 identifier along with customer_trx_line_id, but remains part of the key
1915 so to avoid a case change we populate contingency_code with contingency_id
1916 */
1917
1918 INSERT INTO ar_line_conts
1919 (
1920 customer_trx_line_id,
1921 contingency_id,
1922 contingency_code,
1923 expiration_date,
1924 expiration_days,
1925 expiration_event_date,
1926 reason_removal_date,
1927 completed_flag,
1928 completed_by,
1929 request_id,
1930 created_by,
1931 creation_date,
1932 last_updated_by,
1933 last_update_date,
1934 last_update_login,
1935 org_id
1936 )
1937 SELECT
1938 tlg.customer_trx_line_id,
1939 tcg.contingency_id,
1940 tcg.contingency_id,
1941 nvl(trunc(tcg.expiration_date), decode(dr.expiration_event_code,
1942 'TRANSACTION_DATE', trunc(thg.trx_date)
1943 + nvl(tcg.expiration_days, nvl(dr.expiration_days, 0)),
1944 'SHIP_CONFIRM_DATE', trunc(thg.ship_date_actual)
1945 + nvl(tcg.expiration_days, nvl(dr.expiration_days, 0)), NULL))
1946 expiration_date,
1947 nvl(tcg.expiration_days, dr.expiration_days) expiration_days,
1948 decode( dr.expiration_event_code,
1949 'TRANSACTION_DATE', trunc(thg.trx_date),
1950 'SHIP_CONFIRM_DATE', trunc(thg.ship_date_actual), NULL)
1951 expiration_event_date,
1952 decode(revrec_event_code, 'INVOICING',
1953 NVL(expiration_date, sysdate), NULL) reason_removal_date,
1954 decode(revrec_event_code, 'INVOICING', 'Y',nvl(completed_flag, 'N'))
1955 completed_flag,
1956 decode(revrec_event_code, 'INVOICING', completed_by, NULL)
1957 completed_by,
1958 tlg.request_id,
1959 l_user_id,
1960 sysdate,
1961 l_user_id,
1962 sysdate,
1963 l_user_id,
1964 thg.org_id
1965 FROM ar_trx_lines_gt tlg,
1966 ar_trx_header_gt thg,
1967 ra_cust_trx_types ctt,
1968 ar_trx_contingencies_gt tcg,
1969 ar_deferral_reasons dr,
1970 pa_implementations pa
1971 WHERE tlg.request_id = p_request_id
1972 AND tlg.trx_header_id = thg.trx_header_id
1973 AND thg.batch_source_id NOT IN (20, 21)
1974 AND thg.org_id = pa.org_id (+)
1975 AND thg.batch_source_id <> NVL(pa.invoice_batch_source_id, -99)
1976 AND thg.batch_source_id <> NVL(pa.cc_ic_ar_batch_source_id, -99)
1977 AND nvl(thg.invoicing_rule_id, 0) <> -3 /* 11711172 */
1978 AND thg.cust_trx_type_id = ctt.cust_trx_type_id
1979 AND ctt.type = 'INV'
1980 AND tlg.line_type = 'LINE'
1981 AND tlg.trx_line_id = tcg.trx_line_id
1982 AND tcg.contingency_id = dr.contingency_id
1983 AND NOT EXISTS
1984 ( SELECT 'errors exist'
1985 FROM ar_trx_errors_gt err
1986 WHERE err.trx_header_id = tlg.trx_header_id
1987 AND err.trx_line_id = tlg.trx_line_id
1988 );
1989
1990 debug('gt contingencies inserted: ' || SQL%ROWCOUNT);
1991 debug('insert_contingencies_from_gt()-');
1992
1993 EXCEPTION
1994 WHEN NO_DATA_FOUND THEN
1995 debug('NO_DATA_FOUND: insert_contingencies_from_gt');
1996 debug(sqlerrm);
1997 RAISE;
1998
1999 WHEN OTHERS THEN
2000 debug('OTHERS: insert_contingencies_from_gt');
2001 debug(sqlerrm);
2002 RAISE;
2003
2004 END insert_contingencies_from_gt;
2005
2006
2007 PROCEDURE insert_contingencies_from_itf (p_request_id NUMBER) IS
2008
2009 l_user_id NUMBER;
2010
2011 BEGIN
2012
2013 debug('insert_contingencies_from_itf()+');
2014 debug( ' p_request_id : ' || p_request_id);
2015
2016 l_user_id := fnd_global.user_id;
2017
2018 -- now we are about to process the contingencies passed through the
2019 -- ar_interface_contingencies_all before we do that we have retrieve
2020 -- the context and using that determing the dynamic portion of
2021 -- where clause.
2022
2023 /* As from R12 contingency_id replaces contingency_code as the unique
2024 identifier along with customer_trx_line_id, but remains part of the key
2025 so to avoid a case change we populate contingency_code with contingency_id
2026 */
2027
2028 INSERT INTO ar_line_conts
2029 (
2030 customer_trx_line_id,
2031 contingency_id,
2032 contingency_code,
2033 expiration_date,
2034 expiration_days,
2035 expiration_event_date,
2036 reason_removal_date,
2037 completed_flag,
2038 completed_by,
2039 request_id,
2040 created_by,
2041 creation_date,
2042 last_updated_by,
2043 last_update_date,
2044 org_id
2045 )
2046 SELECT
2047 ctl.customer_trx_line_id,
2048 ic.contingency_id,
2049 ic.contingency_id,
2050 nvl(trunc(expiration_date), decode(dr.expiration_event_code,
2051 'TRANSACTION_DATE', trunc(ct.trx_date)
2052 + nvl(ic.expiration_days, nvl(dr.expiration_days, 0)),
2053 'SHIP_CONFIRM_DATE', trunc(ct.ship_date_actual)
2054 + nvl(ic.expiration_days, nvl(dr.expiration_days, 0)), NULL))
2055 expiration_date,
2056 nvl(ic.expiration_days, dr.expiration_days) expiration_days,
2057 decode( dr.expiration_event_code,
2058 'TRANSACTION_DATE', trunc(ct.trx_date),
2059 'SHIP_CONFIRM_DATE', trunc(ct.ship_date_actual), NULL)
2060 expiration_event_date,
2061 decode(revrec_event_code, 'INVOICING',
2062 nvl(expiration_date, sysdate),
2063 DECODE(NVL(completed_flag, 'N'),'Y',
2064 NVL(expiration_date,sysdate), NULL))
2065 reason_removal_date,
2066 decode(revrec_event_code, 'INVOICING', 'Y',nvl(completed_flag, 'N'))
2067 completed_flag,
2068 decode(revrec_event_code, 'INVOICING', completed_by, NULL)
2069 completed_by,
2070 ctl.request_id,
2071 l_user_id,
2072 sysdate,
2073 l_user_id,
2074 sysdate,
2075 ct.org_id
2076 FROM ra_customer_trx_lines ctl,
2077 ra_customer_trx ct,
2078 ra_cust_trx_types ctt,
2079 ar_interface_conts ic,
2080 ar_deferral_reasons dr,
2081 pa_implementations pa
2082 WHERE ctl.request_id = p_request_id
2083 AND ctl.customer_trx_id = ct.customer_trx_id
2084 AND ct.batch_source_id NOT IN (20, 21)
2085 AND ct.org_id = pa.org_id (+)
2086 AND ct.batch_source_id <> NVL(pa.invoice_batch_source_id, -99)
2087 AND ct.batch_source_id <> NVL(pa.cc_ic_ar_batch_source_id, -99)
2088 AND nvl(ct.invoicing_rule_id, 0) <> -3 /* 11711172 */
2089 AND ct.cust_trx_type_id = ctt.cust_trx_type_id
2090 AND ctt.type = 'INV'
2091 AND ctl.line_type = 'LINE'
2092 AND ctl.customer_trx_line_id = ic.interface_line_id
2093 AND ic.contingency_id = dr.contingency_id
2094 AND NOT EXISTS
2095 (SELECT 'errors'
2096 FROM ra_interface_errors ie
2097 WHERE ie.interface_line_id = ctl.customer_trx_line_id);
2098
2099 debug('itf contingencies inserted: ' || SQL%ROWCOUNT);
2100 debug('insert_contingencies_from_itf()-');
2101
2102 EXCEPTION
2103 WHEN NO_DATA_FOUND THEN
2104 debug('NO_DATA_FOUND: insert_contingencies_from_itf');
2105 debug(sqlerrm);
2106 RAISE;
2107
2108 WHEN OTHERS THEN
2109 debug('OTHERS: insert_contingencies_from_itf');
2110 debug(sqlerrm);
2111 RAISE;
2112
2113 END insert_contingencies_from_itf;
2114
2115 /* Bug 4693399 - added customer_trx_line_id for manual invoices */
2116 PROCEDURE insert_deferred_lines (p_request_id NUMBER
2117 ,p_customer_trx_line_id ra_customer_trx_lines.customer_trx_line_id%TYPE) IS
2118
2119 l_user_id NUMBER;
2120 l_insert_stmt VARCHAR2(4000);
2121 l_where_clause VARCHAR2(4000);
2122
2123 BEGIN
2124
2125 debug('insert_deferred_lines()+');
2126 debug('p_request_id : ' || p_request_id);
2127 debug('p_customer_trx_line_id : ' || p_customer_trx_line_id);
2128
2129 l_user_id := fnd_global.user_id;
2130
2131 -- please note we are joining with ar_line_conts
2132 -- becuase we want to insert rows in the parent table only if
2133 -- there exists a row in the child table.
2134
2135 IF (g_source = 'AR_INVOICE_API') THEN
2136
2137 INSERT INTO ar_deferred_lines
2138 (
2139 customer_trx_line_id,
2140 customer_trx_id,
2141 original_collectibility_flag,
2142 line_collectible_flag,
2143 manual_override_flag,
2144 amount_due_original,
2145 acctd_amount_due_original,
2146 amount_recognized,
2147 acctd_amount_recognized,
2148 amount_pending,
2149 acctd_amount_pending,
2150 parent_line_id,
2151 attribute_category,
2152 attribute1,
2153 attribute2,
2154 attribute3,
2155 attribute4,
2156 attribute5,
2157 attribute6,
2158 attribute7,
2159 attribute8,
2160 attribute9,
2161 attribute10,
2162 attribute11,
2163 attribute12,
2164 attribute13,
2165 attribute14,
2166 attribute15,
2167 request_id,
2168 created_by,
2169 creation_date,
2170 last_updated_by,
2171 last_update_date,
2172 org_id
2173 )
2174 SELECT
2175 tlg.customer_trx_line_id,
2176 MAX(thg.customer_trx_id),
2177 'N',
2178 'N',
2179 'N',
2180 MAX(tlg.extended_amount),
2181 MAX(decode(g_minimum_accountable_unit_f, NULL,
2182 ROUND( tlg.extended_amount * nvl(thg.exchange_rate, 1),
2183 g_precision_f),
2184 ROUND((tlg.extended_amount * nvl(thg.exchange_rate, 1))
2185 / g_minimum_accountable_unit_f) * g_minimum_accountable_unit_f)),
2186 0,
2187 0,
2188 0,
2189 0,
2190 MAX(tlg.parent_line_id),
2191 MAX(tcg.attribute_category),
2192 MAX(tcg.attribute1),
2193 MAX(tcg.attribute2),
2194 MAX(tcg.attribute3),
2195 MAX(tcg.attribute4),
2196 MAX(tcg.attribute5),
2197 MAX(tcg.attribute6),
2198 MAX(tcg.attribute7),
2199 MAX(tcg.attribute8),
2200 MAX(tcg.attribute9),
2201 MAX(tcg.attribute10),
2202 MAX(tcg.attribute11),
2203 MAX(tcg.attribute12),
2204 MAX(tcg.attribute13),
2205 MAX(tcg.attribute14),
2206 MAX(tcg.attribute15),
2207 MAX(tlg.request_id),
2208 l_user_id,
2209 sysdate,
2210 l_user_id,
2211 sysdate,
2212 thg.org_id
2213 FROM ar_trx_header_gt thg,
2214 ar_trx_lines_gt tlg,
2215 ar_trx_contingencies_gt tcg,
2216 ar_line_conts lrc
2217 WHERE tlg.request_id = p_request_id
2218 AND tlg.customer_trx_id = thg.customer_trx_id
2219 AND tlg.customer_trx_line_id = lrc.customer_trx_line_id
2220 AND tlg.trx_header_id = tcg.trx_header_id
2221 AND tlg.trx_line_id = tcg.trx_line_id
2222 GROUP BY tlg.customer_trx_line_id, thg.org_id;
2223
2224 -- do the same for contingencies that are generated in this program
2225 -- not passed through the GT. The reason we can't do this with one SQL
2226 -- is because we would like to copy the values passed in the attributes
2227 -- columns.
2228
2229 INSERT INTO ar_deferred_lines
2230 (
2231 customer_trx_line_id,
2232 customer_trx_id,
2233 original_collectibility_flag,
2234 line_collectible_flag,
2235 manual_override_flag,
2236 amount_due_original,
2237 acctd_amount_due_original,
2238 amount_recognized,
2239 acctd_amount_recognized,
2240 amount_pending,
2241 acctd_amount_pending,
2242 parent_line_id,
2243 request_id,
2244 created_by,
2245 creation_date,
2246 last_updated_by,
2247 last_update_date,
2248 org_id
2249 )
2250 SELECT
2251 tlg.customer_trx_line_id,
2252 MAX(thg.customer_trx_id),
2253 'N',
2254 'N',
2255 'N',
2256 MAX(tlg.extended_amount),
2257 MAX(decode(g_minimum_accountable_unit_f, NULL,
2258 ROUND( tlg.extended_amount * nvl(thg.exchange_rate, 1),
2259 g_precision_f),
2260 ROUND((tlg.extended_amount * nvl(thg.exchange_rate, 1))
2261 / g_minimum_accountable_unit_f) * g_minimum_accountable_unit_f)),
2262 0,
2263 0,
2264 0,
2265 0,
2266 MAX(tlg.parent_line_id),
2267 MAX(tlg.request_id),
2268 l_user_id,
2269 sysdate,
2270 l_user_id,
2271 sysdate,
2272 thg.org_id
2273 FROM ar_trx_header_gt thg,
2274 ar_trx_lines_gt tlg,
2275 ar_line_conts lrc
2276 WHERE tlg.request_id = p_request_id
2277 AND tlg.customer_trx_id = thg.customer_trx_id
2278 AND tlg.customer_trx_line_id = lrc.customer_trx_line_id
2279 AND NOT EXISTS
2280 (SELECT 'line already inserted'
2281 FROM ar_deferred_lines dl
2282 WHERE dl.customer_trx_line_id = lrc.customer_trx_line_id)
2283 GROUP BY tlg.customer_trx_line_id, thg.org_id;
2284
2285 ELSE
2286 IF p_request_id IS NOT NULL THEN
2287 INSERT INTO ar_deferred_lines
2288 (
2289 customer_trx_line_id,
2290 customer_trx_id,
2291 original_collectibility_flag,
2292 line_collectible_flag,
2293 manual_override_flag,
2294 amount_due_original,
2295 acctd_amount_due_original,
2296 amount_recognized,
2297 acctd_amount_recognized,
2298 amount_pending,
2299 acctd_amount_pending,
2300 attribute_category,
2301 attribute1,
2302 attribute2,
2303 attribute3,
2304 attribute4,
2305 attribute5,
2306 attribute6,
2307 attribute7,
2308 attribute8,
2309 attribute9,
2310 attribute10,
2311 attribute11,
2312 attribute12,
2313 attribute13,
2314 attribute14,
2315 attribute15,
2316 request_id,
2317 created_by,
2318 creation_date,
2319 last_updated_by,
2320 last_update_date,
2321 org_id,
2322 parent_line_id
2323 )
2324 SELECT
2325 ctl.customer_trx_line_id,
2326 MAX(ct.customer_trx_id),
2327 'N',
2328 'N',
2329 'N',
2330 MAX(ctl.extended_amount),
2331 MAX(decode(g_minimum_accountable_unit_f, NULL,
2332 ROUND( ctl.extended_amount * nvl(ct.exchange_rate, 1),
2333 g_precision_f),
2334 ROUND((ctl.extended_amount * nvl(ct.exchange_rate, 1))
2335 / g_minimum_accountable_unit_f) * g_minimum_accountable_unit_f)),
2336 0,
2337 0,
2338 0,
2339 0,
2340 MAX(ic.attribute_category),
2341 MAX(ic.attribute1),
2342 MAX(ic.attribute2),
2343 MAX(ic.attribute3),
2344 MAX(ic.attribute4),
2345 MAX(ic.attribute5),
2346 MAX(ic.attribute6),
2347 MAX(ic.attribute7),
2348 MAX(ic.attribute8),
2349 MAX(ic.attribute9),
2350 MAX(ic.attribute10),
2351 MAX(ic.attribute11),
2352 MAX(ic.attribute12),
2353 MAX(ic.attribute13),
2354 MAX(ic.attribute14),
2355 MAX(ic.attribute15),
2356 MAX(ctl.request_id),
2357 l_user_id,
2358 sysdate,
2359 l_user_id,
2360 sysdate,
2361 ct.org_id,
2362 MAX(il.parent_line_id)
2363 FROM ra_customer_trx ct,
2364 ra_customer_trx_lines ctl,
2365 ar_line_conts lrc,
2366 ar_interface_conts ic,
2367 ra_interface_lines il
2368 WHERE ctl.request_id = p_request_id
2369 AND ctl.customer_trx_id = ct.customer_trx_id
2370 AND ctl.customer_trx_line_id = lrc.customer_trx_line_id
2371 AND ctl.customer_trx_line_id = ic.interface_line_id
2372 AND ctl.customer_trx_line_id = il.interface_line_id
2373 GROUP BY ctl.customer_trx_line_id, ct.org_id;
2374
2375 END IF; -- p_request_id IS NOT NULL
2376
2377 -- do the same for contingencies that are generated in this program
2378 -- not passed through the interface table. The reason we can't do this
2379 -- with one SQL is because we would like to copy the values passed in the
2380 -- attributes columns.
2381
2382 /* 5279702 - Populate parent_line_id when possible. This
2383 is important for contingencies defaulted to child lines
2384 from parents. */
2385
2386 INSERT INTO ar_deferred_lines
2387 (
2388 customer_trx_line_id,
2389 customer_trx_id,
2390 original_collectibility_flag,
2391 line_collectible_flag,
2392 manual_override_flag,
2393 amount_due_original,
2394 acctd_amount_due_original,
2395 amount_recognized,
2396 acctd_amount_recognized,
2397 amount_pending,
2398 acctd_amount_pending,
2399 request_id,
2400 created_by,
2401 creation_date,
2402 last_updated_by,
2403 last_update_date,
2404 org_id,
2405 parent_line_id
2406 )
2407 SELECT
2408 ctl.customer_trx_line_id,
2409 MAX(ct.customer_trx_id),
2410 'N',
2411 'N',
2412 'N',
2413 MAX(ctl.extended_amount),
2414 MAX(decode(g_minimum_accountable_unit_f, NULL,
2415 ROUND( ctl.extended_amount * nvl(ct.exchange_rate, 1),
2416 g_precision_f),
2417 ROUND((ctl.extended_amount * nvl(ct.exchange_rate, 1))
2418 / g_minimum_accountable_unit_f) * g_minimum_accountable_unit_f)),
2419 0,
2420 0,
2421 0,
2422 0,
2423 MAX(ctl.request_id),
2424 l_user_id,
2425 sysdate,
2426 l_user_id,
2427 sysdate,
2428 ct.org_id,
2429 MAX(il.parent_line_id)
2430 FROM ra_customer_trx ct,
2431 ra_customer_trx_lines ctl,
2432 ar_line_conts lrc,
2433 ra_interface_lines il
2434 WHERE ((p_request_id IS NULL AND p_customer_trx_line_id IS NOT NULL AND
2435 ctl.customer_trx_line_id = p_customer_trx_line_id) OR
2436 (p_request_id IS NOT NULL AND ctl.request_id = p_request_id))
2437 AND ctl.customer_trx_id = ct.customer_trx_id
2438 AND ctl.customer_trx_line_id = lrc.customer_trx_line_id
2439 AND ctl.customer_trx_line_id = il.interface_line_id (+)
2440 AND NOT EXISTS
2441 (SELECT 'line already inserted'
2442 FROM ar_deferred_lines dl
2443 WHERE dl.customer_trx_line_id = lrc.customer_trx_line_id)
2444 GROUP BY ctl.customer_trx_line_id, ct.org_id;
2445
2446 END IF;
2447
2448 debug('deferred lines inserted: ' || SQL%ROWCOUNT);
2449
2450 -- it is possible that the line gets imported with one pre-billing
2451 -- acceptance contingency. In that case, we would like to insert
2452 -- the a row in the ar_deferred_lines_all, however, we need to mark
2453 -- it as collectible now.
2454
2455 IF p_request_id IS NOT NULL THEN
2456
2457 UPDATE ar_deferred_lines dl
2458 SET line_collectible_flag = 'Y'
2459 WHERE dl.request_id = p_request_id
2460 AND NOT EXISTS
2461 (SELECT 'incomplete contingency'
2462 FROM ar_line_conts_all lc
2463 WHERE request_id = p_request_id
2464 AND lc.customer_trx_line_id = dl.customer_trx_line_id
2465 AND lc.completed_flag = 'N');
2466
2467 /* Bug 12813416 */
2468 update ar_deferred_lines dl
2469 set original_collectibility_flag = 'Y'
2470 WHERE dl.request_id = p_request_id
2471 and dl.customer_trx_id in
2472 (SELECT ctl.customer_trx_id /* its a pre-billing contingency */
2473 FROM ra_customer_trx_lines_all ctl,
2474 ar_line_conts_all lrc,
2475 ar_deferral_reasons dr
2476 WHERE ctl.customer_trx_line_id = dl.customer_trx_line_id
2477 and ctl.customer_trx_line_id=lrc.customer_trx_line_id
2478 and trunc(nvl(lrc.expiration_date,sysdate)) - trunc(sysdate) <= 0
2479 and dr.contingency_id = lrc.contingency_id
2480 AND dr.REVREC_EVENT_CODE = 'INVOICING');
2481
2482 ELSIF p_customer_trx_line_id IS NOT NULL THEN
2483
2484 UPDATE ar_deferred_lines dl
2485 SET line_collectible_flag = 'Y'
2486 WHERE dl.customer_trx_line_id = p_customer_trx_line_id
2487 AND NOT EXISTS
2488 (SELECT 'incomplete contingency'
2489 FROM ar_line_conts_all lc
2490 WHERE customer_trx_line_id = p_customer_trx_line_id
2491 AND lc.customer_trx_line_id = dl.customer_trx_line_id
2492 AND lc.completed_flag = 'N');
2493
2494 /* Bug 12813416 */
2495 update ar_deferred_lines dl
2496 set original_collectibility_flag = 'Y'
2497 WHERE dl.customer_trx_line_id = p_customer_trx_line_id
2498 and dl.customer_trx_id in
2499 (SELECT ctl.customer_trx_id /* its a pre-billing contingency */
2500 FROM ra_customer_trx_lines_all ctl,
2501 ar_line_conts_all lrc,
2502 ar_deferral_reasons dr
2503 WHERE ctl.customer_trx_line_id = dl.customer_trx_line_id
2504 and ctl.customer_trx_line_id=lrc.customer_trx_line_id
2505 and trunc(nvl(lrc.expiration_date,sysdate)) - trunc(sysdate) <= 0
2506 and dr.contingency_id = lrc.contingency_id
2507 AND dr.REVREC_EVENT_CODE = 'INVOICING');
2508 END IF;
2509
2510 debug('deferred lines updated: ' || SQL%ROWCOUNT);
2511
2512 debug('insert_deferred_lines()-');
2513
2514 EXCEPTION
2515 WHEN NO_DATA_FOUND THEN
2516 debug('NO_DATA_FOUND: insert_deferred_lines');
2517 debug(sqlerrm);
2518 RAISE;
2519
2520 WHEN OTHERS THEN
2521 debug('OTHERS: insert_deferred_lines');
2522 debug(sqlerrm);
2523 RAISE;
2524
2525 END insert_deferred_lines;
2526
2527
2528 FUNCTION validate_gt_contingencies (
2529 p_request_id NUMBER)
2530 RETURN NUMBER IS
2531
2532 l_error_count NUMBER DEFAULT 0;
2533
2534 BEGIN
2535
2536 debug('validate_gt_contingencies()+');
2537
2538 -- this subroutine validates contingecies passed through invice api.
2539 -- at the moment only validation we are doing is to see that contingency
2540 -- passed exists.
2541
2542 -- we will not validate against start and end date until we expose
2543 -- the table.
2544
2545 INSERT INTO ar_trx_errors_gt
2546 (
2547 trx_header_id,
2548 trx_line_id,
2549 trx_contingency_id,
2550 error_message,
2551 invalid_value
2552 )
2553 SELECT
2554 lgt.trx_header_id,
2555 lgt.trx_line_id,
2556 cgt.trx_contingency_id,
2557 arp_standard.fnd_message('AR_RVMG_INVALID_CONTINGENCY'),
2558 cgt.contingency_id
2559 FROM ar_trx_lines_gt lgt,
2560 ar_trx_header_gt hgt,
2561 ar_trx_contingencies_gt cgt
2562 WHERE lgt.trx_header_id = hgt.trx_header_id
2563 AND cgt.trx_line_id = lgt.trx_line_id
2564 AND NOT EXISTS
2565 (
2566 SELECT 'valid lookup code'
2567 FROM ar_deferral_reasons l
2568 WHERE l.contingency_id = cgt.contingency_id
2569 );
2570
2571 l_error_count := SQL%ROWCOUNT;
2572 debug('contingency validation errors inserted: ' || l_error_count);
2573
2574 -- do not let users populate the expiration date if the event attribute
2575 -- and/or num of days is populated
2576
2577 /* 5026580 - Validation was testing all contingencies in
2578 interface table rather than just those paired with
2579 the target line. */
2580
2581 /* 5556360 - only raise this validation error for incomplete
2582 contingencies. We allow import with expiration_date
2583 on completed ones and use that date to set the
2584 event removal date accordingly */
2585
2586 INSERT INTO ar_trx_errors_gt
2587 (
2588 trx_header_id,
2589 trx_line_id,
2590 trx_contingency_id,
2591 error_message,
2592 invalid_value
2593 )
2594 SELECT
2595 lgt.trx_header_id,
2596 lgt.trx_line_id,
2597 cgt.trx_contingency_id,
2598 arp_standard.fnd_message('AR_RVMG_NO_EXP_DATE'),
2599 cgt.contingency_id
2600 FROM ar_trx_lines_gt lgt,
2601 ar_trx_header_gt hgt,
2602 ar_trx_contingencies_gt cgt,
2603 ar_deferral_reasons dr
2604 WHERE lgt.trx_header_id = hgt.trx_header_id
2605 AND cgt.trx_line_id = lgt.trx_line_id
2606 AND cgt.contingency_id = dr.contingency_id
2607 AND cgt.expiration_date IS NOT NULL
2608 AND dr.expiration_event_code IS NOT NULL
2609 AND NVL(cgt.completed_flag, 'N') = 'N';
2610
2611 l_error_count := SQL%ROWCOUNT;
2612 debug('contingency validation errors inserted: ' || l_error_count);
2613
2614 debug('validate_gt_contingencies()-');
2615
2616 RETURN l_error_count;
2617
2618 EXCEPTION
2619 WHEN NO_DATA_FOUND THEN
2620 debug('NO_DATA_FOUND: validate_gt_contingencies');
2621 debug(sqlerrm);
2622 RAISE;
2623
2624 WHEN OTHERS THEN
2625 debug('OTHERS: validate_gt_contingencies');
2626 debug(sqlerrm);
2627 RAISE;
2628
2629 END validate_gt_contingencies;
2630
2631
2632 FUNCTION validate_itf_contingencies (
2633 p_request_id NUMBER)
2634 RETURN NUMBER IS
2635
2636 l_error_count NUMBER DEFAULT 0;
2637
2638 BEGIN
2639
2640 debug('validate_itf_continencies()+');
2641 debug(' p_request_id : ' || p_request_id);
2642
2643 -- this subroutine validates contingecies passed through auto invoice.
2644 -- at the moment only validation we are doing is to see that contingency
2645 -- passed exists.
2646
2647 -- we will not validate against start and end date until we expose
2648 -- the table.
2649
2650 INSERT INTO ra_interface_errors
2651 (
2652 interface_line_id,
2653 interface_contingency_id,
2654 message_text,
2655 invalid_value,
2656 org_id
2657 )
2658 SELECT
2659 l.interface_line_id,
2660 c.interface_contingency_id,
2661 arp_standard.fnd_message('AR_RVMG_INVALID_CONTINGENCY'),
2662 c.contingency_id,
2663 l.org_id
2664 FROM ra_interface_lines l,
2665 ar_interface_conts c
2666 WHERE l.request_id = p_request_id
2667 AND c.interface_line_id = l.interface_line_id
2668 AND NOT EXISTS
2669 (
2670 SELECT 'valid lookup code'
2671 FROM ar_deferral_reasons l
2672 WHERE l.contingency_id = c.contingency_id
2673 );
2674
2675 l_error_count := SQL%ROWCOUNT;
2676 debug('validation errors inserted: ' || l_error_count);
2677 debug('validate_itf_continencies()-');
2678
2679 -- do not let users populate the expiration date if the event attribute
2680 -- and/or num of days is populated
2681
2682 /* 5026580 - validation was detecting any contingencies
2683 with dates (not restricted to only those for each line) */
2684
2685 /* 5556360 - Only raise this validation message for incomplete
2686 contingencies. Complete ones should bypass this as
2687 we use the expiration_date to populate the
2688 event removal date */
2689
2690 INSERT INTO ra_interface_errors
2691 (
2692 interface_line_id,
2693 interface_contingency_id,
2694 message_text,
2695 invalid_value,
2696 org_id
2697 )
2698 SELECT
2699 l.interface_line_id,
2700 c.interface_contingency_id,
2701 arp_standard.fnd_message('AR_RVMG_NO_EXP_DATE'),
2702 c.contingency_id,
2703 l.org_id
2704 FROM ra_interface_lines l,
2705 ar_interface_conts c,
2706 ar_deferral_reasons dr
2707 WHERE l.request_id = p_request_id
2708 AND c.interface_line_id = l.interface_line_id
2709 AND dr.contingency_id = c.contingency_id
2710 AND c.expiration_date IS NOT NULL
2711 AND dr.expiration_event_code IS NOT NULL
2712 AND NVL(c.completed_flag, 'N') = 'N';
2713
2714 l_error_count := SQL%ROWCOUNT;
2715 debug('contingency validation errors inserted: ' || l_error_count);
2716
2717 RETURN l_error_count;
2718
2719 EXCEPTION
2720 WHEN NO_DATA_FOUND THEN
2721 debug('NO_DATA_FOUND: validate_itf_contingencies');
2722 debug(sqlerrm);
2723 RAISE;
2724
2725 END validate_itf_contingencies;
2726
2727
2728 FUNCTION validate_contingencies(p_request_id NUMBER)
2729
2730 RETURN NUMBER IS
2731
2732 l_error_count NUMBER DEFAULT 0;
2733
2734 BEGIN
2735
2736 -- ths subroutine simply routes the validation to the correct helper
2737 -- routine depending on the source.
2738
2739 debug('validate_continencies()+');
2740
2741 IF (g_source = 'AR_INVOICE_API') THEN
2742 l_error_count := validate_gt_contingencies(p_request_id);
2743 ELSE
2744 l_error_count := validate_itf_contingencies(p_request_id);
2745 END IF;
2746
2747 RETURN l_error_count;
2748
2749 debug('validate_continencies()-');
2750
2751 END validate_contingencies;
2752
2753
2754 PROCEDURE get_base_currency_info IS
2755
2756 -- This cursor retrieves the functional currency details for the current
2757 -- set of books id. This is done once per session.
2758
2759 CURSOR currency IS
2760 SELECT c.currency_code,
2761 c.precision,
2762 c.minimum_accountable_unit
2763 FROM ar_system_parameters sysp,
2764 gl_sets_of_books sob,
2765 fnd_currencies c
2766 WHERE sob.set_of_books_id = sysp.set_of_books_id
2767 AND sob.currency_code = c.currency_code;
2768
2769 BEGIN
2770
2771 IF pg_debug IN ('Y', 'C') THEN
2772 debug('get_base_currency_info()+');
2773 END IF;
2774
2775 OPEN currency;
2776 FETCH currency INTO g_currency_code_f,
2777 g_precision_f,
2778 g_minimum_accountable_unit_f;
2779 CLOSE currency;
2780
2781 IF pg_debug IN ('Y', 'C') THEN
2782 debug('Functional Currency Code : ' || g_currency_code_f);
2783 debug(' Precision : ' || g_precision_f);
2784 debug(' accountable unit : ' ||
2785 g_minimum_accountable_unit_f);
2786 END IF;
2787
2788 IF pg_debug IN ('Y', 'C') THEN
2789 debug('get_base_currency_info()-');
2790 END IF;
2791
2792 EXCEPTION
2793
2794 WHEN NO_DATA_FOUND THEN
2795 debug('NO_DATA_FOUND: get_base_currency_info');
2796 debug(sqlerrm);
2797 RAISE;
2798
2799 WHEN OTHERS THEN
2800 debug('OTHERS: get_base_currency_info');
2801 debug(sqlerrm);
2802 RAISE;
2803
2804 END get_base_currency_info;
2805
2806
2807 PROCEDURE update_deferred_lines (
2808 p_customer_trx_id NUMBER DEFAULT NULL,
2809 p_customer_trx_line_id NUMBER DEFAULT NULL,
2810 p_line_status NUMBER DEFAULT NULL,
2811 p_manual_override VARCHAR2 DEFAULT NULL,
2812 p_amount_recognized NUMBER DEFAULT NULL,
2813 p_acctd_amount_recognized NUMBER DEFAULT NULL,
2814 p_amount_pending NUMBER DEFAULT NULL,
2815 p_acctd_amount_pending NUMBER DEFAULT NULL) IS
2816
2817 l_sysdate DATE;
2818 l_last_updated_by NUMBER;
2819 l_last_update_login NUMBER;
2820 l_line_collectible VARCHAR2(1) DEFAULT NULL;
2821
2822 BEGIN
2823
2824 -- This procedure simply updates a row of data in the
2825 -- ar_deferred_lines table. It will only update columns
2826 -- for which data is provided, the rest will retain their
2827 -- original values.
2828
2829 IF pg_debug IN ('Y', 'C') THEN
2830 debug('update_deferred_lines()+');
2831 debug('** update_deferred_lines parameters **');
2832 debug(' p_customer_trx_id : ' || p_customer_trx_id);
2833 debug(' p_customer_trx_line_id : ' ||
2834 p_customer_trx_line_id);
2835 END IF;
2836
2837 l_sysdate := trunc(sysdate);
2838 l_last_updated_by := arp_global.user_id;
2839 l_last_update_login := arp_global.last_update_login;
2840
2841 IF (p_line_status = c_recognizable) THEN
2842 l_line_collectible := 'Y';
2843 END IF;
2844
2845 IF (p_customer_trx_line_id IS NULL) THEN
2846
2847 IF pg_debug IN ('Y', 'C') THEN
2848 debug('p_customer_trx_line_id IS NULL');
2849 END IF;
2850
2851 UPDATE ar_deferred_lines
2852 SET line_collectible_flag = nvl(l_line_collectible,
2853 line_collectible_flag),
2854 manual_override_flag = nvl(p_manual_override,
2855 manual_override_flag),
2856 last_updated_by = l_last_updated_by,
2857 last_update_date = l_sysdate,
2858 last_update_login = l_last_update_login
2859 WHERE customer_trx_id = p_customer_trx_id;
2860
2861 ELSE
2862
2863 IF pg_debug IN ('Y', 'C') THEN
2864 debug('p_customer_trx_line_id IS NOT NULL');
2865 END IF;
2866
2867 UPDATE ar_deferred_lines
2868 SET line_collectible_flag = nvl(l_line_collectible,
2869 line_collectible_flag),
2870 manual_override_flag = nvl(p_manual_override,
2871 manual_override_flag),
2872 amount_recognized = nvl(p_amount_recognized,
2873 amount_recognized),
2874 acctd_amount_recognized = nvl(p_acctd_amount_recognized,
2875 acctd_amount_recognized),
2876 amount_pending = nvl(p_amount_pending, amount_pending),
2877 acctd_amount_pending = nvl(p_acctd_amount_pending,
2878 acctd_amount_pending),
2879 last_updated_by = l_last_updated_by,
2880 last_update_date = l_sysdate,
2881 last_update_login = l_last_update_login
2882 WHERE customer_trx_line_id = p_customer_trx_line_id;
2883
2884 END IF;
2885
2886 IF pg_debug IN ('Y', 'C') THEN
2887 debug('update_deferred_lines()-');
2888 END IF;
2889
2890 EXCEPTION
2891
2892 WHEN NO_DATA_FOUND THEN
2893 IF pg_debug IN ('Y', 'C') THEN
2894 debug('NO_DATA_FOUND: update_deferred_lines');
2895 debug(sqlerrm);
2896 END IF;
2897 RAISE;
2898
2899 WHEN OTHERS THEN
2900 IF pg_debug IN ('Y', 'C') THEN
2901 debug('OTHERS: update_deferred_lines');
2902 debug(sqlerrm);
2903 END IF;
2904 RAISE;
2905
2906 END update_deferred_lines;
2907
2908
2909 FUNCTION rule_based (p_customer_trx_id IN NUMBER)
2910 RETURN BOOLEAN IS
2911
2912 -- This cursor returns TRUE if there exists a invoicing rule
2913 -- for the invoice.
2914
2915 CURSOR c IS
2916 SELECT 1
2917 FROM ra_customer_trx rctl
2918 WHERE rctl.customer_trx_id = p_customer_trx_id
2919 AND invoicing_rule_id IS NOT NULL;
2920
2921 l_dummy_flag NUMBER;
2922 l_return_value BOOLEAN;
2923
2924 BEGIN
2925
2926 -- This function determined if the invoice in question has
2927 -- invoicing rules assocaited with it.
2928
2929 IF pg_debug IN ('Y', 'C') THEN
2930 debug('rule_based()+');
2931 debug('** rule_based parameter **');
2932 debug('rule_based: ' || ' p_customer_trx_id : ' ||
2933 p_customer_trx_id);
2934 END IF;
2935
2936 OPEN c;
2937 FETCH c INTO l_dummy_flag;
2938 l_return_value := c%FOUND;
2939 CLOSE c;
2940
2941 IF pg_debug IN ('Y', 'C') THEN
2942 debug('rule_based()-');
2943 END IF;
2944 RETURN l_return_value;
2945
2946 EXCEPTION
2947
2948 WHEN NO_DATA_FOUND THEN
2949 IF pg_debug IN ('Y', 'C') THEN
2950 debug('NO_DATA_FOUND: rule_based');
2951 debug(sqlerrm);
2952 END IF;
2953 RAISE;
2954
2955 WHEN OTHERS THEN
2956 IF pg_debug IN ('Y', 'C') THEN
2957 debug('OTHERS: rule_based');
2958 debug(sqlerrm);
2959 END IF;
2960 RAISE;
2961
2962 END rule_based;
2963
2964
2965 FUNCTION distributions_created (p_customer_trx_id IN NUMBER)
2966 RETURN BOOLEAN IS
2967
2968 -- This cursor returns TRUE if the distributions have been created
2969 -- for the invoice.
2970
2971 CURSOR c IS
2972 SELECT 1
2973 FROM ra_cust_trx_line_gl_dist
2974 WHERE customer_trx_id = p_customer_trx_id
2975 AND account_set_flag = 'N'
2976 AND rownum = 1;
2977
2978 l_dummy_flag NUMBER;
2979 l_return_value BOOLEAN;
2980
2981 BEGIN
2982
2983 -- This function determines if the revenue recognition has run
2984 -- called for this invoices with rules to create the distributions.
2985
2986 IF pg_debug IN ('Y', 'C') THEN
2987 debug('distributions_created()+');
2988 debug('** distributions_created parameter **');
2989 debug(' p_customer_trx_id : ' || p_customer_trx_id);
2990 END IF;
2991
2992 OPEN c;
2993 FETCH c INTO l_dummy_flag;
2994 l_return_value := c%FOUND;
2995 CLOSE c;
2996
2997 IF pg_debug IN ('Y', 'C') THEN
2998 debug('distributions_created()-');
2999 END IF;
3000
3001 RETURN l_return_value;
3002
3003 EXCEPTION
3004
3005 WHEN NO_DATA_FOUND THEN
3006 IF pg_debug IN ('Y', 'C') THEN
3007 debug('NO_DATA_FOUND: distributions_created');
3008 debug(sqlerrm);
3009 END IF;
3010 RAISE;
3011
3012 WHEN OTHERS THEN
3013 IF pg_debug IN ('Y', 'C') THEN
3014 debug('OTHERS: distributions_created');
3015 debug(sqlerrm);
3016 END IF;
3017 RAISE;
3018
3019 END distributions_created;
3020
3021
3022 FUNCTION monitored_transaction (p_customer_trx_id IN NUMBER)
3023 RETURN BOOLEAN IS
3024
3025 -- This cursor checks to see if the invoice
3026 -- was analyzed by the revenue management engine.
3027
3028 CURSOR monitored_txn IS
3029 SELECT 1
3030 FROM ar_deferred_lines
3031 WHERE customer_trx_id = p_customer_trx_id
3032 AND manual_override_flag = 'N'
3033 AND line_collectible_flag = 'N';
3034
3035 l_dummy_flag NUMBER;
3036 l_return_value BOOLEAN;
3037
3038 BEGIN
3039
3040 IF pg_debug IN ('Y', 'C') THEN
3041 debug('monitored_transaction()+');
3042 debug('** monitored_transaction parameter **');
3043 debug(' p_customer_trx_id : ' || p_customer_trx_id);
3044 END IF;
3045
3046 OPEN monitored_txn;
3047 FETCH monitored_txn INTO l_dummy_flag;
3048
3049 IF monitored_txn%FOUND THEN
3050 IF pg_debug IN ('Y', 'C') THEN
3051 debug ('RAM-C Transaction');
3052 END IF;
3053 CLOSE monitored_txn;
3054 IF pg_debug IN ('Y', 'C') THEN
3055 debug('monitored_transaction()-');
3056 END IF;
3057 RETURN TRUE;
3058 END IF;
3059 CLOSE monitored_txn;
3060
3061 IF pg_debug IN ('Y', 'C') THEN
3062 debug ('Not a monitored transaction');
3063 debug('monitored_transaction()-');
3064 END IF;
3065
3066 RETURN FALSE;
3067
3068 EXCEPTION
3069
3070 WHEN NO_DATA_FOUND THEN
3071 IF pg_debug IN ('Y', 'C') THEN
3072 debug('NO_DATA_FOUND: monitored_transaction');
3073 debug(sqlerrm);
3074 END IF;
3075 RAISE;
3076
3077 WHEN OTHERS THEN
3078 IF pg_debug IN ('Y', 'C') THEN
3079 debug('OTHERS: ramc_transcation');
3080 debug(sqlerrm);
3081 END IF;
3082 RAISE;
3083
3084 END monitored_transaction;
3085
3086
3087 PROCEDURE manual_override (
3088 p_customer_trx_id NUMBER,
3089 p_customer_trx_line_id NUMBER DEFAULT NULL) IS
3090
3091 l_sysdate DATE;
3092 l_user_id NUMBER;
3093
3094 BEGIN
3095
3096 --------------------------------------------------------------------------
3097 -- This procedure updates the manual_oveeride column in the
3098 -- ar_deferred_lines table to indicate that this line or transction
3099 -- has been manually manipulated by user in the RAM screens. As a result
3100 -- of this update, the revenue management engine will not keep track of
3101 -- this line anymore.
3102 ---------------------------------------------------------------------------
3103
3104 IF pg_debug IN ('Y', 'C') THEN
3105 debug('manual_override()+');
3106 debug('** manual_override parameters **');
3107 debug(' p_customer_trx_id : ' || p_customer_trx_id);
3108 debug(' p_customer_trx_line_id : ' ||
3109 p_customer_trx_line_id);
3110 END IF;
3111
3112 l_sysdate := trunc(sysdate);
3113 l_user_id := fnd_global.user_id;
3114
3115 IF (p_customer_trx_line_id IS NULL) THEN
3116
3117 IF pg_debug IN ('Y', 'C') THEN
3118 debug('Manual RAM adjustments done to the entire txn');
3119 END IF;
3120
3121 update_deferred_lines (
3122 p_customer_trx_id => p_customer_trx_id,
3123 p_manual_override => 'Y');
3124
3125 ELSE
3126
3127 IF pg_debug IN ('Y', 'C') THEN
3128 debug('Manual RAM adjustments done to a specific line');
3129 END IF;
3130
3131 update_deferred_lines (
3132 p_customer_trx_line_id => p_customer_trx_line_id,
3133 p_manual_override => 'Y');
3134
3135 END IF;
3136
3137 IF pg_debug IN ('Y', 'C') THEN
3138 debug('manual_override()-');
3139 END IF;
3140
3141 EXCEPTION
3142
3143 WHEN NO_DATA_FOUND THEN
3144 IF pg_debug IN ('Y', 'C') THEN
3145 debug('NO_DATA_FOUND: manual_override');
3146 debug(sqlerrm);
3147 END IF;
3148 RAISE;
3149
3150 WHEN OTHERS THEN
3151 IF pg_debug IN ('Y', 'C') THEN
3152 debug('OTHERS: manual_override');
3153 debug(sqlerrm);
3154 END IF;
3155 RAISE;
3156
3157 END manual_override;
3158
3159
3160 PROCEDURE update_for_event (
3161 p_cust_trx_line_id IN NUMBER,
3162 p_event_date IN DATE,
3163 p_event_code IN VARCHAR2) IS
3164
3165 l_user_id NUMBER;
3166 l_dummy NUMBER;
3167 l_revrec_event_code VARCHAR2(30);
3168 l_expiration_event_code VARCHAR2(30);
3169
3170 -- select the contingencies for this line which
3171 -- was waiting for this event.
3172 CURSOR conts IS
3173 SELECT dr.contingency_id, revrec_event_code, expiration_event_code
3174 FROM ar_line_conts lc,
3175 ar_deferral_reasons dr
3176 WHERE lc.contingency_id = dr.contingency_id
3177 AND lc.customer_trx_line_id = p_cust_trx_line_id
3178 AND (dr.revrec_event_code = p_event_code OR
3179 dr.expiration_event_code = p_event_code);
3180
3181 BEGIN
3182
3183 IF pg_debug IN ('Y', 'C') THEN
3184 debug('update_for_event()+');
3185 debug('** update_for_event parameters **');
3186 debug(' p_cust_trx_line_id : ' ||
3187 p_cust_trx_line_id);
3188 debug(' p_event_code: ' || p_event_code);
3189 END IF;
3190
3191 -- if we reach here that means, we do care about this event.
3192
3193 l_user_id := fnd_global.user_id;
3194
3195 FOR cont_rec IN conts LOOP
3196
3197 /* 5530037 - Revised logic to handle both
3198 expiration_event_code and revrec_event_code
3199 events. Specifically, we removed the exclusionary
3200 IF/ELSIF logic
3201
3202 DEV NOTE: the expiration_date logic simply
3203 insures that the expiration_date is set correctly
3204 for contingencies that have PROOF_OF_DELIVERY
3205 for their alternate (time-based) expirations.
3206
3207 While it is possible to have 'PROOF_OF_DELIVERY' for
3208 both expiration_event_code and revrec_event_code, the act
3209 of POD would complete the contingency
3210 immediately and the expiration_date would be meaningless.
3211
3212 The original logic made the expiration and revrec events
3213 mutually exclusive where the design clearly intended to
3214 allow them together.
3215
3216 Please note that I also added code to populate
3217 expiration_event_date in cases where it is the
3218 expiration_event_code activity that is happening. This
3219 will insure that the expiration_event_date is always
3220
3221 */
3222
3223 UPDATE ar_line_conts
3224 SET expiration_date =
3225 DECODE(cont_rec.expiration_event_code, p_event_code,
3226 NVL(p_event_date + expiration_days,expiration_date),
3227 expiration_date),
3228 expiration_event_date =
3229 DECODE(cont_rec.expiration_event_code, p_event_code,
3230 NVL(p_event_date,
3231 NVL(expiration_date - expiration_days,
3232 expiration_date))),
3233 completed_flag =
3234 DECODE(cont_rec.revrec_event_code, p_event_code,'Y',
3235 completed_flag),
3236 completed_by =
3237 DECODE(cont_rec.revrec_event_code, p_event_code,
3238 fnd_global.user_id, completed_by),
3239 reason_removal_date =
3240 DECODE(cont_rec.revrec_event_code, p_event_code,
3241 sysdate, reason_removal_date),
3242 last_updated_by = l_user_id,
3243 last_update_date = sysdate,
3244 last_update_login = l_user_id
3245 WHERE customer_trx_line_id = p_cust_trx_line_id
3246 AND contingency_id = cont_rec.contingency_id;
3247
3248 END LOOP;
3249
3250 IF pg_debug IN ('Y', 'C') THEN
3251 debug('update_for_event()-');
3252 END IF;
3253
3254 EXCEPTION
3255
3256 WHEN NO_DATA_FOUND THEN
3257 IF pg_debug IN ('Y', 'C') THEN
3258 debug('NO_DATA_FOUND: update_for_event');
3259 debug(sqlerrm);
3260 END IF;
3261 RAISE;
3262
3263 WHEN OTHERS THEN
3264 IF pg_debug IN ('Y', 'C') THEN
3265 debug('OTHERS: update_for_event');
3266 debug(sqlerrm);
3267 END IF;
3268 RAISE;
3269
3270 END update_for_event;
3271
3272
3273 PROCEDURE adjust_revenue (
3274 p_mode IN VARCHAR2 DEFAULT c_earn_revenue,
3275 p_customer_trx_id IN NUMBER,
3276 p_customer_trx_line_id IN NUMBER,
3277 p_acctd_amount IN NUMBER,
3278 p_gl_date IN DATE DEFAULT NULL,
3279 p_comments IN VARCHAR2 DEFAULT NULL,
3280 p_ram_desc_flexfield IN desc_flexfield,
3281 p_rev_adj_rec IN ar_revenue_adjustment_pvt.rev_adj_rec_type,
3282 p_delta_amount IN NUMBER DEFAULT 0,
3283 p_acctd_delta_amount IN NUMBER DEFAULT 0,
3284 x_adjustment_number OUT NOCOPY NUMBER,
3285 x_return_status OUT NOCOPY VARCHAR2,
3286 x_msg_count OUT NOCOPY NUMBER,
3287 x_msg_data OUT NOCOPY VARCHAR2) IS
3288
3289 l_api_version NUMBER := 2.0;
3290 l_init_msg_list VARCHAR2(30) DEFAULT fnd_api.g_true;
3291 l_commit VARCHAR2(30) DEFAULT FND_API.G_FALSE;
3292
3293 l_sysdate DATE;
3294 l_user_id NUMBER;
3295
3296 l_line_count NUMBER;
3297 l_status NUMBER;
3298
3299 l_adjustable_amount NUMBER DEFAULT 0;
3300 l_acctd_adjustable_amount NUMBER DEFAULT 0;
3301
3302 l_item_key wf_items.ITEM_KEY%TYPE;
3303 l_adjustment_id ar_adjustments.adjustment_id%TYPE;
3304 l_adjustment_number ar_adjustments.adjustment_number%TYPE;
3305
3306 l_rev_adj_rec ar_revenue_adjustment_pvt.rev_adj_rec_type;
3307
3308 BEGIN
3309
3310 /*------------------------------------------------------------------------
3311 | This procedure is a wrapper for RAM apis and also raises buisness events.
3312 | Amount in invoice currrency is passed as part of the p_rev_adj_rec record.
3313 | But, the accounted amoutn is not passed in the record, so that needs to
3314 | passed explicitly.
3315 +------------------------------------------------------------------------*/
3316
3317 IF pg_debug IN ('Y', 'C') THEN
3318 debug('adjust_revenue()+');
3319 debug('** adjust_revenue parameters **');
3320 debug(' p_mode : ' || p_mode);
3321 debug(' p_gl_date : ' || p_gl_date);
3322 debug(' p_customer_trx_id : ' || p_customer_trx_id);
3323 debug(' p_customer_trx_line_id : ' ||
3324 p_customer_trx_line_id);
3325 debug(' p_amount : ' || p_rev_adj_rec.amount);
3326 debug(' p_acctd_amount : ' || p_acctd_amount);
3327 debug(' p_delta_amount : ' || p_delta_amount);
3328 debug(' p_acctd_delta_amount : ' || p_acctd_delta_amount);
3329 debug(' p_sales_credit_type : ' || p_rev_adj_rec.sales_credit_type);
3330 END IF;
3331
3332 /* 7569247 - removed zero amount check.. we need to let zero
3333 amount adjustments through so the lines will register
3334 for COGS */
3335
3336 l_sysdate := trunc(sysdate);
3337
3338 l_rev_adj_rec := p_rev_adj_rec;
3339
3340 l_rev_adj_rec.source := c_revenue_management_source;
3341
3342 l_rev_adj_rec.attribute1 := p_ram_desc_flexfield.attribute1;
3343 l_rev_adj_rec.attribute2 := p_ram_desc_flexfield.attribute2;
3344 l_rev_adj_rec.attribute3 := p_ram_desc_flexfield.attribute3;
3345 l_rev_adj_rec.attribute4 := p_ram_desc_flexfield.attribute4;
3346 l_rev_adj_rec.attribute5 := p_ram_desc_flexfield.attribute5;
3347 l_rev_adj_rec.attribute6 := p_ram_desc_flexfield.attribute6;
3348 l_rev_adj_rec.attribute7 := p_ram_desc_flexfield.attribute7;
3349 l_rev_adj_rec.attribute8 := p_ram_desc_flexfield.attribute8;
3350 l_rev_adj_rec.attribute9 := p_ram_desc_flexfield.attribute9;
3351 l_rev_adj_rec.attribute10 := p_ram_desc_flexfield.attribute10;
3352 l_rev_adj_rec.attribute11 := p_ram_desc_flexfield.attribute11;
3353 l_rev_adj_rec.attribute12 := p_ram_desc_flexfield.attribute12;
3354 l_rev_adj_rec.attribute13 := p_ram_desc_flexfield.attribute13;
3355 l_rev_adj_rec.attribute14 := p_ram_desc_flexfield.attribute14;
3356 l_rev_adj_rec.attribute15 := p_ram_desc_flexfield.attribute15;
3357
3358 l_rev_adj_rec.attribute_category := p_ram_desc_flexfield.attribute_category;
3359
3360 l_rev_adj_rec.comments := p_comments;
3361
3362 IF (p_gl_date IS NOT NULL) THEN
3363 l_rev_adj_rec.gl_date := p_gl_date;
3364 ELSE
3365 l_rev_adj_rec.gl_date := (l_sysdate);
3366 END IF;
3367
3368 -- *** Being called in earned mode ***
3369 IF (p_mode = c_earn_revenue) THEN
3370
3371 IF pg_debug IN ('Y', 'C') THEN
3372 debug('RAM being called in EARN mode');
3373 END IF;
3374
3375 IF (rule_based(p_customer_trx_id) AND
3376 NOT distributions_created(p_customer_trx_id)) THEN
3377
3378 IF pg_debug IN ('Y', 'C') THEN
3379 debug ('revenue recognition has not run for this txn');
3380 END IF;
3381
3382 -- call to the concurrent program
3383 l_status := arp_auto_rule.create_distributions(
3384 p_commit => 'N',
3385 p_debug => 'N',
3386 p_trx_id => p_customer_trx_id);
3387
3388 IF pg_debug IN ('Y', 'C') THEN
3389 debug ('revenue recognition done');
3390 END IF;
3391
3392 END IF;
3393
3394 ar_raapi_util.constant_system_values;
3395
3396 l_adjustable_amount := ar_raapi_util.adjustable_revenue (
3397 p_customer_trx_line_id => p_customer_trx_line_id,
3398 p_adjustment_type => 'EA',
3399 p_customer_trx_id => p_customer_trx_id,
3400 p_salesrep_id => NULL,
3401 p_sales_credit_type => NULL,
3402 p_item_id => NULL,
3403 p_category_id => NULL,
3404 p_revenue_adjustment_id => NULL,
3405 p_line_count_out => l_line_count,
3406 p_acctd_amount_out => l_acctd_adjustable_amount);
3407
3408 IF (l_acctd_adjustable_amount IS NULL) THEN
3409 l_acctd_adjustable_amount := 0;
3410 END IF;
3411
3412 IF pg_debug IN ('Y', 'C') THEN
3413 debug('adjust_revenue - amount adjustable: ' ||
3414 l_adjustable_amount);
3415 debug('adjust_revenue - acctd amount adjustable: ' ||
3416 l_acctd_adjustable_amount);
3417 END IF;
3418 /*6157033 changed condition to avoid error in case of
3419 -ve adjustments with negative invoices*/
3420 IF (ABS(l_adjustable_amount) < ABS(l_rev_adj_rec.amount)) THEN
3421
3422 ------------------------------------------------------------------------
3423 -- There must have been some credit memos that were applied
3424 -- to this invoice. So, we can not recognize the computed
3425 -- amount, instead we should recognize only adjustable amount.
3426 -- Hence update ar_deferred_lines table by taking out the
3427 -- initial amount and then adding the adjustable amount.
3428 -----------------------------------------------------------------------
3429
3430 IF pg_debug IN ('Y', 'C') THEN
3431 debug('Adjustable amount is less the computed amount');
3432 END IF;
3433
3434 -- The reason we are subtracting the l_rev_adj_rec.amount from the
3435 -- recognized amount is because that is what has been added to
3436 -- l_rev_adj_rec.amount before coming to this subroutine. So, must take
3437 -- that out before adding the adjustable amount.
3438
3439
3440 /* 6008164 - This code actually causes amount_recognized to
3441 be incorrect for inv+adj+rec case. In my case, the amounts
3442 were 100 - 102.61 + 100 = 97.39 when the rev_adj should have been
3443 for $100. So I think l_rev_adj_rec.amount is wrong */
3444 /* 6157033 used delta amount passed to calculate correct amount
3445 to be recogonied*/
3446 UPDATE ar_deferred_lines
3447 SET amount_recognized = amount_recognized + p_delta_amount -
3448 l_rev_adj_rec.amount +
3449 l_adjustable_amount,
3450 acctd_amount_recognized = acctd_amount_recognized + p_acctd_delta_amount -
3451 p_acctd_amount +
3452 l_acctd_adjustable_amount
3453 WHERE customer_trx_line_id = p_customer_trx_line_id;
3454
3455 l_rev_adj_rec.amount := l_adjustable_amount;
3456
3457 END IF;
3458
3459 IF pg_debug IN ('Y', 'C') THEN
3460 debug(l_rev_adj_rec.amount ||
3461 ' Being Earned For Customer Trx ID ' || p_customer_trx_id ||
3462 ' Line ID ' || p_customer_trx_line_id);
3463 END IF;
3464
3465 /* 5462746 - The Sweeper may attempt to earn revenue
3466 twice -- once via the record_acceptance code and again
3467 in the actual sweeper code. The second call fails
3468 if the amount is zero. */
3469 IF pg_debug IN ('Y', 'C')
3470 THEN
3471 debug('trx_id = ' || p_customer_trx_id || ' amount = ' ||
3472 l_rev_adj_rec.amount);
3473 END IF;
3474
3475 /* 7569247 - Pushed earn_revenue call outside of IF amount <> 0
3476 case. All adjustments, even zero ones must go through
3477 in order to work for COGS */
3478
3479 ar_revenueadjust_pub.earn_revenue(
3480 p_api_version => l_api_version,
3481 p_init_msg_list => l_init_msg_list,
3482 p_rev_adj_rec => l_rev_adj_rec,
3483 x_return_status => x_return_status,
3484 x_msg_count => x_msg_count,
3485 x_msg_data => x_msg_data,
3486 x_adjustment_id => l_adjustment_id,
3487 x_adjustment_number => x_adjustment_number);
3488
3489 -- *** Being called in un-earn mode ***
3490 ELSE
3491
3492 IF pg_debug IN ('Y', 'C') THEN
3493 debug('RAM being called in UN-EARN mode');
3494 debug(l_rev_adj_rec.amount ||
3495 ' UnEarned For Customer Trx ID ' || p_customer_trx_id ||
3496 ' Line ID ' || p_customer_trx_line_id);
3497 END IF;
3498
3499 ar_revenueadjust_pub.unearn_revenue(
3500 p_api_version => l_api_version,
3501 p_init_msg_list => l_init_msg_list,
3502 p_rev_adj_rec => l_rev_adj_rec,
3503 x_return_status => x_return_status,
3504 x_msg_count => x_msg_count,
3505 x_msg_data => x_msg_data,
3506 x_adjustment_id => l_adjustment_id,
3507 x_adjustment_number => x_adjustment_number);
3508
3509 END IF;
3510
3511
3512 IF x_return_status = fnd_api.g_ret_sts_success THEN
3513
3514 IF pg_debug IN ('Y', 'C') THEN
3515 debug('Call To RAM API successful');
3516 END IF;
3517
3518 ELSE
3519
3520 IF pg_debug IN ('Y', 'C') THEN
3521 debug('RME encountered an ERROR with RAM!');
3522 debug(' p_customer_trx_id : ' || p_customer_trx_id);
3523 debug(' p_customer_trx_line_id : ' ||
3524 p_customer_trx_line_id);
3525 END IF;
3526
3527 fnd_msg_pub.get(fnd_msg_pub.g_first, fnd_api.g_false,
3528 x_msg_data, x_msg_count);
3529
3530 IF pg_debug IN ('Y', 'C') THEN
3531 debug('Error Reported By RAM API: ' || x_msg_data);
3532 END IF;
3533
3534 fnd_message.set_name ('AR','GENERIC_MESSAGE');
3535 fnd_message.set_token('GENERIC_TEXT', x_msg_data);
3536 app_exception.raise_exception;
3537
3538 END IF;
3539
3540 IF pg_debug IN ('Y', 'C') THEN
3541 debug('adjustment number: ' || l_adjustment_number);
3542 debug('adjustment id: ' || l_adjustment_id);
3543 debug('adjust_revenue()-');
3544 END IF;
3545
3546 EXCEPTION
3547
3548 WHEN NO_DATA_FOUND THEN
3549 IF pg_debug IN ('Y', 'C') THEN
3550 debug('NO_DATA_FOUND: adjust_revenue');
3551 debug(sqlerrm);
3552 END IF;
3553 RAISE;
3554
3555 WHEN OTHERS THEN
3556 IF pg_debug IN ('Y', 'C') THEN
3557 debug('OTHERS: adjust_revenue');
3558 debug(sqlerrm);
3559 END IF;
3560 RAISE;
3561
3562 END adjust_revenue;
3563
3564
3565 FUNCTION creditworthy (
3566 p_customer_account_id IN NUMBER,
3567 p_customer_site_use_id IN NUMBER)
3568 RETURN NUMBER IS
3569
3570 -- This cursor retrives the party id for a customer account id.
3571 CURSOR cust_party IS
3572 SELECT party_id
3573 FROM hz_cust_accounts
3574 WHERE cust_account_id = p_customer_account_id;
3575
3576 -- This cursor retrived credit classification at site level
3577 CURSOR site (p_party_id NUMBER, p_account_id NUMBER, p_site_use_id NUMBER )IS
3578 SELECT credit_classification
3579 FROM hz_customer_profiles
3580 WHERE party_id = p_party_id
3581 AND cust_account_id = p_account_id
3582 AND site_use_id = p_site_use_id;
3583
3584 -- This cursor retrives credit classification at account level
3585 CURSOR account (p_party_id NUMBER, p_account_id NUMBER )IS
3586 SELECT credit_classification
3587 FROM hz_customer_profiles
3588 WHERE party_id = p_party_id
3589 AND cust_account_id = p_account_id
3590 AND site_use_id IS NULL;
3591
3592 -- This cursor retrives credit classification at party level
3593 CURSOR party (p_party_id NUMBER) IS
3594 SELECT credit_classification
3595 FROM hz_customer_profiles
3596 WHERE party_id = p_party_id
3597 AND cust_account_id = -1;
3598
3599 -- This cursor traverses the party hierarchy.
3600 CURSOR party_hierarchy (p_child_id IN NUMBER) IS
3601 SELECT parent_id
3602 FROM hz_hierarchy_nodes
3603 WHERE child_id = p_child_id
3604 AND parent_table_name = 'HZ_PARTIES'
3605 AND parent_object_type = 'ORGANIZATION'
3606 AND hierarchy_type = 'CREDIT'
3607 AND level_number > 0
3608 AND effective_start_date <= trunc(sysdate)
3609 AND effective_end_date >= trunc(sysdate);
3610
3611 -- This cursor retrieves credit classifiction for parties
3612 -- in party hierarchy.
3613 CURSOR parent (p_party_id NUMBER) IS
3614 SELECT credit_classification
3615 FROM hz_customer_profiles
3616 WHERE party_id = p_party_id
3617 AND cust_account_id = -1
3618 AND site_use_id IS NULL;
3619
3620 l_verdict NUMBER DEFAULT collect;
3621 l_party_id hz_cust_accounts.party_id%TYPE;
3622 l_parent_id hz_cust_accounts.party_id%TYPE;
3623
3624 l_credit_classification
3625 ar_system_parameters.credit_classification1%TYPE;
3626
3627 BEGIN
3628
3629 -- This subroutine computes the credit classification.
3630
3631 IF pg_debug IN ('Y', 'C') THEN
3632 debug('creditworthy()+');
3633 debug('** creditworthy parameters **');
3634 debug(' p_customer_account_id : ' ||
3635 p_customer_account_id);
3636 debug(' p_customer_site_use_id : ' ||
3637 p_customer_site_use_id);
3638 END IF;
3639
3640 IF NOT g_credit_class_tbl.EXISTS (p_customer_site_use_id) THEN
3641
3642 OPEN cust_party;
3643 FETCH cust_party INTO l_party_id;
3644 CLOSE cust_party;
3645
3646
3647 -- find out if a credit classificaion exist for bill to site, account
3648 -- or party level.
3649
3650 IF pg_debug IN ('Y', 'C') THEN
3651 debug('Party ID: ' || l_party_id);
3652 END IF;
3653
3654 --------------------------------------------------------------------------
3655 -- This following logic retrives the classifcation for a customer. First
3656 -- First it looks to see if the classification is stored at the site level
3657 -- if it is not there then it looks at the account layer, and if it does
3658 -- not find it there it looks at the party level to see if it find a
3659 -- classifcation there.
3660 -------------------------------------------------------------------------
3661
3662 IF pg_debug IN ('Y', 'C') THEN
3663 debug('Looking at site, account, party for classification');
3664 END IF;
3665
3666 OPEN site(l_party_id, p_customer_account_id, p_customer_site_use_id);
3667 FETCH site INTO l_credit_classification;
3668 CLOSE site;
3669
3670 IF l_credit_classification IS NULL THEN
3671
3672 IF pg_debug IN ('Y', 'C') THEN
3673 debug('(site) no credit classification');
3674 END IF;
3675
3676 OPEN account(l_party_id, p_customer_account_id);
3677 FETCH account INTO l_credit_classification;
3678 CLOSE account;
3679
3680 END IF;
3681
3682 IF l_credit_classification IS NULL THEN
3683
3684 IF pg_debug IN ('Y', 'C') THEN
3685 debug('(account) no credit classification');
3686 END IF;
3687
3688 OPEN party(l_party_id);
3689 FETCH party INTO l_credit_classification;
3690 CLOSE party;
3691
3692 END IF;
3693
3694 IF l_credit_classification IS NULL THEN
3695
3696 IF pg_debug IN ('Y', 'C') THEN
3697 debug('(party) no credit classification');
3698 END IF;
3699
3700 -------------------------------------------------------------------------
3701 -- no credit classification was found for bill to site, account
3702 -- or party level. So, now we have to look for it in the party
3703 -- hierarchy.
3704 --
3705 -- The following sql is used to to retrieve a classification by
3706 -- traversing the party hierarchy. This sql will be executed for
3707 -- each party in the hierarchy.
3708 ------------------------------------------------------------------------
3709
3710 OPEN party_hierarchy(l_party_id);
3711
3712 LOOP
3713 FETCH party_hierarchy INTO l_parent_id;
3714 EXIT WHEN party_hierarchy%NOTFOUND;
3715
3716 OPEN parent(l_parent_id);
3717 FETCH parent INTO l_credit_classification;
3718 CLOSE parent;
3719
3720 IF pg_debug IN ('Y', 'C') THEN
3721 debug('Parent Party ID: ' || l_party_id);
3722 END IF;
3723
3724 IF l_credit_classification IS NOT NULL THEN
3725 IF pg_debug IN ('Y', 'C') THEN
3726 debug('(parent) - classification : ' ||
3727 l_credit_classification);
3728 END IF;
3729 EXIT;
3730 END IF;
3731 END LOOP;
3732
3733 CLOSE party_hierarchy;
3734
3735 END IF; -- l_credit_classification IS NULL
3736
3737 g_credit_class_tbl(p_customer_site_use_id) := l_credit_classification;
3738
3739 END IF; -- NOT EXISTS
3740
3741
3742 IF pg_debug IN ('Y', 'C') THEN
3743 debug('credit Classification: ' || l_credit_classification);
3744 debug('creditworthy()-');
3745 END IF;
3746
3747 IF g_credit_class_tbl(p_customer_site_use_id) IN (
3748 arp_standard.sysparm.credit_classification1,
3749 arp_standard.sysparm.credit_classification2,
3750 arp_standard.sysparm.credit_classification3) THEN
3751
3752 l_verdict := defer;
3753
3754 ELSE
3755
3756 l_verdict := collect;
3757
3758 END IF;
3759
3760 RETURN l_verdict;
3761
3762
3763 EXCEPTION
3764
3765 WHEN NO_DATA_FOUND THEN
3766 IF pg_debug IN ('Y', 'C') THEN
3767 debug('NO_DATA_FOUND: creditworthy');
3768 debug(sqlerrm);
3769 END IF;
3770 RETURN NULL;
3771
3772 WHEN OTHERS THEN
3773 IF pg_debug IN ('Y', 'C') THEN
3774 debug(' creditworthy');
3775 debug(sqlerrm);
3776 END IF;
3777 RETURN NULL;
3778
3779 END creditworthy;
3780
3781
3782 FUNCTION get_total_application(
3783 p_customer_trx_id IN NUMBER)
3784 RETURN NUMBER IS
3785
3786 -- This cursor retrieves the toal receipts applied so far
3787 -- to an invoice.
3788
3789 CURSOR total_app IS
3790 SELECT sum(amount_recognized) + sum(amount_pending)
3791 FROM ar_deferred_lines
3792 WHERE customer_trx_id = p_customer_trx_id;
3793
3794 l_total_application ar_deferred_lines.amount_recognized%TYPE;
3795
3796 BEGIN
3797
3798 -- This functions gets total receipt amount applied against
3799 -- the invoice.
3800
3801 IF pg_debug IN ('Y', 'C') THEN
3802 debug('get_total_application()+');
3803 debug('** get_total_application parameters **');
3804 debug(' p_customer_trx_id : ' || p_customer_trx_id);
3805 END IF;
3806
3807 OPEN total_app;
3808 FETCH total_app INTO l_total_application;
3809 CLOSE total_app;
3810
3811 IF pg_debug IN ('Y', 'C') THEN
3812 debug('get_total_application()-');
3813 END IF;
3814
3815 RETURN l_total_application;
3816
3817 EXCEPTION
3818
3819 WHEN NO_DATA_FOUND THEN
3820 IF pg_debug IN ('Y', 'C') THEN
3821 debug('NO_DATA_FOUND: get_total_application');
3822 debug(sqlerrm);
3823 END IF;
3824 RAISE;
3825
3826 WHEN OTHERS THEN
3827 IF pg_debug IN ('Y', 'C') THEN
3828 debug('OTHERS: get_total_application');
3829 debug(sqlerrm);
3830 END IF;
3831 RAISE;
3832
3833 END get_total_application;
3834
3835
3836 FUNCTION get_acctd_total_application(
3837 p_customer_trx_id IN NUMBER)
3838 RETURN NUMBER IS
3839
3840 -- This cursor retrieves the toal receipts applied so far
3841 -- to an invoice in functional currency.
3842
3843 CURSOR acctd_total_app IS
3844 SELECT sum(acctd_amount_recognized) + sum(acctd_amount_pending)
3845 FROM ar_deferred_lines
3846 WHERE customer_trx_id = p_customer_trx_id;
3847
3848 l_acctd_total_application ar_deferred_lines.amount_recognized%TYPE;
3849
3850 BEGIN
3851
3852 -- This functions gets total accounted amount receipt amount applied against
3853 -- the invoice.
3854
3855 IF pg_debug IN ('Y', 'C') THEN
3856 debug('get_acctd_total_application()+');
3857 debug('** get_acctd_total_application parameters **');
3858 debug(' p_customer_trx_id : ' || p_customer_trx_id);
3859 END IF;
3860
3861 OPEN acctd_total_app;
3862 FETCH acctd_total_app INTO l_acctd_total_application;
3863 CLOSE acctd_total_app;
3864
3865 IF pg_debug IN ('Y', 'C') THEN
3866 debug('get_acctd_total_application()-');
3867 END IF;
3868
3869 RETURN l_acctd_total_application;
3870
3871 EXCEPTION
3872
3873 WHEN NO_DATA_FOUND THEN
3874 IF pg_debug IN ('Y', 'C') THEN
3875 debug('NO_DATA_FOUND: get_acctd_total_application');
3876 debug(sqlerrm);
3877 END IF;
3878 RAISE;
3879
3880 WHEN OTHERS THEN
3881 IF pg_debug IN ('Y', 'C') THEN
3882 debug('OTHERS: get_acctd_total_application');
3883 debug(sqlerrm);
3884 END IF;
3885 RAISE;
3886
3887 END get_acctd_total_application;
3888
3889
3890 FUNCTION compute_line_amount (
3891 p_mode IN NUMBER,
3892 p_amount_previously_applied IN NUMBER,
3893 p_current_amount_applied IN NUMBER,
3894 p_line_balance_orig IN NUMBER,
3895 p_currency_code IN VARCHAR2,
3896 p_sum_of_all_lines IN NUMBER,
3897 p_current_line_balance IN NUMBER,
3898 p_running_lines_balance IN OUT NOCOPY NUMBER,
3899 p_running_allocated_balance IN OUT NOCOPY NUMBER)
3900 RETURN NUMBER IS
3901
3902 l_total_amount ar_deferred_lines.amount_recognized%TYPE;
3903 l_computed_line_amount ra_customer_trx_lines.extended_amount%TYPE;
3904
3905 BEGIN
3906
3907 /*------------------------------------------------------------------------
3908 | This function is a generic function to compute the line balance
3909 | using a standard rounding logic. This function is being called
3910 | both for invoice currency and functional currency. It is important
3911 | to understand the meanign of each parameter, so I give a detailed
3912 | explanations explantion for each parameter.
3913 |
3914 | PARAMETER DEFINITIONS:
3915 |
3916 | P_MODE
3917 | to indicate if this procedure is called for receipt application
3918 | or receipt reversal. the processing is quite different between the
3919 | two.
3920 |
3921 | P_AMOUNT_PREVIOUSLY_APPLIED
3922 | this is the receipt amount that has been applied previously over time
3923 | to this transaction. this amount would reflect any receipts reversals
3924 | if such an event happenned. this could be zero when the first receipt
3925 | application is being done.
3926 |
3927 | P_CURRENT_AMOUNT_APPLIED
3928 | current receipt amount at our disposal to distribute over lines.
3929 | to avoid rounding errors during receipt application we always sum the
3930 | p_amount_previously_applied and p_current_amount_applied and then
3931 | determine the line amount taking the whole amount into consideration.
3932 |
3933 | P_LINE_BALANCE_ORIGINAL
3934 | this is the line balance during invoice creation.
3935 |
3936 | P_CURRENCY_CODE
3937 | invoice currency code.
3938 |
3939 | P_SUM_OF_ALL_LINES
3940 | line 1 original balance + line 2 original balance + line 3 ....
3941 |
3942 | P_CURRENT_LINE_BALANCE
3943 | this is the receipt amount that has been applied previously over time
3944 | to this line.
3945 |
3946 | P_RUNNING_LINES_BALANCE :
3947 | this is sum of all lines processed so far, so for example, the first time
3948 | it is called it will be equal to line1, then it will be line1 + line2
3949 | then line 1 + line 2 + line 3 etc.
3950 |
3951 | RUNNING_ALLOCATED_BALANCE
3952 | if we had $1000 to apply and we have applied $100 to Line 1
3953 | then running_allocated balance would $100, then if we
3954 | allocated $300 for line 2 then the balance would be $400
3955 |
3956 | COMPUTED_LINE_AMOUNT
3957 | This is the amount computed that will be applied to the line. Since,
3958 | we determine the line amount taking the whole amount into
3959 | consideration, we must subtract the p_current_line_balance amount.
3960 |
3961 | THE ALOGORITHM for rounding
3962 |
3963 | line amounts $10, $20, $30, $40, Rev Total $100, $10 to be applied
3964 |
3965 | Line 1 a -> 10 * 10/100 = 1 (allocated)
3966 | -------------------------------------------
3967 |
3968 | Line 2 -> (10 + 20)/100 * 10 = 3
3969 |
3970 | b -> 3 - a = 2 (allocated)
3971 | -------------------------------------------
3972 |
3973 | Line 3 -> (10 + 20 + 30) * 10/100 = 6
3974 |
3975 | c -> 6 - a - b = 3
3976 |
3977 | -------------------------------------------
3978 | Line .....
3979 |
3980 +--------------------------------------------------------------------------*/
3981
3982 IF pg_debug IN ('Y', 'C') THEN
3983 debug('compute_line_amount()+');
3984 debug('** compute_line_amount parameters **');
3985 debug(' p_mode : ' || p_mode);
3986 debug(' p_amount_previously applied : ' ||
3987 p_amount_previously_applied);
3988 debug(' p_current_amount_applied : ' ||
3989 p_current_amount_applied);
3990 debug(' p_line_balance_orig : ' ||
3991 p_line_balance_orig);
3992 debug(' p_currency_code : ' ||
3993 p_currency_code);
3994 debug(' p_sum_of_all_lines : ' ||
3995 p_sum_of_all_lines);
3996 debug(' p_current_line_balance : ' ||
3997 p_current_line_balance);
3998 debug(' p_running_lines_balance : ' ||
3999 p_running_lines_balance);
4000 debug(' p_running_allocated_balance : ' ||
4001 p_running_allocated_balance);
4002 END IF;
4003
4004 IF (p_sum_of_all_lines = 0) THEN
4005 RETURN 0;
4006 END IF;
4007
4008 IF (p_mode = c_receipt_application_mode) THEN
4009
4010 l_total_amount := p_amount_previously_applied + p_current_amount_applied;
4011
4012 ELSE
4013
4014
4015 /*------------------------------------------------------------------------
4016 |
4017 | if this reversal makes the pending or recognized amount to go
4018 | down to zero, then we want to avoid having rounding errors as
4019 | follows : L1 -0.01, L2 -0.01 and L3 +0.02. So, simply reverse
4020 | amount in the pending column or recognized column and we will
4021 | avoid the above scenario.
4022 +------------------------------------------------------------------------*/
4023
4024 IF (p_current_amount_applied = p_amount_previously_applied) THEN
4025 RETURN p_current_line_balance;
4026 ELSE
4027 l_total_amount := p_current_amount_applied;
4028 END IF;
4029
4030 END IF;
4031
4032 p_running_lines_balance := p_running_lines_balance + p_line_balance_orig;
4033
4034 l_computed_line_amount :=
4035 arpcurr.currround(
4036 p_running_lines_balance /
4037 p_sum_of_all_lines * l_total_amount,
4038 p_currency_code)
4039 - p_running_allocated_balance;
4040
4041 p_running_allocated_balance := p_running_allocated_balance +
4042 l_computed_line_amount;
4043
4044 IF pg_debug IN ('Y', 'C') THEN
4045 debug('Calculation : ');
4046 debug('l_computed_line_amount := ');
4047 debug(' arpcurr.currround(');
4048 debug(' p_running_lines_balance / ');
4049 debug(' p_sum_of_all_lines * l_total_amount,');
4050 debug(' p_currency_code)');
4051 debug(' - p_running_allocated_balance');
4052 debug('-----------------------------------------');
4053 debug(' p_running_lines_balance : ' ||
4054 p_running_lines_balance);
4055 debug(' p_sum_of_all_lines : ' ||
4056 p_sum_of_all_lines);
4057 debug(' l_total_amount : ' || l_total_amount);
4058 debug(' p_currency_code : ' ||
4059 p_currency_code);
4060 debug(' p_running_allocated_balance : ' ||
4061 p_running_allocated_balance);
4062 debug(' p_computed_line_amount : ' ||
4063 l_computed_line_amount);
4064 END IF;
4065
4066
4067 IF (p_mode = c_receipt_application_mode) THEN
4068 l_computed_line_amount := l_computed_line_amount - p_current_line_balance;
4069 END IF;
4070
4071 IF pg_debug IN ('Y', 'C') THEN
4072 debug('compute_line_amount()-');
4073 END IF;
4074
4075 RETURN l_computed_line_amount;
4076
4077 EXCEPTION
4078
4079 WHEN NO_DATA_FOUND THEN
4080 IF pg_debug IN ('Y', 'C') THEN
4081 debug('NO_DATA_FOUND: compute_line_amount');
4082 debug(sqlerrm);
4083 END IF;
4084 RAISE;
4085
4086 WHEN OTHERS THEN
4087 IF pg_debug IN ('Y', 'C') THEN
4088 debug('OTHERS: compute_line_amount');
4089 debug(sqlerrm);
4090 END IF;
4091 RAISE;
4092
4093 END compute_line_amount;
4094
4095
4096 PROCEDURE get_receipt_parameters (
4097 p_mode IN VARCHAR2 DEFAULT NULL,
4098 p_customer_trx_id IN NUMBER DEFAULT NULL,
4099 p_acctd_amount_applied IN NUMBER DEFAULT NULL, -- func currency
4100 p_exchange_rate IN NUMBER DEFAULT NULL,
4101 p_invoice_currency_code IN VARCHAR2 DEFAULT NULL,
4102 p_tax_applied IN NUMBER DEFAULT NULL,
4103 p_charges_applied IN NUMBER DEFAULT NULL,
4104 p_freight_applied IN NUMBER DEFAULT NULL,
4105 p_line_applied IN NUMBER DEFAULT NULL,
4106 p_gl_date IN DATE DEFAULT NULL,
4107 p_receivable_application_id IN NUMBER DEFAULT NULL,
4108 x_customer_trx_id OUT NOCOPY NUMBER ,
4109 x_acctd_amount_applied OUT NOCOPY NUMBER, -- func currency
4110 x_exchange_rate OUT NOCOPY NUMBER,
4111 x_invoice_currency_code OUT NOCOPY VARCHAR2,
4112 x_tax_applied OUT NOCOPY NUMBER,
4113 x_charges_applied OUT NOCOPY NUMBER,
4114 x_freight_applied OUT NOCOPY NUMBER,
4115 x_line_applied OUT NOCOPY NUMBER,
4116 x_gl_date OUT NOCOPY DATE) IS
4117
4118 -- This cursor determines the receipt application details given a
4119 -- receivable application id.
4120
4121 CURSOR application IS
4122 SELECT applied_customer_trx_id,
4123 acctd_amount_applied_to,
4124 tax_applied,
4125 receivables_charges_applied,
4126 line_applied,
4127 freight_applied,
4128 gl_date
4129 FROM ar_receivable_applications
4130 WHERE receivable_application_id = p_receivable_application_id;
4131
4132 -- This cursor retrieves the currency information for the
4133 -- given invoice.
4134
4135 CURSOR invoice (p_trx_id IN NUMBER) IS
4136 SELECT invoice_currency_code,
4137 exchange_rate
4138 FROM ra_customer_trx
4139 WHERE customer_trx_id = p_trx_id;
4140
4141 BEGIN
4142
4143 /*------------------------------------------------------------------------
4144 | If this procedure is being called from receipt reversal then
4145 | the only thing we have is the receivable application id. As a result,
4146 | query the details from ar_receivable_applications_all table. and then
4147 | from the ra_customer_trx_all.
4148 | If this is being called from receipt application then simply copy the
4149 | passed variable into the local variables.
4150 +------------------------------------------------------------------------*/
4151
4152 IF pg_debug IN ('Y', 'C') THEN
4153 debug('get_receipt_parameters()+');
4154 END IF;
4155
4156 IF (p_mode = c_receipt_application_mode) THEN
4157
4158 IF pg_debug IN ('Y', 'C') THEN
4159 debug('receipt application mode');
4160 END IF;
4161
4162 -- simply copy over.
4163
4164 x_customer_trx_id := p_customer_trx_id;
4165 x_acctd_amount_applied := p_acctd_amount_applied;
4166 x_exchange_rate := p_exchange_rate;
4167 x_invoice_currency_code := p_invoice_currency_code;
4168 x_tax_applied := p_tax_applied;
4169 x_charges_applied := p_charges_applied;
4170 x_freight_applied := p_freight_applied;
4171 x_line_applied := p_line_applied;
4172 x_gl_date := p_gl_date;
4173
4174 ELSE
4175 IF pg_debug IN ('Y', 'C') THEN
4176 debug('receipt reversal mode');
4177 END IF;
4178
4179 OPEN application;
4180 FETCH application INTO
4181 x_customer_trx_id,
4182 x_acctd_amount_applied,
4183 x_tax_applied,
4184 x_charges_applied,
4185 x_line_applied,
4186 x_freight_applied,
4187 x_gl_date;
4188
4189 CLOSE application;
4190
4191 OPEN invoice (x_customer_trx_id);
4192 FETCH invoice INTO x_invoice_currency_code, x_exchange_rate;
4193 CLOSE invoice;
4194
4195 END IF;
4196
4197 IF pg_debug IN ('Y', 'C') THEN
4198 debug('** get_receipt_parameters **');
4199 debug(' p_mode : ' || p_mode);
4200 debug(' x_customer_trx_id : ' || x_customer_trx_id);
4201 debug(' x_acctd_amount_applied : ' ||
4202 x_acctd_amount_applied);
4203 debug(' x_exchange_rate : ' || x_exchange_rate);
4204 debug(' x_invoice_currency_code : ' ||
4205 x_invoice_currency_code);
4206 debug(' x_tax_applied : ' || x_tax_applied);
4207 debug(' x_charges_applied : ' || x_charges_applied);
4208 debug(' x_line_applied : ' || x_line_applied);
4209 debug(' x_freight_applied : ' || x_freight_applied);
4210 debug(' x_gl_date : ' || x_gl_date);
4211 debug(' p_recv..._appl..id : ' ||
4212 p_receivable_application_id);
4213 debug('get_receipt_parameters()-');
4214 END IF;
4215
4216 EXCEPTION
4217
4218 WHEN NO_DATA_FOUND THEN
4219 IF pg_debug IN ('Y', 'C') THEN
4220 debug('NO_DATA_FOUND: get_receipt_parameters');
4221 debug(sqlerrm);
4222 END IF;
4223 RAISE;
4224
4225 WHEN OTHERS THEN
4226 IF pg_debug IN ('Y', 'C') THEN
4227 debug('OTHERS: get_receipt_parameters');
4228 debug(sqlerrm);
4229 END IF;
4230 RAISE;
4231
4232 END get_receipt_parameters;
4233
4234
4235 PROCEDURE review_contingencies(p_customer_trx_line_id IN NUMBER) IS
4236
4237 CURSOR contingencies IS
4238 SELECT lc.customer_trx_line_id,
4239 lc.contingency_id,
4240 lc.expiration_date
4241 FROM ar_line_conts lc,
4242 ar_deferral_reasons dr
4243 WHERE lc.customer_trx_line_id = p_customer_trx_line_id
4244 AND lc.contingency_id = dr.contingency_id
4245 AND lc.completed_flag = 'N'
4246 AND lc.expiration_date IS NOT NULL
4247 AND trunc(lc.expiration_date) <= trunc(sysdate);
4248
4249 l_cust_trx_line_id_tbl number_table;
4250 l_contingency_id_tbl number_table;
4251 l_index NUMBER DEFAULT 1;
4252
4253 l_last_updated_by NUMBER;
4254 l_last_update_login NUMBER;
4255
4256
4257 BEGIN
4258
4259 debug('review_contingencies()+');
4260
4261 l_last_updated_by := fnd_global.user_id;
4262 l_last_update_login := fnd_global.user_id;
4263
4264 -- ***** should be converted into bulk fetch and
4265
4266 FOR cont_rec IN contingencies LOOP
4267
4268 l_cust_trx_line_id_tbl(l_index) := cont_rec.customer_trx_line_id;
4269 l_contingency_id_tbl(l_index) := cont_rec.contingency_id;
4270 l_index := l_index + 1;
4271
4272 END LOOP;
4273
4274 FORALL i in 1..l_index-1
4275 UPDATE ar_line_conts
4276 SET completed_flag = 'Y',
4277 reason_removal_date = sysdate,
4278 last_updated_by = l_last_updated_by,
4279 last_update_date = sysdate,
4280 last_update_login = l_last_update_login
4281 WHERE customer_trx_line_id = l_cust_trx_line_id_tbl(i)
4282 AND contingency_id = l_contingency_id_tbl(i);
4283
4284 debug('review_contingencies()-');
4285
4286 END review_contingencies;
4287
4288
4289 FUNCTION get_line_status (p_cust_trx_line_id IN NUMBER)
4290 RETURN NUMBER IS
4291
4292 l_line_status NUMBER DEFAULT c_recognizable;
4293 l_dummy NUMBER;
4294 l_exists_cash_based BOOLEAN;
4295 l_exists_contingency_based BOOLEAN;
4296
4297 CURSOR cash_based IS
4298 SELECT 1
4299 FROM ar_line_conts lc,
4300 ar_deferral_reasons dr
4301 WHERE lc.contingency_id = dr.contingency_id
4302 AND lc.customer_trx_line_id = p_cust_trx_line_id
4303 AND lc.completed_flag = 'N'
4304 AND dr.revrec_event_code = 'RECEIPT_APPLICATION';
4305
4306
4307 CURSOR contingency_based IS
4308 SELECT 1
4309 FROM ar_line_conts lc,
4310 ar_deferral_reasons dr
4311 WHERE lc.contingency_id = dr.contingency_id
4312 AND lc.customer_trx_line_id = p_cust_trx_line_id
4313 AND lc.completed_flag = 'N'
4314 AND dr.revrec_event_code <> 'RECEIPT_APPLICATION';
4315
4316 BEGIN
4317
4318 /*------------------------------------------------------------------------
4319 | In many part of this package there is a need to know the line status.
4320 | IS the line deferred because of line level, or header level concerns,
4321 | or is it both or none. Given the status the code has to handle it
4322 | differently. So, this function determines which of the four
4323 | scenarios we are facing.
4324 +------------------------------------------------------------------------*/
4325
4326 IF pg_debug IN ('Y', 'C') THEN
4327 debug('get_line_status()+');
4328 debug('** get_line_status parameters **');
4329 debug(' line : ' || p_cust_trx_line_id);
4330 END IF;
4331
4332 /* 7276627 call review_contingenices by customer_Trx_line_id */
4333 review_contingencies(p_cust_trx_line_id);
4334
4335 OPEN cash_based;
4336 FETCH cash_based INTO l_dummy;
4337 l_exists_cash_based := cash_based%FOUND;
4338 CLOSE cash_based;
4339
4340 OPEN contingency_based;
4341 FETCH contingency_based INTO l_dummy;
4342 l_exists_contingency_based := contingency_based%FOUND;
4343 CLOSE contingency_based;
4344
4345 IF (l_exists_cash_based AND l_exists_contingency_based) THEN
4346 l_line_status := c_combination;
4347 ELSIF (l_exists_cash_based) THEN
4348 l_line_status := c_cash_based;
4349 ELSIF (l_exists_contingency_based) THEN
4350 l_line_status := c_contingency_based;
4351 ELSE
4352 l_line_status := c_recognizable;
4353 END IF;
4354
4355 IF pg_debug IN ('Y', 'C') THEN
4356 debug('get_receipt_analyzer_scenario()-');
4357 END IF;
4358
4359 RETURN l_line_status;
4360
4361 EXCEPTION
4362 WHEN OTHERS THEN
4363 IF pg_debug IN ('Y', 'C') THEN
4364 debug('OTHERS: get_line_status');
4365 debug(sqlerrm);
4366 END IF;
4367 RAISE;
4368
4369 END get_line_status;
4370
4371
4372 /****************************************************************************/
4373 /**** All the subroutines from this point onward are public subroutines. ****/
4374 /****************************************************************************/
4375
4376
4377 /*========================================================================
4378 | PUBLIC PROCEDURE update_line_conts
4379 |
4380 | DESCRIPTION
4381 | This procedures lets calling programs update contingencies
4382 |
4383 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
4384 | RAM Wizard
4385 |
4386 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
4387 | None.
4388 |
4389 | PARAMETERS
4390 | None.
4391 |
4392 | NOTES
4393 | None.
4394 |
4395 | MODIFICATION HISTORY
4396 | Date Author Description of Changes
4397 | 28-APR-2005 ORASHID Subroutine Created
4398 | 29-JUN-2006 MRAYMOND 5201842 - Fixed expiration_event_date
4399 | to use correct parameter.
4400 *===========================================================================*/
4401
4402 PROCEDURE update_line_conts (
4403 p_customer_trx_line_id NUMBER,
4404 p_contingency_id NUMBER,
4405 p_expiration_date DATE DEFAULT NULL,
4406 p_expiration_event_date DATE DEFAULT NULL,
4407 p_expiration_days NUMBER DEFAULT NULL,
4408 p_completed_flag VARCHAR2 DEFAULT NULL,
4409 p_reason_removal_date DATE DEFAULT NULL) IS
4410
4411 l_sysdate DATE;
4412 l_last_updated_by NUMBER;
4413 l_last_update_login NUMBER;
4414 l_line_collectible VARCHAR2(1) DEFAULT NULL;
4415
4416 BEGIN
4417
4418 -- This procedure simply updates a row of data in the
4419 -- ar_deferred_lines table. It will only update columns
4420 -- for which data is provided, the rest will retain their
4421 -- original values.
4422
4423 IF pg_debug IN ('Y', 'C') THEN
4424 debug('update_line_conts()+');
4425 debug('** parameters **');
4426 debug(' p_customer_trx_line_id : ' ||
4427 p_customer_trx_line_id);
4428 debug(' p_contingency_id : ' ||
4429 p_contingency_id);
4430 END IF;
4431
4432 l_sysdate := trunc(sysdate);
4433 l_last_updated_by := arp_global.user_id;
4434 l_last_update_login := arp_global.last_update_login;
4435
4436 UPDATE ar_line_conts
4437 SET expiration_date = nvl(p_expiration_date, expiration_date),
4438 expiration_event_date = nvl(p_expiration_event_date, expiration_event_date),
4439 expiration_days = nvl(p_expiration_days, expiration_days),
4440 completed_flag = nvl(p_completed_flag, completed_flag),
4441 reason_removal_date = nvl(p_reason_removal_date, reason_removal_date),
4442 last_updated_by = l_last_updated_by,
4443 last_update_date = l_sysdate,
4444 last_update_login = l_last_update_login
4445 WHERE customer_trx_line_id = p_customer_trx_line_id
4446 AND contingency_id = p_contingency_id;
4447
4448 IF pg_debug IN ('Y', 'C') THEN
4449 debug('update_line_conts()-');
4450 END IF;
4451
4452 EXCEPTION
4453
4454 WHEN NO_DATA_FOUND THEN
4455 IF pg_debug IN ('Y', 'C') THEN
4456 debug('NO_DATA_FOUND: update_line_conts');
4457 debug(sqlerrm);
4458 END IF;
4459 RAISE;
4460
4461 WHEN OTHERS THEN
4462 IF pg_debug IN ('Y', 'C') THEN
4463 debug('OTHERS: update_line_conts');
4464 debug(sqlerrm);
4465 END IF;
4466 RAISE;
4467
4468 END update_line_conts;
4469
4470
4471 /*========================================================================
4472 | PUBLIC PROCEDURE delete_line_conts
4473 |
4474 | DESCRIPTION
4475 | This procedures lets calling programs delete contingencies
4476 |
4477 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
4478 | RAM Wizard
4479 |
4480 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
4481 | None.
4482 |
4483 | PARAMETERS
4484 | None.
4485 |
4486 | NOTES
4487 | None.
4488 |
4489 | MODIFICATION HISTORY
4490 | Date Author Description of Changes
4491 | 18-MAY-2005 ORASHID Subroutine Created
4492 |
4493 *===========================================================================*/
4494
4495 PROCEDURE delete_line_conts (
4496 p_customer_trx_line_id NUMBER,
4497 p_contingency_id NUMBER) IS
4498
4499 BEGIN
4500
4501 -- This procedure simply deletes a row of data in the
4502 -- ar_deferred_lines table. It will only delete columns
4503 -- for which data is provided, the rest will retain their
4504 -- original values.
4505
4506 IF pg_debug IN ('Y', 'C') THEN
4507 debug('delete_line_conts()+');
4508 debug('** delete_line_conts parameters **');
4509 debug(' p_customer_trx_line_id : ' ||
4510 p_customer_trx_line_id);
4511 debug(' p_contingency_id : ' ||
4512 p_contingency_id);
4513 END IF;
4514
4515 DELETE
4516 FROM ar_line_conts
4517 WHERE customer_trx_line_id = p_customer_trx_line_id
4518 AND contingency_id = p_contingency_id;
4519
4520 IF pg_debug IN ('Y', 'C') THEN
4521 debug('delete_line_conts()-');
4522 END IF;
4523
4524 EXCEPTION
4525
4526 WHEN NO_DATA_FOUND THEN
4527 IF pg_debug IN ('Y', 'C') THEN
4528 debug('NO_DATA_FOUND: delete_line_conts');
4529 debug(sqlerrm);
4530 END IF;
4531 RAISE;
4532
4533 WHEN OTHERS THEN
4534 IF pg_debug IN ('Y', 'C') THEN
4535 debug('OTHERS: delete_line_conts');
4536 debug(sqlerrm);
4537 END IF;
4538 RAISE;
4539
4540 END delete_line_conts;
4541
4542
4543 /*========================================================================
4544 | PUBLIC FUNCTION revenue_management_enabled
4545 |
4546 | DESCRIPTION
4547 | This function checks to if anyone of the fields in revenue policy tab
4548 | in the system options form is filled out. If so it knows this feature
4549 | is in use.
4550 |
4551 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
4552 | Auto Invoice
4553 |
4554 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
4555 | None.
4556 |
4557 | PARAMETERS
4558 | None.
4559 |
4560 | NOTES
4561 | None.
4562 |
4563 | MODIFICATION HISTORY
4564 | Date Author Description of Changes
4565 | 26-JUL-2002 ORASHID Subroutine Created
4566 |
4567 *===========================================================================*/
4568
4569 FUNCTION revenue_management_enabled
4570 RETURN BOOLEAN IS
4571
4572 l_return_value BOOLEAN DEFAULT FALSE;
4573
4574 BEGIN
4575
4576 -- This subroutine is obsolete as of 11i10+. As of the new of the
4577 -- new infrastructure Revenue Management is always turned on there
4578 -- there is no specific mechanism to explicitly turn off the feature.
4579 -- Nevertheless, if no cntingencies are passed for a line then
4580 -- it automatically means Revenue Management would not manage that
4581 -- transaction. So, this subroutine returns TRUE in hard coded
4582 -- fashion.
4583
4584 debug('ar_revenue_management_pvt.revenue_management_enabled()+');
4585 l_return_value := TRUE;
4586
4587 debug('ar_revenue_management_pvt.revenue_management_enabled()-');
4588 RETURN l_return_value;
4589
4590 EXCEPTION
4591
4592 WHEN NO_DATA_FOUND THEN
4593 debug('NO_DATA_FOUND: revenue_management_enabled');
4594 debug(sqlerrm);
4595 RAISE;
4596
4597 WHEN OTHERS THEN
4598 debug('OTHERS: revenue_management_enabled');
4599 debug(sqlerrm);
4600 RAISE;
4601
4602 END revenue_management_enabled;
4603
4604
4605 /*========================================================================
4606 | PUBLIC FUNCTION line_collectible
4607 |
4608 | DESCRIPTION
4609 |
4610 | This function simply checks to see if a line was deemed collectible by
4611 | the revenue management engine.
4612 |
4613 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
4614 |
4615 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
4616 | None.
4617 |
4618 | PARAMETERS
4619 | p_customer_trx_id
4620 | p_customer_trx_line_id
4621 |
4622 | KNOWN ISSUES
4623 | Enter business functionality which was de-scoped as part of the
4624 | implementation. Ideally this should never be used.
4625 |
4626 | NOTES
4627 | Any interesting aspect of the code in the package body which needs
4628 | to be stated.
4629 |
4630 | MODIFICATION HISTORY
4631 | Date Author Description of Changes
4632 | 24-SEP-2002 ORASHID Subroutine Created
4633 | 24-SEP-2006 MRAYMOND 5374131 - Corrected problem with
4634 | cursor where line_id was not
4635 | used to restrict rows.
4636 *===========================================================================*/
4637
4638 FUNCTION line_collectible (
4639 p_customer_trx_id NUMBER,
4640 p_customer_trx_line_id NUMBER)
4641 RETURN NUMBER IS
4642
4643 -- This cursor determines if a line is collectible or not.
4644 -- It does not recompute, but simply looks it up.
4645
4646 CURSOR c IS
4647 SELECT line_collectible_flag
4648 FROM ar_deferred_lines
4649 WHERE customer_trx_id = p_customer_trx_id
4650 AND customer_trx_line_id = p_customer_trx_line_id;
4651
4652 l_flag ar_deferred_lines.line_collectible_flag%TYPE;
4653 l_return_value NUMBER;
4654
4655 BEGIN
4656
4657 IF pg_debug IN ('Y', 'C') THEN
4658 debug('ar_revenue_management_pvt.line_collectible()+');
4659 debug('** line_collectible parameters **');
4660 debug(' p_customer_trx_id : ' || p_customer_trx_id);
4661 debug(' p_customer_trx_line_id : ' ||
4662 p_customer_trx_line_id);
4663 END IF;
4664
4665 OPEN c;
4666 FETCH c INTO l_flag;
4667
4668 IF (l_flag = 'Y') THEN
4669 l_return_value := collect;
4670 ELSIF (l_flag = 'N') THEN
4671 l_return_value := defer;
4672 ELSE
4673 l_return_value := not_analyzed;
4674 END IF;
4675
4676 CLOSE c;
4677
4678 IF pg_debug IN ('Y', 'C') THEN
4679 debug('ar_revenue_management_pvt.line_collectible()-');
4680 END IF;
4681
4682 RETURN l_return_value;
4683
4684 EXCEPTION
4685
4686 WHEN NO_DATA_FOUND THEN
4687 IF pg_debug IN ('Y', 'C') THEN
4688 debug('NO_DATA_FOUND: line_collectible');
4689 debug(sqlerrm);
4690 END IF;
4691 RAISE;
4692
4693 WHEN OTHERS THEN
4694 IF pg_debug IN ('Y', 'C') THEN
4695 debug('OTHERS: line_collectible');
4696 debug(sqlerrm);
4697 END IF;
4698 RAISE;
4699
4700 END line_collectible;
4701
4702
4703 /*========================================================================
4704 | PUBLIC FUNCTION txn_collectible
4705 |
4706 | DESCRIPTION
4707 |
4708 | This function simply checks to see if a txn was deemed collectible by
4709 | the revenue management engine by looking through each line.
4710 |
4711 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
4712 | Transactions Work Bench
4713 |
4714 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
4715 | None.
4716 |
4717 | PARAMETERS
4718 | p_customer_trx_id
4719 |
4720 | NOTES
4721 |
4722 | None.
4723 |
4724 | MODIFICATION HISTORY
4725 | Date Author Description of Changes
4726 | 14-OCT-2002 ORASHID Subroutine Created
4727 |
4728 *===========================================================================*/
4729
4730 FUNCTION txn_collectible (p_customer_trx_id IN NUMBER)
4731 RETURN BOOLEAN IS
4732
4733 -- This cursor determines if a txn is collectible or not.
4734 -- It does not recompute, but simply looks it up.
4735
4736 CURSOR c IS
4737 SELECT 1
4738 FROM ar_deferred_lines
4739 WHERE customer_trx_id = p_customer_trx_id
4740 AND original_collectibility_flag = 'N'
4741 AND manual_override_flag = 'N'
4742 AND rownum = 1;
4743
4744 /* Bug 12813416 */
4745 CURSOR c1 IS
4746 SELECT 'its a pre-billing contingency'
4747 FROM ra_customer_trx_lines_all ctl,
4748 ar_line_conts_all lrc,
4749 ar_deferral_reasons dr
4750 WHERE ctl.customer_trx_id = p_customer_trx_id
4751 and ctl.customer_trx_line_id=lrc.customer_trx_line_id
4752 and trunc(nvl(lrc.expiration_date,sysdate)) - trunc(sysdate) <= 0
4753 and dr.contingency_id = lrc.contingency_id
4754 AND dr.REVREC_EVENT_CODE = 'INVOICING' ;
4755
4756 l_flag NUMBER;
4757 l_return_value BOOLEAN;
4758 l_cont VARCHAR2(50);
4759 BEGIN
4760
4761 IF pg_debug IN ('Y', 'C') THEN
4762 debug('ar_revenue_management_pvt.txn_collectible()+');
4763 debug('** txn_collectible parameters **');
4764 debug(' p_customer_trx_id : ' || p_customer_trx_id);
4765 END IF;
4766
4767 OPEN c;
4768 FETCH c INTO l_flag;
4769 l_return_value := c%NOTFOUND;
4770 CLOSE c;
4771
4772 /* Bug 12813416 */
4773 If(not(l_return_value)) then
4774
4775 OPEN c1;
4776 FETCH c1 INTO l_cont;
4777 l_return_value := c1%FOUND;
4778 CLOSE c1;
4779
4780 If (l_return_value) then
4781 Update ar_deferred_lines dl
4782 set original_collectibility_flag = 'Y'
4783 WHERE dl.customer_trx_id = p_customer_trx_id;
4784 end if;
4785 end if;
4786
4787 IF pg_debug IN ('Y', 'C') THEN
4788 debug('ar_revenue_management_pvt.txn_collectible()-');
4789 END IF;
4790
4791 RETURN l_return_value;
4792
4793 EXCEPTION
4794
4795 WHEN NO_DATA_FOUND THEN
4796 IF pg_debug IN ('Y', 'C') THEN
4797 debug('NO_DATA_FOUND: txn_collectible');
4798 debug(sqlerrm);
4799 END IF;
4800 RAISE;
4801
4802 WHEN OTHERS THEN
4803 IF pg_debug IN ('Y', 'C') THEN
4804 debug('OTHERS: txn_collectible');
4805 debug(sqlerrm);
4806 END IF;
4807 RAISE;
4808
4809 END txn_collectible;
4810
4811
4812 /*========================================================================
4813 | PUBLIC PROCEDURE delete_failed_rows
4814 |
4815 | DESCRIPTION
4816 |
4817 | This procedure deletes rows from the revenue management tables for
4818 | a failed auto invoice run.
4819 |
4820 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
4821 | Auto Invoice.
4822 |
4823 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
4824 | None.
4825 |
4826 | PARAMETERS
4827 | p_request_id
4828 |
4829 | NOTES
4830 | None.
4831 |
4832 | MODIFICATION HISTORY
4833 | Date Author Description of Changes
4834 | 24-SEP-2002 ORASHID Subroutine Created
4835 |
4836 *===========================================================================*/
4837
4838 PROCEDURE delete_failed_rows (p_request_id IN NUMBER) IS
4839
4840 BEGIN
4841
4842 IF pg_debug IN ('Y', 'C') THEN
4843 debug('ar_revenue_management_pvt.delete_failed_rows()+');
4844 debug('** delete_failed_rows parameters **');
4845 debug(' p_request_id : ' || p_request_id);
4846 END IF;
4847
4848 DELETE FROM ar_deferred_lines
4849 WHERE request_id = p_request_id;
4850
4851 DELETE FROM ar_line_conts
4852 WHERE request_id = p_request_id;
4853
4854 IF pg_debug IN ('Y', 'C') THEN
4855 debug('ar_revenue_management_pvt.delete_failed_rows()-');
4856 END IF;
4857
4858 EXCEPTION
4859
4860 WHEN NO_DATA_FOUND THEN
4861 IF pg_debug IN ('Y', 'C') THEN
4862 debug('NO_DATA_FOUND: delete_failed_rows');
4863 debug(sqlerrm);
4864 END IF;
4865 RAISE;
4866
4867 WHEN OTHERS THEN
4868 IF pg_debug IN ('Y', 'C') THEN
4869 debug('OTHERS: delete_failed_rows');
4870 debug(sqlerrm);
4871 END IF;
4872 RAISE;
4873
4874 END delete_failed_rows;
4875
4876
4877 /*========================================================================
4878 | PUBLIC PROCEDURE delete_rejected_rows
4879 |
4880 | DESCRIPTION
4881 |
4882 | This procedure deletes rows those are rejected by auto invoice
4883 | from the revenue management tables.
4884 |
4885 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
4886 | Auto Invoice.
4887 |
4888 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
4889 | None.
4890 |
4891 | PARAMETERS
4892 | p_request_id
4893 |
4894 | NOTES
4895 | None.
4896 |
4897 | MODIFICATION HISTORY
4898 | Date Author Description of Changes
4899 | 23-OCT-2002 ORASHID Subroutine Created
4900 | 06-APR-2004 MRAYMOND Modified sql to remove all
4901 | rows for a specific transaction
4902 | when any one is in error.
4903 *===========================================================================*/
4904
4905 PROCEDURE delete_rejected_rows (p_request_id IN NUMBER) IS
4906
4907 BEGIN
4908
4909 debug('ar_revenue_management_pvt.delete_rejected_rows()+');
4910 debug('** delete_rejected_rows parameters **');
4911 debug(' p_request_id : ' || p_request_id);
4912
4913 ---------------------------------------------------------------------------
4914 -- Remove all rows form the revenue management associated with each trx
4915 -- that has a line being rejected.
4916 --
4917 -- NOTE: When this code gets called, all rows with the same customer_trx_id
4918 -- must be rejected regardless of the batch source setting for failed
4919 -- lines. The batch source setting is only relevant to early
4920 -- validations (pre grouping ones).
4921
4922 -- First delete from the child rows
4923
4924 DELETE
4925 FROM ar_line_conts
4926 WHERE customer_trx_line_id IN
4927 (
4928 SELECT customer_trx_line_id
4929 FROM ar_deferred_lines
4930 WHERE customer_trx_id IN
4931 (
4932 SELECT DISTINCT il.customer_trx_id
4933 FROM ra_interface_errors ie,
4934 ra_interface_lines il
4935 WHERE ie.interface_line_id = il.interface_line_id
4936 AND il.request_id = p_request_id
4937 )
4938 );
4939
4940 debug('contingencies deleted : ' || SQL%ROWCOUNT);
4941
4942 -- Now delete from the parent rows
4943
4944 DELETE
4945 FROM ar_deferred_lines
4946 WHERE customer_trx_id IN
4947 (
4948 SELECT DISTINCT il.customer_trx_id
4949 FROM ra_interface_errors ie,
4950 ra_interface_lines il
4951 WHERE ie.interface_line_id = il.interface_line_id
4952 AND il.request_id = p_request_id
4953 );
4954
4955 debug('lines deleted : ' || SQL%ROWCOUNT);
4956 debug('ar_revenue_management_pvt.delete_rejected_rows()-');
4957
4958 EXCEPTION
4959
4960 WHEN NO_DATA_FOUND THEN
4961 debug('NO_DATA_FOUND: delete_rejected_rows');
4962 debug(sqlerrm);
4963 RAISE;
4964
4965 WHEN OTHERS THEN
4966 debug('OTHERS: delete_rejected_rows');
4967 debug(sqlerrm);
4968 RAISE;
4969
4970 END delete_rejected_rows;
4971
4972
4973 /*========================================================================
4974 | PUBLIC FUNCTION acceptance_allowed
4975 |
4976 | DESCRIPTION
4977 |
4978 | This functions checks to see if a acceptance is required for this
4979 | transaction. The users should not be able to record acceptance
4980 | if the transaction is not being monitored by revenue management engine,
4981 | or it is already collectible, or it is manually over ridden, and finally
4982 | if acceptance is not an issue.
4983 |
4984 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
4985 | RAM Wizard.
4986 |
4987 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
4988 | monitored_transaction
4989 |
4990 | PARAMETERS
4991 | p_customer_trx_id
4992 | p_customer_trx_line_id
4993 |
4994 | NOTES
4995 | None.
4996 |
4997 | MODIFICATION HISTORY
4998 | Date Author Description of Changes
4999 | 23-SEP-2002 ORASHID Subroutine Created
5000 |
5001 *===========================================================================*/
5002
5003 FUNCTION acceptance_allowed (
5004 p_customer_trx_id IN NUMBER,
5005 p_customer_trx_line_id IN NUMBER)
5006 RETURN NUMBER IS
5007
5008 -- This cursor checks to see acceptance is required
5009 -- for this transaction or line
5010
5011 CURSOR contingencies IS
5012 SELECT 1
5013 FROM ar_deferred_lines dl,
5014 ar_line_conts lc,
5015 ar_deferral_reasons dl
5016 WHERE dl.customer_trx_line_id = lc.customer_trx_line_id
5017 AND lc.contingency_id = dl.contingency_id
5018 AND lc.completed_flag = 'N'
5019 AND dl.customer_trx_id = p_customer_trx_id
5020 AND dl.customer_trx_line_id = nvl(p_customer_trx_line_id,
5021 dl.customer_trx_line_id)
5022 AND dl.revrec_event_code = 'CUSTOMER_ACCEPTANCE';
5023
5024 l_return_value NUMBER DEFAULT c_acceptance_allowed;
5025 l_dummy NUMBER;
5026
5027 BEGIN
5028
5029 /*------------------------------------------------------------------------
5030 | This functions determines if the user using the RAM wizard will be
5031 | allowed to "Early Accept". The user can NOT do that, if the transaction
5032 | is not being monitored by revenue management engine and/or if the line(s)
5033 | do not have an acceptance problem.
5034 +------------------------------------------------------------------------*/
5035
5036 IF pg_debug IN ('Y', 'C') THEN
5037 debug('ar_revenue_management_pvt.acceptance_allowed()+');
5038 debug('** acceptance_allowed parameters **');
5039 debug(' p_customer_trx_id : ' || p_customer_trx_id);
5040 debug(' p_customer_trx_line_id : ' ||
5041 p_customer_trx_line_id);
5042 END IF;
5043
5044
5045 IF NOT monitored_transaction(p_customer_trx_id) THEN
5046
5047 IF pg_debug IN ('Y', 'C') THEN
5048 debug('Not A RAMC Transaction');
5049 END IF;
5050 l_return_value := c_transaction_not_monitored;
5051
5052 ELSE
5053
5054 IF pg_debug IN ('Y', 'C') THEN
5055 debug('It is a RAMC Transaction');
5056 END IF;
5057 OPEN contingencies;
5058 FETCH contingencies INTO l_dummy;
5059 IF contingencies%NOTFOUND THEN
5060 IF pg_debug IN ('Y', 'C') THEN
5061 debug('acceptance not required');
5062 END IF;
5063 l_return_value := c_acceptance_not_required;
5064 END IF;
5065 CLOSE contingencies;
5066
5067 END IF;
5068
5069 IF pg_debug IN ('Y', 'C') THEN
5070 debug('Acceptance Allowed? : ' || l_return_value);
5071 debug('ar_revenue_management_pvt.acceptance_allowed()-');
5072 END IF;
5073
5074 RETURN l_return_value;
5075
5076 EXCEPTION
5077
5078 WHEN NO_DATA_FOUND THEN
5079 IF pg_debug IN ('Y', 'C') THEN
5080 debug('NO_DATA_FOUND: acceptance_allowed');
5081 debug(sqlerrm);
5082 END IF;
5083 RAISE;
5084
5085 WHEN OTHERS THEN
5086 IF pg_debug IN ('Y', 'C') THEN
5087 debug('OTHERS: acceptance_allowed');
5088 debug(sqlerrm);
5089 END IF;
5090 RAISE;
5091
5092 END acceptance_allowed;
5093
5094
5095 /*========================================================================
5096 | PUBLIC FUNCTION cash_based
5097 |
5098 | DESCRIPTION
5099 | This functions determines if the invoice being credited is cash based
5100 | invoice. If so then the credit memo being created should
5101 | hit the un-earned bucket only.
5102 |
5103 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
5104 | Credit Memo Module.
5105 |
5106 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
5107 | None.
5108 |
5109 | PARAMETERS
5110 | p_customer_trx_id
5111 |
5112 | NOTES
5113 | None.
5114 |
5115 | MODIFICATION HISTORY
5116 | Date Author Description of Changes
5117 | 07-OCT-2002 ORASHID Subroutine Created
5118 |
5119 *===========================================================================*/
5120
5121 FUNCTION cash_based (p_customer_trx_id IN NUMBER)
5122 RETURN NUMBER IS
5123
5124 CURSOR contingencies IS
5125 SELECT 1
5126 FROM ar_deferred_lines dl,
5127 ar_line_conts lc,
5128 ar_deferral_reasons dr
5129 WHERE dl.customer_trx_line_id = lc.customer_trx_line_id
5130 AND lc.contingency_id = dr.contingency_id
5131 AND lc.completed_flag = 'N'
5132 AND dr.revrec_event_code = 'RECEIPT_APPLICATION'
5133 AND dl.customer_trx_id = p_customer_trx_id;
5134
5135 l_dummy NUMBER;
5136
5137 BEGIN
5138
5139 IF pg_debug IN ('Y', 'C') THEN
5140 debug('ar_revenue_management_pvt.cash_based()+');
5141 debug('** cash_based parameters **');
5142 debug(' p_customer_trx_id : ' || p_customer_trx_id);
5143 END IF;
5144
5145 IF NOT monitored_transaction (p_customer_trx_id) THEN
5146 IF pg_debug IN ('Y', 'C') THEN
5147 debug ('*** This Transaction Is Not Being Monitored ***');
5148 END IF;
5149 RETURN c_no;
5150 END IF;
5151
5152 OPEN contingencies;
5153 FETCH contingencies INTO l_dummy;
5154
5155 IF contingencies%FOUND THEN
5156 RETURN c_yes;
5157 ELSE
5158 RETURN c_no;
5159 END IF;
5160
5161 CLOSE contingencies;
5162
5163 IF pg_debug IN ('Y', 'C') THEN
5164 debug('ar_revenue_management_pvt.cash_based()-');
5165 END IF;
5166
5167 EXCEPTION
5168
5169 WHEN NO_DATA_FOUND THEN
5170 IF pg_debug IN ('Y', 'C') THEN
5171 debug('NO_DATA_FOUND: cash_based');
5172 debug(sqlerrm);
5173 END IF;
5174 RAISE;
5175
5176 WHEN OTHERS THEN
5177 IF pg_debug IN ('Y', 'C') THEN
5178 debug('OTHERS: cash_based');
5179 debug(sqlerrm);
5180 END IF;
5181 RAISE;
5182
5183 END cash_based;
5184
5185
5186 /*========================================================================
5187 | PUBLIC PROCEDURE process_event
5188
5189 | MODIFICATION HISTORY
5190 | Date Author Description of Changes
5191 | 26-JUL-2002 ORASHID Subroutine Created
5192 |
5193 *=======================================================================*/
5194
5195 PROCEDURE process_event (
5196 p_cust_trx_line_id IN NUMBER,
5197 p_event_date IN DATE,
5198 p_event_code IN VARCHAR2) IS
5199
5200 /*-----------------------------------------------------------------------+
5201 | Local Variable Declarations and initializations |
5202 +-----------------------------------------------------------------------*/
5203
5204 l_fully_recognized BOOLEAN DEFAULT TRUE;
5205 l_not_recognized BOOLEAN DEFAULT TRUE;
5206 l_first_adjustment BOOLEAN DEFAULT TRUE;
5207 l_partially_recognized BOOLEAN;
5208 l_last_fetch BOOLEAN;
5209 l_line_status NUMBER;
5210 l_adjustment_number NUMBER;
5211 l_rev_adj_rec ar_revenue_adjustment_pvt.rev_adj_rec_type;
5212 l_ram_desc_flexfield desc_flexfield;
5213
5214 l_customer_trx_id NUMBER;
5215 l_amount_due_original NUMBER;
5216 l_amount_recognized NUMBER;
5217 l_amount_pending NUMBER;
5218 l_acctd_amount_due_orig NUMBER;
5219 l_acctd_amt_recognized NUMBER;
5220 l_acctd_amount_pending NUMBER;
5221
5222 l_return_status VARCHAR2(30);
5223 l_msg_count NUMBER;
5224 l_msg_data VARCHAR2(150);
5225
5226 CURSOR lines IS
5227 SELECT customer_trx_id,
5228 amount_due_original,
5229 acctd_amount_due_original,
5230 amount_recognized,
5231 acctd_amount_recognized,
5232 amount_pending,
5233 acctd_amount_pending
5234 FROM ar_deferred_lines
5235 WHERE customer_trx_line_id = p_cust_trx_line_id;
5236
5237 BEGIN
5238
5239 -- this subroutine is equivalent of revenue synchronizer for RAM Wizard.
5240 -- In other words, when an event happens we need to know what is the
5241 -- latest on this line and adjust revenue accordingly.
5242
5243 update_for_event(
5244 p_cust_trx_line_id => p_cust_trx_line_id,
5245 p_event_date => p_event_date,
5246 p_event_code => p_event_code);
5247
5248 OPEN lines;
5249 FETCH lines
5250 INTO
5251 l_customer_trx_id,
5252 l_amount_due_original,
5253 l_acctd_amount_due_orig,
5254 l_amount_recognized,
5255 l_acctd_amt_recognized,
5256 l_amount_pending,
5257 l_acctd_amount_pending;
5258 CLOSE lines;
5259
5260 ----------------------------------------------------------------------
5261 -- This is a call to a procedure that will look into each
5262 -- line level deferral reason and compare the current
5263 -- date with expiry date for each. This will return
5264 -- current status of each deferral reason, and give
5265 -- overall verdict for this line.
5266 ----------------------------------------------------------------------
5267
5268 l_line_status := get_line_status (
5269 p_cust_trx_line_id => p_cust_trx_line_id);
5270
5271 IF (l_line_status = c_recognizable) THEN
5272
5273 IF pg_debug IN ('Y', 'C') THEN
5274 debug('no issues remain');
5275 END IF;
5276
5277 l_rev_adj_rec.line_selection_mode := 'S';
5278 l_rev_adj_rec.from_cust_trx_line_id := p_cust_trx_line_id;
5279 l_rev_adj_rec.customer_trx_id := l_customer_trx_id;
5280 l_rev_adj_rec.gl_date := p_event_date; -- 7556149
5281 l_rev_adj_rec.reason_code := 'REV_MGMT_ENGINE';
5282 l_rev_adj_rec.amount_mode := 'A';
5283 l_rev_adj_rec.amount := l_amount_due_original;
5284
5285 adjust_revenue(
5286 p_mode => c_earn_revenue,
5287 p_customer_trx_id => l_customer_trx_id,
5288 p_customer_trx_line_id => p_cust_trx_line_id,
5289 p_acctd_amount => l_acctd_amount_due_orig,
5290 p_ram_desc_flexfield => l_ram_desc_flexfield,
5291 p_rev_adj_rec => l_rev_adj_rec,
5292 p_gl_date => p_event_date, -- 7556149
5293 x_adjustment_number => l_adjustment_number,
5294 x_return_status => l_return_status,
5295 x_msg_count => l_msg_count,
5296 x_msg_data => l_msg_data);
5297
5298 IF pg_debug IN ('Y', 'C') THEN
5299 debug('Revenue adjusted and now updating rvmg tables');
5300 END IF;
5301
5302 update_deferred_lines (
5303 p_customer_trx_line_id => p_cust_trx_line_id,
5304 p_line_status => l_line_status,
5305 p_amount_recognized => l_amount_due_original,
5306 p_acctd_amount_recognized => l_acctd_amount_due_orig,
5307 p_amount_pending => 0,
5308 p_acctd_amount_pending => 0);
5309
5310 ELSIF (l_line_status = c_cash_based) THEN
5311
5312 --------------------------------------------------------------------
5313 -- acceptance was the only hang up or some line level problems
5314 -- along with acceptance were the problems, but they are now
5315 -- alleviated, so we can now recognize reveneue for this line.
5316 -- However, credit problem still remain, so we can recognize only
5317 -- upto the amount already applied. So, if there is anything in the
5318 -- pending column we should recognize that much. |
5319 --------------------------------------------------------------------
5320
5321 IF pg_debug IN ('Y', 'C') THEN
5322 debug('only header issues remain');
5323 END IF;
5324
5325 IF (l_amount_pending > 0) THEN
5326
5327 IF pg_debug IN ('Y', 'C') THEN
5328 debug('amount pending is greater than zero');
5329 END IF;
5330
5331 l_rev_adj_rec.line_selection_mode := 'S';
5332 l_rev_adj_rec.from_cust_trx_line_id := p_cust_trx_line_id;
5333 l_rev_adj_rec.customer_trx_id := l_customer_trx_id;
5334 l_rev_adj_rec.gl_date := p_event_date;
5335 l_rev_adj_rec.reason_code := 'REV_MGMT_ENGINE';
5336 l_rev_adj_rec.amount_mode := 'A';
5337 l_rev_adj_rec.amount := l_amount_pending;
5338
5339 adjust_revenue(
5340 p_mode => c_earn_revenue,
5341 p_customer_trx_id => l_customer_trx_id,
5342 p_customer_trx_line_id => p_cust_trx_line_id,
5343 p_acctd_amount => l_acctd_amount_pending,
5344 p_ram_desc_flexfield => l_ram_desc_flexfield,
5345 p_rev_adj_rec => l_rev_adj_rec,
5346 p_gl_date => p_event_date,
5347 x_adjustment_number => l_adjustment_number,
5348 x_return_status => l_return_status,
5349 x_msg_count => l_msg_count,
5350 x_msg_data => l_msg_data);
5351
5352 END IF;
5353
5354 update_deferred_lines (
5355 p_customer_trx_line_id => p_cust_trx_line_id,
5356 p_line_status => l_line_status,
5357 p_amount_recognized => l_amount_pending,
5358 p_acctd_amount_recognized => l_acctd_amount_pending,
5359 p_amount_pending => 0,
5360 p_acctd_amount_pending => 0);
5361
5362 END IF;
5363
5364 END process_event;
5365
5366
5367 /*========================================================================
5368 | PUBLIC PROCEDURE revenue_synchronizer
5369 |
5370 | DESCRIPTION
5371 | This procedure takes care of all the manual revenue events such as
5372 | acceptance and manual revenue adjustments from the RAM screens. This way
5373 | our revenue management tables are never out of sync. Here, the mode
5374 | indicates which event has occurred e.g. Acceptance or Manual adjustments.
5375 |
5376 | CALLED FROM PROCEDURES/FUNCTIONS
5377 |
5378 | This is called from RAM Wizard in the application.
5379 |
5380 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
5381 | manual_overide
5382 | review_line_collectibility
5383 | adjust_revenue
5384 | update_deferred_lines
5385 |
5386 | PARAMETERS
5387 | p_mode
5388 | p_customer_trx_id
5389 | p_customer_trx_line_id
5390 | p_gl_date
5391 | p_comments
5392 | p_ram_desc_flexfield
5393 |
5394 | NOTES
5395 | This procedure will be called for any RAM adjustments done any where in
5396 | the system. A new field has been added to RAM record structure called
5397 | source. If the source is not this package
5398 | (c_source_revenue_management_source) then a call will be placed here
5399 | to indicate manual override.
5400 |
5401 | MODIFICATION HISTORY
5402 | Date Author Description of Changes
5403 | 26-JUL-2002 ORASHID Subroutine Created
5404 | 26-APR-2006 MRAYMOND 5043785 - Added logic to prevent
5405 | child insert and OM call when
5406 | acceptance contingency is not
5407 | sourced from OM
5408 |
5409 *=======================================================================*/
5410
5411 PROCEDURE revenue_synchronizer (
5412 p_mode IN NUMBER,
5413 p_customer_trx_id IN NUMBER,
5414 p_customer_trx_line_id IN NUMBER,
5415 p_gl_date IN DATE,
5416 p_comments IN VARCHAR2,
5417 p_ram_desc_flexfield IN desc_flexfield,
5418 x_scenario OUT NOCOPY NUMBER,
5419 x_first_adjustment_number OUT NOCOPY NUMBER,
5420 x_last_adjustment_number OUT NOCOPY NUMBER,
5421 x_return_status OUT NOCOPY VARCHAR2,
5422 x_msg_count OUT NOCOPY NUMBER,
5423 x_msg_data OUT NOCOPY VARCHAR2) IS
5424
5425
5426 /*-----------------------------------------------------------------------+
5427 | Cursor Declarations |
5428 +-----------------------------------------------------------------------*/
5429
5430 -- This cursor retrieves all the valid rows from the revenue
5431 -- management tables.
5432
5433 CURSOR rev_lines IS
5434 SELECT customer_trx_line_id,
5435 customer_trx_id,
5436 amount_due_original,
5437 acctd_amount_due_original,
5438 amount_recognized,
5439 acctd_amount_recognized,
5440 amount_pending,
5441 acctd_amount_pending
5442 FROM ar_reviewed_lines_gt
5443 WHERE request_id = nvl(p_customer_trx_line_id, -- 7328069
5444 nvl(p_customer_trx_id,
5445 request_id));
5446
5447
5448 /*-----------------------------------------------------------------------+
5449 | Local Variable Declarations and initializations |
5450 +-----------------------------------------------------------------------*/
5451
5452 lr_customer_trx_id_tbl number_table;
5453 lr_customer_trx_line_id_tbl number_table;
5454 lr_line_collectible_tbl varchar_table;
5455 lr_amount_due_original_tbl number_table;
5456 lr_amount_recognized_tbl number_table;
5457 lr_amount_pending_tbl number_table;
5458 lr_acctd_amount_due_orig_tbl number_table;
5459 lr_acctd_amt_recognized_tbl number_table;
5460 lr_acctd_amount_pending_tbl number_table;
5461
5462 l_partially_recognized BOOLEAN;
5463 l_fully_recognized BOOLEAN DEFAULT TRUE;
5464 l_not_recognized BOOLEAN DEFAULT TRUE;
5465
5466 l_first_adjustment BOOLEAN DEFAULT TRUE;
5467 l_last_fetch BOOLEAN;
5468 l_line_status NUMBER;
5469 l_adjustment_number NUMBER;
5470 l_rev_adj_rec ar_revenue_adjustment_pvt.rev_adj_rec_type;
5471
5472 l_return_status VARCHAR2(30);
5473 l_msg_count NUMBER;
5474 l_msg_data VARCHAR2(150);
5475
5476 BEGIN
5477
5478 IF pg_debug IN ('Y', 'C') THEN
5479 debug('ar_revenue_management_pvt.revenue_synchronizer +');
5480 debug('** revenue_synchronizer parameters **');
5481 debug(' p_mode : ' || p_mode);
5482 debug(' p_customer_trx_id : ' || p_customer_trx_id);
5483 debug(' p_customer_trx_line_id : ' ||
5484 p_customer_trx_line_id);
5485 debug(' p_gl_date : ' || p_gl_date);
5486 debug(' p_comments : ' || p_comments);
5487 END IF;
5488
5489 x_return_status := fnd_api.g_ret_sts_success;
5490
5491 IF (p_mode = c_manual_override_mode) THEN
5492
5493 IF pg_debug IN ('Y', 'C') THEN
5494 debug('called in manual override mode');
5495 END IF;
5496
5497 manual_override(
5498 p_customer_trx_id => p_customer_trx_id,
5499 p_customer_trx_line_id => p_customer_trx_line_id);
5500
5501 ELSE
5502
5503 IF (p_mode = c_acceptance_obtained_mode) THEN
5504
5505 IF pg_debug IN ('Y', 'C') THEN
5506 debug('called in acceptance obtained mode');
5507 END IF;
5508
5509 populate_acceptance_rows(
5510 p_customer_trx_id => p_customer_trx_id,
5511 p_customer_trx_line_id => p_customer_trx_line_id,
5512 p_mode => 'RECORD');
5513
5514 /* 5043785 - Both populate_child_rows and record_acceptance_with_om
5515 are now smart enough to not process when the lines
5516 did not originate from Oracle's Order Management
5517 product */
5518
5519 populate_child_rows(
5520 p_customer_trx_id => p_customer_trx_id,
5521 p_customer_trx_line_id => p_customer_trx_line_id);
5522
5523 record_acceptance_with_om(
5524 p_called_from => 'WIZARD',
5525 p_customer_trx_id => p_customer_trx_id,
5526 p_cust_trx_line_id => p_customer_trx_line_id,
5527 p_date_accepted => p_gl_date, -- 13482797
5528 x_return_status => l_return_status,
5529 x_msg_count => l_msg_count,
5530 x_msg_data => l_msg_data);
5531
5532 ELSE
5533
5534 -- populate expiring contingencies for this line(s)
5535 -- this is called from RAM Wizard when user modifies
5536 -- contingencies. RAM Wizard would call the subroutine
5537 -- update_line_conts first before calling the synchornize
5538
5539 populate_other_rows(
5540 p_customer_trx_id => p_customer_trx_id,
5541 p_customer_trx_line_id => p_customer_trx_line_id,
5542 p_mode => 'UPDATE');
5543
5544 -- if the user deletes the last contingency
5545 populate_no_contingency_rows(
5546 p_customer_trx_id => p_customer_trx_id,
5547 p_customer_trx_line_id => p_customer_trx_line_id);
5548
5549
5550 END IF;
5551
5552 -- open the lines identified to have updated contingencies
5553 OPEN rev_lines;
5554 LOOP
5555
5556 debug('inside loop');
5557
5558 -- otherwise the row count may not be zero
5559 -- and we will be stuck in an infinite loop.
5560
5561 lr_customer_trx_line_id_tbl.delete;
5562 lr_customer_trx_id_tbl.delete;
5563 lr_amount_due_original_tbl.delete;
5564 lr_acctd_amount_due_orig_tbl.delete;
5565 lr_amount_recognized_tbl.delete;
5566 lr_acctd_amt_recognized_tbl.delete;
5567 lr_amount_pending_tbl.delete;
5568 lr_acctd_amount_pending_tbl.delete;
5569
5570 FETCH rev_lines BULK COLLECT INTO
5571 lr_customer_trx_line_id_tbl,
5572 lr_customer_trx_id_tbl,
5573 lr_amount_due_original_tbl,
5574 lr_acctd_amount_due_orig_tbl,
5575 lr_amount_recognized_tbl,
5576 lr_acctd_amt_recognized_tbl,
5577 lr_amount_pending_tbl,
5578 lr_acctd_amount_pending_tbl
5579 LIMIT c_max_bulk_fetch_size;
5580
5581 IF rev_lines%NOTFOUND THEN
5582 IF pg_debug IN ('Y', 'C') THEN
5583 debug('rev_lines%NOTFOUND');
5584 END IF;
5585 l_last_fetch := TRUE;
5586 END IF;
5587
5588 debug('inside loop: ' || lr_customer_trx_line_id_tbl.COUNT);
5589 IF lr_customer_trx_line_id_tbl.COUNT = 0 AND l_last_fetch THEN
5590 IF pg_debug IN ('Y', 'C') THEN
5591 debug('No more rows');
5592 END IF;
5593 EXIT;
5594 END IF;
5595
5596 FOR i IN lr_customer_trx_line_id_tbl.FIRST ..
5597 lr_customer_trx_line_id_tbl.LAST LOOP
5598
5599 IF pg_debug IN ('Y', 'C') THEN
5600 debug('Revenue Synchronizer Loop - Line ID: ' ||
5601 lr_customer_trx_line_id_tbl(i));
5602 END IF;
5603
5604 IF (p_mode = c_acceptance_obtained_mode) THEN
5605 update_for_event(
5606 p_cust_trx_line_id => lr_customer_trx_line_id_tbl(i),
5607 p_event_date => sysdate,
5608 p_event_code => 'CUSTOMER_ACCEPTANCE');
5609 END IF;
5610
5611 ----------------------------------------------------------------------
5612 -- This is a call to a procedure that will look into each
5613 -- line level deferral reason and compare the current
5614 -- date with expiry date for each. This will return
5615 -- current status of each deferral reason, and give
5616 -- overall verdict for this line.
5617 ----------------------------------------------------------------------
5618
5619 l_line_status := get_line_status (
5620 p_cust_trx_line_id => lr_customer_trx_line_id_tbl(i));
5621
5622 debug('line status: ' || l_line_status);
5623
5624 IF (l_line_status = c_recognizable) THEN
5625
5626 -- acceptance was the only hang up after all the expirations have
5627 -- been re-evaluated, so we can now recognize reveneue for this
5628 -- line.
5629
5630 IF pg_debug IN ('Y', 'C') THEN
5631 debug('no issues remain');
5632 END IF;
5633
5634 l_rev_adj_rec.line_selection_mode := 'S';
5635 l_rev_adj_rec.from_cust_trx_line_id :=
5636 lr_customer_trx_line_id_tbl(i);
5637 l_rev_adj_rec.customer_trx_id := p_customer_trx_id;
5638 l_rev_adj_rec.gl_date := p_gl_date; -- 7158075
5639 l_rev_adj_rec.reason_code := 'REV_MGMT_ENGINE';
5640 l_rev_adj_rec.amount_mode := 'A';
5641 l_rev_adj_rec.amount := lr_amount_due_original_tbl(i);
5642
5643 adjust_revenue(
5644 p_mode => c_earn_revenue,
5645 p_customer_trx_id => p_customer_trx_id,
5646 p_customer_trx_line_id => p_customer_trx_line_id,
5647 p_acctd_amount => lr_acctd_amount_due_orig_tbl(i),
5648 p_ram_desc_flexfield => p_ram_desc_flexfield,
5649 p_gl_date => p_gl_date, -- 7158075
5650 p_rev_adj_rec => l_rev_adj_rec,
5651 p_comments => p_comments,
5652 x_adjustment_number => l_adjustment_number,
5653 x_return_status => x_return_status,
5654 x_msg_count => x_msg_count,
5655 x_msg_data => x_msg_data);
5656
5657 IF pg_debug IN ('Y', 'C') THEN
5658 debug('Revenue adjusted and now updating rvmg tables');
5659 END IF;
5660
5661 update_deferred_lines (
5662 p_customer_trx_line_id => lr_customer_trx_line_id_tbl(i),
5663 p_line_status => l_line_status,
5664 p_amount_recognized => lr_amount_due_original_tbl(i),
5665 p_acctd_amount_recognized => lr_acctd_amount_due_orig_tbl(i),
5666 p_amount_pending => 0,
5667 p_acctd_amount_pending => 0);
5668
5669 -- since at least one line of this invoice is recognized, the
5670 -- flag that says nothing is recognized should be turned off.
5671 l_not_recognized := FALSE;
5672 IF NOT (lr_amount_pending_tbl(i) =
5673 lr_amount_due_original_tbl(i) ) THEN
5674 -- since a partial amount of a line is recognized then
5675 -- flags indicating all or nothing is recognized should be
5676 -- turned off and instead the partial flag must be turned on.
5677 l_partially_recognized := TRUE;
5678 END IF;
5679
5680
5681 ELSIF (l_line_status = c_cash_based) THEN
5682
5683 --------------------------------------------------------------------
5684 -- acceptance was the only hang up or some line level problems
5685 -- along with acceptance were the problems, but they are now
5686 -- alleviated, so we can now recognize reveneue for this line.
5687 -- However, credit problem still remain, so we can recognize only
5688 -- upto the amount already applied. So, if there is anything in the
5689 -- pending column we should recognize that much. |
5690 --------------------------------------------------------------------
5691
5692 IF pg_debug IN ('Y', 'C') THEN
5693 debug('only header issues remain');
5694 END IF;
5695
5696 IF (lr_amount_pending_tbl(i) > 0) THEN
5697
5698 IF pg_debug IN ('Y', 'C') THEN
5699 debug('amount pending is greater than zero');
5700 END IF;
5701
5702 l_rev_adj_rec.line_selection_mode := 'S';
5703 l_rev_adj_rec.from_cust_trx_line_id :=
5704 lr_customer_trx_line_id_tbl(i);
5705 l_rev_adj_rec.customer_trx_id := p_customer_trx_id;
5706 l_rev_adj_rec.gl_date := p_gl_date; -- 7158075
5707 l_rev_adj_rec.reason_code := 'REV_MGMT_ENGINE';
5708 l_rev_adj_rec.amount_mode := 'A';
5709 l_rev_adj_rec.amount := lr_amount_pending_tbl(i);
5710
5711 adjust_revenue(
5712 p_mode => c_earn_revenue,
5713 p_customer_trx_id => p_customer_trx_id,
5714 p_customer_trx_line_id => p_customer_trx_line_id,
5715 p_acctd_amount => lr_acctd_amount_pending_tbl(i),
5716 p_ram_desc_flexfield => p_ram_desc_flexfield,
5717 p_gl_date => p_gl_date, -- 7158075
5718 p_rev_adj_rec => l_rev_adj_rec,
5719 p_comments => p_comments,
5720 x_adjustment_number => l_adjustment_number,
5721 x_return_status => x_return_status,
5722 x_msg_count => x_msg_count,
5723 x_msg_data => x_msg_data);
5724
5725 -- since at least one line of this invoice is recognized, the
5726 -- flag that says nothing is recognized should be turned off.
5727 l_not_recognized := FALSE;
5728
5729 IF NOT (lr_amount_pending_tbl(i) =
5730 lr_amount_due_original_tbl(i) ) THEN
5731
5732 -- since a partial amount of a line is recognized then
5733 -- flags indicating all or nothing is recognized should be
5734 -- turned off and instead the partial flag must be turned on.
5735 l_not_recognized := FALSE;
5736 l_fully_recognized := FALSE;
5737 l_partially_recognized := TRUE;
5738
5739 END IF;
5740
5741 ELSE
5742
5743 -- since at least one line of this invoice was not fully
5744 -- recognized, the flag that says all recognized should be
5745 -- turned off.
5746
5747 IF pg_debug IN ('Y', 'C') THEN
5748 debug('amount pending is NOT greater than zero');
5749 END IF;
5750 l_fully_recognized := FALSE;
5751
5752 END IF;
5753
5754 update_deferred_lines (
5755 p_customer_trx_line_id => lr_customer_trx_line_id_tbl(i),
5756 p_line_status => l_line_status,
5757 p_amount_recognized => lr_amount_pending_tbl(i),
5758 p_acctd_amount_recognized => lr_acctd_amount_pending_tbl(i),
5759 p_amount_pending => 0,
5760 p_acctd_amount_pending => 0);
5761
5762 ELSE
5763
5764 -- Simply record acceptance any other line level updates.
5765 -- we can not recognize revenue because there exists at least
5766 -- one reason for deferral.
5767
5768 IF pg_debug IN ('Y', 'C') THEN
5769 debug('other line level issues remain');
5770 END IF;
5771
5772 l_fully_recognized := FALSE;
5773
5774 END IF;
5775
5776
5777 -- We need to track the first and the last adjustment number
5778 -- because this will be used in RAM results window. These values
5779 -- serve as the lower and upper limit values for the BETWEEN clause.
5780
5781 -- l_adjustment_number is will not be null when a revenue adjustment
5782 -- actually takes place.
5783
5784 IF (l_adjustment_number IS NOT NULL) THEN
5785
5786 IF (l_first_adjustment) THEN
5787
5788 -- for the first time we want to make sure the last one has the
5789 -- value same as the first one, so that if this is the last
5790 -- adjustment in this run, the between clause will still work.
5791
5792 IF pg_debug IN ('Y', 'C') THEN
5793 debug('first adjustment' || l_adjustment_number);
5794 END IF;
5795 x_first_adjustment_number := l_adjustment_number;
5796 x_last_adjustment_number := l_adjustment_number;
5797 l_first_adjustment := FALSE;
5798
5799 ELSE
5800
5801 -- from now on simply move the upper limit along.
5802
5803 IF pg_debug IN ('Y', 'C') THEN
5804 debug('last adjustment' || l_adjustment_number);
5805 END IF;
5806 x_last_adjustment_number := l_adjustment_number;
5807
5808 END IF;
5809
5810 -- reset the variable to null, we will keep getting in and re-assign
5811 -- same number over and over again.
5812
5813 l_adjustment_number := null;
5814
5815 END IF;
5816
5817 END LOOP;
5818
5819 END LOOP;
5820
5821 -- it is possible that there are no rows in ar_reviewed_lines_gt
5822 -- in that no revenue will be recognized.
5823 IF (x_first_adjustment_number IS NULL) AND
5824 (x_last_adjustment_number IS NULL) THEN
5825 x_scenario := c_not_recognized;
5826 RETURN;
5827 END IF;
5828
5829 IF (l_fully_recognized) THEN
5830 IF pg_debug IN ('Y', 'C') THEN
5831 debug('fully recognized');
5832 END IF;
5833 x_scenario := c_fully_recognized;
5834 ELSIF (l_partially_recognized) THEN
5835 IF pg_debug IN ('Y', 'C') THEN
5836 debug('partially recognized');
5837 END IF;
5838 x_scenario := c_partially_recognized;
5839 ELSE
5840 IF pg_debug IN ('Y', 'C') THEN
5841 debug('not recognized');
5842 END IF;
5843 x_scenario := c_not_recognized;
5844 END IF;
5845
5846 END IF;
5847
5848 IF pg_debug IN ('Y', 'C') THEN
5849 debug('ar_revenue_management_pvt.revenue_synchronizer()-');
5850 END IF;
5851
5852 EXCEPTION
5853
5854 WHEN NO_DATA_FOUND THEN
5855 IF pg_debug IN ('Y', 'C') THEN
5856 debug('NO_DATA_FOUND: revenue_synchronizer');
5857 debug(sqlerrm);
5858 END IF;
5859 RAISE;
5860
5861 WHEN OTHERS THEN
5862 IF pg_debug IN ('Y', 'C') THEN
5863 debug('OTHERS: revenue_synchronizer');
5864 debug(sqlerrm);
5865 END IF;
5866 RAISE;
5867
5868 END revenue_synchronizer;
5869
5870 /*========================================================================
5871 | PUBLIC PROCEDURE periodic_sweeper
5872 |
5873 | DESCRIPTION
5874 | This procedure re-evaluates collectibility for alrady deferred invoices.
5875 |
5876 | CALLED FROM PROCEDURES/FUNCTIONS
5877 | This procedure is called from a concurrent program named ARREVSWP.
5878 |
5879 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
5880 |
5881 | review_line_collectibility
5882 | get_line_status
5883 | adjust_revenue
5884 | update_deferred_lines
5885 |
5886 | PARAMETERS
5887 | None.
5888 |
5889 | NOTES
5890 | Note that creditworthiness of a customer will be never be checked again.
5891 | This function only checks for expiration.
5892 |
5893 | MODIFICATION HISTORY
5894 | Date Author Description of Changes
5895 | 26-JUL-2002 ORASHID Subroutine Created
5896 |
5897 | 31-OCT-2005 APANDIT Enabling this conc program to
5898 | be run as multi-org
5899 | 03-FEB-2006 JBECKETT Bug 4757939 - Added org_id parameter
5900 *=======================================================================*/
5901
5902 PROCEDURE periodic_sweeper (
5903 errbuf OUT NOCOPY VARCHAR2,
5904 retcode OUT NOCOPY VARCHAR2,
5905 p_org_id IN NUMBER) IS
5906
5907 /*-----------------------------------------------------------------------+
5908 | Cursor Declarations |
5909 +-----------------------------------------------------------------------*/
5910
5911 -- This cursor retrieves all the lines which are contingency based
5912 CURSOR expiring_lines (p_request_id NUMBER) IS
5913 SELECT customer_trx_line_id,
5914 MAX(customer_trx_id),
5915 MAX(amount_due_original),
5916 MAX(acctd_amount_due_original),
5917 MAX(amount_recognized),
5918 MAX(acctd_amount_recognized),
5919 MAX(amount_pending),
5920 MAX(acctd_amount_pending),
5921 MAX(expiration_date)
5922 FROM ar_reviewed_lines_gt
5923 WHERE request_id = p_request_id
5924 GROUP BY customer_trx_line_id;
5925
5926 /*Bug 4675710 */
5927 CURSOR cur_orgs IS
5928 SELECT org_id FROM ar_system_parameters
5929 WHERE org_id = NVL(p_org_id,org_id);
5930
5931 /*-----------------------------------------------------------------------+
5932 | Local Variable Declarations and initializations |
5933 +-----------------------------------------------------------------------*/
5934
5935 l_return_status VARCHAR2(30);
5936 l_msg_count NUMBER;
5937 l_msg_data VARCHAR2(150);
5938
5939 l_last_fetch BOOLEAN;
5940 l_line_status NUMBER;
5941 l_adjustment_number NUMBER;
5942
5943 l_rev_adj_rec ar_revenue_adjustment_pvt.rev_adj_rec_type;
5944 l_ram_desc_flexfield desc_flexfield;
5945
5946 lr_customer_trx_line_id_tbl number_table;
5947 lr_customer_trx_id_tbl number_table;
5948 lr_line_collectible_tbl varchar_table;
5949 lr_amount_due_original_tbl number_table;
5950 lr_amount_recognized_tbl number_table;
5951 lr_amount_pending_tbl number_table;
5952 lr_acctd_amount_due_orig_tbl number_table;
5953 lr_acctd_amt_recognized_tbl number_table;
5954 lr_acctd_amount_pending_tbl number_table;
5955 lr_expiration_date_tbl date_table;
5956
5957
5958 l_request_id NUMBER;
5959 l_hold_trx_id NUMBER;
5960 l_trx_number NUMBER;
5961 return_warning BOOLEAN;
5962 error_message VARCHAR2(50);
5963
5964 BEGIN
5965
5966 debug('ar_revenue_management_pvt.periodic_sweeper +');
5967
5968 retcode := SUCCESS;
5969
5970 l_request_id := fnd_global.conc_request_id;
5971
5972 /**allows to skip certain steps like rounding from the standard flow and
5973 process the same in bulk mode for better performance. */
5974 AR_RAAPI_UTIL.g_called_from := 'SWEEPER';
5975
5976 FOR morgs in cur_orgs
5977 LOOP
5978 mo_global.set_policy_context('S',morgs.org_id);
5979 /* Bug fix 5351734
5980 Delete the data in the global temporary tables */
5981 delete from ar_rdr_parameters_gt;
5982 delete from ar_trx_errors_gt;
5983 delete from ar_trx_header_gt;
5984 delete from ar_trx_lines_gt;
5985 delete from ar_reviewed_lines_gt;
5986 delete from fun_rule_bulk_result_gt;
5987 delete from ar_trx_contingencies_gt;
5988
5989 populate_acceptance_rows;
5990 populate_child_rows;
5991
5992 record_acceptance_with_om (
5993 p_called_from => 'SWEEPER',
5994 p_request_id => l_request_id,
5995 x_return_status => l_return_status,
5996 x_msg_count => l_msg_count,
5997 x_msg_data => l_msg_data);
5998
5999 populate_other_rows;
6000
6001 debug('about to open deferred lines');
6002
6003 OPEN expiring_lines(l_request_id);
6004 LOOP
6005
6006 -- this table must be deleted for re-entry
6007 -- otherwise the row count may not be zero
6008 -- and we will be stuck in an infinite loop.
6009
6010 lr_customer_trx_line_id_tbl.delete;
6011 lr_customer_trx_id_tbl.delete;
6012 lr_amount_due_original_tbl.delete;
6013 lr_acctd_amount_due_orig_tbl.delete;
6014 lr_amount_recognized_tbl.delete;
6015 lr_acctd_amt_recognized_tbl.delete;
6016 lr_amount_pending_tbl.delete;
6017 lr_acctd_amount_pending_tbl.delete;
6018 lr_expiration_date_tbl.delete;
6019
6020 FETCH expiring_lines BULK COLLECT INTO
6021 lr_customer_trx_line_id_tbl,
6022 lr_customer_trx_id_tbl,
6023 lr_amount_due_original_tbl,
6024 lr_acctd_amount_due_orig_tbl,
6025 lr_amount_recognized_tbl,
6026 lr_acctd_amt_recognized_tbl,
6027 lr_amount_pending_tbl,
6028 lr_acctd_amount_pending_tbl,
6029 lr_expiration_date_tbl
6030 LIMIT c_max_bulk_fetch_size;
6031
6032 IF expiring_lines%NOTFOUND THEN
6033 l_last_fetch := TRUE;
6034 END IF;
6035
6036 IF lr_customer_trx_line_id_tbl.COUNT = 0 AND l_last_fetch THEN
6037 debug('last fetch and COUNT equals zero');
6038 EXIT;
6039 END IF;
6040
6041 debug('Periodic Sweeper: about to enter the loop');
6042 debug('Count: ' || lr_customer_trx_line_id_tbl.COUNT);
6043 debug('First: ' || lr_customer_trx_line_id_tbl.FIRST);
6044 debug('Last: ' || lr_customer_trx_line_id_tbl.LAST);
6045
6046 FOR i IN lr_customer_trx_line_id_tbl.FIRST ..
6047 lr_customer_trx_line_id_tbl.LAST LOOP
6048
6049 debug('Periodic Sweeper Loop - Line ID: ' ||
6050 lr_customer_trx_line_id_tbl(i));
6051
6052 -- re-evaluate each reason for line level and determine
6053 -- line level collectibility. The following function
6054 -- determines the status of line. It will
6055 -- indicate what kind of issues remain.
6056
6057 savepoint s1;
6058
6059 l_line_status := get_line_status (
6060 p_cust_trx_line_id => lr_customer_trx_line_id_tbl(i));
6061
6062 debug('scenario : ' || l_line_status);
6063
6064 l_rev_adj_rec.from_cust_trx_line_id := lr_customer_trx_line_id_tbl(i);
6065 l_rev_adj_rec.customer_trx_id := lr_customer_trx_id_tbl(i);
6066 l_rev_adj_rec.line_selection_mode := 'S';
6067 l_rev_adj_rec.reason_code := 'REV_MGMT_ENGINE';
6068 l_rev_adj_rec.amount_mode := 'A';
6069
6070 /* 7449886 - lr_expiration_date_tbl(i) will only have a value if
6071 there was a contingency with an expiration date
6072 that was <= sysdate. If this is the case, use
6073 the expiration_date as the gl_date */
6074 IF lr_expiration_date_tbl(i) IS NOT NULL
6075 THEN
6076 l_rev_adj_rec.gl_date := lr_expiration_date_tbl(i);
6077 debug(' expiration date = ' || lr_expiration_date_tbl(i));
6078 ELSE
6079 l_rev_adj_rec.gl_date := trunc(sysdate);
6080 END IF;
6081
6082 IF (l_line_status = c_recognizable) THEN
6083
6084 debug('no issues remain');
6085
6086 l_rev_adj_rec.amount := lr_amount_due_original_tbl(i);
6087
6088 debug('Amount Adjusted: ' || lr_amount_due_original_tbl(i));
6089 debug('Acctd Amount Adjusted: ' || lr_acctd_amount_due_orig_tbl(i));
6090
6091 lr_amount_recognized_tbl(i) := lr_amount_due_original_tbl(i);
6092 lr_acctd_amt_recognized_tbl(i) := lr_acctd_amount_due_orig_tbl(i);
6093 lr_amount_pending_tbl(i) := 0;
6094 lr_acctd_amount_pending_tbl(i) := 0;
6095
6096 BEGIN
6097
6098 debug('calling RAM API');
6099
6100 adjust_revenue(
6101 p_mode => c_earn_revenue,
6102 p_customer_trx_id => lr_customer_trx_id_tbl(i),
6103 p_customer_trx_line_id => lr_customer_trx_line_id_tbl(i),
6104 p_acctd_amount => lr_acctd_amount_due_orig_tbl(i),
6105 p_ram_desc_flexfield => l_ram_desc_flexfield,
6106 p_rev_adj_rec => l_rev_adj_rec,
6107 p_gl_date => l_rev_adj_rec.gl_date,
6108 x_adjustment_number => l_adjustment_number,
6109 x_return_status => l_return_status,
6110 x_msg_count => l_msg_count,
6111 x_msg_data => l_msg_data);
6112
6113 debug('returned from RAM API');
6114
6115 EXCEPTION
6116 WHEN OTHERS THEN
6117 return_warning := TRUE;
6118 rollback to s1;
6119
6120 IF nvl(l_hold_trx_id,-99) <> lr_customer_trx_id_tbl(i)
6121 THEN
6122 /* get trx_number */
6123 select trx_number
6124 into l_trx_number
6125 from ra_customer_trx
6126 where customer_trx_id = lr_customer_trx_id_tbl(i);
6127
6128 l_hold_trx_id := lr_customer_trx_id_tbl(i);
6129
6130 fnd_file.put_line(FND_FILE.LOG, 'trx_number ' ||
6131 l_trx_number
6132 || ' had problems generating revenue.');
6133 END IF;
6134 GOTO continue_loop;
6135 END;
6136
6137 ELSIF (l_line_status = c_cash_based) THEN
6138
6139 debug('cash based scenario');
6140
6141 IF (lr_amount_pending_tbl(i) > 0) THEN
6142
6143 -- now the only hang up is header level. So, whatever is sitting
6144 -- in the pending column, must now be recognized.
6145
6146 debug('pending amount being recognized');
6147
6148 l_rev_adj_rec.amount := lr_amount_pending_tbl(i);
6149
6150 debug('Amount Adjusted: ' || lr_amount_pending_tbl(i));
6151
6152 lr_amount_recognized_tbl(i) := lr_amount_pending_tbl(i);
6153 lr_acctd_amt_recognized_tbl(i) := lr_acctd_amount_pending_tbl(i);
6154 lr_amount_pending_tbl(i) := 0;
6155 lr_acctd_amount_pending_tbl(i) := 0;
6156
6157 BEGIN
6158 savepoint s2;
6159 debug('calling RAM API');
6160
6161 adjust_revenue(
6162 p_mode => c_earn_revenue,
6163 p_customer_trx_id => lr_customer_trx_id_tbl(i),
6164 p_customer_trx_line_id => lr_customer_trx_line_id_tbl(i),
6165 p_acctd_amount => lr_acctd_amount_due_orig_tbl(i),
6166 p_ram_desc_flexfield => l_ram_desc_flexfield,
6167 p_rev_adj_rec => l_rev_adj_rec,
6168 p_gl_date => l_rev_adj_rec.gl_date,
6169 x_adjustment_number => l_adjustment_number,
6170 x_return_status => l_return_status,
6171 x_msg_count => l_msg_count,
6172 x_msg_data => l_msg_data);
6173
6174 debug('returned from RAM API');
6175
6176 EXCEPTION
6177 WHEN OTHERS THEN
6178
6179
6180 return_warning := TRUE;
6181 rollback to s2;
6182
6183 IF nvl(l_hold_trx_id,-99) <> lr_customer_trx_id_tbl(i)
6184 THEN
6185 /* get trx_number */
6186 select trx_number
6187 into l_trx_number
6188 from ra_customer_trx
6189 where customer_trx_id = lr_customer_trx_id_tbl(i);
6190
6191 l_hold_trx_id := lr_customer_trx_id_tbl(i);
6192
6193 fnd_file.put_line(FND_FILE.LOG, 'trx_number ' ||
6194 l_trx_number
6195 || ' had problems generating revenue.' );
6196 END IF;
6197 GOTO continue_loop;
6198 END;
6199
6200 END IF;
6201
6202 END IF;
6203
6204 debug('update rvmg table');
6205
6206 update_deferred_lines (
6207 p_customer_trx_line_id => lr_customer_trx_line_id_tbl(i),
6208 p_line_status => l_line_status,
6209 p_amount_recognized => lr_amount_recognized_tbl(i),
6210 p_acctd_amount_recognized => lr_acctd_amt_recognized_tbl(i),
6211 p_amount_pending => lr_amount_pending_tbl(i),
6212 p_acctd_amount_pending => lr_acctd_amount_pending_tbl(i));
6213
6214 -- l_old_customer_trx_id := lr_customer_trx_id_tbl(i);
6215 <<continue_loop>>
6216 debug('done for the line');
6217
6218 END LOOP; -- FIRST .. lr_customer_trx_line_id_tbl.LAST
6219
6220 END LOOP; -- bulk collect
6221
6222 CLOSE expiring_lines;
6223
6224 /** Revenue Adjustment API will not call rounding logic if the call is from sweeper program (based
6225 on package global value AR_RAAPI_UTIL.g_called_from passed as SWEEPER),this call will round
6226 each adjustment that is recorded in ar_rev_line_adj_gt */
6227 IF (arp_rounding.correct_rev_adj_by_line = 0) THEN
6228 arp_util.debug('ERROR: arp_rounding.correct_rev_adj_by_line');
6229 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
6230 END IF;
6231
6232 END LOOP; -- cur_orgs Bug4675710
6233
6234 retcode := SUCCESS;
6235
6236 IF (return_warning)
6237 THEN
6238 debug('Attempting to set WARNING return status');
6239 error_message := FND_MESSAGE.GET_STRING('AR','AR_UNABLE_TO_GEN_DEF_ACCG');
6240
6241 IF (FND_CONCURRENT.SET_COMPLETION_STATUS('WARNING', error_message) = FALSE)
6242 THEN
6243 debug('Unable to set WARNING return status');
6244 END IF;
6245 END IF;
6246
6247 debug('ar_revenue_management_pvt.periodic_sweeper -');
6248
6249 EXCEPTION
6250
6251 WHEN NO_DATA_FOUND THEN
6252 retcode := FAILURE;
6253 errbuf := 'EXCEPTION: NO_DATA_FOUND: periodic_sweeper';
6254 debug('EXCEPTION: NO_DATA_FOUND: periodic_sweeper');
6255 debug(sqlerrm);
6256
6257 RAISE;
6258
6259 WHEN OTHERS THEN
6260 retcode := FAILURE;
6261 errbuf := 'EXCEPTION: OTHERS: periodic_sweeper';
6262 debug('EXCEPTION: OTHERS: periodic_sweeper');
6263 debug(sqlerrm);
6264
6265 RAISE;
6266
6267 END periodic_sweeper;
6268
6269
6270 /*========================================================================
6271 | PUBLIC PROCEDURE receipt_analyzer
6272 |
6273 | DESCRIPTION
6274 | This procedure takes care of receipt applications from collectibility
6275 | perspective. When a receipt is applied, which is an event for
6276 | revenue management engine, this procedure determines if this receipt
6277 | can trigger revenue recognition. In cases where creditworthiness and/or
6278 | payment term was the reason for revenue deferra, it would recognize the
6279 | revenue upto the receipt amount.
6280 |
6281 | CALLED FROM PROCEDURES/FUNCTIONS
6282 | This procedure is called from all the places where receipt is applied.
6283 | 1. receipts api
6284 | 2. receipt application form
6285 | 3. auto receipts
6286 | 4. post batch (lock box)
6287 | 5. confirmation
6288 |
6289 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
6290 |
6291 | get_receipt_parameters
6292 | get_total_application
6293 | get_acctd_total_application
6294 | get_line_status
6295 | compute_line_amount
6296 | update_deferred_lines
6297 | adjust_revenue
6298 |
6299 | PARAMETERS
6300 |
6301 | p_mode
6302 | p_customer_trx_id
6303 | p_acctd_amount_applied
6304 | p_exchange_rate
6305 | p_invoice_currency_code
6306 | p_tax_applied
6307 | p_charges_applied
6308 | p_freight_applied
6309 | p_line_applied
6310 | p_receivable_application_id
6311 |
6312 | KNOWN ISSUES
6313 | Enter business functionality which was de-scoped as part of the
6314 | implementation. Ideally this should never be used.
6315 |
6316 | NOTES
6317 | The receipt analyzer does a variety of things depending on what
6318 | is the scenation it is handling. Below, I give a matrix of what
6319 | it does for future use.
6320 |
6321 | SCENARIO ACTION
6322 |
6323 | Cash Based Recognize Up To The Receipt Amount
6324 | Combination Put In The Pending Column Up to The
6325 | Receipt Amount
6326 | Contingency Based No action, it will be recognized by the
6327 | Peridioc Sweeper.
6328 | Recognize Recognize Fully.
6329 |
6330 | Let me try to give the functional reasoning behind each one of the
6331 | scenario above. The first case is where only problem is a credit
6332 | problem and/or payment term problem. In both cases, the we are
6333 | doubtfult that we may not collect money for it. So, when money
6334 | arrives, we can immediately recognize it.
6335 |
6336 | In the second scneario, it is the very similar to the first one
6337 | however, there may be a non-standard refund clause so we can not
6338 | recognize any revenue until that has expired. At the same time,
6339 | we do not lose track of this receipt. So, we put in pending, and
6340 | as soon as the expiration happens this pending amount will be
6341 | recognized.
6342 |
6343 | Third scenario is the simplest, we do not do anything. Simply because
6344 | when all expire the periodic sweeper is smart enough to recognize the
6345 | entire amount.
6346 |
6347 | The fourth scenario happens when intially there was a non-standard
6348 | refund policy for this line. And just before this receipt arrived,
6349 | this expired, so now we should do the periodc sweeper's job and
6350 | recognize all revenue.
6351 |
6352 |
6353 | MODIFICATION HISTORY
6354 | Date Author Description of Changes
6355 | 26-JUL-2002 ORASHID Subroutine Created
6356 |
6357 *=======================================================================*/
6358
6359 PROCEDURE receipt_analyzer (
6360 p_mode IN VARCHAR2 DEFAULT NULL,
6361 p_customer_trx_id IN NUMBER DEFAULT NULL,
6362 p_acctd_amount_applied IN NUMBER DEFAULT NULL,
6363 p_exchange_rate IN NUMBER DEFAULT NULL,
6364 p_invoice_currency_code IN VARCHAR2 DEFAULT NULL,
6365 p_tax_applied IN NUMBER DEFAULT NULL,
6366 p_charges_applied IN NUMBER DEFAULT NULL,
6367 p_freight_applied IN NUMBER DEFAULT NULL,
6368 p_line_applied IN NUMBER DEFAULT NULL,
6369 p_receivable_application_id IN NUMBER DEFAULT NULL,
6370 p_gl_date IN DATE DEFAULT NULL) IS
6371
6372 /*-----------------------------------------------------------------------+
6373 | Cursor Declarations |
6374 +-----------------------------------------------------------------------*/
6375
6376 -- This cursor retrieves all the deferred lines
6377 /* 9320279 - added CM amounts to cursor */
6378 CURSOR rev_lines (p_trx_id NUMBER) IS
6379 SELECT adl.customer_trx_line_id,
6380 adl.customer_trx_id,
6381 adl.line_collectible_flag,
6382 adl.amount_due_original,
6383 adl.acctd_amount_due_original,
6384 adl.amount_recognized,
6385 adl.acctd_amount_recognized,
6386 adl.amount_pending,
6387 adl.acctd_amount_pending,
6388 SUM(NVL(gld.amount,0)), SUM(NVL(gld.acctd_amount,0)),
6389 SUM(decode(ctrl.customer_trx_id, NULL, 0, 1))
6390 FROM ar_deferred_lines adl,
6391 ra_customer_trx_lines ctrl,
6392 ra_cust_trx_line_gl_dist gld
6393 WHERE adl.customer_trx_id = p_trx_id
6394 AND adl.customer_trx_id = ctrl.previous_customer_trx_id (+)
6395 AND adl.customer_trx_line_id = ctrl.previous_customer_trx_line_id (+)
6396 AND ctrl.customer_trx_line_id = gld.customer_trx_line_id (+)
6397 GROUP BY adl.customer_trx_line_id, adl.customer_trx_id,
6398 adl.line_collectible_flag, adl.amount_due_original,
6399 adl.acctd_amount_due_original, adl.amount_recognized,
6400 adl.acctd_amount_recognized, adl.amount_pending,
6401 adl.acctd_amount_pending;
6402
6403 -- This cursor computes the total balance across lines.
6404
6405 CURSOR amounts (p_trx_id NUMBER) IS
6406 SELECT sum(amount_due_original),
6407 sum(acctd_amount_due_original)
6408 FROM ar_deferred_lines
6409 WHERE customer_trx_id = p_trx_id;
6410
6411 /* 9320279 - added to fetch total CM amounts for proration */
6412 CURSOR cm_amounts (p_trx_id NUMBER) IS
6413 SELECT sum(nvl(amount,0)), sum(nvl(acctd_amount,0))
6414 FROM ra_cust_trx_line_gl_dist gld,
6415 ra_customer_trx ctrx
6416 WHERE ctrx.previous_customer_trx_id = p_trx_id
6417 AND ctrx.customer_trx_id = gld.customer_trx_id
6418 AND account_class in ('REV','UNEARN');
6419
6420 ---This cursor is to try and avoide -ve unearn in a case where the amount being
6421 ---discussed is an overapplication. This will skip the receipt analyzer
6422 CURSOR over_appl (p_trx_id NUMBER) IS
6423 SELECT nvl(allow_overapplication_flag,'N')
6424 FROM ra_cust_trx_types rtt, ra_customer_trx trx
6425 WHERE customer_trx_id = p_trx_id
6426 AND trx.cust_trx_type_id = rtt.cust_trx_type_id;
6427
6428 /*-----------------------------------------------------------------------+
6429 | Local Variable Declarations and initializations |
6430 +-----------------------------------------------------------------------*/
6431
6432 l_acctd_freight_applied ar_receivable_applications.freight_applied%TYPE;
6433 l_acctd_line_applied ar_receivable_applications.line_applied%TYPE;
6434 l_acctd_tax_applied ar_receivable_applications.tax_applied%TYPE;
6435 l_acctd_charges_applied
6436 ar_receivable_applications.receivables_charges_applied%TYPE;
6437
6438 l_freight_applied ar_receivable_applications.freight_applied%TYPE;
6439 l_line_applied ar_receivable_applications.line_applied%TYPE;
6440 l_tax_applied ar_receivable_applications.tax_applied%TYPE;
6441 l_gl_date ar_receivable_applications.gl_date%TYPE;
6442 l_charges_applied
6443 ar_receivable_applications.receivables_charges_applied%TYPE;
6444 l_acctd_amount_applied
6445 ar_receivable_applications.acctd_amount_applied_to%TYPE;
6446
6447 ----------------------
6448 -- rounding related --
6449 ----------------------
6450
6451 l_total_application NUMBER;
6452 l_computed_line_amount NUMBER;
6453 l_current_line_balance NUMBER;
6454 l_sum_of_all_lines NUMBER DEFAULT 0;
6455 l_running_lines_balance NUMBER DEFAULT 0;
6456 l_running_allocated_balance NUMBER DEFAULT 0;
6457
6458 l_acctd_total_application NUMBER;
6459 l_acctd_computed_line_amount NUMBER;
6460 l_acctd_current_line_balance NUMBER;
6461 l_acctd_sum_of_all_lines NUMBER DEFAULT 0;
6462 l_acctd_running_lines_balance NUMBER DEFAULT 0;
6463 l_acctd_running_allocated_bal NUMBER DEFAULT 0;
6464 l_applied_cm_lines NUMBER;
6465 l_applied_acctd_cm_lines NUMBER;
6466
6467 -----------------
6468 -- all flags --
6469 -----------------
6470
6471 l_line_status NUMBER;
6472 l_last_fetch BOOLEAN;
6473
6474 ----------------
6475 -- RAM related
6476 ----------------
6477
6478 l_amount_adjusted NUMBER;
6479 l_acctd_amount_adjusted NUMBER;
6480 l_ram_amount NUMBER;
6481 l_acctd_ram_amount NUMBER;
6482 l_adjustment_number NUMBER;
6483 l_rev_adj_rec ar_revenue_adjustment_pvt.rev_adj_rec_type;
6484 l_ram_desc_flexfield desc_flexfield;
6485
6486 ------------------------------
6487 -- columns from the rvmg table
6488 ------------------------------
6489
6490 lr_customer_trx_line_id_tbl number_table;
6491 lr_customer_trx_id_tbl number_table;
6492 lr_line_collectible_tbl varchar_table;
6493 lr_amount_due_original_tbl number_table;
6494 lr_acctd_amount_due_orig_tbl number_table;
6495 lr_amount_recognized_tbl number_table;
6496 lr_acctd_amt_recognized_tbl number_table;
6497 lr_amount_pending_tbl number_table;
6498 lr_acctd_amount_pending_tbl number_table;
6499 lr_cm_amount_tbl number_table;
6500 lr_cm_acctd_amount_tbl number_table;
6501 lr_cm_exists_tbl number_table;
6502
6503 ---------
6504 -- Misc
6505 ---------
6506
6507 l_customer_trx_id ra_customer_trx.customer_trx_id%TYPE;
6508 l_exchange_rate ra_customer_trx.exchange_rate%TYPE;
6509 l_invoice_currency_code fnd_currencies.currency_code%TYPE;
6510 l_return_status VARCHAR2(30);
6511 l_msg_count NUMBER;
6512 l_msg_data VARCHAR2(150);
6513 l_delta_amount NUMBER; /*6157033*/
6514 l_acctd_delta_amount NUMBER;
6515 l_overappl_flag VARCHAR2(1);
6516 SKIP_RECEIPT_ANALYZER EXCEPTION;
6517 BEGIN
6518
6519 IF pg_debug IN ('Y', 'C') THEN
6520 debug('ar_revenue_management_pvt.receipt_analyzer +');
6521 END IF;
6522
6523 get_receipt_parameters (
6524 p_mode => p_mode,
6525 p_customer_trx_id => p_customer_trx_id,
6526 p_acctd_amount_applied => p_acctd_amount_applied,
6527 p_exchange_rate => p_exchange_rate,
6528 p_invoice_currency_code => p_invoice_currency_code,
6529 p_tax_applied => p_tax_applied,
6530 p_charges_applied => p_charges_applied,
6531 p_freight_applied => p_freight_applied,
6532 p_line_applied => p_line_applied,
6533 p_gl_date => p_gl_date,
6534 p_receivable_application_id => p_receivable_application_id,
6535 x_customer_trx_id => l_customer_trx_id,
6536 x_acctd_amount_applied => l_acctd_amount_applied,
6537 x_exchange_rate => l_exchange_rate,
6538 x_invoice_currency_code => l_invoice_currency_code,
6539 x_tax_applied => l_tax_applied,
6540 x_charges_applied => l_charges_applied,
6541 x_freight_applied => l_freight_applied,
6542 x_line_applied => l_line_applied,
6543 x_gl_date => l_gl_date);
6544
6545 IF NOT monitored_transaction (l_customer_trx_id) THEN
6546 IF pg_debug IN ('Y', 'C') THEN
6547 debug('receipt_analyzer: ' ||
6548 '*** This Transaction Is Not Being Monitored ***');
6549 END IF;
6550 RETURN;
6551 END IF;
6552
6553 IF pg_debug IN ('Y', 'C') THEN
6554 debug('Functional Currency Code : ' || g_currency_code_f);
6555 debug(' Precision : ' || g_precision_f);
6556 debug(' accountable unit : ' ||
6557 g_minimum_accountable_unit_f);
6558 debug('Invoice Currency Code : ' ||
6559 p_invoice_currency_code);
6560 END IF;
6561
6562
6563 ---In reality, we should not be trying to unearn anything in a case where the amount being discussed is the
6564 ---overapplication amount. Receipt analyzer is skipped in this case.
6565 IF l_line_applied < 0 then
6566 OPEN over_appl(l_customer_trx_id);
6567 FETCH over_appl INTO l_overappl_flag;
6568 CLOSE over_appl;
6569 if l_overappl_flag = 'Y' Then
6570 raise SKIP_RECEIPT_ANALYZER;
6571 end if;
6572 END IF;
6573
6574 -- sum of all lines for this transaction
6575
6576 OPEN amounts(l_customer_trx_id);
6577 FETCH amounts INTO l_sum_of_all_lines, l_acctd_sum_of_all_lines;
6578 CLOSE amounts;
6579
6580 OPEN cm_amounts(l_customer_trx_id);
6581 FETCH cm_amounts INTO l_applied_cm_lines, l_applied_acctd_cm_lines;
6582 CLOSE cm_amounts;
6583
6584 IF pg_debug IN ('Y', 'C') THEN
6585 debug('Sum of All INV Lines : ' ||
6586 l_sum_of_all_lines);
6587 debug('Sum of All INV Lines (Accounted) : ' ||
6588 l_acctd_sum_of_all_lines);
6589
6590 debug('Sum of All CM Lines : ' ||
6591 l_applied_cm_lines);
6592 debug('Sum of All CM Lines (Accounted) : ' ||
6593 l_applied_acctd_cm_lines);
6594 END IF;
6595
6596 /* 9320279 - Reduce the total allocatable amount by any applied CMs */
6597 l_sum_of_all_lines := l_sum_of_all_lines + NVL(l_applied_cm_lines,0);
6598 l_acctd_sum_of_all_lines := l_acctd_sum_of_all_lines +
6599 NVL(l_applied_acctd_cm_lines,0);
6600
6601 -- get total receipt application to this transaction so far
6602 -- before this receipt
6603 l_total_application :=
6604 get_total_application (
6605 p_customer_trx_id => l_customer_trx_id);
6606
6607 IF pg_debug IN ('Y', 'C') THEN
6608 debug('Total Application Amount: ' || l_total_application);
6609 END IF;
6610
6611 IF (g_currency_code_f <> p_invoice_currency_code) THEN
6612
6613 -- INVOICE CURRENCY DOES NOT EQUAL FUNCTIONAL CURRENCY, so we must
6614 -- do something special. The l_acctd_amount_applied is the total
6615 -- application amount. It is not divided into freight, charges, line,
6616 -- and tax buckets. So, a call is placed to distribute the amount into the
6617 -- buckets. So that we can figure out what the acctd_line_amount would be.
6618
6619 IF pg_debug IN ('Y', 'C') THEN
6620 debug('Invoice currency and functional currency DIFFER');
6621 END IF;
6622
6623 arp_util.set_buckets(
6624 p_header_acctd_amt => l_acctd_amount_applied,
6625 p_base_currency => g_currency_code_f,
6626 p_exchange_rate => l_exchange_rate,
6627 p_base_precision => g_precision_f,
6628 p_base_min_acc_unit => g_minimum_accountable_unit_f,
6629 p_tax_amt => l_tax_applied,
6630 p_charges_amt => l_charges_applied,
6631 p_line_amt => l_line_applied,
6632 p_freight_amt => l_freight_applied,
6633 p_tax_acctd_amt => l_acctd_tax_applied,
6634 p_charges_acctd_amt => l_acctd_charges_applied,
6635 p_line_acctd_amt => l_acctd_line_applied,
6636 p_freight_acctd_amt => l_acctd_freight_applied);
6637
6638 IF pg_debug IN ('Y', 'C') THEN
6639 debug('Acctd Tax Applied : ' || l_acctd_tax_applied);
6640 debug('Acctd Charges Applied : ' ||
6641 l_acctd_charges_applied);
6642 debug('Acctd Freight Applied : ' ||
6643 l_acctd_freight_applied);
6644 END IF;
6645
6646 -- get acctd total application to this transaction so far.
6647 l_acctd_total_application :=
6648 get_acctd_total_application (
6649 p_customer_trx_id => l_customer_trx_id);
6650
6651 IF pg_debug IN ('Y', 'C') THEN
6652 debug('Total Application Amount (acctd): ' ||
6653 l_acctd_total_application);
6654 END IF;
6655
6656 ELSE
6657
6658 IF pg_debug IN ('Y', 'C') THEN
6659 debug('Invoice currency and functional currency MATCH');
6660 END IF;
6661
6662 l_acctd_line_applied := l_line_applied;
6663 l_acctd_total_application := l_total_application;
6664
6665 END IF;
6666
6667 IF pg_debug IN ('Y', 'C') THEN
6668 debug('Acctd Line Applied : ' || l_acctd_line_applied);
6669 END IF;
6670
6671 OPEN rev_lines(l_customer_trx_id);
6672 LOOP
6673
6674 -- this table must be deleted for re-entry
6675 -- otherwise the row count may not be zero
6676 -- and we will be stuck in an infinite loop.
6677
6678 lr_customer_trx_line_id_tbl.delete;
6679 lr_customer_trx_id_tbl.delete;
6680 lr_line_collectible_tbl.delete;
6681 lr_amount_due_original_tbl.delete;
6682 lr_acctd_amount_due_orig_tbl.delete;
6683 lr_amount_recognized_tbl.delete;
6684 lr_acctd_amt_recognized_tbl.delete;
6685 lr_amount_pending_tbl.delete;
6686 lr_acctd_amount_pending_tbl.delete;
6687 lr_cm_amount_tbl.delete;
6688 lr_cm_acctd_amount_tbl.delete;
6689 lr_cm_exists_tbl.delete;
6690
6691 FETCH rev_lines BULK COLLECT INTO
6692 lr_customer_trx_line_id_tbl,
6693 lr_customer_trx_id_tbl,
6694 lr_line_collectible_tbl,
6695 lr_amount_due_original_tbl,
6696 lr_acctd_amount_due_orig_tbl,
6697 lr_amount_recognized_tbl,
6698 lr_acctd_amt_recognized_tbl,
6699 lr_amount_pending_tbl,
6700 lr_acctd_amount_pending_tbl,
6701 lr_cm_amount_tbl,
6702 lr_cm_acctd_amount_tbl,
6703 lr_cm_exists_tbl
6704 LIMIT c_max_bulk_fetch_size;
6705
6706 IF rev_lines%NOTFOUND THEN
6707 IF pg_debug IN ('Y', 'C') THEN
6708 debug('rev_lines%NOTFOUND');
6709 END IF;
6710 l_last_fetch := TRUE;
6711 END IF;
6712
6713 IF lr_customer_trx_line_id_tbl.COUNT = 0 AND l_last_fetch THEN
6714 IF pg_debug IN ('Y', 'C') THEN
6715 debug('No more rows');
6716 END IF;
6717 EXIT;
6718 END IF;
6719
6720 FOR i IN lr_customer_trx_line_id_tbl.FIRST ..
6721 lr_customer_trx_line_id_tbl.LAST LOOP
6722
6723 IF pg_debug IN ('Y', 'C') THEN
6724 debug('Receipt Analyzer Loop - Line ID: ' ||
6725 lr_customer_trx_line_id_tbl(i));
6726 END IF;
6727
6728
6729 -- at all times one of the columns would always have zero
6730 l_current_line_balance := lr_amount_pending_tbl(i) +
6731 lr_amount_recognized_tbl(i);
6732
6733 ------------------------------------------------------------------------
6734 -- Here we will call the function compute_line_amount to determine what
6735 -- is the exact amount (in invoice currency) that should be applied to
6736 -- the current line. This function takes care of the rounding issues.
6737 -- There are two potential rounding issues here which this function takes
6738 -- care of. First, if we simply prorate an amount across the number of
6739 -- lines, then there is a potential for losing a cent here and there.
6740 -- So, the this function has to make sure the total amount applied equals
6741 -- the total amount applied across lines. Another rounding issue that
6742 -- this function takes care of has to do with the fact, if we continue
6743 -- to apply and apply to these lines, there is a potential for a cent or
6744 -- two to spill over to other lines. As a result, when you reverse
6745 -- receipts completely, you may have lines having -0.01, -0.01, and
6746 -- +0.02. Although, the this balances across lines, this is not right.
6747 -- The compute_line_amount is now smart enough to handle this as well.
6748 -- Please note that the same function will be called in the entered
6749 -- entered currency if this is a cross currency transaction.
6750 -------------------------------------------------------------------------
6751
6752 l_computed_line_amount := compute_line_amount (
6753 p_mode => p_mode,
6754 p_amount_previously_applied => l_total_application,
6755 p_current_amount_applied => l_line_applied,
6756 p_line_balance_orig =>
6757 lr_amount_due_original_tbl(i) + lr_cm_amount_tbl(i),
6758 p_currency_code => l_invoice_currency_code,
6759 p_sum_of_all_lines => l_sum_of_all_lines,
6760 p_current_line_balance => l_current_line_balance,
6761 p_running_lines_balance => l_running_lines_balance,
6762 p_running_allocated_balance => l_running_allocated_balance);
6763
6764
6765 IF pg_debug IN ('Y', 'C') THEN
6766 debug('l_computed_line_amount: ' ||
6767 l_computed_line_amount);
6768 END IF;
6769
6770 IF (g_currency_code_f <> p_invoice_currency_code) THEN
6771
6772 -- INVOICE CURRENCY DOES NOT EQUAL FUNCTIONAL CURRENCY
6773
6774 IF pg_debug IN ('Y', 'C') THEN
6775 debug('Invoice and functional currency DIFFER');
6776 END IF;
6777 -- at all times one of the columns would always have to be zero
6778 l_acctd_current_line_balance := lr_acctd_amount_pending_tbl(i) +
6779 lr_acctd_amt_recognized_tbl(i);
6780
6781 l_acctd_computed_line_amount := compute_line_amount (
6782 p_mode => p_mode,
6783 p_amount_previously_applied => l_acctd_total_application,
6784 p_current_amount_applied => l_line_applied,
6785 p_line_balance_orig =>
6786 lr_acctd_amount_due_orig_tbl(i) + lr_cm_acctd_amount_tbl(i),
6787 p_currency_code => g_currency_code_f,
6788 p_sum_of_all_lines => l_acctd_sum_of_all_lines,
6789 p_current_line_balance => l_acctd_current_line_balance,
6790 p_running_lines_balance => l_acctd_running_lines_balance,
6791 p_running_allocated_balance => l_acctd_running_allocated_bal);
6792
6793 ELSE
6794
6795 IF pg_debug IN ('Y', 'C') THEN
6796 debug('Invoice and functional currency MATCH');
6797 END IF;
6798 l_acctd_computed_line_amount := l_computed_line_amount;
6799
6800 END IF;
6801
6802 IF pg_debug IN ('Y', 'C') THEN
6803 debug('l_acctd_computed_line_amount: ' ||
6804 l_acctd_computed_line_amount);
6805 END IF;
6806
6807
6808 ------------------------------------------------------------------------
6809 -- This is almost like a real time call to periodic sweeper. This way,
6810 -- we get the latest line status taking into account all the expirations
6811 -- as of now. This allows us to recognize revenue as soon as possible.
6812 -- The following function determines the status of line. It will
6813 -- indicate what kind of issues remain: header level only, line level
6814 -- only, header and line level or no issues remain.
6815 ------------------------------------------------------------------------
6816
6817 IF pg_debug in ('Y', 'C') THEN
6818 debug('lr_amount_due_original_tbl(i) = ' ||
6819 lr_amount_due_original_tbl(i));
6820 debug('lr_cm_amount_tbl(i) = ' ||
6821 lr_cm_amount_tbl(i));
6822 debug('lr_cm_exists_tbl(i) = ' ||
6823 lr_cm_amount_tbl(i));
6824 END IF;
6825
6826 /* 9320279 - Prevent any REV/UNEARN if the line is
6827 fully credited
6828 10244103 - Make sure there is at least one line-level
6829 credit on this line. Since zero is the natural result of a zero
6830 line, credited or not */
6831 IF l_computed_line_amount = 0
6832 AND lr_amount_due_original_tbl(i) + lr_cm_amount_tbl(i) = 0
6833 AND lr_cm_exists_tbl(i) > 0 -- cm exists for this line
6834 THEN
6835 /* Line is fully credited */
6836 l_line_status := c_fully_credited;
6837 ELSE
6838 /* do the normal line_status call */
6839 l_line_status := get_line_status (
6840 p_cust_trx_line_id => lr_customer_trx_line_id_tbl(i));
6841 END IF;
6842
6843 IF pg_debug IN ('Y', 'C') THEN
6844 debug('Scenario : ' || l_line_status);
6845 END IF;
6846
6847 -- set the common attributes for the revenue adjustment
6848
6849 l_rev_adj_rec.line_selection_mode := 'S';
6850 l_rev_adj_rec.from_cust_trx_line_id := lr_customer_trx_line_id_tbl(i);
6851 l_rev_adj_rec.customer_trx_id := l_customer_trx_id;
6852 l_rev_adj_rec.gl_date := sysdate;
6853 l_rev_adj_rec.reason_code := 'REV_MGMT_ENGINE';
6854 l_rev_adj_rec.amount_mode := 'A';
6855
6856 IF (p_mode = c_receipt_application_mode AND
6857 l_line_status = c_recognizable) THEN
6858
6859 IF pg_debug IN ('Y', 'C') THEN
6860 debug('No Issues Remain- Recognizing The Whole Amount '
6861 || lr_customer_trx_line_id_tbl(i));
6862 END IF;
6863
6864 l_amount_adjusted := lr_amount_due_original_tbl(i);
6865 l_acctd_amount_adjusted := lr_acctd_amount_due_orig_tbl(i);
6866
6867 update_deferred_lines (
6868 p_customer_trx_line_id => lr_customer_trx_line_id_tbl(i),
6869 p_line_status => l_line_status,
6870 p_amount_recognized => l_amount_adjusted,
6871 p_acctd_amount_recognized => l_acctd_amount_adjusted,
6872 p_amount_pending => 0,
6873 p_acctd_amount_pending => 0);
6874
6875 l_rev_adj_rec.amount := l_amount_adjusted;
6876
6877 adjust_revenue(
6878 p_mode => c_earn_revenue,
6879 p_customer_trx_id => l_customer_trx_id,
6880 p_customer_trx_line_id => lr_customer_trx_line_id_tbl(i),
6881 p_acctd_amount => l_acctd_computed_line_amount,
6882 p_gl_date => l_gl_date,
6883 p_ram_desc_flexfield => l_ram_desc_flexfield,
6884 p_rev_adj_rec => l_rev_adj_rec,
6885 x_adjustment_number => l_adjustment_number,
6886 x_return_status => l_return_status,
6887 x_msg_count => l_msg_count,
6888 x_msg_data => l_msg_data);
6889
6890 ELSIF (p_mode = c_receipt_application_mode AND
6891 l_line_status = c_cash_based) THEN
6892
6893 -----------------------------------------------------------------------
6894 -- This is receipt application scenario # 1 where only
6895 -- hang up is header level, in this case receipt application
6896 -- equals revenue recognition up to the amount received and the
6897 -- pending amount can be recognized. The reason we may have
6898 -- something in pending bucket is because previously this line may
6899 -- have had a line level collectibility issue and that could have
6900 -- removed by the periodic sweeper engine.
6901 -----------------------------------------------------------------------
6902
6903 IF pg_debug IN ('Y', 'C') THEN
6904 debug('Cash Based Scenario- Recognizing For Line ' ||
6905 lr_customer_trx_line_id_tbl(i));
6906 END IF;
6907
6908 l_amount_adjusted := (lr_amount_recognized_tbl(i) +
6909 lr_amount_pending_tbl(i) +
6910 l_computed_line_amount);
6911
6912 l_acctd_amount_adjusted := (lr_acctd_amt_recognized_tbl(i) +
6913 lr_acctd_amount_pending_tbl(i) +
6914 l_acctd_computed_line_amount);
6915
6916 /*6157033 Need to pass delta amount in adjust revenue for calculating
6917 Correct adjustable revenue in case amount recogonized is changed when amount
6918 amount adjusted is more than amount to be recogonized*/
6919 l_delta_amount := 0;
6920 l_acctd_delta_amount := 0;
6921 IF (ABS(l_amount_adjusted) >= ABS(lr_amount_due_original_tbl(i))) THEN
6922
6923 l_delta_amount := l_amount_adjusted - lr_amount_due_original_tbl(i);
6924 l_acctd_delta_amount := l_acctd_amount_adjusted - lr_acctd_amount_due_orig_tbl(i);
6925 l_amount_adjusted := lr_amount_due_original_tbl(i);
6926 l_acctd_amount_adjusted := lr_acctd_amount_due_orig_tbl(i);
6927 END IF;
6928
6929 debug('Amount: ' || l_amount_adjusted);
6930 debug('Acctd Amount: ' || l_acctd_amount_adjusted);
6931
6932 update_deferred_lines (
6933 p_customer_trx_line_id => lr_customer_trx_line_id_tbl(i),
6934 p_line_status => l_line_status,
6935 p_amount_recognized => l_amount_adjusted,
6936 p_acctd_amount_recognized => l_acctd_amount_adjusted,
6937 p_amount_pending => 0,
6938 p_acctd_amount_pending => 0);
6939
6940 -- The RAM should be called for only recent receipt amount,
6941 -- so, l_computed_line_amount and l_acctd_computed_line_amount
6942 -- is what should be used.
6943 --
6944 -- Bug # 2763669 - It should add the pending amount as well,
6945 -- since that should be RAM-ed as well.
6946
6947 l_ram_amount := l_computed_line_amount + lr_amount_pending_tbl(i);
6948 l_acctd_ram_amount := l_acctd_computed_line_amount +
6949 lr_acctd_amount_pending_tbl(i);
6950
6951 l_rev_adj_rec.amount := l_ram_amount;
6952
6953 debug('RAM Amount : ' || l_ram_amount);
6954 debug('Acctd RAM Amount: ' || l_acctd_ram_amount);
6955
6956 /*6157033 Passing delta to adjust_revenue default value for delta is 0*/
6957 adjust_revenue(
6958 p_mode => c_earn_revenue,
6959 p_customer_trx_id => l_customer_trx_id,
6960 p_customer_trx_line_id => lr_customer_trx_line_id_tbl(i),
6961 p_acctd_amount => l_acctd_ram_amount,
6962 p_gl_date => l_gl_date,
6963 p_ram_desc_flexfield => l_ram_desc_flexfield,
6964 p_rev_adj_rec => l_rev_adj_rec,
6965 p_delta_amount => l_delta_amount,
6966 p_acctd_delta_amount => l_acctd_delta_amount,
6967 x_adjustment_number => l_adjustment_number,
6968 x_return_status => l_return_status,
6969 x_msg_count => l_msg_count,
6970 x_msg_data => l_msg_data);
6971
6972 ELSIF (p_mode = c_receipt_reversal_mode AND
6973 l_line_status = c_cash_based) THEN
6974
6975 ----------------------------------------------------------------------
6976 -- This is receipt reversal scenario # 1 where previously
6977 -- earned revenue must be un-earned. Previously, when
6978 -- this receipt was applied we must have pulled everything
6979 -- from pending and added to the current amount and called RAM
6980 -- for that amount. Now, we should un-earn the recognized
6981 -- amount - reversed receipt amount. Nothing should be put back
6982 -- to pending. Anything that was recognized other than this receipt
6983 -- should still be considered recognized.
6984 ----------------------------------------------------------------------
6985
6986 IF pg_debug IN ('Y', 'C') THEN
6987 debug('Header Only Scenario - Reversing For Line ' ||
6988 lr_customer_trx_line_id_tbl(i));
6989 END IF;
6990
6991 l_amount_adjusted := (lr_amount_recognized_tbl(i) -
6992 l_computed_line_amount);
6993 l_acctd_amount_adjusted := (lr_acctd_amt_recognized_tbl(i) -
6994 l_acctd_computed_line_amount);
6995
6996 /* IF (l_computed_line_amount >= lr_amount_due_original_tbl(i)) THEN*/
6997 IF ABS(lr_amount_recognized_tbl(i)) < ABS( l_computed_line_amount) THEN
6998
6999 -- if the original receipt amount was more than the line amount
7000 -- we would have recognized upto the line amount, so the same
7001 -- should happen when the same receipt is unapplied.
7002
7003 l_amount_adjusted := 0; /* 6157033 amount recogonized reduces to zero */
7004 l_acctd_amount_adjusted := 0;
7005
7006 IF lr_amount_recognized_tbl(i) <> 0 THEN
7007 /* 6157113/6008164 - set ram amounts equal to ado amounts */
7008 /* 7413816 - Revised to lr_amount_recognized_tbl */
7009 l_ram_amount := lr_amount_recognized_tbl(i);
7010 l_acctd_ram_amount := lr_acctd_amt_recognized_tbl(i);
7011 ELSE
7012 l_ram_amount := 0;
7013 l_acctd_ram_amount := 0;
7014 END IF;
7015 ELSE
7016 -- Bug # 2763669 - RAM should be called for only recent receipt amount,
7017 -- so, l_computed_line_amount and l_acctd_computed_line_amount
7018 -- is what should be used.
7019
7020 /* 6157113/6008164 - set ram amounts equal to computed amounts */
7021 l_ram_amount := l_computed_line_amount;
7022 l_acctd_ram_amount := l_acctd_computed_line_amount;
7023 END IF;
7024
7025 debug('Amount : ' || l_amount_adjusted);
7026 debug('Acctd. Amount : ' || l_acctd_amount_adjusted);
7027
7028 update_deferred_lines (
7029 p_customer_trx_line_id => lr_customer_trx_line_id_tbl(i),
7030 p_line_status => l_line_status,
7031 p_amount_recognized => l_amount_adjusted,
7032 p_acctd_amount_recognized => l_acctd_amount_adjusted);
7033
7034 l_rev_adj_rec.amount := l_ram_amount;
7035
7036 debug('RAM Amount : ' || l_ram_amount);
7037 debug('Acctd RAM Amount: ' || l_acctd_ram_amount);
7038
7039 adjust_revenue(
7040 p_mode => c_unearn_revenue,
7041 p_customer_trx_id => l_customer_trx_id,
7042 p_customer_trx_line_id => lr_customer_trx_line_id_tbl(i),
7043 p_acctd_amount => l_acctd_ram_amount,
7044 p_gl_date => l_gl_date,
7045 p_ram_desc_flexfield => l_ram_desc_flexfield,
7046 p_rev_adj_rec => l_rev_adj_rec,
7047 x_adjustment_number => l_adjustment_number,
7048 x_return_status => l_return_status,
7049 x_msg_count => l_msg_count,
7050 x_msg_data => l_msg_data);
7051
7052 ELSIF (p_mode = c_receipt_application_mode AND
7053 l_line_status = c_combination) THEN
7054
7055 -----------------------------------------------------------------------
7056 -- This is receipt application scenario # 2 where both line level
7057 -- and header level have problems, in this case, receipt
7058 -- application does not tie to revenue recognition. However, we
7059 -- must keep track of the receipt that was attempted to be
7060 -- recognized. So that when the line level concern is removed
7061 -- the sweeper can recognize this amount. So, we put it in pending.
7062 -----------------------------------------------------------------------
7063
7064 IF pg_debug IN ('Y', 'C') THEN
7065 debug('Move the pending amount to recognized for line '
7066 || lr_customer_trx_line_id_tbl(i));
7067 END IF;
7068
7069 l_amount_adjusted := (lr_amount_pending_tbl(i) +
7070 l_computed_line_amount);
7071
7072 l_acctd_amount_adjusted := (lr_acctd_amount_pending_tbl(i) +
7073 l_acctd_computed_line_amount);
7074
7075 IF (lr_amount_pending_tbl(i) + l_computed_line_amount >=
7076 lr_amount_due_original_tbl(i)) THEN
7077
7078 l_amount_adjusted := lr_amount_due_original_tbl(i);
7079 l_acctd_amount_adjusted := lr_acctd_amount_due_orig_tbl(i);
7080
7081 END IF;
7082
7083 update_deferred_lines (
7084 p_customer_trx_line_id => lr_customer_trx_line_id_tbl(i),
7085 p_amount_pending => l_amount_adjusted,
7086 p_acctd_amount_pending => l_acctd_amount_adjusted);
7087
7088 ELSIF (p_mode = c_receipt_reversal_mode AND
7089 l_line_status = c_combination) THEN
7090
7091 /*--------------------------------------------------------------------
7092 | This is receipt reversal scenario # 2 where pending column
7093 | should go back to the amount as it was before this receipt
7094 | was applied.
7095 +--------------------------------------------------------------------*/
7096
7097 IF pg_debug IN ('Y', 'C') THEN
7098 debug('Reversing The Pending Amount For Line ' ||
7099 lr_customer_trx_line_id_tbl(i));
7100 END IF;
7101
7102 l_amount_adjusted := (lr_amount_pending_tbl(i) -
7103 l_computed_line_amount);
7104
7105 l_acctd_amount_adjusted := (lr_acctd_amount_pending_tbl(i) -
7106 l_acctd_computed_line_amount);
7107
7108 IF (l_computed_line_amount >= lr_amount_due_original_tbl(i)) THEN
7109
7110 -- if the original receipt amount was more than the line amount
7111 -- we would have updated the pending amount upto the line amount,
7112 -- so the same should happen when the same receipt is unapplied.
7113
7114 l_amount_adjusted := lr_amount_due_original_tbl(i);
7115 l_acctd_amount_adjusted := lr_acctd_amount_due_orig_tbl(i);
7116
7117 END IF;
7118
7119 update_deferred_lines (
7120 p_customer_trx_line_id => lr_customer_trx_line_id_tbl(i),
7121 p_amount_pending => l_amount_adjusted,
7122 p_acctd_amount_pending => l_acctd_amount_adjusted);
7123
7124
7125 END IF; -- (scenario #)
7126
7127 IF pg_debug IN ('Y', 'C') THEN
7128 debug('amount adjusted : ' || l_amount_adjusted);
7129 debug('acctd amount adjusted: ' ||
7130 l_acctd_amount_adjusted);
7131 debug('Done for the line');
7132 END IF;
7133
7134 END LOOP; -- FOR i IN l_customer_trx_line_id_tbl.FIRST ..LAST
7135
7136 END LOOP; -- (rev_lines => bulk collect)
7137
7138 IF pg_debug IN ('Y', 'C') THEN
7139 debug('ar_revenue_management_pvt.receipt_analyzer()-');
7140 END IF;
7141
7142 EXCEPTION
7143
7144 WHEN SKIP_RECEIPT_ANALYZER THEN
7145 IF pg_debug IN ('Y', 'C') THEN
7146 debug('ar_revenue_management_pvt.receipt_analyzer()-');
7147 END IF;
7148
7149 WHEN NO_DATA_FOUND THEN
7150 IF pg_debug IN ('Y', 'C') THEN
7151 debug(' (1) NO_DATLR_FOUND: receipt_analyzer)');
7152 debug(sqlerrm);
7153 END IF;
7154 RAISE;
7155
7156 WHEN OTHERS THEN
7157 IF pg_debug IN ('Y', 'C') THEN
7158 debug(' (1) OTHERS: receipt_analyzer');
7159 debug(sqlerrm);
7160 END IF;
7161 RAISE;
7162
7163 END receipt_analyzer;
7164
7165
7166 /*========================================================================
7167 | PUBLIC PROCEDURE receipt_analyzer
7168 |
7169 | DESCRIPTION
7170 | This is a overloaded function. This one takes in request is as the only
7171 | parameter, then bulk processes the receipts. This procedure takes care
7172 | of receipt applications from collectibility perspective. When a receipt
7173 | is applied, which is an event for revenue management engine, this
7174 | procedure determines if this receipt can trigger revenue recognition.
7175 | In cases where creditworthiness and/or payment term was the reason for
7176 | revenue deferral, it would recognize the revenue upto the receipt amount.
7177 |
7178 | CALLED FROM PROCEDURES/FUNCTIONS
7179 | 1. auto receipts
7180 |
7181 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
7182 | receipt_analyzer
7183 |
7184 | PARAMETERS
7185 | p_rerquest_id
7186 |
7187 | NOTES
7188 | None.
7189 |
7190 | MODIFICATION HISTORY
7191 | Date Author Description of Changes
7192 | 26-JUL-2002 ORASHID Subroutine Created
7193 |
7194 *=======================================================================*/
7195
7196 PROCEDURE receipt_analyzer (p_request_id IN NUMBER) IS
7197
7198 -- This cursor retrieves all the receipts for a request id
7199
7200 CURSOR receipts IS
7201 SELECT ara.rowid,
7202 ara.applied_customer_trx_id,
7203 ara.acctd_amount_applied_to,
7204 ara.tax_applied,
7205 ara.receivables_charges_applied,
7206 ara.line_applied,
7207 ara.freight_applied,
7208 rct.invoice_currency_code,
7209 rct.exchange_rate,
7210 ara.gl_date
7211 FROM ar_receivable_applications ara,
7212 ra_customer_trx rct
7213 WHERE ara.request_id = p_request_id
7214 AND ara.applied_customer_trx_id = rct.customer_trx_id;
7215
7216 l_last_fetch BOOLEAN;
7217
7218 l_rowid_tbl varchar_table;
7219 l_customer_trx_id_tbl number_table;
7220 l_acctd_amount_applied_tbl number_table;
7221 l_exchange_rate_tbl number_table;
7222 l_invoice_currency_code_tbl varchar_table;
7223 l_tax_applied_tbl number_table;
7224 l_charges_applied_tbl number_table;
7225 l_freight_applied_tbl number_table;
7226 l_line_applied_tbl number_table;
7227 l_gl_date_tbl date_table;
7228
7229 BEGIN
7230
7231 debug('ar_revenue_management_pvt.receipt_analyzer()+ ');
7232 debug(' p_request_id : ' || p_request_id);
7233
7234 OPEN receipts;
7235 LOOP
7236
7237 -- this table must be deleted for re-entry
7238 -- otherwise the row count may not be zero
7239 -- and we will be stuck in an infinite loop.
7240
7241 l_rowid_tbl.delete;
7242
7243 FETCH receipts BULK COLLECT INTO
7244 l_rowid_tbl,
7245 l_customer_trx_id_tbl,
7246 l_acctd_amount_applied_tbl,
7247 l_tax_applied_tbl,
7248 l_charges_applied_tbl,
7249 l_line_applied_tbl,
7250 l_freight_applied_tbl,
7251 l_invoice_currency_code_tbl,
7252 l_exchange_rate_tbl,
7253 l_gl_date_tbl
7254 LIMIT c_max_bulk_fetch_size;
7255
7256 IF receipts%NOTFOUND THEN
7257
7258 debug('last fetch');
7259 l_last_fetch := TRUE;
7260
7261 END IF;
7262
7263 IF l_rowid_tbl.COUNT = 0 AND l_last_fetch THEN
7264 debug('last fetch and COUNT equals zero');
7265 EXIT;
7266 END IF;
7267
7268 FOR i IN l_rowid_tbl.FIRST .. l_rowid_tbl.LAST LOOP
7269
7270 debug( 'i: ' || i);
7271 debug( 'p_customer_trx_id: ' || l_customer_trx_id_tbl(i));
7272 debug( 'p_acctd_amount_applied: ' || l_acctd_amount_applied_tbl(i));
7273 debug( 'p_exchange_rate: ' || l_exchange_rate_tbl(i));
7274 debug( 'p_invoice_currency_code: ' || l_invoice_currency_code_tbl(i));
7275 debug( 'p_tax_applied: ' || l_tax_applied_tbl(i));
7276 debug( 'p_charges_applied: ' || l_charges_applied_tbl(i));
7277 debug( 'p_freight_applied: ' || l_freight_applied_tbl(i));
7278 debug( 'p_line_applied: ' || l_line_applied_tbl(i));
7279 debug( 'p_gl_date: ' || l_gl_date_tbl(i));
7280
7281 receipt_analyzer (
7282 p_mode => c_receipt_application_mode,
7283 p_customer_trx_id => l_customer_trx_id_tbl(i),
7284 p_acctd_amount_applied => l_acctd_amount_applied_tbl(i),
7285 p_exchange_rate => l_exchange_rate_tbl(i),
7286 p_invoice_currency_code => l_invoice_currency_code_tbl(i),
7287 p_tax_applied => l_tax_applied_tbl(i),
7288 p_charges_applied => l_charges_applied_tbl(i),
7289 p_freight_applied => l_freight_applied_tbl(i),
7290 p_line_applied => l_line_applied_tbl(i),
7291 p_gl_date => l_gl_date_tbl(i));
7292
7293 debug('returned from the call to the original analyzer');
7294
7295 END LOOP;
7296
7297 debug('End First Loop');
7298
7299 END LOOP;
7300
7301 CLOSE receipts;
7302
7303 debug('ar_revenue_management_pvt.receipt_analyzer()- ');
7304
7305 EXCEPTION
7306
7307 WHEN NO_DATA_FOUND THEN
7308 debug('EXCEPTION: (2) NO_DATA_FOUND: receipt_analyzer');
7309 debug(sqlerrm);
7310 RAISE;
7311
7312 WHEN OTHERS THEN
7313 debug('EXCEPTION: (2) OTHERS: receipt_analyzer');
7314 debug(sqlerrm);
7315 RAISE;
7316
7317 END receipt_analyzer;
7318
7319
7320 /*========================================================================
7321 | PUBLIC FUNCTION line_collectibility
7322 |
7323 | DESCRIPTION
7324 | This procedure computes collectibility for a given line.
7325 |
7326 | CALLED FROM PROCEDURES/FUNCTIONS
7327 | This procedure is called from revenue recognition program.
7328 |
7329 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
7330 | line_collectible
7331 |
7332 | PARAMETERS
7333 | p_customer_trx_id
7334 | p_customer_trx_line_id
7335 |
7336 | NOTES
7337 | This function checks if the the line has gone through collectibility
7338 | analysis by calling line_collectible, there is not need tocompute it
7339 | from scratch.
7340 |
7341 | MODIFICATION HISTORY
7342 | Date Author Description of Changes
7343 | 26-JUL-2002 ORASHID Subroutine Created
7344 |
7345 *=======================================================================*/
7346
7347 FUNCTION line_collectibility(
7348 p_customer_trx_id NUMBER,
7349 p_customer_trx_line_id NUMBER)
7350 RETURN NUMBER IS
7351
7352 /*-----------------------------------------------------------------------+
7353 | Local Variable Declarations and initializations |
7354 +-----------------------------------------------------------------------*/
7355
7356 l_line_verdict NUMBER;
7357
7358 BEGIN
7359
7360 debug('ar_revenue_management_pvt.line_collectibility()+');
7361 debug('** line_collectibility parameters **');
7362 debug(' p_customer_trx_id : ' || p_customer_trx_id);
7363 debug(' p_customer_trx_line_id : ' || p_customer_trx_line_id);
7364
7365 l_line_verdict := line_collectible (
7366 p_customer_trx_id => p_customer_trx_id,
7367 p_customer_trx_line_id => p_customer_trx_line_id);
7368
7369 IF (l_line_verdict = not_analyzed) THEN
7370 RETURN collect;
7371 END IF;
7372
7373 debug('ar_revenue_management_pvt.line_collectibility()-');
7374
7375 RETURN l_line_verdict;
7376
7377 EXCEPTION
7378
7379 WHEN NO_DATA_FOUND THEN
7380 IF pg_debug IN ('Y', 'C') THEN
7381 debug(' (1) NO_DATA_FOUND: line_collectibility');
7382 debug(sqlerrm);
7383 END IF;
7384 RAISE;
7385
7386 WHEN OTHERS THEN
7387 IF pg_debug IN ('Y', 'C') THEN
7388 debug(' (1) OTHERS: line_collectibility');
7389 debug(sqlerrm);
7390 END IF;
7391 RAISE;
7392
7393 END line_collectibility;
7394
7395
7396 /*=========================================================================
7397 | PUBLIC FUNCTION line_collectibility
7398 |
7399 | DESCRIPTION
7400 | This function computes collectibility given the request ID.
7401 |
7402 | CALLED FROM PROCEDURES/FUNCTIONS
7403 | This function is during auto-invoice. To be specific, it is called
7404 | from arp_auto_accounting package (ARTEAACB.pls).
7405 |
7406 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
7407 |
7408 | PARAMETERS
7409 | p_request_id => request_id in the ar_interface_lines_all table
7410 |
7411 | NOTES
7412 |
7413 *=======================================================================*/
7414
7415 FUNCTION line_collectibility (
7416 p_request_id NUMBER,
7417 p_source VARCHAR2 DEFAULT NULL,
7418 x_error_count OUT NOCOPY NUMBER,
7419 p_customer_trx_line_id ra_customer_trx_lines.customer_trx_line_id%TYPE )
7420 RETURN long_number_table IS
7421
7422 /*-----------------------------------------------------------------------+
7423 | Cursor Declarations |
7424 +-----------------------------------------------------------------------*/
7425
7426 -- This cursor retrieves the revenue management verdicts for each line
7427 CURSOR verdicts IS
7428 SELECT ctl.customer_trx_line_id,
7429 decode(lrs.line_collectible_flag, NULL, 1, 'Y', 1, 'N', 0) verdict
7430 FROM ra_customer_trx_lines ctl,
7431 ar_deferred_lines lrs
7432 WHERE ((p_request_id IS NULL AND
7433 p_customer_trx_line_id IS NOT NULL AND
7434 ctl.customer_trx_line_id = p_customer_trx_line_id) OR
7435 (p_request_id IS NOT NULL AND
7436 ctl.request_id = p_request_id))
7437 AND ctl.line_type = 'LINE'
7438 AND ctl.customer_trx_line_id = lrs.customer_trx_line_id (+)
7439 ORDER BY ctl.customer_trx_line_id;
7440
7441 /*-----------------------------------------------------------------------+
7442 | Local Variable Declarations and initializations |
7443 +-----------------------------------------------------------------------*/
7444
7445 l_line_verdicts_tbl long_number_table;
7446
7447 BEGIN
7448
7449 g_source := p_source;
7450
7451 debug('ar_revenue_management_pvt.line_collectibility()+');
7452 debug(' p_request_id : ' || p_request_id);
7453 debug(' p_source : ' || p_source);
7454 debug(' p_customer_trx_line_id : ' || p_customer_trx_line_id);
7455
7456 -- validate the contingencies and populate the errors table
7457
7458 IF p_request_id IS NOT NULL THEN
7459 x_error_count := validate_contingencies( p_request_id => p_request_id);
7460
7461 debug('validation done');
7462
7463 -- the following would insert a row in the ar_line_conts table
7464 -- for each contingency passed in the ra_interface_contingencies_all table.
7465
7466 IF (g_source = 'AR_INVOICE_API') THEN
7467 insert_contingencies_from_gt(p_request_id => p_request_id);
7468 ELSE
7469 insert_contingencies_from_itf(p_request_id => p_request_id);
7470 /* 5142216 - copy parent contingencies if necessary */
7471 copy_parent_contingencies(p_request_id => p_request_id);
7472 END IF;
7473
7474 debug('contingency rows inserted: ' || SQL%ROWCOUNT);
7475 END IF; --p_request_id not null
7476
7477 default_contingencies (p_request_id => p_request_id
7478 ,p_customer_trx_line_id => p_customer_trx_line_id);
7479
7480 delete_unwanted_contingencies (p_request_id => p_request_id
7481 ,p_customer_trx_line_id => p_customer_trx_line_id);
7482
7483 -- now all the contingencies have been inserted we can insert
7484 -- the deferred lines (parent) into ar_deferred_lines_all table.
7485
7486 insert_deferred_lines (p_request_id => p_request_id
7487 ,p_customer_trx_line_id => p_customer_trx_line_id);
7488
7489 debug('deferred rows inserted: ' || SQL%ROWCOUNT);
7490
7491 FOR revline IN verdicts LOOP
7492 debug('Line ID: ' || revline.customer_trx_line_id);
7493 debug('Verdict: ' || revline.verdict);
7494 l_line_verdicts_tbl(revline.customer_trx_line_id) := revline.verdict;
7495 END LOOP;
7496
7497 debug('ar_revenue_management_pvt.line_collectibility()-');
7498
7499 RETURN l_line_verdicts_tbl;
7500
7501 EXCEPTION
7502
7503 WHEN NO_DATA_FOUND THEN
7504 debug('(2) NO_DATA_FOUND: line_collectibility');
7505 debug(sqlerrm);
7506 RAISE;
7507
7508 WHEN OTHERS THEN
7509 debug('(2) OTHERS: line_collectibility');
7510 debug(sqlerrm);
7511 RAISE;
7512
7513 END line_collectibility;
7514
7515
7516 /*========================================================================
7517 | INITIALIZATION SECTION
7518 |
7519 | DESCRIPTION
7520 | Nothing so far.
7521 |
7522 | MODIFICATION HISTORY
7523 | Date Author Description of Changes
7524 | 26-JUL-2002 ORASHID Created
7525 |
7526 *=======================================================================*/
7527 BEGIN
7528
7529 -- fetch the details about functional currency. This does not change
7530 -- so this should not be fetched multiple times, instead it should
7531 -- fetched only once when the package is loaded to the db.
7532
7533 get_base_currency_info;
7534
7535 /* 5142216 - set g_om_context from profile. Insure that if the profile
7536 is not set, the value is not null, but a inoperable constant */
7537 g_om_context := NVL(fnd_profile.value('ONT_SOURCE_CODE'),'###NOT_SET###');
7538
7539 /* 13482797 - display g_category_set_id, g_inv_org_id */
7540 debug('category_set_id = ' || AR_RAAPI_UTIL.g_category_set_id);
7541 debug('inv_org_id = ' || AR_RAAPI_UTIL.g_inv_org_id);
7542
7543 EXCEPTION
7544
7545 WHEN NO_DATA_FOUND THEN
7546 debug(' ar_revenue_management_pvt.initialize');
7547 debug(sqlerrm);
7548 RAISE;
7549
7550 WHEN OTHERS THEN
7551 debug(' ar_revenue_management_pvt.initialize');
7552 debug(sqlerrm);
7553 RAISE;
7554
7555 END ar_revenue_management_pvt;
[Home] [Help]