[Home] [Help]
610: AND type = 'PRINTER';
611:
612: CURSOR c_order_number(l_header_id NUMBER) IS
613: SELECT source_header_number
614: FROM wsh_delivery_details
615: WHERE source_header_id = l_header_id
616: AND rownum = 1;
617:
618: l_debug_on BOOLEAN;
811: --Cursor to get the Trip stop id associated with the PickUp Stop of delivery detail line.
812: CURSOR get_dd_pup_trip_stop(v_org_id IN NUMBER, v_dd_id NUMBER) IS
813: SELECT wts.STOP_ID
814: FROM wsh_trips wt, wsh_trip_stops wts, wsh_delivery_assignments_v wda,
815: wsh_delivery_legs wdl, wsh_delivery_details wdd
816: WHERE wdd.delivery_detail_id = v_dd_id
817: AND wdd.organization_id = v_org_id
818: AND wda.delivery_detail_id = wdd.delivery_detail_id
819: AND wdl.delivery_id = wda.delivery_id
822:
823: -- Workflow Change
824: CURSOR c_get_picked_lines_count (c_delivery_detail_id NUMBER) IS
825: SELECT count (wdd.delivery_detail_id), delivery_id
826: FROM wsh_delivery_details wdd, wsh_delivery_assignments_v wda
827: WHERE wdd.delivery_detail_id = wda.delivery_detail_id
828: AND wda.delivery_id = ( SELECT delivery_id
829: FROM wsh_delivery_assignments_v
830: WHERE delivery_detail_id = c_delivery_detail_id )
1134: l_rel_delivery_detail_id(l_count_x_relstatus_details):= WSH_PR_CRITERIA.release_table(i).delivery_detail_id;
1135: --}
1136: -- X-dock, X-dock lines which have been processed above
1137: -- will have released status of S
1138: ELSIF (WSH_PR_CRITERIA.release_table(i).released_status = WSH_DELIVERY_DETAILS_PKG.C_RELEASED_TO_WAREHOUSE) THEN --{
1139: IF l_debug_on THEN
1140: WSH_DEBUG_SV.logmsg(l_module_name,'IGNORING PLANNED X-DOCK LINE FOR MOVE ORDER LINE CREATION');
1141: END IF;
1142: --}
1308: --{
1309: -- Bulk Update all Non-Transactable lines as Staged
1310: IF (l_count_x_relstatus_details > 0) THEN
1311: FOR i IN 1..l_count_x_relstatus_details LOOP
1312: UPDATE wsh_delivery_details
1313: SET released_status = 'Y',
1314: batch_id = p_batch_id,
1315: last_updated_by = l_user_id,
1316: last_update_date = l_date,
1332: END IF;
1333: --Done Raise Event: Pick To Pod Workflow
1334: END LOOP;
1335:
1336: -- Update of wsh_delivery_details where released_status is changed, call DBI API after the update.
1337: IF l_debug_on THEN
1338: WSH_DEBUG_SV.log(l_module_name,'Calling DBI API.Detail Count-',l_rel_delivery_detail_id.count);
1339: END IF;
1340: WSH_INTEGRATION.DBI_Update_Detail_Log (
1597: IF (g_trolin_tbl(i).return_status = WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
1598: IF l_debug_on THEN
1599: WSH_DEBUG_SV.logmsg(l_module_name, 'UPDATE WITH TROLIN.LINE_ID ' || G_TROLIN_TBL(I).LINE_ID );
1600: END IF;
1601: UPDATE wsh_delivery_details
1602: SET move_order_line_id = g_trolin_tbl(i).line_id,
1603: released_status = WSH_DELIVERY_DETAILS_PKG.C_RELEASED_TO_WAREHOUSE,
1604: batch_id = p_batch_id,
1605: last_updated_by = l_user_id,
1599: WSH_DEBUG_SV.logmsg(l_module_name, 'UPDATE WITH TROLIN.LINE_ID ' || G_TROLIN_TBL(I).LINE_ID );
1600: END IF;
1601: UPDATE wsh_delivery_details
1602: SET move_order_line_id = g_trolin_tbl(i).line_id,
1603: released_status = WSH_DELIVERY_DETAILS_PKG.C_RELEASED_TO_WAREHOUSE,
1604: batch_id = p_batch_id,
1605: last_updated_by = l_user_id,
1606: last_update_date = l_date,
1607: last_update_login = l_login_id,
1695: WSH_DEBUG_SV.log(l_module_name, 'g_trolin_tbl.count', g_trolin_tbl.count);
1696: WSH_DEBUG_SV.log(l_module_name, 'g_del_detail_ids.count', g_del_detail_ids.count);
1697: END IF;
1698:
1699: -- Update of wsh_delivery_details where released_status is changed, call DBI API after the update.
1700: IF l_debug_on THEN
1701: WSH_DEBUG_SV.log(l_module_name,'Calling DBI API.Detail count',l_detail_tab.count);
1702: END IF;
1703: WSH_INTEGRATION.DBI_Update_Detail_Log (
1803: l_cc_stop_attr_tab WSH_TRIP_STOPS_PVT.Stop_Attr_Tbl_Type;
1804: l_cc_in_ids WSH_UTIL_CORE.Id_Tab_Type;
1805: l_cc_fail_ids WSH_UTIL_CORE.Id_Tab_Type;
1806: -- deliveryMerge
1807: l_appended_det_tbl WSH_DELIVERY_DETAILS_UTILITIES.delivery_assignment_rec_tbl;
1808: l_unappended_det_tbl WSH_UTIL_CORE.Id_Tab_Type;
1809: l_default_ship_confirm_rule_id NUMBER := NULL;
1810: l_appended_del_tbl WSH_UTIL_CORE.Id_Tab_Type;
1811: l_tmp_del_tbl WSH_UTIL_CORE.Id_Tab_Type;
1929: END LOOP;
1930: --{
1931: IF l_debug_on THEN
1932: WSH_DEBUG_SV.log(l_module_name,'det. tab count: '|| l_id_tab_temp.count);
1933: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_DETAILS_UTILITIES.Append_to_Deliveries' ,WSH_DEBUG_SV.C_PROC_LEVEL);
1934: WSH_DEBUG_SV.log(l_module_name,'Current Time is ',SYSDATE);
1935: END IF;
1936: WSH_DELIVERY_DETAILS_UTILITIES.Append_to_Deliveries (
1937: p_delivery_detail_tbl => l_id_tab_temp,
1932: WSH_DEBUG_SV.log(l_module_name,'det. tab count: '|| l_id_tab_temp.count);
1933: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_DETAILS_UTILITIES.Append_to_Deliveries' ,WSH_DEBUG_SV.C_PROC_LEVEL);
1934: WSH_DEBUG_SV.log(l_module_name,'Current Time is ',SYSDATE);
1935: END IF;
1936: WSH_DELIVERY_DETAILS_UTILITIES.Append_to_Deliveries (
1937: p_delivery_detail_tbl => l_id_tab_temp,
1938: p_append_flag => p_append_flag,
1939: p_group_by_header => p_use_header_flag,
1940: p_commit => FND_API.G_FALSE,
1950: END IF;
1951: IF l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR
1952: OR l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR THEN
1953: x_return_status := l_return_status;
1954: WSH_UTIL_CORE.PrintMsg('Error occurred in WSH_DELIVERY_DETAILS_UTILITIES.Append_to_Deliveries');
1955: IF l_debug_on THEN
1956: WSH_DEBUG_SV.pop(l_module_name);
1957: END IF;
1958: RETURN;
2096: --{
2097: -- deliveryMerge
2098: IF l_debug_on THEN
2099: WSH_DEBUG_SV.log(l_module_name,'det. tab count: '|| l_del_details_tbl.count);
2100: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_DETAILS_UTILITIES.Append_to_Deliveries' ,WSH_DEBUG_SV.C_PROC_LEVEL);
2101: WSH_DEBUG_SV.log(l_module_name,'Current Time is ',SYSDATE);
2102: END IF;
2103: WSH_DELIVERY_DETAILS_UTILITIES.Append_to_Deliveries(
2104: p_delivery_detail_tbl => l_del_details_tbl,
2099: WSH_DEBUG_SV.log(l_module_name,'det. tab count: '|| l_del_details_tbl.count);
2100: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_DETAILS_UTILITIES.Append_to_Deliveries' ,WSH_DEBUG_SV.C_PROC_LEVEL);
2101: WSH_DEBUG_SV.log(l_module_name,'Current Time is ',SYSDATE);
2102: END IF;
2103: WSH_DELIVERY_DETAILS_UTILITIES.Append_to_Deliveries(
2104: p_delivery_detail_tbl => l_del_details_tbl,
2105: p_append_flag => p_append_flag,
2106: p_group_by_header => p_use_header_flag,
2107: p_commit => FND_API.G_FALSE,
2117: END IF;
2118: IF l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR
2119: OR l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR THEN
2120: x_return_status := l_return_status;
2121: WSH_UTIL_CORE.PrintMsg('Error occurred in WSH_DELIVERY_DETAILS_UTILITIES.Append_to_Deliveries');
2122: IF l_debug_on THEN
2123: WSH_DEBUG_SV.pop(l_module_name);
2124: END IF;
2125: RETURN;
2206: --{
2207: -- deliveryMerge
2208: IF l_debug_on THEN
2209: WSH_DEBUG_SV.log(l_module_name,'det. tab count: '|| l_del_details_tbl.count);
2210: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_DETAILS_UTILITIES.Append_to_Deliveries' ,WSH_DEBUG_SV.C_PROC_LEVEL);
2211: WSH_DEBUG_SV.log(l_module_name,'Current Time is ',SYSDATE);
2212: END IF;
2213:
2214: WSH_DELIVERY_DETAILS_UTILITIES.Append_to_Deliveries(
2210: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_DETAILS_UTILITIES.Append_to_Deliveries' ,WSH_DEBUG_SV.C_PROC_LEVEL);
2211: WSH_DEBUG_SV.log(l_module_name,'Current Time is ',SYSDATE);
2212: END IF;
2213:
2214: WSH_DELIVERY_DETAILS_UTILITIES.Append_to_Deliveries(
2215: p_delivery_detail_tbl => l_del_details_tbl,
2216: p_append_flag => p_append_flag,
2217: p_group_by_header => p_use_header_flag,
2218: p_commit => FND_API.G_FALSE,
2231: IF l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR OR
2232: l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR THEN
2233: --{
2234: x_return_status := l_return_status;
2235: WSH_UTIL_CORE.PrintMsg('Error occurred in WSH_DELIVERY_DETAILS_UTILITIES.Append_to_Deliveries');
2236: IF l_debug_on THEN
2237: WSH_DEBUG_SV.pop(l_module_name);
2238: END IF;
2239: RETURN;
3584: WHERE WND.DELIVERY_ID = v_delivery_id
3585: AND nvl(WND.SHIPMENT_DIRECTION , 'O') IN ('O', 'IO') -- J Inbound Logistics
3586: UNION ALL
3587: SELECT DISTINCT ORGANIZATION_ID
3588: FROM WSH_DELIVERY_DETAILS
3589: WHERE SOURCE_CODE = 'OE'
3590: AND RELEASED_STATUS IN ('R', 'B', 'X') -- Added For Bug-2722194 (Non-Assigned and
3591: -- Ready to release Lines -> Base Bug-2687090)
3592: AND SOURCE_HEADER_ID = v_order_hdr_id
3593: AND v_del_detail_id in (0,-1)
3594: AND v_delivery_id = 0
3595: UNION ALL
3596: SELECT DISTINCT ORGANIZATION_ID
3597: FROM WSH_DELIVERY_DETAILS
3598: WHERE DELIVERY_DETAIL_ID = v_del_detail_id
3599: AND nvl(LINE_DIRECTION , 'O') IN ('O', 'IO') -- J Inbound Logistics
3600: AND v_delivery_id = 0
3601: UNION ALL
3599: AND nvl(LINE_DIRECTION , 'O') IN ('O', 'IO') -- J Inbound Logistics
3600: AND v_delivery_id = 0
3601: UNION ALL
3602: SELECT DISTINCT WDD.ORGANIZATION_ID
3603: FROM WSH_DELIVERY_DETAILS WDD
3604: WHERE v_delivery_id = 0
3605: AND nvl(WDD.LINE_DIRECTION , 'O') IN ('O', 'IO') -- J Inbound Logistics
3606: AND v_del_detail_id in (0, -1)
3607: AND WDD.BATCH_ID = v_batch_id;
3608:
3609: --Cursor to get ship confirm rule and Packing level.
3610: CURSOR get_pack_ship_groups(v_batch_id in number) IS
3611: SELECT wsp.ship_confirm_rule_id, wsp.autopack_level
3612: FROM wsh_shipping_parameters wsp, wsh_delivery_details wdd
3613: WHERE wdd.batch_id = v_batch_id
3614: AND wsp.organization_id = wdd.organization_id
3615: GROUP BY wsp.ship_confirm_rule_id, wsp.autopack_level;
3616:
3619: --Cursor to get delivery detail attributes for ship confirm rule and Packing level.
3620: CURSOR get_dels_in_group(v_batch_id in number, v_sc_rule_id in number, v_ap_level in number) IS
3621: SELECT DISTINCT wda.delivery_id, wdd.organization_id,
3622: wdd.ship_from_location_id
3623: FROM wsh_delivery_assignments_v wda, wsh_delivery_details wdd,
3624: wsh_shipping_parameters wsp
3625: WHERE wdd.batch_id = v_batch_id
3626: AND wdd.delivery_detail_id = wda.delivery_detail_id
3627: AND wdd.organization_id = wsp.organization_id
3632: --Cursor to get delivery detail attributes for batch_id.
3633: CURSOR get_dels_in_batch(v_batch_id in number) IS
3634: SELECT DISTINCT wda.delivery_id, wdd.organization_id,
3635: wdd.ship_from_location_id
3636: FROM wsh_delivery_assignments_v wda, wsh_delivery_details wdd
3637: WHERE wdd.batch_id = v_batch_id
3638: AND wdd.delivery_detail_id = wda.delivery_detail_id
3639: AND NVL(wdd.LINE_DIRECTION , 'O') IN ('O', 'IO'); -- J Inbound Logistics
3640:
3647: CURSOR c_get_backordered_details(l_batch_id NUMBER) IS
3648: SELECT wdd.delivery_detail_id, wda.delivery_id, wda.parent_delivery_detail_id, wdd.organization_id,
3649: wdd.line_direction, wdd.gross_weight, wdd.net_weight, wdd.volume,
3650: wnd.planned_flag, wnd.batch_id
3651: FROM wsh_delivery_details wdd,
3652: wsh_delivery_assignments_v wda,
3653: wsh_new_deliveries wnd
3654: WHERE wdd.batch_id = l_batch_id
3655: AND wdd.delivery_detail_id = wda.delivery_detail_id
3666: AND type = 'DOC';
3667:
3668: CURSOR c_get_unassigned_details(l_batch_id NUMBER, l_organization_id NUMBER) IS
3669: SELECT wdd.delivery_detail_id
3670: FROM wsh_delivery_details wdd,
3671: wsh_delivery_assignments_v wda
3672: WHERE wdd.batch_id = l_batch_id
3673: AND wdd.released_status in ('S','Y')
3674: AND wdd.delivery_detail_id = wda.delivery_detail_id
3790: AND rownum = 1;
3791:
3792: CURSOR c_batch_unplanned_del(l_batch_id NUMBER) IS
3793: SELECT DISTINCT wda.delivery_id
3794: FROM wsh_delivery_details wdd,
3795: wsh_delivery_assignments_v wda,
3796: wsh_new_deliveries wnd,
3797: wsh_shipping_parameters wsp
3798: WHERE wdd.delivery_detail_id = wda.delivery_detail_id
3819:
3820: -- X-dock changes to only select which have been released to warehouse from Inventory
3821: CURSOR c_wms_details (l_batch_id NUMBER, l_organization_id NUMBER, l_mo_header_id NUMBER) IS
3822: SELECT wdd.delivery_detail_id, wdd.move_order_line_id
3823: FROM wsh_delivery_details wdd,
3824: mtl_txn_request_lines mtrl,
3825: mtl_txn_request_headers mtrh
3826: WHERE wdd.batch_id = l_batch_id
3827: AND wdd.organization_id = l_organization_id
3829: AND wdd.move_order_line_id = mtrl.line_id
3830: AND mtrl.header_id = mtrh.header_id
3831: AND mtrh.header_id = l_mo_header_id
3832: AND mtrh.move_order_type <> 6
3833: AND wdd.released_status = WSH_DELIVERY_DETAILS_PKG.C_RELEASED_TO_WAREHOUSE; -- auto pick confirm = 'N' for these lines
3834:
3835:
3836: CURSOR c_dels_in_batch(l_batch_id NUMBER, p_organization_id NUMBER) IS
3837: SELECT delivery_id
3899:
3900: --bug# 6689448 (replenishment project) : needs to pass replenishment requested lines to WMS
3901: CURSOR get_replenish_orgs(c_batch_id NUMBER) IS
3902: SELECT DISTINCT organization_id
3903: FROM wsh_delivery_details
3904: WHERE released_status = 'B'
3905: AND replenishment_status = 'R'
3906: AND batch_id = c_batch_id;
3907:
4163:
4164: IF l_mode IS NULL THEN
4165: IF l_debug_on THEN
4166: WSH_DEBUG_SV.log(l_module_name,'Current Time is ',SYSDATE);
4167: select count(*) into l_temp_num from wsh_Delivery_details where batch_id = p_batch_id;
4168: WSH_DEBUG_SV.log(l_module_name,'DDs for batch ',l_temp_num);
4169: WSH_DEBUG_SV.log(l_module_name,'G_NUM_WORKERS ',G_NUM_WORKERS);
4170: END IF;
4171: END IF;
4180: x_return_status => l_return_status);
4181:
4182: IF l_debug_on THEN
4183: WSH_DEBUG_SV.log(l_module_name,'INIT_PICK_RELEASE l_return_status',l_return_status);
4184: select count(*) into l_temp_num from wsh_Delivery_details where batch_id = p_batch_id;
4185: WSH_DEBUG_SV.log(l_module_name,'DDs for batch ',l_temp_num);
4186: WSH_DEBUG_SV.log(l_module_name,'Current Time is ',SYSDATE);
4187: END IF;
4188:
5422: --Batch ID set to NULL for all the children of Containers that did not get Pick Released.
5423:
5424: --bug# 6689448 (replenishment project): should not update batch_id value on the replenishment
5425: -- requested delivery detail lines.
5426: UPDATE wsh_delivery_details
5427: SET batch_id = NULL
5428: WHERE batch_id = g_batch_id
5429: AND ( (released_status in ('R','X'))
5430: OR ( released_status = 'B' and nvl(REPLENISHMENT_STATUS,'C') <> 'R') );
6970:
6971: --Cursor to get container attributes.
6972: CURSOR get_container(l_del_detail_id IN VARCHAR2) IS
6973: SELECT container_name, container_flag , organization_id
6974: FROM WSH_DELIVERY_DETAILS
6975: WHERE delivery_detail_id = l_del_detail_id;
6976:
6977: --Cursor to get trip status.
6978: CURSOR check_trip(c_trip_id IN NUMBER) IS
6989: select status_code, organization_id from wsh_new_deliveries
6990: where delivery_id = c_delivery_id;
6991:
6992: CURSOR check_detail (c_delivery_detail_id IN NUMBER) IS
6993: select released_status from wsh_delivery_details
6994: where delivery_detail_id = c_delivery_detail_id;
6995:
6996: --Cursor to validate auto packing.
6997: CURSOR check_detail_for_AP (c_batch_id IN NUMBER) IS
6995:
6996: --Cursor to validate auto packing.
6997: CURSOR check_detail_for_AP (c_batch_id IN NUMBER) IS
6998: select distinct p.organization_id
6999: from wsh_delivery_details d, wsh_shipping_parameters p
7000: where d.organization_id = p.organization_id
7001: and NVL(p.autopack_level,0) = 0
7002: and d.batch_id = c_batch_id;
7003:
7005: --Cursor to validate ship confirm.
7006: CURSOR check_detail_for_SC (c_batch_id IN NUMBER) IS
7007: select distinct p.organization_id, p.ship_confirm_rule_id,
7008: p.autocreate_deliveries_flag, m.mo_pick_confirm_required
7009: from wsh_delivery_details d, wsh_shipping_parameters p, mtl_parameters m
7010: where d.organization_id = p.organization_id and
7011: m.organization_id = d.organization_id
7012: and (ship_confirm_rule_id IS NULL
7013: or NVL(p.autocreate_deliveries_flag, 'N') <> 'Y'
7015: and d.batch_id = c_batch_id;
7016:
7017: --Cursor to check unassign delivery for an batch.
7018: CURSOR check_unassigned_dels(c_org_id IN NUMBER, c_batch_id IN NUMBER) IS
7019: select 1 from wsh_delivery_assignments_v a, wsh_delivery_details d
7020: where d.organization_id = c_org_id
7021: and d.batch_id = c_batch_id
7022: and d.delivery_detail_id = a.delivery_detail_id
7023: and a.delivery_id is null
7033: --and include for planning
7034: CURSOR c_is_detail_assigned(p_detail_id IN NUMBER) IS
7035: SELECT 1
7036: FROM wsh_delivery_assignments wda,
7037: wsh_delivery_details wdd
7038: WHERE wda.delivery_detail_id = p_detail_id
7039: AND wda.delivery_detail_id = wdd.delivery_detail_id
7040: AND wda.delivery_id IS NULL
7041: AND NVL(wdd.ignore_for_planning, 'N') = 'N';
7606:
7607: IF p_detail_ids.count > 0 THEN
7608: --Update the detail lines with batch id.
7609: FORALL i in 1..p_detail_ids.count
7610: update wsh_delivery_details
7611: set batch_id = l_batch_id
7612: where released_status in ('R', 'B', 'X')
7613: and nvl(replenishment_status,'C') = 'C' --bug# 6719369 (replenishment project)
7614: and delivery_detail_id = p_detail_ids(i);
8051: --Start of fix for bug 6459193
8052: IF ( l_reservation_quantity > 0 ) THEN
8053: SELECT sum(wdd.requested_quantity)
8054: INTO l_unallocated_quantity
8055: FROM wsh_delivery_details wdd
8056: WHERE wdd.released_status = 'S'
8057: AND wdd.source_code = 'OE'
8058: AND wdd.source_line_id = p_demand_source_line_id
8059: AND NOT EXISTS