[Home] [Help]
989:
990: SELECT CURRENT_DATE INTO l_start_time FROM DUAL;
991:
992: xml_stmt := 'DECLARE
993: queryCtx DBMS_XMLGEN.ctxHandle;
994: BEGIN
995: queryCtx := DBMS_XMLGEN.newContext(:xml_query_cursor);
996: DBMS_XMLGEN.getXML(queryCtx, :result, DBMS_XMLGEN.NONE);
997: DBMS_XMLGEN.closeContext (queryCtx);
991:
992: xml_stmt := 'DECLARE
993: queryCtx DBMS_XMLGEN.ctxHandle;
994: BEGIN
995: queryCtx := DBMS_XMLGEN.newContext(:xml_query_cursor);
996: DBMS_XMLGEN.getXML(queryCtx, :result, DBMS_XMLGEN.NONE);
997: DBMS_XMLGEN.closeContext (queryCtx);
998: exception when others then
999: DBMS_XMLGEN.closeContext (queryCtx);
992: xml_stmt := 'DECLARE
993: queryCtx DBMS_XMLGEN.ctxHandle;
994: BEGIN
995: queryCtx := DBMS_XMLGEN.newContext(:xml_query_cursor);
996: DBMS_XMLGEN.getXML(queryCtx, :result, DBMS_XMLGEN.NONE);
997: DBMS_XMLGEN.closeContext (queryCtx);
998: exception when others then
999: DBMS_XMLGEN.closeContext (queryCtx);
1000: RAISE;
993: queryCtx DBMS_XMLGEN.ctxHandle;
994: BEGIN
995: queryCtx := DBMS_XMLGEN.newContext(:xml_query_cursor);
996: DBMS_XMLGEN.getXML(queryCtx, :result, DBMS_XMLGEN.NONE);
997: DBMS_XMLGEN.closeContext (queryCtx);
998: exception when others then
999: DBMS_XMLGEN.closeContext (queryCtx);
1000: RAISE;
1001: END;';
995: queryCtx := DBMS_XMLGEN.newContext(:xml_query_cursor);
996: DBMS_XMLGEN.getXML(queryCtx, :result, DBMS_XMLGEN.NONE);
997: DBMS_XMLGEN.closeContext (queryCtx);
998: exception when others then
999: DBMS_XMLGEN.closeContext (queryCtx);
1000: RAISE;
1001: END;';
1002: execute immediate xml_stmt
1003: USING IN OUT xml_query_cursor, IN OUT result;
1436:
1437: SELECT CURRENT_DATE INTO l_start_time FROM DUAL;
1438:
1439: xml_stmt := 'DECLARE
1440: queryCtx DBMS_XMLGEN.ctxHandle;
1441: BEGIN
1442: queryCtx := DBMS_XMLGEN.newContext(:xml_query_cursor);
1443: DBMS_XMLGEN.getXML(queryCtx, :result, DBMS_XMLGEN.NONE);
1444: DBMS_XMLGEN.closeContext (queryCtx);
1438:
1439: xml_stmt := 'DECLARE
1440: queryCtx DBMS_XMLGEN.ctxHandle;
1441: BEGIN
1442: queryCtx := DBMS_XMLGEN.newContext(:xml_query_cursor);
1443: DBMS_XMLGEN.getXML(queryCtx, :result, DBMS_XMLGEN.NONE);
1444: DBMS_XMLGEN.closeContext (queryCtx);
1445: exception when others then
1446: DBMS_XMLGEN.closeContext (queryCtx);
1439: xml_stmt := 'DECLARE
1440: queryCtx DBMS_XMLGEN.ctxHandle;
1441: BEGIN
1442: queryCtx := DBMS_XMLGEN.newContext(:xml_query_cursor);
1443: DBMS_XMLGEN.getXML(queryCtx, :result, DBMS_XMLGEN.NONE);
1444: DBMS_XMLGEN.closeContext (queryCtx);
1445: exception when others then
1446: DBMS_XMLGEN.closeContext (queryCtx);
1447: RAISE;
1440: queryCtx DBMS_XMLGEN.ctxHandle;
1441: BEGIN
1442: queryCtx := DBMS_XMLGEN.newContext(:xml_query_cursor);
1443: DBMS_XMLGEN.getXML(queryCtx, :result, DBMS_XMLGEN.NONE);
1444: DBMS_XMLGEN.closeContext (queryCtx);
1445: exception when others then
1446: DBMS_XMLGEN.closeContext (queryCtx);
1447: RAISE;
1448: END;';
1442: queryCtx := DBMS_XMLGEN.newContext(:xml_query_cursor);
1443: DBMS_XMLGEN.getXML(queryCtx, :result, DBMS_XMLGEN.NONE);
1444: DBMS_XMLGEN.closeContext (queryCtx);
1445: exception when others then
1446: DBMS_XMLGEN.closeContext (queryCtx);
1447: RAISE;
1448: END;';
1449: execute immediate xml_stmt
1450: USING IN OUT xml_query_cursor, IN OUT result;
1656:
1657: SELECT CURRENT_DATE INTO l_start_time FROM DUAL;
1658:
1659: xml_stmt := 'DECLARE
1660: queryCtx DBMS_XMLGEN.ctxHandle;
1661: BEGIN
1662: queryCtx := DBMS_XMLGEN.newContext(:xml_query_cursor);
1663: DBMS_XMLGEN.getXML(queryCtx, :result, DBMS_XMLGEN.NONE);
1664: DBMS_XMLGEN.closeContext (queryCtx);
1658:
1659: xml_stmt := 'DECLARE
1660: queryCtx DBMS_XMLGEN.ctxHandle;
1661: BEGIN
1662: queryCtx := DBMS_XMLGEN.newContext(:xml_query_cursor);
1663: DBMS_XMLGEN.getXML(queryCtx, :result, DBMS_XMLGEN.NONE);
1664: DBMS_XMLGEN.closeContext (queryCtx);
1665: exception when others then
1666: DBMS_XMLGEN.closeContext (queryCtx);
1659: xml_stmt := 'DECLARE
1660: queryCtx DBMS_XMLGEN.ctxHandle;
1661: BEGIN
1662: queryCtx := DBMS_XMLGEN.newContext(:xml_query_cursor);
1663: DBMS_XMLGEN.getXML(queryCtx, :result, DBMS_XMLGEN.NONE);
1664: DBMS_XMLGEN.closeContext (queryCtx);
1665: exception when others then
1666: DBMS_XMLGEN.closeContext (queryCtx);
1667: RAISE;
1660: queryCtx DBMS_XMLGEN.ctxHandle;
1661: BEGIN
1662: queryCtx := DBMS_XMLGEN.newContext(:xml_query_cursor);
1663: DBMS_XMLGEN.getXML(queryCtx, :result, DBMS_XMLGEN.NONE);
1664: DBMS_XMLGEN.closeContext (queryCtx);
1665: exception when others then
1666: DBMS_XMLGEN.closeContext (queryCtx);
1667: RAISE;
1668: END;';
1662: queryCtx := DBMS_XMLGEN.newContext(:xml_query_cursor);
1663: DBMS_XMLGEN.getXML(queryCtx, :result, DBMS_XMLGEN.NONE);
1664: DBMS_XMLGEN.closeContext (queryCtx);
1665: exception when others then
1666: DBMS_XMLGEN.closeContext (queryCtx);
1667: RAISE;
1668: END;';
1669: execute immediate xml_stmt
1670: USING IN OUT xml_query_cursor, IN OUT result;
1710: l_xml_header_length number;
1711: TYPE auction_header_cursor_type IS REF CURSOR;
1712: xml_query_cursor auction_header_cursor_type;
1713:
1714: queryCtx DBMS_XMLGEN.ctxHandle;
1715:
1716: attachments_cursor auction_header_cursor_type;
1717: doc_rules_cursor auction_header_cursor_type;
1718: generic_msgs_cursor auction_header_cursor_type;
1874: l_two_part_general_msg fnd_new_messages.message_text%TYPE;
1875: l_two_part_tech_msg fnd_new_messages.message_text%TYPE;
1876: l_two_part_comm_msg fnd_new_messages.message_text%TYPE;
1877:
1878: linesCtx DBMS_XMLGEN.ctxHandle;
1879: attrsCtx DBMS_XMLGEN.ctxHandle;
1880: payItemsCtx DBMS_XMLGEN.ctxHandle;
1881: linepdiffCtx DBMS_XMLGEN.ctxHandle;
1882: itempbreaksCtx DBMS_XMLGEN.ctxHandle;
1875: l_two_part_tech_msg fnd_new_messages.message_text%TYPE;
1876: l_two_part_comm_msg fnd_new_messages.message_text%TYPE;
1877:
1878: linesCtx DBMS_XMLGEN.ctxHandle;
1879: attrsCtx DBMS_XMLGEN.ctxHandle;
1880: payItemsCtx DBMS_XMLGEN.ctxHandle;
1881: linepdiffCtx DBMS_XMLGEN.ctxHandle;
1882: itempbreaksCtx DBMS_XMLGEN.ctxHandle;
1883: itempdiffsCtx DBMS_XMLGEN.ctxHandle;
1876: l_two_part_comm_msg fnd_new_messages.message_text%TYPE;
1877:
1878: linesCtx DBMS_XMLGEN.ctxHandle;
1879: attrsCtx DBMS_XMLGEN.ctxHandle;
1880: payItemsCtx DBMS_XMLGEN.ctxHandle;
1881: linepdiffCtx DBMS_XMLGEN.ctxHandle;
1882: itempbreaksCtx DBMS_XMLGEN.ctxHandle;
1883: itempdiffsCtx DBMS_XMLGEN.ctxHandle;
1884: quanTiersCtx DBMS_XMLGEN.ctxHandle;
1877:
1878: linesCtx DBMS_XMLGEN.ctxHandle;
1879: attrsCtx DBMS_XMLGEN.ctxHandle;
1880: payItemsCtx DBMS_XMLGEN.ctxHandle;
1881: linepdiffCtx DBMS_XMLGEN.ctxHandle;
1882: itempbreaksCtx DBMS_XMLGEN.ctxHandle;
1883: itempdiffsCtx DBMS_XMLGEN.ctxHandle;
1884: quanTiersCtx DBMS_XMLGEN.ctxHandle;
1885: pfCtx DBMS_XMLGEN.ctxHandle;
1878: linesCtx DBMS_XMLGEN.ctxHandle;
1879: attrsCtx DBMS_XMLGEN.ctxHandle;
1880: payItemsCtx DBMS_XMLGEN.ctxHandle;
1881: linepdiffCtx DBMS_XMLGEN.ctxHandle;
1882: itempbreaksCtx DBMS_XMLGEN.ctxHandle;
1883: itempdiffsCtx DBMS_XMLGEN.ctxHandle;
1884: quanTiersCtx DBMS_XMLGEN.ctxHandle;
1885: pfCtx DBMS_XMLGEN.ctxHandle;
1886:
1879: attrsCtx DBMS_XMLGEN.ctxHandle;
1880: payItemsCtx DBMS_XMLGEN.ctxHandle;
1881: linepdiffCtx DBMS_XMLGEN.ctxHandle;
1882: itempbreaksCtx DBMS_XMLGEN.ctxHandle;
1883: itempdiffsCtx DBMS_XMLGEN.ctxHandle;
1884: quanTiersCtx DBMS_XMLGEN.ctxHandle;
1885: pfCtx DBMS_XMLGEN.ctxHandle;
1886:
1887: CURSOR line_num_cur IS (SELECT line_number FROM pon_auction_item_prices_all WHERE auction_header_id = p_auction_header_id);
1880: payItemsCtx DBMS_XMLGEN.ctxHandle;
1881: linepdiffCtx DBMS_XMLGEN.ctxHandle;
1882: itempbreaksCtx DBMS_XMLGEN.ctxHandle;
1883: itempdiffsCtx DBMS_XMLGEN.ctxHandle;
1884: quanTiersCtx DBMS_XMLGEN.ctxHandle;
1885: pfCtx DBMS_XMLGEN.ctxHandle;
1886:
1887: CURSOR line_num_cur IS (SELECT line_number FROM pon_auction_item_prices_all WHERE auction_header_id = p_auction_header_id);
1888: line_num NUMBER;
1881: linepdiffCtx DBMS_XMLGEN.ctxHandle;
1882: itempbreaksCtx DBMS_XMLGEN.ctxHandle;
1883: itempdiffsCtx DBMS_XMLGEN.ctxHandle;
1884: quanTiersCtx DBMS_XMLGEN.ctxHandle;
1885: pfCtx DBMS_XMLGEN.ctxHandle;
1886:
1887: CURSOR line_num_cur IS (SELECT line_number FROM pon_auction_item_prices_all WHERE auction_header_id = p_auction_header_id);
1888: line_num NUMBER;
1889:
2670:
2671:
2672:
2673: BEGIN
2674: queryCtx := DBMS_XMLGEN.newContext(xml_query_cursor);
2675: DBMS_XMLGEN.SetRowSetTag(queryCtx, null);
2676: DBMS_XMLGEN.SetRowTag(queryCtx, null);
2677: DBMS_XMLGEN.getXMLType(queryCtx, xml_res, DBMS_XMLGEN.NONE);
2678: xml_clob := xml_res.getCLOBVal();
2671:
2672:
2673: BEGIN
2674: queryCtx := DBMS_XMLGEN.newContext(xml_query_cursor);
2675: DBMS_XMLGEN.SetRowSetTag(queryCtx, null);
2676: DBMS_XMLGEN.SetRowTag(queryCtx, null);
2677: DBMS_XMLGEN.getXMLType(queryCtx, xml_res, DBMS_XMLGEN.NONE);
2678: xml_clob := xml_res.getCLOBVal();
2679: DBMS_XMLGEN.closeContext (queryCtx);
2672:
2673: BEGIN
2674: queryCtx := DBMS_XMLGEN.newContext(xml_query_cursor);
2675: DBMS_XMLGEN.SetRowSetTag(queryCtx, null);
2676: DBMS_XMLGEN.SetRowTag(queryCtx, null);
2677: DBMS_XMLGEN.getXMLType(queryCtx, xml_res, DBMS_XMLGEN.NONE);
2678: xml_clob := xml_res.getCLOBVal();
2679: DBMS_XMLGEN.closeContext (queryCtx);
2680: exception when others then
2673: BEGIN
2674: queryCtx := DBMS_XMLGEN.newContext(xml_query_cursor);
2675: DBMS_XMLGEN.SetRowSetTag(queryCtx, null);
2676: DBMS_XMLGEN.SetRowTag(queryCtx, null);
2677: DBMS_XMLGEN.getXMLType(queryCtx, xml_res, DBMS_XMLGEN.NONE);
2678: xml_clob := xml_res.getCLOBVal();
2679: DBMS_XMLGEN.closeContext (queryCtx);
2680: exception when others then
2681: DBMS_XMLGEN.closeContext (queryCtx);
2675: DBMS_XMLGEN.SetRowSetTag(queryCtx, null);
2676: DBMS_XMLGEN.SetRowTag(queryCtx, null);
2677: DBMS_XMLGEN.getXMLType(queryCtx, xml_res, DBMS_XMLGEN.NONE);
2678: xml_clob := xml_res.getCLOBVal();
2679: DBMS_XMLGEN.closeContext (queryCtx);
2680: exception when others then
2681: DBMS_XMLGEN.closeContext (queryCtx);
2682: RAISE;
2683: END;
2677: DBMS_XMLGEN.getXMLType(queryCtx, xml_res, DBMS_XMLGEN.NONE);
2678: xml_clob := xml_res.getCLOBVal();
2679: DBMS_XMLGEN.closeContext (queryCtx);
2680: exception when others then
2681: DBMS_XMLGEN.closeContext (queryCtx);
2682: RAISE;
2683: END;
2684:
2685: CLOSE xml_query_cursor;
2891: AND paip.project_exp_organization_id = hrorg.organization_id(+)
2892: AND paip.work_approver_user_id = fuser.user_id(+)
2893: order by paip.disp_line_number;
2894:
2895: linesCtx := DBMS_XMLGEN.newContext(lines_cursor);
2896:
2897: Dbms_Lob.append(xml_clob, '
2898: BEGIN
2899: DBMS_XMLGEN.SetRowSetTag(linesCtx, null);
2895: linesCtx := DBMS_XMLGEN.newContext(lines_cursor);
2896:
2897: Dbms_Lob.append(xml_clob, '
2898: BEGIN
2899: DBMS_XMLGEN.SetRowSetTag(linesCtx, null);
2900: DBMS_XMLGEN.SetRowTag(linesCtx, null);
2901: lines_res := NULL;
2902: DBMS_XMLGEN.getXMLType(linesCtx, lines_res, DBMS_XMLGEN.NONE);
2903: Dbms_Lob.append(xml_clob, lines_res.getCLOBVal());
2896:
2897: Dbms_Lob.append(xml_clob, '
2898: BEGIN
2899: DBMS_XMLGEN.SetRowSetTag(linesCtx, null);
2900: DBMS_XMLGEN.SetRowTag(linesCtx, null);
2901: lines_res := NULL;
2902: DBMS_XMLGEN.getXMLType(linesCtx, lines_res, DBMS_XMLGEN.NONE);
2903: Dbms_Lob.append(xml_clob, lines_res.getCLOBVal());
2904: exception when others then
2898: BEGIN
2899: DBMS_XMLGEN.SetRowSetTag(linesCtx, null);
2900: DBMS_XMLGEN.SetRowTag(linesCtx, null);
2901: lines_res := NULL;
2902: DBMS_XMLGEN.getXMLType(linesCtx, lines_res, DBMS_XMLGEN.NONE);
2903: Dbms_Lob.append(xml_clob, lines_res.getCLOBVal());
2904: exception when others then
2905: DBMS_XMLGEN.closeContext (linesCtx);
2906: RAISE;
2901: lines_res := NULL;
2902: DBMS_XMLGEN.getXMLType(linesCtx, lines_res, DBMS_XMLGEN.NONE);
2903: Dbms_Lob.append(xml_clob, lines_res.getCLOBVal());
2904: exception when others then
2905: DBMS_XMLGEN.closeContext (linesCtx);
2906: RAISE;
2907: END;
2908:
2909: OPEN pay_items_cursor FOR SELECT
3065: AND fuser.user_id(+) = pay.work_approver_user_id
3066: ORDER BY payment_display_number;
3067:
3068: BEGIN
3069: payItemsCtx := DBMS_XMLGEN.newContext(pay_items_cursor);
3070: DBMS_XMLGEN.SetRowSetTag(payItemsCtx, 'PAY_ITEMS');
3071: DBMS_XMLGEN.SetRowTag(payItemsCtx, 'PAY_ITEMS_ROW');
3072: pay_items_res := NULL;
3073: DBMS_XMLGEN.getXMLType(payItemsCtx, pay_items_res, DBMS_XMLGEN.NONE);
3066: ORDER BY payment_display_number;
3067:
3068: BEGIN
3069: payItemsCtx := DBMS_XMLGEN.newContext(pay_items_cursor);
3070: DBMS_XMLGEN.SetRowSetTag(payItemsCtx, 'PAY_ITEMS');
3071: DBMS_XMLGEN.SetRowTag(payItemsCtx, 'PAY_ITEMS_ROW');
3072: pay_items_res := NULL;
3073: DBMS_XMLGEN.getXMLType(payItemsCtx, pay_items_res, DBMS_XMLGEN.NONE);
3074: IF pay_items_res IS NULL THEN
3067:
3068: BEGIN
3069: payItemsCtx := DBMS_XMLGEN.newContext(pay_items_cursor);
3070: DBMS_XMLGEN.SetRowSetTag(payItemsCtx, 'PAY_ITEMS');
3071: DBMS_XMLGEN.SetRowTag(payItemsCtx, 'PAY_ITEMS_ROW');
3072: pay_items_res := NULL;
3073: DBMS_XMLGEN.getXMLType(payItemsCtx, pay_items_res, DBMS_XMLGEN.NONE);
3074: IF pay_items_res IS NULL THEN
3075: pay_items_res := XMLType('
3069: payItemsCtx := DBMS_XMLGEN.newContext(pay_items_cursor);
3070: DBMS_XMLGEN.SetRowSetTag(payItemsCtx, 'PAY_ITEMS');
3071: DBMS_XMLGEN.SetRowTag(payItemsCtx, 'PAY_ITEMS_ROW');
3072: pay_items_res := NULL;
3073: DBMS_XMLGEN.getXMLType(payItemsCtx, pay_items_res, DBMS_XMLGEN.NONE);
3074: IF pay_items_res IS NULL THEN
3075: pay_items_res := XMLType('
3076: END IF;
3077: Dbms_Lob.append(xml_clob, pay_items_res.getCLOBVal());
3075: pay_items_res := XMLType('
3076: END IF;
3077: Dbms_Lob.append(xml_clob, pay_items_res.getCLOBVal());
3078: exception when others then
3079: DBMS_XMLGEN.closeContext (payItemsCtx);
3080: RAISE;
3081: END;
3082:
3083: CLOSE pay_items_cursor;
3145: and nvl(attrGrpFlv.end_date_active,sysdate) > sysdate-1
3146: order by NVL(pal.attr_group_seq_number,10),pal.attr_disp_seq_number;
3147:
3148: BEGIN
3149: attrsCtx := DBMS_XMLGEN.newContext(line_attr_cursor);
3150: DBMS_XMLGEN.SetRowSetTag(attrsCtx, 'LINE_ATTRIBUTES');
3151: DBMS_XMLGEN.SetRowTag(attrsCtx, 'LINE_ATTRIBUTES_ROW');
3152: line_attr_res := NULL;
3153: DBMS_XMLGEN.getXMLType(attrsCtx, line_attr_res, DBMS_XMLGEN.NONE);
3146: order by NVL(pal.attr_group_seq_number,10),pal.attr_disp_seq_number;
3147:
3148: BEGIN
3149: attrsCtx := DBMS_XMLGEN.newContext(line_attr_cursor);
3150: DBMS_XMLGEN.SetRowSetTag(attrsCtx, 'LINE_ATTRIBUTES');
3151: DBMS_XMLGEN.SetRowTag(attrsCtx, 'LINE_ATTRIBUTES_ROW');
3152: line_attr_res := NULL;
3153: DBMS_XMLGEN.getXMLType(attrsCtx, line_attr_res, DBMS_XMLGEN.NONE);
3154: IF line_attr_res IS NULL THEN
3147:
3148: BEGIN
3149: attrsCtx := DBMS_XMLGEN.newContext(line_attr_cursor);
3150: DBMS_XMLGEN.SetRowSetTag(attrsCtx, 'LINE_ATTRIBUTES');
3151: DBMS_XMLGEN.SetRowTag(attrsCtx, 'LINE_ATTRIBUTES_ROW');
3152: line_attr_res := NULL;
3153: DBMS_XMLGEN.getXMLType(attrsCtx, line_attr_res, DBMS_XMLGEN.NONE);
3154: IF line_attr_res IS NULL THEN
3155: line_attr_res := XMLType('
3149: attrsCtx := DBMS_XMLGEN.newContext(line_attr_cursor);
3150: DBMS_XMLGEN.SetRowSetTag(attrsCtx, 'LINE_ATTRIBUTES');
3151: DBMS_XMLGEN.SetRowTag(attrsCtx, 'LINE_ATTRIBUTES_ROW');
3152: line_attr_res := NULL;
3153: DBMS_XMLGEN.getXMLType(attrsCtx, line_attr_res, DBMS_XMLGEN.NONE);
3154: IF line_attr_res IS NULL THEN
3155: line_attr_res := XMLType('
3156: END IF;
3157: Dbms_Lob.append(xml_clob, line_attr_res.getCLOBVal());
3155: line_attr_res := XMLType('
3156: END IF;
3157: Dbms_Lob.append(xml_clob, line_attr_res.getCLOBVal());
3158: exception when others then
3159: DBMS_XMLGEN.closeContext (attrsCtx);
3160: RAISE;
3161: END;
3162:
3163: CLOSE line_attr_cursor;
3242: AND pf_values.supplier_seq_number(+) = l_supplier_sequence_number
3243: order by pe.line_number, pe.sequence_number ASC;
3244:
3245: BEGIN
3246: pfCtx := DBMS_XMLGEN.newContext(pf_cursor);
3247: DBMS_XMLGEN.SetRowSetTag(pfCtx, 'PRICE_FACTORS');
3248: DBMS_XMLGEN.SetRowTag(pfCtx, 'PRICE_FACTORS_ROW');
3249: pf_res := NULL;
3250: DBMS_XMLGEN.getXMLType(pfCtx, pf_res, DBMS_XMLGEN.NONE);
3243: order by pe.line_number, pe.sequence_number ASC;
3244:
3245: BEGIN
3246: pfCtx := DBMS_XMLGEN.newContext(pf_cursor);
3247: DBMS_XMLGEN.SetRowSetTag(pfCtx, 'PRICE_FACTORS');
3248: DBMS_XMLGEN.SetRowTag(pfCtx, 'PRICE_FACTORS_ROW');
3249: pf_res := NULL;
3250: DBMS_XMLGEN.getXMLType(pfCtx, pf_res, DBMS_XMLGEN.NONE);
3251: IF pf_res IS NULL THEN
3244:
3245: BEGIN
3246: pfCtx := DBMS_XMLGEN.newContext(pf_cursor);
3247: DBMS_XMLGEN.SetRowSetTag(pfCtx, 'PRICE_FACTORS');
3248: DBMS_XMLGEN.SetRowTag(pfCtx, 'PRICE_FACTORS_ROW');
3249: pf_res := NULL;
3250: DBMS_XMLGEN.getXMLType(pfCtx, pf_res, DBMS_XMLGEN.NONE);
3251: IF pf_res IS NULL THEN
3252: pf_res := XMLType('
3246: pfCtx := DBMS_XMLGEN.newContext(pf_cursor);
3247: DBMS_XMLGEN.SetRowSetTag(pfCtx, 'PRICE_FACTORS');
3248: DBMS_XMLGEN.SetRowTag(pfCtx, 'PRICE_FACTORS_ROW');
3249: pf_res := NULL;
3250: DBMS_XMLGEN.getXMLType(pfCtx, pf_res, DBMS_XMLGEN.NONE);
3251: IF pf_res IS NULL THEN
3252: pf_res := XMLType('
3253: END IF;
3254: Dbms_Lob.append(xml_clob, pf_res.getCLOBVal());
3252: pf_res := XMLType('
3253: END IF;
3254: Dbms_Lob.append(xml_clob, pf_res.getCLOBVal());
3255: exception when others then
3256: DBMS_XMLGEN.closeContext (pfCtx);
3257: RAISE;
3258: END;
3259:
3260: CLOSE pf_cursor;
3277: and ppd.shipment_number = -1
3278: and pbpd.price_differential_number(+) = ppd.price_differential_number;
3279:
3280: BEGIN
3281: linepdiffCtx := DBMS_XMLGEN.newContext(line_price_diff_cursor);
3282: DBMS_XMLGEN.SetRowSetTag(linepdiffCtx, 'LINE_PRICE_DIFFERENTIALS');
3283: DBMS_XMLGEN.SetRowTag(linepdiffCtx, 'LINE_PRICE_DIFFERENTIALS_ROW');
3284: line_price_diff_res := NULL;
3285: DBMS_XMLGEN.getXMLType(linepdiffCtx, line_price_diff_res, DBMS_XMLGEN.NONE);
3278: and pbpd.price_differential_number(+) = ppd.price_differential_number;
3279:
3280: BEGIN
3281: linepdiffCtx := DBMS_XMLGEN.newContext(line_price_diff_cursor);
3282: DBMS_XMLGEN.SetRowSetTag(linepdiffCtx, 'LINE_PRICE_DIFFERENTIALS');
3283: DBMS_XMLGEN.SetRowTag(linepdiffCtx, 'LINE_PRICE_DIFFERENTIALS_ROW');
3284: line_price_diff_res := NULL;
3285: DBMS_XMLGEN.getXMLType(linepdiffCtx, line_price_diff_res, DBMS_XMLGEN.NONE);
3286: IF line_price_diff_res IS NULL THEN
3279:
3280: BEGIN
3281: linepdiffCtx := DBMS_XMLGEN.newContext(line_price_diff_cursor);
3282: DBMS_XMLGEN.SetRowSetTag(linepdiffCtx, 'LINE_PRICE_DIFFERENTIALS');
3283: DBMS_XMLGEN.SetRowTag(linepdiffCtx, 'LINE_PRICE_DIFFERENTIALS_ROW');
3284: line_price_diff_res := NULL;
3285: DBMS_XMLGEN.getXMLType(linepdiffCtx, line_price_diff_res, DBMS_XMLGEN.NONE);
3286: IF line_price_diff_res IS NULL THEN
3287: line_price_diff_res := XMLType('
3281: linepdiffCtx := DBMS_XMLGEN.newContext(line_price_diff_cursor);
3282: DBMS_XMLGEN.SetRowSetTag(linepdiffCtx, 'LINE_PRICE_DIFFERENTIALS');
3283: DBMS_XMLGEN.SetRowTag(linepdiffCtx, 'LINE_PRICE_DIFFERENTIALS_ROW');
3284: line_price_diff_res := NULL;
3285: DBMS_XMLGEN.getXMLType(linepdiffCtx, line_price_diff_res, DBMS_XMLGEN.NONE);
3286: IF line_price_diff_res IS NULL THEN
3287: line_price_diff_res := XMLType('
3288: END IF;
3289: Dbms_Lob.append(xml_clob, line_price_diff_res.getCLOBVal());
3287: line_price_diff_res := XMLType('
3288: END IF;
3289: Dbms_Lob.append(xml_clob, line_price_diff_res.getCLOBVal());
3290: exception when others then
3291: DBMS_XMLGEN.closeContext (linepdiffCtx);
3292: RAISE;
3293: END;
3294:
3295: CLOSE line_price_diff_cursor;
3371: AND loc.location_id(+) = pas.ship_to_location_id
3372: and not exists (select 1 from pon_bid_item_prices where bid_number=p_bid_number and line_number=paip.line_number);
3373:
3374: BEGIN
3375: itempbreaksCtx := DBMS_XMLGEN.newContext(item_pb_cursor);
3376: DBMS_XMLGEN.SetRowSetTag(itempbreaksCtx, 'ITEM_PRICE_BREAKS');
3377: DBMS_XMLGEN.SetRowTag(itempbreaksCtx, 'ITEM_PRICE_BREAKS_ROW');
3378: item_pb_res := NULL;
3379: DBMS_XMLGEN.getXMLType(itempbreaksCtx, item_pb_res, DBMS_XMLGEN.NONE);
3372: and not exists (select 1 from pon_bid_item_prices where bid_number=p_bid_number and line_number=paip.line_number);
3373:
3374: BEGIN
3375: itempbreaksCtx := DBMS_XMLGEN.newContext(item_pb_cursor);
3376: DBMS_XMLGEN.SetRowSetTag(itempbreaksCtx, 'ITEM_PRICE_BREAKS');
3377: DBMS_XMLGEN.SetRowTag(itempbreaksCtx, 'ITEM_PRICE_BREAKS_ROW');
3378: item_pb_res := NULL;
3379: DBMS_XMLGEN.getXMLType(itempbreaksCtx, item_pb_res, DBMS_XMLGEN.NONE);
3380: IF item_pb_res IS NULL THEN
3373:
3374: BEGIN
3375: itempbreaksCtx := DBMS_XMLGEN.newContext(item_pb_cursor);
3376: DBMS_XMLGEN.SetRowSetTag(itempbreaksCtx, 'ITEM_PRICE_BREAKS');
3377: DBMS_XMLGEN.SetRowTag(itempbreaksCtx, 'ITEM_PRICE_BREAKS_ROW');
3378: item_pb_res := NULL;
3379: DBMS_XMLGEN.getXMLType(itempbreaksCtx, item_pb_res, DBMS_XMLGEN.NONE);
3380: IF item_pb_res IS NULL THEN
3381: item_pb_res := XMLType('
3375: itempbreaksCtx := DBMS_XMLGEN.newContext(item_pb_cursor);
3376: DBMS_XMLGEN.SetRowSetTag(itempbreaksCtx, 'ITEM_PRICE_BREAKS');
3377: DBMS_XMLGEN.SetRowTag(itempbreaksCtx, 'ITEM_PRICE_BREAKS_ROW');
3378: item_pb_res := NULL;
3379: DBMS_XMLGEN.getXMLType(itempbreaksCtx, item_pb_res, DBMS_XMLGEN.NONE);
3380: IF item_pb_res IS NULL THEN
3381: item_pb_res := XMLType('
3382: END IF;
3383: Dbms_Lob.append(xml_clob, item_pb_res.getCLOBVal());
3381: item_pb_res := XMLType('
3382: END IF;
3383: Dbms_Lob.append(xml_clob, item_pb_res.getCLOBVal());
3384: exception when others then
3385: DBMS_XMLGEN.closeContext (itempbreaksCtx);
3386: RAISE;
3387: END;
3388:
3389: CLOSE item_pb_cursor;
3408: and pbpd.price_differential_number(+) = ppd.price_differential_number
3409: ORDER BY shipment_number, price_differential_number;
3410:
3411: BEGIN
3412: itempdiffsCtx := DBMS_XMLGEN.newContext(item_price_diff_cursor);
3413: DBMS_XMLGEN.SetRowSetTag(itempdiffsCtx, 'ITEM_PRICE_DIFFERENTIALS');
3414: DBMS_XMLGEN.SetRowTag(itempdiffsCtx, 'ITEM_PRICE_DIFFERENTIALS_ROW');
3415: item_price_diff_res := NULL;
3416: DBMS_XMLGEN.getXMLType(itempdiffsCtx, item_price_diff_res, DBMS_XMLGEN.NONE);
3409: ORDER BY shipment_number, price_differential_number;
3410:
3411: BEGIN
3412: itempdiffsCtx := DBMS_XMLGEN.newContext(item_price_diff_cursor);
3413: DBMS_XMLGEN.SetRowSetTag(itempdiffsCtx, 'ITEM_PRICE_DIFFERENTIALS');
3414: DBMS_XMLGEN.SetRowTag(itempdiffsCtx, 'ITEM_PRICE_DIFFERENTIALS_ROW');
3415: item_price_diff_res := NULL;
3416: DBMS_XMLGEN.getXMLType(itempdiffsCtx, item_price_diff_res, DBMS_XMLGEN.NONE);
3417: IF item_price_diff_res IS NULL THEN
3410:
3411: BEGIN
3412: itempdiffsCtx := DBMS_XMLGEN.newContext(item_price_diff_cursor);
3413: DBMS_XMLGEN.SetRowSetTag(itempdiffsCtx, 'ITEM_PRICE_DIFFERENTIALS');
3414: DBMS_XMLGEN.SetRowTag(itempdiffsCtx, 'ITEM_PRICE_DIFFERENTIALS_ROW');
3415: item_price_diff_res := NULL;
3416: DBMS_XMLGEN.getXMLType(itempdiffsCtx, item_price_diff_res, DBMS_XMLGEN.NONE);
3417: IF item_price_diff_res IS NULL THEN
3418: item_price_diff_res := XMLType('
3412: itempdiffsCtx := DBMS_XMLGEN.newContext(item_price_diff_cursor);
3413: DBMS_XMLGEN.SetRowSetTag(itempdiffsCtx, 'ITEM_PRICE_DIFFERENTIALS');
3414: DBMS_XMLGEN.SetRowTag(itempdiffsCtx, 'ITEM_PRICE_DIFFERENTIALS_ROW');
3415: item_price_diff_res := NULL;
3416: DBMS_XMLGEN.getXMLType(itempdiffsCtx, item_price_diff_res, DBMS_XMLGEN.NONE);
3417: IF item_price_diff_res IS NULL THEN
3418: item_price_diff_res := XMLType('
3419: END IF;
3420: Dbms_Lob.append(xml_clob, item_price_diff_res.getCLOBVal());
3418: item_price_diff_res := XMLType('
3419: END IF;
3420: Dbms_Lob.append(xml_clob, item_price_diff_res.getCLOBVal());
3421: exception when others then
3422: DBMS_XMLGEN.closeContext (itempdiffsCtx);
3423: RAISE;
3424: END;
3425:
3426: CLOSE item_price_diff_cursor;
3472: and not exists (select 1 from pon_bid_item_prices where bid_number=p_bid_number and line_number=paip.line_number)
3473: ORDER BY bid_shipment_number ASC;
3474:
3475: BEGIN
3476: quanTiersCtx := DBMS_XMLGEN.newContext(item_quan_cursor);
3477: DBMS_XMLGEN.SetRowSetTag(quanTiersCtx, 'ITEM_QUANTITY_TIERS');
3478: DBMS_XMLGEN.SetRowTag(quanTiersCtx, 'ITEM_QUANTITY_TIERS_ROW');
3479: item_quan_res := NULL;
3480: DBMS_XMLGEN.getXMLType(quanTiersCtx, item_quan_res, DBMS_XMLGEN.NONE);
3473: ORDER BY bid_shipment_number ASC;
3474:
3475: BEGIN
3476: quanTiersCtx := DBMS_XMLGEN.newContext(item_quan_cursor);
3477: DBMS_XMLGEN.SetRowSetTag(quanTiersCtx, 'ITEM_QUANTITY_TIERS');
3478: DBMS_XMLGEN.SetRowTag(quanTiersCtx, 'ITEM_QUANTITY_TIERS_ROW');
3479: item_quan_res := NULL;
3480: DBMS_XMLGEN.getXMLType(quanTiersCtx, item_quan_res, DBMS_XMLGEN.NONE);
3481: IF item_quan_res IS NULL THEN
3474:
3475: BEGIN
3476: quanTiersCtx := DBMS_XMLGEN.newContext(item_quan_cursor);
3477: DBMS_XMLGEN.SetRowSetTag(quanTiersCtx, 'ITEM_QUANTITY_TIERS');
3478: DBMS_XMLGEN.SetRowTag(quanTiersCtx, 'ITEM_QUANTITY_TIERS_ROW');
3479: item_quan_res := NULL;
3480: DBMS_XMLGEN.getXMLType(quanTiersCtx, item_quan_res, DBMS_XMLGEN.NONE);
3481: IF item_quan_res IS NULL THEN
3482: item_quan_res := XMLType('
3476: quanTiersCtx := DBMS_XMLGEN.newContext(item_quan_cursor);
3477: DBMS_XMLGEN.SetRowSetTag(quanTiersCtx, 'ITEM_QUANTITY_TIERS');
3478: DBMS_XMLGEN.SetRowTag(quanTiersCtx, 'ITEM_QUANTITY_TIERS_ROW');
3479: item_quan_res := NULL;
3480: DBMS_XMLGEN.getXMLType(quanTiersCtx, item_quan_res, DBMS_XMLGEN.NONE);
3481: IF item_quan_res IS NULL THEN
3482: item_quan_res := XMLType('
3483: END IF;
3484: Dbms_Lob.append(xml_clob, item_quan_res.getCLOBVal());
3482: item_quan_res := XMLType('
3483: END IF;
3484: Dbms_Lob.append(xml_clob, item_quan_res.getCLOBVal());
3485: exception when others then
3486: DBMS_XMLGEN.closeContext (quanTiersCtx);
3487: RAISE;
3488: END;
3489:
3490: CLOSE item_quan_cursor;
3491:
3492: Dbms_Lob.append(xml_clob, '');
3493: CLOSE lines_cursor;
3494: END LOOP;
3495: DBMS_XMLGEN.closeContext (linesCtx);
3496: DBMS_XMLGEN.closeContext (payItemsCtx);
3497: DBMS_XMLGEN.closeContext (attrsCtx);
3498: DBMS_XMLGEN.closeContext (pfCtx);
3499: DBMS_XMLGEN.closeContext (linepdiffCtx);
3492: Dbms_Lob.append(xml_clob, '');
3493: CLOSE lines_cursor;
3494: END LOOP;
3495: DBMS_XMLGEN.closeContext (linesCtx);
3496: DBMS_XMLGEN.closeContext (payItemsCtx);
3497: DBMS_XMLGEN.closeContext (attrsCtx);
3498: DBMS_XMLGEN.closeContext (pfCtx);
3499: DBMS_XMLGEN.closeContext (linepdiffCtx);
3500: DBMS_XMLGEN.closeContext (itempbreaksCtx);
3493: CLOSE lines_cursor;
3494: END LOOP;
3495: DBMS_XMLGEN.closeContext (linesCtx);
3496: DBMS_XMLGEN.closeContext (payItemsCtx);
3497: DBMS_XMLGEN.closeContext (attrsCtx);
3498: DBMS_XMLGEN.closeContext (pfCtx);
3499: DBMS_XMLGEN.closeContext (linepdiffCtx);
3500: DBMS_XMLGEN.closeContext (itempbreaksCtx);
3501: DBMS_XMLGEN.closeContext (itempdiffsCtx);
3494: END LOOP;
3495: DBMS_XMLGEN.closeContext (linesCtx);
3496: DBMS_XMLGEN.closeContext (payItemsCtx);
3497: DBMS_XMLGEN.closeContext (attrsCtx);
3498: DBMS_XMLGEN.closeContext (pfCtx);
3499: DBMS_XMLGEN.closeContext (linepdiffCtx);
3500: DBMS_XMLGEN.closeContext (itempbreaksCtx);
3501: DBMS_XMLGEN.closeContext (itempdiffsCtx);
3502: DBMS_XMLGEN.closeContext (quanTiersCtx);
3495: DBMS_XMLGEN.closeContext (linesCtx);
3496: DBMS_XMLGEN.closeContext (payItemsCtx);
3497: DBMS_XMLGEN.closeContext (attrsCtx);
3498: DBMS_XMLGEN.closeContext (pfCtx);
3499: DBMS_XMLGEN.closeContext (linepdiffCtx);
3500: DBMS_XMLGEN.closeContext (itempbreaksCtx);
3501: DBMS_XMLGEN.closeContext (itempdiffsCtx);
3502: DBMS_XMLGEN.closeContext (quanTiersCtx);
3503: Dbms_Lob.append(xml_clob, '');
3496: DBMS_XMLGEN.closeContext (payItemsCtx);
3497: DBMS_XMLGEN.closeContext (attrsCtx);
3498: DBMS_XMLGEN.closeContext (pfCtx);
3499: DBMS_XMLGEN.closeContext (linepdiffCtx);
3500: DBMS_XMLGEN.closeContext (itempbreaksCtx);
3501: DBMS_XMLGEN.closeContext (itempdiffsCtx);
3502: DBMS_XMLGEN.closeContext (quanTiersCtx);
3503: Dbms_Lob.append(xml_clob, '');
3504:
3497: DBMS_XMLGEN.closeContext (attrsCtx);
3498: DBMS_XMLGEN.closeContext (pfCtx);
3499: DBMS_XMLGEN.closeContext (linepdiffCtx);
3500: DBMS_XMLGEN.closeContext (itempbreaksCtx);
3501: DBMS_XMLGEN.closeContext (itempdiffsCtx);
3502: DBMS_XMLGEN.closeContext (quanTiersCtx);
3503: Dbms_Lob.append(xml_clob, '');
3504:
3505: CLOSE line_num_cur;
3498: DBMS_XMLGEN.closeContext (pfCtx);
3499: DBMS_XMLGEN.closeContext (linepdiffCtx);
3500: DBMS_XMLGEN.closeContext (itempbreaksCtx);
3501: DBMS_XMLGEN.closeContext (itempdiffsCtx);
3502: DBMS_XMLGEN.closeContext (quanTiersCtx);
3503: Dbms_Lob.append(xml_clob, '');
3504:
3505: CLOSE line_num_cur;
3506:
3554: AND TRUNC(SYSDATE) BETWEEN P.EFFECTIVE_START_DATE AND P.EFFECTIVE_END_DATE
3555: ORDER BY P.FULL_NAME, U.USER_NAME;
3556:
3557: BEGIN
3558: queryCtx := DBMS_XMLGEN.newContext(collab_team_cursor);
3559: DBMS_XMLGEN.SetRowSetTag(queryCtx, 'COLLABORATION_TEAM');
3560: DBMS_XMLGEN.SetRowTag(queryCtx, 'COLLABORATION_TEAM_ROW');
3561: DBMS_XMLGEN.getXMLType(queryCtx, collab_team_res, DBMS_XMLGEN.NONE);
3562: IF collab_team_res IS NULL THEN
3555: ORDER BY P.FULL_NAME, U.USER_NAME;
3556:
3557: BEGIN
3558: queryCtx := DBMS_XMLGEN.newContext(collab_team_cursor);
3559: DBMS_XMLGEN.SetRowSetTag(queryCtx, 'COLLABORATION_TEAM');
3560: DBMS_XMLGEN.SetRowTag(queryCtx, 'COLLABORATION_TEAM_ROW');
3561: DBMS_XMLGEN.getXMLType(queryCtx, collab_team_res, DBMS_XMLGEN.NONE);
3562: IF collab_team_res IS NULL THEN
3563: collab_team_res := XMLType('
3556:
3557: BEGIN
3558: queryCtx := DBMS_XMLGEN.newContext(collab_team_cursor);
3559: DBMS_XMLGEN.SetRowSetTag(queryCtx, 'COLLABORATION_TEAM');
3560: DBMS_XMLGEN.SetRowTag(queryCtx, 'COLLABORATION_TEAM_ROW');
3561: DBMS_XMLGEN.getXMLType(queryCtx, collab_team_res, DBMS_XMLGEN.NONE);
3562: IF collab_team_res IS NULL THEN
3563: collab_team_res := XMLType('
3564: END IF;
3557: BEGIN
3558: queryCtx := DBMS_XMLGEN.newContext(collab_team_cursor);
3559: DBMS_XMLGEN.SetRowSetTag(queryCtx, 'COLLABORATION_TEAM');
3560: DBMS_XMLGEN.SetRowTag(queryCtx, 'COLLABORATION_TEAM_ROW');
3561: DBMS_XMLGEN.getXMLType(queryCtx, collab_team_res, DBMS_XMLGEN.NONE);
3562: IF collab_team_res IS NULL THEN
3563: collab_team_res := XMLType('
3564: END IF;
3565: Dbms_Lob.append(xml_clob, collab_team_res.getCLOBVal());
3562: IF collab_team_res IS NULL THEN
3563: collab_team_res := XMLType('
3564: END IF;
3565: Dbms_Lob.append(xml_clob, collab_team_res.getCLOBVal());
3566: DBMS_XMLGEN.closeContext (queryCtx);
3567: exception when others then
3568: DBMS_XMLGEN.closeContext (queryCtx);
3569: RAISE;
3570: END;
3564: END IF;
3565: Dbms_Lob.append(xml_clob, collab_team_res.getCLOBVal());
3566: DBMS_XMLGEN.closeContext (queryCtx);
3567: exception when others then
3568: DBMS_XMLGEN.closeContext (queryCtx);
3569: RAISE;
3570: END;
3571:
3572: CLOSE collab_team_cursor;
3583: AND pah.has_scoring_teams_flag = 'Y' -- teams present only if flag present
3584: ORDER BY sctm.team_name;
3585:
3586: BEGIN
3587: queryCtx := DBMS_XMLGEN.newContext(scoring_team_cursor);
3588: DBMS_XMLGEN.SetRowSetTag(queryCtx, 'SCORING_TEAMS');
3589: DBMS_XMLGEN.SetRowTag(queryCtx, 'SCORING_TEAMS_ROW');
3590: DBMS_XMLGEN.getXMLType(queryCtx, scoring_team_res, DBMS_XMLGEN.NONE);
3591: IF scoring_team_res IS NULL THEN
3584: ORDER BY sctm.team_name;
3585:
3586: BEGIN
3587: queryCtx := DBMS_XMLGEN.newContext(scoring_team_cursor);
3588: DBMS_XMLGEN.SetRowSetTag(queryCtx, 'SCORING_TEAMS');
3589: DBMS_XMLGEN.SetRowTag(queryCtx, 'SCORING_TEAMS_ROW');
3590: DBMS_XMLGEN.getXMLType(queryCtx, scoring_team_res, DBMS_XMLGEN.NONE);
3591: IF scoring_team_res IS NULL THEN
3592: scoring_team_res := XMLType('
3585:
3586: BEGIN
3587: queryCtx := DBMS_XMLGEN.newContext(scoring_team_cursor);
3588: DBMS_XMLGEN.SetRowSetTag(queryCtx, 'SCORING_TEAMS');
3589: DBMS_XMLGEN.SetRowTag(queryCtx, 'SCORING_TEAMS_ROW');
3590: DBMS_XMLGEN.getXMLType(queryCtx, scoring_team_res, DBMS_XMLGEN.NONE);
3591: IF scoring_team_res IS NULL THEN
3592: scoring_team_res := XMLType('
3593: END IF;
3586: BEGIN
3587: queryCtx := DBMS_XMLGEN.newContext(scoring_team_cursor);
3588: DBMS_XMLGEN.SetRowSetTag(queryCtx, 'SCORING_TEAMS');
3589: DBMS_XMLGEN.SetRowTag(queryCtx, 'SCORING_TEAMS_ROW');
3590: DBMS_XMLGEN.getXMLType(queryCtx, scoring_team_res, DBMS_XMLGEN.NONE);
3591: IF scoring_team_res IS NULL THEN
3592: scoring_team_res := XMLType('
3593: END IF;
3594: Dbms_Lob.append(xml_clob, scoring_team_res.getCLOBVal());
3591: IF scoring_team_res IS NULL THEN
3592: scoring_team_res := XMLType('
3593: END IF;
3594: Dbms_Lob.append(xml_clob, scoring_team_res.getCLOBVal());
3595: DBMS_XMLGEN.closeContext (queryCtx);
3596: exception when others then
3597: DBMS_XMLGEN.closeContext (queryCtx);
3598: RAISE;
3599: END;
3593: END IF;
3594: Dbms_Lob.append(xml_clob, scoring_team_res.getCLOBVal());
3595: DBMS_XMLGEN.closeContext (queryCtx);
3596: exception when others then
3597: DBMS_XMLGEN.closeContext (queryCtx);
3598: RAISE;
3599: END;
3600:
3601: CLOSE scoring_team_cursor;
3617: AND pah.has_scoring_teams_flag = 'Y' -- members present only if teams present
3618: AND per.effective_end_date = (select max(pp.effective_end_date) from per_all_people_f pp where pp.person_id = per.person_id);
3619:
3620: BEGIN
3621: queryCtx := DBMS_XMLGEN.newContext(scoring_mems_cursor);
3622: DBMS_XMLGEN.SetRowSetTag(queryCtx, 'SCORING_TEAM_MEMBERS');
3623: DBMS_XMLGEN.SetRowTag(queryCtx, 'SCORING_TEAM_MEMBERS_ROW');
3624: DBMS_XMLGEN.getXMLType(queryCtx, scoring_mems_res, DBMS_XMLGEN.NONE);
3625: IF scoring_mems_res IS NULL THEN
3618: AND per.effective_end_date = (select max(pp.effective_end_date) from per_all_people_f pp where pp.person_id = per.person_id);
3619:
3620: BEGIN
3621: queryCtx := DBMS_XMLGEN.newContext(scoring_mems_cursor);
3622: DBMS_XMLGEN.SetRowSetTag(queryCtx, 'SCORING_TEAM_MEMBERS');
3623: DBMS_XMLGEN.SetRowTag(queryCtx, 'SCORING_TEAM_MEMBERS_ROW');
3624: DBMS_XMLGEN.getXMLType(queryCtx, scoring_mems_res, DBMS_XMLGEN.NONE);
3625: IF scoring_mems_res IS NULL THEN
3626: scoring_mems_res := XMLType('
3619:
3620: BEGIN
3621: queryCtx := DBMS_XMLGEN.newContext(scoring_mems_cursor);
3622: DBMS_XMLGEN.SetRowSetTag(queryCtx, 'SCORING_TEAM_MEMBERS');
3623: DBMS_XMLGEN.SetRowTag(queryCtx, 'SCORING_TEAM_MEMBERS_ROW');
3624: DBMS_XMLGEN.getXMLType(queryCtx, scoring_mems_res, DBMS_XMLGEN.NONE);
3625: IF scoring_mems_res IS NULL THEN
3626: scoring_mems_res := XMLType('
3627: END IF;
3620: BEGIN
3621: queryCtx := DBMS_XMLGEN.newContext(scoring_mems_cursor);
3622: DBMS_XMLGEN.SetRowSetTag(queryCtx, 'SCORING_TEAM_MEMBERS');
3623: DBMS_XMLGEN.SetRowTag(queryCtx, 'SCORING_TEAM_MEMBERS_ROW');
3624: DBMS_XMLGEN.getXMLType(queryCtx, scoring_mems_res, DBMS_XMLGEN.NONE);
3625: IF scoring_mems_res IS NULL THEN
3626: scoring_mems_res := XMLType('
3627: END IF;
3628: Dbms_Lob.append(xml_clob, scoring_mems_res.getCLOBVal());
3625: IF scoring_mems_res IS NULL THEN
3626: scoring_mems_res := XMLType('
3627: END IF;
3628: Dbms_Lob.append(xml_clob, scoring_mems_res.getCLOBVal());
3629: DBMS_XMLGEN.closeContext (queryCtx);
3630: exception when others then
3631: DBMS_XMLGEN.closeContext (queryCtx);
3632: RAISE;
3633: END;
3627: END IF;
3628: Dbms_Lob.append(xml_clob, scoring_mems_res.getCLOBVal());
3629: DBMS_XMLGEN.closeContext (queryCtx);
3630: exception when others then
3631: DBMS_XMLGEN.closeContext (queryCtx);
3632: RAISE;
3633: END;
3634:
3635: CLOSE scoring_mems_cursor;
3650: AND pah.has_scoring_teams_flag = 'Y'; -- sections present only if teams present
3651:
3652:
3653: BEGIN
3654: queryCtx := DBMS_XMLGEN.newContext(scoring_secs_cursor);
3655: DBMS_XMLGEN.SetRowSetTag(queryCtx, 'SCORING_TEAM_SECTIONS');
3656: DBMS_XMLGEN.SetRowTag(queryCtx, 'SCORING_TEAM_SECTIONS_ROW');
3657: DBMS_XMLGEN.getXMLType(queryCtx, scoring_secs_res, DBMS_XMLGEN.NONE);
3658: IF scoring_secs_res IS NULL THEN
3651:
3652:
3653: BEGIN
3654: queryCtx := DBMS_XMLGEN.newContext(scoring_secs_cursor);
3655: DBMS_XMLGEN.SetRowSetTag(queryCtx, 'SCORING_TEAM_SECTIONS');
3656: DBMS_XMLGEN.SetRowTag(queryCtx, 'SCORING_TEAM_SECTIONS_ROW');
3657: DBMS_XMLGEN.getXMLType(queryCtx, scoring_secs_res, DBMS_XMLGEN.NONE);
3658: IF scoring_secs_res IS NULL THEN
3659: scoring_secs_res := XMLType('
3652:
3653: BEGIN
3654: queryCtx := DBMS_XMLGEN.newContext(scoring_secs_cursor);
3655: DBMS_XMLGEN.SetRowSetTag(queryCtx, 'SCORING_TEAM_SECTIONS');
3656: DBMS_XMLGEN.SetRowTag(queryCtx, 'SCORING_TEAM_SECTIONS_ROW');
3657: DBMS_XMLGEN.getXMLType(queryCtx, scoring_secs_res, DBMS_XMLGEN.NONE);
3658: IF scoring_secs_res IS NULL THEN
3659: scoring_secs_res := XMLType('
3660: END IF;
3653: BEGIN
3654: queryCtx := DBMS_XMLGEN.newContext(scoring_secs_cursor);
3655: DBMS_XMLGEN.SetRowSetTag(queryCtx, 'SCORING_TEAM_SECTIONS');
3656: DBMS_XMLGEN.SetRowTag(queryCtx, 'SCORING_TEAM_SECTIONS_ROW');
3657: DBMS_XMLGEN.getXMLType(queryCtx, scoring_secs_res, DBMS_XMLGEN.NONE);
3658: IF scoring_secs_res IS NULL THEN
3659: scoring_secs_res := XMLType('
3660: END IF;
3661: Dbms_Lob.append(xml_clob, scoring_secs_res.getCLOBVal());
3658: IF scoring_secs_res IS NULL THEN
3659: scoring_secs_res := XMLType('
3660: END IF;
3661: Dbms_Lob.append(xml_clob, scoring_secs_res.getCLOBVal());
3662: DBMS_XMLGEN.closeContext (queryCtx);
3663: exception when others then
3664: DBMS_XMLGEN.closeContext (queryCtx);
3665: RAISE;
3666: END;
3660: END IF;
3661: Dbms_Lob.append(xml_clob, scoring_secs_res.getCLOBVal());
3662: DBMS_XMLGEN.closeContext (queryCtx);
3663: exception when others then
3664: DBMS_XMLGEN.closeContext (queryCtx);
3665: RAISE;
3666: END;
3667:
3668: CLOSE scoring_secs_cursor;
3687: AND forms.form_id = forms_tl.form_id
3688: ORDER BY form_name;
3689:
3690: BEGIN
3691: queryCtx := DBMS_XMLGEN.newContext(abstracts_cursor);
3692: DBMS_XMLGEN.SetRowSetTag(queryCtx, 'ABSTRACT_AND_FORMS');
3693: DBMS_XMLGEN.SetRowTag(queryCtx, 'ABSTRACT_AND_FORMS_ROW');
3694: DBMS_XMLGEN.getXMLType(queryCtx, abstracts_res, DBMS_XMLGEN.NONE);
3695: IF abstracts_res IS NULL THEN
3688: ORDER BY form_name;
3689:
3690: BEGIN
3691: queryCtx := DBMS_XMLGEN.newContext(abstracts_cursor);
3692: DBMS_XMLGEN.SetRowSetTag(queryCtx, 'ABSTRACT_AND_FORMS');
3693: DBMS_XMLGEN.SetRowTag(queryCtx, 'ABSTRACT_AND_FORMS_ROW');
3694: DBMS_XMLGEN.getXMLType(queryCtx, abstracts_res, DBMS_XMLGEN.NONE);
3695: IF abstracts_res IS NULL THEN
3696: abstracts_res := XMLType('
3689:
3690: BEGIN
3691: queryCtx := DBMS_XMLGEN.newContext(abstracts_cursor);
3692: DBMS_XMLGEN.SetRowSetTag(queryCtx, 'ABSTRACT_AND_FORMS');
3693: DBMS_XMLGEN.SetRowTag(queryCtx, 'ABSTRACT_AND_FORMS_ROW');
3694: DBMS_XMLGEN.getXMLType(queryCtx, abstracts_res, DBMS_XMLGEN.NONE);
3695: IF abstracts_res IS NULL THEN
3696: abstracts_res := XMLType('
3697: END IF;
3690: BEGIN
3691: queryCtx := DBMS_XMLGEN.newContext(abstracts_cursor);
3692: DBMS_XMLGEN.SetRowSetTag(queryCtx, 'ABSTRACT_AND_FORMS');
3693: DBMS_XMLGEN.SetRowTag(queryCtx, 'ABSTRACT_AND_FORMS_ROW');
3694: DBMS_XMLGEN.getXMLType(queryCtx, abstracts_res, DBMS_XMLGEN.NONE);
3695: IF abstracts_res IS NULL THEN
3696: abstracts_res := XMLType('
3697: END IF;
3698: Dbms_Lob.append(xml_clob, abstracts_res.getCLOBVal());
3695: IF abstracts_res IS NULL THEN
3696: abstracts_res := XMLType('
3697: END IF;
3698: Dbms_Lob.append(xml_clob, abstracts_res.getCLOBVal());
3699: DBMS_XMLGEN.closeContext (queryCtx);
3700: exception when others then
3701: DBMS_XMLGEN.closeContext (queryCtx);
3702: RAISE;
3703: END;
3697: END IF;
3698: Dbms_Lob.append(xml_clob, abstracts_res.getCLOBVal());
3699: DBMS_XMLGEN.closeContext (queryCtx);
3700: exception when others then
3701: DBMS_XMLGEN.closeContext (queryCtx);
3702: RAISE;
3703: END;
3704:
3705: CLOSE abstracts_cursor;
3723: and ftl.currency_code = pacr.bid_currency_code
3724: and ftl.language = l_printing_language;
3725:
3726: BEGIN
3727: queryCtx := DBMS_XMLGEN.newContext(currency_cursor);
3728: DBMS_XMLGEN.SetRowSetTag(queryCtx, 'CURRENCY');
3729: DBMS_XMLGEN.SetRowTag(queryCtx, 'CURRENCY_ROW');
3730: DBMS_XMLGEN.getXMLType(queryCtx, currency_res, DBMS_XMLGEN.NONE);
3731: IF currency_res IS NULL THEN
3724: and ftl.language = l_printing_language;
3725:
3726: BEGIN
3727: queryCtx := DBMS_XMLGEN.newContext(currency_cursor);
3728: DBMS_XMLGEN.SetRowSetTag(queryCtx, 'CURRENCY');
3729: DBMS_XMLGEN.SetRowTag(queryCtx, 'CURRENCY_ROW');
3730: DBMS_XMLGEN.getXMLType(queryCtx, currency_res, DBMS_XMLGEN.NONE);
3731: IF currency_res IS NULL THEN
3732: currency_res := XMLType('
3725:
3726: BEGIN
3727: queryCtx := DBMS_XMLGEN.newContext(currency_cursor);
3728: DBMS_XMLGEN.SetRowSetTag(queryCtx, 'CURRENCY');
3729: DBMS_XMLGEN.SetRowTag(queryCtx, 'CURRENCY_ROW');
3730: DBMS_XMLGEN.getXMLType(queryCtx, currency_res, DBMS_XMLGEN.NONE);
3731: IF currency_res IS NULL THEN
3732: currency_res := XMLType('
3733: END IF;
3726: BEGIN
3727: queryCtx := DBMS_XMLGEN.newContext(currency_cursor);
3728: DBMS_XMLGEN.SetRowSetTag(queryCtx, 'CURRENCY');
3729: DBMS_XMLGEN.SetRowTag(queryCtx, 'CURRENCY_ROW');
3730: DBMS_XMLGEN.getXMLType(queryCtx, currency_res, DBMS_XMLGEN.NONE);
3731: IF currency_res IS NULL THEN
3732: currency_res := XMLType('
3733: END IF;
3734: Dbms_Lob.append(xml_clob, currency_res.getCLOBVal());
3731: IF currency_res IS NULL THEN
3732: currency_res := XMLType('
3733: END IF;
3734: Dbms_Lob.append(xml_clob, currency_res.getCLOBVal());
3735: DBMS_XMLGEN.closeContext (queryCtx);
3736: exception when others then
3737: DBMS_XMLGEN.closeContext (queryCtx);
3738: RAISE;
3739: END;
3733: END IF;
3734: Dbms_Lob.append(xml_clob, currency_res.getCLOBVal());
3735: DBMS_XMLGEN.closeContext (queryCtx);
3736: exception when others then
3737: DBMS_XMLGEN.closeContext (queryCtx);
3738: RAISE;
3739: END;
3740:
3741: CLOSE currency_cursor;
3764: ORDER BY sequence ASC;
3765: --) AS INVITED_SUPPLIER_CURRENCY,
3766:
3767: BEGIN
3768: queryCtx := DBMS_XMLGEN.newContext(invited_supp_cur_cursor);
3769: DBMS_XMLGEN.SetRowSetTag(queryCtx, 'INVITED_SUPPLIER_CURRENCY');
3770: DBMS_XMLGEN.SetRowTag(queryCtx, 'INVITED_SUPPLIER_CURRENCY_ROW');
3771: DBMS_XMLGEN.getXMLType(queryCtx, invited_supp_cur_res, DBMS_XMLGEN.NONE);
3772: IF invited_supp_cur_res IS NULL THEN
3765: --) AS INVITED_SUPPLIER_CURRENCY,
3766:
3767: BEGIN
3768: queryCtx := DBMS_XMLGEN.newContext(invited_supp_cur_cursor);
3769: DBMS_XMLGEN.SetRowSetTag(queryCtx, 'INVITED_SUPPLIER_CURRENCY');
3770: DBMS_XMLGEN.SetRowTag(queryCtx, 'INVITED_SUPPLIER_CURRENCY_ROW');
3771: DBMS_XMLGEN.getXMLType(queryCtx, invited_supp_cur_res, DBMS_XMLGEN.NONE);
3772: IF invited_supp_cur_res IS NULL THEN
3773: invited_supp_cur_res := XMLType('
3766:
3767: BEGIN
3768: queryCtx := DBMS_XMLGEN.newContext(invited_supp_cur_cursor);
3769: DBMS_XMLGEN.SetRowSetTag(queryCtx, 'INVITED_SUPPLIER_CURRENCY');
3770: DBMS_XMLGEN.SetRowTag(queryCtx, 'INVITED_SUPPLIER_CURRENCY_ROW');
3771: DBMS_XMLGEN.getXMLType(queryCtx, invited_supp_cur_res, DBMS_XMLGEN.NONE);
3772: IF invited_supp_cur_res IS NULL THEN
3773: invited_supp_cur_res := XMLType('
3774: END IF;
3767: BEGIN
3768: queryCtx := DBMS_XMLGEN.newContext(invited_supp_cur_cursor);
3769: DBMS_XMLGEN.SetRowSetTag(queryCtx, 'INVITED_SUPPLIER_CURRENCY');
3770: DBMS_XMLGEN.SetRowTag(queryCtx, 'INVITED_SUPPLIER_CURRENCY_ROW');
3771: DBMS_XMLGEN.getXMLType(queryCtx, invited_supp_cur_res, DBMS_XMLGEN.NONE);
3772: IF invited_supp_cur_res IS NULL THEN
3773: invited_supp_cur_res := XMLType('
3774: END IF;
3775: Dbms_Lob.append(xml_clob, invited_supp_cur_res.getCLOBVal());
3772: IF invited_supp_cur_res IS NULL THEN
3773: invited_supp_cur_res := XMLType('
3774: END IF;
3775: Dbms_Lob.append(xml_clob, invited_supp_cur_res.getCLOBVal());
3776: DBMS_XMLGEN.closeContext (queryCtx);
3777: exception when others then
3778: DBMS_XMLGEN.closeContext (queryCtx);
3779: RAISE;
3780: END;
3774: END IF;
3775: Dbms_Lob.append(xml_clob, invited_supp_cur_res.getCLOBVal());
3776: DBMS_XMLGEN.closeContext (queryCtx);
3777: exception when others then
3778: DBMS_XMLGEN.closeContext (queryCtx);
3779: RAISE;
3780: END;
3781:
3782: CLOSE invited_supp_cur_cursor;
3873: group by(pass.section_name,paa.auction_header_id,paa.section_name,pass.auction_header_id,pass.attr_group_seq_number, two_part_section_type)
3874: order by pass.attr_group_seq_number;
3875:
3876: BEGIN
3877: queryCtx := DBMS_XMLGEN.newContext(header_attr_cursor);
3878: DBMS_XMLGEN.SetRowSetTag(queryCtx, 'GROUP_HEADER_ATTRIBUTES');
3879: DBMS_XMLGEN.SetRowTag(queryCtx, 'GROUP_HEADER_ATTRIBUTES_ROW');
3880: DBMS_XMLGEN.getXMLType(queryCtx, header_attr_res, DBMS_XMLGEN.NONE);
3881: IF header_attr_res IS NULL THEN
3874: order by pass.attr_group_seq_number;
3875:
3876: BEGIN
3877: queryCtx := DBMS_XMLGEN.newContext(header_attr_cursor);
3878: DBMS_XMLGEN.SetRowSetTag(queryCtx, 'GROUP_HEADER_ATTRIBUTES');
3879: DBMS_XMLGEN.SetRowTag(queryCtx, 'GROUP_HEADER_ATTRIBUTES_ROW');
3880: DBMS_XMLGEN.getXMLType(queryCtx, header_attr_res, DBMS_XMLGEN.NONE);
3881: IF header_attr_res IS NULL THEN
3882: header_attr_res := XMLType('
3875:
3876: BEGIN
3877: queryCtx := DBMS_XMLGEN.newContext(header_attr_cursor);
3878: DBMS_XMLGEN.SetRowSetTag(queryCtx, 'GROUP_HEADER_ATTRIBUTES');
3879: DBMS_XMLGEN.SetRowTag(queryCtx, 'GROUP_HEADER_ATTRIBUTES_ROW');
3880: DBMS_XMLGEN.getXMLType(queryCtx, header_attr_res, DBMS_XMLGEN.NONE);
3881: IF header_attr_res IS NULL THEN
3882: header_attr_res := XMLType('
3883: END IF;
3876: BEGIN
3877: queryCtx := DBMS_XMLGEN.newContext(header_attr_cursor);
3878: DBMS_XMLGEN.SetRowSetTag(queryCtx, 'GROUP_HEADER_ATTRIBUTES');
3879: DBMS_XMLGEN.SetRowTag(queryCtx, 'GROUP_HEADER_ATTRIBUTES_ROW');
3880: DBMS_XMLGEN.getXMLType(queryCtx, header_attr_res, DBMS_XMLGEN.NONE);
3881: IF header_attr_res IS NULL THEN
3882: header_attr_res := XMLType('
3883: END IF;
3884: Dbms_Lob.append(xml_clob, header_attr_res.getCLOBVal());
3881: IF header_attr_res IS NULL THEN
3882: header_attr_res := XMLType('
3883: END IF;
3884: Dbms_Lob.append(xml_clob, header_attr_res.getCLOBVal());
3885: DBMS_XMLGEN.closeContext (queryCtx);
3886: exception when others then
3887: DBMS_XMLGEN.closeContext (queryCtx);
3888: RAISE;
3889: END;
3883: END IF;
3884: Dbms_Lob.append(xml_clob, header_attr_res.getCLOBVal());
3885: DBMS_XMLGEN.closeContext (queryCtx);
3886: exception when others then
3887: DBMS_XMLGEN.closeContext (queryCtx);
3888: RAISE;
3889: END;
3890:
3891: CLOSE header_attr_cursor;
3912: AND l_is_buyer_negpdf = 'Y'
3913: ORDER BY sequence ASC;
3914:
3915: BEGIN
3916: queryCtx := DBMS_XMLGEN.newContext(invited_supp_cursor);
3917: DBMS_XMLGEN.SetRowSetTag(queryCtx, 'INVITED_SUPPLIERS');
3918: DBMS_XMLGEN.SetRowTag(queryCtx, 'INVITED_SUPPLIERS_ROW');
3919: DBMS_XMLGEN.getXMLType(queryCtx, invited_supp_res, DBMS_XMLGEN.NONE);
3920: IF invited_supp_res IS NULL THEN
3913: ORDER BY sequence ASC;
3914:
3915: BEGIN
3916: queryCtx := DBMS_XMLGEN.newContext(invited_supp_cursor);
3917: DBMS_XMLGEN.SetRowSetTag(queryCtx, 'INVITED_SUPPLIERS');
3918: DBMS_XMLGEN.SetRowTag(queryCtx, 'INVITED_SUPPLIERS_ROW');
3919: DBMS_XMLGEN.getXMLType(queryCtx, invited_supp_res, DBMS_XMLGEN.NONE);
3920: IF invited_supp_res IS NULL THEN
3921: invited_supp_res := XMLType('
3914:
3915: BEGIN
3916: queryCtx := DBMS_XMLGEN.newContext(invited_supp_cursor);
3917: DBMS_XMLGEN.SetRowSetTag(queryCtx, 'INVITED_SUPPLIERS');
3918: DBMS_XMLGEN.SetRowTag(queryCtx, 'INVITED_SUPPLIERS_ROW');
3919: DBMS_XMLGEN.getXMLType(queryCtx, invited_supp_res, DBMS_XMLGEN.NONE);
3920: IF invited_supp_res IS NULL THEN
3921: invited_supp_res := XMLType('
3922: END IF;
3915: BEGIN
3916: queryCtx := DBMS_XMLGEN.newContext(invited_supp_cursor);
3917: DBMS_XMLGEN.SetRowSetTag(queryCtx, 'INVITED_SUPPLIERS');
3918: DBMS_XMLGEN.SetRowTag(queryCtx, 'INVITED_SUPPLIERS_ROW');
3919: DBMS_XMLGEN.getXMLType(queryCtx, invited_supp_res, DBMS_XMLGEN.NONE);
3920: IF invited_supp_res IS NULL THEN
3921: invited_supp_res := XMLType('
3922: END IF;
3923: Dbms_Lob.append(xml_clob, invited_supp_res.getCLOBVal());
3920: IF invited_supp_res IS NULL THEN
3921: invited_supp_res := XMLType('
3922: END IF;
3923: Dbms_Lob.append(xml_clob, invited_supp_res.getCLOBVal());
3924: DBMS_XMLGEN.closeContext (queryCtx);
3925: exception when others then
3926: DBMS_XMLGEN.closeContext (queryCtx);
3927: RAISE;
3928: END;
3922: END IF;
3923: Dbms_Lob.append(xml_clob, invited_supp_res.getCLOBVal());
3924: DBMS_XMLGEN.closeContext (queryCtx);
3925: exception when others then
3926: DBMS_XMLGEN.closeContext (queryCtx);
3927: RAISE;
3928: END;
3929:
3930: CLOSE invited_supp_cursor;
3954: AND flv2.view_application_id = 0
3955: AND flv2.security_group_id = 0;
3956:
3957: BEGIN
3958: queryCtx := DBMS_XMLGEN.newContext(line_pf_cursor);
3959: DBMS_XMLGEN.SetRowSetTag(queryCtx, 'LINE_PRICE_PF_DETAILS');
3960: DBMS_XMLGEN.SetRowTag(queryCtx, 'LINE_PRICE_PF_DETAILS_ROW');
3961: DBMS_XMLGEN.getXMLType(queryCtx, line_pf_res, DBMS_XMLGEN.NONE);
3962: IF line_pf_res IS NULL THEN
3955: AND flv2.security_group_id = 0;
3956:
3957: BEGIN
3958: queryCtx := DBMS_XMLGEN.newContext(line_pf_cursor);
3959: DBMS_XMLGEN.SetRowSetTag(queryCtx, 'LINE_PRICE_PF_DETAILS');
3960: DBMS_XMLGEN.SetRowTag(queryCtx, 'LINE_PRICE_PF_DETAILS_ROW');
3961: DBMS_XMLGEN.getXMLType(queryCtx, line_pf_res, DBMS_XMLGEN.NONE);
3962: IF line_pf_res IS NULL THEN
3963: line_pf_res := XMLType('
3956:
3957: BEGIN
3958: queryCtx := DBMS_XMLGEN.newContext(line_pf_cursor);
3959: DBMS_XMLGEN.SetRowSetTag(queryCtx, 'LINE_PRICE_PF_DETAILS');
3960: DBMS_XMLGEN.SetRowTag(queryCtx, 'LINE_PRICE_PF_DETAILS_ROW');
3961: DBMS_XMLGEN.getXMLType(queryCtx, line_pf_res, DBMS_XMLGEN.NONE);
3962: IF line_pf_res IS NULL THEN
3963: line_pf_res := XMLType('
3964: END IF;
3957: BEGIN
3958: queryCtx := DBMS_XMLGEN.newContext(line_pf_cursor);
3959: DBMS_XMLGEN.SetRowSetTag(queryCtx, 'LINE_PRICE_PF_DETAILS');
3960: DBMS_XMLGEN.SetRowTag(queryCtx, 'LINE_PRICE_PF_DETAILS_ROW');
3961: DBMS_XMLGEN.getXMLType(queryCtx, line_pf_res, DBMS_XMLGEN.NONE);
3962: IF line_pf_res IS NULL THEN
3963: line_pf_res := XMLType('
3964: END IF;
3965: Dbms_Lob.append(xml_clob, line_pf_res.getCLOBVal());
3962: IF line_pf_res IS NULL THEN
3963: line_pf_res := XMLType('
3964: END IF;
3965: Dbms_Lob.append(xml_clob, line_pf_res.getCLOBVal());
3966: DBMS_XMLGEN.closeContext (queryCtx);
3967: exception when others then
3968: DBMS_XMLGEN.closeContext (queryCtx);
3969: RAISE;
3970: END;
3964: END IF;
3965: Dbms_Lob.append(xml_clob, line_pf_res.getCLOBVal());
3966: DBMS_XMLGEN.closeContext (queryCtx);
3967: exception when others then
3968: DBMS_XMLGEN.closeContext (queryCtx);
3969: RAISE;
3970: END;
3971:
3972: CLOSE line_pf_cursor;
3995: AND pf_values.pf_seq_number = pfs.sequence_number
3996: ORDER BY pf_values.supplier_seq_number, pf_values.line_number, pf_values.pf_seq_number;
3997:
3998: BEGIN
3999: queryCtx := DBMS_XMLGEN.newContext(buyer_pf_cursor);
4000: DBMS_XMLGEN.SetRowSetTag(queryCtx, 'BUYER_PF_VALUES');
4001: DBMS_XMLGEN.SetRowTag(queryCtx, 'BUYER_PF_VALUES_ROW');
4002: DBMS_XMLGEN.getXMLType(queryCtx, buyer_pf_res, DBMS_XMLGEN.NONE);
4003: IF buyer_pf_res IS NULL THEN
3996: ORDER BY pf_values.supplier_seq_number, pf_values.line_number, pf_values.pf_seq_number;
3997:
3998: BEGIN
3999: queryCtx := DBMS_XMLGEN.newContext(buyer_pf_cursor);
4000: DBMS_XMLGEN.SetRowSetTag(queryCtx, 'BUYER_PF_VALUES');
4001: DBMS_XMLGEN.SetRowTag(queryCtx, 'BUYER_PF_VALUES_ROW');
4002: DBMS_XMLGEN.getXMLType(queryCtx, buyer_pf_res, DBMS_XMLGEN.NONE);
4003: IF buyer_pf_res IS NULL THEN
4004: buyer_pf_res := XMLType('
3997:
3998: BEGIN
3999: queryCtx := DBMS_XMLGEN.newContext(buyer_pf_cursor);
4000: DBMS_XMLGEN.SetRowSetTag(queryCtx, 'BUYER_PF_VALUES');
4001: DBMS_XMLGEN.SetRowTag(queryCtx, 'BUYER_PF_VALUES_ROW');
4002: DBMS_XMLGEN.getXMLType(queryCtx, buyer_pf_res, DBMS_XMLGEN.NONE);
4003: IF buyer_pf_res IS NULL THEN
4004: buyer_pf_res := XMLType('
4005: END IF;
3998: BEGIN
3999: queryCtx := DBMS_XMLGEN.newContext(buyer_pf_cursor);
4000: DBMS_XMLGEN.SetRowSetTag(queryCtx, 'BUYER_PF_VALUES');
4001: DBMS_XMLGEN.SetRowTag(queryCtx, 'BUYER_PF_VALUES_ROW');
4002: DBMS_XMLGEN.getXMLType(queryCtx, buyer_pf_res, DBMS_XMLGEN.NONE);
4003: IF buyer_pf_res IS NULL THEN
4004: buyer_pf_res := XMLType('
4005: END IF;
4006: Dbms_Lob.append(xml_clob, buyer_pf_res.getCLOBVal());
4003: IF buyer_pf_res IS NULL THEN
4004: buyer_pf_res := XMLType('
4005: END IF;
4006: Dbms_Lob.append(xml_clob, buyer_pf_res.getCLOBVal());
4007: DBMS_XMLGEN.closeContext (queryCtx);
4008: exception when others then
4009: DBMS_XMLGEN.closeContext (queryCtx);
4010: RAISE;
4011: END;
4005: END IF;
4006: Dbms_Lob.append(xml_clob, buyer_pf_res.getCLOBVal());
4007: DBMS_XMLGEN.closeContext (queryCtx);
4008: exception when others then
4009: DBMS_XMLGEN.closeContext (queryCtx);
4010: RAISE;
4011: END;
4012:
4013: CLOSE buyer_pf_cursor;
4036: AND fl.lookup_type = 'PON_PRICING_BASIS'
4037: ORDER BY name, pricing_basis_meaning;
4038:
4039: BEGIN
4040: queryCtx := DBMS_XMLGEN.newContext(dist_buyer_pf_cursor);
4041: DBMS_XMLGEN.SetRowSetTag(queryCtx, 'DISTINCT_BUYER_PFS');
4042: DBMS_XMLGEN.SetRowTag(queryCtx, 'DISTINCT_BUYER_PFS_ROW');
4043: DBMS_XMLGEN.getXMLType(queryCtx, dist_buyer_pf_res, DBMS_XMLGEN.NONE);
4044: IF dist_buyer_pf_res IS NULL THEN
4037: ORDER BY name, pricing_basis_meaning;
4038:
4039: BEGIN
4040: queryCtx := DBMS_XMLGEN.newContext(dist_buyer_pf_cursor);
4041: DBMS_XMLGEN.SetRowSetTag(queryCtx, 'DISTINCT_BUYER_PFS');
4042: DBMS_XMLGEN.SetRowTag(queryCtx, 'DISTINCT_BUYER_PFS_ROW');
4043: DBMS_XMLGEN.getXMLType(queryCtx, dist_buyer_pf_res, DBMS_XMLGEN.NONE);
4044: IF dist_buyer_pf_res IS NULL THEN
4045: dist_buyer_pf_res := XMLType('
4038:
4039: BEGIN
4040: queryCtx := DBMS_XMLGEN.newContext(dist_buyer_pf_cursor);
4041: DBMS_XMLGEN.SetRowSetTag(queryCtx, 'DISTINCT_BUYER_PFS');
4042: DBMS_XMLGEN.SetRowTag(queryCtx, 'DISTINCT_BUYER_PFS_ROW');
4043: DBMS_XMLGEN.getXMLType(queryCtx, dist_buyer_pf_res, DBMS_XMLGEN.NONE);
4044: IF dist_buyer_pf_res IS NULL THEN
4045: dist_buyer_pf_res := XMLType('
4046: END IF;
4039: BEGIN
4040: queryCtx := DBMS_XMLGEN.newContext(dist_buyer_pf_cursor);
4041: DBMS_XMLGEN.SetRowSetTag(queryCtx, 'DISTINCT_BUYER_PFS');
4042: DBMS_XMLGEN.SetRowTag(queryCtx, 'DISTINCT_BUYER_PFS_ROW');
4043: DBMS_XMLGEN.getXMLType(queryCtx, dist_buyer_pf_res, DBMS_XMLGEN.NONE);
4044: IF dist_buyer_pf_res IS NULL THEN
4045: dist_buyer_pf_res := XMLType('
4046: END IF;
4047: Dbms_Lob.append(xml_clob, dist_buyer_pf_res.getCLOBVal());
4044: IF dist_buyer_pf_res IS NULL THEN
4045: dist_buyer_pf_res := XMLType('
4046: END IF;
4047: Dbms_Lob.append(xml_clob, dist_buyer_pf_res.getCLOBVal());
4048: DBMS_XMLGEN.closeContext (queryCtx);
4049: exception when others then
4050: DBMS_XMLGEN.closeContext (queryCtx);
4051: RAISE;
4052: END;
4046: END IF;
4047: Dbms_Lob.append(xml_clob, dist_buyer_pf_res.getCLOBVal());
4048: DBMS_XMLGEN.closeContext (queryCtx);
4049: exception when others then
4050: DBMS_XMLGEN.closeContext (queryCtx);
4051: RAISE;
4052: END;
4053:
4054: CLOSE dist_buyer_pf_cursor;
4074: AND pon_large_neg_pf_values.value is not null
4075: order by pon_large_neg_pf_values.supplier_seq_number,pf_name;
4076:
4077: BEGIN
4078: queryCtx := DBMS_XMLGEN.newContext(large_neg_bur_pf_cursor);
4079: DBMS_XMLGEN.SetRowSetTag(queryCtx, 'LARGE_NEG_BUYER_PF_VALUES');
4080: DBMS_XMLGEN.SetRowTag(queryCtx, 'LARGE_NEG_BUYER_PF_VALUES_ROW');
4081: DBMS_XMLGEN.getXMLType(queryCtx, large_neg_bur_pf_res, DBMS_XMLGEN.NONE);
4082: IF large_neg_bur_pf_res IS NULL THEN
4075: order by pon_large_neg_pf_values.supplier_seq_number,pf_name;
4076:
4077: BEGIN
4078: queryCtx := DBMS_XMLGEN.newContext(large_neg_bur_pf_cursor);
4079: DBMS_XMLGEN.SetRowSetTag(queryCtx, 'LARGE_NEG_BUYER_PF_VALUES');
4080: DBMS_XMLGEN.SetRowTag(queryCtx, 'LARGE_NEG_BUYER_PF_VALUES_ROW');
4081: DBMS_XMLGEN.getXMLType(queryCtx, large_neg_bur_pf_res, DBMS_XMLGEN.NONE);
4082: IF large_neg_bur_pf_res IS NULL THEN
4083: large_neg_bur_pf_res := XMLType('
4076:
4077: BEGIN
4078: queryCtx := DBMS_XMLGEN.newContext(large_neg_bur_pf_cursor);
4079: DBMS_XMLGEN.SetRowSetTag(queryCtx, 'LARGE_NEG_BUYER_PF_VALUES');
4080: DBMS_XMLGEN.SetRowTag(queryCtx, 'LARGE_NEG_BUYER_PF_VALUES_ROW');
4081: DBMS_XMLGEN.getXMLType(queryCtx, large_neg_bur_pf_res, DBMS_XMLGEN.NONE);
4082: IF large_neg_bur_pf_res IS NULL THEN
4083: large_neg_bur_pf_res := XMLType('
4084: END IF;
4077: BEGIN
4078: queryCtx := DBMS_XMLGEN.newContext(large_neg_bur_pf_cursor);
4079: DBMS_XMLGEN.SetRowSetTag(queryCtx, 'LARGE_NEG_BUYER_PF_VALUES');
4080: DBMS_XMLGEN.SetRowTag(queryCtx, 'LARGE_NEG_BUYER_PF_VALUES_ROW');
4081: DBMS_XMLGEN.getXMLType(queryCtx, large_neg_bur_pf_res, DBMS_XMLGEN.NONE);
4082: IF large_neg_bur_pf_res IS NULL THEN
4083: large_neg_bur_pf_res := XMLType('
4084: END IF;
4085: Dbms_Lob.append(xml_clob, large_neg_bur_pf_res.getCLOBVal());
4082: IF large_neg_bur_pf_res IS NULL THEN
4083: large_neg_bur_pf_res := XMLType('
4084: END IF;
4085: Dbms_Lob.append(xml_clob, large_neg_bur_pf_res.getCLOBVal());
4086: DBMS_XMLGEN.closeContext (queryCtx);
4087: exception when others then
4088: DBMS_XMLGEN.closeContext (queryCtx);
4089: RAISE;
4090: END;
4084: END IF;
4085: Dbms_Lob.append(xml_clob, large_neg_bur_pf_res.getCLOBVal());
4086: DBMS_XMLGEN.closeContext (queryCtx);
4087: exception when others then
4088: DBMS_XMLGEN.closeContext (queryCtx);
4089: RAISE;
4090: END;
4091:
4092: CLOSE large_neg_bur_pf_cursor;
4170: order
4171: by name;
4172:
4173: BEGIN
4174: queryCtx := DBMS_XMLGEN.newContext(pb_loc_cursor);
4175: DBMS_XMLGEN.SetRowSetTag(queryCtx, 'PRICE_BREAK_LOCATIONS');
4176: DBMS_XMLGEN.SetRowTag(queryCtx, 'PRICE_BREAK_LOCATIONS_ROW');
4177: DBMS_XMLGEN.getXMLType(queryCtx, pb_loc_res, DBMS_XMLGEN.NONE);
4178: IF pb_loc_res IS NULL THEN
4171: by name;
4172:
4173: BEGIN
4174: queryCtx := DBMS_XMLGEN.newContext(pb_loc_cursor);
4175: DBMS_XMLGEN.SetRowSetTag(queryCtx, 'PRICE_BREAK_LOCATIONS');
4176: DBMS_XMLGEN.SetRowTag(queryCtx, 'PRICE_BREAK_LOCATIONS_ROW');
4177: DBMS_XMLGEN.getXMLType(queryCtx, pb_loc_res, DBMS_XMLGEN.NONE);
4178: IF pb_loc_res IS NULL THEN
4179: pb_loc_res := XMLType('
4172:
4173: BEGIN
4174: queryCtx := DBMS_XMLGEN.newContext(pb_loc_cursor);
4175: DBMS_XMLGEN.SetRowSetTag(queryCtx, 'PRICE_BREAK_LOCATIONS');
4176: DBMS_XMLGEN.SetRowTag(queryCtx, 'PRICE_BREAK_LOCATIONS_ROW');
4177: DBMS_XMLGEN.getXMLType(queryCtx, pb_loc_res, DBMS_XMLGEN.NONE);
4178: IF pb_loc_res IS NULL THEN
4179: pb_loc_res := XMLType('
4180: END IF;
4173: BEGIN
4174: queryCtx := DBMS_XMLGEN.newContext(pb_loc_cursor);
4175: DBMS_XMLGEN.SetRowSetTag(queryCtx, 'PRICE_BREAK_LOCATIONS');
4176: DBMS_XMLGEN.SetRowTag(queryCtx, 'PRICE_BREAK_LOCATIONS_ROW');
4177: DBMS_XMLGEN.getXMLType(queryCtx, pb_loc_res, DBMS_XMLGEN.NONE);
4178: IF pb_loc_res IS NULL THEN
4179: pb_loc_res := XMLType('
4180: END IF;
4181: Dbms_Lob.append(xml_clob, pb_loc_res.getCLOBVal());
4178: IF pb_loc_res IS NULL THEN
4179: pb_loc_res := XMLType('
4180: END IF;
4181: Dbms_Lob.append(xml_clob, pb_loc_res.getCLOBVal());
4182: DBMS_XMLGEN.closeContext (queryCtx);
4183: exception when others then
4184: DBMS_XMLGEN.closeContext (queryCtx);
4185: RAISE;
4186: END;
4180: END IF;
4181: Dbms_Lob.append(xml_clob, pb_loc_res.getCLOBVal());
4182: DBMS_XMLGEN.closeContext (queryCtx);
4183: exception when others then
4184: DBMS_XMLGEN.closeContext (queryCtx);
4185: RAISE;
4186: END;
4187:
4188: CLOSE pb_loc_cursor;
4195: pov.price_differential_type
4196: FROM po_price_diff_lookups_v pov;
4197:
4198: BEGIN
4199: queryCtx := DBMS_XMLGEN.newContext(price_diff_types_cursor);
4200: DBMS_XMLGEN.SetRowSetTag(queryCtx, 'PRICE_DIFFERENTIAL_TYPES');
4201: DBMS_XMLGEN.SetRowTag(queryCtx, 'PRICE_DIFFERENTIAL_TYPES_ROW');
4202: DBMS_XMLGEN.getXMLType(queryCtx, price_diff_types_res, DBMS_XMLGEN.NONE);
4203: IF price_diff_types_res IS NULL THEN
4196: FROM po_price_diff_lookups_v pov;
4197:
4198: BEGIN
4199: queryCtx := DBMS_XMLGEN.newContext(price_diff_types_cursor);
4200: DBMS_XMLGEN.SetRowSetTag(queryCtx, 'PRICE_DIFFERENTIAL_TYPES');
4201: DBMS_XMLGEN.SetRowTag(queryCtx, 'PRICE_DIFFERENTIAL_TYPES_ROW');
4202: DBMS_XMLGEN.getXMLType(queryCtx, price_diff_types_res, DBMS_XMLGEN.NONE);
4203: IF price_diff_types_res IS NULL THEN
4204: price_diff_types_res := XMLType('
4197:
4198: BEGIN
4199: queryCtx := DBMS_XMLGEN.newContext(price_diff_types_cursor);
4200: DBMS_XMLGEN.SetRowSetTag(queryCtx, 'PRICE_DIFFERENTIAL_TYPES');
4201: DBMS_XMLGEN.SetRowTag(queryCtx, 'PRICE_DIFFERENTIAL_TYPES_ROW');
4202: DBMS_XMLGEN.getXMLType(queryCtx, price_diff_types_res, DBMS_XMLGEN.NONE);
4203: IF price_diff_types_res IS NULL THEN
4204: price_diff_types_res := XMLType('
4205: END IF;
4198: BEGIN
4199: queryCtx := DBMS_XMLGEN.newContext(price_diff_types_cursor);
4200: DBMS_XMLGEN.SetRowSetTag(queryCtx, 'PRICE_DIFFERENTIAL_TYPES');
4201: DBMS_XMLGEN.SetRowTag(queryCtx, 'PRICE_DIFFERENTIAL_TYPES_ROW');
4202: DBMS_XMLGEN.getXMLType(queryCtx, price_diff_types_res, DBMS_XMLGEN.NONE);
4203: IF price_diff_types_res IS NULL THEN
4204: price_diff_types_res := XMLType('
4205: END IF;
4206: Dbms_Lob.append(xml_clob, price_diff_types_res.getCLOBVal());
4203: IF price_diff_types_res IS NULL THEN
4204: price_diff_types_res := XMLType('
4205: END IF;
4206: Dbms_Lob.append(xml_clob, price_diff_types_res.getCLOBVal());
4207: DBMS_XMLGEN.closeContext (queryCtx);
4208: exception when others then
4209: DBMS_XMLGEN.closeContext (queryCtx);
4210: RAISE;
4211: END;
4205: END IF;
4206: Dbms_Lob.append(xml_clob, price_diff_types_res.getCLOBVal());
4207: DBMS_XMLGEN.closeContext (queryCtx);
4208: exception when others then
4209: DBMS_XMLGEN.closeContext (queryCtx);
4210: RAISE;
4211: END;
4212:
4213: CLOSE price_diff_types_cursor;
4358: and categories.category_id = categories_tl.category_id
4359: and categories_tl.language = l_printing_language;
4360:
4361: BEGIN
4362: queryCtx := DBMS_XMLGEN.newContext(attachments_cursor);
4363: DBMS_XMLGEN.SetRowSetTag(queryCtx, 'ATTACHMENTS');
4364: DBMS_XMLGEN.SetRowTag(queryCtx, 'ATTACHMENTS_ROW');
4365: DBMS_XMLGEN.getXMLType(queryCtx, attachments_res, DBMS_XMLGEN.NONE);
4366: IF attachments_res IS NULL THEN
4359: and categories_tl.language = l_printing_language;
4360:
4361: BEGIN
4362: queryCtx := DBMS_XMLGEN.newContext(attachments_cursor);
4363: DBMS_XMLGEN.SetRowSetTag(queryCtx, 'ATTACHMENTS');
4364: DBMS_XMLGEN.SetRowTag(queryCtx, 'ATTACHMENTS_ROW');
4365: DBMS_XMLGEN.getXMLType(queryCtx, attachments_res, DBMS_XMLGEN.NONE);
4366: IF attachments_res IS NULL THEN
4367: attachments_res := XMLType('
4360:
4361: BEGIN
4362: queryCtx := DBMS_XMLGEN.newContext(attachments_cursor);
4363: DBMS_XMLGEN.SetRowSetTag(queryCtx, 'ATTACHMENTS');
4364: DBMS_XMLGEN.SetRowTag(queryCtx, 'ATTACHMENTS_ROW');
4365: DBMS_XMLGEN.getXMLType(queryCtx, attachments_res, DBMS_XMLGEN.NONE);
4366: IF attachments_res IS NULL THEN
4367: attachments_res := XMLType('
4368: END IF;
4361: BEGIN
4362: queryCtx := DBMS_XMLGEN.newContext(attachments_cursor);
4363: DBMS_XMLGEN.SetRowSetTag(queryCtx, 'ATTACHMENTS');
4364: DBMS_XMLGEN.SetRowTag(queryCtx, 'ATTACHMENTS_ROW');
4365: DBMS_XMLGEN.getXMLType(queryCtx, attachments_res, DBMS_XMLGEN.NONE);
4366: IF attachments_res IS NULL THEN
4367: attachments_res := XMLType('
4368: END IF;
4369: Dbms_Lob.append(xml_clob, attachments_res.getCLOBVal());
4366: IF attachments_res IS NULL THEN
4367: attachments_res := XMLType('
4368: END IF;
4369: Dbms_Lob.append(xml_clob, attachments_res.getCLOBVal());
4370: DBMS_XMLGEN.closeContext (queryCtx);
4371: exception when others then
4372: DBMS_XMLGEN.closeContext (queryCtx);
4373: RAISE;
4374: END;
4368: END IF;
4369: Dbms_Lob.append(xml_clob, attachments_res.getCLOBVal());
4370: DBMS_XMLGEN.closeContext (queryCtx);
4371: exception when others then
4372: DBMS_XMLGEN.closeContext (queryCtx);
4373: RAISE;
4374: END;
4375:
4376: CLOSE attachments_cursor;
4418: 'ALLOW_STAGGERED_AWARDS'
4419: );
4420:
4421: BEGIN
4422: queryCtx := DBMS_XMLGEN.newContext(doc_rules_cursor);
4423: DBMS_XMLGEN.SetRowSetTag(queryCtx, 'DOCUMENT_TYPE_RULES');
4424: DBMS_XMLGEN.SetRowTag(queryCtx, 'DOCUMENT_TYPE_RULES_ROW');
4425: DBMS_XMLGEN.getXMLType(queryCtx, doc_rules_res, DBMS_XMLGEN.NONE);
4426: IF doc_rules_res IS NULL THEN
4419: );
4420:
4421: BEGIN
4422: queryCtx := DBMS_XMLGEN.newContext(doc_rules_cursor);
4423: DBMS_XMLGEN.SetRowSetTag(queryCtx, 'DOCUMENT_TYPE_RULES');
4424: DBMS_XMLGEN.SetRowTag(queryCtx, 'DOCUMENT_TYPE_RULES_ROW');
4425: DBMS_XMLGEN.getXMLType(queryCtx, doc_rules_res, DBMS_XMLGEN.NONE);
4426: IF doc_rules_res IS NULL THEN
4427: doc_rules_res := XMLType('
4420:
4421: BEGIN
4422: queryCtx := DBMS_XMLGEN.newContext(doc_rules_cursor);
4423: DBMS_XMLGEN.SetRowSetTag(queryCtx, 'DOCUMENT_TYPE_RULES');
4424: DBMS_XMLGEN.SetRowTag(queryCtx, 'DOCUMENT_TYPE_RULES_ROW');
4425: DBMS_XMLGEN.getXMLType(queryCtx, doc_rules_res, DBMS_XMLGEN.NONE);
4426: IF doc_rules_res IS NULL THEN
4427: doc_rules_res := XMLType('
4428: END IF;
4421: BEGIN
4422: queryCtx := DBMS_XMLGEN.newContext(doc_rules_cursor);
4423: DBMS_XMLGEN.SetRowSetTag(queryCtx, 'DOCUMENT_TYPE_RULES');
4424: DBMS_XMLGEN.SetRowTag(queryCtx, 'DOCUMENT_TYPE_RULES_ROW');
4425: DBMS_XMLGEN.getXMLType(queryCtx, doc_rules_res, DBMS_XMLGEN.NONE);
4426: IF doc_rules_res IS NULL THEN
4427: doc_rules_res := XMLType('
4428: END IF;
4429: Dbms_Lob.append(xml_clob, doc_rules_res.getCLOBVal());
4426: IF doc_rules_res IS NULL THEN
4427: doc_rules_res := XMLType('
4428: END IF;
4429: Dbms_Lob.append(xml_clob, doc_rules_res.getCLOBVal());
4430: DBMS_XMLGEN.closeContext (queryCtx);
4431: exception when others then
4432: DBMS_XMLGEN.closeContext (queryCtx);
4433: RAISE;
4434: END;
4428: END IF;
4429: Dbms_Lob.append(xml_clob, doc_rules_res.getCLOBVal());
4430: DBMS_XMLGEN.closeContext (queryCtx);
4431: exception when others then
4432: DBMS_XMLGEN.closeContext (queryCtx);
4433: RAISE;
4434: END;
4435:
4436: CLOSE doc_rules_cursor;
4765: and application_id = 396
4766: and language_code = l_printing_language;
4767:
4768: BEGIN
4769: queryCtx := DBMS_XMLGEN.newContext(generic_msgs_cursor);
4770: DBMS_XMLGEN.SetRowSetTag(queryCtx, 'GENERIC_MESSAGES');
4771: DBMS_XMLGEN.SetRowTag(queryCtx, 'GENERIC_MESSAGES_ROW');
4772: DBMS_XMLGEN.getXMLType(queryCtx, generic_msgs_res, DBMS_XMLGEN.NONE);
4773: IF generic_msgs_res IS NULL THEN
4766: and language_code = l_printing_language;
4767:
4768: BEGIN
4769: queryCtx := DBMS_XMLGEN.newContext(generic_msgs_cursor);
4770: DBMS_XMLGEN.SetRowSetTag(queryCtx, 'GENERIC_MESSAGES');
4771: DBMS_XMLGEN.SetRowTag(queryCtx, 'GENERIC_MESSAGES_ROW');
4772: DBMS_XMLGEN.getXMLType(queryCtx, generic_msgs_res, DBMS_XMLGEN.NONE);
4773: IF generic_msgs_res IS NULL THEN
4774: generic_msgs_res := XMLType('
4767:
4768: BEGIN
4769: queryCtx := DBMS_XMLGEN.newContext(generic_msgs_cursor);
4770: DBMS_XMLGEN.SetRowSetTag(queryCtx, 'GENERIC_MESSAGES');
4771: DBMS_XMLGEN.SetRowTag(queryCtx, 'GENERIC_MESSAGES_ROW');
4772: DBMS_XMLGEN.getXMLType(queryCtx, generic_msgs_res, DBMS_XMLGEN.NONE);
4773: IF generic_msgs_res IS NULL THEN
4774: generic_msgs_res := XMLType('
4775: END IF;
4768: BEGIN
4769: queryCtx := DBMS_XMLGEN.newContext(generic_msgs_cursor);
4770: DBMS_XMLGEN.SetRowSetTag(queryCtx, 'GENERIC_MESSAGES');
4771: DBMS_XMLGEN.SetRowTag(queryCtx, 'GENERIC_MESSAGES_ROW');
4772: DBMS_XMLGEN.getXMLType(queryCtx, generic_msgs_res, DBMS_XMLGEN.NONE);
4773: IF generic_msgs_res IS NULL THEN
4774: generic_msgs_res := XMLType('
4775: END IF;
4776: Dbms_Lob.append(xml_clob, generic_msgs_res.getCLOBVal());
4773: IF generic_msgs_res IS NULL THEN
4774: generic_msgs_res := XMLType('
4775: END IF;
4776: Dbms_Lob.append(xml_clob, generic_msgs_res.getCLOBVal());
4777: DBMS_XMLGEN.closeContext (queryCtx);
4778: exception when others then
4779: DBMS_XMLGEN.closeContext (queryCtx);
4780: RAISE;
4781: END;
4775: END IF;
4776: Dbms_Lob.append(xml_clob, generic_msgs_res.getCLOBVal());
4777: DBMS_XMLGEN.closeContext (queryCtx);
4778: exception when others then
4779: DBMS_XMLGEN.closeContext (queryCtx);
4780: RAISE;
4781: END;
4782:
4783: CLOSE generic_msgs_cursor;
4800: and msgs.language_code = l_printing_language;
4801:
4802:
4803: BEGIN
4804: queryCtx := DBMS_XMLGEN.newContext(doc_msgs_cursor);
4805: DBMS_XMLGEN.SetRowSetTag(queryCtx, 'DOCUMENT_SPECIFIC_MESSAGES');
4806: DBMS_XMLGEN.SetRowTag(queryCtx, 'DOCUMENT_SPECIFIC_MESSAGES_ROW');
4807: DBMS_XMLGEN.getXMLType(queryCtx, doc_msgs_res, DBMS_XMLGEN.NONE);
4808: IF doc_msgs_res IS NULL THEN
4801:
4802:
4803: BEGIN
4804: queryCtx := DBMS_XMLGEN.newContext(doc_msgs_cursor);
4805: DBMS_XMLGEN.SetRowSetTag(queryCtx, 'DOCUMENT_SPECIFIC_MESSAGES');
4806: DBMS_XMLGEN.SetRowTag(queryCtx, 'DOCUMENT_SPECIFIC_MESSAGES_ROW');
4807: DBMS_XMLGEN.getXMLType(queryCtx, doc_msgs_res, DBMS_XMLGEN.NONE);
4808: IF doc_msgs_res IS NULL THEN
4809: doc_msgs_res := XMLType('
4802:
4803: BEGIN
4804: queryCtx := DBMS_XMLGEN.newContext(doc_msgs_cursor);
4805: DBMS_XMLGEN.SetRowSetTag(queryCtx, 'DOCUMENT_SPECIFIC_MESSAGES');
4806: DBMS_XMLGEN.SetRowTag(queryCtx, 'DOCUMENT_SPECIFIC_MESSAGES_ROW');
4807: DBMS_XMLGEN.getXMLType(queryCtx, doc_msgs_res, DBMS_XMLGEN.NONE);
4808: IF doc_msgs_res IS NULL THEN
4809: doc_msgs_res := XMLType('
4810: END IF;
4803: BEGIN
4804: queryCtx := DBMS_XMLGEN.newContext(doc_msgs_cursor);
4805: DBMS_XMLGEN.SetRowSetTag(queryCtx, 'DOCUMENT_SPECIFIC_MESSAGES');
4806: DBMS_XMLGEN.SetRowTag(queryCtx, 'DOCUMENT_SPECIFIC_MESSAGES_ROW');
4807: DBMS_XMLGEN.getXMLType(queryCtx, doc_msgs_res, DBMS_XMLGEN.NONE);
4808: IF doc_msgs_res IS NULL THEN
4809: doc_msgs_res := XMLType('
4810: END IF;
4811: Dbms_Lob.append(xml_clob, doc_msgs_res.getCLOBVal());
4808: IF doc_msgs_res IS NULL THEN
4809: doc_msgs_res := XMLType('
4810: END IF;
4811: Dbms_Lob.append(xml_clob, doc_msgs_res.getCLOBVal());
4812: DBMS_XMLGEN.closeContext (queryCtx);
4813: exception when others then
4814: DBMS_XMLGEN.closeContext (queryCtx);
4815: RAISE;
4816: END;
4810: END IF;
4811: Dbms_Lob.append(xml_clob, doc_msgs_res.getCLOBVal());
4812: DBMS_XMLGEN.closeContext (queryCtx);
4813: exception when others then
4814: DBMS_XMLGEN.closeContext (queryCtx);
4815: RAISE;
4816: END;
4817:
4818: CLOSE doc_msgs_cursor;
4817:
4818: CLOSE doc_msgs_cursor;
4819:
4820: dbms_lob.createtemporary(res, TRUE);
4821: queryCtx := DBMS_XMLGEN.newContext('select null from dual');
4822: DBMS_XMLGEN.SetRowSetTag(queryCtx, null);
4823: DBMS_XMLGEN.SetRowTag(queryCtx, null);
4824: DBMS_XMLGEN.getXML(queryCtx, res, DBMS_XMLGEN.NONE);
4825: DBMS_XMLGEN.closeContext (queryCtx);
4818: CLOSE doc_msgs_cursor;
4819:
4820: dbms_lob.createtemporary(res, TRUE);
4821: queryCtx := DBMS_XMLGEN.newContext('select null from dual');
4822: DBMS_XMLGEN.SetRowSetTag(queryCtx, null);
4823: DBMS_XMLGEN.SetRowTag(queryCtx, null);
4824: DBMS_XMLGEN.getXML(queryCtx, res, DBMS_XMLGEN.NONE);
4825: DBMS_XMLGEN.closeContext (queryCtx);
4826:
4819:
4820: dbms_lob.createtemporary(res, TRUE);
4821: queryCtx := DBMS_XMLGEN.newContext('select null from dual');
4822: DBMS_XMLGEN.SetRowSetTag(queryCtx, null);
4823: DBMS_XMLGEN.SetRowTag(queryCtx, null);
4824: DBMS_XMLGEN.getXML(queryCtx, res, DBMS_XMLGEN.NONE);
4825: DBMS_XMLGEN.closeContext (queryCtx);
4826:
4827: result := res || '
4820: dbms_lob.createtemporary(res, TRUE);
4821: queryCtx := DBMS_XMLGEN.newContext('select null from dual');
4822: DBMS_XMLGEN.SetRowSetTag(queryCtx, null);
4823: DBMS_XMLGEN.SetRowTag(queryCtx, null);
4824: DBMS_XMLGEN.getXML(queryCtx, res, DBMS_XMLGEN.NONE);
4825: DBMS_XMLGEN.closeContext (queryCtx);
4826:
4827: result := res || '
4828:
4821: queryCtx := DBMS_XMLGEN.newContext('select null from dual');
4822: DBMS_XMLGEN.SetRowSetTag(queryCtx, null);
4823: DBMS_XMLGEN.SetRowTag(queryCtx, null);
4824: DBMS_XMLGEN.getXML(queryCtx, res, DBMS_XMLGEN.NONE);
4825: DBMS_XMLGEN.closeContext (queryCtx);
4826:
4827: result := res || '
4828:
4829: SELECT CURRENT_DATE INTO l_end_time FROM DUAL;