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