[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
g_cnt_cm_selected pls_integer;
g_update_tp_tables pls_integer;
g_delete_record_count pls_integer;
g_delete_record_count_loop pls_integer;
select meaning
from wsh_lookups
where lookup_type = 'WSH_MODE_OF_TRANSPORT'
and lookup_code = l_mode_of_transport;
g_cnt_cm_selected := 0;
g_update_tp_tables := 1;
g_delete_record_count := 1;
g_delete_record_count_loop := 1;
fnd_message.set_token('N1',to_char(g_cnt_cm_selected));
fnd_message.set_token('N5',to_char(g_cnt_cm_selected-g_cnt_cm_released-g_cnt_cm_failed));
if g_update_tp_tables = 1 then
--print_info(g_log_must_message,' + TP tables updated');
g_update_tp_tables := substr(l_profile_mst_release_debug,4,1);
g_delete_record_count_loop := substr(l_profile_mst_release_debug,12,1);
g_delete_record_count := substr(l_profile_mst_release_debug,13);
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)
from wsh_trips_interface
where interface_action_code = g_tp_release_code
and group_id = l_group_id
order by tp_trip_number;
procedure insert_trips (p_plan_id in number
, p_release_id in number
, p_load_tab in number_tab_type
, p_load_type in varchar2) is
begin
print_info(g_log_flow_of_control,'insert_trips : Program started');
insert into mst_release_temp
(
release_id
, group_id
, plan_id
, trip_id
, sr_trip_id
, trip_number
, planned_flag
, release_status
, trip_start_date
, cm_id_of_trip
, continuous_move_sequence
, out_of_scope
, trip_process_flag
, selected_trips
, trip_id_iface
, status_code
, inventory_item_id
, organization_id
, carrier_id
, ship_method_code
, compile_designator
, mode_of_transport
, load_tender_status
, lane_id
, service_level
)
(
select p_release_id
, null
, mt.plan_id
, mt.trip_id
, mt.sr_trip_id
, mt.trip_number
, mt.planned_flag
, mt.release_status
, mt.trip_start_date
, mt.continuous_move_id
, mt.continuous_move_sequence
, mt.out_of_scope
, null
, 1
, wsh_trips_interface_s.nextval
, mt.status_code
, fvt.inventory_item_id
, fvt.organization_id
, mt.carrier_id
, mt.ship_method_code
, mp.compile_designator
, mt.mode_of_transport
, mt.load_tender_status
, mt.lane_id
, mt.service_level
from mst_plans mp
, mst_trips mt
, fte_vehicle_types fvt
where mt.plan_id = p_plan_id
and mt.plan_id = mp.plan_id
and mt.vehicle_type_id = fvt.vehicle_type_id (+)
and mt.continuous_move_id = p_load_tab(i)
);
insert into mst_release_temp
(
release_id
, group_id
, plan_id
, trip_id
, sr_trip_id
, trip_number
, planned_flag
, release_status
, trip_start_date
, cm_id_of_trip
, continuous_move_sequence
, out_of_scope
, trip_process_flag
, selected_trips
, trip_id_iface
, status_code
, inventory_item_id
, organization_id
, carrier_id
, ship_method_code
, compile_designator
, mode_of_transport
, load_tender_status
, lane_id
, service_level
)
(
select p_release_id
, null
, mt.plan_id
, mt.trip_id
, mt.sr_trip_id
, mt.trip_number
, mt.planned_flag
, mt.release_status
, mt.trip_start_date
, mt.continuous_move_id
, mt.continuous_move_sequence
, mt.out_of_scope
, null
, 1
, wsh_trips_interface_s.nextval
, mt.status_code
, fvt.inventory_item_id
, fvt.organization_id
, mt.carrier_id
, mt.ship_method_code
, mp.compile_designator
, mt.mode_of_transport
, mt.load_tender_status
, mt.lane_id
, mt.service_level
from mst_plans mp
, mst_trips mt
, fte_vehicle_types fvt
where mt.plan_id = p_plan_id
and mt.plan_id = mp.plan_id
and mt.vehicle_type_id = fvt.vehicle_type_id (+)
and mt.trip_id = p_load_tab(i)
);
print_info(g_log_flow_of_control,'insert_trips : Program ended');
end insert_trips;
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)
from mst_release_temp_gt mrt
where mrt.release_id = l_release_id
and mrt.trip_id is not null
and mrt.out_of_scope = 1;
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)
from mst_release_temp_gt mrt
where mrt.release_id = l_release_id
and mrt.cm_id_of_trip is null
and mrt.trip_id not in (select mdl.trip_id
from mst_delivery_legs mdl
where mdl.plan_id = l_plan_id);
update mst_release_temp_gt
set planned_flag = -1111
where release_id = p_release_id
and trip_id = l_trip_id_tab(i);
update mst_release_temp_gt
set planned_flag = -2222
where release_id = p_release_id
and trip_id = l_trip_id_tab(i);
select 1
from mst_release_temp_gt
where release_id = l_release_id
and continuous_move_id = l_cm_id;
select mct.planned_flag, mct.sr_cm_trip_id, mct.lane_id, mct.service_level, mp.compile_designator, mct.cm_trip_number
from mst_cm_trips mct
, mst_plans mp
where mp.plan_id = l_plan_id
and mct.plan_id = l_plan_id
and mct.continuous_move_id = l_cm_id;
g_cnt_cm_selected := g_cnt_cm_selected + 1;
insert into mst_release_temp_gt
(
release_id
, plan_id
, group_id
, planned_flag
, continuous_move_id
, sr_cm_trip_id
, continuous_move_id_iface
, lane_id
, service_level
, compile_designator
, cm_trip_number
)
values
(
p_release_id
, p_plan_id
, p_group_id
, l_cm_planned_flag
, p_cm_id_of_trip
, l_sr_cm_trip_id
, fte_moves_interface_s.nextval
, l_lane_id
, l_service_level
, l_compile_designator
, l_cm_trip_number
);
insert into mst_release_temp_gt
(
release_id
, group_id
, plan_id
, trip_id
, sr_trip_id
, trip_number
, planned_flag
, release_status
, trip_start_date
, cm_id_of_trip
, continuous_move_sequence
, out_of_scope
, trip_process_flag
, trip_id_iface
, status_code
, inventory_item_id
, organization_id
, carrier_id
, ship_method_code
, compile_designator
, mode_of_transport
, load_tender_status
, lane_id
, service_level
)
(
select p_release_id
, null
, mt.plan_id
, mt.trip_id
, mt.sr_trip_id
, mt.trip_number
, mt.planned_flag
, mt.release_status
, mt.trip_start_date
, mt.continuous_move_id
, mt.continuous_move_sequence
, mt.out_of_scope
, null
, wsh_trips_interface_s.nextval
, mt.status_code
, fvt.inventory_item_id
, fvt.organization_id
, mt.carrier_id
, mt.ship_method_code
, mp.compile_designator
, mt.mode_of_transport
, mt.load_tender_status
, mt.lane_id
, mt.service_level
from mst_plans mp
, mst_trips mt
, fte_vehicle_types fvt
where mt.plan_id = p_plan_id
and mt.continuous_move_id = p_cm_id_of_trip
and mt.plan_id = mp.plan_id
and mt.vehicle_type_id = fvt.vehicle_type_id (+)
and mt.trip_id not in (select mrt1.trip_id
from mst_release_temp_gt mrt1
where mrt1.release_id = p_release_id
and mrt1.trip_id is not null)
);
update mst_release_temp_gt mrt
Set mrt.group_id = p_group_id
, 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))
where release_id = p_release_id
and cm_id_of_trip = p_cm_id_of_trip;
insert into mst_release_temp_gt
(
release_id
, group_id
, plan_id
, sr_trip_id
, trip_id
, trip_number
, planned_flag
, release_status
, trip_start_date
, cm_id_of_trip
, continuous_move_sequence
, out_of_scope
--, trip_id_iface bug # 3509717 moving to update section below
, trip_process_flag
, status_code
, inventory_item_id
, organization_id
, carrier_id
, ship_method_code
, compile_designator
, mode_of_transport
, load_tender_status
, lane_id
, service_level
)
(
select distinct p_release_id -- distinct added to remove repeatation of trips
, null
, mt.plan_id
, mt.sr_trip_id
, mt.trip_id
, mt.trip_number
, mt.planned_flag
, mt.release_status
, mt.trip_start_date
, mt.continuous_move_id
, mt.continuous_move_sequence
, mt.out_of_scope
--, wsh_trips_interface_s.nextval bug # 3509717 moving to update section below
, null
, mt.status_code
, fvt.inventory_item_id
, fvt.organization_id
, mt.carrier_id
, mt.ship_method_code
, mp.compile_designator
, mt.mode_of_transport
, mt.load_tender_status
, mt.lane_id
, mt.service_level
from mst_delivery_legs mdl
, mst_delivery_legs mdl1
, mst_trips mt
, mst_plans mp
, fte_vehicle_types fvt
where mdl.plan_id = p_plan_id
and mdl.trip_id = p_trip_id
and mdl1.plan_id = mdl.plan_id
and mdl1.delivery_id = mdl.delivery_id
and mdl1.trip_id <> mdl.trip_id
and mt.plan_id = mdl1.plan_id
and mt.trip_id = mdl1.trip_id
and mt.plan_id = mp.plan_id
and mt.vehicle_type_id = fvt.vehicle_type_id (+)
and mt.trip_id not in (select mrt.trip_id
from mst_release_temp_gt mrt
where release_id = p_release_id)
);
update mst_release_temp_gt mrt
Set mrt.group_id = p_group_id
, trip_id_iface = wsh_trips_interface_s.nextval -- bug # 3509717 moved here from insert section
, mrt.planned_flag = decode(p_planned_flag,-1111,-1111,
decode(mrt.out_of_scope,1,mrt.planned_flag
,decode(p_planned_flag,1,decode(nvl(mrt.planned_flag,0),3,2,mrt.planned_flag),mrt.planned_flag)))
where mrt.release_id = p_release_id
and mrt.trip_id in (select mdl_rel.trip_id
from mst_delivery_legs mdl
, mst_delivery_legs mdl_rel
where mdl_rel.plan_id = mdl.plan_id
and mdl_rel.delivery_id = mdl.delivery_id
and mdl_rel.trip_id <> mdl.trip_id
and mdl.plan_id = p_plan_id
and mdl.trip_id = p_trip_id);
select trip_id
, group_id
, planned_flag
, cm_id_of_trip
from mst_release_temp_gt
where release_id = l_release_id
and trip_id is not null
and trip_process_flag is null
order by group_id;
select mst_release_seq.nextval
into l_group_id
from dual;
update mst_release_temp_gt
set group_id = l_group_id
, trip_process_flag = 1
where release_id = p_release_id
and trip_id = l_trip_id;
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)
from mst_release_temp_gt mrt
where mrt.release_id = l_release_id
and mrt.trip_start_date < l_release_start_date;
update mst_release_temp_gt mrt
set mrt.planned_flag = -3333
where mrt.release_id = p_release_id
and mrt.group_id = l_group_id_tab(i);
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)
from mst_release_temp_gt
where release_id = l_release_id
and trip_id is not null
order by group_id;
l_group_id_tobedeleted_tab number_tab_type;
select mep.release_option
from mst_excep_preferences mep
, mst_exception_details med
where mep.user_id = -9999
and mep.release_option = 1
and mep.exception_type = med.exception_type
and med.plan_id = l_plan_id
and (med.trip_id1 = l_trip_id -- trip_id2 is not being checked since
or med.delivery_id in (select mdl.delivery_id -- it can not exists without trip_id1.
from mst_delivery_legs mdl -- continuous_move_id is not being checked
where mdl.plan_id = l_plan_id -- since it is not being populated without trip_id1
and mdl.trip_id = l_trip_id)
or med.location_id in (select mts.stop_location_id
from mst_trip_stops mts
where mts.plan_id = l_plan_id
and mts.trip_id = l_trip_id));
l_group_id_tobedeleted_tab(nvl(l_group_id_tobedeleted_tab.last,0) + 1) := l_group_id_tab(i);
if nvl(l_group_id_tobedeleted_tab.last,0) > 0 then
print_info(g_log_must_message,'');
forall i in 1..l_group_id_tobedeleted_tab.last
update mst_release_temp_gt
set planned_flag = -4444
where release_id = p_release_id
and group_id = l_group_id_tobedeleted_tab(i);
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)
from mst_release_temp_gt
where release_id = l_release_id
and trip_id is not null
and selected_trips is null;
l_group_id_tobedeleted_tab number_tab_type;
l_group_id_tobedeleted_tab(nvl(l_group_id_tobedeleted_tab.last,0) + 1) := l_group_id_tab(i);
if nvl(l_group_id_tobedeleted_tab.last,0) > 0 then
print_info(g_log_must_message,'');
forall i in 1..l_group_id_tobedeleted_tab.last
update mst_release_temp_gt
set planned_flag = -5555
where release_id = p_release_id
and group_id = l_group_id_tobedeleted_tab(i);
select distinct group_id
from mst_release_temp_gt
where release_id = l_release_id
and plan_id = l_plan_id
and trip_id is not null
and group_id is not null
order by group_id;
update mst_release_temp_gt
set group_id = l_group_id-1
where release_id = p_release_id
and group_id = p_group_tab(l_count);
insert into mst_release_temp_gt
(
release_id
, group_id
, plan_id
, planned_flag
, delivery_id
, sr_delivery_id
, out_of_scope
-- , delivery_id_iface
, status_code
, pickup_date
, dropoff_date
, pickup_location_id
, dropoff_location_id
, customer_id
, gross_weight
, net_weight
, weight_uom
, volume
, volume_uom
, currency_uom
, organization_id
, shipment_direction
, delivery_number
, compile_designator
, earliest_pickup_date
, latest_pickup_date
, earliest_acceptable_date
, latest_acceptable_date
, supplier_id
, party_id
)
(
select DISTINCT mrt.release_id
, mrt.group_id
, mrt.plan_id
, DECODE(md.planned_flag, 1, 1
, 2, decode(md.preserve_grouping_flag, 1, 2
, 2, 3
, 3
)
, 3
)
, md.delivery_id
, md.sr_delivery_id
, md.out_of_scope
-- , wsh_new_del_interface_s.nextval
, nvl(md.status_code,1) status_code
, md.pickup_date
, md.dropoff_date
, md.pickup_location_id
, md.dropoff_location_id
, md.customer_id
, md.gross_weight
, md.net_weight
, mp.weight_uom
, md.volume
, mp.volume_uom
, mp.currency_uom
, nvl(md.organization_id,mst_wb_util.get_org_id(md.plan_id,md.delivery_id))
, decode(md.shipment_direction ,1,'I',2,'O',3,'D',4,'IO','O')
, md.delivery_number
, mp.compile_designator
, md.earliest_pickup_date
, md.latest_pickup_date
, md.earliest_acceptable_date
, md.latest_acceptable_date
, md.supplier_id
, hzr.object_id
from mst_release_temp_gt mrt
, mst_deliveries md
, mst_delivery_legs mdl
, mst_plans mp
, hz_relationships hzr
where mrt.release_id = p_release_id
and mrt.plan_id = mdl.plan_id
and mrt.trip_id = mdl.trip_id
and mdl.plan_id = md.plan_id
and mdl.delivery_id = md.delivery_id
and md.plan_id = mp.plan_id
and hzr.relationship_type (+) = 'POS_VENDOR_PARTY'
and hzr.object_table_name (+) = 'PO_VENDORS'
and hzr.object_type (+) = 'POS_VENDOR'
and hzr.subject_table_name (+) = 'HZ_PARTIES'
and hzr.subject_type (+) = 'ORGANIZATION'
and hzr.status (+) = 'A'
and hzr.subject_id (+) = md.supplier_id
);
UPDATE mst_release_temp_gt mrt
SET mrt.delivery_id_iface = wsh_new_del_interface_s.nextval
WHERE mrt.release_id = p_release_id
AND mrt.delivery_id IS NOT NULL;
UPDATE mst_release_temp_gt mrt
SET mrt.planned_flag = 1
WHERE mrt.release_id = p_release_id
AND mrt.delivery_id IN (SELECT mdl.delivery_id
FROM mst_delivery_legs mdl
, mst_release_temp_gt mrt1
WHERE mdl.plan_id = mrt1.plan_id
AND mdl.trip_id = mrt1.trip_id
AND mrt1.release_id = p_release_id
AND mrt1.trip_id IS NOT NULL
AND mrt1.planned_flag = 1);
procedure update_ids_in_wdd_iface_tab (p_release_id in pls_integer) is
cursor cur_wdd_ids (l_release_id in pls_integer)
is
select wdd_iface.delivery_detail_interface_id, min(wdd.source_header_id), min(wdd.source_line_id), min(wdd.source_line_set_id)
from wsh_delivery_details wdd
, mst_release_temp_gt mrt
, wsh_del_details_interface wdd_iface
where wdd.source_code = wdd_iface.source_code
and wdd.source_header_number = wdd_iface.source_header_number
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)
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)
and wdd_iface.delivery_detail_interface_id = mrt.delivery_detail_id_iface
and mrt.release_id = l_release_id
group by wdd_iface.delivery_detail_interface_id;
print_info(g_log_flow_of_control,'update_ids_in_wdd_iface_tab : Program started');
update wsh_del_details_interface
set source_header_id = l_source_header_id_tab(i)
, source_line_id = l_source_line_id_tab(i)
, source_line_set_id = l_source_line_set_id_tab(i)
where delivery_detail_interface_id = l_delivery_detail_iface_id_tab(i);
print_info(g_log_flow_of_control,'update_ids_in_wdd_iface_tab : Program ended');
end update_ids_in_wdd_iface_tab;
procedure update_loc_id_in_iface_tab (p_release_id in pls_integer) is
cursor cur_wdd_loc_ids (l_release_id in pls_integer)
is
select wda_iface.delivery_interface_id, wdd_iface.delivery_detail_interface_id, mplav.cust_location_id
from mst_po_location_asso_v mplav
, wsh_del_details_interface wdd_iface
, wsh_del_assgn_interface wda_iface
, wsh_delivery_details wdd
, mst_release_temp_gt mrt
where mplav.location_id = wdd_iface.ship_to_location_id
and wdd_iface.delivery_detail_id = wdd.delivery_detail_id
and wdd.ship_to_location_id <> wdd_iface.ship_to_location_id
and wda_iface.delivery_detail_interface_id = wdd_iface.delivery_detail_interface_id
and wdd_iface.delivery_detail_interface_id = mrt.delivery_detail_id_iface
and mrt.release_id = l_release_id;
print_info(g_log_flow_of_control,'update_loc_id_in_wdd_iface_tab : Program started');
update wsh_new_del_interface
set ultimate_dropoff_location_id = l_ship_to_location_id_tab(i)
where delivery_interface_id = l_delivery_iface_id_tab(i);
update wsh_del_details_interface
set ship_to_location_id = l_ship_to_location_id_tab(i)
where delivery_detail_interface_id = l_delivery_detail_iface_id_tab(i);
print_info(g_log_flow_of_control,'update_loc_id_in_wdd_iface_tab : Program ended');
end update_loc_id_in_iface_tab;
insert into wsh_trips_interface
( trip_interface_id
, trip_id
, planned_flag
, status_code
, vehicle_item_id
, vehicle_organization_id
, carrier_id
, ship_method_code
, interface_action_code
, tp_plan_name
, tp_trip_number
, group_id
, mode_of_transport
, load_tender_status
, lane_id
, service_level
, creation_date
, created_by
, last_update_date
, last_updated_by
, last_update_login
)
(
select mrt.trip_id_iface
, mrt.sr_trip_id
, decode(mrt.planned_flag,1,'F',2,'Y',3,'N','N')
, decode(mrt.status_code,1,'OP',2,'IT',3,'CL','OP')
, mrt.inventory_item_id
, mrt.organization_id
, mrt.carrier_id
, mrt.ship_method_code
, g_tp_release_code
, mrt.compile_designator
, mrt.trip_number
, mrt.group_id
, mrt.mode_of_transport
, mrt.load_tender_status
, mrt.lane_id
, mrt.service_level
, l_date
, l_user
, l_date
, l_user
, l_user
from mst_release_temp_gt mrt
where mrt.release_id = p_release_id
and mrt.trip_id is not null
);
insert into fte_moves_interface
( move_interface_id
, move_id
, lane_id
, service_level
, planned_flag
, tp_plan_name
, cm_trip_number
, interface_action_code
, creation_date
, created_by
, last_update_date
, last_updated_by
, last_update_login
)
(
select mrt.continuous_move_id_iface
, mrt.sr_cm_trip_id
, mrt.lane_id
, mrt.service_level
, decode(mrt.planned_flag,1,'Y',2,'N','N')
, mrt.compile_designator
, mrt.cm_trip_number
, g_tp_release_code
, l_date
, l_user
, l_date
, l_user
, l_user
from mst_release_temp_gt mrt
where mrt.release_id = p_release_id
and mrt.continuous_move_id is not null
);
insert into fte_trip_moves_interface
( trip_move_interface_id
, trip_move_id
, move_interface_id
, move_id
, trip_interface_id
, trip_id
, sequence_number
, interface_action_code
, creation_date
, created_by
, last_update_date
, last_updated_by
, last_update_login
)
(
select fte_trip_moves_interface_s.nextval
, null
, mrt1.continuous_move_id_iface
, mrt1.sr_cm_trip_id
, mrt2.trip_id_iface
, mrt2.sr_trip_id
, mrt2.continuous_move_sequence
, g_tp_release_code
, l_date
, fnd_global.user_id
, l_date
, l_user
, l_user
from mst_release_temp_gt mrt1
, mst_release_temp_gt mrt2
where mrt1.release_id = mrt2.release_id
and mrt1.continuous_move_id = mrt2.cm_id_of_trip
and mrt2.release_id = p_release_id
);
insert into mst_release_temp_gt
(
release_id
, group_id
, plan_id
, stop_id
, trip_id_iface
, stop_id_iface
, sr_trip_id
, status_code
, sr_stop_id
, stop_location_id
, stop_sequence_number
, planned_arrival_date
, planned_departure_date
, departure_gross_weight
, departure_net_weight
, weight_uom
, departure_volume
, volume_uom
, departure_fill_percent
, wkend_layover_stops
, wkday_layover_stops
, pln_loading_start_time
, pln_loading_end_time
, pln_unloading_start_time
, pln_unloading_end_time
)
(
select p_release_id
, mrt.group_id
, mts.plan_id
, mts.stop_id
, mrt.trip_id_iface
, wsh_trip_stops_interface_s.nextval
, mrt.sr_trip_id
, mrt.status_code
, mts.sr_stop_id
, mts.stop_location_id
, mts.stop_sequence_number
, mts.planned_arrival_date
, mts.planned_departure_date
, mts.departure_gross_weight
, mts.departure_net_weight
, mp.weight_uom
, mts.departure_volume
, mp.volume_uom
, mts.departure_fill_percent
, mts.wkend_layover_stops
, mts.wkday_layover_stops
, mts.pln_loading_start_time
, mts.pln_loading_end_time
, mts.pln_unloading_start_time
, mts.pln_unloading_end_time
from mst_plans mp
, mst_trip_stops mts
, mst_release_temp_gt mrt
where mrt.release_id = p_release_id
and mrt.plan_id = mts.plan_id
and mrt.trip_id = mts.trip_id
and mrt.plan_id = mp.plan_id
);
insert into wsh_trip_stops_interface
( stop_interface_id
, stop_id
, trip_id
, tp_stop_id
, trip_interface_id
, stop_location_id
, status_code
, stop_sequence_number
, planned_arrival_date
, planned_departure_date
, departure_gross_weight
, departure_net_weight
, weight_uom_code
, departure_volume
, volume_uom_code
, departure_fill_percent
, wkend_layover_stops
, wkday_layover_stops
, loading_start_datetime
, loading_end_datetime
, unloading_start_datetime
, unloading_end_datetime
, interface_action_code
, creation_date
, created_by
, last_update_date
, last_updated_by
, last_update_login
)
(
select mrt.stop_id_iface
, mrt.sr_stop_id
, mrt.sr_trip_id
, mrt.stop_id
, mrt.trip_id_iface
, mrt.stop_location_id
, decode(mrt.status_code,1,'OP',2,'IT',3,'CL','OP')
, mrt.stop_sequence_number
, mrt.planned_arrival_date
, mrt.planned_departure_date
, mrt.departure_gross_weight
, mrt.departure_net_weight
, mrt.weight_uom
, mrt.departure_volume
, mrt.volume_uom
, mrt.departure_fill_percent
, mrt.wkend_layover_stops
, mrt.wkday_layover_stops
, mrt.pln_loading_start_time
, mrt.pln_loading_end_time
, mrt.pln_unloading_start_time
, mrt.pln_unloading_end_time
, g_tp_release_code
, l_date
, fnd_global.user_id
, l_date
, l_user
, l_user
from mst_release_temp_gt mrt
where mrt.release_id = p_release_id
and mrt.stop_id is not null
);
insert into wsh_new_del_interface
( delivery_interface_id
, delivery_id
, planned_flag
, status_code
, initial_pickup_date
, initial_pickup_location_id
, ultimate_dropoff_location_id
, ultimate_dropoff_date
, customer_id
, gross_weight
, net_weight
, weight_uom_code
, volume
, volume_uom_code
, currency_code
, organization_id
, shipment_direction
, tp_delivery_number
, tp_plan_name
, earliest_pickup_date
, latest_pickup_date
, earliest_dropoff_date
, latest_dropoff_date
, delivery_type
, interface_action_code
, creation_date
, created_by
, last_update_date
, last_updated_by
, last_update_login
)
(
select mrt.delivery_id_iface
, mrt.sr_delivery_id
, decode(mrt.planned_flag,1,'F',2,'Y',3,'N','N')
, decode(mrt.status_code,1,'OP',2,'IT',3,'CL','OP')
, mrt.pickup_date
, mrt.pickup_location_id
, mrt.dropoff_location_id
, mrt.dropoff_date
, mrt.customer_id
, mrt.gross_weight
, mrt.net_weight
, mrt.weight_uom
, mrt.volume
, mrt.volume_uom
, mrt.currency_uom
, mrt.organization_id
, mrt.shipment_direction
, mrt.delivery_number
, mrt.compile_designator
, mrt.earliest_pickup_date
, mrt.latest_pickup_date
, mrt.earliest_acceptable_date
, mrt.latest_acceptable_date
, 'STANDARD'
, g_tp_release_code
, l_date
, l_user
, l_date
, l_user
, l_user
from mst_release_temp_gt mrt
where mrt.release_id = p_release_id
and mrt.delivery_id is not null
);
insert into wsh_del_legs_interface
( delivery_leg_interface_id
, delivery_leg_id
, delivery_id
, delivery_interface_id
, sequence_number
, pick_up_stop_id
, pick_up_stop_interface_id
, drop_off_stop_id
, drop_off_stop_interface_id
, gross_weight
, net_weight
, weight_uom_code
, volume
, volume_uom_code
, interface_action_code
, creation_date
, created_by
, last_update_date
, last_updated_by
, last_update_login
)
(
select wsh_del_legs_interface_s.nextval
, mdl.sr_delivery_leg_id
, mrt1.sr_delivery_id
, mrt1.delivery_id_iface
, mdl.sequence_number
, mrt2.sr_stop_id
, mrt2.stop_id_iface
, mrt3.sr_stop_id
, mrt3.stop_id_iface
, mrt1.gross_weight
, mrt1.net_weight
, mrt1.weight_uom
, mrt1.volume
, mrt1.volume_uom
, g_tp_release_code
, l_date
, l_user
, l_date
, l_user
, l_user
from mst_release_temp_gt mrt1
, mst_release_temp_gt mrt2
, mst_release_temp_gt mrt3
, mst_delivery_legs mdl
where mrt1.plan_id= mdl.plan_id
and mrt1.delivery_id = mdl.delivery_id
and mrt1.release_id = p_release_id
and mrt2.stop_id = mdl.pick_up_stop_id
and mrt2.release_id = p_release_id
and mrt3.stop_id = mdl.drop_off_stop_id
and mrt3.release_id = p_release_id
);
insert into mst_release_temp_gt
( release_id
, group_id
, plan_id
, sr_delivery_id
, delivery_id_iface
, sr_delivery_assignment_id
, delivery_detail_id
, delivery_detail_id_iface
, sr_delivery_detail_id
, source_code
, customer_id
, inventory_item_id
, ship_from_location_id
, ship_to_location_id
, requested_quantity
, gross_weight
, net_weight
, weight_uom
, volume
, volume_uom
, source_header_number
, ship_set_id
, arrival_set_id
, organization_id
, org_id
, container_flag
, source_line_number
, split_from_delivery_detail_id
, earliest_pickup_date
, latest_pickup_date
, earliest_acceptable_date
, latest_acceptable_date
, line_direction
, po_shipment_line_number
, src_requested_quantity
, src_requested_quantity_uom
, supplier_id
, party_id
)
(
select p_release_id
, mrt.group_id
, mrt.plan_id
, mrt.sr_delivery_id
, mrt.delivery_id_iface
, mda.sr_delivery_assignment_id
, mda.delivery_detail_id
, wsh_del_details_interface_s.nextval
, mdd1.sr_delivery_detail_id
, mdd.source_code
, mdd.customer_id
, mdd.inventory_item_id
, mdd.ship_from_location_id
, mdd.ship_to_location_id
, mdd.requested_quantity
, mdd.gross_weight
, mdd.net_weight
, mp.weight_uom
, mdd.volume
, mp.volume_uom
, mdd.source_header_number
, mdd.ship_set_id
, mdd.arrival_set_id
, mdd.organization_id
, mdd.org_id
, mdd.container_flag
, mdd.source_line_number
, mdd.split_from_delivery_detail_id
, mdd.earliest_pickup_date
, mdd.latest_pickup_date
, mdd.earliest_acceptable_date
, mdd.latest_acceptable_date
, mdd.line_direction
, mdd.po_shipment_line_number
, mdd.src_requested_quantity
, mdd.src_requested_quantity_uom
, mdd.supplier_id
, hzr.object_id
from mst_plans mp
, mst_release_temp_gt mrt
, mst_delivery_assignments mda
, mst_delivery_details mdd
, mst_delivery_details mdd1
, hz_relationships hzr
where mrt.plan_id = mp.plan_id
and mrt.plan_id = mda.plan_id
and mrt.delivery_id = mda.delivery_id
and mda.plan_id = mdd.plan_id
and mda.delivery_detail_id = mdd.delivery_detail_id
and mdd.plan_id = mdd1.plan_id
and mdd.split_from_delivery_detail_id = mdd1.delivery_detail_id
and mdd.split_from_delivery_detail_id is not null
and mrt.release_id = p_release_id
and hzr.relationship_type (+) = 'POS_VENDOR_PARTY'
and hzr.object_table_name (+) = 'PO_VENDORS'
and hzr.object_type (+) = 'POS_VENDOR'
and hzr.subject_table_name (+) = 'HZ_PARTIES'
and hzr.subject_type (+) = 'ORGANIZATION'
and hzr.status (+) = 'A'
and hzr.subject_id (+) = mdd.supplier_id
);
insert into mst_release_temp_gt
( release_id
, group_id
, plan_id
, sr_delivery_id
, delivery_id_iface
, sr_delivery_assignment_id
, delivery_detail_id
, delivery_detail_id_iface
, sr_delivery_detail_id
, source_code
, customer_id
, inventory_item_id
, ship_from_location_id
, ship_to_location_id
, requested_quantity
, gross_weight
, net_weight
, weight_uom
, volume
, volume_uom
, source_header_number
, ship_set_id
, arrival_set_id
, organization_id
, org_id
, container_flag
, source_line_number
, split_from_delivery_detail_id
, earliest_pickup_date
, latest_pickup_date
, earliest_acceptable_date
, latest_acceptable_date
, line_direction
, po_shipment_line_number
, src_requested_quantity
, src_requested_quantity_uom
, supplier_id
, party_id
)
(
select p_release_id
, mrt.group_id
, mrt.plan_id
, mrt.sr_delivery_id
, mrt.delivery_id_iface
, mda.sr_delivery_assignment_id
, mda.delivery_detail_id
, wsh_del_details_interface_s.nextval
, mdd.sr_delivery_detail_id
, mdd.source_code
, mdd.customer_id
, mdd.inventory_item_id
, mdd.ship_from_location_id
, mdd.ship_to_location_id
, mdd.requested_quantity
, mdd.gross_weight
, mdd.net_weight
, mp.weight_uom
, mdd.volume
, mp.volume_uom
, mdd.source_header_number
, mdd.ship_set_id
, mdd.arrival_set_id
, mdd.organization_id
, mdd.org_id
, mdd.container_flag
, mdd.source_line_number
, mdd.split_from_delivery_detail_id
, mdd.earliest_pickup_date
, mdd.latest_pickup_date
, mdd.earliest_acceptable_date
, mdd.latest_acceptable_date
, mdd.line_direction
, mdd.po_shipment_line_number
, mdd.src_requested_quantity
, mdd.src_requested_quantity_uom
, mdd.supplier_id
, hzr.object_id
from mst_plans mp
, mst_release_temp_gt mrt
, mst_delivery_assignments mda
, mst_delivery_details mdd
, hz_relationships hzr
where mrt.plan_id = mp.plan_id
and mrt.plan_id = mda.plan_id
and mrt.delivery_id = mda.delivery_id
and mda.plan_id = mdd.plan_id
and mda.delivery_detail_id = mdd.delivery_detail_id
and mdd.split_from_delivery_detail_id is null
and mrt.release_id = p_release_id
and hzr.relationship_type (+) = 'POS_VENDOR_PARTY'
and hzr.object_table_name (+) = 'PO_VENDORS'
and hzr.object_type (+) = 'POS_VENDOR'
and hzr.subject_table_name (+) = 'HZ_PARTIES'
and hzr.subject_type (+) = 'ORGANIZATION'
and hzr.status (+) = 'A'
and hzr.subject_id (+) = mdd.supplier_id
);
insert into wsh_del_assgn_interface
( del_assgn_interface_id
, delivery_assignment_id
, delivery_interface_id
, delivery_id
, delivery_detail_interface_id
, delivery_detail_id
, interface_action_code
, creation_date
, created_by
, last_update_date
, last_updated_by
, last_update_login
)
(
select wsh_del_assgn_interface_s.nextval
, null -- mrt.sr_delivery_assignment_id commented as not need in TE (william)
, mrt.delivery_id_iface
, mrt.sr_delivery_id
, mrt.delivery_detail_id_iface
, mrt.sr_delivery_detail_id
, g_tp_release_code
, l_date
, l_user
, l_date
, l_user
, l_user
from mst_release_temp_gt mrt
where mrt.release_id = p_release_id
and mrt.delivery_detail_id_iface is not null
);
insert into wsh_del_details_interface
( delivery_detail_interface_id
, delivery_detail_id
, source_code
, customer_id
, inventory_item_id
, ship_from_location_id
, ship_to_location_id
, requested_quantity
, gross_weight
, net_weight
, weight_uom_code
, volume
, volume_uom_code
, source_header_number
, ship_set_id
, arrival_set_id
, organization_id
, org_id
, source_line_id
, container_flag
, source_line_number
, split_from_delivery_detail_id
, earliest_pickup_date
, latest_pickup_date
, earliest_dropoff_date
, latest_dropoff_date
, tp_delivery_detail_id
, line_direction
, po_shipment_line_number
, requested_quantity_uom
, source_header_id
, source_line_set_id
, src_requested_quantity
, src_requested_quantity_uom
, requested_quantity2
, requested_quantity_uom2
, src_requested_quantity2
, src_requested_quantity_uom2
, interface_action_code
, creation_date
, created_by
, last_update_date
, last_updated_by
, last_update_login
)
(
select mrt.delivery_detail_id_iface --wsh_del_details_interface_s.nextval
, mrt.sr_delivery_detail_id
, mrt.source_code
, mrt.customer_id
, mrt.inventory_item_id
, mrt.ship_from_location_id
, mrt.ship_to_location_id
, mrt.requested_quantity
, mrt.gross_weight
, mrt.net_weight
, mrt.weight_uom
, mrt.volume
, mrt.volume_uom
, mrt.source_header_number
, mrt.ship_set_id
, mrt.arrival_set_id
, mrt.organization_id
, mrt.org_id
, nvl(wdd.source_line_id,FND_API.G_MISS_NUM)
, decode(mrt.container_flag,1,'Y','N')
, mrt.source_line_number
, mrt.split_from_delivery_detail_id
, mrt.earliest_pickup_date
, mrt.latest_pickup_date
, mrt.earliest_acceptable_date
, mrt.latest_acceptable_date
, mrt.delivery_detail_id
, decode(mrt.line_direction,1,'I',2,'O',3,'D',4,'IO','O')
, mrt.po_shipment_line_number
, wdd.requested_quantity_uom
, wdd.source_header_id
, wdd.source_line_set_id
, mrt.src_requested_quantity
, mrt.src_requested_quantity_uom
, wdd.requested_quantity2
, wdd.requested_quantity_uom2
, wdd.src_requested_quantity2
, wdd.src_requested_quantity_uom2
, g_tp_release_code
, l_date
, l_user
, l_date
, l_user
, l_user
from mst_release_temp_gt mrt
, wsh_delivery_details wdd
where mrt.release_id = p_release_id
and mrt.sr_delivery_detail_id = wdd.delivery_detail_id (+)
and mrt.delivery_detail_id is not null
);
update_ids_in_wdd_iface_tab (p_release_id);
update_loc_id_in_iface_tab (p_release_id);
procedure update_mst_cm_trips (x_return_status out nocopy varchar2
, p_group_id in pls_integer
, p_plan_id in number
, p_release_id in pls_integer
, p_release_mode in number
, p_date in date
, p_error_found in number) is
--cursor to retrieve the continuous moves in a group to update TP tables
cursor cur_continuous_moves (l_release_id in pls_integer, l_group_id in pls_integer)
is
select continuous_move_id, planned_flag
from mst_release_temp_gt
where release_id = l_release_id
and group_id = l_group_id
and continuous_move_id is not null;
print_info(g_log_flow_of_control,'update_mst_cm_trips for group '||p_group_id||' : Program started');
update mst_cm_trips
set planned_flag = l_planned_flag_tab(i)
, release_status = l_planned_flag_tab(i)
, release_date = l_date -- sysdate since successful
, auto_release_flag = l_release_mode
, selected_for_release = null
where plan_id = l_plan_id
and continuous_move_id = l_continuous_move_id_tab(i);
update mst_cm_trips
set release_status = 3 -- failed
, release_date = l_date -- datetime at start of process since unsuccessful
, auto_release_flag = l_release_mode
, selected_for_release = null
where plan_id = l_plan_id
and continuous_move_id = l_continuous_move_id_tab(i);
print_info(g_log_flow_of_control,'update_mst_cm_trips for group '||p_group_id||' : Program ended');
print_info(g_log_flow_of_control,'update_mst_cm_trips : Unexpected error ' || to_char(sqlcode) || ':' || SQLERRM);
end update_mst_cm_trips;
select count(1)
from mst_release_temp_gt
where release_id = l_release_id
and group_id = l_group_id
and trip_id is not null
and mode_of_transport = l_mode_of_transport;
select count(1)
from mst_release_temp_gt mrt_gt
where mrt_gt.release_id = l_release_id
and mrt_gt.group_id = l_group_id
and mrt_gt.plan_id = l_plan_id
and mrt_gt.trip_id is not null
and mrt_gt.cm_id_of_trip is not null
and mrt_gt.trip_id not in (select mdl.trip_id
from mst_delivery_legs mdl
where mdl.plan_id = l_plan_id);
procedure update_mst_trips (x_return_status out nocopy varchar2
, p_group_id in pls_integer
, p_plan_id in number
, p_release_id in pls_integer
, p_release_mode in number
, p_date in date
, p_error_found in number) is
--cursor to retrieve the trips in a group to update TP tables
cursor cur_trips (l_release_id in pls_integer, l_group_id in pls_integer)
is
select trip_id, planned_flag
from mst_release_temp_gt
where release_id = l_release_id
and group_id = l_group_id
and trip_id is not null;
print_info(g_log_flow_of_control,'update_mst_trips for group '||p_group_id||' : Program started');
update mst_trips
set planned_flag = l_planned_flag_tab(i)
, release_status = l_planned_flag_tab(i)
, release_date = l_date
, auto_release_flag = l_release_mode
, selected_for_release = null
where plan_id = l_plan_id
and trip_id = l_trip_id_tab(i);
update mst_trips
set release_status = 4
, release_date = l_date -- datetime at start process, it is coming as parameter to this procedure
, auto_release_flag = l_release_mode
, selected_for_release = null
where plan_id = l_plan_id
and trip_id = l_trip_id_tab(i) ;
print_info(g_log_flow_of_control,'update_mst_trips for group '||p_group_id||' : Program ended');
print_info(g_log_flow_of_control,'update_mst_trips : Unexpected error ' || to_char(sqlcode) || ':' || SQLERRM);
end update_mst_trips;
procedure update_mst_deliveries (x_return_status out nocopy varchar2
, p_group_id in pls_integer
, p_plan_id in number
, p_release_id in pls_integer
, p_error_found in pls_integer) is
--cursor to retrieve the deliveries in a group to update TP tables
cursor cur_deliveries (l_release_id in pls_integer, l_group_id in pls_integer)
is
select delivery_id, planned_flag
from mst_release_temp_gt
where release_id = l_release_id
and group_id = l_group_id
and delivery_id is not null;
print_info(g_log_flow_of_control,'update_mst_deliveries for group '||p_group_id||' : Program started');
update mst_deliveries -- remember l_error_found = 0 => successful and l_error_found = 1 => unsuccessful
set planned_flag = decode(l_planned_flag_tab(i),1,1,2)
, preserve_grouping_flag = decode(l_planned_flag_tab(i),1,null,2,1,2)
, known_te_firm_status = l_planned_flag_tab(i)
where plan_id = l_plan_id
and delivery_id = l_delivery_id_tab(i);
print_info(g_log_flow_of_control,'update_mst_deliveries for group '||p_group_id||' : Program ended');
print_info(g_log_flow_of_control,'update_mst_deliveries : Unexpected error ' || to_char(sqlcode) || ':' || SQLERRM);
end update_mst_deliveries;
procedure update_sr_ids_in_mt (x_return_status out nocopy varchar2
, p_group_id in pls_integer
, p_plan_id in number
, p_release_id in pls_integer
, p_error_found in pls_integer) is
--cursor to retrieve the trips in a group to update mst_trips
cursor cur_trips (l_release_id in pls_integer, l_group_id in pls_integer)
is
select mrt.trip_id, wti.trip_id
from mst_release_temp_gt mrt
, wsh_trips_interface wti
where mrt.release_id = l_release_id
and mrt.group_id = l_group_id
and mrt.trip_id is not null
and mrt.trip_id_iface = wti.trip_interface_id;
print_info(g_log_flow_of_control,'update_sr_ids_in_mt for group '||p_group_id||' : Program started');
update mst_trips
set sr_trip_id = l_sr_trip_id_tab(i)
where plan_id = l_plan_id
and trip_id = l_trip_id_tab(i);
print_info(g_log_flow_of_control,'update_sr_ids_in_mt for group '||p_group_id||' : Program ended');
print_info(g_log_flow_of_control,'update_sr_ids_in_mt : Unexpected error ' || to_char(sqlcode) || ':' || SQLERRM);
end update_sr_ids_in_mt;
procedure update_sr_ids_in_md (x_return_status out nocopy varchar2
, p_group_id in pls_integer
, p_plan_id in number
, p_release_id in pls_integer
, p_error_found in pls_integer) is
--cursor to retrieve the trips in a group to update mst_trips
cursor cur_deliveries (l_release_id in pls_integer, l_group_id in pls_integer)
is
select mrt.delivery_id, wndi.delivery_id
from mst_release_temp_gt mrt
, wsh_new_del_interface wndi
where mrt.release_id = l_release_id
and mrt.group_id = l_group_id
and mrt.delivery_id is not null
and mrt.delivery_id_iface = wndi.delivery_interface_id;
print_info(g_log_flow_of_control,'update_sr_ids_in_md for group '||p_group_id||' : Program started');
update mst_deliveries
set sr_delivery_id = l_sr_delivery_id_tab(i)
where plan_id = l_plan_id
and delivery_id = l_delivery_id_tab(i);
print_info(g_log_flow_of_control,'update_sr_ids_in_md for group '||p_group_id||' : Program ended');
print_info(g_log_flow_of_control,'update_sr_ids_in_md : Unexpected error ' || to_char(sqlcode) || ':' || SQLERRM);
end update_sr_ids_in_md;
procedure update_sr_ids_in_mdd (x_return_status out nocopy varchar2
, p_group_id in pls_integer
, p_plan_id in number
, p_release_id in pls_integer
, p_error_found in pls_integer) is
--cursor to retrieve the trips in a group to update mst_trips
cursor cur_delivery_details (l_release_id in pls_integer, l_group_id in pls_integer)
is
select mrt.delivery_detail_id, wddi.delivery_detail_id
from mst_release_temp_gt mrt
, wsh_del_details_interface wddi
where mrt.release_id = l_release_id
and mrt.group_id = l_group_id
and mrt.delivery_detail_id is not null
and mrt.delivery_detail_id_iface = wddi.delivery_detail_interface_id;
print_info(g_log_flow_of_control,'update_sr_ids_in_mdd for group '||p_group_id||' : Program started');
update mst_delivery_details
set sr_delivery_detail_id = l_sr_delivery_detail_id_tab(i)
where plan_id = l_plan_id
and delivery_detail_id = l_delivery_detail_id_tab(i);
print_info(g_log_flow_of_control,'update_sr_ids_in_mdd for group '||p_group_id||' : Program ended');
print_info(g_log_flow_of_control,'update_sr_ids_in_mdd : Unexpected error ' || to_char(sqlcode) || ':' || SQLERRM);
end update_sr_ids_in_mdd;
procedure update_tp_tables (x_return_status out nocopy varchar2
, p_group_tab in wsh_tp_release_grp.id_tab_type
, p_plan_id in number
, p_release_id in pls_integer
, p_release_mode in number
, p_release_start_date in date) is
--cursor to check whether a group_id has failed or successful
cursor cur_check_error (l_group_id in pls_integer)
is
select 1
from wsh_interface_errors wie
where wie.interface_error_group_id = l_group_id
and wie.interface_action_code = wsh_tp_release_grp.G_TP_RELEASE_CODE;
select error_message
from wsh_interface_errors wie
where wie.interface_error_group_id = l_group_id
and wie.interface_action_code = wsh_tp_release_grp.G_TP_RELEASE_CODE
order by wie.interface_error_id;
print_info(g_log_flow_of_control,'update_tp_tables : Program started');
select sysdate
into l_date
from dual;
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);
update_mst_trips (l_return_status, p_group_tab(i), p_plan_id, p_release_id, p_release_mode, l_date, l_error_found);
update_mst_deliveries (l_return_status, p_group_tab(i), p_plan_id, p_release_id, l_error_found);
update_sr_ids_in_mt (l_return_status, p_group_tab(i), p_plan_id, p_release_id, l_error_found);
update_sr_ids_in_md (l_return_status, p_group_tab(i), p_plan_id, p_release_id, l_error_found);
update_sr_ids_in_mdd (l_return_status, p_group_tab(i), p_plan_id, p_release_id, l_error_found);
print_info(g_log_flow_of_control,'update_tp_tables : Program ended');
print_info(g_log_flow_of_control,'update_tp_tables : Unexpected error ' || to_char(sqlcode) || ':' || SQLERRM);
end update_tp_tables;
insert into mst_release_temp_gt
( release_id
, group_id
, plan_id
, trip_id
, trip_number
, planned_flag
, release_status
, trip_start_date
, cm_id_of_trip
, continuous_move_id
, delivery_id
, out_of_scope
, trip_process_flag
, selected_trips
, trip_id_iface
, continuous_move_id_iface
, delivery_id_iface
, stop_id_iface
, continuous_move_sequence
, sr_cm_trip_id
, sr_trip_id
, stop_id
, sr_delivery_id
, delivery_detail_id_iface
, delivery_detail_id
, sr_delivery_detail_id
, sr_delivery_assignment_id
, sr_stop_id, status_code
, pickup_date
, dropoff_date
, pickup_location_id
, dropoff_location_id
, customer_id
, gross_weight
, net_weight
, weight_uom
, volume
, volume_uom
, currency_uom
, organization_id
, delivery_number
, compile_designator
, earliest_pickup_date
, latest_pickup_date
, earliest_acceptable_date
, latest_acceptable_date
, inventory_item_id
, carrier_id
, ship_method_code
, mode_of_transport
, load_tender_status
, lane_id, service_level
, cm_trip_number
, stop_location_id
, stop_sequence_number
, planned_arrival_date
, planned_departure_date
, departure_gross_weight
, departure_net_weight
, departure_volume
, departure_fill_percent
, wkend_layover_stops
, wkday_layover_stops
, distance_to_next_stop
, pln_loading_start_time
, pln_loading_end_time
, pln_unloading_start_time
, pln_unloading_end_time
, source_code
, ship_from_location_id
, ship_to_location_id
, requested_quantity
, source_header_number
, ship_set_id
, arrival_set_id
, org_id
, container_flag
, source_line_number
, split_from_delivery_detail_id
, line_direction
, po_shipment_number
, po_shipment_line_number
, src_requested_quantity_uom
, src_requested_quantity
, shipment_direction
, supplier_id
, party_id
)
(
select release_id
, group_id
, plan_id
, trip_id
, trip_number
, planned_flag
, release_status
, trip_start_date
, cm_id_of_trip
, continuous_move_id
, delivery_id
, out_of_scope
, trip_process_flag
, selected_trips
, trip_id_iface
, continuous_move_id_iface
, delivery_id_iface
, stop_id_iface
, continuous_move_sequence
, sr_cm_trip_id
, sr_trip_id
, stop_id
, sr_delivery_id
, delivery_detail_id_iface
, delivery_detail_id
, sr_delivery_detail_id
, sr_delivery_assignment_id
, sr_stop_id, status_code
, pickup_date
, dropoff_date
, pickup_location_id
, dropoff_location_id
, customer_id
, gross_weight
, net_weight
, weight_uom
, volume
, volume_uom
, currency_uom
, organization_id
, delivery_number
, compile_designator
, earliest_pickup_date
, latest_pickup_date
, earliest_acceptable_date
, latest_acceptable_date
, inventory_item_id
, carrier_id
, ship_method_code
, mode_of_transport
, load_tender_status
, lane_id, service_level
, cm_trip_number
, stop_location_id
, stop_sequence_number
, planned_arrival_date
, planned_departure_date
, departure_gross_weight
, departure_net_weight
, departure_volume
, departure_fill_percent
, wkend_layover_stops
, wkday_layover_stops
, distance_to_next_stop
, pln_loading_start_time
, pln_loading_end_time
, pln_unloading_start_time
, pln_unloading_end_time
, source_code
, ship_from_location_id
, ship_to_location_id
, requested_quantity
, source_header_number
, ship_set_id
, arrival_set_id
, org_id
, container_flag
, source_line_number
, split_from_delivery_detail_id
, line_direction
, po_shipment_number
, po_shipment_line_number
, src_requested_quantity_uom
, src_requested_quantity
, shipment_direction
, supplier_id
, party_id
from mst_release_temp
where release_id = p_release_id
);
select count(distinct mrt_gt.group_id)
from mst_release_temp_gt mrt_gt
where mrt_gt.release_id = l_release_id
and mrt_gt.plan_id = l_plan_id
and mrt_gt.trip_id is not null
and mrt_gt.planned_flag in (-1111,-2222,-3333,-4444,-5555);
select count(1)
from mst_release_temp_gt mrt_gt
where mrt_gt.release_id = l_release_id
and mrt_gt.plan_id = l_plan_id
and mrt_gt.trip_id is not null
and mrt_gt.planned_flag in (-1111,-2222,-3333,-4444,-5555)
and mrt_gt.mode_of_transport = l_mode_of_transport;
select count(1)
from mst_release_temp_gt mrt_gt
where mrt_gt.release_id = l_release_id
and mrt_gt.trip_id is not null
and mrt_gt.planned_flag in (-1111,-2222,-3333,-4444,-5555)
and mrt_gt.cm_id_of_trip is not null
and mrt_gt.trip_id not in (select mdl.trip_id
from mst_delivery_legs mdl
where plan_id = l_plan_id);
select count(1)
from mst_release_temp_gt mrt_gt
where mrt_gt.release_id = l_release_id
and mrt_gt.trip_id is not null
and mrt_gt.planned_flag in (-1111,-2222,-3333,-4444,-5555)
and mrt_gt.cm_id_of_trip is null
and mrt_gt.trip_id not in (select mdl.trip_id
from mst_delivery_legs mdl
where plan_id = l_plan_id);
select mrt_gt.planned_flag, count(*)
from mst_release_temp_gt mrt_gt
where mrt_gt.release_id = l_release_id
and mrt_gt.trip_id is not null
and mrt_gt.planned_flag in (-1111,-2222,-3333,-4444,-5555)
group by mrt_gt.planned_flag;
delete from mst_release_temp_gt mrt_gt
where mrt_gt.release_id = p_release_id
and mrt_gt.plan_id = p_plan_id
and mrt_gt.planned_flag in (-1111,-2222,-3333,-4444,-5555);
select 1
from mst_release_temp_gt
where release_id = l_release_id;
select sysdate
into l_release_start_date
from dual;
if g_update_tp_tables = 1 then
print_info(g_log_flow_of_control,'g_update_tp_tables : ' ||g_update_tp_tables);
update_tp_tables (l_return_status, l_group_tab, p_plan_id, p_release_id, p_release_mode, l_release_start_date);
delete from mst_release_temp
where release_id = p_release_id;
insert into mst_release_temp
( release_id
, group_id
, plan_id
, trip_id
, trip_number
, planned_flag
, release_status
, trip_start_date
, cm_id_of_trip
, continuous_move_id
, delivery_id
, out_of_scope
, trip_process_flag
, selected_trips
, trip_id_iface
, continuous_move_id_iface
, delivery_id_iface
, stop_id_iface
, continuous_move_sequence
, sr_cm_trip_id
, sr_trip_id
, stop_id
, sr_delivery_id
, delivery_detail_id_iface
, delivery_detail_id
, sr_delivery_detail_id
, sr_delivery_assignment_id
, sr_stop_id, status_code
, pickup_date
, dropoff_date
, pickup_location_id
, dropoff_location_id
, customer_id
, gross_weight
, net_weight
, weight_uom
, volume
, volume_uom
, currency_uom
, organization_id
, delivery_number
, compile_designator
, earliest_pickup_date
, latest_pickup_date
, earliest_acceptable_date
, latest_acceptable_date
, inventory_item_id
, carrier_id
, ship_method_code
, mode_of_transport
, load_tender_status
, lane_id, service_level
, cm_trip_number
, stop_location_id
, stop_sequence_number
, planned_arrival_date
, planned_departure_date
, departure_gross_weight
, departure_net_weight
, departure_volume
, departure_fill_percent
, wkend_layover_stops
, wkday_layover_stops
, distance_to_next_stop
, pln_loading_start_time
, pln_loading_end_time
, pln_unloading_start_time
, pln_unloading_end_time
, source_code
, ship_from_location_id
, ship_to_location_id
, requested_quantity
, source_header_number
, ship_set_id
, arrival_set_id
, org_id
, container_flag
, source_line_number
, split_from_delivery_detail_id
, line_direction
, po_shipment_number
, po_shipment_line_number
, src_requested_quantity_uom
, src_requested_quantity
, shipment_direction
, supplier_id
, party_id
)
(
select release_id
, group_id
, plan_id
, trip_id
, trip_number
, planned_flag
, release_status
, trip_start_date
, cm_id_of_trip
, continuous_move_id
, delivery_id
, out_of_scope
, trip_process_flag
, selected_trips
, trip_id_iface
, continuous_move_id_iface
, delivery_id_iface
, stop_id_iface
, continuous_move_sequence
, sr_cm_trip_id
, sr_trip_id
, stop_id
, sr_delivery_id
, delivery_detail_id_iface
, delivery_detail_id
, sr_delivery_detail_id
, sr_delivery_assignment_id
, sr_stop_id, status_code
, pickup_date
, dropoff_date
, pickup_location_id
, dropoff_location_id
, customer_id
, gross_weight
, net_weight
, weight_uom
, volume
, volume_uom
, currency_uom
, organization_id
, delivery_number
, compile_designator
, earliest_pickup_date
, latest_pickup_date
, earliest_acceptable_date
, latest_acceptable_date
, inventory_item_id
, carrier_id
, ship_method_code
, mode_of_transport
, load_tender_status
, lane_id, service_level
, cm_trip_number
, stop_location_id
, stop_sequence_number
, planned_arrival_date
, planned_departure_date
, departure_gross_weight
, departure_net_weight
, departure_volume
, departure_fill_percent
, wkend_layover_stops
, wkday_layover_stops
, distance_to_next_stop
, pln_loading_start_time
, pln_loading_end_time
, pln_unloading_start_time
, pln_unloading_end_time
, source_code
, ship_from_location_id
, ship_to_location_id
, requested_quantity
, source_header_number
, ship_set_id
, arrival_set_id
, org_id
, container_flag
, source_line_number
, split_from_delivery_detail_id
, line_direction
, po_shipment_number
, po_shipment_line_number
, src_requested_quantity_uom
, src_requested_quantity
, shipment_direction
, supplier_id
, party_id
from mst_release_temp_gt
where release_id = p_release_id
);
delete from mst_release_temp
where release_id = p_release_id;
procedure insert_plan_trips_ruleset(x_return_status out nocopy varchar2, p_plan_id in number, p_release_id in pls_integer) is
AREL_CARRIER_CODE constant number := 1;
select marrs.rule_set_id
, marrs.auto_release_restriction
, mrra.rule_id
from mst_plans mp
, mst_auto_rel_rule_sets marrs
, mst_rel_rule_associations mrra
where mp.plan_id = l_plan_id
and mp.auto_rel_rule_set_id = marrs.rule_set_id
and marrs.rule_set_id = mrra.rule_set_id;
select mrrc.attribute_code
, mrrc.condition
, mrrc.from_number_value
, mrrc.to_number_value
, mrrc.from_char_value
, mrrc.to_char_value
, mrrc.from_date_value
, mrrc.to_date_value
from mst_rel_rule_conditions mrrc
where mrrc.rule_id = l_rule_id;
print_info(g_log_flow_of_control,'insert_plan_trips_ruleset : Program started');
l_where_clause := l_where_clause || ' and ' || ' exists (select 1 from mst_deliveries md, mst_delivery_legs mdl';
l_where_clause := l_where_clause || ' and ' || ' exists (select 1 from mst_deliveries md, mst_delivery_legs mdl';
'insert into mst_release_temp_gt
(
release_id
, group_id
, plan_id
, trip_id
, sr_trip_id
, trip_number
, planned_flag
, release_status
, trip_start_date
, cm_id_of_trip
, continuous_move_sequence
, out_of_scope
, trip_process_flag
, selected_trips
, trip_id_iface
, status_code
, inventory_item_id
, organization_id
, carrier_id
, ship_method_code
, compile_designator
, mode_of_transport
, load_tender_status
, lane_id
, service_level
)
(
select '||p_release_id||'
, null
, mt.plan_id
, mt.trip_id
, mt.sr_trip_id
, mt.trip_number
, decode(nvl('||l_auto_release_restriction||',3),1,1,2,decode(mt.planned_flag,3,2,mt.planned_flag),mt.planned_flag)
, mt.release_status
, mt.trip_start_date
, mt.continuous_move_id
, mt.continuous_move_sequence
, mt.out_of_scope
, null
, 1
, wsh_trips_interface_s.nextval
, mt.status_code
, fvt.inventory_item_id
, fvt.organization_id
, mt.carrier_id
, mt.ship_method_code
, mp.compile_designator
, mt.mode_of_transport
, mt.load_tender_status
, mt.lane_id
, mt.service_level
from mst_plans mp
, mst_trips mt
, fte_vehicle_types fvt
where mt.plan_id = '||p_plan_id||'
and mt.plan_id = mp.plan_id
and mt.vehicle_type_id = fvt.vehicle_type_id (+) and ( '||l_where_clause||' ))';
print_info(g_log_flow_of_control,'insert_plan_trips_ruleset : Program ended');
print_info(g_log_flow_of_control,'insert_plan_trips_ruleset : ' || to_char(sqlcode) || ':' || SQLERRM);
end insert_plan_trips_ruleset;
procedure insert_plan_trips (x_return_status out nocopy varchar2, p_plan_id in number, p_release_id in number) is
begin
print_info(g_log_flow_of_control,'insert_plan_trips : Program started');
insert into mst_release_temp_gt
(
release_id
, group_id
, plan_id
, trip_id
, sr_trip_id
, trip_number
, planned_flag
, release_status
, trip_start_date
, cm_id_of_trip
, continuous_move_sequence
, out_of_scope
, trip_process_flag
, selected_trips
, trip_id_iface
, status_code
, inventory_item_id
, organization_id
, carrier_id
, ship_method_code
, compile_designator
, mode_of_transport
, load_tender_status
, lane_id
, service_level
)
(
select p_release_id
, null
, mt.plan_id
, mt.trip_id
, mt.sr_trip_id
, mt.trip_number
, mt.planned_flag
, mt.release_status
, mt.trip_start_date
, mt.continuous_move_id
, mt.continuous_move_sequence
, mt.out_of_scope
, null
, 1
, wsh_trips_interface_s.nextval
, mt.status_code
, fvt.inventory_item_id
, fvt.organization_id
, mt.carrier_id
, mt.ship_method_code
, mp.compile_designator
, mt.mode_of_transport
, mt.load_tender_status
, mt.lane_id
, mt.service_level
from mst_plans mp
, mst_trips mt
, fte_vehicle_types fvt
where mt.plan_id = p_plan_id
and mt.plan_id = mp.plan_id
and mt.vehicle_type_id = fvt.vehicle_type_id (+)
);
print_info(g_log_flow_of_control,'insert_plan_trips : Program ended');
print_info(g_log_flow_of_control,'insert_plan_trips : ' || to_char(sqlcode) || ':' || SQLERRM);
end insert_plan_trips;
fnd_message.set_token('N1',to_char(g_delete_record_count));
fnd_message.set_token('N1',to_char(g_delete_record_count_loop));
else -- delete from table
for i in 1..g_delete_record_count_loop loop
delete from mst_release_temp
where rownum < g_delete_record_count;
for i in 1..g_delete_record_count_loop loop
delete from wsh_trips_interface
where interface_action_code = g_tp_release_code
and rownum < g_delete_record_count;
for i in 1..g_delete_record_count_loop loop
delete from fte_moves_interface
where interface_action_code = g_tp_release_code
and rownum < g_delete_record_count;
for i in 1..g_delete_record_count_loop loop
delete from fte_trip_moves_interface
where interface_action_code = g_tp_release_code
and rownum < g_delete_record_count;
for i in 1..g_delete_record_count_loop loop
delete from wsh_trip_stops_interface
where interface_action_code = g_tp_release_code
and rownum < g_delete_record_count;
print_info(g_log_must_message,'Attempted to delete '||sql%rowcount||' records from wsh_trip_stops_interface.');
for i in 1..g_delete_record_count_loop loop
delete from wsh_new_del_interface
where interface_action_code = g_tp_release_code
and rownum < g_delete_record_count;
print_info(g_log_must_message,'Attempted to delete '||sql%rowcount||' records from wsh_new_del_interface.');
for i in 1..g_delete_record_count_loop loop
delete from wsh_del_legs_interface
where interface_action_code = g_tp_release_code
and rownum < g_delete_record_count;
for i in 1..g_delete_record_count_loop loop
delete from wsh_del_assgn_interface
where interface_action_code = g_tp_release_code
and rownum < g_delete_record_count;
for i in 1..g_delete_record_count_loop loop
delete from wsh_del_details_interface
where interface_action_code = g_tp_release_code
and rownum < g_delete_record_count;
for i in 1..g_delete_record_count_loop loop
delete from wsh_interface_errors
where interface_action_code = g_tp_release_code
and rownum < g_delete_record_count;
select auto_release
from mst_plans
where plan_id = l_plan_id;
insert_plan_trips_ruleset(l_return_status, p_plan_id, p_release_id);
insert_plan_trips (l_return_status, p_plan_id, p_release_id);
insert_plan_trips (l_return_status, p_plan_id, p_release_id);
insert_plan_trips_ruleset(l_return_status, p_plan_id, p_release_id);
insert_plan_trips (l_return_status, p_plan_id, p_release_id);
insert_plan_trips_ruleset(l_return_status, p_plan_id, p_release_id);