View: PA_PROJ_AP_INV_DISTRIBUTIONS

Product:	PA - Projects
Description:	Project-related AP invoice distributions not yet in Oracle Projects.
Implementation/DBA Data:	APPS.PA_PROJ_AP_INV_DISTRIBUTIONS

View Text

SELECT I.INVOICE_NUM
, V.VENDOR_NAME
, I.INVOICE_DATE
, 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
, PA_CMT_UTILS.GET_INV_CMT(PO1.PO_DISTRIBUTION_ID
, 'Y'
, D.PA_ADDITION_FLAG
, PA_CMT_UTILS.GET_INV_VAR(D.INVOICE_DISTRIBUTION_ID
, 'Y'
, D.AMOUNT_VARIANCE
, D.QUANTITY_VARIANCE)
, PO1.CODE_COMBINATION_ID
, 'AP'
, I.INVOICE_ID
, D.DISTRIBUTION_LINE_NUMBER
, D.INVOICE_DISTRIBUTION_ID
, NVL(PO1.ACCRUE_ON_RECEIPT_FLAG
, 'N')
, PO1.PO_LINE_ID
, 'Y'
, D.AMOUNT
, D.PROJECT_ID
, D.LINE_TYPE_LOOKUP_CODE
, D.PA_QUANTITY
, I.SOURCE
, NVL(G.SLA_LEDGER_CASH_BASIS_FLAG
, 'N')
, I.INVOICE_TYPE_LOOKUP_CODE
, D.HISTORICAL_FLAG
, D.PREPAY_AMOUNT_REMAINING )
, I.INVOICE_CURRENCY_CODE
, PA_CMT_UTILS.GET_INV_CMT(PO1.PO_DISTRIBUTION_ID
, 'Y'
, D.PA_ADDITION_FLAG
, PA_CMT_UTILS.GET_INV_VAR(D.INVOICE_DISTRIBUTION_ID
, 'Y'
, D.AMOUNT_VARIANCE
, D.QUANTITY_VARIANCE)
, PO1.CODE_COMBINATION_ID
, 'AP'
, I.INVOICE_ID
, D.DISTRIBUTION_LINE_NUMBER
, D.INVOICE_DISTRIBUTION_ID
, NVL(PO1.ACCRUE_ON_RECEIPT_FLAG
, 'N')
, PO1.PO_LINE_ID
, 'N'
, D.AMOUNT
, D.PROJECT_ID
, D.LINE_TYPE_LOOKUP_CODE
, D.PA_QUANTITY
, I.SOURCE
, NVL(G.SLA_LEDGER_CASH_BASIS_FLAG
, 'N')
, I.INVOICE_TYPE_LOOKUP_CODE
, D.HISTORICAL_FLAG
, D.PREPAY_AMOUNT_REMAINING )
, G.CURRENCY_CODE
, PA_CMT_UTILS.GET_INV_CMT(PO1.PO_DISTRIBUTION_ID
, 'N'
, D.PA_ADDITION_FLAG
, PA_CMT_UTILS.GET_INV_VAR(D.INVOICE_DISTRIBUTION_ID
, 'N'
, NVL(BASE_AMOUNT_VARIANCE
, D.AMOUNT_VARIANCE)
, NVL(D.BASE_QUANTITY_VARIANCE
, D.QUANTITY_VARIANCE))
, PO1.CODE_COMBINATION_ID
, 'AP'
, I.INVOICE_ID
, D.DISTRIBUTION_LINE_NUMBER
, D.INVOICE_DISTRIBUTION_ID
, NVL(PO1.ACCRUE_ON_RECEIPT_FLAG
, 'N')
, PO1.PO_LINE_ID
, 'N'
, NVL(D.BASE_AMOUNT
, D.AMOUNT)
, D.PROJECT_ID
, D.LINE_TYPE_LOOKUP_CODE
, D.PA_QUANTITY
, I.SOURCE
, NVL(G.SLA_LEDGER_CASH_BASIS_FLAG
, 'N')
, I.INVOICE_TYPE_LOOKUP_CODE
, D.HISTORICAL_FLAG
, D.PREPAY_AMOUNT_REMAINING )
, 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)
, I.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
, O.NAME
, I.INVOICE_ID
, I.VENDOR_ID
, P.PROJECT_ID
, T.TASK_ID
, O.ORGANIZATION_ID
, 'FINANCIAL_ELEMENTS'
, D.INVOICE_LINE_NUMBER
, D.INVOICE_DISTRIBUTION_ID
, NVL(G.SLA_LEDGER_CASH_BASIS_FLAG
, 'N')
, D.AWARD_ID AWARD_SET_ID
FROM GL_LEDGERS G
, AP_INVOICES_ALL I
, PO_VENDORS V
, HR_ORGANIZATION_UNITS O
, PA_EXPEND_TYP_SYS_LINKS ES
, PA_EXPENDITURE_TYPES ET
, PA_TASKS T
, PO_DISTRIBUTIONS_ALL PO1
, AP_INVOICE_DISTRIBUTIONS_ALL D
, PA_PROJECTS_ALL P
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 = PO1.PO_DISTRIBUTION_ID (+)
AND NVL(PO1.DISTRIBUTION_TYPE
, 'XXX') <> 'PREPAYMENT'
AND ( ( I.INVOICE_TYPE_LOOKUP_CODE <> 'PREPAYMENT' ) OR ( I.INVOICE_TYPE_LOOKUP_CODE = 'PREPAYMENT'
AND D.PO_DISTRIBUTION_ID IS NULL ) )
AND NVL(PO1.DESTINATION_TYPE_CODE
, 'EXPENSE') = 'EXPENSE'
AND D.PROJECT_ID = P.PROJECT_ID
AND D.TASK_ID = T.TASK_ID
AND D.EXPENDITURE_ORGANIZATION_ID = O.ORGANIZATION_ID
AND D.EXPENDITURE_TYPE = ES.EXPENDITURE_TYPE
AND ET.EXPENDITURE_TYPE = ES.EXPENDITURE_TYPE
AND G.LEDGER_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 D.LINE_TYPE_LOOKUP_CODE NOT IN ( 'REC_TAX'
, 'RETAINAGE')
AND ( ( D.PREPAY_DISTRIBUTION_ID IS NULL ) OR ( D.PREPAY_DISTRIBUTION_ID IS NOT NULL
AND EXISTS ( SELECT 1
FROM AP_INVOICE_DISTRIBUTIONS_ALL D2
WHERE D2.INVOICE_DISTRIBUTION_ID = D.PREPAY_DISTRIBUTION_ID
AND NVL(D2.HISTORICAL_FLAG
, 'N') = 'Y' )
AND D.PO_DISTRIBUTION_ID IS NULL ) )
AND ( ( NVL(PO1.ACCRUE_ON_RECEIPT_FLAG
, 'N') = 'N' ) OR ( NVL(PO1.ACCRUE_ON_RECEIPT_FLAG
, 'N') = 'Y'
AND D.LINE_TYPE_LOOKUP_CODE NOT IN ( 'ACCRUAL'
, 'ITEM'
, 'PREPAY'
, 'NONREC_TAX' ) ) )
AND NVL(D.REVERSAL_FLAG
, 'N') <> 'Y'

Columns