[Home] [Help]
844: -- decode (line_type_lookup_code, 'PREPAY',
845: -- invoice_distribution_id, -1) invoice_distribution_id,
846: -- Bug 6931461: invoice_line_number invoice_line_number
847: decode(line_type_lookup_code, 'PREPAY', invoice_line_number, 1) invoice_line_number
848: FROM ap_invoice_distributions AID, financials_system_parameters FSP
849: WHERE AID.invoice_id = P_invoice_id
850: AND AID.awt_invoice_payment_id is NULL
851: AND AID.org_id = FSP.org_id -- Bug 4516136
852: AND AID.set_of_books_id = FSP.set_of_books_id -- Bug 5608968 Avoid full index scan of fsp
876:
877: CURSOR prepay_adj_events(l_prepay_app_event_id NUMBER,
878: l_accounting_date DATE) is
879: SELECT APPH.accounting_event_id
880: FROM AP_PREPAY_HISTORY_ALL APPH, AP_INVOICE_DISTRIBUTIONS AID
881: WHERE APPH.related_prepay_app_Event_id = l_prepay_app_event_id
882: AND APPH.invoice_adjustment_event_id = AID.accounting_event_id
883: AND nvl(APPH.posted_flag,'N') = 'N'
884: AND AID.accounting_date = l_accounting_date;
908: -- Logging:
909: l_procedure_name CONSTANT VARCHAR2(30) := 'DERIVE_INVOICE_EVENTS';
910: l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
911:
912: TYPE l_inv_dist_list IS TABLE OF ap_invoice_distributions.invoice_distribution_id%TYPE;
913: l_inv_dist_tab l_inv_dist_list;
914:
915: BEGIN
916:
934: l_accounting_event_id := NULL;
935:
936: SELECT COUNT(distinct(accounting_event_id))
937: INTO l_event_num
938: FROM ap_invoice_distributions
939: WHERE invoice_id = p_invoice_id;
940:
941:
942:
956: BEGIN
957:
958: SELECT distinct(AID.accounting_event_id)
959: INTO l_accounting_event_id
960: FROM ap_invoice_distributions AID,
961: ap_invoice_lines AIL
962: WHERE AID.accounting_date = event_dist_rec.accounting_date
963: AND AIL.invoice_id = P_invoice_id
964: AND AIL.line_number = event_dist_rec.invoice_line_number
1032: BEGIN
1033:
1034: SELECT MAX(AID2.accounting_event_id)
1035: INTO l_prepay_app_event_id
1036: FROM AP_INVOICE_DISTRIBUTIONS AID, AP_INVOICE_DISTRIBUTIONS AID2
1037: WHERE AID.invoice_id = p_invoice_id
1038: AND AID.invoice_line_number = event_dist_rec.invoice_line_number
1039: AND AID.parent_reversal_id = AID2.invoice_distribution_id
1040: AND AID.accounting_date = AID2.accounting_date
1279:
1280: BEGIN
1281: SELECT MAX(accounting_event_id)
1282: INTO l_accounting_event_id
1283: FROM ap_invoice_distributions
1284: WHERE invoice_id = p_invoice_id
1285: AND NVL(posted_flag, 'N') <> 'Y'
1286: AND line_type_lookup_code <> 'PREPAY'
1287: AND prepay_distribution_id is NULL --for prepay tax
1294:
1295: BEGIN
1296: SELECT MAX(accounting_event_id)
1297: INTO l_prepay_event_id
1298: FROM ap_invoice_distributions AID
1299: WHERE AID.invoice_id = p_invoice_id
1300: AND AID.line_type_lookup_code = 'PREPAY'
1301: AND AID.amount < 0
1302: AND AID.posted_flag = 'Y';
1459: IF ( l_event_type NOT IN ( PREPAYMENT_APPLIED_TYPE,
1460: PREPAY_APP_ADJUSTED_TYPE,
1461: PREPAYMENT_UNAPPLIED_TYPE)) THEN
1462:
1463: UPDATE ap_invoice_distributions
1464: SET accounting_event_id = l_accounting_event_id
1465: WHERE invoice_id = p_invoice_id
1466: AND accounting_date = event_dist_rec.accounting_date
1467: AND awt_invoice_payment_id IS NULL
1487: AND nvl(cancellation_flag, 'N') = 'N';
1488:
1489: ELSE -- for prepay distribution lines
1490:
1491: UPDATE ap_invoice_distributions
1492: SET accounting_event_id = l_accounting_event_id
1493: WHERE accounting_event_id IS NULL
1494: AND invoice_id = p_invoice_id
1495: AND invoice_line_number = event_dist_rec.invoice_line_number
1499: AND sign(amount) = sign(event_dist_rec.dist_amount)
1500: RETURNING invoice_distribution_id BULK COLLECT INTO l_inv_dist_tab;
1501:
1502: FORALL i IN l_inv_dist_tab.FIRST..l_inv_dist_tab.LAST
1503: UPDATE ap_invoice_distributions_all
1504: SET accounting_event_id = l_accounting_event_id
1505: WHERE line_type_lookup_code in ('REC_TAX','NONREC_TAX',
1506: 'TRV','TIPV','TERV') --Bug5455985
1507: AND accounting_event_id IS NULL
2043: END IF;
2044:
2045: SELECT count(accounting_Event_id)
2046: INTO l_event_count
2047: FROM AP_INVOICE_DISTRIBUTIONS AID
2048: WHERE AID.accounting_Event_id is not null
2049: AND AID.invoice_id = P_Invoice_id;
2050:
2051: IF l_Event_count <> 0 THEN
2068: DECLARE
2069:
2070: CURSOR l_invoice_distributions_cur IS
2071: SELECT distinct(AID.accounting_event_id)
2072: FROM ap_invoice_distributions AID
2073: WHERE AID.invoice_id = p_invoice_id
2074: AND AID.accounting_event_id IS NOT NULL;
2075:
2076: l_invoice_event_status VARCHAR2(1);
2185:
2186: --bug 7011943 There are payment time AWT dists.
2187: select count(*)
2188: into l_count
2189: from ap_invoice_distributions_all
2190: where accounting_event_id = l_accounting_event_ids(i)
2191: and invoice_id = l_event_source_info.source_id_int_1
2192: and awt_invoice_payment_id is not null;
2193:
2604: CURSOR l_invoice_distributions_cur IS
2605: SELECT distinct(AID.invoice_id),
2606: AID.accounting_event_id
2607: FROM ap_invoice_payments AIP,
2608: ap_invoice_distributions AID
2609: WHERE AIP.invoice_id = AID.invoice_id
2610: AND AIP.check_id = p_check_id
2611: AND AID.accounting_event_id IS NOT NULL
2612: ORDER BY AID.invoice_id;
2788: END IF;
2789: --bug 7011943
2790: select count(*)
2791: into l_count
2792: from ap_invoice_distributions_all
2793: where accounting_event_id = l_accounting_event_ids(i)
2794: and invoice_id = l_event_source_info.source_id_int_1
2795: and awt_invoice_payment_id is not null;
2796:
3703: G_MODULE_NAME || l_procedure_name,
3704: l_log_msg);
3705: END IF;
3706:
3707: UPDATE ap_invoice_distributions D
3708: SET D.accounting_event_id = p_event_id
3709: WHERE D.accounting_event_id IS NULL
3710: AND D.awt_invoice_payment_id IN
3711: (SELECT AIP1.invoice_payment_id
3723: G_MODULE_NAME || l_procedure_name,
3724: l_log_msg);
3725: END IF;
3726:
3727: UPDATE ap_invoice_distributions_all D
3728: SET D.accounting_event_id = p_event_id
3729: WHERE D.accounting_event_id IS NULL
3730: AND D.invoice_id IN
3731: (SELECT AI.invoice_id
3852: */
3853:
3854: FORALL i IN 1 .. l_check_ids.count
3855:
3856: UPDATE ap_invoice_distributions AID
3857: SET AID.accounting_event_id = l_accounting_event_ids(i)
3858: WHERE AID.accounting_event_id IS NULL
3859: AND AID.awt_invoice_payment_id IN
3860: (SELECT AIP.invoice_payment_id
3871: */
3872:
3873: FORALL i IN 1 .. l_check_ids.count
3874:
3875: UPDATE ap_invoice_distributions_all AID
3876: SET AID.accounting_event_id = l_accounting_event_ids(i)
3877: WHERE AID.accounting_event_id IS NULL
3878: AND AID.invoice_id IN
3879: ( SELECT AI.invoice_id
3983:
3984: BEGIN
3985: SELECT count(*)
3986: INTO l_count
3987: FROM ap_invoice_distributions AID1
3988: WHERE AID1.invoice_id = p_source_id
3989: AND nvl(AID1.match_status_flag,'N') NOT IN ('T','A');
3990: EXCEPTION
3991: WHEN NO_DATA_FOUND THEN
3996:
3997: BEGIN
3998: SELECT count(*)
3999: INTO l_count
4000: FROM ap_invoice_distributions AID
4001: WHERE AID.invoice_id = p_source_id
4002: AND nvl(AID.match_status_flag,'N') <> 'A';
4003: EXCEPTION
4004: WHEN NO_DATA_FOUND THEN
4025:
4026: BEGIN
4027: SELECT count(*)
4028: INTO l_count
4029: FROM ap_invoice_distributions AID1
4030: WHERE AID1.invoice_id in (SELECT invoice_id
4031: FROM ap_invoice_payments
4032: WHERE check_id = p_source_id)
4033: AND nvl(AID1.match_status_flag,'N') NOT IN ('T','A');
4039:
4040: BEGIN
4041: SELECT count(*)
4042: INTO l_count
4043: FROM ap_invoice_distributions AID
4044: WHERE AID.invoice_id in (SELECT invoice_id
4045: FROM ap_invoice_payments
4046: WHERE check_id = p_source_id)
4047: AND nvl(AID.match_status_flag,'N') <> 'A' ;
4452: CURSOR l_prepayment_applications_cur IS
4453: SELECT AID.invoice_id,
4454: AID.accounting_date,
4455: AID.invoice_line_number
4456: FROM ap_invoice_distributions AID
4457: WHERE AID.invoice_id = p_invoice_id
4458: AND AID.line_type_lookup_code = 'PREPAY'
4459: AND AID.amount < 0
4460: AND AID.posted_flag = 'Y'
5146: IS
5147:
5148: CURSOR inv_cancel_event_dists IS
5149: SELECT accounting_date
5150: FROM ap_invoice_distributions
5151: WHERE invoice_id = p_invoice_id
5152: AND awt_invoice_payment_id IS NULL
5153: AND prepay_distribution_id IS NULL --prepay_tax_parent_id obsoleted
5154: AND accounting_event_id IS NULL
5157: ORDER BY accounting_date;
5158:
5159: CURSOR no_action_prepay_dists IS
5160: SELECT accounting_event_id
5161: FROM ap_invoice_distributions
5162: WHERE invoice_id = p_invoice_id
5163: AND line_type_lookup_code = 'PREPAY'
5164: AND amount <= 0;
5165:
5165:
5166: -- bug 4748638
5167: CURSOR non_preapy_dist_grp_count IS
5168: SELECT accounting_date
5169: FROM ap_invoice_distributions
5170: WHERE invoice_id = p_invoice_id
5171: AND line_type_lookup_code <> 'PREPAY'
5172: AND prepay_distribution_id IS NULL --prepay_tax_parent_id obsoleted
5173: GROUP BY accounting_date;
5175:
5176: -- bug fix for 4881719
5177: CURSOR all_unaccounted_dists_cur IS
5178: SELECT accounting_event_id
5179: FROM ap_invoice_distributions
5180: WHERE invoice_id = p_invoice_id
5181: AND posted_flag <> 'Y';
5182:
5183:
5283:
5284: END LOOP;
5285:
5286: FORALL i IN 1 .. l_accounting_event_dates.count
5287: UPDATE ap_invoice_distributions
5288: SET accounting_event_id = l_accounting_event_ids(i)
5289: WHERE invoice_id = p_invoice_id
5290: AND accounting_date = l_accounting_event_dates(i)
5291: AND awt_invoice_payment_id IS NULL
5721: AID.invoice_id,
5722: AID.org_id,
5723: AI.legal_entity_id,
5724: AID.set_of_books_id ledger_id
5725: FROM ap_invoice_distributions_all AID,
5726: ap_invoices_all AI
5727: WHERE AP_UTILITIES_PKG.GET_CURRENT_GL_DATE(TRUNC(AID.accounting_date), aid.org_id) --bug5956469
5728: = p_period_name
5729: AND aid.posted_flag IN ('N', 'S') -- Bug 6869699
5753: AID.invoice_id,
5754: AID.org_id,
5755: AI.legal_entity_id,
5756: AID.set_of_books_id ledger_id
5757: FROM ap_invoice_distributions_all AID,
5758: ap_invoices_all AI
5759: WHERE AID.accounting_date BETWEEN p_from_date AND p_to_date
5760: AND aid.posted_flag IN ('N', 'S') -- Bug 6869699
5761: AND aid.org_id = ai.org_id
6214: AID.invoice_id,
6215: AID.org_id,
6216: AI.legal_entity_id,
6217: AID.set_of_books_id ledger_id
6218: FROM ap_invoice_distributions AID,
6219: ap_invoices AI
6220: WHERE ( ( p_period_name IS NULL AND
6221: AID.accounting_date BETWEEN p_from_date AND p_to_date)
6222: OR
7157: AIL.invoice_id,
7158: AIL.line_number,
7159: AIL.org_id
7160: FROM ap_invoice_lines AIL,
7161: ap_invoice_distributions AID,
7162: ap_invoice_distributions AID1
7163: WHERE AID.invoice_id = p_invoice_id
7164: AND AID.invoice_line_number = p_invoice_line_number
7165: AND AID.line_type_lookup_code = 'PREPAY'
7158: AIL.line_number,
7159: AIL.org_id
7160: FROM ap_invoice_lines AIL,
7161: ap_invoice_distributions AID,
7162: ap_invoice_distributions AID1
7163: WHERE AID.invoice_id = p_invoice_id
7164: AND AID.invoice_line_number = p_invoice_line_number
7165: AND AID.line_type_lookup_code = 'PREPAY'
7166: AND AID.prepay_distribution_id = AID1.invoice_distribution_id
7217: BEGIN
7218:
7219: SELECT min(accounting_Event_id)
7220: INTO l_related_prepay_app_event_id
7221: FROM AP_INVOICE_DISTRIBUTIONS AID
7222: WHERE AID.line_type_lookup_code = 'PREPAY'
7223: AND nvl(posted_flag,'N') = 'Y'
7224: AND nvl(AID.amount,0) < 0
7225: AND AID.invoice_id = P_invoice_id
7306: BEGIN
7307:
7308: SELECT min(accounting_Event_id)
7309: INTO l_related_prepay_app_event_id
7310: FROM AP_INVOICE_DISTRIBUTIONS AID
7311: WHERE AID.line_type_lookup_code = 'PREPAY'
7312: AND nvl(posted_flag,'N') = 'Y'
7313: AND nvl(AID.amount,0) < 0
7314: AND AID.invoice_id = P_invoice_id