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.document_discount_earned), 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,0) + nvl(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", 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), null, 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.DOCUMENT_DISCOUNT_EARNED)
, 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
, 0) + NVL(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"
, 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)
, NULL
, 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(+))