[Home] [Help]
227: , h.CONVERSION_TYPE_CODE
228: , h.Order_Number
229: , h.Order_Type_Id
230: , t.value PLSQL_TBL_INDEX
231: FROM OM_ID_LIST_TMP t,
232: OE_ORDER_LINES_ALL l,
233: OE_ORDER_HEADERS_ALL h
234: WHERE l.line_id = t.key_id
235: AND l.header_id = h.header_id
1345: i:=p_retrobill_tbl.next(i);
1346: j:=j+1;
1347: END LOOP;
1348:
1349: DELETE FROM OM_ID_LIST_TMP;
1350:
1351: --USE BULK INSERT TO MAXIMIZE PERFORMANCE
1352: IF l_id_tbl.FIRST IS NOT NULL THEN
1353: FORALL j IN l_id_tbl.FIRST..l_id_tbl.LAST
1350:
1351: --USE BULK INSERT TO MAXIMIZE PERFORMANCE
1352: IF l_id_tbl.FIRST IS NOT NULL THEN
1353: FORALL j IN l_id_tbl.FIRST..l_id_tbl.LAST
1354: INSERT INTO OM_ID_LIST_TMP(KEY_ID,VALUE)
1355: VALUES (l_id_tbl(j),l_value_tbl(j));
1356: END IF;
1357:
1358: Exception
2806: l_msg_data VARCHAR2(2000);
2807: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2808: Cursor unique_header_id IS
2809: SELECT DISTINCT KEY_ID Header_Id
2810: FROM OM_ID_LIST_TMP;
2811:
2812: Cursor line_numbers(p_header_id IN NUMBER) IS
2813: SELECT line_id
2814: FROM oe_order_lines_all
2853: i:=l_delete_tbl.next(i);
2854: END LOOP;
2855:
2856: -- rearranging the line numbers for the headers corresponding to the lines which were deleted.
2857: DELETE FROM OM_ID_LIST_TMP;
2858:
2859: j := l_delete_tbl.FIRST;
2860: WHILE j IS NOT NULL LOOP
2861: INSERT INTO OM_ID_LIST_TMP(KEY_ID)
2857: DELETE FROM OM_ID_LIST_TMP;
2858:
2859: j := l_delete_tbl.FIRST;
2860: WHILE j IS NOT NULL LOOP
2861: INSERT INTO OM_ID_LIST_TMP(KEY_ID)
2862: VALUES (l_delete_tbl(j).retrobill_header_id);
2863: j:=l_delete_tbl.next(j);
2864: END LOOP;
2865:
2879: END IF;
2880: oe_debug_pub.add('Retro:after update line_num');
2881: END LOOP;
2882:
2883: --handle mass update by joing to OM_ID_LIST_TMP
2884: DELETE FROM OM_ID_LIST_TMP;
2885:
2886: IF l_line_id_tbl.FIRST IS NOT NULL THEN
2887: FORALL j IN l_line_id_tbl.FIRST..l_line_id_tbl.LAST
2880: oe_debug_pub.add('Retro:after update line_num');
2881: END LOOP;
2882:
2883: --handle mass update by joing to OM_ID_LIST_TMP
2884: DELETE FROM OM_ID_LIST_TMP;
2885:
2886: IF l_line_id_tbl.FIRST IS NOT NULL THEN
2887: FORALL j IN l_line_id_tbl.FIRST..l_line_id_tbl.LAST
2888: INSERT INTO OM_ID_LIST_TMP(KEY_ID,VALUE)
2884: DELETE FROM OM_ID_LIST_TMP;
2885:
2886: IF l_line_id_tbl.FIRST IS NOT NULL THEN
2887: FORALL j IN l_line_id_tbl.FIRST..l_line_id_tbl.LAST
2888: INSERT INTO OM_ID_LIST_TMP(KEY_ID,VALUE)
2889: VALUES (l_line_id_tbl(j),l_retrobill_qty_tbl(j));
2890: oe_debug_pub.add('Retro:execution mode='||p_execution_mode);
2891:
2892: --pricing quantity derivation might be wrong need to get back to this!!!
2895: pricing_quantity
2896: )
2897: = (Select value,
2898: (value * oeol.unit_selling_price)/decode(nvl(oeol.unit_selling_price_per_pqty,0),0,1,oeol.unit_selling_price_per_pqty)
2899: From OM_ID_LIST_TMP idt
2900: Where idt.key_id = oeol.line_id)
2901: WHERE oeol.retrobill_request_id IS NOT NULL
2902: AND line_id in (SELECT key_id
2903: FROM OM_ID_LIST_TMP);
2899: From OM_ID_LIST_TMP idt
2900: Where idt.key_id = oeol.line_id)
2901: WHERE oeol.retrobill_request_id IS NOT NULL
2902: AND line_id in (SELECT key_id
2903: FROM OM_ID_LIST_TMP);
2904: END IF;
2905:
2906:
2907: DELETE FROM OM_ID_LIST_TMP;
2903: FROM OM_ID_LIST_TMP);
2904: END IF;
2905:
2906:
2907: DELETE FROM OM_ID_LIST_TMP;
2908:
2909: IF l_header_id_tbl.FIRST IS NOT NULL THEN
2910: FORALL j IN l_header_id_tbl.FIRST..l_header_id_tbl.LAST
2911: INSERT INTO OM_ID_LIST_TMP(KEY_ID)
2907: DELETE FROM OM_ID_LIST_TMP;
2908:
2909: IF l_header_id_tbl.FIRST IS NOT NULL THEN
2910: FORALL j IN l_header_id_tbl.FIRST..l_header_id_tbl.LAST
2911: INSERT INTO OM_ID_LIST_TMP(KEY_ID)
2912: VALUES (l_header_id_tbl(j));
2913: END IF;
2914:
2915: --execute means book the orders