[Home] [Help]
18: END IF;
19:
20: SELECT wda.delivery_id
21: INTO l_delivery_id
22: FROM wsh_delivery_assignments wda,
23: wsh_delivery_details_ob_grp_v wdd,
24: wsh_new_deliveries_ob_grp_v wnd
25: WHERE wdd.delivery_detail_id = wda.delivery_detail_id
26: AND wnd.delivery_id = wda.delivery_id
99: SELECT wda.delivery_id
100: INTO l_delivery_id
101: FROM mtl_material_transactions_temp mmtt,
102: wsh_delivery_details_ob_grp_v wdd,
103: wsh_delivery_assignments_v wda
104: WHERE mmtt.transfer_lpn_id = p_lpn_id
105: AND mmtt.transaction_action_id = 28
106: AND mmtt.transaction_source_type_id IN (2, 8)
107: AND mmtt.move_order_line_id = wdd.move_order_line_id
121: --will be 5 and it will be linked with a delivery
122: select count(wda.delivery_id) into l_no_of_deliveries
123: FROM mtl_material_transactions_temp mmtt,
124: wsh_delivery_details_ob_grp_v wdd,
125: wsh_delivery_assignments_v wda
126: WHERE mmtt.transfer_lpn_id = p_lpn_id
127: AND mmtt.transaction_action_id = 28
128: AND mmtt.transaction_source_type_id IN (2, 8)
129: AND mmtt.move_order_line_id = wdd.move_order_line_id
139: SELECT wda.delivery_id
140: INTO l_delivery_id
141: FROM mtl_material_transactions_temp mmtt,
142: wsh_delivery_details_ob_grp_v wdd,
143: wsh_delivery_assignments_v wda
144: WHERE mmtt.transfer_lpn_id = p_lpn_id
145: AND mmtt.transaction_action_id = 28
146: AND mmtt.transaction_source_type_id IN (2, 8)
147: AND mmtt.move_order_line_id = wdd.move_order_line_id
156: ELSIF l_lpn_context IN (12, 11) THEN -- LPN1 has been staged
157: -- 12:Loaded to stage move Context
158: SELECT wda.delivery_id
159: INTO l_delivery_id
160: FROM wsh_delivery_assignments_v wda,
161: wsh_delivery_details_ob_grp_v wdd
162: WHERE wdd.delivery_detail_id = wda.delivery_detail_id
163: AND wdd.lpn_id = p_lpn_id
164: AND wdd.released_status = 'X' -- For LPN reuse ER : 6845650
170: BEGIN
171: SELECT wda.delivery_id
172: INTO l_delivery_id
173: FROM mtl_txn_request_lines mtrl,
174: wsh_delivery_assignments_v wda
175: WHERE mtrl.lpn_id IN (SELECT wlpn.lpn_id
176: FROM wms_license_plate_numbers wlpn
177: START WITH wlpn.lpn_id = p_lpn_id
178: CONNECT BY PRIOR wlpn.lpn_id = wlpn.parent_lpn_id)
879: SELECT outermost_lpn_id, subinventory_code, locator_id
880: INTO x_to_lpn_id, x_to_subinventory_code, x_to_locator_id
881: FROM
882: (SELECT wlpn.outermost_lpn_id, wlpn.subinventory_code, wlpn.locator_id
883: FROM wsh_delivery_assignments wda
884: , wsh_delivery_details_ob_grp_v wdd
885: , mtl_item_locations mil
886: , wms_license_plate_numbers wlpn
887: , wms_dispatched_tasks_history wdth
921: SELECT outermost_lpn_id, subinventory_code, locator_id
922: INTO x_to_lpn_id, x_to_subinventory_code, x_to_locator_id
923: FROM
924: (SELECT wlpn.outermost_lpn_id, wlpn.subinventory_code, wlpn.locator_id
925: FROM wsh_delivery_assignments wda
926: , wsh_delivery_details_ob_grp_v wdd
927: , mtl_item_locations mil
928: , wms_license_plate_numbers wlpn
929: , wms_dispatched_tasks_history wdth
968: SELECT outermost_lpn_id, subinventory_code, locator_id
969: INTO x_to_lpn_id, x_to_subinventory_code, x_to_locator_id
970: FROM
971: (SELECT wlpn.outermost_lpn_id, wlpn.subinventory_code, wlpn.locator_id
972: FROM wsh_delivery_assignments wda
973: , wsh_delivery_details_ob_grp_v wdd
974: , mtl_item_locations mil
975: , wms_license_plate_numbers wlpn
976: , wms_dispatched_tasks_history wdth