[Home] [Help]
142: p_direct_ship_flag IN VARCHAR2 DEFAULT 'N')
143: RETURN VARCHAR2 IS
144: lpn_loaded VARCHAR2(1) := 'N';
145: BEGIN
146: /*Bug 13104302 - Perf fix:- removed reference to dual and added 'rownum<2' */
147: select 'Y' into lpn_loaded
148: from wms_shipping_transaction_temp wstt
149: where wstt.dock_door_id = p_dock_door_id
150: and wstt.dock_appoint_flag = p_dock_appoint_flag
1297:
1298: if( l_transaction_temp_id is null ) then
1299: select mtl_material_Transactions_s.nextval
1300: into l_InvPCInRecType.transaction_temp_id
1301: from dual;
1302:
1303: l_transaction_temp_id := l_InvPCInRecType.transaction_temp_id;
1304: l_InvPCInRecType.transaction_id := l_transaction_id; --BUG# 10264172
1305:
1496:
1497: BEGIN
1498: select 1
1499: into x_result
1500: from dual
1501: where exists (select 1
1502: from wms_shipping_transaction_temp
1503: where delivery_id = l_delivery_id
1504: and direct_ship_flag='Y');
1514: and wdl.pick_up_stop_id = wts.stop_id;
1515:
1516: select 1
1517: into x_result
1518: from dual
1519: where exists (select 1
1520: from wms_shipping_transaction_temp
1521: where trip_id = l_trip_id
1522: and direct_ship_flag='Y');
1771: FROM wms_shipping_transaction_temp wstt
1772: , wsh_delivery_details wdd
1773: WHERE wstt.outermost_lpn_id = p_outermost_lpn_id
1774: AND wstt.delivery_detail_id = wdd.delivery_detail_id
1775: AND NVL(oe_dual_uom_util.get_fulfillment_base(wdd.source_line_id), 'P') = 'P';
1776:
1777: CURSOR nested_children_lpn_cursor IS
1778: SELECT lpn_id
1779: FROM WMS_LICENSE_PLATE_NUMBERS
1819: -- First check if the lpn has been loaded before
1820: BEGIN
1821: select 1
1822: into l_load_before
1823: from DUAL
1824: where exists (
1825: select 1
1826: from wms_shipping_transaction_temp
1827: where outermost_lpn_id = p_outermost_lpn_id
3132: x_missing_count := l_count;
3133: end if;
3134: EXCEPTION
3135: WHEN NO_DATA_FOUND THEN
3136: open x_missing_lpns FOR select 1 from dual;
3137: return;
3138: END;
3139: open x_missing_lpns FOR
3140: select wlpn.license_plate_number
3214: x_missing_count := l_count;
3215: end if;
3216: EXCEPTION
3217: WHEN NO_DATA_FOUND THEN
3218: open x_missing_lpns FOR select 1 from dual;
3219: return;
3220: END;
3221:
3222:
3320: x_missing_count := l_count;
3321: end if;
3322: EXCEPTION
3323: WHEN NO_DATA_FOUND THEN
3324: open x_missing_item FOR select 1 from dual;
3325: return;
3326: END;
3327: open x_missing_item FOR
3328: select wnd.name
3389: x_missing_count := l_count;
3390: end if;
3391: EXCEPTION
3392: WHEN NO_DATA_FOUND THEN
3393: open x_missing_item FOR select 1 from dual;
3394: return;
3395: END;
3396:
3397: /*Bug#5612236. In the below query, replaced 'MTL_SYSTEM_ITEMS_KFV' with
3534: EXIT WHEN delivered_ship_set%NOTFOUND;
3535: BEGIN
3536: /* SELECT 1
3537: INTO unshipped_count
3538: FROM DUAL
3539: WHERE exists (
3540: SELECT 1
3541: FROM wsh_delivery_details_ob_grp_v wdd
3542: WHERE
5622: WHERE wstt.organization_id = p_organization_id
5623: AND nvl(wstt.direct_ship_flag,'N') = 'N'
5624: AND wstt.outermost_lpn_id = p_outermost_lpn_id
5625: AND wdd.delivery_detail_id = wstt.delivery_detail_id
5626: AND NVL(oe_dual_uom_util.get_fulfillment_base(wdd.source_line_id), 'P') = 'P';
5627:
5628: l_index NUMBER;
5629: l_detail_attributes WSH_DELIVERY_DETAILS_PUB.ChangedAttributeTabType;
5630: l_return_status VARCHAR2(1);
5781: x_nt_count := l_count;
5782: END IF;
5783: EXCEPTION
5784: WHEN NO_DATA_FOUND THEN
5785: OPEN x_nt_item FOR SELECT 1 FROM dual;
5786: RETURN;
5787: END;
5788: OPEN x_nt_item FOR
5789: SELECT wnd.name,
5846: x_nt_count := l_count;
5847: END IF;
5848: EXCEPTION
5849: WHEN NO_DATA_FOUND THEN
5850: OPEN x_nt_item FOR SELECT 1 FROM dual;
5851: RETURN;
5852: END;
5853: OPEN x_nt_item FOR
5854: SELECT wnd.name,
5998: and wdst.organization_id (+) = wdd.organization_id --Added bug 4128854
5999: and exists (select 1
6000: from oe_order_lines_all ool
6001: where ool.header_id = wdd.source_header_id
6002: and NVL(OE_DUAL_UOM_UTIL.get_fulfillment_base(ool.line_id),'P') = l_fulfillment_base
6003: )
6004: order by 2,1;
6005:
6006: END get_order_lov;
6113: WHERE wdd_xdock.source_header_id = oel.header_id
6114: AND wdd_xdock.source_line_id = oel.line_id
6115: AND wdd_xdock.released_status = 'S'
6116: AND wdd_xdock.move_order_line_id IS NULL)
6117: AND NVL(OE_DUAL_UOM_UTIL.get_fulfillment_base(oel.line_id),'P') = l_fulfillment_base
6118: GROUP BY oel.line_id
6119: , to_char(oel.line_number) ||'.'||to_char(oel.shipment_number) ||
6120: decode(oel.option_number,NULL,NULL,'.'||to_char(oel.option_number))||
6121: decode(oel.component_number, null, null,decode(oel.option_number, null,'.',null)||
6223: WHERE wdd_xdock.source_header_id = oel.header_id
6224: AND wdd_xdock.source_line_id = oel.line_id
6225: AND wdd_xdock.released_status = 'S'
6226: AND wdd_xdock.move_order_line_id IS NULL)
6227: AND NVL(OE_DUAL_UOM_UTIL.get_fulfillment_base(oel.line_id),'P') = l_fulfillment_base
6228: GROUP BY oel.line_id
6229: , to_char(oel.line_number) ||'.'||to_char(oel.shipment_number) ||
6230: decode(oel.option_number,NULL,NULL,'.'||to_char(oel.option_number))||
6231: decode(oel.component_number, null, null,decode(oel.option_number, null,'.',null)||
6329: WHERE wdd_xdock.source_header_id = oel.header_id
6330: AND wdd_xdock.source_line_id = oel.line_id
6331: AND wdd_xdock.released_status = 'S'
6332: AND wdd_xdock.move_order_line_id IS NULL)
6333: AND NVL(OE_DUAL_UOM_UTIL.get_fulfillment_base(oel.line_id),'P') = l_fulfillment_base
6334: GROUP BY oel.line_id
6335: , to_char(oel.line_number) ||'.'||to_char(oel.shipment_number) ||
6336: decode(oel.option_number,NULL,NULL,'.'||to_char(oel.option_number))||
6337: decode(oel.component_number, null, null,decode(oel.option_number, null,'.',null)||
6440: WHERE wdd_xdock.source_header_id = oel.header_id
6441: AND wdd_xdock.source_line_id = oel.line_id
6442: AND wdd_xdock.released_status = 'S'
6443: AND wdd_xdock.move_order_line_id IS NULL)
6444: AND NVL(OE_DUAL_UOM_UTIL.get_fulfillment_base(oel.line_id),'P') = l_fulfillment_base
6445: GROUP BY oel.line_id
6446: , to_char(oel.line_number) ||'.'||to_char(oel.shipment_number) ||
6447: decode(oel.option_number,NULL,NULL,'.'||to_char(oel.option_number))||
6448: decode(oel.component_number, null, null,decode(oel.option_number, null,'.',null)||