AP_PREPAYAPP_EXTRACT_DTLS_BC_V

Details

• Schema: FUSION

• Object owner: AP

• Object type: VIEW

Columns

Name

EVENT_ID LINE_NUMBER DISTRIBUTION_LINK_TYPE DEFERRED_OPTION DEFERRED_START_DATE DEFERRED_END_DATE DEFERRED_NUMBER_OF_PERIODS DEFERRED_PERIOD_TYPE APAD_DISTRIBUTION_IDENTIFIER DIST_ACCOUNT_REVERSAL_OPTION APAD_DIST_LOOKUP_CODE APAD_INVOICE_ADJ_EVENT_ID APPH_PREPAY_INVOICE_ID APAD_AMOUNT PREPAY_PAY_CURRENCY_CODE PREPAY_CLEAR_CURRENCY_CODE AIL_LINE_NUMBER AIL_LINE_TYPE_LOOKUP_CODE AIL_ACCOUNT_SEGMENT AIL_BALANCING_SEGMENT AIL_COST_CENTER_SEGMENT AIL_OVERLAY_DIST_CODE_CONCAT AIL_DEFAULT_DIST_CCID AIL_ACCOUNTING_DATE AIL_PERIOD_NAME AIL_AMOUNT AIL_ROUNDING_AMT AIL_ATTRIBUTE_CATEGORY AIL_ATTRIBUTE1 AIL_ATTRIBUTE2 AIL_ATTRIBUTE3 AIL_ATTRIBUTE4 AIL_ATTRIBUTE5 AIL_ATTRIBUTE6 AIL_ATTRIBUTE7 AIL_ATTRIBUTE8 AIL_ATTRIBUTE9 AIL_ATTRIBUTE10 AIL_ATTRIBUTE11 AIL_ATTRIBUTE12 AIL_ATTRIBUTE13 AIL_ATTRIBUTE14 AIL_ATTRIBUTE15 AIL_GLOBAL_ATTRIBUTE_CATEGORY AIL_GLOBAL_ATTRIBUTE1 AIL_GLOBAL_ATTRIBUTE2 AIL_GLOBAL_ATTRIBUTE3 AIL_GLOBAL_ATTRIBUTE4 AIL_GLOBAL_ATTRIBUTE5 AIL_GLOBAL_ATTRIBUTE6 AIL_GLOBAL_ATTRIBUTE7 AIL_GLOBAL_ATTRIBUTE8 AIL_GLOBAL_ATTRIBUTE9 AIL_GLOBAL_ATTRIBUTE10 AIL_GLOBAL_ATTRIBUTE11 AIL_GLOBAL_ATTRIBUTE12 AIL_GLOBAL_ATTRIBUTE13 AIL_GLOBAL_ATTRIBUTE14 AIL_GLOBAL_ATTRIBUTE15 AIL_GLOBAL_ATTRIBUTE16 AIL_GLOBAL_ATTRIBUTE17 AIL_GLOBAL_ATTRIBUTE18 AIL_GLOBAL_ATTRIBUTE19 AIL_GLOBAL_ATTRIBUTE20 SUMMARY_TAX_LINE_ID AID_ACCOUNTING_DATE AID_DISTRIBUTION_LINE_NUMBER AID_LINE_TYPE_LOOKUP_CODE AID_PERIOD_NAME AID_ACCTS_PAY_CCID AID_DIST_CCID AID_DESCRIPTION AID_EXCHANGE_RATE_VARIANCE AID_INVOICE_PRICE_VARIANCE AID_RATE_VAR_CCID AID_AMOUNT_ENCUMBERED AID_REVERSAL_FLAG AID_ENCUMBERED_FLAG AID_EXCHANGE_DATE AID_EXCHANGE_RATE AID_EXCHANGE_RATE_TYPE AID_STAT_AMOUNT AID_ATTRIBUTE1 AID_ATTRIBUTE10 AID_ATTRIBUTE11 AID_ATTRIBUTE12 AID_ATTRIBUTE13 AID_ATTRIBUTE14 AID_ATTRIBUTE15 AID_ATTRIBUTE2 AID_ATTRIBUTE3 AID_ATTRIBUTE4 AID_ATTRIBUTE5 AID_ATTRIBUTE6 AID_ATTRIBUTE7 AID_ATTRIBUTE8 AID_ATTRIBUTE9 AID_ATTRIBUTE_CATEGORY AID_PARENT_INVOICE_ID AID_GLOBAL_ATTRIBUTE_CAT AID_GLOBAL_ATTRIBUTE1 AID_GLOBAL_ATTRIBUTE2 AID_GLOBAL_ATTRIBUTE3 AID_GLOBAL_ATTRIBUTE4 AID_GLOBAL_ATTRIBUTE5 AID_GLOBAL_ATTRIBUTE6 AID_GLOBAL_ATTRIBUTE7 AID_GLOBAL_ATTRIBUTE8 AID_GLOBAL_ATTRIBUTE9 AID_GLOBAL_ATTRIBUTE10 AID_GLOBAL_ATTRIBUTE11 AID_GLOBAL_ATTRIBUTE12 AID_GLOBAL_ATTRIBUTE13 AID_GLOBAL_ATTRIBUTE14 AID_GLOBAL_ATTRIBUTE15 AID_GLOBAL_ATTRIBUTE16 AID_GLOBAL_ATTRIBUTE17 AID_GLOBAL_ATTRIBUTE18 AID_GLOBAL_ATTRIBUTE19 AID_GLOBAL_ATTRIBUTE20 AID_MERCHANT_DOCUMENT_NUMBER AID_MERCHANT_NAME AID_PREPAY_DISTRIBUTION_ID AID_INV_INCLUDES_PREPAY_FLAG AID_INVOICE_LINE_NUMBER AID_ROUNDING_AMOUNT AID_AMT_AT_PREPAY_PAY_XRATE AID_PREPAY_TAX_PARENT_ID REVERSED_PREPAY_APP_DIST_ID INV_DIST_BASE_AMOUNT INV_ENTERED_AMOUNT AID_INVOICE_DIST_ID APAD_PREPAY_XDATE APAD_PREPAY_PAY_XDATE APAD_PREPAY_XRATE APAD_PREPAY_PAY_XRATE APAD_PREPAY_XRATE_TYPE APAD_PREPAY_PAY_XRATE_TYPE APAD_PREPAY_CLR_XRATE APAD_PREPAY_CLR_XRATE_TYPE APAD_PREPAY_CLR_XDATE BASE_AMT_AT_PP_CLR_XRATE BASE_AMT_AT_PP_XRATE BASE_AMT_AT_PP_PAY_XRATE APAD_BASE_AMOUNT APAD_ROUNDING_AMOUNT APAD_BASE_AMT_NO_ROUND APAD_BASE_AMOUNT_DIFF INV_PREPMT_GAIN_LOSS_INDICATOR PO_DISTRIBUTION_ID BUS_FLOW_AP_APP_ID BUS_FLOW_INV_DIST_TYPE BUS_FLOW_INV_ENTITY_CODE BUS_FLOW_INV_DIST_ID BUS_FLOW_INV_ID BUS_FLOW_PO_APP_ID BUS_FLOW_PO_DIST_TYPE BUS_FLOW_PO_ENTITY_CODE BUS_FLOW_PO_DOC_ID BF_ACCRUAL_PREPAY_ENTITY_CODE BF_CASH_ALWAYS_PREPAY_ENTITY BF_CASHCLEARONLY_PREPAY_ENTITY BF_ACCRUAL_PREPAY_DIST_TYPE BF_CASH_ALWAYS_PP_DIST_TYPE BF_CASHCLEARONLY_PP_DIST_TYPE BF_PREPAY_REC_DIST_ID BF_PREPAY_REC_INV BF_ACCRUAL_PP_DIST_ID BF_ACCRUAL_PP_INV BF_CASHALWAYS_PP_PMT_ID BF_CASHALWAYS_PP_PMT_DIST_ID BF_CASHCLEAR_PP_CLEAR_DIST_ID AID_PROJECT_ID AID_TASK_ID AID_EXPENDITURE_TYPE AID_EXPENDITURE_ORG_ID AID_AWARD_ID AID_AMOUNT BF_RET_INV_ASSOC_TO_PP_RECIP BF_RET_DIST_ASSOC_TO_PP_RECIP AI_RECIPIENT_INV_TYPE RECP_INV_DIST_TYPE APAD_PREPAY_APP_DIST_ID RECP_INV_DIST_CCID AID_RET_RELATED_DIST_CCID VARIANCE_PARENT_DIST_ID APAD_QUANTITY_VARIANCE APAD_INV_BASE_QTY_VARIANCE APAD_AMOUNT_VARIANCE APAD_INV_BASE_AMT_VARIANCE RECP_DETAIL_TAX_DIST_ID 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_CCID PO_VARIANCE_ACCOUNT RELATED_INV_DIST_DEST_TYPE POD_PO_HEADER_ID POD_PO_LINE_ID POD_LINE_LOCATION_ID UPG_AP_ENCUM_OPTION UPG_CR_ENC_TYPE_ID UPG_ENC_CR_CCID UPG_ENC_CR_BASE_AMT UPG_ENC_CR_AMT UPG_DR_ENC_TYPE_ID UPG_ENC_DR_CCID UPG_ENC_DR_BASE_AMT UPG_ENC_DR_AMT OVERRIDE_ACCTD_AMT_FLAG

Query

SQL_Statement

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 ,decode( APAD.REVERSED_PREPAY_APP_DIST_ID, null, 'N','Y') 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 ,(SELECT 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' 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 ,0 AID_ACCTS_PAY_CCID ,AID.Dist_code_combination_id AID_Dist_CCID ,AID.DESCRIPTION AID_DESCRIPTION ,0 AID_EXCHANGE_RATE_VARIANCE ,0 AID_INVOICE_PRICE_VARIANCE ,0 AID_RATE_VAR_CCID ,0 AID_AMOUNT_ENCUMBERED ,AID.REVERSAL_FLAG AID_REVERSAL_FLAG ,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 ,0 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 ,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 ,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') BF_CashClearOnly_Prepay_Entity ,'AP_INV_DIST' BF_Accrual_Prepay_Dist_Type ,'AP_PMT_DIST' BF_Cash_Always_PP_Dist_Type ,'PO_DIST' BF_CashClearOnly_PP_Dist_Type ,APAD.INVOICE_DISTRIBUTION_ID BF_Prepay_Rec_Dist_id ,APPH.INVOICE_ID BF_PREPAY_REC_INV ,AID.PREPAY_DISTRIBUTION_ID BF_Accrual_PP_Dist_id ,APPH.PREPAY_INVOICE_ID BF_Accrual_PP_INV ,(SELECT max(APH.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' AND AID.prepay_distribution_id = APHD.invoice_distribution_id(+) group by APHD.invoice_distribution_id) BF_CashAlways_PP_Pmt_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 = 'PAYMENT CREATED' AND AID.prepay_distribution_id = APHD.invoice_distribution_id(+) 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 = 'PAYMENT CLEARING' AND AID.prepay_distribution_id = APHD.invoice_distribution_id(+) 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 ,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 /* PSA_BC_XLA_PUB do not exists in FUSION V1 ,CASE WHEN (fsp.req_encumbrance_flag ='Y' or fsp.purch_encumbrance_flag = 'Y') THEN PSA_BC_XLA_PUB.GET_SLA_NOTUPGRADED_FLAG (Decode(nvl(RECP_INV_DISTS.po_distribution_id,-99), -99, 200, 201), Decode(nvl(RECP_INV_DISTS.po_distribution_id,-99), -99, 'AP_INVOICES', Decode (AIL.po_release_id, NULL, 'PURCHASE_ORDER', 'RELEASE')), Decode(nvl(RECP_INV_DISTS.po_distribution_id,-99), -99, APPH.PREPAY_INVOICE_ID, Decode (AIL.po_release_id, NULL, AIL.po_header_id,AIL.po_release_id)), Decode(nvl(RECP_INV_DISTS.po_distribution_id,-99), -99, 'AP_INV_DIST', 'PO_DISTRIBUTIONS_ALL'), Decode(nvl(RECP_INV_DISTS.po_distribution_id,-99), -99, AID.PREPAY_DISTRIBUTION_ID, RECP_INV_DISTS.po_distribution_id) ) ELSE 'N' END "UPG_AP_ENCUM_OPTION" */ ,NULL UPG_AP_ENCUM_OPTION ,Decode(nvl(RECP_INV_DISTS.po_distribution_id,-99), -99, 0, 1001) UPG_CR_ENC_TYPE_ID ,Decode(nvl(RECP_INV_DISTS.po_distribution_id,-99), -99, 0, POD.budget_account_id) UPG_ENC_CR_CCID ,Decode(nvl(RECP_INV_DISTS.po_distribution_id,-99), -99, 0, nvl(APAD.Base_amount, APAD.amount) ) UPG_ENC_CR_BASE_AMT ,Decode(nvl(RECP_INV_DISTS.po_distribution_id,-99), -99, 0, NVL(APAD.amount,0) ) UPG_ENC_CR_AMT ,0 UPG_DR_ENC_TYPE_ID ,AID.dist_code_combination_id UPG_ENC_DR_CCID ,nvl(APAD.Base_amount, APAD.amount) UPG_ENC_DR_BASE_AMT ,NVL(APAD.amount,0) UPG_ENC_DR_AMT ,'Y' OVERRIDE_ACCTD_AMT_FLAG 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_INVOICES_ALL AI, AP_INVOICE_LINES_ALL RECP_INV_LINES, AP_INVOICE_DISTRIBUTIONS_ALL RECP_INV_DISTS, ap_invoice_distributions_all recp_inv_retainage_dists, PO_DISTRIBUTIONS_ALL POD, FINANCIALS_SYSTEM_PARAMS_ALL FSP WHERE XE.Event_ID = APPH.bc_Event_ID AND AID.bc_event_id = APPH.bc_Event_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 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.po_distribution_id = POD.po_distribution_id(+) AND APPH.org_id = FSP.org_id