[Home] [Help]
303:
304:
305: /* Bug#11868928 - Start - updating the assets_addition_flag to N for the non-asset tax distributions */
306:
307: UPDATE /*+ index(apid AP_INVOICE_DISTRIBUTIONS_N31)*/ ap_invoice_distributions APID --Bug 14314657 added hint
308: SET APID.assets_addition_flag = 'N'
309: WHERE APID.assets_addition_flag = 'U'
310: AND APID.line_type_lookup_code IN ('REC_TAX','NONREC_TAX','MISCELLANEOUS','FREIGHT')
311: AND APID.accounting_date <= P_acctg_date --Bug 14314657
313: AND APID.posted_flag = 'Y'
314: AND apid.set_of_books_id = P_ledger_id
315: AND apid.assets_tracking_flag = 'N'; -- Bug 13821160 --Bug 14314657 changed <> 'Y' to = 'N'
316:
317: UPDATE /*+ index(apid AP_INVOICE_DISTRIBUTIONS_N31)*/ ap_invoice_distributions APID --Bug 14314657 added hint
318: SET APID.assets_addition_flag = 'N'
319: WHERE apid.set_of_books_id = P_ledger_id
320: AND APID.assets_addition_flag = 'U'
321: AND APID.line_type_lookup_code NOT IN ('ITEM','ACCRUAL')
322: AND APID.accounting_date <= P_acctg_date --Bug 14314657
323: AND APID.posted_flag = 'Y'
324: AND NVL(NVL(apid.charge_applicable_to_dist_id, apid.corrected_invoice_dist_id), apid.related_id) IS NOT NULL --Bug 14314657
325: AND EXISTS (SELECT /*+ push_subq no_unnest */ 'non asset ITEM' --Bug 14242750
326: FROM ap_invoice_distributions_all item
327: WHERE item.assets_tracking_flag = 'N'
328: AND item.invoice_distribution_id =
329: NVL(NVL(apid.charge_applicable_to_dist_id, apid.corrected_invoice_dist_id), apid.related_id)
330: UNION ALL /*Bug 14242750 update aaf to N for charge dists for which related ITEM has aaf as N and atf as Y*/
328: AND item.invoice_distribution_id =
329: NVL(NVL(apid.charge_applicable_to_dist_id, apid.corrected_invoice_dist_id), apid.related_id)
330: UNION ALL /*Bug 14242750 update aaf to N for charge dists for which related ITEM has aaf as N and atf as Y*/
331: SELECT /*+ push_subq no_unnest */ 'non asset ITEM'
332: FROM ap_invoice_distributions_all item
333: WHERE item.assets_tracking_flag = 'Y' AND item.assets_addition_flag = 'N'
334: AND item.invoice_distribution_id =
335: NVL(NVL(apid.charge_applicable_to_dist_id, apid.corrected_invoice_dist_id), apid.related_id)); -- Bug Bug 12660674
336:
345: AND APIP.accounting_date <= P_acctg_date
346: AND APIP.set_of_books_id = P_ledger_id
347: AND NOT EXISTS (SELECT /*+ push_subq no_unnest */ 'Payment with discount and ATF not N'
348: FROM ap_payment_hist_dists APHD,
349: ap_invoice_distributions_all APID
350: WHERE APIP.accounting_event_id = APHD.accounting_event_id
351: AND APIP.invoice_payment_id = APHD.invoice_payment_id
352: AND APHD.pay_dist_lookup_code = 'DISCOUNT'
353: AND APHD.invoice_distribution_id = APID.invoice_distribution_id
357: AND ((APID.line_type_lookup_code IN ('ITEM', 'ACCRUAL')
358: AND APID.assets_tracking_flag = 'Y')
359: OR EXISTS
360: ( SELECT /*+ push_subq no_unnest */ 'X'
361: FROM ap_invoice_distributions_all APIDV
362: WHERE COALESCE(APID.charge_applicable_to_dist_id, apid.corrected_invoice_dist_id, APID.related_id) =
363: APIDV.invoice_distribution_id
364: AND APIDV.invoice_distribution_id <> NVL(APIDV.related_id, -1)
365: AND APIDV.assets_tracking_flag = 'Y'
385:
386: --This insert statement below was added for Bug 7284987 / 7392117
387: -- for bug 9669334 we have spilt the query. first part is for ITEM and ACCRUAL.
388: -- second one is for other line type lookups.
389: INSERT INTO ap_invoice_distributions_gt
390: (invoice_distribution_id,
391: invoice_id,
392: invoice_line_number,
393: po_distribution_id,
407: charge_applicable_to_dist_id,
408: asset_book_type_code,
409: set_of_books_id
410: )
411: SELECT /*+ index(apid AP_INVOICE_DISTRIBUTIONS_N31)*/
412: APID.invoice_distribution_id,
413: APID.invoice_id,
414: APID.invoice_line_number,
415: APID.po_distribution_id,
428: APID.related_id,
429: APID.charge_applicable_to_dist_id,
430: APID.asset_book_type_code,
431: APID.set_of_books_id
432: FROM ap_invoice_distributions APID
433: WHERE APID.accounting_date <= P_acctg_date
434: AND APID.assets_addition_flag = 'U'
435: AND APID.line_type_lookup_code IN ('ITEM','ACCRUAL')
436: AND apid.assets_tracking_flag = 'Y'
449: AND (APID.asset_book_type_code = P_bt_code
450: OR APID.asset_book_type_code IS NULL)
451: -- bug 8690407: add end
452: UNION ALL
453: SELECT /*+ index(apid AP_INVOICE_DISTRIBUTIONS_N31)*/
454: APID.invoice_distribution_id,
455: APID.invoice_id,
456: APID.invoice_line_number,
457: APID.po_distribution_id,
470: APID.related_id,
471: APID.charge_applicable_to_dist_id,
472: nvl(APID.asset_book_type_code,item.asset_book_type_code),
473: APID.set_of_books_id
474: FROM ap_invoice_distributions APID,
475: ap_invoice_distributions_all item
476: WHERE APID.accounting_date <= P_acctg_date
477: AND APID.assets_addition_flag = 'U'
478: AND APID.line_type_lookup_code NOT IN ('ITEM','ACCRUAL')
471: APID.charge_applicable_to_dist_id,
472: nvl(APID.asset_book_type_code,item.asset_book_type_code),
473: APID.set_of_books_id
474: FROM ap_invoice_distributions APID,
475: ap_invoice_distributions_all item
476: WHERE APID.accounting_date <= P_acctg_date
477: AND APID.assets_addition_flag = 'U'
478: AND APID.line_type_lookup_code NOT IN ('ITEM','ACCRUAL')
479: AND item.assets_tracking_flag = 'Y'
500: -- bug 8690407: add end
501: -- bug 7215835: add start
502: UNION ALL
503: -- Bug 13821160: Add code to allow Misc/Frieight/Tax lines (not allocated) interface to FA
504: SELECT /*+ index(apid AP_INVOICE_DISTRIBUTIONS_N31)*/
505: APID.invoice_distribution_id,
506: APID.invoice_id,
507: APID.invoice_line_number,
508: APID.po_distribution_id,
521: APID.related_id,
522: APID.charge_applicable_to_dist_id,
523: APID.asset_book_type_code,
524: APID.set_of_books_id
525: FROM ap_invoice_distributions_all APID
526: WHERE APID.accounting_date <= P_acctg_date
527: AND APID.assets_addition_flag = 'U'
528: AND APID.line_type_lookup_code IN ('MISCELLANEOUS', 'FREIGHT', 'NONREC_TAX','REC_TAX')
529: AND APID.assets_tracking_flag = 'Y'
561: satx.related_id,
562: satx.charge_applicable_to_dist_id,
563: nvl(satx.asset_book_type_code, item.asset_book_type_code),
564: satx.set_of_books_id
565: FROM ap_invoice_distributions_all item,
566: ap_self_assessed_tax_dist satx
567: WHERE satx.accounting_date <= P_acctg_date
568: AND satx.assets_addition_flag = 'U'
569: AND item.assets_tracking_flag = 'Y'
815: APIL.warranty_number,
816: APIDG.line_type_lookup_code,
817: POD.po_distribution_id,
818: 'NEW'
819: FROM ap_invoice_distributions_gt APIDG,
820: ap_invoice_lines_all APIL,
821: ap_invoices_all API,
822: financials_system_params_all FSP, -- changed table order # 9669334
823: ap_batches_all APB,
884:
885: --This insert statement below was added for Bug 7284987 / 7392117
886: -- for bug 9669334 we have spilt the query. first part is for ITEM and ACCRUAL.
887: -- second one is for other line type lookups.
888: INSERT INTO ap_invoice_distributions_gt
889: (invoice_distribution_id,
890: invoice_id,
891: invoice_line_number,
892: po_distribution_id,
906: charge_applicable_to_dist_id,
907: asset_book_type_code,
908: set_of_books_id
909: )
910: SELECT /*+ index(apid AP_INVOICE_DISTRIBUTIONS_N31)*/
911: APID.invoice_distribution_id,
912: APID.invoice_id,
913: APID.invoice_line_number,
914: APID.po_distribution_id,
927: APID.related_id,
928: APID.charge_applicable_to_dist_id,
929: APID.asset_book_type_code,
930: APID.set_of_books_id
931: FROM ap_invoice_distributions APID
932: WHERE APID.accounting_date <= P_acctg_date
933: AND APID.assets_addition_flag = 'U'
934: AND APID.line_type_lookup_code IN ('ITEM','ACCRUAL')
935: AND apid.assets_tracking_flag = 'Y'
947: AND APID.set_of_books_id = P_ledger_id
948: AND (APID.asset_book_type_code = P_bt_code OR
949: APID.asset_book_type_code IS NULL)
950: UNION ALL
951: SELECT /*+ index(apid AP_INVOICE_DISTRIBUTIONS_N31)*/
952: APID.invoice_distribution_id,
953: APID.invoice_id,
954: APID.invoice_line_number,
955: APID.po_distribution_id,
968: APID.related_id,
969: APID.charge_applicable_to_dist_id,
970: nvl(APID.asset_book_type_code,item.asset_book_type_code),
971: APID.set_of_books_id
972: FROM ap_invoice_distributions APID,
973: ap_invoice_distributions_all item
974: WHERE APID.accounting_date <= P_acctg_date
975: AND APID.assets_addition_flag = 'U'
976: AND APID.line_type_lookup_code NOT IN ('ITEM','ACCRUAL')
969: APID.charge_applicable_to_dist_id,
970: nvl(APID.asset_book_type_code,item.asset_book_type_code),
971: APID.set_of_books_id
972: FROM ap_invoice_distributions APID,
973: ap_invoice_distributions_all item
974: WHERE APID.accounting_date <= P_acctg_date
975: AND APID.assets_addition_flag = 'U'
976: AND APID.line_type_lookup_code NOT IN ('ITEM','ACCRUAL')
977: AND item.assets_tracking_flag = 'Y'
998: OR APID.asset_book_type_code IS NULL)
999: -- bug 8690407: add end
1000: UNION ALL
1001: -- Bug 13821160: Add code to allow Misc/Frieight/Tax lines (not allocated) interface to FA
1002: SELECT /*+ index(apid AP_INVOICE_DISTRIBUTIONS_N31)*/
1003: APID.invoice_distribution_id,
1004: APID.invoice_id,
1005: APID.invoice_line_number,
1006: APID.po_distribution_id,
1019: APID.related_id,
1020: APID.charge_applicable_to_dist_id,
1021: APID.asset_book_type_code,
1022: APID.set_of_books_id
1023: FROM ap_invoice_distributions_all APID
1024: WHERE APID.accounting_date <= P_acctg_date
1025: AND APID.assets_addition_flag = 'U'
1026: AND APID.line_type_lookup_code IN ('MISCELLANEOUS', 'FREIGHT', 'NONREC_TAX','REC_TAX')
1027: AND APID.assets_tracking_flag = 'Y'
1060: satx.related_id,
1061: satx.charge_applicable_to_dist_id,
1062: nvl(satx.asset_book_type_code, item.asset_book_type_code),
1063: satx.set_of_books_id
1064: FROM ap_invoice_distributions_all item,
1065: ap_self_assessed_tax_dist satx
1066: WHERE satx.accounting_date <= P_acctg_date
1067: AND satx.assets_addition_flag = 'U'
1068: AND item.assets_tracking_flag = 'Y'
1319: APIL.warranty_number,
1320: APIDG.line_type_lookup_code,
1321: POD.po_distribution_id,
1322: 'NEW'
1323: FROM ap_invoice_distributions_gt APIDG,
1324: ap_invoice_lines_all APIL,
1325: ap_invoices_all API,
1326: AP_PAYMENT_HIST_DISTS APHD, --Bug9967535
1327: financials_system_params_all FSP, -- changed table order # 9669334
1394: distributions gt table. by stamping these to N will
1395: avoid from picking up again while loading distributions gt
1396: */
1397: /* Modified the query for performance bug 8729684: start */
1398: UPDATE ap_invoice_distributions_all AID
1399: SET AID.assets_addition_flag = 'N'
1400: WHERE AID.invoice_distribution_id IN
1401: (SELECT APIDG.invoice_distribution_id
1402: FROM ap_invoice_distributions_gt APIDG
1398: UPDATE ap_invoice_distributions_all AID
1399: SET AID.assets_addition_flag = 'N'
1400: WHERE AID.invoice_distribution_id IN
1401: (SELECT APIDG.invoice_distribution_id
1402: FROM ap_invoice_distributions_gt APIDG
1403: where charge_applicable_to_dist_id is null
1404: AND NOT EXISTS
1405: (SELECT 1
1406: FROM fa_mass_additions_gt FAGT
1410: /* Modified the query for performance bug 8729684: end */
1411: -- bug 8690407: add start
1412: /* Modified the query for performance bug8983726 */
1413:
1414: UPDATE /*+ index(AID AP_INVOICE_DISTRIBUTIONS_U2) */
1415: AP_INVOICE_DISTRIBUTIONS_ALL AID
1416: SET AID.ASSETS_ADDITION_FLAG = 'N'
1417: WHERE
1418: AID.INVOICE_DISTRIBUTION_ID IN
1411: -- bug 8690407: add start
1412: /* Modified the query for performance bug8983726 */
1413:
1414: UPDATE /*+ index(AID AP_INVOICE_DISTRIBUTIONS_U2) */
1415: AP_INVOICE_DISTRIBUTIONS_ALL AID
1416: SET AID.ASSETS_ADDITION_FLAG = 'N'
1417: WHERE
1418: AID.INVOICE_DISTRIBUTION_ID IN
1419: ( SELECT APIDG.INVOICE_DISTRIBUTION_ID
1416: SET AID.ASSETS_ADDITION_FLAG = 'N'
1417: WHERE
1418: AID.INVOICE_DISTRIBUTION_ID IN
1419: ( SELECT APIDG.INVOICE_DISTRIBUTION_ID
1420: FROM AP_INVOICE_DISTRIBUTIONS_GT APIDG
1421: WHERE
1422: CHARGE_APPLICABLE_TO_DIST_ID IS NOT NULL
1423: AND NOT EXISTS
1424: (
1427: )
1428: )
1429: AND EXISTS
1430: (
1431: SELECT /*+ index(AP_INVOICE_DISTRIBUTIONS_ALL AP_INVOICE_DISTRIBUTIONS_U2)*/ 1 FROM AP_INVOICE_DISTRIBUTIONS_ALL
1432: WHERE INVOICE_DISTRIBUTION_ID = AID.CHARGE_APPLICABLE_TO_DIST_ID
1433: AND ASSETS_ADDITION_FLAG = 'N'
1434: );
1435:
1440: UPDATE AP_SELF_ASSESSED_TAX_DIST_ALL AID
1441: SET AID.assets_addition_flag = 'N'
1442: WHERE AID.invoice_distribution_id IN
1443: (SELECT APIDG.invoice_distribution_id
1444: FROM ap_invoice_distributions_gt APIDG)
1445: AND AID.invoice_distribution_id NOT IN
1446: (SELECT FAGT.invoice_distribution_id
1447: FROM fa_mass_additions_gt FAGT)
1448: -- bug 7215835: add end
1446: (SELECT FAGT.invoice_distribution_id
1447: FROM fa_mass_additions_gt FAGT)
1448: -- bug 7215835: add end
1449: -- bug 8690407: add start
1450: and exists (select 1 from ap_invoice_distributions_all
1451: where invoice_distribution_id = aid.charge_applicable_to_dist_id
1452: and nvl(assets_addition_flag, 'N') = 'N');
1453: -- bug 8690407: add end
1454:
1511: AND APIP.invoice_payment_id IN (
1512: SELECT + INDEX(aphd ap_payment_hist_dists_n5) -- Bug 8305129
1513: APHD.invoice_payment_id
1514: FROM ap_payment_hist_dists APHD,
1515: ap_invoice_distributions_all APID
1516: WHERE APIP.invoice_payment_id = APHD.invoice_payment_id
1517: AND APIP.ACCOUNTING_EVENT_ID=APHD.ACCOUNTING_EVENT_ID --bug5461146
1518: AND APHD.invoice_distribution_id = APID.invoice_distribution_id
1519: AND APHD.pay_dist_lookup_code = 'DISCOUNT'
1524: AND ( (APID.line_type_lookup_code IN ('ITEM', 'ACCRUAL')
1525: AND APID.assets_tracking_flag = 'Y')
1526: OR EXISTS -- Bug 8305129 : Replaced 2 EXISTS clause with 1
1527: ( SELECT 'X'
1528: FROM ap_invoice_distributions_all APIDV
1529: WHERE NVL(APID.related_id,APID.charge_applicable_to_dist_id) =
1530: APIDV.invoice_distribution_id
1531: AND APIDV.invoice_distribution_id <> NVL(APIDV.related_id, -1)
1532: AND APIDV.assets_tracking_flag = 'Y'
1780: 'DISCOUNT',
1781: POD.po_distribution_id,
1782: 'NEW',
1783: APIP.invoice_payment_id
1784: FROM ap_invoice_distributions_all APID,
1785: ap_invoice_lines_all APIL,
1786: ap_invoice_payments_all APIP,
1787: ap_payment_hist_dists APHD,
1788: ap_invoices_all API,
1818: AND ( (APID.line_type_lookup_code IN ('ITEM', 'ACCRUAL')
1819: AND APID.assets_tracking_flag = 'Y')
1820: /*OR EXISTS --Bug 12703009: commented below two exists
1821: ( SELECT 'X'
1822: FROM ap_invoice_distributions_all APIDV
1823: WHERE APID.related_id =
1824: APIDV.invoice_distribution_id
1825: AND APID.invoice_distribution_id <> APID.related_id --bug6415366
1826: AND APIDV.assets_tracking_flag = 'Y')
1825: AND APID.invoice_distribution_id <> APID.related_id --bug6415366
1826: AND APIDV.assets_tracking_flag = 'Y')
1827: OR EXISTS
1828: ( SELECT 'X'
1829: FROM ap_invoice_distributions_all APIDC
1830: WHERE APID.charge_applicable_to_dist_id =
1831: APIDC.invoice_distribution_id
1832: AND APIDC.assets_tracking_flag = 'Y')*/
1833: OR EXISTS --Bug 12703009 : clubbed two exists into one as per Bug 8305129 and also added condition for corrected invoices as per bug 9001504
1831: APIDC.invoice_distribution_id
1832: AND APIDC.assets_tracking_flag = 'Y')*/
1833: OR EXISTS --Bug 12703009 : clubbed two exists into one as per Bug 8305129 and also added condition for corrected invoices as per bug 9001504
1834: ( SELECT /*+ push_subq no_unnest */ 'X'
1835: FROM ap_invoice_distributions_all APIDV
1836: WHERE COALESCE(APID.charge_applicable_to_dist_id, apid.corrected_invoice_dist_id, APID.related_id) =
1837: APIDV.invoice_distribution_id -- Bug 12660674. Changed order of columns.
1838: AND APIDV.invoice_distribution_id <> NVL(APIDV.related_id, -1)
1839: AND APIDV.assets_tracking_flag = 'Y'
2048: FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,
2049: 'Updating AID records to N which EIB sends to FA ');
2050: END IF;
2051: --
2052: UPDATE /*+ INDEX(apid ap_invoice_distributions_n6) */ -- Bug 8305129
2053: ap_invoice_distributions_all APID
2054: SET APID.assets_addition_flag = 'N',
2055: APID.program_update_date = SYSDATE,
2056: APID.program_application_id = FND_GLOBAL.prog_appl_id,
2049: 'Updating AID records to N which EIB sends to FA ');
2050: END IF;
2051: --
2052: UPDATE /*+ INDEX(apid ap_invoice_distributions_n6) */ -- Bug 8305129
2053: ap_invoice_distributions_all APID
2054: SET APID.assets_addition_flag = 'N',
2055: APID.program_update_date = SYSDATE,
2056: APID.program_application_id = FND_GLOBAL.prog_appl_id,
2057: APID.program_id = FND_GLOBAL.conc_program_id,
2317: Select invoice_id
2318: into L_debug_inv_id
2319: from
2320: (Select distinct aid.invoice_id "INVOICE_ID"
2321: from ap_invoice_distributions_all aid
2322: Where aid.invoice_distribution_id = i.invoice_distribution_id
2323: UNION
2324: Select distinct astx.invoice_id "INVOICE_ID"
2325: from ap_self_assessed_tax_dist_all astx
2340:
2341: END IF;
2342: /*10368924 end*/
2343:
2344: MERGE INTO ap_invoice_distributions_all apid
2345: USING fa_mass_additions_gt fmag
2346: ON (apid.invoice_distribution_id = fmag.invoice_distribution_id
2347: AND fmag.line_status = 'PROCESSED'
2348: AND fmag.ledger_id = l_ledger_id
2380: IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
2381: FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,l_debug_info);
2382: END IF;
2383: --
2384: UPDATE ap_invoice_distributions_all APID
2385: SET APID.assets_addition_flag = 'N',
2386: APID.program_update_date = SYSDATE,
2387: APID.program_application_id = FND_GLOBAL.prog_appl_id,
2388: APID.program_id = FND_GLOBAL.conc_program_id,
2428: AND APIP.set_of_books_id = l_ledger_id
2429: AND APIP.invoice_payment_id IN (
2430: SELECT APHD.invoice_payment_id
2431: FROM ap_payment_hist_dists APHD,
2432: ap_invoice_distributions_all APID,
2433: fa_mass_additions_gt FMAG
2434: WHERE APIP.invoice_payment_id = APHD.invoice_payment_id
2435: AND APHD.invoice_distribution_id =
2436: APID.invoice_distribution_id
2456: AND APIP.set_of_books_id = l_ledger_id
2457: AND APIP.invoice_payment_id IN (
2458: SELECT APHD.invoice_payment_id
2459: FROM ap_payment_hist_dists APHD,
2460: ap_invoice_distributions_all APID,
2461: fa_mass_additions_gt FMAG
2462: WHERE APIP.invoice_payment_id = APHD.invoice_payment_id
2463: AND APHD.invoice_distribution_id =
2464: APID.invoice_distribution_id