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