[Home] [Help]
228: , h.CONVERSION_TYPE_CODE
229: , h.Order_Number
230: , h.Order_Type_Id
231: , t.value PLSQL_TBL_INDEX
232: FROM OM_ID_LIST_TMP t,
233: OE_ORDER_LINES_ALL l,
234: OE_ORDER_HEADERS_ALL h
235: WHERE l.line_id = t.key_id
236: AND l.header_id = h.header_id
1364: i:=p_retrobill_tbl.next(i);
1365: j:=j+1;
1366: END LOOP;
1367:
1368: DELETE FROM OM_ID_LIST_TMP;
1369:
1370: --USE BULK INSERT TO MAXIMIZE PERFORMANCE
1371: IF l_id_tbl.FIRST IS NOT NULL THEN
1372: FORALL j IN l_id_tbl.FIRST..l_id_tbl.LAST
1369:
1370: --USE BULK INSERT TO MAXIMIZE PERFORMANCE
1371: IF l_id_tbl.FIRST IS NOT NULL THEN
1372: FORALL j IN l_id_tbl.FIRST..l_id_tbl.LAST
1373: INSERT INTO OM_ID_LIST_TMP(KEY_ID,VALUE)
1374: VALUES (l_id_tbl(j),l_value_tbl(j));
1375: END IF;
1376:
1377: Exception
3144: l_msg_data VARCHAR2(2000);
3145: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
3146: Cursor unique_header_id IS
3147: SELECT DISTINCT KEY_ID Header_Id
3148: FROM OM_ID_LIST_TMP;
3149:
3150: Cursor line_numbers(p_header_id IN NUMBER) IS
3151: SELECT line_id
3152: FROM oe_order_lines_all
3191: i:=l_delete_tbl.next(i);
3192: END LOOP;
3193:
3194: -- rearranging the line numbers for the headers corresponding to the lines which were deleted.
3195: DELETE FROM OM_ID_LIST_TMP;
3196:
3197: j := l_delete_tbl.FIRST;
3198: WHILE j IS NOT NULL LOOP
3199: INSERT INTO OM_ID_LIST_TMP(KEY_ID)
3195: DELETE FROM OM_ID_LIST_TMP;
3196:
3197: j := l_delete_tbl.FIRST;
3198: WHILE j IS NOT NULL LOOP
3199: INSERT INTO OM_ID_LIST_TMP(KEY_ID)
3200: VALUES (l_delete_tbl(j).retrobill_header_id);
3201: j:=l_delete_tbl.next(j);
3202: END LOOP;
3203:
3217: END IF;
3218: oe_debug_pub.add('Retro:after update line_num');
3219: END LOOP;
3220:
3221: --handle mass update by joing to OM_ID_LIST_TMP
3222: DELETE FROM OM_ID_LIST_TMP;
3223:
3224: IF l_line_id_tbl.FIRST IS NOT NULL THEN
3225: FORALL j IN l_line_id_tbl.FIRST..l_line_id_tbl.LAST
3218: oe_debug_pub.add('Retro:after update line_num');
3219: END LOOP;
3220:
3221: --handle mass update by joing to OM_ID_LIST_TMP
3222: DELETE FROM OM_ID_LIST_TMP;
3223:
3224: IF l_line_id_tbl.FIRST IS NOT NULL THEN
3225: FORALL j IN l_line_id_tbl.FIRST..l_line_id_tbl.LAST
3226: INSERT INTO OM_ID_LIST_TMP(KEY_ID,VALUE)
3222: DELETE FROM OM_ID_LIST_TMP;
3223:
3224: IF l_line_id_tbl.FIRST IS NOT NULL THEN
3225: FORALL j IN l_line_id_tbl.FIRST..l_line_id_tbl.LAST
3226: INSERT INTO OM_ID_LIST_TMP(KEY_ID,VALUE)
3227: VALUES (l_line_id_tbl(j),l_retrobill_qty_tbl(j));
3228: oe_debug_pub.add('Retro:execution mode='||p_execution_mode);
3229:
3230: --pricing quantity derivation might be wrong need to get back to this!!!
3233: pricing_quantity
3234: )
3235: = (Select value,
3236: (value * oeol.unit_selling_price)/decode(nvl(oeol.unit_selling_price_per_pqty,0),0,1,oeol.unit_selling_price_per_pqty)
3237: From OM_ID_LIST_TMP idt
3238: Where idt.key_id = oeol.line_id)
3239: WHERE oeol.retrobill_request_id IS NOT NULL
3240: AND line_id in (SELECT key_id
3241: FROM OM_ID_LIST_TMP);
3237: From OM_ID_LIST_TMP idt
3238: Where idt.key_id = oeol.line_id)
3239: WHERE oeol.retrobill_request_id IS NOT NULL
3240: AND line_id in (SELECT key_id
3241: FROM OM_ID_LIST_TMP);
3242: END IF;
3243:
3244:
3245: DELETE FROM OM_ID_LIST_TMP;
3241: FROM OM_ID_LIST_TMP);
3242: END IF;
3243:
3244:
3245: DELETE FROM OM_ID_LIST_TMP;
3246:
3247: IF l_header_id_tbl.FIRST IS NOT NULL THEN
3248: FORALL j IN l_header_id_tbl.FIRST..l_header_id_tbl.LAST
3249: INSERT INTO OM_ID_LIST_TMP(KEY_ID)
3245: DELETE FROM OM_ID_LIST_TMP;
3246:
3247: IF l_header_id_tbl.FIRST IS NOT NULL THEN
3248: FORALL j IN l_header_id_tbl.FIRST..l_header_id_tbl.LAST
3249: INSERT INTO OM_ID_LIST_TMP(KEY_ID)
3250: VALUES (l_header_id_tbl(j));
3251: END IF;
3252:
3253: --execute means book the orders