eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

[Home] [Help]

View: AP_PREPAYAPP_EXTRACT_DTLS_BC_V

Product:	AP - Payables
Description:
Implementation/DBA Data:	APPS.AP_PREPAYAPP_EXTRACT_DTLS_BC_V

View Text

SELECT XE.EVENT_ID EVENT_ID
, APAD.PREPAY_APP_DIST_ID LINE_NUMBER
, 'AP_PREPAY' DISTRIBUTION_LINK_TYPE
, AIL.DEFERRED_ACCTG_FLAG DEFERRED_OPTION
, AIL.DEF_ACCTG_START_DATE DEFERRED_START_DATE
, AIL.DEF_ACCTG_END_DATE DEFERRED_END_DATE
, AIL.DEF_ACCTG_NUMBER_OF_PERIODS DEFERRED_NUMBER_OF_PERIODS
, AIL.DEF_ACCTG_PERIOD_TYPE DEFERRED_PERIOD_TYPE
, APAD.PREPAY_APP_DIST_ID APAD_DISTRIBUTION_IDENTIFIER
, DECODE( APAD.REVERSED_PREPAY_APP_DIST_ID
, NULL
, 'N'
, 'Y') DIST_ACCOUNT_REVERSAL_OPTION
, APAD.PREPAY_DIST_LOOKUP_CODE APAD_DIST_LOOKUP_CODE
, APPH.INVOICE_ADJUSTMENT_EVENT_ID APAD_INVOICE_ADJ_EVENT_ID
, APPH.PREPAY_INVOICE_ID APPH_PREPAY_INVOICE_ID
, APAD.AMOUNT APAD_AMOUNT
, AI.PAYMENT_CURRENCY_CODE PREPAY_PAY_CURRENCY_CODE
, (SELECT MAX(BANK_CURRENCY_CODE) BANK_CURRENCY_CODE
FROM AP_PAYMENT_HIST_DISTS APHD
, AP_PAYMENT_HISTORY_ALL APH
WHERE APH.PAYMENT_HISTORY_ID = APHD.PAYMENT_HISTORY_ID
AND APHD.PAY_DIST_LOOKUP_CODE = 'CASH'
AND APH.TRANSACTION_TYPE = 'PAYMENT CLEARING'
AND AID.PREPAY_DISTRIBUTION_ID = APHD.INVOICE_DISTRIBUTION_ID(+) GROUP BY APHD.INVOICE_DISTRIBUTION_ID ) PREPAY_CLEAR_CURRENCY_CODE
, AIL.LINE_NUMBER AIL_LINE_NUMBER
, AIL.LINE_TYPE_LOOKUP_CODE AIL_LINE_TYPE_LOOKUP_CODE
, AIL.ACCOUNT_SEGMENT AIL_ACCOUNT_SEGMENT
, AIL.BALANCING_SEGMENT AIL_BALANCING_SEGMENT
, AIL.COST_CENTER_SEGMENT AIL_COST_CENTER_SEGMENT
, AIL.OVERLAY_DIST_CODE_CONCAT AIL_OVERLAY_DIST_CODE_CONCAT
, AIL.DEFAULT_DIST_CCID AIL_DEFAULT_DIST_CCID
, AIL.ACCOUNTING_DATE AIL_ACCOUNTING_DATE
, AIL.PERIOD_NAME AIL_PERIOD_NAME
, AIL.AMOUNT AIL_AMOUNT
, AIL.ROUNDING_AMT AIL_ROUNDING_AMT
, AIL.ATTRIBUTE_CATEGORY AIL_ATTRIBUTE_CATEGORY
, AIL.ATTRIBUTE1 AIL_ATTRIBUTE1
, AIL.ATTRIBUTE2 AIL_ATTRIBUTE2
, AIL.ATTRIBUTE3 AIL_ATTRIBUTE3
, AIL.ATTRIBUTE4 AIL_ATTRIBUTE4
, AIL.ATTRIBUTE5 AIL_ATTRIBUTE5
, AIL.ATTRIBUTE6 AIL_ATTRIBUTE6
, AIL.ATTRIBUTE7 AIL_ATTRIBUTE7
, AIL.ATTRIBUTE8 AIL_ATTRIBUTE8
, AIL.ATTRIBUTE9 AIL_ATTRIBUTE9
, AIL.ATTRIBUTE10 AIL_ATTRIBUTE10
, AIL.ATTRIBUTE11 AIL_ATTRIBUTE11
, AIL.ATTRIBUTE12 AIL_ATTRIBUTE12
, AIL.ATTRIBUTE13 AIL_ATTRIBUTE13
, AIL.ATTRIBUTE14 AIL_ATTRIBUTE14
, AIL.ATTRIBUTE15 AIL_ATTRIBUTE15
, AIL.GLOBAL_ATTRIBUTE_CATEGORY AIL_GLOBAL_ATTRIBUTE_CATEGORY
, AIL.GLOBAL_ATTRIBUTE1 AIL_GLOBAL_ATTRIBUTE1
, AIL.GLOBAL_ATTRIBUTE2 AIL_GLOBAL_ATTRIBUTE2
, AIL.GLOBAL_ATTRIBUTE3 AIL_GLOBAL_ATTRIBUTE3
, AIL.GLOBAL_ATTRIBUTE4 AIL_GLOBAL_ATTRIBUTE4
, AIL.GLOBAL_ATTRIBUTE5 AIL_GLOBAL_ATTRIBUTE5
, AIL.GLOBAL_ATTRIBUTE6 AIL_GLOBAL_ATTRIBUTE6
, AIL.GLOBAL_ATTRIBUTE7 AIL_GLOBAL_ATTRIBUTE7
, AIL.GLOBAL_ATTRIBUTE8 AIL_GLOBAL_ATTRIBUTE8
, AIL.GLOBAL_ATTRIBUTE9 AIL_GLOBAL_ATTRIBUTE9
, AIL.GLOBAL_ATTRIBUTE10 AIL_GLOBAL_ATTRIBUTE10
, AIL.GLOBAL_ATTRIBUTE11 AIL_GLOBAL_ATTRIBUTE11
, AIL.GLOBAL_ATTRIBUTE12 AIL_GLOBAL_ATTRIBUTE12
, AIL.GLOBAL_ATTRIBUTE13 AIL_GLOBAL_ATTRIBUTE13
, AIL.GLOBAL_ATTRIBUTE14 AIL_GLOBAL_ATTRIBUTE14
, AIL.GLOBAL_ATTRIBUTE15 AIL_GLOBAL_ATTRIBUTE15
, AIL.GLOBAL_ATTRIBUTE16 AIL_GLOBAL_ATTRIBUTE16
, AIL.GLOBAL_ATTRIBUTE17 AIL_GLOBAL_ATTRIBUTE17
, AIL.GLOBAL_ATTRIBUTE18 AIL_GLOBAL_ATTRIBUTE18
, AIL.GLOBAL_ATTRIBUTE19 AIL_GLOBAL_ATTRIBUTE19
, AIL.GLOBAL_ATTRIBUTE20 AIL_GLOBAL_ATTRIBUTE20
, AIL.SUMMARY_TAX_LINE_ID SUMMARY_TAX_LINE_ID
, AID.ACCOUNTING_DATE AID_ACCOUNTING_DATE
, AID.DISTRIBUTION_LINE_NUMBER AID_DISTRIBUTION_LINE_NUMBER
, AID.LINE_TYPE_LOOKUP_CODE AID_LINE_TYPE_LOOKUP_CODE
, AID.PERIOD_NAME AID_PERIOD_NAME
, AID.ACCTS_PAY_CODE_COMBINATION_ID AID_ACCTS_PAY_CCID
, AID.DIST_CODE_COMBINATION_ID AID_DIST_CCID
, AID.DESCRIPTION AID_DESCRIPTION
, AID.EXCHANGE_RATE_VARIANCE AID_EXCHANGE_RATE_VARIANCE
, AID.INVOICE_PRICE_VARIANCE AID_INVOICE_PRICE_VARIANCE
, AID.RATE_VAR_CODE_COMBINATION_ID AID_RATE_VAR_CCID
, AID.REVERSAL_FLAG AID_REVERSAL_FLAG
, AID.AMOUNT_ENCUMBERED AID_AMOUNT_ENCUMBERED
, AID.ENCUMBERED_FLAG AID_ENCUMBERED_FLAG
, AID.EXCHANGE_DATE AID_EXCHANGE_DATE
, AID.EXCHANGE_RATE AID_EXCHANGE_RATE
, AID.EXCHANGE_RATE_TYPE AID_EXCHANGE_RATE_TYPE
, AID.STAT_AMOUNT AID_STAT_AMOUNT
, AID.ATTRIBUTE1 AID_ATTRIBUTE1
, AID.ATTRIBUTE10 AID_ATTRIBUTE10
, AID.ATTRIBUTE11 AID_ATTRIBUTE11
, AID.ATTRIBUTE12 AID_ATTRIBUTE12
, AID.ATTRIBUTE13 AID_ATTRIBUTE13
, AID.ATTRIBUTE14 AID_ATTRIBUTE14
, AID.ATTRIBUTE15 AID_ATTRIBUTE15
, AID.ATTRIBUTE2 AID_ATTRIBUTE2
, AID.ATTRIBUTE3 AID_ATTRIBUTE3
, AID.ATTRIBUTE4 AID_ATTRIBUTE4
, AID.ATTRIBUTE5 AID_ATTRIBUTE5
, AID.ATTRIBUTE6 AID_ATTRIBUTE6
, AID.ATTRIBUTE7 AID_ATTRIBUTE7
, AID.ATTRIBUTE8 AID_ATTRIBUTE8
, AID.ATTRIBUTE9 AID_ATTRIBUTE9
, AID.ATTRIBUTE_CATEGORY AID_ATTRIBUTE_CATEGORY
, AID.PARENT_INVOICE_ID AID_PARENT_INVOICE_ID
, AID.GLOBAL_ATTRIBUTE_CATEGORY AID_GLOBAL_ATTRIBUTE_CAT
, AID.GLOBAL_ATTRIBUTE1 AID_GLOBAL_ATTRIBUTE1
, AID.GLOBAL_ATTRIBUTE2 AID_GLOBAL_ATTRIBUTE2
, AID.GLOBAL_ATTRIBUTE3 AID_GLOBAL_ATTRIBUTE3
, AID.GLOBAL_ATTRIBUTE4 AID_GLOBAL_ATTRIBUTE4
, AID.GLOBAL_ATTRIBUTE5 AID_GLOBAL_ATTRIBUTE5
, AID.GLOBAL_ATTRIBUTE6 AID_GLOBAL_ATTRIBUTE6
, AID.GLOBAL_ATTRIBUTE7 AID_GLOBAL_ATTRIBUTE7
, AID.GLOBAL_ATTRIBUTE8 AID_GLOBAL_ATTRIBUTE8
, AID.GLOBAL_ATTRIBUTE9 AID_GLOBAL_ATTRIBUTE9
, AID.GLOBAL_ATTRIBUTE10 AID_GLOBAL_ATTRIBUTE10
, AID.GLOBAL_ATTRIBUTE11 AID_GLOBAL_ATTRIBUTE11
, AID.GLOBAL_ATTRIBUTE12 AID_GLOBAL_ATTRIBUTE12
, AID.GLOBAL_ATTRIBUTE13 AID_GLOBAL_ATTRIBUTE13
, AID.GLOBAL_ATTRIBUTE14 AID_GLOBAL_ATTRIBUTE14
, AID.GLOBAL_ATTRIBUTE15 AID_GLOBAL_ATTRIBUTE15
, AID.GLOBAL_ATTRIBUTE16 AID_GLOBAL_ATTRIBUTE16
, AID.GLOBAL_ATTRIBUTE17 AID_GLOBAL_ATTRIBUTE17
, AID.GLOBAL_ATTRIBUTE18 AID_GLOBAL_ATTRIBUTE18
, AID.GLOBAL_ATTRIBUTE19 AID_GLOBAL_ATTRIBUTE19
, AID.GLOBAL_ATTRIBUTE20 AID_GLOBAL_ATTRIBUTE20
, AID.MERCHANT_DOCUMENT_NUMBER AID_MERCHANT_DOCUMENT_NUMBER
, AID.MERCHANT_NAME AID_MERCHANT_NAME
, AID.PREPAY_DISTRIBUTION_ID AID_PREPAY_DISTRIBUTION_ID
, AID.INVOICE_INCLUDES_PREPAY_FLAG AID_INV_INCLUDES_PREPAY_FLAG
, AID.INVOICE_LINE_NUMBER AID_INVOICE_LINE_NUMBER
, AID.ROUNDING_AMT AID_ROUNDING_AMOUNT
, AID.AMOUNT_AT_PREPAY_PAY_XRATE AID_AMT_AT_PREPAY_PAY_XRATE
, AID.PREPAY_TAX_PARENT_ID AID_PREPAY_TAX_PARENT_ID
, APAD.REVERSED_PREPAY_APP_DIST_ID REVERSED_PREPAY_APP_DIST_ID
, NVL(AID.BASE_AMOUNT
, AID.AMOUNT) INV_DIST_BASE_AMOUNT
, AID.AMOUNT INV_ENTERED_AMOUNT
, AID.INVOICE_DISTRIBUTION_ID AID_INVOICE_DIST_ID
, AID.DETAIL_TAX_DIST_ID AID_DETAIL_TAX_DIST_ID
, APAD.PREPAY_EXCHANGE_DATE APAD_PREPAY_XDATE
, APAD.PREPAY_PAY_EXCHANGE_DATE APAD_PREPAY_PAY_XDATE
, APAD.PREPAY_EXCHANGE_RATE APAD_PREPAY_XRATE
, APAD.PREPAY_PAY_EXCHANGE_RATE APAD_PREPAY_PAY_XRATE
, APAD.PREPAY_EXCHANGE_RATE_TYPE APAD_PREPAY_XRATE_TYPE
, APAD.PREPAY_PAY_EXCHANGE_RATE_TYPE APAD_PREPAY_PAY_XRATE_TYPE
, APAD.PREPAY_CLR_EXCHANGE_RATE APAD_PREPAY_CLR_XRATE
, APAD.PREPAY_CLR_EXCHANGE_RATE_TYPE APAD_PREPAY_CLR_XRATE_TYPE
, APAD.PREPAY_CLR_EXCHANGE_DATE APAD_PREPAY_CLR_XDATE
, NVL(APAD.BASE_AMT_AT_PREPAY_CLR_XRATE
, 0) BASE_AMT_AT_PP_CLR_XRATE
, NVL(APAD.BASE_AMT_AT_PREPAY_XRATE
, 0) BASE_AMT_AT_PP_XRATE
, NVL(APAD.BASE_AMT_AT_PREPAY_PAY_XRATE
, 0) BASE_AMT_AT_PP_PAY_XRATE
, NVL(APAD.BASE_AMOUNT
, APAD.AMOUNT) APAD_BASE_AMOUNT
, NVL(APAD.ROUNDING_AMT
, 0) APAD_ROUNDING_AMOUNT
, (NVL(APAD.BASE_AMOUNT
, APAD.AMOUNT) - NVL(APAD.ROUNDING_AMT
, 0)) APAD_BASE_AMT_NO_ROUND
, (NVL(APAD.BASE_AMOUNT
, APAD.AMOUNT) - APAD.BASE_AMT_AT_PREPAY_XRATE) APAD_BASE_AMOUNT_DIFF
, DECODE(APPH.GAIN_LOSS_INDICATOR
, 'G'
, 'GAIN'
, 'L'
, 'LOSS'
, 'NONE') INV_PREPMT_GAIN_LOSS_INDICATOR
, RECP_INV_DISTS.PO_DISTRIBUTION_ID PO_DISTRIBUTION_ID
, AID.PO_DISTRIBUTION_ID AID_PO_DISTRIBUTION_ID /*BUG 13575501*/
, 200 BUS_FLOW_AP_APP_ID
, 'AP_INV_DIST' BUS_FLOW_INV_DIST_TYPE
, 'AP_INVOICES' BUS_FLOW_INV_ENTITY_CODE
, AID.INVOICE_DISTRIBUTION_ID BUS_FLOW_INV_DIST_ID
, AID.INVOICE_ID BUS_FLOW_INV_ID
, 201 BUS_FLOW_PO_APP_ID
, 'PO_DISTRIBUTIONS_ALL' BUS_FLOW_PO_DIST_TYPE
, DECODE (AIL.PO_RELEASE_ID
, NULL
, 'PURCHASE_ORDER'
, 'RELEASE') BUS_FLOW_PO_ENTITY_CODE
, DECODE (AIL.PO_RELEASE_ID
, NULL
, AIL.PO_HEADER_ID
, AIL.PO_RELEASE_ID) BUS_FLOW_PO_DOC_ID
, 'AP_INVOICES' BF_ACCRUAL_PREPAY_ENTITY_CODE
, 'AP_PAYMENTS' BF_CASH_ALWAYS_PREPAY_ENTITY
, DECODE (AIL.PO_RELEASE_ID
, NULL
, 'PURCHASE_ORDER'
, 'RELEASE') BF_CASHCLEARONLY_PREPAY_ENTITY
, 'AP_INV_DIST' BF_ACCRUAL_PREPAY_DIST_TYPE
, 'AP_PMT_DIST' BF_CASH_ALWAYS_PP_DIST_TYPE
, 'PO_DIST' BF_CASHCLEARONLY_PP_DIST_TYPE
, APAD.INVOICE_DISTRIBUTION_ID BF_PREPAY_REC_DIST_ID
, APPH.INVOICE_ID BF_PREPAY_REC_INV
, AID.PREPAY_DISTRIBUTION_ID BF_ACCRUAL_PP_DIST_ID
, APPH.PREPAY_INVOICE_ID BF_ACCRUAL_PP_INV
, (SELECT MAX(APH.CHECK_ID)
FROM AP_PAYMENT_HIST_DISTS APHD
, AP_PAYMENT_HISTORY_ALL APH
WHERE APH.PAYMENT_HISTORY_ID = APHD.PAYMENT_HISTORY_ID
AND APHD.PAY_DIST_LOOKUP_CODE = 'CASH'
AND APH.TRANSACTION_TYPE = 'PAYMENT CREATED'
AND AID.PREPAY_DISTRIBUTION_ID = APHD.INVOICE_DISTRIBUTION_ID(+) GROUP BY APHD.INVOICE_DISTRIBUTION_ID) BF_CASHALWAYS_PP_PMT_ID
, (SELECT MAX(APHD.PAYMENT_HIST_DIST_ID)
FROM AP_PAYMENT_HIST_DISTS APHD
, AP_PAYMENT_HISTORY_ALL APH
WHERE APH.PAYMENT_HISTORY_ID = APHD.PAYMENT_HISTORY_ID
AND APHD.PAY_DIST_LOOKUP_CODE = 'CASH'
AND APH.TRANSACTION_TYPE = 'PAYMENT CREATED'
AND AID.PREPAY_DISTRIBUTION_ID = APHD.INVOICE_DISTRIBUTION_ID(+) GROUP BY APHD.INVOICE_DISTRIBUTION_ID) BF_CASHALWAYS_PP_PMT_DIST_ID
, (SELECT MAX(APHD.PAYMENT_HIST_DIST_ID)
FROM AP_PAYMENT_HIST_DISTS APHD
, AP_PAYMENT_HISTORY_ALL APH
WHERE APH.PAYMENT_HISTORY_ID = APHD.PAYMENT_HISTORY_ID
AND APHD.PAY_DIST_LOOKUP_CODE = 'CASH'
AND APH.TRANSACTION_TYPE = 'PAYMENT CLEARING'
AND AID.PREPAY_DISTRIBUTION_ID = APHD.INVOICE_DISTRIBUTION_ID(+) GROUP BY APHD.INVOICE_DISTRIBUTION_ID ) BF_CASHCLEAR_PP_CLEAR_DIST_ID
, AID.PROJECT_ID AID_PROJECT_ID
, AID.TASK_ID AID_TASK_ID
, AID.EXPENDITURE_TYPE AID_EXPENDITURE_TYPE
, AID.EXPENDITURE_ORGANIZATION_ID AID_EXPENDITURE_ORG_ID
, AID.AWARD_ID AID_AWARD_ID
, AID.AMOUNT AID_AMOUNT
, RECP_INV_LINES.RETAINED_INVOICE_ID BF_RET_INV_ASSOC_TO_PP_RECIP
, RECP_INV_DISTS.RETAINED_INVOICE_DIST_ID BF_RET_DIST_ASSOC_TO_PP_RECIP
, AI.INVOICE_TYPE_LOOKUP_CODE AI_RECIPIENT_INV_TYPE
, RECP_INV_DISTS.LINE_TYPE_LOOKUP_CODE RECP_INV_DIST_TYPE
, APAD.PREPAY_APP_DISTRIBUTION_ID APAD_PREPAY_APP_DIST_ID
, RECP_INV_DISTS.DIST_CODE_COMBINATION_ID RECP_INV_DIST_CCID
, RECP_INV_RETAINAGE_DISTS.DIST_CODE_COMBINATION_ID AID_RET_RELATED_DIST_CCID
, RECP_INV_DISTS.RELATED_ID VARIANCE_PARENT_DIST_ID
, APAD.QUANTITY_VARIANCE APAD_QUANTITY_VARIANCE
, APAD.INVOICE_BASE_QTY_VARIANCE APAD_INV_BASE_QTY_VARIANCE
, APAD.AMOUNT_VARIANCE APAD_AMOUNT_VARIANCE
, APAD.INVOICE_BASE_AMT_VARIANCE APAD_INV_BASE_AMT_VARIANCE
, RECP_INV_DISTS.DETAIL_TAX_DIST_ID RECP_DETAIL_TAX_DIST_ID
, POD.PO_DISTRIBUTION_ID POD_PO_DISTRIBUTION_ID
, POD.ENCUMBERED_FLAG POD_ENCUMBERED_FLAG
, POD.ENCUMBERED_AMOUNT POD_ENCUMBERED_AMOUNT
, POD.GL_ENCUMBERED_DATE POD_GL_ENCUMBERED_DATE
, POD.GL_ENCUMBERED_PERIOD_NAME POD_GL_ENCUMBERED_PERIOD_NAME
, POD.ATTRIBUTE_CATEGORY POD_ATTRIBUTE_CATEGORY
, POD.ATTRIBUTE1 POD_ATTRIBUTE1
, POD.ATTRIBUTE2 POD_ATTRIBUTE2
, POD.ATTRIBUTE3 POD_ATTRIBUTE3
, POD.ATTRIBUTE4 POD_ATTRIBUTE4
, POD.ATTRIBUTE5 POD_ATTRIBUTE5
, POD.ATTRIBUTE6 POD_ATTRIBUTE6
, POD.ATTRIBUTE7 POD_ATTRIBUTE7
, POD.ATTRIBUTE8 POD_ATTRIBUTE8
, POD.ATTRIBUTE9 POD_ATTRIBUTE9
, POD.ATTRIBUTE10 POD_ATTRIBUTE10
, POD.ATTRIBUTE11 POD_ATTRIBUTE11
, POD.ATTRIBUTE12 POD_ATTRIBUTE12
, POD.ATTRIBUTE13 POD_ATTRIBUTE13
, POD.ATTRIBUTE14 POD_ATTRIBUTE14
, POD.ATTRIBUTE15 POD_ATTRIBUTE15
, POD.DISTRIBUTION_NUM POD_DISTRIBUTION_NUM
, POD.ACCRUE_ON_RECEIPT_FLAG POD_ACCRUE_ON_RECEIPT_FLAG
, POD.CODE_COMBINATION_ID POD_CCID
, POD.VARIANCE_ACCOUNT_ID PO_VARIANCE_ACCOUNT
, POD.DESTINATION_TYPE_CODE RELATED_INV_DIST_DEST_TYPE
, POD.PO_HEADER_ID POD_PO_HEADER_ID
, POD.PO_LINE_ID POD_PO_LINE_ID
, POD.LINE_LOCATION_ID POD_LINE_LOCATION_ID
, CASE WHEN (FSP.REQ_ENCUMBRANCE_FLAG ='Y' OR FSP.PURCH_ENCUMBRANCE_FLAG = 'Y') THEN PSA_BC_XLA_PUB.GET_SLA_NOTUPGRADED_FLAG (DECODE(NVL(AID.PO_DISTRIBUTION_ID
, -99)
, -99
, 200
, 201)
, DECODE(NVL(AID.PO_DISTRIBUTION_ID
, -99)
, -99
, 'AP_INVOICES'
, DECODE (AIL.PO_RELEASE_ID
, NULL
, 'PURCHASE_ORDER'
, 'RELEASE'))
, DECODE(NVL(AID.PO_DISTRIBUTION_ID
, -99)
, -99
, DECODE(APAD.REVERSED_PREPAY_APP_DIST_ID
, NULL
, APPH.PREPAY_INVOICE_ID
, AID.INVOICE_ID)
, DECODE (AIL.PO_RELEASE_ID
, NULL
, AIL.PO_HEADER_ID
, AIL.PO_RELEASE_ID))
, DECODE(NVL(AID.PO_DISTRIBUTION_ID
, -99)
, -99
, 'AP_INV_DIST'
, 'PO_DISTRIBUTIONS_ALL')
, DECODE(NVL(AID.PO_DISTRIBUTION_ID
, -99)
, -99
, DECODE( APAD.REVERSED_PREPAY_APP_DIST_ID
, NULL
, AID.PREPAY_DISTRIBUTION_ID
, AID.PARENT_REVERSAL_ID)
, AID.PO_DISTRIBUTION_ID) ) ELSE 'N' END "UPG_AP_ENCUM_OPTION"
, DECODE(NVL(AID.PO_DISTRIBUTION_ID
, -99)
, -99
, NULL
, 1001) UPG_CR_ENC_TYPE_ID
, DECODE(NVL(AID.PO_DISTRIBUTION_ID
, -99)
, -99
, NULL
, POD.BUDGET_ACCOUNT_ID) UPG_ENC_CR_CCID
, DECODE(NVL(AID.PO_DISTRIBUTION_ID
, -99)
, -99
, NULL
, NVL(APAD.BASE_AMOUNT
, APAD.AMOUNT) ) UPG_ENC_CR_BASE_AMT
, DECODE(NVL(AID.PO_DISTRIBUTION_ID
, -99)
, -99
, NULL
, NVL(APAD.AMOUNT
, 0) ) UPG_ENC_CR_AMT
, GLET.ENCUMBRANCE_TYPE_ID UPG_DR_ENC_TYPE_ID
, AID.DIST_CODE_COMBINATION_ID UPG_ENC_DR_CCID
, NVL(APAD.BASE_AMOUNT
, APAD.AMOUNT) UPG_ENC_DR_BASE_AMT
, NVL(APAD.AMOUNT
, 0) UPG_ENC_DR_AMT
, 'PREPAID_EXPENSE' UPG_ENC_DR_ACCT_CLASS
, 'RFE' UPG_ENC_CR_ACCT_CLASS
, 'Y' OVERRIDE_ACCTD_AMT_FLAG
FROM XLA_EVENTS_GT XE
, AP_PREPAY_HISTORY_ALL APPH
, AP_PREPAY_APP_DISTS APAD
, AP_INVOICE_LINES_ALL AIL
, AP_INVOICE_DISTRIBUTIONS_ALL AID
, GL_ENCUMBRANCE_TYPES GLET
, AP_INVOICES_ALL AI
, /* (SELECT MAX(APHD.PAYMENT_HIST_DIST_ID) PP_PMT_HIST_DIST_ID
, APHD.INVOICE_DISTRIBUTION_ID INVOICE_DISTRIBUTION_ID
, MAX(APH.CHECK_ID) CHECK_ID
FROM AP_PAYMENT_HIST_DISTS APHD
, AP_PAYMENT_HISTORY_ALL APH
WHERE APH.PAYMENT_HISTORY_ID = APHD.PAYMENT_HISTORY_ID
AND APHD.PAY_DIST_LOOKUP_CODE = 'CASH'
AND APH.TRANSACTION_TYPE = 'PAYMENT CREATED' GROUP BY APHD.INVOICE_DISTRIBUTION_ID ) PMT_DISTS
, (SELECT MAX(APHD.PAYMENT_HIST_DIST_ID) PP_CLEAR_HIST_DIST_ID
, APHD.INVOICE_DISTRIBUTION_ID INVOICE_DISTRIBUTION_ID
, MAX(BANK_CURRENCY_CODE) BANK_CURRENCY_CODE
FROM AP_PAYMENT_HIST_DISTS APHD
, AP_PAYMENT_HISTORY_ALL APH
WHERE APH.PAYMENT_HISTORY_ID = APHD.PAYMENT_HISTORY_ID
AND APHD.PAY_DIST_LOOKUP_CODE = 'CASH'
AND APH.TRANSACTION_TYPE = 'PAYMENT CLEARING' GROUP BY APHD.INVOICE_DISTRIBUTION_ID ) CLEAR_DISTS
, */ AP_INVOICE_LINES_ALL RECP_INV_LINES
, AP_INVOICE_DISTRIBUTIONS_ALL RECP_INV_DISTS
, AP_INVOICE_DISTRIBUTIONS_ALL RECP_INV_RETAINAGE_DISTS
, PO_DISTRIBUTIONS_ALL POD
, FINANCIALS_SYSTEM_PARAMS_ALL FSP
WHERE XE.EVENT_ID = APPH.BC_EVENT_ID
AND AID.BC_EVENT_ID = APPH.BC_EVENT_ID
AND AI.INVOICE_ID = AID.INVOICE_ID
AND APAD.PREPAY_APP_DISTRIBUTION_ID = AID.INVOICE_DISTRIBUTION_ID
AND APPH.PREPAY_HISTORY_ID = APAD.PREPAY_HISTORY_ID
AND AID.INVOICE_LINE_NUMBER = AIL.LINE_NUMBER
AND AID.INVOICE_ID = AIL.INVOICE_ID
AND RECP_INV_LINES.INVOICE_ID = AID.INVOICE_ID
AND RECP_INV_LINES.INVOICE_ID = RECP_INV_DISTS.INVOICE_ID
AND RECP_INV_LINES.LINE_NUMBER = RECP_INV_DISTS.INVOICE_LINE_NUMBER
AND RECP_INV_DISTS.LINE_TYPE_LOOKUP_CODE <> 'PREPAY'
AND RECP_INV_DISTS.RELATED_RETAINAGE_DIST_ID = RECP_INV_RETAINAGE_DISTS.INVOICE_DISTRIBUTION_ID(+)
AND APAD.INVOICE_DISTRIBUTION_ID = RECP_INV_DISTS.INVOICE_DISTRIBUTION_ID /*
AND AID.PREPAY_DISTRIBUTION_ID = PMT_DISTS.INVOICE_DISTRIBUTION_ID(+)
AND AID.PREPAY_DISTRIBUTION_ID = CLEAR_DISTS.INVOICE_DISTRIBUTION_ID(+) */
AND RECP_INV_DISTS.PO_DISTRIBUTION_ID = POD.PO_DISTRIBUTION_ID(+)
AND APPH.ORG_ID = FSP.ORG_ID
AND GLET.ENCUMBRANCE_TYPE_KEY='INVOICES'