[Home] [Help]
20: l_plan_order_count number;
21:
22: cursor cur_plan_orders (l_plan_id in number) is
23: select nvl(mp.total_orders,0)
24: from mst_plans mp
25: where mp.plan_id = l_plan_id;
26:
27:
28: cursor cur_plan_orders_myfac (l_plan_id in number
216: l_weight number;
217:
218: cursor cur_weight (l_plan_id in number) is
219: select nvl(mp.total_weight,0)
220: from mst_plans mp
221: where mp.plan_id = l_plan_id;
222:
223:
224: cursor cur_weight_myfac (l_plan_id in number
286: l_volume number;
287:
288: cursor cur_volume (l_plan_id in number) is
289: select nvl(mp.total_volume,0)
290: from mst_plans mp
291: where mp.plan_id = l_plan_id;
292:
293:
294: cursor cur_volume_myfac (l_plan_id in number
356: l_pieces number;
357:
358: cursor cur_pieces (l_plan_id in number) is
359: select nvl(mp.total_pieces,0)
360: from mst_plans mp
361: where mp.plan_id = l_plan_id;
362:
363:
364: cursor cur_pieces_myfac (l_plan_id in number
616: , l_mode in varchar2) is
617: select decode(l_mode,'TRUCK',nvl(mp.total_tl_cost,0)
618: ,'LTL' ,nvl(mp.total_ltl_cost,0)
619: ,nvl(mp.total_parcel_cost,0))
620: from mst_plans mp
621: where mp.plan_id = l_plan_id;
622:
623: cursor cur_trans_cost_myfac (l_plan_id in number
624: , l_myfac_id in number
764: cursor cur_handl_cost (l_plan_id in number
765: , l_mode in varchar2) is
766: select nvl(sum(nvl(mdl.allocated_fac_shp_hand_cost,0)
767: + nvl(mdl.allocated_fac_rec_hand_cost,0)),0)
768: from mst_plans mp
769: , mst_delivery_legs mdl
770: where mp.plan_id = mdl.plan_id
771: and mdl.trip_id in (select mt.trip_id
772: from mst_trips mt
912: + nvl(mdl.allocated_fac_unloading_cost,0)
913: + nvl(mdl.allocated_transport_cost,0)
914: + nvl(mdl.allocated_fac_shp_hand_cost,0)
915: + nvl(mdl.allocated_fac_rec_hand_cost,0)),0)
916: from mst_plans mp
917: , mst_delivery_legs mdl
918: where mp.plan_id = mdl.plan_id
919: and mdl.trip_id in (select mt.trip_id
920: from mst_trips mt
1546:
1547: cursor cur_carr_cost (l_plan_id in number
1548: , l_carrier_id in number) is
1549: select nvl(sum(nvl(mdl.allocated_transport_cost,0)),0)
1550: from mst_plans mp
1551: , mst_delivery_legs mdl
1552: where mp.plan_id = mdl.plan_id
1553: and mdl.trip_id in (select mt.trip_id
1554: from mst_trips mt
6054: +mst_reports_pkg.get_total_cost_per_mode(p_plan_id, p_report_for, p_report_for_id,'PARCEL')
6055: ) / decode( mst_reports_pkg.get_plan_value(p_plan_id, p_report_for, p_report_for_id)
6056: , 0, 1
6057: , mst_reports_pkg.get_plan_value(p_plan_id, p_report_for, p_report_for_id)) ) * 100
6058: from mst_plans mp
6059: where mp.plan_id = p_plan_id
6060: );
6061:
6062:
6115: from mst_trip_stops mts
6116: , mst_trips mt
6117: where mts.plan_id = mt.plan_id
6118: and mts.trip_id = mt.trip_id) temp,
6119: mst_plans mp,
6120: wsh_location_owners wlo,
6121: wsh_locations wl,
6122: fte_location_parameters fte
6123: where mp.plan_id = temp.plan_id
6217: ) / decode( nvl(mst_reports_pkg.get_plan_value(p_plan_id,p_report_for,p_report_for_id),0)
6218: , 0, 1
6219: , mst_reports_pkg.get_plan_value(p_plan_id,p_report_for,p_report_for_id) ) * 100
6220: , 0
6221: from mst_plans mp
6222: ,wsh_locations wl
6223: WHERE mp.plan_id = p_plan_id
6224: and wl.wsh_location_id in (select distinct md.pickup_location_id
6225: from mst_deliveries md
6285: ) / decode( nvl(mst_reports_pkg.get_plan_value(p_plan_id,p_report_for,p_report_for_id),0)
6286: , 0, 1
6287: , mst_reports_pkg.get_plan_value(p_plan_id,p_report_for,p_report_for_id) ) * 100
6288: , 0
6289: from mst_plans mp
6290: ,wsh_locations wl
6291: WHERE mp.plan_id = p_plan_id
6292: and wl.wsh_location_id in (select distinct md.dropoff_location_id
6293: from mst_deliveries md
6357: ) / decode( nvl(mst_reports_pkg.get_plan_value(p_plan_id,p_report_for,p_report_for_id),0)
6358: , 0, 1
6359: , mst_reports_pkg.get_plan_value(p_plan_id,p_report_for,p_report_for_id)) ) * 100
6360: ,0
6361: from mst_plans mp
6362: , hz_parties hzp
6363: , hz_cust_accounts hzc
6364: WHERE mp.plan_id = p_plan_id
6365: and hzp.party_id = hzc.party_id
6432: ) / decode( nvl(mst_reports_pkg.get_plan_value(p_plan_id,p_report_for,p_report_for_id),0)
6433: , 0, 1
6434: , mst_reports_pkg.get_plan_value(p_plan_id,p_report_for,p_report_for_id)) ) * 100
6435: ,0
6436: from mst_plans mp
6437: , po_vendors pov
6438: , hz_relationships hzr
6439: , hz_parties hz
6440: WHERE mp.plan_id = p_plan_id