[Home] [Help]
1981: AND (
1982: EXISTS (
1983: SELECT NULL
1984: FROM ap_invoices_all inv,
1985: po_distributions_all PO,
1986: ap_invoice_distributions_all dist2
1987: WHERE inv.invoice_id = dist2.invoice_id
1988: AND nvl(po.distribution_type,'XXX') <> 'PREPAYMENT'
1989: AND dist2.invoice_id = DIST.invoice_id
2014: /* Bug 6353803: Added the following for this bug. */
2015: AND (pa_nl_installed.is_nl_installed = 'N'
2016: OR ( pa_nl_installed.is_nl_installed = 'Y'
2017: AND NOT EXISTS (SELECT 'X'
2018: FROM po_distributions_all pod, mtl_system_items si, po_lines_all pol
2019: WHERE pod.po_distribution_id = dist.po_distribution_id
2020: AND pod.po_line_id = pol.po_line_id
2021: AND si.inventory_item_id = pol.item_id
2022: AND si.comms_nl_trackable_flag = 'Y'
2024: )
2025: AND NOT EXISTS (SELECT 'X'
2026: FROM
2027: ap_invoice_distributions apdist,
2028: po_distributions pod,
2029: mtl_system_items si,
2030: po_lines_all pol
2031: where DIST.CHARGE_APPLICABLE_TO_DIST_ID
2032: = apdist.INVOICE_DISTRIBUTION_ID
2087: /* Bug 6353803: Added the following for this bug. */
2088: AND (pa_nl_installed.is_nl_installed = 'N'
2089: OR ( pa_nl_installed.is_nl_installed = 'Y'
2090: AND NOT EXISTS (SELECT 'X'
2091: FROM po_distributions_all pod, mtl_system_items si, po_lines_all pol
2092: WHERE pod.po_distribution_id = dist.po_distribution_id
2093: AND pod.po_line_id = pol.po_line_id
2094: AND si.inventory_item_id = pol.item_id
2095: AND si.comms_nl_trackable_flag = 'Y'
2097: )
2098: AND NOT EXISTS (SELECT 'X'
2099: FROM
2100: ap_invoice_distributions apdist,
2101: po_distributions pod,
2102: mtl_system_items si,
2103: po_lines_all pol
2104: where DIST.CHARGE_APPLICABLE_TO_DIST_ID
2105: = apdist.INVOICE_DISTRIBUTION_ID
2140: AND dist.line_type_lookup_code <> 'REC_TAX' -- do not proces recoverable tax
2141: AND EXISTS (
2142: SELECT NULL
2143: FROM ap_invoices_all inv,
2144: po_distributions_all PO,
2145: ap_invoice_distributions_all dist2
2146: WHERE inv.invoice_id = dist2.invoice_id
2147: AND nvl(po.distribution_type,'XXX') <> 'PREPAYMENT'
2148: AND dist2.invoice_id = DIST.invoice_id
2161: /* Bug 6353803: Added the following for this bug. */
2162: AND (pa_nl_installed.is_nl_installed = 'N'
2163: OR ( pa_nl_installed.is_nl_installed = 'Y'
2164: AND NOT EXISTS (SELECT 'X'
2165: FROM po_distributions_all pod, mtl_system_items si, po_lines_all pol
2166: WHERE pod.po_distribution_id = dist.po_distribution_id
2167: AND pod.po_line_id = pol.po_line_id
2168: AND si.inventory_item_id = pol.item_id
2169: AND si.comms_nl_trackable_flag = 'Y'
2171: )
2172: AND NOT EXISTS (SELECT 'X'
2173: FROM
2174: ap_invoice_distributions apdist,
2175: po_distributions pod,
2176: mtl_system_items si,
2177: po_lines_all pol
2178: where DIST.CHARGE_APPLICABLE_TO_DIST_ID
2179: = apdist.INVOICE_DISTRIBUTION_ID
2221: AND (
2222: EXISTS (
2223: SELECT NULL
2224: FROM ap_invoices_all inv,
2225: po_distributions_all PO,
2226: ap_invoice_distributions_all dist2
2227: WHERE inv.invoice_id = dist2.invoice_id
2228: AND nvl(po.distribution_type,'XXX') <> 'PREPAYMENT'
2229: AND dist2.invoice_id = DIST.invoice_id
2253: /* Bug 6353803: Added the following for this bug. */
2254: AND (pa_nl_installed.is_nl_installed = 'N'
2255: OR ( pa_nl_installed.is_nl_installed = 'Y'
2256: AND NOT EXISTS (SELECT 'X'
2257: FROM po_distributions_all pod, mtl_system_items si, po_lines_all pol
2258: WHERE pod.po_distribution_id = dist.po_distribution_id
2259: AND pod.po_line_id = pol.po_line_id
2260: AND si.inventory_item_id = pol.item_id
2261: AND si.comms_nl_trackable_flag = 'Y'
2263: )
2264: AND NOT EXISTS (SELECT 'X'
2265: FROM
2266: ap_invoice_distributions apdist,
2267: po_distributions pod,
2268: mtl_system_items si,
2269: po_lines_all pol
2270: where DIST.CHARGE_APPLICABLE_TO_DIST_ID
2271: = apdist.INVOICE_DISTRIBUTION_ID
2325: /* Bug 6353803: Added the following for this bug. */
2326: AND (pa_nl_installed.is_nl_installed = 'N'
2327: OR ( pa_nl_installed.is_nl_installed = 'Y'
2328: AND NOT EXISTS (SELECT 'X'
2329: FROM po_distributions_all pod, mtl_system_items si, po_lines_all pol
2330: WHERE pod.po_distribution_id = dist.po_distribution_id
2331: AND pod.po_line_id = pol.po_line_id
2332: AND si.inventory_item_id = pol.item_id
2333: AND si.comms_nl_trackable_flag = 'Y'
2335: )
2336: AND NOT EXISTS (SELECT 'X'
2337: FROM
2338: ap_invoice_distributions apdist,
2339: po_distributions pod,
2340: mtl_system_items si,
2341: po_lines_all pol
2342: where DIST.CHARGE_APPLICABLE_TO_DIST_ID
2343: = apdist.INVOICE_DISTRIBUTION_ID
2378: AND dist.line_type_lookup_code <> 'REC_TAX' -- do not proces recoverable tax
2379: AND EXISTS (
2380: SELECT NULL
2381: FROM ap_invoices_all inv,
2382: po_distributions_all PO,
2383: ap_invoice_distributions_all dist2
2384: WHERE inv.invoice_id = dist2.invoice_id
2385: AND nvl(po.distribution_type,'XXX') <> 'PREPAYMENT'
2386: AND dist2.invoice_id = DIST.invoice_id
2400: /* Bug 6353803: Added the following for this bug. */
2401: AND (pa_nl_installed.is_nl_installed = 'N'
2402: OR ( pa_nl_installed.is_nl_installed = 'Y'
2403: AND NOT EXISTS (SELECT 'X'
2404: FROM po_distributions_all pod, mtl_system_items si, po_lines_all pol
2405: WHERE pod.po_distribution_id = dist.po_distribution_id
2406: AND pod.po_line_id = pol.po_line_id
2407: AND si.inventory_item_id = pol.item_id
2408: AND si.comms_nl_trackable_flag = 'Y'
2410: )
2411: AND NOT EXISTS (SELECT 'X'
2412: FROM
2413: ap_invoice_distributions apdist,
2414: po_distributions pod,
2415: mtl_system_items si,
2416: po_lines_all pol
2417: where DIST.CHARGE_APPLICABLE_TO_DIST_ID
2418: = apdist.INVOICE_DISTRIBUTION_ID
4279: UPDATE rcv_receiving_sub_ledger rcv_sub
4280: SET rcv_sub.pa_addition_flag = 'L'
4281: WHERE rcv_sub.pa_addition_flag = 'N'
4282: and reference3 = TO_CHAR(p_po_distribution_id) and exists (
4283: select 1 from po_distributions_all pod
4284: where po_distribution_id = TO_NUMBER(rcv_sub.reference3)
4285: and po_distribution_id = p_po_distribution_id
4286: and code_combination_id = rcv_sub.code_combination_id
4287: and accrue_on_receipt_flag = 'Y');
4490: WHERE hist.payment_history_id = dist.payment_history_id
4491: AND hist.posted_flag = 'Y')
4492: AND exists(SELECT/*+ no_unnest */ inv.invoice_id
4493: FROM AP_INVOICES_ALL inv,
4494: PO_Distributions_all PO,
4495: AP_Invoice_Distributions_all aid,
4496: ap_invoice_payments_all aip
4497: WHERE inv.invoice_id = aip.invoice_id
4498: AND aid.invoice_id = inv.invoice_id
4582: WHERE hist.payment_history_id = dist.payment_history_id
4583: AND hist.posted_flag = 'Y')
4584: AND exists(SELECT /*+ no_unnest */ inv.invoice_id
4585: FROM AP_INVOICES_ALL inv,
4586: PO_Distributions_all PO,
4587: AP_Invoice_Distributions_all aid,
4588: ap_invoice_payments_all aip
4589: WHERE inv.invoice_id = aip.invoice_id
4590: AND aid.invoice_id = inv.invoice_id
5576: rcv_sub.program_update_date = SYSDATE
5577: WHERE exists
5578: (SELECT 1 --rcv_sub1.ROWID --Removed /*+ leading(po_dist) */ for Bug5262594
5579: FROM Rcv_Transactions rcv_txn,
5580: PO_Distributions po_dist
5581: WHERE ((rcv_txn.destination_type_code ='EXPENSE' )
5582: OR (rcv_txn.destination_type_code = 'RECEIVING'
5583: AND (rcv_txn.transaction_type IN ('RETURN TO VENDOR','RETURN TO RECEIVING'))))
5584: AND trunc(rcv_txn.transaction_date) <= trunc(nvl(G_GL_DATE,rcv_txn.transaction_date)) /*Added trunc for the bug 6623163 */
5624: rcv_sub.program_update_date = SYSDATE
5625: WHERE exists
5626: (SELECT 1 --rcv_sub1.ROWID --Removed /*+ leading(po_dist) */ for Bug5262594
5627: FROM Rcv_Transactions rcv_txn,
5628: PO_Distributions po_dist
5629: WHERE ((rcv_txn.destination_type_code ='EXPENSE' )
5630: OR (rcv_txn.destination_type_code = 'RECEIVING'
5631: AND (rcv_txn.transaction_type IN ('RETURN TO VENDOR','RETURN TO RECEIVING'))))
5632: AND trunc(rcv_txn.transaction_date) <= trunc(nvl(G_GL_DATE,rcv_txn.transaction_date)) /*Added trunc for the bug 6623163 */
6173:
6174: /* Bug 5051103 - replace expnediture_item_date with l_ei_date_tbl(i) */
6175: SELECT pa_utils.getweekending(MAX(l_ei_date_tbl(i)))
6176: INTO G_EXPENDITURE_ENDING_DATE
6177: FROM po_distributions
6178: WHERE po_distribution_id = l_po_dist_id_tbl(i);
6179:
6180: END IF;
6181:
7487: OR ( pa_nl_installed.is_nl_installed = 'Y' --EIB trackable items
7488: AND EXISTS (SELECT 'X'
7489: FROM mtl_system_items si,
7490: po_lines_all pol,
7491: po_distributions_all po_dist1
7492: WHERE po_dist1.po_line_id = pol.po_line_id
7493: AND po_dist1.po_distribution_id = dist.po_distribution_id
7494: AND si.inventory_item_id = pol.item_id
7495: AND po_dist1.project_id IS NOT NULL
7516: AND rcv_sub.pa_addition_flag || '' IN ('Y','I')
7517: AND rcv_sub.rcv_transaction_id = rcv_txn.transaction_id
7518: )
7519: OR EXISTS ( SELECT PO.po_distribution_id
7520: FROM po_distributions PO
7521: WHERE PO.po_distribution_id = dist.po_distribution_id
7522: AND nvl(po.distribution_type,'XXX') <> 'PREPAYMENT' -- bug 7192304, added nvl
7523: AND PO.project_id > 0
7524: AND NVL(PO.destination_type_code, 'EXPENSE') = 'EXPENSE'
7557: program_update_date=SYSDATE
7558: WHERE rowid in (
7559: SELECT dist.rowid
7560: FROM ap_invoices inv,
7561: po_distributions po,
7562: ap_invoice_distributions_all dist
7563: WHERE inv.invoice_id = dist.invoice_id
7564: AND po.po_distribution_id = dist.po_distribution_id
7565: AND nvl(po.distribution_type,'XXX') <> 'PREPAYMENT' -- bug 7192304, added nvl
7617: OR ( pa_nl_installed.is_nl_installed = 'Y' --EIB trackable items
7618: AND EXISTS (SELECT 'X'
7619: FROM mtl_system_items si,
7620: po_lines_all pol,
7621: po_distributions_all po_dist1
7622: WHERE po_dist1.po_line_id = pol.po_line_id
7623: AND po_dist1.po_distribution_id = dist.po_distribution_id
7624: AND si.inventory_item_id = pol.item_id
7625: AND po_dist1.project_id IS NOT NULL
7645: AND rcv_sub.pa_addition_flag || '' IN ('Y','I')
7646: AND rcv_sub.rcv_transaction_id = rcv_txn.transaction_id
7647: )
7648: OR EXISTS ( SELECT PO.po_distribution_id
7649: FROM po_distributions PO
7650: WHERE PO.po_distribution_id = dist.po_distribution_id
7651: AND nvl(po.distribution_type,'XXX') <> 'PREPAYMENT' -- bug 7192304, added nvl
7652: AND PO.project_id > 0
7653: AND NVL(PO.destination_type_code, 'EXPENSE') = 'EXPENSE'
7686: program_update_date=SYSDATE
7687: WHERE rowid in (
7688: SELECT dist.rowid
7689: FROM ap_invoices inv,
7690: po_distributions po,
7691: ap_invoice_distributions_all dist
7692: WHERE inv.invoice_id = dist.invoice_id
7693: AND po.po_distribution_id = dist.po_distribution_id
7694: AND NVL(po.destination_type_code, 'EXPENSE') = 'EXPENSE'
8951: AND dist.amount <>0
8952: AND exists(SELECT /*+ no_unnest */ inv.invoice_id
8953: FROM AP_INVOICES_ALL inv,
8954: PO_VENDORS vend,
8955: PO_Distributions_all PO,
8956: AP_Invoice_Distributions_all aid, --STD INV DIST LINE
8957: AP_Invoice_Distributions_all aid2 -- PREPAY APPL DIST LINE
8958: WHERE aid.invoice_id = inv.invoice_id
8959: AND inv.vendor_id = vend.vendor_id
9042: AND dist.amount <>0
9043: AND exists(SELECT /*+ no_unnest */ inv.invoice_id
9044: FROM AP_INVOICES_ALL inv,
9045: PO_VENDORS vend,
9046: PO_Distributions_all PO,
9047: AP_Invoice_Distributions_all aid, --STD INV DIST LINE
9048: AP_Invoice_Distributions_all aid2 -- PREPAY APPL DIST LINE
9049: WHERE aid.invoice_id = inv.invoice_id
9050: AND inv.vendor_id = vend.vendor_id
9105: WHERE hist.payment_history_id = dist.payment_history_id
9106: AND hist.posted_flag = 'Y')
9107: AND exists(SELECT /*+ no_unnest */ inv.invoice_id
9108: FROM AP_INVOICES_ALL inv,
9109: PO_Distributions_all PO,
9110: AP_Invoice_Distributions_all aid,
9111: ap_invoice_payments_all aip
9112: WHERE inv.invoice_id = aip.invoice_id
9113: AND aid.invoice_id = inv.invoice_id
9152: WHERE nvl(dist.pa_addition_flag,'N') = 'N'
9153: AND dist.amount <> 0
9154: AND exists(SELECT /*+ no_unnest */ inv.invoice_id
9155: FROM AP_INVOICES_ALL inv,
9156: PO_Distributions_all PO,
9157: AP_Invoice_Distributions_all aid, --STD INV DIST LINE
9158: AP_Invoice_Distributions_all aid2 -- PREPAY APPL DIST LINE
9159: WHERE aid.invoice_id = inv.invoice_id
9160: AND aid.invoice_distribution_id = dist.invoice_distribution_id -- Std inv line
9207: WHERE hist.payment_history_id = dist.payment_history_id
9208: AND hist.posted_flag = 'Y')
9209: AND exists(SELECT /*+ no_unnest */ inv.invoice_id
9210: FROM AP_INVOICES_ALL inv,
9211: PO_Distributions_all PO,
9212: AP_Invoice_Distributions_all aid,
9213: ap_invoice_payments_all aip
9214: WHERE inv.invoice_id = aip.invoice_id
9215: AND aid.invoice_id = inv.invoice_id
9254: WHERE nvl(dist.pa_addition_flag,'N') = 'N'
9255: AND dist.amount <> 0
9256: AND exists(SELECT /*+ no_unnest */ inv.invoice_id
9257: FROM AP_INVOICES_ALL inv,
9258: PO_Distributions_all PO,
9259: AP_Invoice_Distributions_all aid, --STD INV DIST LINE
9260: AP_Invoice_Distributions_all aid2 -- PREPAY APPL DIST LINE
9261: WHERE aid.invoice_id = inv.invoice_id
9262: AND inv.org_id = G_ORG_ID