1 PACKAGE BODY WSH_OTM_OUTBOUND as
2 /* $Header: WSHOTOIB.pls 120.8.12010000.3 2008/11/10 06:42:36 anvarshn ship $ */
3
4 --===================
5 -- CONSTANTS
6 --===================
7 -- declare debug variables
8 l_debug_on BOOLEAN;
9 l_debugfile varchar2(2000);
10
11
12
13 PROCEDURE GET_DEL_DETAILS( p_all_details IN WSH_OTM_DET_TAB,
14 p_delivery_id IN NUMBER,
15 x_del_details IN OUT NOCOPY WSH_OTM_DET_TAB) IS
16
17 l_sub_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'GET_DEL_DETAILS';
18
19 BEGIN
20 -- Debug
21 --
22 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
23 --
24 IF l_debug_on IS NULL
25 THEN
26 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
27 END IF;
28 --
29 -- Debug Statements
30 --
31 IF l_debug_on THEN
32 WSH_DEBUG_SV.push(l_sub_module_name);
33 END IF;
34
35 FOR i in 1..p_all_details.COUNT LOOP
36 --{
37 IF p_all_details(i).delivery_id = p_delivery_id THEN
38 x_del_details.extend;
39 x_del_details(x_del_details.COUNT) := p_all_details(i);
40 END IF;
41 IF x_del_details.COUNT >0 AND p_all_details(i).delivery_id <> p_delivery_id THEN
42 EXIT;
43 END IF;
44 --}
45 END LOOP;
46
47 IF l_debug_on THEN
48 WSH_DEBUG_SV.pop(l_sub_module_name);
49 END IF;
50
51 EXCEPTION
52
53 WHEN FND_API.G_EXC_ERROR THEN
54 IF l_debug_on THEN
55 WSH_DEBUG_SV.log(l_sub_module_name,' FND_API.G_EXC_ERROR',sqlerrm);
56 WSH_DEBUG_SV.pop(l_sub_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
57 END IF;
58 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
59 IF l_debug_on THEN
60 WSH_DEBUG_SV.log(l_sub_module_name,' FND_API.G_EXC_UNEXPECTED_ERROR',sqlerrm);
61 WSH_DEBUG_SV.pop(l_sub_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
62 END IF;
63 WHEN OTHERS THEN
64 wsh_util_core.default_handler('WSH_OTM_OUTBOUND.GET_DEL_DETAILS',l_sub_module_name);
65 IF l_debug_on THEN
66 WSH_DEBUG_SV.log(l_sub_module_name,' OTHERS',sqlerrm);
67 WSH_DEBUG_SV.pop(l_sub_module_name,'EXCEPTION:OTHERS');
68 END IF;
69 END;
70
71
72
73 -- +==========================================================================================+
74 -- Procedure : GET_TRIP_OBJECTS
75 -- Description:
76 -- Procedure to get the Trip, Trip Stop, delivery,delivery details and Lpn info
77 -- in the form of objects (WSH_OTM_TRIP_TAB)
78
79 -- Inputs/Outputs:
80 -- p_trip_id_tab - id table (list of Trip Ids)
81 -- p_user_id - User Id to set the context
82 -- p_resp_id - Resp Id to set the context
83 -- p_resp_appl_id - Resp Appl Id to set the context
84 -- Output:
85 -- x_domain_name - domain name
86 -- x_otm_user_name - otm User Name
87 -- x_otm_pwd - otm Password
88 -- x_otm_pwd - otm Password
89 -- x_trip_tab - Nested Table which contains the trip info
90 -- x_error_trip_id_tab - List of ids for which the data could not be retrieved
91 -- x_return_status
92 -- +==========================================================================================+
93
94 PROCEDURE GET_TRIP_OBJECTS(p_trip_id_tab IN OUT NOCOPY WSH_OTM_ID_TAB,
95 p_user_id IN NUMBER,
96 p_resp_id IN NUMBER,
97 p_resp_appl_id IN NUMBER,
98 x_domain_name OUT NOCOPY VARCHAR2,
99 x_otm_user_name OUT NOCOPY VARCHAR2,
100 x_otm_pwd OUT NOCOPY VARCHAR2,
101 x_server_tz_code OUT NOCOPY VARCHAR2,
102 x_trip_tab OUT NOCOPY WSH_OTM_TRIP_TAB,
103 x_dlv_tab OUT NOCOPY WSH_OTM_DLV_TAB,
104 x_error_trip_id_tab OUT NOCOPY WSH_OTM_ID_TAB,
105 x_return_status OUT NOCOPY VARCHAR2)IS
106
107 -- Declare local variables
108
109 l_sub_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'GET_TRIP_OBJECTS';
110
111 l_trip_tab WSH_OTM_TRIP_TAB;
112 l_stops_tab WSH_OTM_STOP_TAB;
113 --lpn_tab WSH_OTM_LPN_TAB;
114 l_dlv_tab WSH_OTM_DLV_TAB;
115 l_dlv_ids WSH_OTM_ID_TAB;
116 l_vol NUMBER;
117 l_gross_weight NUMBER;
118 l_net_weight NUMBER;
119 l_total_gross_wt NUMBER;
120 l_total_net_wt NUMBER;
121 l_total_vol NUMBER;
122 l_delivery_id NUMBER;
123 l_vol_uom VARCHAR2(150);
124 l_weight_uom VARCHAR2(150);
125 x_base_wt_uom VARCHAR2(150);
126 x_base_vol_uom VARCHAR2(150);
127 l_lpn_count NUMBER;
128 l_stop_details WSH_OTM_STOP_DET_TAB;
129 l_new_stop_details WSH_OTM_STOP_DET_TAB;
130 l_lpns WSH_OTM_LPN_TAB;
131 l_pick_up_flag VARCHAR2(1);
132 l_drop_off_flag VARCHAR2(1);
133 l_trips_sql VARCHAR2(2000);
134 l_trip_obj WSH_OTM_TRIP_OBJ;
135 l_stop_obj WSH_OTM_STOP_OBJ;
136 c_trips WSH_UTIL_CORE.RefCurType;
137 bind_col_tab WSH_UTIL_CORE.tbl_varchar;
138 i NUMBER;
139 i1 NUMBER;
140 l_lpn_tab WSH_OTM_LPN_TAB;
141 l_organization_id NUMBER;
142 l_return_status VARCHAr2(10);
143 l_all_dlv_tab WSH_OTM_DLV_TAB;
144 l_total_freight_cost NUMBER;
145 l_currency_code VARCHAR2(15);
146 l_sob_id NUMBER;
147 l_car_type VARCHAR2(5) := 'CAR-';
148
149 -- Define cursor to get the deliveries picked at a stop
150 CURSOR get_deliveries_picked (p_stop_id NUMBER) IS
151 select delivery_id
152 from
153 wsh_delivery_legs
154 where pick_up_stop_id = p_stop_id;
155
156 -- Define cursor to get the deliveries dropped at a stop
157 CURSOR get_deliveries_dropped (p_stop_id NUMBER) IS
158 select delivery_id
159 from
160 wsh_delivery_legs
161 where drop_off_stop_id = p_stop_id;
162
163 --bug# 6497991 (begin) : Update tms_interface_flag for error trip stops back to 'ASR'.
164 CURSOR get_trip_stops(c_trip_id NUMBER) IS
165 SELECT stop_id,'ASR' tms_iface_new_status
166 FROM wsh_trip_stops
167 WHERE trip_id = c_trip_id;
168
169 x_error_dlv_id_tab WSH_OTM_ID_TAB;
170 l_upd_trip_stops WSH_UTIL_CORE.ID_TAB_TYPE;
171 l_upd_tms_interface_flags WSH_UTIL_CORE.COLUMN_TAB_TYPE;
172 l_old_trip_id NUMBER;
173 l_del_trip_rec NUMBER := NULL;
174 l_num_error NUMBER;
175 --bug# 6497991 (end)
176
177 TYPE dlv_in_tab_type is TABLE Of wsh_otm_dlv_obj INDEX BY BINARY_INTEGER;
178 dlv_in_tab dlv_in_tab_type;
179
180 GET_DELIVERY_OBJECTS_FALIED EXCEPTION;
181 GET_DEAFULT_UOMS_FALIED EXCEPTION;
182 GET_FREIGHT_COST_ERROR EXCEPTION;
183
184 BEGIN
185
186
187 -- Initialize API return status to success
188
189 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
190
191 -- Debug
192 --
193 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
194 --
195 IF l_debug_on IS NULL
196 THEN
197 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
198 END IF;
199 --
200 -- Debug Statements
201 --
202 IF l_debug_on THEN
203 WSH_DEBUG_SV.push(l_sub_module_name);
204 END IF;
205
206 -- Setting the apps context
207 FND_GLOBAL.apps_initialize(user_id => p_user_id,
208 resp_id =>p_resp_id,
209 resp_appl_id => p_resp_appl_id);
210
211 IF l_debug_on THEN
212 WSH_DEBUG_SV.log(l_sub_module_name,'Building the dymanic sql to fetch Trips');
213 END IF;
214
215 --dbms_output.put_line('Building the sql');
216 -- Bug 6732184: Added null values for dock_dook_xid, start_time and end_time of WSH_OTM_STOP_OBJ
217 l_trips_sql :=
218 'select '||
219 'WSH_OTM_TRIP_OBJ(WT.TP_PLAN_NAME, '||
220 'NAME ,'||
221 'null,'||
222 '''RC'','||
223 'WT.carrier_id,'||
224 'null,'||
225 'WT.MODE_OF_TRANSPORT,'||
226 'null,'||
227 'null,'|| --weight_uom
228 'null,'|| --Volume
229 'null,'|| -- volume_uom
230 'null,'|| --lpn count
231 'WT.FREIGHT_TERMS_CODE,'||
232 'null,'|| -- stop count
233 'null,'|| -- release count
234 'null,'||
235 'null,'||
236 'WT.VEHICLE_ITEM_ID,'||
237 'WT.VEHICLE_NUM_PREFIX,'||
238 'WT.VEHICLE_NUMBER,'||
239 'null,'||
240 'WT.VEHICLE_ORGANIZATION_ID,'||
241 'null,'||
242 'WT.SEAL_CODE,'||
243 'sequence_number,'|| -- master_bol_number
244 'WT.PLANNED_FLAG,'||
245 'WT.ROUTING_INSTRUCTIONS,'||
246 'null, '||-- gross weight
247 'null, '||-- net_weight
248 'wt.BOOKING_NUMBER , '||
249 'null,'||
250 'WT.TRIP_ID,'||
251 'nvl(WT.IGNORE_FOR_PLANNING,''N''),'||
252 'WT.OPERATOR,'||
253 'null,'|| -- Manual Freight cost
254 'null,'|| -- Currency Code
255 'null, '||
256 '''TRIP_ID'','||
257 '''MBOL_NUMBER'','||
258 '''PLANNED_TRIP'','||
259 '''MANUAL_FREIGHT_COSTS'','||
260 '''MAN_FREIGHT_COST_CUR'','||
261 '''OPERATOR'','||
262 '''ROUTING_INSTR'','||
263 'null,'|| -- Stops
264 'null ,null,null,null,null), '||
265 'WSH_OTM_STOP_OBJ(wts.STOP_ID , '||
266 'WTS.STOP_SEQUENCE_NUMBER,null,null,'||
267 'TO_CHAR(WTS.PLANNED_ARRIVAL_DATE,''YYYYMMDDHH24MISS''),'||
268 'TO_CHAR(WTS.PLANNED_DEPARTURE_DATE,''YYYYMMDDHH24MISS''),'||
269 'TO_CHAR(WTS.ACTUAL_ARRIVAL_DATE,''YYYYMMDDHH24MISS''),'||
270 'TO_CHAR(WTS.ACTUAL_DEPARTURE_DATE,''YYYYMMDDHH24MISS''),'||
271 -- 'TO_CHAR(WTS.PLANNED_ARRIVAL_DATE,''DD-MON-YYYY HH24:MI:SS''),'||
272 -- 'TO_CHAR(WTS.PLANNED_DEPARTURE_DATE,''DD-MON-YYYY HH24:MI:SS''),'||
273 -- 'TO_CHAR(WTS.ACTUAL_ARRIVAL_DATE,''DD-MON-YYYY HH24:MI:SS''),'||
274 -- 'TO_CHAR(WTS.ACTUAL_DEPARTURE_DATE,''DD-MON-YYYY HH24:MI:SS''),'||
275 'WTS.loading_end_datetime - WTS.loading_start_datetime,'||
276 'WTS.DEPARTURE_SEAL_CODE,''DEPARTURE_SEAL_CODE'', null, null, null, null, null), '||
277 'nvl(WTS.DEPARTURE_GROSS_WEIGHT,0),'||
278 'nvl(WTS.DEPARTURE_NET_WEIGHT,0),'||
279 'nvl(WTS.DEPARTURE_VOLUME,0), '||
280 'WTS.WEIGHT_UOM_CODE,'||
281 'WTS.VOLUME_UOM_CODE '||
282 ' from wsh_trips wt , wsh_document_instances wdi, wsh_trip_stops wts '||
283 ' where wt.trip_id = wts.trip_id '||
284 ' and wdi.entity_name(+) = ''WSH_TRIPS'''||
285 ' and wdi.entity_id(+) = wt.trip_id '||
286 ' and wts.physical_stop_id is null '||
287 ' and wts.tms_interface_flag = ''ASP'''||
288 ' and wt.trip_id in (';
289
290
291 FOR i in 1..p_trip_id_tab.COUNT LOOP
292 --{
293 if i <> 1 then
294 l_trips_sql := l_trips_sql || ',';
295 end if;
296 l_trips_sql := l_trips_sql || ':' || i;
297 bind_col_tab(bind_col_tab.COUNT+1) := to_char(p_trip_id_tab(i));
298 --}
299 END LOOP;
300 l_trips_sql := l_trips_sql || ')';
301 l_trips_sql := l_trips_sql || ' ORDER BY WT.TRIP_ID,WTS.STOP_SEQUENCE_NUMBER'; --bug#5975661 : order by sequence num.
302
303 i:=1;
304
305 WSH_UTIL_CORE.OpenDynamicCursor(c_trips, l_trips_sql, bind_col_tab);
306 x_trip_tab := WSH_OTM_TRIP_TAB();
307 l_trip_tab := WSH_OTM_TRIP_TAB();
308 l_stops_tab := WSH_OTM_STOP_TAB();
309 l_stop_details := WSH_OTM_STOP_DET_TAB();
310 l_all_dlv_tab := WSH_OTM_DLV_TAB();
311 -- Bug 6497991: Populating error trips
312 x_error_trip_id_tab := WSH_OTM_ID_TAB();
313 l_old_trip_id := -1;
314 -- Bug 6497991:end
315 LOOP
316 --{
317 -- Bug 6497991: The Defined exception for a trip should not stop processing of other trips
318 BEGIN
319 --{
320 FETCH c_trips INTO l_trip_obj,l_stop_obj,l_gross_weight,l_net_weight, l_vol, l_weight_uom, l_vol_uom;
321 EXIT WHEN (c_trips%NOTFOUND);
322 IF ( l_old_trip_id <> l_trip_obj.trip_id ) THEN
323 --{
324 l_old_trip_id := l_trip_obj.trip_id;
325 l_trip_tab.extend;
326 l_trip_tab(l_trip_tab.COUNT) := l_trip_obj;
327 l_trip_tab(l_trip_tab.COUNT).shipment_stops := WSH_OTM_STOP_TAB();
328
329 IF l_debug_on
330 THEN
331 WSH_DEBUG_SV.log(l_sub_module_name,'Fetch Trip Id', l_trip_obj.trip_id);
332 END IF;
333
334
335 -- Get the OrganizationId of Trip
336 l_organization_id := WSH_UTIL_CORE.GET_TRIP_ORGANIZATION_ID(l_trip_obj.trip_id);
337 IF l_debug_on
338 THEN
339 WSH_DEBUG_SV.log(l_sub_module_name,' Organization Id after calling GET_TRIP_ORGANIZATION_ID ' ,
340 l_organization_id);
341 END IF;
342 --If Vehicle_organization_id is null then take the trip Organization_id
343 IF l_trip_tab(l_trip_tab.COUNT).EQUIPMENT_GROUP_XID IS NULL THEN
344 l_trip_tab(l_trip_tab.COUNT).EQUIPMENT_GROUP_XID := l_organization_id;
345 END IF;
346
347
348 -- Get the Default UOMS based on the Organization Id
349 wsh_wv_utils.get_default_uoms(l_organization_id, x_base_wt_uom, x_base_vol_uom, x_return_status);
350 IF x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS then
351 --{
352 -- Bug 6497991: Deleting the trip record before raising exception
353 IF l_debug_on THEN
354 WSH_DEBUG_SV.log(l_sub_module_name,'failed in get_default_uoms' ,x_return_status);
355 END IF;
356 l_del_trip_rec := l_trip_tab(l_trip_tab.COUNT).trip_id;
357 x_error_trip_id_tab.extend;
358 x_error_trip_id_tab(x_error_trip_id_tab.COUNT) := l_trip_tab(l_trip_tab.COUNT).trip_id;
359 l_trip_tab.TRIM;
360 raise GET_DEAFULT_UOMS_FALIED;
361 --}
362 END IF;
363 WSH_OTM_RIQ_XML.Get_EBS_To_OTM_UOM(
364 p_uom=>x_base_wt_uom,
365 x_uom=>l_trip_tab(l_trip_tab.COUNT).WEIGHT_UOM_XID ,
366 x_return_status=>l_return_status);
367
368 IF((l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) AND
369 (l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING)) THEN
370 --{
371 IF l_debug_on THEN
372 WSH_DEBUG_SV.log(l_sub_module_name,'Get_EBS_To_OTM_UOM Weight Failed');
373 END IF;
374 -- Bug 6497991: Deleting the trip record before raising exception
375 l_del_trip_rec := l_trip_tab(l_trip_tab.COUNT).trip_id;
376 x_error_trip_id_tab.extend;
377 x_error_trip_id_tab(x_error_trip_id_tab.COUNT) := l_trip_tab(l_trip_tab.COUNT).trip_id;
378 l_trip_tab.TRIM;
379 raise FND_API.G_EXC_ERROR;
380 --}
381 END IF;
382
383 WSH_OTM_RIQ_XML.Get_EBS_To_OTM_UOM(
384 p_uom=>x_base_vol_uom,
385 x_uom=>l_trip_tab(l_trip_tab.COUNT).VOLUME_UOM_XID ,
386 x_return_status=>l_return_status);
387
388 IF((l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) AND
389 (l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING)) THEN
390 IF l_debug_on THEN
391 WSH_DEBUG_SV.log(l_sub_module_name,'Get_EBS_To_OTM_UOM Weight Failed');
392 END IF;
393 -- Bug 6497991: Deleting the trip record before raising exception
394 --l_trip_tab.delete(l_trip_tab.COUNT);
395 l_del_trip_rec := l_trip_tab(l_trip_tab.COUNT).trip_id;
396 x_error_trip_id_tab.extend;
397 x_error_trip_id_tab(x_error_trip_id_tab.COUNT) := l_trip_tab(l_trip_tab.COUNT).trip_id;
398 l_trip_tab.TRIM;
399 raise FND_API.G_EXC_ERROR;
400 END IF;
401
402 l_total_gross_wt := 0;
403 l_total_net_wt := 0;
404 l_total_vol := 0;
405 --}
406 END IF;
407
408 --Bug 6497991: Need to add stop details ONLY IF the trip is not having any error.
409 IF (NVL(l_del_trip_rec,-1) <> l_trip_obj.trip_id) THEN
410 l_trip_tab(l_trip_tab.COUNT).shipment_stops.extend;
411 l_trip_tab(l_trip_tab.COUNT).shipment_stops(l_trip_tab(l_trip_tab.COUNT).shipment_stops.COUNT) := l_stop_obj;
412
413 IF x_base_wt_uom <> l_weight_uom THEN
414 --{
415 l_total_gross_wt := l_total_gross_wt + WSH_WV_UTILS.CONVERT_UOM(l_weight_uom,
416 x_base_wt_uom,
417 l_gross_weight,null);
418 l_total_net_wt := l_total_net_wt + WSH_WV_UTILS.CONVERT_UOM(l_weight_uom,
419 x_base_wt_uom,
420 l_net_weight,null);
421 --}
422 ELSE
423 --{
424 l_total_gross_wt := l_total_gross_wt + l_gross_weight;
425 l_total_net_wt := l_total_net_wt + l_net_weight;
426 --}
427 END IF;
428
429 IF x_base_vol_uom <> l_vol_uom THEN
430 l_total_vol := l_total_vol + WSH_WV_UTILS.CONVERT_UOM(l_vol_uom,
431 x_base_vol_uom,
432 l_vol,null);
433 ELSE
434 l_total_vol := l_total_vol + l_vol;
435 END IF;
436
437 l_trip_tab(l_trip_tab.COUNT).GROSS_WEIGHT := l_total_gross_wt;
438 l_trip_tab(l_trip_tab.COUNT).NET_WEIGHT := l_total_net_wt;
439 l_trip_tab(l_trip_tab.COUNT).VOLUME := l_total_vol;
440 END IF;
441
442 EXCEPTION
443 WHEN GET_DEAFULT_UOMS_FALIED THEN
444 l_num_error := l_num_error + 1;
445 IF l_debug_on THEN
446 WSH_DEBUG_SV.log(l_sub_module_name,' GET_DEAFULT_UOMS_FALIED',sqlerrm);
447 WSH_DEBUG_SV.pop(l_sub_module_name,'EXCEPTION:GET_DEAFULT_UOMS_FALIED');
448 END IF;
449
450 WHEN FND_API.G_EXC_ERROR THEN
451 l_num_error := l_num_error + 1;
452 IF l_debug_on THEN
453 WSH_DEBUG_SV.log(l_sub_module_name,' FND_API.G_EXC_ERROR',sqlerrm);
454 WSH_DEBUG_SV.pop(l_sub_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
455 END IF;
456
457 END;
458 --}
459 END LOOP;
460
461 IF l_debug_on THEN
462 WSH_DEBUG_SV.log(l_sub_module_name,'Number of Trips Fetched' , l_trip_tab.COUNT);
463 FOR i in 1..l_trip_tab.COUNT LOOP
464 WSH_DEBUG_SV.log(l_sub_module_name,'Fetched Trips' , l_trip_tab(i).trip_id);
465 END LOOP;
466 END IF;
467
468 IF l_trip_tab.COUNT >0 THEN
469 --{
470 FOR i in 1..l_trip_tab.COUNT LOOP
471 --{
472 --bug 6497991 Added iF l_trip_tab.EXISTS(i): if trip errors in this loop.
473 IF l_trip_tab.EXISTS(i) THEN
474 BEGIN
475 --{
476 IF l_debug_on THEN
477 WSH_DEBUG_SV.log(l_sub_module_name,'Processing Trip with Id ' , l_trip_tab(i).trip_id);
478 END IF;
479
480 l_dlv_ids := WSH_OTM_ID_TAB();
481 l_stops_tab := l_trip_tab(i).shipment_stops;
482
483
484
485 IF l_trip_tab(i).ignore_for_planning = 'Y' THEN
486 l_trip_tab(i).SHIPMENT_XID := 'WSH-' || l_trip_tab(i).TRIP_ID ;
487 l_trip_tab(i).STOP_COUNT := l_stops_tab.COUNT;
488 ELSE
489 l_trip_tab(i).SERVICE_PROVIDER_XID := '';
490 l_trip_tab(i).TRANSPORT_MODE_XID := '';
491 l_trip_tab(i).PAYMENT_CODE_XID := '';
492 l_trip_tab(i).BOOKING_NUMBER := '';
493 END IF;
494
495
496 IF l_trip_tab(i).SERVICE_PROVIDER_XID IS NOT NULL THEN
497 l_trip_tab(i).SERVICE_PROVIDER_XID := l_car_type || l_trip_tab(i).SERVICE_PROVIDER_XID;
498 END IF;
499
500 IF l_trip_tab(i).EQUIPMENT_XID IS NOT NULL THEN
501 l_trip_tab(i).EQUIPMENT_XID := wsh_util_core.get_item_name(
502 p_item_id =>to_number(l_trip_tab(i).EQUIPMENT_XID),
503 p_organization_id =>l_trip_tab(i).EQUIPMENT_GROUP_XID);
504 END IF;
505
506
507 -- Get the curreny code from GL_SETS_OF_BOOKS
508 l_sob_id := FND_PROFILE.VALUE('GL_SET_OF_BKS_ID');
509 SELECT currency_code INTO l_currency_code
510 FROM GL_SETS_OF_BOOKS
511 WHERE set_of_books_id = l_sob_id;
512
513 WSH_FREIGHT_COSTS_PVT.Get_Trip_Manual_Freight_Cost(l_trip_tab(i).TRIP_ID,
514 l_currency_code,
515 l_total_freight_cost,
516 l_return_status);
517
518 IF l_return_status NOT IN (WSH_UTIL_CORE.G_RET_STS_SUCCESS,WSH_UTIL_CORE.G_RET_STS_WARNING) THEN
519 --{
520 -- Bug 6497991: Deleting the trip record before raising exception
521 IF l_debug_on THEN
522 WSH_DEBUG_SV.log(l_sub_module_name,'Get_Trip_Manual_Freight_Cost Failed');
523 END IF;
524 x_error_trip_id_tab.extend;
525 x_error_trip_id_tab(x_error_trip_id_tab.COUNT) := l_trip_tab(i).trip_id;
526 l_trip_tab.delete(i);
527 raise GET_FREIGHT_COST_ERROR;
528 ELSIF l_return_status=WSH_UTIL_CORE.G_RET_STS_WARNING THEN
529 x_return_status:=l_return_status;
530 --}
531 END IF;
532
533 l_trip_tab(i).MANUAL_FREIGHT_COSTS := l_total_freight_cost;
534 l_trip_tab(i).CURRENCY_CODE := l_currency_code;
535
536
537
538 FOR i1 in 1..l_stops_tab.COUNT LOOP
539 --{
540
541 IF l_debug_on THEN
542 WSH_DEBUG_SV.log(l_sub_module_name,'Processing Stop with Id ' , l_stops_tab(i1).STOP_LOCATION_XID);
543 END IF;
544
545 l_stop_details := WSH_OTM_STOP_DET_TAB();
546
547
548
549 OPEN get_deliveries_picked(l_stops_tab(i1).STOP_LOCATION_XID);
550 LOOP
551 --{
552 fetch get_deliveries_picked into l_delivery_id;
553 EXIT WHEN (get_deliveries_picked%NOTFOUND);
554 IF l_debug_on THEN
555 WSH_DEBUG_SV.log(l_sub_module_name,'Delivery being Picked up ' , l_delivery_id);
556 END IF;
557 l_dlv_ids.extend;
558 l_dlv_ids(l_dlv_ids.COUNT) := l_delivery_id;
559 -- Pick Up Stop
560 l_pick_up_flag := 'Y';
561 l_stop_details.extend;
562 l_stop_details(l_stop_details.COUNT) := WSH_OTM_STOP_DET_OBJ('P',l_delivery_id);
563 --}
564 END LOOP;
565
566 OPEN get_deliveries_dropped(l_stops_tab(i1).STOP_LOCATION_XID);
567 LOOP
568 --{
569 fetch get_deliveries_dropped into l_delivery_id;
570 EXIT WHEN (get_deliveries_dropped%NOTFOUND);
571 IF l_debug_on THEN
572 WSH_DEBUG_SV.log(l_sub_module_name,'Delivery being Dropped off ' , l_delivery_id);
573 END IF;
574
575 -- Drop Off Stop
576 l_drop_off_flag := 'Y';
577 l_stop_details.extend;
578 l_stop_details(l_stop_details.COUNT) := WSH_OTM_STOP_DET_OBJ('D',l_delivery_id);
579 --}
580 END LOOP;
581
582
583 IF l_trip_tab(i).IGNORE_FOR_PLANNING = 'N' THEN
584 l_stops_tab(i1).stop_duration := null;
585 END IF;
586
587 --bug#5975661: Changing the stop sequence numbers for non planned trips as OTM expects sequence
588 -- numbers starting from 1 (instead of stop sequence number (WTS.STOP_SEQUENCE_NUMBER)).
589 IF l_trip_tab(i).IGNORE_FOR_PLANNING = 'Y' THEN
590 l_stops_tab(i1).stop_sequence_number := i1;
591 END IF;
592 --bug#597566:end
593 -- Drop off Stop
594 IF l_drop_off_flag = 'Y' THEN
595 --{
596 l_stops_tab(i1).stop_duration := null;
597 IF l_trip_tab(i).IGNORE_FOR_PLANNING = 'Y' THEN
598 l_stops_tab(i1).ACTUAL_ARRIVAL_TIME := nvl(l_stops_tab(i1).ACTUAL_ARRIVAL_TIME,l_stops_tab(i1).PLANNED_ARRIVAL_TIME) ;
599 l_stops_tab(i1).ACTUAL_DEPARTURE_TIME := nvl(l_stops_tab(i1).ACTUAL_DEPARTURE_TIME,l_stops_tab(i1).PLANNED_DEPARTURE_TIME);
600 END IF;
601 --}
602 END IF;
603
604 CLOSE get_deliveries_picked;
605 CLOSE get_deliveries_dropped;
606 IF l_debug_on THEN
607 WSH_DEBUG_SV.log(l_sub_module_name,'Assigning the stop Details');
608 END IF;
609 l_stops_tab(i1).stop_details := l_stop_details;
610
611 l_stops_tab(i1).STOP_LOCATION_XID := WSH_OTM_REF_DATA_GEN_PKG.GET_STOP_LOCATION_XID(l_stops_tab(i1).STOP_LOCATION_XID);
612 --}
613 END LOOP;
614
615
616
617
618 IF l_debug_on THEN
619 WSH_DEBUG_SV.log(l_sub_module_name,'List of delivery Ids passed to GET_DELIVERY_OBJECTS');
620 END IF;
621 FOR i in 1..l_dlv_ids.COUNT LOOP
622 --{
623 IF l_debug_on THEN
624 WSH_DEBUG_SV.log(l_sub_module_name,'dlv_ids' ,l_dlv_ids(i));
625 END IF;
626 --}
627 END LOOP;
628
629 -- Bug 6497991: Changed the parameter from x_error_trip_id_tab to x_error_dlv_id_tab
630 -- After the call, deleting the trip record before raising exception
631 --Bug 7408338 Added trip_id parameter
632 Get_Delivery_objects(l_dlv_ids,p_user_id,p_resp_id, p_resp_appl_id,'A',l_trip_tab(i).trip_id,x_domain_name,x_otm_user_name,
633 x_otm_pwd,x_server_tz_code, l_dlv_tab,x_error_dlv_id_tab,x_return_status);
634
635 IF x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS then
636 --{
637 IF l_debug_on THEN
638 WSH_DEBUG_SV.log(l_sub_module_name,'failed in Get_Delivery_objects' ,x_return_status);
639 END IF;
640 x_error_trip_id_tab.extend;
641 x_error_trip_id_tab(x_error_trip_id_tab.COUNT) := l_trip_tab(i).trip_id;
642 l_trip_tab.delete(i);
643 raise GET_DELIVERY_OBJECTS_FALIED;
644 --}
645 END IF;
646
647 l_lpn_tab := WSH_OTM_LPN_TAB();
648
649 FOR i2 in 1..l_dlv_tab.COUNT LOOP
650 --{
651 l_lpn_count := l_lpn_count + l_dlv_tab(i2).lpn.COUNT;
652 FOR j in 1..l_dlv_tab(i2).lpn.COUNT LOOP
653 --{
654 l_lpn_tab.extend;
655 l_lpn_tab(l_lpn_tab.COUNT) := l_dlv_tab(i2).lpn(j);
656 l_lpn_tab(l_lpn_tab.COUNT).EQUIPMENT_XID := l_trip_tab(i).EQUIPMENT_XID;
657 --}
658 END LOOP;
659 dlv_in_tab(l_dlv_tab(i2).delivery_id) := l_dlv_tab(i2);
660 l_all_dlv_tab.extend;
661 l_all_dlv_tab(l_all_dlv_tab.COUNT) := l_dlv_tab(i2);
662 --}
663 END LOOP;
664
665 -- Populating the Stop Details
666 FOR i in 1..l_stops_tab.COUNT LOOP
667 --{
668 l_stop_details := l_stops_tab(i).stop_details;
669 l_stops_tab(i).stop_details := null;
670 l_new_stop_details := WSH_OTM_STOP_DET_TAB();
671 for j in 1..l_stop_details.COUNT loop
672 --{
673 l_lpns := dlv_in_tab(l_stop_details(j).lpn_id).lpn;
674 for k in 1..l_lpns.COUNT LOOP
675 --{
676 l_new_stop_details.extend;
677 l_new_stop_details(l_new_stop_details.COUNT) := WSH_OTM_STOP_DET_OBJ(l_stop_details(j).activity,l_lpns(k).lpn_id);
678 --}
679 end loop;
680 --}
681 end loop;
682 l_stops_tab(i).stop_details := l_new_stop_details;
683 --}
684 end loop;
685
686 l_trip_tab(i).LPNS := l_lpn_tab;
687 l_trip_tab(i).SHIPMENT_STOPS := l_stops_tab;
688 l_trip_tab(i).shipunit_count := l_lpn_count ;
689 --l_trip_tab(i).shipment_deliveries:= l_dlv_tab;
690 --}
691 --Bug 6497991: The Exceptions need to be handled inside the loop.
692 --Exception for 1 trip should not affect other trips.
693 EXCEPTION
694 WHEN GET_FREIGHT_COST_ERROR THEN
695 l_num_error := l_num_error + 1;
696 IF l_debug_on THEN
697 WSH_DEBUG_SV.log(l_sub_module_name,' GET_FREIGHT_COST_ERROR',sqlerrm);
698 WSH_DEBUG_SV.pop(l_sub_module_name,'EXCEPTION:GET_FREIGHT_COST_ERROR');
699 END IF;
700
701 WHEN GET_DELIVERY_OBJECTS_FALIED THEN
702 l_num_error := l_num_error + 1;
703 IF l_debug_on THEN
704 WSH_DEBUG_SV.log(l_sub_module_name,' GET_DELIVERY_OBJECTS_FALIED',sqlerrm);
705 WSH_DEBUG_SV.pop(l_sub_module_name,'EXCEPTION:GET_DELIVERY_OBJECTS_FALIED');
706 END IF;
707
708 END;
709 END IF;
710 --}
711 END LOOP;
712 -- Bug 6497991: the cursor needs to be closed if the last trip in the above loop throws any exception.
713 IF get_deliveries_picked%ISOPEN THEN
714 CLOSE get_deliveries_picked;
715 END IF;
716
717 IF get_deliveries_dropped%ISOPEN THEN
718 CLOSE get_deliveries_dropped;
719 END IF;
720 x_trip_tab := l_trip_tab;
721 x_dlv_tab := l_all_dlv_tab;
722 -- Bug 6497991(begin): Only Trips sucessfully processed to be sent.
723 -- for error trips, update tms_interface_flag back to 'ASR'
724 IF (l_trip_tab.count > 0) THEN
725 --{
726 p_trip_id_tab.TRIM(p_trip_id_tab.count);
727 i := l_trip_tab.first;
728 WHILE (i is NOT NULL) LOOP
729 --{
730 p_trip_id_tab.extend;
731 p_trip_id_tab(p_trip_id_tab.COUNT) := l_trip_tab(i).trip_id;
732 i := l_trip_tab.NEXT(i);
733 --}
734 END LOOP;
735 --}
736 END IF;
737 -- Bug 6497991(end):
738
739 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
740 --}
741 ELSE
742 --{
743 x_return_status := FND_API.G_RET_STS_ERROR;
744 --}
745 END IF;
746 --
747 -- Bug 6497991(begin): for error trips, update tms_interface_flag back to 'ASR'
748 --
749 IF l_debug_on THEN
750 WSH_DEBUG_SV.log(l_sub_module_name,'ERROR TRIP ids Count',x_error_trip_id_tab.count);
751 END IF;
752 IF ( x_error_trip_id_tab.count > 0 ) THEN
753 --{
754 i := x_error_trip_id_tab.first;
755 WHILE (i is not NULL) LOOP
756 --{
757 OPEN get_trip_stops(x_error_trip_id_tab(i));
758 FETCH get_trip_stops BULK COLLECT INTO l_upd_trip_stops,l_upd_tms_interface_flags;
759 CLOSE get_trip_stops;
760 IF l_upd_trip_stops.COUNT > 0 THEN
761 --{
762 IF l_debug_on THEN
763 WSH_DEBUG_SV.log(l_sub_module_name,'Calling WSH_TRIP_STOPS_PVT.UPDATE_TMS_INTERFACE_FLAG Total stops ids',l_upd_trip_stops.COUNT);
764 END IF;
765 --
766 WSH_TRIP_STOPS_PVT.UPDATE_TMS_INTERFACE_FLAG
767 (P_STOP_ID_TAB=>l_upd_trip_stops,
768 P_TMS_INTERFACE_FLAG_TAB =>l_upd_tms_interface_flags,
769 X_RETURN_STATUS =>l_return_status);
770 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS then
771 IF l_debug_on THEN
772 WSH_DEBUG_SV.log(l_sub_module_name,'failed in UPDATE_TMS_INTERFACE_FLAG');
773 END IF;
774 raise FND_API.G_EXC_ERROR;
775 END IF;
776 --}
777 END IF;
778 i := x_error_trip_id_tab.NEXT(i);
779 --}
780 END LOOP;
781 --}
782 END IF;
783 -- Bug 6497991(end)
784 --
785 -- Debug Statements
786 --
787 IF l_debug_on THEN
788 WSH_DEBUG_SV.pop(l_sub_module_name);
789 END IF;
790 --
791 EXCEPTION
792 -- Bug 6497991: Defined exceptions to be handled inside the LOOP
793 WHEN OTHERS THEN
794 wsh_util_core.default_handler('WSH_OTM_OUTBOUND.GET_TRIP_OBJECTS',l_sub_module_name);
795 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
796 IF get_deliveries_picked%ISOPEN THEN
797 CLOSE get_deliveries_picked;
798 END IF;
799 IF get_deliveries_dropped%ISOPEN THEN
800 CLOSE get_deliveries_dropped;
801 END IF;
802 IF l_debug_on THEN
803 WSH_DEBUG_SV.log(l_sub_module_name,' OTHERS',sqlerrm);
804 WSH_DEBUG_SV.pop(l_sub_module_name,'EXCEPTION:OTHERS');
805 END IF;
806 END GET_TRIP_OBJECTS;
807
808
809
810 -- +==================================================================================================+
811 -- Procedure : GET_DELIVERY_OBJECTS
812 -- Description:
813 -- Procedure to get the delivery,delivery details and Lpn info
814 -- in the form of objects (WSH_OTM_DLV_TAB)
815
816 -- Inputs/Outputs:
817 -- p_dlv_id_tab - id table (list of delivery Ids)
818 -- p_user_id - User Id to set the context
819 -- p_resp_id - Resp Id to set the context
820 -- p_resp_appl_id - Resp Appl Id to set the context
821 -- p_caller - When passed from GET_TRIP_OBJECTS this will have a
822 -- value of 'A' else default 'D'
823 -- p_trip_id - When passed from GET_TRIP_OBJECTS this will have trip_id else dafault -1
824 --
825 -- Output:
826 -- x_domain_name - domain name
827 -- x_otm_user_name - otm User Name
828 -- x_otm_pwd - otm Password
829 -- x_otm_pwd - otm Password
830 -- x_dlv_tab - Nested Table which contains the delivery info
831 -- x_error_dlv_id_tab - List of ids for which the data could not be retrieved
832 -- x_return_status
833 -- +==================================================================================================+
834
835
836
837 PROCEDURE GET_DELIVERY_OBJECTS(p_dlv_id_tab IN OUT NOCOPY WSH_OTM_ID_TAB,
838 p_user_id IN NUMBER,
839 p_resp_id IN NUMBER,
840 p_resp_appl_id IN NUMBER,
841 p_caller IN VARCHAR2 DEFAULT 'D',
842 p_trip_id IN NUMBER DEFAULT -1, --Bug7408338
843 x_domain_name OUT NOCOPY VARCHAR2,
844 x_otm_user_name OUT NOCOPY VARCHAR2,
845 x_otm_pwd OUT NOCOPY VARCHAR2,
846 x_server_tz_code OUT NOCOPY VARCHAR2,
847 x_dlv_tab OUT NOCOPY WSH_OTM_DLV_TAB,
848 x_error_dlv_id_tab OUT NOCOPY WSH_OTM_ID_TAB,
849 x_return_status OUT NOCOPY VARCHAR2 ) IS
850
851 -- Declare local variables
852
853 l_sub_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'GET_DELIVERY_OBJECTS';
854 l_dlv_tab wsh_otm_dlv_tab;
855 l_all_details wsh_otm_det_tab;
856 l_del_details wsh_otm_det_tab;
857 l_rl_details wsh_otm_det_tab;
858 l_lpn_tab wsh_otm_lpn_tab;
859 l_packed_items WSH_OTM_LPN_CONT_TAB;
860 l_loose_items_ids WSH_OTM_ID_TAB;
861 l_dlv_obj wsh_otm_dlv_obj;
862 l_det_obj wsh_otm_det_obj;
863 l_weight NUMBER;
864 l_dlv_id_tab WSH_OTM_ID_TAB;
865 l_error_dlv_id_tab WSH_OTM_ID_TAB;
866
867
868 c_deliveries WSH_UTIL_CORE.RefCurType;
869 bind_col_tab WSH_UTIL_CORE.tbl_varchar;
870 otm_uom_tab WSH_UTIL_CORE.tbl_varchar;
871 l_deliveries_sql VARCHAR2(2000);
872 l_count NUMBER;
873 l_customer_id NUMBER;
874 l_cnt NUMBER;
875 i NUMBER;
876 l NUMBER;
877 x NUMBER;
878 l_total_quantity NUMBER;
879 l_cont_type VARCHAR2(30);
880 l_length NUMBER;
881 l_height NUMBER;
882 l_width NUMBER;
883 l_uom VARCHAR2(150);
884 l_organization_code VARCHAR2(30);
885 l_internal_org_location_id VARCHAR2(150);
886 l_dropoff_location_id NUMBER;
887 l_return_status VARCHAR2(10);
888 position NUMBER;
889 l_found BOOLEAN;
890 l_quantity NUMBER;
891 l_delivery_id NUMBER;
892 l_inventory_item VARCHAR2(30);
893 l_otm_dimen_uom VARCHAR2(150);
894 l_cust_type VARCHAR2(5) := 'CUS-';
895 l_org_type VARCHAR2(5) := 'ORG-';
896 l_car_type VARCHAR2(5) := 'CAR-';
897 l_organization_id NUMBER;
898 x_base_wt_uom VARCHAR2(150);
899 x_base_vol_uom VARCHAR2(150);
900
901 CONVERT_INT_LOC_FALIED EXCEPTION;
902 GET_DEAFULT_UOMS_FAILED EXCEPTION;
903
904 TYPE All_LPNS is TABLE of wsh_otm_lpn_obj INDEX BY BINARY_INTEGER;
905 l_all_lpn_tab All_LPNS;
906 l_dummy_lpn_tab All_LPNS;
907
908 TYPE all_lpn_rec_type IS RECORD(
909 lpn_id NUMBER,
910 lpn_type VARCHAR2(100),
911 gross_weight NUMBER,
912 net_weight NUMBER,
913 weight_uom_code varchar2(150),
914 volume_uom_code varchar2(150),
915 seal_code varchar2(30),
916 packed_items WSH_OTM_LPN_CONT_TAB,
917 parent_delivery_detail_id NUMBER);
918
919 -- Bug 7207835
920 l_p_caller VARCHAR2(10);
921 -- 6922924
922 l_good_dlv_tab wsh_otm_dlv_tab;
923 l_upd_tms_interface_flags WSH_UTIL_CORE.COLUMN_TAB_TYPE;
924 l_upd_err_dlvys WSH_UTIL_CORE.ID_TAB_TYPE;
925
926
927 CURSOR get_customer_id (p_delivery_id NUMBER) IS
928 SELECT wdd.customer_id, count(*) cnt
929 FROM wsh_delivery_assignments wda,
930 wsh_delivery_details wdd
931 WHERE wdd.delivery_detail_id = wda.delivery_detail_id
932 AND wda.delivery_id = p_delivery_id
933 AND wdd.container_flag = 'N'
934 GROUP BY customer_id
935 ORDER BY cnt DESC;
936
937 CURSOR get_organization_code( p_location_id NUMBER) IS
938 SELECT organization_code
939 FROM mtl_parameters mp, hr_organization_units hou
940 WHERE mp.organization_id = hou.organization_id
941 AND hou.location_id = p_location_id;
942
943
944 -- Bug#5746380 Added p_organization_id in the Cursor.
945 CURSOR get_container_details ( p_inventory_item_id NUMBER, p_organization_id NUMBER) IS
946 SELECT CONTAINER_TYPE_CODE,
947 UNIT_LENGTH ,
948 UNIT_HEIGHT ,
949 UNIT_WIDTH ,
950 DIMENSION_UOM_CODE
951 FROM mtl_system_items
952 WHERE inventory_item_id = p_inventory_item_id
953 AND organization_id = p_organization_id;
954
955 --6922924 : (begin) : Update tms_interface_flag for error Deliveries
956 CURSOR get_errored_dlvys(c_dlvy_id NUMBER) IS
957 SELECT delivery_id, decode(tms_interface_flag, 'CP', 'CR', 'UP', 'UR', 'DP',
958 'DR', tms_interface_flag ) tms_iface_new_status
959 FROM wsh_new_deliveries
960 WHERE delivery_id = c_dlvy_id
961 FOR UPDATE OF tms_interface_flag NOWAIT;
962
963 begin
964 --{
965 -- Initialize API return status to success
966
967 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
968
969 -- Debug
970 --
971 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
972 --
973 IF l_debug_on IS NULL
974 THEN
975 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
976 END IF;
977 --
978 -- Debug Statements
979 --
980 IF l_debug_on THEN
981 WSH_DEBUG_SV.push(l_sub_module_name);
982 END IF;
983
984 IF l_debug_on THEN
985 FOR i in 1..p_dlv_id_tab.COUNT LOOP
986 WSH_DEBUG_SV.log(l_sub_module_name,'Dlv Id : ' , p_dlv_id_tab(i));
987 END LOOP;
988 WSH_DEBUG_SV.log(l_sub_module_name,'p_user_id' , p_user_id);
989 WSH_DEBUG_SV.log(l_sub_module_name,'p_resp_id' , p_resp_id);
990 WSH_DEBUG_SV.log(l_sub_module_name,'p_resp_appl_id' , p_resp_appl_id);
991 WSH_DEBUG_SV.log(l_sub_module_name,'p_caller' , p_caller);
992 END IF ;
993 -- Bug 7207835 p_caller value not defaulting to 'D' when passed as NULL.
994 l_p_caller := NVL(p_caller,'D');
995 -- End Bug 7207835
996 -- Setting the apps context
997 FND_GLOBAL.APPS_INITIALIZE(user_id => p_user_id,
998 resp_id =>p_resp_id,
999 resp_appl_id => p_resp_appl_id);
1000
1001
1002 -- Getting the profile values
1003 fnd_profile.get('WSH_OTM_DOMAIN_NAME',x_domain_name);
1004 fnd_profile.get('WSH_OTM_USER_ID',x_otm_user_name);
1005 fnd_profile.get('WSH_OTM_PASSWORD',x_otm_pwd);
1006 x_server_tz_code := FND_TIMEZONES.get_server_timezone_code();
1007
1008 IF l_debug_on THEN
1009 WSH_DEBUG_SV.log(l_sub_module_name,'Building the dymanic sql to fetch Deliveries');
1010 END IF;
1011
1012 l_deliveries_sql :=
1013 'select '
1014 ||'wsh_otm_dlv_obj( '
1015 ||'decode(wnd.tms_interface_flag,''CP'',''RC'',''UP'',''RC'',''DP'',''D''),'
1016 --||'''RC'','
1017 ||'wnd.delivery_id,'
1018 ||'wnd.name,'
1019 ||'wnd.freight_terms_code,'
1020 ||'wnd.fob_code,'
1021 ||'wnd.carrier_id,'
1022 ||'wnd.service_level,'
1023 ||'wnd.mode_of_transport,'
1024 ||'wnd.organization_id||''-''|| wnd.INITIAL_PICKUP_LOCATION_ID,'
1025 ||'wnd.customer_id ||''-''||wnd.ULTIMATE_DROPOFF_LOCATION_ID,'
1026 ||'wnd.EARLIEST_PICKUP_DATE,'
1027 ||'wnd.LATEST_PICKUP_DATE,'
1028 ||'wnd.EARLIEST_DROPOFF_DATE,'
1029 ||'wnd.LATEST_DROPOFF_DATE,'
1030 ||'nvl(wnd.GROSS_WEIGHT,0),'
1031 ||'wnd.WEIGHT_UOM_CODE,'
1032 ||'nvl(wnd.VOLUME,0),'
1033 ||'wnd.VOLUME_UOM_CODE,'
1034 ||'nvl(wnd.NET_WEIGHT,0),'
1035 ||'wnd.TMS_VERSION_NUMBER,' -- revision number
1036 ||'wnd.REASON_OF_TRANSPORT,'
1037 ||'wnd.DESCRIPTION,'
1038 ||'wnd.ADDITIONAL_SHIPMENT_INFO,'
1039 ||'wnd.ROUTING_INSTRUCTIONS,'
1040 ||'null,''REVNUM'',''TRSP_REASON'',''DEL_DESCRIPTION'',''ADD_INFOS'',''ROUTING_INSTR'',null,null),'
1041 ||' wsh_otm_det_obj(wdd.delivery_detail_id,'
1042 ||'wdd.lot_number,'
1043 ||'wdd.serial_number,'
1044 ||'wdd.to_serial_number,'
1045 ||'nvl(wdd.GROSS_WEIGHT,0),'
1046 ||'wdd.WEIGHT_UOM_CODE ,'
1047 ||'nvl(wdd.VOLUME,0) ,'
1048 ||'wdd.VOLUME_UOM_CODE,'
1049 ||'wdd.REQUESTED_QUANTITY,'
1050 ||'wdd.SHIPPED_QUANTITY,'
1051 ||'wdd.organization_id || ''-'' || wdd.INVENTORY_ITEM_ID,'
1052 ||'wdd.container_flag,'
1053 ||'wda.parent_delivery_detail_id,'
1054 ||'wdd.cust_po_number,'
1055 ||'wdd.source_header_number,''CUST_PO'',''SO_NUM'', wda.delivery_id,nvl(wdd.NET_WEIGHT,0))'
1056 ||' from wsh_new_deliveries wnd, wsh_delivery_details wdd , wsh_delivery_assignments wda'
1057 ||' where wdd.delivery_detail_id(+) = wda.delivery_detail_id '
1058 ||' and wnd.delivery_id = wda.delivery_id(+) '
1059 ||' and wnd.delivery_id in (';
1060 --Bug 7408338
1061 IF (l_p_caller = 'A') THEN
1062 --{
1063 l_deliveries_sql := l_deliveries_sql ||' select wdl.delivery_id from wsh_delivery_legs wdl, wsh_trip_stops wts where wdl.pick_up_stop_id = wts.stop_id and wts.trip_id = :1 ' ;
1064 bind_col_tab(bind_col_tab.COUNT+1) := to_char(p_trip_id);
1065 ELSE
1066 FOR i in 1..p_dlv_id_tab.COUNT LOOP
1067 --{
1068 IF i <> 1 THEN
1069 l_deliveries_sql := l_deliveries_sql || ',';
1070 END IF;
1071 l_deliveries_sql := l_deliveries_sql || ':' || i;
1072 bind_col_tab(bind_col_tab.COUNT+1) := to_char(p_dlv_id_tab(i));
1073 --}
1074 END LOOP;
1075 --}
1076 END IF;
1077
1078 l_deliveries_sql := l_deliveries_sql || ')';
1079 IF l_p_caller <> 'A' THEN
1080 l_deliveries_sql := l_deliveries_sql || ' and wnd.tms_interface_flag in (''CP'',''DP'',''UP'') ';
1081 END IF;
1082 --bug # 7150082: Deliveries need to be passed in the order of delete,update and create to OTM
1083 l_deliveries_sql := l_deliveries_sql || 'order by decode(wnd.tms_interface_flag,''CP'',''3'',''UP'',''2'',''DP'',''1''),wda.delivery_id, wdd.container_flag desc';
1084
1085 i:=1;
1086
1087 WSH_UTIL_CORE.OpenDynamicCursor(c_deliveries, l_deliveries_sql, bind_col_tab);
1088 l_count := 1;
1089 l_dlv_tab := wsh_otm_dlv_tab();
1090 --l_all_details - contains the delivery details of all the deliveries queried.
1091 l_all_details := wsh_otm_det_tab();
1092 -- 6922924
1093 l_good_dlv_tab := wsh_otm_dlv_tab();
1094
1095 --l_dlv_obj := wsh_otm_dlv_obj();
1096 l_dlv_id_tab := WSH_OTM_ID_TAB();
1097 LOOP
1098 --{
1099 FETCH c_deliveries INTO l_dlv_obj,l_det_obj;
1100 EXIT WHEN (c_deliveries%NOTFOUND);
1101 IF ( l_dlv_tab.COUNT = 0 OR l_dlv_tab(l_dlv_tab.COUNT).delivery_id <> l_dlv_obj.delivery_id) THEN
1102 l_dlv_tab.extend;
1103 l_dlv_tab(l_dlv_tab.COUNT) := l_dlv_obj;
1104 END IF;
1105 l_all_details.extend;
1106 l_all_details(l_all_details.COUNT) := l_det_obj;
1107 l_count := l_count+1;
1108 --}
1109 END LOOP;
1110
1111 IF l_debug_on THEN
1112 WSH_DEBUG_SV.log(l_sub_module_name,'count of deliveries ' , l_dlv_tab.COUNT);
1113 END IF;
1114
1115
1116 IF l_dlv_tab.COUNT >0 THEN
1117 --{ when l_dlv_tab count > 0
1118
1119 l_error_dlv_id_tab := WSH_OTM_ID_TAB();
1120 FOR i in 1..l_dlv_tab.COUNT LOOP
1121 --{ -- 6922924 Loop Start
1122 BEGIN
1123
1124 l_delivery_id := l_dlv_tab(i).delivery_id;
1125
1126 IF l_p_caller = 'A' THEN
1127 l_dlv_tab(i).transaction_code := 'RC';
1128 END IF;
1129
1130
1131 IF l_dlv_tab(i).CARRIER_ID IS NOT NULL THEN
1132 l_dlv_tab(i).CARRIER_ID := l_car_type || l_dlv_tab(i).CARRIER_ID;
1133 END IF;
1134
1135 -- Getting the organization_id
1136 position := instr(l_dlv_tab(i).INITIAL_PICKUP_LOCATION_ID,'-');
1137 l_organization_id := substr(l_dlv_tab(i).INITIAL_PICKUP_LOCATION_ID,1, position-1);
1138
1139
1140 IF l_dlv_tab(i).INITIAL_PICKUP_LOCATION_ID IS NOT NULL THEN
1141 l_dlv_tab(i).INITIAL_PICKUP_LOCATION_ID := l_org_type || l_dlv_tab(i).INITIAL_PICKUP_LOCATION_ID;
1142 END IF;
1143
1144 -- Populating the ULTIMATE_DROPOFF_LOCATION_ID based on whether it is internal or external location.
1145 position := instr(l_dlv_tab(i).ULTIMATE_DROPOFF_LOCATION_ID,'-');
1146 l_customer_id := substr(l_dlv_tab(i).ULTIMATE_DROPOFF_LOCATION_ID,1, position-1);
1147 l_dropoff_location_id := substr(l_dlv_tab(i).ULTIMATE_DROPOFF_LOCATION_ID,position+1);
1148
1149 WSH_OTM_REF_DATA_GEN_PKG.GET_INT_LOCATION_XID(
1150 p_location_id => l_dropoff_location_id,
1151 x_location_xid => l_internal_org_location_id,
1152 x_return_status =>l_return_status);
1153
1154 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS then
1155 raise CONVERT_INT_LOC_FALIED;
1156 END IF;
1157 IF l_internal_org_location_id is NOT NULL THEN
1158 l_dlv_tab(i).ULTIMATE_DROPOFF_LOCATION_ID := l_internal_org_location_id;
1159 ELSE
1160 --{
1161 -- If customer_id is null in wsh_new_deliveries
1162 IF l_customer_id is NULL THEN
1163 open get_customer_id(l_dlv_tab(i).delivery_id);
1164 fetch get_customer_id into l_customer_id,l_cnt;
1165 close get_customer_id;
1166 END IF;
1167 l_dlv_tab(i).ULTIMATE_DROPOFF_LOCATION_ID := l_cust_type
1168 || l_customer_id || '-'
1169 || l_dropoff_location_id;
1170 --}
1171 END IF;
1172
1173 WSH_WV_UTILS.GET_DEFAULT_UOMS(l_organization_id, x_base_wt_uom, x_base_vol_uom, x_return_status);
1174 IF x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS then
1175 raise GET_DEAFULT_UOMS_FAILED;
1176 END IF;
1177
1178 IF l_dlv_tab(i).WEIGHT_UOM_CODE is null THEN
1179 l_dlv_tab(i).WEIGHT_UOM_CODE := x_base_wt_uom;
1180 END IF;
1181 IF l_dlv_tab(i).VOLUME_UOM_CODE is null THEN
1182 l_dlv_tab(i).VOLUME_UOM_CODE := x_base_vol_uom;
1183 END IF;
1184
1185 WSH_OTM_RIQ_XML.Get_EBS_To_OTM_UOM(
1186 p_uom=>l_dlv_tab(i).WEIGHT_UOM_CODE,
1187 x_uom=>l_dlv_tab(i).WEIGHT_UOM_CODE,
1188 x_return_status=>l_return_status);
1189 IF((l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) AND
1190 (l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING)) THEN
1191 IF l_debug_on
1192 THEN
1193 WSH_DEBUG_SV.log(l_sub_module_name,'Get_EBS_To_OTM_UOM Weight Failed');
1194 END IF;
1195 raise FND_API.G_EXC_ERROR;
1196 END IF;
1197
1198 WSH_OTM_RIQ_XML.Get_EBS_To_OTM_UOM(
1199 p_uom=>l_dlv_tab(i).VOLUME_UOM_CODE,
1200 x_uom=>l_dlv_tab(i).VOLUME_UOM_CODE,
1201 x_return_status=>l_return_status);
1202 IF((l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) AND
1203 (l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING)) THEN
1204 IF l_debug_on
1205 THEN
1206 WSH_DEBUG_SV.log(l_sub_module_name,'Get_EBS_To_OTM_UOM Weight Failed');
1207 END IF;
1208 raise FND_API.G_EXC_ERROR;
1209 END IF;
1210
1211
1212
1213
1214
1215 --l_del_details - contains the delivery details of the delivery l_dlv_tab(i).delivery_id
1216
1217 l_del_details := wsh_otm_det_tab();
1218 get_del_details(l_all_details,l_dlv_tab(i).delivery_id,l_del_details);
1219
1220
1221
1222
1223 l_rl_details := wsh_otm_det_tab();
1224 l_lpn_tab := wsh_otm_lpn_tab();
1225 l_all_lpn_tab := l_dummy_lpn_tab;
1226 l_total_quantity := 0;
1227
1228 -- Loop through l_del_details to create the following
1229 -- l_rl_details -- Basically all the release lines of this delivery
1230 -- l_loose_items -- All the loose items in l_del_details
1231 -- lpn_tab -- All the Outmost lpns in l_del_details + Lpns for Loose Items
1232 -- l_all_lpn_tab -- All the lpns in l_del_details
1233 FOR i in 1..l_del_details.COUNT LOOP
1234 --{
1235 IF l_del_details(i).WEIGHT_UOM_CODE is null THEN
1236 l_del_details(i).WEIGHT_UOM_CODE := x_base_wt_uom;
1237 END IF;
1238 IF l_del_details(i).VOLUME_UOM_CODE is null THEN
1239 l_del_details(i).VOLUME_UOM_CODE := x_base_vol_uom;
1240 END IF;
1241
1242
1243 WSH_OTM_RIQ_XML.Get_EBS_To_OTM_UOM(
1244 p_uom=>l_del_details(i).WEIGHT_UOM_CODE,
1245 x_uom=>l_del_details(i).WEIGHT_UOM_CODE,
1246 x_return_status=>l_return_status);
1247 IF((l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) AND
1248 (l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING)) THEN
1249 IF l_debug_on
1250 THEN
1251 WSH_DEBUG_SV.log(l_sub_module_name,'Get_EBS_To_OTM_UOM Weight Failed');
1252 END IF;
1253 raise FND_API.G_EXC_ERROR;
1254 END IF;
1255
1256 WSH_OTM_RIQ_XML.Get_EBS_To_OTM_UOM(
1257 p_uom=>l_del_details(i).VOLUME_UOM_CODE,
1258 x_uom=>l_del_details(i).VOLUME_UOM_CODE,
1259 x_return_status=>l_return_status);
1260 IF((l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) AND
1261 (l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING)) THEN
1262 IF l_debug_on
1263 THEN
1264 WSH_DEBUG_SV.log(l_sub_module_name,'Get_EBS_To_OTM_UOM Weight Failed');
1265 END IF;
1266 raise FND_API.G_EXC_ERROR;
1267 END IF;
1268
1269
1270
1271
1272
1273 IF l_del_details(i).container_flag = 'N'THEN
1274 --{
1275 l_rl_details.extend;
1276 l_rl_details(l_rl_details.COUNT ) := l_del_details(i);
1277 IF l_p_caller = 'A' THEN
1278 l_quantity := l_del_details(i).SHIPPED_QUANTITY;
1279 ELSE
1280 l_quantity := l_del_details(i).REQUESTED_QUANTITY;
1281 END IF;
1282 l_total_quantity := l_total_quantity + l_quantity;
1283
1284 IF l_del_details(i).parent_delivery_detail_id is null THEN
1285 --{
1286
1287 l_packed_items := WSH_OTM_LPN_CONT_TAB();
1288 l_packed_items.extend;
1289 l_packed_items(l_packed_items.COUNT) := WSH_OTM_LPN_CONT_OBJ(l_del_details(i).delivery_detail_id,1,
1290 l_del_details(i).inventory_item_id,
1291 CEIL(l_quantity),
1292 l_del_details(i).delivery_detail_id,
1293 l_delivery_id,
1294 l_del_details(i).gross_weight,
1295 l_del_details(i).net_weight,
1296 l_del_details(i).weight_uom_code,
1297 l_del_details(i).volume,
1298 l_del_details(i).volume_uom_code
1299 );
1300 -- Creating a dummy lpn for the loose item
1301 l_lpn_tab.extend;
1302
1303 -- Getting Item Dimensions for Loose Item
1304 l_inventory_item := l_del_details(i).inventory_item_id;
1305 --Bug 5746380
1306 open get_container_details(substr(l_inventory_item,instr(l_inventory_item,'-') + 1),
1307 l_organization_id);
1308 fetch get_container_details into l_cont_type, l_length, l_height, l_width, l_uom;
1309 close get_container_details;
1310 IF l_debug_on THEN
1311 WSH_DEBUG_SV.log(l_sub_module_name,'Calling Get_EBS_To_OTM_UOM Weight for Loose Item : '||substr(l_inventory_item,instr(l_inventory_item,'-') + 1));
1312 END IF;
1313 WSH_OTM_RIQ_XML.Get_EBS_To_OTM_UOM(
1314 p_uom=>l_uom,
1315 x_uom=>l_otm_dimen_uom ,
1316 x_return_status=>l_return_status);
1317 IF((l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) AND
1318 (l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING)) THEN
1319 IF l_debug_on
1320 THEN
1321 WSH_DEBUG_SV.log(l_sub_module_name,'Get_EBS_To_OTM_UOM Weight Failed for Loose Item');
1322 END IF;
1323 raise FND_API.G_EXC_ERROR;
1324 END IF;
1325
1326 l_lpn_tab(l_lpn_tab.COUNT) := wsh_otm_lpn_obj(l_del_details(i).delivery_detail_id,null,l_del_details(i).gross_weight,l_del_details(i).net_weight,
1327 l_del_details(i).weight_uom_code,l_del_details(i).volume_uom_code,
1328 null,l_packed_items,null,
1329 null,l_length,l_height,l_width,l_otm_dimen_uom,l_del_details(i).volume,null);
1330 --}
1331 ELSE
1332 --{
1333 x := l_del_details(i).parent_delivery_detail_id;
1334 LOOP
1335 --{
1336 IF l_all_lpn_tab(x).parent_delivery_detail_id is null then
1337 l_all_lpn_tab(x).packed_items.extend;
1338 l_all_lpn_tab(x).packed_items(l_all_lpn_tab(x).packed_items.COUNT)
1339 := WSH_OTM_LPN_CONT_OBJ(
1340 l_del_details(i).delivery_detail_id,
1341 l_all_lpn_tab(x).packed_items.COUNT,
1342 l_del_details(i).inventory_item_id,
1343 CEIL(l_quantity),
1344 l_all_lpn_tab(x).lpn_id,
1345 l_delivery_id,
1346 l_del_details(i).gross_weight,
1347 l_del_details(i).net_weight,
1348 l_del_details(i).weight_uom_code,
1349 l_del_details(i).volume,
1350 l_del_details(i).volume_uom_code
1351 );
1352 exit;
1353 ELSE
1354 x:= l_all_lpn_tab(x).parent_delivery_detail_id;
1355 END IF;
1356 --}
1357 END LOOP;
1358 --}
1359 END IF;
1360 --}
1361 ELSE
1362 --{
1363 IF l_del_details(i).parent_delivery_detail_id is null THEN
1364 l_inventory_item := l_del_details(i).inventory_item_id;
1365 --Bug 5746380
1366 open get_container_details(substr(l_inventory_item,instr(l_inventory_item,'-') + 1),
1367 l_organization_id);
1368 fetch get_container_details into l_cont_type, l_length, l_height, l_width, l_uom;
1369 close get_container_details;
1370 END IF;
1371
1372 WSH_OTM_RIQ_XML.Get_EBS_To_OTM_UOM(
1373 p_uom=>l_uom,
1374 x_uom=>l_otm_dimen_uom ,
1375 x_return_status=>l_return_status);
1376 IF((l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) AND
1377 (l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING)) THEN
1378 IF l_debug_on
1379 THEN
1380 WSH_DEBUG_SV.log(l_sub_module_name,'Get_EBS_To_OTM_UOM Weight Failed');
1381 END IF;
1382 raise FND_API.G_EXC_ERROR;
1383 END IF;
1384
1385
1386 l_all_lpn_tab(l_del_details(i).delivery_detail_id) :=
1387 wsh_otm_lpn_obj(l_del_details(i).delivery_detail_id,
1388 null,
1389 l_del_details(i).gross_weight,
1390 l_del_details(i).net_weight,
1391 l_del_details(i).weight_uom_code,
1392 l_del_details(i).volume_uom_code,
1393 null,
1394 null,
1395 l_del_details(i).parent_delivery_detail_id,
1396 l_cont_type, l_length, l_height, l_width, l_otm_dimen_uom,l_del_details(i).volume,null);
1397 l_all_lpn_tab(l_del_details(i).delivery_detail_id).packed_items := WSH_OTM_LPN_CONT_TAB();
1398 --}
1399 END IF;
1400 --}
1401 END LOOP;
1402
1403 -- ECO 5768287
1404 -- Populating the lpn_tab with only non-empty outermost Lpns in l_all_lpn_tab
1405
1406 IF l_all_lpn_tab.COUNT >0 THEN
1407 --{
1408 l := l_all_lpn_tab.FIRST;
1409 WHILE l is not NULL
1410 LOOP
1411 --{
1412 IF l_all_lpn_tab(l).parent_delivery_detail_id is null AND l_all_lpn_tab(l).packed_items.count > 0 THEN
1413 l_lpn_tab.EXTEND;
1414 l_lpn_tab(l_lpn_tab.COUNT) := l_all_lpn_tab(l);
1415
1416 END IF;
1417 l := l_all_lpn_tab.NEXT(l);
1418 --}
1419 END LOOP;
1420 --}
1421 END IF;
1422
1423 l_dlv_tab(i).lpn := l_lpn_tab;
1424 l_dlv_tab(i).rl_details := l_rl_details;
1425 l_dlv_tab(i).TOTAL_ITEM_COUNT := CEIL(l_total_quantity);
1426
1427 -- 6922924
1428 l_dlv_id_tab.extend;
1429 l_dlv_id_tab(l_dlv_id_tab.COUNT) := l_dlv_tab(i).delivery_id;
1430 l_good_dlv_tab.extend;
1431 l_good_dlv_tab(l_good_dlv_tab.COUNT) := l_dlv_tab(i);
1432 --
1433 EXCEPTION
1434 WHEN GET_DEAFULT_UOMS_FAILED THEN
1435 -- 6830854
1436 IF l_debug_on
1437 THEN
1438 WSH_DEBUG_SV.log(l_sub_module_name,'GET_DEAFULT_UOMS_FALIED',sqlerrm);
1439 WSH_DEBUG_SV.log(l_sub_module_name,'Delivery id: '||l_delivery_id);
1440 END IF;
1441
1442 WHEN CONVERT_INT_LOC_FALIED THEN
1443 -- 6830854
1444 IF l_debug_on
1445 THEN
1446 WSH_DEBUG_SV.log(l_sub_module_name,'CONVERT_INT_LOC_FALIED',sqlerrm);
1447 WSH_DEBUG_SV.log(l_sub_module_name,'Delivery id: '||l_delivery_id);
1448 END IF;
1449
1450 WHEN FND_API.G_EXC_ERROR THEN
1451 -- 6830854
1452 IF l_debug_on
1453 THEN
1454 WSH_DEBUG_SV.log(l_sub_module_name,' FND_API.G_EXC_ERROR',sqlerrm);
1455 WSH_DEBUG_SV.log(l_sub_module_name,'Delivery id: '||l_delivery_id);
1456 END IF;
1457
1458 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1459 -- 6830854
1460 IF l_debug_on
1461 THEN
1462 WSH_DEBUG_SV.log(l_sub_module_name,'FND_API.G_EXC_UNEXPECTED_ERROR',sqlerrm);
1463 WSH_DEBUG_SV.log(l_sub_module_name,'Delivery id: '||l_delivery_id);
1464 END IF;
1465
1466 WHEN OTHERS THEN
1467 wsh_util_core.default_handler('WSH_OTM_OUTBOUND.GET_DELIVERY_OBJECTS',l_sub_module_name);
1468 -- 6922924 Note : Removed all ref: to x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1469 -- in this Exception and above Exceptions
1470 IF l_debug_on
1471 THEN
1472 WSH_DEBUG_SV.log(l_sub_module_name,'WHEN OTHERS inside Get Delivery Loop',sqlerrm);
1473 WSH_DEBUG_SV.log(l_sub_module_name,'Delivery id: '||l_delivery_id);
1474 END IF;
1475 END;
1476
1477 -- 6922924
1478 IF get_customer_id%ISOPEN THEN
1479 CLOSE get_customer_id;
1480 END IF;
1481 IF get_container_details%ISOPEN THEN
1482 CLOSE get_container_details;
1483 END IF;
1484 --} --- 6922924 End Loop
1485 END LOOP;
1486
1487 -- If all the deliveries in the input parameter p_dlv_id_tab where not queried
1488 -- Put those id's in the error_dlv_id_list
1489 IF l_dlv_id_tab.COUNT <> p_dlv_id_tab.COUNT THEN
1490 --{
1491 FOR i in 1..p_dlv_id_tab.COUNT LOOP
1492 --{
1493 l_found := FALSE;
1494 FOR j in 1..l_dlv_id_tab.COUNT LOOP
1495 --{
1496 IF l_dlv_id_tab(j) = p_dlv_id_tab(i) THEN
1497 l_found := TRUE;
1498 EXIT;
1499 END IF;
1500 IF l_dlv_id_tab(j) > p_dlv_id_tab(i) THEN
1501 EXIT;
1502 END IF;
1503 --}
1504 END LOOP;
1505 IF l_found = FALSE THEN
1506 l_error_dlv_id_tab.extend;
1507 l_error_dlv_id_tab(l_error_dlv_id_tab.COUNT) := p_dlv_id_tab(i);
1508 IF l_debug_on THEN
1509 WSH_DEBUG_SV.log(l_sub_module_name,'Errored Delivery id: ' , p_dlv_id_tab(i) );
1510 WSH_DEBUG_SV.log(l_sub_module_name,'Count of i' , i);
1511 END IF;
1512 END IF;
1513 --}
1514 END LOOP;
1515 --}
1516 END IF;
1517
1518 -- 6922924
1519 x_dlv_tab := l_good_dlv_tab;
1520 p_dlv_id_tab := l_dlv_id_tab;
1521 x_error_dlv_id_tab := l_error_dlv_id_tab;
1522
1523
1524 --6922924
1525 -- Update the tms_interface_flag to 'CR' and Print the List of Errored Delivery Ids
1526 IF ( x_error_dlv_id_tab.count > 0 ) THEN
1527 IF l_debug_on THEN
1528 WSH_DEBUG_SV.log(l_sub_module_name,'ERROR DELIVERY ids Count',x_error_dlv_id_tab.count);
1529 END IF;
1530 --{
1531 i := x_error_dlv_id_tab.first;
1532 WHILE (i is not NULL) LOOP
1533 --{
1534 OPEN get_errored_dlvys(x_error_dlv_id_tab(i));
1535 FETCH get_errored_dlvys INTO l_upd_err_dlvys(1), l_upd_tms_interface_flags(1);
1536 CLOSE get_errored_dlvys;
1537 IF l_upd_err_dlvys.COUNT > 0 THEN
1538 --{
1539 IF l_debug_on THEN
1540 WSH_DEBUG_SV.log(l_sub_module_name,'Calling WSH_NEW_DELIVERIES_PVT.UPDATE_TMS_INTERFACE_FLAG');
1541 END IF;
1542 --
1543 WSH_NEW_DELIVERIES_PVT.Update_Tms_interface_flag
1544 ( p_delivery_id_tab => l_upd_err_dlvys,
1545 p_tms_interface_flag_tab => l_upd_tms_interface_flags,
1546 x_return_status => l_return_status );
1547 --
1548 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS then
1549 IF l_debug_on THEN
1550 WSH_DEBUG_SV.log(l_sub_module_name,'failed in WND_PVT.UPDATE_TMS_INTERFACE_FLAG');
1551 END IF;
1552 -- 6830854 raise FND_API.G_EXC_ERROR;
1553 -- removing the above as Error can occur in Validation again
1554 END IF;
1555 --}
1556 END IF;
1557 i := x_error_dlv_id_tab.NEXT(i);
1558 --}
1559 END LOOP;
1560 -- }
1561 END IF;
1562
1563
1564 --------- Printing the complete structure------------
1565 IF l_debug_on THEN
1566 --{
1567 FOR k in 1..x_dlv_tab.COUNT LOOP
1568 --{
1569 WSH_DEBUG_SV.log(l_sub_module_name,'dlv_id' , x_dlv_tab(k).delivery_id);
1570 FOR i in 1..x_dlv_tab(k).rl_details.COUNT LOOP
1571 WSH_DEBUG_SV.log(l_sub_module_name,'DD ' , x_dlv_tab(k).rl_details(i).delivery_detail_id);
1572 END LOOP;
1573 WSH_DEBUG_SV.log(l_sub_module_name,'Ship Units Count' , x_dlv_tab(k).lpn.COUNT);
1574 FOR i in 1..x_dlv_tab(k).lpn.COUNT LOOP
1575 --{
1576 WSH_DEBUG_SV.log(l_sub_module_name,'Lpn Id ' , x_dlv_tab(k).lpn(i).lpn_id);
1577 FOR j in 1..x_dlv_tab(k).lpn(i).packed_items.COUNT LOOP
1578 WSH_DEBUG_SV.log(l_sub_module_name,'Content ' ,x_dlv_tab(k).lpn(i).packed_items(j).content_id);
1579 WSH_DEBUG_SV.log(l_sub_module_name,'Content ' ,x_dlv_tab(k).lpn(i).packed_items(j).line_number);
1580 END LOOP;
1581 --}
1582 END LOOP;
1583 --}
1584 END LOOP;
1585 --}
1586 END IF;
1587 --------- End of Printing the complete structure------------
1588 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1589 --
1590 ELSE
1591 IF l_debug_on THEN
1592 WSH_DEBUG_SV.log(l_sub_module_name,'Else error');
1593 END IF;
1594 x_error_dlv_id_tab := p_dlv_id_tab;
1595 p_dlv_id_tab := l_dlv_id_tab;
1596 x_dlv_tab := l_dlv_tab;
1597 x_return_status := FND_API.G_RET_STS_ERROR;
1598 END IF;
1599 -- } Main IF - when l_dlv_tab count > 0
1600 --
1601 -- Debug Statements
1602 --
1603 IF l_debug_on THEN
1604 WSH_DEBUG_SV.pop(l_sub_module_name);
1605 END IF;
1606 --
1607 EXCEPTION
1608 -- 6922924 removeing all Exceptions to Inside the Loop as in above
1609 WHEN OTHERS THEN
1610 wsh_util_core.default_handler('WSH_OTM_OUTBOUND.GET_DELIVERY_OBJECTS',l_sub_module_name);
1611 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1612 IF get_customer_id%ISOPEN THEN
1613 CLOSE get_customer_id;
1614 END IF;
1615 IF get_container_details%ISOPEN THEN
1616 CLOSE get_container_details;
1617 END IF;
1618 IF l_debug_on THEN
1619 WSH_DEBUG_SV.pop(l_sub_module_name,'When Others');
1620 END IF;
1621 end GET_DELIVERY_OBJECTS;
1622
1623
1624
1625 -- +======================================================================+
1626 -- Procedure : UPDATE_ENTITY_INTF_STATUS
1627 -- Description:
1628 -- This procedure will be used to upate the interface flag status on the delivery or trip stop.
1629 --
1630 -- Inputs/Outputs:
1631 -- p_entity_type - Valid values are "DELIVERY", "TRIP"
1632 -- p_entity_id_tab - id table (IN / OUT) -- List of Delivery id or Trip id
1633 -- p_new_intf_status - Delivery or Trip Stop Status
1634 -- Valid values of this parameter are "IN_PROCESS", "COMPLETE"
1635 --
1636 -- Trip Stop Interface Flag values (internal):
1637 -- ASR - ACTUAL_SHIP_REQUIRED
1638 -- ASP - ACTUAL_IN_PROCESS
1639 -- CMP - COMPLETE
1640 --
1641 -- Delivery Interface Flag values (internal):
1642 -- NS - NOT TO BE SENT
1643 -- CR - CREATE_REQUIRED
1644 -- UR - UPDATE_REQUIRED
1645 -- DR - DELETE_REQUIRED
1646 -- CP - CREATE_IN_PROCESS
1647 -- UP- UPDATE_IN_PROCESS
1648 -- DP - DELETE_IN_PROCESS
1649 -- AW - AWAITING_ANSWER
1650 -- AR - ANSWER_RECEIVED
1651 -- CMP - COMPLETE
1652 -- p_user_Id - user id ( application user id )
1653 -- p_resp_Id - responsibility id
1654 -- p_resp_appl_Id - resp application id ( Application Responsibility Id)
1655 -- Output:
1656 -- p_error_id_tab - erred entity id table -- list of ERRORed delivery id or tripd id
1657 -- p_entity_id_tab - id table (IN / OUT) - list of SUCCESS delivery id or trip id
1658 -- x_return_status - "S"-Success, "E"-Error, "U"-Unexpected Error
1659 -- API is called from the following phases / API
1660 /*
1661 1.Concurrent Request --TripStop and Delivery TMS_INTERFACE_FLAG is updated to newStatus = X_IN_PROCESS.
1662 2.WSH_GLOG_OUTBOUND.GET_TRIP_OBJECTS - TripStop and Delivery TMS_INTERFACE_FLAG is updated to newStatus = AWAITING_ANSWER.
1663 3.WSH_GLOG_OUTBOUND.GET_DELIVERY_OBJECTS - TripStop and Delivery TMS_INTERFACE_FLAG is updated to newStatus = AWAITING_ANSWER.
1664 */
1665
1666
1667
1668 -- +======================================================================+
1669 PROCEDURE UPDATE_ENTITY_INTF_STATUS(
1670 x_return_status OUT NOCOPY VARCHAR2,
1671 p_entity_type IN VARCHAR2,
1672 p_new_intf_status IN VARCHAR2,
1673 p_userId IN NUMBER DEFAULT NULL,
1674 p_respId IN NUMBER DEFAULT NULL,
1675 p_resp_appl_Id IN NUMBER DEFAULT NULL,
1676 p_entity_id_tab IN OUT NOCOPY WSH_OTM_ID_TAB,
1677 p_error_id_tab IN OUT NOCOPY WSH_OTM_ID_TAB
1678 ) IS
1679
1680 -- Declare local variables
1681
1682 l_sub_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'UPDATE_ENTITY_INTF_STATUS';
1683 l_entity_id_out_tab WSH_OTM_ID_TAB :=WSH_OTM_ID_TAB();
1684 l_error_id_out_tab WSH_OTM_ID_TAB := WSH_OTM_ID_TAB();
1685 l_id_tab WSH_UTIL_CORE.ID_TAB_TYPE;
1686 l_status_tab WSH_UTIL_CORE.COLUMN_TAB_TYPE;
1687 l_del_current_status varchar2(3);
1688 l_del_id_error_flag VARCHAR2(1) := 'N';
1689 l_stop_id_tab WSH_UTIL_CORE.ID_TAB_TYPE;
1690 l_stop_status_tab WSH_UTIL_CORE.COLUMN_TAB_TYPE;
1691 l_return_status VARCHAR2(1);
1692 l_del_status_code varchar2(30);
1693 l_ignore_for_planning VARCHAR2(1);
1694 l_is_delivery_empty VARCHAR2(1);
1695 i NUMBER;
1696 j NUMBER;
1697 k NUMBER;
1698 -- Define Exception variables
1699 UPD_DEL_INTF_FLAG_API_FALIED EXCEPTION;
1700 UPD_STOP_INTF_FLAG_API_FALIED EXCEPTION;
1701 INVALID_ENTITY_TYPE EXCEPTION;
1702 INVALID_NEW_INTF_STATUS EXCEPTION;
1703
1704 -- define the cursor to get the current TMS_INTERFACE_FLAG status of delivery
1705 cursor get_del_tms_interface_flag(c_delivery_id NUMBER) IS
1706 select TMS_INTERFACE_FLAG,status_code,nvl(ignore_for_planning,'N') from wsh_new_deliveries
1707 where delivery_id = c_delivery_id;
1708
1709 -- define the cursor to get all trip stops for the given trip id.
1710 cursor get_trip_stops(c_trip_id NUMBER) IS
1711 select stop_id,TMS_INTERFACE_FLAG from wsh_trip_stops
1712 where trip_id = c_trip_id;
1713 -- and (TMS_INTERFACE_FLAG ='ASR' or TMS_INTERFACE_FLAG ='ASP');
1714 BEGIN
1715 -- save point
1716 SAVEPOINT UPDATE_ENTITY_INTF_STATUS;
1717
1718 -- Initialize API return status to success
1719 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1720
1721 -- Debug
1722 --
1723 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1724 --
1725 IF l_debug_on IS NULL
1726 THEN
1727 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1728 END IF;
1729 --
1730 -- initialize Apps variables
1731 IF p_userid is NOT NULL THEN
1732 fnd_global.apps_initialize(user_id => p_userid,
1733 resp_id => p_respid,
1734 resp_appl_id => p_resp_appl_Id
1735 );
1736 END IF;
1737 --
1738 -- Debug Statements
1739 --
1740 IF l_debug_on THEN
1741 --{
1742 fnd_profile.get('WSH_DEBUG_LOG_DIRECTORY',l_debugfile);
1743 l_debugfile := l_debugfile||'/'||WSH_DEBUG_SV.g_file;
1744
1745 WSH_DEBUG_SV.push(l_sub_module_name);
1746 WSH_DEBUG_SV.log(l_sub_module_name,'Begin of the process ',l_debugfile);
1747 WSH_DEBUG_SV.log(l_sub_module_name,'p_entity_type ',p_entity_type);
1748 WSH_DEBUG_SV.log(l_sub_module_name,'p_new_intf_status ',p_new_intf_status);
1749 --}
1750 END IF;
1751 --
1752 --Validations
1753 IF p_new_intf_status NOT IN ('IN_PROCESS','COMPLETE') THEN
1754 IF l_debug_on THEN
1755 WSH_DEBUG_SV.log(l_sub_module_name,'Invalid p_new_intf_status',p_new_intf_status);
1756 END IF;
1757 RAISE INVALID_NEW_INTF_STATUS;
1758 END IF;
1759 -- General Process
1760 -- Move all entity id ( trip / delivery ) to Error id table, if there is any error in "U" or "E"
1761 -- Process for DELIVERY
1762 -- if the input - p_new_intf_status is "IN_PROCESS"
1763 -- Query the delivery for the give delivery id
1764 -- if the current interface flag 'CR', need to update the status as "CP"
1765 -- if the current interface flag 'UR', need to update the status as "UP"
1766 -- if the current interface flag 'DR', need to update the status as "DP"
1767 -- if the current interface flag is 'CP,UP,DP', no update required,
1768 -- For all other cases, move the delivery id to error id table.
1769 -- if the input - p_new_intf_status is "COMPLETE"
1770 -- if the current interface flag is CP, DP, or UP -> update to status AW
1771 -- if the current interface flag not in CP, DP, UP , add to ErrorId List
1772 k := 0;
1773 IF p_entity_type = 'DELIVERY' THEN
1774 --{
1775 IF l_debug_on THEN
1776 WSH_DEBUG_SV.log(l_sub_module_name,'Processing Delivery Count',p_entity_id_tab.COUNT);
1777 END IF;
1778 --
1779 FOR i IN 1..p_entity_id_tab.COUNT
1780 LOOP
1781 l_del_id_error_flag := 'N';
1782 open get_del_tms_interface_flag(p_entity_id_tab(i));
1783 fetch get_del_tms_interface_flag into l_del_current_status,l_del_status_code,l_ignore_for_planning;
1784 --
1785 IF get_del_tms_interface_flag%NOTFOUND then
1786 IF l_debug_on THEN
1787 WSH_DEBUG_SV.log(l_sub_module_name,'Processing Delivery Not found',p_entity_id_tab(i));
1788 END IF;
1789 -- Move delivery id to error table - p_error_id_tab
1790 l_del_id_error_flag := 'Y';
1791 END IF;
1792 --
1793 close get_del_tms_interface_flag;
1794 --
1795 IF p_new_intf_status = 'IN_PROCESS' then
1796 IF l_debug_on THEN
1797 WSH_DEBUG_SV.log(l_sub_module_name,'Processing Delivery - Status',p_new_intf_status);
1798 END IF;
1799 --{
1800 -- Query the delivery for the given delivery id list and
1801 -- if the current interface flag is not in 'CR,UR,DR',
1802 -- no update required, move the delivery id to error id table.
1803 --
1804 IF l_del_current_status = 'CR' then
1805 IF l_debug_on THEN
1806 WSH_DEBUG_SV.log(l_sub_module_name,'value of k1 '|| k);
1807 END IF;
1808 l_status_tab(k) := 'CP' ;
1809 l_id_tab(k) := p_entity_id_tab(i);
1810 k := k+ 1;
1811 ELSIF l_del_current_status = 'UR' then
1812 l_status_tab(k) := 'UP';
1813 l_id_tab(k) := p_entity_id_tab(i);
1814 k := k+ 1;
1815 ELSIF l_del_current_status = 'DR' then
1816 l_status_tab(k) := 'DP';
1817 l_id_tab(k) := p_entity_id_tab(i);
1818 k := k+ 1;
1819 elsif l_del_current_status in ('CP','DP','UP') then
1820 -- no change to the status since the delivery is in process
1821 null;
1822 else
1823 IF l_debug_on THEN
1824 WSH_DEBUG_SV.log(l_sub_module_name,'Processing Delivery - Error ',p_entity_id_tab(i));
1825 END IF;
1826 -- set l_del_id_error_flag to "YES" to move this del-id to error table.
1827 l_del_id_error_flag := 'Y';
1828 end if;
1829 --
1830 --}End of p_new_intf_status = 'IN_PROCESS'
1831 --
1832 ELSIF p_new_intf_status = 'COMPLETE' then
1833 --{
1834 -- if the current interface flag is CP, or UP -> update to status AW
1835 -- if the current interface flag is DP -> update to status 'CMP' ( if del-status is "CL")
1836 -- if the current interface flag is DP -> update to status 'CR' (if del-status is "OP"
1837 -- and Include for Planning and the del is not empty)
1838 -- if the current interface flag is DP -> update to status 'NS' ( if del-status is not "CL")
1839 -- if the current interface flag not in CP, DP, UP , add to ErrorId List
1840 if l_del_current_status in ('CP', 'UP') then
1841 l_status_tab(k) := 'AW';
1842 l_id_tab(k) := p_entity_id_tab(i);
1843 k := k+ 1;
1844 elsif l_del_current_status = 'DP' then
1845 if l_del_status_code = 'CL' then
1846 l_status_tab(k) := 'CMP';
1847 -- Added new validation in R12
1848 -- If delivery is Open and Include for planning, and the delivery is not empty
1849 -- set status to "CR"
1850 elsif (l_del_status_code = 'OP' and l_ignore_for_planning = 'N') then
1851 -- check if the delivery is empty or not. If it not empty, set the status to "CR"
1852 l_is_delivery_empty := WSH_NEW_DELIVERY_ACTIONS.IS_DELIVERY_EMPTY(p_entity_id_tab(i));
1853 IF (l_is_delivery_empty = WSH_UTIL_CORE.G_RET_STS_ERROR) THEN
1854 IF l_debug_on THEN
1855 WSH_DEBUG_SV.log(l_sub_module_name,'Error from wsh_new_delivery_actions.is_delivery_empty API for Delivery '||p_entity_id_tab(i));
1856 END IF;
1857 -- Setting this delivery to Error List since it is failed to process
1858 l_del_id_error_flag := 'Y';
1859 END IF;
1860 --
1861 -- if the delivery is not empty, set the status to "CR"
1862 IF (l_is_delivery_empty = 'N') THEN
1863 l_status_tab(k) := 'CR';
1864 else
1865 l_status_tab(k) := 'NS';
1866 end if;
1867 else
1868 l_status_tab(k) := 'NS';
1869 end if;
1870 --
1871 l_id_tab(k) := p_entity_id_tab(i);
1872 k := k+ 1;
1873 else
1874 l_del_id_error_flag := 'Y';
1875 end if;
1876 --}
1877 END IF;
1878 IF l_del_id_error_flag = 'Y' THEN
1879 --{
1880 -- Move delivery id to error table - p_error_id_tab
1881 IF l_debug_on THEN
1882 WSH_DEBUG_SV.log(l_sub_module_name,'Processing Delivery ID-Moving to error table ');
1883 END IF;
1884 l_error_id_out_tab.extend;
1885 l_error_id_out_tab(l_error_id_out_tab.COUNT):=p_entity_id_tab(i);
1886 --
1887 --}
1888 ELSE
1889 IF l_debug_on THEN
1890 WSH_DEBUG_SV.log(l_sub_module_name,'Processing Delivery ID-Success to table ',p_entity_id_tab(i));
1891 END IF;
1892
1893 -- Move delivery id to success table - l_entity_id_out_tab
1894 l_entity_id_out_tab.extend;
1895 l_entity_id_out_tab(l_entity_id_out_tab.COUNT):=p_entity_id_tab(i);
1896 END IF;
1897 --
1898 END LOOP;
1899 IF l_id_tab.COUNT > 0 THEN
1900 IF l_debug_on THEN
1901 WSH_DEBUG_SV.log(l_sub_module_name,'Processing Delivery Calling WSH API to update status-Del-Count',l_id_tab.COUNT);
1902 END IF;
1903 --{
1904 --Call WSH API to update the new status
1905 WSH_NEW_DELIVERIES_PVT.UPDATE_TMS_INTERFACE_FLAG
1906 (P_DELIVERY_ID_TAB=>l_id_tab,
1907 P_TMS_INTERFACE_FLAG_TAB =>l_status_tab,
1908 X_RETURN_STATUS =>l_return_status);
1909 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS then
1910 raise UPD_DEL_INTF_FLAG_API_FALIED;
1911 END IF;
1912 IF l_debug_on THEN
1913 WSH_DEBUG_SV.log(l_sub_module_name,'Processing Delivery Calling WSH API to update status-Success-Del-Count',l_id_tab.COUNT);
1914 END IF;
1915 --}
1916 END IF;
1917 --}
1918 ELSIF p_entity_type = 'TRIP' THEN
1919 --{
1920 -- Process for TRIP
1921 -- For each Trip ID, need to query the trip stops
1922 -- IF p_new_intf_status is 'IN_PROCESS' and the current status is "ASR"
1923 -- update the status to "ASP"
1924 -- Otherwise no change.
1925 -- IF p_new_intf_status is 'COMPLETE' and the current status is "ASP"
1926 -- update the status to "CMP"
1927 -- Otherwise no change.
1928 -- if there is no trip stops for the give trip id, move the trip id to error id table.
1929 k := 0;
1930 IF l_debug_on THEN
1931 WSH_DEBUG_SV.log(l_sub_module_name,'Trip Count ',p_entity_id_tab.COUNT);
1932 END IF;
1933 --{
1934 FOR i IN 1..p_entity_id_tab.COUNT
1935 LOOP
1936 --{
1937 IF l_debug_on THEN
1938 WSH_DEBUG_SV.log(l_sub_module_name,'Trip Stop ID ',p_entity_id_tab(i));
1939 END IF;
1940 OPEN get_trip_stops(p_entity_id_tab(i));
1941 FETCH get_trip_stops BULK COLLECT into l_stop_id_tab,l_stop_status_tab;
1942 CLOSE get_trip_stops;
1943 --
1944 IF l_debug_on THEN
1945 WSH_DEBUG_SV.log(l_sub_module_name,'Trip Stps count ',l_stop_id_tab.COUNT);
1946 END IF;
1947 IF l_stop_id_tab.COUNT > 0 THEN
1948 --{
1949 j := l_stop_id_tab.FIRST;
1950 WHILE j IS NOT NULL
1951 LOOP
1952 --{
1953 IF l_debug_on THEN
1954 WSH_DEBUG_SV.log(l_sub_module_name,'Processing Trip STOP ID ',l_stop_id_tab(j) ||'-'||l_stop_status_tab(j));
1955 END IF;
1956 --
1957 IF p_new_intf_status = 'IN_PROCESS' THEN
1958 --
1959 --IF l_stop_status_tab(j) = 'ASR' then
1960 l_status_tab(k) := 'ASP' ;
1961 IF l_debug_on THEN
1962 WSH_DEBUG_SV.log(l_sub_module_name,'Processing Trip STOP ID -New Status ',l_status_tab(k));
1963 END IF;
1964 l_id_tab(k) := l_stop_id_tab(j);
1965 k := k + 1;
1966 --end if ;
1967 --
1968 ELSIF p_new_intf_status = 'COMPLETE' THEN
1969 --
1970 --IF l_stop_status_tab(j) = 'ASP' then
1971 l_status_tab(k) := 'CMP';
1972 IF l_debug_on THEN
1973 WSH_DEBUG_SV.log(l_sub_module_name,'Processing Trip STOP ID -New Status ',l_status_tab(k));
1974 END IF;
1975 l_id_tab(k) := l_stop_id_tab(j);
1976 k := k + 1;
1977 --END IF;
1978 --
1979 END IF;
1980 --
1981 j := l_stop_id_tab.NEXT(j);
1982 --}
1983 END LOOP;
1984 -- Move delivery id to success table - l_entity_id_out_tab
1985 l_entity_id_out_tab.extend;
1986 l_entity_id_out_tab(l_entity_id_out_tab.COUNT):=p_entity_id_tab(i);
1987 --}
1988 ELSE
1989 --{
1990 IF l_debug_on THEN
1991 WSH_DEBUG_SV.log(l_sub_module_name,'Moving to Error table for Trip id',p_entity_id_tab(i));
1992 END IF;
1993 -- Move the Trip Id into error table
1994 l_error_id_out_tab.extend;
1995 l_error_id_out_tab(l_error_id_out_tab.COUNT):=p_entity_id_tab(i);
1996 --}
1997 END IF;
1998 --}
1999 END LOOP;
2000 --Call WSH API to update the new status in Trip Stops
2001 IF l_id_tab.COUNT > 0 THEN
2002 --{
2003 IF l_debug_on THEN
2004 WSH_DEBUG_SV.log(l_sub_module_name,'Calling WSH API-UPDATE_TMS_INTERFACE_FLAG Total stops ids',l_id_tab.COUNT);
2005 END IF;
2006 --
2007 WSH_TRIP_STOPS_PVT.UPDATE_TMS_INTERFACE_FLAG
2008 (P_STOP_ID_TAB=>l_id_tab,
2009 P_TMS_INTERFACE_FLAG_TAB =>l_status_tab,
2010 X_RETURN_STATUS =>l_return_status);
2011 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS then
2012 raise UPD_STOP_INTF_FLAG_API_FALIED;
2013 END IF;
2014 IF l_debug_on THEN
2015 WSH_DEBUG_SV.log(l_sub_module_name,'Processing Trip Calling WSH API to update status-Success-Trip-Stops-Count',l_id_tab.COUNT);
2016 END IF;
2017 --}
2018 END IF;
2019 --}
2020 ELSE
2021 RAISE INVALID_ENTITY_TYPE;
2022 END IF;
2023 -- store the success deliveries/trips back to p_entity_id_tab table
2024 -- store the error deliveries/trips tp_error_id_tab table
2025 p_entity_id_tab := l_entity_id_out_tab;
2026 p_error_id_tab := l_error_id_out_tab;
2027 IF l_debug_on THEN
2028 WSH_DEBUG_SV.log(l_sub_module_name,'End of Process - Delivery/Trip Success',p_entity_id_tab.COUNT);
2029 WSH_DEBUG_SV.log(l_sub_module_name,'End of Process - Delivery/Trip Error',p_error_id_tab.COUNT);
2030 END IF;
2031 --
2032 EXCEPTION
2033 WHEN UPD_DEL_INTF_FLAG_API_FALIED THEN
2034 IF l_debug_on THEN
2035 WSH_DEBUG_SV.log(l_sub_module_name,'UPD_DEL_INTF_FLAG_API_FALIED',sqlerrm);
2036 END IF;
2037 ROLLBACK TO UPDATE_ENTITY_INTF_STATUS;
2038 -- returning all entitiy id to error id table
2039 p_error_id_tab := p_entity_id_tab;
2040 x_return_status := FND_API.G_RET_STS_ERROR;
2041 WHEN UPD_STOP_INTF_FLAG_API_FALIED THEN
2042 IF l_debug_on THEN
2043 WSH_DEBUG_SV.log(l_sub_module_name,'UPD_STOP_INTF_FLAG_API_FALIED',sqlerrm);
2044 END IF;
2045 ROLLBACK TO UPDATE_ENTITY_INTF_STATUS;
2046 -- returning all entitiy id to error id table
2047 p_error_id_tab := p_entity_id_tab;
2048 x_return_status := FND_API.G_RET_STS_ERROR;
2049 WHEN INVALID_ENTITY_TYPE THEN
2050 IF l_debug_on THEN
2051 WSH_DEBUG_SV.log(l_sub_module_name,'INVALID_ENTITY_TYPE',sqlerrm);
2052 END IF;
2053 ROLLBACK TO UPDATE_ENTITY_INTF_STATUS;
2054 -- returning all entitiy id to error id table
2055 p_error_id_tab := p_entity_id_tab;
2056 x_return_status := FND_API.G_RET_STS_ERROR;
2057 WHEN INVALID_NEW_INTF_STATUS THEN
2058 IF l_debug_on THEN
2059 WSH_DEBUG_SV.log(l_sub_module_name,'INVALID_NEW_INTF_STATUS',sqlerrm);
2060 END IF;
2061 ROLLBACK TO UPDATE_ENTITY_INTF_STATUS;
2062 -- returning all entitiy id to error id table
2063 p_error_id_tab := p_entity_id_tab;
2064 x_return_status := FND_API.G_RET_STS_ERROR;
2065 WHEN FND_API.G_EXC_ERROR THEN
2066 IF l_debug_on THEN
2067 WSH_DEBUG_SV.log(l_sub_module_name,' FND_API.G_EXC_ERROR',sqlerrm);
2068 END IF;
2069 ROLLBACK TO UPDATE_ENTITY_INTF_STATUS;
2070 -- returning all entitiy id to error id table
2071 p_error_id_tab := p_entity_id_tab;
2072 x_return_status := FND_API.G_RET_STS_ERROR;
2073 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2074 IF l_debug_on THEN
2075 WSH_DEBUG_SV.log(l_sub_module_name,'FND_API.G_EXC_UNEXPECTED_ERROR',sqlerrm);
2076 END IF;
2077 ROLLBACK TO UPDATE_ENTITY_INTF_STATUS;
2078 -- returning all entitiy id to error id table
2079 p_error_id_tab := p_entity_id_tab;
2080 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
2081 WHEN OTHERS then
2082 IF l_debug_on THEN
2083 WSH_DEBUG_SV.log(l_sub_module_name,'OTHERS - ERROR',sqlerrm);
2084 END IF;
2085 -- returning all entitiy id to error id table
2086 p_error_id_tab := p_entity_id_tab;
2087 x_return_status := FND_API.G_RET_STS_ERROR;
2088 wsh_util_core.default_handler('WSH_OTM_OUTBOUND.UPDATE_ENTITY_INTF_STATUS');
2089 END;
2090
2091
2092
2093 END WSH_OTM_OUTBOUND;
2094
[Home] [Help]