View: PA_TRANSFER_AP_INVOICES_VIEW

Product:	PA - Projects
Description:	View of project-related invoice distributions in AP
Implementation/DBA Data:	APPS.PA_TRANSFER_AP_INVOICES_VIEW

View Text

SELECT DIST.INVOICE_ID
, DIST.INVOICE_DISTRIBUTION_ID
, DIST.INVOICE_LINE_NUMBER
, DIST.CORRECTED_INVOICE_DIST_ID
, DIST.CHARGE_APPLICABLE_TO_DIST_ID
, DIST.RELATED_ID
, DIST.PARENT_REVERSAL_ID
, DIST.CANCELLATION_FLAG
, DIST.AMOUNT_VARIANCE
, DIST.BASE_AMOUNT_VARIANCE
, DECODE(PO.ACCRUE_ON_RECEIPT_FLAG
, 'Y'
, PO.CODE_COMBINATION_ID
, DIST.DIST_CODE_COMBINATION_ID)
, DIST.LAST_UPDATE_DATE
, DIST.LAST_UPDATED_BY
, DIST.ACCOUNTING_DATE
, DIST.PERIOD_NAME
, DIST.SET_OF_BOOKS_ID
, DIST.AMOUNT
, DIST.DESCRIPTION
, DIST.TYPE_1099
, DIST.VAT_CODE
, DIST.POSTED_FLAG
, DIST.BATCH_ID
, DIST.REQ_DISTRIBUTION_ID
, DIST.QUANTITY_INVOICED
, DIST.UNIT_PRICE
, DIST.PRICE_ADJUSTMENT_FLAG
, DIST.MATCH_STATUS_FLAG
, DIST.ATTRIBUTE_CATEGORY
, DIST.ATTRIBUTE1
, DIST.ATTRIBUTE2
, DIST.ATTRIBUTE3
, DIST.ATTRIBUTE4
, DIST.ATTRIBUTE5
, DIST.PREPAY_AMOUNT_REMAINING
, DIST.EARLIEST_SETTLEMENT_DATE
, DIST.ASSETS_ADDITION_FLAG
, DIST.ASSETS_TRACKING_FLAG
, DIST.DISTRIBUTION_LINE_NUMBER
, DIST.LINE_TYPE_LOOKUP_CODE
, NVL(PO.PO_DISTRIBUTION_ID
, DIST.PO_DISTRIBUTION_ID)
, DECODE(DIST.LINE_TYPE_LOOKUP_CODE
, 'PREPAY'
, (SELECT AP_UTILITIES_PKG.AP_ROUND_CURRENCY(DIST.AMOUNT*INV.EXCHANGE_RATE
, ASP.BASE_CURRENCY_CODE)
FROM AP_INVOICES_ALL INV
, AP_INVOICE_DISTRIBUTIONS DIST2
, AP_SYSTEM_PARAMETERS ASP
WHERE DIST2.INVOICE_DISTRIBUTION_ID = DIST.PREPAY_DISTRIBUTION_ID
AND DIST2.INVOICE_ID = INV.INVOICE_ID)
, DIST.BASE_AMOUNT) BASE_AMOUNT
, DECODE(DIST.LINE_TYPE_LOOKUP_CODE
, 'PREPAY'
, (SELECT INV.EXCHANGE_RATE
FROM AP_INVOICES_ALL INV
, AP_INVOICE_DISTRIBUTIONS DIST2
WHERE DIST2.INVOICE_DISTRIBUTION_ID = DIST.PREPAY_DISTRIBUTION_ID
AND DIST2.INVOICE_ID = INV.INVOICE_ID)
, DIST.EXCHANGE_RATE) EXCHANGE_RATE
, DECODE(DIST.LINE_TYPE_LOOKUP_CODE
, 'PREPAY'
, (SELECT INV.EXCHANGE_RATE_TYPE
FROM AP_INVOICES_ALL INV
, AP_INVOICE_DISTRIBUTIONS DIST2
WHERE DIST2.INVOICE_DISTRIBUTION_ID = DIST.PREPAY_DISTRIBUTION_ID
AND DIST2.INVOICE_ID = INV.INVOICE_ID)
, DIST.EXCHANGE_RATE_TYPE) EXCHANGE_RATE_TYPE
, DECODE(DIST.LINE_TYPE_LOOKUP_CODE
, 'PREPAY'
, (SELECT INV.EXCHANGE_DATE
FROM AP_INVOICES_ALL INV
, AP_INVOICE_DISTRIBUTIONS DIST2
WHERE DIST2.INVOICE_DISTRIBUTION_ID = DIST.PREPAY_DISTRIBUTION_ID
AND DIST2.INVOICE_ID = INV.INVOICE_ID)
, DIST.EXCHANGE_DATE) EXCHANGE_DATE
, DIST.PA_ADDITION_FLAG
, DIST.JE_BATCH_ID
, DIST.POSTED_AMOUNT
, DIST.POSTED_BASE_AMOUNT
, DIST.ENCUMBERED_FLAG
, DIST.AMOUNT_ENCUMBERED
, DIST.QUANTITY_UNENCUMBERED
, DIST.AMOUNT_TO_POST
, DIST.BASE_AMOUNT_TO_POST
, DIST.ACCRUAL_POSTED_FLAG
, DIST.CASH_POSTED_FLAG
, DIST.LAST_UPDATE_LOGIN
, DIST.CREATION_DATE
, DIST.CREATED_BY
, DIST.CASH_JE_BATCH_ID
, DIST.REQUEST_ID
, DIST.PROGRAM_APPLICATION_ID
, DIST.PROGRAM_ID
, DIST.PROGRAM_UPDATE_DATE
, DIST.STAT_AMOUNT
, DIST.BASE_AMOUNT_ENCUMBERED
, DIST.ATTRIBUTE11
, DIST.ATTRIBUTE12
, DIST.ATTRIBUTE13
, DIST.ATTRIBUTE14
, DIST.ATTRIBUTE6
, DIST.ATTRIBUTE7
, DIST.ATTRIBUTE8
, DIST.ATTRIBUTE9
, DIST.ATTRIBUTE10
, DIST.ATTRIBUTE15
, DIST.ACCTS_PAY_CODE_COMBINATION_ID
, DIST.RATE_VAR_CODE_COMBINATION_ID
, DIST.PRICE_VAR_CODE_COMBINATION_ID
, DIST.EXCHANGE_RATE_VARIANCE
, DIST.INVOICE_PRICE_VARIANCE
, DIST.BASE_INVOICE_PRICE_VARIANCE
, DIST.REVERSAL_FLAG
, DIST.PARENT_INVOICE_ID
, DIST.INCOME_TAX_REGION
, DIST.FINAL_MATCH_FLAG
, DIST.USSGL_TRANSACTION_CODE
, DIST.USSGL_TRX_CODE_CONTEXT
, DIST.EXPENDITURE_ITEM_DATE
, DIST.EXPENDITURE_ORGANIZATION_ID
, DIST.EXPENDITURE_TYPE
, DIST.PA_QUANTITY
, DIST.PROJECT_ID
, DIST.TASK_ID
, DIST.PROJECT_ACCOUNTING_CONTEXT
, PO.DESTINATION_TYPE_CODE
, DIST.RECEIPT_CURRENCY_AMOUNT
, DIST.RECEIPT_CURRENCY_CODE
, DIST.RECEIPT_CONVERSION_RATE
, DIST.TAX_RECOVERABLE_FLAG
, PO.ACCRUE_ON_RECEIPT_FLAG
, DIST.JUSTIFICATION
, DIST.PREPAY_DISTRIBUTION_ID
, DIST.HISTORICAL_FLAG
FROM AP_INVOICE_DISTRIBUTIONS DIST
, PO_DISTRIBUTIONS PO
WHERE DIST.PROJECT_ID + 0 > 0
AND DIST.PO_DISTRIBUTION_ID = PO.PO_DISTRIBUTION_ID(+)
AND NVL(PO.DISTRIBUTION_TYPE
, 'XXX') <> 'PREPAYMENT'
AND (PA_NL_INSTALLED.IS_NL_INSTALLED = 'N' OR ( PA_NL_INSTALLED.IS_NL_INSTALLED = 'Y'
AND NOT EXISTS (SELECT 'X'
FROM MTL_SYSTEM_ITEMS SI
, PO_LINES_ALL POL
WHERE PO.PO_LINE_ID = POL.PO_LINE_ID
AND SI.INVENTORY_ITEM_ID = POL.ITEM_ID
AND SI.COMMS_NL_TRACKABLE_FLAG = 'Y') ) )

Columns