[Home] [Help]
SELECT XE.Event_id Event_id
,AI.SOURCE AI_Source
,AI.INVOICE_NUM Inv_Transaction_number
,AI.DOC_SEQUENCE_ID Inv_Doc_Sequence_Identifier
,AI.DOC_SEQUENCE_VALUE Inv_Doc_Sequence_Value
,AI.DOC_CATEGORY_CODE Inv_Doc_Sequence_Category
,FDS.NAME Doc_Sequence_Name
,decode(sign(AI.vendor_id), 1,'S',NULL) Inv_Party_Type
,decode(sign(AI.vendor_id), 1,'S',NULL) Third_Party_Type
,AB.batch_name AB_Batch_Name
,AI.accts_pay_code_combination_id AI_Accts_Pay_CCID
,AI.invoice_id AI_Invoice_Id
,AI.org_id AI_org_id
,AI.invoice_currency_code AI_invoice_currency_code
,decode(sign(AI.vendor_id), 1,
AI.vendor_id, NULL) AI_vendor_id
,decode(sign(AI.vendor_id), 1,
AI.vendor_site_id, NULL) AI_vendor_site_id
,AI.attribute_category AI_Attribute_Category
,AI.attribute1 AI_Attribute1
,AI.attribute10 AI_Attribute10
,AI.attribute11 AI_Attribute11
,AI.attribute12 AI_Attribute12
,AI.attribute13 AI_Attribute13
,AI.attribute14 AI_Attribute14
,AI.attribute15 AI_Attribute15
,AI.attribute2 AI_Attribute2
,AI.attribute3 AI_Attribute3
,AI.attribute4 AI_Attribute4
,AI.attribute5 AI_Attribute5
,AI.attribute6 AI_Attribute6
,AI.attribute7 AI_Attribute7
,AI.attribute8 AI_Attribute8
,AI.attribute9 AI_Attribute9
,AI.cancelled_date AI_Cancelled_Date
,AI.description AI_Description
,AI.invoice_amount AI_Invoice_Amount
,AI.base_Amount AI_Invoice_Base_Amount
,AI.invoice_date AI_Invoice_Date
,AI.invoice_type_lookup_code AI_Invoice_Type_Lookup_Code
,AI.vat_code AI_VAT_Code
,AI.voucher_num AI_Voucher_Num
,AI.AMOUNT_PAID Inv_Amount_Paid
,AI.DISCOUNT_AMOUNT_TAKEN Inv_Discount_Amount_Taken
,AI.INVOICE_DISTRIBUTION_TOTAL Inv_Distribution_Total
,AI.AWT_GROUP_ID Inv_AWT_Group_Id
,AI.GLOBAL_ATTRIBUTE_CATEGORY Inv_Global_Attribute_Category
,AI.GLOBAL_ATTRIBUTE1 Inv_Global_Attribute_1
,AI.GLOBAL_ATTRIBUTE2 Inv_Global_Attribute_2
,AI.GLOBAL_ATTRIBUTE3 Inv_Global_Attribute_3
,AI.GLOBAL_ATTRIBUTE4 Inv_Global_Attribute_4
,AI.GLOBAL_ATTRIBUTE5 Inv_Global_Attribute_5
,AI.GLOBAL_ATTRIBUTE6 Inv_Global_Attribute_6
,AI.GLOBAL_ATTRIBUTE7 Inv_Global_Attribute_7
,AI.GLOBAL_ATTRIBUTE8 Inv_Global_Attribute_8
,AI.GLOBAL_ATTRIBUTE9 Inv_Global_Attribute_9
,AI.GLOBAL_ATTRIBUTE10 Inv_Global_Attribute_10
,AI.GLOBAL_ATTRIBUTE11 Inv_Global_Attribute_11
,AI.GLOBAL_ATTRIBUTE12 Inv_Global_Attribute_12
,AI.GLOBAL_ATTRIBUTE13 Inv_Global_Attribute_13
,AI.GLOBAL_ATTRIBUTE14 Inv_Global_Attribute_14
,AI.GLOBAL_ATTRIBUTE15 Inv_Global_Attribute_15
,AI.GLOBAL_ATTRIBUTE16 Inv_Global_Attribute_16
,AI.GLOBAL_ATTRIBUTE17 Inv_Global_Attribute_17
,AI.GLOBAL_ATTRIBUTE18 Inv_Global_Attribute_18
,AI.GLOBAL_ATTRIBUTE19 Inv_Global_Attribute_19
,AI.GLOBAL_ATTRIBUTE20 Inv_Global_Attribute_20
,AI.GL_DATE Inv_GL_Date
,AI.HISTORICAL_FLAG Inv_Historical_Flag
,decode(XE.event_type_code,
'INVOICE CANCELLED','Y',
'DEBIT MEMO CANCELLED','Y',
'CREDIT MEMO CANCELLED','Y',
'PREPAYMENT CANCELLED','Y','N') Transac_reversal_flag
,AI.set_of_books_id Ledger_id
,nvl(AI.exchange_date, AI.gl_date) Inv_Exchange_Date
,AI.exchange_rate Inv_Exchange_Rate
,AI.exchange_rate_type Inv_Exchange_Rate_Type
,AI.party_id AI_PARTY_ID
,AI.party_site_id AI_PARTY_SITE_ID
,decode(sign(AI.vendor_id), 1,
POV.attribute_category, HZP.attribute_category)
POV_Attribute_Category
,decode(sign(AI.vendor_id), 1,
POV.attribute1, HZP.attribute1 )
POV_Attribute1
,decode(sign(AI.vendor_id), 1,
POV.attribute10, HZP.attribute10 )
POV_Attribute10
,decode(sign(AI.vendor_id), 1,
POV.attribute11, HZP.attribute11) POV_Attribute11
,decode(sign(AI.vendor_id), 1,
POV.attribute12,HZP.attribute12) POV_Attribute12
,decode(sign(AI.vendor_id), 1,
POV.attribute13,HZP.attribute13) POV_Attribute13
,decode(sign(AI.vendor_id), 1,
POV.attribute14,HZP.attribute11) POV_Attribute14
,decode(sign(AI.vendor_id), 1,
POV.attribute15,HZP.attribute15) POV_Attribute15
,decode(sign(AI.vendor_id), 1,
POV.attribute2,HZP.attribute2 ) POV_Attribute2
,decode(sign(AI.vendor_id), 1,
POV.attribute3,HZP.attribute3) POV_Attribute3
,decode(sign(AI.vendor_id), 1,
POV.attribute4,HZP.attribute4) POV_Attribute4
,decode(sign(AI.vendor_id), 1,
POV.attribute5,HZP.attribute5) POV_Attribute5
,decode(sign(AI.vendor_id), 1,
POV.attribute6,HZP.attribute6) POV_Attribute6
,decode(sign(AI.vendor_id), 1,
POV.attribute7,HZP.attribute7) POV_Attribute7
,decode(sign(AI.vendor_id), 1,
POV.attribute8,HZP.attribute8) POV_Attribute8
,decode(sign(AI.vendor_id), 1,
POV.attribute9,HZP.attribute9) POV_Attribute9
,decode(sign(AI.vendor_id), 1,
POV.Vendor_Name_Alt,HZP.ORGANIZATION_NAME_PHONETIC)
POV_Vendor_Name_Alt
,decode(sign(AI.vendor_id), 1,
POV.vendor_id, NULL ) POV_vendor_id
,decode(sign(AI.vendor_id), 1,
POV.segment1, NULL ) POV_Segment1
,decode(sign(AI.vendor_id), 1,
POV.vendor_name, HZP.party_name) POV_Vendor_Name
,decode(sign(AI.vendor_id), 1,
POV.SEGMENT2, NULL) POV_Segment2
,decode(sign(AI.vendor_id), 1,
POV.SEGMENT3, NULL) POV_Segment3
,decode(sign(AI.vendor_id), 1,
POV.SEGMENT4, NULL) POV_Segment4
,decode(sign(AI.vendor_id), 1,
POV.SEGMENT5, NULL) POV_Segment5
,decode(sign(AI.vendor_id), 1,
POV.VENDOR_TYPE_LOOKUP_CODE,NULL) POV_Merchant_Type_Lookup_Code
,decode(sign(AI.vendor_id), 1,
POV.GLOBAL_ATTRIBUTE1,HZP.GLOBAL_ATTRIBUTE1 )
POV_Global_Attribute_1
,decode(sign(AI.vendor_id), 1,
POV.GLOBAL_ATTRIBUTE2,HZP.GLOBAL_ATTRIBUTE2)
POV_Global_Attribute_2
,decode(sign(AI.vendor_id), 1,
POV.GLOBAL_ATTRIBUTE3,HZP.GLOBAL_ATTRIBUTE3)
POV_Global_Attribute_3
,decode(sign(AI.vendor_id), 1,
POV.GLOBAL_ATTRIBUTE4, HZP.GLOBAL_ATTRIBUTE4)
POV_Global_Attribute_4
,decode(sign(AI.vendor_id), 1,
POV.GLOBAL_ATTRIBUTE5,HZP.GLOBAL_ATTRIBUTE5)
POV_Global_Attribute_5
,decode(sign(AI.vendor_id), 1,
POV.GLOBAL_ATTRIBUTE6, HZP.GLOBAL_ATTRIBUTE6)
POV_Global_Attribute_6
,decode(sign(AI.vendor_id), 1,
POV.GLOBAL_ATTRIBUTE7, HZP.GLOBAL_ATTRIBUTE7)
POV_Global_Attribute_7
,decode(sign(AI.vendor_id), 1,
POV.GLOBAL_ATTRIBUTE8,HZP.GLOBAL_ATTRIBUTE8)
POV_Global_Attribute_8
,decode(sign(AI.vendor_id), 1,
POV.GLOBAL_ATTRIBUTE9,HZP.GLOBAL_ATTRIBUTE9)
POV_Global_Attribute_9
,decode(sign(AI.vendor_id), 1,
POV.GLOBAL_ATTRIBUTE10,HZP.GLOBAL_ATTRIBUTE10)
POV_Global_Attribute_10
,decode(sign(AI.vendor_id), 1,
POV.GLOBAL_ATTRIBUTE11, HZP.GLOBAL_ATTRIBUTE11)
POV_Global_Attribute_11
,decode(sign(AI.vendor_id), 1,
POV.GLOBAL_ATTRIBUTE12,HZP.GLOBAL_ATTRIBUTE12)
POV_Global_Attribute_12
,decode(sign(AI.vendor_id), 1,
POV.GLOBAL_ATTRIBUTE13,HZP.GLOBAL_ATTRIBUTE13)
POV_Global_Attribute_13
,decode(sign(AI.vendor_id), 1,
POV.GLOBAL_ATTRIBUTE14,HZP.GLOBAL_ATTRIBUTE14)
POV_Global_Attribute_14
,decode(sign(AI.vendor_id), 1,
POV.GLOBAL_ATTRIBUTE15,HZP.GLOBAL_ATTRIBUTE15)
POV_Global_Attribute_15
,decode(sign(AI.vendor_id), 1,
POV.GLOBAL_ATTRIBUTE16, HZP.GLOBAL_ATTRIBUTE16)
POV_Global_Attribute_16
,decode(sign(AI.vendor_id), 1,
POV.GLOBAL_ATTRIBUTE17,HZP.GLOBAL_ATTRIBUTE17)
POV_Global_Attribute_17
,decode(sign(AI.vendor_id), 1,
POV.GLOBAL_ATTRIBUTE18,HZP.GLOBAL_ATTRIBUTE18)
POV_Global_Attribute_18
,decode(sign(AI.vendor_id), 1,
POV.GLOBAL_ATTRIBUTE19, HZP.GLOBAL_ATTRIBUTE19)
POV_Global_Attribute_19
,decode(sign(AI.vendor_id), 1,
POV.GLOBAL_ATTRIBUTE20,HZP.GLOBAL_ATTRIBUTE20)
POV_Global_Attribute_20
,decode(sign(AI.vendor_id), 1,
POV.GLOBAL_ATTRIBUTE_CATEGORY,HZP.GLOBAL_ATTRIBUTE_CATEGORY)
POV_Global_Attribute_Category
,decode(sign(AI.vendor_id), 1,
POV.num_1099,HZP.JGZZ_FISCAL_CODE)
POV_num_1099
,decode(sign(AI.vendor_id), 1,
POV.PARTY_ID, HZP.party_id) PARTY_ID
,decode(sign(AI.vendor_id), 1,
POS.attribute1, HPS.attribute1 ) POS_Attribute1
,decode(sign(AI.vendor_id), 1,
POS.attribute10,HPS.attribute10) POS_Attribute10
,decode(sign(AI.vendor_id), 1,
POS.attribute11,HPS.attribute11 ) POS_Attribute11
,decode(sign(AI.vendor_id), 1,
POS.attribute12,HPS.attribute12) POS_Attribute12
,decode(sign(AI.vendor_id), 1,
POS.attribute13,HPS.attribute13) POS_Attribute13
,decode(sign(AI.vendor_id), 1,
POS.attribute14,HPS.attribute14) POS_Attribute14
,decode(sign(AI.vendor_id), 1,
POS.attribute15,HPS.attribute15) POS_Attribute15
,decode(sign(AI.vendor_id), 1,
POS.attribute2,HPS.attribute2) POS_Attribute2
,decode(sign(AI.vendor_id), 1,
POS.attribute3,HPS.attribute3) POS_Attribute3
,decode(sign(AI.vendor_id), 1,
POS.attribute4,HPS.attribute4) POS_Attribute4
,decode(sign(AI.vendor_id), 1,
POS.attribute5,HPS.attribute5) POS_Attribute5
,decode(sign(AI.vendor_id), 1,
POS.attribute6,HPS.attribute6) POS_Attribute6
,decode(sign(AI.vendor_id), 1,
POS.attribute7,HPS.attribute7) POS_Attribute7
,decode(sign(AI.vendor_id), 1,
POS.attribute8,HPS.attribute8) POS_Attribute8
,decode(sign(AI.vendor_id), 1,
POS.attribute9,HPS.attribute9) POS_Attribute9
,decode(sign(AI.vendor_id), 1,
POS.attribute_category,HPS.attribute_category)
POS_Attribute_category
,decode(sign(AI.vendor_id), 1,
POS.address_line1, HL.address1 ) POS_address_line1
,decode(sign(AI.vendor_id), 1,
POS.address_line2,HL.address2) POS_address_line2
,decode(sign(AI.vendor_id), 1,
POS.address_line3,HL.address3) POS_address_line3
,decode(sign(AI.vendor_id), 1,
POS.address_line4,HL.address4) POS_address_line4
,decode(sign(AI.vendor_id), 1,
POS.address_lines_alt,HL.ADDRESS_LINES_PHONETIC)
POS_address_lines_alt
,decode(sign(AI.vendor_id), 1,
POS.city,HL.city) POS_City
,decode(sign(AI.vendor_id), 1,
POS.county, HL.county) POS_County
,decode(sign(AI.vendor_id), 1,
POS.country, HL.country) POS_Country
,decode(sign(AI.vendor_id), 1,
POS.vendor_site_code, NULL) POS_Vendor_Site_Code
,decode(sign(AI.vendor_id), 1,
POS.Zip,substr(HL.POSTAL_CODE, 1, 20))
POS_Zip
,decode(sign(AI.vendor_id), 1,
POS.province, HL.province) POS_Province
,decode(sign(AI.vendor_id), 1,
POS.State, HL.State) POS_State
,decode(sign(AI.vendor_id), 1,
POS.Vendor_Site_id, NULL) POS_Vendor_Site_Id
,decode(sign(AI.vendor_id), 1,
POS.GLOBAL_ATTRIBUTE1, HPS.GLOBAL_ATTRIBUTE1 )
POS_Global_Attribute_1
,decode(sign(AI.vendor_id), 1,
POS.GLOBAL_ATTRIBUTE2,HPS.GLOBAL_ATTRIBUTE2)
POS_Global_Attribute_2
,decode(sign(AI.vendor_id), 1,
POS.GLOBAL_ATTRIBUTE3,HPS.GLOBAL_ATTRIBUTE3)
POS_Global_Attribute_3
,decode(sign(AI.vendor_id), 1,
POS.GLOBAL_ATTRIBUTE4,HPS.GLOBAL_ATTRIBUTE4)
POS_Global_Attribute_4
,decode(sign(AI.vendor_id), 1,
POS.GLOBAL_ATTRIBUTE5,HPS.GLOBAL_ATTRIBUTE5)
POS_Global_Attribute_5
,decode(sign(AI.vendor_id), 1,
POS.GLOBAL_ATTRIBUTE6,HPS.GLOBAL_ATTRIBUTE6)
POS_Global_Attribute_6
,decode(sign(AI.vendor_id), 1,
POS.GLOBAL_ATTRIBUTE7,HPS.GLOBAL_ATTRIBUTE7)
POS_Global_Attribute_7
,decode(sign(AI.vendor_id), 1,
POS.GLOBAL_ATTRIBUTE8,HPS.GLOBAL_ATTRIBUTE8)
POS_Global_Attribute_8
,decode(sign(AI.vendor_id), 1,
POS.GLOBAL_ATTRIBUTE9,HPS.GLOBAL_ATTRIBUTE9)
POS_Global_Attribute_9
,decode(sign(AI.vendor_id), 1,
POS.GLOBAL_ATTRIBUTE10,HPS.GLOBAL_ATTRIBUTE10)
POS_Global_Attribute_10
,decode(sign(AI.vendor_id), 1,
POS.GLOBAL_ATTRIBUTE11,HPS.GLOBAL_ATTRIBUTE11)
POS_Global_Attribute_11
,decode(sign(AI.vendor_id), 1,
POS.GLOBAL_ATTRIBUTE12,HPS.GLOBAL_ATTRIBUTE12)
POS_Global_Attribute_12
,decode(sign(AI.vendor_id), 1,
POS.GLOBAL_ATTRIBUTE13,HPS.GLOBAL_ATTRIBUTE13)
POS_Global_Attribute_13
,decode(sign(AI.vendor_id), 1,
POS.GLOBAL_ATTRIBUTE14,HPS.GLOBAL_ATTRIBUTE14)
POS_Global_Attribute_14
,decode(sign(AI.vendor_id), 1,
POS.GLOBAL_ATTRIBUTE15,HPS.GLOBAL_ATTRIBUTE15)
POS_Global_Attribute_15
,decode(sign(AI.vendor_id), 1,
POS.GLOBAL_ATTRIBUTE16,HPS.GLOBAL_ATTRIBUTE16)
POS_Global_Attribute_16
,decode(sign(AI.vendor_id), 1,
POS.GLOBAL_ATTRIBUTE17,HPS.GLOBAL_ATTRIBUTE17)
POS_Global_Attribute_17
,decode(sign(AI.vendor_id), 1,
POS.GLOBAL_ATTRIBUTE18,HPS.GLOBAL_ATTRIBUTE18)
POS_Global_Attribute_18
,decode(sign(AI.vendor_id), 1,
POS.GLOBAL_ATTRIBUTE19,HPS.GLOBAL_ATTRIBUTE19)
POS_Global_Attribute_19
,decode(sign(AI.vendor_id), 1,
POS.GLOBAL_ATTRIBUTE20,HPS.GLOBAL_ATTRIBUTE20)
POS_Global_Attribute_20
,decode(sign(AI.vendor_id), 1,
POS.GLOBAL_ATTRIBUTE_CATEGORY,HPS.GLOBAL_ATTRIBUTE_CATEGORY)
POS_Global_Attribute_Category
,decode(sign(AI.vendor_id), 1,
POS.future_dated_payment_ccid, NULL)
POS_FDP_CCID
,decode(sign(AI.vendor_id), 1,
POS.PARTY_SITE_ID,HPS.PARTY_SITE_ID)
POS_PARTY_SITE_ID
,decode(sign(AI.vendor_id), 1,
POS.ORG_ID, NULL) POS_ORG_ID
,decode(sign(AI.vendor_id), 1,
POS.vendor_id, NULL) POS_VENDOR_ID
,AI.payment_status_flag AI_Payment_Status_Flag
FROM AP_INVOICES_ALL AI,
AP_BATCHES_ALL AB,
FND_DOCUMENT_SEQUENCES FDS,
XLA_EVENTS_GT XE,
ap_suppliers POV,
AP_SUPPLIER_SITES_ALL POS,
HZ_PARTIES HZP,
HZ_PARTY_SITES HPS,
HZ_LOCATIONS HL
WHERE AI.batch_id = AB.batch_id(+)
AND XE.source_id_int_1 = AI.invoice_id
AND XE.application_id = 200
AND AI.doc_sequence_id = FDS.doc_sequence_id(+)
AND XE.entity_code = 'AP_INVOICES'
AND AI.vendor_id = POV.vendor_id (+)
AND AI.vendor_site_id = POS.vendor_site_id(+)
AND HZP.PARTY_ID = AI.party_id
AND AI.PARTY_SITE_ID = HPS.PARTY_SITE_ID(+)
AND HPS.LOCATION_ID = HL.LOCATION_ID(+)
SELECT XE.EVENT_ID EVENT_ID
, AI.SOURCE AI_SOURCE
, AI.INVOICE_NUM INV_TRANSACTION_NUMBER
, AI.DOC_SEQUENCE_ID INV_DOC_SEQUENCE_IDENTIFIER
, AI.DOC_SEQUENCE_VALUE INV_DOC_SEQUENCE_VALUE
, AI.DOC_CATEGORY_CODE INV_DOC_SEQUENCE_CATEGORY
, FDS.NAME DOC_SEQUENCE_NAME
, DECODE(SIGN(AI.VENDOR_ID)
, 1
, 'S'
, NULL) INV_PARTY_TYPE
, DECODE(SIGN(AI.VENDOR_ID)
, 1
, 'S'
, NULL) THIRD_PARTY_TYPE
, AB.BATCH_NAME AB_BATCH_NAME
, AI.ACCTS_PAY_CODE_COMBINATION_ID AI_ACCTS_PAY_CCID
, AI.INVOICE_ID AI_INVOICE_ID
, AI.ORG_ID AI_ORG_ID
, AI.INVOICE_CURRENCY_CODE AI_INVOICE_CURRENCY_CODE
, DECODE(SIGN(AI.VENDOR_ID)
, 1
,
AI.VENDOR_ID
, NULL) AI_VENDOR_ID
, DECODE(SIGN(AI.VENDOR_ID)
, 1
,
AI.VENDOR_SITE_ID
, NULL) AI_VENDOR_SITE_ID
, AI.ATTRIBUTE_CATEGORY AI_ATTRIBUTE_CATEGORY
, AI.ATTRIBUTE1 AI_ATTRIBUTE1
, AI.ATTRIBUTE10 AI_ATTRIBUTE10
, AI.ATTRIBUTE11 AI_ATTRIBUTE11
, AI.ATTRIBUTE12 AI_ATTRIBUTE12
, AI.ATTRIBUTE13 AI_ATTRIBUTE13
, AI.ATTRIBUTE14 AI_ATTRIBUTE14
, AI.ATTRIBUTE15 AI_ATTRIBUTE15
, AI.ATTRIBUTE2 AI_ATTRIBUTE2
, AI.ATTRIBUTE3 AI_ATTRIBUTE3
, AI.ATTRIBUTE4 AI_ATTRIBUTE4
, AI.ATTRIBUTE5 AI_ATTRIBUTE5
, AI.ATTRIBUTE6 AI_ATTRIBUTE6
, AI.ATTRIBUTE7 AI_ATTRIBUTE7
, AI.ATTRIBUTE8 AI_ATTRIBUTE8
, AI.ATTRIBUTE9 AI_ATTRIBUTE9
, AI.CANCELLED_DATE AI_CANCELLED_DATE
, AI.DESCRIPTION AI_DESCRIPTION
, AI.INVOICE_AMOUNT AI_INVOICE_AMOUNT
, AI.BASE_AMOUNT AI_INVOICE_BASE_AMOUNT
, AI.INVOICE_DATE AI_INVOICE_DATE
, AI.INVOICE_TYPE_LOOKUP_CODE AI_INVOICE_TYPE_LOOKUP_CODE
, AI.VAT_CODE AI_VAT_CODE
, AI.VOUCHER_NUM AI_VOUCHER_NUM
, AI.AMOUNT_PAID INV_AMOUNT_PAID
, AI.DISCOUNT_AMOUNT_TAKEN INV_DISCOUNT_AMOUNT_TAKEN
, AI.INVOICE_DISTRIBUTION_TOTAL INV_DISTRIBUTION_TOTAL
, AI.AWT_GROUP_ID INV_AWT_GROUP_ID
, AI.GLOBAL_ATTRIBUTE_CATEGORY INV_GLOBAL_ATTRIBUTE_CATEGORY
, AI.GLOBAL_ATTRIBUTE1 INV_GLOBAL_ATTRIBUTE_1
, AI.GLOBAL_ATTRIBUTE2 INV_GLOBAL_ATTRIBUTE_2
, AI.GLOBAL_ATTRIBUTE3 INV_GLOBAL_ATTRIBUTE_3
, AI.GLOBAL_ATTRIBUTE4 INV_GLOBAL_ATTRIBUTE_4
, AI.GLOBAL_ATTRIBUTE5 INV_GLOBAL_ATTRIBUTE_5
, AI.GLOBAL_ATTRIBUTE6 INV_GLOBAL_ATTRIBUTE_6
, AI.GLOBAL_ATTRIBUTE7 INV_GLOBAL_ATTRIBUTE_7
, AI.GLOBAL_ATTRIBUTE8 INV_GLOBAL_ATTRIBUTE_8
, AI.GLOBAL_ATTRIBUTE9 INV_GLOBAL_ATTRIBUTE_9
, AI.GLOBAL_ATTRIBUTE10 INV_GLOBAL_ATTRIBUTE_10
, AI.GLOBAL_ATTRIBUTE11 INV_GLOBAL_ATTRIBUTE_11
, AI.GLOBAL_ATTRIBUTE12 INV_GLOBAL_ATTRIBUTE_12
, AI.GLOBAL_ATTRIBUTE13 INV_GLOBAL_ATTRIBUTE_13
, AI.GLOBAL_ATTRIBUTE14 INV_GLOBAL_ATTRIBUTE_14
, AI.GLOBAL_ATTRIBUTE15 INV_GLOBAL_ATTRIBUTE_15
, AI.GLOBAL_ATTRIBUTE16 INV_GLOBAL_ATTRIBUTE_16
, AI.GLOBAL_ATTRIBUTE17 INV_GLOBAL_ATTRIBUTE_17
, AI.GLOBAL_ATTRIBUTE18 INV_GLOBAL_ATTRIBUTE_18
, AI.GLOBAL_ATTRIBUTE19 INV_GLOBAL_ATTRIBUTE_19
, AI.GLOBAL_ATTRIBUTE20 INV_GLOBAL_ATTRIBUTE_20
, AI.GL_DATE INV_GL_DATE
, AI.HISTORICAL_FLAG INV_HISTORICAL_FLAG
, DECODE(XE.EVENT_TYPE_CODE
,
'INVOICE CANCELLED'
, 'Y'
,
'DEBIT MEMO CANCELLED'
, 'Y'
,
'CREDIT MEMO CANCELLED'
, 'Y'
,
'PREPAYMENT CANCELLED'
, 'Y'
, 'N') TRANSAC_REVERSAL_FLAG
, AI.SET_OF_BOOKS_ID LEDGER_ID
, NVL(AI.EXCHANGE_DATE
, AI.GL_DATE) INV_EXCHANGE_DATE
, AI.EXCHANGE_RATE INV_EXCHANGE_RATE
, AI.EXCHANGE_RATE_TYPE INV_EXCHANGE_RATE_TYPE
, AI.PARTY_ID AI_PARTY_ID
, AI.PARTY_SITE_ID AI_PARTY_SITE_ID
, DECODE(SIGN(AI.VENDOR_ID)
, 1
,
POV.ATTRIBUTE_CATEGORY
, HZP.ATTRIBUTE_CATEGORY)
POV_ATTRIBUTE_CATEGORY
, DECODE(SIGN(AI.VENDOR_ID)
, 1
,
POV.ATTRIBUTE1
, HZP.ATTRIBUTE1 )
POV_ATTRIBUTE1
, DECODE(SIGN(AI.VENDOR_ID)
, 1
,
POV.ATTRIBUTE10
, HZP.ATTRIBUTE10 )
POV_ATTRIBUTE10
, DECODE(SIGN(AI.VENDOR_ID)
, 1
,
POV.ATTRIBUTE11
, HZP.ATTRIBUTE11) POV_ATTRIBUTE11
, DECODE(SIGN(AI.VENDOR_ID)
, 1
,
POV.ATTRIBUTE12
, HZP.ATTRIBUTE12) POV_ATTRIBUTE12
, DECODE(SIGN(AI.VENDOR_ID)
, 1
,
POV.ATTRIBUTE13
, HZP.ATTRIBUTE13) POV_ATTRIBUTE13
, DECODE(SIGN(AI.VENDOR_ID)
, 1
,
POV.ATTRIBUTE14
, HZP.ATTRIBUTE11) POV_ATTRIBUTE14
, DECODE(SIGN(AI.VENDOR_ID)
, 1
,
POV.ATTRIBUTE15
, HZP.ATTRIBUTE15) POV_ATTRIBUTE15
, DECODE(SIGN(AI.VENDOR_ID)
, 1
,
POV.ATTRIBUTE2
, HZP.ATTRIBUTE2 ) POV_ATTRIBUTE2
, DECODE(SIGN(AI.VENDOR_ID)
, 1
,
POV.ATTRIBUTE3
, HZP.ATTRIBUTE3) POV_ATTRIBUTE3
, DECODE(SIGN(AI.VENDOR_ID)
, 1
,
POV.ATTRIBUTE4
, HZP.ATTRIBUTE4) POV_ATTRIBUTE4
, DECODE(SIGN(AI.VENDOR_ID)
, 1
,
POV.ATTRIBUTE5
, HZP.ATTRIBUTE5) POV_ATTRIBUTE5
, DECODE(SIGN(AI.VENDOR_ID)
, 1
,
POV.ATTRIBUTE6
, HZP.ATTRIBUTE6) POV_ATTRIBUTE6
, DECODE(SIGN(AI.VENDOR_ID)
, 1
,
POV.ATTRIBUTE7
, HZP.ATTRIBUTE7) POV_ATTRIBUTE7
, DECODE(SIGN(AI.VENDOR_ID)
, 1
,
POV.ATTRIBUTE8
, HZP.ATTRIBUTE8) POV_ATTRIBUTE8
, DECODE(SIGN(AI.VENDOR_ID)
, 1
,
POV.ATTRIBUTE9
, HZP.ATTRIBUTE9) POV_ATTRIBUTE9
, DECODE(SIGN(AI.VENDOR_ID)
, 1
,
POV.VENDOR_NAME_ALT
, HZP.ORGANIZATION_NAME_PHONETIC)
POV_VENDOR_NAME_ALT
, DECODE(SIGN(AI.VENDOR_ID)
, 1
,
POV.VENDOR_ID
, NULL ) POV_VENDOR_ID
, DECODE(SIGN(AI.VENDOR_ID)
, 1
,
POV.SEGMENT1
, NULL ) POV_SEGMENT1
, DECODE(SIGN(AI.VENDOR_ID)
, 1
,
POV.VENDOR_NAME
, HZP.PARTY_NAME) POV_VENDOR_NAME
, DECODE(SIGN(AI.VENDOR_ID)
, 1
,
POV.SEGMENT2
, NULL) POV_SEGMENT2
, DECODE(SIGN(AI.VENDOR_ID)
, 1
,
POV.SEGMENT3
, NULL) POV_SEGMENT3
, DECODE(SIGN(AI.VENDOR_ID)
, 1
,
POV.SEGMENT4
, NULL) POV_SEGMENT4
, DECODE(SIGN(AI.VENDOR_ID)
, 1
,
POV.SEGMENT5
, NULL) POV_SEGMENT5
, DECODE(SIGN(AI.VENDOR_ID)
, 1
,
POV.VENDOR_TYPE_LOOKUP_CODE
, NULL) POV_MERCHANT_TYPE_LOOKUP_CODE
, DECODE(SIGN(AI.VENDOR_ID)
, 1
,
POV.GLOBAL_ATTRIBUTE1
, HZP.GLOBAL_ATTRIBUTE1 )
POV_GLOBAL_ATTRIBUTE_1
, DECODE(SIGN(AI.VENDOR_ID)
, 1
,
POV.GLOBAL_ATTRIBUTE2
, HZP.GLOBAL_ATTRIBUTE2)
POV_GLOBAL_ATTRIBUTE_2
, DECODE(SIGN(AI.VENDOR_ID)
, 1
,
POV.GLOBAL_ATTRIBUTE3
, HZP.GLOBAL_ATTRIBUTE3)
POV_GLOBAL_ATTRIBUTE_3
, DECODE(SIGN(AI.VENDOR_ID)
, 1
,
POV.GLOBAL_ATTRIBUTE4
, HZP.GLOBAL_ATTRIBUTE4)
POV_GLOBAL_ATTRIBUTE_4
, DECODE(SIGN(AI.VENDOR_ID)
, 1
,
POV.GLOBAL_ATTRIBUTE5
, HZP.GLOBAL_ATTRIBUTE5)
POV_GLOBAL_ATTRIBUTE_5
, DECODE(SIGN(AI.VENDOR_ID)
, 1
,
POV.GLOBAL_ATTRIBUTE6
, HZP.GLOBAL_ATTRIBUTE6)
POV_GLOBAL_ATTRIBUTE_6
, DECODE(SIGN(AI.VENDOR_ID)
, 1
,
POV.GLOBAL_ATTRIBUTE7
, HZP.GLOBAL_ATTRIBUTE7)
POV_GLOBAL_ATTRIBUTE_7
, DECODE(SIGN(AI.VENDOR_ID)
, 1
,
POV.GLOBAL_ATTRIBUTE8
, HZP.GLOBAL_ATTRIBUTE8)
POV_GLOBAL_ATTRIBUTE_8
, DECODE(SIGN(AI.VENDOR_ID)
, 1
,
POV.GLOBAL_ATTRIBUTE9
, HZP.GLOBAL_ATTRIBUTE9)
POV_GLOBAL_ATTRIBUTE_9
, DECODE(SIGN(AI.VENDOR_ID)
, 1
,
POV.GLOBAL_ATTRIBUTE10
, HZP.GLOBAL_ATTRIBUTE10)
POV_GLOBAL_ATTRIBUTE_10
, DECODE(SIGN(AI.VENDOR_ID)
, 1
,
POV.GLOBAL_ATTRIBUTE11
, HZP.GLOBAL_ATTRIBUTE11)
POV_GLOBAL_ATTRIBUTE_11
, DECODE(SIGN(AI.VENDOR_ID)
, 1
,
POV.GLOBAL_ATTRIBUTE12
, HZP.GLOBAL_ATTRIBUTE12)
POV_GLOBAL_ATTRIBUTE_12
, DECODE(SIGN(AI.VENDOR_ID)
, 1
,
POV.GLOBAL_ATTRIBUTE13
, HZP.GLOBAL_ATTRIBUTE13)
POV_GLOBAL_ATTRIBUTE_13
, DECODE(SIGN(AI.VENDOR_ID)
, 1
,
POV.GLOBAL_ATTRIBUTE14
, HZP.GLOBAL_ATTRIBUTE14)
POV_GLOBAL_ATTRIBUTE_14
, DECODE(SIGN(AI.VENDOR_ID)
, 1
,
POV.GLOBAL_ATTRIBUTE15
, HZP.GLOBAL_ATTRIBUTE15)
POV_GLOBAL_ATTRIBUTE_15
, DECODE(SIGN(AI.VENDOR_ID)
, 1
,
POV.GLOBAL_ATTRIBUTE16
, HZP.GLOBAL_ATTRIBUTE16)
POV_GLOBAL_ATTRIBUTE_16
, DECODE(SIGN(AI.VENDOR_ID)
, 1
,
POV.GLOBAL_ATTRIBUTE17
, HZP.GLOBAL_ATTRIBUTE17)
POV_GLOBAL_ATTRIBUTE_17
, DECODE(SIGN(AI.VENDOR_ID)
, 1
,
POV.GLOBAL_ATTRIBUTE18
, HZP.GLOBAL_ATTRIBUTE18)
POV_GLOBAL_ATTRIBUTE_18
, DECODE(SIGN(AI.VENDOR_ID)
, 1
,
POV.GLOBAL_ATTRIBUTE19
, HZP.GLOBAL_ATTRIBUTE19)
POV_GLOBAL_ATTRIBUTE_19
, DECODE(SIGN(AI.VENDOR_ID)
, 1
,
POV.GLOBAL_ATTRIBUTE20
, HZP.GLOBAL_ATTRIBUTE20)
POV_GLOBAL_ATTRIBUTE_20
, DECODE(SIGN(AI.VENDOR_ID)
, 1
,
POV.GLOBAL_ATTRIBUTE_CATEGORY
, HZP.GLOBAL_ATTRIBUTE_CATEGORY)
POV_GLOBAL_ATTRIBUTE_CATEGORY
, DECODE(SIGN(AI.VENDOR_ID)
, 1
,
POV.NUM_1099
, HZP.JGZZ_FISCAL_CODE)
POV_NUM_1099
, DECODE(SIGN(AI.VENDOR_ID)
, 1
,
POV.PARTY_ID
, HZP.PARTY_ID) PARTY_ID
, DECODE(SIGN(AI.VENDOR_ID)
, 1
,
POS.ATTRIBUTE1
, HPS.ATTRIBUTE1 ) POS_ATTRIBUTE1
, DECODE(SIGN(AI.VENDOR_ID)
, 1
,
POS.ATTRIBUTE10
, HPS.ATTRIBUTE10) POS_ATTRIBUTE10
, DECODE(SIGN(AI.VENDOR_ID)
, 1
,
POS.ATTRIBUTE11
, HPS.ATTRIBUTE11 ) POS_ATTRIBUTE11
, DECODE(SIGN(AI.VENDOR_ID)
, 1
,
POS.ATTRIBUTE12
, HPS.ATTRIBUTE12) POS_ATTRIBUTE12
, DECODE(SIGN(AI.VENDOR_ID)
, 1
,
POS.ATTRIBUTE13
, HPS.ATTRIBUTE13) POS_ATTRIBUTE13
, DECODE(SIGN(AI.VENDOR_ID)
, 1
,
POS.ATTRIBUTE14
, HPS.ATTRIBUTE14) POS_ATTRIBUTE14
, DECODE(SIGN(AI.VENDOR_ID)
, 1
,
POS.ATTRIBUTE15
, HPS.ATTRIBUTE15) POS_ATTRIBUTE15
, DECODE(SIGN(AI.VENDOR_ID)
, 1
,
POS.ATTRIBUTE2
, HPS.ATTRIBUTE2) POS_ATTRIBUTE2
, DECODE(SIGN(AI.VENDOR_ID)
, 1
,
POS.ATTRIBUTE3
, HPS.ATTRIBUTE3) POS_ATTRIBUTE3
, DECODE(SIGN(AI.VENDOR_ID)
, 1
,
POS.ATTRIBUTE4
, HPS.ATTRIBUTE4) POS_ATTRIBUTE4
, DECODE(SIGN(AI.VENDOR_ID)
, 1
,
POS.ATTRIBUTE5
, HPS.ATTRIBUTE5) POS_ATTRIBUTE5
, DECODE(SIGN(AI.VENDOR_ID)
, 1
,
POS.ATTRIBUTE6
, HPS.ATTRIBUTE6) POS_ATTRIBUTE6
, DECODE(SIGN(AI.VENDOR_ID)
, 1
,
POS.ATTRIBUTE7
, HPS.ATTRIBUTE7) POS_ATTRIBUTE7
, DECODE(SIGN(AI.VENDOR_ID)
, 1
,
POS.ATTRIBUTE8
, HPS.ATTRIBUTE8) POS_ATTRIBUTE8
, DECODE(SIGN(AI.VENDOR_ID)
, 1
,
POS.ATTRIBUTE9
, HPS.ATTRIBUTE9) POS_ATTRIBUTE9
, DECODE(SIGN(AI.VENDOR_ID)
, 1
,
POS.ATTRIBUTE_CATEGORY
, HPS.ATTRIBUTE_CATEGORY)
POS_ATTRIBUTE_CATEGORY
, DECODE(SIGN(AI.VENDOR_ID)
, 1
,
POS.ADDRESS_LINE1
, HL.ADDRESS1 ) POS_ADDRESS_LINE1
, DECODE(SIGN(AI.VENDOR_ID)
, 1
,
POS.ADDRESS_LINE2
, HL.ADDRESS2) POS_ADDRESS_LINE2
, DECODE(SIGN(AI.VENDOR_ID)
, 1
,
POS.ADDRESS_LINE3
, HL.ADDRESS3) POS_ADDRESS_LINE3
, DECODE(SIGN(AI.VENDOR_ID)
, 1
,
POS.ADDRESS_LINE4
, HL.ADDRESS4) POS_ADDRESS_LINE4
, DECODE(SIGN(AI.VENDOR_ID)
, 1
,
POS.ADDRESS_LINES_ALT
, HL.ADDRESS_LINES_PHONETIC)
POS_ADDRESS_LINES_ALT
, DECODE(SIGN(AI.VENDOR_ID)
, 1
,
POS.CITY
, HL.CITY) POS_CITY
, DECODE(SIGN(AI.VENDOR_ID)
, 1
,
POS.COUNTY
, HL.COUNTY) POS_COUNTY
, DECODE(SIGN(AI.VENDOR_ID)
, 1
,
POS.COUNTRY
, HL.COUNTRY) POS_COUNTRY
, DECODE(SIGN(AI.VENDOR_ID)
, 1
,
POS.VENDOR_SITE_CODE
, NULL) POS_VENDOR_SITE_CODE
, DECODE(SIGN(AI.VENDOR_ID)
, 1
,
POS.ZIP
, SUBSTR(HL.POSTAL_CODE
, 1
, 20))
POS_ZIP
, DECODE(SIGN(AI.VENDOR_ID)
, 1
,
POS.PROVINCE
, HL.PROVINCE) POS_PROVINCE
, DECODE(SIGN(AI.VENDOR_ID)
, 1
,
POS.STATE
, HL.STATE) POS_STATE
, DECODE(SIGN(AI.VENDOR_ID)
, 1
,
POS.VENDOR_SITE_ID
, NULL) POS_VENDOR_SITE_ID
, DECODE(SIGN(AI.VENDOR_ID)
, 1
,
POS.GLOBAL_ATTRIBUTE1
, HPS.GLOBAL_ATTRIBUTE1 )
POS_GLOBAL_ATTRIBUTE_1
, DECODE(SIGN(AI.VENDOR_ID)
, 1
,
POS.GLOBAL_ATTRIBUTE2
, HPS.GLOBAL_ATTRIBUTE2)
POS_GLOBAL_ATTRIBUTE_2
, DECODE(SIGN(AI.VENDOR_ID)
, 1
,
POS.GLOBAL_ATTRIBUTE3
, HPS.GLOBAL_ATTRIBUTE3)
POS_GLOBAL_ATTRIBUTE_3
, DECODE(SIGN(AI.VENDOR_ID)
, 1
,
POS.GLOBAL_ATTRIBUTE4
, HPS.GLOBAL_ATTRIBUTE4)
POS_GLOBAL_ATTRIBUTE_4
, DECODE(SIGN(AI.VENDOR_ID)
, 1
,
POS.GLOBAL_ATTRIBUTE5
, HPS.GLOBAL_ATTRIBUTE5)
POS_GLOBAL_ATTRIBUTE_5
, DECODE(SIGN(AI.VENDOR_ID)
, 1
,
POS.GLOBAL_ATTRIBUTE6
, HPS.GLOBAL_ATTRIBUTE6)
POS_GLOBAL_ATTRIBUTE_6
, DECODE(SIGN(AI.VENDOR_ID)
, 1
,
POS.GLOBAL_ATTRIBUTE7
, HPS.GLOBAL_ATTRIBUTE7)
POS_GLOBAL_ATTRIBUTE_7
, DECODE(SIGN(AI.VENDOR_ID)
, 1
,
POS.GLOBAL_ATTRIBUTE8
, HPS.GLOBAL_ATTRIBUTE8)
POS_GLOBAL_ATTRIBUTE_8
, DECODE(SIGN(AI.VENDOR_ID)
, 1
,
POS.GLOBAL_ATTRIBUTE9
, HPS.GLOBAL_ATTRIBUTE9)
POS_GLOBAL_ATTRIBUTE_9
, DECODE(SIGN(AI.VENDOR_ID)
, 1
,
POS.GLOBAL_ATTRIBUTE10
, HPS.GLOBAL_ATTRIBUTE10)
POS_GLOBAL_ATTRIBUTE_10
, DECODE(SIGN(AI.VENDOR_ID)
, 1
,
POS.GLOBAL_ATTRIBUTE11
, HPS.GLOBAL_ATTRIBUTE11)
POS_GLOBAL_ATTRIBUTE_11
, DECODE(SIGN(AI.VENDOR_ID)
, 1
,
POS.GLOBAL_ATTRIBUTE12
, HPS.GLOBAL_ATTRIBUTE12)
POS_GLOBAL_ATTRIBUTE_12
, DECODE(SIGN(AI.VENDOR_ID)
, 1
,
POS.GLOBAL_ATTRIBUTE13
, HPS.GLOBAL_ATTRIBUTE13)
POS_GLOBAL_ATTRIBUTE_13
, DECODE(SIGN(AI.VENDOR_ID)
, 1
,
POS.GLOBAL_ATTRIBUTE14
, HPS.GLOBAL_ATTRIBUTE14)
POS_GLOBAL_ATTRIBUTE_14
, DECODE(SIGN(AI.VENDOR_ID)
, 1
,
POS.GLOBAL_ATTRIBUTE15
, HPS.GLOBAL_ATTRIBUTE15)
POS_GLOBAL_ATTRIBUTE_15
, DECODE(SIGN(AI.VENDOR_ID)
, 1
,
POS.GLOBAL_ATTRIBUTE16
, HPS.GLOBAL_ATTRIBUTE16)
POS_GLOBAL_ATTRIBUTE_16
, DECODE(SIGN(AI.VENDOR_ID)
, 1
,
POS.GLOBAL_ATTRIBUTE17
, HPS.GLOBAL_ATTRIBUTE17)
POS_GLOBAL_ATTRIBUTE_17
, DECODE(SIGN(AI.VENDOR_ID)
, 1
,
POS.GLOBAL_ATTRIBUTE18
, HPS.GLOBAL_ATTRIBUTE18)
POS_GLOBAL_ATTRIBUTE_18
, DECODE(SIGN(AI.VENDOR_ID)
, 1
,
POS.GLOBAL_ATTRIBUTE19
, HPS.GLOBAL_ATTRIBUTE19)
POS_GLOBAL_ATTRIBUTE_19
, DECODE(SIGN(AI.VENDOR_ID)
, 1
,
POS.GLOBAL_ATTRIBUTE20
, HPS.GLOBAL_ATTRIBUTE20)
POS_GLOBAL_ATTRIBUTE_20
, DECODE(SIGN(AI.VENDOR_ID)
, 1
,
POS.GLOBAL_ATTRIBUTE_CATEGORY
, HPS.GLOBAL_ATTRIBUTE_CATEGORY)
POS_GLOBAL_ATTRIBUTE_CATEGORY
, DECODE(SIGN(AI.VENDOR_ID)
, 1
,
POS.FUTURE_DATED_PAYMENT_CCID
, NULL)
POS_FDP_CCID
, DECODE(SIGN(AI.VENDOR_ID)
, 1
,
POS.PARTY_SITE_ID
, HPS.PARTY_SITE_ID)
POS_PARTY_SITE_ID
, DECODE(SIGN(AI.VENDOR_ID)
, 1
,
POS.ORG_ID
, NULL) POS_ORG_ID
, DECODE(SIGN(AI.VENDOR_ID)
, 1
,
POS.VENDOR_ID
, NULL) POS_VENDOR_ID
, AI.PAYMENT_STATUS_FLAG AI_PAYMENT_STATUS_FLAG
FROM AP_INVOICES_ALL AI
,
AP_BATCHES_ALL AB
,
FND_DOCUMENT_SEQUENCES FDS
,
XLA_EVENTS_GT XE
,
AP_SUPPLIERS POV
,
AP_SUPPLIER_SITES_ALL POS
,
HZ_PARTIES HZP
,
HZ_PARTY_SITES HPS
,
HZ_LOCATIONS HL
WHERE AI.BATCH_ID = AB.BATCH_ID(+)
AND XE.SOURCE_ID_INT_1 = AI.INVOICE_ID
AND XE.APPLICATION_ID = 200
AND AI.DOC_SEQUENCE_ID = FDS.DOC_SEQUENCE_ID(+)
AND XE.ENTITY_CODE = 'AP_INVOICES'
AND AI.VENDOR_ID = POV.VENDOR_ID (+)
AND AI.VENDOR_SITE_ID = POS.VENDOR_SITE_ID(+)
AND HZP.PARTY_ID = AI.PARTY_ID
AND AI.PARTY_SITE_ID = HPS.PARTY_SITE_ID(+)
AND HPS.LOCATION_ID = HL.LOCATION_ID(+)
|
|
|
|