[Home] [Help]
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*/
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*/
|
|
|
|