[Home] [Help]
PACKAGE BODY: APPS.WSH_DELIVERY_DETAILS_SPLITTER
Source
1 PACKAGE BODY WSH_DELIVERY_DETAILS_SPLITTER as
2 /* $Header: WSHDTSPB.pls 120.3.12020000.2 2013/04/13 01:24:28 rvarghes ship $ */
3
4 G_PACKAGE_NAME CONSTANT VARCHAR2(50) := 'WSH_DELIVERY_DETAILS_SPLITTER';
5
6
7 --OTM R12
8 ------------------------------------------------------------
9 -- PROCEDURE TMS_DELIVERY_DETAIL_SPLIT
10 --
11 -- Parameters: p_detail_tab table of delivery detail information containing
12 -- id, net weight, weight uom,
13 -- pickup loc, org id, quantity, item id
14 -- p_item_quantity_uom_tab table of delivery detail requested
15 -- quantity uoms
16 -- x_return_status return status
17 --
18 -- Description: This API is used to control the delivery detail size when
19 -- it is created. it will take a table of delivery details
20 -- information and call split api until the weight of the
21 -- delivery detail is under the limit specified in the
22 -- shipping parameter. The splitting is done based on
23 -- decimal quantity of the item
24 -- The procedure returns with success if no delivery details are passed in.
25 ------------------------------------------------------------
26 PROCEDURE tms_delivery_detail_split
27 (p_detail_tab IN WSH_ENTITY_INFO_TAB,
28 p_item_quantity_uom_tab IN WSH_UTIL_CORE.COLUMN_TAB_TYPE,
29 x_return_status OUT NOCOPY VARCHAR2) IS
30
31 -- LSP PROJECT :
32 CURSOR c_get_client(p_delivery_detail_id IN NUMBER) IS
33 SELECT client_id
34 FROM wsh_delivery_details
35 WHERE delivery_detail_id = p_delivery_detail_id;
36 --
37 l_client_id NUMBER;
38 l_standalone_mode VARCHAR2(1);
39 -- LSP PROJECT : end
40 l_parameter_info WSH_SHIPPING_PARAMS_PVT.PARAMETER_REC_TYP;
41 l_global_param_info WSH_SHIPPING_PARAMS_PVT.GLOBAL_PARAMETERS_REC_TYP;
42 l_max_quantity WSH_DELIVERY_DETAILS.REQUESTED_QUANTITY%TYPE;
43 l_quantity WSH_DELIVERY_DETAILS.REQUESTED_QUANTITY%TYPE;
44 l_new_detail_id WSH_DELIVERY_DETAILS.DELIVERY_DETAIL_ID%TYPE;
45 l_num_split NUMBER;
46 l_msg_count NUMBER;
47 l_msg_data VARCHAR2(32767);
48 l_exception_id WSH_EXCEPTIONS.EXCEPTION_ID%TYPE;
49 l_number_of_errors NUMBER;
50 l_number_of_warnings NUMBER;
51 l_return_status VARCHAR2(1);
52 l_dd_id_tab WSH_UTIL_CORE.ID_TAB_TYPE;
53 l_temp NUMBER;
54 l_exception_message WSH_EXCEPTIONS.MESSAGE%TYPE;
55 i NUMBER;
56 split_error EXCEPTION;
57 l_gc3_is_installed VARCHAR2(1);
58 -- muom + 16597177
59 l_requested_quantity_uom WSH_DELIVERY_DETAILS.REQUESTED_QUANTITY_UOM%TYPE;
60 l_requested_quantity_uom2 WSH_DELIVERY_DETAILS.REQUESTED_QUANTITY_UOM2%TYPE;
61 l_max_quantity2 WSH_DELIVERY_DETAILS.REQUESTED_QUANTITY2%TYPE;
62 --
63 l_debug_on BOOLEAN;
64 --
65 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' ||
66 G_PACKAGE_NAME ||
67 '.' || 'TMS_DELIVERY_DETAIL_SPLIT';
68 --
69 BEGIN
70 --
71 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
72 --
73 IF l_debug_on IS NULL THEN
74 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
75 END IF;
76 --
77 IF l_debug_on THEN
78 WSH_DEBUG_SV.push(l_module_name);
79 --
80 WSH_DEBUG_SV.log(l_module_name,'P_DETAIL_TAB COUNT',p_detail_tab.COUNT);
81 WSH_DEBUG_SV.log(l_module_name,'P_ITEM_QUANTITY_UOM_TAB COUNT',p_item_quantity_uom_tab.COUNT);
82 END IF;
83 --
84 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
85
86 l_gc3_is_installed := WSH_UTIL_CORE.G_GC3_IS_INSTALLED;
87
88 IF (l_gc3_is_installed IS NULL) THEN
89 l_gc3_is_installed := WSH_UTIL_CORE.GC3_IS_INSTALLED;
90 END IF;
91
92 IF (l_gc3_is_installed = 'Y') THEN
93 --initializing
94 l_max_quantity := 0;
95 l_quantity := 0;
96 l_num_split := 0;
97 l_number_of_warnings := 0;
98 l_number_of_errors := 0;
99 l_temp := 0;
100 i := 0;
101
102 IF (p_detail_tab.COUNT = 0) THEN
103 IF l_debug_on THEN
104 WSH_DEBUG_SV.logmsg(l_module_name, 'no delivery detail to split');
105 WSH_DEBUG_SV.log(l_module_name, 'x_return_status', x_return_status);
106 WSH_DEBUG_SV.pop(l_module_name);
107 END IF;
108 RETURN;
109 END IF;
110
111 --get weight UOM from global parameters
112
113 IF l_debug_on THEN
114 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_SHIPPING_PARAMS_PVT.Get_Global_Parameters',WSH_DEBUG_SV.C_PROC_LEVEL);
115 END IF;
116
117 WSH_SHIPPING_PARAMS_PVT.get_global_parameters(
118 x_param_info => l_global_param_info,
119 x_return_status => l_return_status);
120
121 IF l_debug_on THEN
122 WSH_DEBUG_SV.logmsg(l_module_name, 'return status from WSH_SHIPPING_PARAMS_PVT.Get_Global_Parameters: ' || l_return_status);
123 END IF;
124
125 IF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
126 l_number_of_warnings := l_number_of_warnings + 1;
127 ELSIF (l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR,
128 WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)) THEN
129 FND_MESSAGE.Set_Name('WSH', 'WSH_INVALID_GLOBAL_PARAMETER');
130 wsh_util_core.add_message(l_return_status,l_module_name);
131 x_return_status := l_return_status;
132 --
133 IF l_debug_on THEN
134 WSH_DEBUG_SV.log(l_module_name, 'x_return_status', x_return_status);
135 WSH_DEBUG_SV.pop(l_module_name);
136 END IF;
137 RETURN;
138 END IF;
139
140 i:= p_detail_tab.FIRST;
141 l_standalone_mode := WMS_DEPLOY.WMS_DEPLOYMENT_MODE; -- LSP PROJECT
142 WHILE i IS NOT NULL LOOP
143
144 l_temp := i;
145
146 BEGIN
147 -- LSP PROJECT :Populate local table if client info is there on dd.
148 l_client_id := NULL;
149 IF l_standalone_mode = 'L' THEN
150 --{
151 OPEN c_get_client(p_detail_tab(i).entity_id);
152 FETCH c_get_client INTO l_client_id;
153 CLOSE c_get_client;
154 --}
155 END IF;
156 -- LSP PROJECT : end
157 --
158 --get net weight limit
159 IF l_debug_on THEN
160 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_SHIPPING_PARAMS_PVT.Get',WSH_DEBUG_SV.C_PROC_LEVEL);
161 END IF;
162
163 WSH_SHIPPING_PARAMS_PVT.get(
164 p_organization_id => p_detail_tab(i).organization_id,
165 p_client_id => l_client_id, -- LSP PROJECT
166 x_param_info => l_parameter_info,
167 x_return_status => l_return_status);
168
169 IF l_debug_on THEN
170 WSH_DEBUG_SV.logmsg(l_module_name, 'return status from WSH_SHIPPING_PARAMS_PVT.Get: ' || l_return_status);
171 END IF;
172
173 IF (l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR,
174 WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)) THEN
175 FND_MESSAGE.Set_Name('WSH', 'WSH_PARAM_NOT_DEFINED');
176 FND_MESSAGE.Set_Token('ORGANIZAION_CODE',
177 wsh_util_core.get_org_name(p_detail_tab(i).organization_id));
178 wsh_util_core.add_message(l_return_status,l_module_name);
179 l_number_of_errors := l_number_of_errors+1;
180
181 ELSE
182
183 IF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
184 l_number_of_warnings := l_number_of_warnings + 1;
185 END IF;
186
187 IF l_debug_on THEN
188 wsh_debug_sv.log(l_module_name,'l_parameter_info.otm_enabled', l_parameter_info.otm_enabled );
189 END IF;
190
191 --Added the outer if condition to check OTM enabled at org level.
192 IF l_parameter_info.otm_enabled = 'Y' THEN --OTM R12 Org-Specific
193 --{
194 IF l_debug_on THEN
195 -- muom + 16597177
196 wsh_debug_sv.log(l_module_name,
197 'Processing Detail',
198 p_detail_tab(i).entity_id);
199 wsh_debug_sv.log(l_module_name,
200 'Max Net Weight',
201 l_parameter_info.max_net_weight);
202 wsh_debug_sv.log(l_module_name,
203 'Global UOM',
204 l_global_param_info.GU_WEIGHT_UOM);
205 wsh_debug_sv.log(l_module_name,
206 'detail uom',
207 p_detail_tab(i).weight_uom);
208 END IF;
209
210 -- muom + 16597177
211 l_requested_quantity_uom := null;
212 l_requested_quantity_uom2 := null;
213 BEGIN
214 SELECT requested_quantity_uom, requested_quantity_uom2
215 INTO l_requested_quantity_uom, l_requested_quantity_uom2
216 FROM wsh_delivery_details
217 WHERE delivery_detail_id = p_detail_tab(i).entity_id;
218
219 EXCEPTION
220 when others then
221 null;
222 END;
223
224 IF l_parameter_info.MAX_NET_WEIGHT IS NOT NULL THEN
225 --convert the net weight limit to use delivery detail's weight UOM
226
227 IF (p_detail_tab(i).weight_uom IS NULL) THEN
228 RAISE split_error;
229 END IF;
230
231 IF (l_global_param_info.GU_WEIGHT_UOM <> p_detail_tab(i).weight_uom) THEN
232 l_parameter_info.MAX_NET_WEIGHT := WSH_WV_UTILS.convert_uom(
233 from_uom => l_global_param_info.GU_WEIGHT_UOM,
234 to_uom => p_detail_tab(i).weight_uom,
235 quantity => l_parameter_info.MAX_NET_WEIGHT);
236 END IF;
237
238 IF l_debug_on THEN
239 wsh_debug_sv.log(l_module_name,
240 'Converted Max Net Weight',
241 l_parameter_info.max_net_weight);
242
243 wsh_debug_sv.log(l_module_name,
244 'Detail Weight',
245 NVL(p_detail_tab(i).net_weight, 0));
246 END IF;
247
248 IF (NVL(p_detail_tab(i).net_weight, 0) > l_parameter_info.MAX_NET_WEIGHT) THEN
249
250 IF (l_parameter_info.MAX_NET_WEIGHT = 0) THEN
251 RAISE split_error;
252 END IF;
253
254 --here we are not checking p_detail_tab's weight is 0 or NULL because if 0 it shouldn't
255 --be over the net weight limit
256
257 --calculate ideal max quantity in a delivery
258 l_max_quantity := (l_parameter_info.MAX_NET_WEIGHT / p_detail_tab(i).net_weight) * p_detail_tab(i).quantity;
259 --number of split needed idealy
260 l_num_split := FLOOR(p_detail_tab(i).net_weight/l_parameter_info.MAX_NET_WEIGHT);
261
262 IF l_debug_on THEN
263 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DETAILS_VALIDATIONS.CHECK_DECIMAL_QUANTITY',WSH_DEBUG_SV.C_PROC_LEVEL);
264 WSH_DEBUG_SV.log(l_module_name, 'l_max_quantity', l_max_quantity);
265 WSH_DEBUG_SV.log(l_module_name, 'l_num_split', l_num_split);
266 END IF;
267
268 WSH_DETAILS_VALIDATIONS.check_decimal_quantity(
269 p_item_id => p_detail_tab(i).item_id,
270 p_organization_id => p_detail_tab(i).organization_id,
271 p_input_quantity => l_max_quantity,
272 p_uom_code => p_item_quantity_uom_tab(i),
273 x_output_quantity => l_quantity,
274 x_return_status => l_return_status);
275
276 IF l_debug_on THEN
277 wsh_debug_sv.log(l_module_name,
278 'check decimal quantity return status',
279 l_return_status);
280 WSH_DEBUG_SV.log(l_module_name, 'l_quantity', l_quantity);
281 END IF;
282
283 IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
284 RAISE split_error;
285 ELSIF (l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR) THEN
286 --indivisible quantity, calculate split num by quantity
287 l_max_quantity := FLOOR(l_quantity); --take the maximum whole number quantity since not divisible
288
289 IF (l_max_quantity = 0) THEN
290 l_num_split := 0;
291 ELSE
292 --calculate number of splits
293 l_num_split := FLOOR(p_detail_tab(i).quantity/l_max_quantity);
294 IF (MOD(p_detail_tab(i).quantity, l_max_quantity) = 0) THEN
295 --need to -1 split if the two quantity is perfectly divisible
296 l_num_split := l_num_split -1;
297 END IF;
298 END IF;
299
300 ELSE --has decimal quantity
301
302 IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING) THEN
303 l_number_of_warnings := l_number_of_warnings+1;
304 END IF;
305
306 --if there's no remainder that means the number of splits is one less
307 --than the weights divided
308 IF (MOD(p_detail_tab(i).net_weight,l_parameter_info.MAX_NET_WEIGHT) = 0) THEN
309 l_num_split := l_num_split - 1;
310 END IF;
311 END IF;
312
313 -- muom + 16597177: Derive Secondary Quantity from Calculated Primary Quantity
314 l_max_quantity2 := null;
315 IF l_requested_quantity_uom2 is not null THEN
316 IF l_debug_on THEN
317 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_WV_UTILS.CONVERT_UOM',WSH_DEBUG_SV.C_PROC_LEVEL);
318 END IF;
319 l_max_quantity2 := wsh_wv_utils.convert_uom(
320 from_uom => l_requested_quantity_uom,
321 to_uom => l_requested_quantity_uom2,
322 quantity => l_max_quantity,
323 item_id => p_detail_tab(i).item_id,
324 org_id => p_detail_tab(i).organization_id);
325 IF l_debug_on THEN
326 WSH_DEBUG_SV.logmsg(l_module_name,'l_max_quantity2 is '||l_max_quantity2);
327 END IF;
328 END IF;
329
330 IF l_debug_on THEN
331 WSH_DEBUG_SV.log(l_module_name, 'l_num_split', l_num_split);
332 END IF;
333
334 IF (l_num_split = 0) THEN
335 RAISE split_error;
336 END IF;
337
338 l_dd_id_tab.DELETE;
339 l_new_detail_id := NULL;
340
341 IF l_debug_on THEN
342 WSH_DEBUG_SV.logmsg(l_module_name,
343 'Calling program unit WSH_DELIVERY_DETAILS_ACTIONS.SPLIT_DELIVERY_DETAILS_BULK',
344 WSH_DEBUG_SV.C_PROC_LEVEL);
345 WSH_DEBUG_SV.log(l_module_name, 'l_num_split', l_num_split);
346 END IF;
347
348 WSH_DELIVERY_DETAILS_ACTIONS.split_delivery_details_bulk (
349 p_from_detail_id => p_detail_tab(i).entity_id,
350 p_req_quantity => l_max_quantity,
351 -- muom + 16597177
352 p_req_quantity2 => l_max_quantity2,
353 p_num_of_split => l_num_split,
354 x_new_detail_id => l_new_detail_id,
355 x_dd_id_tab => l_dd_id_tab,
356 x_return_status => l_return_status
357 );
358
359 IF l_debug_on THEN
360 WSH_DEBUG_SV.logmsg(l_module_name, 'return status from WSH_DELIVERY_DETAILS_ACTIONS.SPLIT_DELIVERY_DETAILS_BULK: ' || l_return_status);
361 END IF;
362
363 IF (l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)) THEN
364 RAISE split_error;
365 END IF;
366
367 IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING) THEN
368 l_number_of_warnings := l_number_of_warnings+1;
369 END IF;
370
371 END IF; --detail weight > l_parameter_info.MAX_NET_WEIGHT
372 END IF; --l_parameter_info.MAX_NET_WEIGHT IS NOT NULL
373 --}
374 END IF; --l_parameter_info.otm_enabled = 'Y'
375 END IF; --l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR or UNEXP ERROR
376
377 EXCEPTION
378 WHEN split_error THEN --catching every exception and treat it as cannot split
379 --
380 l_number_of_errors := l_number_of_errors+1;
381 FND_MESSAGE.SET_NAME('WSH', 'WSH_OTM_DET_SPLIT_FAILED');
382 FND_MESSAGE.SET_TOKEN('DELIVERY_DETAIL_ID',
383 p_detail_tab(l_temp).entity_id);
384 FND_MESSAGE.SET_TOKEN('NET_WEIGHT',
385 p_detail_tab(l_temp).net_weight);
386 FND_MESSAGE.SET_TOKEN('WEIGHT_LIMIT',
387 l_parameter_info.MAX_NET_WEIGHT);
388
389 l_exception_message := FND_MESSAGE.Get;
390
391 l_exception_id := NULL;
392
393 IF l_debug_on THEN
394 WSH_DEBUG_SV.logmsg(l_module_name,
395 'Calling program unit WSH_XC_UTIL.LOG_EXCEPTION',
396 WSH_DEBUG_SV.C_PROC_LEVEL);
397 END IF;
398
399 WSH_XC_UTIL.log_exception(
400 p_api_version => 1.0,
401 x_return_status => l_return_status,
402 x_msg_count => l_msg_count,
403 x_msg_data => l_msg_data,
404 x_exception_id => l_exception_id,
405 p_exception_location_id => p_detail_tab(l_temp).init_pickup_loc_id,
406 p_logged_at_location_id => p_detail_tab(l_temp).init_pickup_loc_id,
407 p_logging_entity => 'SHIPPER',
408 p_logging_entity_id => FND_GLOBAL.USER_ID,
409 p_exception_name => 'WSH_OTM_DET_OVERSIZED',
410 p_delivery_detail_id => p_detail_tab(l_temp).entity_id,
411 p_message => SUBSTRB(l_exception_message,1,2000)
412 );
413
414 IF l_debug_on THEN
415 WSH_DEBUG_SV.logmsg(l_module_name, 'return status from WSH_XC_UTIL.log_exception: ' || l_return_status);
416 END IF;
417
418 IF l_debug_on THEN
419 wsh_debug_sv.logmsg(l_module_name,
420 'WSH_DELIVERY_DETAILS_SPLITTER.TMS_DELIVERY_DETAIL_SPLIT exception has occured ',
421 wsh_debug_sv.c_excep_level);
422 END IF;
423
424 END;
425
426 i := p_detail_tab.NEXT(i);
427
428 END LOOP;
429 END IF;
430
431 IF l_number_of_errors > 0 THEN
432 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
433 ELSIF l_number_of_warnings > 0 THEN
434 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
435 ELSE
436 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
437 END IF;
438
439 --
440 -- Debug Statements
441 --
442 IF l_debug_on THEN
443 WSH_DEBUG_SV.log(l_module_name, 'x_return_status', x_return_status);
444 WSH_DEBUG_SV.pop(l_module_name);
445 END IF;
446 --
447 EXCEPTION
448
449 WHEN OTHERS THEN
450 --
451 WSH_UTIL_CORE.default_handler(
452 'WSH_DELIVERY_DETAILS_SPLITTER.TMS_DELIVERY_DETAIL_SPLIT', l_module_name);
453 IF l_debug_on THEN
454 WSH_DEBUG_SV.logmsg(l_module_name,
455 'Unexpected error has occured. Oracle error message is '||
456 SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
457 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
458 END IF;
459 END TMS_DELIVERY_DETAIL_SPLIT;
460
461 END WSH_DELIVERY_DETAILS_SPLITTER;