[Home] [Help]
PACKAGE BODY: APPS.MSC_PHUB_EXCESS_PKG
Source
1 package body msc_phub_excess_pkg as
2 /* $Header: MSCHBESB.pls 120.22.12020000.2 2012/10/11 13:57:33 wexia ship $ */
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_plan_start_date date;
8 l_plan_cutoff_date date;
9 l_plan_type number;
10 l_sr_instance_id number;
11 l_item_simulation_set_id number;
12 l_qid_eo number;
13 l_sim_plan_id number;
14 l_owning_currency_code varchar2(20) := msc_phub_util.get_owning_currency_code(p_plan_run_id);
15 l_start_time timestamp := systimestamp;
16 l_transfer_id number := null;
17 l_enforce_wh_cpty number := 2;
18 begin
19 msc_phub_util.log('msc_phub_excess_pkg.populate_details');
20 retcode := 0;
21 errbuf := null;
22
23
24 select plan_type, sr_instance_id, plan_start_date, plan_cutoff_date, temp_transfer_id
25 into l_plan_type, l_sr_instance_id, l_plan_start_date, l_plan_cutoff_date, l_transfer_id
26 from msc_plan_runs
27 where plan_id=p_plan_id
28 and plan_run_id=p_plan_run_id;
29
30 select nvl(enforce_wrh_cpty,2)
31 into l_enforce_wh_cpty
32 from msc_plans
33 where plan_id=p_plan_id;
34
35 msc_phub_util.log('msc_phub_excess_pkg.populate_details: '||
36 p_plan_id||','||p_plan_run_id||','||l_plan_type||','||
37 l_sr_instance_id||','||l_plan_start_date||','||l_plan_cutoff_date||','||
38 l_transfer_id||','||l_enforce_wh_cpty);
39
40 if l_plan_type in (101,102,103,105) then
41
42 begin
43 select simulation_set_id
44 into l_item_simulation_set_id
45 from msc_plans
46 where plan_id=p_plan_id;
47
48 select plan_id
49 into l_sim_plan_id
50 from msc_rp_simulation_sets
51 where simulation_set_id=l_item_simulation_set_id;
52 exception
53 when others then null;
54 end;
55
56 msc_phub_util.log('msc_phub_excess_pkg.populate_details: '||
57 'l_item_simulation_set_id='||l_item_simulation_set_id||', '||
58 'l_sim_plan_id='||l_sim_plan_id);
59
60 if l_item_simulation_set_id is not null then
61 select msc_hub_query_s.nextval into l_qid_eo from dual;
62 insert into msc_hub_query(
63 query_id,
64 last_update_date,
65 last_updated_by,
66 creation_date,
67 created_by,
68 last_update_login,
69 date1, -- OBSOLESCENCE_DATE
70 number3, -- sr_instance_id
71 number4, -- organization_id
72 number5, -- inventory_item_id
73 number7, -- excess_horizon
74 number8 -- standard_cost
75 )
76 select
77 unique l_qid_eo,
78 sysdate,
79 fnd_global.user_id,
80 sysdate,
81 fnd_global.user_id,
82 fnd_global.login_id,
83 msi.obsolescence_date,
84 msi.sr_instance_id,
85 msi.organization_id,
86 msi.inventory_item_id,
87 msi.excess_horizon,
88 msi.standard_cost
89 from msc_system_items msi
90 where msi.plan_id=l_sim_plan_id
91 and nvl(msi.simulation_set_id, -23453)=l_item_simulation_set_id;
92
93 msc_phub_util.log('l_qid_eo='||l_qid_eo||', count='||sql%rowcount);
94 commit;
95 end if;
96 else
97 if (l_plan_type=6) then
98 select fnd_profile.value('MSC_APCC_SNO_ITEM_SIMULATION_SET')
99 into l_item_simulation_set_id
100 from dual;
101 else
102 select item_simulation_set_id
103 into l_item_simulation_set_id
104 from msc_plans
105 where plan_id=p_plan_id;
106 end if;
107
108 msc_phub_util.log('msc_phub_excess_pkg.populate_details: '||
109 'l_item_simulation_set_id='||l_item_simulation_set_id);
110
111 if l_item_simulation_set_id is not null then
112 select msc_hub_query_s.nextval into l_qid_eo from dual;
113 insert into msc_hub_query(
114 query_id,
115 last_update_date,
116 last_updated_by,
117 creation_date,
118 created_by,
119 last_update_login,
120 date1, -- OBSOLESCENCE_DATE
121 number3, -- sr_instance_id
122 number4, -- organization_id
123 number5, -- inventory_item_id
124 number7, -- excess_horizon
125 number8 -- standard_cost
126 )
127 select
128 unique l_qid_eo,
129 sysdate,
130 fnd_global.user_id,
131 sysdate,
132 fnd_global.user_id,
133 fnd_global.login_id,
134 mia.obsolescence_date,
135 mia.sr_instance_id,
136 mia.organization_id,
137 mia.inventory_item_id,
138 mia.excess_horizon,
139 nvl(mia.standard_cost, msi.standard_cost)
140 from msc_item_attributes mia, msc_system_items msi
141 where mia.plan_id=-1
142 and mia.simulation_set_id=l_item_simulation_set_id
143 and msi.plan_id=p_plan_id
144 and mia.sr_instance_id=msi.sr_instance_id
145 and mia.organization_id=msi.organization_id
146 and mia.inventory_item_id=msi.inventory_item_id;
147
148 /* and (msi.obsolescence_date is not null or msi.excess_horizon is not null);*/
149 msc_phub_util.log('l_qid_eo='||l_qid_eo||', count='||sql%rowcount);
150 commit;
151 end if;
152 end if;
153
154
155 if l_item_simulation_set_id is not null then
156 -- msc_items_f:eo
157 insert /*+ append nologging */ into msc_st_items_f (
158 st_transaction_id,
159 error_code,
160 sr_instance_id,
161 organization_id,
162 owning_inst_id,
163 owning_org_id,
164 inventory_item_id,
165
166 dmd_within_obs_horizon,
167 dmd_within_excess_horizon,
168 excess_onhand,
169 excess_onorder,
170 obsolete_onhand,
171 obsolete_onorder,
172 total_excess,
173 total_obs,
174 excess_from_onhand_value,
175 excess_from_onorder_value,
176 obsolete_onhand_value,
177 obsolete_onorder_value,
178 total_excess_value,
179 total_obs_value,
180 excess_from_onhand_value2,
181 excess_from_onorder_value2,
182 obsolete_onhand_value2,
183 obsolete_onorder_value2,
184 total_excess_value2,
185 total_obs_value2,
186 created_by, creation_date,
187 last_update_date, last_updated_by, last_update_login,
188 program_id, program_login_id,
189 program_application_id, request_id)
190 select
191 l_transfer_id,
192 to_number(0),
193 sdt.sr_instance_id,
194 sdt.organization_id,
195 sdt.sr_instance_id owning_inst_id,
196 sdt.organization_id owning_org_id,
197 sdt.inventory_item_id,
198
199 sum(nvl(sdt.dmd_within_obs_horizon,0)) dmd_within_obs_horizon,
200 sum(nvl(sdt.dmd_within_excess_horizon,0)) dmd_within_excess_horizon,
201
202 greatest(sum(nvl(sdt.onhand_qty,0)-nvl(sdt.dmd_within_excess_horizon,0)), 0) excess_onhand,
203 greatest(sum(nvl(sdt.onorder_qty,0)-nvl(sdt.dmd_within_excess_horizon,0)), 0) excess_onorder,
204
205 greatest(sum(nvl(sdt.onhand_qty,0)-nvl(sdt.dmd_within_obs_horizon,0)), 0) obsolete_onhand,
206 greatest(sum(nvl(sdt.onorder_qty,0)-nvl(sdt.dmd_within_obs_horizon,0)), 0) obsolete_onorder,
207
208 greatest(sum(nvl(sdt.onorder_qty,0)-nvl(sdt.dmd_within_excess_horizon,0)), 0) +
209 greatest(sum(nvl(sdt.onhand_qty,0)-nvl(sdt.dmd_within_excess_horizon,0)), 0) total_excess,
210
211 greatest(sum(nvl(sdt.onorder_qty,0)-nvl(sdt.dmd_within_obs_horizon,0)), 0) +
212 greatest(sum(nvl(sdt.onhand_qty,0)-nvl(sdt.dmd_within_obs_horizon,0)), 0) total_obs,
213
214 greatest(sum(nvl(sdt.onhand_value,0)-nvl(sdt.dmd_within_excess_hor_value,0)), 0) excess_from_onhand_value,
215 greatest(sum(nvl(sdt.onorder_value,0)-nvl(sdt.dmd_within_excess_hor_value,0)), 0) excess_from_onorder_value,
216
217 greatest(sum(nvl(sdt.onhand_value,0)-nvl(sdt.dmd_within_obs_hor_value,0)), 0) obsolete_onhand_value,
218 greatest(sum(nvl(sdt.onorder_value,0)-nvl(sdt.dmd_within_obs_hor_value,0)), 0) obsolete_onorder_value,
219
220 greatest(sum(nvl(sdt.onhand_value,0)-nvl(sdt.dmd_within_excess_hor_value,0)), 0) +
221 greatest(sum(nvl(sdt.onorder_value,0)-nvl(sdt.dmd_within_excess_hor_value,0)), 0) total_excess_value,
222
223 greatest(sum(nvl(sdt.onhand_value,0)-nvl(sdt.dmd_within_obs_hor_value,0)), 0) +
224 greatest(sum(nvl(sdt.onorder_value,0)-nvl(sdt.dmd_within_obs_hor_value,0)), 0) total_obs_value,
225
226
227 greatest(sum((nvl(sdt.onhand_value,0)-nvl(sdt.dmd_within_excess_hor_value,0)) *
228 decode(sdt.currency_code, fnd_profile.value('MSC_HUB_CUR_CODE_RPT'), 1, nvl(mcc.conv_rate,0))), 0) excess_from_onhand_value2,
229 greatest(sum((nvl(sdt.onorder_value,0)-nvl(sdt.dmd_within_excess_hor_value,0)) *
230 decode(sdt.currency_code, fnd_profile.value('MSC_HUB_CUR_CODE_RPT'), 1, nvl(mcc.conv_rate,0))), 0) excess_from_onorder_value2,
231
232 greatest(sum((nvl(sdt.onhand_value,0)-nvl(sdt.dmd_within_obs_hor_value,0)) *
233 decode(sdt.currency_code, fnd_profile.value('MSC_HUB_CUR_CODE_RPT'), 1, nvl(mcc.conv_rate,0))), 0) obsolete_onhand_value2,
234 greatest(sum((nvl(sdt.onorder_value,0)-nvl(sdt.dmd_within_obs_hor_value,0)) *
235 decode(sdt.currency_code, fnd_profile.value('MSC_HUB_CUR_CODE_RPT'), 1, nvl(mcc.conv_rate,0))), 0) obsolete_onorder_value2,
236
237 greatest(sum((nvl(sdt.onhand_value,0)-nvl(sdt.dmd_within_excess_hor_value,0)) *
238 decode(sdt.currency_code, fnd_profile.value('MSC_HUB_CUR_CODE_RPT'), 1, nvl(mcc.conv_rate,0))), 0) +
239 greatest(sum((nvl(sdt.onorder_value,0)-nvl(sdt.dmd_within_excess_hor_value,0)) *
240 decode(sdt.currency_code, fnd_profile.value('MSC_HUB_CUR_CODE_RPT'), 1, nvl(mcc.conv_rate,0))), 0) total_excess_value2,
241
242 greatest(sum((nvl(sdt.onhand_value,0)-nvl(sdt.dmd_within_obs_hor_value,0)) *
243 decode(sdt.currency_code, fnd_profile.value('MSC_HUB_CUR_CODE_RPT'), 1, nvl(mcc.conv_rate,0))), 0) +
244 greatest(sum((nvl(sdt.onorder_value,0)-nvl(sdt.dmd_within_obs_hor_value,0)) *
245 decode(sdt.currency_code, fnd_profile.value('MSC_HUB_CUR_CODE_RPT'), 1, nvl(mcc.conv_rate,0))), 0) total_obs_value2,
246
247 fnd_global.user_id, sysdate,
248 sysdate, fnd_global.user_id, fnd_global.login_id,
249 fnd_global.conc_program_id, fnd_global.conc_login_id,
250 fnd_global.prog_appl_id, fnd_global.conc_request_id
251 from
252 (
253 select
254 msf.plan_id,
255 msf.plan_run_id,
256 msf.sr_instance_id,
257 msf.organization_id,
258 msf.inventory_item_id,
259 msf.supply_date detail_date,
260 decode(l_plan_type, 6, l_owning_currency_code, nvl(mtp.currency_code, l_owning_currency_code)) currency_code,
261 to_number(0) dmd_within_obs_horizon,
262 to_number(0) dmd_within_excess_horizon,
263 to_number(0) dmd_within_obs_hor_value,
264 to_number(0) dmd_within_excess_hor_value,
265 sum(decode(msf.supply_type,18, nvl(msf.supply_qty,0),0)) onhand_qty,
266 sum(case when msf.supply_type in (1,2,3,8,11,12,14,27,49,53,80)
267 then nvl(msf.supply_qty,0) else 0 end) onorder_qty,
268 sum(decode(msf.supply_type,18,nvl(msf.supply_qty,0),0)
269 *nvl(b.number8, nvl(i.standard_cost,0))) onhand_value,
270 sum((case when msf.supply_type in (1,2,3,8,11,12,14,27,49,53,80)
271 then nvl(msf.supply_qty,0) else 0 end)
272 *nvl(b.number8, nvl(i.standard_cost,0))) onorder_value
273 from msc_supplies_f msf,msc_trading_partners mtp, msc_hub_query b, msc_system_items i
274 where msf.plan_id=p_plan_id
275 and msf.plan_run_id=p_plan_run_id
276 and msf.aggr_type=0
277 and b.query_id(+)=l_qid_eo
278 and b.number5(+)=msf.inventory_item_id
279 and b.number3(+)=msf.sr_instance_id
280 and b.number4(+)=msf.organization_id
281 and msf.sr_instance_id(+)=mtp.sr_instance_id
282 and msf.organization_id(+)=mtp.sr_tp_id
283 and mtp.partner_type(+)=3
284 and msf.plan_id=i.plan_id(+)
285 and msf.sr_instance_id=i.sr_instance_id(+)
286 and msf.organization_id=i.organization_id(+)
287 and msf.inventory_item_id=i.inventory_item_id(+)
288 and msf.supply_type in (18,1,2,3,8,11,12,14,27,49,53,80)
289 group by
290 msf.plan_id,
291 msf.plan_run_id,
292 msf.sr_instance_id,
293 msf.organization_id,
294 msf.inventory_item_id,
295 msf.supply_date,
296 decode(l_plan_type, 6, l_owning_currency_code, nvl(mtp.currency_code, l_owning_currency_code))
297 union all
298 select
299 mdf.plan_id,
300 mdf.plan_run_id,
301 mdf.sr_instance_id,
302 mdf.organization_id,
303 mdf.inventory_item_id,
304 mdf.order_date detail_date,
305 decode(l_plan_type, 6, l_owning_currency_code, nvl(mtp.currency_code, l_owning_currency_code)) currency_code,
306 nvl((case when mdf.order_date between l_plan_start_date and nvl(b.date1, l_plan_cutoff_date)
307 then mdf.demand_qty else 0 end),0) dmd_within_obs_horizon,
308 nvl((case when mdf.order_date between l_plan_start_date and decode(b.number7, null, l_plan_cutoff_date, l_plan_start_date+b.number7)
309 then mdf.demand_qty else 0 end),0) dmd_within_excess_horizon,
310 nvl((case when mdf.order_date between l_plan_start_date and nvl(b.date1, l_plan_cutoff_date)
311 then mdf.demand_qty else 0 end),0)*nvl(b.number8,nvl(i.standard_cost,0)) dmd_within_obs_hor_value,
312 nvl((case when mdf.order_date between l_plan_start_date and decode(b.number7, null, l_plan_cutoff_date, l_plan_start_date+b.number7)
313 then mdf.demand_qty else 0 end),0)*nvl(b.number8,nvl(i.standard_cost,0)) dmd_within_excess_hor_value,
314 to_number(0) onhand_qty,
315 to_number(0) onorder_qty,
316 to_number(0) onhand_value,
317 to_number(0) onorder_value
318 from msc_demands_f mdf, msc_trading_partners mtp, msc_hub_query b, msc_system_items i
319 where mdf.plan_id=p_plan_id
320 and mdf.plan_run_id=p_plan_run_id
321 and mdf.aggr_type=0
322 and b.query_id(+)=l_qid_eo
323 and b.number5(+)=mdf.inventory_item_id
324 and b.number3(+)=mdf.sr_instance_id
325 and b.number4(+)=mdf.organization_id
326 and mdf.sr_instance_id=mtp.sr_instance_id(+)
330 and mdf.sr_instance_id=i.sr_instance_id(+)
327 and mdf.organization_id=mtp.sr_tp_id(+)
328 and mtp.partner_type(+)=3
329 and mdf.plan_id=i.plan_id(+)
331 and mdf.organization_id=i.organization_id(+)
332 and mdf.inventory_item_id=i.inventory_item_id(+)
333 ) sdt,
334 msc_currency_conv_mv mcc
335 where sdt.plan_id=p_plan_id
336 and mcc.to_currency(+)=fnd_profile.value('MSC_HUB_CUR_CODE_RPT')
337 and mcc.from_currency(+)=nvl(sdt.currency_code, l_owning_currency_code)
338 and mcc.calendar_date(+)=sdt.detail_date
339 group by
340 sdt.sr_instance_id,
341 sdt.organization_id,
342 sdt.inventory_item_id;
343
344 msc_phub_util.log('insert into msc_st_items_f:eo: '||sql%rowcount);
345 commit;
346 end if;
347
348 if (l_enforce_wh_cpty = 1 and l_plan_type = 4) then
349 if (nvl(fnd_profile.value('MSR_ORGANIZATION_WAREHOUSE_CAPACITY'),1) = 1) then
350 -- msc_items_f:wh_available_capacity
351 insert /*+ append nologging */ into msc_st_items_f (
352 st_transaction_id,
353 error_code,
354 sr_instance_id,
355 organization_id,
356 owning_inst_id,
357 owning_org_id,
358 inventory_item_id,
359 wh_available_capacity,
360 created_by, creation_date,
361 last_update_date, last_updated_by, last_update_login,
362 program_id, program_login_id,
363 program_application_id, request_id)
364 select
365 l_transfer_id,
366 to_number(0),
367 po.sr_instance_id,
368 po.organization_id,
369 to_number(-23453) owning_inst_id,
370 to_number(-23453) owning_org_id,
371 to_number(-23453) inventory_item_id,
372 mtp.maximum_volume*nvl(uc.conversion_rate,1) wh_available_capacity,
373 fnd_global.user_id, sysdate,
374 sysdate, fnd_global.user_id, fnd_global.login_id,
375 fnd_global.conc_program_id, fnd_global.conc_login_id,
376 fnd_global.prog_appl_id, fnd_global.conc_request_id
377 from
378 msc_trading_partners mtp,
379 msc_plan_organizations po,
380 msc_uom_conversions uc
381 where po.plan_id=p_plan_id
382 and po.sr_instance_id=mtp.sr_instance_id
383 and po.organization_id=mtp.sr_tp_id
384 and mtp.partner_type=3
385 and mtp.maximum_volume is not null
386 and mtp.volume_uom=uc.uom_code
387 and mtp.sr_instance_id=uc.sr_instance_id
388 and uc.inventory_item_id=0;
389 msc_phub_util.log('insert into msc_st_items_f:wh_available_capacity: '||sql%rowcount);
390 commit;
391 else
392 -- msc_items_f:wh_available_capacity_cat
393 insert /*+ append nologging */ into msc_st_items_f (
394 st_transaction_id,
395 error_code,
396 sr_instance_id,
397 organization_id,
398 owning_inst_id,
399 owning_org_id,
400 inventory_item_id,
401 wh_available_capacity,
402 created_by, creation_date,
403 last_update_date, last_updated_by, last_update_login,
404 program_id, program_login_id,
405 program_application_id, request_id)
406 select
407 l_transfer_id,
408 to_number(0),
409 mwc.sr_instance_id,
410 mwc.organization_id,
411 mwc.sr_instance_id owning_inst_id,
412 to_number(-23453) owning_org_id,
413 -mwc.sr_category_id inventory_item_id,
414 mwc.available_capacity*nvl(uc.conversion_rate,1) wh_available_capacity,
415 fnd_global.user_id, sysdate,
416 sysdate, fnd_global.user_id, fnd_global.login_id,
417 fnd_global.conc_program_id, fnd_global.conc_login_id,
418 fnd_global.prog_appl_id, fnd_global.conc_request_id
419 from
420 msc_warehouse_capacities mwc,
421 msc_plan_organizations po,
422 msc_uom_conversions uc
423 where po.plan_id=p_plan_id
424 and po.sr_instance_id=mwc.sr_instance_id
425 and po.organization_id=mwc.organization_id
426 and mwc.capacity_uom=uc.uom_code(+)
427 and mwc.sr_instance_id=uc.sr_instance_id(+)
428 and uc.inventory_item_id(+)=0;
429 msc_phub_util.log('insert into msc_st_items_f:wh_available_capacity_cat: '||sql%rowcount);
430 commit;
431 end if;
432 end if;
433
434 -- msc_items_f:final
435 insert into msc_items_f (
436 plan_id,
437 plan_run_id,
438 sr_instance_id,
439 organization_id,
440 owning_inst_id,
441 owning_org_id,
442 inventory_item_id,
443 aggr_type,
444 category_set_id,
445 sr_category_id,
446 dmd_within_obs_horizon,
447 dmd_within_excess_horizon,
451 obsolete_onhand,
448 excess_onorder,
449 excess_onhand,
450 obsolete_onorder,
452 total_excess,
453 total_obs,
454 excess_from_onhand_value,
455 excess_from_onorder_value,
456 obsolete_onhand_value,
457 obsolete_onorder_value,
458 total_excess_value,
459 total_obs_value,
460 excess_from_onhand_value2,
461 excess_from_onorder_value2,
462 obsolete_onhand_value2,
463 obsolete_onorder_value2,
464 total_excess_value2,
465 total_obs_value2,
466 wh_available_capacity,
467 created_by, creation_date,
468 last_update_date, last_updated_by, last_update_login,
469 program_id, program_login_id,
470 program_application_id, request_id)
471 select
472 p_plan_id,
473 p_plan_run_id,
474 sr_instance_id,
475 organization_id,
476 owning_inst_id,
477 owning_org_id,
478 inventory_item_id,
479 to_number(0) aggr_type,
480 to_number(-23453) category_set_id,
481 to_number(-23453) sr_category_id,
482 sum(dmd_within_obs_horizon),
483 sum(dmd_within_excess_horizon),
484 sum(excess_onorder),
485 sum(excess_onhand),
486 sum(obsolete_onorder),
487 sum(obsolete_onhand),
488 sum(total_excess),
489 sum(total_obs),
490 sum(excess_from_onhand_value),
491 sum(excess_from_onorder_value),
492 sum(obsolete_onhand_value),
493 sum(obsolete_onorder_value),
494 sum(total_excess_value),
495 sum(total_obs_value),
496 sum(excess_from_onhand_value2),
497 sum(excess_from_onorder_value2),
498 sum(obsolete_onhand_value2),
499 sum(obsolete_onorder_value2),
500 sum(total_excess_value2),
501 sum(total_obs_value2),
502 sum(wh_available_capacity),
503 fnd_global.user_id, sysdate,
504 sysdate, fnd_global.user_id, fnd_global.login_id,
505 fnd_global.conc_program_id, fnd_global.conc_login_id,
506 fnd_global.prog_appl_id, fnd_global.conc_request_id
507 from msc_st_items_f f
508 where f.st_transaction_id=l_transfer_id
509 group by
510 sr_instance_id,
511 organization_id,
512 owning_inst_id,
513 owning_org_id,
514 inventory_item_id;
515 msc_phub_util.log('insert into msc_items_f:final: '||sql%rowcount);
516 commit;
517
518 msc_phub_util.gather_table_stats(msc_phub_util.stats_fact, 'MSC_ITEMS_F', p_plan_run_id);
519 summarize_items_f(errbuf, retcode, p_plan_id, p_plan_run_id);
520 msc_phub_util.log('msc_phub_excess_pkg.populate_details.complete, duration='||(systimestamp-l_start_time));
521
522 exception
523 when others then
524 msc_phub_util.log('msc_phub_excess_pkg.populate_details.exception: '||sqlerrm);
525 errbuf := msc_phub_util.get_planning_hub_message('MSC_HUB_POPULATE_ERROR')||':'||sqlerrm;
526 retcode := 2;
527 end populate_details;
528
529 procedure summarize_items_f(errbuf out nocopy varchar2, retcode out nocopy varchar2,
530 p_plan_id number, p_plan_run_id number)
531 is
532 l_category_set_id1 number := fnd_profile.value('MSC_HUB_CAT_SET_ID_1');
533 begin
534 msc_phub_util.log('msc_phub_excess_pkg.summarize_items_f');
535 retcode := 0;
536 errbuf := '';
537
538 delete from msc_items_f
539 where plan_id=p_plan_id and plan_run_id=p_plan_run_id and aggr_type>0;
540 msc_phub_util.log('msc_phub_excess_pkg.summarize_items_f, delete='||sql%rowcount);
541 commit;
542
543 -- level 1
544 insert into msc_items_f (
545 plan_id, plan_run_id,
546 sr_instance_id, organization_id,
547 owning_inst_id, owning_org_id, inventory_item_id,
548 aggr_type, category_set_id, sr_category_id,
549 dmd_within_obs_horizon,
550 dmd_within_excess_horizon,
551 excess_onorder,
552 excess_onhand,
553 obsolete_onorder,
554 obsolete_onhand,
555 total_excess,
556 excess_from_onhand_value,
557 excess_from_onorder_value,
558 total_excess_value,
559 excess_from_onhand_value2,
560 excess_from_onorder_value2,
561 total_excess_value2,
562 total_obs,
563 obsolete_onhand_value,
564 obsolete_onorder_value,
565 total_obs_value,
566 obsolete_onhand_value2,
567 obsolete_onorder_value2,
568 total_obs_value2,
569 wh_available_capacity,
570 created_by, creation_date,
571 last_update_date, last_updated_by, last_update_login,
572 program_id, program_login_id,
573 program_application_id, request_id)
574 -- category (42, 43, 44)
575 select
576 f.plan_id, f.plan_run_id,
577 f.sr_instance_id, f.organization_id,
578 f.owning_inst_id, f.owning_org_id,
579 to_number(-23453) inventory_item_id,
580 to_number(42) aggr_type,
581 l_category_set_id1 category_set_id,
582 nvl(q.sr_category_id, -23453),
583 sum(dmd_within_obs_horizon),
587 sum(obsolete_onorder),
584 sum(dmd_within_excess_horizon),
585 sum(excess_onorder),
586 sum(excess_onhand),
588 sum(obsolete_onhand),
589 sum(total_excess),
590 sum(excess_from_onhand_value),
591 sum(excess_from_onorder_value),
592 sum(total_excess_value),
593 sum(excess_from_onhand_value2),
594 sum(excess_from_onorder_value2),
595 sum(total_excess_value2),
596 sum(total_obs),
597 sum(obsolete_onhand_value),
598 sum(obsolete_onorder_value),
599 sum(total_obs_value),
600 sum(obsolete_onhand_value2),
601 sum(obsolete_onorder_value2),
602 sum(total_obs_value2),
603 sum(wh_available_capacity),
604 fnd_global.user_id, sysdate,
605 sysdate, fnd_global.user_id, fnd_global.login_id,
606 fnd_global.conc_program_id, fnd_global.conc_login_id,
607 fnd_global.prog_appl_id, fnd_global.conc_request_id
608 from
609 msc_items_f f,
610 msc_phub_item_categories_mv q
611 where f.plan_id=p_plan_id and f.plan_run_id=p_plan_run_id
612 and f.aggr_type=0
613 and f.sr_instance_id=q.sr_instance_id(+)
614 and f.organization_id=q.organization_id(+)
615 and q.inventory_item_id(+)=decode(sign(f.inventory_item_id), 1, f.inventory_item_id, -23453)
616 and q.sr_category_id(+)=decode(sign(f.inventory_item_id), 1, q.sr_category_id(+), -f.inventory_item_id)
617 and q.category_set_id(+)=l_category_set_id1
618 group by
619 f.plan_id, f.plan_run_id,
620 f.sr_instance_id, f.organization_id,
621 f.owning_inst_id, f.owning_org_id,
622 nvl(q.sr_category_id, -23453);
623
624 msc_phub_util.log('msc_phub_excess_pkg.summarize_items_f, level1='||sql%rowcount);
625 commit;
626
627 exception
628 when others then
629 retcode := 2;
630 errbuf := 'msc_phub_excess_pkg.summarize_demands_f: '||sqlerrm;
631 raise;
632
633 end summarize_items_f;
634
635 procedure export_items_f (
636 errbuf out nocopy varchar2, retcode out nocopy varchar2,
637 p_st_transaction_id number, p_plan_run_id number,
638 p_dblink varchar2, p_source_version varchar2)
639 is
640 l_sql varchar2(5000);
641 l_suffix varchar2(32) := msc_phub_util.suffix(p_dblink);
642 l_apps_schema varchar2(30) := msc_phub_util.apps_schema;
643 begin
644 msc_phub_util.log('msc_phub_excess_pkg.export_items_f');
645 retcode := 0;
646 errbuf := null;
647
648 delete from msc_st_items_f where st_transaction_id=p_st_transaction_id;
649 commit;
650
651 if (p_source_version = '11.5.10') then
652 l_sql :=
653 ' insert into msc_st_items_f('||
654 ' st_transaction_id,'||
655 ' error_code,'||
656 ' sr_instance_id,'||
657 ' organization_id,'||
658 ' inventory_item_id,'||
659 ' organization_code,'||
660 ' item_name,'||
661 ' created_by, creation_date,'||
662 ' last_updated_by, last_update_date, last_update_login'||
663 ' )'||
664 ' select'||
665 ' :p_st_transaction_id,'||
666 ' 0,'||
667 ' f.sr_instance_id,'||
668 ' f.organization_id,'||
669 ' f.inventory_item_id,'||
670 ' mtp.organization_code,'||
671 ' mi.item_name,'||
672 ' fnd_global.user_id, sysdate,'||
673 ' fnd_global.user_id, sysdate, fnd_global.login_id'||
674 ' from'||
675 ' '||l_apps_schema||'.msc_item_orders_f'||l_suffix||' f,'||
676 ' '||l_apps_schema||'.msc_trading_partners'||l_suffix||' mtp,'||
677 ' '||l_apps_schema||'.msc_items'||l_suffix||' mi'||
678 ' where f.plan_run_id=:p_plan_run_id'||
679 ' and f.aggr_type=0'||
680 ' and mtp.partner_type(+)=3'||
681 ' and mtp.sr_instance_id(+)=f.sr_instance_id'||
682 ' and mtp.sr_tp_id(+)=f.organization_id'||
683 ' and mi.inventory_item_id(+)=f.inventory_item_id'||
684 ' group by'||
685 ' f.sr_instance_id,'||
686 ' f.organization_id,'||
687 ' f.inventory_item_id,'||
688 ' mtp.organization_code,'||
689 ' mi.item_name';
690 else
691 l_sql :=
692 ' insert into msc_st_items_f('||
693 ' st_transaction_id,'||
694 ' error_code,'||
695 ' sr_instance_id,'||
696 ' organization_id,'||
697 ' inventory_item_id,'||
698 ' owning_inst_id,'||
699 ' owning_org_id,'||
700 ' organization_code,'||
701 ' owning_org_code,'||
702 ' item_name,'||
703 ' category_instance_code,'||
704 ' category_name,'||
705 ' dmd_within_obs_horizon,'||
706 ' dmd_within_excess_horizon,'||
707 ' excess_onorder,'||
708 ' excess_onhand,'||
709 ' obsolete_onorder,'||
710 ' obsolete_onhand,'||
714 ' total_excess_value,'||
711 ' total_excess,'||
712 ' excess_from_onhand_value,'||
713 ' excess_from_onorder_value,'||
715 ' excess_from_onhand_value2,'||
716 ' excess_from_onorder_value2,'||
717 ' total_excess_value2,'||
718 ' total_obs,'||
719 ' obsolete_onhand_value,'||
720 ' obsolete_onorder_value,'||
721 ' total_obs_value,'||
722 ' obsolete_onhand_value2,'||
723 ' obsolete_onorder_value2,'||
724 ' total_obs_value2,'||
725 ' wh_available_capacity,'||
726 ' created_by, creation_date,'||
727 ' last_updated_by, last_update_date, last_update_login'||
728 ' )'||
729 ' select'||
730 ' :p_st_transaction_id,'||
731 ' 0,'||
732 ' f.sr_instance_id,'||
733 ' f.organization_id,'||
734 ' f.owning_inst_id,'||
735 ' f.owning_org_id,'||
736 ' f.inventory_item_id,'||
737 ' mtp.organization_code,'||
738 ' mtp2.organization_code,'||
739 ' mi.item_name,'||
740 ' mai.instance_code category_instance_code,'||
741 ' (select category_name from msc_phub_categories_mv where sr_instance_id=f.owning_inst_id and sr_category_id=-f.inventory_item_id and rownum=1) category_name,'||
742 ' f.dmd_within_obs_horizon,'||
743 ' f.dmd_within_excess_horizon,'||
744 ' f.excess_onorder,'||
745 ' f.excess_onhand,'||
746 ' f.obsolete_onorder,'||
747 ' f.obsolete_onhand,'||
748 ' f.total_excess,'||
749 ' f.excess_from_onhand_value,'||
750 ' f.excess_from_onorder_value,'||
751 ' f.total_excess_value,'||
752 ' f.excess_from_onhand_value2,'||
753 ' f.excess_from_onorder_value2,'||
754 ' f.total_excess_value2,'||
755 ' f.total_obs,'||
756 ' f.obsolete_onhand_value,'||
757 ' f.obsolete_onorder_value,'||
758 ' f.total_obs_value,'||
759 ' f.obsolete_onhand_value2,'||
760 ' f.obsolete_onorder_value2,'||
761 ' f.total_obs_value2,'||
762 ' f.wh_available_capacity,'||
763 ' fnd_global.user_id, sysdate,'||
764 ' fnd_global.user_id, sysdate, fnd_global.login_id'||
765 ' from'||
766 ' '||l_apps_schema||'.msc_items_f'||l_suffix||' f,'||
767 ' '||l_apps_schema||'.msc_trading_partners'||l_suffix||' mtp,'||
768 ' '||l_apps_schema||'.msc_trading_partners'||l_suffix||' mtp2,'||
769 ' '||l_apps_schema||'.msc_items'||l_suffix||' mi,'||
770 ' '||l_apps_schema||'.msc_apps_instances'||l_suffix||' mai'||
771 ' where f.plan_run_id=:p_plan_run_id'||
772 ' and f.aggr_type=0'||
773 ' and mtp.partner_type(+)=3'||
774 ' and mtp.sr_instance_id(+)=f.sr_instance_id'||
775 ' and mtp.sr_tp_id(+)=f.organization_id'||
776 ' and mi.inventory_item_id(+)=f.inventory_item_id'||
777 ' and mtp2.partner_type(+)=3'||
778 ' and mtp2.sr_instance_id(+)=f.owning_inst_id'||
779 ' and mtp2.sr_tp_id(+)=f.owning_org_id'||
780 ' and mai.instance_id(+)=f.owning_inst_id';
781 end if;
782
783 execute immediate l_sql using p_st_transaction_id, p_plan_run_id;
784 msc_phub_util.log('msc_phub_excess_pkg.export_items_f: inserted='||sql%rowcount);
785 commit;
786 msc_phub_util.log('msc_phub_excess_pkg.export_items_f: complete, retcode='||retcode);
787
788 exception
789 when others then
790 retcode := 2;
791 errbuf := 'msc_phub_excess_pkg.export_items_f: '||sqlerrm;
792 msc_phub_util.log(errbuf);
793 end export_items_f;
794
795 procedure import_items_f (
796 errbuf out nocopy varchar2, retcode out nocopy varchar2,
797 p_st_transaction_id number, p_plan_id number, p_plan_run_id number,
798 p_plan_type number, p_plan_start_date date, p_plan_cutoff_date date,
799 p_upload_mode number, p_overwrite_after_date date, p_def_instance_code varchar2)
800 is
801 l_staging_table varchar2(30) := 'msc_st_items_f';
802 l_fact_table varchar2(30) := 'msc_items_f';
803 l_result number := 0;
804 begin
805 msc_phub_util.log('msc_phub_excess_pkg.import_items_f');
806 retcode := 0;
807 errbuf := null;
808
809 l_result := l_result + msc_phub_util.prepare_fact_dates(
810 l_fact_table, 1, null, p_plan_id, p_plan_run_id,
811 p_upload_mode, p_overwrite_after_date);
812
813 l_result := l_result + msc_phub_util.decode_organization_key(
814 l_staging_table, p_st_transaction_id, p_def_instance_code,
815 'sr_instance_id', 'organization_id', 'organization_code');
816
817 l_result := l_result + msc_phub_util.decode_organization_key(
818 l_staging_table, p_st_transaction_id, p_def_instance_code,
819 'owning_inst_id', 'owning_org_id', 'owning_org_code');
820
821 l_result := l_result + msc_phub_util.decode_item_key(
822 l_staging_table, p_st_transaction_id, 'inventory_item_id', 'item_name');
823
824 l_result := l_result + msc_phub_util.decode_item_key2(
825 l_staging_table, p_st_transaction_id, 'inventory_item_id', 'owning_inst_id', 'category_instance_code', 'category_name', 'item_name');
826
827 msc_phub_util.log('msc_phub_excess_pkg.import_items_f: insert into msc_items_f');
828 insert into msc_items_f (
829 plan_id,
830 plan_run_id,
831 sr_instance_id,
832 organization_id,
833 owning_inst_id,
834 owning_org_id,
835 inventory_item_id,
836 dmd_within_obs_horizon,
837 dmd_within_excess_horizon,
838 excess_onorder,
839 excess_onhand,
840 obsolete_onorder,
841 obsolete_onhand,
842 total_excess,
843 excess_from_onhand_value,
844 excess_from_onorder_value,
845 total_excess_value,
846 excess_from_onhand_value2,
847 excess_from_onorder_value2,
848 total_excess_value2,
849 total_obs,
850 obsolete_onhand_value,
851 obsolete_onorder_value,
852 total_obs_value,
853 obsolete_onhand_value2,
854 obsolete_onorder_value2,
855 total_obs_value2,
856 wh_available_capacity,
857 aggr_type, category_set_id, sr_category_id,
858 created_by, creation_date,
859 last_updated_by, last_update_date, last_update_login
860 )
861 select
862 p_plan_id,
863 p_plan_run_id,
864 nvl(sr_instance_id, -23453),
865 nvl(organization_id, -23453),
866 nvl(owning_inst_id, -23453),
867 nvl(owning_org_id, -23453),
868 nvl(inventory_item_id, -23453),
869 dmd_within_obs_horizon,
870 dmd_within_excess_horizon,
871 excess_onorder,
872 excess_onhand,
873 obsolete_onorder,
874 obsolete_onhand,
875 total_excess,
876 excess_from_onhand_value,
877 excess_from_onorder_value,
878 total_excess_value,
879 excess_from_onhand_value2,
880 excess_from_onorder_value2,
881 total_excess_value2,
882 total_obs,
883 obsolete_onhand_value,
884 obsolete_onorder_value,
885 total_obs_value,
886 obsolete_onhand_value2,
887 obsolete_onorder_value2,
888 total_obs_value2,
889 wh_available_capacity,
890 0, -23453, -23453,
891 fnd_global.user_id, sysdate,
892 fnd_global.user_id, sysdate, fnd_global.login_id
893 from msc_st_items_f
894 where st_transaction_id=p_st_transaction_id and error_code=0;
895
896 msc_phub_util.log('msc_phub_excess_pkg.import_items_f: inserted='||sql%rowcount);
897 commit;
898
899 summarize_items_f(errbuf, retcode, p_plan_id, p_plan_run_id);
900
901 if (l_result > 0) then
902 retcode := -1;
903 end if;
904
905 msc_phub_util.log('msc_phub_excess_pkg.import_items_f: complete, retcode='||retcode);
906
907 exception
908 when others then
909 retcode := 2;
910 errbuf := 'msc_phub_excess_pkg.import_items_f: '||sqlerrm;
911 msc_phub_util.log(errbuf);
912 end import_items_f;
913
914 end msc_phub_excess_pkg;