[Home] [Help]
336: , wdd.lot_number
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
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
346: and wda.parent_delivery_detail_id = wdd2.delivery_detail_id
560: p_trip_id IN NUMBER) IS
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
592: , wda.delivery_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
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
602: and wdd2.released_status = 'X' -- For LPN reuse ER : 6845650
603: and wdd2.delivery_detail_id = wda.parent_delivery_detail_id
918: from wms_license_plate_numbers lpn
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
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')
928: and wnd.delivery_id = wda.delivery_id
945: , wlpn.license_plate_number as license_plate_number
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
970: , wlpn.license_plate_number as license_plate_number
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
992: , wlpn.license_plate_number as license_plate_number
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
1017: , wlpn.license_plate_number as license_plate_number
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
1046: from wms_license_plate_numbers lpn
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
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
1056: --is associated to a consol delivery, do not allow to select here
1077: select distinct lpn.outermost_lpn_id
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
1102: select distinct lpn.outermost_lpn_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
1148: /* select wstt.trip_id, wstt.delivery_id, wstt.delivery_detail_id, wstt.inventory_item_id, wstt.quantity, wstt.outermost_lpn,
1149: wstt.parent_lpn_id, wstt.parent_lpn, wdd.serial_number,
1150: msik.concatenated_segments, msik.serial_number_control_code, wdd.requested_quantity,
1151: wdd.subinventory, wdd.revision, wdd.locator_id, wdd.lot_number
1152: from wms_shipping_transaction_temp wstt, wsh_delivery_details_ob_grp_v wdd, mtl_system_items_kfv msik
1153: where wstt.delivery_detail_id = wdd.delivery_detail_id
1154: and wstt.outermost_lpn_id = p_outermost_lpn_id
1155: and msik.inventory_item_id = wdd.inventory_item_id
1156: and msik.organization_id = wdd.organization_id
1160: wlpn2.license_plate_number,wlpn.lpn_id, wlpn.license_plate_number,wdd.serial_number,
1161: msik.concatenated_segments,
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
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
1237: l_result := 0;
1238:
1239: select transaction_temp_id
1240: into l_transaction_temp_id
1241: from wsh_delivery_details_ob_grp_v
1242: where delivery_detail_id = l_delivery_detail_id;
1243:
1244: if( l_transaction_temp_id is null ) then
1245: select mtl_material_Transactions_s.nextval
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
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
1426: and wdd2.delivery_detail_id = wda.parent_delivery_detail_id
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
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
1496: and wdd2.lpn_id is not null -- for performance, bug 2418639
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
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
1619: and wdd0.released_status = 'X' -- For LPN reuse ER : 6845650
1729: l_loaded_dock_door VARCHAR2(2000);
1730: l_delivery_count NUMBER;
1731: l_delivery_detail_ids VARCHAR2(1024);
1732:
1733: l_detail_attributes wsh_delivery_details_pub.ChangedAttributeTabType;
1734:
1735: l_index NUMBER := 1;
1736: l_return_status VARCHAR2(1);
1737: l_msg_count NUMBER;
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
1936: 'LPN_SUBMIT');
1937: Debug('About to call Shipping API to update the shipped quantity','LPN_SUBMIT');
1938: END IF;
1939:
1940: wsh_delivery_details_pub.update_shipping_attributes
1941: (p_api_version_number => 1.0,
1942: p_init_msg_list => G_TRUE,
1943: p_commit => G_FALSE,
1944: x_return_status => l_return_status,
1948: p_source_code => 'OE');
1949:
1950: IF l_return_status <> G_RET_STS_SUCCESS THEN
1951: IF l_debug = 1 THEN
1952: debug('wsh_delivery_details_pub.update_shipping_attributes failed'
1953: || ' with status: ' || l_return_status, 'LPN_SUBMIT');
1954: debug('l_msg_count: ' || l_msg_count,'LPN_SUBMIT');
1955: debug('l_msg_data: ' || l_msg_data,'LPN_SUBMIT');
1956: END IF;
2056: * Added for g-log changes
2057: **********************************/
2058:
2059:
2060: l_ignore_for_planning wsh_delivery_details.ignore_for_planning%type;
2061: l_tms_interface_flag wsh_new_deliveries.TMS_INTERFACE_FLAG%TYPE;
2062:
2063:
2064: cursor delivery_for_trip is
2390: l_del_rows WSH_UTIL_CORE.ID_TAB_TYPE;
2391: l_delivery_ids WSH_UTIL_CORE.ID_TAB_TYPE;
2392: l_open_del_ids WSH_UTIL_CORE.ID_TAB_TYPE;
2393: l_new_del_id WSH_UTIL_CORE.ID_TAB_TYPE;
2394: l_del_detail_id WSH_DELIVERY_DETAILS_PUB.ID_TAB_TYPE;
2395: l_return_Status VARCHAR2(1);
2396: l_grouping_rows WSH_UTIL_CORE.ID_TAB_TYPE;
2397: l_del_index NUMBER;
2398: l_del_count NUMBER;
2490: IF (l_debug = 1) THEN
2491: DEBUG('The value of del_row is '||l_del_detail_id(1),'mob_ship');
2492: DEBUG('The value of del_id is '||l_open_del_ids(j),'mob_ship');
2493: END IF;
2494: WSH_DELIVERY_DETAILS_PUB.detail_to_delivery
2495: (p_api_version =>1.0
2496: ,x_return_status =>l_return_status
2497: ,x_msg_count =>l_msg_count
2498: ,x_msg_data =>l_msg_data
2535: IF (l_debug = 1) THEN
2536: DEBUG('Calling Auto Create Deliveries - ASSIGNMENT UNSUCCESSFUL','mob_ship');
2537: END IF;
2538:
2539: WSH_DELIVERY_DETAILS_PUB.autocreate_deliveries
2540: (p_api_version_number =>1.0
2541: , p_init_msg_list =>FND_API.G_FALSE
2542: , p_commit =>FND_API.G_FALSE
2543: , x_return_status =>l_return_status
2548: ELSIF l_open_del_ids.count =0 THEN
2549: IF (l_debug = 1) THEN
2550: DEBUG('cALLING aUTO CREATE DELIVIERIES -No open deliveries','mob_ship');
2551: END IF;
2552: WSH_DELIVERY_DETAILS_PUB.autocreate_deliveries
2553: (p_api_version_number =>1.0
2554: , p_init_msg_list =>FND_API.G_FALSE
2555: , p_commit =>FND_API.G_FALSE
2556: , x_return_status =>l_return_status
2774: l_msg_count NUMBER;
2775: l_msg_data VARCHAR2(2000);
2776: l_summary_buffer VARCHAR2(2000);
2777:
2778: l_ignore_for_planning wsh_delivery_details.ignore_for_planning%TYPE;
2779: l_tms_interface_flag wsh_new_deliveries.TMS_INTERFACE_FLAG%TYPE;
2780:
2781: CURSOR c_get_otm_flags is
2782: SELECT ignore_for_planning, tms_interface_flag
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
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
2971: and wda.delivery_id = wdl.delivery_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
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
2994: and wda.delivery_id in ( select distinct delivery_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
3053: -- 5582189 dherring added table in from clause so that
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
3053: -- 5582189 dherring added table in from clause so that
3054: -- the status code can be checked
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
3095: where wdd.delivery_detail_id = wda.delivery_detail_id
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
3095: where wdd.delivery_detail_id = wda.delivery_detail_id
3096: and wdd.released_status = 'Y'
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
3121: ,wms_license_plate_numbers wlpn
3122: where
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
3121: ,wms_license_plate_numbers wlpn
3122: where
3123: wdd.delivery_detail_id = wda.delivery_detail_id
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
3177: where wdd.delivery_detail_id = wda.delivery_detail_id
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
3177: where wdd.delivery_detail_id = wda.delivery_detail_id
3178: and wdd.released_status = 'Y'
3236: if( p_trip_id <> 0 ) then
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
3269: , wdd.inventory_item_id
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
3292: else
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
3341: , wdd.inventory_item_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
3391: unshipped_count NUMBER := 0;
3392: l_source_header_id NUMBER;
3393: CURSOR ship_set_in_trip(p_trip_id NUMBER) IS
3394: SELECT distinct wdd.ship_set_id
3395: FROM wsh_delivery_details_ob_grp_v wdd,wms_shipping_transaction_temp wstt
3396: WHERE wdd.delivery_detail_id = wstt.delivery_detail_id
3397: AND wdd.ship_set_id is not null
3398: AND wstt.organization_id = p_organization_id
3399: AND wstt.trip_id = p_trip_id
3401: AND nvl(wstt.direct_ship_flag,'N') = 'N';
3402:
3403: CURSOR delivered_ship_set IS
3404: SELECT distinct wdd.ship_set_id, wdd.source_header_id
3405: FROM wsh_delivery_details_ob_grp_v wdd,wms_shipping_transaction_temp wstt
3406: WHERE wdd.delivery_detail_id = wstt.delivery_detail_id
3407: AND wdd.ship_set_id is not null
3408: AND wstt.organization_id = p_organization_id
3409: AND wstt.dock_appoint_flag = 'N'
3412:
3413: CURSOR c_unshipped_count (p_ship_set_id NUMBER)
3414: IS
3415: SELECT 1
3416: FROM wsh_delivery_details_ob_grp_v wdd,
3417: oe_order_lines_all oel
3418: WHERE oel.ship_set_id = p_ship_set_id
3419: AND wdd.source_line_id = oel.line_id
3420: AND wdd.source_header_id = oel.header_id
3476: INTO unshipped_count
3477: FROM DUAL
3478: WHERE exists (
3479: SELECT 1
3480: FROM wsh_delivery_details_ob_grp_v wdd
3481: WHERE
3482: wdd.source_header_id = l_source_header_id
3483: AND wdd.ship_set_id = l_ship_set_id
3484: AND nvl(wdd.shipped_quantity,wdd.picked_quantity) is null
3496: -- Cases where the ship set is split within the same
3497: -- delivery will be caught by the missing lpn procedure.
3498: SELECT count(delivery_detail_id)
3499: INTO unshipped_count
3500: FROM wsh_delivery_details wdd
3501: WHERE wdd.released_status NOT IN ('C','X','D')
3502: AND NOT EXISTS (select 1
3503: FROM wms_shipping_transaction_temp wstt
3504: WHERE wstt.delivery_detail_id = wdd.delivery_detail_id)
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: */
3621:
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
5011: l_delivery_out_rec wsh_interface_ext_grp.del_action_out_rec_type;
5012: l_planned_flag VARCHAR2(1);
5013: /* Bug: 5585359: 10/09/06 - End*/
5014:
5015: l_delivery_details wsh_delivery_details_pub.id_tab_type;
5016: l_msg_count NUMBER;
5017: l_msg_data VARCHAR2(2000);
5018: BEGIN
5019: IF g_debug IS NULL THEN
5026: /* Bug: 5585359: 10/09/06 */
5027: l_delivery_id_tab(1) := p_delivery_id;
5028:
5029: IF l_debug = 1 THEN
5030: debug('Calling WSH_DELIVERY_DETAILS_PUB.detail_to_delivery ', 'UNASSIGN_DELIVERY_LINE');
5031: debug('delivery_detail_id : ' || p_delivery_detail_id, 'UNASSIGN_DELIVERY_LINE');
5032: debug('delivery_id : ' || p_delivery_id, 'UNASSIGN_DELIVERY_LINE');
5033: END IF;
5034: -- {{ Test a case where delivery exist before ship confirm }}
5059: BEGIN
5060: SELECT wnd.planned_flag
5061: INTO l_planned_flag
5062: FROM wsh_new_deliveries wnd,
5063: wsh_delivery_details wdd,
5064: wsh_delivery_assignments wda
5065: WHERE wdd.delivery_detail_id = l_delivery_details(1)
5066: AND wda.delivery_detail_id = wdd.delivery_detail_id
5067: AND wda.delivery_id = wnd.delivery_id;
5067: AND wda.delivery_id = wnd.delivery_id;
5068: EXCEPTION
5069: WHEN NO_DATA_FOUND THEN
5070: IF l_debug = 1 THEN
5071: debug('Calling WSH_DELIVERY_DETAILS_PUB.detail_to_delivery ', 'UNASSIGN_DELIVERY_LINE');
5072: END IF;
5073: null; -- It is OK
5074: END;
5075: END IF;
5107: debug('Unfirmed the Delivery !','UNASSIGN_DELIVERY_LINE');
5108: END IF;
5109: /* Bug: 5585359: 10/09/06 - End*/
5110:
5111: WSH_DELIVERY_DETAILS_PUB.detail_to_delivery
5112: (p_api_version => 1.0,
5113: p_init_msg_list => G_FALSE,
5114: p_commit => p_commit_flag,
5115: x_return_status => x_return_status,
5118: p_tabofdeldets => l_delivery_details,
5119: p_action => 'UNASSIGN');
5120:
5121: IF l_debug = 1 THEN
5122: debug('WSH_DELIVERY_DETAILS_PUB.detail_to_delivery '
5123: || 'return status: ' || x_return_status
5124: , 'UNASSIGN_DELIVERY_LINE');
5125:
5126: IF x_return_status <> 'S' THEN
5193: ol.header_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,
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
5203: WHERE lpn.outermost_lpn_id = p_lpn_id
5266: debug('Disassociate delivery detail with the old lpn, delivery detail id = '
5267: ||l_shipping_attr_tab(l_wdd_counter).delivery_detail_id, 'WMS_SHIPPING_TRANSACTION_PUB');
5268: END IF;
5269:
5270: WSH_DELIVERY_DETAILS_ACTIONS.Unassign_Detail_from_Cont
5271: ( P_DETAIL_ID => l_shipping_attr_tab(l_wdd_counter).delivery_detail_id,
5272: X_RETURN_STATUS => l_return_status );
5273: if (l_return_status <> fnd_api.g_ret_sts_success) then
5274: IF (l_debug = 1) THEN
5450: AND nvl(direct_ship_flag,'N') = 'N'
5451: AND outermost_lpn_id = p_outermost_lpn_id;
5452:
5453: l_index NUMBER;
5454: l_detail_attributes WSH_DELIVERY_DETAILS_PUB.ChangedAttributeTabType;
5455: l_return_status VARCHAR2(1);
5456: l_msg_data VARCHAR2(2000);
5457: l_msg_count NUMBER;
5458:
5491: l_index := l_index + 1;
5492: END LOOP;
5493:
5494: IF l_debug = 1 THEN
5495: debug('About to call wsh_delivery_details_pub.update_shipping_attributes','LPN_UNLOAD');
5496: debug('l_detail_attributes count: ' || l_detail_attributes.COUNT,'LPN_UNLOAD');
5497: END IF;
5498:
5499: wsh_delivery_details_pub.update_shipping_attributes
5495: debug('About to call wsh_delivery_details_pub.update_shipping_attributes','LPN_UNLOAD');
5496: debug('l_detail_attributes count: ' || l_detail_attributes.COUNT,'LPN_UNLOAD');
5497: END IF;
5498:
5499: wsh_delivery_details_pub.update_shipping_attributes
5500: (p_api_version_number => 1.0,
5501: p_init_msg_list => G_TRUE,
5502: p_commit => G_FALSE,
5503: x_return_status => l_return_status,
5507: p_source_code => 'OE');
5508:
5509: IF l_return_status <> G_RET_STS_SUCCESS THEN
5510: IF l_debug = 1 THEN
5511: debug('wsh_delivery_details_pub.update_shipping_attributes failed'
5512: || ' with status: ' || l_return_status, 'LPN_UNLOAD');
5513: END IF;
5514: RAISE fnd_api.g_exc_unexpected_error;
5515: END IF;
5582: END IF;
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,
5615: wdd.requested_quantity,
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,
5635: ELSE
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
5680: wdd.requested_quantity,
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
5765: , wdd.source_header_type_id
5766: , c.party_name
5767: , hca.cust_account_id
5768: , c.party_number
5769: from wsh_delivery_details_ob_grp_v wdd
5770: --, R12 TCA changes ra_customers c -- added the following tables instead
5771: , hz_parties c , hz_cust_accounts hca
5772: , oe_transaction_types_tl otl
5773: , wms_direct_ship_temp wdst
5854: , NVL(wds.processed_flag,'N') processed_flag
5855: , NVL(wds.processed_quantity,0) processed_quantity
5856: , wdd.src_requested_quantity_uom
5857: FROM oe_order_lines_all oel
5858: , wsh_delivery_details_ob_grp_v wdd
5859: , mtl_system_items_kfv msik
5860: , wms_direct_ship_temp wds
5861: WHERE oel.header_id =p_header_id
5862: and oel.ship_from_org_id = p_org_id
5884: and lpc.inventory_item_id = oel.inventory_item_id
5885: )
5886: -- Bug# 4258360: Do not include order lines with crossdocked WDD records
5887: AND NOT EXISTS (SELECT 'xdock'
5888: FROM wsh_delivery_details wdd_xdock
5889: WHERE wdd_xdock.source_header_id = oel.header_id
5890: AND wdd_xdock.source_line_id = oel.line_id
5891: AND wdd_xdock.released_status = 'S'
5892: AND wdd_xdock.move_order_line_id IS NULL)
5951: , 'N' processed_flag
5952: , 0 processed_quantity
5953: , wdd.src_requested_quantity_uom
5954: FROM oe_order_lines_all oel
5955: , wsh_delivery_details_ob_grp_v wdd
5956: , mtl_system_items_kfv msik
5957: , wms_direct_ship_temp wds
5958: WHERE oel.header_id =p_header_id
5959: and oel.ship_from_org_id = p_org_id
5964: and wdd.source_header_id = oel.header_id
5965: and wdd.source_line_id = oel.line_id
5966: and wdd.released_status in ('Y')
5967: and not exists (select 1
5968: from wsh_delivery_details wdd2
5969: where wdd.source_header_id =wdd2.source_header_id
5970: and wdd.source_line_id = wdd2.source_line_id
5971: and wdd2.released_status in ('B','X','R')
5972: )
5987: and lpc.inventory_item_id = oel.inventory_item_id
5988: )
5989: -- Bug# 4258360: Do not include order lines with crossdocked WDD records
5990: AND NOT EXISTS (SELECT 'xdock'
5991: FROM wsh_delivery_details wdd_xdock
5992: WHERE wdd_xdock.source_header_id = oel.header_id
5993: AND wdd_xdock.source_line_id = oel.line_id
5994: AND wdd_xdock.released_status = 'S'
5995: AND wdd_xdock.move_order_line_id IS NULL)
6056: , NVL(wds.processed_flag,'N') processed_flag
6057: , NVL(wds.processed_quantity,0) processed_quantity
6058: , wdd.src_requested_quantity_uom
6059: FROM oe_order_lines_all oel
6060: , wsh_delivery_details_ob_grp_v wdd
6061: , mtl_system_items_kfv msik
6062: , wms_direct_ship_temp wds
6063: WHERE oel.header_id =p_header_id
6064: and oel.ship_from_org_id = p_org_id
6087: )
6088: and NVL(oel.project_id,-1)=NVL(p_project_id,-1) and NVL(oel.task_id,-1)=NVL(p_task_id,-1)
6089: -- Bug# 4258360: Do not include order lines with crossdocked WDD records
6090: AND NOT EXISTS (SELECT 'xdock'
6091: FROM wsh_delivery_details wdd_xdock
6092: WHERE wdd_xdock.source_header_id = oel.header_id
6093: AND wdd_xdock.source_line_id = oel.line_id
6094: AND wdd_xdock.released_status = 'S'
6095: AND wdd_xdock.move_order_line_id IS NULL)
6154: , 'N' processed_flag
6155: , 0 processed_quantity
6156: , wdd.src_requested_quantity_uom
6157: FROM oe_order_lines_all oel
6158: , wsh_delivery_details_ob_grp_v wdd
6159: , mtl_system_items_kfv msik
6160: , wms_direct_ship_temp wds
6161: WHERE oel.header_id =p_header_id
6162: and oel.ship_from_org_id = p_org_id
6167: and wdd.source_header_id = oel.header_id
6168: and wdd.source_line_id = oel.line_id
6169: and wdd.released_status in ('Y')
6170: and not exists (select 1
6171: from wsh_delivery_details wdd2
6172: where wdd.source_header_id =wdd2.source_header_id
6173: and wdd.source_line_id = wdd2.source_line_id
6174: and wdd2.released_status in ('B','X','R')
6175: )
6191: )
6192: and NVL(oel.project_id,-1)=NVL(p_project_id,-1) and NVL(oel.task_id,-1)=NVL(p_task_id,-1)
6193: -- Bug# 4258360: Do not include order lines with crossdocked WDD records
6194: AND NOT EXISTS (SELECT 'xdock'
6195: FROM wsh_delivery_details wdd_xdock
6196: WHERE wdd_xdock.source_header_id = oel.header_id
6197: AND wdd_xdock.source_line_id = oel.line_id
6198: AND wdd_xdock.released_status = 'S'
6199: AND wdd_xdock.move_order_line_id IS NULL)
6409: */
6410: FUNCTION get_container_name(p_container_name IN VARCHAR2) RETURN VARCHAR2 IS
6411:
6412: CURSOR l_container IS
6413: select container_name from wsh_delivery_details_ob_grp_v
6414: where container_name like p_container_name||'-@-%';
6415:
6416: l_num_part_str VARCHAR2(30);
6417: