View: PA_AP_INV_EXCEPTIONS_SUM_V

Product:	PA - Projects
Description:	PA_AP_INV_EXCEPTIONS_SUM_V is a view that identifies the Payables invoices that prevent a period from being closed.
Implementation/DBA Data:	APPS.PA_AP_INV_EXCEPTIONS_SUM_V

View Text

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'
, INVDIST.PA_ADDITION_FLAG
, NULL
, 'R') EXCEPTION_REASON
, PA_EXCEPTION_REASONS_PUB.GET_EXCEPTION_TEXT('AP_INV_EXCP'
, 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')
AND INVDIST.POSTED_FLAG ||'' = 'Y'
AND NVL(GLSOB.SLA_LEDGER_CASH_BASIS_FLAG
, 'N') = 'N'
AND NVL(INV.SOURCE
, 'XXX')<>'ORACLE PROJECT ACCOUNTING'
AND GLSOB.LEDGER_ID = INVDIST.SET_OF_BOOKS_ID
AND NVL(INVDIST.ORG_ID
, -99) =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
WHERE INV.PAID_ON_BEHALF_EMPLOYEE_ID IS NULL
AND INVDIST.PO_DISTRIBUTION_ID = PO.PO_DISTRIBUTION_ID(+)
AND NVL(PO.DESTINATION_TYPE_CODE
, 'EXPENSE') = 'EXPENSE'
AND INV.INVOICE_TYPE_LOOKUP_CODE <> 'EXPENSE REPORT'
AND (NVL(INV.SOURCE
, 'XX' ) NOT IN ('ORACLE PROJECT ACCOUNTING'
, 'PA_IC_INVOICES'
, 'PA_COST_ADJUSTMENTS') OR INVDIST.LINE_TYPE_LOOKUP_CODE = 'TAX')) ) 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
, INVPMT.AMOUNT 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'
, PAYDIST.PA_ADDITION_FLAG
, NULL
, 'R') EXCEPTION_REASON
, PA_EXCEPTION_REASONS_PUB.GET_EXCEPTION_TEXT('AP_INV_EXCP'
, 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')
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') = 'Y'
AND NVL(INV.SOURCE
, 'XXX')<>'ORACLE PROJECT ACCOUNTING'
AND GLSOB.LEDGER_ID = INVDIST.SET_OF_BOOKS_ID
AND NVL(INVDIST.ORG_ID
, -99) =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
WHERE INV.PAID_ON_BEHALF_EMPLOYEE_ID IS NULL
AND INVDIST.PO_DISTRIBUTION_ID = PO.PO_DISTRIBUTION_ID(+)
AND NVL(PO.DESTINATION_TYPE_CODE
, 'EXPENSE') = 'EXPENSE'
AND INV.INVOICE_TYPE_LOOKUP_CODE <> 'EXPENSE REPORT'
AND (NVL(INV.SOURCE
, 'XX' ) NOT IN ('ORACLE PROJECT ACCOUNTING'
, 'PA_IC_INVOICES'
, 'PA_COST_ADJUSTMENTS') OR INVDIST.LINE_TYPE_LOOKUP_CODE = 'TAX')))

Columns