[Home] [Help]
1620: BEGIN
1621: SELECT oeoh.header_id, to_char(oeoh.order_number), oeol.line_id, oeol.line_number
1622: INTO l_material_rec.document_id, l_material_rec.document_number
1623: , l_material_rec.document_line_id, l_material_rec.document_line_num
1624: FROM oe_order_headers_all oeoh, oe_order_lines_all oeol
1625: WHERE oeol.line_id = l_material_rec.reference_id
1626: AND oeoh.header_id = oeol.header_id;
1627: EXCEPTION
1628: WHEN no_data_found THEN
1639: IF (l_material_rec.reference_id IS NOT NULL) THEN
1640: BEGIN
1641: SELECT oeol.line_id, oeol.line_number
1642: INTO l_material_rec.document_line_id, l_material_rec.document_line_num
1643: FROM oe_order_lines_all oeol
1644: WHERE oeol.line_id = l_material_rec.reference_id;
1645: EXCEPTION
1646: WHEN no_data_found THEN
1647: IF l_debug = 1 THEN
2392: IF l_material_rec.order_line_id IS NOT NULL AND l_material_rec.order_header_id IS NOT NULL THEN
2393: BEGIN
2394: SELECT nvl(oeol.packing_instructions, oeoh.packing_instructions)
2395: INTO l_material_rec.packing_instruction
2396: FROM oe_order_headers_all oeoh, oe_order_lines_all oeol
2397: WHERE oeoh.header_id = oeol.header_id
2398: AND oeol.line_id = l_material_rec.order_line_id;
2399: EXCEPTION
2400: WHEN NO_DATA_FOUND THEN
2463: BEGIN
2464: SELECT 'Y' INTO l_material_rec.pto_flag
2465: FROM dual
2466: WHERE exists (
2467: select 1 from oe_order_lines_all oel, oe_order_lines_all oel1
2468: where oel.inventory_item_id = l_material_rec.inventory_item_id
2469: and oel.top_model_line_id = l_top_model_line_id
2470: and oel1. inventory_item_id = oel.inventory_item_id
2471: and oel1.top_model_line_id = oel.top_model_line_id
2557: -- since we need to know whether new scanned item IS UNIQUE across ALL the possible
2558: --kits IN the list OF eligible matrl, rather we will do programatically
2559: CURSOR kit_new_cur IS
2560: SELECT oel.INVENTORY_ITEM_id, oel.top_model_line_id, 'N', 'N'
2561: FROM oe_order_lines_all oel
2562: WHERE oel.line_id = oel.top_model_line_id
2563: AND oel.ato_line_id IS NULL
2564: AND oel.top_model_line_id in (
2565: select oel1.top_model_line_id
2562: WHERE oel.line_id = oel.top_model_line_id
2563: AND oel.ato_line_id IS NULL
2564: AND oel.top_model_line_id in (
2565: select oel1.top_model_line_id
2566: from oe_order_lines_all oel1,oe_order_lines_all oel2
2567: where oel1.inventory_item_id = p_item_id
2568: and oel2. inventory_item_id = oel1.inventory_item_id
2569: and oel2.top_model_line_id = oel1.top_model_line_id
2570: AND oel1.line_id = oel2.line_id --bug 3458361
2571: and (((oel1.shippable_flag = 'Y') AND (oel1.line_id <> oel1.TOP_MODEL_LINE_ID) and (oel1.ato_line_id is null))
2572: OR (oel2.ato_line_id is not null and oel2.line_id = oel2.top_model_line_id))
2573: )
2574: AND exists (
2575: select 1 from WMS_PACKING_MATERIAL_GTEMP wpmg, oe_order_lines_all oel1
2576: where WPMG.order_header_id = oel1.header_id
2577: AND wpmg.order_line_id = oel1.line_id
2578: AND wpmg.inventory_item_id = p_item_id
2579: AND wpmg.inventory_item_id = oel1.inventory_item_id
2684: msi.inventory_item_id ITEM_ID,
2685: --round(oel.ordered_quantity/oel1.ordered_quantity) bom_qty,
2686: oel.ordered_quantity ORD_QTY,
2687: OEL.top_model_LINE_ID
2688: FROM oe_order_lines_all oel, mtl_system_items_kfv msi, oe_order_lines_all oel1
2689: WHERE oel.inventory_item_id = msi.inventory_item_id
2690: AND oel.ship_from_org_id = msi.organization_id
2691: AND oel1.inventory_item_id = msi.inventory_item_id
2692: AND oel1.ship_from_org_id = msi.organization_id