[Home] [Help]
35: and md.delivery_id = mda.delivery_id
36: and md.delivery_id in
37: (select mdl.delivery_id
38: from mst_trips t
39: , mst_trip_stops ts
40: , mst_delivery_legs mdl
41: , fte_location_parameters flp
42: where mdl.plan_id = md.plan_id
43: and ts.plan_id = mdl.plan_id
71: and decode(l_c_s_ident, 2, md.customer_id, md.supplier_id) = l_cust_supp_id
72: and md.delivery_id in
73: (select mdl.delivery_id
74: from mst_delivery_legs mdl
75: , mst_trip_stops mts
76: where mdl.plan_id = md.plan_id
77: and mdl.plan_id = mts.plan_id
78: and ( mdl.pick_up_stop_id = mts.stop_id
79: or mdl.drop_off_stop_id = mts.stop_id))
535: , fte_location_parameters flp
536: where mt.plan_id = l_plan_id
537: and mt.mode_of_transport = l_mode
538: and flp.location_id in (select mts.stop_location_id
539: from mst_trip_stops mts
540: where mts.plan_id = mt.plan_id
541: and mts.trip_id = mt.trip_id)
542: and flp.facility_id = l_myfac_id;
543:
549: from mst_trips mt
550: where mt.plan_id = l_plan_id
551: and mt.trip_id in
552: (select distinct mts.trip_id
553: from mst_trip_stops mts
554: , mst_delivery_legs mdl
555: , mst_deliveries md
556: where md.plan_id = mt.plan_id
557: and decode(l_c_s_ident, 2, md.customer_id, md.supplier_id) = l_cust_supp_id
563: and mdl.drop_off_stop_id = mts.stop_id)
564: and mt.mode_of_transport = l_mode
565: and EXISTS
566: (select ts.trip_id
567: from mst_trip_stops ts
568: where ts.plan_id = mt.plan_id
569: and ts.trip_id = mt.trip_id
570: having count(ts.stop_id) >= 2
571: group by ts.trip_id);
633: ( select md.delivery_id
634: from mst_delivery_legs mdl1
635: , mst_deliveries md
636: , mst_trips mt
637: , mst_trip_stops mts
638: , fte_location_parameters flp
639: where mt.plan_id = mdl1.plan_id
640: and mt.trip_id = mdl1.trip_id
641: and mt.mode_of_transport = l_mode
682: ( select md.delivery_id
683: from mst_delivery_legs mdl1
684: , mst_deliveries md
685: , mst_trips mt
686: , mst_trip_stops mts
687: where mt.plan_id = mdl1.plan_id
688: and mt.trip_id = mdl1.trip_id
689: and mt.mode_of_transport = l_mode
690: and mt.plan_id = mts.plan_id
786: ( select md.delivery_id
787: from mst_delivery_legs mdl1
788: , mst_deliveries md
789: , mst_trips mt
790: , mst_trip_stops mts
791: , fte_location_parameters flp
792: where mt.plan_id = mdl1.plan_id
793: and mt.trip_id = mdl1.trip_id
794: and mt.mode_of_transport = l_mode
832: ( select md.delivery_id
833: from mst_delivery_legs mdl1
834: , mst_deliveries md
835: , mst_trips mt
836: , mst_trip_stops mts
837: where mt.plan_id = mdl1.plan_id
838: and mt.trip_id = mdl1.trip_id
839: and mt.mode_of_transport = l_mode
840: and mt.plan_id = mts.plan_id
937: ( select md.delivery_id
938: from mst_delivery_legs mdl1
939: , mst_deliveries md
940: , mst_trips mt
941: , mst_trip_stops mts
942: , fte_location_parameters flp
943: where mt.plan_id = mdl1.plan_id
944: and mt.trip_id = mdl1.trip_id
945: and mt.mode_of_transport = l_mode
989: ( select md.delivery_id
990: from mst_delivery_legs mdl1
991: , mst_deliveries md
992: , mst_trips mt
993: , mst_trip_stops mts
994: where mt.plan_id = mdl1.plan_id
995: and mt.trip_id = mdl1.trip_id
996: and mt.mode_of_transport = l_mode
997: and mt.plan_id = mts.plan_id
1075: l_denom number;
1076:
1077: cursor cur_TL_stops (l_plan_id in number) is
1078: select count(*)
1079: from mst_trip_stops mts
1080: , mst_trips mt
1081: where mt.plan_id = mts.plan_id
1082: and mt.trip_id = mts.trip_id
1083: and mt.mode_of_transport = 'TRUCK'
1086: cursor cur_TL_stops_myfac (l_plan_id in number
1087: , l_myfac_id in number) is
1088: /*
1089: select count(*)
1090: from mst_trip_stops mts
1091: , mst_trips mt
1092: , fte_location_parameters flp
1093: where mt.plan_id = mts.plan_id
1094: and mt.trip_id = mts.trip_id
1099: */
1100: /*
1101: --Bug_Fix for 3693925
1102: select count(*)
1103: from mst_trip_stops mts
1104: where mts.plan_id = l_plan_id
1105: and mts.trip_id in ( select distinct mt.trip_id
1106: from mst_trips mt
1107: , mst_trip_stops mts1
1103: from mst_trip_stops mts
1104: where mts.plan_id = l_plan_id
1105: and mts.trip_id in ( select distinct mt.trip_id
1106: from mst_trips mt
1107: , mst_trip_stops mts1
1108: , fte_location_parameters flp
1109: where mt.plan_id = mts.plan_id
1110: and mt.trip_id = mts.trip_id
1111: and mt.mode_of_transport = 'TRUCK'
1116: */
1117: --Bug_Fix for 3696518 - II
1118: select count ( * )
1119: from mst_trips mt
1120: , mst_trip_stops mts
1121: where mts.plan_id = l_plan_id
1122: and mts.trip_id = mt.trip_id
1123: and mt.plan_id= mts.plan_id
1124: and mt.mode_of_transport = 'TRUCK'
1136: cursor cur_TL_stops_c_s (l_plan_id in number
1137: , l_c_s_ident in number
1138: , l_cust_supp_id in number) is
1139: select count(*)
1140: from mst_trip_stops mts
1141: , mst_trips mt
1142: where mt.plan_id = mts.plan_id
1143: and mt.trip_id = mts.trip_id
1144: and mt.mode_of_transport = 'TRUCK'
1153:
1154: cursor cur_TL_stops_carr (l_plan_id in number
1155: , l_carrier_id in number) is
1156: select count(*)
1157: from mst_trip_stops mts
1158: , mst_trips mt
1159: where mt.plan_id = mts.plan_id
1160: and mt.trip_id = mts.trip_id
1161: and mt.mode_of_transport = 'TRUCK'
1175: from mst_trips mt
1176: where mt.plan_id = l_plan_id
1177: and mt.mode_of_transport = 'TRUCK'
1178: and mt.trip_id in (select distinct mts.trip_id
1179: from mst_trip_stops mts
1180: , fte_location_parameters flp
1181: where mts.plan_id = mt.plan_id
1182: and mts.stop_location_id = flp.location_id
1183: and flp.facility_id = l_myfac_id);
1206: from mst_trips mt
1207: where mt.plan_id = l_plan_id
1208: and mt.mode_of_transport = 'TRUCK'
1209: and mt.trip_id in (select distinct mdl.trip_id
1210: from mst_trip_stops mts
1211: , mst_delivery_legs mdl
1212: , mst_deliveries md
1213: where md.plan_id = mt.plan_id
1214: and decode(l_c_s_ident, 2, md.customer_id, md.supplier_id) = l_cust_supp_id
1219: and mdl.trip_id = mts.trip_id
1220: and mdl.drop_off_stop_id = mts.stop_id)
1221: and EXISTS
1222: (select ts.trip_id
1223: from mst_trip_stops ts
1224: where ts.plan_id = mt.plan_id
1225: and ts.trip_id = mt.trip_id
1226: having count(ts.stop_id) >= 2
1227: group by ts.trip_id);
1328: from mst_trips mt
1329: where mt.plan_id= l_plan_id
1330: and mt.mode_of_transport = 'TRUCK'
1331: and mt.trip_id in (select distinct mts.trip_id
1332: from mst_trip_stops mts
1333: , fte_location_parameters flp
1334: where mts.plan_id = mt.plan_id
1335: and mts.stop_location_id = flp.location_id
1336: and flp.facility_id = l_myfac_id);
1360: from mst_trips mt
1361: where mt.plan_id = l_plan_id
1362: and mt.mode_of_transport = 'TRUCK'
1363: and mt.trip_id in (select distinct mdl.trip_id
1364: from mst_trip_stops mts
1365: , mst_delivery_legs mdl
1366: , mst_deliveries md
1367: where md.plan_id = mt.plan_id
1368: and decode(l_c_s_ident, 2, md.customer_id, md.supplier_id) = l_cust_supp_id
1373: and mdl.trip_id = mts.trip_id
1374: and mdl.drop_off_stop_id = mts.stop_id)
1375: and EXISTS
1376: (select ts.trip_id
1377: from mst_trip_stops ts
1378: where ts.plan_id = mt.plan_id
1379: and ts.trip_id = mt.trip_id
1380: having count(ts.stop_id) >= 2
1381: group by ts.trip_id);
1446: , l_carrier_id in number) is
1447: /*
1448: select count(*)
1449: from mst_trips mt
1450: , mst_trip_stops mts
1451: , fte_location_parameters flp
1452: where mt.plan_id = l_plan_id
1453: and mt.carrier_id = l_carrier_id
1454: and mts.plan_id = mt.plan_id
1482: where mt.plan_id = l_plan_id
1483: and mt.carrier_id = l_carrier_id
1484: and mt.trip_id in
1485: (select distinct mts.trip_id
1486: from mst_trip_stops mts
1487: , mst_delivery_legs mdl
1488: , mst_deliveries md
1489: where md.plan_id = mt.plan_id
1490: and decode(l_c_s_ident, 2, md.customer_id, md.supplier_id) = l_cust_supp_id
1495: and mdl.trip_id = mts.trip_id
1496: and mdl.drop_off_stop_id = mts.stop_id)
1497: and EXISTS
1498: (select ts.trip_id
1499: from mst_trip_stops ts
1500: where ts.plan_id = mt.plan_id
1501: and ts.trip_id = mt.trip_id
1502: having count(ts.stop_id) >= 2
1503: group by ts.trip_id);
1561: , l_carrier_id in number) is
1562: /*
1563: select nvl(sum(nvl(mdl.allocated_transport_cost,0)),0)
1564: from mst_trips mt
1565: , mst_trip_stops mts
1566: , mst_delivery_legs mdl
1567: , fte_location_parameters flp
1568: where mt.plan_id = l_plan_id
1569: and mt.plan_id = mts.plan_id
1702: and mdd.delivery_detail_id = mda.delivery_detail_id
1703: and mda.delivery_id in (select mdl.delivery_id
1704: from mst_deliveries md
1705: , mst_delivery_legs mdl
1706: , mst_trip_stops mts
1707: , fte_location_parameters flp
1708: , wsh_locations wl
1709: where mdl.plan_id = l_plan_id
1710: and mdl.plan_id = mts.plan_id
1750: and md.delivery_id = mda.delivery_id
1751: and md.delivery_id in
1752: (select mdl.delivery_id
1753: from mst_trips t
1754: , mst_trip_stops ts
1755: , mst_delivery_legs mdl
1756: , wsh_locations wl
1757: where mdl.plan_id = md.plan_id
1758: and ts.plan_id = mdl.plan_id
1830: where md.plan_id = l_plan_id
1831: and md.delivery_id in (select mdl.delivery_id
1832: from mst_deliveries md
1833: , mst_delivery_legs mdl
1834: , mst_trip_stops mts
1835: , fte_location_parameters flp
1836: , wsh_locations wl
1837: where mdl.plan_id = l_plan_id
1838: and mdl.plan_id = mts.plan_id
1936: where md.plan_id = l_plan_id
1937: and md.delivery_id in (select mdl.delivery_id
1938: from mst_deliveries md
1939: , mst_delivery_legs mdl
1940: , mst_trip_stops mts
1941: , fte_location_parameters flp
1942: , wsh_locations wl
1943: where mdl.plan_id = l_plan_id
1944: and mdl.plan_id = mts.plan_id
2042: where md.plan_id = l_plan_id
2043: and md.delivery_id in (select mdl.delivery_id
2044: from mst_deliveries md
2045: , mst_delivery_legs mdl
2046: , mst_trip_stops mts
2047: , fte_location_parameters flp
2048: , wsh_locations wl
2049: where mdl.plan_id = l_plan_id
2050: and mdl.plan_id = mts.plan_id
2123: , l_orig_state in varchar2) is
2124: select count(*)
2125: from (select distinct mt.trip_id, count(*) num_stops
2126: from mst_trips mt
2127: , mst_trip_stops mts
2128: where mt.plan_id = l_plan_id
2129: and mt.trip_id in (select distinct mdl.trip_id
2130: from mst_deliveries md
2131: , mst_delivery_legs mdl
2128: where mt.plan_id = l_plan_id
2129: and mt.trip_id in (select distinct mdl.trip_id
2130: from mst_deliveries md
2131: , mst_delivery_legs mdl
2132: , mst_trip_stops mts1
2133: , wsh_locations wl
2134: where md.plan_id = mt.plan_id
2135: and mdl.plan_id = md.plan_id
2136: and mdl.delivery_id = md.delivery_id
2151: , l_orig_state in varchar2) is
2152: select count(*)
2153: from (select distinct mt.trip_id, count(*) num_stops
2154: from mst_trips mt
2155: , mst_trip_stops mts
2156: where mt.plan_id = l_plan_id
2157: and mt.trip_id in (select distinct mdl.trip_id
2158: from mst_deliveries md
2159: , mst_delivery_legs mdl
2156: where mt.plan_id = l_plan_id
2157: and mt.trip_id in (select distinct mdl.trip_id
2158: from mst_deliveries md
2159: , mst_delivery_legs mdl
2160: , mst_trip_stops mts1
2161: , fte_location_parameters flp
2162: , wsh_locations wl
2163: where md.plan_id = mt.plan_id
2164: and mdl.plan_id = md.plan_id
2183: , l_orig_state in varchar2) is
2184: select count(*)
2185: from (select distinct mt.trip_id, count(*) num_stops
2186: from mst_trips mt
2187: , mst_trip_stops mts
2188: where mt.plan_id = l_plan_id
2189: and mt.mode_of_transport = 'TRUCK'
2190: and mts.plan_id = mt.plan_id
2191: and mts.trip_id = mt.trip_id
2192: and mt.trip_id in
2193: (select distinct mdl.trip_id
2194: from mst_deliveries md
2195: , mst_delivery_legs mdl
2196: , mst_trip_stops mts1
2197: , wsh_locations wl
2198: where mdl.plan_id = mt.plan_id
2199: and mdl.pick_up_stop_id = mts1.stop_id
2200: and mdl.trip_id = mts1.trip_id
2213: , l_orig_state in varchar2) is
2214: select count(*)
2215: from (select distinct mt.trip_id, count(*) num_stops
2216: from mst_trips mt
2217: , mst_trip_stops mts
2218: where mt.plan_id = l_plan_id
2219: and mt.carrier_id = l_carrier_id
2220: and mt.trip_id in (select distinct mdl.trip_id
2221: from mst_deliveries md
2219: and mt.carrier_id = l_carrier_id
2220: and mt.trip_id in (select distinct mdl.trip_id
2221: from mst_deliveries md
2222: , mst_delivery_legs mdl
2223: , mst_trip_stops mts1
2224: , wsh_locations wl
2225: where md.plan_id = mt.plan_id
2226: and mdl.plan_id = md.plan_id
2227: and mdl.delivery_id = md.delivery_id
2274: , l_orig_state in varchar2) is
2275: select count(*)
2276: from (select distinct mt.trip_id, count(*) num_stops
2277: from mst_trips mt
2278: , mst_trip_stops mts
2279: where mt.plan_id = l_plan_id
2280: and mt.trip_id in (select distinct mdl.trip_id
2281: from mst_deliveries md
2282: , mst_delivery_legs mdl
2279: where mt.plan_id = l_plan_id
2280: and mt.trip_id in (select distinct mdl.trip_id
2281: from mst_deliveries md
2282: , mst_delivery_legs mdl
2283: , mst_trip_stops mts1
2284: , wsh_locations wl
2285: where md.plan_id = mt.plan_id
2286: and mdl.plan_id = md.plan_id
2287: and mdl.delivery_id = md.delivery_id
2302: , l_orig_state in varchar2) is
2303: select count(*)
2304: from (select distinct mt.trip_id, count(*) num_stops
2305: from mst_trips mt
2306: , mst_trip_stops mts
2307: where mt.plan_id = l_plan_id
2308: and mt.trip_id in (select distinct mdl.trip_id
2309: from mst_deliveries md
2310: , mst_delivery_legs mdl
2307: where mt.plan_id = l_plan_id
2308: and mt.trip_id in (select distinct mdl.trip_id
2309: from mst_deliveries md
2310: , mst_delivery_legs mdl
2311: , mst_trip_stops mts1
2312: , fte_location_parameters flp
2313: , wsh_locations wl
2314: where md.plan_id = mt.plan_id
2315: and mdl.plan_id = md.plan_id
2334: , l_orig_state in varchar2) is
2335: select count(*)
2336: from (select distinct mt.trip_id, count(*) num_stops
2337: from mst_trips mt
2338: , mst_trip_stops mts
2339: where mt.plan_id = l_plan_id
2340: and mt.mode_of_transport = 'TRUCK'
2341: and mts.plan_id = mt.plan_id
2342: and mts.trip_id = mt.trip_id
2343: and mt.trip_id in
2344: (select distinct mdl.trip_id
2345: from mst_deliveries md
2346: , mst_delivery_legs mdl
2347: , mst_trip_stops mts1
2348: , wsh_locations wl
2349: where mdl.plan_id = mt.plan_id
2350: and mdl.pick_up_stop_id = mts1.stop_id
2351: and mdl.trip_id = mts1.trip_id
2364: , l_orig_state in varchar2) is
2365: select count(*)
2366: from (select distinct mt.trip_id, count(*) num_stops
2367: from mst_trips mt
2368: , mst_trip_stops mts
2369: where mt.plan_id = l_plan_id
2370: and mt.carrier_id = l_carrier_id
2371: and mt.trip_id in (select distinct mdl.trip_id
2372: from mst_deliveries md
2370: and mt.carrier_id = l_carrier_id
2371: and mt.trip_id in (select distinct mdl.trip_id
2372: from mst_deliveries md
2373: , mst_delivery_legs mdl
2374: , mst_trip_stops mts1
2375: , wsh_locations wl
2376: where md.plan_id = mt.plan_id
2377: and mdl.plan_id = md.plan_id
2378: and mdl.delivery_id = md.delivery_id
2429: and mt.mode_of_transport = 'LTL'
2430: and mt.trip_id in (select distinct mdl.trip_id
2431: from mst_deliveries md
2432: , mst_delivery_legs mdl
2433: , mst_trip_stops mts1
2434: , wsh_locations wl
2435: where md.plan_id = mt.plan_id
2436: and mdl.plan_id = md.plan_id
2437: and mdl.delivery_id = md.delivery_id
2451: and mt.mode_of_transport = 'LTL'
2452: and mt.trip_id in (select distinct mdl.trip_id
2453: from mst_deliveries md
2454: , mst_delivery_legs mdl
2455: , mst_trip_stops mts1
2456: , fte_location_parameters flp
2457: , wsh_locations wl
2458: where md.plan_id = mt.plan_id
2459: and mdl.plan_id = md.plan_id
2478: and mt.trip_id in
2479: (select distinct mdl.trip_id
2480: from mst_deliveries md
2481: , mst_delivery_legs mdl
2482: , mst_trip_stops mts1
2483: , wsh_locations wl
2484: where mdl.plan_id = mt.plan_id
2485: and mdl.pick_up_stop_id = mts1.stop_id
2486: and mdl.trip_id = mts1.trip_id
2502: and mt.carrier_id = l_carrier_id
2503: and mt.trip_id in (select distinct mdl.trip_id
2504: from mst_deliveries md
2505: , mst_delivery_legs mdl
2506: , mst_trip_stops mts1
2507: , wsh_locations wl
2508: where md.plan_id = mt.plan_id
2509: and mdl.plan_id = md.plan_id
2510: and mdl.delivery_id = md.delivery_id
2555: and mt.mode_of_transport = 'PARCEL'
2556: and mt.trip_id in (select distinct mdl.trip_id
2557: from mst_deliveries md
2558: , mst_delivery_legs mdl
2559: , mst_trip_stops mts1
2560: , wsh_locations wl
2561: where md.plan_id = mt.plan_id
2562: and mdl.plan_id = md.plan_id
2563: and mdl.delivery_id = md.delivery_id
2577: and mt.mode_of_transport = 'PARCEL'
2578: and mt.trip_id in (select distinct mdl.trip_id
2579: from mst_deliveries md
2580: , mst_delivery_legs mdl
2581: , mst_trip_stops mts1
2582: , fte_location_parameters flp
2583: , wsh_locations wl
2584: where md.plan_id = mt.plan_id
2585: and mdl.plan_id = md.plan_id
2604: and mt.trip_id in
2605: (select distinct mdl.trip_id
2606: from mst_deliveries md
2607: , mst_delivery_legs mdl
2608: , mst_trip_stops mts1
2609: , wsh_locations wl
2610: where mdl.plan_id = mt.plan_id
2611: and mdl.pick_up_stop_id = mts1.stop_id
2612: and mdl.trip_id = mts1.trip_id
2628: and mt.carrier_id = l_carrier_id
2629: and mt.trip_id in (select distinct mdl.trip_id
2630: from mst_deliveries md
2631: , mst_delivery_legs mdl
2632: , mst_trip_stops mts1
2633: , wsh_locations wl
2634: where md.plan_id = mt.plan_id
2635: and mdl.plan_id = md.plan_id
2636: and mdl.delivery_id = md.delivery_id
2689: ( select md.delivery_id
2690: from mst_delivery_legs mdl1
2691: , mst_deliveries md
2692: , mst_trips mt
2693: , mst_trip_stops mts
2694: , wsh_locations wl
2695: where mt.plan_id = mdl1.plan_id
2696: and mt.trip_id = mdl1.trip_id
2697: and mt.mode_of_transport = l_mode
2712: + nvl(mdl.allocated_fac_rec_hand_cost,0)),0)
2713: from mst_deliveries md
2714: , mst_delivery_legs mdl
2715: , mst_trips mt
2716: -- , mst_trip_stops mts
2717: , wsh_locations wl
2718: where mt.plan_id = l_plan_id
2719: and mt.mode_of_transport = l_mode
2720: and mt.plan_id = mdl.plan_id
2748: ( select md.delivery_id
2749: from mst_delivery_legs mdl1
2750: , mst_deliveries md
2751: , mst_trips mt
2752: , mst_trip_stops mts
2753: , fte_location_parameters flp
2754: , wsh_locations wl
2755: where mt.plan_id = mdl1.plan_id
2756: and mt.trip_id = mdl1.trip_id
2775: + nvl(mdl.allocated_fac_rec_hand_cost,0)),0)
2776: from mst_deliveries md
2777: , mst_delivery_legs mdl
2778: , mst_trips mt
2779: -- , mst_trip_stops mts
2780: , wsh_locations wl
2781: , fte_location_parameters flp
2782: where mt.plan_id = l_plan_id
2783: and mt.mode_of_transport = l_mode
2808: and mt.trip_id = mdl.trip_id
2809: and mdl.delivery_id in (select mdl.delivery_id
2810: from mst_deliveries md
2811: , mst_delivery_legs mdl
2812: , mst_trip_stops mts
2813: , fte_location_parameters flp
2814: , wsh_locations wl
2815: where mdl.plan_id = l_plan_id
2816: and mdl.plan_id = mts.plan_id
2839: where mdl.plan_id = l_plan_id
2840: and mdl.delivery_id in
2841: ( select md.delivery_id
2842: from mst_trips mt
2843: , mst_trip_stops mts
2844: , mst_delivery_legs mdl1
2845: , mst_deliveries md
2846: , wsh_locations wl
2847: where mt.plan_id = mdl1.plan_id
2865: + nvl(mdl.allocated_fac_rec_hand_cost,0)),0)
2866: from mst_deliveries md
2867: , mst_delivery_legs mdl
2868: , mst_trips mt
2869: -- , mst_trip_stops mts
2870: , wsh_locations wl
2871: where mt.plan_id = l_plan_id
2872: and mt.mode_of_transport = l_mode
2873: and mt.plan_id = mdl.plan_id
2903: and mt.mode_of_transport = l_mode
2904: -- and mt.continuous_move_id is null --check
2905: and mt.trip_id in
2906: (select distinct mts.trip_id
2907: from mst_trip_stops mts
2908: , mst_delivery_legs mdl
2909: , mst_deliveries md
2910: , wsh_locations wl
2911: where md.plan_id = mt.plan_id
2926: from mst_deliveries md
2927: , mst_delivery_legs mdl
2928: , mst_trips mt
2929: , wsh_locations wl
2930: , mst_trip_stops mts
2931: where md.plan_id = l_plan_id
2932: and md.plan_id = mt.plan_id
2933: and md.plan_id = mdl.plan_id
2934: and md.plan_id = mts.plan_id
3021: and mdd.delivery_detail_id = mda.delivery_detail_id
3022: and mda.delivery_id in (select mdl.delivery_id
3023: from mst_deliveries md
3024: , mst_delivery_legs mdl
3025: , mst_trip_stops mts
3026: , fte_location_parameters flp
3027: , wsh_locations wl
3028: where mdl.plan_id = l_plan_id
3029: and mdl.plan_id = mts.plan_id
3068: and md.delivery_id = mda.delivery_id
3069: and md.delivery_id in
3070: (select mdl.delivery_id
3071: from mst_trips t
3072: , mst_trip_stops ts
3073: , mst_delivery_legs mdl
3074: , wsh_locations wl
3075: where mdl.plan_id = md.plan_id
3076: and ts.plan_id = mdl.plan_id
3148: where md.plan_id = l_plan_id
3149: and md.delivery_id in (select mdl.delivery_id
3150: from mst_deliveries md
3151: , mst_delivery_legs mdl
3152: , mst_trip_stops mts
3153: , fte_location_parameters flp
3154: , wsh_locations wl
3155: where mdl.plan_id = l_plan_id
3156: and mdl.plan_id = mts.plan_id
3254: where md.plan_id = l_plan_id
3255: and md.delivery_id in (select mdl.delivery_id
3256: from mst_deliveries md
3257: , mst_delivery_legs mdl
3258: , mst_trip_stops mts
3259: , fte_location_parameters flp
3260: , wsh_locations wl
3261: where mdl.plan_id = l_plan_id
3262: and mdl.plan_id = mts.plan_id
3360: where md.plan_id = l_plan_id
3361: and md.delivery_id in (select mdl.delivery_id
3362: from mst_deliveries md
3363: , mst_delivery_legs mdl
3364: , mst_trip_stops mts
3365: , fte_location_parameters flp
3366: , wsh_locations wl
3367: where mdl.plan_id = l_plan_id
3368: and mdl.plan_id = mts.plan_id
3441: , l_destination_state in varchar2) is
3442: select count(*)
3443: from (select distinct mt.trip_id, count(*) num_stops
3444: from mst_trips mt
3445: , mst_trip_stops mts
3446: where mt.plan_id = l_plan_id
3447: and mt.trip_id in (select distinct mdl.trip_id
3448: from mst_deliveries md
3449: , mst_delivery_legs mdl
3446: where mt.plan_id = l_plan_id
3447: and mt.trip_id in (select distinct mdl.trip_id
3448: from mst_deliveries md
3449: , mst_delivery_legs mdl
3450: , mst_trip_stops mts1
3451: , wsh_locations wl
3452: where md.plan_id = mt.plan_id
3453: and mdl.plan_id = md.plan_id
3454: and mdl.delivery_id = md.delivery_id
3469: , l_destination_state in varchar2) is
3470: select count(*)
3471: from (select distinct mt.trip_id, count(*) num_stops
3472: from mst_trips mt
3473: , mst_trip_stops mts
3474: where mt.plan_id = l_plan_id
3475: and mt.trip_id in (select distinct mdl.trip_id
3476: from mst_deliveries md
3477: , mst_delivery_legs mdl
3474: where mt.plan_id = l_plan_id
3475: and mt.trip_id in (select distinct mdl.trip_id
3476: from mst_deliveries md
3477: , mst_delivery_legs mdl
3478: , mst_trip_stops mts1
3479: , fte_location_parameters flp
3480: , wsh_locations wl
3481: where md.plan_id = mt.plan_id
3482: and mdl.plan_id = md.plan_id
3501: , l_destination_state in varchar2) is
3502: select count(*)
3503: from (select distinct mt.trip_id, count(*) num_stops
3504: from mst_trips mt
3505: , mst_trip_stops mts
3506: where mt.plan_id = l_plan_id
3507: and mt.mode_of_transport = 'TRUCK'
3508: and mts.plan_id = mt.plan_id
3509: and mts.trip_id = mt.trip_id
3510: and mt.trip_id in
3511: (select distinct mdl.trip_id
3512: from mst_deliveries md
3513: , mst_delivery_legs mdl
3514: , mst_trip_stops mts1
3515: , wsh_locations wl
3516: where mdl.plan_id = mt.plan_id
3517: and mdl.drop_off_stop_id = mts1.stop_id
3518: and mdl.trip_id = mts1.trip_id
3531: , l_destination_state in varchar2) is
3532: select count(*)
3533: from (select distinct mt.trip_id, count(*) num_stops
3534: from mst_trips mt
3535: , mst_trip_stops mts
3536: where mt.plan_id = l_plan_id
3537: and mt.carrier_id = l_carrier_id
3538: and mt.trip_id in (select distinct mdl.trip_id
3539: from mst_deliveries md
3537: and mt.carrier_id = l_carrier_id
3538: and mt.trip_id in (select distinct mdl.trip_id
3539: from mst_deliveries md
3540: , mst_delivery_legs mdl
3541: , mst_trip_stops mts1
3542: , wsh_locations wl
3543: where md.plan_id = mt.plan_id
3544: and mdl.plan_id = md.plan_id
3545: and mdl.delivery_id = md.delivery_id
3591: , l_destination_state in varchar2) is
3592: select count(*)
3593: from (select distinct mt.trip_id, count(*) num_stops
3594: from mst_trips mt
3595: , mst_trip_stops mts
3596: where mt.plan_id = l_plan_id
3597: and mt.trip_id in (select distinct mdl.trip_id
3598: from mst_deliveries md
3599: , mst_delivery_legs mdl
3596: where mt.plan_id = l_plan_id
3597: and mt.trip_id in (select distinct mdl.trip_id
3598: from mst_deliveries md
3599: , mst_delivery_legs mdl
3600: , mst_trip_stops mts1
3601: , wsh_locations wl
3602: where md.plan_id = mt.plan_id
3603: and mdl.plan_id = md.plan_id
3604: and mdl.delivery_id = md.delivery_id
3619: , l_destination_state in varchar2) is
3620: select count(*)
3621: from (select distinct mt.trip_id, count(*) num_stops
3622: from mst_trips mt
3623: , mst_trip_stops mts
3624: where mt.plan_id = l_plan_id
3625: and mt.trip_id in (select distinct mdl.trip_id
3626: from mst_deliveries md
3627: , mst_delivery_legs mdl
3624: where mt.plan_id = l_plan_id
3625: and mt.trip_id in (select distinct mdl.trip_id
3626: from mst_deliveries md
3627: , mst_delivery_legs mdl
3628: , mst_trip_stops mts1
3629: , fte_location_parameters flp
3630: , wsh_locations wl
3631: where md.plan_id = mt.plan_id
3632: and mdl.plan_id = md.plan_id
3651: , l_destination_state in varchar2) is
3652: select count(*)
3653: from (select distinct mt.trip_id, count(*) num_stops
3654: from mst_trips mt
3655: , mst_trip_stops mts
3656: where mt.plan_id = l_plan_id
3657: and mt.mode_of_transport = 'TRUCK'
3658: and mts.plan_id = mt.plan_id
3659: and mts.trip_id = mt.trip_id
3660: and mt.trip_id in
3661: (select distinct mdl.trip_id
3662: from mst_deliveries md
3663: , mst_delivery_legs mdl
3664: , mst_trip_stops mts1
3665: , wsh_locations wl
3666: where mdl.plan_id = mt.plan_id
3667: and mdl.drop_off_stop_id = mts1.stop_id
3668: and mdl.trip_id = mts1.trip_id
3681: , l_destination_state in varchar2) is
3682: select count(*)
3683: from (select distinct mt.trip_id, count(*) num_stops
3684: from mst_trips mt
3685: , mst_trip_stops mts
3686: where mt.plan_id = l_plan_id
3687: and mt.carrier_id = l_carrier_id
3688: and mt.trip_id in (select distinct mdl.trip_id
3689: from mst_deliveries md
3687: and mt.carrier_id = l_carrier_id
3688: and mt.trip_id in (select distinct mdl.trip_id
3689: from mst_deliveries md
3690: , mst_delivery_legs mdl
3691: , mst_trip_stops mts1
3692: , wsh_locations wl
3693: where md.plan_id = mt.plan_id
3694: and mdl.plan_id = md.plan_id
3695: and mdl.delivery_id = md.delivery_id
3746: and mt.mode_of_transport = 'LTL'
3747: and mt.trip_id in (select distinct mdl.trip_id
3748: from mst_deliveries md
3749: , mst_delivery_legs mdl
3750: , mst_trip_stops mts1
3751: , wsh_locations wl
3752: where md.plan_id = mt.plan_id
3753: and mdl.plan_id = md.plan_id
3754: and mdl.delivery_id = md.delivery_id
3768: and mt.mode_of_transport = 'LTL'
3769: and mt.trip_id in (select distinct mdl.trip_id
3770: from mst_deliveries md
3771: , mst_delivery_legs mdl
3772: , mst_trip_stops mts1
3773: , fte_location_parameters flp
3774: , wsh_locations wl
3775: where md.plan_id = mt.plan_id
3776: and mdl.plan_id = md.plan_id
3795: and mt.trip_id in
3796: (select distinct mdl.trip_id
3797: from mst_deliveries md
3798: , mst_delivery_legs mdl
3799: , mst_trip_stops mts1
3800: , wsh_locations wl
3801: where mdl.plan_id = mt.plan_id
3802: and mdl.drop_off_stop_id = mts1.stop_id
3803: and mdl.trip_id = mts1.trip_id
3819: and mt.carrier_id = l_carrier_id
3820: and mt.trip_id in (select distinct mdl.trip_id
3821: from mst_deliveries md
3822: , mst_delivery_legs mdl
3823: , mst_trip_stops mts1
3824: , wsh_locations wl
3825: where md.plan_id = mt.plan_id
3826: and mdl.plan_id = md.plan_id
3827: and mdl.delivery_id = md.delivery_id
3872: and mt.mode_of_transport = 'PARCEL'
3873: and mt.trip_id in (select distinct mdl.trip_id
3874: from mst_deliveries md
3875: , mst_delivery_legs mdl
3876: , mst_trip_stops mts1
3877: , wsh_locations wl
3878: where md.plan_id = mt.plan_id
3879: and mdl.plan_id = md.plan_id
3880: and mdl.delivery_id = md.delivery_id
3894: and mt.mode_of_transport = 'PARCEL'
3895: and mt.trip_id in (select distinct mdl.trip_id
3896: from mst_deliveries md
3897: , mst_delivery_legs mdl
3898: , mst_trip_stops mts1
3899: , fte_location_parameters flp
3900: , wsh_locations wl
3901: where md.plan_id = mt.plan_id
3902: and mdl.plan_id = md.plan_id
3921: and mt.trip_id in
3922: (select distinct mdl.trip_id
3923: from mst_deliveries md
3924: , mst_delivery_legs mdl
3925: , mst_trip_stops mts1
3926: , wsh_locations wl
3927: where mdl.plan_id = mt.plan_id
3928: and mdl.drop_off_stop_id = mts1.stop_id
3929: and mdl.trip_id = mts1.trip_id
3945: and mt.carrier_id = l_carrier_id
3946: and mt.trip_id in (select distinct mdl.trip_id
3947: from mst_deliveries md
3948: , mst_delivery_legs mdl
3949: , mst_trip_stops mts1
3950: , wsh_locations wl
3951: where md.plan_id = mt.plan_id
3952: and mdl.plan_id = md.plan_id
3953: and mdl.delivery_id = md.delivery_id
4006: ( select md.delivery_id
4007: from mst_delivery_legs mdl1
4008: , mst_deliveries md
4009: , mst_trips mt
4010: , mst_trip_stops mts
4011: , wsh_locations wl
4012: where mt.plan_id = mdl1.plan_id
4013: and mt.trip_id = mdl1.trip_id
4014: and mt.mode_of_transport = l_mode
4029: + nvl(mdl.allocated_fac_rec_hand_cost,0)),0)
4030: from mst_deliveries md
4031: , mst_delivery_legs mdl
4032: , mst_trips mt
4033: -- , mst_trip_stops mts
4034: , wsh_locations wl
4035: where mt.plan_id = l_plan_id
4036: and mt.mode_of_transport = l_mode
4037: and mt.plan_id = mdl.plan_id
4064: ( select md.delivery_id
4065: from mst_delivery_legs mdl1
4066: , mst_deliveries md
4067: , mst_trips mt
4068: , mst_trip_stops mts
4069: , fte_location_parameters flp
4070: , wsh_locations wl
4071: where mt.plan_id = mdl1.plan_id
4072: and mt.trip_id = mdl1.trip_id
4091: + nvl(mdl.allocated_fac_rec_hand_cost,0)),0)
4092: from mst_deliveries md
4093: , mst_delivery_legs mdl
4094: , mst_trips mt
4095: -- , mst_trip_stops mts
4096: , wsh_locations wl
4097: , fte_location_parameters flp
4098: where mt.plan_id = l_plan_id
4099: and mt.mode_of_transport = l_mode
4124: and mt.trip_id = mdl.trip_id
4125: and mdl.delivery_id in (select mdl.delivery_id
4126: from mst_deliveries md
4127: , mst_delivery_legs mdl
4128: , mst_trip_stops mts
4129: , fte_location_parameters flp
4130: , wsh_locations wl
4131: where mdl.plan_id = l_plan_id
4132: and mdl.plan_id = mts.plan_id
4155: where mdl.plan_id = l_plan_id
4156: and mdl.delivery_id in
4157: ( select md.delivery_id
4158: from mst_trips mt
4159: , mst_trip_stops mts
4160: , mst_delivery_legs mdl1
4161: , mst_deliveries md
4162: , wsh_locations wl
4163: where mt.plan_id = mdl1.plan_id
4181: + nvl(mdl.allocated_fac_rec_hand_cost,0)),0)
4182: from mst_deliveries md
4183: , mst_delivery_legs mdl
4184: , mst_trips mt
4185: -- , mst_trip_stops mts
4186: , wsh_locations wl
4187: where mt.plan_id = l_plan_id
4188: and mt.mode_of_transport = l_mode
4189: and mt.plan_id = mdl.plan_id
4219: and mt.mode_of_transport = l_mode
4220: -- and mt.continuous_move_id is null --check
4221: and mt.trip_id in
4222: (select distinct mts.trip_id
4223: from mst_trip_stops mts
4224: , mst_delivery_legs mdl
4225: , mst_deliveries md
4226: , wsh_locations wl
4227: where md.plan_id = mt.plan_id
4242: from mst_deliveries md
4243: , mst_delivery_legs mdl
4244: , mst_trips mt
4245: , wsh_locations wl
4246: , mst_trip_stops mts
4247: where md.plan_id = l_plan_id
4248: and md.plan_id = mt.plan_id
4249: and md.plan_id = mdl.plan_id
4250: and md.plan_id = mts.plan_id
4301: and md.delivery_id = mda.delivery_id
4302: and md.delivery_id in
4303: (select mdl.delivery_id
4304: from -- mst_trips t -- Removing the join with mst_trips
4305: mst_trip_stops ts
4306: , mst_delivery_legs mdl
4307: , fte_location_parameters flp
4308: where mdl.plan_id = md.plan_id
4309: and ts.plan_id = mdl.plan_id
4341: and decode(l_c_s_ident, 2, md.customer_id, md.supplier_id) = l_cust_supp_id
4342: and md.delivery_id in
4343: (select mdl.delivery_id
4344: from mst_delivery_legs mdl
4345: , mst_trip_stops mts
4346: , fte_location_parameters flp
4347: where mdl.plan_id = md.plan_id
4348: and mdl.plan_id = mts.plan_id
4349: and ( mdl.pick_up_stop_id = mts.stop_id
4376: and md.delivery_id = mda.delivery_id
4377: and md.delivery_id in
4378: (select mdl.delivery_id
4379: from mst_trips t
4380: , mst_trip_stops ts
4381: , mst_delivery_legs mdl
4382: , fte_location_parameters flp
4383: where mdl.plan_id = md.plan_id
4384: and ts.plan_id = mdl.plan_id
4647: , fte_location_parameters flp
4648: where mt.plan_id = l_plan_id
4649: and mt.mode_of_transport = l_mode
4650: and flp.location_id in (select mts.stop_location_id
4651: from mst_trip_stops mts
4652: where mts.plan_id = mt.plan_id
4653: and mts.trip_id = mt.trip_id)
4654: and flp.facility_id = l_myfac_id;
4655:
4662: from mst_trips mt
4663: where mt.plan_id = l_plan_id
4664: and mt.trip_id in
4665: (select distinct mts.trip_id
4666: from mst_trip_stops mts
4667: , mst_delivery_legs mdl
4668: , mst_deliveries md
4669: , fte_location_parameters flp
4670: where md.plan_id = mt.plan_id
4679: and mdl.drop_off_stop_id = mts.stop_id)
4680: and mt.mode_of_transport = l_mode
4681: and EXISTS
4682: (select ts.trip_id
4683: from mst_trip_stops ts
4684: where ts.plan_id = mt.plan_id
4685: and ts.trip_id = mt.trip_id
4686: having count(ts.stop_id) >= 2
4687: group by ts.trip_id);
4696: where mt.plan_id = l_plan_id
4697: and mt.mode_of_transport = l_mode
4698: and mt.carrier_id = l_carrier_id
4699: and flp.location_id in (select mts.stop_location_id
4700: from mst_trip_stops mts
4701: where mts.plan_id = mt.plan_id
4702: and mts.trip_id = mt.trip_id)
4703: and flp.facility_id = l_myfac_id;
4704: begin
4749: ( select md.delivery_id
4750: from mst_delivery_legs mdl1
4751: , mst_deliveries md
4752: , mst_trips mt
4753: , mst_trip_stops mts
4754: , fte_location_parameters flp
4755: where mt.plan_id = mdl1.plan_id
4756: and mt.trip_id = mdl1.trip_id
4757: and mt.mode_of_transport = l_mode
4802: ( select md.delivery_id
4803: from mst_delivery_legs mdl1
4804: , mst_deliveries md
4805: , mst_trips mt
4806: , mst_trip_stops mts
4807: , fte_location_parameters flp
4808: where mt.plan_id = mdl1.plan_id
4809: and mt.trip_id = mdl1.trip_id
4810: and mt.mode_of_transport = l_mode
4859: and mt.mode_of_transport = l_mode
4860: -- and mt.continuous_move_id is null --check
4861: and mt.trip_id in
4862: (select distinct mts.trip_id
4863: from mst_trip_stops mts
4864: , mst_delivery_legs mdl
4865: , mst_deliveries md
4866: , fte_location_parameters flp
4867: where md.plan_id = mt.plan_id
4888: ( select md.delivery_id
4889: from mst_delivery_legs mdl1,
4890: mst_deliveries md,
4891: mst_trips mt,
4892: mst_trip_stops mts
4893: where mt.plan_id = mdl1.plan_id
4894: and mt.trip_id = mdl1.trip_id
4895: and mt.mode_of_transport = l_mode
4896: and mt.plan_id = mts.plan_id
4911: + nvl(mdl.allocated_fac_rec_hand_cost,0)),0)
4912: from mst_deliveries md
4913: , mst_delivery_legs mdl
4914: , mst_trips mt
4915: , mst_trip_stops mts
4916: where mt.plan_id = l_plan_id
4917: and mt.mode_of_transport = l_mode
4918: and mt.plan_id = mdl.plan_id
4919: and mt.trip_id = mdl.trip_id
4941: ( select md.delivery_id
4942: from mst_delivery_legs mdl1,
4943: mst_deliveries md,
4944: mst_trips mt,
4945: mst_trip_stops mts
4946: where mt.plan_id = mdl1.plan_id
4947: and mt.trip_id = mdl1.trip_id
4948: and mt.mode_of_transport = l_mode
4949: and mt.plan_id = mts.plan_id
4966: + nvl(mdl.allocated_fac_rec_hand_cost,0)),0)
4967: from mst_deliveries md
4968: , mst_delivery_legs mdl
4969: , mst_trips mt
4970: , mst_trip_stops mts
4971: where mt.plan_id = l_plan_id
4972: and mt.mode_of_transport = l_mode
4973: and mt.plan_id = mdl.plan_id
4974: and mt.trip_id = mdl.trip_id
5036: and decode(l_c_s_ident, 2, md.customer_id, md.supplier_id) = l_cust_supp_id
5037: and md.delivery_id in
5038: (select mdl.delivery_id
5039: from -- mst_trips t -- removing this join
5040: mst_trip_stops ts
5041: , mst_delivery_legs mdl
5042: , fte_location_parameters flp
5043: where mdl.plan_id = md.plan_id
5044: and ts.plan_id = mdl.plan_id
5078: 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 )
5079: and md.delivery_id in
5080: (select mdl.delivery_id
5081: from mst_delivery_legs mdl
5082: , mst_trip_stops mts
5083: where mdl.plan_id = md.plan_id
5084: and mdl.plan_id = mts.plan_id
5085: and ( mdl.pick_up_stop_id = mts.stop_id
5086: or mdl.drop_off_stop_id = mts.stop_id))
5118: and decode(l_c_s_ident, 2, md.customer_id, md.supplier_id) = l_cust_supp_id
5119: and md.delivery_id in
5120: (select mdl.delivery_id
5121: from mst_delivery_legs mdl
5122: , mst_trip_stops mts
5123: , mst_trips mt
5124: where mdl.plan_id = md.plan_id
5125: and mdl.plan_id = mts.plan_id
5126: and ( mdl.pick_up_stop_id = mts.stop_id
5386: , fte_location_parameters flp
5387: where mt.plan_id = l_plan_id
5388: and mt.mode_of_transport = l_mode
5389: and flp.location_id in (select distinct mts.stop_location_id
5390: from mst_trip_stops mts
5391: , mst_delivery_legs mdl
5392: , mst_deliveries md
5393: where mts.plan_id = mt.plan_id
5394: and mts.trip_id = mt.trip_id
5409: from mst_trips mt
5410: where mt.plan_id = l_plan_id
5411: and mt.trip_id in
5412: (select distinct mts.trip_id
5413: from mst_trip_stops mts
5414: , mst_delivery_legs mdl
5415: , mst_deliveries md
5416: where md.plan_id = mt.plan_id
5417: 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 )
5424: and mdl.drop_off_stop_id = mts.stop_id)
5425: and mt.mode_of_transport = l_mode
5426: and EXISTS
5427: (select ts.trip_id
5428: from mst_trip_stops ts
5429: where ts.plan_id = mt.plan_id
5430: and ts.trip_id = mt.trip_id
5431: having count(ts.stop_id) >= 2
5432: group by ts.trip_id);
5441: where mt.plan_id = l_plan_id
5442: and mt.carrier_id = l_carrier_id
5443: and mt.trip_id in
5444: (select distinct mts.trip_id
5445: from mst_trip_stops mts
5446: , mst_delivery_legs mdl
5447: , mst_deliveries md
5448: where md.plan_id = mt.plan_id
5449: and decode(l_c_s_ident, 2, md.customer_id, md.supplier_id) = l_cust_supp_id
5455: and mdl.drop_off_stop_id = mts.stop_id)
5456: and mt.mode_of_transport = l_mode
5457: and EXISTS
5458: (select ts.trip_id
5459: from mst_trip_stops ts
5460: where ts.plan_id = mt.plan_id
5461: and ts.trip_id = mt.trip_id
5462: having count(ts.stop_id) >= 2
5463: group by ts.trip_id);
5510: ( select md.delivery_id
5511: from mst_delivery_legs mdl1
5512: , mst_deliveries md
5513: , mst_trips mt
5514: , mst_trip_stops mts
5515: , fte_location_parameters flp
5516: where mt.plan_id = mdl1.plan_id
5517: and mt.trip_id = mdl1.trip_id
5518: and mt.mode_of_transport = l_mode
5566: ( select md.delivery_id
5567: from mst_delivery_legs mdl1
5568: , mst_deliveries md
5569: , mst_trips mt
5570: , mst_trip_stops mts
5571: where mt.plan_id = mdl1.plan_id
5572: and mt.trip_id = mdl1.trip_id
5573: and mt.mode_of_transport = l_mode
5574: and mt.plan_id = mts.plan_id
5619: and mt.mode_of_transport = l_mode
5620: -- and mt.continuous_move_id is null --check
5621: and mt.trip_id in
5622: (select distinct mts.trip_id
5623: from mst_trip_stops mts
5624: , mst_delivery_legs mdl
5625: , mst_deliveries md
5626: where md.plan_id = mt.plan_id
5627: and mts.plan_id = md.plan_id
5689: WHERE mt.plan_id = l_plan_id
5690: AND mt.trip_id IN ( SELECT mdl.trip_id
5691: FROM mst_deliveries md
5692: , mst_delivery_legs mdl
5693: , mst_trip_stops mts1
5694: , fte_location_parameters flp
5695: WHERE mdl.plan_id = mt.plan_id
5696: AND md.plan_id = mdl.plan_id
5697: AND md.delivery_id = mdl.delivery_id
5706: AND flp.facility_id = l_myfac_id )
5707: AND mt.mode_of_transport = 'TRUCK'
5708: AND EXISTS
5709: ( SELECT ts.trip_id
5710: FROM mst_trip_stops ts
5711: WHERE ts.plan_id = mt.plan_id
5712: AND ts.trip_id = mt.trip_id
5713: HAVING COUNT(ts.stop_id) = 2
5714: GROUP BY ts.trip_id );
5715: /*
5716: SELECT COUNT(*)
5717: FROM (SELECT DISTINCT mt.trip_id, count(*) num_stops
5718: FROM mst_trips mt
5719: , mst_trip_stops mts
5720: WHERE mt.plan_id = l_plan_id
5721: AND mt.trip_id in (SELECT DISTINCT mdl.trip_id
5722: FROM mst_deliveries md
5723: , mst_delivery_legs mdl
5720: WHERE mt.plan_id = l_plan_id
5721: AND mt.trip_id in (SELECT DISTINCT mdl.trip_id
5722: FROM mst_deliveries md
5723: , mst_delivery_legs mdl
5724: , mst_trip_stops mts1
5725: , fte_location_parameters flp
5726: WHERE mdl.plan_id = mt.plan_id
5727: AND md.plan_id = mdl.plan_id
5728: AND md.delivery_id = mdl.delivery_id
5751: from mst_trips mt
5752: where mt.plan_id = l_plan_id
5753: and mt.trip_id in
5754: (select distinct mts.trip_id
5755: from mst_trip_stops mts
5756: , mst_delivery_legs mdl
5757: , mst_deliveries md
5758: where md.plan_id = mt.plan_id
5759: 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 )
5766: and mdl.drop_off_stop_id = mts.stop_id)
5767: and mt.mode_of_transport = 'TRUCK'
5768: and EXISTS
5769: (select ts.trip_id
5770: from mst_trip_stops ts
5771: where ts.plan_id = mt.plan_id
5772: and ts.trip_id = mt.trip_id
5773: having count(ts.stop_id) = 2
5774: group by ts.trip_id);
5782: where mt.plan_id = l_plan_id
5783: and mt.carrier_id = l_carrier_id
5784: and mt.trip_id in
5785: (select distinct mts.trip_id
5786: from mst_trip_stops mts
5787: , mst_delivery_legs mdl
5788: , mst_deliveries md
5789: where md.plan_id = mt.plan_id
5790: and decode(l_c_s_ident, 2, md.customer_id, md.supplier_id) = l_cust_supp_id
5796: and mdl.drop_off_stop_id = mts.stop_id)
5797: and mt.mode_of_transport = 'TRUCK'
5798: and EXISTS
5799: (select ts.trip_id
5800: from mst_trip_stops ts
5801: where ts.plan_id = mt.plan_id
5802: and ts.trip_id = mt.trip_id
5803: having count(ts.stop_id) = 2
5804: group by ts.trip_id);
5843: WHERE mt.plan_id = l_plan_id
5844: AND mt.trip_id IN ( SELECT mdl.trip_id
5845: FROM mst_deliveries md
5846: , mst_delivery_legs mdl
5847: , mst_trip_stops mts1
5848: , fte_location_parameters flp
5849: WHERE mdl.plan_id = mt.plan_id
5850: AND md.plan_id = mdl.plan_id
5851: AND md.delivery_id = mdl.delivery_id
5860: AND flp.facility_id = l_myfac_id )
5861: AND mt.mode_of_transport = 'TRUCK'
5862: AND EXISTS
5863: ( SELECT ts.trip_id
5864: FROM mst_trip_stops ts
5865: WHERE ts.plan_id = mt.plan_id
5866: AND ts.trip_id = mt.trip_id
5867: HAVING COUNT(ts.stop_id) > 2
5868: GROUP BY ts.trip_id );
5869: /*
5870: select count(*)
5871: from (select distinct mt.trip_id, count(*) num_stops
5872: from mst_trips mt
5873: , mst_trip_stops mts
5874: where mt.plan_id = l_plan_id
5875: and mt.trip_id in (select distinct mdl.trip_id
5876: from mst_deliveries md
5877: , mst_delivery_legs mdl
5874: where mt.plan_id = l_plan_id
5875: and mt.trip_id in (select distinct mdl.trip_id
5876: from mst_deliveries md
5877: , mst_delivery_legs mdl
5878: , mst_trip_stops mts1
5879: , fte_location_parameters flp
5880: where mdl.plan_id = mt.plan_id
5881: and md.plan_id = mdl.plan_id
5882: and md.delivery_id = mdl.delivery_id
5905: from mst_trips mt
5906: where mt.plan_id = l_plan_id
5907: and mt.trip_id in
5908: (select distinct mts.trip_id
5909: from mst_trip_stops mts
5910: , mst_delivery_legs mdl
5911: , mst_deliveries md
5912: where md.plan_id = mt.plan_id
5913: 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 )
5920: and mdl.drop_off_stop_id = mts.stop_id)
5921: and mt.mode_of_transport = 'TRUCK'
5922: and EXISTS
5923: (select ts.trip_id
5924: from mst_trip_stops ts
5925: where ts.plan_id = mt.plan_id
5926: and ts.trip_id = mt.trip_id
5927: having count(ts.stop_id) > 2
5928: group by ts.trip_id);
5936: where mt.plan_id = l_plan_id
5937: and mt.carrier_id = l_carrier_id
5938: and mt.trip_id in
5939: (select distinct mts.trip_id
5940: from mst_trip_stops mts
5941: , mst_delivery_legs mdl
5942: , mst_deliveries md
5943: where md.plan_id = mt.plan_id
5944: and decode(l_c_s_ident, 2, md.customer_id, md.supplier_id) = l_cust_supp_id
5950: and mdl.drop_off_stop_id = mts.stop_id)
5951: and mt.mode_of_transport = 'TRUCK'
5952: and EXISTS
5953: (select ts.trip_id
5954: from mst_trip_stops ts
5955: where ts.plan_id = mt.plan_id
5956: and ts.trip_id = mt.trip_id
5957: having count(ts.stop_id) > 2
5958: group by ts.trip_id);
6111: , 0, 1
6112: , mst_reports_pkg.get_plan_value(p_plan_id,p_report_for,p_report_for_id)) ) * 100
6113: , 0
6114: from (select distinct mts.plan_id plan_id, mts.stop_location_id loc_id
6115: from mst_trip_stops mts
6116: , mst_trips mt
6117: where mts.plan_id = mt.plan_id
6118: and mts.trip_id = mt.trip_id) temp,
6119: mst_plans mp,
6503: SELECT mts2.planned_arrival_date planned_arrival_date,
6504: mts1.planned_departure_date planned_departure_date,
6505: mts1.drv_time_to_next_stop drv_time_to_next_stop,
6506: mts1.total_layover_duration total_layover_duration
6507: FROM mst_trip_stops mts1
6508: ,mst_trip_stops mts2
6509: WHERE mts1.plan_id = l_plan_id
6510: AND mts1.trip_id = l_trip_id
6511: AND mts1.stop_id = l_stop_id
6504: mts1.planned_departure_date planned_departure_date,
6505: mts1.drv_time_to_next_stop drv_time_to_next_stop,
6506: mts1.total_layover_duration total_layover_duration
6507: FROM mst_trip_stops mts1
6508: ,mst_trip_stops mts2
6509: WHERE mts1.plan_id = l_plan_id
6510: AND mts1.trip_id = l_trip_id
6511: AND mts1.stop_id = l_stop_id
6512: AND mts2.plan_id = mts1.plan_id
6511: AND mts1.stop_id = l_stop_id
6512: AND mts2.plan_id = mts1.plan_id
6513: AND mts2.trip_id = mts1.trip_id
6514: AND mts2.stop_sequence_number = ( SELECT MIN( mts3.stop_sequence_number )
6515: FROM mst_trip_stops mts3
6516: WHERE mts3.plan_id = mts1.plan_id
6517: AND mts3.trip_id = mts1.trip_id
6518: AND mts3.stop_sequence_number > mts1.stop_sequence_number );
6519: l_rec_wait_time cur_wait_time%ROWTYPE;