FND Design Data [Home] [Help]

View: AP_PREPAYAPP_EXTRACT_DETAILS_V

Product: AP - Payables
Description:
Implementation/DBA Data: ViewAPPS.AP_PREPAYAPP_EXTRACT_DETAILS_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 /*BUG10074972*/
, CASE WHEN APAD.REVERSED_PREPAY_APP_DIST_ID IS NULL THEN 'N' WHEN APAD.REVERSED_PREPAY_APP_DIST_ID IS NOT NULL THEN CASE WHEN((FSP.REQ_ENCUMBRANCE_FLAG = 'Y' OR FSP.PURCH_ENCUMBRANCE_FLAG = 'Y')
AND NVL(AID.ENCUMBERED_FLAG
, 'N')='Y') THEN /*BUG11720045*/ DECODE(PSA_BC_XLA_PUB.GET_SLA_NOTUPGRADED_FLAG (201
, 'AP_INVOICES'
, DECODE(NVL(APAD.REVERSED_PREPAY_APP_DIST_ID
, -99)
, -99
, APPH.PREPAY_INVOICE_ID
, AID.INVOICE_ID)
, 'AP_INV_DIST'
, DECODE(NVL(APAD.REVERSED_PREPAY_APP_DIST_ID
, -99)
, -99
, AID.PREPAY_DISTRIBUTION_ID
, AID.PARENT_REVERSAL_ID) )
, 'Y'
, 'N'
, 'N'
, 'Y'
, NULL
, 'Y' ) ELSE 'Y' END ELSE 'N' END 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 /*BUG6970538*/
, (SELECT MAX(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
, DECODE(XE.EVENT_TYPE_CODE
, 'PREPAYMENT APPLICATION ADJ'
, 'N'
, 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 /* BUG 7414999 */
, 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') */
, 'AP_PAYMENTS' BF_CASHCLEARONLY_PREPAY_ENTITY /*BUG9411353*/
, 'AP_INV_DIST' BF_ACCRUAL_PREPAY_DIST_TYPE
, 'AP_PMT_DIST' BF_CASH_ALWAYS_PP_DIST_TYPE
, 'AP_PMT_DIST' /*'PO_DIST'*/ BF_CASHCLEARONLY_PP_DIST_TYPE /*BUG9411353*/
, DECODE(APAD.PREPAY_DIST_LOOKUP_CODE
, 'AWT'
, AWT_RELATED_IDS.INVOICE_DISTRIBUTION_ID
, APAD.INVOICE_DISTRIBUTION_ID) BF_PREPAY_REC_DIST_ID
, RECP_INV_DISTS.DESCRIPTION RECP_INV_DIST_DESC /*BUG 13012493*/
, APPH.INVOICE_ID BF_PREPAY_REC_INV
, AID.PREPAY_DISTRIBUTION_ID BF_ACCRUAL_PP_DIST_ID
, APPH.PREPAY_INVOICE_ID BF_ACCRUAL_PP_INV /*BUG6970538*/
, AC.CHECK_ID BF_CASHALWAYS_PP_PMT_ID /*BUG9067098*/
, AC.FUTURE_PAY_DUE_DATE BF_CASHALWAYS_PP_MAT_DATE /*BUG9553037*/
, (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 IN ( 'PAYMENT CREATED'
, 'PAYMENT ADJUSTED'
, 'REFUND RECORDED'
, 'REFUND ADJUSTED') /*BUG9711713*/
AND AID.PREPAY_DISTRIBUTION_ID = APHD.INVOICE_DISTRIBUTION_ID(+)
AND APH.CHECK_ID = AC.CHECK_ID /*BUG9067098*/ 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 IN ( 'PAYMENT MATURITY'
, 'PAYMENT MATURITY ADJUSTED')
AND AID.PREPAY_DISTRIBUTION_ID = APHD.INVOICE_DISTRIBUTION_ID(+)
AND APH.CHECK_ID = AC.CHECK_ID GROUP BY APHD.INVOICE_DISTRIBUTION_ID) BF_CASHALWAYS_PP_MAT_DIST_ID /*BUG9553037*/
, (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 IN ('PAYMENT CLEARING'
, 'PAYMENT CLEARING ADJUSTED')/*BUG9067098*/
AND AID.PREPAY_DISTRIBUTION_ID = APHD.INVOICE_DISTRIBUTION_ID(+)
AND APH.CHECK_ID = AC.CHECK_ID /*BUG9067098*/ 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
, PPD.DETAIL_TAX_DIST_ID PP_DETAIL_TAX_DIST_ID
, PPD.DIST_CODE_COMBINATION_ID PREPAY_INV_DIST_CCID /*BUG11715802
, 11651946*/
, 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 /*BUG8296737*/ /*BUG9846468 */ /* BUG 10074972
, 11720045*/
, CASE WHEN((FSP.REQ_ENCUMBRANCE_FLAG = 'Y' OR FSP.PURCH_ENCUMBRANCE_FLAG = 'Y')
AND NVL(AID.ENCUMBERED_FLAG
, 'N')='Y') THEN PSA_BC_XLA_PUB.GET_SLA_NOTUPGRADED_FLAG (201
, 'AP_INVOICES'
, DECODE(NVL(APAD.REVERSED_PREPAY_APP_DIST_ID
, -99)
, -99
, APPH.PREPAY_INVOICE_ID
, AID.INVOICE_ID)
, 'AP_INV_DIST'
, DECODE(NVL(APAD.REVERSED_PREPAY_APP_DIST_ID
, -99)
, -99
, AID.PREPAY_DISTRIBUTION_ID
, AID.PARENT_REVERSAL_ID) ) ELSE 'N' END UPG_AP_ENCUM_OPTION
, GLET.ENCUMBRANCE_TYPE_ID UPG_CR_ENC_TYPE_ID
, AID.DIST_CODE_COMBINATION_ID UPG_ENC_CR_CCID /* BUG13496767 */
, NVL(APAD.BASE_AMOUNT
, NVL(APAD.AMOUNT
, 0)) UPG_ENC_CR_BASE_AMT
, NVL(APAD.AMOUNT
, 0) UPG_ENC_CR_AMT
, GLET.ENCUMBRANCE_TYPE_ID UPG_DR_ENC_TYPE_ID
, RECP_INV_DISTS.DIST_CODE_COMBINATION_ID UPG_ENC_DR_CCID
, NVL(APAD.BASE_AMOUNT
, NVL(APAD.AMOUNT
, 0)) UPG_ENC_DR_BASE_AMT
, NVL(APAD.AMOUNT
, 0) UPG_ENC_DR_AMT /* BUG 7252576 */
, NULL UPG_ENC_DR_ACCT_CLASS
, NULL UPG_ENC_CR_ACCT_CLASS /* BUG 7252576 */
, 'Y' OVERRIDE_ACCTD_AMT_FLAG
, CGAC.GAIN_CODE_COMBINATION_ID CGAC_GAIN_CCID
, CGAC.LOSS_CODE_COMBINATION_ID CGAC_LOSS_CCID
, AWT_RELATED_IDS.DIST_CODE_COMBINATION_ID AWT_RELATED_DIST_ACCOUNT
, AWT_RELATED_IDS.LINE_TYPE_LOOKUP_CODE AWT_RELATED_LINE_TYPE_LOOKUP
, RECP_INV_DISTS.ENCUMBERED_FLAG INV_DIST_ENCUMBERED_FLAG /* BUG9681285 */
, AWT_RELATED_POD.ACCRUE_ON_RECEIPT_FLAG AWT_REL_ACCURE_ON_RECEIPT_FLAG /*BUG12673419*/
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
, AP_INVOICE_DISTRIBUTIONS_ALL PPD
, GL_ENCUMBRANCE_TYPES GLET
, AP_INVOICES_ALL AI
, /*BUG6970538 (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 AWT_RELATED_IDS
, AP_INVOICE_DISTRIBUTIONS_ALL RECP_INV_RETAINAGE_DISTS
, PO_DISTRIBUTIONS_ALL POD
, /*BUG 7283181*/ AP_CHECKS_ALL AC
, CE_GL_ACCOUNTS_CCID CGAC /*BUG 7283181*/
, FINANCIALS_SYSTEM_PARAMS_ALL FSP /* ADDED FOR BUG#8235541 */
, PO_DISTRIBUTIONS_ALL AWT_RELATED_POD/*BUG12673419*/
WHERE XE.EVENT_ID = APPH.ACCOUNTING_EVENT_ID
AND AID.INVOICE_ID = APPH.INVOICE_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 /*BUG 8945859*/
AND AC.CHECK_ID = (SELECT CHECK_ID
FROM AP_INVOICE_PAYMENTS_ALL AIP
WHERE AIP.INVOICE_ID = APPH.PREPAY_INVOICE_ID
AND NVL(REVERSAL_FLAG
, 'N') <> 'Y'
AND ROWNUM = 1)
AND AC.CE_BANK_ACCT_USE_ID = CGAC.BANK_ACCT_USE_ID(+) /*BUG 7283181*/
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 RECP_INV_DISTS.AWT_RELATED_ID = AWT_RELATED_IDS.INVOICE_DISTRIBUTION_ID(+) /*BUG6970538
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 AID.PREPAY_DISTRIBUTION_ID = PPD.INVOICE_DISTRIBUTION_ID
AND GLET.ENCUMBRANCE_TYPE_KEY='INVOICES' /* ADDED FOR BUG#8235541 START */
AND AI.SET_OF_BOOKS_ID = FSP.SET_OF_BOOKS_ID
AND APPH.ORG_ID = FSP.ORG_ID/* ADDED FOR BUG#8235541 END */
AND AWT_RELATED_IDS.PO_DISTRIBUTION_ID = AWT_RELATED_POD.PO_DISTRIBUTION_ID(+)/*BUG12673419*/