1 PACKAGE BODY WSH_TMS_RELEASE as
2 /* $Header: WSHTMRLB.pls 120.12.12010000.3 2009/01/22 07:24:46 anvarshn ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(50) := 'WSH_TMS_RELEASE';
5
6 --
7 -- Forward Declaration of Internal procedures
8 --
9 PROCEDURE stamp_interface_error(
10 p_group_id IN NUMBER,
11 p_entity_table_name IN VARCHAR2,
12 p_entity_interface_id IN NUMBER,
13 p_message_name IN VARCHAR2,
14 p_message_appl IN VARCHAR2 DEFAULT NULL,
15 p_message_text IN VARCHAR2 DEFAULT NULL,
16 p_token_1_name IN VARCHAR2 DEFAULT NULL,
17 p_token_1_value IN VARCHAR2 DEFAULT NULL,
18 p_token_2_name IN VARCHAR2 DEFAULT NULL,
19 p_token_2_value IN VARCHAR2 DEFAULT NULL,
20 p_token_3_name IN VARCHAR2 DEFAULT NULL,
21 p_token_3_value IN VARCHAR2 DEFAULT NULL,
22 p_token_4_name IN VARCHAR2 DEFAULT NULL,
23 p_token_4_value IN VARCHAR2 DEFAULT NULL,
24 p_dleg_tab IN TMS_DLEG_TAB_TYPE,
25 x_errors_tab IN OUT NOCOPY INTERFACE_ERRORS_TAB_TYPE,
26 x_return_status OUT NOCOPY VARCHAR2);
27
28 PROCEDURE compare_trip_for_deliveries
29 (p_dleg_tab IN OUT NOCOPY TMS_DLEG_TAB_TYPE,
30 p_trip_id IN NUMBER,
31 x_unassign_id_tab IN OUT NOCOPY WSH_UTIL_CORE.id_tab_type,
32 --x_unassign_ver_tab IN OUT NOCOPY WSH_UTIL_CORE.id_tab_type,
33 x_return_status OUT NOCOPY VARCHAR2);
34
35 PROCEDURE process_internal_locations
36 (x_delivery_tab IN OUT NOCOPY TMS_DLEG_TAB_TYPE,
37 x_stop_tab IN OUT NOCOPY TMS_STOP_TAB_TYPE,
38 x_return_status OUT NOCOPY VARCHAR2);
39
40 -- Get Server Time for Timezone conversion
41 PROCEDURE get_server_time
42 (p_source_time IN DATE,
43 p_source_timezone_code IN VARCHAR2,
44 x_server_time OUT NOCOPY DATE,
45 x_return_status OUT NOCOPY VARCHAR2,
46 x_msg_count OUT NOCOPY NUMBER,
47 x_msg_data OUT NOCOPY VARCHAR2);
48
49
50 -- End of Forward Declarations
51 --
52
53 --====================================================
54 --
55 -- Procedure: Release_planned_shipment
56 -- Description: This procedure processes the data populated in the Interface
57 -- tables by the BPEL Inbound process.
58 -- parameters
59 -- IN:
60 -- p_group_id list of group_ids to process their
61 -- WSH_TRIPS_INTERFACE records and
62 -- their associated table records.
63 --
64 -- p_latest_version if 'Y'/null then process only if inbound delivery
65 -- tms_version_number matches on the EBS delivery.
66 -- if 'N' then process the all inbound deliveries
67 -- irrespective of version number but EBS delivery
68 -- tms_interface_flag should be 'UP'/'UR'.
69 -- tms_interface_flag will be remain at 'UR'/'UP' when
70 -- procesed the lower version delivery.
71 --
72 -- p_tp_plan_low process all trips from wsh_trip_interface
73 -- table which are having tp_plan_name greater than or
74 -- equal to 'p_tp_plan_low'
75 --
76 -- p_tp_plan_high process all trips from wsh_trip_interface
77 -- table which are having tp_plan_name less than or
78 -- equal to 'p_tp_plan_high'
79 --
80 -- p_dummy Dummy parameter to disable
81 -- p_del_name_low,p_del_name_high
82 -- p_organization_id,p_del_pickup_date_low and
83 -- p_del_pickup_date_high
84 -- on the concurrent program parameters windonw
85 -- when entered p_tp_plan_low/p_tp_plan_high.
86 --
87 -- Organization_id Process all shipments which are associated to the
88 -- OTM enabled organization id.
89 --
90 -- p_del_name_low Process all shipments which are associated to the
91 -- deliveries from wsh_new_del_interface which
92 -- having delivery name greater than or equal to
93 -- p_del_name_low
94 --
95 -- p_del_name_high Process all shipments which are associated to the
96 -- deliveries from wsh_new_del_interface which
97 -- having delivery name less than or equal to
98 -- p_del_name_high
99 --
100 -- p_del_pickup_date_low Process all shipments which are associated to the
101 -- deliveries which having initial pickup date
102 -- greater than or equal to p_del_pickup_date_low
103 --
104 -- p_del_pickup_date_high low Process all shipments which are associated to
105 -- the deliveries which having initial pickup date
106 -- less than or equal to p_del_pickup_date_high
107 -- OUT:
108 -- errbuf Error message.
109 -- retcode Error Code 1:Successs, 2:Warning and 3:Error.
110 --
111 -- Usage:
112 -- 1. Glog triggered Inbound BPEL process will call the concurrent program
113 -- to call this procedure
114 -- 2. When user fixes interface errors and re-processes the data, this
115 -- procedure is called(from Interface Message Corrections Form)
116 -- Assumption: This procedure is called for a single group id
117 --
118 -- 3. When user submits the planned shipment con. request manually.
119 -- The process flow would be in this order
120 --
121 -- Process group_id(single id only and not table of ids)
122 -- Initialize table of unassigned_delivery_ids.
123 --1. Gather Trips Interface Data for the input group id
124 --2. Gather Trip Stops Interface Data for the input group id
125 -- trip_interface_id is also populated for linking purpose
126 --3. Gather Delivery Legs Interface Data for the input group id
127 -- trip_interface_id is also populated for linking purpose
128 --4. LOOP Start with 1st trip record,
129 -- ai. If trip exists in EBS,Lock Trip (call Table Handler)
130 -- aii. If trip exists in EBS,Lock Trip Stops (call Table Handler)
131 -- b. Lock Delivery/Delivery Legs + compare revision (call Table Handler)
132 -- c. Process Trips: create/update
133 -- d. Process Trip Stops: delete/update/create
134 -- e. Process Deliveries: assign/unassign
135 -- OTM R12
136 -- f. This step is no longer required as trip weight is calculated in previous step itself
137 -- Calculate wt/vol for the trip (includes stops) (call Group API)
138 -- OTM R12
139 -- g. Freight Costs
140 -- h. Stamping the loading sequence number for WMS org
141 -- i. Calling WMS api to send the Dock Appointment info. from OTM to WMS for WMS org.
142 -- j. If any errors exist, then log errors in wsh_interface_errors table and
143 -- (error)exceptions for all the deliveries within this GC3 trip (as per l_dleg_info_tab)
144 -- Elsif no errors encountered: Log (info only) Delivery Exception, Update Interface Status
145 -- to 'ANSWER_RECEIVED', Purge
146 -- END LOOP;
147 --5. For group_id, Update delivery status based on unassigned_delivery_ids,
148 -- Update the Table directly and not call WSHDETHB.update_tms_interface_flag
149 -- to AWAITING_ANSWER
150 -- Do not mark these deliveries as ignore for planning.
151 --
152 --====================================================
153 -- Bug#7491598(ER,defer planned shipment iface): Added new parameters which are used
154 -- when invoked the planned shipment interface manually.
155 PROCEDURE release_planned_shipment(
156 errbuf OUT NOCOPY VARCHAR2,
157 retcode OUT NOCOPY VARCHAR2,
158 p_group_id IN NUMBER DEFAULT NULL,
159 p_latest_version IN VARCHAR2 DEFAULT NULL,
160 p_tp_plan_low IN VARCHAR2 DEFAULT NULL,
161 p_tp_plan_high IN VARCHAR2 DEFAULT NULL,
162 p_dummy IN VARCHAR2 DEFAULT NULL,
163 p_organization_id IN NUMBER DEFAULT NULL,
164 p_del_name_low IN VARCHAR2 DEFAULT NULL,
165 p_del_name_high IN VARCHAR2 DEFAULT NULL,
166 p_del_pickup_date_low IN VARCHAR2 DEFAULT NULL,
167 p_del_pickup_date_high IN VARCHAR2 DEFAULT NULL
168 ) IS
169
170 -- Cursor to fetch Trip information for the input group id
171 CURSOR c_tms_interface_trips IS
172 SELECT wti.trip_interface_id,
173 wti.group_id, --Bug7717569
174 wt.trip_id,
175 wti.tp_plan_name,
176 wt.tp_trip_number,
177 wti.planned_flag,
178 wt.planned_flag wsh_planned_flag,
179 wt.status_code wsh_status_code,
180 wt.name,
181 wti.vehicle_item_id,
182 wti.vehicle_organization_id,
183 wti.vehicle_num_prefix,
184 wti.vehicle_number,
185 wti.carrier_id carrier_id,
186 wt.ship_method_code,
187 wt.route_id,
188 wt.routing_instructions,
189 wti.service_level,
190 wti.mode_of_transport,
191 wti.freight_terms_code,
192 wt.seal_code,
193 wt.shipments_type_flag,
194 'N' wsh_ignore_for_planning,
195 wt.booking_number,
196 wt.vessel,
197 wt.voyage_number,
198 wt.port_of_loading,
199 wt.port_of_discharge,
200 wt.carrier_contact_id,
201 wt.shipper_wait_time,
202 wt.wait_time_uom,
203 wt.carrier_response,
204 wt.operator,
205 wti.vehicle_item_name,
206 wti.interface_action_code,
207 wt.attribute_category,
208 wt.attribute1,
209 wt.attribute2,
210 wt.attribute3,
211 wt.attribute4,
212 wt.attribute5,
213 wt.attribute6,
214 wt.attribute7,
215 wt.attribute8,
216 wt.attribute9,
217 wt.attribute10,
218 wt.attribute11,
219 wt.attribute12,
220 wt.attribute13,
221 wt.attribute14,
222 wt.attribute15
223 FROM wsh_trips_interface wti,
224 wsh_trips wt
225 WHERE wti.group_id = p_group_id --p_group_id is the input parameter
226 AND wti.interface_action_code IN (G_TMS_RELEASE_CODE,G_TMS_DELETE_CODE)
227 AND wt.tp_plan_name(+) = wti.tp_plan_name
228 ORDER BY wti.trip_interface_id;
229
230 -- Bug#7491598(ER,defer planned shipment iface): Cursor to fetch Trip information for the input tp plan name
231 CURSOR c_tms_interface_trips_plan(p_tp_plan_low IN varchar2,p_tp_plan_high IN varchar2) IS
232 SELECT wti.trip_interface_id,
233 wti.group_id, --Bug7717569
234 wt.trip_id,
235 wti.tp_plan_name,
236 wt.tp_trip_number,
237 wti.planned_flag,
238 wt.planned_flag wsh_planned_flag,
239 wt.status_code wsh_status_code,
240 wt.name,
241 wti.vehicle_item_id,
242 wti.vehicle_organization_id,
243 wti.vehicle_num_prefix,
244 wti.vehicle_number,
245 wti.carrier_id carrier_id,
246 wt.ship_method_code,
247 wt.route_id,
248 wt.routing_instructions,
249 wti.service_level,
250 wti.mode_of_transport,
251 wti.freight_terms_code,
252 wt.seal_code,
253 wt.shipments_type_flag,
254 'N' wsh_ignore_for_planning,
255 wt.booking_number,
256 wt.vessel,
257 wt.voyage_number,
258 wt.port_of_loading,
259 wt.port_of_discharge,
260 wt.carrier_contact_id,
261 wt.shipper_wait_time,
262 wt.wait_time_uom,
263 wt.carrier_response,
264 wt.operator,
265 wti.vehicle_item_name,
266 wti.interface_action_code,
267 wt.attribute_category,
268 wt.attribute1,
269 wt.attribute2,
270 wt.attribute3,
271 wt.attribute4,
272 wt.attribute5,
273 wt.attribute6,
274 wt.attribute7,
275 wt.attribute8,
276 wt.attribute9,
277 wt.attribute10,
278 wt.attribute11,
279 wt.attribute12,
280 wt.attribute13,
281 wt.attribute14,
282 wt.attribute15
283 FROM wsh_trips_interface wti,
284 wsh_trips wt
285 WHERE wti.tp_plan_name >= nvl(p_tp_plan_low,wti.tp_plan_name)
286 AND wti.tp_plan_name <= nvl(p_tp_plan_high,wti.tp_plan_name)
287 AND wti.interface_action_code IN (G_TMS_RELEASE_CODE,G_TMS_DELETE_CODE)
288 AND wt.tp_plan_name(+) = wti.tp_plan_name
289 ORDER BY wti.trip_interface_id;
290
291 -- Bug#7491598(ER,defer planned shipment iface):Cursor to fetch Trip information for the input delivery,org,and pickup dates.
292 CURSOR c_tms_interface_trips_del(p_del_name_low varchar2,p_del_name_high varchar2,l_organization_id NUMBER,l_del_pickup_date_low DATE,l_del_pickup_date_high DATE) IS
293 SELECT wti.trip_interface_id,
294 wti.group_id, --Bug7717569
295 wt.trip_id,
296 wti.tp_plan_name,
297 wt.tp_trip_number,
298 wti.planned_flag,
299 wt.planned_flag wsh_planned_flag,
300 wt.status_code wsh_status_code,
301 wt.name,
302 wti.vehicle_item_id,
303 wti.vehicle_organization_id,
304 wti.vehicle_num_prefix,
305 wti.vehicle_number,
306 wti.carrier_id carrier_id,
307 wt.ship_method_code,
308 wt.route_id,
309 wt.routing_instructions,
310 wti.service_level,
311 wti.mode_of_transport,
312 wti.freight_terms_code,
313 wt.seal_code,
314 wt.shipments_type_flag,
315 'N' wsh_ignore_for_planning,
316 wt.booking_number,
317 wt.vessel,
318 wt.voyage_number,
319 wt.port_of_loading,
320 wt.port_of_discharge,
321 wt.carrier_contact_id,
322 wt.shipper_wait_time,
323 wt.wait_time_uom,
324 wt.carrier_response,
325 wt.operator,
326 wti.vehicle_item_name,
327 wti.interface_action_code,
328 wt.attribute_category,
329 wt.attribute1,
330 wt.attribute2,
331 wt.attribute3,
332 wt.attribute4,
333 wt.attribute5,
334 wt.attribute6,
335 wt.attribute7,
336 wt.attribute8,
337 wt.attribute9,
338 wt.attribute10,
339 wt.attribute11,
340 wt.attribute12,
341 wt.attribute13,
342 wt.attribute14,
343 wt.attribute15
344 FROM wsh_trips_interface wti,
345 wsh_trips wt
346 WHERE wti.interface_action_code IN (G_TMS_RELEASE_CODE,G_TMS_DELETE_CODE)
347 AND wt.tp_plan_name(+) = wti.tp_plan_name
348 AND wti.group_id in
349 (SELECT wti.group_id FROM wsh_new_del_interface wndi,
350 wsh_del_legs_interface wdli,
351 wsh_trip_stops_interface wtsi,
352 wsh_trips_interface wti,
353 wsh_new_deliveries wnd
354 where wndi.interface_action_code IN (G_TMS_RELEASE_CODE,G_TMS_DELETE_CODE)
355 AND wdli.delivery_interface_id = wndi.delivery_interface_id
356 AND wdli.pick_up_stop_interface_id = wtsi.stop_interface_id
357 AND wtsi.trip_interface_id = wti.trip_interface_id
358 AND wnd.delivery_id = wndi.delivery_id
359 AND wndi.name >= nvl(p_del_name_low,wndi.name)
360 AND wndi.name <= nvl(p_del_name_high,wndi.name)
361 AND wnd.organization_id = NVL(l_organization_id,wnd.organization_id)
362 AND wnd.initial_pickup_date >= nvl(l_del_pickup_date_low,wnd.initial_pickup_date)
363 AND wnd.initial_pickup_date <= nvl(l_del_pickup_date_high,wnd.initial_pickup_date))
364 ORDER BY wti.trip_interface_id;
365
366 --Bug#7491598(ER,defer planned shipment iface): Cursor to fetch Trip information for all group ids
367 CURSOR c_tms_interface_trips_all IS
368 SELECT wti.trip_interface_id,
369 wti.group_id, --Bug7717569
370 wt.trip_id,
371 wti.tp_plan_name,
372 wt.tp_trip_number,
373 wti.planned_flag,
374 wt.planned_flag wsh_planned_flag,
375 wt.status_code wsh_status_code,
376 wt.name,
377 wti.vehicle_item_id,
378 wti.vehicle_organization_id,
379 wti.vehicle_num_prefix,
380 wti.vehicle_number,
381 wti.carrier_id carrier_id,
382 wt.ship_method_code,
383 wt.route_id,
384 wt.routing_instructions,
385 wti.service_level,
386 wti.mode_of_transport,
387 wti.freight_terms_code,
388 wt.seal_code,
389 wt.shipments_type_flag,
390 'N' wsh_ignore_for_planning,
391 wt.booking_number,
392 wt.vessel,
393 wt.voyage_number,
394 wt.port_of_loading,
395 wt.port_of_discharge,
396 wt.carrier_contact_id,
397 wt.shipper_wait_time,
398 wt.wait_time_uom,
399 wt.carrier_response,
400 wt.operator,
401 wti.vehicle_item_name,
402 wti.interface_action_code,
403 wt.attribute_category,
404 wt.attribute1,
405 wt.attribute2,
406 wt.attribute3,
407 wt.attribute4,
408 wt.attribute5,
409 wt.attribute6,
410 wt.attribute7,
411 wt.attribute8,
412 wt.attribute9,
413 wt.attribute10,
414 wt.attribute11,
415 wt.attribute12,
416 wt.attribute13,
417 wt.attribute14,
418 wt.attribute15
419 FROM wsh_trips_interface wti,
420 wsh_trips wt
421 WHERE wti.interface_action_code IN (G_TMS_RELEASE_CODE,G_TMS_DELETE_CODE)
422 AND wt.tp_plan_name(+) = wti.tp_plan_name
423 ORDER BY wti.trip_interface_id;
424
425
426 -- Cursor to fetch Trip Stop information for the input group id(Order by trip_interface_id)
427 CURSOR c_tms_interface_stops IS
428 SELECT wtsi.stop_interface_id,
429 wtsi.stop_id, -- will be populated after create or update, not from GC3
430 wtsi.tp_stop_id,
431 wtsi.stop_location_id,
432 wtsi.stop_sequence_number,
433 wtsi.planned_arrival_date,
434 wtsi.planned_departure_date,
435 wtsi.departure_gross_weight,
436 wtsi.departure_net_weight,
437 wtsi.weight_uom_code,
438 wtsi.departure_volume,
439 wtsi.volume_uom_code,
440 wtsi.departure_seal_code,
441 wtsi.departure_fill_percent,
442 wtsi.wkend_layover_stops,
443 wtsi.wkday_layover_stops,
444 wtsi.shipments_type_flag,
445 wtsi.trip_interface_id,
446 wtsi.timezone_code,
447 'C' dml_action, -- indicates if stop has to be created or updated
448 null tp_attribute_category,
449 null tp_attribute1,
450 null tp_attribute2,
451 null tp_attribute3,
452 null tp_attribute4,
453 null tp_attribute5,
454 null tp_attribute6,
455 null tp_attribute7,
456 null tp_attribute8,
457 null tp_attribute9,
458 null tp_attribute10,
459 null tp_attribute11,
460 null tp_attribute12,
461 null tp_attribute13,
462 null tp_attribute14,
463 null tp_attribute15,
464 null attribute_category,
465 null attribute1,
466 null attribute2,
467 null attribute3,
468 null attribute4,
469 null attribute5,
470 null attribute6,
471 null attribute7,
472 null attribute8,
473 null attribute9,
474 null attribute10,
475 null attribute11,
476 null attribute12,
477 null attribute13,
478 null attribute14,
479 null attribute15
480 FROM wsh_trip_stops_interface wtsi,
481 wsh_trips_interface wti
482 WHERE wti.group_id = p_group_id --p_group_id is the input parameter
483 AND wtsi.trip_interface_id = wti.trip_interface_id
484 AND wtsi.interface_action_code = G_TMS_RELEASE_CODE
485 AND wti.interface_action_code = G_TMS_RELEASE_CODE
486 ORDER BY wtsi.trip_interface_id,wtsi.stop_sequence_number ;
487
488 --Bug#7491598(ER,defer planned shipment iface): Cursor to fetch Trip Stop information for the input tp plan names(Order by trip_interface_id)
489 CURSOR c_tms_interface_stops_plan(p_tp_plan_low varchar2,p_tp_plan_high varchar2) IS
490 SELECT wtsi.stop_interface_id,
491 wtsi.stop_id, -- will be populated after create or update, not from GC3
492 wtsi.tp_stop_id,
493 wtsi.stop_location_id,
494 wtsi.stop_sequence_number,
495 wtsi.planned_arrival_date,
496 wtsi.planned_departure_date,
497 wtsi.departure_gross_weight,
498 wtsi.departure_net_weight,
499 wtsi.weight_uom_code,
500 wtsi.departure_volume,
501 wtsi.volume_uom_code,
502 wtsi.departure_seal_code,
503 wtsi.departure_fill_percent,
504 wtsi.wkend_layover_stops,
505 wtsi.wkday_layover_stops,
506 wtsi.shipments_type_flag,
507 wtsi.trip_interface_id,
508 wtsi.timezone_code,
509 'C' dml_action, -- indicates if stop has to be created or updated
510 null tp_attribute_category,
511 null tp_attribute1,
512 null tp_attribute2,
513 null tp_attribute3,
514 null tp_attribute4,
515 null tp_attribute5,
516 null tp_attribute6,
517 null tp_attribute7,
518 null tp_attribute8,
519 null tp_attribute9,
520 null tp_attribute10,
521 null tp_attribute11,
522 null tp_attribute12,
523 null tp_attribute13,
524 null tp_attribute14,
525 null tp_attribute15,
526 null attribute_category,
527 null attribute1,
528 null attribute2,
529 null attribute3,
530 null attribute4,
531 null attribute5,
532 null attribute6,
533 null attribute7,
534 null attribute8,
535 null attribute9,
536 null attribute10,
537 null attribute11,
538 null attribute12,
539 null attribute13,
540 null attribute14,
541 null attribute15
542 FROM wsh_trip_stops_interface wtsi,
543 wsh_trips_interface wti
544 WHERE wti.tp_plan_name >= nvl(p_tp_plan_low,wti.tp_plan_name)
545 AND wti.tp_plan_name <= nvl(p_tp_plan_high,wti.tp_plan_name)
546 AND wtsi.trip_interface_id = wti.trip_interface_id
547 AND wtsi.interface_action_code = G_TMS_RELEASE_CODE
548 AND wti.interface_action_code = G_TMS_RELEASE_CODE
549 ORDER BY wtsi.trip_interface_id,wtsi.stop_sequence_number ;
550
551 --Bug#7491598(ER,defer planned shipment iface): Cursor to fetch Trip Stop information for the input delivery (Order by trip_interface_id)
552 CURSOR c_tms_interface_stops_del(p_del_name_low varchar2,p_del_name_high varchar2,l_organization_id NUMBER,l_del_pickup_date_low DATE,l_del_pickup_date_high DATE) IS
553 SELECT wtsi.stop_interface_id,
554 wtsi.stop_id, -- will be populated after create or update, not from GC3
555 wtsi.tp_stop_id,
556 wtsi.stop_location_id,
557 wtsi.stop_sequence_number,
558 wtsi.planned_arrival_date,
559 wtsi.planned_departure_date,
560 wtsi.departure_gross_weight,
561 wtsi.departure_net_weight,
562 wtsi.weight_uom_code,
563 wtsi.departure_volume,
564 wtsi.volume_uom_code,
565 wtsi.departure_seal_code,
566 wtsi.departure_fill_percent,
567 wtsi.wkend_layover_stops,
568 wtsi.wkday_layover_stops,
569 wtsi.shipments_type_flag,
570 wtsi.trip_interface_id,
571 wtsi.timezone_code,
572 'C' dml_action, -- indicates if stop has to be created or updated
573 null tp_attribute_category,
574 null tp_attribute1,
575 null tp_attribute2,
576 null tp_attribute3,
577 null tp_attribute4,
578 null tp_attribute5,
579 null tp_attribute6,
580 null tp_attribute7,
581 null tp_attribute8,
582 null tp_attribute9,
583 null tp_attribute10,
584 null tp_attribute11,
585 null tp_attribute12,
586 null tp_attribute13,
587 null tp_attribute14,
588 null tp_attribute15,
589 null attribute_category,
590 null attribute1,
591 null attribute2,
592 null attribute3,
593 null attribute4,
594 null attribute5,
595 null attribute6,
596 null attribute7,
597 null attribute8,
598 null attribute9,
599 null attribute10,
600 null attribute11,
601 null attribute12,
602 null attribute13,
603 null attribute14,
604 null attribute15
605 FROM wsh_trip_stops_interface wtsi,
606 wsh_trips_interface wti
607 WHERE wtsi.trip_interface_id = wti.trip_interface_id
608 AND wtsi.interface_action_code = G_TMS_RELEASE_CODE
609 AND wti.interface_action_code = G_TMS_RELEASE_CODE
610 AND wti.group_id in
611 (SELECT wti.group_id FROM wsh_new_del_interface wndi,
612 wsh_del_legs_interface wdli,
613 wsh_trip_stops_interface wtsi,
614 wsh_trips_interface wti,
615 wsh_new_deliveries wnd
616 where wndi.interface_action_code IN (G_TMS_RELEASE_CODE,G_TMS_DELETE_CODE)
617 AND wdli.delivery_interface_id = wndi.delivery_interface_id
618 AND wdli.pick_up_stop_interface_id = wtsi.stop_interface_id
619 AND wtsi.trip_interface_id = wti.trip_interface_id
620 AND wnd.delivery_id = wndi.delivery_id
621 AND wndi.name >= nvl(p_del_name_low,wndi.name)
622 AND wndi.name <= nvl(p_del_name_high,wndi.name)
623 AND wnd.organization_id = NVL(p_organization_id,wnd.organization_id)
624 AND wnd.initial_pickup_date >= nvl(l_del_pickup_date_low,wnd.initial_pickup_date)
625 AND wnd.initial_pickup_date <= nvl(l_del_pickup_date_high,wnd.initial_pickup_date))
626 ORDER BY wtsi.trip_interface_id,wtsi.stop_sequence_number ;
627
628 --Bug#7491598(ER,defer planned shipment iface): Cursor to fetch Trip Stop information for the all group ids (Order by trip_interface_id)
629 CURSOR c_tms_interface_stops_all IS
630 SELECT wtsi.stop_interface_id,
631 wtsi.stop_id, -- will be populated after create or update, not from GC3
632 wtsi.tp_stop_id,
633 wtsi.stop_location_id,
634 wtsi.stop_sequence_number,
635 wtsi.planned_arrival_date,
636 wtsi.planned_departure_date,
637 wtsi.departure_gross_weight,
638 wtsi.departure_net_weight,
639 wtsi.weight_uom_code,
640 wtsi.departure_volume,
641 wtsi.volume_uom_code,
642 wtsi.departure_seal_code,
643 wtsi.departure_fill_percent,
644 wtsi.wkend_layover_stops,
645 wtsi.wkday_layover_stops,
646 wtsi.shipments_type_flag,
647 wtsi.trip_interface_id,
648 wtsi.timezone_code,
649 'C' dml_action, -- indicates if stop has to be created or updated
650 null tp_attribute_category,
651 null tp_attribute1,
652 null tp_attribute2,
653 null tp_attribute3,
654 null tp_attribute4,
655 null tp_attribute5,
656 null tp_attribute6,
657 null tp_attribute7,
658 null tp_attribute8,
659 null tp_attribute9,
660 null tp_attribute10,
661 null tp_attribute11,
662 null tp_attribute12,
663 null tp_attribute13,
664 null tp_attribute14,
665 null tp_attribute15,
666 null attribute_category,
667 null attribute1,
668 null attribute2,
669 null attribute3,
670 null attribute4,
671 null attribute5,
672 null attribute6,
673 null attribute7,
674 null attribute8,
675 null attribute9,
676 null attribute10,
677 null attribute11,
678 null attribute12,
679 null attribute13,
680 null attribute14,
681 null attribute15
682 FROM wsh_trip_stops_interface wtsi,
683 wsh_trips_interface wti
684 WHERE wtsi.trip_interface_id = wti.trip_interface_id
685 AND wtsi.interface_action_code = G_TMS_RELEASE_CODE
686 AND wti.interface_action_code = G_TMS_RELEASE_CODE
687 ORDER BY wtsi.trip_interface_id,wtsi.stop_sequence_number ;
688
689
690 -- Cursor to fetch Delivery Leg information for the input group id(order by trip_interface_id)
691 CURSOR c_tms_interface_dlegs IS
692 SELECT wdli.delivery_leg_interface_id,
693 wdli.delivery_interface_id,
694 wtsi_pu.stop_location_id pickup_stop_location_id, -- pickup stop location
695 wtsi_pu.stop_sequence_number pickup_stop_sequence, -- pickup stop sequence
696 wtsi_do.stop_location_id dropoff_stop_location_id,-- dropoff stop location
697 wtsi_do.stop_sequence_number dropoff_stop_sequence, -- dropoff stop sequence
698 wtsi_pu.trip_interface_id, -- trip_interface_id
699 wdli.delivery_leg_id,
700 wdli.delivery_id,
701 wdli.pick_up_stop_interface_id,
702 wdli.drop_off_stop_interface_id,
703 wnd.weight_uom_code weight_uom,
704 wnd.volume_uom_code volume_uom,
705 wnd.organization_id, -- Organization id, used for deriving default UOM
706 wnd.tms_version_number, -- used before locking the delivery and rollback updates
707 wndi.tms_version_number otm_tms_version_number, -- OTM sent Version number
708 wnd.initial_pickup_location_id,-- used while logging exceptions
709 wnd.ultimate_dropoff_location_id,-- used for internal Locations
710 'N' processed_flag, -- used to indicate which deliveries have to be assigned
711 wnd.tms_interface_flag -- used to check whether tms version check can be avoided.
712 FROM wsh_del_legs_interface wdli,
713 wsh_new_del_interface wndi,
714 wsh_trip_stops_interface wtsi_pu,
715 wsh_trip_stops_interface wtsi_do,
716 wsh_trips_interface wti,
717 wsh_new_deliveries wnd
718 WHERE wti.group_id = p_group_id --p_group_id is the input parameter
719 AND wti.interface_action_code = G_TMS_RELEASE_CODE
720 AND wtsi_pu.trip_interface_id = wti.trip_interface_id
721 AND wtsi_pu.stop_interface_id = wdli.pick_up_stop_interface_id
722 AND wtsi_pu.interface_action_code = G_TMS_RELEASE_CODE
723 AND wtsi_do.trip_interface_id = wti.trip_interface_id
724 AND wtsi_do.stop_interface_id = wdli.drop_off_stop_interface_id
725 AND wtsi_do.interface_action_code = G_TMS_RELEASE_CODE
726 -- OTM R12 no need to have outer join
727 AND wnd.delivery_id = wdli.delivery_id
728 AND wndi.delivery_id = wdli.delivery_id
729 AND wdli.interface_action_code = G_TMS_RELEASE_CODE
730 --AND wnd.delivery_id(+) = wdli.delivery_id
731 --AND wnd.delivery_id = wndi.delivery_id
732 -- OTM R12
733 -- Fix Bug 5134725
734 --AND nvl(wnd.tms_version_number,-99) = nvl(wndi.tms_version_number,-99) -- version number check
735 ORDER BY wtsi_pu.trip_interface_id,wdli.sequence_number;
736
737 --Bug#7491598(ER,defer planned shipment iface): Cursor to fetch Delivery Leg information for the input plan (order by trip_interface_id)
738 CURSOR c_tms_interface_dlegs_plan(p_tp_plan_low varchar2,p_tp_plan_high varchar2) IS
739 SELECT wdli.delivery_leg_interface_id,
740 wdli.delivery_interface_id,
741 wtsi_pu.stop_location_id pickup_stop_location_id, -- pickup stop location
742 wtsi_pu.stop_sequence_number pickup_stop_sequence, -- pickup stop sequence
743 wtsi_do.stop_location_id dropoff_stop_location_id,-- dropoff stop location
744 wtsi_do.stop_sequence_number dropoff_stop_sequence, -- dropoff stop sequence
745 wtsi_pu.trip_interface_id, -- trip_interface_id
746 wdli.delivery_leg_id,
747 wdli.delivery_id,
748 wdli.pick_up_stop_interface_id,
749 wdli.drop_off_stop_interface_id,
750 wnd.weight_uom_code weight_uom,
751 wnd.volume_uom_code volume_uom,
752 wnd.organization_id, -- Organization id, used for deriving default UOM
753 wnd.tms_version_number, -- used before locking the delivery and rollback updates
754 wndi.tms_version_number otm_tms_version_number, -- OTM sent Version number
755 wnd.initial_pickup_location_id,-- used while logging exceptions
756 wnd.ultimate_dropoff_location_id,-- used for internal Locations
757 'N' processed_flag, -- used to indicate which deliveries have to be assigned
758 wnd.tms_interface_flag -- used to check whether tms version check can be avoided.
759 FROM wsh_del_legs_interface wdli,
760 wsh_new_del_interface wndi,
761 wsh_trip_stops_interface wtsi_pu,
762 wsh_trip_stops_interface wtsi_do,
763 wsh_trips_interface wti,
764 wsh_new_deliveries wnd
765 WHERE wti.tp_plan_name >= nvl(p_tp_plan_low,wti.tp_plan_name)
766 AND wti.tp_plan_name <= nvl(p_tp_plan_high,wti.tp_plan_name)
767 AND wti.interface_action_code = G_TMS_RELEASE_CODE
768 AND wtsi_pu.trip_interface_id = wti.trip_interface_id
769 AND wtsi_pu.stop_interface_id = wdli.pick_up_stop_interface_id
770 AND wtsi_pu.interface_action_code = G_TMS_RELEASE_CODE
771 AND wtsi_do.trip_interface_id = wti.trip_interface_id
772 AND wtsi_do.stop_interface_id = wdli.drop_off_stop_interface_id
773 AND wtsi_do.interface_action_code = G_TMS_RELEASE_CODE
774 -- OTM R12 no need to have outer join
775 AND wnd.delivery_id = wdli.delivery_id
776 AND wndi.delivery_id = wdli.delivery_id
777 AND wdli.interface_action_code = G_TMS_RELEASE_CODE
778 --AND wnd.delivery_id(+) = wdli.delivery_id
779 --AND wnd.delivery_id = wndi.delivery_id
780 -- OTM R12
781 -- Fix Bug 5134725
782 --AND nvl(wnd.tms_version_number,-99) = nvl(wndi.tms_version_number,-99) -- version number check
783 ORDER BY wtsi_pu.trip_interface_id,wdli.sequence_number;
784
785 --Bug#7491598(ER,defer planned shipment iface): Cursor to fetch Delivery Leg information for the input del(order by trip_interface_id)
786 CURSOR c_tms_interface_dlegs_del(p_del_name_low varchar2,p_del_name_high varchar2,l_organization_id NUMBER,l_del_pickup_date_low DATE,l_del_pickup_date_high DATE) IS
787 SELECT wdli.delivery_leg_interface_id,
788 wdli.delivery_interface_id,
789 wtsi_pu.stop_location_id pickup_stop_location_id, -- pickup stop location
790 wtsi_pu.stop_sequence_number pickup_stop_sequence, -- pickup stop sequence
791 wtsi_do.stop_location_id dropoff_stop_location_id,-- dropoff stop location
792 wtsi_do.stop_sequence_number dropoff_stop_sequence, -- dropoff stop sequence
793 wtsi_pu.trip_interface_id, -- trip_interface_id
794 wdli.delivery_leg_id,
795 wdli.delivery_id,
796 wdli.pick_up_stop_interface_id,
797 wdli.drop_off_stop_interface_id,
798 wnd.weight_uom_code weight_uom,
799 wnd.volume_uom_code volume_uom,
800 wnd.organization_id, -- Organization id, used for deriving default UOM
801 wnd.tms_version_number, -- used before locking the delivery and rollback updates
802 wndi.tms_version_number otm_tms_version_number, -- OTM sent Version number
803 wnd.initial_pickup_location_id,-- used while logging exceptions
804 wnd.ultimate_dropoff_location_id,-- used for internal Locations
805 'N' processed_flag, -- used to indicate which deliveries have to be assigned
806 wnd.tms_interface_flag -- used to check whether tms version check can be avoided.
807 FROM wsh_del_legs_interface wdli,
808 wsh_new_del_interface wndi,
809 wsh_trip_stops_interface wtsi_pu,
810 wsh_trip_stops_interface wtsi_do,
811 wsh_trips_interface wti,
812 wsh_new_deliveries wnd
813 WHERE wti.interface_action_code = G_TMS_RELEASE_CODE
814 AND wtsi_pu.trip_interface_id = wti.trip_interface_id
815 AND wtsi_pu.stop_interface_id = wdli.pick_up_stop_interface_id
816 AND wtsi_pu.interface_action_code = G_TMS_RELEASE_CODE
817 AND wtsi_do.trip_interface_id = wti.trip_interface_id
818 AND wtsi_do.stop_interface_id = wdli.drop_off_stop_interface_id
819 AND wtsi_do.interface_action_code = G_TMS_RELEASE_CODE
820 -- OTM R12 no need to have outer join
821 AND wnd.delivery_id = wdli.delivery_id
822 AND wndi.delivery_id = wdli.delivery_id
823 AND wdli.interface_action_code = G_TMS_RELEASE_CODE
824 AND wti.group_id in
825 (SELECT wti.group_id FROM wsh_new_del_interface wndi,
826 wsh_del_legs_interface wdli,
827 wsh_trip_stops_interface wtsi,
828 wsh_trips_interface wti,
829 wsh_new_deliveries wnd
830 where wndi.interface_action_code IN (G_TMS_RELEASE_CODE,G_TMS_DELETE_CODE)
831 AND wdli.delivery_interface_id = wndi.delivery_interface_id
832 AND wdli.pick_up_stop_interface_id = wtsi.stop_interface_id
833 AND wtsi.trip_interface_id = wti.trip_interface_id
834 AND wnd.delivery_id = wndi.delivery_id
835 AND wndi.name >= nvl(p_del_name_low,wndi.name)
836 AND wndi.name <= nvl(p_del_name_high,wndi.name)
837 AND wnd.organization_id = NVL(p_organization_id,wnd.organization_id)
838 AND wnd.initial_pickup_date >= nvl(l_del_pickup_date_low,wnd.initial_pickup_date)
839 AND wnd.initial_pickup_date <= nvl(l_del_pickup_date_high,wnd.initial_pickup_date))
840 ORDER BY wtsi_pu.trip_interface_id,wdli.sequence_number;
841
842 --Bug#7491598(ER,defer planned shipment iface): Cursor to fetch Delivery Leg information for all group ids(order by trip_interface_id)
843 CURSOR c_tms_interface_dlegs_all IS
844 SELECT wdli.delivery_leg_interface_id,
845 wdli.delivery_interface_id,
846 wtsi_pu.stop_location_id pickup_stop_location_id, -- pickup stop location
847 wtsi_pu.stop_sequence_number pickup_stop_sequence, -- pickup stop sequence
848 wtsi_do.stop_location_id dropoff_stop_location_id,-- dropoff stop location
849 wtsi_do.stop_sequence_number dropoff_stop_sequence, -- dropoff stop sequence
850 wtsi_pu.trip_interface_id, -- trip_interface_id
851 wdli.delivery_leg_id,
852 wdli.delivery_id,
853 wdli.pick_up_stop_interface_id,
854 wdli.drop_off_stop_interface_id,
855 wnd.weight_uom_code weight_uom,
856 wnd.volume_uom_code volume_uom,
857 wnd.organization_id, -- Organization id, used for deriving default UOM
858 wnd.tms_version_number, -- used before locking the delivery and rollback updates
859 wndi.tms_version_number otm_tms_version_number, -- OTM sent Version number
860 wnd.initial_pickup_location_id,-- used while logging exceptions
861 wnd.ultimate_dropoff_location_id,-- used for internal Locations
862 'N' processed_flag, -- used to indicate which deliveries have to be assigned
863 wnd.tms_interface_flag -- used to check whether tms version check can be avoided.
864 FROM wsh_del_legs_interface wdli,
865 wsh_new_del_interface wndi,
866 wsh_trip_stops_interface wtsi_pu,
867 wsh_trip_stops_interface wtsi_do,
868 wsh_trips_interface wti,
869 wsh_new_deliveries wnd
870 WHERE wti.interface_action_code = G_TMS_RELEASE_CODE
871 AND wtsi_pu.trip_interface_id = wti.trip_interface_id
872 AND wtsi_pu.stop_interface_id = wdli.pick_up_stop_interface_id
873 AND wtsi_pu.interface_action_code = G_TMS_RELEASE_CODE
874 AND wtsi_do.trip_interface_id = wti.trip_interface_id
875 AND wtsi_do.stop_interface_id = wdli.drop_off_stop_interface_id
876 AND wtsi_do.interface_action_code = G_TMS_RELEASE_CODE
877 -- OTM R12 no need to have outer join
878 AND wnd.delivery_id = wdli.delivery_id
879 AND wndi.delivery_id = wdli.delivery_id
880 AND wdli.interface_action_code = G_TMS_RELEASE_CODE
881 ORDER BY wtsi_pu.trip_interface_id,wdli.sequence_number;
882
883 -- Find Trip Stops
884 -- Will pick physical and dummy both, as they would be on
885 -- same trip
886 -- Further, use this same cursor to populate l_stop_local_tab
887 -- and override selected values in l_stop_info_tab
888 CURSOR c_get_stops (p_trip_id IN NUMBER) IS
889 SELECT stop_id,stop_location_id,stop_sequence_number,
890 physical_stop_id,physical_location_id,departure_gross_weight,departure_net_weight,
891 departure_volume,weight_uom_code,volume_uom_code,departure_seal_code,
892 departure_fill_percent,
893 tp_attribute_category,
894 tp_attribute1,
895 tp_attribute2,
896 tp_attribute3,
897 tp_attribute4,
898 tp_attribute5,
899 tp_attribute6,
900 tp_attribute7,
901 tp_attribute8,
902 tp_attribute9,
903 tp_attribute10,
904 tp_attribute11,
905 tp_attribute12,
906 tp_attribute13,
907 tp_attribute14,
908 tp_attribute15,
909 attribute_category,
910 attribute1,
911 attribute2,
912 attribute3,
913 attribute4,
914 attribute5,
915 attribute6,
916 attribute7,
917 attribute8,
918 attribute9,
919 attribute10,
920 attribute11,
921 attribute12,
922 attribute13,
923 attribute14,
924 attribute15
925 FROM wsh_trip_stops
926 WHERE trip_id = p_trip_id;
927
928 -- Find Deliveries associated to a stop
929 -- GC3 sends only physical stops back to EBS
930 -- 1st select is for deliveries with matching pickup
931 -- 2nd select is for deliveries with matching dropoff
932 -- 3rd select is for deliveries having dropoff as
933 -- dummy stop which is linked to this physical stop
934 CURSOR c_get_deliveries(p_stop_id IN NUMBER) IS
935 SELECT wdl.delivery_id, wnd.organization_id,wnd.tms_version_number
936 FROM wsh_delivery_legs wdl,
937 wsh_new_deliveries wnd
938 WHERE wdl.pick_up_stop_id = p_stop_id
939 AND wnd.delivery_id = wdl.delivery_id
940 UNION
941 SELECT wdl.delivery_id, wnd.organization_id,wnd.tms_version_number
942 FROM wsh_delivery_legs wdl,
943 wsh_new_deliveries wnd
944 WHERE wdl.drop_off_stop_id = p_stop_id
945 AND wnd.delivery_id = wdl.delivery_id
946 UNION
947 SELECT wdl.delivery_id, wnd.organization_id,wnd.tms_version_number
948 FROM wsh_delivery_legs wdl,
949 wsh_new_deliveries wnd,
950 wsh_trip_stops wts
951 WHERE wdl.drop_off_stop_id = wts.stop_id
952 AND wnd.delivery_id = wdl.delivery_id
953 AND wts.physical_stop_id = p_stop_id;
954
955
956 -- Find Delivery Legs for the delivery id
957 CURSOR c_get_dleg_id (p_delivery_id IN NUMBER) IS
958 SELECT delivery_leg_id
959 FROM wsh_delivery_legs
960 WHERE delivery_id = p_delivery_id;
961
962 -- Freight Cost Interface Data
963 CURSOR c_freight_int_cur (p_delivery_id IN NUMBER) IS
964 SELECT freight_cost_interface_id,
965 freight_cost_id,
966 freight_cost_type_id,
967 freight_cost_type_code,
968 unit_amount,
969 uom,
970 total_amount,
971 currency_code,
972 delivery_id
973 FROM wsh_freight_costs_interface
974 WHERE delivery_id = p_delivery_id
975 AND interface_action_code = G_TMS_RELEASE_CODE;
976
977 CURSOR c_get_currency_code(p_trip_interface_id IN NUMBER) IS
978 SELECT wc.currency_code, wti.carrier_id
979 FROM wsh_trips_interface wti,
980 wsh_carriers wc
981 WHERE wti.trip_interface_id = p_trip_interface_id
982 AND wti.carrier_id = wc.carrier_id;
983
984 -- OTM R12
985 CURSOR c_get_frcost_type_id(c_name IN VARCHAR2, c_fc_type_code IN VARCHAR2) IS
986 SELECT freight_cost_type_id
987 FROM wsh_freight_cost_types
988 --WHERE name = 'OTM Freight Cost'
989 WHERE name = c_name
990 AND freight_cost_type_code = c_fc_type_code;
991 --WHERE name = 'ORACLE TRANSPORTATION MANAGEMENT'
992 --AND freight_cost_type_code = 'FREIGHT';
993
994 -- Bug#7491598(ER,defer planned shipment iface): Lock each interace record. Becuase
995 -- records can be deleted by the receiveshipmentfromOTM BPEL process (via the API WSH_OTM_INBOUND_GRP.initiate_planned_shipment).
996 CURSOR c_lock_trip_interface(c_trip_interface_id NUMBER) IS
997 SELECT wti.trip_interface_id,
998 wndi.delivery_interface_id,
999 wdli.delivery_leg_interface_id,
1000 wtsi.stop_interface_id,
1001 wfci.freight_cost_interface_id
1002 from wsh_trips_interface wti ,
1003 wsh_trip_stops_interface wtsi ,
1004 wsh_del_legs_interface wdli ,
1005 wsh_freight_costs_interface wfci,
1006 wsh_new_del_interface wndi
1007 where wti.trip_interface_id = c_trip_interface_id --Bug7717569
1008 and wdli.delivery_interface_id = wndi.delivery_interface_id
1009 and wndi.delivery_interface_id = wfci.delivery_interface_id (+)
1010 and wndi.interface_action_code = 'TMS_RELEASE'
1011 and wdli.pick_up_stop_interface_id = wtsi.stop_interface_id
1012 and wtsi.trip_interface_id = wti.trip_interface_id
1013 FOR UPDATE NOWAIT;
1014
1015 l_lock_rec c_lock_trip_interface%ROWTYPE;
1016
1017 -- bug 6700792: OTM Dock Door App Sched Proj
1018 l_dock_appt_tab WMS_DOCK_APPOINTMENTS_PUB.DOCKAPPTTABTYPE;
1019 l_dock_appt_index NUMBER;
1020 --
1021
1022 l_trip_info_rec tms_trip_rec_type;
1023 l_stop_info_rec tms_stop_rec_type;
1024 l_dleg_info_rec tms_dleg_rec_type;
1025
1026 --l_del_wti_index NUMBER;
1027 l_trip_index NUMBER := 0;
1028 l_stop_index NUMBER := 0;
1029 l_dleg_index NUMBER := 0;
1030
1031 -- Variables to store information about various entities
1032 l_trip_info_tab tms_trip_tab_type;
1033 l_stop_info_tab tms_stop_tab_type;
1034 l_dleg_info_tab tms_dleg_tab_type;
1035 l_del_wti_tab WSH_UTIL_CORE.id_tab_type; -- OTM R12
1036
1037 l_stop_local_tab tms_stop_tab_type;
1038 l_dleg_local_tab tms_dleg_tab_type;
1039
1040 -- List of Delivery ids which are unassigned during the flow
1041 l_unassigned_delivery_id_tab WSH_UTIL_CORE.id_tab_type;
1042 l_unassigned_dlvy_version_tab WSH_UTIL_CORE.id_tab_type;
1043 l_delivery_info_tab WSH_NEW_DELIVERIES_PVT.Delivery_Attr_Tbl_Type;
1044 l_delivery_info WSH_NEW_DELIVERIES_PVT.Delivery_Rec_Type;
1045 l_new_interface_flag_tab WSH_UTIL_CORE.COLUMN_TAB_TYPE;
1046 l_unassigned_del_index NUMBER;
1047
1048 -- Populate Errors
1049 l_errors_tab interface_errors_tab_type;
1050
1051 l_return_status VARCHAR2(1);
1052 l_group_error_count NUMBER := 0;
1053
1054 -- Variables to store(new) ids after creation
1055 l_trip_id NUMBER;
1056 l_stop_id_tab WSH_UTIL_CORE.id_tab_type;
1057
1058 -- successful deliveries, used for freight costs and set in process_delivery section
1059 l_dlvy_id_tab WSH_UTIL_CORE.id_tab_type;
1060 l_dlvy_version_tab WSH_UTIL_CORE.id_tab_type;
1061
1062 l_ret_code BOOLEAN;
1063 l_temp BOOLEAN;
1064 l_completion_status VARCHAR2(30);
1065
1066 l_success_trips NUMBER;
1067 l_error_trips NUMBER;
1068
1069 -- OTM R12 populate record structure to call update_tms_interface_flag
1070 l_upd_dlvy_id_tab WSH_UTIL_CORE.id_tab_type;
1071 l_upd_dlvy_tms_tab WSH_UTIL_CORE.COLUMN_TAB_TYPE;
1072 -- OTM R12
1073 -- Bug#7491598(ER,defer planned shipment iface)
1074 l_del_pickup_date_low DATE;
1075 l_del_pickup_date_high DATE;
1076 -- Bug#7491598(ER,defer planned shipment iface)
1077
1078 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'RELEASE_PLANNED_SHIPMENT';
1079 --
1080 l_debug_on BOOLEAN;
1081 --
1082 BEGIN
1083
1084 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1085 --
1086 IF l_debug_on IS NULL THEN
1087 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1088 END IF;
1089
1090 IF l_debug_on THEN
1091 WSH_DEBUG_SV.push(l_module_name);
1092 WSH_DEBUG_SV.log(l_module_name, 'p_group_id', p_group_id);
1093 WSH_DEBUG_SV.log(l_module_name, 'p_latest_version', p_latest_version);
1094 WSH_DEBUG_SV.log(l_module_name, 'p_tp_plan_low', p_tp_plan_low);
1095 WSH_DEBUG_SV.log(l_module_name, 'p_tp_plan_high', p_tp_plan_high);
1096 WSH_DEBUG_SV.log(l_module_name, 'p_dummy', p_dummy);
1097 WSH_DEBUG_SV.log(l_module_name, 'p_organization_id', p_organization_id);
1098 WSH_DEBUG_SV.log(l_module_name, 'p_del_name_low', p_del_name_low);
1099 WSH_DEBUG_SV.log(l_module_name, 'p_del_name_high', p_del_name_high);
1100 WSH_DEBUG_SV.log(l_module_name, 'p_del_pickup_date_low', p_del_pickup_date_low);
1101 WSH_DEBUG_SV.log(l_module_name, 'p_del_pickup_date_high', p_del_pickup_date_high);
1102 WSH_DEBUG_SV.logmsg(l_module_name, 'START PROCESSING...');
1103 END IF;
1104 --Bug#7491598(ER,defer planned shipment iface)
1105 IF p_group_id IS NULL THEN
1106 --{
1107 -- pick up dates
1108 IF (p_del_pickup_date_low IS NOT NULL) THEN
1109 --{
1110 SELECT fnd_date.chardt_to_date(p_del_pickup_date_low)
1111 INTO l_del_pickup_date_low
1112 FROM dual;
1113 --}
1114 END IF;
1115 IF (p_del_pickup_date_high IS NOT NULL) THEN
1116 --{
1117 SELECT fnd_date.chardt_to_date(p_del_pickup_date_high)
1118 INTO l_del_pickup_date_high
1119 FROM dual;
1120 --}
1121 END IF;
1122 --} group id is null
1123 END IF;
1124
1125 IF l_debug_on THEN
1126 WSH_DEBUG_SV.log(l_module_name, 'l_del_pickup_date_low', l_del_pickup_date_low);
1127 WSH_DEBUG_SV.log(l_module_name, 'l_del_pickup_date_high', l_del_pickup_date_high);
1128 END IF;
1129
1130
1131 -- --Bug#7491598(ER,defer planned shipment iface): Group Id check needs to be removed.
1132 -- Check for p_group_id
1133 /*
1134 IF p_group_id IS NULL THEN
1135 FND_MESSAGE.SET_NAME('WSH','WSH_GROUP_ID_IS_REQUIRED');
1136 WSH_UTIL_CORE.PrintMsg(fnd_message.get);
1137 WSH_UTIL_CORE.ADD_MESSAGE(WSH_UTIL_CORE.G_RET_STS_ERROR);
1138 errbuf := 'Exception occurred in Release_Planned_Shipment';
1139 retcode := '2';
1140 IF l_debug_on THEN
1141 WSH_DEBUG_SV.logmsg(l_module_name, 'Group Id cannot be Null');
1142 WSH_DEBUG_SV.pop(l_module_name);
1143 END IF;
1144 RETURN;
1145 END IF;
1146 */
1147 l_success_trips := 0;
1148 l_error_trips := 0;
1149 l_completion_status := 'NORMAL';
1150
1151 -- Process for input group_id
1152 -- Initialize List of unassigned delivery ids, this list is used for a group id
1153 l_unassigned_delivery_id_tab.DELETE;
1154 l_unassigned_dlvy_version_tab.DELETE;
1155
1156 -- Initialize the tables for trips, stops and delivery legs
1157 -- index all the tables by trip_interface_id
1158 l_trip_info_tab.DELETE;
1159 l_stop_info_tab.DELETE;
1160 l_dleg_info_tab.DELETE;
1161 -- bug 6700792: OTM Dock Door App Sched Proj
1162 l_dock_appt_tab.DELETE;
1163 l_dock_appt_index := 0;
1164
1165 --SAVEPOINT process_group;
1166 --
1167
1168 --=======================================================================
1169 -- 1. Get All Trips Information
1170 -- Loop to find trips for the group_id
1171 IF l_debug_on THEN
1172 WSH_DEBUG_SV.logmsg(l_module_name,'===============================');
1173 WSH_DEBUG_SV.logmsg(l_module_name,'1. Gather Trips Interface data');
1174 WSH_DEBUG_SV.logmsg(l_module_name,'===============================');
1175 END IF;
1176 -- Bug#7491598(ER,defer planned shipment iface)
1177 IF (P_GROUP_ID is not null) THEN
1178 OPEN c_tms_interface_trips;
1179 ELSIF ( p_tp_plan_low is not NULL OR p_tp_plan_high is not NULL) THEN
1180 OPEN c_tms_interface_trips_plan(p_tp_plan_low,p_tp_plan_high);
1181 ELSIF ( p_del_name_low is not NULL OR p_del_name_high is not NULL OR p_organization_id IS NOT NULL OR l_del_pickup_date_low IS NOT NULL OR l_del_pickup_date_high IS NOT NULL) THEN
1182 OPEN c_tms_interface_trips_del(p_del_name_low,p_del_name_high,p_organization_id,l_del_pickup_date_low,l_del_pickup_date_high);
1183 ELSE
1184 OPEN c_tms_interface_trips_all;
1185 END IF;
1186
1187 LOOP --{
1188 IF (P_GROUP_ID is not null) THEN
1189 FETCH c_tms_interface_trips INTO l_trip_info_rec;
1190 EXIT WHEN c_tms_interface_trips%NOTFOUND;
1191 ELSIF ( p_tp_plan_low is not NULL OR p_tp_plan_high is not NULL) THEN
1192 FETCH c_tms_interface_trips_plan INTO l_trip_info_rec;
1193 EXIT WHEN c_tms_interface_trips_plan%NOTFOUND;
1194 ELSIF ( p_del_name_low is not NULL OR p_del_name_high is not NULL OR p_organization_id IS NOT NULL OR l_del_pickup_date_low IS NOT NULL OR l_del_pickup_date_high IS NOT NULL) THEN
1195 FETCH c_tms_interface_trips_del INTO l_trip_info_rec;
1196 EXIT WHEN c_tms_interface_trips_del%NOTFOUND;
1197 ELSE
1198 FETCH c_tms_interface_trips_all INTO l_trip_info_rec;
1199 EXIT WHEN c_tms_interface_trips_all%NOTFOUND;
1200 END IF;
1201 /*
1202 FETCH c_tms_interface_trips
1203 INTO l_trip_info_rec;
1204 EXIT WHEN c_tms_interface_trips%NOTFOUND; */
1205
1206 --l_trip_index := l_trip_info_rec.trip_interface_id;
1207 --l_trip_index := l_trip_index + 1;
1208 l_trip_index := l_trip_info_tab.count + 1;
1209 IF l_debug_on THEN
1210 WSH_DEBUG_SV.log(l_module_name,'Trip Interface id', l_trip_info_rec.trip_interface_id);
1211 END IF;
1212 --l_trip_info_tab(l_trip_info_tab.count + 1) := l_trip_info_rec;
1213 l_trip_info_tab(l_trip_index) := l_trip_info_rec;
1214
1215 -- OTM R12
1216 l_del_wti_tab(l_trip_index) := l_trip_info_rec.trip_interface_id;
1217
1218
1219 END LOOP;--}
1220 IF (P_GROUP_ID is not null) THEN
1221 CLOSE c_tms_interface_trips;
1222 ELSIF ( p_tp_plan_low is not NULL OR p_tp_plan_high is not NULL) THEN
1223 CLOSE c_tms_interface_trips_plan;
1224 ELSIF ( p_del_name_low is not NULL OR p_del_name_high is not NULL OR p_organization_id IS NOT NULL OR l_del_pickup_date_low IS NOT NULL OR l_del_pickup_date_high IS NOT NULL) THEN
1225 CLOSE c_tms_interface_trips_del;
1226 ELSE
1227 CLOSE c_tms_interface_trips_all;
1228 END IF;
1229 -- Bug#7491598(ER,defer planned shipment iface): end
1230 IF l_debug_on THEN
1231 WSH_DEBUG_SV.log(l_module_name,'Count Trip Info Tab:', l_trip_info_tab.count);
1232 END IF;
1233
1234 --=======================================================================
1235 -- 2. Get All Trip Stops Information(order by trip ids)
1236 -- Loop to find trip stops for the group_id
1237 IF l_debug_on THEN
1238 WSH_DEBUG_SV.logmsg(l_module_name,'===============================');
1239 WSH_DEBUG_SV.logmsg(l_module_name,'2. Gather Trip Stops Interface data');
1240 WSH_DEBUG_SV.logmsg(l_module_name,'===============================');
1241 END IF;
1242 -- --Bug#7491598(ER,defer planned shipment iface): Begin
1243 IF (P_GROUP_ID is not null) THEN
1244 OPEN c_tms_interface_stops;
1245 ELSIF ( p_tp_plan_low is not NULL OR p_tp_plan_high is not NULL) THEN
1246 OPEN c_tms_interface_stops_plan(p_tp_plan_low,p_tp_plan_high);
1247 ELSIF ( p_del_name_low is not NULL OR p_del_name_high is not NULL OR p_organization_id IS NOT NULL OR l_del_pickup_date_low IS NOT NULL OR l_del_pickup_date_high IS NOT NULL) THEN
1248 OPEN c_tms_interface_stops_del(p_del_name_low,p_del_name_high,p_organization_id,l_del_pickup_date_low,l_del_pickup_date_high);
1249 ELSE
1250 OPEN c_tms_interface_stops_all;
1251 END IF;
1252
1253 LOOP --{
1254 IF (P_GROUP_ID is not null) THEN
1255 FETCH c_tms_interface_stops INTO l_stop_info_rec;
1256 EXIT WHEN c_tms_interface_stops%NOTFOUND;
1257 ELSIF ( p_tp_plan_low is not NULL OR p_tp_plan_high is not NULL) THEN
1258 FETCH c_tms_interface_stops_plan INTO l_stop_info_rec;
1259 EXIT WHEN c_tms_interface_stops_plan%NOTFOUND;
1260 ELSIF ( p_del_name_low is not NULL OR p_del_name_high is not NULL OR p_organization_id IS NOT NULL OR l_del_pickup_date_low IS NOT NULL OR l_del_pickup_date_high IS NOT NULL) THEN
1261 FETCH c_tms_interface_stops_del INTO l_stop_info_rec;
1262 EXIT WHEN c_tms_interface_stops_del%NOTFOUND;
1263 ELSE
1264 FETCH c_tms_interface_stops_all INTO l_stop_info_rec;
1265 EXIT WHEN c_tms_interface_stops_all%NOTFOUND;
1266 END IF;
1267 /* FETCH c_tms_interface_stops
1268 INTO l_stop_info_rec;
1269 EXIT WHEN c_tms_interface_stops%NOTFOUND; */
1270 --l_stop_index := l_stop_index + 1;
1271 IF l_debug_on THEN
1272 WSH_DEBUG_SV.log(l_module_name,'Trip Interface id', l_stop_info_rec.trip_interface_id);
1273 WSH_DEBUG_SV.log(l_module_name,'Stop Interface id', l_stop_info_rec.stop_interface_id);
1274 WSH_DEBUG_SV.log(l_module_name,'Stop Location id', l_stop_info_rec.stop_location_id);
1275 WSH_DEBUG_SV.log(l_module_name,'Stop Sequence No.', l_stop_info_rec.stop_sequence_number);
1276 WSH_DEBUG_SV.log(l_module_name,'Timezone Code', l_stop_info_rec.timezone_code);
1277 END IF;
1278 l_stop_info_tab(l_stop_info_tab.count + 1) := l_stop_info_rec;
1279
1280 END LOOP;--}
1281 IF (P_GROUP_ID is not null) THEN
1282 CLOSE c_tms_interface_stops;
1283 ELSIF ( p_tp_plan_low is not NULL OR p_tp_plan_high is not NULL) THEN
1284 CLOSE c_tms_interface_stops_plan;
1285 ELSIF ( p_del_name_low is not NULL OR p_del_name_high is not NULL OR p_organization_id IS NOT NULL OR l_del_pickup_date_low IS NOT NULL OR l_del_pickup_date_high IS NOT NULL) THEN
1286 CLOSE c_tms_interface_stops_del;
1287 ELSE
1288 CLOSE c_tms_interface_stops_all;
1289 END IF;
1290
1291 IF l_debug_on THEN
1292 WSH_DEBUG_SV.log(l_module_name,'Count Trip Stop Info Tab:', l_stop_info_tab.count);
1293 END IF;
1294
1295 --=======================================================================
1296 -- 3. For each trip stop, get Delivery Leg information(order by trip ids)
1297 -- Loop to find delivery legs for the group_id
1298 IF l_debug_on THEN
1299 WSH_DEBUG_SV.logmsg(l_module_name,'===============================');
1300 WSH_DEBUG_SV.logmsg(l_module_name,'3. Gather Delivery Legs Interface data');
1301 WSH_DEBUG_SV.logmsg(l_module_name,'===============================');
1302 END IF;
1303 --Bug#7491598(ER,defer planned shipment iface): Begin
1304 IF (P_GROUP_ID is not null) THEN
1305 OPEN c_tms_interface_dlegs;
1306 ELSIF ( p_tp_plan_low is not NULL OR p_tp_plan_high is not NULL) THEN
1307 OPEN c_tms_interface_dlegs_plan(p_tp_plan_low,p_tp_plan_high);
1308 ELSIF ( p_del_name_low is not NULL OR p_del_name_high is not NULL OR p_organization_id IS NOT NULL OR l_del_pickup_date_low IS NOT NULL OR l_del_pickup_date_high IS NOT NULL) THEN
1309 OPEN c_tms_interface_dlegs_del(p_del_name_low,p_del_name_high,p_organization_id,l_del_pickup_date_low,l_del_pickup_date_high);
1310 ELSE
1311 OPEN c_tms_interface_dlegs_all;
1312 END IF;
1313
1314 LOOP --{
1315 IF (P_GROUP_ID is not null) THEN
1316 FETCH c_tms_interface_dlegs INTO l_dleg_info_rec;
1317 EXIT WHEN c_tms_interface_dlegs%NOTFOUND;
1318 ELSIF ( p_tp_plan_low is not NULL OR p_tp_plan_high is not NULL) THEN
1319 FETCH c_tms_interface_dlegs_plan INTO l_dleg_info_rec;
1320 EXIT WHEN c_tms_interface_dlegs_plan%NOTFOUND;
1321 ELSIF ( p_del_name_low is not NULL OR p_del_name_high is not NULL OR p_organization_id IS NOT NULL OR l_del_pickup_date_low IS NOT NULL OR l_del_pickup_date_high IS NOT NULL) THEN
1322 FETCH c_tms_interface_dlegs_del INTO l_dleg_info_rec;
1323 EXIT WHEN c_tms_interface_dlegs_del%NOTFOUND;
1324 ELSE
1325 FETCH c_tms_interface_dlegs_all INTO l_dleg_info_rec;
1326 EXIT WHEN c_tms_interface_dlegs_all%NOTFOUND;
1327 END IF;
1328 /*LOOP --{
1329 FETCH c_tms_interface_dlegs
1330 INTO l_dleg_info_rec;
1331 EXIT WHEN c_tms_interface_dlegs%NOTFOUND; */
1332 l_dleg_index := l_dleg_info_rec.trip_interface_id;
1333 --l_dleg_index := l_dleg_index + 1;
1334 IF l_debug_on THEN
1335 WSH_DEBUG_SV.log(l_module_name,'Trip Interface id', l_dleg_info_rec.trip_interface_id);
1336 WSH_DEBUG_SV.log(l_module_name,'Pickup Stop Interface id', l_dleg_info_rec.pick_up_stop_interface_id);
1337 WSH_DEBUG_SV.log(l_module_name,'Pickup Stop Location id', l_dleg_info_rec.pickup_stop_location_id);
1338 WSH_DEBUG_SV.log(l_module_name,'Dropoff Stop Interface id', l_dleg_info_rec.drop_off_stop_interface_id);
1339 WSH_DEBUG_SV.log(l_module_name,'Dropoff Stop Location id', l_dleg_info_rec.dropoff_stop_location_id);
1340 WSH_DEBUG_SV.log(l_module_name,'Delivery id', l_dleg_info_rec.delivery_id);
1341 WSH_DEBUG_SV.log(l_module_name,'EBS Version Number', l_dleg_info_rec.tms_version_number);
1342 WSH_DEBUG_SV.log(l_module_name,'OTM Version Number', l_dleg_info_rec.otm_tms_version_number);
1343 END IF;
1344
1345 l_dleg_info_tab(l_dleg_info_tab.count + 1) := l_dleg_info_rec;
1346 END LOOP;--}
1347
1348 IF (P_GROUP_ID is not null) THEN
1349 CLOSE c_tms_interface_dlegs;
1350 ELSIF ( p_tp_plan_low is not NULL OR p_tp_plan_high is not NULL) THEN
1351 CLOSE c_tms_interface_dlegs_plan;
1352 ELSIF ( p_del_name_low is not NULL OR p_del_name_high is not NULL OR p_organization_id IS NOT NULL OR l_del_pickup_date_low IS NOT NULL OR l_del_pickup_date_high IS NOT NULL) THEN
1353 CLOSE c_tms_interface_dlegs_del;
1354 ELSE
1355 CLOSE c_tms_interface_dlegs_all;
1356 END IF;
1357 --Bug#7491598(ER,defer planned shipment iface): end
1358 IF l_debug_on THEN
1359 WSH_DEBUG_SV.log(l_module_name,'Count Delivery Leg Info Tab:', l_dleg_info_tab.count);
1360 END IF;
1361
1362 -- OTM R12 Clean up for all trips in a bulk delete outside the loop
1363
1364 IF l_debug_on THEN
1365 WSH_DEBUG_SV.logmsg(l_module_name,'CLEAN UP EXISTING DATA FOR ALL TRIPS');
1366 END IF;
1367
1368 -- Delete Records in Interface errors table and exceptions table
1369 -- for the associated entities. This will ensure there are no duplicate
1370 -- or multiple errors and exceptions logged.
1371 -- Perform this for each trip_interface_id
1372
1373 -- OTM R12
1374 IF l_del_wti_tab.COUNT > 0 THEN
1375
1376 -- Errors are logged always for the deliveries
1377 --FORALL l_del_wti_index in INDICES OF l_del_wti_tab
1378 FORALL i in l_del_wti_tab.FIRST..l_del_wti_tab.LAST
1379 DELETE from wsh_interface_errors wie
1380 WHERE wie.interface_table_name = 'WSH_NEW_DEL_INTERFACE'
1381 AND wie.interface_id in (
1382 select wdli.delivery_interface_id
1383 from wsh_trip_stops_interface wtsi,
1384 wsh_del_legs_interface wdli
1385 where wtsi.trip_interface_id = l_del_wti_tab(i)
1386 and wtsi.stop_interface_id = wdli.pick_up_stop_interface_id
1387 and wtsi.interface_action_code = G_TMS_RELEASE_CODE
1388 and wdli.interface_action_code = G_TMS_RELEASE_CODE)
1389 AND wie.interface_action_code = G_TMS_RELEASE_CODE;
1390
1391 -- Exceptions are logged always for the deliveries
1392 FORALL i in l_del_wti_tab.FIRST..l_del_wti_tab.LAST
1393 DELETE from WSH_EXCEPTIONS we
1394 WHERE we.delivery_id in (
1395 select wdli.delivery_id
1396 from wsh_trip_stops_interface wtsi,
1397 wsh_del_legs_interface wdli
1398 where wtsi.trip_interface_id = l_del_wti_tab(i)
1399 and wtsi.stop_interface_id = wdli.pick_up_stop_interface_id
1400 and wtsi.interface_action_code = G_TMS_RELEASE_CODE
1401 and wdli.interface_action_code = G_TMS_RELEASE_CODE)
1402 and we.status = 'OPEN'
1403 and we.exception_name IN ('WSH_OTM_SHIPMENT_ERROR','WSH_OTM_INVALID_LOC');
1404
1405 END IF;
1406 -- OTM R12
1407
1408 --
1409 -- Specially committing the delete transactions above to avoid multiple errors
1410 -- and exceptions.
1411 -- This is for each trip, In case of multiple trips, the record would have been
1412 -- committed or rolledback at end of each transaction, so it will not overlap
1413 --
1414 COMMIT;
1415 --
1416 -- NO CREATE/UPDATE/DELETE transactions should be added before the above delete transaction
1417 --
1418 -- OTM R12
1419
1420 SAVEPOINT process_group;
1421 --=======================================================================
1422 -- 4. Loop thru all trips in l_trip_info_tab
1423 IF l_debug_on THEN
1424 WSH_DEBUG_SV.logmsg(l_module_name,'===============================');
1425 WSH_DEBUG_SV.logmsg(l_module_name,'4. START LOOPING THROUGH EACH TRIP');
1426 WSH_DEBUG_SV.logmsg(l_module_name,'===============================');
1427 END IF;
1428
1429 -- Trip count should be > 0
1430 IF l_trip_info_tab.count > 0 THEN--{
1431 FOR i in l_trip_info_tab.FIRST..l_trip_info_tab.LAST
1432 LOOP--{
1433
1434 l_dleg_local_tab.DELETE;
1435 l_stop_local_tab.DELETE;
1436 l_dlvy_id_tab.DELETE;
1437 -- OTM R12 Cleaning up will be done for all trips in a bulk delete outside the loop
1438
1439 SAVEPOINT process_single_trip;
1440
1441 IF l_debug_on THEN
1442 WSH_DEBUG_SV.logmsg(l_module_name,'Populate Delivery Legs Info in Local Table');
1443 END IF;
1444
1445 -- Loop through l_dleg_info_tab to find the deliveries for current trip
1446 IF l_dleg_info_tab.COUNT > 0 THEN
1447 FOR del_check in l_dleg_info_tab.FIRST..l_dleg_info_tab.LAST
1448 LOOP
1449 IF l_dleg_info_tab(del_check).trip_interface_id = l_trip_info_tab(i).trip_interface_id THEN
1450 -- Copy the relevant records in identical record structure
1451 l_dleg_local_tab(l_dleg_local_tab.count+1) := l_dleg_info_tab(del_check);
1452 END IF;
1453 END LOOP;
1454 END IF;
1455
1456 -- Locking Bug 5135606 for Trip, Stop,Dleg and Delivery
1457 -- Need to raise appropriate messages as they are not raised from the
1458 -- core APIs
1459 IF l_trip_info_tab(i).trip_id IS NOT NULL THEN--{
1460
1461 IF l_debug_on THEN
1462 WSH_DEBUG_SV.logmsg(l_module_name,'===============================');
1463 WSH_DEBUG_SV.logmsg(l_module_name,'4ai. LOCK TRIP:-'||l_trip_info_tab(i).trip_id);
1464 WSH_DEBUG_SV.logmsg(l_module_name,'===============================');
1465 END IF;
1466
1467 BEGIN
1468 -- Lock Trip
1469 WSH_TRIPS_PVT.lock_trip_no_compare
1470 (p_trip_id => l_trip_info_tab(i).trip_id);
1471 EXCEPTION
1472 WHEN OTHERS THEN
1473 FND_MESSAGE.SET_NAME('WSH','WSH_OTM_IB_LOCK_TRIP');
1474 FND_MESSAGE.SET_TOKEN('TRIP_NAME',WSH_TRIPS_PVT.get_name(l_trip_info_tab(i).trip_id));
1475 WSH_UTIL_CORE.ADD_MESSAGE(WSH_UTIL_CORE.G_RET_STS_ERROR);
1476 l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1477 IF l_debug_on THEN
1478 WSH_DEBUG_SV.log(l_module_name, 'trip not locked: l_return_status', l_return_status);
1479 END IF;
1480 GOTO trip_error;
1481 END;
1482
1483 -- 4a. Lock corresponding trip stops in EBS(for trip_id)
1484 -- Trip has to exist in EBS for locking this stops
1485 IF l_debug_on THEN
1486 WSH_DEBUG_SV.logmsg(l_module_name,'===============================');
1487 WSH_DEBUG_SV.logmsg(l_module_name,'4aii. LOCK TRIP STOP');
1488 WSH_DEBUG_SV.logmsg(l_module_name,'===============================');
1489 END IF;
1490 DECLARE
1491 l_stop_id NUMBER;
1492 BEGIN--{
1493 FOR rec in c_get_stops(l_trip_info_tab(i).trip_id)
1494 LOOP
1495 l_stop_id := rec.stop_id;
1496 wsh_trip_stops_pvt.lock_trip_stop_no_compare(
1497 p_stop_id => rec.stop_id);
1498 END LOOP;
1499
1500 EXCEPTION
1501 WHEN OTHERS THEN
1502 FND_MESSAGE.SET_NAME('WSH','WSH_OTM_IB_LOCK_STOP');
1503 FND_MESSAGE.SET_TOKEN('STOP_NAME',WSH_TRIP_STOPS_PVT.get_name(l_stop_id,'FTE_TMS_INTEGRATION'));
1504 WSH_UTIL_CORE.ADD_MESSAGE(WSH_UTIL_CORE.G_RET_STS_ERROR);
1505 l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1506 IF l_debug_on THEN
1507 WSH_DEBUG_SV.log(l_module_name, 'trip stop not locked: l_return_status', l_return_status);
1508 END IF;
1509 GOTO trip_error;
1510 END;--}
1511
1512 END IF;--}
1513
1514 -- 4b. Locking of Delivery and Delivery legs is required even when there is no trip in EBS
1515
1516 IF l_debug_on THEN
1517 WSH_DEBUG_SV.logmsg(l_module_name,'===============================');
1518 WSH_DEBUG_SV.logmsg(l_module_name,'4b. LOCK DELIVERY AND DELIVERY LEG');
1519 WSH_DEBUG_SV.logmsg(l_module_name,'===============================');
1520 END IF;
1521 IF l_dleg_local_tab.count > 0 THEN
1522 FOR m in l_dleg_local_tab.FIRST..l_dleg_local_tab.LAST
1523 -- Lock corresponding delivery legs in EBS
1524 LOOP--{
1525 -- Need to lock delivery legs even if the delivery was assigned to a different trip
1526 IF l_dleg_local_tab(m).trip_interface_id = l_trip_info_tab(i).trip_interface_id THEN--{
1527 BEGIN
1528 -- lock legs in this delivery
1529 wsh_delivery_legs_pvt.lock_dlvy_leg_no_compare(
1530 p_delivery_id => l_dleg_local_tab(m).delivery_id);
1531
1532 EXCEPTION
1533 WHEN OTHERS THEN
1534 FND_MESSAGE.SET_NAME('WSH','WSH_OTM_IB_LOCK_DLEG');
1535 FND_MESSAGE.SET_TOKEN('DELIVERY_NAME',wsh_new_deliveries_pvt.get_name(l_dleg_local_tab(m).delivery_id));
1536 WSH_UTIL_CORE.ADD_MESSAGE(WSH_UTIL_CORE.G_RET_STS_ERROR);
1537 l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1538 IF l_debug_on THEN
1539 WSH_DEBUG_SV.log(l_module_name, 'Delivery Leg not locked: l_return_status', l_return_status);
1540 END IF;
1541 GOTO trip_error;
1542 END;
1543
1544 -- Lock corresponding deliveries in EBS
1545 -- Compare the version number in the where clause of the cursor c_tms_interface_legs
1546 BEGIN
1547 wsh_new_deliveries_pvt.lock_dlvy_no_compare(
1548 p_delivery_id => l_dleg_local_tab(m).delivery_id);
1549 EXCEPTION
1550 WHEN OTHERS THEN
1551 FND_MESSAGE.SET_NAME('WSH','WSH_OTM_IB_LOCK_DLVY');
1552 FND_MESSAGE.SET_TOKEN('DELIVERY_NAME',wsh_new_deliveries_pvt.get_name(l_dleg_local_tab(m).delivery_id));
1553 WSH_UTIL_CORE.ADD_MESSAGE(WSH_UTIL_CORE.G_RET_STS_ERROR);
1554 l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1555 IF l_debug_on THEN
1556 WSH_DEBUG_SV.log(l_module_name, 'Delivery not locked: l_return_status', l_return_status);
1557 END IF;
1558 GOTO trip_error;
1559 END;
1560 END IF;--}
1561 END LOOP; --} -- across l_dleg_local_tab
1562 END IF;
1563 -- 4c. Process Trip
1564 -- Vehicle Derivation
1565 IF l_debug_on THEN
1566 WSH_DEBUG_SV.logmsg(l_module_name,'===============================');
1567 WSH_DEBUG_SV.logmsg(l_module_name,'4c. PROCESS TRIP');
1568 WSH_DEBUG_SV.logmsg(l_module_name,'===============================');
1569 END IF;
1570 DECLARE
1571
1572 l_rs VARCHAR2(1);
1573 l_msg_count NUMBER;
1574 l_msg_data VARCHAR2(32767);
1575
1576 l_trip_input_tab WSH_TRIPS_PVT.Trip_Attr_Tbl_Type;
1577 l_in_rec WSH_TRIPS_GRP.tripInRecType;
1578 l_trip_output_tab WSH_TRIPS_GRP.trip_Out_Tab_Type;
1579
1580 l_stop_location_id NUMBER;
1581 l_organization_tab WSH_UTIL_CORE.id_tab_type;
1582
1583 BEGIN--{
1584
1585 -- try to lock the trip interface records
1586 IF l_debug_on THEN
1587 WSH_DEBUG_SV.logmsg(l_module_name,'Locking the trip interface record:'||l_trip_info_tab(i).trip_interface_id);
1588 END IF;
1589 OPEN c_lock_trip_interface(l_trip_info_tab(i).trip_interface_id) ;
1590 FETCH c_lock_trip_interface INTO l_lock_rec;
1591 CLOSE c_lock_trip_interface;
1592 -- For each trip, Copy Values from l_trip_info_tab to l_trip_input_tab
1593
1594 -- Derive the Vehicle Item Id based on the name
1595 IF l_trip_info_tab(i).vehicle_item_name IS NOT NULL
1596 AND l_trip_info_tab(i).interface_action_code = G_TMS_RELEASE_CODE THEN--{
1597 IF l_debug_on THEN
1598 WSH_DEBUG_SV.logmsg(l_module_name,'Vehicle Item Name:'||l_trip_info_tab(i).vehicle_item_name);
1599 END IF;
1600 -- Get the first stop location from l_stop_info_tab
1601 -- l_stop_info would be populated
1602 FOR i_vehicle in l_stop_info_tab.FIRST..l_stop_info_tab.LAST
1603 LOOP--{
1604 IF l_stop_info_tab(i_vehicle).trip_interface_id = l_trip_info_tab(i).trip_interface_id
1605 THEN
1606 -- stop location id would be not null
1607 -- ECO 5008405, check here
1608 -- Stop Location id in Interface table is Varchar2
1609 -- need to convert it to number
1610 BEGIN --(
1611 l_stop_location_id :=
1612 to_number(l_stop_info_tab(i_vehicle).stop_location_id);
1613 EXCEPTION
1614 WHEN OTHERS THEN
1615 -- capture cases where stop_location_id is not a number
1616 -- special case, need to handle error a bit differently
1617 l_return_status := 'T';
1618 FND_MESSAGE.SET_NAME('WSH','WSH_OI_INVALID_LOCATION');
1619 WSH_UTIL_CORE.ADD_MESSAGE(WSH_UTIL_CORE.G_RET_STS_ERROR);
1620 IF l_debug_on THEN
1621 WSH_DEBUG_SV.log(l_module_name, 'Invalid Stop Location, l_return_status:', l_return_status);
1622 END IF;
1623 GOTO trip_error;
1624 END;--}
1625
1626 EXIT; -- exit the loop after 1st match
1627 END IF;
1628 END LOOP;--}
1629
1630 IF l_stop_location_id IS NULL THEN
1631 --l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1632 l_return_status := 'T';
1633 FND_MESSAGE.SET_NAME('WSH','WSH_OI_INVALID_LOCATION');
1634 WSH_UTIL_CORE.ADD_MESSAGE(WSH_UTIL_CORE.G_RET_STS_ERROR);
1635 IF l_debug_on THEN
1636 WSH_DEBUG_SV.log(l_module_name, 'Unable to Derive Stop Location', l_return_status);
1637 END IF;
1638 GOTO trip_error;
1639 END IF;
1640
1641 IF l_debug_on THEN
1642 WSH_DEBUG_SV.logmsg(l_module_name,'Calling wsh_util_core.get_org_from_location:'||l_stop_location_id);
1643 END IF;
1644 --get_org_from_location
1645 WSH_UTIL_CORE.get_org_from_location(
1646 p_location_id => l_stop_location_id,
1647 x_organization_tab => l_organization_tab,
1648 x_return_status => l_rs);
1649
1650 IF l_debug_on THEN
1651 WSH_DEBUG_SV.logmsg(l_module_name,'return status:'||l_rs);
1652 WSH_DEBUG_SV.logmsg(l_module_name,'Organizations Count:'||l_organization_tab.Count);
1653 END IF;
1654
1655 --Bug 5931958. Added the OR condition l_organization_tab.COUNT = 0
1656 IF (l_rs IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) OR
1657 (l_organization_tab.COUNT = 0 ))THEN
1658 l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1659 FND_MESSAGE.SET_NAME('WSH','WSH_LOC_ORG_UNDEFINED');
1660 WSH_UTIL_CORE.ADD_MESSAGE(WSH_UTIL_CORE.G_RET_STS_ERROR);
1661 IF l_debug_on THEN
1662 WSH_DEBUG_SV.log(l_module_name, 'Error: Failed to get Organization from stop location id ',
1663 l_stop_location_id );
1664 END IF;
1665 GOTO trip_error;
1666 END IF;
1667
1668 -- Assign first record in organization table
1669 l_trip_info_tab(i).vehicle_organization_id := l_organization_tab(l_organization_tab.FIRST);
1670 IF l_debug_on THEN
1671 WSH_DEBUG_SV.logmsg(l_module_name,'Calling WSH_EXTERNAL_INTERFACE_SV.Validate_Item');
1672 END IF;
1673
1674 WSH_EXTERNAL_INTERFACE_SV.Validate_Item
1675 (p_concatenated_segments => l_trip_info_tab(i).vehicle_item_name,
1676 p_organization_id => l_organization_tab(l_organization_tab.FIRST),
1677 x_inventory_item_id => l_trip_info_tab(i).vehicle_item_id,
1678 x_return_status => l_rs);
1679
1680 IF l_debug_on THEN
1681 WSH_DEBUG_SV.logmsg(l_module_name,'return status:'||l_rs);
1682 WSH_DEBUG_SV.logmsg(l_module_name,'Vehicle Item id:'||l_trip_info_tab(i).vehicle_item_id);
1683 END IF;
1684
1685 IF l_rs IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
1686 l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1687 FND_MESSAGE.SET_NAME('WSH','WSH_VEHICLE_TYPE_UNDEFINED');
1688 FND_MESSAGE.SET_TOKEN('ITEM',l_trip_info_tab(i).vehicle_item_name);
1689 FND_MESSAGE.SET_TOKEN('ORGANIZATION',
1690 WSH_UTIL_CORE.get_org_name (p_organization_id =>l_organization_tab(l_organization_tab.FIRST)));
1691 WSH_UTIL_CORE.ADD_MESSAGE(WSH_UTIL_CORE.G_RET_STS_ERROR);
1692 IF l_debug_on THEN
1693 WSH_DEBUG_SV.log(l_module_name, 'Validate Item Failed: l_return_status', l_return_status);
1694 END IF;
1695 GOTO trip_error;
1696 END IF;
1697
1698 END IF;--}
1699
1700 IF l_debug_on THEN
1701 WSH_DEBUG_SV.logmsg(l_module_name,'Vehicle Item id:'||l_trip_info_tab(i).vehicle_item_id);
1702 WSH_DEBUG_SV.logmsg(l_module_name,'Vehicle Org:'||l_trip_info_tab(i).vehicle_organization_id);
1703 WSH_DEBUG_SV.logmsg(l_module_name,'Vehicle Num Prefix:'||l_trip_info_tab(i).vehicle_num_prefix);
1704 WSH_DEBUG_SV.logmsg(l_module_name,'Vehicle Number:'||l_trip_info_tab(i).vehicle_number);
1705 WSH_DEBUG_SV.logmsg(l_module_name,'Carrier id:'||l_trip_info_tab(i).carrier_id);
1706 WSH_DEBUG_SV.logmsg(l_module_name,'Mode of Transport:'||l_trip_info_tab(i).mode_of_transport);
1707 WSH_DEBUG_SV.logmsg(l_module_name,'Service Level:'||l_trip_info_tab(i).service_level);
1708 WSH_DEBUG_SV.logmsg(l_module_name,'Tp Plan Name:'||l_trip_info_tab(i).tp_plan_name);
1709 WSH_DEBUG_SV.logmsg(l_module_name,'Interface Action Code:'||l_trip_info_tab(i).interface_action_code);
1710 END IF;
1711
1712 --
1713 -- This logic would be only for G_TMS_DELETE_CODE
1714 --
1715 -- HANDLE INTERFACE_ACTION_CODE of DELETE HERE...and UNASSIGN THE DELIVERIES in EBS
1716 IF l_trip_info_tab(i).interface_action_code = G_TMS_DELETE_CODE THEN--{
1717
1718 DECLARE
1719 l_delivery_tab WSH_TMS_RELEASE.delivery_tab;
1720 l_delivery_id_tab WSH_UTIL_CORE.id_tab_type;
1721
1722 l_del_attrs WSH_NEW_DELIVERIES_PVT.delivery_attr_tbl_type;
1723 l_del_action_prms WSH_DELIVERIES_GRP.action_parameters_rectype;
1724 l_del_action_rec WSH_DELIVERIES_GRP.delivery_action_out_rec_type;
1725 l_del_defaults WSH_DELIVERIES_GRP.default_parameters_rectype;
1726
1727 l_rs VARCHAR2(1);
1728 l_msg_count NUMBER;
1729 l_msg_data VARCHAR2(32767);
1730
1731 BEGIN
1732 -- Find EBS deliveries which are currently assigned to this GC3 trip
1733 -- Delete Shipment would be sent only for existing trips!
1734 find_deliveries_for_trip(
1735 p_trip_id => l_trip_info_tab(i).trip_id,
1736 p_tp_plan_name => NULL,
1737 x_delivery_tab => l_delivery_tab, -- dlvy_interface_id + tms_version_number
1738 x_delivery_id_tab => l_delivery_id_tab, -- dlvy ids
1739 x_return_status => l_rs);
1740
1741 IF l_debug_on THEN
1742 WSH_DEBUG_SV.logmsg(l_module_name,'Return Status after find_deliveries_for_trip'||l_rs);
1743 END IF;
1744
1745 -- Handle return status here !!!
1746 IF l_rs IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
1747 l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1748 IF l_debug_on THEN
1749 WSH_DEBUG_SV.log(l_module_name, 'Find Deliveries for Trip Failed,l_return_status:', l_return_status);
1750 END IF;
1751 GOTO trip_error;
1752 END IF;
1753
1754 -- All the deliveries are assigned to sametrip, so combine them for single call
1755 IF l_delivery_id_tab.count > 0 THEN--{
1756 FOR del_rec IN l_delivery_id_tab.FIRST..l_delivery_id_tab.LAST
1757 LOOP--{
1758 -- For each delivery, derive the organization id
1759 -- No information is passed from GC3, count of l_delivery_tab and l_delivery_id_tab
1760 -- is identical
1761 l_del_attrs(l_del_attrs.count + 1).delivery_id := l_delivery_id_tab(del_rec);
1762 l_del_attrs(l_del_attrs.count).organization_id :=
1763 l_delivery_tab(l_delivery_id_tab(del_rec)).organization_id;
1764
1765 IF l_delivery_tab(l_delivery_id_tab(del_rec)).status_code = 'OP' THEN
1766 l_unassigned_delivery_id_tab(l_unassigned_delivery_id_tab.count + 1) :=
1767 l_delivery_id_tab(del_rec);
1768 l_unassigned_dlvy_version_tab(l_unassigned_delivery_id_tab.count) :=
1769 l_delivery_tab(l_delivery_id_tab(del_rec)).tms_version_number;
1770 IF l_debug_on THEN
1771 WSH_DEBUG_SV.logmsg(l_module_name,'Status of Delivery '||
1772 l_delivery_id_tab(del_rec)||'-->'||l_delivery_tab(l_delivery_id_tab(del_rec)).status_code);
1773 END IF;
1774 END IF;
1775 END LOOP; --}
1776 IF l_debug_on THEN
1777 WSH_DEBUG_SV.logmsg(l_module_name,'Deliveries to be unassigned:'||l_del_attrs.count);
1778 WSH_DEBUG_SV.logmsg(l_module_name,'Overall Unassign Count:'||l_unassigned_delivery_id_tab.count);
1779 WSH_DEBUG_SV.logmsg(l_module_name,'Unassign Version Count:'||l_unassigned_dlvy_version_tab.count);
1780 END IF;
1781
1782 l_del_action_prms.caller := 'FTE_TMS_INTEGRATION'; --'FTE_TMS_RELEASE';
1783 l_del_action_prms.action_code := 'UNASSIGN-TRIP';
1784 l_del_action_prms.trip_id := l_trip_info_tab(i).trip_id;
1785
1786 WSH_DELIVERIES_GRP.delivery_action(
1787 p_api_version_number => 1.0,
1788 p_init_msg_list => FND_API.G_TRUE,
1789 p_commit => FND_API.G_FALSE,
1790 p_action_prms => l_del_action_prms,
1791 p_rec_attr_tab => l_del_attrs,
1792 x_delivery_out_rec => l_del_action_rec,
1793 x_defaults_rec => l_del_defaults,
1794 x_return_status => l_rs,
1795 x_msg_count => l_msg_count,
1796 x_msg_data => l_msg_data);
1797
1798 IF l_debug_on THEN
1799 WSH_DEBUG_SV.logmsg(l_module_name,'Return Status after Unassign Delivery'||l_rs);
1800 END IF;
1801 IF l_rs IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
1802 l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1803 IF l_debug_on THEN
1804 WSH_DEBUG_SV.log(l_module_name, 'Unassign Delivery from Trip Failed: l_return_status', l_return_status);
1805 END IF;
1806 GOTO trip_error;
1807 END IF;
1808 END IF;--} -- delivery_id_tab.count
1809 END;
1810 -- if above unassignment was success, proceed to next trip
1811 l_success_trips := l_success_trips + 1;
1812 EXIT;--skip to next trip
1813 END IF;--} -- interface_action_code is DELETE
1814
1815 --
1816 -- This logic would be only for G_TMS_RELEASE_CODE
1817 --
1818
1819 l_trip_input_tab(1).VEHICLE_NUM_PREFIX := l_trip_info_tab(i).vehicle_num_prefix;
1820 l_trip_input_tab(1).VEHICLE_NUMBER := l_trip_info_tab(i).vehicle_number;
1821 l_trip_input_tab(1).VEHICLE_ITEM_ID := l_trip_info_tab(i).vehicle_item_id;
1822 l_trip_input_tab(1).VEHICLE_ORGANIZATION_ID := l_trip_info_tab(i).vehicle_organization_id;
1823
1824 l_trip_input_tab(1).trip_id := l_trip_info_tab(i).trip_id;
1825 l_trip_input_tab(1).CARRIER_ID := l_trip_info_tab(i).carrier_id;
1826 l_trip_input_tab(1).SHIP_METHOD_CODE := l_trip_info_tab(i).ship_method_code;
1827 l_trip_input_tab(1).ROUTE_ID := l_trip_info_tab(i).route_id;
1828 l_trip_input_tab(1).ROUTING_INSTRUCTIONS := l_trip_info_tab(i).routing_instructions;
1829 l_trip_input_tab(1).SERVICE_LEVEL := l_trip_info_tab(i).service_level;
1830 l_trip_input_tab(1).MODE_OF_TRANSPORT := l_trip_info_tab(i).mode_of_transport;
1831 l_trip_input_tab(1).FREIGHT_TERMS_CODE := l_trip_info_tab(i).freight_terms_code;
1832 l_trip_input_tab(1).SEAL_CODE := l_trip_info_tab(i).seal_code;
1833 l_trip_input_tab(1).TP_PLAN_NAME := l_trip_info_tab(i).tp_plan_name;
1834 l_trip_input_tab(1).TP_TRIP_NUMBER := l_trip_info_tab(i).tp_trip_number;
1835 l_trip_input_tab(1).SHIPMENTS_TYPE_FLAG := l_trip_info_tab(i).shipments_type_flag;
1836 l_trip_input_tab(1).BOOKING_NUMBER := l_trip_info_tab(i).booking_number;
1837 l_trip_input_tab(1).VESSEL := l_trip_info_tab(i).vessel;
1838 l_trip_input_tab(1).VOYAGE_NUMBER := l_trip_info_tab(i).voyage_number;
1839 l_trip_input_tab(1).PORT_OF_LOADING := l_trip_info_tab(i).port_of_loading;
1840 l_trip_input_tab(1).PORT_OF_DISCHARGE := l_trip_info_tab(i).port_of_discharge;
1841 l_trip_input_tab(1).CARRIER_CONTACT_ID := l_trip_info_tab(i).carrier_contact_id;
1842 l_trip_input_tab(1).SHIPPER_WAIT_TIME := l_trip_info_tab(i).shipper_wait_time;
1843 l_trip_input_tab(1).WAIT_TIME_UOM := l_trip_info_tab(i).wait_time_uom;
1844 l_trip_input_tab(1).CARRIER_RESPONSE := l_trip_info_tab(i).carrier_response;
1845 l_trip_input_tab(1).OPERATOR := l_trip_info_tab(i).operator;
1846 l_trip_input_tab(1).IGNORE_FOR_PLANNING := 'N';
1847 l_trip_input_tab(1).attribute_category := l_trip_info_tab(i).attribute_category;
1848 l_trip_input_tab(1).attribute1 := l_trip_info_tab(i).attribute1;
1849 l_trip_input_tab(1).attribute2 := l_trip_info_tab(i).attribute2;
1850 l_trip_input_tab(1).attribute3 := l_trip_info_tab(i).attribute3;
1851 l_trip_input_tab(1).attribute4 := l_trip_info_tab(i).attribute4;
1852 l_trip_input_tab(1).attribute5 := l_trip_info_tab(i).attribute5;
1853 l_trip_input_tab(1).attribute6 := l_trip_info_tab(i).attribute6;
1854 l_trip_input_tab(1).attribute7 := l_trip_info_tab(i).attribute7;
1855 l_trip_input_tab(1).attribute8 := l_trip_info_tab(i).attribute8;
1856 l_trip_input_tab(1).attribute9 := l_trip_info_tab(i).attribute9;
1857 l_trip_input_tab(1).attribute10 := l_trip_info_tab(i).attribute10;
1858 l_trip_input_tab(1).attribute11 := l_trip_info_tab(i).attribute11;
1859 l_trip_input_tab(1).attribute12 := l_trip_info_tab(i).attribute12;
1860 l_trip_input_tab(1).attribute13 := l_trip_info_tab(i).attribute13;
1861 l_trip_input_tab(1).attribute14 := l_trip_info_tab(i).attribute14;
1862 l_trip_input_tab(1).attribute15 := l_trip_info_tab(i).attribute15;
1863
1864 IF l_trip_info_tab(i).trip_id IS NOT NULL THEN--{
1865 --trip already exists in EBS
1866 l_in_rec.action_code := 'UPDATE';
1867 l_trip_input_tab(1).NAME := WSH_TRIPS_PVT.get_name(l_trip_info_tab(i).trip_id);
1868 ELSE -- trip doesnot exist in EBS
1869 l_in_rec.action_code := 'CREATE';
1870 l_trip_input_tab(1).NAME := l_trip_info_tab(i).name;
1871 END IF; --}
1872
1873 l_in_rec.caller := 'FTE_TMS_INTEGRATION';
1874
1875 IF l_debug_on THEN
1876 WSH_DEBUG_SV.logmsg(l_module_name,'Calling WSH_TRIPS_GRP.Create_Update_Trip:'||l_in_rec.action_code);
1877 END IF;
1878
1879 WSH_TRIPS_GRP.Create_Update_Trip(
1880 p_api_version_number => 1.0,
1881 p_init_msg_list => FND_API.G_TRUE,
1882 p_commit => FND_API.G_FALSE,
1883 x_return_status => l_rs,
1884 x_msg_count => l_msg_count,
1885 x_msg_data => l_msg_data,
1886 p_trip_info_tab => l_trip_input_tab,
1887 p_In_rec => l_in_rec,
1888 x_Out_tab => l_trip_output_tab);
1889
1890
1891 IF l_rs IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
1892 l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1893 IF l_debug_on THEN
1894 WSH_DEBUG_SV.log(l_module_name, 'Create Update Trip Failed: l_return_status', l_return_status);
1895 END IF;
1896 GOTO trip_error;
1897 END IF;
1898
1899 -- Trip id after create or update
1900 IF l_trip_output_tab.count > 0 AND l_in_rec.action_code = 'CREATE' THEN
1901 l_trip_id := l_trip_output_tab(l_trip_output_tab.FIRST).trip_id;
1902
1903 ELSIF l_in_rec.action_code = 'UPDATE' THEN -- trip_id is already known
1904 l_trip_id := l_trip_info_tab(i).trip_id;
1905 END IF;
1906
1907 IF l_debug_on THEN
1908 WSH_DEBUG_SV.logmsg(l_module_name,'Create_Update_Trip,return status:'||l_rs);
1909 WSH_DEBUG_SV.logmsg(l_module_name,'Trip id:'||l_trip_id);
1910 END IF;
1911
1912 END;--} process trip
1913
1914
1915 -- 4d. Process Trip Stops
1916 -- populate stop UOM in case of creation
1917 IF l_debug_on THEN
1918 WSH_DEBUG_SV.logmsg(l_module_name,'===============================');
1919 WSH_DEBUG_SV.logmsg(l_module_name,'4d. PROCESS TRIP STOPS');
1920 WSH_DEBUG_SV.logmsg(l_module_name,'===============================');
1921 END IF;
1922
1923 DECLARE
1924 -- Find Deliveries associated to a stop
1925 l_stop_matches VARCHAR2(1); -- stop sent by GC3 matches stop in EBS
1926 l_stop_delete_tab WSH_UTIL_CORE.id_tab_type; -- stops to be deleted
1927 --l_dlvy_unassign_tab WSH_UTIL_CORE.id_tab_type; -- deliveries to be unassigned
1928
1929 l_del_attrs WSH_NEW_DELIVERIES_PVT.delivery_attr_tbl_type;
1930 l_del_action_prms WSH_DELIVERIES_GRP.action_parameters_rectype;
1931 l_del_action_rec WSH_DELIVERIES_GRP.delivery_action_out_rec_type;
1932 l_del_defaults WSH_DELIVERIES_GRP.default_parameters_rectype;
1933
1934 l_stop_attrs WSH_TRIP_STOPS_PVT.stop_attr_tbl_type;
1935 l_stop_action_prms WSH_TRIP_STOPS_GRP.action_parameters_rectype;
1936 l_stop_action_rec WSH_TRIP_STOPS_GRP.StopActionOutRecType;
1937 l_stop_defaults WSH_TRIP_STOPS_GRP.default_parameters_rectype;
1938 l_stop_in_rec WSH_TRIP_STOPS_GRP.stopInRecType;
1939 l_stop_out_tab WSH_TRIP_STOPS_GRP.stop_out_tab_type;
1940 l_stop_wt_vol_out_tab WSH_TRIP_STOPS_GRP.stop_wt_vol_tab_type;
1941
1942 l_del_count NUMBER;
1943 l_stop_count NUMBER;
1944
1945 l_rs VARCHAR2(1);
1946 l_msg_count NUMBER;
1947 l_msg_data VARCHAR2(32767);
1948 l_location_id NUMBER;
1949 l_is_duplicate VARCHAR2(1);
1950 l_physical_loc_id NUMBER;
1951
1952 BEGIN--{
1953
1954
1955 -- Populate the trip stops for this trip in a different table
1956 -- Further processing will use a table with less records
1957 -- l_stop_info_tab would not be null
1958 l_stop_local_tab.DELETE;
1959 FOR stop_count IN l_stop_info_tab.FIRST..l_stop_info_tab.LAST
1960 LOOP--{
1961 IF l_stop_info_tab(stop_count).trip_interface_id = l_trip_info_tab(i).trip_interface_id THEN
1962 -- Add ECO 5008405 here to validate stop location!!!
1963 -- If location_id is NULL or invalid, log exception
1964 -- and delete data relevant to this trip_interface_id,
1965 BEGIN --(
1966 l_location_id
1967 := to_number(l_stop_info_tab(stop_count).stop_location_id);
1968 EXCEPTION
1969 WHEN OTHERS THEN
1970 -- capture cases where stop_location_id is not a number
1971 -- special case, need to handle error a bit differently
1972 l_return_status := 'T';
1973 FND_MESSAGE.SET_NAME('WSH','WSH_OI_INVALID_LOCATION');
1974 WSH_UTIL_CORE.ADD_MESSAGE(WSH_UTIL_CORE.G_RET_STS_ERROR);
1975 IF l_debug_on THEN
1976 WSH_DEBUG_SV.log(l_module_name, 'Invalid Stop Location, l_return_status:', l_return_status);
1977 END IF;
1978 GOTO trip_error;
1979 END;--}
1980
1981
1982 WSH_UTIL_VALIDATE.VALIDATE_LOCATION
1983 (p_location_id =>l_location_id,
1984 x_return_status => l_rs);
1985
1986 IF l_rs IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
1987 l_return_status := 'T';-- special case, need to handle error a bit differently
1988 FND_MESSAGE.SET_NAME('WSH','WSH_OI_INVALID_LOCATION');
1989 WSH_UTIL_CORE.ADD_MESSAGE(WSH_UTIL_CORE.G_RET_STS_ERROR);
1990 IF l_debug_on THEN
1991 WSH_DEBUG_SV.log(l_module_name, 'Invalid Stop Location, l_return_status:', l_return_status);
1992 END IF;
1993 GOTO trip_error;
1994 END IF;
1995
1996 l_stop_local_tab(l_stop_local_tab.count + 1) := l_stop_info_tab(stop_count);
1997 END IF;
1998 END LOOP;--}
1999
2000 IF l_trip_info_tab(i).trip_id IS NOT NULL THEN--{
2001
2002 -- Get trip stops in EBS
2003 -- Find the EBS stops and compare against data being sent
2004 -- by GC3, if existing EBS stops donot match new GC3 sent
2005 -- stops, then delete the stops and unassign associated
2006 -- deliveries
2007 FOR rec in c_get_stops(l_trip_info_tab(i).trip_id)
2008 LOOP--{
2009 IF l_debug_on THEN
2010 WSH_DEBUG_SV.logmsg(l_module_name,'===== EBS STOPS =======');
2011 WSH_DEBUG_SV.logmsg(l_module_name,'Stop id:'||rec.stop_id);
2012 WSH_DEBUG_SV.logmsg(l_module_name,'Location id:'||rec.stop_location_id);
2013 WSH_DEBUG_SV.logmsg(l_module_name,'Stop Seq Num:'||rec.stop_sequence_number);
2014 WSH_DEBUG_SV.logmsg(l_module_name,'Physical Stop id:'||rec.physical_stop_id);
2015 WSH_DEBUG_SV.logmsg(l_module_name,'Physical Location id:'||rec.physical_location_id);
2016 END IF;
2017
2018 l_stop_matches := 'N';
2019 FOR stop_index in l_stop_local_tab.FIRST..l_stop_local_tab.LAST
2020 LOOP--{
2021 IF l_debug_on THEN
2022 WSH_DEBUG_SV.logmsg(l_module_name,'===== GC3 STOP DESCRIPTION =======');
2023 WSH_DEBUG_SV.logmsg(l_module_name,'Location id:'||l_stop_local_tab(stop_index).stop_location_id);
2024 WSH_DEBUG_SV.logmsg(l_module_name,'Stop Seq Num:'||l_stop_local_tab(stop_index).stop_sequence_number);
2025 END IF;
2026
2027 -- Compare the GC3 sent stop location + stop sequence number
2028 IF to_number(l_stop_local_tab(stop_index).stop_location_id) = rec.stop_location_id AND
2029 l_stop_local_tab(stop_index).stop_sequence_number = rec.stop_sequence_number
2030 THEN--{
2031 -- Physical Stop Match
2032 -- Stop with matching Location + Sequence Number in GC3 and EBS
2033 l_stop_matches := 'Y';
2034 -- Stop_id would normally be null and dml_action be 'C'
2035 -- But for update scenario, mark it as U and with stop id
2036 l_stop_local_tab(stop_index).dml_action := 'U';
2037 l_stop_local_tab(stop_index).stop_id := rec.stop_id;
2038
2039 -- Populate other fields to ensure they are not overridden during Update
2040 l_stop_local_tab(stop_index).departure_gross_weight := rec.departure_gross_weight;
2041 l_stop_local_tab(stop_index).departure_net_weight := rec.departure_net_weight;
2042 l_stop_local_tab(stop_index).departure_volume := rec.departure_volume;
2043 l_stop_local_tab(stop_index).weight_uom_code := rec.weight_uom_code;
2044 l_stop_local_tab(stop_index).volume_uom_code := rec.volume_uom_code;
2045 l_stop_local_tab(stop_index).departure_seal_code := rec.departure_seal_code;
2046 l_stop_local_tab(stop_index).departure_fill_percent := rec.departure_fill_percent;
2047
2048 l_stop_local_tab(stop_index).tp_attribute_category := rec.tp_attribute_category;
2049 l_stop_local_tab(stop_index).tp_attribute1 := rec.tp_attribute1;
2050 l_stop_local_tab(stop_index).tp_attribute2 := rec.tp_attribute2;
2051 l_stop_local_tab(stop_index).tp_attribute3 := rec.tp_attribute3;
2052 l_stop_local_tab(stop_index).tp_attribute4 := rec.tp_attribute4;
2053 l_stop_local_tab(stop_index).tp_attribute5 := rec.tp_attribute5;
2054 l_stop_local_tab(stop_index).tp_attribute6 := rec.tp_attribute6;
2055 l_stop_local_tab(stop_index).tp_attribute7 := rec.tp_attribute7;
2056 l_stop_local_tab(stop_index).tp_attribute8 := rec.tp_attribute8;
2057 l_stop_local_tab(stop_index).tp_attribute9 := rec.tp_attribute9;
2058 l_stop_local_tab(stop_index).tp_attribute10 := rec.tp_attribute10;
2059 l_stop_local_tab(stop_index).tp_attribute11 := rec.tp_attribute11;
2060 l_stop_local_tab(stop_index).tp_attribute12 := rec.tp_attribute12;
2061 l_stop_local_tab(stop_index).tp_attribute13 := rec.tp_attribute13;
2062 l_stop_local_tab(stop_index).tp_attribute14 := rec.tp_attribute14;
2063 l_stop_local_tab(stop_index).tp_attribute15 := rec.tp_attribute15;
2064
2065 l_stop_local_tab(stop_index).attribute_category := rec.attribute_category;
2066 l_stop_local_tab(stop_index).attribute1 := rec.attribute1;
2067 l_stop_local_tab(stop_index).attribute2 := rec.attribute2;
2068 l_stop_local_tab(stop_index).attribute3 := rec.attribute3;
2069 l_stop_local_tab(stop_index).attribute4 := rec.attribute4;
2070 l_stop_local_tab(stop_index).attribute5 := rec.attribute5;
2071 l_stop_local_tab(stop_index).attribute6 := rec.attribute6;
2072 l_stop_local_tab(stop_index).attribute7 := rec.attribute7;
2073 l_stop_local_tab(stop_index).attribute8 := rec.attribute8;
2074 l_stop_local_tab(stop_index).attribute9 := rec.attribute9;
2075 l_stop_local_tab(stop_index).attribute10 := rec.attribute10;
2076 l_stop_local_tab(stop_index).attribute11 := rec.attribute11;
2077 l_stop_local_tab(stop_index).attribute12 := rec.attribute12;
2078 l_stop_local_tab(stop_index).attribute13 := rec.attribute13;
2079 l_stop_local_tab(stop_index).attribute14 := rec.attribute14;
2080 l_stop_local_tab(stop_index).attribute15 := rec.attribute15;
2081
2082 -- End of Populating other fields
2083 IF l_debug_on THEN
2084 WSH_DEBUG_SV.logmsg(l_module_name,'Matching Stop found,id:'||rec.stop_id);
2085 END IF;
2086 EXIT; --from the l_stop_local_tab loop
2087 ELSE -- also check for Internal Location
2088
2089 -- If the EBS Stop has the dummy location for incoming GC3 stop
2090 -- then convert EBS Stop Location to physical location and then
2091 -- compare
2092 WSH_LOCATIONS_PKG.convert_internal_cust_location(
2093 p_internal_cust_location_id => rec.stop_location_id,
2094 --p_internal_cust_location_id => l_stop_local_tab(stop_index).stop_location_id,
2095 x_internal_org_location_id => l_physical_loc_id,
2096 x_return_status => l_rs);
2097
2098 -- Treat this also as Invalid Stop Location
2099 IF l_rs IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
2100 l_return_status := 'T';-- special case, need to handle error a bit differently
2101 FND_MESSAGE.SET_NAME('WSH','WSH_OI_INVALID_LOCATION');
2102 WSH_UTIL_CORE.ADD_MESSAGE(WSH_UTIL_CORE.G_RET_STS_ERROR);
2103 IF l_debug_on THEN
2104 WSH_DEBUG_SV.log(l_module_name, 'Invalid Stop Location, l_return_status:', l_return_status);
2105 END IF;
2106 GOTO trip_error;
2107 END IF;
2108
2109 IF l_debug_on THEN
2110 WSH_DEBUG_SV.log(l_module_name, 'Physical Location:',l_physical_loc_id);
2111 END IF;
2112
2113 -- Compare the GC3 sent stop location + stop sequence number
2114 IF to_number(l_stop_local_tab(stop_index).stop_location_id) = l_physical_loc_id AND
2115 l_stop_local_tab(stop_index).stop_sequence_number = rec.stop_sequence_number
2116 THEN--{
2117 -- Physical Stop Match
2118 -- Stop with matching Location + Sequence Number in GC3 and EBS
2119 l_stop_matches := 'Y';
2120 -- Stop_id would normally be null and dml_action be 'C'
2121 -- But for update scenario, mark it as U and with stop id
2122 l_stop_local_tab(stop_index).dml_action := 'U';
2123 l_stop_local_tab(stop_index).stop_id := rec.stop_id;
2124
2125 -- Populate other fields to ensure they are not overridden during Update
2126 l_stop_local_tab(stop_index).departure_gross_weight := rec.departure_gross_weight;
2127 l_stop_local_tab(stop_index).departure_net_weight := rec.departure_net_weight;
2128 l_stop_local_tab(stop_index).departure_volume := rec.departure_volume;
2129 l_stop_local_tab(stop_index).weight_uom_code := rec.weight_uom_code;
2130 l_stop_local_tab(stop_index).volume_uom_code := rec.volume_uom_code;
2131 l_stop_local_tab(stop_index).departure_seal_code := rec.departure_seal_code;
2132 l_stop_local_tab(stop_index).departure_fill_percent := rec.departure_fill_percent;
2133
2134 l_stop_local_tab(stop_index).tp_attribute_category := rec.tp_attribute_category;
2135 l_stop_local_tab(stop_index).tp_attribute1 := rec.tp_attribute1;
2136 l_stop_local_tab(stop_index).tp_attribute2 := rec.tp_attribute2;
2137 l_stop_local_tab(stop_index).tp_attribute3 := rec.tp_attribute3;
2138 l_stop_local_tab(stop_index).tp_attribute4 := rec.tp_attribute4;
2139 l_stop_local_tab(stop_index).tp_attribute5 := rec.tp_attribute5;
2140 l_stop_local_tab(stop_index).tp_attribute6 := rec.tp_attribute6;
2141 l_stop_local_tab(stop_index).tp_attribute7 := rec.tp_attribute7;
2142 l_stop_local_tab(stop_index).tp_attribute8 := rec.tp_attribute8;
2143 l_stop_local_tab(stop_index).tp_attribute9 := rec.tp_attribute9;
2144 l_stop_local_tab(stop_index).tp_attribute10 := rec.tp_attribute10;
2145 l_stop_local_tab(stop_index).tp_attribute11 := rec.tp_attribute11;
2146 l_stop_local_tab(stop_index).tp_attribute12 := rec.tp_attribute12;
2147 l_stop_local_tab(stop_index).tp_attribute13 := rec.tp_attribute13;
2148 l_stop_local_tab(stop_index).tp_attribute14 := rec.tp_attribute14;
2149 l_stop_local_tab(stop_index).tp_attribute15 := rec.tp_attribute15;
2150
2151 l_stop_local_tab(stop_index).attribute_category := rec.attribute_category;
2152 l_stop_local_tab(stop_index).attribute1 := rec.attribute1;
2153 l_stop_local_tab(stop_index).attribute2 := rec.attribute2;
2154 l_stop_local_tab(stop_index).attribute3 := rec.attribute3;
2155 l_stop_local_tab(stop_index).attribute4 := rec.attribute4;
2156 l_stop_local_tab(stop_index).attribute5 := rec.attribute5;
2157 l_stop_local_tab(stop_index).attribute6 := rec.attribute6;
2158 l_stop_local_tab(stop_index).attribute7 := rec.attribute7;
2159 l_stop_local_tab(stop_index).attribute8 := rec.attribute8;
2160 l_stop_local_tab(stop_index).attribute9 := rec.attribute9;
2161 l_stop_local_tab(stop_index).attribute10 := rec.attribute10;
2162 l_stop_local_tab(stop_index).attribute11 := rec.attribute11;
2163 l_stop_local_tab(stop_index).attribute12 := rec.attribute12;
2164 l_stop_local_tab(stop_index).attribute13 := rec.attribute13;
2165 l_stop_local_tab(stop_index).attribute14 := rec.attribute14;
2166 l_stop_local_tab(stop_index).attribute15 := rec.attribute15;
2167 -- End of Populating other fields
2168
2169 IF l_debug_on THEN
2170 WSH_DEBUG_SV.logmsg(l_module_name,'Matching Stop found,id:'||rec.stop_id);
2171 END IF;
2172 EXIT; --from the l_stop_local_tab loop
2173 END IF;--}
2174 END IF;--}
2175 END LOOP;--} -- looping thru l_stop_local_tab
2176
2177 -- No incoming stop from GC3 on this trip matches existing stop in EBS
2178 IF l_stop_matches = 'N' THEN--{
2179 --
2180 -- Need to unassign any deliveries associates to this stop and then
2181 -- delete this stop in EBS.
2182 -- The call to Delete Stops will delete the delivery legs and remove any
2183 -- association between delivery and trip.
2184 -- But the GC3 need is to unassign these deliveries from trip and mark them
2185 -- with tms_interface_flag = 'CREATION_REQUIRED' on successful operation.
2186 -- The deliveries will be left as include for planning.
2187 --
2188 -- i. Find deliveries associated with this stop id and then unassign
2189 -- using loop instead of bulk fetch, as a loop would be required to populate
2190 -- l_del_attrs.delivery_id
2191 l_del_attrs.DELETE;
2192 FOR del_rec in c_get_deliveries(rec.stop_id)
2193 LOOP
2194 -- Need to make sure this delivery has not already been selected
2195 -- For example, if GC3 sends request to delete 2 trip stops which
2196 -- are linked to 1 delivery, then donot launch unassign-trip twice
2197 l_is_duplicate := 'N';
2198
2199 IF l_debug_on THEN
2200 WSH_DEBUG_SV.logmsg(l_module_name,'Unassign Dlvy Tab count:'||l_unassigned_delivery_id_tab.count);
2201 END IF;
2202 IF l_del_attrs.count > 0 THEN
2203 FOR duplicate in l_del_attrs.FIRST..l_del_attrs.LAST
2204 LOOP
2205 IF l_del_attrs(duplicate).delivery_id = del_rec.delivery_id THEN
2206 -- Duplicate record found
2207 l_is_duplicate := 'Y';
2208 END IF;
2209 END LOOP;
2210 END IF;
2211
2212 IF l_debug_on THEN
2213 WSH_DEBUG_SV.logmsg(l_module_name,'Is Duplicate:'||l_is_duplicate);
2214 END IF;
2215
2216 -- Only increase the count for non-duplicate deliveries
2217 -- Cursor would have selected deliveries with dummy stops, Internal Location
2218 IF l_is_duplicate = 'N' THEN
2219 l_del_count := l_del_attrs.count + 1;
2220 l_del_attrs(l_del_count).delivery_id := del_rec.delivery_id;
2221 l_del_attrs(l_del_count).organization_id := del_rec.organization_id;
2222 l_unassigned_delivery_id_tab(l_unassigned_delivery_id_tab.count + 1) :=
2223 del_rec.delivery_id;
2224 l_unassigned_dlvy_version_tab(l_unassigned_delivery_id_tab.count) :=
2225 del_rec.tms_version_number;
2226
2227 END IF;
2228
2229 IF l_debug_on THEN
2230 WSH_DEBUG_SV.logmsg(l_module_name,'After populating l_del_attrs count:'||l_del_attrs.count);
2231 WSH_DEBUG_SV.logmsg(l_module_name,'After populating Unassign Dlvy Tab count:'||l_unassigned_delivery_id_tab.count);
2232 END IF;
2233
2234 END LOOP;
2235
2236 IF l_debug_on THEN
2237 WSH_DEBUG_SV.logmsg(l_module_name,'Deliveries associated to the stop:'||l_del_attrs.count);
2238 END IF;
2239
2240 IF l_del_attrs.count > 0 THEN--{
2241 l_del_action_prms.caller := 'FTE_TMS_INTEGRATION'; --'FTE_TMS_RELEASE';
2242 l_del_action_prms.action_code := 'UNASSIGN-TRIP';
2243 l_del_action_prms.trip_id := l_trip_info_tab(i).trip_id;
2244
2245 WSH_DELIVERIES_GRP.delivery_action(
2246 p_api_version_number => 1.0,
2247 p_init_msg_list => FND_API.G_TRUE,
2248 p_commit => FND_API.G_FALSE,
2249 p_action_prms => l_del_action_prms,
2250 p_rec_attr_tab => l_del_attrs,
2251 x_delivery_out_rec => l_del_action_rec,
2252 x_defaults_rec => l_del_defaults,
2253 x_return_status => l_rs,
2254 x_msg_count => l_msg_count,
2255 x_msg_data => l_msg_data);
2256
2257 IF l_debug_on THEN
2258 WSH_DEBUG_SV.logmsg(l_module_name,'Return Status after Unassign Delivery'||l_rs);
2259 END IF;
2260
2261 -- Handle error here !!!, should these deliveries be deleted from unassign table list??
2262 IF l_rs IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
2263 l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2264 IF l_debug_on THEN
2265 WSH_DEBUG_SV.log(l_module_name, 'Unassign Delivery Failed: l_return_status', l_return_status);
2266 END IF;
2267 GOTO trip_error;
2268 END IF;
2269 END IF;--}
2270
2271 -- ii. Call WSH_TRIP_STOPS_GRP.Stop_Action() to DELETE
2272 l_stop_action_prms.caller := 'FTE_TMS_INTEGRATION';
2273 l_stop_action_prms.action_code := 'DELETE';
2274 l_stop_attrs(1).stop_id := rec.stop_id;
2275 l_stop_attrs(1).trip_id := l_trip_info_tab(i).trip_id;
2276
2277 WSH_TRIP_STOPS_GRP.stop_action(
2278 p_api_version_number => 1.0,
2279 p_init_msg_list => FND_API.G_TRUE,
2280 p_commit => FND_API.G_FALSE,
2281 p_action_prms => l_stop_action_prms,
2282 p_rec_attr_tab => l_stop_attrs,
2283 x_stop_out_rec => l_stop_action_rec,
2284 x_def_rec => l_stop_defaults,
2285 x_return_status => l_rs,
2286 x_msg_count => l_msg_count,
2287 x_msg_data => l_msg_data);
2288
2289 -- Handle return status here, treat warning as success
2290 IF l_debug_on THEN
2291 WSH_DEBUG_SV.logmsg(l_module_name,'Return Status after deleting stop'||l_rs);
2292 END IF;
2293 IF l_rs IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
2294 -- go to trip_error
2295 l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2296 IF l_debug_on THEN
2297 WSH_DEBUG_SV.log(l_module_name, 'Delete Trip Stop Failed: l_return_status', l_return_status);
2298 END IF;
2299 GOTO trip_error;
2300 END IF;
2301 END IF;--} -- if l_stop_matches = 'N'
2302
2303 END LOOP;--} -- processing pre-existing stops existing in EBS for the GC3 trip
2304
2305 END IF;--} -- if l_trip_info_tab(i).trip_id is not null, trip pre-existed in EBS
2306
2307 IF l_debug_on THEN
2308 WSH_DEBUG_SV.logmsg(l_module_name,'Before Internal Locations Check, Stop Local Table, Count'||l_stop_local_tab.count);
2309 END IF;
2310
2311 -- Internal Locations Check here
2312 -- Call Process Internal Locations Here
2313 IF l_dleg_local_tab.count > 0 AND l_stop_local_tab.count > 0 THEN--{
2314 process_internal_locations
2315 (x_delivery_tab => l_dleg_local_tab,
2316 x_stop_tab => l_stop_local_tab,
2317 x_return_status => l_rs);
2318
2319 IF l_rs IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
2320 l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2321 IF l_debug_on THEN
2322 WSH_DEBUG_SV.log(l_module_name, 'Process Internal Location, l_return_status:', l_return_status);
2323 END IF;
2324 GOTO trip_error;
2325 END IF;
2326 END IF;--}
2327
2328 IF l_debug_on THEN
2329 WSH_DEBUG_SV.logmsg(l_module_name,'After Internal Locations Check,Stop Local Table, Count'||l_stop_local_tab.count);
2330 END IF;
2331
2332 -- Loop across l_stop_local_tab for Create and Update stops
2333 -- Initialize l_stop_attrs
2334 -- l_stop_local_tab would not be not null
2335 FOR stop_rec IN l_stop_local_tab.FIRST..l_stop_local_tab.LAST
2336 LOOP--{
2337 -- Find stops based on the trip_interface_id link and process only these
2338 -- Look at removing this later as l_stop_local_tab is only for the selected trip
2339 -- !!!! REMOVE condition !!!
2340 IF l_stop_local_tab(stop_rec).trip_interface_id = l_trip_info_tab(i).trip_interface_id
2341 THEN--{
2342
2343 l_stop_attrs.DELETE;
2344 -- Group API does not accept create and update together
2345 -- as action can either be CREATE or UPDATE
2346 l_stop_in_rec.caller := 'FTE_TMS_INTEGRATION';
2347
2348 IF l_stop_local_tab(stop_rec).dml_action = 'C' THEN--{
2349 -- action is CREATE
2350 l_stop_in_rec.action_code := 'CREATE';
2351 ELSE -- dml_action = 'U'
2352 -- action is UPDATE
2353 l_stop_in_rec.action_code := 'UPDATE';
2354 END IF; --}
2355
2356 l_stop_count := l_stop_attrs.count + 1;
2357
2358 l_stop_attrs(l_stop_count).STOP_ID := l_stop_local_tab(stop_rec).stop_id;
2359 l_stop_attrs(l_stop_count).TP_STOP_ID := l_stop_local_tab(stop_rec).tp_stop_id;
2360 l_stop_attrs(l_stop_count).TRIP_ID := l_trip_id;
2361 --
2362 l_stop_attrs(l_stop_count).STOP_LOCATION_ID := to_number(l_stop_local_tab(stop_rec).stop_location_id);
2363 l_stop_attrs(l_stop_count).STOP_SEQUENCE_NUMBER := l_stop_local_tab(stop_rec).stop_sequence_number;
2364 --
2365 -- ECO 5101760
2366 -- Convert Stop level planned dates, based on the timezone code
2367 IF l_debug_on THEN
2368 WSH_DEBUG_SV.log(l_module_name,'OTM sent Planned Arrival Date',l_stop_local_tab(stop_rec).planned_arrival_date);
2369 WSH_DEBUG_SV.log(l_module_name,'OTM sent Planned Departure Date',l_stop_local_tab(stop_rec).planned_departure_date);
2370 END IF;
2371
2372 get_server_time
2373 (p_source_time => l_stop_local_tab(stop_rec).planned_arrival_date,
2374 p_source_timezone_code => l_stop_local_tab(stop_rec).timezone_code,
2375 x_server_time => l_stop_local_tab(stop_rec).PLANNED_ARRIVAL_DATE,
2376 x_return_status => l_rs,
2377 x_msg_count => l_msg_count,
2378 x_msg_data => l_msg_data);
2379
2380 IF l_rs IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
2381 l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2382 IF l_debug_on THEN
2383 WSH_DEBUG_SV.log(l_module_name, 'Get Server Time for Planned Arrival Date, l_return_status:', l_return_status);
2384 END IF;
2385 GOTO trip_error;
2386 END IF;
2387
2388 get_server_time
2389 (p_source_time => l_stop_local_tab(stop_rec).planned_departure_date,
2390 p_source_timezone_code => l_stop_local_tab(stop_rec).timezone_code,
2391 x_server_time => l_stop_local_tab(stop_rec).PLANNED_DEPARTURE_DATE,
2392 x_return_status => l_rs,
2393 x_msg_count => l_msg_count,
2394 x_msg_data => l_msg_data);
2395
2396 IF l_rs IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
2397 l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2398 IF l_debug_on THEN
2399 WSH_DEBUG_SV.log(l_module_name, 'Get Server Time for Planned Arrival Date, l_return_status:', l_return_status);
2400 END IF;
2401 GOTO trip_error;
2402 END IF;
2403
2404 l_stop_attrs(l_stop_count).PLANNED_ARRIVAL_DATE := l_stop_local_tab(stop_rec).planned_arrival_date;
2405 l_stop_attrs(l_stop_count).PLANNED_DEPARTURE_DATE := l_stop_local_tab(stop_rec).planned_departure_date;
2406
2407 IF l_debug_on THEN
2408 WSH_DEBUG_SV.log(l_module_name,'Converted Planned Arrival Date',l_stop_attrs(l_stop_count).PLANNED_ARRIVAL_DATE);
2409 WSH_DEBUG_SV.log(l_module_name,'Converted Planned Departure Date',l_stop_attrs(l_stop_count).PLANNED_departure_DATE);
2410 END IF;
2411 -- End of ECO 5101760
2412 --
2413 l_stop_attrs(l_stop_count).DEPARTURE_GROSS_WEIGHT := nvl(l_stop_local_tab(stop_rec).departure_gross_weight,0);
2414 l_stop_attrs(l_stop_count).DEPARTURE_NET_WEIGHT := nvl(l_stop_local_tab(stop_rec).departure_net_weight,0);
2415 l_stop_attrs(l_stop_count).DEPARTURE_VOLUME := nvl(l_stop_local_tab(stop_rec).departure_volume,0);
2416 l_stop_attrs(l_stop_count).DEPARTURE_SEAL_CODE := l_stop_local_tab(stop_rec).departure_seal_code;
2417 l_stop_attrs(l_stop_count).DEPARTURE_FILL_PERCENT := l_stop_local_tab(stop_rec).departure_fill_percent;
2418 l_stop_attrs(l_stop_count).WKEND_LAYOVER_STOPS := l_stop_local_tab(stop_rec).wkend_layover_stops;
2419 l_stop_attrs(l_stop_count).WKDAY_LAYOVER_STOPS := l_stop_local_tab(stop_rec).wkday_layover_stops;
2420 l_stop_attrs(l_stop_count).SHIPMENTS_TYPE_FLAG := l_stop_local_tab(stop_rec).shipments_type_flag;
2421 --l_stop_attrs(l_stop_count).weight_uom_code := l_stop_local_tab(stop_rec).weight_uom_code;
2422 --l_stop_attrs(l_stop_count).volume_uom_code := l_stop_local_tab(stop_rec).volume_uom_code;
2423
2424 --UOM to be derived from delivery for new trip stops only
2425 IF l_stop_local_tab(stop_rec).stop_id IS NULL THEN--{
2426 -- For the stop_interface_id in l_stop_local_tab(stop_rec), find delivery uom
2427 IF l_dleg_local_tab.COUNT > 0 THEN
2428 FOR dleg_rec IN l_dleg_local_tab.FIRST..l_dleg_local_tab.LAST
2429 LOOP--{
2430 IF l_dleg_local_tab(dleg_rec).pick_up_stop_interface_id = l_stop_local_tab(stop_rec).stop_interface_id THEN
2431 -- For 1st delivery, get the organization id
2432 IF l_debug_on THEN
2433 WSH_DEBUG_SV.logmsg(l_module_name,'Calling WSH_WV_UTILS.GET_DEFAULT_UOMS for pickup');
2434 END IF;
2435 --
2436 wsh_wv_utils.get_default_uoms
2437 (p_organization_id => l_dleg_local_tab(dleg_rec).organization_id,
2438 x_weight_uom_code => l_stop_local_tab(stop_rec).weight_uom_code,
2439 x_volume_uom_code => l_stop_local_tab(stop_rec).volume_uom_code,
2440 x_return_status => l_rs);
2441 IF l_debug_on THEN
2442 WSH_DEBUG_SV.logmsg(l_module_name,'Return Status from WSH_WV_UTILS.GET_DEFAULT_UOMS'||l_rs);
2443 END IF;
2444 -- Handle return Status error!!!
2445 IF l_rs IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
2446 l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2447 IF l_debug_on THEN
2448 WSH_DEBUG_SV.log(l_module_name, 'Get_Default_UOM failed: l_return_status', l_return_status);
2449 END IF;
2450 GOTO trip_error;
2451 END IF;
2452
2453 IF l_debug_on THEN
2454 WSH_DEBUG_SV.logmsg(l_module_name,'WT UOM:'||l_stop_local_tab(stop_rec).weight_uom_code||'VOLUME UOM:'||l_stop_local_tab(stop_rec).volume_uom_code);
2455 END IF;
2456 ELSE -- to find UOM for dropoff stops, which are not pickups
2457 IF l_dleg_local_tab(dleg_rec).drop_off_stop_interface_id = l_stop_local_tab(stop_rec).stop_interface_id THEN--{
2458 -- For 1st delivery, get the organization id
2459 IF l_debug_on THEN
2460 WSH_DEBUG_SV.logmsg(l_module_name,'Calling WSH_WV_UTILS.GET_DEFAULT_UOMS for dropoff');
2461 END IF;
2462 --
2463 wsh_wv_utils.get_default_uoms
2464 (p_organization_id => l_dleg_local_tab(dleg_rec).organization_id,
2465 x_weight_uom_code => l_stop_local_tab(stop_rec).weight_uom_code,
2466 x_volume_uom_code => l_stop_local_tab(stop_rec).volume_uom_code,
2467 x_return_status => l_rs);
2468 IF l_debug_on THEN
2469 WSH_DEBUG_SV.logmsg(l_module_name,'Return Status from WSH_WV_UTILS.GET_DEFAULT_UOMS'||l_rs);
2470 END IF;
2471 IF l_rs IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
2472 l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2473 IF l_debug_on THEN
2474 WSH_DEBUG_SV.log(l_module_name, 'Get_Default_UOM Failed: l_return_status', l_return_status);
2475 END IF;
2476 GOTO trip_error;
2477 END IF;
2478 -- Handle return Status error!!!
2479
2480 IF l_debug_on THEN
2481 WSH_DEBUG_SV.logmsg(l_module_name,'WT UOM:'||l_stop_local_tab(stop_rec).weight_uom_code||'VOLUME UOM:'||l_stop_local_tab(stop_rec).volume_uom_code);
2482 END IF;
2483
2484 END IF; --}
2485 END IF;
2486 END LOOP;--}
2487 END IF;
2488 END IF; --} -- derive UOM
2489
2490 l_stop_attrs(l_stop_count).WEIGHT_UOM_CODE := l_stop_local_tab(stop_rec).weight_uom_code;
2491 l_stop_attrs(l_stop_count).VOLUME_UOM_CODE := l_stop_local_tab(stop_rec).volume_uom_code;
2492 l_stop_attrs(l_stop_count).TP_ATTRIBUTE_CATEGORY := l_stop_local_tab(stop_rec).tp_attribute_category;
2493 l_stop_attrs(l_stop_count).TP_ATTRIBUTE1 := l_stop_local_tab(stop_rec).tp_attribute1;
2494 l_stop_attrs(l_stop_count).TP_ATTRIBUTE1 := l_stop_local_tab(stop_rec).tp_attribute1;
2495 l_stop_attrs(l_stop_count).TP_ATTRIBUTE1 := l_stop_local_tab(stop_rec).tp_attribute1;
2496 l_stop_attrs(l_stop_count).TP_ATTRIBUTE1 := l_stop_local_tab(stop_rec).tp_attribute1;
2497 l_stop_attrs(l_stop_count).TP_ATTRIBUTE1 := l_stop_local_tab(stop_rec).tp_attribute1;
2498 l_stop_attrs(l_stop_count).TP_ATTRIBUTE1 := l_stop_local_tab(stop_rec).tp_attribute1;
2499 l_stop_attrs(l_stop_count).TP_ATTRIBUTE1 := l_stop_local_tab(stop_rec).tp_attribute1;
2500 l_stop_attrs(l_stop_count).TP_ATTRIBUTE1 := l_stop_local_tab(stop_rec).tp_attribute1;
2501 l_stop_attrs(l_stop_count).TP_ATTRIBUTE1 := l_stop_local_tab(stop_rec).tp_attribute1;
2502 l_stop_attrs(l_stop_count).TP_ATTRIBUTE10 := l_stop_local_tab(stop_rec).tp_attribute10;
2503 l_stop_attrs(l_stop_count).TP_ATTRIBUTE11 := l_stop_local_tab(stop_rec).tp_attribute11;
2504 l_stop_attrs(l_stop_count).TP_ATTRIBUTE12 := l_stop_local_tab(stop_rec).tp_attribute12;
2505 l_stop_attrs(l_stop_count).TP_ATTRIBUTE13 := l_stop_local_tab(stop_rec).tp_attribute13;
2506 l_stop_attrs(l_stop_count).TP_ATTRIBUTE14 := l_stop_local_tab(stop_rec).tp_attribute14;
2507 l_stop_attrs(l_stop_count).TP_ATTRIBUTE15 := l_stop_local_tab(stop_rec).tp_attribute15;
2508
2509 l_stop_attrs(l_stop_count).ATTRIBUTE_CATEGORY := l_stop_local_tab(stop_rec).attribute_category;
2510 l_stop_attrs(l_stop_count).ATTRIBUTE1 := l_stop_local_tab(stop_rec).attribute1;
2511 l_stop_attrs(l_stop_count).ATTRIBUTE1 := l_stop_local_tab(stop_rec).attribute1;
2512 l_stop_attrs(l_stop_count).ATTRIBUTE1 := l_stop_local_tab(stop_rec).attribute1;
2513 l_stop_attrs(l_stop_count).ATTRIBUTE1 := l_stop_local_tab(stop_rec).attribute1;
2514 l_stop_attrs(l_stop_count).ATTRIBUTE1 := l_stop_local_tab(stop_rec).attribute1;
2515 l_stop_attrs(l_stop_count).ATTRIBUTE1 := l_stop_local_tab(stop_rec).attribute1;
2516 l_stop_attrs(l_stop_count).ATTRIBUTE1 := l_stop_local_tab(stop_rec).attribute1;
2517 l_stop_attrs(l_stop_count).ATTRIBUTE1 := l_stop_local_tab(stop_rec).attribute1;
2518 l_stop_attrs(l_stop_count).ATTRIBUTE1 := l_stop_local_tab(stop_rec).attribute1;
2519 l_stop_attrs(l_stop_count).ATTRIBUTE10 := l_stop_local_tab(stop_rec).attribute10;
2520 l_stop_attrs(l_stop_count).ATTRIBUTE11 := l_stop_local_tab(stop_rec).attribute11;
2521 l_stop_attrs(l_stop_count).ATTRIBUTE12 := l_stop_local_tab(stop_rec).attribute12;
2522 l_stop_attrs(l_stop_count).ATTRIBUTE13 := l_stop_local_tab(stop_rec).attribute13;
2523 l_stop_attrs(l_stop_count).ATTRIBUTE14 := l_stop_local_tab(stop_rec).attribute14;
2524 l_stop_attrs(l_stop_count).ATTRIBUTE15 := l_stop_local_tab(stop_rec).attribute15;
2525
2526 IF l_debug_on THEN
2527 WSH_DEBUG_SV.logmsg(l_module_name,'Stop id:'||l_stop_local_tab(stop_rec).stop_id);
2528 WSH_DEBUG_SV.logmsg(l_module_name,'Stop Location id:'||l_stop_local_tab(stop_rec).stop_location_id);
2529 WSH_DEBUG_SV.logmsg(l_module_name,'Stop Sequence Number:'||l_stop_local_tab(stop_rec).stop_sequence_number);
2530 WSH_DEBUG_SV.logmsg(l_module_name,'Stop PAD:'||l_stop_local_tab(stop_rec).planned_arrival_date);
2531 WSH_DEBUG_SV.logmsg(l_module_name,'Stop PDD:'||l_stop_local_tab(stop_rec).planned_departure_date);
2532 WSH_DEBUG_SV.logmsg(l_module_name,'Stop Wt UOM:'||l_stop_local_tab(stop_rec).weight_uom_code);
2533 WSH_DEBUG_SV.logmsg(l_module_name,'Stop Vol UOM:'||l_stop_local_tab(stop_rec).volume_uom_code);
2534 WSH_DEBUG_SV.logmsg(l_module_name,'Before Calling Create_update_Stop,count:'||l_stop_attrs.count);
2535 END IF;
2536
2537 wsh_trip_stops_grp.Create_Update_Stop(
2538 p_api_version_number => 1.0,
2539 p_init_msg_list => FND_API.G_FALSE,
2540 p_commit => FND_API.G_FALSE,
2541 p_in_rec => l_stop_in_rec,
2542 p_rec_attr_tab => l_stop_attrs,
2543 x_stop_out_tab => l_stop_out_tab,
2544 x_return_status => l_rs,
2545 x_msg_count => l_msg_count,
2546 x_msg_data => l_msg_data,
2547 x_stop_wt_vol_out_tab => l_stop_wt_vol_out_tab);
2548
2549
2550 -- Handle return status and error Here !!!
2551 IF l_rs IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
2552 l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2553 IF l_debug_on THEN
2554 WSH_DEBUG_SV.log(l_module_name, 'Create Update Trip Stop Failed: l_return_status', l_return_status);
2555 END IF;
2556 GOTO trip_error;
2557 END IF;
2558
2559
2560 -- Need Stop id while assigning delivery to trip, for Update it is already populated
2561 IF l_stop_in_rec.action_code = 'CREATE' AND l_stop_out_tab.count > 0 THEN
2562 l_stop_local_tab(stop_rec).stop_id := l_stop_out_tab(l_stop_out_tab.FIRST).stop_id;
2563 END IF;
2564
2565 IF l_debug_on THEN
2566 WSH_DEBUG_SV.logmsg(l_module_name,'Return Status after Create_Update_stop'||l_rs);
2567 WSH_DEBUG_SV.logmsg(l_module_name,'Stop id after Create_Update_stop'||l_stop_local_tab(stop_rec).stop_id);
2568 END IF;
2569
2570 -- OTM Dock Door App Sched Proj
2571 DECLARE
2572 CURSOR c_dock_appt_rec(c_stop_interface_id IN NUMBER, c_group_id IN NUMBER) IS
2573 SELECT wtsi.dock_name, wnd.organization_id, wtsi.start_time, wtsi.end_time
2574 FROM wsh_del_legs_interface wdli,
2575 wsh_new_del_interface wndi,
2576 wsh_new_deliveries wnd,
2577 wsh_trip_stops_interface wtsi,
2578 wsh_trips_interface wti
2579 WHERE wti.group_id = c_group_id
2580 AND wti.interface_action_code = G_TMS_RELEASE_CODE
2581 AND wtsi.trip_interface_id = wti.trip_interface_id
2582 AND wtsi.stop_interface_id = wdli.pick_up_stop_interface_id
2583 AND wtsi.interface_action_code = G_TMS_RELEASE_CODE
2584 AND wnd.delivery_id = wdli.delivery_id
2585 AND wndi.delivery_id = wdli.delivery_id
2586 AND wdli.interface_action_code = G_TMS_RELEASE_CODE
2587 AND wtsi.stop_interface_id = c_stop_interface_id;
2588
2589 l_dock_name VARCHAR2(200);
2590 l_organization_id NUMBER;
2591 l_start_time DATE;
2592 x_start_time DATE := NULL;
2593 l_end_time DATE;
2594 x_end_time DATE := NULL;
2595 l_shipping_parameters WSH_SHIPPING_PARAMS_PVT.PARAMETER_REC_TYP;
2596
2597 BEGIN
2598 -- Fetching Dock Door Appt Scheduling info for the pick_up_stop
2599 --Bug7717569 replaced p_group_id with l_trip_info_tab(i).group_id
2600 OPEN c_dock_appt_rec(l_stop_local_tab(stop_rec).stop_interface_id,l_trip_info_tab(i).group_id);
2601 -- Getting info for the first record as the info. will be same even if several deliveries have the same pick up stop
2602 FETCH c_dock_appt_rec INTO l_dock_name, l_organization_id, l_start_time, l_end_time;
2603 CLOSE c_dock_appt_rec;
2604 IF (l_organization_id IS NOT NULL) THEN --{
2605 IF (wsh_util_validate.check_wms_org(l_organization_id) = 'Y') THEN --{
2606 IF l_debug_on THEN
2607 WSH_DEBUG_SV.log(l_module_name,'Calling WSH_SHIPPING_PARAMS_PVT.GET',WSH_DEBUG_SV.C_PROC_LEVEL);
2608 END IF;
2609 WSH_SHIPPING_PARAMS_PVT.GET(p_organization_id => l_organization_id,
2610 x_param_info => l_shipping_parameters,
2611 x_return_status => l_rs);
2612
2613 IF l_rs IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
2614 l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2615 IF l_debug_on THEN
2616 WSH_DEBUG_SV.log(l_module_name, 'WSH_SHIPPING_PARAMS_PVT.GET Failed: l_return_status', l_return_status);
2617 END IF;
2618 GOTO trip_error;
2619 END IF;
2620
2621 IF (l_shipping_parameters.dock_appt_scheduling_flag = 'Y') THEN
2622
2623 IF (l_start_time IS NOT NULL) THEN
2624 get_server_time(p_source_time => l_start_time,
2625 p_source_timezone_code => l_stop_local_tab(stop_rec).timezone_code,
2626 x_server_time => x_start_time,
2627 x_return_status => l_rs,
2628 x_msg_count => l_msg_count,
2629 x_msg_data => l_msg_data);
2630
2631 IF l_rs IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
2632 l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2633 IF l_debug_on THEN
2634 WSH_DEBUG_SV.log(l_module_name, 'Get Server Time for Dock Door Appointment Start Time, l_return_status:', l_return_status);
2635 END IF;
2636 GOTO trip_error;
2637 END IF;
2638 END IF;
2639
2640 IF (l_end_time IS NOT NULL) THEN
2641 get_server_time(p_source_time => l_end_time,
2642 p_source_timezone_code => l_stop_local_tab(stop_rec).timezone_code,
2643 x_server_time => x_end_time,
2644 x_return_status => l_rs,
2645 x_msg_count => l_msg_count,
2646 x_msg_data => l_msg_data);
2647
2648 IF l_rs IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
2649 l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2650 IF l_debug_on THEN
2651 WSH_DEBUG_SV.log(l_module_name, 'Get Server Time for Dock Door Appointment End Time, l_return_status:', l_return_status);
2652 END IF;
2653 GOTO trip_error;
2654 END IF;
2655 END IF;
2656
2657 -- Populating table with the Dock Door App Sched Info. to be sent to WMS
2658 l_dock_appt_index := l_dock_appt_tab.COUNT + 1;
2659 l_dock_appt_tab(l_dock_appt_index).dock_name := l_dock_name;
2660 l_dock_appt_tab(l_dock_appt_index).trip_stop_id := l_stop_local_tab(stop_rec).stop_id;
2661 l_dock_appt_tab(l_dock_appt_index).organization_id := l_organization_id;
2662 l_dock_appt_tab(l_dock_appt_index).start_time := x_start_time;
2663 l_dock_appt_tab(l_dock_appt_index).end_time := x_end_time;
2664
2665 IF l_debug_on THEN
2666 WSH_DEBUG_SV.log(l_module_name, 'l_dock_appt_tab('||l_dock_appt_index||').dock_name',l_dock_appt_tab(l_dock_appt_index).dock_name);
2667 WSH_DEBUG_SV.log(l_module_name, 'l_dock_appt_tab('||l_dock_appt_index||').stop_id',l_dock_appt_tab(l_dock_appt_index).trip_stop_id);
2668 WSH_DEBUG_SV.log(l_module_name, 'l_dock_appt_tab('||l_dock_appt_index||').organization_id',l_dock_appt_tab(l_dock_appt_index).organization_id);
2669 WSH_DEBUG_SV.log(l_module_name, 'l_dock_appt_tab('||l_dock_appt_index||').start_time',l_dock_appt_tab(l_dock_appt_index).start_time);
2670 WSH_DEBUG_SV.log(l_module_name, 'l_dock_appt_tab('||l_dock_appt_index||').end_time',l_dock_appt_tab(l_dock_appt_index).end_time);
2671 END IF;
2672 END IF; --} Cheking dock_appt_schedling_flag
2673 END IF; --} Checking WMS Org
2674 END IF; --} Checking l_organization_id as NULL
2675 EXCEPTION
2676 WHEN OTHERS THEN
2677 l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2678 IF l_debug_on THEN
2679 WSH_DEBUG_SV.log(l_module_name, 'Error while fetching Dock Door Appointment Scheduling Info.');
2680 END IF;
2681 IF (c_dock_appt_rec%ISOPEN) THEN
2682 CLOSE c_dock_appt_rec;
2683 END IF;
2684 GOTO trip_error;
2685 END;
2686
2687 DECLARE
2688 CURSOR c_1 IS
2689 select stop_id,stop_location_id,stop_sequence_number,
2690 physical_stop_id,physical_location_id
2691 from wsh_trip_stops
2692 where stop_id = l_stop_local_tab(stop_rec).stop_id;
2693 BEGIN
2694 FOR st in c_1
2695 LOOP
2696 IF l_debug_on THEN
2697 WSH_DEBUG_SV.logmsg(l_module_name,'Stop id:'||st.stop_id);
2698 WSH_DEBUG_SV.logmsg(l_module_name,'Stop Location id:'||st.stop_location_id);
2699 WSH_DEBUG_SV.logmsg(l_module_name,'Stop Seq Num:'||st.stop_sequence_number);
2700 WSH_DEBUG_SV.logmsg(l_module_name,'Physical Stop id:'||st.physical_stop_id);
2701 WSH_DEBUG_SV.logmsg(l_module_name,'Physical Loc id:'||st.physical_location_id);
2702 END IF;
2703 END LOOP;
2704 END;
2705
2706 END IF;--} -- matching trip_interface_id to process only relevant records
2707 END LOOP;--} -- looping across l_stop_local_tab
2708
2709 END;--} -- end of process trip stops
2710
2711
2712 -- 4e. Process Deliveries
2713 IF l_debug_on THEN
2714 WSH_DEBUG_SV.logmsg(l_module_name,'===============================');
2715 WSH_DEBUG_SV.logmsg(l_module_name,'4e. PROCESS DELIVERIES');
2716 WSH_DEBUG_SV.logmsg(l_module_name,'===============================');
2717 END IF;
2718
2719 DECLARE
2720
2721 l_delivery_tab WSH_TMS_RELEASE.delivery_tab;
2722 l_delivery_id_tab WSH_UTIL_CORE.id_tab_type;
2723
2724 l_del_attrs WSH_NEW_DELIVERIES_PVT.delivery_attr_tbl_type;
2725 l_del_action_prms WSH_DELIVERIES_GRP.action_parameters_rectype;
2726 l_del_action_rec WSH_DELIVERIES_GRP.delivery_action_out_rec_type;
2727 l_del_defaults WSH_DELIVERIES_GRP.default_parameters_rectype;
2728
2729 l_rs VARCHAR2(1);
2730 l_msg_count NUMBER;
2731 l_msg_data VARCHAR2(32767);
2732
2733 -- flag
2734 l_del_matches VARCHAR2(1);
2735
2736 l_dleg_count NUMBER;
2737 l_del_index WSH_UTIL_CORE.id_tab_type;
2738
2739 BEGIN--{
2740 l_delivery_id_tab.DELETE;
2741 l_del_attrs.DELETE;
2742
2743 --l_dleg_local_tab.DELETE;
2744 IF l_dleg_local_tab.COUNT > 0 THEN
2745 -- Bug 5134725
2746 -- EBS Version Number will always be equal to or greater than the OTM sent version
2747 -- Stop further processing, if the EBS version number is greater than OTM version
2748 -- Bug#7491598(ER,defer planned shipment iface): check the parameter p_latest_version
2749 -- before raising the error. Processing of lower tms version deliveries is allowed only
2750 -- when the EBS delivery is 'UPDATE required' from OTM status(tms_interface_flag value should be 'UR'/'UP').
2751 FOR del_version IN l_dleg_local_tab.FIRST..l_dleg_local_tab.LAST
2752 LOOP --{
2753 IF l_dleg_local_tab(del_version).tms_version_number > l_dleg_local_tab(del_version).otm_tms_version_number THEN
2754 --{
2755 IF ( NVL(p_latest_version,'Y') = 'N' AND l_dleg_local_tab(del_version).tms_interface_flag NOT IN ('UR','UP') ) THEN
2756 --{
2757 -- raise error
2758 IF l_debug_on THEN
2759 WSH_DEBUG_SV.logmsg(l_module_name,'EBS and OTM version number donot match');
2760 END IF;
2761 FND_MESSAGE.SET_NAME('WSH','WSH_OTM_IB_NOT_UPD_VERSION_ERR');
2762 WSH_UTIL_CORE.ADD_MESSAGE(WSH_UTIL_CORE.G_RET_STS_ERROR);
2763 l_rs := WSH_UTIL_CORE.G_RET_STS_ERROR;
2764 EXIT; -- out of this loop
2765 --}
2766 END IF;
2767 --
2768 IF NVL(p_latest_version,'Y') = 'Y' THEN
2769 --{
2770 -- raise error
2771 IF l_debug_on THEN
2772 WSH_DEBUG_SV.logmsg(l_module_name,'EBS and OTM version number donot match');
2773 END IF;
2774 FND_MESSAGE.SET_NAME('WSH','WSH_OTM_IB_VERSION_ERROR');
2775 WSH_UTIL_CORE.ADD_MESSAGE(WSH_UTIL_CORE.G_RET_STS_ERROR);
2776 l_rs := WSH_UTIL_CORE.G_RET_STS_ERROR;
2777 EXIT; -- out of this loop
2778 --}
2779 END IF;
2780 --}
2781 END IF;
2782 END LOOP;--}
2783 -- End of Bug 5134725
2784
2785 -- Handle return status here !!!
2786 IF l_rs IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
2787 -- go to trip_error
2788 l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2789 IF l_debug_on THEN
2790 WSH_DEBUG_SV.log(l_module_name, 'Version Comparison failed,l_return_status:', l_return_status);
2791 END IF;
2792 GOTO trip_error;
2793 END IF;
2794 -- End of Bug 5134725
2795 --
2796 END IF;
2797
2798 IF l_trip_info_tab(i).trip_id IS NOT NULL THEN--{
2799 -- Find EBS deliveries which are currently assigned to this GC3 trip
2800 find_deliveries_for_trip(
2801 p_trip_id => l_trip_info_tab(i).trip_id,
2802 p_tp_plan_name => NULL,
2803 x_delivery_tab => l_delivery_tab, -- dlvy_interface_id + tms_version_number
2804 x_delivery_id_tab => l_delivery_id_tab, -- dlvy ids
2805 x_return_status => l_rs);
2806
2807 IF l_debug_on THEN
2808 WSH_DEBUG_SV.logmsg(l_module_name,'Return Status after find_deliveries_for_trip'||l_rs);
2809 END IF;
2810 -- Handle return status here !!!
2811 IF l_rs IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
2812 -- go to trip_error
2813 l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2814 IF l_debug_on THEN
2815 WSH_DEBUG_SV.log(l_module_name, 'Find Deliveries for Trip Failed,l_return_status:', l_return_status);
2816 END IF;
2817 GOTO trip_error;
2818 END IF;
2819
2820 -- Compare the List of deliveries assigned to the Trip(per EBS) with
2821 -- list of deliveries assigned to the Trip(per GC3)
2822 -- l_delivery_id_tab and l_delivery_tab count are identical
2823 IF l_delivery_id_tab.count > 0 THEN
2824 FOR del_rec IN l_delivery_id_tab.FIRST..l_delivery_id_tab.LAST
2825 LOOP--{
2826 IF l_debug_on THEN
2827 WSH_DEBUG_SV.log(l_module_name,'Delivery id:',l_delivery_id_tab(del_rec));
2828 END IF;
2829 l_del_matches := 'N';
2830 FOR del_intface_rec IN l_dleg_local_tab.FIRST..l_dleg_local_tab.LAST
2831 LOOP--{
2832 -- Get only relevant deliveries, based on trip_interface_id
2833 -- Compare the list
2834 IF (l_delivery_id_tab(del_rec) = l_dleg_local_tab(del_intface_rec).delivery_id) THEN
2835 l_del_matches := 'Y'; --matching delivery found
2836 IF l_debug_on THEN
2837 WSH_DEBUG_SV.log(l_module_name,'Match Delivery id:',l_dleg_local_tab(del_intface_rec).delivery_id);
2838 END IF;
2839 EXIT;
2840 END IF;
2841 END LOOP;--}
2842
2843 IF l_del_matches = 'Y' THEN--{
2844 -- This EBS delivery is still on the same GC3 trip
2845 -- Will compare if the stops/legs have changed for this assignment
2846 -- Delivery is on same trip, but new stop linking,then unassign and assign
2847 IF l_debug_on THEN
2848 WSH_DEBUG_SV.logmsg(l_module_name,'Delivery:'||l_delivery_id_tab(del_rec)||' is already assigned to Trip:'||l_trip_info_tab(i).trip_id);
2849 END IF;
2850 ELSE -- l_del_matches = 'N'
2851 -- This EBS delivery is to be unassigned from the GC3 trip
2852 IF l_debug_on THEN
2853 WSH_DEBUG_SV.logmsg(l_module_name,'Delivery:'||l_delivery_id_tab(del_rec)||' to be unassigned from Trip:'||l_trip_info_tab(i).trip_id);
2854 END IF;
2855 -- Populate l_del_attrs for call to delivery_action()
2856 l_del_attrs(l_del_attrs.count + 1).delivery_id := l_delivery_id_tab(del_rec);
2857 l_del_attrs(l_del_attrs.count).organization_id :=
2858 l_delivery_tab(l_delivery_id_tab(del_rec)).organization_id;
2859 -- Append to the l_unassigned_delivery_id_tab
2860 l_unassigned_delivery_id_tab(l_unassigned_delivery_id_tab.count + 1) :=
2861 l_delivery_id_tab(del_rec);
2862 l_unassigned_dlvy_version_tab(l_unassigned_delivery_id_tab.count) :=
2863 l_delivery_tab(l_delivery_id_tab(del_rec)).tms_version_number;
2864 END IF;--}
2865 END LOOP;--}
2866 END IF;
2867 END IF; --} -- if l_trip_info_tab(i).trip_id is not null
2868
2869 -- The above Unassignments can be grouped together, as they are for
2870 -- Deliveries from SAME TRIP.
2871 -- Bulk Unassignment of Deliveries From Trip
2872 IF l_debug_on THEN
2873 WSH_DEBUG_SV.logmsg(l_module_name,'Deliveries to be unassigned:'||l_del_attrs.count);
2874 END IF;
2875
2876 IF l_del_attrs.count > 0 THEN--{
2877 l_del_action_prms.caller := 'FTE_TMS_INTEGRATION'; --'FTE_TMS_RELEASE';
2878 l_del_action_prms.action_code := 'UNASSIGN-TRIP';
2879 l_del_action_prms.trip_id := l_trip_info_tab(i).trip_id;
2880
2881 WSH_DELIVERIES_GRP.delivery_action(
2882 p_api_version_number => 1.0,
2883 p_init_msg_list => FND_API.G_TRUE,
2884 p_commit => FND_API.G_FALSE,
2885 p_action_prms => l_del_action_prms,
2886 p_rec_attr_tab => l_del_attrs,
2887 x_delivery_out_rec => l_del_action_rec,
2888 x_defaults_rec => l_del_defaults,
2889 x_return_status => l_rs,
2890 x_msg_count => l_msg_count,
2891 x_msg_data => l_msg_data);
2892
2893 IF l_debug_on THEN
2894 WSH_DEBUG_SV.logmsg(l_module_name,'Return Status after Unassign Delivery'||l_rs);
2895 END IF;
2896 IF l_rs IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
2897 l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2898 IF l_debug_on THEN
2899 WSH_DEBUG_SV.log(l_module_name, 'Unassign Delivery from Trip Failed: l_return_status', l_return_status);
2900 END IF;
2901 GOTO trip_error;
2902 END IF;
2903 END IF;--}
2904 -- End of Using Bulk Unassignment
2905
2906 -- Call Compare and Unassign API
2907 -- l_trip_info_tab(i).trip_id can be null or not null
2908 -- Need to unassign in this API itself as the current trip can
2909 -- be different for different delivery OR the trip can be same but stops might
2910 -- have been modified(example: different dropoff stop)
2911 -- l_unassigned_delivery_id_tab is not being MODIFIED in this procedure
2912 -- so no need to populate the version tab l_unassigned_dlvy_version_tab
2913 --
2914 compare_trip_for_deliveries
2915 (p_dleg_tab => l_dleg_local_tab, --IN/OUT
2916 p_trip_id => l_trip_info_tab(i).trip_id,
2917 x_unassign_id_tab => l_unassigned_delivery_id_tab,
2918 --x_unassign_ver_tab => l_unassigned_dlvy_version_tab,
2919 x_return_status => l_rs);
2920 IF l_debug_on THEN
2921 WSH_DEBUG_SV.logmsg(l_module_name,'Return Status after Comparing Trips:'||l_rs);
2922 END IF;
2923 -- Handle return Status here !!!
2924 IF l_rs IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
2925 l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2926 IF l_debug_on THEN
2927 WSH_DEBUG_SV.log(l_module_name, 'Compare Trip for Deliveries Failed: l_return_status', l_return_status);
2928 END IF;
2929 GOTO trip_error;
2930 END IF;
2931
2932 IF l_debug_on THEN
2933 WSH_DEBUG_SV.logmsg(l_module_name,'Unassign Delivery Tab Count:'||l_unassigned_delivery_id_tab.count);
2934 END IF;
2935
2936 --
2937 -- ASSIGN ALL DELIVERIES in l_dleg_local_tab to l_trip_info_tab(i).trip_id
2938 -- DELETE the deliveries from l_unassigned_delivery_id_tab, which were successfully assigned
2939 -- One option is to not populate l_unassigned_delivery_id_tab after procedure
2940 -- compare_trip_for_deliveries as the input set of delivery ids is based on the deliveries
2941 -- which have to be assigned to the trip l_trip_id using the stops
2942
2943 l_del_attrs.DELETE;
2944 l_del_action_prms := NULL;
2945 l_del_action_rec := NULL;
2946 l_del_defaults := NULL;
2947
2948 l_del_action_prms.caller := 'FTE_TMS_INTEGRATION';
2949 l_del_action_prms.action_code := 'ASSIGN-TRIP';
2950
2951 -- identical for all deliveries in l_dleg_local_tab
2952 l_del_action_prms.trip_id := l_trip_id;
2953
2954 IF l_debug_on THEN
2955 WSH_DEBUG_SV.log(l_module_name, 'DLEG LOCAL TAB COUNT:', l_dleg_local_tab.count);
2956 WSH_DEBUG_SV.log(l_module_name, 'DLVY ID TAB COUNT:', l_dlvy_id_tab.count);
2957 END IF;
2958
2959 IF l_dleg_local_tab.count > 0 THEN--{
2960 FOR assign_count IN l_dleg_local_tab.FIRST..l_dleg_local_tab.LAST
2961 LOOP--{
2962 IF l_debug_on THEN
2963 WSH_DEBUG_SV.logmsg(l_module_name, 'Delivery:'||l_dleg_local_tab(assign_count).delivery_id||' has processed flag of:'||l_dleg_local_tab(assign_count).processed_flag);
2964 END IF;
2965 -- No need to process the deliveries which still have the same assignment
2966 -- as before
2967 IF l_dleg_local_tab(assign_count).processed_flag = 'Y' THEN--{
2968 l_dlvy_id_tab(l_dlvy_id_tab.count + 1) := l_dleg_local_tab(assign_count).delivery_id;
2969 l_dlvy_version_tab(l_dlvy_id_tab.count) := l_dleg_local_tab(assign_count).tms_version_number;
2970 -- skip to next delivery
2971 IF l_debug_on THEN
2972 WSH_DEBUG_SV.log(l_module_name, 'Skip Delivery:', l_dleg_local_tab(assign_count).delivery_id);
2973 END IF;
2974 -- do not exit
2975
2976 ELSE--} --{
2977
2978 -- Again processing one delivery at a time as we need to specify the pickup
2979 -- and dropoff stop details which are a record and not table
2980 -- Always populating 1st index
2981 l_del_attrs(1).delivery_id := l_dleg_local_tab(assign_count).delivery_id;
2982 l_del_attrs(1).organization_id := l_dleg_local_tab(assign_count).organization_id;
2983
2984 IF l_debug_on THEN
2985 WSH_DEBUG_SV.log(l_module_name, 'Process Delivery:',l_dleg_local_tab(assign_count).delivery_id);
2986 END IF;
2987
2988 -- Need to find EBS stop id, GC3 sent planned dates
2989 -- Location and Sequence Number are part of dleg data structure
2990 FOR pkup_stop IN l_stop_local_tab.FIRST..l_stop_local_tab.LAST
2991 LOOP
2992 IF( l_stop_local_tab(pkup_stop).stop_interface_id
2993 = l_dleg_local_tab(assign_count).pick_up_stop_interface_id ) AND
2994 (to_number(l_stop_local_tab(pkup_stop).stop_location_id)
2995 = to_number(l_dleg_local_tab(assign_count).pickup_stop_location_id)) --for Internal Location
2996 -- as the dummy and physical stops in this table have same stop_interface_id
2997 -- but different stop_location_id
2998 THEN
2999 -- Pickup Information
3000 l_del_action_prms.pickup_stop_id := l_stop_local_tab(pkup_stop).stop_id;
3001 l_del_action_prms.pickup_loc_id := to_number(l_stop_local_tab(pkup_stop).stop_location_id);
3002 l_del_action_prms.pickup_stop_seq := l_stop_local_tab(pkup_stop).stop_sequence_number;
3003 l_del_action_prms.pickup_arr_date := l_stop_local_tab(pkup_stop).planned_arrival_date;
3004 l_del_action_prms.pickup_dep_date := l_stop_local_tab(pkup_stop).planned_departure_date;
3005 --l_del_action_prms.pickup_stop_status := 'OP';
3006 END IF;
3007 END LOOP;
3008
3009 FOR dropoff_stop IN l_stop_local_tab.FIRST..l_stop_local_tab.LAST
3010 LOOP
3011 IF (l_stop_local_tab(dropoff_stop).stop_interface_id
3012 = l_dleg_local_tab(assign_count).drop_off_stop_interface_id) AND
3013 (to_number(l_stop_local_tab(dropoff_stop).stop_location_id)
3014 = to_number(l_dleg_local_tab(assign_count).dropoff_stop_location_id)) --for Internal Location
3015 THEN
3016 -- Dropoff Information
3017 l_del_action_prms.dropoff_stop_id := l_stop_local_tab(dropoff_stop).stop_id;
3018 l_del_action_prms.dropoff_loc_id := to_number(l_stop_local_tab(dropoff_stop).stop_location_id);
3019 l_del_action_prms.dropoff_stop_seq := l_stop_local_tab(dropoff_stop).stop_sequence_number;
3020 l_del_action_prms.dropoff_arr_date := l_stop_local_tab(dropoff_stop).planned_arrival_date;
3021 l_del_action_prms.dropoff_dep_date := l_stop_local_tab(dropoff_stop).planned_departure_date;
3022 --l_del_action_prms.dropoff_stop_status := 'OP';
3023 END IF;
3024 END LOOP;
3025
3026 IF l_debug_on THEN
3027 WSH_DEBUG_SV.logmsg(l_module_name,'========================================');
3028 WSH_DEBUG_SV.logmsg(l_module_name,'Calling Assign Delivery to Trip API');
3029 WSH_DEBUG_SV.logmsg(l_module_name,'Delivery id:'||l_dleg_local_tab(assign_count).delivery_id);
3030 WSH_DEBUG_SV.logmsg(l_module_name,'Trip id:'||l_del_action_prms.trip_id);
3031 WSH_DEBUG_SV.logmsg(l_module_name,'---------Pickup Stop information----------');
3032 WSH_DEBUG_SV.logmsg(l_module_name,'Pickup Stop id:'||l_del_action_prms.pickup_stop_id);
3033 WSH_DEBUG_SV.logmsg(l_module_name,'Pickup Stop Location id:'||l_del_action_prms.pickup_loc_id);
3034 WSH_DEBUG_SV.logmsg(l_module_name,'Pickup Stop Sequence:'||l_del_action_prms.pickup_stop_seq);
3035 WSH_DEBUG_SV.logmsg(l_module_name,'Pickup Pl Arr Date:'||l_del_action_prms.pickup_arr_date);
3036 WSH_DEBUG_SV.logmsg(l_module_name,'Pickup Pl Dep Date:'||l_del_action_prms.pickup_dep_date);
3037 WSH_DEBUG_SV.logmsg(l_module_name,'---------Dropoff Stop information----------');
3038 WSH_DEBUG_SV.logmsg(l_module_name,'Dropoff Stop id:'||l_del_action_prms.dropoff_stop_id);
3039 WSH_DEBUG_SV.logmsg(l_module_name,'Dropoff Stop Location id:'||l_del_action_prms.dropoff_loc_id);
3040 WSH_DEBUG_SV.logmsg(l_module_name,'Dropoff Stop Sequence:'||l_del_action_prms.dropoff_stop_seq);
3041 WSH_DEBUG_SV.logmsg(l_module_name,'Dropoff Pl Arr Date:'||l_del_action_prms.dropoff_arr_date);
3042 WSH_DEBUG_SV.logmsg(l_module_name,'Dropoff Pl Dep Date:'||l_del_action_prms.dropoff_dep_date);
3043 WSH_DEBUG_SV.logmsg(l_module_name,'========================================');
3044 END IF;
3045
3046 WSH_DELIVERIES_GRP.delivery_action(
3047 p_api_version_number => 1.0,
3048 p_init_msg_list => FND_API.G_TRUE,
3049 p_commit => FND_API.G_FALSE,
3050 p_action_prms => l_del_action_prms,
3051 p_rec_attr_tab => l_del_attrs,
3052 x_delivery_out_rec => l_del_action_rec,
3053 x_defaults_rec => l_del_defaults,
3054 x_return_status => l_rs,
3055 x_msg_count => l_msg_count,
3056 x_msg_data => l_msg_data);
3057
3058 IF l_debug_on THEN
3059 WSH_DEBUG_SV.logmsg(l_module_name,'Return Status after Assign Delivery to Trip'||l_rs);
3060 END IF;
3061
3062 IF l_rs IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
3063 l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3064 IF l_debug_on THEN
3065 WSH_DEBUG_SV.log(l_module_name, 'Assign Delivery to Trip Failed: l_return_status', l_return_status);
3066 END IF;
3067 GOTO trip_error;
3068 ELSIF l_rs = WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
3069 l_dleg_local_tab(assign_count).processed_flag := 'Y';
3070 l_dlvy_id_tab(l_dlvy_id_tab.count + 1) := l_dleg_local_tab(assign_count).delivery_id;
3071 l_dlvy_version_tab(l_dlvy_id_tab.count) := l_dleg_local_tab(assign_count).tms_version_number;
3072 END IF;
3073
3074 END IF;--}
3075 END LOOP;--}
3076 END IF;--}
3077
3078 IF l_debug_on THEN
3079 WSH_DEBUG_SV.logmsg(l_module_name,'Deliveries successfully assigned to trip:'||l_dlvy_id_tab.count);
3080 WSH_DEBUG_SV.logmsg(l_module_name,'Unassign Delivery Count:'||l_unassigned_delivery_id_tab.count);
3081 END IF;
3082
3083 -- Make sure the successfully assigned deliveries are removed from
3084 -- l_unassigned_delivery_id_tab.
3085 l_del_index.DELETE;
3086 IF l_dlvy_id_tab.count > 0 AND l_unassigned_delivery_id_tab.count > 0 THEN--{
3087 FOR assign_count IN l_dlvy_id_tab.FIRST..l_dlvy_id_tab.LAST
3088 LOOP--{
3089 FOR unassign_count IN l_unassigned_delivery_id_tab.FIRST..l_unassigned_delivery_id_tab.LAST
3090 LOOP
3091 IF l_unassigned_delivery_id_tab(unassign_count) = l_dlvy_id_tab(assign_count) THEN
3092 --Index of record to be removed from Unassign Table
3093 l_del_index(l_del_index.count + 1) := unassign_count;
3094 END IF;
3095 END LOOP;
3096 END LOOP;--}
3097
3098 IF l_debug_on THEN
3099 WSH_DEBUG_SV.logmsg(l_module_name,'Unassign Dlvy Index Count:'||l_del_index.count);
3100 END IF;
3101
3102 -- Actual Deletion from the Unassign Delivery Table
3103 IF l_del_index.count > 0 THEN
3104 FOR i_del IN l_del_index.FIRST..l_del_index.LAST
3105 LOOP
3106 l_unassigned_delivery_id_tab.DELETE(l_del_index(i_del));
3107 l_unassigned_dlvy_version_tab.DELETE(l_del_index(i_del));
3108 END LOOP;
3109 END IF;
3110 END IF; --}
3111
3112 IF l_debug_on THEN
3113 WSH_DEBUG_SV.logmsg(l_module_name,'After compare, Unassign Dlvy Count:'||l_unassigned_delivery_id_tab.count);
3114 WSH_DEBUG_SV.logmsg(l_module_name,'After compare, Unassign Version Count:'||l_unassigned_dlvy_version_tab.count);
3115 END IF;
3116
3117 END;--} -- process delivery
3118
3119 -- OTM R12
3120 -- 4f. Calculate wt/vol for the trip (includes stops) (call Group API)
3121 -- This step is no longer required as trip weight is calculated in previous step itself
3122 -- OTM R12
3123
3124 -- 4g. Process Freight Costs for each delivery
3125 -- Still within the loop of l_trip_info_tab, so trip_interface_id is known
3126 IF l_debug_on THEN
3127 WSH_DEBUG_SV.logmsg(l_module_name,'===============================');
3128 WSH_DEBUG_SV.logmsg(l_module_name,'4g. PROCESS FREIGHT COSTS');
3129 WSH_DEBUG_SV.logmsg(l_module_name,'===============================');
3130 END IF;
3131
3132 DECLARE
3133 -- Delivery id in wsh_freight_costs_interface would be populated
3134 -- while importing information from Glog
3135
3136 -- variables
3137 l_freight_info_tab WSH_FREIGHT_COSTS_GRP.freight_rec_tab_type;
3138 l_in_rec WSH_FREIGHT_COSTS_GRP.freightInRecType;
3139 l_out_tab WSH_FREIGHT_COSTS_GRP.freight_out_tab_type;
3140 l_rs VARCHAR2(1);
3141 l_msg_count NUMBER;
3142 l_msg_data VARCHAR2(32767);
3143
3144 l_carrier_cur_code VARCHAR2(15) := NULL;
3145 l_carrier_id NUMBER := NULL;
3146 l_count NUMBER := 0;
3147 l_frcost_type_id NUMBER := NULL;
3148 l_dleg_id_tab WSH_UTIL_CORE.id_tab_type; -- for deleting Dleg freight cost
3149 l_index NUMBER;
3150 --l_conversion_type_code VARCHAR2(30); -- OTM R12
3151 l_conversion_type_code FND_PROFILE_OPTION_VALUES.PROFILE_OPTION_VALUE%TYPE := NULL; -- OTM R12
3152
3153 BEGIN--{
3154 -- For successfully processed deliveries Only
3155 -- The deliveries are marked successful after process delivery section
3156 -- PROVIDE THE INPUT OF DELIVERY IDS !!!!! l_dlvy_id_tab
3157 IF l_dlvy_id_tab.count > 0 THEN
3158
3159 IF l_frcost_type_id IS NULL THEN
3160 -- Seed Data, expected to be always populated
3161 OPEN c_get_frcost_type_id('OTM Freight Cost','FREIGHT'); -- OTM R12
3162 FETCH c_get_frcost_type_id
3163 INTO l_frcost_type_id;
3164 CLOSE c_get_frcost_type_id;
3165
3166 IF l_debug_on THEN
3167 WSH_DEBUG_SV.logmsg(l_module_name, 'Freight Cost Type Id:'||l_frcost_type_id);
3168 WSH_DEBUG_SV.logmsg(l_module_name, 'Count of Delivery Id:'||l_dlvy_id_tab.count);
3169 END IF;
3170 END IF;
3171
3172 -- NEED TO DELETE THE EXISTING FREIGHT COST RECORDS FOR THE DELIVERY FOR THIS TYPE
3173 -- and then Insert new records
3174 l_dleg_id_tab.DELETE;
3175 FOR l_index in l_dlvy_id_tab.FIRST..l_dlvy_id_tab.LAST
3176 LOOP--{
3177 -- get all legs
3178 FOR rec in c_get_dleg_id(l_dlvy_id_tab(l_index))
3179 LOOP
3180 l_dleg_id_tab(l_dleg_id_tab.count + 1) := rec.delivery_leg_id;
3181 END LOOP;
3182 END LOOP;--}
3183
3184 -- Records for Delivery Legs
3185 FORALL k in l_dleg_id_tab.FIRST..l_dleg_id_tab.LAST
3186 DELETE FROM wsh_freight_costs
3187 WHERE delivery_id = l_dleg_id_tab(k)
3188 AND freight_cost_type_id = l_frcost_type_id;
3189
3190 -- Records for Delivery
3191 FORALL j in l_dlvy_id_tab.FIRST..l_dlvy_id_tab.LAST
3192 DELETE FROM wsh_freight_costs
3193 WHERE delivery_id = l_dlvy_id_tab(j)
3194 AND freight_cost_type_id = l_frcost_type_id;
3195
3196 IF l_debug_on THEN
3197 WSH_DEBUG_SV.logmsg(l_module_name, 'After Deleting Freight Cost Records:');
3198 END IF;
3199
3200 FOR p in l_dlvy_id_tab.FIRST..l_dlvy_id_tab.LAST
3201 LOOP--{
3202 -- There can be multiple freight cost records for each delivery
3203 IF l_debug_on THEN
3204 WSH_DEBUG_SV.log(l_module_name,'Different Delivery id:',l_dlvy_id_tab(p));
3205 END IF;
3206
3207 FOR rec in c_freight_int_cur(l_dlvy_id_tab(p))
3208 LOOP--{
3209 -- Single record is being created, so always use 1
3210 l_count := 1;
3211 --l_count := l_count + 1;
3212
3213 -- OTM R12 l_frcost_type_id IS NULL will never happen as this cursor already used above
3214
3215 l_freight_info_tab(l_count).freight_cost_type_id := l_frcost_type_id; --rec.freight_cost_type_id;
3216 --l_freight_info_tab(l_count).freight_cost_type := rec.freight_cost_type_code;
3217 l_freight_info_tab(l_count).unit_amount := rec.unit_amount;
3218 l_freight_info_tab(l_count).uom := rec.uom;
3219 l_freight_info_tab(l_count).total_amount := rec.total_amount;
3220 l_freight_info_tab(l_count).currency_code := rec.currency_code;
3221 l_freight_info_tab(l_count).delivery_id := rec.delivery_id;
3222
3223 IF l_debug_on THEN
3224 WSH_DEBUG_SV.log(l_module_name,'Delivery id:',l_freight_info_tab(l_count).delivery_id);
3225 WSH_DEBUG_SV.log(l_module_name,'Unit Amount:',l_freight_info_tab(l_count).Unit_amount);
3226 WSH_DEBUG_SV.log(l_module_name,'Total Amount:',l_freight_info_tab(l_count).Total_amount);
3227 WSH_DEBUG_SV.log(l_module_name,'Currency Code:',l_freight_info_tab(l_count).currency_code);
3228 END IF;
3229
3230 -- OTM R12, start of Bug 5952842(5729276)
3231 IF l_conversion_type_code IS NULL THEN--{
3232 WSH_UTIL_CORE.get_currency_conversion_type(
3233 x_curr_conv_type => l_conversion_type_code,
3234 x_return_status => l_rs);
3235
3236 IF l_rs IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
3237 l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3238 IF l_debug_on THEN
3239 WSH_DEBUG_SV.log(l_module_name, 'get_currency_conversion_type Failed');
3240 END IF;
3241 GOTO trip_error;
3242 END IF;
3243
3244 END IF;--}
3245
3246 -- OTM R12
3247 l_freight_info_tab(l_count).conversion_type_code := l_conversion_type_code;
3248
3249 IF l_debug_on THEN
3250 WSH_DEBUG_SV.logmsg(l_module_name, 'Conversion Type: '||l_freight_info_tab(l_count).conversion_type_code);
3251 END IF;
3252 -- OTM R12, end of Bug 5952842(5729276)
3253
3254 -- OTM R12 Only if freight cost record has currency
3255 IF l_freight_info_tab(l_count).currency_code IS NOT NULL AND
3256 l_freight_info_tab(l_count).unit_amount IS NOT NULL THEN
3257 --{
3258
3259 -- OTM R12 11510 code was opening this cursor for every delivery
3260 IF l_carrier_id IS NULL THEN
3261
3262 OPEN c_get_currency_code(l_trip_info_tab(i).trip_interface_id);
3263 FETCH c_get_currency_code INTO l_carrier_cur_code,l_carrier_id;
3264
3265 IF l_debug_on THEN
3266 WSH_DEBUG_SV.logmsg(l_module_name, 'Carrier Currency Code:'||l_carrier_cur_code);
3267 WSH_DEBUG_SV.logmsg(l_module_name, 'Carrier id:'||l_carrier_id);
3268 END IF;
3269
3270 -- OTM R12 check if carrier has a non-null currency
3271 IF c_get_currency_code%NOTFOUND THEN
3272 l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3273 IF l_debug_on THEN
3274 WSH_DEBUG_SV.log(l_module_name, 'Carrier not found ');
3275 END IF;
3276 FND_MESSAGE.SET_NAME('WSH','WSH_CARRIER_ID_NOT_FOUND');
3277 WSH_UTIL_CORE.ADD_MESSAGE(WSH_UTIL_CORE.G_RET_STS_ERROR);
3278 CLOSE c_get_currency_code;
3279 GOTO trip_error;
3280 ELSE
3281 WSH_UTIL_VALIDATE.Validate_Carrier(
3282 p_carrier_name => NULL,
3283 x_carrier_id => l_carrier_id,
3284 x_return_status => l_rs);
3285
3286 IF l_debug_on THEN
3287 WSH_DEBUG_SV.log(l_module_name,'Return Status from WSH_UTIL_VALIDATE.Validate_Carrier:',l_rs);
3288 END IF;
3289
3290 IF l_rs IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
3291 l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3292 IF l_debug_on THEN
3293 WSH_DEBUG_SV.log(l_module_name, 'Validate Carrier Failed: l_return_status', l_return_status);
3294 END IF;
3295 CLOSE c_get_currency_code;
3296 GOTO trip_error;
3297 END IF;
3298 END IF;
3299 CLOSE c_get_currency_code;
3300
3301 IF l_carrier_cur_code IS NULL THEN
3302 l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3303 IF l_debug_on THEN
3304 WSH_DEBUG_SV.log(l_module_name, 'Carrier Currency not defined ');
3305 END IF;
3306 FND_MESSAGE.SET_NAME('WSH','WSH_CARRIER_NO_CURRENCY');
3307 WSH_UTIL_CORE.ADD_MESSAGE(WSH_UTIL_CORE.G_RET_STS_ERROR);
3308 GOTO trip_error;
3309 END IF;
3310
3311 -- OTM R12 check if carrier has a non-null currency
3312 END IF;
3313
3314 -- Per ECO 5008405, Validate GC3 sent currency
3315 -- Need to adjust precision, dependent on WSHUTVLB new parameter
3316 IF ( l_freight_info_tab(l_count).currency_code <> fnd_api.g_miss_char ) THEN--{
3317 WSH_UTIL_VALIDATE.Validate_Currency(
3318 p_currency_code => l_freight_info_tab(l_count).currency_code,
3319 p_currency_name => NULL,
3320 p_amount => l_freight_info_tab(l_count).unit_amount,
3321 p_otm_enabled => 'Y', -- OTM R12
3322 x_return_status => l_rs,
3323 x_adjusted_amount => l_freight_info_tab(l_count).unit_amount); -- OTM R12
3324
3325 IF l_debug_on THEN
3326 WSH_DEBUG_SV.log(l_module_name,'Return Status from WSH_UTIL_VALIDATE.Validate_Currency:',l_rs);
3327 END IF;
3328 -- Handle error here !!!
3329 -- ECO 5008405 handle here !!!
3330 IF l_rs IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
3331 l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3332 IF l_debug_on THEN
3333 WSH_DEBUG_SV.log(l_module_name, 'Validate Currency Failed: l_return_status', l_return_status);
3334 END IF;
3335 GOTO trip_error;
3336 ELSE -- OTM R12
3337 IF l_debug_on THEN
3338 WSH_DEBUG_SV.log(l_module_name, 'Validate Currency result: x_adjusted_amount : ', l_freight_info_tab(l_count).unit_amount);
3339 END IF;
3340 END IF;
3341 END IF;--}
3342
3343
3344 -- OTM R12 old code would have inserted currency even if carrier's currency was null
3345 -- This was not correct
3346 -- COMPARE carrier currency UOM with GC3 Currency UOM. If different, then
3347 -- Convert the Amount to carrier UOM
3348 -- else use the amount specified in the interface table directly.
3349
3350 --IF (l_carrier_cur_code IS NOT NULL) AND
3351 -- (l_freight_info_tab(l_count).currency_code IS NOT NULL) AND
3352
3353 IF (l_carrier_cur_code <> l_freight_info_tab(l_count).currency_code) THEN--{
3354
3355 -- Logic to populate l_conversion_type_code is moved above
3356 -- Issue raised in Bug 5952842 (5729276)
3357
3358 -- OTM R12 Why was 11510 using total_amount here
3359 -- validated unit_amount above
3360
3361 IF l_debug_on THEN
3362 WSH_DEBUG_SV.logmsg(l_module_name, 'Calling GL_CURRENCY_API.convert_uom:');
3363 WSH_DEBUG_SV.logmsg(l_module_name, 'Currency Code:'||l_freight_info_tab(l_count).currency_code);
3364 WSH_DEBUG_SV.logmsg(l_module_name, 'Conversion Type: '||l_freight_info_tab(l_count).conversion_type_code);
3365 WSH_DEBUG_SV.logmsg(l_module_name, 'Carrier Currency Code:'||l_carrier_cur_code);
3366 -- OTM R12 print unit here as total is not being used
3367 --WSH_DEBUG_SV.logmsg(l_module_name, 'Freight Cost Amount:'||l_freight_info_tab(l_count).total_amount);
3368 WSH_DEBUG_SV.logmsg(l_module_name, 'Freight Cost Amount:'||l_freight_info_tab(l_count).unit_amount);
3369 END IF;
3370
3371 -- Convert API should be called only when the carrier has been validated above
3372 DECLARE
3373
3374 gl_currency_excp EXCEPTION;
3375
3376 -- Bug 5886042
3377 -- currency conversion_type in the error message should be
3378 -- user_conversion_type
3379
3380 l_user_conv_type VARCHAR2(30) := NULL;
3381
3382 CURSOR c_get_user_conv_type(p_curr_conv_type varchar2) IS
3383 SELECT user_conversion_type
3384 FROM gl_daily_conversion_types
3385 WHERE conversion_type = p_curr_conv_type;
3386 --
3387 -- end of Bug 5886042
3388
3389 BEGIN--{
3390 -- Convert Amount from GC3 currency code to carrier currency code
3391 -- OTM R12 Why using total_amount here
3392
3393 -- OTM R12 use positional style
3394 l_freight_info_tab(l_count).total_amount
3395 := GL_CURRENCY_API.convert_amount(
3396 x_from_currency => l_freight_info_tab(l_count).currency_code,
3397 x_to_currency => l_carrier_cur_code,
3398 x_conversion_date => SYSDATE,
3399 x_conversion_type => l_freight_info_tab(l_count).conversion_type_code,
3400 x_amount => l_freight_info_tab(l_count).unit_amount);
3401
3402 IF l_freight_info_tab(l_count).total_amount IS NULL THEN
3403 RAISE gl_currency_excp;
3404 END IF;
3405 -- OTM R12 use positional style
3406
3407 EXCEPTION
3408 -- OTM R12 Start
3409 WHEN gl_currency_api.no_rate THEN
3410 -- Bug 5886042
3411 -- currency conversion_type in the error message should be
3412 -- user_conversion_type
3413 BEGIN
3414 OPEN c_get_user_conv_type(l_conversion_type_code);
3415 FETCH c_get_user_conv_type INTO l_user_conv_type;
3416 CLOSE c_get_user_conv_type;
3417 EXCEPTION
3418 WHEN OTHERS THEN
3419 l_user_conv_type := l_conversion_type_code;
3420 IF c_get_user_conv_type%ISOPEN THEN
3421 CLOSE c_get_user_conv_type;
3422 END IF;
3423
3424 IF l_debug_on THEN
3425 WSH_DEBUG_SV.logmsg(l_module_name, 'Error occurred while getting the user currency conversion type');
3426 WSH_DEBUG_SV.log(l_module_name, 'l_currency_conversion_type', l_conversion_type_code);
3427 END IF;
3428 END;
3429 -- end of changes for Bug 5886042
3430
3431 FND_MESSAGE.SET_NAME('WSH','WSH_CURR_CONV_ERROR');
3432 FND_MESSAGE.SET_TOKEN('FROM_CURR',l_freight_info_tab(l_count).currency_code);
3433 FND_MESSAGE.SET_TOKEN('TO_CURR',l_carrier_cur_code);
3434 FND_MESSAGE.SET_TOKEN('CONV_TYPE',l_user_conv_type);-- Bug 5886042
3435 WSH_UTIL_CORE.ADD_MESSAGE(WSH_UTIL_CORE.G_RET_STS_ERROR);
3436 l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3437 IF l_debug_on THEN
3438 WSH_DEBUG_SV.log(l_module_name, 'GL_CURRENCY_API.CONVERT_AMOUNT'
3439 ||' failed no_rate', l_return_status);
3440 END IF;
3441 GOTO trip_error;
3442 WHEN gl_currency_api.invalid_currency THEN
3443 -- Bug 5886042
3444 -- currency conversion_type in the error message should be
3445 -- user_conversion_type
3446 BEGIN
3447 OPEN c_get_user_conv_type(l_conversion_type_code);
3448 FETCH c_get_user_conv_type INTO l_user_conv_type;
3449 CLOSE c_get_user_conv_type;
3450 EXCEPTION
3451 WHEN OTHERS THEN
3452 l_user_conv_type := l_conversion_type_code;
3453 IF c_get_user_conv_type%ISOPEN THEN
3454 CLOSE c_get_user_conv_type;
3455 END IF;
3456
3457 IF l_debug_on THEN
3458 WSH_DEBUG_SV.logmsg(l_module_name, 'Error occurred while getting the user currency conversion type');
3459 WSH_DEBUG_SV.log(l_module_name, 'l_currency_conversion_type', l_conversion_type_code);
3460 END IF;
3461 END;
3462 -- end of changes for Bug 5886042
3463
3464 FND_MESSAGE.SET_NAME('WSH','WSH_CURR_CONV_ERROR');
3465 FND_MESSAGE.SET_TOKEN('FROM_CURR',l_freight_info_tab(l_count).currency_code);
3466 FND_MESSAGE.SET_TOKEN('TO_CURR',l_carrier_cur_code);
3467 FND_MESSAGE.SET_TOKEN('CONV_TYPE',l_user_conv_type);-- Bug 5886042
3468 WSH_UTIL_CORE.ADD_MESSAGE(WSH_UTIL_CORE.G_RET_STS_ERROR);
3469 l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3470 IF l_debug_on THEN
3471 WSH_DEBUG_SV.log(l_module_name, 'GL_CURRENCY_API.CONVERT_AMOUNT'
3472 ||' failed invalid_currency', l_return_status);
3473 END IF;
3474 GOTO trip_error;
3475
3476 -- OTM R12 end
3477 WHEN gl_currency_excp THEN
3478 l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3479 IF l_debug_on THEN
3480 WSH_DEBUG_SV.log(l_module_name, 'GL_CURRENCY_API.CONVERT_AMOUNT returned NULL :', l_return_status);
3481 END IF;
3482 GOTO trip_error;
3483
3484 WHEN OTHERS THEN
3485 l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3486 IF l_debug_on THEN
3487 WSH_DEBUG_SV.log(l_module_name, 'GL_CURRENCY_API.CONVERT_AMOUNT failed:', l_return_status);
3488 END IF;
3489 GOTO trip_error;
3490 END;--}
3491
3492 l_freight_info_tab(l_count).currency_code := l_carrier_cur_code;
3493 l_freight_info_tab(l_count).unit_amount := l_freight_info_tab(l_count).total_amount;
3494
3495 IF l_debug_on THEN
3496 WSH_DEBUG_SV.logmsg(l_module_name, 'Converted Amount:'||l_freight_info_tab(l_count).total_amount);
3497 WSH_DEBUG_SV.logmsg(l_module_name, 'New Currency Code:'||l_freight_info_tab(l_count).currency_code);
3498 END IF;
3499
3500 END IF;--}
3501
3502 --
3503 -- Call Create_Update_freight_cost here, for each record
3504 -- The API was not accepting 2 freight cost records for same delivery_id
3505 -- Need to look into this later!!!
3506 -- l_freight_info_tab.count will be > 0
3507
3508 l_in_rec.action_code := 'CREATE';
3509 l_in_rec.caller := 'FTE_TMS_INTEGRATION'; --'FTE_TMS_RELEASE';
3510
3511 IF l_debug_on THEN
3512 WSH_DEBUG_SV.logmsg(l_module_name, 'Calling WSH_FREIGHT_COSTS_GRP.Create_Update_Freight_Costs');
3513 END IF;
3514
3515 WSH_FREIGHT_COSTS_GRP.Create_Update_Freight_Costs
3516 (p_api_version_number => 1.0,
3517 p_init_msg_list => FND_API.G_TRUE,
3518 p_commit => FND_API.G_FALSE,
3519 p_freight_info_tab => l_freight_info_tab,
3520 p_in_rec => l_in_rec,
3521 x_out_tab => l_out_tab,
3522 x_return_status => l_rs,
3523 x_msg_count => l_msg_count,
3524 x_msg_data => l_msg_data);
3525
3526 IF l_debug_on THEN
3527 WSH_DEBUG_SV.logmsg(l_module_name, 'Return Status after Create Freight Cost:'||l_rs);
3528 END IF;
3529
3530 IF l_rs IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
3531 l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3532 IF l_debug_on THEN
3533 WSH_DEBUG_SV.log(l_module_name, 'Create_Update_Freight_Costs Failed: l_return_status', l_return_status);
3534 END IF;
3535 EXIT; -- need to exit out of the parent loop also!!!
3536 -- OTM R12 will never reach this GOTO and GOTO not required as the immediate next section is trip_error
3537 --GOTO trip_error;
3538 END IF;
3539 -- end of call to create_update_freight API
3540
3541 END IF;--}-- only if freight cost record has currency
3542
3543 END LOOP;--}-- loop across freight_costs_interface table
3544 IF l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR THEN
3545 EXIT; -- need to exit out of the parent loop also!!!
3546 -- OTM R12 will never reach this GOTO and GOTO not required as the immediate next section is trip_error
3547 --GOTO trip_error;
3548 END IF;
3549 END LOOP;--} -- loop across successful deliveries
3550 END IF;
3551
3552
3553 IF l_debug_on THEN
3554 WSH_DEBUG_SV.logmsg(l_module_name, 'Freight Info Table Count:'||l_freight_info_tab.count);
3555 END IF;
3556 END; --}
3557
3558 -- OTM Dock Door App Sched Proj
3559 -- 4h. Stamping the Loading Sequence Number for OTM enabled WMS organization
3560 DECLARE
3561 CURSOR c_stops (c_trip_id IN number) IS
3562 SELECT wts.stop_id stop_id
3563 FROM wsh_trips wt,
3564 wsh_trip_stops wts
3565 WHERE wt.trip_id = c_trip_id
3566 AND wt.trip_id = wts.trip_id
3567 ORDER BY wts.stop_sequence_number DESC;
3568
3569 CURSOR c_lock_deliveries_and_details (c_drop_off_stop_id IN number) IS
3570 SELECT wnd.delivery_id, wdd.delivery_detail_id
3571 FROM wsh_new_deliveries wnd,
3572 wsh_delivery_legs wdl,
3573 wsh_delivery_assignments wda, wsh_delivery_details wdd
3574 WHERE wdl.drop_off_stop_id = c_drop_off_stop_id
3575 AND wdl.delivery_id = wnd.delivery_id
3576 AND WSH_UTIL_VALIDATE.CHECK_WMS_ORG(wnd.organization_id) = 'Y'
3577 AND wnd.delivery_id = wda.delivery_id
3578 AND wda.delivery_detail_id = wdd.delivery_detail_id
3579 FOR UPDATE OF wnd.loading_sequence, wdd.load_seq_number NOWAIT;
3580
3581 l_del_tab WSH_UTIL_CORE.ID_TAB_TYPE;
3582 l_del_det_tab WSH_UTIL_CORE.ID_TAB_TYPE;
3583 l_loading_sequence NUMBER := 0;
3584 l_stop_id_rec NUMBER;
3585
3586 BEGIN
3587 FOR crec in c_stops(l_trip_id) LOOP
3588 IF l_debug_on THEN
3589 WSH_DEBUG_SV.log(l_module_name, 'Locking Deliveries and Details for stop '||crec.stop_id);
3590 END IF;
3591
3592 l_stop_id_rec := crec.stop_id;
3593 l_loading_sequence := l_loading_sequence + 10;
3594 l_del_tab.DELETE;
3595 l_del_det_tab.DELETE;
3596
3597 OPEN c_lock_deliveries_and_details (crec.stop_id);
3598 FETCH c_lock_deliveries_and_details BULK COLLECT INTO l_del_tab, l_del_det_tab;
3599
3600 IF l_del_tab.COUNT > 0 THEN
3601 IF l_debug_on THEN
3602 WSH_DEBUG_SV.log(l_module_name, 'Updating Deliveries and Details With Loading Sequence '||l_loading_sequence);
3603 END IF;
3604
3605 FORALL i in l_del_tab.FIRST..l_del_tab.LAST
3606 UPDATE wsh_new_deliveries
3607 SET loading_sequence = l_loading_sequence,
3608 last_update_date = SYSDATE,
3609 last_updated_by = FND_GLOBAL.USER_ID,
3610 last_update_login = FND_GLOBAL.LOGIN_ID
3611 WHERE delivery_id = l_del_tab(i);
3612
3613 FORALL i in l_del_det_tab.FIRST..l_del_det_tab.LAST
3614 UPDATE wsh_delivery_details
3615 SET load_seq_number = l_loading_sequence,
3616 last_update_date = SYSDATE,
3617 last_updated_by = FND_GLOBAL.USER_ID,
3618 last_update_login = FND_GLOBAL.LOGIN_ID
3619 WHERE delivery_detail_id = l_del_det_tab(i);
3620 END IF;
3621
3622 CLOSE c_lock_deliveries_and_details;
3623
3624 END LOOP;
3625
3626 EXCEPTION
3627 WHEN OTHERS THEN
3628 l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3629 IF c_lock_deliveries_and_details%ISOPEN then
3630 CLOSE c_lock_deliveries_and_details;
3631 END IF;
3632 IF l_debug_on THEN
3633 WSH_DEBUG_SV.log(l_module_name, 'Unable to lock Deliveries/Details for drop off stop '||l_stop_id_rec);
3634 WSH_DEBUG_SV.log(l_module_name, 'l_return_status', l_return_status);
3635 END IF;
3636 GOTO trip_error;
3637 END;
3638
3639 -- 4i. Calling the WMS API
3640 DECLARE
3641 l_rs VARCHAR2(1);
3642 l_msg_count NUMBER;
3643 l_msg_data VARCHAR2(32767);
3644 l_message VARCHAR2(2000);
3645 BEGIN
3646 IF (l_dock_appt_tab.COUNT > 0) THEN --{
3647 IF l_debug_on THEN
3648 WSH_DEBUG_SV.log(l_module_name, 'Calling wms_dock_appointments_pub.OTM_Dock_Appointment',WSH_DEBUG_SV.C_PROC_LEVEL);
3649 END IF;
3650 -- Calling the WMS API with the Dock Door App Sched Info.
3651 wms_dock_appointments_pub.OTM_Dock_Appointment(p_dock_appt_tab => l_dock_appt_tab,
3652 x_return_status => l_rs,
3653 x_msg_count => l_msg_count,
3654 x_msg_data => l_msg_data);
3655 IF l_debug_on THEN
3656 WSH_DEBUG_SV.log(l_module_name, 'wms_dock_appointments_pub.OTM_Dock_Appointment: Return Status', l_rs);
3657 END IF;
3658 IF l_rs IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
3659 l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3660 FOR l_err_msg IN 1..l_msg_count LOOP
3661 l_message := fnd_msg_pub.get(l_err_msg,'F');
3662 l_message := replace(l_message,chr(0),' ');
3663 IF l_debug_on THEN
3664 WSH_DEBUG_SV.log(l_module_name, 'ERROR: '|| l_message);
3665 END IF;
3666 END LOOP;
3667 fnd_msg_pub.delete_msg();
3668 FND_MESSAGE.SET_NAME('WSH','WSH_DOCK_SCHED_ERROR');
3669 WSH_UTIL_CORE.ADD_MESSAGE(WSH_UTIL_CORE.G_RET_STS_ERROR);
3670 GOTO trip_error;
3671 END IF;
3672 END IF; --} l_dock_appt_tab.COUNT > 0
3673 EXCEPTION
3674 WHEN OTHERS THEN
3675 l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3676 IF l_debug_on THEN
3677 WSH_DEBUG_SV.log(l_module_name, 'Error while calling wms_dock_appointments_pub.OTM_Dock_Appointment' || SQLERRM);
3678 END IF;
3679 GOTO trip_error;
3680 END;
3681 --
3682
3683 -- For each trip interface
3684 <<trip_error>>
3685
3686 -- 4j. Final Step : error logging, cleaning up interface table
3687 IF l_debug_on THEN
3688 WSH_DEBUG_SV.logmsg(l_module_name,'===============================');
3689 WSH_DEBUG_SV.logmsg(l_module_name,'4h. DATA CLEANUP FOR THE TRIP');
3690 WSH_DEBUG_SV.logmsg(l_module_name,'===============================');
3691 END IF;
3692
3693 DECLARE
3694 l_rs VARCHAR2(1);
3695 l_msg_count NUMBER;
3696 l_msg_data VARCHAR2(32767);
3697 l_exception_id NUMBER;
3698 l_exception_message VARCHAR2(2000);
3699 l_exception_name VARCHAR2(30);
3700 l_count NUMBER;
3701
3702 BEGIN--{
3703
3704 IF l_debug_on THEN
3705 WSH_DEBUG_SV.logmsg(l_module_name,'Status here:'||l_return_status );
3706 END IF;
3707
3708 -- Based on errors for each trip
3709 -- Status of T is for special case of Invalid Location for Trip Stop
3710 -- For Invalid Location error, donot insert records in error table,
3711 -- Only Log Delivery Level Error exceptions and purge the data from
3712 -- Interface tables
3713 IF l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR,'T') THEN --{
3714 --IF l_errors_tab.count > 0 THEN
3715 IF l_debug_on THEN
3716 WSH_DEBUG_SV.logmsg(l_module_name,'Rollback.....' );
3717 END IF;
3718
3719 ROLLBACK TO process_single_trip;
3720
3721 l_error_trips := l_error_trips + 1;
3722 -- Set completion status to Warning, no concept of error
3723 -- so no need to count and conditionally set error/warning
3724 l_completion_status := 'WARNING';
3725
3726 IF l_debug_on THEN
3727 WSH_DEBUG_SV.logmsg(l_module_name,'Set Completion to WARNING');
3728 WSH_DEBUG_SV.logmsg(l_module_name,'Before Inserting in Interface error,count:'||l_dleg_local_tab.count );
3729 END IF;
3730
3731 IF l_return_status = 'T' THEN--{
3732 IF l_debug_on THEN
3733 WSH_DEBUG_SV.logmsg(l_module_name,'Before Purging the Interface Data' );
3734 END IF;
3735
3736 -- Need to purge the data in interface tables for invalid stop location
3737 -- Also, clean up existing Freight cost and Exceptions in EBS
3738 purge_interface_data(
3739 p_tp_plan_name => NULL,
3740 p_trip_interface_id => l_trip_info_tab(i).trip_interface_id,
3741 p_commit_flag => 'N',
3742 p_exception_action => 'PURGE',
3743 x_return_status => l_rs);
3744
3745 IF l_debug_on THEN
3746 WSH_DEBUG_SV.logmsg(l_module_name,'After Purging the Interface Data:'||l_rs );
3747 END IF;
3748
3749 -- OTM R12
3750 IF l_rs IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
3751 l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3752 IF l_debug_on THEN
3753 WSH_DEBUG_SV.log(l_module_name, 'purge_interface_data Failed');
3754 END IF;
3755 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3756 END IF;
3757 -- OTM R12
3758
3759 END IF;--}
3760
3761 -- For Delete Shipment Cases, there will not be any delivery level information
3762 IF l_dleg_local_tab.count > 0 THEN--{
3763 IF l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR THEN--{
3764
3765 -- Insert record in wsh_interface_errors table
3766 -- Generic error message is to be logged against all deliveries
3767 -- Handled in stamp_interface_errors
3768 stamp_interface_error(
3769 p_group_id => l_trip_info_tab(i).group_id, --Bug7717569 replaced p_group_id with l_trip_info_tab(i).group_id
3770 p_entity_table_name => 'WSH_NEW_DELIVERIES_INTERFACE',
3771 p_entity_interface_id => l_trip_info_tab(i).trip_interface_id,
3772 p_message_name => 'WSH_OTM_GENERIC', -- NEW MESSAGE !!!
3773 p_dleg_tab => l_dleg_local_tab,
3774 x_errors_tab => l_errors_tab,
3775 x_return_status => l_rs);
3776
3777 IF l_debug_on THEN
3778 WSH_DEBUG_SV.logmsg(l_module_name,'Return Status after Stamp_Interface_error'||l_rs);
3779 END IF;
3780
3781 -- OTM R12
3782 IF l_rs IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
3783 l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3784 IF l_debug_on THEN
3785 WSH_DEBUG_SV.log(l_module_name, 'stamp_interface_error Failed');
3786 END IF;
3787 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3788 END IF;
3789 -- OTM R12
3790
3791 END IF;--} -- l_return_status = E
3792
3793 IF l_debug_on THEN
3794 WSH_DEBUG_SV.logmsg(l_module_name,'Now Logging Error Exceptions' );
3795 END IF;
3796
3797 -- Log error level exception for all deliveries, use l_dleg_local_tab
3798 -- to find deliveries which are on this trip
3799 IF l_return_status = 'T' THEN
3800 l_exception_name := 'WSH_OTM_INVALID_LOC'; -- Need new exception here
3801 FND_MESSAGE.SET_NAME('WSH', 'WSH_OTM_INVALID_LOC');
3802 ELSE
3803 l_exception_name := 'WSH_OTM_SHIPMENT_ERROR';
3804 FND_MESSAGE.SET_NAME('WSH', 'WSH_OTM_DELIVERY_FAIL');
3805 END IF;
3806
3807 l_exception_message := FND_MESSAGE.Get;
3808
3809 FOR rec IN l_dleg_local_tab.FIRST..l_dleg_local_tab.LAST
3810 LOOP--{
3811 IF l_dleg_local_tab(rec).trip_interface_id = l_trip_info_tab(i).trip_interface_id THEN
3812 l_exception_id := NULL; -- need to initialize for each exception
3813
3814 WSH_XC_UTIL.log_exception(
3815 p_api_version => 1.0,
3816 x_return_status => l_rs,
3817 x_msg_count => l_msg_count,
3818 x_msg_data => l_msg_data,
3819 x_exception_id => l_exception_id,
3820 p_exception_location_id => l_dleg_local_tab(rec).initial_pickup_location_id,
3821 p_logged_at_location_id => l_dleg_local_tab(rec).initial_pickup_location_id,
3822 p_logging_entity => 'SHIPPER',
3823 p_logging_entity_id => FND_GLOBAL.USER_ID,
3824 p_exception_name => l_exception_name, -- 'WSH_OTM_SHIPMENT_ERROR',
3825 p_message => substrb(l_exception_message,1,2000),
3826 p_delivery_id => l_dleg_local_tab(rec).delivery_id);
3827 IF l_debug_on THEN
3828 WSH_DEBUG_SV.logmsg(l_module_name,'Return Status after Log_exception'||l_rs);
3829 END IF;
3830
3831 -- OTM R12
3832 IF l_rs IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
3833 l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3834 IF l_debug_on THEN
3835 WSH_DEBUG_SV.log(l_module_name, 'WSH_XC_UTIL.log_exception Failed');
3836 END IF;
3837 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3838 END IF;
3839 -- OTM R12
3840
3841 END IF;
3842 END LOOP; --}
3843 END IF;--} --l_dleg_local_tab.count > 0
3844
3845 ELSE -- l_return_status <> 'E' --} --{
3846
3847 IF l_debug_on THEN
3848 WSH_DEBUG_SV.logmsg(l_module_name,'Success; Now Setting Interface flag to ANSWER RECEIVED' );
3849 END IF;
3850
3851 -- l_dlvy_id_tab only has successfully processed deliveries
3852 -- l_dlvy_id_tab was used in freight_costs section also, make sure it is
3853 -- still kept only for good deliveries!!!
3854 -- Do not update the tms_version_number, to accept multiple messages from
3855 -- GC3 for same version of the delivery
3856 -- TMS_VERSION_NUMBER gets updated due to the EBS code flow, need to set it back
3857 -- to the original value
3858 -- OTM R12 Use update_tms_interface_flag API
3859
3860 IF l_debug_on THEN
3861 WSH_DEBUG_SV.logmsg(l_module_name,'Before Logging Information Only Exceptions' );
3862 END IF;
3863
3864 -- All the records in l_dleg_local_tab should have been processed
3865 -- Count should be identical in l_dleg_local_tab and l_dlvy_id_tab
3866 IF l_dleg_local_tab.COUNT > 0 THEN
3867 FOR rec IN l_dleg_local_tab.FIRST..l_dleg_local_tab.LAST
3868 LOOP--{
3869 IF l_dleg_local_tab(rec).trip_interface_id = l_trip_info_tab(i).trip_interface_id THEN
3870
3871
3872 -- Bug#7491598(ER,defer planned shipment iface): Closing of exceptions should be done only
3873 -- when EBS delivery tms version number is less than or equal to tms version number from OTM
3874 IF l_dleg_local_tab(rec).tms_version_number <= l_dleg_local_tab(rec).otm_tms_version_number THEN
3875 --{
3876 --
3877 -- ECO 5171627 Close the previous AWAIT Trip exception
3878 -- OTM R12
3879 WSH_XC_UTIL.Purge (
3880 p_api_version => 1.0,
3881 x_return_status => l_rs,
3882 x_msg_count => l_msg_count,
3883 x_msg_data => l_msg_data,
3884 x_no_of_recs_purged => l_count,
3885 p_exception_name => 'WSH_OTM_DEL_AWAIT_TRIP',
3886 p_delivery_id => l_dleg_local_tab(rec).delivery_id,
3887 p_delivery_contents => 'N',
3888 p_action => 'CLOSED'
3889 );
3890
3891 IF l_debug_on THEN
3892 WSH_DEBUG_SV.logmsg(l_module_name,'Return Status after WSH_XC_UTIL.Purge' || l_rs);
3893 END IF;
3894 -- OTM R12
3895 IF l_rs IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
3896 l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3897 IF l_debug_on THEN
3898 WSH_DEBUG_SV.log(l_module_name, 'WSH_XC_UTIL.Purge Failed');
3899 END IF;
3900 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3901 END IF;
3902
3903 -- OTM R12 populate record structure to call update_tms_interface_flag
3904 l_upd_dlvy_id_tab(l_upd_dlvy_id_tab.COUNT + 1) := l_dleg_local_tab(rec).delivery_id;
3905 l_upd_dlvy_tms_tab(l_upd_dlvy_tms_tab.COUNT + 1) := WSH_NEW_DELIVERIES_PVT.C_TMS_ANSWER_RECEIVED;
3906 -- OTM R12
3907 ELSE
3908 l_upd_dlvy_id_tab(l_upd_dlvy_id_tab.COUNT + 1) := l_dleg_local_tab(rec).delivery_id;
3909 l_upd_dlvy_tms_tab(l_upd_dlvy_tms_tab.COUNT + 1) := l_dleg_local_tab(rec).tms_interface_flag; --update to the old status.
3910 --}
3911 END IF;
3912 -- -- Bug#7491598(ER,defer planned shipment iface):end
3913 END IF;
3914
3915 END LOOP; --}
3916
3917 END IF;
3918
3919 IF l_debug_on THEN
3920 WSH_DEBUG_SV.logmsg(l_module_name,'Before Purging the Interface Data' );
3921 END IF;
3922
3923 -- Need to purge the data in interface tables after successful processing
3924 -- p_exception_action is 'CLOSE' to close specific exceptions logged for the
3925 -- deliveries
3926 purge_interface_data(
3927 p_tp_plan_name => NULL,
3928 p_trip_interface_id => l_trip_info_tab(i).trip_interface_id,
3929 p_commit_flag => 'N',
3930 p_exception_action => 'CLOSE', --'PURGE' is called by Arindam
3931 x_return_status => l_rs);
3932
3933 IF l_debug_on THEN
3934 WSH_DEBUG_SV.logmsg(l_module_name,'After Purging the Interface Data:'||l_rs );
3935 END IF;
3936
3937 IF l_rs IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
3938 l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3939 IF l_debug_on THEN
3940 WSH_DEBUG_SV.log(l_module_name, 'purge_interface_data Failed');
3941 END IF;
3942 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3943 END IF;
3944
3945 l_success_trips := l_success_trips + 1;
3946
3947 END IF;--} -- l_return_status <> 'E'
3948 END;--} -- end of data cleanup
3949 -- For each trip, delete the messages on stack
3950 fnd_msg_pub.delete_msg();
3951 END LOOP; --} for each trip in l_trip_info_tab
3952 END IF; --}l_trip_info_tab.count > 0
3953 --=======================================================================
3954
3955 -- 5. Final Step : error logging, cleaning up interface table
3956 IF l_debug_on THEN
3957 WSH_DEBUG_SV.logmsg(l_module_name,'===============================');
3958 WSH_DEBUG_SV.logmsg(l_module_name,'5. UNASSIGN DELIVERIES FROM TRIP');
3959 WSH_DEBUG_SV.logmsg(l_module_name,'===============================');
3960 WSH_DEBUG_SV.logmsg(l_module_name,'Deliveries to be Unassigned :'||l_unassigned_delivery_id_tab.count);
3961 WSH_DEBUG_SV.logmsg(l_module_name,'Version of Deliveries to be Unassigned :'||l_unassigned_dlvy_version_tab.count);
3962 END IF;
3963 IF l_unassigned_delivery_id_tab.count > 0 THEN--{
3964 l_unassigned_del_index := l_unassigned_delivery_id_tab.FIRST;
3965 WHILE (l_unassigned_del_index IS NOT NULL )
3966 LOOP--{
3967 --glog proj, getting initial value of the delivery(ie. interface_flag)
3968 l_delivery_info_tab.DELETE;
3969 IF l_debug_on THEN
3970 WSH_DEBUG_SV.log(l_module_name, 'l_unassigned_delivery_id_tab('||l_unassigned_del_index||')='||
3971 l_unassigned_delivery_id_tab(l_unassigned_del_index));
3972
3973 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_NEW_DELIVERIES_PVT.TABLE_TO_RECORD',WSH_DEBUG_SV.C_PROC_LEVEL);
3974 END IF;
3975
3976 WSH_NEW_DELIVERIES_PVT.TABLE_TO_RECORD(p_delivery_id => l_unassigned_delivery_id_tab(l_unassigned_del_index),
3977 x_delivery_rec => l_delivery_info,
3978 x_return_status => l_return_status);
3979
3980 -- OTM R12
3981 IF l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
3982 --l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3983 IF l_debug_on THEN
3984 WSH_DEBUG_SV.log(l_module_name, 'WSH_NEW_DELIVERIES_PVT.TABLE_TO_RECORD Failed');
3985 END IF;
3986 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3987 END IF;
3988 -- OTM R12
3989
3990
3991 l_delivery_info_tab(l_delivery_info_tab.COUNT+1) := l_delivery_info;
3992
3993 IF WSH_NEW_DELIVERY_ACTIONS.IS_DELIVERY_EMPTY(l_unassigned_delivery_id_tab(l_unassigned_del_index)) = 'N' THEN
3994 -- set the flag to AW
3995 l_new_interface_flag_tab(l_delivery_info_tab.count) := WSH_NEW_DELIVERIES_PVT.C_TMS_AWAITING_ANSWER;
3996
3997 ELSIF WSH_NEW_DELIVERY_ACTIONS.IS_DELIVERY_EMPTY(l_unassigned_delivery_id_tab(l_unassigned_del_index)) = 'Y' THEN
3998 -- set the flag to NS
3999 l_new_interface_flag_tab(l_delivery_info_tab.count) := WSH_NEW_DELIVERIES_PVT.C_TMS_NOT_TO_BE_SENT;
4000 END IF;
4001
4002 -- OTM R12 populate record structure to call update_tms_interface_flag
4003 l_upd_dlvy_id_tab(l_upd_dlvy_id_tab.COUNT + 1) := l_unassigned_delivery_id_tab(l_unassigned_del_index);
4004 l_upd_dlvy_tms_tab(l_upd_dlvy_tms_tab.COUNT + 1) := l_new_interface_flag_tab(l_delivery_info_tab.count);
4005 -- OTM R12
4006 l_unassigned_del_index := l_unassigned_delivery_id_tab.NEXT(l_unassigned_del_index);
4007
4008 END LOOP;--}
4009
4010 IF l_debug_on THEN
4011 WSH_DEBUG_SV.log(l_module_name,'Delivery Info Tab Count:'||l_delivery_info_tab.count);
4012 WSH_DEBUG_SV.log(l_module_name,'New Interface Flag Tab Count:'||l_new_interface_flag_tab.count);
4013 END IF;
4014
4015 END IF; --}
4016
4017 -- OTM R12 call update_tms_interface_flag here
4018
4019 WSH_NEW_DELIVERIES_PVT.update_tms_interface_flag
4020 ( p_delivery_id_tab => l_upd_dlvy_id_tab,
4021 p_tms_interface_flag_tab => l_upd_dlvy_tms_tab,
4022 x_return_status => l_return_status);
4023
4024 IF l_debug_on THEN
4025 WSH_DEBUG_SV.log(l_module_name, 'update_tms_interface_flag : l_return_status : ', l_return_status);
4026 END IF;
4027 IF l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
4028 --l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
4029 ROLLBACK TO process_group;
4030 l_completion_status := 'WARNING';
4031 --l_completion_status := 'ERROR';
4032 ELSE
4033
4034 IF l_debug_on THEN
4035 WSH_DEBUG_SV.logmsg(l_module_name,'Now Committing for the group....');
4036 END IF;
4037
4038 COMMIT;
4039
4040 END IF;
4041 -- OTM R12
4042
4043 IF l_debug_on THEN
4044 WSH_DEBUG_SV.logmsg(l_module_name,'======================================');
4045 WSH_DEBUG_SV.logmsg(l_module_name,'Completion Status'||l_completion_status);
4046 WSH_DEBUG_SV.logmsg(l_module_name,'Trip Error Count'||l_error_trips);
4047 WSH_DEBUG_SV.logmsg(l_module_name,'Trip Success Count'||l_success_trips);
4048 WSH_DEBUG_SV.logmsg(l_module_name,'======================================');
4049 END IF;
4050
4051 IF l_completion_status = 'WARNING' THEN
4052 errbuf := 'Atleast one trip was not Interfaced';
4053 retcode := '1';
4054 l_ret_code := FND_CONCURRENT.SET_COMPLETION_STATUS(l_completion_status,'');
4055 ELSIF l_completion_status = 'NORMAL' THEN
4056 retcode := '0';
4057 l_ret_code := FND_CONCURRENT.SET_COMPLETION_STATUS(l_completion_status,'');
4058 -- OTM R12
4059 /*
4060 ELSE -- Cannot come here
4061 errbuf := 'Exception occurred in Release_Planned_Shipment';
4062 retcode := '2';
4063 l_ret_code := FND_CONCURRENT.SET_COMPLETION_STATUS(l_completion_status,'');
4064 */
4065 -- OTM R12
4066 END IF;
4067
4068 IF l_debug_on THEN
4069 WSH_DEBUG_SV.log(l_module_name,'ERRBUF',errbuf);
4070 WSH_DEBUG_SV.log(l_module_name,'RETCODE',retcode);
4071 WSH_DEBUG_SV.pop(l_module_name);
4072 END IF;
4073
4074 EXCEPTION
4075 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4076 ROLLBACK TO process_group;
4077 --
4078 IF l_debug_on THEN
4079 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
4080 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
4081 END IF;
4082 --
4083 -- Always concurrent request,no online option
4084 l_ret_code := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR','');
4085 errbuf := 'Exception occurred in Release_Planned_Shipment';
4086 retcode := '2';
4087 --
4088 WHEN OTHERS THEN
4089 -- OTM R12
4090 --ROLLBACK;
4091 ROLLBACK TO process_group;
4092 -- OTM R12
4093 IF c_tms_interface_trips%ISOPEN THEN
4094 CLOSE c_tms_interface_trips;
4095 END IF;
4096 IF c_tms_interface_stops%ISOPEN THEN
4097 CLOSE c_tms_interface_stops;
4098 END IF;
4099 IF c_tms_interface_dlegs%ISOPEN THEN
4100 CLOSE c_tms_interface_dlegs;
4101 END IF;
4102 IF c_tms_interface_trips_plan%ISOPEN THEN
4103 CLOSE c_tms_interface_trips_plan;
4104 END IF;
4105 IF c_tms_interface_trips_del%ISOPEN THEN
4106 CLOSE c_tms_interface_trips_del;
4107 END IF;
4108 IF c_tms_interface_trips_all%ISOPEN THEN
4109 CLOSE c_tms_interface_trips_all;
4110 END IF;
4111 IF c_tms_interface_stops_plan%ISOPEN THEN
4112 CLOSE c_tms_interface_stops_plan;
4113 END IF;
4114 IF c_tms_interface_stops_del%ISOPEN THEN
4115 CLOSE c_tms_interface_stops_del;
4116 END IF;
4117 IF c_tms_interface_stops_all%ISOPEN THEN
4118 CLOSE c_tms_interface_stops_all;
4119 END IF;
4120
4121 IF c_tms_interface_dlegs_plan%ISOPEN THEN
4122 CLOSE c_tms_interface_dlegs_plan;
4123 END IF;
4124 IF c_tms_interface_dlegs_del%ISOPEN THEN
4125 CLOSE c_tms_interface_dlegs_del;
4126 END IF;
4127 IF c_tms_interface_dlegs_all%ISOPEN THEN
4128 CLOSE c_tms_interface_dlegs_all;
4129 END IF;
4130 IF c_lock_trip_interface%ISOPEN THEN
4131 CLOSE c_lock_trip_interface;
4132 END IF;
4133 IF c_get_stops%ISOPEN THEN
4134 CLOSE c_get_stops;
4135 END IF;
4136 IF c_get_deliveries%ISOPEN THEN
4137 CLOSE c_get_deliveries;
4138 END IF;
4139 IF c_get_dleg_id%ISOPEN THEN
4140 CLOSE c_get_dleg_id;
4141 END IF;
4142 IF c_freight_int_cur%ISOPEN THEN
4143 CLOSE c_freight_int_cur;
4144 END IF;
4145 IF c_get_currency_code%ISOPEN THEN
4146 CLOSE c_get_currency_code;
4147 END IF;
4148 --
4149 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
4150 FND_MSG_PUB.Add_Exc_Msg( 'WSH_TMS_RELEASE', 'Release_Planned_Shipment' );
4151 END IF;
4152 --
4153 -- Always concurrent request,no online option
4154 l_ret_code := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR','');
4155 errbuf := 'Exception occurred in Release_Planned_Shipment';
4156 retcode := '2';
4157 --
4158 WSH_UTIL_CORE.DEFAULT_HANDLER(
4159 'WSH_TMS_RELEASE.RELEASE_PLANNED_SHIPMENT',
4160 l_module_name);
4161 IF l_debug_on THEN
4162 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
4163 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
4164 END IF;
4165 END release_planned_shipment;
4166
4167 --=========================================================================
4168 -- Procedure: PURGE_INTERFACE_DATA
4169 -- Description:
4170 -- wsh_del_details_interface and wsh_del_assgn_interface will not be populated
4171 -- Purge records in wsh_freight_costs_interface
4172 -- If p_exception_action =PURGE, then purge the exceptions
4173 -- for WSH_OTM_SHIPMENT_ERROR
4174 -- elsif p_exception_action = CLOSE, then close the exception
4175 -- for WSH_OTM_SHIPMENT_ERROR(used for reprocessing from Interface Mesg
4176 -- Corrections Form)
4177 -- Purge any records in wsh_interface_errors
4178 -- Purge records in wsh_new_del_interface
4179 -- Purge records in wsh_del_legs_interface
4180 -- Purge records in wsh_trip_stops_interface
4181 -- Purge records in wsh_trips_interface
4182 -- Index exists on group_id and trip_interface_id of wsh_trips_interface,
4183 -- so use trip_interface_id.
4184 --
4185 -- Usage:
4186 -- 1. Inbound BPEL Process calls this API to purge data based on tp_plan_name
4187 -- 2. Inbound Interface Code calls this to purge interface data and close
4188 -- exceptions
4189 -- Assumption: One of the two p_tp_plan_name or p_trip_interface_id will
4190 -- be populated
4191 --
4192 --=========================================================================
4193 PROCEDURE purge_interface_data(
4194 p_tp_plan_name IN VARCHAR2 DEFAULT NULL,
4195 p_trip_interface_id IN VARCHAR2 DEFAULT NULL,
4196 p_commit_flag IN VARCHAR2,
4197 p_exception_action IN VARCHAR2 DEFAULT 'PURGE',
4198 x_return_status OUT NOCOPY VARCHAR2) IS
4199
4200 CURSOR c_get_interface_id (p_tp_plan_name IN VARCHAR2) IS
4201 SELECT wti.trip_interface_id
4202 FROM wsh_trips_interface wti
4203 WHERE wti.tp_plan_name = p_tp_plan_name
4204 AND wti.interface_action_code = G_TMS_RELEASE_CODE;
4205
4206 l_trip_interface_id NUMBER;
4207 l_trip_interface_id_tab WSH_UTIL_CORE.id_tab_type;
4208
4209 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'PURGE_INTERFACE_DATA';
4210 --
4211 l_debug_on BOOLEAN;
4212 --
4213 BEGIN
4214
4215 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
4216 --
4217 IF l_debug_on IS NULL THEN
4218 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
4219 END IF;
4220
4221 IF l_debug_on THEN
4222 WSH_DEBUG_SV.push(l_module_name);
4223 WSH_DEBUG_SV.log(l_module_name,'Tp Plan Name:', p_tp_plan_name);
4224 WSH_DEBUG_SV.log(l_module_name,'Trip Interface Id:', p_trip_interface_id);
4225 WSH_DEBUG_SV.log(l_module_name,'p_commit_flag:', p_commit_flag);
4226 WSH_DEBUG_SV.log(l_module_name,'p_exception_action:', p_exception_action);
4227 END IF;
4228
4229 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
4230
4231 SAVEPOINT BEFORE_PURGE;
4232
4233 -- Derive the trip_interface_id
4234 IF p_trip_interface_id IS NULL THEN
4235 OPEN c_get_interface_id (p_tp_plan_name);
4236 FETCH c_get_interface_id BULK COLLECT
4237 INTO l_trip_interface_id_tab;
4238 CLOSE c_get_interface_id;
4239 ELSE
4240 l_trip_interface_id_tab(1) := p_trip_interface_id;
4241 END IF;
4242
4243 IF l_debug_on THEN
4244 WSH_DEBUG_SV.log(l_module_name,'Count of Trip Interface Ids:', l_trip_interface_id_tab.count);
4245 END IF;
4246
4247 IF l_debug_on THEN
4248 WSH_DEBUG_SV.logmsg(l_module_name,'Start Purging Freight Costs Interface Data');
4249 END IF;
4250
4251 IF l_trip_interface_id_tab.count > 0 THEN--{
4252
4253 IF l_debug_on THEN
4254 WSH_DEBUG_SV.log(l_module_name,'Trip Interface Id:', l_trip_interface_id_tab(l_trip_interface_id_tab.FIRST));
4255 END IF;
4256
4257 -- Purge wsh_freight_cost_interface
4258 FORALL i in l_trip_interface_id_tab.FIRST..l_trip_interface_id_tab.LAST
4259 Delete from wsh_freight_costs_interface wfci
4260 where wfci.delivery_interface_id in (
4261 select wdli.delivery_interface_id
4262 from wsh_trip_stops_interface wtsi,
4263 wsh_del_legs_interface wdli
4264 where wtsi.trip_interface_id = l_trip_interface_id_tab(i)
4265 and wtsi.stop_interface_id = wdli.pick_up_stop_interface_id
4266 and wtsi.interface_action_code = G_TMS_RELEASE_CODE
4267 and wdli.interface_action_code = G_TMS_RELEASE_CODE)
4268 and wfci.interface_action_code = G_TMS_RELEASE_CODE;
4269
4270
4271 IF p_exception_action = 'PURGE' THEN--{
4272 -- Purge exceptions logged for the deliveries
4273 IF l_debug_on THEN
4274 WSH_DEBUG_SV.logmsg(l_module_name,'Start Purging Exceptions');
4275 END IF;
4276 FORALL i in l_trip_interface_id_tab.FIRST..l_trip_interface_id_tab.LAST
4277 DELETE from WSH_EXCEPTIONS we
4278 WHERE we.delivery_id in (
4279 select wdli.delivery_id
4280 from wsh_trip_stops_interface wtsi,
4281 wsh_del_legs_interface wdli
4282 where wtsi.trip_interface_id = l_trip_interface_id_tab(i)
4283 and wtsi.stop_interface_id = wdli.pick_up_stop_interface_id
4284 and wtsi.interface_action_code = G_TMS_RELEASE_CODE
4285 and wdli.interface_action_code = G_TMS_RELEASE_CODE)
4286 and we.status = 'OPEN'
4287 and we.exception_name IN ('WSH_OTM_SHIPMENT_ERROR','WSH_OTM_INVALID_LOC');
4288
4289 ELSIF p_exception_action = 'CLOSE' THEN
4290 -- Close exceptions logged for the deliveries
4291 IF l_debug_on THEN
4292 WSH_DEBUG_SV.logmsg(l_module_name,'Start Closing Exceptions');
4293 END IF;
4294 FORALL i in l_trip_interface_id_tab.FIRST..l_trip_interface_id_tab.LAST
4295 UPDATE WSH_EXCEPTIONS we
4296 SET status = 'CLOSED'
4297 WHERE we.delivery_id in (
4298 select wdli.delivery_id
4299 from wsh_trip_stops_interface wtsi,
4300 wsh_del_legs_interface wdli
4301 where wtsi.trip_interface_id = l_trip_interface_id_tab(i)
4302 and wtsi.stop_interface_id = wdli.pick_up_stop_interface_id
4303 and wtsi.interface_action_code = G_TMS_RELEASE_CODE
4304 and wdli.interface_action_code = G_TMS_RELEASE_CODE)
4305 and we.exception_name IN ('WSH_OTM_SHIPMENT_ERROR','WSH_OTM_INVALID_LOC')
4306 and we.status = 'OPEN';
4307 END IF;--}
4308
4309 -- Purge wsh_interface_errors
4310 -- for each of the above entity, there could be errors logged in wsh_interface_errors
4311 -- table_name can be WSH_NEW_DEL_INTERFACE, WSH_TRIP_STOPS_INTERFACE,
4312 -- WSH_TRIPS_INTERFACE
4313
4314 IF l_debug_on THEN
4315 WSH_DEBUG_SV.logmsg(l_module_name,'Start Purging Interface Errors Data for delivery');
4316 END IF;
4317 -- Delete errors logged for deliveries
4318 FORALL i in l_trip_interface_id_tab.FIRST..l_trip_interface_id_tab.LAST
4319 Delete from wsh_interface_errors wie
4320 where wie.interface_table_name = 'WSH_NEW_DEL_INTERFACE'
4321 and wie.interface_id in (
4322 select wdli.delivery_interface_id
4323 from wsh_trip_stops_interface wtsi,
4324 wsh_del_legs_interface wdli
4325 where wtsi.trip_interface_id = l_trip_interface_id_tab(i)
4326 and wtsi.stop_interface_id = wdli.pick_up_stop_interface_id
4327 and wtsi.interface_action_code = G_TMS_RELEASE_CODE
4328 and wdli.interface_action_code = G_TMS_RELEASE_CODE)
4329 and wie.interface_action_code = G_TMS_RELEASE_CODE;
4330
4331 IF l_debug_on THEN
4332 WSH_DEBUG_SV.logmsg(l_module_name,'Start Purging Interface Errors Data for Trip Stops');
4333 END IF;
4334 -- Delete errors logged for trip stops
4335 FORALL i in l_trip_interface_id_tab.FIRST..l_trip_interface_id_tab.LAST
4336 Delete from wsh_interface_errors wie
4337 where wie.interface_table_name = 'WSH_TRIP_STOPS_INTERFACE'
4338 and wie.interface_id in (
4339 select wtsi.stop_interface_id
4340 from wsh_trip_stops_interface wtsi
4341 where wtsi.trip_interface_id = l_trip_interface_id_tab(i)
4342 and wtsi.interface_action_code = G_TMS_RELEASE_CODE)
4343 and wie.interface_action_code = G_TMS_RELEASE_CODE;
4344
4345 -- Delete errors logged for trips
4346 IF l_debug_on THEN
4347 WSH_DEBUG_SV.logmsg(l_module_name,'Start Purging Interface Errors Data for Trips');
4348 END IF;
4349 FORALL i in l_trip_interface_id_tab.FIRST..l_trip_interface_id_tab.LAST
4350 Delete from wsh_interface_errors wie
4351 where wie.interface_table_name = 'WSH_TRIPS_INTERFACE'
4352 and wie.interface_id = l_trip_interface_id_tab(i)
4353 and wie.interface_action_code = G_TMS_RELEASE_CODE;
4354
4355 -- Purge wsh_new_del_interface
4356 IF l_debug_on THEN
4357 WSH_DEBUG_SV.logmsg(l_module_name,'Start Purging Deliveries Interface Data');
4358 END IF;
4359 FORALL i in l_trip_interface_id_tab.FIRST..l_trip_interface_id_tab.LAST
4360 Delete from wsh_new_del_interface wndi
4361 where wndi.delivery_interface_id in (
4362 select wdli.delivery_interface_id
4363 from wsh_trip_stops_interface wtsi,
4364 wsh_del_legs_interface wdli
4365 where wtsi.trip_interface_id = l_trip_interface_id_tab(i)
4366 and wtsi.stop_interface_id = wdli.pick_up_stop_interface_id
4367 and wtsi.interface_action_code = G_TMS_RELEASE_CODE
4368 and wdli.interface_action_code = G_TMS_RELEASE_CODE)
4369 and wndi.interface_action_code = G_TMS_RELEASE_CODE;
4370
4371 -- Purge wsh_del_legs_interface
4372 IF l_debug_on THEN
4373 WSH_DEBUG_SV.logmsg(l_module_name,'Start Purging Delivery Legs Interface Data');
4374 END IF;
4375 FORALL i in l_trip_interface_id_tab.FIRST..l_trip_interface_id_tab.LAST
4376 Delete from wsh_del_legs_interface wdli
4377 where wdli.pick_up_stop_interface_id in (
4378 select wtsi.stop_interface_id
4379 from wsh_trip_stops_interface wtsi
4380 where wtsi.trip_interface_id = l_trip_interface_id_tab(i)
4381 and wtsi.interface_action_code = G_TMS_RELEASE_CODE)
4382 and wdli.interface_action_code = G_TMS_RELEASE_CODE;
4383
4384 -- Purge wsh_trip_stops_interface
4385 IF l_debug_on THEN
4386 WSH_DEBUG_SV.logmsg(l_module_name,'Start Purging Trip Stops Interface Data');
4387 END IF;
4388 FORALL i in l_trip_interface_id_tab.FIRST..l_trip_interface_id_tab.LAST
4389 Delete from wsh_trip_stops_interface wtsi
4390 where wtsi.trip_interface_id = l_trip_interface_id_tab(i)
4391 and wtsi.interface_action_code = G_TMS_RELEASE_CODE;
4392
4393 -- Purge wsh_trips_interface
4394 -- (Trips have to be the last entity interface table purged
4395 -- because this is the only entity interface table having TP_PLAN_NAME.)
4396 IF l_debug_on THEN
4397 WSH_DEBUG_SV.logmsg(l_module_name,'Start Purging Trips Interface Data');
4398 END IF;
4399 FORALL i in l_trip_interface_id_tab.FIRST..l_trip_interface_id_tab.LAST
4400 Delete from wsh_trips_interface wti
4401 where wti.trip_interface_id = l_trip_interface_id_tab(i)
4402 and wti.INTERFACE_ACTION_CODE = G_TMS_RELEASE_CODE;
4403
4404 END IF;--}
4405
4406 IF p_commit_flag = 'Y' THEN
4407 IF l_debug_on THEN
4408 WSH_DEBUG_SV.logmsg(l_module_name,'Committing....');
4409 END IF;
4410 commit;
4411 END IF;
4412
4413 IF l_debug_on THEN
4414 WSH_DEBUG_SV.log(l_module_name, 'x_return_status', x_return_status);
4415 WSH_DEBUG_SV.pop(l_module_name);
4416 END IF;
4417
4418 EXCEPTION
4419 WHEN OTHERS THEN
4420 IF c_get_interface_id%ISOPEN THEN
4421 CLOSE c_get_interface_id;
4422 END IF;
4423 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
4424 ROLLBACK to before_purge;
4425 WSH_UTIL_CORE.DEFAULT_HANDLER(
4426 'WSH_TMS_RELEASE.PURGE_INTERFACE_DATA',
4427 l_module_name);
4428 IF l_debug_on THEN
4429 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
4430 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
4431 END IF;
4432
4433 END purge_interface_data;
4434
4435 --====================================================
4436 --
4437 -- Procedure: stamp_interface_error
4438 -- Parameters:
4439 -- p_group_id group identifier where the error is found
4440 -- p_entity_table_name entity table where the error is found
4441 -- p_entity_interface_id record where the error is found
4442 -- p_message_name message name identifying the error
4443 -- p_message_appl message application name (NULL means 'WSH')
4444 -- p_message_text optional text for output to the user-
4445 -- p_token_1_name optional token 1 name
4446 -- p_token_1_value optional token 1 value
4447 -- p_token_2_name optional token 2 name
4448 -- p_token_2_value optional token 2 value
4449 -- p_token_3_name optional token 3 name
4450 -- p_token_3_value optional token 3 value
4451 -- p_token_4_name optional token 4 name
4452 -- p_token_4_value optional token 4 value
4453 -- p_dleg_tab Table of Delivery Leg information
4454 -- x_errors_tab list of errors to insert into wsh_interface_errors at the end
4455 -- x_return_status return status
4456 --
4457 -- Description:
4458 -- Inserts the error information for each delivery
4459 --
4460 --
4461 --====================================================
4462 PROCEDURE stamp_interface_error(
4463 p_group_id IN NUMBER,
4464 p_entity_table_name IN VARCHAR2,
4465 p_entity_interface_id IN NUMBER,
4466 p_message_name IN VARCHAR2,
4467 p_message_appl IN VARCHAR2 DEFAULT NULL,
4468 p_message_text IN VARCHAR2 DEFAULT NULL,
4469 p_token_1_name IN VARCHAR2 DEFAULT NULL,
4470 p_token_1_value IN VARCHAR2 DEFAULT NULL,
4471 p_token_2_name IN VARCHAR2 DEFAULT NULL,
4472 p_token_2_value IN VARCHAR2 DEFAULT NULL,
4473 p_token_3_name IN VARCHAR2 DEFAULT NULL,
4474 p_token_3_value IN VARCHAR2 DEFAULT NULL,
4475 p_token_4_name IN VARCHAR2 DEFAULT NULL,
4476 p_token_4_value IN VARCHAR2 DEFAULT NULL,
4477 p_dleg_tab IN TMS_DLEG_TAB_TYPE,
4478 x_errors_tab IN OUT NOCOPY INTERFACE_ERRORS_TAB_TYPE,
4479 x_return_status OUT NOCOPY VARCHAR2) IS
4480
4481 TYPE text_tab_type IS TABLE OF WSH_INTERFACE_ERRORS.ERROR_MESSAGE%TYPE INDEX BY BINARY_INTEGER;
4482 --
4483 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'STAMP_INTERFACE_ERROR';
4484 --
4485 l_debug_on BOOLEAN;
4486 --
4487 l_index BINARY_INTEGER := NULL;
4488 l_message_appl VARCHAR2(30) := p_message_appl;
4489 c NUMBER;
4490 l_buffer VARCHAR2(4000);
4491 l_index_out NUMBER;
4492
4493 --
4494 l_groups WSH_UTIL_CORE.ID_TAB_TYPE;
4495 l_table_names WSH_UTIL_CORE.COLUMN_TAB_TYPE;
4496 l_interface_ids WSH_UTIL_CORE.ID_TAB_TYPE;
4497 l_message_names WSH_UTIL_CORE.COLUMN_TAB_TYPE;
4498 l_messages text_tab_type;
4499
4500 l_message VARCHAR2(4000);
4501 l_message_name VARCHAR2(30);
4502 l_dleg_tab tms_dleg_tab_type;
4503 l_dlvy_id_tab WSH_UTIL_CORE.id_tab_type;
4504
4505 l_msg_data VARCHAR2(240);
4506
4507 BEGIN
4508
4509 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
4510 --
4511 IF l_debug_on IS NULL THEN
4512 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
4513 END IF;
4514
4515 IF l_debug_on THEN
4516 WSH_DEBUG_SV.push(l_module_name);
4517 WSH_DEBUG_SV.log(l_module_name,'p_group_id', p_group_id);
4518 WSH_DEBUG_SV.log(l_module_name,'p_entity_table_name', p_entity_table_name);
4519 WSH_DEBUG_SV.log(l_module_name,'p_entity_interface_id', p_entity_interface_id);
4520 WSH_DEBUG_SV.log(l_module_name,'p_message_appl', p_message_appl);
4521 WSH_DEBUG_SV.log(l_module_name,'p_message_name', p_message_name);
4522 WSH_DEBUG_SV.log(l_module_name,'p_message_text', p_message_text);
4523 WSH_DEBUG_SV.log(l_module_name,'p_token_1_name', p_token_1_name);
4524 WSH_DEBUG_SV.log(l_module_name,'p_token_1_value', p_token_1_value);
4525 WSH_DEBUG_SV.log(l_module_name,'p_token_2_name', p_token_2_name);
4526 WSH_DEBUG_SV.log(l_module_name,'p_token_2_value', p_token_2_value);
4527 WSH_DEBUG_SV.log(l_module_name,'p_token_3_name', p_token_3_name);
4528 WSH_DEBUG_SV.log(l_module_name,'p_token_3_value', p_token_3_value);
4529 WSH_DEBUG_SV.log(l_module_name,'p_token_4_name', p_token_4_name);
4530 WSH_DEBUG_SV.log(l_module_name,'p_token_4_value', p_token_4_value);
4531 WSH_DEBUG_SV.logmsg(l_module_name,'Count of Input Deliveries:'||p_dleg_tab.count);
4532 END IF;
4533
4534 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
4535
4536 l_index := x_errors_tab.COUNT + 1;
4537
4538 IF p_message_text IS NULL THEN --{
4539 l_message_appl := NVL(l_message_appl, 'WSH');
4540
4541 FND_MESSAGE.SET_NAME(l_message_appl, 'WSH_OTM_GENERIC');
4542 -- remove the above, when message is seeded
4543 --FND_MESSAGE.SET_NAME(l_message_appl, p_message_name);
4544 -- OTM R12 the tokens are not used at all
4545 /*
4546 IF p_token_1_name IS NOT NULL THEN
4547 FND_MESSAGE.SET_TOKEN(p_token_1_name, p_token_1_value);
4548 IF p_token_2_name IS NOT NULL THEN
4549 FND_MESSAGE.SET_TOKEN(p_token_2_name, p_token_2_value);
4550 IF p_token_3_name IS NOT NULL THEN
4551 FND_MESSAGE.SET_TOKEN(p_token_3_name, p_token_3_value);
4552 IF p_token_4_name IS NOT NULL THEN
4553 FND_MESSAGE.SET_TOKEN(p_token_4_name, p_token_4_value);
4554 END IF;
4555 END IF;
4556 END IF;
4557 END IF;
4558 */
4559 -- OTM R12
4560
4561 fnd_msg_pub.add;
4562
4563 ELSE
4564 x_errors_tab(l_index).ERROR_MESSAGE := p_message_text;
4565 x_errors_tab(l_index).INTERFACE_TABLE_NAME := p_entity_table_name;
4566 x_errors_tab(l_index).INTERFACE_ID := p_entity_interface_id;
4567 x_errors_tab(l_index).INTERFACE_ERROR_GROUP_ID := p_group_id;
4568 x_errors_tab(l_index).MESSAGE_NAME := p_message_name;
4569
4570 l_index := l_index +1;
4571 END IF; --}
4572
4573 c := FND_MSG_PUB.count_msg;
4574 IF l_debug_on THEN
4575 WSH_DEBUG_SV.log(l_module_name,'COUNT--',c);
4576 END IF;
4577
4578 FOR i in 1..c LOOP--{
4579 --FOR i in REVERSE(c)..1 LOOP--{
4580
4581 IF l_debug_on THEN
4582 WSH_DEBUG_SV.logmsg(l_module_name,'C:'||i);
4583 END IF;
4584
4585 FND_MSG_PUB.get(p_encoded => FND_API.G_FALSE,
4586 p_msg_index => i,
4587 p_data => l_buffer,
4588 p_msg_index_out => l_index_out);
4589
4590 -- Concatenate the message
4591 IF l_debug_on THEN
4592 WSH_DEBUG_SV.logmsg(l_module_name,'l_buffer:'||l_buffer);
4593 END IF;
4594 l_message := l_message||l_buffer;
4595
4596 -- l_index := l_index + 1;
4597 END LOOP;--}
4598
4599 FND_MSG_PUB.initialize;
4600
4601 l_message_name := 'WSH_OTM_GENERIC';
4602
4603 IF l_debug_on THEN
4604 WSH_DEBUG_SV.log(l_module_name,'l_index--',l_index);
4605 WSH_DEBUG_SV.logmsg(l_module_name,'l_message:'||substr(l_message,1,200));
4606 WSH_DEBUG_SV.logmsg(l_module_name,'Continued l_message:'||substr(l_message,201,200));
4607 WSH_DEBUG_SV.logmsg(l_module_name,'Continued l_message:'||substr(l_message,401,200));
4608 END IF;
4609
4610
4611 -- Populate the Delivery_interface_id
4612 IF p_dleg_tab.count > 0 THEN--{
4613 FOR i in p_dleg_tab.FIRST..p_dleg_tab.LAST
4614 LOOP
4615 l_dlvy_id_tab(l_dlvy_id_tab.count + 1) := p_dleg_tab(i).delivery_interface_id;
4616 END LOOP;
4617
4618 IF l_dlvy_id_tab.count > 0 THEN
4619 FORALL i IN p_dleg_tab.FIRST .. p_dleg_tab.LAST
4620 INSERT INTO WSH_INTERFACE_ERRORS (
4621 INTERFACE_ERROR_ID,
4622 INTERFACE_ERROR_GROUP_ID,
4623 INTERFACE_TABLE_NAME,
4624 INTERFACE_ID,
4625 INTERFACE_ACTION_CODE,
4626 MESSAGE_NAME,
4627 ERROR_MESSAGE,
4628 CREATION_DATE,
4629 CREATED_BY,
4630 LAST_UPDATE_DATE,
4631 LAST_UPDATED_BY)
4632 VALUES (
4633 WSH_INTERFACE_ERRORS_S.nextval,
4634 p_group_id, --l_groups(i),
4635 'WSH_NEW_DEL_INTERFACE', --l_table_names(i),
4636 l_dlvy_id_tab(i),--l_interface_ids(i),
4637 G_TMS_RELEASE_CODE,
4638 l_message_name,
4639 l_message,
4640 SYSDATE,
4641 FND_GLOBAL.USER_ID,
4642 SYSDATE,
4643 FND_GLOBAL.USER_ID);
4644 END IF;
4645
4646 END IF;--}
4647
4648 IF l_debug_on THEN
4649 WSH_DEBUG_SV.log(l_module_name, 'x_return_status', x_return_status);
4650 WSH_DEBUG_SV.pop(l_module_name);
4651 END IF;
4652
4653 EXCEPTION
4654 WHEN OTHERS THEN
4655 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
4656 WSH_UTIL_CORE.DEFAULT_HANDLER(
4657 'WSH_TMS_RELEASE.STAMP_INTERFACE_ERROR',
4658 l_module_name);
4659 IF l_debug_on THEN
4660 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
4661 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
4662 END IF;
4663 END stamp_interface_error;
4664
4665 --====================================================
4666 -- Procedure: find_deliveries_for_trip
4667 -- Description: This Procedure returns a table of delivery ids
4668 -- and a table of records(delivery_interface_id, revision)
4669 -- based on input of either tp_plan_name or trip_id.
4670 -- Usage : Inbound BPEL process will also call this API before
4671 -- populating the Interface tables.
4672 --====================================================
4673 PROCEDURE find_deliveries_for_trip(
4674 p_trip_id IN NUMBER,
4675 p_tp_plan_name IN VARCHAR2,
4676 x_delivery_tab OUT NOCOPY WSH_TMS_RELEASE.delivery_tab,
4677 x_delivery_id_tab OUT NOCOPY WSH_UTIL_CORE.id_tab_type,
4678 x_return_status OUT NOCOPY VARCHAR2) IS
4679
4680 -- Query deliveries based on p_trip_id
4681 CURSOR c_get_deliveries1 IS
4682 SELECT wdl.delivery_id,
4683 wnd.tms_version_number,
4684 wnd.organization_id,
4685 wnd.name,
4686 wnd.status_code
4687 FROM wsh_delivery_legs wdl,
4688 wsh_trip_stops wts,
4689 wsh_new_deliveries wnd
4690 WHERE wdl.pick_up_stop_id = wts.stop_id
4691 AND wts.trip_id = p_trip_id
4692 AND wdl.delivery_id = wnd.delivery_id(+);
4693
4694 -- Query deliveries based on tp_plan_name
4695 CURSOR c_get_deliveries2 IS
4696 SELECT wdl.delivery_id,
4697 wnd.tms_version_number,
4698 wnd.organization_id,
4699 wnd.name,
4700 wnd.status_code
4701 FROM wsh_delivery_legs wdl,
4702 wsh_trip_stops wts,
4703 wsh_trips wt,
4704 wsh_new_deliveries wnd
4705 WHERE wdl.pick_up_stop_id = wts.stop_id
4706 AND wts.trip_id = wt.trip_id
4707 AND wt.tp_plan_name = p_tp_plan_name
4708 AND wdl.delivery_id = wnd.delivery_id(+);
4709
4710 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'FIND_DELIVERIES_FOR_TRIP';
4711 --
4712 l_debug_on BOOLEAN;
4713 --
4714 --l_index NUMBER := 0;
4715 l_index VARCHAR2(38) := NULL;
4716
4717 BEGIN
4718
4719 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
4720 --
4721 IF l_debug_on IS NULL THEN
4722 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
4723 END IF;
4724
4725 IF l_debug_on THEN
4726 WSH_DEBUG_SV.push(l_module_name);
4727 WSH_DEBUG_SV.log(l_module_name,'Tp Plan Name:', p_tp_plan_name);
4728 WSH_DEBUG_SV.log(l_module_name,'Trip Id:', p_trip_id);
4729 END IF;
4730
4731 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
4732
4733 IF p_trip_id IS NOT NULL THEN--{
4734 FOR rec in c_get_deliveries1
4735 LOOP
4736 l_index := rec.delivery_id;
4737 x_delivery_tab(l_index).tms_version_number := rec.tms_version_number;
4738 x_delivery_tab(l_index).organization_id := rec.organization_id;
4739 x_delivery_tab(l_index).name := rec.name;
4740 x_delivery_tab(l_index).status_code := rec.status_code;
4741 -- leave delivery_interface_id as null in x_delivery_tab
4742 x_delivery_id_tab(x_delivery_id_tab.count + 1) := rec.delivery_id;
4743 END LOOP;
4744 ELSIF p_tp_plan_name IS NOT NULL THEN
4745 FOR rec in c_get_deliveries2
4746 LOOP
4747 l_index := rec.delivery_id;
4748 x_delivery_tab(l_index).tms_version_number := rec.tms_version_number;
4749 x_delivery_tab(l_index).organization_id := rec.organization_id;
4750 x_delivery_tab(l_index).name := rec.name;
4751 x_delivery_tab(l_index).status_code := rec.status_code;
4752 -- leave delivery_interface_id as null
4753 x_delivery_id_tab(x_delivery_id_tab.count + 1) := rec.delivery_id;
4754 END LOOP;
4755 END IF;--}
4756
4757 IF l_debug_on THEN
4758 WSH_DEBUG_SV.log(l_module_name, 'x_delivery_id_tab.count', x_delivery_id_tab.count);
4759 WSH_DEBUG_SV.log(l_module_name, 'x_delivery_tab.count', x_delivery_tab.count);
4760 WSH_DEBUG_SV.log(l_module_name, 'x_return_status', x_return_status);
4761 WSH_DEBUG_SV.pop(l_module_name);
4762 END IF;
4763
4764 EXCEPTION
4765 WHEN OTHERS THEN
4766 IF c_get_deliveries1 %ISOPEN THEN
4767 CLOSE c_get_deliveries1;
4768 END IF;
4769 IF c_get_deliveries2 %ISOPEN THEN
4770 CLOSE c_get_deliveries2;
4771 END IF;
4772 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
4773 WSH_UTIL_CORE.DEFAULT_HANDLER(
4774 'WSH_TMS_RELEASE.FIND_DELIVERIES_FOR_TRIP',
4775 l_module_name);
4776 IF l_debug_on THEN
4777 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
4778 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
4779 END IF;
4780 END find_deliveries_for_trip;
4781
4782 --====================================================
4783 -- Procedure: compare_trip_for_deliveries
4784 -- Description: This Procedure returns a table of delivery ids
4785 -- which have been unassigned from their current trips
4786 -- (as they have to be placed on GC3 trip)
4787 -- GC3 trip may or may not have been created in EBS, so
4788 -- p_trip_id is optional field
4789 -- Also appends to existing list of deliveries to be
4790 -- unassigned and their version numbers
4791 -- Usage : Internal
4792 --====================================================
4793 PROCEDURE compare_trip_for_deliveries
4794 (p_dleg_tab IN OUT NOCOPY TMS_DLEG_TAB_TYPE,
4795 p_trip_id IN NUMBER,
4796 x_unassign_id_tab IN OUT NOCOPY WSH_UTIL_CORE.id_tab_type,
4797 --x_unassign_ver_tab IN OUT NOCOPY WSH_UTIL_CORE.id_tab_type,
4798 x_return_status OUT NOCOPY VARCHAR2) IS
4799
4800 CURSOR c_get_current_trip(p_delivery_id IN NUMBER) IS
4801 SELECT wdl.delivery_leg_id,
4802 wdl.pick_up_stop_id pick_up_stop_id,
4803 wdl.drop_off_stop_id drop_off_stop_id,
4804 wts_pu.stop_location_id pickup_stop_location_id,
4805 wts_pu.stop_sequence_number pickup_stop_sequence,
4806 wts_do.stop_location_id dropoff_stop_location_id,
4807 wts_do.stop_sequence_number dropoff_stop_sequence,
4808 wts_pu.trip_id trip_id
4809 FROM wsh_delivery_legs wdl,
4810 wsh_trip_stops wts_pu,
4811 wsh_trip_stops wts_do
4812 WHERE wdl.delivery_id = p_delivery_id
4813 AND wdl.pick_up_stop_id = wts_pu.stop_id
4814 AND wdl.drop_off_stop_id = wts_do.stop_id ;
4815
4816 l_current_trip_id NUMBER;
4817
4818 l_del_attrs WSH_NEW_DELIVERIES_PVT.delivery_attr_tbl_type;
4819 l_del_action_prms WSH_DELIVERIES_GRP.action_parameters_rectype;
4820 l_del_action_rec WSH_DELIVERIES_GRP.delivery_action_out_rec_type;
4821 l_del_defaults WSH_DELIVERIES_GRP.default_parameters_rectype;
4822
4823 l_rs VARCHAR2(1);
4824 l_msg_count NUMBER;
4825 l_msg_data VARCHAR2(32767);
4826
4827 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'COMPARE_TRIP_FOR_DELIVERIES';
4828 --
4829 l_debug_on BOOLEAN;
4830 --
4831
4832 BEGIN
4833
4834 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
4835 --
4836 IF l_debug_on IS NULL THEN
4837 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
4838 END IF;
4839
4840 IF l_debug_on THEN
4841 WSH_DEBUG_SV.push(l_module_name);
4842 WSH_DEBUG_SV.log(l_module_name,'Input Delivery Count:', p_dleg_tab.count);
4843 WSH_DEBUG_SV.log(l_module_name,'Trip Id:', p_trip_id);
4844 WSH_DEBUG_SV.log(l_module_name,'Unassign Delivery Count:', x_unassign_id_tab.count);
4845 -- WSH_DEBUG_SV.log(l_module_name,'Unassign Delivery Version Count:', x_unassign_ver_tab.count);
4846 END IF;
4847
4848 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
4849
4850 IF p_dleg_tab.count > 0 THEN
4851
4852 FOR dlvy_count IN p_dleg_tab.FIRST..p_dleg_tab.LAST
4853 LOOP--{
4854 -- Loop as there can be multiple trips for a delivery!!!
4855 FOR dleg_rec IN c_get_current_trip(p_dleg_tab(dlvy_count).delivery_id)
4856 LOOP--{
4857
4858 l_del_attrs.DELETE;
4859 l_current_trip_id := dleg_rec.trip_id;
4860
4861 -- All assignments happen together, later
4862 IF (
4863 -- If trips are different, then need to unassign the delivery from trip
4864 (l_current_trip_id IS NOT NULL AND l_current_trip_id <> nvl(p_trip_id,-99))
4865 OR
4866 (
4867 -- Need to check if the pickup and dropoff stops are identical or different
4868 -- If different, then need to unassign the delivery from trip
4869 (l_current_trip_id IS NOT NULL AND p_trip_id IS NOT NULL AND l_current_trip_id = p_trip_id)
4870 AND
4871 (dleg_rec.pickup_stop_location_id <> to_number(p_dleg_tab(dlvy_count).pickup_stop_location_id) OR
4872 dleg_rec.pickup_stop_sequence <> p_dleg_tab(dlvy_count).pickup_stop_sequence OR
4873 dleg_rec.dropoff_stop_location_id <> to_number(p_dleg_tab(dlvy_count).dropoff_stop_location_id) OR
4874 dleg_rec.dropoff_stop_sequence <> p_dleg_tab(dlvy_count).dropoff_stop_sequence)
4875 )
4876 ) THEN--{
4877
4878
4879 IF l_debug_on THEN
4880 WSH_DEBUG_SV.logmsg(l_module_name, 'Need to UNASSIGN DELIVERY');
4881 END IF;
4882
4883 -- Cannot do BULK UNASSIGNMENT HERE, as there can be multiple trips and the
4884 -- deliveries could be associated with different trips
4885 -- Delivery_action API gives an option to specify multiple deliveries
4886 -- but single trip.
4887 -- This unassign is for single delivery from single trip
4888 l_del_attrs(l_del_attrs.count + 1).delivery_id := p_dleg_tab(dlvy_count).delivery_id;
4889 l_del_attrs(l_del_attrs.count).organization_id := p_dleg_tab(dlvy_count).organization_id;
4890
4891 IF l_debug_on THEN
4892 WSH_DEBUG_SV.logmsg(l_module_name,'Deliveries to be unassigned:'||l_del_attrs.count);
4893 END IF;
4894
4895 l_del_action_prms.caller := 'FTE_TMS_INTEGRATION'; --'FTE_TMS_RELEASE';
4896 l_del_action_prms.action_code := 'UNASSIGN-TRIP';
4897 l_del_action_prms.trip_id := l_current_trip_id;
4898
4899 WSH_DELIVERIES_GRP.delivery_action(
4900 p_api_version_number => 1.0,
4901 p_init_msg_list => FND_API.G_TRUE,
4902 p_commit => FND_API.G_FALSE,
4903 p_action_prms => l_del_action_prms,
4904 p_rec_attr_tab => l_del_attrs,
4905 x_delivery_out_rec => l_del_action_rec,
4906 x_defaults_rec => l_del_defaults,
4907 x_return_status => l_rs,
4908 x_msg_count => l_msg_count,
4909 x_msg_data => l_msg_data);
4910
4911 IF l_debug_on THEN
4912 WSH_DEBUG_SV.logmsg(l_module_name,'Return Status after Unassign Delivery'||l_rs);
4913 END IF;
4914 IF l_rs IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
4915 x_return_status := l_rs;
4916 EXIT;
4917 END IF;
4918 -- THESE WILL BE ASSIGNED LATER, SO WHY POPULATE IN UNASSIGNED TAB AND THEN DELETE LATER!!!!!
4919 -- CHECK THIS !!!!
4920 ELSIF (
4921 -- Need to check if the pickup and dropoff stops are identical or different
4922 -- If identical, then NO need to unassign the delivery from trip
4923 -- skip this delivery while processing the deliveries
4924 (l_current_trip_id IS NOT NULL AND p_trip_id IS NOT NULL AND l_current_trip_id = p_trip_id)
4925 AND
4926 (dleg_rec.pickup_stop_location_id = to_number(p_dleg_tab(dlvy_count).pickup_stop_location_id) AND
4927 dleg_rec.pickup_stop_sequence = p_dleg_tab(dlvy_count).pickup_stop_sequence AND
4928 dleg_rec.dropoff_stop_location_id = to_number(p_dleg_tab(dlvy_count).dropoff_stop_location_id) AND
4929 dleg_rec.dropoff_stop_sequence = p_dleg_tab(dlvy_count).dropoff_stop_sequence)
4930 ) THEN
4931
4932 IF l_debug_on THEN
4933 WSH_DEBUG_SV.logmsg(l_module_name, 'DELIVERY IS ALREADY ASSIGNED TO TRIP');
4934 END IF;
4935 p_dleg_tab(dlvy_count).processed_flag := 'Y';
4936 END IF;--}
4937 END LOOP;--}
4938
4939 IF l_rs IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
4940 x_return_status := l_rs;
4941 IF l_debug_on THEN
4942 WSH_DEBUG_SV.logmsg(l_module_name, 'Error Processing Delivery id:'||(p_dleg_tab(dlvy_count).delivery_id));
4943 END IF;
4944 EXIT;
4945 END IF;
4946 END LOOP;--}
4947 END IF;
4948
4949 IF l_debug_on THEN
4950 WSH_DEBUG_SV.log(l_module_name, 'Unassign Delivery Count:', x_unassign_id_tab.count);
4951 -- WSH_DEBUG_SV.log(l_module_name,'Unassign Delivery Version Count:', x_unassign_ver_tab.count);
4952 WSH_DEBUG_SV.log(l_module_name, 'x_return_status', x_return_status);
4953 WSH_DEBUG_SV.pop(l_module_name);
4954 END IF;
4955
4956 EXCEPTION
4957 WHEN OTHERS THEN
4958 IF c_get_current_trip %ISOPEN THEN
4959 CLOSE c_get_current_trip;
4960 END IF;
4961 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
4962 WSH_UTIL_CORE.DEFAULT_HANDLER(
4963 'WSH_TMS_RELEASE.COMPARE_TRIP_FOR_DELIVERIES',
4964 l_module_name);
4965 IF l_debug_on THEN
4966 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
4967 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
4968 END IF;
4969
4970 END compare_trip_for_deliveries;
4971
4972 --
4973 -- Procedure : launch_release_request
4974 -- Description: Submit a concurrent request
4975 -- Parameters :
4976 -- Input
4977 -- p_group_id Input group id
4978 -- Output
4979 -- x_request_id Request id of the concurrent program
4980 -- x_return_status Return Status
4981 --
4982 PROCEDURE launch_release_request
4983 (p_group_id IN NUMBER,
4984 x_request_id OUT NOCOPY NUMBER,
4985 x_return_status OUT NOCOPY VARCHAR2) IS
4986
4987 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'LAUNCH_RELEASE_REQUEST';
4988 --
4989 l_debug_on BOOLEAN;
4990 --
4991
4992 BEGIN
4993
4994 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
4995 --
4996 IF l_debug_on IS NULL THEN
4997 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
4998 END IF;
4999
5000 IF l_debug_on THEN
5001 WSH_DEBUG_SV.push(l_module_name);
5002 WSH_DEBUG_SV.log(l_module_name,'Group Id:', p_group_id);
5003 END IF;
5004
5005 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
5006
5007 x_request_id := FND_REQUEST.Submit_Request
5008 (application => 'WSH',
5009 program => 'WSHOTMRL',
5010 argument1 => p_group_id);
5011 -- COMMIT;
5012
5013 IF l_debug_on THEN
5014 WSH_DEBUG_SV.log(l_module_name, 'Request id:', x_request_id);
5015 WSH_DEBUG_SV.log(l_module_name, 'x_return_status', x_return_status);
5016 WSH_DEBUG_SV.pop(l_module_name);
5017 END IF;
5018
5019 EXCEPTION
5020 WHEN OTHERS THEN
5021 x_request_id := 0;
5022 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
5023 WSH_UTIL_CORE.DEFAULT_HANDLER(
5024 'WSH_TMS_RELEASE.LAUNCH_RELEASE_REQUEST',
5025 l_module_name);
5026 IF l_debug_on THEN
5027 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
5028 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
5029 END IF;
5030
5031 END launch_release_request;
5032
5033 --===================================================================
5034 -- Procedure : Process Internal Locations
5035 -- Description: Process Internal Locations before Create or Update
5036 -- of Stops in EBS based on the information sent from
5037 -- GC3.
5038 -- Refer to ECO 5033116
5039 -- Parameters :
5040 -- In/Out
5041 -- x_delivery_tab : Delivery Information received from GC3
5042 -- x_stop_tab : Stop Information received from GC3
5043 -- Output
5044 -- x_return_status: Return Status
5045 --
5046 -- Possible Scenarios
5047 -- Single Delivery
5048 -- Pickup Dropoff
5049 -- Delivery1 : M1 M2'
5050 --
5051 -- Multiple Delivery
5052 -- Pickup Dropoff
5053 -- Delivery1 : M1 M2'
5054 -- Delivery2 : M2 XYZ
5055 -- For OUTBOUND only scenarios
5056 -- Dummy location will never be pickup stop location and
5057 -- Physical location cannot be the dropoff stop location
5058 --
5059 --===================================================================
5060 PROCEDURE process_internal_locations
5061 (x_delivery_tab IN OUT NOCOPY TMS_DLEG_TAB_TYPE,
5062 x_stop_tab IN OUT NOCOPY TMS_STOP_TAB_TYPE,
5063 x_return_status OUT NOCOPY VARCHAR2) IS
5064
5065
5066 l_stop_tab TMS_STOP_TAB_TYPE;
5067 l_new_stop_tab TMS_STOP_TAB_TYPE;
5068 l_dlvy_tab TMS_DLEG_TAB_TYPE;
5069 l_return_status VARCHAR2(1);
5070 l_physical_loc_id NUMBER;
5071 l_dropoff_location_id NUMBER;
5072 l_update_stop VARCHAR2(1);
5073 l_index NUMBER;
5074
5075 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'PROCESS_INTERNAL_LOCATIONS';
5076 --
5077 l_debug_on BOOLEAN;
5078 --
5079
5080 BEGIN
5081
5082 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
5083 --
5084 IF l_debug_on IS NULL THEN
5085 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
5086 END IF;
5087
5088 IF l_debug_on THEN
5089 WSH_DEBUG_SV.push(l_module_name);
5090 WSH_DEBUG_SV.log(l_module_name,'Delivery Tab Count:', x_delivery_tab.count);
5091 WSH_DEBUG_SV.log(l_module_name,'Stop Tab Count:', x_stop_tab.count);
5092 END IF;
5093
5094 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
5095
5096 l_stop_tab := x_stop_tab;
5097 l_dlvy_tab := x_delivery_tab;
5098
5099 FOR i IN l_stop_tab.FIRST..l_stop_tab.LAST
5100 LOOP--{
5101
5102 IF l_debug_on THEN
5103 WSH_DEBUG_SV.logmsg(l_module_name, '=====================================');
5104 WSH_DEBUG_SV.logmsg(l_module_name, 'Stop Location:'||l_stop_tab(i).stop_location_id);
5105 END IF;
5106
5107 IF x_delivery_tab.count = 1 THEN -- Single Delivery in the GC3 Trip--{
5108 -- Match Stop Location to Delivery Dropoff Location
5109 l_dropoff_location_id := to_number(x_delivery_tab(x_delivery_tab.FIRST).ultimate_dropoff_location_id);
5110 IF l_dropoff_location_id = to_number(l_stop_tab(i).stop_location_id) THEN--{
5111 -- no action required as Dropoff Location matches incoming stop
5112 --null;
5113 IF l_debug_on THEN
5114 WSH_DEBUG_SV.logmsg(l_module_name, 'Dropoff Location Matches Stop Location');
5115 END IF;
5116 ELSIF l_dropoff_location_id <> to_number(l_stop_tab(i).stop_location_id) THEN--} --{
5117
5118 IF l_debug_on THEN
5119 WSH_DEBUG_SV.logmsg(l_module_name, 'Dropoff Location Does not match Stop Location');
5120 END IF;
5121
5122 -- get the physical location corresponding to delivery
5123 -- dropoff location and match with Stop Location
5124 WSH_LOCATIONS_PKG.convert_internal_cust_location(
5125 p_internal_cust_location_id => l_dropoff_location_id,
5126 x_internal_org_location_id => l_physical_loc_id,
5127 x_return_status => l_return_status);
5128
5129 IF l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR,
5130 WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)
5131 THEN
5132 x_return_status := l_return_status;
5133 IF l_debug_on THEN
5134 WSH_DEBUG_SV.log(l_module_name, 'Conversion Failed,l_return_status', l_return_status);
5135 END IF;
5136 EXIT; -- exit out of the stop_tab loop
5137 END IF;
5138
5139 IF l_debug_on THEN
5140 WSH_DEBUG_SV.log(l_module_name, 'Delivery Physical Location Id:'||l_physical_loc_id);
5141 WSH_DEBUG_SV.log(l_module_name, 'Stop Location Id:'||l_stop_tab(i).stop_location_id);
5142 END IF;
5143
5144 IF l_physical_loc_id IS NOT NULL AND
5145 to_number(l_stop_tab(i).stop_location_id) = l_physical_loc_id THEN
5146 IF l_debug_on THEN
5147 WSH_DEBUG_SV.log(l_module_name, 'Delivery Dropoff Dummy Location Matches Incoming Stop Location');
5148 END IF;
5149 -- As there is only 1 delivery involved, create the stop based on the
5150 -- dummy location. Delivery assignment is done based on this location.
5151 -- x_stop_tab matches l_stop_tab, same index
5152 --l_stop_tab(i).stop_location_id := l_dropoff_location_id;
5153 x_stop_tab(i).stop_location_id := l_dropoff_location_id;
5154 x_delivery_tab(x_delivery_tab.FIRST).dropoff_stop_location_id := l_dropoff_location_id;
5155 -- Keep the other attributes same, like Sequence,dates
5156 END IF;
5157
5158 END IF;--}
5159
5160 ELSE -- Multiple Deliveries coming in the GC3 trip --} --{
5161 -- Loop thru the deliveries to compare the dropoff location of deliveries
5162 -- with current stop location
5163 FOR j in x_delivery_tab.FIRST..x_delivery_tab.LAST
5164 LOOP--{
5165
5166 -- Match Stop Location to Delivery Dropoff Location
5167 l_dropoff_location_id := to_number(x_delivery_tab(j).ultimate_dropoff_location_id);
5168 IF l_dropoff_location_id = to_number(l_stop_tab(i).stop_location_id) THEN--{
5169 -- no action required as Dropoff Location matches incoming stop
5170 IF l_debug_on THEN
5171 WSH_DEBUG_SV.logmsg(l_module_name, 'Dropoff Location Matches Stop Location for delivery:'||x_delivery_tab(j).delivery_id);
5172 END IF;
5173
5174 ELSIF l_dropoff_location_id <> to_number(l_stop_tab(i).stop_location_id) THEN--} --{
5175
5176 IF l_debug_on THEN
5177 WSH_DEBUG_SV.logmsg(l_module_name, 'Dropoff Location Does not match Stop Location for delivery:'||x_delivery_tab(j).delivery_id);
5178 END IF;
5179
5180 -- get the physical location corresponding to delivery
5181 -- dropoff location and match with Stop Location
5182 WSH_LOCATIONS_PKG.convert_internal_cust_location(
5183 p_internal_cust_location_id => l_dropoff_location_id,
5184 x_internal_org_location_id => l_physical_loc_id,
5185 x_return_status => l_return_status);
5186
5187 IF l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR,
5188 WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)
5189 THEN
5190 x_return_status := l_return_status;
5191 IF l_debug_on THEN
5192 WSH_DEBUG_SV.log(l_module_name, 'Conversion Failed,l_return_status', l_return_status);
5193 END IF;
5194 EXIT; -- exit out of the stop_tab loop
5195 END IF;
5196
5197 IF l_debug_on THEN
5198 WSH_DEBUG_SV.logmsg(l_module_name, 'Delivery Physical Location Id:'||l_physical_loc_id);
5199 WSH_DEBUG_SV.logmsg(l_module_name, 'Stop Location Id:'||l_stop_tab(i).stop_location_id);
5200 END IF;
5201
5202 -- The physical location for delivery dropoff location matches the incoming stop
5203 -- location.
5204 IF l_physical_loc_id IS NOT NULL AND
5205 to_number(l_stop_tab(i).stop_location_id) = l_physical_loc_id THEN--{
5206 IF l_debug_on THEN
5207 WSH_DEBUG_SV.logmsg(l_module_name, 'Delivery Dropoff Dummy Location Matches Incoming Stop Location');
5208 END IF;
5209
5210 l_update_stop := 'Y';
5211 -- As there are multiple deliveries, need to evaluate the pickup of those
5212 -- as well to see if any of them have location matching to the physical
5213 FOR k in l_dlvy_tab.FIRST..l_dlvy_tab.LAST
5214 LOOP
5215 -- Compare the deliveries except the current delivery
5216 -- and the Pickup location of those match the Incoming Stop
5217 -- Location
5218 IF l_dlvy_tab(k).delivery_id <> x_delivery_tab(j).delivery_id AND
5219 to_number(l_dlvy_tab(k).pickup_stop_location_id) = to_number(l_stop_tab(i).stop_location_id) THEN
5220 -- There exist other deliveries linked to this stop location,
5221 -- Cannot convert the Incoming Stop location to correspond to the dummy
5222 -- Need to create a new stop for the Dummy Location
5223 l_update_stop := 'N';
5224 IF l_debug_on THEN
5225 WSH_DEBUG_SV.logmsg(l_module_name,'Atleast one delivery uses this location as pickup, delivery_id:'||l_dlvy_tab(k).delivery_id);
5226 END IF;
5227 EXIT; -- out of this loop for delivery, once a match is found
5228 END IF;
5229 END LOOP;
5230
5231 IF l_debug_on THEN
5232 WSH_DEBUG_SV.logmsg(l_module_name, 'L_UPDATE_STOP FLAG:'||l_update_stop);
5233 END IF;
5234
5235 IF l_update_stop = 'Y' THEN--{
5236 -- Update the Stop Location for current stop to match delivery location
5237 -- x_stop_tab matches l_stop_tab
5238 --l_stop_tab(i).stop_location_id := l_dropoff_location_id;
5239 x_stop_tab(i).stop_location_id := l_dropoff_location_id;
5240 -- Update Delivery tab, which is used while assigning delivery to trip
5241 x_delivery_tab(j).dropoff_stop_location_id := l_dropoff_location_id;
5242
5243 ELSE -- l_update_stop = N, when other deliveries use physical location
5244
5245 -- Assign current Stop record to the new stop table
5246 l_index := l_new_stop_tab.count + 1;
5247 l_new_stop_tab(l_index) := l_stop_tab(i);
5248 -- Keep all information same, except location
5249 -- Stop interface id is also kept the same!
5250 -- Stop Sequence Number is incremented for all the following stops
5251 l_new_stop_tab(l_index).stop_location_id := l_dropoff_location_id;
5252 -- For this delivery, set dropoff, which is used in Assign Delivery to Trip
5253 x_delivery_tab(j).dropoff_stop_location_id := l_dropoff_location_id;
5254
5255 -- As the SSN can be in sent by GC3 in any order. Hence,
5256 -- Update Stop Sequence Number for all the stops with sequence number
5257 -- higher than the stop for dummy location. Also, update SSN for the
5258 -- current stop being processed which has SSN same as the dummy stop
5259 -- Loop across x_stop_tab, x_stop_tab is ordered by SSN for each trip
5260 FOR stop_update IN x_stop_tab.FIRST..x_stop_tab.LAST
5261 LOOP
5262 IF x_stop_tab(stop_update).stop_sequence_number >= l_stop_tab(i).stop_sequence_number
5263 THEN
5264 -- This will ensure dummy and physical stops are created together
5265 -- Physical and other higher stops (with higher SSN are pushed out by 1)
5266 x_stop_tab(stop_update).stop_sequence_number
5267 := x_stop_tab(stop_update).stop_sequence_number + 1;
5268 END IF;
5269 END LOOP;
5270 END IF;--} -- l_update_stop condition
5271
5272 END IF;--} -- physical location of delivery matches incoming stop location
5273 END IF;--} -- dropoff location comparison with incoming stop location
5274 END LOOP;--} -- across delivery_tab
5275 END IF; --} -- single or multiple count of deliveries
5276
5277 END LOOP;--} -- across stop_tab Loop
5278
5279 IF l_debug_on THEN
5280 WSH_DEBUG_SV.log(l_module_name,'New Dummy Stop Tab Count:', l_new_stop_tab.count);
5281 WSH_DEBUG_SV.log(l_module_name,'Input Stop Tab Count:', x_stop_tab.count);
5282 END IF;
5283
5284 IF l_new_stop_tab.count > 0 THEN--{
5285 FOR new_stop in l_new_stop_tab.FIRST..l_new_stop_tab.LAST
5286 LOOP
5287 -- This record will be inserted towards the end of x_stop_tab, but
5288 -- that is fine for create/update as well as for Assignment where
5289 -- delivery dropoff location is compared with stop location and
5290 -- delivery leg dropoff stop interface id to differenciate between
5291 -- the physical and dummy dropoffs.
5292 x_stop_tab(l_stop_tab.count + 1) := l_new_stop_tab(new_stop);
5293 END LOOP;
5294 END IF;--}
5295
5296 IF l_debug_on THEN
5297 WSH_DEBUG_SV.log(l_module_name,'Delivery Tab Count:', x_delivery_tab.count);
5298 WSH_DEBUG_SV.log(l_module_name,'Output Stop Tab Count:', x_stop_tab.count);
5299 WSH_DEBUG_SV.log(l_module_name,'x_return_status', x_return_status);
5300 WSH_DEBUG_SV.pop(l_module_name);
5301 END IF;
5302
5303 EXCEPTION
5304 WHEN OTHERS THEN
5305 -- Close any open cursors
5306 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
5307 WSH_UTIL_CORE.DEFAULT_HANDLER(
5308 'WSH_TMS_RELEASE.PROCESS_INTERNAL_LOCATIONS',l_module_name);
5309 IF l_debug_on THEN
5310 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
5311 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
5312 END IF;
5313
5314 END process_internal_locations;
5315
5316 --
5317 --
5318 -- ECO 5101760
5319 -- Get Server Time for Timezone conversion
5320 PROCEDURE get_server_time
5321 (p_source_time IN DATE,
5322 p_source_timezone_code IN VARCHAR2,
5323 x_server_time OUT NOCOPY DATE,
5324 x_return_status OUT NOCOPY VARCHAR2,
5325 x_msg_count OUT NOCOPY NUMBER,
5326 x_msg_data OUT NOCOPY VARCHAR2) IS
5327
5328 CURSOR c_get_timezone_id(c_timezone_code IN VARCHAR2) IS
5329 SELECT upgrade_tz_id
5330 FROM fnd_timezones_b
5331 WHERE timezone_code = c_timezone_code;
5332
5333 l_server_tz_id NUMBER;
5334 l_source_tz_id NUMBER;
5335
5336 invalid_timezone EXCEPTION;
5337
5338 l_debug_on CONSTANT BOOLEAN := WSH_DEBUG_SV.is_debug_enabled;
5339 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'get_server_time';
5340
5341 BEGIN
5342 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
5343
5344 IF l_debug_on THEN
5345 WSH_DEBUG_SV.push (l_module_name);
5346 WSH_DEBUG_SV.logmsg(l_module_name,'p_source_timezone_code : '||p_source_timezone_code);
5347 WSH_DEBUG_SV.logmsg(l_module_name,'p_source_time : '||to_char(p_source_time,'DD-MON-RRRR HH24:MI:SS'));
5348 END IF;
5349
5350 IF p_source_timezone_code IS NULL THEN
5351 RAISE invalid_timezone;
5352 END IF;
5353
5354 OPEN c_get_timezone_id(p_source_timezone_code);
5355 FETCH c_get_timezone_id INTO l_source_tz_id;
5356 IF c_get_timezone_id%NOTFOUND THEN
5357 RAISE invalid_timezone;
5358 END IF;
5359 CLOSE c_get_timezone_id;
5360
5361 l_server_tz_id := FND_PROFILE.value('SERVER_TIMEZONE_ID');
5362
5363 IF l_debug_on THEN
5364 WSH_DEBUG_SV.logmsg(l_module_name,'Server tz id :'||l_server_tz_id);
5365 END IF;
5366
5367 HZ_TIMEZONE_PUB.Get_Time(
5368 p_api_version => 1.0,
5369 p_init_msg_list => 'F',
5370 p_source_tz_id => l_source_tz_id,
5371 p_dest_tz_id => l_server_tz_id,
5372 p_source_day_time => p_source_time,
5373 x_dest_day_time => x_server_time,
5374 x_return_status => x_return_status,
5375 x_msg_count => x_msg_count,
5376 x_msg_data => x_msg_data);
5377
5378 IF l_debug_on THEN
5379 WSH_DEBUG_SV.logmsg(l_module_name,'Returning time : '|| x_server_time);
5380 WSH_DEBUG_SV.logmsg(l_module_name,'Returning x_return_status : '|| x_return_status);
5381 WSH_DEBUG_SV.pop(l_module_name);
5382 END IF;
5383
5384 EXCEPTION
5385 WHEN invalid_timezone THEN
5386 FND_MESSAGE.SET_NAME('WSH','WSH_OTM_INVALID_TIMEZONE');
5387 WSH_UTIL_CORE.ADD_MESSAGE(WSH_UTIL_CORE.G_RET_STS_ERROR);
5388 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
5389 x_msg_data := 'Input Timezone is not a valid FND timezone code';
5390 --
5391 IF l_debug_on THEN
5392 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:invalid_timezone');
5393 END IF;
5394 --
5395
5396 WHEN others THEN
5397 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
5398 x_msg_data := 'Oracle error message is '|| SQLERRM;
5399 WSH_UTIL_CORE.default_handler('WSH_OTM_INBOUND_GRP.get_server_time');
5400 --
5401 IF l_debug_on THEN
5402 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
5403 WSH_DEBUG_SV.logmsg(l_module_name,'-------------- END ----------------');
5404 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
5405 END IF;
5406 --
5407 END get_server_time;
5408
5409 --====================================================
5410
5411 END WSH_TMS_RELEASE;
[Home] [Help]