VIEW: APPS.IBY_XML_FNDCPT_DOC_HEADER_V

Source

View Text - Preformatted

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",inv_header.document_amount), XMLElement("Currency", XMLElement("Code",inv_header.document_currency_code) ) ), XMLElement("PaymentAmount", XMLElement("Value",inv_header.settlement_amount), XMLElement("Currency", XMLElement("Code",inv_header.document_amount) ) ), XMLElement("Charge", XMLElement("Amount", XMLElement("Value",inv_header.freight_amount), XMLElement("Currency", XMLElement("Code",inv_header.document_currency_code) ) ), XMLForest('FREIGHT' AS "ChargeType") ), XMLElement("Discount", XMLElement("Amount", XMLElement("Value",inv_header.discount_amount), XMLElement("Currency", XMLElement("Code",inv_header.document_currency_code) ) ), XMLForest(null AS "DiscountType") ), XMLElement("Tax", XMLElement("Amount", XMLElement("Value",inv_header.local_tax_amount+inv_header.national_tax_amount), 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",inv_header.vat_tax_amount), XMLElement("Currency", XMLElement("Code",inv_header.document_currency_code) ) ), XMLElement("RatePercent",null), XMLForest('VAT' AS "TaxType"), XMLForest(null AS "TaxJurisdiction") ), XMLElement("ShipmentOrigin", XMLElement("AddressLine1",null), XMLForest(null AS "AddressLine2"), XMLForest(null AS "AddressLine3"), XMLElement("City",null), XMLForest(null AS "County"), XMLElement("State",null), XMLElement("Country",null), XMLElement("PostalCode",null) ), 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') 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), inv_header.order_tangible_id, txn.transactionid, txn.initiator_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 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(+))

View Text - HTML Formatted

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"
, INV_HEADER.DOCUMENT_AMOUNT)
, XMLELEMENT("CURRENCY"
, XMLELEMENT("CODE"
, INV_HEADER.DOCUMENT_CURRENCY_CODE) ) )
, XMLELEMENT("PAYMENTAMOUNT"
, XMLELEMENT("VALUE"
, INV_HEADER.SETTLEMENT_AMOUNT)
, XMLELEMENT("CURRENCY"
, XMLELEMENT("CODE"
, INV_HEADER.DOCUMENT_AMOUNT) ) )
, XMLELEMENT("CHARGE"
, XMLELEMENT("AMOUNT"
, XMLELEMENT("VALUE"
, INV_HEADER.FREIGHT_AMOUNT)
, XMLELEMENT("CURRENCY"
, XMLELEMENT("CODE"
, INV_HEADER.DOCUMENT_CURRENCY_CODE) ) )
, XMLFOREST('FREIGHT' AS "CHARGETYPE") )
, XMLELEMENT("DISCOUNT"
, XMLELEMENT("AMOUNT"
, XMLELEMENT("VALUE"
, INV_HEADER.DISCOUNT_AMOUNT)
, XMLELEMENT("CURRENCY"
, XMLELEMENT("CODE"
, INV_HEADER.DOCUMENT_CURRENCY_CODE) ) )
, XMLFOREST(NULL AS "DISCOUNTTYPE") )
, XMLELEMENT("TAX"
, XMLELEMENT("AMOUNT"
, XMLELEMENT("VALUE"
, INV_HEADER.LOCAL_TAX_AMOUNT+INV_HEADER.NATIONAL_TAX_AMOUNT)
, 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"
, INV_HEADER.VAT_TAX_AMOUNT)
, XMLELEMENT("CURRENCY"
, XMLELEMENT("CODE"
, INV_HEADER.DOCUMENT_CURRENCY_CODE) ) )
, XMLELEMENT("RATEPERCENT"
, NULL)
, XMLFOREST('VAT' AS "TAXTYPE")
, XMLFOREST(NULL AS "TAXJURISDICTION") )
, XMLELEMENT("SHIPMENTORIGIN"
, XMLELEMENT("ADDRESSLINE1"
, NULL)
, XMLFOREST(NULL AS "ADDRESSLINE2")
, XMLFOREST(NULL AS "ADDRESSLINE3")
, XMLELEMENT("CITY"
, NULL)
, XMLFOREST(NULL AS "COUNTY")
, XMLELEMENT("STATE"
, NULL)
, XMLELEMENT("COUNTRY"
, NULL)
, XMLELEMENT("POSTALCODE"
, NULL) )
, 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') 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)
, INV_HEADER.ORDER_TANGIBLE_ID
, TXN.TRANSACTIONID
, TXN.INITIATOR_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
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(+))