[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select count(1)
into l_count
from mst_trips
where plan_id = p_plan_id
and nvl(move_type, 2) <> 1;
select count(1)
into l_count
from mst_trips
where plan_id = p_plan_id
and mode_of_transport = p_mode_of_transport
and nvl(move_type, 2) <> 1 ;
select count(1)
into l_count
from mst_cm_trips
where plan_id = p_plan_id;
select total_orders
into l_count
from mst_plans
where plan_id = p_plan_id;
select decode(p_mode_of_transport, 'TRUCK', total_tl_orders, 'LTL', total_ltl_orders, 'PARCEL', total_parcel_orders, 0)
into l_count
from mst_plans
where plan_id = p_plan_id;
select total_cm_orders
into l_count
from mst_plans
where plan_id = p_plan_id;
select count(1)
into l_count
from mst_deliveries
where plan_id = p_plan_id
and delivery_id not in (
select delivery_id
from mst_delivery_legs
where plan_id = p_plan_id
);
select sum(gross_weight)
into l_count
from mst_deliveries
where plan_id = p_plan_id
and delivery_id not in (
select delivery_id
from mst_delivery_legs
where plan_id = p_plan_id
);
select sum(volume)
into l_count
from mst_deliveries
where plan_id = p_plan_id
and delivery_id not in (
select delivery_id
from mst_delivery_legs
where plan_id = p_plan_id
);
select sum(number_of_pieces)
into l_count
from mst_deliveries
where plan_id = p_plan_id
and delivery_id not in (
select delivery_id
from mst_delivery_legs
where plan_id = p_plan_id
);
select sum(number_of_pallets)
into l_count
from mst_deliveries
where plan_id = p_plan_id
and delivery_id not in (
select delivery_id
from mst_delivery_legs
where plan_id = p_plan_id
);
select sum(total_basic_transport_cost)
into l_fixed_charge
from mst_trips
where plan_id = p_plan_id
and mode_of_transport = p_mode;
select sum(total_stop_cost)
into l_stop_charge
from mst_trips
where plan_id = p_plan_id
and mode_of_transport = 'TRUCK';
select sum(total_load_unload_cost)
into l_ld_unld_charge
from mst_trips
where plan_id = p_plan_id
and mode_of_transport = 'TRUCK';
select sum(total_layover_cost)
into l_layover_charge
from mst_trips
where plan_id = p_plan_id
and mode_of_transport = 'TRUCK';
select sum(total_accessorial_cost)
into l_assessorial_charge
from mst_trips
where plan_id = p_plan_id
and mode_of_transport = p_mode;
SELECT SUM(dd.allocated_Cost)
FROM mst_delivery_details dd,
mst_delivery_assignments da
WHERE dd.plan_id = p_plan_id
AND da.plan_id = dd.plan_id
AND da.delivery_detail_id = dd.delivery_detail_id
and da.parent_delivery_detail_id is null
AND da.delivery_id = p_delivery_id;
select sum(mdl.allocated_fac_loading_cost)
into l_loading_charge
from mst_trip_stops mts,
mst_delivery_legs mdl
where mts.stop_id = p_stop_id
AND mts.plan_id = P_PLAN_ID
AND mts.plan_id = mdl.plan_Id
and mts.stop_id = mdl.pick_up_stop_id;
select sum(mdl.allocated_fac_unloading_cost)
into l_unloading_charge
from mst_trip_stops mts,
mst_delivery_legs mdl
where mts.stop_id = p_stop_id
and mts.stop_id = mdl.drop_off_stop_id;
select sum(md.gross_weight)
into l_loading_weight
from mst_trip_stops mts,
mst_delivery_legs mdl,
mst_deliveries md
where mts.stop_id = p_stop_id
and mts.stop_id = mdl.pick_up_stop_id
and mdl.delivery_id = md.delivery_id;
select sum(md.volume)
into l_loading_volume
from mst_trip_stops mts,
mst_delivery_legs mdl,
mst_deliveries md
where mts.stop_id = p_stop_id
and mts.stop_id = mdl.pick_up_stop_id
and mdl.delivery_id = md.delivery_id;
select sum(md.volume)
into l_loading_pallets
from mst_trip_stops mts,
mst_delivery_legs mdl,
mst_deliveries md
where mts.stop_id = p_stop_id
and mts.stop_id = mdl.pick_up_stop_id
and mdl.delivery_id = md.delivery_id;
select sum(md.number_of_pieces)
into l_loading_pieces
from mst_trip_stops mts,
mst_delivery_legs mdl,
mst_deliveries md
where mts.stop_id = p_stop_id
and mts.stop_id = mdl.pick_up_stop_id
and mdl.delivery_id = md.delivery_id;
select sum(md.gross_weight)
into l_unloading_weight
from mst_trip_stops mts,
mst_delivery_legs mdl,
mst_deliveries md
where mts.stop_id = p_stop_id
and mts.stop_id = mdl.drop_off_stop_id
and mdl.delivery_id = md.delivery_id;
select sum(md.volume)
into l_unloading_volume
from mst_trip_stops mts,
mst_delivery_legs mdl,
mst_deliveries md
where mts.stop_id = p_stop_id
and mts.stop_id = mdl.drop_off_stop_id
and mdl.delivery_id = md.delivery_id;
select sum(md.number_of_pallets)
into l_unloading_pallets
from mst_trip_stops mts,
mst_delivery_legs mdl,
mst_deliveries md
where mts.stop_id = p_stop_id
and mts.stop_id = mdl.drop_off_stop_id
and mdl.delivery_id = md.delivery_id;
select sum(md.number_of_pieces)
into l_unloading_pieces
from mst_trip_stops mts,
mst_delivery_legs mdl,
mst_deliveries md
where mts.stop_id = p_stop_id
and mts.stop_id = mdl.drop_off_stop_id
and mdl.delivery_id = md.delivery_id;
select
item.maximum_load_weight max_load_weight,
??decode() max_load_volume -- based on direct move, pool move, stop numbers, the effective cap could be different
pallet_floor_space * pallet_stacking_height max_number_of_pallets
from fte_vehicle_types fte, mtl_system_items item
where vehicle_type_id = p_vehicle_type_id
and fte.organization_id = item.organization_id
and fte.inventory_item_id = item.inventory_item_id
*/
/* Delivery Details */
function get_delivery_cost(p_delivery_id in number)
return number is
l_delivery_cost number;
select sum(allocated_transport_cost + allocated_fac_loading_cost + allocated_fac_unloading_cost)
into l_delivery_cost
from mst_delivery_legs
where delivery_id = p_delivery_id;
select total_cm_cost
into l_total_cm_cost
from mst_plans
where plan_id = p_plan_id;
select
sum(total_basic_transport_cost + total_stop_cost
+ total_load_unload_cost + total_layover_cost
+ total_accessorial_cost + total_handling_cost)
into l_total_cost_for_TLs_with_CMs
from mst_trips
where plan_id = p_plan_id
and continuous_move_id is not null
and mode_of_transport = 'TRUCK';
select count(1)
into l_total_number_of_tl
from mst_trips
where plan_id = p_plan_id
and mode_of_transport = 'TRUCK';
select count(1)
into l_total_number_of_tl_in_cm
from mst_trips
where plan_id = p_plan_id
and continuous_move_id is not null
and mode_of_transport = 'TRUCK';
select sum(mdd.net_weight)
into l_order_weight
from mst_delivery_details mdd
where mdd.source_code = p_source_code
and mdd.source_header_number = p_source_header_number;
select sum(mdd.volume)
into l_order_cube
from mst_delivery_details mdd
where mdd.source_code = p_source_code
and mdd.source_header_number = p_source_header_number;
select sum(ceil(mdd.number_of_pallets))
into l_order_pallets
from mst_delivery_details mdd
where mdd.source_code = p_source_code
and mdd.source_header_number = p_source_header_number;
select sum(mdd.requested_quantity)
into l_order_pieces
from mst_delivery_details mdd
where mdd.source_code = p_source_code
and mdd.source_header_number = p_source_header_number;
select sum(total_basic_transport_cost + total_stop_cost + total_load_unload_cost + total_layover_cost + total_accessorial_cost + total_handling_cost)
into l_carrier_tl_cost
from mst_trips
where plan_id = p_plan_id
and carrier_id = p_carrier_id
and mode_of_transport = 'TRUCK'
and continuous_move_id is null;
select sum(total_basic_transport_cost + total_accessorial_cost)
into l_carrier_ltl_parcel_cost
from mst_trips
where plan_id = p_plan_id
and carrier_id = p_carrier_id
and mode_of_transport in ('LTL', 'PARCEL');
select sum(TOTAL_TRANSPORTATION_COST)
into l_carrier_cm_cost
from mst_cm_trips
where plan_id = p_plan_id
and carrier_id = p_carrier_id;
SELECT SUM(md.gross_weight)
INTO l_carrier_weight
FROM MST_DELIVERIES md
WHERE md.plan_id = p_plan_id
AND md.delivery_id IN
( SELECT mdl.delivery_id
FROM MST_DELIVERY_LEGS mdl,
MST_TRIPS mt,
MST_TRIP_STOPS mts
WHERE mt.plan_id = md.plan_id
AND mt.carrier_id = p_carrier_id
AND mt.plan_id = mdl.plan_id
AND mt.trip_id = mdl.trip_id
AND mt.plan_id = mts.plan_id
AND mt.trip_id = mts.trip_id
AND mts.stop_location_id in
(SELECT wlo.wsh_location_id
FROM WSH_LOCATION_OWNERS wlo
WHERE wlo.owner_party_id = mt.carrier_id) );
SELECT SUM(md.gross_weight)
INTO l_carrier_weight
FROM mst_deliveries md
WHERE md.plan_id = p_plan_id
AND md.delivery_id IN
( SELECT mdl.delivery_id
FROM mst_delivery_legs mdl,
mst_trip_stops mts
WHERE mdl.plan_id = md.plan_id
AND mdl.plan_id = mts.plan_id
AND mdl.pick_up_stop_id = mts.stop_id
AND EXISTS
(SELECT 1
FROM wsh_location_owners wlo
WHERE wlo.owner_party_id = p_carrier_id
AND wlo.owner_type = CARRIER
AND wlo.wsh_location_id = mts.stop_location_id) );
SELECT SUM(md.gross_weight)
INTO l_carrier_weight
FROM mst_deliveries md
WHERE md.plan_id = p_plan_id
AND md.delivery_id IN
( SELECT mdl.delivery_id
FROM mst_delivery_legs mdl,
mst_trip_stops mts
WHERE mdl.plan_id = md.plan_id
AND mdl.plan_id = mts.plan_id
AND mdl.drop_off_stop_id = mts.stop_id
AND EXISTS
(SELECT 1
FROM wsh_location_owners wlo
WHERE wlo.owner_party_id = p_carrier_id
AND wlo.owner_type = CARRIER
AND wlo.wsh_location_id = mts.stop_location_id) );
SELECT SUM(md.volume)
INTO l_carrier_volume
FROM MST_DELIVERIES md
WHERE md.plan_id = p_plan_id
AND md.delivery_id IN
( SELECT mdl.delivery_id
FROM MST_DELIVERY_LEGS mdl,
MST_TRIPS mt,
MST_TRIP_STOPS mts
WHERE mt.plan_id = md.plan_id
AND mt.carrier_id = p_carrier_id
AND mt.plan_id = mdl.plan_id
AND mt.trip_id = mdl.trip_id
AND mt.plan_id = mts.plan_id
AND mt.trip_id = mts.trip_id
AND mts.stop_location_id in
(SELECT wlo.wsh_location_id
FROM WSH_LOCATION_OWNERS wlo
WHERE wlo.owner_party_id = mt.carrier_id) );
SELECT SUM(md.volume)
INTO l_carrier_volume
FROM mst_deliveries md
WHERE md.plan_id = p_plan_id
AND md.delivery_id IN
( SELECT mdl.delivery_id
FROM mst_delivery_legs mdl,
mst_trip_stops mts
WHERE mdl.plan_id = md.plan_id
AND mdl.plan_id = mts.plan_id
AND mdl.pick_up_stop_id = mts.stop_id
AND EXISTS
(SELECT 1
FROM wsh_location_owners wlo
WHERE wlo.owner_party_id = p_carrier_id
AND wlo.owner_type = CARRIER
AND wlo.wsh_location_id = mts.stop_location_id) );
SELECT SUM(md.volume)
INTO l_carrier_volume
FROM mst_deliveries md
WHERE md.plan_id = p_plan_id
AND md.delivery_id IN
( SELECT mdl.delivery_id
FROM mst_delivery_legs mdl,
mst_trip_stops mts
WHERE mdl.plan_id = md.plan_id
AND mdl.plan_id = mts.plan_id
AND mdl.drop_off_stop_id = mts.stop_id
AND EXISTS
(SELECT 1
FROM wsh_location_owners wlo
WHERE wlo.owner_party_id = p_carrier_id
AND wlo.owner_type = CARRIER
AND wlo.wsh_location_id = mts.stop_location_id) );
SELECT SUM(md.number_of_pallets)
INTO l_carrier_pallets
FROM MST_DELIVERIES md
WHERE md.plan_id = p_plan_id
AND md.delivery_id IN
( SELECT mdl.delivery_id
FROM MST_DELIVERY_LEGS mdl,
MST_TRIPS mt,
MST_TRIP_STOPS mts
WHERE mt.plan_id = md.plan_id
AND mt.carrier_id = p_carrier_id
AND mt.plan_id = mdl.plan_id
AND mt.trip_id = mdl.trip_id
AND mt.plan_id = mts.plan_id
AND mt.trip_id = mts.trip_id
AND mts.stop_location_id in
(SELECT wlo.wsh_location_id
FROM WSH_LOCATION_OWNERS wlo
WHERE wlo.owner_party_id = mt.carrier_id) );
SELECT SUM(md.number_of_pallets)
INTO l_carrier_pallets
FROM mst_deliveries md
WHERE md.plan_id = p_plan_id
AND md.delivery_id IN
( SELECT mdl.delivery_id
FROM mst_delivery_legs mdl,
mst_trip_stops mts
WHERE mdl.plan_id = md.plan_id
AND mdl.plan_id = mts.plan_id
AND mdl.pick_up_stop_id = mts.stop_id
AND EXISTS
(SELECT 1
FROM wsh_location_owners wlo
WHERE wlo.owner_party_id = p_carrier_id
AND wlo.owner_type = CARRIER
AND wlo.wsh_location_id = mts.stop_location_id) );
SELECT SUM(md.number_of_pallets)
INTO l_carrier_pallets
FROM mst_deliveries md
WHERE md.plan_id = p_plan_id
AND md.delivery_id IN
( SELECT mdl.delivery_id
FROM mst_delivery_legs mdl,
mst_trip_stops mts
WHERE mdl.plan_id = md.plan_id
AND mdl.plan_id = mts.plan_id
AND mdl.drop_off_stop_id = mts.stop_id
AND EXISTS
(SELECT 1
FROM wsh_location_owners wlo
WHERE wlo.owner_party_id = p_carrier_id
AND wlo.owner_type = CARRIER
AND wlo.wsh_location_id = mts.stop_location_id) );
SELECT SUM(md.number_of_pieces)
INTO l_carrier_pieces
FROM MST_DELIVERIES md
WHERE md.plan_id = p_plan_id
AND md.delivery_id IN
( SELECT mdl.delivery_id
FROM MST_DELIVERY_LEGS mdl,
MST_TRIPS mt,
MST_TRIP_STOPS mts
WHERE mt.plan_id = md.plan_id
AND mt.carrier_id = p_carrier_id
AND mt.plan_id = mdl.plan_id
AND mt.trip_id = mdl.trip_id
AND mt.plan_id = mts.plan_id
AND mt.trip_id = mts.trip_id
AND mts.stop_location_id in
(SELECT wlo.wsh_location_id
FROM WSH_LOCATION_OWNERS wlo
WHERE wlo.owner_party_id = mt.carrier_id) );
SELECT SUM(md.number_of_pieces)
INTO l_carrier_pieces
FROM mst_deliveries md
WHERE md.plan_id = p_plan_id
AND md.delivery_id IN
( SELECT mdl.delivery_id
FROM mst_delivery_legs mdl,
mst_trip_stops mts
WHERE mdl.plan_id = md.plan_id
AND mdl.plan_id = mts.plan_id
AND mdl.pick_up_stop_id = mts.stop_id
AND EXISTS
(SELECT 1
FROM wsh_location_owners wlo
WHERE wlo.owner_party_id = p_carrier_id
AND wlo.owner_type = CARRIER
AND wlo.wsh_location_id = mts.stop_location_id) );
SELECT SUM(md.number_of_pieces)
INTO l_carrier_pieces
FROM mst_deliveries md
WHERE md.plan_id = p_plan_id
AND md.delivery_id IN
( SELECT mdl.delivery_id
FROM mst_delivery_legs mdl,
mst_trip_stops mts
WHERE mdl.plan_id = md.plan_id
AND mdl.plan_id = mts.plan_id
AND mdl.drop_off_stop_id = mts.stop_id
AND EXISTS
(SELECT 1
FROM wsh_location_owners wlo
WHERE wlo.owner_party_id = p_carrier_id
AND wlo.owner_type = CARRIER
AND wlo.wsh_location_id = mts.stop_location_id) );
SELECT COUNT(DISTINCT mdd.SOURCE_HEADER_NUMBER)
INTO l_carrier_orders
FROM MST_DELIVERY_DETAILS MDD,
MST_DELIVERIES MD,
MST_DELIVERY_ASSIGNMENTS MDA
WHERE MD.PLAN_ID = MDA.PLAN_ID
AND MD.DELIVERY_ID = MDA.DELIVERY_ID
AND MD.DELIVERY_ID IN
(SELECT MDL.DELIVERY_ID
FROM MST_TRIPS T,
MST_TRIP_STOPS TS,
MST_DELIVERY_LEGS MDL
WHERE MDL.PLAN_ID = MD.PLAN_ID
AND TS.PLAN_ID = MDL.PLAN_ID
AND ( TS.STOP_ID = MDL.PICK_UP_STOP_ID
OR TS.STOP_ID = MDL.DROP_OFF_STOP_ID )
AND TS.PLAN_ID = T.PLAN_ID
AND TS.TRIP_ID = T.TRIP_ID
AND T.CARRIER_ID = P_CARRIER_ID
AND ts.stop_location_id in
(SELECT wlo.wsh_location_id
FROM WSH_LOCATION_OWNERS wlo
WHERE wlo.owner_party_id = t.carrier_id))
AND MDA.PLAN_ID = MDD.PLAN_ID
AND MDA.DELIVERY_DETAIL_ID = MDD.DELIVERY_DETAIL_ID
AND MD.PLAN_ID = P_PLAN_ID;
SELECT COUNT(DISTINCT mdd.SOURCE_HEADER_NUMBER)
INTO l_carrier_orders
FROM mst_delivery_details mdd,
mst_deliveries md,
mst_delivery_assignments mda
WHERE md.plan_id = p_plan_id
and md.plan_id = mda.plan_id
and md.delivery_id = mda.delivery_id
and mda.plan_id = mdd.plan_id
and mda.delivery_detail_id = mdd.delivery_detail_id
AND md.delivery_id IN
( SELECT mdl.delivery_id
FROM mst_delivery_legs mdl,
mst_trip_stops mts
WHERE mdl.plan_id = md.plan_id
AND mdl.plan_id = mts.plan_id
AND mdl.pick_up_stop_id = mts.stop_id
AND EXISTS
(SELECT 1
FROM wsh_location_owners wlo
WHERE wlo.owner_party_id = p_carrier_id
AND wlo.owner_type = CARRIER
AND wlo.wsh_location_id = mts.stop_location_id) );
SELECT COUNT(DISTINCT mdd.SOURCE_HEADER_NUMBER)
INTO l_carrier_orders
FROM mst_delivery_details mdd,
mst_deliveries md,
mst_delivery_assignments mda
WHERE md.plan_id = p_plan_id
and md.plan_id = mda.plan_id
and md.delivery_id = mda.delivery_id
and mda.plan_id = mdd.plan_id
and mda.delivery_detail_id = mdd.delivery_detail_id
AND md.delivery_id IN
( SELECT mdl.delivery_id
FROM mst_delivery_legs mdl,
mst_trip_stops mts
WHERE mdl.plan_id = md.plan_id
AND mdl.plan_id = mts.plan_id
AND mdl.drop_off_stop_id = mts.stop_id
AND EXISTS
(SELECT 1
FROM wsh_location_owners wlo
WHERE wlo.owner_party_id = p_carrier_id
AND wlo.owner_type = CARRIER
AND wlo.wsh_location_id = mts.stop_location_id) );
select sum(total_basic_transport_cost + total_stop_cost + total_load_unload_cost+ total_layover_cost+ total_accessorial_cost + total_handling_cost)
into l_carrier_tl_cost
from mst_trips
where plan_id = p_plan_id
and carrier_id = p_carrier_id
and mode_of_transport = 'TRUCK'
and service_level = p_service
and continuous_move_id is null;
select sum(total_basic_transport_cost + total_accessorial_cost)
into l_carrier_ltl_cost
from mst_trips
where plan_id = p_plan_id
and carrier_id = p_carrier_id
and mode_of_transport = 'LTL'
and service_level = p_service;
select sum(total_basic_transport_cost + total_accessorial_cost)
into l_carrier_parcel_cost
from mst_trips
where plan_id = p_plan_id
and carrier_id = p_carrier_id
and mode_of_transport = 'LTL'
and service_level = p_service;
select sum(gross_weight)
into l_carrier_weight
from(
select distinct md.delivery_id, md.gross_weight gross_weight
from mst_delivery_legs mdl,
mst_deliveries md,
mst_trips mt,
mst_trip_stops mts
where mt.plan_id = p_plan_id
and mt.mode_of_transport = p_mode
and mt.service_level = p_service
and mt.carrier_id = p_carrier_id
and mt.trip_id = mts.trip_id
and mts.stop_id = mdl.pick_up_stop_id
and mdl.delivery_id = md.delivery_id
);
select sum(volume)
into l_carrier_volume
from(
select distinct md.delivery_id, md.volume volume
from mst_delivery_legs mdl,
mst_deliveries md,
mst_trips mt,
mst_trip_stops mts
where mt.plan_id = p_plan_id
and mt.mode_of_transport = p_mode
and mt.service_level = p_service
and mt.carrier_id = p_carrier_id
and mt.trip_id = mts.trip_id
and mts.stop_id = mdl.pick_up_stop_id
and mdl.delivery_id = md.delivery_id
);
select sum(number_of_pallets)
into l_carrier_pallets
from(
select distinct md.delivery_id, md.number_of_pallets number_of_pallets
from mst_delivery_legs mdl,
mst_deliveries md,
mst_trips mt,
mst_trip_stops mts
where mt.plan_id = p_plan_id
and mt.mode_of_transport = p_mode
and mt.service_level = p_service
and mt.carrier_id = p_carrier_id
and mt.trip_id = mts.trip_id
and mts.stop_id = mdl.pick_up_stop_id
and mdl.delivery_id = md.delivery_id
);
select sum(number_of_pieces)
into l_carrier_pieces
from(
select distinct md.delivery_id, md.number_of_pieces number_of_pieces
from mst_delivery_legs mdl,
mst_deliveries md,
mst_trips mt,
mst_trip_stops mts
where mt.plan_id = p_plan_id
and mt.mode_of_transport = p_mode
and mt.service_level = p_service
and mt.carrier_id = p_carrier_id
and mt.trip_id = mts.trip_id
and mts.stop_id = mdl.pick_up_stop_id
and mdl.delivery_id = md.delivery_id
);
select sum(mdl.allocated_fac_loading_cost +
mdl.allocated_fac_unloading_cost +
mdl.ALLOCATED_FAC_SHP_HAND_COST +
mdl.ALLOCATED_FAC_REC_HAND_COST +
mdl.allocated_transport_cost )
into l_total_cost
from mst_deliveries md,
mst_delivery_legs mdl
where md.plan_id = p_plan_id
and md.customer_id = p_customer_id
AND md.plan_id = mdl.plan_id
and md.delivery_id = mdl.delivery_id;
/* select sum(md.net_weight)
into l_total_weight
from mst_deliveries md
where md.plan_id = p_plan_id
and md.customer_id = p_customer_id;
select sum(nvl(mdd.net_weight,0))
into l_total_weight
from mst_delivery_details mdd
, mst_delivery_assignments mda
, mst_deliveries md
where md.plan_id = mda.plan_id
and md.delivery_id = mda.delivery_id
and mda.plan_id = mdd.plan_id
and mda.delivery_detail_id = mdd.delivery_detail_id
and mdd.container_flag = 2
and md.plan_id = p_plan_id
and md.customer_id = p_customer_id;
/* select sum(md.volume)
into l_total_volume
from mst_deliveries md
where md.plan_id = p_plan_id
and md.customer_id = p_customer_id;
select sum(nvl(mdd.volume,0))
into l_total_volume
from mst_delivery_details mdd
, mst_delivery_assignments mda
, mst_deliveries md
where md.plan_id = mda.plan_id
and md.delivery_id = mda.delivery_id
and mda.plan_id = mdd.plan_id
and mda.delivery_detail_id = mdd.delivery_detail_id
and mdd.container_flag = 2
and md.plan_id = p_plan_id
and md.customer_id = p_customer_id;
/* select sum(md.number_of_pallets)
into l_total_pallets
from mst_deliveries md
where md.plan_id = p_plan_id
and md.customer_id = p_customer_id;
select sum(ceil(nvl(mdd.number_of_pallets,0)))
into l_total_pallets
from mst_delivery_details mdd
, mst_delivery_assignments mda
, mst_deliveries md
where md.plan_id = mda.plan_id
and md.delivery_id = mda.delivery_id
and mda.plan_id = mdd.plan_id
and mda.delivery_detail_id = mdd.delivery_detail_id
and mdd.container_flag = 2
and md.plan_id = p_plan_id
and md.customer_id = p_customer_id;
/* select sum(md.number_of_pieces)
into l_total_pieces
from mst_deliveries md
where md.plan_id = p_plan_id
and md.customer_id = p_customer_id;
select sum(nvl(mdd.requested_quantity,0))
into l_total_pieces
from mst_delivery_details mdd
, mst_delivery_assignments mda
, mst_deliveries md
where md.plan_id = mda.plan_id
and md.delivery_id = mda.delivery_id
and mda.plan_id = mdd.plan_id
and mda.delivery_detail_id = mdd.delivery_detail_id
and mdd.container_flag = 2
and md.plan_id = p_plan_id
and md.customer_id = p_customer_id;
select count(1)
into l_total_orders
from mst_delivery_details mdd,
mst_delivery_assignments mda
where mdd.plan_id = p_plan_id
and mdd.customer_id = p_customer_id
and mdd.split_from_delivery_detail_id is null
and mdd.delivery_detail_id = mda.delivery_detail_id
and mda.parent_delivery_detail_id is null
and exists (select 1
from mst_delivery_legs mdl
where mdl.delivery_id = mda.delivery_id
);
/*SELECT COUNT(DISTINCT mdd.SOURCE_HEADER_NUMBER)
INTO l_total_orders
FROM MST_DELIVERY_DETAILS MDD,
MST_DELIVERIES MD,
MST_DELIVERY_ASSIGNMENTS MDA
WHERE MD.PLAN_ID = MDA.PLAN_ID
AND MD.DELIVERY_ID = MDA.DELIVERY_ID
AND MD.DELIVERY_ID IN
(SELECT MDL.DELIVERY_ID
FROM MST_DELIVERY_LEGS MDL
WHERE MDL.PLAN_ID = MD.PLAN_ID)
AND MDA.PLAN_ID = MDD.PLAN_ID
AND MDA.DELIVERY_DETAIL_ID = MDD.DELIVERY_DETAIL_ID
AND MD.PLAN_ID = P_PLAN_ID
AND MD.CUSTOMER_ID = p_customer_id;*/
SELECT COUNT(DISTINCT dd.SOURCE_HEADER_NUMBER)
INTO l_total_orders
from (
SELECT mdd.SOURCE_HEADER_NUMBER
FROM MST_DELIVERY_DETAILS MDD,
MST_DELIVERIES MD,
MST_DELIVERY_ASSIGNMENTS MDA
WHERE MD.PLAN_ID = MDA.PLAN_ID
AND MD.DELIVERY_ID = MDA.DELIVERY_ID
AND MD.DELIVERY_ID IN
(SELECT MDL.DELIVERY_ID
FROM MST_DELIVERY_LEGS MDL
WHERE MDL.PLAN_ID = MD.PLAN_ID)
AND MDA.PLAN_ID = MDD.PLAN_ID
AND MDA.DELIVERY_DETAIL_ID = MDD.DELIVERY_DETAIL_ID
AND MD.PLAN_ID = p_PLAN_ID
AND MD.CUSTOMER_ID = p_customer_id
union all
SELECT mdd.SOURCE_HEADER_NUMBER
FROM MST_DELIVERY_DETAILS MDD,
MST_DELIVERIES MD,
MST_DELIVERY_ASSIGNMENTS MDA
WHERE MD.PLAN_ID = MDA.PLAN_ID
AND MD.DELIVERY_ID = MDA.DELIVERY_ID
AND not exists
(SELECT 1
FROM MST_DELIVERY_LEGS MDL
WHERE MDL.PLAN_ID = MD.PLAN_ID
and MDL.DELIVERY_ID = md.plan_id)
AND MDA.PLAN_ID = MDD.PLAN_ID
AND MDA.DELIVERY_DETAIL_ID = MDD.DELIVERY_DETAIL_ID
AND MD.PLAN_ID = p_PLAN_ID
AND MD.CUSTOMER_ID = p_customer_id) dd;
select delivery_id
from mst_deliveries md
where md.plan_id = p_plan_id
and md.customer_id = p_customer_id;
select count(mdl.delivery_leg_id)
from mst_delivery_legs mdl
where mdl.delivery_id = p_delivery_id
and mdl.plan_id = p_plan_id;
select mdl.delivery_leg_id
from mst_delivery_legs mdl
where mdl.delivery_id = p_delivery_id
and mdl.plan_id = p_plan_id;
select mt.mode_of_transport mode_of_transport, mt.trip_id trip_id
from mst_delivery_legs mdl, mst_trip_stops mts, mst_trips mt
where mdl.delivery_leg_id = p_delivery_leg_id
and mdl.plan_id = mts.plan_id
and mdl.pick_up_stop_id = mts.stop_id
and mdl.plan_id = mt.plan_id
and mts.trip_id = mt.trip_id;
select mts.stop_location_id location_id
from mst_delivery_legs mdl, mst_trip_stops mts
where delivery_leg_id = p_delivery_leg_id
and mdl.plan_id = mts.plan_id
and mdl.pick_up_stop_id = mts.stop_id ;
select mts.stop_location_id location_id
from mst_delivery_legs mdl, mst_trip_stops mts
where delivery_leg_id = p_delivery_leg_id
and mdl.plan_id = mts.plan_id
and mdl.drop_off_stop_id = mts.stop_id ;
select delivery_id
from mst_deliveries md
where md.plan_id = p_plan_id
and decode(p_partner_type, 0, md.customer_id, md.supplier_id) = p_partner_id;
select count(mdl.delivery_leg_id)
from mst_delivery_legs mdl
where mdl.delivery_id = p_delivery_id
and mdl.plan_id = p_plan_id;
select mdl.delivery_leg_id
from mst_delivery_legs mdl
where mdl.delivery_id = p_delivery_id
and mdl.plan_id = p_plan_id;
select mt.mode_of_transport mode_of_transport, mt.trip_id trip_id
from mst_delivery_legs mdl, mst_trip_stops mts, mst_trips mt
where mdl.delivery_leg_id = p_delivery_leg_id
and mdl.plan_id = mts.plan_id
and mdl.pick_up_stop_id = mts.stop_id
and mdl.plan_id = mt.plan_id
and mts.trip_id = mt.trip_id;
select mts.stop_location_id location_id
from mst_delivery_legs mdl, mst_trip_stops mts
where delivery_leg_id = p_delivery_leg_id
and mdl.plan_id = mts.plan_id
and mdl.pick_up_stop_id = mts.stop_id ;
select mts.stop_location_id location_id
from mst_delivery_legs mdl, mst_trip_stops mts
where delivery_leg_id = p_delivery_leg_id
and mdl.plan_id = mts.plan_id
and mdl.drop_off_stop_id = mts.stop_id ;
select count(mt.trip_id)
into l_num_of_direct_tls
from mst_trips mt
where mt.plan_id = p_plan_id
and mt.trip_id in
(select distinct mts.trip_id
from mst_trip_stops mts
, mst_delivery_legs mdl
, mst_deliveries md
where md.plan_id = mt.plan_id
and md.customer_id = p_partner_id
and mts.plan_id = md.plan_id
and mts.stop_location_id = md.dropoff_location_id
and mdl.plan_id = md.plan_id
and mdl.delivery_id = md.delivery_id
and mdl.trip_id = mts.trip_id
and mdl.drop_off_stop_id = mts.stop_id)
and mt.mode_of_transport = 'TRUCK'
and EXISTS
(select ts.trip_id
from mst_trip_stops ts
where ts.plan_id = mt.plan_id
and ts.trip_id = mt.trip_id
having count(ts.stop_id) =2
group by ts.trip_id);
select count(mt.trip_id)
into l_num_of_multi_stop_tls
from mst_trips mt
where mt.plan_id = p_plan_id
and mt.trip_id in
(select distinct mts.trip_id
from mst_trip_stops mts
, mst_delivery_legs mdl
, mst_deliveries md
where md.plan_id = mt.plan_id
and md.customer_id = p_partner_id
and mts.plan_id = md.plan_id
and mts.stop_location_id = md.dropoff_location_id
and mdl.plan_id = md.plan_id
and mdl.delivery_id = md.delivery_id
and mdl.trip_id = mts.trip_id
and mdl.drop_off_stop_id = mts.stop_id)
and mt.mode_of_transport = 'TRUCK'
and EXISTS
(select ts.trip_id
from mst_trip_stops ts
where ts.plan_id = mt.plan_id
and ts.trip_id = mt.trip_id
having count(ts.stop_id) >2
group by ts.trip_id);
select count(mt.trip_id)
into l_num_of_ltls
from mst_trips mt
where mt.plan_id = p_plan_id
and mt.trip_id in
(select distinct mts.trip_id
from mst_trip_stops mts
, mst_delivery_legs mdl
, mst_deliveries md
where md.plan_id = mt.plan_id
and md.customer_id = p_partner_id
and mts.plan_id = md.plan_id
and mts.stop_location_id = md.dropoff_location_id
and mdl.plan_id = md.plan_id
and mdl.delivery_id = md.delivery_id
and mdl.trip_id = mts.trip_id
and mdl.drop_off_stop_id = mts.stop_id)
and mt.mode_of_transport = 'LTL';
select count(mt.trip_id)
into l_num_of_parcels
from mst_trips mt
where mt.plan_id = p_plan_id
and mt.trip_id in
(select distinct mts.trip_id
from mst_trip_stops mts
, mst_delivery_legs mdl
, mst_deliveries md
where md.plan_id = mt.plan_id
and md.customer_id = p_partner_id
and mts.plan_id = md.plan_id
and mts.stop_location_id = md.dropoff_location_id
and mdl.plan_id = md.plan_id
and mdl.delivery_id = md.delivery_id
and mdl.trip_id = mts.trip_id
and mdl.drop_off_stop_id = mts.stop_id)
and mt.mode_of_transport = 'PARCEL';
select count(mt.trip_id)
into l_num_of_direct_tls
from mst_trips mt
where mt.plan_id = p_plan_id
and mt.trip_id in
(select distinct mts.trip_id
from mst_trip_stops mts
, mst_delivery_legs mdl
, mst_deliveries md
where md.plan_id = mt.plan_id
and md.supplier_id = p_partner_id
and mts.plan_id = md.plan_id
and mts.stop_location_id = md.pickup_location_id
and mdl.plan_id = md.plan_id
and mdl.delivery_id = md.delivery_id
and mdl.trip_id = mts.trip_id
and mdl.pick_up_stop_id = mts.stop_id)
and mt.mode_of_transport = 'TRUCK'
and EXISTS
(select ts.trip_id
from mst_trip_stops ts
where ts.plan_id = mt.plan_id
and ts.trip_id = mt.trip_id
having count(ts.stop_id) =2
group by ts.trip_id);
select count(mt.trip_id)
into l_num_of_multi_stop_tls
from mst_trips mt
where mt.plan_id = p_plan_id
and mt.trip_id in
(select distinct mts.trip_id
from mst_trip_stops mts
, mst_delivery_legs mdl
, mst_deliveries md
where md.plan_id = mt.plan_id
and md.supplier_id = p_partner_id
and mts.plan_id = md.plan_id
and mts.stop_location_id = md.pickup_location_id
and mdl.plan_id = md.plan_id
and mdl.delivery_id = md.delivery_id
and mdl.trip_id = mts.trip_id
and mdl.pick_up_stop_id = mts.stop_id)
and mt.mode_of_transport = 'TRUCK'
and EXISTS
(select ts.trip_id
from mst_trip_stops ts
where ts.plan_id = mt.plan_id
and ts.trip_id = mt.trip_id
having count(ts.stop_id) >2
group by ts.trip_id);
select count(mt.trip_id)
into l_num_of_ltls
from mst_trips mt
where mt.plan_id = p_plan_id
and mt.trip_id in
(select distinct mts.trip_id
from mst_trip_stops mts
, mst_delivery_legs mdl
, mst_deliveries md
where md.plan_id = mt.plan_id
and md.supplier_id = p_partner_id
and mts.plan_id = md.plan_id
and mts.stop_location_id = md.pickup_location_id
and mdl.plan_id = md.plan_id
and mdl.delivery_id = md.delivery_id
and mdl.trip_id = mts.trip_id
and mdl.pick_up_stop_id = mts.stop_id)
and mt.mode_of_transport = 'LTL';
select count(mt.trip_id)
into l_num_of_parcels
from mst_trips mt
where mt.plan_id = p_plan_id
and mt.trip_id in
(select distinct mts.trip_id
from mst_trip_stops mts
, mst_delivery_legs mdl
, mst_deliveries md
where md.plan_id = mt.plan_id
and md.supplier_id = p_partner_id
and mts.plan_id = md.plan_id
and mts.stop_location_id = md.pickup_location_id
and mdl.plan_id = md.plan_id
and mdl.delivery_id = md.delivery_id
and mdl.trip_id = mts.trip_id
and mdl.pick_up_stop_id = mts.stop_id)
and mt.mode_of_transport = 'PARCEL';
SELECT fte.location_id customer_facility_id,
fte.facility_code facility_code,
fte.description description
FROM fte_location_parameters fte
WHERE fte.location_id IN
(SELECT DECODE(mdd.source_code, OE , mdd.ship_to_location_id,
RFC, mdd.ship_from_location_id) location_id
FROM mst_delivery_details mdd
WHERE mdd.plan_id = p_plan_id
AND mdd.customer_id = p_customer_id
AND mdd.source_code IN (OE, RFC) );
select distinct decode(source_code, OE, ship_to_location_id,
RFC, ship_from_location_id) customer_facility_id,
fte.facility_code facility_code,
fte.description description
from mst_delivery_details, fte_location_parameters fte
where plan_id = p_plan_id
and customer_id = p_customer_id
and source_code in (OE, RFC)
and decode(source_code, RFC, ship_from_location_id, ship_to_location_id) = fte.location_id;*/
SELECT fte.location_id supplier_facility_id,
fte.facility_code facility_code,
fte.description description
FROM fte_location_parameters fte
WHERE fte.location_id in
(SELECT DECODE(mdd.source_code, RTV, mdd.ship_to_location_id,
PO, mdd.ship_from_location_id) location_id
FROM mst_delivery_details mdd
WHERE mdd.plan_id = p_plan_id
AND mdd.supplier_id = p_supplier_id
AND mdd.source_code in (PO, RTV));
select distinct decode(source_code, RTV, ship_to_location_id,
PO, ship_from_location_id) supplier_facility_id,
fte.facility_code facility_code,
fte.description description
from mst_delivery_details, fte_location_parameters fte
where plan_id = p_plan_id
and supplier_id = p_supplier_id
and source_code in (PO, RTV)
and decode(source_code, RTV, ship_to_location_id, PO, ship_from_location_id) = fte.location_id;*/
SELECT count(stop_id)
FROM mst_trip_stops
WHERE plan_id = p_plan_id
AND trip_id = p_trip_id;
SELECT DL.delivery_leg_id
FROM mst_delivery_legs DL
WHERE DL.plan_id = p_plan_id
AND DL.delivery_id = p_delivery_id
AND DL.sequence_number = (SELECT min(DL1.sequence_number) seq_no
FROM mst_delivery_legs DL1
WHERE DL1.plan_id = DL.plan_id
AND DL1.delivery_id = DL.delivery_id);
SELECT DL.delivery_leg_id
FROM mst_delivery_legs DL
WHERE DL.plan_id = p_plan_id
AND DL.delivery_id = p_delivery_id
AND DL.sequence_number = (SELECT max(DL1.sequence_number) seq_no
FROM mst_delivery_legs DL1
WHERE DL1.plan_id = DL.plan_id
AND DL1.delivery_id = DL.delivery_id);
select decode (p_type, 0, min(sequence_number),
1, max(sequence_number)) seq_no
from mst_delivery_legs
where plan_id = p_plan_id
and delivery_id = p_delivery_id;
select delivery_leg_id
from mst_delivery_legs
where plan_id = p_plan_id
and delivery_id = p_delivery_id
and sequence_number = p_seq_no;
select sum(mdl.allocated_fac_loading_cost + mdl.allocated_fac_unloading_cost
+ mdl.ALLOCATED_FAC_SHP_HAND_COST + mdl.ALLOCATED_FAC_REC_HAND_COST + mdl.allocated_transport_cost)
into l_total_cost
from mst_deliveries md,
mst_delivery_legs mdl
where md.plan_id = p_plan_id
and md.supplier_id = p_supplier_id
and md.delivery_id = mdl.delivery_id;
/* select sum(md.net_weight)
into l_total_weight
from mst_deliveries md
where md.plan_id = p_plan_id
and md.supplier_id = p_supplier_id;
select sum(nvl(mdd.net_weight,0))
into l_total_weight
from mst_delivery_details mdd
, mst_delivery_assignments mda
, mst_deliveries md
where md.plan_id = mda.plan_id
and md.delivery_id = mda.delivery_id
and mda.plan_id = mdd.plan_id
and mda.delivery_detail_id = mdd.delivery_detail_id
and mdd.container_flag = 2
and md.plan_id = p_plan_id
and md.supplier_id = p_supplier_id;
/* select sum(md.volume)
into l_total_volume
from mst_deliveries md
where md.plan_id = p_plan_id
and md.supplier_id = p_supplier_id;
select sum(nvl(mdd.volume,0))
into l_total_volume
from mst_delivery_details mdd
, mst_delivery_assignments mda
, mst_deliveries md
where md.plan_id = mda.plan_id
and md.delivery_id = mda.delivery_id
and mda.plan_id = mdd.plan_id
and mda.delivery_detail_id = mdd.delivery_detail_id
and mdd.container_flag = 2
and md.plan_id = p_plan_id
and md.supplier_id = p_supplier_id;
/* select sum(md.number_of_pallets)
into l_total_pallets
from mst_deliveries md
where md.plan_id = p_plan_id
and md.supplier_id = p_supplier_id;
select sum(ceil(nvl(mdd.number_of_pallets,0)))
into l_total_pallets
from mst_delivery_details mdd
, mst_delivery_assignments mda
, mst_deliveries md
where md.plan_id = mda.plan_id
and md.delivery_id = mda.delivery_id
and mda.plan_id = mdd.plan_id
and mda.delivery_detail_id = mdd.delivery_detail_id
and mdd.container_flag = 2
and md.plan_id = p_plan_id
and md.supplier_id = p_supplier_id;
/* select sum(md.number_of_pieces)
into l_total_pieces
from mst_deliveries md
where md.plan_id = p_plan_id
and md.supplier_id = p_supplier_id;
select sum(nvl(mdd.requested_quantity,0))
into l_total_pieces
from mst_delivery_details mdd
, mst_delivery_assignments mda
, mst_deliveries md
where md.plan_id = mda.plan_id
and md.delivery_id = mda.delivery_id
and mda.plan_id = mdd.plan_id
and mda.delivery_detail_id = mdd.delivery_detail_id
and mdd.container_flag = 2
and md.plan_id = p_plan_id
and md.supplier_id = p_supplier_id;
select count(1)
into l_total_orders
from mst_delivery_details mdd,
mst_delivery_assignments mda
where mdd.plan_id = p_plan_id
and mdd.supplier_id = p_supplier_id
and mdd.split_from_delivery_detail_id is null
and mdd.delivery_detail_id = mda.delivery_detail_id
and mda.parent_delivery_detail_id is null
and exists (select 1
from mst_delivery_legs mdl
where mdl.delivery_id = mda.delivery_id
);
/*SELECT COUNT(DISTINCT mdd.SOURCE_HEADER_NUMBER)
INTO l_total_orders
FROM MST_DELIVERY_DETAILS MDD,
MST_DELIVERIES MD,
MST_DELIVERY_ASSIGNMENTS MDA
WHERE MD.PLAN_ID = MDA.PLAN_ID
AND MD.DELIVERY_ID = MDA.DELIVERY_ID
AND MD.DELIVERY_ID IN
(SELECT MDL.DELIVERY_ID
FROM MST_DELIVERY_LEGS MDL
WHERE MDL.PLAN_ID = MD.PLAN_ID)
AND MDA.PLAN_ID = MDD.PLAN_ID
AND MDA.DELIVERY_DETAIL_ID = MDD.DELIVERY_DETAIL_ID
AND MD.PLAN_ID = P_PLAN_ID
AND MD.SUPPLIER_ID = p_supplier_id;*/
SELECT COUNT(DISTINCT dd.SOURCE_HEADER_NUMBER)
INTO l_total_orders
from (
SELECT mdd.SOURCE_HEADER_NUMBER
FROM MST_DELIVERY_DETAILS MDD,
MST_DELIVERIES MD,
MST_DELIVERY_ASSIGNMENTS MDA
WHERE MD.PLAN_ID = MDA.PLAN_ID
AND MD.DELIVERY_ID = MDA.DELIVERY_ID
AND MD.DELIVERY_ID IN
(SELECT MDL.DELIVERY_ID
FROM MST_DELIVERY_LEGS MDL
WHERE MDL.PLAN_ID = MD.PLAN_ID)
AND MDA.PLAN_ID = MDD.PLAN_ID
AND MDA.DELIVERY_DETAIL_ID = MDD.DELIVERY_DETAIL_ID
AND MD.PLAN_ID = p_PLAN_ID
AND MD.SUPPLIER_ID = p_supplier_id
union all
SELECT mdd.SOURCE_HEADER_NUMBER
FROM MST_DELIVERY_DETAILS MDD,
MST_DELIVERIES MD,
MST_DELIVERY_ASSIGNMENTS MDA
WHERE MD.PLAN_ID = MDA.PLAN_ID
AND MD.DELIVERY_ID = MDA.DELIVERY_ID
AND not exists
(SELECT 1
FROM MST_DELIVERY_LEGS MDL
WHERE MDL.PLAN_ID = MD.PLAN_ID
and MDL.DELIVERY_ID = md.plan_id)
AND MDA.PLAN_ID = MDD.PLAN_ID
AND MDA.DELIVERY_DETAIL_ID = MDD.DELIVERY_DETAIL_ID
AND MD.PLAN_ID = p_PLAN_ID
AND MD.SUPPLIER_ID = p_supplier_id) dd;
select sum(mdl.allocated_fac_loading_cost + mdl.allocated_fac_unloading_cost
+ mdl.ALLOCATED_FAC_SHP_HAND_COST + mdl.ALLOCATED_FAC_REC_HAND_COST + mdl.allocated_transport_cost)
into l_total_cost
from mst_deliveries md,
mst_delivery_legs mdl
where md.plan_id = p_plan_id
and decode(p_facility_type, 0, md.customer_id, md.supplier_id) = p_cust_or_supp_id
and (md.pickup_location_id = p_location_id
or md.dropoff_location_id = p_location_id)
and md.delivery_id = mdl.delivery_id;
/* select sum(md.net_weight)
into l_total_weight
from mst_deliveries md
where md.plan_id = p_plan_id
and decode(p_facility_type, 0, md.customer_id, md.supplier_id) = p_cust_or_supp_id
and (md.pickup_location_id = p_location_id
or md.dropoff_location_id = p_location_id);
select sum(nvl(mdd.net_weight,0))
into l_total_weight
from mst_delivery_details mdd
, mst_delivery_assignments mda
, mst_deliveries md
where md.plan_id = mda.plan_id
and md.delivery_id = mda.delivery_id
and mda.plan_id = mdd.plan_id
and mda.delivery_detail_id = mdd.delivery_detail_id
and mdd.container_flag = 2
and md.plan_id = p_plan_id
and decode(p_facility_type, 0, md.customer_id, md.supplier_id) = p_cust_or_supp_id
and (md.pickup_location_id = p_location_id
or md.dropoff_location_id = p_location_id);
/* select sum(md.volume)
into l_total_cube
from mst_deliveries md
where md.plan_id = p_plan_id
and decode(p_facility_type, 0, md.customer_id, md.supplier_id) = p_cust_or_supp_id
and (md.pickup_location_id = p_location_id
or md.dropoff_location_id = p_location_id);
select sum(nvl(mdd.volume,0))
into l_total_cube
from mst_delivery_details mdd
, mst_delivery_assignments mda
, mst_deliveries md
where md.plan_id = mda.plan_id
and md.delivery_id = mda.delivery_id
and mda.plan_id = mdd.plan_id
and mda.delivery_detail_id = mdd.delivery_detail_id
and mdd.container_flag = 2
and md.plan_id = p_plan_id
and decode(p_facility_type, 0, md.customer_id, md.supplier_id) = p_cust_or_supp_id
and (md.pickup_location_id = p_location_id
or md.dropoff_location_id = p_location_id);
/* select sum(md.number_of_pallets)
into l_total_pallets
from mst_deliveries md
where md.plan_id = p_plan_id
and decode(p_facility_type, 0, md.customer_id, md.supplier_id) = p_cust_or_supp_id
and (md.pickup_location_id = p_location_id
or md.dropoff_location_id = p_location_id);
select sum(ceil(nvl(mdd.number_of_pallets,0)))
into l_total_pallets
from mst_delivery_details mdd
, mst_delivery_assignments mda
, mst_deliveries md
where md.plan_id = mda.plan_id
and md.delivery_id = mda.delivery_id
and mda.plan_id = mdd.plan_id
and mda.delivery_detail_id = mdd.delivery_detail_id
and mdd.container_flag = 2
and md.plan_id = p_plan_id
and decode(p_facility_type, 0, md.customer_id, md.supplier_id) = p_cust_or_supp_id
and (md.pickup_location_id = p_location_id
or md.dropoff_location_id = p_location_id);
/* select sum(md.number_of_pieces)
into l_total_pieces
from mst_deliveries md
where md.plan_id = p_plan_id
and decode(p_facility_type, 0, md.customer_id, md.supplier_id) = p_cust_or_supp_id
and (md.pickup_location_id = p_location_id
or md.dropoff_location_id = p_location_id);
select sum(nvl(mdd.requested_quantity,0))
into l_total_pieces
from mst_delivery_details mdd
, mst_delivery_assignments mda
, mst_deliveries md
where md.plan_id = mda.plan_id
and md.delivery_id = mda.delivery_id
and mda.plan_id = mdd.plan_id
and mda.delivery_detail_id = mdd.delivery_detail_id
and mdd.container_flag = 2
and md.plan_id = p_plan_id
and decode(p_facility_type, 0, md.customer_id, md.supplier_id) = p_cust_or_supp_id
and (md.pickup_location_id = p_location_id
or md.dropoff_location_id = p_location_id);
select count(1)
into l_total_orders
from mst_delivery_details mdd,
mst_delivery_assignments mda
where mdd.plan_id = p_plan_id
and decode(p_facility_type, 0, mdd.customer_id, mdd.supplier_id) = p_cust_or_supp_id
and (mdd.ship_from_location_id = p_location_id or
mdd.ship_to_location_id = p_location_id)
and mdd.split_from_delivery_detail_id is null
and mdd.delivery_detail_id = mda.delivery_detail_id
and mda.parent_delivery_detail_id is null
and exists (select 1
from mst_delivery_legs mdl
where mdl.delivery_id = mda.delivery_id
);
SELECT COUNT(DISTINCT dd.SOURCE_HEADER_NUMBER)
INTO l_total_orders
FROM (
SELECT mdd.SOURCE_HEADER_NUMBER
FROM MST_DELIVERY_DETAILS MDD,
MST_DELIVERIES MD,
MST_DELIVERY_ASSIGNMENTS MDA
WHERE MD.PLAN_ID = MDA.PLAN_ID
AND MD.DELIVERY_ID = MDA.DELIVERY_ID
AND MDA.PLAN_ID = MDD.PLAN_ID
AND MDA.DELIVERY_DETAIL_ID = MDD.DELIVERY_DETAIL_ID
AND MD.PLAN_ID = P_PLAN_ID
AND MD.CUSTOMER_ID = P_CUST_OR_SUPP_ID
AND MD.DELIVERY_ID IN
(SELECT MDL.DELIVERY_ID
FROM MST_DELIVERY_LEGS MDL,
MST_TRIP_STOPS MTS
WHERE MDL.PLAN_ID = MD.PLAN_ID
AND MDL.PLAN_ID = MTS.PLAN_ID
AND ( MDL.PICK_UP_STOP_ID = MTS.STOP_ID
OR MDL.DROP_OFF_STOP_ID = MTS.STOP_ID)
AND MTS.STOP_LOCATION_ID = P_LOCATION_ID)
union ALL
SELECT mdd.SOURCE_HEADER_NUMBER
FROM MST_DELIVERY_DETAILS MDD,
MST_DELIVERIES MD,
MST_DELIVERY_ASSIGNMENTS MDA
WHERE MD.PLAN_ID = MDA.PLAN_ID
AND MD.DELIVERY_ID = MDA.DELIVERY_ID
AND MDA.PLAN_ID = MDD.PLAN_ID
AND MDA.DELIVERY_DETAIL_ID = MDD.DELIVERY_DETAIL_ID
AND MD.PLAN_ID = P_PLAN_ID
AND MD.CUSTOMER_ID = P_CUST_OR_SUPP_ID
AND md.dropoff_location_id = p_location_id
AND NOT EXISTS (SELECT 1 FROM mst_delivery_legs mdl
WHERE mdl.plan_id=md.plan_id
AND mdl.delivery_id = md.delivery_id)) dd;
SELECT COUNT(DISTINCT dd.SOURCE_HEADER_NUMBER)
INTO l_total_orders
FROM (
SELECT mdd.SOURCE_HEADER_NUMBER
FROM MST_DELIVERY_DETAILS MDD,
MST_DELIVERIES MD,
MST_DELIVERY_ASSIGNMENTS MDA
WHERE MD.PLAN_ID = MDA.PLAN_ID
AND MD.DELIVERY_ID = MDA.DELIVERY_ID
AND MDA.PLAN_ID = MDD.PLAN_ID
AND MDA.DELIVERY_DETAIL_ID = MDD.DELIVERY_DETAIL_ID
AND MD.PLAN_ID = P_PLAN_ID
AND MD.SUPPLIER_ID = P_CUST_OR_SUPP_ID
AND MD.DELIVERY_ID IN
(SELECT MDL.DELIVERY_ID
FROM MST_DELIVERY_LEGS MDL,
MST_TRIP_STOPS MTS
WHERE MDL.PLAN_ID = MD.PLAN_ID
AND MDL.PLAN_ID = MTS.PLAN_ID
AND ( MDL.PICK_UP_STOP_ID = MTS.STOP_ID
OR MDL.DROP_OFF_STOP_ID = MTS.STOP_ID)
AND MTS.STOP_LOCATION_ID = P_LOCATION_ID)
union ALL
SELECT mdd.SOURCE_HEADER_NUMBER
FROM MST_DELIVERY_DETAILS MDD,
MST_DELIVERIES MD,
MST_DELIVERY_ASSIGNMENTS MDA
WHERE MD.PLAN_ID = MDA.PLAN_ID
AND MD.DELIVERY_ID = MDA.DELIVERY_ID
AND MDA.PLAN_ID = MDD.PLAN_ID
AND MDA.DELIVERY_DETAIL_ID = MDD.DELIVERY_DETAIL_ID
AND MD.PLAN_ID = P_PLAN_ID
AND MD.SUPPLIER_ID = P_CUST_OR_SUPP_ID
AND md.dropoff_location_id = p_location_id
AND NOT EXISTS (SELECT 1 FROM mst_delivery_legs mdl
WHERE mdl.plan_id=md.plan_id
AND mdl.delivery_id = md.delivery_id)) dd;
SELECT COUNT(mt.trip_id)
INTO l_total_trips
FROM mst_trips mt
WHERE mt.plan_id = p_plan_id
AND mt.trip_id IN
(select distinct mts.trip_id
from mst_trip_stops mts
, mst_delivery_legs mdl
, mst_deliveries md
where md.plan_id = mt.plan_id
and md.customer_id = p_partner_id
and mts.plan_id = md.plan_id
and mts.stop_location_id = md.dropoff_location_id
and md.dropoff_location_id = p_location_id
and mdl.plan_id = md.plan_id
and mdl.delivery_id = md.delivery_id
and mdl.trip_id = mts.trip_id
and mdl.drop_off_stop_id = mts.stop_id)
AND mt.mode_of_transport = TRUCK -- p_mode_of_transport
AND EXISTS
(SELECT ts.trip_id
FROM mst_trip_stops ts
WHERE ts.plan_id = mt.plan_id
AND ts.trip_id = mt.trip_ID
HAVING COUNT(ts.stop_id) =2
GROUP BY ts.trip_id) ;
SELECT COUNT(mt.trip_id)
INTO l_total_trips
FROM mst_trips mt
WHERE mt.plan_id = p_plan_id
AND mt.trip_id IN
(select distinct mts.trip_id
from mst_trip_stops mts
, mst_delivery_legs mdl
, mst_deliveries md
where md.plan_id = mt.plan_id
and md.supplier_id = p_partner_id
and mts.plan_id = md.plan_id
and mts.stop_location_id = md.pickup_location_id
and md.pickup_location_id = p_location_id
and mdl.plan_id = md.plan_id
and mdl.delivery_id = md.delivery_id
and mdl.trip_id = mts.trip_id
and mdl.pick_up_stop_id = mts.stop_id)
AND mt.mode_of_transport = TRUCK -- p_mode_of_transport
AND EXISTS
(SELECT ts.trip_id
FROM mst_trip_stops ts
WHERE ts.plan_id = mt.plan_id
AND ts.trip_id = mt.trip_ID
HAVING COUNT(ts.stop_id) =2
GROUP BY ts.trip_id) ;
SELECT COUNT(mt.trip_id)
INTO l_total_trips
FROM mst_trips mt
WHERE mt.plan_id = p_plan_id
AND mt.trip_id IN
(select distinct mts.trip_id
from mst_trip_stops mts
, mst_delivery_legs mdl
, mst_deliveries md
where md.plan_id = mt.plan_id
and md.customer_id = p_partner_id
and mts.plan_id = md.plan_id
and mts.stop_location_id = md.dropoff_location_id
and md.dropoff_location_id = p_location_id
and mdl.plan_id = md.plan_id
and mdl.delivery_id = md.delivery_id
and mdl.trip_id = mts.trip_id
and mdl.drop_off_stop_id = mts.stop_id)
AND mt.mode_of_transport = TRUCK -- p_mode_of_transport
AND EXISTS
(SELECT ts.trip_id
FROM mst_trip_stops ts
WHERE ts.plan_id = mt.plan_id
AND ts.trip_id = mt.trip_ID
HAVING COUNT(ts.stop_id) > 2
GROUP BY ts.trip_id) ;
SELECT COUNT(mt.trip_id)
INTO l_total_trips
FROM mst_trips mt
WHERE mt.plan_id = p_plan_id
AND mt.trip_id IN
(select distinct mts.trip_id
from mst_trip_stops mts
, mst_delivery_legs mdl
, mst_deliveries md
where md.plan_id = mt.plan_id
and md.supplier_id = p_partner_id
and mts.plan_id = md.plan_id
and mts.stop_location_id = md.pickup_location_id
and md.pickup_location_id = p_location_id
and mdl.plan_id = md.plan_id
and mdl.delivery_id = md.delivery_id
and mdl.trip_id = mts.trip_id
and mdl.pick_up_stop_id = mts.stop_id)
AND mt.mode_of_transport = TRUCK -- p_mode_of_transport
AND EXISTS
(SELECT ts.trip_id
FROM mst_trip_stops ts
WHERE ts.plan_id = mt.plan_id
AND ts.trip_id = mt.trip_ID
HAVING COUNT(ts.stop_id) >2
GROUP BY ts.trip_id) ;
/*SELECT COUNT(mt.trip_id)
INTO l_total_trips
FROM mst_trips mt
WHERE mt.plan_id = p_plan_id
AND mt.trip_id IN
(SELECT mdl.trip_id
FROM mst_delivery_legs mdl,
mst_deliveries md
WHERE mdl.plan_id = mt.plan_id
AND md.plan_id = mdl.plan_id
AND md.delivery_id = mdl.delivery_id
AND md.customer_id = p_partner_id
AND ( md.pickup_location_id = p_location_id
OR md.dropoff_location_id = p_location_id))
AND mt.mode_of_transport = p_mode_of_transport;*/
SELECT COUNT(mt.trip_id)
INTO l_total_trips
FROM mst_trips mt
WHERE mt.plan_id = p_plan_id
AND mt.trip_id IN
(SELECT mdl.trip_id
FROM mst_delivery_legs mdl,
mst_deliveries md,
mst_trip_stops mts
WHERE mdl.plan_id = mt.plan_id
AND md.plan_id = mdl.plan_id
AND md.delivery_id = mdl.delivery_id
AND md.customer_id = p_partner_id
AND mts.plan_id = mdl.plan_id
AND ( mdl.pick_up_stop_id = mts.stop_id
OR mdl.drop_off_stop_id = mts.stop_id)
AND mts.stop_location_id = p_location_id)
AND mt.mode_of_transport = p_mode_of_transport;
/*SELECT COUNT(mt.trip_id)
INTO l_total_trips
FROM mst_trips mt
WHERE mt.plan_id = p_plan_id
AND mt.trip_id IN
(SELECT mdl.trip_id
FROM mst_delivery_legs mdl,
mst_deliveries md
WHERE mdl.plan_id = mt.plan_id
AND md.plan_id = mdl.plan_id
AND md.delivery_id = mdl.delivery_id
AND md.supplier_id = p_partner_id
AND ( md.pickup_location_id = p_location_id
OR md.dropoff_location_id = p_location_id))
AND mt.mode_of_transport = p_mode_of_transport;*/
SELECT COUNT(mt.trip_id)
INTO l_total_trips
FROM mst_trips mt
WHERE mt.plan_id = p_plan_id
AND mt.trip_id IN
(SELECT mdl.trip_id
FROM mst_delivery_legs mdl,
mst_deliveries md,
mst_trip_stops mts
WHERE mdl.plan_id = mt.plan_id
AND md.plan_id = mdl.plan_id
AND md.delivery_id = mdl.delivery_id
AND md.supplier_id = p_partner_id
AND mts.plan_id = mdl.plan_id
AND ( mdl.pick_up_stop_id = mts.stop_id
OR mdl.drop_off_stop_id = mts.stop_id)
AND mts.stop_location_id = p_location_id)
AND mt.mode_of_transport = p_mode_of_transport;
SELECT COUNT(mt.trip_id)
FROM mst_trips mt
WHERE mt.plan_id = p_plan_id
AND mt.mode_of_transport = p_mode_of_transport
AND mt.trip_id IN (
SELECT ts.trip_id
FROM mst_trip_stops ts,
mst_delivery_legs mdl
WHERE ts.plan_id = p_plan_id
AND ts.stop_location_id = p_fac_loc_id
AND ts.plan_Id = mdl.plan_Id
AND ( ts.stop_id = mdl.pick_up_stop_id
OR ts.stop_id = mdl.drop_off_stop_id));
SELECT COUNT(mt.trip_id)
FROM mst_trips mt
WHERE mt.plan_id = p_plan_id
AND mt.mode_of_transport = p_mode_of_transport
AND mt.continuous_move_id IS NULL
AND EXISTS (SELECT 1
FROM mst_trip_stops mts
WHERE mts.plan_id = mt.plan_id
AND mts.trip_id = mt.trip_id
AND mts.stop_location_id = p_fac_loc_id)
AND NOT EXISTS (SELECT 1
FROM mst_delivery_legs mdl
WHERE mdl.plan_id = mt.plan_id
AND mdl.trip_id = mt.trip_id);
SELECT COUNT(mt.trip_id)
FROM mst_trips mt
WHERE mt.plan_id = p_plan_id
AND mt.mode_of_transport = p_mode_of_transport
AND mt.trip_id IN (
SELECT ts.trip_id
FROM mst_trip_stops ts,
mst_delivery_legs mdl
WHERE ts.plan_id = p_plan_id
AND ts.stop_location_id = p_fac_loc_id
AND ts.stop_id = mdl.pick_up_stop_id
AND ts.plan_id = mdl.plan_id);
SELECT count(mt.trip_id)
FROM mst_trips mt
WHERE mt.plan_id = p_plan_id
AND mt.mode_of_transport = p_mode_of_transport
AND mt.trip_id IN (
SELECT ts.trip_id
FROM mst_trip_stops ts,
mst_delivery_legs mdl
WHERE ts.plan_id = p_plan_id
AND ts.stop_location_id = p_fac_loc_id
AND ts.stop_id = mdl.drop_off_stop_id
AND ts.plan_id = mdl.plan_id);
SELECT COUNT(DISTINCT mdd.source_header_number)
INTO l_orders
FROM mst_delivery_details mdd
WHERE mdd.plan_id = p_plan_id
AND mdd.delivery_detail_id IN
(SELECT mda.delivery_detail_id
FROM mst_delivery_assignments mda,
mst_trips mt,
mst_trip_stops mts,
mst_delivery_legs mdl,
mst_deliveries md
WHERE mda.plan_id = mdd.plan_id
AND mda.plan_id = md.plan_id
AND mda.delivery_id = md.delivery_id
AND md.plan_id = mdl.plan_id
AND md.delivery_id = mdl.delivery_id
AND mdl.plan_id = mts.plan_id
AND mdl.drop_off_stop_id = mts.stop_id
AND mts.stop_location_id = p_fac_loc_id
AND mt.plan_id = mts.plan_id
AND mt.trip_id = mts.trip_id
AND mt.mode_of_transport = p_mode_of_transport
AND ( md.pickup_location_id <> p_fac_loc_id
OR md.dropoff_location_id <> p_fac_loc_id ));
SELECT COUNT(DISTINCT mdd.source_header_number)
INTO l_orders
FROM mst_delivery_details mdd
WHERE mdd.plan_id = p_plan_id
AND mdd.delivery_detail_id IN
( SELECT mda.delivery_detail_id
FROM mst_delivery_assignments mda,
mst_deliveries md,
mst_trips mt,
mst_delivery_legs mdl
WHERE mda.plan_id = mdd.plan_id
AND mda.plan_id = md.plan_id
AND mda.delivery_id = md.delivery_id
AND md.plan_id = mdl.plan_id
AND md.delivery_id = mdl.delivery_id
AND mdl.plan_id = mt.plan_Id
AND mdl.trip_id = mt.trip_id
AND mt.mode_of_transport = p_mode_of_transport
AND ( md.pickup_location_id = p_fac_loc_id
OR md.dropoff_location_id = p_fac_loc_id));
SELECT COUNT(DISTINCT mdd.source_header_number)
INTO l_orders
FROM mst_delivery_details mdd
WHERE mdd.plan_id = p_plan_id
AND mdd.delivery_detail_id IN
( SELECT mda.delivery_detail_id
FROM mst_delivery_assignments mda,
mst_deliveries md,
mst_trips mt,
mst_delivery_legs mdl
WHERE mda.plan_id = mdd.plan_id
AND mda.plan_id = md.plan_id
AND mda.delivery_id = md.delivery_id
AND md.plan_id = mdl.plan_id
AND md.delivery_id = mdl.delivery_id
AND mdl.plan_id = mt.plan_Id
AND mdl.trip_id = mt.trip_id
AND mt.mode_of_transport = p_mode_of_transport
AND md.pickup_location_id = p_fac_loc_id );
SELECT COUNT(DISTINCT mdd.source_header_number)
INTO l_orders
FROM mst_delivery_details mdd
WHERE mdd.plan_id = p_plan_id
AND mdd.delivery_detail_id IN
( SELECT mda.delivery_detail_id
FROM mst_delivery_assignments mda,
mst_deliveries md,
mst_trips mt,
mst_delivery_legs mdl
WHERE mda.plan_id = mdd.plan_id
AND mda.plan_id = md.plan_id
AND mda.delivery_id = md.delivery_id
AND md.plan_id = mdl.plan_id
AND md.delivery_id = mdl.delivery_id
AND mdl.plan_id = mt.plan_Id
AND mdl.trip_id = mt.trip_id
AND mt.mode_of_transport = p_mode_of_transport
AND md.dropoff_location_id = p_fac_loc_id );
select count(distinct mdd.source_header_number)
into l_total_orders
from mst_delivery_details mdd,
mst_deliveries md,
mst_delivery_assignments mda
where md.plan_id = mda.plan_id
and md.delivery_id = mda.delivery_id
and md.delivery_id in
(select mdl.delivery_id
from mst_trips t,
mst_trip_stops ts,
mst_delivery_legs mdl
where mdl.plan_id = md.plan_id
and ts.plan_id = mdl.plan_id
and (ts.stop_id = mdl.pick_up_stop_id
or ts.stop_id = mdl.drop_off_stop_id)
and ts.stop_location_id = p_my_fac_location_id
and ts.plan_id = t.plan_id
and ts.trip_id = t.trip_id)
and mda.plan_id = mdd.plan_id
and mda.delivery_detail_id = mdd.delivery_detail_id
and md.plan_id = p_plan_id
and mdd.container_flag = 2;
select count(mdd.delivery_detail_id)
into l_total_orders
from mst_trips mt,
mst_trip_stops mts,
mst_delivery_legs mdl,
mst_deliveries md,
mst_delivery_details mdd,
mst_delivery_assignments mda
where mt.plan_id = p_plan_id
and mt.mode_of_transport = p_mode
and mt.trip_id = mts.trip_id
and mt.trip_id = mdl.trip_id
and (mdl.pick_up_stop_id = mts.stop_id or
mdl.drop_off_stop_id = mts.stop_id)
and mts.stop_location_id = p_my_fac_location_id
and mdl.delivery_id = md.delivery_id
and md.delivery_id = mda.delivery_id
and mda.delivery_detail_id = mdd.delivery_detail_id
and mda.parent_delivery_detail_id is null
and mdd.split_from_delivery_detail_id is null;
/*select count(mdd.delivery_detail_id)
into l_total_orders
from mst_trips mt,
mst_trip_stops mts,
mst_delivery_legs mdl,
mst_deliveries md,
mst_delivery_details mdd,
mst_delivery_assignments mda
where mt.plan_id = p_plan_id
and mt.mode_of_transport = p_mode
and mt.trip_id = mts.trip_id
and mt.trip_id = mdl.trip_id
and mdl.pick_up_stop_id = mts.stop_id
and mts.stop_location_id = p_my_fac_location_id
and mdl.delivery_id = md.delivery_id
and md.delivery_id = mda.delivery_id
and mda.delivery_detail_id = mdd.delivery_detail_id
and mda.parent_delivery_detail_id is null
and mdd.split_from_delivery_detail_id is null;*/
SELECT COUNT(DISTINCT mdd.SOURCE_HEADER_NUMBER)
INTO l_total_orders
FROM MST_DELIVERY_DETAILS MDD,
MST_DELIVERIES MD,
MST_DELIVERY_ASSIGNMENTS MDA
WHERE MD.PLAN_ID = MDA.PLAN_ID
AND MD.DELIVERY_ID = MDA.DELIVERY_ID
AND MD.DELIVERY_ID IN
(SELECT MDL.DELIVERY_ID
FROM MST_TRIPS T,
MST_TRIP_STOPS TS,
MST_DELIVERY_LEGS MDL
WHERE MDL.PLAN_ID = MD.PLAN_ID
AND TS.PLAN_ID = MDL.PLAN_ID
AND TS.STOP_ID = MDL.PICK_UP_STOP_ID
AND TS.STOP_LOCATION_ID = p_my_fac_location_id
AND TS.PLAN_ID = T.PLAN_ID
AND TS.TRIP_ID = T.TRIP_ID
AND T.MODE_OF_TRANSPORT = P_MODE)
AND MDA.PLAN_ID = MDD.PLAN_ID
AND MDA.DELIVERY_DETAIL_ID = MDD.DELIVERY_DETAIL_ID
AND MD.PLAN_ID = P_PLAN_ID
AND MDD.CONTAINER_FLAG = 2;
/*select count(mdd.delivery_detail_id)
into l_total_orders
from mst_trips mt,
mst_trip_stops mts,
mst_delivery_legs mdl,
mst_deliveries md,
mst_delivery_details mdd,
mst_delivery_assignments mda
where mt.plan_id = p_plan_id
and mt.mode_of_transport = p_mode
and mt.trip_id = mts.trip_id
and mt.trip_id = mdl.trip_id
and mdl.drop_off_stop_id = mts.stop_id
and mts.stop_location_id = p_my_fac_location_id
and mdl.delivery_id = md.delivery_id
and md.delivery_id = mda.delivery_id
and mda.delivery_detail_id = mdd.delivery_detail_id
and mda.parent_delivery_detail_id is null
and mdd.split_from_delivery_detail_id is null;*/
SELECT COUNT(DISTINCT mdd.SOURCE_HEADER_NUMBER)
INTO l_total_orders
FROM MST_DELIVERY_DETAILS MDD,
MST_DELIVERIES MD,
MST_DELIVERY_ASSIGNMENTS MDA
WHERE MD.PLAN_ID = MDA.PLAN_ID
AND MD.DELIVERY_ID = MDA.DELIVERY_ID
AND MD.DELIVERY_ID IN
(SELECT MDL.DELIVERY_ID
FROM MST_TRIPS T,
MST_TRIP_STOPS TS,
MST_DELIVERY_LEGS MDL
WHERE MDL.PLAN_ID = MD.PLAN_ID
AND TS.PLAN_ID = MDL.PLAN_ID
AND TS.STOP_ID = MDL.DROP_OFF_STOP_ID
AND TS.STOP_LOCATION_ID = p_my_fac_location_id
AND TS.PLAN_ID = T.PLAN_ID
AND TS.TRIP_ID = T.TRIP_ID
AND T.MODE_OF_TRANSPORT = P_MODE)
AND MDA.PLAN_ID = MDD.PLAN_ID
AND MDA.DELIVERY_DETAIL_ID = MDD.DELIVERY_DETAIL_ID
AND MD.PLAN_ID = P_PLAN_ID
AND MDD.CONTAINER_FLAG = 2;
/*SELECT SUM(NVL(mdl.allocated_fac_loading_cost,0) +
NVL(mdl.allocated_transport_cost,0) +
NVL(mdl.allocated_fac_shp_hand_cost,0) )
INTO l_loading_cost
FROM mst_trips mt,
mst_trip_stops mts,
mst_delivery_legs mdl
WHERE mt.plan_id = p_plan_id
AND mt.mode_of_transport = p_mode
AND mt.plan_id = mts.plan_id
AND mt.trip_id = mts.trip_id
AND mts.plan_id = mdl.plan_id
AND mdl.pick_up_stop_id = mts.stop_id
AND mts.stop_location_id = p_my_fac_location_id;*/
SELECT SUM(NVL(mdl.allocated_fac_loading_cost,0) +
NVL(mdl.allocated_transport_cost,0) +
NVL(mdl.allocated_fac_shp_hand_cost,0) )
INTO l_loading_cost
FROM mst_delivery_legs mdl
WHERE mdl.plan_id = p_plan_id
AND mdl.delivery_id IN
( SELECT md.delivery_id
FROM mst_delivery_legs mdl1,
mst_deliveries md,
mst_trips mt,
mst_trip_stops mts
WHERE mt.plan_id = mdl1.plan_id
AND mt.trip_id = mdl1.trip_id
AND mt.mode_of_transport = p_mode
and mt.plan_id = mts.plan_Id
and mt.trip_id = mts.trip_id
and mts.stop_location_id = p_my_fac_location_id
AND md.plan_id = mdl1.plan_id
AND md.delivery_id = mdl1.delivery_id
AND md.plan_id = mdl.plan_id
AND md.PICKUP_LOCATION_ID = p_my_fac_location_id);
SELECT SUM(NVL(mdl.allocated_fac_loading_cost,0) +
NVL(mdl.allocated_transport_cost,0) +
NVL(mdl.allocated_fac_shp_hand_cost,0) )
INTO l_loading_cost_temp
FROM mst_delivery_legs mdl
WHERE mdl.plan_id = p_plan_id
AND mdl.delivery_id IN
( SELECT md.delivery_id
FROM mst_delivery_legs mdl1,
mst_deliveries md,
mst_trips mt,
mst_trip_stops mts
WHERE mt.plan_id = mdl1.plan_id
AND mt.trip_id = mdl1.trip_id
AND mt.mode_of_transport = p_mode
and mt.plan_id = mts.plan_Id
and mt.trip_id = mts.trip_id
and mts.stop_location_id = p_my_fac_location_id
AND MTS.STOP_ID = mdl.pick_up_stop_id
AND md.plan_id = mdl1.plan_id
AND md.delivery_id = mdl1.delivery_id
AND md.plan_id = mdl.plan_id
AND md.PICKUP_LOCATION_ID <> mts.stop_location_id
AND MD.DROPOFF_LOCATION_ID <> mts.stop_location_id);
/*select sum(NVL(mdl.allocated_fac_unloading_cost,0)+
NVL(mdl.allocated_transport_cost,0) +
NVL(mdl.allocated_fac_rec_hand_cost,0) )
into l_unloading_cost
from mst_trips mt,
mst_trip_stops mts,
mst_delivery_legs mdl
where mt.plan_id = p_plan_id
and mt.mode_of_transport = p_mode
and mt.plan_id = mts.plan_id
and mt.trip_id = mts.trip_id
and mts.plan_id = mdl.plan_id
and mdl.drop_off_stop_id = mts.stop_id
and mts.stop_location_id = p_my_fac_location_id;*/
SELECT SUM(NVL(mdl.allocated_fac_unloading_cost,0) +
NVL(mdl.allocated_transport_cost,0) +
NVL(mdl.allocated_fac_rec_hand_cost,0) )
INTO l_unloading_Cost
FROM mst_delivery_legs mdl
WHERE mdl.plan_id = p_plan_id
AND mdl.delivery_id IN
( SELECT md.delivery_id
FROM mst_delivery_legs mdl1,
mst_deliveries md,
mst_trips mt,
mst_trip_stops mts
WHERE mt.plan_id = mdl1.plan_id
AND mt.trip_id = mdl1.trip_id
AND mt.mode_of_transport = p_mode
and mt.plan_id = mts.plan_Id
and mt.trip_id = mts.trip_id
and mts.stop_location_id = p_my_fac_location_id
--AND mts.stop_id = mdl.drop_off_stop_id
AND md.plan_id = mdl1.plan_id
AND md.delivery_id = mdl1.delivery_id
AND md.plan_id = mdl.plan_id
AND md.DROPOFF_LOCATION_ID = p_my_fac_location_id);
SELECT SUM(NVL(mdl.allocated_fac_loading_cost,0) +
NVL(mdl.allocated_transport_cost,0) +
NVL(mdl.allocated_fac_shp_hand_cost,0) )
INTO l_unloading_cost_temp
FROM mst_delivery_legs mdl
WHERE mdl.plan_id = p_plan_id
AND mdl.delivery_id IN
( SELECT md.delivery_id
FROM mst_delivery_legs mdl1,
mst_deliveries md,
mst_trips mt,
mst_trip_stops mts
WHERE mt.plan_id = mdl1.plan_id
AND mt.trip_id = mdl1.trip_id
AND mt.mode_of_transport = p_mode
and mt.plan_id = mts.plan_Id
and mt.trip_id = mts.trip_id
and mts.stop_location_id = p_my_fac_location_id
AND MTS.STOP_ID = mdl.drop_off_stop_id
AND md.plan_id = mdl1.plan_id
AND md.delivery_id = mdl1.delivery_id
AND md.plan_id = mdl.plan_id
AND md.PICKUP_LOCATION_ID <> mts.stop_location_id
AND MD.DROPOFF_LOCATION_ID <> mts.stop_location_id);
select sum(mdl.allocated_fac_loading_cost + mdl.ALLOCATED_FAC_SHP_HAND_COST + mdl.allocated_transport_cost) total_departing_cost
from mst_trips mt,
mst_trip_stops mts,
mst_delivery_legs mdl
where mt.plan_id = p_plan_id
and mt.mode_of_transport = p_mode
and mt.trip_id = mts.trip_id
and mdl.pick_up_stop_id = mts.stop_id
and mts.stop_location_id = p_my_fac_location_id;
select sum(mdl.allocated_fac_unloading_cost + mdl.ALLOCATED_FAC_REC_HAND_COST+ mdl.allocated_transport_cost) total_arriving_cost
from mst_trips mt,
mst_trip_stops mts,
mst_delivery_legs mdl
where mt.plan_id = p_plan_id
and mt.mode_of_transport = p_mode
and mt.trip_id = mts.trip_id
and mdl.drop_off_stop_id = mts.stop_id
and mts.stop_location_id = p_my_fac_location_id;
select sum(md.gross_weight)
into l_loading_weight
from mst_trips mt,
mst_trip_stops mts,
mst_delivery_legs mdl,
mst_deliveries md
where mt.plan_id = p_plan_id
and mt.mode_of_transport = p_mode
and mt.plan_id = mts.plan_id
and mt.trip_id = mts.trip_id
and mts.plan_id = mdl.plan_id
and mdl.pick_up_stop_id = mts.stop_id
and mts.stop_location_id = p_my_fac_location_id
and mdl.plan_id = md.plan_id
and mdl.delivery_id = md.delivery_id;
select sum(md.gross_weight)
into l_unloading_weight
from mst_trips mt,
mst_trip_stops mts,
mst_delivery_legs mdl,
mst_deliveries md
where mt.plan_id = p_plan_id
and mt.mode_of_transport = p_mode
and mt.plan_id = mts.plan_id
and mt.trip_id = mts.trip_id
and mts.plan_id = mdl.plan_id
and mdl.drop_off_stop_id = mts.stop_id
and mts.stop_location_id = p_my_fac_location_id
and mdl.plan_id = md.plan_id
and mdl.delivery_id = md.delivery_id;
select sum(md.volume)
into l_loading_cube
from mst_trips mt,
mst_trip_stops mts,
mst_delivery_legs mdl,
mst_deliveries md
where mt.plan_id = p_plan_id
and mt.mode_of_transport = p_mode
and mt.plan_id = mts.plan_id
and mt.trip_id = mts.trip_id
and mts.plan_id = mdl.plan_id
and mdl.pick_up_stop_id = mts.stop_id
and mts.stop_location_id = p_my_fac_location_id
and mdl.plan_id = md.plan_id
and mdl.delivery_id = md.delivery_id;
select sum(md.volume)
into l_unloading_cube
from mst_trips mt,
mst_trip_stops mts,
mst_delivery_legs mdl,
mst_deliveries md
where mt.plan_id = p_plan_id
and mt.mode_of_transport = p_mode
and mt.plan_id = mts.plan_id
and mt.trip_id = mts.trip_id
and mts.plan_id = mdl.plan_id
and mdl.drop_off_stop_id = mts.stop_id
and mts.stop_location_id = p_my_fac_location_id
and mdl.plan_id = md.plan_id
and mdl.delivery_id = md.delivery_id;
select sum(md.number_of_pallets)
into l_loading_pallets
from mst_trips mt,
mst_trip_stops mts,
mst_delivery_legs mdl,
mst_deliveries md
where mt.plan_id = p_plan_id
and mt.mode_of_transport = p_mode
and mt.plan_id = mts.plan_id
and mt.trip_id = mts.trip_id
and mdl.plan_id = mts.plan_id
and mdl.pick_up_stop_id = mts.stop_id
and mts.stop_location_id = p_my_fac_location_id
and mdl.plan_id = md.plan_id
and mdl.delivery_id = md.delivery_id;
select sum(md.number_of_pallets)
into l_unloading_pallets
from mst_trips mt,
mst_trip_stops mts,
mst_delivery_legs mdl,
mst_deliveries md
where mt.plan_id = p_plan_id
and mt.mode_of_transport = p_mode
and mt.plan_id = mts.plan_id
and mt.trip_id = mts.trip_id
and mdl.plan_id = mts.plan_id
and mdl.drop_off_stop_id = mts.stop_id
and mts.stop_location_id = p_my_fac_location_id
and mdl.plan_id = md.plan_id
and mdl.delivery_id = md.delivery_id;
select sum(md.number_of_pieces)
into l_loading_pieces
from mst_trips mt,
mst_trip_stops mts,
mst_delivery_legs mdl,
mst_deliveries md
where mt.plan_id = p_plan_id
and mt.mode_of_transport = p_mode
and mt.plan_id = mts.plan_id
and mt.trip_id = mts.trip_id
and mdl.plan_id = mts.plan_id
and mdl.pick_up_stop_id = mts.stop_id
and mts.stop_location_id = p_my_fac_location_id
and mdl.plan_id = md.plan_id
and mdl.delivery_id = md.delivery_id;
SELECT sum(md.number_of_pieces)
into l_unloading_pieces
from mst_trips mt,
mst_trip_stops mts,
mst_delivery_legs mdl,
mst_deliveries md
where mt.plan_id = p_plan_id
and mt.mode_of_transport = p_mode
and mt.plan_id = mts.plan_id
and mt.trip_id = mts.trip_id
and mdl.plan_id = mts.plan_id
and mdl.drop_off_stop_id = mts.stop_id
and mts.stop_location_id = p_my_fac_location_id
and mdl.plan_id = md.plan_id
and mdl.delivery_id = md.delivery_id;
SELECT t.trip_id, t.mode_of_transport
FROM mst_trips t
WHERE t.plan_id = p_plan_id
AND t.carrier_id = p_carrier_id
ORDER BY t.mode_of_transport;
SELECT ts.stop_location_id
FROM mst_trip_stops ts,
wsh_location_owners wlo
WHERE plan_id = p_plan_id
AND ts.trip_id = p_trip_id
AND ts.stop_location_id = wlo.wsh_location_id
AND owner_party_id = p_carrier_id
AND owner_type = CARRIER; -- 'carrier'
SELECT COUNT(mst.stop_id)
FROM mst_trip_stops mst
WHERE mst.plan_id = p_plan_id
AND mst.trip_id = P_TRIP;
SELECT wlo.wsh_location_id
FROM wsh_location_owners wlo
WHERE owner_party_id = p_carrier_id
AND owner_type = CARRIER;
SELECT t.trip_id, t.mode_of_transport
FROM mst_trips t,
mst_trip_stops ts
WHERE t.plan_id = p_plan_id
AND t.plan_id = ts.plan_id
AND t.trip_id = ts.trip_id
AND ts.stop_location_id = p_location_id
ORDER BY t.mode_of_transport;
SELECT COUNT(mst.stop_id)
FROM mst_trip_stops mst
WHERE mst.plan_id = p_plan_id
AND mst.trip_id = P_TRIP;
SELECT SUM(mdl.allocated_transport_cost +
DECODE(mts.stop_id,
mdl.pick_up_stop_id,
mdl.allocated_fac_loading_cost,
mdl.drop_off_stop_id,
mdl.allocated_fac_unloading_cost,0))
FROM mst_delivery_legs mdl,
mst_trip_stops mts,
mst_trips mt
WHERE mdl.plan_id = p_plan_id
AND mts.plan_id = mdl.plan_id
AND ( mts.stop_id = mdl.pick_up_stop_id
OR mts.stop_id = mdl.drop_off_stop_id)
AND mt.plan_id = mts.plan_Id
AND mt.trip_id = mts.trip_id
AND mt.carrier_id = p_carrier_id
AND EXISTS (SELECT 1
FROM wsh_location_owners wlo
WHERE wlo.owner_party_id = mt.carrier_id
AND wlo.wsh_location_id = mts.stop_location_id
AND wlo.owner_type = CARRIER);*/
SELECT SUM(mdl.allocated_transport_cost +
DECODE(mts.stop_id,
mdl.pick_up_stop_id,
mdl.allocated_fac_loading_cost,
mdl.drop_off_stop_id,
mdl.allocated_fac_unloading_cost,0) +
DECODE(mts.stop_id,
mdl.pick_up_stop_id,
mdl.allocated_fac_shp_hand_cost,
mdl.drop_off_stop_id,
mdl.allocated_fac_Rec_hand_cost,0) )
FROM mst_delivery_legs mdl,
mst_trip_stops mts,
mst_trips mt
WHERE mdl.plan_id = p_plan_id
AND mt.plan_id = mdl.plan_id
AND mt.trip_id = mdl.trip_id
AND mt.plan_id = mts.plan_Id
AND mt.trip_id = mts.trip_id
AND EXISTS (SELECT 1
FROM wsh_location_owners wlo
WHERE wlo.owner_party_id = p_carrier_id
AND wlo.wsh_location_id = mts.stop_location_id
AND wlo.owner_type = CARRIER);
SELECT SUM(md.gross_weight)
INTO l_total_weight
FROM mst_deliveries md
WHERE md.plan_id = p_plan_id
AND md.delivery_id IN
(SELECT mdl.delivery_id
FROM mst_delivery_legs mdl,
mst_trips mt,
mst_trip_stops mts
WHERE mdl.plan_id = md.plan_id
AND mts.plan_Id = mdl.plan_id
AND mts.stop_id = mdl.drop_off_stop_id
AND mts.stop_location_id = p_location_id
AND mdl.plan_id = mt.plan_id
AND mdl.trip_id = mt.trip_id );
SELECT SUM(md.gross_weight)
INTO l_total_weight
FROM mst_deliveries md
WHERE md.plan_id = p_plan_id
AND md.delivery_id IN
(SELECT mdl.delivery_id
FROM mst_delivery_legs mdl,
mst_trips mt,
mst_trip_stops mts
WHERE mdl.plan_id = md.plan_id
AND mts.plan_id = mdl.plan_id
AND mts.stop_id = mdl.pick_up_stop_id
AND mts.stop_location_id = p_location_id
AND mdl.plan_id = mt.plan_id
AND mdl.trip_id = mt.trip_id);
SELECT SUM(md.gross_weight)
INTO l_total_weight
FROM mst_deliveries md
WHERE md.plan_id = p_plan_id
AND md.delivery_id IN
(SELECT mdl.delivery_id
FROM mst_delivery_legs mdl,
mst_trips mt,
mst_trip_stops mts
WHERE mdl.plan_id = md.plan_id
AND mts.plan_Id = mdl.plan_id
AND ( mts.stop_id = mdl.pick_up_stop_id
OR mts.stop_id = mdl.drop_off_stop_id)
AND mts.stop_location_id = p_location_id
AND mdl.plan_id = mt.plan_id
AND mdl.trip_id = mt.trip_id
--AND mt.carrier_id = p_carrier_id
AND mt.mode_of_transport = p_mode_of_transport);
SELECT SUM(md.gross_weight)
INTO l_total_weight
FROM mst_deliveries md
WHERE md.plan_id = p_plan_id
AND md.delivery_id IN
(SELECT mdl.delivery_id
FROM mst_delivery_legs mdl,
mst_trips mt,
mst_trip_stops mts
WHERE mdl.plan_id = md.plan_id
AND mts.plan_Id = mdl.plan_id
AND mts.stop_id = mdl.pick_up_stop_id
AND mts.stop_location_id = p_location_id
AND mdl.plan_id = mt.plan_id
AND mdl.trip_id = mt.trip_id
AND mt.mode_of_transport = p_mode_of_transport);
SELECT SUM(md.gross_weight)
INTO l_total_weight
FROM mst_deliveries md
WHERE md.plan_id = p_plan_id
AND md.delivery_id IN
(SELECT mdl.delivery_id
FROM mst_delivery_legs mdl,
mst_trips mt,
mst_trip_stops mts
WHERE mdl.plan_id = md.plan_id
AND mts.plan_Id = mdl.plan_id
AND mts.stop_id = mdl.drop_off_stop_id
AND mts.stop_location_id = p_location_id
AND mdl.plan_id = mt.plan_id
AND mdl.trip_id = mt.trip_id
AND mt.mode_of_transport = p_mode_of_transport);
SELECT SUM(md.gross_weight)
INTO l_total_weight
FROM mst_deliveries md
WHERE md.plan_id = p_plan_id
AND md.delivery_id IN
(SELECT mdl.delivery_id
FROM mst_delivery_legs mdl,
mst_trips mt,
mst_trip_stops mts
WHERE mdl.plan_id = md.plan_id
AND mts.plan_Id = mdl.plan_id
AND ( mts.stop_id = mdl.pick_up_stop_id
OR mts.stop_id = mdl.drop_off_stop_id)
AND mts.stop_location_id = p_location_id
AND mdl.plan_id = mt.plan_id
AND mdl.trip_id = mt.trip_id
AND mt.carrier_id = p_carrier_id);
SELECT SUM(md.gross_weight)
INTO l_total_weight
FROM mst_deliveries md
WHERE md.plan_id = p_plan_id
AND md.delivery_id IN
(SELECT mdl.delivery_id
FROM mst_delivery_legs mdl,
mst_trips mt,
mst_trip_stops mts
WHERE mdl.plan_id = md.plan_id
AND mts.plan_Id = mdl.plan_id
AND mts.stop_id = mdl.pick_up_stop_id
AND mts.stop_location_id = p_location_id
AND mdl.plan_id = mt.plan_id
AND mdl.trip_id = mt.trip_id);
SELECT SUM(md.gross_weight)
INTO l_total_weight
FROM mst_deliveries md
WHERE md.plan_id = p_plan_id
AND md.delivery_id IN
(SELECT mdl.delivery_id
FROM mst_delivery_legs mdl,
mst_trips mt,
mst_trip_stops mts
WHERE mdl.plan_id = md.plan_id
AND mts.plan_Id = mdl.plan_id
AND mts.stop_id = mdl.drop_off_stop_id
AND mts.stop_location_id = p_location_id
AND mdl.plan_id = mt.plan_id
AND mdl.trip_id = mt.trip_id);
SELECT SUM(md.volume)
INTO l_total_volume
FROM mst_deliveries md
WHERE md.plan_id = p_plan_id
AND md.delivery_id IN
(SELECT mdl.delivery_id
FROM mst_delivery_legs mdl,
mst_trips mt,
mst_trip_stops mts
WHERE mdl.plan_id = md.plan_id
AND mts.plan_Id = mdl.plan_id
AND mts.stop_id = mdl.drop_off_stop_id
AND mts.stop_location_id = p_location_id
AND mdl.plan_id = mt.plan_id
AND mdl.trip_id = mt.trip_id);
SELECT SUM(md.volume)
INTO l_total_volume
FROM mst_deliveries md
WHERE md.plan_id = p_plan_id
AND md.delivery_id IN
(SELECT mdl.delivery_id
FROM mst_delivery_legs mdl,
mst_trips mt,
mst_trip_stops mts
WHERE mdl.plan_id = md.plan_id
AND mts.plan_id = mdl.plan_id
AND mts.stop_id = mdl.pick_up_stop_id
AND mts.stop_location_id = p_location_id
AND mdl.plan_id = mt.plan_id
AND mdl.trip_id = mt.trip_id);
SELECT SUM(md.volume)
INTO l_total_volume
FROM mst_deliveries md
WHERE md.plan_id = p_plan_id
AND md.delivery_id IN
(SELECT mdl.delivery_id
FROM mst_delivery_legs mdl,
mst_trips mt,
mst_trip_stops mts
WHERE mdl.plan_id = md.plan_id
AND mts.plan_Id = mdl.plan_id
AND (mts.stop_id = mdl.pick_up_stop_id
OR mts.stop_id = mdl.drop_off_stop_id)
AND mts.stop_location_id = p_location_id
AND mdl.plan_id = mt.plan_id
AND mdl.trip_id = mt.trip_id
--AND mt.carrier_id = p_carrier_id
AND mt.mode_of_transport = p_mode_of_transport);
SELECT SUM(md.volume)
INTO l_total_volume
FROM mst_deliveries md
WHERE md.plan_id = p_plan_id
AND md.delivery_id IN
(SELECT mdl.delivery_id
FROM mst_delivery_legs mdl,
mst_trips mt,
mst_trip_stops mts
WHERE mdl.plan_id = md.plan_id
AND mts.plan_Id = mdl.plan_id
AND mts.stop_id = mdl.pick_up_stop_id
AND mts.stop_location_id = p_location_id
AND mdl.plan_id = mt.plan_id
AND mdl.trip_id = mt.trip_id
AND mt.mode_of_transport = p_mode_of_transport);
SELECT SUM(md.volume)
INTO l_total_volume
FROM mst_deliveries md
WHERE md.plan_id = p_plan_id
AND md.delivery_id IN
(SELECT mdl.delivery_id
FROM mst_delivery_legs mdl,
mst_trips mt,
mst_trip_stops mts
WHERE mdl.plan_id = md.plan_id
AND mts.plan_Id = mdl.plan_id
AND mts.stop_id = mdl.drop_off_stop_id
AND mts.stop_location_id = p_location_id
AND mdl.plan_id = mt.plan_id
AND mdl.trip_id = mt.trip_id
AND mt.mode_of_transport = p_mode_of_transport);
SELECT SUM(md.volume)
INTO l_total_volume
FROM mst_deliveries md
WHERE md.plan_id = p_plan_id
AND md.delivery_id IN
(SELECT mdl.delivery_id
FROM mst_delivery_legs mdl,
mst_trips mt,
mst_trip_stops mts
WHERE mdl.plan_id = md.plan_id
AND mts.plan_Id = mdl.plan_id
AND (mts.stop_id = mdl.pick_up_stop_id
OR mts.stop_id = mdl.drop_off_stop_id)
AND mts.stop_location_id = p_location_id
AND mdl.plan_id = mt.plan_id
AND mdl.trip_id = mt.trip_id
AND mt.carrier_id = p_carrier_id);
SELECT SUM(md.volume)
INTO l_total_volume
FROM mst_deliveries md
WHERE md.plan_id = p_plan_id
AND md.delivery_id IN
(SELECT mdl.delivery_id
FROM mst_delivery_legs mdl,
mst_trips mt,
mst_trip_stops mts
WHERE mdl.plan_id = md.plan_id
AND mts.plan_Id = mdl.plan_id
AND mts.stop_id = mdl.pick_up_stop_id
AND mts.stop_location_id = p_location_id
AND mdl.plan_id = mt.plan_id
AND mdl.trip_id = mt.trip_id);
SELECT SUM(md.volume)
INTO l_total_volume
FROM mst_deliveries md
WHERE md.plan_id = p_plan_id
AND md.delivery_id IN
(SELECT mdl.delivery_id
FROM mst_delivery_legs mdl,
mst_trips mt,
mst_trip_stops mts
WHERE mdl.plan_id = md.plan_id
AND mts.plan_Id = mdl.plan_id
AND mts.stop_id = mdl.drop_off_stop_id
AND mts.stop_location_id = p_location_id
AND mdl.plan_id = mt.plan_id
AND mdl.trip_id = mt.trip_id );
SELECT SUM(md.number_of_pallets)
INTO l_total_Pallets
FROM mst_deliveries md
WHERE md.plan_id = p_plan_id
AND md.delivery_id IN
(SELECT mdl.delivery_id
FROM mst_delivery_legs mdl,
mst_trips mt,
mst_trip_stops mts
WHERE mdl.plan_id = md.plan_id
AND mts.plan_Id = mdl.plan_id
AND mts.stop_id = mdl.drop_off_stop_id
AND mts.stop_location_id = p_location_id
AND mdl.plan_id = mt.plan_id
AND mdl.trip_id = mt.trip_id );
SELECT SUM(md.number_of_pallets)
INTO l_total_Pallets
FROM mst_deliveries md
WHERE md.plan_id = p_plan_id
AND md.delivery_id IN
(SELECT mdl.delivery_id
FROM mst_delivery_legs mdl,
mst_trips mt,
mst_trip_stops mts
WHERE mdl.plan_id = md.plan_id
AND mts.plan_id = mdl.plan_id
AND mts.stop_id = mdl.pick_up_stop_id
AND mts.stop_location_id = p_location_id
AND mdl.plan_id = mt.plan_id
AND mdl.trip_id = mt.trip_id);
SELECT SUM(md.number_of_pallets)
INTO l_total_Pallets
FROM mst_deliveries md
WHERE md.plan_id = p_plan_id
AND md.delivery_id IN
(SELECT mdl.delivery_id
FROM mst_delivery_legs mdl,
mst_trips mt,
mst_trip_stops mts
WHERE mdl.plan_id = md.plan_id
AND mts.plan_Id = mdl.plan_id
AND ( mts.stop_id = mdl.pick_up_stop_id
OR mts.stop_id = mdl.drop_off_stop_id)
AND mts.stop_location_id = p_location_id
AND mdl.plan_id = mt.plan_id
AND mdl.trip_id = mt.trip_id
AND mt.carrier_id = p_carrier_id);
SELECT SUM(md.number_of_pallets)
INTO l_total_Pallets
FROM mst_deliveries md
WHERE md.plan_id = p_plan_id
AND md.delivery_id IN
(SELECT mdl.delivery_id
FROM mst_delivery_legs mdl,
mst_trips mt,
mst_trip_stops mts
WHERE mdl.plan_id = md.plan_id
AND mts.plan_Id = mdl.plan_id
AND mts.stop_id = mdl.pick_up_stop_id
AND mts.stop_location_id = p_location_id
AND mdl.plan_id = mt.plan_id
AND mdl.trip_id = mt.trip_id
AND mt.carrier_id = p_carrier_id);
SELECT SUM(md.number_of_pallets)
INTO l_total_Pallets
FROM mst_deliveries md
WHERE md.plan_id = p_plan_id
AND md.delivery_id IN
(SELECT mdl.delivery_id
FROM mst_delivery_legs mdl,
mst_trips mt,
mst_trip_stops mts
WHERE mdl.plan_id = md.plan_id
AND mts.plan_Id = mdl.plan_id
AND mts.stop_id = mdl.drop_off_stop_id
AND mts.stop_location_id = p_location_id
AND mdl.plan_id = mt.plan_id
AND mdl.trip_id = mt.trip_id
AND mt.carrier_id = p_carrier_id);
SELECT SUM(md.number_of_pallets)
INTO l_total_Pallets
FROM mst_deliveries md
WHERE md.plan_id = p_plan_id
AND md.delivery_id IN
(SELECT mdl.delivery_id
FROM mst_delivery_legs mdl,
mst_trips mt,
mst_trip_stops mts
WHERE mdl.plan_id = md.plan_id
AND mts.plan_Id = mdl.plan_id
AND ( mts.stop_id = mdl.pick_up_stop_id
OR mts.stop_id = mdl.drop_off_stop_id)
AND mts.stop_location_id = p_location_id
AND mdl.plan_id = mt.plan_id
AND mdl.trip_id = mt.trip_id
AND mt.carrier_id = p_carrier_id);
SELECT SUM(md.number_of_pallets)
INTO l_total_Pallets
FROM mst_deliveries md
WHERE md.plan_id = p_plan_id
AND md.delivery_id IN
(SELECT mdl.delivery_id
FROM mst_delivery_legs mdl,
mst_trips mt,
mst_trip_stops mts
WHERE mdl.plan_id = md.plan_id
AND mts.plan_Id = mdl.plan_id
AND mts.stop_id = mdl.pick_up_stop_id
AND mts.stop_location_id = p_location_id
AND mdl.plan_id = mt.plan_id
AND mdl.trip_id = mt.trip_id);
SELECT SUM(md.number_of_pallets)
INTO l_total_Pallets
FROM mst_deliveries md
WHERE md.plan_id = p_plan_id
AND md.delivery_id IN
(SELECT mdl.delivery_id
FROM mst_delivery_legs mdl,
mst_trips mt,
mst_trip_stops mts
WHERE mdl.plan_id = md.plan_id
AND mts.plan_Id = mdl.plan_id
AND mts.stop_id = mdl.drop_off_stop_id
AND mts.stop_location_id = p_location_id
AND mdl.plan_id = mt.plan_id
AND mdl.trip_id = mt.trip_id);
SELECT SUM(md.number_of_pieces)
INTO l_total_Pieces
FROM mst_deliveries md
WHERE md.plan_id = p_plan_id
AND md.delivery_id IN
(SELECT mdl.delivery_id
FROM mst_delivery_legs mdl,
mst_trips mt,
mst_trip_stops mts
WHERE mdl.plan_id = md.plan_id
AND mts.plan_Id = mdl.plan_id
AND mts.stop_id = mdl.drop_off_stop_id
AND mts.stop_location_id = p_location_id
AND mdl.plan_id = mt.plan_id
AND mdl.trip_id = mt.trip_id);
SELECT SUM(md.number_of_pieces)
INTO l_total_Pieces
FROM mst_deliveries md
WHERE md.plan_id = p_plan_id
AND md.delivery_id IN
(SELECT mdl.delivery_id
FROM mst_delivery_legs mdl,
mst_trips mt,
mst_trip_stops mts
WHERE mdl.plan_id = md.plan_id
AND mts.plan_id = mdl.plan_id
AND mts.stop_id = mdl.pick_up_stop_id
AND mts.stop_location_id = p_location_id
AND mdl.plan_id = mt.plan_id
AND mdl.trip_id = mt.trip_id);
SELECT SUM(md.number_of_pieces)
INTO l_total_Pieces
FROM mst_deliveries md
WHERE md.plan_id = p_plan_id
AND md.delivery_id IN
(SELECT mdl.delivery_id
FROM mst_delivery_legs mdl,
mst_trips mt,
mst_trip_stops mts
WHERE mdl.plan_id = md.plan_id
AND mts.plan_Id = mdl.plan_id
AND ( mts.stop_id = mdl.pick_up_stop_id
OR mts.stop_id = mdl.drop_off_stop_id)
AND mts.stop_location_id = p_location_id
AND mdl.plan_id = mt.plan_id
AND mdl.trip_id = mt.trip_id
--AND mt.carrier_id = p_carrier_id
AND mt.mode_of_transport = p_mode_of_transport);
SELECT SUM(md.number_of_pieces)
INTO l_total_Pieces
FROM mst_deliveries md
WHERE md.plan_id = p_plan_id
AND md.delivery_id IN
(SELECT mdl.delivery_id
FROM mst_delivery_legs mdl,
mst_trips mt,
mst_trip_stops mts
WHERE mdl.plan_id = md.plan_id
AND mts.plan_Id = mdl.plan_id
AND mts.stop_id = mdl.pick_up_stop_id
AND mts.stop_location_id = p_location_id
AND mdl.plan_id = mt.plan_id
AND mdl.trip_id = mt.trip_id
AND mt.mode_of_transport = p_mode_of_transport);
SELECT SUM(md.number_of_pieces)
INTO l_total_Pieces
FROM mst_deliveries md
WHERE md.plan_id = p_plan_id
AND md.delivery_id IN
(SELECT mdl.delivery_id
FROM mst_delivery_legs mdl,
mst_trips mt,
mst_trip_stops mts
WHERE mdl.plan_id = md.plan_id
AND mts.plan_Id = mdl.plan_id
AND mts.stop_id = mdl.drop_off_stop_id
AND mts.stop_location_id = p_location_id
AND mdl.plan_id = mt.plan_id
AND mdl.trip_id = mt.trip_id
AND mt.mode_of_transport = p_mode_of_transport);
SELECT SUM(md.number_of_pieces)
INTO l_total_Pieces
FROM mst_deliveries md
WHERE md.plan_id = p_plan_id
AND md.delivery_id IN
(SELECT mdl.delivery_id
FROM mst_delivery_legs mdl,
mst_trips mt,
mst_trip_stops mts
WHERE mdl.plan_id = md.plan_id
AND mts.plan_Id = mdl.plan_id
AND ( mts.stop_id = mdl.pick_up_stop_id
OR mts.stop_id = mdl.drop_off_stop_id)
AND mts.stop_location_id = p_location_id
AND mdl.plan_id = mt.plan_id
AND mdl.trip_id = mt.trip_id
AND mt.carrier_id = p_carrier_id);
SELECT SUM(md.number_of_pieces)
INTO l_total_Pieces
FROM mst_deliveries md
WHERE md.plan_id = p_plan_id
AND md.delivery_id IN
(SELECT mdl.delivery_id
FROM mst_delivery_legs mdl,
mst_trips mt,
mst_trip_stops mts
WHERE mdl.plan_id = md.plan_id
AND mts.plan_Id = mdl.plan_id
AND mts.stop_id = mdl.pick_up_stop_id
AND mts.stop_location_id = p_location_id
AND mdl.plan_id = mt.plan_id
AND mdl.trip_id = mt.trip_id);
SELECT SUM(md.number_of_pieces)
INTO l_total_Pieces
FROM mst_deliveries md
WHERE md.plan_id = p_plan_id
AND md.delivery_id IN
(SELECT mdl.delivery_id
FROM mst_delivery_legs mdl,
mst_trips mt,
mst_trip_stops mts
WHERE mdl.plan_id = md.plan_id
AND mts.plan_Id = mdl.plan_id
AND mts.stop_id = mdl.drop_off_stop_id
AND mts.stop_location_id = p_location_id
AND mdl.plan_id = mt.plan_id
AND mdl.trip_id = mt.trip_id);
SELECT COUNT(DISTINCT mdd.SOURCE_HEADER_NUMBER)
INTO l_total_orders
FROM MST_DELIVERY_DETAILS MDD,
MST_DELIVERIES MD,
MST_DELIVERY_ASSIGNMENTS MDA
WHERE MD.PLAN_ID = MDA.PLAN_ID
AND MD.DELIVERY_ID = MDA.DELIVERY_ID
AND MD.DELIVERY_ID IN
(SELECT MDL.DELIVERY_ID
FROM MST_TRIPS T,
MST_TRIP_STOPS TS,
MST_DELIVERY_LEGS MDL
WHERE MDL.PLAN_ID = MD.PLAN_ID
AND TS.PLAN_ID = MDL.PLAN_ID
AND ( TS.STOP_ID = MDL.PICK_UP_STOP_ID
OR TS.STOP_ID = MDL.DROP_OFF_STOP_ID )
AND TS.STOP_LOCATION_ID = P_FAC_LOCATION_ID
AND TS.PLAN_ID = T.PLAN_ID
AND TS.TRIP_ID = T.TRIP_ID
--AND T.CARRIER_ID = P_CARRIER_ID
AND T.MODE_OF_TRANSPORT = P_MODE)
AND MDA.PLAN_ID = MDD.PLAN_ID
AND MDA.DELIVERY_DETAIL_ID = MDD.DELIVERY_DETAIL_ID
AND MD.PLAN_ID = P_PLAN_ID;
SELECT COUNT(DISTINCT mdd.SOURCE_HEADER_NUMBER)
INTO l_total_orders
FROM MST_DELIVERY_DETAILS MDD,
MST_DELIVERIES MD,
MST_DELIVERY_ASSIGNMENTS MDA
WHERE MD.PLAN_ID = MDA.PLAN_ID
AND MD.DELIVERY_ID = MDA.DELIVERY_ID
AND MD.DELIVERY_ID IN
(SELECT MDL.DELIVERY_ID
FROM MST_TRIPS T,
MST_TRIP_STOPS TS,
MST_DELIVERY_LEGS MDL
WHERE MDL.PLAN_ID = MD.PLAN_ID
AND TS.PLAN_ID = MDL.PLAN_ID
AND TS.STOP_ID = MDL.PICK_UP_STOP_ID
AND TS.STOP_LOCATION_ID = P_FAC_LOCATION_ID
AND TS.PLAN_ID = T.PLAN_ID
AND TS.TRIP_ID = T.TRIP_ID
AND T.MODE_OF_TRANSPORT = P_MODE)
AND MDA.PLAN_ID = MDD.PLAN_ID
AND MDA.DELIVERY_DETAIL_ID = MDD.DELIVERY_DETAIL_ID
AND MD.PLAN_ID = P_PLAN_ID;
SELECT COUNT(DISTINCT mdd.SOURCE_HEADER_NUMBER)
INTO l_total_orders
FROM MST_DELIVERY_DETAILS MDD,
MST_DELIVERIES MD,
MST_DELIVERY_ASSIGNMENTS MDA
WHERE MD.PLAN_ID = MDA.PLAN_ID
AND MD.DELIVERY_ID = MDA.DELIVERY_ID
AND MD.DELIVERY_ID IN
(SELECT MDL.DELIVERY_ID
FROM MST_TRIPS T,
MST_TRIP_STOPS TS,
MST_DELIVERY_LEGS MDL
WHERE MDL.PLAN_ID = MD.PLAN_ID
AND TS.PLAN_ID = MDL.PLAN_ID
AND TS.STOP_ID = MDL.DROP_OFF_STOP_ID
AND TS.STOP_LOCATION_ID = P_FAC_LOCATION_ID
AND TS.PLAN_ID = T.PLAN_ID
AND TS.TRIP_ID = T.TRIP_ID
AND T.MODE_OF_TRANSPORT = P_MODE)
AND MDA.PLAN_ID = MDD.PLAN_ID
AND MDA.DELIVERY_DETAIL_ID = MDD.DELIVERY_DETAIL_ID
AND MD.PLAN_ID = P_PLAN_ID;
SELECT COUNT(DISTINCT mdd.SOURCE_HEADER_NUMBER)
INTO l_total_orders
FROM MST_DELIVERY_DETAILS MDD,
MST_DELIVERIES MD,
MST_DELIVERY_ASSIGNMENTS MDA
WHERE MD.PLAN_ID = MDA.PLAN_ID
AND MD.DELIVERY_ID = MDA.DELIVERY_ID
AND MD.DELIVERY_ID IN
(SELECT MDL.DELIVERY_ID
FROM MST_TRIPS T,
MST_TRIP_STOPS TS,
MST_DELIVERY_LEGS MDL
WHERE MDL.PLAN_ID = MD.PLAN_ID
AND TS.PLAN_ID = MDL.PLAN_ID
AND ( TS.STOP_ID = MDL.PICK_UP_STOP_ID
OR TS.STOP_ID = MDL.DROP_OFF_STOP_ID )
AND TS.STOP_LOCATION_ID = P_FAC_LOCATION_ID
AND TS.PLAN_ID = T.PLAN_ID
AND TS.TRIP_ID = T.TRIP_ID
AND T.CARRIER_ID = P_CARRIER_ID)
AND MDA.PLAN_ID = MDD.PLAN_ID
AND MDA.DELIVERY_DETAIL_ID = MDD.DELIVERY_DETAIL_ID
AND MD.PLAN_ID = P_PLAN_ID;
SELECT COUNT(DISTINCT mdd.SOURCE_HEADER_NUMBER)
INTO l_total_orders
FROM mst_delivery_details mdd,
mst_deliveries md,
mst_delivery_assignments mda
WHERE md.plan_id = p_plan_id
AND md.plan_id = mda.plan_id
AND md.delivery_id = mda.delivery_id
AND mda.plan_id = mdd.plan_id
AND mda.delivery_detail_id = mdd.delivery_detail_id
AND md.delivery_id IN
( SELECT mdl.delivery_id
FROM mst_delivery_legs mdl,
mst_trip_stops mts
WHERE mdl.plan_id = md.plan_id
AND mdl.plan_id = mts.plan_id
AND mdl.pick_up_stop_id = mts.stop_id
AND mts.stop_location_id = p_fac_location_id);
SELECT COUNT(DISTINCT mdd.SOURCE_HEADER_NUMBER)
INTO l_total_orders
FROM mst_delivery_details mdd,
mst_deliveries md,
mst_delivery_assignments mda
WHERE md.plan_id = p_plan_id
AND md.plan_id = mda.plan_id
AND md.delivery_id = mda.delivery_id
AND mda.plan_id = mdd.plan_id
AND mda.delivery_detail_id = mdd.delivery_detail_id
AND md.delivery_id IN
( SELECT mdl.delivery_id
FROM mst_delivery_legs mdl,
mst_trip_stops mts
WHERE mdl.plan_id = md.plan_id
AND mdl.plan_id = mts.plan_id
AND mdl.drop_off_stop_id = mts.stop_id
AND mts.stop_location_id = p_fac_location_id);
SELECT COUNT(DISTINCT mdd.SOURCE_HEADER_NUMBER)
INTO l_total_orders
FROM MST_DELIVERY_DETAILS MDD,
MST_DELIVERIES MD,
MST_DELIVERY_ASSIGNMENTS MDA
WHERE MD.PLAN_ID = MDA.PLAN_ID
AND MD.DELIVERY_ID = MDA.DELIVERY_ID
AND MD.DELIVERY_ID IN
(SELECT MDL.DELIVERY_ID
--FROM MST_TRIPS T,
FROM MST_TRIP_STOPS TS,
MST_DELIVERY_LEGS MDL
WHERE MDL.PLAN_ID = MD.PLAN_ID
AND TS.PLAN_ID = MDL.PLAN_ID
AND TS.STOP_ID = MDL.PICK_UP_STOP_ID
AND TS.STOP_LOCATION_ID = P_FAC_LOCATION_ID)
--AND TS.PLAN_ID = T.PLAN_ID
--AND TS.TRIP_ID = T.TRIP_ID
--AND T.CARRIER_ID = P_CARRIER_ID)
AND MDA.PLAN_ID = MDD.PLAN_ID
AND MDA.DELIVERY_DETAIL_ID = MDD.DELIVERY_DETAIL_ID
AND MD.PLAN_ID = P_PLAN_ID;
SELECT COUNT(DISTINCT mdd.SOURCE_HEADER_NUMBER)
INTO l_total_orders
FROM MST_DELIVERY_DETAILS MDD,
MST_DELIVERIES MD,
MST_DELIVERY_ASSIGNMENTS MDA
WHERE MD.PLAN_ID = MDA.PLAN_ID
AND MD.DELIVERY_ID = MDA.DELIVERY_ID
AND MD.DELIVERY_ID IN
(SELECT MDL.DELIVERY_ID
--FROM MST_TRIPS T,
FROM MST_TRIP_STOPS TS,
MST_DELIVERY_LEGS MDL
WHERE MDL.PLAN_ID = MD.PLAN_ID
AND TS.PLAN_ID = MDL.PLAN_ID
AND TS.STOP_ID = MDL.DROP_OFF_STOP_ID
AND TS.STOP_LOCATION_ID = P_FAC_LOCATION_ID)
--AND TS.PLAN_ID = T.PLAN_ID
--AND TS.TRIP_ID = T.TRIP_ID
--AND T.CARRIER_ID = P_CARRIER_ID)
AND MDA.PLAN_ID = MDD.PLAN_ID
AND MDA.DELIVERY_DETAIL_ID = MDD.DELIVERY_DETAIL_ID
AND MD.PLAN_ID = P_PLAN_ID;
SELECT COUNT(mt.trip_id)
INTO l_total_trips
FROM mst_trips mt
WHERE mt.plan_id = p_plan_id
AND mt.trip_id IN
(SELECT mdl.trip_id
FROM mst_delivery_legs mdl,
mst_trip_stops mts
WHERE mdl.plan_id = mt.plan_id
AND mts.plan_id = mdl.plan_id
AND mts.stop_id = mdl.drop_off_stop_id
AND mts.stop_location_id = P_location_id)
--AND mt.carrier_id = p_carrier_id
AND mt.mode_of_transport = p_mode_of_transport;
SELECT COUNT(mt.trip_id)
INTO l_total_trips
FROM mst_trips mt
WHERE mt.plan_id = p_plan_id
AND mt.trip_id IN
(SELECT mdl.trip_id
FROM mst_deliveries md,
mst_delivery_legs mdl,
mst_trip_stops mts
WHERE mdl.plan_id = mt.plan_id
AND mts.plan_id = mdl.plan_id
AND mts.stop_id = mdl.pick_up_stop_id
AND mts.stop_location_id = p_location_Id)
--AND mt.carrier_id = p_carrier_id
AND mt.mode_of_transport = p_mode_of_transport;
SELECT COUNT(mt.trip_id)
INTO l_total_trips
FROM mst_trips mt
WHERE mt.plan_id = p_plan_id
AND mt.trip_id IN
(SELECT mdl.trip_id
FROM mst_delivery_legs mdl,
mst_trip_stops mts
WHERE mdl.plan_id = mt.plan_id
AND mts.plan_id = mdl.plan_id
AND ( mts.stop_id = mdl.pick_up_stop_id
OR mts.stop_id = mdl.drop_off_stop_id)
AND mts.stop_location_id = P_location_id)
--AND mt.carrier_id = p_carrier_id
AND mt.mode_of_transport = p_mode_of_transport;
SELECT COUNT(mt.trip_id)
INTO l_total_trips
FROM mst_trips mt
WHERE mt.plan_id = p_plan_id
AND mt.trip_id IN
(SELECT mdl.trip_id
FROM mst_delivery_legs mdl,
mst_trip_stops mts
WHERE mdl.plan_id = mt.plan_id
AND mts.plan_id = mdl.plan_id
AND ( mts.stop_id = mdl.pick_up_stop_id
OR mts.stop_id = mdl.drop_off_stop_id)
AND mts.stop_location_id = P_location_id)
AND mt.carrier_id = p_carrier_id;
SELECT COUNT(mt.trip_id)
INTO l_total_trips
FROM mst_trips mt
WHERE mt.plan_id = p_plan_id
AND mt.trip_id IN
(SELECT mdl.trip_id
FROM mst_delivery_legs mdl,
mst_trip_stops mts
WHERE mdl.plan_id = mt.plan_id
AND mts.plan_id = mdl.plan_id
AND mts.stop_id = mdl.pick_up_stop_id
AND mts.stop_location_id = P_location_id);
SELECT COUNT(mt.trip_id)
INTO l_total_trips
FROM mst_trips mt
WHERE mt.plan_id = p_plan_id
AND mt.trip_id IN
(SELECT mdl.trip_id
FROM mst_delivery_legs mdl,
mst_trip_stops mts
WHERE mdl.plan_id = mt.plan_id
AND mts.plan_id = mdl.plan_id
AND mts.stop_id = mdl.drop_off_stop_id
AND mts.stop_location_id = P_location_id);
SELECT COUNT(mt.trip_id)
INTO l_total_trips
FROM mst_trips mt
WHERE mt.plan_id = p_plan_id
AND mt.trip_id IN
(SELECT mdl.trip_id
FROM mst_delivery_legs mdl,
mst_trip_stops mts
WHERE mdl.plan_id = mt.plan_id
AND mts.plan_id = mdl.plan_id
AND mts.stop_id = mdl.pick_up_stop_Id
AND mts.stop_location_id = p_location_id)
AND mt.mode_of_transport = TRUCK -- p_mode_of_transport
--AND mt.carrier_id = p_carrier_id
AND EXISTS (SELECT mts1.trip_id
FROM mst_trip_stops mts1
WHERE mts1.plan_id = mt.plan_id
AND mts1.trip_Id = mt.trip_ID
HAVING COUNT(mts1.stop_id) =2
GROUP BY mts1.trip_id);
SELECT COUNT(mt.trip_id)
INTO l_total_trips
FROM mst_trips mt
WHERE mt.plan_id = p_plan_id
AND mt.trip_id IN
(SELECT mdl.trip_id
FROM mst_delivery_legs mdl,
mst_trip_stops mts
WHERE mdl.plan_id = mt.plan_id
AND mts.plan_id = mdl.plan_id
AND mts.stop_id = mdl.drop_off_stop_id
AND mts.stop_location_id = p_location_id)
AND mt.mode_of_transport = TRUCK -- p_mode_of_transport
--AND mt.carrier_id = p_carrier_id
AND EXISTS (SELECT mts1.trip_id
FROM mst_trip_stops mts1
WHERE mts1.plan_id = mt.plan_id
AND mts1.trip_Id = mt.trip_ID
HAVING COUNT(mts1.stop_id) =2
GROUP BY mts1.trip_id);
SELECT COUNT(mt.trip_id)
INTO l_total_trips
FROM mst_trips mt
WHERE mt.plan_id = p_plan_id
AND mt.trip_id IN
(SELECT mdl.trip_id
FROM mst_delivery_legs mdl,
mst_trip_stops mts
WHERE mdl.plan_id = mt.plan_id
AND mts.plan_id = mdl.plan_id
AND mts.stop_id = mdl.pick_up_stop_Id
AND mts.stop_location_id = p_location_id)
AND mt.mode_of_transport = TRUCK -- p_mode_of_transport
--AND mt.carrier_id = p_carrier_id
AND EXISTS (SELECT mts1.trip_id
FROM mst_trip_stops mts1
WHERE mts1.plan_id = mt.plan_id
AND mts1.trip_Id = mt.trip_ID
HAVING COUNT(mts1.stop_id) > 2
GROUP BY mts1.trip_id);
SELECT COUNT(mt.trip_id)
INTO l_total_trips
FROM mst_trips mt
WHERE mt.plan_id = p_plan_id
AND mt.trip_id IN
(SELECT mdl.trip_id
FROM mst_delivery_legs mdl,
mst_trip_stops mts
WHERE mdl.plan_id = mt.plan_id
AND mts.plan_id = mdl.plan_id
AND mts.stop_id = mdl.drop_off_stop_Id
AND mts.stop_location_id = p_location_id)
AND mt.mode_of_transport = TRUCK -- p_mode_of_transport
--AND mt.carrier_id = p_carrier_id
AND EXISTS (SELECT mts1.trip_id
FROM mst_trip_stops mts1
WHERE mts1.plan_id = mt.plan_id
AND mts1.trip_Id = mt.trip_ID
HAVING COUNT(mts1.stop_id) > 2
GROUP BY mts1.trip_id);
SELECT NVL(SUM(NVL(mdl.allocated_transport_cost,0) +
DECODE(mts.stop_id,
mdl.pick_up_stop_id,
NVL(mdl.allocated_fac_loading_cost,0),
mdl.drop_off_stop_id,
NVL(mdl.allocated_fac_unloading_cost,0),0)+
DECODE(mts.stop_id,
mdl.pick_up_stop_id,
NVL(mdl.allocated_fac_shp_hand_cost,0),
mdl.drop_off_stop_id,
NVL(mdl.allocated_fac_Rec_hand_cost,0),0) ), 0)
FROM mst_delivery_legs mdl,
mst_trip_stops mts,
mst_trips mt
WHERE mdl.plan_id = p_plan_id
AND mt.plan_id = mdl.plan_id
AND mt.trip_id = mdl.trip_id
AND mt.plan_id = mts.plan_Id
AND mt.trip_id = mts.trip_id
AND mt.mode_of_transport = p_mode_of_transport
AND mts.stop_location_id = p_location_id;
SELECT NVL(SUM(NVL(mdl.allocated_transport_cost,0) +
DECODE(mts.stop_id,
mdl.pick_up_stop_id,
NVL(mdl.allocated_fac_loading_cost,0),
mdl.drop_off_stop_id,
NVL(mdl.allocated_fac_unloading_cost,0),0) +
DECODE(mts.stop_id,
mdl.pick_up_stop_id,
NVL(mdl.allocated_fac_shp_hand_cost,0),
mdl.drop_off_stop_id,
NVL(mdl.allocated_fac_Rec_hand_cost,0),0) ), 0)
FROM mst_delivery_legs mdl,
mst_trip_stops mts,
mst_trips mt
WHERE mdl.plan_id = p_plan_id
AND mt.plan_id = mdl.plan_id
AND mt.trip_id = mdl.trip_id
AND mt.plan_id = mts.plan_Id
AND mt.trip_id = mts.trip_id
AND mts.stop_location_id = p_location_id;
select location_source_code
into l_location_source_code
from wsh_locations loc
where loc.wsh_location_id = p_location_id;
select hzp.party_name
into l_owner_name
from hz_cust_accounts hzc,
hz_parties hzp
where hzc.cust_account_id = p_customer_id
and hzc.party_id = hzp.party_id;
select party_name
from hz_locations hzl,
hz_party_sites hzs,
hz_cust_acct_sites_all hzcas,
hz_cust_accounts hzca,
hz_parties hzp,
mst_deliveries md
where md.plan_id = p_plan_id
AND md.delivery_id = p_delivery_id
and hzl.location_id = p_location_id
and hzl.location_id = hzs.location_id
and hzs.party_site_id = hzcas.party_site_id
and hzcas.cust_account_id = hzca.cust_account_id
and hzcas.cust_account_id = md.customer_id
and hzca.party_id = hzp.party_id;
select car.freight_code
from hz_locations hzl,
hz_party_sites hzs,
hz_parties hzp,
wsh_carriers car
where hzl.location_id = p_location_id
and hzl.location_id = hzs.location_id
and hzs.party_id = hzp.party_id
and hzp.party_id = car.carrier_id;
select location_source_code
into l_location_source_code
from wsh_locations loc
where loc.wsh_location_id = p_location_id;
SELECT hzp.party_name
FROM hz_parties hzp
WHERE hzp.party_id IN (SELECT hzca.party_id
FROM hz_locations hzl , hz_party_sites hzs,
hz_cust_acct_sites_all hzcas, hz_cust_accounts hzca
WHERE hzl.location_id = p_location_id
AND hzl.location_id = hzs.location_id
AND hzs.party_site_id = hzcas.party_site_id
AND hzcas.cust_account_id = hzca.cust_account_id);
select distinct party_name
from hz_locations hzl,
hz_party_sites hzs,
hz_cust_acct_sites_all hzcas,
hz_cust_accounts hzca,
hz_parties hzp
where hzl.location_id = p_location_id
and hzl.location_id = hzs.location_id
and hzs.party_site_id = hzcas.party_site_id
and hzcas.cust_account_id = hzca.cust_account_id
and hzca.party_id = hzp.party_id;*/
select car.freight_code
from hz_locations hzl,
hz_party_sites hzs,
hz_parties hzp,
wsh_carriers car
where hzl.location_id = p_location_id
and hzl.location_id = hzs.location_id
and hzs.party_id = hzp.party_id
and hzp.party_id = car.carrier_id;
select location_source_code
into l_location_source_code
from wsh_locations loc
where loc.wsh_location_id = p_location_id;
select count(mt.trip_id)
into l_count
from mst_trips mt
where plan_id = p_plan_id
and mt.continuous_move_id in (select cm.continuous_move_id
from mst_cm_trips cm
where cm.plan_id = p_plan_id);
select sum(exception_count)
into l_count
from mst_exceptions
where plan_id = p_plan_id;
select count(*)
into l_count
from mst_exception_details
where plan_id = p_plan_id
and (trip_id1 = p_trip_id
or trip_id2 = p_trip_id);
select NVL(sum(nvl(md.gross_weight, 0)),0)
into l_total_weight
from mst_deliveries md
where md.plan_id = p_plan_id
and exists (select 1
from mst_delivery_legs mdl,
mst_trip_stops mts,
mst_trips mt
where mdl.delivery_id = md.delivery_id
and mdl.pick_up_stop_id = mts.stop_id
and mt.trip_id = p_trip_id
and mts.trip_id = mt.trip_id
and mt.plan_id = p_plan_id
and mts.plan_id = p_plan_id
and mdl.plan_id = p_plan_id);
select NVL(sum(md.volume),0)
into l_total_volume
from mst_deliveries md
where md.plan_id = p_plan_id
and exists (select 1
from mst_delivery_legs mdl,
mst_trip_stops mts,
mst_trips mt
where mdl.delivery_id = md.delivery_id
and mdl.pick_up_stop_id = mts.stop_id
and mt.trip_id = p_trip_id
and mts.trip_id = mt.trip_id
and mt.plan_id = p_plan_id
and mts.plan_id = p_plan_id
and mdl.plan_id = p_plan_id);
select NVL(sum(nvl(md.number_of_pallets,0)), 0)
into l_total_pallets
from mst_deliveries md
where md.plan_id = p_plan_id
and exists (select 1
from mst_delivery_legs mdl,
mst_trip_stops mts,
mst_trips mt
where mdl.delivery_id = md.delivery_id
and mdl.pick_up_stop_id = mts.stop_id
and mt.trip_id = p_trip_id
and mts.trip_id = mt.trip_id
and mt.plan_id = p_plan_id
and mts.plan_id = p_plan_id
and mdl.plan_id = p_plan_id);
select sum(nvl(md.number_of_pieces, 0))
into l_total_pieces
from mst_deliveries md
where md.plan_id = p_plan_id
and exists (select 1
from mst_delivery_legs mdl,
mst_trip_stops mts,
mst_trips mt
where mdl.delivery_id = md.delivery_id
and mdl.pick_up_stop_id = mts.stop_id
and mt.trip_id = p_trip_id
and mts.trip_id = mt.trip_id
and mt.plan_id = p_plan_id
and mts.plan_id = p_plan_id
and mdl.plan_id = p_plan_id);
select count(mt.trip_id)
into l_count
from mst_trips mt
where mt.mode_of_transport = 'TRUCK'
and mt.plan_id = p_plan_id
and mt.trip_id in (select mts.trip_id
from mst_trip_stops mts
where mt.trip_id = mts.trip_id
and mt.plan_id = mts.plan_id
having count(mts.trip_id) = 2
group by mts.trip_id);
select count(mt.trip_id)
into l_count
from mst_trips mt
where mt.mode_of_transport = 'TRUCK'
and mt.plan_id = p_plan_id
and mt.trip_id in (select mts.trip_id
from mst_trip_stops mts
where mt.trip_id = mts.trip_id
and mt.plan_id = mts.plan_id
having count(mts.trip_id) > 2
group by mts.trip_id);
select count('x')
into l_count
from mst_trips
where plan_id = p_plan_id
and auto_release_flag = 1
and mode_of_transport = p_mode;
select count('x')
into l_count
from mst_trips
where plan_id = p_plan_id
and release_status <> 4
and release_date is not null
and auto_release_flag <> 1
and mode_of_transport = p_mode;
select count('x')
into l_count
from mst_trips
where plan_id = p_plan_id
and release_status = 4
and release_date is not null
and mode_of_transport = p_mode;
select count('x')
into l_count
from mst_trips
where plan_id = p_plan_id
and selected_for_release = 1
and mode_of_transport = p_mode;
select count('x')
into l_count
from mst_trips
where plan_id = p_plan_id
and release_date is null
and mode_of_transport = p_mode;
select count('x')
into l_count
from mst_cm_trips
where plan_id = p_plan_id
and auto_release_flag = 1;
select count('x')
into l_count
from mst_cm_trips
where plan_id = p_plan_id
and release_status <> 4
and release_date is not null
and auto_release_flag <> 1;
select count('x')
into l_count
from mst_cm_trips
where plan_id = p_plan_id
and release_status = 4
and release_date is not null;
select count('x')
into l_count
from mst_cm_trips
where plan_id = p_plan_id
and selected_for_release = 1;
select count('x')
into l_count
from mst_cm_trips
where plan_id = p_plan_id
and release_date is null;
select count(*)
into l_count
from mst_exception_details
where plan_id = p_plan_id
and continuous_move_id = p_cm_id;