add

About Me

My photo
Oracle Apps - Techno Functional consultant

Wednesday, September 28

Example of DBMS_XMLGEN.getxml to generate XML Tag using oracle query

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: