1. Tables and Views for Financials
  2. AR_XML_INVOICE_V

AR_XML_INVOICE_V

Details

  • Schema: FUSION

  • Object owner: AR

  • Object type: VIEW

Columns

Name

DOCUMENT_TRANSFER_ID

CUSTOMER_TRX_ID

TERM_ID

AMOUNT

CURRENCY_CODE

TRX_DATE

TRX_NUMBER

PURCHASE_ORDER

DOCUMENT_TYPE

PAYMENT_METHOD

SUPPLIER_NAME

SUPPLIER_CODE

SALES_REPRESENT

SALES_REPRESENT_EMAIL

REFERENCE_NUMBER

SHIP_DATE

TAX_AMOUNT

SHIP_TO_CUSTOMER_ID

SHIP_TO_SITE_USE_ID

SHIP_TO_CONTACT_ID

BILL_TO_CUSTOMER_ID

BILL_TO_SITE_USE_ID

BILL_TO_CONTACT_ID

REMIT_TO_ADDRESS_ID

SUPPLIER_DESCRIPTION

SUPPLIER_PRIMARY_ADDRESS

SUPPLIER_ADDRESS_LINE_ONE

SUPPLIER_ADDRESS_LINE_TWO

SUPPLIER_ADDRESS_LINE_THREE

SUPPLIER_TOWN_OR_CITY

SUPPLIER_COUNTRY

SUPPLIER_REGION_ONE

SUPPLIER_REGION_TWO

SUPPLIER_REGION_THREE

SUPPLIER_POSTAL_CODE

SUPPLIER_PHONE_NUMBER_ONE

SUPPLIER_PHONE_NUMBER_TWO

SUPPLIER_PHONE_NUMBER_THREE

FREIGHT_CODE

FREIGHT_DESCRIPTION

WAYBILL_NUMBER

LEGAL_ENTITY_NAME

LEGAL_ENTITY_TAXPAYER_ID

TRANSACTION_CLASS

LEGAL_ENTITY_PRIMARY_ADDRESS

HEADER_USER_ONE

HEADER_USER_TWO

HEADER_USER_THREE

HEADER_USER_FOUR

HEADER_USER_FIVE

HEADER_USER_SIX

HEADER_USER_SEVEN

HEADER_USER_EIGHT

HEADER_USER_NINE

HEADER_USER_TEN

HEADER_USER_ELEVEN

HEADER_USER_TWELVE

HEADER_USER_THIRTEEN

HEADER_USER_FOURTEEN

HEADER_USER_FIFTEEN

HEADER_USER_SIXTEEN

HEADER_USER_SEVENTEEN

HEADER_USER_EIGHTEEN

HEADER_USER_NINETEEN

HEADER_USER_TWENTY

HEADER_USER_TWENTY_ONE

HEADER_USER_TWENTY_TWO

HEADER_USER_TWENTY_THREE

HEADER_USER_TWENTY_FOUR

HEADER_USER_TWENTY_FIVE

HEADER_USER_TWENTY_SIX

HEADER_USER_TWENTY_SEVEN

HEADER_USER_TWENTY_EIGHT

HEADER_USER_TWENTY_NINE

HEADER_USER_THIRTY

DOCUMENT_SEQUENCE_NUMBER

LINE_COUNT

CT_REFERENCE

ORIGINAL_TRX_NUMBER

ORIG_TRX_DATE

SUPPLIER_REG_NUMBER

TAX_REGIME_NAME

TAX_TYPE_CODE

LEGAL_ENTITY_POSTBOX

LEGAL_ENTITY_ADDR_LINE_ONE

LEGAL_ENTITY_ADDR_LINE_TWO

LEGAL_ENTITY_ADDR_LINE_THREE

LEGAL_ENTITY_TOWN_OR_CITY

LEGAL_ENTITY_COUNTY

LEGAL_ENTITY_COUNTRY_CODE

RECEIPT_METHOD_NAME

PAYMENT_CHANNEL_CODE

SUPPLIER_LANG_NAME

ROUNDED_TAX_AMOUNT

LINE_EXTENSION_AMOUNT

TAX_EXCLUSIVE_AMOUNT

TOTAL_TAX_AMOUNT

CONTRACT_DOC_REF_ID

EXEMPT_REASON_CODE

GLOBAL_ATTRIBUTE_CATEGORY

GLOBAL_ATTRIBUTE1

GLOBAL_ATTRIBUTE2

GLOBAL_ATTRIBUTE3

GLOBAL_ATTRIBUTE4

GLOBAL_ATTRIBUTE5

GLOBAL_ATTRIBUTE6

GLOBAL_ATTRIBUTE7

GLOBAL_ATTRIBUTE8

GLOBAL_ATTRIBUTE9

GLOBAL_ATTRIBUTE10

GLOBAL_ATTRIBUTE11

GLOBAL_ATTRIBUTE12

GLOBAL_ATTRIBUTE13

GLOBAL_ATTRIBUTE14

GLOBAL_ATTRIBUTE15

GLOBAL_ATTRIBUTE16

GLOBAL_ATTRIBUTE17

GLOBAL_ATTRIBUTE18

GLOBAL_ATTRIBUTE19

GLOBAL_ATTRIBUTE20

GLOBAL_ATTRIBUTE21

GLOBAL_ATTRIBUTE22

GLOBAL_ATTRIBUTE23

GLOBAL_ATTRIBUTE24

GLOBAL_ATTRIBUTE25

GLOBAL_ATTRIBUTE26

GLOBAL_ATTRIBUTE27

GLOBAL_ATTRIBUTE28

GLOBAL_ATTRIBUTE29

GLOBAL_ATTRIBUTE30

GLOBAL_ATTRIBUTE_NUMBER1

GLOBAL_ATTRIBUTE_NUMBER2

GLOBAL_ATTRIBUTE_NUMBER3

GLOBAL_ATTRIBUTE_NUMBER4

GLOBAL_ATTRIBUTE_NUMBER5

GLOBAL_ATTRIBUTE_NUMBER6

GLOBAL_ATTRIBUTE_NUMBER7

GLOBAL_ATTRIBUTE_NUMBER8

GLOBAL_ATTRIBUTE_NUMBER9

GLOBAL_ATTRIBUTE_NUMBER10

GLOBAL_ATTRIBUTE_NUMBER11

GLOBAL_ATTRIBUTE_NUMBER12

GLOBAL_ATTRIBUTE_DATE1

GLOBAL_ATTRIBUTE_DATE2

GLOBAL_ATTRIBUTE_DATE3

GLOBAL_ATTRIBUTE_DATE4

GLOBAL_ATTRIBUTE_DATE5

PAYEE_IBAN_NUMBER

PAYEE_BANK_ACCOUNT_NAME

PAYEE_BANK_ACCOUNT_TYPE

PAYEE_BANK_CURRENCY_CODE

PAYEE_BANK_BRANCH_ID

PAYEE_BANK_COUNTRY

TERM_DUE_DATE

SPECIAL_INSTRUCTIONS

ATTRIBUTE_CATEGORY

ATTRIBUTE1

ATTRIBUTE2

ATTRIBUTE3

ATTRIBUTE4

ATTRIBUTE5

ATTRIBUTE6

ATTRIBUTE7

ATTRIBUTE8

ATTRIBUTE9

ATTRIBUTE10

ATTRIBUTE11

ATTRIBUTE12

ATTRIBUTE13

ATTRIBUTE14

ATTRIBUTE15

ATTRIBUTE_DATE1

ATTRIBUTE_DATE2

ATTRIBUTE_DATE3

ATTRIBUTE_DATE4

ATTRIBUTE_DATE5

ATTRIBUTE_NUMBER1

ATTRIBUTE_NUMBER2

ATTRIBUTE_NUMBER3

ATTRIBUTE_NUMBER4

ATTRIBUTE_NUMBER5

INTERFACE_HEADER_ATTRIBUTE1

INTERFACE_HEADER_ATTRIBUTE2

INTERFACE_HEADER_ATTRIBUTE3

INTERFACE_HEADER_ATTRIBUTE4

INTERFACE_HEADER_ATTRIBUTE5

INTERFACE_HEADER_ATTRIBUTE6

INTERFACE_HEADER_ATTRIBUTE7

INTERFACE_HEADER_ATTRIBUTE8

INTERFACE_HEADER_ATTRIBUTE9

INTERFACE_HEADER_ATTRIBUTE10

INTERFACE_HEADER_ATTRIBUTE11

INTERFACE_HEADER_ATTRIBUTE12

INTERFACE_HEADER_ATTRIBUTE13

INTERFACE_HEADER_ATTRIBUTE14

INTERFACE_HEADER_ATTRIBUTE15

INTERFACE_HEADER_CONTEXT

INTERFACE_HEADER_DATE1

INTERFACE_HEADER_DATE2

INTERFACE_HEADER_DATE3

INTERFACE_HEADER_DATE4

INTERFACE_HEADER_DATE5

INTERFACE_HEADER_NUMBER1

INTERFACE_HEADER_NUMBER2

INTERFACE_HEADER_NUMBER3

INTERFACE_HEADER_NUMBER4

INTERFACE_HEADER_NUMBER5

STRUCTURED_PAYMENT_REFERENCE

COMMENTS

TRX_TYPE_NAME

TRX_TYPE_DESCRIPTION

TYPE_OF_COMPANY

LE_ATTRIBUTE_CATEGORY

LE_ATTRIBUTE1

LE_ATTRIBUTE2

LE_ATTRIBUTE3

LE_ATTRIBUTE4

LE_ATTRIBUTE5

LE_ATTRIBUTE6

LE_ATTRIBUTE7

LE_ATTRIBUTE8

LE_ATTRIBUTE9

LE_ATTRIBUTE10

LE_ATTRIBUTE11

LE_ATTRIBUTE12

LE_ATTRIBUTE13

LE_ATTRIBUTE14

LE_ATTRIBUTE15

LE_ATTRIBUTE16

LE_ATTRIBUTE17

LE_ATTRIBUTE18

LE_ATTRIBUTE19

LE_ATTRIBUTE20

LE_ATTRIBUTE_NUMBER1

LE_ATTRIBUTE_NUMBER2

LE_ATTRIBUTE_NUMBER3

LE_ATTRIBUTE_NUMBER4

LE_ATTRIBUTE_NUMBER5

LE_ATTRIBUTE_DATE1

LE_ATTRIBUTE_DATE2

LE_ATTRIBUTE_DATE3

LE_ATTRIBUTE_DATE4

LE_ATTRIBUTE_DATE5

LE_INFORMATION1

LE_INFORMATION2

LE_INFORMATION3

LE_INFORMATION4

LE_INFORMATION5

LE_INFORMATION6

LE_INFORMATION7

LE_INFORMATION8

LE_INFORMATION9

LE_INFORMATION10

LE_INFORMATION11

LE_INFORMATION12

LE_INFORMATION13

LE_INFORMATION14

LE_INFORMATION15

LE_INFORMATION16

LE_INFORMATION17

LE_INFORMATION18

LE_INFORMATION19

LE_INFORMATION20

LE_INFORMATION_CONTEXT

LE_INFORMATION_DATE1

LE_INFORMATION_DATE2

LE_INFORMATION_DATE3

LE_INFORMATION_DATE4

LE_INFORMATION_DATE5

LE_INFORMATION_NUMBER1

LE_INFORMATION_NUMBER2

LE_INFORMATION_NUMBER3

LE_INFORMATION_NUMBER4

LE_INFORMATION_NUMBER5

LEGAL_ENTITY_IDENTIFIER

LEGAL_ENTITY_ADDR_LINE_FOUR

LEGAL_ENTITY_BUILDING

LEGAL_ENTITY_FLOOR_NUMBER

LEGAL_ENTITY_PROVINCE

LEGAL_ENTITY_REGION_1

ACTIVITY_CODE

SUB_ACTIVITY_CODE

LEGISLATIVE_CAT_CODE

LE_REGISTRATION_CODE

ETB_REGISTRATION_CODE

JURISDICTION_NAME

LE_REGISTERED_NAME

LE_ALTERNATE_REGISTERED_NAME

LE_REGISTRATION_NUMBER

LE_ACTIVITY_CATEGORY

BU_MANAGER_NAME

LE_CONTACT_NAME

LE_CONTACT_PHONE_AREA_CODE

LE_CONTACT_PHONE_NUMBER

LE_CONTACT_PHONE_COUNTRY_CODE

LE_CONTACT_EMAIL_ADDRESS

TAX_REP_NAME

TAX_REP_ID

TAX_REP_ADDRESS4

TAX_REP_BUILDING

TAX_REP_COUNTY

TAX_REP_FLOOR_NUMBER

TAX_REP_PROVINCE

TAX_REP_ADDRESS1

TAX_REP_ADDRESS2

TAX_REP_CITY

TAX_REP_POSTAL_CODE

TAX_REP_STATE

TAX_REP_ADDRESS3

TAX_REP_COUNTRY

TAX_REP_REGISTRATION_NUMBER

DEBIT_AUTHORIZATION_ID

BANK_ACCT_NUM_HASH1

DOC_FISC_CLASSIF_NAME

DOC_FISC_CLASSIF_CODE

DOC_FISC_CLASSIF_ID

CM_ISSUE_DATE

PAYMENT_STATUS

PRODUCT_TYPE

FINAL_INVOICE_FLAG

MASKED_CC_NUMBER

CC_HOLDER_NAME

PREPAYMENT_AMOUNT

ORDER_ROUNDING_AMOUNT

BILLING_DATE

FOB_POINT

EXEMPT_FROM_LATE_CHARGES

PRINTING_LAST_PRINTED

DEFAULT_TAX_EXEMPT_FLAG

BATCH_SOURCE_NAME

CONS_BILLING_NUMBER

GL_DATE

DEFAULT_TAX_EXEMPT_HANDLING

LE_PLACE_OF_REGISTRATION

THIRD_PARTY_REG_STATUS

THIRD_PARTY_REG_NUMBER

BANK_ACCOUNT_DESCRIPTION

BANK_LE_ADDRESS1

BANK_LE_ADDRESS2

BANK_LE_ADDRESS3

BANK_LE_ADDRESS4

BANK_LE_BUILDING

BANK_LE_FLOOR_NUMBER

BANK_LE_CITY

BANK_LE_POSTAL_CODE

BANK_LE_COUNTY

BANK_LE_STATE

BANK_LE_PROVINCE

BANK_LE_COUNTRY

BANK_LE_CONTACT_NAME

BANK_LE_CP_PHONE_AREA_CODE

BANK_LE_CP_PHONE_NUMBER

BANK_LE_CP_PHONE_COUNTRY_CODE

BANK_LE_CP_EMAIL_ADDRESS

BANK_LE_REGISTRATION_NUMBER

BANK_LE_REGISTRATION_CODE

BANK_LRU_REGISTRATION_CODE

BANK_LRU_REGISTRATION_NUMBER

BANK_LE_IDENTIFIER

ORIGINAL_TRX_CLASS

ORIGINAL_TRX_TYPE

LRU_REGISTERED_NAME

LRU_ALTERNATE_REGISTERED_NAME

LRU_REGISTRATION_NUMBER

REQUEST_ID

INTERNAL_NOTES

Query

SQL_Statement

SELECT

D.DOCUMENT_TRANSFER_ID DOCUMENT_TRANSFER_ID,

H.CUSTOMER_TRX_ID CUSTOMER_TRX_ID,

H.TERM_ID TERM_ID,

(select sum(extended_amount) from ra_customer_trx_lines_all l WHERE l.customer_trx_id = h.customer_trx_id) AMOUNT,

H.INVOICE_CURRENCY_CODE CURRENCY_CODE,

H.TRX_DATE TRX_DATE,

H.TRX_NUMBER TRX_NUMBER,

nvl(H.PURCHASE_ORDER,'N/A') PURCHASE_ORDER,

T.type DOCUMENT_TYPE,

RM.payment_type_code PAYMENT_METHOD,

org.name SUPPLIER_NAME,

null SUPPLIER_CODE,

sr.name Sales_Represent,

sr.email_address Sales_Represent_EMAIL,

to_char(d.document_transfer_id) ||':'|| to_char(h.customer_trx_id) REFERENCE_NUMBER,

H.SHIP_DATE_ACTUAL SHIP_DATE,

(select sum(nvl(extended_amount,0)) from ra_customer_trx_lines_all l where l.line_type = 'TAX' and l.customer_trx_id = h.customer_trx_id) TAX_AMOUNT,

H.ship_to_party_id SHIP_TO_CUSTOMER_ID,

H.ship_to_party_site_use_id SHIP_TO_SITE_USE_ID,

H.ship_to_party_contact_id SHIP_TO_CONTACT_ID,

H.BILL_TO_CUSTOMER_ID BILL_TO_CUSTOMER_ID,

H.BILL_TO_SITE_USE_ID BILL_TO_SITE_USE_ID,

H.BILL_TO_CONTACT_ID BILL_TO_CONTACT_ID,

H.REMIT_TO_ADDRESS_ID REMIT_TO_ADDRESS_ID,

loc.description SUPPLIER_DESCRIPTION,

hz_format_pub.format_address(loc.address_line_1, loc.address_line_2, loc.address_line_3, loc.address_line_4, loc.town_or_city, null, null, null, null, NULL, null) SUPPLIER_Primary_ADDRESS,

loc.address_line_1 SUPPLIER_ADDRESS_LINE_One,

loc.address_line_2 SUPPLIER_ADDRESS_LINE_Two,

loc.address_line_3 SUPPLIER_ADDRESS_LINE_Three,

loc.town_or_city SUPPLIER_TOWN_OR_CITY,

loc.country SUPPLIER_COUNTRY,

loc.region_1 SUPPLIER_REGION_One,

loc.region_2 SUPPLIER_REGION_Two,

loc.region_3 SUPPLIER_REGION_Three,

loc.postal_code SUPPLIER_POSTAL_CODE,

loc.telephone_number_1 SUPPLIER_PHONE_NUMBER_One,

loc.telephone_number_2 SUPPLIER_PHONE_NUMBER_Two,

loc.telephone_number_3 SUPPLIER_PHONE_NUMBER_Three,

H.Ship_VIA FREIGHT_CODE,

null FREIGHT_DESCRIPTION,

h.waybill_number WAYBILL_NUMBER,

xlePro.Name Legal_Entity_Name,

xleReg.REGISTRATION_NUMBER Legal_Entity_TaxPayer_ID,

H.TRX_CLASS Transaction_Class,

hz_format_pub.format_address(hrl.address_line_1, hrl.address_line_2, hrl.address_line_3, null, hrl.town_or_city, null, null, null, null, NULL, null) Legal_Entity_Primary_ADDRESS,

ar_xml_view_functions.header_function1 (H.customer_trx_id) Header_User_One,

ar_xml_view_functions.header_function2 (H.customer_trx_id) Header_User_Two,

ar_xml_view_functions.header_function3 (H.customer_trx_id) Header_User_Three,

ar_xml_view_functions.header_function4 (H.customer_trx_id) Header_User_Four,

ar_xml_view_functions.header_function5 (H.customer_trx_id) Header_User_Five,

ar_xml_view_functions.header_function6 (H.customer_trx_id) Header_User_Six,

ar_xml_view_functions.header_function7 (H.customer_trx_id) Header_User_Seven,

ar_xml_view_functions.header_function8 (H.customer_trx_id) Header_User_Eight,

ar_xml_view_functions.header_function9 (H.customer_trx_id) Header_User_Nine ,

ar_xml_view_functions.header_function10 (H.customer_trx_id) Header_User_Ten,

ar_xml_view_functions.header_function11 (H.customer_trx_id) Header_User_Eleven,

ar_xml_view_functions.header_function12 (H.customer_trx_id) Header_User_Twelve,

ar_xml_view_functions.header_function13 (H.customer_trx_id) Header_User_Thirteen,

ar_xml_view_functions.header_function14 (H.customer_trx_id) Header_User_Fourteen,

ar_xml_view_functions.header_function15 (H.customer_trx_id) Header_User_Fifteen,

ar_xml_view_functions.header_function16 (H.customer_trx_id) Header_User_Sixteen,

ar_xml_view_functions.header_function17 (H.customer_trx_id) Header_User_Seventeen,

ar_xml_view_functions.header_function18 (H.customer_trx_id) Header_User_Eighteen,

ar_xml_view_functions.header_function19 (H.customer_trx_id) Header_User_Nineteen,

ar_xml_view_functions.header_function20 (H.customer_trx_id) Header_User_Twenty,

ar_xml_view_functions.header_function21 (H.customer_trx_id) Header_User_Twenty_One,

ar_xml_view_functions.header_function22 (H.customer_trx_id) Header_User_Twenty_Two,

ar_xml_view_functions.header_function23 (H.customer_trx_id) Header_User_Twenty_Three,

ar_xml_view_functions.header_function24 (H.customer_trx_id) Header_User_Twenty_Four,

ar_xml_view_functions.header_function25 (H.customer_trx_id) Header_User_Twenty_Five,

ar_xml_view_functions.header_function26 (H.customer_trx_id) Header_User_Twenty_Six,

ar_xml_view_functions.header_function27 (H.customer_trx_id) Header_User_Twenty_Seven,

ar_xml_view_functions.header_function28 (H.customer_trx_id) Header_User_Twenty_Eight,

ar_xml_view_functions.header_function29 (H.customer_trx_id) Header_User_Twenty_Nine,

ar_xml_view_functions.header_function30 (H.customer_trx_id) Header_User_Thirty,

H.DOC_SEQUENCE_VALUE DOCUMENT_SEQUENCE_NUMBER,

(select count(1) from RA_CUSTOMER_TRX_LINES_ALL where LINE_TYPE= 'LINE' and CUSTOMER_TRX_ID=H.customer_trx_id) LINE_COUNT,

H.CT_REFERENCE CT_REFERENCE,

DECODE(h.previous_customer_trx_id,NULL,NULL,cm.trx_number) ORIGINAL_TRX_NUMBER,

TO_CHAR(CM.TRX_DATE,'YYYY-MM-DD') ORIG_TRX_DATE,

DECODE(h.FIRST_PTY_REG_ID,NULL,NULL,zxreg.REGISTRATION_NUMBER) SUPPLIER_REG_NUMBER,

DECODE(h.FIRST_PTY_REG_ID,NULL,NULL,zxreg.TAX_REGIME_CODE) TAX_REGIME_NAME,

DECODE(h.FIRST_PTY_REG_ID,NULL,NULL,zxreg.TAX) TAX_TYPE_CODE,

hrl.POSTAL_CODE LEGAL_ENTITY_POSTBOX,

hrl.ADDRESS_LINE_1 LEGAL_ENTITY_ADDR_LINE_ONE,

hrl.ADDRESS_LINE_2 LEGAL_ENTITY_ADDR_LINE_TWO,

hrl.ADDRESS_LINE_3 LEGAL_ENTITY_ADDR_LINE_THREE,

hrl.TOWN_OR_CITY LEGAL_ENTITY_TOWN_OR_CITY,

hrl.REGION_2 LEGAL_ENTITY_COUNTY,

hrl.COUNTRY LEGAL_ENTITY_COUNTRY_CODE,

DECODE(h.receipt_method_id,NULL,NULL,rm.NAME) RECEIPT_METHOD_NAME,

DECODE(h.receipt_method_id,NULL,NULL,rm.PAYMENT_CHANNEL_CODE) PAYMENT_CHANNEL_CODE,

LOC.OFFICIAL_LANGUAGE_CODE SUPPLIER_LANG_NAME,

(select SUM(ZX.UNROUNDED_TAX_AMT) - (SUM (NVL (l.extended_amount ,0))) from ra_customer_trx_lines_all l, ZX_LINES ZX where l.line_type = 'TAX' AND l.customer_trx_id = h.customer_trx_id AND l.tax_line_id = zx.tax_line_id) ROUNDED_TAX_AMOUNT,

(SELECT (SUM (NVL (extended_amount ,0))) FROM ra_customer_trx_lines_all l WHERE l.line_type = 'LINE' AND l.customer_trx_id = h.customer_trx_id) LINE_EXTENSION_AMOUNT,

(SELECT (SUM (NVL (extended_amount ,0))) FROM ra_customer_trx_lines_all l WHERE l.line_type !='TAX' AND l.customer_trx_id = h.customer_trx_id) TAX_EXCLUSIVE_AMOUNT,

(SELECT (SUM (NVL (extended_amount ,0))) FROM ra_customer_trx_lines_all l WHERE l.line_type = 'TAX' AND l.customer_trx_id = h.customer_trx_id) TOTAL_TAX_AMOUNT,

NVL((select sales_order from ra_customer_trx_lines_all where customer_trx_id = h.customer_trx_id and line_type='LINE' and rownum=1), h.ct_reference) CONTRACT_DOC_REF_ID,

(select EXEMPT_REASON_CODE from ZX_EXEMPTIONS where party_tax_profile_id=zxreg.party_tax_profile_id and rownum=1) EXEMPT_REASON_CODE,

H.GLOBAL_ATTRIBUTE_CATEGORY,

H.GLOBAL_ATTRIBUTE1,

H.GLOBAL_ATTRIBUTE2,

H.GLOBAL_ATTRIBUTE3,

H.GLOBAL_ATTRIBUTE4,

H.GLOBAL_ATTRIBUTE5,

H.GLOBAL_ATTRIBUTE6,

H.GLOBAL_ATTRIBUTE7,

H.GLOBAL_ATTRIBUTE8,

H.GLOBAL_ATTRIBUTE9,

H.GLOBAL_ATTRIBUTE10,

H.GLOBAL_ATTRIBUTE11,

H.GLOBAL_ATTRIBUTE12,

H.GLOBAL_ATTRIBUTE13,

H.GLOBAL_ATTRIBUTE14,

H.GLOBAL_ATTRIBUTE15,

H.GLOBAL_ATTRIBUTE16,

H.GLOBAL_ATTRIBUTE17,

H.GLOBAL_ATTRIBUTE18,

H.GLOBAL_ATTRIBUTE19,

H.GLOBAL_ATTRIBUTE20,

H.GLOBAL_ATTRIBUTE21,

H.GLOBAL_ATTRIBUTE22,

H.GLOBAL_ATTRIBUTE23,

H.GLOBAL_ATTRIBUTE24,

H.GLOBAL_ATTRIBUTE25,

H.GLOBAL_ATTRIBUTE26,

H.GLOBAL_ATTRIBUTE27,

H.GLOBAL_ATTRIBUTE28,

H.GLOBAL_ATTRIBUTE29,

H.GLOBAL_ATTRIBUTE30,

H.GLOBAL_ATTRIBUTE_NUMBER1,

H.GLOBAL_ATTRIBUTE_NUMBER2,

H.GLOBAL_ATTRIBUTE_NUMBER3,

H.GLOBAL_ATTRIBUTE_NUMBER4,

H.GLOBAL_ATTRIBUTE_NUMBER5,

H.GLOBAL_ATTRIBUTE_NUMBER6,

H.GLOBAL_ATTRIBUTE_NUMBER7,

H.GLOBAL_ATTRIBUTE_NUMBER8,

H.GLOBAL_ATTRIBUTE_NUMBER9,

H.GLOBAL_ATTRIBUTE_NUMBER10,

H.GLOBAL_ATTRIBUTE_NUMBER11,

H.GLOBAL_ATTRIBUTE_NUMBER12,

H.GLOBAL_ATTRIBUTE_DATE1,

H.GLOBAL_ATTRIBUTE_DATE2,

H.GLOBAL_ATTRIBUTE_DATE3,

H.GLOBAL_ATTRIBUTE_DATE4,

H.GLOBAL_ATTRIBUTE_DATE5,

hext.PAYEE_IBAN_NUMBER,

hext.PAYEE_BANK_ACCOUNT_NAME,

hext.PAYEE_BANK_ACCOUNT_TYPE,

hext.PAYEE_BANK_CURRENCY_CODE,

hext.EFT_SWIFT_CODE PAYEE_BANK_BRANCH_ID,

hext.PAYEE_BANK_COUNTRY,

H.TERM_DUE_DATE,

H.SPECIAL_INSTRUCTIONS,

H.ATTRIBUTE_CATEGORY,

H.ATTRIBUTE1,

H.ATTRIBUTE2,

H.ATTRIBUTE3,

H.ATTRIBUTE4,

H.ATTRIBUTE5,

H.ATTRIBUTE6,

H.ATTRIBUTE7,

H.ATTRIBUTE8,

H.ATTRIBUTE9,

H.ATTRIBUTE10,

H.ATTRIBUTE11,

H.ATTRIBUTE12,

H.ATTRIBUTE13,

H.ATTRIBUTE14,

H.ATTRIBUTE15,

H.ATTRIBUTE_DATE1,

H.ATTRIBUTE_DATE2,

H.ATTRIBUTE_DATE3,

H.ATTRIBUTE_DATE4,

H.ATTRIBUTE_DATE5,

H.ATTRIBUTE_NUMBER1,

H.ATTRIBUTE_NUMBER2,

H.ATTRIBUTE_NUMBER3,

H.ATTRIBUTE_NUMBER4,

H.ATTRIBUTE_NUMBER5,

H.INTERFACE_HEADER_ATTRIBUTE1,

H.INTERFACE_HEADER_ATTRIBUTE2,

H.INTERFACE_HEADER_ATTRIBUTE3,

H.INTERFACE_HEADER_ATTRIBUTE4,

H.INTERFACE_HEADER_ATTRIBUTE5,

H.INTERFACE_HEADER_ATTRIBUTE6,

H.INTERFACE_HEADER_ATTRIBUTE7,

H.INTERFACE_HEADER_ATTRIBUTE8,

H.INTERFACE_HEADER_ATTRIBUTE9,

H.INTERFACE_HEADER_ATTRIBUTE10,

H.INTERFACE_HEADER_ATTRIBUTE11,

H.INTERFACE_HEADER_ATTRIBUTE12,

H.INTERFACE_HEADER_ATTRIBUTE13,

H.INTERFACE_HEADER_ATTRIBUTE14,

H.INTERFACE_HEADER_ATTRIBUTE15,

H.INTERFACE_HEADER_CONTEXT,

H.INTERFACE_HEADER_DATE1,

H.INTERFACE_HEADER_DATE2,

H.INTERFACE_HEADER_DATE3,

H.INTERFACE_HEADER_DATE4,

H.INTERFACE_HEADER_DATE5,

H.INTERFACE_HEADER_NUMBER1,

H.INTERFACE_HEADER_NUMBER2,

H.INTERFACE_HEADER_NUMBER3,

H.INTERFACE_HEADER_NUMBER4,

H.INTERFACE_HEADER_NUMBER5,

H.STRUCTURED_PAYMENT_REFERENCE,

H.COMMENTS,

T.NAME TRX_TYPE_NAME,

T.DESCRIPTION TRX_TYPE_DESCRIPTION,

xlePro.TYPE_OF_COMPANY,

xlePro.ATTRIBUTE_CATEGORY LE_ATTRIBUTE_CATEGORY,

xlePro.ATTRIBUTE1 LE_ATTRIBUTE1,

xlePro.ATTRIBUTE2 LE_ATTRIBUTE2,

xlePro.ATTRIBUTE3 LE_ATTRIBUTE3,

xlePro.ATTRIBUTE4 LE_ATTRIBUTE4,

xlePro.ATTRIBUTE5 LE_ATTRIBUTE5,

xlePro.ATTRIBUTE6 LE_ATTRIBUTE6,

xlePro.ATTRIBUTE7 LE_ATTRIBUTE7,

xlePro.ATTRIBUTE8 LE_ATTRIBUTE8,

xlePro.ATTRIBUTE9 LE_ATTRIBUTE9,

xlePro.ATTRIBUTE10 LE_ATTRIBUTE10,

xlePro.ATTRIBUTE11 LE_ATTRIBUTE11,

xlePro.ATTRIBUTE12 LE_ATTRIBUTE12,

xlePro.ATTRIBUTE13 LE_ATTRIBUTE13,

xlePro.ATTRIBUTE14 LE_ATTRIBUTE14,

xlePro.ATTRIBUTE15 LE_ATTRIBUTE15,

xlePro.ATTRIBUTE16 LE_ATTRIBUTE16,

xlePro.ATTRIBUTE17 LE_ATTRIBUTE17,

xlePro.ATTRIBUTE18 LE_ATTRIBUTE18,

xlePro.ATTRIBUTE19 LE_ATTRIBUTE19,

xlePro.ATTRIBUTE20 LE_ATTRIBUTE20,

xlePro.ATTRIBUTE_NUMBER1 LE_ATTRIBUTE_NUMBER1,

xlePro.ATTRIBUTE_NUMBER2 LE_ATTRIBUTE_NUMBER2,

xlePro.ATTRIBUTE_NUMBER3 LE_ATTRIBUTE_NUMBER3,

xlePro.ATTRIBUTE_NUMBER4 LE_ATTRIBUTE_NUMBER4,

xlePro.ATTRIBUTE_NUMBER5 LE_ATTRIBUTE_NUMBER5,

xlePro.ATTRIBUTE_DATE1 LE_ATTRIBUTE_DATE1,

xlePro.ATTRIBUTE_DATE2 LE_ATTRIBUTE_DATE2,

xlePro.ATTRIBUTE_DATE3 LE_ATTRIBUTE_DATE3,

xlePro.ATTRIBUTE_DATE4 LE_ATTRIBUTE_DATE4,

xlePro.ATTRIBUTE_DATE5 LE_ATTRIBUTE_DATE5,

xlePro.LE_INFORMATION1,

xlePro.LE_INFORMATION2,

xlePro.LE_INFORMATION3,

xlePro.LE_INFORMATION4,

xlePro.LE_INFORMATION5,

xlePro.LE_INFORMATION6,

xlePro.LE_INFORMATION7,

xlePro.LE_INFORMATION8,

xlePro.LE_INFORMATION9,

xlePro.LE_INFORMATION10,

xlePro.LE_INFORMATION11,

xlePro.LE_INFORMATION12,

xlePro.LE_INFORMATION13,

xlePro.LE_INFORMATION14,

xlePro.LE_INFORMATION15,

xlePro.LE_INFORMATION16,

xlePro.LE_INFORMATION17,

xlePro.LE_INFORMATION18,

xlePro.LE_INFORMATION19,

xlePro.LE_INFORMATION20,

xlePro.LE_INFORMATION_CONTEXT,

xlePro.LE_INFORMATION_DATE1,

xlePro.LE_INFORMATION_DATE2,

xlePro.LE_INFORMATION_DATE3,

xlePro.LE_INFORMATION_DATE4,

xlePro.LE_INFORMATION_DATE5,

xlePro.LE_INFORMATION_NUMBER1,

xlePro.LE_INFORMATION_NUMBER2,

xlePro.LE_INFORMATION_NUMBER3,

xlePro.LE_INFORMATION_NUMBER4,

xlePro.LE_INFORMATION_NUMBER5,

xlePro.LEGAL_ENTITY_IDENTIFIER,

hrl.ADDRESS_LINE_4 LEGAL_ENTITY_ADDR_LINE_FOUR,

hrl.BUILDING LEGAL_ENTITY_BUILDING,

hrl.FLOOR_NUMBER LEGAL_ENTITY_FLOOR_NUMBER,

hext.LEGAL_ENTITY_PROVINCE,

hrl.REGION_1 LEGAL_ENTITY_REGION_1,

xlePro.ACTIVITY_CODE,

xlePro.SUB_ACTIVITY_CODE,

hext.LEGISLATIVE_CAT_CODE,

hext.LE_REGISTRATION_CODE,

hext.ETB_REGISTRATION_CODE,

hext.JURISDICTION_NAME,

hext.LE_REGISTERED_NAME,

hext.LE_ALTERNATE_REGISTERED_NAME,

hext.LE_REGISTRATION_NUMBER,

hext.LE_ACTIVITY_CATEGORY,

hext.BU_MANAGER_NAME,

hext.LE_CONTACT_NAME,

hext.LE_CONTACT_PHONE_AREA_CODE,

hext.LE_CONTACT_PHONE_NUMBER,

hext.LE_CONTACT_PHONE_COUNTRY_CODE,

hext.LE_CONTACT_EMAIL_ADDRESS,

hext.TAXREP_NAME TAX_REP_NAME,

hext.TAXREP_ID TAX_REP_ID,

hext.TAXREP_ADDRESS4 TAX_REP_ADDRESS4,

hext.TAXREP_BUILDING TAX_REP_BUILDING,

hext.TAXREP_COUNTY TAX_REP_COUNTY,

hext.TAXREP_FLOOR_NUMBER TAX_REP_FLOOR_NUMBER,

hext.TAXREP_PROVINCE TAX_REP_PROVINCE,

hext.TAXREP_ADDRESS1 TAX_REP_ADDRESS1,

hext.TAXREP_ADDRESS2 TAX_REP_ADDRESS2,

hext.TAXREP_CITY TAX_REP_CITY,

hext.TAXREP_POSTAL_CODE TAX_REP_POSTAL_CODE,

hext.TAXREP_STATE TAX_REP_STATE,

hext.TAXREP_ADDRESS3 TAX_REP_ADDRESS3,

hext.TAXREP_COUNTRY TAX_REP_COUNTRY,

hext.TAXREP_REGISTRATION_NUM TAX_REP_REGISTRATION_NUMBER,

hext.DEBIT_AUTHORIZATION_ID,

hext.BANK_ACCT_NUM_HASH1,

hext.DOC_FISC_CLASSIF_NAME,

hext.DOC_FISC_CLASSIF_CODE,

hext.DOC_FISC_CLASSIF_ID,

hext.CREDIT_MEMO_ISSUE_DATE CM_ISSUE_DATE,

hext.PAYMENT_STATUS,

hext.PRODUCT_TYPE,

hext.FINAL_INVOICE_FLAG,

hext.MASKED_CC_NUMBER,

hext.CC_HOLDER_NAME,

hext.PREPAYMENT_AMOUNT,

hext.ORDER_ROUNDING_AMOUNT,

h.BILLING_DATE,

h.FOB_POINT,

decode(nvl(h.FINANCE_CHARGES,'Y'),'N','Y','N') EXEMPT_FROM_LATE_CHARGES,

h.PRINTING_LAST_PRINTED,

h.DEFAULT_TAX_EXEMPT_FLAG,

hext.BATCH_SOURCE_NAME,

hext.CONS_BILLING_NUMBER,

hext.GL_DATE,

hext.DEFAULT_TAX_EXEMPT_HANDLING,

hext.LE_PLACE_OF_REGISTRATION,

hext.THIRD_PARTY_REG_STATUS,

hext.THIRD_PARTY_REG_NUMBER,

hext.BANK_ACCOUNT_DESCRIPTION,

hext.BANK_LE_ADDRESS1,

hext.BANK_LE_ADDRESS2,

hext.BANK_LE_ADDRESS3,

hext.BANK_LE_ADDRESS4,

hext.BANK_LE_BUILDING,

hext.BANK_LE_FLOOR_NUMBER,

hext.BANK_LE_CITY,

hext.BANK_LE_POSTAL_CODE,

hext.BANK_LE_COUNTY,

hext.BANK_LE_STATE,

hext.BANK_LE_PROVINCE,

hext.BANK_LE_COUNTRY,

hext.BANK_LE_CONTACT_NAME,

hext.BANK_LE_CP_PHONE_AREA_CODE,

hext.BANK_LE_CP_PHONE_NUMBER,

hext.BANK_LE_CP_PHONE_COUNTRY_CODE,

hext.BANK_LE_CP_EMAIL_ADDRESS,

hext.BANK_LE_REGISTRATION_NUMBER,

hext.BANK_LE_REGISTRATION_CODE,

hext.BANK_LRU_REGISTRATION_CODE,

hext.BANK_LRU_REGISTRATION_NUMBER,

hext.BANK_LE_IDENTIFIER,

hext.ORIGINAL_TRX_CLASS,

hext.ORIGINAL_TRX_TYPE,

hext.LRU_REGISTERED_NAME,

hext.LRU_ALTERNATE_REGISTERED_NAME,

hext.LRU_REGISTRATION_NUMBER,

hext.REQUEST_ID,

h.INTERNAL_NOTES

from

AR_DOCUMENT_TRANSFERS D,

RA_CUSTOMER_TRX_ALL H,

RA_CUST_TRX_TYPES_ALL T,

AR_RECEIPT_METHODS RM,

HR_ALL_ORGANIZATION_UNITS ORG,

HR_LOCATIONS LOC,

XLE_ENTITY_PROFILES xlePro,

XLE_REGISTRATIONS xleReg,

XLE_LOCATIONS_V hrl,

(

SELECT JRS.RESOURCE_SALESREP_ID, HZP.PARTY_ID, HZP.PARTY_NAME NAME , HZP.EMAIL_ADDRESS

FROM JTF_RS_SALESREPS JRS, HZ_PARTIES HZP

WHERE JRS.RESOURCE_ID = HZP.PARTY_ID

UNION ALL

SELECT -1, -1, lk.meaning NAME, NULL

FROM ar_lookups lk

WHERE lk.lookup_type = 'AR_SALES_CREDIT' AND lk.lookup_code = 'NO_SALES_CREDIT'

) SR,

RA_CUSTOMER_TRX_ALL cm,

ZX_REGISTRATIONS zxreg,

ar_doc_transfer_header_ext hext

where d.source_id = h.customer_trx_id

and h.CUST_TRX_TYPE_SEQ_ID = t.CUST_TRX_TYPE_SEQ_ID

and h.receipt_method_id = rm.receipt_method_id(+)

and org.organization_id = h.org_id

and org.location_id = loc.location_id(+)

AND xlePro.LEGAL_ENTITY_ID = h.LEGAL_ENTITY_ID

AND xleReg.SOURCE_TABLE = 'XLE_ENTITY_PROFILES'

AND xleReg.SOURCE_ID = h.LEGAL_ENTITY_ID

AND h.PRIMARY_RESOURCE_SALESREP_ID = SR.RESOURCE_SALESREP_ID(+)

AND xleReg.location_id = hrl.location_id

AND xleReg.identifying_flag = 'Y'

AND cm.customer_trx_id(+) =h.previous_customer_trx_id

AND zxreg.REGISTRATION_ID(+) =h.FIRST_PTY_REG_ID

AND hext.customer_trx_id(+) = h.customer_trx_id