[Home] [Help]
PACKAGE BODY: APPS.MSC_RESOURCE_PKG
Source
1 PACKAGE BODY MSC_RESOURCE_PKG AS
2 /* $Header: MSCHBRSB.pls 120.14.12010000.4 2008/09/03 15:48:27 wexia ship $ */
3 l_constrained_plan NUMBER ;
4
5 PROCEDURE populate_details(errbuf out nocopy varchar2, retcode out nocopy varchar2, p_plan_id in number, p_plan_run_id in number) IS
6
7 BEGIN
8
9 l_constrained_plan := msc_phub_util.is_plan_constrained(p_plan_id);
10 retcode := 0;
11 errbuf := NULL;
12
13 insert into msc_resources_f(
14 plan_id,
15 plan_run_id,
16 sr_instance_id,
17 organization_id,
18 department_id,
19 owning_department_id,
20 resource_id,
21 inventory_item_id,
22 analysis_date,
23 aggr_type, category_set_id, sr_category_id, resource_group,
24 created_by,
25 creation_date,
26 last_update_date,
27 last_updated_by,
28 last_update_login,
29 program_id,
30 program_login_id,
31 program_application_id,
32 request_id,
33 required_hours,
34 available_hours,
35 setup_time_hrs,
36 order_quantity,
37 resource_hours,
38 no_of_orders,
39 resource_cost,
40 resource_cost2
41 )
42 --values
43 select
44 plan_id ,
45 plan_run_id ,
46 sr_instance_id ,
47 organization_id ,
48 department_id ,
49 nvl(owning_department_id,-23453),
50 resource_id ,
51 inventory_item_id ,
52 analysis_date ,
53 to_number(0) aggr_type,
54 to_number(-23453) category_set_id,
55 to_number(-23453) sr_category_id,
56 '-23453' resource_group,
57 fnd_global.user_id created_by,
58 sysdate creation_date,
59 sysdate last_update_date,
60 fnd_global.user_id last_updated_by,
61 fnd_global.login_id last_update_login,
62 fnd_global.conc_program_id program_id,
63 fnd_global.conc_login_id program_login_id,
64 fnd_global.prog_appl_id program_application_id,
65 fnd_global.conc_request_id request_id,
66 sum(required_hours),
67 sum(available_hours) ,
68 sum(setup_time_hrs) ,
69 sum(order_quantity) ,
70 sum(resource_hours),
71 sum(no_of_orders),
72 sum(resource_cost) resource_cost,
73 sum(resource_cost2) resource_cost2
74 from
75 (
76 select
77 mrr.plan_id plan_id,
78 p_plan_run_id plan_run_id,
79 mrr.sr_instance_id sr_instance_id,
80 mrr.organization_id organization_id,
81 mrr.department_id department_id,
82 nvl(mdr.owning_department_id,mrr.department_id) owning_department_id,
83 mrr.resource_id resource_id,
84 -23453 inventory_item_id,
85 null supply_id,
86 trunc(decode(l_constrained_plan,1,mrr.start_date,nvl(mrr.end_date,mrr.start_date))) analysis_date,
87 sum(decode(nvl(mrr.schedule_flag,2),1,(mrr.resource_hours),0) ) required_hours,
88 to_number(null) available_hours,
89 sum(decode(nvl(mrr.schedule_flag,2),1,0,(mrr.resource_hours))) setup_time_hrs,
90 to_number(null) order_quantity,
91 to_number(null) resource_hours,
92 to_number(null) no_of_orders,
93 to_number(null) resource_cost,
94 to_number(null) resource_cost2
95 from msc_resource_requirements mrr,
96 msc_department_resources mdr,
97 msc_plans mp
98 where mp.plan_id = p_plan_id
99 and mrr.plan_id = mp.plan_id
100 and mdr.plan_id = mrr.plan_id
101 and mdr.sr_instance_id = mrr.sr_instance_id
102 and mdr.organization_id = mrr.organization_id
103 and mdr.department_id = mrr.department_id
104 and mdr.resource_id = mrr.resource_id
105 and mrr.resource_id > 0
106 and ((l_constrained_plan=2 and mrr.parent_id = 2) or (l_constrained_plan=1 and mrr.parent_id = 1))
107 and trunc(decode(l_constrained_plan,1,mrr.start_date,nvl(mrr.end_date,mrr.start_date))) between mp.curr_start_date and mp.curr_cutoff_date
108 group by
109 mrr.plan_id,
110 p_plan_run_id,
111 mrr.sr_instance_id,
112 mrr.organization_id,
113 mrr.department_id,
114 nvl(mdr.owning_department_id,mrr.department_id),
115 mrr.resource_id,
116 -23453,
117 null,
118 trunc(decode(l_constrained_plan,1,mrr.start_date,nvl(mrr.end_date,mrr.start_date)))
119
120
121 union all
122
123 select
124 mra.plan_id plan_id,
125 p_plan_run_id plan_run_id,
126 mra.sr_instance_id sr_instance_id,
127 mra.organization_id organization_id,
128 mra.department_id department_id,
129 nvl(mdr.owning_department_id,mra.department_id) owning_department_id,
130 mra.resource_id resource_id,
131 -23453 inventory_item_id,
132 null supply_id,
133 trunc(mra.shift_date) analysis_date,
134 to_number(null) required_hours,
135 sum((mra.capacity_units * decode(mra.from_time,null,1,(( decode(sign(mra.to_time-mra.from_time),
136 -1, mra.to_time+86400, mra.to_time) - mra.from_time)/3600))) ) available_hours,
137 to_number(null) setup_time_hrs,
138 to_number(null) order_quantity,
139 to_number(null) resource_hours,
140 to_number(null) no_of_orders,
141 to_number(null) resource_cost,
142 to_number(null) resource_cost2
143 from msc_net_resource_avail mra,
144 msc_department_resources mdr,
145 msc_plans mp
146 where mp.plan_id = p_plan_id
147 and mra.plan_id = mp.plan_id
148 and mdr.plan_id = mra.plan_id
149 and mdr.sr_instance_id = mra.sr_instance_id
150 and mdr.organization_id = mra.organization_id
151 and mdr.department_id = mra.department_id
152 and mdr.resource_id = mra.resource_id
153 and mra.parent_id <> -1
154 and mra.resource_id > 0
155 and trunc(mra.shift_date) between mp.curr_start_date and mp.curr_cutoff_date
156 group by
157 mra.plan_id,
158 p_plan_run_id,
159 mra.sr_instance_id,
160 mra.organization_id,
161 mra.department_id,
162 nvl(mdr.owning_department_id,mra.department_id),
163 mra.resource_id,
164 -23453,
165 null,
166 trunc(mra.shift_date)
167
168 union all
169
170
171
172 select
173 mrr.plan_id plan_id,
174 p_plan_run_id plan_run_id,
175 mrr.sr_instance_id sr_instance_id,
176 mrr.organization_id organization_id,
177 mrr.department_id department_id,
178 nvl(mdr.owning_department_id,mrr.department_id) owning_department_id,
179 mrr.resource_id resource_id,
180 ms.inventory_item_id inventory_item_id,
181 mrr.supply_id supply_id,
182 trunc(max(nvl(mrr.end_date,mrr.start_date))) analysis_date,
183 to_number(null) required_hours,
184 to_number(null) available_hours,
185 to_number(null) setup_time_hrs,
186 nvl(mrr.cummulative_quantity, ms.NEW_ORDER_QUANTITY) order_quantity,
187 sum(mrr.resource_hours) resource_hours,
188 1 no_of_orders,
189 to_number(null) resource_cost,
190 to_number(null) resource_cost2
191 from
192 msc_resource_requirements mrr,
193 msc_supplies ms ,
194 msc_department_resources mdr,
195 msc_plans mp
196 where mp.plan_id = p_plan_id
197 and mrr.plan_id = mp.plan_id
198 and mrr.parent_id = 2
199 and nvl(mrr.schedule_flag,2) = 1
200 and mdr.plan_id = mrr.plan_id
201 and mdr.sr_instance_id = mrr.sr_instance_id
202 and mdr.organization_id = mrr.organization_id
203 and mdr.department_id = mrr.department_id
204 and mdr.resource_id = mrr.resource_id
205 and mrr.plan_id = ms.plan_id
206 and mrr.sr_instance_id = ms.sr_instance_id
207 and mrr.organization_id = ms.organization_id
208 and mrr.supply_id = ms.transaction_id
209 and mrr.resource_id > 0
210 and trunc(nvl(mrr.end_date,mrr.start_date)) between mp.curr_start_date and mp.curr_cutoff_date
211 group by
212 mrr.plan_id ,
213 p_plan_run_id,
214 mrr.sr_instance_id ,
215 mrr.organization_id ,
216 mrr.department_id ,
217 nvl(mdr.owning_department_id,mrr.department_id),
218 mrr.resource_id ,
219 ms.inventory_item_id,
220 mrr.supply_id,
221 nvl(mrr.cummulative_quantity, ms.NEW_ORDER_QUANTITY),
222 1
223
224 union all
225 select
226 t1.plan_id,
227 p_plan_run_id plan_run_id,
228 t1.sr_instance_id,
229 t1.organization_id,
230 t1.department_id,
231 t1.owning_department_id,
232 t1.resource_id,
233 -23453 inventory_item_id,
234 null supply_id,
235 t1.resource_date analysis_date,
236 t1.required_hours,
237 t1.available_hours,
238 t1.setup_hours setup_time_hrs,
239 to_number(null) order_quantity,
240 to_number(null) resource_hours,
241 to_number(null) no_of_orders,
242 t1.resource_cost,
243 t1.resource_cost * decode(t1.currency_code, fnd_profile.value('MSC_HUB_CUR_CODE_RPT'), 1, nvl(mcc.conv_rate, 0)) resource_cost2
244 from
245 /* For SNO plan don't filter detail_level.
246 */
247 (select
248 mdrs.plan_id,
249 mdrs.sr_instance_id,
250 mdrs.organization_id,
251 mtp.currency_code,
252 mdrs.department_id,
253 mdr.owning_department_id,
254 mdrs.resource_id,
255 trunc(mdrs.resource_date) resource_date,
256 mdrs.required_hours,
257 mdrs.available_hours,
258 mdrs.setup_hours,
259 mdrs.resource_cost
260 from
261 msc_bis_res_summary mdrs,
262 msc_department_resources mdr,
263 msc_trading_partners mtp,
264 msc_plans mp
265 where mdrs.plan_id = p_plan_id
266 and mp.plan_id = mdrs.plan_id
267 and mp.plan_type = 6
268 and nvl(mdrs.period_type, 0) = 1
269 and mdrs.sr_instance_id = mtp.sr_instance_id(+)
270 and mdrs.organization_id = mtp.sr_tp_id(+)
271 and mtp.partner_type(+) = 3
272 and mdr.plan_id = mdrs.plan_id
273 and mdr.sr_instance_id = mdrs.sr_instance_id
274 and mdr.organization_id = mdrs.organization_id
275 and mdr.department_id = mdrs.department_id
276 and mdr.resource_id = mdrs.resource_id) t1,
277 msc_currency_conv_mv mcc
278 where mcc.to_currency(+) = fnd_profile.value('MSC_HUB_CUR_CODE_RPT')
279 and mcc.from_currency(+) = nvl(t1.currency_code, 'XXX')
280 and mcc.calendar_date(+) = t1.resource_date
281
282 )
283 group by
284 plan_id,
285 plan_run_id,
286 sr_instance_id,
287 organization_id,
288 department_id,
289 nvl(owning_department_id,-23453),
290 resource_id,
291 inventory_item_id,
292 analysis_date
293 ;
294 commit;
295
296 populate_summary(errbuf, retcode, p_plan_id, p_plan_run_id);
297
298 EXCEPTION
299 WHEN DUP_VAL_ON_INDEX THEN
300 errbuf := msc_phub_util.get_planning_hub_message('MSC_HUB_DUPLICATE_DATA')||SQLCODE||' -ERROR- '||SQLERRM;
301 retcode := 2;
302 WHEN OTHERS THEN
303 errbuf := msc_phub_util.get_planning_hub_message('MSC_HUB_POPULATE_ERROR')||SQLCODE||' -ERROR- '||SQLERRM;
304 retcode := 2;
305 END populate_details;
306
307 procedure populate_summary(errbuf out nocopy varchar2, retcode out nocopy varchar2,
308 p_plan_id number, p_plan_run_id number)
309 is
310 begin
311 -- level 1
312 insert into msc_resources_f (
313 plan_id, plan_run_id,
314 sr_instance_id, organization_id,
315 department_id, owning_department_id, resource_id,
316 inventory_item_id, analysis_date,
317 aggr_type, category_set_id, sr_category_id, resource_group,
318 required_hours,
319 available_hours,
320 setup_time_hrs,
321 order_quantity,
322 resource_hours,
323 no_of_orders,
324 resource_cost,
325 resource_cost2,
326 created_by, creation_date,
327 last_update_date, last_updated_by, last_update_login,
328 program_id, program_login_id,
329 program_application_id, request_id)
330 -- department (81)
331 select
332 f.plan_id, f.plan_run_id,
333 f.sr_instance_id, f.organization_id,
334 f.department_id, f.owning_department_id, to_number(-23453) resource_id,
335 f.inventory_item_id, f.analysis_date,
336 to_number(81) aggr_type,
337 to_number(-23453) category_set_id,
338 to_number(-23453) sr_category_id,
339 '-23453' resource_group,
340 sum(f.required_hours),
341 sum(f.available_hours),
342 sum(f.setup_time_hrs),
343 sum(f.order_quantity),
344 sum(f.resource_hours),
345 sum(f.no_of_orders),
346 sum(f.resource_cost),
347 sum(f.resource_cost2),
348 fnd_global.user_id, sysdate,
349 sysdate, fnd_global.user_id, fnd_global.login_id,
350 fnd_global.conc_program_id, fnd_global.conc_login_id,
351 fnd_global.prog_appl_id, fnd_global.conc_request_id
352 from
353 msc_resources_f f
354 where f.plan_id = p_plan_id and f.plan_run_id = p_plan_run_id
355 and f.aggr_type=0
356 group by
357 f.plan_id, f.plan_run_id,
358 f.sr_instance_id, f.organization_id,
359 f.department_id, f.owning_department_id,
360 f.inventory_item_id, f.analysis_date
361 union all
362 -- resource_group (82)
363 select
364 f.plan_id, f.plan_run_id,
365 f.sr_instance_id, f.organization_id,
366 to_number(-23453) department_id,
367 to_number(-23453) owning_department_id,
368 to_number(-23453) resource_id,
369 f.inventory_item_id, f.analysis_date,
370 to_number(82) aggr_type,
371 to_number(-23453) category_set_id,
372 to_number(-23453) sr_category_id,
373 nvl(r.resource_group_name, '-23453') resource_group,
374 sum(f.required_hours),
375 sum(f.available_hours),
376 sum(f.setup_time_hrs),
377 sum(f.order_quantity),
378 sum(f.resource_hours),
379 sum(f.no_of_orders),
380 sum(f.resource_cost),
381 sum(f.resource_cost2),
382 fnd_global.user_id, sysdate,
383 sysdate, fnd_global.user_id, fnd_global.login_id,
384 fnd_global.conc_program_id, fnd_global.conc_login_id,
385 fnd_global.prog_appl_id, fnd_global.conc_request_id
386 from
387 msc_resources_f f,
388 msc_department_resources r
389 where f.plan_id = p_plan_id and f.plan_run_id = p_plan_run_id
390 and f.aggr_type=0
391 and r.plan_id(+) = f.plan_id
392 and r.sr_instance_id(+) = f.sr_instance_id
393 and r.organization_id(+) = f.organization_id
394 and r.department_id(+) = nvl(f.owning_department_id, f.department_id)
395 and r.resource_id(+) = f.resource_id
396 group by
397 f.plan_id, f.plan_run_id,
398 f.sr_instance_id, f.organization_id,
399 f.inventory_item_id, f.analysis_date,
400 r.resource_group_name;
401
402 commit;
403
404 -- level 2
405 insert into msc_resources_f (
406 plan_id, plan_run_id,
407 sr_instance_id, organization_id,
408 department_id, owning_department_id, resource_id,
409 inventory_item_id, analysis_date,
410 aggr_type, category_set_id, sr_category_id, resource_group,
411 required_hours,
412 available_hours,
413 setup_time_hrs,
414 order_quantity,
415 resource_hours,
416 no_of_orders,
417 resource_cost,
418 resource_cost2,
419 created_by, creation_date,
420 last_update_date, last_updated_by, last_update_login,
421 program_id, program_login_id,
422 program_application_id, request_id)
423 -- resource_group-mfg_period (1038)
424 select
425 f.plan_id, f.plan_run_id,
426 f.sr_instance_id, f.organization_id,
427 f.department_id, f.owning_department_id, f.resource_id,
428 f.inventory_item_id, mp.period_start_date,
429 to_number(1038) aggr_type,
430 f.category_set_id, f.sr_category_id, f.resource_group,
431 sum(f.required_hours),
432 sum(f.available_hours),
433 sum(f.setup_time_hrs),
434 sum(f.order_quantity),
435 sum(f.resource_hours),
436 sum(f.no_of_orders),
437 sum(f.resource_cost),
438 sum(f.resource_cost2),
439 fnd_global.user_id, sysdate,
440 sysdate, fnd_global.user_id, fnd_global.login_id,
441 fnd_global.conc_program_id, fnd_global.conc_login_id,
442 fnd_global.prog_appl_id, fnd_global.conc_request_id
443 from
444 msc_resources_f f,
445 msc_phub_mfg_cal_periods_mv mp
446 where f.plan_id = p_plan_id and f.plan_run_id = p_plan_run_id
447 and f.aggr_type = 82
448 and f.analysis_date between mp.period_start_date and mp.period_end_date
449 group by
450 f.plan_id, f.plan_run_id,
451 f.sr_instance_id, f.organization_id,
452 f.department_id, f.owning_department_id, f.resource_id,
453 f.inventory_item_id, mp.period_start_date,
454 f.category_set_id, f.sr_category_id, f.resource_group
455 union all
456 -- resource_group-fiscal_period (1039)
457 select
458 f.plan_id, f.plan_run_id,
459 f.sr_instance_id, f.organization_id,
460 f.department_id, f.owning_department_id, f.resource_id,
461 f.inventory_item_id, fp.start_date,
462 to_number(1039) aggr_type,
463 f.category_set_id, f.sr_category_id, f.resource_group,
464 sum(f.required_hours),
465 sum(f.available_hours),
466 sum(f.setup_time_hrs),
467 sum(f.order_quantity),
468 sum(f.resource_hours),
469 sum(f.no_of_orders),
470 sum(f.resource_cost),
471 sum(f.resource_cost2),
472 fnd_global.user_id, sysdate,
473 sysdate, fnd_global.user_id, fnd_global.login_id,
474 fnd_global.conc_program_id, fnd_global.conc_login_id,
475 fnd_global.prog_appl_id, fnd_global.conc_request_id
476 from
477 msc_resources_f f,
478 msc_phub_fiscal_periods_mv fp
479 where f.plan_id = p_plan_id and f.plan_run_id = p_plan_run_id
480 and f.aggr_type = 82
481 and f.analysis_date between fp.start_date and fp.end_date
482 group by
483 f.plan_id, f.plan_run_id,
484 f.sr_instance_id, f.organization_id,
485 f.department_id, f.owning_department_id, f.resource_id,
486 f.inventory_item_id, fp.start_date,
487 f.category_set_id, f.sr_category_id, f.resource_group;
488
489 commit;
490
491 exception
492 when dup_val_on_index then
493 errbuf := msc_phub_util.get_planning_hub_message('MSC_HUB_DUPLICATE_DATA')||
494 SQLCODE||' -ERROR- '||SQLERRM;
495 retcode := 2;
496 when others then
497 errbuf := msc_phub_util.get_planning_hub_message('MSC_HUB_POPULATE_ERROR')||
498 SQLCODE||' -ERROR- '||SQLERRM;
499 retcode := 2;
500
501 end populate_summary;
502
503 PROCEDURE purge_details(errbuf out nocopy varchar2, retcode out nocopy varchar2,
504 p_plan_id number, p_plan_run_id number ) IS
505 BEGIN
506 retcode := 0;
507 errbuf := NULL;
508
509 delete
510 from msc_resources_f
511 where plan_id = p_plan_id
512 and plan_run_id = nvl(p_plan_run_id,plan_run_id);
513 COMMIT;
514
515 EXCEPTION
516 WHEN OTHERS THEN
517 errbuf := msc_phub_util.get_planning_hub_message('MSC_HUB_PURGE_ERROR')||SQLCODE||' -ERROR- '||SQLERRM;
518 retcode := 2;
519
520 END purge_details;
521
522
523 END MSC_RESOURCE_PKG;