[Home] [Help]
337: , wdd.serial_number
338: , wdd.transaction_temp_id
339: from wms_license_plate_numbers wlpn
340: , wsh_delivery_details_ob_grp_v wdd
341: , wsh_delivery_assignments_v wda
342: , wsh_delivery_details_ob_grp_v wdd2
343: where wlpn.lpn_id = wdd2.lpn_id
344: and wlpn.outermost_lpn_id = p_lpn_id
345: and wdd2.lpn_id is not null -- for performance, bug 2418639
561: BEGIN
562: open x_delivery_detail_id for
563: select wdd.delivery_detail_id, wdd.lpn_id
564: from wsh_delivery_details_ob_grp_v wdd
565: , wsh_delivery_assignments_v wda
566: , wsh_delivery_legs_ob_grp_v wdl
567: , wsh_trip_stops_ob_grp_v pickup_stop
568: , wsh_trip_stops_ob_grp_v dropoff_stop
569: where wdd.delivery_detail_id = wda.delivery_detail_id
593: , lpn.license_plate_number
594: , wdd.locator_id
595: , wdd.released_status
596: from wsh_delivery_details_ob_grp_v wdd
597: , wsh_delivery_assignments_v wda
598: , wms_license_plate_numbers lpn
599: , wsh_delivery_details_ob_grp_v wdd2
600: where wdd2.lpn_id = lpn.lpn_id
601: and wdd2.lpn_id is not null
919: , wms_license_plate_numbers wlpn
920: , wsh_new_deliveries_ob_grp_v wnd
921: , wsh_delivery_legs_ob_grp_v wdl
922: , wsh_delivery_details_ob_grp_v wdd
923: , wsh_delivery_assignments_v wda
924: , wsh_delivery_details_ob_grp_v wdd2
925: where wdl.pick_up_stop_id = p_trip_stop_id
926: and wdl.delivery_id = wnd.delivery_id
927: and wnd.status_code in ('OP', 'PA')
946: , nvl(wda.delivery_id,0)
947: , get_delivery_name(wda.delivery_id)
948: , nvl(wdd.load_seq_number,0) as load_seq_num
949: from wsh_delivery_details_ob_grp_v wdd
950: , wsh_delivery_assignments_v wda
951: , wms_license_plate_numbers lpn
952: ,wms_license_plate_numbers wlpn
953: ,wsh_new_deliveries_ob_grp_v wndv
954: where wdd.delivery_detail_id = wda.delivery_detail_id
971: , nvl(wda.delivery_id,0)
972: , get_delivery_name(wda.delivery_id)
973: , nvl(wdd.load_seq_number,0) as load_seq_num
974: from wsh_delivery_details_ob_grp_v wdd
975: , wsh_delivery_assignments_v wda
976: , wms_license_plate_numbers lpn
977: , wms_license_plate_numbers wlpn
978: where wdd.delivery_detail_id = wda.delivery_detail_id
979: and wdd.lpn_id is not null
993: , nvl(wda.delivery_id,0)
994: , get_delivery_name(wda.delivery_id)
995: , nvl(wdd.load_seq_number,0) as load_seq_num
996: from wsh_delivery_details_ob_grp_v wdd
997: , wsh_delivery_assignments_v wda
998: , wms_license_plate_numbers lpn
999: ,wms_license_plate_numbers wlpn
1000: ,wsh_new_deliveries_ob_grp_v wndv
1001: where wdd.delivery_detail_id = wda.delivery_detail_id
1018: , nvl(wda.delivery_id,0)
1019: , get_delivery_name(wda.delivery_id)
1020: , nvl(wdd.load_seq_number,0) as load_seq_num
1021: from wsh_delivery_details_ob_grp_v wdd
1022: , wsh_delivery_assignments_v wda
1023: , wms_license_plate_numbers lpn
1024: ,wms_license_plate_numbers wlpn
1025: where wdd.delivery_detail_id = wda.delivery_detail_id
1026: and wdd.lpn_id is not null
1047: , wms_license_plate_numbers wlpn
1048: , wsh_new_deliveries_ob_grp_v wnd
1049: , wsh_delivery_legs_ob_grp_v wdl
1050: , wsh_delivery_details_ob_grp_v wdd
1051: , wsh_delivery_assignments_v wda
1052: , wsh_delivery_details_ob_grp_v wdd2
1053: where wdl.pick_up_stop_id = p_trip_stop_id
1054: and wdl.delivery_id = wnd.delivery_id
1055: --and wdl.PARENT_DELIVERY_LEG_ID IS NULL -- Added for MDC : if delivery
1078: , wlpn.license_plate_number as license_plate_number
1079: , nvl(wda.delivery_id,0)
1080: , get_delivery_name(wda.delivery_id)
1081: from wsh_delivery_details_ob_grp_v wdd
1082: , wsh_delivery_assignments_v wda
1083: , wms_license_plate_numbers lpn
1084: ,wms_license_plate_numbers wlpn
1085: ,wsh_new_deliveries_ob_grp_v wndv
1086: where wdd.delivery_detail_id = wda.delivery_detail_id
1103: , wlpn.license_plate_number as license_plate_number
1104: , nvl(wda.delivery_id,0)
1105: , get_delivery_name(wda.delivery_id)
1106: from wsh_delivery_details_ob_grp_v wdd
1107: , wsh_delivery_assignments_v wda
1108: , wms_license_plate_numbers lpn
1109: ,wms_license_plate_numbers wlpn
1110: where wdd.delivery_detail_id = wda.delivery_detail_id
1111: and wdd.lpn_id is not null -- for performance, bug 2418639
1162: msik.serial_number_control_code, wdd.requested_quantity,wdd.subinventory, wdd.revision,
1163: wdd.locator_id, wdd.lot_number,wdd.picked_quantity
1164: from wms_license_plate_numbers wlpn, wms_license_plate_numbers wlpn2, wsh_delivery_details_ob_grp_v wdd,
1165: mtl_system_items_kfv msik,
1166: wsh_delivery_assignments_v wda, wsh_delivery_details_ob_grp_v wdd2
1167: where wlpn.outermost_lpn_id = p_outermost_lpn_id
1168: and wlpn.lpn_id = wdd2.lpn_id
1169: and wdd2.lpn_id is not null -- for performance, bug 2418639
1170: and wdd2.released_status = 'X' -- For LPN reuse ER : 6845650
1337: p_outermost_lpn_id IN NUMBER) IS
1338:
1339: cursor sum_delivery_details(p_outermost_lpn_id IN NUMBER, p_delivery_id IN NUMBER) is
1340: select sum(wdd.requested_quantity), sum(wlc.quantity), wda.delivery_id, wlc.inventory_item_id, wlc.parent_lpn_id
1341: from wsh_delivery_details_ob_grp_v wdd, wsh_delivery_assignments_v wda, wsh_delivery_details_ob_grp_v wdd2,
1342: wms_license_plate_numbers lpn, wms_lpn_contents wlc
1343: where wdd.delivery_detail_id = wda.delivery_detail_id
1344: and wdd2.delivery_detail_id = wda.parent_delivery_detail_id
1345: and wdd2.lpn_id = lpn.lpn_id
1417: l_delivery_id NUMBER;
1418: l_trip_id NUMBER;
1419: cursor deliveries_in_lpn is
1420: select distinct wda.delivery_id
1421: from wsh_delivery_details_ob_grp_v wdd, wsh_delivery_assignments_v wda, wms_license_plate_numbers lpn,
1422: wsh_delivery_details_ob_grp_v wdd2
1423: where lpn.outermost_lpn_id = p_outermost_lpn_id
1424: and wdd2.lpn_id = lpn.lpn_id
1425: and wdd2.released_status = 'X' -- For LPN reuse ER : 6845650
1487: l_delivery_id NUMBER;
1488: l_trip_id NUMBER;
1489: cursor deliveries_in_lpn is
1490: select distinct wda.delivery_id
1491: from wsh_delivery_details_ob_grp_v wdd, wsh_delivery_assignments_v wda, wms_license_plate_numbers lpn,
1492: wsh_delivery_details_ob_grp_v wdd2
1493: where lpn.outermost_lpn_id = p_outermost_lpn_id
1494: and wdd2.lpn_id = lpn.lpn_id
1495: and wdd2.released_status = 'X' -- For LPN reuse ER : 6845650
1610: l_prev_hdr_id NUMBER;
1611: l_prev_line_id NUMBER;
1612: cursor delivery_details( p_outermost_lpn_id NUMBER) is
1613: select wdd.delivery_detail_id, wdd.source_header_id, wdd.source_line_id
1614: from wms_license_plate_numbers wlpn, wsh_delivery_details_ob_grp_v wdd0, wsh_delivery_assignments_v wda,
1615: wsh_delivery_details_ob_grp_v wdd
1616: where
1617: wlpn.outermost_lpn_id = p_outermost_lpn_id
1618: and wlpn.lpn_id = wdd0.lpn_id
1848: -- check if the outermost LPN contains multiple delivery
1849: if p_trip_id = 0 then
1850: select count(distinct wda.delivery_id)
1851: into l_delivery_count
1852: from wsh_delivery_assignments_v wda, wsh_delivery_details_ob_grp_v wdd
1853: , wms_license_plate_numbers wlpn
1854: where wlpn.outermost_lpn_id = p_outermost_lpn_id
1855: and wlpn.lpn_id = wdd.lpn_id
1856: and wdd.lpn_id is not null -- for performance, bug 2418639
2430: -- Add the following code in case user created delivery manually from
2431: -- desktop
2432: select wda.delivery_id, wnd.name
2433: into l_delivery_id, l_delivery_name
2434: from wsh_delivery_assignments_v wda, wsh_new_deliveries_ob_grp_v wnd
2435: where wda.delivery_id = wnd.delivery_id
2436: and wda.delivery_detail_id = l_delivery_detail_id;
2437:
2438: update wms_shipping_transaction_temp
2585:
2586: for l_del_count in 1..l_del_rows.COUNT LOOP
2587: select wda.delivery_id, wnd.name
2588: into l_delivery_id, l_delivery_name
2589: from wsh_delivery_assignments_v wda, wsh_new_deliveries_ob_grp_v wnd
2590: where wda.delivery_id = wnd.delivery_id
2591: and wda.delivery_detail_id = l_del_rows(l_del_count);
2592:
2593: update wms_shipping_transaction_temp
2962: lpn.subinventory_code,
2963: milk.concatenated_segments,
2964: wnd.name
2965: from wms_license_plate_numbers lpn, mtl_item_locations_kfv milk,wsh_trip_stops_ob_grp_v pickup_stop,
2966: wsh_delivery_legs_ob_grp_v wdl,wsh_delivery_assignments_v wda,wsh_delivery_details_ob_grp_v wdd,
2967: wsh_delivery_details_ob_grp_v wdd2,wsh_new_deliveries_ob_grp_v wnd,wms_license_plate_numbers wlpn
2968: where wdd.delivery_detail_id = wda.delivery_detail_id
2969: and wdd.released_status = 'Y'
2970: and wdd2.delivery_detail_id = wda.parent_delivery_detail_id
2985: lpn.subinventory_code,
2986: milk.concatenated_segments,
2987: wnd.name
2988: from wms_license_plate_numbers lpn, mtl_item_locations_kfv milk,
2989: wsh_delivery_assignments_v wda,wsh_delivery_details_ob_grp_v wdd,
2990: wsh_delivery_details_ob_grp_v wdd2,wsh_new_deliveries_ob_grp_v wnd,wms_license_plate_numbers wlpn
2991: where wdd.delivery_detail_id = wda.delivery_detail_id
2992: and wdd.released_status = 'Y'
2993: and wdd2.delivery_detail_id = wda.parent_delivery_detail_id
3044: select count(distinct lpn.outermost_lpn_id)
3045: into l_count
3046: from wsh_trip_stops_ob_grp_v pickup_stop
3047: ,wsh_delivery_legs_ob_grp_v wdl
3048: ,wsh_delivery_assignments_v wda
3049: ,wsh_delivery_details_ob_grp_v wdd
3050: ,wsh_delivery_details_ob_grp_v wdd2
3051: ,wms_license_plate_numbers lpn
3052: ,wms_license_plate_numbers wlpn
3086: from wms_license_plate_numbers lpn
3087: , mtl_item_locations_kfv milk
3088: , wsh_trip_stops_ob_grp_v pickup_stop
3089: , wsh_delivery_legs_ob_grp_v wdl
3090: , wsh_delivery_assignments_v wda
3091: , wsh_delivery_details_ob_grp_v wdd
3092: , wsh_delivery_details_ob_grp_v wdd2
3093: , wsh_new_deliveries_ob_grp_v wnd
3094: , wms_license_plate_numbers wlpn
3112: else
3113: BEGIN
3114: select count(distinct lpn.outermost_lpn_id)
3115: into l_count
3116: from wsh_delivery_assignments_v wda
3117: ,wsh_new_deliveries_ob_grp_v wnd
3118: ,wsh_delivery_details_ob_grp_v wdd
3119: ,wsh_delivery_details_ob_grp_v wdd2
3120: ,wms_license_plate_numbers lpn
3168: , milk.concatenated_segments
3169: , wnd.name
3170: from wms_license_plate_numbers lpn
3171: , mtl_item_locations_kfv milk
3172: , wsh_delivery_assignments_v wda
3173: , wsh_delivery_details_ob_grp_v wdd
3174: , wsh_delivery_details_ob_grp_v wdd2
3175: , wsh_new_deliveries_ob_grp_v wnd
3176: , wms_license_plate_numbers wlpn
3237: BEGIN
3238: select count(*)
3239: into l_count
3240: from wsh_delivery_details_ob_grp_v wdd
3241: , wsh_delivery_assignments_v wda
3242: , wsh_delivery_legs_ob_grp_v wdl
3243: , wsh_Trip_stops_ob_grp_v pickup_stop
3244: , mtl_system_items_kfv msik
3245: where
3270: , wdd.requested_quantity
3271: , msik.concatenated_segments
3272: , msik.description
3273: from wsh_delivery_details_ob_grp_v wdd
3274: , wsh_delivery_assignments_v wda
3275: , wsh_new_deliveries_ob_grp_v wnd
3276: , wsh_delivery_legs_ob_grp_v wdl
3277: , wsh_trip_Stops_ob_grp_v pickup_stop
3278: , mtl_system_items_kfv msik
3293: BEGIN
3294: select count(*)
3295: into l_count
3296: from wsh_delivery_details_ob_grp_v wdd
3297: , wsh_delivery_assignments_v wda
3298: , mtl_system_items_kfv msik
3299: where
3300: wda.delivery_detail_id = wdd.delivery_detail_id
3301: and wda.delivery_id in (select distinct delivery_id
3342: , wdd.requested_quantity
3343: , msiv.concatenated_segments
3344: , msiv.description
3345: from wsh_delivery_details_ob_grp_v wdd
3346: , wsh_delivery_assignments_v wda
3347: , mtl_system_items_vl msiv
3348: , wsh_new_deliveries_ob_grp_v wnd
3349: where wda.delivery_detail_id = wdd.delivery_detail_id
3350: and wdd.lpn_id is null
3543: x_return_status OUT NOCOPY VARCHAR2) IS
3544: /*
3545: cursor Delivery_Details(p_delivery_id NUMBER) is
3546: select wdd.delivery_detail_id
3547: from wsh_delivery_details_ob_grp_v wdd, wsh_delivery_assignments_v wda
3548: where wda.delivery_id = p_delivery_id
3549: and wda.delivery_detail_id = wdd.delivery_detail_id
3550: and wdd.lpn_id is NULL;
3551: */
3622: CURSOR outer_lpn(p_delivery_id number) IS
3623: SELECT DISTINCT wda.parent_delivery_detail_id inner_lpn_wdd,
3624: wlpn.outermost_lpn_id outermost_lpn_id
3625: FROM wsh_delivery_details_ob_grp_v wdd,
3626: wsh_delivery_assignments_v wda,
3627: wms_license_plate_numbers wlpn
3628: WHERE wda.delivery_id = p_delivery_id
3629: AND wda.parent_delivery_detail_id = wdd.delivery_detail_id
3630: AND wdd.lpn_id = wlpn.lpn_id;
5194: ol.line_id,
5195: wdd.released_status,
5196: mol.organization_id
5197: FROM wsh_delivery_details_ob_grp_v wdd,
5198: wsh_delivery_assignments_v wda,
5199: wsh_delivery_details_ob_grp_v wdd2,
5200: wms_license_plate_numbers lpn,
5201: mtl_txn_request_lines mol,
5202: oe_order_lines_all ol
5583: BEGIN
5584: SELECT COUNT(*)
5585: INTO l_count
5586: FROM wsh_delivery_details_ob_grp_v wdd,
5587: wsh_delivery_assignments_v wda,
5588: wsh_new_deliveries_ob_grp_v wnd,
5589: wsh_delivery_legs_ob_grp_v wdl,
5590: wsh_trip_Stops_ob_grp_v pickup_stop,
5591: mtl_system_items_kfv msik
5616: wdd.requested_quantity_uom,
5617: msik.concatenated_segments,
5618: msik.description
5619: FROM wsh_delivery_details_ob_grp_v wdd,
5620: wsh_delivery_assignments_v wda,
5621: wsh_new_deliveries_ob_grp_v wnd,
5622: wsh_delivery_legs_ob_grp_v wdl,
5623: wsh_trip_stops_ob_grp_v pickup_stop,
5624: mtl_system_items_kfv msik
5636: BEGIN
5637: SELECT COUNT(*)
5638: INTO l_count
5639: FROM wsh_delivery_details_ob_grp_v wdd,
5640: wsh_delivery_assignments_v wda,
5641: mtl_system_items_kfv
5642: msik, wsh_new_deliveries_ob_grp_v wnd
5643: WHERE wda.delivery_detail_id = wdd.delivery_detail_id
5644: AND wdd.lpn_id IS NULL
5681: wdd.requested_quantity_uom,
5682: msik.concatenated_segments,
5683: msik.description
5684: FROM wsh_delivery_details_ob_grp_v wdd,
5685: wsh_delivery_assignments_v wda,
5686: mtl_system_items_kfv
5687: msik, wsh_new_deliveries_ob_grp_v wnd
5688: WHERE wda.delivery_detail_id = wdd.delivery_detail_id
5689: AND wdd.lpn_id IS NULL