[Home] [Help]
PACKAGE BODY: APPS.MSC_RESOURCE_PKG
Source
1 package body msc_resource_pkg as
2 /* $Header: MSCHBRSB.pls 120.61.12020000.2 2012/10/11 13:59:00 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 l_refresh_mode number;
7 l_res_rn_qid number;
8 l_plan_start_date date;
9 l_plan_cutoff_date date;
10 l_plan_type number;
11 l_sr_instance_id number;
12 l_rowcount1 number := 0;
13 l_rowcount2 number := 0;
14 l_owning_currency_code varchar2(20) := msc_phub_util.get_owning_currency_code(p_plan_run_id);
15 l_transfer_id number := null;
16 l_constrained_plan number := null;
17 l_start_time timestamp := systimestamp;
18 l_enable_num number := nvl(fnd_profile.value('MSC_APCC_ENABLE_CUM'), 1);
19 begin
20 msc_phub_util.log('msc_resource_pkg.populate_details');
21 l_constrained_plan := msc_phub_util.is_plan_constrained(p_plan_id);
22 retcode := 0;
23 errbuf := null;
24
25 select plan_type, sr_instance_id, plan_start_date, plan_cutoff_date, temp_transfer_id
26 into l_plan_type, l_sr_instance_id, l_plan_start_date, l_plan_cutoff_date, l_transfer_id
27 from msc_plan_runs
28 where plan_id=p_plan_id
29 and plan_run_id=p_plan_run_id;
30
31 -- ODS plan
32 if p_plan_id = -1
33 then
34 -- get refresh_mode
35 select refresh_mode into l_refresh_mode
36 from msc_plan_runs
37 where plan_run_id = p_plan_run_id;
38
39 if l_refresh_mode = 2 -- targeted refesh
40 then
41 l_res_rn_qid := msc_phub_util.get_resource_rn_qid(p_plan_id, p_plan_run_id);
42
43 delete from msc_resources_f
44 where plan_id = p_plan_id
45 and plan_run_id = p_plan_run_id
46 and (p_plan_id, sr_instance_id, organization_id, department_id, resource_id) in
47 (select number1, number2, number3, number4, number5 from msc_hub_query q where q.query_id = l_res_rn_qid);
48
49 l_rowcount1 := l_rowcount1 + sql%rowcount;
50 msc_phub_util.log('msc_resources_f, delete='||sql%rowcount||', l_rowcount1='||l_rowcount1);
51
52 if (l_enable_num not in (2)) then
53 delete from msc_resources_cum_f
54 where plan_id = p_plan_id
55 and plan_run_id = p_plan_run_id
56 and (p_plan_id, sr_instance_id, organization_id, department_id, resource_id) in
57 (select number1, number2, number3, number4, number5 from msc_hub_query q where q.query_id = l_res_rn_qid);
58
59 l_rowcount2 := l_rowcount2 + sql%rowcount;
60 msc_phub_util.log('msc_resources_cum_f, delete='||sql%rowcount||', l_rowcount2='||l_rowcount2);
61 commit;
62 end if;
63 end if;
64 end if;
65
66 msc_phub_util.log('msc_resource_pkg.populate_details: '||
67 p_plan_id||','||p_plan_run_id||','||l_plan_type||','||
68 l_sr_instance_id||','||l_plan_start_date||','||l_plan_cutoff_date||','||
69 l_transfer_id||','||l_refresh_mode||','||l_res_rn_qid);
70
71 -- msc_st_resources_f:requirements
72 insert /*+ append nologging */ into msc_st_resources_f (
73 st_transaction_id,
74 error_code,
75 sr_instance_id,
76 organization_id,
77 department_id,
78 resource_id,
79 inventory_item_id,
80 analysis_date,
81
82 required_hours,
83 setup_time_hrs,
84
85 created_by, creation_date,
86 last_update_date, last_updated_by, last_update_login,
87 program_id, program_login_id,
88 program_application_id, request_id)
89 select
90 l_transfer_id,
91 to_number(0),
92 mrr.sr_instance_id,
93 mrr.organization_id,
94 mrr.department_id,
95 mrr.resource_id,
96 to_number(-23453) inventory_item_id, -- do not use nvl(mrr.assembly_item_id, -23453), res-item granularity is too much
97 trunc(decode(l_constrained_plan,1,mrr.start_date,nvl(mrr.end_date,mrr.start_date))) analysis_date,
98 sum(decode(mdr.line_flag,
99 2, decode(mrr.end_date, null, mrr.resource_hours, mrr.daily_resource_hours),
100 mdr.max_rate * mrr.daily_resource_hours)) required_hours,
101
102 sum(decode(nvl(mrr.schedule_flag,1), 1, 0,
103 decode(mdr.line_flag,
104 2, decode(mrr.end_date, null, mrr.resource_hours, mrr.daily_resource_hours),
105 mdr.max_rate * mrr.daily_resource_hours))) setup_time_hrs,
106
107 fnd_global.user_id, sysdate,
108 sysdate, fnd_global.user_id, fnd_global.login_id,
109 fnd_global.conc_program_id, fnd_global.conc_login_id,
110 fnd_global.prog_appl_id, fnd_global.conc_request_id
111 from msc_resource_requirements mrr,
112 msc_department_resources mdr
113 where mrr.plan_id = p_plan_id
114 and mdr.plan_id = mrr.plan_id
115 and mdr.sr_instance_id = mrr.sr_instance_id
116 and mdr.organization_id = mrr.organization_id
117 and mdr.department_id = mrr.department_id
118 and mdr.resource_id = mrr.resource_id
119 and mrr.resource_id > 0
120 --and nvl(mdr.batchable_flag,2) =2
121 and trunc(decode(l_constrained_plan,1,mrr.start_date,nvl(mrr.end_date,mrr.start_date)))
122 between l_plan_start_date and l_plan_cutoff_date
123 and nvl(mrr.parent_id, l_constrained_plan) = l_constrained_plan
124 and (p_plan_id <> -1
125 or (p_plan_id = -1
126 and mdr.sr_instance_id = l_sr_instance_id
127 and (l_refresh_mode = 1
128 or (l_refresh_mode = 2 and (p_plan_id, mdr.sr_instance_id, mdr.organization_id, mdr.department_id, mdr.resource_id) in
129 (select number1, number2, number3, number4, number5 from msc_hub_query q where q.query_id = l_res_rn_qid)))
130 )
131 )
132 group by
133 mrr.sr_instance_id,
134 mrr.organization_id,
135 mrr.department_id,
136 mrr.resource_id,
137 trunc(decode(l_constrained_plan,1,mrr.start_date,nvl(mrr.end_date,mrr.start_date)));
138
139 msc_phub_util.log('insert into msc_st_resources_f:requirements: '||sql%rowcount);
140 commit;
141
142 -- msc_st_resources_f:availability
143 insert /*+ append nologging */ into msc_st_resources_f (
144 st_transaction_id,
145 error_code,
146 sr_instance_id,
147 organization_id,
148 department_id,
149 resource_id,
150 inventory_item_id,
151 analysis_date,
152 available_hours,
153
154 created_by, creation_date,
155 last_update_date, last_updated_by, last_update_login,
156 program_id, program_login_id,
157 program_application_id, request_id)
158 select
159 l_transfer_id,
160 to_number(0),
161 mra.sr_instance_id,
162 mra.organization_id,
163 mra.department_id,
164 mra.resource_id,
165 to_number(-23453) inventory_item_id,
166 trunc(mra.shift_date) analysis_date,
167
168 sum(mra.capacity_units * decode(mra.from_time,null,1,((decode(sign(mra.to_time-mra.from_time),
169 -1, mra.to_time+86400, mra.to_time) - mra.from_time)/3600))) available_hours,
170
171 fnd_global.user_id, sysdate,
172 sysdate, fnd_global.user_id, fnd_global.login_id,
173 fnd_global.conc_program_id, fnd_global.conc_login_id,
174 fnd_global.prog_appl_id, fnd_global.conc_request_id
175 from msc_net_resource_avail mra
176 where mra.plan_id = p_plan_id
177 and mra.resource_id > 0
178 and mra.capacity_units >= 0 -- bug 10010498
179 and mra.sr_instance_id=decode(p_plan_id, -1, l_sr_instance_id, mra.sr_instance_id) -- bug 9599539
180 and trunc(mra.shift_date) between l_plan_start_date and l_plan_cutoff_date
181 and ((p_plan_id <> -1
182 and nvl(mra.parent_id,0) <> -1)
183 or (p_plan_id = -1
184 and mra.simulation_set is null
185 and (l_refresh_mode = 1
186 or (l_refresh_mode = 2 and (p_plan_id, mra.sr_instance_id, mra.organization_id, mra.department_id, mra.resource_id) in
187 (select number1, number2, number3, number4, number5 from msc_hub_query q where q.query_id = l_res_rn_qid) ) )
188 )
189 )
190 group by
191 mra.sr_instance_id,
192 mra.organization_id,
193 mra.department_id,
194 mra.resource_id,
195 trunc(mra.shift_date);
196
197 msc_phub_util.log('insert into msc_st_resources_f:availability: '||sql%rowcount);
198 commit;
199
200 -- msc_st_resources_f:orders
201 insert /*+ append nologging */ into msc_st_resources_f (
202 st_transaction_id,
203 error_code,
204 sr_instance_id,
205 organization_id,
206 department_id,
207 resource_id,
208 inventory_item_id,
209 analysis_date,
210 order_quantity,
211 resource_hours,
212 no_of_orders,
213 created_by, creation_date,
214 last_update_date, last_updated_by, last_update_login,
215 program_id, program_login_id,
216 program_application_id, request_id)
217 select
218 l_transfer_id,
219 to_number(0),
220 mrr.sr_instance_id,
221 mrr.organization_id,
222 mrr.department_id,
223 mrr.resource_id,
224 ms.inventory_item_id,
225 trunc(nvl(mrr.end_date,mrr.start_date)) analysis_date,
226
227 sum(nvl(mrr.cummulative_quantity, ms.new_order_quantity)) order_quantity,
228 sum(mrr.resource_hours) resource_hours,
229 1 no_of_orders,
230
231 fnd_global.user_id, sysdate,
232 sysdate, fnd_global.user_id, fnd_global.login_id,
233 fnd_global.conc_program_id, fnd_global.conc_login_id,
234 fnd_global.prog_appl_id, fnd_global.conc_request_id
235 from
236 msc_resource_requirements mrr,
237 msc_supplies ms,
238 msc_department_resources mdr
239 where mrr.plan_id = p_plan_id
240 and nvl(mrr.parent_id, l_constrained_plan) = 2
241 and nvl(mrr.schedule_flag,2) = 1
242 and mdr.plan_id = mrr.plan_id
243 and mdr.sr_instance_id = mrr.sr_instance_id
244 and mdr.organization_id = mrr.organization_id
245 and mdr.department_id = mrr.department_id
246 and mdr.resource_id = mrr.resource_id
247 and mrr.plan_id = ms.plan_id
248 and mrr.sr_instance_id = ms.sr_instance_id
249 and mrr.organization_id = ms.organization_id
250 and mrr.supply_id = ms.transaction_id
251 and mrr.resource_id > 0
252 and trunc(nvl(mrr.end_date,mrr.start_date)) between l_plan_start_date and l_plan_cutoff_date
253 and p_plan_id <> -1
254 group by
255 mrr.sr_instance_id,
256 mrr.organization_id,
257 mrr.department_id,
258 mrr.resource_id,
259 ms.inventory_item_id,
260 ms.transaction_id,
261 trunc(nvl(mrr.end_date,mrr.start_date));
262
263 msc_phub_util.log('insert into msc_st_resources_f:orders: '||sql%rowcount);
264 commit;
265
266 -- msc_st_resources_f:resource_cost
267 insert /*+ append nologging */ into msc_st_resources_f (
268 st_transaction_id,
269 error_code,
270 sr_instance_id,
271 organization_id,
272 department_id,
273 resource_id,
274 inventory_item_id,
275 analysis_date,
276 required_hours,
277 available_hours,
278 setup_time_hrs,
279 resource_cost,
280 resource_cost2,
281 created_by, creation_date,
285 select
282 last_update_date, last_updated_by, last_update_login,
283 program_id, program_login_id,
284 program_application_id, request_id)
286 l_transfer_id,
287 to_number(0),
288 t1.sr_instance_id,
289 t1.organization_id,
290 t1.department_id,
291 t1.resource_id,
292 t1.inventory_item_id,
293 t1.resource_date analysis_date,
294 t1.required_hours,
295 t1.available_hours,
296 t1.setup_hours setup_time_hrs,
297 t1.resource_cost,
298 t1.resource_cost * decode(decode(l_plan_type, 6, l_owning_currency_code, t1.currency_code),
299 fnd_profile.value('MSC_HUB_CUR_CODE_RPT'), 1, nvl(mcc.conv_rate, 0)) resource_cost2,
300 fnd_global.user_id, sysdate,
301 sysdate, fnd_global.user_id, fnd_global.login_id,
302 fnd_global.conc_program_id, fnd_global.conc_login_id,
303 fnd_global.prog_appl_id, fnd_global.conc_request_id
304 from
305 /* For SNO plan don't filter detail_level.
306 */
307 (select /*+ ordered */
308 mdrs.sr_instance_id,
309 mdrs.organization_id,
310 mtp.currency_code,
311 to_number(-23453) inventory_item_id,
312 mdrs.department_id,
313 mdrs.resource_id,
314 trunc(mdrs.resource_date) resource_date,
315 mdrs.required_hours,
316 mdrs.available_hours,
317 mdrs.setup_hours,
318 mdrs.resource_cost
319 from
320 msc_bis_res_summary mdrs,
321 msc_trading_partners mtp
322 where mdrs.plan_id = p_plan_id
323 and l_plan_type = 6
324 and nvl(mdrs.period_type, 0) = 1
325 and mdrs.sr_instance_id = mtp.sr_instance_id(+)
326 and mdrs.organization_id = mtp.sr_tp_id(+)
327 and mtp.partner_type(+) = 3
328 union all
329 select /*+ ordered */
330 mbid.sr_instance_id,
331 mbid.organization_id,
332 mtp.currency_code,
333 mbid.inventory_item_id,
334 to_number(-23453) department_id,
335 to_number(-23453) resource_id,
336 trunc(mbid.detail_date) resource_date,
337 to_number(null) required_hours,
338 to_number(null) available_hours,
339 to_number(null) setup_hours,
340 mbid.production_cost
341 from
342 msc_bis_inv_detail mbid,
343 msc_trading_partners mtp
344 where mbid.plan_id = p_plan_id
345 and nvl(mbid.detail_level, 0) = 1
346 and nvl(mbid.period_type, 0) = 1
347 and l_plan_type in (1,101,102,103,105)
348 and mbid.sr_instance_id = mtp.sr_instance_id(+)
349 and mbid.organization_id = mtp.sr_tp_id(+)
350 and mtp.partner_type(+) = 3
351 and mbid.production_cost>0
352 ) t1,
353 msc_currency_conv_mv mcc
354 where mcc.to_currency(+) = fnd_profile.value('MSC_HUB_CUR_CODE_RPT')
355 and mcc.from_currency(+) = decode(l_plan_type, 6, l_owning_currency_code, t1.currency_code)
356 and mcc.calendar_date(+) = t1.resource_date
357 and p_plan_id <> -1;
358
359 msc_phub_util.log('insert into msc_st_resources_f:resource_cost: '||sql%rowcount);
360 commit;
361
362 -- msc_resources_f:final
363 msc_phub_util.gather_table_stats(msc_phub_util.stats_st, 'MSC_ST_RESOURCES_F', l_transfer_id);
364 insert into msc_resources_f (
365 plan_id,
366 plan_run_id,
367 sr_instance_id,
368 organization_id,
369 department_id,
370 resource_id,
371 inventory_item_id,
372 analysis_date,
373 aggr_type,
374 resource_group,
375 available_hours,
376 required_hours,
377 setup_time_hrs,
378 order_quantity,
379 resource_hours,
380 no_of_orders,
381 resource_cost,
382 resource_cost2,
383 created_by, creation_date,
384 last_update_date, last_updated_by, last_update_login,
385 program_id, program_login_id,
386 program_application_id, request_id)
387 select
388 p_plan_id,
389 p_plan_run_id,
390 f.sr_instance_id,
391 f.organization_id,
392 f.department_id,
393 f.resource_id,
394 f.inventory_item_id,
395 f.analysis_date,
396 to_number(0) aggr_type,
397 '-23453' resource_group,
398 sum(f.available_hours),
399 sum(f.required_hours),
400 sum(f.setup_time_hrs),
401 sum(f.order_quantity),
402 sum(f.resource_hours),
403 sum(f.no_of_orders),
404 sum(f.resource_cost),
405 sum(f.resource_cost2),
406 fnd_global.user_id, sysdate,
407 sysdate, fnd_global.user_id, fnd_global.login_id,
408 fnd_global.conc_program_id, fnd_global.conc_login_id,
409 fnd_global.prog_appl_id, fnd_global.conc_request_id
410 from msc_st_resources_f f
411 where f.st_transaction_id=l_transfer_id
412 group by
413 sr_instance_id,
414 organization_id,
418 analysis_date;
415 department_id,
416 resource_id,
417 inventory_item_id,
419
420 l_rowcount1 := l_rowcount1 + sql%rowcount;
421 msc_phub_util.log('insert into msc_st_resources_f:final: '||sql%rowcount);
422 commit;
423 msc_phub_util.gather_table_stats(msc_phub_util.stats_fact, 'MSC_RESOURCES_F', p_plan_run_id);
424
425 if (l_enable_num not in (2)) then
426 -- msc_resources_cum_f
427 insert into msc_resources_cum_f (
428 plan_id,
429 plan_run_id,
430 sr_instance_id,
431 organization_id,
432 department_id,
433 resource_id,
434 inventory_item_id,
435 analysis_date,
436 aggr_type,
437 resource_group,
438 cum_net_resource_avail,
439 created_by, creation_date,
440 last_update_date, last_updated_by, last_update_login,
441 program_id, program_login_id,
442 program_application_id, request_id)
443 select
444 f.plan_id,
445 f.plan_run_id,
446 f.sr_instance_id,
447 f.organization_id,
448 f.department_id,
449 f.resource_id,
450 f.inventory_item_id,
451 d.calendar_date analysis_date,
452 to_number(0) aggr_type,
453 '-23453' resource_group,
454 sum(nvl(f.available_hours, 0) - nvl(f.required_hours, 0)) cum_net_resource_avail,
455 fnd_global.user_id, sysdate,
456 sysdate, fnd_global.user_id, fnd_global.login_id,
457 fnd_global.conc_program_id, fnd_global.conc_login_id,
458 fnd_global.prog_appl_id, fnd_global.conc_request_id
459 from
460 msc_resources_f f,
461 msc_phub_dates_mv d
462 where f.plan_id=p_plan_id
463 and f.plan_run_id=p_plan_run_id
464 and f.aggr_type=0
465 and d.calendar_date between l_plan_start_date and l_plan_cutoff_date
466 and d.calendar_date in (d.mfg_week_end_date, d.fis_period_end_date, d.month_end_date, l_plan_cutoff_date)
467 and d.calendar_date >= f.analysis_date
468 and (f.available_hours > 0 or f.required_hours > 0)
469 and ((f.plan_id <> -1)
470 or (f.plan_id = -1
471 and f.sr_instance_id = l_sr_instance_id
472 and (l_refresh_mode = 1
473 or (l_refresh_mode = 2 and (p_plan_id, f.sr_instance_id, f.organization_id, f.department_id, f.resource_id) in
474 (select number1, number2, number3, number4, number5 from msc_hub_query q where q.query_id = l_res_rn_qid)))
475 )
476 )
477 group by
478 f.plan_id,
479 f.plan_run_id,
480 f.sr_instance_id,
481 f.organization_id,
482 f.department_id,
483 f.resource_id,
484 f.inventory_item_id,
485 d.calendar_date;
486
487 l_rowcount2 := l_rowcount2 + sql%rowcount;
488 msc_phub_util.log('msc_resources_cum_f, insert='||sql%rowcount||', l_rowcount2='||l_rowcount2);
489 commit;
490 msc_phub_util.gather_table_stats(msc_phub_util.stats_fact, 'MSC_RESOURCES_CUM_F', p_plan_run_id);
491 end if;
492
493 if (l_rowcount1 > 0) then
494 summarize_resources_f(errbuf, retcode, p_plan_id, p_plan_run_id);
495 end if;
496
497 if (l_rowcount2 > 0) then
498 summarize_resources_cum_f(errbuf, retcode, p_plan_id, p_plan_run_id);
499 end if;
500
501 msc_phub_util.log('msc_resource_pkg.populate_details.complete, duration='||(systimestamp-l_start_time));
502
503 exception
504 when others then
505 msc_phub_util.log('msc_resource_pkg.populate_details: '||sqlerrm);
506 raise;
507
508 end populate_details;
509
510
511 procedure summarize_resources_f(errbuf out nocopy varchar2, retcode out nocopy varchar2,
512 p_plan_id number, p_plan_run_id number)
513 is
514 begin
515 msc_phub_util.log('msc_resource_pkg.summarize_resources_f');
516 retcode := 0;
517 errbuf := '';
518
519 delete from msc_resources_f
520 where plan_id=p_plan_id and plan_run_id=p_plan_run_id and aggr_type>0;
521 msc_phub_util.log('msc_resource_pkg.summarize_resources_f, delete='||sql%rowcount);
522 commit;
523
524 -- level 1
525 insert into msc_resources_f (
526 plan_id, plan_run_id,
527 sr_instance_id, organization_id,
528 department_id, resource_id,
529 inventory_item_id, analysis_date,
530 aggr_type, resource_group,
531 required_hours,
532 available_hours,
533 setup_time_hrs,
534 order_quantity,
535 resource_hours,
536 no_of_orders,
537 resource_cost,
538 resource_cost2,
539 created_by, creation_date,
540 last_update_date, last_updated_by, last_update_login,
541 program_id, program_login_id,
542 program_application_id, request_id)
543 -- department (81)
544 select
545 f.plan_id, f.plan_run_id,
546 f.sr_instance_id, f.organization_id,
547 f.department_id,
551 '-23453' resource_group,
548 to_number(-23453) resource_id,
549 f.inventory_item_id, f.analysis_date,
550 to_number(81) aggr_type,
552 sum(f.required_hours),
553 sum(f.available_hours),
554 sum(f.setup_time_hrs),
555 sum(f.order_quantity),
556 sum(f.resource_hours),
557 sum(f.no_of_orders),
558 sum(f.resource_cost),
559 sum(f.resource_cost2),
560 fnd_global.user_id, sysdate,
561 sysdate, fnd_global.user_id, fnd_global.login_id,
562 fnd_global.conc_program_id, fnd_global.conc_login_id,
563 fnd_global.prog_appl_id, fnd_global.conc_request_id
564 from
565 msc_resources_f f
566 where f.plan_id = p_plan_id and f.plan_run_id = p_plan_run_id
567 and f.aggr_type=0
568 group by
569 f.plan_id, f.plan_run_id,
570 f.sr_instance_id, f.organization_id,
571 f.department_id,
572 f.inventory_item_id, f.analysis_date
573 union all
574 -- resource_group (82)
575 select
576 f.plan_id, f.plan_run_id,
577 f.sr_instance_id, f.organization_id,
578 to_number(-23453) department_id,
579 to_number(-23453) resource_id,
580 f.inventory_item_id, f.analysis_date,
581 to_number(82) aggr_type,
582 nvl(r.resource_group_id, '-23453') resource_group,
583 sum(f.required_hours),
584 sum(f.available_hours),
585 sum(f.setup_time_hrs),
586 sum(f.order_quantity),
587 sum(f.resource_hours),
588 sum(f.no_of_orders),
589 sum(f.resource_cost),
590 sum(f.resource_cost2),
591 fnd_global.user_id, sysdate,
592 sysdate, fnd_global.user_id, fnd_global.login_id,
593 fnd_global.conc_program_id, fnd_global.conc_login_id,
594 fnd_global.prog_appl_id, fnd_global.conc_request_id
595 from
596 msc_resources_f f,
597 msc_phub_resources_mv r
598 where f.plan_id = p_plan_id and f.plan_run_id = p_plan_run_id
599 and f.aggr_type=0
600 and r.sr_instance_id(+) = f.sr_instance_id
601 and r.organization_id(+) = f.organization_id
602 and r.department_id(+) = f.department_id
603 and r.resource_id(+) = f.resource_id
604 group by
605 f.plan_id, f.plan_run_id,
606 f.sr_instance_id, f.organization_id,
607 f.inventory_item_id, f.analysis_date,
608 nvl(r.resource_group_id, '-23453');
609
610 msc_phub_util.log('msc_resource_pkg.summarize_resources_f, level1='||sql%rowcount);
611 commit;
612
613 -- level 2
614 insert into msc_resources_f (
615 plan_id, plan_run_id,
616 sr_instance_id, organization_id,
617 department_id, resource_id,
618 inventory_item_id, analysis_date,
619 aggr_type, resource_group,
620 required_hours,
621 available_hours,
622 setup_time_hrs,
623 order_quantity,
624 resource_hours,
625 no_of_orders,
626 resource_cost,
627 resource_cost2,
628 created_by, creation_date,
629 last_update_date, last_updated_by, last_update_login,
630 program_id, program_login_id,
631 program_application_id, request_id)
632 -- resource_group-mfg_period (1038)
633 select
634 f.plan_id, f.plan_run_id,
635 f.sr_instance_id, f.organization_id,
636 f.department_id, f.resource_id,
637 f.inventory_item_id, d.mfg_period_start_date,
638 to_number(1038) aggr_type,
639 f.resource_group,
640 sum(f.required_hours),
641 sum(f.available_hours),
642 sum(f.setup_time_hrs),
643 sum(f.order_quantity),
644 sum(f.resource_hours),
645 sum(f.no_of_orders),
646 sum(f.resource_cost),
647 sum(f.resource_cost2),
648 fnd_global.user_id, sysdate,
649 sysdate, fnd_global.user_id, fnd_global.login_id,
650 fnd_global.conc_program_id, fnd_global.conc_login_id,
651 fnd_global.prog_appl_id, fnd_global.conc_request_id
652 from
653 msc_resources_f f,
654 msc_phub_dates_mv d
655 where f.plan_id = p_plan_id and f.plan_run_id = p_plan_run_id
656 and f.aggr_type = 82
657 and f.analysis_date = d.calendar_date
658 and d.mfg_period_start_date is not null
659 group by
660 f.plan_id, f.plan_run_id,
661 f.sr_instance_id, f.organization_id,
662 f.department_id, f.resource_id,
663 f.inventory_item_id, d.mfg_period_start_date,
664 f.resource_group
665 union all
666 -- resource_group-fiscal_period (1039)
667 select
668 f.plan_id, f.plan_run_id,
669 f.sr_instance_id, f.organization_id,
670 f.department_id, f.resource_id,
671 f.inventory_item_id, d.fis_period_start_date,
672 to_number(1039) aggr_type,
673 f.resource_group,
674 sum(f.required_hours),
675 sum(f.available_hours),
676 sum(f.setup_time_hrs),
677 sum(f.order_quantity),
678 sum(f.resource_hours),
679 sum(f.no_of_orders),
680 sum(f.resource_cost),
681 sum(f.resource_cost2),
685 fnd_global.prog_appl_id, fnd_global.conc_request_id
682 fnd_global.user_id, sysdate,
683 sysdate, fnd_global.user_id, fnd_global.login_id,
684 fnd_global.conc_program_id, fnd_global.conc_login_id,
686 from
687 msc_resources_f f,
688 msc_phub_dates_mv d
689 where f.plan_id = p_plan_id and f.plan_run_id = p_plan_run_id
690 and f.aggr_type = 82
691 and f.analysis_date = d.calendar_date
692 and d.fis_period_start_date is not null
693 group by
694 f.plan_id, f.plan_run_id,
695 f.sr_instance_id, f.organization_id,
696 f.department_id, f.resource_id,
697 f.inventory_item_id, d.fis_period_start_date,
698 f.resource_group;
699
700 msc_phub_util.log('msc_resource_pkg.summarize_resources_f, level2='||sql%rowcount);
701 commit;
702
703 exception
704 when others then
705 retcode := 2;
706 errbuf := 'msc_resource_pkg.summarize_demands_f: '||sqlerrm;
707 raise;
708
709 end summarize_resources_f;
710
711 procedure summarize_resources_cum_f(errbuf out nocopy varchar2, retcode out nocopy varchar2,
712 p_plan_id number, p_plan_run_id number)
713 is
714 begin
715 msc_phub_util.log('msc_resource_pkg.summarize_resources_cum_f');
716 retcode := 0;
717 errbuf := '';
718
719 delete from msc_resources_cum_f
720 where plan_id=p_plan_id and plan_run_id=p_plan_run_id and aggr_type>0;
721 msc_phub_util.log('msc_resource_pkg.summarize_resources_cum_f, delete='||sql%rowcount);
722 commit;
723
724 -- level 1
725 insert into msc_resources_cum_f (
726 plan_id, plan_run_id,
727 sr_instance_id, organization_id,
728 department_id, resource_id,
729 inventory_item_id, analysis_date,
730 aggr_type, resource_group,
731 cum_net_resource_avail,
732 created_by, creation_date,
733 last_update_date, last_updated_by, last_update_login,
734 program_id, program_login_id,
735 program_application_id, request_id)
736 -- department (81)
737 select
738 f.plan_id, f.plan_run_id,
739 f.sr_instance_id, f.organization_id,
740 f.department_id,
741 to_number(-23453) resource_id,
742 f.inventory_item_id, f.analysis_date,
743 to_number(81) aggr_type,
744 '-23453' resource_group,
745 sum(f.cum_net_resource_avail),
746 fnd_global.user_id, sysdate,
747 sysdate, fnd_global.user_id, fnd_global.login_id,
748 fnd_global.conc_program_id, fnd_global.conc_login_id,
749 fnd_global.prog_appl_id, fnd_global.conc_request_id
750 from
751 msc_resources_cum_f f
752 where f.plan_id = p_plan_id and f.plan_run_id = p_plan_run_id
753 and f.aggr_type=0
754 group by
755 f.plan_id, f.plan_run_id,
756 f.sr_instance_id, f.organization_id,
757 f.department_id,
758 f.inventory_item_id, f.analysis_date
759 union all
760 -- resource_group (82)
761 select
762 f.plan_id, f.plan_run_id,
763 f.sr_instance_id, f.organization_id,
764 to_number(-23453) department_id,
765 to_number(-23453) resource_id,
766 f.inventory_item_id, f.analysis_date,
767 to_number(82) aggr_type,
768 nvl(r.resource_group_id, '-23453') resource_group,
769 sum(f.cum_net_resource_avail),
770 fnd_global.user_id, sysdate,
771 sysdate, fnd_global.user_id, fnd_global.login_id,
772 fnd_global.conc_program_id, fnd_global.conc_login_id,
773 fnd_global.prog_appl_id, fnd_global.conc_request_id
774 from
775 msc_resources_cum_f f,
776 msc_phub_resources_mv r
777 where f.plan_id = p_plan_id and f.plan_run_id = p_plan_run_id
778 and f.aggr_type=0
779 and r.sr_instance_id(+) = f.sr_instance_id
780 and r.organization_id(+) = f.organization_id
781 and r.department_id(+) = f.department_id
782 and r.resource_id(+) = f.resource_id
783 group by
784 f.plan_id, f.plan_run_id,
785 f.sr_instance_id, f.organization_id,
786 f.inventory_item_id, f.analysis_date,
787 nvl(r.resource_group_id, '-23453');
788
789 msc_phub_util.log('msc_resource_pkg.summarize_resources_cum_f, level1='||sql%rowcount);
790 commit;
791
792 exception
793 when others then
794 retcode := 2;
795 errbuf := 'msc_resource_pkg.summarize_resources_cum_f: '||sqlerrm;
796 raise;
797 end summarize_resources_cum_f;
798
799 procedure export_resources_f (
800 errbuf out nocopy varchar2, retcode out nocopy varchar2,
801 p_st_transaction_id number, p_plan_run_id number,
802 p_dblink varchar2, p_source_version varchar2)
803 is
804 l_sql varchar2(5000);
805 l_suffix varchar2(32) := msc_phub_util.suffix(p_dblink);
806 l_apps_schema varchar2(30) := msc_phub_util.apps_schema;
807 begin
808 msc_phub_util.log('msc_resource_pkg.export_resources_f');
809 retcode := 0;
810 errbuf := null;
811
812 delete from msc_st_resources_f where st_transaction_id=p_st_transaction_id;
813 commit;
814
815 l_sql :=
816 ' insert into msc_st_resources_f('||
820 ' organization_id,'||
817 ' st_transaction_id,'||
818 ' error_code,'||
819 ' sr_instance_id,'||
821 ' department_id,'||
822 ' resource_id,'||
823 ' inventory_item_id,'||
824 ' organization_code,'||
825 ' department_code,'||
826 ' department_class,'||
827 ' resource_code,'||
828 ' resource_group_name,'||
829 ' item_name,'||
830 ' analysis_date,'||
831 ' available_hours,'||
832 ' required_hours,'||
833 ' setup_time_hrs,'||
834 ' order_quantity,'||
835 ' resource_hours,'||
836 ' no_of_orders,'||
837 ' resource_cost,'||
838 ' resource_cost2,'||
839 ' created_by, creation_date,'||
840 ' last_updated_by, last_update_date, last_update_login'||
841 ' )'||
842 ' select'||
843 ' :p_st_transaction_id,'||
844 ' 0,'||
845 ' f.sr_instance_id,'||
846 ' f.organization_id,'||
847 ' f.department_id,'||
848 ' f.resource_id,'||
849 ' f.inventory_item_id,'||
850 ' mtp.organization_code,'||
851 ' mdr.department_code,'||
852 ' mdr.department_class,'||
853 ' mdr.resource_code,'||
854 ' mdr.resource_group_name,'||
855 ' mi.item_name,'||
856 ' f.analysis_date,'||
857 ' f.available_hours,'||
858 ' f.required_hours,'||
859 ' f.setup_time_hrs,'||
860 ' f.order_quantity,'||
861 ' f.resource_hours,'||
862 ' f.no_of_orders,'||
863 ' f.resource_cost,'||
864 ' f.resource_cost2,'||
865 ' fnd_global.user_id, sysdate,'||
866 ' fnd_global.user_id, sysdate, fnd_global.login_id'||
867 ' from'||
868 ' '||l_apps_schema||'.msc_resources_f'||l_suffix||' f,'||
869 ' '||l_apps_schema||'.msc_trading_partners'||l_suffix||' mtp,'||
870 ' '||l_apps_schema||'.msc_items'||l_suffix||' mi,'||
871 ' '||l_apps_schema||'.msc_department_resources'||l_suffix||' mdr'||
872 ' where f.plan_run_id=:p_plan_run_id'||
873 ' and f.aggr_type=0'||
874 ' and mtp.partner_type(+)=3'||
875 ' and mtp.sr_instance_id(+)=f.sr_instance_id'||
876 ' and mtp.sr_tp_id(+)=f.organization_id'||
877 ' and mi.inventory_item_id(+)=f.inventory_item_id'||
878 ' and mdr.plan_id(+)=-1'||
879 ' and mdr.department_id(+)=f.department_id'||
880 ' and mdr.resource_id(+)=f.resource_id'||
881 ' and mdr.sr_instance_id(+)=f.sr_instance_id'||
882 ' and mdr.organization_id(+)=f.organization_id';
883
884 execute immediate l_sql using p_st_transaction_id, p_plan_run_id;
885 commit;
886 msc_phub_util.log('msc_resource_pkg.export_resources_f: complete, retcode='||retcode);
887
888 exception
889 when others then
890 retcode := 2;
891 errbuf := 'msc_resource_pkg.export_resources_f: '||sqlerrm;
892 msc_phub_util.log(errbuf);
893 end export_resources_f;
894
895 procedure export_resources_cum_f (
896 errbuf out nocopy varchar2, retcode out nocopy varchar2,
897 p_st_transaction_id number, p_plan_run_id number,
898 p_dblink varchar2, p_source_version varchar2)
899 is
900 l_sql varchar2(5000);
901 l_suffix varchar2(32) := msc_phub_util.suffix(p_dblink);
902 l_apps_schema varchar2(30) := msc_phub_util.apps_schema;
903 begin
904 msc_phub_util.log('msc_resource_pkg.export_resources_cum_f');
905 retcode := 0;
906 errbuf := null;
907
908 delete from msc_st_resources_cum_f where st_transaction_id=p_st_transaction_id;
909 commit;
910
911 l_sql :=
912 ' insert into msc_st_resources_cum_f('||
913 ' st_transaction_id,'||
914 ' error_code,'||
915 ' sr_instance_id,'||
916 ' organization_id,'||
917 ' department_id,'||
918 ' resource_id,'||
919 ' inventory_item_id,'||
920 ' organization_code,'||
921 ' department_code,'||
922 ' department_class,'||
923 ' resource_code,'||
924 ' resource_group_name,'||
925 ' item_name,'||
926 ' analysis_date,';
927 if (p_source_version >= '12.1.3') then l_sql := l_sql||
928 ' cum_net_resource_avail,';
929 end if;
930 l_sql := l_sql||
931 ' created_by, creation_date,'||
932 ' last_updated_by, last_update_date, last_update_login'||
933 ' )'||
934 ' select'||
935 ' :p_st_transaction_id,'||
936 ' 0,'||
937 ' f.sr_instance_id,'||
938 ' f.organization_id,'||
939 ' f.department_id,'||
940 ' f.resource_id,'||
941 ' f.inventory_item_id,'||
942 ' mtp.organization_code,'||
943 ' mdr.department_code,'||
944 ' mdr.department_class,'||
945 ' mdr.resource_code,'||
946 ' mdr.resource_group_name,'||
950 ' f.cum_net_resource_avail,';
947 ' mi.item_name,'||
948 ' f.analysis_date,';
949 if (p_source_version >= '12.1.3') then l_sql := l_sql||
951 end if;
952 l_sql := l_sql||
953 ' fnd_global.user_id, sysdate,'||
954 ' fnd_global.user_id, sysdate, fnd_global.login_id'||
955 ' from'||
956 ' '||l_apps_schema||'.msc_resources_cum_f'||l_suffix||' f,'||
957 ' '||l_apps_schema||'.msc_trading_partners'||l_suffix||' mtp,'||
958 ' '||l_apps_schema||'.msc_items'||l_suffix||' mi,'||
959 ' '||l_apps_schema||'.msc_department_resources'||l_suffix||' mdr'||
960 ' where f.plan_run_id=:p_plan_run_id'||
961 ' and f.aggr_type=0'||
962 ' and mtp.partner_type(+)=3'||
963 ' and mtp.sr_instance_id(+)=f.sr_instance_id'||
964 ' and mtp.sr_tp_id(+)=f.organization_id'||
965 ' and mi.inventory_item_id(+)=f.inventory_item_id'||
966 ' and mdr.plan_id(+)=-1'||
967 ' and mdr.department_id(+)=f.department_id'||
968 ' and mdr.resource_id(+)=f.resource_id'||
969 ' and mdr.sr_instance_id(+)=f.sr_instance_id'||
970 ' and mdr.organization_id(+)=f.organization_id';
971
972 execute immediate l_sql using p_st_transaction_id, p_plan_run_id;
973 commit;
974 msc_phub_util.log('msc_resource_pkg.export_resources_cum_f: complete, retcode='||retcode);
975
976 exception
977 when others then
978 retcode := 2;
979 errbuf := 'msc_resource_pkg.export_resources_cum_f: '||sqlerrm;
980 msc_phub_util.log(errbuf);
981 end export_resources_cum_f;
982
983 procedure import_resources_f (
984 errbuf out nocopy varchar2, retcode out nocopy varchar2,
985 p_st_transaction_id number, p_plan_id number, p_plan_run_id number,
986 p_plan_type number, p_plan_start_date date, p_plan_cutoff_date date,
987 p_upload_mode number, p_overwrite_after_date date, p_def_instance_code varchar2)
988 is
989 l_staging_table varchar2(30) := 'msc_st_resources_f';
990 l_fact_table varchar2(30) := 'msc_resources_f';
991 l_result number := 0;
992 begin
993 msc_phub_util.log('msc_resource_pkg.import_resources_f');
994 retcode := 0;
995 errbuf := null;
996
997 l_result := l_result + msc_phub_util.prepare_staging_dates(
998 l_staging_table, 'analysis_date', p_st_transaction_id,
999 p_upload_mode, p_overwrite_after_date,
1000 p_plan_start_date, p_plan_cutoff_date);
1001
1002 l_result := l_result + msc_phub_util.prepare_fact_dates(
1003 l_fact_table, 1, 'analysis_date', p_plan_id, p_plan_run_id,
1004 p_upload_mode, p_overwrite_after_date);
1005
1006 l_result := l_result + msc_phub_util.decode_organization_key(
1007 l_staging_table, p_st_transaction_id, p_def_instance_code,
1008 'sr_instance_id', 'organization_id', 'organization_code');
1009
1010 l_result := l_result + msc_phub_util.decode_item_key(
1011 l_staging_table, p_st_transaction_id, 'inventory_item_id', 'item_name');
1012
1013 l_result := l_result + msc_phub_util.decode_resource_key(
1014 l_staging_table, p_st_transaction_id);
1015
1016 msc_phub_util.log('msc_resource_pkg.import_resources_f: insert into msc_resources_f');
1017 insert into msc_resources_f (
1018 plan_id,
1019 plan_run_id,
1020 sr_instance_id,
1021 organization_id,
1022 department_id,
1023 resource_id,
1024 inventory_item_id,
1025 analysis_date,
1026 available_hours,
1027 required_hours,
1028 setup_time_hrs,
1029 order_quantity,
1030 resource_hours,
1031 no_of_orders,
1032 resource_cost,
1033 resource_cost2,
1034 aggr_type, resource_group,
1035 created_by, creation_date,
1036 last_updated_by, last_update_date, last_update_login
1037 )
1038 select
1039 p_plan_id,
1040 p_plan_run_id,
1041 nvl(sr_instance_id, -23453),
1042 nvl(organization_id, -23453),
1043 nvl(department_id, -23453),
1044 nvl(resource_id, -23453),
1045 nvl(inventory_item_id, -23453),
1046 analysis_date,
1047 available_hours,
1048 required_hours,
1049 setup_time_hrs,
1050 order_quantity,
1051 resource_hours,
1052 no_of_orders,
1053 resource_cost,
1054 resource_cost2,
1055 0, '-23453',
1056 fnd_global.user_id, sysdate,
1057 fnd_global.user_id, sysdate, fnd_global.login_id
1058 from msc_st_resources_f
1059 where st_transaction_id=p_st_transaction_id and error_code=0;
1060
1061 msc_phub_util.log('msc_resource_pkg.import_resources_f: inserted='||sql%rowcount);
1062 commit;
1063
1064 summarize_resources_f(errbuf, retcode, p_plan_id, p_plan_run_id);
1065
1066 if (l_result > 0) then
1067 retcode := -1;
1068 end if;
1069
1070 msc_phub_util.log('msc_resource_pkg.import_resources_f: complete, retcode='||retcode);
1071
1072 exception
1073 when others then
1074 retcode := 2;
1075 errbuf := 'msc_resource_pkg.import_resources_f: '||sqlerrm;
1076 msc_phub_util.log(errbuf);
1077 end import_resources_f;
1078
1082 p_plan_type number, p_plan_start_date date, p_plan_cutoff_date date,
1079 procedure import_resources_cum_f (
1080 errbuf out nocopy varchar2, retcode out nocopy varchar2,
1081 p_st_transaction_id number, p_plan_id number, p_plan_run_id number,
1083 p_upload_mode number, p_overwrite_after_date date, p_def_instance_code varchar2)
1084 is
1085 l_staging_table varchar2(30) := 'msc_st_resources_cum_f';
1086 l_fact_table varchar2(30) := 'msc_resources_cum_f';
1087 l_result number := 0;
1088 begin
1089 msc_phub_util.log('msc_resource_pkg.import_resources_cum_f');
1090 retcode := 0;
1091 errbuf := null;
1092
1093 l_result := l_result + msc_phub_util.prepare_staging_dates(
1094 l_staging_table, 'analysis_date', p_st_transaction_id,
1095 p_upload_mode, p_overwrite_after_date,
1096 p_plan_start_date, p_plan_cutoff_date);
1097
1098 l_result := l_result + msc_phub_util.prepare_fact_dates(
1099 l_fact_table, 1, 'analysis_date', p_plan_id, p_plan_run_id,
1100 p_upload_mode, p_overwrite_after_date);
1101
1102 l_result := l_result + msc_phub_util.decode_organization_key(
1103 l_staging_table, p_st_transaction_id, p_def_instance_code,
1104 'sr_instance_id', 'organization_id', 'organization_code');
1105
1106 l_result := l_result + msc_phub_util.decode_item_key(
1107 l_staging_table, p_st_transaction_id, 'inventory_item_id', 'item_name');
1108
1109 l_result := l_result + msc_phub_util.decode_resource_key(
1110 l_staging_table, p_st_transaction_id);
1111
1112 msc_phub_util.log('msc_resource_pkg.import_resources_cum_f: insert into msc_resources_cum_f');
1113 insert into msc_resources_cum_f (
1114 plan_id,
1115 plan_run_id,
1116 sr_instance_id,
1117 organization_id,
1118 department_id,
1119 resource_id,
1120 inventory_item_id,
1121 analysis_date,
1122 cum_net_resource_avail,
1123 aggr_type, resource_group,
1124 created_by, creation_date,
1125 last_updated_by, last_update_date, last_update_login
1126 )
1127 select
1128 p_plan_id,
1129 p_plan_run_id,
1130 nvl(sr_instance_id, -23453),
1131 nvl(organization_id, -23453),
1132 nvl(department_id, -23453),
1133 nvl(resource_id, -23453),
1134 nvl(inventory_item_id, -23453),
1135 analysis_date,
1136 cum_net_resource_avail,
1137 0, '-23453',
1138 fnd_global.user_id, sysdate,
1139 fnd_global.user_id, sysdate, fnd_global.login_id
1140 from msc_st_resources_cum_f
1141 where st_transaction_id=p_st_transaction_id and error_code=0;
1142
1143 msc_phub_util.log('msc_resource_pkg.import_resources_cum_f: inserted='||sql%rowcount);
1144 commit;
1145
1146 summarize_resources_cum_f(errbuf, retcode, p_plan_id, p_plan_run_id);
1147
1148 if (l_result > 0) then
1149 retcode := -1;
1150 end if;
1151
1152 msc_phub_util.log('msc_resource_pkg.import_resources_cum_f: complete, retcode='||retcode);
1153
1154 exception
1155 when others then
1156 retcode := 2;
1157 errbuf := 'msc_resource_pkg.import_resources_cum_f: '||sqlerrm;
1158 msc_phub_util.log(errbuf);
1159 end import_resources_cum_f;
1160
1161 end msc_resource_pkg;