AP_PREPAYAPP_EXTRACT_DETAILS_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_DIST_CCID AID_DESCRIPTION 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 AID_DETAIL_TAX_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_MAT_DATE AC_PAYMENT_MODE_CODE BF_CASHALWAYS_PP_PMT_DIST_ID BF_CASHALWAYS_PP_MAT_DIST_ID BF_CASHCLEAR_PP_CLEAR_DIST_ID AID_PROJECT_ID AID_TASK_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 PP_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 OVERRIDE_ACCTD_AMT_FLAG CGAC_GAIN_CCID CGAC_LOSS_CCID AWT_RELATED_DIST_ACCOUNT AWT_RELATED_LINE_TYPE_LOOKUP AWT_AT_PMT_TIME INVOICE_NUM_DURING_PREPAY AIL_CREATION_DATE AIL_CREATED_BY AID_PREPAY_CHARGE_TO_ITEM_CCID PREPAY_CHARGE_PO_DIST_CCID PREP_SLA_BC_COMPLETE_STATUS PREP_DATA_SET_ID

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', DECODE(XE.event_type_code, 'PREPAYMENT APPLICATION ADJ', 'N', DECODE(APAD.prepay_dist_lookup_code, 'AWT', 'N', 'EXCHANGE RATE VARIANCE', 'N', 'TAX EXCHANGE RATE VARIANCE', '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 /*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.Dist_code_combination_id AID_Dist_CCID ,AID.DESCRIPTION AID_DESCRIPTION ,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 ,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 ,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 /*bug6970538*/ ,AC.check_id BF_CashAlways_PP_Pmt_Id ,AC.future_pay_due_date BF_CashAlways_PP_Mat_Date ,AC.payment_mode_code AC_payment_mode_code ,(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') 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_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 ,(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') AND AID.prepay_distribution_id = APHD.invoice_distribution_id(+) AND APH.check_id = AC.check_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.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 ,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 ,'Y' OVERRIDE_ACCTD_AMT_FLAG , CGAC.gain_code_combination_id CGAC_GAIN_CCID , CGAC.loss_code_combination_id CGAC_LOSS_CCID /*bug11702556*/ , AWT_RELATED_IDS.dist_code_combination_id AWT_RELATED_DIST_ACCOUNT , AWT_RELATED_IDS.line_type_lookup_code AWT_RELATED_LINE_TYPE_LOOKUP ,DECODE(AID.awt_invoice_payment_id,null,'N','Y') AWT_AT_PMT_TIME ,(select invoice_num from ap_invoices_all where invoice_id = apph.prepay_invoice_id) INVOICE_NUM_DURING_PREPAY , cast(ail.creation_date as DATE) ail_creation_date , ail.created_by ail_created_by ,CASE WHEN AID.line_type_lookup_code in ( 'REC_TAX' , 'NONREC_TAX') THEN (select item.dist_code_combination_id from ap_invoice_distributions_all item Where item.invoice_distribution_id = aid.charge_applicable_to_dist_id) ELSE NULL END AID_PREPAY_CHARGE_TO_ITEM_CCID ,CASE WHEN AID.line_type_lookup_code in ( 'REC_TAX' , 'NONREC_TAX') THEN (select pda.code_combination_id from ap_invoice_distributions_all item , po_distributions_all pda Where item.invoice_distribution_id = aid.charge_applicable_to_dist_id and item.po_distribution_id=pda.po_distribution_id) ELSE NULL END PREPAY_CHARGE_PO_DIST_CCID /*Bug 35746250*/ ,CASE WHEN (AID.funds_status LIKE 'RESERVED%' AND AID.funds_status NOT IN ( 'RESERVED_NOT_APPLICABLE', 'NOT_REQUIRED_BY_SUBLEDGER') AND AID.LINE_TYPE_LOOKUP_CODE NOT IN ('REC_TAX', 'AWT', 'ERV', 'TERV') ) THEN 'Y' WHEN AID.funds_status = 'RESERVED_NOT_APPLICABLE' then 'N' WHEN AID.funds_status = 'NOT_ATTEMPTED' then NULL WHEN AID.funds_status = 'NOT_REQUIRED_BY_SUBLEDGER' THEN 'R' ELSE NULL END PREP_SLA_BC_COMPLETE_STATUS ,AID.DATA_SET_ID PREP_DATA_SET_ID 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, AP_INVOICES_ALL AI, /*bug6970538 (SELECT ma x(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, /*bug11702556*/ AP_INVOICE_DISTRIBUTIONS_ALL AWT_RELATED_IDS, ap_invoice_distributions_all recp_inv_retainage_dists, PO_DISTRIBUTIONS_ALL POD, AP_CHECKS_ALL AC, CE_GL_ACCOUNTS_CCID CGAC 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 AND AC.check_id = ( select check_id from AP_INVOICE_PAYMENTS_ALL AIP,AP_INVOICES_ALL AI2 where AIP.invoice_id = APPH.prepay_invoice_id and AI2.invoice_id = AIP.invoice_id and rownum = 1 and (nvl(reversal_flag, 'N')<> 'Y' OR AI2.cancelled_date is not null) ) AND ac.ce_bank_acct_use_id = CGAC.bank_acct_use_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 /*bug11702556*/ 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