[Home] [Help]
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'),
'MANUAL REFUND 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 /* bug 12937499 */
,APHD.invoice_distribution_id
,APHD.payment_history_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
,nvl(AI.Exchange_Date, AI.GL_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
,CASE
WHEN aphd.gain_loss_indicator='G' THEN
'GAIN'
WHEN aphd.gain_loss_indicator='L' THEN
'LOSS'
ELSE 'NONE'
END Inv_Pmt_Gain_loss_Indicator
,CASE
WHEN aphd.gain_loss_indicator='G' THEN
'GAIN'
WHEN aphd.gain_loss_indicator='L' THEN
'LOSS'
ELSE 'NONE'
END Inv_Clr_Gain_loss_Indicator
,CASE
WHEN aphd.gain_loss_indicator='G' THEN
'GAIN'
WHEN aphd.gain_loss_indicator='L' THEN
'LOSS'
ELSE 'NONE'
END Pmt_Mat_Gain_loss_Indicator
,CASE
WHEN aphd.gain_loss_indicator='G' THEN
'GAIN'
WHEN aphd.gain_loss_indicator='L' THEN
'LOSS'
ELSE 'NONE'
END Mat_Clr_Gain_loss_Indicator
,CASE
WHEN aphd.gain_loss_indicator='G' THEN
'GAIN'
WHEN aphd.gain_loss_indicator='L' THEN
'LOSS'
ELSE 'NONE'
END 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','MANUAL PAYMENT ADJUSTED',
'REFUND ADJUSTED' ,'MANUAL REFUND 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
,AWT_RELATED_IDS.line_type_lookup_code AWT_RELATED_LINE_TYPE_LOOKUP
,NVL(APHD.INVOICE_DIST_BASE_AMOUNT,0)
- NVL(APHD.INVOICE_BASE_AMT_VARIANCE, 0)
- NVL(APHD.INVOICE_BASE_QTY_VARIANCE, 0)
- NVL(ERV_DISTS.INVOICE_DIST_BASE_AMOUNT,0) ENCUMBRANCE_PMT_BASE_AMOUNT
,CASE
WHEN AID.po_distribution_id IS NOT NULL AND AI.Exchange_Rate IS NOT NULL
THEN
DECODE(aphd.PAY_DIST_LOOKUP_CODE, 'CASH',
(nvl(aphd.PAID_BASE_AMOUNT,0) - nvl(
(select sum(nvl(aphd2.PAID_BASE_AMOUNT,0))
from ap_Payment_Hist_dIsts aphd2
where aphd.accounting_event_id = aphd2.accounting_event_id
and aphd.INVOICE_DISTRIBUTION_ID = aphd2.INVOICE_DISTRIBUTION_ID
and aphd2.invoice_payment_id = aip.invoice_payment_id
and aphd2.PAY_DIST_LOOKUP_CODE = 'EXCHANGE RATE VARIANCE'
),0)), aphd.PAID_BASE_AMOUNT)
ELSE aphd.PAID_BASE_AMOUNT
end CASH_PMT_BASE_AMOUNT /*Bug#8785586*/
,AWT_RELATED_IDS.encumbered_flag AWT_RELATED_ENC_FLAG
,AID.description AID_DESC_PAY
, 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'
, ai.invoice_id
, 'AP_INV_DIST'
, COALESCE (aid.parent_reversal_id, aid.invoice_distribution_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
, COALESCE (aphd.invoice_dist_base_amount, aphd.invoice_dist_amount, aphd.amount) UPG_ENC_CR_BASE_AMT
, nvl(aphd.invoice_dist_amount, aphd.amount) UPG_ENC_CR_AMT
, GLET.encumbrance_type_id UPG_DR_ENC_TYPE_ID
, AID.dist_code_combination_id UPG_ENC_DR_CCID
, COALESCE (aphd.invoice_dist_base_amount, aphd.invoice_dist_amount, aphd.amount) UPG_ENC_DR_BASE_AMT
, nvl(aphd.invoice_dist_amount, aphd.amount) UPG_ENC_DR_AMT
, NULL UPG_ENC_DR_ACCT_CLASS
, NULL UPG_ENC_CR_ACCT_CLASS
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,
(SELECT APHDE.INVOICE_DISTRIBUTION_ID INVOICE_DISTRIBUTION_ID,
APHDE.INVOICE_DIST_BASE_AMOUNT INVOICE_DIST_BASE_AMOUNT,
APHDE.PAYMENT_HISTORY_ID PAYMENT_HISTORY_ID,
APHDE.INVOICE_PAYMENT_ID INVOICE_PAYMENT_ID
FROM AP_PAYMENT_HIST_DISTS APHDE
WHERE APHDE.PAY_DIST_LOOKUP_CODE in
('EXCHANGE RATE VARIANCE','TAX EXCHANGE RATE VARIANCE')) ERV_DISTS,
gl_encumbrance_types GLET,
financials_system_params_all fsp
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(+)
AND APHD.INVOICE_DISTRIBUTION_ID = ERV_DISTS.INVOICE_DISTRIBUTION_ID(+)
AND APHD.PAYMENT_HISTORY_ID = ERV_DISTS.PAYMENT_HISTORY_ID(+)
AND APHD.INVOICE_PAYMENT_ID = ERV_DISTS.INVOICE_PAYMENT_ID(+)
AND GLET.encumbrance_type_key = 'Invoices'
AND fsp.set_of_books_id = ai.set_of_books_id
and fsp.org_id = ai.org_id
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')
,
'MANUAL REFUND 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 /* BUG 12937499 */
, APHD.INVOICE_DISTRIBUTION_ID
, APHD.PAYMENT_HISTORY_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
, NVL(AI.EXCHANGE_DATE
, AI.GL_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
, CASE
WHEN APHD.GAIN_LOSS_INDICATOR='G' THEN
'GAIN'
WHEN APHD.GAIN_LOSS_INDICATOR='L' THEN
'LOSS'
ELSE 'NONE'
END INV_PMT_GAIN_LOSS_INDICATOR
, CASE
WHEN APHD.GAIN_LOSS_INDICATOR='G' THEN
'GAIN'
WHEN APHD.GAIN_LOSS_INDICATOR='L' THEN
'LOSS'
ELSE 'NONE'
END INV_CLR_GAIN_LOSS_INDICATOR
, CASE
WHEN APHD.GAIN_LOSS_INDICATOR='G' THEN
'GAIN'
WHEN APHD.GAIN_LOSS_INDICATOR='L' THEN
'LOSS'
ELSE 'NONE'
END PMT_MAT_GAIN_LOSS_INDICATOR
, CASE
WHEN APHD.GAIN_LOSS_INDICATOR='G' THEN
'GAIN'
WHEN APHD.GAIN_LOSS_INDICATOR='L' THEN
'LOSS'
ELSE 'NONE'
END MAT_CLR_GAIN_LOSS_INDICATOR
, CASE
WHEN APHD.GAIN_LOSS_INDICATOR='G' THEN
'GAIN'
WHEN APHD.GAIN_LOSS_INDICATOR='L' THEN
'LOSS'
ELSE 'NONE'
END 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'
, 'MANUAL PAYMENT ADJUSTED'
,
'REFUND ADJUSTED'
, 'MANUAL REFUND 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
, AWT_RELATED_IDS.LINE_TYPE_LOOKUP_CODE AWT_RELATED_LINE_TYPE_LOOKUP
, NVL(APHD.INVOICE_DIST_BASE_AMOUNT
, 0)
- NVL(APHD.INVOICE_BASE_AMT_VARIANCE
, 0)
- NVL(APHD.INVOICE_BASE_QTY_VARIANCE
, 0)
- NVL(ERV_DISTS.INVOICE_DIST_BASE_AMOUNT
, 0) ENCUMBRANCE_PMT_BASE_AMOUNT
, CASE
WHEN AID.PO_DISTRIBUTION_ID IS NOT NULL
AND AI.EXCHANGE_RATE IS NOT NULL
THEN
DECODE(APHD.PAY_DIST_LOOKUP_CODE
, 'CASH'
,
(NVL(APHD.PAID_BASE_AMOUNT
, 0) - NVL(
(SELECT SUM(NVL(APHD2.PAID_BASE_AMOUNT
, 0))
FROM AP_PAYMENT_HIST_DISTS APHD2
WHERE APHD.ACCOUNTING_EVENT_ID = APHD2.ACCOUNTING_EVENT_ID
AND APHD.INVOICE_DISTRIBUTION_ID = APHD2.INVOICE_DISTRIBUTION_ID
AND APHD2.INVOICE_PAYMENT_ID = AIP.INVOICE_PAYMENT_ID
AND APHD2.PAY_DIST_LOOKUP_CODE = 'EXCHANGE RATE VARIANCE'
)
, 0))
, APHD.PAID_BASE_AMOUNT)
ELSE APHD.PAID_BASE_AMOUNT
END CASH_PMT_BASE_AMOUNT /*BUG#8785586*/
, AWT_RELATED_IDS.ENCUMBERED_FLAG AWT_RELATED_ENC_FLAG
, AID.DESCRIPTION AID_DESC_PAY
, 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'
, AI.INVOICE_ID
, 'AP_INV_DIST'
, COALESCE (AID.PARENT_REVERSAL_ID
, AID.INVOICE_DISTRIBUTION_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
, COALESCE (APHD.INVOICE_DIST_BASE_AMOUNT
, APHD.INVOICE_DIST_AMOUNT
, APHD.AMOUNT) UPG_ENC_CR_BASE_AMT
, NVL(APHD.INVOICE_DIST_AMOUNT
, APHD.AMOUNT) UPG_ENC_CR_AMT
, GLET.ENCUMBRANCE_TYPE_ID UPG_DR_ENC_TYPE_ID
, AID.DIST_CODE_COMBINATION_ID UPG_ENC_DR_CCID
, COALESCE (APHD.INVOICE_DIST_BASE_AMOUNT
, APHD.INVOICE_DIST_AMOUNT
, APHD.AMOUNT) UPG_ENC_DR_BASE_AMT
, NVL(APHD.INVOICE_DIST_AMOUNT
, APHD.AMOUNT) UPG_ENC_DR_AMT
, NULL UPG_ENC_DR_ACCT_CLASS
, NULL UPG_ENC_CR_ACCT_CLASS
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
,
(SELECT APHDE.INVOICE_DISTRIBUTION_ID INVOICE_DISTRIBUTION_ID
,
APHDE.INVOICE_DIST_BASE_AMOUNT INVOICE_DIST_BASE_AMOUNT
,
APHDE.PAYMENT_HISTORY_ID PAYMENT_HISTORY_ID
,
APHDE.INVOICE_PAYMENT_ID INVOICE_PAYMENT_ID
FROM AP_PAYMENT_HIST_DISTS APHDE
WHERE APHDE.PAY_DIST_LOOKUP_CODE IN
('EXCHANGE RATE VARIANCE'
, 'TAX EXCHANGE RATE VARIANCE')) ERV_DISTS
,
GL_ENCUMBRANCE_TYPES GLET
,
FINANCIALS_SYSTEM_PARAMS_ALL FSP
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(+)
AND APHD.INVOICE_DISTRIBUTION_ID = ERV_DISTS.INVOICE_DISTRIBUTION_ID(+)
AND APHD.PAYMENT_HISTORY_ID = ERV_DISTS.PAYMENT_HISTORY_ID(+)
AND APHD.INVOICE_PAYMENT_ID = ERV_DISTS.INVOICE_PAYMENT_ID(+)
AND GLET.ENCUMBRANCE_TYPE_KEY = 'INVOICES'
AND FSP.SET_OF_BOOKS_ID = AI.SET_OF_BOOKS_ID
AND FSP.ORG_ID = AI.ORG_ID
|
|
|
|