[Home] [Help]
28: cursor cur_plan_orders_myfac (l_plan_id in number
29: , l_myfac_id in number) is
30: select count(distinct mdd.source_header_number)
31: from mst_delivery_details mdd
32: , mst_deliveries md
33: , mst_delivery_assignments mda
34: where md.plan_id = mda.plan_id
35: and md.delivery_id = mda.delivery_id
36: and md.delivery_id in
60: select count(distinct dd.source_header_number)
61: from (
62: select mdd.source_header_number
63: from mst_delivery_details mdd
64: , mst_deliveries md
65: , mst_delivery_assignments mda
66: where md.plan_id = mda.plan_id
67: and md.delivery_id = mda.delivery_id
68: and mda.plan_id = mdd.plan_id
79: or mdl.drop_off_stop_id = mts.stop_id))
80: union all
81: select mdd.source_header_number
82: from mst_delivery_details mdd
83: , mst_deliveries md
84: , mst_delivery_assignments mda
85: where md.plan_id = mda.plan_id
86: and md.delivery_id = mda.delivery_id
87: and mda.plan_id = mdd.plan_id
96: , l_carrier_id in number) is
97: select count(distinct nvl(mdd.split_from_delivery_detail_id, mdd.delivery_detail_id))
98: from mst_delivery_details mdd
99: , mst_delivery_assignments mda
100: , mst_deliveries md
101: where mdd.plan_id = l_plan_id
102: and mdd.plan_id = mda.plan_id
103: and mdd.delivery_detail_id = mda.delivery_detail_id
104: and mda.parent_delivery_detail_id is null
146: l_order_groups number;
147:
148: cursor cur_order_groups (l_plan_id in number) is
149: select count(*)
150: from mst_deliveries md
151: where md.plan_id = l_plan_id;
152:
153:
154: cursor cur_order_groups_myfac (l_plan_id in number
153:
154: cursor cur_order_groups_myfac (l_plan_id in number
155: , l_myfac_id in number) is
156: select count(*)
157: from mst_deliveries md
158: , fte_location_parameters flp
159: where md.plan_id = l_plan_id
160: and (md.pickup_location_id = flp.location_id
161: or md.dropoff_location_id = flp.location_id)
165: cursor cur_order_groups_c_s (l_plan_id in number
166: , l_c_s_ident in number
167: , l_cust_supp_id in number) is
168: select count(*)
169: from mst_deliveries md
170: where md.plan_id = l_plan_id
171: and decode(l_c_s_ident, 2, md.customer_id, md.supplier_id) = l_cust_supp_id;
172:
173: cursor cur_order_groups_carr (l_plan_id in number
172:
173: cursor cur_order_groups_carr (l_plan_id in number
174: , l_carrier_id in number) is
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
223:
224: cursor cur_weight_myfac (l_plan_id in number
225: , l_myfac_id in number) is
226: select nvl(sum(nvl(md.gross_weight,0)),0)
227: from mst_deliveries md
228: , fte_location_parameters flp
229: where md.plan_id = l_plan_id
230: and (md.pickup_location_id = flp.location_id
231: or md.dropoff_location_id = flp.location_id)
235: cursor cur_weight_c_s (l_plan_id in number
236: , l_c_s_ident in number
237: , l_cust_supp_id in number) is
238: select nvl(sum(nvl(md.gross_weight,0)),0)
239: from mst_deliveries md
240: where md.plan_id = l_plan_id
241: and decode(l_c_s_ident, 2, md.customer_id, md.supplier_id) = l_cust_supp_id;
242:
243: cursor cur_weight_carr (l_plan_id in number
242:
243: cursor cur_weight_carr (l_plan_id in number
244: , l_carrier_id in number) is
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
293:
294: cursor cur_volume_myfac (l_plan_id in number
295: , l_myfac_id in number) is
296: select nvl(sum(nvl(md.volume,0)),0)
297: from mst_deliveries md
298: , fte_location_parameters flp
299: where md.plan_id = l_plan_id
300: and (md.pickup_location_id = flp.location_id
301: or md.dropoff_location_id = flp.location_id)
305: cursor cur_volume_c_s (l_plan_id in number
306: , l_c_s_ident in number
307: , l_cust_supp_id in number) is
308: select nvl(sum(nvl(md.volume,0)),0)
309: from mst_deliveries md
310: where md.plan_id = l_plan_id
311: and decode(l_c_s_ident, 2, md.customer_id, md.supplier_id) = l_cust_supp_id;
312:
313: cursor cur_volume_carr (l_plan_id in number
312:
313: cursor cur_volume_carr (l_plan_id in number
314: , l_carrier_id in number) is
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
363:
364: cursor cur_pieces_myfac (l_plan_id in number
365: , l_myfac_id in number) is
366: select nvl(sum(nvl(md.number_of_pieces,0)),0)
367: from mst_deliveries md
368: , fte_location_parameters flp
369: where md.plan_id = l_plan_id
370: and (md.pickup_location_id = flp.location_id
371: or md.dropoff_location_id = flp.location_id)
375: cursor cur_pieces_c_s (l_plan_id in number
376: , l_c_s_ident in number
377: , l_cust_supp_id in number) is
378: select nvl(sum(nvl(md.number_of_pieces,0)),0)
379: from mst_deliveries md
380: where md.plan_id = l_plan_id
381: and decode(l_c_s_ident, 2, md.customer_id, md.supplier_id) = l_cust_supp_id;
382:
383: cursor cur_pieces_carr (l_plan_id in number
382:
383: cursor cur_pieces_carr (l_plan_id in number
384: , l_carrier_id in number) is
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
427: cursor cur_plan_value (l_plan_id in number) is
428: select sum(nvl(mdd.unit_price,0)* nvl(mdd.requested_quantity,0))
429: from mst_delivery_details mdd
430: , mst_delivery_assignments mda
431: , mst_deliveries md
432: where mdd.plan_id = l_plan_id
433: and mda.plan_id = mdd.plan_id
434: and mda.delivery_detail_id = mdd.delivery_detail_id
435: and mda.parent_delivery_detail_id is null
440: , l_myfac_id in number) is
441: select sum(nvl(mdd.unit_price,0)* nvl(mdd.requested_quantity,0))
442: from mst_delivery_details mdd
443: , mst_delivery_assignments mda
444: , mst_deliveries md
445: , fte_location_parameters flp
446: where mdd.plan_id = l_plan_id
447: and mda.plan_id = mdd.plan_id
448: and mda.delivery_detail_id = mdd.delivery_detail_id
458: , l_cust_supp_id in number) is
459: select sum(nvl(mdd.unit_price,0)* nvl(mdd.requested_quantity,0))
460: from mst_delivery_details mdd
461: , mst_delivery_assignments mda
462: , mst_deliveries md
463: where mdd.plan_id = l_plan_id
464: and mda.plan_id = mdd.plan_id
465: and mda.delivery_detail_id = mdd.delivery_detail_id
466: and mda.parent_delivery_detail_id is null
472: , l_carrier_id in number) is
473: select sum(nvl(mdd.unit_price,0)* nvl(mdd.requested_quantity,0))
474: from mst_delivery_details mdd
475: , mst_delivery_assignments mda
476: , mst_deliveries md
477: where mdd.plan_id = l_plan_id
478: and mdd.plan_id = mda.plan_id
479: and mdd.delivery_detail_id = mda.delivery_detail_id
480: and mda.parent_delivery_detail_id is null
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
559: and mts.stop_location_id = md.dropoff_location_id
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
639: where mt.plan_id = mdl1.plan_id
652: --Bug_Fix for 3696518 - II
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
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
688: and mt.trip_id = mdl1.trip_id
699: --Bug_Fix for 3696518 - II
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
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
792: where mt.plan_id = mdl1.plan_id
804: */
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
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
838: and mt.trip_id = mdl1.trip_id
848: */
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
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
943: where mt.plan_id = mdl1.plan_id
958: + nvl(mdl.allocated_fac_unloading_cost,0)
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
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
995: and mt.trip_id = mdl1.trip_id
1008: + nvl(mdl.allocated_fac_unloading_cost,0)
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
1122: and mts.trip_id = mt.trip_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
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
1152: and decode(l_c_s_ident, 2, md.customer_id, md.supplier_id) = l_cust_supp_id);
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
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
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
1216: and mts.stop_location_id = md.dropoff_location_id
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
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
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
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
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
1370: and mts.stop_location_id = md.dropoff_location_id
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
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
1457: and flp.facility_id = l_myfac_id;
1458: */
1459: --Bug_Fix for 3696518 - II
1460: select count ( distinct mt.trip_id )
1461: from mst_deliveries md
1462: , fte_location_parameters flp
1463: , mst_delivery_legs mdl
1464: , mst_trips mt
1465: where md.plan_id = l_plan_id
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
1492: and mts.stop_location_id = md.dropoff_location_id
1503: group by ts.trip_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
1578: and flp.facility_id = l_myfac_id;
1579: */
1580: --Bug_Fix for 3696518 - II
1581: select sum ( nvl ( mdl.allocated_transport_cost, 0 ) )
1582: from mst_deliveries md
1583: , fte_location_parameters flp
1584: , mst_delivery_legs mdl
1585: , mst_trips mt
1586: where md.plan_id = l_plan_id
1598: , l_cust_supp_id in number
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
1611: and mt.carrier_id = l_carrier_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
1659: -- select count(distinct nvl(mdd.split_from_delivery_detail_id, mdd.delivery_detail_id))
1660: select count(distinct mdd.source_header_number)
1661: from mst_delivery_details mdd
1662: , mst_delivery_assignments mda
1663: , mst_deliveries md
1664: , wsh_locations wl
1665: where md.plan_id = mda.plan_id
1666: and md.delivery_id = mda.delivery_id
1667: and md.pickup_location_id = wl.wsh_location_id
1678: -- select count(distinct nvl(mdd.split_from_delivery_detail_id, mdd.delivery_detail_id))
1679: select count(distinct mdd.source_header_number)
1680: from mst_delivery_details mdd
1681: , mst_delivery_assignments mda
1682: , mst_deliveries md
1683: , fte_location_parameters flp
1684: , wsh_locations wl
1685: where md.plan_id = mda.plan_id
1686: and md.delivery_id = mda.delivery_id
1700: where mdd.plan_id = l_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
1725: -- select count(distinct nvl(mdd.split_from_delivery_detail_id, mdd.delivery_detail_id))
1726: select count(distinct mdd.source_header_number)
1727: from mst_delivery_details mdd
1728: , mst_delivery_assignments mda
1729: , mst_deliveries md
1730: , wsh_locations wl
1731: where md.plan_id = mda.plan_id
1732: and md.delivery_id = mda.delivery_id
1733: and md.pickup_location_id = wl.wsh_location_id
1743: , l_carrier_id in number
1744: , l_orig_state in varchar2) is
1745: select count(distinct mdd.source_header_number)
1746: from mst_delivery_details mdd
1747: , mst_deliveries md
1748: , mst_delivery_assignments mda
1749: where md.plan_id = mda.plan_id
1750: and md.delivery_id = mda.delivery_id
1751: and md.delivery_id in
1803:
1804: cursor cur_weight_orig (l_plan_id in number
1805: , l_orig_state in varchar2) is
1806: select nvl(sum(nvl(md.gross_weight,0)),0)
1807: from mst_deliveries md
1808: , wsh_locations wl
1809: where md.plan_id = l_plan_id
1810: and md.pickup_location_id = wl.wsh_location_id
1811: and wl.state = l_orig_state;
1814: , l_myfac_id in number
1815: , l_orig_state in varchar2) is
1816: /*
1817: select nvl(sum(nvl(md.gross_weight,0)),0)
1818: from mst_deliveries md
1819: , fte_location_parameters flp
1820: , wsh_locations wl
1821: where md.plan_id = l_plan_id
1822: and md.pickup_location_id = wl.wsh_location_id
1825: and flp.facility_id = l_myfac_id;
1826: */
1827: --Bug_Fix for 3696518 - II
1828: select nvl(sum(nvl(md.gross_weight,0)),0)
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
1828: select nvl(sum(nvl(md.gross_weight,0)),0)
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
1850: , l_c_s_ident in number
1851: , l_cust_supp_id in number
1852: , l_orig_state in varchar2) is
1853: select nvl(sum(nvl(md.gross_weight,0)),0)
1854: from mst_deliveries md
1855: , wsh_locations wl
1856: where md.plan_id = l_plan_id
1857: and md.pickup_location_id = wl.wsh_location_id
1858: and wl.state = l_orig_state
1861: cursor cur_weight_orig_carr (l_plan_id in number
1862: , l_carrier_id in number
1863: , l_orig_state in varchar2) is
1864: select nvl(sum(nvl(md.gross_weight,0)),0)
1865: from mst_deliveries md
1866: , wsh_locations wl
1867: where md.plan_id = l_plan_id
1868: and md.pickup_location_id = wl.wsh_location_id
1869: and wl.state = l_orig_state
1909:
1910: cursor cur_volume_orig (l_plan_id in number
1911: , l_orig_state in varchar2) is
1912: select nvl(sum(nvl(md.volume,0)),0)
1913: from mst_deliveries md
1914: , wsh_locations wl
1915: where md.plan_id = l_plan_id
1916: and md.pickup_location_id = wl.wsh_location_id
1917: and wl.state = l_orig_state;
1920: , l_myfac_id in number
1921: , l_orig_state in varchar2) is
1922: /*
1923: select nvl(sum(nvl(md.volume,0)),0)
1924: from mst_deliveries md
1925: , fte_location_parameters flp
1926: , wsh_locations wl
1927: where md.plan_id = l_plan_id
1928: and md.pickup_location_id = wl.wsh_location_id
1931: and flp.facility_id = l_myfac_id;
1932: */
1933: --Bug_Fix for 3693518
1934: select nvl(sum(nvl(md.volume,0)),0)
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
1934: select nvl(sum(nvl(md.volume,0)),0)
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
1956: , l_c_s_ident in number
1957: , l_cust_supp_id in number
1958: , l_orig_state in varchar2) is
1959: select nvl(sum(nvl(md.volume,0)),0)
1960: from mst_deliveries md
1961: , wsh_locations wl
1962: where md.plan_id = l_plan_id
1963: and md.pickup_location_id = wl.wsh_location_id
1964: and wl.state = l_orig_state
1967: cursor cur_volume_orig_carr (l_plan_id in number
1968: , l_carrier_id in number
1969: , l_orig_state in varchar2) is
1970: select nvl(sum(nvl(md.volume,0)),0)
1971: from mst_deliveries md
1972: , wsh_locations wl
1973: where md.plan_id = l_plan_id
1974: and md.pickup_location_id = wl.wsh_location_id
1975: and wl.state = l_orig_state
2015:
2016: cursor cur_pieces_orig (l_plan_id in number
2017: , l_orig_state in varchar2) is
2018: select nvl(sum(nvl(md.number_of_pieces,0)),0)
2019: from mst_deliveries md
2020: , wsh_locations wl
2021: where md.plan_id = l_plan_id
2022: and md.pickup_location_id = wl.wsh_location_id
2023: and wl.state = l_orig_state;
2026: , l_myfac_id in number
2027: , l_orig_state in varchar2) is
2028: /*
2029: select nvl(sum(nvl(md.number_of_pieces,0)),0)
2030: from mst_deliveries md
2031: , fte_location_parameters flp
2032: , wsh_locations wl
2033: where md.plan_id = l_plan_id
2034: and md.pickup_location_id = wl.wsh_location_id
2037: and flp.facility_id = l_myfac_id;
2038: */
2039: --Bug_Fix for 3696518
2040: select nvl(sum(nvl(md.number_of_pieces,0)),0)
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
2040: select nvl(sum(nvl(md.number_of_pieces,0)),0)
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
2062: , l_c_s_ident in number
2063: , l_cust_supp_id in number
2064: , l_orig_state in varchar2) is
2065: select nvl(sum(nvl(md.number_of_pieces,0)),0)
2066: from mst_deliveries md
2067: , wsh_locations wl
2068: where md.plan_id = l_plan_id
2069: and md.pickup_location_id = wl.wsh_location_id
2070: and wl.state = l_orig_state
2073: cursor cur_pieces_orig_carr (l_plan_id in number
2074: , l_carrier_id in number
2075: , l_orig_state in varchar2) is
2076: select nvl(sum(nvl(md.number_of_pieces,0)),0)
2077: from mst_deliveries md
2078: , wsh_locations wl
2079: where md.plan_id = l_plan_id
2080: and md.pickup_location_id = wl.wsh_location_id
2081: and wl.state = l_orig_state
2126: from mst_trips mt
2127: , mst_trip_stops mts
2128: where mt.plan_id = l_plan_id
2129: and mt.trip_id in (select distinct mdl.trip_id
2130: from mst_deliveries md
2131: , mst_delivery_legs mdl
2132: , mst_trip_stops mts1
2133: , wsh_locations wl
2134: where md.plan_id = mt.plan_id
2154: from mst_trips mt
2155: , mst_trip_stops mts
2156: where mt.plan_id = l_plan_id
2157: and mt.trip_id in (select distinct mdl.trip_id
2158: from mst_deliveries md
2159: , mst_delivery_legs mdl
2160: , mst_trip_stops mts1
2161: , fte_location_parameters flp
2162: , wsh_locations wl
2190: and mts.plan_id = mt.plan_id
2191: and mts.trip_id = mt.trip_id
2192: and mt.trip_id in
2193: (select distinct mdl.trip_id
2194: from mst_deliveries md
2195: , mst_delivery_legs mdl
2196: , mst_trip_stops mts1
2197: , wsh_locations wl
2198: where mdl.plan_id = mt.plan_id
2217: , mst_trip_stops mts
2218: where mt.plan_id = l_plan_id
2219: and mt.carrier_id = l_carrier_id
2220: and mt.trip_id in (select distinct mdl.trip_id
2221: from mst_deliveries md
2222: , mst_delivery_legs mdl
2223: , mst_trip_stops mts1
2224: , wsh_locations wl
2225: where md.plan_id = mt.plan_id
2277: from mst_trips mt
2278: , mst_trip_stops mts
2279: where mt.plan_id = l_plan_id
2280: and mt.trip_id in (select distinct mdl.trip_id
2281: from mst_deliveries md
2282: , mst_delivery_legs mdl
2283: , mst_trip_stops mts1
2284: , wsh_locations wl
2285: where md.plan_id = mt.plan_id
2305: from mst_trips mt
2306: , mst_trip_stops mts
2307: where mt.plan_id = l_plan_id
2308: and mt.trip_id in (select distinct mdl.trip_id
2309: from mst_deliveries md
2310: , mst_delivery_legs mdl
2311: , mst_trip_stops mts1
2312: , fte_location_parameters flp
2313: , wsh_locations wl
2341: and mts.plan_id = mt.plan_id
2342: and mts.trip_id = mt.trip_id
2343: and mt.trip_id in
2344: (select distinct mdl.trip_id
2345: from mst_deliveries md
2346: , mst_delivery_legs mdl
2347: , mst_trip_stops mts1
2348: , wsh_locations wl
2349: where mdl.plan_id = mt.plan_id
2368: , mst_trip_stops mts
2369: where mt.plan_id = l_plan_id
2370: and mt.carrier_id = l_carrier_id
2371: and mt.trip_id in (select distinct mdl.trip_id
2372: from mst_deliveries md
2373: , mst_delivery_legs mdl
2374: , mst_trip_stops mts1
2375: , wsh_locations wl
2376: where md.plan_id = mt.plan_id
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
2432: , mst_delivery_legs mdl
2433: , mst_trip_stops mts1
2434: , wsh_locations wl
2435: where md.plan_id = mt.plan_id
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
2454: , mst_delivery_legs mdl
2455: , mst_trip_stops mts1
2456: , fte_location_parameters flp
2457: , wsh_locations wl
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
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
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
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
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
2558: , mst_delivery_legs mdl
2559: , mst_trip_stops mts1
2560: , wsh_locations wl
2561: where md.plan_id = mt.plan_id
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
2580: , mst_delivery_legs mdl
2581: , mst_trip_stops mts1
2582: , fte_location_parameters flp
2583: , wsh_locations wl
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
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
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
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
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
2695: where mt.plan_id = mdl1.plan_id
2709: + nvl(mdl.allocated_fac_unloading_cost,0)
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
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
2754: , wsh_locations wl
2772: + nvl(mdl.allocated_fac_unloading_cost,0)
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
2806: and mt.mode_of_transport = l_mode
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
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
2849: and mt.mode_of_transport = l_mode
2862: + nvl(mdl.allocated_fac_unloading_cost,0)
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
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
2913: and mdl.plan_id = md.plan_id
2922: + nvl(mdl.allocated_fac_unloading_cost,0)
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
2978: -- select count(distinct nvl(mdd.split_from_delivery_detail_id, mdd.delivery_detail_id))
2979: select count(distinct mdd.source_header_number)
2980: from mst_delivery_details mdd
2981: , mst_delivery_assignments mda
2982: , mst_deliveries md
2983: , wsh_locations wl
2984: where md.plan_id = mda.plan_id
2985: and md.delivery_id = mda.delivery_id
2986: and md.dropoff_location_id = wl.wsh_location_id
2997: -- select count(distinct nvl(mdd.split_from_delivery_detail_id, mdd.delivery_detail_id))
2998: select count(distinct mdd.source_header_number)
2999: from mst_delivery_details mdd
3000: , mst_delivery_assignments mda
3001: , mst_deliveries md
3002: , fte_location_parameters flp
3003: , wsh_locations wl
3004: where md.plan_id = mda.plan_id
3005: and md.delivery_id = mda.delivery_id
3019: where mdd.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
3044: -- select count(distinct nvl(mdd.split_from_delivery_detail_id, mdd.delivery_detail_id))
3045: select count(distinct mdd.source_header_number)
3046: from mst_delivery_details mdd
3047: , mst_delivery_assignments mda
3048: , mst_deliveries md
3049: , wsh_locations wl
3050: where md.plan_id = mda.plan_id
3051: and md.delivery_id = mda.delivery_id
3052: and md.dropoff_location_id = wl.wsh_location_id
3061: , l_carrier_id in number
3062: , l_destination_state in varchar2) is
3063: select count(distinct mdd.source_header_number)
3064: from mst_delivery_details mdd
3065: , mst_deliveries md
3066: , mst_delivery_assignments mda
3067: where md.plan_id = mda.plan_id
3068: and md.delivery_id = mda.delivery_id
3069: and md.delivery_id in
3121:
3122: cursor cur_weight_dest (l_plan_id in number
3123: , l_destination_state in varchar2) is
3124: select nvl(sum(nvl(md.gross_weight,0)),0)
3125: from mst_deliveries md
3126: , wsh_locations wl
3127: where md.plan_id = l_plan_id
3128: and md.dropoff_location_id = wl.wsh_location_id
3129: and wl.state = l_destination_state;
3132: , l_myfac_id in number
3133: , l_destination_state in varchar2) is
3134: /*
3135: select nvl(sum(nvl(md.gross_weight,0)),0)
3136: from mst_deliveries md
3137: , fte_location_parameters flp
3138: , wsh_locations wl
3139: where md.plan_id = l_plan_id
3140: and md.dropoff_location_id = wl.wsh_location_id
3143: and flp.facility_id = l_myfac_id;
3144: */
3145: --Bug_Fix for 3696518 - II
3146: select nvl(sum(nvl(md.gross_weight,0)),0)
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
3146: select nvl(sum(nvl(md.gross_weight,0)),0)
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
3168: , l_c_s_ident in number
3169: , l_cust_supp_id in number
3170: , l_destination_state in varchar2) is
3171: select nvl(sum(nvl(md.gross_weight,0)),0)
3172: from mst_deliveries md
3173: , wsh_locations wl
3174: where md.plan_id = l_plan_id
3175: and md.dropoff_location_id = wl.wsh_location_id
3176: and wl.state = l_destination_state
3179: cursor cur_weight_dest_carr (l_plan_id in number
3180: , l_carrier_id in number
3181: , l_destination_state in varchar2) is
3182: select nvl(sum(nvl(md.gross_weight,0)),0)
3183: from mst_deliveries md
3184: , wsh_locations wl
3185: where md.plan_id = l_plan_id
3186: and md.dropoff_location_id = wl.wsh_location_id
3187: and wl.state = l_destination_state
3227:
3228: cursor cur_volume_dest (l_plan_id in number
3229: , l_destination_state in varchar2) is
3230: select nvl(sum(nvl(md.volume,0)),0)
3231: from mst_deliveries md
3232: , wsh_locations wl
3233: where md.plan_id = l_plan_id
3234: and md.dropoff_location_id = wl.wsh_location_id
3235: and wl.state = l_destination_state;
3238: , l_myfac_id in number
3239: , l_destination_state in varchar2) is
3240: /*
3241: select nvl(sum(nvl(md.volume,0)),0)
3242: from mst_deliveries md
3243: , fte_location_parameters flp
3244: , wsh_locations wl
3245: where md.plan_id = l_plan_id
3246: and md.dropoff_location_id = wl.wsh_location_id
3249: and flp.facility_id = l_myfac_id;
3250: */
3251: --Bug_Fix for 3696518 - II
3252: select nvl(sum(nvl(md.volume,0)),0)
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
3252: select nvl(sum(nvl(md.volume,0)),0)
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
3274: , l_c_s_ident in number
3275: , l_cust_supp_id in number
3276: , l_destination_state in varchar2) is
3277: select nvl(sum(nvl(md.volume,0)),0)
3278: from mst_deliveries md
3279: , wsh_locations wl
3280: where md.plan_id = l_plan_id
3281: and md.dropoff_location_id = wl.wsh_location_id
3282: and wl.state = l_destination_state
3285: cursor cur_volume_dest_carr (l_plan_id in number
3286: , l_carrier_id in number
3287: , l_destination_state in varchar2) is
3288: select nvl(sum(nvl(md.volume,0)),0)
3289: from mst_deliveries md
3290: , wsh_locations wl
3291: where md.plan_id = l_plan_id
3292: and md.dropoff_location_id = wl.wsh_location_id
3293: and wl.state = l_destination_state
3333:
3334: cursor cur_pieces_dest (l_plan_id in number
3335: , l_destination_state in varchar2) is
3336: select nvl(sum(nvl(md.number_of_pieces,0)),0)
3337: from mst_deliveries md
3338: , wsh_locations wl
3339: where md.plan_id = l_plan_id
3340: and md.dropoff_location_id = wl.wsh_location_id
3341: and wl.state = l_destination_state;
3344: , l_myfac_id in number
3345: , l_destination_state in varchar2) is
3346: /*
3347: select nvl(sum(nvl(md.number_of_pieces,0)),0)
3348: from mst_deliveries md
3349: , fte_location_parameters flp
3350: , wsh_locations wl
3351: where md.plan_id = l_plan_id
3352: and md.dropoff_location_id = wl.wsh_location_id
3355: and flp.facility_id = l_myfac_id;
3356: */
3357: --Bug_Fix for 3696518 - II
3358: select nvl(sum(nvl(md.number_of_pieces,0)),0)
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
3358: select nvl(sum(nvl(md.number_of_pieces,0)),0)
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
3380: , l_c_s_ident in number
3381: , l_cust_supp_id in number
3382: , l_destination_state in varchar2) is
3383: select nvl(sum(nvl(md.number_of_pieces,0)),0)
3384: from mst_deliveries md
3385: , wsh_locations wl
3386: where md.plan_id = l_plan_id
3387: and md.dropoff_location_id = wl.wsh_location_id
3388: and wl.state = l_destination_state
3391: cursor cur_pieces_dest_carr (l_plan_id in number
3392: , l_carrier_id in number
3393: , l_destination_state in varchar2) is
3394: select nvl(sum(nvl(md.number_of_pieces,0)),0)
3395: from mst_deliveries md
3396: , wsh_locations wl
3397: where md.plan_id = l_plan_id
3398: and md.dropoff_location_id = wl.wsh_location_id
3399: and wl.state = l_destination_state
3444: from mst_trips mt
3445: , mst_trip_stops mts
3446: where mt.plan_id = l_plan_id
3447: and mt.trip_id in (select distinct mdl.trip_id
3448: from mst_deliveries md
3449: , mst_delivery_legs mdl
3450: , mst_trip_stops mts1
3451: , wsh_locations wl
3452: where md.plan_id = mt.plan_id
3472: from mst_trips mt
3473: , mst_trip_stops mts
3474: where mt.plan_id = l_plan_id
3475: and mt.trip_id in (select distinct mdl.trip_id
3476: from mst_deliveries md
3477: , mst_delivery_legs mdl
3478: , mst_trip_stops mts1
3479: , fte_location_parameters flp
3480: , wsh_locations wl
3508: and mts.plan_id = mt.plan_id
3509: and mts.trip_id = mt.trip_id
3510: and mt.trip_id in
3511: (select distinct mdl.trip_id
3512: from mst_deliveries md
3513: , mst_delivery_legs mdl
3514: , mst_trip_stops mts1
3515: , wsh_locations wl
3516: where mdl.plan_id = mt.plan_id
3535: , mst_trip_stops mts
3536: where mt.plan_id = l_plan_id
3537: and mt.carrier_id = l_carrier_id
3538: and mt.trip_id in (select distinct mdl.trip_id
3539: from mst_deliveries md
3540: , mst_delivery_legs mdl
3541: , mst_trip_stops mts1
3542: , wsh_locations wl
3543: where md.plan_id = mt.plan_id
3594: from mst_trips mt
3595: , mst_trip_stops mts
3596: where mt.plan_id = l_plan_id
3597: and mt.trip_id in (select distinct mdl.trip_id
3598: from mst_deliveries md
3599: , mst_delivery_legs mdl
3600: , mst_trip_stops mts1
3601: , wsh_locations wl
3602: where md.plan_id = mt.plan_id
3622: from mst_trips mt
3623: , mst_trip_stops mts
3624: where mt.plan_id = l_plan_id
3625: and mt.trip_id in (select distinct mdl.trip_id
3626: from mst_deliveries md
3627: , mst_delivery_legs mdl
3628: , mst_trip_stops mts1
3629: , fte_location_parameters flp
3630: , wsh_locations wl
3658: and mts.plan_id = mt.plan_id
3659: and mts.trip_id = mt.trip_id
3660: and mt.trip_id in
3661: (select distinct mdl.trip_id
3662: from mst_deliveries md
3663: , mst_delivery_legs mdl
3664: , mst_trip_stops mts1
3665: , wsh_locations wl
3666: where mdl.plan_id = mt.plan_id
3685: , mst_trip_stops mts
3686: where mt.plan_id = l_plan_id
3687: and mt.carrier_id = l_carrier_id
3688: and mt.trip_id in (select distinct mdl.trip_id
3689: from mst_deliveries md
3690: , mst_delivery_legs mdl
3691: , mst_trip_stops mts1
3692: , wsh_locations wl
3693: where md.plan_id = mt.plan_id
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
3749: , mst_delivery_legs mdl
3750: , mst_trip_stops mts1
3751: , wsh_locations wl
3752: where md.plan_id = mt.plan_id
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
3771: , mst_delivery_legs mdl
3772: , mst_trip_stops mts1
3773: , fte_location_parameters flp
3774: , wsh_locations wl
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
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
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
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
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
3875: , mst_delivery_legs mdl
3876: , mst_trip_stops mts1
3877: , wsh_locations wl
3878: where md.plan_id = mt.plan_id
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
3897: , mst_delivery_legs mdl
3898: , mst_trip_stops mts1
3899: , fte_location_parameters flp
3900: , wsh_locations wl
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
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
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
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
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
4012: where mt.plan_id = mdl1.plan_id
4026: + nvl(mdl.allocated_fac_unloading_cost,0)
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
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
4070: , wsh_locations wl
4088: + nvl(mdl.allocated_fac_unloading_cost,0)
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
4122: and mt.mode_of_transport = l_mode
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
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
4165: and mt.mode_of_transport = l_mode
4178: + nvl(mdl.allocated_fac_unloading_cost,0)
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
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
4229: and mdl.plan_id = md.plan_id
4238: + nvl(mdl.allocated_fac_unloading_cost,0)
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
4294: cursor cur_plan_orders_myfac (l_plan_id in number
4295: , l_myfac_id in number) is
4296: select count(distinct mdd.source_header_number)
4297: from mst_delivery_details mdd
4298: , mst_deliveries md
4299: , mst_delivery_assignments mda
4300: where md.plan_id = mda.plan_id
4301: and md.delivery_id = mda.delivery_id
4302: and md.delivery_id in
4330: select count(distinct dd.source_header_number)
4331: from (
4332: select mdd.source_header_number
4333: from mst_delivery_details mdd
4334: , mst_deliveries md
4335: , mst_delivery_assignments mda
4336: where md.plan_id = mda.plan_id
4337: and md.delivery_id = mda.delivery_id
4338: and mda.plan_id = mdd.plan_id
4352: and flp.facility_id = l_myfac_id)
4353: union all
4354: select mdd.source_header_number
4355: from mst_delivery_details mdd
4356: , mst_deliveries md
4357: , mst_delivery_assignments mda
4358: where md.plan_id = mda.plan_id
4359: and md.delivery_id = mda.delivery_id
4360: and mda.plan_id = mdd.plan_id
4369: , l_myfac_id in number
4370: , l_carrier_id in number) is
4371: select count(distinct mdd.source_header_number)
4372: from mst_delivery_details mdd
4373: , mst_deliveries md
4374: , mst_delivery_assignments mda
4375: where md.plan_id = mda.plan_id
4376: and md.delivery_id = mda.delivery_id
4377: and md.delivery_id in
4428:
4429: cursor cur_weight_myfac (l_plan_id in number
4430: , l_myfac_id in number) is
4431: select nvl(sum(nvl(md.gross_weight,0)),0)
4432: from mst_deliveries md
4433: , fte_location_parameters flp
4434: where md.plan_id = l_plan_id
4435: and (md.pickup_location_id = flp.location_id
4436: or md.dropoff_location_id = flp.location_id)
4441: , l_myfac_id in number
4442: , l_c_s_ident in number
4443: , l_cust_supp_id in number) is
4444: select nvl(sum(nvl(md.gross_weight,0)),0)
4445: from mst_deliveries md
4446: , fte_location_parameters flp
4447: where md.plan_id = l_plan_id
4448: and (md.pickup_location_id = flp.location_id
4449: or md.dropoff_location_id = flp.location_id)
4453: cursor cur_weight_carr (l_plan_id in number
4454: , l_myfac_id in number
4455: , l_carrier_id in number) is
4456: select nvl(sum(nvl(md.gross_weight,0)),0)
4457: from mst_deliveries md
4458: , fte_location_parameters flp
4459: where md.plan_id = l_plan_id
4460: and (md.pickup_location_id = flp.location_id
4461: or md.dropoff_location_id = flp.location_id)
4499:
4500: cursor cur_volume_myfac (l_plan_id in number
4501: , l_myfac_id in number) is
4502: select nvl(sum(nvl(md.volume,0)),0)
4503: from mst_deliveries md
4504: , fte_location_parameters flp
4505: where md.plan_id = l_plan_id
4506: and (md.pickup_location_id = flp.location_id
4507: or md.dropoff_location_id = flp.location_id)
4512: , l_myfac_id in number
4513: , l_c_s_ident in number
4514: , l_cust_supp_id in number) is
4515: select nvl(sum(nvl(md.volume,0)),0)
4516: from mst_deliveries md
4517: , fte_location_parameters flp
4518: where md.plan_id = l_plan_id
4519: and (md.pickup_location_id = flp.location_id
4520: or md.dropoff_location_id = flp.location_id)
4524: cursor cur_volume_carr (l_plan_id in number
4525: , l_myfac_id in number
4526: , l_carrier_id in number) is
4527: select nvl(sum(nvl(md.volume,0)),0)
4528: from mst_deliveries md
4529: , fte_location_parameters flp
4530: where md.plan_id = l_plan_id
4531: and (md.pickup_location_id = flp.location_id
4532: or md.dropoff_location_id = flp.location_id)
4570:
4571: cursor cur_pieces_myfac (l_plan_id in number
4572: , l_myfac_id in number) is
4573: select nvl(sum(nvl(md.number_of_pieces,0)),0)
4574: from mst_deliveries md
4575: , fte_location_parameters flp
4576: where md.plan_id = l_plan_id
4577: and (md.pickup_location_id = flp.location_id
4578: or md.dropoff_location_id = flp.location_id)
4583: , l_myfac_id in number
4584: , l_c_s_ident in number
4585: , l_cust_supp_id in number) is
4586: select nvl(sum(nvl(md.number_of_pieces,0)),0)
4587: from mst_deliveries md
4588: , fte_location_parameters flp
4589: where md.plan_id = l_plan_id
4590: and (md.pickup_location_id = flp.location_id
4591: or md.dropoff_location_id = flp.location_id)
4595: cursor cur_pieces_carr (l_plan_id in number
4596: , l_myfac_id in number
4597: , l_carrier_id in number) is
4598: select nvl(sum(nvl(md.number_of_pieces,0)),0)
4599: from mst_deliveries md
4600: , fte_location_parameters flp
4601: where md.plan_id = l_plan_id
4602: and (md.pickup_location_id = flp.location_id
4603: or md.dropoff_location_id = flp.location_id)
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
4672: and mts.plan_id = md.plan_id
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
4755: where mt.plan_id = mdl1.plan_id
4770: + nvl(mdl.allocated_fac_unloading_cost,0)
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
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
4808: where mt.plan_id = mdl1.plan_id
4824: + nvl(mdl.allocated_fac_unloading_cost,0)
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
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
4869: and mdl.plan_id = md.plan_id
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
4894: and mt.trip_id = mdl1.trip_id
4908: + nvl(mdl.allocated_fac_unloading_cost,0)
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
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
4947: and mt.trip_id = mdl1.trip_id
4963: + nvl(mdl.allocated_fac_unloading_cost,0)
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
5028: , l_cust_supp_id in number
5029: , l_myfac_id in number) is
5030: select count(distinct mdd.source_header_number)
5031: from mst_delivery_details mdd
5032: , mst_deliveries md
5033: , mst_delivery_assignments mda
5034: where md.plan_id = mda.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
5066: select count(distinct dd.source_header_number)
5067: from (
5068: select mdd.source_header_number
5069: from mst_delivery_details mdd
5070: , mst_deliveries md
5071: , mst_delivery_assignments mda
5072: where md.plan_id = mda.plan_id
5073: and md.delivery_id = mda.delivery_id
5074: and mda.plan_id = mdd.plan_id
5086: or mdl.drop_off_stop_id = mts.stop_id))
5087: union all
5088: select mdd.source_header_number
5089: from mst_delivery_details mdd
5090: , mst_deliveries md
5091: , mst_delivery_assignments mda
5092: where md.plan_id = mda.plan_id
5093: and md.delivery_id = mda.delivery_id
5094: and mda.plan_id = mdd.plan_id
5107: select count(distinct dd.source_header_number)
5108: from (
5109: select mdd.source_header_number source_header_number
5110: from mst_delivery_details mdd
5111: , mst_deliveries md
5112: , mst_delivery_assignments mda
5113: where md.plan_id = mda.plan_id
5114: and md.delivery_id = mda.delivery_id
5115: and mda.plan_id = mdd.plan_id
5165: , l_c_s_ident in number
5166: , l_cust_supp_id in number
5167: , l_myfac_id in number) is
5168: select nvl(sum(nvl(md.gross_weight,0)),0)
5169: from mst_deliveries md
5170: , fte_location_parameters flp
5171: where md.plan_id = l_plan_id
5172: and decode(l_c_s_ident, 2, md.customer_id, md.supplier_id) = l_cust_supp_id
5173: and (md.pickup_location_id = flp.location_id
5180: , l_report_for_id in number
5181: , l_c_s_ident in number
5182: , l_cust_supp_id in number) is
5183: select nvl(sum(nvl(md.gross_weight,0)),0)
5184: from mst_deliveries md
5185: where md.plan_id = l_plan_id
5186: 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 )
5187: and decode(l_c_s_ident, 2, md.customer_id, md.supplier_id) = l_cust_supp_id;
5188:
5190: , l_c_s_ident in number
5191: , l_cust_supp_id in number
5192: , l_carrier_id in number) is
5193: select nvl(sum(nvl(md.gross_weight,0)),0)
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
5237: , l_c_s_ident in number
5238: , l_cust_supp_id in number
5239: , l_myfac_id in number) is
5240: select nvl(sum(nvl(md.volume,0)),0)
5241: from mst_deliveries md
5242: , fte_location_parameters flp
5243: where md.plan_id = l_plan_id
5244: and decode(l_c_s_ident, 2, md.customer_id, md.supplier_id) = l_cust_supp_id
5245: and (md.pickup_location_id = flp.location_id
5252: , l_report_for_id in number
5253: , l_c_s_ident in number
5254: , l_cust_supp_id in number) is
5255: select nvl(sum(nvl(md.volume,0)),0)
5256: from mst_deliveries md
5257: where md.plan_id = l_plan_id
5258: 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 )
5259: and decode(l_c_s_ident, 2, md.customer_id, md.supplier_id) = l_cust_supp_id;
5260:
5262: , l_c_s_ident in number
5263: , l_cust_supp_id in number
5264: , l_carrier_id in number) is
5265: select nvl(sum(nvl(md.volume,0)),0)
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
5308: , l_c_s_ident in number
5309: , l_cust_supp_id in number
5310: , l_myfac_id in number) is
5311: select nvl(sum(nvl(md.number_of_pieces,0)),0)
5312: from mst_deliveries md
5313: , fte_location_parameters flp
5314: where md.plan_id = l_plan_id
5315: and decode(l_c_s_ident, 2, md.customer_id, md.supplier_id) = l_cust_supp_id
5316: and (md.pickup_location_id = flp.location_id
5323: , l_report_for_id in number
5324: , l_c_s_ident in number
5325: , l_cust_supp_id in number) is
5326: select nvl(sum(nvl(md.number_of_pieces,0)),0)
5327: from mst_deliveries md
5328: where md.plan_id = l_plan_id
5329: 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 )
5330: and decode(l_c_s_ident, 2, md.customer_id, md.supplier_id) = l_cust_supp_id;
5331:
5333: , l_c_s_ident in number
5334: , l_cust_supp_id in number
5335: , l_carrier_id in number) is
5336: select nvl(sum(nvl(md.number_of_pieces,0)),0)
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
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
5396: and mdl.trip_id = mts.trip_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
5419: and mts.plan_id = md.plan_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
5451: -- and mts.stop_location_id = md.dropoff_location_id
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
5516: where mt.plan_id = mdl1.plan_id
5532: + nvl(mdl.allocated_fac_unloading_cost,0)
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
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
5572: and mt.trip_id = mdl1.trip_id
5586: + nvl(mdl.allocated_fac_unloading_cost,0)
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
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
5629: and mdl.delivery_id = md.delivery_id
5636: + nvl(mdl.allocated_fac_unloading_cost,0)
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
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
5693: , mst_trip_stops mts1
5694: , fte_location_parameters flp
5695: WHERE mdl.plan_id = mt.plan_id
5718: FROM mst_trips mt
5719: , mst_trip_stops mts
5720: WHERE mt.plan_id = l_plan_id
5721: AND mt.trip_id in (SELECT DISTINCT mdl.trip_id
5722: FROM mst_deliveries md
5723: , mst_delivery_legs mdl
5724: , mst_trip_stops mts1
5725: , fte_location_parameters flp
5726: WHERE mdl.plan_id = mt.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
5761: and mts.plan_id = md.plan_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
5792: -- and mts.stop_location_id = md.dropoff_location_id
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
5847: , mst_trip_stops mts1
5848: , fte_location_parameters flp
5849: WHERE mdl.plan_id = mt.plan_id
5872: from mst_trips mt
5873: , mst_trip_stops mts
5874: where mt.plan_id = l_plan_id
5875: and mt.trip_id in (select distinct mdl.trip_id
5876: from mst_deliveries md
5877: , mst_delivery_legs mdl
5878: , mst_trip_stops mts1
5879: , fte_location_parameters flp
5880: where mdl.plan_id = mt.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
5915: and mts.plan_id = md.plan_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
5946: -- and mts.stop_location_id = md.dropoff_location_id
6221: from mst_plans mp
6222: ,wsh_locations wl
6223: WHERE mp.plan_id = p_plan_id
6224: and wl.wsh_location_id in (select distinct md.pickup_location_id
6225: from mst_deliveries md
6226: where md.plan_id = mp.plan_id)
6227: );
6228:
6229:
6289: from mst_plans mp
6290: ,wsh_locations wl
6291: WHERE mp.plan_id = p_plan_id
6292: and wl.wsh_location_id in (select distinct md.dropoff_location_id
6293: from mst_deliveries md
6294: where md.plan_id = mp.plan_id)
6295: );
6296:
6297:
6363: , hz_cust_accounts hzc
6364: WHERE mp.plan_id = p_plan_id
6365: and hzp.party_id = hzc.party_id
6366: and hzc.cust_account_id in (select distinct md.customer_id
6367: from mst_deliveries md
6368: where md.plan_id = mp.plan_id)
6369: and decode ( p_report_for, 2, hzc.cust_account_id, p_report_for_id ) = p_report_for_id
6370: );
6371: