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