[Home] [Help]
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,
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;
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,
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;
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,
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;
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,
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 */
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,
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 */
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,
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:
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
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
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
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'
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
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
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)
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
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,
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
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
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
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
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
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
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);
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,
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
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);
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()-');
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,
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,
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
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,
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,
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
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
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
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
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))
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
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;
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
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),
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'
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,
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'
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'
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),
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
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()-');
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
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'
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'
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);