[Home] [Help]
PACKAGE BODY: APPS.MSC_ITEM_PKG
Source
1 package body msc_item_pkg as
2 /* $Header: MSCHBITB.pls 120.99.12020000.2 2012/10/11 14:14:22 wexia ship $ */
3
4
5
6 procedure populate_details(errbuf out nocopy varchar2,
7 retcode out nocopy varchar2,
8 p_plan_id number,
9 p_plan_run_id number default null)
10 is
11 l_plan_start_date date;
12 l_plan_cutoff_date date;
13 l_plan_type number;
14 l_sr_instance_id number;
15 l_plan_days number;
16 l_owning_currency_code varchar2(20) := msc_phub_util.get_owning_currency_code(p_plan_run_id);
17 l_refresh_mode number;
18 l_item_rn_qid number;
19 l_qid_last_date1 number;
20 l_rowcount1 number := 0;
21 l_rowcount2 number := 0;
22 l_transfer_id number := null;
23 l_start_time timestamp := systimestamp;
24 l_enforce_wh_cpty number := 2;
25 begin
26 msc_phub_util.log('msc_item_pkg.populate_details');
27 retcode := 0; -- this means successfully
28 errbuf := '';
29
30 -- ODS plan
31 if p_plan_id=-1
32 then
33 -- get plan_cutoff_date
34 select trunc(plan_cutoff_date) into l_plan_cutoff_date
35 from msc_plan_runs
36 where plan_run_id=p_plan_run_id;
37
38 -- get refresh_mode
39 select refresh_mode into l_refresh_mode
40 from msc_plan_runs
41 where plan_run_id=p_plan_run_id;
42
43 if l_refresh_mode=2 -- targeted refesh
44 then
45 l_item_rn_qid := msc_phub_util.get_item_rn_qid(p_plan_id, p_plan_run_id);
46
47 delete from msc_item_inventory_f
48 where plan_id=p_plan_id
49 and plan_run_id=p_plan_run_id
50 and (p_plan_id, sr_instance_id, organization_id, inventory_item_id) in
51 (select number1, number2, number3, number4 from msc_hub_query q where q.query_id=l_item_rn_qid);
52
53 l_rowcount1 := l_rowcount1 + sql%rowcount;
54 msc_phub_util.log('msc_item_inventory_f, delete='||sql%rowcount||', l_rowcount1='||l_rowcount1);
55 commit;
56
57 delete from msc_item_orders_f
58 where plan_id=p_plan_id
59 and plan_run_id=p_plan_run_id
60 and (p_plan_id, sr_instance_id, organization_id, inventory_item_id) in
61 (select number1, number2, number3, number4 from msc_hub_query q where q.query_id=l_item_rn_qid);
62
63 l_rowcount2 := l_rowcount2 + sql%rowcount;
64 msc_phub_util.log('msc_item_orders_f, delete='||sql%rowcount||', l_rowcount2='||l_rowcount2);
65 commit;
66 end if;
67 end if;
68
69 select plan_type, sr_instance_id, plan_start_date, plan_cutoff_date, temp_transfer_id
70 into l_plan_type, l_sr_instance_id, l_plan_start_date, l_plan_cutoff_date, l_transfer_id
71 from msc_plan_runs
72 where plan_id=p_plan_id
73 and plan_run_id=p_plan_run_id;
74
75 l_plan_days := (l_plan_cutoff_date - l_plan_start_date + 1);
76 l_qid_last_date1 := msc_phub_util.get_reporting_dates(l_plan_start_date, l_plan_cutoff_date);
77
78 select nvl(enforce_wrh_cpty,2)
79 into l_enforce_wh_cpty
80 from msc_plans
81 where plan_id=p_plan_id;
82
83 msc_phub_util.log('msc_item_pkg.populate_details: '||
84 p_plan_id||','||p_plan_run_id||','||l_plan_type||','||
85 l_sr_instance_id||','||l_plan_start_date||','||l_plan_cutoff_date||','||
86 l_transfer_id||','||l_refresh_mode||','||l_item_rn_qid||','||
87 l_enforce_wh_cpty);
88
89 -- msc_st_item_orders_f:demands
90 insert /*+ append nologging */ into msc_st_item_orders_f (
91 st_transaction_id,
92 error_code,
93 sr_instance_id,
94 organization_id,
95 subinventory_code,
96 owning_inst_id,
97 owning_org_id,
98 inventory_item_id,
99 order_date,
100
101 demand_qty,
102 indep_demand_qty,
103 indep_demand_value,
104 dep_demand_qty,
105 sales_order_value,
106 forecast_qty,
107
108 created_by, creation_date,
109 last_update_date, last_updated_by, last_update_login,
110 program_id, program_login_id,
111 program_application_id, request_id)
112 select
113 l_transfer_id,
114 to_number(0),
115 f.sr_instance_id,
116 f.organization_id,
117 '-23453',
118 f.owning_inst_id,
119 f.owning_org_id,
120 f.inventory_item_id,
121 d.date2 order_date,
122
123 -- for drp plan
124 ---
125 -- work order,INTER_ORG_DEMAND (based on order_date), exclude planned order
126 -- supply, exclude onhand(18)
127 --- supply type 1,2,51 also count as demand
128 -- decode(ms.order_type, PLANNED_ARRIVAL, PLANNED_SHIPMENT_OFF,
129 -- PURCHASE_ORDER,PLANNED_SHIPMENT_OFF,
130 -- PURCH_REQ,PLANNED_SHIPMENT_OFF)
131
132 --pab= total_suply+onhand-total_demand
133
134 sum(case when l_plan_type=5 then
135 decode(nvl(f.order_type,0), 0,0, -1,0,
136 -29,decode(f.organization_id,-23453,0,nvl(f.demand_qty,0)),
137 -31,0,
138 nvl(f.demand_qty,0))
139 --- exclude defective demand from total demand qty for spp
140 when l_plan_type=8 and nvl(f.part_condition,1)=2
141 then 0
142
143 else
144 decode(nvl(f.order_type,0), 0,0, -5,0, -22,0,
145 -29,decode(f.organization_id,-23453,0,nvl(f.demand_qty,0)),
146 -31,0,
147 nvl(f.demand_qty,0))
148
149 end) demand_qty,
150
151 sum(nvl(f.indep_demand_qty,0)) indep_demand_qty,
152 sum(nvl(f.indep_demand_qty,0) * nvl(i.standard_cost,0)) indep_demand_value,
153
154 ---- make sure this indep_demand_value is qty * std_cost. this is used to calculate
155 ---- cogs =item std cost x sum of Indep dem qty
156
157 --sum(nvl(INDEP_DEMAND_QTY,0) * i.standard_cost) indep_demand_value,
158 --bnaghi -dep_demand_qty not calculated for DRP plan
159 decode(l_plan_type,5,to_number(null), sum(decode(nvl(f.order_type,0),
160 -1,decode(l_plan_type,5,0,nvl(f.demand_qty,0)), -- exclude drp planned demand from dep demand
161 -2,nvl(f.demand_qty,0),
162 -3,nvl(f.demand_qty,0),
163 -4,nvl(f.demand_qty,0),
164 -24,nvl(f.demand_qty,0),
165 -25,nvl(f.demand_qty,0),
166 0)) ) dep_demand_qty,
167
168 --bnaghi -sales_order_value and forecast_qty not calculated for DRP plan
169 decode(l_plan_type,5,to_number(null),sum(nvl(f.sales_order_qty,0) * nvl(i.list_price,0)*(1-nvl(i.average_discount,0)/100))) sales_order_value,
170 decode(l_plan_type,5,to_number(null),sum(decode(nvl(f.order_type,0), -29,nvl(f.demand_qty,0), 0))) forecast_qty,
171
172 fnd_global.user_id, sysdate,
173 sysdate, fnd_global.user_id, fnd_global.login_id,
174 fnd_global.conc_program_id, fnd_global.conc_login_id,
175 fnd_global.prog_appl_id, fnd_global.conc_request_id
176 from
177 msc_demands_f f,
178 msc_hub_query d,
179 msc_apcc_item_d i
180 where f.plan_run_id=p_plan_run_id
181 and f.aggr_type=0
182 and d.query_id=l_qid_last_date1
183 and f.order_date between d.date1 and d.date2
184 and f.plan_id=i.plan_id
185 and f.sr_instance_id=i.sr_instance_id
186 and f.organization_id=i.organization_id
187 and f.inventory_item_id=i.inventory_item_id
188 and (p_plan_id <> -1
189 or (p_plan_id=-1
190 and f.sr_instance_id=l_sr_instance_id
191 and (l_refresh_mode=1
192 or (l_refresh_mode=2 and (f.plan_id, f.sr_instance_id, f.organization_id, f.inventory_item_id) in
193 (select number1, number2, number3, number4 from msc_hub_query q where q.query_id=l_item_rn_qid)))))
194 group by
195 f.sr_instance_id,
196 f.organization_id,
197 f.owning_inst_id,
198 f.owning_org_id,
199 f.inventory_item_id,
200 d.date2;
201
202 msc_phub_util.log('insert into msc_st_item_orders_f:demands: '||sql%rowcount);
203 commit;
204
205 -- msc_st_item_orders_f:supplies
206 insert /*+ append nologging */ into msc_st_item_orders_f (
207 st_transaction_id,
208 error_code,
209 sr_instance_id,
210 organization_id,
211 subinventory_code,
212 owning_inst_id,
213 owning_org_id,
214 inventory_item_id,
215 order_date,
216
217 supply_qty,
218 demand_qty,
219 planned_order_qty,
220 return_order_value,
221 make_order_qty,
222 make_order_leadtime,
223 make_order_count,
224 item_leadtime,
225 onhand_qty,
226 onhand_value,
227 onhand_usable,
228 onhand_defective,
229 intransit_usable,
230 intransit_defective,
231 plnd_xfer_usable,
232 plnd_xfer_defective,
233 supply_qty_usable,
234 supply_qty_defective,
235 scheduled_rept_qty,
236 scheduled_rept_value,
237
238 created_by, creation_date,
239 last_update_date, last_updated_by, last_update_login,
240 program_id, program_login_id,
241 program_application_id, request_id)
242 select
243 l_transfer_id,
244 to_number(0),
245 f.sr_instance_id,
246 f.organization_id,
247 f.subinventory_code,
248 f.owning_inst_id,
249 f.owning_org_id,
250 f.inventory_item_id,
251 d.date2 order_date,
252
253 --- exclude onhand from total supply for drp
254 sum(case when l_plan_type=5 and f.supply_type in (18)
255 then null
256 when f.supply_type in (4,0)
257 then 0
258 --- exclude defective supply from total supply qty for spp
259 when l_plan_type=8 and nvl(f.part_condition,1)=2
260 then 0
261 else nvl(f.supply_qty,0) end) supply_qty,
262
263 --- in drp, supply(1,2,51) is demand
264 sum(nvl(f.drp_supply_as_demand,0)) demand_qty,
265
266 /* ms.source_organization_id <> ms.organization_id
267 and (ms.order_type <> PURCH_REQ or
268 (ms.order_type=PURCH_REQ and ms.supplier_id is not null))*/
269 -- bnaghi : not available for DRP plan
270 decode(l_plan_type,5,to_number(null), sum(case when f.supply_type in (5,76,77,78,79) then f.supply_qty else 0 end)) planned_order_qty,
271
272 decode(l_plan_type,5,to_number(null),sum(nvl(f.return_order_qty,0) * nvl(i.list_price,0)*(1-nvl(i.average_discount,0)/100))) return_order_value,
273
274 decode(l_plan_type,5,to_number(null),sum(nvl(f.work_order_qty,0))) make_order_qty,
275 -- return order in srp is defined
276 -- as order_type in (1,2,18) and nvl(item_type_id,401)=401 and nvl(item_type_value,1)=2
277
278 decode(l_plan_type,5,to_number(null),sum(nvl(f.work_order_leadtime,0))) make_order_leadtime,
279 decode(l_plan_type,5,to_number(null),sum(nvl(f.work_order_count,0))) make_order_count,
280 decode(l_plan_type,5,to_number(null),avg(i.fixed_lead_time)) item_leadtime,
281 sum(decode(nvl(f.supply_type,0), 18, nvl(f.supply_qty,0), 0)) onhand_qty,
282 sum(decode(nvl(f.supply_type,0), 18, nvl(f.supply_qty,0), 0) * nvl(i.standard_cost,0)) onhand_value,
283
284 sum(case when l_plan_type=8
285 and nvl(f.part_condition,1)=1
286 and f.supply_type=18
287 then f.supply_qty else 0 end) onhand_usable,
288
289 sum(case when l_plan_type=8
290 and nvl(f.part_condition,1)=2
291 and f.supply_type=18
292 then f.supply_qty else 0 end) onhand_defective,
293
294 sum(case when l_plan_type=8
295 and nvl(f.part_condition,1)=1
296 and f.supply_type in (8,11,12)
297 then f.supply_qty else 0 end) intransit_usable,
298
299 sum(case when l_plan_type=8
300 and nvl(f.part_condition,1)=2
301 and f.supply_type in (8,11,12)
302 then f.supply_qty else 0 end) intransit_defective,
303
304 sum(case when l_plan_type=8
305 and nvl(f.part_condition,1)=1
306 and f.supply_type=51
307 then f.supply_qty else 0 end) plnd_xfer_usable,
308
309 sum(case when l_plan_type=8
310 and nvl(f.part_condition,1)=2
311 and f.supply_type=51
312 then f.supply_qty else 0 end) plnd_xfer_defective,
313
314 sum(case when l_plan_type=8
315 and nvl(f.part_condition,1)=1
319 and nvl(f.part_condition,1)=2
316 then f.supply_qty else 0 end) supply_qty_usable,
317
318 sum(case when l_plan_type=8
320 then f.supply_qty else 0 end) supply_qty_defective,
321
322 sum(case when f.supply_type in (1,2,3,8,11,12,14,27,49,53,80)
323 then nvl(f.supply_qty, 0) else 0 end) scheduled_rept_qty, -- bug 6797566, 9376354
324
325 sum((case when f.supply_type in (1,2,3,8,11,12,14,27,49,53,80)
326 then nvl(f.supply_qty, 0) else 0 end) * nvl(i.standard_cost,0)) scheduled_rept_value,
327
328 fnd_global.user_id, sysdate,
329 sysdate, fnd_global.user_id, fnd_global.login_id,
330 fnd_global.conc_program_id, fnd_global.conc_login_id,
331 fnd_global.prog_appl_id, fnd_global.conc_request_id
332 from
333 msc_supplies_f f,
334 msc_hub_query d,
335 msc_apcc_item_d i
336 where f.plan_run_id=p_plan_run_id
337 and f.aggr_type=0
338 and d.query_id=l_qid_last_date1
339 and f.supply_date between d.date1 and d.date2
340 and f.plan_id=i.plan_id
341 and f.sr_instance_id=i.sr_instance_id
342 and f.organization_id=i.organization_id
343 and f.inventory_item_id=i.inventory_item_id
344 and (p_plan_id <> -1
345 or (p_plan_id=-1
346 and f.sr_instance_id=l_sr_instance_id
347 and (l_refresh_mode=1
348 or (l_refresh_mode=2 and (f.plan_id, f.sr_instance_id, f.organization_id, f.inventory_item_id) in
349 (select number1, number2, number3, number4 from msc_hub_query q where q.query_id=l_item_rn_qid)))))
350 group by
351 f.sr_instance_id,
352 f.organization_id,
353 f.subinventory_code,
354 f.owning_inst_id,
355 f.owning_org_id,
356 f.inventory_item_id,
357 d.date2;
358
359 msc_phub_util.log('insert into msc_st_item_orders_f:supplies: '||sql%rowcount);
360 commit;
361
362 if l_plan_type not in (5) and p_plan_id <> -1 then
363 -- msc_st_item_orders_f:supply end-pegged
364 insert /*+ append nologging */ into msc_st_item_orders_f (
365 st_transaction_id,
366 error_code,
367 sr_instance_id,
368 organization_id,
369 subinventory_code,
370 owning_inst_id,
371 owning_org_id,
372 inventory_item_id,
373 order_date,
374
375 sup_end_pgd_to_fcst,
376 sup_end_pgd_to_so,
377 sup_end_pgd_to_ss,
378 sup_end_pgd_to_excess,
379 sup_end_pgd_to_fcst_value,
380 sup_end_pgd_to_so_value,
381 sup_end_pgd_to_ss_value,
382 sup_end_pgd_to_excess_value,
383
384 created_by, creation_date,
385 last_update_date, last_updated_by, last_update_login,
386 program_id, program_login_id,
387 program_application_id, request_id)
388 select /*+ ordered */
389 l_transfer_id,
390 to_number(0),
391 mfp.sr_instance_id,
392 mfp.organization_id,
393 '-23453',
394 mfp.sr_instance_id,
395 mfp.organization_id,
396 mfp.inventory_item_id,
397 trunc(nvl(ms.firm_date, ms.new_schedule_date)) order_date,
398
399 sum(decode(mfp2.end_origination_type, 29, mfp.allocated_quantity, 0)) sup_end_pgd_to_fcst,
400 sum(decode(mfp2.end_origination_type, 30, mfp.allocated_quantity, 0)) sup_end_pgd_to_so,
401 sum(decode(mfp2.demand_id, -2, mfp.allocated_quantity, 0)) sup_end_pgd_to_ss,
402 sum(decode(mfp2.demand_id, -1, mfp.allocated_quantity, 0)) sup_end_pgd_to_excess,
403 sum(decode(mfp2.end_origination_type, 29, mfp.allocated_quantity,0) *
404 nvl(i.standard_cost,0)) sup_end_pgd_to_fcst_value,
405 sum(decode(mfp2.end_origination_type, 30, mfp.allocated_quantity, 0) *
406 nvl(i.standard_cost,0)) sup_end_pgd_to_so_value,
407 sum(decode(mfp2.demand_id, -2, mfp.allocated_quantity, 0) *
408 nvl(i.standard_cost,0)) sup_end_pgd_to_ss_value,
409 sum(decode(mfp2.demand_id, -1, mfp.allocated_quantity, 0) *
410 nvl(i.standard_cost,0)) sup_end_pgd_to_excess_value,
411
412 fnd_global.user_id, sysdate,
413 sysdate, fnd_global.user_id, fnd_global.login_id,
414 fnd_global.conc_program_id, fnd_global.conc_login_id,
415 fnd_global.prog_appl_id, fnd_global.conc_request_id
416 from
417 msc_full_pegging mfp,
418 msc_supplies ms,
419 msc_apcc_item_d i,
420 msc_full_pegging mfp2
421 where mfp.plan_id=mfp2.plan_id
422 and mfp.end_pegging_id=mfp2.end_pegging_id
423 and mfp2.prev_pegging_id is null
424 and mfp2.plan_id=p_plan_id
425 and mfp.plan_id=i.plan_id
426 and mfp.sr_instance_id=i.sr_instance_id
427 and mfp.organization_id=i.organization_id
428 and mfp.inventory_item_id=i.inventory_item_id
429 and mfp.plan_id=ms.plan_id
430 and mfp.transaction_id=ms.transaction_id
431 and (mfp2.demand_id in (-1, -2) or mfp2.end_origination_type in (29, 30))
432 group by
433 mfp.sr_instance_id,
434 mfp.organization_id,
435 mfp.inventory_item_id,
436 trunc(nvl(ms.firm_date, ms.new_schedule_date));
437
441
438 msc_phub_util.log('insert into msc_st_item_orders_f:supply end-pegged: '||sql%rowcount);
439 commit;
440 end if;
442 -- msc_st_item_orders_f:demand pegged
443 -- bnaghi : these facts are not available for DRP plan
444 insert /*+ append nologging */ into msc_st_item_orders_f (
445 st_transaction_id,
446 error_code,
447 sr_instance_id,
448 organization_id,
449 subinventory_code,
450 owning_inst_id,
451 owning_org_id,
452 inventory_item_id,
453 order_date,
454
455 dmd_pgd_to_schd_recp,
456 dmd_pgd_to_plnd_order,
457 dmd_pgd_to_onhand,
458 dmd_pgd_to_schd_recp_value,
459 dmd_pgd_to_plnd_order_value,
460 dmd_pgd_to_onhand_value,
461
462 created_by, creation_date,
463 last_update_date, last_updated_by, last_update_login,
464 program_id, program_login_id,
465 program_application_id, request_id)
466 select
467 l_transfer_id,
468 to_number(0),
469 mfp.sr_instance_id,
470 mfp.organization_id,
471 '-23453',
472 mfp.sr_instance_id,
473 mfp.organization_id,
474 mfp.inventory_item_id,
475 trunc(mfp.demand_date) order_date,
476
477 sum(case when mfp.supply_type in (1,2,3,11,12) then mfp.allocated_quantity else 0 end) dmd_pgd_to_schd_recp,
478 sum(case when mfp.supply_type in (5,76,77,78,79) then mfp.allocated_quantity else 0 end) dmd_pgd_to_plnd_order,
479 sum(case when mfp.supply_type in (18) then mfp.allocated_quantity else 0 end) dmd_pgd_to_onhand,
480 sum((case when mfp.supply_type in (1,2,3,11,12) then mfp.allocated_quantity else 0 end) *
481 nvl(i.list_price,0)*(1-nvl(i.average_discount,0)/100)) dmd_pgd_to_schd_recp_value,
482 sum((case when mfp.supply_type in (5,76,77,78,79) then mfp.allocated_quantity else 0 end) *
483 nvl(i.list_price,0)*(1-nvl(i.average_discount,0)/100)) dmd_pgd_to_plnd_order_value,
484 sum((case when mfp.supply_type in (18) then mfp.allocated_quantity else 0 end) *
485 nvl(i.list_price,0)*(1-nvl(i.average_discount,0)/100)) dmd_pgd_to_onhand_value,
486
487 fnd_global.user_id, sysdate,
488 sysdate, fnd_global.user_id, fnd_global.login_id,
489 fnd_global.conc_program_id, fnd_global.conc_login_id,
490 fnd_global.prog_appl_id, fnd_global.conc_request_id
491 from
492 msc_full_pegging mfp,
493 msc_apcc_item_d i,
494 msc_demands md
495 where mfp.plan_id=md.plan_id
496 and mfp.demand_id=md.demand_id
497 and md.origination_type in (5,6,7,8,9,10,11,12,15,22,24,27,29,30)
498 and mfp.plan_id=p_plan_id
499 and mfp.plan_id=i.plan_id
500 and mfp.sr_instance_id=i.sr_instance_id
501 and mfp.organization_id=i.organization_id
502 and mfp.inventory_item_id=i.inventory_item_id
503 and p_plan_id <> -1
504 and l_plan_type not in (5)
505 group by
506 mfp.sr_instance_id,
507 mfp.organization_id,
508 mfp.inventory_item_id,
509 trunc(mfp.demand_date);
510
511 msc_phub_util.log('insert into msc_st_item_orders_f:demand pegged: '||sql%rowcount);
512 commit;
513
514 -- msc_st_item_orders_f:exception
515 -- bnaghi - not available for DRP plans
516 insert /*+ append nologging */ into msc_st_item_orders_f (
517 st_transaction_id,
518 error_code,
519 sr_instance_id,
520 organization_id,
521 subinventory_code,
522 owning_inst_id,
523 owning_org_id,
524 inventory_item_id,
525 order_date,
526
527 no_activity_item_count,
528 stock_outs_count,
529
530 created_by, creation_date,
531 last_update_date, last_updated_by, last_update_login,
532 program_id, program_login_id,
533 program_application_id, request_id)
534 select
535 l_transfer_id,
536 to_number(0),
537 me.sr_instance_id,
538 me.organization_id,
539 '-23453',
540 me.owning_inst_id,
541 me.owning_org_id,
542 me.inventory_item_id,
543 me.analysis_date order_date, --- bkt_start_date
544
545 sum(decode(exception_type,5,exception_count,0)) no_activity_item_count,
546 sum(decode(exception_type,2,exception_count,0)) stock_outs_count,
547
548 fnd_global.user_id, sysdate,
549 sysdate, fnd_global.user_id, fnd_global.login_id,
550 fnd_global.conc_program_id, fnd_global.conc_login_id,
551 fnd_global.prog_appl_id, fnd_global.conc_request_id
552 from
553 msc_exceptions_f me
554 where me.exception_type in (5,2)
555 and me.plan_id=p_plan_id
556 and me.plan_run_id=p_plan_run_id
557 and me.aggr_type=0
558 and p_plan_id <> -1
559 and l_plan_type not in (5)
560 group by
561 me.sr_instance_id,
562 me.organization_id,
563 me.owning_inst_id,
564 me.owning_org_id,
565 me.inventory_item_id,
566 me.analysis_date;
567
568 msc_phub_util.log('insert into msc_st_item_orders_f:exception: '||sql%rowcount);
569 commit;
570
574 plan_id,
571 -- msc_item_orders_f:final
572 msc_phub_util.gather_table_stats(msc_phub_util.stats_st, 'MSC_ST_ITEM_ORDERS_F', l_transfer_id);
573 insert into msc_item_orders_f (
575 plan_run_id,
576 sr_instance_id,
577 organization_id,
578 subinventory_code,
579 owning_inst_id,
580 owning_org_id,
581 inventory_item_id,
582 order_date,
583 io_plan_flag,
584 aggr_type,
585 category_set_id,
586 sr_category_id,
587 demand_qty,
588 pegged_to_excess_qty,
589 supply_qty,
590 planned_order_qty,
591 indep_demand_qty,
592 dep_demand_qty,
593 sales_order_value,
594 sales_order_value2,
595 return_order_value,
596 make_order_qty,
597 make_order_leadtime,
598 make_order_count,
599 stock_outs_count,
600 no_activity_item_count,
601 item_leadtime,
602 indep_demand_value,
603 scheduled_rept_qty,
604 onhand_qty,
605 forecast_qty,
606 onhand_usable,
607 intransit_usable,
608 plnd_xfer_usable,
609 onhand_defective,
610 intransit_defective,
611 plnd_xfer_defective,
612 dmd_pgd_to_schd_recp,
613 dmd_pgd_to_plnd_order,
614 dmd_pgd_to_onhand,
615 sup_end_pgd_to_fcst,
616 sup_end_pgd_to_so,
617 sup_end_pgd_to_ss,
618 sup_end_pgd_to_excess,
619 dmd_pgd_to_schd_recp_value,
620 dmd_pgd_to_plnd_order_value,
621 dmd_pgd_to_onhand_value,
622 sup_end_pgd_to_fcst_value,
623 sup_end_pgd_to_so_value,
624 sup_end_pgd_to_ss_value,
625 sup_end_pgd_to_excess_value,
626 dmd_pgd_to_schd_recp_value2,
627 dmd_pgd_to_plnd_order_value2,
628 dmd_pgd_to_onhand_value2,
629 sup_end_pgd_to_fcst_value2,
630 sup_end_pgd_to_so_value2,
631 sup_end_pgd_to_ss_value2,
632 sup_end_pgd_to_excess_value2,
633 supply_qty_usable,
634 supply_qty_defective,
635 onhand_value,
636 onhand_value2,
637 scheduled_rept_value,
638 scheduled_rept_value2,
639 created_by, creation_date,
640 last_update_date, last_updated_by, last_update_login,
641 program_id, program_login_id,
642 program_application_id, request_id)
643 select
644 p_plan_id,
645 p_plan_run_id,
646 f.sr_instance_id,
647 f.organization_id,
648 f.subinventory_code,
649 f.owning_inst_id,
650 f.owning_org_id,
651 f.inventory_item_id,
652 f.order_date,
653 decode(l_plan_type, 4, 1, 0) io_plan_flag,
654 to_number(0) aggr_type,
655 to_number(-23453) category_set_id,
656 to_number(-23453) sr_category_id,
657 f.demand_qty,
658 f.sup_end_pgd_to_excess pegged_to_excess_qty,
659 f.supply_qty,
660 f.planned_order_qty,
661 f.indep_demand_qty,
662 f.dep_demand_qty,
663 f.sales_order_value,
664 f.sales_order_value *
665 decode(f.currency_code, fnd_profile.value('MSC_HUB_CUR_CODE_RPT'), 1, nvl(mcc.conv_rate, 0)) sales_order_value2,
666 f.return_order_value,
667 f.make_order_qty,
668 f.make_order_leadtime,
669 f.make_order_count,
670 f.stock_outs_count,
671 f.no_activity_item_count,
672 f.item_leadtime,
673 f.indep_demand_value,
674 f.scheduled_rept_qty,
675 f.onhand_qty,
676 f.forecast_qty,
677 f.onhand_usable,
678 f.intransit_usable,
679 f.plnd_xfer_usable,
680 f.onhand_defective,
681 f.intransit_defective,
682 f.plnd_xfer_defective,
683 f.dmd_pgd_to_schd_recp,
684 f.dmd_pgd_to_plnd_order,
685 f.dmd_pgd_to_onhand,
686 f.sup_end_pgd_to_fcst,
687 f.sup_end_pgd_to_so,
688 f.sup_end_pgd_to_ss,
689 f.sup_end_pgd_to_excess,
690 f.dmd_pgd_to_schd_recp_value,
691 f.dmd_pgd_to_plnd_order_value,
692 f.dmd_pgd_to_onhand_value,
693 f.sup_end_pgd_to_fcst_value,
694 f.sup_end_pgd_to_so_value,
695 f.sup_end_pgd_to_ss_value,
696 f.sup_end_pgd_to_excess_value,
697 f.dmd_pgd_to_schd_recp_value *
698 decode(f.currency_code, fnd_profile.value('MSC_HUB_CUR_CODE_RPT'), 1, nvl(mcc.conv_rate, 0)) dmd_pgd_to_schd_recp_value2,
699 f.dmd_pgd_to_plnd_order_value *
700 decode(f.currency_code, fnd_profile.value('MSC_HUB_CUR_CODE_RPT'), 1, nvl(mcc.conv_rate, 0)) dmd_pgd_to_plnd_order_value2,
701 f.dmd_pgd_to_onhand_value *
702 decode(f.currency_code, fnd_profile.value('MSC_HUB_CUR_CODE_RPT'), 1, nvl(mcc.conv_rate, 0)) dmd_pgd_to_onhand_value2,
703 f.sup_end_pgd_to_fcst_value *
704 decode(f.currency_code, fnd_profile.value('MSC_HUB_CUR_CODE_RPT'), 1, nvl(mcc.conv_rate, 0)) sup_end_pgd_to_fcst_value2,
705 f.sup_end_pgd_to_so_value *
709 f.sup_end_pgd_to_excess_value *
706 decode(f.currency_code, fnd_profile.value('MSC_HUB_CUR_CODE_RPT'), 1, nvl(mcc.conv_rate, 0)) sup_end_pgd_to_so_value2,
707 f.sup_end_pgd_to_ss_value *
708 decode(f.currency_code, fnd_profile.value('MSC_HUB_CUR_CODE_RPT'), 1, nvl(mcc.conv_rate, 0)) sup_end_pgd_to_ss_value2,
710 decode(f.currency_code, fnd_profile.value('MSC_HUB_CUR_CODE_RPT'), 1, nvl(mcc.conv_rate, 0)) sup_end_pgd_to_excess_value2,
711 f.supply_qty_usable,
712 f.supply_qty_defective,
713 f.onhand_value,
714 f.onhand_value *
715 decode(f.currency_code, fnd_profile.value('MSC_HUB_CUR_CODE_RPT'), 1, nvl(mcc.conv_rate, 0)) onhand_value2,
716 f.scheduled_rept_value,
717 f.scheduled_rept_value *
718 decode(f.currency_code, fnd_profile.value('MSC_HUB_CUR_CODE_RPT'), 1, nvl(mcc.conv_rate, 0)) scheduled_rept_value2,
719 fnd_global.user_id, sysdate,
720 sysdate, fnd_global.user_id, fnd_global.login_id,
721 fnd_global.conc_program_id, fnd_global.conc_login_id,
722 fnd_global.prog_appl_id, fnd_global.conc_request_id
723 from
724 (select
725 f.sr_instance_id,
726 f.organization_id,
727 f.subinventory_code,
728 f.owning_inst_id,
729 f.owning_org_id,
730 f.inventory_item_id,
731 f.order_date,
732 decode(l_plan_type, 6, l_owning_currency_code, nvl(mtp.currency_code, l_owning_currency_code)) currency_code,
733 sum(f.demand_qty) demand_qty,
734 sum(f.supply_qty) supply_qty,
735 sum(f.planned_order_qty) planned_order_qty,
736 sum(f.indep_demand_qty) indep_demand_qty,
737 sum(f.dep_demand_qty) dep_demand_qty,
738 sum(f.sales_order_value) sales_order_value,
739 sum(f.return_order_value) return_order_value,
740 sum(f.make_order_qty) make_order_qty,
741 sum(f.make_order_leadtime) make_order_leadtime,
742 sum(f.make_order_count) make_order_count,
743 sum(f.stock_outs_count) stock_outs_count,
744 sum(f.no_activity_item_count) no_activity_item_count,
745 sum(f.item_leadtime) item_leadtime,
746 sum(f.indep_demand_value) indep_demand_value,
747 sum(f.scheduled_rept_qty) scheduled_rept_qty,
748 sum(f.onhand_qty) onhand_qty,
749 sum(f.forecast_qty) forecast_qty,
750 sum(f.onhand_usable) onhand_usable,
751 sum(f.intransit_usable) intransit_usable,
752 sum(f.plnd_xfer_usable) plnd_xfer_usable,
753 sum(f.onhand_defective) onhand_defective,
754 sum(f.intransit_defective) intransit_defective,
755 sum(f.plnd_xfer_defective) plnd_xfer_defective,
756 sum(f.dmd_pgd_to_schd_recp) dmd_pgd_to_schd_recp,
757 sum(f.dmd_pgd_to_plnd_order) dmd_pgd_to_plnd_order,
758 sum(f.dmd_pgd_to_onhand) dmd_pgd_to_onhand,
759 sum(f.sup_end_pgd_to_fcst) sup_end_pgd_to_fcst,
760 sum(f.sup_end_pgd_to_so) sup_end_pgd_to_so,
761 sum(f.sup_end_pgd_to_ss) sup_end_pgd_to_ss,
762 sum(f.sup_end_pgd_to_excess) sup_end_pgd_to_excess,
763 sum(f.dmd_pgd_to_schd_recp_value) dmd_pgd_to_schd_recp_value,
764 sum(f.dmd_pgd_to_plnd_order_value) dmd_pgd_to_plnd_order_value,
765 sum(f.dmd_pgd_to_onhand_value) dmd_pgd_to_onhand_value,
766 sum(f.sup_end_pgd_to_fcst_value) sup_end_pgd_to_fcst_value,
767 sum(f.sup_end_pgd_to_so_value) sup_end_pgd_to_so_value,
768 sum(f.sup_end_pgd_to_ss_value) sup_end_pgd_to_ss_value,
769 sum(f.sup_end_pgd_to_excess_value) sup_end_pgd_to_excess_value,
770 sum(f.supply_qty_usable) supply_qty_usable,
771 sum(f.supply_qty_defective) supply_qty_defective,
772 sum(f.onhand_value) onhand_value,
773 sum(f.scheduled_rept_value) scheduled_rept_value
774 from
775 msc_st_item_orders_f f,
776 msc_trading_partners mtp
777 where f.st_transaction_id=l_transfer_id
778 and mtp.partner_type(+)=3
779 and f.owning_inst_id=mtp.sr_instance_id(+)
780 and f.owning_org_id=mtp.sr_tp_id(+)
781 group by
782 f.sr_instance_id,
783 f.organization_id,
784 f.subinventory_code,
785 f.owning_inst_id,
786 f.owning_org_id,
787 f.inventory_item_id,
788 f.order_date,
789 decode(l_plan_type, 6, l_owning_currency_code, nvl(mtp.currency_code, l_owning_currency_code))
790 ) f,
791 msc_currency_conv_mv mcc
792 where f.currency_code=mcc.from_currency(+)
793 and fnd_profile.value('MSC_HUB_CUR_CODE_RPT')=mcc.to_currency(+)
794 and f.order_date=mcc.calendar_date(+);
795
796 l_rowcount1 := l_rowcount1 + sql%rowcount;
797 msc_phub_util.log('insert into msc_item_orders_f:final: '||sql%rowcount);
798 commit;
799 msc_phub_util.gather_table_stats(msc_phub_util.stats_fact, 'MSC_ITEM_ORDERS_F', p_plan_run_id);
800
801 -- msc_st_item_inventory_f:pab
802 insert /*+ append nologging */ into msc_st_item_inventory_f (
803 st_transaction_id,
804 error_code,
805 sr_instance_id,
806 organization_id,
807 owning_inst_id,
808 owning_org_id,
809 inventory_item_id,
810 vmi_flag,
811 order_date,
812
813 pab_qty,
817 created_by, creation_date,
814 pab_value,
815 pab_volume,
816
818 last_update_date, last_updated_by, last_update_login,
819 program_id, program_login_id,
820 program_application_id, request_id)
821
822 select
823 l_transfer_id,
824 to_number(0),
825 f.sr_instance_id,
826 f.organization_id,
827 f.owning_inst_id,
828 f.owning_org_id,
829 f.inventory_item_id,
830 nvl(i.vmi_flag,0) vmi_flag,
831 d.date2 order_date,
832
833 sum(nvl(f.supply_qty,0) - nvl(f.demand_qty,0)) pab_qty,
834 sum((nvl(f.supply_qty,0) - nvl(f.demand_qty,0)) * nvl(i.standard_cost,0)) pab_value,
835 sum((nvl(f.supply_qty,0) - nvl(f.demand_qty,0)) * nvl(i.unit_volume,1)) pab_volume,
836
837 fnd_global.user_id, sysdate,
838 sysdate, fnd_global.user_id, fnd_global.login_id,
839 fnd_global.conc_program_id, fnd_global.conc_login_id,
840 fnd_global.prog_appl_id, fnd_global.conc_request_id
841 from
842 msc_item_orders_f f,
843 msc_apcc_item_d i,
844 msc_hub_query d
845 where f.plan_run_id=p_plan_run_id
846 and f.aggr_type=0
847 and f.plan_id=i.plan_id
848 and f.owning_inst_id=i.sr_instance_id
849 and f.owning_org_id=i.organization_id
850 and f.inventory_item_id=i.inventory_item_id
851 and d.query_id=l_qid_last_date1
852 and f.order_date <= d.date2
853 and (f.supply_qty is not null or f.demand_qty is not null)
854 group by
855 f.sr_instance_id,
856 f.organization_id,
857 f.owning_inst_id,
858 f.owning_org_id,
859 f.inventory_item_id,
860 nvl(i.vmi_flag,0),
861 d.date2;
862
863 msc_phub_util.log('insert into msc_st_item_inventory_f:pab: '||sql%rowcount);
864 commit;
865
866 -- msc_st_item_inventory_f:sno pab
867 insert /*+ append nologging */ into msc_st_item_inventory_f (
868 st_transaction_id,
869 error_code,
870 sr_instance_id,
871 organization_id,
872 owning_inst_id,
873 owning_org_id,
874 inventory_item_id,
875 vmi_flag,
876 order_date,
877
878 pab_qty,
879 pab_value,
880 pab_volume,
881 inv_build_target,
882
883 created_by, creation_date,
884 last_update_date, last_updated_by, last_update_login,
885 program_id, program_login_id,
886 program_application_id, request_id)
887
888 select
889 l_transfer_id,
890 to_number(0),
891 t.sr_instance_id,
892 t.organization_id,
893 t.sr_instance_id,
894 t.organization_id,
895 t.inventory_item_id,
896 nvl(i.vmi_flag,0) vmi_flag,
897 t.order_date,
898
899 t.pab_qty,
900 t.pab_qty * nvl(i.standard_cost, 0) pab_value,
901 t.pab_qty * nvl(i.unit_volume, 1) pab_volume,
902 t.pab_qty inv_build_target,
903
904 fnd_global.user_id, sysdate,
905 sysdate, fnd_global.user_id, fnd_global.login_id,
906 fnd_global.conc_program_id, fnd_global.conc_login_id,
907 fnd_global.prog_appl_id, fnd_global.conc_request_id
908 from
909 (select
910 mbid.plan_id,
911 mbid.sr_instance_id,
912 mbid.organization_id,
913 mbid.inventory_item_id,
914 d.date2 order_date,
915 mbid.pab pab_qty,
916 rank() over (partition by mbid.plan_id,
917 mbid.sr_instance_id, mbid.organization_id, mbid.inventory_item_id,
918 d.date2 order by mbid.detail_date desc, nvl(mbid.period_type,0) desc) rn
919 from msc_bis_inv_detail mbid, msc_hub_query d
920 where mbid.plan_id=p_plan_id
921 and l_plan_type=6
922 and d.query_id=l_qid_last_date1
923 and mbid.detail_date <= d.date2
924 and ((nvl(mbid.detail_level,0)=1 and nvl(mbid.period_type,0)=1)
925 or (nvl(mbid.detail_level,0)=0 and nvl(mbid.period_type,0)=0))
926 ) t,
927 msc_apcc_item_d i
928 where t.rn=1
929 and t.plan_id=i.plan_id
930 and t.sr_instance_id=i.sr_instance_id
931 and t.organization_id=i.organization_id
932 and t.inventory_item_id=i.inventory_item_id;
933
934 msc_phub_util.log('insert into msc_st_item_inventory_f:sno pab: '||sql%rowcount);
935 commit;
936
937 -- msc_st_item_inventory_f:safety_stock_qty
938 insert /*+ append nologging */ into msc_st_item_inventory_f (
939 st_transaction_id,
940 error_code,
941 sr_instance_id,
942 organization_id,
943 owning_inst_id,
944 owning_org_id,
945 inventory_item_id,
946 vmi_flag,
947 order_date,
948
949 safety_stock_qty,
950 safety_stock_value,
951 safety_stock_volume,
952 safety_stock_days,
953 demand_var_ss_qty,
954 sup_ltvar_ss_qty,
955 transit_ltvar_ss_qty,
956 mfg_ltvar_ss_qty,
960 last_update_date, last_updated_by, last_update_login,
957 total_unpooled_safety_stock,
958
959 created_by, creation_date,
961 program_id, program_login_id,
962 program_application_id, request_id)
963
964 select
965 l_transfer_id,
966 to_number(0),
967 t.sr_instance_id,
968 t.organization_id,
969 t.sr_instance_id,
970 t.organization_id,
971 t.inventory_item_id,
972 nvl(i.vmi_flag,0) vmi_flag,
973 t.order_date,
974
975 t.safety_stock_quantity safety_stock_qty,
976 t.safety_stock_quantity * nvl(i.standard_cost,0) safety_stock_value,
977 t.safety_stock_quantity * nvl(i.unit_volume,1) safety_stock_volume,
978 (case when l_plan_type in (4, 9) then t.achieved_days_of_supply else null end) safety_stock_days,
979
980 t.demand_var_ss_percent*t.total_unpooled_safety_stock/100 demand_var_ss_qty,
981 t.sup_ltvar_ss_percent*t.total_unpooled_safety_stock/100 sup_ltvar_ss_qty,
982 t.transit_ltvar_ss_percent*t.total_unpooled_safety_stock/100 transit_ltvar_ss_qty,
983 t.mfg_ltvar_ss_percent*t.total_unpooled_safety_stock/100 mfg_ltvar_ss_qty,
984 t.total_unpooled_safety_stock,
985
986 fnd_global.user_id, sysdate,
987 sysdate, fnd_global.user_id, fnd_global.login_id,
988 fnd_global.conc_program_id, fnd_global.conc_login_id,
989 fnd_global.prog_appl_id, fnd_global.conc_request_id
990 from
991 (select
992 f.plan_id,
993 f.sr_instance_id,
994 f.organization_id,
995 f.inventory_item_id,
996 d.date2 order_date,
997 f.safety_stock_quantity,
998 f.achieved_days_of_supply,
999 f.demand_var_ss_percent,
1000 f.sup_ltvar_ss_percent,
1001 f.transit_ltvar_ss_percent,
1002 f.mfg_ltvar_ss_percent,
1003 f.total_unpooled_safety_stock,
1004 rank() over (partition by f.plan_id,
1005 f.sr_instance_id, f.organization_id, f.inventory_item_id,
1006 d.date2 order by f.period_start_date desc) rn
1007 from msc_safety_stocks f, msc_hub_query d
1008 where f.plan_id=p_plan_id
1009 and p_plan_id <> -1
1010 and d.query_id=l_qid_last_date1
1011 and f.period_start_date <= d.date2
1012 ) t,
1013 msc_apcc_item_d i,
1014 msc_trading_partners mtp
1015 where t.rn=1
1016 and t.plan_id=i.plan_id
1017 and t.sr_instance_id=i.sr_instance_id
1018 and t.organization_id=i.organization_id
1019 and t.inventory_item_id=i.inventory_item_id
1020 and t.sr_instance_id=mtp.sr_instance_id(+)
1021 and t.organization_id=mtp.sr_tp_id(+)
1022 and mtp.partner_type(+)=3
1023 and l_plan_type not in(5);
1024
1025 msc_phub_util.log('insert into msc_st_item_inventory_f:safety_stock_qty: '||sql%rowcount);
1026 commit;
1027
1028 ------------------------------------------------------------------------------------
1029 --- in msc_inventory_level, even if it is day bucket, if it is
1030 --- not a working day, there is no row for it. in such case
1031 --- we use the previous working day's value of the no-working day bucket inventory value
1032 --- see bug 6706755
1033 --- attention: with this, we will not pick up inventory value on not working day in
1034 --- msc_inventory_level ???
1035 /*
1036
1037 here is the very trick part. for min inventory level. in msc_inventory_level table
1038 if it is not a working day, even if it is a day bucket, there is no value for the
1039 day in msc_inventory_level. however, there could be safety stock value for the day
1040 since msc_safety_stock.period_start_date is not aligned with bkt start date
1041 so for min inventory level (day bucket, non working day), we first get min_quantity
1042 from msc_inventory_level for the previous working day and if specified, use it
1043 otherwise, get the ss qty for the bucket day
1044 */
1045
1046 /*
1047 min_inventory_level, max_inventory_level are for DRP and can be ignored for now.
1048 To preserve old code without affecting performance, we'll only populate items
1049 in msc_inventory_levels. To populate remaining items from i.min_minmax_quantity,
1050 we may add another union later when we support DRP.
1051 */
1052 ---------------------------------------------------------------------------------------
1053 -- msc_st_item_inventory_f:min_inventory_level
1054 insert /*+ append nologging */ into msc_st_item_inventory_f (
1055 st_transaction_id,
1056 error_code,
1057 sr_instance_id,
1058 organization_id,
1059 owning_inst_id,
1060 owning_org_id,
1061 inventory_item_id,
1062 vmi_flag,
1063 order_date,
1064
1065 min_inventory_level,
1066 max_inventory_level, -- measures populated for DRP
1067 max_inventory_level_dos, -- new measures populated for DRP
1068 target_inventory_level, -- new measures populated for DRP
1069 target_inventory_level_dos, -- new measures populated for DRP
1070
1071 created_by, creation_date,
1072 last_update_date, last_updated_by, last_update_login,
1073 program_id, program_login_id,
1074 program_application_id, request_id)
1075
1076 select
1077 l_transfer_id,
1081 t.sr_instance_id,
1078 to_number(0),
1079 t.sr_instance_id,
1080 t.organization_id,
1082 t.organization_id,
1083 t.inventory_item_id,
1084 nvl(i.vmi_flag,0) vmi_flag,
1085 t.order_date,
1086
1087 decode(i.safety_stock_code, 2, nvl(t.min_quantity, i.min_minmax_quantity), null) min_inventory_level, --Bug 9858214
1088 nvl(t.max_quantity, i.max_minmax_quantity) max_inventory_level,
1089 nvl(t.max_quantity_dos, 0) max_inventory_level_dos,
1090 nvl(t.target_quantity, 0) target_inventory_level,
1091 nvl(t.target_quantity_dos, 0) target_inventory_level_dos,
1092
1093 fnd_global.user_id, sysdate,
1094 sysdate, fnd_global.user_id, fnd_global.login_id,
1095 fnd_global.conc_program_id, fnd_global.conc_login_id,
1096 fnd_global.prog_appl_id, fnd_global.conc_request_id
1097 from
1098 (select
1099 f.plan_id,
1100 f.sr_instance_id,
1101 f.organization_id,
1102 f.inventory_item_id,
1103 d.date2 order_date,
1104 f.min_quantity,
1105 f.max_quantity,
1106 f.max_quantity_dos,
1107 f.target_quantity,
1108 f.target_quantity_dos,
1109 rank() over (partition by f.plan_id,
1110 f.sr_instance_id, f.organization_id, f.inventory_item_id,
1111 d.date2 order by f.inventory_date desc) rn
1112 from msc_inventory_levels f, msc_hub_query d
1113 where f.plan_id=p_plan_id
1114 and p_plan_id <> -1
1115 and d.query_id=l_qid_last_date1
1116 and f.inventory_date <= d.date2
1117 ) t,
1118 msc_apcc_item_d i,
1119 msc_trading_partners mtp
1120 where t.rn=1
1121 and t.plan_id=i.plan_id
1122 and t.sr_instance_id=i.sr_instance_id
1123 and t.organization_id=i.organization_id
1124 and t.inventory_item_id=i.inventory_item_id
1125 and t.sr_instance_id=mtp.sr_instance_id(+)
1126 and t.organization_id=mtp.sr_tp_id(+)
1127 and mtp.partner_type(+)=3;
1128
1129 msc_phub_util.log('insert into msc_st_item_inventory_f:min_inventory_level: '||sql%rowcount);
1130 commit;
1131
1132 -- msc_st_item_inventory_f:inventory_value
1133 -- bnaghi :not calculated for DRP plans
1134 insert /*+ append nologging */ into msc_st_item_inventory_f (
1135 st_transaction_id,
1136 error_code,
1137 sr_instance_id,
1138 organization_id,
1139 owning_inst_id,
1140 owning_org_id,
1141 inventory_item_id,
1142 vmi_flag,
1143 order_date,
1144
1145 inventory_cost_post,
1146 inventory_cost_no_post,
1147 inventory_value_post,
1148 inventory_value_no_post,
1149 inventory_value,
1150
1151 created_by, creation_date,
1152 last_update_date, last_updated_by, last_update_login,
1153 program_id, program_login_id,
1154 program_application_id, request_id)
1155
1156 select
1157 l_transfer_id,
1158 to_number(0),
1159 mbid.sr_instance_id,
1160 mbid.organization_id,
1161 mbid.sr_instance_id,
1162 mbid.organization_id,
1163 mbid.inventory_item_id,
1164 nvl(i.vmi_flag,0) vmi_flag,
1165 d.mfg_week_end_date order_date,
1166
1167 sum(inventory_cost_post) inventory_cost_post,
1168 sum(inventory_cost_no_post) inventory_cost_no_post,
1169 avg(inventory_value_post) inventory_value_post,
1170 avg(inventory_value_no_post) inventory_value_no_post,
1171 avg(inventory_value) inventory_value,
1172
1173 fnd_global.user_id, sysdate,
1174 sysdate, fnd_global.user_id, fnd_global.login_id,
1175 fnd_global.conc_program_id, fnd_global.conc_login_id,
1176 fnd_global.prog_appl_id, fnd_global.conc_request_id
1177 from
1178 msc_bis_inv_detail mbid,
1179 msc_phub_dates_mv d,
1180 msc_apcc_item_d i
1181 where trunc(mbid.detail_date)=d.calendar_date
1182 and mbid.plan_id=p_plan_id
1183 and mbid.plan_id=i.plan_id
1184 and mbid.sr_instance_id=i.sr_instance_id
1185 and mbid.organization_id=i.organization_id
1186 and mbid.inventory_item_id=i.inventory_item_id
1187 and nvl(mbid.detail_level,0)=1
1188 and nvl(mbid.period_type,0)=1
1189 and l_plan_type=4
1190 group by
1191 mbid.sr_instance_id,
1192 mbid.organization_id,
1193 mbid.inventory_item_id,
1194 nvl(i.vmi_flag,0),
1195 d.mfg_week_end_date;
1196
1197 msc_phub_util.log('insert into msc_st_item_inventory_f:inventory_value: '||sql%rowcount);
1198 commit;
1199
1200
1201
1202
1203 if ((l_enforce_wh_cpty = 1 or nvl(fnd_profile.value('MSR_ENABLE_CYCLE_STOCK_ADJUSTMENT'), 'N') = 'Y')
1204 and l_plan_type = 4) then
1205 -- msc_st_item_inventory_f:warehouse_req
1206 insert /*+ append nologging */ into msc_st_item_inventory_f (
1207 st_transaction_id,
1208 error_code,
1209 sr_instance_id,
1210 organization_id,
1211 owning_inst_id,
1212 owning_org_id,
1213 inventory_item_id,
1214 vmi_flag,
1218 avg_cycle_stock,
1215 order_date,
1216
1217 wh_required_capacity,
1219 avg_cycle_stock_volume,
1220 iqi_value,
1221 iqi_volume,
1222 avg_daily_demand,
1223 avg_daily_demand_volume,
1224
1225 created_by, creation_date,
1226 last_update_date, last_updated_by, last_update_login,
1227 program_id, program_login_id,
1228 program_application_id, request_id)
1229
1230 select
1231 l_transfer_id,
1232 to_number(0),
1233 mwr.sr_instance_id,
1234 mwr.organization_id,
1235 mwr.sr_instance_id,
1236 mwr.organization_id,
1237 mwr.inventory_item_id,
1238 nvl(i.vmi_flag,0) vmi_flag,
1239 d.mfg_week_end_date order_date,
1240
1241 sum(decode(l_enforce_wh_cpty, 1, mwr.required_capacity, null)) wh_required_capacity,
1242 sum(mwr.avg_cycle_stock) avg_cycle_stock,
1243 sum(mwr.avg_cycle_stock * nvl(i.unit_volume,1)) avg_cycle_stock_volume,
1244 sum(decode(l_enforce_wh_cpty, 1, mwr.iqi_value, null)) iqi_value,
1245 sum(decode(l_enforce_wh_cpty, 1, mwr.iqi_value, null) *
1246 nvl(i.unit_volume,1)) iqi_volume,
1247 sum(mwr.avg_daily_demand) avg_daily_demand,
1248 sum(mwr.avg_daily_demand * nvl(i.unit_volume,1)) avg_daily_demand_volume,
1249
1250 fnd_global.user_id, sysdate,
1251 sysdate, fnd_global.user_id, fnd_global.login_id,
1252 fnd_global.conc_program_id, fnd_global.conc_login_id,
1253 fnd_global.prog_appl_id, fnd_global.conc_request_id
1254 from
1255 msc_whse_requirements mwr,
1256 msc_phub_dates_mv d,
1257 msc_apcc_item_d i
1258 where trunc(mwr.stock_date)=d.calendar_date
1259 and mwr.plan_id=p_plan_id
1260 and mwr.plan_id=i.plan_id
1261 and mwr.sr_instance_id=i.sr_instance_id
1262 and mwr.organization_id=i.organization_id
1263 and mwr.inventory_item_id=i.inventory_item_id
1264 and l_plan_type=4
1265 group by
1266 mwr.sr_instance_id,
1267 mwr.organization_id,
1268 mwr.inventory_item_id,
1269 nvl(i.vmi_flag,0),
1270 d.mfg_week_end_date;
1271
1272 msc_phub_util.log('insert into msc_st_item_inventory_f:warehouse_req: '||sql%rowcount);
1273 commit;
1274 else
1275 -- msc_st_item_inventory_f:avg_daily_demand
1276 insert /*+ append nologging */ into msc_st_item_inventory_f (
1277 st_transaction_id,
1278 error_code,
1279 sr_instance_id,
1280 organization_id,
1281 owning_inst_id,
1282 owning_org_id,
1283 inventory_item_id,
1284 vmi_flag,
1285 order_date,
1286
1287 avg_daily_demand,
1288 avg_daily_demand_volume,
1289
1290 created_by, creation_date,
1291 last_update_date, last_updated_by, last_update_login,
1292 program_id, program_login_id,
1293 program_application_id, request_id)
1294
1295 select
1296 l_transfer_id,
1297 to_number(0),
1298 f.sr_instance_id,
1299 f.organization_id,
1300 f.sr_instance_id,
1301 f.organization_id,
1302 f.inventory_item_id,
1303 nvl(i.vmi_flag,0) vmi_flag,
1304 d.mfg_week_end_date order_date,
1305 sum(nvl(f.demand_qty,0)) / l_plan_days avg_daily_demand,
1306 sum(nvl(f.demand_qty,0) * nvl(i.unit_volume,1)) / l_plan_days avg_daily_demand,
1307 fnd_global.user_id, sysdate,
1308 sysdate, fnd_global.user_id, fnd_global.login_id,
1309 fnd_global.conc_program_id, fnd_global.conc_login_id,
1310 fnd_global.prog_appl_id, fnd_global.conc_request_id
1311 from
1312 msc_demands_f f,
1313 msc_phub_dates_mv d,
1314 msc_apcc_item_d i
1315 where trunc(f.order_date)=d.calendar_date
1316 and f.plan_run_id=p_plan_run_id
1317 and f.aggr_type=0
1318 and f.plan_id=i.plan_id
1319 and f.sr_instance_id=i.sr_instance_id
1320 and f.organization_id=i.organization_id
1321 and f.inventory_item_id=i.inventory_item_id
1322 group by
1323 f.sr_instance_id,
1324 f.organization_id,
1325 f.inventory_item_id,
1326 nvl(i.vmi_flag,0),
1327 d.mfg_week_end_date;
1328
1329 msc_phub_util.log('insert into msc_st_item_inventory_f:avg_daily_demand: '||sql%rowcount);
1330 commit;
1331 end if;
1332
1333 -- msc_item_inventory_f:final
1334 msc_phub_util.gather_table_stats(msc_phub_util.stats_st, 'MSC_ST_ITEM_INVENTORY_F', l_transfer_id);
1335 msc_phub_util.unusuable_local_index('MSC_ITEM_INVENTORY_F', p_plan_run_id, 1);
1336 insert into msc_item_inventory_f (
1337 plan_id,
1338 plan_run_id,
1339 sr_instance_id,
1340 organization_id,
1341 owning_inst_id,
1342 owning_org_id,
1343 inventory_item_id,
1344 vmi_flag,
1345 order_date,
1349 sr_category_id,
1346 io_plan_flag,
1347 aggr_type,
1348 category_set_id,
1350
1351 pab_qty,
1352 pab_value,
1353 pab_value2,
1354 pab_volume,
1355 safety_stock_qty,
1356 min_inventory_level,
1357 max_inventory_level,
1358 max_inventory_level_dos, -- new measures populated for DRP
1359 target_inventory_level, -- new measures populated for DRP
1360 target_inventory_level_dos, -- new measures populated for DRP
1361 inv_build_target,
1362 safety_stock_value,
1363 safety_stock_value2,
1364 safety_stock_volume,
1365 safety_stock_days,
1366 inventory_cost_post,
1367 inventory_cost_post2,
1368 inventory_cost_no_post,
1369 inventory_cost_no_post2,
1370 inventory_value_post,
1371 inventory_value_post2,
1372 inventory_value_no_post,
1373 inventory_value_no_post2,
1374 demand_var_ss_qty,
1375 sup_ltvar_ss_qty,
1376 transit_ltvar_ss_qty,
1377 mfg_ltvar_ss_qty,
1378 total_unpooled_safety_stock,
1379 inventory_value,
1380 inventory_value2,
1381 wh_required_capacity,
1382 avg_cycle_stock,
1383 avg_cycle_stock_volume,
1384 iqi_value,
1385 iqi_volume,
1386 avg_daily_demand,
1387 avg_daily_demand_volume,
1388
1389 created_by, creation_date,
1390 last_update_date, last_updated_by, last_update_login,
1391 program_id, program_login_id,
1392 program_application_id, request_id)
1393 select
1394 p_plan_id,
1395 p_plan_run_id,
1396 f.sr_instance_id,
1397 f.organization_id,
1398 f.owning_inst_id,
1399 f.owning_org_id,
1400 f.inventory_item_id,
1401 f.vmi_flag,
1402 f.order_date,
1403 decode(l_plan_type, 4, 1, 0) io_plan_flag,
1404 to_number(0) aggr_type,
1405 to_number(-23453) category_set_id,
1406 to_number(-23453) sr_category_id,
1407
1408 f.pab_qty,
1409 f.pab_value,
1410 f.pab_value * decode(f.currency_code, fnd_profile.value('MSC_HUB_CUR_CODE_RPT'), 1, nvl(mcc.conv_rate, 0)) pab_value2,
1411 f.pab_volume,
1412 f.safety_stock_qty,
1413 f.min_inventory_level,
1414 f.max_inventory_level,
1415 f.max_inventory_level_dos, -- new measures populated for DRP
1416 f.target_inventory_level, -- new measures populated for DRP
1417 f.target_inventory_level_dos, -- new measures populated for DRP
1418 f.inv_build_target,
1419 f.safety_stock_value,
1420 f.safety_stock_value * decode(f.currency_code, fnd_profile.value('MSC_HUB_CUR_CODE_RPT'), 1, nvl(mcc.conv_rate, 0)) safety_stock_value2,
1421 f.safety_stock_volume,
1422 f.safety_stock_days,
1423 f.inventory_cost_post,
1424 f.inventory_cost_post * decode(f.currency_code, fnd_profile.value('MSC_HUB_CUR_CODE_RPT'), 1, nvl(mcc.conv_rate, 0)) inventory_cost_post2,
1425 f.inventory_cost_no_post,
1426 f.inventory_cost_no_post * decode(f.currency_code, fnd_profile.value('MSC_HUB_CUR_CODE_RPT'), 1, nvl(mcc.conv_rate, 0)) inventory_cost_no_post2,
1427 f.inventory_value_post,
1428 f.inventory_value_post * decode(f.currency_code, fnd_profile.value('MSC_HUB_CUR_CODE_RPT'), 1, nvl(mcc.conv_rate, 0)) inventory_value_post2,
1429 f.inventory_value_no_post,
1430 f.inventory_value_no_post * decode(f.currency_code, fnd_profile.value('MSC_HUB_CUR_CODE_RPT'), 1, nvl(mcc.conv_rate, 0)) inventory_value_no_post2,
1431 f.demand_var_ss_qty,
1432 f.sup_ltvar_ss_qty,
1433 f.transit_ltvar_ss_qty,
1434 f.mfg_ltvar_ss_qty,
1435 f.total_unpooled_safety_stock,
1436 f.inventory_value,
1437 f.inventory_value * decode(f.currency_code, fnd_profile.value('MSC_HUB_CUR_CODE_RPT'), 1, nvl(mcc.conv_rate, 0)) inventory_value2,
1438 f.wh_required_capacity,
1439 f.avg_cycle_stock,
1440 f.avg_cycle_stock_volume,
1441 f.iqi_value,
1442 f.iqi_volume,
1443 f.avg_daily_demand,
1444 f.avg_daily_demand_volume,
1445
1446 fnd_global.user_id, sysdate,
1447 sysdate, fnd_global.user_id, fnd_global.login_id,
1448 fnd_global.conc_program_id, fnd_global.conc_login_id,
1449 fnd_global.prog_appl_id, fnd_global.conc_request_id
1450 from
1451 (select
1452 f.sr_instance_id,
1453 f.organization_id,
1454 f.owning_inst_id,
1455 f.owning_org_id,
1456 f.inventory_item_id,
1457 f.vmi_flag,
1458 i.safety_stock_code,
1459 f.order_date,
1460 decode(l_plan_type, 6, l_owning_currency_code, nvl(mtp.currency_code, l_owning_currency_code)) currency_code,
1461 sum(nvl(f.pab_qty,0)) pab_qty,
1462 sum(nvl(f.pab_value,0)) pab_value,
1463 sum(nvl(f.pab_volume,0)) pab_volume,
1464 sum(nvl(f.safety_stock_qty,0)) safety_stock_qty,
1465 sum(nvl(f.safety_stock_volume,0)) safety_stock_volume,
1466 decode(i.safety_stock_code, 2,
1467 nvl(sum(f.min_inventory_level), sum(f.safety_stock_qty)), null) min_inventory_level, --Bug 9858214
1468 sum(f.max_inventory_level) max_inventory_level,
1472 sum(f.inv_build_target) inv_build_target,
1469 sum(f.max_inventory_level_dos) max_inventory_level_dos, -- new measures populated for DRP
1470 sum(f.target_inventory_level) target_inventory_level, -- new measures populated for DRP
1471 sum(f.target_inventory_level_dos) target_inventory_level_dos, -- new measures populated for DRP
1473 sum(nvl(f.safety_stock_value,0)) safety_stock_value,
1474 (case when l_plan_type in (4, 9) then sum(f.safety_stock_days) else null end) safety_stock_days,
1475 sum(f.inventory_cost_post) inventory_cost_post,
1476 sum(f.inventory_cost_no_post) inventory_cost_no_post,
1477 avg(f.inventory_value_post) inventory_value_post,
1478 avg(f.inventory_value_no_post) inventory_value_no_post,
1479 sum(f.demand_var_ss_qty) demand_var_ss_qty,
1480 sum(f.sup_ltvar_ss_qty) sup_ltvar_ss_qty,
1481 sum(f.transit_ltvar_ss_qty) transit_ltvar_ss_qty,
1482 sum(f.mfg_ltvar_ss_qty) mfg_ltvar_ss_qty,
1483 sum(f.total_unpooled_safety_stock) total_unpooled_safety_stock,
1484 avg(f.inventory_value) inventory_value,
1485 sum(f.wh_required_capacity) wh_required_capacity,
1486 sum(f.avg_cycle_stock) avg_cycle_stock,
1487 sum(f.avg_cycle_stock_volume) avg_cycle_stock_volume,
1488 sum(f.iqi_value) iqi_value,
1489 sum(f.iqi_volume) iqi_volume,
1490 sum(f.avg_daily_demand) avg_daily_demand,
1491 sum(f.avg_daily_demand_volume) avg_daily_demand_volume
1492 from
1493 msc_st_item_inventory_f f,
1494 msc_trading_partners mtp,
1495 msc_apcc_item_d i
1496 where f.st_transaction_id=l_transfer_id
1497 and mtp.partner_type(+)=3
1498 and f.owning_inst_id=mtp.sr_instance_id(+)
1499 and f.owning_org_id=mtp.sr_tp_id(+)
1500 and i.plan_id=p_plan_id
1501 and f.owning_inst_id=i.sr_instance_id
1502 and f.owning_org_id=i.organization_id
1503 and f.inventory_item_id=i.inventory_item_id
1504 group by
1505 f.sr_instance_id,
1506 f.organization_id,
1507 f.owning_inst_id,
1508 f.owning_org_id,
1509 f.inventory_item_id,
1510 f.vmi_flag,
1511 i.safety_stock_code,
1512 f.order_date,
1513 decode(l_plan_type, 6, l_owning_currency_code, nvl(mtp.currency_code, l_owning_currency_code))
1514 ) f,
1515 msc_currency_conv_mv mcc
1516 where f.currency_code=mcc.from_currency(+)
1517 and fnd_profile.value('MSC_HUB_CUR_CODE_RPT')=mcc.to_currency(+)
1518 and f.order_date=mcc.calendar_date(+);
1519
1520 l_rowcount2 := l_rowcount2 + sql%rowcount;
1521 msc_phub_util.log('insert into msc_item_inventory_f:final: '||sql%rowcount);
1522 commit;
1523 msc_phub_util.unusuable_local_index('MSC_ITEM_INVENTORY_F', p_plan_run_id, 2);
1524 msc_phub_util.gather_table_stats(msc_phub_util.stats_fact, 'MSC_ITEM_INVENTORY_F', p_plan_run_id);
1525
1526 if (l_rowcount1 > 0) then
1527 summarize_item_orders_f(errbuf, retcode, p_plan_id, p_plan_run_id);
1528 end if;
1529
1530 if (l_rowcount2 > 0) then
1531 summarize_item_inventory_f(errbuf, retcode, p_plan_id, p_plan_run_id);
1532 end if;
1533
1534 msc_phub_util.log('msc_item_pkg.populate_details.complete, duration='||(systimestamp-l_start_time));
1535
1536 exception
1537 when others then
1538 msc_phub_util.log('msc_item_pkg.populate_details: '||sqlerrm);
1539 raise;
1540
1541 end populate_details;
1542
1543
1544 procedure summarize_item_inventory_f(errbuf out nocopy varchar2, retcode out nocopy varchar2,
1545 p_plan_id number, p_plan_run_id number)
1546 is
1547 l_category_set_id1 number := fnd_profile.value('MSC_HUB_CAT_SET_ID_1');
1548 begin
1549 msc_phub_util.log('msc_item_pkg.summarize_item_inventory_f');
1550 retcode := 0;
1551 errbuf := '';
1552
1553 delete from msc_item_inventory_f
1554 where plan_id=p_plan_id and plan_run_id=p_plan_run_id and aggr_type>0;
1555 msc_phub_util.log('msc_item_pkg.summarize_item_inventory_f, delete='||sql%rowcount);
1556 commit;
1557
1558 -- level 1
1559 insert into msc_item_inventory_f (
1560 plan_id, plan_run_id, io_plan_flag,
1561 sr_instance_id, organization_id, owning_inst_id, owning_org_id,
1562 inventory_item_id,
1563 vmi_flag, order_date,
1564 aggr_type, category_set_id, sr_category_id,
1565 pab_qty,
1566 pab_value,
1567 pab_value2,
1568 pab_volume,
1569 safety_stock_qty,
1570 safety_stock_value,
1571 safety_stock_value2,
1572 safety_stock_volume,
1573 safety_stock_days,
1574 demand_var_ss_qty,
1575 sup_ltvar_ss_qty,
1576 transit_ltvar_ss_qty,
1577 mfg_ltvar_ss_qty,
1578 total_unpooled_safety_stock,
1579 min_inventory_level,
1580 max_inventory_level,
1581 max_inventory_level_dos, -- new measures populated for DRP
1582 target_inventory_level, -- new measures populated for DRP
1583 target_inventory_level_dos, -- new measures populated for DRP
1584 inv_build_target,
1585 inventory_cost_post,
1586 inventory_cost_no_post,
1587 inventory_value_post,
1588 inventory_value_no_post,
1592 inventory_value_post2,
1589 inventory_value,
1590 inventory_cost_post2,
1591 inventory_cost_no_post2,
1593 inventory_value_no_post2,
1594 inventory_value2,
1595 wh_required_capacity,
1596 avg_cycle_stock,
1597 avg_cycle_stock_volume,
1598 iqi_value,
1599 iqi_volume,
1600 avg_daily_demand,
1601 avg_daily_demand_volume,
1602 created_by, creation_date,
1603 last_update_date, last_updated_by, last_update_login,
1604 program_id, program_login_id,
1605 program_application_id, request_id)
1606 -- category (42, 43, 44)
1607 select
1608 f.plan_id, f.plan_run_id, f.io_plan_flag,
1609 f.sr_instance_id, f.organization_id, f.owning_inst_id, f.owning_org_id,
1610 to_number(-23453) inventory_item_id,
1611 f.vmi_flag, f.order_date,
1612 to_number(42) aggr_type,
1613 l_category_set_id1 category_set_id,
1614 nvl(q.sr_category_id, -23453),
1615 sum(f.pab_qty),
1616 sum(f.pab_value),
1617 sum(f.pab_value2),
1618 sum(f.pab_volume),
1619 sum(f.safety_stock_qty),
1620 sum(f.safety_stock_value),
1621 sum(f.safety_stock_value2),
1622 sum(f.safety_stock_volume),
1623 sum(f.safety_stock_days),
1624 sum(f.demand_var_ss_qty),
1625 sum(f.sup_ltvar_ss_qty),
1626 sum(f.transit_ltvar_ss_qty),
1627 sum(f.mfg_ltvar_ss_qty),
1628 sum(f.total_unpooled_safety_stock),
1629 sum(f.min_inventory_level),
1630 sum(f.max_inventory_level),
1631 sum(f.max_inventory_level_dos), -- new measures populated for DRP
1632 sum(f.target_inventory_level), -- new measures populated for DRP
1633 sum(f.target_inventory_level_dos), -- new measures populated for DRP
1634 sum(f.inv_build_target),
1635 sum(f.inventory_cost_post),
1636 sum(f.inventory_cost_no_post),
1637 sum(f.inventory_value_post),
1638 sum(f.inventory_value_no_post),
1639 sum(f.inventory_value),
1640 sum(f.inventory_cost_post2),
1641 sum(f.inventory_cost_no_post2),
1642 sum(f.inventory_value_post2),
1643 sum(f.inventory_value_no_post2),
1644 sum(f.inventory_value2),
1645 sum(f.wh_required_capacity),
1646 sum(f.avg_cycle_stock),
1647 sum(f.avg_cycle_stock_volume),
1648 sum(f.iqi_value),
1649 sum(f.iqi_volume),
1650 sum(f.avg_daily_demand),
1651 sum(f.avg_daily_demand_volume),
1652 fnd_global.user_id, sysdate,
1653 sysdate, fnd_global.user_id, fnd_global.login_id,
1654 fnd_global.conc_program_id, fnd_global.conc_login_id,
1655 fnd_global.prog_appl_id, fnd_global.conc_request_id
1656 from
1657 msc_item_inventory_f f,
1658 msc_phub_item_categories_mv q
1659 where f.plan_id=p_plan_id and f.plan_run_id=p_plan_run_id
1660 and f.aggr_type=0
1661 and f.owning_inst_id=q.sr_instance_id(+)
1662 and f.owning_org_id=q.organization_id(+)
1663 and f.inventory_item_id=q.inventory_item_id(+)
1664 and q.category_set_id(+)=l_category_set_id1
1665 group by
1666 f.plan_id, f.plan_run_id, f.io_plan_flag,
1667 f.sr_instance_id, f.organization_id, f.owning_inst_id, f.owning_org_id,
1668 f.vmi_flag, f.order_date,
1669 nvl(q.sr_category_id, -23453);
1670
1671 msc_phub_util.log('msc_item_pkg.summarize_item_inventory_f, level1='||sql%rowcount);
1672 commit;
1673
1674 exception
1675 when others then
1676 retcode := 2;
1677 errbuf := 'msc_item_pkg.summarize_item_inventory_f: '||sqlerrm;
1678 raise;
1679 end summarize_item_inventory_f;
1680
1681
1682 procedure summarize_item_orders_f(errbuf out nocopy varchar2, retcode out nocopy varchar2,
1683 p_plan_id number, p_plan_run_id number)
1684 is
1685 l_category_set_id1 number := fnd_profile.value('MSC_HUB_CAT_SET_ID_1');
1686 begin
1687 msc_phub_util.log('msc_item_pkg.summarize_item_orders_f');
1688 retcode := 0;
1689 errbuf := '';
1690
1691 delete from msc_item_orders_f
1692 where plan_id=p_plan_id and plan_run_id=p_plan_run_id and aggr_type>0;
1693 msc_phub_util.log('msc_item_pkg.summarize_item_orders_f, delete='||sql%rowcount);
1694 commit;
1695
1696 -- level 1
1697 insert into msc_item_orders_f (
1698 plan_id, plan_run_id, io_plan_flag,
1699 sr_instance_id, organization_id, subinventory_code,
1700 owning_inst_id, owning_org_id,
1701 inventory_item_id,
1702 order_date,
1703 aggr_type, category_set_id, sr_category_id,
1704 demand_qty,
1705 supply_qty,
1706 pegged_to_excess_qty,
1707 planned_order_qty,
1708 indep_demand_qty,
1709 indep_demand_value,
1710 dep_demand_qty,
1711 sales_order_value,
1712 sales_order_value2,
1713 return_order_value,
1714 make_order_qty,
1715 make_order_leadtime,
1716 make_order_count,
1717 stock_outs_count,
1718 no_activity_item_count,
1719 item_leadtime,
1720 onhand_qty,
1721 onhand_value,
1722 onhand_value2,
1723 onhand_usable,
1727 intransit_defective,
1724 intransit_usable,
1725 plnd_xfer_usable,
1726 onhand_defective,
1728 plnd_xfer_defective,
1729 supply_qty_usable,
1730 supply_qty_defective,
1731 scheduled_rept_qty,
1732 scheduled_rept_value,
1733 scheduled_rept_value2,
1734 forecast_qty,
1735 sup_end_pgd_to_fcst,
1736 sup_end_pgd_to_so,
1737 sup_end_pgd_to_ss,
1738 sup_end_pgd_to_excess,
1739 dmd_pgd_to_schd_recp,
1740 dmd_pgd_to_plnd_order,
1741 dmd_pgd_to_onhand,
1742 sup_end_pgd_to_fcst_value,
1743 sup_end_pgd_to_so_value,
1744 sup_end_pgd_to_ss_value,
1745 sup_end_pgd_to_excess_value,
1746 dmd_pgd_to_schd_recp_value,
1747 dmd_pgd_to_plnd_order_value,
1748 dmd_pgd_to_onhand_value,
1749 sup_end_pgd_to_fcst_value2,
1750 sup_end_pgd_to_so_value2,
1751 sup_end_pgd_to_ss_value2,
1752 sup_end_pgd_to_excess_value2,
1753 dmd_pgd_to_schd_recp_value2,
1754 dmd_pgd_to_plnd_order_value2,
1755 dmd_pgd_to_onhand_value2,
1756 created_by, creation_date,
1757 last_update_date, last_updated_by, last_update_login,
1758 program_id, program_login_id,
1759 program_application_id, request_id)
1760 -- category (42, 43, 44)
1761 select
1762 f.plan_id, f.plan_run_id, f.io_plan_flag,
1763 f.sr_instance_id, f.organization_id, f.subinventory_code,
1764 f.owning_inst_id, f.owning_org_id,
1765 to_number(-23453) inventory_item_id,
1766 f.order_date,
1767 to_number(42) aggr_type,
1768 l_category_set_id1 category_set_id,
1769 nvl(q.sr_category_id, -23453),
1770 sum(f.demand_qty),
1771 sum(f.supply_qty),
1772 sum(f.pegged_to_excess_qty ),
1773 sum(f.planned_order_qty),
1774 sum(f.indep_demand_qty),
1775 sum(f.indep_demand_value),
1776 sum(f.dep_demand_qty),
1777 sum(f.sales_order_value),
1778 sum(f.sales_order_value2),
1779 sum(f.return_order_value),
1780 sum(f.make_order_qty),
1781 sum(f.make_order_leadtime),
1782 sum(f.make_order_count),
1783 sum(f.stock_outs_count),
1784 sum(f.no_activity_item_count),
1785 sum(f.item_leadtime),
1786 sum(f.onhand_qty),
1787 sum(f.onhand_value),
1788 sum(f.onhand_value2),
1789 sum(f.onhand_usable),
1790 sum(f.intransit_usable),
1791 sum(f.plnd_xfer_usable),
1792 sum(f.onhand_defective),
1793 sum(f.intransit_defective),
1794 sum(f.plnd_xfer_defective),
1795 sum(f.supply_qty_usable),
1796 sum(f.supply_qty_defective),
1797 sum(f.scheduled_rept_qty),
1798 sum(f.scheduled_rept_value),
1799 sum(f.scheduled_rept_value2),
1800 sum(f.forecast_qty),
1801 sum(f.sup_end_pgd_to_fcst),
1802 sum(f.sup_end_pgd_to_so),
1803 sum(f.sup_end_pgd_to_ss),
1804 sum(f.sup_end_pgd_to_excess),
1805 sum(f.dmd_pgd_to_schd_recp),
1806 sum(f.dmd_pgd_to_plnd_order),
1807 sum(f.dmd_pgd_to_onhand),
1808 sum(f.sup_end_pgd_to_fcst_value),
1809 sum(f.sup_end_pgd_to_so_value),
1810 sum(f.sup_end_pgd_to_ss_value),
1811 sum(f.sup_end_pgd_to_excess_value),
1812 sum(f.dmd_pgd_to_schd_recp_value),
1813 sum(f.dmd_pgd_to_plnd_order_value),
1814 sum(f.dmd_pgd_to_onhand_value),
1815 sum(f.sup_end_pgd_to_fcst_value2),
1816 sum(f.sup_end_pgd_to_so_value2),
1817 sum(f.sup_end_pgd_to_ss_value2),
1818 sum(f.sup_end_pgd_to_excess_value2),
1819 sum(f.dmd_pgd_to_schd_recp_value2),
1820 sum(f.dmd_pgd_to_plnd_order_value2),
1821 sum(f.dmd_pgd_to_onhand_value2),
1822 fnd_global.user_id, sysdate,
1823 sysdate, fnd_global.user_id, fnd_global.login_id,
1824 fnd_global.conc_program_id, fnd_global.conc_login_id,
1825 fnd_global.prog_appl_id, fnd_global.conc_request_id
1826 from
1827 msc_item_orders_f f,
1828 msc_phub_item_categories_mv q
1829 where f.plan_id=p_plan_id and f.plan_run_id=p_plan_run_id
1830 and f.aggr_type=0
1831 and f.owning_inst_id=q.sr_instance_id(+)
1832 and f.owning_org_id=q.organization_id(+)
1833 and f.inventory_item_id=q.inventory_item_id(+)
1834 and q.category_set_id(+)=l_category_set_id1
1835 group by
1836 f.plan_id, f.plan_run_id, f.io_plan_flag,
1837 f.sr_instance_id, f.organization_id, f.subinventory_code,
1838 f.owning_inst_id, f.owning_org_id,
1839 f.order_date,
1840 nvl(q.sr_category_id, -23453);
1841
1842 msc_phub_util.log('msc_item_pkg.summarize_item_orders_f, level1='||sql%rowcount);
1843 commit;
1844
1845 exception
1846 when others then
1847 retcode := 2;
1848 errbuf := 'msc_item_pkg.summarize_item_orders_f: '||sqlerrm;
1849 raise;
1850 end summarize_item_orders_f;
1851
1852 procedure export_item_inventory_f (
1853 errbuf out nocopy varchar2, retcode out nocopy varchar2,
1854 p_st_transaction_id number, p_plan_run_id number,
1855 p_dblink varchar2, p_source_version varchar2)
1856 is
1857 l_sql varchar2(5000);
1861 msc_phub_util.log('msc_item_pkg.export_item_inventory_f');
1858 l_suffix varchar2(32) := msc_phub_util.suffix(p_dblink);
1859 l_apps_schema varchar2(30) := msc_phub_util.apps_schema;
1860 begin
1862 retcode := 0;
1863 errbuf := null;
1864
1865 delete from msc_st_item_inventory_f where st_transaction_id=p_st_transaction_id;
1866 commit;
1867
1868 l_sql :=
1869 ' insert into msc_st_item_inventory_f('||
1870 ' st_transaction_id,'||
1871 ' error_code,'||
1872 ' sr_instance_id,'||
1873 ' organization_id,'||
1874 ' owning_inst_id,'||
1875 ' owning_org_id,'||
1876 ' inventory_item_id,'||
1877 ' organization_code,'||
1878 ' owning_org_code,'||
1879 ' item_name,'||
1880 ' vmi_flag,'||
1881 ' order_date,'||
1882 ' pab_qty,'||
1883 ' pab_value,'||
1884 ' pab_value2,'||
1885 ' safety_stock_qty,'||
1886 ' min_inventory_level,'||
1887 ' max_inventory_level,'||
1888 ' inv_build_target,';
1889 if (p_source_version >= '12.1.3') then l_sql := l_sql||
1890 ' safety_stock_value,'||
1891 ' safety_stock_value2,'||
1892 ' safety_stock_volume,'||
1893 ' safety_stock_days,'||
1894 ' demand_var_ss_qty,'||
1895 ' sup_ltvar_ss_qty,'||
1896 ' transit_ltvar_ss_qty,'||
1897 ' mfg_ltvar_ss_qty,'||
1898 ' total_unpooled_safety_stock,'||
1899 ' inventory_cost_post,'||
1900 ' inventory_cost_no_post,'||
1901 ' inventory_value_post,'||
1902 ' inventory_value_no_post,'||
1903 ' inventory_value,'||
1904 ' inventory_cost_post2,'||
1905 ' inventory_cost_no_post2,'||
1906 ' inventory_value_post2,'||
1907 ' inventory_value_no_post2,'||
1908 ' inventory_value2,' ||
1909 ' max_inventory_level_dos,'|| -- new measures populated for DRP
1910 ' target_inventory_level,'|| -- new measures populated for DRP
1911 ' target_inventory_level_dos,'|| -- new measures populated for DRP
1912 ' wh_required_capacity,'||
1913 ' avg_cycle_stock,'||
1914 ' avg_cycle_stock_volume,'||
1915 ' iqi_value,'||
1916 ' iqi_volume,'||
1917 ' avg_daily_demand,'||
1918 ' avg_daily_demand_volume,'||
1919 ' pab_volume,';
1920 end if;
1921 l_sql := l_sql||
1922 ' created_by, creation_date,'||
1923 ' last_updated_by, last_update_date, last_update_login'||
1924 ' )'||
1925 ' select'||
1926 ' :p_st_transaction_id,'||
1927 ' 0,'||
1928 ' f.sr_instance_id,'||
1929 ' f.organization_id,'||
1930 ' f.owning_inst_id,'||
1931 ' f.owning_org_id,'||
1932 ' f.inventory_item_id,'||
1933 ' mtp.organization_code,'||
1934 ' mtp2.organization_code,'||
1935 ' mi.item_name,'||
1936 ' f.vmi_flag,'||
1937 ' f.order_date,'||
1938 ' f.pab_qty,'||
1939 ' f.pab_value,'||
1940 ' f.pab_value2,'||
1941 ' f.safety_stock_qty,'||
1942 ' f.min_inventory_level,'||
1943 ' f.max_inventory_level,'||
1944 ' f.inv_build_target,';
1945 if (p_source_version >= '12.1.3') then l_sql := l_sql||
1946 ' f.safety_stock_value,'||
1947 ' f.safety_stock_value2,'||
1948 ' f.safety_stock_volume,'||
1949 ' f.safety_stock_days,'||
1950 ' f.demand_var_ss_qty,'||
1951 ' f.sup_ltvar_ss_qty,'||
1952 ' f.transit_ltvar_ss_qty,'||
1953 ' f.mfg_ltvar_ss_qty,'||
1954 ' f.total_unpooled_safety_stock,'||
1955 ' f.inventory_cost_post,'||
1956 ' f.inventory_cost_no_post,'||
1957 ' f.inventory_value_post,'||
1958 ' f.inventory_value_no_post,'||
1959 ' f.inventory_value,'||
1960 ' f.inventory_cost_post2,'||
1961 ' f.inventory_cost_no_post2,'||
1962 ' f.inventory_value_post2,'||
1963 ' f.inventory_value_no_post2,'||
1964 ' f.inventory_value2,'||
1965 ' f.max_inventory_level_dos,'|| -- new measures populated for DRP
1966 ' f.target_inventory_level,'|| -- new measures populated for DRP
1967 ' f.target_inventory_level_dos,'|| -- new measures populated for DRP
1968 ' f.wh_required_capacity,'||
1969 ' f.avg_cycle_stock,'||
1970 ' f.avg_cycle_stock_volume,'||
1971 ' f.iqi_value,'||
1972 ' f.iqi_volume,'||
1973 ' f.avg_daily_demand,'||
1974 ' f.avg_daily_demand_volume,'||
1975 ' f.pab_volume,';
1976 end if;
1977 l_sql := l_sql||
1978 ' fnd_global.user_id, sysdate,'||
1979 ' fnd_global.user_id, sysdate, fnd_global.login_id'||
1980 ' from'||
1981 ' '||l_apps_schema||'.msc_item_inventory_f'||l_suffix||' f,'||
1982 ' '||l_apps_schema||'.msc_trading_partners'||l_suffix||' mtp,'||
1983 ' '||l_apps_schema||'.msc_trading_partners'||l_suffix||' mtp2,'||
1987 ' and mtp.partner_type(+)=3'||
1984 ' '||l_apps_schema||'.msc_items'||l_suffix||' mi'||
1985 ' where f.plan_run_id=:p_plan_run_id'||
1986 ' and f.aggr_type=0'||
1988 ' and mtp.sr_instance_id(+)=f.sr_instance_id'||
1989 ' and mtp.sr_tp_id(+)=f.organization_id'||
1990 ' and mtp2.partner_type(+)=3'||
1991 ' and mtp2.sr_instance_id(+)=f.owning_inst_id'||
1992 ' and mtp2.sr_tp_id(+)=f.owning_org_id'||
1993 ' and mi.inventory_item_id(+)=f.inventory_item_id';
1994
1995 execute immediate l_sql using p_st_transaction_id, p_plan_run_id;
1996 msc_phub_util.log('msc_item_pkg.export_item_inventory_f: inserted='||sql%rowcount);
1997 commit;
1998 msc_phub_util.log('msc_item_pkg.export_item_inventory_f: complete, retcode='||retcode);
1999
2000 exception
2001 when others then
2002 retcode := 2;
2003 errbuf := 'msc_item_pkg.export_item_inventory_f: '||sqlerrm;
2004 msc_phub_util.log(errbuf);
2005 end export_item_inventory_f;
2006
2007 procedure export_item_orders_f (
2008 errbuf out nocopy varchar2, retcode out nocopy varchar2,
2009 p_st_transaction_id number, p_plan_run_id number,
2010 p_dblink varchar2, p_source_version varchar2)
2011 is
2012 l_sql varchar2(5000);
2013 l_suffix varchar2(32) := msc_phub_util.suffix(p_dblink);
2014 l_apps_schema varchar2(30) := msc_phub_util.apps_schema;
2015 begin
2016 msc_phub_util.log('msc_item_pkg.export_item_orders_f');
2017 retcode := 0;
2018 errbuf := null;
2019
2020 delete from msc_st_item_orders_f where st_transaction_id=p_st_transaction_id;
2021 commit;
2022
2023 l_sql :=
2024 ' insert into msc_st_item_orders_f('||
2025 ' st_transaction_id,'||
2026 ' error_code,'||
2027 ' sr_instance_id,'||
2028 ' organization_id,'||
2029 ' owning_inst_id,'||
2030 ' owning_org_id,'||
2031 ' inventory_item_id,'||
2032 ' organization_code,'||
2033 ' owning_org_code,'||
2034 ' item_name,';
2035 if (p_source_version >= '12.1.3') then l_sql := l_sql||
2036 ' subinventory_code,';
2037 end if;
2038 l_sql := l_sql||
2039 ' order_date,'||
2040 ' demand_qty,'||
2041 ' pegged_to_excess_qty,'||
2042 ' supply_qty,'||
2043 ' planned_order_qty,'||
2044 ' indep_demand_qty,'||
2045 ' dep_demand_qty,'||
2046 ' sales_order_value,'||
2047 ' return_order_value,'||
2048 ' make_order_qty,'||
2049 ' make_order_leadtime,'||
2050 ' make_order_count,'||
2051 ' stock_outs_count,'||
2052 ' no_activity_item_count,'||
2053 ' item_leadtime,'||
2054 ' indep_demand_value,'||
2055 ' scheduled_rept_qty,'||
2056 ' onhand_qty,'||
2057 ' forecast_qty,';
2058 if (p_source_version >= '12.1.3') then l_sql := l_sql||
2059 ' onhand_usable,'||
2060 ' intransit_usable,'||
2061 ' plnd_xfer_usable,'||
2062 ' onhand_defective,'||
2063 ' intransit_defective,'||
2064 ' plnd_xfer_defective,'||
2065 ' sup_end_pgd_to_fcst,'||
2066 ' sup_end_pgd_to_so,'||
2067 ' sup_end_pgd_to_ss,'||
2068 ' sup_end_pgd_to_excess,'||
2069 ' dmd_pgd_to_schd_recp,'||
2070 ' dmd_pgd_to_plnd_order,'||
2071 ' dmd_pgd_to_onhand,'||
2072 ' sup_end_pgd_to_fcst_value,'||
2073 ' sup_end_pgd_to_so_value,'||
2074 ' sup_end_pgd_to_ss_value,'||
2075 ' sup_end_pgd_to_excess_value,'||
2076 ' dmd_pgd_to_schd_recp_value,'||
2077 ' dmd_pgd_to_plnd_order_value,'||
2078 ' dmd_pgd_to_onhand_value,'||
2079 ' sup_end_pgd_to_fcst_value2,'||
2080 ' sup_end_pgd_to_so_value2,'||
2081 ' sup_end_pgd_to_ss_value2,'||
2082 ' sup_end_pgd_to_excess_value2,'||
2083 ' dmd_pgd_to_schd_recp_value2,'||
2084 ' dmd_pgd_to_plnd_order_value2,'||
2085 ' dmd_pgd_to_onhand_value2,'||
2086 ' supply_qty_usable,'||
2087 ' supply_qty_defective,'||
2088 ' sales_order_value2,'||
2089 ' onhand_value,'||
2090 ' onhand_value2,'||
2091 ' scheduled_rept_value,'||
2092 ' scheduled_rept_value2,';
2093 end if;
2094 l_sql := l_sql||
2095 ' created_by, creation_date,'||
2096 ' last_updated_by, last_update_date, last_update_login'||
2097 ' )'||
2098 ' select'||
2099 ' :p_st_transaction_id,'||
2100 ' 0,'||
2101 ' f.sr_instance_id,'||
2102 ' f.organization_id,'||
2103 ' f.owning_inst_id,'||
2104 ' f.owning_org_id,'||
2105 ' f.inventory_item_id,'||
2106 ' mtp.organization_code,'||
2107 ' mtp2.organization_code,'||
2108 ' mi.item_name,';
2109 if (p_source_version >= '12.1.3') then l_sql := l_sql||
2110 ' f.subinventory_code,';
2111 end if;
2112 l_sql := l_sql||
2113 ' f.order_date,'||
2117 ' f.planned_order_qty,'||
2114 ' f.demand_qty,'||
2115 ' f.pegged_to_excess_qty,'||
2116 ' f.supply_qty,'||
2118 ' f.indep_demand_qty,'||
2119 ' f.dep_demand_qty,'||
2120 ' f.sales_order_value,'||
2121 ' f.return_order_value,'||
2122 ' f.make_order_qty,'||
2123 ' f.make_order_leadtime,'||
2124 ' f.make_order_count,'||
2125 ' f.stock_outs_count,'||
2126 ' f.no_activity_item_count,'||
2127 ' f.item_leadtime,'||
2128 ' f.indep_demand_value,'||
2129 ' f.scheduled_rept_qty,'||
2130 ' f.onhand_qty,'||
2131 ' f.forecast_qty,';
2132 if (p_source_version >= '12.1.3') then l_sql := l_sql||
2133 ' f.onhand_usable,'||
2134 ' f.intransit_usable,'||
2135 ' f.plnd_xfer_usable,'||
2136 ' f.onhand_defective,'||
2137 ' f.intransit_defective,'||
2138 ' f.plnd_xfer_defective,'||
2139 ' f.sup_end_pgd_to_fcst,'||
2140 ' f.sup_end_pgd_to_so,'||
2141 ' f.sup_end_pgd_to_ss,'||
2142 ' f.sup_end_pgd_to_excess,'||
2143 ' f.dmd_pgd_to_schd_recp,'||
2144 ' f.dmd_pgd_to_plnd_order,'||
2145 ' f.dmd_pgd_to_onhand,'||
2146 ' f.sup_end_pgd_to_fcst_value,'||
2147 ' f.sup_end_pgd_to_so_value,'||
2148 ' f.sup_end_pgd_to_ss_value,'||
2149 ' f.sup_end_pgd_to_excess_value,'||
2150 ' f.dmd_pgd_to_schd_recp_value,'||
2151 ' f.dmd_pgd_to_plnd_order_value,'||
2152 ' f.dmd_pgd_to_onhand_value,'||
2153 ' f.sup_end_pgd_to_fcst_value2,'||
2154 ' f.sup_end_pgd_to_so_value2,'||
2155 ' f.sup_end_pgd_to_ss_value2,'||
2156 ' f.sup_end_pgd_to_excess_value2,'||
2157 ' f.dmd_pgd_to_schd_recp_value2,'||
2158 ' f.dmd_pgd_to_plnd_order_value2,'||
2159 ' f.dmd_pgd_to_onhand_value2,'||
2160 ' f.supply_qty_usable,'||
2161 ' f.supply_qty_defective,'||
2162 ' f.sales_order_value2,'||
2163 ' f.onhand_value,'||
2164 ' f.onhand_value2,'||
2165 ' f.scheduled_rept_value,'||
2166 ' f.scheduled_rept_value2,';
2167 end if;
2168 l_sql := l_sql||
2169 ' fnd_global.user_id, sysdate,'||
2170 ' fnd_global.user_id, sysdate, fnd_global.login_id'||
2171 ' from'||
2172 ' '||l_apps_schema||'.msc_item_orders_f'||l_suffix||' f,'||
2173 ' '||l_apps_schema||'.msc_trading_partners'||l_suffix||' mtp,'||
2174 ' '||l_apps_schema||'.msc_trading_partners'||l_suffix||' mtp2,'||
2175 ' '||l_apps_schema||'.msc_items'||l_suffix||' mi'||
2176 ' where f.plan_run_id=:p_plan_run_id'||
2177 ' and f.aggr_type=0'||
2178 ' and mtp.partner_type(+)=3'||
2179 ' and mtp.sr_instance_id(+)=f.sr_instance_id'||
2180 ' and mtp.sr_tp_id(+)=f.organization_id'||
2181 ' and mtp2.partner_type(+)=3'||
2182 ' and mtp2.sr_instance_id(+)=f.owning_inst_id'||
2183 ' and mtp2.sr_tp_id(+)=f.owning_org_id'||
2184 ' and mi.inventory_item_id(+)=f.inventory_item_id';
2185
2186 execute immediate l_sql using p_st_transaction_id, p_plan_run_id;
2187 msc_phub_util.log('msc_item_pkg.export_item_orders_f: inserted='||sql%rowcount);
2188 commit;
2189 msc_phub_util.log('msc_item_pkg.export_item_orders_f: complete, retcode='||retcode);
2190
2191 exception
2192 when others then
2193 retcode := 2;
2194 errbuf := 'msc_item_pkg.export_item_orders_f: '||sqlerrm;
2195 msc_phub_util.log(errbuf);
2196 end export_item_orders_f;
2197
2198 procedure import_item_inventory_f (
2199 errbuf out nocopy varchar2, retcode out nocopy varchar2,
2200 p_st_transaction_id number, p_plan_id number, p_plan_run_id number,
2201 p_plan_type number, p_plan_start_date date, p_plan_cutoff_date date,
2202 p_upload_mode number, p_overwrite_after_date date, p_def_instance_code varchar2)
2203 is
2204 l_staging_table varchar2(30) := 'msc_st_item_inventory_f';
2205 l_fact_table varchar2(30) := 'msc_item_inventory_f';
2206 l_result number := 0;
2207 begin
2208 msc_phub_util.log('msc_item_pkg.import_item_inventory_f');
2209 retcode := 0;
2210 errbuf := null;
2211
2212 l_result := l_result + msc_phub_util.prepare_staging_dates(
2213 l_staging_table, 'order_date', p_st_transaction_id,
2214 p_upload_mode, p_overwrite_after_date,
2215 p_plan_start_date, p_plan_cutoff_date);
2216
2217 l_result := l_result + msc_phub_util.prepare_fact_dates(
2218 l_fact_table, 1, 'order_date', p_plan_id, p_plan_run_id,
2219 p_upload_mode, p_overwrite_after_date);
2220
2221 l_result := l_result + msc_phub_util.decode_organization_key(
2222 l_staging_table, p_st_transaction_id, p_def_instance_code,
2223 'sr_instance_id', 'organization_id', 'organization_code');
2224
2225 l_result := l_result + msc_phub_util.decode_organization_key(
2226 l_staging_table, p_st_transaction_id, p_def_instance_code,
2227 'owning_inst_id', 'owning_org_id', 'owning_org_code');
2228
2229 l_result := l_result + msc_phub_util.decode_item_key(
2230 l_staging_table, p_st_transaction_id, 'inventory_item_id', 'item_name');
2231
2235 plan_run_id,
2232 msc_phub_util.log('msc_item_pkg.import_item_inventory_f: insert into msc_item_inventory_f');
2233 insert into msc_item_inventory_f (
2234 plan_id,
2236 sr_instance_id,
2237 organization_id,
2238 owning_inst_id,
2239 owning_org_id,
2240 inventory_item_id,
2241 io_plan_flag,
2242 vmi_flag,
2243 order_date,
2244 pab_qty,
2245 pab_value,
2246 pab_value2,
2247 pab_volume,
2248 safety_stock_qty,
2249 min_inventory_level,
2250 max_inventory_level,
2251 max_inventory_level_dos, -- new measures populated for DRP
2252 target_inventory_level, -- new measures populated for DRP
2253 target_inventory_level_dos, -- new measures populated for DRP
2254 inv_build_target,
2255 safety_stock_value,
2256 safety_stock_value2,
2257 safety_stock_volume,
2258 safety_stock_days,
2259 demand_var_ss_qty,
2260 sup_ltvar_ss_qty,
2261 transit_ltvar_ss_qty,
2262 mfg_ltvar_ss_qty,
2263 total_unpooled_safety_stock,
2264 inventory_cost_post,
2265 inventory_cost_no_post,
2266 inventory_value_post,
2267 inventory_value_no_post,
2268 inventory_value,
2269 inventory_cost_post2,
2270 inventory_cost_no_post2,
2271 inventory_value_post2,
2272 inventory_value_no_post2,
2273 inventory_value2,
2274 wh_required_capacity,
2275 avg_cycle_stock,
2276 avg_cycle_stock_volume,
2277 iqi_value,
2278 iqi_volume,
2279 avg_daily_demand,
2280 avg_daily_demand_volume,
2281 aggr_type, category_set_id, sr_category_id,
2282 created_by, creation_date,
2283 last_updated_by, last_update_date, last_update_login
2284 )
2285 select
2286 p_plan_id,
2287 p_plan_run_id,
2288 nvl(sr_instance_id, -23453),
2289 nvl(organization_id, -23453),
2290 nvl(owning_inst_id, -23453),
2291 nvl(owning_org_id, -23453),
2292 nvl(inventory_item_id, -23453),
2293 decode(p_plan_type, 4, 1, 0) io_plan_flag,
2294 vmi_flag,
2295 order_date,
2296 pab_qty,
2297 pab_value,
2298 pab_value2,
2299 pab_volume,
2300 safety_stock_qty,
2301 min_inventory_level,
2302 max_inventory_level,
2303 max_inventory_level_dos, -- new measures populated for DRP
2304 target_inventory_level, -- new measures populated for DRP
2305 target_inventory_level_dos, -- new measures populated for DRP
2306 inv_build_target,
2307 safety_stock_value,
2308 safety_stock_value2,
2309 safety_stock_volume,
2310 safety_stock_days,
2311 demand_var_ss_qty,
2312 sup_ltvar_ss_qty,
2313 transit_ltvar_ss_qty,
2314 mfg_ltvar_ss_qty,
2315 total_unpooled_safety_stock,
2316 inventory_cost_post,
2317 inventory_cost_no_post,
2318 inventory_value_post,
2319 inventory_value_no_post,
2320 inventory_value,
2321 inventory_cost_post2,
2322 inventory_cost_no_post2,
2323 inventory_value_post2,
2324 inventory_value_no_post2,
2325 inventory_value2,
2326 wh_required_capacity,
2327 avg_cycle_stock,
2328 avg_cycle_stock_volume,
2329 iqi_value,
2330 iqi_volume,
2331 avg_daily_demand,
2332 avg_daily_demand_volume,
2333 0, -23453, -23453,
2334 fnd_global.user_id, sysdate,
2335 fnd_global.user_id, sysdate, fnd_global.login_id
2336 from msc_st_item_inventory_f
2337 where st_transaction_id=p_st_transaction_id and error_code=0;
2338
2339 msc_phub_util.log('msc_item_pkg.import_item_inventory_f: inserted='||sql%rowcount);
2340 commit;
2341
2342 summarize_item_inventory_f(errbuf, retcode, p_plan_id, p_plan_run_id);
2343
2344 if (l_result > 0) then
2345 retcode := -1;
2346 end if;
2347
2348 msc_phub_util.log('msc_item_pkg.import_item_inventory_f: complete, retcode='||retcode);
2349
2350 exception
2351 when others then
2352 retcode := 2;
2353 errbuf := 'msc_item_pkg.import_item_inventory_f: '||sqlerrm;
2354 raise;
2355 end import_item_inventory_f;
2356
2357 procedure import_item_orders_f (
2358 errbuf out nocopy varchar2, retcode out nocopy varchar2,
2359 p_st_transaction_id number, p_plan_id number, p_plan_run_id number,
2360 p_plan_type number, p_plan_start_date date, p_plan_cutoff_date date,
2361 p_upload_mode number, p_overwrite_after_date date, p_def_instance_code varchar2)
2362 is
2363 l_staging_table varchar2(30) := 'msc_st_item_orders_f';
2364 l_fact_table varchar2(30) := 'msc_item_orders_f';
2365 l_result number := 0;
2366 begin
2367 msc_phub_util.log('msc_item_pkg.import_item_orders_f');
2368 retcode := 0;
2369 errbuf := null;
2370
2371 l_result := l_result + msc_phub_util.prepare_staging_dates(
2372 l_staging_table, 'order_date', p_st_transaction_id,
2373 p_upload_mode, p_overwrite_after_date,
2377 l_fact_table, 1, 'order_date', p_plan_id, p_plan_run_id,
2374 p_plan_start_date, p_plan_cutoff_date);
2375
2376 l_result := l_result + msc_phub_util.prepare_fact_dates(
2378 p_upload_mode, p_overwrite_after_date);
2379
2380 l_result := l_result + msc_phub_util.decode_organization_key(
2381 l_staging_table, p_st_transaction_id, p_def_instance_code,
2382 'sr_instance_id', 'organization_id', 'organization_code');
2383
2384 l_result := l_result + msc_phub_util.decode_organization_key(
2385 l_staging_table, p_st_transaction_id, p_def_instance_code,
2386 'owning_inst_id', 'owning_org_id', 'owning_org_code');
2387
2388 l_result := l_result + msc_phub_util.decode_item_key(
2389 l_staging_table, p_st_transaction_id, 'inventory_item_id', 'item_name');
2390
2391 msc_phub_util.log('msc_item_pkg.import_item_orders_f: insert into msc_st_item_orders_f');
2392 insert into msc_item_orders_f (
2393 plan_id,
2394 plan_run_id,
2395 sr_instance_id,
2396 organization_id,
2397 subinventory_code,
2398 owning_inst_id,
2399 owning_org_id,
2400 inventory_item_id,
2401 io_plan_flag,
2402 order_date,
2403 demand_qty,
2404 pegged_to_excess_qty,
2405 supply_qty,
2406 planned_order_qty,
2407 indep_demand_qty,
2408 dep_demand_qty,
2409 sales_order_value,
2410 sales_order_value2,
2411 return_order_value,
2412 make_order_qty,
2413 make_order_leadtime,
2414 make_order_count,
2415 stock_outs_count,
2416 no_activity_item_count,
2417 item_leadtime,
2418 indep_demand_value,
2419 scheduled_rept_qty,
2420 onhand_qty,
2421 forecast_qty,
2422 onhand_usable,
2423 intransit_usable,
2424 plnd_xfer_usable,
2425 onhand_defective,
2426 intransit_defective,
2427 plnd_xfer_defective,
2428 sup_end_pgd_to_fcst,
2429 sup_end_pgd_to_so,
2430 sup_end_pgd_to_ss,
2431 sup_end_pgd_to_excess,
2432 dmd_pgd_to_schd_recp,
2433 dmd_pgd_to_plnd_order,
2434 dmd_pgd_to_onhand,
2435 sup_end_pgd_to_fcst_value,
2436 sup_end_pgd_to_so_value,
2437 sup_end_pgd_to_ss_value,
2438 sup_end_pgd_to_excess_value,
2439 dmd_pgd_to_schd_recp_value,
2440 dmd_pgd_to_plnd_order_value,
2441 dmd_pgd_to_onhand_value,
2442 sup_end_pgd_to_fcst_value2,
2443 sup_end_pgd_to_so_value2,
2444 sup_end_pgd_to_ss_value2,
2445 sup_end_pgd_to_excess_value2,
2446 dmd_pgd_to_schd_recp_value2,
2447 dmd_pgd_to_plnd_order_value2,
2448 dmd_pgd_to_onhand_value2,
2449 supply_qty_usable,
2450 supply_qty_defective,
2451 onhand_value,
2452 onhand_value2,
2453 scheduled_rept_value,
2454 scheduled_rept_value2,
2455 aggr_type, category_set_id, sr_category_id,
2456 created_by, creation_date,
2457 last_updated_by, last_update_date, last_update_login
2458 )
2459 select
2460 p_plan_id,
2461 p_plan_run_id,
2462 nvl(sr_instance_id, -23453),
2463 nvl(organization_id, -23453),
2464 nvl(subinventory_code, '-23453'),
2465 nvl(owning_inst_id, -23453),
2466 nvl(owning_org_id, -23453),
2467 nvl(inventory_item_id, -23453),
2468 decode(p_plan_type, 4, 1, 0) io_plan_flag,
2469 order_date,
2470 demand_qty,
2471 pegged_to_excess_qty,
2472 supply_qty,
2473 planned_order_qty,
2474 indep_demand_qty,
2475 dep_demand_qty,
2476 sales_order_value,
2477 sales_order_value2,
2478 return_order_value,
2479 make_order_qty,
2480 make_order_leadtime,
2481 make_order_count,
2482 stock_outs_count,
2483 no_activity_item_count,
2484 item_leadtime,
2485 indep_demand_value,
2486 scheduled_rept_qty,
2487 onhand_qty,
2488 forecast_qty,
2489 onhand_usable,
2490 intransit_usable,
2491 plnd_xfer_usable,
2492 onhand_defective,
2493 intransit_defective,
2494 plnd_xfer_defective,
2495 sup_end_pgd_to_fcst,
2496 sup_end_pgd_to_so,
2497 sup_end_pgd_to_ss,
2498 sup_end_pgd_to_excess,
2499 dmd_pgd_to_schd_recp,
2500 dmd_pgd_to_plnd_order,
2501 dmd_pgd_to_onhand,
2502 sup_end_pgd_to_fcst_value,
2503 sup_end_pgd_to_so_value,
2504 sup_end_pgd_to_ss_value,
2505 sup_end_pgd_to_excess_value,
2506 dmd_pgd_to_schd_recp_value,
2507 dmd_pgd_to_plnd_order_value,
2508 dmd_pgd_to_onhand_value,
2509 sup_end_pgd_to_fcst_value2,
2510 sup_end_pgd_to_so_value2,
2511 sup_end_pgd_to_ss_value2,
2512 sup_end_pgd_to_excess_value2,
2513 dmd_pgd_to_schd_recp_value2,
2514 dmd_pgd_to_plnd_order_value2,
2515 dmd_pgd_to_onhand_value2,
2516 supply_qty_usable,
2517 supply_qty_defective,
2521 scheduled_rept_value2,
2518 onhand_value,
2519 onhand_value2,
2520 scheduled_rept_value,
2522 0, -23453, -23453,
2523 fnd_global.user_id, sysdate,
2524 fnd_global.user_id, sysdate, fnd_global.login_id
2525 from msc_st_item_orders_f
2526 where st_transaction_id=p_st_transaction_id and error_code=0;
2527
2528 msc_phub_util.log('msc_item_pkg.import_item_orders_f: inserted='||sql%rowcount);
2529 commit;
2530
2531 summarize_item_orders_f(errbuf, retcode, p_plan_id, p_plan_run_id);
2532
2533 if (l_result > 0) then
2534 retcode := -1;
2535 end if;
2536
2537 msc_phub_util.log('msc_item_pkg.import_item_orders_f: complete, retcode='||retcode);
2538
2539 exception
2540 when others then
2541 retcode := 2;
2542 errbuf := 'msc_item_pkg.import_item_orders_f: '||sqlerrm;
2543 raise;
2544 end import_item_orders_f;
2545
2546 end msc_item_pkg;