[Home] [Help]
549: max(dl.acctd_amount_pending),
550: 'PARENT',
551: max(interface_line_attribute6),
552: l_request_id
553: FROM ar_deferred_lines dl,
554: ar_line_conts lc,
555: ar_deferral_reasons dr,
556: ra_customer_trx_lines ctl
557: WHERE dl.customer_trx_line_id = lc.customer_trx_line_id
597: max(dl.acctd_amount_pending),
598: 'PARENT',
599: max(interface_line_attribute6),
600: l_request_id
601: FROM ar_deferred_lines dl,
602: ar_line_conts lc,
603: ar_deferral_reasons dr,
604: ra_customer_trx_lines ctl
605: WHERE dl.customer_trx_line_id = lc.customer_trx_line_id
695: dl.acctd_amount_pending,
696: 'PARENT',
697: interface_line_attribute6,
698: l_request_id
699: FROM ar_deferred_lines dl,
700: ra_customer_trx_lines ctl
701: WHERE dl.customer_trx_line_id = ctl.customer_trx_line_id
702: AND dl.customer_trx_id = nvl(p_customer_trx_id, dl.customer_trx_id)
703: AND dl.customer_trx_line_id = nvl(p_customer_trx_line_id,
786: child_line.interface_line_attribute6,
787: max(lc.expiration_date)
788: FROM ar_reviewed_lines_gt parent,
789: ra_customer_trx_lines parent_line,
790: ar_deferred_lines child,
791: ra_customer_trx_lines child_line,
792: ar_line_conts lc,
793: ar_deferral_reasons dr
794: WHERE parent.customer_trx_id = parent_line.customer_trx_id
873: max(acctd_amount_pending),
874: 'OTHERS',
875: l_request_id,
876: max(lc.expiration_date)
877: FROM ar_deferred_lines dl,
878: ar_line_conts lc,
879: ar_deferral_reasons dr
880: WHERE dl.customer_trx_line_id = lc.customer_trx_line_id
881: AND lc.contingency_id = dr.contingency_id
921: max(amount_pending),
922: max(acctd_amount_pending),
923: 'UPDATE',
924: l_request_id
925: FROM ar_deferred_lines dl,
926: ar_line_conts lc,
927: ar_deferral_reasons dr
928: WHERE dl.customer_trx_line_id = lc.customer_trx_line_id
929: AND lc.contingency_id = dr.contingency_id
2133: -- there exists a row in the child table.
2134:
2135: IF (g_source = 'AR_INVOICE_API') THEN
2136:
2137: INSERT INTO ar_deferred_lines
2138: (
2139: customer_trx_line_id,
2140: customer_trx_id,
2141: original_collectibility_flag,
2225: -- not passed through the GT. The reason we can't do this with one SQL
2226: -- is because we would like to copy the values passed in the attributes
2227: -- columns.
2228:
2229: INSERT INTO ar_deferred_lines
2230: (
2231: customer_trx_line_id,
2232: customer_trx_id,
2233: original_collectibility_flag,
2277: AND tlg.customer_trx_id = thg.customer_trx_id
2278: AND tlg.customer_trx_line_id = lrc.customer_trx_line_id
2279: AND NOT EXISTS
2280: (SELECT 'line already inserted'
2281: FROM ar_deferred_lines dl
2282: WHERE dl.customer_trx_line_id = lrc.customer_trx_line_id)
2283: GROUP BY tlg.customer_trx_line_id, thg.org_id;
2284:
2285: ELSE
2283: GROUP BY tlg.customer_trx_line_id, thg.org_id;
2284:
2285: ELSE
2286: IF p_request_id IS NOT NULL THEN
2287: INSERT INTO ar_deferred_lines
2288: (
2289: customer_trx_line_id,
2290: customer_trx_id,
2291: original_collectibility_flag,
2382: /* 5279702 - Populate parent_line_id when possible. This
2383: is important for contingencies defaulted to child lines
2384: from parents. */
2385:
2386: INSERT INTO ar_deferred_lines
2387: (
2388: customer_trx_line_id,
2389: customer_trx_id,
2390: original_collectibility_flag,
2438: AND ctl.customer_trx_line_id = lrc.customer_trx_line_id
2439: AND ctl.customer_trx_line_id = il.interface_line_id (+)
2440: AND NOT EXISTS
2441: (SELECT 'line already inserted'
2442: FROM ar_deferred_lines dl
2443: WHERE dl.customer_trx_line_id = lrc.customer_trx_line_id)
2444: GROUP BY ctl.customer_trx_line_id, ct.org_id;
2445:
2446: END IF;
2448: debug('deferred lines inserted: ' || SQL%ROWCOUNT);
2449:
2450: -- it is possible that the line gets imported with one pre-billing
2451: -- acceptance contingency. In that case, we would like to insert
2452: -- the a row in the ar_deferred_lines_all, however, we need to mark
2453: -- it as collectible now.
2454:
2455: IF p_request_id IS NOT NULL THEN
2456:
2453: -- it as collectible now.
2454:
2455: IF p_request_id IS NOT NULL THEN
2456:
2457: UPDATE ar_deferred_lines dl
2458: SET line_collectible_flag = 'Y'
2459: WHERE dl.request_id = p_request_id
2460: AND NOT EXISTS
2461: (SELECT 'incomplete contingency'
2464: AND lc.customer_trx_line_id = dl.customer_trx_line_id
2465: AND lc.completed_flag = 'N');
2466:
2467: /* Bug 12813416 */
2468: update ar_deferred_lines dl
2469: set original_collectibility_flag = 'Y'
2470: WHERE dl.request_id = p_request_id
2471: and dl.customer_trx_id in
2472: (SELECT ctl.customer_trx_id /* its a pre-billing contingency */
2480: AND dr.REVREC_EVENT_CODE = 'INVOICING');
2481:
2482: ELSIF p_customer_trx_line_id IS NOT NULL THEN
2483:
2484: UPDATE ar_deferred_lines dl
2485: SET line_collectible_flag = 'Y'
2486: WHERE dl.customer_trx_line_id = p_customer_trx_line_id
2487: AND NOT EXISTS
2488: (SELECT 'incomplete contingency'
2491: AND lc.customer_trx_line_id = dl.customer_trx_line_id
2492: AND lc.completed_flag = 'N');
2493:
2494: /* Bug 12813416 */
2495: update ar_deferred_lines dl
2496: set original_collectibility_flag = 'Y'
2497: WHERE dl.customer_trx_line_id = p_customer_trx_line_id
2498: and dl.customer_trx_id in
2499: (SELECT ctl.customer_trx_id /* its a pre-billing contingency */
2821:
2822: BEGIN
2823:
2824: -- This procedure simply updates a row of data in the
2825: -- ar_deferred_lines table. It will only update columns
2826: -- for which data is provided, the rest will retain their
2827: -- original values.
2828:
2829: IF pg_debug IN ('Y', 'C') THEN
2847: IF pg_debug IN ('Y', 'C') THEN
2848: debug('p_customer_trx_line_id IS NULL');
2849: END IF;
2850:
2851: UPDATE ar_deferred_lines
2852: SET line_collectible_flag = nvl(l_line_collectible,
2853: line_collectible_flag),
2854: manual_override_flag = nvl(p_manual_override,
2855: manual_override_flag),
2863: IF pg_debug IN ('Y', 'C') THEN
2864: debug('p_customer_trx_line_id IS NOT NULL');
2865: END IF;
2866:
2867: UPDATE ar_deferred_lines
2868: SET line_collectible_flag = nvl(l_line_collectible,
2869: line_collectible_flag),
2870: manual_override_flag = nvl(p_manual_override,
2871: manual_override_flag),
3026: -- was analyzed by the revenue management engine.
3027:
3028: CURSOR monitored_txn IS
3029: SELECT 1
3030: FROM ar_deferred_lines
3031: WHERE customer_trx_id = p_customer_trx_id
3032: AND manual_override_flag = 'N'
3033: AND line_collectible_flag = 'N';
3034:
3094: BEGIN
3095:
3096: --------------------------------------------------------------------------
3097: -- This procedure updates the manual_oveeride column in the
3098: -- ar_deferred_lines table to indicate that this line or transction
3099: -- has been manually manipulated by user in the RAM screens. As a result
3100: -- of this update, the revenue management engine will not keep track of
3101: -- this line anymore.
3102: ---------------------------------------------------------------------------
3422: ------------------------------------------------------------------------
3423: -- There must have been some credit memos that were applied
3424: -- to this invoice. So, we can not recognize the computed
3425: -- amount, instead we should recognize only adjustable amount.
3426: -- Hence update ar_deferred_lines table by taking out the
3427: -- initial amount and then adding the adjustable amount.
3428: -----------------------------------------------------------------------
3429:
3430: IF pg_debug IN ('Y', 'C') THEN
3442: were 100 - 102.61 + 100 = 97.39 when the rev_adj should have been
3443: for $100. So I think l_rev_adj_rec.amount is wrong */
3444: /* 6157033 used delta amount passed to calculate correct amount
3445: to be recogonied*/
3446: UPDATE ar_deferred_lines
3447: SET amount_recognized = amount_recognized + p_delta_amount -
3448: l_rev_adj_rec.amount +
3449: l_adjustable_amount,
3450: acctd_amount_recognized = acctd_amount_recognized + p_acctd_delta_amount -
3787: -- to an invoice.
3788:
3789: CURSOR total_app IS
3790: SELECT sum(amount_recognized) + sum(amount_pending)
3791: FROM ar_deferred_lines
3792: WHERE customer_trx_id = p_customer_trx_id;
3793:
3794: l_total_application ar_deferred_lines.amount_recognized%TYPE;
3795:
3790: SELECT sum(amount_recognized) + sum(amount_pending)
3791: FROM ar_deferred_lines
3792: WHERE customer_trx_id = p_customer_trx_id;
3793:
3794: l_total_application ar_deferred_lines.amount_recognized%TYPE;
3795:
3796: BEGIN
3797:
3798: -- This functions gets total receipt amount applied against
3841: -- to an invoice in functional currency.
3842:
3843: CURSOR acctd_total_app IS
3844: SELECT sum(acctd_amount_recognized) + sum(acctd_amount_pending)
3845: FROM ar_deferred_lines
3846: WHERE customer_trx_id = p_customer_trx_id;
3847:
3848: l_acctd_total_application ar_deferred_lines.amount_recognized%TYPE;
3849:
3844: SELECT sum(acctd_amount_recognized) + sum(acctd_amount_pending)
3845: FROM ar_deferred_lines
3846: WHERE customer_trx_id = p_customer_trx_id;
3847:
3848: l_acctd_total_application ar_deferred_lines.amount_recognized%TYPE;
3849:
3850: BEGIN
3851:
3852: -- This functions gets total accounted amount receipt amount applied against
3898: p_running_lines_balance IN OUT NOCOPY NUMBER,
3899: p_running_allocated_balance IN OUT NOCOPY NUMBER)
3900: RETURN NUMBER IS
3901:
3902: l_total_amount ar_deferred_lines.amount_recognized%TYPE;
3903: l_computed_line_amount ra_customer_trx_lines.extended_amount%TYPE;
3904:
3905: BEGIN
3906:
4415:
4416: BEGIN
4417:
4418: -- This procedure simply updates a row of data in the
4419: -- ar_deferred_lines table. It will only update columns
4420: -- for which data is provided, the rest will retain their
4421: -- original values.
4422:
4423: IF pg_debug IN ('Y', 'C') THEN
4498:
4499: BEGIN
4500:
4501: -- This procedure simply deletes a row of data in the
4502: -- ar_deferred_lines table. It will only delete columns
4503: -- for which data is provided, the rest will retain their
4504: -- original values.
4505:
4506: IF pg_debug IN ('Y', 'C') THEN
4644: -- It does not recompute, but simply looks it up.
4645:
4646: CURSOR c IS
4647: SELECT line_collectible_flag
4648: FROM ar_deferred_lines
4649: WHERE customer_trx_id = p_customer_trx_id
4650: AND customer_trx_line_id = p_customer_trx_line_id;
4651:
4652: l_flag ar_deferred_lines.line_collectible_flag%TYPE;
4648: FROM ar_deferred_lines
4649: WHERE customer_trx_id = p_customer_trx_id
4650: AND customer_trx_line_id = p_customer_trx_line_id;
4651:
4652: l_flag ar_deferred_lines.line_collectible_flag%TYPE;
4653: l_return_value NUMBER;
4654:
4655: BEGIN
4656:
4734: -- It does not recompute, but simply looks it up.
4735:
4736: CURSOR c IS
4737: SELECT 1
4738: FROM ar_deferred_lines
4739: WHERE customer_trx_id = p_customer_trx_id
4740: AND original_collectibility_flag = 'N'
4741: AND manual_override_flag = 'N'
4742: AND rownum = 1;
4777: l_return_value := c1%FOUND;
4778: CLOSE c1;
4779:
4780: If (l_return_value) then
4781: Update ar_deferred_lines dl
4782: set original_collectibility_flag = 'Y'
4783: WHERE dl.customer_trx_id = p_customer_trx_id;
4784: end if;
4785: end if;
4844: debug('** delete_failed_rows parameters **');
4845: debug(' p_request_id : ' || p_request_id);
4846: END IF;
4847:
4848: DELETE FROM ar_deferred_lines
4849: WHERE request_id = p_request_id;
4850:
4851: DELETE FROM ar_line_conts
4852: WHERE request_id = p_request_id;
4925: FROM ar_line_conts
4926: WHERE customer_trx_line_id IN
4927: (
4928: SELECT customer_trx_line_id
4929: FROM ar_deferred_lines
4930: WHERE customer_trx_id IN
4931: (
4932: SELECT DISTINCT il.customer_trx_id
4933: FROM ra_interface_errors ie,
4941:
4942: -- Now delete from the parent rows
4943:
4944: DELETE
4945: FROM ar_deferred_lines
4946: WHERE customer_trx_id IN
4947: (
4948: SELECT DISTINCT il.customer_trx_id
4949: FROM ra_interface_errors ie,
5009: -- for this transaction or line
5010:
5011: CURSOR contingencies IS
5012: SELECT 1
5013: FROM ar_deferred_lines dl,
5014: ar_line_conts lc,
5015: ar_deferral_reasons dl
5016: WHERE dl.customer_trx_line_id = lc.customer_trx_line_id
5017: AND lc.contingency_id = dl.contingency_id
5122: RETURN NUMBER IS
5123:
5124: CURSOR contingencies IS
5125: SELECT 1
5126: FROM ar_deferred_lines dl,
5127: ar_line_conts lc,
5128: ar_deferral_reasons dr
5129: WHERE dl.customer_trx_line_id = lc.customer_trx_line_id
5130: AND lc.contingency_id = dr.contingency_id
5230: amount_recognized,
5231: acctd_amount_recognized,
5232: amount_pending,
5233: acctd_amount_pending
5234: FROM ar_deferred_lines
5235: WHERE customer_trx_line_id = p_cust_trx_line_id;
5236:
5237: BEGIN
5238:
6386: adl.amount_pending,
6387: adl.acctd_amount_pending,
6388: SUM(NVL(gld.amount,0)), SUM(NVL(gld.acctd_amount,0)),
6389: SUM(decode(ctrl.customer_trx_id, NULL, 0, 1))
6390: FROM ar_deferred_lines adl,
6391: ra_customer_trx_lines ctrl,
6392: ra_cust_trx_line_gl_dist gld
6393: WHERE adl.customer_trx_id = p_trx_id
6394: AND adl.customer_trx_id = ctrl.previous_customer_trx_id (+)
6404:
6405: CURSOR amounts (p_trx_id NUMBER) IS
6406: SELECT sum(amount_due_original),
6407: sum(acctd_amount_due_original)
6408: FROM ar_deferred_lines
6409: WHERE customer_trx_id = p_trx_id;
6410:
6411: /* 9320279 - added to fetch total CM amounts for proration */
6412: CURSOR cm_amounts (p_trx_id NUMBER) IS
7427: CURSOR verdicts IS
7428: SELECT ctl.customer_trx_line_id,
7429: decode(lrs.line_collectible_flag, NULL, 1, 'Y', 1, 'N', 0) verdict
7430: FROM ra_customer_trx_lines ctl,
7431: ar_deferred_lines lrs
7432: WHERE ((p_request_id IS NULL AND
7433: p_customer_trx_line_id IS NOT NULL AND
7434: ctl.customer_trx_line_id = p_customer_trx_line_id) OR
7435: (p_request_id IS NOT NULL AND
7480: delete_unwanted_contingencies (p_request_id => p_request_id
7481: ,p_customer_trx_line_id => p_customer_trx_line_id);
7482:
7483: -- now all the contingencies have been inserted we can insert
7484: -- the deferred lines (parent) into ar_deferred_lines_all table.
7485:
7486: insert_deferred_lines (p_request_id => p_request_id
7487: ,p_customer_trx_line_id => p_customer_trx_line_id);
7488: