[Home] [Help]
96: l_count number;
97: begin
98: select count(1)
99: into l_count
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
115: l_count number;
116: begin
117: select sum(gross_weight)
118: into l_count
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
133: l_count number;
134: begin
135: select sum(volume)
136: into l_count
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
152: l_count number;
153: begin
154: select sum(number_of_pieces)
155: into l_count
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
171: l_count number;
172: begin
173: select sum(number_of_pallets)
174: into l_count
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
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;
347: return l_loading_weight;
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;
365: return l_loading_volume;
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;
383: return l_loading_pallets;
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;
401: return l_loading_pieces;
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;
420: return l_unloading_weight;
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;
438: return l_unloading_volume;
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;
456: return l_unloading_pallets;
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;
474:
713: -- ----------------------------------
714: /*
715: SELECT SUM(md.gross_weight)
716: INTO l_carrier_weight
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,
739: -- for unload and load.
740: -- ----------------------------------------------------------------------------
741: SELECT SUM(md.gross_weight)
742: INTO l_carrier_weight
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,
760: l_carrier_weight := 0;
761:
762: SELECT SUM(md.gross_weight)
763: INTO l_carrier_weight
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,
794: -- ----------------------------------
795: /*********************
796: SELECT SUM(md.volume)
797: INTO l_carrier_volume
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,
820: -- for unload and load.
821: -- ----------------------------------------------------------------------------
822: SELECT SUM(md.volume)
823: INTO l_carrier_volume
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,
841: l_carrier_volume := 0;
842:
843: SELECT SUM(md.volume)
844: INTO l_carrier_volume
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,
876: -- ----------------------------------
877: /******************************
878: SELECT SUM(md.number_of_pallets)
879: INTO l_carrier_pallets
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,
902: -- for unload and load.
903: -- ----------------------------------------------------------------------------
904: SELECT SUM(md.number_of_pallets)
905: INTO l_carrier_pallets
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,
923: l_carrier_pallets := 0;
924:
925: SELECT SUM(md.number_of_pallets)
926: INTO l_carrier_pallets
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,
957: -- ----------------------------------
958: /******************************
959: SELECT SUM(md.number_of_pieces)
960: INTO l_carrier_pieces
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,
983: -- for unload and load.
984: -- ----------------------------------------------------------------------------
985: SELECT SUM(md.number_of_pieces)
986: INTO l_carrier_pieces
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,
1004: l_carrier_pieces := 0;
1005:
1006: SELECT SUM(md.number_of_pieces)
1007: INTO l_carrier_pieces
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,
1045: /*********************************************
1046: SELECT COUNT(DISTINCT mdd.SOURCE_HEADER_NUMBER)
1047: INTO l_carrier_orders
1048: FROM MST_DELIVERY_DETAILS MDD,
1049: MST_DELIVERIES MD,
1050: MST_DELIVERY_ASSIGNMENTS MDA
1051: WHERE MD.PLAN_ID = MDA.PLAN_ID
1052: AND MD.DELIVERY_ID = MDA.DELIVERY_ID
1053: AND MD.DELIVERY_ID IN
1078: -- ----------------------------------------------------------------------------
1079: SELECT COUNT(DISTINCT mdd.SOURCE_HEADER_NUMBER)
1080: INTO l_carrier_orders
1081: FROM mst_delivery_details mdd,
1082: mst_deliveries md,
1083: mst_delivery_assignments mda
1084: WHERE md.plan_id = p_plan_id
1085: and md.plan_id = mda.plan_id
1086: and md.delivery_id = mda.delivery_id
1105:
1106: SELECT COUNT(DISTINCT mdd.SOURCE_HEADER_NUMBER)
1107: INTO l_carrier_orders
1108: FROM mst_delivery_details mdd,
1109: mst_deliveries md,
1110: mst_delivery_assignments mda
1111: WHERE md.plan_id = p_plan_id
1112: and md.plan_id = mda.plan_id
1113: and md.delivery_id = mda.delivery_id
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
1196: and mt.mode_of_transport = p_mode
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
1223: and mt.mode_of_transport = p_mode
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
1249: and mt.mode_of_transport = p_mode
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
1275: and mt.mode_of_transport = p_mode
1301: mdl.ALLOCATED_FAC_SHP_HAND_COST +
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
1332: -- --------------------
1333: --select sum(md.gross_weight)
1334: /* select sum(md.net_weight)
1335: into l_total_weight
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
1349: select sum(nvl(mdd.net_weight,0))
1350: into l_total_weight
1351: from mst_delivery_details mdd
1352: , mst_delivery_assignments mda
1353: , mst_deliveries md
1354: where md.plan_id = mda.plan_id
1355: and md.delivery_id = mda.delivery_id
1356: and mda.plan_id = mdd.plan_id
1357: and mda.delivery_detail_id = mdd.delivery_detail_id
1379: -- both Assigned and Unassigned deliveries.
1380: -- ----------------------------------------
1381: /* select sum(md.volume)
1382: into l_total_volume
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
1396: select sum(nvl(mdd.volume,0))
1397: into l_total_volume
1398: from mst_delivery_details mdd
1399: , mst_delivery_assignments mda
1400: , mst_deliveries md
1401: where md.plan_id = mda.plan_id
1402: and md.delivery_id = mda.delivery_id
1403: and mda.plan_id = mdd.plan_id
1404: and mda.delivery_detail_id = mdd.delivery_detail_id
1424: -- both Assigned and Unassigned deliveries.
1425: -- ----------------------------------------
1426: /* select sum(md.number_of_pallets)
1427: into l_total_pallets
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
1441: select sum(ceil(nvl(mdd.number_of_pallets,0)))
1442: into l_total_pallets
1443: from mst_delivery_details mdd
1444: , mst_delivery_assignments mda
1445: , mst_deliveries md
1446: where md.plan_id = mda.plan_id
1447: and md.delivery_id = mda.delivery_id
1448: and mda.plan_id = mdd.plan_id
1449: and mda.delivery_detail_id = mdd.delivery_detail_id
1471: -- both Assigned and Unassigned deliveries.
1472: -- ----------------------------------------
1473: /* select sum(md.number_of_pieces)
1474: into l_total_pieces
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
1488: select sum(nvl(mdd.requested_quantity,0))
1489: into l_total_pieces
1490: from mst_delivery_details mdd
1491: , mst_delivery_assignments mda
1492: , mst_deliveries md
1493: where md.plan_id = mda.plan_id
1494: and md.delivery_id = mda.delivery_id
1495: and mda.plan_id = mdd.plan_id
1496: and mda.delivery_detail_id = mdd.delivery_detail_id
1535: ***/
1536: /*SELECT COUNT(DISTINCT mdd.SOURCE_HEADER_NUMBER)
1537: INTO l_total_orders
1538: FROM MST_DELIVERY_DETAILS MDD,
1539: MST_DELIVERIES MD,
1540: MST_DELIVERY_ASSIGNMENTS MDA
1541: WHERE MD.PLAN_ID = MDA.PLAN_ID
1542: AND MD.DELIVERY_ID = MDA.DELIVERY_ID
1543: AND MD.DELIVERY_ID IN
1556: INTO l_total_orders
1557: from (
1558: SELECT mdd.SOURCE_HEADER_NUMBER
1559: FROM MST_DELIVERY_DETAILS MDD,
1560: MST_DELIVERIES MD,
1561: MST_DELIVERY_ASSIGNMENTS MDA
1562: WHERE MD.PLAN_ID = MDA.PLAN_ID
1563: AND MD.DELIVERY_ID = MDA.DELIVERY_ID
1564: AND MD.DELIVERY_ID IN
1571: AND MD.CUSTOMER_ID = p_customer_id
1572: union all
1573: SELECT mdd.SOURCE_HEADER_NUMBER
1574: FROM MST_DELIVERY_DETAILS MDD,
1575: MST_DELIVERIES MD,
1576: MST_DELIVERY_ASSIGNMENTS MDA
1577: WHERE MD.PLAN_ID = MDA.PLAN_ID
1578: AND MD.DELIVERY_ID = MDA.DELIVERY_ID
1579: AND not exists
1597:
1598: return varchar2 is
1599: cursor mst_d is
1600: select delivery_id
1601: from mst_deliveries md
1602: where md.plan_id = p_plan_id
1603: and md.customer_id = p_customer_id;
1604:
1605: cursor mst_d_l_count (p_delivery_id NUMBER) is
1748:
1749: -- p_partner_type: 0 -- customer, 1 -- supplier
1750: cursor mst_d is
1751: select delivery_id
1752: from mst_deliveries md
1753: where md.plan_id = p_plan_id
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
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
1933: and mts.stop_location_id = md.dropoff_location_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
1960: and mts.stop_location_id = md.dropoff_location_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
1987: and mts.stop_location_id = md.dropoff_location_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
2007: and mts.stop_location_id = md.dropoff_location_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
2028: and mts.stop_location_id = md.pickup_location_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
2055: and mts.stop_location_id = md.pickup_location_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
2082: and mts.stop_location_id = md.pickup_location_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
2102: and mts.stop_location_id = md.pickup_location_id
2292: begin
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;
2322: -- --------------------
2323: --select sum(md.gross_weight)
2324: /* select sum(md.net_weight)
2325: into l_total_weight
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
2338: select sum(nvl(mdd.net_weight,0))
2339: into l_total_weight
2340: from mst_delivery_details mdd
2341: , mst_delivery_assignments mda
2342: , mst_deliveries md
2343: where md.plan_id = mda.plan_id
2344: and md.delivery_id = mda.delivery_id
2345: and mda.plan_id = mdd.plan_id
2346: and mda.delivery_detail_id = mdd.delivery_detail_id
2368: -- both Assigned and Unassigned deliveries.
2369: -- ----------------------------------------
2370: /* select sum(md.volume)
2371: into l_total_volume
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
2384: select sum(nvl(mdd.volume,0))
2385: into l_total_volume
2386: from mst_delivery_details mdd
2387: , mst_delivery_assignments mda
2388: , mst_deliveries md
2389: where md.plan_id = mda.plan_id
2390: and md.delivery_id = mda.delivery_id
2391: and mda.plan_id = mdd.plan_id
2392: and mda.delivery_detail_id = mdd.delivery_detail_id
2414: -- both Assigned and Unassigned deliveries.
2415: -- ----------------------------------------
2416: /* select sum(md.number_of_pallets)
2417: into l_total_pallets
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
2430: select sum(ceil(nvl(mdd.number_of_pallets,0)))
2431: into l_total_pallets
2432: from mst_delivery_details mdd
2433: , mst_delivery_assignments mda
2434: , mst_deliveries md
2435: where md.plan_id = mda.plan_id
2436: and md.delivery_id = mda.delivery_id
2437: and mda.plan_id = mdd.plan_id
2438: and mda.delivery_detail_id = mdd.delivery_detail_id
2460: -- both Assigned and Unassigned deliveries.
2461: -- ----------------------------------------
2462: /* select sum(md.number_of_pieces)
2463: into l_total_pieces
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
2476: select sum(nvl(mdd.requested_quantity,0))
2477: into l_total_pieces
2478: from mst_delivery_details mdd
2479: , mst_delivery_assignments mda
2480: , mst_deliveries md
2481: where md.plan_id = mda.plan_id
2482: and md.delivery_id = mda.delivery_id
2483: and mda.plan_id = mdd.plan_id
2484: and mda.delivery_detail_id = mdd.delivery_detail_id
2522: ***/
2523: /*SELECT COUNT(DISTINCT mdd.SOURCE_HEADER_NUMBER)
2524: INTO l_total_orders
2525: FROM MST_DELIVERY_DETAILS MDD,
2526: MST_DELIVERIES MD,
2527: MST_DELIVERY_ASSIGNMENTS MDA
2528: WHERE MD.PLAN_ID = MDA.PLAN_ID
2529: AND MD.DELIVERY_ID = MDA.DELIVERY_ID
2530: AND MD.DELIVERY_ID IN
2543: INTO l_total_orders
2544: from (
2545: SELECT mdd.SOURCE_HEADER_NUMBER
2546: FROM MST_DELIVERY_DETAILS MDD,
2547: MST_DELIVERIES MD,
2548: MST_DELIVERY_ASSIGNMENTS MDA
2549: WHERE MD.PLAN_ID = MDA.PLAN_ID
2550: AND MD.DELIVERY_ID = MDA.DELIVERY_ID
2551: AND MD.DELIVERY_ID IN
2558: AND MD.SUPPLIER_ID = p_supplier_id
2559: union all
2560: SELECT mdd.SOURCE_HEADER_NUMBER
2561: FROM MST_DELIVERY_DETAILS MDD,
2562: MST_DELIVERIES MD,
2563: MST_DELIVERY_ASSIGNMENTS MDA
2564: WHERE MD.PLAN_ID = MDA.PLAN_ID
2565: AND MD.DELIVERY_ID = MDA.DELIVERY_ID
2566: AND not exists
2590: begin
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
2623: -- --------------------
2624: --select sum(md.gross_weight)
2625: /* select sum(md.net_weight)
2626: into l_total_weight
2627: from mst_deliveries md
2628: where md.plan_id = p_plan_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);
2642: select sum(nvl(mdd.net_weight,0))
2643: into l_total_weight
2644: from mst_delivery_details mdd
2645: , mst_delivery_assignments mda
2646: , mst_deliveries md
2647: where md.plan_id = mda.plan_id
2648: and md.delivery_id = mda.delivery_id
2649: and mda.plan_id = mdd.plan_id
2650: and mda.delivery_detail_id = mdd.delivery_detail_id
2672: -- both Assigned and Unassigned deliveries.
2673: -- ----------------------------------------
2674: /* select sum(md.volume)
2675: into l_total_cube
2676: from mst_deliveries md
2677: where md.plan_id = p_plan_id
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);
2691: select sum(nvl(mdd.volume,0))
2692: into l_total_cube
2693: from mst_delivery_details mdd
2694: , mst_delivery_assignments mda
2695: , mst_deliveries md
2696: where md.plan_id = mda.plan_id
2697: and md.delivery_id = mda.delivery_id
2698: and mda.plan_id = mdd.plan_id
2699: and mda.delivery_detail_id = mdd.delivery_detail_id
2721: -- both Assigned and Unassigned deliveries.
2722: -- ----------------------------------------
2723: /* select sum(md.number_of_pallets)
2724: into l_total_pallets
2725: from mst_deliveries md
2726: where md.plan_id = p_plan_id
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);
2741: select sum(ceil(nvl(mdd.number_of_pallets,0)))
2742: into l_total_pallets
2743: from mst_delivery_details mdd
2744: , mst_delivery_assignments mda
2745: , mst_deliveries md
2746: where md.plan_id = mda.plan_id
2747: and md.delivery_id = mda.delivery_id
2748: and mda.plan_id = mdd.plan_id
2749: and mda.delivery_detail_id = mdd.delivery_detail_id
2771: -- both Assigned and Unassigned deliveries.
2772: -- ----------------------------------------
2773: /* select sum(md.number_of_pieces)
2774: into l_total_pieces
2775: from mst_deliveries md
2776: where md.plan_id = p_plan_id
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);
2792: select sum(nvl(mdd.requested_quantity,0))
2793: into l_total_pieces
2794: from mst_delivery_details mdd
2795: , mst_delivery_assignments mda
2796: , mst_deliveries md
2797: where md.plan_id = mda.plan_id
2798: and md.delivery_id = mda.delivery_id
2799: and mda.plan_id = mdd.plan_id
2800: and mda.delivery_detail_id = mdd.delivery_detail_id
2845: INTO l_total_orders
2846: FROM (
2847: SELECT mdd.SOURCE_HEADER_NUMBER
2848: FROM MST_DELIVERY_DETAILS MDD,
2849: MST_DELIVERIES MD,
2850: MST_DELIVERY_ASSIGNMENTS MDA
2851: WHERE MD.PLAN_ID = MDA.PLAN_ID
2852: AND MD.DELIVERY_ID = MDA.DELIVERY_ID
2853: AND MDA.PLAN_ID = MDD.PLAN_ID
2865: AND MTS.STOP_LOCATION_ID = P_LOCATION_ID)
2866: union ALL
2867: SELECT mdd.SOURCE_HEADER_NUMBER
2868: FROM MST_DELIVERY_DETAILS MDD,
2869: MST_DELIVERIES MD,
2870: MST_DELIVERY_ASSIGNMENTS MDA
2871: WHERE MD.PLAN_ID = MDA.PLAN_ID
2872: AND MD.DELIVERY_ID = MDA.DELIVERY_ID
2873: AND MDA.PLAN_ID = MDD.PLAN_ID
2883: INTO l_total_orders
2884: FROM (
2885: SELECT mdd.SOURCE_HEADER_NUMBER
2886: FROM MST_DELIVERY_DETAILS MDD,
2887: MST_DELIVERIES MD,
2888: MST_DELIVERY_ASSIGNMENTS MDA
2889: WHERE MD.PLAN_ID = MDA.PLAN_ID
2890: AND MD.DELIVERY_ID = MDA.DELIVERY_ID
2891: AND MDA.PLAN_ID = MDD.PLAN_ID
2903: AND MTS.STOP_LOCATION_ID = P_LOCATION_ID)
2904: union ALL
2905: SELECT mdd.SOURCE_HEADER_NUMBER
2906: FROM MST_DELIVERY_DETAILS MDD,
2907: MST_DELIVERIES MD,
2908: MST_DELIVERY_ASSIGNMENTS MDA
2909: WHERE MD.PLAN_ID = MDA.PLAN_ID
2910: AND MD.DELIVERY_ID = MDA.DELIVERY_ID
2911: AND MDA.PLAN_ID = MDD.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
2964: and mts.stop_location_id = md.dropoff_location_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
3001: and mts.stop_location_id = md.pickup_location_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
3040: and mts.stop_location_id = md.dropoff_location_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
3077: and mts.stop_location_id = md.pickup_location_id
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
3133: AND md.customer_id = p_partner_id
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
3149: AND md.delivery_id = mdl.delivery_id
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
3177: AND md.supplier_id = p_partner_id
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
3193: AND md.delivery_id = mdl.delivery_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
3345: AND md.plan_id = mdl.plan_id
3366: WHERE mdd.plan_id = p_plan_id
3367: AND mdd.delivery_detail_id IN
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
3394: WHERE mdd.plan_id = p_plan_id
3395: AND mdd.delivery_detail_id IN
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
3421: WHERE mdd.plan_id = p_plan_id
3422: AND mdd.delivery_detail_id IN
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
3445: if (p_activity_type is null and p_mode is null) then
3446: select count(distinct mdd.source_header_number)
3447: into l_total_orders
3448: from mst_delivery_details mdd,
3449: mst_deliveries md,
3450: mst_delivery_assignments mda
3451: where md.plan_id = mda.plan_id
3452: and md.delivery_id = mda.delivery_id
3453: and md.delivery_id in
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
3483: and mt.mode_of_transport = p_mode
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
3511: and mt.mode_of_transport = p_mode
3521:
3522: SELECT COUNT(DISTINCT mdd.SOURCE_HEADER_NUMBER)
3523: INTO l_total_orders
3524: FROM MST_DELIVERY_DETAILS MDD,
3525: MST_DELIVERIES MD,
3526: MST_DELIVERY_ASSIGNMENTS MDA
3527: WHERE MD.PLAN_ID = MDA.PLAN_ID
3528: AND MD.DELIVERY_ID = MDA.DELIVERY_ID
3529: AND MD.DELIVERY_ID IN
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
3563: and mt.mode_of_transport = p_mode
3572: and mdd.split_from_delivery_detail_id is null;*/
3573: SELECT COUNT(DISTINCT mdd.SOURCE_HEADER_NUMBER)
3574: INTO l_total_orders
3575: FROM MST_DELIVERY_DETAILS MDD,
3576: MST_DELIVERIES MD,
3577: MST_DELIVERY_ASSIGNMENTS MDA
3578: WHERE MD.PLAN_ID = MDA.PLAN_ID
3579: AND MD.DELIVERY_ID = MDA.DELIVERY_ID
3580: AND MD.DELIVERY_ID IN
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
3657: AND mt.trip_id = mdl1.trip_id
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
3683: AND mt.trip_id = mdl1.trip_id
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
3760: AND mt.trip_id = mdl1.trip_id
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
3788: AND mt.trip_id = mdl1.trip_id
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
3873: and mt.trip_id = mts.trip_id
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
3907: and mt.trip_id = mts.trip_id
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
3941: and mt.trip_id = mts.trip_id
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
3975: and mt.trip_id = mts.trip_id
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
4009: and mt.trip_id = mts.trip_id
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
4043: and mt.trip_id = mts.trip_id
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
4077: and mt.trip_id = mts.trip_id
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
4111: and mt.trip_id = mts.trip_id
4388: -- ---------------------------------------------
4389:
4390: SELECT SUM(md.gross_weight)
4391: INTO l_total_weight
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,
4411: -- for a specified carrier facility.
4412: -- ---------------------------------------------
4413: SELECT SUM(md.gross_weight)
4414: INTO l_total_weight
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,
4440: -- -------------------------------------
4441: /*
4442: SELECT SUM(md.gross_weight)
4443: INTO l_total_weight
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,
4459: AND mt.mode_of_transport = p_mode_of_transport);
4460: */
4461: SELECT SUM(md.gross_weight)
4462: INTO l_total_weight
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,
4479: l_total_weight := 0;
4480:
4481: SELECT SUM(md.gross_weight)
4482: INTO l_total_weight
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,
4508: -- ------------------------------------------------------------------
4509: /*************************
4510: SELECT SUM(md.gross_weight)
4511: INTO l_total_weight
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,
4526: AND mt.carrier_id = p_carrier_id);
4527: ***************************/
4528: SELECT SUM(md.gross_weight)
4529: INTO l_total_weight
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,
4545: l_total_weight := 0;
4546:
4547: SELECT SUM(md.gross_weight)
4548: INTO l_total_weight
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,
4593: -- for a specified carrier facility.
4594: -- ---------------------------------------------
4595: SELECT SUM(md.volume)
4596: INTO l_total_volume
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,
4616: -- for a specified carrier facility.
4617: -- ---------------------------------------------
4618: SELECT SUM(md.volume)
4619: INTO l_total_volume
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,
4645: -- -------------------------------------
4646: /*
4647: SELECT SUM(md.volume)
4648: INTO l_total_volume
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,
4664: AND mt.mode_of_transport = p_mode_of_transport);
4665: */
4666: SELECT SUM(md.volume)
4667: INTO l_total_volume
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,
4684: l_total_volume := 0;
4685:
4686: SELECT SUM(md.volume)
4687: INTO l_total_volume
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,
4713: -- ------------------------------------------------------------------
4714: /*************************
4715: SELECT SUM(md.volume)
4716: INTO l_total_volume
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,
4731: AND mt.carrier_id = p_carrier_id);
4732: *****************************/
4733: SELECT SUM(md.volume)
4734: INTO l_total_volume
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,
4750: l_total_volume := 0;
4751:
4752: SELECT SUM(md.volume)
4753: INTO l_total_volume
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,
4796: -- for a specified carrier facility.
4797: -- ---------------------------------------------
4798: SELECT SUM(md.number_of_pallets)
4799: INTO l_total_Pallets
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,
4819: -- for a specified carrier facility.
4820: -- ---------------------------------------------
4821: SELECT SUM(md.number_of_pallets)
4822: INTO l_total_Pallets
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,
4848: -- -------------------------------------
4849: /*
4850: SELECT SUM(md.number_of_pallets)
4851: INTO l_total_Pallets
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,
4867: --AND mt.mode_of_transport = p_mode_of_transport);
4868: */
4869: SELECT SUM(md.number_of_pallets)
4870: INTO l_total_Pallets
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,
4887: l_total_Pallets := 0;
4888:
4889: SELECT SUM(md.number_of_pallets)
4890: INTO l_total_Pallets
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,
4916: -- ------------------------------------------------------------------
4917: /*************************
4918: SELECT SUM(md.number_of_pallets)
4919: INTO l_total_Pallets
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,
4934: AND mt.carrier_id = p_carrier_id);
4935: ************************/
4936: SELECT SUM(md.number_of_pallets)
4937: INTO l_total_Pallets
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,
4953: l_total_Pallets := 0;
4954:
4955: SELECT SUM(md.number_of_pallets)
4956: INTO l_total_Pallets
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,
4999: -- for a specified carrier facility.
5000: -- ---------------------------------------------
5001: SELECT SUM(md.number_of_pieces)
5002: INTO l_total_Pieces
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,
5022: -- for a specified carrier facility.
5023: -- ---------------------------------------------
5024: SELECT SUM(md.number_of_pieces)
5025: INTO l_total_Pieces
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,
5051: -- -------------------------------------
5052: /*
5053: SELECT SUM(md.number_of_pieces)
5054: INTO l_total_Pieces
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,
5070: AND mt.mode_of_transport = p_mode_of_transport);
5071: */
5072: SELECT SUM(md.number_of_pieces)
5073: INTO l_total_Pieces
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,
5090: l_total_Pieces := 0;
5091:
5092: SELECT SUM(md.number_of_pieces)
5093: INTO l_total_Pieces
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,
5119: -- ------------------------------------------------------------------
5120: /*************************
5121: SELECT SUM(md.number_of_pieces)
5122: INTO l_total_Pieces
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,
5137: AND mt.carrier_id = p_carrier_id);
5138: *************************/
5139: SELECT SUM(md.number_of_pieces)
5140: INTO l_total_Pieces
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,
5156: l_total_Pieces := 0;
5157:
5158: SELECT SUM(md.number_of_pieces)
5159: INTO l_total_Pieces
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,
5210: /*
5211: SELECT COUNT(DISTINCT mdd.SOURCE_HEADER_NUMBER)
5212: INTO l_total_orders
5213: FROM MST_DELIVERY_DETAILS MDD,
5214: MST_DELIVERIES MD,
5215: MST_DELIVERY_ASSIGNMENTS MDA
5216: WHERE MD.PLAN_ID = MDA.PLAN_ID
5217: AND MD.DELIVERY_ID = MDA.DELIVERY_ID
5218: AND MD.DELIVERY_ID IN
5235: */
5236: SELECT COUNT(DISTINCT mdd.SOURCE_HEADER_NUMBER)
5237: INTO l_total_orders
5238: FROM MST_DELIVERY_DETAILS MDD,
5239: MST_DELIVERIES MD,
5240: MST_DELIVERY_ASSIGNMENTS MDA
5241: WHERE MD.PLAN_ID = MDA.PLAN_ID
5242: AND MD.DELIVERY_ID = MDA.DELIVERY_ID
5243: AND MD.DELIVERY_ID IN
5261:
5262: SELECT COUNT(DISTINCT mdd.SOURCE_HEADER_NUMBER)
5263: INTO l_total_orders
5264: FROM MST_DELIVERY_DETAILS MDD,
5265: MST_DELIVERIES MD,
5266: MST_DELIVERY_ASSIGNMENTS MDA
5267: WHERE MD.PLAN_ID = MDA.PLAN_ID
5268: AND MD.DELIVERY_ID = MDA.DELIVERY_ID
5269: AND MD.DELIVERY_ID IN
5295: /*******
5296: SELECT COUNT(DISTINCT mdd.SOURCE_HEADER_NUMBER)
5297: INTO l_total_orders
5298: FROM MST_DELIVERY_DETAILS MDD,
5299: MST_DELIVERIES MD,
5300: MST_DELIVERY_ASSIGNMENTS MDA
5301: WHERE MD.PLAN_ID = MDA.PLAN_ID
5302: AND MD.DELIVERY_ID = MDA.DELIVERY_ID
5303: AND MD.DELIVERY_ID IN
5319: ***********/
5320: SELECT COUNT(DISTINCT mdd.SOURCE_HEADER_NUMBER)
5321: INTO l_total_orders
5322: FROM mst_delivery_details mdd,
5323: mst_deliveries md,
5324: mst_delivery_assignments mda
5325: WHERE md.plan_id = p_plan_id
5326: AND md.plan_id = mda.plan_id
5327: AND md.delivery_id = mda.delivery_id
5341:
5342: SELECT COUNT(DISTINCT mdd.SOURCE_HEADER_NUMBER)
5343: INTO l_total_orders
5344: FROM mst_delivery_details mdd,
5345: mst_deliveries md,
5346: mst_delivery_assignments mda
5347: WHERE md.plan_id = p_plan_id
5348: AND md.plan_id = mda.plan_id
5349: AND md.delivery_id = mda.delivery_id
5366: -- ------------------------------------------ -
5367: SELECT COUNT(DISTINCT mdd.SOURCE_HEADER_NUMBER)
5368: INTO l_total_orders
5369: FROM MST_DELIVERY_DETAILS MDD,
5370: MST_DELIVERIES MD,
5371: MST_DELIVERY_ASSIGNMENTS MDA
5372: WHERE MD.PLAN_ID = MDA.PLAN_ID
5373: AND MD.DELIVERY_ID = MDA.DELIVERY_ID
5374: AND MD.DELIVERY_ID IN
5393: -- ------------------------------------------ -
5394: SELECT COUNT(DISTINCT mdd.SOURCE_HEADER_NUMBER)
5395: INTO l_total_orders
5396: FROM MST_DELIVERY_DETAILS MDD,
5397: MST_DELIVERIES MD,
5398: MST_DELIVERY_ASSIGNMENTS MDA
5399: WHERE MD.PLAN_ID = MDA.PLAN_ID
5400: AND MD.DELIVERY_ID = MDA.DELIVERY_ID
5401: AND MD.DELIVERY_ID IN
5466: FROM mst_trips mt
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
5832: hz_party_sites hzs,
5833: hz_cust_acct_sites_all hzcas,
5834: hz_cust_accounts hzca,
5835: hz_parties hzp,
5836: mst_deliveries md
5837: where md.plan_id = p_plan_id
5838: AND md.delivery_id = p_delivery_id
5839: and hzl.location_id = p_location_id
5840: and hzl.location_id = hzs.location_id
5990: l_total_weight number;
5991: begin
5992: select NVL(sum(nvl(md.gross_weight, 0)),0)
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,
6013: l_total_volume number;
6014: begin
6015: select NVL(sum(md.volume),0)
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,
6036: l_total_pallets number;
6037: begin
6038: select NVL(sum(nvl(md.number_of_pallets,0)), 0)
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,
6059: l_total_pieces number;
6060: begin
6061: select sum(nvl(md.number_of_pieces, 0))
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,