[Home] [Help]
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
44: and ts.stop_id = mdl.pick_up_stop_id
45: and ts.stop_location_id = flp.location_id
154: cursor cur_order_groups_myfac (l_plan_id in number
155: , l_myfac_id in number) is
156: select count(*)
157: from mst_deliveries md
158: , fte_location_parameters flp
159: where md.plan_id = l_plan_id
160: and (md.pickup_location_id = flp.location_id
161: or md.dropoff_location_id = flp.location_id)
162: and flp.facility_id = l_myfac_id;
224: cursor cur_weight_myfac (l_plan_id in number
225: , l_myfac_id in number) is
226: select nvl(sum(nvl(md.gross_weight,0)),0)
227: from mst_deliveries md
228: , fte_location_parameters flp
229: where md.plan_id = l_plan_id
230: and (md.pickup_location_id = flp.location_id
231: or md.dropoff_location_id = flp.location_id)
232: and flp.facility_id = l_myfac_id;
294: cursor cur_volume_myfac (l_plan_id in number
295: , l_myfac_id in number) is
296: select nvl(sum(nvl(md.volume,0)),0)
297: from mst_deliveries md
298: , fte_location_parameters flp
299: where md.plan_id = l_plan_id
300: and (md.pickup_location_id = flp.location_id
301: or md.dropoff_location_id = flp.location_id)
302: and flp.facility_id = l_myfac_id;
364: cursor cur_pieces_myfac (l_plan_id in number
365: , l_myfac_id in number) is
366: select nvl(sum(nvl(md.number_of_pieces,0)),0)
367: from mst_deliveries md
368: , fte_location_parameters flp
369: where md.plan_id = l_plan_id
370: and (md.pickup_location_id = flp.location_id
371: or md.dropoff_location_id = flp.location_id)
372: and flp.facility_id = l_myfac_id;
441: select sum(nvl(mdd.unit_price,0)* nvl(mdd.requested_quantity,0))
442: from mst_delivery_details mdd
443: , mst_delivery_assignments mda
444: , mst_deliveries md
445: , fte_location_parameters flp
446: where mdd.plan_id = l_plan_id
447: and mda.plan_id = mdd.plan_id
448: and mda.delivery_detail_id = mdd.delivery_detail_id
449: and mda.parent_delivery_detail_id is null
531: , l_myfac_id in number
532: , l_mode in varchar2) is
533: select count(*)
534: from mst_trips mt
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
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
642: and mt.plan_id = mts.plan_id
655: + nvl(mdl.allocated_fac_unloading_cost,0)),0)
656: from mst_deliveries md
657: , mst_delivery_legs mdl
658: , mst_trips mt
659: , fte_location_parameters flp
660: where mt.plan_id = l_plan_id
661: and mt.mode_of_transport = l_mode
662: and mt.plan_id = mdl.plan_id
663: and mt.trip_id = mdl.trip_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
795: and mt.plan_id = mts.plan_id
807: + nvl(mdl.allocated_fac_rec_hand_cost,0)),0)
808: from mst_deliveries md
809: , mst_delivery_legs mdl
810: , mst_trips mt
811: , fte_location_parameters flp
812: where mt.plan_id = l_plan_id
813: and mt.mode_of_transport = l_mode
814: and mt.plan_id = mdl.plan_id
815: and mt.trip_id = mdl.trip_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
946: and mt.plan_id = mts.plan_id
961: + nvl(mdl.allocated_fac_rec_hand_cost,0)),0)
962: from mst_deliveries md
963: , mst_delivery_legs mdl
964: , mst_trips mt
965: , fte_location_parameters flp
966: where mt.plan_id = l_plan_id
967: and mt.mode_of_transport = l_mode
968: and mt.plan_id = mdl.plan_id
969: and mt.trip_id = mdl.trip_id
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
1095: and mt.mode_of_transport = 'TRUCK'
1096: and flp.location_id = mts.stop_location_id
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'
1112: and mts1.plan_id = mt.plan_id
1124: and mt.mode_of_transport = 'TRUCK'
1125: and mt.trip_id in ( select mdl.trip_id
1126: from mst_deliveries md
1127: , mst_delivery_legs mdl
1128: , fte_location_parameters flp
1129: where md.plan_id = l_plan_id
1130: and flp.facility_id = l_myfac_id
1131: and ( md.dropoff_location_id = flp.location_id
1132: or md.pickup_location_id = flp.location_id )
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);
1184: */
1189: and mt.mode_of_transport = 'TRUCK'
1190: and mt.trip_id in ( select mdl.trip_id
1191: from mst_deliveries md
1192: , mst_delivery_legs mdl
1193: , fte_location_parameters flp
1194: where md.plan_id = l_plan_id
1195: and flp.facility_id = l_myfac_id
1196: and ( md.dropoff_location_id = flp.location_id
1197: or md.pickup_location_id = flp.location_id )
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);
1337: */
1342: and mt.mode_of_transport = 'TRUCK'
1343: and mt.trip_id in ( select mdl.trip_id
1344: from mst_deliveries md
1345: , mst_delivery_legs mdl
1346: , fte_location_parameters flp
1347: where md.plan_id = l_plan_id
1348: and flp.facility_id = l_myfac_id
1349: and ( md.dropoff_location_id = flp.location_id
1350: or md.pickup_location_id = flp.location_id )
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
1455: and mts.trip_id = mt.trip_id
1458: */
1459: --Bug_Fix for 3696518 - II
1460: select count ( distinct mt.trip_id )
1461: from mst_deliveries md
1462: , fte_location_parameters flp
1463: , mst_delivery_legs mdl
1464: , mst_trips mt
1465: where md.plan_id = l_plan_id
1466: and flp.facility_id = l_myfac_id
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
1570: and mt.trip_id = mts.trip_id
1571: and mt.carrier_id = l_carrier_id
1579: */
1580: --Bug_Fix for 3696518 - II
1581: select sum ( nvl ( mdl.allocated_transport_cost, 0 ) )
1582: from mst_deliveries md
1583: , fte_location_parameters flp
1584: , mst_delivery_legs mdl
1585: , mst_trips mt
1586: where md.plan_id = l_plan_id
1587: and flp.facility_id = l_myfac_id
1679: select count(distinct mdd.source_header_number)
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
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
1711: and mdl.trip_id = mts.trip_id
1815: , l_orig_state in varchar2) is
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
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
1839: and mdl.trip_id = mts.trip_id
1921: , l_orig_state in varchar2) is
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
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
1945: and mdl.trip_id = mts.trip_id
2027: , l_orig_state in varchar2) is
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
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
2051: and mdl.trip_id = mts.trip_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
2165: and mdl.delivery_id = md.delivery_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
2316: and mdl.delivery_id = md.delivery_id
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
2460: and mdl.delivery_id = md.delivery_id
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
2586: and mdl.delivery_id = 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
2757: and mt.mode_of_transport = l_mode
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
2785: 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
2817: and mdl.trip_id = mts.trip_id
2998: select count(distinct mdd.source_header_number)
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
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
3030: and mdl.trip_id = mts.trip_id
3133: , l_destination_state in varchar2) is
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
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
3157: and mdl.trip_id = mts.trip_id
3239: , l_destination_state in varchar2) is
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
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
3263: and mdl.trip_id = mts.trip_id
3345: , l_destination_state in varchar2) is
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
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
3369: and mdl.trip_id = mts.trip_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
3483: and mdl.delivery_id = md.delivery_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
3633: and mdl.delivery_id = md.delivery_id
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
3777: and mdl.delivery_id = md.delivery_id
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
3903: and mdl.delivery_id = 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
4073: and mt.mode_of_transport = l_mode
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
4101: 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
4133: and mdl.trip_id = mts.trip_id
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
4310: -- and ts.stop_id = mdl.pick_up_stop_id
4311: -- Bug_Fix for 3693945
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
4350: or mdl.drop_off_stop_id = mts.stop_id)
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
4385: -- and ts.stop_id = mdl.pick_up_stop_id
4386: -- Bug_Fix for 3693945
4429: cursor cur_weight_myfac (l_plan_id in number
4430: , l_myfac_id in number) is
4431: select nvl(sum(nvl(md.gross_weight,0)),0)
4432: from mst_deliveries md
4433: , fte_location_parameters flp
4434: where md.plan_id = l_plan_id
4435: and (md.pickup_location_id = flp.location_id
4436: or md.dropoff_location_id = flp.location_id)
4437: and flp.facility_id = l_myfac_id;
4442: , l_c_s_ident in number
4443: , l_cust_supp_id in number) is
4444: select nvl(sum(nvl(md.gross_weight,0)),0)
4445: from mst_deliveries md
4446: , fte_location_parameters flp
4447: where md.plan_id = l_plan_id
4448: and (md.pickup_location_id = flp.location_id
4449: or md.dropoff_location_id = flp.location_id)
4450: and flp.facility_id = l_myfac_id
4454: , l_myfac_id in number
4455: , l_carrier_id in number) is
4456: select nvl(sum(nvl(md.gross_weight,0)),0)
4457: from mst_deliveries md
4458: , fte_location_parameters flp
4459: where md.plan_id = l_plan_id
4460: and (md.pickup_location_id = flp.location_id
4461: or md.dropoff_location_id = flp.location_id)
4462: and flp.facility_id = l_myfac_id
4500: cursor cur_volume_myfac (l_plan_id in number
4501: , l_myfac_id in number) is
4502: select nvl(sum(nvl(md.volume,0)),0)
4503: from mst_deliveries md
4504: , fte_location_parameters flp
4505: where md.plan_id = l_plan_id
4506: and (md.pickup_location_id = flp.location_id
4507: or md.dropoff_location_id = flp.location_id)
4508: and flp.facility_id = l_myfac_id;
4513: , l_c_s_ident in number
4514: , l_cust_supp_id in number) is
4515: select nvl(sum(nvl(md.volume,0)),0)
4516: from mst_deliveries md
4517: , fte_location_parameters flp
4518: where md.plan_id = l_plan_id
4519: and (md.pickup_location_id = flp.location_id
4520: or md.dropoff_location_id = flp.location_id)
4521: and flp.facility_id = l_myfac_id
4525: , l_myfac_id in number
4526: , l_carrier_id in number) is
4527: select nvl(sum(nvl(md.volume,0)),0)
4528: from mst_deliveries md
4529: , fte_location_parameters flp
4530: where md.plan_id = l_plan_id
4531: and (md.pickup_location_id = flp.location_id
4532: or md.dropoff_location_id = flp.location_id)
4533: and flp.facility_id = l_myfac_id
4571: cursor cur_pieces_myfac (l_plan_id in number
4572: , l_myfac_id in number) is
4573: select nvl(sum(nvl(md.number_of_pieces,0)),0)
4574: from mst_deliveries md
4575: , fte_location_parameters flp
4576: where md.plan_id = l_plan_id
4577: and (md.pickup_location_id = flp.location_id
4578: or md.dropoff_location_id = flp.location_id)
4579: and flp.facility_id = l_myfac_id;
4584: , l_c_s_ident in number
4585: , l_cust_supp_id in number) is
4586: select nvl(sum(nvl(md.number_of_pieces,0)),0)
4587: from mst_deliveries md
4588: , fte_location_parameters flp
4589: where md.plan_id = l_plan_id
4590: and (md.pickup_location_id = flp.location_id
4591: or md.dropoff_location_id = flp.location_id)
4592: and flp.facility_id = l_myfac_id
4596: , l_myfac_id in number
4597: , l_carrier_id in number) is
4598: select nvl(sum(nvl(md.number_of_pieces,0)),0)
4599: from mst_deliveries md
4600: , fte_location_parameters flp
4601: where md.plan_id = l_plan_id
4602: and (md.pickup_location_id = flp.location_id
4603: or md.dropoff_location_id = flp.location_id)
4604: and flp.facility_id = l_myfac_id
4643: , l_myfac_id in number
4644: , l_mode in varchar2) is
4645: select count(*)
4646: from mst_trips mt
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
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
4671: and decode(l_c_s_ident, 2, md.customer_id, md.supplier_id) = l_cust_supp_id
4672: and mts.plan_id = md.plan_id
4673: and mts.stop_location_id = md.dropoff_location_id
4691: , l_carrier_id in number
4692: , l_mode in varchar2) is
4693: select count(*)
4694: from mst_trips mt
4695: , fte_location_parameters flp
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
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
4758: and mt.plan_id = mts.plan_id
4773: + nvl(mdl.allocated_fac_rec_hand_cost,0)),0)
4774: from mst_deliveries md
4775: , mst_delivery_legs mdl
4776: , mst_trips mt
4777: , fte_location_parameters flp
4778: where mt.plan_id = l_plan_id
4779: and mt.mode_of_transport = l_mode
4780: and mt.plan_id = mdl.plan_id
4781: and mt.trip_id = mdl.trip_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
4811: and mt.plan_id = mts.plan_id
4827: + nvl(mdl.allocated_fac_rec_hand_cost,0)),0)
4828: from mst_deliveries md
4829: , mst_delivery_legs mdl
4830: , mst_trips mt
4831: , fte_location_parameters flp
4832: where mt.plan_id = l_plan_id
4833: and mt.mode_of_transport = l_mode
4834: and mt.plan_id = mdl.plan_id
4835: and mt.trip_id = mdl.trip_id
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
4868: and mts.plan_id = md.plan_id
4869: and mdl.plan_id = md.plan_id
4870: and mdl.delivery_id = md.delivery_id
4995: elsif p_report_for = 3 then
4996: --get the facility location
4997: select flp.location_id
4998: into l_loc_id
4999: from fte_location_parameters flp
5000: where flp.facility_id = p_myfac_id;
5001: open cur_total_cost_carr (p_plan_id, l_loc_id, p_report_for_id, p_mode);
5002: fetch cur_total_cost_carr into l_total_cost;
5003: close cur_total_cost_carr;
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
5045: -- and ts.stop_id = mdl.pick_up_stop_id
5046: -- Bug_Fix for 3694008
5166: , l_cust_supp_id in number
5167: , l_myfac_id in number) is
5168: select nvl(sum(nvl(md.gross_weight,0)),0)
5169: from mst_deliveries md
5170: , fte_location_parameters flp
5171: where md.plan_id = l_plan_id
5172: and decode(l_c_s_ident, 2, md.customer_id, md.supplier_id) = l_cust_supp_id
5173: and (md.pickup_location_id = flp.location_id
5174: or md.dropoff_location_id = flp.location_id)
5238: , l_cust_supp_id in number
5239: , l_myfac_id in number) is
5240: select nvl(sum(nvl(md.volume,0)),0)
5241: from mst_deliveries md
5242: , fte_location_parameters flp
5243: where md.plan_id = l_plan_id
5244: and decode(l_c_s_ident, 2, md.customer_id, md.supplier_id) = l_cust_supp_id
5245: and (md.pickup_location_id = flp.location_id
5246: or md.dropoff_location_id = flp.location_id)
5309: , l_cust_supp_id in number
5310: , l_myfac_id in number) is
5311: select nvl(sum(nvl(md.number_of_pieces,0)),0)
5312: from mst_deliveries md
5313: , fte_location_parameters flp
5314: where md.plan_id = l_plan_id
5315: and decode(l_c_s_ident, 2, md.customer_id, md.supplier_id) = l_cust_supp_id
5316: and (md.pickup_location_id = flp.location_id
5317: or md.dropoff_location_id = flp.location_id)
5382: , l_myfac_id in number
5383: , l_mode in varchar2) is
5384: select count(*)
5385: from mst_trips mt
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
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
5519: and mt.plan_id = mts.plan_id
5535: + nvl(mdl.allocated_fac_rec_hand_cost,0)),0)
5536: from mst_deliveries md
5537: , mst_delivery_legs mdl
5538: , mst_trips mt
5539: , fte_location_parameters flp
5540: where mt.plan_id = l_plan_id
5541: and mt.mode_of_transport = l_mode
5542: and mt.plan_id = mdl.plan_id
5543: and mt.trip_id = mdl.trip_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
5698: AND decode(l_c_s_ident, 2, md.customer_id, md.supplier_id) = l_cust_supp_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
5729: AND decode(l_c_s_ident, 2, md.customer_id, md.supplier_id) = l_cust_supp_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
5852: AND decode(l_c_s_ident, 2, md.customer_id, md.supplier_id) = l_cust_supp_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
5883: and decode(l_c_s_ident, 2, md.customer_id, md.supplier_id) = l_cust_supp_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
6126: and wl.wsh_location_id = wlo.wsh_location_id