[Home] [Help]
PACKAGE BODY: APPS.MST_RELEASE
Source
1 PACKAGE BODY MST_RELEASE AS
2 /* $Header: MSTRELPB.pls 115.37 2004/05/13 09:27:40 atsrivas noship $ */
3
4 type varchar2_tab_type is table of varchar2(100) index by binary_integer;
5 g_release_type number; -- being used in MSTEXCEP.pld
6 g_tp_release_code varchar2(30);
7
8 g_cnt_group_released pls_integer;
9 g_cnt_group_failed pls_integer;
10 g_cnt_group_not_attempted pls_integer;
11 g_cnt_trip_released pls_integer;
12 g_cnt_trip_failed pls_integer;
13 g_cnt_trip_not_attempted pls_integer;
14 g_cnt_truck_released pls_integer;
15 g_cnt_truck_failed pls_integer;
16 g_cnt_truck_not_attempted pls_integer;
17 g_cnt_ltl_released pls_integer;
18 g_cnt_ltl_failed pls_integer;
19 g_cnt_ltl_not_attempted pls_integer;
20 g_cnt_parcel_released pls_integer;
21 g_cnt_parcel_failed pls_integer;
22 g_cnt_parcel_not_attempted pls_integer;
23 g_cnt_cm_released pls_integer;
24 g_cnt_cm_failed pls_integer;
25 g_cnt_cm_selected pls_integer;
26 g_cnt_deadhead_released pls_integer;
27 g_cnt_deadhead_failed pls_integer;
28 g_cnt_deadhead_not_attempted pls_integer;
29 g_cnt_etrip_not_attempted pls_integer;
30
31 g_release_debug_flag_set boolean;
32 g_log_must_message pls_integer;
33 g_house_keeping pls_integer;
34 g_apply_to_te pls_integer;
35 g_purge_interface_table pls_integer;
36 g_update_tp_tables pls_integer;
37 g_log_flow_of_control pls_integer;
38 g_log_failed_data pls_integer;
39 g_log_released_data pls_integer;
40 g_purge_mst_release_temp pls_integer;
41 g_log_statistics pls_integer;
42 g_log_ruleset_where_clause pls_integer;
43 g_truncate_mrt pls_integer;
44 g_delete_record_count pls_integer;
45 g_delete_record_count_loop pls_integer;
46
47 g_where_clause varchar2(4000); -- where clause is stored for auto release with rule set
48 g_auto_release pls_integer; -- used only in release with rule set
49 g_release_data varchar2(4);
50
51 g_str_truck_tl varchar2(80);
52 g_str_ltl_tl varchar2(80);
53 g_str_parcel_tl varchar2(80);
54
55 function get_cond(p_condition varchar2) return varchar2;
56 procedure print_info(p_release_debug_control in number, p_info_str in varchar2);
57
58 procedure initialize_package_variables is
59 cursor cur_mode_of_transport (l_mode_of_transport in varchar2)
60 is
61 select meaning
62 from wsh_lookups
63 where lookup_type = 'WSH_MODE_OF_TRANSPORT'
64 and lookup_code = l_mode_of_transport;
65
66 l_mode_of_transport varchar2(30);
67 begin
68 --g_release_type
69 g_tp_release_code := wsh_tp_release_grp.G_TP_RELEASE_CODE;
70
71 g_cnt_group_released := 0;
72 g_cnt_group_failed := 0;
73 g_cnt_group_not_attempted := 0;
74 g_cnt_trip_released := 0;
75 g_cnt_trip_failed := 0;
76 g_cnt_trip_not_attempted := 0;
77 g_cnt_truck_released := 0;
78 g_cnt_truck_failed := 0;
79 g_cnt_truck_not_attempted := 0;
80 g_cnt_ltl_released := 0;
81 g_cnt_ltl_failed := 0;
82 g_cnt_ltl_not_attempted := 0;
83 g_cnt_parcel_released := 0;
84 g_cnt_parcel_failed := 0;
85 g_cnt_parcel_not_attempted := 0;
86 g_cnt_cm_released := 0;
87 g_cnt_cm_failed := 0;
88 g_cnt_cm_selected := 0;
89 g_cnt_deadhead_released := 0;
90 g_cnt_deadhead_failed := 0;
91 g_cnt_deadhead_not_attempted := 0;
92 g_cnt_etrip_not_attempted := 0;
93
94 g_release_debug_flag_set := FALSE;
95 g_log_must_message := 1;
96 g_house_keeping := 0;
97 g_apply_to_te := 1;
98 g_purge_interface_table := 1;
99 g_update_tp_tables := 1;
100 g_log_flow_of_control := 0;
101 g_log_failed_data := 1;
102 g_log_released_data := 0;
103 g_purge_mst_release_temp := 1;
104 g_log_statistics := 1;
105 g_log_ruleset_where_clause := 0;
106 g_truncate_mrt := 1;
107 g_delete_record_count := 1;
108 g_delete_record_count_loop := 1;
109
110 g_where_clause := null; -- where clause is stored for auto release with rule set
111 g_auto_release := null; -- used only in release with rule set
112 g_release_data := 'TRIP';
113
114 open cur_mode_of_transport ('TRUCK');
115 fetch cur_mode_of_transport into g_str_truck_tl;
116 close cur_mode_of_transport;
117
118 open cur_mode_of_transport ('LTL');
119 fetch cur_mode_of_transport into g_str_ltl_tl;
120 close cur_mode_of_transport;
121
122 open cur_mode_of_transport ('PARCEL');
123 fetch cur_mode_of_transport into g_str_parcel_tl;
124 close cur_mode_of_transport;
125 end initialize_package_variables;
126
127 procedure set_concurrent_status(p_status in varchar2, p_message in varchar2) is
128 l_flag boolean := false;
129 begin
130 l_flag := fnd_concurrent.set_completion_status(p_status, p_message);
131 end set_concurrent_status;
132
133 function get_seeded_message(p_seeded_string in varchar2)
134 return varchar2 is
135 l_Message_Text varchar2(1000);
136 begin
137 fnd_message.set_name('MST',p_seeded_string);
138 l_Message_Text := fnd_message.get;
139 return l_Message_Text;
140 exception
141 when others then
142 return null;
143 end get_seeded_message;
144
145 procedure log_statistics is
146 l_Message_Text varchar2(1000);
147 begin
148 if g_log_statistics = 1 then
149
150 print_info(g_log_must_message,'*****************************************************************************************************************');
151 fnd_message.set_name('MST','MST_REL_BK_MESSAGE_1');
152 fnd_message.set_token('N1',to_char(g_cnt_group_released+g_cnt_group_failed+g_cnt_group_not_attempted));
153 fnd_message.set_token('N2',to_char(g_cnt_group_released+g_cnt_group_failed));
154 fnd_message.set_token('N3',to_char(g_cnt_group_released));
155 fnd_message.set_token('N4',to_char(g_cnt_group_failed));
156 fnd_message.set_token('N5',to_char(g_cnt_group_not_attempted));
157 l_Message_Text := fnd_message.get;
158 print_info(g_log_must_message,l_Message_Text);
159
160 fnd_message.set_name('MST','MST_REL_BK_MESSAGE_2');
161 fnd_message.set_token('N1',to_char(g_cnt_trip_released+g_cnt_trip_failed+g_cnt_trip_not_attempted));
162 fnd_message.set_token('N2',to_char(g_cnt_trip_released+g_cnt_trip_failed));
163 fnd_message.set_token('N3',to_char(g_cnt_trip_released));
164 fnd_message.set_token('N4',to_char(g_cnt_trip_failed));
165 fnd_message.set_token('N5',to_char(g_cnt_trip_not_attempted));
166 l_Message_Text := fnd_message.get;
167 print_info(g_log_must_message,l_Message_Text);
168
169 fnd_message.set_name('MST','MST_REL_BK_MESSAGE_50');
170 fnd_message.set_token('MODEOFTRANSPORT',g_str_truck_tl||' ');
171 fnd_message.set_token('N1',to_char(g_cnt_truck_released+g_cnt_truck_failed+g_cnt_truck_not_attempted));
172 fnd_message.set_token('N2',to_char(g_cnt_truck_released+g_cnt_truck_failed));
173 fnd_message.set_token('N3',to_char(g_cnt_truck_released));
174 fnd_message.set_token('N4',to_char(g_cnt_truck_failed));
175 fnd_message.set_token('N5',to_char(g_cnt_truck_not_attempted));
176 l_Message_Text := fnd_message.get;
177 print_info(g_log_must_message,l_Message_Text);
178
179 fnd_message.set_name('MST','MST_REL_BK_MESSAGE_50');
180 fnd_message.set_token('MODEOFTRANSPORT',g_str_ltl_tl||' ');
181 fnd_message.set_token('N1',to_char(g_cnt_ltl_released+g_cnt_ltl_failed+g_cnt_ltl_not_attempted));
182 fnd_message.set_token('N2',to_char(g_cnt_ltl_released+g_cnt_ltl_failed));
183 fnd_message.set_token('N3',to_char(g_cnt_ltl_released));
184 fnd_message.set_token('N4',to_char(g_cnt_ltl_failed));
185 fnd_message.set_token('N5',to_char(g_cnt_ltl_not_attempted));
186 l_Message_Text := fnd_message.get;
187 print_info(g_log_must_message,l_Message_Text);
188
189 fnd_message.set_name('MST','MST_REL_BK_MESSAGE_50');
190 fnd_message.set_token('MODEOFTRANSPORT',g_str_parcel_tl);
191 fnd_message.set_token('N1',to_char(g_cnt_parcel_released+g_cnt_parcel_failed+g_cnt_parcel_not_attempted));
192 fnd_message.set_token('N2',to_char(g_cnt_parcel_released+g_cnt_parcel_failed));
193 fnd_message.set_token('N3',to_char(g_cnt_parcel_released));
194 fnd_message.set_token('N4',to_char(g_cnt_parcel_failed));
195 fnd_message.set_token('N5',to_char(g_cnt_parcel_not_attempted));
196 l_Message_Text := fnd_message.get;
197 print_info(g_log_must_message,l_Message_Text);
198
199 fnd_message.set_name('MST','MST_REL_BK_MESSAGE_3');
200 fnd_message.set_token('N1',to_char(g_cnt_cm_selected));
201 fnd_message.set_token('N2',to_char(g_cnt_cm_released+g_cnt_cm_failed));
202 fnd_message.set_token('N3',to_char(g_cnt_cm_released));
203 fnd_message.set_token('N4',to_char(g_cnt_cm_failed));
204 fnd_message.set_token('N5',to_char(g_cnt_cm_selected-g_cnt_cm_released-g_cnt_cm_failed));
205 l_Message_Text := fnd_message.get;
206 print_info(g_log_must_message,l_Message_Text);
207
208 fnd_message.set_name('MST','MST_REL_BK_MESSAGE_49');
209 fnd_message.set_token('N1',to_char(g_cnt_deadhead_released+g_cnt_deadhead_failed+g_cnt_deadhead_not_attempted));
210 fnd_message.set_token('N2',to_char(g_cnt_deadhead_released+g_cnt_deadhead_failed));
211 fnd_message.set_token('N3',to_char(g_cnt_deadhead_released));
212 fnd_message.set_token('N4',to_char(g_cnt_deadhead_failed));
213 fnd_message.set_token('N5',to_char(g_cnt_deadhead_not_attempted));
214 l_Message_Text := fnd_message.get;
215 print_info(g_log_must_message,l_Message_Text);
216 print_info(g_log_must_message,'*****************************************************************************************************************');
217 end if;
218 --print_info(g_log_must_message,'Note :');
219 print_info(g_log_must_message,get_seeded_message('MST_REL_BK_MESSAGE_4'));
220 if g_house_keeping = 1 then
221 --print_info(g_log_must_message,' + House keeping done');
222 print_info(g_log_must_message,get_seeded_message('MST_REL_BK_MESSAGE_5'));
223 else
224 --print_info(g_log_must_message,' - House keeping NOT done');
225 print_info(g_log_must_message,get_seeded_message('MST_REL_BK_MESSAGE_6'));
226 end if;
227 if g_apply_to_te = 1 then
228 --print_info(g_log_must_message,' + TP data applied to TE');
229 print_info(g_log_must_message,get_seeded_message('MST_REL_BK_MESSAGE_7'));
230 else
231 --print_info(g_log_must_message,' - TP data NOT applied to TE');
232 print_info(g_log_must_message,get_seeded_message('MST_REL_BK_MESSAGE_8'));
233 end if;
234 if g_purge_interface_table = 1 then
235 --print_info(g_log_must_message,' + Interface table purged');
236 print_info(g_log_must_message,get_seeded_message('MST_REL_BK_MESSAGE_9'));
237 else
238 --print_info(g_log_must_message,' - Interface table NOT purged');
239 print_info(g_log_must_message,get_seeded_message('MST_REL_BK_MESSAGE_10'));
240 end if;
241 if g_update_tp_tables = 1 then
242 --print_info(g_log_must_message,' + TP tables updated');
243 print_info(g_log_must_message,get_seeded_message('MST_REL_BK_MESSAGE_11'));
244 else
245 --print_info(g_log_must_message,' - TP tables NOT updated');
249 --print_info(g_log_must_message,' + Flow of control info logged');
246 print_info(g_log_must_message,get_seeded_message('MST_REL_BK_MESSAGE_12'));
247 end if;
248 if g_log_flow_of_control = 1 then
250 print_info(g_log_must_message,get_seeded_message('MST_REL_BK_MESSAGE_13'));
251 else
252 --print_info(g_log_must_message,' - Flow of control info NOT logged');
253 print_info(g_log_must_message,get_seeded_message('MST_REL_BK_MESSAGE_14'));
254 end if;
255 if g_log_must_message = 1 then
256 --print_info(g_log_must_message,' + Mendatory messages logged');
257 print_info(g_log_must_message,get_seeded_message('MST_REL_BK_MESSAGE_15'));
258 else
259 --print_info(g_log_must_message,' - Mendatory messages NOT logged');
260 print_info(g_log_must_message,get_seeded_message('MST_REL_BK_MESSAGE_16'));
261 end if;
262 if g_log_failed_data = 1 then
263 --print_info(g_log_must_message,' + Failed release data logged');
264 print_info(g_log_must_message,get_seeded_message('MST_REL_BK_MESSAGE_17'));
265 else
266 --print_info(g_log_must_message,' - Failed release data NOT logged');
267 print_info(g_log_must_message,get_seeded_message('MST_REL_BK_MESSAGE_18'));
268 end if;
269 if g_log_released_data = 1 then
270 --print_info(g_log_must_message,' + Successful release data logged');
271 print_info(g_log_must_message,get_seeded_message('MST_REL_BK_MESSAGE_19'));
272 else
273 --print_info(g_log_must_message,' - Successful release data NOT logged');
274 print_info(g_log_must_message,get_seeded_message('MST_REL_BK_MESSAGE_20'));
275 end if;
276 if g_purge_mst_release_temp = 1 then
277 --print_info(g_log_must_message,' + TP release temporary table purged');
278 print_info(g_log_must_message,get_seeded_message('MST_REL_BK_MESSAGE_21'));
279 else
280 --print_info(g_log_must_message,' - TP release temporary table NOT purged');
281 print_info(g_log_must_message,get_seeded_message('MST_REL_BK_MESSAGE_22'));
282 end if;
283 if g_log_statistics = 1 then
284 --print_info(g_log_must_message,' + Release statistics logged');
285 print_info(g_log_must_message,get_seeded_message('MST_REL_BK_MESSAGE_23'));
286 else
287 --print_info(g_log_must_message,' - Release statistics NOT logged');
288 print_info(g_log_must_message,get_seeded_message('MST_REL_BK_MESSAGE_24'));
289 end if;
290 if g_cnt_group_failed > 0 then
291 if g_cnt_trip_not_attempted < 1 then
292 --set_concurrent_status('WARNING', g_cnt_group_failed ||' Groups failed. '|| g_cnt_trip_failed ||' Trips failed. All Trips attempted.');
293 fnd_message.set_name('MST','MST_REL_BK_MESSAGE_25');
294 fnd_message.set_token('N1',to_char(g_cnt_group_failed));
295 fnd_message.set_token('N2',to_char(g_cnt_trip_failed));
296 l_Message_Text := fnd_message.get;
297 set_concurrent_status('WARNING', l_Message_Text);
298 else
299 --set_concurrent_status('WARNING', g_cnt_group_failed ||' Groups failed. '|| g_cnt_trip_failed ||' Trips failed. '||g_cnt_trip_not_attempted||' Trips not attempted to release.');
300 fnd_message.set_name('MST','MST_REL_BK_MESSAGE_26');
301 fnd_message.set_token('N1',to_char(g_cnt_group_failed));
302 fnd_message.set_token('N2',to_char(g_cnt_trip_failed));
303 fnd_message.set_token('N3',to_char(g_cnt_trip_not_attempted));
304 l_Message_Text := fnd_message.get;
305 set_concurrent_status('WARNING', l_Message_Text);
306 end if;
307 elsif g_cnt_trip_not_attempted > 0 then
308 --set_concurrent_status('WARNING', g_cnt_group_released ||' Groups released. '|| g_cnt_trip_released ||' Trips released. '||g_cnt_trip_not_attempted||' Trips not attempted to release.' );
309 fnd_message.set_name('MST','MST_REL_BK_MESSAGE_27');
310 fnd_message.set_token('N1',to_char(g_cnt_group_released));
311 fnd_message.set_token('N2',to_char(g_cnt_trip_released));
312 fnd_message.set_token('N3',to_char(g_cnt_trip_not_attempted));
313 l_Message_Text := fnd_message.get;
314 set_concurrent_status('WARNING', l_Message_Text);
315 else
316 --set_concurrent_status('NORMAL', g_cnt_group_released ||' Groups released. '|| g_cnt_trip_released ||' Trips released.');
317 fnd_message.set_name('MST','MST_REL_BK_MESSAGE_28');
318 fnd_message.set_token('N1',to_char(g_cnt_group_released));
319 fnd_message.set_token('N2',to_char(g_cnt_trip_released));
320 l_Message_Text := fnd_message.get;
321 set_concurrent_status('NORMAL', l_Message_Text);
322 end if;
323 end log_statistics;
324
325 procedure set_release_debug_flags is
326 l_profile_mst_release_debug varchar2(100);
327 begin
328 if not g_release_debug_flag_set then
329 l_profile_mst_release_debug := nvl(fnd_profile.value('MST_RELEASE_DEBUG'),'0111010110');
330 g_house_keeping := substr(l_profile_mst_release_debug,1,1);
331 g_apply_to_te := substr(l_profile_mst_release_debug,2,1);
332 g_purge_interface_table := substr(l_profile_mst_release_debug,3,1);
333 g_update_tp_tables := substr(l_profile_mst_release_debug,4,1);
334 g_log_flow_of_control := substr(l_profile_mst_release_debug,5,1);
335 g_log_failed_data := substr(l_profile_mst_release_debug,6,1);
336 g_log_released_data := substr(l_profile_mst_release_debug,7,1);
337 g_purge_mst_release_temp := substr(l_profile_mst_release_debug,8,1);
338 g_log_statistics := substr(l_profile_mst_release_debug,9,1);
339 g_log_ruleset_where_clause := substr(l_profile_mst_release_debug,10,1);
340 g_truncate_mrt := substr(l_profile_mst_release_debug,11,1);
341 g_delete_record_count_loop := substr(l_profile_mst_release_debug,12,1);
342 g_delete_record_count := substr(l_profile_mst_release_debug,13);
343 g_release_debug_flag_set := TRUE;
344 end if;
345 exception
349
346 when others then
347 null;
348 end set_release_debug_flags;
350 procedure log_group_trip_data (p_log_data in pls_integer
351 , p_group_id in pls_integer
352 , p_status in varchar2) is
353
354 cursor cur_trip_info (l_group_id in pls_integer)
355 is
356 select substr(' '||tp_plan_name||' '||tp_trip_number||' '||decode(mode_of_transport,'TRUCK',g_str_truck_tl,'LTL',g_str_ltl_tl,'PARCEL',g_str_parcel_tl,mode_of_transport),1,100)
357 from wsh_trips_interface
358 where interface_action_code = g_tp_release_code
359 and group_id = l_group_id
360 order by tp_trip_number;
361
362 type varchar2_tab_type is table of varchar2(100) index by binary_integer;
363
364 l_trip_info_tab varchar2_tab_type;
365 l_Message_Text varchar2(1000);
366 begin
367 if p_log_data = 1 then
368 --print_info(p_log_data,' Trips in this group are :');
369
370 open cur_trip_info (p_group_id);
371 fetch cur_trip_info bulk collect into l_trip_info_tab;
372 close cur_trip_info;
373
374 if nvl(l_trip_info_tab.last,0) > 0 then
375 fnd_message.set_name('MST','MST_REL_BK_MESSAGE_29');
376 fnd_message.set_token('N1',to_char(l_trip_info_tab.last));
377 l_Message_Text := fnd_message.get;
378 print_info(g_log_must_message,l_Message_Text);
379 for i in 1..l_trip_info_tab.last loop
380 print_info(p_log_data,l_trip_info_tab(i)||' '||p_status);
381 end loop;
382 end if;
383
384 end if;
385 end log_group_trip_data;
386
387 procedure insert_trips (p_plan_id in number
388 , p_release_id in number
389 , p_load_tab in number_tab_type
390 , p_load_type in varchar2) is
391 begin
392 print_info(g_log_flow_of_control,'insert_trips : Program started');
393 if nvl(p_load_tab.last,0) > 0 then
394 if p_load_type = 'CM' then
395 forall i in 1..p_load_tab.last
396 insert into mst_release_temp
397 (
398 release_id
399 , group_id
400 , plan_id
401 , trip_id
402 , sr_trip_id
403 , trip_number
404 , planned_flag
405 , release_status
406 , trip_start_date
407 , cm_id_of_trip
408 , continuous_move_sequence
409 , out_of_scope
410 , trip_process_flag
411 , selected_trips
412 , trip_id_iface
413 , status_code
414 , inventory_item_id
415 , organization_id
416 , carrier_id
417 , ship_method_code
418 , compile_designator
419 , mode_of_transport
420 , load_tender_status
421 , lane_id
422 , service_level
423 )
424 (
425 select p_release_id
426 , null
427 , mt.plan_id
428 , mt.trip_id
429 , mt.sr_trip_id
430 , mt.trip_number
431 , mt.planned_flag
432 , mt.release_status
433 , mt.trip_start_date
434 , mt.continuous_move_id
435 , mt.continuous_move_sequence
436 , mt.out_of_scope
437 , null
438 , 1
439 , wsh_trips_interface_s.nextval
440 , mt.status_code
441 , fvt.inventory_item_id
442 , fvt.organization_id
443 , mt.carrier_id
444 , mt.ship_method_code
445 , mp.compile_designator
446 , mt.mode_of_transport
447 , mt.load_tender_status
448 , mt.lane_id
449 , mt.service_level
450 from mst_plans mp
451 , mst_trips mt
452 , fte_vehicle_types fvt
453 where mt.plan_id = p_plan_id
454 and mt.plan_id = mp.plan_id
455 and mt.vehicle_type_id = fvt.vehicle_type_id (+)
456 and mt.continuous_move_id = p_load_tab(i)
457 );
458 elsif p_load_type = 'TRIP' then
459 forall i in 1..p_load_tab.last
460 insert into mst_release_temp
461 (
462 release_id
463 , group_id
464 , plan_id
465 , trip_id
466 , sr_trip_id
467 , trip_number
468 , planned_flag
469 , release_status
470 , trip_start_date
471 , cm_id_of_trip
472 , continuous_move_sequence
473 , out_of_scope
474 , trip_process_flag
475 , selected_trips
476 , trip_id_iface
477 , status_code
478 , inventory_item_id
479 , organization_id
480 , carrier_id
481 , ship_method_code
482 , compile_designator
483 , mode_of_transport
484 , load_tender_status
485 , lane_id
486 , service_level
487 )
488 (
489 select p_release_id
490 , null
491 , mt.plan_id
492 , mt.trip_id
493 , mt.sr_trip_id
494 , mt.trip_number
495 , mt.planned_flag
496 , mt.release_status
497 , mt.trip_start_date
498 , mt.continuous_move_id
499 , mt.continuous_move_sequence
500 , mt.out_of_scope
501 , null
502 , 1
503 , wsh_trips_interface_s.nextval
504 , mt.status_code
505 , fvt.inventory_item_id
506 , fvt.organization_id
507 , mt.carrier_id
508 , mt.ship_method_code
509 , mp.compile_designator
510 , mt.mode_of_transport
514 from mst_plans mp
511 , mt.load_tender_status
512 , mt.lane_id
513 , mt.service_level
515 , mst_trips mt
516 , fte_vehicle_types fvt
517 where mt.plan_id = p_plan_id
518 and mt.plan_id = mp.plan_id
519 and mt.vehicle_type_id = fvt.vehicle_type_id (+)
520 and mt.trip_id = p_load_tab(i)
521 );
522 end if;
523 commit;
524 end if;
525
526 print_info(g_log_flow_of_control,'insert_trips : Program ended');
527 end insert_trips;
528
529 procedure remove_unqualified_trips (x_return_status out nocopy varchar2
530 , p_plan_id in pls_integer
531 , p_release_id in pls_integer) is
532
533 -- cursor to retrieve out of scope trips and trips in TE
534 cursor cur_trips (l_release_id in pls_integer, l_plan_id in pls_integer)
535 is
536 select mrt.trip_id, mrt.trip_number, mrt.out_of_scope, mrt.planned_flag, mrt.release_status, decode(mrt.mode_of_transport,'TRUCK',g_str_truck_tl,'LTL',g_str_ltl_tl,'PARCEL',g_str_parcel_tl,mrt.mode_of_transport)
537 from mst_release_temp_gt mrt
538 where mrt.release_id = l_release_id
539 and mrt.trip_id is not null
540 and mrt.out_of_scope = 1;
541
542 -- cursor to retrieve empty trips and not part of continuous move
543 cursor cur_trips_1 (l_release_id in pls_integer, l_plan_id in pls_integer)
544 is
545 select mrt.trip_id, mrt.trip_number, decode(mrt.mode_of_transport,'TRUCK',g_str_truck_tl,'LTL',g_str_ltl_tl,'PARCEL',g_str_parcel_tl,mrt.mode_of_transport)
546 from mst_release_temp_gt mrt
547 where mrt.release_id = l_release_id
548 and mrt.cm_id_of_trip is null
549 and mrt.trip_id not in (select mdl.trip_id
550 from mst_delivery_legs mdl
551 where mdl.plan_id = l_plan_id);
552
553 l_trip_id_tab number_tab_type;
554 l_trip_number_tab number_tab_type;
555 l_out_of_scope_tab number_tab_type;
556 l_planned_flag_tab number_tab_type;
557 l_release_status_tab number_tab_type;
558 l_mode_of_transport_tl_tab varchar2_tab_type;
559 l_Message_Text varchar2(1000);
560 begin
561 print_info(g_log_flow_of_control,'remove_unqualified_trips : Program started');
562 if g_release_data <> 'PLAN' then
563 -- remove the out of scope trips
564 open cur_trips (p_release_id, p_plan_id);
565 fetch cur_trips bulk collect into l_trip_id_tab, l_trip_number_tab, l_out_of_scope_tab, l_planned_flag_tab, l_release_status_tab, l_mode_of_transport_tl_tab;
566 close cur_trips;
567
568 if nvl(l_trip_id_tab.last,0) >= 1 then
569 for i in 1..l_trip_id_tab.last loop
570 --print_info(g_log_must_message,'Trip '||l_trip_number_tab(i)||' was out of scope so can not be released directly');
571 fnd_message.set_name('MST','MST_REL_BK_MESSAGE_30');
572 fnd_message.set_token('N1',to_char(l_trip_number_tab(i))||' [ '||l_mode_of_transport_tl_tab(i)||' ]');
573 l_Message_Text := fnd_message.get;
574 print_info(g_log_must_message,l_Message_Text);
575 end loop;
576 print_info(g_log_must_message,'');
577
578 forall i in 1..l_trip_id_tab.last
579 update mst_release_temp_gt
580 set planned_flag = -1111
581 where release_id = p_release_id
582 and trip_id = l_trip_id_tab(i);
583 end if;
584 end if;
585
586 -- remove the empty trips which are not part of continuous move
587 open cur_trips_1 (p_release_id, p_plan_id);
588 fetch cur_trips_1 bulk collect into l_trip_id_tab, l_trip_number_tab, l_mode_of_transport_tl_tab;
589 close cur_trips_1;
590
591 if nvl(l_trip_id_tab.last,0) >= 1 then
592 for i in 1..l_trip_id_tab.last loop
593 --print_info(g_log_must_message,'Trip '||l_trip_number_tab(i)||' was an empty trip and not part of continuous move. So can not be released.');
594 fnd_message.set_name('MST','MST_REL_BK_MESSAGE_31');
595 fnd_message.set_token('N1',to_char(l_trip_number_tab(i))||' [ '||l_mode_of_transport_tl_tab(i)||' ]');
596 l_Message_Text := fnd_message.get;
597 print_info(g_log_must_message,l_Message_Text);
598 end loop;
599 print_info(g_log_must_message,'');
600
601 forall i in 1..l_trip_id_tab.last
602 update mst_release_temp_gt
603 set planned_flag = -2222
604 where release_id = p_release_id
605 and trip_id = l_trip_id_tab(i);
606 end if;
607
608 x_return_status := fnd_api.g_ret_sts_success;
609 print_info(g_log_flow_of_control,'remove_unqualified_trips : Program ended');
610 exception
611 when others then
612 x_return_status := fnd_api.g_ret_sts_unexp_error;
613 print_info(g_log_flow_of_control,'remove_unqualified_trips : Unexpected error ' || to_char(sqlcode) || ':' || SQLERRM);
614 set_concurrent_status('ERROR',get_seeded_message('MST_REL_BK_MESSAGE_47') || to_char(sqlcode) || ':' || SQLERRM);
615 end remove_unqualified_trips;
616
617 procedure populate_related_trip_thru_cm (x_return_status out nocopy varchar2
618 , p_plan_id in number
619 , p_release_id in pls_integer
620 , p_cm_id_of_trip in number
621 , p_planned_flag in number
622 , p_group_id in pls_integer) is
623
624 cursor cur_cm (l_release_id in pls_integer, l_cm_id in number)
625 is
626 select 1
627 from mst_release_temp_gt
628 where release_id = l_release_id
629 and continuous_move_id = l_cm_id;
630
631 l_cm_exist number := 0;
635 select mct.planned_flag, mct.sr_cm_trip_id, mct.lane_id, mct.service_level, mp.compile_designator, mct.cm_trip_number
632
633 cursor cur_cm_trips (l_plan_id in number, l_cm_id in number)
634 is
636 from mst_cm_trips mct
637 , mst_plans mp
638 where mp.plan_id = l_plan_id
639 and mct.plan_id = l_plan_id
640 and mct.continuous_move_id = l_cm_id;
641
642 l_cm_planned_flag number;
643 l_sr_cm_trip_id number;
644 l_lane_id number;
645 l_service_level varchar2(30);
646 l_compile_designator varchar2(10);
647 l_cm_trip_number number;
648
649 begin
650 print_info(g_log_flow_of_control,'populate_related_trip_thru_cm for CM = '||p_cm_id_of_trip||' : Program started');
651
652 open cur_cm (p_release_id, p_cm_id_of_trip);
653 fetch cur_cm into l_cm_exist;
654
655 if cur_cm%notfound then -- means its data are not available in mst_release_temp_gt
656 g_cnt_cm_selected := g_cnt_cm_selected + 1;
657 -- get the planned flag of cm
658 open cur_cm_trips(p_plan_id, p_cm_id_of_trip);
659 fetch cur_cm_trips into l_cm_planned_flag, l_sr_cm_trip_id, l_lane_id, l_service_level, l_compile_designator, l_cm_trip_number;
660 close cur_cm_trips;
661
662 -- insert continuous moves data into permanent temporary table
663 insert into mst_release_temp_gt
664 (
665 release_id
666 , plan_id
667 , group_id
668 , planned_flag
669 , continuous_move_id
670 , sr_cm_trip_id
671 , continuous_move_id_iface
672 , lane_id
673 , service_level
674 , compile_designator
675 , cm_trip_number
676 )
677 values
678 (
679 p_release_id
680 , p_plan_id
681 , p_group_id
682 , l_cm_planned_flag
683 , p_cm_id_of_trip
684 , l_sr_cm_trip_id
685 , fte_moves_interface_s.nextval
686 , l_lane_id
687 , l_service_level
688 , l_compile_designator
689 , l_cm_trip_number
690 );
691
692 -- insert trips corresponding to this continuous move into mst_release_temp_gt if it is not available
693 insert into mst_release_temp_gt
694 (
695 release_id
696 , group_id
697 , plan_id
698 , trip_id
699 , sr_trip_id
700 , trip_number
701 , planned_flag
702 , release_status
703 , trip_start_date
704 , cm_id_of_trip
705 , continuous_move_sequence
706 , out_of_scope
707 , trip_process_flag
708 , trip_id_iface
709 , status_code
710 , inventory_item_id
711 , organization_id
712 , carrier_id
713 , ship_method_code
714 , compile_designator
715 , mode_of_transport
716 , load_tender_status
717 , lane_id
718 , service_level
719 )
720 (
721 select p_release_id
722 , null
723 , mt.plan_id
724 , mt.trip_id
725 , mt.sr_trip_id
726 , mt.trip_number
727 , mt.planned_flag
728 , mt.release_status
729 , mt.trip_start_date
730 , mt.continuous_move_id
731 , mt.continuous_move_sequence
732 , mt.out_of_scope
733 , null
734 , wsh_trips_interface_s.nextval
735 , mt.status_code
736 , fvt.inventory_item_id
737 , fvt.organization_id
738 , mt.carrier_id
739 , mt.ship_method_code
740 , mp.compile_designator
741 , mt.mode_of_transport
742 , mt.load_tender_status
743 , mt.lane_id
744 , mt.service_level
745 from mst_plans mp
746 , mst_trips mt
747 , fte_vehicle_types fvt
748 where mt.plan_id = p_plan_id
749 and mt.continuous_move_id = p_cm_id_of_trip
750 and mt.plan_id = mp.plan_id
751 and mt.vehicle_type_id = fvt.vehicle_type_id (+)
752 and mt.trip_id not in (select mrt1.trip_id
753 from mst_release_temp_gt mrt1
754 where mrt1.release_id = p_release_id
755 and mrt1.trip_id is not null)
756 );
757
758 --assign same group_id and set planned_flag of trip under this cm minimum as 2 (routing) if cm is firm
759
760 update mst_release_temp_gt mrt
761 Set mrt.group_id = p_group_id
765
762 , mrt.planned_flag = decode(p_planned_flag,-1111,-1111,decode(l_cm_planned_flag, 1, decode(nvl(mrt.planned_flag,3),3,2,mrt.planned_flag), mrt.planned_flag))
763 where release_id = p_release_id
764 and cm_id_of_trip = p_cm_id_of_trip;
766 end if;
767 close cur_cm;
768
769 x_return_status := fnd_api.g_ret_sts_success;
770 print_info(g_log_flow_of_control,'populate_related_trip_thru_cm for CM = '||p_cm_id_of_trip||' Program ended');
771 exception
772 when others then
773 x_return_status := fnd_api.g_ret_sts_unexp_error;
774 print_info(g_log_flow_of_control,'populate_related_trip_thru_cm : Unexpected error ' || to_char(sqlcode) || ':' || SQLERRM);
775 set_concurrent_status('ERROR',get_seeded_message('MST_REL_BK_MESSAGE_47') || to_char(sqlcode) || ':' || SQLERRM);
776 end populate_related_trip_thru_cm;
777
778 procedure populate_related_trip_thru_del (x_return_status out nocopy varchar2
779 , p_plan_id in number
780 , p_release_id in pls_integer
781 , p_trip_id in number
782 , p_planned_flag in number
783 , p_group_id in pls_integer) is
784 l_count pls_integer := 0;
785 begin
786 print_info(g_log_flow_of_control,'populate_related_trip_thru_del for trip = '||p_trip_id||' : Program started');
787 if g_release_data <> 'PLAN' then
788 -- insert related trip ids into mst_release_temp_gt with same group_id
789 insert into mst_release_temp_gt
790 (
791 release_id
792 , group_id
793 , plan_id
794 , sr_trip_id
795 , trip_id
796 , trip_number
797 , planned_flag
798 , release_status
799 , trip_start_date
800 , cm_id_of_trip
801 , continuous_move_sequence
802 , out_of_scope
803 --, trip_id_iface bug # 3509717 moving to update section below
804 , trip_process_flag
805 , status_code
806 , inventory_item_id
807 , organization_id
808 , carrier_id
809 , ship_method_code
810 , compile_designator
811 , mode_of_transport
812 , load_tender_status
813 , lane_id
814 , service_level
815 )
816 (
817 select distinct p_release_id -- distinct added to remove repeatation of trips
818 , null
819 , mt.plan_id
820 , mt.sr_trip_id
821 , mt.trip_id
822 , mt.trip_number
823 , mt.planned_flag
824 , mt.release_status
825 , mt.trip_start_date
826 , mt.continuous_move_id
827 , mt.continuous_move_sequence
828 , mt.out_of_scope
829 --, wsh_trips_interface_s.nextval bug # 3509717 moving to update section below
830 , null
831 , mt.status_code
832 , fvt.inventory_item_id
836 , mp.compile_designator
833 , fvt.organization_id
834 , mt.carrier_id
835 , mt.ship_method_code
837 , mt.mode_of_transport
838 , mt.load_tender_status
839 , mt.lane_id
840 , mt.service_level
841 from mst_delivery_legs mdl
842 , mst_delivery_legs mdl1
843 , mst_trips mt
844 , mst_plans mp
845 , fte_vehicle_types fvt
846 where mdl.plan_id = p_plan_id
847 and mdl.trip_id = p_trip_id
848 and mdl1.plan_id = mdl.plan_id
849 and mdl1.delivery_id = mdl.delivery_id
850 and mdl1.trip_id <> mdl.trip_id
851 and mt.plan_id = mdl1.plan_id
852 and mt.trip_id = mdl1.trip_id
853 and mt.plan_id = mp.plan_id
854 and mt.vehicle_type_id = fvt.vehicle_type_id (+)
855 and mt.trip_id not in (select mrt.trip_id
856 from mst_release_temp_gt mrt
857 where release_id = p_release_id)
858 );
859 l_count := sql%rowcount;
860 end if;
861 --assign same group_id and set planned_flag of trip which are related to current trip thru delivery
862 update mst_release_temp_gt mrt
863 Set mrt.group_id = p_group_id
864 , trip_id_iface = wsh_trips_interface_s.nextval -- bug # 3509717 moved here from insert section
865 , mrt.planned_flag = decode(p_planned_flag,-1111,-1111,
866 decode(mrt.out_of_scope,1,mrt.planned_flag
867 ,decode(p_planned_flag,1,decode(nvl(mrt.planned_flag,0),3,2,mrt.planned_flag),mrt.planned_flag)))
868 where mrt.release_id = p_release_id
869 and mrt.trip_id in (select mdl_rel.trip_id
870 from mst_delivery_legs mdl
871 , mst_delivery_legs mdl_rel
872 where mdl_rel.plan_id = mdl.plan_id
873 and mdl_rel.delivery_id = mdl.delivery_id
874 and mdl_rel.trip_id <> mdl.trip_id
875 and mdl.plan_id = p_plan_id
876 and mdl.trip_id = p_trip_id);
877
878 x_return_status := fnd_api.g_ret_sts_success;
879 print_info(g_log_flow_of_control,'populate_related_trip_thru_del for trip = '||p_trip_id||' : Program ended');
880 exception
881 when others then
882 x_return_status := fnd_api.g_ret_sts_unexp_error;
883 print_info(g_log_flow_of_control,'populate_related_trip_thru_del : Unexpected error ' || to_char(sqlcode) || ':' || SQLERRM);
884 set_concurrent_status('ERROR',get_seeded_message('MST_REL_BK_MESSAGE_47') || to_char(sqlcode) || ':' || SQLERRM);
885 end populate_related_trip_thru_del;
886
887 procedure populate_related_trips (x_return_status out nocopy varchar2
888 , p_plan_id in number
889 , p_release_id in pls_integer) is
890
891 cursor cur_trips(l_release_id in pls_integer)
892 is
893 select trip_id
894 , group_id
895 , planned_flag
896 , cm_id_of_trip
897 from mst_release_temp_gt
898 where release_id = l_release_id
899 and trip_id is not null
900 and trip_process_flag is null
901 order by group_id;
902
903 l_trip_id number;
904 l_group_id number;
905 l_planned_flag number;
906 l_cm_id_of_trip number;
907
908 l_counter number;
909
910 l_return_status varchar2(1);
911 l_error_from_called_procedure exception;
912 begin
913 print_info(g_log_flow_of_control,'populate_related_trips : Program started');
914
915 loop
916 l_counter := 0;
917 open cur_trips(p_release_id);
918 fetch cur_trips into l_trip_id, l_group_id, l_planned_flag, l_cm_id_of_trip;
919 if cur_trips%notfound then
920 l_counter := 1;
921 end if;
922 close cur_trips;
923
924 if l_counter = 1 then
925 exit;
926 end if;
927
928 if l_group_id is null then
929 select mst_release_seq.nextval
930 into l_group_id
931 from dual;
932 end if;
933
934 --update mst_release_temp_gt so that it would not be selected during next round of cursor opening
935 update mst_release_temp_gt
936 set group_id = l_group_id
940
937 , trip_process_flag = 1
938 where release_id = p_release_id
939 and trip_id = l_trip_id;
941 if l_cm_id_of_trip is not null then -- means this trips is part of a continuous move
942 populate_related_trip_thru_cm(l_return_status, p_plan_id, p_release_id, l_cm_id_of_trip, l_planned_flag, l_group_id);
943 if l_return_status <> fnd_api.g_ret_sts_success then
944 raise l_error_from_called_procedure;
945 end if;
946 end if;
947
948 populate_related_trip_thru_del(l_return_status, p_plan_id, p_release_id, l_trip_id, l_planned_flag, l_group_id);
949 if l_return_status <> fnd_api.g_ret_sts_success then
950 raise l_error_from_called_procedure;
951 end if;
952 end loop;
953
954 x_return_status := fnd_api.g_ret_sts_success;
955 print_info(g_log_flow_of_control,'populate_related_trips : Program ended');
956 exception
957 when l_error_from_called_procedure then
958 x_return_status := fnd_api.g_ret_sts_error;
959 when others then
960 x_return_status := fnd_api.g_ret_sts_unexp_error;
961 print_info(g_log_flow_of_control,'populate_related_trips : Unexpected error ' || to_char(sqlcode) || ':' || SQLERRM);
962 set_concurrent_status('ERROR',get_seeded_message('MST_REL_BK_MESSAGE_47') || to_char(sqlcode) || ':' || SQLERRM);
963 end populate_related_trips;
964
965 procedure remove_grp_of_passed_dep_dt (x_return_status out nocopy varchar2
966 , p_release_id in number
967 , p_release_start_date in date) is
968
969 cursor cur_trips (l_release_id in pls_integer, l_release_start_date in date)
970 is
971 select mrt.group_id, mrt.trip_number, decode(mrt.mode_of_transport,'TRUCK',g_str_truck_tl,'LTL',g_str_ltl_tl,'PARCEL',g_str_parcel_tl,mrt.mode_of_transport)
972 from mst_release_temp_gt mrt
973 where mrt.release_id = l_release_id
974 and mrt.trip_start_date < l_release_start_date;
975
976 l_group_id_tab number_tab_type;
977 l_trip_number_tab number_tab_type;
978 l_mode_of_transport_tl_tab varchar2_tab_type;
979 l_Message_Text varchar2(1000);
980 begin
981 print_info(g_log_flow_of_control,'remove_grp_of_passed_dep_dt : Program started');
982
983 open cur_trips (p_release_id, p_release_start_date);
984 fetch cur_trips bulk collect into l_group_id_tab, l_trip_number_tab, l_mode_of_transport_tl_tab;
985 close cur_trips;
986
987 if nvl(l_group_id_tab.last,0) > 0 then
988 -- mark entire group for delete in temporary table
989 forall i in 1..l_group_id_tab.last
990 update mst_release_temp_gt mrt
991 set mrt.planned_flag = -3333
992 where mrt.release_id = p_release_id
993 and mrt.group_id = l_group_id_tab(i);
994
995 for i in 1..l_trip_number_tab.last loop
996 --print_info(g_log_must_message,'Group of trip '||l_trip_number_tab(i)||' is not being released as departure date of this trip has been passed');
997 fnd_message.set_name('MST','MST_REL_BK_MESSAGE_32');
998 fnd_message.set_token('N1',to_char(l_trip_number_tab(i))||' [ '||l_mode_of_transport_tl_tab(i)||' ]');
999 l_Message_Text := fnd_message.get;
1000 print_info(g_log_must_message,l_Message_Text);
1001 end loop;
1002 print_info(g_log_must_message,'');
1003 end if;
1004
1005 x_return_status := fnd_api.g_ret_sts_success;
1006 print_info(g_log_flow_of_control,'remove_grp_of_passed_dep_dt : Program ended');
1007 exception
1008 when others then
1009 x_return_status := fnd_api.g_ret_sts_unexp_error;
1010 print_info(g_log_flow_of_control,'remove_grp_of_passed_dep_dt : Unexpected error ' || to_char(sqlcode) || ':' || SQLERRM);
1011 set_concurrent_status('ERROR',get_seeded_message('MST_REL_BK_MESSAGE_47') || to_char(sqlcode) || ':' || SQLERRM);
1012 end remove_grp_of_passed_dep_dt;
1013
1014 procedure remove_grp_of_exceptions (x_return_status out nocopy varchar2
1015 , p_plan_id in number
1016 , p_release_id in pls_integer
1017 , p_release_mode in number) is
1018
1019 -- cursor to retrieve group_ids in current release
1020 cursor cur_group_trips (l_release_id in pls_integer) is
1021 select group_id, trip_id, trip_number, decode(mode_of_transport,'TRUCK',g_str_truck_tl,'LTL',g_str_ltl_tl,'PARCEL',g_str_parcel_tl,mode_of_transport)
1022 from mst_release_temp_gt
1023 where release_id = l_release_id
1024 and trip_id is not null
1025 order by group_id;
1026
1027 l_group_id_tab number_tab_type;
1028 l_trip_id_tab number_tab_type;
1029 l_trip_number_tab number_tab_type;
1030 l_mode_of_transport_tl_tab varchar2_tab_type;
1031
1032 l_group_id_tobedeleted_tab number_tab_type;
1033
1034 l_previous_group_id pls_integer := -1;
1035
1036 -- cursor to get the release option corresponding to the current trip/cm/delivery's exception
1037 cursor cur_excep (l_plan_id in number, l_trip_id in number)
1038 is
1039 select mep.release_option
1040 from mst_excep_preferences mep
1041 , mst_exception_details med
1042 where mep.user_id = -9999
1043 and mep.release_option = 1
1044 and mep.exception_type = med.exception_type
1045 and med.plan_id = l_plan_id
1046 and (med.trip_id1 = l_trip_id -- trip_id2 is not being checked since
1047 or med.delivery_id in (select mdl.delivery_id -- it can not exists without trip_id1.
1048 from mst_delivery_legs mdl -- continuous_move_id is not being checked
1049 where mdl.plan_id = l_plan_id -- since it is not being populated without trip_id1
1050 and mdl.trip_id = l_trip_id)
1054 and mts.trip_id = l_trip_id));
1051 or med.location_id in (select mts.stop_location_id
1052 from mst_trip_stops mts
1053 where mts.plan_id = l_plan_id
1055
1056 l_release_option number;
1057 l_Message_Text varchar2(1000);
1058 begin
1059 print_info(g_log_flow_of_control,'remove_grp_of_exceptions : Program started');
1060
1061 open cur_group_trips (p_release_id);
1062 fetch cur_group_trips bulk collect into l_group_id_tab, l_trip_id_tab, l_trip_number_tab, l_mode_of_transport_tl_tab;
1063 close cur_group_trips;
1064
1065 if nvl(l_trip_id_tab.last,0) > 0 then
1066 for i in 1..l_trip_id_tab.last loop
1067 if l_previous_group_id <> l_group_id_tab(i) then
1068
1069 open cur_excep(p_plan_id, l_trip_id_tab(i));
1070 fetch cur_excep into l_release_option;
1071 if not cur_excep%notfound then
1072 --if l_release_option = 1 and nvl(p_release_mode,2) = 2 then
1073 -- print_info(g_log_must_message,'Group of trip '||l_trip_number_tab(i)||' is being selected for release but this trip has exception');
1074 --elsif l_release_option = 1 and p_release_mode = 1 then
1075 if p_release_mode = 1 then
1076 l_group_id_tobedeleted_tab(nvl(l_group_id_tobedeleted_tab.last,0) + 1) := l_group_id_tab(i);
1077 l_previous_group_id := l_group_id_tab(i);
1078 --print_info(g_log_must_message,'Group of trip '||l_trip_number_tab(i)||' is not being released as this trip has restricted exception.');
1079 fnd_message.set_name('MST','MST_REL_BK_MESSAGE_33');
1080 fnd_message.set_token('N1',to_char(l_trip_number_tab(i))||' [ '||l_mode_of_transport_tl_tab(i)||' ]');
1081 l_Message_Text := fnd_message.get;
1082 print_info(g_log_must_message,l_Message_Text);
1083 end if;
1084 end if;
1085 close cur_excep;
1086 end if;
1087 end loop;
1088
1089 if nvl(l_group_id_tobedeleted_tab.last,0) > 0 then
1090 print_info(g_log_must_message,'');
1091 -- fail group since its one trip disqualified due to exception
1092 forall i in 1..l_group_id_tobedeleted_tab.last
1093 update mst_release_temp_gt
1094 set planned_flag = -4444
1095 where release_id = p_release_id
1096 and group_id = l_group_id_tobedeleted_tab(i);
1097 end if;
1098 end if;
1099
1100 x_return_status := fnd_api.g_ret_sts_success;
1101 print_info(g_log_flow_of_control,'remove_grp_of_exceptions : Program ended');
1102 exception
1103 when others then
1104 x_return_status := fnd_api.g_ret_sts_unexp_error;
1105 print_info(g_log_flow_of_control,'remove_grp_of_exceptions : Unexpected error ' || to_char(sqlcode) || ':' || SQLERRM);
1106 set_concurrent_status('ERROR',get_seeded_message('MST_REL_BK_MESSAGE_47') || to_char(sqlcode) || ':' || SQLERRM);
1107 end remove_grp_of_exceptions;
1108
1109 /*
1110 procedure remove_grp_of_unwanted_trip (x_return_status out nocopy varchar2
1111 , p_plan_id in number
1112 , p_release_id in pls_integer
1113 , p_release_mode in number) is
1114
1115 -- cursor to retrieve group_ids in current release
1116 cursor cur_group_trips (l_release_id in pls_integer) is
1117 select group_id, trip_id, trip_number, decode(mrt.mode_of_transport,'TRUCK',g_str_truck_tl,'LTL',g_str_ltl_tl,'PARCEL',g_str_parcel_tl,mrt.mode_of_transport)
1118 from mst_release_temp_gt
1119 where release_id = l_release_id
1120 and trip_id is not null
1121 and selected_trips is null;
1122
1123 l_group_id_tab number_tab_type;
1124 l_trip_id_tab number_tab_type;
1125 l_trip_number_tab number_tab_type;
1126 l_mode_of_transport_tl_tab varchar2_tab_type;
1127
1128 l_group_id_tobedeleted_tab number_tab_type;
1129 l_previous_group_id pls_integer := -1;
1130 l_Message_Text varchar2(1000);
1131 begin
1132 print_info(g_log_flow_of_control,'remove_grp_of_unwanted_trip : Program started');
1133
1134 if nvl(p_release_mode,2) = 1 and g_auto_release = 3 and g_where_clause is not null then -- only for auto release
1135 open cur_group_trips (p_release_id);
1136 fetch cur_group_trips bulk collect into l_group_id_tab, l_trip_id_tab, l_trip_number_tab, l_mode_of_transport_tl_tab;
1137 close cur_group_trips;
1138
1139 if nvl(l_trip_id_tab.last,0) > 0 then
1140 for i in 1..l_trip_id_tab.last loop
1141 if l_previous_group_id <> l_group_id_tab(i) then
1142 l_group_id_tobedeleted_tab(nvl(l_group_id_tobedeleted_tab.last,0) + 1) := l_group_id_tab(i);
1143 l_previous_group_id := l_group_id_tab(i);
1144 --print_info(g_log_must_message,'Group of trip '||l_trip_number_tab(i)||' is not being released as this trip does not obey rule set');
1145 fnd_message.set_name('MST','MST_REL_BK_MESSAGE_34');
1146 fnd_message.set_token('N1',to_char(l_trip_number_tab(i))||' [ '||l_mode_of_transport_tl_tab(i)||' ]');
1147 l_Message_Text := fnd_message.get;
1148 print_info(g_log_must_message,l_Message_Text);
1149 end if;
1150 end loop;
1151
1152 if nvl(l_group_id_tobedeleted_tab.last,0) > 0 then
1153 print_info(g_log_must_message,'');
1154 -- fail group since its one trip disqualified due to rule set
1155 forall i in 1..l_group_id_tobedeleted_tab.last
1156 update mst_release_temp_gt
1157 set planned_flag = -5555
1158 where release_id = p_release_id
1159 and group_id = l_group_id_tobedeleted_tab(i);
1160 end if;
1161 end if;
1162 end if;
1163
1164 x_return_status := fnd_api.g_ret_sts_success;
1168 x_return_status := fnd_api.g_ret_sts_unexp_error;
1165 print_info(g_log_flow_of_control,'remove_grp_of_unwanted_trip : Program ended');
1166 exception
1167 when others then
1169 print_info(g_log_flow_of_control,'remove_grp_of_unwanted_trip : Unexpected error ' || to_char(sqlcode) || ':' || SQLERRM);
1170 set_concurrent_status('ERROR',get_seeded_message('MST_REL_BK_MESSAGE_47') || to_char(sqlcode) || ':' || SQLERRM);
1171 end remove_grp_of_unwanted_trip;
1172 */
1173
1174 procedure reset_grp_ids_in_sequence_of_1(x_return_status out nocopy varchar2
1175 , p_group_tab out nocopy wsh_tp_release_grp.id_tab_type
1176 , p_plan_id in pls_integer
1177 , p_release_id in pls_integer) is
1178 l_count pls_integer;
1179 l_group_id pls_integer;
1180
1181 cursor cur_groups (l_release_id in pls_integer, l_plan_id in pls_integer)
1182 is
1183 select distinct group_id
1184 from mst_release_temp_gt
1185 where release_id = l_release_id
1186 and plan_id = l_plan_id
1187 and trip_id is not null
1188 and group_id is not null
1189 order by group_id;
1190
1191 begin
1192 print_info(g_log_flow_of_control,'reset_grp_ids_in_sequence_of_1 : Program started');
1193
1194 -- populate l_group_tab with the distinct group_id in mst_release_temp_gt
1195 open cur_groups (p_release_id, p_plan_id);
1196 fetch cur_groups bulk collect into p_group_tab;
1197 close cur_groups;
1198
1199 -- reset group_id such that it could be in a sequence with difference of 1 (demanded by william)
1200 l_count := nvl(p_group_tab.last,0);
1201
1202 loop
1203 if l_count < 2 then
1204 exit;
1205 end if;
1206 l_group_id := p_group_tab(l_count);
1207 l_count := l_count - 1;
1208 if l_group_id - p_group_tab(l_count) > 1 then
1209 update mst_release_temp_gt
1210 set group_id = l_group_id-1
1211 where release_id = p_release_id
1212 and group_id = p_group_tab(l_count);
1213
1214 p_group_tab(l_count) := l_group_id-1;
1215
1216 end if;
1217 end loop;
1218
1219 x_return_status := fnd_api.g_ret_sts_success;
1220 print_info(g_log_flow_of_control,'reset_grp_ids_in_sequence_of_1 : Program ended');
1221 exception
1222 when others then
1223 x_return_status := fnd_api.g_ret_sts_unexp_error;
1224 print_info(g_log_flow_of_control,'reset_grp_ids_in_sequence_of_1 : Unexpected error ' || to_char(sqlcode) || ':' || SQLERRM);
1225 set_concurrent_status('ERROR',get_seeded_message('MST_REL_BK_MESSAGE_47') || to_char(sqlcode) || ':' || SQLERRM);
1226 end reset_grp_ids_in_sequence_of_1;
1227
1228 procedure populate_deliveries (x_return_status out nocopy varchar2
1229 , p_release_id in pls_integer) is
1230 begin
1231 print_info(g_log_flow_of_control,'populate_deliveries : Program started');
1232
1233 -- insert deliveries corresponding to the trips being released into permanent temporary table
1234 insert into mst_release_temp_gt
1235 (
1236 release_id
1237 , group_id
1238 , plan_id
1239 , planned_flag
1240 , delivery_id
1241 , sr_delivery_id
1242 , out_of_scope
1243 -- , delivery_id_iface
1244 , status_code
1245 , pickup_date
1246 , dropoff_date
1247 , pickup_location_id
1248 , dropoff_location_id
1249 , customer_id
1250 , gross_weight
1251 , net_weight
1252 , weight_uom
1253 , volume
1254 , volume_uom
1255 , currency_uom
1256 , organization_id
1257 , shipment_direction
1258 , delivery_number
1259 , compile_designator
1260 , earliest_pickup_date
1261 , latest_pickup_date
1262 , earliest_acceptable_date
1263 , latest_acceptable_date
1264 , supplier_id
1265 , party_id
1266 )
1267 (
1268 select DISTINCT mrt.release_id
1269 , mrt.group_id
1270 , mrt.plan_id
1271 , DECODE(md.planned_flag, 1, 1
1272 , 2, decode(md.preserve_grouping_flag, 1, 2
1273 , 2, 3
1274 , 3
1275 )
1276 , 3
1277 )
1278 , md.delivery_id
1279 , md.sr_delivery_id
1280 , md.out_of_scope
1281 -- , wsh_new_del_interface_s.nextval
1282 , nvl(md.status_code,1) status_code
1283 , md.pickup_date
1284 , md.dropoff_date
1285 , md.pickup_location_id
1286 , md.dropoff_location_id
1287 , md.customer_id
1288 , md.gross_weight
1289 , md.net_weight
1290 , mp.weight_uom
1291 , md.volume
1292 , mp.volume_uom
1293 , mp.currency_uom
1294 , nvl(md.organization_id,mst_wb_util.get_org_id(md.plan_id,md.delivery_id))
1295 , decode(md.shipment_direction ,1,'I',2,'O',3,'D',4,'IO','O')
1296 , md.delivery_number
1297 , mp.compile_designator
1298 , md.earliest_pickup_date
1299 , md.latest_pickup_date
1300 , md.earliest_acceptable_date
1301 , md.latest_acceptable_date
1302 , md.supplier_id
1303 , hzr.object_id
1304 from mst_release_temp_gt mrt
1305 , mst_deliveries md
1306 , mst_delivery_legs mdl
1307 , mst_plans mp
1308 , hz_relationships hzr
1309 where mrt.release_id = p_release_id
1310 and mrt.plan_id = mdl.plan_id
1311 and mrt.trip_id = mdl.trip_id
1312 and mdl.plan_id = md.plan_id
1313 and mdl.delivery_id = md.delivery_id
1317 and hzr.object_type (+) = 'POS_VENDOR'
1314 and md.plan_id = mp.plan_id
1315 and hzr.relationship_type (+) = 'POS_VENDOR_PARTY'
1316 and hzr.object_table_name (+) = 'PO_VENDORS'
1318 and hzr.subject_table_name (+) = 'HZ_PARTIES'
1319 and hzr.subject_type (+) = 'ORGANIZATION'
1320 and hzr.status (+) = 'A'
1321 and hzr.subject_id (+) = md.supplier_id
1322 );
1323
1324 -- update delivery_id_iface and planned_flag for deliveries in mst_release_temp_gt
1325 UPDATE mst_release_temp_gt mrt
1326 SET mrt.delivery_id_iface = wsh_new_del_interface_s.nextval
1327 WHERE mrt.release_id = p_release_id
1328 AND mrt.delivery_id IS NOT NULL;
1329
1330 -- update planned_flag for deliveries of RCF trips
1331 UPDATE mst_release_temp_gt mrt
1332 SET mrt.planned_flag = 1
1333 WHERE mrt.release_id = p_release_id
1334 AND mrt.delivery_id IN (SELECT mdl.delivery_id
1335 FROM mst_delivery_legs mdl
1336 , mst_release_temp_gt mrt1
1337 WHERE mdl.plan_id = mrt1.plan_id
1338 AND mdl.trip_id = mrt1.trip_id
1339 AND mrt1.release_id = p_release_id
1340 AND mrt1.trip_id IS NOT NULL
1341 AND mrt1.planned_flag = 1);
1342
1343 x_return_status := fnd_api.g_ret_sts_success;
1344 print_info(g_log_flow_of_control,'populate_deliveries : Program ended');
1345 exception
1346 when others then
1347 x_return_status := fnd_api.g_ret_sts_unexp_error;
1348 print_info(g_log_flow_of_control,'populate_deliveries : Unexpected error ' || to_char(sqlcode) || ':' || SQLERRM);
1349 set_concurrent_status('ERROR',get_seeded_message('MST_REL_BK_MESSAGE_47') || to_char(sqlcode) || ':' || SQLERRM);
1350 end populate_deliveries;
1351
1352 procedure update_ids_in_wdd_iface_tab (p_release_id in pls_integer) is
1353 cursor cur_wdd_ids (l_release_id in pls_integer)
1354 is
1355 select wdd_iface.delivery_detail_interface_id, min(wdd.source_header_id), min(wdd.source_line_id), min(wdd.source_line_set_id)
1356 from wsh_delivery_details wdd
1357 , mst_release_temp_gt mrt
1358 , wsh_del_details_interface wdd_iface
1359 where wdd.source_code = wdd_iface.source_code
1360 and wdd.source_header_number = wdd_iface.source_header_number
1361 and substr(wdd.source_line_number,1,instr(wdd.source_line_number||'.','.',1,1)-1) = substr(wdd_iface.source_line_number,1,instr(wdd_iface.source_line_number||'.','.',1,1)-1)
1362 and (wdd_iface.source_header_id is null or wdd_iface.source_line_id = FND_API.G_MISS_NUM or wdd_iface.source_line_set_id is null)
1363 and wdd_iface.delivery_detail_interface_id = mrt.delivery_detail_id_iface
1364 and mrt.release_id = l_release_id
1365 group by wdd_iface.delivery_detail_interface_id;
1366
1367 l_delivery_detail_iface_id_tab number_tab_type;
1368 l_source_header_id_tab number_tab_type;
1369 l_source_line_id_tab number_tab_type;
1370 l_source_line_set_id_tab number_tab_type;
1371 begin
1372 print_info(g_log_flow_of_control,'update_ids_in_wdd_iface_tab : Program started');
1373 open cur_wdd_ids (p_release_id);
1374 fetch cur_wdd_ids bulk collect into l_delivery_detail_iface_id_tab, l_source_header_id_tab, l_source_line_id_tab, l_source_line_set_id_tab;
1375 close cur_wdd_ids;
1376
1377 if nvl(l_delivery_detail_iface_id_tab.last,0) > 0 then
1378 forall i in 1..l_delivery_detail_iface_id_tab.last
1379 update wsh_del_details_interface
1380 set source_header_id = l_source_header_id_tab(i)
1381 , source_line_id = l_source_line_id_tab(i)
1382 , source_line_set_id = l_source_line_set_id_tab(i)
1383 where delivery_detail_interface_id = l_delivery_detail_iface_id_tab(i);
1384 end if;
1385 print_info(g_log_flow_of_control,'update_ids_in_wdd_iface_tab : Program ended');
1386 end update_ids_in_wdd_iface_tab;
1387
1388 procedure update_loc_id_in_iface_tab (p_release_id in pls_integer) is
1389 cursor cur_wdd_loc_ids (l_release_id in pls_integer)
1390 is
1391 select wda_iface.delivery_interface_id, wdd_iface.delivery_detail_interface_id, mplav.cust_location_id
1392 from mst_po_location_asso_v mplav
1393 , wsh_del_details_interface wdd_iface
1394 , wsh_del_assgn_interface wda_iface
1395 , wsh_delivery_details wdd
1396 , mst_release_temp_gt mrt
1397 where mplav.location_id = wdd_iface.ship_to_location_id
1398 and wdd_iface.delivery_detail_id = wdd.delivery_detail_id
1399 and wdd.ship_to_location_id <> wdd_iface.ship_to_location_id
1400 and wda_iface.delivery_detail_interface_id = wdd_iface.delivery_detail_interface_id
1401 and wdd_iface.delivery_detail_interface_id = mrt.delivery_detail_id_iface
1402 and mrt.release_id = l_release_id;
1403
1404 l_delivery_iface_id_tab number_tab_type;
1405 l_delivery_detail_iface_id_tab number_tab_type;
1406 l_ship_to_location_id_tab number_tab_type;
1407 begin
1408 print_info(g_log_flow_of_control,'update_loc_id_in_wdd_iface_tab : Program started');
1409 open cur_wdd_loc_ids (p_release_id);
1410 fetch cur_wdd_loc_ids bulk collect into l_delivery_iface_id_tab, l_delivery_detail_iface_id_tab, l_ship_to_location_id_tab;
1411 close cur_wdd_loc_ids;
1412
1413 if nvl(l_delivery_iface_id_tab.last,0) > 0 then
1414 forall i in 1..l_delivery_iface_id_tab.last
1415 update wsh_new_del_interface
1416 set ultimate_dropoff_location_id = l_ship_to_location_id_tab(i)
1417 where delivery_interface_id = l_delivery_iface_id_tab(i);
1418 end if;
1419
1420 if nvl(l_delivery_detail_iface_id_tab.last,0) > 0 then
1421 forall i in 1..l_delivery_detail_iface_id_tab.last
1422 update wsh_del_details_interface
1423 set ship_to_location_id = l_ship_to_location_id_tab(i)
1424 where delivery_detail_interface_id = l_delivery_detail_iface_id_tab(i);
1425 end if;
1429 procedure populate_interface_tables(x_return_status out nocopy varchar2
1426 print_info(g_log_flow_of_control,'update_loc_id_in_wdd_iface_tab : Program ended');
1427 end update_loc_id_in_iface_tab;
1428
1430 , p_release_id in pls_integer) is
1431 l_date date := sysdate;
1432 l_user number := fnd_global.user_id;
1433 begin
1434 print_info(g_log_flow_of_control,'populate_interface_tables : Program started');
1435
1436 insert into wsh_trips_interface
1437 ( trip_interface_id
1438 , trip_id
1439 , planned_flag
1440 , status_code
1441 , vehicle_item_id
1442 , vehicle_organization_id
1443 , carrier_id
1444 , ship_method_code
1445 , interface_action_code
1446 , tp_plan_name
1447 , tp_trip_number
1448 , group_id
1449 , mode_of_transport
1450 , load_tender_status
1451 , lane_id
1452 , service_level
1453 , creation_date
1454 , created_by
1455 , last_update_date
1456 , last_updated_by
1457 , last_update_login
1458 )
1459 (
1460 select mrt.trip_id_iface
1461 , mrt.sr_trip_id
1462 , decode(mrt.planned_flag,1,'F',2,'Y',3,'N','N')
1463 , decode(mrt.status_code,1,'OP',2,'IT',3,'CL','OP')
1464 , mrt.inventory_item_id
1465 , mrt.organization_id
1466 , mrt.carrier_id
1467 , mrt.ship_method_code
1468 , g_tp_release_code
1469 , mrt.compile_designator
1470 , mrt.trip_number
1471 , mrt.group_id
1472 , mrt.mode_of_transport
1473 , mrt.load_tender_status
1474 , mrt.lane_id
1475 , mrt.service_level
1476 , l_date
1477 , l_user
1478 , l_date
1479 , l_user
1480 , l_user
1481 from mst_release_temp_gt mrt
1482 where mrt.release_id = p_release_id
1483 and mrt.trip_id is not null
1484 );
1485
1486 insert into fte_moves_interface
1487 ( move_interface_id
1488 , move_id
1489 , lane_id
1490 , service_level
1491 , planned_flag
1492 , tp_plan_name
1493 , cm_trip_number
1494 , interface_action_code
1495 , creation_date
1496 , created_by
1497 , last_update_date
1498 , last_updated_by
1499 , last_update_login
1500 )
1501 (
1502 select mrt.continuous_move_id_iface
1503 , mrt.sr_cm_trip_id
1504 , mrt.lane_id
1505 , mrt.service_level
1506 , decode(mrt.planned_flag,1,'Y',2,'N','N')
1507 , mrt.compile_designator
1508 , mrt.cm_trip_number
1509 , g_tp_release_code
1510 , l_date
1511 , l_user
1512 , l_date
1513 , l_user
1514 , l_user
1515 from mst_release_temp_gt mrt
1516 where mrt.release_id = p_release_id
1517 and mrt.continuous_move_id is not null
1518 );
1519
1520 insert into fte_trip_moves_interface
1521 ( trip_move_interface_id
1522 , trip_move_id
1523 , move_interface_id
1524 , move_id
1525 , trip_interface_id
1526 , trip_id
1527 , sequence_number
1528 , interface_action_code
1529 , creation_date
1530 , created_by
1531 , last_update_date
1532 , last_updated_by
1533 , last_update_login
1534 )
1535 (
1536 select fte_trip_moves_interface_s.nextval
1537 , null
1538 , mrt1.continuous_move_id_iface
1539 , mrt1.sr_cm_trip_id
1540 , mrt2.trip_id_iface
1541 , mrt2.sr_trip_id
1542 , mrt2.continuous_move_sequence
1543 , g_tp_release_code
1544 , l_date
1545 , fnd_global.user_id
1546 , l_date
1547 , l_user
1548 , l_user
1549 from mst_release_temp_gt mrt1
1550 , mst_release_temp_gt mrt2
1551 where mrt1.release_id = mrt2.release_id
1552 and mrt1.continuous_move_id = mrt2.cm_id_of_trip
1553 and mrt2.release_id = p_release_id
1554 );
1555
1556 --generate interface ids for stops
1557 insert into mst_release_temp_gt
1558 (
1559 release_id
1560 , group_id
1561 , plan_id
1562 , stop_id
1563 , trip_id_iface
1564 , stop_id_iface
1565 , sr_trip_id
1566 , status_code
1567 , sr_stop_id
1568 , stop_location_id
1569 , stop_sequence_number
1570 , planned_arrival_date
1571 , planned_departure_date
1572 , departure_gross_weight
1573 , departure_net_weight
1574 , weight_uom
1575 , departure_volume
1576 , volume_uom
1577 , departure_fill_percent
1578 , wkend_layover_stops
1579 , wkday_layover_stops
1580 , pln_loading_start_time
1581 , pln_loading_end_time
1582 , pln_unloading_start_time
1583 , pln_unloading_end_time
1584 )
1585 (
1586 select p_release_id
1587 , mrt.group_id
1588 , mts.plan_id
1589 , mts.stop_id
1590 , mrt.trip_id_iface
1591 , wsh_trip_stops_interface_s.nextval
1592 , mrt.sr_trip_id
1593 , mrt.status_code
1594 , mts.sr_stop_id
1595 , mts.stop_location_id
1596 , mts.stop_sequence_number
1597 , mts.planned_arrival_date
1598 , mts.planned_departure_date
1599 , mts.departure_gross_weight
1600 , mts.departure_net_weight
1601 , mp.weight_uom
1602 , mts.departure_volume
1603 , mp.volume_uom
1604 , mts.departure_fill_percent
1605 , mts.wkend_layover_stops
1606 , mts.wkday_layover_stops
1607 , mts.pln_loading_start_time
1608 , mts.pln_loading_end_time
1609 , mts.pln_unloading_start_time
1610 , mts.pln_unloading_end_time
1614 where mrt.release_id = p_release_id
1611 from mst_plans mp
1612 , mst_trip_stops mts
1613 , mst_release_temp_gt mrt
1615 and mrt.plan_id = mts.plan_id
1616 and mrt.trip_id = mts.trip_id
1617 and mrt.plan_id = mp.plan_id
1618 );
1619
1620 insert into wsh_trip_stops_interface
1621 ( stop_interface_id
1622 , stop_id
1623 , trip_id
1624 , tp_stop_id
1625 , trip_interface_id
1626 , stop_location_id
1627 , status_code
1628 , stop_sequence_number
1629 , planned_arrival_date
1630 , planned_departure_date
1631 , departure_gross_weight
1632 , departure_net_weight
1633 , weight_uom_code
1634 , departure_volume
1635 , volume_uom_code
1636 , departure_fill_percent
1637 , wkend_layover_stops
1638 , wkday_layover_stops
1639 , loading_start_datetime
1640 , loading_end_datetime
1641 , unloading_start_datetime
1642 , unloading_end_datetime
1643 , interface_action_code
1644 , creation_date
1645 , created_by
1646 , last_update_date
1647 , last_updated_by
1648 , last_update_login
1649 )
1650 (
1651 select mrt.stop_id_iface
1652 , mrt.sr_stop_id
1653 , mrt.sr_trip_id
1654 , mrt.stop_id
1655 , mrt.trip_id_iface
1656 , mrt.stop_location_id
1657 , decode(mrt.status_code,1,'OP',2,'IT',3,'CL','OP')
1658 , mrt.stop_sequence_number
1659 , mrt.planned_arrival_date
1660 , mrt.planned_departure_date
1661 , mrt.departure_gross_weight
1662 , mrt.departure_net_weight
1663 , mrt.weight_uom
1664 , mrt.departure_volume
1665 , mrt.volume_uom
1666 , mrt.departure_fill_percent
1667 , mrt.wkend_layover_stops
1668 , mrt.wkday_layover_stops
1669 , mrt.pln_loading_start_time
1670 , mrt.pln_loading_end_time
1671 , mrt.pln_unloading_start_time
1672 , mrt.pln_unloading_end_time
1673 , g_tp_release_code
1674 , l_date
1675 , fnd_global.user_id
1676 , l_date
1677 , l_user
1678 , l_user
1679 from mst_release_temp_gt mrt
1680 where mrt.release_id = p_release_id
1681 and mrt.stop_id is not null
1682 );
1683
1684 insert into wsh_new_del_interface
1685 ( delivery_interface_id
1686 , delivery_id
1687 , planned_flag
1688 , status_code
1689 , initial_pickup_date
1690 , initial_pickup_location_id
1691 , ultimate_dropoff_location_id
1692 , ultimate_dropoff_date
1693 , customer_id
1694 , gross_weight
1695 , net_weight
1696 , weight_uom_code
1697 , volume
1698 , volume_uom_code
1699 , currency_code
1700 , organization_id
1701 , shipment_direction
1702 , tp_delivery_number
1703 , tp_plan_name
1704 , earliest_pickup_date
1705 , latest_pickup_date
1706 , earliest_dropoff_date
1707 , latest_dropoff_date
1708 , delivery_type
1709 , interface_action_code
1710 , creation_date
1711 , created_by
1712 , last_update_date
1713 , last_updated_by
1714 , last_update_login
1715 )
1716 (
1717 select mrt.delivery_id_iface
1718 , mrt.sr_delivery_id
1719 , decode(mrt.planned_flag,1,'F',2,'Y',3,'N','N')
1720 , decode(mrt.status_code,1,'OP',2,'IT',3,'CL','OP')
1721 , mrt.pickup_date
1722 , mrt.pickup_location_id
1723 , mrt.dropoff_location_id
1724 , mrt.dropoff_date
1725 , mrt.customer_id
1726 , mrt.gross_weight
1727 , mrt.net_weight
1728 , mrt.weight_uom
1729 , mrt.volume
1730 , mrt.volume_uom
1731 , mrt.currency_uom
1732 , mrt.organization_id
1733 , mrt.shipment_direction
1734 , mrt.delivery_number
1735 , mrt.compile_designator
1736 , mrt.earliest_pickup_date
1737 , mrt.latest_pickup_date
1738 , mrt.earliest_acceptable_date
1739 , mrt.latest_acceptable_date
1740 , 'STANDARD'
1741 , g_tp_release_code
1742 , l_date
1743 , l_user
1744 , l_date
1745 , l_user
1746 , l_user
1747 from mst_release_temp_gt mrt
1748 where mrt.release_id = p_release_id
1749 and mrt.delivery_id is not null
1750 );
1751
1752 insert into wsh_del_legs_interface
1753 ( delivery_leg_interface_id
1754 , delivery_leg_id
1755 , delivery_id
1756 , delivery_interface_id
1757 , sequence_number
1758 , pick_up_stop_id
1759 , pick_up_stop_interface_id
1760 , drop_off_stop_id
1761 , drop_off_stop_interface_id
1762 , gross_weight
1763 , net_weight
1764 , weight_uom_code
1765 , volume
1766 , volume_uom_code
1767 , interface_action_code
1768 , creation_date
1769 , created_by
1770 , last_update_date
1771 , last_updated_by
1772 , last_update_login
1773 )
1774 (
1775 select wsh_del_legs_interface_s.nextval
1776 , mdl.sr_delivery_leg_id
1777 , mrt1.sr_delivery_id
1778 , mrt1.delivery_id_iface
1779 , mdl.sequence_number
1780 , mrt2.sr_stop_id
1781 , mrt2.stop_id_iface
1782 , mrt3.sr_stop_id
1783 , mrt3.stop_id_iface
1784 , mrt1.gross_weight
1785 , mrt1.net_weight
1786 , mrt1.weight_uom
1787 , mrt1.volume
1788 , mrt1.volume_uom
1789 , g_tp_release_code
1790 , l_date
1791 , l_user
1792 , l_date
1793 , l_user
1794 , l_user
1795 from mst_release_temp_gt mrt1
1796 , mst_release_temp_gt mrt2
1797 , mst_release_temp_gt mrt3
1798 , mst_delivery_legs mdl
1799 where mrt1.plan_id= mdl.plan_id
1800 and mrt1.delivery_id = mdl.delivery_id
1801 and mrt1.release_id = p_release_id
1802 and mrt2.stop_id = mdl.pick_up_stop_id
1803 and mrt2.release_id = p_release_id
1804 and mrt3.stop_id = mdl.drop_off_stop_id
1805 and mrt3.release_id = p_release_id
1806 );
1807
1808 --inserting the splitted TE lines
1809 insert into mst_release_temp_gt
1810 ( release_id
1811 , group_id
1812 , plan_id
1813 , sr_delivery_id
1814 , delivery_id_iface
1815 , sr_delivery_assignment_id
1816 , delivery_detail_id
1817 , delivery_detail_id_iface
1818 , sr_delivery_detail_id
1819 , source_code
1820 , customer_id
1821 , inventory_item_id
1822 , ship_from_location_id
1823 , ship_to_location_id
1824 , requested_quantity
1825 , gross_weight
1826 , net_weight
1827 , weight_uom
1828 , volume
1829 , volume_uom
1830 , source_header_number
1831 , ship_set_id
1832 , arrival_set_id
1833 , organization_id
1834 , org_id
1835 , container_flag
1836 , source_line_number
1837 , split_from_delivery_detail_id
1838 , earliest_pickup_date
1839 , latest_pickup_date
1840 , earliest_acceptable_date
1841 , latest_acceptable_date
1842 , line_direction
1843 , po_shipment_line_number
1844 , src_requested_quantity
1845 , src_requested_quantity_uom
1846 , supplier_id
1847 , party_id
1848 )
1849 (
1850 select p_release_id
1851 , mrt.group_id
1852 , mrt.plan_id
1853 , mrt.sr_delivery_id
1854 , mrt.delivery_id_iface
1855 , mda.sr_delivery_assignment_id
1856 , mda.delivery_detail_id
1857 , wsh_del_details_interface_s.nextval
1858 , mdd1.sr_delivery_detail_id
1859 , mdd.source_code
1860 , mdd.customer_id
1861 , mdd.inventory_item_id
1862 , mdd.ship_from_location_id
1863 , mdd.ship_to_location_id
1864 , mdd.requested_quantity
1865 , mdd.gross_weight
1866 , mdd.net_weight
1867 , mp.weight_uom
1868 , mdd.volume
1869 , mp.volume_uom
1870 , mdd.source_header_number
1871 , mdd.ship_set_id
1872 , mdd.arrival_set_id
1873 , mdd.organization_id
1874 , mdd.org_id
1875 , mdd.container_flag
1876 , mdd.source_line_number
1877 , mdd.split_from_delivery_detail_id
1878 , mdd.earliest_pickup_date
1879 , mdd.latest_pickup_date
1880 , mdd.earliest_acceptable_date
1881 , mdd.latest_acceptable_date
1882 , mdd.line_direction
1883 , mdd.po_shipment_line_number
1884 , mdd.src_requested_quantity
1885 , mdd.src_requested_quantity_uom
1886 , mdd.supplier_id
1887 , hzr.object_id
1888 from mst_plans mp
1889 , mst_release_temp_gt mrt
1890 , mst_delivery_assignments mda
1891 , mst_delivery_details mdd
1892 , mst_delivery_details mdd1
1893 , hz_relationships hzr
1894 where mrt.plan_id = mp.plan_id
1895 and mrt.plan_id = mda.plan_id
1896 and mrt.delivery_id = mda.delivery_id
1897 and mda.plan_id = mdd.plan_id
1898 and mda.delivery_detail_id = mdd.delivery_detail_id
1899 and mdd.plan_id = mdd1.plan_id
1900 and mdd.split_from_delivery_detail_id = mdd1.delivery_detail_id
1901 and mdd.split_from_delivery_detail_id is not null
1902 and mrt.release_id = p_release_id
1903 and hzr.relationship_type (+) = 'POS_VENDOR_PARTY'
1904 and hzr.object_table_name (+) = 'PO_VENDORS'
1905 and hzr.object_type (+) = 'POS_VENDOR'
1906 and hzr.subject_table_name (+) = 'HZ_PARTIES'
1907 and hzr.subject_type (+) = 'ORGANIZATION'
1908 and hzr.status (+) = 'A'
1909 and hzr.subject_id (+) = mdd.supplier_id
1910 );
1911
1912 --inserting the unsplitted TE lines
1913 insert into mst_release_temp_gt
1914 ( release_id
1915 , group_id
1916 , plan_id
1917 , sr_delivery_id
1918 , delivery_id_iface
1919 , sr_delivery_assignment_id
1920 , delivery_detail_id
1921 , delivery_detail_id_iface
1922 , sr_delivery_detail_id
1923 , source_code
1924 , customer_id
1925 , inventory_item_id
1926 , ship_from_location_id
1927 , ship_to_location_id
1928 , requested_quantity
1929 , gross_weight
1930 , net_weight
1931 , weight_uom
1932 , volume
1933 , volume_uom
1934 , source_header_number
1935 , ship_set_id
1936 , arrival_set_id
1937 , organization_id
1938 , org_id
1939 , container_flag
1940 , source_line_number
1941 , split_from_delivery_detail_id
1942 , earliest_pickup_date
1943 , latest_pickup_date
1944 , earliest_acceptable_date
1945 , latest_acceptable_date
1946 , line_direction
1947 , po_shipment_line_number
1948 , src_requested_quantity
1949 , src_requested_quantity_uom
1950 , supplier_id
1951 , party_id
1952 )
1953 (
1954 select p_release_id
1955 , mrt.group_id
1956 , mrt.plan_id
1957 , mrt.sr_delivery_id
1958 , mrt.delivery_id_iface
1959 , mda.sr_delivery_assignment_id
1960 , mda.delivery_detail_id
1961 , wsh_del_details_interface_s.nextval
1962 , mdd.sr_delivery_detail_id
1963 , mdd.source_code
1964 , mdd.customer_id
1965 , mdd.inventory_item_id
1966 , mdd.ship_from_location_id
1967 , mdd.ship_to_location_id
1968 , mdd.requested_quantity
1969 , mdd.gross_weight
1970 , mdd.net_weight
1971 , mp.weight_uom
1972 , mdd.volume
1973 , mp.volume_uom
1974 , mdd.source_header_number
1975 , mdd.ship_set_id
1976 , mdd.arrival_set_id
1977 , mdd.organization_id
1978 , mdd.org_id
1979 , mdd.container_flag
1980 , mdd.source_line_number
1981 , mdd.split_from_delivery_detail_id
1982 , mdd.earliest_pickup_date
1983 , mdd.latest_pickup_date
1984 , mdd.earliest_acceptable_date
1985 , mdd.latest_acceptable_date
1986 , mdd.line_direction
1987 , mdd.po_shipment_line_number
1988 , mdd.src_requested_quantity
1992 from mst_plans mp
1989 , mdd.src_requested_quantity_uom
1990 , mdd.supplier_id
1991 , hzr.object_id
1993 , mst_release_temp_gt mrt
1994 , mst_delivery_assignments mda
1995 , mst_delivery_details mdd
1996 , hz_relationships hzr
1997 where mrt.plan_id = mp.plan_id
1998 and mrt.plan_id = mda.plan_id
1999 and mrt.delivery_id = mda.delivery_id
2000 and mda.plan_id = mdd.plan_id
2001 and mda.delivery_detail_id = mdd.delivery_detail_id
2002 and mdd.split_from_delivery_detail_id is null
2003 and mrt.release_id = p_release_id
2004 and hzr.relationship_type (+) = 'POS_VENDOR_PARTY'
2005 and hzr.object_table_name (+) = 'PO_VENDORS'
2006 and hzr.object_type (+) = 'POS_VENDOR'
2007 and hzr.subject_table_name (+) = 'HZ_PARTIES'
2008 and hzr.subject_type (+) = 'ORGANIZATION'
2009 and hzr.status (+) = 'A'
2010 and hzr.subject_id (+) = mdd.supplier_id
2011 );
2012
2013 insert into wsh_del_assgn_interface
2014 ( del_assgn_interface_id
2015 , delivery_assignment_id
2016 , delivery_interface_id
2017 , delivery_id
2018 , delivery_detail_interface_id
2019 , delivery_detail_id
2020 , interface_action_code
2021 , creation_date
2022 , created_by
2023 , last_update_date
2024 , last_updated_by
2025 , last_update_login
2026 )
2027 (
2028 select wsh_del_assgn_interface_s.nextval
2029 , null -- mrt.sr_delivery_assignment_id commented as not need in TE (william)
2030 , mrt.delivery_id_iface
2031 , mrt.sr_delivery_id
2032 , mrt.delivery_detail_id_iface
2033 , mrt.sr_delivery_detail_id
2034 , g_tp_release_code
2035 , l_date
2036 , l_user
2037 , l_date
2038 , l_user
2039 , l_user
2040 from mst_release_temp_gt mrt
2041 where mrt.release_id = p_release_id
2042 and mrt.delivery_detail_id_iface is not null
2043 );
2044
2045 insert into wsh_del_details_interface
2046 ( delivery_detail_interface_id
2047 , delivery_detail_id
2048 , source_code
2049 , customer_id
2050 , inventory_item_id
2051 , ship_from_location_id
2052 , ship_to_location_id
2053 , requested_quantity
2054 , gross_weight
2055 , net_weight
2056 , weight_uom_code
2057 , volume
2058 , volume_uom_code
2059 , source_header_number
2060 , ship_set_id
2061 , arrival_set_id
2062 , organization_id
2063 , org_id
2064 , source_line_id
2065 , container_flag
2066 , source_line_number
2067 , split_from_delivery_detail_id
2068 , earliest_pickup_date
2069 , latest_pickup_date
2070 , earliest_dropoff_date
2071 , latest_dropoff_date
2072 , tp_delivery_detail_id
2073 , line_direction
2074 , po_shipment_line_number
2075 , requested_quantity_uom
2076 , source_header_id
2077 , source_line_set_id
2078 , src_requested_quantity
2079 , src_requested_quantity_uom
2080 , requested_quantity2
2081 , requested_quantity_uom2
2082 , src_requested_quantity2
2083 , src_requested_quantity_uom2
2084 , interface_action_code
2085 , creation_date
2086 , created_by
2087 , last_update_date
2088 , last_updated_by
2089 , last_update_login
2090 )
2091 (
2092 select mrt.delivery_detail_id_iface --wsh_del_details_interface_s.nextval
2093 , mrt.sr_delivery_detail_id
2094 , mrt.source_code
2095 , mrt.customer_id
2096 , mrt.inventory_item_id
2097 , mrt.ship_from_location_id
2098 , mrt.ship_to_location_id
2099 , mrt.requested_quantity
2100 , mrt.gross_weight
2101 , mrt.net_weight
2102 , mrt.weight_uom
2103 , mrt.volume
2104 , mrt.volume_uom
2105 , mrt.source_header_number
2106 , mrt.ship_set_id
2107 , mrt.arrival_set_id
2108 , mrt.organization_id
2109 , mrt.org_id
2110 , nvl(wdd.source_line_id,FND_API.G_MISS_NUM)
2111 , decode(mrt.container_flag,1,'Y','N')
2112 , mrt.source_line_number
2113 , mrt.split_from_delivery_detail_id
2114 , mrt.earliest_pickup_date
2115 , mrt.latest_pickup_date
2116 , mrt.earliest_acceptable_date
2117 , mrt.latest_acceptable_date
2118 , mrt.delivery_detail_id
2119 , decode(mrt.line_direction,1,'I',2,'O',3,'D',4,'IO','O')
2120 , mrt.po_shipment_line_number
2121 , wdd.requested_quantity_uom
2122 , wdd.source_header_id
2123 , wdd.source_line_set_id
2124 , mrt.src_requested_quantity
2125 , mrt.src_requested_quantity_uom
2126 , wdd.requested_quantity2
2127 , wdd.requested_quantity_uom2
2128 , wdd.src_requested_quantity2
2129 , wdd.src_requested_quantity_uom2
2130 , g_tp_release_code
2131 , l_date
2132 , l_user
2133 , l_date
2134 , l_user
2135 , l_user
2136 from mst_release_temp_gt mrt
2137 , wsh_delivery_details wdd
2138 where mrt.release_id = p_release_id
2139 and mrt.sr_delivery_detail_id = wdd.delivery_detail_id (+)
2140 and mrt.delivery_detail_id is not null
2141 );
2142
2143 update_ids_in_wdd_iface_tab (p_release_id);
2144 update_loc_id_in_iface_tab (p_release_id);
2145
2146 x_return_status := fnd_api.g_ret_sts_success;
2147 print_info(g_log_flow_of_control,'populate_interface_tables : Program ended');
2148 exception
2149 when others then
2150 x_return_status := fnd_api.g_ret_sts_unexp_error;
2151 print_info(g_log_flow_of_control,'populate_interface_tables : Unexpected error ' || to_char(sqlcode) || ':' || SQLERRM);
2152 set_concurrent_status('ERROR',get_seeded_message('MST_REL_BK_MESSAGE_47') || to_char(sqlcode) || ':' || SQLERRM);
2153 end populate_interface_tables;
2154
2155 procedure update_mst_cm_trips (x_return_status out nocopy varchar2
2156 , p_group_id in pls_integer
2157 , p_plan_id in number
2158 , p_release_id in pls_integer
2159 , p_release_mode in number
2160 , p_date in date
2161 , p_error_found in number) is
2162
2163 --cursor to retrieve the continuous moves in a group to update TP tables
2164 cursor cur_continuous_moves (l_release_id in pls_integer, l_group_id in pls_integer)
2165 is
2166 select continuous_move_id, planned_flag
2167 from mst_release_temp_gt
2168 where release_id = l_release_id
2169 and group_id = l_group_id
2170 and continuous_move_id is not null;
2171
2172 l_continuous_move_id_tab number_tab_type;
2173 l_planned_flag_tab number_tab_type;
2174
2178 begin
2175 l_release_mode pls_integer := nvl(p_release_mode,2);
2176 l_plan_id number := p_plan_id;
2177 l_date date := p_date;
2179 print_info(g_log_flow_of_control,'update_mst_cm_trips for group '||p_group_id||' : Program started');
2180
2181 open cur_continuous_moves(p_release_id, p_group_id);
2182 fetch cur_continuous_moves bulk collect into l_continuous_move_id_tab, l_planned_flag_tab;
2183 close cur_continuous_moves;
2184
2185 if nvl(l_continuous_move_id_tab.last,0) > 0 then
2186 if p_error_found = 0 then -- group was successful
2187 forall i in 1..l_continuous_move_id_tab.last
2188 update mst_cm_trips
2189 set planned_flag = l_planned_flag_tab(i)
2190 , release_status = l_planned_flag_tab(i)
2191 , release_date = l_date -- sysdate since successful
2192 , auto_release_flag = l_release_mode
2193 , selected_for_release = null
2194 where plan_id = l_plan_id
2195 and continuous_move_id = l_continuous_move_id_tab(i);
2196 g_cnt_cm_released := g_cnt_cm_released + nvl(l_continuous_move_id_tab.last,0);
2197 else -- group was not successful
2198 forall i in 1..l_continuous_move_id_tab.last
2199 update mst_cm_trips
2200 set release_status = 3 -- failed
2201 , release_date = l_date -- datetime at start of process since unsuccessful
2202 , auto_release_flag = l_release_mode
2203 , selected_for_release = null
2204 where plan_id = l_plan_id
2205 and continuous_move_id = l_continuous_move_id_tab(i);
2206 g_cnt_cm_failed := g_cnt_cm_failed + nvl(l_continuous_move_id_tab.last,0);
2207 end if;
2208 end if;
2209
2210 x_return_status := fnd_api.g_ret_sts_success;
2211 print_info(g_log_flow_of_control,'update_mst_cm_trips for group '||p_group_id||' : Program ended');
2212 exception
2213 when others then
2214 x_return_status := fnd_api.g_ret_sts_unexp_error;
2215 print_info(g_log_flow_of_control,'update_mst_cm_trips : Unexpected error ' || to_char(sqlcode) || ':' || SQLERRM);
2216 set_concurrent_status('ERROR',get_seeded_message('MST_REL_BK_MESSAGE_47') || to_char(sqlcode) || ':' || SQLERRM);
2217 end update_mst_cm_trips;
2218
2219 function get_statistics (p_release_id in number
2220 , p_plan_id in number
2221 , p_group_id in number
2222 , p_mode_or_type in varchar2) return number is
2223
2224 --cursor to count trips of a certain mode of transport in a group
2225 cursor cur_trips_mode (l_release_id in pls_integer, l_group_id in pls_integer, l_mode_of_transport in varchar2)
2226 is
2227 select count(1)
2228 from mst_release_temp_gt
2229 where release_id = l_release_id
2230 and group_id = l_group_id
2231 and trip_id is not null
2232 and mode_of_transport = l_mode_of_transport;
2233
2234 --cursor to count deadhead trips in a group
2235 cursor cur_deadheads (l_release_id in pls_integer, l_plan_id in pls_integer, l_group_id in pls_integer)
2236 is
2237 select count(1)
2238 from mst_release_temp_gt mrt_gt
2239 where mrt_gt.release_id = l_release_id
2240 and mrt_gt.group_id = l_group_id
2241 and mrt_gt.plan_id = l_plan_id
2242 and mrt_gt.trip_id is not null
2243 and mrt_gt.cm_id_of_trip is not null
2244 and mrt_gt.trip_id not in (select mdl.trip_id
2245 from mst_delivery_legs mdl
2246 where mdl.plan_id = l_plan_id);
2247
2248 l_count pls_integer := 0;
2249
2250 begin
2251 if p_mode_or_type = 'DEADHEAD' then
2252 open cur_deadheads(p_release_id, p_plan_id, p_group_id);
2253 fetch cur_deadheads into l_count;
2254 close cur_deadheads;
2255 else -- for TRUCK, LTL, PARCEL
2256 open cur_trips_mode(p_release_id, p_group_id, p_mode_or_type);
2257 fetch cur_trips_mode into l_count;
2258 close cur_trips_mode;
2259 end if;
2260
2261 return nvl(l_count,0);
2262 exception
2263 when others then
2264 return 0;
2265 end get_statistics;
2266
2267 procedure update_mst_trips (x_return_status out nocopy varchar2
2268 , p_group_id in pls_integer
2269 , p_plan_id in number
2270 , p_release_id in pls_integer
2271 , p_release_mode in number
2272 , p_date in date
2273 , p_error_found in number) is
2274
2275 --cursor to retrieve the trips in a group to update TP tables
2276 cursor cur_trips (l_release_id in pls_integer, l_group_id in pls_integer)
2277 is
2278 select trip_id, planned_flag
2279 from mst_release_temp_gt
2280 where release_id = l_release_id
2281 and group_id = l_group_id
2282 and trip_id is not null;
2283
2284 l_trip_id_tab number_tab_type;
2285 l_planned_flag_tab number_tab_type;
2286
2287 l_release_mode pls_integer := nvl(p_release_mode,2);
2288 l_plan_id number := p_plan_id;
2289 l_date date := p_date;
2290 begin
2291 print_info(g_log_flow_of_control,'update_mst_trips for group '||p_group_id||' : Program started');
2292
2293 open cur_trips(p_release_id, p_group_id);
2294 fetch cur_trips bulk collect into l_trip_id_tab, l_planned_flag_tab;
2295 close cur_trips;
2296
2297 if nvl(l_trip_id_tab.last,0) > 0 then
2298 if p_error_found = 0 then -- group was successful
2299 forall i in 1..l_trip_id_tab.last
2300 update mst_trips
2301 set planned_flag = l_planned_flag_tab(i)
2302 , release_status = l_planned_flag_tab(i)
2303 , release_date = l_date
2304 , auto_release_flag = l_release_mode
2305 , selected_for_release = null
2306 where plan_id = l_plan_id
2307 and trip_id = l_trip_id_tab(i);
2308 g_cnt_trip_released := g_cnt_trip_released + nvl(l_trip_id_tab.last,0);
2309
2310 g_cnt_truck_released := g_cnt_truck_released + get_statistics(p_release_id, p_plan_id, p_group_id, 'TRUCK');
2311 g_cnt_ltl_released := g_cnt_ltl_released + get_statistics(p_release_id, p_plan_id, p_group_id, 'LTL');
2312 g_cnt_parcel_released := g_cnt_parcel_released + get_statistics(p_release_id, p_plan_id, p_group_id, 'PARCEL');
2313 g_cnt_deadhead_released := g_cnt_deadhead_released + get_statistics(p_release_id, p_plan_id, p_group_id, 'DEADHEAD');
2314
2315 else -- group was not successful
2316 forall i in 1..l_trip_id_tab.last
2317 update mst_trips
2318 set release_status = 4
2319 , release_date = l_date -- datetime at start process, it is coming as parameter to this procedure
2320 , auto_release_flag = l_release_mode
2321 , selected_for_release = null
2322 where plan_id = l_plan_id
2323 and trip_id = l_trip_id_tab(i) ;
2324 g_cnt_trip_failed := g_cnt_trip_failed + nvl(l_trip_id_tab.last,0);
2328 g_cnt_parcel_failed := g_cnt_parcel_failed + get_statistics(p_release_id, p_plan_id, p_group_id, 'PARCEL');
2325
2326 g_cnt_truck_failed := g_cnt_truck_failed + get_statistics(p_release_id, p_plan_id, p_group_id, 'TRUCK');
2327 g_cnt_ltl_failed := g_cnt_ltl_failed + get_statistics(p_release_id, p_plan_id, p_group_id, 'LTL');
2329 g_cnt_deadhead_failed := g_cnt_deadhead_failed + get_statistics(p_release_id, p_plan_id, p_group_id, 'DEADHEAD');
2330 end if;
2331 end if;
2332
2333 x_return_status := fnd_api.g_ret_sts_success;
2334 print_info(g_log_flow_of_control,'update_mst_trips for group '||p_group_id||' : Program ended');
2335 exception
2336 when others then
2337 x_return_status := fnd_api.g_ret_sts_unexp_error;
2338 print_info(g_log_flow_of_control,'update_mst_trips : Unexpected error ' || to_char(sqlcode) || ':' || SQLERRM);
2339 set_concurrent_status('ERROR',get_seeded_message('MST_REL_BK_MESSAGE_47') || to_char(sqlcode) || ':' || SQLERRM);
2340 end update_mst_trips;
2341
2342 procedure update_mst_deliveries (x_return_status out nocopy varchar2
2343 , p_group_id in pls_integer
2344 , p_plan_id in number
2345 , p_release_id in pls_integer
2346 , p_error_found in pls_integer) is
2347
2348 --cursor to retrieve the deliveries in a group to update TP tables
2349 cursor cur_deliveries (l_release_id in pls_integer, l_group_id in pls_integer)
2350 is
2351 select delivery_id, planned_flag
2352 from mst_release_temp_gt
2353 where release_id = l_release_id
2354 and group_id = l_group_id
2355 and delivery_id is not null;
2356
2357 l_delivery_id_tab number_tab_type;
2358 l_planned_flag_tab number_tab_type;
2359
2360 l_error_found pls_integer := p_error_found;
2361 l_plan_id number := p_plan_id;
2362 begin
2363 print_info(g_log_flow_of_control,'update_mst_deliveries for group '||p_group_id||' : Program started');
2364 if l_error_found = 0 then
2365 open cur_deliveries(p_release_id, p_group_id);
2366 fetch cur_deliveries bulk collect into l_delivery_id_tab, l_planned_flag_tab;
2367 close cur_deliveries;
2368
2369 if nvl(l_delivery_id_tab.last,0) > 0 then
2370 forall i in 1..l_delivery_id_tab.last
2371 update mst_deliveries -- remember l_error_found = 0 => successful and l_error_found = 1 => unsuccessful
2372 set planned_flag = decode(l_planned_flag_tab(i),1,1,2)
2373 , preserve_grouping_flag = decode(l_planned_flag_tab(i),1,null,2,1,2)
2374 , known_te_firm_status = l_planned_flag_tab(i)
2375 where plan_id = l_plan_id
2376 and delivery_id = l_delivery_id_tab(i);
2377 end if;
2378 end if;
2379
2380 x_return_status := fnd_api.g_ret_sts_success;
2381 print_info(g_log_flow_of_control,'update_mst_deliveries for group '||p_group_id||' : Program ended');
2382 exception
2383 when others then
2384 x_return_status := fnd_api.g_ret_sts_unexp_error;
2385 print_info(g_log_flow_of_control,'update_mst_deliveries : Unexpected error ' || to_char(sqlcode) || ':' || SQLERRM);
2386 set_concurrent_status('ERROR',get_seeded_message('MST_REL_BK_MESSAGE_47') || to_char(sqlcode) || ':' || SQLERRM);
2387 end update_mst_deliveries;
2388
2389 procedure update_sr_ids_in_mt (x_return_status out nocopy varchar2
2390 , p_group_id in pls_integer
2391 , p_plan_id in number
2395 --cursor to retrieve the trips in a group to update mst_trips
2392 , p_release_id in pls_integer
2393 , p_error_found in pls_integer) is
2394
2396 cursor cur_trips (l_release_id in pls_integer, l_group_id in pls_integer)
2397 is
2398 select mrt.trip_id, wti.trip_id
2399 from mst_release_temp_gt mrt
2400 , wsh_trips_interface wti
2401 where mrt.release_id = l_release_id
2402 and mrt.group_id = l_group_id
2403 and mrt.trip_id is not null
2404 and mrt.trip_id_iface = wti.trip_interface_id;
2405
2406 l_trip_id_tab number_tab_type;
2407 l_sr_trip_id_tab number_tab_type;
2408
2409 l_error_found pls_integer := p_error_found;
2410 l_plan_id number := p_plan_id;
2411 begin
2412 print_info(g_log_flow_of_control,'update_sr_ids_in_mt for group '||p_group_id||' : Program started');
2413 if l_error_found = 0 then -- remember l_error_found = 0 => successful and l_error_found = 1 => unsuccessful
2414 open cur_trips(p_release_id, p_group_id);
2415 fetch cur_trips bulk collect into l_trip_id_tab, l_sr_trip_id_tab;
2416 close cur_trips;
2417
2418 if nvl(l_trip_id_tab.last,0) > 0 then
2419 forall i in 1..l_trip_id_tab.last
2420 update mst_trips
2421 set sr_trip_id = l_sr_trip_id_tab(i)
2422 where plan_id = l_plan_id
2423 and trip_id = l_trip_id_tab(i);
2424 end if;
2425 end if;
2426
2427 x_return_status := fnd_api.g_ret_sts_success;
2428 print_info(g_log_flow_of_control,'update_sr_ids_in_mt for group '||p_group_id||' : Program ended');
2429 exception
2430 when others then
2431 x_return_status := fnd_api.g_ret_sts_unexp_error;
2432 print_info(g_log_flow_of_control,'update_sr_ids_in_mt : Unexpected error ' || to_char(sqlcode) || ':' || SQLERRM);
2433 set_concurrent_status('ERROR',get_seeded_message('MST_REL_BK_MESSAGE_47') || to_char(sqlcode) || ':' || SQLERRM);
2434 end update_sr_ids_in_mt;
2435
2436 procedure update_sr_ids_in_md (x_return_status out nocopy varchar2
2437 , p_group_id in pls_integer
2438 , p_plan_id in number
2439 , p_release_id in pls_integer
2440 , p_error_found in pls_integer) is
2441
2442 --cursor to retrieve the trips in a group to update mst_trips
2443 cursor cur_deliveries (l_release_id in pls_integer, l_group_id in pls_integer)
2444 is
2445 select mrt.delivery_id, wndi.delivery_id
2446 from mst_release_temp_gt mrt
2447 , wsh_new_del_interface wndi
2448 where mrt.release_id = l_release_id
2449 and mrt.group_id = l_group_id
2450 and mrt.delivery_id is not null
2451 and mrt.delivery_id_iface = wndi.delivery_interface_id;
2452
2453 l_delivery_id_tab number_tab_type;
2454 l_sr_delivery_id_tab number_tab_type;
2455
2456 l_error_found pls_integer := p_error_found;
2457 l_plan_id number := p_plan_id;
2458 begin
2459 print_info(g_log_flow_of_control,'update_sr_ids_in_md for group '||p_group_id||' : Program started');
2460 if l_error_found = 0 then -- remember l_error_found = 0 => successful and l_error_found = 1 => unsuccessful
2461 open cur_deliveries(p_release_id, p_group_id);
2462 fetch cur_deliveries bulk collect into l_delivery_id_tab, l_sr_delivery_id_tab;
2463 close cur_deliveries;
2464
2465 if nvl(l_delivery_id_tab.last,0) > 0 then
2466 forall i in 1..l_delivery_id_tab.last
2467 update mst_deliveries
2468 set sr_delivery_id = l_sr_delivery_id_tab(i)
2469 where plan_id = l_plan_id
2470 and delivery_id = l_delivery_id_tab(i);
2471 end if;
2472 end if;
2473
2474 x_return_status := fnd_api.g_ret_sts_success;
2475 print_info(g_log_flow_of_control,'update_sr_ids_in_md for group '||p_group_id||' : Program ended');
2476 exception
2477 when others then
2478 x_return_status := fnd_api.g_ret_sts_unexp_error;
2479 print_info(g_log_flow_of_control,'update_sr_ids_in_md : Unexpected error ' || to_char(sqlcode) || ':' || SQLERRM);
2480 set_concurrent_status('ERROR',get_seeded_message('MST_REL_BK_MESSAGE_47') || to_char(sqlcode) || ':' || SQLERRM);
2481 end update_sr_ids_in_md;
2482
2483 procedure update_sr_ids_in_mdd (x_return_status out nocopy varchar2
2484 , p_group_id in pls_integer
2485 , p_plan_id in number
2486 , p_release_id in pls_integer
2487 , p_error_found in pls_integer) is
2488
2489 --cursor to retrieve the trips in a group to update mst_trips
2490 cursor cur_delivery_details (l_release_id in pls_integer, l_group_id in pls_integer)
2491 is
2492 select mrt.delivery_detail_id, wddi.delivery_detail_id
2493 from mst_release_temp_gt mrt
2494 , wsh_del_details_interface wddi
2495 where mrt.release_id = l_release_id
2496 and mrt.group_id = l_group_id
2497 and mrt.delivery_detail_id is not null
2498 and mrt.delivery_detail_id_iface = wddi.delivery_detail_interface_id;
2499
2500 l_delivery_detail_id_tab number_tab_type;
2501 l_sr_delivery_detail_id_tab number_tab_type;
2502
2503 l_error_found pls_integer := p_error_found;
2504 l_plan_id number := p_plan_id;
2505 begin
2506 print_info(g_log_flow_of_control,'update_sr_ids_in_mdd for group '||p_group_id||' : Program started');
2507 if l_error_found = 0 then -- remember l_error_found = 0 => successful and l_error_found = 1 => unsuccessful
2508 open cur_delivery_details(p_release_id, p_group_id);
2509 fetch cur_delivery_details bulk collect into l_delivery_detail_id_tab, l_sr_delivery_detail_id_tab;
2510 close cur_delivery_details;
2511
2512 if nvl(l_delivery_detail_id_tab.last,0) > 0 then
2513 forall i in 1..l_delivery_detail_id_tab.last
2514 update mst_delivery_details
2515 set sr_delivery_detail_id = l_sr_delivery_detail_id_tab(i)
2516 where plan_id = l_plan_id
2517 and delivery_detail_id = l_delivery_detail_id_tab(i);
2518 end if;
2519 end if;
2520
2521 x_return_status := fnd_api.g_ret_sts_success;
2522 print_info(g_log_flow_of_control,'update_sr_ids_in_mdd for group '||p_group_id||' : Program ended');
2523 exception
2524 when others then
2525 x_return_status := fnd_api.g_ret_sts_unexp_error;
2526 print_info(g_log_flow_of_control,'update_sr_ids_in_mdd : Unexpected error ' || to_char(sqlcode) || ':' || SQLERRM);
2527 set_concurrent_status('ERROR',get_seeded_message('MST_REL_BK_MESSAGE_47') || to_char(sqlcode) || ':' || SQLERRM);
2528 end update_sr_ids_in_mdd;
2529
2530 procedure update_tp_tables (x_return_status out nocopy varchar2
2531 , p_group_tab in wsh_tp_release_grp.id_tab_type
2532 , p_plan_id in number
2533 , p_release_id in pls_integer
2534 , p_release_mode in number
2535 , p_release_start_date in date) is
2536
2537 --cursor to check whether a group_id has failed or successful
2538 cursor cur_check_error (l_group_id in pls_integer)
2539 is
2540 select 1
2541 from wsh_interface_errors wie
2542 where wie.interface_error_group_id = l_group_id
2543 and wie.interface_action_code = wsh_tp_release_grp.G_TP_RELEASE_CODE;
2544
2545 --cursor to log error message into log file after pulling from wsh_interface_errors
2546 cursor cur_log_error (l_group_id in pls_integer)
2547 is
2548 select error_message
2549 from wsh_interface_errors wie
2550 where wie.interface_error_group_id = l_group_id
2551 and wie.interface_action_code = wsh_tp_release_grp.G_TP_RELEASE_CODE
2552 order by wie.interface_error_id;
2553
2554 l_dummy pls_integer;
2555 l_error_found pls_integer := 0;
2556 l_date date;
2557
2558 type varchar2_tab_type is table of varchar2(4000) index by binary_integer;
2559 l_message_tab varchar2_tab_type;
2560
2561 l_return_status varchar2(1);
2562 l_error_from_called_procedure exception;
2563 l_Message_Text varchar2(1000);
2564 begin
2565 print_info(g_log_flow_of_control,'update_tp_tables : Program started');
2566
2567 if nvl(p_group_tab.last,0) > 0 then
2568 for i in 1..p_group_tab.last loop
2569 -- check for error for current group_id
2570 open cur_check_error(p_group_tab(i));
2571 fetch cur_check_error into l_dummy;
2572 if cur_check_error%notfound then
2573 l_error_found := 0; -- release was successful
2574 select sysdate
2575 into l_date
2576 from dual;
2577 g_cnt_group_released := g_cnt_group_released + 1;
2578 else
2579 l_error_found := 1; -- release was failed
2580 l_date := p_release_start_date; -- release start datetime
2581 g_cnt_group_failed := g_cnt_group_failed + 1;
2582 end if;
2583 close cur_check_error;
2584
2585 update_mst_cm_trips (l_return_status, p_group_tab(i), p_plan_id, p_release_id, p_release_mode, l_date, l_error_found);
2586 if l_return_status <> fnd_api.g_ret_sts_success then
2587 raise l_error_from_called_procedure;
2588 else
2589 update_mst_trips (l_return_status, p_group_tab(i), p_plan_id, p_release_id, p_release_mode, l_date, l_error_found);
2593 if l_error_found = 0 then -- if successful only proceed
2590 if l_return_status <> fnd_api.g_ret_sts_success then
2591 raise l_error_from_called_procedure;
2592 else
2594 update_mst_deliveries (l_return_status, p_group_tab(i), p_plan_id, p_release_id, l_error_found);
2595 if l_return_status <> fnd_api.g_ret_sts_success then
2596 raise l_error_from_called_procedure;
2597 else
2598 update_sr_ids_in_mt (l_return_status, p_group_tab(i), p_plan_id, p_release_id, l_error_found);
2599 if l_return_status <> fnd_api.g_ret_sts_success then
2600 raise l_error_from_called_procedure;
2601 else
2602 update_sr_ids_in_md (l_return_status, p_group_tab(i), p_plan_id, p_release_id, l_error_found);
2603 if l_return_status <> fnd_api.g_ret_sts_success then
2604 raise l_error_from_called_procedure;
2605 else
2606 update_sr_ids_in_mdd (l_return_status, p_group_tab(i), p_plan_id, p_release_id, l_error_found);
2607 if l_return_status <> fnd_api.g_ret_sts_success then
2608 raise l_error_from_called_procedure;
2609 end if;
2610 end if;
2611 end if;
2612 end if;
2613 end if;
2614 end if;
2615 end if;
2616
2617 -- log error messages into log file
2618 if l_error_found = 1 then -- release was failed
2619 open cur_log_error(p_group_tab(i));
2620 fetch cur_log_error bulk collect into l_message_tab;
2621 close cur_log_error;
2622 if nvl(l_message_tab.last,0) > 0 then
2623 --print_info(g_log_must_message,'Group '||p_group_tab(i)||' failed');
2624 fnd_message.set_name('MST','MST_REL_BK_MESSAGE_35');
2625 fnd_message.set_token('N1',to_char(p_group_tab(i)));
2626 l_Message_Text := fnd_message.get;
2627 print_info(g_log_must_message,l_Message_Text);
2628 for j in 1..l_message_tab.last loop
2629 print_info(g_log_must_message,' '||l_message_tab(j));
2630 end loop;
2631 log_group_trip_data(g_log_failed_data,p_group_tab(i),'[ failed ]');
2632 end if;
2633 else
2634 --print_info(g_log_released_data,'Group '||p_group_tab(i)||' released');
2635 fnd_message.set_name('MST','MST_REL_BK_MESSAGE_36');
2636 fnd_message.set_token('N1',to_char(p_group_tab(i)));
2637 l_Message_Text := fnd_message.get;
2638 print_info(g_log_must_message,l_Message_Text);
2639 log_group_trip_data(g_log_released_data,p_group_tab(i),'[ released ]');
2640 end if;
2641 end loop;
2642 end if;
2643
2644 x_return_status := fnd_api.g_ret_sts_success;
2645 print_info(g_log_flow_of_control,'update_tp_tables : Program ended');
2646 exception
2647 when l_error_from_called_procedure then
2648 x_return_status := fnd_api.g_ret_sts_error;
2649 when others then
2650 x_return_status := fnd_api.g_ret_sts_unexp_error;
2651 print_info(g_log_flow_of_control,'update_tp_tables : Unexpected error ' || to_char(sqlcode) || ':' || SQLERRM);
2652 set_concurrent_status('ERROR',get_seeded_message('MST_REL_BK_MESSAGE_47') || to_char(sqlcode) || ':' || SQLERRM);
2653 end update_tp_tables;
2654
2655 procedure pull_into_global_temp_table (x_return_status out nocopy varchar2
2656 , p_release_id in number) is
2657 begin
2658 print_info(g_log_flow_of_control,'pull_into_global_temp_table : Program started');
2662 , plan_id
2659 insert into mst_release_temp_gt
2660 ( release_id
2661 , group_id
2663 , trip_id
2664 , trip_number
2665 , planned_flag
2666 , release_status
2667 , trip_start_date
2668 , cm_id_of_trip
2669 , continuous_move_id
2670 , delivery_id
2671 , out_of_scope
2672 , trip_process_flag
2673 , selected_trips
2674 , trip_id_iface
2675 , continuous_move_id_iface
2676 , delivery_id_iface
2677 , stop_id_iface
2678 , continuous_move_sequence
2679 , sr_cm_trip_id
2680 , sr_trip_id
2681 , stop_id
2682 , sr_delivery_id
2683 , delivery_detail_id_iface
2684 , delivery_detail_id
2685 , sr_delivery_detail_id
2686 , sr_delivery_assignment_id
2687 , sr_stop_id, status_code
2688 , pickup_date
2689 , dropoff_date
2690 , pickup_location_id
2691 , dropoff_location_id
2692 , customer_id
2693 , gross_weight
2694 , net_weight
2695 , weight_uom
2696 , volume
2697 , volume_uom
2698 , currency_uom
2699 , organization_id
2700 , delivery_number
2701 , compile_designator
2702 , earliest_pickup_date
2703 , latest_pickup_date
2704 , earliest_acceptable_date
2705 , latest_acceptable_date
2706 , inventory_item_id
2707 , carrier_id
2708 , ship_method_code
2709 , mode_of_transport
2710 , load_tender_status
2711 , lane_id, service_level
2712 , cm_trip_number
2713 , stop_location_id
2714 , stop_sequence_number
2715 , planned_arrival_date
2716 , planned_departure_date
2717 , departure_gross_weight
2718 , departure_net_weight
2719 , departure_volume
2720 , departure_fill_percent
2721 , wkend_layover_stops
2722 , wkday_layover_stops
2723 , distance_to_next_stop
2724 , pln_loading_start_time
2725 , pln_loading_end_time
2726 , pln_unloading_start_time
2727 , pln_unloading_end_time
2728 , source_code
2729 , ship_from_location_id
2730 , ship_to_location_id
2731 , requested_quantity
2732 , source_header_number
2733 , ship_set_id
2734 , arrival_set_id
2735 , org_id
2736 , container_flag
2737 , source_line_number
2738 , split_from_delivery_detail_id
2739 , line_direction
2740 , po_shipment_number
2741 , po_shipment_line_number
2742 , src_requested_quantity_uom
2743 , src_requested_quantity
2744 , shipment_direction
2745 , supplier_id
2746 , party_id
2747 )
2748 (
2749 select release_id
2750 , group_id
2751 , plan_id
2752 , trip_id
2753 , trip_number
2754 , planned_flag
2755 , release_status
2756 , trip_start_date
2757 , cm_id_of_trip
2758 , continuous_move_id
2759 , delivery_id
2760 , out_of_scope
2761 , trip_process_flag
2762 , selected_trips
2763 , trip_id_iface
2764 , continuous_move_id_iface
2765 , delivery_id_iface
2766 , stop_id_iface
2767 , continuous_move_sequence
2768 , sr_cm_trip_id
2769 , sr_trip_id
2770 , stop_id
2771 , sr_delivery_id
2772 , delivery_detail_id_iface
2773 , delivery_detail_id
2774 , sr_delivery_detail_id
2775 , sr_delivery_assignment_id
2776 , sr_stop_id, status_code
2777 , pickup_date
2778 , dropoff_date
2779 , pickup_location_id
2780 , dropoff_location_id
2781 , customer_id
2782 , gross_weight
2783 , net_weight
2784 , weight_uom
2785 , volume
2786 , volume_uom
2787 , currency_uom
2788 , organization_id
2789 , delivery_number
2790 , compile_designator
2791 , earliest_pickup_date
2792 , latest_pickup_date
2793 , earliest_acceptable_date
2794 , latest_acceptable_date
2795 , inventory_item_id
2796 , carrier_id
2797 , ship_method_code
2798 , mode_of_transport
2799 , load_tender_status
2800 , lane_id, service_level
2801 , cm_trip_number
2802 , stop_location_id
2803 , stop_sequence_number
2804 , planned_arrival_date
2805 , planned_departure_date
2806 , departure_gross_weight
2807 , departure_net_weight
2808 , departure_volume
2809 , departure_fill_percent
2810 , wkend_layover_stops
2811 , wkday_layover_stops
2812 , distance_to_next_stop
2813 , pln_loading_start_time
2814 , pln_loading_end_time
2815 , pln_unloading_start_time
2816 , pln_unloading_end_time
2817 , source_code
2818 , ship_from_location_id
2819 , ship_to_location_id
2820 , requested_quantity
2821 , source_header_number
2822 , ship_set_id
2823 , arrival_set_id
2824 , org_id
2825 , container_flag
2826 , source_line_number
2827 , split_from_delivery_detail_id
2828 , line_direction
2829 , po_shipment_number
2830 , po_shipment_line_number
2831 , src_requested_quantity_uom
2832 , src_requested_quantity
2833 , shipment_direction
2834 , supplier_id
2835 , party_id
2836 from mst_release_temp
2837 where release_id = p_release_id
2838 );
2839
2840 x_return_status := fnd_api.g_ret_sts_success;
2841 print_info(g_log_flow_of_control,'pull_into_global_temp_table : Program ended');
2842 exception
2843 when others then
2844 x_return_status := fnd_api.g_ret_sts_unexp_error;
2845 print_info(g_log_flow_of_control,'pull_into_global_temp_table : Unexpected error ' || to_char(sqlcode) || ':' || SQLERRM);
2846 set_concurrent_status('ERROR',get_seeded_message('MST_REL_BK_MESSAGE_47') || to_char(sqlcode) || ':' || SQLERRM);
2847 end pull_into_global_temp_table;
2848
2849 procedure compute_statistics (x_return_status out nocopy varchar2
2850 , p_plan_id in number
2851 , p_release_id in number) is
2852
2853 -- cursor to compute the count of unattempted group
2854 cursor cur_group_counts (l_plan_id in number, l_release_id in number)
2855 is
2856 select count(distinct mrt_gt.group_id)
2857 from mst_release_temp_gt mrt_gt
2858 where mrt_gt.release_id = l_release_id
2859 and mrt_gt.plan_id = l_plan_id
2860 and mrt_gt.trip_id is not null
2861 and mrt_gt.planned_flag in (-1111,-2222,-3333,-4444,-5555);
2862
2863 -- cursor to compute the count of unattempted trips, tls, ltls and parcels
2864 cursor cur_trip_counts (l_plan_id in number, l_release_id in number, l_mode_of_transport in varchar2)
2865 is
2866 select count(1)
2867 from mst_release_temp_gt mrt_gt
2868 where mrt_gt.release_id = l_release_id
2869 and mrt_gt.plan_id = l_plan_id
2870 and mrt_gt.trip_id is not null
2871 and mrt_gt.planned_flag in (-1111,-2222,-3333,-4444,-5555)
2872 and mrt_gt.mode_of_transport = l_mode_of_transport;
2873
2874 -- cursor to compute the count of unattempted deadheads
2875 cursor cur_deadhead_counts (l_plan_id in number, l_release_id in number)
2876 is
2877 select count(1)
2878 from mst_release_temp_gt mrt_gt
2879 where mrt_gt.release_id = l_release_id
2880 and mrt_gt.trip_id is not null
2881 and mrt_gt.planned_flag in (-1111,-2222,-3333,-4444,-5555)
2882 and mrt_gt.cm_id_of_trip is not null
2883 and mrt_gt.trip_id not in (select mdl.trip_id
2884 from mst_delivery_legs mdl
2885 where plan_id = l_plan_id);
2886
2887 -- cursor to compute the count of unattempted empty trips which are not part of any continuous move
2888 cursor cur_non_cm_empty_trip_counts (l_plan_id in number, l_release_id in number)
2889 is
2890 select count(1)
2891 from mst_release_temp_gt mrt_gt
2892 where mrt_gt.release_id = l_release_id
2893 and mrt_gt.trip_id is not null
2894 and mrt_gt.planned_flag in (-1111,-2222,-3333,-4444,-5555)
2895 and mrt_gt.cm_id_of_trip is null
2896 and mrt_gt.trip_id not in (select mdl.trip_id
2897 from mst_delivery_legs mdl
2898 where plan_id = l_plan_id);
2899
2900 -- cursor to compute the statistics of unattempted empty trips
2901 cursor cur_statistics_of_unattempted (l_plan_id in number, l_release_id in number)
2902 is
2903 select mrt_gt.planned_flag, count(*)
2904 from mst_release_temp_gt mrt_gt
2905 where mrt_gt.release_id = l_release_id
2906 and mrt_gt.trip_id is not null
2907 and mrt_gt.planned_flag in (-1111,-2222,-3333,-4444,-5555)
2908 group by mrt_gt.planned_flag;
2909
2910 l_planned_flag_tab number_tab_type;
2911 l_count_tab number_tab_type;
2912 l_Message_Text varchar2(1000);
2913 begin
2914 print_info(g_log_flow_of_control,'compute_statistics : Program started');
2915 open cur_group_counts (p_plan_id, p_release_id);
2916 fetch cur_group_counts into g_cnt_group_not_attempted;
2917 close cur_group_counts;
2918
2919 open cur_trip_counts (p_plan_id, p_release_id, 'TRUCK');
2920 fetch cur_trip_counts into g_cnt_truck_not_attempted;
2921 close cur_trip_counts;
2922
2923 open cur_trip_counts (p_plan_id, p_release_id, 'LTL');
2924 fetch cur_trip_counts into g_cnt_ltl_not_attempted;
2925 close cur_trip_counts;
2926
2927 open cur_trip_counts (p_plan_id, p_release_id, 'PARCEL');
2928 fetch cur_trip_counts into g_cnt_parcel_not_attempted;
2929 close cur_trip_counts;
2930
2931 g_cnt_trip_not_attempted := g_cnt_truck_not_attempted + g_cnt_ltl_not_attempted + g_cnt_parcel_not_attempted;
2932
2933 open cur_deadhead_counts (p_plan_id, p_release_id);
2934 fetch cur_deadhead_counts into g_cnt_deadhead_not_attempted;
2935 close cur_deadhead_counts;
2936
2937 open cur_non_cm_empty_trip_counts (p_plan_id, p_release_id);
2938 fetch cur_non_cm_empty_trip_counts into g_cnt_etrip_not_attempted;
2939 close cur_non_cm_empty_trip_counts;
2940
2941 open cur_statistics_of_unattempted (p_plan_id, p_release_id);
2942 fetch cur_statistics_of_unattempted bulk collect into l_planned_flag_tab, l_count_tab;
2943 close cur_statistics_of_unattempted;
2944
2945 if nvl(l_planned_flag_tab.last,0) >= 1 then
2946 for i in 1..l_planned_flag_tab.last loop
2947 if l_planned_flag_tab(i) = -1111 then
2948 fnd_message.set_name('MST','MST_REL_BK_MESSAGE_53');
2949 elsif l_planned_flag_tab(i) = -2222 then
2950 fnd_message.set_name('MST','MST_REL_BK_MESSAGE_54');
2951 elsif l_planned_flag_tab(i) = -3333 then
2952 fnd_message.set_name('MST','MST_REL_BK_MESSAGE_55');
2953 elsif l_planned_flag_tab(i) = -4444 then
2954 fnd_message.set_name('MST','MST_REL_BK_MESSAGE_56');
2955 elsif l_planned_flag_tab(i) = -5555 then
2956 fnd_message.set_name('MST','MST_REL_BK_MESSAGE_57');
2957 end if;
2958 fnd_message.set_token('N1',to_char(l_count_tab(i)));
2959 l_Message_Text := fnd_message.get;
2960 print_info(g_log_must_message,l_Message_Text);
2961 print_info(g_log_must_message,'');
2962 end loop;
2963 end if;
2964
2965 delete from mst_release_temp_gt mrt_gt
2966 where mrt_gt.release_id = p_release_id
2967 and mrt_gt.plan_id = p_plan_id
2968 and mrt_gt.planned_flag in (-1111,-2222,-3333,-4444,-5555);
2969
2970 x_return_status := fnd_api.g_ret_sts_success;
2971 print_info(g_log_flow_of_control,'compute_statistics : Program ended');
2972 exception
2973 when others then
2974 x_return_status := fnd_api.g_ret_sts_unexp_error;
2975 print_info(g_log_flow_of_control,'compute_statistics : Unexpected error ' || to_char(sqlcode) || ':' || SQLERRM);
2976 set_concurrent_status('ERROR',get_seeded_message('MST_REL_BK_MESSAGE_47') || to_char(sqlcode) || ':' || SQLERRM);
2977 end compute_statistics;
2978
2979 procedure do_release (p_plan_id in number
2980 , p_release_id in number
2981 , p_release_mode in number) is
2982
2983 cursor cur_check_trips (l_release_id in pls_integer)
2984 is
2985 select 1
2986 from mst_release_temp_gt
2987 where release_id = l_release_id;
2988
2989 l_dummy pls_integer := null;
2990
2991 l_group_tab wsh_tp_release_grp.id_tab_type;
2992 l_input_rec wsh_tp_release_grp.input_rec_type;
2993 l_output_rec wsh_tp_release_grp.output_rec_type;
2994
2995 l_return_status varchar2(1);
2996 l_release_start_date date;
2997 l_Message_Text varchar2(1000);
2998 begin
2999 print_info(g_log_flow_of_control,'do_release : Release Id = ' ||p_release_id|| ' Program started');
3000
3001 open cur_check_trips (p_release_id);
3002 fetch cur_check_trips into l_dummy;
3003 close cur_check_trips;
3004
3005 if l_dummy is null then
3006 --print_info(g_log_must_message,'Not even a single trip was found to release.');
3007 print_info(g_log_must_message,get_seeded_message('MST_REL_BK_MESSAGE_37'));
3008 log_statistics;
3009 --set_concurrent_status('WARNING', 'Zero Groups attempted to release.');
3010 set_concurrent_status('WARNING', get_seeded_message('MST_REL_BK_MESSAGE_38'));
3011 else
3012 select sysdate
3013 into l_release_start_date
3014 from dual;
3015
3016 remove_unqualified_trips(l_return_status, p_plan_id, p_release_id);
3017 if l_return_status = fnd_api.g_ret_sts_success then
3018 populate_related_trips(l_return_status, p_plan_id, p_release_id);
3019 if l_return_status = fnd_api.g_ret_sts_success then
3020 remove_grp_of_passed_dep_dt (l_return_status, p_release_id,l_release_start_date);
3021 if l_return_status = fnd_api.g_ret_sts_success then
3022 remove_grp_of_exceptions(l_return_status, p_plan_id, p_release_id, p_release_mode);
3023 if l_return_status = fnd_api.g_ret_sts_success then
3024 --remove_grp_of_unwanted_trip(l_return_status, p_plan_id, p_release_id, p_release_mode);
3025 compute_statistics (l_return_status, p_plan_id, p_release_id);
3026 if l_return_status = fnd_api.g_ret_sts_success then
3027 reset_grp_ids_in_sequence_of_1(l_return_status, l_group_tab, p_plan_id, p_release_id);
3028 if l_return_status = fnd_api.g_ret_sts_success then
3029 populate_deliveries(l_return_status, p_release_id);
3030 if l_return_status = fnd_api.g_ret_sts_success then
3031 populate_interface_tables(l_return_status, p_release_id);
3032 if l_return_status = fnd_api.g_ret_sts_success then
3033 if g_apply_to_te = 1 then
3034 print_info(g_log_flow_of_control,'g_apply_to_te : ' ||g_apply_to_te);
3035 l_input_rec.ACTION_CODE := wsh_tp_release_grp.G_ACTION_RELEASE;
3036 l_input_rec.COMMIT_FLAG := fnd_api.G_FALSE;
3037
3038 wsh_tp_release_grp.action( l_group_tab -- list of group ids created in release process above
3039 , l_input_rec
3040 , l_output_rec
3041 , l_return_status);
3042 -- l_return_status -> fnd_api.g_ret_sts_success ---> all group released to TE
3043 -- l_return_status -> WSH_UTIL_CORE.G_RET_STS_WARNING ---> at least one group failed and at least one successful
3044 -- l_return_status -> fnd_api.g_ret_sts_error ---> all groups failed
3045 -- l_return_status -> fnd_api.g_ret_sts_unexp_error ---> unexpected error occured in TE
3046 end if;
3047 if l_return_status = fnd_api.g_ret_sts_unexp_error then
3048 rollback;
3052 update_tp_tables (l_return_status, l_group_tab, p_plan_id, p_release_id, p_release_mode, l_release_start_date);
3049 else
3050 if g_update_tp_tables = 1 then
3051 print_info(g_log_flow_of_control,'g_update_tp_tables : ' ||g_update_tp_tables);
3053 if l_return_status <> fnd_api.g_ret_sts_success then
3054 rollback;
3055 end if;
3056 end if;
3057 print_info(g_log_flow_of_control,'g_purge_interface_table : ' ||g_purge_interface_table);
3058 if g_purge_interface_table = 1 then
3059 l_input_rec.ACTION_CODE := wsh_tp_release_grp.G_ACTION_PURGE;
3060 l_input_rec.COMMIT_FLAG := fnd_api.G_FALSE;
3061
3062 wsh_tp_release_grp.action( l_group_tab -- list of group ids created in release process above
3063 , l_input_rec
3064 , l_output_rec
3065 , l_return_status);
3066 end if;
3067 end if;
3068 end if;
3069 end if;
3070 end if;
3071 end if;
3072 end if;
3073 end if;
3074 end if;
3075 end if;
3076 log_statistics;
3077 -- delete records of current release_id
3078 print_info(g_log_flow_of_control,'g_purge_mst_release_temp : ' ||g_purge_mst_release_temp);
3079 if g_release_data = 'TRIP' then
3080 delete from mst_release_temp
3081 where release_id = p_release_id;
3082 end if;
3083 if g_purge_mst_release_temp = 0 then
3084 insert into mst_release_temp
3085 ( release_id
3086 , group_id
3087 , plan_id
3088 , trip_id
3089 , trip_number
3090 , planned_flag
3091 , release_status
3092 , trip_start_date
3093 , cm_id_of_trip
3094 , continuous_move_id
3095 , delivery_id
3096 , out_of_scope
3097 , trip_process_flag
3098 , selected_trips
3099 , trip_id_iface
3100 , continuous_move_id_iface
3101 , delivery_id_iface
3102 , stop_id_iface
3103 , continuous_move_sequence
3104 , sr_cm_trip_id
3105 , sr_trip_id
3106 , stop_id
3107 , sr_delivery_id
3108 , delivery_detail_id_iface
3109 , delivery_detail_id
3110 , sr_delivery_detail_id
3111 , sr_delivery_assignment_id
3112 , sr_stop_id, status_code
3113 , pickup_date
3114 , dropoff_date
3115 , pickup_location_id
3116 , dropoff_location_id
3117 , customer_id
3118 , gross_weight
3119 , net_weight
3120 , weight_uom
3121 , volume
3122 , volume_uom
3123 , currency_uom
3124 , organization_id
3125 , delivery_number
3126 , compile_designator
3127 , earliest_pickup_date
3128 , latest_pickup_date
3129 , earliest_acceptable_date
3130 , latest_acceptable_date
3131 , inventory_item_id
3132 , carrier_id
3133 , ship_method_code
3134 , mode_of_transport
3138 , stop_location_id
3135 , load_tender_status
3136 , lane_id, service_level
3137 , cm_trip_number
3139 , stop_sequence_number
3140 , planned_arrival_date
3141 , planned_departure_date
3142 , departure_gross_weight
3143 , departure_net_weight
3144 , departure_volume
3145 , departure_fill_percent
3146 , wkend_layover_stops
3147 , wkday_layover_stops
3148 , distance_to_next_stop
3149 , pln_loading_start_time
3150 , pln_loading_end_time
3151 , pln_unloading_start_time
3152 , pln_unloading_end_time
3153 , source_code
3154 , ship_from_location_id
3155 , ship_to_location_id
3156 , requested_quantity
3157 , source_header_number
3158 , ship_set_id
3159 , arrival_set_id
3160 , org_id
3161 , container_flag
3162 , source_line_number
3163 , split_from_delivery_detail_id
3164 , line_direction
3165 , po_shipment_number
3166 , po_shipment_line_number
3167 , src_requested_quantity_uom
3168 , src_requested_quantity
3169 , shipment_direction
3170 , supplier_id
3171 , party_id
3172 )
3173 (
3174 select release_id
3175 , group_id
3176 , plan_id
3177 , trip_id
3178 , trip_number
3179 , planned_flag
3180 , release_status
3181 , trip_start_date
3182 , cm_id_of_trip
3183 , continuous_move_id
3184 , delivery_id
3185 , out_of_scope
3186 , trip_process_flag
3187 , selected_trips
3188 , trip_id_iface
3189 , continuous_move_id_iface
3190 , delivery_id_iface
3191 , stop_id_iface
3192 , continuous_move_sequence
3193 , sr_cm_trip_id
3194 , sr_trip_id
3195 , stop_id
3196 , sr_delivery_id
3197 , delivery_detail_id_iface
3198 , delivery_detail_id
3199 , sr_delivery_detail_id
3200 , sr_delivery_assignment_id
3201 , sr_stop_id, status_code
3202 , pickup_date
3203 , dropoff_date
3204 , pickup_location_id
3205 , dropoff_location_id
3206 , customer_id
3207 , gross_weight
3208 , net_weight
3209 , weight_uom
3210 , volume
3211 , volume_uom
3212 , currency_uom
3213 , organization_id
3214 , delivery_number
3215 , compile_designator
3216 , earliest_pickup_date
3217 , latest_pickup_date
3218 , earliest_acceptable_date
3219 , latest_acceptable_date
3220 , inventory_item_id
3221 , carrier_id
3222 , ship_method_code
3223 , mode_of_transport
3224 , load_tender_status
3225 , lane_id, service_level
3226 , cm_trip_number
3227 , stop_location_id
3228 , stop_sequence_number
3229 , planned_arrival_date
3230 , planned_departure_date
3231 , departure_gross_weight
3232 , departure_net_weight
3233 , departure_volume
3234 , departure_fill_percent
3235 , wkend_layover_stops
3236 , wkday_layover_stops
3237 , distance_to_next_stop
3238 , pln_loading_start_time
3239 , pln_loading_end_time
3240 , pln_unloading_start_time
3241 , pln_unloading_end_time
3242 , source_code
3243 , ship_from_location_id
3244 , ship_to_location_id
3245 , requested_quantity
3246 , source_header_number
3247 , ship_set_id
3248 , arrival_set_id
3249 , org_id
3250 , container_flag
3251 , source_line_number
3252 , split_from_delivery_detail_id
3253 , line_direction
3254 , po_shipment_number
3255 , po_shipment_line_number
3256 , src_requested_quantity_uom
3257 , src_requested_quantity
3258 , shipment_direction
3259 , supplier_id
3260 , party_id
3261 from mst_release_temp_gt
3262 where release_id = p_release_id
3263 );
3264 end if;
3265 commit;
3266 end if;
3267 print_info(g_log_flow_of_control,'do_release : Release Id = ' ||p_release_id|| ' Program ended');
3268 exception
3269 when others then
3270 print_info(g_log_flow_of_control,'do_release : Release Id = ' ||p_release_id|| ' Unexpected error ' || to_char(sqlcode) || ':' || SQLERRM);
3271 set_concurrent_status('ERROR',get_seeded_message('MST_REL_BK_MESSAGE_47') || to_char(sqlcode) || ':' || SQLERRM);
3272 end do_release;
3273
3274 procedure release_load (p_err_code out nocopy varchar2
3275 , p_err_buff out nocopy varchar2
3276 , p_plan_id in number
3277 , p_release_id in number
3278 , p_release_mode in number) is
3279
3280 l_return_status varchar2(1);
3281 begin
3282 initialize_package_variables;
3283 set_release_debug_flags;
3284 print_info(g_log_flow_of_control,'release_load : Release Id = ' ||p_release_id|| ' Program started');
3285 g_release_data := 'TRIP';
3286 pull_into_global_temp_table (l_return_status, p_release_id);
3287 if l_return_status = fnd_api.g_ret_sts_success then
3288 do_release (p_plan_id, p_release_id, p_release_mode);
3289 end if;
3290
3291 print_info(g_log_flow_of_control,'release_load : Release Id = ' ||p_release_id|| ' Program ended');
3292 exception
3293 when others then
3294 delete from mst_release_temp
3295 where release_id = p_release_id;
3296 commit;
3297 log_statistics;
3298 print_info(g_log_flow_of_control,'release_load : Release Id = ' ||p_release_id|| ' Unexpected error ' || to_char(sqlcode) || ':' || SQLERRM);
3299 set_concurrent_status('ERROR',get_seeded_message('MST_REL_BK_MESSAGE_47') || to_char(sqlcode) || ':' || SQLERRM);
3300 end release_load;
3301
3302 function get_cond (p_condition in varchar2)
3303 return varchar2 is
3304 l_str_cond varchar2(30) := '=';
3305 begin
3306 if p_condition = 1 then
3307 l_str_cond := '=';
3311 l_str_cond := '<';
3308 elsif p_condition = 2 then
3309 l_str_cond := '<>';
3310 elsif p_condition = 3 then
3312 elsif p_condition = 4 then
3313 l_str_cond := '<=';
3314 elsif p_condition = 5 then
3315 l_str_cond := '>=';
3316 elsif p_condition = 6 then
3317 l_str_cond := '>';
3318 elsif p_condition = 7 then
3319 l_str_cond := 'between';
3320 end if;
3321 return l_str_cond;
3322 exception
3323 when others then
3324 return '=';
3325 end get_cond;
3326
3327 procedure insert_plan_trips_ruleset(x_return_status out nocopy varchar2, p_plan_id in number, p_release_id in pls_integer) is
3328
3329 AREL_CARRIER_CODE constant number := 1;
3330 AREL_SUPPLIER_CODE constant number := 2;
3331 AREL_CUSTOMER_CODE constant number := 3;
3332 AREL_UTILIZATION_CODE constant number := 4;
3333 AREL_REMAINING_TIME_CODE constant number := 5;
3334 AREL_CIRCUITY_CODE constant number := 6;
3335 AREL_MODE_CODE constant number := 7;
3336
3337 l_rule_id mst_rel_rule_associations.rule_id%type;
3338 l_rule_set_id mst_auto_rel_rule_sets.rule_set_id%type;
3339 l_auto_release_restriction mst_auto_rel_rule_sets.auto_release_restriction%type;
3340 l_attribute_code mst_rel_rule_conditions.attribute_code%type;
3341 l_condition mst_rel_rule_conditions.condition%type;
3342 l_from_number_value mst_rel_rule_conditions.from_number_value%type;
3343 l_to_number_value mst_rel_rule_conditions.to_number_value%type;
3344 l_from_char_value mst_rel_rule_conditions.from_char_value%type;
3345 l_to_char_value mst_rel_rule_conditions.to_char_value%type;
3346 l_from_date_value mst_rel_rule_conditions.from_date_value%type;
3347 l_to_date_value mst_rel_rule_conditions.to_date_value%type;
3348
3349 cursor cur_rules (l_plan_id in number)
3350 is
3351 select marrs.rule_set_id
3352 , marrs.auto_release_restriction
3353 , mrra.rule_id
3354 from mst_plans mp
3355 , mst_auto_rel_rule_sets marrs
3356 , mst_rel_rule_associations mrra
3357 where mp.plan_id = l_plan_id
3358 and mp.auto_rel_rule_set_id = marrs.rule_set_id
3359 and marrs.rule_set_id = mrra.rule_set_id;
3360
3361 cursor cur_rule_condition (l_rule_id in number)
3362 is
3363 select mrrc.attribute_code
3364 , mrrc.condition
3365 , mrrc.from_number_value
3366 , mrrc.to_number_value
3367 , mrrc.from_char_value
3368 , mrrc.to_char_value
3369 , mrrc.from_date_value
3370 , mrrc.to_date_value
3371 from mst_rel_rule_conditions mrrc
3372 where mrrc.rule_id = l_rule_id;
3373
3374 l_where_clause varchar2(4000) := null;
3375
3376 begin
3377 print_info(g_log_flow_of_control,'insert_plan_trips_ruleset : Program started');
3378 open cur_rules(p_plan_id);
3379 loop
3380 fetch cur_rules into l_rule_set_id
3381 , l_auto_release_restriction
3382 , l_rule_id;
3383 exit when cur_rules%notfound;
3384 l_where_clause := l_where_clause || ' ( 1 = 1 ';
3385 open cur_rule_condition(l_rule_id);
3386 loop
3387 fetch cur_rule_condition into l_attribute_code
3388 , l_condition
3389 , l_from_number_value
3390 , l_to_number_value
3391 , l_from_char_value
3392 , l_to_char_value
3393 , l_from_date_value
3394 , l_to_date_value;
3395 exit when cur_rule_condition%notfound;
3396
3397 if (l_attribute_code = AREL_CARRIER_CODE) then
3398 -- CARRIER (=, !=)
3399 l_where_clause := l_where_clause || ' and ' || ' mt.carrier_id ' || get_cond(l_condition) || ' ' || nvl(l_from_number_value,0);
3400 elsif (l_attribute_code = AREL_SUPPLIER_CODE) then
3401 -- SUPPLIER (=, !=)
3402 l_where_clause := l_where_clause || ' and ' || ' exists (select 1 from mst_deliveries md, mst_delivery_legs mdl';
3403 l_where_clause := l_where_clause || ' where mt.plan_id = mdl.plan_id and mt.trip_id = mdl.trip_id and mdl.plan_id = md.plan_id';
3404 l_where_clause := l_where_clause || ' and mdl.delivery_id = md.delivery_id and md.supplier_id ' || get_cond(l_condition);
3405 l_where_clause := l_where_clause || ' ' || nvl(l_from_number_value,0) || ')';
3406 elsif (l_attribute_code = AREL_CUSTOMER_CODE) then
3407 -- CUSTOMER (=, !=)
3408 l_where_clause := l_where_clause || ' and ' || ' exists (select 1 from mst_deliveries md, mst_delivery_legs mdl';
3409 l_where_clause := l_where_clause || ' where mt.plan_id = mdl.plan_id and mt.trip_id = mdl.trip_id and mdl.plan_id = md.plan_id';
3410 l_where_clause := l_where_clause || ' and mdl.delivery_id = md.delivery_id and md.customer_id ' || get_cond(l_condition);
3411 l_where_clause := l_where_clause || ' ' || nvl(l_from_number_value,0) || ')';
3412 elsif (l_attribute_code = AREL_MODE_CODE) then
3413 -- MODE (=, !=)
3414 if l_from_number_value = 1 then
3415 l_from_char_value := 'TRUCK';
3416 elsif l_from_number_value = 2 then
3417 l_from_char_value := 'LTL';
3418 else
3419 l_from_char_value := 'PARCEL';
3420 end if;
3421 l_where_clause := l_where_clause || ' and ' || ' mt.mode_of_transport '|| get_cond(l_condition)|| ' ''' || l_from_char_value || '''';
3422 elsif (l_attribute_code = AREL_UTILIZATION_CODE) then
3423 -- UTILIZATION (=, !=, <, <=, >, >=, is between)
3424 if (l_condition = 7) then -- between
3425 l_where_clause := l_where_clause || ' and ' || ' (greatest(mt.peak_weight_utilization,mt.peak_volume_utilization,mt.peak_pallet_utilization)*100 ' || get_cond(l_condition) || ' ' || l_from_number_value || ' and ' || l_to_number_value || ')';
3426 else -- all other conditions
3427 l_where_clause := l_where_clause || ' and ' || ' greatest(mt.peak_weight_utilization,mt.peak_volume_utilization,mt.peak_pallet_utilization)*100 ' || get_cond(l_condition) || ' ' || l_from_number_value;
3428 end if;
3429 elsif (l_attribute_code = AREL_REMAINING_TIME_CODE) then
3430 -- REMAINING_TIME (=, !=, <, <=, >, >=, is between)
3431 if (l_condition = 7) then -- between
3432 l_where_clause := l_where_clause || ' and ' || ' ((mt.trip_start_date-sysdate)*24 ' || get_cond(l_condition) || ' ' || l_from_number_value || ' and ' || l_to_number_value || ')';
3433 else
3434 l_where_clause := l_where_clause || ' and ' || ' (mt.trip_start_date-sysdate)*24 ' || get_cond(l_condition) || ' ' || l_from_number_value;
3435 end if;
3436 elsif (l_attribute_code = AREL_CIRCUITY_CODE) then
3437 -- CIRCUITY (=, !=, <, <=, >, >=, is between)
3438 if (l_condition = 7) then -- between
3439 l_where_clause := l_where_clause || ' and ' || ' (((mt.total_trip_distance/mt.total_direct_distance -1)*100) ' || get_cond(l_condition) || ' ' || l_from_number_value || ' and ' || l_to_number_value || ')';
3440 else
3441 l_where_clause := l_where_clause || ' and ' || ' ((mt.total_trip_distance/mt.total_direct_distance -1)*100) ' || get_cond(l_condition) || ' ' || l_from_number_value;
3442 end if;
3443 end if;
3444
3448 end loop;
3445 end loop;
3446 close cur_rule_condition;
3447 l_where_clause := l_where_clause || ' ) OR ';
3449
3450 l_where_clause := substr(l_where_clause,1,length(l_where_clause)-3);
3451 if l_where_clause is null or l_where_clause = ' ( 1 = 1 ) ' then
3452 --print_info(g_log_must_message,'Rule set / rule / rule condition not defined to auto-release this plan. No trip eligible for release.');
3453 print_info(g_log_must_message,get_seeded_message('MST_REL_BK_MESSAGE_39'));
3454 x_return_status := fnd_api.g_ret_sts_error;
3455 log_statistics;
3456 --set_concurrent_status('WARNING', 'Zero Groups attempted to release.');
3457 set_concurrent_status('WARNING', get_seeded_message('MST_REL_BK_MESSAGE_39'));
3458 else
3459 print_info(g_log_ruleset_where_clause,l_where_clause);
3460
3461 -- insert qualified trips into mst_release_temp_gt
3462 execute immediate
3463 'insert into mst_release_temp_gt
3464 (
3465 release_id
3466 , group_id
3467 , plan_id
3468 , trip_id
3469 , sr_trip_id
3470 , trip_number
3471 , planned_flag
3472 , release_status
3473 , trip_start_date
3474 , cm_id_of_trip
3475 , continuous_move_sequence
3476 , out_of_scope
3477 , trip_process_flag
3478 , selected_trips
3479 , trip_id_iface
3480 , status_code
3481 , inventory_item_id
3482 , organization_id
3483 , carrier_id
3484 , ship_method_code
3485 , compile_designator
3486 , mode_of_transport
3487 , load_tender_status
3488 , lane_id
3489 , service_level
3490 )
3491 (
3492 select '||p_release_id||'
3493 , null
3494 , mt.plan_id
3495 , mt.trip_id
3496 , mt.sr_trip_id
3497 , mt.trip_number
3498 , decode(nvl('||l_auto_release_restriction||',3),1,1,2,decode(mt.planned_flag,3,2,mt.planned_flag),mt.planned_flag)
3499 , mt.release_status
3500 , mt.trip_start_date
3501 , mt.continuous_move_id
3502 , mt.continuous_move_sequence
3503 , mt.out_of_scope
3504 , null
3505 , 1
3506 , wsh_trips_interface_s.nextval
3507 , mt.status_code
3508 , fvt.inventory_item_id
3509 , fvt.organization_id
3510 , mt.carrier_id
3511 , mt.ship_method_code
3512 , mp.compile_designator
3513 , mt.mode_of_transport
3514 , mt.load_tender_status
3515 , mt.lane_id
3516 , mt.service_level
3517 from mst_plans mp
3518 , mst_trips mt
3519 , fte_vehicle_types fvt
3520 where mt.plan_id = '||p_plan_id||'
3521 and mt.plan_id = mp.plan_id
3522 and mt.vehicle_type_id = fvt.vehicle_type_id (+) and ( '||l_where_clause||' ))';
3523
3524 g_where_clause := l_where_clause; -- to use in procedure remove_grp_of_unwanted_trip to eliminate groups of disqualified trips
3525 x_return_status := fnd_api.g_ret_sts_success;
3526 end if;
3527 print_info(g_log_flow_of_control,'insert_plan_trips_ruleset : Program ended');
3528 exception
3529 when others then
3530 x_return_status := fnd_api.g_ret_sts_unexp_error;
3531 print_info(g_log_flow_of_control,'insert_plan_trips_ruleset : ' || to_char(sqlcode) || ':' || SQLERRM);
3532 set_concurrent_status('ERROR',get_seeded_message('MST_REL_BK_MESSAGE_47') || to_char(sqlcode) || ':' || SQLERRM);
3533 end insert_plan_trips_ruleset;
3534
3535 procedure insert_plan_trips (x_return_status out nocopy varchar2, p_plan_id in number, p_release_id in number) is
3536 begin
3537 print_info(g_log_flow_of_control,'insert_plan_trips : Program started');
3538 insert into mst_release_temp_gt
3539 (
3540 release_id
3541 , group_id
3542 , plan_id
3543 , trip_id
3544 , sr_trip_id
3545 , trip_number
3546 , planned_flag
3547 , release_status
3548 , trip_start_date
3549 , cm_id_of_trip
3550 , continuous_move_sequence
3551 , out_of_scope
3552 , trip_process_flag
3553 , selected_trips
3554 , trip_id_iface
3555 , status_code
3556 , inventory_item_id
3557 , organization_id
3558 , carrier_id
3559 , ship_method_code
3560 , compile_designator
3561 , mode_of_transport
3562 , load_tender_status
3563 , lane_id
3564 , service_level
3565 )
3566 (
3567 select p_release_id
3568 , null
3569 , mt.plan_id
3570 , mt.trip_id
3571 , mt.sr_trip_id
3572 , mt.trip_number
3573 , mt.planned_flag
3574 , mt.release_status
3575 , mt.trip_start_date
3576 , mt.continuous_move_id
3577 , mt.continuous_move_sequence
3578 , mt.out_of_scope
3579 , null
3580 , 1
3581 , wsh_trips_interface_s.nextval
3582 , mt.status_code
3583 , fvt.inventory_item_id
3584 , fvt.organization_id
3585 , mt.carrier_id
3586 , mt.ship_method_code
3587 , mp.compile_designator
3588 , mt.mode_of_transport
3589 , mt.load_tender_status
3590 , mt.lane_id
3591 , mt.service_level
3592 from mst_plans mp
3593 , mst_trips mt
3594 , fte_vehicle_types fvt
3595 where mt.plan_id = p_plan_id
3596 and mt.plan_id = mp.plan_id
3597 and mt.vehicle_type_id = fvt.vehicle_type_id (+)
3598 );
3599
3600 x_return_status := fnd_api.g_ret_sts_success;
3601 print_info(g_log_flow_of_control,'insert_plan_trips : Program ended');
3602 exception
3603 when others then
3604 x_return_status := fnd_api.g_ret_sts_unexp_error;
3608
3605 print_info(g_log_flow_of_control,'insert_plan_trips : ' || to_char(sqlcode) || ':' || SQLERRM);
3606 set_concurrent_status('ERROR',get_seeded_message('MST_REL_BK_MESSAGE_47') || to_char(sqlcode) || ':' || SQLERRM);
3607 end insert_plan_trips;
3609 procedure truncate_mst_table( p_table_name in varchar2) is
3610 l_retval boolean;
3611 l_dummy1 varchar2(32);
3612 l_dummy2 varchar2(32);
3613 l_mst_schema varchar2(32);
3614 begin
3615 l_retval := FND_INSTALLATION.GET_APP_INFO('MST', l_dummy1, l_dummy2, l_mst_schema);
3616 execute immediate 'truncate table '||l_mst_schema||'.'||p_table_name;
3617 commit;
3618 end truncate_mst_table;
3619
3620 procedure do_house_keeping is
3621 l_Message_Text varchar2(1000);
3622 begin
3623 --print_info(g_log_must_message,'do_house_keeping : Program started');
3624 print_info(g_log_must_message,get_seeded_message('MST_REL_BK_MESSAGE_40'));
3625 print_info(g_log_must_message,'');
3626 --print_info(g_log_must_message,g_delete_record_count||' number of records will be deleted in one loop');
3627 fnd_message.set_name('MST','MST_REL_BK_MESSAGE_41');
3628 fnd_message.set_token('N1',to_char(g_delete_record_count));
3629 l_Message_Text := fnd_message.get;
3630 print_info(g_log_must_message,l_Message_Text);
3631 --print_info(g_log_must_message,'Total number of loops for one delete attempt are '||g_delete_record_count_loop);
3632 fnd_message.set_name('MST','MST_REL_BK_MESSAGE_42');
3633 fnd_message.set_token('N1',to_char(g_delete_record_count_loop));
3634 l_Message_Text := fnd_message.get;
3635 print_info(g_log_must_message,l_Message_Text);
3636 print_info(g_log_must_message,'');
3637
3638 if g_truncate_mrt = 1 then
3639 truncate_mst_table('mst_release_temp');
3640 --print_info(g_log_must_message,'mst_release_temp truncated.');
3641 fnd_message.set_name('MST','MST_REL_BK_MESSAGE_43');
3642 fnd_message.set_token('TABLE_NAME','MST_RELEASE_TEMP');
3643 l_Message_Text := fnd_message.get;
3644 print_info(g_log_must_message,l_Message_Text);
3645 else -- delete from table
3646 for i in 1..g_delete_record_count_loop loop
3647 delete from mst_release_temp
3648 where rownum < g_delete_record_count;
3649 --print_info(g_log_must_message,'Attempted to delete '||sql%rowcount||' records from mst_release_temp.');
3650 fnd_message.set_name('MST','MST_REL_BK_MESSAGE_44');
3651 fnd_message.set_token('N1',to_char(sql%rowcount));
3652 fnd_message.set_token('TABLE_NAME','MST_RELEASE_TEMP');
3653 l_Message_Text := fnd_message.get;
3654 print_info(g_log_must_message,l_Message_Text);
3655 commit;
3656 end loop;
3657 end if;
3658 print_info(g_log_must_message,'');
3659
3660 for i in 1..g_delete_record_count_loop loop
3661 delete from wsh_trips_interface
3662 where interface_action_code = g_tp_release_code
3663 and rownum < g_delete_record_count;
3664 --print_info(g_log_must_message,'Attempted to delete '||sql%rowcount||' records from wsh_trips_interface.');
3665 fnd_message.set_name('MST','MST_REL_BK_MESSAGE_44');
3666 fnd_message.set_token('N1',to_char(sql%rowcount));
3667 fnd_message.set_token('TABLE_NAME','WSH_TRIPS_INTERFACE');
3668 l_Message_Text := fnd_message.get;
3669 print_info(g_log_must_message,l_Message_Text);
3670 commit;
3671 end loop;
3672 print_info(g_log_must_message,'');
3673
3674 for i in 1..g_delete_record_count_loop loop
3675 delete from fte_moves_interface
3676 where interface_action_code = g_tp_release_code
3677 and rownum < g_delete_record_count;
3678 --print_info(g_log_must_message,'Attempted to delete '||sql%rowcount||' records from fte_moves_interface.');
3679 fnd_message.set_name('MST','MST_REL_BK_MESSAGE_44');
3680 fnd_message.set_token('N1',to_char(sql%rowcount));
3681 fnd_message.set_token('TABLE_NAME','FTE_MOVES_INTERFACE');
3682 l_Message_Text := fnd_message.get;
3683 print_info(g_log_must_message,l_Message_Text);
3684 commit;
3685 end loop;
3686 print_info(g_log_must_message,'');
3687
3688 for i in 1..g_delete_record_count_loop loop
3689 delete from fte_trip_moves_interface
3690 where interface_action_code = g_tp_release_code
3691 and rownum < g_delete_record_count;
3692 --print_info(g_log_must_message,'Attempted to delete '||sql%rowcount||' records from fte_trip_moves_interface.');
3693 fnd_message.set_name('MST','MST_REL_BK_MESSAGE_44');
3694 fnd_message.set_token('N1',to_char(sql%rowcount));
3695 fnd_message.set_token('TABLE_NAME','FTE_TRIP_MOVES_INTERFACE');
3696 l_Message_Text := fnd_message.get;
3697 print_info(g_log_must_message,l_Message_Text);
3698 commit;
3699 end loop;
3700 print_info(g_log_must_message,'');
3701
3702 for i in 1..g_delete_record_count_loop loop
3703 delete from wsh_trip_stops_interface
3704 where interface_action_code = g_tp_release_code
3705 and rownum < g_delete_record_count;
3706 print_info(g_log_must_message,'Attempted to delete '||sql%rowcount||' records from wsh_trip_stops_interface.');
3707 fnd_message.set_name('MST','MST_REL_BK_MESSAGE_44');
3708 fnd_message.set_token('N1',to_char(sql%rowcount));
3709 fnd_message.set_token('TABLE_NAME','WSH_TRIP_STOPS_INTERFACE');
3710 l_Message_Text := fnd_message.get;
3711 print_info(g_log_must_message,l_Message_Text);
3712 commit;
3713 end loop;
3714 print_info(g_log_must_message,'');
3715
3716 for i in 1..g_delete_record_count_loop loop
3717 delete from wsh_new_del_interface
3718 where interface_action_code = g_tp_release_code
3719 and rownum < g_delete_record_count;
3720 print_info(g_log_must_message,'Attempted to delete '||sql%rowcount||' records from wsh_new_del_interface.');
3724 l_Message_Text := fnd_message.get;
3721 fnd_message.set_name('MST','MST_REL_BK_MESSAGE_44');
3722 fnd_message.set_token('N1',to_char(sql%rowcount));
3723 fnd_message.set_token('TABLE_NAME','WSH_NEW_DEL_INTERFACE');
3725 print_info(g_log_must_message,l_Message_Text);
3726 commit;
3727 end loop;
3728 print_info(g_log_must_message,'');
3729
3730 for i in 1..g_delete_record_count_loop loop
3731 delete from wsh_del_legs_interface
3732 where interface_action_code = g_tp_release_code
3733 and rownum < g_delete_record_count;
3734 --print_info(g_log_must_message,'Attempted to delete '||sql%rowcount||' records from wsh_del_legs_interface.');
3735 fnd_message.set_name('MST','MST_REL_BK_MESSAGE_44');
3736 fnd_message.set_token('N1',to_char(sql%rowcount));
3737 fnd_message.set_token('TABLE_NAME','WSH_DEL_LEGS_INTERFACE');
3738 l_Message_Text := fnd_message.get;
3739 print_info(g_log_must_message,l_Message_Text);
3740 commit;
3741 end loop;
3742 print_info(g_log_must_message,'');
3743
3744 for i in 1..g_delete_record_count_loop loop
3745 delete from wsh_del_assgn_interface
3746 where interface_action_code = g_tp_release_code
3747 and rownum < g_delete_record_count;
3748 --print_info(g_log_must_message,'Attempted to delete '||sql%rowcount||' records from wsh_del_assgn_interface.');
3749 fnd_message.set_name('MST','MST_REL_BK_MESSAGE_44');
3750 fnd_message.set_token('N1',to_char(sql%rowcount));
3751 fnd_message.set_token('TABLE_NAME','WSH_DEL_ASSGN_INTERFACE');
3752 l_Message_Text := fnd_message.get;
3753 print_info(g_log_must_message,l_Message_Text);
3754 commit;
3755 end loop;
3756 print_info(g_log_must_message,'');
3757
3758 for i in 1..g_delete_record_count_loop loop
3759 delete from wsh_del_details_interface
3760 where interface_action_code = g_tp_release_code
3761 and rownum < g_delete_record_count;
3762 --print_info(g_log_must_message,'Attempted to delete '||sql%rowcount||' records from wsh_del_details_interface.');
3763 fnd_message.set_name('MST','MST_REL_BK_MESSAGE_44');
3764 fnd_message.set_token('N1',to_char(sql%rowcount));
3765 fnd_message.set_token('TABLE_NAME','WSH_DEL_DETAILS_INTERFACE');
3766 l_Message_Text := fnd_message.get;
3767 print_info(g_log_must_message,l_Message_Text);
3768 commit;
3769 end loop;
3770 print_info(g_log_must_message,'');
3771
3772 for i in 1..g_delete_record_count_loop loop
3773 delete from wsh_interface_errors
3774 where interface_action_code = g_tp_release_code
3775 and rownum < g_delete_record_count;
3776 --print_info(g_log_must_message,'Attempted to delete '||sql%rowcount||' records from wsh_interface_errors.');
3777 fnd_message.set_name('MST','MST_REL_BK_MESSAGE_44');
3778 fnd_message.set_token('N1',to_char(sql%rowcount));
3779 fnd_message.set_token('TABLE_NAME','WSH_INTERFACE_ERRORS');
3780 l_Message_Text := fnd_message.get;
3781 print_info(g_log_must_message,l_Message_Text);
3782 commit;
3783 end loop;
3784 print_info(g_log_must_message,'');
3785
3786 --print_info(g_log_must_message,'do_house_keeping : Program ended');
3787 print_info(g_log_must_message,get_seeded_message('MST_REL_BK_MESSAGE_45'));
3788 end do_house_keeping;
3789
3790 procedure release_plan (p_err_code out nocopy varchar2
3791 , p_err_buff out nocopy varchar2
3792 , p_plan_id in number
3793 , p_release_id in number
3794 , p_release_mode in number) is
3795 -- cursor to check the auto_release flag
3796 cursor c_auto_rel (l_plan_id in number)
3797 is
3798 select auto_release
3799 from mst_plans
3800 where plan_id = l_plan_id;
3801
3802 l_auto_release number;
3803
3804 l_return_status varchar2(1) := 'U';
3805 l_Message_Text varchar2(1000);
3806 begin
3807 initialize_package_variables;
3808 set_release_debug_flags;
3809 if g_house_keeping = 1 then
3810 do_house_keeping;
3811 print_info(g_log_must_message,'...................................................................................');
3812 --print_info(g_log_must_message,'Plan NOT released. ONLY house keeping done.');
3813 print_info(g_log_must_message,get_seeded_message('MST_REL_BK_MESSAGE_46'));
3814 --print_info(g_log_must_message,'Now make change in profile "tp:release debug" to release plan.');
3815 print_info(g_log_must_message,get_seeded_message('MST_REL_BK_MESSAGE_48'));
3816 elsif g_house_keeping = 2 then -- Manual-release with ruleset mode will be run. This is not for customer.
3817 print_info(g_log_must_message,'Simulated Auto-Release with Ruleset: Manual-release with ruleset mode will be run. This is not for customer.');
3818 g_release_data := 'TRIP';
3819 g_auto_release := 3;
3820 insert_plan_trips_ruleset(l_return_status, p_plan_id, p_release_id);
3821 if l_return_status = fnd_api.g_ret_sts_success then
3822 do_release (p_plan_id, p_release_id, 1);
3823 end if;
3824 elsif g_house_keeping = 3 then -- Manual-release as auto-release will be run. This is not for customer.
3828 if l_return_status = fnd_api.g_ret_sts_success then
3825 print_info(g_log_must_message,'Simulated Auto-Release of Entire Plan: Manual-release as auto-release will be run. This is not for customer.');
3826 g_release_data := 'PLAN';
3827 insert_plan_trips (l_return_status, p_plan_id, p_release_id);
3829 do_release (p_plan_id, p_release_id, 1);
3830 end if;
3831 else
3832 print_info(g_log_flow_of_control,'release_plan : Release Id = ' ||p_release_id|| ' Program started');
3833
3834 if nvl(p_release_mode,2) = 2 then -- called from ui (manual release entire plan)
3835 g_release_data := 'PLAN';
3836 insert_plan_trips (l_return_status, p_plan_id, p_release_id);
3837 if l_return_status = fnd_api.g_ret_sts_success then
3838 do_release (p_plan_id, p_release_id, p_release_mode);
3839 end if;
3840 elsif p_release_mode = 3 then -- called from ui (manual release entire plan with ruleset)
3841 g_release_data := 'TRIP'; -- added this portion of elsif due to bug 3570370
3842 g_auto_release := 3;
3843 insert_plan_trips_ruleset(l_return_status, p_plan_id, p_release_id);
3844 if l_return_status = fnd_api.g_ret_sts_success then
3845 do_release (p_plan_id, p_release_id, 1); -- making release mode (third parameter) to 1 to simulate auto-release
3846 end if;
3847 elsif p_release_mode = 1 then -- called from engine for auto release
3848
3849 open c_auto_rel (p_plan_id);
3850 fetch c_auto_rel into l_auto_release;
3851 close c_auto_rel;
3852
3853 if l_auto_release = 1 then -- no auto release
3854 null;
3855 elsif l_auto_release = 2 then -- release entire plan as per planned-flag in tables
3856 g_release_data := 'PLAN';
3857 insert_plan_trips (l_return_status, p_plan_id, p_release_id);
3858 if l_return_status = fnd_api.g_ret_sts_success then
3859 do_release (p_plan_id, p_release_id, p_release_mode);
3860 end if;
3861 elsif l_auto_release = 3 then -- release plan as per the rule set defined in plan option
3862 g_auto_release := 3;
3863 g_release_data := 'TRIP';
3864 insert_plan_trips_ruleset(l_return_status, p_plan_id, p_release_id);
3865 if l_return_status = fnd_api.g_ret_sts_success then
3866 do_release (p_plan_id, p_release_id, p_release_mode);
3867 end if;
3868 end if;
3869 end if;
3870 print_info(g_log_flow_of_control,'release_plan : Release Id = ' ||p_release_id|| ' Program ended');
3871 end if;
3872 exception
3873 when others then
3874 log_statistics;
3875 print_info(g_log_flow_of_control,'release_plan : Release Id = ' ||p_release_id|| ' Unexpected error ' || to_char(sqlcode) || ':' || SQLERRM);
3876 set_concurrent_status('ERROR',get_seeded_message('MST_REL_BK_MESSAGE_47') || to_char(sqlcode) || ':' || SQLERRM);
3877 end release_plan;
3878
3879 procedure submit_release_request ( p_request_id OUT NOCOPY NUMBER
3880 , p_release_type IN VARCHAR2 -- 'LOAD' or 'PLAN'
3881 , p_plan_id IN NUMBER
3882 , p_release_id IN NUMBER
3883 , p_release_mode IN NUMBER DEFAULT NULL) is
3884 l_CP_name VARCHAR2(80);
3885 l_status NUMBER;
3886 l_errbuf VARCHAR2(1000);
3887 l_retcode NUMBER;
3888 begin
3889 if p_release_type = 'LOAD' then
3890 l_CP_name := 'MSTRELLD';
3891 elsif p_release_type = 'PLAN' then
3892 l_CP_name := 'MSTRELPL';
3893 end if;
3894 p_request_id := fnd_request.submit_request('MST', l_CP_name, NULL, NULL, NULL, p_plan_id, p_release_id, p_release_mode);
3895 if p_request_id = 0 then
3896 l_errbuf := fnd_message.get;
3897 else
3898 commit;
3899 end if;
3900 end submit_release_request;
3901
3902 ------- used in exception summary screen to set the release type ---------------
3903 -- p_release_type = 1 => auto released, 2 => released, 3 => flagged for release, 4 => unreleased
3904 procedure set_release_type (p_release_type IN NUMBER) is
3905 begin
3906 g_release_type := p_release_type;
3907 end set_release_type;
3908
3909 -- used in views of all tls, all ltls, all parcels, all continuous moves
3910 function get_release_type RETURN NUMBER is
3911 l_temp number;
3912 begin
3913 l_temp := g_release_type;
3914 g_release_type := 0;
3915 return l_temp;
3916 end get_release_type;
3917
3918 procedure print_info(p_release_debug_control in number, p_info_str in varchar2) is
3919 begin
3920 if p_release_debug_control = 1 then
3921 fnd_file.put_line(fnd_file.log, p_info_str);
3922 --dbms_output.put_line(p_info_str);
3923 --abc123pro(p_info_str);
3924 end if;
3925 end print_info;
3926
3927 END MST_RELEASE;