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