refernce :
select XMLELEMENT("ORDERS",
XMLELEMENT("ORDER",
XMLFOREST('RETURN' AS "ORD_CAT_COD",
18184 AS "SOLD_TO_ORG",
TO_CHAR(SYSDATE, 'YYYY-MM-DD') AS "ORD_DATE",
'R'||OH.CUST_PO_NUMBER AS "PURCHASE_ORDER",
'RET-'||OH.ORIG_SYS_DOCUMENT_REF AS "SOURCE_DOC_REF",
OH.ATTRIBUTE1 AS "H_ATTR1",
OH.ORIG_SYS_DOCUMENT_REF AS "ORIG_SOURCE_DOC_REF",
(SELECT XMLAGG(XMLELEMENT("LINE",
XMLFOREST(OL.ATTRIBUTE4 AS "L_ATTR4",
OL.ATTRIBUTE2 AS "L_ATTR2",
OL.ATTRIBUTE1 AS "L_ATTR1",
OL.ATTRIBUTE3 AS "L_ATTR3",
OL.ATTRIBUTE8 AS "L_ATTR13",
OL.ORDERED_QUANTITY AS "ORD_QTY",
OL.ORDERED_ITEM AS "ORD_ITEM",
OL.UNIT_SELLING_PRICE AS "UNIT_SELL",
OL.TAX_VALUE AS "TOTAL_TAX",
OL.INVENTORY_ITEM_ID AS "ITEM_ID",
OL.LINE_NUMBER AS "SRC_LINE_NUM",
OL.ORIG_SYS_LINE_REF AS "ORIG_REFERENCE_LINE_ID",
'SHORT' AS RETURN_REASON )))
FROM OE_ORDER_LINES_ALL OL
WHERE OL.HEADER_ID = OH.HEADER_ID
) LINES
)))
FROM OE_ORDER_HEADERS_ALL OH
WHERE 1=1
--AND OH.HEADER_ID = OL.HEADER_ID
AND OH.ORDER_NUMBER = '5638446'
xml:
No comments:
Post a Comment