VIEW: APPS.PA_NL_TRANSFER_AP_INV_VIEW

Source

View Text - Preformatted

SELECT DIST.INVOICE_ID, 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), DIST.BASE_AMOUNT, DIST.EXCHANGE_RATE, DIST.EXCHANGE_RATE_TYPE, DIST.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, /* Bug 1471137 */ PO.org_id FROM ap_invoice_distributions_all DIST, po_distributions PO WHERE DIST.project_id > 0 AND DIST.po_distribution_id = PO.po_distribution_id(+) 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')

View Text - HTML Formatted

SELECT DIST.INVOICE_ID
, 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)
, DIST.BASE_AMOUNT
, DIST.EXCHANGE_RATE
, DIST.EXCHANGE_RATE_TYPE
, DIST.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
, /* BUG 1471137 */ PO.ORG_ID
FROM AP_INVOICE_DISTRIBUTIONS_ALL DIST
, PO_DISTRIBUTIONS PO
WHERE DIST.PROJECT_ID > 0
AND DIST.PO_DISTRIBUTION_ID = PO.PO_DISTRIBUTION_ID(+)
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')