Tuesday, May 18, 2010

XML from QUERY Directky

SELECT DBMS_XMLGEN.getxml(
'SELECT CURSOR(SELECT oha.order_number,
ola.ordered_item,
ola.ordered_quantity
FROM ont.oe_order_headers_all oha,
ont.oe_order_lines_all ola
WHERE oha.header_id = ola.header_id
and oha.order_number in (&order_number) order by ola.line_id) as order_detail,
CURSOR(SELECT ohd.name, ohs.hold_comment
FROM ont.oe_hold_sources_all ohs,
ont.oe_order_holds_all ohld,
ont.oe_hold_definitions ohd,
ont.oe_order_headers_all oha,
ont.oe_order_lines_all ola
WHERE oha.header_id = ola.header_id
AND ola.line_id = ohld.line_id
and ohld.hold_release_id is null
AND ohld.hold_source_id = ohs.hold_source_id
AND ohs.hold_id = ohd.hold_id
AND oha.order_number = &&order_number) as holds_detail
FROM DUAL')
FROM DUAL

No comments:

Post a Comment