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_PAYMENT_EXTRACT_DETAILS_V

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

View Text

SELECT /*+ LEADING(XE) CARDINALITY(XE
, 1) */ APHD.ACCOUNTING_EVENT_ID
, APHD.PAY_DIST_LOOKUP_CODE
, APHD.PAYMENT_HIST_DIST_ID
, DECODE(XE.EVENT_TYPE_CODE
, 'MANUAL PAYMENT ADJUSTED'
, DECODE(AIP.REVERSAL_INV_PMT_ID
, NULL
, 'N'
, 'Y')
, 'PAYMENT UNCLEARED'
, 'Y'
, 'PAYMENT MATURITY REVERSED'
, 'Y'
, 'N')
, APHD.REVERSED_PAY_HIST_DIST_ID
, 'S'
, AI.INVOICE_CURRENCY_CODE
, APHD.REVERSAL_FLAG
, APHD.AMOUNT
, APHD.BANK_CURR_AMOUNT
, APHD.HISTORICAL_FLAG
, NVL(APHD.INVOICE_DIST_BASE_AMOUNT
, APHD.INVOICE_DIST_AMOUNT)
, APHD.PAYMENT_HIST_DIST_ID
, APHD.PAYMENT_HISTORY_ID
, APHD.INVOICE_DISTRIBUTION_ID
, APHD.INVOICE_PAYMENT_ID
, NVL(APHD.INVOICE_DIST_BASE_AMOUNT
, APHD.INVOICE_DIST_AMOUNT)- NVL(APHD.PAID_BASE_AMOUNT
, APHD.AMOUNT)
, NVL(APHD.PAID_BASE_AMOUNT
, APHD.AMOUNT) - NVL(APHD.MATURED_BASE_AMOUNT
, APHD.AMOUNT)
, NVL(APHD.MATURED_BASE_AMOUNT
, APHD.AMOUNT) - NVL(APHD.CLEARED_BASE_AMOUNT
, APHD.AMOUNT)
, NVL(APHD.PAID_BASE_AMOUNT
, APHD.AMOUNT) - NVL(APHD.CLEARED_BASE_AMOUNT
, APHD.AMOUNT)
, NVL(APHD.INVOICE_DIST_BASE_AMOUNT
, APHD.INVOICE_DIST_AMOUNT) - NVL(APHD.CLEARED_BASE_AMOUNT
, APHD.AMOUNT)
, AIP.ACCOUNTING_DATE
, AIP.AMOUNT
, AIP.INVOICE_ID
, AIP.INVOICE_PAYMENT_ID
, AIP.PAYMENT_NUM
, AIP.PERIOD_NAME
, AIP.ACCTS_PAY_CODE_COMBINATION_ID
, AIP.ASSET_CODE_COMBINATION_ID
, AIP.ATTRIBUTE1
, AIP.ATTRIBUTE10
, AIP.ATTRIBUTE11
, AIP.ATTRIBUTE12
, AIP.ATTRIBUTE13
, AIP.ATTRIBUTE14
, AIP.ATTRIBUTE15
, AIP.ATTRIBUTE2
, AIP.ATTRIBUTE3
, AIP.ATTRIBUTE4
, AIP.ATTRIBUTE5
, AIP.ATTRIBUTE6
, AIP.ATTRIBUTE7
, AIP.ATTRIBUTE8
, AIP.ATTRIBUTE9
, AIP.ATTRIBUTE_CATEGORY
, AIP.INVOICE_PAYMENT_TYPE
, AIP.GLOBAL_ATTRIBUTE_CATEGORY
, AIP.GLOBAL_ATTRIBUTE1
, AIP.GLOBAL_ATTRIBUTE2
, AIP.GLOBAL_ATTRIBUTE3
, AIP.GLOBAL_ATTRIBUTE4
, AIP.GLOBAL_ATTRIBUTE5
, AIP.GLOBAL_ATTRIBUTE6
, AIP.GLOBAL_ATTRIBUTE7
, AIP.GLOBAL_ATTRIBUTE8
, AIP.GLOBAL_ATTRIBUTE9
, AIP.GLOBAL_ATTRIBUTE10
, AIP.GLOBAL_ATTRIBUTE11
, AIP.GLOBAL_ATTRIBUTE12
, AIP.GLOBAL_ATTRIBUTE13
, AIP.GLOBAL_ATTRIBUTE14
, AIP.GLOBAL_ATTRIBUTE15
, AIP.GLOBAL_ATTRIBUTE16
, AIP.GLOBAL_ATTRIBUTE17
, AIP.GLOBAL_ATTRIBUTE18
, AIP.GLOBAL_ATTRIBUTE19
, AIP.GLOBAL_ATTRIBUTE20
, AIP.EXTERNAL_BANK_ACCOUNT_ID
, AIP.ACCOUNTING_EVENT_ID
, AIP.REVERSAL_FLAG
, AIP.REVERSAL_INV_PMT_ID
, AID.DIST_CODE_COMBINATION_ID
, AID.AMOUNT
, AID.AWT_FLAG
, AID.LINE_TYPE_LOOKUP_CODE
, AID.DISTRIBUTION_LINE_NUMBER
, AWTG.NAME
, AID.AWARD_ID
, AID.RATE_VAR_CODE_COMBINATION_ID
, AID.INVOICE_DISTRIBUTION_ID
, AID.INCOME_TAX_REGION
, DECODE(AID.AWT_INVOICE_PAYMENT_ID
, NULL
, 'N'
, 'Y')
, AID.MERCHANT_DOCUMENT_NUMBER
, AID.MERCHANT_REFERENCE
, AID.MERCHANT_TAXPAYER_ID
, AID.MERCHANT_TAX_REG_NUMBER
, AI.INVOICE_AMOUNT
, AI.ACCTS_PAY_CODE_COMBINATION_ID
, 'AP_PMT_DIST'
, AID.PO_DISTRIBUTION_ID
, AWT_RELATED_IDS.DIST_CODE_COMBINATION_ID AWT_RELATED_DIST_CCID
, AI.INVOICE_DATE
, AI.INVOICE_TYPE_LOOKUP_CODE
, AI.DISCOUNT_AMOUNT_TAKEN
, AIP.SET_OF_BOOKS_ID
, AI.EXCHANGE_DATE
, AI.EXCHANGE_RATE
, AI.EXCHANGE_RATE_TYPE
, APHD.INVOICE_DIST_AMOUNT
, CASE WHEN APH.BANK_CURRENCY_CODE <> ASP.BASE_CURRENCY_CODE AND APH.BANK_TO_BASE_XRATE_TYPE IS NOT NULL AND APH.BANK_TO_BASE_XRATE IS NULL THEN NULL ELSE NVL(APHD.CLEARED_BASE_AMOUNT
, APHD.AMOUNT) END
, NVL(APHD.INVOICE_DIST_BASE_AMOUNT
, APHD.INVOICE_DIST_AMOUNT)
, CASE WHEN APH.PMT_CURRENCY_CODE <> ASP.BASE_CURRENCY_CODE AND APH.PMT_TO_BASE_XRATE_TYPE IS NOT NULL AND APH.PMT_TO_BASE_XRATE IS NULL THEN NULL ELSE NVL(APHD.MATURED_BASE_AMOUNT
, APHD.AMOUNT) END
, CASE WHEN APH.PMT_CURRENCY_CODE <> ASP.BASE_CURRENCY_CODE AND APH.PMT_TO_BASE_XRATE_TYPE IS NOT NULL AND APH.PMT_TO_BASE_XRATE IS NULL THEN NULL ELSE NVL(APHD.PAID_BASE_AMOUNT
, APHD.AMOUNT) END
, DECODE(APH.GAIN_LOSS_INDICATOR
, 'G'
, 'GAIN'
, 'L'
, 'LOSS'
, 'NONE') INV_PMT_GAIN_LOSS_INDICATOR
, DECODE(APH.GAIN_LOSS_INDICATOR
, 'G'
, 'GAIN'
, 'L'
, 'LOSS'
, 'NONE') INV_CLR_GAIN_LOSS_INDICATOR
, DECODE(APH.GAIN_LOSS_INDICATOR
, 'G'
, 'GAIN'
, 'L'
, 'LOSS'
, 'NONE') PMT_MAT_GAIN_LOSS_INDICATOR
, DECODE(APH.GAIN_LOSS_INDICATOR
, 'G'
, 'GAIN'
, 'L'
, 'LOSS'
, 'NONE') MAT_CLR_GAIN_LOSS_INDICATOR
, DECODE(APH.GAIN_LOSS_INDICATOR
, 'G'
, 'GAIN'
, 'L'
, 'LOSS'
, 'NONE') PMT_CLR_GAIN_LOSS_INDICATOR
, NVL(APHD.ROUNDING_AMT
, 0)
, NVL(APHD.PAID_BASE_AMOUNT
, APHD.AMOUNT) - NVL(APHD.ROUNDING_AMT
, 0)
, NVL(APHD.CLEARED_BASE_AMOUNT
, APHD.AMOUNT) - NVL(APHD.ROUNDING_AMT
, 0)
, NVL(APHD.MATURED_BASE_AMOUNT
, APHD.AMOUNT) - NVL(APHD.ROUNDING_AMT
, 0)
, 200
, 'AP_INV_DIST'
, 'AP_INVOICES'
, DECODE(APHD.PAY_DIST_LOOKUP_CODE
, 'AWT'
, APHD.AWT_RELATED_ID
, AID.INVOICE_DISTRIBUTION_ID)
, AID.INVOICE_ID
, 201
, 'PO_DIST'
, 'PURCHASE ORDER'
, 'AP_PMT_DIST'
, 'AP_PAYMENTS'
, (SELECT PMTAPHD.PAYMENT_HIST_DIST_ID
FROM AP_PAYMENT_HIST_DISTS PMTAPHD
, AP_PAYMENT_HISTORY_ALL PMTAPH
WHERE PMTAPH.PAYMENT_HISTORY_ID = PMTAPHD.PAYMENT_HISTORY_ID
AND PMTAPH.ACCOUNTING_EVENT_ID = PMTAPHD.ACCOUNTING_EVENT_ID
AND PMTAPH.TRANSACTION_TYPE IN ('PAYMENT CREATED'
, 'REFUND RECORDED'
, 'PAYMENT ADJUSTED')
AND APHD.INVOICE_DISTRIBUTION_ID = PMTAPHD.INVOICE_DISTRIBUTION_ID(+)
AND APHD.INVOICE_PAYMENT_ID = PMTAPHD.INVOICE_PAYMENT_ID(+)
AND APHD.PAY_DIST_LOOKUP_CODE = PMTAPHD.PAY_DIST_LOOKUP_CODE(+)
AND ROWNUM = 1 ) BUS_FLOW_PAYMENT_DIST_ID
, AIP.CHECK_ID
, AID.PROJECT_ID
, AID.TASK_ID
, AID.EXPENDITURE_TYPE
, AID.EXPENDITURE_ORGANIZATION_ID
, RET_DISTS.RETAINED_INVOICE_ID
, RET_DISTS.RETAINED_INVOICE_DIST_ID
, RET_DISTS.RETAINED_INVOICE_ID
, RET_DISTS.RETAINED_INVOICE_DIST_ID
, -1
, AID.AWT_RELATED_ID AID_AWT_RELATED_ID
, APHD.QUANTITY_VARIANCE
, APHD.INVOICE_BASE_QTY_VARIANCE
, APHD.AMOUNT_VARIANCE
, APHD.INVOICE_BASE_AMT_VARIANCE
, DECODE(AID.LINE_TYPE_LOOKUP_CODE
, 'AWT'
, DECODE((SELECT DEF_REC_SETTLEMENT_OPTION_CODE
FROM ZX_AP_DEF_TAX_EXTRACT_V ZDT
WHERE ZDT.REC_NREC_TAX_DIST_ID =AWT_RELATED_IDS.DETAIL_TAX_DIST_ID )
, 'DEFERRED'
, AWT_RELATED_IDS.DETAIL_TAX_DIST_ID
, AID.DETAIL_TAX_DIST_ID)
, AID.DETAIL_TAX_DIST_ID) AID_DETAIL_TAX_DIST_ID
, AID.ENCUMBERED_FLAG
, POD.PO_DISTRIBUTION_ID
, POD.ENCUMBERED_FLAG
, POD.ENCUMBERED_AMOUNT
, POD.GL_ENCUMBERED_DATE
, POD.GL_ENCUMBERED_PERIOD_NAME
, POD.ATTRIBUTE_CATEGORY
, POD.ATTRIBUTE1
, POD.ATTRIBUTE2
, POD.ATTRIBUTE3
, POD.ATTRIBUTE4
, POD.ATTRIBUTE5
, POD.ATTRIBUTE6
, POD.ATTRIBUTE7
, POD.ATTRIBUTE8
, POD.ATTRIBUTE9
, POD.ATTRIBUTE10
, POD.ATTRIBUTE11
, POD.ATTRIBUTE12
, POD.ATTRIBUTE13
, POD.ATTRIBUTE14
, POD.ATTRIBUTE15
, POD.DISTRIBUTION_NUM
, POD.ACCRUE_ON_RECEIPT_FLAG
, POD.CODE_COMBINATION_ID
, POD.VARIANCE_ACCOUNT_ID
, POD.DESTINATION_TYPE_CODE
, POD.PO_HEADER_ID
, POD.PO_LINE_ID
, POD.LINE_LOCATION_ID
, (SELECT MAX(PMTAPHD.PAYMENT_HIST_DIST_ID)
FROM AP_PAYMENT_HIST_DISTS PMTAPHD
, AP_PAYMENT_HISTORY_ALL PMTAPH
WHERE PMTAPH.PAYMENT_HISTORY_ID = PMTAPHD.PAYMENT_HISTORY_ID
AND PMTAPH.ACCOUNTING_EVENT_ID = PMTAPHD.ACCOUNTING_EVENT_ID
AND PMTAPH.TRANSACTION_TYPE IN ('PAYMENT MATURITY'
, 'PAYMENT MATURITY ADJUSTED')
AND APHD.INVOICE_DISTRIBUTION_ID = PMTAPHD.INVOICE_DISTRIBUTION_ID(+)
AND APHD.INVOICE_PAYMENT_ID = PMTAPHD.INVOICE_PAYMENT_ID(+)
AND APHD.PAY_DIST_LOOKUP_CODE = PMTAPHD.PAY_DIST_LOOKUP_CODE(+)
AND ROWNUM = 1 ) BUS_FLOW_PAYMENT_MAT_DIST_ID
, 'Y' OVERRIDE_ACCTD_AMT_FLAG
, AI.PAYMENT_STATUS_FLAG AI_PAYMENT_STATUS_FLAG
, (NVL(AID.AMOUNT
, 0) - NVL(AID.AMOUNT_VARIANCE
, 0) - NVL(AID.QUANTITY_VARIANCE
, 0)) ENCUMBRANCE_AMOUNT
, DECODE(AID.LINE_TYPE_LOOKUP_CODE
, 'AWT'
, DECODE((SELECT ZDT.DEF_REC_SETTLEMENT_OPTION_CODE
FROM ZX_AP_DEF_TAX_EXTRACT_V ZDT
WHERE ZDT.REC_NREC_TAX_DIST_ID =AWT_RELATED_IDS.DETAIL_TAX_DIST_ID)
, 'DEFERRED'
, 'AWT_DEFERRED'
, NULL)
, NULL) AWT_RELATED_DIST_TYPE
FROM AP_PAYMENT_HIST_DISTS APHD
, AP_PAYMENT_HISTORY_ALL APH
, AP_INVOICE_PAYMENTS_ALL AIP
, AP_INVOICE_DISTRIBUTIONS_ALL AID
, XLA_EVENTS_GT XE
, AP_AWT_GROUPS AWTG
, AP_INVOICES_ALL AI
, AP_INVOICE_DISTRIBUTIONS_ALL AWT_RELATED_IDS
, PO_DISTRIBUTIONS_ALL POD
, AP_SYSTEM_PARAMETERS_ALL ASP
, (SELECT AIDR.RETAINED_INVOICE_DIST_ID RETAINED_INVOICE_DIST_ID
, AILR.RETAINED_INVOICE_ID RETAINED_INVOICE_ID
, AIDR.INVOICE_DISTRIBUTION_ID INVOICE_DISTRIBUTION_ID
FROM AP_INVOICE_LINES_ALL AILR
, AP_INVOICE_DISTRIBUTIONS_ALL AIDR
WHERE AILR.INVOICE_ID = AIDR.INVOICE_DISTRIBUTION_ID
AND AILR.LINE_TYPE_LOOKUP_CODE = 'RETAINAGE RELEASE'
AND AIDR.LINE_TYPE_LOOKUP_CODE = 'RETAINAGE') RET_DISTS
WHERE APHD.PAYMENT_HISTORY_ID = APH.PAYMENT_HISTORY_ID
AND APHD.INVOICE_PAYMENT_ID = AIP.INVOICE_PAYMENT_ID
AND APHD.INVOICE_DISTRIBUTION_ID = AID.INVOICE_DISTRIBUTION_ID
AND APH.ORG_ID = ASP.ORG_ID
AND AID.INVOICE_ID = AI.INVOICE_ID
AND AID.AWT_GROUP_ID = AWTG.GROUP_ID(+)
AND APHD.ACCOUNTING_EVENT_ID = XE.EVENT_ID
AND AID.AWT_RELATED_ID = AWT_RELATED_IDS.INVOICE_DISTRIBUTION_ID(+) AND APHD.INVOICE_DISTRIBUTION_ID = RET_DISTS.INVOICE_DISTRIBUTION_ID(+)
AND XE.APPLICATION_ID = 200
AND XE.ENTITY_CODE = 'AP_PAYMENTS'
AND AID.PO_DISTRIBUTION_ID = POD.PO_DISTRIBUTION_ID(+)