View: IBY_XML_FNDCPT_DOC_HEADER_V

Product:	IBY - Payments
Description:
Implementation/DBA Data:	APPS.IBY_XML_FNDCPT_DOC_HEADER_V

View Text

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 LINES.DOCUMENT_LINES ELSE NULL END)
, INV_HEADER.ORDER_TANGIBLE_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
, IBY_XML_FNDCPT_DOC_LINES_V LINES
WHERE TXN.TANGIBLEID=INV_HEADER.ORDER_TANGIBLE_ID
AND (TXN.TRXNMID = CORE.TRXNMID(+))
AND (TXN.TRXNTYPEID=8)
AND (INV_HEADER.DOC_UNIQUE_REF= LINES.DOC_UNIQUE_REF(+))
AND (INV_HEADER.SHIP_FROM_ADDRESS_ID=FROM_LOC.LOCATION_ID(+))
AND (INV_HEADER.SHIP_TO_ADDRESS_ID=TO_LOC.LOCATION_ID(+))

Columns