[Home] [Help]
PACKAGE BODY: APPS.WSH_DELIVERY_DETAILS_SPLITTER
Source
1 PACKAGE BODY WSH_DELIVERY_DETAILS_SPLITTER as
2 /* $Header: WSHDTSPB.pls 120.1.12000000.2 2007/02/01 22:48:04 jishen noship $ */
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 l_parameter_info WSH_SHIPPING_PARAMS_PVT.PARAMETER_REC_TYP;
32 l_global_param_info WSH_SHIPPING_PARAMS_PVT.GLOBAL_PARAMETERS_REC_TYP;
33 l_max_quantity WSH_DELIVERY_DETAILS.REQUESTED_QUANTITY%TYPE;
34 l_quantity WSH_DELIVERY_DETAILS.REQUESTED_QUANTITY%TYPE;
35 l_new_detail_id WSH_DELIVERY_DETAILS.DELIVERY_DETAIL_ID%TYPE;
36 l_num_split NUMBER;
37 l_msg_count NUMBER;
38 l_msg_data VARCHAR2(32767);
39 l_exception_id WSH_EXCEPTIONS.EXCEPTION_ID%TYPE;
40 l_number_of_errors NUMBER;
41 l_number_of_warnings NUMBER;
42 l_return_status VARCHAR2(1);
43 l_dd_id_tab WSH_UTIL_CORE.ID_TAB_TYPE;
44 l_temp NUMBER;
45 l_exception_message WSH_EXCEPTIONS.MESSAGE%TYPE;
46 i NUMBER;
47 split_error EXCEPTION;
48 l_gc3_is_installed VARCHAR2(1);
49 --
50 l_debug_on BOOLEAN;
51 --
52 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' ||
53 G_PACKAGE_NAME ||
54 '.' || 'TMS_DELIVERY_DETAIL_SPLIT';
55 --
56 BEGIN
57 --
58 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
59 --
60 IF l_debug_on IS NULL THEN
61 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
62 END IF;
63 --
64 IF l_debug_on THEN
65 WSH_DEBUG_SV.push(l_module_name);
66 --
67 WSH_DEBUG_SV.log(l_module_name,'P_DETAIL_TAB COUNT',p_detail_tab.COUNT);
68 WSH_DEBUG_SV.log(l_module_name,'P_ITEM_QUANTITY_UOM_TAB COUNT',p_item_quantity_uom_tab.COUNT);
69 END IF;
70 --
71 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
72
73 l_gc3_is_installed := WSH_UTIL_CORE.G_GC3_IS_INSTALLED;
74
75 IF (l_gc3_is_installed IS NULL) THEN
76 l_gc3_is_installed := WSH_UTIL_CORE.GC3_IS_INSTALLED;
77 END IF;
78
79 IF (l_gc3_is_installed = 'Y') THEN
80 --initializing
81 l_max_quantity := 0;
82 l_quantity := 0;
83 l_num_split := 0;
84 l_number_of_warnings := 0;
85 l_number_of_errors := 0;
86 l_temp := 0;
87 i := 0;
88
89 IF (p_detail_tab.COUNT = 0) THEN
90 IF l_debug_on THEN
91 WSH_DEBUG_SV.logmsg(l_module_name, 'no delivery detail to split');
92 WSH_DEBUG_SV.log(l_module_name, 'x_return_status', x_return_status);
93 WSH_DEBUG_SV.pop(l_module_name);
94 END IF;
95 RETURN;
96 END IF;
97
98 --get weight UOM from global parameters
99
100 IF l_debug_on THEN
101 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_SHIPPING_PARAMS_PVT.Get_Global_Parameters',WSH_DEBUG_SV.C_PROC_LEVEL);
102 END IF;
103
104 WSH_SHIPPING_PARAMS_PVT.get_global_parameters(
105 x_param_info => l_global_param_info,
106 x_return_status => l_return_status);
107
108 IF l_debug_on THEN
109 WSH_DEBUG_SV.logmsg(l_module_name, 'return status from WSH_SHIPPING_PARAMS_PVT.Get_Global_Parameters: ' || l_return_status);
110 END IF;
111
112 IF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
113 l_number_of_warnings := l_number_of_warnings + 1;
114 ELSIF (l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR,
115 WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)) THEN
116 FND_MESSAGE.Set_Name('WSH', 'WSH_INVALID_GLOBAL_PARAMETER');
117 wsh_util_core.add_message(l_return_status,l_module_name);
118 x_return_status := l_return_status;
119 --
120 IF l_debug_on THEN
121 WSH_DEBUG_SV.log(l_module_name, 'x_return_status', x_return_status);
122 WSH_DEBUG_SV.pop(l_module_name);
123 END IF;
124 RETURN;
125 END IF;
126
127 i:= p_detail_tab.FIRST;
128
129 WHILE i IS NOT NULL LOOP
130
131 l_temp := i;
132
133 BEGIN
134 --get net weight limit
135 IF l_debug_on THEN
136 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_SHIPPING_PARAMS_PVT.Get',WSH_DEBUG_SV.C_PROC_LEVEL);
137 END IF;
138
139 WSH_SHIPPING_PARAMS_PVT.get(
140 p_organization_id => p_detail_tab(i).organization_id,
141 x_param_info => l_parameter_info,
142 x_return_status => l_return_status);
143
144 IF l_debug_on THEN
145 WSH_DEBUG_SV.logmsg(l_module_name, 'return status from WSH_SHIPPING_PARAMS_PVT.Get: ' || l_return_status);
146 END IF;
147
148 IF (l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR,
149 WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)) THEN
150 FND_MESSAGE.Set_Name('WSH', 'WSH_PARAM_NOT_DEFINED');
151 FND_MESSAGE.Set_Token('ORGANIZAION_CODE',
152 wsh_util_core.get_org_name(p_detail_tab(i).organization_id));
153 wsh_util_core.add_message(l_return_status,l_module_name);
154 l_number_of_errors := l_number_of_errors+1;
155
156 ELSE
157
158 IF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
159 l_number_of_warnings := l_number_of_warnings + 1;
160 END IF;
161
162 IF l_debug_on THEN
163 wsh_debug_sv.log(l_module_name,'l_parameter_info.otm_enabled', l_parameter_info.otm_enabled );
164 END IF;
165
166 --Added the outer if condition to check OTM enabled at org level.
167 IF l_parameter_info.otm_enabled = 'Y' THEN --OTM R12 Org-Specific
168 --{
169 IF l_debug_on THEN
170 wsh_debug_sv.log(l_module_name,
171 'Max Net Weight',
172 l_parameter_info.max_net_weight);
173 wsh_debug_sv.log(l_module_name,
174 'Global UOM',
175 l_global_param_info.GU_WEIGHT_UOM);
176 wsh_debug_sv.log(l_module_name,
177 'detail uom',
178 p_detail_tab(i).weight_uom);
179 END IF;
180
181 IF l_parameter_info.MAX_NET_WEIGHT IS NOT NULL THEN
182 --convert the net weight limit to use delivery detail's weight UOM
183
184 IF (p_detail_tab(i).weight_uom IS NULL) THEN
185 RAISE split_error;
186 END IF;
187
188 IF (l_global_param_info.GU_WEIGHT_UOM <> p_detail_tab(i).weight_uom) THEN
189 l_parameter_info.MAX_NET_WEIGHT := WSH_WV_UTILS.convert_uom(
190 from_uom => l_global_param_info.GU_WEIGHT_UOM,
191 to_uom => p_detail_tab(i).weight_uom,
192 quantity => l_parameter_info.MAX_NET_WEIGHT);
193 END IF;
194
195 IF l_debug_on THEN
196 wsh_debug_sv.log(l_module_name,
197 'Converted Max Net Weight',
198 l_parameter_info.max_net_weight);
199
200 wsh_debug_sv.log(l_module_name,
201 'Detail Weight',
202 NVL(p_detail_tab(i).net_weight, 0));
203 END IF;
204
205 IF (NVL(p_detail_tab(i).net_weight, 0) > l_parameter_info.MAX_NET_WEIGHT) THEN
206
207 IF (l_parameter_info.MAX_NET_WEIGHT = 0) THEN
208 RAISE split_error;
209 END IF;
210
211 --here we are not checking p_detail_tab's weight is 0 or NULL because if 0 it shouldn't
212 --be over the net weight limit
213
214 --calculate ideal max quantity in a delivery
215 l_max_quantity := (l_parameter_info.MAX_NET_WEIGHT / p_detail_tab(i).net_weight) * p_detail_tab(i).quantity;
216 --number of split needed idealy
217 l_num_split := FLOOR(p_detail_tab(i).net_weight/l_parameter_info.MAX_NET_WEIGHT);
218
219 IF l_debug_on THEN
220 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DETAILS_VALIDATIONS.CHECK_DECIMAL_QUANTITY',WSH_DEBUG_SV.C_PROC_LEVEL);
221 WSH_DEBUG_SV.log(l_module_name, 'l_max_quantity', l_max_quantity);
222 WSH_DEBUG_SV.log(l_module_name, 'l_num_split', l_num_split);
223 END IF;
224
225 WSH_DETAILS_VALIDATIONS.check_decimal_quantity(
226 p_item_id => p_detail_tab(i).item_id,
227 p_organization_id => p_detail_tab(i).organization_id,
228 p_input_quantity => l_max_quantity,
229 p_uom_code => p_item_quantity_uom_tab(i),
230 x_output_quantity => l_quantity,
231 x_return_status => l_return_status);
232
233 IF l_debug_on THEN
234 wsh_debug_sv.log(l_module_name,
235 'check decimal quantity return status',
236 l_return_status);
237 WSH_DEBUG_SV.log(l_module_name, 'l_quantity', l_quantity);
238 END IF;
239
240 IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
241 RAISE split_error;
242 ELSIF (l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR) THEN
243 --indivisible quantity, calculate split num by quantity
244 l_max_quantity := FLOOR(l_quantity); --take the maximum whole number quantity since not divisible
245
246 IF (l_max_quantity = 0) THEN
247 l_num_split := 0;
248 ELSE
249 --calculate number of splits
250 l_num_split := FLOOR(p_detail_tab(i).quantity/l_max_quantity);
251 IF (MOD(p_detail_tab(i).quantity, l_max_quantity) = 0) THEN
252 --need to -1 split if the two quantity is perfectly divisible
253 l_num_split := l_num_split -1;
254 END IF;
255 END IF;
256
257 ELSE --has decimal quantity
258
259 IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING) THEN
260 l_number_of_warnings := l_number_of_warnings+1;
261 END IF;
262
263 --if there's no remainder that means the number of splits is one less
264 --than the weights divided
265 IF (MOD(p_detail_tab(i).net_weight,l_parameter_info.MAX_NET_WEIGHT) = 0) THEN
266 l_num_split := l_num_split - 1;
267 END IF;
268 END IF;
269
270 IF l_debug_on THEN
271 WSH_DEBUG_SV.log(l_module_name, 'l_num_split', l_num_split);
272 END IF;
273
274 IF (l_num_split = 0) THEN
275 RAISE split_error;
276 END IF;
277
278 l_dd_id_tab.DELETE;
279 l_new_detail_id := NULL;
280
281 IF l_debug_on THEN
282 WSH_DEBUG_SV.logmsg(l_module_name,
283 'Calling program unit WSH_DELIVERY_DETAILS_ACTIONS.SPLIT_DELIVERY_DETAILS_BULK',
284 WSH_DEBUG_SV.C_PROC_LEVEL);
285 WSH_DEBUG_SV.log(l_module_name, 'l_num_split', l_num_split);
286 END IF;
287
288 WSH_DELIVERY_DETAILS_ACTIONS.split_delivery_details_bulk (
289 p_from_detail_id => p_detail_tab(i).entity_id,
290 p_req_quantity => l_max_quantity,
291 p_num_of_split => l_num_split,
292 x_new_detail_id => l_new_detail_id,
293 x_dd_id_tab => l_dd_id_tab,
294 x_return_status => l_return_status
295 );
296
297 IF l_debug_on THEN
298 WSH_DEBUG_SV.logmsg(l_module_name, 'return status from WSH_DELIVERY_DETAILS_ACTIONS.SPLIT_DELIVERY_DETAILS_BULK: ' || l_return_status);
299 END IF;
300
301 IF (l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)) THEN
302 RAISE split_error;
303 END IF;
304
305 IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING) THEN
306 l_number_of_warnings := l_number_of_warnings+1;
307 END IF;
308
309 END IF; --detail weight > l_parameter_info.MAX_NET_WEIGHT
310 END IF; --l_parameter_info.MAX_NET_WEIGHT IS NOT NULL
311 --}
312 END IF; --l_parameter_info.otm_enabled = 'Y'
313 END IF; --l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR or UNEXP ERROR
314
315 EXCEPTION
316 WHEN split_error THEN --catching every exception and treat it as cannot split
317 --
318 l_number_of_errors := l_number_of_errors+1;
319 FND_MESSAGE.SET_NAME('WSH', 'WSH_OTM_DET_SPLIT_FAILED');
320 FND_MESSAGE.SET_TOKEN('DELIVERY_DETAIL_ID',
321 p_detail_tab(l_temp).entity_id);
322 FND_MESSAGE.SET_TOKEN('NET_WEIGHT',
323 p_detail_tab(l_temp).net_weight);
324 FND_MESSAGE.SET_TOKEN('WEIGHT_LIMIT',
325 l_parameter_info.MAX_NET_WEIGHT);
326
327 l_exception_message := FND_MESSAGE.Get;
328
329 l_exception_id := NULL;
330
331 IF l_debug_on THEN
332 WSH_DEBUG_SV.logmsg(l_module_name,
333 'Calling program unit WSH_XC_UTIL.LOG_EXCEPTION',
334 WSH_DEBUG_SV.C_PROC_LEVEL);
335 END IF;
336
337 WSH_XC_UTIL.log_exception(
338 p_api_version => 1.0,
339 x_return_status => l_return_status,
340 x_msg_count => l_msg_count,
341 x_msg_data => l_msg_data,
342 x_exception_id => l_exception_id,
343 p_exception_location_id => p_detail_tab(l_temp).init_pickup_loc_id,
344 p_logged_at_location_id => p_detail_tab(l_temp).init_pickup_loc_id,
345 p_logging_entity => 'SHIPPER',
346 p_logging_entity_id => FND_GLOBAL.USER_ID,
347 p_exception_name => 'WSH_OTM_DET_OVERSIZED',
348 p_delivery_detail_id => p_detail_tab(l_temp).entity_id,
349 p_message => SUBSTRB(l_exception_message,1,2000)
350 );
351
352 IF l_debug_on THEN
353 WSH_DEBUG_SV.logmsg(l_module_name, 'return status from WSH_XC_UTIL.log_exception: ' || l_return_status);
354 END IF;
355
356 IF l_debug_on THEN
357 wsh_debug_sv.logmsg(l_module_name,
358 'WSH_DELIVERY_DETAILS_SPLITTER.TMS_DELIVERY_DETAIL_SPLIT exception has occured ',
359 wsh_debug_sv.c_excep_level);
360 END IF;
361
362 END;
363
364 i := p_detail_tab.NEXT(i);
365
366 END LOOP;
367 END IF;
368
369 IF l_number_of_errors > 0 THEN
370 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
371 ELSIF l_number_of_warnings > 0 THEN
372 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
373 ELSE
374 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
375 END IF;
376
377 --
378 -- Debug Statements
379 --
380 IF l_debug_on THEN
381 WSH_DEBUG_SV.log(l_module_name, 'x_return_status', x_return_status);
382 WSH_DEBUG_SV.pop(l_module_name);
383 END IF;
384 --
385 EXCEPTION
386
387 WHEN OTHERS THEN
388 --
389 WSH_UTIL_CORE.default_handler(
390 'WSH_DELIVERY_DETAILS_SPLITTER.TMS_DELIVERY_DETAIL_SPLIT', l_module_name);
391 IF l_debug_on THEN
392 WSH_DEBUG_SV.logmsg(l_module_name,
393 'Unexpected error has occured. Oracle error message is '||
394 SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
395 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
396 END IF;
397 END TMS_DELIVERY_DETAIL_SPLIT;
398
399 END WSH_DELIVERY_DETAILS_SPLITTER;