VIEW: APPS.PA_AP_INV_EXCEPTIONS_SUM_V

Source

View Text - Preformatted

SELECT INVDIST.PROJECT_ID PROJECT_ID , INVDIST.TASK_ID TASK_ID , INV.INVOICE_ID INVOICE_ID , INV.INVOICE_NUM INVOICE_NUMBER , INVDIST.INVOICE_LINE_NUMBER , INVDIST.DISTRIBUTION_LINE_NUMBER DISTRIBUTION_LINE_NUMBER , INVDIST.INVOICE_DISTRIBUTION_ID , INV.INVOICE_DATE INVOICE_DATE , INV.VENDOR_ID VENDOR_ID , INV.INVOICE_CURRENCY_CODE DENOM_CURRENCY_CODE , INVDIST.AMOUNT DENOM_AMOUNT , GLSOB.CURRENCY_CODE ACCT_CURRENCY_CODE , NVL(INVDIST.BASE_AMOUNT,INVDIST.AMOUNT) AMOUNT , DECODE (NVL(INV.INVOICE_CURRENCY_CODE,'-99'),NVL(GLSOB.CURRENCY_CODE,'-99'),TO_DATE(NULL), INVDIST.EXCHANGE_DATE) ACCT_RATE_DATE , DECODE (NVL(INV.INVOICE_CURRENCY_CODE,'-99'),NVL(GLSOB.CURRENCY_CODE,'-99'),NULL, INVDIST.EXCHANGE_RATE_TYPE) ACCT_RATE_TYPE , DECODE (NVL(INV.INVOICE_CURRENCY_CODE,'-99'),NVL(GLSOB.CURRENCY_CODE,'-99'),TO_NUMBER(NULL), INVDIST.EXCHANGE_RATE) ACCT_EXCHANGE_RATE , INVDIST.PA_ADDITION_FLAG EXCEPTION_CODE , INVDIST.EXPENDITURE_ORGANIZATION_ID EXPENDITURE_ORGANIZATION_ID , INVDIST.EXPENDITURE_TYPE EXPENDITURE_TYPE , INVDIST.EXPENDITURE_ITEM_DATE EXPENDITURE_ITEM_DATE , INVDIST.PA_QUANTITY QUANTITY , INVDIST.ACCOUNTING_DATE GL_DATE , pa_utils2.pa_date_profile(invdist.expenditure_item_date,invdist.accounting_date,invdist.org_id) PA_DATE , pa_utils2.pa_period_name_profile() PERIOD_NAME , INVDIST.PERIOD_NAME GL_PERIOD_NAME , DECODE(INVDIST.POSTED_FLAG,'Y','*',NULL) POSTED_FLAG , PA_EXCEPTION_REASONS_PUB.GET_EXCEPTION_TEXT('AP_INV_EXCP', DECODE(INVDIST.PA_ADDITION_FLAG, 'N',DECODE(INV.INVOICE_TYPE_LOOKUP_CODE, 'EXPENSE REPORT', 'ERN', INVDIST.PA_ADDITION_FLAG),INVDIST.PA_ADDITION_FLAG),NULL,'R') EXCEPTION_REASON , PA_EXCEPTION_REASONS_PUB.GET_EXCEPTION_TEXT('AP_INV_EXCP', DECODE(INVDIST.PA_ADDITION_FLAG, 'N',DECODE(INV.INVOICE_TYPE_LOOKUP_CODE, 'EXPENSE REPORT', 'ERN', INVDIST.PA_ADDITION_FLAG),INVDIST.PA_ADDITION_FLAG),NULL,'A') CORRECTIVE_ACTION , INVDIST.ORG_ID , IMP.SAME_PA_GL_PERIOD , PA_EXPENDITURES_UTILS.getorgtlname(INVDIST.ORG_ID) , imp.set_of_books_id , VEND.SEGMENT1 VENDOR_NUMBER , VEND.VENDOR_NAME VENDOR_NAME , NULL payment_num , NULL check_number FROM GL_LEDGERS GLSOB, PO_VENDORS VEND, AP_INVOICE_DISTRIBUTIONS_ALL INVDIST, AP_INVOICE_LINES_ALL INVLINE, AP_INVOICES_ALL INV, PA_IMPLEMENTATIONS_ALL IMP WHERE VEND.VENDOR_ID = INV.VENDOR_ID AND INV.INVOICE_ID = INVDIST.INVOICE_ID AND INV.INVOICE_ID = INVLINE.INVOICE_ID AND INVLINE.LINE_NUMBER = INVDIST.INVOICE_LINE_NUMBER AND DECODE(INV.INVOICE_TYPE_LOOKUP_CODE , 'EXPENSE REPORT' , VEND.EMPLOYEE_ID , -99) IS NOT NULL AND INVDIST.PROJECT_ID > 0 AND INVDIST.PA_ADDITION_FLAG IN ( 'A','B','C','D','I','J','K','M','N','P','Q','S','V','X','O') AND INVDIST.POSTED_FLAG = 'Y' AND nvl(glsob.sla_ledger_cash_basis_flag,'N') = 'N' AND INV.SOURCE <>'Oracle Project Accounting' AND GLSOB.LEDGER_ID = INVDIST.SET_OF_BOOKS_ID AND INVDIST.ORG_ID =IMP.ORG_ID AND NVL(invdist.tax_recoverable_flag,'N') ='N' AND(((inv.invoice_type_lookup_code = 'EXPENSE REPORT' AND inv.source IN ('XpenseXpress','Manual Invoice Entry','SelfService') AND (vend.employee_id IS NOT NULL or nvl(inv.paid_on_behalf_employee_id,0) > 0)) OR (inv.invoice_type_lookup_code in ('STANDARD','CREDIT','MIXED') AND inv.source in ('CREDIT CARD','Both Pay') AND nvl(inv.PAID_ON_BEHALF_EMPLOYEE_ID,0) > 0 ) ) OR EXISTS ( SELECT 1 from po_distributions PO , AP_INVOICE_DISTRIBUTIONS_ALL APD WHERE inv.PAID_ON_BEHALF_EMPLOYEE_ID IS NULL AND APD.invoice_distribution_id = invdist.invoice_distribution_id AND APD.po_distribution_id = PO.po_distribution_id(+) AND NVL(PO.destination_type_code, 'EXPENSE') = 'EXPENSE' AND inv.invoice_type_lookup_code NOT IN ('PREPAYMENT', 'EXPENSE REPORT') AND INVDIST.line_type_lookup_code <> 'PREPAY' AND (nvl(INV.source, 'xx' ) NOT IN ('Oracle Project Accounting','PA_IC_INVOICES','PA_COST_ADJUSTMENTS') OR invdist.line_type_lookup_code = 'TAX')) ) AND (pa_nl_installed.is_nl_installed = 'N' OR ( pa_nl_installed.is_nl_installed = 'Y' AND NOT EXISTS (SELECT 'X' FROM po_distributions_all pod,mtl_system_items si, po_lines_all pol WHERE pod.po_distribution_id = INVDIST.po_distribution_id AND pod.po_line_id = pol.po_line_id AND si.inventory_item_id = pol.item_id AND si.comms_nl_trackable_flag = 'Y' AND (si.organization_id = pod.org_id OR si.organization_id IN (SELECT NVL(ship_to_organization_id,-9999) FROM po_line_locations_all pll WHERE pll.po_header_id = pod.po_header_id AND pll.po_line_id = pod.po_line_id))) AND NOT EXISTS (SELECT 'X' FROM ap_invoice_distributions_all apdist, po_distributions_all pod, mtl_system_items si, po_lines_all pol WHERE INVDIST.CHARGE_APPLICABLE_TO_DIST_ID = apdist.INVOICE_DISTRIBUTION_ID AND apdist.po_distribution_id = pod.po_distribution_id AND pod.po_line_id = pol.po_line_id AND si.inventory_item_id = pol.item_id AND si.comms_nl_trackable_flag = 'Y' AND (si.organization_id = pod.org_id OR si.organization_id IN (SELECT NVL(ship_to_organization_id,-9999) FROM po_line_locations_all pll WHERE pll.po_header_id = pod.po_header_id AND pll.po_line_id = pod.po_line_id))))) UNION ALL SELECT INVDIST.PROJECT_ID PROJECT_ID , INVDIST.TASK_ID TASK_ID , INV.INVOICE_ID INVOICE_ID , INV.INVOICE_NUM INVOICE_NUMBER , INVDIST.INVOICE_LINE_NUMBER , INVDIST.DISTRIBUTION_LINE_NUMBER DISTRIBUTION_LINE_NUMBER , INVDIST.INVOICE_DISTRIBUTION_ID , INV.INVOICE_DATE INVOICE_DATE , INV.VENDOR_ID VENDOR_ID , INV.INVOICE_CURRENCY_CODE DENOM_CURRENCY_CODE , DECODE(PAYDIST.PAY_DIST_LOOKUP_CODE, 'CASH', INVPMT.AMOUNT,'DISCOUNT' , INVPMT.DISCOUNT_TAKEN) DENOM_AMOUNT , GLSOB.CURRENCY_CODE ACCT_CURRENCY_CODE , NVL(INVPMT.PAYMENT_BASE_AMOUNT,INVPMT.AMOUNT) AMOUNT , DECODE (NVL(INV.INVOICE_CURRENCY_CODE,'-99'),NVL(GLSOB.CURRENCY_CODE,'-99'),TO_DATE(NULL), INVDIST.EXCHANGE_DATE) ACCT_RATE_DATE , DECODE (NVL(INV.INVOICE_CURRENCY_CODE,'-99'),NVL(GLSOB.CURRENCY_CODE,'-99'),NULL, INVDIST.EXCHANGE_RATE_TYPE) ACCT_RATE_TYPE , DECODE (NVL(INV.INVOICE_CURRENCY_CODE,'-99'),NVL(GLSOB.CURRENCY_CODE,'-99'),TO_NUMBER(NULL), INVDIST.EXCHANGE_RATE) ACCT_EXCHANGE_RATE , PAYDIST.PA_ADDITION_FLAG EXCEPTION_CODE , INVDIST.EXPENDITURE_ORGANIZATION_ID EXPENDITURE_ORGANIZATION_ID , INVDIST.EXPENDITURE_TYPE EXPENDITURE_TYPE , INVDIST.EXPENDITURE_ITEM_DATE EXPENDITURE_ITEM_DATE , INVDIST.PA_QUANTITY QUANTITY , INVDIST.ACCOUNTING_DATE GL_DATE , pa_utils2.pa_date_profile(invdist.expenditure_item_date,invdist.accounting_date,invdist.org_id) PA_DATE , pa_utils2.pa_period_name_profile() PERIOD_NAME , INVDIST.PERIOD_NAME GL_PERIOD_NAME , DECODE(PAYHIST.POSTED_FLAG,'Y','*',NULL) POSTED_FLAG , PA_EXCEPTION_REASONS_PUB.GET_EXCEPTION_TEXT('AP_INV_EXCP', DECODE(PAYDIST.PA_ADDITION_FLAG, 'N',DECODE(INV.INVOICE_TYPE_LOOKUP_CODE, 'EXPENSE REPORT', 'ERN', PAYDIST.PA_ADDITION_FLAG),PAYDIST.PA_ADDITION_FLAG),NULL,'R') EXCEPTION_REASON , PA_EXCEPTION_REASONS_PUB.GET_EXCEPTION_TEXT('AP_INV_EXCP', DECODE(PAYDIST.PA_ADDITION_FLAG, 'N',DECODE(INV.INVOICE_TYPE_LOOKUP_CODE, 'EXPENSE REPORT', 'ERN', PAYDIST.PA_ADDITION_FLAG),PAYDIST.PA_ADDITION_FLAG),NULL,'A') CORRECTIVE_ACTION , INVDIST.ORG_ID , IMP.SAME_PA_GL_PERIOD , PA_EXPENDITURES_UTILS.getorgtlname(INVDIST.ORG_ID) , imp.set_of_books_id , VEND.SEGMENT1 VENDOR_NUMBER , VEND.VENDOR_NAME VENDOR_NAME , INVPMT.PAYMENT_NUM , PAYCHK.check_number FROM GL_LEDGERS GLSOB, PO_VENDORS VEND, AP_INVOICE_DISTRIBUTIONS_ALL INVDIST, AP_PAYMENT_HIST_DISTS PAYDIST, AP_PAYMENT_HISTORY_ALL PAYHIST, AP_INVOICE_PAYMENTS_ALL INVPMT, AP_CHECKS_ALL PAYCHK, AP_INVOICE_LINES_ALL INVLINE, AP_INVOICES_ALL INV, PA_IMPLEMENTATIONS_ALL IMP WHERE VEND.VENDOR_ID = INV.VENDOR_ID AND INV.INVOICE_ID = INVDIST.INVOICE_ID AND INV.INVOICE_ID = INVLINE.INVOICE_ID AND PAYDIST.INVOICE_DISTRIBUTION_ID = INVDIST.INVOICE_DISTRIBUTION_ID AND INVLINE.LINE_NUMBER = INVDIST.INVOICE_LINE_NUMBER AND DECODE(INV.INVOICE_TYPE_LOOKUP_CODE , 'EXPENSE REPORT' , VEND.EMPLOYEE_ID , -99) IS NOT NULL AND INVDIST.PROJECT_ID > 0 AND PAYDIST.PA_ADDITION_FLAG IN ( 'A','B','C','D','I','J','K','M','N','P','Q','S','V','X','O') AND PAYDIST.PAYMENT_HISTORY_ID = PAYHIST.PAYMENT_HISTORY_ID AND PAYHIST.POSTED_FLAG = 'Y' AND INVPMT.invoice_payment_id = PAYDIST.invoice_payment_id AND invpmt.check_id = paychk.check_id AND nvl(glsob.sla_ledger_cash_basis_flag,'N') in ('N','Y') AND PAYDIST.PAY_DIST_LOOKUP_CODE = decode ( nvl(glsob.sla_ledger_cash_basis_flag,'N') , 'N' , 'DISCOUNT', nvl(glsob.sla_ledger_cash_basis_flag,'N') , 'Y' , 'CASH') AND INV.SOURCE <>'Oracle Project Accounting' AND GLSOB.LEDGER_ID = INVDIST.SET_OF_BOOKS_ID AND INVDIST.ORG_ID =IMP.ORG_ID AND NVL(invdist.tax_recoverable_flag,'N') ='N' AND(((inv.invoice_type_lookup_code = 'EXPENSE REPORT' AND inv.source IN ('XpenseXpress','Manual Invoice Entry','SelfService') AND (vend.employee_id IS NOT NULL or nvl(inv.paid_on_behalf_employee_id,0) > 0)) OR (inv.invoice_type_lookup_code in ('STANDARD','CREDIT','MIXED') AND inv.source in ('CREDIT CARD','Both Pay') AND nvl(inv.PAID_ON_BEHALF_EMPLOYEE_ID,0) > 0 ) ) OR EXISTS ( SELECT 1 from po_distributions PO , AP_INVOICE_DISTRIBUTIONS_ALL APD WHERE inv.PAID_ON_BEHALF_EMPLOYEE_ID IS NULL AND APD.invoice_distribution_id = invdist.invoice_distribution_id AND APD.po_distribution_id = PO.po_distribution_id(+) AND NVL(PO.destination_type_code, 'EXPENSE') = 'EXPENSE' AND inv.invoice_type_lookup_code NOT IN ('PREPAYMENT', 'EXPENSE REPORT') AND INVDIST.line_type_lookup_code <> 'PREPAY' AND (nvl(INV.source, 'xx' ) NOT IN ('Oracle Project Accounting','PA_IC_INVOICES','PA_COST_ADJUSTMENTS') OR invdist.line_type_lookup_code = 'TAX'))) AND (pa_nl_installed.is_nl_installed = 'N' OR ( pa_nl_installed.is_nl_installed = 'Y' AND NOT EXISTS (SELECT 'X' FROM po_distributions_all pod,mtl_system_items si, po_lines_all pol WHERE pod.po_distribution_id = INVDIST.po_distribution_id AND pod.po_line_id = pol.po_line_id AND si.inventory_item_id = pol.item_id AND si.comms_nl_trackable_flag = 'Y' AND (si.organization_id = pod.org_id OR si.organization_id IN (SELECT NVL(ship_to_organization_id,-9999) FROM po_line_locations_all pll WHERE pll.po_header_id = pod.po_header_id AND pll.po_line_id = pod.po_line_id))) AND NOT EXISTS (SELECT 'X' FROM ap_invoice_distributions_all apdist, po_distributions_all pod, mtl_system_items si, po_lines_all pol WHERE INVDIST.CHARGE_APPLICABLE_TO_DIST_ID = apdist.INVOICE_DISTRIBUTION_ID AND apdist.po_distribution_id = pod.po_distribution_id AND pod.po_line_id = pol.po_line_id AND si.inventory_item_id = pol.item_id AND si.comms_nl_trackable_flag = 'Y' AND (si.organization_id = pod.org_id OR si.organization_id IN (SELECT NVL(ship_to_organization_id,-9999) FROM po_line_locations_all pll WHERE pll.po_header_id = pod.po_header_id AND pll.po_line_id = pod.po_line_id))))) UNION ALL SELECT INVDIST.PROJECT_ID PROJECT_ID , INVDIST.TASK_ID TASK_ID , INV.INVOICE_ID INVOICE_ID , INV.INVOICE_NUM INVOICE_NUMBER , INVDIST.INVOICE_LINE_NUMBER , INVDIST.DISTRIBUTION_LINE_NUMBER DISTRIBUTION_LINE_NUMBER , INVDIST.INVOICE_DISTRIBUTION_ID , INV.INVOICE_DATE INVOICE_DATE , INV.VENDOR_ID VENDOR_ID , INV.INVOICE_CURRENCY_CODE DENOM_CURRENCY_CODE , INVDIST.AMOUNT DENOM_AMOUNT , GLSOB.CURRENCY_CODE ACCT_CURRENCY_CODE , NVL(INVDIST.BASE_AMOUNT, INVDIST.AMOUNT) AMOUNT , INV.EXCHANGE_DATE ACCT_RATE_DATE , INV.EXCHANGE_RATE_TYPE ACCT_RATE_TYPE , INV.EXCHANGE_RATE ACCT_EXCHANGE_RATE , INVDIST.PA_ADDITION_FLAG EXCEPTION_CODE , INVDIST.EXPENDITURE_ORGANIZATION_ID EXPENDITURE_ORGANIZATION_ID , INVDIST.EXPENDITURE_TYPE EXPENDITURE_TYPE , INVDIST.EXPENDITURE_ITEM_DATE EXPENDITURE_ITEM_DATE , INVDIST.PA_QUANTITY QUANTITY , INVDIST.ACCOUNTING_DATE GL_DATE , PA_UTILS2.PA_DATE_PROFILE(INVDIST.EXPENDITURE_ITEM_DATE, INVDIST.ACCOUNTING_DATE, INVDIST.ORG_ID) PA_DATE , PA_UTILS2.PA_PERIOD_NAME_PROFILE() PERIOD_NAME , INVDIST.PERIOD_NAME GL_PERIOD_NAME , DECODE(INVDIST.POSTED_FLAG, 'Y', '*', NULL) POSTED_FLAG , PA_EXCEPTION_REASONS_PUB.GET_EXCEPTION_TEXT('AP_INV_EXCP', DECODE(INVDIST.PA_ADDITION_FLAG, 'N',DECODE(INV.INVOICE_TYPE_LOOKUP_CODE, 'EXPENSE REPORT', 'ERN', INVDIST.PA_ADDITION_FLAG),INVDIST.PA_ADDITION_FLAG), NULL, 'R') EXCEPTION_REASON , PA_EXCEPTION_REASONS_PUB.GET_EXCEPTION_TEXT('AP_INV_EXCP', DECODE(INVDIST.PA_ADDITION_FLAG, 'N',DECODE(INV.INVOICE_TYPE_LOOKUP_CODE, 'EXPENSE REPORT', 'ERN', INVDIST.PA_ADDITION_FLAG),INVDIST.PA_ADDITION_FLAG), NULL, 'A') CORRECTIVE_ACTION , INVDIST.ORG_ID , IMP.SAME_PA_GL_PERIOD , PA_EXPENDITURES_UTILS.GETORGTLNAME(INVDIST.ORG_ID) , IMP.SET_OF_BOOKS_ID , VEND.SEGMENT1 VENDOR_NUMBER , VEND.VENDOR_NAME VENDOR_NAME , NULL PAYMENT_NUM , NULL CHECK_NUMBER FROM GL_LEDGERS GLSOB , PO_VENDORS VEND , AP_SELF_ASSESSED_TAX_DIST_ALL INVDIST , AP_INVOICE_LINES_ALL INVLINE , AP_INVOICES_ALL INV , PA_IMPLEMENTATIONS_ALL IMP WHERE VEND.VENDOR_ID = INV.VENDOR_ID AND INV.INVOICE_ID = INVDIST.INVOICE_ID AND INV.INVOICE_ID = INVLINE.INVOICE_ID AND INVLINE.LINE_NUMBER = INVDIST.INVOICE_LINE_NUMBER AND DECODE(INV.INVOICE_TYPE_LOOKUP_CODE, 'EXPENSE REPORT', VEND.EMPLOYEE_ID, -99) IS NOT NULL AND INVDIST.PROJECT_ID > 0 AND INVDIST.PA_ADDITION_FLAG IN ( 'A', 'B', 'C', 'D', 'I', 'J', 'K', 'M', 'N', 'P', 'Q', 'S', 'V', 'X','O') AND INVDIST.POSTED_FLAG = 'Y' AND NVL(GLSOB.SLA_LEDGER_CASH_BASIS_FLAG, 'N') = 'N' AND INV.SOURCE <>'ORACLE PROJECT ACCOUNTING' AND GLSOB.LEDGER_ID = INVDIST.SET_OF_BOOKS_ID AND INVDIST.ORG_ID = IMP.ORG_ID AND NVL(invdist.tax_recoverable_flag,'N') ='N' AND(((inv.invoice_type_lookup_code = 'EXPENSE REPORT' AND inv.source IN ('XpenseXpress','Manual Invoice Entry','SelfService') AND INVDIST.line_type_lookup_code <> 'PREPAY' AND (vend.employee_id IS NOT NULL OR NVL(inv.paid_on_behalf_employee_id,0) > 0)) OR (inv.invoice_type_lookup_code IN ('STANDARD','CREDIT','MIXED') AND inv.source IN ('CREDIT CARD','Both Pay') AND INVDIST.line_type_lookup_code <> 'PREPAY' AND NVL(inv.PAID_ON_BEHALF_EMPLOYEE_ID,0) > 0 ) ) OR EXISTS (SELECT 1 FROM po_distributions PO , AP_SELF_ASSESSED_TAX_DIST_ALL APD WHERE inv.PAID_ON_BEHALF_EMPLOYEE_ID IS NULL AND APD.invoice_distribution_id = invdist.invoice_distribution_id AND APD.po_distribution_id = PO.po_distribution_id(+) AND NVL(PO.destination_type_code, 'EXPENSE') = 'EXPENSE' AND inv.invoice_type_lookup_code NOT IN ('PREPAYMENT', 'EXPENSE REPORT') AND INVDIST.line_type_lookup_code <> 'PREPAY' AND (NVL(INV.source, 'xx' ) NOT IN ('Oracle Project Accounting','PA_IC_INVOICES','PA_COST_ADJUSTMENTS') OR invdist.line_type_lookup_code = 'TAX') )) AND (pa_nl_installed.is_nl_installed = 'N' OR ( pa_nl_installed.is_nl_installed = 'Y' AND NOT EXISTS (SELECT 'X' FROM po_distributions_all pod,mtl_system_items si, po_lines_all pol WHERE pod.po_distribution_id = INVDIST.po_distribution_id AND pod.po_line_id = pol.po_line_id AND si.inventory_item_id = pol.item_id AND si.comms_nl_trackable_flag = 'Y' AND (si.organization_id = pod.org_id OR si.organization_id IN (SELECT NVL(ship_to_organization_id,-9999) FROM po_line_locations_all pll WHERE pll.po_header_id = pod.po_header_id AND pll.po_line_id = pod.po_line_id))) AND NOT EXISTS (SELECT 'X' FROM AP_SELF_ASSESSED_TAX_DIST_ALL apdist, po_distributions_all pod, mtl_system_items si, po_lines_all pol WHERE INVDIST.CHARGE_APPLICABLE_TO_DIST_ID = apdist.INVOICE_DISTRIBUTION_ID AND apdist.po_distribution_id = pod.po_distribution_id AND pod.po_line_id = pol.po_line_id AND si.inventory_item_id = pol.item_id AND si.comms_nl_trackable_flag = 'Y' AND (si.organization_id = pod.org_id OR si.organization_id IN (SELECT NVL(ship_to_organization_id,-9999) FROM po_line_locations_all pll WHERE pll.po_header_id = pod.po_header_id AND pll.po_line_id = pod.po_line_id)))))

View Text - HTML Formatted

SELECT INVDIST.PROJECT_ID PROJECT_ID
, INVDIST.TASK_ID TASK_ID
, INV.INVOICE_ID INVOICE_ID
, INV.INVOICE_NUM INVOICE_NUMBER
, INVDIST.INVOICE_LINE_NUMBER
, INVDIST.DISTRIBUTION_LINE_NUMBER DISTRIBUTION_LINE_NUMBER
, INVDIST.INVOICE_DISTRIBUTION_ID
, INV.INVOICE_DATE INVOICE_DATE
, INV.VENDOR_ID VENDOR_ID
, INV.INVOICE_CURRENCY_CODE DENOM_CURRENCY_CODE
, INVDIST.AMOUNT DENOM_AMOUNT
, GLSOB.CURRENCY_CODE ACCT_CURRENCY_CODE
, NVL(INVDIST.BASE_AMOUNT
, INVDIST.AMOUNT) AMOUNT
, DECODE (NVL(INV.INVOICE_CURRENCY_CODE
, '-99')
, NVL(GLSOB.CURRENCY_CODE
, '-99')
, TO_DATE(NULL)
, INVDIST.EXCHANGE_DATE) ACCT_RATE_DATE
, DECODE (NVL(INV.INVOICE_CURRENCY_CODE
, '-99')
, NVL(GLSOB.CURRENCY_CODE
, '-99')
, NULL
, INVDIST.EXCHANGE_RATE_TYPE) ACCT_RATE_TYPE
, DECODE (NVL(INV.INVOICE_CURRENCY_CODE
, '-99')
, NVL(GLSOB.CURRENCY_CODE
, '-99')
, TO_NUMBER(NULL)
, INVDIST.EXCHANGE_RATE) ACCT_EXCHANGE_RATE
, INVDIST.PA_ADDITION_FLAG EXCEPTION_CODE
, INVDIST.EXPENDITURE_ORGANIZATION_ID EXPENDITURE_ORGANIZATION_ID
, INVDIST.EXPENDITURE_TYPE EXPENDITURE_TYPE
, INVDIST.EXPENDITURE_ITEM_DATE EXPENDITURE_ITEM_DATE
, INVDIST.PA_QUANTITY QUANTITY
, INVDIST.ACCOUNTING_DATE GL_DATE
, PA_UTILS2.PA_DATE_PROFILE(INVDIST.EXPENDITURE_ITEM_DATE
, INVDIST.ACCOUNTING_DATE
, INVDIST.ORG_ID) PA_DATE
, PA_UTILS2.PA_PERIOD_NAME_PROFILE() PERIOD_NAME
, INVDIST.PERIOD_NAME GL_PERIOD_NAME
, DECODE(INVDIST.POSTED_FLAG
, 'Y'
, '*'
, NULL) POSTED_FLAG
, PA_EXCEPTION_REASONS_PUB.GET_EXCEPTION_TEXT('AP_INV_EXCP'
, DECODE(INVDIST.PA_ADDITION_FLAG
, 'N'
, DECODE(INV.INVOICE_TYPE_LOOKUP_CODE
, 'EXPENSE REPORT'
, 'ERN'
, INVDIST.PA_ADDITION_FLAG)
, INVDIST.PA_ADDITION_FLAG)
, NULL
, 'R') EXCEPTION_REASON
, PA_EXCEPTION_REASONS_PUB.GET_EXCEPTION_TEXT('AP_INV_EXCP'
, DECODE(INVDIST.PA_ADDITION_FLAG
, 'N'
, DECODE(INV.INVOICE_TYPE_LOOKUP_CODE
, 'EXPENSE REPORT'
, 'ERN'
, INVDIST.PA_ADDITION_FLAG)
, INVDIST.PA_ADDITION_FLAG)
, NULL
, 'A') CORRECTIVE_ACTION
, INVDIST.ORG_ID
, IMP.SAME_PA_GL_PERIOD
, PA_EXPENDITURES_UTILS.GETORGTLNAME(INVDIST.ORG_ID)
, IMP.SET_OF_BOOKS_ID
, VEND.SEGMENT1 VENDOR_NUMBER
, VEND.VENDOR_NAME VENDOR_NAME
, NULL PAYMENT_NUM
, NULL CHECK_NUMBER
FROM GL_LEDGERS GLSOB
, PO_VENDORS VEND
, AP_INVOICE_DISTRIBUTIONS_ALL INVDIST
, AP_INVOICE_LINES_ALL INVLINE
, AP_INVOICES_ALL INV
, PA_IMPLEMENTATIONS_ALL IMP
WHERE VEND.VENDOR_ID = INV.VENDOR_ID
AND INV.INVOICE_ID = INVDIST.INVOICE_ID
AND INV.INVOICE_ID = INVLINE.INVOICE_ID
AND INVLINE.LINE_NUMBER = INVDIST.INVOICE_LINE_NUMBER
AND DECODE(INV.INVOICE_TYPE_LOOKUP_CODE
, 'EXPENSE REPORT'
, VEND.EMPLOYEE_ID
, -99) IS NOT NULL
AND INVDIST.PROJECT_ID > 0
AND INVDIST.PA_ADDITION_FLAG IN ( 'A'
, 'B'
, 'C'
, 'D'
, 'I'
, 'J'
, 'K'
, 'M'
, 'N'
, 'P'
, 'Q'
, 'S'
, 'V'
, 'X'
, 'O')
AND INVDIST.POSTED_FLAG = 'Y'
AND NVL(GLSOB.SLA_LEDGER_CASH_BASIS_FLAG
, 'N') = 'N'
AND INV.SOURCE <>'ORACLE PROJECT ACCOUNTING'
AND GLSOB.LEDGER_ID = INVDIST.SET_OF_BOOKS_ID
AND INVDIST.ORG_ID =IMP.ORG_ID
AND NVL(INVDIST.TAX_RECOVERABLE_FLAG
, 'N') ='N' AND(((INV.INVOICE_TYPE_LOOKUP_CODE = 'EXPENSE REPORT'
AND INV.SOURCE IN ('XPENSEXPRESS'
, 'MANUAL INVOICE ENTRY'
, 'SELFSERVICE')
AND (VEND.EMPLOYEE_ID IS NOT NULL OR NVL(INV.PAID_ON_BEHALF_EMPLOYEE_ID
, 0) > 0)) OR (INV.INVOICE_TYPE_LOOKUP_CODE IN ('STANDARD'
, 'CREDIT'
, 'MIXED')
AND INV.SOURCE IN ('CREDIT CARD'
, 'BOTH PAY')
AND NVL(INV.PAID_ON_BEHALF_EMPLOYEE_ID
, 0) > 0 ) ) OR EXISTS ( SELECT 1
FROM PO_DISTRIBUTIONS PO
, AP_INVOICE_DISTRIBUTIONS_ALL APD
WHERE INV.PAID_ON_BEHALF_EMPLOYEE_ID IS NULL
AND APD.INVOICE_DISTRIBUTION_ID = INVDIST.INVOICE_DISTRIBUTION_ID
AND APD.PO_DISTRIBUTION_ID = PO.PO_DISTRIBUTION_ID(+)
AND NVL(PO.DESTINATION_TYPE_CODE
, 'EXPENSE') = 'EXPENSE'
AND INV.INVOICE_TYPE_LOOKUP_CODE NOT IN ('PREPAYMENT'
, 'EXPENSE REPORT')
AND INVDIST.LINE_TYPE_LOOKUP_CODE <> 'PREPAY'
AND (NVL(INV.SOURCE
, 'XX' ) NOT IN ('ORACLE PROJECT ACCOUNTING'
, 'PA_IC_INVOICES'
, 'PA_COST_ADJUSTMENTS') OR INVDIST.LINE_TYPE_LOOKUP_CODE = 'TAX')) )
AND (PA_NL_INSTALLED.IS_NL_INSTALLED = 'N' OR ( PA_NL_INSTALLED.IS_NL_INSTALLED = 'Y'
AND NOT EXISTS (SELECT 'X'
FROM PO_DISTRIBUTIONS_ALL POD
, MTL_SYSTEM_ITEMS SI
, PO_LINES_ALL POL
WHERE POD.PO_DISTRIBUTION_ID = INVDIST.PO_DISTRIBUTION_ID
AND POD.PO_LINE_ID = POL.PO_LINE_ID
AND SI.INVENTORY_ITEM_ID = POL.ITEM_ID
AND SI.COMMS_NL_TRACKABLE_FLAG = 'Y'
AND (SI.ORGANIZATION_ID = POD.ORG_ID OR SI.ORGANIZATION_ID IN (SELECT NVL(SHIP_TO_ORGANIZATION_ID
, -9999)
FROM PO_LINE_LOCATIONS_ALL PLL
WHERE PLL.PO_HEADER_ID = POD.PO_HEADER_ID
AND PLL.PO_LINE_ID = POD.PO_LINE_ID)))
AND NOT EXISTS (SELECT 'X'
FROM AP_INVOICE_DISTRIBUTIONS_ALL APDIST
, PO_DISTRIBUTIONS_ALL POD
, MTL_SYSTEM_ITEMS SI
, PO_LINES_ALL POL
WHERE INVDIST.CHARGE_APPLICABLE_TO_DIST_ID = APDIST.INVOICE_DISTRIBUTION_ID
AND APDIST.PO_DISTRIBUTION_ID = POD.PO_DISTRIBUTION_ID
AND POD.PO_LINE_ID = POL.PO_LINE_ID
AND SI.INVENTORY_ITEM_ID = POL.ITEM_ID
AND SI.COMMS_NL_TRACKABLE_FLAG = 'Y'
AND (SI.ORGANIZATION_ID = POD.ORG_ID OR SI.ORGANIZATION_ID IN (SELECT NVL(SHIP_TO_ORGANIZATION_ID
, -9999)
FROM PO_LINE_LOCATIONS_ALL PLL
WHERE PLL.PO_HEADER_ID = POD.PO_HEADER_ID
AND PLL.PO_LINE_ID = POD.PO_LINE_ID))))) UNION ALL SELECT INVDIST.PROJECT_ID PROJECT_ID
, INVDIST.TASK_ID TASK_ID
, INV.INVOICE_ID INVOICE_ID
, INV.INVOICE_NUM INVOICE_NUMBER
, INVDIST.INVOICE_LINE_NUMBER
, INVDIST.DISTRIBUTION_LINE_NUMBER DISTRIBUTION_LINE_NUMBER
, INVDIST.INVOICE_DISTRIBUTION_ID
, INV.INVOICE_DATE INVOICE_DATE
, INV.VENDOR_ID VENDOR_ID
, INV.INVOICE_CURRENCY_CODE DENOM_CURRENCY_CODE
, DECODE(PAYDIST.PAY_DIST_LOOKUP_CODE
, 'CASH'
, INVPMT.AMOUNT
, 'DISCOUNT'
, INVPMT.DISCOUNT_TAKEN) DENOM_AMOUNT
, GLSOB.CURRENCY_CODE ACCT_CURRENCY_CODE
, NVL(INVPMT.PAYMENT_BASE_AMOUNT
, INVPMT.AMOUNT) AMOUNT
, DECODE (NVL(INV.INVOICE_CURRENCY_CODE
, '-99')
, NVL(GLSOB.CURRENCY_CODE
, '-99')
, TO_DATE(NULL)
, INVDIST.EXCHANGE_DATE) ACCT_RATE_DATE
, DECODE (NVL(INV.INVOICE_CURRENCY_CODE
, '-99')
, NVL(GLSOB.CURRENCY_CODE
, '-99')
, NULL
, INVDIST.EXCHANGE_RATE_TYPE) ACCT_RATE_TYPE
, DECODE (NVL(INV.INVOICE_CURRENCY_CODE
, '-99')
, NVL(GLSOB.CURRENCY_CODE
, '-99')
, TO_NUMBER(NULL)
, INVDIST.EXCHANGE_RATE) ACCT_EXCHANGE_RATE
, PAYDIST.PA_ADDITION_FLAG EXCEPTION_CODE
, INVDIST.EXPENDITURE_ORGANIZATION_ID EXPENDITURE_ORGANIZATION_ID
, INVDIST.EXPENDITURE_TYPE EXPENDITURE_TYPE
, INVDIST.EXPENDITURE_ITEM_DATE EXPENDITURE_ITEM_DATE
, INVDIST.PA_QUANTITY QUANTITY
, INVDIST.ACCOUNTING_DATE GL_DATE
, PA_UTILS2.PA_DATE_PROFILE(INVDIST.EXPENDITURE_ITEM_DATE
, INVDIST.ACCOUNTING_DATE
, INVDIST.ORG_ID) PA_DATE
, PA_UTILS2.PA_PERIOD_NAME_PROFILE() PERIOD_NAME
, INVDIST.PERIOD_NAME GL_PERIOD_NAME
, DECODE(PAYHIST.POSTED_FLAG
, 'Y'
, '*'
, NULL) POSTED_FLAG
, PA_EXCEPTION_REASONS_PUB.GET_EXCEPTION_TEXT('AP_INV_EXCP'
, DECODE(PAYDIST.PA_ADDITION_FLAG
, 'N'
, DECODE(INV.INVOICE_TYPE_LOOKUP_CODE
, 'EXPENSE REPORT'
, 'ERN'
, PAYDIST.PA_ADDITION_FLAG)
, PAYDIST.PA_ADDITION_FLAG)
, NULL
, 'R') EXCEPTION_REASON
, PA_EXCEPTION_REASONS_PUB.GET_EXCEPTION_TEXT('AP_INV_EXCP'
, DECODE(PAYDIST.PA_ADDITION_FLAG
, 'N'
, DECODE(INV.INVOICE_TYPE_LOOKUP_CODE
, 'EXPENSE REPORT'
, 'ERN'
, PAYDIST.PA_ADDITION_FLAG)
, PAYDIST.PA_ADDITION_FLAG)
, NULL
, 'A') CORRECTIVE_ACTION
, INVDIST.ORG_ID
, IMP.SAME_PA_GL_PERIOD
, PA_EXPENDITURES_UTILS.GETORGTLNAME(INVDIST.ORG_ID)
, IMP.SET_OF_BOOKS_ID
, VEND.SEGMENT1 VENDOR_NUMBER
, VEND.VENDOR_NAME VENDOR_NAME
, INVPMT.PAYMENT_NUM
, PAYCHK.CHECK_NUMBER
FROM GL_LEDGERS GLSOB
, PO_VENDORS VEND
, AP_INVOICE_DISTRIBUTIONS_ALL INVDIST
, AP_PAYMENT_HIST_DISTS PAYDIST
, AP_PAYMENT_HISTORY_ALL PAYHIST
, AP_INVOICE_PAYMENTS_ALL INVPMT
, AP_CHECKS_ALL PAYCHK
, AP_INVOICE_LINES_ALL INVLINE
, AP_INVOICES_ALL INV
, PA_IMPLEMENTATIONS_ALL IMP
WHERE VEND.VENDOR_ID = INV.VENDOR_ID
AND INV.INVOICE_ID = INVDIST.INVOICE_ID
AND INV.INVOICE_ID = INVLINE.INVOICE_ID
AND PAYDIST.INVOICE_DISTRIBUTION_ID = INVDIST.INVOICE_DISTRIBUTION_ID
AND INVLINE.LINE_NUMBER = INVDIST.INVOICE_LINE_NUMBER
AND DECODE(INV.INVOICE_TYPE_LOOKUP_CODE
, 'EXPENSE REPORT'
, VEND.EMPLOYEE_ID
, -99) IS NOT NULL
AND INVDIST.PROJECT_ID > 0
AND PAYDIST.PA_ADDITION_FLAG IN ( 'A'
, 'B'
, 'C'
, 'D'
, 'I'
, 'J'
, 'K'
, 'M'
, 'N'
, 'P'
, 'Q'
, 'S'
, 'V'
, 'X'
, 'O')
AND PAYDIST.PAYMENT_HISTORY_ID = PAYHIST.PAYMENT_HISTORY_ID
AND PAYHIST.POSTED_FLAG = 'Y'
AND INVPMT.INVOICE_PAYMENT_ID = PAYDIST.INVOICE_PAYMENT_ID
AND INVPMT.CHECK_ID = PAYCHK.CHECK_ID
AND NVL(GLSOB.SLA_LEDGER_CASH_BASIS_FLAG
, 'N') IN ('N'
, 'Y')
AND PAYDIST.PAY_DIST_LOOKUP_CODE = DECODE ( NVL(GLSOB.SLA_LEDGER_CASH_BASIS_FLAG
, 'N')
, 'N'
, 'DISCOUNT'
, NVL(GLSOB.SLA_LEDGER_CASH_BASIS_FLAG
, 'N')
, 'Y'
, 'CASH')
AND INV.SOURCE <>'ORACLE PROJECT ACCOUNTING'
AND GLSOB.LEDGER_ID = INVDIST.SET_OF_BOOKS_ID
AND INVDIST.ORG_ID =IMP.ORG_ID
AND NVL(INVDIST.TAX_RECOVERABLE_FLAG
, 'N') ='N' AND(((INV.INVOICE_TYPE_LOOKUP_CODE = 'EXPENSE REPORT'
AND INV.SOURCE IN ('XPENSEXPRESS'
, 'MANUAL INVOICE ENTRY'
, 'SELFSERVICE')
AND (VEND.EMPLOYEE_ID IS NOT NULL OR NVL(INV.PAID_ON_BEHALF_EMPLOYEE_ID
, 0) > 0)) OR (INV.INVOICE_TYPE_LOOKUP_CODE IN ('STANDARD'
, 'CREDIT'
, 'MIXED')
AND INV.SOURCE IN ('CREDIT CARD'
, 'BOTH PAY')
AND NVL(INV.PAID_ON_BEHALF_EMPLOYEE_ID
, 0) > 0 ) ) OR EXISTS ( SELECT 1
FROM PO_DISTRIBUTIONS PO
, AP_INVOICE_DISTRIBUTIONS_ALL APD
WHERE INV.PAID_ON_BEHALF_EMPLOYEE_ID IS NULL
AND APD.INVOICE_DISTRIBUTION_ID = INVDIST.INVOICE_DISTRIBUTION_ID
AND APD.PO_DISTRIBUTION_ID = PO.PO_DISTRIBUTION_ID(+)
AND NVL(PO.DESTINATION_TYPE_CODE
, 'EXPENSE') = 'EXPENSE'
AND INV.INVOICE_TYPE_LOOKUP_CODE NOT IN ('PREPAYMENT'
, 'EXPENSE REPORT')
AND INVDIST.LINE_TYPE_LOOKUP_CODE <> 'PREPAY'
AND (NVL(INV.SOURCE
, 'XX' ) NOT IN ('ORACLE PROJECT ACCOUNTING'
, 'PA_IC_INVOICES'
, 'PA_COST_ADJUSTMENTS') OR INVDIST.LINE_TYPE_LOOKUP_CODE = 'TAX')))
AND (PA_NL_INSTALLED.IS_NL_INSTALLED = 'N' OR ( PA_NL_INSTALLED.IS_NL_INSTALLED = 'Y'
AND NOT EXISTS (SELECT 'X'
FROM PO_DISTRIBUTIONS_ALL POD
, MTL_SYSTEM_ITEMS SI
, PO_LINES_ALL POL
WHERE POD.PO_DISTRIBUTION_ID = INVDIST.PO_DISTRIBUTION_ID
AND POD.PO_LINE_ID = POL.PO_LINE_ID
AND SI.INVENTORY_ITEM_ID = POL.ITEM_ID
AND SI.COMMS_NL_TRACKABLE_FLAG = 'Y'
AND (SI.ORGANIZATION_ID = POD.ORG_ID OR SI.ORGANIZATION_ID IN (SELECT NVL(SHIP_TO_ORGANIZATION_ID
, -9999)
FROM PO_LINE_LOCATIONS_ALL PLL
WHERE PLL.PO_HEADER_ID = POD.PO_HEADER_ID
AND PLL.PO_LINE_ID = POD.PO_LINE_ID)))
AND NOT EXISTS (SELECT 'X'
FROM AP_INVOICE_DISTRIBUTIONS_ALL APDIST
, PO_DISTRIBUTIONS_ALL POD
, MTL_SYSTEM_ITEMS SI
, PO_LINES_ALL POL
WHERE INVDIST.CHARGE_APPLICABLE_TO_DIST_ID = APDIST.INVOICE_DISTRIBUTION_ID
AND APDIST.PO_DISTRIBUTION_ID = POD.PO_DISTRIBUTION_ID
AND POD.PO_LINE_ID = POL.PO_LINE_ID
AND SI.INVENTORY_ITEM_ID = POL.ITEM_ID
AND SI.COMMS_NL_TRACKABLE_FLAG = 'Y'
AND (SI.ORGANIZATION_ID = POD.ORG_ID OR SI.ORGANIZATION_ID IN (SELECT NVL(SHIP_TO_ORGANIZATION_ID
, -9999)
FROM PO_LINE_LOCATIONS_ALL PLL
WHERE PLL.PO_HEADER_ID = POD.PO_HEADER_ID
AND PLL.PO_LINE_ID = POD.PO_LINE_ID))))) UNION ALL SELECT INVDIST.PROJECT_ID PROJECT_ID
, INVDIST.TASK_ID TASK_ID
, INV.INVOICE_ID INVOICE_ID
, INV.INVOICE_NUM INVOICE_NUMBER
, INVDIST.INVOICE_LINE_NUMBER
, INVDIST.DISTRIBUTION_LINE_NUMBER DISTRIBUTION_LINE_NUMBER
, INVDIST.INVOICE_DISTRIBUTION_ID
, INV.INVOICE_DATE INVOICE_DATE
, INV.VENDOR_ID VENDOR_ID
, INV.INVOICE_CURRENCY_CODE DENOM_CURRENCY_CODE
, INVDIST.AMOUNT DENOM_AMOUNT
, GLSOB.CURRENCY_CODE ACCT_CURRENCY_CODE
, NVL(INVDIST.BASE_AMOUNT
, INVDIST.AMOUNT) AMOUNT
, INV.EXCHANGE_DATE ACCT_RATE_DATE
, INV.EXCHANGE_RATE_TYPE ACCT_RATE_TYPE
, INV.EXCHANGE_RATE ACCT_EXCHANGE_RATE
, INVDIST.PA_ADDITION_FLAG EXCEPTION_CODE
, INVDIST.EXPENDITURE_ORGANIZATION_ID EXPENDITURE_ORGANIZATION_ID
, INVDIST.EXPENDITURE_TYPE EXPENDITURE_TYPE
, INVDIST.EXPENDITURE_ITEM_DATE EXPENDITURE_ITEM_DATE
, INVDIST.PA_QUANTITY QUANTITY
, INVDIST.ACCOUNTING_DATE GL_DATE
, PA_UTILS2.PA_DATE_PROFILE(INVDIST.EXPENDITURE_ITEM_DATE
, INVDIST.ACCOUNTING_DATE
, INVDIST.ORG_ID) PA_DATE
, PA_UTILS2.PA_PERIOD_NAME_PROFILE() PERIOD_NAME
, INVDIST.PERIOD_NAME GL_PERIOD_NAME
, DECODE(INVDIST.POSTED_FLAG
, 'Y'
, '*'
, NULL) POSTED_FLAG
, PA_EXCEPTION_REASONS_PUB.GET_EXCEPTION_TEXT('AP_INV_EXCP'
, DECODE(INVDIST.PA_ADDITION_FLAG
, 'N'
, DECODE(INV.INVOICE_TYPE_LOOKUP_CODE
, 'EXPENSE REPORT'
, 'ERN'
, INVDIST.PA_ADDITION_FLAG)
, INVDIST.PA_ADDITION_FLAG)
, NULL
, 'R') EXCEPTION_REASON
, PA_EXCEPTION_REASONS_PUB.GET_EXCEPTION_TEXT('AP_INV_EXCP'
, DECODE(INVDIST.PA_ADDITION_FLAG
, 'N'
, DECODE(INV.INVOICE_TYPE_LOOKUP_CODE
, 'EXPENSE REPORT'
, 'ERN'
, INVDIST.PA_ADDITION_FLAG)
, INVDIST.PA_ADDITION_FLAG)
, NULL
, 'A') CORRECTIVE_ACTION
, INVDIST.ORG_ID
, IMP.SAME_PA_GL_PERIOD
, PA_EXPENDITURES_UTILS.GETORGTLNAME(INVDIST.ORG_ID)
, IMP.SET_OF_BOOKS_ID
, VEND.SEGMENT1 VENDOR_NUMBER
, VEND.VENDOR_NAME VENDOR_NAME
, NULL PAYMENT_NUM
, NULL CHECK_NUMBER
FROM GL_LEDGERS GLSOB
, PO_VENDORS VEND
, AP_SELF_ASSESSED_TAX_DIST_ALL INVDIST
, AP_INVOICE_LINES_ALL INVLINE
, AP_INVOICES_ALL INV
, PA_IMPLEMENTATIONS_ALL IMP
WHERE VEND.VENDOR_ID = INV.VENDOR_ID
AND INV.INVOICE_ID = INVDIST.INVOICE_ID
AND INV.INVOICE_ID = INVLINE.INVOICE_ID
AND INVLINE.LINE_NUMBER = INVDIST.INVOICE_LINE_NUMBER
AND DECODE(INV.INVOICE_TYPE_LOOKUP_CODE
, 'EXPENSE REPORT'
, VEND.EMPLOYEE_ID
, -99) IS NOT NULL
AND INVDIST.PROJECT_ID > 0
AND INVDIST.PA_ADDITION_FLAG IN ( 'A'
, 'B'
, 'C'
, 'D'
, 'I'
, 'J'
, 'K'
, 'M'
, 'N'
, 'P'
, 'Q'
, 'S'
, 'V'
, 'X'
, 'O')
AND INVDIST.POSTED_FLAG = 'Y'
AND NVL(GLSOB.SLA_LEDGER_CASH_BASIS_FLAG
, 'N') = 'N'
AND INV.SOURCE <>'ORACLE PROJECT ACCOUNTING'
AND GLSOB.LEDGER_ID = INVDIST.SET_OF_BOOKS_ID
AND INVDIST.ORG_ID = IMP.ORG_ID
AND NVL(INVDIST.TAX_RECOVERABLE_FLAG
, 'N') ='N' AND(((INV.INVOICE_TYPE_LOOKUP_CODE = 'EXPENSE REPORT'
AND INV.SOURCE IN ('XPENSEXPRESS'
, 'MANUAL INVOICE ENTRY'
, 'SELFSERVICE')
AND INVDIST.LINE_TYPE_LOOKUP_CODE <> 'PREPAY'
AND (VEND.EMPLOYEE_ID IS NOT NULL OR NVL(INV.PAID_ON_BEHALF_EMPLOYEE_ID
, 0) > 0)) OR (INV.INVOICE_TYPE_LOOKUP_CODE IN ('STANDARD'
, 'CREDIT'
, 'MIXED')
AND INV.SOURCE IN ('CREDIT CARD'
, 'BOTH PAY')
AND INVDIST.LINE_TYPE_LOOKUP_CODE <> 'PREPAY'
AND NVL(INV.PAID_ON_BEHALF_EMPLOYEE_ID
, 0) > 0 ) ) OR EXISTS (SELECT 1
FROM PO_DISTRIBUTIONS PO
, AP_SELF_ASSESSED_TAX_DIST_ALL APD
WHERE INV.PAID_ON_BEHALF_EMPLOYEE_ID IS NULL
AND APD.INVOICE_DISTRIBUTION_ID = INVDIST.INVOICE_DISTRIBUTION_ID
AND APD.PO_DISTRIBUTION_ID = PO.PO_DISTRIBUTION_ID(+)
AND NVL(PO.DESTINATION_TYPE_CODE
, 'EXPENSE') = 'EXPENSE'
AND INV.INVOICE_TYPE_LOOKUP_CODE NOT IN ('PREPAYMENT'
, 'EXPENSE REPORT')
AND INVDIST.LINE_TYPE_LOOKUP_CODE <> 'PREPAY'
AND (NVL(INV.SOURCE
, 'XX' ) NOT IN ('ORACLE PROJECT ACCOUNTING'
, 'PA_IC_INVOICES'
, 'PA_COST_ADJUSTMENTS') OR INVDIST.LINE_TYPE_LOOKUP_CODE = 'TAX') ))
AND (PA_NL_INSTALLED.IS_NL_INSTALLED = 'N' OR ( PA_NL_INSTALLED.IS_NL_INSTALLED = 'Y'
AND NOT EXISTS (SELECT 'X'
FROM PO_DISTRIBUTIONS_ALL POD
, MTL_SYSTEM_ITEMS SI
, PO_LINES_ALL POL
WHERE POD.PO_DISTRIBUTION_ID = INVDIST.PO_DISTRIBUTION_ID
AND POD.PO_LINE_ID = POL.PO_LINE_ID
AND SI.INVENTORY_ITEM_ID = POL.ITEM_ID
AND SI.COMMS_NL_TRACKABLE_FLAG = 'Y'
AND (SI.ORGANIZATION_ID = POD.ORG_ID OR SI.ORGANIZATION_ID IN (SELECT NVL(SHIP_TO_ORGANIZATION_ID
, -9999)
FROM PO_LINE_LOCATIONS_ALL PLL
WHERE PLL.PO_HEADER_ID = POD.PO_HEADER_ID
AND PLL.PO_LINE_ID = POD.PO_LINE_ID)))
AND NOT EXISTS (SELECT 'X'
FROM AP_SELF_ASSESSED_TAX_DIST_ALL APDIST
, PO_DISTRIBUTIONS_ALL POD
, MTL_SYSTEM_ITEMS SI
, PO_LINES_ALL POL
WHERE INVDIST.CHARGE_APPLICABLE_TO_DIST_ID = APDIST.INVOICE_DISTRIBUTION_ID
AND APDIST.PO_DISTRIBUTION_ID = POD.PO_DISTRIBUTION_ID
AND POD.PO_LINE_ID = POL.PO_LINE_ID
AND SI.INVENTORY_ITEM_ID = POL.ITEM_ID
AND SI.COMMS_NL_TRACKABLE_FLAG = 'Y'
AND (SI.ORGANIZATION_ID = POD.ORG_ID OR SI.ORGANIZATION_ID IN (SELECT NVL(SHIP_TO_ORGANIZATION_ID
, -9999)
FROM PO_LINE_LOCATIONS_ALL PLL
WHERE PLL.PO_HEADER_ID = POD.PO_HEADER_ID
AND PLL.PO_LINE_ID = POD.PO_LINE_ID)))))