[Home] [Help]
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
395: and wlpn.outermost_lpn_id = p_lpn_id
396: and wdd2.lpn_id is not null -- for performance, bug 2418639
614: BEGIN
615: open x_delivery_detail_id for
616: select wdd.delivery_detail_id, wdd.lpn_id
617: from wsh_delivery_details_ob_grp_v wdd
618: , wsh_delivery_assignments_v wda
619: , wsh_delivery_legs_ob_grp_v wdl
620: , wsh_trip_stops_ob_grp_v pickup_stop
621: , wsh_trip_stops_ob_grp_v dropoff_stop
622: where wdd.delivery_detail_id = wda.delivery_detail_id
646: , lpn.license_plate_number
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
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
977: , wsh_delivery_details_ob_grp_v wdd2
978: where wdl.pick_up_stop_id = p_trip_stop_id
979: and wdl.delivery_id = wnd.delivery_id
980: and wnd.status_code in ('OP', 'PA')
999: , nvl(wda.delivery_id,0)
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
1024: , nvl(wda.delivery_id,0)
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
1046: , nvl(wda.delivery_id,0)
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
1071: , nvl(wda.delivery_id,0)
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
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
1105: , wsh_delivery_details_ob_grp_v wdd2
1106: where wdl.pick_up_stop_id = p_trip_stop_id
1107: and wdl.delivery_id = wnd.delivery_id
1108: --and wdl.PARENT_DELIVERY_LEG_ID IS NULL -- Added for MDC : if delivery
1131: , wlpn.license_plate_number as license_plate_number
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
1156: , wlpn.license_plate_number as license_plate_number
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
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
1222: and wdd2.lpn_id is not null -- for performance, bug 2418639
1223: and wdd2.released_status = 'X' -- For LPN reuse ER : 6845650
1395: p_outermost_lpn_id IN NUMBER) IS
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
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
1908: -- check if the outermost LPN contains multiple delivery
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
2491: -- Add the following code in case user created delivery manually from
2492: -- desktop
2493: select wda.delivery_id, wnd.name
2494: into l_delivery_id, l_delivery_name
2495: from wsh_delivery_assignments_v wda, wsh_new_deliveries_ob_grp_v wnd
2496: where wda.delivery_id = wnd.delivery_id
2497: and wda.delivery_detail_id = l_delivery_detail_id;
2498:
2499: update wms_shipping_transaction_temp
2646:
2647: for l_del_count in 1..l_del_rows.COUNT LOOP
2648: select wda.delivery_id, wnd.name
2649: into l_delivery_id, l_delivery_name
2650: from wsh_delivery_assignments_v wda, wsh_new_deliveries_ob_grp_v wnd
2651: where wda.delivery_id = wnd.delivery_id
2652: and wda.delivery_detail_id = l_del_rows(l_del_count);
2653:
2654: update wms_shipping_transaction_temp
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'
3031: and wdd2.delivery_detail_id = wda.parent_delivery_detail_id
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'
3054: and wdd2.delivery_detail_id = wda.parent_delivery_detail_id
3105: select count(distinct lpn.outermost_lpn_id)
3106: into l_count
3107: from wsh_trip_stops_ob_grp_v pickup_stop
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
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
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
3173: else
3174: BEGIN
3175: select count(distinct lpn.outermost_lpn_id)
3176: into l_count
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
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
3236: , wsh_new_deliveries_ob_grp_v wnd
3237: , wms_license_plate_numbers wlpn
3298: BEGIN
3299: select count(*)
3300: into l_count
3301: from wsh_delivery_details_ob_grp_v wdd
3302: , wsh_delivery_assignments_v wda
3303: , wsh_delivery_legs_ob_grp_v wdl
3304: , wsh_Trip_stops_ob_grp_v pickup_stop
3305: , mtl_system_items_kfv msik
3306: where
3331: , wdd.requested_quantity
3332: , msik.concatenated_segments
3333: , msik.description
3334: from wsh_delivery_details_ob_grp_v wdd
3335: , wsh_delivery_assignments_v wda
3336: , wsh_new_deliveries_ob_grp_v wnd
3337: , wsh_delivery_legs_ob_grp_v wdl
3338: , wsh_trip_Stops_ob_grp_v pickup_stop
3339: , mtl_system_items_kfv msik
3354: BEGIN
3355: select count(*)
3356: into l_count
3357: from wsh_delivery_details_ob_grp_v wdd
3358: , wsh_delivery_assignments_v wda
3359: , mtl_system_items_kfv msik
3360: where
3361: wda.delivery_detail_id = wdd.delivery_detail_id
3362: and wda.delivery_id in (select distinct delivery_id
3403: , wdd.requested_quantity
3404: , msiv.concatenated_segments
3405: , msiv.description
3406: from wsh_delivery_details_ob_grp_v wdd
3407: , wsh_delivery_assignments_v wda
3408: , mtl_system_items_vl msiv
3409: , wsh_new_deliveries_ob_grp_v wnd
3410: where wda.delivery_detail_id = wdd.delivery_detail_id
3411: and wdd.lpn_id is null
3604: x_return_status OUT NOCOPY VARCHAR2) IS
3605: /*
3606: cursor Delivery_Details(p_delivery_id NUMBER) is
3607: select wdd.delivery_detail_id
3608: from wsh_delivery_details_ob_grp_v wdd, wsh_delivery_assignments_v wda
3609: where wda.delivery_id = p_delivery_id
3610: and wda.delivery_detail_id = wdd.delivery_detail_id
3611: and wdd.lpn_id is NULL;
3612: */
3694: CURSOR outer_lpn(p_delivery_id number) IS
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;
5212: -- being known.
5213:
5214: SELECT delivery_id
5215: INTO l_delivery_id_tab(1)
5216: FROM wsh_delivery_assignments
5217: WHERE delivery_detail_id = l_delivery_details(1);
5218:
5219: BEGIN
5220: SELECT wnd.planned_flag
5220: SELECT wnd.planned_flag
5221: INTO l_planned_flag
5222: FROM wsh_new_deliveries wnd,
5223: wsh_delivery_details wdd,
5224: wsh_delivery_assignments wda
5225: WHERE wdd.delivery_detail_id = l_delivery_details(1)
5226: AND wda.delivery_detail_id = wdd.delivery_detail_id
5227: AND wda.delivery_id = wnd.delivery_id;
5228: EXCEPTION
5354: ol.line_id,
5355: wdd.released_status,
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
5760: BEGIN
5761: SELECT COUNT(*)
5762: INTO l_count
5763: FROM wsh_delivery_details_ob_grp_v wdd,
5764: wsh_delivery_assignments_v wda,
5765: wsh_new_deliveries_ob_grp_v wnd,
5766: wsh_delivery_legs_ob_grp_v wdl,
5767: wsh_trip_Stops_ob_grp_v pickup_stop,
5768: mtl_system_items_kfv msik
5793: wdd.requested_quantity_uom,
5794: msik.concatenated_segments,
5795: msik.description
5796: FROM wsh_delivery_details_ob_grp_v wdd,
5797: wsh_delivery_assignments_v wda,
5798: wsh_new_deliveries_ob_grp_v wnd,
5799: wsh_delivery_legs_ob_grp_v wdl,
5800: wsh_trip_stops_ob_grp_v pickup_stop,
5801: mtl_system_items_kfv msik
5813: BEGIN
5814: SELECT COUNT(*)
5815: INTO l_count
5816: FROM wsh_delivery_details_ob_grp_v wdd,
5817: wsh_delivery_assignments_v wda,
5818: mtl_system_items_kfv
5819: msik, wsh_new_deliveries_ob_grp_v wnd
5820: WHERE wda.delivery_detail_id = wdd.delivery_detail_id
5821: AND wdd.lpn_id IS NULL
5858: wdd.requested_quantity_uom,
5859: msik.concatenated_segments,
5860: msik.description
5861: FROM wsh_delivery_details_ob_grp_v wdd,
5862: wsh_delivery_assignments_v wda,
5863: mtl_system_items_kfv
5864: msik, wsh_new_deliveries_ob_grp_v wnd
5865: WHERE wda.delivery_detail_id = wdd.delivery_detail_id
5866: AND wdd.lpn_id IS NULL