[Home] [Help]
1869: AND dist.prepay_distribution_id is not null
1870: AND trunc(dist.Accounting_Date) <= trunc(nvl(G_GL_DATE,dist.Accounting_Date))
1871: AND trunc(dist.expenditure_item_date) <= trunc(nvl(G_TRANSACTION_DATE,dist.expenditure_item_date))
1872: AND exists (SELECT NULL
1873: FROM AP_INVOICES_ALL inv,
1874: PO_VENDORS vend
1875: WHERE inv.invoice_id = DIST.invoice_id
1876: AND inv.vendor_id = vend.vendor_id
1877: AND ((inv.invoice_type_lookup_code = G_INVOICE_TYPE
1881: (inv.invoice_type_lookup_code in ('STANDARD','CREDIT','MIXED') /*Bug# 3373933*/ /*Bug 4099522*/
1882: AND inv.source in ('CREDIT CARD','Both Pay')
1883: AND nvl(inv.PAID_ON_BEHALF_EMPLOYEE_ID,0) > 0)))
1884: AND exists(SELECT inv.invoice_id
1885: FROM AP_INVOICES_ALL inv,
1886: AP_Invoice_Distributions_all aid
1887: WHERE aid.invoice_id = inv.invoice_id
1888: AND inv.invoice_type_lookup_code = 'PREPAYMENT'
1889: AND aid.historical_flag = 'Y'
1912: ('S', 'A', 'B', 'C', 'D', 'I', 'N', 'J', 'K', 'M', 'P','Q', 'V', 'X', 'W')
1913: AND DIST.project_id > 0
1914: AND dist.line_type_lookup_code <> 'REC_TAX' -- do not proces recoverable tax
1915: AND exists (SELECT invoice_id
1916: FROM AP_INVOICES_ALL inv,
1917: PO_VENDORS vend
1918: WHERE inv.invoice_id = DIST.invoice_id
1919: AND inv.vendor_id = vend.vendor_id
1920: AND ((inv.invoice_type_lookup_code = G_INVOICE_TYPE
1980: AND dist.line_type_lookup_code <> 'REC_TAX' -- do not proces recoverable tax
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'
2001: )
2002: OR
2003: EXISTS (
2004: SELECT NULL
2005: FROM ap_invoices_all inv1,
2006: ap_invoice_distributions_all dist3
2007: WHERE inv1.invoice_id = dist3.invoice_id
2008: AND inv1.invoice_id = dist.invoice_id
2009: AND inv1.invoice_type_lookup_code <> 'EXPENSE REPORT'
2139: AND DIST.project_id = G_PROJECT_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'
2220: AND dist.line_type_lookup_code <> 'REC_TAX' -- do not proces recoverable tax
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'
2313: AND inv.PAID_ON_BEHALF_EMPLOYEE_ID IS NULL
2314: AND nvl(INV.source, 'xx' ) NOT IN ('Oracle Project Accounting',
2315: 'PA_IC_INVOICES','PA_COST_ADJUSTMENTS') )
2316: AND exists(SELECT inv.invoice_id
2317: FROM AP_INVOICES_all inv,
2318: AP_Invoice_Distributions_all aid
2319: WHERE aid.invoice_id = inv.invoice_id
2320: AND inv.invoice_type_lookup_code = 'PREPAYMENT'
2321: AND aid.historical_flag = 'Y'
2377: trunc(NVL(G_TRANSACTION_DATE, DIST.Expenditure_Item_Date)) /*GSCC*/
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'
4185: FROM ap_payment_history_all hist
4186: WHERE hist.payment_history_id = dist.payment_history_id
4187: AND hist.posted_flag = 'Y')
4188: AND exists(SELECT /*+ no_unnest */ inv.invoice_id
4189: FROM AP_INVOICES_ALL inv,
4190: AP_Invoice_Distributions_all aid,
4191: ap_invoice_payments_all aip
4192: WHERE inv.invoice_id = aip.invoice_id
4193: AND aid.invoice_id = inv.invoice_id
4451: FROM ap_payment_history_all hist
4452: WHERE hist.payment_history_id = dist.payment_history_id
4453: AND hist.posted_flag = 'Y')
4454: AND exists(SELECT /*+ no_unnest */ inv.invoice_id
4455: FROM AP_INVOICES_ALL inv,
4456: AP_Invoice_Distributions_all aid,
4457: ap_invoice_payments_all aip
4458: WHERE inv.invoice_id = aip.invoice_id
4459: AND aid.invoice_id = inv.invoice_id
4489: FROM ap_payment_history_all hist
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
4547: FROM ap_payment_history_all hist
4548: WHERE hist.payment_history_id = dist.payment_history_id
4549: AND hist.posted_flag = 'Y')
4550: AND exists(SELECT /*+ no_unnest */ inv.invoice_id
4551: FROM AP_INVOICES_ALL inv,
4552: AP_Invoice_Distributions_all aid,
4553: ap_invoice_payments_all aip
4554: WHERE inv.invoice_id = aip.invoice_id
4555: AND aid.invoice_id = inv.invoice_id
4581: FROM ap_payment_history_all hist
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
8949: program_update_date=SYSDATE
8950: WHERE nvl(dist.pa_addition_flag,'N') = 'N'
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
8999: FROM ap_payment_history_all hist
9000: WHERE hist.payment_history_id = dist.payment_history_id
9001: AND hist.posted_flag = 'Y')
9002: AND exists (SELECT inv.invoice_id
9003: FROM AP_INVOICES_ALL inv,
9004: PO_VENDORS vend,
9005: AP_Invoice_Distributions_all aid,
9006: ap_invoice_payments_all aip
9007: WHERE inv.vendor_id = vend.vendor_id
9040: program_update_date=SYSDATE
9041: WHERE nvl(dist.pa_addition_flag,'N') = 'N'
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
9104: FROM ap_payment_history_all hist
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
9151: program_update_date=SYSDATE
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
9206: FROM ap_payment_history_all hist
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
9253: program_update_date=SYSDATE
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