1. Tables and Views for Financials
  2. IBY_XML_FNDCPT_DOC_HEADER_V

IBY_XML_FNDCPT_DOC_HEADER_V

Details

  • Schema: FUSION

  • Object owner: IBY

  • Object type: VIEW

Columns

Name

DOCUMENT_HEADER

TANGIBLEID

TRXN_EXTENSION_ID

Query

SQL_Statement

SELECT XMLElement("DocumentReceivable",

XMLElement("DocumentID",inv_header.CALLING_APP_DOC_REF_NUMBER),

XMLElement("DocumentStatus",

XMLElement("Code",NULL),

XMLElement("Meaning",NULL)

),

XMLElement("DocumentDate",TO_CHAR(inv_header.document_date,'YYYY-MM-DD"T"HH24:MI:SS')),

XMLElement("DocumentCreationDate",TO_CHAR(inv_header.creation_date,'YYYY-MM-DD"T"HH24:MI:SS')),

XMLForest(NULL AS "PaymentDueDate"),

XMLElement("DocumentType",

XMLElement("Code",inv_header.document_type),

XMLElement("Meaning",NULL)

),

XMLElement("DocumentDescription",inv_header.document_description),

XMLElement("TotalDocumentAmount",

XMLElement("Value", NVL(inv_header.document_amount, 0)),

XMLElement("Currency",

XMLElement("Code",inv_header.document_currency_code)

)

),

XMLElement("PaymentAmount",

XMLElement("Value", NVL(inv_header.settlement_amount, 0)),

XMLElement("Currency",

XMLElement("Code",inv_header.document_currency_code)

)

),

XMLElement("Charge",

XMLElement("Amount",

XMLElement("Value",NVL(freight.freight_amount,0)),

XMLElement("Currency",

XMLElement("Code",inv_header.document_currency_code)

)

),

XMLForest('FREIGHT' AS "ChargeType")

),

XMLElement("Discount",

XMLElement("Amount",

XMLElement("Value",NVL(inv_header.discount_amount,0)),

XMLElement("Currency",

XMLElement("Code",inv_header.document_currency_code)

)

),

XMLForest(NULL AS "DiscountType")

),

XMLElement("Tax",

XMLElement("Amount",

XMLElement("Value", NVL((inv_header.local_tax_amount+inv_header.national_tax_amount), 0)),

XMLElement("Currency",

XMLElement("Code",inv_header.document_currency_code)

)

),

XMLElement("RatePercent",NULL),

XMLForest('SALESTAX' AS "TaxType"),

XMLForest(NULL AS "TaxJurisdiction")

),

XMLElement("Tax",

XMLElement("Amount",

XMLElement("Value", NVL(inv_header.vat_tax_amount, 0)),

XMLElement("Currency",

XMLElement("Code",inv_header.document_currency_code)

)

),

XMLElement("RatePercent",NULL),

XMLForest('VAT' AS "TaxType"),

XMLForest(NULL AS "TaxJurisdiction")

),

XMLElement("ShipmentOrigin",

XMLElement("AddressLine1",from_loc.address1),

XMLForest(from_loc.address2 AS "AddressLine2"),

XMLForest(from_loc.address2 AS "AddressLine3"),

XMLElement("City",from_loc.city),

XMLForest(from_loc.county AS "County"),

XMLElement("State",from_loc.state),

XMLElement("Country",from_loc.country),

XMLElement("PostalCode",from_loc.postal_code)

),

XMLElement("ShipmentDestination",

XMLElement("AddressLine1",to_loc.address1),

XMLForest(to_loc.address2 AS "AddressLine2"),

XMLForest(to_loc.address3 AS "AddressLine3"),

XMLElement("City",to_loc.city),

XMLForest(to_loc.county AS "County"),

XMLElement("State",to_loc.state),

XMLElement("Country", to_loc.country),

XMLElement("PostalCode",to_loc.postal_code)

),

CASE WHEN (core.card_data_level = '3') OR (txn.instrtype = 'BANKACCOUNT') THEN

(SELECT xmlagg(document_line ORDER BY line_number ASC)

FROM iby_xml_fndcpt_doc_line_v inv_line

WHERE inv_header.doc_unique_ref = inv_line.doc_unique_ref(+)

)

ELSE

NULL

END

,

XMLElement("BillToCustomer",

XMLElement("PartyInternalID", hzp.party_id),

XMLElement("PartyNumber", hzp.party_number),

XMLElement("Name", hzp.party_name),

XMLElement("PartyType", XMLElement("Code", NVL(hzp.party_type, 'PERSON')),

XMLElement("Meaning", party_type_lookup.meaning) ),

XMLElement("AlternateName",DECODE(hzp.party_type, 'ORGANIZATION', party_name.party_name, 'PERSON', party_name.person_first_name

|| ' '

|| party_name.person_last_name)), XMLElement("CustomerAccountNumber", hca. account_number),

CASE

WHEN inv_header.bill_to_address_id IS NULL

THEN NULL

ELSE XMLElement("Address", XMLConcat( XMLElement("AddressInternalID", hz_loc.location_id), XMLElement("AddressLine1", hz_loc.address1), XMLElement("AddressLine2", hz_loc.address2), XMLElement("AddressLine3", hz_loc.address3), XMLElement("City", hz_loc.city), XMLElement("County", hz_loc.county), XMLElement("State", hz_loc.state), XMLElement("Country", hz_loc.country), XMLElement("CountryName", terrt.territory_short_name), XMLElement("ISO3DigitCountry", terrb.iso_territory_code), XMLElement("PostalCode", hz_loc.postal_code), XMLElement("AlternateAddressName", NULL) ) )

END,

CASE

WHEN hzp.party_number IS NULL

THEN NULL

ELSE XMLElement("FirstPartyReference", hzp.party_number)

END, XMLElement("TaxRegistrationNumber", iby_fd_extract_gen_pvt.Get_FP_TaxRegistration(xle_firstparty.legal_entity_id)), XMLElement("LegalRegistrationNumber", xle_firstparty.registration_number)

),

XMLElement("DocumentReceivableLegalEntity", payee.xml_payee, XMLElement("DocumentReceivableTaxRegNum", inv_header.TAX_REGISTRATION_NUMBER))

) DOCUMENT_HEADER,

inv_header.order_tangible_id TANGIBLEID,

txn.initiator_extension_id TRXN_EXTENSION_ID

FROM ar_docs_receivables_v inv_header,

hz_locations from_loc,

hz_locations to_loc,

iby_trxn_summaries_all txn,

iby_trxn_core core,

hz_parties hzp,

hz_cust_accounts hca,

HZ_ADDTNL_PARTY_NAMES party_name,

hz_locations hz_loc,

hr_all_organization_units_tl hou,

fnd_lookup_values party_type_lookup,

fnd_territories_b terrb,

fnd_territories_tl terrt,

xle_firstparty_information_v xle_firstparty,

IBY_XML_FNDCPT_PAYEE_1_0_V payee,

(select doc_unique_ref, sum(extended_amount) freight_amount from ar_document_lines_v lines where lines.line_type = 'FREIGHT' group by doc_unique_ref) freight

WHERE txn.initiator_extension_id = inv_header.order_ext_id

AND txn.trxnmid = core.trxnmid(+)

AND txn.trxntypeid IN (8,9,100)

AND inv_header.ship_from_address_id=from_loc.location_id(+)

AND inv_header.ship_to_address_id =to_loc.location_id(+)

AND inv_header.bill_to_customer_id = hca.cust_account_id(+)

AND inv_header.bill_to_address_id = hz_loc.location_id(+)

AND inv_header.legal_entity_id = hou.organization_id(+)

AND HCA.PARTY_ID = hzp.party_id(+)

AND userenv('LANG') = hou.LANGUAGE(+)

AND hzp.party_id = party_name.party_id(+)

AND party_name.party_name_type(+) = 'PHONETIC'

AND (hz_loc.country = terrb.territory_code(+))

AND (terrb.territory_code = terrt.territory_code(+))

AND (terrt.language(+) = USERENV('LANG'))

AND hzp.party_type = party_type_lookup.lookup_code(+)

AND party_type_lookup.lookup_type(+) = 'PARTY_TYPE'

AND party_type_lookup.LANGUAGE(+) = userenv('LANG')

AND party_type_lookup.VIEW_APPLICATION_ID(+) = 222

AND xle_firstparty.party_id(+) = hzp.party_id

AND payee.LEGAL_ENTITY_ID(+) = inv_header.LEGAL_ENTITY_ID

AND inv_header.doc_unique_ref = freight.doc_unique_ref(+)