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.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
,DECODE(ac.paycard_reference_id, null,
CBA.BANK_ACCOUNT_NAME, AC.BANK_ACCOUNT_NAME) CBA_bank_account_name
,CBA.bank_account_name_alt CBA_bank_account_name_alt
,DECODE(ac.paycard_reference_id, null,
CBA.bank_account_num, ac.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,CGAC.ASSET_CODE_COMBINATION_ID) CGAC_cash_clearing_ccid
,CGAC.ap_asset_ccid CGAC_ap_asset_ccid
,CGAC.bank_charges_ccid CGAC_bank_charges_ccid
,CGAC.bank_errors_ccid CGAC_bank_errors_ccid
,CGAC.future_dated_payment_ccid CGAC_future_dated_pmt_ccid
,decode(nvl(APH.historical_flag,'N'),
'Y','N'
,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.errors_base_amount,APH.ERRORS_PMT_AMOUNT)
APH_Errors_Base_Amount
, nvl(APH.charges_base_amount,APH.CHARGES_PMT_AMOUNT)
APH_Charges_Base_Amount
, APH.bank_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,HZP.ORGANIZATION_NAME_PHONETIC)
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.SEGMENT2, NULL) POV_Segment2
,decode(sign(AC.VENDOR_ID), 1,
POV.SEGMENT3, NULL) POV_Segment3
,decode(sign(AC.VENDOR_ID), 1,
POV.SEGMENT4, NULL) POV_Segment4
,decode(sign(AC.VENDOR_ID), 1,
POV.SEGMENT5, NULL) POV_Segment5
,decode(sign(AC.VENDOR_ID), 1,
POV.VENDOR_TYPE_LOOKUP_CODE,NULL) POV_Merchant_Type_Lookup_Code
,decode(sign(AC.VENDOR_ID), 1,
POV.GLOBAL_ATTRIBUTE1,HZP.GLOBAL_ATTRIBUTE1 )
POV_Global_Attribute_1
,decode(sign(AC.VENDOR_ID), 1,
POV.GLOBAL_ATTRIBUTE2,HZP.GLOBAL_ATTRIBUTE2)
POV_Global_Attribute_2
,decode(sign(AC.VENDOR_ID), 1,
POV.GLOBAL_ATTRIBUTE3,HZP.GLOBAL_ATTRIBUTE3)
POV_Global_Attribute_3
,decode(sign(AC.VENDOR_ID), 1,
POV.GLOBAL_ATTRIBUTE4, HZP.GLOBAL_ATTRIBUTE4)
POV_Global_Attribute_4
,decode(sign(AC.VENDOR_ID), 1,
POV.GLOBAL_ATTRIBUTE5,HZP.GLOBAL_ATTRIBUTE5)
POV_Global_Attribute_5
,decode(sign(AC.VENDOR_ID), 1,
POV.GLOBAL_ATTRIBUTE6, HZP.GLOBAL_ATTRIBUTE6)
POV_Global_Attribute_6
,decode(sign(AC.VENDOR_ID), 1,
POV.GLOBAL_ATTRIBUTE7, HZP.GLOBAL_ATTRIBUTE7)
POV_Global_Attribute_7
,decode(sign(AC.VENDOR_ID), 1,
POV.GLOBAL_ATTRIBUTE8,HZP.GLOBAL_ATTRIBUTE8)
POV_Global_Attribute_8
,decode(sign(AC.VENDOR_ID), 1,
POV.GLOBAL_ATTRIBUTE9,HZP.GLOBAL_ATTRIBUTE9)
POV_Global_Attribute_9
,decode(sign(AC.VENDOR_ID), 1,
POV.GLOBAL_ATTRIBUTE10,HZP.GLOBAL_ATTRIBUTE10)
POV_Global_Attribute_10
,decode(sign(AC.VENDOR_ID), 1,
POV.GLOBAL_ATTRIBUTE11, HZP.GLOBAL_ATTRIBUTE11)
POV_Global_Attribute_11
,decode(sign(AC.VENDOR_ID), 1,
POV.GLOBAL_ATTRIBUTE12,HZP.GLOBAL_ATTRIBUTE12)
POV_Global_Attribute_12
,decode(sign(AC.VENDOR_ID), 1,
POV.GLOBAL_ATTRIBUTE13,HZP.GLOBAL_ATTRIBUTE13)
POV_Global_Attribute_13
,decode(sign(AC.VENDOR_ID), 1,
POV.GLOBAL_ATTRIBUTE14,HZP.GLOBAL_ATTRIBUTE14)
POV_Global_Attribute_14
,decode(sign(AC.VENDOR_ID), 1,
POV.GLOBAL_ATTRIBUTE15,HZP.GLOBAL_ATTRIBUTE15)
POV_Global_Attribute_15
,decode(sign(AC.VENDOR_ID), 1,
POV.GLOBAL_ATTRIBUTE16, HZP.GLOBAL_ATTRIBUTE16)
POV_Global_Attribute_16
,decode(sign(AC.VENDOR_ID), 1,
POV.GLOBAL_ATTRIBUTE17,HZP.GLOBAL_ATTRIBUTE17)
POV_Global_Attribute_17
,decode(sign(AC.VENDOR_ID), 1,
POV.GLOBAL_ATTRIBUTE18,HZP.GLOBAL_ATTRIBUTE18)
POV_Global_Attribute_18
,decode(sign(AC.VENDOR_ID), 1,
POV.GLOBAL_ATTRIBUTE19, HZP.GLOBAL_ATTRIBUTE19)
POV_Global_Attribute_19
,decode(sign(AC.VENDOR_ID), 1,
POV.GLOBAL_ATTRIBUTE20,HZP.GLOBAL_ATTRIBUTE20)
POV_Global_Attribute_20
,decode(sign(AC.VENDOR_ID), 1,
POV.GLOBAL_ATTRIBUTE_CATEGORY,HZP.GLOBAL_ATTRIBUTE_CATEGORY)
POV_Global_Attribute_Category
,decode(sign(AC.VENDOR_ID), 1,
POV.num_1099,HZP.JGZZ_FISCAL_CODE)
POV_num_1099
,decode(sign(AC.VENDOR_ID), 1,
POV.PARTY_ID, HZP.party_id) PARTY_ID
,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, HPS.GLOBAL_ATTRIBUTE1 )
POS_Global_Attribute_1
,decode(sign(AC.VENDOR_ID), 1,
POS.GLOBAL_ATTRIBUTE2,HPS.GLOBAL_ATTRIBUTE2)
POS_Global_Attribute_2
,decode(sign(AC.VENDOR_ID), 1,
POS.GLOBAL_ATTRIBUTE3,HPS.GLOBAL_ATTRIBUTE3)
POS_Global_Attribute_3
,decode(sign(AC.VENDOR_ID), 1,
POS.GLOBAL_ATTRIBUTE4,HPS.GLOBAL_ATTRIBUTE4)
POS_Global_Attribute_4
,decode(sign(AC.VENDOR_ID), 1,
POS.GLOBAL_ATTRIBUTE5,HPS.GLOBAL_ATTRIBUTE5)
POS_Global_Attribute_5
,decode(sign(AC.VENDOR_ID), 1,
POS.GLOBAL_ATTRIBUTE6,HPS.GLOBAL_ATTRIBUTE6)
POS_Global_Attribute_6
,decode(sign(AC.VENDOR_ID), 1,
POS.GLOBAL_ATTRIBUTE7,HPS.GLOBAL_ATTRIBUTE7)
POS_Global_Attribute_7
,decode(sign(AC.VENDOR_ID), 1,
POS.GLOBAL_ATTRIBUTE8,HPS.GLOBAL_ATTRIBUTE8)
POS_Global_Attribute_8
,decode(sign(AC.VENDOR_ID), 1,
POS.GLOBAL_ATTRIBUTE9,HPS.GLOBAL_ATTRIBUTE9)
POS_Global_Attribute_9
,decode(sign(AC.VENDOR_ID), 1,
POS.GLOBAL_ATTRIBUTE10,HPS.GLOBAL_ATTRIBUTE10)
POS_Global_Attribute_10
,decode(sign(AC.VENDOR_ID), 1,
POS.GLOBAL_ATTRIBUTE11,HPS.GLOBAL_ATTRIBUTE11)
POS_Global_Attribute_11
,decode(sign(AC.VENDOR_ID), 1,
POS.GLOBAL_ATTRIBUTE12,HPS.GLOBAL_ATTRIBUTE12)
POS_Global_Attribute_12
,decode(sign(AC.VENDOR_ID), 1,
POS.GLOBAL_ATTRIBUTE13,HPS.GLOBAL_ATTRIBUTE13)
POS_Global_Attribute_13
,decode(sign(AC.VENDOR_ID), 1,
POS.GLOBAL_ATTRIBUTE14,HPS.GLOBAL_ATTRIBUTE14)
POS_Global_Attribute_14
,decode(sign(AC.VENDOR_ID), 1,
POS.GLOBAL_ATTRIBUTE15,HPS.GLOBAL_ATTRIBUTE15)
POS_Global_Attribute_15
,decode(sign(AC.VENDOR_ID), 1,
POS.GLOBAL_ATTRIBUTE16,HPS.GLOBAL_ATTRIBUTE16)
POS_Global_Attribute_16
,decode(sign(AC.VENDOR_ID), 1,
POS.GLOBAL_ATTRIBUTE17,HPS.GLOBAL_ATTRIBUTE17)
POS_Global_Attribute_17
,decode(sign(AC.VENDOR_ID), 1,
POS.GLOBAL_ATTRIBUTE18,HPS.GLOBAL_ATTRIBUTE18)
POS_Global_Attribute_18
,decode(sign(AC.VENDOR_ID), 1,
POS.GLOBAL_ATTRIBUTE19,HPS.GLOBAL_ATTRIBUTE19)
POS_Global_Attribute_19
,decode(sign(AC.VENDOR_ID), 1,
POS.GLOBAL_ATTRIBUTE20,HPS.GLOBAL_ATTRIBUTE20)
POS_Global_Attribute_20
,decode(sign(AC.VENDOR_ID), 1,
POS.GLOBAL_ATTRIBUTE_CATEGORY,HPS.GLOBAL_ATTRIBUTE_CATEGORY)
POS_Global_Attribute_Category
,decode(sign(AC.VENDOR_ID), 1,
POS.future_dated_payment_ccid, NULL)
POS_FDP_CCID
,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.ORG_ID, NULL) POS_ORG_ID
,decode(sign(AC.VENDOR_ID), 1,
POS.vendor_id, NULL) POS_VENDOR_ID
,CGAC.GAIN_CODE_COMBINATION_ID CGAC_Gain_CCID
,CGAC.LOSS_CODE_COMBINATION_ID CGAC_Loss_CCID
,IPP.PROCESSING_TYPE PAYMENT_PROCESSING_TYPE
,ACPROG.EXPENSE_CLEARING_CCID PAYCARD_ACCRUED_ACCOUNT_CCID
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,
ap_suppliers POV,
AP_SUPPLIER_SITES_ALL POS,
HZ_PARTIES HZP,
HZ_PARTY_SITES HPS,
HZ_LOCATIONS HL,
IBY_PAYMENT_PROFILES IPP,
AP_CARDS_ALL ACARD,
AP_CARD_PROGRAMS_ALL ACPROG
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 AC.vendor_site_id = POS.vendor_site_id(+)
AND HZP.PARTY_ID = AC.party_id
AND AC.PARTY_SITE_ID = HPS.PARTY_SITE_ID(+)
AND HPS.LOCATION_ID = HL.LOCATION_ID(+)
AND AC.PAYMENT_PROFILE_ID = IPP.PAYMENT_PROFILE_ID(+)
AND AC.PAYCARD_REFERENCE_ID = ACARD.PAYCARD_REFERENCE_ID(+)
AND ACARD.CARD_PROGRAM_ID = ACPROG.CARD_PROGRAM_ID (+)
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.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
, DECODE(AC.PAYCARD_REFERENCE_ID
, NULL
,
CBA.BANK_ACCOUNT_NAME
, AC.BANK_ACCOUNT_NAME) CBA_BANK_ACCOUNT_NAME
, CBA.BANK_ACCOUNT_NAME_ALT CBA_BANK_ACCOUNT_NAME_ALT
, DECODE(AC.PAYCARD_REFERENCE_ID
, NULL
,
CBA.BANK_ACCOUNT_NUM
, AC.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
, CGAC.ASSET_CODE_COMBINATION_ID) CGAC_CASH_CLEARING_CCID
, CGAC.AP_ASSET_CCID CGAC_AP_ASSET_CCID
, CGAC.BANK_CHARGES_CCID CGAC_BANK_CHARGES_CCID
, CGAC.BANK_ERRORS_CCID CGAC_BANK_ERRORS_CCID
, CGAC.FUTURE_DATED_PAYMENT_CCID CGAC_FUTURE_DATED_PMT_CCID
, DECODE(NVL(APH.HISTORICAL_FLAG
, 'N')
,
'Y'
, 'N'
, 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.ERRORS_BASE_AMOUNT
, APH.ERRORS_PMT_AMOUNT)
APH_ERRORS_BASE_AMOUNT
, NVL(APH.CHARGES_BASE_AMOUNT
, APH.CHARGES_PMT_AMOUNT)
APH_CHARGES_BASE_AMOUNT
, APH.BANK_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
, HZP.ORGANIZATION_NAME_PHONETIC)
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.SEGMENT2
, NULL) POV_SEGMENT2
, DECODE(SIGN(AC.VENDOR_ID)
, 1
,
POV.SEGMENT3
, NULL) POV_SEGMENT3
, DECODE(SIGN(AC.VENDOR_ID)
, 1
,
POV.SEGMENT4
, NULL) POV_SEGMENT4
, DECODE(SIGN(AC.VENDOR_ID)
, 1
,
POV.SEGMENT5
, NULL) POV_SEGMENT5
, DECODE(SIGN(AC.VENDOR_ID)
, 1
,
POV.VENDOR_TYPE_LOOKUP_CODE
, NULL) POV_MERCHANT_TYPE_LOOKUP_CODE
, DECODE(SIGN(AC.VENDOR_ID)
, 1
,
POV.GLOBAL_ATTRIBUTE1
, HZP.GLOBAL_ATTRIBUTE1 )
POV_GLOBAL_ATTRIBUTE_1
, DECODE(SIGN(AC.VENDOR_ID)
, 1
,
POV.GLOBAL_ATTRIBUTE2
, HZP.GLOBAL_ATTRIBUTE2)
POV_GLOBAL_ATTRIBUTE_2
, DECODE(SIGN(AC.VENDOR_ID)
, 1
,
POV.GLOBAL_ATTRIBUTE3
, HZP.GLOBAL_ATTRIBUTE3)
POV_GLOBAL_ATTRIBUTE_3
, DECODE(SIGN(AC.VENDOR_ID)
, 1
,
POV.GLOBAL_ATTRIBUTE4
, HZP.GLOBAL_ATTRIBUTE4)
POV_GLOBAL_ATTRIBUTE_4
, DECODE(SIGN(AC.VENDOR_ID)
, 1
,
POV.GLOBAL_ATTRIBUTE5
, HZP.GLOBAL_ATTRIBUTE5)
POV_GLOBAL_ATTRIBUTE_5
, DECODE(SIGN(AC.VENDOR_ID)
, 1
,
POV.GLOBAL_ATTRIBUTE6
, HZP.GLOBAL_ATTRIBUTE6)
POV_GLOBAL_ATTRIBUTE_6
, DECODE(SIGN(AC.VENDOR_ID)
, 1
,
POV.GLOBAL_ATTRIBUTE7
, HZP.GLOBAL_ATTRIBUTE7)
POV_GLOBAL_ATTRIBUTE_7
, DECODE(SIGN(AC.VENDOR_ID)
, 1
,
POV.GLOBAL_ATTRIBUTE8
, HZP.GLOBAL_ATTRIBUTE8)
POV_GLOBAL_ATTRIBUTE_8
, DECODE(SIGN(AC.VENDOR_ID)
, 1
,
POV.GLOBAL_ATTRIBUTE9
, HZP.GLOBAL_ATTRIBUTE9)
POV_GLOBAL_ATTRIBUTE_9
, DECODE(SIGN(AC.VENDOR_ID)
, 1
,
POV.GLOBAL_ATTRIBUTE10
, HZP.GLOBAL_ATTRIBUTE10)
POV_GLOBAL_ATTRIBUTE_10
, DECODE(SIGN(AC.VENDOR_ID)
, 1
,
POV.GLOBAL_ATTRIBUTE11
, HZP.GLOBAL_ATTRIBUTE11)
POV_GLOBAL_ATTRIBUTE_11
, DECODE(SIGN(AC.VENDOR_ID)
, 1
,
POV.GLOBAL_ATTRIBUTE12
, HZP.GLOBAL_ATTRIBUTE12)
POV_GLOBAL_ATTRIBUTE_12
, DECODE(SIGN(AC.VENDOR_ID)
, 1
,
POV.GLOBAL_ATTRIBUTE13
, HZP.GLOBAL_ATTRIBUTE13)
POV_GLOBAL_ATTRIBUTE_13
, DECODE(SIGN(AC.VENDOR_ID)
, 1
,
POV.GLOBAL_ATTRIBUTE14
, HZP.GLOBAL_ATTRIBUTE14)
POV_GLOBAL_ATTRIBUTE_14
, DECODE(SIGN(AC.VENDOR_ID)
, 1
,
POV.GLOBAL_ATTRIBUTE15
, HZP.GLOBAL_ATTRIBUTE15)
POV_GLOBAL_ATTRIBUTE_15
, DECODE(SIGN(AC.VENDOR_ID)
, 1
,
POV.GLOBAL_ATTRIBUTE16
, HZP.GLOBAL_ATTRIBUTE16)
POV_GLOBAL_ATTRIBUTE_16
, DECODE(SIGN(AC.VENDOR_ID)
, 1
,
POV.GLOBAL_ATTRIBUTE17
, HZP.GLOBAL_ATTRIBUTE17)
POV_GLOBAL_ATTRIBUTE_17
, DECODE(SIGN(AC.VENDOR_ID)
, 1
,
POV.GLOBAL_ATTRIBUTE18
, HZP.GLOBAL_ATTRIBUTE18)
POV_GLOBAL_ATTRIBUTE_18
, DECODE(SIGN(AC.VENDOR_ID)
, 1
,
POV.GLOBAL_ATTRIBUTE19
, HZP.GLOBAL_ATTRIBUTE19)
POV_GLOBAL_ATTRIBUTE_19
, DECODE(SIGN(AC.VENDOR_ID)
, 1
,
POV.GLOBAL_ATTRIBUTE20
, HZP.GLOBAL_ATTRIBUTE20)
POV_GLOBAL_ATTRIBUTE_20
, DECODE(SIGN(AC.VENDOR_ID)
, 1
,
POV.GLOBAL_ATTRIBUTE_CATEGORY
, HZP.GLOBAL_ATTRIBUTE_CATEGORY)
POV_GLOBAL_ATTRIBUTE_CATEGORY
, DECODE(SIGN(AC.VENDOR_ID)
, 1
,
POV.NUM_1099
, HZP.JGZZ_FISCAL_CODE)
POV_NUM_1099
, DECODE(SIGN(AC.VENDOR_ID)
, 1
,
POV.PARTY_ID
, HZP.PARTY_ID) PARTY_ID
, 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
, HPS.GLOBAL_ATTRIBUTE1 )
POS_GLOBAL_ATTRIBUTE_1
, DECODE(SIGN(AC.VENDOR_ID)
, 1
,
POS.GLOBAL_ATTRIBUTE2
, HPS.GLOBAL_ATTRIBUTE2)
POS_GLOBAL_ATTRIBUTE_2
, DECODE(SIGN(AC.VENDOR_ID)
, 1
,
POS.GLOBAL_ATTRIBUTE3
, HPS.GLOBAL_ATTRIBUTE3)
POS_GLOBAL_ATTRIBUTE_3
, DECODE(SIGN(AC.VENDOR_ID)
, 1
,
POS.GLOBAL_ATTRIBUTE4
, HPS.GLOBAL_ATTRIBUTE4)
POS_GLOBAL_ATTRIBUTE_4
, DECODE(SIGN(AC.VENDOR_ID)
, 1
,
POS.GLOBAL_ATTRIBUTE5
, HPS.GLOBAL_ATTRIBUTE5)
POS_GLOBAL_ATTRIBUTE_5
, DECODE(SIGN(AC.VENDOR_ID)
, 1
,
POS.GLOBAL_ATTRIBUTE6
, HPS.GLOBAL_ATTRIBUTE6)
POS_GLOBAL_ATTRIBUTE_6
, DECODE(SIGN(AC.VENDOR_ID)
, 1
,
POS.GLOBAL_ATTRIBUTE7
, HPS.GLOBAL_ATTRIBUTE7)
POS_GLOBAL_ATTRIBUTE_7
, DECODE(SIGN(AC.VENDOR_ID)
, 1
,
POS.GLOBAL_ATTRIBUTE8
, HPS.GLOBAL_ATTRIBUTE8)
POS_GLOBAL_ATTRIBUTE_8
, DECODE(SIGN(AC.VENDOR_ID)
, 1
,
POS.GLOBAL_ATTRIBUTE9
, HPS.GLOBAL_ATTRIBUTE9)
POS_GLOBAL_ATTRIBUTE_9
, DECODE(SIGN(AC.VENDOR_ID)
, 1
,
POS.GLOBAL_ATTRIBUTE10
, HPS.GLOBAL_ATTRIBUTE10)
POS_GLOBAL_ATTRIBUTE_10
, DECODE(SIGN(AC.VENDOR_ID)
, 1
,
POS.GLOBAL_ATTRIBUTE11
, HPS.GLOBAL_ATTRIBUTE11)
POS_GLOBAL_ATTRIBUTE_11
, DECODE(SIGN(AC.VENDOR_ID)
, 1
,
POS.GLOBAL_ATTRIBUTE12
, HPS.GLOBAL_ATTRIBUTE12)
POS_GLOBAL_ATTRIBUTE_12
, DECODE(SIGN(AC.VENDOR_ID)
, 1
,
POS.GLOBAL_ATTRIBUTE13
, HPS.GLOBAL_ATTRIBUTE13)
POS_GLOBAL_ATTRIBUTE_13
, DECODE(SIGN(AC.VENDOR_ID)
, 1
,
POS.GLOBAL_ATTRIBUTE14
, HPS.GLOBAL_ATTRIBUTE14)
POS_GLOBAL_ATTRIBUTE_14
, DECODE(SIGN(AC.VENDOR_ID)
, 1
,
POS.GLOBAL_ATTRIBUTE15
, HPS.GLOBAL_ATTRIBUTE15)
POS_GLOBAL_ATTRIBUTE_15
, DECODE(SIGN(AC.VENDOR_ID)
, 1
,
POS.GLOBAL_ATTRIBUTE16
, HPS.GLOBAL_ATTRIBUTE16)
POS_GLOBAL_ATTRIBUTE_16
, DECODE(SIGN(AC.VENDOR_ID)
, 1
,
POS.GLOBAL_ATTRIBUTE17
, HPS.GLOBAL_ATTRIBUTE17)
POS_GLOBAL_ATTRIBUTE_17
, DECODE(SIGN(AC.VENDOR_ID)
, 1
,
POS.GLOBAL_ATTRIBUTE18
, HPS.GLOBAL_ATTRIBUTE18)
POS_GLOBAL_ATTRIBUTE_18
, DECODE(SIGN(AC.VENDOR_ID)
, 1
,
POS.GLOBAL_ATTRIBUTE19
, HPS.GLOBAL_ATTRIBUTE19)
POS_GLOBAL_ATTRIBUTE_19
, DECODE(SIGN(AC.VENDOR_ID)
, 1
,
POS.GLOBAL_ATTRIBUTE20
, HPS.GLOBAL_ATTRIBUTE20)
POS_GLOBAL_ATTRIBUTE_20
, DECODE(SIGN(AC.VENDOR_ID)
, 1
,
POS.GLOBAL_ATTRIBUTE_CATEGORY
, HPS.GLOBAL_ATTRIBUTE_CATEGORY)
POS_GLOBAL_ATTRIBUTE_CATEGORY
, DECODE(SIGN(AC.VENDOR_ID)
, 1
,
POS.FUTURE_DATED_PAYMENT_CCID
, NULL)
POS_FDP_CCID
, 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.ORG_ID
, NULL) POS_ORG_ID
, DECODE(SIGN(AC.VENDOR_ID)
, 1
,
POS.VENDOR_ID
, NULL) POS_VENDOR_ID
, CGAC.GAIN_CODE_COMBINATION_ID CGAC_GAIN_CCID
, CGAC.LOSS_CODE_COMBINATION_ID CGAC_LOSS_CCID
, IPP.PROCESSING_TYPE PAYMENT_PROCESSING_TYPE
, ACPROG.EXPENSE_CLEARING_CCID PAYCARD_ACCRUED_ACCOUNT_CCID
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
,
AP_SUPPLIERS POV
,
AP_SUPPLIER_SITES_ALL POS
,
HZ_PARTIES HZP
,
HZ_PARTY_SITES HPS
,
HZ_LOCATIONS HL
,
IBY_PAYMENT_PROFILES IPP
,
AP_CARDS_ALL ACARD
,
AP_CARD_PROGRAMS_ALL ACPROG
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 AC.VENDOR_SITE_ID = POS.VENDOR_SITE_ID(+)
AND HZP.PARTY_ID = AC.PARTY_ID
AND AC.PARTY_SITE_ID = HPS.PARTY_SITE_ID(+)
AND HPS.LOCATION_ID = HL.LOCATION_ID(+)
AND AC.PAYMENT_PROFILE_ID = IPP.PAYMENT_PROFILE_ID(+)
AND AC.PAYCARD_REFERENCE_ID = ACARD.PAYCARD_REFERENCE_ID(+)
AND ACARD.CARD_PROGRAM_ID = ACPROG.CARD_PROGRAM_ID (+)
|
|
|