[Home] [Help]
1660: select count(distinct mdd.source_header_number)
1661: from mst_delivery_details mdd
1662: , mst_delivery_assignments mda
1663: , mst_deliveries md
1664: , wsh_locations wl
1665: where md.plan_id = mda.plan_id
1666: and md.delivery_id = mda.delivery_id
1667: and md.pickup_location_id = wl.wsh_location_id
1668: and wl.state = l_orig_state
1680: from mst_delivery_details mdd
1681: , mst_delivery_assignments mda
1682: , mst_deliveries md
1683: , fte_location_parameters flp
1684: , wsh_locations wl
1685: where md.plan_id = mda.plan_id
1686: and md.delivery_id = mda.delivery_id
1687: and md.pickup_location_id = wl.wsh_location_id
1688: and wl.state = l_orig_state
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
1711: and mdl.trip_id = mts.trip_id
1712: and ( mdl.pick_up_stop_id = mts.stop_id
1726: select count(distinct mdd.source_header_number)
1727: from mst_delivery_details mdd
1728: , mst_delivery_assignments mda
1729: , mst_deliveries md
1730: , wsh_locations wl
1731: where md.plan_id = mda.plan_id
1732: and md.delivery_id = mda.delivery_id
1733: and md.pickup_location_id = wl.wsh_location_id
1734: and wl.state = l_orig_state
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
1759: and ts.stop_id = mdl.pick_up_stop_id
1760: and wl.wsh_location_id = md.pickup_location_id
1804: cursor cur_weight_orig (l_plan_id in number
1805: , l_orig_state in varchar2) is
1806: select nvl(sum(nvl(md.gross_weight,0)),0)
1807: from mst_deliveries md
1808: , wsh_locations wl
1809: where md.plan_id = l_plan_id
1810: and md.pickup_location_id = wl.wsh_location_id
1811: and wl.state = l_orig_state;
1812:
1816: /*
1817: select nvl(sum(nvl(md.gross_weight,0)),0)
1818: from mst_deliveries md
1819: , fte_location_parameters flp
1820: , wsh_locations wl
1821: where md.plan_id = l_plan_id
1822: and md.pickup_location_id = wl.wsh_location_id
1823: and wl.state = l_orig_state
1824: and flp.location_id = md.pickup_location_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
1839: and mdl.trip_id = mts.trip_id
1840: and ( mdl.pick_up_stop_id = mts.stop_id
1851: , l_cust_supp_id in number
1852: , l_orig_state in varchar2) is
1853: select nvl(sum(nvl(md.gross_weight,0)),0)
1854: from mst_deliveries md
1855: , wsh_locations wl
1856: where md.plan_id = l_plan_id
1857: and md.pickup_location_id = wl.wsh_location_id
1858: and wl.state = l_orig_state
1859: and decode(l_c_s_ident, 2, md.customer_id, md.supplier_id) = l_cust_supp_id;
1862: , l_carrier_id in number
1863: , l_orig_state in varchar2) is
1864: select nvl(sum(nvl(md.gross_weight,0)),0)
1865: from mst_deliveries md
1866: , wsh_locations wl
1867: where md.plan_id = l_plan_id
1868: and md.pickup_location_id = wl.wsh_location_id
1869: and wl.state = l_orig_state
1870: and md.delivery_id in (select distinct mdl.delivery_id
1910: cursor cur_volume_orig (l_plan_id in number
1911: , l_orig_state in varchar2) is
1912: select nvl(sum(nvl(md.volume,0)),0)
1913: from mst_deliveries md
1914: , wsh_locations wl
1915: where md.plan_id = l_plan_id
1916: and md.pickup_location_id = wl.wsh_location_id
1917: and wl.state = l_orig_state;
1918:
1922: /*
1923: select nvl(sum(nvl(md.volume,0)),0)
1924: from mst_deliveries md
1925: , fte_location_parameters flp
1926: , wsh_locations wl
1927: where md.plan_id = l_plan_id
1928: and md.pickup_location_id = wl.wsh_location_id
1929: and wl.state = l_orig_state
1930: and flp.location_id = md.pickup_location_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
1945: and mdl.trip_id = mts.trip_id
1946: and ( mdl.pick_up_stop_id = mts.stop_id
1957: , l_cust_supp_id in number
1958: , l_orig_state in varchar2) is
1959: select nvl(sum(nvl(md.volume,0)),0)
1960: from mst_deliveries md
1961: , wsh_locations wl
1962: where md.plan_id = l_plan_id
1963: and md.pickup_location_id = wl.wsh_location_id
1964: and wl.state = l_orig_state
1965: and decode(l_c_s_ident, 2, md.customer_id, md.supplier_id) = l_cust_supp_id;
1968: , l_carrier_id in number
1969: , l_orig_state in varchar2) is
1970: select nvl(sum(nvl(md.volume,0)),0)
1971: from mst_deliveries md
1972: , wsh_locations wl
1973: where md.plan_id = l_plan_id
1974: and md.pickup_location_id = wl.wsh_location_id
1975: and wl.state = l_orig_state
1976: and md.delivery_id in (select distinct mdl.delivery_id
2016: cursor cur_pieces_orig (l_plan_id in number
2017: , l_orig_state in varchar2) is
2018: select nvl(sum(nvl(md.number_of_pieces,0)),0)
2019: from mst_deliveries md
2020: , wsh_locations wl
2021: where md.plan_id = l_plan_id
2022: and md.pickup_location_id = wl.wsh_location_id
2023: and wl.state = l_orig_state;
2024:
2028: /*
2029: select nvl(sum(nvl(md.number_of_pieces,0)),0)
2030: from mst_deliveries md
2031: , fte_location_parameters flp
2032: , wsh_locations wl
2033: where md.plan_id = l_plan_id
2034: and md.pickup_location_id = wl.wsh_location_id
2035: and wl.state = l_orig_state
2036: and flp.location_id = md.pickup_location_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
2051: and mdl.trip_id = mts.trip_id
2052: and ( mdl.pick_up_stop_id = mts.stop_id
2063: , l_cust_supp_id in number
2064: , l_orig_state in varchar2) is
2065: select nvl(sum(nvl(md.number_of_pieces,0)),0)
2066: from mst_deliveries md
2067: , wsh_locations wl
2068: where md.plan_id = l_plan_id
2069: and md.pickup_location_id = wl.wsh_location_id
2070: and wl.state = l_orig_state
2071: and decode(l_c_s_ident, 2, md.customer_id, md.supplier_id) = l_cust_supp_id;
2074: , l_carrier_id in number
2075: , l_orig_state in varchar2) is
2076: select nvl(sum(nvl(md.number_of_pieces,0)),0)
2077: from mst_deliveries md
2078: , wsh_locations wl
2079: where md.plan_id = l_plan_id
2080: and md.pickup_location_id = wl.wsh_location_id
2081: and wl.state = l_orig_state
2082: and md.delivery_id in (select distinct mdl.delivery_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
2137: and mts1.plan_id = mdl.plan_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
2165: and mdl.delivery_id = md.delivery_id
2166: and mts1.plan_id = mdl.plan_id
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
2201: and mts1.plan_id = mt.plan_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
2228: and mts1.plan_id = mdl.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
2288: and mts1.plan_id = mdl.plan_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
2316: and mdl.delivery_id = md.delivery_id
2317: and mts1.plan_id = mdl.plan_id
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
2352: and mts1.plan_id = mt.plan_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
2379: and mts1.plan_id = mdl.plan_id
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
2438: and mts1.plan_id = mdl.plan_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
2460: and mdl.delivery_id = md.delivery_id
2461: and mts1.plan_id = mdl.plan_id
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
2487: and mts1.plan_id = mt.plan_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
2511: and mts1.plan_id = mdl.plan_id
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
2564: and mts1.plan_id = mdl.plan_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
2586: and mdl.delivery_id = md.delivery_id
2587: and mts1.plan_id = mdl.plan_id
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
2613: and mts1.plan_id = mt.plan_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
2637: and mts1.plan_id = mdl.plan_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
2698: and mt.plan_id = mts.plan_id
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
2721: and mt.trip_id = mdl.trip_id
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
2757: and mt.mode_of_transport = l_mode
2758: and mt.plan_id = mts.plan_id
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
2784: and mt.plan_id = mdl.plan_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
2817: and mdl.trip_id = mts.trip_id
2818: and ( mdl.pick_up_stop_id = mts.stop_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
2848: and mt.trip_id = mdl1.trip_id
2849: and mt.mode_of_transport = l_mode
2850: and mt.plan_id = mts.plan_id
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
2874: and mt.trip_id = mdl.trip_id
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
2912: and mts.plan_id = md.plan_id
2913: and mdl.plan_id = md.plan_id
2914: and mdl.delivery_id = md.delivery_id
2925: + nvl(mdl.allocated_transport_cost,0)), 0 )
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
2979: select count(distinct mdd.source_header_number)
2980: from mst_delivery_details mdd
2981: , mst_delivery_assignments mda
2982: , mst_deliveries md
2983: , wsh_locations wl
2984: where md.plan_id = mda.plan_id
2985: and md.delivery_id = mda.delivery_id
2986: and md.dropoff_location_id = wl.wsh_location_id
2987: and wl.state = l_destination_state
2999: from mst_delivery_details mdd
3000: , mst_delivery_assignments mda
3001: , mst_deliveries md
3002: , fte_location_parameters flp
3003: , wsh_locations wl
3004: where md.plan_id = mda.plan_id
3005: and md.delivery_id = mda.delivery_id
3006: and md.dropoff_location_id = wl.wsh_location_id
3007: and wl.state = l_destination_state
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
3030: and mdl.trip_id = mts.trip_id
3031: and ( mdl.pick_up_stop_id = mts.stop_id
3045: select count(distinct mdd.source_header_number)
3046: from mst_delivery_details mdd
3047: , mst_delivery_assignments mda
3048: , mst_deliveries md
3049: , wsh_locations wl
3050: where md.plan_id = mda.plan_id
3051: and md.delivery_id = mda.delivery_id
3052: and md.dropoff_location_id = wl.wsh_location_id
3053: and wl.state = l_destination_state
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
3077: and ts.stop_id = mdl.drop_off_stop_id
3078: and wl.wsh_location_id = md.dropoff_location_id
3122: cursor cur_weight_dest (l_plan_id in number
3123: , l_destination_state in varchar2) is
3124: select nvl(sum(nvl(md.gross_weight,0)),0)
3125: from mst_deliveries md
3126: , wsh_locations wl
3127: where md.plan_id = l_plan_id
3128: and md.dropoff_location_id = wl.wsh_location_id
3129: and wl.state = l_destination_state;
3130:
3134: /*
3135: select nvl(sum(nvl(md.gross_weight,0)),0)
3136: from mst_deliveries md
3137: , fte_location_parameters flp
3138: , wsh_locations wl
3139: where md.plan_id = l_plan_id
3140: and md.dropoff_location_id = wl.wsh_location_id
3141: and wl.state = l_destination_state
3142: and flp.location_id = md.dropoff_location_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
3157: and mdl.trip_id = mts.trip_id
3158: and ( mdl.pick_up_stop_id = mts.stop_id
3169: , l_cust_supp_id in number
3170: , l_destination_state in varchar2) is
3171: select nvl(sum(nvl(md.gross_weight,0)),0)
3172: from mst_deliveries md
3173: , wsh_locations wl
3174: where md.plan_id = l_plan_id
3175: and md.dropoff_location_id = wl.wsh_location_id
3176: and wl.state = l_destination_state
3177: and decode(l_c_s_ident, 2, md.customer_id, md.supplier_id) = l_cust_supp_id;
3180: , l_carrier_id in number
3181: , l_destination_state in varchar2) is
3182: select nvl(sum(nvl(md.gross_weight,0)),0)
3183: from mst_deliveries md
3184: , wsh_locations wl
3185: where md.plan_id = l_plan_id
3186: and md.dropoff_location_id = wl.wsh_location_id
3187: and wl.state = l_destination_state
3188: and md.delivery_id in (select distinct mdl.delivery_id
3228: cursor cur_volume_dest (l_plan_id in number
3229: , l_destination_state in varchar2) is
3230: select nvl(sum(nvl(md.volume,0)),0)
3231: from mst_deliveries md
3232: , wsh_locations wl
3233: where md.plan_id = l_plan_id
3234: and md.dropoff_location_id = wl.wsh_location_id
3235: and wl.state = l_destination_state;
3236:
3240: /*
3241: select nvl(sum(nvl(md.volume,0)),0)
3242: from mst_deliveries md
3243: , fte_location_parameters flp
3244: , wsh_locations wl
3245: where md.plan_id = l_plan_id
3246: and md.dropoff_location_id = wl.wsh_location_id
3247: and wl.state = l_destination_state
3248: and flp.location_id = md.dropoff_location_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
3263: and mdl.trip_id = mts.trip_id
3264: and ( mdl.pick_up_stop_id = mts.stop_id
3275: , l_cust_supp_id in number
3276: , l_destination_state in varchar2) is
3277: select nvl(sum(nvl(md.volume,0)),0)
3278: from mst_deliveries md
3279: , wsh_locations wl
3280: where md.plan_id = l_plan_id
3281: and md.dropoff_location_id = wl.wsh_location_id
3282: and wl.state = l_destination_state
3283: and decode(l_c_s_ident, 2, md.customer_id, md.supplier_id) = l_cust_supp_id;
3286: , l_carrier_id in number
3287: , l_destination_state in varchar2) is
3288: select nvl(sum(nvl(md.volume,0)),0)
3289: from mst_deliveries md
3290: , wsh_locations wl
3291: where md.plan_id = l_plan_id
3292: and md.dropoff_location_id = wl.wsh_location_id
3293: and wl.state = l_destination_state
3294: and md.delivery_id in (select distinct mdl.delivery_id
3334: cursor cur_pieces_dest (l_plan_id in number
3335: , l_destination_state in varchar2) is
3336: select nvl(sum(nvl(md.number_of_pieces,0)),0)
3337: from mst_deliveries md
3338: , wsh_locations wl
3339: where md.plan_id = l_plan_id
3340: and md.dropoff_location_id = wl.wsh_location_id
3341: and wl.state = l_destination_state;
3342:
3346: /*
3347: select nvl(sum(nvl(md.number_of_pieces,0)),0)
3348: from mst_deliveries md
3349: , fte_location_parameters flp
3350: , wsh_locations wl
3351: where md.plan_id = l_plan_id
3352: and md.dropoff_location_id = wl.wsh_location_id
3353: and wl.state = l_destination_state
3354: and flp.location_id = md.dropoff_location_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
3369: and mdl.trip_id = mts.trip_id
3370: and ( mdl.pick_up_stop_id = mts.stop_id
3381: , l_cust_supp_id in number
3382: , l_destination_state in varchar2) is
3383: select nvl(sum(nvl(md.number_of_pieces,0)),0)
3384: from mst_deliveries md
3385: , wsh_locations wl
3386: where md.plan_id = l_plan_id
3387: and md.dropoff_location_id = wl.wsh_location_id
3388: and wl.state = l_destination_state
3389: and decode(l_c_s_ident, 2, md.customer_id, md.supplier_id) = l_cust_supp_id;
3392: , l_carrier_id in number
3393: , l_destination_state in varchar2) is
3394: select nvl(sum(nvl(md.number_of_pieces,0)),0)
3395: from mst_deliveries md
3396: , wsh_locations wl
3397: where md.plan_id = l_plan_id
3398: and md.dropoff_location_id = wl.wsh_location_id
3399: and wl.state = l_destination_state
3400: and md.delivery_id in (select distinct mdl.delivery_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
3455: and mts1.plan_id = mdl.plan_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
3483: and mdl.delivery_id = md.delivery_id
3484: and mts1.plan_id = mdl.plan_id
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
3519: and mts1.plan_id = mt.plan_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
3546: and mts1.plan_id = mdl.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
3605: and mts1.plan_id = mdl.plan_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
3633: and mdl.delivery_id = md.delivery_id
3634: and mts1.plan_id = mdl.plan_id
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
3669: and mts1.plan_id = mt.plan_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
3696: and mts1.plan_id = mdl.plan_id
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
3755: and mts1.plan_id = mdl.plan_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
3777: and mdl.delivery_id = md.delivery_id
3778: and mts1.plan_id = mdl.plan_id
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
3804: and mts1.plan_id = mt.plan_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
3828: and mts1.plan_id = mdl.plan_id
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
3881: and mts1.plan_id = mdl.plan_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
3903: and mdl.delivery_id = md.delivery_id
3904: and mts1.plan_id = mdl.plan_id
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
3930: and mts1.plan_id = mt.plan_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
3954: and mts1.plan_id = mdl.plan_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
4015: and mt.plan_id = mts.plan_id
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
4038: and mt.trip_id = mdl.trip_id
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
4073: and mt.mode_of_transport = l_mode
4074: and mt.plan_id = mts.plan_id
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
4100: and mt.plan_id = mdl.plan_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
4133: and mdl.trip_id = mts.trip_id
4134: and ( mdl.pick_up_stop_id = mts.stop_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
4164: and mt.trip_id = mdl1.trip_id
4165: and mt.mode_of_transport = l_mode
4166: and mt.plan_id = mts.plan_id
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
4190: and mt.trip_id = mdl.trip_id
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
4228: and mts.plan_id = md.plan_id
4229: and mdl.plan_id = md.plan_id
4230: and mdl.delivery_id = md.delivery_id
4241: + nvl(mdl.allocated_transport_cost,0)), 0 )
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
6117: where mts.plan_id = mt.plan_id
6118: and mts.trip_id = mt.trip_id) temp,
6119: mst_plans mp,
6120: wsh_location_owners wlo,
6121: wsh_locations wl,
6122: fte_location_parameters fte
6123: where mp.plan_id = temp.plan_id
6124: and temp.loc_id = fte.location_id
6125: and fte.location_id = wl.wsh_location_id
6218: , 0, 1
6219: , mst_reports_pkg.get_plan_value(p_plan_id,p_report_for,p_report_for_id) ) * 100
6220: , 0
6221: from mst_plans mp
6222: ,wsh_locations wl
6223: WHERE mp.plan_id = p_plan_id
6224: and wl.wsh_location_id in (select distinct md.pickup_location_id
6225: from mst_deliveries md
6226: where md.plan_id = mp.plan_id)
6286: , 0, 1
6287: , mst_reports_pkg.get_plan_value(p_plan_id,p_report_for,p_report_for_id) ) * 100
6288: , 0
6289: from mst_plans mp
6290: ,wsh_locations wl
6291: WHERE mp.plan_id = p_plan_id
6292: and wl.wsh_location_id in (select distinct md.dropoff_location_id
6293: from mst_deliveries md
6294: where md.plan_id = mp.plan_id)