[Home] [Help]
1691: BEGIN
1692: SELECT oeoh.header_id, to_char(oeoh.order_number), oeol.line_id, oeol.line_number
1693: INTO l_material_rec.document_id, l_material_rec.document_number
1694: , l_material_rec.document_line_id, l_material_rec.document_line_num
1695: FROM oe_order_headers_all oeoh, oe_order_lines_all oeol
1696: WHERE oeol.line_id = l_material_rec.reference_id
1697: AND oeoh.header_id = oeol.header_id;
1698: EXCEPTION
1699: WHEN no_data_found THEN
1710: IF (l_material_rec.reference_id IS NOT NULL) THEN
1711: BEGIN
1712: SELECT oeol.line_id, oeol.line_number
1713: INTO l_material_rec.document_line_id, l_material_rec.document_line_num
1714: FROM oe_order_lines_all oeol
1715: WHERE oeol.line_id = l_material_rec.reference_id;
1716: EXCEPTION
1717: WHEN no_data_found THEN
1718: IF l_debug = 1 THEN
2465: IF l_material_rec.order_line_id IS NOT NULL AND l_material_rec.order_header_id IS NOT NULL THEN
2466: BEGIN
2467: SELECT nvl(oeol.packing_instructions, oeoh.packing_instructions)
2468: INTO l_material_rec.packing_instruction
2469: FROM oe_order_headers_all oeoh, oe_order_lines_all oeol
2470: WHERE oeoh.header_id = oeol.header_id
2471: AND oeol.line_id = l_material_rec.order_line_id;
2472: EXCEPTION
2473: WHEN NO_DATA_FOUND THEN
2536: BEGIN
2537: SELECT 'Y' INTO l_material_rec.pto_flag
2538: FROM dual
2539: WHERE exists (
2540: select 1 from oe_order_lines_all oel, oe_order_lines_all oel1
2541: where oel.inventory_item_id = l_material_rec.inventory_item_id
2542: and oel.top_model_line_id = l_top_model_line_id
2543: and oel1. inventory_item_id = oel.inventory_item_id
2544: and oel1.top_model_line_id = oel.top_model_line_id
2630: -- since we need to know whether new scanned item IS UNIQUE across ALL the possible
2631: --kits IN the list OF eligible matrl, rather we will do programatically
2632: CURSOR kit_new_cur IS
2633: SELECT oel.INVENTORY_ITEM_id, oel.top_model_line_id, 'N', 'N'
2634: FROM oe_order_lines_all oel
2635: WHERE oel.line_id = oel.top_model_line_id
2636: AND oel.ato_line_id IS NULL
2637: AND oel.top_model_line_id in (
2638: select oel1.top_model_line_id
2635: WHERE oel.line_id = oel.top_model_line_id
2636: AND oel.ato_line_id IS NULL
2637: AND oel.top_model_line_id in (
2638: select oel1.top_model_line_id
2639: from oe_order_lines_all oel1,oe_order_lines_all oel2
2640: where oel1.inventory_item_id = p_item_id
2641: and oel2. inventory_item_id = oel1.inventory_item_id
2642: and oel2.top_model_line_id = oel1.top_model_line_id
2643: AND oel1.line_id = oel2.line_id --bug 3458361
2644: and (((oel1.shippable_flag = 'Y') AND (oel1.line_id <> oel1.TOP_MODEL_LINE_ID) and (oel1.ato_line_id is null))
2645: OR (oel2.ato_line_id is not null and oel2.line_id = oel2.top_model_line_id))
2646: )
2647: AND exists (
2648: select 1 from WMS_PACKING_MATERIAL_GTEMP wpmg, oe_order_lines_all oel1
2649: where WPMG.order_header_id = oel1.header_id
2650: AND wpmg.order_line_id = oel1.line_id
2651: AND wpmg.inventory_item_id = p_item_id
2652: AND wpmg.inventory_item_id = oel1.inventory_item_id
2757: msi.inventory_item_id ITEM_ID,
2758: --round(oel.ordered_quantity/oel1.ordered_quantity) bom_qty,
2759: oel.ordered_quantity ORD_QTY,
2760: OEL.top_model_LINE_ID
2761: FROM oe_order_lines_all oel, mtl_system_items_kfv msi, oe_order_lines_all oel1
2762: WHERE oel.inventory_item_id = msi.inventory_item_id
2763: AND oel.ship_from_org_id = msi.organization_id
2764: AND oel1.inventory_item_id = msi.inventory_item_id
2765: AND oel1.ship_from_org_id = msi.organization_id