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(+)
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(+)
|
|
|