View: PA_PROJ_AP_INV_DIST_V

Product:	PA - Projects
Description:
Implementation/DBA Data:	APPS.PA_PROJ_AP_INV_DIST_V

View Text

SELECT /*+ ORDERED LEADING(P) USE_NL(T D)*/ I.INVOICE_NUM
, V.VENDOR_NAME
, I.INVOICE_DATE
, D.INVOICE_DISTRIBUTION_ID
, D.DISTRIBUTION_LINE_NUMBER
, D.DESCRIPTION
, D.ACCOUNTING_DATE
, DECODE(D.POSTED_FLAG
, 'Y'
, 'Y'
, 'N')
, DECODE(AP_INVOICES_PKG.GET_APPROVAL_STATUS(I.INVOICE_ID
, I.INVOICE_AMOUNT
, I.PAYMENT_STATUS_FLAG
, I.INVOICE_TYPE_LOOKUP_CODE)
, 'APPROVED'
, 'Y'
, 'N') APPROVED_FLAG
, (DECODE(NVL(PO.ACCRUE_ON_RECEIPT_FLAG
, 'N')
, 'Y'
, PA_CMT_UTILS.GET_RCPT_QTY(PO.PO_DISTRIBUTION_ID
, 0
, 0
, 0
, 'AP'
, PO.PO_LINE_ID
, P.PROJECT_ID
, T.TASK_ID
, PO.PO_DISTRIBUTION_ID
, D.PA_QUANTITY
, I.SOURCE
, D.LINE_TYPE_LOOKUP_CODE)
, 'N'
, D.PA_QUANTITY)) PA_QUANTITY
, I.INVOICE_CURRENCY_CODE
, (DECODE(NVL(PO.ACCRUE_ON_RECEIPT_FLAG
, 'N')
, 'Y'
, PA_CMT_UTILS.GET_INV_CMT(PO.PO_DISTRIBUTION_ID
, 'Y'
, D.PA_ADDITION_FLAG
, (NVL(D.INVOICE_PRICE_VARIANCE
, 0)+NVL(D.EXCHANGE_RATE_VARIANCE
, 0))
, PO.CODE_COMBINATION_ID
, 'AP'
, D.INVOICE_ID
, D.DISTRIBUTION_LINE_NUMBER)
, 'N'
, D.AMOUNT)) DENOM_AMOUNT
, G.CURRENCY_CODE
, (DECODE(NVL(PO.ACCRUE_ON_RECEIPT_FLAG
, 'N')
, 'Y'
, PA_CMT_UTILS.GET_INV_CMT(PO.PO_DISTRIBUTION_ID
, 'N'
, D.PA_ADDITION_FLAG
, (NVL(NVL(D.BASE_INVOICE_PRICE_VARIANCE
, NVL(D.INVOICE_PRICE_VARIANCE
, 0))
, 0) + NVL(D.EXCHANGE_RATE_VARIANCE
, 0))
, PO.CODE_COMBINATION_ID
, 'AP'
, D.INVOICE_ID
, D.DISTRIBUTION_LINE_NUMBER )
, 'N'
, NVL(D.BASE_AMOUNT
, D.AMOUNT))) AMOUNT
, DECODE(NVL(G.CURRENCY_CODE
, '-99')
, NVL(I.INVOICE_CURRENCY_CODE
, '-99')
, TO_DATE(NULL)
, D.EXCHANGE_DATE)
, DECODE(NVL(G.CURRENCY_CODE
, '-99')
, NVL(I.INVOICE_CURRENCY_CODE
, '-99')
, NULL
, D.EXCHANGE_RATE_TYPE)
, DECODE(NVL(G.CURRENCY_CODE
, '-99')
, NVL(I.INVOICE_CURRENCY_CODE
, '-99')
, TO_NUMBER(NULL)
, D.EXCHANGE_RATE)
, D.RECEIPT_CURRENCY_CODE
, D.RECEIPT_CURRENCY_AMOUNT
, D.RECEIPT_CONVERSION_RATE
, P.SEGMENT1
, P.NAME
, T.TASK_NUMBER
, T.TASK_NAME
, D.EXPENDITURE_TYPE
, ET.EXPENDITURE_CATEGORY
, ET.REVENUE_CATEGORY_CODE
, D.EXPENDITURE_ITEM_DATE
, I.INVOICE_ID
, I.VENDOR_ID
, P.PROJECT_ID
, T.TASK_ID
, D.EXPENDITURE_ORGANIZATION_ID
, 'FINANCIAL_ELEMENTS'
FROM PA_PROJECTS P
, PA_TASKS T
, AP_INVOICE_DISTRIBUTIONS_ALL D
, GL_SETS_OF_BOOKS G
, AP_INVOICES_ALL I
, PO_VENDORS V
, PA_EXPEND_TYP_SYS_LINKS ES
, PA_EXPENDITURE_TYPES ET
, PO_DISTRIBUTIONS_ALL PO
WHERE I.VENDOR_ID = V.VENDOR_ID
AND I.INVOICE_ID = D.INVOICE_ID
AND DECODE(D.PA_ADDITION_FLAG
, 'G'
, 'Y'
, 'Z'
, 'Y'
, 'T'
, 'Y'
, 'E'
, 'Y'
, 'F'
, 'Y'
, NULL
, 'N'
, D.PA_ADDITION_FLAG) <> 'Y'
AND ( ES.SYSTEM_LINKAGE_FUNCTION = 'VI' OR ( ES.SYSTEM_LINKAGE_FUNCTION = 'ER'
AND V.EMPLOYEE_ID IS NOT NULL ))
AND D.PO_DISTRIBUTION_ID = PO.PO_DISTRIBUTION_ID (+)
AND NVL(PO.DESTINATION_TYPE_CODE
, 'EXPENSE') = 'EXPENSE'
AND D.PROJECT_ID = T.PROJECT_ID
AND D.TASK_ID = T.TASK_ID
AND P.PROJECT_ID = T.PROJECT_ID
AND D.EXPENDITURE_TYPE = ES.EXPENDITURE_TYPE
AND ET.EXPENDITURE_TYPE = ES.EXPENDITURE_TYPE
AND G.SET_OF_BOOKS_ID = D.SET_OF_BOOKS_ID
AND NVL(I.SOURCE
, 'XXX') NOT IN ( 'ORACLE PROJECT ACCOUNTING'
, 'PA_IC_INVOICES')
AND ES.SYSTEM_LINKAGE_FUNCTION =DECODE( I.INVOICE_TYPE_LOOKUP_CODE
, 'EXPENSE REPORT'
, 'ER'
, 'VI')
AND NVL(D.TAX_RECOVERABLE_FLAG
, 'N') <> 'Y'

Columns