[Home] [Help]
36: and md.delivery_id in
37: (select mdl.delivery_id
38: from mst_trips t
39: , mst_trip_stops ts
40: , mst_delivery_legs mdl
41: , fte_location_parameters flp
42: where mdl.plan_id = md.plan_id
43: and ts.plan_id = mdl.plan_id
44: and ts.stop_id = mdl.pick_up_stop_id
70: and md.plan_id = l_plan_id
71: and decode(l_c_s_ident, 2, md.customer_id, md.supplier_id) = l_cust_supp_id
72: and md.delivery_id in
73: (select mdl.delivery_id
74: from mst_delivery_legs mdl
75: , mst_trip_stops mts
76: where mdl.plan_id = md.plan_id
77: and mdl.plan_id = mts.plan_id
78: and ( mdl.pick_up_stop_id = mts.stop_id
87: and mda.plan_id = mdd.plan_id
88: and mda.delivery_detail_id = mdd.delivery_detail_id
89: and md.plan_id = l_plan_id
90: and decode(l_c_s_ident, 2, md.customer_id, md.supplier_id) = l_cust_supp_id
91: and not exists (select 1 from mst_delivery_legs mdl
92: where mdl.plan_id=md.plan_id
93: and mdl.delivery_id = md.delivery_id)) dd;
94:
95: cursor cur_plan_orders_carr (l_plan_id in number
104: and mda.parent_delivery_detail_id is null
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
175: select count(*)
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
245: select nvl(sum(nvl(md.gross_weight,0)),0)
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
315: select nvl(sum(nvl(md.volume,0)),0)
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
385: select nvl(sum(nvl(md.number_of_pieces,0)),0)
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
480: and mda.parent_delivery_detail_id is null
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
550: where mt.plan_id = l_plan_id
551: and mt.trip_id in
552: (select distinct mts.trip_id
553: from mst_trip_stops mts
554: , mst_delivery_legs mdl
555: , mst_deliveries md
556: where md.plan_id = mt.plan_id
557: and decode(l_c_s_ident, 2, md.customer_id, md.supplier_id) = l_cust_supp_id
558: and mts.plan_id = md.plan_id
626: /*
627: select nvl(sum(nvl(mdl.allocated_transport_cost,0)
628: + nvl(mdl.allocated_fac_loading_cost,0)
629: + nvl(mdl.allocated_fac_unloading_cost,0)),0)
630: from mst_delivery_legs mdl
631: where mdl.plan_id = l_plan_id
632: and mdl.delivery_id in
633: ( select md.delivery_id
634: from mst_delivery_legs mdl1
630: from mst_delivery_legs mdl
631: where mdl.plan_id = l_plan_id
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
653: ( select nvl(sum(nvl(mdl.allocated_transport_cost,0)
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
675: /*
676: select nvl(sum(nvl(mdl.allocated_transport_cost,0)
677: + nvl(mdl.allocated_fac_loading_cost,0)
678: + nvl(mdl.allocated_fac_unloading_cost,0)),0)
679: from mst_delivery_legs mdl
680: where mdl.plan_id = l_plan_id
681: and mdl.delivery_id in
682: ( select md.delivery_id
683: from mst_delivery_legs mdl1
679: from mst_delivery_legs mdl
680: where mdl.plan_id = l_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
700: ( select nvl(sum(nvl(mdl.allocated_transport_cost,0)
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
765: , l_mode in varchar2) is
766: select nvl(sum(nvl(mdl.allocated_fac_shp_hand_cost,0)
767: + nvl(mdl.allocated_fac_rec_hand_cost,0)),0)
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
779: , l_mode in varchar2) is
780: /*
781: select nvl(sum(nvl(mdl.allocated_fac_shp_hand_cost,0)
782: + nvl(mdl.allocated_fac_rec_hand_cost,0)),0)
783: from mst_delivery_legs mdl
784: where mdl.plan_id = l_plan_id
785: and mdl.delivery_id in
786: ( select md.delivery_id
787: from mst_delivery_legs mdl1
783: from mst_delivery_legs mdl
784: where mdl.plan_id = l_plan_id
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
805: --Bug_Fix for 3696518 - II
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
825: , l_mode in varchar2) is
826: /*
827: select nvl(sum(nvl(mdl.allocated_fac_shp_hand_cost,0)
828: + nvl(mdl.allocated_fac_rec_hand_cost,0)),0)
829: from mst_delivery_legs mdl
830: where mdl.plan_id = l_plan_id
831: and mdl.delivery_id in
832: ( select md.delivery_id
833: from mst_delivery_legs mdl1
829: from mst_delivery_legs mdl
830: where mdl.plan_id = l_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
849: --Bug_Fix for 3696518 - II
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
913: + nvl(mdl.allocated_transport_cost,0)
914: + nvl(mdl.allocated_fac_shp_hand_cost,0)
915: + nvl(mdl.allocated_fac_rec_hand_cost,0)),0)
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
930: + nvl(mdl.allocated_fac_unloading_cost,0)
931: + nvl(mdl.allocated_transport_cost,0)
932: + nvl(mdl.allocated_fac_shp_hand_cost,0)
933: + nvl(mdl.allocated_fac_rec_hand_cost,0)),0)
934: from mst_delivery_legs mdl
935: where mdl.plan_id = l_plan_id
936: and mdl.delivery_id in
937: ( select md.delivery_id
938: from mst_delivery_legs mdl1
934: from mst_delivery_legs mdl
935: where mdl.plan_id = l_plan_id
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
959: + nvl(mdl.allocated_transport_cost,0)
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
982: + nvl(mdl.allocated_fac_unloading_cost,0)
983: + nvl(mdl.allocated_transport_cost,0)
984: + nvl(mdl.allocated_fac_shp_hand_cost,0)
985: + nvl(mdl.allocated_fac_rec_hand_cost,0)),0)
986: from mst_delivery_legs mdl
987: where mdl.plan_id = l_plan_id
988: and mdl.delivery_id in
989: ( select md.delivery_id
990: from mst_delivery_legs mdl1
986: from mst_delivery_legs mdl
987: where mdl.plan_id = l_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
1009: + nvl(mdl.allocated_transport_cost,0)
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
1123: and mt.plan_id= mts.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
1143: and mt.trip_id = mts.trip_id
1144: and mt.mode_of_transport = 'TRUCK'
1145: and mts.plan_id = l_plan_id
1146: and mt.trip_id in (select distinct mdl.trip_id
1147: from mst_delivery_legs mdl
1148: , mst_deliveries md
1149: where mdl.plan_id = mt.plan_id
1150: and md.plan_id = mdl.plan_id
1151: and md.delivery_id = mdl.delivery_id
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
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
1207: where mt.plan_id = l_plan_id
1208: and mt.mode_of_transport = 'TRUCK'
1209: and mt.trip_id in (select distinct mdl.trip_id
1210: from mst_trip_stops mts
1211: , mst_delivery_legs mdl
1212: , mst_deliveries md
1213: where md.plan_id = mt.plan_id
1214: and decode(l_c_s_ident, 2, md.customer_id, md.supplier_id) = l_cust_supp_id
1215: and mts.plan_id = md.plan_id
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
1236: , mst_delivery_legs mdl
1237: where md.plan_id = l_plan_id
1238: and mdl.plan_id = md.plan_id
1239: and mdl.delivery_id = md.delivery_id
1240: and decode ( l_c_s_ident, 2, md.customer_id, md.supplier_id ) = l_cust_supp_id );
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
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
1361: where mt.plan_id = l_plan_id
1362: and mt.mode_of_transport = 'TRUCK'
1363: and mt.trip_id in (select distinct mdl.trip_id
1364: from mst_trip_stops mts
1365: , mst_delivery_legs mdl
1366: , mst_deliveries md
1367: where md.plan_id = mt.plan_id
1368: and decode(l_c_s_ident, 2, md.customer_id, md.supplier_id) = l_cust_supp_id
1369: and mts.plan_id = md.plan_id
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
1390: , mst_delivery_legs mdl
1391: where md.plan_id = l_plan_id
1392: and mdl.plan_id = md.plan_id
1393: and mdl.delivery_id = md.delivery_id
1394: and decode ( l_c_s_ident, 2, md.customer_id, md.supplier_id ) = l_cust_supp_id );
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
1467: and ( md.dropoff_location_id = flp.location_id
1483: and mt.carrier_id = l_carrier_id
1484: and mt.trip_id in
1485: (select distinct mts.trip_id
1486: from mst_trip_stops mts
1487: , mst_delivery_legs mdl
1488: , mst_deliveries md
1489: where md.plan_id = mt.plan_id
1490: and decode(l_c_s_ident, 2, md.customer_id, md.supplier_id) = l_cust_supp_id
1491: and mts.plan_id = md.plan_id
1504: */
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
1547: cursor cur_carr_cost (l_plan_id in number
1548: , l_carrier_id in number) is
1549: select nvl(sum(nvl(mdl.allocated_transport_cost,0)),0)
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
1562: /*
1563: select nvl(sum(nvl(mdl.allocated_transport_cost,0)),0)
1564: from mst_trips mt
1565: , mst_trip_stops mts
1566: , mst_delivery_legs mdl
1567: , fte_location_parameters flp
1568: where mt.plan_id = l_plan_id
1569: and mt.plan_id = mts.plan_id
1570: and mt.trip_id = mts.trip_id
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
1588: and ( md.dropoff_location_id = flp.location_id
1599: , l_carrier_id in number) is
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
1612: */
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
1701: and mdd.plan_id = mda.plan_id
1702: and mdd.delivery_detail_id = mda.delivery_detail_id
1703: and mda.delivery_id in (select mdl.delivery_id
1704: from mst_deliveries md
1705: , mst_delivery_legs mdl
1706: , mst_trip_stops mts
1707: , fte_location_parameters flp
1708: , wsh_locations wl
1709: where mdl.plan_id = l_plan_id
1751: and md.delivery_id in
1752: (select mdl.delivery_id
1753: from mst_trips t
1754: , mst_trip_stops ts
1755: , mst_delivery_legs mdl
1756: , wsh_locations wl
1757: where mdl.plan_id = md.plan_id
1758: and ts.plan_id = mdl.plan_id
1759: and ts.stop_id = mdl.pick_up_stop_id
1829: from mst_deliveries md
1830: where md.plan_id = l_plan_id
1831: and md.delivery_id in (select mdl.delivery_id
1832: from mst_deliveries md
1833: , mst_delivery_legs mdl
1834: , mst_trip_stops mts
1835: , fte_location_parameters flp
1836: , wsh_locations wl
1837: where mdl.plan_id = l_plan_id
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
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
1935: from mst_deliveries md
1936: where md.plan_id = l_plan_id
1937: and md.delivery_id in (select mdl.delivery_id
1938: from mst_deliveries md
1939: , mst_delivery_legs mdl
1940: , mst_trip_stops mts
1941: , fte_location_parameters flp
1942: , wsh_locations wl
1943: where mdl.plan_id = l_plan_id
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
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
2041: from mst_deliveries md
2042: where md.plan_id = l_plan_id
2043: and md.delivery_id in (select mdl.delivery_id
2044: from mst_deliveries md
2045: , mst_delivery_legs mdl
2046: , mst_trip_stops mts
2047: , fte_location_parameters flp
2048: , wsh_locations wl
2049: where mdl.plan_id = l_plan_id
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
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
2127: , mst_trip_stops mts
2128: where mt.plan_id = l_plan_id
2129: and mt.trip_id in (select distinct mdl.trip_id
2130: from mst_deliveries md
2131: , mst_delivery_legs mdl
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
2155: , mst_trip_stops mts
2156: where mt.plan_id = l_plan_id
2157: and mt.trip_id in (select distinct mdl.trip_id
2158: from mst_deliveries md
2159: , mst_delivery_legs mdl
2160: , mst_trip_stops mts1
2161: , fte_location_parameters flp
2162: , wsh_locations wl
2163: where md.plan_id = mt.plan_id
2191: and mts.trip_id = mt.trip_id
2192: and mt.trip_id in
2193: (select distinct mdl.trip_id
2194: from mst_deliveries md
2195: , mst_delivery_legs mdl
2196: , mst_trip_stops mts1
2197: , wsh_locations wl
2198: where mdl.plan_id = mt.plan_id
2199: and mdl.pick_up_stop_id = mts1.stop_id
2218: where mt.plan_id = l_plan_id
2219: and mt.carrier_id = l_carrier_id
2220: and mt.trip_id in (select distinct mdl.trip_id
2221: from mst_deliveries md
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
2278: , mst_trip_stops mts
2279: where mt.plan_id = l_plan_id
2280: and mt.trip_id in (select distinct mdl.trip_id
2281: from mst_deliveries md
2282: , mst_delivery_legs mdl
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
2306: , mst_trip_stops mts
2307: where mt.plan_id = l_plan_id
2308: and mt.trip_id in (select distinct mdl.trip_id
2309: from mst_deliveries md
2310: , mst_delivery_legs mdl
2311: , mst_trip_stops mts1
2312: , fte_location_parameters flp
2313: , wsh_locations wl
2314: where md.plan_id = mt.plan_id
2342: and mts.trip_id = mt.trip_id
2343: and mt.trip_id in
2344: (select distinct mdl.trip_id
2345: from mst_deliveries md
2346: , mst_delivery_legs mdl
2347: , mst_trip_stops mts1
2348: , wsh_locations wl
2349: where mdl.plan_id = mt.plan_id
2350: and mdl.pick_up_stop_id = mts1.stop_id
2369: where mt.plan_id = l_plan_id
2370: and mt.carrier_id = l_carrier_id
2371: and mt.trip_id in (select distinct mdl.trip_id
2372: from mst_deliveries md
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
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
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
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
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
2477: and mt.mode_of_transport = 'LTL'
2478: and mt.trip_id in
2479: (select distinct mdl.trip_id
2480: from mst_deliveries md
2481: , mst_delivery_legs mdl
2482: , mst_trip_stops mts1
2483: , wsh_locations wl
2484: where mdl.plan_id = mt.plan_id
2485: and mdl.pick_up_stop_id = mts1.stop_id
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
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
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
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
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
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
2603: and mt.mode_of_transport = 'PARCEL'
2604: and mt.trip_id in
2605: (select distinct mdl.trip_id
2606: from mst_deliveries md
2607: , mst_delivery_legs mdl
2608: , mst_trip_stops mts1
2609: , wsh_locations wl
2610: where mdl.plan_id = mt.plan_id
2611: and mdl.pick_up_stop_id = mts1.stop_id
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
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
2682: + nvl(mdl.allocated_fac_unloading_cost,0)
2683: + nvl(mdl.allocated_transport_cost,0)
2684: + nvl(mdl.allocated_fac_shp_hand_cost,0)
2685: + nvl(mdl.allocated_fac_rec_hand_cost,0)),0)
2686: from mst_delivery_legs mdl
2687: where mdl.plan_id = l_plan_id
2688: and mdl.delivery_id in
2689: ( select md.delivery_id
2690: from mst_delivery_legs mdl1
2686: from mst_delivery_legs mdl
2687: where mdl.plan_id = l_plan_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
2710: + nvl(mdl.allocated_transport_cost,0)
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
2741: + nvl(mdl.allocated_fac_unloading_cost,0)
2742: + nvl(mdl.allocated_transport_cost,0)
2743: + nvl(mdl.allocated_fac_shp_hand_cost,0)
2744: + nvl(mdl.allocated_fac_rec_hand_cost,0)),0)
2745: from mst_delivery_legs mdl
2746: where mdl.plan_id = l_plan_id
2747: and mdl.delivery_id in
2748: ( select md.delivery_id
2749: from mst_delivery_legs mdl1
2745: from mst_delivery_legs mdl
2746: where mdl.plan_id = l_plan_id
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
2773: + nvl(mdl.allocated_transport_cost,0)
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
2799: + nvl(mdl.allocated_fac_unloading_cost,0)
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
2807: and mt.plan_id = mdl.plan_id
2808: and mt.trip_id = mdl.trip_id
2809: and mdl.delivery_id in (select mdl.delivery_id
2810: from mst_deliveries md
2811: , mst_delivery_legs mdl
2812: , mst_trip_stops mts
2813: , fte_location_parameters flp
2814: , wsh_locations wl
2815: where mdl.plan_id = l_plan_id
2834: + nvl(mdl.allocated_fac_unloading_cost,0)
2835: + nvl(mdl.allocated_transport_cost,0)
2836: + nvl(mdl.allocated_fac_shp_hand_cost,0)
2837: + nvl(mdl.allocated_fac_rec_hand_cost,0)),0)
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
2840: and mdl.delivery_id in
2841: ( select md.delivery_id
2842: from mst_trips mt
2843: , mst_trip_stops mts
2844: , mst_delivery_legs mdl1
2845: , mst_deliveries md
2846: , wsh_locations wl
2847: where mt.plan_id = mdl1.plan_id
2848: and mt.trip_id = mdl1.trip_id
2863: + nvl(mdl.allocated_transport_cost,0)
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
2904: -- and mt.continuous_move_id is null --check
2905: and mt.trip_id in
2906: (select distinct mts.trip_id
2907: from mst_trip_stops mts
2908: , mst_delivery_legs mdl
2909: , mst_deliveries md
2910: , wsh_locations wl
2911: where md.plan_id = mt.plan_id
2912: and mts.plan_id = md.plan_id
2923: + nvl(mdl.allocated_fac_shp_hand_cost,0)
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
3020: and mdd.plan_id = mda.plan_id
3021: and mdd.delivery_detail_id = mda.delivery_detail_id
3022: and mda.delivery_id in (select mdl.delivery_id
3023: from mst_deliveries md
3024: , mst_delivery_legs mdl
3025: , mst_trip_stops mts
3026: , fte_location_parameters flp
3027: , wsh_locations wl
3028: where mdl.plan_id = l_plan_id
3069: and md.delivery_id in
3070: (select mdl.delivery_id
3071: from mst_trips t
3072: , mst_trip_stops ts
3073: , mst_delivery_legs mdl
3074: , wsh_locations wl
3075: where mdl.plan_id = md.plan_id
3076: and ts.plan_id = mdl.plan_id
3077: and ts.stop_id = mdl.drop_off_stop_id
3147: from mst_deliveries md
3148: where md.plan_id = l_plan_id
3149: and md.delivery_id in (select mdl.delivery_id
3150: from mst_deliveries md
3151: , mst_delivery_legs mdl
3152: , mst_trip_stops mts
3153: , fte_location_parameters flp
3154: , wsh_locations wl
3155: where mdl.plan_id = l_plan_id
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
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
3253: from mst_deliveries md
3254: where md.plan_id = l_plan_id
3255: and md.delivery_id in (select mdl.delivery_id
3256: from mst_deliveries md
3257: , mst_delivery_legs mdl
3258: , mst_trip_stops mts
3259: , fte_location_parameters flp
3260: , wsh_locations wl
3261: where mdl.plan_id = l_plan_id
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
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
3359: from mst_deliveries md
3360: where md.plan_id = l_plan_id
3361: and md.delivery_id in (select mdl.delivery_id
3362: from mst_deliveries md
3363: , mst_delivery_legs mdl
3364: , mst_trip_stops mts
3365: , fte_location_parameters flp
3366: , wsh_locations wl
3367: where mdl.plan_id = l_plan_id
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
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
3445: , mst_trip_stops mts
3446: where mt.plan_id = l_plan_id
3447: and mt.trip_id in (select distinct mdl.trip_id
3448: from mst_deliveries md
3449: , mst_delivery_legs mdl
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
3473: , mst_trip_stops mts
3474: where mt.plan_id = l_plan_id
3475: and mt.trip_id in (select distinct mdl.trip_id
3476: from mst_deliveries md
3477: , mst_delivery_legs mdl
3478: , mst_trip_stops mts1
3479: , fte_location_parameters flp
3480: , wsh_locations wl
3481: where md.plan_id = mt.plan_id
3509: and mts.trip_id = mt.trip_id
3510: and mt.trip_id in
3511: (select distinct mdl.trip_id
3512: from mst_deliveries md
3513: , mst_delivery_legs mdl
3514: , mst_trip_stops mts1
3515: , wsh_locations wl
3516: where mdl.plan_id = mt.plan_id
3517: and mdl.drop_off_stop_id = mts1.stop_id
3536: where mt.plan_id = l_plan_id
3537: and mt.carrier_id = l_carrier_id
3538: and mt.trip_id in (select distinct mdl.trip_id
3539: from mst_deliveries md
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
3595: , mst_trip_stops mts
3596: where mt.plan_id = l_plan_id
3597: and mt.trip_id in (select distinct mdl.trip_id
3598: from mst_deliveries md
3599: , mst_delivery_legs mdl
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
3623: , mst_trip_stops mts
3624: where mt.plan_id = l_plan_id
3625: and mt.trip_id in (select distinct mdl.trip_id
3626: from mst_deliveries md
3627: , mst_delivery_legs mdl
3628: , mst_trip_stops mts1
3629: , fte_location_parameters flp
3630: , wsh_locations wl
3631: where md.plan_id = mt.plan_id
3659: and mts.trip_id = mt.trip_id
3660: and mt.trip_id in
3661: (select distinct mdl.trip_id
3662: from mst_deliveries md
3663: , mst_delivery_legs mdl
3664: , mst_trip_stops mts1
3665: , wsh_locations wl
3666: where mdl.plan_id = mt.plan_id
3667: and mdl.drop_off_stop_id = mts1.stop_id
3686: where mt.plan_id = l_plan_id
3687: and mt.carrier_id = l_carrier_id
3688: and mt.trip_id in (select distinct mdl.trip_id
3689: from mst_deliveries md
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
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
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
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
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
3794: and mt.mode_of_transport = 'LTL'
3795: and mt.trip_id in
3796: (select distinct mdl.trip_id
3797: from mst_deliveries md
3798: , mst_delivery_legs mdl
3799: , mst_trip_stops mts1
3800: , wsh_locations wl
3801: where mdl.plan_id = mt.plan_id
3802: and mdl.drop_off_stop_id = mts1.stop_id
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
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
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
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
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
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
3920: and mt.mode_of_transport = 'PARCEL'
3921: and mt.trip_id in
3922: (select distinct mdl.trip_id
3923: from mst_deliveries md
3924: , mst_delivery_legs mdl
3925: , mst_trip_stops mts1
3926: , wsh_locations wl
3927: where mdl.plan_id = mt.plan_id
3928: and mdl.drop_off_stop_id = mts1.stop_id
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
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
3999: + nvl(mdl.allocated_fac_unloading_cost,0)
4000: + nvl(mdl.allocated_transport_cost,0)
4001: + nvl(mdl.allocated_fac_shp_hand_cost,0)
4002: + nvl(mdl.allocated_fac_rec_hand_cost,0)),0)
4003: from mst_delivery_legs mdl
4004: where mdl.plan_id = l_plan_id
4005: and mdl.delivery_id in
4006: ( select md.delivery_id
4007: from mst_delivery_legs mdl1
4003: from mst_delivery_legs mdl
4004: where mdl.plan_id = l_plan_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
4027: + nvl(mdl.allocated_transport_cost,0)
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
4057: + nvl(mdl.allocated_fac_unloading_cost,0)
4058: + nvl(mdl.allocated_transport_cost,0)
4059: + nvl(mdl.allocated_fac_shp_hand_cost,0)
4060: + nvl(mdl.allocated_fac_rec_hand_cost,0)),0)
4061: from mst_delivery_legs mdl
4062: where mdl.plan_id = l_plan_id
4063: and mdl.delivery_id in
4064: ( select md.delivery_id
4065: from mst_delivery_legs mdl1
4061: from mst_delivery_legs mdl
4062: where mdl.plan_id = l_plan_id
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
4089: + nvl(mdl.allocated_transport_cost,0)
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
4115: + nvl(mdl.allocated_fac_unloading_cost,0)
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
4123: and mt.plan_id = mdl.plan_id
4124: and mt.trip_id = mdl.trip_id
4125: and mdl.delivery_id in (select mdl.delivery_id
4126: from mst_deliveries md
4127: , mst_delivery_legs mdl
4128: , mst_trip_stops mts
4129: , fte_location_parameters flp
4130: , wsh_locations wl
4131: where mdl.plan_id = l_plan_id
4150: + nvl(mdl.allocated_fac_unloading_cost,0)
4151: + nvl(mdl.allocated_transport_cost,0)
4152: + nvl(mdl.allocated_fac_shp_hand_cost,0)
4153: + nvl(mdl.allocated_fac_rec_hand_cost,0)),0)
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
4156: and mdl.delivery_id in
4157: ( select md.delivery_id
4158: from mst_trips mt
4159: , mst_trip_stops mts
4160: , mst_delivery_legs mdl1
4161: , mst_deliveries md
4162: , wsh_locations wl
4163: where mt.plan_id = mdl1.plan_id
4164: and mt.trip_id = mdl1.trip_id
4179: + nvl(mdl.allocated_transport_cost,0)
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
4220: -- and mt.continuous_move_id is null --check
4221: and mt.trip_id in
4222: (select distinct mts.trip_id
4223: from mst_trip_stops mts
4224: , mst_delivery_legs mdl
4225: , mst_deliveries md
4226: , wsh_locations wl
4227: where md.plan_id = mt.plan_id
4228: and mts.plan_id = md.plan_id
4239: + nvl(mdl.allocated_fac_shp_hand_cost,0)
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
4302: and md.delivery_id in
4303: (select mdl.delivery_id
4304: from -- mst_trips t -- Removing the join with mst_trips
4305: mst_trip_stops ts
4306: , mst_delivery_legs mdl
4307: , fte_location_parameters flp
4308: where mdl.plan_id = md.plan_id
4309: and ts.plan_id = mdl.plan_id
4310: -- and ts.stop_id = mdl.pick_up_stop_id
4340: and md.plan_id = l_plan_id
4341: and decode(l_c_s_ident, 2, md.customer_id, md.supplier_id) = l_cust_supp_id
4342: and md.delivery_id in
4343: (select mdl.delivery_id
4344: from mst_delivery_legs mdl
4345: , mst_trip_stops mts
4346: , fte_location_parameters flp
4347: where mdl.plan_id = md.plan_id
4348: and mdl.plan_id = mts.plan_id
4360: and mda.plan_id = mdd.plan_id
4361: and mda.delivery_detail_id = mdd.delivery_detail_id
4362: and md.plan_id = l_plan_id
4363: and decode(l_c_s_ident, 2, md.customer_id, md.supplier_id) = l_cust_supp_id
4364: and not exists (select 1 from mst_delivery_legs mdl
4365: where mdl.plan_id=md.plan_id
4366: and mdl.delivery_id = md.delivery_id)) dd;
4367:
4368: cursor cur_plan_orders_carr (l_plan_id in number
4377: and md.delivery_id in
4378: (select mdl.delivery_id
4379: from mst_trips t
4380: , mst_trip_stops ts
4381: , mst_delivery_legs mdl
4382: , fte_location_parameters flp
4383: where mdl.plan_id = md.plan_id
4384: and ts.plan_id = mdl.plan_id
4385: -- and ts.stop_id = mdl.pick_up_stop_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
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
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
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
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
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
4663: where mt.plan_id = l_plan_id
4664: and mt.trip_id in
4665: (select distinct mts.trip_id
4666: from mst_trip_stops mts
4667: , mst_delivery_legs mdl
4668: , mst_deliveries md
4669: , fte_location_parameters flp
4670: where md.plan_id = mt.plan_id
4671: and decode(l_c_s_ident, 2, md.customer_id, md.supplier_id) = l_cust_supp_id
4742: + nvl(mdl.allocated_fac_unloading_cost,0)
4743: + nvl(mdl.allocated_transport_cost,0)
4744: + nvl(mdl.allocated_fac_shp_hand_cost,0)
4745: + nvl(mdl.allocated_fac_rec_hand_cost,0)),0)
4746: from mst_delivery_legs mdl
4747: where mdl.plan_id = l_plan_id
4748: and mdl.delivery_id in
4749: ( select md.delivery_id
4750: from mst_delivery_legs mdl1
4746: from mst_delivery_legs mdl
4747: where mdl.plan_id = l_plan_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
4771: + nvl(mdl.allocated_transport_cost,0)
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
4795: + nvl(mdl.allocated_fac_unloading_cost,0)
4796: + nvl(mdl.allocated_transport_cost,0)
4797: + nvl(mdl.allocated_fac_shp_hand_cost,0)
4798: + nvl(mdl.allocated_fac_rec_hand_cost,0)),0)
4799: from mst_delivery_legs mdl
4800: where mdl.plan_id = l_plan_id
4801: and mdl.delivery_id in
4802: ( select md.delivery_id
4803: from mst_delivery_legs mdl1
4799: from mst_delivery_legs mdl
4800: where mdl.plan_id = l_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
4825: + nvl(mdl.allocated_transport_cost,0)
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
4860: -- and mt.continuous_move_id is null --check
4861: and mt.trip_id in
4862: (select distinct mts.trip_id
4863: from mst_trip_stops mts
4864: , mst_delivery_legs mdl
4865: , mst_deliveries md
4866: , fte_location_parameters flp
4867: where md.plan_id = mt.plan_id
4868: and mts.plan_id = md.plan_id
4881: /*
4882: select sum(nvl(mdl.allocated_fac_loading_cost,0) +
4883: nvl(mdl.allocated_transport_cost,0) +
4884: nvl(mdl.allocated_fac_shp_hand_cost,0) )
4885: from mst_delivery_legs mdl
4886: where mdl.plan_id = l_plan_id
4887: and mdl.delivery_id in
4888: ( select md.delivery_id
4889: from mst_delivery_legs mdl1,
4885: from mst_delivery_legs mdl
4886: where mdl.plan_id = l_plan_id
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
4909: + nvl(mdl.allocated_transport_cost,0)
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
4934: /*
4935: select sum(nvl(mdl.allocated_fac_loading_cost,0) +
4936: nvl(mdl.allocated_transport_cost,0) +
4937: nvl(mdl.allocated_fac_shp_hand_cost,0) )
4938: from mst_delivery_legs mdl
4939: where mdl.plan_id = l_plan_id
4940: and mdl.delivery_id in
4941: ( select md.delivery_id
4942: from mst_delivery_legs mdl1,
4938: from mst_delivery_legs mdl
4939: where mdl.plan_id = l_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
4964: + nvl(mdl.allocated_transport_cost,0)
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
5037: and md.delivery_id in
5038: (select mdl.delivery_id
5039: from -- mst_trips t -- removing this join
5040: mst_trip_stops ts
5041: , mst_delivery_legs mdl
5042: , fte_location_parameters flp
5043: where mdl.plan_id = md.plan_id
5044: and ts.plan_id = mdl.plan_id
5045: -- and ts.stop_id = mdl.pick_up_stop_id
5077: and decode(l_c_s_ident, 2, md.customer_id, md.supplier_id) = l_cust_supp_id
5078: and decode ( l_report_for, 2, md.customer_id, 4, md.supplier_id, 0 ) = decode ( l_report_for, 2, l_report_for_id, 4, l_report_for_id, 0 )
5079: and md.delivery_id in
5080: (select mdl.delivery_id
5081: from mst_delivery_legs mdl
5082: , mst_trip_stops mts
5083: where mdl.plan_id = md.plan_id
5084: and mdl.plan_id = mts.plan_id
5085: and ( mdl.pick_up_stop_id = mts.stop_id
5095: and mda.delivery_detail_id = mdd.delivery_detail_id
5096: and md.plan_id = l_plan_id
5097: and decode(l_c_s_ident, 2, md.customer_id, md.supplier_id) = l_cust_supp_id
5098: and decode ( l_report_for, 2, md.customer_id, 4, md.supplier_id, 0 ) = decode ( l_report_for, 2, l_report_for_id, 4, l_report_for_id, 0 )
5099: and not exists (select 1 from mst_delivery_legs mdl
5100: where mdl.plan_id=md.plan_id
5101: and mdl.delivery_id = md.delivery_id)) dd;
5102:
5103: cursor cur_plan_orders_carr (l_plan_id in number
5117: and md.plan_id = l_plan_id
5118: and decode(l_c_s_ident, 2, md.customer_id, md.supplier_id) = l_cust_supp_id
5119: and md.delivery_id in
5120: (select mdl.delivery_id
5121: from mst_delivery_legs mdl
5122: , mst_trip_stops mts
5123: , mst_trips mt
5124: where mdl.plan_id = md.plan_id
5125: and mdl.plan_id = mts.plan_id
5194: from mst_deliveries md
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
5266: from mst_deliveries md
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
5337: from mst_deliveries md
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
5387: where mt.plan_id = l_plan_id
5388: and mt.mode_of_transport = l_mode
5389: and flp.location_id in (select distinct mts.stop_location_id
5390: from mst_trip_stops mts
5391: , mst_delivery_legs mdl
5392: , mst_deliveries md
5393: where mts.plan_id = mt.plan_id
5394: and mts.trip_id = mt.trip_id
5395: and mdl.plan_id = mts.plan_id
5410: where mt.plan_id = l_plan_id
5411: and mt.trip_id in
5412: (select distinct mts.trip_id
5413: from mst_trip_stops mts
5414: , mst_delivery_legs mdl
5415: , mst_deliveries md
5416: where md.plan_id = mt.plan_id
5417: and decode ( l_report_for, 2, md.customer_id, 4, md.supplier_id, 0 ) = decode ( l_report_for, 2, l_report_for_id, 4, l_report_for_id, 0 )
5418: and decode(l_c_s_ident, 2, md.customer_id, md.supplier_id) = l_cust_supp_id
5442: and mt.carrier_id = l_carrier_id
5443: and mt.trip_id in
5444: (select distinct mts.trip_id
5445: from mst_trip_stops mts
5446: , mst_delivery_legs mdl
5447: , mst_deliveries md
5448: where md.plan_id = mt.plan_id
5449: and decode(l_c_s_ident, 2, md.customer_id, md.supplier_id) = l_cust_supp_id
5450: and mts.plan_id = md.plan_id
5503: + nvl(mdl.allocated_fac_unloading_cost,0)
5504: + nvl(mdl.allocated_transport_cost,0)
5505: + nvl(mdl.allocated_fac_shp_hand_cost,0)
5506: + nvl(mdl.allocated_fac_rec_hand_cost,0)),0)
5507: from mst_delivery_legs mdl
5508: where mdl.plan_id = l_plan_id
5509: and mdl.delivery_id in
5510: ( select md.delivery_id
5511: from mst_delivery_legs mdl1
5507: from mst_delivery_legs mdl
5508: where mdl.plan_id = l_plan_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
5533: + nvl(mdl.allocated_transport_cost,0)
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
5559: + nvl(mdl.allocated_fac_unloading_cost,0)
5560: + nvl(mdl.allocated_transport_cost,0)
5561: + nvl(mdl.allocated_fac_shp_hand_cost,0)
5562: + nvl(mdl.allocated_fac_rec_hand_cost,0)),0)
5563: from mst_delivery_legs mdl
5564: where mdl.plan_id = l_plan_id
5565: and mdl.delivery_id in
5566: ( select md.delivery_id
5567: from mst_delivery_legs mdl1
5563: from mst_delivery_legs mdl
5564: where mdl.plan_id = l_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
5587: + nvl(mdl.allocated_transport_cost,0)
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
5620: -- and mt.continuous_move_id is null --check
5621: and mt.trip_id in
5622: (select distinct mts.trip_id
5623: from mst_trip_stops mts
5624: , mst_delivery_legs mdl
5625: , mst_deliveries md
5626: where md.plan_id = mt.plan_id
5627: and mts.plan_id = md.plan_id
5628: and mdl.plan_id = md.plan_id
5637: + nvl(mdl.allocated_fac_shp_hand_cost,0)
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
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
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
5719: , mst_trip_stops mts
5720: WHERE mt.plan_id = l_plan_id
5721: AND mt.trip_id in (SELECT DISTINCT mdl.trip_id
5722: FROM mst_deliveries md
5723: , mst_delivery_legs mdl
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
5752: where mt.plan_id = l_plan_id
5753: and mt.trip_id in
5754: (select distinct mts.trip_id
5755: from mst_trip_stops mts
5756: , mst_delivery_legs mdl
5757: , mst_deliveries md
5758: where md.plan_id = mt.plan_id
5759: and decode ( l_report_for, 2, md.customer_id, 4, md.supplier_id, 0 ) = decode ( l_report_for, 2, l_report_for_id, 4, l_report_for_id, 0 )
5760: and decode(l_c_s_ident, 2, md.customer_id, md.supplier_id) = l_cust_supp_id
5783: and mt.carrier_id = l_carrier_id
5784: and mt.trip_id in
5785: (select distinct mts.trip_id
5786: from mst_trip_stops mts
5787: , mst_delivery_legs mdl
5788: , mst_deliveries md
5789: where md.plan_id = mt.plan_id
5790: and decode(l_c_s_ident, 2, md.customer_id, md.supplier_id) = l_cust_supp_id
5791: and mts.plan_id = md.plan_id
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
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
5873: , mst_trip_stops mts
5874: where mt.plan_id = l_plan_id
5875: and mt.trip_id in (select distinct mdl.trip_id
5876: from mst_deliveries md
5877: , mst_delivery_legs mdl
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
5906: where mt.plan_id = l_plan_id
5907: and mt.trip_id in
5908: (select distinct mts.trip_id
5909: from mst_trip_stops mts
5910: , mst_delivery_legs mdl
5911: , mst_deliveries md
5912: where md.plan_id = mt.plan_id
5913: and decode ( l_report_for, 2, md.customer_id, 4, md.supplier_id, 0 ) = decode ( l_report_for, 2, l_report_for_id, 4, l_report_for_id, 0 )
5914: and decode(l_c_s_ident, 2, md.customer_id, md.supplier_id) = l_cust_supp_id
5937: and mt.carrier_id = l_carrier_id
5938: and mt.trip_id in
5939: (select distinct mts.trip_id
5940: from mst_trip_stops mts
5941: , mst_delivery_legs mdl
5942: , mst_deliveries md
5943: where md.plan_id = mt.plan_id
5944: and decode(l_c_s_ident, 2, md.customer_id, md.supplier_id) = l_cust_supp_id
5945: and mts.plan_id = md.plan_id