AP_PAYMENT_EXTRACT_HEADER_V

Details

• Schema: FUSION

• Object owner: AP

• Object type: VIEW

Columns

Name

EVENT_ID PAYMENT_TRANSACTION_NUMBER PAYMENT_TYPE PAYMENT_DOCUMENT_TYPE AC_PAYMENT_MODE_CODE PAYMENT_DOCUMENT_SEQUENCE_ID DOC_SEQUENCE_NAME THIRD_PARTY_TYPE CBA_POOLED_BANK_ACOUNT_FLAG AC_CHECK_ID AC_PROVINCE AC_ACTUAL_VALUE_DATE AC_ADDRESS_LINE_1 AC_ADDRESS_LINE_2 AC_ADDRESS_LINE_3 AC_ADDRESS_LINE_4 AC_AMOUNT AC_ATTRIBUTE_CATEGORY AC_ATTRIBUTE1 AC_ATTRIBUTE10 AC_ATTRIBUTE11 AC_ATTRIBUTE12 AC_ATTRIBUTE13 AC_ATTRIBUTE14 AC_ATTRIBUTE15 AC_ATTRIBUTE2 AC_ATTRIBUTE3 AC_ATTRIBUTE4 AC_ATTRIBUTE5 AC_ATTRIBUTE6 AC_ATTRIBUTE7 AC_ATTRIBUTE8 AC_ATTRIBUTE9 AC_CHECK_DATE AC_CHECK_VOUCHER_NUMBER AC_CHECKRUN_NAME AC_CITY AC_COUNTRY AC_COUNTY AC_DESCRIPTION AC_DOC_CATEGORY_CODE AC_DOC_SEQUENCE_VALUE AC_FUTURE_PAY_DUE_DATE AC_STATE AC_VENDOR_NAME AC_VOID_DATE AC_ZIP CBA_ACCOUNT_CLASSIFICATION CBA_BANK_ACCOUNT_NAME CBA_BANK_ACCOUNT_NAME_ALT CBA_BANK_ACCOUNT_NUM CBA_BANK_ACCOUNT_TYPE CBA_DESCRIPTION CBA_IBAN_NUMBER AC_VENDOR_ID AC_VENDOR_SITE_ID AC_ORG_ID AC_GLOBAL_ATTRIBUTE_CATEGORY AC_GLOBAL_ATTRIBUTE_1 AC_GLOBAL_ATTRIBUTE_2 AC_GLOBAL_ATTRIBUTE_3 AC_GLOBAL_ATTRIBUTE_4 AC_GLOBAL_ATTRIBUTE_5 AC_GLOBAL_ATTRIBUTE_6 AC_GLOBAL_ATTRIBUTE_7 AC_GLOBAL_ATTRIBUTE_8 AC_GLOBAL_ATTRIBUTE_9 AC_GLOBAL_ATTRIBUTE_10 AC_GLOBAL_ATTRIBUTE_11 AC_GLOBAL_ATTRIBUTE_12 AC_GLOBAL_ATTRIBUTE_13 AC_GLOBAL_ATTRIBUTE_14 AC_GLOBAL_ATTRIBUTE_15 AC_GLOBAL_ATTRIBUTE_16 AC_GLOBAL_ATTRIBUTE_17 AC_GLOBAL_ATTRIBUTE_18 AC_GLOBAL_ATTRIBUTE_19 AC_GLOBAL_ATTRIBUTE_20 AC_EXTERNAL_BANK_ACCOUNT_ID APH_PAYMENT_HISTORY_ID APH_ACCOUNTING_DATE APH_TRANSACTION_TYPE APH_REV_PMT_HIST_ID APH_BANK_CURR_AMOUNT APH_AMOUNT APH_HISTORICAL_FLAG CGAC_CASH_CLEARING_CCID CGAC_AP_ASSET_CCID CGAC_FUTURE_DATED_PMT_CCID TRANSAC_REVERSAL_FLAG AC_PARTY_ID AC_PARTY_SITE_ID AC_CURRENCY_CODE AC_EXCHANGE_DATE AC_EXCHANGE_RATE AC_EXCHANGE_RATE_TYPE AC_LEDGER_AMOUNT APH_BASE_AMOUNT APH_BANK_CURRENCY_CODE APH_BANK_TO_BASE_XRATE_TYPE APH_BANK_TO_BASE_XRATE_DATE APH_BANK_TO_BASE_XRATE APH_PMT_CURRENCY_CODE APH_PMT_TO_BASE_XRATE_TYPE APH_PMT_TO_BASE_XRATE_DATE APH_PMT_TO_BASE_XRATE APH_INVOICE_ADJ_EVENT_ID POV_ATTRIBUTE_CATEGORY POV_ATTRIBUTE1 POV_ATTRIBUTE10 POV_ATTRIBUTE11 POV_ATTRIBUTE12 POV_ATTRIBUTE13 POV_ATTRIBUTE14 POV_ATTRIBUTE15 POV_ATTRIBUTE2 POV_ATTRIBUTE3 POV_ATTRIBUTE4 POV_ATTRIBUTE5 POV_ATTRIBUTE6 POV_ATTRIBUTE7 POV_ATTRIBUTE8 POV_ATTRIBUTE9 POV_VENDOR_NAME_ALT POV_VENDOR_ID POV_SEGMENT1 POV_VENDOR_NAME POV_GLOBAL_ATTRIBUTE_1 POV_GLOBAL_ATTRIBUTE_2 POV_GLOBAL_ATTRIBUTE_3 POV_GLOBAL_ATTRIBUTE_4 POV_GLOBAL_ATTRIBUTE_5 POV_GLOBAL_ATTRIBUTE_6 POV_GLOBAL_ATTRIBUTE_7 POV_GLOBAL_ATTRIBUTE_8 POV_GLOBAL_ATTRIBUTE_9 POV_GLOBAL_ATTRIBUTE_10 POV_GLOBAL_ATTRIBUTE_11 POV_GLOBAL_ATTRIBUTE_12 POV_GLOBAL_ATTRIBUTE_13 POV_GLOBAL_ATTRIBUTE_14 POV_GLOBAL_ATTRIBUTE_15 POV_GLOBAL_ATTRIBUTE_16 POV_GLOBAL_ATTRIBUTE_17 POV_GLOBAL_ATTRIBUTE_18 POV_GLOBAL_ATTRIBUTE_19 POV_GLOBAL_ATTRIBUTE_20 POV_GLOBAL_ATTRIBUTE_CATEGORY POV_NUM_1099 PARTY_ID AC_PAYMENT_DOCUMENT_ID AC_PAYMENT_PROFILE_ID CGAC_GAIN_CCID CGAC_LOSS_CCID AC_CREATION_DATE AC_CREATED_BY AC_LAST_UPDATE_DATE AC_LAST_UPDATED_BY EA_ENABLED_FLAG AC_SUBLEDGER_BC_STATUS AC_DATA_SET_ID OVERRIDE_BC_COMPL_STATUS_FLAG AC_BU_NAME AC_LE_NAME POS_FUTURE_DATED_PAY_CCID AC_PAYMENT_INSTRUMENT_ID AC_PMT_INSTR_BU_ASSIGNMENT_ID

Query

SQL_Statement

SELECT APH.accounting_Event_id Event_Id ,AC.check_number Payment_Transaction_number ,AC.payment_type_flag Payment_Type ,AC.payment_method_code Payment_Document_Type ,AC.payment_mode_code AC_payment_mode_code ,AC.doc_sequence_id Payment_Document_Sequence_Id ,FDS.name Doc_Sequence_Name ,decode(sign(AC.vendor_id), 1,'S',NULL) Third_Party_Type ,nvl(CBA.pooled_flag,'N') CBA_Pooled_Bank_Acount_Flag ,AC.check_id AC_Check_ID ,AC.Province AC_Province ,AC.actual_value_date AC_actual_value_date ,AC.address_line1 AC_address_line_1 ,AC.address_line2 AC_address_line_2 ,AC.address_line3 AC_address_line_3 ,AC.address_line4 AC_address_line_4 ,AC.amount AC_amount ,AC.attribute_category AC_attribute_category ,AC.attribute1 AC_attribute1 ,AC.attribute10 AC_attribute10 ,AC.attribute11 AC_attribute11 ,AC.attribute12 AC_attribute12 ,AC.attribute13 AC_attribute13 ,AC.attribute14 AC_attribute14 ,AC.attribute15 AC_attribute15 ,AC.attribute2 AC_attribute2 ,AC.attribute3 AC_attribute3 ,AC.attribute4 AC_attribute4 ,AC.attribute5 AC_attribute5 ,AC.attribute6 AC_attribute6 ,AC.attribute7 AC_attribute7 ,AC.attribute8 AC_attribute8 ,AC.attribute9 AC_attribute9 ,AC.check_date AC_check_date ,AC.check_voucher_num AC_check_voucher_number ,AC.checkrun_name AC_checkrun_name ,AC.city AC_city ,AC.country AC_country ,AC.county AC_county ,AC.description AC_description ,AC.doc_category_code AC_doc_category_code ,AC.doc_sequence_value AC_doc_sequence_value ,AC.future_pay_due_date AC_future_pay_due_date ,AC.state AC_state ,AC.vendor_name AC_vendor_name ,AC.void_date AC_void_date ,AC.zip AC_zip ,CBA.account_classification CBA_account_classification ,CBA.bank_account_name CBA_bank_account_name ,CBA.bank_account_name_alt CBA_bank_account_name_alt ,CBA.bank_account_num CBA_bank_account_num ,CBA.bank_account_type CBA_bank_account_type ,CBA.description CBA_description ,CBA.iban_number CBA_iban_number ,decode(sign(AC.vendor_id), 1, AC.vendor_id, NULL) AC_Vendor_Id ,decode(sign(AC.vendor_id), 1, AC.vendor_site_id, NULL) AC_vendor_site_id ,AC.org_id AC_Org_Id ,AC.global_attribute_category AC_Global_Attribute_Category ,AC.global_attribute1 AC_Global_Attribute_1 ,AC.global_attribute2 AC_Global_Attribute_2 ,AC.global_attribute3 AC_Global_Attribute_3 ,AC.global_attribute4 AC_Global_Attribute_4 ,AC.global_attribute5 AC_Global_Attribute_5 ,AC.global_attribute6 AC_Global_Attribute_6 ,AC.global_attribute7 AC_Global_Attribute_7 ,AC.global_attribute8 AC_Global_Attribute_8 ,AC.global_attribute9 AC_Global_Attribute_9 ,AC.global_attribute10 AC_Global_Attribute_10 ,AC.global_attribute11 AC_Global_Attribute_11 ,AC.global_attribute12 AC_Global_Attribute_12 ,AC.global_attribute13 AC_Global_Attribute_13 ,AC.global_attribute14 AC_Global_Attribute_14 ,AC.global_attribute15 AC_Global_Attribute_15 ,AC.global_attribute16 AC_Global_Attribute_16 ,AC.global_attribute17 AC_Global_Attribute_17 ,AC.global_attribute18 AC_Global_Attribute_18 ,AC.global_attribute19 AC_Global_Attribute_19 ,AC.global_attribute20 AC_Global_Attribute_20 ,AC.external_bank_account_id AC_External_Bank_Account_Id ,APH.PAYMENT_HISTORY_ID APH_Payment_History_Id ,APH.ACCOUNTING_DATE APH_Accounting_Date ,APH.TRANSACTION_TYPE APH_Transaction_Type ,APH.REV_PMT_HIST_ID APH_Rev_Pmt_Hist_Id ,APH.TRX_BANK_AMOUNT APH_Bank_Curr_Amount ,APH.TRX_PMT_AMOUNT APH_Amount ,APH.historical_flag APH_Historical_flag ,nvl(CGAC.cash_clearing_ccid,NVL(CGAC.ASSET_CODE_COMBINATION_ID, NVL(CBA.cash_clearing_ccid,CBA.ASSET_CODE_COMBINATION_ID))) CGAC_cash_clearing_ccid ,nvl(CGAC.asset_code_combination_id, CBA.asset_code_combination_id) CGAC_ap_asset_ccid ,0 CGAC_future_dated_pmt_ccid ,decode(APH.transaction_type, 'PAYMENT CANCELLED','Y', 'REFUND CANCELLED','Y','N') Transac_reversal_flag ,AC.party_id AC_PARTY_ID ,AC.party_site_id AC_PARTY_SITE_ID , AC.CURRENCY_CODE AC_currency_code , AC.exchange_date AC_Exchange_Date , AC.exchange_rate AC_Exchange_Rate , AC.Exchange_Rate_Type AC_Exchange_Rate_Type , nvl(AC.base_amount, AC.amount) AC_ledger_amount , nvl(APH.trx_base_amount, APH.trx_pmt_Amount) APH_Base_Amount , nvl(APH.bank_currency_code, CBA.Currency_code ) APH_Bank_Currency_Code , APH.bank_to_base_xrate_type APH_Bank_To_Base_Xrate_Type , APH.bank_to_base_xrate_date APH_Bank_To_Base_Xrate_Date , APH.bank_to_base_xrate APH_Bank_To_Base_XRate , APH.pmt_currency_code APH_Pmt_Currency_Code , APH.pmt_to_base_xrate_type APH_Pmt_To_Base_Xrate_Type , APH.pmt_to_base_xrate_date APH_Pmt_To_Base_Xrate_Date , APH.pmt_to_base_xrate APH_Pmt_To_Base_Xrate , APH.invoice_adjustment_event_id APH_Invoice_Adj_Event_Id ,decode(sign(AC.vendor_id), 1,POV.attribute_category, HZP.attribute_category) POV_Attribute_Category ,decode(sign(AC.VENDOR_ID), 1,POV.attribute1, HZP.attribute1 ) POV_Attribute1 ,decode(sign(AC.VENDOR_ID), 1,POV.attribute10, HZP.attribute10 ) POV_Attribute10 ,decode(sign(AC.VENDOR_ID), 1,POV.attribute11, HZP.attribute11) POV_Attribute11 ,decode(sign(AC.VENDOR_ID), 1,POV.attribute12,HZP.attribute12) POV_Attribute12 ,decode(sign(AC.VENDOR_ID), 1,POV.attribute13,HZP.attribute13) POV_Attribute13 ,decode(sign(AC.VENDOR_ID), 1,POV.attribute14,HZP.attribute11) POV_Attribute14 ,decode(sign(AC.VENDOR_ID), 1,POV.attribute15,HZP.attribute15) POV_Attribute15 ,decode(sign(AC.VENDOR_ID), 1,POV.attribute2,HZP.attribute2 ) POV_Attribute2 ,decode(sign(AC.VENDOR_ID), 1,POV.attribute3,HZP.attribute3) POV_Attribute3 ,decode(sign(AC.VENDOR_ID), 1,POV.attribute4,HZP.attribute4) POV_Attribute4 ,decode(sign(AC.VENDOR_ID), 1,POV.attribute5,HZP.attribute5) POV_Attribute5 ,decode(sign(AC.VENDOR_ID), 1,POV.attribute6,HZP.attribute6) POV_Attribute6 ,decode(sign(AC.VENDOR_ID), 1,POV.attribute7,HZP.attribute7) POV_Attribute7 ,decode(sign(AC.VENDOR_ID), 1,POV.attribute8,HZP.attribute8) POV_Attribute8 ,decode(sign(AC.VENDOR_ID), 1,POV.attribute9,HZP.attribute9) POV_Attribute9 ,decode(sign(AC.vendor_id), 1, POV.Vendor_Name_Alt,NULL) POV_Vendor_Name_Alt ,decode(sign(AC.VENDOR_ID), 1, POV.vendor_id, NULL ) POV_vendor_id ,decode(sign(AC.VENDOR_ID), 1, POV.segment1, NULL ) POV_Segment1 ,decode(sign(AC.VENDOR_ID), 1, POV.vendor_name, HZP.party_name) POV_Vendor_Name ,decode(sign(AC.VENDOR_ID), 1, POV.GLOBAL_ATTRIBUTE1,NULL ) POV_Global_Attribute_1 ,decode(sign(AC.VENDOR_ID), 1, POV.GLOBAL_ATTRIBUTE2,NULL) POV_Global_Attribute_2 ,decode(sign(AC.VENDOR_ID), 1, POV.GLOBAL_ATTRIBUTE3,NULL) POV_Global_Attribute_3 ,decode(sign(AC.VENDOR_ID), 1, POV.GLOBAL_ATTRIBUTE4, NULL) POV_Global_Attribute_4 ,decode(sign(AC.VENDOR_ID), 1, POV.GLOBAL_ATTRIBUTE5,NULL) POV_Global_Attribute_5 ,decode(sign(AC.VENDOR_ID), 1, POV.GLOBAL_ATTRIBUTE6, NULL) POV_Global_Attribute_6 ,decode(sign(AC.VENDOR_ID), 1, POV.GLOBAL_ATTRIBUTE7, NULL) POV_Global_Attribute_7 ,decode(sign(AC.VENDOR_ID), 1, POV.GLOBAL_ATTRIBUTE8,NULL) POV_Global_Attribute_8 ,decode(sign(AC.VENDOR_ID), 1, POV.GLOBAL_ATTRIBUTE9,NULL) POV_Global_Attribute_9 ,decode(sign(AC.VENDOR_ID), 1, POV.GLOBAL_ATTRIBUTE10,NULL) POV_Global_Attribute_10 ,decode(sign(AC.VENDOR_ID), 1, POV.GLOBAL_ATTRIBUTE11, NULL) POV_Global_Attribute_11 ,decode(sign(AC.VENDOR_ID), 1, POV.GLOBAL_ATTRIBUTE12,NULL) POV_Global_Attribute_12 ,decode(sign(AC.VENDOR_ID), 1, POV.GLOBAL_ATTRIBUTE13,NULL) POV_Global_Attribute_13 ,decode(sign(AC.VENDOR_ID), 1, POV.GLOBAL_ATTRIBUTE14,NULL) POV_Global_Attribute_14 ,decode(sign(AC.VENDOR_ID), 1, POV.GLOBAL_ATTRIBUTE15,NULL) POV_Global_Attribute_15 ,decode(sign(AC.VENDOR_ID), 1, POV.GLOBAL_ATTRIBUTE16, NULL) POV_Global_Attribute_16 ,decode(sign(AC.VENDOR_ID), 1, POV.GLOBAL_ATTRIBUTE17,NULL) POV_Global_Attribute_17 ,decode(sign(AC.VENDOR_ID), 1, POV.GLOBAL_ATTRIBUTE18,NULL) POV_Global_Attribute_18 ,decode(sign(AC.VENDOR_ID), 1, POV.GLOBAL_ATTRIBUTE19, NULL) POV_Global_Attribute_19 ,decode(sign(AC.VENDOR_ID), 1, POV.GLOBAL_ATTRIBUTE20,NULL) POV_Global_Attribute_20 ,decode(sign(AC.VENDOR_ID), 1, POV.GLOBAL_ATTRIBUTE_CATEGORY,NULL) POV_Global_Attribute_Category ,decode(sign(AC.VENDOR_ID), 1, null,HZP.JGZZ_FISCAL_CODE) POV_num_1099 ,decode(sign(AC.VENDOR_ID), 1, POV.PARTY_ID, HZP.party_id) PARTY_ID ,AC.PAYMENT_DOCUMENT_ID AC_PAYMENT_DOCUMENT_ID ,AC.PAYMENT_PROFILE_ID AC_PAYMENT_PROFILE_ID ,CGAC.GAIN_CODE_COMBINATION_ID CGAC_Gain_CCID ,CGAC.LOSS_CODE_COMBINATION_ID CGAC_Loss_CCID , cast(ac.creation_date as DATE) AC_CREATION_DATE , ac.created_by AC_CREATED_BY , cast(ac.last_update_date as DATE) AC_LAST_UPDATE_DATE , ac.last_updated_by AC_LAST_UPDATED_BY ,NVL(XBTT.ea_enabled_flag,'N') EA_ENABLED_FLAG ,CASE WHEN iby.funds_status_code IN ('RESERVED_WARNING','RESERVED_PASSED','UNRESERVED') then 'Y' WHEN iby.funds_status_code = 'RESERVED_NOT_APPLICABLE' then 'N' WHEN iby.funds_status_code = 'NOT_REQUIRED_BY_SUBLEDGER' then 'R' ELSE NULL END AC_SUBLEDGER_BC_STATUS ,iby.data_set_id AC_DATA_SET_ID ,'Y' OVERRIDE_BC_COMPL_STATUS_FLAG ,AC.ORG_ID AC_BU_NAME ,AC.LEGAL_ENTITY_ID AC_LE_NAME ,(SELECT POS.FUTURE_DATED_PAYMENT_CCID FROM POZ_SITE_ASSIGNMENTS_ALL_M POS WHERE POS.VENDOR_SITE_ID = AC.VENDOR_SITE_ID AND POS.BU_ID = AC.ORG_ID AND ROWNUM = 1) POS_FUTURE_DATED_PAY_CCID ,AC.payment_instrument_id AC_Payment_Instrument_ID ,AC.pmt_instr_bu_assignment_id AC_pmt_instr_bu_assignment_id FROM AP_CHECKS_ALL AC, AP_PAYMENT_HISTORY_ALL APH, FND_DOCUMENT_SEQUENCES FDS, CE_BANK_ACCOUNTS CBA, CE_BANK_ACCT_USES_ALL CBAU, CE_GL_ACCOUNTS_CCID CGAC, POZ_SUPPLIERS_V POV, HZ_PARTIES HZP, XCC_BCE_BUSINESS_UNITS XBBU, XCC_BCE_TRANSACTION_TYPES XBTT, IBY_PAYMENTS_ALL iby WHERE AC.check_id = APH.check_id AND AC.doc_sequence_id = FDS.doc_sequence_id(+) AND AC.CE_bank_acct_use_id = CBAU.BANK_ACCT_USE_ID(+) AND CBAU.bank_Account_id = CBA.bank_Account_id(+) AND AC.ce_bank_acct_use_id = CGAC.bank_acct_use_id(+) AND AC.vendor_id = POV.vendor_id (+) AND HZP.PARTY_ID = AC.party_id AND XBBU.business_unit_id(+) = AC.org_id AND XBBU.LOCKED_FLAG(+) = 'Y' AND XBTT.BUSINESS_UNIT_ID (+) = XBBU.BUSINESS_UNIT_ID AND XBTT.TRANSACTION_TYPE_CODE(+) = 'PURCHASE_ORDER' AND XBTT.TRANSACTION_SUBTYPE_CODE(+) = 'PO_STANDARD' AND IBY.PAYMENT_ID(+) = AC.PAYMENT_ID