[Home] [Help]
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
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;
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;
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');
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);
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);
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);
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);
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);
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);
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);
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);
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);
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);
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);
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;
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;
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
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
346: when others then
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
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
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
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;
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;
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;
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;
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
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
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
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:
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
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;
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
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
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;
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
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;
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:
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
937: , trip_process_flag = 1
938: where release_id = p_release_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
937: , trip_process_flag = 1
938: where release_id = p_release_id
939: and trip_id = l_trip_id;
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;
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:
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:
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;
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:
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;
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
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:
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:
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
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
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;
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:
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
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
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)
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
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:
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:
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
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: );
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
1609: , mts.pln_unloading_start_time
1610: , mts.pln_unloading_end_time
1611: from mst_plans mp
1612: , mst_trip_stops mts
1613: , mst_release_temp_gt mrt
1614: where mrt.release_id = p_release_id
1615: and mrt.plan_id = mts.plan_id
1616: and mrt.trip_id = mts.trip_id
1617: and mrt.plan_id = mp.plan_id
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:
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:
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
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
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
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
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
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
1989: , mdd.src_requested_quantity_uom
1990: , mdd.supplier_id
1991: , hzr.object_id
1992: from mst_plans mp
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
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:
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
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:
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;
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
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:
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:
2395: --cursor to retrieve the trips in a group to update mst_trips
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
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
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
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');
2659: insert into mst_release_temp_gt
2660: ( release_id
2661: , group_id
2662: , plan_id
2663: , trip_id
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;
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);
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)
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
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
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;
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:
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:
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;
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
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
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
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;
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);
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
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
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
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;
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);
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
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));
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;
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;
3923: --abc123pro(p_info_str);
3924: end if;
3925: end print_info;
3926:
3927: END MST_RELEASE;