[Home] [Help]
303: -- Added new parameter plan_id
304: -- Added join for plan id
305: select sum(mdl.allocated_fac_loading_cost)
306: into l_loading_charge
307: from mst_trip_stops mts,
308: mst_delivery_legs mdl
309: where mts.stop_id = p_stop_id
310: AND mts.plan_id = P_PLAN_ID
311: AND mts.plan_id = mdl.plan_Id
321: l_unloading_charge number;
322: begin
323: select sum(mdl.allocated_fac_unloading_cost)
324: into l_unloading_charge
325: from mst_trip_stops mts,
326: mst_delivery_legs mdl
327: where mts.stop_id = p_stop_id
328: and mts.stop_id = mdl.drop_off_stop_id;
329: return l_unloading_charge;
337: l_loading_weight number;
338: begin
339: select sum(md.gross_weight)
340: into l_loading_weight
341: from mst_trip_stops mts,
342: mst_delivery_legs mdl,
343: mst_deliveries md
344: where mts.stop_id = p_stop_id
345: and mts.stop_id = mdl.pick_up_stop_id
355: l_loading_volume number;
356: begin
357: select sum(md.volume)
358: into l_loading_volume
359: from mst_trip_stops mts,
360: mst_delivery_legs mdl,
361: mst_deliveries md
362: where mts.stop_id = p_stop_id
363: and mts.stop_id = mdl.pick_up_stop_id
373: l_loading_pallets number;
374: begin
375: select sum(md.volume)
376: into l_loading_pallets
377: from mst_trip_stops mts,
378: mst_delivery_legs mdl,
379: mst_deliveries md
380: where mts.stop_id = p_stop_id
381: and mts.stop_id = mdl.pick_up_stop_id
391: l_loading_pieces number;
392: begin
393: select sum(md.number_of_pieces)
394: into l_loading_pieces
395: from mst_trip_stops mts,
396: mst_delivery_legs mdl,
397: mst_deliveries md
398: where mts.stop_id = p_stop_id
399: and mts.stop_id = mdl.pick_up_stop_id
410: l_unloading_weight number;
411: begin
412: select sum(md.gross_weight)
413: into l_unloading_weight
414: from mst_trip_stops mts,
415: mst_delivery_legs mdl,
416: mst_deliveries md
417: where mts.stop_id = p_stop_id
418: and mts.stop_id = mdl.drop_off_stop_id
428: l_unloading_volume number;
429: begin
430: select sum(md.volume)
431: into l_unloading_volume
432: from mst_trip_stops mts,
433: mst_delivery_legs mdl,
434: mst_deliveries md
435: where mts.stop_id = p_stop_id
436: and mts.stop_id = mdl.drop_off_stop_id
446: l_unloading_pallets number;
447: begin
448: select sum(md.number_of_pallets)
449: into l_unloading_pallets
450: from mst_trip_stops mts,
451: mst_delivery_legs mdl,
452: mst_deliveries md
453: where mts.stop_id = p_stop_id
454: and mts.stop_id = mdl.drop_off_stop_id
464: l_unloading_pieces number;
465: begin
466: select sum(md.number_of_pieces)
467: into l_unloading_pieces
468: from mst_trip_stops mts,
469: mst_delivery_legs mdl,
470: mst_deliveries md
471: where mts.stop_id = p_stop_id
472: and mts.stop_id = mdl.drop_off_stop_id
479: end;
480:
481: /* Vehicle Trip Leg Details */
482: -- None aggregation fields:
483: -- On board weight/cube/pallets/pieces can be fetched directly from MST_TRIP_STOPS
484: -- the columns are departure weight/cube/pieces/pallets of the first stop
485:
486: -- On board orders:
487: ---- ?????
719: AND md.delivery_id IN
720: ( SELECT mdl.delivery_id
721: FROM MST_DELIVERY_LEGS mdl,
722: MST_TRIPS mt,
723: MST_TRIP_STOPS mts
724: WHERE mt.plan_id = md.plan_id
725: AND mt.carrier_id = p_carrier_id
726: AND mt.plan_id = mdl.plan_id
727: AND mt.trip_id = mdl.trip_id
744: WHERE md.plan_id = p_plan_id
745: AND md.delivery_id IN
746: ( SELECT mdl.delivery_id
747: FROM mst_delivery_legs mdl,
748: mst_trip_stops mts
749: WHERE mdl.plan_id = md.plan_id
750: AND mdl.plan_id = mts.plan_id
751: AND mdl.pick_up_stop_id = mts.stop_id
752: AND EXISTS
765: WHERE md.plan_id = p_plan_id
766: AND md.delivery_id IN
767: ( SELECT mdl.delivery_id
768: FROM mst_delivery_legs mdl,
769: mst_trip_stops mts
770: WHERE mdl.plan_id = md.plan_id
771: AND mdl.plan_id = mts.plan_id
772: AND mdl.drop_off_stop_id = mts.stop_id
773: AND EXISTS
800: AND md.delivery_id IN
801: ( SELECT mdl.delivery_id
802: FROM MST_DELIVERY_LEGS mdl,
803: MST_TRIPS mt,
804: MST_TRIP_STOPS mts
805: WHERE mt.plan_id = md.plan_id
806: AND mt.carrier_id = p_carrier_id
807: AND mt.plan_id = mdl.plan_id
808: AND mt.trip_id = mdl.trip_id
825: WHERE md.plan_id = p_plan_id
826: AND md.delivery_id IN
827: ( SELECT mdl.delivery_id
828: FROM mst_delivery_legs mdl,
829: mst_trip_stops mts
830: WHERE mdl.plan_id = md.plan_id
831: AND mdl.plan_id = mts.plan_id
832: AND mdl.pick_up_stop_id = mts.stop_id
833: AND EXISTS
846: WHERE md.plan_id = p_plan_id
847: AND md.delivery_id IN
848: ( SELECT mdl.delivery_id
849: FROM mst_delivery_legs mdl,
850: mst_trip_stops mts
851: WHERE mdl.plan_id = md.plan_id
852: AND mdl.plan_id = mts.plan_id
853: AND mdl.drop_off_stop_id = mts.stop_id
854: AND EXISTS
882: AND md.delivery_id IN
883: ( SELECT mdl.delivery_id
884: FROM MST_DELIVERY_LEGS mdl,
885: MST_TRIPS mt,
886: MST_TRIP_STOPS mts
887: WHERE mt.plan_id = md.plan_id
888: AND mt.carrier_id = p_carrier_id
889: AND mt.plan_id = mdl.plan_id
890: AND mt.trip_id = mdl.trip_id
907: WHERE md.plan_id = p_plan_id
908: AND md.delivery_id IN
909: ( SELECT mdl.delivery_id
910: FROM mst_delivery_legs mdl,
911: mst_trip_stops mts
912: WHERE mdl.plan_id = md.plan_id
913: AND mdl.plan_id = mts.plan_id
914: AND mdl.pick_up_stop_id = mts.stop_id
915: AND EXISTS
928: WHERE md.plan_id = p_plan_id
929: AND md.delivery_id IN
930: ( SELECT mdl.delivery_id
931: FROM mst_delivery_legs mdl,
932: mst_trip_stops mts
933: WHERE mdl.plan_id = md.plan_id
934: AND mdl.plan_id = mts.plan_id
935: AND mdl.drop_off_stop_id = mts.stop_id
936: AND EXISTS
963: AND md.delivery_id IN
964: ( SELECT mdl.delivery_id
965: FROM MST_DELIVERY_LEGS mdl,
966: MST_TRIPS mt,
967: MST_TRIP_STOPS mts
968: WHERE mt.plan_id = md.plan_id
969: AND mt.carrier_id = p_carrier_id
970: AND mt.plan_id = mdl.plan_id
971: AND mt.trip_id = mdl.trip_id
988: WHERE md.plan_id = p_plan_id
989: AND md.delivery_id IN
990: ( SELECT mdl.delivery_id
991: FROM mst_delivery_legs mdl,
992: mst_trip_stops mts
993: WHERE mdl.plan_id = md.plan_id
994: AND mdl.plan_id = mts.plan_id
995: AND mdl.pick_up_stop_id = mts.stop_id
996: AND EXISTS
1009: WHERE md.plan_id = p_plan_id
1010: AND md.delivery_id IN
1011: ( SELECT mdl.delivery_id
1012: FROM mst_delivery_legs mdl,
1013: mst_trip_stops mts
1014: WHERE mdl.plan_id = md.plan_id
1015: AND mdl.plan_id = mts.plan_id
1016: AND mdl.drop_off_stop_id = mts.stop_id
1017: AND EXISTS
1052: AND MD.DELIVERY_ID = MDA.DELIVERY_ID
1053: AND MD.DELIVERY_ID IN
1054: (SELECT MDL.DELIVERY_ID
1055: FROM MST_TRIPS T,
1056: MST_TRIP_STOPS TS,
1057: MST_DELIVERY_LEGS MDL
1058: WHERE MDL.PLAN_ID = MD.PLAN_ID
1059: AND TS.PLAN_ID = MDL.PLAN_ID
1060: AND ( TS.STOP_ID = MDL.PICK_UP_STOP_ID
1088: and mda.delivery_detail_id = mdd.delivery_detail_id
1089: AND md.delivery_id IN
1090: ( SELECT mdl.delivery_id
1091: FROM mst_delivery_legs mdl,
1092: mst_trip_stops mts
1093: WHERE mdl.plan_id = md.plan_id
1094: AND mdl.plan_id = mts.plan_id
1095: AND mdl.pick_up_stop_id = mts.stop_id
1096: AND EXISTS
1115: and mda.delivery_detail_id = mdd.delivery_detail_id
1116: AND md.delivery_id IN
1117: ( SELECT mdl.delivery_id
1118: FROM mst_delivery_legs mdl,
1119: mst_trip_stops mts
1120: WHERE mdl.plan_id = md.plan_id
1121: AND mdl.plan_id = mts.plan_id
1122: AND mdl.drop_off_stop_id = mts.stop_id
1123: AND EXISTS
1190: select distinct md.delivery_id, md.gross_weight gross_weight
1191: from mst_delivery_legs mdl,
1192: mst_deliveries md,
1193: mst_trips mt,
1194: mst_trip_stops mts
1195: where mt.plan_id = p_plan_id
1196: and mt.mode_of_transport = p_mode
1197: and mt.service_level = p_service
1198: and mt.carrier_id = p_carrier_id
1217: select distinct md.delivery_id, md.volume volume
1218: from mst_delivery_legs mdl,
1219: mst_deliveries md,
1220: mst_trips mt,
1221: mst_trip_stops mts
1222: where mt.plan_id = p_plan_id
1223: and mt.mode_of_transport = p_mode
1224: and mt.service_level = p_service
1225: and mt.carrier_id = p_carrier_id
1243: select distinct md.delivery_id, md.number_of_pallets number_of_pallets
1244: from mst_delivery_legs mdl,
1245: mst_deliveries md,
1246: mst_trips mt,
1247: mst_trip_stops mts
1248: where mt.plan_id = p_plan_id
1249: and mt.mode_of_transport = p_mode
1250: and mt.service_level = p_service
1251: and mt.carrier_id = p_carrier_id
1269: select distinct md.delivery_id, md.number_of_pieces number_of_pieces
1270: from mst_delivery_legs mdl,
1271: mst_deliveries md,
1272: mst_trips mt,
1273: mst_trip_stops mts
1274: where mt.plan_id = p_plan_id
1275: and mt.mode_of_transport = p_mode
1276: and mt.service_level = p_service
1277: and mt.carrier_id = p_carrier_id
1615: and mdl.plan_id = p_plan_id;
1616:
1617: cursor mst_mode_of_transport (p_delivery_leg_id NUMBER) is
1618: select mt.mode_of_transport mode_of_transport, mt.trip_id trip_id
1619: from mst_delivery_legs mdl, mst_trip_stops mts, mst_trips mt
1620: where mdl.delivery_leg_id = p_delivery_leg_id
1621: and mdl.plan_id = mts.plan_id
1622: and mdl.pick_up_stop_id = mts.stop_id
1623: and mdl.plan_id = mt.plan_id
1624: and mts.trip_id = mt.trip_id;
1625:
1626: cursor mst_pickup_stop_location (p_delivery_leg_id NUMBER) is
1627: select mts.stop_location_id location_id
1628: from mst_delivery_legs mdl, mst_trip_stops mts
1629: where delivery_leg_id = p_delivery_leg_id
1630: and mdl.plan_id = mts.plan_id
1631: and mdl.pick_up_stop_id = mts.stop_id ;
1632:
1631: and mdl.pick_up_stop_id = mts.stop_id ;
1632:
1633: cursor mst_dropoff_stop_location (p_delivery_leg_id NUMBER) is
1634: select mts.stop_location_id location_id
1635: from mst_delivery_legs mdl, mst_trip_stops mts
1636: where delivery_leg_id = p_delivery_leg_id
1637: and mdl.plan_id = mts.plan_id
1638: and mdl.drop_off_stop_id = mts.stop_id ;
1639:
1766: and mdl.plan_id = p_plan_id;
1767:
1768: cursor mst_mode_of_transport (p_delivery_leg_id NUMBER) is
1769: select mt.mode_of_transport mode_of_transport, mt.trip_id trip_id
1770: from mst_delivery_legs mdl, mst_trip_stops mts, mst_trips mt
1771: where mdl.delivery_leg_id = p_delivery_leg_id
1772: and mdl.plan_id = mts.plan_id
1773: and mdl.pick_up_stop_id = mts.stop_id
1774: and mdl.plan_id = mt.plan_id
1775: and mts.trip_id = mt.trip_id;
1776:
1777: cursor mst_pickup_stop_location (p_delivery_leg_id NUMBER) is
1778: select mts.stop_location_id location_id
1779: from mst_delivery_legs mdl, mst_trip_stops mts
1780: where delivery_leg_id = p_delivery_leg_id
1781: and mdl.plan_id = mts.plan_id
1782: and mdl.pick_up_stop_id = mts.stop_id ;
1783:
1782: and mdl.pick_up_stop_id = mts.stop_id ;
1783:
1784: cursor mst_dropoff_stop_location (p_delivery_leg_id NUMBER) is
1785: select mts.stop_location_id location_id
1786: from mst_delivery_legs mdl, mst_trip_stops mts
1787: where delivery_leg_id = p_delivery_leg_id
1788: and mdl.plan_id = mts.plan_id
1789: and mdl.drop_off_stop_id = mts.stop_id ;
1790:
1923: from mst_trips mt
1924: where mt.plan_id = p_plan_id
1925: and mt.trip_id in
1926: (select distinct mts.trip_id
1927: from mst_trip_stops mts
1928: , mst_delivery_legs mdl
1929: , mst_deliveries md
1930: where md.plan_id = mt.plan_id
1931: and md.customer_id = p_partner_id
1937: and mdl.drop_off_stop_id = mts.stop_id)
1938: and mt.mode_of_transport = 'TRUCK'
1939: and EXISTS
1940: (select ts.trip_id
1941: from mst_trip_stops ts
1942: where ts.plan_id = mt.plan_id
1943: and ts.trip_id = mt.trip_id
1944: having count(ts.stop_id) =2
1945: group by ts.trip_id);
1950: from mst_trips mt
1951: where mt.plan_id = p_plan_id
1952: and mt.trip_id in
1953: (select distinct mts.trip_id
1954: from mst_trip_stops mts
1955: , mst_delivery_legs mdl
1956: , mst_deliveries md
1957: where md.plan_id = mt.plan_id
1958: and md.customer_id = p_partner_id
1964: and mdl.drop_off_stop_id = mts.stop_id)
1965: and mt.mode_of_transport = 'TRUCK'
1966: and EXISTS
1967: (select ts.trip_id
1968: from mst_trip_stops ts
1969: where ts.plan_id = mt.plan_id
1970: and ts.trip_id = mt.trip_id
1971: having count(ts.stop_id) >2
1972: group by ts.trip_id);
1977: from mst_trips mt
1978: where mt.plan_id = p_plan_id
1979: and mt.trip_id in
1980: (select distinct mts.trip_id
1981: from mst_trip_stops mts
1982: , mst_delivery_legs mdl
1983: , mst_deliveries md
1984: where md.plan_id = mt.plan_id
1985: and md.customer_id = p_partner_id
1997: from mst_trips mt
1998: where mt.plan_id = p_plan_id
1999: and mt.trip_id in
2000: (select distinct mts.trip_id
2001: from mst_trip_stops mts
2002: , mst_delivery_legs mdl
2003: , mst_deliveries md
2004: where md.plan_id = mt.plan_id
2005: and md.customer_id = p_partner_id
2018: from mst_trips mt
2019: where mt.plan_id = p_plan_id
2020: and mt.trip_id in
2021: (select distinct mts.trip_id
2022: from mst_trip_stops mts
2023: , mst_delivery_legs mdl
2024: , mst_deliveries md
2025: where md.plan_id = mt.plan_id
2026: and md.supplier_id = p_partner_id
2032: and mdl.pick_up_stop_id = mts.stop_id)
2033: and mt.mode_of_transport = 'TRUCK'
2034: and EXISTS
2035: (select ts.trip_id
2036: from mst_trip_stops ts
2037: where ts.plan_id = mt.plan_id
2038: and ts.trip_id = mt.trip_id
2039: having count(ts.stop_id) =2
2040: group by ts.trip_id);
2045: from mst_trips mt
2046: where mt.plan_id = p_plan_id
2047: and mt.trip_id in
2048: (select distinct mts.trip_id
2049: from mst_trip_stops mts
2050: , mst_delivery_legs mdl
2051: , mst_deliveries md
2052: where md.plan_id = mt.plan_id
2053: and md.supplier_id = p_partner_id
2059: and mdl.pick_up_stop_id = mts.stop_id)
2060: and mt.mode_of_transport = 'TRUCK'
2061: and EXISTS
2062: (select ts.trip_id
2063: from mst_trip_stops ts
2064: where ts.plan_id = mt.plan_id
2065: and ts.trip_id = mt.trip_id
2066: having count(ts.stop_id) >2
2067: group by ts.trip_id);
2072: from mst_trips mt
2073: where mt.plan_id = p_plan_id
2074: and mt.trip_id in
2075: (select distinct mts.trip_id
2076: from mst_trip_stops mts
2077: , mst_delivery_legs mdl
2078: , mst_deliveries md
2079: where md.plan_id = mt.plan_id
2080: and md.supplier_id = p_partner_id
2092: from mst_trips mt
2093: where mt.plan_id = p_plan_id
2094: and mt.trip_id in
2095: (select distinct mts.trip_id
2096: from mst_trip_stops mts
2097: , mst_delivery_legs mdl
2098: , mst_deliveries md
2099: where md.plan_id = mt.plan_id
2100: and md.supplier_id = p_partner_id
2207: FUNCTION get_num_of_stops_for_tl(p_plan_id IN NUMBER, p_trip_id IN NUMBER)
2208: RETURN NUMBER IS
2209: CURSOR tl_stops is
2210: SELECT count(stop_id)
2211: FROM mst_trip_stops
2212: WHERE plan_id = p_plan_id
2213: AND trip_id = p_trip_id;
2214:
2215: l_num_of_stops number;
2856: AND MD.CUSTOMER_ID = P_CUST_OR_SUPP_ID
2857: AND MD.DELIVERY_ID IN
2858: (SELECT MDL.DELIVERY_ID
2859: FROM MST_DELIVERY_LEGS MDL,
2860: MST_TRIP_STOPS MTS
2861: WHERE MDL.PLAN_ID = MD.PLAN_ID
2862: AND MDL.PLAN_ID = MTS.PLAN_ID
2863: AND ( MDL.PICK_UP_STOP_ID = MTS.STOP_ID
2864: OR MDL.DROP_OFF_STOP_ID = MTS.STOP_ID)
2894: AND MD.SUPPLIER_ID = P_CUST_OR_SUPP_ID
2895: AND MD.DELIVERY_ID IN
2896: (SELECT MDL.DELIVERY_ID
2897: FROM MST_DELIVERY_LEGS MDL,
2898: MST_TRIP_STOPS MTS
2899: WHERE MDL.PLAN_ID = MD.PLAN_ID
2900: AND MDL.PLAN_ID = MTS.PLAN_ID
2901: AND ( MDL.PICK_UP_STOP_ID = MTS.STOP_ID
2902: OR MDL.DROP_OFF_STOP_ID = MTS.STOP_ID)
2954: FROM mst_trips mt
2955: WHERE mt.plan_id = p_plan_id
2956: AND mt.trip_id IN
2957: (select distinct mts.trip_id
2958: from mst_trip_stops mts
2959: , mst_delivery_legs mdl
2960: , mst_deliveries md
2961: where md.plan_id = mt.plan_id
2962: and md.customer_id = p_partner_id
2969: and mdl.drop_off_stop_id = mts.stop_id)
2970: AND mt.mode_of_transport = TRUCK -- p_mode_of_transport
2971: AND EXISTS
2972: (SELECT ts.trip_id
2973: FROM mst_trip_stops ts
2974: WHERE ts.plan_id = mt.plan_id
2975: AND ts.trip_id = mt.trip_ID
2976: HAVING COUNT(ts.stop_id) =2
2977: GROUP BY ts.trip_id) ;
2991: FROM mst_trips mt
2992: WHERE mt.plan_id = p_plan_id
2993: AND mt.trip_id IN
2994: (select distinct mts.trip_id
2995: from mst_trip_stops mts
2996: , mst_delivery_legs mdl
2997: , mst_deliveries md
2998: where md.plan_id = mt.plan_id
2999: and md.supplier_id = p_partner_id
3006: and mdl.pick_up_stop_id = mts.stop_id)
3007: AND mt.mode_of_transport = TRUCK -- p_mode_of_transport
3008: AND EXISTS
3009: (SELECT ts.trip_id
3010: FROM mst_trip_stops ts
3011: WHERE ts.plan_id = mt.plan_id
3012: AND ts.trip_id = mt.trip_ID
3013: HAVING COUNT(ts.stop_id) =2
3014: GROUP BY ts.trip_id) ;
3030: FROM mst_trips mt
3031: WHERE mt.plan_id = p_plan_id
3032: AND mt.trip_id IN
3033: (select distinct mts.trip_id
3034: from mst_trip_stops mts
3035: , mst_delivery_legs mdl
3036: , mst_deliveries md
3037: where md.plan_id = mt.plan_id
3038: and md.customer_id = p_partner_id
3045: and mdl.drop_off_stop_id = mts.stop_id)
3046: AND mt.mode_of_transport = TRUCK -- p_mode_of_transport
3047: AND EXISTS
3048: (SELECT ts.trip_id
3049: FROM mst_trip_stops ts
3050: WHERE ts.plan_id = mt.plan_id
3051: AND ts.trip_id = mt.trip_ID
3052: HAVING COUNT(ts.stop_id) > 2
3053: GROUP BY ts.trip_id) ;
3067: FROM mst_trips mt
3068: WHERE mt.plan_id = p_plan_id
3069: AND mt.trip_id IN
3070: (select distinct mts.trip_id
3071: from mst_trip_stops mts
3072: , mst_delivery_legs mdl
3073: , mst_deliveries md
3074: where md.plan_id = mt.plan_id
3075: and md.supplier_id = p_partner_id
3082: and mdl.pick_up_stop_id = mts.stop_id)
3083: AND mt.mode_of_transport = TRUCK -- p_mode_of_transport
3084: AND EXISTS
3085: (SELECT ts.trip_id
3086: FROM mst_trip_stops ts
3087: WHERE ts.plan_id = mt.plan_id
3088: AND ts.trip_id = mt.trip_ID
3089: HAVING COUNT(ts.stop_id) >2
3090: GROUP BY ts.trip_id) ;
3142: AND mt.trip_id IN
3143: (SELECT mdl.trip_id
3144: FROM mst_delivery_legs mdl,
3145: mst_deliveries md,
3146: mst_trip_stops mts
3147: WHERE mdl.plan_id = mt.plan_id
3148: AND md.plan_id = mdl.plan_id
3149: AND md.delivery_id = mdl.delivery_id
3150: AND md.customer_id = p_partner_id
3186: AND mt.trip_id IN
3187: (SELECT mdl.trip_id
3188: FROM mst_delivery_legs mdl,
3189: mst_deliveries md,
3190: mst_trip_stops mts
3191: WHERE mdl.plan_id = mt.plan_id
3192: AND md.plan_id = mdl.plan_id
3193: AND md.delivery_id = mdl.delivery_id
3194: AND md.supplier_id = p_partner_id
3222: WHERE mt.plan_id = p_plan_id
3223: AND mt.mode_of_transport = p_mode_of_transport
3224: AND mt.trip_id IN (
3225: SELECT ts.trip_id
3226: FROM mst_trip_stops ts,
3227: mst_delivery_legs mdl
3228: WHERE ts.plan_id = p_plan_id
3229: AND ts.stop_location_id = p_fac_loc_id
3230: AND ts.plan_Id = mdl.plan_Id
3237: WHERE mt.plan_id = p_plan_id
3238: AND mt.mode_of_transport = p_mode_of_transport
3239: AND mt.continuous_move_id IS NULL
3240: AND EXISTS (SELECT 1
3241: FROM mst_trip_stops mts
3242: WHERE mts.plan_id = mt.plan_id
3243: AND mts.trip_id = mt.trip_id
3244: AND mts.stop_location_id = p_fac_loc_id)
3245: AND NOT EXISTS (SELECT 1
3253: WHERE mt.plan_id = p_plan_id
3254: AND mt.mode_of_transport = p_mode_of_transport
3255: AND mt.trip_id IN (
3256: SELECT ts.trip_id
3257: FROM mst_trip_stops ts,
3258: mst_delivery_legs mdl
3259: WHERE ts.plan_id = p_plan_id
3260: AND ts.stop_location_id = p_fac_loc_id
3261: AND ts.stop_id = mdl.pick_up_stop_id
3267: WHERE mt.plan_id = p_plan_id
3268: AND mt.mode_of_transport = p_mode_of_transport
3269: AND mt.trip_id IN (
3270: SELECT ts.trip_id
3271: FROM mst_trip_stops ts,
3272: mst_delivery_legs mdl
3273: WHERE ts.plan_id = p_plan_id
3274: AND ts.stop_location_id = p_fac_loc_id
3275: AND ts.stop_id = mdl.drop_off_stop_id
3335: AND mdd.delivery_detail_id IN
3336: (SELECT mda.delivery_detail_id
3337: FROM mst_delivery_assignments mda,
3338: mst_trips mt,
3339: mst_trip_stops mts,
3340: mst_delivery_legs mdl,
3341: mst_deliveries md
3342: WHERE mda.plan_id = mdd.plan_id
3343: AND mda.plan_id = md.plan_id
3452: and md.delivery_id = mda.delivery_id
3453: and md.delivery_id in
3454: (select mdl.delivery_id
3455: from mst_trips t,
3456: mst_trip_stops ts,
3457: mst_delivery_legs mdl
3458: where mdl.plan_id = md.plan_id
3459: and ts.plan_id = mdl.plan_id
3460: and (ts.stop_id = mdl.pick_up_stop_id
3473: if p_activity_type is null and p_mode is not null then
3474: select count(mdd.delivery_detail_id)
3475: into l_total_orders
3476: from mst_trips mt,
3477: mst_trip_stops mts,
3478: mst_delivery_legs mdl,
3479: mst_deliveries md,
3480: mst_delivery_details mdd,
3481: mst_delivery_assignments mda
3501: */
3502: /*select count(mdd.delivery_detail_id)
3503: into l_total_orders
3504: from mst_trips mt,
3505: mst_trip_stops mts,
3506: mst_delivery_legs mdl,
3507: mst_deliveries md,
3508: mst_delivery_details mdd,
3509: mst_delivery_assignments mda
3528: AND MD.DELIVERY_ID = MDA.DELIVERY_ID
3529: AND MD.DELIVERY_ID IN
3530: (SELECT MDL.DELIVERY_ID
3531: FROM MST_TRIPS T,
3532: MST_TRIP_STOPS TS,
3533: MST_DELIVERY_LEGS MDL
3534: WHERE MDL.PLAN_ID = MD.PLAN_ID
3535: AND TS.PLAN_ID = MDL.PLAN_ID
3536: AND TS.STOP_ID = MDL.PICK_UP_STOP_ID
3553: */
3554: /*select count(mdd.delivery_detail_id)
3555: into l_total_orders
3556: from mst_trips mt,
3557: mst_trip_stops mts,
3558: mst_delivery_legs mdl,
3559: mst_deliveries md,
3560: mst_delivery_details mdd,
3561: mst_delivery_assignments mda
3579: AND MD.DELIVERY_ID = MDA.DELIVERY_ID
3580: AND MD.DELIVERY_ID IN
3581: (SELECT MDL.DELIVERY_ID
3582: FROM MST_TRIPS T,
3583: MST_TRIP_STOPS TS,
3584: MST_DELIVERY_LEGS MDL
3585: WHERE MDL.PLAN_ID = MD.PLAN_ID
3586: AND TS.PLAN_ID = MDL.PLAN_ID
3587: AND TS.STOP_ID = MDL.DROP_OFF_STOP_ID
3630: NVL(mdl.allocated_transport_cost,0) +
3631: NVL(mdl.allocated_fac_shp_hand_cost,0) )
3632: INTO l_loading_cost
3633: FROM mst_trips mt,
3634: mst_trip_stops mts,
3635: mst_delivery_legs mdl
3636: WHERE mt.plan_id = p_plan_id
3637: AND mt.mode_of_transport = p_mode
3638: AND mt.plan_id = mts.plan_id
3651: ( SELECT md.delivery_id
3652: FROM mst_delivery_legs mdl1,
3653: mst_deliveries md,
3654: mst_trips mt,
3655: mst_trip_stops mts
3656: WHERE mt.plan_id = mdl1.plan_id
3657: AND mt.trip_id = mdl1.trip_id
3658: AND mt.mode_of_transport = p_mode
3659: and mt.plan_id = mts.plan_Id
3677: ( SELECT md.delivery_id
3678: FROM mst_delivery_legs mdl1,
3679: mst_deliveries md,
3680: mst_trips mt,
3681: mst_trip_stops mts
3682: WHERE mt.plan_id = mdl1.plan_id
3683: AND mt.trip_id = mdl1.trip_id
3684: AND mt.mode_of_transport = p_mode
3685: and mt.plan_id = mts.plan_Id
3733: NVL(mdl.allocated_transport_cost,0) +
3734: NVL(mdl.allocated_fac_rec_hand_cost,0) )
3735: into l_unloading_cost
3736: from mst_trips mt,
3737: mst_trip_stops mts,
3738: mst_delivery_legs mdl
3739: where mt.plan_id = p_plan_id
3740: and mt.mode_of_transport = p_mode
3741: and mt.plan_id = mts.plan_id
3754: ( SELECT md.delivery_id
3755: FROM mst_delivery_legs mdl1,
3756: mst_deliveries md,
3757: mst_trips mt,
3758: mst_trip_stops mts
3759: WHERE mt.plan_id = mdl1.plan_id
3760: AND mt.trip_id = mdl1.trip_id
3761: AND mt.mode_of_transport = p_mode
3762: and mt.plan_id = mts.plan_Id
3782: ( SELECT md.delivery_id
3783: FROM mst_delivery_legs mdl1,
3784: mst_deliveries md,
3785: mst_trips mt,
3786: mst_trip_stops mts
3787: WHERE mt.plan_id = mdl1.plan_id
3788: AND mt.trip_id = mdl1.trip_id
3789: AND mt.mode_of_transport = p_mode
3790: and mt.plan_id = mts.plan_Id
3819:
3820: cursor departing_delivery_leg is
3821: select sum(mdl.allocated_fac_loading_cost + mdl.ALLOCATED_FAC_SHP_HAND_COST + mdl.allocated_transport_cost) total_departing_cost
3822: from mst_trips mt,
3823: mst_trip_stops mts,
3824: mst_delivery_legs mdl
3825: where mt.plan_id = p_plan_id
3826: and mt.mode_of_transport = p_mode
3827: and mt.trip_id = mts.trip_id
3830:
3831: cursor arriving_delivery_leg is
3832: select sum(mdl.allocated_fac_unloading_cost + mdl.ALLOCATED_FAC_REC_HAND_COST+ mdl.allocated_transport_cost) total_arriving_cost
3833: from mst_trips mt,
3834: mst_trip_stops mts,
3835: mst_delivery_legs mdl
3836: where mt.plan_id = p_plan_id
3837: and mt.mode_of_transport = p_mode
3838: and mt.trip_id = mts.trip_id
3863: -- -----------------------------------------
3864: select sum(md.gross_weight)
3865: into l_loading_weight
3866: from mst_trips mt,
3867: mst_trip_stops mts,
3868: mst_delivery_legs mdl,
3869: mst_deliveries md
3870: where mt.plan_id = p_plan_id
3871: and mt.mode_of_transport = p_mode
3897: -- -----------------------------------------
3898: select sum(md.gross_weight)
3899: into l_unloading_weight
3900: from mst_trips mt,
3901: mst_trip_stops mts,
3902: mst_delivery_legs mdl,
3903: mst_deliveries md
3904: where mt.plan_id = p_plan_id
3905: and mt.mode_of_transport = p_mode
3931: -- -----------------------------------------
3932: select sum(md.volume)
3933: into l_loading_cube
3934: from mst_trips mt,
3935: mst_trip_stops mts,
3936: mst_delivery_legs mdl,
3937: mst_deliveries md
3938: where mt.plan_id = p_plan_id
3939: and mt.mode_of_transport = p_mode
3965: -- -----------------------------------------
3966: select sum(md.volume)
3967: into l_unloading_cube
3968: from mst_trips mt,
3969: mst_trip_stops mts,
3970: mst_delivery_legs mdl,
3971: mst_deliveries md
3972: where mt.plan_id = p_plan_id
3973: and mt.mode_of_transport = p_mode
3999: -- -----------------------------------------
4000: select sum(md.number_of_pallets)
4001: into l_loading_pallets
4002: from mst_trips mt,
4003: mst_trip_stops mts,
4004: mst_delivery_legs mdl,
4005: mst_deliveries md
4006: where mt.plan_id = p_plan_id
4007: and mt.mode_of_transport = p_mode
4033: -- -----------------------------------------
4034: select sum(md.number_of_pallets)
4035: into l_unloading_pallets
4036: from mst_trips mt,
4037: mst_trip_stops mts,
4038: mst_delivery_legs mdl,
4039: mst_deliveries md
4040: where mt.plan_id = p_plan_id
4041: and mt.mode_of_transport = p_mode
4067: -- -----------------------------------------
4068: select sum(md.number_of_pieces)
4069: into l_loading_pieces
4070: from mst_trips mt,
4071: mst_trip_stops mts,
4072: mst_delivery_legs mdl,
4073: mst_deliveries md
4074: where mt.plan_id = p_plan_id
4075: and mt.mode_of_transport = p_mode
4101: -- -----------------------------------------
4102: SELECT sum(md.number_of_pieces)
4103: into l_unloading_pieces
4104: from mst_trips mt,
4105: mst_trip_stops mts,
4106: mst_delivery_legs mdl,
4107: mst_deliveries md
4108: where mt.plan_id = p_plan_id
4109: and mt.mode_of_transport = p_mode
4153: ORDER BY t.mode_of_transport;
4154:
4155: CURSOR stop_locations(p_trip_id IN NUMBER) IS
4156: SELECT ts.stop_location_id
4157: FROM mst_trip_stops ts,
4158: wsh_location_owners wlo
4159: WHERE plan_id = p_plan_id
4160: AND ts.trip_id = p_trip_id
4161: AND ts.stop_location_id = wlo.wsh_location_id
4163: AND owner_type = CARRIER; -- 'carrier'
4164:
4165: CURSOR total_stops(P_TRIP IN NUMBER) IS
4166: SELECT COUNT(mst.stop_id)
4167: FROM mst_trip_stops mst
4168: WHERE mst.plan_id = p_plan_id
4169: AND mst.trip_id = P_TRIP;
4170:
4171: l_stops NUMBER;
4240:
4241: CURSOR trips_to_location(p_location_id IN NUMBER) IS
4242: SELECT t.trip_id, t.mode_of_transport
4243: FROM mst_trips t,
4244: mst_trip_stops ts
4245: WHERE t.plan_id = p_plan_id
4246: AND t.plan_id = ts.plan_id
4247: AND t.trip_id = ts.trip_id
4248: AND ts.stop_location_id = p_location_id
4249: ORDER BY t.mode_of_transport;
4250:
4251: CURSOR total_stops(P_TRIP IN NUMBER) IS
4252: SELECT COUNT(mst.stop_id)
4253: FROM mst_trip_stops mst
4254: WHERE mst.plan_id = p_plan_id
4255: AND mst.trip_id = P_TRIP;
4256:
4257: l_stops NUMBER;
4310: mdl.allocated_fac_loading_cost,
4311: mdl.drop_off_stop_id,
4312: mdl.allocated_fac_unloading_cost,0))
4313: FROM mst_delivery_legs mdl,
4314: mst_trip_stops mts,
4315: mst_trips mt
4316: WHERE mdl.plan_id = p_plan_id
4317: AND mts.plan_id = mdl.plan_id
4318: AND ( mts.stop_id = mdl.pick_up_stop_id
4338: mdl.allocated_fac_shp_hand_cost,
4339: mdl.drop_off_stop_id,
4340: mdl.allocated_fac_Rec_hand_cost,0) )
4341: FROM mst_delivery_legs mdl,
4342: mst_trip_stops mts,
4343: mst_trips mt
4344: WHERE mdl.plan_id = p_plan_id
4345: AND mt.plan_id = mdl.plan_id
4346: AND mt.trip_id = mdl.trip_id
4394: AND md.delivery_id IN
4395: (SELECT mdl.delivery_id
4396: FROM mst_delivery_legs mdl,
4397: mst_trips mt,
4398: mst_trip_stops mts
4399: WHERE mdl.plan_id = md.plan_id
4400: AND mts.plan_Id = mdl.plan_id
4401: AND mts.stop_id = mdl.drop_off_stop_id
4402: AND mts.stop_location_id = p_location_id
4417: AND md.delivery_id IN
4418: (SELECT mdl.delivery_id
4419: FROM mst_delivery_legs mdl,
4420: mst_trips mt,
4421: mst_trip_stops mts
4422: WHERE mdl.plan_id = md.plan_id
4423: AND mts.plan_id = mdl.plan_id
4424: AND mts.stop_id = mdl.pick_up_stop_id
4425: AND mts.stop_location_id = p_location_id
4446: AND md.delivery_id IN
4447: (SELECT mdl.delivery_id
4448: FROM mst_delivery_legs mdl,
4449: mst_trips mt,
4450: mst_trip_stops mts
4451: WHERE mdl.plan_id = md.plan_id
4452: AND mts.plan_Id = mdl.plan_id
4453: AND ( mts.stop_id = mdl.pick_up_stop_id
4454: OR mts.stop_id = mdl.drop_off_stop_id)
4465: AND md.delivery_id IN
4466: (SELECT mdl.delivery_id
4467: FROM mst_delivery_legs mdl,
4468: mst_trips mt,
4469: mst_trip_stops mts
4470: WHERE mdl.plan_id = md.plan_id
4471: AND mts.plan_Id = mdl.plan_id
4472: AND mts.stop_id = mdl.pick_up_stop_id
4473: AND mts.stop_location_id = p_location_id
4485: AND md.delivery_id IN
4486: (SELECT mdl.delivery_id
4487: FROM mst_delivery_legs mdl,
4488: mst_trips mt,
4489: mst_trip_stops mts
4490: WHERE mdl.plan_id = md.plan_id
4491: AND mts.plan_Id = mdl.plan_id
4492: AND mts.stop_id = mdl.drop_off_stop_id
4493: AND mts.stop_location_id = p_location_id
4514: AND md.delivery_id IN
4515: (SELECT mdl.delivery_id
4516: FROM mst_delivery_legs mdl,
4517: mst_trips mt,
4518: mst_trip_stops mts
4519: WHERE mdl.plan_id = md.plan_id
4520: AND mts.plan_Id = mdl.plan_id
4521: AND ( mts.stop_id = mdl.pick_up_stop_id
4522: OR mts.stop_id = mdl.drop_off_stop_id)
4532: AND md.delivery_id IN
4533: (SELECT mdl.delivery_id
4534: FROM mst_delivery_legs mdl,
4535: mst_trips mt,
4536: mst_trip_stops mts
4537: WHERE mdl.plan_id = md.plan_id
4538: AND mts.plan_Id = mdl.plan_id
4539: AND mts.stop_id = mdl.pick_up_stop_id
4540: AND mts.stop_location_id = p_location_id
4551: AND md.delivery_id IN
4552: (SELECT mdl.delivery_id
4553: FROM mst_delivery_legs mdl,
4554: mst_trips mt,
4555: mst_trip_stops mts
4556: WHERE mdl.plan_id = md.plan_id
4557: AND mts.plan_Id = mdl.plan_id
4558: AND mts.stop_id = mdl.drop_off_stop_id
4559: AND mts.stop_location_id = p_location_id
4599: AND md.delivery_id IN
4600: (SELECT mdl.delivery_id
4601: FROM mst_delivery_legs mdl,
4602: mst_trips mt,
4603: mst_trip_stops mts
4604: WHERE mdl.plan_id = md.plan_id
4605: AND mts.plan_Id = mdl.plan_id
4606: AND mts.stop_id = mdl.drop_off_stop_id
4607: AND mts.stop_location_id = p_location_id
4622: AND md.delivery_id IN
4623: (SELECT mdl.delivery_id
4624: FROM mst_delivery_legs mdl,
4625: mst_trips mt,
4626: mst_trip_stops mts
4627: WHERE mdl.plan_id = md.plan_id
4628: AND mts.plan_id = mdl.plan_id
4629: AND mts.stop_id = mdl.pick_up_stop_id
4630: AND mts.stop_location_id = p_location_id
4651: AND md.delivery_id IN
4652: (SELECT mdl.delivery_id
4653: FROM mst_delivery_legs mdl,
4654: mst_trips mt,
4655: mst_trip_stops mts
4656: WHERE mdl.plan_id = md.plan_id
4657: AND mts.plan_Id = mdl.plan_id
4658: AND (mts.stop_id = mdl.pick_up_stop_id
4659: OR mts.stop_id = mdl.drop_off_stop_id)
4670: AND md.delivery_id IN
4671: (SELECT mdl.delivery_id
4672: FROM mst_delivery_legs mdl,
4673: mst_trips mt,
4674: mst_trip_stops mts
4675: WHERE mdl.plan_id = md.plan_id
4676: AND mts.plan_Id = mdl.plan_id
4677: AND mts.stop_id = mdl.pick_up_stop_id
4678: AND mts.stop_location_id = p_location_id
4690: AND md.delivery_id IN
4691: (SELECT mdl.delivery_id
4692: FROM mst_delivery_legs mdl,
4693: mst_trips mt,
4694: mst_trip_stops mts
4695: WHERE mdl.plan_id = md.plan_id
4696: AND mts.plan_Id = mdl.plan_id
4697: AND mts.stop_id = mdl.drop_off_stop_id
4698: AND mts.stop_location_id = p_location_id
4719: AND md.delivery_id IN
4720: (SELECT mdl.delivery_id
4721: FROM mst_delivery_legs mdl,
4722: mst_trips mt,
4723: mst_trip_stops mts
4724: WHERE mdl.plan_id = md.plan_id
4725: AND mts.plan_Id = mdl.plan_id
4726: AND (mts.stop_id = mdl.pick_up_stop_id
4727: OR mts.stop_id = mdl.drop_off_stop_id)
4737: AND md.delivery_id IN
4738: (SELECT mdl.delivery_id
4739: FROM mst_delivery_legs mdl,
4740: mst_trips mt,
4741: mst_trip_stops mts
4742: WHERE mdl.plan_id = md.plan_id
4743: AND mts.plan_Id = mdl.plan_id
4744: AND mts.stop_id = mdl.pick_up_stop_id
4745: AND mts.stop_location_id = p_location_id
4756: AND md.delivery_id IN
4757: (SELECT mdl.delivery_id
4758: FROM mst_delivery_legs mdl,
4759: mst_trips mt,
4760: mst_trip_stops mts
4761: WHERE mdl.plan_id = md.plan_id
4762: AND mts.plan_Id = mdl.plan_id
4763: AND mts.stop_id = mdl.drop_off_stop_id
4764: AND mts.stop_location_id = p_location_id
4802: AND md.delivery_id IN
4803: (SELECT mdl.delivery_id
4804: FROM mst_delivery_legs mdl,
4805: mst_trips mt,
4806: mst_trip_stops mts
4807: WHERE mdl.plan_id = md.plan_id
4808: AND mts.plan_Id = mdl.plan_id
4809: AND mts.stop_id = mdl.drop_off_stop_id
4810: AND mts.stop_location_id = p_location_id
4825: AND md.delivery_id IN
4826: (SELECT mdl.delivery_id
4827: FROM mst_delivery_legs mdl,
4828: mst_trips mt,
4829: mst_trip_stops mts
4830: WHERE mdl.plan_id = md.plan_id
4831: AND mts.plan_id = mdl.plan_id
4832: AND mts.stop_id = mdl.pick_up_stop_id
4833: AND mts.stop_location_id = p_location_id
4854: AND md.delivery_id IN
4855: (SELECT mdl.delivery_id
4856: FROM mst_delivery_legs mdl,
4857: mst_trips mt,
4858: mst_trip_stops mts
4859: WHERE mdl.plan_id = md.plan_id
4860: AND mts.plan_Id = mdl.plan_id
4861: AND ( mts.stop_id = mdl.pick_up_stop_id
4862: OR mts.stop_id = mdl.drop_off_stop_id)
4873: AND md.delivery_id IN
4874: (SELECT mdl.delivery_id
4875: FROM mst_delivery_legs mdl,
4876: mst_trips mt,
4877: mst_trip_stops mts
4878: WHERE mdl.plan_id = md.plan_id
4879: AND mts.plan_Id = mdl.plan_id
4880: AND mts.stop_id = mdl.pick_up_stop_id
4881: AND mts.stop_location_id = p_location_id
4893: AND md.delivery_id IN
4894: (SELECT mdl.delivery_id
4895: FROM mst_delivery_legs mdl,
4896: mst_trips mt,
4897: mst_trip_stops mts
4898: WHERE mdl.plan_id = md.plan_id
4899: AND mts.plan_Id = mdl.plan_id
4900: AND mts.stop_id = mdl.drop_off_stop_id
4901: AND mts.stop_location_id = p_location_id
4922: AND md.delivery_id IN
4923: (SELECT mdl.delivery_id
4924: FROM mst_delivery_legs mdl,
4925: mst_trips mt,
4926: mst_trip_stops mts
4927: WHERE mdl.plan_id = md.plan_id
4928: AND mts.plan_Id = mdl.plan_id
4929: AND ( mts.stop_id = mdl.pick_up_stop_id
4930: OR mts.stop_id = mdl.drop_off_stop_id)
4940: AND md.delivery_id IN
4941: (SELECT mdl.delivery_id
4942: FROM mst_delivery_legs mdl,
4943: mst_trips mt,
4944: mst_trip_stops mts
4945: WHERE mdl.plan_id = md.plan_id
4946: AND mts.plan_Id = mdl.plan_id
4947: AND mts.stop_id = mdl.pick_up_stop_id
4948: AND mts.stop_location_id = p_location_id
4959: AND md.delivery_id IN
4960: (SELECT mdl.delivery_id
4961: FROM mst_delivery_legs mdl,
4962: mst_trips mt,
4963: mst_trip_stops mts
4964: WHERE mdl.plan_id = md.plan_id
4965: AND mts.plan_Id = mdl.plan_id
4966: AND mts.stop_id = mdl.drop_off_stop_id
4967: AND mts.stop_location_id = p_location_id
5005: AND md.delivery_id IN
5006: (SELECT mdl.delivery_id
5007: FROM mst_delivery_legs mdl,
5008: mst_trips mt,
5009: mst_trip_stops mts
5010: WHERE mdl.plan_id = md.plan_id
5011: AND mts.plan_Id = mdl.plan_id
5012: AND mts.stop_id = mdl.drop_off_stop_id
5013: AND mts.stop_location_id = p_location_id
5028: AND md.delivery_id IN
5029: (SELECT mdl.delivery_id
5030: FROM mst_delivery_legs mdl,
5031: mst_trips mt,
5032: mst_trip_stops mts
5033: WHERE mdl.plan_id = md.plan_id
5034: AND mts.plan_id = mdl.plan_id
5035: AND mts.stop_id = mdl.pick_up_stop_id
5036: AND mts.stop_location_id = p_location_id
5057: AND md.delivery_id IN
5058: (SELECT mdl.delivery_id
5059: FROM mst_delivery_legs mdl,
5060: mst_trips mt,
5061: mst_trip_stops mts
5062: WHERE mdl.plan_id = md.plan_id
5063: AND mts.plan_Id = mdl.plan_id
5064: AND ( mts.stop_id = mdl.pick_up_stop_id
5065: OR mts.stop_id = mdl.drop_off_stop_id)
5076: AND md.delivery_id IN
5077: (SELECT mdl.delivery_id
5078: FROM mst_delivery_legs mdl,
5079: mst_trips mt,
5080: mst_trip_stops mts
5081: WHERE mdl.plan_id = md.plan_id
5082: AND mts.plan_Id = mdl.plan_id
5083: AND mts.stop_id = mdl.pick_up_stop_id
5084: AND mts.stop_location_id = p_location_id
5096: AND md.delivery_id IN
5097: (SELECT mdl.delivery_id
5098: FROM mst_delivery_legs mdl,
5099: mst_trips mt,
5100: mst_trip_stops mts
5101: WHERE mdl.plan_id = md.plan_id
5102: AND mts.plan_Id = mdl.plan_id
5103: AND mts.stop_id = mdl.drop_off_stop_id
5104: AND mts.stop_location_id = p_location_id
5125: AND md.delivery_id IN
5126: (SELECT mdl.delivery_id
5127: FROM mst_delivery_legs mdl,
5128: mst_trips mt,
5129: mst_trip_stops mts
5130: WHERE mdl.plan_id = md.plan_id
5131: AND mts.plan_Id = mdl.plan_id
5132: AND ( mts.stop_id = mdl.pick_up_stop_id
5133: OR mts.stop_id = mdl.drop_off_stop_id)
5143: AND md.delivery_id IN
5144: (SELECT mdl.delivery_id
5145: FROM mst_delivery_legs mdl,
5146: mst_trips mt,
5147: mst_trip_stops mts
5148: WHERE mdl.plan_id = md.plan_id
5149: AND mts.plan_Id = mdl.plan_id
5150: AND mts.stop_id = mdl.pick_up_stop_id
5151: AND mts.stop_location_id = p_location_id
5162: AND md.delivery_id IN
5163: (SELECT mdl.delivery_id
5164: FROM mst_delivery_legs mdl,
5165: mst_trips mt,
5166: mst_trip_stops mts
5167: WHERE mdl.plan_id = md.plan_id
5168: AND mts.plan_Id = mdl.plan_id
5169: AND mts.stop_id = mdl.drop_off_stop_id
5170: AND mts.stop_location_id = p_location_id
5217: AND MD.DELIVERY_ID = MDA.DELIVERY_ID
5218: AND MD.DELIVERY_ID IN
5219: (SELECT MDL.DELIVERY_ID
5220: FROM MST_TRIPS T,
5221: MST_TRIP_STOPS TS,
5222: MST_DELIVERY_LEGS MDL
5223: WHERE MDL.PLAN_ID = MD.PLAN_ID
5224: AND TS.PLAN_ID = MDL.PLAN_ID
5225: AND ( TS.STOP_ID = MDL.PICK_UP_STOP_ID
5242: AND MD.DELIVERY_ID = MDA.DELIVERY_ID
5243: AND MD.DELIVERY_ID IN
5244: (SELECT MDL.DELIVERY_ID
5245: FROM MST_TRIPS T,
5246: MST_TRIP_STOPS TS,
5247: MST_DELIVERY_LEGS MDL
5248: WHERE MDL.PLAN_ID = MD.PLAN_ID
5249: AND TS.PLAN_ID = MDL.PLAN_ID
5250: AND TS.STOP_ID = MDL.PICK_UP_STOP_ID
5268: AND MD.DELIVERY_ID = MDA.DELIVERY_ID
5269: AND MD.DELIVERY_ID IN
5270: (SELECT MDL.DELIVERY_ID
5271: FROM MST_TRIPS T,
5272: MST_TRIP_STOPS TS,
5273: MST_DELIVERY_LEGS MDL
5274: WHERE MDL.PLAN_ID = MD.PLAN_ID
5275: AND TS.PLAN_ID = MDL.PLAN_ID
5276: AND TS.STOP_ID = MDL.DROP_OFF_STOP_ID
5302: AND MD.DELIVERY_ID = MDA.DELIVERY_ID
5303: AND MD.DELIVERY_ID IN
5304: (SELECT MDL.DELIVERY_ID
5305: FROM MST_TRIPS T,
5306: MST_TRIP_STOPS TS,
5307: MST_DELIVERY_LEGS MDL
5308: WHERE MDL.PLAN_ID = MD.PLAN_ID
5309: AND TS.PLAN_ID = MDL.PLAN_ID
5310: AND ( TS.STOP_ID = MDL.PICK_UP_STOP_ID
5329: AND mda.delivery_detail_id = mdd.delivery_detail_id
5330: AND md.delivery_id IN
5331: ( SELECT mdl.delivery_id
5332: FROM mst_delivery_legs mdl,
5333: mst_trip_stops mts
5334: WHERE mdl.plan_id = md.plan_id
5335: AND mdl.plan_id = mts.plan_id
5336: AND mdl.pick_up_stop_id = mts.stop_id
5337: AND mts.stop_location_id = p_fac_location_id);
5351: AND mda.delivery_detail_id = mdd.delivery_detail_id
5352: AND md.delivery_id IN
5353: ( SELECT mdl.delivery_id
5354: FROM mst_delivery_legs mdl,
5355: mst_trip_stops mts
5356: WHERE mdl.plan_id = md.plan_id
5357: AND mdl.plan_id = mts.plan_id
5358: AND mdl.drop_off_stop_id = mts.stop_id
5359: AND mts.stop_location_id = p_fac_location_id);
5373: AND MD.DELIVERY_ID = MDA.DELIVERY_ID
5374: AND MD.DELIVERY_ID IN
5375: (SELECT MDL.DELIVERY_ID
5376: --FROM MST_TRIPS T,
5377: FROM MST_TRIP_STOPS TS,
5378: MST_DELIVERY_LEGS MDL
5379: WHERE MDL.PLAN_ID = MD.PLAN_ID
5380: AND TS.PLAN_ID = MDL.PLAN_ID
5381: AND TS.STOP_ID = MDL.PICK_UP_STOP_ID
5400: AND MD.DELIVERY_ID = MDA.DELIVERY_ID
5401: AND MD.DELIVERY_ID IN
5402: (SELECT MDL.DELIVERY_ID
5403: --FROM MST_TRIPS T,
5404: FROM MST_TRIP_STOPS TS,
5405: MST_DELIVERY_LEGS MDL
5406: WHERE MDL.PLAN_ID = MD.PLAN_ID
5407: AND TS.PLAN_ID = MDL.PLAN_ID
5408: AND TS.STOP_ID = MDL.DROP_OFF_STOP_ID
5447: WHERE mt.plan_id = p_plan_id
5448: AND mt.trip_id IN
5449: (SELECT mdl.trip_id
5450: FROM mst_delivery_legs mdl,
5451: mst_trip_stops mts
5452: WHERE mdl.plan_id = mt.plan_id
5453: AND mts.plan_id = mdl.plan_id
5454: AND mts.stop_id = mdl.drop_off_stop_id
5455: AND mts.stop_location_id = P_location_id)
5468: AND mt.trip_id IN
5469: (SELECT mdl.trip_id
5470: FROM mst_deliveries md,
5471: mst_delivery_legs mdl,
5472: mst_trip_stops mts
5473: WHERE mdl.plan_id = mt.plan_id
5474: AND mts.plan_id = mdl.plan_id
5475: AND mts.stop_id = mdl.pick_up_stop_id
5476: AND mts.stop_location_id = p_location_Id)
5488: WHERE mt.plan_id = p_plan_id
5489: AND mt.trip_id IN
5490: (SELECT mdl.trip_id
5491: FROM mst_delivery_legs mdl,
5492: mst_trip_stops mts
5493: WHERE mdl.plan_id = mt.plan_id
5494: AND mts.plan_id = mdl.plan_id
5495: AND ( mts.stop_id = mdl.pick_up_stop_id
5496: OR mts.stop_id = mdl.drop_off_stop_id)
5514: WHERE mt.plan_id = p_plan_id
5515: AND mt.trip_id IN
5516: (SELECT mdl.trip_id
5517: FROM mst_delivery_legs mdl,
5518: mst_trip_stops mts
5519: WHERE mdl.plan_id = mt.plan_id
5520: AND mts.plan_id = mdl.plan_id
5521: AND ( mts.stop_id = mdl.pick_up_stop_id
5522: OR mts.stop_id = mdl.drop_off_stop_id)
5529: WHERE mt.plan_id = p_plan_id
5530: AND mt.trip_id IN
5531: (SELECT mdl.trip_id
5532: FROM mst_delivery_legs mdl,
5533: mst_trip_stops mts
5534: WHERE mdl.plan_id = mt.plan_id
5535: AND mts.plan_id = mdl.plan_id
5536: AND mts.stop_id = mdl.pick_up_stop_id
5537: AND mts.stop_location_id = P_location_id);
5545: WHERE mt.plan_id = p_plan_id
5546: AND mt.trip_id IN
5547: (SELECT mdl.trip_id
5548: FROM mst_delivery_legs mdl,
5549: mst_trip_stops mts
5550: WHERE mdl.plan_id = mt.plan_id
5551: AND mts.plan_id = mdl.plan_id
5552: AND mts.stop_id = mdl.drop_off_stop_id
5553: AND mts.stop_location_id = P_location_id);
5594: WHERE mt.plan_id = p_plan_id
5595: AND mt.trip_id IN
5596: (SELECT mdl.trip_id
5597: FROM mst_delivery_legs mdl,
5598: mst_trip_stops mts
5599: WHERE mdl.plan_id = mt.plan_id
5600: AND mts.plan_id = mdl.plan_id
5601: AND mts.stop_id = mdl.pick_up_stop_Id
5602: AND mts.stop_location_id = p_location_id)
5602: AND mts.stop_location_id = p_location_id)
5603: AND mt.mode_of_transport = TRUCK -- p_mode_of_transport
5604: --AND mt.carrier_id = p_carrier_id
5605: AND EXISTS (SELECT mts1.trip_id
5606: FROM mst_trip_stops mts1
5607: WHERE mts1.plan_id = mt.plan_id
5608: AND mts1.trip_Id = mt.trip_ID
5609: HAVING COUNT(mts1.stop_id) =2
5610: GROUP BY mts1.trip_id);
5616: WHERE mt.plan_id = p_plan_id
5617: AND mt.trip_id IN
5618: (SELECT mdl.trip_id
5619: FROM mst_delivery_legs mdl,
5620: mst_trip_stops mts
5621: WHERE mdl.plan_id = mt.plan_id
5622: AND mts.plan_id = mdl.plan_id
5623: AND mts.stop_id = mdl.drop_off_stop_id
5624: AND mts.stop_location_id = p_location_id)
5624: AND mts.stop_location_id = p_location_id)
5625: AND mt.mode_of_transport = TRUCK -- p_mode_of_transport
5626: --AND mt.carrier_id = p_carrier_id
5627: AND EXISTS (SELECT mts1.trip_id
5628: FROM mst_trip_stops mts1
5629: WHERE mts1.plan_id = mt.plan_id
5630: AND mts1.trip_Id = mt.trip_ID
5631: HAVING COUNT(mts1.stop_id) =2
5632: GROUP BY mts1.trip_id);
5649: WHERE mt.plan_id = p_plan_id
5650: AND mt.trip_id IN
5651: (SELECT mdl.trip_id
5652: FROM mst_delivery_legs mdl,
5653: mst_trip_stops mts
5654: WHERE mdl.plan_id = mt.plan_id
5655: AND mts.plan_id = mdl.plan_id
5656: AND mts.stop_id = mdl.pick_up_stop_Id
5657: AND mts.stop_location_id = p_location_id)
5657: AND mts.stop_location_id = p_location_id)
5658: AND mt.mode_of_transport = TRUCK -- p_mode_of_transport
5659: --AND mt.carrier_id = p_carrier_id
5660: AND EXISTS (SELECT mts1.trip_id
5661: FROM mst_trip_stops mts1
5662: WHERE mts1.plan_id = mt.plan_id
5663: AND mts1.trip_Id = mt.trip_ID
5664: HAVING COUNT(mts1.stop_id) > 2
5665: GROUP BY mts1.trip_id);
5671: WHERE mt.plan_id = p_plan_id
5672: AND mt.trip_id IN
5673: (SELECT mdl.trip_id
5674: FROM mst_delivery_legs mdl,
5675: mst_trip_stops mts
5676: WHERE mdl.plan_id = mt.plan_id
5677: AND mts.plan_id = mdl.plan_id
5678: AND mts.stop_id = mdl.drop_off_stop_Id
5679: AND mts.stop_location_id = p_location_id)
5679: AND mts.stop_location_id = p_location_id)
5680: AND mt.mode_of_transport = TRUCK -- p_mode_of_transport
5681: --AND mt.carrier_id = p_carrier_id
5682: AND EXISTS (SELECT mts1.trip_id
5683: FROM mst_trip_stops mts1
5684: WHERE mts1.plan_id = mt.plan_id
5685: AND mts1.trip_Id = mt.trip_ID
5686: HAVING COUNT(mts1.stop_id) > 2
5687: GROUP BY mts1.trip_id);
5716: NVL(mdl.allocated_fac_shp_hand_cost,0),
5717: mdl.drop_off_stop_id,
5718: NVL(mdl.allocated_fac_Rec_hand_cost,0),0) ), 0)
5719: FROM mst_delivery_legs mdl,
5720: mst_trip_stops mts,
5721: mst_trips mt
5722: WHERE mdl.plan_id = p_plan_id
5723: AND mt.plan_id = mdl.plan_id
5724: AND mt.trip_id = mdl.trip_id
5744: NVL(mdl.allocated_fac_shp_hand_cost,0),
5745: mdl.drop_off_stop_id,
5746: NVL(mdl.allocated_fac_Rec_hand_cost,0),0) ), 0)
5747: FROM mst_delivery_legs mdl,
5748: mst_trip_stops mts,
5749: mst_trips mt
5750: WHERE mdl.plan_id = p_plan_id
5751: AND mt.plan_id = mdl.plan_id
5752: AND mt.trip_id = mdl.trip_id
5994: from mst_deliveries md
5995: where md.plan_id = p_plan_id
5996: and exists (select 1
5997: from mst_delivery_legs mdl,
5998: mst_trip_stops mts,
5999: mst_trips mt
6000: where mdl.delivery_id = md.delivery_id
6001: and mdl.pick_up_stop_id = mts.stop_id
6002: and mt.trip_id = p_trip_id
6017: from mst_deliveries md
6018: where md.plan_id = p_plan_id
6019: and exists (select 1
6020: from mst_delivery_legs mdl,
6021: mst_trip_stops mts,
6022: mst_trips mt
6023: where mdl.delivery_id = md.delivery_id
6024: and mdl.pick_up_stop_id = mts.stop_id
6025: and mt.trip_id = p_trip_id
6040: from mst_deliveries md
6041: where md.plan_id = p_plan_id
6042: and exists (select 1
6043: from mst_delivery_legs mdl,
6044: mst_trip_stops mts,
6045: mst_trips mt
6046: where mdl.delivery_id = md.delivery_id
6047: and mdl.pick_up_stop_id = mts.stop_id
6048: and mt.trip_id = p_trip_id
6063: from mst_deliveries md
6064: where md.plan_id = p_plan_id
6065: and exists (select 1
6066: from mst_delivery_legs mdl,
6067: mst_trip_stops mts,
6068: mst_trips mt
6069: where mdl.delivery_id = md.delivery_id
6070: and mdl.pick_up_stop_id = mts.stop_id
6071: and mt.trip_id = p_trip_id
6085: from mst_trips mt
6086: where mt.mode_of_transport = 'TRUCK'
6087: and mt.plan_id = p_plan_id
6088: and mt.trip_id in (select mts.trip_id
6089: from mst_trip_stops mts
6090: where mt.trip_id = mts.trip_id
6091: and mt.plan_id = mts.plan_id
6092: having count(mts.trip_id) = 2
6093: group by mts.trip_id);
6103: from mst_trips mt
6104: where mt.mode_of_transport = 'TRUCK'
6105: and mt.plan_id = p_plan_id
6106: and mt.trip_id in (select mts.trip_id
6107: from mst_trip_stops mts
6108: where mt.trip_id = mts.trip_id
6109: and mt.plan_id = mts.plan_id
6110: having count(mts.trip_id) > 2
6111: group by mts.trip_id);