AP_PAYMENT_EXTRACT_DETAILS_V

Details

• Schema: FUSION

• Object owner: AP

• Object type: VIEW

Columns

Name

EVENT_ID APHD_PAY_DIST_LOOKUP_CODE LINE_NUMBER DIST_ACCOUNT_REVERSAL_OPTION APHD_REV_PAY_HIST_DIST_ID THIRD_PARTY_TYPE AI_INVOICE_CURRENCY_CODE APHD_REVERSAL_FLAG APHD_AMOUNT APHD_BANK_CURR_AMOUNT APHD_HISTORICAL_FLAG APHD_INVOICE_DIST_BASE_AMT APHD_PAYMENT_HIST_DIST_ID APHD_PAYMENT_HISTORY_ID APHD_INVOICE_DISTRIBUTION_ID APHD_INVOICE_PAYMENT_ID INV_PMT_BASE_AMT_DIFF PMT_MAT_BASE_AMT_DIFF MAT_CLR_BASE_AMT_DIFF PMT_CLR_BASE_AMT_DIFF INV_CLR_BASE_AMT_DIFF AIP_ACCOUNTING_DATE AIP_AMOUNT AIP_INVOICE_ID AIP_INVOICE_PAYMENT_ID AIP_PAYMENT_NUM AIP_PERIOD_NAME AIP_ACCTS_PAY_CCID AIP_ASSET_CCID 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_CAT 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 AIP_INV_ORG_ID AID_DIST_CCID AID_AMOUNT AID_AWT_FLAG AID_LINE_TYPE_LOOKUP_CODE AID_DISTRIBUTION_LINE_NUMBER AWTG_NAME AID_AWARD_ID AID_INVOICE_DIST_ID AID_INCOME_TAX_REGION AWT_AT_PMT_TIME AID_MERCHANT_DOCUMENT_NUMBER AID_MERCHANT_REFERENCE AID_MERCHANT_TAXPAYER_ID AID_MERCHANT_TAX_REG_NUMBER AI_INVOICE_AMOUNT AI_ACCTS_PAY_CCID DISTRIBUTION_LINK_TYPE PO_DISTRIBUTION_ID AWT_RELATED_DIST_ACCOUNT AI_INVOICE_DATE AI_INVOICE_TYPE_PAID INV_DISCOUNT_AMOUNT_TAKEN LEDGER_ID INV_EXCHANGE_DATE INV_EXCHANGE_RATE INV_EXCHANGE_RATE_TYPE INVOICE_NUM_DURING_PAYMENT APHD_INVOICE_DIST_AMOUNT APHD_CLEARING_BASE_AMT APHD_INVOICE_BASE_AMT APHD_MATURED_BASE_AMOUNT APHD_PAYMENT_BASE_AMT INV_PMT_GAIN_LOSS_INDICATOR INV_CLR_GAIN_LOSS_INDICATOR PMT_MAT_GAIN_LOSS_INDICATOR MAT_CLR_GAIN_LOSS_INDICATOR PMT_CLR_GAIN_LOSS_INDICATOR APHD_ROUNDING_AMT APHD_PMT_BASE_NO_ROUND APHD_CLEAR_BASE_AMT_NO_ROUND APHD_MAT_BASE_AMT_NO_ROUND 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_PAYMENT_DIST_TYPE BUS_FLOW_PAYMENT_ENTITY_CODE BUS_FLOW_PAYMENT_DIST_ID BUS_FLOW_PAYMENT_ID AID_PROJECT_ID AID_TASK_ID BF_RETAINED_INV_ID_PMT BF_RETAINED_INV_DIST_ID_PMT BF_RETAINED_INV_ID_CLR BF_RETAINED_INV_DIST_ID_CLR AID_RET_RELATED_DIST_CCID AID_AWT_RELATED_ID APHD_QUANTITY_VARIANCE APHD_INV_BASE_QTY_VARIANCE APHD_AMOUNT_VARIANCE APHD_INV_BASE_AMT_VARIANCE AID_DETAIL_TAX_DIST_ID AID_DETAIL_TAX_LINE_ID AID_ENCUMBERED_FLAG POD_PO_DISTRIBUTION_ID POD_ENCUMBERED_FLAG POD_ENCUMBERED_AMOUNT POD_GL_ENCUMBERED_DATE POD_GL_ENCUMBERED_PERIOD_NAM 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 BUS_FLOW_PAYMENT_MAT_DIST_ID OVERRIDE_ACCTD_AMT_FLAG AI_PAYMENT_STATUS_FLAG ENCUMBRANCE_AMOUNT AWT_RELATED_DIST_TYPE POS_ATTRIBUTE1 POS_ATTRIBUTE10 POS_ATTRIBUTE11 POS_ATTRIBUTE12 POS_ATTRIBUTE13 POS_ATTRIBUTE14 POS_ATTRIBUTE15 POS_ATTRIBUTE2 POS_ATTRIBUTE3 POS_ATTRIBUTE4 POS_ATTRIBUTE5 POS_ATTRIBUTE6 POS_ATTRIBUTE7 POS_ATTRIBUTE8 POS_ATTRIBUTE9 POS_ATTRIBUTE_CATEGORY POS_ADDRESS_LINE1 POS_ADDRESS_LINE2 POS_ADDRESS_LINE3 POS_ADDRESS_LINE4 POS_ADDRESS_LINES_ALT POS_CITY POS_COUNTY POS_COUNTRY POS_VENDOR_SITE_CODE POS_ZIP POS_PROVINCE POS_STATE POS_VENDOR_SITE_ID POS_GLOBAL_ATTRIBUTE_1 POS_GLOBAL_ATTRIBUTE_2 POS_GLOBAL_ATTRIBUTE_3 POS_GLOBAL_ATTRIBUTE_4 POS_GLOBAL_ATTRIBUTE_5 POS_GLOBAL_ATTRIBUTE_6 POS_GLOBAL_ATTRIBUTE_7 POS_GLOBAL_ATTRIBUTE_8 POS_GLOBAL_ATTRIBUTE_9 POS_GLOBAL_ATTRIBUTE_10 POS_GLOBAL_ATTRIBUTE_11 POS_GLOBAL_ATTRIBUTE_12 POS_GLOBAL_ATTRIBUTE_13 POS_GLOBAL_ATTRIBUTE_14 POS_GLOBAL_ATTRIBUTE_15 POS_GLOBAL_ATTRIBUTE_16 POS_GLOBAL_ATTRIBUTE_17 POS_GLOBAL_ATTRIBUTE_18 POS_GLOBAL_ATTRIBUTE_19 POS_GLOBAL_ATTRIBUTE_20 POS_GLOBAL_ATTRIBUTE_CATEGORY POS_PARTY_SITE_ID POS_ORG_ID POS_VENDOR_ID AC_AI_CURRENCY_CODE AC_AI_EXCHANGE_RATE AC_AI_EXCHANGE_DATE AC_AI_EXCHANGE_RATE_TYPE AID_PAY_CHARGE_TO_ITEM_CCID PAY_CHARGE_PO_DIST_CCID AI_INVOICE_SOURCE

Query

SQL_Statement

SELECT /*+ leading(xe) cardinality(xe,1) */ APHD.Accounting_Event_id Event_Id ,APHD.pay_dist_lookup_code APHD_pay_dist_lookup_code ,APHD.payment_hist_dist_id Line_Number ,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') Dist_Account_Reversal_Option ,APHD.reversed_pay_hist_dist_id APHD_rev_pay_hist_dist_id ,'S' Third_Party_Type ,AI.Invoice_Currency_code AI_Invoice_Currency_Code ,APHD.reversal_flag APHD_Reversal_Flag ,APHD.amount APHD_amount ,APHD.bank_curr_amount APHD_Bank_Curr_Amount ,APHD.historical_flag APHD_Historical_Flag ,nvl(APHD.invoice_dist_base_amount, APHD.invoice_dist_amount) APHD_Invoice_Dist_Base_Amt ,APHD.payment_hist_dist_id APHD_Payment_Hist_Dist_Id ,APHD.payment_history_id APHD_Payment_History_Id ,APHD.invoice_distribution_id APHD_Invoice_Distribution_Id ,APHD.invoice_payment_id APHD_Invoice_Payment_Id ,nvl(APHD.invoice_dist_base_amount, APHD.invoice_dist_amount)- nvl(APHD.paid_base_amount, APHD.amount) Inv_Pmt_Base_Amt_Diff ,nvl(APHD.paid_base_amount, APHD.amount) - nvl(APHD.matured_base_amount, APHD.amount) Pmt_Mat_Base_Amt_Diff ,nvl(APHD.matured_base_amount, APHD.amount) - nvl(APHD.cleared_base_amount, APHD.amount) Mat_Clr_Base_Amt_Diff ,nvl(APHD.paid_base_amount, APHD.amount) - nvl(APHD.cleared_base_amount, APHD.amount) Pmt_Clr_Base_Amt_Diff ,nvl(APHD.invoice_dist_base_amount, APHD.invoice_dist_amount) - nvl(APHD.cleared_base_amount, APHD.amount) Inv_Clr_Base_Amt_Diff ,AIP.accounting_date AIP_accounting_date ,AIP.amount AIP_amount ,AIP.invoice_id AIP_invoice_id ,AIP.invoice_payment_id AIP_invoice_payment_id ,AIP.payment_num AIP_payment_num ,AIP.period_name AIP_period_name ,AIP.accts_pay_code_combination_id AIP_accts_pay_ccid ,AIP.asset_code_combination_id AIP_asset_ccid ,AIP.attribute1 AIP_attribute1 ,AIP.attribute10 AIP_attribute10 ,AIP.attribute11 AIP_attribute11 ,AIP.attribute12 AIP_attribute12 ,AIP.attribute13 AIP_attribute13 ,AIP.attribute14 AIP_attribute14 ,AIP.attribute15 AIP_attribute15 ,AIP.attribute2 AIP_attribute2 ,AIP.attribute3 AIP_attribute3 ,AIP.attribute4 AIP_attribute4 ,AIP.attribute5 AIP_attribute5 ,AIP.attribute6 AIP_attribute6 ,AIP.attribute7 AIP_attribute7 ,AIP.attribute8 AIP_attribute8 ,AIP.attribute9 AIP_attribute9 ,AIP.attribute_category AIP_attribute_category ,AIP.invoice_payment_type AIP_invoice_payment_type ,AIP.global_attribute_category AIP_global_attribute_cat ,AIP.global_attribute1 AIP_global_attribute1 ,AIP.global_attribute2 AIP_global_attribute2 ,AIP.global_attribute3 AIP_global_attribute3 ,AIP.global_attribute4 AIP_global_attribute4 ,AIP.global_attribute5 AIP_global_attribute5 ,AIP.global_attribute6 AIP_global_attribute6 ,AIP.global_attribute7 AIP_global_attribute7 ,AIP.global_attribute8 AIP_global_attribute8 ,AIP.global_attribute9 AIP_global_attribute9 ,AIP.global_attribute10 AIP_global_attribute10 ,AIP.global_attribute11 AIP_global_attribute11 ,AIP.global_attribute12 AIP_global_attribute12 ,AIP.global_attribute13 AIP_global_attribute13 ,AIP.global_attribute14 AIP_global_attribute14 ,AIP.global_attribute15 AIP_global_attribute15 ,AIP.global_attribute16 AIP_global_attribute16 ,AIP.global_attribute17 AIP_global_attribute17 ,AIP.global_attribute18 AIP_global_attribute18 ,AIP.global_attribute19 AIP_global_attribute19 ,AIP.global_attribute20 AIP_global_attribute20 ,AIP.external_bank_account_id AIP_External_Bank_Account_Id ,AIP.accounting_event_id AIP_Accounting_Event_Id ,AIP.reversal_flag AIP_Reversal_Flag ,AIP.reversal_inv_pmt_id AIP_Reversal_Inv_Pmt_Id ,NVL(AIP.inv_org_id, AIP.org_id) AIP_Inv_Org_Id ,AID.dist_code_combination_id AID_Dist_CCID ,AID.amount AID_Amount ,AID.awt_flag AID_AWT_Flag ,AID.Line_type_lookup_code AID_Line_type_lookup_code ,AID.distribution_line_number AID_distribution_line_number ,ZCG.condition_group_name AWTG_name ,AID.award_id AID_award_id ,AID.invoice_distribution_id AID_invoice_dist_id ,AID.income_tax_region AID_income_tax_region ,decode(AID.awt_invoice_payment_id ,null,'N','Y') AWT_at_pmt_time ,AID.merchant_document_number AID_merchant_document_number ,AID.merchant_reference AID_merchant_reference ,AID.merchant_taxpayer_id AID_merchant_taxpayer_id ,AID.merchant_tax_reg_number AID_merchant_tax_reg_number ,AI.invoice_amount AI_invoice_amount ,AI.accts_pay_code_combination_id AI_accts_pay_ccid ,'AP_PMT_DIST' Distribution_link_type ,AID.po_distribution_id PO_distribution_id ,AWT_RELATED_IDS.dist_code_combination_id AWT_Related_dist_account ,AI.Invoice_Date AI_Invoice_Date ,AI.Invoice_type_lookup_code AI_Invoice_type_paid ,AI.discount_amount_taken Inv_discount_amount_taken ,AIP.set_of_books_id Ledger_id ,AI.Exchange_Date Inv_Exchange_Date ,AI.Exchange_Rate Inv_Exchange_Rate ,AI.Exchange_Rate_Type Inv_Exchange_Rate_Type ,AI.Invoice_Num INVOICE_NUM_DURING_PAYMENT ,APHD.invoice_dist_amount 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 APHD_Clearing_Base_Amt ,nvl(APHD.invoice_dist_base_amount, APHD.invoice_dist_amount) APHD_Invoice_Base_Amt ,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 APHD_Matured_Base_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.paid_base_amount, APHD.amount) END APHD_Payment_Base_Amt ,DECODE(APHD.Gain_Loss_Indicator, 'G', 'GAIN', 'L', 'LOSS', 'NONE') Inv_Pmt_Gain_loss_Indicator ,DECODE(APHD.Gain_Loss_Indicator, 'G', 'GAIN', 'L', 'LOSS', 'NONE') Inv_Clr_Gain_loss_Indicator ,DECODE(APHD.Gain_Loss_Indicator, 'G', 'GAIN', 'L', 'LOSS', 'NONE') Pmt_Mat_Gain_loss_Indicator ,DECODE(APHD.Gain_Loss_Indicator, 'G', 'GAIN', 'L', 'LOSS', 'NONE') Mat_Clr_Gain_loss_Indicator ,DECODE(APHD.Gain_Loss_Indicator, 'G', 'GAIN', 'L', 'LOSS', 'NONE') Pmt_Clr_Gain_loss_Indicator ,nvl(APHD.Rounding_Amt,0) APHD_Rounding_Amt ,nvl(APHD.paid_base_amount, APHD.amount) - nvl(APHD.Rounding_Amt,0) APHD_pmt_base_no_round ,nvl(APHD.cleared_base_amount, APHD.amount) - nvl(APHD.Rounding_Amt,0) APHD_Clear_Base_Amt_no_round ,nvl(APHD.matured_base_amount, APHD.amount) - nvl(APHD.Rounding_Amt,0) APHD_Mat_Base_Amt_no_round ,200 Bus_Flow_AP_App_Id ,'AP_INV_DIST' Bus_Flow_Inv_Dist_Type ,'AP_INVOICES' Bus_Flow_Inv_Entity_Code ,decode(APHD.pay_dist_lookup_code,'AWT', APHD.awt_related_id,AID.invoice_distribution_Id) Bus_Flow_Inv_Dist_Id ,AID.invoice_id Bus_Flow_Inv_Id ,201 Bus_Flow_PO_App_Id ,'PO_DIST' Bus_Flow_PO_Dist_Type ,'PURCHASE ORDER' Bus_Flow_PO_Entity_Code ,'AP_PMT_DIST' Bus_Flow_Payment_Dist_Type ,'AP_PAYMENTS' Bus_Flow_Payment_Entity_Code ,(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') 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 Bus_Flow_Payment_Id ,AID.Project_ID AID_Project_ID ,AID.Task_Id AID_Task_Id ,RET_DISTS.retained_invoice_id BF_Retained_Inv_Id_Pmt ,RET_DISTS.retained_invoice_dist_id BF_Retained_Inv_Dist_Id_Pmt ,RET_DISTS.retained_invoice_id BF_Retained_Inv_Id_Clr ,RET_DISTS.retained_invoice_dist_id BF_Retained_Inv_Dist_Id_Clr ,-1 AID_Ret_Related_Dist_Ccid ,AID.AWT_RELATED_ID aid_awt_related_id ,APHD.Quantity_Variance APHD_Quantity_Variance ,APHD.Invoice_Base_Qty_Variance APHD_Inv_Base_Qty_Variance ,APHD.Amount_Variance APHD_Amount_Variance ,APHD.Invoice_Base_Amt_Variance APHD_Inv_Base_Amt_Variance ,decode(AID.Line_type_lookup_code ,'AWT' ,Decode((select TAX_POINT_BASIS from ZX_AP_DEF_TAX_EXTRACT_V ZDT where ZDT.rec_nrec_tax_dist_id =AWT_RELATED_IDS.detail_tax_dist_id ) ,'PAYMENT' ,AWT_RELATED_IDS.detail_tax_dist_id ,AID.DETAIL_TAX_DIST_ID) ,AID.DETAIL_TAX_DIST_ID) AID_DETAIL_TAX_DIST_ID ,AID.DETAIL_TAX_LINE_ID AID_DETAIL_TAX_LINE_ID ,AID.ENCUMBERED_FLAG AID_ENCUMBERED_FLAG ,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_Nam ,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 ,(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.TAX_POINT_BASIS from ZX_AP_DEF_TAX_EXTRACT_V ZDT where ZDT.rec_nrec_tax_dist_id =AWT_RELATED_IDS.detail_tax_dist_id) ,'PAYMENT' ,'AWT_DEFERRED' ,null) ,null) AWT_RELATED_DIST_TYPE ,decode(sign(AC.VENDOR_ID), 1, POS.attribute1, HPS.attribute1 ) POS_Attribute1 ,decode(sign(AC.VENDOR_ID), 1, POS.attribute10,HPS.attribute10) POS_Attribute10 ,decode(sign(AC.VENDOR_ID), 1, POS.attribute11,HPS.attribute11 ) POS_Attribute11 ,decode(sign(AC.VENDOR_ID), 1, POS.attribute12,HPS.attribute12) POS_Attribute12 ,decode(sign(AC.VENDOR_ID), 1, POS.attribute13,HPS.attribute13) POS_Attribute13 ,decode(sign(AC.VENDOR_ID), 1, POS.attribute14,HPS.attribute14) POS_Attribute14 ,decode(sign(AC.VENDOR_ID), 1, POS.attribute15,HPS.attribute15) POS_Attribute15 ,decode(sign(AC.VENDOR_ID), 1, POS.attribute2,HPS.attribute2) POS_Attribute2 ,decode(sign(AC.VENDOR_ID), 1, POS.attribute3,HPS.attribute3) POS_Attribute3 ,decode(sign(AC.VENDOR_ID), 1, POS.attribute4,HPS.attribute4) POS_Attribute4 ,decode(sign(AC.VENDOR_ID), 1, POS.attribute5,HPS.attribute5) POS_Attribute5 ,decode(sign(AC.VENDOR_ID), 1, POS.attribute6,HPS.attribute6) POS_Attribute6 ,decode(sign(AC.VENDOR_ID), 1, POS.attribute7,HPS.attribute7) POS_Attribute7 ,decode(sign(AC.VENDOR_ID), 1, POS.attribute8,HPS.attribute8) POS_Attribute8 ,decode(sign(AC.VENDOR_ID), 1, POS.attribute9,HPS.attribute9) POS_Attribute9 ,decode(sign(AC.VENDOR_ID), 1, POS.attribute_category,HPS.attribute_category) POS_Attribute_category ,decode(sign(AC.VENDOR_ID), 1, POS.address_line1, HL.address1 ) POS_address_line1 ,decode(sign(AC.VENDOR_ID), 1, POS.address_line2,HL.address2) POS_address_line2 ,decode(sign(AC.VENDOR_ID), 1, POS.address_line3,HL.address3) POS_address_line3 ,decode(sign(AC.VENDOR_ID), 1, POS.address_line4,HL.address4) POS_address_line4 ,decode(sign(AC.VENDOR_ID), 1, POS.address_lines_alt,HL.ADDRESS_LINES_PHONETIC) POS_address_lines_alt ,decode(sign(AC.VENDOR_ID), 1, POS.city,HL.city) POS_City ,decode(sign(AC.VENDOR_ID), 1, POS.county, HL.county) POS_County ,decode(sign(AC.VENDOR_ID), 1, POS.country, HL.country) POS_Country ,decode(sign(AC.VENDOR_ID), 1, POS.vendor_site_code, NULL) POS_Vendor_Site_Code ,decode(sign(AC.VENDOR_ID), 1, POS.Zip,substr(HL.POSTAL_CODE, 1, 20)) POS_Zip ,decode(sign(AC.VENDOR_ID), 1, POS.province, HL.province) POS_Province ,decode(sign(AC.VENDOR_ID), 1, POS.State, HL.State) POS_State ,decode(sign(AC.VENDOR_ID), 1, POS.Vendor_Site_id, NULL) POS_Vendor_Site_Id ,decode(sign(AC.VENDOR_ID), 1, POS.GLOBAL_ATTRIBUTE1, NULL) POS_Global_Attribute_1 ,decode(sign(AC.VENDOR_ID), 1, POS.GLOBAL_ATTRIBUTE2,NULL) POS_Global_Attribute_2 ,decode(sign(AC.VENDOR_ID), 1, POS.GLOBAL_ATTRIBUTE3,NULL) POS_Global_Attribute_3 ,decode(sign(AC.VENDOR_ID), 1, POS.GLOBAL_ATTRIBUTE4,NULL) POS_Global_Attribute_4 ,decode(sign(AC.VENDOR_ID), 1, POS.GLOBAL_ATTRIBUTE5,NULL) POS_Global_Attribute_5 ,decode(sign(AC.VENDOR_ID), 1, POS.GLOBAL_ATTRIBUTE6,NULL) POS_Global_Attribute_6 ,decode(sign(AC.VENDOR_ID), 1, POS.GLOBAL_ATTRIBUTE7,NULL) POS_Global_Attribute_7 ,decode(sign(AC.VENDOR_ID), 1, POS.GLOBAL_ATTRIBUTE8,NULL) POS_Global_Attribute_8 ,decode(sign(AC.VENDOR_ID), 1, POS.GLOBAL_ATTRIBUTE9,NULL) POS_Global_Attribute_9 ,decode(sign(AC.VENDOR_ID), 1, POS.GLOBAL_ATTRIBUTE10,NULL) POS_Global_Attribute_10 ,decode(sign(AC.VENDOR_ID), 1, POS.GLOBAL_ATTRIBUTE11,NULL) POS_Global_Attribute_11 ,decode(sign(AC.VENDOR_ID), 1, POS.GLOBAL_ATTRIBUTE12,NULL) POS_Global_Attribute_12 ,decode(sign(AC.VENDOR_ID), 1, POS.GLOBAL_ATTRIBUTE13,NULL) POS_Global_Attribute_13 ,decode(sign(AC.VENDOR_ID), 1, POS.GLOBAL_ATTRIBUTE14,NULL) POS_Global_Attribute_14 ,decode(sign(AC.VENDOR_ID), 1, POS.GLOBAL_ATTRIBUTE15,NULL) POS_Global_Attribute_15 ,decode(sign(AC.VENDOR_ID), 1, POS.GLOBAL_ATTRIBUTE16,NULL) POS_Global_Attribute_16 ,decode(sign(AC.VENDOR_ID), 1, POS.GLOBAL_ATTRIBUTE17,NULL) POS_Global_Attribute_17 ,decode(sign(AC.VENDOR_ID), 1, POS.GLOBAL_ATTRIBUTE18,NULL) POS_Global_Attribute_18 ,decode(sign(AC.VENDOR_ID), 1, POS.GLOBAL_ATTRIBUTE19,NULL) POS_Global_Attribute_19 ,decode(sign(AC.VENDOR_ID), 1, POS.GLOBAL_ATTRIBUTE20,NULL) POS_Global_Attribute_20 ,decode(sign(AC.VENDOR_ID), 1, POS.GLOBAL_ATTRIBUTE_CATEGORY,NULL) POS_Global_Attribute_Category ,decode(sign(AC.VENDOR_ID), 1, POS.PARTY_SITE_ID,HPS.PARTY_SITE_ID) POS_PARTY_SITE_ID ,decode(sign(AC.VENDOR_ID), 1, POS.BU_ID, NULL) POS_ORG_ID ,decode(sign(AC.VENDOR_ID), 1, POS.vendor_id, NULL) POS_VENDOR_ID , CASE WHEN AI.INVOICE_CURRENCY_CODE <> AC.CURRENCY_CODE AND AC.CURRENCY_CODE = ASP.BASE_CURRENCY_CODE THEN AI.INVOICE_CURRENCY_CODE ELSE AC.CURRENCY_CODE END AC_AI_CURRENCY_CODE , CASE WHEN AI.INVOICE_CURRENCY_CODE <> AC.CURRENCY_CODE AND AC.CURRENCY_CODE = ASP.BASE_CURRENCY_CODE THEN AI.EXCHANGE_RATE ELSE AC.EXCHANGE_RATE END AC_AI_EXCHANGE_RATE , CASE WHEN AI.INVOICE_CURRENCY_CODE <> AC.CURRENCY_CODE AND AC.CURRENCY_CODE = ASP.BASE_CURRENCY_CODE THEN AI.EXCHANGE_DATE ELSE AC.EXCHANGE_DATE END AC_AI_EXCHANGE_DATE , CASE WHEN AI.INVOICE_CURRENCY_CODE <> AC.CURRENCY_CODE AND AC.CURRENCY_CODE = ASP.BASE_CURRENCY_CODE THEN AI.EXCHANGE_RATE_TYPE ELSE AC.EXCHANGE_RATE_TYPE END AC_AI_EXCHANGE_RATE_TYPE , 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_PAY_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 PAY_CHARGE_PO_DIST_CCID , AI.SOURCE AI_INVOICE_SOURCE 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, ZX_CONDITION_GROUPS_VL ZCG, 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, AP_POZ_SITE_ADDR_ASSIGNMENT_V POS, AP_CHECKS_ALL AC, HZ_PARTY_SITES HPS, HZ_LOCATIONS HL WHERE APHD.payment_history_id = APH.payment_history_id AND APHD.invoice_payment_id = AIP.invoice_payment_id AND APH.check_id = AC.check_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 = ZCG.condition_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 AC.vendor_site_id = POS.vendor_site_id (+) AND ( POS.bu_id is null OR ( POS.bu_id is not null AND nvl(AIP.inv_org_id,AIP.org_id) = POS.bu_id)) AND AC.PARTY_SITE_ID = HPS.PARTY_SITE_ID(+) AND HPS.LOCATION_ID = HL.LOCATION_ID(+)