[Home] [Help]
34: where md.plan_id = mda.plan_id
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
105: and md.plan_id = mda.plan_id
106: and md.delivery_id = mda.delivery_id
107: and md.delivery_id in (select distinct mdl.delivery_id
108: from mst_delivery_legs mdl
109: , mst_trips mt
110: where mdl.plan_id = md.plan_id
111: and mt.plan_id = mdl.plan_id
112: and mt.trip_id = mdl.trip_id
113: and mt.carrier_id = l_carrier_id);
176: from mst_deliveries md
177: where md.plan_id = l_plan_id
178: and md.delivery_id in (select distinct mdl.delivery_id
179: from mst_delivery_legs mdl
180: , mst_trips mt
181: where mdl.plan_id = md.plan_id
182: and mt.plan_id = mdl.plan_id
183: and mt.trip_id = mdl.trip_id
184: and mt.carrier_id = l_carrier_id);
246: from mst_deliveries md
247: where md.plan_id = l_plan_id
248: and md.delivery_id in (select distinct mdl.delivery_id
249: from mst_delivery_legs mdl
250: , mst_trips mt
251: where mdl.plan_id = md.plan_id
252: and mt.plan_id = mdl.plan_id
253: and mt.trip_id = mdl.trip_id
254: and mt.carrier_id = l_carrier_id);
316: from mst_deliveries md
317: where md.plan_id = l_plan_id
318: and md.delivery_id in (select distinct mdl.delivery_id
319: from mst_delivery_legs mdl
320: , mst_trips mt
321: where mdl.plan_id = md.plan_id
322: and mt.plan_id = mdl.plan_id
323: and mt.trip_id = mdl.trip_id
324: and mt.carrier_id = l_carrier_id);
386: from mst_deliveries md
387: where md.plan_id = l_plan_id
388: and md.delivery_id in (select distinct mdl.delivery_id
389: from mst_delivery_legs mdl
390: , mst_trips mt
391: where mdl.plan_id = md.plan_id
392: and mt.plan_id = mdl.plan_id
393: and mt.trip_id = mdl.trip_id
394: and mt.carrier_id = l_carrier_id);
481: and md.plan_id = mda.plan_id
482: and md.delivery_id = mda.delivery_id
483: and md.delivery_id in (select distinct mdl.delivery_id
484: from mst_delivery_legs mdl
485: , mst_trips mt
486: where mdl.plan_id = md.plan_id
487: and mt.plan_id = mdl.plan_id
488: and mt.trip_id = mdl.trip_id
489: and mt.carrier_id = l_carrier_id);
522:
523: cursor cur_trips (l_plan_id in number
524: , l_mode in varchar2) is
525: select count(*)
526: from mst_trips mt
527: where mt.plan_id = l_plan_id
528: and mt.mode_of_transport = l_mode;
529:
530: cursor cur_trips_myfac (l_plan_id in number
530: cursor cur_trips_myfac (l_plan_id in number
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
545: , l_c_s_ident in number
546: , l_cust_supp_id in number
547: , l_mode in varchar2) is
548: select count(mt.trip_id)
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
573: cursor cur_trips_carr (l_plan_id in number
574: , l_carrier_id in number
575: , l_mode in varchar2) is
576: select count(*)
577: from mst_trips mt
578: where mt.plan_id = l_plan_id
579: and mt.carrier_id = l_carrier_id
580: and mt.mode_of_transport = l_mode;
581:
632: and mdl.delivery_id in
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
654: + nvl(mdl.allocated_fac_loading_cost,0)
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
681: and mdl.delivery_id in
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
701: + nvl(mdl.allocated_fac_loading_cost,0)
702: + nvl(mdl.allocated_fac_unloading_cost,0)),0)
703: from mst_deliveries md
704: , mst_delivery_legs mdl
705: , mst_trips mt
706: where mt.plan_id = l_plan_id
707: and mt.mode_of_transport = l_mode
708: and mt.plan_id = mdl.plan_id
709: and mt.trip_id = mdl.trip_id
722: + nvl(mt.total_layover_cost,0)
723: + nvl(mt.total_accessorial_cost,0))
724: , (nvl(mt.total_basic_transport_cost,0)
725: + nvl(mt.total_accessorial_cost,0)))),0)
726: from mst_trips mt
727: where mt.plan_id = l_plan_id
728: and mt.carrier_id = l_carrier_id
729: and mt.mode_of_transport = l_mode;
730: -- and mt.continuous_move_id is null;
768: from mst_plans mp
769: , mst_delivery_legs mdl
770: where mp.plan_id = mdl.plan_id
771: and mdl.trip_id in (select mt.trip_id
772: from mst_trips mt
773: where mt.plan_id = mp.plan_id
774: and mt.mode_of_transport = l_mode)
775: and mdl.plan_id = l_plan_id;
776:
785: and mdl.delivery_id in
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
806: ( select nvl(sum(nvl(mdl.allocated_fac_shp_hand_cost,0)
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
831: and mdl.delivery_id in
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
850: (select nvl(sum(nvl(mdl.allocated_fac_shp_hand_cost,0)
851: + nvl(mdl.allocated_fac_rec_hand_cost,0)),0)
852: from mst_deliveries md
853: , mst_delivery_legs mdl
854: , mst_trips mt
855: where mt.plan_id = l_plan_id
856: and mt.mode_of_transport = l_mode
857: and mt.plan_id = mdl.plan_id
858: and mt.trip_id = mdl.trip_id
867: , l_mode in varchar2) is
868: select nvl(sum(decode(mt.mode_of_transport
869: , 'TRUCK', (nvl(mt.total_handling_cost,0))
870: , (nvl(mt.total_handling_cost,0)))),0)
871: from mst_trips mt
872: where mt.plan_id = l_plan_id
873: and mt.carrier_id = l_carrier_id
874: and mt.mode_of_transport = l_mode;
875: -- and mt.continuous_move_id is null;
916: from mst_plans mp
917: , mst_delivery_legs mdl
918: where mp.plan_id = mdl.plan_id
919: and mdl.trip_id in (select mt.trip_id
920: from mst_trips mt
921: where mt.plan_id = mp.plan_id
922: and mt.mode_of_transport = l_mode)
923: and mdl.plan_id = l_plan_id;
924:
936: and mdl.delivery_id in
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
960: + nvl(mdl.allocated_fac_shp_hand_cost,0)
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
988: and mdl.delivery_id in
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
1010: + nvl(mdl.allocated_fac_shp_hand_cost,0)
1011: + nvl(mdl.allocated_fac_rec_hand_cost,0)),0)
1012: from mst_deliveries md
1013: , mst_delivery_legs mdl
1014: , mst_trips mt
1015: where mt.plan_id = l_plan_id
1016: and mt.mode_of_transport = l_mode
1017: and mt.plan_id = mdl.plan_id
1018: and mt.trip_id = mdl.trip_id
1032: + nvl(mt.total_accessorial_cost,0)
1033: + nvl(mt.total_handling_cost,0))
1034: , (nvl(mt.total_basic_transport_cost,0)
1035: + nvl(mt.total_accessorial_cost,0)))),0)
1036: from mst_trips mt
1037: where mt.plan_id = l_plan_id
1038: and mt.carrier_id = l_carrier_id
1039: and mt.mode_of_transport = l_mode;
1040: -- and mt.continuous_move_id is null;
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'
1084: and mts.plan_id = l_plan_id;
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
1095: and mt.mode_of_transport = 'TRUCK'
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
1108: , fte_location_parameters flp
1109: where mt.plan_id = mts.plan_id
1110: and mt.trip_id = mts.trip_id
1115: and flp.location_id = mts1.stop_location_id );
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
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'
1145: and mts.plan_id = l_plan_id
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'
1162: and mt.carrier_id = l_carrier_id
1163: and mts.plan_id = l_plan_id;
1164:
1165: cursor cur_TLs (l_plan_id in number) is
1166: select count(*)
1167: from mst_trips mt
1168: where mt.plan_id = l_plan_id
1169: and mt.mode_of_transport = 'TRUCK';
1170:
1171: cursor cur_TLs_myfac (l_plan_id in number
1171: cursor cur_TLs_myfac (l_plan_id in number
1172: , l_myfac_id in number) is
1173: /*
1174: select count(*)
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
1183: and flp.facility_id = l_myfac_id);
1184: */
1185: --Bug_Fix for 3696518 - II
1186: select count ( * )
1187: from mst_trips mt
1188: where mt.plan_id= l_plan_id
1189: and mt.mode_of_transport = 'TRUCK'
1190: and mt.trip_id in ( select mdl.trip_id
1191: from mst_deliveries md
1202: , l_c_s_ident in number
1203: , l_cust_supp_id in number) is
1204: /*
1205: select count(*)
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
1227: group by ts.trip_id);
1228: */
1229: --Bug_Fix for 3696518 - II
1230: select count ( * )
1231: from mst_trips mt
1232: where mt.plan_id= l_plan_id
1233: and mt.mode_of_transport = 'TRUCK'
1234: and mt.trip_id in ( select mdl.trip_id
1235: from mst_deliveries md
1241:
1242: cursor cur_TLs_carr (l_plan_id in number
1243: , l_carrier_id in number) is
1244: select count(*)
1245: from mst_trips mt
1246: where mt.plan_id = l_plan_id
1247: and mt.mode_of_transport = 'TRUCK'
1248: and mt.carrier_id = l_carrier_id;
1249:
1316: l_TL_distance number;
1317:
1318: cursor cur_TL_distance (l_plan_id in number) is
1319: select nvl(sum(mt.total_trip_distance),0)
1320: from mst_trips mt
1321: where mt.plan_id= l_plan_id
1322: and mt.mode_of_transport = 'TRUCK';
1323:
1324: cursor cur_TL_distance_myfac (l_plan_id in number
1324: cursor cur_TL_distance_myfac (l_plan_id in number
1325: , l_myfac_id in number) is
1326: /*
1327: select nvl(sum(mt.total_trip_distance),0)
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
1336: and flp.facility_id = l_myfac_id);
1337: */
1338: --Bug_Fix for 3696518 - II
1339: select nvl(sum(nvl(mt.total_trip_distance,0)),0)
1340: from mst_trips mt
1341: where mt.plan_id= l_plan_id
1342: and mt.mode_of_transport = 'TRUCK'
1343: and mt.trip_id in ( select mdl.trip_id
1344: from mst_deliveries md
1356: , l_c_s_ident in number
1357: , l_cust_supp_id in number) is
1358: /*
1359: select nvl(sum(mt.total_trip_distance),0)
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
1381: group by ts.trip_id);
1382: */
1383: --Bug_Fix for 3696518 - II
1384: select nvl(sum(nvl(mt.total_trip_distance,0)),0)
1385: from mst_trips mt
1386: where mt.plan_id= l_plan_id
1387: and mt.mode_of_transport = 'TRUCK'
1388: and mt.trip_id in ( select mdl.trip_id
1389: from mst_deliveries md
1395:
1396: cursor cur_TL_distance_carr (l_plan_id in number
1397: , l_carrier_id in number) is
1398: select nvl(sum(mt.total_trip_distance),0)
1399: from mst_trips mt
1400: where mt.plan_id= l_plan_id
1401: and mt.mode_of_transport = 'TRUCK'
1402: and mt.carrier_id = l_carrier_id;
1403:
1436:
1437: cursor cur_carr_moves (l_plan_id in number
1438: , l_carrier_id in number) is
1439: select count(*)
1440: from mst_trips mt
1441: where mt.plan_id = l_plan_id
1442: and mt.carrier_id = l_carrier_id;
1443:
1444: cursor cur_carr_moves_myfac (l_plan_id in number
1445: , l_myfac_id in number
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
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
1467: and ( md.dropoff_location_id = flp.location_id
1468: or md.pickup_location_id = flp.location_id )
1477: , l_cust_supp_id in number
1478: , l_carrier_id in number) is
1479: /*
1480: select count(mt.trip_id)
1481: from mst_trips mt
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
1505: --Bug_Fix for 3696518 - II
1506: select count ( distinct mt.trip_id )
1507: from mst_deliveries md
1508: , mst_delivery_legs mdl
1509: , mst_trips mt
1510: where md.plan_id = l_plan_id
1511: and decode ( l_c_s_ident, 2, md.customer_id, md.supplier_id ) = l_cust_supp_id
1512: and mdl.plan_id = md.plan_id
1513: and mdl.delivery_id = md.delivery_id
1550: from mst_plans mp
1551: , mst_delivery_legs mdl
1552: where mp.plan_id = mdl.plan_id
1553: and mdl.trip_id in (select mt.trip_id
1554: from mst_trips mt
1555: where mt.plan_id = mp.plan_id
1556: and mt.carrier_id = l_carrier_id)
1557: and mdl.plan_id = l_plan_id;
1558:
1560: , l_myfac_id in number
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
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
1588: and ( md.dropoff_location_id = flp.location_id
1589: or md.pickup_location_id = flp.location_id )
1600: /*
1601: select nvl(sum(nvl(mdl.allocated_transport_cost,0)),0)
1602: from mst_deliveries md
1603: , mst_delivery_legs mdl
1604: , mst_trips mt
1605: where md.plan_id = l_plan_id
1606: and decode(l_c_s_ident, 2, md.customer_id, md.supplier_id) = l_cust_supp_id
1607: and md.plan_id = mdl.plan_id
1608: and md.delivery_id = mdl.delivery_id
1613: --Bug_Fix for 3696518 - II
1614: select sum ( nvl ( mdl.allocated_transport_cost, 0 ) )
1615: from mst_deliveries md
1616: , mst_delivery_legs mdl
1617: , mst_trips mt
1618: where md.plan_id = l_plan_id
1619: and decode ( l_c_s_ident, 2, md.customer_id, md.supplier_id ) = l_cust_supp_id
1620: and mdl.plan_id = md.plan_id
1621: and mdl.delivery_id = md.delivery_id
1749: where md.plan_id = mda.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
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
1871: from mst_delivery_legs mdl
1872: , mst_trips mt
1873: where mdl.plan_id = md.plan_id
1874: and mt.plan_id = mdl.plan_id
1875: and mt.trip_id = mdl.trip_id
1876: and mt.carrier_id = l_carrier_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
1977: from mst_delivery_legs mdl
1978: , mst_trips mt
1979: where mdl.plan_id = md.plan_id
1980: and mt.plan_id = mdl.plan_id
1981: and mt.trip_id = mdl.trip_id
1982: and mt.carrier_id = l_carrier_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
2083: from mst_delivery_legs mdl
2084: , mst_trips mt
2085: where mdl.plan_id = md.plan_id
2086: and mt.plan_id = mdl.plan_id
2087: and mt.trip_id = mdl.trip_id
2088: and mt.carrier_id = l_carrier_id);
2122: cursor cur_MTL_orig (l_plan_id in number
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
2150: , l_myfac_id in number
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
2182: , l_cust_supp_id in number
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
2212: , l_carrier_id in number
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
2273: cursor cur_DTL_orig (l_plan_id in number
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
2301: , l_myfac_id in number
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
2333: , l_cust_supp_id in number
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
2363: , l_carrier_id in number
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
2423:
2424: cursor cur_LTL_orig (l_plan_id in number
2425: , l_orig_state in varchar2) is
2426: select count(*)
2427: from mst_trips mt
2428: where mt.plan_id = l_plan_id
2429: and mt.mode_of_transport = 'LTL'
2430: and mt.trip_id in (select distinct mdl.trip_id
2431: from mst_deliveries md
2445: cursor cur_LTL_orig_myfac (l_plan_id in number
2446: , l_myfac_id in number
2447: , l_orig_state in varchar2) is
2448: select count(*)
2449: from mst_trips mt
2450: where mt.plan_id = l_plan_id
2451: and mt.mode_of_transport = 'LTL'
2452: and mt.trip_id in (select distinct mdl.trip_id
2453: from mst_deliveries md
2471: , l_c_s_ident in number
2472: , l_cust_supp_id in number
2473: , l_orig_state in varchar2) is
2474: select count(*)
2475: from mst_trips mt
2476: where mt.plan_id = l_plan_id
2477: and mt.mode_of_transport = 'LTL'
2478: and mt.trip_id in
2479: (select distinct mdl.trip_id
2495: cursor cur_LTL_orig_carr (l_plan_id in number
2496: , l_carrier_id in number
2497: , l_orig_state in varchar2) is
2498: select count(*)
2499: from mst_trips mt
2500: where mt.plan_id = l_plan_id
2501: and mt.mode_of_transport = 'LTL'
2502: and mt.carrier_id = l_carrier_id
2503: and mt.trip_id in (select distinct mdl.trip_id
2549:
2550: cursor cur_PCL_orig (l_plan_id in number
2551: , l_orig_state in varchar2) is
2552: select count(*)
2553: from mst_trips mt
2554: where mt.plan_id = l_plan_id
2555: and mt.mode_of_transport = 'PARCEL'
2556: and mt.trip_id in (select distinct mdl.trip_id
2557: from mst_deliveries md
2571: cursor cur_PCL_orig_myfac (l_plan_id in number
2572: , l_myfac_id in number
2573: , l_orig_state in varchar2) is
2574: select count(*)
2575: from mst_trips mt
2576: where mt.plan_id = l_plan_id
2577: and mt.mode_of_transport = 'PARCEL'
2578: and mt.trip_id in (select distinct mdl.trip_id
2579: from mst_deliveries md
2597: , l_c_s_ident in number
2598: , l_cust_supp_id in number
2599: , l_orig_state in varchar2) is
2600: select count(*)
2601: from mst_trips mt
2602: where mt.plan_id = l_plan_id
2603: and mt.mode_of_transport = 'PARCEL'
2604: and mt.trip_id in
2605: (select distinct mdl.trip_id
2621: cursor cur_PCL_orig_carr (l_plan_id in number
2622: , l_carrier_id in number
2623: , l_orig_state in varchar2) is
2624: select count(*)
2625: from mst_trips mt
2626: where mt.plan_id = l_plan_id
2627: and mt.mode_of_transport = 'PARCEL'
2628: and mt.carrier_id = l_carrier_id
2629: and mt.trip_id in (select distinct mdl.trip_id
2688: and mdl.delivery_id in
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
2711: + nvl(mdl.allocated_fac_shp_hand_cost,0)
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
2747: and mdl.delivery_id in
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
2774: + nvl(mdl.allocated_fac_shp_hand_cost,0)
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
2800: + nvl(mdl.allocated_transport_cost,0)
2801: + nvl(mdl.allocated_fac_shp_hand_cost,0)
2802: + nvl(mdl.allocated_fac_rec_hand_cost,0)),0)
2803: from mst_delivery_legs mdl
2804: , mst_trips mt
2805: where mt.plan_id = l_plan_id
2806: and mt.mode_of_transport = l_mode
2807: and mt.plan_id = mdl.plan_id
2808: and mt.trip_id = mdl.trip_id
2838: from mst_delivery_legs mdl
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
2864: + nvl(mdl.allocated_fac_shp_hand_cost,0)
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
2896: + nvl(mt.total_accessorial_cost,0)
2897: + nvl(mt.total_handling_cost,0))
2898: , (nvl(mt.total_basic_transport_cost,0)
2899: + nvl(mt.total_accessorial_cost,0)))),0)
2900: from mst_trips mt
2901: where mt.plan_id = l_plan_id
2902: and mt.carrier_id = l_carrier_id
2903: and mt.mode_of_transport = l_mode
2904: -- and mt.continuous_move_id is null --check
2924: + nvl(mdl.allocated_fac_rec_hand_cost,0)
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
3067: where md.plan_id = mda.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
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
3189: from mst_delivery_legs mdl
3190: , mst_trips mt
3191: where mdl.plan_id = md.plan_id
3192: and mt.plan_id = mdl.plan_id
3193: and mt.trip_id = mdl.trip_id
3194: and mt.carrier_id = l_carrier_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
3295: from mst_delivery_legs mdl
3296: , mst_trips mt
3297: where mdl.plan_id = md.plan_id
3298: and mt.plan_id = mdl.plan_id
3299: and mt.trip_id = mdl.trip_id
3300: and mt.carrier_id = l_carrier_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
3401: from mst_delivery_legs mdl
3402: , mst_trips mt
3403: where mdl.plan_id = md.plan_id
3404: and mt.plan_id = mdl.plan_id
3405: and mt.trip_id = mdl.trip_id
3406: and mt.carrier_id = l_carrier_id);
3440: cursor cur_MTL_dest (l_plan_id in number
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
3468: , l_myfac_id in number
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
3500: , l_cust_supp_id in number
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
3530: , l_carrier_id in number
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
3590: cursor cur_DTL_dest (l_plan_id in number
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
3618: , l_myfac_id in number
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
3650: , l_cust_supp_id in number
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
3680: , l_carrier_id in number
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
3740:
3741: cursor cur_LTL_dest (l_plan_id in number
3742: , l_destination_state in varchar2) is
3743: select count(*)
3744: from mst_trips mt
3745: where mt.plan_id = l_plan_id
3746: and mt.mode_of_transport = 'LTL'
3747: and mt.trip_id in (select distinct mdl.trip_id
3748: from mst_deliveries md
3762: cursor cur_LTL_dest_myfac (l_plan_id in number
3763: , l_myfac_id in number
3764: , l_destination_state in varchar2) is
3765: select count(*)
3766: from mst_trips mt
3767: where mt.plan_id = l_plan_id
3768: and mt.mode_of_transport = 'LTL'
3769: and mt.trip_id in (select distinct mdl.trip_id
3770: from mst_deliveries md
3788: , l_c_s_ident in number
3789: , l_cust_supp_id in number
3790: , l_destination_state in varchar2) is
3791: select count(*)
3792: from mst_trips mt
3793: where mt.plan_id = l_plan_id
3794: and mt.mode_of_transport = 'LTL'
3795: and mt.trip_id in
3796: (select distinct mdl.trip_id
3812: cursor cur_LTL_dest_carr (l_plan_id in number
3813: , l_carrier_id in number
3814: , l_destination_state in varchar2) is
3815: select count(*)
3816: from mst_trips mt
3817: where mt.plan_id = l_plan_id
3818: and mt.mode_of_transport = 'LTL'
3819: and mt.carrier_id = l_carrier_id
3820: and mt.trip_id in (select distinct mdl.trip_id
3866:
3867: cursor cur_PCL_dest (l_plan_id in number
3868: , l_destination_state in varchar2) is
3869: select count(*)
3870: from mst_trips mt
3871: where mt.plan_id = l_plan_id
3872: and mt.mode_of_transport = 'PARCEL'
3873: and mt.trip_id in (select distinct mdl.trip_id
3874: from mst_deliveries md
3888: cursor cur_PCL_dest_myfac (l_plan_id in number
3889: , l_myfac_id in number
3890: , l_destination_state in varchar2) is
3891: select count(*)
3892: from mst_trips mt
3893: where mt.plan_id = l_plan_id
3894: and mt.mode_of_transport = 'PARCEL'
3895: and mt.trip_id in (select distinct mdl.trip_id
3896: from mst_deliveries md
3914: , l_c_s_ident in number
3915: , l_cust_supp_id in number
3916: , l_destination_state in varchar2) is
3917: select count(*)
3918: from mst_trips mt
3919: where mt.plan_id = l_plan_id
3920: and mt.mode_of_transport = 'PARCEL'
3921: and mt.trip_id in
3922: (select distinct mdl.trip_id
3938: cursor cur_PCL_dest_carr (l_plan_id in number
3939: , l_carrier_id in number
3940: , l_destination_state in varchar2) is
3941: select count(*)
3942: from mst_trips mt
3943: where mt.plan_id = l_plan_id
3944: and mt.mode_of_transport = 'PARCEL'
3945: and mt.carrier_id = l_carrier_id
3946: and mt.trip_id in (select distinct mdl.trip_id
4005: and mdl.delivery_id in
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
4028: + nvl(mdl.allocated_fac_shp_hand_cost,0)
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
4063: and mdl.delivery_id in
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
4090: + nvl(mdl.allocated_fac_shp_hand_cost,0)
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
4116: + nvl(mdl.allocated_transport_cost,0)
4117: + nvl(mdl.allocated_fac_shp_hand_cost,0)
4118: + nvl(mdl.allocated_fac_rec_hand_cost,0)),0)
4119: from mst_delivery_legs mdl
4120: , mst_trips mt
4121: where mt.plan_id = l_plan_id
4122: and mt.mode_of_transport = l_mode
4123: and mt.plan_id = mdl.plan_id
4124: and mt.trip_id = mdl.trip_id
4154: from mst_delivery_legs mdl
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
4180: + nvl(mdl.allocated_fac_shp_hand_cost,0)
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
4212: + nvl(mt.total_accessorial_cost,0)
4213: + nvl(mt.total_handling_cost,0))
4214: , (nvl(mt.total_basic_transport_cost,0)
4215: + nvl(mt.total_accessorial_cost,0)))),0)
4216: from mst_trips mt
4217: where mt.plan_id = l_plan_id
4218: and mt.carrier_id = l_carrier_id
4219: and mt.mode_of_transport = l_mode
4220: -- and mt.continuous_move_id is null --check
4240: + nvl(mdl.allocated_fac_rec_hand_cost,0)
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
4300: where md.plan_id = mda.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
4311: -- Bug_Fix for 3693945
4312: and ( ts.stop_id = mdl.pick_up_stop_id
4313: or ts.stop_id = mdl.drop_off_stop_id )
4314: and ts.stop_location_id = flp.location_id
4315: and flp.facility_id = l_myfac_id ) -- ending the subquery here, with the join with mst_trips removed
4316: -- and ts.plan_id = t.plan_id
4317: -- and ts.trip_id = t.trip_id)
4318: and mda.plan_id = mdd.plan_id
4319: and mda.delivery_detail_id = mdd.delivery_detail_id
4375: where md.plan_id = mda.plan_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
4461: or md.dropoff_location_id = flp.location_id)
4462: and flp.facility_id = l_myfac_id
4463: and md.delivery_id in (select distinct mdl.delivery_id
4464: from mst_delivery_legs mdl
4465: , mst_trips mt
4466: where mdl.plan_id = md.plan_id
4467: and mt.plan_id = mdl.plan_id
4468: and mt.trip_id = mdl.trip_id
4469: and mt.carrier_id = l_carrier_id);
4532: or md.dropoff_location_id = flp.location_id)
4533: and flp.facility_id = l_myfac_id
4534: and md.delivery_id in (select distinct mdl.delivery_id
4535: from mst_delivery_legs mdl
4536: , mst_trips mt
4537: where mdl.plan_id = md.plan_id
4538: and mt.plan_id = mdl.plan_id
4539: and mt.trip_id = mdl.trip_id
4540: and mt.carrier_id = l_carrier_id);
4603: or md.dropoff_location_id = flp.location_id)
4604: and flp.facility_id = l_myfac_id
4605: and md.delivery_id in (select distinct mdl.delivery_id
4606: from mst_delivery_legs mdl
4607: , mst_trips mt
4608: where mdl.plan_id = md.plan_id
4609: and mt.plan_id = mdl.plan_id
4610: and mt.trip_id = mdl.trip_id
4611: and mt.carrier_id = l_carrier_id);
4642: cursor cur_trips_myfac (l_plan_id in number
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
4658: , l_c_s_ident in number
4659: , l_cust_supp_id in number
4660: , l_mode in varchar2) is
4661: select count(mt.trip_id)
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
4690: , l_myfac_id in number
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
4748: and mdl.delivery_id in
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
4772: + nvl(mdl.allocated_fac_shp_hand_cost,0)
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
4801: and mdl.delivery_id in
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
4826: + nvl(mdl.allocated_fac_shp_hand_cost,0)
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
4852: + nvl(mt.total_accessorial_cost,0)
4853: + nvl(mt.total_handling_cost,0))
4854: , (nvl(mt.total_basic_transport_cost,0)
4855: + nvl(mt.total_accessorial_cost,0)))),0)
4856: from mst_trips mt
4857: where mt.plan_id = l_plan_id
4858: and mt.carrier_id = l_carrier_id
4859: and mt.mode_of_transport = l_mode
4860: -- and mt.continuous_move_id is null --check
4887: and mdl.delivery_id in
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
4910: + nvl(mdl.allocated_fac_shp_hand_cost,0)
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
4940: and mdl.delivery_id in
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
4965: + nvl(mdl.allocated_fac_shp_hand_cost,0)
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
5035: and md.delivery_id = mda.delivery_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
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
5127: or mdl.drop_off_stop_id = mts.stop_id)
5195: where md.plan_id = l_plan_id
5196: and decode(l_c_s_ident, 2, md.customer_id, md.supplier_id) = l_cust_supp_id
5197: and md.delivery_id in (select distinct mdl.delivery_id
5198: from mst_delivery_legs mdl
5199: , mst_trips mt
5200: where mdl.plan_id = md.plan_id
5201: and mt.plan_id = mdl.plan_id
5202: and mt.trip_id = mdl.trip_id
5203: and mt.carrier_id = l_carrier_id);
5267: where md.plan_id = l_plan_id
5268: and decode(l_c_s_ident, 2, md.customer_id, md.supplier_id) = l_cust_supp_id
5269: and md.delivery_id in (select distinct mdl.delivery_id
5270: from mst_delivery_legs mdl
5271: , mst_trips mt
5272: where mdl.plan_id = md.plan_id
5273: and mt.plan_id = mdl.plan_id
5274: and mt.trip_id = mdl.trip_id
5275: and mt.carrier_id = l_carrier_id);
5338: where md.plan_id = l_plan_id
5339: and decode(l_c_s_ident, 2, md.customer_id, md.supplier_id) = l_cust_supp_id
5340: and md.delivery_id in (select distinct mdl.delivery_id
5341: from mst_delivery_legs mdl
5342: , mst_trips mt
5343: where mdl.plan_id = md.plan_id
5344: and mt.plan_id = mdl.plan_id
5345: and mt.trip_id = mdl.trip_id
5346: and mt.carrier_id = l_carrier_id);
5381: , l_cust_supp_id in number
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
5405: , l_c_s_ident in number
5406: , l_cust_supp_id in number
5407: , l_mode in varchar2) is
5408: select count(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
5436: , l_cust_supp_id in number
5437: , l_carrier_id in number
5438: , l_mode in varchar2) is
5439: select count(mt.trip_id)
5440: from mst_trips mt
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
5509: and mdl.delivery_id in
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
5534: + nvl(mdl.allocated_fac_shp_hand_cost,0)
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
5565: and mdl.delivery_id in
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
5588: + nvl(mdl.allocated_fac_shp_hand_cost,0)
5589: + nvl(mdl.allocated_fac_rec_hand_cost,0)),0)
5590: from mst_deliveries md
5591: , mst_delivery_legs mdl
5592: , mst_trips mt
5593: where mt.plan_id = l_plan_id
5594: and mt.mode_of_transport = l_mode
5595: and mt.plan_id = mdl.plan_id
5596: and mt.trip_id = mdl.trip_id
5612: + nvl(mt.total_accessorial_cost,0)
5613: + nvl(mt.total_handling_cost,0))
5614: , (nvl(mt.total_basic_transport_cost,0)
5615: + nvl(mt.total_accessorial_cost,0)))),0)
5616: from mst_trips mt
5617: where mt.plan_id = l_plan_id
5618: and mt.carrier_id = l_carrier_id
5619: and mt.mode_of_transport = l_mode
5620: -- and mt.continuous_move_id is null --check
5638: + nvl(mdl.allocated_fac_rec_hand_cost,0)
5639: + nvl(mdl.allocated_transport_cost,0))
5640: from mst_deliveries md,
5641: mst_delivery_legs mdl,
5642: mst_trips mt
5643: where md.plan_id = l_plan_id
5644: and decode(l_c_s_ident, 2, md.customer_id, md.supplier_id) = l_cust_supp_id
5645: and md.delivery_id = mdl.delivery_id
5646: and mt.plan_id = mdl.plan_id
5684: , l_cust_supp_id in number
5685: , l_myfac_id in number) is
5686: -- Bug_Fix for 3694008 -- optimized query
5687: SELECT count(mt.trip_id) num_stops
5688: FROM mst_trips mt
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
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
5747: , l_report_for_id in number
5748: , l_c_s_ident in number
5749: , l_cust_supp_id in number) is
5750: select count(mt.trip_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
5777: , l_c_s_ident in number
5778: , l_cust_supp_id in number
5779: , l_carrier_id in number) is
5780: select count(mt.trip_id)
5781: from mst_trips mt
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
5838: , l_cust_supp_id in number
5839: , l_myfac_id in number) is
5840: -- Bug_Fix for 3694008 -- optimized query
5841: SELECT count(mt.trip_id) num_stops
5842: FROM mst_trips mt
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
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
5901: , l_report_for_id in number
5902: , l_c_s_ident in number
5903: , l_cust_supp_id in number) is
5904: select count(mt.trip_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
5931: , l_c_s_ident in number
5932: , l_cust_supp_id in number
5933: , l_carrier_id in number) is
5934: select count(mt.trip_id)
5935: from mst_trips mt
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
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,
6120: wsh_location_owners wlo,
6145: , wc.freight_code
6146: , mst_reports_pkg.get_carr_movements(p_plan_id,p_report_for,p_report_for_id,mt.carrier_id)
6147: , mst_reports_pkg.get_carr_cost(p_plan_id,p_report_for,p_report_for_id,mt.carrier_id)
6148: , mt.mode_of_transport
6149: from mst_trips mt
6150: , wsh_carriers wc
6151: WHERE mt.carrier_id = wc.carrier_id
6152: and mt.plan_id = p_plan_id
6153: and decode ( p_report_for, 3, mt.carrier_id, p_report_for_id ) = p_report_for_id