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, 'N' SELF_ASSESSED_FLAG /*Self Assessed Tax Changes*/ 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) ) ) /*Self Assessed Tax Changes*/ UNION 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 , NULL VAT_CODE , DIST.POSTED_FLAG , DIST.BATCH_ID , NULL REQ_DISTRIBUTION_ID , DIST.QUANTITY_INVOICED , DIST.UNIT_PRICE , NULL PRICE_ADJUSTMENT_FLAG , DIST.MATCH_STATUS_FLAG , DIST.ATTRIBUTE_CATEGORY , DIST.ATTRIBUTE1 , DIST.ATTRIBUTE2 , DIST.ATTRIBUTE3 , DIST.ATTRIBUTE4 , DIST.ATTRIBUTE5 , DIST.PREPAY_AMOUNT_REMAINING , NULL EARLIEST_SETTLEMENT_DATE , DIST.ASSETS_ADDITION_FLAG , DIST.ASSETS_TRACKING_FLAG , DIST.DISTRIBUTION_LINE_NUMBER , DIST.LINE_TYPE_LOOKUP_CODE , DIST.PO_DISTRIBUTION_ID , DIST.BASE_AMOUNT , (select exchange_rate from ap_invoices_all apd where dist.invoice_id = apd.invoice_id) EXCHANGE_RATE , (select exchange_rate_type from ap_invoices_all apd where dist.invoice_id = apd.invoice_id ) EXCHANGE_RATE_TYPE , (select exchange_date from ap_invoices_all apd where dist.invoice_id = apd.invoice_id) EXCHANGE_DATE , DIST.PA_ADDITION_FLAG , NULL JE_BATCH_ID , NULL POSTED_AMOUNT , NULL POSTED_BASE_AMOUNT , DIST.ENCUMBERED_FLAG , NULL AMOUNT_ENCUMBERED , NULL QUANTITY_UNENCUMBERED , NULL AMOUNT_TO_POST , NULL BASE_AMOUNT_TO_POST , DIST.ACCRUAL_POSTED_FLAG , DIST.CASH_POSTED_FLAG , DIST.LAST_UPDATE_LOGIN , DIST.CREATION_DATE , DIST.CREATED_BY , NULL CASH_JE_BATCH_ID , DIST.REQUEST_ID , DIST.PROGRAM_APPLICATION_ID , DIST.PROGRAM_ID , DIST.PROGRAM_UPDATE_DATE , DIST.STAT_AMOUNT , NULL BASE_AMOUNT_ENCUMBERED , DIST.ATTRIBUTE11 , DIST.ATTRIBUTE12 , DIST.ATTRIBUTE13 , DIST.ATTRIBUTE14 , DIST.ATTRIBUTE6 , DIST.ATTRIBUTE7 , DIST.ATTRIBUTE8 , DIST.ATTRIBUTE9 , DIST.ATTRIBUTE10 , DIST.ATTRIBUTE15 , NULL ACCTS_PAY_CODE_COMBINATION_ID , NULL RATE_VAR_CODE_COMBINATION_ID , NULL PRICE_VAR_CODE_COMBINATION_ID , NULL EXCHANGE_RATE_VARIANCE , NULL INVOICE_PRICE_VARIANCE , NULL 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 , 'N' HISTORICAL_FLAG , DIST.SELF_ASSESSED_FLAG FROM AP_SELF_ASSESSED_TAX_DIST 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') ) )

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
, 'N' SELF_ASSESSED_FLAG /*SELF ASSESSED TAX CHANGES*/
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) ) ) /*SELF ASSESSED TAX CHANGES*/ UNION 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
, NULL VAT_CODE
, DIST.POSTED_FLAG
, DIST.BATCH_ID
, NULL REQ_DISTRIBUTION_ID
, DIST.QUANTITY_INVOICED
, DIST.UNIT_PRICE
, NULL PRICE_ADJUSTMENT_FLAG
, DIST.MATCH_STATUS_FLAG
, DIST.ATTRIBUTE_CATEGORY
, DIST.ATTRIBUTE1
, DIST.ATTRIBUTE2
, DIST.ATTRIBUTE3
, DIST.ATTRIBUTE4
, DIST.ATTRIBUTE5
, DIST.PREPAY_AMOUNT_REMAINING
, NULL EARLIEST_SETTLEMENT_DATE
, DIST.ASSETS_ADDITION_FLAG
, DIST.ASSETS_TRACKING_FLAG
, DIST.DISTRIBUTION_LINE_NUMBER
, DIST.LINE_TYPE_LOOKUP_CODE
, DIST.PO_DISTRIBUTION_ID
, DIST.BASE_AMOUNT
, (SELECT EXCHANGE_RATE
FROM AP_INVOICES_ALL APD
WHERE DIST.INVOICE_ID = APD.INVOICE_ID) EXCHANGE_RATE
, (SELECT EXCHANGE_RATE_TYPE
FROM AP_INVOICES_ALL APD
WHERE DIST.INVOICE_ID = APD.INVOICE_ID ) EXCHANGE_RATE_TYPE
, (SELECT EXCHANGE_DATE
FROM AP_INVOICES_ALL APD
WHERE DIST.INVOICE_ID = APD.INVOICE_ID) EXCHANGE_DATE
, DIST.PA_ADDITION_FLAG
, NULL JE_BATCH_ID
, NULL POSTED_AMOUNT
, NULL POSTED_BASE_AMOUNT
, DIST.ENCUMBERED_FLAG
, NULL AMOUNT_ENCUMBERED
, NULL QUANTITY_UNENCUMBERED
, NULL AMOUNT_TO_POST
, NULL BASE_AMOUNT_TO_POST
, DIST.ACCRUAL_POSTED_FLAG
, DIST.CASH_POSTED_FLAG
, DIST.LAST_UPDATE_LOGIN
, DIST.CREATION_DATE
, DIST.CREATED_BY
, NULL CASH_JE_BATCH_ID
, DIST.REQUEST_ID
, DIST.PROGRAM_APPLICATION_ID
, DIST.PROGRAM_ID
, DIST.PROGRAM_UPDATE_DATE
, DIST.STAT_AMOUNT
, NULL BASE_AMOUNT_ENCUMBERED
, DIST.ATTRIBUTE11
, DIST.ATTRIBUTE12
, DIST.ATTRIBUTE13
, DIST.ATTRIBUTE14
, DIST.ATTRIBUTE6
, DIST.ATTRIBUTE7
, DIST.ATTRIBUTE8
, DIST.ATTRIBUTE9
, DIST.ATTRIBUTE10
, DIST.ATTRIBUTE15
, NULL ACCTS_PAY_CODE_COMBINATION_ID
, NULL RATE_VAR_CODE_COMBINATION_ID
, NULL PRICE_VAR_CODE_COMBINATION_ID
, NULL EXCHANGE_RATE_VARIANCE
, NULL INVOICE_PRICE_VARIANCE
, NULL 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
, 'N' HISTORICAL_FLAG
, DIST.SELF_ASSESSED_FLAG
FROM AP_SELF_ASSESSED_TAX_DIST 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') ) )