VIEW: APPS.PA_TRANSFER_AP_INVOICES_VIEW

Source

View Text - Preformatted

SELECT DIST.INVOICE_ID, /* REL12 AP Lines Uptake start */ 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, /* REL12 AP Lines Uptake end */ 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' AND si.organization_id = pol.org_id) AND NOT EXISTS (SELECT 'X' FROM ap_invoice_distributions apdist, po_distributions pod, mtl_system_items si, po_lines_all pol where apdist.INVOICE_DISTRIBUTION_ID= DIST.CHARGE_APPLICABLE_TO_DIST_ID and apdist.po_distribution_id = pod.po_distribution_id and pod.po_line_id = pol.po_line_id and si.inventory_item_id = pol.item_id AND si.comms_nl_trackable_flag = 'Y' AND si.organization_id = pol.org_id) ) )

View Text - HTML Formatted

SELECT DIST.INVOICE_ID
, /* REL12 AP LINES UPTAKE START */ 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
, /* REL12 AP LINES UPTAKE END */ 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'
AND SI.ORGANIZATION_ID = POL.ORG_ID)
AND NOT EXISTS (SELECT 'X'
FROM AP_INVOICE_DISTRIBUTIONS APDIST
, PO_DISTRIBUTIONS POD
, MTL_SYSTEM_ITEMS SI
, PO_LINES_ALL POL
WHERE APDIST.INVOICE_DISTRIBUTION_ID= DIST.CHARGE_APPLICABLE_TO_DIST_ID
AND APDIST.PO_DISTRIBUTION_ID = POD.PO_DISTRIBUTION_ID
AND POD.PO_LINE_ID = POL.PO_LINE_ID
AND SI.INVENTORY_ITEM_ID = POL.ITEM_ID
AND SI.COMMS_NL_TRACKABLE_FLAG = 'Y'
AND SI.ORGANIZATION_ID = POL.ORG_ID) ) )