[Home] [Help]
1227:
1228: /*
1229: ** Fix Bug # 1920064
1230: ** The SQL below was modified. To improve the performance, the scan on
1231: ** the table OE_ORDER_LINES_ALL was avoided as the header_id was being
1232: ** passed as a parameter to this procedure. The UNION was removed and
1233: ** the whole SQL was re-written as below.
1234: ** Fix Bug # 2984023
1235: ** Modified the query to check for only line level holds
1370: SELECT /* MOAC_SQL_CHANGE */ 'ANY_ATO_LINE_HOLD'
1371: INTO l_dummy
1372: FROM oe_order_holds_all oh
1373: WHERE oh.header_id = p_hdr_id
1374: and oh.line_id in (select ol.line_id from oe_order_lines_all ol
1375: where ol.header_id = oh.header_id
1376: and ol.ato_line_id = p_ato_line_id
1377: and ol.top_model_line_id = p_top_model_line_id)
1378: and oh.hold_release_id is null
1497: SELECT /* MOAC_SQL_CHANGE */ 'ANY_SMC_LINE_HOLD'
1498: INTO l_dummy
1499: FROM oe_order_holds_all oh
1500: WHERE oh.header_id = p_hdr_id
1501: and oh.line_id in (select ol.line_id from oe_order_lines_all ol
1502: where ol.header_id = oh.header_id
1503: and ol.top_model_line_id = p_top_model_line_id)
1504: and oh.hold_release_id is null
1505: AND EXISTS
1732: p_hdr_id NUMBER;
1733:
1734: CURSOR ato_model_lines IS
1735: select line_id
1736: from oe_order_lines_all
1737: where ato_line_id = l_ato_line_id
1738: and top_model_line_id = l_top_model_line_id;
1739:
1740: CURSOR smc_lines IS
1738: and top_model_line_id = l_top_model_line_id;
1739:
1740: CURSOR smc_lines IS
1741: select line_id
1742: from oe_order_lines_all
1743: where top_model_line_id = l_top_model_line_id;
1744: --
1745: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1746: --
1778: IF p_header_id IS NULL THEN
1779: Begin
1780: SELECT header_id
1781: INTO p_hdr_id
1782: FROM oe_order_lines_all
1783: WHERE line_id = p_line_id;
1784: EXCEPTION
1785: WHEN NO_DATA_FOUND THEN
1786: x_result_out := FND_API.G_FALSE;
1916: SHIP_MODEL_COMPLETE_FLAG, ITEM_TYPE_CODE,
1917: LINK_TO_LINE_ID
1918: INTO l_ato_line_id, l_top_model_line_id,
1919: l_smc_flag, l_item_type_code, l_link_to_line_id
1920: FROM oe_order_lines_all
1921: WHERE line_id = p_line_id;
1922: EXCEPTION
1923: WHEN NO_DATA_FOUND THEN
1924: x_result_out := FND_API.G_FALSE;
2644: AND OH.hold_source_id=HS.hold_source_id
2645: AND HS.hold_entity_id=OL.inventory_item_id
2646: AND HS.hold_entity_id2=p_inventory_item_id;
2647:
2648: l_top_model_line_id OE_ORDER_LINES_ALL.TOP_MODEL_LINE_ID%TYPE;
2649: l_inventory_item_id OE_ORDER_LINES_ALL.INVENTORY_ITEM_ID%TYPE;
2650: --ER#7479609 end
2651:
2652: --
2645: AND HS.hold_entity_id=OL.inventory_item_id
2646: AND HS.hold_entity_id2=p_inventory_item_id;
2647:
2648: l_top_model_line_id OE_ORDER_LINES_ALL.TOP_MODEL_LINE_ID%TYPE;
2649: l_inventory_item_id OE_ORDER_LINES_ALL.INVENTORY_ITEM_ID%TYPE;
2650: --ER#7479609 end
2651:
2652: --
2653: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2970: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2971: --
2972: --ER#7479609 start
2973: l_header_rec OE_ORDER_HEADERS_ALL%rowtype;
2974: l_line_rec OE_ORDER_LINES_ALL%rowtype;
2975: TYPE entity_rec IS RECORD (entity_code oe_hold_sources_all.hold_entity_code%TYPE,
2976: entity_id oe_hold_sources_all.hold_entity_id%TYPE);
2977:
2978: TYPE entity_tab IS TABLE OF entity_rec INDEX BY BINARY_INTEGER;
4263: AND HS.HOLD_ENTITY_CODE <> 'O';
4264: */
4265: --ER#7479609 start
4266: l_header_rec OE_ORDER_HEADERS_ALL%rowtype;
4267: l_line_rec OE_ORDER_LINES_ALL%rowtype;
4268: TYPE entity_rec IS RECORD (entity_code oe_hold_sources_all.hold_entity_code%TYPE,
4269: entity_id oe_hold_sources_all.hold_entity_id%TYPE);
4270:
4271: TYPE entity_tab IS TABLE OF entity_rec INDEX BY BINARY_INTEGER;