[Home] [Help]
103: l_user_id,
104: sysdate,
105: l_user_id,
106: max(ctl.org_id)
107: FROM ra_customer_trx_lines_all ctl,
108: ra_customer_trx_all ct,
109: ra_terms_lines tl,
110: ra_cust_trx_types_all ctt,
111: pa_implementations pa
173: l_user_id,
174: sysdate,
175: l_user_id,
176: max(ctl.org_id)
177: FROM ra_customer_trx_lines_all ctl,
178: ra_customer_trx_all ct,
179: ra_terms_lines tl,
180: ra_cust_trx_types_all ctt,
181: pa_implementations pa
240: l_user_id,
241: sysdate,
242: l_user_id,
243: ctl.org_id
244: FROM ra_customer_trx_lines_all ctl,
245: ra_customer_trx_all ct,
246: ra_terms_lines tl,
247: ra_cust_trx_types_all ctt,
248: pa_implementations pa
339: l_user_id,
340: sysdate,
341: l_user_id,
342: ctl.org_id
343: FROM ra_customer_trx_lines_all ctl,
344: ra_customer_trx_all ct,
345: ra_cust_trx_types_all ctt,
346: pa_implementations pa
347: WHERE ctl.request_id = p_request_id
407: l_user_id,
408: sysdate,
409: l_user_id,
410: ctl.org_id
411: FROM ra_customer_trx_lines_all ctl,
412: ra_customer_trx_all ct,
413: ra_cust_trx_types_all ctt,
414: pa_implementations pa
415: WHERE ctl.request_id = p_request_id
472: l_user_id,
473: sysdate,
474: l_user_id,
475: ctl.org_id
476: FROM ra_customer_trx_lines_all ctl,
477: ra_customer_trx_all ct,
478: ra_cust_trx_types_all ctt,
479: pa_implementations pa
480: WHERE ctl.customer_trx_line_id = p_customer_trx_line_id
552: l_request_id
553: FROM ar_deferred_lines dl,
554: ar_line_conts lc,
555: ar_deferral_reasons dr,
556: ra_customer_trx_lines ctl
557: WHERE dl.customer_trx_line_id = lc.customer_trx_line_id
558: AND lc.contingency_id = dr.contingency_id
559: AND ctl.customer_trx_line_id = lc.customer_trx_line_id
560: AND dr.revrec_event_code = 'CUSTOMER_ACCEPTANCE'
600: l_request_id
601: FROM ar_deferred_lines dl,
602: ar_line_conts lc,
603: ar_deferral_reasons dr,
604: ra_customer_trx_lines ctl
605: WHERE dl.customer_trx_line_id = lc.customer_trx_line_id
606: AND lc.contingency_id = dr.contingency_id
607: AND ctl.customer_trx_line_id = lc.customer_trx_line_id
608: AND dr.revrec_event_code = 'CUSTOMER_ACCEPTANCE'
626: DELETE FROM AR_REVIEWED_LINES_GT
627: WHERE (customer_trx_id, customer_trx_line_id) IN
628: (select gt.customer_trx_id, gt.customer_trx_line_id
629: FROM AR_REVIEWED_LINES_GT gt,
630: RA_CUSTOMER_TRX_LINES ctl,
631: OE_ORDER_LINES oel
632: WHERE gt.customer_trx_id = ctl.customer_trx_id
633: AND gt.customer_trx_line_id = ctl.customer_trx_line_id
634: AND ctl.interface_line_context = g_om_context
696: 'PARENT',
697: interface_line_attribute6,
698: l_request_id
699: FROM ar_deferred_lines dl,
700: ra_customer_trx_lines ctl
701: WHERE dl.customer_trx_line_id = ctl.customer_trx_line_id
702: AND dl.customer_trx_id = nvl(p_customer_trx_id, dl.customer_trx_id)
703: AND dl.customer_trx_line_id = nvl(p_customer_trx_line_id,
704: dl.customer_trx_line_id)
746: so that acceptance picks up the parent lines and any child lines
747: associated with that parent */
748:
749: /* 5043785 - The sql below contains an ORDERED hint because it must
750: join to RA_CUSTOMER_TRX_LINES before it attempts to join to the
751: child lines via so_line_id. In cases where the line is not from OM,
752: this join will fail with an invalid number (if it is attempted).
753:
754: So the ORDERED hint along with the table order prevents the failure
785: l_request_id,
786: child_line.interface_line_attribute6,
787: max(lc.expiration_date)
788: FROM ar_reviewed_lines_gt parent,
789: ra_customer_trx_lines parent_line,
790: ar_deferred_lines child,
791: ra_customer_trx_lines child_line,
792: ar_line_conts lc,
793: ar_deferral_reasons dr
787: max(lc.expiration_date)
788: FROM ar_reviewed_lines_gt parent,
789: ra_customer_trx_lines parent_line,
790: ar_deferred_lines child,
791: ra_customer_trx_lines child_line,
792: ar_line_conts lc,
793: ar_deferral_reasons dr
794: WHERE parent.customer_trx_id = parent_line.customer_trx_id
795: AND parent.customer_trx_line_id = parent_line.customer_trx_line_id
970: SELECT rl.customer_trx_line_id,
971: rl.customer_trx_id,
972: rl.so_line_id
973: FROM ar_reviewed_lines_gt rl,
974: ra_customer_trx_lines tl
975: WHERE rl.request_id = p_req_id
976: AND tl.customer_trx_line_id = rl.customer_trx_line_id
977: AND tl.customer_trx_id = rl.customer_trx_id
978: AND tl.interface_line_context = g_om_context;
1111: the logic for removing unnecessary contingencies. Note that
1112: REFUND is used on the seeded contingency and REFUND_POLICY
1113: is assigned to new ones. */
1114: PROCEDURE delete_unwanted_contingencies (p_request_id NUMBER
1115: ,p_customer_trx_line_id ra_customer_trx_lines.customer_trx_line_id%TYPE) IS
1116:
1117: /* debug cursor */
1118: CURSOR alc (p_req_id NUMBER, p_line_id NUMBER) IS
1119: select lc.customer_trx_line_id, lc.contingency_id,
1154: DELETE
1155: FROM ar_line_conts lrc
1156: WHERE customer_trx_line_id IN
1157: (SELECT customer_trx_line_id
1158: FROM ra_customer_trx_lines ctl
1159: WHERE ctl.request_id = p_request_id)
1160: AND trunc(expiration_date) - trunc(sysdate) <
1161: NVL(arp_standard.sysparm.standard_refund,0)
1162: AND EXISTS
1175: DELETE
1176: FROM ar_line_conts lc
1177: WHERE lc.customer_trx_line_id IN
1178: (SELECT customer_trx_line_id
1179: FROM ra_customer_trx_lines ctl,
1180: ra_customer_trx ct
1181: WHERE ctl.customer_trx_id = ct.customer_trx_id
1182: AND ctl.request_id = p_request_id
1183: AND ar_revenue_management_pvt.creditworthy
1201: DELETE
1202: FROM ar_line_conts lc
1203: WHERE lc.customer_trx_line_id IN
1204: (SELECT customer_trx_line_id
1205: FROM ra_customer_trx_lines ctl,
1206: ra_customer_trx ct,
1207: ra_terms_lines tl
1208: WHERE ctl.customer_trx_id = ct.customer_trx_id
1209: AND ct.term_id = tl.term_id
1232: and batch processing */
1233: DELETE from ar_line_conts
1234: WHERE customer_trx_line_id IN
1235: (SELECT customer_trx_line_id
1236: FROM ra_customer_trx_lines ctl,
1237: ra_rules r
1238: WHERE ctl.request_id = p_request_id
1239: AND ctl.accounting_rule_id IS NOT NULL
1240: AND ctl.accounting_rule_id = r.rule_id
1274: WHERE dr.contingency_id = lc.contingency_id
1275: AND dr.policy_attached = 'CREDIT_CLASSIFICATION')
1276: AND EXISTS
1277: (SELECT 'customer is not credit worthy'
1278: FROM ra_customer_trx_lines ctl,
1279: ra_customer_trx ct
1280: WHERE ctl.customer_trx_id = ct.customer_trx_id
1281: AND ctl.customer_trx_line_id = p_customer_trx_line_id
1282: AND ar_revenue_management_pvt.creditworthy
1301: WHERE dr.policy_attached = 'PAYMENT_TERM'
1302: AND dr.contingency_id = lc.contingency_id)
1303: AND EXISTS
1304: (SELECT 'term exceeds threshold'
1305: FROM ra_customer_trx_lines ctl,
1306: ra_customer_trx ct,
1307: ra_terms_lines tl
1308: WHERE ctl.customer_trx_id = ct.customer_trx_id
1309: AND ct.term_id = tl.term_id
1327: and batch processing */
1328: DELETE FROM AR_LINE_CONTS A
1329: WHERE A.customer_trx_line_id = p_customer_trx_line_id
1330: AND EXISTS (SELECT 'DEFERRED RULE'
1331: FROM ra_customer_trx_lines ctl,
1332: ra_rules r
1333: WHERE ctl.customer_trx_line_id = A.customer_trx_line_id
1334: AND ctl.accounting_rule_id = r.rule_id
1335: AND r.deferred_revenue_flag = 'Y');
1391: l_line_flex_rec ar_deferral_reasons_grp.line_flex_rec;
1392: l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
1393: l_msg_count NUMBER;
1394: l_msg_data VARCHAR2(2000);
1395: l_customer_trx_line_id ra_customer_trx_lines_all.customer_trx_line_id%type;
1396: BEGIN
1397: /* Call OM to get the DFF attributes for the parent line */
1398: OE_AR_Acceptance_GRP.Get_interface_attributes
1399: (p_line_id => p_so_line_id,
1419: has zeros in attribute11 and 14 */
1420: BEGIN
1421: SELECT MIN(customer_trx_line_id)
1422: INTO l_customer_trx_line_id
1423: FROM RA_CUSTOMER_TRX_LINES
1424: WHERE interface_line_context = l_line_flex_rec.interface_line_context
1425: AND interface_line_attribute1 = l_line_flex_rec.interface_line_attribute1
1426: AND interface_line_attribute2 = l_line_flex_rec.interface_line_attribute2
1427: AND interface_line_attribute3 = l_line_flex_rec.interface_line_attribute3
1432: AND ltrim(interface_line_attribute14) = '0'; --13018057
1433:
1434: EXCEPTION
1435: WHEN NO_DATA_FOUND THEN
1436: debug('unable to locate matching line in ra_customer_trx_lines');
1437: l_customer_trx_line_id := -98;
1438: END;
1439: ELSE
1440: /* OM responded with an error
1507: sysdate,
1508: l_user_id,
1509: plc.org_id
1510: FROM ra_customer_trx ct,
1511: ra_customer_trx_lines ctl,
1512: ra_cust_trx_types ctt,
1513: ra_interface_lines il,
1514: ar_line_conts plc
1515: WHERE ct.request_id = p_request_id
1549: /* Bug 4693399 - added customer_trx_line_id for manual invoices */
1550: /* 5236506 - Do not default post or pre billing customer acceptance
1551: contingencies on OM lines */
1552: PROCEDURE default_contingencies (p_request_id NUMBER
1553: ,p_customer_trx_line_id ra_customer_trx_lines.customer_trx_line_id%TYPE) IS
1554:
1555: l_user_id NUMBER;
1556:
1557: BEGIN
1614: NVL(ctl.ship_to_customer_id,ct.ship_to_customer_id),
1615: NVL(ctl.ship_to_site_use_id,ct.ship_to_site_use_id)
1616: FROM
1617: ra_customer_trx ct,
1618: ra_customer_trx_lines ctl,
1619: hz_customer_profiles hcp,
1620: ra_cust_trx_types ctt
1621: WHERE (ctl.customer_trx_line_id = p_customer_trx_line_id)
1622: AND ct.cust_trx_type_id = ctt.cust_trx_type_id
1645: accounting_rule_id,
1646: ship_to_cust_acct_id,
1647: ship_to_site_use_id
1648: )
1649: SELECT /*+ index(ctl ra_customer_trx_lines_n2) */
1650: ctl.customer_trx_line_id,
1651: ct.batch_source_id,
1652: decode(ctl.deferral_exclusion_flag, 'Y','',
1653: decode(hcp.cust_account_id,'','',
1664: NVL(ctl.ship_to_customer_id,ct.ship_to_customer_id),
1665: NVL(ctl.ship_to_site_use_id,ct.ship_to_site_use_id)
1666: FROM
1667: ra_customer_trx ct,
1668: ra_customer_trx_lines ctl,
1669: hz_customer_profiles hcp,
1670: ra_cust_trx_types ctt
1671: WHERE ct.request_id = p_request_id
1672: AND ct.cust_trx_type_id = ctt.cust_trx_type_id
1742: last_update_login,
1743: org_id
1744: )
1745: SELECT /*+ leading(rbr,ctl) use_hash(ctl)
1746: index(ctl,RA_CUSTOMER_TRX_LINES_N4) */
1747: rbr.id,
1748: dr.contingency_id,
1749: dr.contingency_id,
1750: decode(dr.expiration_event_code,
1769: l_user_id,
1770: ct.org_id
1771: FROM fun_rule_bulk_result_gt rbr,
1772: ar_deferral_reasons dr,
1773: ra_customer_trx_lines ctl,
1774: ra_customer_trx ct,
1775: ra_cust_trx_types ctt,
1776: pa_implementations pa
1777: WHERE rbr.result_value = dr.contingency_id
1846: l_user_id,
1847: ct.org_id
1848: FROM fun_rule_bulk_result_gt rbr,
1849: ar_deferral_reasons dr,
1850: ra_customer_trx_lines ctl,
1851: ra_customer_trx ct,
1852: ra_cust_trx_types ctt,
1853: pa_implementations pa
1854: WHERE rbr.result_value = dr.contingency_id
2072: sysdate,
2073: l_user_id,
2074: sysdate,
2075: ct.org_id
2076: FROM ra_customer_trx_lines ctl,
2077: ra_customer_trx ct,
2078: ra_cust_trx_types ctt,
2079: ar_interface_conts ic,
2080: ar_deferral_reasons dr,
2113: END insert_contingencies_from_itf;
2114:
2115: /* Bug 4693399 - added customer_trx_line_id for manual invoices */
2116: PROCEDURE insert_deferred_lines (p_request_id NUMBER
2117: ,p_customer_trx_line_id ra_customer_trx_lines.customer_trx_line_id%TYPE) IS
2118:
2119: l_user_id NUMBER;
2120: l_insert_stmt VARCHAR2(4000);
2121: l_where_clause VARCHAR2(4000);
2360: sysdate,
2361: ct.org_id,
2362: MAX(il.parent_line_id)
2363: FROM ra_customer_trx ct,
2364: ra_customer_trx_lines ctl,
2365: ar_line_conts lrc,
2366: ar_interface_conts ic,
2367: ra_interface_lines il
2368: WHERE ctl.request_id = p_request_id
2427: sysdate,
2428: ct.org_id,
2429: MAX(il.parent_line_id)
2430: FROM ra_customer_trx ct,
2431: ra_customer_trx_lines ctl,
2432: ar_line_conts lrc,
2433: ra_interface_lines il
2434: WHERE ((p_request_id IS NULL AND p_customer_trx_line_id IS NOT NULL AND
2435: ctl.customer_trx_line_id = p_customer_trx_line_id) OR
2469: set original_collectibility_flag = 'Y'
2470: WHERE dl.request_id = p_request_id
2471: and dl.customer_trx_id in
2472: (SELECT ctl.customer_trx_id /* its a pre-billing contingency */
2473: FROM ra_customer_trx_lines_all ctl,
2474: ar_line_conts_all lrc,
2475: ar_deferral_reasons dr
2476: WHERE ctl.customer_trx_line_id = dl.customer_trx_line_id
2477: and ctl.customer_trx_line_id=lrc.customer_trx_line_id
2496: set original_collectibility_flag = 'Y'
2497: WHERE dl.customer_trx_line_id = p_customer_trx_line_id
2498: and dl.customer_trx_id in
2499: (SELECT ctl.customer_trx_id /* its a pre-billing contingency */
2500: FROM ra_customer_trx_lines_all ctl,
2501: ar_line_conts_all lrc,
2502: ar_deferral_reasons dr
2503: WHERE ctl.customer_trx_line_id = dl.customer_trx_line_id
2504: and ctl.customer_trx_line_id=lrc.customer_trx_line_id
3899: p_running_allocated_balance IN OUT NOCOPY NUMBER)
3900: RETURN NUMBER IS
3901:
3902: l_total_amount ar_deferred_lines.amount_recognized%TYPE;
3903: l_computed_line_amount ra_customer_trx_lines.extended_amount%TYPE;
3904:
3905: BEGIN
3906:
3907: /*------------------------------------------------------------------------
4743:
4744: /* Bug 12813416 */
4745: CURSOR c1 IS
4746: SELECT 'its a pre-billing contingency'
4747: FROM ra_customer_trx_lines_all ctl,
4748: ar_line_conts_all lrc,
4749: ar_deferral_reasons dr
4750: WHERE ctl.customer_trx_id = p_customer_trx_id
4751: and ctl.customer_trx_line_id=lrc.customer_trx_line_id
6387: adl.acctd_amount_pending,
6388: SUM(NVL(gld.amount,0)), SUM(NVL(gld.acctd_amount,0)),
6389: SUM(decode(ctrl.customer_trx_id, NULL, 0, 1))
6390: FROM ar_deferred_lines adl,
6391: ra_customer_trx_lines ctrl,
6392: ra_cust_trx_line_gl_dist gld
6393: WHERE adl.customer_trx_id = p_trx_id
6394: AND adl.customer_trx_id = ctrl.previous_customer_trx_id (+)
6395: AND adl.customer_trx_line_id = ctrl.previous_customer_trx_line_id (+)
7415: FUNCTION line_collectibility (
7416: p_request_id NUMBER,
7417: p_source VARCHAR2 DEFAULT NULL,
7418: x_error_count OUT NOCOPY NUMBER,
7419: p_customer_trx_line_id ra_customer_trx_lines.customer_trx_line_id%TYPE )
7420: RETURN long_number_table IS
7421:
7422: /*-----------------------------------------------------------------------+
7423: | Cursor Declarations |
7426: -- This cursor retrieves the revenue management verdicts for each line
7427: CURSOR verdicts IS
7428: SELECT ctl.customer_trx_line_id,
7429: decode(lrs.line_collectible_flag, NULL, 1, 'Y', 1, 'N', 0) verdict
7430: FROM ra_customer_trx_lines ctl,
7431: ar_deferred_lines lrs
7432: WHERE ((p_request_id IS NULL AND
7433: p_customer_trx_line_id IS NOT NULL AND
7434: ctl.customer_trx_line_id = p_customer_trx_line_id) OR