[Home] [Help]
248: /*skakani - commented on 16-10-03 ...
249: CURSOR cur_get_trip_orders is
250: SELECT COUNT(DISTINCT dd.source_header_number)
251: FROM mst_delivery_details dd,
252: mst_delivery_assignments da
253: WHERE dd.plan_id = p_plan_Id
254: AND dd.delivery_detail_id = da.delivery_detail_id
255: AND dd.plan_id = da.plan_id
256: AND da.delivery_id IN
261:
262: CURSOR cur_get_trip_orders is
263: SELECT COUNT(distinct nvl(dd.split_from_delivery_detail_id,dd.delivery_detail_id))
264: FROM mst_delivery_details dd,
265: mst_delivery_assignments da
266: WHERE dd.plan_id = p_plan_Id
267: AND dd.delivery_detail_id = da.delivery_detail_id
268: AND dd.plan_id = da.plan_id
269: AND da.delivery_id IN
415: WHERE mdd.plan_id = p_plan_id
416: AND mdd.delivery_detail_id IN
417: ( SELECT mda.delivery_detail_id
418: FROM mst_deliveries md,
419: mst_delivery_assignments mda,
420: mst_delivery_legs mdl
421: WHERE mda.plan_id = mdd.plan_id
422: AND mda.plan_id = md.plan_id
423: AND mda.delivery_id = md.delivery_id
439: FROM mst_delivery_details mdd
440: WHERE mdd.plan_id = p_plan_Id
441: AND mdd.delivery_detail_id IN
442: ( SELECT mda.delivery_detail_id
443: FROM mst_delivery_assignments mda,
444: mst_delivery_legs mdl
445: WHERE mda.plan_id = mdd.plan_id
446: AND mda.plan_id = mdl.plan_id
447: AND mda.delivery_id = mdl.delivery_id
453: FROM mst_delivery_details mdd
454: WHERE mdd.plan_id = p_plan_Id
455: AND mdd.delivery_detail_id IN
456: ( SELECT mda.delivery_detail_id
457: FROM mst_delivery_assignments mda,
458: mst_delivery_legs mdl
459: WHERE mda.plan_id = mdd.plan_id
460: AND mda.plan_id = mdl.plan_id
461: AND mda.delivery_id = mdl.delivery_id
473: FROM mst_delivery_details mdd
474: WHERE mdd.plan_id = p_plan_Id
475: AND mdd.delivery_detail_id IN
476: ( SELECT mda.delivery_detail_id
477: FROM mst_delivery_assignments mda,
478: mst_delivery_legs mdl
479: WHERE mda.plan_id = mdd.plan_id
480: AND mda.plan_id = mdl.plan_id
481: AND mda.delivery_id = mdl.delivery_id
526:
527: CURSOR Cur_Get_Orders IS
528: Select Count(distinct nvl(DD.split_from_delivery_Detail_id,DD.Delivery_Detail_Id))
529: FROM Mst_Delivery_Details DD,
530: Mst_Delivery_Assignments DA
531: WHERE DD.PLAN_ID = DA.PLAN_ID
532: AND DD.Delivery_Detail_Id = DA.Delivery_Detail_Id
533: AND DA.parent_delivery_detail_id is null
534: AND DA.Delivery_Id = p_delivery_id
2506:
2507: select sum(nvl(mdd.unit_price,0)* mdd.requested_quantity)
2508: into l_plan_value
2509: from mst_delivery_details mdd
2510: , mst_delivery_assignments mda
2511: where mdd.plan_id = p_plan_id
2512: and mda.plan_id = mdd.plan_id
2513: and mda.delivery_detail_id = mdd.delivery_detail_id
2514: and mda.parent_delivery_detail_id is null;
2552: mst_trip_stops mts,
2553: mst_delivery_legs mdl,
2554: mst_deliveries md,
2555: mst_delivery_details mdd,
2556: mst_delivery_assignments mda
2557: where mt.plan_id = p_plan_id
2558: and mt.mode_of_transport = p_mode
2559: and mt.trip_id = mts.trip_id
2560: and mt.trip_id = mdl.trip_id
2573: mst_trip_stops mts,
2574: mst_delivery_legs mdl,
2575: mst_deliveries md,
2576: mst_delivery_details mdd,
2577: mst_delivery_assignments mda
2578: where mt.plan_id = p_plan_id
2579: and mt.mode_of_transport = p_mode
2580: and mt.trip_id = mts.trip_id
2581: and mt.trip_id = mdl.trip_id
2593: mst_trip_stops mts,
2594: mst_delivery_legs mdl,
2595: mst_deliveries md,
2596: mst_delivery_details mdd,
2597: mst_delivery_assignments mda
2598: where mt.plan_id = p_plan_id
2599: and mt.mode_of_transport = p_mode
2600: and mt.trip_id = mts.trip_id
2601: and mt.trip_id = mdl.trip_id
2612: select count(distinct mdd.source_header_number)
2613: into l_total_orders
2614: from mst_delivery_details mdd,
2615: mst_deliveries md,
2616: mst_delivery_assignments mda
2617: where md.plan_id = mda.plan_id
2618: and md.delivery_id = mda.delivery_id
2619: and md.delivery_id in
2620: (select mdl.delivery_id
2832: select count(distinct mdd.source_header_number)
2833: into l_total_orders
2834: from mst_delivery_details mdd,
2835: mst_deliveries md,
2836: mst_delivery_assignments mda
2837: where md.plan_id = mda.plan_id
2838: and md.delivery_id = mda.delivery_id
2839: and md.delivery_id in
2840: (select mdl.delivery_id
2979: begin
2980: select count(distinct nvl(mdd.split_from_delivery_detail_id, mdd.delivery_detail_id))
2981: into l_count_origin_orders
2982: from mst_delivery_details mdd
2983: , mst_delivery_assignments mda
2984: , mst_deliveries md
2985: where md.plan_id = mda.plan_id
2986: and md.delivery_id = mda.delivery_id
2987: and md.pickup_location_id = p_origin_id
3224: begin
3225: select count(distinct nvl(mdd.split_from_delivery_detail_id, mdd.delivery_detail_id))
3226: into l_count_dest_orders
3227: from mst_delivery_details mdd
3228: , mst_delivery_assignments mda
3229: , mst_deliveries md
3230: where md.plan_id = mda.plan_id
3231: and md.delivery_id = mda.delivery_id
3232: and md.dropoff_location_id = p_dest_id
5729:
5730: delete from MST_CM_TRIPS
5731: where plan_id = p_plan_id;
5732:
5733: delete from MST_DELIVERY_ASSIGNMENTS
5734: where plan_id = p_plan_id;
5735:
5736: delete from MST_DELIVERY_DETAILS
5737: where plan_id = p_plan_id;
5821: function get_org_id(p_plan_id in number, p_delivery_id in number) return number is
5822: cursor cur_orgs is
5823: select mdd.organization_id
5824: from mst_deliveries md
5825: , mst_delivery_assignments mda
5826: , mst_delivery_details mdd
5827: where mdd.plan_id = mda.plan_id
5828: and mdd.delivery_detail_id = mda.delivery_detail_id
5829: and mda.plan_id = md.plan_id