1 PACKAGE BODY mst_cm_details AS
2 /*$Header: MSTCMDLB.pls 115.6 2004/07/30 09:59:58 bramacha noship $ */
3
4 function get_number_of_loads(
5 arg_plan_id number,
6 arg_continuous_move_id number
7 ) return number IS
8 -- local variables
9 l_num_of_loads number;
10 cursor cm_loads IS
11 select count(trip_id)
12 from mst_trips
13 where plan_id = arg_plan_id
14 and continuous_move_id = arg_continuous_move_id;
15 begin
16 open cm_loads;
17 fetch cm_loads into l_num_of_loads;
18 if cm_loads%NOTFOUND then
19 close cm_loads;
20 raise No_Data_Found;
21 end if;
22 close cm_loads;
23 return l_num_of_loads;
24 exception
25 when others then
26 return 0;
27 end get_number_of_loads;
28
29 function get_distance(
30 arg_plan_id number,
31 arg_continuous_move_id number
32 ) return number IS
33 -- local variables
34 l_distance number;
35 cursor cm_distance IS
36 select sum(total_trip_distance)
37 from mst_trips
38 where plan_id = arg_plan_id
39 and continuous_move_id = arg_continuous_move_id;
40 begin
41 open cm_distance;
42 fetch cm_distance into l_distance;
43 if cm_distance%NOTFOUND then
44 close cm_distance;
45 raise No_Data_Found;
46 end if;
47 close cm_distance;
48 return l_distance;
49 exception
50 when others then
51 return 0;
52 end get_distance;
53
54 function get_savings(
55 arg_plan_id number,
56 arg_continuous_move_id number,
57 arg_total_cm_trip_cost number
58 ) return number IS
59 -- local variables
60 l_savings number;
61 begin
62 select nvl(total_saving, 0)
63 into l_savings
64 from mst_cm_trips
65 where plan_id = arg_plan_id
66 and continuous_move_id = arg_continuous_move_id;
67 return l_savings;
68 exception
69 when NO_DATA_FOUND then
70 raise;
71 end get_savings;
72
73 function get_number_of_stops(
74 arg_plan_id number,
75 arg_trip_id number
76 ) return number IS
77 -- local variables
78 l_num_of_stops number;
79 begin
80 l_num_of_stops := 12;
81 return l_num_of_stops;
82 exception
83 when NO_DATA_FOUND then
84 raise;
85 end get_number_of_stops;
86
87 function get_total_savings(
88 arg_plan_id number
89 ) return number IS
90 -- local variables
91 l_savings number;
92 begin
93 l_savings := 12;
94 return l_savings;
95 exception
96 when NO_DATA_FOUND then
97 raise;
98 end get_total_savings;
99
100 function get_number_of_exceptions(
101 arg_plan_id number
102 ) return number IS
103 -- local variables
104 l_number_of_exceptions number := 0;
105 cursor num_of_excep is select count(1)
106 from mst_exception_details
107 where plan_id = arg_plan_id
108 and continuous_move_id is not null;
109
110 begin
111 open num_of_excep;
112 fetch num_of_excep into l_number_of_exceptions;
113 close num_of_excep;
114 return l_number_of_exceptions;
115 exception
116 when NO_DATA_FOUND then
117 return l_number_of_exceptions;
118 end get_number_of_exceptions;
119
120 function get_percent_of_tl_in_cm(
121 arg_plan_id number
122 ) return number IS
123 l_percent NUMBER := null;
124 l_tl_count NUMBER;
125 l_tl_in_cm_count NUMBER;
126 cursor tl_count is
127 select count(1) from mst_trips
128 where plan_id = arg_plan_id
129 and mode_of_transport = 'TRUCK'
130 --Bug_Fix for 3803450
131 -- and move_type = 2;
132 and nvl ( move_type, 2 ) = 2;
133
134 cursor tl_in_cm_count is
135 select count(1) from mst_trips
136 where plan_id = arg_plan_id
137 and mode_of_transport = 'TRUCK'
138 and continuous_move_id is not null
139 --Bug_Fix for 3803450
140 -- and move_type = 2;
141 and nvl ( move_type, 2 ) = 2;
142
143 BEGIN
144 OPEN tl_count;
145 FETCH tl_count into l_tl_count;
146 CLOSE tl_count;
147 if l_tl_count = 0 then
148 l_tl_count := 1;
149 end if;
150
151 OPEN tl_in_cm_count;
152 FETCH tl_in_cm_count into l_tl_in_cm_count;
153 CLOSE tl_in_cm_count;
154
155 l_percent := round(100*(l_tl_in_cm_count/l_tl_count), 0);
156 return l_percent;
157 end get_percent_of_tl_in_cm;
158
159 function get_trip_loading_status(
160 arg_plan_id number,
161 arg_trip_id number
162 ) return varchar2 IS
163 cursor delivery_leg_count is
164 select count(1)
165 from mst_delivery_legs
166 where plan_id = arg_plan_id
167 and trip_id = arg_trip_id;
168 l_del_leg_count number;
169 l_meaning varchar2(100);
170
171 cursor loading_status(p_del_leg_count NUMBER) is
172 select meaning
173 from mfg_lookups
174 where lookup_type = 'MST_YES_NO'
175 and lookup_code = 1;
176
177 BEGIN
178 OPEN delivery_leg_count;
179 FETCH delivery_leg_count into l_del_leg_count;
180 CLOSE delivery_leg_count;
181
182 if(l_del_leg_count = 0) then
183 OPEN loading_status(l_del_leg_count);
184 FETCH loading_status into l_meaning;
185 CLOSE loading_status;
186 else
187 l_meaning := null;
188 end if;
189 return l_meaning;
190 end get_trip_loading_status;
191 END mst_cm_details;
192
193
[Home] [Help]