[Home] [Help]
973: charge_applicable_to_dist_id ,
974: cancellation_flag ,
975: match_status_flag ,
976: awt_invoice_payment_id
977: FROM ap_invoice_distributions_all
978: UNION
979: SELECT accounting_date ,
980: accounting_event_id ,
981: invoice_line_number,
1021:
1022: CURSOR prepay_adj_events(l_prepay_app_event_id NUMBER,
1023: l_accounting_date DATE) is
1024: SELECT APPH.accounting_event_id
1025: FROM AP_PREPAY_HISTORY_ALL APPH, AP_INVOICE_DISTRIBUTIONS AID
1026: WHERE APPH.related_prepay_app_Event_id = l_prepay_app_event_id
1027: AND APPH.invoice_adjustment_event_id = AID.accounting_event_id
1028: AND nvl(APPH.posted_flag,'N') = 'N';
1029: --AND AID.accounting_date = l_accounting_date; Commented for bug 10132577
1053: -- Logging:
1054: l_procedure_name CONSTANT VARCHAR2(30) := 'DERIVE_INVOICE_EVENTS';
1055: l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
1056:
1057: TYPE l_inv_dist_list IS TABLE OF ap_invoice_distributions.invoice_distribution_id%TYPE;
1058: l_inv_dist_tab l_inv_dist_list;
1059:
1060: BEGIN
1061:
1081: l_prepay_event_id := NULL; --bug9973070
1082:
1083: SELECT COUNT(distinct(accounting_event_id))
1084: INTO l_event_num
1085: FROM ap_invoice_distributions
1086: WHERE invoice_id = p_invoice_id;
1087:
1088:
1089:
1103: BEGIN
1104: --11659317
1105: SELECT distinct(AID.accounting_event_id)
1106: INTO l_accounting_event_id
1107: FROM ap_invoice_distributions AID,
1108: ap_invoice_lines AIL,
1109: xla_events xe
1110: WHERE AID.accounting_date = event_dist_rec.accounting_date
1111: AND AIL.invoice_id = P_invoice_id
1184:
1185: BEGIN
1186: SELECT MAX(AID2.accounting_event_id)
1187: INTO l_prepay_app_event_id
1188: FROM AP_INVOICE_DISTRIBUTIONS AID, AP_INVOICE_DISTRIBUTIONS AID2
1189: WHERE AID.invoice_id = p_invoice_id
1190: AND AID.invoice_line_number = event_dist_rec.invoice_line_number
1191: AND AID.parent_reversal_id = AID2.invoice_distribution_id
1192: --AND AID.accounting_date = AID2.accounting_date Commented for bug 10132577
1433: --11659317
1434: BEGIN
1435: SELECT MAX(accounting_event_id)
1436: INTO l_accounting_event_id
1437: FROM ap_invoice_distributions,
1438: xla_events xe,
1439: xla_transaction_entities_upg xte --bug 13508211
1440: WHERE invoice_id = p_invoice_id
1441: AND NVL(posted_flag, 'N') <> 'Y'
1462: -- as well.
1463: --
1464: SELECT MAX(accounting_event_id)
1465: INTO l_prepay_event_id
1466: FROM ap_invoice_distributions AID
1467: WHERE AID.invoice_id = p_invoice_id
1468: AND AID.line_type_lookup_code = 'PREPAY'
1469: AND AID.amount < 0
1470: AND (AID.posted_flag = 'Y' OR
1525:
1526: /* start commented by abhsaxen for bug 9159069
1527: * Moving this code because cascade event will work only
1528: * if invoice_distribution is populated earlier
1529: * This code is added below (after ap_invoice_distributions update statements).
1530:
1531: -- raise payment/prepayment cascade event only when there is payment
1532: -- or prepayment application existed for the invoice.
1533:
1634: IF ( l_event_type NOT IN ( PREPAYMENT_APPLIED_TYPE,
1635: PREPAY_APP_ADJUSTED_TYPE,
1636: PREPAYMENT_UNAPPLIED_TYPE)) THEN
1637:
1638: UPDATE ap_invoice_distributions
1639: SET accounting_event_id = l_accounting_event_id
1640: WHERE invoice_id = p_invoice_id
1641: AND accounting_date = event_dist_rec.accounting_date
1642: AND awt_invoice_payment_id IS NULL
1669: -- for amount 0
1670: --
1671: IF l_event_type = PREPAYMENT_APPLIED_TYPE THEN
1672:
1673: UPDATE ap_invoice_distributions
1674: SET accounting_event_id = l_accounting_event_id
1675: WHERE accounting_event_id IS NULL
1676: AND invoice_id = p_invoice_id
1677: AND invoice_line_number = event_dist_rec.invoice_line_number
1682: RETURNING invoice_distribution_id BULK COLLECT INTO l_inv_dist_tab;
1683:
1684: ELSE
1685:
1686: UPDATE ap_invoice_distributions
1687: SET accounting_event_id = l_accounting_event_id
1688: WHERE accounting_event_id IS NULL
1689: AND invoice_id = p_invoice_id
1690: AND invoice_line_number = event_dist_rec.invoice_line_number
1697: END IF;
1698:
1699:
1700: FORALL i IN l_inv_dist_tab.FIRST..l_inv_dist_tab.LAST
1701: UPDATE ap_invoice_distributions_all
1702: SET accounting_event_id = l_accounting_event_id
1703: WHERE line_type_lookup_code in ('REC_TAX','NONREC_TAX',
1704: 'TRV','TIPV','TERV') --Bug5455985
1705: AND accounting_event_id IS NULL
2544: END IF;
2545:
2546: SELECT count(accounting_Event_id)
2547: INTO l_event_count
2548: FROM AP_INVOICE_DISTRIBUTIONS AID
2549: WHERE AID.accounting_Event_id is not null
2550: AND AID.invoice_id = P_Invoice_id;
2551:
2552: IF l_Event_count <> 0 THEN
2569: DECLARE
2570:
2571: CURSOR l_invoice_distributions_cur IS
2572: SELECT distinct(AID.accounting_event_id)
2573: FROM ap_invoice_distributions AID
2574: WHERE AID.invoice_id = p_invoice_id
2575: AND AID.accounting_event_id IS NOT NULL;
2576:
2577: l_invoice_event_status VARCHAR2(1);
2686:
2687: --bug 7011943 There are payment time AWT dists.
2688: select count(*)
2689: into l_count
2690: from ap_invoice_distributions_all
2691: where accounting_event_id = l_accounting_event_ids(i)
2692: and invoice_id = l_event_source_info.source_id_int_1
2693: and awt_invoice_payment_id is not null;
2694:
3124: CURSOR l_invoice_distributions_cur IS
3125: SELECT distinct(AID.invoice_id),
3126: AID.accounting_event_id
3127: FROM ap_invoice_payments AIP,
3128: ap_invoice_distributions AID
3129: WHERE AIP.invoice_id = AID.invoice_id
3130: AND AIP.check_id = p_check_id
3131: AND AID.accounting_event_id IS NOT NULL
3132: ORDER BY AID.invoice_id;
3308: END IF;
3309: --bug 7011943
3310: select count(*)
3311: into l_count
3312: from ap_invoice_distributions_all
3313: where accounting_event_id = l_accounting_event_ids(i)
3314: and invoice_id = l_event_source_info.source_id_int_1
3315: and awt_invoice_payment_id is not null;
3316:
4244: G_MODULE_NAME || l_procedure_name,
4245: l_log_msg);
4246: END IF;
4247:
4248: UPDATE ap_invoice_distributions D
4249: SET D.accounting_event_id = p_event_id
4250: WHERE D.accounting_event_id IS NULL
4251: AND D.awt_invoice_payment_id IN
4252: (SELECT AIP1.invoice_payment_id
4264: G_MODULE_NAME || l_procedure_name,
4265: l_log_msg);
4266: END IF;
4267:
4268: UPDATE ap_invoice_distributions_all D
4269: SET D.accounting_event_id = p_event_id
4270: WHERE D.accounting_event_id IS NULL
4271: AND D.invoice_id IN
4272: (SELECT AI.invoice_id
4402: */
4403:
4404: FORALL i IN 1 .. l_check_ids.count
4405:
4406: UPDATE ap_invoice_distributions AID
4407: SET AID.accounting_event_id = l_accounting_event_ids(i)
4408: WHERE AID.accounting_event_id IS NULL
4409: AND AID.awt_invoice_payment_id IN
4410: (SELECT AIP.invoice_payment_id
4437: /* Added for bug#8438184 Start */
4438:
4439: FORALL i IN 1 .. l_check_ids.count
4440:
4441: UPDATE ap_invoice_distributions_all AID
4442: SET AID.accounting_event_id = l_accounting_event_ids(i)
4443: WHERE AID.accounting_event_id IS NULL
4444: AND AID.invoice_id IN
4445: ( SELECT AI.invoice_id
4553:
4554: BEGIN
4555: SELECT count(*)
4556: INTO l_count
4557: FROM ap_invoice_distributions AID1
4558: WHERE AID1.invoice_id = p_source_id
4559: AND nvl(AID1.match_status_flag,'N') NOT IN ('T','A');
4560: EXCEPTION
4561: WHEN NO_DATA_FOUND THEN
4566:
4567: BEGIN
4568: SELECT count(*)
4569: INTO l_count
4570: FROM ap_invoice_distributions AID
4571: WHERE AID.invoice_id = p_source_id
4572: AND nvl(AID.match_status_flag,'N') <> 'A';
4573: EXCEPTION
4574: WHEN NO_DATA_FOUND THEN
4595:
4596: BEGIN
4597: SELECT count(*)
4598: INTO l_count
4599: FROM ap_invoice_distributions AID1
4600: WHERE AID1.invoice_id in (SELECT invoice_id
4601: FROM ap_invoice_payments
4602: WHERE check_id = p_source_id)
4603: AND nvl(AID1.match_status_flag,'N') NOT IN ('T','A');
4609:
4610: BEGIN
4611: SELECT count(*)
4612: INTO l_count
4613: FROM ap_invoice_distributions AID
4614: WHERE AID.invoice_id in (SELECT invoice_id
4615: FROM ap_invoice_payments
4616: WHERE check_id = p_source_id)
4617: AND nvl(AID.match_status_flag,'N') <> 'A' ;
5040: --
5041: -- bug9973070, added the condition AID.Accounting_Event_id
5042: -- IS NOT NULL, removed outer join with XLA_EVENTS
5043: --
5044: /* Bug 9682862 added exists condition for ap_invoice_distributions table*/
5045:
5046: CURSOR l_prepayment_applications_cur IS
5047: SELECT DISTINCT
5048: AID.invoice_id,
5047: SELECT DISTINCT
5048: AID.invoice_id,
5049: AID.accounting_date,
5050: AID.invoice_line_number
5051: FROM ap_invoice_distributions AID,
5052: xla_events XE
5053: WHERE AID.invoice_id = p_invoice_id
5054: AND AID.line_type_lookup_code = 'PREPAY'
5055: AND AID.Accounting_Event_ID IS NOT NULL
5070: AND APPH.Related_Prepay_App_Event_Id = AID.Accounting_Event_Id
5071: AND APPH.transaction_type = 'PREPAYMENT APPLICATION ADJ'
5072: AND nvl(APPH.posted_flag, 'N') <> 'Y')
5073: AND EXISTS (SELECT 'INVOICE DIST WITH INV ADJ EVENT ID'
5074: FROM ap_invoice_distributions aid2
5075: WHERE aid2.accounting_event_id=p_adj_accounting_event_id
5076: AND aid2.invoice_id = p_invoice_id
5077: AND aid2.line_type_lookup_code <> 'AWT'); --bug 9682862
5078:
5076: AND aid2.invoice_id = p_invoice_id
5077: AND aid2.line_type_lookup_code <> 'AWT'); --bug 9682862
5078:
5079: /* BUG # 7560346 added DISTINCT keyword */
5080: /* Bug 9103993 added exists condition for ap_invoice_distributions table*/
5081: /* Bug 9582285 extended the changes done in bug 9103993 to cursors
5082: l_payment_clearings_cur and l_payment_maturities_cur and added
5083: AWT decode on top of the Exists clause added by bug 9103993 for all
5084: cursors*/
5107: FROM AP_Payment_History APH
5108: WHERE APH.Check_ID = AIP.Check_ID
5109: AND APH.transaction_type='PAYMENT CANCELLED')
5110: AND EXISTS (SELECT 'INVOICE DIST WITH INV ADJ EVENT ID'
5111: FROM ap_invoice_distributions aid
5112: where aid.accounting_event_id=p_adj_accounting_event_id
5113: and aid.invoice_id = p_invoice_id
5114: and decode(aid.line_type_lookup_code, 'AWT',
5115: decode(aid.awt_invoice_payment_id,
5152: OR APH1.transaction_type = 'PAYMENT CANCELLED'))
5153: /*bug 8882614, added the OR condition above to prevent clearing adjustment
5154: events from being created if cancellation event exists*/
5155: AND EXISTS (SELECT 'INVOICE DIST WITH INV ADJ EVENT ID'
5156: FROM ap_invoice_distributions aid
5157: where aid.accounting_event_id=p_adj_accounting_event_id
5158: and aid.invoice_id = p_invoice_id
5159: and decode(aid.line_type_lookup_code, 'AWT',
5160: decode(aid.awt_invoice_payment_id,
5193: OR APH1.transaction_type = 'PAYMENT CANCELLED'))
5194: /*bug 8882614, added the OR condition above to prevent maturity adjustment
5195: events from being created if cancellation event exists*/
5196: AND EXISTS (SELECT 'INVOICE DIST WITH INV ADJ EVENT ID'
5197: FROM ap_invoice_distributions aid
5198: where aid.accounting_event_id=p_adj_accounting_event_id
5199: and aid.invoice_id = p_invoice_id
5200: and decode(aid.line_type_lookup_code, 'AWT',
5201: decode(aid.awt_invoice_payment_id,
5311: l_count := 0;
5312: BEGIN
5313: SELECT count(1)
5314: INTO l_count
5315: FROM ap_invoice_distributions_all aid
5316: WHERE aid.invoice_id = l_prepay_app_invoice_ids(i)
5317: AND aid.accounting_event_id = p_adj_accounting_event_id
5318: AND NOT EXISTS
5319: (SELECT 1
5911:
5912: /* bug 11663644 added ap_self_assessed_tax_dist_all */
5913: CURSOR inv_cancel_event_dists IS
5914: SELECT accounting_date
5915: FROM ap_invoice_distributions
5916: WHERE invoice_id = p_invoice_id
5917: AND awt_invoice_payment_id IS NULL
5918: AND prepay_distribution_id IS NULL --prepay_tax_parent_id obsoleted
5919: AND accounting_event_id IS NULL
6036:
6037: END LOOP;
6038:
6039: FORALL i IN 1 .. l_accounting_event_dates.count
6040: UPDATE ap_invoice_distributions
6041: SET accounting_event_id = l_accounting_event_ids(i)
6042: WHERE invoice_id = p_invoice_id
6043: AND accounting_date = l_accounting_event_dates(i)
6044: AND awt_invoice_payment_id IS NULL
6465: AID.invoice_id,
6466: AID.org_id,
6467: AI.legal_entity_id,
6468: AID.set_of_books_id ledger_id
6469: FROM ap_invoice_distributions_all AID,
6470: ap_invoices_all AI
6471: WHERE AP_UTILITIES_PKG.GET_CURRENT_GL_DATE(TRUNC(AID.accounting_date), aid.org_id) --bug5956469
6472: = p_period_name
6473: AND aid.posted_flag IN ('N', 'S') -- Bug 6869699
6497: AID.invoice_id,
6498: AID.org_id,
6499: AI.legal_entity_id,
6500: AID.set_of_books_id ledger_id
6501: FROM ap_invoice_distributions_all AID,
6502: ap_invoices_all AI
6503: WHERE AID.accounting_date BETWEEN p_from_date AND p_to_date
6504: AND aid.posted_flag IN ('N', 'S') -- Bug 6869699
6505: AND aid.org_id = ai.org_id
6958: AID.invoice_id,
6959: AID.org_id,
6960: AI.legal_entity_id,
6961: AID.set_of_books_id ledger_id
6962: FROM ap_invoice_distributions AID,
6963: ap_invoices AI
6964: WHERE ( ( p_period_name IS NULL AND
6965: AID.accounting_date BETWEEN p_from_date AND p_to_date)
6966: OR
7815: -- the Accounting Event ID
7816: --
7817: SELECT min(accounting_Event_id)
7818: INTO l_related_prepay_app_event_id
7819: FROM AP_Invoice_Distributions_All AID
7820: WHERE AID.line_type_lookup_code = 'PREPAY'
7821: AND nvl(AID.amount,0) < 0
7822: AND AID.invoice_id = P_invoice_id
7823: AND AID.invoice_line_number = P_invoice_line_number;
7923: AIL.invoice_id,
7924: AIL.line_number,
7925: AIL.org_id
7926: FROM ap_invoice_lines AIL,
7927: ap_invoice_distributions AID,
7928: ap_invoice_distributions AID1
7929: WHERE AID.invoice_id = p_invoice_id
7930: AND AID.invoice_line_number = p_invoice_line_number
7931: AND AID.line_type_lookup_code = 'PREPAY'
7924: AIL.line_number,
7925: AIL.org_id
7926: FROM ap_invoice_lines AIL,
7927: ap_invoice_distributions AID,
7928: ap_invoice_distributions AID1
7929: WHERE AID.invoice_id = p_invoice_id
7930: AND AID.invoice_line_number = p_invoice_line_number
7931: AND AID.line_type_lookup_code = 'PREPAY'
7932: AND AID.prepay_distribution_id = AID1.invoice_distribution_id
7983: BEGIN
7984:
7985: SELECT min(accounting_Event_id)
7986: INTO l_related_prepay_app_event_id
7987: FROM AP_INVOICE_DISTRIBUTIONS AID
7988: WHERE AID.line_type_lookup_code = 'PREPAY'
7989: AND (nvl(posted_flag,'N') = 'Y' OR
7990: nvl(encumbered_flag, 'N') = 'Y') --bug9973070
7991: AND nvl(AID.amount,0) < 0
8073: BEGIN
8074:
8075: SELECT min(accounting_Event_id)
8076: INTO l_related_prepay_app_event_id
8077: FROM AP_INVOICE_DISTRIBUTIONS AID
8078: WHERE AID.line_type_lookup_code = 'PREPAY'
8079: AND (nvl(posted_flag,'N') = 'Y' OR
8080: nvl(encumbered_flag, 'N') = 'Y') --bug9973070
8081: AND nvl(AID.amount,0) < 0
8174:
8175: CURSOR reversed_prepay_events IS
8176: SELECT DISTINCT aid.invoice_id,
8177: aid.accounting_event_id
8178: FROM ap_invoice_distributions_all aid,
8179: ap_invoices_all ai
8180: WHERE ai.invoice_id = p_invoice_id
8181: AND aid.invoice_id = ai.invoice_id
8182: --AND ai.cancelled_date IS NOT NULL --bug9441420
8186: AND NVL(aid.posted_flag, 'N') <> 'Y'
8187: AND NVL(aid.reversal_flag, 'N') = 'Y'
8188: AND EXISTS
8189: (SELECT 1
8190: FROM ap_invoice_distributions_all aid1
8191: WHERE aid1.invoice_id = aid.invoice_id
8192: AND aid1.line_type_lookup_code IN ('PREPAY','REC_TAX','NONREC_TAX')
8193: AND aid1.prepay_distribution_id IS NOT NULL
8194: AND aid1.accounting_event_id IS NOT NULL
8199: NVL(aid.parent_reversal_id, aid.invoice_distribution_id))
8200: AND ((ai.cancelled_date IS NOT NULL AND
8201: NOT EXISTS
8202: (SELECT 1
8203: FROM ap_invoice_distributions_all aid1
8204: WHERE aid1.invoice_id = ai.invoice_id
8205: AND aid1.prepay_distribution_id IS NULL
8206: )
8207: ) OR --bug9441420 added OR clause
8207: ) OR --bug9441420 added OR clause
8208: (NOT EXISTS
8209: (SELECT 1
8210: FROM ap_invoice_payments_all aip,
8211: ap_invoice_distributions_all aid_prepay
8212: WHERE aip.invoice_id = aid_prepay.invoice_id
8213: AND aid_prepay.invoice_distribution_id = aid.prepay_distribution_id
8214: AND nvl(aip.reversal_flag, 'N') = 'N'
8215: ))
8217: /* RCA bug 14120800*/
8218: UNION
8219: SELECT app_aid.invoice_id,
8220: unapp_aid.accounting_Event_id unapp_event_id
8221: FROM ap_invoice_distributions_all app_aid,
8222: xla_events xe_app,
8223: ap_invoices_all ai,
8224: ap_invoice_distributions_all unapp_aid,
8225: xla_events xe_unapp
8220: unapp_aid.accounting_Event_id unapp_event_id
8221: FROM ap_invoice_distributions_all app_aid,
8222: xla_events xe_app,
8223: ap_invoices_all ai,
8224: ap_invoice_distributions_all unapp_aid,
8225: xla_events xe_unapp
8226: WHERE ai.invoice_id = p_invoice_id
8227: AND app_aid.prepay_distribution_id IS NOT NULL
8228: AND app_aid.accounting_event_id = xe_app.event_id