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