1 PACKAGE BODY WSH_TP_RELEASE as
2 /* $Header: WSHTPRLB.pls 120.13 2010/09/22 08:53:48 brana ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(50) := 'WSH_TP_RELEASE';
5
6 c_routing_firm_flag CONSTANT VARCHAR2(1) := 'F';
7 G_lc_days_profile VARCHAR2(10);
8 G_earliest_profile VARCHAR2(30);
9 G_LDD_profile VARCHAR2(30);
10 G_populate_date_profile VARCHAR2(1); -- Bug 4368984
11
12 TYPE Distinct_Ids_tab IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
13 -- START
14 -- Package for Transportation Planning Actions specific code
15 -- END
16
17 -- START
18 -- PROCEDURE change_ignoreplan_status
19 --
20 -- 1. Actions Ignore for Planning, Include for Planning can onlybe performed from the topmost entity.
21 -- If there is a line assigned to a delivery which is assigned to a trip, the action can only be
22 -- performed from the trip.
23 -- 2. If the user performs Ignore for Planning from the trip, the flag must get cascaded down to
24 -- all the deliveries, lines of that trip. If there are other trips associated with the deliveries
25 -- with a different ignore for planning, error. If there are multiple trips associated with
26 -- a delivery, all the trips must be selected and the action performed.
27 -- 3. TPW, Carrier Manifest lines, dels have to be always marked as ignore_for_plan
28 -- 4. When OTM is installed, third party instance lines can become included
29 -- for planning.
30 --
31 -- IMPORTANT NOTE (from bugs 5746444 and 5746110):
32 -- No new internal calls to this API should be added; the new calls
33 -- should go through the group API. This is to enforce validation
34 -- consistently because this API does not have consistent validation when
35 -- entity is TRIP. At this time (January 2007), there is no internal call
36 -- for this entity.
37 --
38 -- END
39
40 PROCEDURE change_ignoreplan_status
41 (p_entity IN VARCHAR2, --'DLVY', 'DLVB', 'TRIP' dep on place from which it is called
42 p_in_ids IN wsh_util_core.id_tab_type, -- table of ids of above entity
43 p_action_code IN VARCHAR2, -- either 'IGNORE_PLAN', 'INCLUDE_PLAN'
44 x_return_status OUT NOCOPY VARCHAR2) IS
45
46 CURSOR c_get_deliveries (p_tripid NUMBER) IS
47 SELECT dl.delivery_id, dl.planned_flag, dl.status_code, dl.ignore_for_planning, dl.name delivery_name,
48 dl.organization_id organization_id, -- LPN CONV. rv
49 nvl(dl.shipment_direction,'O') shipment_direction -- LPN CONV. rv
50 FROM wsh_trips t, wsh_trip_stops st, wsh_delivery_legs dg, wsh_new_deliveries dl
51 WHERE t.trip_id = p_tripid AND
52 st.trip_id = t.trip_id AND
53 dg.pick_up_stop_id = st.stop_id AND
54 dl.delivery_id = dg.delivery_id;
55
56 CURSOR c_get_dels_diff_ignoreflag (p_delid NUMBER, p_tripid NUMBER, p_ignoreplan VARCHAR2) IS
57 SELECT dl.name delivery_name, t.name trip_name
58 FROM wsh_trips t, wsh_trip_stops st, wsh_delivery_legs dg, wsh_new_deliveries dl
59 WHERE t.trip_id <> p_tripid AND
60 st.trip_id = t.trip_id AND
61 dg.pick_up_stop_id = st.stop_id AND
62 dl.delivery_id = dg.delivery_id AND
63 dl.delivery_id=p_delid AND
64 (nvl(t.ignore_for_planning,'N')<>p_ignoreplan);
65 -- OR t.planned_flag='F'); --change in design - firm check not needed
66
67 CURSOR c_get_del_trips (p_delid NUMBER, p_tripid NUMBER) IS
68 SELECT t.trip_id, t.name trip_name
69 FROM wsh_trips t, wsh_trip_stops st, wsh_delivery_legs dg
70 WHERE t.trip_id <> p_tripid AND
71 st.trip_id = t.trip_id AND
72 dg.pick_up_stop_id = st.stop_id AND
73 dg.delivery_id=p_delid;
74 -- AND t.planned_flag <> 'F'; --change in design - firm check not needed
75
76 CURSOR c_get_del_ignoreflag_difftrip (p_delid NUMBER, p_ignoreplan VARCHAR2) IS
77 SELECT dl.name delivery_name, t.name trip_name
78 FROM wsh_trips t, wsh_trip_stops st, wsh_delivery_legs dg, wsh_new_deliveries dl
79 WHERE st.trip_id = t.trip_id AND
80 dg.pick_up_stop_id = st.stop_id AND
81 dl.delivery_id = dg.delivery_id AND
82 dl.delivery_id=p_delid AND
83 ( nvl(t.ignore_for_planning, 'N')<>p_ignoreplan);
84 -- OR t.planned_flag='F'); --change in design - firm check not needed
85
86 CURSOR c_get_det_ignoreflag_diff_del (p_detailid NUMBER, p_ignoreplan VARCHAR2) IS
87 SELECT wnd.name delivery_name
88 FROM wsh_delivery_assignments_v wda, wsh_new_deliveries wnd
89 WHERE wda.delivery_id = wnd.delivery_id AND
90 wda.delivery_id IS NOT NULL AND
91 wda.delivery_detail_id=p_detailid AND
92 (nvl(wnd.ignore_for_planning, 'N')<>p_ignoreplan);
93 -- OR wnd.planned_flag IN ('F', 'Y'));--change in design - firm check not needed
94
95 CURSOR c_get_lines(p_delid NUMBER) IS
96 SELECT dd.delivery_detail_id, dd.ignore_for_planning, dd.source_code, dd.container_flag,
97 nvl(dd.line_direction,'O') line_direction, organization_id -- LPN CONV. rv
98 FROM wsh_delivery_details dd,
99 wsh_delivery_assignments_v da
100 WHERE da.delivery_id = p_delid AND
101 da.delivery_id IS NOT NULL AND
102 da.delivery_detail_id = dd.delivery_detail_id;
103
104 CURSOR c_get_det_org (p_detid NUMBER) IS
105 select organization_id, source_code, container_flag
106 from wsh_delivery_details
107 where delivery_detail_id=p_detid;
108
109 CURSOR c_get_del_org (p_delid NUMBER) IS
110 select organization_id, name delivery_name, delivery_type
111 from wsh_new_deliveries
112 where delivery_id=p_delid;
113
114 CURSOR c_get_container (p_detailid NUMBER) IS
115 select container_flag, container_name,
116 organization_id,
117 nvl(line_direction,'O') line_direction -- LPN CONV. rv
118 from wsh_delivery_details
119 where delivery_detail_id=p_detailid
120 and container_flag='Y';
121
122 CURSOR c_get_cont_lines(p_detailid NUMBER) IS
123 SELECT delivery_detail_id
124 FROM wsh_delivery_assignments_v
125 START WITH delivery_detail_id =p_detailid
126 CONNECT BY prior delivery_detail_id = parent_delivery_detail_id
127 and rownum < 10;
128
129 --see if detail is assigned to a container and a diff ignore/include plan action is being performed. if so, ask user to do it from topmost entity.
130 CURSOR c_get_det_ignoreflag_diff_cont (p_detailid NUMBER, p_ignoreplan VARCHAR2) IS
131 SELECT 'Y'
132 FROM wsh_delivery_assignments_v wda, wsh_delivery_details wdd
133 WHERE wda.delivery_detail_id = wdd.delivery_detail_id AND
134 wda.parent_delivery_detail_id IS NOT NULL AND
135 wdd.delivery_detail_id=p_detailid AND
136 nvl(wdd.ignore_for_planning,'N')<>p_ignoreplan;
137
138
139 l_del_cur c_get_deliveries%ROWTYPE;
140 --l_del_forlines_cur c_get_del_forlines%ROWTYPE;
141 l_lines_cur c_get_lines%ROWTYPE;
142 l_tmp_trip_ids wsh_util_core.id_tab_type;
143 l_tmp_del_ids wsh_util_core.id_tab_type;
144 l_tmp_det_ids wsh_util_core.id_tab_type;
145 l_tmp_detail_ids wsh_util_core.id_tab_type;
146 l_ignoreplan VARCHAR2(1);
147 l_is_container VARCHAR2(1);
148 l_container_name wsh_delivery_details.container_name%TYPE;
149 l_num_error NUMBER :=0;
150 l_warn NUMBER :=0;
151 l_return_status VARCHAR2 (1);
152 l_wh_type VARCHAR2(3);
153 l_okay VARCHAR2(1);
154 l_entity VARCHAR2(2000);
155 others EXCEPTION;
156 l_batch_size NUMBER:=10000;
157
158 --OTM R12, glog proj
159 l_delivery_info_tab WSH_NEW_DELIVERIES_PVT.DELIVERY_ATTR_TBL_TYPE;
160 l_delivery_info WSH_NEW_DELIVERIES_PVT.DELIVERY_REC_TYPE;
161 l_new_interface_flag_tab WSH_UTIL_CORE.COLUMN_TAB_TYPE;
162 l_new_version_number_tab WSH_UTIL_CORE.ID_TAB_TYPE;
163 l_temp NUMBER;
164 l_msg_count NUMBER;
165 l_msg_data VARCHAR2(2000);
166 l_del_trip_tab WMS_SHIPPING_INTERFACE_GRP.G_DLVY_TRIP_TBL;
167 l_tms_update VARCHAR2(1);
168 l_gc3_is_installed VARCHAR2(1);
169 l_is_delivery_empty VARCHAR2(1);
170 l_tp_plan_name_update VARCHAR2(1);
171
172 --end of OTM R12, glog proj
173
174 -- LPN CONV. rv
175 l_wms_org VARCHAR2(10) := 'N';
176 l_sync_tmp_wms_recTbl wsh_glbl_var_strct_grp.sync_tmp_recTbl_type;
177 l_sync_tmp_inv_recTbl wsh_glbl_var_strct_grp.sync_tmp_recTbl_type;
178 TYPE T_V1 is TABLE OF VARCHAR2(01);
179
180 l_cont_flag_tbl wsh_util_core.Column_Tab_Type;
181 l_line_dir_tbl wsh_util_core.Column_Tab_Type;
182 l_orgn_id_tbl wsh_util_core.id_Tab_Type;
183 --l_cont_flag_tbl T_V1 := T_V1();
184
185 l_child_cnt_counter NUMBER;
186 l_cnt_wms_counter NUMBER;
187 l_cnt_inv_counter NUMBER;
188 l_cont_org_id NUMBER;
189 l_cont_line_dir VARCHAR2(10);
190
191 -- LPN CONV. rv
192
193
194 --
195 l_debug_on BOOLEAN;
196 --
197 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'change_ignoreplan_status';
198 --
199 BEGIN
200
201 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
202
203 IF l_debug_on IS NULL THEN
204 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
205 END IF;
206
207 IF l_debug_on THEN
208 WSH_DEBUG_SV.push(l_module_name);
209 END IF;
210
211 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
212
213 --OTM R12, glog proj, use Global Variable
214 l_gc3_is_installed := WSH_UTIL_CORE.G_GC3_IS_INSTALLED;
215
216 -- If null, call the function
217 IF l_gc3_is_installed IS NULL THEN
218 l_gc3_is_installed := WSH_UTIL_CORE.GC3_IS_INSTALLED;
219 END IF;
220 -- Initialize these as they are used in UPDATE statement and in cases where
221 -- these are not actually populated
222 l_new_interface_flag_tab(1) := WSH_NEW_DELIVERIES_PVT.C_TMS_NOT_TO_BE_SENT;
223 l_new_version_number_tab(1) := 1;
224 -- end of OTM R12, glog proj
225
226 IF ((WSH_UTIL_CORE.TP_IS_INSTALLED = 'Y' OR
227 l_gc3_is_installed = 'Y') -- OTM R12, glog proj
228 AND p_entity IN ('DLVY', 'DLVB', 'TRIP')
229 AND p_in_ids is not null AND p_in_ids.COUNT>0) THEN
230
231 IF l_debug_on THEN
232 WSH_DEBUG_SV.log(l_module_name,'p_entity',p_entity);
233 WSH_DEBUG_SV.log(l_module_name,'P_in_IDs.COUNT',p_in_ids.count);
234 WSH_DEBUG_SV.log(l_module_name,'p_action_code',p_action_code);
235 END IF;
236
237 --if lower level entity is passed and higher level entity has different
238 --ignore for planning, error. same applies for TPW/manifesting carrier updates
239
240 IF (p_action_code='IGNORE_PLAN') THEN
241 l_ignoreplan:='Y';
242 ELSE
243 l_ignoreplan:='N';
244 END IF;
245
246 if p_entity='TRIP' then
247
248 -- LPN CONV. rv
249 l_cnt_wms_counter := 1;
250 l_cnt_inv_counter := 1;
251 -- LPN CONV. rv
252 FOR i in 1..p_in_ids.COUNT LOOP
253
254 l_tmp_del_ids.delete;
255 l_tmp_detail_ids.delete;
256
257 FOR l_del_cur IN c_get_deliveries(p_in_ids(i)) LOOP
258
259
260 -- if delivery has other trips, check if those trips also have been selected for
261 -- doing the action. if even one related trip has not been selected, it is a error
262 -- this might also affect trips which are in list of p_in_ids but which
263 -- does not have any other related trips (thru deliveries). This has been done for performance reasons
264
265 l_okay:='F';
266 FOR cur_othertrip IN c_get_del_trips(l_del_cur.delivery_id, p_in_ids(i)) LOOP
267 l_okay:='F';
268 FOR j IN p_in_ids.FIRST..p_in_ids.LAST LOOP
269 IF cur_othertrip.trip_id=p_in_ids(j) THEN
270 l_okay:='T';
271 GOTO next_deltrip;
272 END IF;
273 END LOOP;
274 IF l_okay='F' THEN -- atleast one of the trips the del is assigned to is not in list of ids
275 FND_MESSAGE.SET_NAME('WSH','WSH_TRIP_IGNOREPLAN_ERROR');
276 FND_MESSAGE.SET_TOKEN('TRIP_NAME',wsh_trips_pvt.get_name(p_in_ids(i)));
277 FND_MESSAGE.SET_TOKEN('DEL_NAME',l_del_cur.delivery_name);
278 FND_MESSAGE.SET_TOKEN('REL_TRIP_NAME',cur_othertrip.trip_name);
279 wsh_util_core.add_message(WSH_UTIL_CORE.g_ret_sts_error,l_module_name);
280
281 FND_MESSAGE.SET_NAME('WSH','WSH_ALL_IGNORE_PLAN_ERROR');
282 l_entity := FND_MESSAGE.GET_STRING('WSH','WSH_ENTITY_TRIP');
283 FND_MESSAGE.SET_TOKEN('ENTITY',l_entity);
284 wsh_util_core.add_message(WSH_UTIL_CORE.g_ret_sts_error,l_module_name);
285 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
286 IF l_debug_on THEN
287 WSH_DEBUG_SV.pop(l_module_name);
288 END IF;
289 RETURN;
290 END IF;
291 <<next_deltrip>>
292 null;
293 END LOOP;
294
295 --2. add check to see if del is TPW or CMS if action is 'INCLUDE_PLAN'
296 IF l_ignoreplan='N' THEN
297 FOR cur IN c_get_del_org(l_del_cur.delivery_id) LOOP
298 l_wh_type := WSH_EXTERNAL_INTERFACE_SV.get_warehouse_type
299 (p_organization_id => cur.organization_id,
300 x_return_status => l_return_status,
301 p_delivery_id => l_del_cur.delivery_id,
302 p_msg_display => 'N');
303
304
305 IF (nvl(l_wh_type, FND_API.G_MISS_CHAR) IN ('TPW','CMS')) THEN
306 IF l_debug_on THEN
307 WSH_DEBUG_SV.log(l_module_name,'Get_Warehouse_Type cur.organization_id,l_wh_type,l_return_status',cur.organization_id||l_wh_type||l_return_status);
308 END IF;
309
310 FND_MESSAGE.SET_NAME('WSH','WSH_IGNORE_PLAN_TPW_ERROR');
311 FND_MESSAGE.SET_TOKEN('DEL_NAME',cur.delivery_name);
312 wsh_util_core.add_message(WSH_UTIL_CORE.g_ret_sts_error,l_module_name);
313 l_num_error:=l_num_error+1;
314 GOTO next_trip;
315 END IF;
316 IF cur.delivery_type = 'CONSOLIDATION' THEN
317 FND_MESSAGE.SET_NAME('WSH','WSH_IGNORE_PLAN_MDC_ERROR');
318 FND_MESSAGE.SET_TOKEN('DEL_NAME',cur.delivery_name);
319 wsh_util_core.add_message(WSH_UTIL_CORE.g_ret_sts_error,l_module_name);
320 l_num_error:=l_num_error+1;
321 GOTO next_trip;
322 END IF;
323 END LOOP;
324 END IF;
325
326 --get details
327 FOR l_det_cur IN c_get_lines(l_del_cur.delivery_id) LOOP
328 -- 5746444: disable this check when OTM is installed.
329 IF l_ignoreplan='N' AND l_det_cur.source_code='WSH'
330 and l_det_cur.container_flag='N'
331 AND l_gc3_is_installed = 'N' THEN -- OTM R12
332 --do not allow lines in thrid party instance to be set to include for planning
333 FND_MESSAGE.SET_NAME('WSH','WSH_IGNORE_PLAN_TPW_ERROR');
334 FND_MESSAGE.SET_TOKEN('DEL_NAME',wsh_new_deliveries_pvt.get_name(l_del_cur.delivery_id));
335 wsh_util_core.add_message(WSH_UTIL_CORE.g_ret_sts_error,l_module_name);
336 l_num_error:=l_num_error+1;
337 GOTO next_trip;
338 ELSE
339 l_tmp_detail_ids(l_tmp_detail_ids.COUNT+1):=l_det_cur.delivery_detail_id;
340
341 --
342 --LPN CONV. rv
343 --
344 IF WSH_WMS_LPN_GRP.G_CALLBACK_REQUIRED = 'Y'
345 THEN
346 --{
347
348 l_wms_org := wsh_util_validate.check_wms_org(l_det_cur.organization_id);
349 --
350 IF(l_wms_org = 'Y' AND l_det_cur.container_flag IN ('Y','C')
351 AND l_det_cur.line_direction in ('O', 'IO')) THEN
352 l_sync_tmp_wms_recTbl.delivery_detail_id_tbl(l_cnt_wms_counter) := l_det_cur.delivery_detail_id;
353 l_sync_tmp_wms_recTbl.operation_type_tbl(l_cnt_wms_counter) := 'UPDATE';
354 l_cnt_wms_counter := l_cnt_wms_counter +1;
355 ELSIF (l_wms_org = 'N' AND l_det_cur.container_flag IN ('Y','C')
356 AND l_det_cur.line_direction in ('O', 'IO')) THEN
357 l_sync_tmp_inv_recTbl.delivery_detail_id_tbl(l_cnt_inv_counter) := l_det_cur.delivery_detail_id;
358 l_sync_tmp_inv_recTbl.operation_type_tbl(l_cnt_inv_counter) := 'UPDATE';
359 l_cnt_inv_counter := l_cnt_inv_counter +1;
360 END IF;
361 --}
362 END IF;
363 -- LPN CONV. rv
364 --
365 END IF;
366 END LOOP;
367
368 l_tmp_del_ids(l_tmp_del_ids.COUNT+1):=l_del_cur.delivery_id;
369
370 END LOOP;
371
372 SAVEPOINT before_update;
373
374 -- LPN CONV. rv
375 --
376 IF l_debug_on THEN
377 wsh_debug_sv.LOG(l_module_name, 'Count of l_sync_tmp_wms_recTbl', l_sync_tmp_wms_recTbl.delivery_detail_id_tbl.count);
378 wsh_debug_sv.LOG(l_module_name, 'Count of l_sync_tmp_inv_recTbl', l_sync_tmp_inv_recTbl.delivery_detail_id_tbl.count);
379 END IF;
380 --
381 --
382 IF WSH_WMS_LPN_GRP.G_CALLBACK_REQUIRED = 'Y'
383 THEN
384 --{
385 IF WSH_WMS_LPN_GRP.GK_WMS_UPD_GRP
386 AND l_sync_tmp_wms_recTbl.delivery_detail_id_tbl.count > 0
387 THEN
388 --{
389 --
390 -- Debug Statements
391 --
392 IF l_debug_on THEN
393 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_WMS_SYNC_TMP_PKG.MERGE_BULK',WSH_DEBUG_SV.C_PROC_LEVEL);
394 END IF;
395 --
396 WSH_WMS_SYNC_TMP_PKG.MERGE_BULK
397 (
398 p_sync_tmp_recTbl => l_sync_tmp_wms_recTbl,
399 x_return_status => l_return_status
400 );
401 --
402 IF l_debug_on THEN
403 wsh_debug_sv.log(l_module_name, 'Return status after the call to WSH_WMS_SYNC_TMP_PKG.MERGE_BULK is ', l_return_status);
404 END IF;
405 --
406 WSH_UTIL_CORE.API_POST_CALL
407 (
408 p_return_status => l_return_status,
409 x_num_warnings => l_warn,
410 x_num_errors => l_num_error,
411 p_raise_error_flag => false
412 );
413 -- deleting the tables right here as they are being used in a loop.
414 l_sync_tmp_wms_recTbl.delivery_detail_id_tbl.delete;
415 l_sync_tmp_wms_recTbl.operation_type_tbl.delete;
416 --
417 IF (l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)) THEN
418 rollback to before_update;
419 GOTO next_trip;
420 END IF;
421 --
422 --}
423 ELSIF WSH_WMS_LPN_GRP.GK_INV_UPD_GRP
424 AND l_sync_tmp_inv_recTbl.delivery_detail_id_tbl.count > 0
425 THEN
426 --{
427 --
428 -- Debug Statements
429 --
430 IF l_debug_on THEN
431 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_WMS_SYNC_TMP_PKG.MERGE_BULK',WSH_DEBUG_SV.C_PROC_LEVEL);
432 END IF;
433 --
434 WSH_WMS_SYNC_TMP_PKG.MERGE_BULK
435 (
436 p_sync_tmp_recTbl => l_sync_tmp_inv_recTbl,
437 x_return_status => l_return_status
438 );
439
440 --
441 IF l_debug_on THEN
442 wsh_debug_sv.log(l_module_name, 'Return status after the call to WSH_WMS_SYNC_TMP_PKG.MERGE_BULK is ', l_return_status);
443 END IF;
444 --
445 WSH_UTIL_CORE.API_POST_CALL
446 (
447 p_return_status => l_return_status,
448 x_num_warnings => l_warn,
449 x_num_errors => l_num_error,
450 p_raise_error_flag => false
451 );
452 -- deleting the tables right here as they are being used in a loop.
453 l_sync_tmp_inv_recTbl.delivery_detail_id_tbl.delete;
454 l_sync_tmp_inv_recTbl.operation_type_tbl.delete;
455 --
456 IF (l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)) THEN
457 rollback to before_update;
458 GOTO next_trip;
459 END IF;
460 --
461 --}
462 END IF;
463 --
464 --}
465 END IF;
466 -- LPN CONV. rv
467
468 --update details
469 IF l_tmp_detail_ids is not null and l_tmp_detail_ids.COUNT>0 THEN
470 FORALL i in l_tmp_detail_ids.FIRST..l_tmp_detail_ids.LAST
471 UPDATE wsh_delivery_details
472 SET ignore_for_planning = l_ignoreplan,
473 last_update_date = sysdate,
474 last_updated_by = FND_GLOBAL.USER_ID
475 WHERE delivery_detail_id=l_tmp_detail_ids(i);
476 IF (SQL%NOTFOUND) THEN
477 rollback to before_update;
478 FND_MESSAGE.SET_NAME('WSH','WSH_IGNOREPLAN_UPDATE_ERROR');
479 l_entity := FND_MESSAGE.GET_STRING('WSH','WSH_ENTITY_TRIP');
480 FND_MESSAGE.SET_TOKEN('ENTITY',l_entity);
481 FND_MESSAGE.SET_TOKEN('NAME',wsh_trips_pvt.get_name(p_in_ids(i)));
482 wsh_util_core.add_message(WSH_UTIL_CORE.g_ret_sts_error,l_module_name);
483 l_num_error:=l_num_error+1;
484 GOTO next_trip;
485 END IF;
486 Check_Shipset_Ignoreflag( p_delivery_detail_ids=>l_tmp_detail_ids,
487 p_ignore_for_planning=>l_ignoreplan,
488 p_logexcep=>false,
489 x_return_status=>l_return_status );
490 IF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
491 l_warn:=l_warn+1;
492 END IF;
493 IF l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR THEN
494 raise OTHERS;
495 END IF;
496 END IF;
497
498 --update dels
499 IF l_tmp_del_ids is not null and l_tmp_del_ids.COUNT>0 THEN
500 FORALL i in l_tmp_del_ids.FIRST..l_tmp_del_ids.LAST
501 UPDATE wsh_new_deliveries
502 SET ignore_for_planning = l_ignoreplan,
503 last_update_date = sysdate,
504 last_updated_by = FND_GLOBAL.USER_ID
505 WHERE delivery_id=l_tmp_del_ids(i);
506
507 IF (SQL%NOTFOUND) THEN
508 rollback to before_update;
509 FND_MESSAGE.SET_NAME('WSH','WSH_IGNOREPLAN_UPDATE_ERROR');
510 l_entity := FND_MESSAGE.GET_STRING('WSH','WSH_ENTITY_TRIP');
511 FND_MESSAGE.SET_TOKEN('ENTITY',l_entity);
512 FND_MESSAGE.SET_TOKEN('NAME',wsh_trips_pvt.get_name(p_in_ids(i)));
513 wsh_util_core.add_message(WSH_UTIL_CORE.g_ret_sts_error,l_module_name);
514 l_num_error:=l_num_error+1;
515 GOTO next_trip;
516 ELSE --Added for bug 7611042 .More than zero rows updated. So calling the API to update the hash string
517 FOR i in l_tmp_del_ids.FIRST..l_tmp_del_ids.LAST LOOP
518 --{
519 --
520 IF l_debug_on THEN
521 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_NEW_DELIVERIES_PVT.TABLE_TO_RECORD',WSH_DEBUG_SV.C_PROC_LEVEL);
522 END IF;
523 --
524 WSH_NEW_DELIVERIES_PVT.TABLE_TO_RECORD(p_delivery_id => l_tmp_del_ids(i),
525 x_delivery_rec => l_delivery_info,
526 x_return_status => l_return_status);
527 --
528 IF l_debug_on THEN
529 wsh_debug_sv.log(l_module_name,'Return Status After Calling table_to_record ',l_return_status);
530 END IF;
531 --
532 IF l_return_status in (WSH_UTIL_CORE.G_RET_STS_ERROR,WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
533 rollback to before_update;
534 FND_MESSAGE.SET_NAME('WSH','WSH_IGNOREPLAN_UPDATE_ERROR');
535 l_entity := FND_MESSAGE.GET_STRING('WSH','WSH_ENTITY_TRIP');
536 FND_MESSAGE.SET_TOKEN('ENTITY',l_entity);
537 FND_MESSAGE.SET_TOKEN('NAME',wsh_trips_pvt.get_name(p_in_ids(i)));
538 wsh_util_core.add_message(WSH_UTIL_CORE.g_ret_sts_error,l_module_name);
539 l_num_error:=l_num_error+1;
540 goto next_trip;
541 END IF;
542 --
543 IF l_debug_on THEN
544 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_AUTOCREATE.CREATE_UPDATE_HASH',WSH_DEBUG_SV.C_PROC_LEVEL);
545 END IF;
546 --
547 WSH_DELIVERY_AUTOCREATE.CREATE_UPDATE_HASH(p_delivery_rec => l_delivery_info,
548 x_return_status => l_return_status);
549 --
550 IF l_debug_on THEN
551 wsh_debug_sv.log(l_module_name,'Return Status After Calling create_update_hash ',l_return_status);
552 END IF;
553 --
554 IF l_return_status in (WSH_UTIL_CORE.G_RET_STS_ERROR,WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
555 rollback to before_update;
556 FND_MESSAGE.SET_NAME('WSH','WSH_IGNOREPLAN_UPDATE_ERROR');
557 l_entity := FND_MESSAGE.GET_STRING('WSH','WSH_ENTITY_TRIP');
558 FND_MESSAGE.SET_TOKEN('ENTITY',l_entity);
559 FND_MESSAGE.SET_TOKEN('NAME',wsh_trips_pvt.get_name(p_in_ids(i)));
560 wsh_util_core.add_message(WSH_UTIL_CORE.g_ret_sts_error,l_module_name);
561 l_num_error:=l_num_error+1;
562 goto next_trip;
563 END IF;
564 --
565 IF l_debug_on THEN
566 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_NEW_DELIVERIES_PVT.UPDATE_DELIVERY',WSH_DEBUG_SV.C_PROC_LEVEL);
567 END IF;
568 --
569 WSH_NEW_DELIVERIES_PVT.UPDATE_DELIVERY( p_rowid => NULL,
570 p_delivery_info => l_delivery_info,
571 x_return_status => l_return_status);
572 --
573 IF l_debug_on THEN
574 wsh_debug_sv.log(l_module_name,'Return Status After Calling update_delivery ',l_return_status);
575 END IF;
576 --
577 IF l_return_status in (WSH_UTIL_CORE.G_RET_STS_ERROR,WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
578 rollback to before_update;
579 FND_MESSAGE.SET_NAME('WSH','WSH_IGNOREPLAN_UPDATE_ERROR');
580 l_entity := FND_MESSAGE.GET_STRING('WSH','WSH_ENTITY_TRIP');
581 FND_MESSAGE.SET_TOKEN('ENTITY',l_entity);
582 FND_MESSAGE.SET_TOKEN('NAME',wsh_trips_pvt.get_name(p_in_ids(i)));
583 wsh_util_core.add_message(WSH_UTIL_CORE.g_ret_sts_error,l_module_name);
584 l_num_error:=l_num_error+1;
585 goto next_trip;
586 END IF;
587 --}
588 END LOOP;
589 END IF;
590
591 END IF;
592
593 -- OTM R12, glog proj
594 IF l_debug_on THEN
595 WSH_DEBUG_SV.log(l_module_name,'Gc3 Installed:',l_gc3_is_installed);
596 WSH_DEBUG_SV.log(l_module_name,'Ignore :',l_ignoreplan);
597 END IF;
598
599 -- MDC Changes, Updating Trip tp_plan_name when it is set to
600 -- Ignore for Planning
601 IF (l_gc3_is_installed = 'Y' AND l_ignoreplan = 'Y') THEN
602 l_tp_plan_name_update := 'Y';
603 ELSE
604 l_tp_plan_name_update := 'N';
605 END IF;
606
607 IF l_debug_on THEN
608 WSH_DEBUG_SV.log(l_module_name,'Tp Plan Name Update',l_tp_plan_name_update);
609 END IF;
610
611 --update trips
612 -- OTM R12, glog proj, check the condition first and then update
613 IF l_tp_plan_name_update = 'Y' THEN
614 UPDATE wsh_trips
615 SET ignore_for_planning = l_ignoreplan,
616 tp_plan_name = NULL, -- OTM R12, glog proj
617 last_update_date = sysdate,
618 last_updated_by = FND_GLOBAL.USER_ID,
619 last_update_login = FND_GLOBAL.LOGIN_ID -- OTM R12, glog proj
620 WHERE trip_id=p_in_ids(i);
621
622 ELSE -- l_tp_plan_name_update is null or N, do not update tp_plan_name
623 UPDATE wsh_trips
624 SET ignore_for_planning = l_ignoreplan,
625 last_update_date = sysdate,
626 last_updated_by = FND_GLOBAL.USER_ID,
627 last_update_login = FND_GLOBAL.LOGIN_ID -- OTM R12, glog proj
628 WHERE trip_id=p_in_ids(i);
629 END IF;
630 -- end of OTM R12, glog proj
631 --
632
633 IF (SQL%NOTFOUND) THEN
634 rollback to before_update;
635 FND_MESSAGE.SET_NAME('WSH','WSH_IGNOREPLAN_UPDATE_ERROR');
636 l_entity := FND_MESSAGE.GET_STRING('WSH','WSH_ENTITY_TRIP');
637 FND_MESSAGE.SET_TOKEN('ENTITY',l_entity);
638 FND_MESSAGE.SET_TOKEN('NAME',wsh_trips_pvt.get_name(p_in_ids(i)));
639 wsh_util_core.add_message(WSH_UTIL_CORE.g_ret_sts_error,l_module_name);
640 l_num_error:=l_num_error+1;
641 END IF;
642
643 <<next_trip>>
644 null;
645 END LOOP;
646
647 --if all trips have problem show as error
648 IF l_num_error>0 AND l_num_error=p_in_ids.COUNT THEN
649 FND_MESSAGE.SET_NAME('WSH','WSH_ALL_IGNORE_PLAN_ERROR');
650 l_entity := FND_MESSAGE.GET_STRING('WSH','WSH_ENTITY_TRIP');
651 FND_MESSAGE.SET_TOKEN('ENTITY',l_entity);
652 wsh_util_core.add_message(WSH_UTIL_CORE.g_ret_sts_error,l_module_name);
653 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
654 IF l_debug_on THEN
655 WSH_DEBUG_SV.pop(l_module_name);
656 END IF;
657 RETURN;
658 ELSIF l_num_error>0 THEN
659 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
660 END IF;
661
662 elsif p_entity='DLVY' then
663
664 -- LPN CONV. rv
665 l_cnt_wms_counter := 1;
666 l_cnt_inv_counter := 1;
667 -- LPN CONV. rv
668 FOR i in 1..p_in_ids.COUNT LOOP
669 l_tmp_detail_ids.delete;
670
671 --1. check if trip the del is assigned to has diff ignore_plan value or is firm
672 FOR cur IN c_get_del_ignoreflag_difftrip(p_in_ids(i), l_ignoreplan) LOOP
673 FND_MESSAGE.SET_NAME('WSH','WSH_IGNORE_PLAN_ERROR');
674 FND_MESSAGE.SET_TOKEN('DEL_NAME',cur.delivery_name);
675 FND_MESSAGE.SET_TOKEN('TRIP_NAME',cur.trip_name);
676 wsh_util_core.add_message(WSH_UTIL_CORE.g_ret_sts_error,l_module_name);
677 l_num_error:=l_num_error+1;
678 GOTO next_del;
679 END LOOP;
680
681 --2. add check to see if del is TPW or CMS if action is 'INCLUDE_PLAN'
682 -- LPN CONV. rv
683 -- moved the if condition inside the loop
684 FOR cur IN c_get_del_org(p_in_ids(i)) LOOP
685 --
686 IF l_ignoreplan='N' THEN
687 l_wh_type := WSH_EXTERNAL_INTERFACE_SV.Get_Warehouse_Type
688 (p_organization_id => cur.organization_id,
689 x_return_status => l_return_status,
690 p_delivery_id => p_in_ids(i),
691 p_msg_display => 'N');
692
693
694 IF (nvl(l_wh_type, FND_API.G_MISS_CHAR) IN ('TPW','CMS')) THEN
695 IF l_debug_on THEN
696 WSH_DEBUG_SV.log(l_module_name,'Get_Warehouse_Type cur.organization_id,l_wh_type,l_return_status',cur.organization_id||l_wh_type||l_return_status);
697 END IF;
698
699 FND_MESSAGE.SET_NAME('WSH','WSH_IGNORE_PLAN_TPW_ERROR');
700 FND_MESSAGE.SET_TOKEN('DEL_NAME',cur.delivery_name);
701 wsh_util_core.add_message(WSH_UTIL_CORE.g_ret_sts_error,l_module_name);
702 l_num_error:=l_num_error+1;
703 GOTO next_del;
704 END IF;
705 IF cur.delivery_type = 'CONSOLIDATION' THEN
706 FND_MESSAGE.SET_NAME('WSH','WSH_IGNORE_PLAN_MDC_ERROR');
707 FND_MESSAGE.SET_TOKEN('DEL_NAME',cur.delivery_name);
708 wsh_util_core.add_message(WSH_UTIL_CORE.g_ret_sts_error,l_module_name);
709 l_num_error:=l_num_error+1;
710 GOTO next_del;
711 END IF;
712 END IF;
713 END LOOP;
714
715 FOR l_det_cur IN c_get_lines(p_in_ids(i)) LOOP
716 --
717 -- 5746444: disable this check when OTM is not installed
718 --
719 IF l_ignoreplan='N' AND l_det_cur.source_code='WSH'
720 and l_det_cur.container_flag='N'
721 and l_gc3_is_installed = 'N' THEN -- OTM R12
722 --do not allow lines in thrid party instance to be set to include for planning
723 FND_MESSAGE.SET_NAME('WSH','WSH_IGNORE_PLAN_TPW_ERROR');
724 FND_MESSAGE.SET_TOKEN('DEL_NAME',wsh_new_deliveries_pvt.get_name(l_del_cur.delivery_id));
725 wsh_util_core.add_message(WSH_UTIL_CORE.g_ret_sts_error,l_module_name);
726 l_num_error:=l_num_error+1;
727 GOTO next_del;
728 ELSE
729 l_tmp_detail_ids(l_tmp_detail_ids.COUNT+1):=l_det_cur.delivery_detail_id;
730 --
731 -- LPN CONV. rv
732 --
733 IF WSH_WMS_LPN_GRP.G_CALLBACK_REQUIRED = 'Y'
734 THEN
735 --{
736
737 l_wms_org := wsh_util_validate.check_wms_org(l_det_cur.organization_id);
738 --
739 IF(l_wms_org = 'Y' AND l_det_cur.container_flag IN ('Y','C')
740 AND l_det_cur.line_direction in ('O', 'IO')) THEN
741 l_sync_tmp_wms_recTbl.delivery_detail_id_tbl(l_cnt_wms_counter) := l_det_cur.delivery_detail_id;
742 l_sync_tmp_wms_recTbl.operation_type_tbl(l_cnt_wms_counter) := 'UPDATE';
743 l_cnt_wms_counter := l_cnt_wms_counter +1;
744 ELSIF (l_wms_org = 'N' AND l_det_cur.container_flag IN ('Y','C')
745 AND l_det_cur.line_direction in ('O', 'IO')) THEN
746 l_sync_tmp_inv_recTbl.delivery_detail_id_tbl(l_cnt_inv_counter) := l_det_cur.delivery_detail_id;
747 l_sync_tmp_inv_recTbl.operation_type_tbl(l_cnt_inv_counter) := 'UPDATE';
748 l_cnt_inv_counter := l_cnt_inv_counter +1;
749 END IF;
750 --
751 --}
752 END IF;
753 -- LPN CONV. rv
754 --
755 END IF;
756 END LOOP;
757
758 SAVEPOINT before_update;
759 --
760 -- LPN CONV. rv
761 --
762 IF l_debug_on THEN
763 wsh_debug_sv.LOG(l_module_name, 'Count of l_sync_tmp_wms_recTbl', l_sync_tmp_wms_recTbl.delivery_detail_id_tbl.count);
764 wsh_debug_sv.LOG(l_module_name, 'Count of l_sync_tmp_inv_recTbl', l_sync_tmp_inv_recTbl.delivery_detail_id_tbl.count);
765 END IF;
766 --
767 --
768 IF WSH_WMS_LPN_GRP.G_CALLBACK_REQUIRED = 'Y'
769 THEN
770 --{
771 IF WSH_WMS_LPN_GRP.GK_WMS_UPD_GRP
772 AND l_sync_tmp_wms_recTbl.delivery_detail_id_tbl.count > 0
773 THEN
774 --{
775 --
776 -- Debug Statements
777 --
778 IF l_debug_on THEN
779 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_WMS_SYNC_TMP_PKG.MERGE_BULK',WSH_DEBUG_SV.C_PROC_LEVEL);
780 END IF;
781 --
782 WSH_WMS_SYNC_TMP_PKG.MERGE_BULK
783 (
784 p_sync_tmp_recTbl => l_sync_tmp_wms_recTbl,
785 x_return_status => l_return_status
786 );
787 --
788 IF l_debug_on THEN
789 wsh_debug_sv.log(l_module_name, 'Return status after the call to WSH_WMS_SYNC_TMP_PKG.MERGE_BULK is ', l_return_status);
790 END IF;
791 --
792 WSH_UTIL_CORE.API_POST_CALL
793 (
794 p_return_status => l_return_status,
795 x_num_warnings => l_warn,
796 x_num_errors => l_num_error,
797 p_raise_error_flag => false
798 );
799 -- deleting the tables right here as they are being used in a loop.
800 l_sync_tmp_wms_recTbl.delivery_detail_id_tbl.delete;
801 l_sync_tmp_wms_recTbl.operation_type_tbl.delete;
802 --
803 IF (l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)) THEN
804 ROLLBACK to before_update;
805 GOTO next_del;
806 END IF;
807 --
808 --}
809 ELSIF WSH_WMS_LPN_GRP.GK_INV_UPD_GRP
810 AND l_sync_tmp_inv_recTbl.delivery_detail_id_tbl.count > 0
811 THEN
812 --{
813 --
814 -- Debug Statements
815 --
816 IF l_debug_on THEN
817 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_WMS_SYNC_TMP_PKG.MERGE_BULK',WSH_DEBUG_SV.C_PROC_LEVEL);
818 END IF;
819 --
820 WSH_WMS_SYNC_TMP_PKG.MERGE_BULK
821 (
822 p_sync_tmp_recTbl => l_sync_tmp_inv_recTbl,
823 x_return_status => l_return_status
824 );
825
826 --
827 IF l_debug_on THEN
828 wsh_debug_sv.log(l_module_name, 'Return status after the call to WSH_WMS_SYNC_TMP_PKG.MERGE_BULK is ', l_return_status);
829 END IF;
830 --
831 WSH_UTIL_CORE.API_POST_CALL
832 (
833 p_return_status => l_return_status,
834 x_num_warnings => l_warn,
835 x_num_errors => l_num_error,
836 p_raise_error_flag => false
837 );
838 -- deleting the tables right here as they are being used in a loop.
839 l_sync_tmp_inv_recTbl.delivery_detail_id_tbl.delete;
840 l_sync_tmp_inv_recTbl.operation_type_tbl.delete;
841 --
842 IF (l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)) THEN
843 ROLLBACK to before_update;
844 GOTO next_del;
845 END IF;
846 --
847 --}
848 END IF;
849 --}
850 END IF;
851 -- LPN CONV. rv
852
853 --update details
854 -- bug 6369687: While Ship Confirming with 'Backorder All', ignore_for_planning for Delivery Detail should not be set to 'Y'.
855 IF l_debug_on THEN
856 WSH_DEBUG_SV.log(l_module_name,'WSH_DELIVERIES_GRP.G_ACTION',WSH_DELIVERIES_GRP.G_ACTION);
857 END IF;
858 IF NVL(WSH_DELIVERIES_GRP.G_ACTION,'UPDATE') <> 'CONFIRM' THEN
859 IF l_tmp_detail_ids is not null and l_tmp_detail_ids.COUNT>0 THEN
860 FORALL i in l_tmp_detail_ids.FIRST..l_tmp_detail_ids.LAST
861 UPDATE wsh_delivery_details
862 SET ignore_for_planning = l_ignoreplan,
863 last_update_date = sysdate,
864 last_updated_by = FND_GLOBAL.USER_ID
865 WHERE delivery_detail_id=l_tmp_detail_ids(i);
866 IF (SQL%NOTFOUND) THEN
867 ROLLBACK to before_update;
868 FND_MESSAGE.SET_NAME('WSH','WSH_IGNOREPLAN_UPDATE_ERROR');
869 l_entity := FND_MESSAGE.GET_STRING('WSH','WSH_DELIVERY');
870 FND_MESSAGE.SET_TOKEN('ENTITY',l_entity);
871 FND_MESSAGE.SET_TOKEN('NAME',wsh_new_deliveries_pvt.get_name(p_in_ids(i)));
872 wsh_util_core.add_message(WSH_UTIL_CORE.g_ret_sts_error,l_module_name);
873 l_num_error:=l_num_error+1;
874 GOTO next_del;
875 END IF;
876 Check_Shipset_Ignoreflag( p_delivery_detail_ids=>l_tmp_detail_ids,
877 p_ignore_for_planning=>l_ignoreplan,
878 p_logexcep=>false,
879 x_return_status=>l_return_status );
880 IF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
881 l_warn:=l_warn+1;
882 END IF;
883 IF l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR THEN
884 raise OTHERS;
885 END IF;
886 END IF;
887 END IF;
888 WSH_DELIVERIES_GRP.G_ACTION := NULL;
889
890 -- OTM R12, glog proj
891 IF l_gc3_is_installed = 'Y' THEN--{
892 -- initialize the variables, tables
893 l_delivery_info_tab.DELETE;
894 --l_new_interface_flag_tab.DELETE;
895 --l_new_version_number_tab.delete;
896 l_tms_update := 'Y';
897 l_is_delivery_empty := NULL;
898
899 WSH_DELIVERY_VALIDATIONS.get_delivery_information
900 (p_delivery_id => p_in_ids(i),
901 x_delivery_rec => l_delivery_info,
902 x_return_status => l_return_status);
903
904 IF l_debug_on THEN
905 WSH_DEBUG_SV.log(l_module_name,'Return Status',l_return_status);
906 END IF;
907
908 IF (l_return_status IN (WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR,
909 WSH_UTIL_CORE.G_RET_STS_ERROR)) THEN
910 ROLLBACK to before_update;
911 l_num_error := l_num_error + 1;
912 GOTO next_del;
913 ELSIF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
914 l_warn:=l_warn+1;
915 END IF;
916
917 l_delivery_info_tab(1) := l_delivery_info;
918
919 IF (l_ignoreplan = 'N') THEN--{
920 l_is_delivery_empty := WSH_NEW_DELIVERY_ACTIONS.IS_DELIVERY_EMPTY(p_in_ids(i));
921
922 IF l_debug_on THEN
923 WSH_DEBUG_SV.log(l_module_name,'Is Delivery Empty',l_is_delivery_empty);
924 END IF;
925 IF (l_is_delivery_empty = 'E') THEN
926 ROLLBACK to before_update;
927 l_num_error := l_num_error + 1;
928 GOTO next_del;
929 END IF;
930
931 IF l_debug_on THEN
932 WSH_DEBUG_SV.log(l_module_name,'Delivery Tms_interface_flag',l_delivery_info.tms_interface_flag);
933 END IF;
934
935 IF (nvl(l_delivery_info.tms_interface_flag,
936 WSH_NEW_DELIVERIES_PVT.C_TMS_NOT_TO_BE_SENT) =
937 WSH_NEW_DELIVERIES_PVT.C_TMS_NOT_TO_BE_SENT
938 AND l_is_delivery_empty = 'N') THEN--{
939 l_new_interface_flag_tab(1) := WSH_NEW_DELIVERIES_PVT.C_TMS_CREATE_REQUIRED;
940 l_new_version_number_tab(1) :=
941 nvl(l_delivery_info.tms_version_number, 1) + 1;
942 ELSIF
943 ((nvl(l_delivery_info.tms_interface_flag,
944 WSH_NEW_DELIVERIES_PVT.C_TMS_NOT_TO_BE_SENT)
945 IN (WSH_NEW_DELIVERIES_PVT.C_TMS_DELETE_REQUIRED,
946 WSH_NEW_DELIVERIES_PVT.C_TMS_DELETE_IN_PROCESS))
947 AND l_is_delivery_empty = 'N') THEN
948 l_new_interface_flag_tab(1) := WSH_NEW_DELIVERIES_PVT.C_TMS_UPDATE_REQUIRED;
949 l_new_version_number_tab(1) :=
950 nvl(l_delivery_info.tms_version_number, 1) + 1;
951 ELSE
952 l_tms_update := 'N';
953 END IF;--}
954 ELSIF (l_ignoreplan = 'Y') THEN--} {
955
956 IF (nvl(l_delivery_info.tms_interface_flag,
957 WSH_NEW_DELIVERIES_PVT.C_TMS_NOT_TO_BE_SENT) IN
958 (WSH_NEW_DELIVERIES_PVT.C_TMS_CREATE_IN_PROCESS,
959 WSH_NEW_DELIVERIES_PVT.C_TMS_UPDATE_REQUIRED,
960 WSH_NEW_DELIVERIES_PVT.C_TMS_UPDATE_IN_PROCESS,
961 WSH_NEW_DELIVERIES_PVT.C_TMS_AWAITING_ANSWER,
962 WSH_NEW_DELIVERIES_PVT.C_TMS_ANSWER_RECEIVED)) THEN
963 l_new_interface_flag_tab(1) := WSH_NEW_DELIVERIES_PVT.C_TMS_DELETE_REQUIRED;
964 l_new_version_number_tab(1) :=
965 nvl(l_delivery_info.tms_version_number, 1) + 1;
966 ELSIF (nvl(l_delivery_info.tms_interface_flag,
967 WSH_NEW_DELIVERIES_PVT.C_TMS_NOT_TO_BE_SENT) =
968 (WSH_NEW_DELIVERIES_PVT.C_TMS_CREATE_REQUIRED)) THEN
969 l_new_interface_flag_tab(1) := WSH_NEW_DELIVERIES_PVT.C_TMS_NOT_TO_BE_SENT;
970 l_new_version_number_tab(1) :=
971 nvl(l_delivery_info.tms_version_number, 1);
972 ELSE
973 l_tms_update := 'N';
974 END IF;
975 END IF;--}
976
977 IF l_debug_on THEN
978 WSH_DEBUG_SV.log(l_module_name,'l_tms_update',l_tms_update);
979 END IF;
980
981 -- have to call wms to check if deliveries being updated
982 -- are assigned to a trip being loaded
983 IF (wsh_util_validate.Check_Wms_Org(l_delivery_info.organization_id)='Y'
984 AND l_ignoreplan = 'N') THEN--{
985 l_del_trip_tab.delete;
986 l_temp := NULL;
987 l_del_trip_tab(1).delivery_id := p_in_ids(i);
988
989 --check delivery's current ignore for planning flag
990 IF (l_delivery_info.ignore_for_planning = 'Y') THEN--{
991 IF l_debug_on THEN
992 WSH_DEBUG_SV.logmsg(l_module_name,
993 'Calling program unit WMS_SHIPPING_INTERFACE_GRP.PROCESS_DELIVERIES',
994 WSH_DEBUG_SV.C_PROC_LEVEL);
995 END IF;
996
997 WMS_SHIPPING_INTERFACE_GRP.process_deliveries(
998 p_api_version => 1.0,
999 p_init_msg_list => wms_shipping_interface_grp.g_false,
1000 p_commit => wms_shipping_interface_grp.g_false,
1001 p_validation_level=> wms_shipping_interface_grp.g_full_validation,
1002 p_action => wms_shipping_interface_grp.g_action_plan_delivery,
1003 x_dlvy_trip_tbl => l_del_trip_tab,
1004 x_return_status => l_return_status,
1005 x_msg_count => l_msg_count,
1006 x_msg_data => l_msg_data);
1007
1008 IF l_debug_on THEN
1009 WSH_DEBUG_SV.log(l_module_name,'Return Status after WMS API Call',l_return_status);
1010 WSH_DEBUG_SV.log(l_module_name,'Message Code',l_del_trip_tab(l_del_trip_tab.LAST).r_message_code);
1011 END IF;
1012
1013 IF (l_return_status IN (WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR,
1014 WSH_UTIL_CORE.G_RET_STS_ERROR)) THEN
1015 ROLLBACK to before_update;
1016 l_num_error := l_num_error + 1;
1017 GOTO next_del;
1018 ELSIF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
1019 l_warn:=l_warn+1;
1020 END IF;
1021
1022 l_temp := l_del_trip_tab.LAST;
1023 IF (l_del_trip_tab(l_temp).r_message_code = 'WMS_DELIVERY_LOADED_TO_DOCK')
1024 THEN--{
1025 ROLLBACK to before_update;
1026 FND_MESSAGE.SET_NAME(l_del_trip_tab(l_temp).r_message_appl,
1027 l_del_trip_tab(l_temp).r_message_code);
1028 FND_MESSAGE.SET_TOKEN(l_del_trip_tab(l_temp).r_message_token_name,
1029 l_del_trip_tab(l_temp).r_message_token);
1030 wsh_util_core.add_message(l_del_trip_tab(l_temp).r_message_type, l_module_name);
1031 l_num_error:=l_num_error+1;
1032 GOTO next_del;
1033 END IF; --}
1034 END IF;--}
1035 END IF;--}
1036 END IF;--}
1037 -- end of OTM R12, glog proj
1038
1039 --update dels
1040 UPDATE wsh_new_deliveries
1041 SET ignore_for_planning = l_ignoreplan,
1042 -- OTM R12, glog proj, based on the l_tms_update flag set above
1043 tms_interface_flag = DECODE(l_tms_update,
1044 'Y', l_new_interface_flag_tab(1),
1045 tms_interface_flag),
1046 tms_version_number = DECODE(l_tms_update,
1047 'Y', l_new_version_number_tab(1),
1048 tms_version_number),
1049 last_update_date = sysdate,
1050 last_updated_by = FND_GLOBAL.USER_ID,
1051 last_update_login = FND_GLOBAL.LOGIN_ID -- OTM R12, glog proj
1052 WHERE delivery_id=p_in_ids(i);
1053
1054 IF (SQL%NOTFOUND) THEN
1055 ROLLBACK to before_update;
1056 FND_MESSAGE.SET_NAME('WSH','WSH_IGNOREPLAN_UPDATE_ERROR');
1057 l_entity := FND_MESSAGE.GET_STRING('WSH','WSH_DELIVERY');
1058 FND_MESSAGE.SET_TOKEN('ENTITY',l_entity);
1059 FND_MESSAGE.SET_TOKEN('NAME',wsh_new_deliveries_pvt.get_name(p_in_ids(i)));
1060 wsh_util_core.add_message(WSH_UTIL_CORE.g_ret_sts_error,l_module_name);
1061 l_num_error:=l_num_error+1;
1062 goto next_del;
1063 ELSE --Added for bug 7611042 .More than zero rows updated. So calling the API to update the hash string
1064 --{
1065 --
1066 IF l_debug_on THEN
1067 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_NEW_DELIVERIES_PVT.TABLE_TO_RECORD',WSH_DEBUG_SV.C_PROC_LEVEL);
1068 END IF;
1069 --
1070 WSH_NEW_DELIVERIES_PVT.TABLE_TO_RECORD(p_delivery_id => p_in_ids(i),
1071 x_delivery_rec => l_delivery_info,
1072 x_return_status => l_return_status);
1073 --
1074 IF l_debug_on THEN
1075 wsh_debug_sv.log(l_module_name,'Return Status After Calling table_to_record ',l_return_status);
1076 END IF;
1077 --
1078 IF l_return_status in (WSH_UTIL_CORE.G_RET_STS_ERROR,WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
1079 rollback to before_update;
1080 FND_MESSAGE.SET_NAME('WSH','WSH_IGNOREPLAN_UPDATE_ERROR');
1081 l_entity := FND_MESSAGE.GET_STRING('WSH','WSH_DELIVERY');
1082 FND_MESSAGE.SET_TOKEN('ENTITY',l_entity);
1083 FND_MESSAGE.SET_TOKEN('NAME',wsh_new_deliveries_pvt.get_name(p_in_ids(i)));
1084 wsh_util_core.add_message(WSH_UTIL_CORE.g_ret_sts_error,l_module_name);
1085 l_num_error:=l_num_error+1;
1086 goto next_del;
1087 END IF;
1088 --
1089 IF l_debug_on THEN
1090 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_AUTOCREATE.CREATE_UPDATE_HASH',WSH_DEBUG_SV.C_PROC_LEVEL);
1091 END IF;
1092 --
1093 WSH_DELIVERY_AUTOCREATE.CREATE_UPDATE_HASH(p_delivery_rec => l_delivery_info,
1094 x_return_status => l_return_status);
1095 --
1096 IF l_debug_on THEN
1097 wsh_debug_sv.log(l_module_name,'Return Status After Calling create_update_hash ',l_return_status);
1098 END IF;
1099 --
1100 IF l_return_status in (WSH_UTIL_CORE.G_RET_STS_ERROR,WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
1101 rollback to before_update;
1102 FND_MESSAGE.SET_NAME('WSH','WSH_IGNOREPLAN_UPDATE_ERROR');
1103 l_entity := FND_MESSAGE.GET_STRING('WSH','WSH_DELIVERY');
1104 FND_MESSAGE.SET_TOKEN('ENTITY',l_entity);
1105 FND_MESSAGE.SET_TOKEN('NAME',wsh_new_deliveries_pvt.get_name(p_in_ids(i)));
1106 wsh_util_core.add_message(WSH_UTIL_CORE.g_ret_sts_error,l_module_name);
1107 l_num_error:=l_num_error+1;
1108 goto next_del;
1109 END IF;
1110 --
1111 IF l_debug_on THEN
1112 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_NEW_DELIVERIES_PVT.UPDATE_DELIVERY',WSH_DEBUG_SV.C_PROC_LEVEL);
1113 END IF;
1114 --
1115 WSH_NEW_DELIVERIES_PVT.UPDATE_DELIVERY( p_rowid => NULL,
1116 p_delivery_info => l_delivery_info,
1117 x_return_status => l_return_status);
1118 --
1119 IF l_debug_on THEN
1120 wsh_debug_sv.log(l_module_name,'Return Status After Calling update_delivery ',l_return_status);
1121 END IF;
1122 --
1123 IF l_return_status in (WSH_UTIL_CORE.G_RET_STS_ERROR,WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
1124 rollback to before_update;
1125 FND_MESSAGE.SET_NAME('WSH','WSH_IGNOREPLAN_UPDATE_ERROR');
1126 l_entity := FND_MESSAGE.GET_STRING('WSH','WSH_DELIVERY');
1127 FND_MESSAGE.SET_TOKEN('ENTITY',l_entity);
1128 FND_MESSAGE.SET_TOKEN('NAME',wsh_new_deliveries_pvt.get_name(p_in_ids(i)));
1129 wsh_util_core.add_message(WSH_UTIL_CORE.g_ret_sts_error,l_module_name);
1130 l_num_error:=l_num_error+1;
1131 goto next_del;
1132 END IF;
1133 --}
1134 END IF;
1135
1136 -- OTM R12, glog proj
1137 IF (l_gc3_is_installed = 'Y'
1138 AND l_new_interface_flag_tab.COUNT > 0) THEN--{
1139
1140 IF l_debug_on THEN
1141 WSH_DEBUG_SV.logmsg(l_module_name,
1142 'Calling program unit WSH_XC_UTIL.LOG_OTM_EXCEPTION',
1143 WSH_DEBUG_SV.C_PROC_LEVEL);
1144 END IF;
1145
1146 WSH_XC_UTIL.log_otm_exception(
1147 p_delivery_info_tab => l_delivery_info_tab,
1148 p_new_interface_flag_tab => l_new_interface_flag_tab,
1149 x_return_status => l_return_status);
1150
1151 IF l_debug_on THEN
1152 WSH_DEBUG_SV.log(l_module_name,'Return Status after log_otm_exception',l_return_status);
1153 END IF;
1154
1155 IF (l_return_status IN (WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR,
1156 WSH_UTIL_CORE.G_RET_STS_ERROR)) THEN
1157 ROLLBACK to before_update;
1158 l_num_error := l_num_error + 1;
1159 GOTO next_del;
1160 ELSIF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
1161 l_warn := l_warn + 1;
1162 END IF;
1163
1164 END IF;--}
1165 -- end of OTM R12, glog proj
1166
1167 <<next_del>>
1168 null;
1169 END LOOP;
1170
1171 --if all dels have problem show as error
1172 IF l_num_error>0 AND l_num_error=p_in_ids.COUNT THEN
1173 FND_MESSAGE.SET_NAME('WSH','WSH_ALL_IGNORE_PLAN_ERROR');
1174 l_entity := FND_MESSAGE.GET_STRING('WSH','WSH_DELIVERY');
1175 FND_MESSAGE.SET_TOKEN('ENTITY',l_entity);
1176 wsh_util_core.add_message(WSH_UTIL_CORE.g_ret_sts_error,l_module_name);
1177 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1178 IF l_debug_on THEN
1179 WSH_DEBUG_SV.pop(l_module_name);
1180 END IF;
1181 RETURN;
1182 ELSIF l_num_error>0 THEN
1183 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
1184 END IF;
1185
1186
1187 else
1188 --p_entity='DLVB' (only 3 values for p_entity)
1189
1190 FOR i in 1..p_in_ids.COUNT LOOP
1191 --1. check if del the detail is assigned to has diff ignore_plan value or the del is planned/firm
1192 FOR cur IN c_get_det_ignoreflag_diff_del(p_in_ids(i), l_ignoreplan) LOOP
1193 FND_MESSAGE.SET_NAME('WSH','WSH_IGNORE_PLAN_ERROR_DETAIL');
1194 FND_MESSAGE.SET_TOKEN('DET_ID',p_in_ids(i));
1195 FND_MESSAGE.SET_TOKEN('DEL_NAME',cur.delivery_name);
1196 wsh_util_core.add_message(WSH_UTIL_CORE.g_ret_sts_error,l_module_name);
1197 l_num_error:=l_num_error+1;
1198 GOTO next_det;
1199 END LOOP;
1200
1201 --2. add check to see if detail is TPW or CMS if action is 'INCLUDE_PLAN'
1202 IF l_ignoreplan='N' THEN
1203 FOR cur IN c_get_det_org(p_in_ids(i)) LOOP
1204 l_wh_type := WSH_EXTERNAL_INTERFACE_SV.Get_Warehouse_Type
1205 (p_organization_id => cur.organization_id,
1206 x_return_status => l_return_status,
1207 p_delivery_detail_id => p_in_ids(i),
1208 p_msg_display => 'N');
1209
1210 -- 5746444: disable this condition when OTM is installed
1211 IF (nvl(l_wh_type, FND_API.G_MISS_CHAR) IN ('TPW','CMS'))
1212 OR (cur.source_code='WSH' and cur.container_flag='N'
1213 and l_gc3_is_installed = 'N' )
1214 THEN
1215 IF l_debug_on THEN
1216 WSH_DEBUG_SV.log(l_module_name,'Get_Warehouse_Type cur.organization_id,l_wh_type,l_return_status',cur.organization_id||l_wh_type||l_return_status);
1217 WSH_DEBUG_SV.log(l_module_name,'source_code, container_flag',cur.source_code||cur.container_flag);
1218 END IF;
1219
1220 FND_MESSAGE.SET_NAME('WSH','WSH_IGNORE_PLAN_DETTPW_ERROR');
1221 FND_MESSAGE.SET_TOKEN('DET_ID',p_in_ids(i));
1222 wsh_util_core.add_message(WSH_UTIL_CORE.g_ret_sts_error,l_module_name);
1223 l_num_error:=l_num_error+1;
1224 GOTO next_det;
1225 END IF;
1226
1227 END LOOP;
1228 END IF;
1229
1230 --3. check if line is assigned to a container. if so, user has to perform from topmost entity
1231 FOR cur IN c_get_det_ignoreflag_diff_cont(p_in_ids(i), l_ignoreplan) LOOP
1232 FND_MESSAGE.SET_NAME('WSH','WSH_IGNOREPLAN_DET_ASSN_CONT');
1233 FND_MESSAGE.SET_TOKEN('DET_ID',p_in_ids(i));
1234 wsh_util_core.add_message(WSH_UTIL_CORE.g_ret_sts_error,l_module_name);
1235 l_num_error:=l_num_error+1;
1236 GOTO next_det;
1237 END LOOP;
1238
1239 SAVEPOINT before_update;
1240 l_is_container :='N';
1241 FOR cur in c_get_container(p_in_ids(i)) LOOP
1242 l_is_container :=cur.container_flag;
1243 l_container_name :=cur.container_name;
1244 l_cont_org_id := cur.organization_id;
1245 l_cont_line_dir := cur.organization_id;
1246 END LOOP;
1247
1248 --If line is a container, get all lines inside and update them and then update container
1249 IF l_is_container='Y' THEN
1250 --
1251 l_tmp_detail_ids.delete;
1252 l_tmp_detail_ids(l_tmp_detail_ids.COUNT+1):=p_in_ids(i);
1253 FOR l_det_cur IN c_get_cont_lines(p_in_ids(i)) LOOP
1254 l_tmp_detail_ids(l_tmp_detail_ids.COUNT+1):=l_det_cur.delivery_detail_id;
1255 END LOOP;
1256
1257 --update all details in hierarchy
1258 IF l_tmp_detail_ids is not null and l_tmp_detail_ids.COUNT>0 THEN
1259 FORALL i in l_tmp_detail_ids.FIRST..l_tmp_detail_ids.LAST
1260 UPDATE wsh_delivery_details
1261 SET ignore_for_planning=l_ignoreplan,
1262 last_update_date = sysdate,
1263 last_updated_by = FND_GLOBAL.USER_ID
1264 WHERE delivery_detail_id=l_tmp_detail_ids(i)
1265 RETURNING container_flag, organization_id, line_direction bulk collect into l_cont_flag_tbl, l_orgn_id_tbl, l_line_dir_tbl; -- LPN CONV. rv
1266 IF (SQL%NOTFOUND) THEN
1267 rollback to before_update;
1268 FND_MESSAGE.SET_NAME('WSH','WSH_IGNOREPLAN_UPDATE_ERROR');
1269 l_entity := FND_MESSAGE.GET_STRING('WSH','WSH_CONTAINER');
1270 FND_MESSAGE.SET_TOKEN('ENTITY',l_entity);
1271 FND_MESSAGE.SET_TOKEN('NAME',l_container_name);
1272 wsh_util_core.add_message(WSH_UTIL_CORE.g_ret_sts_error,l_module_name);
1273 l_num_error:=l_num_error+1;
1274 GOTO next_det;
1275 END IF;
1276
1277 -- LPN CONV. rv
1278 IF l_cont_flag_tbl.count > 0
1279 AND WSH_WMS_LPN_GRP.G_CALLBACK_REQUIRED = 'Y'
1280 THEN
1281 --{
1282 --
1283 l_cnt_wms_counter := 1;
1284 l_cnt_inv_counter := 1;
1285 --
1286 FOR i in l_tmp_detail_ids.FIRST..l_tmp_detail_ids.LAST
1287 LOOP
1288 --{
1289 --
1290 -- LPN CONV. rv
1291 l_wms_org := wsh_util_validate.check_wms_org(l_orgn_id_tbl(i));
1292 -- LPN CONV. rv
1293
1294 IF (l_wms_org = 'Y' and l_cont_flag_tbl(i) = 'Y'
1295 and nvl(l_line_dir_tbl(i),'O') in ('O', 'IO')) THEN
1296 --{
1297 l_sync_tmp_wms_recTbl.delivery_detail_id_tbl(l_cnt_wms_counter) := l_tmp_detail_ids(i);
1298 l_sync_tmp_wms_recTbl.operation_type_tbl(l_cnt_wms_counter) := 'UPDATE';
1299 l_cnt_wms_counter := l_cnt_wms_counter +1;
1300 --}
1301 ELSIF (l_wms_org = 'N' and l_cont_flag_tbl(i) = 'Y'
1302 and nvl(l_line_dir_tbl(i),'O') in ('O', 'IO')) THEN
1303 --{
1304 l_sync_tmp_inv_recTbl.delivery_detail_id_tbl(l_cnt_inv_counter) := l_tmp_detail_ids(i);
1305 l_sync_tmp_inv_recTbl.operation_type_tbl(l_cnt_inv_counter) := 'UPDATE';
1306 l_cnt_inv_counter := l_cnt_inv_counter +1;
1307
1308 --}
1309 END IF;
1310 --}
1311 END LOOP;
1312 --
1313 --
1314 IF l_debug_on THEN
1315 wsh_debug_sv.LOG(l_module_name, 'Count of l_sync_tmp_wms_recTbl', l_sync_tmp_wms_recTbl.delivery_detail_id_tbl.count);
1316 wsh_debug_sv.LOG(l_module_name, 'Count of l_sync_tmp_inv_recTbl', l_sync_tmp_inv_recTbl.delivery_detail_id_tbl.count);
1317 END IF;
1318 --
1319 --
1320 IF WSH_WMS_LPN_GRP.GK_WMS_UPD_GRP
1321 AND l_sync_tmp_wms_recTbl.delivery_detail_id_tbl.count > 0
1322 THEN
1323 --{
1324 --
1325 -- Debug Statements
1326 --
1327 IF l_debug_on THEN
1328 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_WMS_SYNC_TMP_PKG.MERGE_BULK',WSH_DEBUG_SV.C_PROC_LEVEL);
1329 END IF;
1330 --
1331 WSH_WMS_SYNC_TMP_PKG.MERGE_BULK
1332 (
1333 p_sync_tmp_recTbl => l_sync_tmp_wms_recTbl,
1334 x_return_status => l_return_status
1335 );
1336 --
1337 IF l_debug_on THEN
1338 wsh_debug_sv.log(l_module_name, 'Return status after the call to WSH_WMS_SYNC_TMP_PKG.MERGE_BULK is ', l_return_status);
1339 END IF;
1340 --
1341 WSH_UTIL_CORE.API_POST_CALL
1342 (
1343 p_return_status => l_return_status,
1344 x_num_warnings => l_warn,
1345 x_num_errors => l_num_error,
1346 p_raise_error_flag => false
1347 );
1348 -- deleting the tables right here as they are being used in a loop.
1349 l_sync_tmp_wms_recTbl.delivery_detail_id_tbl.delete;
1350 l_sync_tmp_wms_recTbl.operation_type_tbl.delete;
1351 --
1352 IF (l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)) THEN
1353 GOTO next_det;
1354 END IF;
1355 --
1356 --}
1357 ELSIF WSH_WMS_LPN_GRP.GK_INV_UPD_GRP
1358 AND l_sync_tmp_inv_recTbl.delivery_detail_id_tbl.count > 0
1359 THEN
1360 --{
1361 --
1362 -- Debug Statements
1363 --
1364 IF l_debug_on THEN
1365 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_WMS_SYNC_TMP_PKG.MERGE_BULK',WSH_DEBUG_SV.C_PROC_LEVEL);
1366 END IF;
1367 --
1368 WSH_WMS_SYNC_TMP_PKG.MERGE_BULK
1369 (
1370 p_sync_tmp_recTbl => l_sync_tmp_inv_recTbl,
1371 x_return_status => l_return_status
1372 );
1373
1374 --
1375 IF l_debug_on THEN
1376 wsh_debug_sv.log(l_module_name, 'Return status after the call to WSH_WMS_SYNC_TMP_PKG.MERGE_BULK is ', l_return_status);
1377 END IF;
1378 --
1379 WSH_UTIL_CORE.API_POST_CALL
1380 (
1381 p_return_status => l_return_status,
1382 x_num_warnings => l_warn,
1383 x_num_errors => l_num_error,
1384 p_raise_error_flag => false
1385 );
1386 -- deleting the tables right here as they are being used in a loop.
1387 l_sync_tmp_inv_recTbl.delivery_detail_id_tbl.delete;
1388 l_sync_tmp_inv_recTbl.operation_type_tbl.delete;
1389 --
1390 IF (l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)) THEN
1391 GOTO next_det;
1392 END IF;
1393 --
1394 --}
1395 END IF;
1396 --
1397 --}
1398 END IF;
1399 -- LPN CONV. rv
1400 Check_Shipset_Ignoreflag( p_delivery_detail_ids=>l_tmp_detail_ids,
1401 p_ignore_for_planning=>l_ignoreplan,
1402 p_logexcep=>false,
1403 x_return_status=>l_return_status );
1404 IF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
1405 l_warn:=l_warn+1;
1406 END IF;
1407 IF l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR THEN
1408 raise OTHERS;
1409 END IF;
1410 END IF;
1411 ELSE --not a container
1412
1413 UPDATE wsh_delivery_details
1414 SET ignore_for_planning = l_ignoreplan,
1415 last_update_date = sysdate,
1416 last_updated_by = FND_GLOBAL.USER_ID
1417 WHERE delivery_detail_id=p_in_ids(i);
1418 IF (SQL%NOTFOUND) THEN
1419 rollback to before_update;
1420 FND_MESSAGE.SET_NAME('WSH','WSH_IGNOREPLAN_UPDATE_ERROR');
1421 l_entity := FND_MESSAGE.GET_STRING('WSH','WSH_ENTITY_LINE');
1422 FND_MESSAGE.SET_TOKEN('ENTITY',l_entity);
1423 FND_MESSAGE.SET_TOKEN('NAME',p_in_ids(i));
1424 wsh_util_core.add_message(WSH_UTIL_CORE.g_ret_sts_error,l_module_name);
1425 l_num_error:=l_num_error+1;
1426 END IF;
1427 Check_Shipset_Ignoreflag( p_delivery_detail_id=>p_in_ids(i),
1428 p_ignore_for_planning=>l_ignoreplan,
1429 p_logexcep=>false,
1430 x_return_status=>l_return_status );
1431 IF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
1432 l_warn:=l_warn+1;
1433 END IF;
1434 IF l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR THEN
1435 raise OTHERS;
1436 END IF;
1437 END IF; --container
1438
1439 <<next_det>>
1440 null;
1441 END LOOP;
1442
1443 --if all details have problem show as error
1444 IF l_num_error>0 AND l_num_error=p_in_ids.COUNT THEN
1445 FND_MESSAGE.SET_NAME('WSH','WSH_ALL_IGNORE_PLAN_ERROR');
1446 l_entity := FND_MESSAGE.GET_STRING('WSH','WSH_ENTITY_LINE');
1447 FND_MESSAGE.SET_TOKEN('ENTITY',l_entity);
1448 wsh_util_core.add_message(WSH_UTIL_CORE.g_ret_sts_error,l_module_name);
1449 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1450 IF l_debug_on THEN
1451 WSH_DEBUG_SV.pop(l_module_name);
1452 END IF;
1453 RETURN;
1454 ELSIF l_num_error>0 THEN
1455 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
1456 END IF;
1457 end if; --entity
1458 IF l_warn>0 THEN
1459 FND_MESSAGE.SET_NAME('WSH','WSH_IGNORE_PLAN_WARN');
1460 wsh_util_core.add_message(WSH_UTIL_CORE.g_ret_sts_warning,l_module_name);
1461 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
1462 END IF;
1463
1464 END IF; --tp_is_installed
1465
1466 IF l_debug_on THEN
1467 WSH_DEBUG_SV.pop(l_module_name);
1468 END IF;
1469
1470 EXCEPTION
1471 WHEN OTHERS THEN
1472 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1473 wsh_util_core.default_handler('WSH_TP_RELEASE.change_ignoreplan_status');
1474 --
1475 -- Debug Statements
1476 --
1477 IF l_debug_on THEN
1478 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1479 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1480 END IF;
1481 --
1482 END change_ignoreplan_status;
1483
1484 -- START
1485 -- PROCEDURE firm_entity
1486 --
1487 -- 1. Making a trip as Routing and Contents Firm will make all associated deliveries to be
1488 -- Routing and Contents Firm as well. In addition, if there are any other trips associated
1489 -- to these deliveries they will become ATLEAST Routing Firm.
1490 -- 2. Making a delivery as Routing and Contents Firm will make all associated trips to become
1491 -- ATLEAST Routing Firm.
1492 -- END
1493
1494 PROCEDURE firm_entity( p_entity IN VARCHAR2, --either 'DLVY' or 'TRIP'
1495 p_entity_id IN NUMBER,
1496 x_return_status OUT NOCOPY VARCHAR2) IS
1497
1498 CURSOR c_deliveries (c_trip_id NUMBER) IS
1499 SELECT dg.delivery_id
1500 FROM wsh_trips t,
1501 wsh_trip_stops st,
1502 wsh_delivery_legs dg
1503 WHERE t.trip_id = c_trip_id AND
1504 st.trip_id = t.trip_id AND
1505 dg.pick_up_stop_id = st.stop_id;
1506
1507 --for related trips
1508 -- bug 3687559: we only need to look for unfirmed related trips
1509 CURSOR c_trips (c_trip_id NUMBER, c_delid NUMBER) IS
1510 SELECT t.trip_id
1511 FROM wsh_trips t,
1512 wsh_trip_stops st,
1513 wsh_delivery_legs dg
1514 WHERE t.trip_id <> c_trip_id AND
1515 st.trip_id = t.trip_id AND
1516 dg.pick_up_stop_id = st.stop_id AND
1517 dg.delivery_id=c_delid AND
1518 NVL(t.planned_flag, 'N') = 'N';
1519
1520 --for related trips for this delivery, find atleast one delivery which is either planned/unplanned
1521 --trip doesnot have to be firmed for this case. just planned is enough
1522 CURSOR c_find_planunplandeliveries (c_tripid NUMBER, c_delid NUMBER) IS
1523 SELECT dg.delivery_id
1524 FROM wsh_trips t,
1525 wsh_trip_stops st,
1526 wsh_delivery_legs dg,
1527 wsh_new_deliveries dl
1528 WHERE t.trip_id = c_tripid AND
1529 st.trip_id = t.trip_id AND
1530 dg.pick_up_stop_id = st.stop_id AND
1531 dg.delivery_id=dl.delivery_id AND
1532 dl.delivery_id<>c_delid AND
1533 dl.planned_flag IN ('Y','N') AND
1534 rownum=1;
1535
1536 /******DLVY*****/
1537 --for related trips
1538 -- Bug 3294663, bug 3687559 need to find trips and their firm status
1539 CURSOR c_dlvy_trips (c_delid NUMBER) IS
1540 SELECT distinct st.trip_id, t.planned_flag
1541 FROM wsh_trip_stops st,
1542 wsh_delivery_legs dg,
1543 wsh_trips t
1544 WHERE dg.pick_up_stop_id = st.stop_id AND
1545 dg.delivery_id=c_delid AND
1546 st.trip_id = t.trip_id;
1547
1548 l_plannedflag VARCHAR2(1);
1549 l_deliveries_exist VARCHAR2(1);
1550 l_entity VARCHAR2(2000);
1551
1552 --
1553 l_debug_on BOOLEAN;
1554 --
1555 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'firm_entity';
1556 --
1557
1558 BEGIN
1559
1560 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1561
1562 IF l_debug_on IS NULL THEN
1563 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1564 END IF;
1565
1566 IF l_debug_on THEN
1567 WSH_DEBUG_SV.push(l_module_name);
1568 WSH_DEBUG_SV.log(l_module_name,'p_entity',p_entity);
1569 WSH_DEBUG_SV.log(l_module_name,'p_entity_id',p_entity_id);
1570 END IF;
1571
1572 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1573
1574 SAVEPOINT before_firm;
1575
1576 IF (p_entity='TRIP') THEN
1577 FOR del_cur IN c_deliveries(p_entity_id) LOOP
1578
1579 wsh_delivery_validations.check_plan(p_delivery_id => del_cur.delivery_id,
1580 x_return_status => x_return_status);
1581 IF (x_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR) OR (x_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
1582 rollback to before_firm;
1583 FND_MESSAGE.SET_NAME('WSH','WSH_DELIVERY_CANNOT_FIRM');
1584 FND_MESSAGE.SET_TOKEN('DEL_NAME',wsh_new_deliveries_pvt.get_name(del_cur.delivery_id));
1585 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1586 wsh_util_core.add_message(x_return_status);
1587 IF l_debug_on THEN
1588 WSH_DEBUG_SV.pop(l_module_name);
1589 END IF;
1590 RETURN;
1591 END IF;
1592
1593 l_deliveries_exist:='Y';
1594 UPDATE wsh_new_deliveries
1595 SET planned_flag = c_routing_firm_flag,
1596 last_update_date = sysdate,
1597 last_updated_by = FND_GLOBAL.USER_ID
1598 WHERE delivery_id = del_cur.delivery_id;
1599 --raise error to avoid inconsistency
1600 IF (SQL%NOTFOUND) THEN
1601 FND_MESSAGE.SET_NAME('WSH','WSH_FIRM_UPDATE_ERROR');
1602 l_entity := FND_MESSAGE.GET_STRING('WSH','WSH_DELIVERY');
1603 FND_MESSAGE.SET_TOKEN('ENTITY',l_entity);
1604 FND_MESSAGE.SET_TOKEN('NAME',wsh_new_deliveries_pvt.get_name(del_cur.delivery_id));
1605 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1606 wsh_util_core.add_message(x_return_status);
1607 IF l_debug_on THEN
1608 WSH_DEBUG_SV.pop(l_module_name);
1609 END IF;
1610 rollback to before_firm;
1611 RETURN;
1612 END IF;
1613
1614
1615 FOR l_trips_cur IN c_trips(p_entity_id,del_cur.delivery_id) LOOP
1616
1617 wsh_trip_validations.check_plan(p_trip_id => l_trips_cur.trip_id,
1618 x_return_status => x_return_status);
1619 IF (x_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR) OR (x_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
1620 rollback to before_firm;
1621 FND_MESSAGE.SET_NAME('WSH','WSH_TRIP_CANNOT_FIRM');
1622 FND_MESSAGE.SET_TOKEN('TRIP_NAME',wsh_trips_pvt.get_name(l_trips_cur.trip_id));
1623 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1624 wsh_util_core.add_message(x_return_status);
1625 IF l_debug_on THEN
1626 WSH_DEBUG_SV.pop(l_module_name);
1627 END IF;
1628 RETURN;
1629 END IF;
1630
1631 -- bug 3687559: related trips should become RF (not RCF)
1632 l_plannedflag:='Y';
1633
1634 UPDATE wsh_trips
1635 SET planned_flag = l_plannedflag,
1636 last_update_date = sysdate,
1637 last_updated_by = FND_GLOBAL.USER_ID
1638 WHERE trip_id = l_trips_cur.trip_id;
1639 --return error to avoid inconsistency
1640 IF (SQL%NOTFOUND) THEN
1641 FND_MESSAGE.SET_NAME('WSH','WSH_FIRM_UPDATE_ERROR');
1642 l_entity := FND_MESSAGE.GET_STRING('WSH','WSH_ENTITY_RELATED_TRIP');
1643 FND_MESSAGE.SET_TOKEN('ENTITY',l_entity);
1644 FND_MESSAGE.SET_TOKEN('NAME',wsh_trips_pvt.get_name(l_trips_cur.trip_id));
1645 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1646 wsh_util_core.add_message(x_return_status);
1647 IF l_debug_on THEN
1648 WSH_DEBUG_SV.pop(l_module_name);
1649 END IF;
1650 rollback to before_firm;
1651 RETURN;
1652 END IF;
1653
1654 END LOOP;
1655 END LOOP;
1656
1657 --if l_deliveries_exist is null, no deliveries exist for trip => trip can't be firmed
1658 IF l_deliveries_exist IS null THEN
1659 FND_MESSAGE.SET_NAME('WSH','WSH_FIRM_TRIP_ERROR');
1660 FND_MESSAGE.SET_TOKEN('TRIP_NAME',wsh_trips_pvt.get_name(p_entity_id));
1661 wsh_util_core.add_message(WSH_UTIL_CORE.g_ret_sts_error,l_module_name);
1662 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1663 IF l_debug_on THEN
1664 WSH_DEBUG_SV.pop(l_module_name);
1665 END IF;
1666 rollback to before_firm;
1667 RETURN;
1668 ELSE
1669 UPDATE wsh_trips
1670 SET planned_flag = c_routing_firm_flag ,
1671 last_update_date = sysdate,
1672 last_updated_by = FND_GLOBAL.USER_ID
1673 WHERE trip_id = p_entity_id;
1674 IF (SQL%NOTFOUND) THEN
1675 FND_MESSAGE.SET_NAME('WSH','WSH_TRIP_NOT_FOUND');
1676 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1677 wsh_util_core.add_message(x_return_status);
1678 IF l_debug_on THEN
1679 WSH_DEBUG_SV.pop(l_module_name);
1680 END IF;
1681 rollback to before_firm;
1682 RETURN;
1683 END IF;
1684
1685 END IF;
1686
1687 ELSIF (p_entity='DLVY') THEN
1688 FOR l_trips_cur IN c_dlvy_trips(p_entity_id) LOOP
1689
1690 l_plannedflag:='Y'; -- trip is found; if needed, make it RF.
1691
1692 -- Bug 3687559: making delivery RCF should not upgrade trips to RCF.
1693 -- If trip is already RF or RCF, there is no need to validate or update it.
1694 -- Bug 3294663, When upgrading the delivery from NF to RCF, the trip will be upgraded from NF to RF (not RCF).
1695 -- When upgrading a delivery from CF to RCF, trip should be upgraded from NF to RF.
1696 -- When upgrading a delivery from NF to RCF, trip should be upgraded from RF to RF.
1697
1698 IF NVL(l_trips_cur.planned_flag, 'N') = 'N' THEN
1699 wsh_trip_validations.check_plan(p_trip_id => l_trips_cur.trip_id,
1700 x_return_status => x_return_status);
1701 IF (x_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR) OR (x_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
1702 rollback to before_firm;
1703 FND_MESSAGE.SET_NAME('WSH','WSH_TRIP_CANNOT_FIRM');
1704 FND_MESSAGE.SET_TOKEN('TRIP_NAME',wsh_trips_pvt.get_name(l_trips_cur.trip_id));
1705 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1706 wsh_util_core.add_message(x_return_status);
1707 IF l_debug_on THEN
1708 WSH_DEBUG_SV.pop(l_module_name);
1709 END IF;
1710 RETURN;
1711 END IF;
1712
1713 UPDATE wsh_trips
1714 SET planned_flag = l_plannedflag,
1715 last_update_date = sysdate,
1716 last_updated_by = FND_GLOBAL.USER_ID
1717 WHERE trip_id = l_trips_cur.trip_id;
1718
1719 --return error to avoid inconsistency
1720 IF (SQL%NOTFOUND) THEN
1721 FND_MESSAGE.SET_NAME('WSH','WSH_FIRM_UPDATE_ERROR');
1722 l_entity := FND_MESSAGE.GET_STRING('WSH','WSH_ENTITY_RELATED_TRIP');
1723 FND_MESSAGE.SET_TOKEN('ENTITY',l_entity);
1724 FND_MESSAGE.SET_TOKEN('NAME',wsh_trips_pvt.get_name(l_trips_cur.trip_id));
1725 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1726 wsh_util_core.add_message(x_return_status);
1727 IF l_debug_on THEN
1728 WSH_DEBUG_SV.pop(l_module_name);
1729 END IF;
1730 rollback to before_firm;
1731 RETURN;
1732 END IF;
1733 END IF;
1734
1735 END LOOP;
1736
1737 --if l_plannedflag is null, no trip is associated => delivery can't be firmed
1738 IF l_plannedflag IS null THEN
1739 FND_MESSAGE.SET_NAME('WSH','WSH_FIRM_DELIVERY_ERROR');
1740 FND_MESSAGE.SET_TOKEN('DEL_NAME',wsh_new_deliveries_pvt.get_name(p_entity_id));
1741 wsh_util_core.add_message(WSH_UTIL_CORE.g_ret_sts_error,l_module_name);
1742 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1743 IF l_debug_on THEN
1744 WSH_DEBUG_SV.pop(l_module_name);
1745 END IF;
1746 rollback to before_firm;
1747 RETURN;
1748 ELSE
1749 UPDATE wsh_new_deliveries
1750 SET planned_flag = c_routing_firm_flag,
1751 last_update_date = sysdate,
1752 last_updated_by = FND_GLOBAL.USER_ID
1753 WHERE delivery_id = p_entity_id;
1754
1755 IF (SQL%NOTFOUND) THEN
1756 FND_MESSAGE.SET_NAME('WSH','WSH_DEL_NOT_FOUND');
1757 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1758 wsh_util_core.add_message(x_return_status);
1759 IF l_debug_on THEN
1760 WSH_DEBUG_SV.pop(l_module_name);
1761 END IF;
1762 rollback to before_firm;
1763 RETURN;
1764 END IF;
1765
1766 END IF;
1767
1768 END IF;--p_entity
1769
1770 IF l_debug_on THEN
1771 WSH_DEBUG_SV.pop(l_module_name);
1772 END IF;
1773 EXCEPTION
1774 WHEN OTHERS THEN
1775 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1776 rollback to before_firm;
1777 wsh_util_core.default_handler('WSH_TP_RELEASE.firm_entity');
1778 --
1779 -- Debug Statements
1780 --
1781 IF l_debug_on THEN
1782 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1783 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1784 END IF;
1785 --
1786
1787 end firm_entity;
1788
1789 -- START
1790 -- PROCEDURE unfirm_entity
1791 --
1792 -- 1. Downgrading a trip (to Routing Firm/Unfirm) will make the deliveries in the trip a Contents Firm.
1793 -- If there are other trips associated to this delivery, those trips will become Routing Firm.
1794 -- 2. Downgrading a delivery (to Contents Firm/Unfirm) will make the associated trips to become
1795 -- Routing Firm.
1796 -- 3. Unfirming trip will unfirm CM as well (if CM is firm) - not handled in this api but handled
1797 -- at the group api level.
1798 -- END
1799
1800 PROCEDURE unfirm_entity(
1801 p_entity IN VARCHAR2, --either 'DLVY' or 'TRIP'
1802 p_entity_id IN NUMBER,
1803 p_action IN VARCHAR2, --either 'PLAN' or 'UNPLAN'
1804 x_return_status OUT NOCOPY VARCHAR2) IS
1805
1806 CURSOR c_deliveries IS
1807 SELECT dg.delivery_id
1808 FROM wsh_trip_stops st,
1809 wsh_delivery_legs dg,
1810 wsh_new_deliveries nd
1811 WHERE st.trip_id = p_entity_id AND
1812 dg.pick_up_stop_id = st.stop_id AND
1813 nd.delivery_id=dg.delivery_id AND
1814 nd.planned_flag='F';
1815
1816 --for related trips
1817 CURSOR c_trips (p_delid NUMBER) IS
1818 SELECT t.trip_id
1819 FROM wsh_trips t,
1820 wsh_trip_stops st,
1821 wsh_delivery_legs dg
1822 WHERE t.trip_id <> p_entity_id AND
1823 st.trip_id = t.trip_id AND
1824 dg.pick_up_stop_id = st.stop_id AND
1825 dg.delivery_id=p_delid
1826 and t.planned_flag='F';
1827
1828 /******DLVY*****/
1829 --for related trips
1830 CURSOR c_dlvy_trips IS
1831 SELECT t.trip_id
1832 FROM wsh_trips t,
1833 wsh_trip_stops st,
1834 wsh_delivery_legs dg
1835 WHERE st.trip_id = t.trip_id AND
1836 dg.pick_up_stop_id = st.stop_id AND
1837 dg.delivery_id=p_entity_id
1838 and t.planned_flag='F';
1839
1840 CURSOR c_gettripplannedflag IS
1841 select planned_flag
1842 from wsh_trips
1843 where trip_id=p_entity_id;
1844
1845 l_tripplan VARCHAR2(1);
1846
1847 l_action VARCHAR2(1);
1848 l_entity VARCHAR2(2000);
1849
1850 --
1851 l_debug_on BOOLEAN;
1852 --
1853 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'unfirm_entity';
1854 --
1855 begin
1856
1857 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1858
1859 IF l_debug_on IS NULL THEN
1860 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1861 END IF;
1862
1863 IF l_debug_on THEN
1864 WSH_DEBUG_SV.push(l_module_name);
1865 WSH_DEBUG_SV.log(l_module_name,'p_entity',p_entity);
1866 WSH_DEBUG_SV.log(l_module_name,'p_entity_id',p_entity_id);
1867 WSH_DEBUG_SV.log(l_module_name,'p_action',p_action);
1868 END IF;
1869
1870 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1871
1872 IF p_action='PLAN' THEN
1873 l_action:='Y';
1874 ELSIF p_action='UNPLAN' THEN
1875 l_action:='N';
1876 END IF;
1877
1878 SAVEPOINT before_unfirm;
1879
1880 IF (p_entity='TRIP') THEN
1881
1882 OPEN c_gettripplannedflag;
1883 FETCH c_gettripplannedflag INTO l_tripplan;
1884 CLOSE c_gettripplannedflag;
1885
1886 UPDATE wsh_trips
1887 SET planned_flag = l_action,
1888 last_update_date = sysdate,
1889 last_updated_by = FND_GLOBAL.USER_ID
1890 WHERE trip_id = p_entity_id;
1891
1892 IF (SQL%NOTFOUND) THEN
1893 FND_MESSAGE.SET_NAME('WSH','WSH_TRIP_NOT_FOUND');
1894 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1895 wsh_util_core.add_message(x_return_status);
1896 IF l_debug_on THEN
1897 WSH_DEBUG_SV.pop(l_module_name);
1898 END IF;
1899 rollback to before_unfirm;
1900 RETURN;
1901 END IF;
1902
1903 --if l_tripplan is F, all dels are F, so cursor fetches all deliveries
1904 --else if l_tripplan=Y and user is trying to unplan, see if F deliveries exist
1905 --and make them Y and related trips as Y if F
1906
1907 IF l_tripplan='F' OR (l_tripplan='Y' and l_action='N') THEN
1908 FOR del_cur IN c_deliveries LOOP
1909 --set all deliveries as 'PLAN' irrespective of whether trip is being reduced to planned/unplanned
1910 --as unplanned trip may have planned deliveries, unless (bug 3294663) the trip is being set to RF
1911 --from RCF (l_action = 'Y') in which case the delivery will remain at RCF.
1912 UPDATE wsh_new_deliveries
1913 SET planned_flag = decode(l_action, 'Y', planned_flag, 'Y'),
1914 last_update_date = sysdate,
1915 last_updated_by = FND_GLOBAL.USER_ID
1916 WHERE delivery_id = del_cur.delivery_id;
1917 --raise error to avoid inconsistency
1918 IF (SQL%NOTFOUND) THEN
1919 FND_MESSAGE.SET_NAME('WSH','WSH_FIRM_UPDATE_ERROR');
1920 l_entity := FND_MESSAGE.GET_STRING('WSH','WSH_DELIVERY');
1921 FND_MESSAGE.SET_TOKEN('ENTITY',l_entity);
1922 FND_MESSAGE.SET_TOKEN('NAME',wsh_new_deliveries_pvt.get_name(del_cur.delivery_id));
1923 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1924 wsh_util_core.add_message(x_return_status);
1925 IF l_debug_on THEN
1926 WSH_DEBUG_SV.pop(l_module_name);
1927 END IF;
1928 rollback to before_unfirm;
1929 RETURN;
1930 END IF;
1931
1932
1933 FOR l_trips_cur IN c_trips(del_cur.delivery_id) LOOP
1934 UPDATE wsh_trips
1935 SET planned_flag = 'Y',
1936 last_update_date = sysdate,
1937 last_updated_by = FND_GLOBAL.USER_ID
1938 WHERE trip_id = l_trips_cur.trip_id;
1939 --raise error to avoid inconsistency
1940 IF (SQL%NOTFOUND) THEN
1941 FND_MESSAGE.SET_NAME('WSH','WSH_FIRM_UPDATE_ERROR');
1942 l_entity := FND_MESSAGE.GET_STRING('WSH','WSH_ENTITY_RELATED_TRIP');
1943 FND_MESSAGE.SET_TOKEN('ENTITY',l_entity);
1944 FND_MESSAGE.SET_TOKEN('NAME',wsh_trips_pvt.get_name(l_trips_cur.trip_id));
1945 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1946 wsh_util_core.add_message(x_return_status);
1947 IF l_debug_on THEN
1948 WSH_DEBUG_SV.pop(l_module_name);
1949 END IF;
1950 rollback to before_unfirm;
1951 RETURN;
1952 END IF;
1953
1954 END LOOP;
1955 END LOOP;
1956 END IF; --trip plan flag = F or (trip_plan=Y and del plan flag=F)
1957 ELSIF p_entity='DLVY' THEN
1958
1959 UPDATE wsh_new_deliveries
1960 SET planned_flag = l_action,
1961 last_update_date = sysdate,
1962 last_updated_by = FND_GLOBAL.USER_ID
1963 WHERE delivery_id = p_entity_id;
1964
1965 IF (SQL%NOTFOUND) THEN
1966 FND_MESSAGE.SET_NAME('WSH','WSH_DEL_NOT_FOUND');
1967 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1968 wsh_util_core.add_message(x_return_status);
1969 IF l_debug_on THEN
1970 WSH_DEBUG_SV.pop(l_module_name);
1971 END IF;
1972 rollback to before_unfirm;
1973 RETURN;
1974 END IF;
1975
1976 FOR cur_deltrip IN c_dlvy_trips LOOP
1977 UPDATE wsh_trips
1978 SET planned_flag = 'Y',
1979 last_update_date = sysdate,
1980 last_updated_by = FND_GLOBAL.USER_ID
1981 WHERE trip_id = cur_deltrip.trip_id;
1982
1983 --raise error to avoid inconsistency
1984 IF (SQL%NOTFOUND) THEN
1985 FND_MESSAGE.SET_NAME('WSH','WSH_FIRM_UPDATE_ERROR');
1986 l_entity := FND_MESSAGE.GET_STRING('WSH','WSH_ENTITY_RELATED_TRIP');
1987 FND_MESSAGE.SET_TOKEN('ENTITY',l_entity);
1988 FND_MESSAGE.SET_TOKEN('NAME',wsh_trips_pvt.get_name(p_entity_id));
1989 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1990 wsh_util_core.add_message(x_return_status);
1991 IF l_debug_on THEN
1992 WSH_DEBUG_SV.pop(l_module_name);
1993 END IF;
1994 rollback to before_unfirm;
1995 RETURN;
1996 END IF;
1997 END LOOP;
1998
1999 END IF;
2000
2001 IF l_debug_on THEN
2002 WSH_DEBUG_SV.pop(l_module_name);
2003 END IF;
2004
2005 EXCEPTION
2006 WHEN OTHERS THEN
2007 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
2008 wsh_util_core.default_handler('WSH_TP_RELEASE.unfirm_entity');
2009 rollback to before_unfirm;
2010 --
2011 -- Debug Statements
2012 --
2013 IF l_debug_on THEN
2014 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2015 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
2016 END IF;
2017 --
2018
2019 end unfirm_entity;
2020
2021 PROCEDURE calculate_lpn_tpdates(p_delivery_detail_id NUMBER,
2022 x_updated_flag OUT NOCOPY VARCHAR2,
2023 x_delivery_id OUT NOCOPY NUMBER,
2024 x_return_status OUT NOCOPY VARCHAR2);
2025 PROCEDURE refresh_lpn_hierarchy_dates(l_lpndetail_ids IN wsh_util_core.id_tab_type,
2026 x_upd_del_tab OUT NOCOPY wsh_util_core.id_tab_type,
2027 x_return_status OUT NOCOPY VARCHAR2);
2028 PROCEDURE calculate_del_tpdates(l_del_ids IN wsh_util_core.id_tab_type,
2029 l_refresh_lpn_flag IN VARCHAR2,
2030 x_return_status OUT NOCOPY VARCHAR2);
2031
2032 --needs to be changed based on Roy's email and after talking to Hema
2033 --to be used only if source_code='OE'. OKE lines will already be populated
2034 /**
2035 * Calculates the TP dates based on the OE dates provided.
2036 * The calculation is based on the profile values Earliest_Profile and LDD Profile.
2037 */
2038 PROCEDURE calculate_tp_dates(
2039 p_request_date_type IN VARCHAR2,
2040 p_latest_acceptable_date IN DATE,
2041 p_promise_date IN DATE,
2042 p_schedule_arrival_date IN DATE,
2043 p_schedule_ship_date IN DATE,
2044 p_earliest_acceptable_date IN DATE,
2045 p_demand_satisfaction_date IN DATE,
2046 p_source_line_id NUMBER,
2047 p_source_code IN VARCHAR2,
2048 p_organization_id NUMBER,
2049 p_inventory_item_id NUMBER,
2050 x_return_status OUT NOCOPY VARCHAR2,
2051 x_earliest_pickup_date OUT NOCOPY DATE,
2052 x_latest_pickup_date OUT NOCOPY DATE,
2053 x_earliest_dropoff_date OUT NOCOPY DATE,
2054 x_latest_dropoff_date OUT NOCOPY DATE
2055 ) IS
2056
2057
2058 l_request_date_type VARCHAR(20);
2059 l_days_profile NUMBER;
2060 item_type VARCHAR2(10);
2061 --
2062 l_earliest_pickup_date DATE;
2063 l_latest_pickup_date DATE;
2064 l_earliest_dropoff_date DATE;
2065 l_latest_dropoff_date DATE;
2066
2067 l_deldetail_creation_date DATE;
2068 l_delivery_detail_id NUMBER;
2069 l_split_delivery_detail_id NUMBER;
2070 l_creation_date DATE;
2071 l_latest_acceptable_date DATE;
2072 l_promise_date DATE;
2073 l_earliest_acceptable_date DATE;
2074 l_demand_satisfaction_date DATE;
2075
2076 l_inventory_item_id NUMBER;
2077 l_organization_id NUMBER;
2078 l_atp_flag VARCHAR(1);
2079
2080
2081 CURSOR c_oe_item_id(p_source_line_id IN NUMBER) IS
2082 SELECT ship_from_org_id,inventory_item_id
2083 FROM OE_ORDER_LINES_ALL
2084 WHERE line_id = p_source_line_id;
2085
2086 CURSOR c_dd_item_id(p_source_line_id IN NUMBER,p_source_code IN VARCHAR2) IS
2087 SELECT organization_id,inventory_item_id,delivery_detail_id,split_from_delivery_detail_id,creation_date
2088 FROM WSH_DELIVERY_DETAILS
2089 WHERE source_line_id = p_source_line_id
2090 AND source_code = p_source_code;
2091
2092 CURSOR c_min_ddcreation_date(p_source_line_id IN NUMBER,p_source_code IN VARCHAR2) IS
2093 SELECT min(creation_date)
2094 FROM WSH_DELIVERY_DETAILS
2095 WHERE source_line_id = p_source_line_id
2096 AND source_code = p_source_code;
2097
2098 CURSOR c_atp_flag_info(p_inventory_item_id IN NUMBER,p_org_id IN NUMBER) IS
2099 SELECT ATP_FLAG
2100 FROM MTL_SYSTEM_ITEMS
2101 WHERE inventory_item_id = p_inventory_item_id
2102 AND organization_id = p_org_id;
2103
2104
2105
2106 --
2107 l_debug_on BOOLEAN;
2108 --
2109 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'calculate_tp_dates';
2110 --
2111
2112
2113 l_earliestprofile_EAD CONSTANT VARCHAR2(20) := 'EAR_ACC_DATE';
2114 l_earliestprofile_LD CONSTANT VARCHAR2(20) := 'SAME_SHIP_DELIVER';
2115
2116 l_LDDprofile_LPS CONSTANT VARCHAR2(20) := 'LAD_PD_SCH';
2117 l_LDDprofile_PS CONSTANT VARCHAR2(10) := 'PD_SCH';
2118 l_LDDprofile_S CONSTANT VARCHAR2(10) := 'SCH_DATE';
2119 l_modified VARCHAR2(1);
2120 adjustDates BOOLEAN := FALSE;
2121
2122 others EXCEPTION;
2123
2124 BEGIN
2125
2126 --
2127 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
2128 --
2129 IF l_debug_on IS NULL
2130 THEN
2131 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
2132 END IF;
2133
2134 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2135
2136
2137 --
2138 IF l_debug_on THEN
2139 wsh_debug_sv.push(l_module_name, 'calculate_tp_dates');
2140 wsh_debug_sv.log (l_module_name,'p_request_date_type', p_request_date_type);
2141 wsh_debug_sv.log (l_module_name,'p_latest_acceptable_date', p_latest_acceptable_date);
2142 wsh_debug_sv.log (l_module_name,'p_promise_date', p_promise_date);
2143 wsh_debug_sv.log (l_module_name,'p_schedule_arrival_date',p_schedule_arrival_date);
2144 wsh_debug_sv.log (l_module_name,'p_schedule_ship_date',p_schedule_ship_date);
2145 wsh_debug_sv.log (l_module_name,'p_earliest_acceptable_date', p_earliest_acceptable_date);
2146 wsh_debug_sv.log (l_module_name,'p_source_line_id', p_source_line_id);
2147 wsh_debug_sv.log (l_module_name,'p_source_code', p_source_code);
2148 wsh_debug_sv.log (l_module_name,'p_organization_id', p_organization_id);
2149 wsh_debug_sv.log (l_module_name,'p_inventory_item_id', p_inventory_item_id);
2150 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_CUSTOM_PUB.calculate_tp_dates',WSH_DEBUG_SV.C_PROC_LEVEL);
2151 END IF;
2152
2153
2154 WSH_CUSTOM_PUB.calculate_tp_dates(
2155 p_source_line_id => p_source_line_id,
2156 p_source_code => p_source_code,
2157 x_earliest_pickup_date => l_earliest_pickup_date,
2158 x_latest_pickup_date => l_latest_pickup_date,
2159 x_earliest_dropoff_date => l_earliest_dropoff_date,
2160 x_latest_dropoff_date => l_latest_dropoff_date,
2161 x_modified => l_modified);
2162
2163 IF l_debug_on THEN
2164 wsh_debug_sv.log (l_module_name,'l_modified', l_modified);
2165 END IF;
2166
2167 IF l_modified='Y' THEN -- use results from customized call, go directly to end of procedure
2168 x_earliest_pickup_date := l_earliest_pickup_date;
2169 x_latest_pickup_date := l_latest_pickup_date;
2170 x_earliest_dropoff_date := l_earliest_dropoff_date;
2171 x_latest_dropoff_date := l_latest_dropoff_date;
2172
2173 IF l_debug_on THEN
2174 wsh_debug_sv.logmsg (l_module_name,'Results from customized call...');
2175 wsh_debug_sv.log (l_module_name,'l_earliest_pickup_date', l_earliest_pickup_date);
2176 wsh_debug_sv.log (l_module_name,'l_latest_pickup_date', l_latest_pickup_date);
2177 wsh_debug_sv.log (l_module_name,'l_earliest_dropoff_date', l_earliest_dropoff_date);
2178 wsh_debug_sv.log (l_module_name,'l_latest_dropoff_date', l_latest_dropoff_date);
2179 END IF;
2180
2181 ELSE -- l_modified='N' - use our calculation
2182
2183 --Bug 3816115
2184 IF G_LDD_profile IS NULL THEN
2185 G_LDD_profile := NVL(FND_PROFILE.VALUE('ONT_SHIP_DEADLINE_SEQUENCE'),
2186 l_LDDprofile_LPS);
2187 END IF;
2188 IF G_earliest_profile IS NULL THEN
2189 G_earliest_profile := NVL(FND_PROFILE.VALUE('ONT_EARLY_SHIP_DATE_SOURCE'),
2190 l_earliestprofile_EAD);
2191 END IF;
2192
2193 IF l_debug_on THEN
2194 wsh_debug_sv.log (l_module_name,'G_LDD_profile', G_LDD_profile);
2195 wsh_debug_sv.log (l_module_name,'G_earliest_profile', G_earliest_profile);
2196 END IF;
2197
2198 --Bug 3816115
2199 IF G_lc_days_profile IS NULL THEN
2200 G_lc_days_profile := NVL(FND_PROFILE.VALUE('FTE_LATESHIP_OFFSET_DAYS'), 90);
2201 END IF;
2202 --
2203 BEGIN
2204 l_days_profile := TO_NUMBER(G_lc_days_profile);
2205 EXCEPTION
2206 WHEN others THEN
2207 l_days_profile := 0;
2208 END;
2209 --
2210 IF l_days_profile < 0 THEN
2211 l_days_profile := 0;
2212 END IF;
2213 --
2214 IF l_debug_on THEN
2215 wsh_debug_sv.log(l_module_name, 'l_days_profile', l_days_profile);
2216 END IF;
2217 --
2218 -- Bug 4368984
2219 -- Populate lastest ship/delivery date
2220 IF G_populate_date_profile IS NULL THEN
2221 G_populate_date_profile := NVL(FND_PROFILE.VALUE('WSH_POPULATE_LATEST_SHIP_DELIVERY_DATE'), 'N');
2222 END IF;
2223
2224 IF l_debug_on THEN
2225 wsh_debug_sv.log (l_module_name,'G_populate_date_profile', G_populate_date_profile);
2226 END IF;
2227 --
2228
2229 IF p_request_date_type IS NULL THEN
2230 l_request_date_type := 'SHIP';
2231 ELSE
2232 l_request_date_type := p_request_date_type;
2233 END IF;
2234 IF (p_organization_id IS NULL OR p_organization_id = FND_API.G_MISS_NUM)
2235 OR (p_inventory_item_id IS NULL OR p_inventory_item_id = FND_API.G_MISS_NUM) THEN
2236
2237 OPEN c_dd_item_id(p_source_line_id,p_source_code);
2238 FETCH c_dd_item_id INTO l_organization_id,l_inventory_item_id,
2239 l_delivery_detail_id,l_split_delivery_detail_id,l_creation_date;
2240 IF c_dd_item_id%NOTFOUND THEN
2241 CLOSE c_dd_item_id;
2242 l_deldetail_creation_date := sysdate;
2243 OPEN c_oe_item_id(p_source_line_id);
2244 FETCH c_oe_item_id INTO l_organization_id,l_inventory_item_id;
2245 IF c_oe_item_id%NOTFOUND THEN
2246 CLOSE c_oe_item_id;
2247 raise others;
2248 END IF;
2249 IF c_oe_item_id%ISOPEN THEN
2250 CLOSE c_oe_item_id;
2251 END IF;
2252 ELSE
2253 IF l_split_delivery_detail_id is NULL THEN
2254 l_deldetail_creation_date := l_creation_date;
2255 ELSE
2256 OPEN c_min_ddcreation_date(p_source_line_id,p_source_code);
2257 FETCH c_min_ddcreation_date INTO l_creation_date;
2258 CLOSE c_min_ddcreation_date;
2259 l_deldetail_creation_date := l_creation_date;
2260 END IF;
2261 END IF;
2262 IF c_dd_item_id%ISOPEN THEN
2263 CLOSE c_dd_item_id;
2264 END IF;
2265 ELSE
2266 OPEN c_min_ddcreation_date(p_source_line_id,p_source_code);
2267 FETCH c_min_ddcreation_date INTO l_creation_date;
2268 CLOSE c_min_ddcreation_date;
2269 l_deldetail_creation_date := l_creation_date;
2270 l_inventory_item_id := p_inventory_item_id;
2271 l_organization_id := p_organization_id;
2272 END IF;
2273
2274 OPEN c_atp_flag_info(l_inventory_item_id,l_organization_id);
2275 FETCH c_atp_flag_info INTO l_atp_flag;
2276 IF c_atp_flag_info%NOTFOUND THEN
2277 CLOSE c_atp_flag_info;
2278 raise others;
2279 END IF;
2280 IF c_atp_flag_info%ISOPEN THEN
2281 CLOSE c_atp_flag_info;
2282 END IF;
2283 IF (l_atp_flag ='Y') OR (l_atp_flag = 'C') THEN
2284 item_type := 'ATP';
2285 ELSE
2286 item_type := 'NON-ATP';
2287 END IF;
2288
2289
2290 -- Latest Ship Date(Ship) and Latest Delivery Date(Arrival) Calculation
2291 IF ((p_latest_acceptable_date IS NOT NULL) AND
2292 (to_char(p_latest_acceptable_date,'HH24:MI') = '00:00') OR (to_char(p_latest_acceptable_date,'HH24:MI') = '00:01')) THEN
2293
2294 l_latest_acceptable_date := to_date((to_char(p_latest_acceptable_date,'mm-dd-yy')||' 23:59:00'),'mm-dd-yy HH24:MI:SS');
2295
2296 ELSE
2297
2298 l_latest_acceptable_date := p_latest_acceptable_date;
2299
2300 END IF;
2301
2302 IF ((p_promise_date IS NOT NULL) AND
2303 (to_char(p_promise_date,'HH24:MI') = '00:00') OR (to_char(p_promise_date,'HH24:MI') = '00:01')) THEN
2304 l_promise_date := to_date((to_char(p_promise_date,'mm-dd-yy')||' 23:59:00'),'mm-dd-yy HH24:MI:SS');
2305 ELSE
2306 l_promise_date := p_promise_date;
2307 END IF;
2308
2309 IF l_request_date_type='SHIP' THEN
2310 --
2311 IF G_LDD_profile =l_LDDprofile_LPS THEN
2312 l_latest_pickup_date:=nvl(l_latest_acceptable_date, nvl(l_promise_date, p_schedule_ship_date ));
2313 ELSIF G_LDD_profile =l_LDDprofile_PS THEN
2314 l_latest_pickup_date:=nvl(l_promise_date, p_schedule_ship_date );
2315 ELSIF G_LDD_profile = l_LDDprofile_S THEN
2316 l_latest_pickup_date:=p_schedule_ship_date ;
2317 END IF;
2318 --
2319 ELSIF l_request_date_type='ARRIVAL' THEN
2320 --
2321 IF G_LDD_profile =l_LDDprofile_LPS THEN
2322 l_latest_dropoff_date:=nvl(l_latest_acceptable_date, nvl(l_promise_date, p_schedule_arrival_date ));
2323 ELSIF G_LDD_profile =l_LDDprofile_PS THEN
2324 l_latest_dropoff_date:=nvl(l_promise_date, p_schedule_arrival_date );
2325 ELSIF G_LDD_profile =l_LDDprofile_S THEN
2326 l_latest_dropoff_date:=p_schedule_arrival_date ;
2327 END IF;
2328 --
2329 END IF;
2330
2331 -- END Latest Ship Date(Ship) and Latest Delivery Date(Arrival) Calculation
2332 /** bugfix 10013948 populated l_demand_satisfaction_date as schedule ship date **/
2333 IF p_schedule_ship_date IS NOT NULL THEN
2334 l_demand_satisfaction_date := to_date((to_char(p_schedule_ship_date,'mm-dd-yy')||' 00:01:00'),'mm-dd-yy HH24:MI:SS');
2335 ELSE
2336 l_demand_satisfaction_date := NULL;
2337 END IF;
2338
2339 IF l_debug_on THEN
2340 wsh_debug_sv.log (l_module_name,'l_demand_staisfaction_date', l_demand_satisfaction_date);
2341 END IF ;
2342
2343 IF l_request_date_type='SHIP' THEN
2344 --{
2345 -- Earliest Ship Date Calculation
2346 IF ((p_earliest_acceptable_date IS NOT NULL) AND
2347 (to_char(p_earliest_acceptable_date,'HH24:MI') = '00:00') OR (to_char(p_earliest_acceptable_date,'HH24:MI') = '23:59')) THEN
2348
2349 l_earliest_acceptable_date := to_date((to_char(p_earliest_acceptable_date,'mm-dd-yy')||' 00:01:00'),'mm-dd-yy HH24:MI:SS');
2350
2351 ELSE
2352 l_earliest_acceptable_date := p_earliest_acceptable_date;
2353 END IF;
2354 --
2355 IF item_type='ATP' THEN
2356 --{
2357 IF G_earliest_profile=l_earliestprofile_EAD THEN
2358 IF (p_earliest_acceptable_date is null) OR (l_demand_satisfaction_date > p_earliest_acceptable_date) THEN
2359 l_earliest_pickup_date := l_demand_satisfaction_date;
2360 ELSE
2361 l_earliest_pickup_date := l_earliest_acceptable_date;
2362 END IF;
2363 END IF;
2364 --
2365 IF G_earliest_profile=l_earliestprofile_LD THEN
2366 IF (l_demand_satisfaction_date is null) OR (l_latest_pickup_date > l_demand_satisfaction_date) THEN
2367 IF l_latest_pickup_date IS NOT NULL THEN
2368 l_earliest_pickup_date := to_date((to_char(l_latest_pickup_date,'mm-dd-yy')||' 00:01:00'),'mm-dd-yy HH24:MI:SS');
2369 ELSE
2370 l_earliest_pickup_date := NULL;
2371 END IF;
2372 ELSE
2373 l_earliest_pickup_date := l_demand_satisfaction_date;
2374 END IF;
2375 END IF;
2376 --}
2377 ELSE
2378 --{
2379 IF G_earliest_profile=l_earliestprofile_EAD THEN
2380 IF l_earliest_acceptable_date IS NOT NULL THEN
2381 l_earliest_pickup_date := l_earliest_acceptable_date;
2382 ELSE
2383 l_earliest_pickup_date := l_deldetail_creation_date;
2384 END IF;
2385 END IF;
2386 --
2387 IF G_earliest_profile=l_earliestprofile_LD THEN
2388 IF l_latest_pickup_date IS NOT NULL THEN
2389 l_earliest_pickup_date := to_date((to_char(l_latest_pickup_date,'mm-dd-yy')||' 00:01:00'),'mm-dd-yy HH24:MI:SS');
2390 ELSE
2391 l_earliest_pickup_date := NULL;
2392 END IF;
2393 END IF;
2394 --}
2395 END IF;
2396
2397 -- End of Earliest Ship Date Calculation
2398
2399 -- Earliest/Latest Delivery Date Calculation
2400 l_earliest_dropoff_date := NULL;
2401
2402 -- Bug 4368984
2403 IF G_populate_date_profile = 'Y' THEN
2404 l_latest_dropoff_date := to_date((to_char(p_schedule_arrival_date,'mm-dd-yy')||' 23:59:00'),'mm-dd-yy HH24:MI:SS');
2405 ELSE
2406 l_latest_dropoff_date := NULL;
2407 END IF;
2408 --
2409 -- End Earliest/Latest Delivery Date Calculation
2410 --}
2411 END IF; -- date_type is 'SHIP'
2412
2413
2414 IF l_request_date_type='ARRIVAL' THEN
2415 -- Earliest Ship Date Calculation
2416 IF item_type='ATP' THEN
2417 IF (l_demand_satisfaction_date is not null) THEN
2418 l_earliest_pickup_date := to_date((to_char(l_demand_satisfaction_date,'mm-dd-yy')||' 00:01:00'),'mm-dd-yy HH24:MI:SS');
2419 ELSE
2420 l_earliest_pickup_date := NULL;
2421 END IF;
2422 ELSE
2423 l_earliest_pickup_date := l_deldetail_creation_date;
2424 END IF;
2425 -- End of Earliest Ship Date Calculation
2426
2427
2428 -- Latest Ship Date Calculation
2429
2430 -- Bug 4368984
2431 IF G_populate_date_profile = 'Y' THEN
2432 l_latest_pickup_date := to_date((to_char(p_schedule_ship_date,'mm-dd-yy')||' 23:59:00'),'mm-dd-yy HH24:MI:SS');
2433 ELSE
2434 l_latest_pickup_date := l_latest_dropoff_date+l_days_profile;
2435 END IF;
2436 --
2437
2438 -- End of Latest Ship Date Calculation
2439
2440 -- Earliest Delivery Date Calculation
2441 IF (p_earliest_acceptable_date is not null) AND
2442 ((to_char(p_earliest_acceptable_date,'HH24:MI') = '00:00') OR (to_char(p_earliest_acceptable_date,'HH24:MI') = '23:59')) THEN
2443
2444 l_earliest_acceptable_date := to_date((to_char(p_earliest_acceptable_date,'mm-dd-yy')||' 00:01:00'),'mm-dd-yy HH24:MI:SS');
2445
2446 ELSE
2447 l_earliest_acceptable_date := p_earliest_acceptable_date;
2448 END IF;
2449
2450 IF G_earliest_profile=l_earliestprofile_EAD THEN
2451 l_earliest_dropoff_date:=l_earliest_acceptable_date;
2452 ELSIF G_earliest_profile=l_earliestprofile_LD THEN
2453 IF (l_latest_dropoff_date is not null) THEN
2454 l_earliest_dropoff_date:= to_date((to_char(l_latest_dropoff_date,'mm-dd-yy')||' 00:01:00'),'mm-dd-yy HH24:MI:SS');
2455 ELSE
2456 l_earliest_dropoff_date := NULL;
2457 END IF;
2458 END IF;
2459 -- End of Earliest Delivery Date Calculation
2460
2461 END IF; -- date_type is 'Arrival'
2462 --
2463 x_earliest_pickup_date := l_earliest_pickup_date;
2464 x_latest_pickup_date := l_latest_pickup_date;
2465 x_earliest_dropoff_date := l_earliest_dropoff_date;
2466 x_latest_dropoff_date := l_latest_dropoff_date;
2467
2468 IF ((to_char(x_earliest_pickup_date,'HH24:MI') = '00:00') OR (to_char(x_earliest_pickup_date,'HH24:MI') = '23:59')) THEN
2469 x_earliest_pickup_date := to_date((to_char(x_earliest_pickup_date,'MM-DD-YYYY')||' 00:01:00'),'MM-DD-YYYY HH24:MI:SS');
2470 END IF;
2471 IF ((to_char(x_earliest_dropoff_date,'HH24:MI') = '00:00') OR (to_char(x_earliest_dropoff_date,'HH24:MI') = '23:59')) THEN
2472 x_earliest_dropoff_date := to_date((to_char(x_earliest_dropoff_date,'MM-DD-YYYY')||' 00:01:00'),'MM-DD-YYYY HH24:MI:SS');
2473 END IF;
2474 IF ((to_char(x_latest_pickup_date,'HH24:MI') = '00:00') OR (to_char(x_latest_pickup_date,'HH24:MI') = '23:59')) THEN
2475 x_latest_pickup_date := to_date((to_char(x_latest_pickup_date,'MM-DD-YYYY')||' 23:59:00'),'MM-DD-YYYY HH24:MI:SS');
2476 END IF;
2477 IF ((to_char(x_latest_dropoff_date,'HH24:MI') = '00:00') OR (to_char(x_latest_dropoff_date,'HH24:MI') = '23:59')) THEN
2478 x_latest_dropoff_date := to_date((to_char(x_latest_dropoff_date,'MM-DD-YYYY')||' 23:59:00'),'MM-DD-YYYY HH24:MI:SS');
2479 END IF;
2480
2481 --bug 3798349 : if earliest dates happen to be > latest dates, set latest=earliest
2482 --and log exception against detail
2483 --if earliest dates timecomponent is 00:00 or 23:59, it should be adjusted to 00:01
2484 --if latest dates timecomponent is 00:00 or 23:59, it should be adjusted to 23:59
2485 IF x_earliest_pickup_date > x_latest_pickup_date
2486 OR x_earliest_dropoff_date > x_latest_dropoff_date THEN
2487 OPEN c_dd_item_id(p_source_line_id,p_source_code);
2488 LOOP
2489
2490 FETCH c_dd_item_id INTO l_organization_id,l_inventory_item_id,
2491 l_delivery_detail_id,l_split_delivery_detail_id,l_creation_date;
2492
2493 EXIT WHEN c_dd_item_id%NOTFOUND;
2494 adjustDates := TRUE;
2495 IF x_earliest_pickup_date > x_latest_pickup_date THEN
2496 log_tpdate_exception('LINE',l_delivery_detail_id,TRUE,x_earliest_pickup_date,x_latest_pickup_date);
2497 END IF;
2498
2499 IF x_earliest_dropoff_date > x_latest_dropoff_date THEN
2500 log_tpdate_exception('LINE',l_delivery_detail_id,FALSE,x_earliest_dropoff_date,x_latest_dropoff_date);
2501 END IF;
2502 END LOOP;
2503 CLOSE c_dd_item_id;
2504 IF adjustDates THEN
2505 IF x_earliest_pickup_date > x_latest_pickup_date THEN
2506 x_latest_pickup_date := x_earliest_pickup_date;
2507 END IF;
2508
2509 IF x_earliest_dropoff_date > x_latest_dropoff_date THEN
2510 x_latest_dropoff_date := x_earliest_dropoff_date;
2511 END IF;
2512 END IF;
2513 END IF;
2514
2515 END IF; --l_modified='Y'
2516
2517 --
2518 IF l_debug_on THEN
2519 wsh_debug_sv.log (l_module_name,'x_earliest_pickup_date', x_earliest_pickup_date);
2520 wsh_debug_sv.log (l_module_name,'x_latest_pickup_date', x_latest_pickup_date);
2521 wsh_debug_sv.log (l_module_name,'x_earliest_dropoff_date', x_earliest_dropoff_date);
2522 wsh_debug_sv.log (l_module_name,'x_latest_dropoff_date', x_latest_dropoff_date);
2523 WSH_DEBUG_SV.log(l_module_name, 'x_return_status', x_return_status);
2524 WSH_DEBUG_SV.pop(l_module_name);
2525 END IF;
2526 --
2527 EXCEPTION
2528 WHEN OTHERS THEN
2529 x_return_status := WSH_UTIL_CORE.g_ret_sts_unexp_error;
2530 IF l_debug_on THEN
2531 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,
2532 WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2533 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
2534 END IF;
2535 END calculate_tp_dates;
2536
2537 /**
2538 * calculate_cont_del_tpdates calculates TPdates for the Deliveries and containers.
2539 * p_entity takes in three values DLVY, LPN and DLVB
2540 * If DLVY p_entity_ids are to be deliveryIds (This will also update underlying containers if any)
2541 * If LPN p_entity_ids are to be ContainerId(delivery_detail_ids with container flag 'Y')
2542 * If DLVB p_entity_ids are to be delivery_detail_ids. From the delivery_detail_ids delivery_ids,container_ids
2543 * are derived.
2544 * If the delivery_detail_id is assigned to delivery_id the corresponding delivery_id is added to delivery_list.
2545 * If the delivery_detail_id is packed in container the corresponding container_id(delivery_detail_id)
2546 * is added to container_list
2547 * If the delivery_detail_id itself is container then it is added to container_list
2548 * Atlast the tp dates are calculated for sorted delivery_list and container_list.
2549 */
2550
2551 PROCEDURE calculate_cont_del_tpdates(p_entity IN VARCHAR2,
2552 p_entity_ids IN wsh_util_core.id_tab_type,
2553 x_return_status OUT NOCOPY VARCHAR2) IS
2554
2555 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'calculate_cont_del_tpdates';
2556 --
2557 l_debug_on BOOLEAN;
2558
2559 l_earliest_mpickup_date DATE;
2560 l_earliest_mdropoff_date DATE;
2561 l_latest_mpickup_date DATE;
2562 l_latest_mdropoff_date DATE;
2563
2564
2565 dlvry_ids Distinct_Ids_tab;
2566 detail_ids Distinct_Ids_tab;
2567
2568
2569 CURSOR c_MasterLPN_del_id(p_delivery_detail_id IN NUMBER) IS
2570 SELECT wda.parent_delivery_detail_id,wda.delivery_id,container_flag
2571 FROM wsh_delivery_details wdd,wsh_delivery_assignments_v wda
2572 WHERE wda.delivery_detail_id = wdd.delivery_detail_id
2573 AND wda.delivery_detail_id = p_delivery_detail_id;
2574
2575 l_entity_id NUMBER;
2576 l_delivery_id NUMBER;
2577 l_mas_detail_id NUMBER;
2578 l_container_flag VARCHAR(2);
2579
2580
2581 l_del_ids wsh_util_core.id_tab_type;
2582 l_dummy_ids wsh_util_core.id_tab_type;
2583 l_lpndetail_ids wsh_util_core.id_tab_type;
2584
2585 del_index NUMBER;
2586 detail_index NUMBER;
2587
2588 j BINARY_INTEGER;
2589 others exception;
2590
2591 BEGIN
2592 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
2593 --
2594 IF l_debug_on IS NULL THEN
2595 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
2596 END IF;
2597
2598 IF l_debug_on THEN
2599 wsh_debug_sv.push(l_module_name);
2600 wsh_debug_sv.log (l_module_name,'p_entity', p_entity);
2601 wsh_debug_sv.log (l_module_name,'p_entity_ids.count', p_entity_ids.count);
2602 END IF;
2603
2604 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2605 --
2606 --
2607
2608 IF p_entity = 'DLVY' THEN
2609 IF p_entity_ids.count > 0 THEN
2610 calculate_del_tpdates(
2611 l_del_ids => p_entity_ids,
2612 l_refresh_lpn_flag => 'Y',
2613 x_return_status => x_return_status);
2614
2615 IF x_return_status in (WSH_UTIL_CORE.G_RET_STS_ERROR,WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
2616 return;
2617 END IF;
2618 END IF;
2619 ELSIF p_entity = 'LPN' THEN
2620 IF p_entity_ids.count > 0 THEN
2621 refresh_lpn_hierarchy_dates(l_lpndetail_ids => p_entity_ids,
2622 x_upd_del_tab => l_del_ids,
2623 x_return_status => x_return_status);
2624
2625 IF ( l_del_ids.COUNT > 0 ) THEN
2626 calculate_del_tpdates( l_del_ids => l_del_ids,
2627 l_refresh_lpn_flag => 'N',
2628 x_return_status => x_return_status );
2629 IF x_return_status in (WSH_UTIL_CORE.G_RET_STS_ERROR,WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
2630 raise OTHERS;
2631 END IF;
2632 END IF;
2633
2634 END IF;
2635 ELSIF p_entity ='DLVB' THEN
2636 FOR i IN 1..p_entity_ids.count LOOP
2637 l_entity_id := p_entity_ids(i);
2638 OPEN c_MasterLPN_del_id(l_entity_id);
2639 FETCH c_MasterLPN_del_id INTO l_mas_detail_id,l_delivery_id,l_container_flag;
2640 IF c_MasterLPN_del_id%FOUND THEN
2641 IF l_delivery_id IS NOT NULL THEN
2642 dlvry_ids(l_delivery_id) := l_delivery_id;
2643 ELSIF l_container_flag = 'Y' THEN
2644 detail_ids(l_entity_id) := l_entity_id;
2645 ELSIF l_mas_detail_id IS NOT NULL THEN
2646 detail_ids(l_mas_detail_id) := l_mas_detail_id;
2647 END IF;
2648 END IF;
2649 CLOSE c_MasterLPN_del_id;
2650 END LOOP;
2651 del_index := 1;
2652 detail_index :=1;
2653
2654 j := detail_ids.FIRST;
2655 IF j IS NOT NULL THEN
2656 WHILE j iS NOT NULL LOOP
2657 l_lpndetail_ids(detail_index) := detail_ids(j);
2658 detail_index := detail_index + 1;
2659 j := detail_ids.NEXT(j);
2660 END LOOP;
2661 refresh_lpn_hierarchy_dates(l_lpndetail_ids => l_lpndetail_ids,
2662 x_upd_del_tab => l_dummy_ids,
2663 x_return_status => x_return_status);
2664 IF x_return_status in (WSH_UTIL_CORE.G_RET_STS_ERROR,WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
2665 return;
2666 END IF;
2667 END IF;
2668
2669 j := dlvry_ids.FIRST;
2670 IF j IS NOT NULL THEN
2671 WHILE j iS NOT NULL LOOP
2672 l_del_ids(del_index) := dlvry_ids(j);
2673 del_index := del_index + 1;
2674 j := dlvry_ids.NEXT(j);
2675 END LOOP;
2676 calculate_del_tpdates( l_del_ids => l_del_ids,
2677 l_refresh_lpn_flag => 'Y',
2678 x_return_status => x_return_status );
2679
2680 IF x_return_status in (WSH_UTIL_CORE.G_RET_STS_ERROR,WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
2681 return;
2682 END IF;
2683 END IF;
2684
2685
2686 END IF;
2687
2688 --
2689 --
2690 IF l_debug_on THEN
2691 WSH_DEBUG_SV.log(l_module_name, 'x_return_status', x_return_status);
2692 WSH_DEBUG_SV.pop(l_module_name);
2693 END IF;
2694
2695 EXCEPTION
2696 WHEN OTHERS THEN
2697 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
2698 WSH_UTIL_CORE.DEFAULT_HANDLER(
2699 'WSH_TP_RELEASE.calculate_cont_del_tpdates',
2700 l_module_name);
2701
2702 IF l_debug_on THEN
2703 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2704 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
2705 END IF;
2706 END calculate_cont_del_tpdates;
2707
2708 /**
2709 * calculate_del_tpdates calculates TPdates for the Deliveries.
2710 * l_del_ids is the list of deliveries for which tpdates have to be calculated or recalculated
2711 */
2712 PROCEDURE calculate_del_tpdates(l_del_ids IN wsh_util_core.id_tab_type,
2713 l_refresh_lpn_flag IN VARCHAR2,
2714 x_return_status OUT NOCOPY VARCHAR2
2715 ) IS
2716
2717 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'calculate_del_tpdates';
2718
2719 --
2720 l_debug_on BOOLEAN;
2721 --
2722 l_initial_mpickup_date DATE;
2723 l_ultimate_mdropoff_date DATE;
2724 l_earliest_mpickup_date DATE;
2725 l_earliest_mdropoff_date DATE;
2726 l_latest_mpickup_date DATE;
2727 l_latest_mdropoff_date DATE;
2728 l_max_schedule_date DATE;
2729 l_min_schedule_date DATE;
2730 l_min_request_date DATE;
2731 l_max_request_date DATE;
2732 l_min_detSch_date DATE;
2733 l_min_detReq_date DATE;
2734 l_max_detSch_date DATE;
2735 l_delivery_id NUMBER;
2736 l_masdet_id wsh_util_core.id_tab_type;
2737 l_dummy_ids wsh_util_core.id_tab_type;
2738 l_del_date_calc_method VARCHAR(1);
2739 l_shp_dir VARCHAR2(5);
2740 others EXCEPTION;
2741
2742 -- BugFix 3570954 - Start
2743 CURSOR get_delivery_for_lock (p_delivery_id IN NUMBER) IS
2744 SELECT earliest_pickup_date,
2745 earliest_dropoff_date,
2746 latest_pickup_date,
2747 latest_dropoff_date,
2748 initial_pickup_date,
2749 ultimate_dropoff_date,
2750 ignore_for_planning --OTM R12
2751 FROM WSH_NEW_DELIVERIES
2752 WHERE delivery_id = p_delivery_id
2753 FOR UPDATE NOWAIT;
2754
2755 l_delivery_rec get_delivery_for_lock%rowtype;
2756 lock_detected EXCEPTION;
2757 PRAGMA EXCEPTION_INIT( lock_detected, -00054);
2758 -- BugFix 3570954 - End
2759
2760
2761 CURSOR get_LPNS(p_delivery_id IN NUMBER) IS
2762 SELECT wdd.delivery_detail_id,wda.delivery_id
2763 FROM wsh_delivery_details wdd,wsh_delivery_assignments_v wda
2764 WHERE wda.delivery_detail_id = wdd.delivery_detail_id
2765 AND wdd.container_flag = 'Y'
2766 -- AND wda.parent_delivery_detail_id IS NULL
2767 AND wda.delivery_id = p_delivery_id;
2768
2769
2770 CURSOR max_min_det_deltp_dates(p_delivery_id IN NUMBER) IS
2771 SELECT max(wdd.earliest_pickup_date),max(wdd.earliest_dropoff_date),
2772 min(wdd.latest_pickup_date),min(wdd.latest_dropoff_date),
2773 min(wdd.date_scheduled), min(wdd.date_requested), max(wdd.date_scheduled)
2774 FROM wsh_delivery_details wdd,wsh_delivery_assignments_v wda
2775 WHERE wda.delivery_detail_id = wdd.delivery_detail_id
2776 AND wda.parent_delivery_detail_id IS NULL
2777 AND wda.delivery_id = p_delivery_id;
2778
2779
2780 CURSOR max_min_om_deltp_dates(p_delivery_id IN NUMBER) IS
2781 SELECT max(date_scheduled) , min(date_scheduled),min(date_requested),max(date_requested)
2782 FROM wsh_delivery_details wdd,wsh_delivery_assignments_v wda
2783 WHERE wda.delivery_detail_id = wdd.delivery_detail_id
2784 AND delivery_id =p_delivery_id;
2785
2786 CURSOR is_inbound_dropshp(p_delivery_id IN NUMBER) IS
2787 SELECT shipment_direction
2788 FROM wsh_new_deliveries
2789 WHERE delivery_id =p_delivery_id
2790 AND shipment_direction IN ('I','D');
2791
2792 l_global_param_rec_type WSH_SHIPPING_PARAMS_PVT.Global_Parameters_Rec_Typ;
2793
2794 --OTM R12, glog proj
2795 l_delivery_info_tab WSH_NEW_DELIVERIES_PVT.Delivery_Attr_Tbl_Type;
2796 l_delivery_info WSH_NEW_DELIVERIES_PVT.Delivery_Rec_Type;
2797 l_new_interface_flag_tab WSH_UTIL_CORE.COLUMN_TAB_TYPE;
2798 l_tms_update VARCHAR2(1);
2799 l_trip_not_found VARCHAR2(1);
2800 l_trip_info_rec WSH_DELIVERY_VALIDATIONS.trip_info_rec_type;
2801 l_tms_version_number WSH_NEW_DELIVERIES.TMS_VERSION_NUMBER%TYPE;
2802 l_return_status VARCHAR2(1);
2803 l_gc3_is_installed VARCHAR2(1);
2804 --l_sysdate DATE;
2805 e_gc3_exception EXCEPTION;
2806 --
2807
2808
2809 BEGIN
2810 --
2811 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
2812 --
2813 IF l_debug_on IS NULL THEN
2814 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
2815 END IF;
2816 --
2817 IF l_debug_on THEN
2818 WSH_DEBUG_SV.push(l_module_name);
2819 END IF;
2820 --
2821 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2822 --
2823
2824 --
2825 --OTM R12, glog proj, use Global Variable
2826 l_gc3_is_installed := WSH_UTIL_CORE.G_GC3_IS_INSTALLED;
2827
2828 -- If null, call the function
2829 IF l_gc3_is_installed IS NULL THEN
2830 l_gc3_is_installed := WSH_UTIL_CORE.GC3_IS_INSTALLED;
2831 END IF;
2832 -- Initialize these as they are used in UPDATE statement and in cases where
2833 -- these are not actually populated
2834 l_new_interface_flag_tab(1) := WSH_NEW_DELIVERIES_PVT.C_TMS_NOT_TO_BE_SENT;
2835 -- end of OTM R12, glog proj
2836
2837 IF l_debug_on THEN
2838 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_SHIPPING_PARAMS_PVT. Get_Global_Parameters',WSH_DEBUG_SV.C_PROC_LEVEL);
2839 END IF;
2840 --
2841 WSH_SHIPPING_PARAMS_PVT.Get_Global_Parameters(l_global_param_rec_type,
2842 x_return_status);
2843 --
2844 IF l_debug_on THEN
2845 WSH_DEBUG_SV.log(l_module_name, 'After calling Get_Global_Parameters',
2846 x_return_status );
2847 END IF;
2848 --
2849 IF (x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
2850 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2851 END IF;
2852 --
2853 l_del_date_calc_method := l_global_param_rec_type.DEL_DATE_CALC_METHOD;
2854 --
2855 IF l_del_date_calc_method IS NULL THEN
2856 l_del_date_calc_method := 'S';
2857 END IF;
2858 --
2859 IF l_debug_on THEN
2860 WSH_DEBUG_SV.logmsg(l_module_name, 'l_del_date_calc_method: ' || l_del_date_calc_method);
2861 END IF;
2862 --
2863 FOR i IN 1..l_del_ids.count LOOP
2864 --{
2865 l_delivery_id := l_del_ids(i);
2866 IF ( l_refresh_lpn_flag = 'Y' ) THEN
2867
2868 --
2869 FOR master_lpns_rec IN get_LPNS(l_delivery_id) LOOP
2870 --{
2871 l_masdet_id(1) :=master_lpns_rec.delivery_detail_id;
2872
2873 refresh_lpn_hierarchy_dates(l_lpndetail_ids => l_masdet_id, x_upd_del_tab => l_dummy_ids, x_return_status => x_return_status);
2874
2875 --
2876 IF x_return_status in (WSH_UTIL_CORE.G_RET_STS_ERROR,WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
2877 raise others;
2878 END IF;
2879 --}
2880 END LOOP;
2881 --
2882 END IF;
2883
2884 OPEN max_min_det_deltp_dates(l_delivery_id);
2885 FETCH max_min_det_deltp_dates INTO
2886 l_earliest_mpickup_date, l_earliest_mdropoff_date,
2887 l_latest_mpickup_date,l_latest_mdropoff_date,
2888 l_min_detSch_date, l_min_detReq_date, l_max_detSch_date;
2889 --
2890 IF max_min_det_deltp_dates%NOTFOUND THEN
2891 l_earliest_mpickup_date := NULL;
2892 l_earliest_mdropoff_date := NULL;
2893 l_latest_mpickup_date := NULL;
2894 l_latest_mdropoff_date := NULL;
2895 l_min_detReq_date := NULL;
2896 l_min_detSch_date := NULL;
2897 END IF;
2898 --
2899 IF l_debug_on THEN
2900 wsh_debug_sv.log(l_module_name, 'l_earliest_mpickup_date',
2901 l_earliest_mpickup_date);
2902 wsh_debug_Sv.log(l_module_name, 'l_earliest_mdropoff_date',
2903 l_earliest_mdropoff_date);
2904 wsh_debug_sv.log(l_module_name, 'l_latest_mpickup_date',
2905 l_latest_mpickup_date);
2906 wsh_debug_sv.log(l_module_name, 'l_latest_mdropoff_date',
2907 l_latest_mdropoff_date);
2908 wsh_debug_sv.log(l_module_name, 'l_min_detSch_date',
2909 l_min_detSch_date);
2910 wsh_debug_sv.log(l_module_name, 'l_min_detReq_date',
2911 l_min_detReq_date);
2912 wsh_debug_sv.log(l_module_name, 'l_max_detSch_date',
2913 l_max_detSch_date);
2914 END IF;
2915 --
2916 IF (l_latest_mpickup_date IS NOT NULL AND
2917 l_earliest_mpickup_date IS NOT NULL) AND
2918 (l_latest_mpickup_date < l_earliest_mpickup_date)
2919 THEN
2920 --
2921 --exception will be logged per Jeff's email on 08/05/2004
2922 --if the user does not want it, they can change the exception to information only
2923 --and avoid the warnings
2924 log_tpdate_exception('DLVY',l_delivery_id,TRUE,l_earliest_mpickup_date,l_latest_mpickup_date);
2925 l_latest_mpickup_date := l_earliest_mpickup_date;
2926 --
2927 END IF;
2928 --
2929 IF (l_latest_mdropoff_date IS NOT NULL AND
2930 l_earliest_mdropoff_date IS NOT NULL) AND
2931 (l_latest_mdropoff_date < l_earliest_mdropoff_date)
2932 THEN
2933 --
2934 log_tpdate_exception('DLVY',l_delivery_id,FALSE,l_earliest_mdropoff_date,l_latest_mdropoff_date);
2935 l_latest_mdropoff_date := l_earliest_mdropoff_date;
2936 --
2937 END IF;
2938 --
2939 OPEN is_inbound_dropshp(l_delivery_id);
2940 FETCH is_inbound_dropshp INTO l_shp_dir;
2941 IF is_inbound_dropshp%NOTFOUND THEN
2942 l_shp_dir := NULL;
2943 END IF;
2944 CLOSE is_inbound_dropshp;
2945
2946 IF l_del_date_calc_method = 'S' AND l_shp_dir IS NULL THEN
2947 --{
2948
2949 --
2950 OPEN max_min_om_deltp_dates(l_delivery_id);
2951 FETCH max_min_om_deltp_dates INTO
2952 l_max_schedule_date, l_min_schedule_date,
2953 l_min_request_date, l_max_request_date;
2954 --
2955 IF l_debug_on THEN
2956 WSH_DEBUG_SV.log(l_module_name, 'l_max_schedule_date', l_max_schedule_date);
2957 WSH_DEBUG_SV.log(l_module_name, 'l_min_schedule_date', l_min_schedule_date);
2958 WSH_DEBUG_SV.log(l_module_name, 'l_min_request_date', l_min_request_date);
2959 WSH_DEBUG_SV.log(l_module_name, 'l_max_request_date', l_max_request_date);
2960 END IF;
2961 --
2962 IF max_min_om_deltp_dates%FOUND THEN
2963 --{
2964 --
2965 l_initial_mpickup_date := l_max_schedule_date;
2966 --
2967 IF (l_min_request_date IS NOT NULL AND
2968 l_min_schedule_date IS NOT NULL) THEN
2969 --{
2970 IF (l_min_request_date > l_min_schedule_date) THEN
2971 l_ultimate_mdropoff_date := l_min_request_date ;
2972 ELSE
2973 l_ultimate_mdropoff_date := l_min_schedule_date;
2974 END IF;
2975 --}
2976 END IF;
2977 --
2978 IF l_min_request_date IS NULL THEN
2979 l_ultimate_mdropoff_date := l_min_schedule_date;
2980 END IF;
2981 --
2982 IF l_min_schedule_date IS NULL THEN
2983 l_ultimate_mdropoff_date := l_min_request_date ;
2984 END IF;
2985 --}
2986 ELSE
2987 --
2988 l_initial_mpickup_date := NULL;
2989 l_ultimate_mdropoff_date := NULL;
2990 --
2991 END IF;
2992 --}
2993 ELSE /* Delivery Date calc method is E and line direction is inbound or drop ship*/
2994 --{
2995 l_initial_mpickup_date := l_earliest_mpickup_date;
2996 l_ultimate_mdropoff_date := l_latest_mdropoff_date;
2997 --
2998 -- Bug 3451919 - if the initial or ultimate dates are null, use the default option for calculation
2999 -- For IPD (if null), calculate using schedule date only for outbound
3000 IF l_shp_dir IS NULL AND (l_initial_mpickup_date IS NULL) THEN
3001 --{
3002 l_initial_mpickup_date := l_max_detSch_date;
3003 --
3004 --}
3005 END IF;
3006 IF (l_ultimate_mdropoff_date IS NULL) THEN
3007 --{
3008 l_ultimate_mdropoff_date := GREATEST(l_min_detReq_date,
3009 l_min_detSch_date);
3010 --
3011 --}
3012 END IF;
3013 --}
3014 END IF;
3015
3016 IF l_ultimate_mdropoff_date < l_initial_mpickup_date OR
3017 l_ultimate_mdropoff_date IS NULL THEN
3018 --
3019 l_ultimate_mdropoff_date := l_initial_mpickup_date;
3020 --
3021 END IF;
3022 --
3023 IF l_debug_on THEN
3024 WSH_DEBUG_SV.log(l_module_name, 'l_initial_mpickup_date', l_initial_mpickup_date );
3025 WSH_DEBUG_SV.log(l_module_name, 'l_ultimate_mdropoff_date', l_ultimate_mdropoff_date);
3026 WSH_DEBUG_SV.log(l_module_name, 'GC3_IS_INSTALLED', l_gc3_is_installed);
3027 END IF;
3028 --
3029
3030 OPEN get_delivery_for_lock(l_delivery_id); -- BugFix 3570954
3031 FETCH get_delivery_for_lock INTO l_delivery_rec; -- BugFix 3570954
3032
3033 --OTM R12, check for tms update
3034 IF l_debug_on THEN
3035 WSH_DEBUG_SV.log(l_module_name,'Ignore for Planning:',l_delivery_rec.ignore_for_planning);
3036 END IF;
3037 IF l_gc3_is_installed = 'Y' AND
3038 NVL(l_delivery_rec.ignore_for_planning, 'N') = 'N' THEN--{
3039
3040 l_tms_update := 'N';
3041 l_trip_not_found := 'N';
3042 l_tms_version_number := 1;
3043 --l_sysdate := TO_CHAR(SYSDATE,'DD-MON-RRRR HH24:MI:SS');
3044 l_delivery_info_tab.DELETE;
3045 --l_new_interface_flag_tab.DELETE;
3046
3047 --get trip information for delivery, no update when trip not OPEN
3048 WSH_DELIVERY_VALIDATIONS.get_trip_information
3049 (p_delivery_id => l_delivery_id,
3050 x_trip_info_rec => l_trip_info_rec,
3051 x_return_status => l_return_status);
3052
3053 IF l_debug_on THEN
3054 WSH_DEBUG_SV.log(l_module_name, 'Return Status after WSH_DELIVERY_VALIDATIONS.get_trip_information', l_return_status);
3055 END IF;
3056
3057 IF (l_return_status IN (WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR, WSH_UTIL_CORE.G_RET_STS_ERROR)) THEN
3058 x_return_status := l_return_status;
3059 IF l_debug_on THEN
3060 WSH_DEBUG_SV.logmsg(l_module_name,'Error in WSH_DELIVERY_VALIDATIONS.get_trip_information');
3061 END IF;
3062 RAISE e_gc3_exception;
3063 END IF;
3064
3065 IF (l_trip_info_rec.trip_id IS NULL) THEN
3066 l_trip_not_found := 'Y';
3067 END IF;
3068
3069 -- only do changes when there's no trip or trip status is OPEN
3070 -- Checking for Only status_code = OP or l_trip_not_found=Y will
3071 -- suffice, no need for NVL or l_trip_not_found=N
3072 IF ((l_trip_info_rec.status_code = 'OP') OR
3073 (l_trip_not_found = 'Y')) THEN--{
3074
3075 WSH_DELIVERY_VALIDATIONS.get_delivery_information(
3076 p_delivery_id => l_delivery_id,
3077 x_delivery_rec => l_delivery_info,
3078 x_return_status => l_return_status);
3079
3080 IF l_debug_on THEN
3081 WSH_DEBUG_SV.log(l_module_name, 'Return Status after WSH_DELIVERY_VALIDATIONS.get_delivery_information', l_return_status);
3082 END IF;
3083
3084 IF (l_return_status IN (WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR, WSH_UTIL_CORE.G_RET_STS_ERROR)) THEN
3085 x_return_status := l_return_status;
3086 IF l_debug_on THEN
3087 WSH_DEBUG_SV.logmsg(l_module_name,'Error in WSH_DELIVERY_VALIDATIONS.get_delivery_information');
3088 END IF;
3089 RAISE e_gc3_exception;
3090 END IF;
3091
3092 l_delivery_info_tab(1) := l_delivery_info;
3093
3094 --checking the value differences for the relevant critical fields
3095 --if any of the earliest/latest dates are changed and delivery is include for planning, then
3096 --update is needed
3097 IF (nvl(l_delivery_info.EARLIEST_PICKUP_DATE, fnd_api.g_miss_date) <> nvl(l_earliest_mpickup_date, fnd_api.g_miss_date) OR
3098 nvl(l_delivery_info.LATEST_PICKUP_DATE, fnd_api.g_miss_date) <> nvl(l_latest_mpickup_date, fnd_api.g_miss_date) OR
3099 nvl(l_delivery_info.EARLIEST_DROPOFF_DATE, fnd_api.g_miss_date) <> nvl(l_earliest_mdropoff_date, fnd_api.g_miss_date) OR
3100 nvl(l_delivery_info.LATEST_DROPOFF_DATE, fnd_api.g_miss_date) <> nvl(l_latest_mdropoff_date, fnd_api.g_miss_date)
3101 ) THEN--{
3102
3103 IF (NVL(l_delivery_info.tms_interface_flag, WSH_NEW_DELIVERIES_PVT.C_TMS_NOT_TO_BE_SENT) IN
3104 (WSH_NEW_DELIVERIES_PVT.C_TMS_NOT_TO_BE_SENT,
3105 WSH_NEW_DELIVERIES_PVT.C_TMS_CREATE_REQUIRED,
3106 WSH_NEW_DELIVERIES_PVT.C_TMS_DELETE_REQUIRED,
3107 WSH_NEW_DELIVERIES_PVT.C_TMS_DELETE_IN_PROCESS,
3108 WSH_NEW_DELIVERIES_PVT.C_TMS_UPDATE_REQUIRED)) THEN
3109 l_tms_update := 'N';
3110 ELSE
3111 l_tms_update := 'Y';
3112 l_new_interface_flag_tab(1) := WSH_NEW_DELIVERIES_PVT.C_TMS_UPDATE_REQUIRED;
3113 l_tms_version_number := NVL(l_delivery_info.tms_version_number, 1) + 1;
3114 END IF;
3115 ELSE
3116 l_tms_update := 'N';
3117 END IF;--}
3118 END IF; --}
3119 END IF; --}
3120
3121 IF l_debug_on THEN
3122 WSH_DEBUG_SV.log(l_module_name,'l_tms_update flag', l_tms_update);
3123 END IF;
3124 --END OTM R12
3125
3126 UPDATE WSH_NEW_DELIVERIES
3127 SET earliest_pickup_date = l_earliest_mpickup_date,
3128 earliest_dropoff_date = l_earliest_mdropoff_date,
3129 latest_pickup_date = l_latest_mpickup_date,
3130 latest_dropoff_date = l_latest_mdropoff_date,
3131 initial_pickup_date = l_initial_mpickup_date,
3132 ultimate_dropoff_date = l_ultimate_mdropoff_date,
3133 --OTM R12, glog proj
3134 TMS_INTERFACE_FLAG = DECODE(l_tms_update,
3135 'Y', l_new_interface_flag_tab(1),
3136 NVL(TMS_INTERFACE_FLAG, WSH_NEW_DELIVERIES_PVT.C_TMS_NOT_TO_BE_SENT)),
3137 TMS_VERSION_NUMBER = DECODE(l_tms_update,
3138 'Y', l_tms_version_number,
3139 NVL(tms_version_number, 1)),
3140 --
3141 last_update_date = sysdate,
3142 last_updated_by = FND_GLOBAL.USER_ID,
3143 last_update_login = FND_GLOBAL.LOGIN_ID
3144 WHERE delivery_id = l_delivery_id;
3145
3146 --OTM R12
3147 IF (l_gc3_is_installed = 'Y' AND l_tms_update = 'Y') THEN--{
3148
3149 WSH_XC_UTIL.log_otm_exception(
3150 p_delivery_info_tab => l_delivery_info_tab,
3151 p_new_interface_flag_tab => l_new_interface_flag_tab,
3152 x_return_status => l_return_status);
3153
3154 IF l_debug_on THEN
3155 WSH_DEBUG_SV.log(l_module_name,'l_return_status after log_otm_exception', l_return_status);
3156 END IF;
3157
3158 IF (l_return_status IN (WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR, WSH_UTIL_CORE.G_RET_STS_ERROR)) THEN
3159 x_return_status := l_return_status;
3160 IF l_debug_on THEN
3161 WSH_DEBUG_SV.logmsg(l_module_name,'Error in WSH_XC_UTIL.log_otm_exception');
3162 END IF;
3163 RAISE e_gc3_exception;
3164 END IF;
3165 END IF;--}
3166 --
3167
3168 IF get_delivery_for_lock%ISOPEN THEN
3169 CLOSE get_delivery_for_lock; -- BugFix 3570954
3170 END IF;
3171
3172 --
3173 IF max_min_det_deltp_dates%ISOPEN THEN
3174 CLOSE max_min_det_deltp_dates;
3175 END IF;
3176 --
3177 IF max_min_om_deltp_dates%ISOPEN THEN
3178 CLOSE max_min_om_deltp_dates;
3179 END IF;
3180 --}
3181 END LOOP;
3182 --
3183 IF l_debug_on THEN
3184 WSH_DEBUG_SV.log(l_module_name, 'x_return_status', x_return_status);
3185 WSH_DEBUG_SV.pop(l_module_name);
3186 END IF;
3187 --
3188 EXCEPTION
3189 WHEN e_gc3_exception THEN
3190 -- OTM R12, glog proj, Close the cursors which are OPEN
3191 IF get_delivery_for_lock%ISOPEN THEN
3192 CLOSE get_delivery_for_lock;
3193 END IF;
3194
3195 IF max_min_det_deltp_dates%ISOPEN THEN
3196 CLOSE max_min_det_deltp_dates;
3197 END IF;
3198
3199 IF max_min_om_deltp_dates%ISOPEN THEN
3200 CLOSE max_min_om_deltp_dates;
3201 END IF;
3202
3203 IF is_inbound_dropshp%ISOPEN THEN
3204 CLOSE is_inbound_dropshp;
3205 END IF;
3206
3207 -- The APIs which errored out would have set appropriate message
3208 x_return_status := WSH_UTIL_CORE.g_ret_sts_error;
3209
3210 IF l_debug_on THEN
3211 WSH_DEBUG_SV.logmsg(l_module_name,'Error Calculating TP Dates');
3212 WSH_DEBUG_SV.pop(l_module_name);
3213 END IF;
3214
3215 WHEN lock_detected THEN -- BugFix 3570954
3216
3217 -- OTM R12, glog proj, Close the cursors which are OPEN
3218 IF get_delivery_for_lock%ISOPEN THEN
3219 CLOSE get_delivery_for_lock;
3220 END IF;
3221
3222 IF max_min_det_deltp_dates%ISOPEN THEN
3223 CLOSE max_min_det_deltp_dates;
3224 END IF;
3225
3226 IF max_min_om_deltp_dates%ISOPEN THEN
3227 CLOSE max_min_om_deltp_dates;
3228 END IF;
3229
3230 IF is_inbound_dropshp%ISOPEN THEN
3231 CLOSE is_inbound_dropshp;
3232 END IF;
3233
3234 FND_MESSAGE.SET_NAME('WSH','WSH_NO_LOCK');
3235 x_return_status := WSH_UTIL_CORE.g_ret_sts_error;
3236 wsh_util_core.add_message(WSH_UTIL_CORE.g_ret_sts_error,l_module_name);
3237
3238 IF l_debug_on THEN
3239 WSH_DEBUG_SV.log(l_module_name,'Cannot lock delivery for update',l_delivery_id);
3240 WSH_DEBUG_SV.pop(l_module_name);
3241 END IF;
3242
3243 WHEN OTHERS THEN
3244 -- OTM R12, glog proj, Close the cursors which are OPEN
3245 IF get_delivery_for_lock%ISOPEN THEN
3246 CLOSE get_delivery_for_lock;
3247 END IF;
3248
3249 IF max_min_det_deltp_dates%ISOPEN THEN
3250 CLOSE max_min_det_deltp_dates;
3251 END IF;
3252
3253 IF max_min_om_deltp_dates%ISOPEN THEN
3254 CLOSE max_min_om_deltp_dates;
3255 END IF;
3256
3257 IF is_inbound_dropshp%ISOPEN THEN
3258 CLOSE is_inbound_dropshp;
3259 END IF;
3260
3261 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
3262 WSH_UTIL_CORE.DEFAULT_HANDLER(
3263 'WSH_TP_RELEASE.calculate_del_tpdates',
3264 l_module_name);
3265
3266 IF l_debug_on THEN
3267 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
3268 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
3269 END IF;
3270
3271 END calculate_del_tpdates;
3272
3273 /**
3274 * refresh_lpn_hierarchy_dates calculates TPdates for the Containers.
3275 * l_lpndetail_ids is the list of containers or items of containers for the which container tpdates
3276 * have to be calculated or recalculated
3277 * The LPN dates ripple upwards, So all the outer contaniers tpdates are recalculated.
3278 */
3279 PROCEDURE refresh_lpn_hierarchy_dates(l_lpndetail_ids IN wsh_util_core.id_tab_type,
3280 x_upd_del_tab OUT NOCOPY wsh_util_core.id_tab_type,
3281 x_return_status OUT NOCOPY VARCHAR2) IS
3282
3283 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'refresh_lpn_hierarchy_dates';
3284 --
3285 l_debug_on BOOLEAN;
3286 --
3287 l_detail_tab WSH_UTIL_CORE.id_tab_type;
3288 l_container_flag VARCHAR(2);
3289 --Added for bug 5234326
3290 l_updated_flag VARCHAR2(1);
3291 l_delivery_id NUMBER;
3292
3293
3294 CURSOR Outer_LPNS(p_delivery_detail_id IN NUMBER) IS
3295 SELECT parent_delivery_detail_id
3296 FROM wsh_delivery_assignments_v
3297 START WITH delivery_detail_id = p_delivery_detail_id
3298 CONNECT BY PRIOR parent_delivery_detail_id = delivery_detail_id;
3299
3300 CURSOR isContainer(p_delivery_detail_id IN NUMBER) IS
3301 SELECT Container_flag
3302 FROM wsh_delivery_details
3303 WHERE delivery_detail_id = p_delivery_detail_id;
3304
3305 OTHERS EXCEPTION;
3306 BEGIN
3307 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
3308 --
3309 IF l_debug_on IS NULL THEN
3310 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
3311 END IF;
3312
3313 IF l_debug_on THEN
3314 WSH_DEBUG_SV.push(l_module_name);
3315 END IF;
3316
3317 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
3318 --
3319 --
3320
3321 FOR i IN 1..l_lpndetail_ids.count LOOP
3322 -- The Input Deilvery_Detail_Id
3323 IF l_debug_on THEN
3324 WSH_DEBUG_SV.log(l_module_name, 'Input Container', l_lpndetail_ids(i));
3325 END IF;
3326 calculate_lpn_tpdates(p_delivery_detail_id=> l_lpndetail_ids(i),
3327 x_updated_flag=> l_updated_flag,
3328 x_delivery_id => l_delivery_id,
3329 x_return_status=> x_return_status);
3330 IF x_return_status in (WSH_UTIL_CORE.G_RET_STS_ERROR,WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
3331 raise OTHERS;
3332 END IF;
3333
3334 --Added for bug 5234326
3335 IF ( l_updated_flag = 'Y' ) THEN
3336
3337 -- Outer Containers
3338 OPEN Outer_LPNS(l_lpndetail_ids(i));
3339 FETCH Outer_LPNS BULK COLLECT INTO l_detail_tab;
3340 CLOSE Outer_LPNS;
3341 IF l_detail_tab.count > 0 THEN
3342 FOR j IN l_detail_tab.FIRST..l_detail_tab.LAST LOOP
3343 IF l_detail_tab(j) IS NOT NULL THEN
3344 -- Removed Container Flag check for bug 5234326
3345 IF l_debug_on THEN
3346 WSH_DEBUG_SV.log(l_module_name, 'Outer', l_detail_tab(j));
3347 END IF;
3348 calculate_lpn_tpdates(p_delivery_detail_id=> l_detail_tab(j),
3349 x_delivery_id => l_delivery_id,
3350 x_updated_flag=> l_updated_flag,
3351 x_return_status=> x_return_status);
3352 IF x_return_status in (WSH_UTIL_CORE.G_RET_STS_ERROR,WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
3353 raise OTHERS;
3354 END IF;
3355 IF (l_updated_flag = 'N') THEN
3356 EXIT;
3357 END IF;
3358 END IF;
3359 END LOOP;
3360 -- Outer Containers
3361 END IF;
3362 END IF;
3363
3364 IF (l_delivery_id is not null) THEN
3365 x_upd_del_tab(x_upd_del_tab.COUNT+1) := l_delivery_id;
3366 END IF;
3367
3368 END LOOP;
3369 --
3370 --
3371 IF l_debug_on THEN
3372 WSH_DEBUG_SV.log(l_module_name, 'x_return_status', x_return_status);
3373 WSH_DEBUG_SV.pop(l_module_name);
3374 END IF;
3375
3376 EXCEPTION
3377 WHEN OTHERS THEN
3378 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
3379 WSH_UTIL_CORE.DEFAULT_HANDLER(
3380 'WSH_TP_RELEASE.refresh_lpn_hierarchy_dates',
3381 l_module_name);
3382
3383 IF l_debug_on THEN
3384 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
3385 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
3386 END IF;
3387
3388 END refresh_lpn_hierarchy_dates;
3389
3390 /**
3391 * calculate_lpn_tpdates calculates TPdates for the Container.
3392 * p_delivery_detail_id is the continer for which tpdates ids calculated
3393 */
3394 PROCEDURE calculate_lpn_tpdates(p_delivery_detail_id NUMBER,
3395 x_updated_flag OUT NOCOPY VARCHAR2,
3396 x_delivery_id OUT NOCOPY NUMBER,
3397 x_return_status OUT NOCOPY VARCHAR2) IS
3398
3399 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'calculate_lpn_tpdates';
3400 --
3401 l_debug_on BOOLEAN;
3402 --
3403
3404 l_earliest_mpickup_date DATE;
3405 l_earliest_mdropoff_date DATE;
3406 l_latest_mpickup_date DATE;
3407 l_latest_mdropoff_date DATE;
3408 l_delivery_detail_id NUMBER;
3409
3410
3411
3412 CURSOR max_min_tp_dates(c_delivery_detail_id IN NUMBER) IS
3413 SELECT max(earliest_pickup_date),max(earliest_dropoff_date),min(latest_pickup_date),min(latest_dropoff_date)
3414 FROM wsh_delivery_assignments_v wda,wsh_delivery_details wdd
3415 WHERE wda.delivery_detail_id = wdd.delivery_detail_id
3416 AND parent_delivery_detail_id = c_delivery_detail_id;
3417
3418 -- K LPN CONV. rv
3419 l_wms_org VARCHAR2(10) := 'N';
3420 l_sync_tmp_rec wsh_glbl_var_strct_grp.sync_tmp_rec_type;
3421 l_line_direction VARCHAR2(10);
3422 l_organization_id NUMBER;
3423 l_cnt_flag VARCHAR2(10);
3424 l_return_status VARCHAR2(10);
3425 -- K LPN CONV. rv
3426
3427
3428
3429 BEGIN
3430 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
3431 --
3432 IF l_debug_on IS NULL THEN
3433 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
3434 END IF;
3435
3436 IF l_debug_on THEN
3437 WSH_DEBUG_SV.push(l_module_name);
3438 END IF;
3439
3440 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
3441 --
3442 --
3443 OPEN max_min_tp_dates(p_delivery_detail_id);
3444 FETCH max_min_tp_dates INTO l_earliest_mpickup_date,l_earliest_mdropoff_date,l_latest_mpickup_date,l_latest_mdropoff_date;
3445 IF max_min_tp_dates%FOUND THEN
3446 IF(l_latest_mpickup_date IS NOT NULL AND l_earliest_mpickup_date IS NOT NULL) AND
3447 (l_latest_mpickup_date < l_earliest_mpickup_date)
3448 THEN
3449 log_tpdate_exception('LPN',p_delivery_detail_id,TRUE,l_earliest_mpickup_date,l_latest_mpickup_date);
3450 l_latest_mpickup_date := l_earliest_mpickup_date;
3451 END IF;
3452 IF (l_latest_mdropoff_date IS NOT NULL AND l_earliest_mdropoff_date IS NOT NULL) AND
3453 (l_latest_mdropoff_date < l_earliest_mdropoff_date)
3454 THEN
3455 log_tpdate_exception('LPN',p_delivery_detail_id,FALSE,l_earliest_mdropoff_date,l_latest_mdropoff_date);
3456 l_latest_mdropoff_date := l_earliest_mdropoff_date;
3457 END IF;
3458 ELSE
3459 l_earliest_mpickup_date := NULL;
3460 l_earliest_mdropoff_date := NULL;
3461 l_latest_mpickup_date := NULL;
3462 l_latest_mdropoff_date := NULL;
3463 END IF;
3464
3465 -- Bug 5234326: Update TP dates in WDD only if there is a change
3466 UPDATE WSH_DELIVERY_DETAILS
3467 SET earliest_pickup_date = l_earliest_mpickup_date,
3468 earliest_dropoff_date = l_earliest_mdropoff_date,
3469 latest_pickup_date = l_latest_mpickup_date,
3470 latest_dropoff_date = l_latest_mdropoff_date,
3471 last_update_date = sysdate,
3472 last_updated_by = FND_GLOBAL.USER_ID
3473 WHERE delivery_detail_id = p_delivery_detail_id
3474 AND ( nvl(earliest_pickup_date, sysdate) <> nvl(l_earliest_mpickup_date, sysdate)
3475 OR nvl(earliest_dropoff_date, sysdate) <> nvl(earliest_dropoff_date, sysdate)
3476 OR nvl(latest_pickup_date, sysdate) <> nvl(latest_pickup_date, sysdate)
3477 OR nvl(latest_dropoff_date, sysdate) <> nvl(latest_dropoff_date, sysdate) );
3478 IF ( SQL%ROWCOUNT > 0 ) THEN
3479 x_updated_flag := 'Y';
3480 ELSE
3481 x_updated_flag := 'N';
3482 x_delivery_id := NULL;
3483 END IF;
3484 IF max_min_tp_dates%ISOPEN THEN
3485 CLOSE max_min_tp_dates;
3486 END IF;
3487
3488
3489 -- LPN CONV. rv
3490 IF l_debug_on THEN
3491 WSH_DEBUG_SV.log(l_module_name, 'l_cnt_flag', l_cnt_flag);
3492 WSH_DEBUG_SV.log(l_module_name, 'l_organization_id', l_organization_id);
3493 WSH_DEBUG_SV.log(l_module_name, 'l_line_direction', l_line_direction);
3494 END IF;
3495 --
3496 IF (l_cnt_flag = 'Y' and nvl(l_line_direction,'O') in ('O', 'IO'))
3497 AND WSH_WMS_LPN_GRP.G_CALLBACK_REQUIRED = 'Y'
3498 THEN
3499 --{
3500 l_wms_org := wsh_util_validate.check_wms_org(l_organization_id);
3501 IF (WSH_WMS_LPN_GRP.GK_WMS_UPD_DATE and l_wms_org = 'Y')
3502 OR
3503 (WSH_WMS_LPN_GRP.GK_INV_UPD_DATE and l_wms_org = 'N')
3504 THEN
3505 --{
3506 --
3507 l_sync_tmp_rec.delivery_detail_id := p_delivery_detail_id;
3508 l_sync_tmp_rec.operation_type := 'UPDATE';
3509 --
3510 --
3511 -- Debug Statements
3512 --
3513 IF l_debug_on THEN
3514 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_WMS_SYNC_TMP_PKG.MERGE',WSH_DEBUG_SV.C_PROC_LEVEL);
3515 END IF;
3516 --
3517 WSH_WMS_SYNC_TMP_PKG.MERGE
3518 (
3519 p_sync_tmp_rec => l_sync_tmp_rec,
3520 x_return_status => x_return_status
3521 );
3522
3523 IF l_debug_on THEN
3524 WSH_DEBUG_SV.log(l_module_name, 'x_return_status', x_return_status);
3525 END IF;
3526 --
3527 IF (x_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)) THEN
3528 IF l_debug_on THEN
3529 WSH_DEBUG_SV.logmsg(l_module_name,'Error occured in WSH_WMS_SYNC_TMP_PKG.MERGE');
3530 WSH_DEBUG_SV.pop(l_module_name);
3531 END IF;
3532 return;
3533 END IF;
3534 --
3535 --}
3536 END IF;
3537 --}
3538 END IF;
3539 --
3540 -- LPN CONV. rv
3541 --
3542 IF l_debug_on THEN
3543 WSH_DEBUG_SV.log(l_module_name, 'x_return_status', x_return_status);
3544 WSH_DEBUG_SV.pop(l_module_name);
3545 END IF;
3546
3547 EXCEPTION
3548 WHEN OTHERS THEN
3549 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
3550 WSH_UTIL_CORE.DEFAULT_HANDLER(
3551 'WSH_TP_RELEASE.calculate_lpn_tpdates(delivery_id)',
3552 l_module_name);
3553
3554 IF l_debug_on THEN
3555 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
3556 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
3557 END IF;
3558
3559 END calculate_lpn_tpdates;
3560
3561 PROCEDURE log_tpdate_exception(p_entity VARCHAR2,
3562 p_entity_id NUMBER,
3563 p_pick_up BOOLEAN,
3564 early_date DATE,
3565 latest_date DATE
3566 ) IS
3567
3568 CURSOR c_det_location_id(p_delivery_detail_id IN NUMBER) IS
3569 SELECT ship_from_location_id
3570 FROM wsh_delivery_details wdd
3571 WHERE delivery_detail_id = p_delivery_detail_id;
3572
3573 CURSOR c_del_location_id(p_delivery_id IN NUMBER) IS
3574 SELECT wdd.ship_from_location_id
3575 FROM wsh_delivery_details wdd ,wsh_delivery_assignments_v wda
3576 WHERE wdd.delivery_detail_id = wda.delivery_detail_id
3577 AND delivery_id = p_delivery_id
3578 AND ROWNUM = 1;
3579
3580
3581 l_api_version NUMBER := 1.0;
3582 l_return_status VARCHAR2(1);
3583 l_msg_count NUMBER;
3584 l_msg_data VARCHAR2(6000);
3585 l_exception_id NUMBER;
3586
3587 l_validation_level NUMBER default FND_API.G_VALID_LEVEL_FULL;
3588
3589
3590 l_exception_message1 varchar2(2000);
3591 l_exception_message2 varchar2(2000);
3592 l_exception_message varchar2(2000);
3593 l_exception_name varchar2(30);
3594
3595 l_location_id NUMBER;
3596 l_logging_entity VARCHAR2(50);
3597
3598 BEGIN
3599
3600
3601 l_exception_name := 'WSH_INVALID_TPDATES';
3602 l_logging_entity := 'SHIPPER';
3603
3604 IF p_pick_up THEN
3605 FND_MESSAGE.SET_NAME('WSH','WSH_INVALID_TPDATES_SHIP');
3606 FND_MESSAGE.SET_TOKEN('EARLIEST', FND_DATE.DATE_TO_CANONICAL(early_date));
3607 FND_MESSAGE.SET_TOKEN('LATEST', FND_DATE.DATE_TO_CANONICAL(latest_date));
3608 l_exception_message := FND_MESSAGE.GET;
3609 ELSE
3610 FND_MESSAGE.SET_NAME('WSH','WSH_INVALID_TPDATES_DELIVERY');
3611 FND_MESSAGE.SET_TOKEN('EARLIEST', FND_DATE.DATE_TO_CANONICAL(early_date));
3612 FND_MESSAGE.SET_TOKEN('LATEST', FND_DATE.DATE_TO_CANONICAL(latest_date));
3613 l_exception_message := FND_MESSAGE.GET;
3614 END IF;
3615
3616 IF p_entity = 'DLVY' THEN
3617
3618 OPEN c_del_location_id(p_entity_id);
3619 FETCH c_del_location_id INTO l_location_id;
3620 CLOSE c_del_location_id;
3621 IF l_location_id IS NOT NULL THEN
3622 WSH_XC_UTIL.log_exception(
3623 p_api_version => l_api_version,
3624 p_init_msg_list => FND_API.G_FALSE,
3625 p_commit => FND_API.G_FALSE,
3626 p_validation_level => l_validation_level,
3627 x_return_status => l_return_status,
3628 x_msg_count => l_msg_count,
3629 x_msg_data => l_msg_data,
3630 x_exception_id => l_exception_id,
3631 p_exception_location_id => l_location_id,
3632 p_logged_at_location_id => l_location_id,
3633 p_logging_entity => l_logging_entity,
3634 p_logging_entity_id => FND_GLOBAL.USER_ID,
3635 p_exception_name => l_exception_name,
3636 p_message => l_exception_message,
3637 p_delivery_id => p_entity_id,
3638 p_delivery_name => to_char(p_entity_id)
3639 );
3640 END IF;
3641 ELSIF p_entity IN ('LPN','LINE') THEN
3642 OPEN c_det_location_id(p_entity_id);
3643 FETCH c_det_location_id INTO l_location_id;
3644 CLOSE c_det_location_id;
3645 IF l_location_id IS NOT NULL THEN
3646 WSH_XC_UTIL.log_exception(
3647 p_api_version => l_api_version,
3648 p_init_msg_list => FND_API.G_FALSE,
3649 p_commit => FND_API.G_FALSE,
3650 p_validation_level => l_validation_level,
3651 x_return_status => l_return_status,
3652 x_msg_count => l_msg_count,
3653 x_msg_data => l_msg_data,
3654 x_exception_id => l_exception_id,
3655 p_exception_location_id => l_location_id,
3656 p_logged_at_location_id => l_location_id,
3657 p_logging_entity => l_logging_entity,
3658 p_logging_entity_id => FND_GLOBAL.USER_ID,
3659 p_exception_name => l_exception_name,
3660 p_message => l_exception_message,
3661 p_delivery_detail_id => p_entity_id
3662 );
3663 END IF;
3664 END IF;
3665
3666
3667 END log_tpdate_exception;
3668
3669 /**
3670 * Check_Shipset_Ignoreflag Checks if the p_delivery_detail_id ignore_for_planning
3671 * is different from other lines ignore_for_palnning which are in same ship set.
3672 * If so exception is logged if p_logexcep is True otherwise warinig message is thrown.
3673 */
3674 PROCEDURE Check_Shipset_Ignoreflag( p_delivery_detail_ids wsh_util_core.id_tab_type,
3675 p_ignore_for_planning VARCHAR2,
3676 p_logexcep boolean,
3677 x_return_status OUT NOCOPY VARCHAR2) IS
3678
3679 CURSOR c_check_ignore_for_planning(c_delivery_detail_id NUMBER) IS
3680 SELECT wdd.ignore_for_planning,a.ship_from_location_id
3681 FROM wsh_delivery_details wdd ,
3682 (SELECT source_code,source_header_id,ship_set_id,ship_from_location_id
3683 FROM wsh_delivery_details
3684 WHERE delivery_detail_id = c_delivery_detail_id) a
3685 WHERE
3686 wdd.delivery_detail_id <> c_delivery_detail_id AND
3687 nvl(wdd.ignore_for_planning,'N') <> p_ignore_for_planning AND
3688 wdd.ship_set_id = a.ship_set_id AND
3689 wdd.source_header_id = a.source_header_id AND
3690 wdd.source_code= a.source_code AND
3691 rownum <= 1;
3692
3693
3694 l_api_version NUMBER := 1.0;
3695 l_src_hdr_id NUMBER;
3696 l_ship_set_id NUMBER;
3697 l_src_code VARCHAR(5);
3698 l_delivery_detail_id NUMBER;
3699 l_exception_message varchar2(2000);
3700 l_exception_name varchar2(30);
3701 l_exception_id NUMBER;
3702 l_location_id NUMBER;
3703 l_logging_entity VARCHAR2(50);
3704 l_validation_level NUMBER default FND_API.G_VALID_LEVEL_FULL;
3705 l_msg_count NUMBER;
3706 l_msg_data VARCHAR2(200);
3707 l_ignore_for_planning VARCHAR2 (1);
3708 l_return_status VARCHAR2(1);
3709 others EXCEPTION;
3710
3711 --
3712 l_debug_on BOOLEAN;
3713 --
3714 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'Check_Shipset_Ignoreflag';
3715 --
3716 BEGIN
3717
3718 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
3719 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
3720 IF l_debug_on IS NULL THEN
3721 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
3722 END IF;
3723
3724 IF l_debug_on THEN
3725 WSH_DEBUG_SV.push(l_module_name);
3726 WSH_DEBUG_SV.log(l_module_name,'p_ignore_for_planning',p_ignore_for_planning);
3727 WSH_DEBUG_SV.log(l_module_name,'p_logexcep',p_logexcep);
3728 END IF;
3729
3730 l_exception_name := 'WSH_SHPST_IGNRE_FR_PLNG';
3731 l_logging_entity := 'SHIPPER';
3732 FOR j IN p_delivery_detail_ids.FIRST..p_delivery_detail_ids.LAST LOOP
3733 l_delivery_detail_id :=p_delivery_detail_ids(j);
3734 IF l_debug_on THEN
3735 WSH_DEBUG_SV.log(l_module_name,'l_delivery_detail_id',l_delivery_detail_id);
3736 END IF;
3737 OPEN c_check_ignore_for_planning(l_delivery_detail_id);
3738 FETCH c_check_ignore_for_planning INTO l_ignore_for_planning,l_location_id;
3739 IF c_check_ignore_for_planning%FOUND THEN
3740 IF p_logexcep THEN
3741 -- LOG EXCEPTION
3742 IF l_debug_on THEN
3743 WSH_DEBUG_SV.log(l_module_name,'Log exception l_location_id',l_location_id);
3744 END IF;
3745 IF l_location_id IS NOT NULL THEN
3746 FND_MESSAGE.SET_NAME('WSH','WSH_SHIPSET_IGNORE_FOR_PLANG');
3747 FND_MESSAGE.SET_TOKEN('ENTITY',l_delivery_detail_id);
3748 l_exception_message := FND_MESSAGE.GET;
3749 WSH_XC_UTIL.log_exception(
3750 p_api_version => l_api_version,
3751 p_init_msg_list => FND_API.G_FALSE,
3752 p_commit => FND_API.G_FALSE,
3753 p_validation_level => l_validation_level,
3754 x_return_status => l_return_status,
3755 x_msg_count => l_msg_count,
3756 x_msg_data => l_msg_data,
3757 x_exception_id => l_exception_id,
3758 p_exception_location_id => l_location_id,
3759 p_logged_at_location_id => l_location_id,
3760 p_logging_entity => l_logging_entity,
3761 p_logging_entity_id => FND_GLOBAL.USER_ID,
3762 p_exception_name => l_exception_name,
3763 p_message => l_exception_message,
3764 p_delivery_detail_id => l_delivery_detail_id
3765 );
3766 END IF;
3767 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
3768 x_return_status := l_return_status;
3769 raise OTHERS;
3770 END IF;
3771 IF l_debug_on THEN
3772 WSH_DEBUG_SV.log(l_module_name,'L_EXCEPTION_ID',L_EXCEPTION_ID);
3773 WSH_DEBUG_SV.log(l_module_name,'x_return_status',l_return_status);
3774 END IF;
3775 ELSE
3776 IF l_debug_on THEN
3777 WSH_DEBUG_SV.log(l_module_name,'Log waring l_delivery_detail_id',l_delivery_detail_id);
3778 END IF;
3779 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
3780 FND_MESSAGE.SET_NAME('WSH','WSH_SHIPSET_IGNORE_FOR_PLANG');
3781 FND_MESSAGE.SET_TOKEN('ENTITY',l_delivery_detail_id);
3782 wsh_util_core.add_message(WSH_UTIL_CORE.G_RET_STS_WARNING,l_module_name);
3783 END IF;
3784 END IF;
3785 CLOSE c_check_ignore_for_planning;
3786 END LOOP;
3787 --
3788 --
3789 IF l_debug_on THEN
3790 WSH_DEBUG_SV.pop(l_module_name);
3791 END IF;
3792 EXCEPTION
3793 WHEN OTHERS THEN
3794 IF c_check_ignore_for_planning%ISOPEN THEN
3795 CLOSE c_check_ignore_for_planning;
3796 END IF;
3797 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
3798 wsh_util_core.default_handler('WSH_TP_RELEASE.Check_Shipset_Ignoreflag');
3799 --
3800 --
3801 IF l_debug_on THEN
3802 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| x_return_status||SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
3803 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
3804 END IF;
3805
3806 END Check_Shipset_Ignoreflag;
3807
3808 PROCEDURE Check_Shipset_Ignoreflag( p_delivery_detail_id NUMBER,
3809 p_ignore_for_planning VARCHAR2,
3810 p_logexcep boolean,
3811 x_return_status OUT NOCOPY VARCHAR2) IS
3812
3813 p_delivery_detail_ids wsh_util_core.id_tab_type;
3814
3815 BEGIN
3816 p_delivery_detail_ids(0) := p_delivery_detail_id;
3817 Check_Shipset_Ignoreflag( p_delivery_detail_ids=>p_delivery_detail_ids,
3818 p_ignore_for_planning=>p_ignore_for_planning,
3819 p_logexcep=>p_logexcep,
3820 x_return_status=>x_return_status );
3821 END Check_Shipset_Ignoreflag;
3822
3823
3824 END WSH_TP_RELEASE;
[Home] [Help]