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:	FND - Application Object Library
Description:
Implementation/DBA Data:	APPS.AP_PAYMENT_EXTRACT_DETAILS_V

View Text

SELECT 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
, NVL(APHD.CLEARED_BASE_AMOUNT
, APHD.AMOUNT)
, NVL(APHD.INVOICE_DIST_BASE_AMOUNT
, APHD.INVOICE_DIST_AMOUNT)
, NVL(APHD.MATURED_BASE_AMOUNT
, APHD.AMOUNT)
, NVL(APHD.PAID_BASE_AMOUNT
, APHD.AMOUNT)
, 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'
, PMT_ISSUE_DISTS.PAYMENT_HIST_DIST_ID 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
, 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
, PMT_MATURE_DISTS.PAYMENT_HIST_DIST_ID BUS_FLOW_PAYMENT_MAT_DIST_ID
, 'Y' OVERRIDE_ACCTD_AMT_FLAG
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
, (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
, (SELECT PMTAPHD.PAYMENT_HIST_DIST_ID
, PMTAPHD.PAY_DIST_LOOKUP_CODE
, PMTAPHD.INVOICE_DISTRIBUTION_ID
, PMTAPHD.INVOICE_PAYMENT_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')) PMT_ISSUE_DISTS
, (SELECT PMTAPHD.PAYMENT_HIST_DIST_ID
, PMTAPHD.PAY_DIST_LOOKUP_CODE
, PMTAPHD.INVOICE_DISTRIBUTION_ID
, PMTAPHD.INVOICE_PAYMENT_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 = 'PAYMENT MATURITY') PMT_MATURE_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 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 APHD.INVOICE_DISTRIBUTION_ID = PMT_ISSUE_DISTS.INVOICE_DISTRIBUTION_ID(+)
AND APHD.INVOICE_PAYMENT_ID = PMT_ISSUE_DISTS.INVOICE_PAYMENT_ID(+)
AND APHD.PAY_DIST_LOOKUP_CODE = PMT_ISSUE_DISTS.PAY_DIST_LOOKUP_CODE(+)
AND APHD.INVOICE_DISTRIBUTION_ID = PMT_MATURE_DISTS.INVOICE_DISTRIBUTION_ID(+)
AND APHD.INVOICE_PAYMENT_ID = PMT_MATURE_DISTS.INVOICE_PAYMENT_ID(+)
AND APHD.PAY_DIST_LOOKUP_CODE = PMT_MATURE_DISTS.PAY_DIST_LOOKUP_CODE(+)
AND XE.APPLICATION_ID = 200
AND XE.ENTITY_CODE = 'AP_PAYMENTS'
AND AID.PO_DISTRIBUTION_ID = POD.PO_DISTRIBUTION_ID(+)