[Home] [Help]
1316:
1317: /*
1318: ** Fix Bug # 1920064
1319: ** The SQL below was modified. To improve the performance, the scan on
1320: ** the table OE_ORDER_LINES_ALL was avoided as the header_id was being
1321: ** passed as a parameter to this procedure. The UNION was removed and
1322: ** the whole SQL was re-written as below.
1323: ** Fix Bug # 2984023
1324: ** Modified the query to check for only line level holds
1459: SELECT /* MOAC_SQL_CHANGE */ 'ANY_ATO_LINE_HOLD'
1460: INTO l_dummy
1461: FROM oe_order_holds_all oh
1462: WHERE oh.header_id = p_hdr_id
1463: and oh.line_id in (select ol.line_id from oe_order_lines_all ol
1464: where ol.header_id = oh.header_id
1465: and ol.ato_line_id = p_ato_line_id
1466: and ol.top_model_line_id = p_top_model_line_id)
1467: and oh.hold_release_id is null
1586: SELECT /* MOAC_SQL_CHANGE */ 'ANY_SMC_LINE_HOLD'
1587: INTO l_dummy
1588: FROM oe_order_holds_all oh
1589: WHERE oh.header_id = p_hdr_id
1590: and oh.line_id in (select ol.line_id from oe_order_lines_all ol
1591: where ol.header_id = oh.header_id
1592: and ol.top_model_line_id = p_top_model_line_id)
1593: and oh.hold_release_id is null
1594: AND EXISTS
1821: p_hdr_id NUMBER;
1822:
1823: CURSOR ato_model_lines IS
1824: select line_id
1825: from oe_order_lines_all
1826: where ato_line_id = l_ato_line_id
1827: and top_model_line_id = l_top_model_line_id;
1828:
1829: CURSOR smc_lines IS
1827: and top_model_line_id = l_top_model_line_id;
1828:
1829: CURSOR smc_lines IS
1830: select line_id
1831: from oe_order_lines_all
1832: where top_model_line_id = l_top_model_line_id;
1833: --
1834: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1835: --
1867: IF p_header_id IS NULL THEN
1868: Begin
1869: SELECT header_id
1870: INTO p_hdr_id
1871: FROM oe_order_lines_all
1872: WHERE line_id = p_line_id;
1873: EXCEPTION
1874: WHEN NO_DATA_FOUND THEN
1875: x_result_out := FND_API.G_FALSE;
2005: SHIP_MODEL_COMPLETE_FLAG, ITEM_TYPE_CODE,
2006: LINK_TO_LINE_ID
2007: INTO l_ato_line_id, l_top_model_line_id,
2008: l_smc_flag, l_item_type_code, l_link_to_line_id
2009: FROM oe_order_lines_all
2010: WHERE line_id = p_line_id;
2011: EXCEPTION
2012: WHEN NO_DATA_FOUND THEN
2013: x_result_out := FND_API.G_FALSE;
2736: AND OH.hold_source_id=HS.hold_source_id
2737: AND HS.hold_entity_id=OL.inventory_item_id
2738: AND HS.hold_entity_id2=p_inventory_item_id;
2739:
2740: l_top_model_line_id OE_ORDER_LINES_ALL.TOP_MODEL_LINE_ID%TYPE;
2741: l_inventory_item_id OE_ORDER_LINES_ALL.INVENTORY_ITEM_ID%TYPE;
2742: --ER#7479609 end
2743:
2744: --
2737: AND HS.hold_entity_id=OL.inventory_item_id
2738: AND HS.hold_entity_id2=p_inventory_item_id;
2739:
2740: l_top_model_line_id OE_ORDER_LINES_ALL.TOP_MODEL_LINE_ID%TYPE;
2741: l_inventory_item_id OE_ORDER_LINES_ALL.INVENTORY_ITEM_ID%TYPE;
2742: --ER#7479609 end
2743:
2744: --
2745: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
3076: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
3077: --
3078: --ER#7479609 start
3079: l_header_rec OE_ORDER_HEADERS_ALL%rowtype;
3080: l_line_rec OE_ORDER_LINES_ALL%rowtype;
3081: TYPE entity_rec IS RECORD (entity_code oe_hold_sources_all.hold_entity_code%TYPE,
3082: entity_id oe_hold_sources_all.hold_entity_id%TYPE);
3083:
3084: TYPE entity_tab IS TABLE OF entity_rec INDEX BY BINARY_INTEGER;
4560: AND HS.HOLD_ENTITY_CODE <> 'O';
4561: */
4562: --ER#7479609 start
4563: l_header_rec OE_ORDER_HEADERS_ALL%rowtype;
4564: l_line_rec OE_ORDER_LINES_ALL%rowtype;
4565: TYPE entity_rec IS RECORD (entity_code oe_hold_sources_all.hold_entity_code%TYPE,
4566: entity_id oe_hold_sources_all.hold_entity_id%TYPE);
4567:
4568: TYPE entity_tab IS TABLE OF entity_rec INDEX BY BINARY_INTEGER;