[Home] [Help]
PACKAGE BODY: APPS.MSC_RES_PLAN_PKG
Source
1 package body msc_res_plan_pkg as
2 /* $Header: MSCPCRHB.pls 120.0.12020000.2 2012/10/10 15:29:55 wexia noship $ */
3 g_page_size number := 10;
4 g_max_fetch_size number := 100;
5 g_fetch_size number := g_max_fetch_size;
6
7 procedure prepare_row_dtls(
8 p_query_id number,
9 p_plan_id number,
10 p_organization_filters varchar2,
11 p_department_filters varchar2,
12 p_resource_filters varchar2,
13 p_exception_filters varchar2,
14 p_item_filters varchar2,
15 p_page_size number)
16 is
17 l_org_filter_cnt number;
18 l_item_filter_cnt number;
19 l_dept_filter_cnt number;
20 l_res_filter_cnt number;
21 l_exception_filter_cnt number;
22 begin
23 msc_phub_util.log('msc_res_plan_pkg.prepare_row_dtls('||p_query_id||', '||p_plan_id||
24 ','||p_page_size||')');
25
26 delete from msc_form_query where query_id = p_query_id;
27 msc_phub_util.log('delete from msc_form_query: '||sql%rowcount);
28 commit;
29
30 msc_hp_util.prepare_filters(p_query_id, msc_hp_util.ft_organization, p_organization_filters);
31 msc_hp_util.prepare_filters(p_query_id, msc_hp_util.ft_department, p_department_filters);
32 msc_hp_util.prepare_filters(p_query_id, msc_hp_util.ft_resource, p_resource_filters);
33 msc_hp_util.prepare_filters(p_query_id, msc_hp_util.ft_exception, p_exception_filters);
34 msc_hp_util.prepare_filters(p_query_id, msc_hp_util.ft_item, p_item_filters);
35
36 select count(*)
37 into l_org_filter_cnt
38 from msc_form_query
39 where query_id = p_query_id and number1 = msc_hp_util.ft_organization;
40
41 select count(*)
42 into l_dept_filter_cnt
43 from msc_form_query
44 where query_id = p_query_id and number1 = msc_hp_util.ft_department;
45
46 select count(*)
47 into l_res_filter_cnt
48 from msc_form_query
49 where query_id = p_query_id and number1 = msc_hp_util.ft_resource;
50
51 select count(*)
52 into l_exception_filter_cnt
53 from msc_form_query
54 where query_id = p_query_id and number1 = msc_hp_util.ft_exception and number2 in (24,26);
55
56 select count(*)
57 into l_item_filter_cnt
58 from msc_form_query
59 where query_id = p_query_id and number1 = msc_hp_util.ft_item;
60
61 msc_phub_util.log('l_org_filter_cnt = '||l_org_filter_cnt||', '||
62 'l_dept_filter_cnt = '||l_dept_filter_cnt||', '||
63 'l_res_filter_cnt = '||l_res_filter_cnt||', '||
64 'l_exception_filter_cnt = '||l_exception_filter_cnt||', '||
65 'l_item_filter_cnt = '||l_item_filter_cnt);
66
67 delete from msc_hp_row_dtls where query_id = p_query_id;
68 msc_phub_util.log('delete from msc_hp_row_dtls: '||sql%rowcount);
69 commit;
70
71 if (l_exception_filter_cnt > 0) then
72 insert into msc_form_query(query_id, number1, number2, number3,
73 created_by, creation_date, last_update_date, last_updated_by, last_update_login)
74 select /*+ ordered */
75 p_query_id,
76 msc_hp_util.ft_end_pegging,
77 mfp1.sr_instance_id,
78 mfp1.end_pegging_id,
79 fnd_global.user_id, sysdate,
80 sysdate, fnd_global.user_id, fnd_global.login_id
81 from
82 msc_full_pegging mfp1,
83 msc_exception_details med1,
84 msc_form_query q,
85 msc_trading_partners mtp1
86 where mfp1.plan_id = p_plan_id
87 and mfp1.plan_id = med1.plan_id
88 and mfp1.prev_pegging_id is null
89 and mfp1.plan_id = med1.plan_id
90 and mfp1.demand_id = med1.number1
91 and med1.exception_type = q.number2
92 and q.query_id = p_query_id
93 and q.number1 = msc_hp_util.ft_exception
94 and q.number2 in (24,26)
95 and mfp1.sr_instance_id = mtp1.sr_instance_id
96 and mfp1.organization_id = mtp1.sr_tp_id
97 and mtp1.partner_type = 3
98 and (l_org_filter_cnt = 0 or mtp1.organization_code in (select char1 from msc_form_query where query_id = p_query_id and number1 = msc_hp_util.ft_organization))
99 and (l_item_filter_cnt = 0 or mfp1.inventory_item_id in (select number2 from msc_form_query where query_id = p_query_id and number1 = msc_hp_util.ft_item));
100 msc_phub_util.log('insert into msc_form_query: '||sql%rowcount);
101 commit;
102
103 insert into msc_hp_row_dtls(
104 query_id,
105 row_index,
106 plan_id,
107 sr_instance_id,
108 organization_id,
109 department_id,
110 resource_id,
111 organization_code,
112 department_code,
113 resource_code,
114 data_flag,
115 created_by, creation_date,
116 last_update_date, last_updated_by, last_update_login)
117 select
118 p_query_id,
119 (rank() over(order by organization_code, department_code, resource_code))-1 row_index,
120 plan_id,
121 sr_instance_id,
122 organization_id,
123 department_id,
124 resource_id,
125 organization_code,
126 department_code,
127 resource_code,
128 1 data_flag,
129 fnd_global.user_id, sysdate,
130 sysdate, fnd_global.user_id, fnd_global.login_id
131 from
132 (select distinct
133 r.plan_id,
134 r.sr_instance_id,
135 r.organization_id,
136 mdp.department_id,
137 mdp.resource_id,
138 mtp2.organization_code,
139 mdp.department_code,
140 mdp.resource_code
141 from
142 (select distinct
143 mfp2.plan_id,
144 mfp2.sr_instance_id,
145 mfp2.transaction_id,
146 mfp2.end_pegging_id,
147 mfp2.organization_id,
148 mrr.department_id,
149 mrr.resource_id
150 from
151 msc_full_pegging mfp2,
152 msc_resource_requirements mrr
153 where mfp2.plan_id = p_plan_id
154 and mfp2.plan_id = mrr.plan_id
155 and mfp2.sr_instance_id = mrr.sr_instance_id
156 --and mfp2.organization_id = mrr.organization_id
157 and mfp2.transaction_id = mrr.supply_id
158 and mrr.resource_id > 0
159 ) r,
160 msc_exception_details med2,
161 msc_form_query q,
162 msc_trading_partners mtp2,
163 msc_department_resources mdp
164 where q.query_id = p_query_id
165 and q.number1 = msc_hp_util.ft_end_pegging
166 and r.sr_instance_id = q.number2
167 and r.end_pegging_id = q.number3
168 and r.plan_id = med2.plan_id
169 and r.transaction_id = med2.number1
170 --and med2.exception_type in (36,37,59,60,62,63,66,67)
171 and med2.exception_type in (36,60,63)
172 and r.sr_instance_id = mtp2.sr_instance_id
173 and r.organization_id = mtp2.sr_tp_id
174 and mtp2.partner_type = 3
175 and r.plan_id = mdp.plan_id
176 and r.sr_instance_id = mdp.sr_instance_id
177 and r.organization_id = mdp.organization_id
178 and r.department_id = mdp.department_id
179 and r.resource_id = mdp.resource_id
180 and (l_dept_filter_cnt = 0 or mdp.department_code in (select char1 from msc_form_query where query_id = p_query_id and number1 = msc_hp_util.ft_department))
181 and (l_res_filter_cnt = 0 or mdp.resource_code in (select char1 from msc_form_query where query_id = p_query_id and number1 = msc_hp_util.ft_resource))
182 ) t;
183 msc_phub_util.log('insert into msc_hp_row_dtls: '||sql%rowcount);
184 commit;
185 else
186 insert into msc_hp_row_dtls(
187 query_id,
188 row_index,
189 plan_id,
190 sr_instance_id,
191 organization_id,
192 department_id,
193 resource_id,
194 organization_code,
195 department_code,
196 resource_code,
197 data_flag,
198 created_by, creation_date,
199 last_update_date, last_updated_by, last_update_login)
200 select
201 p_query_id,
202 (rank() over(order by mtp.organization_code, mdp.department_code, mdp.resource_code))-1 row_index,
203 mdp.plan_id,
204 mdp.sr_instance_id,
205 mdp.organization_id,
206 mdp.department_id,
207 mdp.resource_id,
208 mtp.organization_code,
209 mdp.department_code,
210 mdp.resource_code,
211 1 data_flag,
212 fnd_global.user_id, sysdate,
213 sysdate, fnd_global.user_id, fnd_global.login_id
214 from
215 msc_department_resources mdp,
216 msc_trading_partners mtp
217 where mdp.plan_id = p_plan_id
218 and mdp.sr_instance_id = mtp.sr_instance_id
219 and mdp.organization_id = mtp.sr_tp_id
220 and mtp.partner_type = 3
221 and mdp.resource_id > 0
222 and (l_org_filter_cnt = 0 or mtp.organization_code in (select char1 from msc_form_query where query_id = p_query_id and number1 = msc_hp_util.ft_organization))
223 and (l_dept_filter_cnt = 0 or mdp.department_code in (select char1 from msc_form_query where query_id = p_query_id and number1 = msc_hp_util.ft_department))
224 and (l_res_filter_cnt = 0 or mdp.resource_code in (select char1 from msc_form_query where query_id = p_query_id and number1 = msc_hp_util.ft_resource));
225 msc_phub_util.log('insert into msc_hp_row_dtls: '||sql%rowcount);
226 commit;
227 end if;
228
229 insert into msc_hp_row_dtls(
230 query_id,
231 row_index,
232 plan_id,
233 data_flag,
234 created_by, creation_date,
235 last_update_date, last_updated_by, last_update_login)
236 values (p_query_id, -1, p_plan_id, 1,
237 fnd_global.user_id, sysdate,
238 sysdate, fnd_global.user_id, fnd_global.login_id);
239 msc_phub_util.log('insert into msc_hp_row_dtls: '||sql%rowcount);
240 commit;
241
242 g_page_size := p_page_size;
243 g_fetch_size := ceil(g_max_fetch_size/g_page_size) * g_page_size;
244 msc_phub_util.log('g_max_fetch_size = '||g_max_fetch_size||', '||
245 'g_page_size = '||g_page_size||', '||
246 'g_fetch_size = '||g_fetch_size);
247
248 end prepare_row_dtls;
249
250 procedure populate_data(p_query_id number, p_summary number)
251 is
252 l_new_cnt number;
253 l_constrained_plan number := null;
254 l_plan_id number;
255 l_past_date date;
256 begin
257 msc_phub_util.log('msc_res_plan_pkg.populate_data('||p_query_id||', '||p_summary||')');
258
259 begin
260 select h.plan_id, b.bkt_start_date
261 into l_plan_id, l_past_date
262 from msc_hp_row_dtls h, msc_hp_col_dtls b
263 where h.query_id = p_query_id
264 and h.data_flag = decode(p_summary, 1, h.data_flag, 3)
265 and h.plan_id = b.plan_id
266 and b.bucket_type = 0
267 and rownum = 1;
268 msc_phub_util.log('l_plan_id = '||l_plan_id||', l_past_date = '||l_past_date);
269 exception
270 when no_data_found then return;
271 end;
272
273 delete from msc_res_plan_data
274 where (query_id, row_index) in
275 (select
276 h.query_id,
277 decode(p_summary, 1, -1, h.row_index) row_index
278 from msc_hp_row_dtls h
279 where h.query_id = p_query_id
280 and h.data_flag = decode(p_summary, 1, h.data_flag, 3));
281 msc_phub_util.log('delete from msc_res_plan_data: '||sql%rowcount);
282 commit;
283
284 l_constrained_plan := msc_phub_util.is_plan_constrained(l_plan_id);
285 msc_phub_util.log('l_constrained_plan = '||l_constrained_plan);
286
287 insert into msc_res_plan_data(
288 query_id,
289 row_index,
290 analysis_date,
291 required_hours,
292 available_hours,
293 non_standard_jobs,
294 planned_order,
295 work_order,
296 total_resource_cost,
297 created_by, creation_date,
298 last_update_date, last_updated_by, last_update_login)
299 select
300 p_query_id,
301 row_index,
302 analysis_date,
303 sum(required_hours) required_hours,
304 sum(available_hours) available_hours,
305 sum(non_standard_jobs) non_standard_jobs,
306 sum(planned_order) planned_order,
307 sum(work_order) work_order,
308 sum(total_resource_cost) total_resource_cost,
309 fnd_global.user_id, sysdate,
310 sysdate, fnd_global.user_id, fnd_global.login_id
311 from
312 (
313 -- requirements, non-batchable
314 select
315 decode(p_summary, 1, -1, h.row_index) row_index,
316 b.bkt_start_date analysis_date,
317 sum(decode(mdr.line_flag,
318 2, decode(mrr.end_date, null, mrr.resource_hours, mrr.daily_resource_hours),
319 mdr.max_rate * mrr.daily_resource_hours)) required_hours,
320 to_number(null) available_hours,
321 to_number(null) non_standard_jobs,
322 to_number(null) planned_order,
323 to_number(null) work_order,
324 to_number(null) total_resource_cost
325 from
326 msc_resource_requirements mrr,
327 msc_department_resources mdr,
328 msc_hp_col_dtls b,
329 msc_hp_row_dtls h
330 where h.query_id = p_query_id
331 and mrr.plan_id = h.plan_id
332 and mrr.sr_instance_id = h.sr_instance_id
333 and mrr.organization_id = h.organization_id
334 and mrr.department_id = h.department_id
335 and mrr.resource_id = h.resource_id
336 and h.data_flag = decode(p_summary, 1, h.data_flag, 3)
337 and mdr.plan_id = mrr.plan_id
338 and mdr.sr_instance_id = mrr.sr_instance_id
339 and mdr.organization_id = mrr.organization_id
340 and mdr.department_id = mrr.department_id
341 and mdr.resource_id = mrr.resource_id
342 and mrr.resource_id > 0
343 --and nvl(mdr.batchable_flag,2) = 2
344 and mrr.batch_number is null
345 and mrr.plan_id = b.plan_id
346 and trunc(decode(l_constrained_plan,1,mrr.start_date,nvl(mrr.end_date,mrr.start_date)))
347 between b.bkt_start_date and b.bkt_end_date
348 and b.bucket_index>0
349 and nvl(mrr.parent_id, l_constrained_plan) = l_constrained_plan
350 group by
351 decode(p_summary, 1, -1, h.row_index),
352 b.bkt_start_date
353 union all
354 -- requirements, batchable
355 select
356 decode(p_summary, 1, -1, h.row_index) row_index,
357 b.bkt_start_date analysis_date,
358 avg(mrr.resource_hours) required_hours,
359 to_number(null) available_hours,
360 to_number(null) non_standard_jobs,
361 to_number(null) planned_order,
362 to_number(null) work_order,
363 to_number(null) total_resource_cost
364 from
365 msc_resource_requirements mrr,
366 msc_department_resources mdr,
367 msc_hp_col_dtls b,
368 msc_hp_row_dtls h
369 where h.query_id = p_query_id
370 and mrr.plan_id = h.plan_id
371 and mrr.sr_instance_id = h.sr_instance_id
372 and mrr.organization_id = h.organization_id
373 and mrr.department_id = h.department_id
374 and mrr.resource_id = h.resource_id
375 and h.data_flag = decode(p_summary, 1, h.data_flag, 3)
376 and mdr.plan_id = mrr.plan_id
377 and mdr.sr_instance_id = mrr.sr_instance_id
378 and mdr.organization_id = mrr.organization_id
379 and mdr.department_id = mrr.department_id
380 and mdr.resource_id = mrr.resource_id
381 and mrr.resource_id > 0
382 --and nvl(mdr.batchable_flag,2) = 2
383 and mrr.batch_number is not null
384 and mrr.supply_type in (3,4,5,7,27,60)
385 and mrr.plan_id = b.plan_id
386 and trunc(decode(l_constrained_plan,1,mrr.start_date,nvl(mrr.end_date,mrr.start_date)))
387 between b.bkt_start_date and b.bkt_end_date
388 and b.bucket_index>0
389 and nvl(mrr.parent_id, l_constrained_plan) = l_constrained_plan
390 group by
391 decode(p_summary, 1, -1, h.row_index),
392 b.bkt_start_date
393 union all
394 -- availability, PDS
395 select
396 decode(p_summary, 1, -1, h.row_index) row_index,
397 b.bkt_start_date analysis_date,
398 to_number(null) required_hours,
399 sum(mra.capacity_units * decode(mra.from_time, null, 0,
400 (decode(sign(mra.to_time-mra.from_time),
401 -1, mra.to_time+86400, mra.to_time) - mra.from_time)/3600)) available_hours,
402 to_number(null) non_standard_jobs,
403 to_number(null) planned_order,
404 to_number(null) work_order,
405 to_number(null) total_resource_cost
406 from
407 msc_net_resource_avail mra,
408 msc_hp_col_dtls b,
409 msc_hp_row_dtls h
410 where h.query_id = p_query_id
411 and mra.plan_id = h.plan_id
412 and mra.sr_instance_id = h.sr_instance_id
413 and mra.organization_id = h.organization_id
414 and mra.department_id = h.department_id
415 and mra.resource_id = h.resource_id
416 and h.data_flag = decode(p_summary, 1, h.data_flag, 3)
417 and mra.resource_id > 0
418 and mra.capacity_units >= 0
419 and mra.plan_id = b.plan_id
420 and trunc(mra.shift_date) between b.bkt_start_date and b.bkt_end_date
421 and b.bucket_index>0
422 and nvl(mra.parent_id,0) <> -1
423 and not exists (
424 select 1
425 from msc_res_plan_updates a2
426 where mra.plan_id = a2.plan_id
427 and mra.sr_instance_id = a2.sr_instance_id
428 and mra.organization_id = a2.organization_id
429 and mra.department_id = a2.department_id
430 and mra.resource_id = a2.resource_id
431 and mra.shift_date = a2.shift_date
432 and a2.query_id = h.query_id
433 )
434 group by
435 decode(p_summary, 1, -1, h.row_index),
436 b.bkt_start_date
437 union all
438 -- availability, updates
439 select
440 decode(p_summary, 1, -1, h.row_index) row_index,
441 b.bkt_start_date analysis_date,
442 to_number(null) required_hours,
443 sum(a2.capacity_units * decode(a2.from_time, null, 0,
444 (decode(sign(a2.to_time-a2.from_time),
445 -1, a2.to_time+86400, a2.to_time) - a2.from_time)/3600)) available_hours,
446 to_number(null) non_standard_jobs,
447 to_number(null) planned_order,
448 to_number(null) work_order,
449 to_number(null) total_resource_cost
450 from
451 msc_res_plan_updates a2,
452 msc_hp_col_dtls b,
453 msc_hp_row_dtls h
454 where h.query_id = p_query_id
455 and a2.query_id = h.query_id
456 and a2.plan_id = h.plan_id
457 and a2.sr_instance_id = h.sr_instance_id
458 and a2.organization_id = h.organization_id
459 and a2.department_id = h.department_id
460 and a2.resource_id = h.resource_id
461 and h.data_flag = decode(p_summary, 1, h.data_flag, 3)
462 and a2.resource_id > 0
463 and a2.capacity_units >= 0
464 and a2.plan_id = b.plan_id
465 and trunc(a2.shift_date) between b.bkt_start_date and b.bkt_end_date
466 and b.bucket_index>0
467 and nvl(a2.parent_id,0) <> -1
468 group by
469 decode(p_summary, 1, -1, h.row_index),
470 b.bkt_start_date
471 union all
472 -- orders
473 select
474 decode(p_summary, 1, -1, h.row_index) row_index,
475 b.bkt_start_date analysis_date,
476 to_number(null) required_hours,
477 to_number(null) available_hours,
478 sum(decode(ms.order_type, 7, decode(mrr.batch_number, null,
479 (decode(mdr.line_flag, 2, decode(mrr.end_date, null, mrr.resource_hours, mrr.daily_resource_hours),
480 mdr.max_rate * mrr.daily_resource_hours)),
481 (case when mrr.supply_type in (3,4,5,7,27,60) then mrr.resource_hours end)))) non_standard_jobs,
482 sum(decode(ms.order_type, 5, decode(mrr.batch_number, null,
483 (decode(mdr.line_flag, 2, decode(mrr.end_date, null, mrr.resource_hours, mrr.daily_resource_hours),
484 mdr.max_rate * mrr.daily_resource_hours)),
485 (case when mrr.supply_type in (3,4,5,7,27,60) then mrr.resource_hours end)))) planned_order,
486 sum(decode(ms.order_type, 3, decode(mrr.batch_number, null,
487 (decode(mdr.line_flag, 2, decode(mrr.end_date, null, mrr.resource_hours, mrr.daily_resource_hours),
488 mdr.max_rate * mrr.daily_resource_hours)),
489 (case when mrr.supply_type in (3,4,5,7,27,60) then mrr.resource_hours end)))) work_order,
490 sum(case when ms.order_type in (3,4,5,7,27,60,70,74,79,92) then
491 decode(mrr.batch_number, null,
492 (decode(mdr.line_flag, 2, decode(mrr.end_date, null, mrr.resource_hours, mrr.daily_resource_hours),
493 mdr.max_rate * mrr.daily_resource_hours)),
494 (case when mrr.supply_type in (3,4,5,7,27,60) then mrr.resource_hours end)) *
495 mdr.resource_cost * (1 + nvl(mdr2.dept_overhead_cost,0)) end)
496 total_resource_cost
497 from
498 msc_resource_requirements mrr,
499 msc_department_resources mdr,
500 msc_department_resources mdr2,
501 msc_supplies ms,
502 msc_hp_col_dtls b,
503 msc_hp_row_dtls h
504 where h.query_id = p_query_id
505 and mrr.plan_id = h.plan_id
506 and mrr.sr_instance_id = h.sr_instance_id
507 and mrr.organization_id = h.organization_id
508 and mrr.department_id = h.department_id
509 and mrr.resource_id = h.resource_id
510 and h.data_flag = decode(p_summary, 1, h.data_flag, 3)
511 and nvl(mrr.parent_id, l_constrained_plan) = l_constrained_plan
512 and nvl(mrr.schedule_flag,2) = 1
513 and mrr.plan_id = ms.plan_id
514 and mrr.sr_instance_id = ms.sr_instance_id
515 and mrr.organization_id = ms.organization_id
516 and mrr.supply_id = ms.transaction_id
517 and mrr.resource_id > 0
518 and mrr.plan_id = b.plan_id
519 and trunc(decode(l_constrained_plan,1,mrr.start_date,nvl(mrr.end_date,mrr.start_date)))
520 between b.bkt_start_date and b.bkt_end_date
521 and b.bucket_index>0
522 and mrr.plan_id = mdr.plan_id
523 and mrr.sr_instance_id = mdr.sr_instance_id
524 and mrr.organization_id = mdr.organization_id
525 and mrr.department_id = mdr.department_id
526 and mrr.resource_id = mdr.resource_id
527 and mdr2.plan_id = -1
528 and mrr.sr_instance_id = mdr2.sr_instance_id
529 and mrr.organization_id = mdr2.organization_id
530 and mrr.department_id = mdr2.department_id
531 and mrr.resource_id = mdr2.resource_id
532 and mdr2.line_flag<>1
533 group by
534 decode(p_summary, 1, -1, h.row_index),
535 b.bkt_start_date
536 )
537 group by
538 row_index,
539 analysis_date;
540 msc_phub_util.log('insert into msc_res_plan_data: '||sql%rowcount);
541 commit;
542
543 update msc_hp_row_dtls
544 set data_flag = 2
545 where query_id = p_query_id and data_flag = 3;
546 msc_phub_util.log('update msc_hp_row_dtls: '||sql%rowcount);
547 commit;
548
549 end populate_data;
550
551 procedure summarize_data(p_query_id number)
552 is
553 begin
554 msc_phub_util.log('msc_res_plan_pkg.summarize_data('||p_query_id||')');
555
556 update msc_hp_row_dtls
557 set data_flag = 3
558 where query_id = p_query_id
559 and row_index = -1
560 and data_flag = 1;
561 commit;
562
563 populate_data(p_query_id, 1);
564 end summarize_data;
565
566 procedure initialize_data(
567 p_query_id number,
568 p_plan_id number,
569 p_organization_filters varchar2,
570 p_department_filters varchar2,
571 p_resource_filters varchar2,
572 p_exception_filters varchar2,
573 p_item_filters varchar2,
574 p_page_size number)
575 is
576 begin
577 msc_phub_util.log('msc_res_plan_pkg.initialize_data('||p_query_id||', '||p_plan_id||
578 ','||p_page_size||')');
579
580 msc_hp_util.prepare_col_dtls(p_plan_id, 2);
581
582 prepare_row_dtls(p_query_id, p_plan_id,
583 p_organization_filters, p_department_filters, p_resource_filters, p_exception_filters, p_item_filters,
584 p_page_size);
585
586 delete from msc_res_plan_data where query_id = p_query_id;
587 commit;
588
589 fetch_data(p_query_id, 0);
590 end initialize_data;
591
592
593 procedure fetch_data(p_query_id number, p_row_index number)
594 is
595 l_n number;
596 begin
597 msc_phub_util.log('msc_res_plan_pkg.fetch_data('||p_query_id||', '||p_row_index||')');
598
599 select count(*) into l_n
600 from msc_hp_row_dtls
601 where query_id = p_query_id
602 and row_index between p_row_index and p_row_index+g_page_size-1
603 and data_flag = 1;
604 if (l_n = 0) then
605 return;
606 end if;
607
608 update msc_hp_row_dtls
609 set data_flag = 3
610 where query_id = p_query_id
611 and row_index between p_row_index and p_row_index+g_fetch_size-1
612 and data_flag = 1;
613 commit;
614
615 populate_data(p_query_id, 2);
616 end fetch_data;
617
618 procedure recalculate(p_query_id number)
619 is
620 cursor cu is
621 select
622 h.row_index,
623 h.plan_id,
624 h.sr_instance_id,
625 h.organization_id,
626 h.department_id,
627 h.resource_id,
628 u.analysis_date,
629 u.time_level,
630 decode(u.time_level, 2, b.week_start_date, 3, period_start_date, bkt_start_date) start_date,
631 decode(u.time_level, 2, b.week_end_date, 3, period_end_date, bkt_end_date) end_date,
632 decode(u.time_level, 2, b.week_last_work_date, 3, period_last_work_date, bkt_end_date) last_work_date,
633 u.new_value
634 from
635 msc_hp_updates u,
636 msc_hp_row_dtls h,
637 msc_hp_col_dtls b
638 where h.query_id = p_query_id
639 and h.query_id = u.query_id
640 and h.row_index = u.row_index
641 and h.plan_id = b.plan_id
642 and u.analysis_date = b.bkt_start_date
643 and u.hp_type_code = 'MSC_ASCP_RES_PLAN_TYPE'
644 and u.column_name = 'available_hours'
645 and u.new_value >= 0
646 and nvl(u.process_status, 1) = 1;
647
648 cursor c (
649 p_query_id number,
650 p_plan_id number,
651 p_sr_instance_id number,
652 p_organization_id number,
653 p_department_id number,
654 p_resource_id number,
655 p_start_date date,
656 p_end_date date)
657 is
658 select
659 query_id,
660 plan_id,
661 sr_instance_id,
662 organization_id,
663 department_id,
664 resource_id,
665 shift_num,
666 shift_date,
667 from_time,
668 to_time,
669 from_time2,
670 to_time2,
671 min(from_time2) over(partition by shift_date order by shift_date, from_time) first_from_time2,
672 max(to_time2) over(partition by shift_date order by shift_date, from_time) last_to_time2,
673 lead(from_time2) over(order by shift_date, from_time) next_from_time2,
674 lag(to_time2) over(order by shift_date, from_time) prev_to_time2,
675 capacity_units,
676 parent_id,
677 decode(from_time, null, 0,
678 (decode(sign(to_time-from_time),
679 -1, to_time+86400, to_time) - from_time)/3600) shift_capacity
680 from msc_res_plan_updates
681 where query_id = p_query_id
682 and plan_id = p_plan_id
683 and sr_instance_id = p_sr_instance_id
684 and organization_id = p_organization_id
685 and department_id = p_department_id
686 and resource_id = p_resource_id
687 and shift_date between p_start_date and p_end_date
688 order by shift_date, from_time
689 for update;
690
691 r c%rowtype;
692 l_calendar_code varchar2(30);
693 l_difference number;
694 l_change number;
695 l_total_capacity number;
696 l_new_to_time2 date;
697 l_new_from_time2 date;
698 begin
699 msc_phub_util.log('msc_res_plan_pkg.recalculate('||p_query_id||')');
700 l_calendar_code := msc_hp_util.get_calendar_code(msc_hp_util.get_plan_id(p_query_id));
701
702 for ru in cu loop
703 -- prepare staging table
704 msc_phub_util.log('ru='||ru.sr_instance_id||','||ru.organization_id||','||
705 ru.department_id||','||ru.resource_id||','||
706 ru.start_date||','||ru.end_date);
707
708 /*
709 msc_phub_util.log(
710 ' from msc_net_resource_avail mra, msc_calendar_dates md'||
711 ' where mra.plan_id(+) = '||ru.plan_id||
712 ' and mra.sr_instance_id(+) = '||ru.sr_instance_id||
713 ' and mra.organization_id(+) = '||ru.organization_id||
714 ' and mra.department_id(+) = '||ru.department_id||
715 ' and mra.resource_id(+) = '||ru.resource_id||
716 ' and trunc(mra.shift_date(+)) = md.calendar_date'||
717 ' and md.calendar_date between '''||(ru.start_date)||''' and '''||(ru.end_date)||''''||
718 ' and md.calendar_code = '''||l_calendar_code||''''||
719 ' and md.exception_set_id = -1'||
720 ' and nvl(mra.parent_id(+),0) <> -1');
721 */
722 delete from msc_res_plan_updates
723 where query_id = p_query_id
724 and sr_instance_id = ru.sr_instance_id
725 and organization_id = ru.organization_id
726 and department_id = ru.department_id
727 and resource_id = ru.resource_id
728 and shift_date between ru.start_date and ru.end_date;
729 msc_phub_util.log('delete from msc_res_plan_updates: '||sql%rowcount);
730 commit;
731
732 insert into msc_res_plan_updates (
733 query_id,
734 plan_id,
735 transaction_id,
736 sr_instance_id,
737 organization_id,
738 department_id,
739 resource_id,
740 shift_num,
741 shift_date,
742 from_time,
743 to_time,
744 from_time2,
745 to_time2,
746 parent_id,
747 capacity_units,
748 created_by, creation_date,
749 last_update_date, last_updated_by, last_update_login)
750 select
751 p_query_id,
752 ru.plan_id plan_id,
753 nvl(mra.transaction_id, -1) transaction_id,
754 ru.sr_instance_id sr_instance_id,
755 ru.organization_id organization_id,
756 ru.department_id department_id,
757 ru.resource_id resource_id,
758 mra.shift_num,
759 md.calendar_date shift_date,
760 mra.from_time,
761 mra.to_time,
762 mra.shift_date+(mra.from_time/86400) from_time2,
763 mra.shift_date+(decode(sign(mra.to_time-mra.from_time), -1, mra.to_time+86400, mra.to_time))/86400 to_time2,
764 mra.parent_id,
765 mra.capacity_units,
766 fnd_global.user_id, sysdate,
767 sysdate, fnd_global.user_id, fnd_global.login_id
768 from msc_net_resource_avail mra, msc_calendar_dates md
769 where mra.plan_id(+) = ru.plan_id
770 and mra.sr_instance_id(+) = ru.sr_instance_id
771 and mra.organization_id(+) = ru.organization_id
772 and mra.department_id(+) = ru.department_id
773 and mra.resource_id(+) = ru.resource_id
774 and trunc(mra.shift_date(+)) = md.calendar_date
775 and md.calendar_date between ru.start_date and ru.end_date
776 and md.calendar_code = l_calendar_code
777 and md.exception_set_id = -1
778 and nvl(mra.parent_id(+),0) <> -1;
779 msc_phub_util.log('insert into msc_res_plan_updates: '||sql%rowcount);
780 commit;
781
782 select
783 sum(nvl(decode(from_time, null, 0,
784 (decode(sign(to_time-from_time),
785 -1, to_time+86400, to_time) - from_time)/3600) * capacity_units, 0))
786 total_capacity
787 into l_total_capacity
788 from msc_res_plan_updates
789 where query_id = p_query_id
790 and plan_id = ru.plan_id
791 and sr_instance_id = ru.sr_instance_id
792 and organization_id = ru.organization_id
793 and department_id = ru.department_id
794 and resource_id = ru.resource_id
795 and shift_date between ru.start_date and ru.end_date;
796
797
798 /*
799 msc_phub_util.log(
800 ' from msc_res_plan_updates'||
801 ' where query_id = '||p_query_id||
802 ' and plan_id = '||ru.plan_id||
803 ' and sr_instance_id = '||ru.sr_instance_id||
804 ' and organization_id = '||ru.organization_id||
805 ' and department_id = '||ru.department_id||
806 ' and resource_id = '||ru.resource_id||
807 ' and trunc(shift_date) between to_date('''||ru.start_date||''') and to_date('''||ru.end_date||''')');
808 */
809
810 l_difference := (ru.new_value-l_total_capacity)*3600;
811 msc_phub_util.log('l_difference = '||(l_difference/3600)||', l_total_capacity = '||l_total_capacity);
812
813 if (l_difference < 0) then
814 msc_phub_util.log('decrease: proportionally adjust');
815 open c (
816 p_query_id,
817 ru.plan_id,
818 ru.sr_instance_id,
819 ru.organization_id,
820 ru.department_id,
821 ru.resource_id,
822 ru.start_date,
823 ru.end_date);
824 fetch c into r;
825 while (not c%notfound) loop
826 if (r.from_time is not null) then
827 if (ru.new_value = 0) then
828 r.to_time := r.from_time;
829 r.to_time2 := r.from_time2;
830
831 msc_phub_util.log('D1:'||r.shift_num||','||r.shift_date||','||round(r.from_time)||','||round(r.to_time)||','||
832 round((r.to_time2 - r.from_time2)*24)||','||r.capacity_units);
833
834 update msc_res_plan_updates
835 set from_time = r.from_time,
836 from_time2 = r.from_time2,
837 to_time = r.to_time,
838 to_time2 = r.to_time2,
839 change_flag = 1,
840 last_update_date = sysdate,
841 last_updated_by = fnd_global.user_id,
842 last_update_login = fnd_global.login_id
843 where current of c;
844 else
845 r.to_time2 := r.to_time2 + (l_difference*r.shift_capacity/l_total_capacity/86400);
846 r.to_time := (r.to_time2 - trunc(r.to_time2, 'DDD'))*86400;
847
848 msc_phub_util.log('D2:'||r.shift_num||','||r.shift_date||','||round(r.from_time)||','||round(r.to_time)||','||
849 round((r.to_time2 - r.from_time2)*24)||','||r.capacity_units);
850
851 update msc_res_plan_updates
852 set to_time = r.to_time,
853 to_time2 = r.to_time2,
854 change_flag = 1,
855 last_update_date = sysdate,
856 last_updated_by = fnd_global.user_id,
857 last_update_login = fnd_global.login_id
858 where current of c;
859 end if;
860 end if;
861 fetch c into r;
862 end loop;
863 close c;
864 end if;
865
866 if (l_difference > 0) then
867 msc_phub_util.log('step 1: adjust last shift size on working days');
868 open c (
869 p_query_id,
870 ru.plan_id,
871 ru.sr_instance_id,
872 ru.organization_id,
873 ru.department_id,
874 ru.resource_id,
875 ru.start_date,
876 ru.end_date);
877 fetch c into r;
878 while (not c%notfound and l_difference>0) loop
879 /*
880 msc_phub_util.log('r1:('||r.shift_date||','||ru.start_date||','||ru.end_date||'),'||
881 'r2:('||r.capacity_units||','||r.from_time2||','||r.to_time2||'),'||
882 'r3:('||r.first_from_time2||','||r.last_to_time2||'),'||
883 'r4:('||r.prev_to_time2||','||r.next_from_time2||')');
884 */
885
886 if (r.shift_date between ru.start_date and ru.end_date
887 and nvl(r.capacity_units, 0) > 0
888 and r.last_to_time2 - r.first_from_time2 < 1
889 and r.from_time is not null
890 and (r.next_from_time2 is null or trunc(r.from_time2,'DDD')<>trunc(r.next_from_time2,'DDD'))) then
891
892 if (r.prev_to_time2 is null) then
893 select max(to_time2)
894 into r.prev_to_time2
895 from msc_res_plan_updates
896 where query_id = p_query_id
897 and sr_instance_id = r.sr_instance_id
898 and organization_id = r.organization_id
899 and department_id = r.department_id
900 and resource_id = r.resource_id
901 and shift_date = r.shift_date - 1;
902 end if;
903
904 if (r.prev_to_time2 is null) then
905 select max(shift_date+(decode(sign(to_time-from_time), -1, to_time+86400, to_time))/86400)
906 into r.prev_to_time2
907 from msc_net_resource_avail
908 where plan_id = r.plan_id
909 and sr_instance_id = r.sr_instance_id
910 and organization_id = r.organization_id
911 and department_id = r.department_id
912 and resource_id = r.resource_id
913 and shift_date = r.shift_date - 1;
914 end if;
915
916 -- extend start time
917 l_new_from_time2 := greatest(
918 r.shift_date,
919 nvl(r.prev_to_time2, r.shift_date),
920 r.last_to_time2 - 1,
921 r.from_time2 - l_difference/r.capacity_units/86400);
922
923 l_change := (r.from_time2 - l_new_from_time2)*86400;
924 msc_phub_util.log('r.prev_to_time2='||r.prev_to_time2||', r.from_time2='||r.from_time2||
925 ', l_new_from_time2='||l_new_from_time2||', l_change='||round(l_change));
926 r.from_time := r.from_time - l_change;
927 r.from_time2 := l_new_from_time2;
928 l_difference := l_difference - l_change*r.capacity_units;
929 l_total_capacity := l_total_capacity + l_change*r.capacity_units/3600;
930
931 if (r.from_time2 < r.first_from_time2) then
932 r.first_from_time2 := r.from_time2;
933 end if;
934
935 msc_phub_util.log('1A:'||r.shift_num||','||r.shift_date||','||round(l_change)||','||round(r.from_time)||','||round(r.to_time)||','||
936 round((r.to_time2 - r.from_time2)*24)||','||r.capacity_units||','||round(l_total_capacity)||','||round(l_difference/3600));
937
938 if (l_difference > 0) then
939 -- extend end time
940 if (r.next_from_time2 is null) then
941 select min(from_time2)
942 into r.next_from_time2
943 from msc_res_plan_updates
944 where query_id = p_query_id
945 and sr_instance_id = r.sr_instance_id
946 and organization_id = r.organization_id
947 and department_id = r.department_id
948 and resource_id = r.resource_id
949 and shift_date = r.shift_date + 1;
950 end if;
951
952 if (r.next_from_time2 is null) then
953 select min(shift_date+(from_time/86400))
954 into r.next_from_time2
955 from msc_net_resource_avail
956 where plan_id = r.plan_id
957 and sr_instance_id = r.sr_instance_id
958 and organization_id = r.organization_id
959 and department_id = r.department_id
960 and resource_id = r.resource_id
961 and shift_date = r.shift_date + 1;
962 end if;
963
964 l_new_to_time2 := least(
965 nvl(r.next_from_time2, r.first_from_time2 + 1),
966 r.first_from_time2 + 1,
967 r.to_time2 + l_difference/r.capacity_units/86400);
968
969 l_change := (l_new_to_time2 - r.to_time2)*86400;
970 msc_phub_util.log('r.next_from_time2='||r.next_from_time2||', r.to_time2='||r.to_time2||
971 ', l_new_to_time2='||l_new_to_time2||', l_change='||round(l_change));
972 r.to_time := r.to_time + l_change;
973 if (r.to_time > 86400) then
974 r.to_time := r.to_time - 86400;
975 end if;
976
977 r.to_time2 := l_new_to_time2;
978 l_difference := l_difference - l_change*r.capacity_units;
979 l_total_capacity := l_total_capacity + l_change*r.capacity_units/3600;
980
981 msc_phub_util.log('1B:'||r.shift_num||','||r.shift_date||','||round(l_change)||','||round(r.from_time)||','||round(r.to_time)||','||
982 round((r.to_time2 - r.from_time2)*24)||','||r.capacity_units||','||round(l_total_capacity)||','||round(l_difference/3600));
983 end if;
984
985 update msc_res_plan_updates
986 set from_time = r.from_time,
987 from_time2 = r.from_time2,
988 to_time = r.to_time,
989 to_time2 = r.to_time2,
990 change_flag = 1,
991 last_update_date = sysdate,
992 last_updated_by = fnd_global.user_id,
993 last_update_login = fnd_global.login_id
994 where current of c;
995 end if;
996 fetch c into r;
997 end loop;
998 close c;
999
1000 msc_phub_util.log('step 2: add shifts to non-shift days');
1001 open c (
1002 p_query_id,
1003 ru.plan_id,
1004 ru.sr_instance_id,
1005 ru.organization_id,
1006 ru.department_id,
1007 ru.resource_id,
1008 ru.start_date,
1009 ru.end_date);
1010 fetch c into r;
1011 while (not c%notfound and l_difference>0) loop
1012 if (r.shift_num is null) then
1013 l_change := least(
1014 86400,
1015 l_difference);
1016
1017 r.shift_num := 1;
1018 r.capacity_units := 1;
1019 r.from_time := 0;
1020 r.from_time2 := r.shift_date;
1021 r.to_time := l_change;
1022 r.to_time2 := r.shift_date + l_change/86400;
1023 r.capacity_units := 1;
1024 l_difference := l_difference - l_change*r.capacity_units;
1025 l_total_capacity := l_total_capacity + l_change*r.capacity_units/3600;
1026
1027 msc_phub_util.log('2:'||r.shift_num||','||r.shift_date||','||round(r.from_time)||','||round(r.to_time)||','||
1028 round((r.to_time2 - r.from_time2)*24)||','||r.capacity_units||','||round(l_total_capacity)||','||round(l_difference/3600));
1029
1030 update msc_res_plan_updates
1031 set from_time = r.from_time,
1032 from_time2 = r.from_time2,
1033 to_time = r.to_time,
1034 to_time2 = r.to_time2,
1035 shift_num = r.shift_num,
1036 capacity_units = r.capacity_units,
1037 change_flag = 1,
1038 last_update_date = sysdate,
1039 last_updated_by = fnd_global.user_id,
1040 last_update_login = fnd_global.login_id
1041 where current of c;
1042 end if;
1043 fetch c into r;
1044 end loop;
1045 close c;
1046
1047 msc_phub_util.log('step 3: add units');
1048 while (l_difference > 0) loop
1049 open c (
1050 p_query_id,
1051 ru.plan_id,
1052 ru.sr_instance_id,
1053 ru.organization_id,
1054 ru.department_id,
1055 ru.resource_id,
1056 ru.start_date,
1057 ru.end_date);
1058 fetch c into r;
1059 while (not c%notfound and l_difference>0) loop
1060 r.capacity_units := r.capacity_units + 1;
1061 l_difference := l_difference - r.shift_capacity*3600;
1062 l_total_capacity := l_total_capacity + r.shift_capacity;
1063
1064 msc_phub_util.log('3A:'||r.shift_num||','||r.shift_date||','||round(r.from_time)||','||round(r.to_time)||','||
1065 round((r.to_time2 - r.from_time2)*24)||','||r.capacity_units||','||round(l_total_capacity)||','||round(l_difference/3600));
1066
1067 if (l_difference < 0) then
1068 r.to_time2 := r.to_time2 + l_difference/r.capacity_units/86400;
1069 r.to_time := (r.to_time2 - trunc(r.to_time2, 'DDD'))*86400;
1070 l_total_capacity := l_total_capacity + l_difference/3600;
1071 l_difference := 0;
1072
1073 msc_phub_util.log('3B:'||r.shift_num||','||r.shift_date||','||round(r.from_time)||','||round(r.to_time)||','||
1074 round((r.to_time2 - r.from_time2)*24)||','||r.capacity_units||','||round(l_total_capacity)||','||round(l_difference/3600));
1075 end if;
1076
1077 update msc_res_plan_updates
1078 set from_time = r.from_time,
1079 from_time2 = r.from_time2,
1080 to_time = r.to_time,
1081 to_time2 = r.to_time2,
1082 capacity_units = r.capacity_units,
1083 change_flag = 1,
1084 last_update_date = sysdate,
1085 last_updated_by = fnd_global.user_id,
1086 last_update_login = fnd_global.login_id
1087 where current of c;
1088 fetch c into r;
1089 end loop;
1090 close c;
1091 end loop;
1092 end if;
1093
1094 update msc_hp_row_dtls
1095 set data_flag = 3
1096 where query_id = p_query_id
1097 and row_index = ru.row_index;
1098 msc_phub_util.log('update msc_hp_row_dtls: '||sql%rowcount);
1099 commit;
1100 end loop;
1101
1102 populate_data(p_query_id, 2);
1103 summarize_data(p_query_id);
1104
1105 update msc_hp_updates set
1106 process_status = 2,
1107 last_update_date = sysdate,
1108 last_updated_by = fnd_global.user_id,
1109 last_update_login = fnd_global.login_id
1110 where query_id = p_query_id and nvl(process_status, 1) = 1;
1111 msc_phub_util.log('update msc_hp_updates: '||sql%rowcount);
1112 commit;
1113
1114 end recalculate;
1115
1116 procedure close_data(p_query_id number)
1117 is
1118 begin
1119 msc_phub_util.log('msc_res_plan_pkg.close_data('||p_query_id||')');
1120
1121 delete from msc_hp_row_dtls where query_id = p_query_id;
1122 msc_phub_util.log('delete from msc_hp_row_dtls: '||sql%rowcount);
1123 commit;
1124
1125 delete from msc_res_plan_data where query_id = p_query_id;
1126 msc_phub_util.log('delete from msc_res_plan_data: '||sql%rowcount);
1127 commit;
1128
1129 delete from msc_hp_updates where query_id = p_query_id;
1130 msc_phub_util.log('delete from msc_hp_updates: '||sql%rowcount);
1131 commit;
1132
1133 delete from msc_res_plan_updates where query_id = p_query_id;
1134 msc_phub_util.log('delete from msc_res_plan_updates: '||sql%rowcount);
1135 commit;
1136 end close_data;
1137
1138 procedure save_changes(p_query_id number)
1139 is
1140 cursor c3 is
1141 select
1142 query_id,
1143 plan_id,
1144 transaction_id,
1145 sr_instance_id,
1146 organization_id,
1147 department_id,
1148 resource_id,
1149 shift_num,
1150 shift_date,
1151 from_time,
1152 to_time,
1153 from_time2,
1154 to_time2,
1155 parent_id,
1156 change_flag,
1157 capacity_units
1158 from msc_res_plan_updates
1159 where query_id = p_query_id
1160 and change_flag = 1
1161 for update nowait;
1162
1163 cursor c4(
1164 p_plan_id number,
1165 p_transaction_id number,
1166 p_sr_instance_id number)
1167 is
1168 select
1169 from_time,
1170 to_time,
1171 shift_num,
1172 capacity_units,
1173 last_update_date,
1174 last_updated_by,
1175 last_update_login
1176 from msc_net_resource_avail
1177 where plan_id = p_plan_id
1178 and transaction_id = p_transaction_id
1179 and sr_instance_id = p_sr_instance_id
1180 for update nowait;
1181
1182 cursor c5(
1183 p_plan_id number,
1184 p_parent_id number,
1185 p_sr_instance_id number)
1186 is
1187 select
1188 capacity_units,
1189 last_update_date,
1190 last_updated_by,
1191 last_update_login
1192 from msc_net_resource_avail
1193 where plan_id = p_plan_id
1194 and transaction_id = p_parent_id
1195 and parent_id = -1
1196 and sr_instance_id = p_sr_instance_id
1197 for update nowait;
1198
1199 l_n number;
1200 begin
1201 msc_phub_util.log('msc_res_plan_pkg.save_changes('||p_query_id||')');
1202
1203 for r3 in c3 loop
1204 msc_phub_util.log('r3.transaction_id='||r3.transaction_id||', r3.parent_id='||r3.parent_id);
1205
1206 if (r3.parent_id is null) then
1207 begin
1208 select transaction_id into r3.parent_id
1209 from msc_net_resource_avail
1210 where plan_id = r3.plan_id
1211 and sr_instance_id = r3.sr_instance_id
1212 and organization_id = r3.organization_id
1213 and department_id = r3.department_id
1214 and resource_id = r3.resource_id
1215 and shift_date = r3.shift_date
1216 and parent_id = -1;
1217
1218 exception
1219 when no_data_found then
1220 select msc_net_resource_avail_s.nextval into r3.parent_id from dual;
1221
1222 insert into msc_net_resource_avail (
1223 plan_id,
1224 transaction_id,
1225 parent_id,
1226 sr_instance_id,
1227 organization_id,
1228 department_id,
1229 resource_id,
1230 shift_date,
1231 capacity_units,
1232 created_by, creation_date,
1233 last_update_date, last_updated_by, last_update_login)
1234 values (
1235 r3.plan_id,
1236 r3.parent_id,
1237 -1,
1238 r3.sr_instance_id,
1239 r3.organization_id,
1240 r3.department_id,
1241 r3.resource_id,
1242 r3.shift_date,
1243 r3.capacity_units * decode(r3.from_time, null, 0,
1244 (decode(sign(r3.to_time-r3.from_time),
1245 -1, r3.to_time+86400, r3.to_time) - r3.from_time)/3600),
1246 fnd_global.user_id, sysdate,
1247 sysdate, fnd_global.user_id, fnd_global.login_id);
1248 msc_phub_util.log('insert into msc_net_resource_avail (summary): '||sql%rowcount||', '||r3.parent_id);
1249 end;
1250 end if;
1251
1252 if (r3.transaction_id = -1) then
1253 select msc_net_resource_avail_s.nextval into r3.transaction_id from dual;
1254
1255 insert into msc_net_resource_avail (
1256 plan_id,
1257 transaction_id,
1258 parent_id,
1259 sr_instance_id,
1260 organization_id,
1261 department_id,
1262 resource_id,
1263 shift_num,
1264 shift_date,
1265 from_time,
1266 to_time,
1267 capacity_units,
1268 status,
1269 applied,
1270 updated,
1271 created_by, creation_date,
1272 last_update_date, last_updated_by, last_update_login)
1273 values (
1274 r3.plan_id,
1275 r3.transaction_id,
1276 r3.parent_id,
1277 r3.sr_instance_id,
1278 r3.organization_id,
1279 r3.department_id,
1280 r3.resource_id,
1281 r3.shift_num,
1282 r3.shift_date,
1283 r3.from_time,
1284 r3.to_time,
1285 r3.capacity_units,
1286 0,
1287 2,
1288 2,
1289 fnd_global.user_id, sysdate,
1290 sysdate, fnd_global.user_id, fnd_global.login_id);
1291 msc_phub_util.log('insert into msc_net_resource_avail (detail): '||sql%rowcount||', '||r3.transaction_id);
1292
1293 update msc_res_plan_updates
1294 set transaction_id=r3.transaction_id
1295 where current of c3;
1296 msc_phub_util.log('update msc_res_plan_updates: '||sql%rowcount);
1297 else
1298 for r4 in c4(r3.plan_id, r3.transaction_id, r3.sr_instance_id) loop
1299 update msc_net_resource_avail
1300 set from_time = r3.from_time,
1301 to_time = r3.to_time,
1302 shift_num = r3.shift_num,
1303 capacity_units = r3.capacity_units,
1304 status = 0,
1305 applied = 2,
1306 updated = 2,
1307 last_update_date = sysdate,
1308 last_updated_by = fnd_global.user_id,
1309 last_update_login = fnd_global.login_id
1310 where current of c4;
1311 msc_phub_util.log('update msc_net_resource_avail (detail): '||sql%rowcount||', '||r3.transaction_id);
1312 exit;
1313 end loop;
1314 end if;
1315
1316 for r5 in c5(r3.plan_id, r3.parent_id, r3.sr_instance_id) loop
1317 update msc_net_resource_avail
1318 set capacity_units = r3.capacity_units * decode(r3.from_time, null, 0,
1319 (decode(sign(r3.to_time-r3.from_time),
1320 -1, r3.to_time+86400, r3.to_time) - r3.from_time)/3600)
1321 where current of c5;
1322 msc_phub_util.log('update msc_net_resource_avail (summary): '||sql%rowcount||', '||r3.parent_id);
1323 exit;
1324 end loop;
1325
1326 end loop;
1327 commit;
1328
1329 /*
1330 update msc_res_plan_updates
1331 set change_flag = 2
1332 where query_id = p_query_id
1333 and change_flag = 1;
1334 msc_phub_util.log('update msc_res_plan_updates: '||sql%rowcount);
1335 commit;
1336 */
1337
1338 delete from msc_res_plan_updates
1339 where query_id = p_query_id;
1340 msc_phub_util.log('delete from msc_res_plan_updates: '||sql%rowcount);
1341 commit;
1342
1343
1344 end save_changes;
1345
1346 end msc_res_plan_pkg;