eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.MST_REPORTS_PKG SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 23


  select nvl(mp.total_orders,0)
  from mst_plans mp
  where mp.plan_id = l_plan_id;

Line: 30


  select count(distinct 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_trips t
            , mst_trip_stops ts
            , mst_delivery_legs mdl
            , fte_location_parameters flp
         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 = flp.location_id
	 and flp.facility_id = l_myfac_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 = l_plan_id
  and   mdd.container_flag = 2;

Line: 60


  select count(distinct dd.source_header_number)
  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 = l_plan_id
        and   decode(l_c_s_ident, 2, md.customer_id, md.supplier_id) = l_cust_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))
  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 = l_plan_id
  and   decode(l_c_s_ident, 2, md.customer_id, md.supplier_id) = l_cust_supp_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;

Line: 97


  select count(distinct nvl(mdd.split_from_delivery_detail_id, mdd.delivery_detail_id))
  from mst_delivery_details mdd
  , mst_delivery_assignments mda
  , mst_deliveries md
  where mdd.plan_id = l_plan_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 = mda.plan_id
  and md.delivery_id = mda.delivery_id
  and md.delivery_id in (select distinct mdl.delivery_id
                         from mst_delivery_legs mdl
			 , mst_trips mt
			 where mdl.plan_id = md.plan_id
			 and mt.plan_id = mdl.plan_id
			 and mt.trip_id = mdl.trip_id
			 and mt.carrier_id = l_carrier_id);

Line: 149


  select count(*)
  from mst_deliveries md
  where md.plan_id = l_plan_id;

Line: 156


  select count(*)
  from mst_deliveries md
  , fte_location_parameters flp
  where md.plan_id = l_plan_id
  and (md.pickup_location_id = flp.location_id
      or md.dropoff_location_id = flp.location_id)
  and flp.facility_id = l_myfac_id;

Line: 168


  select count(*)
  from mst_deliveries md
  where md.plan_id = l_plan_id
  and decode(l_c_s_ident, 2, md.customer_id, md.supplier_id) = l_cust_supp_id;

Line: 175


  select count(*)
  from mst_deliveries md
  where md.plan_id = l_plan_id
  and md.delivery_id in (select distinct mdl.delivery_id
                         from mst_delivery_legs mdl
			 , mst_trips mt
			 where mdl.plan_id = md.plan_id
			 and mt.plan_id = mdl.plan_id
			 and mt.trip_id = mdl.trip_id
			 and mt.carrier_id = l_carrier_id);

Line: 219


  select nvl(mp.total_weight,0)
  from mst_plans mp
  where mp.plan_id = l_plan_id;

Line: 226


  select nvl(sum(nvl(md.gross_weight,0)),0)
  from mst_deliveries md
     , fte_location_parameters flp
  where md.plan_id = l_plan_id
  and (md.pickup_location_id = flp.location_id
      or md.dropoff_location_id = flp.location_id)
  and flp.facility_id = l_myfac_id;

Line: 238


  select nvl(sum(nvl(md.gross_weight,0)),0)
  from mst_deliveries md
  where md.plan_id = l_plan_id
  and decode(l_c_s_ident, 2, md.customer_id, md.supplier_id) = l_cust_supp_id;

Line: 245


  select nvl(sum(nvl(md.gross_weight,0)),0)
  from mst_deliveries md
  where md.plan_id = l_plan_id
  and md.delivery_id in (select distinct mdl.delivery_id
                         from mst_delivery_legs mdl
			 , mst_trips mt
			 where mdl.plan_id = md.plan_id
			 and mt.plan_id = mdl.plan_id
			 and mt.trip_id = mdl.trip_id
			 and mt.carrier_id = l_carrier_id);

Line: 289


  select nvl(mp.total_volume,0)
  from mst_plans mp
  where mp.plan_id = l_plan_id;

Line: 296


  select nvl(sum(nvl(md.volume,0)),0)
  from mst_deliveries md
     , fte_location_parameters flp
  where md.plan_id = l_plan_id
  and (md.pickup_location_id = flp.location_id
      or md.dropoff_location_id = flp.location_id)
  and flp.facility_id = l_myfac_id;

Line: 308


  select nvl(sum(nvl(md.volume,0)),0)
  from mst_deliveries md
  where md.plan_id = l_plan_id
  and decode(l_c_s_ident, 2, md.customer_id, md.supplier_id) = l_cust_supp_id;

Line: 315


  select nvl(sum(nvl(md.volume,0)),0)
  from mst_deliveries md
  where md.plan_id = l_plan_id
  and md.delivery_id in (select distinct mdl.delivery_id
                         from mst_delivery_legs mdl
			 , mst_trips mt
			 where mdl.plan_id = md.plan_id
			 and mt.plan_id = mdl.plan_id
			 and mt.trip_id = mdl.trip_id
			 and mt.carrier_id = l_carrier_id);

Line: 359


  select nvl(mp.total_pieces,0)
  from mst_plans mp
  where mp.plan_id = l_plan_id;

Line: 366


  select nvl(sum(nvl(md.number_of_pieces,0)),0)
  from mst_deliveries md
     , fte_location_parameters flp
  where md.plan_id = l_plan_id
  and (md.pickup_location_id = flp.location_id
      or md.dropoff_location_id = flp.location_id)
  and flp.facility_id = l_myfac_id;

Line: 378


  select nvl(sum(nvl(md.number_of_pieces,0)),0)
  from mst_deliveries md
  where md.plan_id = l_plan_id
  and decode(l_c_s_ident, 2, md.customer_id, md.supplier_id) = l_cust_supp_id;

Line: 385


  select nvl(sum(nvl(md.number_of_pieces,0)),0)
  from mst_deliveries md
  where md.plan_id = l_plan_id
  and md.delivery_id in (select distinct mdl.delivery_id
                         from mst_delivery_legs mdl
			 , mst_trips mt
			 where mdl.plan_id = md.plan_id
			 and mt.plan_id = mdl.plan_id
			 and mt.trip_id = mdl.trip_id
			 and mt.carrier_id = l_carrier_id);

Line: 428


  select sum(nvl(mdd.unit_price,0)* nvl(mdd.requested_quantity,0))
  from mst_delivery_details mdd
  , mst_delivery_assignments mda
  , mst_deliveries md
  where mdd.plan_id = l_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
  and md.plan_id = mda.plan_id
  and md.delivery_id = mda.delivery_id;

Line: 441


  select sum(nvl(mdd.unit_price,0)* nvl(mdd.requested_quantity,0))
  from mst_delivery_details mdd
  , mst_delivery_assignments mda
  , mst_deliveries md
  , fte_location_parameters flp
  where mdd.plan_id = l_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
  and md.plan_id = mda.plan_id
  and md.delivery_id = mda.delivery_id
  and (md.pickup_location_id = flp.location_id
      or md.dropoff_location_id = flp.location_id)
  and flp.facility_id = l_myfac_id;

Line: 459


  select sum(nvl(mdd.unit_price,0)* nvl(mdd.requested_quantity,0))
  from mst_delivery_details mdd
  , mst_delivery_assignments mda
  , mst_deliveries md
  where mdd.plan_id = l_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
  and md.plan_id = mda.plan_id
  and md.delivery_id = mda.delivery_id
  and decode(l_c_s_ident, 2, md.customer_id, md.supplier_id) = l_cust_supp_id;

Line: 473


  select sum(nvl(mdd.unit_price,0)* nvl(mdd.requested_quantity,0))
  from mst_delivery_details mdd
  , mst_delivery_assignments mda
  , mst_deliveries md
  where mdd.plan_id = l_plan_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 = mda.plan_id
  and md.delivery_id = mda.delivery_id
  and md.delivery_id in (select distinct mdl.delivery_id
                         from mst_delivery_legs mdl
			 , mst_trips mt
			 where mdl.plan_id = md.plan_id
			 and mt.plan_id = mdl.plan_id
			 and mt.trip_id = mdl.trip_id
			 and mt.carrier_id = l_carrier_id);

Line: 525


  select count(*)
  from mst_trips mt
  where mt.plan_id = l_plan_id
  and mt.mode_of_transport = l_mode;

Line: 533


  select count(*)
  from mst_trips mt
  , fte_location_parameters flp
  where mt.plan_id = l_plan_id
  and mt.mode_of_transport = l_mode
  and flp.location_id in (select mts.stop_location_id
                          from mst_trip_stops mts
			  where mts.plan_id = mt.plan_id
			  and mts.trip_id = mt.trip_id)
  and flp.facility_id = l_myfac_id;

Line: 548


  select count(mt.trip_id)
  from mst_trips mt
  where mt.plan_id = l_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 decode(l_c_s_ident, 2, md.customer_id, md.supplier_id) = l_cust_supp_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 = l_mode
  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);

Line: 576


  select count(*)
  from mst_trips mt
  where mt.plan_id = l_plan_id
  and mt.carrier_id = l_carrier_id
  and mt.mode_of_transport = l_mode;

Line: 617


  select decode(l_mode,'TRUCK',nvl(mp.total_tl_cost,0)
                      ,'LTL'  ,nvl(mp.total_ltl_cost,0)
		              ,nvl(mp.total_parcel_cost,0))
  from mst_plans mp
  where mp.plan_id = l_plan_id;

Line: 627


  select nvl(sum(nvl(mdl.allocated_transport_cost,0)
               + nvl(mdl.allocated_fac_loading_cost,0)
               + nvl(mdl.allocated_fac_unloading_cost,0)),0)
  from mst_delivery_legs mdl
  where mdl.plan_id = l_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
		   , fte_location_parameters flp
                where mt.plan_id = mdl1.plan_id
                and   mt.trip_id  = mdl1.trip_id
                and   mt.mode_of_transport = l_mode
                and   mt.plan_id = mts.plan_id
                and   mt.trip_id = mts.trip_id
                and   mts.stop_location_id = flp.location_id
                and   flp.facility_id = l_myfac_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 = flp.location_id
                       or md.dropoff_location_id = flp.location_id));

Line: 653


  ( select nvl(sum(nvl(mdl.allocated_transport_cost,0)
               + nvl(mdl.allocated_fac_loading_cost,0)
               + nvl(mdl.allocated_fac_unloading_cost,0)),0)
  from mst_deliveries md
  , mst_delivery_legs mdl
  , mst_trips mt
  , fte_location_parameters flp
  where mt.plan_id = l_plan_id
  and mt.mode_of_transport = l_mode
  and mt.plan_id = mdl.plan_id
  and mt.trip_id = mdl.trip_id
  and mdl.plan_id = md.plan_id
  and mdl.delivery_id = md.delivery_id
  and flp.facility_id = l_myfac_id
  and ( md.pickup_location_id = flp.location_id
        or md.dropoff_location_id = flp.location_id ) );

Line: 676


  select nvl(sum(nvl(mdl.allocated_transport_cost,0)
               + nvl(mdl.allocated_fac_loading_cost,0)
               + nvl(mdl.allocated_fac_unloading_cost,0)),0)
  from mst_delivery_legs mdl
  where mdl.plan_id = l_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 = l_mode
                and   mt.plan_id = mts.plan_id
                and   mt.trip_id = mts.trip_id
                and   md.plan_id = mdl1.plan_id
                and   md.delivery_id  = mdl1.delivery_id
                and   md.plan_id = mdl.plan_id
                and   decode(l_c_s_ident, 2, md.customer_id, md.supplier_id) = l_cust_supp_id
                and   (md.pickup_location_id = mts.stop_location_id
		       or md.dropoff_location_id = mts.stop_location_id));

Line: 700


  ( select nvl(sum(nvl(mdl.allocated_transport_cost,0)
               + nvl(mdl.allocated_fac_loading_cost,0)
               + nvl(mdl.allocated_fac_unloading_cost,0)),0)
  from mst_deliveries md
  , mst_delivery_legs mdl
  , mst_trips mt
  where mt.plan_id = l_plan_id
  and mt.mode_of_transport = l_mode
  and mt.plan_id = mdl.plan_id
  and mt.trip_id = mdl.trip_id
  and mdl.plan_id = md.plan_id
  and mdl.delivery_id = md.delivery_id
--  and decode ( l_report_for, 2, md.customer_id, 4, md.supplier_id, 0 ) = decode ( l_report_for, 2, l_report_for_id, 4, l_report_for_id, 0 )
  and decode(l_c_s_ident, 2, md.customer_id, md.supplier_id) = l_cust_supp_id );

Line: 718


  select nvl(sum(decode(mt.mode_of_transport
                       , 'TRUCK', (nvl(mt.total_basic_transport_cost,0)
                                 + nvl(mt.total_stop_cost,0)
		                 + nvl(mt.total_load_unload_cost,0)
			         + nvl(mt.total_layover_cost,0)
			         + nvl(mt.total_accessorial_cost,0))
		       , (nvl(mt.total_basic_transport_cost,0)
		       + nvl(mt.total_accessorial_cost,0)))),0)
  from mst_trips mt
  where mt.plan_id = l_plan_id
  and mt.carrier_id = l_carrier_id
  and mt.mode_of_transport = l_mode;

Line: 766


  select nvl(sum(nvl(mdl.allocated_fac_shp_hand_cost,0)
               + nvl(mdl.allocated_fac_rec_hand_cost,0)),0)
  from mst_plans mp
  , mst_delivery_legs mdl
  where mp.plan_id = mdl.plan_id
  and mdl.trip_id in (select mt.trip_id
                      from mst_trips mt
		      where mt.plan_id = mp.plan_id
		      and   mt.mode_of_transport = l_mode)
  and mdl.plan_id = l_plan_id;

Line: 781


  select nvl(sum(nvl(mdl.allocated_fac_shp_hand_cost,0)
               + nvl(mdl.allocated_fac_rec_hand_cost,0)),0)
  from mst_delivery_legs mdl
  where mdl.plan_id = l_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
		   , fte_location_parameters flp
                where mt.plan_id = mdl1.plan_id
                and   mt.trip_id  = mdl1.trip_id
                and   mt.mode_of_transport = l_mode
                and   mt.plan_id = mts.plan_id
                and   mt.trip_id = mts.trip_id
                and   mts.stop_location_id = flp.location_id
                and   flp.facility_id = l_myfac_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 = flp.location_id
                       or md.dropoff_location_id = flp.location_id));

Line: 806


  ( select nvl(sum(nvl(mdl.allocated_fac_shp_hand_cost,0)
               + nvl(mdl.allocated_fac_rec_hand_cost,0)),0)
  from mst_deliveries md
  , mst_delivery_legs mdl
  , mst_trips mt
  , fte_location_parameters flp
  where mt.plan_id = l_plan_id
  and mt.mode_of_transport = l_mode
  and mt.plan_id = mdl.plan_id
  and mt.trip_id = mdl.trip_id
  and mdl.plan_id = md.plan_id
  and mdl.delivery_id = md.delivery_id
  and flp.facility_id = l_myfac_id
  and ( md.pickup_location_id = flp.location_id
        or md.dropoff_location_id = flp.location_id ) );

Line: 827


  select nvl(sum(nvl(mdl.allocated_fac_shp_hand_cost,0)
               + nvl(mdl.allocated_fac_rec_hand_cost,0)),0)
  from mst_delivery_legs mdl
  where mdl.plan_id = l_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 = l_mode
                and   mt.plan_id = mts.plan_id
                and   mt.trip_id = mts.trip_id
                and   md.plan_id = mdl1.plan_id
                and   md.delivery_id  = mdl1.delivery_id
                and   md.plan_id = mdl.plan_id
                and   decode(l_c_s_ident, 2, md.customer_id, md.supplier_id) = l_cust_supp_id
                and   (md.pickup_location_id = mts.stop_location_id
		       or md.dropoff_location_id = mts.stop_location_id));

Line: 850


  (select nvl(sum(nvl(mdl.allocated_fac_shp_hand_cost,0)
               + nvl(mdl.allocated_fac_rec_hand_cost,0)),0)
  from mst_deliveries md
  , mst_delivery_legs mdl
  , mst_trips mt
  where mt.plan_id = l_plan_id
  and mt.mode_of_transport = l_mode
  and mt.plan_id = mdl.plan_id
  and mt.trip_id = mdl.trip_id
  and mdl.plan_id = md.plan_id
  and mdl.delivery_id = md.delivery_id
--  and decode ( l_report_for, 2, md.customer_id, 4, md.supplier_id, 0 ) = decode ( l_report_for, 2, l_report_for_id, 4, l_report_for_id, 0 )
  and decode(l_c_s_ident, 2, md.customer_id, md.supplier_id) = l_cust_supp_id );

Line: 868


  select nvl(sum(decode(mt.mode_of_transport
                       , 'TRUCK', (nvl(mt.total_handling_cost,0))
		       , (nvl(mt.total_handling_cost,0)))),0)
  from mst_trips mt
  where mt.plan_id = l_plan_id
  and mt.carrier_id = l_carrier_id
  and mt.mode_of_transport = l_mode;

Line: 911


  select nvl(sum(nvl(mdl.allocated_fac_loading_cost,0)
               + nvl(mdl.allocated_fac_unloading_cost,0)
	       + nvl(mdl.allocated_transport_cost,0)
	       + nvl(mdl.allocated_fac_shp_hand_cost,0)
               + nvl(mdl.allocated_fac_rec_hand_cost,0)),0)
  from mst_plans mp
  , mst_delivery_legs mdl
  where mp.plan_id = mdl.plan_id
  and mdl.trip_id in (select mt.trip_id
                      from mst_trips mt
		      where mt.plan_id = mp.plan_id
		      and   mt.mode_of_transport = l_mode)
  and mdl.plan_id = l_plan_id;

Line: 929


  select nvl(sum(nvl(mdl.allocated_fac_loading_cost,0)
               + nvl(mdl.allocated_fac_unloading_cost,0)
	       + nvl(mdl.allocated_transport_cost,0)
	       + nvl(mdl.allocated_fac_shp_hand_cost,0)
               + nvl(mdl.allocated_fac_rec_hand_cost,0)),0)
  from mst_delivery_legs mdl
  where mdl.plan_id = l_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
		   , fte_location_parameters flp
                where mt.plan_id = mdl1.plan_id
                and   mt.trip_id  = mdl1.trip_id
                and   mt.mode_of_transport = l_mode
                and   mt.plan_id = mts.plan_id
                and   mt.trip_id = mts.trip_id
                and   mts.stop_location_id = flp.location_id
                and   flp.facility_id = l_myfac_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 = flp.location_id
                       or md.dropoff_location_id = flp.location_id));

Line: 957


  ( select nvl(sum(nvl(mdl.allocated_fac_loading_cost,0)
               + nvl(mdl.allocated_fac_unloading_cost,0)
	       + nvl(mdl.allocated_transport_cost,0)
	       + nvl(mdl.allocated_fac_shp_hand_cost,0)
               + nvl(mdl.allocated_fac_rec_hand_cost,0)),0)
  from mst_deliveries md
  , mst_delivery_legs mdl
  , mst_trips mt
  , fte_location_parameters flp
  where mt.plan_id = l_plan_id
  and mt.mode_of_transport = l_mode
  and mt.plan_id = mdl.plan_id
  and mt.trip_id = mdl.trip_id
  and mdl.plan_id = md.plan_id
  and mdl.delivery_id = md.delivery_id
  and flp.facility_id = l_myfac_id
  and ( md.pickup_location_id = flp.location_id
        or md.dropoff_location_id = flp.location_id ) );

Line: 981


  select nvl(sum(nvl(mdl.allocated_fac_loading_cost,0)
               + nvl(mdl.allocated_fac_unloading_cost,0)
	       + nvl(mdl.allocated_transport_cost,0)
	       + nvl(mdl.allocated_fac_shp_hand_cost,0)
               + nvl(mdl.allocated_fac_rec_hand_cost,0)),0)
  from mst_delivery_legs mdl
  where mdl.plan_id = l_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 = l_mode
                and   mt.plan_id = mts.plan_id
                and   mt.trip_id = mts.trip_id
                and   md.plan_id = mdl1.plan_id
                and   md.delivery_id  = mdl1.delivery_id
                and   md.plan_id = mdl.plan_id
                and   decode(l_c_s_ident, 2, md.customer_id, md.supplier_id) = l_cust_supp_id
                and   (md.pickup_location_id = mts.stop_location_id
		       or md.dropoff_location_id = mts.stop_location_id));

Line: 1007


  (select nvl(sum(nvl(mdl.allocated_fac_loading_cost,0)
               + nvl(mdl.allocated_fac_unloading_cost,0)
	       + nvl(mdl.allocated_transport_cost,0)
	       + nvl(mdl.allocated_fac_shp_hand_cost,0)
               + nvl(mdl.allocated_fac_rec_hand_cost,0)),0)
  from mst_deliveries md
  , mst_delivery_legs mdl
  , mst_trips mt
  where mt.plan_id = l_plan_id
  and mt.mode_of_transport = l_mode
  and mt.plan_id = mdl.plan_id
  and mt.trip_id = mdl.trip_id
  and mdl.plan_id = md.plan_id
  and mdl.delivery_id = md.delivery_id
--  and decode ( l_report_for, 2, md.customer_id, 4, md.supplier_id, 0 ) = decode ( l_report_for, 2, l_report_for_id, 4, l_report_for_id, 0 )
  and decode(l_c_s_ident, 2, md.customer_id, md.supplier_id) = l_cust_supp_id );

Line: 1027


  select nvl(sum(decode(mt.mode_of_transport
                       , 'TRUCK', (nvl(mt.total_basic_transport_cost,0)
                                 + nvl(mt.total_stop_cost,0)
		                 + nvl(mt.total_load_unload_cost,0)
			         + nvl(mt.total_layover_cost,0)
			         + nvl(mt.total_accessorial_cost,0)
			         + nvl(mt.total_handling_cost,0))
		       , (nvl(mt.total_basic_transport_cost,0)
		        + nvl(mt.total_accessorial_cost,0)))),0)
  from mst_trips mt
  where mt.plan_id = l_plan_id
  and mt.carrier_id = l_carrier_id
  and mt.mode_of_transport = l_mode;

Line: 1078


  select count(*)
  from mst_trip_stops mts
  , mst_trips mt
  where mt.plan_id = mts.plan_id
  and mt.trip_id = mts.trip_id
  and mt.mode_of_transport = 'TRUCK'
  and mts.plan_id = l_plan_id;

Line: 1089


  select count(*)
  from mst_trip_stops mts
  , mst_trips mt
  , fte_location_parameters flp
  where mt.plan_id = mts.plan_id
  and mt.trip_id = mts.trip_id
  and mt.mode_of_transport = 'TRUCK'
  and flp.location_id = mts.stop_location_id
  and flp.facility_id = l_myfac_id
  and mts.plan_id = l_plan_id;

Line: 1102


  select count(*)
  from mst_trip_stops mts
  where mts.plan_id = l_plan_id
  and mts.trip_id in ( select distinct mt.trip_id
                    from mst_trips mt
                    , mst_trip_stops mts1
                    , fte_location_parameters flp
                    where mt.plan_id = mts.plan_id
                    and mt.trip_id = mts.trip_id
                    and mt.mode_of_transport = 'TRUCK'
                    and mts1.plan_id = mt.plan_id
                    and mts1.trip_id = mt.trip_id
                    and flp.facility_id = l_myfac_id
                    and flp.location_id = mts1.stop_location_id );

Line: 1118


  select count ( * )
  from mst_trips mt
  , mst_trip_stops mts
  where mts.plan_id = l_plan_id
  and mts.trip_id = mt.trip_id
  and mt.plan_id= mts.plan_id
  and mt.mode_of_transport = 'TRUCK'
  and mt.trip_id in ( select mdl.trip_id
                      from mst_deliveries md
					  , mst_delivery_legs mdl
					  , fte_location_parameters flp
					  where md.plan_id = l_plan_id
					  and flp.facility_id = l_myfac_id
					  and ( md.dropoff_location_id = flp.location_id
					        or md.pickup_location_id = flp.location_id )
					  and mdl.plan_id = md.plan_id
					  and mdl.delivery_id = md.delivery_id );

Line: 1139


  select count(*)
  from mst_trip_stops mts
  , mst_trips mt
  where mt.plan_id = mts.plan_id
  and mt.trip_id = mts.trip_id
  and mt.mode_of_transport = 'TRUCK'
  and mts.plan_id = l_plan_id
  and mt.trip_id in (select distinct 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 decode(l_c_s_ident, 2, md.customer_id, md.supplier_id) = l_cust_supp_id);

Line: 1156


  select count(*)
  from mst_trip_stops mts
  , mst_trips mt
  where mt.plan_id = mts.plan_id
  and mt.trip_id = mts.trip_id
  and mt.mode_of_transport = 'TRUCK'
  and mt.carrier_id = l_carrier_id
  and mts.plan_id = l_plan_id;

Line: 1166


  select count(*)
  from mst_trips mt
  where mt.plan_id = l_plan_id
  and mt.mode_of_transport = 'TRUCK';

Line: 1174


  select count(*)
  from mst_trips mt
  where mt.plan_id = l_plan_id
  and mt.mode_of_transport = 'TRUCK'
  and mt.trip_id in (select distinct mts.trip_id
                     from mst_trip_stops mts
		     , fte_location_parameters flp
		     where mts.plan_id = mt.plan_id
		     and mts.stop_location_id = flp.location_id
		     and flp.facility_id = l_myfac_id);

Line: 1186


  select count ( * )
  from mst_trips mt
  where mt.plan_id= l_plan_id
  and mt.mode_of_transport = 'TRUCK'
  and mt.trip_id in ( select mdl.trip_id
                      from mst_deliveries md
					  , mst_delivery_legs mdl
					  , fte_location_parameters flp
					  where md.plan_id = l_plan_id
					  and flp.facility_id = l_myfac_id
					  and ( md.dropoff_location_id = flp.location_id
					        or md.pickup_location_id = flp.location_id )
					  and mdl.plan_id = md.plan_id
					  and mdl.delivery_id = md.delivery_id );

Line: 1205


  select count(*)
  from mst_trips mt
  where mt.plan_id = l_plan_id
  and mt.mode_of_transport = 'TRUCK'
  and mt.trip_id in (select distinct mdl.trip_id
                     from mst_trip_stops mts
		     , mst_delivery_legs mdl
		     , mst_deliveries md
                    where md.plan_id = mt.plan_id
                    and decode(l_c_s_ident, 2, md.customer_id, md.supplier_id) = l_cust_supp_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   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);

Line: 1230


  select count ( * )
  from mst_trips mt
  where mt.plan_id= l_plan_id
  and mt.mode_of_transport = 'TRUCK'
  and mt.trip_id in ( select mdl.trip_id
                      from mst_deliveries md
					  , mst_delivery_legs mdl
					  where md.plan_id = l_plan_id
					  and mdl.plan_id = md.plan_id
					  and mdl.delivery_id = md.delivery_id
					  and decode ( l_c_s_ident, 2, md.customer_id, md.supplier_id ) = l_cust_supp_id );

Line: 1244


  select count(*)
  from mst_trips mt
  where mt.plan_id = l_plan_id
  and mt.mode_of_transport = 'TRUCK'
  and mt.carrier_id = l_carrier_id;

Line: 1319


  select nvl(sum(mt.total_trip_distance),0)
  from mst_trips mt
  where mt.plan_id= l_plan_id
  and mt.mode_of_transport = 'TRUCK';

Line: 1327


  select nvl(sum(mt.total_trip_distance),0)
  from mst_trips mt
  where mt.plan_id= l_plan_id
  and mt.mode_of_transport = 'TRUCK'
  and mt.trip_id in (select distinct mts.trip_id
                     from mst_trip_stops mts
		     , fte_location_parameters flp
		     where mts.plan_id = mt.plan_id
		     and mts.stop_location_id = flp.location_id
		     and flp.facility_id = l_myfac_id);

Line: 1339


  select nvl(sum(nvl(mt.total_trip_distance,0)),0)
  from mst_trips mt
  where mt.plan_id= l_plan_id
  and mt.mode_of_transport = 'TRUCK'
  and mt.trip_id in ( select mdl.trip_id
                      from mst_deliveries md
					  , mst_delivery_legs mdl
					  , fte_location_parameters flp
					  where md.plan_id = l_plan_id
					  and flp.facility_id = l_myfac_id
					  and ( md.dropoff_location_id = flp.location_id
					        or md.pickup_location_id = flp.location_id )
					  and mdl.plan_id = md.plan_id
					  and mdl.delivery_id = md.delivery_id );

Line: 1359


  select nvl(sum(mt.total_trip_distance),0)
  from mst_trips mt
  where mt.plan_id = l_plan_id
  and mt.mode_of_transport = 'TRUCK'
  and mt.trip_id in (select distinct mdl.trip_id
                     from mst_trip_stops mts
		     , mst_delivery_legs mdl
		     , mst_deliveries md
                    where md.plan_id = mt.plan_id
                    and decode(l_c_s_ident, 2, md.customer_id, md.supplier_id) = l_cust_supp_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   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);

Line: 1384


  select nvl(sum(nvl(mt.total_trip_distance,0)),0)
  from mst_trips mt
  where mt.plan_id= l_plan_id
  and mt.mode_of_transport = 'TRUCK'
  and mt.trip_id in ( select mdl.trip_id
                      from mst_deliveries md
					  , mst_delivery_legs mdl
					  where md.plan_id = l_plan_id
					  and mdl.plan_id = md.plan_id
					  and mdl.delivery_id = md.delivery_id
					  and decode ( l_c_s_ident, 2, md.customer_id, md.supplier_id ) = l_cust_supp_id );

Line: 1398


  select nvl(sum(mt.total_trip_distance),0)
  from mst_trips mt
  where mt.plan_id= l_plan_id
  and mt.mode_of_transport = 'TRUCK'
  and mt.carrier_id = l_carrier_id;

Line: 1439


  select count(*)
  from mst_trips mt
  where mt.plan_id = l_plan_id
  and mt.carrier_id = l_carrier_id;

Line: 1448


  select count(*)
  from mst_trips mt
  , mst_trip_stops mts
  , fte_location_parameters flp
  where mt.plan_id = l_plan_id
  and mt.carrier_id = l_carrier_id
  and mts.plan_id = mt.plan_id
  and mts.trip_id = mt.trip_id
  and mts.stop_location_id = flp.location_id
  and flp.facility_id = l_myfac_id;

Line: 1460


  select count ( distinct mt.trip_id )
  from mst_deliveries md
  , fte_location_parameters flp
  , mst_delivery_legs mdl
  , mst_trips mt
  where md.plan_id = l_plan_id
  and flp.facility_id = l_myfac_id
  and ( md.dropoff_location_id = flp.location_id
        or md.pickup_location_id = flp.location_id )
  and mdl.plan_id = md.plan_id
  and mdl.delivery_id = md.delivery_id
  and mt.plan_id = mdl.plan_id
  and mt.trip_id = mdl.trip_id
  and mt.carrier_id = l_carrier_id;

Line: 1480


  select count(mt.trip_id)
  from mst_trips mt
  where mt.plan_id = l_plan_id
  and   mt.carrier_id = l_carrier_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 decode(l_c_s_ident, 2, md.customer_id, md.supplier_id) = l_cust_supp_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   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);

Line: 1506


  select count ( distinct mt.trip_id )
  from mst_deliveries md
  , mst_delivery_legs mdl
  , mst_trips mt
  where md.plan_id = l_plan_id
  and decode ( l_c_s_ident, 2, md.customer_id, md.supplier_id ) = l_cust_supp_id
  and mdl.plan_id = md.plan_id
  and mdl.delivery_id = md.delivery_id
  and mt.plan_id = mdl.plan_id
  and mt.trip_id = mdl.trip_id
  and mt.carrier_id = l_carrier_id;

Line: 1549


  select nvl(sum(nvl(mdl.allocated_transport_cost,0)),0)
  from mst_plans mp
  , mst_delivery_legs mdl
  where mp.plan_id = mdl.plan_id
  and mdl.trip_id in (select mt.trip_id
                      from mst_trips mt
		      where mt.plan_id = mp.plan_id
		      and   mt.carrier_id = l_carrier_id)
  and mdl.plan_id = l_plan_id;

Line: 1563


  select nvl(sum(nvl(mdl.allocated_transport_cost,0)),0)
  from mst_trips mt
     , mst_trip_stops mts
     , mst_delivery_legs mdl
     , fte_location_parameters flp
  where mt.plan_id = l_plan_id
  and   mt.plan_id = mts.plan_id
  and   mt.trip_id = mts.trip_id
  and   mt.carrier_id = l_carrier_id
  and   mts.plan_id = mdl.plan_id
--  and   mdl.pick_up_stop_id = mts.stop_id
-- Bug_Fix for 3694008
  and ( mdl.pick_up_stop_id = mts.stop_id
      or mdl.drop_off_stop_id = mts.stop_id )
  and   mts.stop_location_id = flp.location_id
  and   flp.facility_id = l_myfac_id;

Line: 1581


  select sum ( nvl ( mdl.allocated_transport_cost, 0 ) )
  from mst_deliveries md
  , fte_location_parameters flp
  , mst_delivery_legs mdl
  , mst_trips mt
  where md.plan_id = l_plan_id
  and flp.facility_id = l_myfac_id
  and ( md.dropoff_location_id = flp.location_id
        or md.pickup_location_id = flp.location_id )
  and mdl.plan_id = md.plan_id
  and mdl.delivery_id = md.delivery_id
  and mt.plan_id = mdl.plan_id
  and mt.trip_id = mdl.trip_id
  and mt.carrier_id = l_carrier_id;

Line: 1601


  select nvl(sum(nvl(mdl.allocated_transport_cost,0)),0)
  from  mst_deliveries md
  , mst_delivery_legs mdl
  , mst_trips mt
  where md.plan_id = l_plan_id
  and decode(l_c_s_ident, 2, md.customer_id, md.supplier_id) = l_cust_supp_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.carrier_id = l_carrier_id;

Line: 1614


  select sum ( nvl ( mdl.allocated_transport_cost, 0 ) )
  from mst_deliveries md
  , mst_delivery_legs mdl
  , mst_trips mt
  where md.plan_id = l_plan_id
  and decode ( l_c_s_ident, 2, md.customer_id, md.supplier_id ) = l_cust_supp_id
  and mdl.plan_id = md.plan_id
  and mdl.delivery_id = md.delivery_id
  and mt.plan_id = mdl.plan_id
  and mt.trip_id = mdl.trip_id
  and mt.carrier_id = l_carrier_id;

Line: 1660


  select count(distinct mdd.source_header_number)
  from mst_delivery_details mdd
  , mst_delivery_assignments mda
  , mst_deliveries md
  , wsh_locations wl
  where md.plan_id = mda.plan_id
  and   md.delivery_id = mda.delivery_id
  and   md.pickup_location_id = wl.wsh_location_id
  and   wl.state = l_orig_state
  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 = l_plan_id;

Line: 1679


  select count(distinct mdd.source_header_number)
  from mst_delivery_details mdd
  , mst_delivery_assignments mda
  , mst_deliveries md
  , fte_location_parameters flp
  , wsh_locations wl
  where md.plan_id = mda.plan_id
  and   md.delivery_id = mda.delivery_id
  and   md.pickup_location_id = wl.wsh_location_id
  and   wl.state = l_orig_state
  and   flp.location_id = md.pickup_location_id
  and   flp.facility_id = l_myfac_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 = l_plan_id;

Line: 1697


  select count(distinct mdd.source_header_number)
  from mst_delivery_assignments mda
  , mst_delivery_details mdd
  where mdd.plan_id = l_plan_id
  and mdd.plan_id = mda.plan_id
  and mdd.delivery_detail_id = mda.delivery_detail_id
  and mda.delivery_id in (select mdl.delivery_id
                         from mst_deliveries md
                         , mst_delivery_legs mdl
                         , mst_trip_stops mts
                         , fte_location_parameters flp
                         , wsh_locations wl
                         where mdl.plan_id = l_plan_id
                         and mdl.plan_id = mts.plan_id
                         and mdl.trip_id = mts.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 = flp.location_id
                         and flp.facility_id = l_myfac_id
                         and mdl.plan_id = md.plan_id
                         and mdl.delivery_id = md.delivery_id
                         and md.pickup_location_id = wl.wsh_location_id
                         and wl.state = l_orig_state);

Line: 1726


  select count(distinct mdd.source_header_number)
  from mst_delivery_details mdd
  , mst_delivery_assignments mda
  , mst_deliveries md
  , wsh_locations wl
  where md.plan_id = mda.plan_id
  and   md.delivery_id = mda.delivery_id
  and   md.pickup_location_id = wl.wsh_location_id
  and   wl.state = l_orig_state
  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 = l_plan_id
  and   decode(l_c_s_ident, 2, md.customer_id, md.supplier_id) = l_cust_supp_id;

Line: 1745


  select count(distinct 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_trips t
         , mst_trip_stops ts
         , mst_delivery_legs mdl
         , wsh_locations wl
         where mdl.plan_id = md.plan_id
         and ts.plan_id  = mdl.plan_id
         and ts.stop_id  = mdl.pick_up_stop_id
	 and wl.wsh_location_id = md.pickup_location_id
         and ts.stop_location_id = wl.wsh_location_id
	 and wl.state = l_orig_state
         and ts.plan_id  = t.plan_id
         and ts.trip_id  = t.trip_id
	 and t.carrier_id = l_carrier_id)
  and   mda.plan_id = mdd.plan_id
  and   mda.delivery_detail_id = mdd.delivery_detail_id
  and   md.plan_id = l_plan_id
  and   mdd.container_flag = 2;

Line: 1806


  select nvl(sum(nvl(md.gross_weight,0)),0)
  from mst_deliveries md
  , wsh_locations wl
  where md.plan_id = l_plan_id
  and md.pickup_location_id = wl.wsh_location_id
  and wl.state = l_orig_state;

Line: 1817


  select nvl(sum(nvl(md.gross_weight,0)),0)
  from mst_deliveries md
  , fte_location_parameters flp
  , wsh_locations wl
  where md.plan_id = l_plan_id
  and md.pickup_location_id = wl.wsh_location_id
  and wl.state = l_orig_state
  and flp.location_id = md.pickup_location_id
  and flp.facility_id = l_myfac_id;

Line: 1828


  select nvl(sum(nvl(md.gross_weight,0)),0)
  from mst_deliveries md
  where md.plan_id = l_plan_id
  and md.delivery_id in (select mdl.delivery_id
                         from mst_deliveries md
                         , mst_delivery_legs mdl
                         , mst_trip_stops mts
                         , fte_location_parameters flp
                         , wsh_locations wl
                         where mdl.plan_id = l_plan_id
                         and mdl.plan_id = mts.plan_id
                         and mdl.trip_id = mts.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 = flp.location_id
                         and flp.facility_id = l_myfac_id
                         and mdl.plan_id = md.plan_id
                         and mdl.delivery_id = md.delivery_id
                         and md.pickup_location_id = wl.wsh_location_id
                         and wl.state = l_orig_state);

Line: 1853


  select nvl(sum(nvl(md.gross_weight,0)),0)
  from mst_deliveries md
  , wsh_locations wl
  where md.plan_id = l_plan_id
  and md.pickup_location_id = wl.wsh_location_id
  and wl.state = l_orig_state
  and decode(l_c_s_ident, 2, md.customer_id, md.supplier_id) = l_cust_supp_id;

Line: 1864


  select nvl(sum(nvl(md.gross_weight,0)),0)
  from mst_deliveries md
  , wsh_locations wl
  where md.plan_id = l_plan_id
  and md.pickup_location_id = wl.wsh_location_id
  and wl.state = l_orig_state
  and md.delivery_id in (select distinct mdl.delivery_id
                         from mst_delivery_legs mdl
			 , mst_trips mt
			 where mdl.plan_id = md.plan_id
			 and mt.plan_id = mdl.plan_id
			 and mt.trip_id = mdl.trip_id
			 and mt.carrier_id = l_carrier_id);

Line: 1912


  select nvl(sum(nvl(md.volume,0)),0)
  from mst_deliveries md
  , wsh_locations wl
  where md.plan_id = l_plan_id
  and md.pickup_location_id = wl.wsh_location_id
  and wl.state = l_orig_state;

Line: 1923


  select nvl(sum(nvl(md.volume,0)),0)
  from mst_deliveries md
  , fte_location_parameters flp
  , wsh_locations wl
  where md.plan_id = l_plan_id
  and md.pickup_location_id = wl.wsh_location_id
  and wl.state = l_orig_state
  and flp.location_id = md.pickup_location_id
  and flp.facility_id = l_myfac_id;

Line: 1934


  select nvl(sum(nvl(md.volume,0)),0)
  from mst_deliveries md
  where md.plan_id = l_plan_id
  and md.delivery_id in (select mdl.delivery_id
                         from mst_deliveries md
                         , mst_delivery_legs mdl
                         , mst_trip_stops mts
                         , fte_location_parameters flp
                         , wsh_locations wl
                         where mdl.plan_id = l_plan_id
                         and mdl.plan_id = mts.plan_id
                         and mdl.trip_id = mts.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 = flp.location_id
                         and flp.facility_id = l_myfac_id
                         and mdl.plan_id = md.plan_id
                         and mdl.delivery_id = md.delivery_id
                         and md.pickup_location_id = wl.wsh_location_id
                         and wl.state = l_orig_state);

Line: 1959


  select nvl(sum(nvl(md.volume,0)),0)
  from mst_deliveries md
  , wsh_locations wl
  where md.plan_id = l_plan_id
  and md.pickup_location_id = wl.wsh_location_id
  and wl.state = l_orig_state
  and decode(l_c_s_ident, 2, md.customer_id, md.supplier_id) = l_cust_supp_id;

Line: 1970


  select nvl(sum(nvl(md.volume,0)),0)
  from mst_deliveries md
  , wsh_locations wl
  where md.plan_id = l_plan_id
  and md.pickup_location_id = wl.wsh_location_id
  and wl.state = l_orig_state
  and md.delivery_id in (select distinct mdl.delivery_id
                         from mst_delivery_legs mdl
			 , mst_trips mt
			 where mdl.plan_id = md.plan_id
			 and mt.plan_id = mdl.plan_id
			 and mt.trip_id = mdl.trip_id
			 and mt.carrier_id = l_carrier_id);

Line: 2018


  select nvl(sum(nvl(md.number_of_pieces,0)),0)
  from mst_deliveries md
  , wsh_locations wl
  where md.plan_id = l_plan_id
  and md.pickup_location_id = wl.wsh_location_id
  and wl.state = l_orig_state;

Line: 2029


  select nvl(sum(nvl(md.number_of_pieces,0)),0)
  from mst_deliveries md
  , fte_location_parameters flp
  , wsh_locations wl
  where md.plan_id = l_plan_id
  and md.pickup_location_id = wl.wsh_location_id
  and wl.state = l_orig_state
  and flp.location_id = md.pickup_location_id
  and flp.facility_id = l_myfac_id;

Line: 2040


  select nvl(sum(nvl(md.number_of_pieces,0)),0)
  from mst_deliveries md
  where md.plan_id = l_plan_id
  and md.delivery_id in (select mdl.delivery_id
                         from mst_deliveries md
                         , mst_delivery_legs mdl
                         , mst_trip_stops mts
                         , fte_location_parameters flp
                         , wsh_locations wl
                         where mdl.plan_id = l_plan_id
                         and mdl.plan_id = mts.plan_id
                         and mdl.trip_id = mts.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 = flp.location_id
                         and flp.facility_id = l_myfac_id
                         and mdl.plan_id = md.plan_id
                         and mdl.delivery_id = md.delivery_id
                         and md.pickup_location_id = wl.wsh_location_id
                         and wl.state = l_orig_state);

Line: 2065


  select nvl(sum(nvl(md.number_of_pieces,0)),0)
  from mst_deliveries md
  , wsh_locations wl
  where md.plan_id = l_plan_id
  and md.pickup_location_id = wl.wsh_location_id
  and wl.state = l_orig_state
  and decode(l_c_s_ident, 2, md.customer_id, md.supplier_id) = l_cust_supp_id;

Line: 2076


  select nvl(sum(nvl(md.number_of_pieces,0)),0)
  from mst_deliveries md
  , wsh_locations wl
  where md.plan_id = l_plan_id
  and md.pickup_location_id = wl.wsh_location_id
  and wl.state = l_orig_state
  and md.delivery_id in (select distinct mdl.delivery_id
                         from mst_delivery_legs mdl
			 , mst_trips mt
			 where mdl.plan_id = md.plan_id
			 and mt.plan_id = mdl.plan_id
			 and mt.trip_id = mdl.trip_id
			 and mt.carrier_id = l_carrier_id);

Line: 2124


  select count(*)
  from (select distinct mt.trip_id, count(*) num_stops
        from mst_trips mt
        , mst_trip_stops mts
	where mt.plan_id = l_plan_id
	and mt.trip_id in (select distinct mdl.trip_id
	                   from mst_deliveries md
			   , mst_delivery_legs mdl
			   , mst_trip_stops mts1
			   , wsh_locations wl
			   where md.plan_id = mt.plan_id
			   and mdl.plan_id = md.plan_id
			   and mdl.delivery_id = md.delivery_id
                           and mts1.plan_id = mdl.plan_id
			   and mts1.trip_id = mdl.trip_id
			   and mts1.stop_id = mdl.pick_up_stop_id
			   and mts1.stop_location_id = wl.wsh_location_id
			   and mts1.stop_location_id = md.pickup_location_id
			   and wl.state = l_orig_state)
	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;

Line: 2152


  select count(*)
  from (select distinct mt.trip_id, count(*) num_stops
        from mst_trips mt
        , mst_trip_stops mts
	where mt.plan_id = l_plan_id
	and mt.trip_id in (select distinct mdl.trip_id
	                   from mst_deliveries md
			   , mst_delivery_legs mdl
			   , mst_trip_stops mts1
                           , fte_location_parameters flp
			   , wsh_locations wl
			   where md.plan_id = mt.plan_id
			   and mdl.plan_id = md.plan_id
			   and mdl.delivery_id = md.delivery_id
                           and mts1.plan_id = mdl.plan_id
			   and mts1.trip_id = mdl.trip_id
			   and mts1.stop_id = mdl.pick_up_stop_id
			   and mts1.stop_location_id = wl.wsh_location_id
			   and mts1.stop_location_id = md.pickup_location_id
			   and wl.state = l_orig_state
                           and flp.location_id = mts1.stop_location_id
                           and flp.facility_id = l_myfac_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;

Line: 2184


  select count(*)
  from (select distinct mt.trip_id, count(*) num_stops
        from mst_trips mt
        , mst_trip_stops mts
	where mt.plan_id = l_plan_id
	and mt.mode_of_transport = 'TRUCK'
	and mts.plan_id = mt.plan_id
	and mts.trip_id = mt.trip_id
	and mt.trip_id in
	          (select distinct mdl.trip_id
	           from mst_deliveries md
		   , mst_delivery_legs mdl
		   , mst_trip_stops mts1
		   , wsh_locations wl
		   where mdl.plan_id = mt.plan_id
		   and mdl.pick_up_stop_id = mts1.stop_id
		   and mdl.trip_id = mts1.trip_id
		   and mts1.plan_id = mt.plan_id
		   and mts1.stop_location_id = wl.wsh_location_id
		   and mts1.stop_location_id = md.pickup_location_id
		   and wl.state = l_orig_state
		   and md.plan_id = mdl.plan_id
		   and md.delivery_id = mdl.delivery_id
                   and decode(l_c_s_ident, 2, md.customer_id, md.supplier_id) = l_cust_supp_id)
	group by mt.trip_id) temp
  where temp.num_stops > 2;

Line: 2214


  select count(*)
  from (select distinct mt.trip_id, count(*) num_stops
        from mst_trips mt
        , mst_trip_stops mts
	where mt.plan_id = l_plan_id
	and mt.carrier_id = l_carrier_id
	and mt.trip_id in (select distinct mdl.trip_id
	                   from mst_deliveries md
			   , mst_delivery_legs mdl
			   , mst_trip_stops mts1
			   , wsh_locations wl
			   where md.plan_id = mt.plan_id
			   and mdl.plan_id = md.plan_id
			   and mdl.delivery_id = md.delivery_id
                           and mts1.plan_id = mdl.plan_id
			   and mts1.trip_id = mdl.trip_id
			   and mts1.stop_id = mdl.pick_up_stop_id
			   and mts1.stop_location_id = wl.wsh_location_id
			   and mts1.stop_location_id = md.pickup_location_id
			   and wl.state = l_orig_state)
	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;

Line: 2275


  select count(*)
  from (select distinct mt.trip_id, count(*) num_stops
        from mst_trips mt
        , mst_trip_stops mts
	where mt.plan_id = l_plan_id
	and mt.trip_id in (select distinct mdl.trip_id
	                   from mst_deliveries md
			   , mst_delivery_legs mdl
			   , mst_trip_stops mts1
			   , wsh_locations wl
			   where md.plan_id = mt.plan_id
			   and mdl.plan_id = md.plan_id
			   and mdl.delivery_id = md.delivery_id
                           and mts1.plan_id = mdl.plan_id
			   and mts1.trip_id = mdl.trip_id
			   and mts1.stop_id = mdl.pick_up_stop_id
			   and mts1.stop_location_id = wl.wsh_location_id
			   and mts1.stop_location_id = md.pickup_location_id
			   and wl.state = l_orig_state)
	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;

Line: 2303


  select count(*)
  from (select distinct mt.trip_id, count(*) num_stops
        from mst_trips mt
        , mst_trip_stops mts
	where mt.plan_id = l_plan_id
	and mt.trip_id in (select distinct mdl.trip_id
	                   from mst_deliveries md
			   , mst_delivery_legs mdl
			   , mst_trip_stops mts1
                           , fte_location_parameters flp
			   , wsh_locations wl
			   where md.plan_id = mt.plan_id
			   and mdl.plan_id = md.plan_id
			   and mdl.delivery_id = md.delivery_id
                           and mts1.plan_id = mdl.plan_id
			   and mts1.trip_id = mdl.trip_id
			   and mts1.stop_id = mdl.pick_up_stop_id
			   and mts1.stop_location_id = wl.wsh_location_id
			   and mts1.stop_location_id = md.pickup_location_id
			   and wl.state = l_orig_state
                           and flp.location_id = mts1.stop_location_id
                           and flp.facility_id = l_myfac_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;

Line: 2335


  select count(*)
  from (select distinct mt.trip_id, count(*) num_stops
        from mst_trips mt
        , mst_trip_stops mts
	where mt.plan_id = l_plan_id
	and mt.mode_of_transport = 'TRUCK'
	and mts.plan_id = mt.plan_id
	and mts.trip_id = mt.trip_id
	and mt.trip_id in
	          (select distinct mdl.trip_id
	           from mst_deliveries md
		   , mst_delivery_legs mdl
		   , mst_trip_stops mts1
		   , wsh_locations wl
		   where mdl.plan_id = mt.plan_id
		   and mdl.pick_up_stop_id = mts1.stop_id
		   and mdl.trip_id = mts1.trip_id
		   and mts1.plan_id = mt.plan_id
		   and mts1.stop_location_id = wl.wsh_location_id
		   and mts1.stop_location_id = md.pickup_location_id
		   and wl.state = l_orig_state
		   and md.plan_id = mdl.plan_id
		   and md.delivery_id = mdl.delivery_id
                   and decode(l_c_s_ident, 2, md.customer_id, md.supplier_id) = l_cust_supp_id)
	group by mt.trip_id) temp
  where temp.num_stops = 2;

Line: 2365


  select count(*)
  from (select distinct mt.trip_id, count(*) num_stops
        from mst_trips mt
        , mst_trip_stops mts
	where mt.plan_id = l_plan_id
	and mt.carrier_id = l_carrier_id
	and mt.trip_id in (select distinct mdl.trip_id
	                   from mst_deliveries md
			   , mst_delivery_legs mdl
			   , mst_trip_stops mts1
			   , wsh_locations wl
			   where md.plan_id = mt.plan_id
			   and mdl.plan_id = md.plan_id
			   and mdl.delivery_id = md.delivery_id
                           and mts1.plan_id = mdl.plan_id
			   and mts1.trip_id = mdl.trip_id
			   and mts1.stop_id = mdl.pick_up_stop_id
			   and mts1.stop_location_id = wl.wsh_location_id
			   and mts1.stop_location_id = md.pickup_location_id
			   and wl.state = l_orig_state)
	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;

Line: 2426


  select count(*)
  from mst_trips mt
  where mt.plan_id = l_plan_id
  and mt.mode_of_transport = 'LTL'
  and mt.trip_id in (select distinct mdl.trip_id
                     from mst_deliveries md
		     , mst_delivery_legs mdl
                     , mst_trip_stops mts1
		     , wsh_locations wl
		     where md.plan_id = mt.plan_id
		     and mdl.plan_id = md.plan_id
		     and mdl.delivery_id = md.delivery_id
                     and mts1.plan_id = mdl.plan_id
		     and mts1.trip_id = mdl.trip_id
		     and mts1.stop_id = mdl.pick_up_stop_id
 	 	     and mts1.stop_location_id = wl.wsh_location_id
		     and mts1.stop_location_id = md.pickup_location_id
		     and wl.state = l_orig_state);

Line: 2448


  select count(*)
  from mst_trips mt
  where mt.plan_id = l_plan_id
  and mt.mode_of_transport = 'LTL'
  and mt.trip_id in (select distinct mdl.trip_id
                     from mst_deliveries md
		     , mst_delivery_legs mdl
                     , mst_trip_stops mts1
		     , fte_location_parameters flp
		     , wsh_locations wl
		     where md.plan_id = mt.plan_id
		     and mdl.plan_id = md.plan_id
		     and mdl.delivery_id = md.delivery_id
                     and mts1.plan_id = mdl.plan_id
		     and mts1.trip_id = mdl.trip_id
		     and mts1.stop_id = mdl.pick_up_stop_id
	 	     and mts1.stop_location_id = wl.wsh_location_id
		     and mts1.stop_location_id = md.pickup_location_id
		     and wl.state = l_orig_state
		     and flp.location_id = mts1.stop_location_id
		     and flp.facility_id = l_myfac_id);

Line: 2474


  select count(*)
  from mst_trips mt
  where mt.plan_id = l_plan_id
  and mt.mode_of_transport = 'LTL'
  and mt.trip_id in
	          (select distinct mdl.trip_id
	           from mst_deliveries md
		   , mst_delivery_legs mdl
		   , mst_trip_stops mts1
		   , wsh_locations wl
		   where mdl.plan_id = mt.plan_id
		   and mdl.pick_up_stop_id = mts1.stop_id
		   and mdl.trip_id = mts1.trip_id
		   and mts1.plan_id = mt.plan_id
		   and mts1.stop_location_id = wl.wsh_location_id
		   and mts1.stop_location_id = md.pickup_location_id
		   and wl.state = l_orig_state
		   and md.plan_id = mdl.plan_id
		   and md.delivery_id = mdl.delivery_id
                   and decode(l_c_s_ident, 2, md.customer_id, md.supplier_id) = l_cust_supp_id);

Line: 2498


  select count(*)
  from mst_trips mt
  where mt.plan_id = l_plan_id
  and mt.mode_of_transport = 'LTL'
  and mt.carrier_id = l_carrier_id
  and mt.trip_id in (select distinct mdl.trip_id
                     from mst_deliveries md
		     , mst_delivery_legs mdl
                     , mst_trip_stops mts1
		     , wsh_locations wl
		     where md.plan_id = mt.plan_id
		     and mdl.plan_id = md.plan_id
		     and mdl.delivery_id = md.delivery_id
                     and mts1.plan_id = mdl.plan_id
		     and mts1.trip_id = mdl.trip_id
		     and mts1.stop_id = mdl.pick_up_stop_id
 	 	     and mts1.stop_location_id = wl.wsh_location_id
		     and mts1.stop_location_id = md.pickup_location_id
		     and wl.state = l_orig_state);

Line: 2552


  select count(*)
  from mst_trips mt
  where mt.plan_id = l_plan_id
  and mt.mode_of_transport = 'PARCEL'
  and mt.trip_id in (select distinct mdl.trip_id
                     from mst_deliveries md
		     , mst_delivery_legs mdl
                     , mst_trip_stops mts1
		     , wsh_locations wl
		     where md.plan_id = mt.plan_id
		     and mdl.plan_id = md.plan_id
		     and mdl.delivery_id = md.delivery_id
                     and mts1.plan_id = mdl.plan_id
		     and mts1.trip_id = mdl.trip_id
		     and mts1.stop_id = mdl.pick_up_stop_id
		     and mts1.stop_location_id = wl.wsh_location_id
		     and mts1.stop_location_id = md.pickup_location_id
		     and wl.state = l_orig_state);

Line: 2574


  select count(*)
  from mst_trips mt
  where mt.plan_id = l_plan_id
  and mt.mode_of_transport = 'PARCEL'
  and mt.trip_id in (select distinct mdl.trip_id
                     from mst_deliveries md
		     , mst_delivery_legs mdl
                     , mst_trip_stops mts1
		     , fte_location_parameters flp
		     , wsh_locations wl
		     where md.plan_id = mt.plan_id
		     and mdl.plan_id = md.plan_id
		     and mdl.delivery_id = md.delivery_id
                     and mts1.plan_id = mdl.plan_id
		     and mts1.trip_id = mdl.trip_id
		     and mts1.stop_id = mdl.pick_up_stop_id
		     and mts1.stop_location_id = wl.wsh_location_id
		     and mts1.stop_location_id = md.pickup_location_id
		     and wl.state = l_orig_state
		     and flp.location_id = mts1.stop_location_id
		     and flp.facility_id = l_myfac_id);

Line: 2600


  select count(*)
  from mst_trips mt
  where mt.plan_id = l_plan_id
  and mt.mode_of_transport = 'PARCEL'
  and mt.trip_id in
	          (select distinct mdl.trip_id
	           from mst_deliveries md
		   , mst_delivery_legs mdl
		   , mst_trip_stops mts1
		   , wsh_locations wl
		   where mdl.plan_id = mt.plan_id
		   and mdl.pick_up_stop_id = mts1.stop_id
		   and mdl.trip_id = mts1.trip_id
		   and mts1.plan_id = mt.plan_id
		   and mts1.stop_location_id = wl.wsh_location_id
		   and mts1.stop_location_id = md.pickup_location_id
		   and wl.state = l_orig_state
		   and md.plan_id = mdl.plan_id
		   and md.delivery_id = mdl.delivery_id
                   and decode(l_c_s_ident, 2, md.customer_id, md.supplier_id) = l_cust_supp_id);

Line: 2624


  select count(*)
  from mst_trips mt
  where mt.plan_id = l_plan_id
  and mt.mode_of_transport = 'PARCEL'
  and mt.carrier_id = l_carrier_id
  and mt.trip_id in (select distinct mdl.trip_id
                     from mst_deliveries md
		     , mst_delivery_legs mdl
                     , mst_trip_stops mts1
		     , wsh_locations wl
		     where md.plan_id = mt.plan_id
		     and mdl.plan_id = md.plan_id
		     and mdl.delivery_id = md.delivery_id
                     and mts1.plan_id = mdl.plan_id
		     and mts1.trip_id = mdl.trip_id
		     and mts1.stop_id = mdl.pick_up_stop_id
		     and mts1.stop_location_id = wl.wsh_location_id
		     and mts1.stop_location_id = md.pickup_location_id
		     and wl.state = l_orig_state);

Line: 2681


  select nvl(sum(nvl(mdl.allocated_fac_loading_cost,0)
               + nvl(mdl.allocated_fac_unloading_cost,0)
	       + nvl(mdl.allocated_transport_cost,0)
	       + nvl(mdl.allocated_fac_shp_hand_cost,0)
               + nvl(mdl.allocated_fac_rec_hand_cost,0)),0)
  from mst_delivery_legs mdl
  where mdl.plan_id = l_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
		   , wsh_locations wl
                where mt.plan_id = mdl1.plan_id
                and   mt.trip_id  = mdl1.trip_id
                and   mt.mode_of_transport = l_mode
                and   mt.plan_id = mts.plan_id
                and   mt.trip_id = mts.trip_id
	        and   mts.stop_location_id = wl.wsh_location_id
	        and   wl.state = l_orig_state
                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);

Line: 2708


  (select nvl(sum(nvl(mdl.allocated_fac_loading_cost,0)
               + nvl(mdl.allocated_fac_unloading_cost,0)
	       + nvl(mdl.allocated_transport_cost,0)
	       + nvl(mdl.allocated_fac_shp_hand_cost,0)
               + nvl(mdl.allocated_fac_rec_hand_cost,0)),0)
  from mst_deliveries md
  , mst_delivery_legs mdl
  , mst_trips mt
--  , mst_trip_stops mts
  , wsh_locations wl
  where mt.plan_id = l_plan_id
  and mt.mode_of_transport = l_mode
  and mt.plan_id = mdl.plan_id
  and mt.trip_id = mdl.trip_id
  and mdl.plan_id = md.plan_id
  and mdl.delivery_id = md.delivery_id
  and wl.state = l_orig_state
  and md.pickup_location_id = wl.wsh_location_id );

Line: 2740


  select nvl(sum(nvl(mdl.allocated_fac_loading_cost,0)
               + nvl(mdl.allocated_fac_unloading_cost,0)
	       + nvl(mdl.allocated_transport_cost,0)
	       + nvl(mdl.allocated_fac_shp_hand_cost,0)
               + nvl(mdl.allocated_fac_rec_hand_cost,0)),0)
  from mst_delivery_legs mdl
  where mdl.plan_id = l_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
		   , fte_location_parameters flp
		   , wsh_locations wl
                where mt.plan_id = mdl1.plan_id
                and   mt.trip_id  = mdl1.trip_id
                and   mt.mode_of_transport = l_mode
                and   mt.plan_id = mts.plan_id
                and   mt.trip_id = mts.trip_id
                and   mts.stop_location_id = flp.location_id
                and   flp.facility_id = l_myfac_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 = flp.location_id
	        and   flp.location_id = wl.wsh_location_id
	        and   wl.state = l_orig_state);

Line: 2771


  (select nvl(sum(nvl(mdl.allocated_fac_loading_cost,0)
               + nvl(mdl.allocated_fac_unloading_cost,0)
	       + nvl(mdl.allocated_transport_cost,0)
	       + nvl(mdl.allocated_fac_shp_hand_cost,0)
               + nvl(mdl.allocated_fac_rec_hand_cost,0)),0)
  from mst_deliveries md
  , mst_delivery_legs mdl
  , mst_trips mt
--  , mst_trip_stops mts
  , wsh_locations wl
  , fte_location_parameters flp
  where mt.plan_id = l_plan_id
  and mt.mode_of_transport = l_mode
  and mt.plan_id = mdl.plan_id
  and mt.trip_id = mdl.trip_id
  and mdl.plan_id = md.plan_id
  and mdl.delivery_id = md.delivery_id
--  and mts.stop_location_id = wl.wsh_location_id
  and md.pickup_location_id = wl.wsh_location_id
  and wl.state = l_orig_state
--  and mt.plan_id = mts.plan_id
--  and mt.trip_id = mts.trip_id
  and flp.facility_id = l_myfac_id
  and md.pickup_location_id = flp.location_id );

Line: 2798


  select nvl(sum(nvl(mdl.allocated_fac_loading_cost,0)
               + nvl(mdl.allocated_fac_unloading_cost,0)
	       + nvl(mdl.allocated_transport_cost,0)
	       + nvl(mdl.allocated_fac_shp_hand_cost,0)
               + nvl(mdl.allocated_fac_rec_hand_cost,0)),0)
  from mst_delivery_legs mdl
  , mst_trips mt
  where mt.plan_id = l_plan_id
  and mt.mode_of_transport = l_mode
  and mt.plan_id = mdl.plan_id
  and mt.trip_id = mdl.trip_id
  and mdl.delivery_id in (select mdl.delivery_id
                         from mst_deliveries md
                         , mst_delivery_legs mdl
                         , mst_trip_stops mts
                         , fte_location_parameters flp
                         , wsh_locations wl
                         where mdl.plan_id = l_plan_id
                         and mdl.plan_id = mts.plan_id
                         and mdl.trip_id = mts.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 = flp.location_id
                         and flp.facility_id = l_myfac_id
                         and mdl.plan_id = md.plan_id
                         and mdl.delivery_id = md.delivery_id
                         and md.pickup_location_id = wl.wsh_location_id
                         and wl.state = l_orig_state);

Line: 2833


  select nvl(sum(nvl(mdl.allocated_fac_loading_cost,0)
               + nvl(mdl.allocated_fac_unloading_cost,0)
	       + nvl(mdl.allocated_transport_cost,0)
	       + nvl(mdl.allocated_fac_shp_hand_cost,0)
               + nvl(mdl.allocated_fac_rec_hand_cost,0)),0)
  from mst_delivery_legs mdl
  where mdl.plan_id = l_plan_id
  and mdl.delivery_id in
             ( select md.delivery_id
               from mst_trips mt
                , mst_trip_stops mts
                , mst_delivery_legs mdl1
                , mst_deliveries md
		, wsh_locations wl
                where mt.plan_id = mdl1.plan_id
                and   mt.trip_id  = mdl1.trip_id
                and   mt.mode_of_transport = l_mode
                and   mt.plan_id = mts.plan_id
                and   mt.trip_id = mts.trip_id
                and   mts.stop_location_id = wl.wsh_location_id
	        and   wl.state = l_orig_state
                and   md.plan_id = mdl1.plan_id
                and   md.delivery_id  = mdl1.delivery_id
                and   md.plan_id = mdl.plan_id
                and   decode(l_c_s_ident, 2, md.customer_id, md.supplier_id) = l_cust_supp_id
                and   md.pickup_location_id = mts.stop_location_id);

Line: 2861


  (select nvl(sum(nvl(mdl.allocated_fac_loading_cost,0)
               + nvl(mdl.allocated_fac_unloading_cost,0)
	       + nvl(mdl.allocated_transport_cost,0)
	       + nvl(mdl.allocated_fac_shp_hand_cost,0)
               + nvl(mdl.allocated_fac_rec_hand_cost,0)),0)
  from mst_deliveries md
  , mst_delivery_legs mdl
  , mst_trips mt
--  , mst_trip_stops mts
  , wsh_locations wl
  where mt.plan_id = l_plan_id
  and mt.mode_of_transport = l_mode
  and mt.plan_id = mdl.plan_id
  and mt.trip_id = mdl.trip_id
  and mdl.plan_id = md.plan_id
  and mdl.delivery_id = md.delivery_id
--  and mts.stop_location_id = wl.wsh_location_id
  and md.pickup_location_id = wl.wsh_location_id
  and wl.state = l_orig_state
--  and mt.plan_id = mts.plan_id
--  and mt.trip_id = mts.trip_id
  and decode(l_c_s_ident, 2, md.customer_id, md.supplier_id) = l_cust_supp_id );

Line: 2891


  select nvl(sum(decode(mt.mode_of_transport
                       , 'TRUCK', (nvl(mt.total_basic_transport_cost,0)
                                 + nvl(mt.total_stop_cost,0)
		                 + nvl(mt.total_load_unload_cost,0)
			         + nvl(mt.total_layover_cost,0)
			         + nvl(mt.total_accessorial_cost,0)
			         + nvl(mt.total_handling_cost,0))
		       , (nvl(mt.total_basic_transport_cost,0)
		        + nvl(mt.total_accessorial_cost,0)))),0)
  from mst_trips mt
  where mt.plan_id = l_plan_id
  and mt.carrier_id = l_carrier_id
  and mt.mode_of_transport = l_mode
--  and mt.continuous_move_id is null --check
  and   mt.trip_id in
                   (select distinct mts.trip_id
                    from mst_trip_stops mts
                    , mst_delivery_legs mdl
                    , mst_deliveries md
                    , wsh_locations wl
                    where md.plan_id = mt.plan_id
                    and mts.plan_id = md.plan_id
                    and mdl.plan_id = md.plan_id
                    and mdl.delivery_id = md.delivery_id
                    and mdl.trip_id = mts.trip_id
		    and mts.stop_id = mdl.pick_up_stop_id
                    and mts.stop_location_id = md.pickup_location_id
		    and wl.wsh_location_id = mts.stop_location_id
		    and wl.state = l_orig_state);

Line: 2921


	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 )
	from  mst_deliveries md
	, mst_delivery_legs mdl
	, mst_trips mt
	, wsh_locations wl
	, mst_trip_stops mts
	where md.plan_id = l_plan_id
	and md.plan_id = mt.plan_id
	and md.plan_id = mdl.plan_id
	and md.plan_id = mts.plan_id
	and md.delivery_id = mdl.delivery_id
	and mt.trip_id = mdl.trip_id
	and mt.carrier_id = l_carrier_id
	and mt.mode_of_transport = l_mode
	and mts.trip_id = mt.trip_id
	and mts.stop_location_id = wl.wsh_location_id
	and mdl.pick_up_stop_id = mts.stop_id
	and wl.state = l_orig_state;

Line: 2979


  select count(distinct mdd.source_header_number)
  from mst_delivery_details mdd
  , mst_delivery_assignments mda
  , mst_deliveries md
  , wsh_locations wl
  where md.plan_id = mda.plan_id
  and   md.delivery_id = mda.delivery_id
  and   md.dropoff_location_id = wl.wsh_location_id
  and   wl.state = l_destination_state
  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 = l_plan_id;

Line: 2998


  select count(distinct mdd.source_header_number)
  from mst_delivery_details mdd
  , mst_delivery_assignments mda
  , mst_deliveries md
  , fte_location_parameters flp
  , wsh_locations wl
  where md.plan_id = mda.plan_id
  and   md.delivery_id = mda.delivery_id
  and   md.dropoff_location_id = wl.wsh_location_id
  and   wl.state = l_destination_state
  and   flp.location_id = md.dropoff_location_id
  and   flp.facility_id = l_myfac_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 = l_plan_id;

Line: 3016


  select count(distinct mdd.source_header_number)
  from mst_delivery_assignments mda
  , mst_delivery_details mdd
  where mdd.plan_id = l_plan_id
  and mdd.plan_id = mda.plan_id
  and mdd.delivery_detail_id = mda.delivery_detail_id
  and mda.delivery_id in (select mdl.delivery_id
                         from mst_deliveries md
                         , mst_delivery_legs mdl
                         , mst_trip_stops mts
                         , fte_location_parameters flp
                         , wsh_locations wl
                         where mdl.plan_id = l_plan_id
                         and mdl.plan_id = mts.plan_id
                         and mdl.trip_id = mts.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 = flp.location_id
                         and flp.facility_id = l_myfac_id
                         and mdl.plan_id = md.plan_id
                         and mdl.delivery_id = md.delivery_id
                         and md.dropoff_location_id = wl.wsh_location_id
                         and wl.state = l_destination_state);

Line: 3045


  select count(distinct mdd.source_header_number)
  from mst_delivery_details mdd
  , mst_delivery_assignments mda
  , mst_deliveries md
  , wsh_locations wl
  where md.plan_id = mda.plan_id
  and   md.delivery_id = mda.delivery_id
  and   md.dropoff_location_id = wl.wsh_location_id
  and   wl.state = l_destination_state
  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 = l_plan_id
  and   decode(l_c_s_ident, 2, md.customer_id, md.supplier_id) = l_cust_supp_id;

Line: 3063


  select count(distinct 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_trips t
         , mst_trip_stops ts
         , mst_delivery_legs mdl
         , wsh_locations wl
         where mdl.plan_id = md.plan_id
         and ts.plan_id  = mdl.plan_id
         and ts.stop_id  = mdl.drop_off_stop_id
	 and wl.wsh_location_id = md.dropoff_location_id
         and ts.stop_location_id = wl.wsh_location_id
	 and wl.state = l_destination_state
         and ts.plan_id  = t.plan_id
         and ts.trip_id  = t.trip_id
	 and t.carrier_id = l_carrier_id)
  and   mda.plan_id = mdd.plan_id
  and   mda.delivery_detail_id = mdd.delivery_detail_id
  and   md.plan_id = l_plan_id
  and   mdd.container_flag = 2;

Line: 3124


  select nvl(sum(nvl(md.gross_weight,0)),0)
  from mst_deliveries md
  , wsh_locations wl
  where md.plan_id = l_plan_id
  and   md.dropoff_location_id = wl.wsh_location_id
  and   wl.state = l_destination_state;

Line: 3135


  select nvl(sum(nvl(md.gross_weight,0)),0)
  from mst_deliveries md
  , fte_location_parameters flp
  , wsh_locations wl
  where md.plan_id = l_plan_id
  and   md.dropoff_location_id = wl.wsh_location_id
  and   wl.state = l_destination_state
  and flp.location_id = md.dropoff_location_id
  and flp.facility_id = l_myfac_id;

Line: 3146


  select nvl(sum(nvl(md.gross_weight,0)),0)
  from mst_deliveries md
  where md.plan_id = l_plan_id
  and md.delivery_id in (select mdl.delivery_id
                         from mst_deliveries md
                         , mst_delivery_legs mdl
                         , mst_trip_stops mts
                         , fte_location_parameters flp
                         , wsh_locations wl
                         where mdl.plan_id = l_plan_id
                         and mdl.plan_id = mts.plan_id
                         and mdl.trip_id = mts.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 = flp.location_id
                         and flp.facility_id = l_myfac_id
                         and mdl.plan_id = md.plan_id
                         and mdl.delivery_id = md.delivery_id
                         and md.dropoff_location_id = wl.wsh_location_id
                         and wl.state = l_destination_state);

Line: 3171


  select nvl(sum(nvl(md.gross_weight,0)),0)
  from mst_deliveries md
  , wsh_locations wl
  where md.plan_id = l_plan_id
  and   md.dropoff_location_id = wl.wsh_location_id
  and   wl.state = l_destination_state
  and decode(l_c_s_ident, 2, md.customer_id, md.supplier_id) = l_cust_supp_id;

Line: 3182


  select nvl(sum(nvl(md.gross_weight,0)),0)
  from mst_deliveries md
  , wsh_locations wl
  where md.plan_id = l_plan_id
  and md.dropoff_location_id = wl.wsh_location_id
  and wl.state = l_destination_state
  and md.delivery_id in (select distinct mdl.delivery_id
                         from mst_delivery_legs mdl
			 , mst_trips mt
			 where mdl.plan_id = md.plan_id
			 and mt.plan_id = mdl.plan_id
			 and mt.trip_id = mdl.trip_id
			 and mt.carrier_id = l_carrier_id);

Line: 3230


  select nvl(sum(nvl(md.volume,0)),0)
  from mst_deliveries md
  , wsh_locations wl
  where md.plan_id = l_plan_id
  and   md.dropoff_location_id = wl.wsh_location_id
  and   wl.state = l_destination_state;

Line: 3241


  select nvl(sum(nvl(md.volume,0)),0)
  from mst_deliveries md
  , fte_location_parameters flp
  , wsh_locations wl
  where md.plan_id = l_plan_id
  and   md.dropoff_location_id = wl.wsh_location_id
  and   wl.state = l_destination_state
  and flp.location_id = md.dropoff_location_id
  and flp.facility_id = l_myfac_id;

Line: 3252


  select nvl(sum(nvl(md.volume,0)),0)
  from mst_deliveries md
  where md.plan_id = l_plan_id
  and md.delivery_id in (select mdl.delivery_id
                         from mst_deliveries md
                         , mst_delivery_legs mdl
                         , mst_trip_stops mts
                         , fte_location_parameters flp
                         , wsh_locations wl
                         where mdl.plan_id = l_plan_id
                         and mdl.plan_id = mts.plan_id
                         and mdl.trip_id = mts.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 = flp.location_id
                         and flp.facility_id = l_myfac_id
                         and mdl.plan_id = md.plan_id
                         and mdl.delivery_id = md.delivery_id
                         and md.dropoff_location_id = wl.wsh_location_id
                         and wl.state = l_destination_state);

Line: 3277


  select nvl(sum(nvl(md.volume,0)),0)
  from mst_deliveries md
  , wsh_locations wl
  where md.plan_id = l_plan_id
  and   md.dropoff_location_id = wl.wsh_location_id
  and   wl.state = l_destination_state
  and decode(l_c_s_ident, 2, md.customer_id, md.supplier_id) = l_cust_supp_id;

Line: 3288


  select nvl(sum(nvl(md.volume,0)),0)
  from mst_deliveries md
  , wsh_locations wl
  where md.plan_id = l_plan_id
  and md.dropoff_location_id = wl.wsh_location_id
  and wl.state = l_destination_state
  and md.delivery_id in (select distinct mdl.delivery_id
                         from mst_delivery_legs mdl
			 , mst_trips mt
			 where mdl.plan_id = md.plan_id
			 and mt.plan_id = mdl.plan_id
			 and mt.trip_id = mdl.trip_id
			 and mt.carrier_id = l_carrier_id);

Line: 3336


  select nvl(sum(nvl(md.number_of_pieces,0)),0)
  from mst_deliveries md
  , wsh_locations wl
  where md.plan_id = l_plan_id
  and   md.dropoff_location_id = wl.wsh_location_id
  and   wl.state = l_destination_state;

Line: 3347


  select nvl(sum(nvl(md.number_of_pieces,0)),0)
  from mst_deliveries md
  , fte_location_parameters flp
  , wsh_locations wl
  where md.plan_id = l_plan_id
  and   md.dropoff_location_id = wl.wsh_location_id
  and   wl.state = l_destination_state
  and flp.location_id = md.dropoff_location_id
  and flp.facility_id = l_myfac_id;

Line: 3358


  select nvl(sum(nvl(md.number_of_pieces,0)),0)
  from mst_deliveries md
  where md.plan_id = l_plan_id
  and md.delivery_id in (select mdl.delivery_id
                         from mst_deliveries md
                         , mst_delivery_legs mdl
                         , mst_trip_stops mts
                         , fte_location_parameters flp
                         , wsh_locations wl
                         where mdl.plan_id = l_plan_id
                         and mdl.plan_id = mts.plan_id
                         and mdl.trip_id = mts.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 = flp.location_id
                         and flp.facility_id = l_myfac_id
                         and mdl.plan_id = md.plan_id
                         and mdl.delivery_id = md.delivery_id
                         and md.dropoff_location_id = wl.wsh_location_id
                         and wl.state = l_destination_state);

Line: 3383


  select nvl(sum(nvl(md.number_of_pieces,0)),0)
  from mst_deliveries md
  , wsh_locations wl
  where md.plan_id = l_plan_id
  and   md.dropoff_location_id = wl.wsh_location_id
  and   wl.state = l_destination_state
  and decode(l_c_s_ident, 2, md.customer_id, md.supplier_id) = l_cust_supp_id;

Line: 3394


  select nvl(sum(nvl(md.number_of_pieces,0)),0)
  from mst_deliveries md
  , wsh_locations wl
  where md.plan_id = l_plan_id
  and md.dropoff_location_id = wl.wsh_location_id
  and wl.state = l_destination_state
  and md.delivery_id in (select distinct mdl.delivery_id
                         from mst_delivery_legs mdl
			 , mst_trips mt
			 where mdl.plan_id = md.plan_id
			 and mt.plan_id = mdl.plan_id
			 and mt.trip_id = mdl.trip_id
			 and mt.carrier_id = l_carrier_id);

Line: 3442


  select count(*)
  from (select distinct mt.trip_id, count(*) num_stops
        from mst_trips mt
        , mst_trip_stops mts
	where mt.plan_id = l_plan_id
	and mt.trip_id in (select distinct mdl.trip_id
	                   from mst_deliveries md
			   , mst_delivery_legs mdl
			   , mst_trip_stops mts1
			   , wsh_locations wl
			   where md.plan_id = mt.plan_id
			   and mdl.plan_id = md.plan_id
			   and mdl.delivery_id = md.delivery_id
                           and mts1.plan_id = mdl.plan_id
			   and mts1.trip_id = mdl.trip_id
			   and mts1.stop_id = mdl.drop_off_stop_id
			   and mts1.stop_location_id = wl.wsh_location_id
			   and mts1.stop_location_id = md.dropoff_location_id
			   and wl.state = l_destination_state)
	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;

Line: 3470


  select count(*)
  from (select distinct mt.trip_id, count(*) num_stops
        from mst_trips mt
        , mst_trip_stops mts
	where mt.plan_id = l_plan_id
	and mt.trip_id in (select distinct mdl.trip_id
	                   from mst_deliveries md
			   , mst_delivery_legs mdl
			   , mst_trip_stops mts1
                           , fte_location_parameters flp
			   , wsh_locations wl
			   where md.plan_id = mt.plan_id
			   and mdl.plan_id = md.plan_id
			   and mdl.delivery_id = md.delivery_id
                           and mts1.plan_id = mdl.plan_id
			   and mts1.trip_id = mdl.trip_id
			   and mts1.stop_id = mdl.drop_off_stop_id
			   and mts1.stop_location_id = wl.wsh_location_id
			   and mts1.stop_location_id = md.dropoff_location_id
			   and wl.state = l_destination_state
                           and flp.location_id = mts1.stop_location_id
                           and flp.facility_id = l_myfac_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;

Line: 3502


  select count(*)
  from (select distinct mt.trip_id, count(*) num_stops
        from mst_trips mt
        , mst_trip_stops mts
	where mt.plan_id = l_plan_id
	and mt.mode_of_transport = 'TRUCK'
	and mts.plan_id = mt.plan_id
	and mts.trip_id = mt.trip_id
	and mt.trip_id in
	          (select distinct mdl.trip_id
	           from mst_deliveries md
		   , mst_delivery_legs mdl
		   , mst_trip_stops mts1
		   , wsh_locations wl
		   where mdl.plan_id = mt.plan_id
		   and mdl.drop_off_stop_id = mts1.stop_id
		   and mdl.trip_id = mts1.trip_id
		   and mts1.plan_id = mt.plan_id
		   and mts1.stop_location_id = wl.wsh_location_id
		   and mts1.stop_location_id = md.dropoff_location_id
		   and wl.state = l_destination_state
		   and md.plan_id = mdl.plan_id
		   and md.delivery_id = mdl.delivery_id
                   and decode(l_c_s_ident, 2, md.customer_id, md.supplier_id) = l_cust_supp_id)
	group by mt.trip_id) temp
  where temp.num_stops > 2;

Line: 3532


  select count(*)
  from (select distinct mt.trip_id, count(*) num_stops
        from mst_trips mt
        , mst_trip_stops mts
	where mt.plan_id = l_plan_id
	and mt.carrier_id = l_carrier_id
	and mt.trip_id in (select distinct mdl.trip_id
	                   from mst_deliveries md
			   , mst_delivery_legs mdl
			   , mst_trip_stops mts1
			   , wsh_locations wl
			   where md.plan_id = mt.plan_id
			   and mdl.plan_id = md.plan_id
			   and mdl.delivery_id = md.delivery_id
                           and mts1.plan_id = mdl.plan_id
			   and mts1.trip_id = mdl.trip_id
			   and mts1.stop_id = mdl.drop_off_stop_id
			   and mts1.stop_location_id = wl.wsh_location_id
			   and mts1.stop_location_id = md.dropoff_location_id
			   and wl.state = l_destination_state)
	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;

Line: 3592


  select count(*)
  from (select distinct mt.trip_id, count(*) num_stops
        from mst_trips mt
        , mst_trip_stops mts
	where mt.plan_id = l_plan_id
	and mt.trip_id in (select distinct mdl.trip_id
	                   from mst_deliveries md
			   , mst_delivery_legs mdl
			   , mst_trip_stops mts1
			   , wsh_locations wl
			   where md.plan_id = mt.plan_id
			   and mdl.plan_id = md.plan_id
			   and mdl.delivery_id = md.delivery_id
                           and mts1.plan_id = mdl.plan_id
			   and mts1.trip_id = mdl.trip_id
			   and mts1.stop_id = mdl.drop_off_stop_id
			   and mts1.stop_location_id = wl.wsh_location_id
			   and mts1.stop_location_id = md.dropoff_location_id
			   and wl.state = l_destination_state)
	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;

Line: 3620


  select count(*)
  from (select distinct mt.trip_id, count(*) num_stops
        from mst_trips mt
        , mst_trip_stops mts
	where mt.plan_id = l_plan_id
	and mt.trip_id in (select distinct mdl.trip_id
	                   from mst_deliveries md
			   , mst_delivery_legs mdl
			   , mst_trip_stops mts1
                           , fte_location_parameters flp
			   , wsh_locations wl
			   where md.plan_id = mt.plan_id
			   and mdl.plan_id = md.plan_id
			   and mdl.delivery_id = md.delivery_id
                           and mts1.plan_id = mdl.plan_id
			   and mts1.trip_id = mdl.trip_id
			   and mts1.stop_id = mdl.drop_off_stop_id
			   and mts1.stop_location_id = wl.wsh_location_id
			   and mts1.stop_location_id = md.dropoff_location_id
			   and wl.state = l_destination_state
                           and flp.location_id = mts1.stop_location_id
                           and flp.facility_id = l_myfac_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;

Line: 3652


  select count(*)
  from (select distinct mt.trip_id, count(*) num_stops
        from mst_trips mt
        , mst_trip_stops mts
	where mt.plan_id = l_plan_id
	and mt.mode_of_transport = 'TRUCK'
	and mts.plan_id = mt.plan_id
	and mts.trip_id = mt.trip_id
	and mt.trip_id in
	          (select distinct mdl.trip_id
	           from mst_deliveries md
		   , mst_delivery_legs mdl
		   , mst_trip_stops mts1
		   , wsh_locations wl
		   where mdl.plan_id = mt.plan_id
		   and mdl.drop_off_stop_id = mts1.stop_id
		   and mdl.trip_id = mts1.trip_id
		   and mts1.plan_id = mt.plan_id
		   and mts1.stop_location_id = wl.wsh_location_id
		   and mts1.stop_location_id = md.dropoff_location_id
		   and wl.state = l_destination_state
		   and md.plan_id = mdl.plan_id
		   and md.delivery_id = mdl.delivery_id
                   and decode(l_c_s_ident, 2, md.customer_id, md.supplier_id) = l_cust_supp_id)
	group by mt.trip_id) temp
  where temp.num_stops = 2;

Line: 3682


  select count(*)
  from (select distinct mt.trip_id, count(*) num_stops
        from mst_trips mt
        , mst_trip_stops mts
	where mt.plan_id = l_plan_id
	and mt.carrier_id = l_carrier_id
	and mt.trip_id in (select distinct mdl.trip_id
	                   from mst_deliveries md
			   , mst_delivery_legs mdl
			   , mst_trip_stops mts1
			   , wsh_locations wl
			   where md.plan_id = mt.plan_id
			   and mdl.plan_id = md.plan_id
			   and mdl.delivery_id = md.delivery_id
                           and mts1.plan_id = mdl.plan_id
			   and mts1.trip_id = mdl.trip_id
			   and mts1.stop_id = mdl.drop_off_stop_id
			   and mts1.stop_location_id = wl.wsh_location_id
			   and mts1.stop_location_id = md.dropoff_location_id
			   and wl.state = l_destination_state)
	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;

Line: 3743


  select count(*)
  from mst_trips mt
  where mt.plan_id = l_plan_id
  and mt.mode_of_transport = 'LTL'
  and mt.trip_id in (select distinct mdl.trip_id
                     from mst_deliveries md
		     , mst_delivery_legs mdl
                     , mst_trip_stops mts1
		     , wsh_locations wl
		     where md.plan_id = mt.plan_id
		     and mdl.plan_id = md.plan_id
		     and mdl.delivery_id = md.delivery_id
                     and mts1.plan_id = mdl.plan_id
		     and mts1.trip_id = mdl.trip_id
		     and mts1.stop_id = mdl.drop_off_stop_id
		     and mts1.stop_location_id = wl.wsh_location_id
		     and mts1.stop_location_id = md.dropoff_location_id
		     and wl.state = l_destination_state);

Line: 3765


  select count(*)
  from mst_trips mt
  where mt.plan_id = l_plan_id
  and mt.mode_of_transport = 'LTL'
  and mt.trip_id in (select distinct mdl.trip_id
                     from mst_deliveries md
		     , mst_delivery_legs mdl
                     , mst_trip_stops mts1
		     , fte_location_parameters flp
		     , wsh_locations wl
		     where md.plan_id = mt.plan_id
		     and mdl.plan_id = md.plan_id
		     and mdl.delivery_id = md.delivery_id
                     and mts1.plan_id = mdl.plan_id
		     and mts1.trip_id = mdl.trip_id
		     and mts1.stop_id = mdl.drop_off_stop_id
		     and mts1.stop_location_id = wl.wsh_location_id
		     and mts1.stop_location_id = md.dropoff_location_id
		     and wl.state = l_destination_state
		     and flp.location_id = mts1.stop_location_id
		     and flp.facility_id = l_myfac_id);

Line: 3791


  select count(*)
  from mst_trips mt
  where mt.plan_id = l_plan_id
  and mt.mode_of_transport = 'LTL'
  and mt.trip_id in
	          (select distinct mdl.trip_id
	           from mst_deliveries md
		   , mst_delivery_legs mdl
		   , mst_trip_stops mts1
		   , wsh_locations wl
		   where mdl.plan_id = mt.plan_id
		   and mdl.drop_off_stop_id = mts1.stop_id
		   and mdl.trip_id = mts1.trip_id
		   and mts1.plan_id = mt.plan_id
		   and mts1.stop_location_id = wl.wsh_location_id
		   and mts1.stop_location_id = md.dropoff_location_id
		   and wl.state = l_destination_state
		   and md.plan_id = mdl.plan_id
		   and md.delivery_id = mdl.delivery_id
                   and decode(l_c_s_ident, 2, md.customer_id, md.supplier_id) = l_cust_supp_id);

Line: 3815


  select count(*)
  from mst_trips mt
  where mt.plan_id = l_plan_id
  and mt.mode_of_transport = 'LTL'
  and mt.carrier_id = l_carrier_id
  and mt.trip_id in (select distinct mdl.trip_id
                     from mst_deliveries md
		     , mst_delivery_legs mdl
                     , mst_trip_stops mts1
		     , wsh_locations wl
		     where md.plan_id = mt.plan_id
		     and mdl.plan_id = md.plan_id
		     and mdl.delivery_id = md.delivery_id
                     and mts1.plan_id = mdl.plan_id
		     and mts1.trip_id = mdl.trip_id
		     and mts1.stop_id = mdl.drop_off_stop_id
		     and mts1.stop_location_id = wl.wsh_location_id
		     and mts1.stop_location_id = md.dropoff_location_id
		     and wl.state = l_destination_state);

Line: 3869


  select count(*)
  from mst_trips mt
  where mt.plan_id = l_plan_id
  and mt.mode_of_transport = 'PARCEL'
  and mt.trip_id in (select distinct mdl.trip_id
                     from mst_deliveries md
		     , mst_delivery_legs mdl
                     , mst_trip_stops mts1
		     , wsh_locations wl
		     where md.plan_id = mt.plan_id
		     and mdl.plan_id = md.plan_id
		     and mdl.delivery_id = md.delivery_id
                     and mts1.plan_id = mdl.plan_id
		     and mts1.trip_id = mdl.trip_id
		     and mts1.stop_id = mdl.drop_off_stop_id
		     and mts1.stop_location_id = wl.wsh_location_id
		     and mts1.stop_location_id = md.dropoff_location_id
		     and wl.state = l_destination_state);

Line: 3891


  select count(*)
  from mst_trips mt
  where mt.plan_id = l_plan_id
  and mt.mode_of_transport = 'PARCEL'
  and mt.trip_id in (select distinct mdl.trip_id
                     from mst_deliveries md
		     , mst_delivery_legs mdl
                     , mst_trip_stops mts1
		     , fte_location_parameters flp
		     , wsh_locations wl
		     where md.plan_id = mt.plan_id
 	             and mdl.plan_id = md.plan_id
  		     and mdl.delivery_id = md.delivery_id
                     and mts1.plan_id = mdl.plan_id
		     and mts1.trip_id = mdl.trip_id
		     and mts1.stop_id = mdl.drop_off_stop_id
		     and mts1.stop_location_id = wl.wsh_location_id
		     and mts1.stop_location_id = md.dropoff_location_id
		     and wl.state = l_destination_state
		     and flp.location_id = mts1.stop_location_id
		     and flp.facility_id = l_myfac_id);

Line: 3917


  select count(*)
  from mst_trips mt
  where mt.plan_id = l_plan_id
  and mt.mode_of_transport = 'PARCEL'
  and mt.trip_id in
	          (select distinct mdl.trip_id
	           from mst_deliveries md
		   , mst_delivery_legs mdl
		   , mst_trip_stops mts1
		   , wsh_locations wl
		   where mdl.plan_id = mt.plan_id
		   and mdl.drop_off_stop_id = mts1.stop_id
		   and mdl.trip_id = mts1.trip_id
		   and mts1.plan_id = mt.plan_id
		   and mts1.stop_location_id = wl.wsh_location_id
		   and mts1.stop_location_id = md.dropoff_location_id
		   and wl.state = l_destination_state
		   and md.plan_id = mdl.plan_id
		   and md.delivery_id = mdl.delivery_id
                   and decode(l_c_s_ident, 2, md.customer_id, md.supplier_id) = l_cust_supp_id);

Line: 3941


  select count(*)
  from mst_trips mt
  where mt.plan_id = l_plan_id
  and mt.mode_of_transport = 'PARCEL'
  and mt.carrier_id = l_carrier_id
  and mt.trip_id in (select distinct mdl.trip_id
                     from mst_deliveries md
		     , mst_delivery_legs mdl
                     , mst_trip_stops mts1
		     , wsh_locations wl
		     where md.plan_id = mt.plan_id
		     and mdl.plan_id = md.plan_id
		     and mdl.delivery_id = md.delivery_id
                     and mts1.plan_id = mdl.plan_id
		     and mts1.trip_id = mdl.trip_id
		     and mts1.stop_id = mdl.drop_off_stop_id
		     and mts1.stop_location_id = wl.wsh_location_id
		     and mts1.stop_location_id = md.dropoff_location_id
		     and wl.state = l_destination_state);

Line: 3998


  select nvl(sum(nvl(mdl.allocated_fac_loading_cost,0)
               + nvl(mdl.allocated_fac_unloading_cost,0)
	       + nvl(mdl.allocated_transport_cost,0)
	       + nvl(mdl.allocated_fac_shp_hand_cost,0)
               + nvl(mdl.allocated_fac_rec_hand_cost,0)),0)
  from mst_delivery_legs mdl
  where mdl.plan_id = l_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
		   , wsh_locations wl
                where mt.plan_id = mdl1.plan_id
                and   mt.trip_id  = mdl1.trip_id
                and   mt.mode_of_transport = l_mode
                and   mt.plan_id = mts.plan_id
                and   mt.trip_id = mts.trip_id
	        and   mts.stop_location_id = wl.wsh_location_id
	        and   wl.state = l_destination_state
                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 = mts.stop_location_id);

Line: 4025


  (select nvl(sum(nvl(mdl.allocated_fac_loading_cost,0)
               + nvl(mdl.allocated_fac_unloading_cost,0)
	       + nvl(mdl.allocated_transport_cost,0)
	       + nvl(mdl.allocated_fac_shp_hand_cost,0)
               + nvl(mdl.allocated_fac_rec_hand_cost,0)),0)
  from mst_deliveries md
  , mst_delivery_legs mdl
  , mst_trips mt
--  , mst_trip_stops mts
  , wsh_locations wl
  where mt.plan_id = l_plan_id
  and mt.mode_of_transport = l_mode
  and mt.plan_id = mdl.plan_id
  and mt.trip_id = mdl.trip_id
  and mdl.plan_id = md.plan_id
  and mdl.delivery_id = md.delivery_id
  and wl.state = l_destination_state
  and md.dropoff_location_id = wl.wsh_location_id );

Line: 4056


  select nvl(sum(nvl(mdl.allocated_fac_loading_cost,0)
               + nvl(mdl.allocated_fac_unloading_cost,0)
	       + nvl(mdl.allocated_transport_cost,0)
	       + nvl(mdl.allocated_fac_shp_hand_cost,0)
               + nvl(mdl.allocated_fac_rec_hand_cost,0)),0)
  from mst_delivery_legs mdl
  where mdl.plan_id = l_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
		   , fte_location_parameters flp
		   , wsh_locations wl
                where mt.plan_id = mdl1.plan_id
                and   mt.trip_id  = mdl1.trip_id
                and   mt.mode_of_transport = l_mode
                and   mt.plan_id = mts.plan_id
                and   mt.trip_id = mts.trip_id
                and   mts.stop_location_id = flp.location_id
                and   flp.facility_id = l_myfac_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 = flp.location_id
		and   flp.location_id = wl.wsh_location_id
		and   wl.state = l_destination_state);

Line: 4087


  (select nvl(sum(nvl(mdl.allocated_fac_loading_cost,0)
               + nvl(mdl.allocated_fac_unloading_cost,0)
	       + nvl(mdl.allocated_transport_cost,0)
	       + nvl(mdl.allocated_fac_shp_hand_cost,0)
               + nvl(mdl.allocated_fac_rec_hand_cost,0)),0)
  from mst_deliveries md
  , mst_delivery_legs mdl
  , mst_trips mt
--  , mst_trip_stops mts
  , wsh_locations wl
  , fte_location_parameters flp
  where mt.plan_id = l_plan_id
  and mt.mode_of_transport = l_mode
  and mt.plan_id = mdl.plan_id
  and mt.trip_id = mdl.trip_id
  and mdl.plan_id = md.plan_id
  and mdl.delivery_id = md.delivery_id
--  and mts.stop_location_id = wl.wsh_location_id
  and wl.state = l_destination_state
  and md.dropoff_location_id = wsh_location_id
--  and mt.plan_id = mts.plan_id
--  and mt.trip_id = mts.trip_id
  and flp.facility_id = l_myfac_id
  and md.dropoff_location_id = flp.location_id );

Line: 4114


  select nvl(sum(nvl(mdl.allocated_fac_loading_cost,0)
               + nvl(mdl.allocated_fac_unloading_cost,0)
	       + nvl(mdl.allocated_transport_cost,0)
	       + nvl(mdl.allocated_fac_shp_hand_cost,0)
               + nvl(mdl.allocated_fac_rec_hand_cost,0)),0)
  from mst_delivery_legs mdl
  , mst_trips mt
  where mt.plan_id = l_plan_id
  and mt.mode_of_transport = l_mode
  and mt.plan_id = mdl.plan_id
  and mt.trip_id = mdl.trip_id
  and mdl.delivery_id in (select mdl.delivery_id
                         from mst_deliveries md
                         , mst_delivery_legs mdl
                         , mst_trip_stops mts
                         , fte_location_parameters flp
                         , wsh_locations wl
                         where mdl.plan_id = l_plan_id
                         and mdl.plan_id = mts.plan_id
                         and mdl.trip_id = mts.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 = flp.location_id
                         and flp.facility_id = l_myfac_id
                         and mdl.plan_id = md.plan_id
                         and mdl.delivery_id = md.delivery_id
                         and md.dropoff_location_id = wl.wsh_location_id
                         and wl.state = l_destination_state);

Line: 4149


  select nvl(sum(nvl(mdl.allocated_fac_loading_cost,0)
               + nvl(mdl.allocated_fac_unloading_cost,0)
	       + nvl(mdl.allocated_transport_cost,0)
	       + nvl(mdl.allocated_fac_shp_hand_cost,0)
               + nvl(mdl.allocated_fac_rec_hand_cost,0)),0)
  from mst_delivery_legs mdl
  where mdl.plan_id = l_plan_id
  and mdl.delivery_id in
             ( select md.delivery_id
               from mst_trips mt
                , mst_trip_stops mts
                , mst_delivery_legs mdl1
                , mst_deliveries md
		, wsh_locations wl
                where mt.plan_id = mdl1.plan_id
                and   mt.trip_id  = mdl1.trip_id
                and   mt.mode_of_transport = l_mode
                and   mt.plan_id = mts.plan_id
                and   mt.trip_id = mts.trip_id
                and   mts.stop_location_id = wl.wsh_location_id
	        and   wl.state = l_destination_state
                and   md.plan_id = mdl1.plan_id
                and   md.delivery_id  = mdl1.delivery_id
                and   md.plan_id = mdl.plan_id
                and   decode(l_c_s_ident, 2, md.customer_id, md.supplier_id) = l_cust_supp_id
                and   md.dropoff_location_id = mts.stop_location_id);

Line: 4177


  (select nvl(sum(nvl(mdl.allocated_fac_loading_cost,0)
               + nvl(mdl.allocated_fac_unloading_cost,0)
	       + nvl(mdl.allocated_transport_cost,0)
	       + nvl(mdl.allocated_fac_shp_hand_cost,0)
               + nvl(mdl.allocated_fac_rec_hand_cost,0)),0)
  from mst_deliveries md
  , mst_delivery_legs mdl
  , mst_trips mt
--  , mst_trip_stops mts
  , wsh_locations wl
  where mt.plan_id = l_plan_id
  and mt.mode_of_transport = l_mode
  and mt.plan_id = mdl.plan_id
  and mt.trip_id = mdl.trip_id
  and mdl.plan_id = md.plan_id
  and mdl.delivery_id = md.delivery_id
--  and mts.stop_location_id = wl.wsh_location_id
  and wl.state = l_destination_state
  and md.dropoff_location_id = wl.wsh_location_id
--  and mt.plan_id = mts.plan_id
--  and mt.trip_id = mts.trip_id
  and decode(l_c_s_ident, 2, md.customer_id, md.supplier_id) = l_cust_supp_id );

Line: 4207


  select nvl(sum(decode(mt.mode_of_transport
                       , 'TRUCK', (nvl(mt.total_basic_transport_cost,0)
                                 + nvl(mt.total_stop_cost,0)
		                 + nvl(mt.total_load_unload_cost,0)
			         + nvl(mt.total_layover_cost,0)
			         + nvl(mt.total_accessorial_cost,0)
			         + nvl(mt.total_handling_cost,0))
		       , (nvl(mt.total_basic_transport_cost,0)
		        + nvl(mt.total_accessorial_cost,0)))),0)
  from mst_trips mt
  where mt.plan_id = l_plan_id
  and mt.carrier_id = l_carrier_id
  and mt.mode_of_transport = l_mode
--  and mt.continuous_move_id is null --check
  and   mt.trip_id in
                   (select distinct mts.trip_id
                    from mst_trip_stops mts
                    , mst_delivery_legs mdl
                    , mst_deliveries md
                    , wsh_locations wl
                    where md.plan_id = mt.plan_id
                    and mts.plan_id = md.plan_id
                    and mdl.plan_id = md.plan_id
                    and mdl.delivery_id = md.delivery_id
                    and mdl.trip_id = mts.trip_id
                    and mts.stop_id = mdl.drop_off_stop_id
                    and mts.stop_location_id = md.dropoff_location_id
		    and wl.wsh_location_id = mts.stop_location_id
		    and wl.state = l_destination_state);

Line: 4237


	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 )
	from  mst_deliveries md
	, mst_delivery_legs mdl
	, mst_trips mt
	, wsh_locations wl
	, mst_trip_stops mts
	where md.plan_id = l_plan_id
	and md.plan_id = mt.plan_id
	and md.plan_id = mdl.plan_id
	and md.plan_id = mts.plan_id
	and md.delivery_id = mdl.delivery_id
	and mt.trip_id = mdl.trip_id
	and mt.carrier_id = l_carrier_id
	and mt.mode_of_transport = l_mode
	and mts.trip_id = mt.trip_id
	and mts.stop_location_id = wl.wsh_location_id
	and mdl.drop_off_stop_id = mts.stop_id
	and wl.state = l_destination_state;

Line: 4296


  select count(distinct 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_trips t -- Removing the join with mst_trips
            mst_trip_stops ts
            , mst_delivery_legs mdl
            , fte_location_parameters flp
         where mdl.plan_id = md.plan_id
         and ts.plan_id  = mdl.plan_id
--         and ts.stop_id  = mdl.pick_up_stop_id
-- Bug_Fix for 3693945
         and ( ts.stop_id = mdl.pick_up_stop_id
             or ts.stop_id = mdl.drop_off_stop_id )
         and ts.stop_location_id = flp.location_id
	 and flp.facility_id = l_myfac_id ) -- ending the subquery here, with the join with mst_trips removed
--         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 = l_plan_id
  and   mdd.container_flag = 2;

Line: 4330


  select count(distinct dd.source_header_number)
  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 = l_plan_id
        and   decode(l_c_s_ident, 2, md.customer_id, md.supplier_id) = l_cust_supp_id
        and   md.delivery_id in
                    (select mdl.delivery_id
                     from  mst_delivery_legs mdl
                         , mst_trip_stops mts
			 , fte_location_parameters flp
                     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   flp.location_id = mts.stop_location_id
		     and   flp.facility_id = l_myfac_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 = l_plan_id
  and   decode(l_c_s_ident, 2, md.customer_id, md.supplier_id) = l_cust_supp_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;

Line: 4371


  select count(distinct 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_trips t
            , mst_trip_stops ts
            , mst_delivery_legs mdl
            , fte_location_parameters flp
         where mdl.plan_id = md.plan_id
         and ts.plan_id  = mdl.plan_id
--         and ts.stop_id  = mdl.pick_up_stop_id
-- Bug_Fix for 3693945
         and ( ts.stop_id = mdl.pick_up_stop_id
             or ts.stop_id = mdl.drop_off_stop_id )
         and ts.stop_location_id = flp.location_id
	 and flp.facility_id = l_myfac_id
         and ts.plan_id  = t.plan_id
         and ts.trip_id  = t.trip_id
	 and t.carrier_id = l_carrier_id)
  and   mda.plan_id = mdd.plan_id
  and   mda.delivery_detail_id = mdd.delivery_detail_id
  and   md.plan_id = l_plan_id
  and   mdd.container_flag = 2;

Line: 4431


  select nvl(sum(nvl(md.gross_weight,0)),0)
  from mst_deliveries md
     , fte_location_parameters flp
  where md.plan_id = l_plan_id
  and (md.pickup_location_id = flp.location_id
      or md.dropoff_location_id = flp.location_id)
  and flp.facility_id = l_myfac_id;

Line: 4444


  select nvl(sum(nvl(md.gross_weight,0)),0)
  from mst_deliveries md
  , fte_location_parameters flp
  where md.plan_id = l_plan_id
  and (md.pickup_location_id = flp.location_id
      or md.dropoff_location_id = flp.location_id)
  and flp.facility_id = l_myfac_id
  and decode(l_c_s_ident, 2, md.customer_id, md.supplier_id) = l_cust_supp_id;

Line: 4456


  select nvl(sum(nvl(md.gross_weight,0)),0)
  from mst_deliveries md
  , fte_location_parameters flp
  where md.plan_id = l_plan_id
  and (md.pickup_location_id = flp.location_id
      or md.dropoff_location_id = flp.location_id)
  and flp.facility_id = l_myfac_id
  and md.delivery_id in (select distinct mdl.delivery_id
                         from mst_delivery_legs mdl
			 , mst_trips mt
			 where mdl.plan_id = md.plan_id
			 and mt.plan_id = mdl.plan_id
			 and mt.trip_id = mdl.trip_id
			 and mt.carrier_id = l_carrier_id);

Line: 4502


  select nvl(sum(nvl(md.volume,0)),0)
  from mst_deliveries md
     , fte_location_parameters flp
  where md.plan_id = l_plan_id
  and (md.pickup_location_id = flp.location_id
      or md.dropoff_location_id = flp.location_id)
  and flp.facility_id = l_myfac_id;

Line: 4515


  select nvl(sum(nvl(md.volume,0)),0)
  from mst_deliveries md
  , fte_location_parameters flp
  where md.plan_id = l_plan_id
  and (md.pickup_location_id = flp.location_id
      or md.dropoff_location_id = flp.location_id)
  and flp.facility_id = l_myfac_id
  and decode(l_c_s_ident, 2, md.customer_id, md.supplier_id) = l_cust_supp_id;

Line: 4527


  select nvl(sum(nvl(md.volume,0)),0)
  from mst_deliveries md
  , fte_location_parameters flp
  where md.plan_id = l_plan_id
  and (md.pickup_location_id = flp.location_id
      or md.dropoff_location_id = flp.location_id)
  and flp.facility_id = l_myfac_id
  and md.delivery_id in (select distinct mdl.delivery_id
                         from mst_delivery_legs mdl
			 , mst_trips mt
			 where mdl.plan_id = md.plan_id
			 and mt.plan_id = mdl.plan_id
			 and mt.trip_id = mdl.trip_id
			 and mt.carrier_id = l_carrier_id);

Line: 4573


  select nvl(sum(nvl(md.number_of_pieces,0)),0)
  from mst_deliveries md
     , fte_location_parameters flp
  where md.plan_id = l_plan_id
  and (md.pickup_location_id = flp.location_id
      or md.dropoff_location_id = flp.location_id)
  and flp.facility_id = l_myfac_id;

Line: 4586


  select nvl(sum(nvl(md.number_of_pieces,0)),0)
  from mst_deliveries md
  , fte_location_parameters flp
  where md.plan_id = l_plan_id
  and (md.pickup_location_id = flp.location_id
      or md.dropoff_location_id = flp.location_id)
  and flp.facility_id = l_myfac_id
  and decode(l_c_s_ident, 2, md.customer_id, md.supplier_id) = l_cust_supp_id;

Line: 4598


  select nvl(sum(nvl(md.number_of_pieces,0)),0)
  from mst_deliveries md
  , fte_location_parameters flp
  where md.plan_id = l_plan_id
  and (md.pickup_location_id = flp.location_id
      or md.dropoff_location_id = flp.location_id)
  and flp.facility_id = l_myfac_id
  and md.delivery_id in (select distinct mdl.delivery_id
                         from mst_delivery_legs mdl
			 , mst_trips mt
			 where mdl.plan_id = md.plan_id
			 and mt.plan_id = mdl.plan_id
			 and mt.trip_id = mdl.trip_id
			 and mt.carrier_id = l_carrier_id);

Line: 4645


  select count(*)
  from mst_trips mt
  , fte_location_parameters flp
  where mt.plan_id = l_plan_id
  and mt.mode_of_transport = l_mode
  and flp.location_id in (select mts.stop_location_id
                          from mst_trip_stops mts
			  where mts.plan_id = mt.plan_id
			  and mts.trip_id = mt.trip_id)
  and flp.facility_id = l_myfac_id;

Line: 4661


  select count(mt.trip_id)
  from mst_trips mt
  where mt.plan_id = l_plan_id
  and   mt.trip_id in
                   (select distinct mts.trip_id
                    from mst_trip_stops mts
                    , mst_delivery_legs mdl
                    , mst_deliveries md
		    , fte_location_parameters flp
                    where md.plan_id = mt.plan_id
                    and decode(l_c_s_ident, 2, md.customer_id, md.supplier_id) = l_cust_supp_id
                    and mts.plan_id = md.plan_id
                    and mts.stop_location_id = md.dropoff_location_id
		    and flp.location_id = mts.stop_location_id
		    and flp.facility_id = l_myfac_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 = l_mode
  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);

Line: 4693


  select count(*)
  from mst_trips mt
  , fte_location_parameters flp
  where mt.plan_id = l_plan_id
  and mt.mode_of_transport = l_mode
  and mt.carrier_id = l_carrier_id
  and flp.location_id in (select mts.stop_location_id
                          from mst_trip_stops mts
			  where mts.plan_id = mt.plan_id
			  and mts.trip_id = mt.trip_id)
  and flp.facility_id = l_myfac_id;

Line: 4741


  select nvl(sum(nvl(mdl.allocated_fac_loading_cost,0)
               + nvl(mdl.allocated_fac_unloading_cost,0)
	       + nvl(mdl.allocated_transport_cost,0)
	       + nvl(mdl.allocated_fac_shp_hand_cost,0)
               + nvl(mdl.allocated_fac_rec_hand_cost,0)),0)
  from mst_delivery_legs mdl
  where mdl.plan_id = l_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
		   , fte_location_parameters flp
                where mt.plan_id = mdl1.plan_id
                and   mt.trip_id  = mdl1.trip_id
                and   mt.mode_of_transport = l_mode
                and   mt.plan_id = mts.plan_id
                and   mt.trip_id = mts.trip_id
                and   mts.stop_location_id = flp.location_id
                and   flp.facility_id = l_myfac_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 = flp.location_id
                       or md.dropoff_location_id = flp.location_id));

Line: 4769


  (select nvl(sum(nvl(mdl.allocated_fac_loading_cost,0)
               + nvl(mdl.allocated_fac_unloading_cost,0)
	       + nvl(mdl.allocated_transport_cost,0)
	       + nvl(mdl.allocated_fac_shp_hand_cost,0)
               + nvl(mdl.allocated_fac_rec_hand_cost,0)),0)
  from mst_deliveries md
  , mst_delivery_legs mdl
  , mst_trips mt
  , fte_location_parameters flp
  where mt.plan_id = l_plan_id
  and mt.mode_of_transport = l_mode
  and mt.plan_id = mdl.plan_id
  and mt.trip_id = mdl.trip_id
  and mdl.plan_id = md.plan_id
  and mdl.delivery_id = md.delivery_id
  and flp.facility_id = l_myfac_id
  and ( md.pickup_location_id = flp.location_id
        or md.dropoff_location_id = flp.location_id ) );

Line: 4794


  select nvl(sum(nvl(mdl.allocated_fac_loading_cost,0)
               + nvl(mdl.allocated_fac_unloading_cost,0)
	       + nvl(mdl.allocated_transport_cost,0)
	       + nvl(mdl.allocated_fac_shp_hand_cost,0)
               + nvl(mdl.allocated_fac_rec_hand_cost,0)),0)
  from mst_delivery_legs mdl
  where mdl.plan_id = l_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
		   , fte_location_parameters flp
                where mt.plan_id = mdl1.plan_id
                and   mt.trip_id  = mdl1.trip_id
                and   mt.mode_of_transport = l_mode
                and   mt.plan_id = mts.plan_id
                and   mt.trip_id = mts.trip_id
                and   mts.stop_location_id = flp.location_id
		and   flp.facility_id = l_myfac_id
                and   md.plan_id = mdl1.plan_id
                and   md.delivery_id  = mdl1.delivery_id
                and   md.plan_id = mdl.plan_id
                and   decode(l_c_s_ident, 2, md.customer_id, md.supplier_id) = l_cust_supp_id
                and   (md.pickup_location_id = flp.location_id
		       or md.dropoff_location_id = flp.location_id));

Line: 4823


  (select nvl(sum(nvl(mdl.allocated_fac_loading_cost,0)
               + nvl(mdl.allocated_fac_unloading_cost,0)
	       + nvl(mdl.allocated_transport_cost,0)
	       + nvl(mdl.allocated_fac_shp_hand_cost,0)
               + nvl(mdl.allocated_fac_rec_hand_cost,0)),0)
  from mst_deliveries md
  , mst_delivery_legs mdl
  , mst_trips mt
  , fte_location_parameters flp
  where mt.plan_id = l_plan_id
  and mt.mode_of_transport = l_mode
  and mt.plan_id = mdl.plan_id
  and mt.trip_id = mdl.trip_id
  and mdl.plan_id = md.plan_id
  and mdl.delivery_id = md.delivery_id
  and flp.facility_id = l_myfac_id
  and decode(l_c_s_ident, 2, md.customer_id, md.supplier_id) = l_cust_supp_id
  and ( md.pickup_location_id = flp.location_id
        or md.dropoff_location_id = flp.location_id ) );

Line: 4847


  select nvl(sum(decode(mt.mode_of_transport
                       , 'TRUCK', (nvl(mt.total_basic_transport_cost,0)
                                 + nvl(mt.total_stop_cost,0)
		                 + nvl(mt.total_load_unload_cost,0)
			         + nvl(mt.total_layover_cost,0)
			         + nvl(mt.total_accessorial_cost,0)
			         + nvl(mt.total_handling_cost,0))
		       , (nvl(mt.total_basic_transport_cost,0)
		        + nvl(mt.total_accessorial_cost,0)))),0)
  from mst_trips mt
  where mt.plan_id = l_plan_id
  and mt.carrier_id = l_carrier_id
  and mt.mode_of_transport = l_mode
--  and mt.continuous_move_id is null  --check
  and mt.trip_id in
                   (select distinct mts.trip_id
                    from mst_trip_stops mts
                    , mst_delivery_legs mdl
                    , mst_deliveries md
                    , fte_location_parameters flp
                    where md.plan_id = mt.plan_id
                    and mts.plan_id = md.plan_id
                    and mdl.plan_id = md.plan_id
                    and mdl.delivery_id = md.delivery_id
                    and mdl.trip_id = mts.trip_id
                    and mts.stop_location_id = flp.location_id
		    and (md.pickup_location_id = flp.location_id
		        or md.dropoff_location_id = flp.location_id)
		    and flp.facility_id = l_myfac_id);

Line: 4882


 select sum(nvl(mdl.allocated_fac_loading_cost,0)  +
            nvl(mdl.allocated_transport_cost,0)    +
            nvl(mdl.allocated_fac_shp_hand_cost,0)  )
 from mst_delivery_legs mdl
 where mdl.plan_id = l_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 = l_mode
                and   mt.plan_id = mts.plan_id
                and   mt.trip_id = mts.trip_id
                and   mt.carrier_id = l_carrier_id
                and   mts.stop_location_id = l_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 = mts.stop_location_id
                     or md.dropoff_location_id = mts.stop_location_id));

Line: 4907


  (select nvl(sum(nvl(mdl.allocated_fac_loading_cost,0)
               + nvl(mdl.allocated_fac_unloading_cost,0)
	       + nvl(mdl.allocated_transport_cost,0)
	       + nvl(mdl.allocated_fac_shp_hand_cost,0)
               + nvl(mdl.allocated_fac_rec_hand_cost,0)),0)
  from mst_deliveries md
  , mst_delivery_legs mdl
  , mst_trips mt
  , mst_trip_stops mts
  where mt.plan_id = l_plan_id
  and mt.mode_of_transport = l_mode
  and mt.plan_id = mdl.plan_id
  and mt.trip_id = mdl.trip_id
  and mt.carrier_id = l_carrier_id
  and mts.stop_location_id = l_location_id
  and mdl.plan_id = md.plan_id
  and mdl.delivery_id = md.delivery_id
  and mt.plan_id = mts.plan_id
  and mt.trip_id = mts.trip_id
  and ( md.pickup_location_id = mts.stop_location_id
        or md.dropoff_location_id = mts.stop_location_id ) );

Line: 4935


  select sum(nvl(mdl.allocated_fac_loading_cost,0)  +
             nvl(mdl.allocated_transport_cost,0)    +
             nvl(mdl.allocated_fac_shp_hand_cost,0)  )
  from mst_delivery_legs mdl
  where mdl.plan_id = l_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 = l_mode
                and   mt.plan_id = mts.plan_id
                and   mt.trip_id = mts.trip_id
		and   mt.carrier_id = l_carrier_id
                and   mts.stop_location_id = l_location_id
                and   (mts.stop_id = mdl.pick_up_stop_id
                      or 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);

Line: 4962


  (select nvl(sum(nvl(mdl.allocated_fac_loading_cost,0)
               + nvl(mdl.allocated_fac_unloading_cost,0)
	       + nvl(mdl.allocated_transport_cost,0)
	       + nvl(mdl.allocated_fac_shp_hand_cost,0)
               + nvl(mdl.allocated_fac_rec_hand_cost,0)),0)
  from mst_deliveries md
  , mst_delivery_legs mdl
  , mst_trips mt
  , mst_trip_stops mts
  where mt.plan_id = l_plan_id
  and mt.mode_of_transport = l_mode
  and mt.plan_id = mdl.plan_id
  and mt.trip_id = mdl.trip_id
  and mt.carrier_id = l_carrier_id
  and mts.stop_location_id = l_location_id
  and mdl.plan_id = md.plan_id
  and mdl.delivery_id = md.delivery_id
  and mt.plan_id = mts.plan_id
  and mt.trip_id = mts.trip_id
  and ( mts.stop_id = mdl.pick_up_stop_id
        or mts.stop_id = mdl.drop_off_stop_id )
  and md.pickup_location_id <> mts.stop_location_id
  and md.dropoff_location_id <> mts.stop_location_id );

Line: 4997


    select flp.location_id
    into l_loc_id
    from fte_location_parameters flp
    where flp.facility_id = p_myfac_id;

Line: 5030


  select count(distinct 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 decode(l_c_s_ident, 2, md.customer_id, md.supplier_id) = l_cust_supp_id
  and md.delivery_id in
        (select mdl.delivery_id
         from -- mst_trips t -- removing this join
              mst_trip_stops ts
            , mst_delivery_legs mdl
            , fte_location_parameters flp
         where mdl.plan_id = md.plan_id
         and ts.plan_id  = mdl.plan_id
--         and ts.stop_id  = mdl.pick_up_stop_id
-- Bug_Fix for 3694008
         and ( ts.stop_id = mdl.pick_up_stop_id
             or ts.stop_id = mdl.drop_off_stop_id )
         and ts.stop_location_id = flp.location_id
	 and flp.facility_id = l_myfac_id ) -- end of subquery, as the join with t has been removed
         -- 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 = l_plan_id
  and   mdd.container_flag = 2
  and   mdd.split_from_delivery_detail_id is null;

Line: 5066


  select count(distinct dd.source_header_number)
  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 = l_plan_id
        and   decode(l_c_s_ident, 2, md.customer_id, md.supplier_id) = l_cust_supp_id
	and decode ( l_report_for, 2, md.customer_id, 4, md.supplier_id, 0 ) = decode ( l_report_for, 2, l_report_for_id, 4, l_report_for_id, 0 )
        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))
  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 = l_plan_id
  and   decode(l_c_s_ident, 2, md.customer_id, md.supplier_id) = l_cust_supp_id
  and decode ( l_report_for, 2, md.customer_id, 4, md.supplier_id, 0 ) = decode ( l_report_for, 2, l_report_for_id, 4, l_report_for_id, 0 )
  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;

Line: 5107


  select count(distinct dd.source_header_number)
  from (
        select mdd.source_header_number 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 = l_plan_id
        and   decode(l_c_s_ident, 2, md.customer_id, md.supplier_id) = l_cust_supp_id
        and   md.delivery_id in
                    (select mdl.delivery_id
                     from  mst_delivery_legs mdl
                         , mst_trip_stops mts
			 , mst_trips mt
                     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   mdl.trip_id = mts.trip_id
		     and   mt.plan_id = mts.plan_id
		     and   mt.trip_id = mts.trip_id
		     and   mt.carrier_id = l_carrier_id)) dd;

Line: 5168


  select nvl(sum(nvl(md.gross_weight,0)),0)
  from mst_deliveries md
     , fte_location_parameters flp
  where md.plan_id = l_plan_id
  and decode(l_c_s_ident, 2, md.customer_id, md.supplier_id) = l_cust_supp_id
  and (md.pickup_location_id = flp.location_id
      or md.dropoff_location_id = flp.location_id)
  and flp.facility_id = l_myfac_id;

Line: 5183


  select nvl(sum(nvl(md.gross_weight,0)),0)
  from mst_deliveries md
  where md.plan_id = l_plan_id
  and decode ( l_report_for, 2, md.customer_id, 4, md.supplier_id, 0 ) = decode ( l_report_for, 2, l_report_for_id, 4, l_report_for_id, 0 )
  and decode(l_c_s_ident, 2, md.customer_id, md.supplier_id) = l_cust_supp_id;

Line: 5193


  select nvl(sum(nvl(md.gross_weight,0)),0)
  from mst_deliveries md
  where md.plan_id = l_plan_id
  and decode(l_c_s_ident, 2, md.customer_id, md.supplier_id) = l_cust_supp_id
  and md.delivery_id in (select distinct mdl.delivery_id
                         from mst_delivery_legs mdl
			 , mst_trips mt
			 where mdl.plan_id = md.plan_id
			 and mt.plan_id = mdl.plan_id
			 and mt.trip_id = mdl.trip_id
			 and mt.carrier_id = l_carrier_id);

Line: 5240


  select nvl(sum(nvl(md.volume,0)),0)
  from mst_deliveries md
     , fte_location_parameters flp
  where md.plan_id = l_plan_id
  and decode(l_c_s_ident, 2, md.customer_id, md.supplier_id) = l_cust_supp_id
  and (md.pickup_location_id = flp.location_id
      or md.dropoff_location_id = flp.location_id)
  and flp.facility_id = l_myfac_id;

Line: 5255


  select nvl(sum(nvl(md.volume,0)),0)
  from mst_deliveries md
  where md.plan_id = l_plan_id
  and decode ( l_report_for, 2, md.customer_id, 4, md.supplier_id, 0 ) = decode ( l_report_for, 2, l_report_for_id, 4, l_report_for_id, 0 )
  and decode(l_c_s_ident, 2, md.customer_id, md.supplier_id) = l_cust_supp_id;

Line: 5265


  select nvl(sum(nvl(md.volume,0)),0)
  from mst_deliveries md
  where md.plan_id = l_plan_id
  and decode(l_c_s_ident, 2, md.customer_id, md.supplier_id) = l_cust_supp_id
  and md.delivery_id in (select distinct mdl.delivery_id
                         from mst_delivery_legs mdl
			 , mst_trips mt
			 where mdl.plan_id = md.plan_id
			 and mt.plan_id = mdl.plan_id
			 and mt.trip_id = mdl.trip_id
			 and mt.carrier_id = l_carrier_id);

Line: 5311


  select nvl(sum(nvl(md.number_of_pieces,0)),0)
  from mst_deliveries md
     , fte_location_parameters flp
  where md.plan_id = l_plan_id
  and decode(l_c_s_ident, 2, md.customer_id, md.supplier_id) = l_cust_supp_id
  and (md.pickup_location_id = flp.location_id
      or md.dropoff_location_id = flp.location_id)
  and flp.facility_id = l_myfac_id;

Line: 5326


  select nvl(sum(nvl(md.number_of_pieces,0)),0)
  from mst_deliveries md
  where md.plan_id = l_plan_id
  and decode ( l_report_for, 2, md.customer_id, 4, md.supplier_id, 0 ) = decode ( l_report_for, 2, l_report_for_id, 4, l_report_for_id, 0 )
  and decode(l_c_s_ident, 2, md.customer_id, md.supplier_id) = l_cust_supp_id;

Line: 5336


  select nvl(sum(nvl(md.number_of_pieces,0)),0)
  from mst_deliveries md
  where md.plan_id = l_plan_id
  and decode(l_c_s_ident, 2, md.customer_id, md.supplier_id) = l_cust_supp_id
  and md.delivery_id in (select distinct mdl.delivery_id
                         from mst_delivery_legs mdl
			 , mst_trips mt
			 where mdl.plan_id = md.plan_id
			 and mt.plan_id = mdl.plan_id
			 and mt.trip_id = mdl.trip_id
			 and mt.carrier_id = l_carrier_id);

Line: 5384


  select count(*)
  from mst_trips mt
  , fte_location_parameters flp
  where mt.plan_id = l_plan_id
  and mt.mode_of_transport = l_mode
  and flp.location_id in (select distinct mts.stop_location_id
                          from mst_trip_stops mts
			  , mst_delivery_legs mdl
			  , mst_deliveries md
			  where mts.plan_id = mt.plan_id
			  and mts.trip_id = mt.trip_id
			  and mdl.plan_id = mts.plan_id
			  and mdl.trip_id = mts.trip_id
			  and md.plan_id = mdl.plan_id
			  and md.delivery_id = mdl.delivery_id
			  and decode(l_c_s_ident, 2, md.customer_id, md.supplier_id) = l_cust_supp_id)
  and flp.facility_id = l_myfac_id;

Line: 5408


  select count(mt.trip_id)
  from mst_trips mt
  where mt.plan_id = l_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 decode ( l_report_for, 2, md.customer_id, 4, md.supplier_id, 0 ) = decode ( l_report_for, 2, l_report_for_id, 4, l_report_for_id, 0 )
                    and decode(l_c_s_ident, 2, md.customer_id, md.supplier_id) = l_cust_supp_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 = l_mode
  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);

Line: 5439


  select count(mt.trip_id)
  from mst_trips mt
  where mt.plan_id = l_plan_id
  and   mt.carrier_id = l_carrier_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 decode(l_c_s_ident, 2, md.customer_id, md.supplier_id) = l_cust_supp_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 = l_mode
  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);

Line: 5502


  select nvl(sum(nvl(mdl.allocated_fac_loading_cost,0)
               + nvl(mdl.allocated_fac_unloading_cost,0)
	       + nvl(mdl.allocated_transport_cost,0)
	       + nvl(mdl.allocated_fac_shp_hand_cost,0)
               + nvl(mdl.allocated_fac_rec_hand_cost,0)),0)
  from mst_delivery_legs mdl
  where mdl.plan_id = l_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
		   , fte_location_parameters flp
                where mt.plan_id = mdl1.plan_id
                and   mt.trip_id  = mdl1.trip_id
                and   mt.mode_of_transport = l_mode
                and   mt.plan_id = mts.plan_id
                and   mt.trip_id = mts.trip_id
                and   mts.stop_location_id = flp.location_id
		and   flp.facility_id = l_myfac_id
                and   md.plan_id = mdl1.plan_id
                and   md.delivery_id  = mdl1.delivery_id
                and   md.plan_id = mdl.plan_id
                and   decode(l_c_s_ident, 2, md.customer_id, md.supplier_id) = l_cust_supp_id
                and   (md.pickup_location_id = flp.location_id
		       or md.dropoff_location_id = flp.location_id));

Line: 5531


  (select nvl(sum(nvl(mdl.allocated_fac_loading_cost,0)
               + nvl(mdl.allocated_fac_unloading_cost,0)
	       + nvl(mdl.allocated_transport_cost,0)
	       + nvl(mdl.allocated_fac_shp_hand_cost,0)
               + nvl(mdl.allocated_fac_rec_hand_cost,0)),0)
  from mst_deliveries md
  , mst_delivery_legs mdl
  , mst_trips mt
  , fte_location_parameters flp
  where mt.plan_id = l_plan_id
  and mt.mode_of_transport = l_mode
  and mt.plan_id = mdl.plan_id
  and mt.trip_id = mdl.trip_id
  and mdl.plan_id = md.plan_id
  and mdl.delivery_id = md.delivery_id
  and flp.facility_id = l_myfac_id
  and decode(l_c_s_ident, 2, md.customer_id, md.supplier_id) = l_cust_supp_id
  and ( md.pickup_location_id = flp.location_id
        or md.dropoff_location_id = flp.location_id ) );

Line: 5558


  select nvl(sum(nvl(mdl.allocated_fac_loading_cost,0)
               + nvl(mdl.allocated_fac_unloading_cost,0)
	       + nvl(mdl.allocated_transport_cost,0)
	       + nvl(mdl.allocated_fac_shp_hand_cost,0)
               + nvl(mdl.allocated_fac_rec_hand_cost,0)),0)
  from mst_delivery_legs mdl
  where mdl.plan_id = l_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 = l_mode
                and   mt.plan_id = mts.plan_id
                and   mt.trip_id = mts.trip_id
                and   md.plan_id = mdl1.plan_id
                and   md.delivery_id  = mdl1.delivery_id
                and   md.plan_id = mdl.plan_id
  		and decode ( l_report_for, 2, md.customer_id, 4, md.supplier_id, 0 ) = decode ( l_report_for, 2, l_report_for_id, 4, l_report_for_id, 0 )
                and   decode(l_c_s_ident, 2, md.customer_id, md.supplier_id) = l_cust_supp_id
                and   (md.pickup_location_id = mts.stop_location_id
		       or md.dropoff_location_id = mts.stop_location_id));

Line: 5585


  (select nvl(sum(nvl(mdl.allocated_fac_loading_cost,0)
               + nvl(mdl.allocated_fac_unloading_cost,0)
	       + nvl(mdl.allocated_transport_cost,0)
	       + nvl(mdl.allocated_fac_shp_hand_cost,0)
               + nvl(mdl.allocated_fac_rec_hand_cost,0)),0)
  from mst_deliveries md
  , mst_delivery_legs mdl
  , mst_trips mt
  where mt.plan_id = l_plan_id
  and mt.mode_of_transport = l_mode
  and mt.plan_id = mdl.plan_id
  and mt.trip_id = mdl.trip_id
  and mdl.plan_id = md.plan_id
  and mdl.delivery_id = md.delivery_id
  and decode ( l_report_for, 2, md.customer_id, 4, md.supplier_id, 0 ) = decode ( l_report_for, 2, l_report_for_id, 4, l_report_for_id, 0 )
  and decode(l_c_s_ident, 2, md.customer_id, md.supplier_id) = l_cust_supp_id );

Line: 5607


/*  select nvl(sum(decode(mt.mode_of_transport
                       , 'TRUCK', (nvl(mt.total_basic_transport_cost,0)
                                 + nvl(mt.total_stop_cost,0)
		                 + nvl(mt.total_load_unload_cost,0)
			         + nvl(mt.total_layover_cost,0)
			         + nvl(mt.total_accessorial_cost,0)
			         + nvl(mt.total_handling_cost,0))
		       , (nvl(mt.total_basic_transport_cost,0)
		        + nvl(mt.total_accessorial_cost,0)))),0)
  from mst_trips mt
  where mt.plan_id = l_plan_id
  and mt.carrier_id = l_carrier_id
  and mt.mode_of_transport = l_mode
--  and mt.continuous_move_id is null  --check
  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 mts.plan_id = md.plan_id
                    and mdl.plan_id = md.plan_id
                    and mdl.delivery_id = md.delivery_id
                    and mdl.trip_id = mts.trip_id
--		    and (md.pickup_location_id = mts.stop_location_id
--		        or md.dropoff_location_id = mts.stop_location_id)
		    and   decode(l_c_s_ident, 2, md.customer_id, md.supplier_id) = l_cust_supp_id);

Line: 5635


 select 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))
  from  mst_deliveries md,
        mst_delivery_legs mdl,
        mst_trips mt
  where md.plan_id = l_plan_id
  and   decode(l_c_s_ident, 2, md.customer_id, md.supplier_id) = l_cust_supp_id
  and   md.delivery_id = mdl.delivery_id
  and   mt.plan_id = mdl.plan_id
  and   mt.trip_id = mdl.trip_id
  and   mt.carrier_id = l_carrier_id
  and   mt.mode_of_transport = l_mode;

Line: 5687


  SELECT count(mt.trip_id) num_stops
  FROM mst_trips mt
  WHERE mt.plan_id = l_plan_id
  AND mt.trip_id IN ( SELECT mdl.trip_id
	                 FROM mst_deliveries md
                         , mst_delivery_legs mdl
                         , mst_trip_stops mts1
                         , fte_location_parameters flp
                         WHERE mdl.plan_id = mt.plan_id
                         AND md.plan_id = mdl.plan_id
                         AND md.delivery_id = mdl.delivery_id
                         AND decode(l_c_s_ident, 2, md.customer_id, md.supplier_id) = l_cust_supp_id
                         AND mts1.plan_id = mdl.plan_id
                         AND mts1.trip_id = mdl.trip_id
                         -- and mts1.stop_id = mdl.pick_up_stop_id
                         -- Bug_Fix for 3694008
                         AND ( mts1.stop_id = mdl.pick_up_stop_id
                             OR mts1.stop_id = mdl.drop_off_stop_id )
                         AND flp.location_id = mts1.stop_location_id
                         AND flp.facility_id = l_myfac_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 );

Line: 5716


  SELECT COUNT(*)
  FROM (SELECT DISTINCT mt.trip_id, count(*) num_stops
        FROM mst_trips mt
           , mst_trip_stops mts
	    WHERE mt.plan_id = l_plan_id
	    AND mt.trip_id in (SELECT DISTINCT mdl.trip_id
	                       FROM   mst_deliveries md
			                    , mst_delivery_legs mdl
			                    , mst_trip_stops mts1
                                , fte_location_parameters flp
			               WHERE mdl.plan_id = mt.plan_id
			               AND md.plan_id = mdl.plan_id
			               AND md.delivery_id = mdl.delivery_id
			               AND decode(l_c_s_ident, 2, md.customer_id, md.supplier_id) = l_cust_supp_id
                           AND mts1.plan_id = mdl.plan_id
			               AND mts1.trip_id = mdl.trip_id
                           --			   and mts1.stop_id = mdl.pick_up_stop_id
                           -- Bug_Fix for 3694008
                           AND ( mts1.stop_id = mdl.pick_up_stop_id
                                OR mts1.stop_id = mdl.drop_off_stop_id )
                           AND flp.location_id = mts1.stop_location_id
                           AND flp.facility_id = l_myfac_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;

Line: 5750


  select count(mt.trip_id)
  from mst_trips mt
  where mt.plan_id = l_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 decode ( l_report_for, 2, md.customer_id, 4, md.supplier_id, 0 ) = decode ( l_report_for, 2, l_report_for_id, 4, l_report_for_id, 0 )
                    and decode(l_c_s_ident, 2, md.customer_id, md.supplier_id) = l_cust_supp_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);

Line: 5780


  select count(mt.trip_id)
  from mst_trips mt
  where mt.plan_id = l_plan_id
  and   mt.carrier_id = l_carrier_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 decode(l_c_s_ident, 2, md.customer_id, md.supplier_id) = l_cust_supp_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);

Line: 5841


  SELECT count(mt.trip_id) num_stops
  FROM mst_trips mt
  WHERE mt.plan_id = l_plan_id
  AND mt.trip_id IN ( SELECT mdl.trip_id
	                 FROM mst_deliveries md
                         , mst_delivery_legs mdl
                         , mst_trip_stops mts1
                         , fte_location_parameters flp
                         WHERE mdl.plan_id = mt.plan_id
                         AND md.plan_id = mdl.plan_id
                         AND md.delivery_id = mdl.delivery_id
                         AND decode(l_c_s_ident, 2, md.customer_id, md.supplier_id) = l_cust_supp_id
                         AND mts1.plan_id = mdl.plan_id
                         AND mts1.trip_id = mdl.trip_id
                         -- and mts1.stop_id = mdl.pick_up_stop_id
                         -- Bug_Fix for 3694008
                         AND ( mts1.stop_id = mdl.pick_up_stop_id
                             OR mts1.stop_id = mdl.drop_off_stop_id )
                         AND flp.location_id = mts1.stop_location_id
                         AND flp.facility_id = l_myfac_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 );

Line: 5870


  select count(*)
  from (select distinct mt.trip_id, count(*) num_stops
        from mst_trips mt
        , mst_trip_stops mts
	where mt.plan_id = l_plan_id
	and mt.trip_id in (select distinct mdl.trip_id
	                   from mst_deliveries md
			   , mst_delivery_legs mdl
			   , mst_trip_stops mts1
                           , fte_location_parameters flp
			   where mdl.plan_id = mt.plan_id
			   and md.plan_id = mdl.plan_id
			   and md.delivery_id = mdl.delivery_id
			   and decode(l_c_s_ident, 2, md.customer_id, md.supplier_id) = l_cust_supp_id
                           and mts1.plan_id = mdl.plan_id
			   and mts1.trip_id = mdl.trip_id
--			   and mts1.stop_id = mdl.pick_up_stop_id
-- Bug_Fix for 3694008
                           and ( mts1.stop_id = mdl.pick_up_stop_id
                               or mts1.stop_id = mdl.drop_off_stop_id )
                           and flp.location_id = mts1.stop_location_id
                           and flp.facility_id = l_myfac_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;

Line: 5904


  select count(mt.trip_id)
  from mst_trips mt
  where mt.plan_id = l_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 decode ( l_report_for, 2, md.customer_id, 4, md.supplier_id, 0 ) = decode ( l_report_for, 2, l_report_for_id, 4, l_report_for_id, 0 )
                    and decode(l_c_s_ident, 2, md.customer_id, md.supplier_id) = l_cust_supp_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);

Line: 5934


  select count(mt.trip_id)
  from mst_trips mt
  where mt.plan_id = l_plan_id
  and   mt.carrier_id = l_carrier_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 decode(l_c_s_ident, 2, md.customer_id, md.supplier_id) = l_cust_supp_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);

Line: 5991


  insert into mst_mast_sum_report_temp_gt
  ( view_id
  , plan_id
  , compile_designator
  , report_date
  , plan_start_date
  , plan_end_date
  , orders
  , order_groups
  , weight
  , volume
  , pieces
  , pallets
  , allocated_cost  ---not used anymore
  , plan_value
  , total_tl_count
  , ltl_count
  , parcel_count
  , tl_cost
  , ltl_cost
  , parcel_cost
  , transportation_cost
  , handling_cost
  , total_cost
  , tl_stops
  , tl_distance
  , percent_alloc_cost  ---not used anymore
  , percent_value
  )
 (SELECT 1
  , mp.plan_id
  , mp.compile_designator
  , sysdate
  , mp.start_date
  , mp.cutoff_date
  , mst_reports_pkg.get_plan_order_count(p_plan_id, p_report_for, p_report_for_id)
  , mst_reports_pkg.get_order_group_count(p_plan_id, p_report_for, p_report_for_id)
  , mst_reports_pkg.get_weight(p_plan_id, p_report_for, p_report_for_id)
  , mst_reports_pkg.get_volume(p_plan_id, p_report_for, p_report_for_id)
  , mst_reports_pkg.get_pieces(p_plan_id, p_report_for, p_report_for_id)
  , 0
  , 0
  , mst_reports_pkg.get_plan_value(p_plan_id, p_report_for, p_report_for_id)
  , mst_reports_pkg.get_trips_per_mode(p_plan_id, p_report_for, p_report_for_id,'TRUCK')
  , mst_reports_pkg.get_trips_per_mode(p_plan_id, p_report_for, p_report_for_id,'LTL')
  , mst_reports_pkg.get_trips_per_mode(p_plan_id, p_report_for, p_report_for_id,'PARCEL')
  , mst_reports_pkg.get_trans_cost_per_mode(p_plan_id, p_report_for, p_report_for_id,'TRUCK')
  , mst_reports_pkg.get_trans_cost_per_mode(p_plan_id, p_report_for, p_report_for_id,'LTL')
  , mst_reports_pkg.get_trans_cost_per_mode(p_plan_id, p_report_for, p_report_for_id,'PARCEL')
  , mst_reports_pkg.get_trans_cost_per_mode(p_plan_id, p_report_for, p_report_for_id,'TRUCK')
   +mst_reports_pkg.get_trans_cost_per_mode(p_plan_id, p_report_for, p_report_for_id,'LTL')
   +mst_reports_pkg.get_trans_cost_per_mode(p_plan_id, p_report_for, p_report_for_id,'PARCEL')
  , mst_reports_pkg.get_handl_cost_per_mode(p_plan_id, p_report_for, p_report_for_id,'TRUCK')
   +mst_reports_pkg.get_handl_cost_per_mode(p_plan_id, p_report_for, p_report_for_id,'LTL')
   +mst_reports_pkg.get_handl_cost_per_mode(p_plan_id, p_report_for, p_report_for_id,'PARCEL')
  , mst_reports_pkg.get_total_cost_per_mode(p_plan_id, p_report_for, p_report_for_id,'TRUCK')
   +mst_reports_pkg.get_total_cost_per_mode(p_plan_id, p_report_for, p_report_for_id,'LTL')
   +mst_reports_pkg.get_total_cost_per_mode(p_plan_id, p_report_for, p_report_for_id,'PARCEL')
  , mst_reports_pkg.get_stops_per_load(p_plan_id, p_report_for, p_report_for_id)
  , mst_reports_pkg.get_TL_distance(p_plan_id, p_report_for, p_report_for_id)
  , 0
  , ( ( mst_reports_pkg.get_total_cost_per_mode(p_plan_id, p_report_for, p_report_for_id,'TRUCK')
       +mst_reports_pkg.get_total_cost_per_mode(p_plan_id, p_report_for, p_report_for_id,'LTL')
       +mst_reports_pkg.get_total_cost_per_mode(p_plan_id, p_report_for, p_report_for_id,'PARCEL')
       ) / decode( mst_reports_pkg.get_plan_value(p_plan_id, p_report_for, p_report_for_id)
                 , 0, 1
	         , mst_reports_pkg.get_plan_value(p_plan_id, p_report_for, p_report_for_id)) ) * 100
  from mst_plans mp
  where mp.plan_id = p_plan_id
 );

Line: 6065


  insert into mst_mast_sum_report_temp_gt
  ( view_id
  , plan_id
  , facility_name
  , orders
  , weight
  , volume
  , pieces
  , tl_cost
  , ltl_cost
  , parcel_cost
  , total_cost
  , cost_per_unit_weight
  , cost_per_unit_volume
  , percent_value
  , percent_alloc_cost
  )
  (SELECT 2
  , temp.plan_id
  , fte.facility_code
  , mst_reports_pkg.get_orders_myfac(p_plan_id,p_report_for,p_report_for_id,fte.facility_id)
  , mst_reports_pkg.get_weight_myfac(p_plan_id,p_report_for,p_report_for_id,fte.facility_id)
  , mst_reports_pkg.get_volume_myfac(p_plan_id,p_report_for,p_report_for_id,fte.facility_id)
  , mst_reports_pkg.get_pieces_myfac(p_plan_id,p_report_for,p_report_for_id,fte.facility_id)
  , mst_reports_pkg.get_cost_per_mode_myfac(p_plan_id,p_report_for,p_report_for_id,'TRUCK',fte.facility_id)
  , mst_reports_pkg.get_cost_per_mode_myfac(p_plan_id,p_report_for,p_report_for_id,'LTL',fte.facility_id)
  , mst_reports_pkg.get_cost_per_mode_myfac(p_plan_id,p_report_for,p_report_for_id,'PARCEL',fte.facility_id)
  , mst_reports_pkg.get_cost_per_mode_myfac(p_plan_id,p_report_for,p_report_for_id,'TRUCK',fte.facility_id)
    +mst_reports_pkg.get_cost_per_mode_myfac(p_plan_id,p_report_for,p_report_for_id,'LTL',fte.facility_id)
    +mst_reports_pkg.get_cost_per_mode_myfac(p_plan_id,p_report_for,p_report_for_id,'PARCEL',fte.facility_id)
  ,( mst_reports_pkg.get_cost_per_mode_myfac(p_plan_id,p_report_for,p_report_for_id,'TRUCK',fte.facility_id)
    +mst_reports_pkg.get_cost_per_mode_myfac(p_plan_id,p_report_for,p_report_for_id,'LTL',fte.facility_id)
    +mst_reports_pkg.get_cost_per_mode_myfac(p_plan_id,p_report_for,p_report_for_id,'PARCEL',fte.facility_id)
   ) / decode( mst_reports_pkg.get_weight_myfac(p_plan_id,p_report_for,p_report_for_id,fte.facility_id)
              ,0 ,1
              , mst_reports_pkg.get_weight_myfac(p_plan_id,p_report_for,p_report_for_id,fte.facility_id))
  ,( mst_reports_pkg.get_cost_per_mode_myfac(p_plan_id,p_report_for,p_report_for_id,'TRUCK',fte.facility_id)
    +mst_reports_pkg.get_cost_per_mode_myfac(p_plan_id,p_report_for,p_report_for_id,'LTL',fte.facility_id)
    +mst_reports_pkg.get_cost_per_mode_myfac(p_plan_id,p_report_for,p_report_for_id,'PARCEL',fte.facility_id)
   ) / decode( mst_reports_pkg.get_volume_myfac(p_plan_id,p_report_for,p_report_for_id,fte.facility_id)
              ,0 ,1
	      , mst_reports_pkg.get_volume_myfac(p_plan_id,p_report_for,p_report_for_id,fte.facility_id))
, ( ( mst_reports_pkg.get_cost_per_mode_myfac(p_plan_id,p_report_for,p_report_for_id,'TRUCK',fte.facility_id)
     +mst_reports_pkg.get_cost_per_mode_myfac(p_plan_id,p_report_for,p_report_for_id,'LTL',fte.facility_id)
     +mst_reports_pkg.get_cost_per_mode_myfac(p_plan_id,p_report_for,p_report_for_id,'PARCEL',fte.facility_id)
     ) / decode( nvl(mst_reports_pkg.get_plan_value(p_plan_id,p_report_for,p_report_for_id),0)
                 , 0, 1
		 , mst_reports_pkg.get_plan_value(p_plan_id,p_report_for,p_report_for_id)) ) * 100
  , 0
  from (select distinct mts.plan_id plan_id, mts.stop_location_id loc_id
        from mst_trip_stops mts
        , mst_trips mt
        where mts.plan_id = mt.plan_id
        and mts.trip_id = mt.trip_id) temp,
  mst_plans mp,
  wsh_location_owners wlo,
  wsh_locations wl,
  fte_location_parameters fte
  where mp.plan_id = temp.plan_id
  and temp.loc_id = fte.location_id
  and fte.location_id = wl.wsh_location_id
  and wl.wsh_location_id = wlo.wsh_location_id
  and wlo.owner_type = 1
  and mp.plan_id = p_plan_id
  and decode ( p_report_for, 1, fte.facility_id, p_report_for_id ) = p_report_for_id
);

Line: 6135


  insert into mst_mast_sum_report_temp_gt
  ( view_id
  , plan_id
  , carrier_name
  , carr_moves
  , total_cost
  , mode_of_transport
  )
  (SELECT 3
  , mt.plan_id
  , wc.freight_code
  , mst_reports_pkg.get_carr_movements(p_plan_id,p_report_for,p_report_for_id,mt.carrier_id)
  , mst_reports_pkg.get_carr_cost(p_plan_id,p_report_for,p_report_for_id,mt.carrier_id)
  , mt.mode_of_transport
  from mst_trips mt
  , wsh_carriers wc
  WHERE mt.carrier_id = wc.carrier_id
  and mt.plan_id = p_plan_id
  and decode ( p_report_for, 3, mt.carrier_id, p_report_for_id ) = p_report_for_id
  group by mt.plan_id
  , mt.carrier_id
  , wc.freight_code
  ,mt.mode_of_transport
 );

Line: 6164


  insert into mst_mast_sum_report_temp_gt
  ( view_id
  , plan_id
  , origin_name
  , orders
  , weight
  , volume
  , pieces
  , mtl_count
  , dtl_count
  , ltl_count
  , parcel_count
  , tl_cost
  , ltl_cost
  , parcel_cost
  , total_cost
  , cost_per_unit_weight
  , cost_per_unit_volume
  , percent_value
  , percent_alloc_cost
  )
  (SELECT distinct 4
  , mp.plan_id
  , wl.state
  , mst_reports_pkg.get_orders_orig(p_plan_id,p_report_for,p_report_for_id,wl.state)
  , mst_reports_pkg.get_weight_orig(p_plan_id,p_report_for,p_report_for_id,wl.state)
  , mst_reports_pkg.get_volume_orig(p_plan_id,p_report_for,p_report_for_id,wl.state)
  , mst_reports_pkg.get_pieces_orig(p_plan_id,p_report_for,p_report_for_id,wl.state)
  , mst_reports_pkg.get_MTL_orig(p_plan_id,p_report_for,p_report_for_id,wl.state)
  , mst_reports_pkg.get_DTL_orig(p_plan_id,p_report_for,p_report_for_id,wl.state)
  , mst_reports_pkg.get_LTL_orig(p_plan_id,p_report_for,p_report_for_id,wl.state)
  , mst_reports_pkg.get_PCL_orig(p_plan_id,p_report_for,p_report_for_id,wl.state)
  , mst_reports_pkg.get_total_cost_mode_orig(p_plan_id,p_report_for,p_report_for_id,'TRUCK',wl.state)
  , mst_reports_pkg.get_total_cost_mode_orig(p_plan_id,p_report_for,p_report_for_id,'LTL',wl.state)
  , mst_reports_pkg.get_total_cost_mode_orig(p_plan_id,p_report_for,p_report_for_id,'PARCEL',wl.state)
  , mst_reports_pkg.get_total_cost_mode_orig(p_plan_id,p_report_for,p_report_for_id,'TRUCK',wl.state)
       +mst_reports_pkg.get_total_cost_mode_orig(p_plan_id,p_report_for,p_report_for_id,'LTL',wl.state)
       +mst_reports_pkg.get_total_cost_mode_orig(p_plan_id,p_report_for,p_report_for_id,'PARCEL',wl.state)
  , ( mst_reports_pkg.get_total_cost_mode_orig(p_plan_id,p_report_for,p_report_for_id,'TRUCK',wl.state)
     +mst_reports_pkg.get_total_cost_mode_orig(p_plan_id,p_report_for,p_report_for_id,'LTL',wl.state)
     +mst_reports_pkg.get_total_cost_mode_orig(p_plan_id,p_report_for,p_report_for_id,'PARCEL',wl.state)
    ) / decode( mst_reports_pkg.get_weight_orig(p_plan_id,p_report_for,p_report_for_id,wl.state)
               ,0 ,1
	       ,mst_reports_pkg.get_weight_orig(p_plan_id,p_report_for,p_report_for_id,wl.state))
  , ( mst_reports_pkg.get_total_cost_mode_orig(p_plan_id,p_report_for,p_report_for_id,'TRUCK',wl.state)
     +mst_reports_pkg.get_total_cost_mode_orig(p_plan_id,p_report_for,p_report_for_id,'LTL',wl.state)
     +mst_reports_pkg.get_total_cost_mode_orig(p_plan_id,p_report_for,p_report_for_id,'PARCEL',wl.state)
    ) / decode( mst_reports_pkg.get_volume_orig(p_plan_id,p_report_for,p_report_for_id,wl.state)
               ,0 ,1
 	       ,mst_reports_pkg.get_volume_orig(p_plan_id,p_report_for,p_report_for_id,wl.state))
  ,( mst_reports_pkg.get_total_cost_mode_orig(p_plan_id,p_report_for,p_report_for_id,'TRUCK',wl.state)
    +mst_reports_pkg.get_total_cost_mode_orig(p_plan_id,p_report_for,p_report_for_id,'LTL',wl.state)
    +mst_reports_pkg.get_total_cost_mode_orig(p_plan_id,p_report_for,p_report_for_id,'PARCEL',wl.state)
   ) / decode( nvl(mst_reports_pkg.get_plan_value(p_plan_id,p_report_for,p_report_for_id),0)
             , 0, 1
 	     , mst_reports_pkg.get_plan_value(p_plan_id,p_report_for,p_report_for_id) ) * 100
  , 0
  from mst_plans mp
  ,wsh_locations wl
  WHERE mp.plan_id = p_plan_id
  and wl.wsh_location_id in (select distinct md.pickup_location_id
                             from mst_deliveries md
                             where md.plan_id = mp.plan_id)
  );

Line: 6232


  insert into mst_mast_sum_report_temp_gt
  ( view_id
  , plan_id
  , destination_name
  , orders
  , weight
  , volume
  , pieces
  , mtl_count
  , dtl_count
  , ltl_count
  , parcel_count
  , tl_cost
  , ltl_cost
  , parcel_cost
  , total_cost
  , cost_per_unit_weight
  , cost_per_unit_volume
  , percent_value
  , percent_alloc_cost
  )
  (SELECT distinct 5
  , mp.plan_id
  , wl.state
  , mst_reports_pkg.get_orders_dest(p_plan_id,p_report_for,p_report_for_id,wl.state)
  , mst_reports_pkg.get_weight_dest(p_plan_id,p_report_for,p_report_for_id,wl.state)
  , mst_reports_pkg.get_volume_dest(p_plan_id,p_report_for,p_report_for_id,wl.state)
  , mst_reports_pkg.get_pieces_dest(p_plan_id,p_report_for,p_report_for_id,wl.state)
  , mst_reports_pkg.get_MTL_dest(p_plan_id,p_report_for,p_report_for_id,wl.state)
  , mst_reports_pkg.get_DTL_dest(p_plan_id,p_report_for,p_report_for_id,wl.state)
  , mst_reports_pkg.get_LTL_dest(p_plan_id,p_report_for,p_report_for_id,wl.state)
  , mst_reports_pkg.get_PCL_dest(p_plan_id,p_report_for,p_report_for_id,wl.state)
  , mst_reports_pkg.get_total_cost_mode_dest(p_plan_id,p_report_for,p_report_for_id,'TRUCK',wl.state)
  , mst_reports_pkg.get_total_cost_mode_dest(p_plan_id,p_report_for,p_report_for_id,'LTL',wl.state)
  , mst_reports_pkg.get_total_cost_mode_dest(p_plan_id,p_report_for,p_report_for_id,'PARCEL',wl.state)
  , mst_reports_pkg.get_total_cost_mode_dest(p_plan_id,p_report_for,p_report_for_id,'TRUCK',wl.state)
    +mst_reports_pkg.get_total_cost_mode_dest(p_plan_id,p_report_for,p_report_for_id,'LTL',wl.state)
    +mst_reports_pkg.get_total_cost_mode_dest(p_plan_id,p_report_for,p_report_for_id,'PARCEL',wl.state)
  , ( mst_reports_pkg.get_total_cost_mode_dest(p_plan_id,p_report_for,p_report_for_id,'TRUCK',wl.state)
     +mst_reports_pkg.get_total_cost_mode_dest(p_plan_id,p_report_for,p_report_for_id,'LTL',wl.state)
     +mst_reports_pkg.get_total_cost_mode_dest(p_plan_id,p_report_for,p_report_for_id,'PARCEL',wl.state)
    ) / decode( mst_reports_pkg.get_weight_dest(p_plan_id,p_report_for,p_report_for_id,wl.state)
               ,0 ,1
               ,mst_reports_pkg.get_weight_dest(p_plan_id,p_report_for,p_report_for_id,wl.state))
  , ( mst_reports_pkg.get_total_cost_mode_dest(p_plan_id,p_report_for,p_report_for_id,'TRUCK',wl.state)
     +mst_reports_pkg.get_total_cost_mode_dest(p_plan_id,p_report_for,p_report_for_id,'LTL',wl.state)
     +mst_reports_pkg.get_total_cost_mode_dest(p_plan_id,p_report_for,p_report_for_id,'PARCEL',wl.state)
    ) / decode( mst_reports_pkg.get_volume_dest(p_plan_id,p_report_for,p_report_for_id,wl.state)
               ,0 ,1
               ,mst_reports_pkg.get_volume_dest(p_plan_id,p_report_for,p_report_for_id,wl.state))
  ,( mst_reports_pkg.get_total_cost_mode_dest(p_plan_id,p_report_for,p_report_for_id,'TRUCK',wl.state)
    +mst_reports_pkg.get_total_cost_mode_dest(p_plan_id,p_report_for,p_report_for_id,'LTL',wl.state)
    +mst_reports_pkg.get_total_cost_mode_dest(p_plan_id,p_report_for,p_report_for_id,'PARCEL',wl.state)
   ) / decode( nvl(mst_reports_pkg.get_plan_value(p_plan_id,p_report_for,p_report_for_id),0)
               , 0, 1
               , mst_reports_pkg.get_plan_value(p_plan_id,p_report_for,p_report_for_id) ) * 100
  , 0
  from mst_plans mp
  ,wsh_locations wl
  WHERE mp.plan_id = p_plan_id
  and wl.wsh_location_id in (select distinct md.dropoff_location_id
                             from mst_deliveries md
                             where md.plan_id = mp.plan_id)
  );

Line: 6304


  insert into mst_mast_sum_report_temp_gt
  ( view_id
  , plan_id
  , customer_name
  , orders
  , weight
  , volume
  , pieces
  , mtl_count
  , dtl_count
  , ltl_count
  , parcel_count
  , tl_cost
  , ltl_cost
  , parcel_cost
  , total_cost
  , cost_per_unit_weight
  , cost_per_unit_volume
  , percent_value
  , percent_alloc_cost
  )
  (SELECT 6
  , mp.plan_id
  , hzp.party_name
  , mst_reports_pkg.get_orders_c_s(p_plan_id,p_report_for,p_report_for_id,l_c_s_ident,hzc.cust_account_id)
  , mst_reports_pkg.get_weight_c_s(p_plan_id,p_report_for,p_report_for_id,l_c_s_ident,hzc.cust_account_id)
  , mst_reports_pkg.get_volume_c_s(p_plan_id,p_report_for,p_report_for_id,l_c_s_ident,hzc.cust_account_id)
  , mst_reports_pkg.get_pieces_c_s(p_plan_id,p_report_for,p_report_for_id,l_c_s_ident,hzc.cust_account_id)
  , mst_reports_pkg.get_MTL_c_s(p_plan_id,p_report_for,p_report_for_id,l_c_s_ident,hzc.cust_account_id)
  , mst_reports_pkg.get_DTL_c_s(p_plan_id,p_report_for,p_report_for_id,l_c_s_ident,hzc.cust_account_id)
  , mst_reports_pkg.get_trips_per_mode_c_s(p_plan_id,p_report_for,p_report_for_id,'LTL',l_c_s_ident,hzc.cust_account_id)
  , mst_reports_pkg.get_trips_per_mode_c_s(p_plan_id,p_report_for,p_report_for_id,'PARCEL',l_c_s_ident,hzc.cust_account_id)
  , mst_reports_pkg.get_cost_per_mode_c_s(p_plan_id,p_report_for,p_report_for_id,'TRUCK',l_c_s_ident,hzc.cust_account_id)
  , mst_reports_pkg.get_cost_per_mode_c_s(p_plan_id,p_report_for,p_report_for_id,'LTL',l_c_s_ident,hzc.cust_account_id)
  , mst_reports_pkg.get_cost_per_mode_c_s(p_plan_id,p_report_for,p_report_for_id,'PARCEL',l_c_s_ident,hzc.cust_account_id)
  , mst_reports_pkg.get_cost_per_mode_c_s(p_plan_id,p_report_for,p_report_for_id,'TRUCK',l_c_s_ident,hzc.cust_account_id)
   +mst_reports_pkg.get_cost_per_mode_c_s(p_plan_id,p_report_for,p_report_for_id,'LTL',l_c_s_ident,hzc.cust_account_id)
   +mst_reports_pkg.get_cost_per_mode_c_s(p_plan_id,p_report_for,p_report_for_id,'PARCEL',l_c_s_ident,hzc.cust_account_id)
  , ( mst_reports_pkg.get_cost_per_mode_c_s(p_plan_id,p_report_for,p_report_for_id,'TRUCK',l_c_s_ident,hzc.cust_account_id)
     +mst_reports_pkg.get_cost_per_mode_c_s(p_plan_id,p_report_for,p_report_for_id,'LTL',l_c_s_ident,hzc.cust_account_id)
     +mst_reports_pkg.get_cost_per_mode_c_s(p_plan_id,p_report_for,p_report_for_id,'PARCEL',l_c_s_ident,hzc.cust_account_id)
	) / decode( mst_reports_pkg.get_weight_c_s(p_plan_id,p_report_for,p_report_for_id,l_c_s_ident,hzc.cust_account_id)
	            ,0 ,1
	            ,mst_reports_pkg.get_weight_c_s(p_plan_id,p_report_for,p_report_for_id,l_c_s_ident,hzc.cust_account_id))
  , ( mst_reports_pkg.get_cost_per_mode_c_s(p_plan_id,p_report_for,p_report_for_id,'TRUCK',l_c_s_ident,hzc.cust_account_id)
     +mst_reports_pkg.get_cost_per_mode_c_s(p_plan_id,p_report_for,p_report_for_id,'LTL',l_c_s_ident,hzc.cust_account_id)
     +mst_reports_pkg.get_cost_per_mode_c_s(p_plan_id,p_report_for,p_report_for_id,'PARCEL',l_c_s_ident,hzc.cust_account_id)
	) / decode( mst_reports_pkg.get_volume_c_s(p_plan_id,p_report_for,p_report_for_id,l_c_s_ident,hzc.cust_account_id)
	            ,0 ,1
                    ,mst_reports_pkg.get_volume_c_s(p_plan_id,p_report_for,p_report_for_id,l_c_s_ident,hzc.cust_account_id))
  , (( mst_reports_pkg.get_cost_per_mode_c_s(p_plan_id,p_report_for,p_report_for_id,'TRUCK',l_c_s_ident,hzc.cust_account_id)
      +mst_reports_pkg.get_cost_per_mode_c_s(p_plan_id,p_report_for,p_report_for_id,'LTL',l_c_s_ident,hzc.cust_account_id)
      +mst_reports_pkg.get_cost_per_mode_c_s(p_plan_id,p_report_for,p_report_for_id,'PARCEL',l_c_s_ident,hzc.cust_account_id)
     ) / decode( nvl(mst_reports_pkg.get_plan_value(p_plan_id,p_report_for,p_report_for_id),0)
                 , 0, 1
		 , mst_reports_pkg.get_plan_value(p_plan_id,p_report_for,p_report_for_id)) ) * 100
  ,0
  from mst_plans mp
  , hz_parties hzp
  , hz_cust_accounts hzc
  WHERE mp.plan_id = p_plan_id
  and hzp.party_id = hzc.party_id
  and hzc.cust_account_id in (select distinct md.customer_id
                              from mst_deliveries md
                              where md.plan_id = mp.plan_id)
  and decode ( p_report_for, 2, hzc.cust_account_id, p_report_for_id ) = p_report_for_id
  );

Line: 6379


  insert into mst_mast_sum_report_temp_gt
  ( view_id
  , plan_id
  , supplier_name
  , orders
  , weight
  , volume
  , pieces
  , mtl_count
  , dtl_count
  , ltl_count
  , parcel_count
  , tl_cost
  , ltl_cost
  , parcel_cost
  , total_cost
  , cost_per_unit_weight
  , cost_per_unit_volume
  , percent_value
  , percent_alloc_cost
  )
  (SELECT 7
  , mp.plan_id
  , hz.party_name
  , mst_reports_pkg.get_orders_c_s(p_plan_id,p_report_for,p_report_for_id,l_c_s_ident,pov.vendor_id)
  , mst_reports_pkg.get_weight_c_s(p_plan_id,p_report_for,p_report_for_id,l_c_s_ident,pov.vendor_id)
  , mst_reports_pkg.get_volume_c_s(p_plan_id,p_report_for,p_report_for_id,l_c_s_ident,pov.vendor_id)
  , mst_reports_pkg.get_pieces_c_s(p_plan_id,p_report_for,p_report_for_id,l_c_s_ident,pov.vendor_id)
  , mst_reports_pkg.get_MTL_c_s(p_plan_id,p_report_for,p_report_for_id,l_c_s_ident,pov.vendor_id)
  , mst_reports_pkg.get_DTL_c_s(p_plan_id,p_report_for,p_report_for_id,l_c_s_ident,pov.vendor_id)
  , mst_reports_pkg.get_trips_per_mode_c_s(p_plan_id,p_report_for,p_report_for_id,'LTL',l_c_s_ident,pov.vendor_id)
  , mst_reports_pkg.get_trips_per_mode_c_s(p_plan_id,p_report_for,p_report_for_id,'PARCEL',l_c_s_ident,pov.vendor_id)
  , mst_reports_pkg.get_cost_per_mode_c_s(p_plan_id,p_report_for,p_report_for_id,'TRUCK',l_c_s_ident,pov.vendor_id)
  , mst_reports_pkg.get_cost_per_mode_c_s(p_plan_id,p_report_for,p_report_for_id,'LTL',l_c_s_ident,pov.vendor_id)
  , mst_reports_pkg.get_cost_per_mode_c_s(p_plan_id,p_report_for,p_report_for_id,'PARCEL',l_c_s_ident,pov.vendor_id)
  , mst_reports_pkg.get_cost_per_mode_c_s(p_plan_id,p_report_for,p_report_for_id,'TRUCK',l_c_s_ident,pov.vendor_id)
   +mst_reports_pkg.get_cost_per_mode_c_s(p_plan_id,p_report_for,p_report_for_id,'LTL',l_c_s_ident,pov.vendor_id)
   +mst_reports_pkg.get_cost_per_mode_c_s(p_plan_id,p_report_for,p_report_for_id,'PARCEL',l_c_s_ident,pov.vendor_id)
  , ( mst_reports_pkg.get_cost_per_mode_c_s(p_plan_id,p_report_for,p_report_for_id,'TRUCK',l_c_s_ident,pov.vendor_id)
     +mst_reports_pkg.get_cost_per_mode_c_s(p_plan_id,p_report_for,p_report_for_id,'LTL',l_c_s_ident,pov.vendor_id)
     +mst_reports_pkg.get_cost_per_mode_c_s(p_plan_id,p_report_for,p_report_for_id,'PARCEL',l_c_s_ident,pov.vendor_id)
	) / decode( mst_reports_pkg.get_weight_c_s(p_plan_id,p_report_for,p_report_for_id,l_c_s_ident,pov.vendor_id)
	            ,0 ,1
	            ,mst_reports_pkg.get_weight_c_s(p_plan_id,p_report_for,p_report_for_id,l_c_s_ident,pov.vendor_id))
  , ( mst_reports_pkg.get_cost_per_mode_c_s(p_plan_id,p_report_for,p_report_for_id,'TRUCK',l_c_s_ident,pov.vendor_id)
     +mst_reports_pkg.get_cost_per_mode_c_s(p_plan_id,p_report_for,p_report_for_id,'LTL',l_c_s_ident,pov.vendor_id)
     +mst_reports_pkg.get_cost_per_mode_c_s(p_plan_id,p_report_for,p_report_for_id,'PARCEL',l_c_s_ident,pov.vendor_id)
	) / decode( mst_reports_pkg.get_volume_c_s(p_plan_id,p_report_for,p_report_for_id,l_c_s_ident,pov.vendor_id)
	            ,0 ,1
                    ,mst_reports_pkg.get_volume_c_s(p_plan_id,p_report_for,p_report_for_id,l_c_s_ident,pov.vendor_id))
  , (( mst_reports_pkg.get_cost_per_mode_c_s(p_plan_id,p_report_for,p_report_for_id,'TRUCK',l_c_s_ident,pov.vendor_id)
      +mst_reports_pkg.get_cost_per_mode_c_s(p_plan_id,p_report_for,p_report_for_id,'LTL',l_c_s_ident,pov.vendor_id)
      +mst_reports_pkg.get_cost_per_mode_c_s(p_plan_id,p_report_for,p_report_for_id,'PARCEL',l_c_s_ident,pov.vendor_id)
     ) / decode( nvl(mst_reports_pkg.get_plan_value(p_plan_id,p_report_for,p_report_for_id),0)
                 , 0, 1
		 , mst_reports_pkg.get_plan_value(p_plan_id,p_report_for,p_report_for_id)) ) * 100
  ,0
  from mst_plans mp
     , po_vendors pov
     , hz_relationships hzr
     , hz_parties hz
  WHERE mp.plan_id = p_plan_id
  and pov.vendor_id = hzr.subject_id
  and hzr.object_id = hz.party_id
  and hzr.relationship_type = 'POS_VENDOR_PARTY'
  and pov.vendor_id in (select mdd.supplier_id
                        from  mst_delivery_details mdd
                        where mdd.plan_id = mp.plan_id)
  and decode ( p_report_for, 4, pov.vendor_id , p_report_for_id ) = p_report_for_id
  );

Line: 6466


  select distinct mc.segment2
  from mtl_category_sets mcs
  , mtl_categories mc
  , mst_delivery_details mdd
  where mcs.structure_id = mc.structure_id
  and UPPER(mcs.category_set_name) = 'WSH_COMMODITY_CODE'    --'FREIGHT CLASS'
  and mc.category_id = mdd.commodity_code_cat_id             --mdd.freight_class_cat_id
  and mdd.plan_id = l_plan_id
  and mdd.source_header_number = l_source_header_number;

Line: 6503


  SELECT mts2.planned_arrival_date   planned_arrival_date,
         mts1.planned_departure_date planned_departure_date,
         mts1.drv_time_to_next_stop  drv_time_to_next_stop,
         mts1.total_layover_duration total_layover_duration
  FROM mst_trip_stops mts1
       ,mst_trip_stops mts2
  WHERE mts1.plan_id = l_plan_id
  AND   mts1.trip_id = l_trip_id
  AND   mts1.stop_id = l_stop_id
  AND   mts2.plan_id = mts1.plan_id
  AND   mts2.trip_id = mts1.trip_id
  AND   mts2.stop_sequence_number = ( SELECT MIN( mts3.stop_sequence_number )
                                      FROM mst_trip_stops mts3
                                      WHERE mts3.plan_id = mts1.plan_id
                                      AND  mts3.trip_id = mts1.trip_id
                                      AND  mts3.stop_sequence_number > mts1.stop_sequence_number );