[Home] [Help]
PACKAGE BODY: APPS.MSC_SNO_PKG
Source
1 package body msc_sno_pkg as
2 /* $Header: MSCHBSNB.pls 120.7 2008/01/17 22:12:58 wexia noship $ */
3
4 procedure populate_details(errbuf out nocopy varchar2, retcode out nocopy varchar2,
5 p_plan_id number, p_plan_run_id number)
6 is
7 l_api_name varchar2(100) := 'msc_sno_pkg.populate_details';
8 l_appl_id number := fnd_global.prog_appl_id;
9 l_cp_login_id number := fnd_global.conc_login_id;
10 l_program_id number := fnd_global.conc_program_id;
11 l_request_id number := fnd_global.conc_request_id;
12 l_sysdate date := sysdate;
13 l_user_id number := fnd_global.user_id;
14 l_user_login_id number := fnd_global.login_id;
15
16 l_qid_vmi_item number := 0;
17 l_plan_constrained number := 0;
18 l_plan_type number := msc_phub_util.get_plan_type(p_plan_id);
19
20 planned_order constant integer := 5;
21 purchase_order constant integer := 1;
22 purchase_req constant integer := 2;
23 planned_arrival constant integer := 51;
24 new_buy_pos constant integer := 76;
25 begin
26 --dbms_output.put_line('wei: msc_sno_pkg.populate_details('||p_plan_id||', '||p_plan_run_id||')');
27 retcode := 0;
28 errbuf := '';
29
30 /*
31 process in following order
32 exceptions
33 resources
34 suppliers
35 supplies
36 demands
37 items
38 */
39
40 /*
41 msc_exceptions_f: Union block into msc_exception_pkg, filter out SNO from existing code.
42 */
43 -- msc_exceptions_f: tuned to change in existing codes
44 --dbms_output.put_line('wei: msc_exceptions_f');
45 insert into msc_exceptions_f (
46 plan_id,
47 plan_run_id,
48 organization_id,
49 sr_instance_id,
50 inventory_item_id,
51 department_id,
52 resource_id,
53 supplier_id,
54 supplier_site_id,
55 customer_id,
56 customer_site_id,
57 project_id,
58 task_id,
59 analysis_date,
60 exception_type,
61 exception_count,
62 exception_value,
63 exception_value2,
64 exception_days,
65 exception_quantity,
66 exception_ratio,
67 created_by,
68 creation_date,
69 last_update_date,
70 last_updated_by,
71 last_update_login,
72 program_id,
73 program_login_id,
74 program_application_id,
75 request_id)
76 select
77 exception_tbl.plan_id,
78 p_plan_run_id,
79 exception_tbl.organization_id,
80 exception_tbl.sr_instance_id,
81 nvl(exception_tbl.inventory_item_id, -23453),
82 exception_tbl.department_id,
83 exception_tbl.resource_id,
84 nvl(exception_tbl.supplier_id, -23453),
85 nvl(exception_tbl.supplier_site_id, -23453),
86 nvl(exception_tbl.customer_id, -23453),
87 nvl(exception_tbl.customer_site_id, -23453),
88 exception_tbl.project_id,
89 exception_tbl.task_id,
90 exception_tbl.analysis_date,
91 exception_tbl.exception_type,
92 exception_tbl.exception_count,
93 exception_tbl.exception_value,
94 exception_tbl.exception_value * decode(exception_tbl.currency_code, fnd_profile.value('MSC_HUB_CUR_CODE_RPT'),1, nvl(mcc.CONV_RATE,0)) exception_value2,
95 exception_tbl.exception_days,
96 exception_tbl.exception_quantity,
97 exception_tbl.exception_ratio,
98 fnd_global.user_id,
99 sysdate,
100 sysdate,
101 fnd_global.user_id,
102 fnd_global.login_id,
103 fnd_global.conc_program_id,
104 fnd_global.conc_login_id,
105 fnd_global.prog_appl_id,
106 fnd_global.conc_request_id
107 from
108 (
109 select
110 t.plan_id,
111 decode(t.organization_id, -1, -23453, t.organization_id) organization_id,
112 decode(t.organization_id, -1, -23453, t.sr_instance_id) sr_instance_id, -- wei: sync sr_instance_id with organization_id
113 decode(t.inventory_item_id, -1, -23453, t.inventory_item_id) inventory_item_id,
114 decode(t.department_id, -1, -23453, t.department_id) department_id,
115 decode(t.resource_id, -1, -23453, t.resource_id) resource_id,
116 decode(t.supplier_id, -1, -23453, t.supplier_id) supplier_id,
117 decode(t.supplier_site_id, -1, -23453, t.supplier_site_id) supplier_site_id,
118 decode(t.customer_id, -1, -23453, t.customer_id) customer_id,
119 decode(t.customer_site_id, -1, -23453, t.customer_site_id) customer_site_id,
120 -23453 project_id, -- SNO does not write project_id, task_id
121 -23453 task_id,
122 mtp.currency_code currency_code,
123 t.date1 analysis_date,
124 t.exception_type, -- wei: SNO same
125 count(*) exception_count, -- wei: SNO same
126 sum(decode(t.exception_type,
127 150, abs(t.quantity) *nvl(msi.list_price,0) * (1-(nvl(msi.average_discount,0)/100)),
128 151, t.quantity *nvl(msi.list_price,0) * (1-(nvl(msi.average_discount,0)/100)),
129 152, t.quantity *nvl(msi.list_price,0) * (1-(nvl(msi.average_discount,0)/100)),
130 160, abs(t.quantity) *msi.standard_cost,
131 161, t.quantity *msi.standard_cost,
132 162, t.quantity *msi.standard_cost,
133 190, abs(t.quantity) *msi.standard_cost,
134 191, t.quantity *msi.standard_cost,
135 to_number(null)) )exception_value, -- wei: SNO
136 to_number(null) exception_days, -- wei: SNO always null
137 sum(decode( t.exception_type,
138 150, abs(t.quantity),
139 151, t.quantity,
140 152, t.quantity,
141 160, abs(t.quantity),
142 161, t.quantity,
143 162, t.quantity,
144 170, abs(t.quantity),
145 171, t.quantity,
146 172, abs(t.quantity),
147 173, t.quantity,
148 180, abs(t.quantity),
149 181, t.quantity,
150 190, abs(t.quantity),
151 191, t.quantity,
152 200, abs(t.quantity),
153 201, t.quantity,
154 to_number(null))) exception_quantity, -- wei: SNO
155 avg(t.number2) exception_ratio --wei: SNO
156 from
157 (select
158 med.plan_id,
159 med.organization_id,
160 med.sr_instance_id,
161 med.inventory_item_id,
162 med.department_id,
163 med.resource_id,
164 med.supplier_id,
165 med.supplier_site_id,
166 med.customer_id,
167 med.customer_site_id,
168 med.exception_type,
169 med.quantity,
170 med.date1,
171 med.number2,
172 decode(med.organization_id, -1, mp.organization_id, med.organization_id) eff_organization_id,
173 decode(med.organization_id, -1, mp.sr_instance_id, med.sr_instance_id) eff_sr_instance_id -- wei: sync sr_instance_id with organization_id
174 from
175 msc_exception_details med,
176 msc_plans mp
177 where mp.plan_id = med.plan_id
178 and mp.plan_type = 6
179 and mp.plan_id = p_plan_id) t,
180 msc_system_items msi,
181 msc_trading_partners mtp
182 where msi.plan_id(+) = t.plan_id
183 and msi.inventory_item_id(+) = t.inventory_item_id
184 and msi.organization_id(+) = t.eff_organization_id
185 and msi.sr_instance_id(+) = t.eff_sr_instance_id
186 and mtp.sr_instance_id(+) = t.sr_instance_id
187 and mtp.sr_tp_id(+) = t.organization_id
188 and mtp.partner_type(+) = 3
189 group by
190 t.plan_id,
191 t.organization_id,
192 t.sr_instance_id,
193 decode(t.inventory_item_id, -1, -23453, t.inventory_item_id),
194 decode(t.department_id, -1, -23453, t.department_id),
195 decode(t.resource_id, -1, -23453, t.resource_id),
196 decode(t.supplier_id, -1, -23453, t.supplier_id),
197 decode(t.supplier_site_id, -1, -23453, t.supplier_site_id),
198 decode(t.customer_id, -1, -23453, t.customer_id),
199 decode(t.customer_site_id, -1, -23453, t.customer_site_id),
200 mtp.currency_code,
201 t.date1,
202 t.exception_type
203 ) exception_tbl,
204 msc_currency_conv_mv mcc
205 where mcc.FROM_CURRENCY(+) = nvl(exception_tbl.currency_code, 'XXX')
206 and mcc.TO_CURRENCY(+) = fnd_profile.value('MSC_HUB_CUR_CODE_RPT')
207 and mcc.CALENDAR_DATE(+) = exception_tbl.analysis_date;
208
209 commit;
210
211 /*
212 msc_resources_f: Union block into msc_resource_pkg, filter out SNO from existing code.
213 */
214 --dbms_output.put_line('wei: msc_resources_f');
215 -- msc_resources_f
216 insert into msc_resources_f(
217 plan_id,
218 plan_run_id,
219 sr_instance_id,
220 organization_id,
221 department_id,
222 owning_department_id,
223 resource_id,
224 inventory_item_id,
225 analysis_date,
226 created_by,
227 creation_date,
228 last_update_date,
229 last_updated_by,
230 last_update_login,
231 program_id,
232 program_login_id,
233 program_application_id,
234 request_id,
235 required_hours,
236 available_hours,
237 setup_time_hrs,
238 order_quantity,
239 resource_hours,
240 no_of_orders,
241 resource_cost, --wei
242 resource_cost2 --wei
243 )
244 select
245 p_plan_id,
246 p_plan_run_id,
247 sr_instance_id,
248 organization_id,
249 department_id,
250 nvl(owning_department_id, -23453),
251 resource_id,
252 inventory_item_id,
253 analysis_date,
254 fnd_global.user_id created_by,
255 sysdate creation_date,
256 sysdate last_update_date,
257 fnd_global.user_id last_updated_by,
258 fnd_global.login_id last_update_login,
259 fnd_global.conc_program_id program_id,
260 fnd_global.conc_login_id program_login_id,
261 fnd_global.prog_appl_id program_application_id,
262 fnd_global.conc_request_id request_id,
263 sum(required_hours),
264 sum(available_hours),
265 sum(setup_time_hrs),
266 sum(order_quantity),
267 sum(resource_hours),
268 sum(no_of_orders),
269 sum(resource_cost) resource_cost, --wei
270 sum(resource_cost2) resource_cost2 --wei
271 from
272 (
273 select
274 t1.plan_id,
275 t1.sr_instance_id,
276 t1.organization_id,
277 t1.department_id,
278 t1.owning_department_id,
279 t1.resource_id,
280 -23453 inventory_item_id,
281 t1.resource_date analysis_date,
282 t1.required_hours,
283 t1.available_hours,
284 t1.setup_hours setup_time_hrs,
285 to_number(null) order_quantity,
286 to_number(null) resource_hours,
287 to_number(null) no_of_orders,
288 t1.resource_cost,
289 t1.resource_cost * decode(t1.currency_code, fnd_profile.value('MSC_HUB_CUR_CODE_RPT'), 1, nvl(mcc.conv_rate, 0)) resource_cost2
290 from
291 (select
292 mdrs.plan_id,
293 mdrs.sr_instance_id,
294 mdrs.organization_id,
295 mtp.currency_code,
296 mdrs.department_id,
297 mdr.owning_department_id,
298 mdrs.resource_id,
299 mdrs.resource_date,
300 mdrs.required_hours,
301 mdrs.available_hours,
302 mdrs.setup_hours,
303 mdrs.resource_cost
304 from
305 msc_bis_res_summary mdrs,
306 msc_department_resources mdr,
307 msc_trading_partners mtp
308 where mdrs.plan_id = p_plan_id
309 and nvl(mdrs.detail_level, 0) = 1
310 and nvl(mdrs.period_type, 0) = 1
311 and mdrs.sr_instance_id = mtp.sr_instance_id(+)
312 and mdrs.organization_id = mtp.sr_tp_id(+)
313 and mtp.partner_type(+) = 3
314 and mdr.plan_id = mdrs.plan_id
315 and mdr.sr_instance_id = mdrs.sr_instance_id
316 and mdr.organization_id = mdrs.organization_id
317 and mdr.department_id = mdrs.department_id
318 and mdr.resource_id = mdrs.resource_id) t1,
319 msc_currency_conv_mv mcc
320 where mcc.to_currency(+) = fnd_profile.value('MSC_HUB_CUR_CODE_RPT')
321 and mcc.from_currency(+) = nvl(t1.currency_code, 'XXX')
322 and mcc.calendar_date(+) = t1.resource_date
323 )
324 group by
325 sr_instance_id,
326 organization_id,
327 department_id,
328 nvl(owning_department_id,-23453),
329 resource_id,
330 inventory_item_id,
331 analysis_date;
332
333 commit;
334
335 /*
336 msc_suppliers_f: Add conditional branch to msc_supplier_pkg (because existing code maps new_order_placement_date to Time dimension, SNO maps new_schedule_date). msc_supplier_pkg.is_new_buy_order to count SNO plans.
337 */
338 -- msc_suppliers_f: tuned to change in existing codes
339 --dbms_output.put_line('wei: msc_suppliers_f');
340 insert into msc_suppliers_f (
341 plan_id,
342 plan_run_id,
343 sr_instance_id,
344 organization_id,
345 supplier_id,
346 supplier_site_id,
347 inventory_item_id,
348 analysis_date,
349 required_qty,
350 avail_qty,
351 po_reschedule_count,
352 po_count,
353 po_cancel_count,
354 buy_order_value,
355 buy_order_value2,
356 buy_order_count,
357 created_by,
358 creation_date,
359 last_update_date,
360 last_updated_by,
361 last_update_login,
362 program_id,
363 program_login_id,
364 program_application_id,
365 request_id)
366 select
367 t1.plan_id,
368 p_plan_run_id,
369 t1.sr_instance_id,
370 t1.organization_id,
371 t1.supplier_id,
372 t1.supplier_site_id,
373 t1.inventory_item_id,
374 t1.analysis_date,
375 sum(t1.required_qty),
376 sum(t1.avail_qty),
377 sum(t1.po_reschedule_count),
378 sum(t1.po_count),
379 sum(t1.po_cancel_count),
380 sum(t1.buy_order_value),
381 sum(t1.buy_order_value * decode(t1.currency_code,fnd_profile.value('MSC_HUB_CUR_CODE_RPT'),1,nvl(mcc.conv_rate,0))) buy_order_value2,
382 sum(t1.buy_order_count),
383 fnd_global.user_id,
384 sysdate,
385 sysdate,
386 fnd_global.user_id,
387 fnd_global.login_id,
388 fnd_global.conc_program_id,
389 fnd_global.conc_login_id,
390 fnd_global.prog_appl_id,
391 fnd_global.conc_request_id
392 from
393 (
394 select
395 mbid.plan_id plan_id,
396 p_plan_run_id plan_run_id,
397 mbid.sr_instance_id,
398 mbid.organization_id,
399 mbid.supplier_id,
400 nvl(mbid.supplier_site_id, -23453) supplier_site_id,
401 mbid.inventory_item_id,
402 mtp.currency_code,
403 trunc(mbid.detail_date) analysis_date,
404 sum(mbid.supplier_usage) required_qty,
405 sum(mbid.supplier_capacity) avail_qty,
406 to_number(null) po_reschedule_count,
407 to_number(null) po_count,
408 to_number(null) po_cancel_count,
409 to_number(null) buy_order_value,
410 to_number(null) buy_order_value2,
411 to_number(null) buy_order_count
412 from
413 msc_bis_inv_detail mbid,
414 msc_trading_partners mtp
415 where mbid.plan_id = p_plan_id
416 and mbid.supplier_id is not null
417 and mbid.organization_id = mtp.sr_tp_id
418 and mbid.sr_instance_id = mtp.sr_instance_id
419 and mtp.partner_type = 3
420 group by
421 mbid.plan_id,
422 mbid.sr_instance_id,
423 mbid.organization_id,
424 mtp.currency_code,
425 mbid.supplier_id,
426 nvl(mbid.supplier_site_id, -23453),
427 mbid.inventory_item_id,
428 trunc(mbid.detail_date)
429
430 union all
431 select
432 ms.plan_id plan_id,
433 p_plan_run_id plan_run_id,
434 ms.sr_instance_id,
435 ms.organization_id,
436 ms.supplier_id, -- supplier_id for SNO
437 nvl(ms.supplier_site_id, -23453) supplier_site_id, -- supplier_site_id for SNO
438 ms.inventory_item_id inventory_item_id,
439 mtp.currency_code,
440 trunc(ms.new_schedule_date) analysis_date, -- new_schedule_date for SNO
441 to_number(null) required_qty, -- msc_bis_inv_detail.supplier_usage
442 to_number(null) avail_qty, -- msc_bis_inv_detail.supplier_capacity
443 to_number(null) po_reschedule_count, -- 0 for SNO
444 sum(decode(ms.order_type, 1, 1, 0)) po_count,
445 to_number(null), -- 0 for SNO
446
447 --sum(msc_supplier_pkg.supplier_spend_value (ms.new_order_quantity, nvl(msi.list_price,0)*(1-(nvl(msi.average_discount,0)/100)), ms.order_type)) buy_order_value,
448 --sum(msc_supplier_pkg.is_new_buy_order(ms.order_type, l_plan_type, msi.purchasing_enabled_flag))) buy_order_count
449
450 to_number(null) buy_order_value, -- wei: temp, should use lines above
451 to_number(null) buy_order_value2, -- wei: temp, should use lines above
452 to_number(null) buy_order_count -- wei: temp, should use lines above
453 from
454 msc_supplies ms,
455 msc_system_items msi,
456 msc_plans mp,
457 msc_trading_partners mtp
458 where mp.plan_id = p_plan_id
459 and mp.plan_type = 6
460 and ms.plan_id = p_plan_id
461 and ms.supplier_id is not null -- wei: make/move orders don't go to supplier and are filtered out
462 and ms.plan_id = msi.plan_id
463 and ms.sr_instance_id = msi.sr_instance_id
464 and ms.organization_id = msi.organization_id
465 and ms.inventory_item_id = msi.inventory_item_id
466 and ms.order_type in (planned_order, purchase_order, purchase_req, planned_arrival, new_buy_pos)
467 and ms.organization_id = mtp.sr_tp_id
468 and ms.sr_instance_id = mtp.sr_instance_id
469 and mtp.partner_type = 3
470 group by
471 ms.plan_id,
472 ms.sr_instance_id,
473 ms.organization_id,
474 mtp.currency_code,
475 ms.supplier_id,
476 nvl(ms.supplier_site_id, -23453),
477 ms.inventory_item_id,
478 trunc(ms.new_schedule_date)
479 ) t1,
480 msc_currency_conv_mv mcc
481 where mcc.to_currency(+) = FND_PROFILE.value('MSC_HUB_CUR_CODE_RPT')
482 and mcc.from_currency(+) = t1.currency_code
483 and mcc.calendar_date(+) = trunc(t1.analysis_date)
484 group by
485 t1.plan_id,
486 t1.sr_instance_id,
487 t1.organization_id,
488 t1.supplier_id,
489 t1.supplier_site_id,
490 t1.inventory_item_id,
491 t1.analysis_date;
492
493 commit;
494
495 /*
496 msc_demands_f: Merge SNO columns (net_demand, constrained_fcst, constrained_fcst_value, constrained_fcst_value2) in msc_demand_pkg.
497 msc_demands_cum_f: (new)
498 */
499 --dbms_output.put_line('wei: msc_demands_f');
500 -- msc_demands_f
501 insert into msc_demands_f (
502 plan_id,
503 plan_run_id,
504 sr_instance_id,
505 organization_id,
506 inventory_item_id,
507 project_id,
508 task_id,
509 customer_id,
510 customer_site_id,
511 demand_class,
512 order_date,
513 order_type,
514 demand_qty,
515 qty_by_due_date,
516 net_demand,
517 constrained_fcst,
518 constrained_fcst_value,
519 constrained_fcst_value2,
520 created_by,
521 creation_date,
522 last_updated_by,
523 last_update_date,
524 last_update_login
525 )
526 select
527 t1.plan_id,
528 p_plan_run_id,
529 t1.sr_instance_id,
530 t1.organization_id,
531 t1.inventory_item_id,
532 t1.project_id,
533 t1.task_id,
534 t1.customer_id,
535 t1.customer_site_id,
536 t1.demand_class,
537 t1.order_date,
538 t1.order_type,
539 t1.demand_qty,
540 t1.qty_by_due_date,
541 t1.demand_qty net_demand, --wei: SNO new column
542 t1.qty_by_due_date constrained_fcst, --wei: SNO new column
543 t1.qty_by_due_date * nvl(msi.list_price,0) * (1-(nvl(msi.average_discount,0)/100)) constrained_fcst_value, --wei: SNO new column
544 t1.qty_by_due_date * nvl(msi.list_price,0) * (1-(nvl(msi.average_discount,0)/100)) * decode(t1.currency_code, fnd_profile.value('MSC_HUB_CUR_CODE_RPT'), 1, nvl(mcc.conv_rate, 0)) constrained_fcst_value2, --wei: SNO new column
545 l_user_id created_by,
546 l_sysdate creation_date,
547 l_user_id last_updated_by,
548 l_sysdate last_update_date,
549 l_user_id last_update_login
550 from
551 (select
552 md.plan_id,
553 decode(md.organization_id, -1, -23453, md.sr_instance_id) sr_instance_id, -- wei: sync sr_instance_id with organization_id
554 decode(md.organization_id, -1, -23453, md.organization_id) organization_id,
555 mtp.currency_code,
556 md.inventory_item_id,
557 nvl(md.project_id, -23453) project_id,
558 nvl(md.task_id, -23453) task_id,
559 nvl(md.customer_id, -23453) customer_id,
560 nvl(md.customer_site_id, -23453) customer_site_id,
561 nvl(md.demand_class, '-23453') demand_class,
562 trunc(nvl(md.firm_date, md.using_assembly_demand_date)) order_date,
563 - md.origination_type order_type,
564 sum(using_requirement_quantity) demand_qty,
565 sum(quantity_by_due_date) qty_by_due_date
566 from
567 msc_demands md,
568 msc_trading_partners mtp
569 where md.plan_id = p_plan_id
570 and md.origination_type = 81 -- wei: SNO
571 and md.sr_instance_id = mtp.sr_instance_id(+)
572 and md.organization_id = mtp.sr_tp_id(+)
573 and mtp.partner_type(+) = 3
574 group by
575 md.plan_id,
576 md.sr_instance_id,
577 md.organization_id,
578 mtp.currency_code,
579 md.inventory_item_id,
580 nvl(md.project_id, -23453),
581 nvl(md.task_id, -23453),
582 nvl(md.customer_id, -23453),
583 nvl(md.customer_site_id, -23453),
584 nvl(md.demand_class, '-23453'),
585 trunc(nvl(md.firm_date, md.using_assembly_demand_date)),
586 md.origination_type) t1,
587 msc_system_items msi,
588 msc_plans mp,
589 msc_currency_conv_mv mcc
590 where msi.plan_id = t1.plan_id
591 and msi.sr_instance_id = decode(sign(t1.sr_instance_id), -1, mp.sr_instance_id, t1.sr_instance_id) --wei
592 and msi.organization_id = decode(sign(t1.organization_id), -1, mp.organization_id, t1.organization_id) --wei
593 and msi.inventory_item_id = t1.inventory_item_id
594 and t1.plan_id = mp.plan_id
595 and mcc.to_currency(+) = fnd_profile.value('MSC_HUB_CUR_CODE_RPT')
596 and mcc.from_currency(+) = nvl(t1.currency_code, 'XXX')
597 and mcc.calendar_date(+) = t1.order_date;
598
599 commit;
600
601
602 --dbms_output.put_line('wei: msc_demands_cum_f');
603 -- msc_demands_cum_f
604 insert into msc_demands_cum_f (
605 plan_id,
606 plan_run_id,
607 sr_instance_id,
608 organization_id,
609 inventory_item_id,
610 customer_id,
611 customer_site_id,
612 demand_class,
613 order_date,
614 cum_constrained_fcst,
615 cum_constrained_fcst_value,
616 cum_constrained_fcst_value2,
617 created_by,
618 creation_date,
619 last_updated_by,
620 last_update_date,
621 last_update_login,
622 program_id,
623 program_login_id,
624 program_application_id,
625 request_id
626 )
627 select
628 cum.plan_id,
629 cum.plan_run_id,
630 cum.sr_instance_id,
631 cum.organization_id,
632 cum.inventory_item_id,
633 cum.customer_id,
634 cum.customer_site_id,
635 cum.demand_class,
636 cum.end_date,
637 last_value(cum.cum_constrained_fcst ignore nulls) over (partition by
638 cum.plan_id, cum.plan_run_id,
639 cum.sr_instance_id, cum.organization_id,
640 cum.inventory_item_id, cum.demand_class,
641 cum.customer_id, cum.customer_site_id
642 order by cum.end_date) cum_constrained_fcst,
643 last_value(cum.cum_constrained_fcst_value ignore nulls) over (partition by
644 cum.plan_id, cum.plan_run_id,
645 cum.sr_instance_id, cum.organization_id,
646 cum.inventory_item_id, cum.demand_class,
647 cum.customer_id, cum.customer_site_id
648 order by cum.end_date) cum_constrained_fcst_value,
649 last_value(cum.cum_constrained_fcst_value2 ignore nulls) over (partition by
650 cum.plan_id, cum.plan_run_id,
651 cum.sr_instance_id, cum.organization_id,
652 cum.inventory_item_id, cum.demand_class,
653 cum.customer_id, cum.customer_site_id
654 order by cum.end_date) cum_constrained_fcst_value2,
655 l_user_id,
656 l_sysdate,
657 l_user_id,
658 l_sysdate,
659 l_user_id,
660 l_program_id,
661 l_cp_login_id,
662 l_appl_id,
663 l_request_id
664 from (
665 select
666 k.plan_id,
667 k.plan_run_id,
668 k.sr_instance_id,
669 k.organization_id,
670 k.inventory_item_id,
671 k.customer_id,
672 k.customer_site_id,
673 k.demand_class,
674 k.end_date,
675 sum(f.constrained_fcst) over(partition by
676 f.plan_id, f.plan_run_id,
677 f.sr_instance_id, f.organization_id,
678 f.inventory_item_id, f.demand_class,
679 f.customer_id, f.customer_site_id
680 order by f.order_date) cum_constrained_fcst,
681 sum(f.constrained_fcst_value) over(partition by
682 f.plan_id, f.plan_run_id,
683 f.sr_instance_id, f.organization_id,
684 f.inventory_item_id, f.demand_class,
685 f.customer_id, f.customer_site_id
686 order by f.order_date) cum_constrained_fcst_value,
687 sum(f.constrained_fcst_value2) over(partition by
688 f.plan_id, f.plan_run_id,
689 f.sr_instance_id, f.organization_id,
690 f.inventory_item_id, f.demand_class,
691 f.customer_id, f.customer_site_id
692 order by f.order_date) cum_constrained_fcst_value2
693 from
694 (select distinct
695 k1.plan_id,
696 k1.plan_run_id,
697 k1.sr_instance_id,
698 k1.organization_id,
699 k1.inventory_item_id,
700 k1.customer_id,
701 k1.customer_site_id,
702 k1.demand_class,
703 k2.end_date
704 from msc_demands_f k1,
705 (select trunc(v.month_end_date) end_date
706 from msc_calendar_dtl v, msc_plans mp
707 where mp.plan_id=p_plan_id
708 and v.month_end_date between mp.curr_start_date and mp.curr_cutoff_date
709 union all
710 select trunc(v.week_end_date) end_date
711 from msc_phub_mfg_cal_weeks_mv v, msc_plans mp
712 where mp.plan_id=p_plan_id
713 and v.week_end_date between mp.curr_start_date and mp.curr_cutoff_date
714 union all
715 select trunc(v.end_date) end_date
716 from msc_phub_fiscal_periods_mv v, msc_plans mp
717 where mp.plan_id=p_plan_id
718 and v.end_date between mp.curr_start_date and mp.curr_cutoff_date
719 union all
720 select order_date from msc_demands_f f
721 where f.plan_id=p_plan_id and f.plan_run_id=p_plan_run_id
722 ) k2
723 where k1.plan_id=p_plan_id and k1.plan_run_id=p_plan_run_id) k,
724 msc_demands_f f
725 where k.plan_id = f.plan_id(+)
726 and k.plan_run_id = f.plan_run_id(+)
727 and k.sr_instance_id = f.sr_instance_id(+)
728 and k.organization_id = f.organization_id(+)
729 and k.inventory_item_id = f.inventory_item_id(+)
730 and k.customer_id = f.customer_id(+)
731 and k.customer_site_id = f.customer_site_id(+)
732 and k.demand_class = f.demand_class(+)
733 and k.end_date = f.order_date(+)) cum;
734
735 commit;
736
737 /*
738 msc_item_inventory_f: Merge SNO columns (inv_build_target) in msc_item_pkg.
739 */
740 -- msc_item_inventory_f: tuned to change in existing codes
741 --dbms_output.put_line('wei: msc_item_inventory_f');
742 insert into msc_item_inventory_f (
743 created_by,
744 creation_date,
745 last_updated_by,
746 last_update_date,
747 last_update_login,
748 program_id,
749 program_login_id,
750 program_application_id,
751 request_id,
752 plan_id,
753 plan_run_id,
754 sr_instance_id,
755 organization_id,
756 inventory_item_id,
757 ship_method, --wei
758 vmi_flag,
759 order_date,
760 pab_qty,
761 pab_value,
762 pab_value2,
763 safety_stock_qty,
764 min_inventory_level,
765 max_inventory_level,
766 avg_daily_demand,
767 supply_chain_cost,
768 supply_chain_cost2,
769 revenue,
770 revenue2,
771 manufacturing_cost,
772 manufacturing_cost2,
773 transportation_cost,
774 transportation_cost2,
775 purchasing_cost,
776 purchasing_cost2,
777 carrying_cost,
778 carrying_cost2,
779 gross_margin,
780 gross_margin2,
781 inv_build_target) --wei
782 select
783 l_user_id,
784 l_sysdate,
785 l_user_id,
786 l_sysdate,
787 l_user_login_id,
788 l_program_id,
789 l_cp_login_id,
790 l_appl_id,
791 l_request_id,
792 pab_tbl.plan_id,
793 pab_tbl.plan_run_id,
794 pab_tbl.sr_instance_id,
795 pab_tbl.organization_id,
796 pab_tbl.inventory_item_id,
797 pab_tbl.ship_method, --wei: SNO dimension
798 pab_tbl.vmi_flag,
799 pab_tbl.order_date,
800 sum(pab_tbl.pab_qty) pab_qty,
801 sum(pab_tbl.pab_value) pab_value,
802 sum((pab_tbl.pab_value) *decode(pab_tbl.currency_code,fnd_profile.value('MSC_HUB_CUR_CODE_RPT'),1,
803 nvl(mcc.CONV_RATE,0))) pab_value2,
804 sum(pab_tbl.safety_Stock_qty) safety_Stock_qty,
805 sum(pab_tbl.min_inventory_level) min_inventory_level,
806 sum(pab_tbl.max_inventory_level) max_inventory_level,
807 sum(pab_tbl.avg_daily_demand) avg_daily_demand,
808 sum(pab_tbl.total_cost),
809 sum(pab_tbl.total_cost * decode(pab_tbl.currency_code,fnd_profile.value('MSC_HUB_CUR_CODE_RPT'),1,
810 nvl(mcc.CONV_RATE,0))),
811 sum(pab_tbl.revenue),
812 sum(pab_tbl.revenue * decode(pab_tbl.currency_code,fnd_profile.value('MSC_HUB_CUR_CODE_RPT'),1,
813 nvl(mcc.CONV_RATE,0))),
814 sum(pab_tbl.mfg_cost),
815 sum(pab_tbl.mfg_cost* decode(pab_tbl.currency_code,fnd_profile.value('MSC_HUB_CUR_CODE_RPT'),1,
816 nvl(mcc.CONV_RATE,0))),
817 sum(pab_tbl.tp_cost),
818 sum(pab_tbl.tp_cost * decode(pab_tbl.currency_code,fnd_profile.value('MSC_HUB_CUR_CODE_RPT'),1,
819 nvl(mcc.CONV_RATE,0))),
820 sum(pab_tbl.po_cost),
821 sum(pab_tbl.po_cost * decode(pab_tbl.currency_code,fnd_profile.value('MSC_HUB_CUR_CODE_RPT'),1,
822 nvl(mcc.CONV_RATE,0))),
823 sum(pab_tbl.carrying_cost),
824 sum(pab_tbl.carrying_cost * decode(pab_tbl.currency_code,fnd_profile.value('MSC_HUB_CUR_CODE_RPT'),1,
825 nvl(mcc.CONV_RATE,0))),
826 sum(pab_tbl.revenue- pab_tbl.total_cost),
827 sum((pab_tbl.revenue- pab_tbl.total_cost) *decode(pab_tbl.currency_code,fnd_profile.value('MSC_HUB_CUR_CODE_RPT'),1,
828 nvl(mcc.CONV_RATE,0))),
829 sum(pab_tbl.inv_build_target) --wei: SNO measure
830 from
831 (
832 select
833 p_plan_id plan_id,
834 p_plan_run_id plan_run_id,
835 mbid.sr_instance_id,
836 mbid.organization_id,
837 mbid.inventory_item_id,
838 mbid.ship_method, --wei: SNO dimension
839 to_number(null) vmi_flag,
840 nvl(bis_mtp.currency_code,'XXX') currency_code,
841 mbid.detail_date order_date, --- hub week end date
842 to_number(null) pab_qty,
843 to_number(null) pab_value,
844 to_number(null) safety_stock_qty, -- ss
845 to_number(null) min_inventory_level, -- min level
846 to_number(null) max_inventory_level,
847 to_number(null) avg_daily_demand,
848 mbid.mds_price revenue,
849 mbid.PRODUCTION_COST mfg_cost,
850 mbid.purchasing_cost po_cost,
851 mbid.TRANSPORTATION_COST tp_cost,
852 mbid.carrying_cost carrying_cost,
853 nvl(mbid.PRODUCTION_COST,0) + nvl(mbid.purchasing_cost,0) +
854 nvl(mbid.carrying_cost,0) + nvl(mbid.TRANSPORTATION_COST,0) total_cost,
855 last_value(mbid.pab ignore nulls) over (partition by mbid.plan_id, mbid.sr_instance_id,
856 mbid.organization_id, mbid.inventory_item_id, mbid.ship_method
857 order by mbid.detail_date) inv_build_target
858 -- wei: SNO meassure, to add include partition by vmi.number6 order by map.date2
859 from msc_bis_inv_detail mbid,
860 msc_trading_partners bis_mtp
861 where nvl(mbid.detail_level,0)=1
862 and nvl(mbid.period_type,0)=1
863 and mbid.plan_id=p_plan_id
864 and mbid.sr_instance_id = bis_mtp.sr_instance_id
865 and mbid.organization_id = bis_mtp.sr_tp_id
866 and bis_mtp.partner_type = 3
867 ) pab_tbl,
868 msc_currency_conv_mv mcc
869 where mcc.from_currency(+) =pab_tbl.currency_code --- make sure 'xxx' is not a valid currency code
870 and mcc.to_currency(+) = fnd_profile.value('MSC_HUB_CUR_CODE_RPT')
871 and mcc.calendar_date(+) = pab_tbl.order_date
872 group by
873 pab_tbl.plan_id,
874 pab_tbl.plan_run_id,
875 pab_tbl.sr_instance_id,
876 pab_tbl.organization_id,
877 pab_tbl.inventory_item_id,
878 pab_tbl.ship_method, --wei: SNO dimension
879 pab_tbl.vmi_flag,
880 pab_tbl.order_date;
881
882 commit;
883
884 exception
885 when dup_val_on_index then
886 --dbms_output.put_line('wei: dup_val_on_index');
887 errbuf := msc_phub_util.get_planning_hub_message('MSC_HUB_DUPLICATE_DATA')||
888 SQLCODE||' -ERROR- '||SQLERRM;
889 retcode := 2;
890
891 if (fnd_log.level_unexpected >= fnd_log.g_current_runtime_level) then
892 fnd_log.string(fnd_log.level_unexpected, l_api_name, to_char(SQLCODE)||':'||SQLERRM);
893 end if;
894
895
896 when others then
897 --dbms_output.put_line('wei: others');
898 errbuf := msc_phub_util.get_planning_hub_message('MSC_HUB_POPULATE_ERROR')||
899 SQLCODE||' -ERROR- '||SQLERRM;
900 retcode := 2;
901
902 if (fnd_log.level_unexpected >= fnd_log.g_current_runtime_level) then
903 fnd_log.string(fnd_log.level_unexpected, l_api_name, to_char(SQLCODE)||':'||SQLERRM);
904 end if;
905
906 end populate_details;
907
908 end msc_sno_pkg;