[Home] [Help]
100: from mst_deliveries
101: where plan_id = p_plan_id
102: and delivery_id not in (
103: select delivery_id
104: from mst_delivery_legs
105: where plan_id = p_plan_id
106: );
107: return nvl(l_count,0);
108: EXCEPTION
119: from mst_deliveries
120: where plan_id = p_plan_id
121: and delivery_id not in (
122: select delivery_id
123: from mst_delivery_legs
124: where plan_id = p_plan_id
125: );
126: return nvl(l_count,0);
127: EXCEPTION
137: from mst_deliveries
138: where plan_id = p_plan_id
139: and delivery_id not in (
140: select delivery_id
141: from mst_delivery_legs
142: where plan_id = p_plan_id
143: );
144: return nvl(l_count,0);
145: EXCEPTION
156: from mst_deliveries
157: where plan_id = p_plan_id
158: and delivery_id not in (
159: select delivery_id
160: from mst_delivery_legs
161: where plan_id = p_plan_id
162: );
163: return nvl(l_count,0);
164: EXCEPTION
175: from mst_deliveries
176: where plan_id = p_plan_id
177: and delivery_id not in (
178: select delivery_id
179: from mst_delivery_legs
180: where plan_id = p_plan_id
181: );
182: return nvl(l_count,0);
183: EXCEPTION
304: -- Added join for plan id
305: select sum(mdl.allocated_fac_loading_cost)
306: into l_loading_charge
307: from mst_trip_stops mts,
308: mst_delivery_legs mdl
309: where mts.stop_id = p_stop_id
310: AND mts.plan_id = P_PLAN_ID
311: AND mts.plan_id = mdl.plan_Id
312: and mts.stop_id = mdl.pick_up_stop_id;
322: begin
323: select sum(mdl.allocated_fac_unloading_cost)
324: into l_unloading_charge
325: from mst_trip_stops mts,
326: mst_delivery_legs mdl
327: where mts.stop_id = p_stop_id
328: and mts.stop_id = mdl.drop_off_stop_id;
329: return l_unloading_charge;
330: EXCEPTION
338: begin
339: select sum(md.gross_weight)
340: into l_loading_weight
341: from mst_trip_stops mts,
342: mst_delivery_legs mdl,
343: mst_deliveries md
344: where mts.stop_id = p_stop_id
345: and mts.stop_id = mdl.pick_up_stop_id
346: and mdl.delivery_id = md.delivery_id;
356: begin
357: select sum(md.volume)
358: into l_loading_volume
359: from mst_trip_stops mts,
360: mst_delivery_legs mdl,
361: mst_deliveries md
362: where mts.stop_id = p_stop_id
363: and mts.stop_id = mdl.pick_up_stop_id
364: and mdl.delivery_id = md.delivery_id;
374: begin
375: select sum(md.volume)
376: into l_loading_pallets
377: from mst_trip_stops mts,
378: mst_delivery_legs mdl,
379: mst_deliveries md
380: where mts.stop_id = p_stop_id
381: and mts.stop_id = mdl.pick_up_stop_id
382: and mdl.delivery_id = md.delivery_id;
392: begin
393: select sum(md.number_of_pieces)
394: into l_loading_pieces
395: from mst_trip_stops mts,
396: mst_delivery_legs mdl,
397: mst_deliveries md
398: where mts.stop_id = p_stop_id
399: and mts.stop_id = mdl.pick_up_stop_id
400: and mdl.delivery_id = md.delivery_id;
411: begin
412: select sum(md.gross_weight)
413: into l_unloading_weight
414: from mst_trip_stops mts,
415: mst_delivery_legs mdl,
416: mst_deliveries md
417: where mts.stop_id = p_stop_id
418: and mts.stop_id = mdl.drop_off_stop_id
419: and mdl.delivery_id = md.delivery_id;
429: begin
430: select sum(md.volume)
431: into l_unloading_volume
432: from mst_trip_stops mts,
433: mst_delivery_legs mdl,
434: mst_deliveries md
435: where mts.stop_id = p_stop_id
436: and mts.stop_id = mdl.drop_off_stop_id
437: and mdl.delivery_id = md.delivery_id;
447: begin
448: select sum(md.number_of_pallets)
449: into l_unloading_pallets
450: from mst_trip_stops mts,
451: mst_delivery_legs mdl,
452: mst_deliveries md
453: where mts.stop_id = p_stop_id
454: and mts.stop_id = mdl.drop_off_stop_id
455: and mdl.delivery_id = md.delivery_id;
465: begin
466: select sum(md.number_of_pieces)
467: into l_unloading_pieces
468: from mst_trip_stops mts,
469: mst_delivery_legs mdl,
470: mst_deliveries md
471: where mts.stop_id = p_stop_id
472: and mts.stop_id = mdl.drop_off_stop_id
473: and mdl.delivery_id = md.delivery_id;
507: l_delivery_cost number;
508: begin
509: select sum(allocated_transport_cost + allocated_fac_loading_cost + allocated_fac_unloading_cost)
510: into l_delivery_cost
511: from mst_delivery_legs
512: where delivery_id = p_delivery_id;
513:
514: return l_delivery_cost;
515: EXCEPTION
717: FROM MST_DELIVERIES md
718: WHERE md.plan_id = p_plan_id
719: AND md.delivery_id IN
720: ( SELECT mdl.delivery_id
721: FROM MST_DELIVERY_LEGS mdl,
722: MST_TRIPS mt,
723: MST_TRIP_STOPS mts
724: WHERE mt.plan_id = md.plan_id
725: AND mt.carrier_id = p_carrier_id
743: FROM mst_deliveries md
744: WHERE md.plan_id = p_plan_id
745: AND md.delivery_id IN
746: ( SELECT mdl.delivery_id
747: FROM mst_delivery_legs mdl,
748: mst_trip_stops mts
749: WHERE mdl.plan_id = md.plan_id
750: AND mdl.plan_id = mts.plan_id
751: AND mdl.pick_up_stop_id = mts.stop_id
764: FROM mst_deliveries md
765: WHERE md.plan_id = p_plan_id
766: AND md.delivery_id IN
767: ( SELECT mdl.delivery_id
768: FROM mst_delivery_legs mdl,
769: mst_trip_stops mts
770: WHERE mdl.plan_id = md.plan_id
771: AND mdl.plan_id = mts.plan_id
772: AND mdl.drop_off_stop_id = mts.stop_id
798: FROM MST_DELIVERIES md
799: WHERE md.plan_id = p_plan_id
800: AND md.delivery_id IN
801: ( SELECT mdl.delivery_id
802: FROM MST_DELIVERY_LEGS mdl,
803: MST_TRIPS mt,
804: MST_TRIP_STOPS mts
805: WHERE mt.plan_id = md.plan_id
806: AND mt.carrier_id = p_carrier_id
824: FROM mst_deliveries md
825: WHERE md.plan_id = p_plan_id
826: AND md.delivery_id IN
827: ( SELECT mdl.delivery_id
828: FROM mst_delivery_legs mdl,
829: mst_trip_stops mts
830: WHERE mdl.plan_id = md.plan_id
831: AND mdl.plan_id = mts.plan_id
832: AND mdl.pick_up_stop_id = mts.stop_id
845: FROM mst_deliveries md
846: WHERE md.plan_id = p_plan_id
847: AND md.delivery_id IN
848: ( SELECT mdl.delivery_id
849: FROM mst_delivery_legs mdl,
850: mst_trip_stops mts
851: WHERE mdl.plan_id = md.plan_id
852: AND mdl.plan_id = mts.plan_id
853: AND mdl.drop_off_stop_id = mts.stop_id
880: FROM MST_DELIVERIES md
881: WHERE md.plan_id = p_plan_id
882: AND md.delivery_id IN
883: ( SELECT mdl.delivery_id
884: FROM MST_DELIVERY_LEGS mdl,
885: MST_TRIPS mt,
886: MST_TRIP_STOPS mts
887: WHERE mt.plan_id = md.plan_id
888: AND mt.carrier_id = p_carrier_id
906: FROM mst_deliveries md
907: WHERE md.plan_id = p_plan_id
908: AND md.delivery_id IN
909: ( SELECT mdl.delivery_id
910: FROM mst_delivery_legs mdl,
911: mst_trip_stops mts
912: WHERE mdl.plan_id = md.plan_id
913: AND mdl.plan_id = mts.plan_id
914: AND mdl.pick_up_stop_id = mts.stop_id
927: FROM mst_deliveries md
928: WHERE md.plan_id = p_plan_id
929: AND md.delivery_id IN
930: ( SELECT mdl.delivery_id
931: FROM mst_delivery_legs mdl,
932: mst_trip_stops mts
933: WHERE mdl.plan_id = md.plan_id
934: AND mdl.plan_id = mts.plan_id
935: AND mdl.drop_off_stop_id = mts.stop_id
961: FROM MST_DELIVERIES md
962: WHERE md.plan_id = p_plan_id
963: AND md.delivery_id IN
964: ( SELECT mdl.delivery_id
965: FROM MST_DELIVERY_LEGS mdl,
966: MST_TRIPS mt,
967: MST_TRIP_STOPS mts
968: WHERE mt.plan_id = md.plan_id
969: AND mt.carrier_id = p_carrier_id
987: FROM mst_deliveries md
988: WHERE md.plan_id = p_plan_id
989: AND md.delivery_id IN
990: ( SELECT mdl.delivery_id
991: FROM mst_delivery_legs mdl,
992: mst_trip_stops mts
993: WHERE mdl.plan_id = md.plan_id
994: AND mdl.plan_id = mts.plan_id
995: AND mdl.pick_up_stop_id = mts.stop_id
1008: FROM mst_deliveries md
1009: WHERE md.plan_id = p_plan_id
1010: AND md.delivery_id IN
1011: ( SELECT mdl.delivery_id
1012: FROM mst_delivery_legs mdl,
1013: mst_trip_stops mts
1014: WHERE mdl.plan_id = md.plan_id
1015: AND mdl.plan_id = mts.plan_id
1016: AND mdl.drop_off_stop_id = mts.stop_id
1053: AND MD.DELIVERY_ID IN
1054: (SELECT MDL.DELIVERY_ID
1055: FROM MST_TRIPS T,
1056: MST_TRIP_STOPS TS,
1057: MST_DELIVERY_LEGS MDL
1058: WHERE MDL.PLAN_ID = MD.PLAN_ID
1059: AND TS.PLAN_ID = MDL.PLAN_ID
1060: AND ( TS.STOP_ID = MDL.PICK_UP_STOP_ID
1061: OR TS.STOP_ID = MDL.DROP_OFF_STOP_ID )
1087: and mda.plan_id = mdd.plan_id
1088: and mda.delivery_detail_id = mdd.delivery_detail_id
1089: AND md.delivery_id IN
1090: ( SELECT mdl.delivery_id
1091: FROM mst_delivery_legs mdl,
1092: mst_trip_stops mts
1093: WHERE mdl.plan_id = md.plan_id
1094: AND mdl.plan_id = mts.plan_id
1095: AND mdl.pick_up_stop_id = mts.stop_id
1114: and mda.plan_id = mdd.plan_id
1115: and mda.delivery_detail_id = mdd.delivery_detail_id
1116: AND md.delivery_id IN
1117: ( SELECT mdl.delivery_id
1118: FROM mst_delivery_legs mdl,
1119: mst_trip_stops mts
1120: WHERE mdl.plan_id = md.plan_id
1121: AND mdl.plan_id = mts.plan_id
1122: AND mdl.drop_off_stop_id = mts.stop_id
1187: select sum(gross_weight)
1188: into l_carrier_weight
1189: from(
1190: select distinct md.delivery_id, md.gross_weight gross_weight
1191: from mst_delivery_legs mdl,
1192: mst_deliveries md,
1193: mst_trips mt,
1194: mst_trip_stops mts
1195: where mt.plan_id = p_plan_id
1214: select sum(volume)
1215: into l_carrier_volume
1216: from(
1217: select distinct md.delivery_id, md.volume volume
1218: from mst_delivery_legs mdl,
1219: mst_deliveries md,
1220: mst_trips mt,
1221: mst_trip_stops mts
1222: where mt.plan_id = p_plan_id
1240: select sum(number_of_pallets)
1241: into l_carrier_pallets
1242: from(
1243: select distinct md.delivery_id, md.number_of_pallets number_of_pallets
1244: from mst_delivery_legs mdl,
1245: mst_deliveries md,
1246: mst_trips mt,
1247: mst_trip_stops mts
1248: where mt.plan_id = p_plan_id
1266: select sum(number_of_pieces)
1267: into l_carrier_pieces
1268: from(
1269: select distinct md.delivery_id, md.number_of_pieces number_of_pieces
1270: from mst_delivery_legs mdl,
1271: mst_deliveries md,
1272: mst_trips mt,
1273: mst_trip_stops mts
1274: where mt.plan_id = p_plan_id
1302: mdl.ALLOCATED_FAC_REC_HAND_COST +
1303: mdl.allocated_transport_cost )
1304: into l_total_cost
1305: from mst_deliveries md,
1306: mst_delivery_legs mdl
1307: where md.plan_id = p_plan_id
1308: and md.customer_id = p_customer_id
1309: AND md.plan_id = mdl.plan_id
1310: and md.delivery_id = mdl.delivery_id;
1336: from mst_deliveries md
1337: where md.plan_id = p_plan_id
1338: and md.customer_id = p_customer_id;
1339: */
1340: --and exists (select 1 from mst_delivery_legs mdl
1341: -- where md.delivery_id = mdl.delivery_id
1342: -- );
1343:
1344:
1383: from mst_deliveries md
1384: where md.plan_id = p_plan_id
1385: and md.customer_id = p_customer_id;
1386: */
1387: --and exists (select 1 from mst_delivery_legs mdl
1388: -- where md.delivery_id = mdl.delivery_id
1389: -- );
1390:
1391:
1428: from mst_deliveries md
1429: where md.plan_id = p_plan_id
1430: and md.customer_id = p_customer_id;
1431: */
1432: --and exists (select 1 from mst_delivery_legs mdl
1433: -- where md.delivery_id = mdl.delivery_id
1434: -- );
1435:
1436:
1475: from mst_deliveries md
1476: where md.plan_id = p_plan_id
1477: and md.customer_id = p_customer_id;
1478: */
1479: --and exists (select 1 from mst_delivery_legs mdl
1480: -- where md.delivery_id = mdl.delivery_id
1481: -- );
1482:
1483:
1528: and mdd.split_from_delivery_detail_id is null
1529: and mdd.delivery_detail_id = mda.delivery_detail_id
1530: and mda.parent_delivery_detail_id is null
1531: and exists (select 1
1532: from mst_delivery_legs mdl
1533: where mdl.delivery_id = mda.delivery_id
1534: );
1535: ***/
1536: /*SELECT COUNT(DISTINCT mdd.SOURCE_HEADER_NUMBER)
1541: WHERE MD.PLAN_ID = MDA.PLAN_ID
1542: AND MD.DELIVERY_ID = MDA.DELIVERY_ID
1543: AND MD.DELIVERY_ID IN
1544: (SELECT MDL.DELIVERY_ID
1545: FROM MST_DELIVERY_LEGS MDL
1546: WHERE MDL.PLAN_ID = MD.PLAN_ID)
1547: AND MDA.PLAN_ID = MDD.PLAN_ID
1548: AND MDA.DELIVERY_DETAIL_ID = MDD.DELIVERY_DETAIL_ID
1549: AND MD.PLAN_ID = P_PLAN_ID
1562: WHERE MD.PLAN_ID = MDA.PLAN_ID
1563: AND MD.DELIVERY_ID = MDA.DELIVERY_ID
1564: AND MD.DELIVERY_ID IN
1565: (SELECT MDL.DELIVERY_ID
1566: FROM MST_DELIVERY_LEGS MDL
1567: WHERE MDL.PLAN_ID = MD.PLAN_ID)
1568: AND MDA.PLAN_ID = MDD.PLAN_ID
1569: AND MDA.DELIVERY_DETAIL_ID = MDD.DELIVERY_DETAIL_ID
1570: AND MD.PLAN_ID = p_PLAN_ID
1577: WHERE MD.PLAN_ID = MDA.PLAN_ID
1578: AND MD.DELIVERY_ID = MDA.DELIVERY_ID
1579: AND not exists
1580: (SELECT 1
1581: FROM MST_DELIVERY_LEGS MDL
1582: WHERE MDL.PLAN_ID = MD.PLAN_ID
1583: and MDL.DELIVERY_ID = md.plan_id)
1584: AND MDA.PLAN_ID = MDD.PLAN_ID
1585: AND MDA.DELIVERY_DETAIL_ID = MDD.DELIVERY_DETAIL_ID
1603: and md.customer_id = p_customer_id;
1604:
1605: cursor mst_d_l_count (p_delivery_id NUMBER) is
1606: select count(mdl.delivery_leg_id)
1607: from mst_delivery_legs mdl
1608: where mdl.delivery_id = p_delivery_id
1609: and mdl.plan_id = p_plan_id;
1610:
1611: cursor mst_d_l (p_delivery_id NUMBER) is
1609: and mdl.plan_id = p_plan_id;
1610:
1611: cursor mst_d_l (p_delivery_id NUMBER) is
1612: select mdl.delivery_leg_id
1613: from mst_delivery_legs mdl
1614: where mdl.delivery_id = p_delivery_id
1615: and mdl.plan_id = p_plan_id;
1616:
1617: cursor mst_mode_of_transport (p_delivery_leg_id NUMBER) is
1615: and mdl.plan_id = p_plan_id;
1616:
1617: cursor mst_mode_of_transport (p_delivery_leg_id NUMBER) is
1618: select mt.mode_of_transport mode_of_transport, mt.trip_id trip_id
1619: from mst_delivery_legs mdl, mst_trip_stops mts, mst_trips mt
1620: where mdl.delivery_leg_id = p_delivery_leg_id
1621: and mdl.plan_id = mts.plan_id
1622: and mdl.pick_up_stop_id = mts.stop_id
1623: and mdl.plan_id = mt.plan_id
1624: and mts.trip_id = mt.trip_id;
1625:
1626: cursor mst_pickup_stop_location (p_delivery_leg_id NUMBER) is
1627: select mts.stop_location_id location_id
1628: from mst_delivery_legs mdl, mst_trip_stops mts
1629: where delivery_leg_id = p_delivery_leg_id
1630: and mdl.plan_id = mts.plan_id
1631: and mdl.pick_up_stop_id = mts.stop_id ;
1632:
1631: and mdl.pick_up_stop_id = mts.stop_id ;
1632:
1633: cursor mst_dropoff_stop_location (p_delivery_leg_id NUMBER) is
1634: select mts.stop_location_id location_id
1635: from mst_delivery_legs mdl, mst_trip_stops mts
1636: where delivery_leg_id = p_delivery_leg_id
1637: and mdl.plan_id = mts.plan_id
1638: and mdl.drop_off_stop_id = mts.stop_id ;
1639:
1754: and decode(p_partner_type, 0, md.customer_id, md.supplier_id) = p_partner_id;
1755:
1756: cursor mst_d_l_count (p_delivery_id NUMBER) is
1757: select count(mdl.delivery_leg_id)
1758: from mst_delivery_legs mdl
1759: where mdl.delivery_id = p_delivery_id
1760: and mdl.plan_id = p_plan_id;
1761:
1762: cursor mst_d_l (p_delivery_id NUMBER) is
1760: and mdl.plan_id = p_plan_id;
1761:
1762: cursor mst_d_l (p_delivery_id NUMBER) is
1763: select mdl.delivery_leg_id
1764: from mst_delivery_legs mdl
1765: where mdl.delivery_id = p_delivery_id
1766: and mdl.plan_id = p_plan_id;
1767:
1768: cursor mst_mode_of_transport (p_delivery_leg_id NUMBER) is
1766: and mdl.plan_id = p_plan_id;
1767:
1768: cursor mst_mode_of_transport (p_delivery_leg_id NUMBER) is
1769: select mt.mode_of_transport mode_of_transport, mt.trip_id trip_id
1770: from mst_delivery_legs mdl, mst_trip_stops mts, mst_trips mt
1771: where mdl.delivery_leg_id = p_delivery_leg_id
1772: and mdl.plan_id = mts.plan_id
1773: and mdl.pick_up_stop_id = mts.stop_id
1774: and mdl.plan_id = mt.plan_id
1775: and mts.trip_id = mt.trip_id;
1776:
1777: cursor mst_pickup_stop_location (p_delivery_leg_id NUMBER) is
1778: select mts.stop_location_id location_id
1779: from mst_delivery_legs mdl, mst_trip_stops mts
1780: where delivery_leg_id = p_delivery_leg_id
1781: and mdl.plan_id = mts.plan_id
1782: and mdl.pick_up_stop_id = mts.stop_id ;
1783:
1782: and mdl.pick_up_stop_id = mts.stop_id ;
1783:
1784: cursor mst_dropoff_stop_location (p_delivery_leg_id NUMBER) is
1785: select mts.stop_location_id location_id
1786: from mst_delivery_legs mdl, mst_trip_stops mts
1787: where delivery_leg_id = p_delivery_leg_id
1788: and mdl.plan_id = mts.plan_id
1789: and mdl.drop_off_stop_id = mts.stop_id ;
1790:
1924: where mt.plan_id = p_plan_id
1925: and mt.trip_id in
1926: (select distinct mts.trip_id
1927: from mst_trip_stops mts
1928: , mst_delivery_legs mdl
1929: , mst_deliveries md
1930: where md.plan_id = mt.plan_id
1931: and md.customer_id = p_partner_id
1932: and mts.plan_id = md.plan_id
1951: where mt.plan_id = p_plan_id
1952: and mt.trip_id in
1953: (select distinct mts.trip_id
1954: from mst_trip_stops mts
1955: , mst_delivery_legs mdl
1956: , mst_deliveries md
1957: where md.plan_id = mt.plan_id
1958: and md.customer_id = p_partner_id
1959: and mts.plan_id = md.plan_id
1978: where mt.plan_id = p_plan_id
1979: and mt.trip_id in
1980: (select distinct mts.trip_id
1981: from mst_trip_stops mts
1982: , mst_delivery_legs mdl
1983: , mst_deliveries md
1984: where md.plan_id = mt.plan_id
1985: and md.customer_id = p_partner_id
1986: and mts.plan_id = md.plan_id
1998: where mt.plan_id = p_plan_id
1999: and mt.trip_id in
2000: (select distinct mts.trip_id
2001: from mst_trip_stops mts
2002: , mst_delivery_legs mdl
2003: , mst_deliveries md
2004: where md.plan_id = mt.plan_id
2005: and md.customer_id = p_partner_id
2006: and mts.plan_id = md.plan_id
2019: where mt.plan_id = p_plan_id
2020: and mt.trip_id in
2021: (select distinct mts.trip_id
2022: from mst_trip_stops mts
2023: , mst_delivery_legs mdl
2024: , mst_deliveries md
2025: where md.plan_id = mt.plan_id
2026: and md.supplier_id = p_partner_id
2027: and mts.plan_id = md.plan_id
2046: where mt.plan_id = p_plan_id
2047: and mt.trip_id in
2048: (select distinct mts.trip_id
2049: from mst_trip_stops mts
2050: , mst_delivery_legs mdl
2051: , mst_deliveries md
2052: where md.plan_id = mt.plan_id
2053: and md.supplier_id = p_partner_id
2054: and mts.plan_id = md.plan_id
2073: where mt.plan_id = p_plan_id
2074: and mt.trip_id in
2075: (select distinct mts.trip_id
2076: from mst_trip_stops mts
2077: , mst_delivery_legs mdl
2078: , mst_deliveries md
2079: where md.plan_id = mt.plan_id
2080: and md.supplier_id = p_partner_id
2081: and mts.plan_id = md.plan_id
2093: where mt.plan_id = p_plan_id
2094: and mt.trip_id in
2095: (select distinct mts.trip_id
2096: from mst_trip_stops mts
2097: , mst_delivery_legs mdl
2098: , mst_deliveries md
2099: where md.plan_id = mt.plan_id
2100: and md.supplier_id = p_partner_id
2101: and mts.plan_id = md.plan_id
2224: RETURN NUMBER IS
2225:
2226: CURSOR Cur_First_Delivery_Leg IS
2227: SELECT DL.delivery_leg_id
2228: FROM mst_delivery_legs DL
2229: WHERE DL.plan_id = p_plan_id
2230: AND DL.delivery_id = p_delivery_id
2231: AND DL.sequence_number = (SELECT min(DL1.sequence_number) seq_no
2232: FROM mst_delivery_legs DL1
2228: FROM mst_delivery_legs DL
2229: WHERE DL.plan_id = p_plan_id
2230: AND DL.delivery_id = p_delivery_id
2231: AND DL.sequence_number = (SELECT min(DL1.sequence_number) seq_no
2232: FROM mst_delivery_legs DL1
2233: WHERE DL1.plan_id = DL.plan_id
2234: AND DL1.delivery_id = DL.delivery_id);
2235:
2236: CURSOR Cur_Last_Delivery_Leg IS
2234: AND DL1.delivery_id = DL.delivery_id);
2235:
2236: CURSOR Cur_Last_Delivery_Leg IS
2237: SELECT DL.delivery_leg_id
2238: FROM mst_delivery_legs DL
2239: WHERE DL.plan_id = p_plan_id
2240: AND DL.delivery_id = p_delivery_id
2241: AND DL.sequence_number = (SELECT max(DL1.sequence_number) seq_no
2242: FROM mst_delivery_legs DL1
2238: FROM mst_delivery_legs DL
2239: WHERE DL.plan_id = p_plan_id
2240: AND DL.delivery_id = p_delivery_id
2241: AND DL.sequence_number = (SELECT max(DL1.sequence_number) seq_no
2242: FROM mst_delivery_legs DL1
2243: WHERE DL1.plan_id = DL.plan_id
2244: AND DL1.delivery_id = DL.delivery_id);
2245: /*
2246: cursor seq_no is
2245: /*
2246: cursor seq_no is
2247: select decode (p_type, 0, min(sequence_number),
2248: 1, max(sequence_number)) seq_no
2249: from mst_delivery_legs
2250: where plan_id = p_plan_id
2251: and delivery_id = p_delivery_id;
2252:
2253: cursor delivery_leg (p_seq_no number) is
2251: and delivery_id = p_delivery_id;
2252:
2253: cursor delivery_leg (p_seq_no number) is
2254: select delivery_leg_id
2255: from mst_delivery_legs
2256: where plan_id = p_plan_id
2257: and delivery_id = p_delivery_id
2258: and sequence_number = p_seq_no;
2259: */
2293: select sum(mdl.allocated_fac_loading_cost + mdl.allocated_fac_unloading_cost
2294: + mdl.ALLOCATED_FAC_SHP_HAND_COST + mdl.ALLOCATED_FAC_REC_HAND_COST + mdl.allocated_transport_cost)
2295: into l_total_cost
2296: from mst_deliveries md,
2297: mst_delivery_legs mdl
2298: where md.plan_id = p_plan_id
2299: and md.supplier_id = p_supplier_id
2300: and md.delivery_id = mdl.delivery_id;
2301:
2326: from mst_deliveries md
2327: where md.plan_id = p_plan_id
2328: and md.supplier_id = p_supplier_id;
2329: */
2330: --and exists (select 1 from mst_delivery_legs mdl
2331: -- where md.delivery_id = mdl.delivery_id
2332: -- );
2333:
2334: -- ----------------------------------------
2372: from mst_deliveries md
2373: where md.plan_id = p_plan_id
2374: and md.supplier_id = p_supplier_id;
2375: */
2376: --and exists (select 1 from mst_delivery_legs mdl
2377: -- where md.delivery_id = mdl.delivery_id
2378: -- );
2379:
2380: -- ----------------------------------------
2418: from mst_deliveries md
2419: where md.plan_id = p_plan_id
2420: and md.supplier_id = p_supplier_id;
2421: */
2422: --and exists (select 1 from mst_delivery_legs mdl
2423: -- where md.delivery_id = mdl.delivery_id
2424: -- );
2425:
2426: -- ----------------------------------------
2464: from mst_deliveries md
2465: where md.plan_id = p_plan_id
2466: and md.supplier_id = p_supplier_id;
2467: */
2468: --and exists (select 1 from mst_delivery_legs mdl
2469: -- where md.delivery_id = mdl.delivery_id
2470: -- );
2471:
2472: -- ----------------------------------------
2515: and mdd.split_from_delivery_detail_id is null
2516: and mdd.delivery_detail_id = mda.delivery_detail_id
2517: and mda.parent_delivery_detail_id is null
2518: and exists (select 1
2519: from mst_delivery_legs mdl
2520: where mdl.delivery_id = mda.delivery_id
2521: );
2522: ***/
2523: /*SELECT COUNT(DISTINCT mdd.SOURCE_HEADER_NUMBER)
2528: WHERE MD.PLAN_ID = MDA.PLAN_ID
2529: AND MD.DELIVERY_ID = MDA.DELIVERY_ID
2530: AND MD.DELIVERY_ID IN
2531: (SELECT MDL.DELIVERY_ID
2532: FROM MST_DELIVERY_LEGS MDL
2533: WHERE MDL.PLAN_ID = MD.PLAN_ID)
2534: AND MDA.PLAN_ID = MDD.PLAN_ID
2535: AND MDA.DELIVERY_DETAIL_ID = MDD.DELIVERY_DETAIL_ID
2536: AND MD.PLAN_ID = P_PLAN_ID
2549: WHERE MD.PLAN_ID = MDA.PLAN_ID
2550: AND MD.DELIVERY_ID = MDA.DELIVERY_ID
2551: AND MD.DELIVERY_ID IN
2552: (SELECT MDL.DELIVERY_ID
2553: FROM MST_DELIVERY_LEGS MDL
2554: WHERE MDL.PLAN_ID = MD.PLAN_ID)
2555: AND MDA.PLAN_ID = MDD.PLAN_ID
2556: AND MDA.DELIVERY_DETAIL_ID = MDD.DELIVERY_DETAIL_ID
2557: AND MD.PLAN_ID = p_PLAN_ID
2564: WHERE MD.PLAN_ID = MDA.PLAN_ID
2565: AND MD.DELIVERY_ID = MDA.DELIVERY_ID
2566: AND not exists
2567: (SELECT 1
2568: FROM MST_DELIVERY_LEGS MDL
2569: WHERE MDL.PLAN_ID = MD.PLAN_ID
2570: and MDL.DELIVERY_ID = md.plan_id)
2571: AND MDA.PLAN_ID = MDD.PLAN_ID
2572: AND MDA.DELIVERY_DETAIL_ID = MDD.DELIVERY_DETAIL_ID
2591: select sum(mdl.allocated_fac_loading_cost + mdl.allocated_fac_unloading_cost
2592: + mdl.ALLOCATED_FAC_SHP_HAND_COST + mdl.ALLOCATED_FAC_REC_HAND_COST + mdl.allocated_transport_cost)
2593: into l_total_cost
2594: from mst_deliveries md,
2595: mst_delivery_legs mdl
2596: where md.plan_id = p_plan_id
2597: and decode(p_facility_type, 0, md.customer_id, md.supplier_id) = p_cust_or_supp_id
2598: and (md.pickup_location_id = p_location_id
2599: or md.dropoff_location_id = p_location_id)
2629: and decode(p_facility_type, 0, md.customer_id, md.supplier_id) = p_cust_or_supp_id
2630: and (md.pickup_location_id = p_location_id
2631: or md.dropoff_location_id = p_location_id);
2632: */
2633: --and exists (select * from mst_delivery_legs mdl
2634: -- where md.delivery_id = mdl.delivery_id
2635: -- );
2636: -- l_total_weight := NVL(l_total_weight,0);
2637:
2678: and decode(p_facility_type, 0, md.customer_id, md.supplier_id) = p_cust_or_supp_id
2679: and (md.pickup_location_id = p_location_id
2680: or md.dropoff_location_id = p_location_id);
2681: */
2682: --and exists (select * from mst_delivery_legs mdl
2683: -- where md.delivery_id = mdl.delivery_id
2684: -- );
2685: -- l_total_cube := NVL(l_total_cube,0);
2686:
2727: and decode(p_facility_type, 0, md.customer_id, md.supplier_id) = p_cust_or_supp_id
2728: and (md.pickup_location_id = p_location_id
2729: or md.dropoff_location_id = p_location_id);
2730: */
2731: --and exists (select * from mst_delivery_legs mdl
2732: -- where md.delivery_id = mdl.delivery_id
2733: -- );
2734:
2735: -- l_total_pallets := NVL(l_total_pallets,0);
2777: and decode(p_facility_type, 0, md.customer_id, md.supplier_id) = p_cust_or_supp_id
2778: and (md.pickup_location_id = p_location_id
2779: or md.dropoff_location_id = p_location_id);
2780: */
2781: --and exists (select 1 from mst_delivery_legs mdl
2782: -- where md.delivery_id = mdl.delivery_id
2783: -- );
2784:
2785: -- l_total_pieces := NVL(l_total_pieces, 0);
2831: and mdd.split_from_delivery_detail_id is null
2832: and mdd.delivery_detail_id = mda.delivery_detail_id
2833: and mda.parent_delivery_detail_id is null
2834: and exists (select 1
2835: from mst_delivery_legs mdl
2836: where mdl.delivery_id = mda.delivery_id
2837: );
2838: ***/
2839: -- ----------------------------------------
2855: AND MD.PLAN_ID = P_PLAN_ID
2856: AND MD.CUSTOMER_ID = P_CUST_OR_SUPP_ID
2857: AND MD.DELIVERY_ID IN
2858: (SELECT MDL.DELIVERY_ID
2859: FROM MST_DELIVERY_LEGS MDL,
2860: MST_TRIP_STOPS MTS
2861: WHERE MDL.PLAN_ID = MD.PLAN_ID
2862: AND MDL.PLAN_ID = MTS.PLAN_ID
2863: AND ( MDL.PICK_UP_STOP_ID = MTS.STOP_ID
2874: AND MDA.DELIVERY_DETAIL_ID = MDD.DELIVERY_DETAIL_ID
2875: AND MD.PLAN_ID = P_PLAN_ID
2876: AND MD.CUSTOMER_ID = P_CUST_OR_SUPP_ID
2877: AND md.dropoff_location_id = p_location_id
2878: AND NOT EXISTS (SELECT 1 FROM mst_delivery_legs mdl
2879: WHERE mdl.plan_id=md.plan_id
2880: AND mdl.delivery_id = md.delivery_id)) dd;
2881: ELSE
2882: SELECT COUNT(DISTINCT dd.SOURCE_HEADER_NUMBER)
2893: AND MD.PLAN_ID = P_PLAN_ID
2894: AND MD.SUPPLIER_ID = P_CUST_OR_SUPP_ID
2895: AND MD.DELIVERY_ID IN
2896: (SELECT MDL.DELIVERY_ID
2897: FROM MST_DELIVERY_LEGS MDL,
2898: MST_TRIP_STOPS MTS
2899: WHERE MDL.PLAN_ID = MD.PLAN_ID
2900: AND MDL.PLAN_ID = MTS.PLAN_ID
2901: AND ( MDL.PICK_UP_STOP_ID = MTS.STOP_ID
2912: AND MDA.DELIVERY_DETAIL_ID = MDD.DELIVERY_DETAIL_ID
2913: AND MD.PLAN_ID = P_PLAN_ID
2914: AND MD.SUPPLIER_ID = P_CUST_OR_SUPP_ID
2915: AND md.dropoff_location_id = p_location_id
2916: AND NOT EXISTS (SELECT 1 FROM mst_delivery_legs mdl
2917: WHERE mdl.plan_id=md.plan_id
2918: AND mdl.delivery_id = md.delivery_id)) dd;
2919:
2920: END IF;
2955: WHERE mt.plan_id = p_plan_id
2956: AND mt.trip_id IN
2957: (select distinct mts.trip_id
2958: from mst_trip_stops mts
2959: , mst_delivery_legs mdl
2960: , mst_deliveries md
2961: where md.plan_id = mt.plan_id
2962: and md.customer_id = p_partner_id
2963: and mts.plan_id = md.plan_id
2992: WHERE mt.plan_id = p_plan_id
2993: AND mt.trip_id IN
2994: (select distinct mts.trip_id
2995: from mst_trip_stops mts
2996: , mst_delivery_legs mdl
2997: , mst_deliveries md
2998: where md.plan_id = mt.plan_id
2999: and md.supplier_id = p_partner_id
3000: and mts.plan_id = md.plan_id
3031: WHERE mt.plan_id = p_plan_id
3032: AND mt.trip_id IN
3033: (select distinct mts.trip_id
3034: from mst_trip_stops mts
3035: , mst_delivery_legs mdl
3036: , mst_deliveries md
3037: where md.plan_id = mt.plan_id
3038: and md.customer_id = p_partner_id
3039: and mts.plan_id = md.plan_id
3068: WHERE mt.plan_id = p_plan_id
3069: AND mt.trip_id IN
3070: (select distinct mts.trip_id
3071: from mst_trip_stops mts
3072: , mst_delivery_legs mdl
3073: , mst_deliveries md
3074: where md.plan_id = mt.plan_id
3075: and md.supplier_id = p_partner_id
3076: and mts.plan_id = md.plan_id
3124: FROM mst_trips mt
3125: WHERE mt.plan_id = p_plan_id
3126: AND mt.trip_id IN
3127: (SELECT mdl.trip_id
3128: FROM mst_delivery_legs mdl,
3129: mst_deliveries md
3130: WHERE mdl.plan_id = mt.plan_id
3131: AND md.plan_id = mdl.plan_id
3132: AND md.delivery_id = mdl.delivery_id
3140: FROM mst_trips mt
3141: WHERE mt.plan_id = p_plan_id
3142: AND mt.trip_id IN
3143: (SELECT mdl.trip_id
3144: FROM mst_delivery_legs mdl,
3145: mst_deliveries md,
3146: mst_trip_stops mts
3147: WHERE mdl.plan_id = mt.plan_id
3148: AND md.plan_id = mdl.plan_id
3168: FROM mst_trips mt
3169: WHERE mt.plan_id = p_plan_id
3170: AND mt.trip_id IN
3171: (SELECT mdl.trip_id
3172: FROM mst_delivery_legs mdl,
3173: mst_deliveries md
3174: WHERE mdl.plan_id = mt.plan_id
3175: AND md.plan_id = mdl.plan_id
3176: AND md.delivery_id = mdl.delivery_id
3184: FROM mst_trips mt
3185: WHERE mt.plan_id = p_plan_id
3186: AND mt.trip_id IN
3187: (SELECT mdl.trip_id
3188: FROM mst_delivery_legs mdl,
3189: mst_deliveries md,
3190: mst_trip_stops mts
3191: WHERE mdl.plan_id = mt.plan_id
3192: AND md.plan_id = mdl.plan_id
3223: AND mt.mode_of_transport = p_mode_of_transport
3224: AND mt.trip_id IN (
3225: SELECT ts.trip_id
3226: FROM mst_trip_stops ts,
3227: mst_delivery_legs mdl
3228: WHERE ts.plan_id = p_plan_id
3229: AND ts.stop_location_id = p_fac_loc_id
3230: AND ts.plan_Id = mdl.plan_Id
3231: AND ( ts.stop_id = mdl.pick_up_stop_id
3242: WHERE mts.plan_id = mt.plan_id
3243: AND mts.trip_id = mt.trip_id
3244: AND mts.stop_location_id = p_fac_loc_id)
3245: AND NOT EXISTS (SELECT 1
3246: FROM mst_delivery_legs mdl
3247: WHERE mdl.plan_id = mt.plan_id
3248: AND mdl.trip_id = mt.trip_id);
3249:
3250: CURSOR get_trips_load IS
3254: AND mt.mode_of_transport = p_mode_of_transport
3255: AND mt.trip_id IN (
3256: SELECT ts.trip_id
3257: FROM mst_trip_stops ts,
3258: mst_delivery_legs mdl
3259: WHERE ts.plan_id = p_plan_id
3260: AND ts.stop_location_id = p_fac_loc_id
3261: AND ts.stop_id = mdl.pick_up_stop_id
3262: AND ts.plan_id = mdl.plan_id);
3268: AND mt.mode_of_transport = p_mode_of_transport
3269: AND mt.trip_id IN (
3270: SELECT ts.trip_id
3271: FROM mst_trip_stops ts,
3272: mst_delivery_legs mdl
3273: WHERE ts.plan_id = p_plan_id
3274: AND ts.stop_location_id = p_fac_loc_id
3275: AND ts.stop_id = mdl.drop_off_stop_id
3276: AND ts.plan_id = mdl.plan_id);
3336: (SELECT mda.delivery_detail_id
3337: FROM mst_delivery_assignments mda,
3338: mst_trips mt,
3339: mst_trip_stops mts,
3340: mst_delivery_legs mdl,
3341: mst_deliveries md
3342: WHERE mda.plan_id = mdd.plan_id
3343: AND mda.plan_id = md.plan_id
3344: AND mda.delivery_id = md.delivery_id
3368: ( SELECT mda.delivery_detail_id
3369: FROM mst_delivery_assignments mda,
3370: mst_deliveries md,
3371: mst_trips mt,
3372: mst_delivery_legs mdl
3373: WHERE mda.plan_id = mdd.plan_id
3374: AND mda.plan_id = md.plan_id
3375: AND mda.delivery_id = md.delivery_id
3376: AND md.plan_id = mdl.plan_id
3396: ( SELECT mda.delivery_detail_id
3397: FROM mst_delivery_assignments mda,
3398: mst_deliveries md,
3399: mst_trips mt,
3400: mst_delivery_legs mdl
3401: WHERE mda.plan_id = mdd.plan_id
3402: AND mda.plan_id = md.plan_id
3403: AND mda.delivery_id = md.delivery_id
3404: AND md.plan_id = mdl.plan_id
3423: ( SELECT mda.delivery_detail_id
3424: FROM mst_delivery_assignments mda,
3425: mst_deliveries md,
3426: mst_trips mt,
3427: mst_delivery_legs mdl
3428: WHERE mda.plan_id = mdd.plan_id
3429: AND mda.plan_id = md.plan_id
3430: AND mda.delivery_id = md.delivery_id
3431: AND md.plan_id = mdl.plan_id
3453: and md.delivery_id in
3454: (select mdl.delivery_id
3455: from mst_trips t,
3456: mst_trip_stops ts,
3457: mst_delivery_legs mdl
3458: where mdl.plan_id = md.plan_id
3459: and ts.plan_id = mdl.plan_id
3460: and (ts.stop_id = mdl.pick_up_stop_id
3461: or ts.stop_id = mdl.drop_off_stop_id)
3474: select count(mdd.delivery_detail_id)
3475: into l_total_orders
3476: from mst_trips mt,
3477: mst_trip_stops mts,
3478: mst_delivery_legs mdl,
3479: mst_deliveries md,
3480: mst_delivery_details mdd,
3481: mst_delivery_assignments mda
3482: where mt.plan_id = p_plan_id
3502: /*select count(mdd.delivery_detail_id)
3503: into l_total_orders
3504: from mst_trips mt,
3505: mst_trip_stops mts,
3506: mst_delivery_legs mdl,
3507: mst_deliveries md,
3508: mst_delivery_details mdd,
3509: mst_delivery_assignments mda
3510: where mt.plan_id = p_plan_id
3529: AND MD.DELIVERY_ID IN
3530: (SELECT MDL.DELIVERY_ID
3531: FROM MST_TRIPS T,
3532: MST_TRIP_STOPS TS,
3533: MST_DELIVERY_LEGS MDL
3534: WHERE MDL.PLAN_ID = MD.PLAN_ID
3535: AND TS.PLAN_ID = MDL.PLAN_ID
3536: AND TS.STOP_ID = MDL.PICK_UP_STOP_ID
3537: AND TS.STOP_LOCATION_ID = p_my_fac_location_id
3554: /*select count(mdd.delivery_detail_id)
3555: into l_total_orders
3556: from mst_trips mt,
3557: mst_trip_stops mts,
3558: mst_delivery_legs mdl,
3559: mst_deliveries md,
3560: mst_delivery_details mdd,
3561: mst_delivery_assignments mda
3562: where mt.plan_id = p_plan_id
3580: AND MD.DELIVERY_ID IN
3581: (SELECT MDL.DELIVERY_ID
3582: FROM MST_TRIPS T,
3583: MST_TRIP_STOPS TS,
3584: MST_DELIVERY_LEGS MDL
3585: WHERE MDL.PLAN_ID = MD.PLAN_ID
3586: AND TS.PLAN_ID = MDL.PLAN_ID
3587: AND TS.STOP_ID = MDL.DROP_OFF_STOP_ID
3588: AND TS.STOP_LOCATION_ID = p_my_fac_location_id
3631: NVL(mdl.allocated_fac_shp_hand_cost,0) )
3632: INTO l_loading_cost
3633: FROM mst_trips mt,
3634: mst_trip_stops mts,
3635: mst_delivery_legs mdl
3636: WHERE mt.plan_id = p_plan_id
3637: AND mt.mode_of_transport = p_mode
3638: AND mt.plan_id = mts.plan_id
3639: AND mt.trip_id = mts.trip_id
3644: SELECT SUM(NVL(mdl.allocated_fac_loading_cost,0) +
3645: NVL(mdl.allocated_transport_cost,0) +
3646: NVL(mdl.allocated_fac_shp_hand_cost,0) )
3647: INTO l_loading_cost
3648: FROM mst_delivery_legs mdl
3649: WHERE mdl.plan_id = p_plan_id
3650: AND mdl.delivery_id IN
3651: ( SELECT md.delivery_id
3652: FROM mst_delivery_legs mdl1,
3648: FROM mst_delivery_legs mdl
3649: WHERE mdl.plan_id = p_plan_id
3650: AND mdl.delivery_id IN
3651: ( SELECT md.delivery_id
3652: FROM mst_delivery_legs mdl1,
3653: mst_deliveries md,
3654: mst_trips mt,
3655: mst_trip_stops mts
3656: WHERE mt.plan_id = mdl1.plan_id
3670: SELECT SUM(NVL(mdl.allocated_fac_loading_cost,0) +
3671: NVL(mdl.allocated_transport_cost,0) +
3672: NVL(mdl.allocated_fac_shp_hand_cost,0) )
3673: INTO l_loading_cost_temp
3674: FROM mst_delivery_legs mdl
3675: WHERE mdl.plan_id = p_plan_id
3676: AND mdl.delivery_id IN
3677: ( SELECT md.delivery_id
3678: FROM mst_delivery_legs mdl1,
3674: FROM mst_delivery_legs mdl
3675: WHERE mdl.plan_id = p_plan_id
3676: AND mdl.delivery_id IN
3677: ( SELECT md.delivery_id
3678: FROM mst_delivery_legs mdl1,
3679: mst_deliveries md,
3680: mst_trips mt,
3681: mst_trip_stops mts
3682: WHERE mt.plan_id = mdl1.plan_id
3734: NVL(mdl.allocated_fac_rec_hand_cost,0) )
3735: into l_unloading_cost
3736: from mst_trips mt,
3737: mst_trip_stops mts,
3738: mst_delivery_legs mdl
3739: where mt.plan_id = p_plan_id
3740: and mt.mode_of_transport = p_mode
3741: and mt.plan_id = mts.plan_id
3742: and mt.trip_id = mts.trip_id
3747: SELECT SUM(NVL(mdl.allocated_fac_unloading_cost,0) +
3748: NVL(mdl.allocated_transport_cost,0) +
3749: NVL(mdl.allocated_fac_rec_hand_cost,0) )
3750: INTO l_unloading_Cost
3751: FROM mst_delivery_legs mdl
3752: WHERE mdl.plan_id = p_plan_id
3753: AND mdl.delivery_id IN
3754: ( SELECT md.delivery_id
3755: FROM mst_delivery_legs mdl1,
3751: FROM mst_delivery_legs mdl
3752: WHERE mdl.plan_id = p_plan_id
3753: AND mdl.delivery_id IN
3754: ( SELECT md.delivery_id
3755: FROM mst_delivery_legs mdl1,
3756: mst_deliveries md,
3757: mst_trips mt,
3758: mst_trip_stops mts
3759: WHERE mt.plan_id = mdl1.plan_id
3775: SELECT SUM(NVL(mdl.allocated_fac_loading_cost,0) +
3776: NVL(mdl.allocated_transport_cost,0) +
3777: NVL(mdl.allocated_fac_shp_hand_cost,0) )
3778: INTO l_unloading_cost_temp
3779: FROM mst_delivery_legs mdl
3780: WHERE mdl.plan_id = p_plan_id
3781: AND mdl.delivery_id IN
3782: ( SELECT md.delivery_id
3783: FROM mst_delivery_legs mdl1,
3779: FROM mst_delivery_legs mdl
3780: WHERE mdl.plan_id = p_plan_id
3781: AND mdl.delivery_id IN
3782: ( SELECT md.delivery_id
3783: FROM mst_delivery_legs mdl1,
3784: mst_deliveries md,
3785: mst_trips mt,
3786: mst_trip_stops mts
3787: WHERE mt.plan_id = mdl1.plan_id
3820: cursor departing_delivery_leg is
3821: select sum(mdl.allocated_fac_loading_cost + mdl.ALLOCATED_FAC_SHP_HAND_COST + mdl.allocated_transport_cost) total_departing_cost
3822: from mst_trips mt,
3823: mst_trip_stops mts,
3824: mst_delivery_legs mdl
3825: where mt.plan_id = p_plan_id
3826: and mt.mode_of_transport = p_mode
3827: and mt.trip_id = mts.trip_id
3828: and mdl.pick_up_stop_id = mts.stop_id
3831: cursor arriving_delivery_leg is
3832: select sum(mdl.allocated_fac_unloading_cost + mdl.ALLOCATED_FAC_REC_HAND_COST+ mdl.allocated_transport_cost) total_arriving_cost
3833: from mst_trips mt,
3834: mst_trip_stops mts,
3835: mst_delivery_legs mdl
3836: where mt.plan_id = p_plan_id
3837: and mt.mode_of_transport = p_mode
3838: and mt.trip_id = mts.trip_id
3839: and mdl.drop_off_stop_id = mts.stop_id
3864: select sum(md.gross_weight)
3865: into l_loading_weight
3866: from mst_trips mt,
3867: mst_trip_stops mts,
3868: mst_delivery_legs mdl,
3869: mst_deliveries md
3870: where mt.plan_id = p_plan_id
3871: and mt.mode_of_transport = p_mode
3872: and mt.plan_id = mts.plan_id
3898: select sum(md.gross_weight)
3899: into l_unloading_weight
3900: from mst_trips mt,
3901: mst_trip_stops mts,
3902: mst_delivery_legs mdl,
3903: mst_deliveries md
3904: where mt.plan_id = p_plan_id
3905: and mt.mode_of_transport = p_mode
3906: and mt.plan_id = mts.plan_id
3932: select sum(md.volume)
3933: into l_loading_cube
3934: from mst_trips mt,
3935: mst_trip_stops mts,
3936: mst_delivery_legs mdl,
3937: mst_deliveries md
3938: where mt.plan_id = p_plan_id
3939: and mt.mode_of_transport = p_mode
3940: and mt.plan_id = mts.plan_id
3966: select sum(md.volume)
3967: into l_unloading_cube
3968: from mst_trips mt,
3969: mst_trip_stops mts,
3970: mst_delivery_legs mdl,
3971: mst_deliveries md
3972: where mt.plan_id = p_plan_id
3973: and mt.mode_of_transport = p_mode
3974: and mt.plan_id = mts.plan_id
4000: select sum(md.number_of_pallets)
4001: into l_loading_pallets
4002: from mst_trips mt,
4003: mst_trip_stops mts,
4004: mst_delivery_legs mdl,
4005: mst_deliveries md
4006: where mt.plan_id = p_plan_id
4007: and mt.mode_of_transport = p_mode
4008: and mt.plan_id = mts.plan_id
4034: select sum(md.number_of_pallets)
4035: into l_unloading_pallets
4036: from mst_trips mt,
4037: mst_trip_stops mts,
4038: mst_delivery_legs mdl,
4039: mst_deliveries md
4040: where mt.plan_id = p_plan_id
4041: and mt.mode_of_transport = p_mode
4042: and mt.plan_id = mts.plan_id
4068: select sum(md.number_of_pieces)
4069: into l_loading_pieces
4070: from mst_trips mt,
4071: mst_trip_stops mts,
4072: mst_delivery_legs mdl,
4073: mst_deliveries md
4074: where mt.plan_id = p_plan_id
4075: and mt.mode_of_transport = p_mode
4076: and mt.plan_id = mts.plan_id
4102: SELECT sum(md.number_of_pieces)
4103: into l_unloading_pieces
4104: from mst_trips mt,
4105: mst_trip_stops mts,
4106: mst_delivery_legs mdl,
4107: mst_deliveries md
4108: where mt.plan_id = p_plan_id
4109: and mt.mode_of_transport = p_mode
4110: and mt.plan_id = mts.plan_id
4309: mdl.pick_up_stop_id,
4310: mdl.allocated_fac_loading_cost,
4311: mdl.drop_off_stop_id,
4312: mdl.allocated_fac_unloading_cost,0))
4313: FROM mst_delivery_legs mdl,
4314: mst_trip_stops mts,
4315: mst_trips mt
4316: WHERE mdl.plan_id = p_plan_id
4317: AND mts.plan_id = mdl.plan_id
4337: mdl.pick_up_stop_id,
4338: mdl.allocated_fac_shp_hand_cost,
4339: mdl.drop_off_stop_id,
4340: mdl.allocated_fac_Rec_hand_cost,0) )
4341: FROM mst_delivery_legs mdl,
4342: mst_trip_stops mts,
4343: mst_trips mt
4344: WHERE mdl.plan_id = p_plan_id
4345: AND mt.plan_id = mdl.plan_id
4392: FROM mst_deliveries md
4393: WHERE md.plan_id = p_plan_id
4394: AND md.delivery_id IN
4395: (SELECT mdl.delivery_id
4396: FROM mst_delivery_legs mdl,
4397: mst_trips mt,
4398: mst_trip_stops mts
4399: WHERE mdl.plan_id = md.plan_id
4400: AND mts.plan_Id = mdl.plan_id
4415: FROM mst_deliveries md
4416: WHERE md.plan_id = p_plan_id
4417: AND md.delivery_id IN
4418: (SELECT mdl.delivery_id
4419: FROM mst_delivery_legs mdl,
4420: mst_trips mt,
4421: mst_trip_stops mts
4422: WHERE mdl.plan_id = md.plan_id
4423: AND mts.plan_id = mdl.plan_id
4444: FROM mst_deliveries md
4445: WHERE md.plan_id = p_plan_id
4446: AND md.delivery_id IN
4447: (SELECT mdl.delivery_id
4448: FROM mst_delivery_legs mdl,
4449: mst_trips mt,
4450: mst_trip_stops mts
4451: WHERE mdl.plan_id = md.plan_id
4452: AND mts.plan_Id = mdl.plan_id
4463: FROM mst_deliveries md
4464: WHERE md.plan_id = p_plan_id
4465: AND md.delivery_id IN
4466: (SELECT mdl.delivery_id
4467: FROM mst_delivery_legs mdl,
4468: mst_trips mt,
4469: mst_trip_stops mts
4470: WHERE mdl.plan_id = md.plan_id
4471: AND mts.plan_Id = mdl.plan_id
4483: FROM mst_deliveries md
4484: WHERE md.plan_id = p_plan_id
4485: AND md.delivery_id IN
4486: (SELECT mdl.delivery_id
4487: FROM mst_delivery_legs mdl,
4488: mst_trips mt,
4489: mst_trip_stops mts
4490: WHERE mdl.plan_id = md.plan_id
4491: AND mts.plan_Id = mdl.plan_id
4512: FROM mst_deliveries md
4513: WHERE md.plan_id = p_plan_id
4514: AND md.delivery_id IN
4515: (SELECT mdl.delivery_id
4516: FROM mst_delivery_legs mdl,
4517: mst_trips mt,
4518: mst_trip_stops mts
4519: WHERE mdl.plan_id = md.plan_id
4520: AND mts.plan_Id = mdl.plan_id
4530: FROM mst_deliveries md
4531: WHERE md.plan_id = p_plan_id
4532: AND md.delivery_id IN
4533: (SELECT mdl.delivery_id
4534: FROM mst_delivery_legs mdl,
4535: mst_trips mt,
4536: mst_trip_stops mts
4537: WHERE mdl.plan_id = md.plan_id
4538: AND mts.plan_Id = mdl.plan_id
4549: FROM mst_deliveries md
4550: WHERE md.plan_id = p_plan_id
4551: AND md.delivery_id IN
4552: (SELECT mdl.delivery_id
4553: FROM mst_delivery_legs mdl,
4554: mst_trips mt,
4555: mst_trip_stops mts
4556: WHERE mdl.plan_id = md.plan_id
4557: AND mts.plan_Id = mdl.plan_id
4597: FROM mst_deliveries md
4598: WHERE md.plan_id = p_plan_id
4599: AND md.delivery_id IN
4600: (SELECT mdl.delivery_id
4601: FROM mst_delivery_legs mdl,
4602: mst_trips mt,
4603: mst_trip_stops mts
4604: WHERE mdl.plan_id = md.plan_id
4605: AND mts.plan_Id = mdl.plan_id
4620: FROM mst_deliveries md
4621: WHERE md.plan_id = p_plan_id
4622: AND md.delivery_id IN
4623: (SELECT mdl.delivery_id
4624: FROM mst_delivery_legs mdl,
4625: mst_trips mt,
4626: mst_trip_stops mts
4627: WHERE mdl.plan_id = md.plan_id
4628: AND mts.plan_id = mdl.plan_id
4649: FROM mst_deliveries md
4650: WHERE md.plan_id = p_plan_id
4651: AND md.delivery_id IN
4652: (SELECT mdl.delivery_id
4653: FROM mst_delivery_legs mdl,
4654: mst_trips mt,
4655: mst_trip_stops mts
4656: WHERE mdl.plan_id = md.plan_id
4657: AND mts.plan_Id = mdl.plan_id
4668: FROM mst_deliveries md
4669: WHERE md.plan_id = p_plan_id
4670: AND md.delivery_id IN
4671: (SELECT mdl.delivery_id
4672: FROM mst_delivery_legs mdl,
4673: mst_trips mt,
4674: mst_trip_stops mts
4675: WHERE mdl.plan_id = md.plan_id
4676: AND mts.plan_Id = mdl.plan_id
4688: FROM mst_deliveries md
4689: WHERE md.plan_id = p_plan_id
4690: AND md.delivery_id IN
4691: (SELECT mdl.delivery_id
4692: FROM mst_delivery_legs mdl,
4693: mst_trips mt,
4694: mst_trip_stops mts
4695: WHERE mdl.plan_id = md.plan_id
4696: AND mts.plan_Id = mdl.plan_id
4717: FROM mst_deliveries md
4718: WHERE md.plan_id = p_plan_id
4719: AND md.delivery_id IN
4720: (SELECT mdl.delivery_id
4721: FROM mst_delivery_legs mdl,
4722: mst_trips mt,
4723: mst_trip_stops mts
4724: WHERE mdl.plan_id = md.plan_id
4725: AND mts.plan_Id = mdl.plan_id
4735: FROM mst_deliveries md
4736: WHERE md.plan_id = p_plan_id
4737: AND md.delivery_id IN
4738: (SELECT mdl.delivery_id
4739: FROM mst_delivery_legs mdl,
4740: mst_trips mt,
4741: mst_trip_stops mts
4742: WHERE mdl.plan_id = md.plan_id
4743: AND mts.plan_Id = mdl.plan_id
4754: FROM mst_deliveries md
4755: WHERE md.plan_id = p_plan_id
4756: AND md.delivery_id IN
4757: (SELECT mdl.delivery_id
4758: FROM mst_delivery_legs mdl,
4759: mst_trips mt,
4760: mst_trip_stops mts
4761: WHERE mdl.plan_id = md.plan_id
4762: AND mts.plan_Id = mdl.plan_id
4800: FROM mst_deliveries md
4801: WHERE md.plan_id = p_plan_id
4802: AND md.delivery_id IN
4803: (SELECT mdl.delivery_id
4804: FROM mst_delivery_legs mdl,
4805: mst_trips mt,
4806: mst_trip_stops mts
4807: WHERE mdl.plan_id = md.plan_id
4808: AND mts.plan_Id = mdl.plan_id
4823: FROM mst_deliveries md
4824: WHERE md.plan_id = p_plan_id
4825: AND md.delivery_id IN
4826: (SELECT mdl.delivery_id
4827: FROM mst_delivery_legs mdl,
4828: mst_trips mt,
4829: mst_trip_stops mts
4830: WHERE mdl.plan_id = md.plan_id
4831: AND mts.plan_id = mdl.plan_id
4852: FROM mst_deliveries md
4853: WHERE md.plan_id = p_plan_id
4854: AND md.delivery_id IN
4855: (SELECT mdl.delivery_id
4856: FROM mst_delivery_legs mdl,
4857: mst_trips mt,
4858: mst_trip_stops mts
4859: WHERE mdl.plan_id = md.plan_id
4860: AND mts.plan_Id = mdl.plan_id
4871: FROM mst_deliveries md
4872: WHERE md.plan_id = p_plan_id
4873: AND md.delivery_id IN
4874: (SELECT mdl.delivery_id
4875: FROM mst_delivery_legs mdl,
4876: mst_trips mt,
4877: mst_trip_stops mts
4878: WHERE mdl.plan_id = md.plan_id
4879: AND mts.plan_Id = mdl.plan_id
4891: FROM mst_deliveries md
4892: WHERE md.plan_id = p_plan_id
4893: AND md.delivery_id IN
4894: (SELECT mdl.delivery_id
4895: FROM mst_delivery_legs mdl,
4896: mst_trips mt,
4897: mst_trip_stops mts
4898: WHERE mdl.plan_id = md.plan_id
4899: AND mts.plan_Id = mdl.plan_id
4920: FROM mst_deliveries md
4921: WHERE md.plan_id = p_plan_id
4922: AND md.delivery_id IN
4923: (SELECT mdl.delivery_id
4924: FROM mst_delivery_legs mdl,
4925: mst_trips mt,
4926: mst_trip_stops mts
4927: WHERE mdl.plan_id = md.plan_id
4928: AND mts.plan_Id = mdl.plan_id
4938: FROM mst_deliveries md
4939: WHERE md.plan_id = p_plan_id
4940: AND md.delivery_id IN
4941: (SELECT mdl.delivery_id
4942: FROM mst_delivery_legs mdl,
4943: mst_trips mt,
4944: mst_trip_stops mts
4945: WHERE mdl.plan_id = md.plan_id
4946: AND mts.plan_Id = mdl.plan_id
4957: FROM mst_deliveries md
4958: WHERE md.plan_id = p_plan_id
4959: AND md.delivery_id IN
4960: (SELECT mdl.delivery_id
4961: FROM mst_delivery_legs mdl,
4962: mst_trips mt,
4963: mst_trip_stops mts
4964: WHERE mdl.plan_id = md.plan_id
4965: AND mts.plan_Id = mdl.plan_id
5003: FROM mst_deliveries md
5004: WHERE md.plan_id = p_plan_id
5005: AND md.delivery_id IN
5006: (SELECT mdl.delivery_id
5007: FROM mst_delivery_legs mdl,
5008: mst_trips mt,
5009: mst_trip_stops mts
5010: WHERE mdl.plan_id = md.plan_id
5011: AND mts.plan_Id = mdl.plan_id
5026: FROM mst_deliveries md
5027: WHERE md.plan_id = p_plan_id
5028: AND md.delivery_id IN
5029: (SELECT mdl.delivery_id
5030: FROM mst_delivery_legs mdl,
5031: mst_trips mt,
5032: mst_trip_stops mts
5033: WHERE mdl.plan_id = md.plan_id
5034: AND mts.plan_id = mdl.plan_id
5055: FROM mst_deliveries md
5056: WHERE md.plan_id = p_plan_id
5057: AND md.delivery_id IN
5058: (SELECT mdl.delivery_id
5059: FROM mst_delivery_legs mdl,
5060: mst_trips mt,
5061: mst_trip_stops mts
5062: WHERE mdl.plan_id = md.plan_id
5063: AND mts.plan_Id = mdl.plan_id
5074: FROM mst_deliveries md
5075: WHERE md.plan_id = p_plan_id
5076: AND md.delivery_id IN
5077: (SELECT mdl.delivery_id
5078: FROM mst_delivery_legs mdl,
5079: mst_trips mt,
5080: mst_trip_stops mts
5081: WHERE mdl.plan_id = md.plan_id
5082: AND mts.plan_Id = mdl.plan_id
5094: FROM mst_deliveries md
5095: WHERE md.plan_id = p_plan_id
5096: AND md.delivery_id IN
5097: (SELECT mdl.delivery_id
5098: FROM mst_delivery_legs mdl,
5099: mst_trips mt,
5100: mst_trip_stops mts
5101: WHERE mdl.plan_id = md.plan_id
5102: AND mts.plan_Id = mdl.plan_id
5123: FROM mst_deliveries md
5124: WHERE md.plan_id = p_plan_id
5125: AND md.delivery_id IN
5126: (SELECT mdl.delivery_id
5127: FROM mst_delivery_legs mdl,
5128: mst_trips mt,
5129: mst_trip_stops mts
5130: WHERE mdl.plan_id = md.plan_id
5131: AND mts.plan_Id = mdl.plan_id
5141: FROM mst_deliveries md
5142: WHERE md.plan_id = p_plan_id
5143: AND md.delivery_id IN
5144: (SELECT mdl.delivery_id
5145: FROM mst_delivery_legs mdl,
5146: mst_trips mt,
5147: mst_trip_stops mts
5148: WHERE mdl.plan_id = md.plan_id
5149: AND mts.plan_Id = mdl.plan_id
5160: FROM mst_deliveries md
5161: WHERE md.plan_id = p_plan_id
5162: AND md.delivery_id IN
5163: (SELECT mdl.delivery_id
5164: FROM mst_delivery_legs mdl,
5165: mst_trips mt,
5166: mst_trip_stops mts
5167: WHERE mdl.plan_id = md.plan_id
5168: AND mts.plan_Id = mdl.plan_id
5218: AND MD.DELIVERY_ID IN
5219: (SELECT MDL.DELIVERY_ID
5220: FROM MST_TRIPS T,
5221: MST_TRIP_STOPS TS,
5222: MST_DELIVERY_LEGS MDL
5223: WHERE MDL.PLAN_ID = MD.PLAN_ID
5224: AND TS.PLAN_ID = MDL.PLAN_ID
5225: AND ( TS.STOP_ID = MDL.PICK_UP_STOP_ID
5226: OR TS.STOP_ID = MDL.DROP_OFF_STOP_ID )
5243: AND MD.DELIVERY_ID IN
5244: (SELECT MDL.DELIVERY_ID
5245: FROM MST_TRIPS T,
5246: MST_TRIP_STOPS TS,
5247: MST_DELIVERY_LEGS MDL
5248: WHERE MDL.PLAN_ID = MD.PLAN_ID
5249: AND TS.PLAN_ID = MDL.PLAN_ID
5250: AND TS.STOP_ID = MDL.PICK_UP_STOP_ID
5251: AND TS.STOP_LOCATION_ID = P_FAC_LOCATION_ID
5269: AND MD.DELIVERY_ID IN
5270: (SELECT MDL.DELIVERY_ID
5271: FROM MST_TRIPS T,
5272: MST_TRIP_STOPS TS,
5273: MST_DELIVERY_LEGS MDL
5274: WHERE MDL.PLAN_ID = MD.PLAN_ID
5275: AND TS.PLAN_ID = MDL.PLAN_ID
5276: AND TS.STOP_ID = MDL.DROP_OFF_STOP_ID
5277: AND TS.STOP_LOCATION_ID = P_FAC_LOCATION_ID
5303: AND MD.DELIVERY_ID IN
5304: (SELECT MDL.DELIVERY_ID
5305: FROM MST_TRIPS T,
5306: MST_TRIP_STOPS TS,
5307: MST_DELIVERY_LEGS MDL
5308: WHERE MDL.PLAN_ID = MD.PLAN_ID
5309: AND TS.PLAN_ID = MDL.PLAN_ID
5310: AND ( TS.STOP_ID = MDL.PICK_UP_STOP_ID
5311: OR TS.STOP_ID = MDL.DROP_OFF_STOP_ID )
5328: AND mda.plan_id = mdd.plan_id
5329: AND mda.delivery_detail_id = mdd.delivery_detail_id
5330: AND md.delivery_id IN
5331: ( SELECT mdl.delivery_id
5332: FROM mst_delivery_legs mdl,
5333: mst_trip_stops mts
5334: WHERE mdl.plan_id = md.plan_id
5335: AND mdl.plan_id = mts.plan_id
5336: AND mdl.pick_up_stop_id = mts.stop_id
5350: AND mda.plan_id = mdd.plan_id
5351: AND mda.delivery_detail_id = mdd.delivery_detail_id
5352: AND md.delivery_id IN
5353: ( SELECT mdl.delivery_id
5354: FROM mst_delivery_legs mdl,
5355: mst_trip_stops mts
5356: WHERE mdl.plan_id = md.plan_id
5357: AND mdl.plan_id = mts.plan_id
5358: AND mdl.drop_off_stop_id = mts.stop_id
5374: AND MD.DELIVERY_ID IN
5375: (SELECT MDL.DELIVERY_ID
5376: --FROM MST_TRIPS T,
5377: FROM MST_TRIP_STOPS TS,
5378: MST_DELIVERY_LEGS MDL
5379: WHERE MDL.PLAN_ID = MD.PLAN_ID
5380: AND TS.PLAN_ID = MDL.PLAN_ID
5381: AND TS.STOP_ID = MDL.PICK_UP_STOP_ID
5382: AND TS.STOP_LOCATION_ID = P_FAC_LOCATION_ID)
5401: AND MD.DELIVERY_ID IN
5402: (SELECT MDL.DELIVERY_ID
5403: --FROM MST_TRIPS T,
5404: FROM MST_TRIP_STOPS TS,
5405: MST_DELIVERY_LEGS MDL
5406: WHERE MDL.PLAN_ID = MD.PLAN_ID
5407: AND TS.PLAN_ID = MDL.PLAN_ID
5408: AND TS.STOP_ID = MDL.DROP_OFF_STOP_ID
5409: AND TS.STOP_LOCATION_ID = P_FAC_LOCATION_ID)
5446: FROM mst_trips mt
5447: WHERE mt.plan_id = p_plan_id
5448: AND mt.trip_id IN
5449: (SELECT mdl.trip_id
5450: FROM mst_delivery_legs mdl,
5451: mst_trip_stops mts
5452: WHERE mdl.plan_id = mt.plan_id
5453: AND mts.plan_id = mdl.plan_id
5454: AND mts.stop_id = mdl.drop_off_stop_id
5467: WHERE mt.plan_id = p_plan_id
5468: AND mt.trip_id IN
5469: (SELECT mdl.trip_id
5470: FROM mst_deliveries md,
5471: mst_delivery_legs mdl,
5472: mst_trip_stops mts
5473: WHERE mdl.plan_id = mt.plan_id
5474: AND mts.plan_id = mdl.plan_id
5475: AND mts.stop_id = mdl.pick_up_stop_id
5487: FROM mst_trips mt
5488: WHERE mt.plan_id = p_plan_id
5489: AND mt.trip_id IN
5490: (SELECT mdl.trip_id
5491: FROM mst_delivery_legs mdl,
5492: mst_trip_stops mts
5493: WHERE mdl.plan_id = mt.plan_id
5494: AND mts.plan_id = mdl.plan_id
5495: AND ( mts.stop_id = mdl.pick_up_stop_id
5513: FROM mst_trips mt
5514: WHERE mt.plan_id = p_plan_id
5515: AND mt.trip_id IN
5516: (SELECT mdl.trip_id
5517: FROM mst_delivery_legs mdl,
5518: mst_trip_stops mts
5519: WHERE mdl.plan_id = mt.plan_id
5520: AND mts.plan_id = mdl.plan_id
5521: AND ( mts.stop_id = mdl.pick_up_stop_id
5528: FROM mst_trips mt
5529: WHERE mt.plan_id = p_plan_id
5530: AND mt.trip_id IN
5531: (SELECT mdl.trip_id
5532: FROM mst_delivery_legs mdl,
5533: mst_trip_stops mts
5534: WHERE mdl.plan_id = mt.plan_id
5535: AND mts.plan_id = mdl.plan_id
5536: AND mts.stop_id = mdl.pick_up_stop_id
5544: FROM mst_trips mt
5545: WHERE mt.plan_id = p_plan_id
5546: AND mt.trip_id IN
5547: (SELECT mdl.trip_id
5548: FROM mst_delivery_legs mdl,
5549: mst_trip_stops mts
5550: WHERE mdl.plan_id = mt.plan_id
5551: AND mts.plan_id = mdl.plan_id
5552: AND mts.stop_id = mdl.drop_off_stop_id
5593: FROM mst_trips mt
5594: WHERE mt.plan_id = p_plan_id
5595: AND mt.trip_id IN
5596: (SELECT mdl.trip_id
5597: FROM mst_delivery_legs mdl,
5598: mst_trip_stops mts
5599: WHERE mdl.plan_id = mt.plan_id
5600: AND mts.plan_id = mdl.plan_id
5601: AND mts.stop_id = mdl.pick_up_stop_Id
5615: FROM mst_trips mt
5616: WHERE mt.plan_id = p_plan_id
5617: AND mt.trip_id IN
5618: (SELECT mdl.trip_id
5619: FROM mst_delivery_legs mdl,
5620: mst_trip_stops mts
5621: WHERE mdl.plan_id = mt.plan_id
5622: AND mts.plan_id = mdl.plan_id
5623: AND mts.stop_id = mdl.drop_off_stop_id
5648: FROM mst_trips mt
5649: WHERE mt.plan_id = p_plan_id
5650: AND mt.trip_id IN
5651: (SELECT mdl.trip_id
5652: FROM mst_delivery_legs mdl,
5653: mst_trip_stops mts
5654: WHERE mdl.plan_id = mt.plan_id
5655: AND mts.plan_id = mdl.plan_id
5656: AND mts.stop_id = mdl.pick_up_stop_Id
5670: FROM mst_trips mt
5671: WHERE mt.plan_id = p_plan_id
5672: AND mt.trip_id IN
5673: (SELECT mdl.trip_id
5674: FROM mst_delivery_legs mdl,
5675: mst_trip_stops mts
5676: WHERE mdl.plan_id = mt.plan_id
5677: AND mts.plan_id = mdl.plan_id
5678: AND mts.stop_id = mdl.drop_off_stop_Id
5715: mdl.pick_up_stop_id,
5716: NVL(mdl.allocated_fac_shp_hand_cost,0),
5717: mdl.drop_off_stop_id,
5718: NVL(mdl.allocated_fac_Rec_hand_cost,0),0) ), 0)
5719: FROM mst_delivery_legs mdl,
5720: mst_trip_stops mts,
5721: mst_trips mt
5722: WHERE mdl.plan_id = p_plan_id
5723: AND mt.plan_id = mdl.plan_id
5743: mdl.pick_up_stop_id,
5744: NVL(mdl.allocated_fac_shp_hand_cost,0),
5745: mdl.drop_off_stop_id,
5746: NVL(mdl.allocated_fac_Rec_hand_cost,0),0) ), 0)
5747: FROM mst_delivery_legs mdl,
5748: mst_trip_stops mts,
5749: mst_trips mt
5750: WHERE mdl.plan_id = p_plan_id
5751: AND mt.plan_id = mdl.plan_id
5993: into l_total_weight
5994: from mst_deliveries md
5995: where md.plan_id = p_plan_id
5996: and exists (select 1
5997: from mst_delivery_legs mdl,
5998: mst_trip_stops mts,
5999: mst_trips mt
6000: where mdl.delivery_id = md.delivery_id
6001: and mdl.pick_up_stop_id = mts.stop_id
6016: into l_total_volume
6017: from mst_deliveries md
6018: where md.plan_id = p_plan_id
6019: and exists (select 1
6020: from mst_delivery_legs mdl,
6021: mst_trip_stops mts,
6022: mst_trips mt
6023: where mdl.delivery_id = md.delivery_id
6024: and mdl.pick_up_stop_id = mts.stop_id
6039: into l_total_pallets
6040: from mst_deliveries md
6041: where md.plan_id = p_plan_id
6042: and exists (select 1
6043: from mst_delivery_legs mdl,
6044: mst_trip_stops mts,
6045: mst_trips mt
6046: where mdl.delivery_id = md.delivery_id
6047: and mdl.pick_up_stop_id = mts.stop_id
6062: into l_total_pieces
6063: from mst_deliveries md
6064: where md.plan_id = p_plan_id
6065: and exists (select 1
6066: from mst_delivery_legs mdl,
6067: mst_trip_stops mts,
6068: mst_trips mt
6069: where mdl.delivery_id = md.delivery_id
6070: and mdl.pick_up_stop_id = mts.stop_id