[Home] [Help]
PACKAGE BODY: APPS.MSC_BUDGET_DETAILS
Source
1 PACKAGE BODY msc_budget_details AS
2 /* $Header: MSCBDDTB.pls 115.7 2004/05/07 18:47:07 rvrao noship $ */
3 PROCEDURE populate_budget_details(
4 p_plan_id IN number,
5 p_date IN date,
6 p_item in number,
7 p_query_id in number,
8 p_organization_id in number,
9 p_sr_instance_id in number,
10 p_budget_value in number,
11 p_tot_inv in number,
12 p_violation_level in number, -- 1 plan, 2 org, 3 cat
13 p_category_name in varchar2 default null) is
14
15
16 cursor get_mbid_date_c(p_bucket_type number)is -- get the date to use in msc_bis_inv_details
17 select max(detail_date)
18 from msc_bis_inv_detail
19 where detail_date <= p_date
20 and plan_id = p_plan_id
21 and nvl(period_type, -1) = 1
22 and organization_id = p_organization_id
23 and sr_instance_id = p_sr_instance_id
24 and nvl(detail_level,3) = nvl(p_bucket_type, 3);
25
26 cursor get_bucket_dates is
27 select bkt_start_date, bkt_end_date, decode(bucket_type,2,1,3,3)
28 from msc_plan_buckets
29 where plan_id = p_plan_id
30 and p_date between bkt_start_date and bkt_end_date;
31
32 cursor bis_inv_details_plan_c(l_tot_inv number, -- get the values from mbid
33 l_date date,
34 l_bucket_type number) is
35 select bid.inventory_item_id,
36 bid.inventory_value,
37 bid.inventory_value/l_tot_inv
38 from msc_bis_inv_detail bid ,
39 msc_system_items item
40 where nvl(bid.period_type,-1)=1
41 and bid.detail_date = l_date
42 and bid.plan_id = p_plan_id
43 and nvl(bid.detail_level,3) = nvl(l_bucket_type,3)
44 and bid.plan_id = item.plan_id
45 and bid.sr_instance_id = item.sr_instance_id
46 and bid.organization_id = item.organization_id
47 and bid.inventory_item_id = item.inventory_item_id
48 and item.budget_constrained =1;
49
50 cursor bis_inv_details_org_c(l_tot_inv number,
51 l_date date,
52 l_bucket_type number,
53 l_organization_id number,
54 l_sr_instance_id number) is
55 select bid.inventory_item_id,
56 bid.inventory_value,
57 bid.inventory_value/l_tot_inv
58 from msc_bis_inv_detail bid,
59 msc_system_items item
60 where nvl(bid.period_type,-1)=1
61 and bid.detail_date = l_date
62 and bid.plan_id = p_plan_id
63 and bid.organization_id=l_organization_id
64 and bid.sr_instance_id =l_sr_instance_id
65 and nvl(bid.detail_level,3) = nvl(l_bucket_type,3)
66 and bid.plan_id = item.plan_id
67 and bid.sr_instance_id = item.sr_instance_id
68 and bid.organization_id = item.organization_id
69 and bid.inventory_item_id = item.inventory_item_id
70 and item.budget_constrained =1;
71
72
73 cursor bis_inv_details_cat_c(l_tot_inv number,
74 l_date date,
75 l_bucket_type number,
76 l_cat_name varchar2) is
77 select bid.inventory_item_id,
78 bid.inventory_value,
79 bid.inventory_value/l_tot_inv
80 from msc_bis_inv_detail bid,
81 msc_item_categories mic,
82 msc_system_items item
83 where nvl(bid.period_type,-1)=1
84 and bid.detail_date = l_date
85 and bid.plan_id = p_plan_id
86 and nvl(bid.detail_level,3) = nvl(l_bucket_type,3)
87 and mic.inventory_item_id= bid.inventory_item_id
88 and mic.sr_instance_id = bid.sr_instance_id
89 and mic.organization_id= bid.organization_id
90 and mic.category_name = l_cat_name
91 and bid.plan_id = item.plan_id
92 and bid.sr_instance_id = item.sr_instance_id
93 and bid.organization_id = item.organization_id
94 and bid.inventory_item_id = item.inventory_item_id
95 and item.budget_constrained =1;
96
97 cursor ss_date_c(l_inventory_item_id1 number, -- get the date to use in msc_safety_stocks
98 l_organization_id1 number,
99 l_sr_instance_id1 number,
100 l_plan_id1 number,
101 l_detail_date1 date) is
102 select max(period_start_date)
103 from msc_safety_stocks
104 where period_start_date <= l_detail_date1
105 and inventory_item_id = l_inventory_item_id1
106 and organization_id = l_organization_id1
107 and sr_instance_id = l_sr_instance_id1
108 and plan_id = l_plan_id1;
109
110
111 cursor safety_stocks_c (l_inventory_item_id number, -- get the values from msc_safety_stocks
112 l_organization_id number,
113 l_sr_instance_id number,
114 l_plan_id number,
115 l_period_start_date date) is
116 select target_safety_stock, safety_stock_quantity
117 from msc_safety_stocks
118 where inventory_item_id = l_inventory_item_id
119 and organization_id= l_organization_id
120 and sr_instance_id = l_sr_instance_id
121 and plan_id = l_plan_id
122 and period_start_date = l_period_start_date;
123
124
125 l_inv_item number;
126 l_achieved_sl number;
127 l_target_sl number;
128 l_target_ss number;
129 l_achieved_ss number;
130 l_ss_date date;
131 L_PERCENT_INV_VALUE number;
132 l_inv_value number;
133
134 l_detail_date date;
135 l_start_date date;
136 l_end_date date;
137 l_item_name varchar2(10);
138
139 l_bucket_type number; -- bucket type plan_buckets bis_inv_detail
140 -- week 2 1
141 -- period 3 null
142
143 plan_level number := 1;
144 org_level number := 2;
145
146 begin
147
148 delete msc_form_query;
149
150 open get_bucket_dates;
151 fetch get_bucket_dates into l_start_date , l_end_date, l_bucket_type;
152 close get_bucket_dates;
153
154 open get_mbid_date_c(l_bucket_type);
155 fetch get_mbid_date_c into l_detail_date;
156 close get_mbid_date_c;
157
158
159 dbms_output.put_line('date is ' || l_detail_date);
160 if p_violation_level = plan_level then
161 dbms_output.put_line('level is ' || p_violation_level || ' plan_level ');
162 open bis_inv_details_plan_c(p_tot_inv,
163 l_detail_date,
164 l_bucket_type);
165 elsif p_violation_level = org_level then
166 open bis_inv_details_org_c(p_tot_inv,
167 l_detail_date,
168 l_bucket_type,
169 p_organization_id,
170 p_sr_instance_id );
171
172 else
173 open bis_inv_details_cat_c(p_tot_inv,
174 l_detail_date,
175 l_bucket_type,
176 p_category_name);
177
178 end if;
179
180 loop
181 if p_violation_level = plan_level then
182 fetch bis_inv_details_plan_c into
183 l_inv_item,
184 l_inv_value,
185 l_percent_inv_value;
186 exit when bis_inv_details_plan_c%notfound;
187 elsif p_violation_level = org_level then
188 fetch bis_inv_details_org_c into
189 l_inv_item,
190 l_inv_value,
191 l_percent_inv_value;
192 exit when bis_inv_details_org_c%notfound;
193 else -- cat_level
194 fetch bis_inv_details_cat_c into
195 l_inv_item,
196 l_inv_value,
197 l_percent_inv_value;
198 exit when bis_inv_details_cat_c%notfound;
199 end if;
200 dbms_output.put_line('item is ' || l_inv_item ||
201 ' inv val ' || l_inv_value ||
202 ' percetn inv valu ' || l_percent_inv_value);
203 -- get item name
204 l_item_name := substr(msc_get_name.item_name(l_inv_item, null, null, null),1,10);
205 -- get achieved service level
206 l_achieved_sl := Msc_Get_Bis_Values.get_service_level(p_plan_id,
207 p_sr_instance_id,
208 p_organization_id,
209 l_inv_item,
210 l_start_date,
211 l_end_date);
212
213 -- get target service level
214 l_target_sl := Msc_Get_Bis_Values.service_target(p_plan_id ,
215 p_sr_instance_id ,
216 p_organization_id ,
217 l_inv_item);
218
219 open ss_date_c(l_inv_item,
220 p_organization_id,
221 p_sr_instance_id,
222 p_plan_id,
223 p_date);
224 fetch ss_date_c into l_ss_date;
225 close ss_date_c;
226 -- get achieved and target safety stocks
227 open safety_stocks_c(l_inv_item,
228 p_organization_id,
229 p_sr_instance_id,
230 p_plan_id,
231 l_ss_date);
232 fetch safety_stocks_c into l_target_ss, l_achieved_ss;
233 close safety_stocks_c;
234
235
236 insert into msc_form_query
237 (query_id,
238 number1, --item
239 number2, --budget value
240 number3, --inv value
241 number4, --%total value
242 number5, --target sl
243 number6, --achieved sl
244 number7, --target ss
245 number8, --achieved ss
246 number9, -- org
247 number10, -- inst
248 date1 , --detail_date
249 char1, --item_name
250 char2, -- bucket type
251 last_update_date,
252 last_updated_by,
253 creation_date,
254 created_by)
255 values
256 (p_query_id,
257 l_inv_item,
258 p_budget_value,
259 round(l_inv_value,0),
260 round(l_percent_inv_value * 100,2),
261 l_target_sl,
262 l_achieved_sl,
263 l_target_ss,
264 l_achieved_ss,
265 p_organization_id,
266 p_sr_instance_id,
267 l_detail_date,
268 l_item_name,
269 decode(l_bucket_type,1,'Week','Period'),
270 sysdate,
271 -1,
272 sysdate,
273 -1);
274
275 end loop;
276 if p_violation_level = plan_level then
277 close bis_inv_details_plan_c;
278 elsif p_violation_level = org_level then
279 close bis_inv_details_org_c;
280 else
281 close bis_inv_details_cat_c;
282 end if;
283 exception
284 when no_data_found
285 then null;
286 when others then
287 raise_application_error(-20000,sqlerrm);
288 END populate_budget_details;
289 end msc_budget_details;