[Home] [Help]
546: from mst_release_temp_gt mrt
547: where mrt.release_id = l_release_id
548: and mrt.cm_id_of_trip is null
549: and mrt.trip_id not in (select mdl.trip_id
550: from mst_delivery_legs mdl
551: where mdl.plan_id = l_plan_id);
552:
553: l_trip_id_tab number_tab_type;
554: l_trip_number_tab number_tab_type;
837: , mt.mode_of_transport
838: , mt.load_tender_status
839: , mt.lane_id
840: , mt.service_level
841: from mst_delivery_legs mdl
842: , mst_delivery_legs mdl1
843: , mst_trips mt
844: , mst_plans mp
845: , fte_vehicle_types fvt
838: , mt.load_tender_status
839: , mt.lane_id
840: , mt.service_level
841: from mst_delivery_legs mdl
842: , mst_delivery_legs mdl1
843: , mst_trips mt
844: , mst_plans mp
845: , fte_vehicle_types fvt
846: where mdl.plan_id = p_plan_id
866: decode(mrt.out_of_scope,1,mrt.planned_flag
867: ,decode(p_planned_flag,1,decode(nvl(mrt.planned_flag,0),3,2,mrt.planned_flag),mrt.planned_flag)))
868: where mrt.release_id = p_release_id
869: and mrt.trip_id in (select mdl_rel.trip_id
870: from mst_delivery_legs mdl
871: , mst_delivery_legs mdl_rel
872: where mdl_rel.plan_id = mdl.plan_id
873: and mdl_rel.delivery_id = mdl.delivery_id
874: and mdl_rel.trip_id <> mdl.trip_id
867: ,decode(p_planned_flag,1,decode(nvl(mrt.planned_flag,0),3,2,mrt.planned_flag),mrt.planned_flag)))
868: where mrt.release_id = p_release_id
869: and mrt.trip_id in (select mdl_rel.trip_id
870: from mst_delivery_legs mdl
871: , mst_delivery_legs mdl_rel
872: where mdl_rel.plan_id = mdl.plan_id
873: and mdl_rel.delivery_id = mdl.delivery_id
874: and mdl_rel.trip_id <> mdl.trip_id
875: and mdl.plan_id = p_plan_id
1044: and mep.exception_type = med.exception_type
1045: and med.plan_id = l_plan_id
1046: and (med.trip_id1 = l_trip_id -- trip_id2 is not being checked since
1047: or med.delivery_id in (select mdl.delivery_id -- it can not exists without trip_id1.
1048: from mst_delivery_legs mdl -- continuous_move_id is not being checked
1049: where mdl.plan_id = l_plan_id -- since it is not being populated without trip_id1
1050: and mdl.trip_id = l_trip_id)
1051: or med.location_id in (select mts.stop_location_id
1052: from mst_trip_stops mts
1302: , md.supplier_id
1303: , hzr.object_id
1304: from mst_release_temp_gt mrt
1305: , mst_deliveries md
1306: , mst_delivery_legs mdl
1307: , mst_plans mp
1308: , hz_relationships hzr
1309: where mrt.release_id = p_release_id
1310: and mrt.plan_id = mdl.plan_id
1331: UPDATE mst_release_temp_gt mrt
1332: SET mrt.planned_flag = 1
1333: WHERE mrt.release_id = p_release_id
1334: AND mrt.delivery_id IN (SELECT mdl.delivery_id
1335: FROM mst_delivery_legs mdl
1336: , mst_release_temp_gt mrt1
1337: WHERE mdl.plan_id = mrt1.plan_id
1338: AND mdl.trip_id = mrt1.trip_id
1339: AND mrt1.release_id = p_release_id
1794: , l_user
1795: from mst_release_temp_gt mrt1
1796: , mst_release_temp_gt mrt2
1797: , mst_release_temp_gt mrt3
1798: , mst_delivery_legs mdl
1799: where mrt1.plan_id= mdl.plan_id
1800: and mrt1.delivery_id = mdl.delivery_id
1801: and mrt1.release_id = p_release_id
1802: and mrt2.stop_id = mdl.pick_up_stop_id
2241: and mrt_gt.plan_id = l_plan_id
2242: and mrt_gt.trip_id is not null
2243: and mrt_gt.cm_id_of_trip is not null
2244: and mrt_gt.trip_id not in (select mdl.trip_id
2245: from mst_delivery_legs mdl
2246: where mdl.plan_id = l_plan_id);
2247:
2248: l_count pls_integer := 0;
2249:
2880: and mrt_gt.trip_id is not null
2881: and mrt_gt.planned_flag in (-1111,-2222,-3333,-4444,-5555)
2882: and mrt_gt.cm_id_of_trip is not null
2883: and mrt_gt.trip_id not in (select mdl.trip_id
2884: from mst_delivery_legs mdl
2885: where plan_id = l_plan_id);
2886:
2887: -- cursor to compute the count of unattempted empty trips which are not part of any continuous move
2888: cursor cur_non_cm_empty_trip_counts (l_plan_id in number, l_release_id in number)
2893: and mrt_gt.trip_id is not null
2894: and mrt_gt.planned_flag in (-1111,-2222,-3333,-4444,-5555)
2895: and mrt_gt.cm_id_of_trip is null
2896: and mrt_gt.trip_id not in (select mdl.trip_id
2897: from mst_delivery_legs mdl
2898: where plan_id = l_plan_id);
2899:
2900: -- cursor to compute the statistics of unattempted empty trips
2901: cursor cur_statistics_of_unattempted (l_plan_id in number, l_release_id in number)
3398: -- CARRIER (=, !=)
3399: l_where_clause := l_where_clause || ' and ' || ' mt.carrier_id ' || get_cond(l_condition) || ' ' || nvl(l_from_number_value,0);
3400: elsif (l_attribute_code = AREL_SUPPLIER_CODE) then
3401: -- SUPPLIER (=, !=)
3402: l_where_clause := l_where_clause || ' and ' || ' exists (select 1 from mst_deliveries md, mst_delivery_legs mdl';
3403: l_where_clause := l_where_clause || ' where mt.plan_id = mdl.plan_id and mt.trip_id = mdl.trip_id and mdl.plan_id = md.plan_id';
3404: l_where_clause := l_where_clause || ' and mdl.delivery_id = md.delivery_id and md.supplier_id ' || get_cond(l_condition);
3405: l_where_clause := l_where_clause || ' ' || nvl(l_from_number_value,0) || ')';
3406: elsif (l_attribute_code = AREL_CUSTOMER_CODE) then
3404: l_where_clause := l_where_clause || ' and mdl.delivery_id = md.delivery_id and md.supplier_id ' || get_cond(l_condition);
3405: l_where_clause := l_where_clause || ' ' || nvl(l_from_number_value,0) || ')';
3406: elsif (l_attribute_code = AREL_CUSTOMER_CODE) then
3407: -- CUSTOMER (=, !=)
3408: l_where_clause := l_where_clause || ' and ' || ' exists (select 1 from mst_deliveries md, mst_delivery_legs mdl';
3409: l_where_clause := l_where_clause || ' where mt.plan_id = mdl.plan_id and mt.trip_id = mdl.trip_id and mdl.plan_id = md.plan_id';
3410: l_where_clause := l_where_clause || ' and mdl.delivery_id = md.delivery_id and md.customer_id ' || get_cond(l_condition);
3411: l_where_clause := l_where_clause || ' ' || nvl(l_from_number_value,0) || ')';
3412: elsif (l_attribute_code = AREL_MODE_CODE) then