[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT NVL(sum(mdd.Gross_weight),0)
INTO l_weight
FROM mst_delivery_details mdd
WHERE mdd.plan_id = p_plan_id
AND mdd.source_code = p_source_code
AND mdd.source_header_number = p_source_header_number;
SELECT NVL(sum(mdd.volume),0)
INTO l_volume
FROM mst_delivery_details mdd
WHERE mdd.plan_id = p_plan_id
AND mdd.source_code = p_source_code
AND mdd.source_header_number = p_source_header_number;
SELECT NVL(sum(mdd.number_of_pallets),0)
INTO l_pallets
FROM mst_delivery_details mdd
WHERE mdd.plan_id = p_plan_id
AND mdd.source_code = p_source_code
AND mdd.source_header_number = p_source_header_number;
SELECT NVL(sum(mdd.requested_quantity),0)
INTO l_pieces
FROM mst_delivery_details mdd
WHERE mdd.plan_id = p_plan_id
AND mdd.source_code = p_source_code
AND mdd.source_header_number = p_source_header_number;
SELECT NVL(sum(MDD.ALLOCATED_COST),0)
INTO l_cost
FROM mst_delivery_details mdd
WHERE mdd.plan_id = p_plan_id
AND mdd.source_code = p_source_code
AND mdd.source_header_number = p_source_header_number;
SELECT (T.Total_trip_distance/T.Total_direct_distance -1)*100
FROM mst_trips T
WHERE T.plan_id = p_plan_id
AND T.trip_id = p_trip_id;
SELECT COUNT(TS.Stop_Id)
FROM mst_trip_stops TS
WHERE TS.plan_id = p_plan_id
AND TS.trip_id = p_trip_id;
SELECT COUNT(DISTINCT dd.source_header_number)
FROM mst_delivery_details dd,
mst_delivery_assignments da
WHERE dd.plan_id = p_plan_Id
AND dd.delivery_detail_id = da.delivery_detail_id
AND dd.plan_id = da.plan_id
AND da.delivery_id IN
(SELECT dl.delivery_id
FROM mst_delivery_legs dl
WHERE dl.plan_id = da.plan_id
AND dl.trip_id = p_trip_id);*/
SELECT COUNT(distinct nvl(dd.split_from_delivery_detail_id,dd.delivery_detail_id))
FROM mst_delivery_details dd,
mst_delivery_assignments da
WHERE dd.plan_id = p_plan_Id
AND dd.delivery_detail_id = da.delivery_detail_id
AND dd.plan_id = da.plan_id
AND da.delivery_id IN
(SELECT dl.delivery_id
FROM mst_delivery_legs dl
WHERE dl.plan_id = da.plan_id
AND dl.trip_id = p_trip_id)
AND da.parent_delivery_detail_id is null;
SELECT NVL(SUM(d.gross_weight),0), NVL(SUM(d.volume),0),
NVL(SUM(d.number_of_pallets),0),NVL(SUM(d.number_of_pieces),0)
FROM mst_deliveries d
WHERE d.plan_id = pp_plan_id
AND d.delivery_id IN
(SELECT delivery_id
FROM mst_delivery_legs dl,
mst_trip_stops ts
WHERE dl.plan_id = d.plan_id
AND dl.pick_up_stop_id = ts.stop_id
AND ts.plan_id = dl.plan_id
AND ts.stop_id = pp_stop_id
AND ts.trip_id = pp_trip_id);
SELECT NVL(SUM(d.gross_weight),0), NVL(SUM(d.volume),0),
NVL(SUM(d.number_of_pallets),0), NVL(SUM(d.number_of_pieces),0)
FROM mst_deliveries d
WHERE d.plan_id = pp_plan_id
AND d.delivery_id IN
(SELECT delivery_id
FROM mst_delivery_legs dl,
mst_trip_stops ts
WHERE dl.plan_id = d.plan_id
AND dl.drop_off_stop_id = ts.stop_id
AND ts.plan_id = dl.plan_id
AND ts.stop_id = pp_stop_id
AND ts.trip_id = pp_trip_id);
SELECT NVL(SUM(D.Gross_Weight),0), NVL(SUM(D.Volume),0),
NVL(SUM(D.NUMBER_OF_Pallets),0), NVL(SUM(D.NUMBER_OF_PIECES),0)
FROM MST_DELIVERIES D
WHERE D.PLAN_ID = pp_plan_id
AND D.DELIVERY_ID IN
(SELECT DL.DELIVERY_ID
FROM MST_DELIVERY_LEGS DL
WHERE DL.plan_id = d.plan_id
AND dl.Trip_id = PP_Trip_Id);
SELECT COUNT(DISTINCT mdd.source_header_number)
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_deliveries md,
mst_delivery_assignments mda,
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.trip_id = p_trip_id
AND ( mdl.pick_up_stop_id = p_stop_id
OR mdl.drop_off_stop_id = p_stop_id));*/
SELECT stop_sequence_number
FROM mst_trip_stops
WHERE plan_Id = p_plan_Id
AND trip_id = p_trip_id
AND stop_id = p_stop_id;
SELECT COUNT(mdd.delivery_detail_id)
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_delivery_legs mdl
WHERE mda.plan_id = mdd.plan_id
AND mda.plan_id = mdl.plan_id
AND mda.delivery_id = mdl.delivery_id
AND mdl.trip_id = p_trip_id
AND mdl.pick_up_stop_id = p_stop_id;*/
SELECT COUNT(distinct nvl(mdd.split_from_delivery_detail_id,mdd.delivery_detail_id))
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_delivery_legs mdl
WHERE mda.plan_id = mdd.plan_id
AND mda.plan_id = mdl.plan_id
AND mda.delivery_id = mdl.delivery_id
AND mda.parent_delivery_detail_id is null
AND mdl.trip_id = p_trip_id
AND mdl.pick_up_stop_id IN
(SELECT mts1.stop_id
FROM mst_trip_stops mts1
WHERE mts1.plan_id = mdl.plan_Id
AND mts1.trip_id = mdl.trip_id
AND mts1.stop_sequence_number <= p_stop_sequence));
SELECT COUNT(distinct nvl(mdd.split_from_delivery_detail_id,mdd.delivery_detail_id))
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_delivery_legs mdl
WHERE mda.plan_id = mdd.plan_id
AND mda.plan_id = mdl.plan_id
AND mda.delivery_id = mdl.delivery_id
AND mda.parent_delivery_detail_id is null
AND mdl.trip_id = p_trip_id
AND mdl.drop_off_stop_id IN
(SELECT mts1.stop_id
FROM mst_trip_stops mts1
WHERE mts1.plan_id = mdl.plan_Id
AND mts1.trip_id = mdl.trip_id
AND mts1.stop_sequence_number <= p_stop_sequence));
Select Count(distinct nvl(DD.split_from_delivery_Detail_id,DD.Delivery_Detail_Id))
FROM Mst_Delivery_Details DD,
Mst_Delivery_Assignments DA
WHERE DD.PLAN_ID = DA.PLAN_ID
AND DD.Delivery_Detail_Id = DA.Delivery_Detail_Id
AND DA.parent_delivery_detail_id is null
AND DA.Delivery_Id = p_delivery_id
AND DA.plan_id = p_plan_id;
SELECT HZP.PARTY_NAME
FROM HZ_PARTIES HZP
WHERE HZP.PARTY_ID = P_party_id;
SELECT wlo.OWNER_TYPE, wlo.OWNER_PARTY_ID
FROM wsh_location_owners wlo
WHERE wlo.wsh_location_id = p_location_id;
SELECT 1
FROM hz_cust_acct_sites_all hzcasa,
hz_party_sites hzps,
hz_cust_accounts hzc
WHERE hzc.party_id = hzps.party_id
AND hzps.location_id = p_location_id
AND hzps.party_site_id = hzcasa.party_site_id
AND hzcasa.cust_account_id = hzc.cust_account_id
AND hzps.party_id = p_owner_party_id;
SELECT Meaning
FROM MFG_Lookups
WHERE Lookup_Code = P_Lookup_Code
AND Lookup_Type = P_Lookup_Type;
SELECT Meaning
FROM Wsh_Lookups
WHERE Lookup_Code = P_Lookup_Code
AND Lookup_Type = P_Lookup_Type;
SELECT HZP.PARTY_NAME
FROM HZ_PARTIES HZP, HZ_CUST_ACCOUNTS HCA
WHERE HCA.CUST_ACCOUNT_ID = P_party_id
AND HCA.PARTY_ID = HZP.PARTY_ID;
SELECT VENDOR_NAME
FROM PO_VENDORS pov
WHERE pov.VENDOR_ID = p_PARTY_ID;
SELECT NVL(sum(Total_Trip_Distance),0)
FROM Mst_Trips
WHERE Plan_Id = Cp_Plan_Id
AND Continuous_Move_Id = Cp_Cont_Move_Id;
SELECT COUNT(Trip_id)
FROM Mst_Trips
WHERE Plan_Id = Cp_Plan_Id
AND Continuous_Move_Id = Cp_Cont_Move_Id;
SELECT TS.PLANNED_DEPARTURE_DATE
FROM MST_TRIP_STOPS TS
WHERE TS.PLAN_ID = P_PLAN_ID
AND TS.TRIP_ID = P_TRIP_ID
AND TS.STOP_SEQUENCE_NUMBER =
(SELECT MIN(STOP_SEQUENCE_NUMBER)
FROM MST_TRIP_STOPS TS1
WHERE TS1.PLAN_ID = TS.PLAN_ID
AND TS1.TRIP_ID = TS.TRIP_ID
GROUP BY TS1.PLAN_ID, TS1.TRIP_ID);
SELECT TS.PLANNED_ARRIVAL_DATE
FROM MST_TRIP_STOPS TS
WHERE TS.PLAN_ID = P_PLAN_ID
AND TS.TRIP_ID = P_TRIP_ID
AND TS.STOP_SEQUENCE_NUMBER =
(SELECT MAX(STOP_SEQUENCE_NUMBER)
FROM MST_TRIP_STOPS TS1
WHERE TS1.PLAN_ID = TS.PLAN_ID
AND TS1.TRIP_ID = TS.TRIP_ID
GROUP BY TS1.PLAN_ID, TS1.TRIP_ID);
select departure_volume
from mst_trip_stops
where plan_id = p_plan_id
and trip_id = p_trip_id;
select nvl(peak_volume_utilization, 1)
from mst_trips
where plan_id = p_plan_id
and trip_id = p_trip_id;
SELECT MSI.Maximum_Load_Weight
FROM MTL_SYSTEM_ITEMS MSI,
FTE_VEHICLE_TYPES FVT
WHERE FVT.VEHICLE_TYPE_ID = pp_vehicle_type_id
AND MSI.INVENTORY_ITEM_ID = FVT.INVENTORY_ITEM_ID
AND MSI.ORGANIZATION_ID = FVT.ORGANIZATION_ID;
SELECT (FVT.USABLE_LENGTH *
FVT.USABLE_WIDTH *
FVT.USABLE_HEIGHT) USABLE_VOLUME
FROM MTL_SYSTEM_ITEMS MSI,
FTE_VEHICLE_TYPES FVT
WHERE FVT.VEHICLE_TYPE_ID = pp_vehicle_type_id
AND MSI.INVENTORY_ITEM_ID = FVT.INVENTORY_ITEM_ID
AND MSI.ORGANIZATION_ID = FVT.ORGANIZATION_ID;
SELECT (FVT.PALLET_FLOOR_SPACE *
FVT.PALLET_STACKING_HEIGHT ) Pallets
FROM MTL_SYSTEM_ITEMS MSI,
FTE_VEHICLE_TYPES FVT
WHERE FVT.VEHICLE_TYPE_ID = pp_vehicle_type_id
AND MSI.INVENTORY_ITEM_ID = FVT.INVENTORY_ITEM_ID
AND MSI.ORGANIZATION_ID = FVT.ORGANIZATION_ID;
SELECT TRIM(TO_CHAR(threshold_value))||' '||threshold_uom
FROM mst_excep_preferences
WHERE exception_type = p_exception_type
AND user_id = p_user_id;
SELECT wlo.owner_party_id, wlo.owner_type
FROM wsh_location_owners wlo
WHERE wlo.wsh_location_id =
(SELECT flp.location_id
FROM fte_location_parameters flp
WHERE flp.facility_id = p_facility_ID )
AND Wlo.owner_type = p_owner_type;
SELECT wlo.owner_party_id, wlo.owner_type
FROM wsh_location_owners wlo,
fte_location_parameters flp
WHERE wlo.wsh_location_id = flp.location_id
AND flp.facility_id = p_facility_ID
ORDER BY wlo.owner_type;*/
SELECT hzp.party_name
FROM hz_parties hzp,
HZ_CONTACT_POINTS hzcp
WHERE hzp.party_id = hzcp.owner_table_id
AND hzcp.CONTACT_POINT_ID = p_contact_id;
SELECT hzcp.PHONE_NUMBER
FROM HZ_CONTACT_POINTS hzcp
WHERE hzcp.CONTACT_POINT_ID = p_contact_id;
SELECT ts.stop_id
FROM mst_trip_stops ts
WHERE plan_id = p_plan_Id
AND ts.trip_id = p_trip_id
ORDER BY ts.stop_sequence_number;
select nvl(peak_weight_utilization,0)
into l_weight_utilization
from mst_trips mt
where mt.trip_id = p_trip_id
and mt.plan_id = p_plan_id;
select nvl(peak_volume_utilization,0)
into l_volume_utilization
from mst_trips mt
where mt.trip_id = p_trip_id
and mt.plan_id = p_plan_id;
select nvl(peak_pallet_utilization,0)
into l_pallet_utilization
from mst_trips mt
where mt.trip_id = p_trip_id
and mt.plan_id = p_plan_id;
select (trip_start_date - sysdate)
into l_time_remaining
from mst_trips
where plan_id = p_plan_id
and trip_id = p_trip_id;
select (start_date - sysdate)
into l_time_remaining
from mst_cm_trips
where plan_id = p_plan_id
and continuous_move_id = p_cm_id;
SELECT exception_type
INTO l_exception_type
FROM mst_exception_details
WHERE plan_id = P_PLAN_ID
AND exception_detail_id = P_EXCEPTION_DETAIL_ID;
SELECT mt.trip_number, wlk.meaning
INTO l_id1, l_temp
FROM mst_trips mt, wsh_lookups wlk
WHERE plan_id = P_PLAN_ID
AND trip_id = P_TRIP_ID
and wlk.lookup_type = 'WSH_MODE_OF_TRANSPORT'
and mt.mode_of_transport = wlk.lookup_code;
select description, med.delivery_detail_id
into l_temp1, l_id2
from mtl_system_items_tl msitl, mst_delivery_details mdd, mst_exception_details med
where med.plan_id=p_plan_id
and med.exception_detail_id = P_EXCEPTION_DETAIL_ID
and med.delivery_detail_id = mdd.delivery_detail_id
and med.plan_id = mdd.plan_id
and mdd.inventory_item_id = msitl.inventory_item_id
and mdd.organization_id = msitl.organization_id
and msitl.language = userenv('LANG');
SELECT trip_number, carrier_id
INTO l_id1, l_id2
FROM mst_trips
WHERE plan_id = P_PLAN_ID
AND trip_id = P_TRIP_ID;
select description, med.delivery_detail_id
into l_temp, l_id3
from mtl_system_items_tl msitl, mst_delivery_details mdd, mst_exception_details med
where med.plan_id=p_plan_id
and med.exception_detail_id = P_EXCEPTION_DETAIL_ID
and med.delivery_detail_id = mdd.delivery_detail_id
and med.plan_id=mdd.plan_id
and mdd.inventory_item_id = msitl.inventory_item_id
and mdd.organization_id = msitl.organization_id
and msitl.language = userenv('LANG');
SELECT freight_code into l_temp1
FROM wsh_carriers
WHERE carrier_id = l_id2;
select number1
into l_id1
from mst_exception_details med
where med.exception_detail_id = p_exception_detail_id
and med.plan_id=p_plan_id;
select set_name into l_temp
from oe_sets os
where os.set_id=l_id1;
select number1
into l_id1
from mst_exception_details med
where med.exception_detail_id = p_exception_detail_id
and med.plan_id=p_plan_id;
select set_name into l_temp
from oe_sets os
where os.set_id=l_id1;
select carrier_id
, fnd_date.DATE_TO_CHARDATE(date1)
, fnd_date.DATE_TO_CHARDATE(date2)
, decode ( fsrr.attribute_name
, 'SPEND'
, to_char ( ( med.number3 - med.number2 ) , fnd_currency.get_format_mask ( fsl.uom_code, 67 ) )
, to_char( round ( med.number3 ) - round ( med.number2 ), mst_wb_util.get_format_string ( 'NUMBER' ) ) )
into l_id2,l_temp1,l_temp2,l_temp3
from mst_exception_details med
, fte_sel_rule_restrictions fsrr
, fte_sel_rules fsl
where med.exception_detail_id = p_exception_detail_id
and med.number1 = fsrr.rule_id
and med.number1 = fsl.rule_id
and med.plan_id=p_plan_id;
SELECT freight_code INTO l_temp
FROM wsh_carriers
WHERE carrier_id = l_id2;
select LANE
into l_temp
from(
select fl.lane_number "LANE"
from fte_lanes fl, mst_exception_details med
where med.lane_id = fl.lane_id
and med.exception_detail_id=p_exception_detail_id
and med.plan_id=p_plan_id
union all
select flg.name "LANE"
from fte_lane_groups flg, mst_exception_details med
where med.lane_set_id = flg.lane_group_id
and med.exception_detail_id=p_exception_detail_id
and med.plan_id=p_plan_id);
select flp.facility_code
into l_temp
from fte_location_parameters flp, mst_trip_stops mts, mst_exception_details med
where mts.stop_id = med.stop_id1
and mts.stop_location_id = flp.location_id
and med.exception_detail_id = p_exception_detail_id
and med.plan_id = mts.plan_id
and med.plan_id = p_plan_id;
SELECT trip_number, carrier_id
INTO l_id1, l_id2
FROM mst_trips
WHERE plan_id = P_PLAN_ID
AND trip_id = P_TRIP_ID;
SELECT freight_code INTO l_temp
FROM wsh_carriers
WHERE carrier_id = l_id2;
SELECT mt.trip_number,vehicle_type_id
INTO l_id1,l_id2
FROM mst_trips mt
WHERE plan_id = P_PLAN_ID
AND trip_id = P_TRIP_ID;
select msitl.description into l_temp1
from mtl_system_items_tl msitl, mst_delivery_details mdd, mst_exception_details med
where med.plan_id=p_plan_id
and med.plan_id = mdd.plan_id
and med.exception_detail_id = P_EXCEPTION_DETAIL_ID
and med.delivery_detail_id = mdd.delivery_detail_id
and mdd.inventory_item_id = msitl.inventory_item_id
and mdd.organization_id = msitl.organization_id
and msitl.language = userenv('LANG');
SELECT msikfv.concatenated_segments
into l_temp
FROM mtl_system_items_kfv msikfv, fte_vehicle_types fvt
WHERE fvt.vehicle_type_id = l_id2
AND fvt.organization_id = msikfv.organization_id
AND fvt.inventory_item_id = msikfv.inventory_item_id;
SELECT mt.trip_number
INTO l_id1
FROM mst_trips mt
WHERE plan_id = P_PLAN_ID
AND trip_id = P_TRIP_ID;
select msitl.description into l_temp1
from mtl_system_items_tl msitl, mst_delivery_details mdd, mst_exception_details med
where med.plan_id=p_plan_id
and med.plan_id = mdd.plan_id
and med.exception_detail_id = P_EXCEPTION_DETAIL_ID
and med.delivery_detail_id = mdd.delivery_detail_id
and mdd.inventory_item_id = msitl.inventory_item_id
and mdd.organization_id = msitl.organization_id
and msitl.language = userenv('LANG');
select flp.description
into l_temp
from fte_location_parameters flp, mst_exception_details med
where
med.location_id = flp.location_id
and med.plan_id=p_plan_id
and med.exception_detail_id=p_exception_detail_id;
SELECT mt.trip_number, wlk.meaning
INTO l_id1, l_temp
FROM mst_trips mt, wsh_lookups wlk
WHERE plan_id = P_PLAN_ID
AND trip_id = P_TRIP_ID
and wlk.lookup_type = 'WSH_MODE_OF_TRANSPORT'
and mt.mode_of_transport = wlk.lookup_code;
select flp.description
into l_temp1
from fte_location_parameters flp, mst_exception_details med
where
med.location_id = flp.location_id
and med.plan_id=p_plan_id
and med.exception_detail_id=p_exception_detail_id;
select md.delivery_number
into l_id1
from mst_deliveries md, mst_exception_details med
where med.delivery_id = md.delivery_id
and med.plan_id=p_plan_id
and med.plan_id = md.plan_id
and med.exception_detail_id=p_exception_detail_id;
select flp1.facility_code, flp2.facility_code
into l_temp1, l_temp2
from fte_location_parameters flp1, fte_location_parameters flp2, mst_exception_details med
where med.location_id = flp1.location_id
and med.number1 = flp2.location_id
and med.plan_id = p_plan_id
and med.exception_detail_id = p_exception_detail_id;
SELECT carrier_id, origin_location_id, destination_location_id, trip_number
INTO l_id1, l_id2, l_id3, l_id4
FROM mst_trips
WHERE plan_id = P_PLAN_ID
AND trip_id = P_TRIP_ID;
SELECT freight_code INTO l_temp
FROM wsh_carriers
WHERE carrier_id = l_id1;
SELECT city INTO l_temp
FROM wsh_locations
WHERE wsh_location_id = l_id2;
SELECT city INTO l_temp
FROM wsh_locations
WHERE wsh_location_id = l_id3;
SELECT distance_uom
INTO l_str1
FROM mst_plans
WHERE plan_id = P_PLAN_ID;
SELECT round(number3), round(number4)
INTO l_id1, l_id2
FROM mst_exception_details
WHERE plan_id = P_PLAN_ID
AND exception_detail_id = P_EXCEPTION_DETAIL_ID;
SELECT round(number1), round(number2)
INTO l_id1, l_id2
FROM mst_exception_details
WHERE plan_id = P_PLAN_ID
AND exception_detail_id = P_EXCEPTION_DETAIL_ID;
SELECT carrier_id, origin_location_id, destination_location_id, trip_number
INTO l_id1, l_id2, l_id3, l_id4
FROM mst_trips
WHERE plan_id = P_PLAN_ID
AND trip_id = P_TRIP_ID;
SELECT freight_code INTO l_temp
FROM wsh_carriers
WHERE carrier_id = l_id1;
SELECT city INTO l_temp
FROM wsh_locations
WHERE wsh_location_id = l_id2;
SELECT city INTO l_temp
FROM wsh_locations
WHERE wsh_location_id = l_id3;
SELECT nvl(round(wcs.max_driving_time_in_24hr),0)
INTO l_id2
FROM wsh_carrier_services wcs,
mst_exception_details med
WHERE wcs.carrier_service_id = med.carrier_service_id
AND med.plan_id = P_PLAN_ID
AND med.exception_detail_id = P_EXCEPTION_DETAIL_ID;
SELECT nvl(round(number1),0)
INTO l_id1
FROM mst_exception_details
WHERE plan_id = P_PLAN_ID
AND exception_detail_id = P_EXCEPTION_DETAIL_ID;
SELECT nvl(round(wcs.max_total_time),0)
INTO l_id2
FROM wsh_carrier_services wcs,
mst_exception_details med
WHERE wcs.carrier_service_id = med.carrier_service_id
AND med.plan_id = P_PLAN_ID
AND med.exception_detail_id = P_EXCEPTION_DETAIL_ID;
SELECT nvl(round(wcs.max_duty_time_in_24hr),0)
INTO l_id2
FROM wsh_carrier_services wcs,
mst_exception_details med
WHERE wcs.carrier_service_id = med.carrier_service_id
AND med.plan_id = P_PLAN_ID
AND med.exception_detail_id = P_EXCEPTION_DETAIL_ID;
SELECT nvl(round(number2),0)
INTO l_id1
FROM mst_exception_details
WHERE plan_id = P_PLAN_ID
AND exception_detail_id = P_EXCEPTION_DETAIL_ID;
SELECT nvl(round(wcs.min_layover_time),0)
INTO l_id2
FROM wsh_carrier_services wcs,
mst_exception_details med
WHERE wcs.carrier_service_id = med.carrier_service_id
AND med.plan_id = P_PLAN_ID
AND med.exception_detail_id = P_EXCEPTION_DETAIL_ID;
SELECT nvl(round(number3),0)
INTO l_id1
FROM mst_exception_details
WHERE plan_id = P_PLAN_ID
AND exception_detail_id = P_EXCEPTION_DETAIL_ID;
SELECT nvl(round(((trip_end_date - trip_start_date) * 24)),0)
INTO l_id1
FROM mst_trips
WHERE plan_id = P_PLAN_ID
AND trip_id = P_TRIP_ID;
SELECT carrier_id, origin_location_id, destination_location_id, trip_number
INTO l_id1, l_id2, l_id3, l_id4
FROM mst_trips
WHERE plan_id = P_PLAN_ID
AND trip_id = P_TRIP_ID;
SELECT freight_code INTO l_temp
FROM wsh_carriers
WHERE carrier_id = l_id1;
SELECT city INTO l_temp
FROM wsh_locations
WHERE wsh_location_id = l_id2;
SELECT city INTO l_temp
FROM wsh_locations
WHERE wsh_location_id = l_id3;
SELECT number1, number2
INTO l_id1, l_id2
FROM mst_exception_details
WHERE plan_id = P_PLAN_ID
AND trip_id1 = P_TRIP_ID
AND exception_detail_id = P_EXCEPTION_DETAIL_ID;
SELECT carrier_id, origin_location_id, destination_location_id, trip_number
INTO l_id1, l_id2, l_id3, l_id4
FROM mst_trips
WHERE plan_id = P_PLAN_ID
AND trip_id = P_TRIP_ID;
SELECT freight_code INTO l_temp
FROM wsh_carriers
WHERE carrier_id = l_id1;
SELECT city INTO l_temp
FROM wsh_locations
WHERE wsh_location_id = l_id2;
SELECT city INTO l_temp
FROM wsh_locations
WHERE wsh_location_id = l_id3;
SELECT distance_uom INTO l_temp
FROM mst_plans
WHERE plan_id = P_PLAN_ID;
SELECT carrier_id, continuous_move_id, round(number2), round(number3)
INTO l_id1, l_id2, l_id3, l_id4
FROM mst_exception_details
WHERE plan_id = P_PLAN_ID
AND exception_detail_id = P_EXCEPTION_DETAIL_ID;
SELECT freight_code INTO l_temp
FROM wsh_carriers
WHERE carrier_id = l_id1;
SELECT cm_trip_number, start_location_id, end_location_id
INTO l_id1, l_id3, l_id4
FROM mst_cm_trips
WHERE plan_id = P_PLAN_ID
AND continuous_move_id = l_id2;
SELECT city INTO l_temp
FROM wsh_locations
WHERE wsh_location_id = l_id3;
SELECT city INTO l_temp
FROM wsh_locations
WHERE wsh_location_id = l_id4;
SELECT origin_location_id, destination_location_id, trip_number
INTO l_id2, l_id3, l_id4
FROM mst_trips
WHERE plan_id = P_PLAN_ID
AND trip_id = P_TRIP_ID;
SELECT city INTO l_temp
FROM wsh_locations
WHERE wsh_location_id = l_id2;
SELECT city INTO l_temp
FROM wsh_locations
WHERE wsh_location_id = l_id3;
SELECT trip_number
INTO l_id1
FROM mst_trips
WHERE plan_id = P_PLAN_ID
AND trip_id = P_TRIP_ID;
SELECT mts.stop_sequence_number, mts.stop_location_id
INTO l_id2, l_id3
FROM mst_trip_stops mts,
mst_exception_details med
WHERE mts.plan_id = med.plan_id
AND med.plan_id = P_PLAN_ID
AND mts.trip_id = med.trip_id1
AND mts.stop_id = med.stop_id1
AND med.exception_detail_id = P_EXCEPTION_DETAIL_ID;
SELECT city INTO l_temp
FROM wsh_locations
WHERE wsh_location_id = l_id3;
SELECT trip_number
INTO l_id1
FROM mst_trips
WHERE plan_id = P_PLAN_ID
AND trip_id = P_TRIP_ID;
SELECT mts.stop_location_id, mts.planned_departure_date, round(med.number1)
INTO l_id1, l_date1, l_id3
FROM mst_trip_stops mts,
mst_exception_details med
WHERE mts.plan_id = med.plan_id
AND mts.stop_id = med.stop_id1
AND med.plan_id = P_PLAN_ID
AND med.exception_detail_id = P_EXCEPTION_DETAIL_ID;
SELECT city INTO l_temp
FROM wsh_locations
WHERE wsh_location_id = l_id1;
SELECT mts.stop_location_id, mts.planned_arrival_date
INTO l_id3, l_date2
FROM mst_trip_stops mts,
mst_exception_details med
WHERE mts.plan_id = med.plan_id
AND mts.stop_id = med.stop_id2
AND med.plan_id = P_PLAN_ID
AND med.exception_detail_id = P_EXCEPTION_DETAIL_ID;
SELECT city INTO l_temp
FROM wsh_locations
WHERE wsh_location_id = l_id3;
SELECT trip_number
INTO l_id1
FROM mst_trips
WHERE plan_id = P_PLAN_ID
AND trip_id = P_TRIP_ID;
SELECT round(nvl(number1, 0)), round(nvl(number2, 0)), round(nvl(number3, 0)), round(nvl(number4, 0))
INTO l_id1, l_id2, l_id3, l_id7
FROM mst_exception_details med
WHERE exception_detail_id = P_EXCEPTION_DETAIL_ID
AND plan_id = P_PLAN_ID;
SELECT round(nvl(max_drive_time, 0)), round(nvl(max_duty_time, 0)), round(nvl(minimum_lay_time, 0)), round(nvl(max_driving_distance, 0))
INTO l_id4, l_id5, l_id6, l_id8
FROM mst_parameters
WHERE user_id = -9999;
SELECT trip_number
INTO l_id1
FROM mst_trips
WHERE plan_id = P_PLAN_ID
AND trip_id = P_TRIP_ID;
SELECT description into l_temp
FROM fte_location_parameters
WHERE location_id in (SELECT mts.stop_location_id
FROM mst_trip_stops mts,
mst_exception_details med
WHERE mts.plan_id = med.plan_id
AND mts.stop_id = med.stop_id1
AND med.plan_id = P_PLAN_ID
AND med.exception_detail_id = P_EXCEPTION_DETAIL_ID);
SELECT trip_number
INTO l_id1
FROM mst_trips
WHERE plan_id = P_PLAN_ID
AND trip_id = P_TRIP_ID;
SELECT flp.description, round(nvl(med.number1, 0), 1), round((mts.planned_departure_date - mts.planned_arrival_date) * 24, 1)
INTO l_temp, l_id1, l_id2
FROM fte_location_parameters flp,
mst_trip_stops mts,
mst_exception_details med
WHERE flp.location_id = mts.stop_location_id
AND mts.plan_id = med.plan_id
AND mts.stop_id = med.stop_id1
AND med.plan_id = P_PLAN_ID
AND med.exception_detail_id = P_EXCEPTION_DETAIL_ID;
SELECT trip_number, origin_location_id, destination_location_id, carrier_id,
round((nvl(total_basic_transport_cost,0) + nvl(total_accessorial_cost,0) + nvl(total_layover_cost,0) + nvl(total_load_unload_cost,0) + nvl(total_stop_cost,0)))
INTO l_id1, l_id2, l_id3, l_id4, l_id5
FROM mst_trips
WHERE plan_id = P_PLAN_ID
AND trip_id = P_TRIP_ID;
SELECT freight_code
INTO l_temp
FROM wsh_carriers
WHERE carrier_id = l_id4;
SELECT city
INTO l_temp
FROM wsh_locations
WHERE wsh_location_id = l_id2;
SELECT city
INTO l_temp
FROM wsh_locations
WHERE wsh_location_id = l_id3;
SELECT wc.freight_code, round(nvl(med.number1, 0))
INTO l_temp, l_id1
FROM wsh_carriers wc,
mst_exception_details med
WHERE med.plan_id = P_PLAN_ID
AND med.exception_detail_id = P_EXCEPTION_DETAIL_ID
AND med.carrier_id = wc.carrier_id;
SELECT trip_number
INTO l_id1
FROM mst_trips
WHERE plan_id = P_PLAN_ID
AND trip_id = P_TRIP_ID;
SELECT mt.trip_number, mt.peak_weight_utilization,
mt.peak_volume_utilization,
mt.peak_pallet_utilization,
med.number2,
med.number1,
med.number3,
med.vehicle_type_id
INTO l_id1, l_id2, l_id3, l_id4, l_id5, l_id6, l_id7, l_id8
FROM mst_trips mt,
mst_exception_details med
WHERE mt.plan_id = med.plan_id
AND med.plan_id = P_PLAN_ID
AND mt.trip_id = P_TRIP_ID
AND med.exception_detail_id = P_EXCEPTION_DETAIL_ID;
SELECT volume_uom
INTO l_temp
FROM mst_plans
WHERE plan_id = P_PLAN_ID;
SELECT msikfv.internal_volume * get_uom_conversion_rate (msikfv.volume_uom_code,
l_temp,
msikfv.organization_id,
msikfv.inventory_item_id)
INTO l_id1
FROM mtl_system_items_kfv msikfv,
fte_vehicle_types fvt
WHERE msikfv.inventory_item_id = fvt.inventory_item_id
AND msikfv.organization_id = fvt.organization_id
AND fvt.vehicle_type_id = l_id8;
SELECT weight_uom, volume_uom
INTO l_temp, l_temp1
FROM mst_plans
WHERE plan_id = P_PLAN_ID;
SELECT mt.trip_number, mt.peak_weight_utilization,
mt.peak_volume_utilization,
mt.peak_pallet_utilization,
med.number2,
med.number1,
med.number3
INTO l_id1, l_id2, l_id3, l_id4, l_id5, l_id6, l_id7
FROM mst_trips mt,
mst_exception_details med
WHERE mt.plan_id = med.plan_id
AND med.plan_id = P_PLAN_ID
AND mt.trip_id = P_TRIP_ID
AND med.exception_detail_id = P_EXCEPTION_DETAIL_ID;
SELECT weight_uom, volume_uom
INTO l_temp, l_temp1
FROM mst_plans
WHERE plan_id = P_PLAN_ID;
SELECT trip_number
INTO l_id1
FROM mst_trips
WHERE plan_id = P_PLAN_ID
AND trip_id = P_TRIP_ID;
SELECT wc.freight_code
INTO l_temp
FROM mst_exception_details med,
wsh_carriers wc
WHERE med.plan_id = P_PLAN_ID
AND med.exception_detail_id = P_EXCEPTION_DETAIL_ID
AND med.carrier_id = wc.carrier_id;
SELECT Get_Partner_Name(decode(med.customer_id, null, med.supplier_id, med.customer_id),
decode(med.customer_id, null, 2, 1))
INTO l_temp
FROM mst_exception_details med
WHERE med.plan_id = P_PLAN_ID
AND med.exception_detail_id = P_EXCEPTION_DETAIL_ID;
SELECT trip_number, carrier_id
INTO l_id1, l_id2
FROM mst_trips
WHERE plan_id = P_PLAN_ID
AND trip_id = P_TRIP_ID;
SELECT wc.freight_code
INTO l_temp
FROM wsh_carriers wc
WHERE wc.carrier_id = l_id2;
SELECT flp.description
INTO l_temp
FROM fte_location_parameters flp,
mst_exception_details med,
mst_trip_stops mts
WHERE med.stop_id1 = mts.stop_id
AND mts.plan_id = P_PLAN_ID
AND med.exception_detail_id = P_EXCEPTION_DETAIL_ID
AND mts.stop_location_id = flp.location_id;
SELECT Get_Partner_Name(decode(med.customer_id, null, med.supplier_id, med.customer_id),
decode(med.customer_id, null, 2, 1))
INTO l_temp
FROM mst_exception_details med
WHERE med.plan_id = P_PLAN_ID
AND med.exception_detail_id = P_EXCEPTION_DETAIL_ID;
SELECT trip_number
INTO l_id1
FROM mst_trips
WHERE plan_id = P_PLAN_ID
AND trip_id = P_TRIP_ID;
SELECT Get_Partner_Name(med.customer_id, 1)
INTO l_temp
FROM mst_exception_details med
WHERE med.plan_id = P_PLAN_ID
AND med.exception_detail_id = P_EXCEPTION_DETAIL_ID;
SELECT Get_Partner_Name(med.number1, 1)
INTO l_temp
FROM mst_exception_details med
WHERE med.plan_id = P_PLAN_ID
AND med.exception_detail_id = P_EXCEPTION_DETAIL_ID;
SELECT ml.meaning INTO l_message
FROM mfg_lookups ml, mst_exception_details med
WHERE med.exception_type = ml.lookup_code
AND ml.lookup_type = 'MST_EXCEPTION_TYPE'
and med.exception_detail_id = P_EXCEPTION_DETAIL_ID;
SELECT hzp.party_name
from hz_parties hzp
where hzp.party_id = l_contact_id;
select hzcp.phone_country_code||'-'||hzcp.phone_area_code||'-'||hzcp.phone_number||'-'||hzcp.phone_extension
, hzcp.email_address
from hz_contact_points hzcp
where hzcp.owner_table_id = l_contact_id
and hzcp.contact_point_type = l_ret_str_type
and hzcp.primary_flag = 'Y'
and hzcp.owner_table_name = 'HZ_PARTIES'
and hzcp.status = 'A';
select hzcp.phone_country_code||'-'||hzcp.phone_area_code||'-'||hzcp.phone_number||'-'||hzcp.phone_extension
, hzcp.email_address
from hz_contact_points hzcp
where hzcp.owner_table_id = l_contact_id
and hzcp.contact_point_type = l_ret_str_type
and hzcp.primary_flag = 'N'
and hzcp.owner_table_name = 'HZ_PARTIES'
and hzcp.status = 'A';
SELECT HZP.PARTY_NAME
FROM HZ_PARTIES HZP
WHERE HZP.PARTY_ID = P_party_id;
SELECT wlo.OWNER_PARTY_ID
INTO l_owner_party_id
FROM wsh_location_owners wlo
WHERE wlo.wsh_location_id = P_Location_id
AND wlo.owner_type = P_Owner_Type;
/* select sum(nvl(mt.total_basic_transport_cost,0)
+ nvl(mt.total_accessorial_cost,0)
+ nvl(mt.total_handling_cost,0)
+ nvl(mt.total_layover_cost,0)
+ nvl(mt.total_load_unload_cost,0)
+ nvl(mt.total_stop_cost,0))
into l_plan_value
from mst_trips mt
where mt.plan_id = p_plan_id;
select sum(nvl(mdd.unit_price,0)* mdd.requested_quantity)
into l_plan_value
from mst_delivery_details mdd
, mst_delivery_assignments mda
where mdd.plan_id = p_plan_id
and mda.plan_id = mdd.plan_id
and mda.delivery_detail_id = mdd.delivery_detail_id
and mda.parent_delivery_detail_id is null;
select sum(nvl(mdl.allocated_transport_cost,0)
+ (nvl(allocated_fac_shp_hand_cost, 0) + nvl(allocated_fac_rec_hand_cost, 0))
+ nvl(mdl.allocated_fac_loading_cost,0)
+ nvl(mdl.allocated_fac_unloading_cost,0)) allocated_cost
into l_plan_alloc_cost
from mst_delivery_legs mdl
where mdl.plan_id = p_plan_id;
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(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.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
and mdd.split_from_delivery_detail_id is null;
select sum( nvl(mdl.allocated_fac_loading_cost,0)
+ nvl(mdl.allocated_fac_shp_hand_cost,0)
+ nvl(mdl.allocated_transport_cost,0)) total_departing_cost
from mst_trips mt,
mst_trip_stops mts,
mst_delivery_legs mdl
where mt.plan_id = l_plan_id
and mt.mode_of_transport = l_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 = l_my_fac_location_id;
select sum( nvl(mdl.allocated_fac_unloading_cost,0)
+ nvl(mdl.allocated_fac_rec_hand_cost,0)
+ nvl(mdl.allocated_transport_cost,0)) total_arriving_cost
from mst_trips mt,
mst_trip_stops mts,
mst_delivery_legs mdl
where mt.plan_id = l_plan_id
and mt.mode_of_transport = l_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 = l_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 mts.trip_id = mt.trip_id
and mts.plan_id = mt.plan_id
and mdl.plan_id = mt.plan_id
and mdl.trip_id = mt.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 md.delivery_id = mdl.delivery_id
and md.plan_id = mt.plan_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 mts.trip_id = mt.trip_id
and mts.plan_id = mt.plan_id
and mdl.plan_id = mt.plan_id
and mdl.trip_id = mt.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 md.delivery_id = mdl.delivery_id
and md.plan_id = mt.plan_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 mts.trip_id = mt.trip_id
and mts.plan_id = mt.plan_id
and mdl.plan_id = mt.plan_id
and mdl.trip_id = mt.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 md.delivery_id = mdl.delivery_id
and md.plan_id = mt.plan_id;
select sum(nvl(mt.total_basic_transport_cost,0)
+ nvl(mt.total_accessorial_cost,0)
+ nvl(mt.total_handling_cost,0)
+ nvl(mt.total_layover_cost,0)
+ nvl(mt.total_load_unload_cost,0)
+ nvl(mt.total_stop_cost,0))
into l_total_value
from mst_trips mt
where mt.plan_id = p_plan_id
and (mt.origin_location_id IN (select distinct fte.location_id
from fte_location_parameters fte
where fte.facility_id = p_facility_id)
or mt.destination_location_id IN (select distinct fte.location_id
from fte_location_parameters fte
where fte.facility_id = p_facility_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 sum(nvl(mt.total_basic_transport_cost,0)
+ nvl(mt.total_accessorial_cost,0)
+ nvl(mt.total_handling_cost,0)
+ nvl(mt.total_layover_cost,0)
+ nvl(mt.total_load_unload_cost,0)
+ nvl(mt.total_stop_cost,0))
into l_total_value
from mst_trips mt
where mt.plan_id = p_plan_id
and mt.origin_location_id = p_origin_id;
select sum(nvl(mdl.allocated_transport_cost,0)
+ (nvl(allocated_fac_shp_hand_cost, 0) + nvl(allocated_fac_rec_hand_cost, 0))
+ nvl(mdl.allocated_fac_loading_cost,0)
+ nvl(mdl.allocated_fac_unloading_cost,0)) allocated_cost
into l_alloc_cost
from mst_delivery_legs mdl
, mst_trips mt
where mdl.plan_id = mt.plan_id
and mdl.trip_id = mt.trip_id
and mt.plan_id = p_plan_id
and mt.origin_location_id = p_origin_id;
select count(distinct nvl(mdd.split_from_delivery_detail_id, mdd.delivery_detail_id))
into l_count_origin_orders
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 md.pickup_location_id = p_origin_id
and mdd.plan_id = mda.plan_id
and mdd.delivery_detail_id = mda.delivery_detail_id
and mda.parent_delivery_detail_id is null
and md.plan_id = p_plan_id;
select count(*)
into l_count_stops_origin
from (select distinct mt.trip_id, count(*) num_stops
from mst_trips mt
, mst_trip_stops mts
where mt.plan_id = p_plan_id
and mt.origin_location_id = p_origin_id
and mts.plan_id = mt.plan_id
and mts.trip_id = mt.trip_id
group by mt.trip_id) temp
where temp.num_stops > 2;
select sum(md.gross_weight)
into l_total_weight_origin
from mst_deliveries md
where md.plan_id = p_plan_id
and md.pickup_location_id = p_origin_id;
select sum(md.volume)
into l_total_volume_origin
from mst_deliveries md
where md.plan_id = p_plan_id
and md.pickup_location_id = p_origin_id;
select sum(md.number_of_pieces)
into l_total_pieces_origin
from mst_deliveries md
where md.plan_id = p_plan_id
and md.pickup_location_id = p_origin_id;
select count (1)
into l_trip_count
from mst_trips mt
where mt.plan_id = p_plan_id
and mt.mode_of_transport = p_mode_of_transport
and mt.origin_location_id = p_origin_id;
select nvl(sum(nvl(mt.total_accessorial_cost,0)
+ nvl(mt.total_basic_transport_cost,0)
+ nvl(mt.total_layover_cost,0)
+ nvl(mt.total_handling_cost,0)
+ nvl(mt.total_load_unload_cost,0)
+ nvl(mt.total_stop_cost,0)),0)
into l_cost
from mst_trips mt
where mt.plan_id = p_plan_id
and mt.mode_of_transport = p_mode_of_transport
and mt.origin_location_id = p_origin_id;
select nvl(sum(nvl(mdl.allocated_fac_loading_cost,0)
+ nvl(mdl.allocated_fac_unloading_cost,0)
+ nvl(mdl.ALLOCATED_FAC_SHP_HAND_COST,0)
+ nvl(mdl.ALLOCATED_FAC_REC_HAND_COST,0)
+ nvl(mdl.allocated_transport_cost,0)),0)
into l_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_of_transport
and mt.origin_location_id = p_origin_id
and mts.plan_id = mt.plan_id
and mts.trip_id = mt.trip_id
and mts.stop_location_id = mt.origin_location_id
and mdl.plan_id = mt.plan_id
and mdl.trip_id = mt.trip_id;
select count(*)
into total_dtl
from (select distinct mt.trip_id, count(*) num_stops
from mst_trips mt
, mst_trip_stops mts
where mt.plan_id = p_plan_id
and mt.origin_location_id = p_origin_id
and mt.mode_of_transport = 'TRUCK'
and mts.plan_id = mt.plan_id
and mts.trip_id = mt.trip_id
group by mt.trip_id) temp
where temp.num_stops = 2;
select sum(nvl(mt.total_basic_transport_cost,0)
+ nvl(mt.total_accessorial_cost,0)
+ nvl(mt.total_handling_cost,0)
+ nvl(mt.total_layover_cost,0)
+ nvl(mt.total_load_unload_cost,0)
+ nvl(mt.total_stop_cost,0))
into l_total_value
from mst_trips mt
where mt.plan_id = p_plan_id
and mt.destination_location_id = p_dest_id;
select sum(nvl(mdl.allocated_transport_cost,0)
+ (nvl(allocated_fac_shp_hand_cost, 0) + nvl(allocated_fac_rec_hand_cost, 0))
+ nvl(mdl.allocated_fac_loading_cost,0)
+ nvl(mdl.allocated_fac_unloading_cost,0)) allocated_cost
into l_alloc_cost
from mst_delivery_legs mdl
, mst_trips mt
where mdl.plan_id = mt.plan_id
and mdl.trip_id = mt.trip_id
and mt.plan_id = p_plan_id
and mt.destination_location_id = p_dest_id;
select count(distinct nvl(mdd.split_from_delivery_detail_id, mdd.delivery_detail_id))
into l_count_dest_orders
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 md.dropoff_location_id = p_dest_id
and mdd.plan_id = mda.plan_id
and mdd.delivery_detail_id = mda.delivery_detail_id
and mda.parent_delivery_detail_id is null
and md.plan_id = p_plan_id;
select count(*)
into l_count_stops_dest
from (select distinct mt.trip_id, count(*) num_stops
from mst_trips mt
, mst_trip_stops mts
where mt.plan_id = p_plan_id
and mt.destination_location_id = p_dest_id
and mts.plan_id = mt.plan_id
and mts.trip_id = mt.trip_id
group by mt.trip_id) temp
where temp.num_stops > 2;
select sum(md.gross_weight)
into l_total_weight_dest
from mst_deliveries md
where md.plan_id = p_plan_id
and md.dropoff_location_id = p_dest_id;
select sum(md.volume)
into l_total_volume_dest
from mst_deliveries md
where md.plan_id = p_plan_id
and md.dropoff_location_id = p_dest_id;
select sum(md.number_of_pieces)
into l_total_pieces_dest
from mst_deliveries md
where md.plan_id = p_plan_id
and md.dropoff_location_id = p_dest_id;
select count (1)
into l_trip_count
from mst_trips mt
where mt.plan_id = p_plan_id
and mt.mode_of_transport = p_mode_of_transport
and mt.destination_location_id = p_dest_id;
select nvl(sum(nvl(mt.total_accessorial_cost,0)
+ nvl(mt.total_basic_transport_cost,0)
+ nvl(mt.total_layover_cost,0)
+ nvl(mt.total_handling_cost,0)
+ nvl(mt.total_load_unload_cost,0)
+ nvl(mt.total_stop_cost,0)),0)
into l_cost
from mst_trips mt
where mt.plan_id = p_plan_id
and mt.mode_of_transport = p_mode_of_transport
and mt.destination_location_id = p_dest_id;
select nvl(sum(nvl(mdl.allocated_fac_loading_cost,0)
+ nvl(mdl.allocated_fac_unloading_cost,0)
+ nvl(mdl.ALLOCATED_FAC_SHP_HAND_COST,0)
+ nvl(mdl.ALLOCATED_FAC_REC_HAND_COST,0)
+ nvl(mdl.allocated_transport_cost,0)),0)
into l_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_of_transport
and mt.destination_location_id = p_dest_id
and mts.plan_id = mt.plan_id
and mts.trip_id = mt.trip_id
and mts.stop_location_id = mt.destination_location_id
and mdl.plan_id = mt.plan_id
and mdl.trip_id = mt.trip_id;
select count(*)
into total_dtl
from (select distinct mt.trip_id, count(*) num_stops
from mst_trips mt
, mst_trip_stops mts
where mt.plan_id = p_plan_id
and mt.destination_location_id = p_dest_id
and mt.mode_of_transport = 'TRUCK'
and mts.plan_id = mt.plan_id
and mts.trip_id = mt.trip_id
group by mt.trip_id) temp
where temp.num_stops = 2;
select sum(nvl(mt.total_basic_transport_cost,0)
+ nvl(mt.total_accessorial_cost,0)
+ nvl(mt.total_handling_cost,0)
+ nvl(mt.total_layover_cost,0)
+ nvl(mt.total_load_unload_cost,0)
+ nvl(mt.total_stop_cost,0))
into l_total_value
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 = mdl.plan_id
and md.customer_id = p_customer_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);
select sum(nvl(mdl.allocated_transport_cost,0)
+ (nvl(allocated_fac_shp_hand_cost, 0) + nvl(allocated_fac_rec_hand_cost, 0))
+ nvl(mdl.allocated_fac_loading_cost,0)
+ nvl(mdl.allocated_fac_unloading_cost,0)) allocated_cost
into l_alloc_cost
from mst_delivery_legs mdl
where mdl.plan_id = p_plan_id
and mdl.trip_id IN
(select distinct mts.trip_id
from mst_trip_stops mts
, mst_delivery_legs mdl
, mst_deliveries md
where md.plan_id = mdl.plan_id
and md.customer_id = p_customer_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);
select count(mt.trip_id)
into l_count_stops
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_customer_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_trip_count
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 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_customer_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);
SELECT SUM (nvl(MT.TOTAL_ACCESSORIAL_COST,0)
+ nvl(MT.TOTAL_BASIC_TRANSPORT_COST,0)
+ nvl(MT.TOTAL_LAYOVER_COST,0)
+ nvl(MT.TOTAL_HANDLING_COST,0)
+ nvl(MT.TOTAL_LOAD_UNLOAD_COST,0)
+ nvl(MT.TOTAL_STOP_COST,0))
INTO l_cost
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 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_customer_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);
select nvl(sum(nvl(mdl.allocated_fac_loading_cost,0)
+ nvl(mdl.allocated_fac_unloading_cost,0)
+ nvl(mdl.ALLOCATED_FAC_SHP_HAND_COST,0)
+ nvl(mdl.ALLOCATED_FAC_REC_HAND_COST,0)
+ nvl(mdl.allocated_transport_cost,0)),0)
into l_cost
from mst_deliveries md
, mst_delivery_legs mdl
, mst_trips mt
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
and mt.plan_id = mdl.plan_id
and mt.trip_id = mdl.trip_id
and mt.mode_of_transport = p_mode_of_transport;
select count(mt.trip_id)
into total_dtl
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_customer_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 sum(nvl(mt.total_basic_transport_cost,0)
+ nvl(mt.total_accessorial_cost,0)
+ nvl(mt.total_handling_cost,0)
+ nvl(mt.total_layover_cost,0)
+ nvl(mt.total_load_unload_cost,0)
+ nvl(mt.total_stop_cost,0))
into l_total_value
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 = mdl.plan_id
and md.supplier_id = p_supplier_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);
select sum(nvl(mdl.allocated_transport_cost,0)
+ (nvl(allocated_fac_shp_hand_cost, 0) + nvl(allocated_fac_rec_hand_cost, 0))
+ nvl(mdl.allocated_fac_loading_cost,0)
+ nvl(mdl.allocated_fac_unloading_cost,0)) allocated_cost
into l_alloc_cost
from mst_delivery_legs mdl
where mdl.plan_id = p_plan_id
and mdl.trip_id IN
(select distinct mts.trip_id
from mst_trip_stops mts
, mst_delivery_legs mdl
, mst_deliveries md
where md.plan_id = mdl.plan_id
and md.supplier_id = p_supplier_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);
select count(mt.trip_id)
into l_count_stops
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_supplier_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_trip_count
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 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_supplier_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);
SELECT SUM (nvl(MT.TOTAL_ACCESSORIAL_COST,0)
+ nvl(MT.TOTAL_BASIC_TRANSPORT_COST,0)
+ nvl(MT.TOTAL_LAYOVER_COST,0)
+ nvl(MT.TOTAL_HANDLING_COST,0)
+ nvl(MT.TOTAL_LOAD_UNLOAD_COST,0)
+ nvl(MT.TOTAL_STOP_COST,0))
INTO l_cost
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 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_supplier_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);
select nvl(sum(nvl(mdl.allocated_fac_loading_cost,0)
+ nvl(mdl.allocated_fac_unloading_cost,0)
+ nvl(mdl.ALLOCATED_FAC_SHP_HAND_COST,0)
+ nvl(mdl.ALLOCATED_FAC_REC_HAND_COST,0)
+ nvl(mdl.allocated_transport_cost,0)),0)
into l_cost
from mst_deliveries md
, mst_delivery_legs mdl
, mst_trips mt
where md.plan_id = p_plan_id
and md.supplier_id = p_supplier_id
and md.plan_id = mdl.plan_id
and md.delivery_id = mdl.delivery_id
and mt.plan_id = mdl.plan_id
and mt.trip_id = mdl.trip_id
and mt.mode_of_transport = p_mode_of_transport;
select count(mt.trip_id)
into total_dtl
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_supplier_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 mts.planned_arrival_date, mts.planned_departure_date
into l_arr_time, l_dep_time
from mst_trip_stops mts
where mts.plan_id = p_plan_id
and mts.trip_id = p_trip_id
and mts.stop_id = p_stop_id;
select (mts.planned_departure_date - mts.planned_arrival_date) * 24
, TRUNC((mts.planned_departure_date - mts.planned_arrival_date) * 24)
into l_wait_hrs_min, l_wait_hrs
from mst_trip_stops mts
where mts.plan_id = p_plan_id
and mts.trip_id = p_trip_id
and mts.stop_id = p_stop_id;
select mt.carrier_id, mt.trip_id
into l_prev_carr, l_trip_id
from mst_delivery_legs mdl
, mst_trips mt
where mdl.plan_id = p_plan_id
and mdl.delivery_id = p_delivery_id
and mdl.trip_id <> p_trip_id
and mt.plan_id = mdl.plan_id
and mt.trip_id = mdl.trip_id
and (mdl.pick_up_stop_id IN (select mts.stop_id from mst_trip_stops mts
where mts.plan_id = p_plan_id
and mts.stop_location_id = p_stop_location_id)
or mdl.drop_off_stop_id IN (select mts.stop_id from mst_trip_stops mts
where mts.plan_id = p_plan_id
and mts.stop_location_id = p_stop_location_id)
);
select nvl(mt.total_handling_cost,0) -- + mt.total_load_unload_cost)
into l_carr_cost
from mst_trips mt
where mt.plan_id = p_plan_id
and mt.trip_id = l_trip_id;
select mts.stop_sequence_number
from mst_trip_stops mts
where mts.plan_id = p_plan_id
and mts.trip_id = p_trip_id
order by mts.stop_sequence_number desc;
select min(mts.stop_sequence_number)
into p_min_seq_num
from mst_trip_stops mts
where mts.plan_id = p_plan_id
and mts.trip_id = p_trip_id;
select mts.distance_to_next_stop
into p_ret_val
from mst_trip_stops mts
where mts.plan_id = p_plan_id
and mts.trip_id = p_trip_id
and mts.stop_sequence_number = r_get_prev_stop_seqnum (p_plan_id, p_trip_id, p_curr_seq_num);
select mts.drv_time_to_next_stop
into p_ret_val
from mst_trip_stops mts
where mts.plan_id = p_plan_id
and mts.trip_id = p_trip_id
and mts.stop_sequence_number = r_get_prev_stop_seqnum (p_plan_id, p_trip_id, p_curr_seq_num);
select mts.distance_cost
into p_ret_val
from mst_trip_stops mts
where mts.plan_id = p_plan_id
and mts.trip_id = p_trip_id
and mts.stop_sequence_number = r_get_prev_stop_seqnum (p_plan_id, p_trip_id, p_curr_seq_num);
SELECT substr(wl.location_code,1,5)
, substr(mst_wb_util.get_name(loc_id),1,10)
, substr(wl.address1,1,20)
, substr(wl.city,1,10)
, substr(wl.state,1,10)
, substr(wl.postal_code,1,5)
INTO l_code
, l_name1
, l_address
, l_city
, l_state
, l_zip
FROM wsh_locations wl
WHERE wl.wsh_location_id = loc_id;
select substr(mst_wb_util.get_local_chardt(p_stop_location_id, min(mts.planned_departure_date)),1,20)
into latest_depart_date_frm_dest
from mst_delivery_legs mdl
, mst_trip_stops mts
where mdl.plan_id = p_plan_id
and mdl.delivery_id in (select distinct mdl1.delivery_id from mst_delivery_legs mdl1
where mdl1.plan_id = mdl.plan_id
and mdl1.trip_id = p_trip_id)
and mdl.trip_id <> p_trip_id
and mts.plan_id = mdl.plan_id
and mts.trip_id = mdl.trip_id
and mts.stop_location_id = p_stop_location_id -- will be destination_location_id of the previous trip
and mdl.pick_up_stop_id in (select distinct mts1.stop_id from mst_trip_stops mts1
where mts1.plan_id = mts.plan_id
and mts1.trip_id = mts.trip_id);
select sum(mdd.allocated_cost)
into l_order_cost
from mst_delivery_details mdd
where mdd.source_code = p_source_code
and mdd.source_header_number = p_source_header_number;
select mt.destination_location_id
from mst_deliveries md
, mst_delivery_legs mdl
, mst_trips mt
where md.plan_id = p_plan_id
and md.delivery_id = p_delivery_id
and mdl.plan_id = md.plan_id
and mdl.delivery_id = md.delivery_id
and mdl.delivery_leg_id <> p_delivery_leg_id
and mt.plan_id = mdl.plan_id
and mt.trip_id = mdl.trip_id;
select mts.stop_location_id
into leg_pickup_loc_id
from mst_trip_stops mts
where mts.plan_id = p_plan_id
and mts.stop_id = p_pick_up_stop_id;
select mt.origin_location_id
from mst_deliveries md
, mst_delivery_legs mdl
, mst_trips mt
where md.plan_id = p_plan_id
and md.delivery_id = p_delivery_id
and mdl.plan_id = md.plan_id
and mdl.delivery_id = md.delivery_id
and mdl.delivery_leg_id <> p_delivery_leg_id
and mt.plan_id = mdl.plan_id
and mt.trip_id = mdl.trip_id;
select mts.stop_location_id
into leg_dropoff_loc_id
from mst_trip_stops mts
where mts.plan_id = p_plan_id
and mts.stop_id = p_drop_off_stop_id;
SELECT SUM(total_basic_transport_cost + total_accessorial_cost +
total_handling_cost + total_layover_cost +
total_load_unload_cost + total_stop_cost)
INTO l_cost
FROM mst_trips
WHERE plan_id = p_plan_id
AND trip_id IN (p_trip_id1, p_trip_id2);
SELECT COUNT('x')
INTO l_count
FROM mst_delivery_legs
WHERE plan_id = p_plan_id
AND trip_id = p_trip_id;
SELECT meaning
INTO l_str
FROM mfg_lookups
WHERE lookup_type = 'MST_TRIP_LOADING_STATUS'
AND lookup_code = l_lookup_code;
SELECT city
FROM wsh_locations
WHERE wsh_location_id = p_location_id;
SELECT freight_code
FROM wsh_carriers
WHERE carrier_id = p_carrier_id;
SELECT distance_uom, weight_uom, volume_uom
FROM mst_plans
WHERE plan_id = P_PLAN_ID;
SELECT msikfv.concatenated_segments
FROM mtl_system_items_kfv msikfv,
fte_vehicle_types fvt
WHERE fvt.vehicle_type_id = p_vehicle_type_id
AND fvt.organization_id = msikfv.organization_id
AND fvt.inventory_item_id = msikfv.inventory_item_id;
SELECT lane_number
FROM fte_lanes
WHERE lane_id = p_lane_id;
SELECT description
FROM fte_location_parameters
WHERE location_id = p_location_id;
SELECT nvl(number1, 0), round(nvl(number2, 0), 1)
INTO l_id1, l_id2
FROM mst_out_request_details
WHERE out_request_detail_id = P_OUT_REQUEST_DETAIL_ID
AND exception_type = P_EXCEPTION_TYPE;
SELECT number1, number2, number3, number4
INTO l_id1, l_id2, l_id3, l_id4
FROM mst_out_request_details
WHERE out_request_detail_id = P_OUT_REQUEST_DETAIL_ID
AND exception_type = P_EXCEPTION_TYPE;
SELECT round(nvl(number7, 0)), round(nvl(number8, 0))
INTO l_id1, l_id2
FROM mst_out_request_details
WHERE out_request_detail_id = P_OUT_REQUEST_DETAIL_ID
AND exception_type = P_EXCEPTION_TYPE;
SELECT round(nvl(number5, 0)), round(nvl(number6, 0))
INTO l_id1, l_id2
FROM mst_out_request_details
WHERE out_request_detail_id = P_OUT_REQUEST_DETAIL_ID
AND exception_type = P_EXCEPTION_TYPE;
SELECT number1, number2, number3, number4, round(nvl(number5, 0)), round(nvl(number6, 0))
INTO l_id1, l_id2, l_id3, l_id4, l_id5, l_id6
FROM mst_out_request_details
WHERE out_request_detail_id = P_OUT_REQUEST_DETAIL_ID
AND exception_type = P_EXCEPTION_TYPE;
SELECT nvl(number1, -1)
INTO l_id1
FROM mst_out_request_details
WHERE out_request_detail_id = P_OUT_REQUEST_DETAIL_ID
AND exception_type = P_EXCEPTION_TYPE;
SELECT trip_number, origin_location_id, destination_location_id
INTO l_id2, l_id3, l_id4
FROM mst_trips
WHERE plan_id = P_PLAN_ID
AND trip_id = l_id1;
SELECT nvl(number1, -1), nvl(number2, -1)
INTO l_id1, l_id2
FROM mst_out_request_details
WHERE out_request_detail_id = P_OUT_REQUEST_DETAIL_ID
AND exception_type = P_EXCEPTION_TYPE;
SELECT trip_number
INTO l_id3
FROM mst_trips
WHERE trip_id = l_id1
AND plan_id = P_PLAN_ID;
SELECT stop_location_id, stop_sequence_number
INTO l_id4, l_id5
FROM mst_trip_stops
WHERE plan_id = P_PLAN_ID
AND stop_id = l_id2;
SELECT number1, number2, number3, round(nvl(number4, 0)), round(nvl(number5, 0))
INTO l_id1, l_id2, l_id3, l_id4, l_id5
FROM mst_out_request_details
WHERE out_request_detail_id = P_OUT_REQUEST_DETAIL_ID
AND exception_type = P_EXCEPTION_TYPE;
SELECT number1, round(nvl(number2, 0)), round(nvl(number3, 0)), round(nvl(number4, 0)), round(nvl(number5, 0)), round(nvl(number6, 0)), round(nvl(number7, 0)), round(nvl(number8, 0)), round(nvl(number9, 0))
INTO l_id1, l_id2, l_id3, l_id4, l_id5, l_id6, l_id7, l_id8, l_id9
FROM mst_out_request_details
WHERE out_request_detail_id = P_OUT_REQUEST_DETAIL_ID
AND exception_type = P_EXCEPTION_TYPE;
SELECT number1, number2, number3, number4
INTO l_id1, l_id2, l_id3, l_id4
FROM mst_out_request_details
WHERE out_request_detail_id = P_OUT_REQUEST_DETAIL_ID
AND exception_type = P_EXCEPTION_TYPE;
SELECT nvl(number5, 0), nvl(number6, 0)
INTO l_id1, l_id2
FROM mst_out_request_details
WHERE out_request_detail_id = P_OUT_REQUEST_DETAIL_ID
AND exception_type = P_EXCEPTION_TYPE;
SELECT number1, number2, number3, number4
INTO l_id1, l_id2, l_id3, l_id4
FROM mst_out_request_details
WHERE out_request_detail_id = P_OUT_REQUEST_DETAIL_ID
AND exception_type = P_EXCEPTION_TYPE;
SELECT round(nvl(number5, 0)), round(nvl(number6, 0))
INTO l_id1, l_id2
FROM mst_out_request_details
WHERE out_request_detail_id = P_OUT_REQUEST_DETAIL_ID
AND exception_type = P_EXCEPTION_TYPE;
SELECT round(nvl(number7, 0)), round(nvl(number8, 0))
INTO l_id1, l_id2
FROM mst_out_request_details
WHERE out_request_detail_id = P_OUT_REQUEST_DETAIL_ID
AND exception_type = P_EXCEPTION_TYPE;
SELECT round(nvl(number9, 0)), round(nvl(number10, 0))
INTO l_id1, l_id2
FROM mst_out_request_details
WHERE out_request_detail_id = P_OUT_REQUEST_DETAIL_ID
AND exception_type = P_EXCEPTION_TYPE;
SELECT round(nvl(number11, 0)), round(nvl(number12, 0))
INTO l_id1, l_id2
FROM mst_out_request_details
WHERE out_request_detail_id = P_OUT_REQUEST_DETAIL_ID
AND exception_type = P_EXCEPTION_TYPE;
SELECT number1, number2, number3, number4
INTO l_id1, l_id2, l_id3, l_id4
FROM mst_out_request_details
WHERE out_request_detail_id = P_OUT_REQUEST_DETAIL_ID
AND exception_type = P_EXCEPTION_TYPE;
SELECT number1, number2, char1
INTO l_id1, l_id2, l_str1
FROM mst_out_request_details
WHERE out_request_detail_id = P_OUT_REQUEST_DETAIL_ID
AND exception_type = P_EXCEPTION_TYPE;
SELECT wlk.meaning
INTO l_str2
FROM wsh_lookups wlk
WHERE wlk.lookup_type = 'WSH_MODE_OF_TRANSPORT'
and wlk.lookup_code = l_str1;
select msitl.description into l_str3
from mtl_system_items_tl msitl, mst_delivery_details mdd
where mdd.plan_id = P_PLAN_ID
and mdd.delivery_detail_id = l_id2
and mdd.inventory_item_id = msitl.inventory_item_id
and mdd.organization_id = msitl.organization_id
and msitl.language = userenv('LANG');
SELECT number1, number2, number3
INTO l_id1, l_id2, l_id3
FROM mst_out_request_details
WHERE out_request_detail_id = P_OUT_REQUEST_DETAIL_ID
AND exception_type = P_EXCEPTION_TYPE;
select msitl.description into l_str2
from mtl_system_items_tl msitl, mst_delivery_details mdd
where mdd.plan_id = P_PLAN_ID
and mdd.delivery_detail_id = l_id2
and mdd.inventory_item_id = msitl.inventory_item_id
and mdd.organization_id = msitl.organization_id
and msitl.language = userenv('LANG');
SELECT freight_code into l_str3
FROM wsh_carriers
WHERE carrier_id = l_id3;
SELECT number1
INTO l_id1
FROM mst_out_request_details
WHERE out_request_detail_id = P_OUT_REQUEST_DETAIL_ID
AND exception_type = P_EXCEPTION_TYPE;
select set_name into l_str1
from oe_sets os
where os.set_id=l_id1;
SELECT number1
INTO l_id1
FROM mst_out_request_details
WHERE out_request_detail_id = P_OUT_REQUEST_DETAIL_ID
AND exception_type = P_EXCEPTION_TYPE;
select set_name into l_str1
from oe_sets os
where os.set_id=l_id1;
SELECT number1, number2, number3, number4, nvl(number5, 0), nvl(number6, 0)
INTO l_id1, l_id2, l_id3, l_id4, l_id5, l_id6
FROM mst_out_request_details
WHERE out_request_detail_id = P_OUT_REQUEST_DETAIL_ID
AND exception_type = P_EXCEPTION_TYPE;
SELECT number1, number3, number4, number5, date1, date2
INTO l_id1, l_id3, l_id4, l_id5, l_date1, l_date2
FROM mst_out_request_details
WHERE out_request_detail_id = P_OUT_REQUEST_DETAIL_ID
AND exception_type = P_EXCEPTION_TYPE;
SELECT freight_code INTO l_str1
FROM wsh_carriers
WHERE carrier_id = l_id1;
select LANE
into l_str2
from(
select fl.lane_number "LANE"
from fte_lanes fl
where fl.lane_id = l_id3
UNION ALL
select flg.name "LANE"
from fte_lane_groups flg
where flg.lane_group_id = l_id4);
SELECT number1, number2
INTO l_id1, l_id2
FROM mst_out_request_details
WHERE out_request_detail_id = P_OUT_REQUEST_DETAIL_ID
AND exception_type = P_EXCEPTION_TYPE;
SELECT number1, number2, round(nvl(number3, 0), 2), round(nvl(number4, 0), 2)
INTO l_id1, l_id2, l_id3, l_id4
FROM mst_out_request_details
WHERE out_request_detail_id = P_OUT_REQUEST_DETAIL_ID
AND exception_type = P_EXCEPTION_TYPE;
SELECT number1, number2, number3, number4, round(nvl(number5, 0), 2), round(nvl(number6, 0), 2)
INTO l_id1, l_id2, l_id3, l_id4, l_id5, l_id6
FROM mst_out_request_details
WHERE out_request_detail_id = P_OUT_REQUEST_DETAIL_ID
AND exception_type = P_EXCEPTION_TYPE;
SELECT number1, number2, number3
INTO l_id1, l_id2, l_id3
FROM mst_out_request_details
WHERE out_request_detail_id = P_OUT_REQUEST_DETAIL_ID
AND exception_type = P_EXCEPTION_TYPE;
SELECT number1, number2, number3, number4
INTO l_id1, l_id2, l_id3, l_id4
FROM mst_out_request_details
WHERE out_request_detail_id = P_OUT_REQUEST_DETAIL_ID
AND exception_type = P_EXCEPTION_TYPE;
select flp.facility_code
into l_str1
from fte_location_parameters flp
where flp.location_id = l_id2;
SELECT freight_code INTO l_str2
FROM wsh_carriers
WHERE carrier_id = l_id4;
SELECT number1, number2, number3, number4, round(nvl(number5, 0)), number6, round(nvl(number7, 0))
INTO l_id1, l_id2, l_id3, l_id4, l_id5, l_id6, l_id7
FROM mst_out_request_details
WHERE out_request_detail_id = P_OUT_REQUEST_DETAIL_ID
AND exception_type = P_EXCEPTION_TYPE;
SELECT number1
INTO l_id1
FROM mst_out_request_details
WHERE out_request_detail_id = P_OUT_REQUEST_DETAIL_ID
AND exception_type = P_EXCEPTION_TYPE;
SELECT number1, round(number2), round(number3), round(number4), round(number5), round(number6), round(number7), number8
INTO l_id1, l_id2, l_id3, l_id4, l_id5, l_id6, l_id7, l_id8
FROM mst_out_request_details
WHERE out_request_detail_id = P_OUT_REQUEST_DETAIL_ID
AND exception_type = P_EXCEPTION_TYPE;
SELECT volume_uom
INTO l_str1
FROM mst_plans
WHERE plan_id = P_PLAN_ID;
SELECT msikfv.internal_volume * get_uom_conversion_rate (msikfv.volume_uom_code,
l_str1,
msikfv.organization_id,
msikfv.inventory_item_id)
INTO l_id9
FROM mtl_system_items_kfv msikfv,
fte_vehicle_types fvt
WHERE msikfv.inventory_item_id = fvt.inventory_item_id
AND msikfv.organization_id = fvt.organization_id
AND fvt.vehicle_type_id = l_id8;
SELECT number1, round(number2), round(number3), round(number4), round(number5), round(number6), round(number7)
INTO l_id1, l_id2, l_id3, l_id4, l_id5, l_id6, l_id7
FROM mst_out_request_details
WHERE out_request_detail_id = P_OUT_REQUEST_DETAIL_ID
AND exception_type = P_EXCEPTION_TYPE;
SELECT number1, number2, number3
INTO l_id1, l_id2, l_id3
FROM mst_out_request_details
WHERE out_request_detail_id = P_OUT_REQUEST_DETAIL_ID
AND exception_type = P_EXCEPTION_TYPE;
select msitl.description into l_str1
from mtl_system_items_tl msitl, mst_delivery_details mdd
where mdd.plan_id = P_PLAN_ID
and mdd.delivery_detail_id = l_id2
and mdd.inventory_item_id = msitl.inventory_item_id
and mdd.organization_id = msitl.organization_id
and msitl.language = userenv('LANG');
SELECT msikfv.concatenated_segments
into l_str2
FROM mtl_system_items_kfv msikfv, fte_vehicle_types fvt
WHERE fvt.vehicle_type_id = l_id3
AND fvt.organization_id = msikfv.organization_id
AND fvt.inventory_item_id = msikfv.inventory_item_id;
SELECT number1
INTO l_id1
FROM mst_out_request_details
WHERE out_request_detail_id = P_OUT_REQUEST_DETAIL_ID
AND exception_type = P_EXCEPTION_TYPE;
SELECT number1, number2, number3
INTO l_id1, l_id2, l_id3
FROM mst_out_request_details
WHERE out_request_detail_id = P_OUT_REQUEST_DETAIL_ID
AND exception_type = P_EXCEPTION_TYPE;
SELECT number1, number2, number3
INTO l_id1, l_id2, l_id3
FROM mst_out_request_details
WHERE out_request_detail_id = P_OUT_REQUEST_DETAIL_ID
AND exception_type = P_EXCEPTION_TYPE;
SELECT number1, number2, number3
INTO l_id1, l_id2, l_id3
FROM mst_out_request_details
WHERE out_request_detail_id = P_OUT_REQUEST_DETAIL_ID
AND exception_type = P_EXCEPTION_TYPE;
select msitl.description into l_str1
from mtl_system_items_tl msitl, mst_delivery_details mdd
where mdd.plan_id = P_PLAN_ID
and mdd.delivery_detail_id = l_id2
and mdd.inventory_item_id = msitl.inventory_item_id
and mdd.organization_id = msitl.organization_id
and msitl.language = userenv('LANG');
select flp.description
into l_str2
from fte_location_parameters flp
where flp.location_id = l_id3;
SELECT number1, number3, char1
INTO l_id1, l_id3, l_str1
FROM mst_out_request_details
WHERE out_request_detail_id = P_OUT_REQUEST_DETAIL_ID
AND exception_type = P_EXCEPTION_TYPE;
SELECT wlk.meaning
INTO l_str2
FROM wsh_lookups wlk
WHERE wlk.lookup_type = 'WSH_MODE_OF_TRANSPORT'
and wlk.lookup_code = l_str1;
select flp.description
into l_str3
from fte_location_parameters flp
where flp.location_id = l_id3;
SELECT number1, number2, number3, number4
INTO l_id1, l_id2, l_id3, l_id4
FROM mst_out_request_details
WHERE out_request_detail_id = P_OUT_REQUEST_DETAIL_ID
AND exception_type = P_EXCEPTION_TYPE;
select flp1.facility_code
into l_str1
from fte_location_parameters flp1
where flp1.location_id = l_id2;
select flp1.facility_code
into l_str2
from fte_location_parameters flp1
where flp1.location_id = l_id3;
SELECT number1, number2, number3, number4, number5
INTO l_id1, l_id2, l_id3, l_id4, l_id5
FROM mst_out_request_details
WHERE out_request_detail_id = P_OUT_REQUEST_DETAIL_ID
AND exception_type = P_EXCEPTION_TYPE;
SELECT number1, number2, number3, number4, number5, number6
INTO l_id1, l_id2, l_id3, l_id4, l_id5, l_id6
FROM mst_out_request_details
WHERE out_request_detail_id = P_OUT_REQUEST_DETAIL_ID
AND exception_type = P_EXCEPTION_TYPE;
SELECT number1
INTO l_id1
FROM mst_out_request_details
WHERE out_request_detail_id = P_OUT_REQUEST_DETAIL_ID
AND exception_type = P_EXCEPTION_TYPE;
SELECT meaning
INTO l_message
FROM mfg_lookups
WHERE lookup_type = 'MST_EXCEPTION_TYPE'
AND lookup_code = P_EXCEPTION_TYPE;
SELECT flp.location_id
FROM fte_location_parameters flp
WHERE flp.facility_id = P_facility_id;
delete from MST_DELIVERY_LEGS
where plan_id = p_plan_id;
delete from MST_TRIP_STOPS
where plan_id = p_plan_id;
delete from MST_TRIPS
where plan_id = p_plan_id;
delete from MST_CM_TRIPS
where plan_id = p_plan_id;
delete from MST_DELIVERY_ASSIGNMENTS
where plan_id = p_plan_id;
delete from MST_DELIVERY_DETAILS
where plan_id = p_plan_id;
delete from MST_DELIVERIES
where plan_id = p_plan_id;
delete from MST_EXCEPTION_DETAILS
where plan_id = p_plan_id;
delete from MST_EXCEPTIONS
where plan_id = p_plan_id;
delete from MST_FILES
where plan_id = p_plan_id;
delete from MST_IN_REQUESTS
where plan_id = p_plan_id;
delete from MST_LOAD_SUMMARY
where plan_id = p_plan_id;
delete from MST_OUT_REQUEST_DETAILS
where plan_id = p_plan_id;
delete from MST_OUT_REQUESTS
where plan_id = p_plan_id;
delete from MST_PERSONAL_QUERY_RESULTS
where plan_id = p_plan_id;
delete from MST_PLAN_CONSTRAINT_RULES
where plan_id = p_plan_id;
delete from MST_PLAN_FACILITIES
where plan_id = p_plan_id;
delete from MST_PLAN_PENALTY_BREAKS
where plan_id = p_plan_id;
delete from MST_PLAN_ZIP_LOCATIONS
where plan_id = p_plan_id;
delete from MST_RELATED_LOADS_TEMP
where plan_id = p_plan_id;
delete from MST_RELEASE_TEMP
where plan_id = p_plan_id;
delete from MST_SNAPSHOT_TASKS
where plan_id = p_plan_id;
delete from MST_TMP_PLN_LOC
where plan_id = p_plan_id;
delete from MST_EXCEPT_DETAILS_DETAILS
where plan_id = p_plan_id;
delete from MST_PLANS
where plan_id = p_plan_id;
select mdd.organization_id
from mst_deliveries md
, mst_delivery_assignments mda
, mst_delivery_details mdd
where mdd.plan_id = mda.plan_id
and mdd.delivery_detail_id = mda.delivery_detail_id
and mda.plan_id = md.plan_id
and mda.delivery_id = md.delivery_id
and md.plan_id = p_plan_id
and md.delivery_id = p_delivery_id;
select '1'
from wf_item_activity_statuses
where item_type ='MSTEXPWF'
and item_key = l_plan_id || '-' || l_exception_detail_id
and notification_id is not null;
select nvl(wr.city_code, wr.city) from
wsh_locations wsh,
wsh_region_locations wlr,
wsh_regions_v wr
where wsh.wsh_location_id = p_location_id
and wsh.wsh_location_id = wlr.location_id
and wlr.region_type = 2 -- city
and wlr.region_id = wr.region_id;
SELECT substr(wl.city , 1, 10)
FROM wsh_locations wl
WHERE wsh_location_id = p_location_id;
select exception_type
from mst_exceptions
where plan_id = l_plan_id
and exception_count_context = l_dummy;
execute immediate 'update mst_exceptions
set exception_count_context = -9999
where '||p_Exp_Summary_Where_Clause;
execute immediate 'select count(1)
from mst_exception_details
where '||replace(p_Exp_Details_Where_Clause,'!~!',l_Exception_Type_Tab(i))
into l_Count_Tab(i);
update mst_exceptions
set exception_count_context = l_count_Tab(i)
where Plan_id = p_Plan_Id
and exception_type = l_Exception_Type_Tab(i);
PROCEDURE Update_Del_And_Rel_Trips(p_Plan_Id IN NUMBER,
p_Trip_Id IN NUMBER,
p_Planned_Flag IN NUMBER,
P_Notified OUT NOCOPY NUMBER) IS
CURSOR cur_affected_deliveries(p_plan_id IN NUMBER,
p_trip_id IN NUMBER) IS
SELECT DELIVERY_ID, PLANNED_FLAG, KNOWN_TE_FIRM_STATUS, PRESERVE_GROUPING_FLAG
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
WHERE mdl.PLAN_ID = md.Plan_Id
AND mdl.TRIP_ID = p_Trip_Id)
FOR UPDATE OF PLANNED_FLAG NOWAIT;
SELECT TRIP_ID, PLANNED_FLAG
FROM mst_trips mt
WHERE mt.PLAN_ID = p_Plan_Id
AND mt.TRIP_ID IN (SELECT mdl2.TRIP_ID
FROM MST_DELIVERY_LEGS mdl1
, MST_DELIVERY_LEGS mdl2
WHERE mdl1.PLAN_ID = mdl2.PLAN_ID
AND mdl1.DELIVERY_ID = mdl2.DELIVERY_ID
AND mdl1.TRIP_ID <> mdl2.TRIP_ID
AND mdl1.PLAN_ID = mt.Plan_Id
AND mdl1.TRIP_ID = p_Trip_Id);
l_update_stmt VARCHAR2(500);
l_update_stmt := 'UPDATE MST_DELIVERIES '||
' SET PLANNED_FLAG = decode(:p_Planned_Flag,1,1,3,2,PLANNED_FLAG) '||
' , PRESERVE_GROUPING_FLAG = DECODE(:p_Planned_Flag, '||
' 3, DECODE(KNOWN_TE_FIRM_STATUS, '||
' 2 , 1, '||
' PRESERVE_GROUPING_FLAG), '||
' PRESERVE_GROUPING_FLAG) '||
' WHERE plan_id = :p_plan_id '||
' and delivery_id = :p_delivery_id '||
' RETURNING planned_flag into :l_planned_flag';
EXECUTE IMMEDIATE l_update_stmt
USING p_planned_flag, p_planned_flag,
p_plan_id, l_rec_affected_deliveries.delivery_id
RETURNING INTO l_planned_flag;
l_update_stmt := ' UPDATE MST_TRIPS '||
' SET PLANNED_FLAG = DECODE(:p_Planned_Flag, '||
' 1, DECODE(SIGN(PLANNED_FLAG-2), '||
' -1, PLANNED_FLAG, 2), '||
' 3,DECODE(SIGN(PLANNED_FLAG-2), '||
' -1, 2, PLANNED_FLAG), '||
' PLANNED_FLAG) '||
' WHERE PLAN_ID = :p_Plan_Id '||
' AND TRIP_ID = :p_trip_id '||
' RETURNING planned_flag into :l_planned_flag';
EXECUTE IMMEDIATE l_update_stmt
USING p_planned_flag, p_plan_id, l_rec_affected_trips.trip_id
RETURNING INTO l_planned_flag;
END Update_Del_And_Rel_Trips;
PROCEDURE Update_Trips_Of_CM(p_Plan_Id IN NUMBER,
p_Continuous_Move_Id IN NUMBER,
P_Notified OUT NOCOPY NUMBER) IS
CURSOR cur_affected_trips(p_plan_id IN NUMBER,
p_Continuous_Move_Id IN NUMBER) IS
SELECT TRIP_ID, PLANNED_FLAG
FROM mst_trips mt
WHERE plan_id = p_plan_id
AND Continuous_Move_Id = p_Continuous_Move_Id
FOR UPDATE OF PLANNED_FLAG NOWAIT;
l_update_stmt VARCHAR2(500);
l_update_stmt := ' UPDATE MST_TRIPS '||
' SET PLANNED_FLAG = DECODE(SIGN(PLANNED_FLAG-2),-1,PLANNED_FLAG,2) '||
' WHERE PLAN_ID = :p_Plan_Id '||
' AND TRIP_ID = :p_trip_id '||
' RETURNING planned_flag into :l_planned_flag';
EXECUTE IMMEDIATE l_update_stmt
USING p_plan_id, l_rec_affected_trips.trip_id
RETURNING INTO l_planned_flag;
END Update_Trips_Of_CM;
SELECT uom_class
FROM mtl_units_of_measure
WHERE uom_code = p_uom_code;
SELECT muc2.conversion_rate/muc1.conversion_rate
FROM mtl_uom_conversions muc1,
mtl_uom_conversions muc2
WHERE muc1.inventory_item_id = 0
AND muc2.inventory_item_id = 0
AND muc1.uom_class = l_to_uom_class
AND muc1.uom_code = p_to_uom_code
AND muc2.uom_class = l_from_uom_class
AND muc2.uom_code = p_from_uom_code;
SELECT muc.conversion_rate
FROM mtl_uom_conversions_view muc
WHERE muc.inventory_item_id = p_inventory_item_id
AND muc.organization_id = p_org_id
AND muc.primary_uom_code = p_uom1
AND muc.uom_code = p_uom2;
SELECT 'x'
FROM fte_sel_rule_restrictions fsrr
WHERE fsrr.rule_id = p_rule_id
AND fsrr.attribute_name = 'SPEND';
EXECUTE IMMEDIATE ' SELECT COUNT(1) FROM ' || p_view_name || ' WHERE ' || p_where_clause
INTO l_count;