[Home] [Help]
PACKAGE BODY: APPS.MSC_PHUB_COST_PKG
Source
1 package body msc_phub_cost_pkg as
2 /* $Header: MSCHBCTB.pls 120.37.12020000.2 2012/10/11 13:52:34 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_owning_currency_code varchar2(20) := msc_phub_util.get_owning_currency_code(p_plan_run_id);
8
9 l_plan_start_date date;
10 l_plan_cutoff_date date;
11 l_plan_type number;
12 l_sr_instance_id number;
13 l_organization_id number;
14 l_transfer_id number := null;
15 l_start_time timestamp := systimestamp;
16 l_complete_time timestamp := systimestamp;
17 l_enable_scrm number := nvl(fnd_profile.value('MSC_APCC_ENABLE_SCRM'), 1);
18 begin
19 msc_phub_util.log('msc_phub_cost_pkg.populate_details:');
20 retcode := 0;
21 errbuf := null;
22
23 select plan_type, sr_instance_id, organization_id, plan_start_date, plan_cutoff_date, temp_transfer_id
24 into l_plan_type, l_sr_instance_id, l_organization_id, l_plan_start_date, l_plan_cutoff_date, l_transfer_id
25 from msc_plan_runs
26 where plan_id=p_plan_id
27 and plan_run_id=p_plan_run_id;
28
29 -- msc_st_costs_f:regular_costs
30 msc_phub_util.gather_table_stats(msc_phub_util.stats_pds, 'MSC_BIS_INV_DETAIL');
31 insert /*+ append nologging */ into msc_st_costs_f (
32 st_transaction_id,
33 error_code,
34 owning_inst_id,
35 owning_org_id,
36 sr_instance_id,
37 organization_id,
38 source_org_instance_id,
39 source_organization_id,
40 inventory_item_id,
41 customer_id,
42 customer_site_id,
43 customer_region_id,
44 supplier_id,
45 supplier_site_id,
46 ship_method,
47 detail_date,
48
49 revenue,
50 manufacturing_cost,
51 purchasing_cost,
52 transportation_cost,
53 carrying_cost,
54 supply_chain_cost,
55 item_travel_distance,
56
57 created_by, creation_date,
58 last_update_date, last_updated_by, last_update_login,
59 program_id, program_login_id,
60 program_application_id, request_id)
61 select
62 l_transfer_id,
63 to_number(0),
64 decode(sign(mbid.sr_instance_id), -1,
65 l_sr_instance_id, mbid.sr_instance_id) owning_inst_id,
66 decode(sign(mbid.organization_id), -1,
67 msc_hub_calendar.get_item_org(p_plan_id,
68 mbid.inventory_item_id,
69 decode(sign(mbid.sr_instance_id), -1,
70 l_sr_instance_id, mbid.sr_instance_id)),
71 mbid.organization_id) owning_org_id,
72 decode(sign(nvl(mbid.organization_id, -23453)),
73 -1, -23453, nvl(mbid.sr_instance_id, -23453)) sr_instance_id,
74 nvl(mbid.organization_id, -23453) organization_id,
75 nvl(mbid.source_org_instance_id, -23453) source_org_instance_id,
76 nvl(mbid.source_organization_id, -23453) source_organization_id,
77 nvl(mbid.inventory_item_id, -23453) inventory_item_id,
78 decode(sign(mbid.customer_id), 1, mbid.customer_id, -23453) customer_id,
79 decode(sign(mbid.customer_site_id), 1, mbid.customer_site_id, -23453) customer_site_id,
80 decode(sign(mbid.zone_id), 1, mbid.zone_id, -23453) customer_region_id,
81 decode(sign(mbid.supplier_id), 1, mbid.supplier_id, -23453) supplier_id,
82 decode(sign(mbid.supplier_site_id), 1, mbid.supplier_site_id, -23453) supplier_site_id,
83 nvl(mbid.ship_method, '-23453') ship_method,
84 d.mfg_week_end_date detail_date,
85 -- bnaghi: DRP fact not available item_travel_distance , manufacturing_cost and transportation_cost=0
86 sum(decode(l_plan_type, 6,
87 decode(sign(mbid.mds_price), -1, -mbid.mds_price, 0), mbid.mds_price)) revenue,
88 decode(l_plan_type,5, to_number(null),sum(nvl(mbid.production_cost,0))) manufacturing_cost,
89 sum(nvl(mbid.purchasing_cost,0)) purchasing_cost,
90 decode(l_plan_type,5, 0,sum(nvl(mbid.transportation_cost,0))) transportation_cost,
91 sum(nvl(mbid.carrying_cost,0)) carrying_cost,
92 sum(nvl(mbid.production_cost,0) + nvl(mbid.purchasing_cost,0) +
93 nvl(mbid.carrying_cost,0) + decode(l_plan_type,5, 0,nvl(mbid.transportation_cost,0))) supply_chain_cost,
94 decode(l_plan_type,5, to_number(null),sum(nvl(mbid.item_travel_distance,0))) item_travel_distance,
95
96 fnd_global.user_id, sysdate,
97 sysdate, fnd_global.user_id, fnd_global.login_id,
98 fnd_global.conc_program_id, fnd_global.conc_login_id,
99 fnd_global.prog_appl_id, fnd_global.conc_request_id
100 from msc_bis_inv_detail mbid, msc_phub_dates_mv d
101 where mbid.plan_id=p_plan_id
102 and l_plan_type not in (101,102,103,105)
103 and (mbid.detail_level=1 or l_plan_type=6)
104 and mbid.period_type=1
105 and trunc(mbid.detail_date)=d.calendar_date
106 group by
107 decode(sign(mbid.sr_instance_id), -1,
108 l_sr_instance_id, mbid.sr_instance_id),
109 decode(sign(mbid.organization_id), -1,
110 msc_hub_calendar.get_item_org(p_plan_id,
111 mbid.inventory_item_id,
112 decode(sign(mbid.sr_instance_id), -1,
113 l_sr_instance_id, mbid.sr_instance_id)),
114 mbid.organization_id),
115 decode(sign(nvl(mbid.organization_id, -23453)),
116 -1, -23453, nvl(mbid.sr_instance_id, -23453)),
117 nvl(mbid.organization_id, -23453),
118 nvl(mbid.source_org_instance_id, -23453),
119 nvl(mbid.source_organization_id, -23453),
120 nvl(mbid.inventory_item_id, -23453),
121 decode(sign(mbid.customer_id), 1, mbid.customer_id, -23453),
122 decode(sign(mbid.customer_site_id), 1, mbid.customer_site_id, -23453),
123 decode(sign(mbid.zone_id), 1, mbid.zone_id, -23453),
124 decode(sign(mbid.supplier_id), 1, mbid.supplier_id, -23453),
125 decode(sign(mbid.supplier_site_id), 1, mbid.supplier_site_id, -23453),
126 nvl(mbid.ship_method, '-23453'),
127 d.mfg_week_end_date;
128
129 msc_phub_util.log('insert into msc_st_costs_f:regular_costs: '||sql%rowcount);
130 commit;
131
132 -- msc_st_costs_f:facility_cost
133 insert /*+ append nologging */ into msc_st_costs_f (
134 st_transaction_id,
135 error_code,
136 owning_inst_id,
137 owning_org_id,
138 sr_instance_id,
139 organization_id,
140 source_org_instance_id,
141 source_organization_id,
142 inventory_item_id,
143 customer_id,
144 customer_site_id,
145 customer_region_id,
146 supplier_id,
147 supplier_site_id,
148 ship_method,
149 detail_date,
150
151 fixed_cost,
152 facility_cost,
153
154 created_by, creation_date,
155 last_update_date, last_updated_by, last_update_login,
156 program_id, program_login_id,
157 program_application_id, request_id)
158 select
159 l_transfer_id,
160 to_number(0),
161 decode(sign(mbod.sr_instance_id), -1,
162 l_sr_instance_id, mbod.sr_instance_id) owning_inst_id,
163 decode(sign(mbod.organization_id), -1,
164 l_organization_id, mbod.organization_id) owning_org_id,
165 mbod.sr_instance_id,
166 mbod.organization_id,
167 to_number(-23453) source_org_instance_id,
168 to_number(-23453) source_organization_id,
169 to_number(-23453) inventory_item_id,
170 to_number(-23453) customer_id,
171 to_number(-23453) customer_site_id,
172 to_number(-23453) customer_region_id,
173 to_number(-23453) supplier_id,
174 to_number(-23453) supplier_site_id,
175 '-23453' ship_method,
176 d.mfg_week_end_date,
177 --bnaghi: these facts not available for DRP plans
178 decode(l_plan_type,5, to_number(null),sum(decode(mbod.facility_cost_type,
179 3, mbod.facility_cost, null))) fixed_cost,
180 decode(l_plan_type,5, to_number(null),sum(decode(mbod.facility_cost_type,
181 1, mbod.facility_cost, 2, mbod.facility_cost,
182 null))) facility_cost,
183
184 fnd_global.user_id, sysdate,
185 sysdate, fnd_global.user_id, fnd_global.login_id,
186 fnd_global.conc_program_id, fnd_global.conc_login_id,
187 fnd_global.prog_appl_id, fnd_global.conc_request_id
188 from msc_bis_org_detail mbod, msc_phub_dates_mv d
189 where mbod.plan_id=p_plan_id
190 and trunc(mbod.detail_date)=d.calendar_date
191 group by
192 decode(sign(mbod.sr_instance_id), -1,
193 l_sr_instance_id, mbod.sr_instance_id),
194 decode(sign(mbod.organization_id), -1,
195 l_organization_id, mbod.organization_id),
196 mbod.sr_instance_id,
197 mbod.organization_id,
198 d.mfg_week_end_date;
199
200 msc_phub_util.log('insert into msc_st_costs_f:facility_cost: '||sql%rowcount);
201 commit;
202
203 if (l_enable_scrm not in (2) and l_plan_type not in (5)) then
204 -- msc_st_costs_f:source_count
205 insert /*+ append nologging */ into msc_st_costs_f (
206 st_transaction_id,
207 error_code,
208 owning_inst_id,
209 owning_org_id,
210 sr_instance_id,
211 organization_id,
212 source_org_instance_id,
213 source_organization_id,
214 inventory_item_id,
215 customer_id,
216 customer_site_id,
217 customer_region_id,
218 supplier_id,
219 supplier_site_id,
220 ship_method,
221 detail_date,
222
223 source_count,
224 risk_item_count,
225
226 created_by, creation_date,
227 last_update_date, last_updated_by, last_update_login,
228 program_id, program_login_id,
229 program_application_id, request_id)
230 select
231 l_transfer_id,
232 to_number(0),
233
234 decode(sign(f.sr_instance_id), -1,
235 l_sr_instance_id, f.sr_instance_id) owning_inst_id,
236 decode(sign(f.organization_id), -1,
237 msc_hub_calendar.get_item_org(p_plan_id,
238 f.inventory_item_id,
239 decode(sign(f.sr_instance_id), -1,
240 l_sr_instance_id, f.sr_instance_id)),
241 f.organization_id) owning_org_id,
242 decode(sign(nvl(f.organization_id, -23453)),
243 -1, -23453, nvl(f.sr_instance_id, -23453)) sr_instance_id,
244 nvl(f.organization_id, -23453) organization_id,
245 nvl(f.sr_instance_id2, -23453) source_org_instance_id,
246 nvl(f.source_organization_id, -23453) source_organization_id,
247 nvl(f.inventory_item_id, -23453) inventory_item_id,
248 nvl(f.customer_id, -23453) customer_id,
249 nvl(f.customer_site_id, -23453) customer_site_id,
250 nvl(f.zone_id, -23453) customer_region_id,
251 nvl(f.supplier_id, -23453) supplier_id,
252 nvl(f.supplier_site_id, -23453) supplier_site_id,
253 '-23453' ship_method,
254 d.calendar_date detail_date,
255 f.source_count,
256 f.risk_item_count,
257
258 fnd_global.user_id, sysdate,
259 sysdate, fnd_global.user_id, fnd_global.login_id,
260 fnd_global.conc_program_id, fnd_global.conc_login_id,
261 fnd_global.prog_appl_id, fnd_global.conc_request_id
262 from
263 (select
264 plan_id, sr_instance_id, organization_id, inventory_item_id,
265 customer_id, customer_site_id, zone_id,
266 sr_instance_id2, source_organization_id, supplier_id, supplier_site_id,
267 effective_date, disable_date,
268 source_count,
269 decode(count(1) over(partition by
270 plan_id, sr_instance_id, organization_id, inventory_item_id,
271 customer_id, customer_site_id, zone_id,
272 effective_date, disable_date), 1, 1, 0) risk_item_count
273 from
274 (select
275 plan_id, sr_instance_id, organization_id, inventory_item_id,
276 customer_id, customer_site_id, zone_id,
277 sr_instance_id2, source_organization_id, supplier_id, supplier_site_id,
278 effective_date, disable_date,
279 to_number(1) source_count
280 from msc_item_sourcing
281 where plan_id=p_plan_id
282 group by
283 plan_id, sr_instance_id, organization_id, inventory_item_id,
284 customer_id, customer_site_id, zone_id,
285 sr_instance_id2, source_organization_id, supplier_id, supplier_site_id,
286 effective_date, disable_date
287 )
288 ) f,
289 msc_phub_dates_mv d
290 where d.calendar_date between l_plan_start_date and l_plan_cutoff_date
291 and d.calendar_date in (d.mfg_week_end_date, l_plan_cutoff_date)
292 and d.calendar_date between nvl(f.effective_date, l_plan_start_date) and nvl(f.disable_date, l_plan_cutoff_date);
293
294 msc_phub_util.log('insert into msc_st_costs_f:source_count: '||sql%rowcount);
295 commit;
296 end if;
297
298 -- msc_st_costs_f:RP
299 insert /*+ append nologging */ into msc_st_costs_f (
300 st_transaction_id,
301 error_code,
302 owning_inst_id,
303 owning_org_id,
304 sr_instance_id,
305 organization_id,
306 source_org_instance_id,
307 source_organization_id,
308 inventory_item_id,
309 customer_id,
310 customer_site_id,
311 customer_region_id,
312 supplier_id,
313 supplier_site_id,
314 ship_method,
315 detail_date,
316
317 revenue,
318 manufacturing_cost,
319 purchasing_cost,
320 supply_chain_cost,
321 ctb_make_order_cnt,
322 total_make_order_cnt,
323 avail_component_qty,
324 total_component_qty,
325 ready_to_build_qty,
326 total_build_qty,
327
328 created_by, creation_date,
329 last_update_date, last_updated_by, last_update_login,
330 program_id, program_login_id,
331 program_application_id, request_id)
332 select
333 l_transfer_id,
334 to_number(0),
335
336 decode(sign(mrk.instance_id), -1,
337 l_sr_instance_id, mrk.instance_id) owning_inst_id,
338 decode(sign(mrk.org_id), -1,
339 msc_hub_calendar.get_item_org(p_plan_id,
340 mrk.item_id,
341 decode(sign(mrk.instance_id), -1,
342 l_sr_instance_id, mrk.instance_id)),
343 mrk.org_id) owning_org_id,
344 decode(sign(nvl(mrk.org_id, -23453)),
345 -1, -23453, nvl(mrk.instance_id, -23453)) sr_instance_id,
346 nvl(mrk.org_id, -23453) organization_id,
347 to_number(-23453) source_org_instance_id,
348 to_number(-23453) source_organization_id,
349 nvl(mrk.item_id, -23453) inventory_item_id,
350 nvl(mrk.customer_id, -23453) customer_id,
351 nvl(mrk.customer_site_id, -23453) customer_site_id,
352 to_number(-23453) customer_region_id,
353 nvl(mrk.supplier_id, -23453) supplier_id,
354 nvl(mrk.supplier_site_id, -23453) supplier_site_id,
355 '-23453' ship_method,
356 d.mfg_week_end_date detail_date,
357 sum(decode(mrk.kpi_type_id, 5, kpi_value, 0)) revenue,
358 sum(decode(mrk.kpi_type_id, 7, kpi_value, 0)) manufacturing_cost,
359 sum(decode(mrk.kpi_type_id, 8, kpi_value, 0)) purchasing_cost,
360 sum(case when mrk.kpi_type_id in (7,8) then kpi_value else 0 end) supply_chain_cost,
361 sum(decode(mrk.kpi_type_id, 36, kpi_value_num1, 0)) ctb_make_order_cnt,
362 sum(decode(mrk.kpi_type_id, 36, kpi_value_num2, 0)) total_make_order_cnt,
363 sum(decode(mrk.kpi_type_id, 37, kpi_value_num1, 0)) avail_component_qty,
364 sum(decode(mrk.kpi_type_id, 37, kpi_value_num2, 0)) total_component_qty,
365 sum(decode(mrk.kpi_type_id, 38, kpi_value_num1, 0)) ready_to_build_qty,
366 sum(decode(mrk.kpi_type_id, 38, kpi_value_num2, 0)) total_build_qty,
367
368 fnd_global.user_id, sysdate,
369 sysdate, fnd_global.user_id, fnd_global.login_id,
370 fnd_global.conc_program_id, fnd_global.conc_login_id,
371 fnd_global.prog_appl_id, fnd_global.conc_request_id
372 from
373 msc_rp_kpi mrk,
374 msc_phub_dates_mv d
375 where mrk.plan_id=p_plan_id
376 and mrk.kpi_type_id in (5,7,8,36,37,38)
377 and l_plan_type in (101,102,103,105)
378 and trunc(mrk.kpi_time)=d.calendar_date
379 and mrk.item_id is not null
380 and mrk.kpi_time is not null
381 and mrk.org_id is not null
382 group by
383 decode(sign(mrk.instance_id), -1,
384 l_sr_instance_id, mrk.instance_id),
385 decode(sign(mrk.org_id), -1,
386 msc_hub_calendar.get_item_org(p_plan_id,
387 mrk.item_id,
388 decode(sign(mrk.instance_id), -1,
389 l_sr_instance_id, mrk.instance_id)),
390 mrk.org_id),
391 decode(sign(nvl(mrk.org_id, -23453)),
392 -1, -23453, nvl(mrk.instance_id, -23453)),
393 nvl(mrk.org_id, -23453),
394 nvl(mrk.item_id, -23453),
395 nvl(mrk.customer_id, -23453),
396 nvl(mrk.customer_site_id, -23453),
397 nvl(mrk.supplier_id, -23453),
398 nvl(mrk.supplier_site_id, -23453),
399 d.mfg_week_end_date;
400
401 msc_phub_util.log('insert into msc_st_costs_f:RP: '||sql%rowcount);
402 commit;
403
404 -- msc_costs_f:final
405 msc_phub_util.unusuable_local_index('MSC_COSTS_F', p_plan_run_id, 1);
406 insert into msc_costs_f (
407 plan_id,
408 plan_run_id,
409 io_plan_flag,
410 owning_inst_id,
411 owning_org_id,
412 sr_instance_id,
413 organization_id,
414 source_org_instance_id,
415 source_organization_id,
416 inventory_item_id,
417 customer_id,
418 customer_site_id,
419 customer_region_id,
420 supplier_id,
421 supplier_site_id,
422 ship_method,
423 detail_date,
424 aggr_type,
425 category_set_id,
426 sr_category_id,
427
428 revenue,
429 revenue2,
430 manufacturing_cost,
431 manufacturing_cost2,
432 purchasing_cost,
433 purchasing_cost2,
434 transportation_cost,
435 transportation_cost2,
436 carrying_cost,
437 carrying_cost2,
438 supply_chain_cost,
439 supply_chain_cost2,
440 gross_margin,
441 gross_margin2,
442 fixed_cost,
443 fixed_cost2,
444 facility_cost,
445 facility_cost2,
446 item_travel_distance,
447 source_count,
448 risk_item_count,
449 ctb_make_order_cnt,
450 total_make_order_cnt,
451 avail_component_qty,
452 total_component_qty,
453 ready_to_build_qty,
454 total_build_qty,
455
456 created_by, creation_date,
457 last_update_date, last_updated_by, last_update_login,
458 program_id, program_login_id,
459 program_application_id, request_id)
460 select
461 p_plan_id,
462 p_plan_run_id,
463 decode(l_plan_type,4,1,9,1,0) io_plan_flag,
464 f.owning_inst_id,
465 f.owning_org_id,
466 f.sr_instance_id,
467 f.organization_id,
468 f.source_org_instance_id,
469 f.source_organization_id,
470 f.inventory_item_id,
471 f.customer_id,
472 f.customer_site_id,
473 f.customer_region_id,
474 f.supplier_id,
475 f.supplier_site_id,
476 f.ship_method,
477 f.detail_date,
478 to_number(0) aggr_type,
479 to_number(-23453) category_set_id,
480 to_number(-23453) sr_category_id,
481 f.revenue,
482 f.revenue * decode(f.currency_code, fnd_profile.value('MSC_HUB_CUR_CODE_RPT'), 1, nvl(mcc.conv_rate, 0)) revenue2,
483 f.manufacturing_cost,
484 f.manufacturing_cost * decode(f.currency_code, fnd_profile.value('MSC_HUB_CUR_CODE_RPT'), 1, nvl(mcc.conv_rate, 0)) manufacturing_cost2,
485 f.purchasing_cost,
486 f.purchasing_cost * decode(f.currency_code, fnd_profile.value('MSC_HUB_CUR_CODE_RPT'), 1, nvl(mcc.conv_rate, 0)) purchasing_cost2,
487 f.transportation_cost,
488 f.transportation_cost * decode(f.currency_code, fnd_profile.value('MSC_HUB_CUR_CODE_RPT'), 1, nvl(mcc.conv_rate, 0)) transportation_cost2,
489 f.carrying_cost,
490 f.carrying_cost * decode(f.currency_code, fnd_profile.value('MSC_HUB_CUR_CODE_RPT'), 1, nvl(mcc.conv_rate, 0)) carrying_cost2,
491 f.supply_chain_cost,
492 f.supply_chain_cost * decode(f.currency_code, fnd_profile.value('MSC_HUB_CUR_CODE_RPT'), 1, nvl(mcc.conv_rate, 0)) supply_chain_cost2,
493 (nvl(f.revenue,0) - nvl(f.supply_chain_cost,0)) gross_margin,
494 (nvl(f.revenue,0) - nvl(f.supply_chain_cost,0)) * decode(f.currency_code, fnd_profile.value('MSC_HUB_CUR_CODE_RPT'), 1, nvl(mcc.conv_rate, 0)) gross_margin2,
495 f.fixed_cost,
496 f.fixed_cost * decode(f.currency_code, fnd_profile.value('MSC_HUB_CUR_CODE_RPT'), 1, nvl(mcc.conv_rate, 0)) fixed_cost2,
497 f.facility_cost,
498 f.facility_cost * decode(f.currency_code, fnd_profile.value('MSC_HUB_CUR_CODE_RPT'), 1, nvl(mcc.conv_rate, 0)) facility_cost2,
499 f.item_travel_distance,
500 f.source_count,
501 f.risk_item_count,
502 f.ctb_make_order_cnt,
503 f.total_make_order_cnt,
504 f.avail_component_qty,
505 f.total_component_qty,
506 f.ready_to_build_qty,
507 f.total_build_qty,
508 fnd_global.user_id, sysdate,
509 sysdate, fnd_global.user_id, fnd_global.login_id,
510 fnd_global.conc_program_id, fnd_global.conc_login_id,
511 fnd_global.prog_appl_id, fnd_global.conc_request_id
512 from
513 (select
514 f.owning_inst_id,
515 f.owning_org_id,
516 f.sr_instance_id,
517 f.organization_id,
518 f.source_org_instance_id,
519 f.source_organization_id,
520 f.inventory_item_id,
521 f.customer_id,
522 f.customer_site_id,
523 f.customer_region_id,
524 f.supplier_id,
525 f.supplier_site_id,
526 f.ship_method,
527 f.detail_date,
528 decode(l_plan_type, 6, l_owning_currency_code, nvl(mtp.currency_code, l_owning_currency_code)) currency_code,
529
530 sum(f.revenue) revenue,
531 sum(f.manufacturing_cost) manufacturing_cost,
532 sum(f.purchasing_cost) purchasing_cost,
533 sum(f.transportation_cost) transportation_cost,
534 sum(f.carrying_cost) carrying_cost,
535 sum(f.supply_chain_cost) supply_chain_cost,
536 sum(f.item_travel_distance) item_travel_distance,
537 sum(f.fixed_cost) fixed_cost,
538 sum(f.facility_cost) facility_cost,
539 sum(f.source_count) source_count,
540 sum(f.risk_item_count) risk_item_count,
541 sum(f.ctb_make_order_cnt) ctb_make_order_cnt,
542 sum(f.total_make_order_cnt) total_make_order_cnt,
543 sum(f.avail_component_qty) avail_component_qty,
544 sum(f.total_component_qty) total_component_qty,
545 sum(f.ready_to_build_qty) ready_to_build_qty,
546 sum(f.total_build_qty) total_build_qty
547 from
548 msc_st_costs_f f,
549 msc_trading_partners mtp
550 where f.st_transaction_id=l_transfer_id
551 and mtp.partner_type(+)=3
552 and f.owning_inst_id=mtp.sr_instance_id(+)
553 and f.owning_org_id=mtp.sr_tp_id(+)
554 group by
555 f.owning_inst_id,
556 f.owning_org_id,
557 f.sr_instance_id,
558 f.organization_id,
559 f.source_org_instance_id,
560 f.source_organization_id,
561 f.inventory_item_id,
562 f.customer_id,
563 f.customer_site_id,
564 f.customer_region_id,
565 f.supplier_id,
566 f.supplier_site_id,
567 f.ship_method,
568 f.detail_date,
569 decode(l_plan_type, 6, l_owning_currency_code, nvl(mtp.currency_code, l_owning_currency_code))
570 ) f,
571 msc_currency_conv_mv mcc
572 where f.currency_code=mcc.from_currency(+)
573 and fnd_profile.value('MSC_HUB_CUR_CODE_RPT')=mcc.to_currency(+)
574 and f.detail_date=mcc.calendar_date(+);
575
576 msc_phub_util.log('insert into msc_costs_f:final: '||sql%rowcount);
577 commit;
578 msc_phub_util.unusuable_local_index('MSC_COSTS_F', p_plan_run_id, 2);
579 msc_phub_util.gather_table_stats(msc_phub_util.stats_fact, 'MSC_COSTS_F', p_plan_run_id);
580
581 summarize_costs_f(errbuf, retcode, p_plan_id, p_plan_run_id);
582 l_complete_time := systimestamp;
583 msc_phub_util.log('msc_phub_cost_pkg.populate_details.complete, duration='||(l_complete_time-l_start_time));
584
585 exception
586 when others then
587 msc_phub_util.log('msc_phub_cost_pkg.populate_details: '||sqlerrm);
588 raise;
589
590 end populate_details;
591
592
593 procedure summarize_costs_f(errbuf out nocopy varchar2, retcode out nocopy varchar2,
594 p_plan_id number, p_plan_run_id number)
595 is
596 l_category_set_id1 number := fnd_profile.value('MSC_HUB_CAT_SET_ID_1');
597 begin
598 msc_phub_util.log('msc_phub_cost_pkg.summarize_costs_f');
599 retcode := 0;
600 errbuf := '';
601
602 delete from msc_costs_f
603 where plan_id=p_plan_id and plan_run_id=p_plan_run_id and aggr_type>0;
604 msc_phub_util.log('msc_phub_cost_pkg.summarize_costs_f, delete='||sql%rowcount);
605 commit;
606
607 -- level 1
608 insert into msc_costs_f (
609 plan_id, plan_run_id,
610 owning_inst_id, owning_org_id,
611 sr_instance_id, organization_id,
612 source_org_instance_id, source_organization_id,
613 inventory_item_id,
614 customer_id, customer_site_id, customer_region_id,
615 supplier_id, supplier_site_id,
616 io_plan_flag, ship_method, detail_date,
617 aggr_type, category_set_id, sr_category_id,
618 revenue,
619 revenue2,
620 manufacturing_cost,
621 manufacturing_cost2,
622 purchasing_cost,
623 purchasing_cost2,
624 transportation_cost,
625 transportation_cost2,
626 carrying_cost,
627 carrying_cost2,
628 supply_chain_cost,
629 supply_chain_cost2,
630 gross_margin,
631 gross_margin2,
632 fixed_cost,
633 fixed_cost2,
634 facility_cost,
635 facility_cost2,
636 item_travel_distance,
637 source_count,
638 risk_item_count,
639 ctb_make_order_cnt,
640 total_make_order_cnt,
641 avail_component_qty,
642 total_component_qty,
643 ready_to_build_qty,
644 total_build_qty,
645 created_by, creation_date,
646 last_update_date, last_updated_by, last_update_login,
647 program_id, program_login_id,
648 program_application_id, request_id)
649 -- category (42, 43, 44)
650 select
651 f.plan_id, f.plan_run_id,
652 f.owning_inst_id, f.owning_org_id,
653 f.sr_instance_id, f.organization_id,
654 f.source_org_instance_id, f.source_organization_id,
655 to_number(-23453) inventory_item_id,
656 f.customer_id, f.customer_site_id, f.customer_region_id,
657 f.supplier_id, f.supplier_site_id,
658 f.io_plan_flag, f.ship_method, f.detail_date,
659 to_number(42) aggr_type,
660 l_category_set_id1 category_set_id,
661 nvl(q.sr_category_id, -23453),
662 sum(f.revenue),
663 sum(f.revenue2),
664 sum(f.manufacturing_cost),
665 sum(f.manufacturing_cost2),
666 sum(f.purchasing_cost),
667 sum(f.purchasing_cost2),
668 sum(f.transportation_cost),
669 sum(f.transportation_cost2),
670 sum(f.carrying_cost),
671 sum(f.carrying_cost2),
672 sum(f.supply_chain_cost),
673 sum(f.supply_chain_cost2),
674 sum(f.gross_margin),
675 sum(f.gross_margin2),
676 sum(f.fixed_cost),
677 sum(f.fixed_cost2),
678 sum(f.facility_cost),
679 sum(f.facility_cost2),
680 sum(f.item_travel_distance),
681 sum(f.source_count),
682 sum(f.risk_item_count),
683 sum(f.ctb_make_order_cnt),
684 sum(f.total_make_order_cnt),
685 sum(f.avail_component_qty),
686 sum(f.total_component_qty),
687 sum(f.ready_to_build_qty),
688 sum(f.total_build_qty),
689 fnd_global.user_id, sysdate,
690 sysdate, fnd_global.user_id, fnd_global.login_id,
691 fnd_global.conc_program_id, fnd_global.conc_login_id,
692 fnd_global.prog_appl_id, fnd_global.conc_request_id
693 from
694 msc_costs_f f,
695 msc_phub_item_categories_mv q
696 where f.plan_id = p_plan_id and f.plan_run_id = p_plan_run_id
697 and f.aggr_type=0
698 and f.sr_instance_id=q.sr_instance_id(+)
699 and f.organization_id=q.organization_id(+)
700 and f.inventory_item_id=q.inventory_item_id(+)
701 and q.category_set_id(+)=l_category_set_id1
702 group by
703 f.plan_id, f.plan_run_id,
704 f.owning_inst_id, f.owning_org_id,
705 f.sr_instance_id, f.organization_id,
706 f.source_org_instance_id, f.source_organization_id,
707 f.customer_id, f.customer_site_id, f.customer_region_id,
708 f.supplier_id, f.supplier_site_id,
709 f.io_plan_flag, f.ship_method, f.detail_date,
710 nvl(q.sr_category_id, -23453);
711
712 msc_phub_util.log('msc_phub_cost_pkg.summarize_costs_f, level1='||sql%rowcount);
713 commit;
714
715 exception
716 when others then
717 retcode := 2;
718 errbuf := 'msc_phub_cost_pkg.summarize_costs_f: '||sqlerrm;
719 raise;
720
721 end summarize_costs_f;
722
723 procedure export_costs_f (
724 errbuf out nocopy varchar2, retcode out nocopy varchar2,
725 p_st_transaction_id number, p_plan_run_id number,
726 p_dblink varchar2, p_source_version varchar2)
727 is
728 l_sql varchar2(5000);
729 l_suffix varchar2(32) := msc_phub_util.suffix(p_dblink);
730 l_apps_schema varchar2(30) := msc_phub_util.apps_schema;
731 begin
732 msc_phub_util.log('msc_phub_cost_pkg.export_costs_f');
733 retcode := 0;
734 errbuf := null;
735
736 delete from msc_st_costs_f where st_transaction_id=p_st_transaction_id;
737 commit;
738
739 if (p_source_version < '12.1.3') then
740 l_sql :=
741 ' insert into msc_st_costs_f('||
742 ' st_transaction_id,'||
743 ' error_code,'||
744 ' owning_inst_id,'||
745 ' owning_org_id,'||
746 ' sr_instance_id,'||
747 ' organization_id,'||
748 ' inventory_item_id,'||
749 ' owning_org_code,'||
750 ' organization_code,'||
751 ' item_name,'||
752 ' detail_date,'||
753 ' revenue,'||
754 ' revenue2,'||
755 ' manufacturing_cost,'||
756 ' manufacturing_cost2,'||
757 ' purchasing_cost,'||
758 ' purchasing_cost2,'||
759 ' transportation_cost,'||
760 ' transportation_cost2,'||
761 ' carrying_cost,'||
762 ' carrying_cost2,'||
763 ' supply_chain_cost,'||
764 ' supply_chain_cost2,'||
765 ' gross_margin,'||
766 ' gross_margin2,'||
767 ' created_by, creation_date,'||
768 ' last_updated_by, last_update_date, last_update_login'||
769 ' )'||
770 ' select'||
771 ' :p_st_transaction_id,'||
772 ' 0,'||
773 ' f.owning_inst_id,'||
774 ' f.owning_org_id,'||
775 ' f.sr_instance_id,'||
776 ' f.organization_id,'||
777 ' f.inventory_item_id,'||
778 ' mtp3.organization_code,'||
779 ' mtp.organization_code,'||
780 ' mi.item_name,'||
781 ' f.order_date,'||
782 ' f.revenue,'||
783 ' f.revenue2,'||
784 ' f.manufacturing_cost,'||
785 ' f.manufacturing_cost2,'||
786 ' f.purchasing_cost,'||
787 ' f.purchasing_cost2,'||
788 ' f.transportation_cost,'||
789 ' f.transportation_cost2,'||
790 ' f.carrying_cost,'||
791 ' f.carrying_cost2,'||
792 ' f.supply_chain_cost,'||
793 ' f.supply_chain_cost2,'||
794 ' f.gross_margin,'||
795 ' f.gross_margin2,'||
796 ' fnd_global.user_id, sysdate,'||
797 ' fnd_global.user_id, sysdate, fnd_global.login_id'||
798 ' from'||
799 ' '||l_apps_schema||'.msc_item_inventory_f'||l_suffix||' f,'||
800 ' '||l_apps_schema||'.msc_trading_partners'||l_suffix||' mtp,'||
801 ' '||l_apps_schema||'.msc_trading_partners'||l_suffix||' mtp3,'||
802 ' '||l_apps_schema||'.msc_items'||l_suffix||' mi'||
803 ' where f.plan_run_id=:p_plan_run_id'||
804 ' and f.aggr_type=0'||
805 ' and mtp.partner_type(+)=3'||
806 ' and mtp.sr_instance_id(+)=f.sr_instance_id'||
807 ' and mtp.sr_tp_id(+)=f.organization_id'||
808 ' and mtp3.partner_type(+)=3'||
809 ' and mtp3.sr_instance_id(+)=f.owning_inst_id'||
810 ' and mtp3.sr_tp_id(+)=f.owning_org_id'||
811 ' and mi.inventory_item_id(+)=f.inventory_item_id';
812 else
813 l_sql :=
814 ' insert into msc_st_costs_f('||
815 ' st_transaction_id,'||
816 ' error_code,'||
817 ' owning_inst_id,'||
818 ' owning_org_id,'||
819 ' sr_instance_id,'||
820 ' organization_id,'||
821 ' source_org_instance_id,'||
822 ' source_organization_id,'||
823 ' inventory_item_id,'||
824 ' customer_id,'||
825 ' customer_site_id,'||
826 ' customer_region_id,'||
827 ' supplier_id,'||
828 ' supplier_site_id,'||
829 ' owning_org_code,'||
830 ' organization_code,'||
831 ' source_org_code,'||
832 ' item_name,'||
833 ' customer_name,'||
834 ' customer_site_code,'||
835 ' customer_zone,'||
836 ' supplier_name,'||
837 ' supplier_site_code,'||
838 ' ship_method,'||
839 ' detail_date,'||
840 ' revenue,'||
841 ' revenue2,'||
842 ' manufacturing_cost,'||
843 ' manufacturing_cost2,'||
844 ' purchasing_cost,'||
845 ' purchasing_cost2,'||
846 ' transportation_cost,'||
847 ' transportation_cost2,'||
848 ' carrying_cost,'||
849 ' carrying_cost2,'||
850 ' supply_chain_cost,'||
851 ' supply_chain_cost2,'||
852 ' gross_margin,'||
853 ' gross_margin2,'||
854 ' fixed_cost,'||
855 ' fixed_cost2,'||
856 ' facility_cost,'||
857 ' facility_cost2,'||
858 ' item_travel_distance,'||
859 ' source_count,'||
860 ' risk_item_count,'||
861 ' ctb_make_order_cnt,'||
862 ' total_make_order_cnt,'||
863 ' avail_component_qty,'||
864 ' total_component_qty,'||
865 ' ready_to_build_qty,'||
866 ' total_build_qty,'||
867 ' created_by, creation_date,'||
868 ' last_updated_by, last_update_date, last_update_login'||
869 ' )'||
870 ' select'||
871 ' :p_st_transaction_id,'||
872 ' 0,'||
873 ' f.owning_inst_id,'||
874 ' f.owning_org_id,'||
875 ' f.sr_instance_id,'||
876 ' f.organization_id,'||
877 ' f.source_org_instance_id,'||
878 ' f.source_organization_id,'||
879 ' f.inventory_item_id,'||
880 ' f.customer_id,'||
881 ' f.customer_site_id,'||
882 ' f.customer_region_id,'||
883 ' f.supplier_id,'||
884 ' f.supplier_site_id,'||
885 ' mtp3.organization_code,'||
886 ' mtp.organization_code,'||
887 ' mtp2.organization_code,'||
888 ' mi.item_name,'||
889 ' decode(f.customer_id, -23453, null, cmv.customer_name),'||
890 ' decode(f.customer_site_id, -23453, null, cmv.customer_site),'||
891 ' decode(f.customer_region_id, -23453, null, cmv.zone),'||
892 ' decode(f.supplier_id, -23453, null, smv.supplier_name),'||
893 ' decode(f.supplier_site_id, -23453, null, smv.supplier_site_code),'||
894 ' f.ship_method,'||
895 ' f.detail_date,'||
896 ' f.revenue,'||
897 ' f.revenue2,'||
898 ' f.manufacturing_cost,'||
899 ' f.manufacturing_cost2,'||
900 ' f.purchasing_cost,'||
901 ' f.purchasing_cost2,'||
902 ' f.transportation_cost,'||
903 ' f.transportation_cost2,'||
904 ' f.carrying_cost,'||
905 ' f.carrying_cost2,'||
906 ' f.supply_chain_cost,'||
907 ' f.supply_chain_cost2,'||
908 ' f.gross_margin,'||
909 ' f.gross_margin2,'||
910 ' f.fixed_cost,'||
911 ' f.fixed_cost2,'||
912 ' f.facility_cost,'||
913 ' f.facility_cost2,'||
914 ' f.item_travel_distance,'||
915 ' f.source_count,'||
916 ' f.risk_item_count,'||
917 ' f.ctb_make_order_cnt,'||
918 ' f.total_make_order_cnt,'||
919 ' f.avail_component_qty,'||
920 ' f.total_component_qty,'||
921 ' f.ready_to_build_qty,'||
922 ' f.total_build_qty,'||
923 ' fnd_global.user_id, sysdate,'||
924 ' fnd_global.user_id, sysdate, fnd_global.login_id'||
925 ' from'||
926 ' '||l_apps_schema||'.msc_costs_f'||l_suffix||' f,'||
927 ' '||l_apps_schema||'.msc_trading_partners'||l_suffix||' mtp,'||
928 ' '||l_apps_schema||'.msc_trading_partners'||l_suffix||' mtp2,'||
929 ' '||l_apps_schema||'.msc_trading_partners'||l_suffix||' mtp3,'||
930 ' '||l_apps_schema||'.msc_items'||l_suffix||' mi,'||
931 ' '||l_apps_schema||'.msc_phub_customers_mv'||l_suffix||' cmv,'||
932 ' '||l_apps_schema||'.msc_phub_suppliers_mv'||l_suffix||' smv'||
933 ' where f.plan_run_id=:p_plan_run_id'||
934 ' and f.aggr_type=0'||
935 ' and mtp.partner_type(+)=3'||
936 ' and mtp.sr_instance_id(+)=f.sr_instance_id'||
937 ' and mtp.sr_tp_id(+)=f.organization_id'||
938 ' and mtp2.partner_type(+)=3'||
939 ' and mtp2.sr_instance_id(+)=f.source_org_instance_id'||
940 ' and mtp2.sr_tp_id(+)=f.source_organization_id'||
941 ' and mtp3.partner_type(+)=3'||
942 ' and mtp3.sr_instance_id(+)=f.owning_inst_id'||
943 ' and mtp3.sr_tp_id(+)=f.owning_org_id'||
944 ' and mi.inventory_item_id(+)=f.inventory_item_id'||
945 ' and cmv.customer_id(+)=f.customer_id'||
946 ' and cmv.customer_site_id(+)=f.customer_site_id'||
947 ' and cmv.region_id(+)=f.customer_region_id';
948 if (p_source_version >= '12.1.3') then l_sql := l_sql||
949 ' and cmv.sr_instance_id(+)=decode(f.customer_region_id,-23453,cmv.sr_instance_id(+),f.owning_inst_id)';
950 end if;
951 l_sql := l_sql||
952 ' and smv.supplier_id(+)=f.supplier_id'||
953 ' and smv.supplier_site_id(+)=f.supplier_site_id';
954 end if;
955
956 execute immediate l_sql using p_st_transaction_id, p_plan_run_id;
957 commit;
958 msc_phub_util.log('msc_phub_cost_pkg.export_costs_f: complete, retcode='||retcode);
959
960 exception
961 when others then
962 retcode := 2;
963 errbuf := 'msc_phub_cost_pkg.export_costs_f: '||sqlerrm;
964 msc_phub_util.log(errbuf);
965 end export_costs_f;
966
967 procedure import_costs_f (
968 errbuf out nocopy varchar2, retcode out nocopy varchar2,
969 p_st_transaction_id number, p_plan_id number, p_plan_run_id number,
970 p_plan_type number, p_plan_start_date date, p_plan_cutoff_date date,
971 p_upload_mode number, p_overwrite_after_date date, p_def_instance_code varchar2)
972 is
973 l_staging_table varchar2(30) := 'msc_st_costs_f';
974 l_fact_table varchar2(30) := 'msc_costs_f';
975 l_result number := 0;
976 begin
977 msc_phub_util.log('msc_phub_cost_pkg.import_costs_f');
978 retcode := 0;
979 errbuf := null;
980
981 l_result := l_result + msc_phub_util.prepare_staging_dates(
982 l_staging_table, 'detail_date', p_st_transaction_id,
983 p_upload_mode, p_overwrite_after_date,
984 p_plan_start_date, p_plan_cutoff_date);
985
986 l_result := l_result + msc_phub_util.prepare_fact_dates(
987 l_fact_table, 1, 'detail_date', p_plan_id, p_plan_run_id,
988 p_upload_mode, p_overwrite_after_date);
989
990 l_result := l_result + msc_phub_util.decode_organization_key(
991 l_staging_table, p_st_transaction_id, p_def_instance_code,
992 'owning_inst_id', 'owning_org_id', 'owning_org_code');
993
994 l_result := l_result + msc_phub_util.decode_organization_key(
995 l_staging_table, p_st_transaction_id, p_def_instance_code,
996 'sr_instance_id', 'organization_id', 'organization_code');
997
998 l_result := l_result + msc_phub_util.decode_organization_key(
999 l_staging_table, p_st_transaction_id, p_def_instance_code,
1000 'source_org_instance_id', 'source_organization_id', 'source_org_code');
1001
1002 l_result := l_result + msc_phub_util.decode_item_key(
1003 l_staging_table, p_st_transaction_id, 'inventory_item_id', 'item_name');
1004
1005 l_result := l_result + msc_phub_util.decode_customer_key(
1006 l_staging_table, p_st_transaction_id,
1007 'customer_id', 'customer_site_id', 'owning_inst_id', 'customer_region_id',
1008 'customer_name', 'customer_site_code', 'customer_zone');
1009
1010 l_result := l_result + msc_phub_util.decode_supplier_key(
1011 l_staging_table, p_st_transaction_id,
1012 'supplier_id', 'supplier_site_id',
1013 'supplier_name', 'supplier_site_code');
1014
1015 msc_phub_util.log('msc_phub_cost_pkg.import_costs_f: insert into msc_costs_f');
1016 insert into msc_costs_f (
1017 plan_id,
1018 plan_run_id,
1019 sr_instance_id,
1020 organization_id,
1021 owning_inst_id,
1022 owning_org_id,
1023 source_org_instance_id,
1024 source_organization_id,
1025 inventory_item_id,
1026 customer_id,
1027 customer_site_id,
1028 customer_region_id,
1029 supplier_id,
1030 supplier_site_id,
1031 io_plan_flag,
1032 ship_method,
1033 detail_date,
1034 revenue,
1035 revenue2,
1036 manufacturing_cost,
1037 manufacturing_cost2,
1038 purchasing_cost,
1039 purchasing_cost2,
1040 transportation_cost,
1041 transportation_cost2,
1042 carrying_cost,
1043 carrying_cost2,
1044 supply_chain_cost,
1045 supply_chain_cost2,
1046 gross_margin,
1047 gross_margin2,
1048 fixed_cost,
1049 fixed_cost2,
1050 facility_cost,
1051 facility_cost2,
1052 item_travel_distance,
1053 source_count,
1054 risk_item_count,
1055 ctb_make_order_cnt,
1056 total_make_order_cnt,
1057 avail_component_qty,
1058 total_component_qty,
1059 ready_to_build_qty,
1060 total_build_qty,
1061 aggr_type, category_set_id, sr_category_id,
1062 created_by, creation_date,
1063 last_updated_by, last_update_date, last_update_login
1064 )
1065 select
1066 p_plan_id,
1067 p_plan_run_id,
1068 nvl(sr_instance_id, -23453),
1069 nvl(organization_id, -23453),
1070 nvl(owning_inst_id, -23453),
1071 nvl(owning_org_id, -23453),
1072 nvl(source_org_instance_id, -23453),
1073 nvl(source_organization_id, -23453),
1074 nvl(inventory_item_id, -23453),
1075 nvl(customer_id, -23453),
1076 nvl(customer_site_id, -23453),
1077 nvl(customer_region_id, -23453),
1078 nvl(supplier_id, -23453),
1079 nvl(supplier_site_id, -23453),
1080 decode(p_plan_type, 4, 1, 0) io_plan_flag,
1081 ship_method,
1082 detail_date,
1083 revenue,
1084 revenue2,
1085 manufacturing_cost,
1086 manufacturing_cost2,
1087 purchasing_cost,
1088 purchasing_cost2,
1089 transportation_cost,
1090 transportation_cost2,
1091 carrying_cost,
1092 carrying_cost2,
1093 supply_chain_cost,
1094 supply_chain_cost2,
1095 gross_margin,
1096 gross_margin2,
1097 fixed_cost,
1098 fixed_cost2,
1099 facility_cost,
1100 facility_cost2,
1101 item_travel_distance,
1102 source_count,
1103 risk_item_count,
1104 ctb_make_order_cnt,
1105 total_make_order_cnt,
1106 avail_component_qty,
1107 total_component_qty,
1108 ready_to_build_qty,
1109 total_build_qty,
1110 0, -23453, -23453,
1111 fnd_global.user_id, sysdate,
1112 fnd_global.user_id, sysdate, fnd_global.login_id
1113 from msc_st_costs_f
1114 where st_transaction_id=p_st_transaction_id and error_code=0;
1115
1116 msc_phub_util.log('msc_phub_cost_pkg.import_costs_f: inserted='||sql%rowcount);
1117 commit;
1118
1119 summarize_costs_f(errbuf, retcode, p_plan_id, p_plan_run_id);
1120
1121 if (l_result > 0) then
1122 retcode := -1;
1123 end if;
1124
1125 msc_phub_util.log('msc_phub_cost_pkg.import_costs_f: complete, retcode='||retcode);
1126
1127 exception
1128 when others then
1129 retcode := 2;
1130 errbuf := 'msc_phub_cost_pkg.import_costs_f: '||sqlerrm;
1131 msc_phub_util.log(errbuf);
1132 end import_costs_f;
1133
1134 procedure migrate
1135 is
1136 l_applsys_schema varchar2(100);
1137 l_msc_schema varchar2(100);
1138 e_migrate exception;
1139 dummy1 varchar2(100);
1140 dummy2 varchar2(100);
1141 l_sql varchar2(2000);
1142 l_need_migrate number := 0;
1143
1144 l_costs_f varchar2(30) := 'MSC_COSTS_F';
1145 l_next_id number;
1146
1147 cursor c2 is
1148 select distinct plan_run_id
1149 from msc_item_inventory_f f
1150 where not exists (select 1 from msc_costs_f where plan_run_id=f.plan_run_id)
1151 order by plan_run_id;
1152 begin
1153 msc_phub_util.log('msc_phub_cost_pkg.migrate');
1154
1155 l_need_migrate := need_migrate;
1156 msc_phub_util.log('msc_phub_cost_pkg.migrate, l_need_migrate='||l_need_migrate);
1157 if (l_need_migrate <> 1) then
1158 return;
1159 end if;
1160
1161
1162 if (fnd_installation.get_app_info('FND',
1163 dummy1, dummy2, l_applsys_schema) = false) then
1164 msc_phub_util.log('get_app_info(FND) failed');
1165 raise e_migrate;
1166 end if;
1167
1168 if (fnd_installation.get_app_info('MSC',
1169 dummy1, dummy2, l_msc_schema) = false) then
1170 msc_phub_util.log('get_app_info(MSC) failed');
1171 raise e_migrate;
1172 end if;
1173
1174 for r in c2
1175 loop
1176 l_sql :=
1177 ' select nvl(min(partition_id), -1) next_id'||
1178 ' from'||
1179 ' (select to_number(substr(partition_name, length(:table_name)-2)) partition_id'||
1180 ' from sys.all_tab_partitions'||
1181 ' where table_name=:table_name) t'||
1182 ' where partition_id>=:plan_run_id';
1183
1184 execute immediate l_sql into l_next_id using 'MSC_COSTS_F', 'MSC_COSTS_F', r.plan_run_id;
1185
1186
1187 --msc_phub_util.log(r.plan_run_id||','||l_next_id);
1188 if (l_next_id=-1) then
1189 l_sql := 'alter table '||l_costs_f||
1190 ' add partition '||substr(l_costs_f, 5)||'_'||to_char(r.plan_run_id)||
1191 ' values less than ('||to_char(r.plan_run_id+1)||')';
1192
1193 ad_ddl.do_ddl(l_applsys_schema, l_msc_schema,
1194 ad_ddl.alter_table, l_sql, l_costs_f);
1195 --msc_phub_util.log(l_sql);
1196 end if;
1197
1198 l_sql :=
1199 ' insert into msc_costs_f ('||
1200 ' plan_id,'||
1201 ' plan_run_id,'||
1202 ' sr_instance_id,'||
1203 ' organization_id,'||
1204 ' inventory_item_id,'||
1205 ' owning_org_id,'||
1206 ' owning_inst_id,'||
1207 ' source_org_instance_id,'||
1208 ' source_organization_id,'||
1209 ' customer_id,'||
1210 ' customer_site_id,'||
1211 ' customer_region_id,'||
1212 ' supplier_id,'||
1213 ' supplier_site_id,'||
1214 ' ship_method,'||
1215 ' detail_date,'||
1216 ' io_plan_flag,'||
1217 ' aggr_type,'||
1218 ' category_set_id,'||
1219 ' sr_category_id,'||
1220 ' revenue,'||
1221 ' revenue2,'||
1222 ' manufacturing_cost,'||
1223 ' manufacturing_cost2,'||
1224 ' purchasing_cost,'||
1225 ' purchasing_cost2,'||
1226 ' transportation_cost,'||
1227 ' transportation_cost2,'||
1228 ' carrying_cost,'||
1229 ' carrying_cost2,'||
1230 ' supply_chain_cost,'||
1231 ' supply_chain_cost2,'||
1232 ' gross_margin,'||
1233 ' gross_margin2,'||
1234 ' created_by, creation_date,'||
1235 ' last_updated_by, last_update_date, last_update_login'||
1236 ' )'||
1237 ' select'||
1238 ' plan_id,'||
1239 ' plan_run_id,'||
1240 ' sr_instance_id,'||
1241 ' organization_id,'||
1242 ' inventory_item_id,'||
1243 ' owning_org_id,'||
1244 ' owning_inst_id,'||
1245 ' to_number(-23453) source_org_instance_id,'||
1246 ' to_number(-23453) source_organization_id,'||
1247 ' to_number(-23453) customer_id,'||
1248 ' to_number(-23453) customer_site_id,'||
1249 ' to_number(-23453) customer_region_id,'||
1250 ' to_number(-23453) supplier_id,'||
1251 ' to_number(-23453) supplier_site_id,'||
1252 ' ship_method,'||
1253 ' order_date detail_date,'||
1254 ' io_plan_flag,'||
1255 ' aggr_type,'||
1256 ' category_set_id,'||
1257 ' sr_category_id,'||
1258 ' revenue,'||
1259 ' revenue2,'||
1260 ' manufacturing_cost,'||
1261 ' manufacturing_cost2,'||
1262 ' purchasing_cost,'||
1263 ' purchasing_cost2,'||
1264 ' transportation_cost,'||
1265 ' transportation_cost2,'||
1266 ' carrying_cost,'||
1267 ' carrying_cost2,'||
1268 ' supply_chain_cost,'||
1269 ' supply_chain_cost2,'||
1270 ' gross_margin,'||
1271 ' gross_margin2,'||
1272 ' fnd_global.user_id, sysdate,'||
1273 ' fnd_global.user_id, sysdate, fnd_global.login_id'||
1274 ' from msc_item_inventory_f'||
1275 ' where plan_run_id=:p_plan_run_id';
1276
1277 execute immediate l_sql using r.plan_run_id;
1278 --msc_phub_util.log('insert: plan_run_id='||r.plan_run_id||', rowcount='||sql%rowcount);
1279 commit;
1280
1281 if (l_next_id>r.plan_run_id) then
1282 l_sql := 'alter table '||l_costs_f||
1283 ' split partition '||substr(l_costs_f, 5)||'_'||to_char(l_next_id)||
1284 ' at ('||to_char(r.plan_run_id+1)||')'||
1285 ' into (partition '||substr(l_costs_f, 5)||'_'||to_char(r.plan_run_id)||', '||
1286 ' partition '||substr(l_costs_f, 5)||'_'||to_char(l_next_id)||')';
1287
1288 ad_ddl.do_ddl(l_applsys_schema, l_msc_schema,
1289 ad_ddl.alter_table, l_sql, l_costs_f);
1290 --msc_phub_util.log(l_sql);
1291 end if;
1292 end loop;
1293 msc_phub_util.log('msc_phub_cost_pkg.migrate complete');
1294
1295 exception
1296 when others then
1297 msc_phub_util.log('msc_phub_cost_pkg.migrate.exception:'||sqlerrm);
1298 raise;
1299 end migrate;
1300
1301 function need_migrate return number
1302 is
1303 l_n1 number := 0;
1304 l_n2 number := 0;
1305 l_sql varchar2(2000);
1306 e_need_migrate exception;
1307 begin
1308 -- test whether 12.1 columns exist
1309 begin
1310 l_sql := 'select count(*) from msc_item_inventory_f where rownum=1 and gross_margin2=0';
1311 execute immediate l_sql into l_n1;
1312 exception
1313 when others then
1314 return 0;
1315 end;
1316
1317 l_sql :=
1318 ' select count(*)'||
1319 ' from sys.all_tab_partitions'||
1320 ' where table_name=:table_name'||
1321 ' and partition_name<>:base_partition_name'||
1322 ' and rownum=1';
1323
1324 execute immediate l_sql into l_n1 using 'MSC_ITEM_INVENTORY_F', 'ITEM_INVENTORY_F_0';
1325 execute immediate l_sql into l_n2 using 'MSC_COSTS_F', 'COSTS_F_0';
1326 return (case when l_n1=1 and l_n2=0 then 1 else 0 end);
1327 end;
1328
1329 end msc_phub_cost_pkg;