[Home] [Help]
PACKAGE BODY: APPS.MSC_MATL_PLAN_PKG
Source
1 package body msc_matl_plan_pkg as
2 /* $Header: MSCPCMHB.pls 120.0.12020000.3 2013/04/30 16:53:52 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_category_set_id number,
11 p_organization_filters varchar2,
12 p_category_filters varchar2,
13 p_item_filters varchar2,
14 p_page_size number)
15 is
16 l_org_filter_cnt number;
17 l_cat_filter_cnt number;
18 l_item_filter_cnt number;
19 begin
20 msc_phub_util.log('msc_matl_plan_pkg.prepare_row_dtls('||p_query_id||', '||p_plan_id||
21 ','||p_category_set_id||','||p_page_size||')');
22
23 delete from msc_form_query where query_id = p_query_id;
24 msc_phub_util.log('delete from msc_form_query: '||sql%rowcount);
25 commit;
26
27 msc_hp_util.prepare_filters(p_query_id, msc_hp_util.ft_organization, p_organization_filters);
28 msc_hp_util.prepare_filters(p_query_id, msc_hp_util.ft_category, p_category_filters);
29 msc_hp_util.prepare_filters(p_query_id, msc_hp_util.ft_item, p_item_filters);
30
31 select count(*)
32 into l_org_filter_cnt
33 from msc_form_query
34 where query_id = p_query_id and number1 = msc_hp_util.ft_organization;
35
36 select count(*)
37 into l_cat_filter_cnt
38 from msc_form_query
39 where query_id = p_query_id and number1 = msc_hp_util.ft_category;
40
41 select count(*)
42 into l_item_filter_cnt
43 from msc_form_query
44 where query_id = p_query_id and number1 = msc_hp_util.ft_item;
45
46 msc_phub_util.log('l_org_filter_cnt='||l_org_filter_cnt||', '||
47 'l_cat_filter_cnt='||l_cat_filter_cnt||', '||
48 'l_item_filter_cnt='||l_item_filter_cnt);
49
50 delete from msc_hp_row_dtls where query_id = p_query_id;
51 msc_phub_util.log('delete from msc_hp_row_dtls: '||sql%rowcount);
52 commit;
53
54 insert into msc_hp_row_dtls(
55 query_id,
56 row_index,
57 plan_id,
58 sr_instance_id,
59 organization_id,
60 inventory_item_id,
61 base_item_id,
62 category_set_id,
63 sr_category_id,
64 organization_code,
65 category_name,
66 item_name,
67 data_flag,
68 created_by, creation_date,
69 last_update_date, last_updated_by, last_update_login)
70 select
71 p_query_id,
72 (rank() over(order by o.organization_code, ic.category_name, i.item_name))-1 row_index,
73 i.plan_id,
74 i.sr_instance_id,
75 i.organization_id,
76 i.inventory_item_id,
77 i.base_item_id,
78 p_category_set_id,
79 ic.sr_category_id,
80 o.organization_code,
81 ic.category_name,
82 i.item_name,
83 1 data_flag,
84 fnd_global.user_id, sysdate,
85 sysdate, fnd_global.user_id, fnd_global.login_id
86 from
87 msc_system_items i,
88 msc_item_categories ic,
89 msc_trading_partners o
90 where i.plan_id = p_plan_id
91 and i.sr_instance_id = o.sr_instance_id
92 and i.organization_id = o.sr_tp_id
93 and o.partner_type = 3
94 and i.sr_instance_id = ic.sr_instance_id(+)
95 and i.organization_id = ic.organization_id(+)
96 and i.inventory_item_id = ic.inventory_item_id(+)
97 and ic.category_set_id(+) = p_category_set_id
98 and (l_org_filter_cnt = 0 or o.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_cat_filter_cnt = 0 or ic.category_name in (select char1 from msc_form_query where query_id = p_query_id and number1 = msc_hp_util.ft_category))
100 and (l_item_filter_cnt = 0 or i.inventory_item_id in (select number2 from msc_form_query where query_id = p_query_id and number1 = msc_hp_util.ft_item));
101 msc_phub_util.log('insert into msc_hp_row_dtls: '||sql%rowcount);
102 commit;
103
104 insert into msc_hp_row_dtls(
105 query_id,
106 row_index,
107 plan_id,
108 data_flag,
109 created_by, creation_date,
110 last_update_date, last_updated_by, last_update_login)
111 values (p_query_id, -1, p_plan_id, 1,
112 fnd_global.user_id, sysdate,
113 sysdate, fnd_global.user_id, fnd_global.login_id);
114 msc_phub_util.log('insert into msc_hp_row_dtls: '||sql%rowcount);
115 commit;
116
117 g_page_size := p_page_size;
118 g_fetch_size := ceil(g_max_fetch_size/g_page_size) * g_page_size;
119 msc_phub_util.log('g_max_fetch_size = '||g_max_fetch_size||', '||
120 'g_page_size = '||g_page_size||', '||
121 'g_fetch_size = '||g_fetch_size);
122
123 end prepare_row_dtls;
124
125 procedure populate_data(p_query_id number, p_summary number)
126 is
127 l_plan_id number;
128 l_past_date date;
129 l_pre_start_date date;
130 l_plan_cutoff_date date;
131 begin
132 msc_phub_util.log('msc_matl_plan_pkg.populate_data('||p_query_id||', '||p_summary||')');
133
134 begin
135 select h.plan_id, b.bkt_start_date
136 into l_plan_id, l_past_date
137 from msc_hp_row_dtls h, msc_hp_col_dtls b
138 where h.query_id = p_query_id
139 and h.data_flag = decode(p_summary, 1, h.data_flag, 3)
140 and h.plan_id = b.plan_id
141 and b.bucket_type = 0
142 and rownum = 1;
143 msc_phub_util.log('l_plan_id='||l_plan_id||', l_past_date='||l_past_date);
144
145 select min(b.bkt_start_date), max(b.bkt_end_date)
146 into l_pre_start_date, l_plan_cutoff_date
147 from msc_hp_row_dtls h, msc_hp_col_dtls b
148 where h.query_id = p_query_id
149 and h.plan_id = b.plan_id;
150 msc_phub_util.log('l_pre_start_date='||l_pre_start_date||
151 ', l_plan_cutoff_date='||l_plan_cutoff_date);
152 exception
153 when no_data_found then return;
154 end;
155
156 delete from msc_matl_plan_data
157 where (query_id, row_index) in
158 (select
159 h.query_id,
160 decode(p_summary, 1, -1, h.row_index) row_index
161 from msc_hp_row_dtls h
162 where h.query_id = p_query_id
163 and h.data_flag = decode(p_summary, 1, h.data_flag, 3));
164 msc_phub_util.log('delete from msc_matl_plan_data: '||sql%rowcount);
165 commit;
166
167 insert into msc_matl_plan_data(
168 query_id,
169 row_index,
170 analysis_date,
171 total_supply,
172 on_hand,
173 scheduled_receipts,
174 planned_order,
175 purchase_order,
176 requisition,
177 work_order,
178 total_demand,
179 forecast,
180 forecast_mds,
181 dependent_demand,
182 manual_demand,
183 sales_order,
184 safety_stock,
185 created_by, creation_date,
186 last_update_date, last_updated_by, last_update_login)
187 select
188 p_query_id,
189 row_index,
190 analysis_date,
191 sum(nvl(on_hand,0) + nvl(planned_order,0) + nvl(other_supply,0)) total_supply,
192 sum(on_hand) on_hand,
193 sum(scheduled_receipts) scheduled_receipts,
194 sum(planned_order) planned_order,
195 sum(purchase_order) purchase_order,
196 sum(requisition) requisition,
197 sum(work_order) work_order,
198 sum(nvl(forecast,0) + nvl(forecast_mds,0) + nvl(dependent_demand,0) + nvl(manual_demand,0) +
199 nvl(sales_order,0) + nvl(other_demand,0)) total_demand,
200 sum(forecast) forecast,
201 sum(forecast_mds) forecast_mds,
202 sum(dependent_demand) dependent_demand,
203 sum(manual_demand) manual_demand,
204 sum(sales_order) sales_order,
205 sum(safety_stock) safety_stock,
206 fnd_global.user_id, sysdate,
207 sysdate, fnd_global.user_id, fnd_global.login_id
208 from
209 (
210 -- supplies:scheduled_receipts
211 select
212 decode(p_summary, 1, -1, h.row_index) row_index,
213 b.bkt_start_date analysis_date,
214
215 to_number(null) on_hand,
216
217 sum(decode(ms.last_unit_completion_date, null,
218 nvl(ms.firm_quantity, ms.new_order_quantity), ms.daily_rate))
219 scheduled_receipts,
220
221 to_number(null) planned_order,
222 to_number(null) purchase_order,
223 to_number(null) requisition,
224 to_number(null) work_order,
225 to_number(null) other_supply,
226
227 to_number(null) forecast,
228 to_number(null) forecast_mds,
229 to_number(null) dependent_demand,
230 to_number(null) manual_demand,
231 to_number(null) sales_order,
232 to_number(null) other_demand,
233 to_number(null) safety_stock
234 from
235 msc_supplies ms,
236 msc_hp_col_dtls b,
237 msc_hp_row_dtls h
238 where h.query_id = p_query_id
239 and ms.plan_id = h.plan_id
240 and ms.sr_instance_id = h.sr_instance_id
241 and ms.organization_id = h.organization_id
242 and ms.inventory_item_id = h.inventory_item_id
243 and h.data_flag = decode(p_summary, 1, h.data_flag, 3)
244 and (h.base_item_id is not null or nvl(ms.disposition_status_type, 1) <> 2)
245 and ms.plan_id = b.plan_id
246 --and b.curr_flag = 1
247 and greatest(l_pre_start_date,
248 trunc(nvl(ms.firm_date, nvl(ms.old_schedule_date, ms.new_schedule_date))))
249 between b.bkt_start_date and b.bkt_end_date
250 and ms.order_type in (1,2,3,8,11,12,14,27,49,53,80)
251 group by
252 decode(p_summary, 1, -1, h.row_index),
253 b.bkt_start_date
254 union all
255 -- supplies: new_schedule_date
256 select
257 decode(p_summary, 1, -1, h.row_index) row_index,
258 b.bkt_start_date analysis_date,
259
260 sum(case when nvl(ms.item_type_value,1) = 1 and ms.order_type in (18) then
261 decode(ms.last_unit_completion_date, null,
262 nvl(ms.firm_quantity, ms.new_order_quantity), ms.daily_rate)
263 end) on_hand, -- apcc does not check part condistion
264
265 to_number(null) scheduled_receipts,
266
267 sum(case when ms.order_type in (5) then
268 decode(ms.last_unit_completion_date, null,
269 nvl(ms.firm_quantity, ms.new_order_quantity), ms.daily_rate)
270 end) planned_order,
271
272 sum(case when ms.order_type in (1) then
273 decode(ms.last_unit_completion_date, null,
274 nvl(ms.firm_quantity, ms.new_order_quantity), ms.daily_rate)
275 end) purchase_order,
276
277 sum(case when ms.order_type in (2) then
278 decode(ms.last_unit_completion_date, null,
279 nvl(ms.firm_quantity, ms.new_order_quantity), ms.daily_rate)
280 end) requisition,
281
282 sum(case when ms.order_type in (3) then
283 decode(ms.last_unit_completion_date, null,
284 nvl(ms.firm_quantity, ms.new_order_quantity), ms.daily_rate)
285 end) work_order,
286
287 sum(case when ms.order_type in (1,2,3,8,11,12,14,27,49,53,80) then
288 decode(ms.last_unit_completion_date, null,
289 nvl(ms.firm_quantity, ms.new_order_quantity), ms.daily_rate)
290 end) other_supply,
291
292 to_number(null) forecast,
293 to_number(null) forecast_mds,
294 to_number(null) dependent_demand,
295 to_number(null) manual_demand,
296 to_number(null) sales_order,
297 to_number(null) other_demand,
298 to_number(null) safety_stock
299 from
300 msc_supplies ms,
301 msc_hp_col_dtls b,
302 msc_hp_row_dtls h
303 where h.query_id = p_query_id
304 and ms.plan_id = h.plan_id
305 and ms.sr_instance_id = h.sr_instance_id
306 and ms.organization_id = h.organization_id
307 and ms.inventory_item_id = h.inventory_item_id
308 and h.data_flag = decode(p_summary, 1, h.data_flag, 3)
309 and (h.base_item_id is not null or nvl(ms.disposition_status_type, 1) <> 2)
310 and ms.plan_id = b.plan_id
311 --and b.curr_flag = 1
312 and trunc(nvl(ms.firm_date, ms.new_schedule_date)) between b.bkt_start_date and b.bkt_end_date
313 group by
314 decode(p_summary, 1, -1, h.row_index),
315 b.bkt_start_date
316 union all
317 -- demands
318 select
319 decode(p_summary, 1, -1, h.row_index) row_index,
320 b.bkt_start_date analysis_date,
321 to_number(null) on_hand,
322 to_number(null) scheduled_receipts,
323 to_number(null) planned_order,
324 to_number(null) purchase_order,
325 to_number(null) requisition,
326 to_number(null) work_order,
327 to_number(null) other_supply,
328
329 sum(case when origination_type in (29) then
330 decode(md.assembly_demand_comp_date, null,
331 nvl(md.probability,1) * nvl(md.firm_quantity, md.using_requirement_quantity),
332 nvl(md.probability,1) * md.daily_demand_rate) end)
333 / decode(nvl(least(sum(nvl(md.probability,0)), 1), 1),
334 0, 1, nvl(least(sum(nvl(md.probability,0)), 1), 1))
335 forecast,
336
337 sum(case when origination_type in (7) then
338 decode(md.assembly_demand_comp_date, null,
339 nvl(md.probability,1) * nvl(md.firm_quantity, md.using_requirement_quantity),
340 nvl(md.probability,1) * md.daily_demand_rate) end)
341 / decode(nvl(least(sum(nvl(md.probability,0)), 1), 1),
342 0, 1, nvl(least(sum(nvl(md.probability,0)), 1), 1))
343 forecast_mds,
344
345 sum(case when origination_type in (1,2,3,4,24,25) then
346 decode(md.assembly_demand_comp_date, null,
347 nvl(md.firm_quantity, md.using_requirement_quantity), md.daily_demand_rate)
348 end) dependent_demand,
349
350 sum(case when origination_type in (8) then
351 decode(md.assembly_demand_comp_date, null,
352 nvl(md.firm_quantity, md.using_requirement_quantity), md.daily_demand_rate)
353 end) manual_demand,
354
355 sum(case when origination_type in (6,30) then
356 decode(md.assembly_demand_comp_date, null,
357 nvl(md.firm_quantity, md.using_requirement_quantity), md.daily_demand_rate)
358 end) sales_order,
359
360 sum(case when origination_type not in (1,2,3,4,6,7,8,24,25,29,30) then
361 decode(md.assembly_demand_comp_date, null,
365 to_number(null) safety_stock
362 nvl(md.firm_quantity, md.using_requirement_quantity), md.daily_demand_rate)
363 end) other_demand,
364
366 from
367 msc_demands md,
368 msc_hp_col_dtls b,
369 msc_hp_row_dtls h
370 where h.query_id = p_query_id
371 and md.plan_id = h.plan_id
372 and md.sr_instance_id = h.sr_instance_id
373 and md.organization_id = h.organization_id
374 and md.inventory_item_id = h.inventory_item_id
375 and h.data_flag = decode(p_summary, 1, h.data_flag, 3)
376 and md.plan_id = b.plan_id
377 --and b.curr_flag = 1
378 and trunc(nvl(md.firm_date, md.using_assembly_demand_date)) between b.bkt_start_date and b.bkt_end_date
379 group by
380 decode(p_summary, 1, -1, h.row_index),
381 b.bkt_start_date
382 union all
383 -- safety_stock
384 select
385 f.row_index,
386 min(b.bkt_start_date) analysis_date,
387 to_number(null) on_hand,
388 to_number(null) scheduled_receipts,
389 to_number(null) planned_order,
390 to_number(null) purchase_order,
391 to_number(null) requisition,
392 to_number(null) work_order,
393 to_number(null) other_supply,
394 to_number(null) forecast,
395 to_number(null) forecast_mds,
396 to_number(null) dependent_demand,
397 to_number(null) manual_demand,
398 to_number(null) sales_order,
399 to_number(null) other_demand,
400 f.safety_stock_quantity
401 from
402 (select
403 row_index,
404 plan_id,
405 period_start_date effective_date,
406 nvl(lead(period_start_date-1) over(partition by row_index order by period_start_date), l_plan_cutoff_date) expiry_date,
407 safety_stock_quantity
408 from
409 (select
410 decode(p_summary, 1, -1, h.row_index) row_index,
411 ss.plan_id,
412 ss.period_start_date,
413 sum(ss.safety_stock_quantity) safety_stock_quantity
414 from
415 msc_safety_stocks ss,
416 msc_hp_row_dtls h
417 where h.query_id = p_query_id
418 and ss.plan_id = h.plan_id
419 and ss.sr_instance_id = h.sr_instance_id
420 and ss.organization_id = h.organization_id
421 and ss.inventory_item_id = h.inventory_item_id
422 and h.data_flag = decode(p_summary, 1, h.data_flag, 3)
423 group by
424 decode(p_summary, 1, -1, h.row_index),
425 ss.plan_id,
426 ss.period_start_date
427 )
428 ) f,
429 msc_hp_col_dtls b
430 where f.plan_id = b.plan_id
431 and b.bkt_start_date between f.effective_date and f.expiry_date
432 group by
433 f.row_index,
434 f.effective_date,
435 f.expiry_date,
436 f.safety_stock_quantity
437 )
438 group by
439 row_index,
440 analysis_date;
441 msc_phub_util.log('insert into msc_matl_plan_data: '||sql%rowcount);
442 commit;
443
444 update msc_hp_row_dtls
445 set data_flag = 2
446 where query_id = p_query_id and data_flag = 3;
447 msc_phub_util.log('update msc_hp_row_dtls: '||sql%rowcount);
448 commit;
449
450 end populate_data;
451
452 procedure summarize_data(p_query_id number)
453 is
454 begin
455 msc_phub_util.log('msc_matl_plan_pkg.summarize_data('||p_query_id||')');
456
457 update msc_hp_row_dtls
458 set data_flag = 3
459 where query_id = p_query_id
460 and row_index = -1
461 and data_flag = 1;
462 commit;
463
464 populate_data(p_query_id, 1);
465 end summarize_data;
466
467 procedure initialize_data(
468 p_query_id number,
469 p_plan_id number,
470 p_category_set_id number,
471 p_organization_filters varchar2,
472 p_category_filters varchar2,
473 p_item_filters varchar2,
474 p_page_size number)
475 is
476 begin
477 msc_phub_util.log('msc_matl_plan_pkg.initialize_data('||p_query_id||', '||p_plan_id||
478 ','||p_category_set_id||','||p_page_size||')');
479
480 msc_hp_util.prepare_col_dtls(p_plan_id, 2);
481
482 prepare_row_dtls(p_query_id, p_plan_id, p_category_set_id,
483 p_organization_filters, p_category_filters, p_item_filters, p_page_size);
484
485 delete from msc_matl_plan_data where query_id = p_query_id;
486 commit;
487
488 fetch_data(p_query_id, 0);
489 end initialize_data;
490
491
492 procedure fetch_data(p_query_id number, p_row_index number)
493 is
494 l_n number;
495 begin
496 msc_phub_util.log('msc_matl_plan_pkg.fetch_data('||p_query_id||', '||p_row_index||')');
497
498 select count(*) into l_n
499 from msc_hp_row_dtls
503 if (l_n = 0) then
500 where query_id = p_query_id
501 and row_index between p_row_index and p_row_index+g_page_size-1
502 and data_flag = 1;
504 return;
505 end if;
506
507 update msc_hp_row_dtls
508 set data_flag = 3
509 where query_id = p_query_id
510 and row_index between p_row_index and p_row_index+g_page_size-1
511 and data_flag = 1;
512 commit;
513
514 populate_data(p_query_id, 2);
515 end fetch_data;
516
517 procedure save_changes(p_query_id number)
518 is
519 cursor cu is
520 select
521 h.row_index,
522 h.plan_id,
523 h.sr_instance_id,
524 h.organization_id,
525 h.inventory_item_id,
526 decode(u.column_name, 'planned_order', 5) supply_type,
527 decode(u.column_name, 'forecast', 29, 'forecast_mds', 7, 'manual_demand', 8) demand_type,
528 u.analysis_date,
529 u.time_level,
530 decode(u.time_level, 2, b.week_start_date, 3, period_start_date, bkt_start_date) start_date,
531 decode(u.time_level, 2, b.week_end_date, 3, period_end_date, bkt_end_date) end_date,
532 decode(u.time_level, 2, b.week_last_work_date, 3, period_last_work_date, bkt_end_date) last_work_date,
533 b.bucket_type,
534 u.column_name,
535 u.new_value
536 from
537 msc_hp_updates u,
538 msc_hp_row_dtls h,
539 msc_hp_col_dtls b
540 where h.query_id = p_query_id
541 and h.query_id = u.query_id
542 and h.row_index = u.row_index
543 and h.plan_id = b.plan_id
544 and u.analysis_date = b.bkt_start_date
545 and u.hp_type_code = 'MSC_ASCP_MATL_PLAN_TYPE'
546 and u.new_value >= 0
547 and nvl(u.process_status, 1) = 1;
548
549 cursor c1(
550 p_plan_id number,
551 p_sr_instance_id number,
552 p_organization_id number,
553 p_inventory_item_id number,
554 p_start_date date,
555 p_end_date date,
556 p_supply_type number)
557 is
558 select
559 firm_planned_type,
560 firm_date,
561 firm_quantity,
562 status,
563 applied,
564 last_update_date,
565 last_updated_by,
566 last_update_login
567 from msc_supplies
568 where plan_id = p_plan_id
569 and sr_instance_id = p_sr_instance_id
570 and organization_id = p_organization_id
571 and inventory_item_id = p_inventory_item_id
572 and trunc(nvl(firm_date, new_schedule_date)) between p_start_date and p_end_date
573 and order_type = p_supply_type
574 and last_unit_completion_date is null
575 for update nowait;
576
577 cursor c2(
578 p_plan_id number,
579 p_sr_instance_id number,
580 p_organization_id number,
581 p_inventory_item_id number,
582 p_start_date date,
583 p_end_date date,
584 p_demand_type number)
585 is
586 select
587 firm_date,
588 firm_quantity,
589 status,
590 applied,
591 last_update_date,
592 last_updated_by,
593 last_update_login
594 from msc_demands
595 where plan_id = p_plan_id
596 and sr_instance_id = p_sr_instance_id
597 and organization_id = p_organization_id
598 and inventory_item_id = p_inventory_item_id
599 and trunc(nvl(firm_date, using_assembly_demand_date)) between p_start_date and p_end_date
600 and origination_type = p_demand_type
601 and assembly_demand_comp_date is null
602 for update nowait;
603
604 l_n number;
605 l_old_value number;
606 l_fixed number;
607 l_cnt number;
608 begin
609 msc_phub_util.log('msc_matl_plan_pkg.save_changes('||p_query_id||')');
610
611 for ru in cu loop
612 if (ru.supply_type is not null) then
613 /*
614 msc_phub_util.log(' where plan_id='||ru.plan_id||
615 ' and sr_instance_id='||ru.sr_instance_id||
616 ' and organization_id='||ru.organization_id||
617 ' and inventory_item_id='||ru.inventory_item_id||
618 ' and trunc(nvl(firm_date, new_schedule_date)) between to_date('''||ru.start_date||''') and to_date('''||ru.end_date||''')'||
619 ' and order_type='||ru.supply_type||
620 ' and last_unit_completion_date is null;');
621 */
622
623 select
624 nvl(sum(decode(last_unit_completion_date, null,
625 nvl(firm_quantity, new_order_quantity), null)), 0) old_value,
626 nvl(sum(decode(last_unit_completion_date, null, null, daily_rate)), 0) fixed,
627 nvl(sum(decode(last_unit_completion_date, null, 1, 0)), 0) cnt
628 into l_old_value, l_fixed, l_cnt
629 from msc_supplies
630 where plan_id = ru.plan_id
631 and sr_instance_id = ru.sr_instance_id
632 and organization_id = ru.organization_id
636
633 and inventory_item_id = ru.inventory_item_id
634 and trunc(nvl(firm_date, new_schedule_date)) between ru.start_date and ru.end_date
635 and order_type = ru.supply_type;
637 msc_phub_util.log('l_old_value='||l_old_value||',l_fixed='||l_fixed||',l_cnt='||l_cnt);
638
639 l_n := 0;
640 for r1 in c1(
641 ru.plan_id,
642 ru.sr_instance_id,
643 ru.organization_id,
644 ru.inventory_item_id,
645 ru.start_date,
646 ru.end_date,
647 ru.supply_type)
648 loop
649 update msc_supplies set
650 firm_planned_type = 1,
651 firm_date = nvl(firm_date, new_schedule_date),
652 firm_quantity = decode(l_old_value, 0, (ru.new_value-l_fixed)/l_cnt,
653 nvl(firm_quantity, new_order_quantity)*(ru.new_value-l_fixed)/l_old_value),
654 status = 0,
655 applied = 2,
656 last_update_date = sysdate,
657 last_updated_by = fnd_global.user_id,
658 last_update_login = fnd_global.login_id
659 where current of c1;
660 l_n := l_n + 1;
661 end loop;
662
663 if (l_n > 0) then
664 msc_phub_util.log('update msc_supplies: '||l_n);
665 else
666 insert into msc_supplies(
667 plan_id,
668 transaction_id,
669 sr_instance_id,
670 organization_id,
671 inventory_item_id,
672 order_type,
673 new_schedule_date,
674 firm_planned_type,
675 firm_date,
676 firm_quantity,
677 status,
678 applied,
679 created_by, creation_date,
680 last_update_date, last_updated_by, last_update_login)
681 values(
682 ru.plan_id,
683 msc_supplies_s.nextval,
684 ru.sr_instance_id,
685 ru.organization_id,
686 ru.inventory_item_id,
687 ru.supply_type,
688 ru.last_work_date,
689 1,
690 ru.last_work_date,
691 (ru.new_value-l_fixed),
692 0,
693 2,
694 fnd_global.user_id, sysdate,
695 sysdate, fnd_global.user_id, fnd_global.login_id);
696 msc_phub_util.log('insert into msc_supplies: '||sql%rowcount);
697 end if;
698 commit;
699 end if;
700
701 if (ru.demand_type is not null) then
702 /*
703 msc_phub_util.log(' where plan_id='||ru.plan_id||
704 ' and sr_instance_id='||ru.sr_instance_id||
705 ' and organization_id='||ru.organization_id||
706 ' and inventory_item_id='||ru.inventory_item_id||
707 ' and trunc(nvl(firm_date, using_assembly_demand_date)) between to_date('''||ru.start_date||''') and to_date('''||ru.end_date||''')'||
708 ' and origination_type='||ru.demand_type||
709 ' and assembly_demand_comp_date is null;');
710 */
711
712 select
713 nvl(sum(decode(assembly_demand_comp_date, null,
714 nvl(firm_quantity, using_requirement_quantity), null)), 0) old_value,
715 nvl(sum(decode(assembly_demand_comp_date, null, null, daily_demand_rate)), 0) fixed,
716 nvl(sum(decode(assembly_demand_comp_date, null, 1, 0)), 0) cnt
717 into l_old_value, l_fixed, l_cnt
718 from msc_demands
719 where plan_id = ru.plan_id
720 and sr_instance_id = ru.sr_instance_id
721 and organization_id = ru.organization_id
722 and inventory_item_id = ru.inventory_item_id
723 and trunc(nvl(firm_date, using_assembly_demand_date)) between ru.start_date and ru.end_date
724 and origination_type = ru.demand_type;
725
726 msc_phub_util.log('l_old_value='||l_old_value||',l_fixed='||l_fixed||',l_cnt='||l_cnt);
727
728 l_n := 0;
729 for r2 in c2(
730 ru.plan_id,
731 ru.sr_instance_id,
732 ru.organization_id,
733 ru.inventory_item_id,
734 ru.start_date,
735 ru.end_date,
736 ru.demand_type)
737 loop
738 update msc_demands set
739 firm_date = nvl(firm_date, using_assembly_demand_date),
740 firm_quantity = decode(l_old_value, 0, (ru.new_value-l_fixed)/l_cnt,
741 nvl(firm_quantity, using_requirement_quantity)*(ru.new_value-l_fixed)/l_old_value),
742 org_firm_flag = 1,
743 status = 0,
744 applied = 2,
745 last_update_date = sysdate,
746 last_updated_by = fnd_global.user_id,
747 last_update_login = fnd_global.login_id
748 where current of c2;
749 l_n := l_n + 1;
750 end loop;
751
752 if (l_n > 0) then
753 msc_phub_util.log('update msc_demands: '||l_n);
754 else
755 insert into msc_demands(
756 plan_id,
757 demand_id,
758 sr_instance_id,
759 organization_id,
760 inventory_item_id,
761 origination_type,
762 using_requirement_quantity,
763 demand_type,
764 using_assembly_item_id,
765 using_assembly_demand_date,
766 firm_date,
767 firm_quantity,
768 demand_priority,
769 org_firm_flag,
770 status,
771 applied,
772 created_by, creation_date,
773 last_update_date, last_updated_by, last_update_login)
774 values(
775 ru.plan_id,
776 msc_demands_s.nextval,
777 ru.sr_instance_id,
778 ru.organization_id,
779 ru.inventory_item_id,
780 ru.demand_type,
781 0,
782 1,
783 ru.inventory_item_id,
784 ru.last_work_date,
785 ru.last_work_date,
786 (ru.new_value-l_fixed),
787 1,
788 1,
789 0,
790 2,
791 fnd_global.user_id, sysdate,
792 sysdate, fnd_global.user_id, fnd_global.login_id);
793 msc_phub_util.log('insert into msc_demands: '||sql%rowcount);
794 end if;
795 commit;
796 end if;
797 end loop;
798
799 update msc_hp_row_dtls
800 set data_flag = 3
801 where query_id = p_query_id
802 and row_index in (select row_index from msc_hp_updates where query_id = p_query_id);
803 commit;
804
805 populate_data(p_query_id, 2);
806
807 update msc_hp_updates set
808 process_status = 2,
809 last_update_date = sysdate,
810 last_updated_by = fnd_global.user_id,
811 last_update_login = fnd_global.login_id
812 where query_id = p_query_id and nvl(process_status, 1) = 1;
813 msc_phub_util.log('update msc_hp_updates: '||sql%rowcount);
814 commit;
815
816 end save_changes;
817
818 procedure close_data(p_query_id number)
819 is
820 begin
821 msc_phub_util.log('msc_matl_plan_pkg.close_data('||p_query_id||')');
822
823 delete from msc_hp_row_dtls where query_id = p_query_id;
824 msc_phub_util.log('delete from msc_hp_row_dtls: '||sql%rowcount);
825 commit;
826
827 delete from msc_matl_plan_data where query_id = p_query_id;
828 msc_phub_util.log('delete from msc_matl_plan_data: '||sql%rowcount);
829 commit;
830
831 delete from msc_hp_updates where query_id = p_query_id;
832 msc_phub_util.log('delete from msc_hp_updates: '||sql%rowcount);
833 commit;
834
835 end close_data;
836
837 end msc_matl_plan_pkg;