[Home] [Help]
386: , wdd.locator_id
387: , wdd.lot_number
388: , wdd.serial_number
389: , wdd.transaction_temp_id
390: from wms_license_plate_numbers wlpn
391: , wsh_delivery_details_ob_grp_v wdd
392: , wsh_delivery_assignments_v wda
393: , wsh_delivery_details_ob_grp_v wdd2
394: where wlpn.lpn_id = wdd2.lpn_id
595: elsif( p_txn_dock = 'N' ) then
596: open x_staging_lane_lov for
597: select distinct milk.inventory_location_id, milk.concatenated_segments
598: from mtl_item_locations_kfv milk
599: , wms_license_plate_numbers lpn
600: where milk.inventory_location_id (+) = lpn.locator_id
601: and milk.organization_id (+) = lpn.organization_id
602: and milk.organization_id = p_organization_id
603: and milk.subinventory_code = p_sub_code
647: , wdd.locator_id
648: , wdd.released_status
649: from wsh_delivery_details_ob_grp_v wdd
650: , wsh_delivery_assignments_v wda
651: , wms_license_plate_numbers lpn
652: , wsh_delivery_details_ob_grp_v wdd2
653: where wdd2.lpn_id = lpn.lpn_id
654: and wdd2.lpn_id is not null
655: and wdd2.released_status = 'X' -- For LPN reuse ER : 6845650
740:
741:
742: select outermost_lpn_id
743: into l_outermost_lpn_id
744: from wms_license_plate_numbers
745: where lpn_id = l_parent_lpn_id;
746:
747: IF (l_debug = 1) THEN
748: debug( 'l_outermost_lpn_id is ' || l_outermost_lpn_id, 'Populate_WSTT');
749: END IF;
750: BEGIN
751: select license_plate_number
752: into l_outermost_lpn
753: from wms_license_plate_numbers
754: where lpn_id = l_outermost_lpn_id;
755: EXCEPTION
756: when no_data_found then
757: IF (l_debug = 1) THEN
967: select distinct lpn.outermost_lpn_id
968: , wlpn.license_plate_number
969: , wnd.delivery_id,wnd.name
970: , nvl(wdd.load_seq_number,0) as load_seq_num
971: from wms_license_plate_numbers lpn
972: , wms_license_plate_numbers wlpn
973: , wsh_new_deliveries_ob_grp_v wnd
974: , wsh_delivery_legs_ob_grp_v wdl
975: , wsh_delivery_details_ob_grp_v wdd
968: , wlpn.license_plate_number
969: , wnd.delivery_id,wnd.name
970: , nvl(wdd.load_seq_number,0) as load_seq_num
971: from wms_license_plate_numbers lpn
972: , wms_license_plate_numbers wlpn
973: , wsh_new_deliveries_ob_grp_v wnd
974: , wsh_delivery_legs_ob_grp_v wdl
975: , wsh_delivery_details_ob_grp_v wdd
976: , wsh_delivery_assignments_v wda
1000: , get_delivery_name(wda.delivery_id)
1001: , nvl(wdd.load_seq_number,0) as load_seq_num
1002: from wsh_delivery_details_ob_grp_v wdd
1003: , wsh_delivery_assignments_v wda
1004: , wms_license_plate_numbers lpn
1005: ,wms_license_plate_numbers wlpn
1006: ,wsh_new_deliveries_ob_grp_v wndv
1007: where wdd.delivery_detail_id = wda.delivery_detail_id
1008: and wdd.lpn_id is not null
1001: , nvl(wdd.load_seq_number,0) as load_seq_num
1002: from wsh_delivery_details_ob_grp_v wdd
1003: , wsh_delivery_assignments_v wda
1004: , wms_license_plate_numbers lpn
1005: ,wms_license_plate_numbers wlpn
1006: ,wsh_new_deliveries_ob_grp_v wndv
1007: where wdd.delivery_detail_id = wda.delivery_detail_id
1008: and wdd.lpn_id is not null
1009: and wdd.lpn_id = lpn.outermost_lpn_id
1025: , get_delivery_name(wda.delivery_id)
1026: , nvl(wdd.load_seq_number,0) as load_seq_num
1027: from wsh_delivery_details_ob_grp_v wdd
1028: , wsh_delivery_assignments_v wda
1029: , wms_license_plate_numbers lpn
1030: , wms_license_plate_numbers wlpn
1031: where wdd.delivery_detail_id = wda.delivery_detail_id
1032: and wdd.lpn_id is not null
1033: and wdd.lpn_id = lpn.outermost_lpn_id
1026: , nvl(wdd.load_seq_number,0) as load_seq_num
1027: from wsh_delivery_details_ob_grp_v wdd
1028: , wsh_delivery_assignments_v wda
1029: , wms_license_plate_numbers lpn
1030: , wms_license_plate_numbers wlpn
1031: where wdd.delivery_detail_id = wda.delivery_detail_id
1032: and wdd.lpn_id is not null
1033: and wdd.lpn_id = lpn.outermost_lpn_id
1034: and wdd.released_status = 'X' -- For LPN reuse ER : 6845650
1047: , get_delivery_name(wda.delivery_id)
1048: , nvl(wdd.load_seq_number,0) as load_seq_num
1049: from wsh_delivery_details_ob_grp_v wdd
1050: , wsh_delivery_assignments_v wda
1051: , wms_license_plate_numbers lpn
1052: ,wms_license_plate_numbers wlpn
1053: ,wsh_new_deliveries_ob_grp_v wndv
1054: where wdd.delivery_detail_id = wda.delivery_detail_id
1055: and wdd.lpn_id is not null
1048: , nvl(wdd.load_seq_number,0) as load_seq_num
1049: from wsh_delivery_details_ob_grp_v wdd
1050: , wsh_delivery_assignments_v wda
1051: , wms_license_plate_numbers lpn
1052: ,wms_license_plate_numbers wlpn
1053: ,wsh_new_deliveries_ob_grp_v wndv
1054: where wdd.delivery_detail_id = wda.delivery_detail_id
1055: and wdd.lpn_id is not null
1056: and wdd.lpn_id = lpn.lpn_id
1072: , get_delivery_name(wda.delivery_id)
1073: , nvl(wdd.load_seq_number,0) as load_seq_num
1074: from wsh_delivery_details_ob_grp_v wdd
1075: , wsh_delivery_assignments_v wda
1076: , wms_license_plate_numbers lpn
1077: ,wms_license_plate_numbers wlpn
1078: where wdd.delivery_detail_id = wda.delivery_detail_id
1079: and wdd.lpn_id is not null
1080: and wdd.lpn_id = lpn.lpn_id
1073: , nvl(wdd.load_seq_number,0) as load_seq_num
1074: from wsh_delivery_details_ob_grp_v wdd
1075: , wsh_delivery_assignments_v wda
1076: , wms_license_plate_numbers lpn
1077: ,wms_license_plate_numbers wlpn
1078: where wdd.delivery_detail_id = wda.delivery_detail_id
1079: and wdd.lpn_id is not null
1080: and wdd.lpn_id = lpn.lpn_id
1081: and wdd.released_status = 'X' -- For LPN reuse ER : 6845650
1095: open x_lpn_lov for
1096: select distinct lpn.outermost_lpn_id
1097: , wlpn.license_plate_number
1098: , wnd.delivery_id,wnd.name
1099: from wms_license_plate_numbers lpn
1100: , wms_license_plate_numbers wlpn
1101: , wsh_new_deliveries_ob_grp_v wnd
1102: , wsh_delivery_legs_ob_grp_v wdl
1103: , wsh_delivery_details_ob_grp_v wdd
1096: select distinct lpn.outermost_lpn_id
1097: , wlpn.license_plate_number
1098: , wnd.delivery_id,wnd.name
1099: from wms_license_plate_numbers lpn
1100: , wms_license_plate_numbers wlpn
1101: , wsh_new_deliveries_ob_grp_v wnd
1102: , wsh_delivery_legs_ob_grp_v wdl
1103: , wsh_delivery_details_ob_grp_v wdd
1104: , wsh_delivery_assignments_v wda
1132: , nvl(wda.delivery_id,0)
1133: , get_delivery_name(wda.delivery_id)
1134: from wsh_delivery_details_ob_grp_v wdd
1135: , wsh_delivery_assignments_v wda
1136: , wms_license_plate_numbers lpn
1137: ,wms_license_plate_numbers wlpn
1138: ,wsh_new_deliveries_ob_grp_v wndv
1139: where wdd.delivery_detail_id = wda.delivery_detail_id
1140: and wdd.lpn_id is not null -- for performance, bug 2418639
1133: , get_delivery_name(wda.delivery_id)
1134: from wsh_delivery_details_ob_grp_v wdd
1135: , wsh_delivery_assignments_v wda
1136: , wms_license_plate_numbers lpn
1137: ,wms_license_plate_numbers wlpn
1138: ,wsh_new_deliveries_ob_grp_v wndv
1139: where wdd.delivery_detail_id = wda.delivery_detail_id
1140: and wdd.lpn_id is not null -- for performance, bug 2418639
1141: and wdd.lpn_id = lpn.outermost_lpn_id
1157: , nvl(wda.delivery_id,0)
1158: , get_delivery_name(wda.delivery_id)
1159: from wsh_delivery_details_ob_grp_v wdd
1160: , wsh_delivery_assignments_v wda
1161: , wms_license_plate_numbers lpn
1162: ,wms_license_plate_numbers wlpn
1163: where wdd.delivery_detail_id = wda.delivery_detail_id
1164: and wdd.lpn_id is not null -- for performance, bug 2418639
1165: and wdd.lpn_id = lpn.outermost_lpn_id
1158: , get_delivery_name(wda.delivery_id)
1159: from wsh_delivery_details_ob_grp_v wdd
1160: , wsh_delivery_assignments_v wda
1161: , wms_license_plate_numbers lpn
1162: ,wms_license_plate_numbers wlpn
1163: where wdd.delivery_detail_id = wda.delivery_detail_id
1164: and wdd.lpn_id is not null -- for performance, bug 2418639
1165: and wdd.lpn_id = lpn.outermost_lpn_id
1166: and wdd.released_status = 'X' -- For LPN reuse ER : 6845650
1213: wlpn2.license_plate_number,wlpn.lpn_id, wlpn.license_plate_number,wdd.serial_number,
1214: msik.concatenated_segments,
1215: msik.serial_number_control_code, wdd.requested_quantity,wdd.subinventory, wdd.revision,
1216: wdd.locator_id, wdd.lot_number,wdd.picked_quantity
1217: from wms_license_plate_numbers wlpn, wms_license_plate_numbers wlpn2, wsh_delivery_details_ob_grp_v wdd,
1218: mtl_system_items_kfv msik,
1219: wsh_delivery_assignments_v wda, wsh_delivery_details_ob_grp_v wdd2
1220: where wlpn.outermost_lpn_id = p_outermost_lpn_id
1221: and wlpn.lpn_id = wdd2.lpn_id
1396:
1397: cursor sum_delivery_details(p_outermost_lpn_id IN NUMBER, p_delivery_id IN NUMBER) is
1398: select sum(wdd.requested_quantity), sum(wlc.quantity), wda.delivery_id, wlc.inventory_item_id, wlc.parent_lpn_id
1399: from wsh_delivery_details_ob_grp_v wdd, wsh_delivery_assignments_v wda, wsh_delivery_details_ob_grp_v wdd2,
1400: wms_license_plate_numbers lpn, wms_lpn_contents wlc
1401: where wdd.delivery_detail_id = wda.delivery_detail_id
1402: and wdd2.delivery_detail_id = wda.parent_delivery_detail_id
1403: and wdd2.lpn_id = lpn.lpn_id
1404: and wdd2.released_status = 'X' -- For LPN reuse ER : 6845650
1475: l_delivery_id NUMBER;
1476: l_trip_id NUMBER;
1477: cursor deliveries_in_lpn is
1478: select distinct wda.delivery_id
1479: from wsh_delivery_details_ob_grp_v wdd, wsh_delivery_assignments_v wda, wms_license_plate_numbers lpn,
1480: wsh_delivery_details_ob_grp_v wdd2
1481: where lpn.outermost_lpn_id = p_outermost_lpn_id
1482: and wdd2.lpn_id = lpn.lpn_id
1483: and wdd2.released_status = 'X' -- For LPN reuse ER : 6845650
1545: l_delivery_id NUMBER;
1546: l_trip_id NUMBER;
1547: cursor deliveries_in_lpn is
1548: select distinct wda.delivery_id
1549: from wsh_delivery_details_ob_grp_v wdd, wsh_delivery_assignments_v wda, wms_license_plate_numbers lpn,
1550: wsh_delivery_details_ob_grp_v wdd2
1551: where lpn.outermost_lpn_id = p_outermost_lpn_id
1552: and wdd2.lpn_id = lpn.lpn_id
1553: and wdd2.released_status = 'X' -- For LPN reuse ER : 6845650
1668: l_prev_hdr_id NUMBER;
1669: l_prev_line_id NUMBER;
1670: cursor delivery_details( p_outermost_lpn_id NUMBER) is
1671: select wdd.delivery_detail_id, wdd.source_header_id, wdd.source_line_id
1672: from wms_license_plate_numbers wlpn, wsh_delivery_details_ob_grp_v wdd0, wsh_delivery_assignments_v wda,
1673: wsh_delivery_details_ob_grp_v wdd
1674: where
1675: wlpn.outermost_lpn_id = p_outermost_lpn_id
1676: and wlpn.lpn_id = wdd0.lpn_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
1780: START WITH lpn_id = p_outermost_lpn_id
1781: CONNECT BY parent_lpn_id = PRIOR lpn_id;
1782:
1783: l_delivery_detail_id NUMBER;
1909: if p_trip_id = 0 then
1910: select count(distinct wda.delivery_id)
1911: into l_delivery_count
1912: from wsh_delivery_assignments_v wda, wsh_delivery_details_ob_grp_v wdd
1913: , wms_license_plate_numbers wlpn
1914: where wlpn.outermost_lpn_id = p_outermost_lpn_id
1915: and wlpn.lpn_id = wdd.lpn_id
1916: and wdd.lpn_id is not null -- for performance, bug 2418639
1917: and wda.parent_delivery_detail_id = wdd.delivery_detail_id
2019: END IF;
2020: --\Shipping API cleanup
2021:
2022: -- change the LPN context
2023: UPDATE WMS_LICENSE_PLATE_NUMBERS
2024: SET lpn_context = WMS_Container_PUB.LPN_LOADED_FOR_SHIPMENT,
2025: last_update_date = SYSDATE,
2026: last_updated_by = FND_GLOBAL.USER_ID
2027: where lpn_id = p_outermost_lpn_id;
2027: where lpn_id = p_outermost_lpn_id;
2028:
2029: -- update the lpn context for all children lpns
2030: FOR l_lpn_id IN nested_children_lpn_cursor LOOP
2031: UPDATE WMS_LICENSE_PLATE_NUMBERS
2032: SET lpn_context = WMS_Container_PUB.LPN_LOADED_FOR_SHIPMENT,
2033: last_update_date = SYSDATE,
2034: last_updated_by = FND_GLOBAL.USER_ID
2035: where lpn_id = l_lpn_id.lpn_id;
3022: select DISTINCT wlpn.license_plate_number,
3023: lpn.subinventory_code,
3024: milk.concatenated_segments,
3025: wnd.name
3026: from wms_license_plate_numbers lpn, mtl_item_locations_kfv milk,wsh_trip_stops_ob_grp_v pickup_stop,
3027: wsh_delivery_legs_ob_grp_v wdl,wsh_delivery_assignments_v wda,wsh_delivery_details_ob_grp_v wdd,
3028: wsh_delivery_details_ob_grp_v wdd2,wsh_new_deliveries_ob_grp_v wnd,wms_license_plate_numbers wlpn
3029: where wdd.delivery_detail_id = wda.delivery_detail_id
3030: and wdd.released_status = 'Y'
3024: milk.concatenated_segments,
3025: wnd.name
3026: from wms_license_plate_numbers lpn, mtl_item_locations_kfv milk,wsh_trip_stops_ob_grp_v pickup_stop,
3027: wsh_delivery_legs_ob_grp_v wdl,wsh_delivery_assignments_v wda,wsh_delivery_details_ob_grp_v wdd,
3028: wsh_delivery_details_ob_grp_v wdd2,wsh_new_deliveries_ob_grp_v wnd,wms_license_plate_numbers wlpn
3029: where wdd.delivery_detail_id = wda.delivery_detail_id
3030: and wdd.released_status = 'Y'
3031: and wdd2.delivery_detail_id = wda.parent_delivery_detail_id
3032: and wda.delivery_id = wdl.delivery_id
3045: select DISTINCT wlpn.license_plate_number,
3046: lpn.subinventory_code,
3047: milk.concatenated_segments,
3048: wnd.name
3049: from wms_license_plate_numbers lpn, mtl_item_locations_kfv milk,
3050: wsh_delivery_assignments_v wda,wsh_delivery_details_ob_grp_v wdd,
3051: wsh_delivery_details_ob_grp_v wdd2,wsh_new_deliveries_ob_grp_v wnd,wms_license_plate_numbers wlpn
3052: where wdd.delivery_detail_id = wda.delivery_detail_id
3053: and wdd.released_status = 'Y'
3047: milk.concatenated_segments,
3048: wnd.name
3049: from wms_license_plate_numbers lpn, mtl_item_locations_kfv milk,
3050: wsh_delivery_assignments_v wda,wsh_delivery_details_ob_grp_v wdd,
3051: wsh_delivery_details_ob_grp_v wdd2,wsh_new_deliveries_ob_grp_v wnd,wms_license_plate_numbers wlpn
3052: where wdd.delivery_detail_id = wda.delivery_detail_id
3053: and wdd.released_status = 'Y'
3054: and wdd2.delivery_detail_id = wda.parent_delivery_detail_id
3055: and wda.delivery_id in ( select distinct delivery_id
3108: ,wsh_delivery_legs_ob_grp_v wdl
3109: ,wsh_delivery_assignments_v wda
3110: ,wsh_delivery_details_ob_grp_v wdd
3111: ,wsh_delivery_details_ob_grp_v wdd2
3112: ,wms_license_plate_numbers lpn
3113: ,wms_license_plate_numbers wlpn
3114: -- 5582189 dherring added table in from clause so that
3115: -- the status code can be checked
3116: ,wsh_new_deliveries_ob_grp_v wnd
3109: ,wsh_delivery_assignments_v wda
3110: ,wsh_delivery_details_ob_grp_v wdd
3111: ,wsh_delivery_details_ob_grp_v wdd2
3112: ,wms_license_plate_numbers lpn
3113: ,wms_license_plate_numbers wlpn
3114: -- 5582189 dherring added table in from clause so that
3115: -- the status code can be checked
3116: ,wsh_new_deliveries_ob_grp_v wnd
3117: Where pickup_stop.trip_id = p_trip_id
3143: , lpn.subinventory_code
3144: , lpn.locator_id
3145: , milk.concatenated_segments
3146: , wnd.name
3147: from wms_license_plate_numbers lpn
3148: , mtl_item_locations_kfv milk
3149: , wsh_trip_stops_ob_grp_v pickup_stop
3150: , wsh_delivery_legs_ob_grp_v wdl
3151: , wsh_delivery_assignments_v wda
3151: , wsh_delivery_assignments_v wda
3152: , wsh_delivery_details_ob_grp_v wdd
3153: , wsh_delivery_details_ob_grp_v wdd2
3154: , wsh_new_deliveries_ob_grp_v wnd
3155: , wms_license_plate_numbers wlpn
3156: where wdd.delivery_detail_id = wda.delivery_detail_id
3157: and wdd.released_status = 'Y'
3158: and wdd2.delivery_detail_id = wda.parent_delivery_detail_id
3159: and wda.delivery_id = wdl.delivery_id
3177: from wsh_delivery_assignments_v wda
3178: ,wsh_new_deliveries_ob_grp_v wnd
3179: ,wsh_delivery_details_ob_grp_v wdd
3180: ,wsh_delivery_details_ob_grp_v wdd2
3181: ,wms_license_plate_numbers lpn
3182: ,wms_license_plate_numbers wlpn
3183: where
3184: wdd.delivery_detail_id = wda.delivery_detail_id
3185: and wdd.released_status = 'Y'
3178: ,wsh_new_deliveries_ob_grp_v wnd
3179: ,wsh_delivery_details_ob_grp_v wdd
3180: ,wsh_delivery_details_ob_grp_v wdd2
3181: ,wms_license_plate_numbers lpn
3182: ,wms_license_plate_numbers wlpn
3183: where
3184: wdd.delivery_detail_id = wda.delivery_detail_id
3185: and wdd.released_status = 'Y'
3186: and wdd2.delivery_detail_id = wda.parent_delivery_detail_id
3227: , lpn.subinventory_code
3228: , lpn.locator_id
3229: , milk.concatenated_segments
3230: , wnd.name
3231: from wms_license_plate_numbers lpn
3232: , mtl_item_locations_kfv milk
3233: , wsh_delivery_assignments_v wda
3234: , wsh_delivery_details_ob_grp_v wdd
3235: , wsh_delivery_details_ob_grp_v wdd2
3233: , wsh_delivery_assignments_v wda
3234: , wsh_delivery_details_ob_grp_v wdd
3235: , wsh_delivery_details_ob_grp_v wdd2
3236: , wsh_new_deliveries_ob_grp_v wnd
3237: , wms_license_plate_numbers wlpn
3238: where wdd.delivery_detail_id = wda.delivery_detail_id
3239: and wdd.released_status = 'Y'
3240: and wdd2.delivery_detail_id = wda.parent_delivery_detail_id
3241: and wda.delivery_id in ( select distinct delivery_id
3695: SELECT DISTINCT wda.parent_delivery_detail_id inner_lpn_wdd,
3696: wlpn.outermost_lpn_id outermost_lpn_id
3697: FROM wsh_delivery_details_ob_grp_v wdd,
3698: wsh_delivery_assignments_v wda,
3699: wms_license_plate_numbers wlpn
3700: WHERE wda.delivery_id = p_delivery_id
3701: AND wda.parent_delivery_detail_id = wdd.delivery_detail_id
3702: AND wdd.lpn_id = wlpn.lpn_id;
3703:
4026: and nvl(direct_ship_flag,'N') = 'N';
4027:
4028: CURSOR nested_children_lpn_cursor(l_outermost_lpn_id NUMBER) is
4029: SELECT lpn_id
4030: FROM WMS_LICENSE_PLATE_NUMBERS
4031: START WITH lpn_id = l_outermost_lpn_id
4032: CONNECT BY parent_lpn_id = PRIOR lpn_id;
4033:
4034: cursor Deliveries_without_trip is
4307: open outermost_lpn_for_trip;
4308: LOOP
4309: fetch outermost_lpn_for_trip into l_outermost_lpn_id;
4310: exit when outermost_lpn_for_trip%NOTFOUND;
4311: UPDATE WMS_LICENSE_PLATE_NUMBERS
4312: SET lpn_context = 11, --WMS_Container_PUB.LPN_CONTEXT_INV,
4313: last_update_date = SYSDATE,
4314: last_updated_by = FND_GLOBAL.USER_ID
4315: where lpn_id = l_outermost_lpn_id;
4315: where lpn_id = l_outermost_lpn_id;
4316:
4317: -- update the lpn context for all children lpns
4318: FOR l_lpn_id IN nested_children_lpn_cursor(l_outermost_lpn_id) LOOP
4319: UPDATE WMS_LICENSE_PLATE_NUMBERS
4320: SET lpn_context = 11, --WMS_Container_PUB.LPN_CONTEXT_INV,
4321: last_update_date = SYSDATE,
4322: last_updated_by = FND_GLOBAL.USER_ID
4323: where lpn_id = l_lpn_id.lpn_id;
4346: open outermost_lpn_for_dock;
4347: LOOP
4348: fetch outermost_lpn_for_dock into l_outermost_lpn_id;
4349: exit when outermost_lpn_for_dock%NOTFOUND;
4350: UPDATE WMS_LICENSE_PLATE_NUMBERS
4351: SET lpn_context = 11, --WMS_Container_PUB.LPN_CONTEXT_INV,
4352: last_update_date = SYSDATE,
4353: last_updated_by = FND_GLOBAL.USER_ID
4354: where lpn_id = l_outermost_lpn_id;
4354: where lpn_id = l_outermost_lpn_id;
4355:
4356: -- update the lpn context for all children lpns
4357: FOR l_lpn_id IN nested_children_lpn_cursor(l_outermost_lpn_id) LOOP
4358: UPDATE WMS_LICENSE_PLATE_NUMBERS
4359: SET lpn_context = 11, --WMS_Container_PUB.LPN_CONTEXT_INV,
4360: last_update_date = SYSDATE,
4361: last_updated_by = FND_GLOBAL.USER_ID
4362: where lpn_id = l_lpn_id.lpn_id;
5356: mol.organization_id
5357: FROM wsh_delivery_details_ob_grp_v wdd,
5358: wsh_delivery_assignments_v wda,
5359: wsh_delivery_details_ob_grp_v wdd2,
5360: wms_license_plate_numbers lpn,
5361: mtl_txn_request_lines mol,
5362: oe_order_lines_all ol
5363: WHERE lpn.outermost_lpn_id = p_lpn_id
5364: AND wdd2.lpn_id = lpn.lpn_id
5557: END IF;
5558: open x_lpn_lov for
5559: select distinct wlpn.license_plate_number, wlpn.lpn_id,
5560: wlpn.subinventory_code, milk.concatenated_segments
5561: from wms_license_plate_numbers wlpn,
5562: wms_shipping_transaction_temp wstt,
5563: mtl_item_locations_kfv milk
5564: WHERE wlpn.organization_id = wstt.organization_id
5565: AND wlpn.organization_id = p_organization_id
5645: debug('In lpn_unload', 'LPN_UNLOAD');
5646: END IF;
5647:
5648: --update lpn_context
5649: UPDATE wms_license_plate_numbers
5650: SET lpn_context = wms_globals.lpn_context_picked,
5651: last_update_date = Sysdate,
5652: last_updated_by = fnd_global.user_id
5653: WHERE organization_id = p_organization_id
5909: , wlpn.gross_weight
5910: , wlpn.gross_weight_uom_code
5911: , wlpn.tare_weight
5912: , wlpn.tare_weight_uom_code
5913: FROM wms_license_plate_numbers wlpn
5914: , mtl_system_items_kfv msi
5915: WHERE wlpn.organization_id = p_organization_id
5916: AND wlpn.license_plate_number LIKE (p_lpn)
5917: AND wlpn.lpn_context = 1 /* Resides in Inventory */
5920: AND msi.organization_id(+) = wlpn.organization_id
5921: AND NOT EXISTS (SELECT 1 FROM mtl_system_items
5922: WHERE inventory_item_id IN (SELECT DISTINCT inventory_item_id
5923: FROM wms_lpn_contents
5924: WHERE parent_lpn_id IN (SELECT lpn_id FROM wms_license_plate_numbers wlpn2 WHERE wlpn2.outermost_lpn_id = wlpn.lpn_id))
5925: AND TRACKING_QUANTITY_IND <> 'PS'
5926: AND organization_id = p_organization_id)
5927: ORDER BY upper (wlpn.license_plate_number);
5928:
5935: , wlpn.gross_weight
5936: , wlpn.gross_weight_uom_code
5937: , wlpn.tare_weight
5938: , wlpn.tare_weight_uom_code
5939: FROM wms_license_plate_numbers wlpn
5940: , mtl_system_items_kfv msi
5941: WHERE wlpn.organization_id = p_organization_id
5942: AND wlpn.license_plate_number LIKE (p_lpn)
5943: AND wlpn.lpn_context = 1 /* Resides in Inventory */
6100: decode(oel.option_number,NULL,NULL,'.'||to_char(oel.option_number))||
6101: decode(oel.component_number, null, null,decode(oel.option_number, null, '.',null)||
6102: '.'||to_char(oel.component_number)) like (p_order_line)
6103: and exists (select 1
6104: from wms_license_plate_numbers lpn
6105: , wms_lpn_contents lpc
6106: where lpn.outermost_lpn_id = p_outermost_lpn_id
6107: and lpn.lpn_id = lpc.parent_lpn_id
6108: and lpc.inventory_item_id = oel.inventory_item_id
6210: decode(oel.option_number,NULL,NULL,'.'||to_char(oel.option_number))||
6211: decode(oel.component_number, null, null,decode(oel.option_number, null, '.',null)||
6212: '.'||to_char(oel.component_number)) like (p_order_line)
6213: and exists (select 1
6214: from wms_license_plate_numbers lpn
6215: , wms_lpn_contents lpc
6216: where lpn.outermost_lpn_id = p_outermost_lpn_id
6217: and lpn.lpn_id = lpc.parent_lpn_id
6218: and lpc.inventory_item_id = oel.inventory_item_id
6315: decode(oel.option_number,NULL,NULL,'.'||to_char(oel.option_number))||
6316: decode(oel.component_number, null, null,decode(oel.option_number, null, '.',null)||
6317: '.'||to_char(oel.component_number)) like (p_order_line)
6318: and exists (select 1
6319: from wms_license_plate_numbers lpn
6320: , wms_lpn_contents lpc
6321: where lpn.outermost_lpn_id = p_outermost_lpn_id
6322: and lpn.lpn_id = lpc.parent_lpn_id
6323: and lpc.inventory_item_id = oel.inventory_item_id
6426: decode(oel.option_number,NULL,NULL,'.'||to_char(oel.option_number))||
6427: decode(oel.component_number, null, null,decode(oel.option_number, null, '.',null)||
6428: '.'||to_char(oel.component_number)) like (p_order_line)
6429: and exists (select 1
6430: from wms_license_plate_numbers lpn
6431: , wms_lpn_contents lpc
6432: where lpn.outermost_lpn_id = p_outermost_lpn_id
6433: and lpn.lpn_id = lpc.parent_lpn_id
6434: and lpc.inventory_item_id = oel.inventory_item_id
6574: select distinct wlpn.license_plate_number
6575: , wlpn.lpn_id
6576: , wlpn.subinventory_code
6577: , milk.concatenated_segments
6578: from wms_license_plate_numbers wlpn
6579: , wms_shipping_transaction_temp wstt
6580: , mtl_item_locations_kfv milk
6581: WHERE wlpn.organization_id = wstt.organization_id
6582: AND wlpn.organization_id = p_organization_id
6600: )
6601: IS
6602: CURSOR lpn_id_cursor IS
6603: SELECT lpn_id
6604: FROM wms_license_plate_numbers lpn
6605: WHERE outermost_lpn_id = p_lpn_id
6606: AND organization_id = p_org_id
6607: AND lpn_context = 1;
6608:
7001: x_return_status := 'S';
7002:
7003: SELECT wlc.parent_lpn_id, wlc.inventory_item_id
7004: bulk collect INTO l_parent_lpn_id, l_inventory_item_id
7005: from wms_lpn_contents wlc, wms_license_plate_numbers wlpn, mtl_system_items_kfv msik
7006: where wlc.organization_id = p_org_id
7007: and msik.organization_id = p_org_id
7008: and wlpn.organization_id = wlc.organization_id
7009: AND wlpn.outermost_lpn_id = p_outermost_lpn_id