[Home] [Help]
PACKAGE BODY: APPS.MSC_SUPPLY_PKG
Source
1 package body msc_supply_pkg as
2 /* $Header: MSCHBSUB.pls 120.69.12020000.2 2012/10/11 14:01:48 wexia ship $ */
3
4
5 function implement_code (p_source_org_id in number,
6 p_org_id in number,
7 p_repetitive_type in number,
8 p_source_supplier_id in number,
9 p_planning_make_buy_code in number,
10 p_build_in_wip_flag in number) return number is
11
12 begin
13 if (p_source_org_id is NULL) and (p_source_supplier_id is null) THEN
14 if (p_planning_make_buy_code = 1) and (p_build_in_wip_flag= 1) THEN
15 return 3;
16 else
17 return 2;
18 end if;
19 elsif (p_org_id = p_source_org_id ) then
20 if (p_repetitive_type=2) then return 4;
21 elsif (p_build_in_wip_flag=1) then return 3;
22 else return 3;
23 end if;
24 elsif (p_source_org_id <>p_org_id ) then return 2;
25 else return 2;
26 end if;
27 end implement_code;
28
29 procedure populate_details(errbuf out nocopy varchar2, retcode out nocopy varchar2,
30 p_plan_id number, p_plan_run_id number default null)
31 is
32 l_plan_start_date date;
33 l_plan_cutoff_date date;
34 l_plan_type number;
35 l_sr_instance_id number;
36 l_refresh_mode number;
37 l_item_rn_qid number;
38 l_rowcount1 number := 0;
39 l_rowcount2 number := 0;
40 l_pegging_granularity number := 2;
41 l_owning_currency_code varchar2(20) := msc_phub_util.get_owning_currency_code(p_plan_run_id);
42 l_start_time timestamp := systimestamp;
43 begin
44
45 msc_phub_util.log('msc_supply_pkg.populate_details');
46 retcode :=0; -- this means successfully
47 errbuf :='';
48
49 -- ODS plan
50 if p_plan_id = -1
51 then
52 -- get refresh_mode
53 select refresh_mode into l_refresh_mode
54 from msc_plan_runs
55 where plan_run_id = p_plan_run_id;
56
57 if l_refresh_mode = 2 -- targeted refesh
58 then
59 l_item_rn_qid := msc_phub_util.get_item_rn_qid(p_plan_id, p_plan_run_id);
60
61 delete from msc_supplies_f
62 where plan_id = p_plan_id
63 and plan_run_id = p_plan_run_id
64 and (p_plan_id, sr_instance_id, organization_id, inventory_item_id) in
65 (select number1, number2, number3, number4 from msc_hub_query q where q.query_id = l_item_rn_qid);
66
67 l_rowcount1 := l_rowcount1 + sql%rowcount;
68 msc_phub_util.log('msc_supplies_f, delete='||sql%rowcount||', l_rowcount1='||l_rowcount1);
69 commit;
70
71 delete from msc_item_wips_f
72 where plan_id = p_plan_id
73 and plan_run_id = p_plan_run_id
74 and (p_plan_id, sr_instance_id, organization_id, inventory_item_id) in
75 (select number1, number2, number3, number4 from msc_hub_query q where q.query_id = l_item_rn_qid);
76
77 l_rowcount2 := l_rowcount2 + sql%rowcount;
78 msc_phub_util.log('msc_item_wips_f, delete='||sql%rowcount||', l_rowcount2='||l_rowcount2);
79 commit;
80 end if;
81 end if;
82
83 -- Successfully populated msc_supplies_f table for plan_id =' || p_plan_id || ',plan_run_id=' || p_plan_run_id;
84 -- initial there is no error message
85
86 select plan_type, sr_instance_id
87 into l_plan_type, l_sr_instance_id
88 from msc_plan_runs
89 where plan_id=p_plan_id
90 and plan_run_id=p_plan_run_id;
91
92 select trunc(plan_start_date), trunc(plan_cutoff_date)
93 into l_plan_start_date, l_plan_cutoff_date
94 from msc_plan_runs
95 where plan_run_id = p_plan_run_id;
96
97 if (l_plan_type=1) then
98 l_pegging_granularity := nvl(fnd_profile.value('MSC_APCC_END_ITEM_ENABLED'), 2);
99 end if;
100
101
102 msc_phub_util.log('msc_supply_pkg.populate_details: '||
103 p_plan_id||','||p_plan_run_id||','||l_plan_type||','||
104 l_sr_instance_id||','||l_plan_start_date||','||l_plan_cutoff_date||','||
105 l_refresh_mode||','||l_item_rn_qid||','||l_pegging_granularity);
106
107 ----------------------------------------------------
108 msc_phub_util.gather_table_stats(msc_phub_util.stats_pds, 'MSC_SUPPLIES');
109 msc_phub_util.gather_table_stats(msc_phub_util.stats_pds, 'MSC_FULL_PEGGING');
110 insert into msc_supplies_f (
111 plan_id, -- plan_id
112 plan_run_id,
113 io_plan_flag, --- this flag indidate whether it is an io plan
114 sr_instance_id,
115 organization_id,
116 subinventory_code,
117 owning_inst_id,
118 owning_org_id,
119 source_org_instance_id,
120 source_organization_id,
121 inventory_item_id,
122 end_item_inst_id,
123 end_item_org_id,
124 end_item_id,
125 parent_model_item_id,
126 project_id,
127 task_id,
128 supplier_id,
129 supplier_site_id,
130 customer_region_id,
131 ship_method,
132 part_condition,
133 supply_date,
134 aggr_type, category_set_id, sr_category_id,
135 end_item_cat_id,
136 parent_model_cat_id,
137 supply_type,
138 vmi_flag,
139 supply_qty,
140 planned_order_count,
141 work_order_leadtime, --- for work order (work order, planned work order)
142 work_order_count,
143 work_order_qty,
144 supply_volume,
145 po_reschedule_count,
146 po_count,
147 po_cancel_count,
148 buy_order_value,
149 buy_order_value2,
150 buy_order_count,
151 drp_supply_as_demand,
152 return_order_qty,
153 return_fcst,
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 p_plan_id, -- plan_id
160 p_plan_run_id, -- plan_run_id,
161 decode(l_plan_type,4,1,9,1,0) io_plan_flag,
162 supply_tbl.sr_instance_id,
163 supply_tbl.organization_id,
164 supply_tbl.subinventory_code,
165 supply_tbl.owning_inst_id,
166 supply_tbl.owning_org_id,
167 supply_tbl.source_org_instance_id,
168 supply_tbl.source_organization_id,
169 supply_tbl.inventory_item_id,
170 supply_tbl.end_item_inst_id,
171 supply_tbl.end_item_org_id,
172 supply_tbl.end_item_id,
173 supply_tbl.parent_model_item_id,
174 supply_tbl.project_id,
175 supply_tbl.task_id,
176 supply_tbl.supplier_id,
177 supply_tbl.supplier_site_id,
178 supply_tbl.customer_region_id,
179 --- if supply_date <l_plan_start_date, supply_date =l_plan_start_date-1
180 --- else if supply_date>l_curr_cutoff_date, supply_date=l_curr_cutoff_date+1
181 --- else supply_date
182 --- we can not simply put it at plan start date,
183 --- should be at the last working day of the bucket where plan start date is
184
185 supply_tbl.ship_method,
186 supply_tbl.part_condition,
187 decode(sign(to_number(supply_tbl.supply_date-l_plan_start_date)),
188 -1, msc_hub_calendar.last_work_date(p_plan_id,l_plan_start_date),
189 decode(supply_tbl.supply_type,
190 18, msc_hub_calendar.last_work_date(p_plan_id,supply_tbl.supply_date),
191 supply_tbl.supply_date)) supply_date,
192 to_number(0) aggr_type,
193 to_number(-23453) category_set_id,
194 to_number(-23453) sr_category_id,
195 to_number(-23453) end_item_cat_id,
196 to_number(-23453) parent_model_cat_id,
197 supply_tbl.supply_type,
198 supply_tbl.vmi_flag,
199 sum(supply_tbl.supply_qty) supply_qty,
200 sum(case when l_plan_type <>5 then supply_tbl.planned_order_count else to_number(null) end ) planned_order_count,
201 sum(case when l_plan_type <>5 then supply_tbl.work_order_leadtime else to_number(null) end ) work_order_leadtime,
202 sum(case when l_plan_type <>5 then supply_tbl.work_order_count else to_number(null) end ) work_order_count,
203 sum(case when l_plan_type <>5 then supply_tbl.work_order_qty else to_number(null) end ) work_order_qty,
204 sum(case when l_plan_type <>5 then supply_tbl.supply_volume else to_number(null) end ) supply_volume,
205 sum(supply_tbl.po_reschedule_count) po_reschedule_count,
206 sum(supply_tbl.po_count) po_count,
207 sum(supply_tbl.po_cancel_count) po_cancel_count,
208 sum(supply_tbl.buy_order_value) buy_order_value,
209 sum(supply_tbl.buy_order_value * decode(supply_tbl.currency_code,
210 fnd_profile.value('MSC_HUB_CUR_CODE_RPT'), 1, nvl(mcc.conv_rate,0))) buy_order_value2,
211 sum(supply_tbl.buy_order_count) buy_order_count,
212 sum(case when l_plan_type <>5 then supply_tbl.drp_supply_as_demand else to_number(null) end ) drp_supply_as_demand,
213 sum(case when l_plan_type <>5 then supply_tbl.return_order_qty else to_number(null) end ) return_order_qty,
214 sum(case when l_plan_type <>5 then supply_tbl.return_fcst else to_number(null) end ) return_fcst,
215
216 fnd_global.user_id, sysdate,
217 sysdate, fnd_global.user_id, fnd_global.login_id,
218 fnd_global.conc_program_id, fnd_global.conc_login_id,
219 fnd_global.prog_appl_id, fnd_global.conc_request_id
220 from
221 (select /*+ ordered */
222 decode(sign(ms.organization_id), -1, -23453, ms.sr_instance_id) sr_instance_id,
223 decode(sign(ms.organization_id), -1, -23453, ms.organization_id) organization_id,
224 nvl(mtp.currency_code, l_owning_currency_code) currency_code,
225 decode(ms.order_type, 18, nvl(ms.subinventory_code, '-23453'), '-23453') subinventory_code,
226 decode(sign(ms.sr_instance_id), -1, l_sr_instance_id, ms.sr_instance_id) owning_inst_id,
227 decode(sign(ms.organization_id), -1, msc_hub_calendar.get_item_org(p_plan_id, ms.inventory_item_id,
228 decode(sign(ms.sr_instance_id), -1, l_sr_instance_id, ms.sr_instance_id)),
229 ms.organization_id) owning_org_id,
230 decode(ms.source_sr_instance_id,null,-23453,0,-23453,ms.source_sr_instance_id) source_org_instance_id,
231 decode(ms.source_organization_id,null,-23453,0,-23453,ms.source_organization_id) source_organization_id,
232 ms.inventory_item_id,
233 nvl(pg.end_item_inst_id, -23453) end_item_inst_id,
234 nvl(pg.end_item_org_id, -23453) end_item_org_id,
235 nvl(pg.end_item_id, -23453) end_item_id,
236 nvl(pg.parent_model_item_id, -23453) parent_model_item_id,
237 nvl(ms.project_id,-23453) project_id,
238 nvl(ms.task_id,-23453) task_id,
239 nvl(case when ms.order_type in (5,51) then ms.source_supplier_id else ms.supplier_id end, -23453) supplier_id,
240 nvl(case when ms.order_type in (5,51) then ms.source_supplier_site_id else ms.supplier_site_id end, -23453) supplier_site_id,
241 decode(l_plan_type, 8, nvl(ms.zone_id,-23453), -23453) customer_region_id,
242 nvl(ms.ship_method, '-23453') ship_method,
243 nvl(ms.item_type_value,1) part_condition,
244 trunc(nvl(ms.firm_date,ms.new_schedule_date)) supply_date,
245 ms.order_type supply_type,
246 nvl(msi.vmi_flag,0) vmi_flag,
247
248 sum(nvl(pg.supply_qty,
249 decode(msi.base_item_id,null,
250 decode(ms.disposition_status_type,2, 0,
251 decode(ms.last_unit_completion_date, null, nvl(ms.firm_quantity, ms.new_order_quantity), ms.daily_rate)),
252 decode(ms.last_unit_completion_date, null, nvl(ms.firm_quantity, ms.new_order_quantity), ms.daily_rate)))) supply_qty,
253
254 sum(decode(ms.order_type,5,
255 decode(msi.base_item_id,null,
256 decode(ms.disposition_status_type, 2, 0,1),1),to_number(null))) planned_order_count,
257
258
259 ---------------------------------------------------------------------------
260 --- ??? exclude if new_schedule_date is null
261 --- decode(nvl(ms.source_organization_id, ms.organization_id),
262 -- ms.organization_id,
263 -- PLANNED_MAKE_OFF,
264 -- PLANNED_BUY_OFF)
265 -- make order 3,7,14,15,27,28,
266 -- 4,13 ?? do we need to include Repetitive schdule as make order??
267 -- make planned order
268 -- 3,4,5,7,13,14,15,16,17,27,28,30
269 ---------------------------------------------------------------------------
270 sum(decode(ms.order_type,3, nvl(ms.new_schedule_date,null)-nvl(ms.new_wip_start_date,null),
271 5,decode(implement_code(ms.source_organization_id,ms.organization_id,
275 4,nvl(ms.new_schedule_date,null)- nvl(ms.first_unit_start_date,null),
272 msi.repetitive_type,ms.source_supplier_id,
273 msi.planning_make_buy_code,msi.build_in_wip_flag),
274 3,nvl(ms.new_schedule_date,null)-nvl(ms.new_wip_start_date,null),
276 0),
277 7,nvl(ms.new_schedule_date,null)-nvl(ms.new_wip_start_date,null),
278 14,nvl(ms.new_schedule_date,null)-nvl(ms.new_wip_start_date,null),
279 15,nvl(ms.new_schedule_date,null)-nvl(ms.new_wip_start_date,null),
280 27,nvl(ms.new_schedule_date,null)-nvl(ms.new_wip_start_date,null),
281 28,nvl(ms.new_schedule_date,null)-nvl(ms.new_wip_start_date,null),
282 4,nvl(ms.new_schedule_date,null)- nvl(ms.first_unit_start_date,null),
283 13,nvl(ms.new_schedule_date,null)- nvl(ms.first_unit_start_date,null),
284 16,nvl(ms.new_schedule_date,null)- nvl(ms.first_unit_start_date,null),
285 17,nvl(ms.new_schedule_date,null)-nvl(ms.new_wip_start_date,null),
286 30,nvl(ms.new_schedule_date,null)- nvl(ms.first_unit_start_date,null),
287 88,nvl(ms.new_schedule_date,null)- nvl(ms.first_unit_start_date,null),
288 to_number(null))) work_order_leadtime,
289
290 sum(decode(ms.order_type,3,1,
291 5,decode(implement_code(ms.source_organization_id,ms.organization_id,
292 msi.repetitive_type,ms.source_supplier_id,
293 msi.planning_make_buy_code,msi.build_in_wip_flag),
294 3,1,0),
295 7,1,
296 14,1,
297 15,1,
298 27,1,
299 28,1,
300 4,1,
301 13,1,
302 16,1,
303 17,1,
304 30,1,
305 88,1,
306 to_number(null))) work_order_count,
307
308 sum(case when ms.order_type in (3,4,7,13,14,15,16,17,27,28,30,88)
309 and (msi.base_item_id is not null or ms.disposition_status_type<>2)
310 then decode(ms.last_unit_completion_date, null, ms.new_order_quantity, ms.daily_rate)
311 when ms.order_type in (5)
312 and implement_code(ms.source_organization_id, ms.organization_id, msi.repetitive_type,
313 ms.source_supplier_id, msi.planning_make_buy_code, msi.build_in_wip_flag) in (3,4)
314 and (msi.base_item_id is not null or ms.disposition_status_type<>2)
315 then decode(ms.last_unit_completion_date, null, ms.new_order_quantity, ms.daily_rate)
316 else null end) work_order_qty,
317
318 --- order_type in (1,2,18) and nvl(ms.item_type_id,401) = 401 and nvl(ms.item_type_value,1) = 2
319
320 sum(case when l_plan_type in (8,9) and ms.order_type in (1,2,18)
321 and nvl(ms.item_type_id,401)=401 and nvl(ms.item_type_value,1)=2
322 and (msi.base_item_id is not null or ms.disposition_status_type<>2)
323 then decode(ms.last_unit_completion_date, null, ms.new_order_quantity, ms.daily_rate)
324 else null end) return_order_qty,
325
326 sum(decode(ms.order_type,81,ms.new_order_quantity,0)) return_fcst,
327
328 sum(case when l_plan_type=5
329 and (ms.order_type in (1,51) or (ms.order_type in (2) and ms.supplier_id is not null))
330 and ms.organization_id<>ms.source_organization_id
331 and (msi.base_item_id is not null or ms.disposition_status_type<>2)
332 then decode(ms.last_unit_completion_date,null,ms.new_order_quantity,ms.daily_rate)
333 end) drp_supply_as_demand,
334
335
336
337 sum(case when ms.order_type=5
338 and nvl(ms.source_organization_id,-23453)<>ms.organization_id then nvl(ms.firm_quantity, ms.new_order_quantity)
339 when ms.order_type in (1,2,8,51,53,76,80,87) then ms.new_order_quantity else null end) supply_volume,
340
341 sum(case when ms.order_type in (1) and ms.reschedule_flag is not null
342 and ms.new_schedule_date<>ms.old_schedule_date
343 then 1 else 0 end) po_reschedule_count,
344
345 sum(case when ms.order_type in (1) then 1 else 0 end) po_count,
346
347 sum(case when ms.order_type in (1) and ms.disposition_status_type=2
348 then 1 else 0 end) po_cancel_count,
349
350 sum(case when (ms.order_type in (1,2,76) or
351 (ms.order_type in (5) and msi.purchasing_enabled_flag=1))
352 then ms.new_order_quantity * nvl(ms.delivery_price, msi.standard_cost)
353 else null end) buy_order_value,
354
355 sum(case when (ms.order_type in (1,2,76) or
356 (ms.order_type in (5) and msi.purchasing_enabled_flag=1))
357 then 1 else 0 end) buy_order_count
358
359 from
360 msc_supplies ms,
361 (select
362 mfp.transaction_id,
363 mfp.sr_instance_id,
364 mfp2.sr_instance_id end_item_inst_id,
365 mfp2.organization_id end_item_org_id,
366 mfp2.inventory_item_id end_item_id,
367 nvl(decode(l_pegging_granularity, 1, md.parent_model_item_id, md.top_model_item_id), -23453) parent_model_item_id,
368 sum(mfp.allocated_quantity) supply_qty
369 from msc_full_pegging mfp,
373 where l_pegging_granularity in (0,1)
370 msc_full_pegging mfp2,
371 msc_demands md,
372 msc_apcc_item_d msi
374 and mfp.plan_id=p_plan_id
375 and mfp.plan_id=mfp2.plan_id
376 and mfp.end_pegging_id=mfp2.end_pegging_id
377 and mfp2.prev_pegging_id is null
378 and mfp2.plan_id=md.plan_id(+)
379 and mfp2.demand_id=md.demand_id(+)
380 and mfp.plan_id=msi.plan_id
381 and mfp.sr_instance_id=msi.sr_instance_id
382 and mfp.organization_id=msi.organization_id
383 and mfp.inventory_item_id=msi.inventory_item_id
384 and msi.pegging_sr_category_id>0
385 group by
386 mfp.transaction_id,
387 mfp.sr_instance_id,
388 mfp2.sr_instance_id,
389 mfp2.organization_id,
390 mfp2.inventory_item_id,
391 nvl(decode(l_pegging_granularity, 1, md.parent_model_item_id, md.top_model_item_id), -23453)
392 ) pg,
393 msc_apcc_item_d msi,
394 msc_trading_partners mtp
395 where ms.plan_id = msi.plan_id
396 and decode(sign(ms.sr_instance_id), -1, l_sr_instance_id, ms.sr_instance_id) = msi.sr_instance_id
397 and decode(sign(ms.organization_id), -1, msc_hub_calendar.get_item_org(p_plan_id, ms.inventory_item_id,
398 decode(sign(ms.sr_instance_id), -1, l_sr_instance_id, ms.sr_instance_id)),
399 ms.organization_id) = msi.organization_id
400 and ms.inventory_item_id = msi.inventory_item_id
401 and ms.plan_id=p_plan_id
402 and not (l_plan_type=8 and ms.order_type in (3)) -- bug 9123354, 10044668
403 and (p_plan_id <> -1
404 or ( p_plan_id = -1
405 and ms.sr_instance_id = l_sr_instance_id
406 and (l_refresh_mode = 1
407 or (l_refresh_mode = 2 and (p_plan_id, ms.sr_instance_id, ms.organization_id, ms.inventory_item_id) in
408 (select number1, number2, number3, number4 from msc_hub_query q where q.query_id = l_item_rn_qid) ) )
409 and trunc(nvl(ms.firm_date,ms.new_schedule_date)) between l_plan_start_date and l_plan_cutoff_date
410 )
411 )
412 and ms.transaction_id=pg.transaction_id(+)
413 and ms.sr_instance_id=pg.sr_instance_id(+)
414 and ms.sr_instance_id=mtp.sr_instance_id
415 and ms.organization_id=mtp.sr_tp_id
416 and mtp.partner_type=3
417 group by
418 decode(sign(ms.organization_id), -1, -23453, ms.sr_instance_id),
419 decode(sign(ms.organization_id), -1, -23453, ms.organization_id),
420 nvl(mtp.currency_code, l_owning_currency_code),
421 decode(ms.order_type, 18, nvl(ms.subinventory_code, '-23453'), '-23453'),
422 decode(sign(ms.sr_instance_id), -1, l_sr_instance_id, ms.sr_instance_id),
423 decode(sign(ms.organization_id), -1, msc_hub_calendar.get_item_org(p_plan_id, ms.inventory_item_id,
424 decode(sign(ms.sr_instance_id), -1, l_sr_instance_id, ms.sr_instance_id)),
425 ms.organization_id),
426 decode(ms.source_sr_instance_id,null,-23453,0,-23453,ms.source_sr_instance_id),
427 decode(ms.source_organization_id,null,-23453,0,-23453,ms.source_organization_id),
428 ms.inventory_item_id,
429 nvl(pg.end_item_inst_id, -23453),
430 nvl(pg.end_item_org_id, -23453),
431 nvl(pg.end_item_id, -23453),
432 nvl(pg.parent_model_item_id, -23453),
433 nvl(ms.project_id,-23453),
434 nvl(ms.task_id,-23453),
435 nvl(case when ms.order_type in (5,51) then ms.source_supplier_id else ms.supplier_id end, -23453),
436 nvl(case when ms.order_type in (5,51) then ms.source_supplier_site_id else ms.supplier_site_id end, -23453),
437 decode(l_plan_type, 8, nvl(ms.zone_id,-23453), -23453),
438 nvl(ms.ship_method, '-23453'),
439 nvl(ms.item_type_value,1),
440 trunc(nvl(ms.firm_date,ms.new_schedule_date)),
441 ms.order_type,
442 nvl(msi.vmi_flag,0)
443 ) supply_tbl,
444 msc_currency_conv_mv mcc
445 where mcc.from_currency(+)=supply_tbl.currency_code
446 and mcc.to_currency(+)=fnd_profile.value('MSC_HUB_CUR_CODE_RPT')
447 and mcc.calendar_date(+)=supply_tbl.supply_date
448 group by
449 decode(l_plan_type,4,1,9,1,0),
450 supply_tbl.sr_instance_id,
451 supply_tbl.organization_id,
452 supply_tbl.subinventory_code,
453 supply_tbl.owning_inst_id,
454 supply_tbl.owning_org_id,
455 supply_tbl.source_org_instance_id,
456 supply_tbl.source_organization_id,
457 supply_tbl.inventory_item_id,
458 supply_tbl.end_item_inst_id,
459 supply_tbl.end_item_org_id,
460 supply_tbl.end_item_id,
461 supply_tbl.parent_model_item_id,
462 supply_tbl.project_id,
463 supply_tbl.task_id,
464 supply_tbl.supplier_id,
465 supply_tbl.supplier_site_id,
466 supply_tbl.customer_region_id,
467 supply_tbl.ship_method,
468 supply_tbl.part_condition,
469 decode(sign(to_number(supply_tbl.supply_date-l_plan_start_date)),
470 -1, msc_hub_calendar.last_work_date(p_plan_id,l_plan_start_date),
471 decode(supply_tbl.supply_type,
475 supply_tbl.vmi_flag;
472 18, msc_hub_calendar.last_work_date(p_plan_id,supply_tbl.supply_date),
473 supply_tbl.supply_date)),
474 supply_tbl.supply_type,
476
477 l_rowcount1 := l_rowcount1 + sql%rowcount;
478 msc_phub_util.log('msc_supplies_f, insert='||sql%rowcount||', l_rowcount1='||l_rowcount1);
479 commit;
480 msc_phub_util.gather_table_stats(msc_phub_util.stats_fact, 'MSC_SUPPLIES_F', p_plan_run_id);
481
482
483 --- populate wip start qty
484 --- msc_item_wip_f
485 ---
486
487 insert into msc_item_wips_f (
488 plan_id, -- plan_id
489 plan_run_id,
490 sr_instance_id,
491 organization_id,
492 inventory_item_id,
493 vmi_flag,
494 wip_start_date,
495 aggr_type, category_set_id, sr_category_id,
496 wip_qty,
497 created_by, creation_date,
498 last_update_date, last_updated_by, last_update_login,
499 program_id, program_login_id,
500 program_application_id, request_id)
501 select
502 p_plan_id, -- plan_id
503 p_plan_run_id, -- plan_run_id,
504 wip_tbl.sr_instance_id,
505 wip_tbl.organization_id,
506 wip_tbl.inventory_item_id,
507 wip_tbl.vmi_flag,
508 decode(sign(to_number(wip_tbl.wip_start_date-l_plan_start_date)),-1,l_plan_start_date,wip_tbl.wip_start_date),
509 to_number(0) aggr_type,
510 to_number(-23453) category_set_id,
511 to_number(-23453) sr_category_id,
512 sum(wip_tbl.wip_qty),
513 fnd_global.user_id, sysdate,
514 sysdate, fnd_global.user_id, fnd_global.login_id,
515 fnd_global.conc_program_id, fnd_global.conc_login_id,
516 fnd_global.prog_appl_id, fnd_global.conc_request_id
517 from
518 (select
519 ms.sr_instance_id,
520 ms.organization_id,
521 ms.inventory_item_id,
522 nvl(msi.vmi_flag, 0) vmi_flag,
523 trunc(nvl(nvl(ms.new_wip_start_date,ms.first_unit_start_date),l_plan_start_date)) wip_start_date,
524 -- make order 3,7,14,15,27,28,
525 -- 4,13 ?? do we need to include Repetitive schdule as make order??
526 -- make planned order
527 ---------------------------------------------------------------------------
528 sum(case when ms.order_type in (3,4,7,13,14,15,16,27,28,30,88)
529 and (msi.base_item_id is not null or ms.disposition_status_type<>2)
530 then decode(ms.last_unit_completion_date, null, ms.new_order_quantity, ms.daily_rate)
531 when ms.order_type in (5,17)
532 and implement_code(ms.source_organization_id, ms.organization_id, msi.repetitive_type,
533 ms.source_supplier_id, msi.planning_make_buy_code, msi.build_in_wip_flag) in (3,4)
534 and (msi.base_item_id is not null or ms.disposition_status_type<>2)
535 then decode(ms.last_unit_completion_date, null, ms.new_order_quantity, ms.daily_rate)
536 else null end) wip_qty
537 from
538 msc_supplies ms,
539 msc_apcc_item_d msi
540 where ms.plan_id = msi.plan_id
541 and ms.sr_instance_id = msi.sr_instance_id
542 and ms.organization_id =msi.organization_id
543 and ms.inventory_item_id = msi.inventory_item_id
544 and ms.plan_id=p_plan_id
545 and l_plan_type not in (4,9) --- exclude io plan
546 and (p_plan_id <> -1
547 or ( p_plan_id = -1
548 and ms.sr_instance_id = l_sr_instance_id
549 and (l_refresh_mode = 1
550 or (l_refresh_mode = 2 and (p_plan_id, ms.sr_instance_id, ms.organization_id, ms.inventory_item_id) in
551 (select number1, number2, number3, number4 from msc_hub_query q where q.query_id = l_item_rn_qid) ) )
552 and trunc(nvl(nvl(ms.new_wip_start_date,ms.first_unit_start_date),l_plan_start_date)) between l_plan_start_date and l_plan_cutoff_date
553 )
554 )
555 group by
556 ms.sr_instance_id,
557 ms.organization_id,
558 ms.inventory_item_id,
559 nvl(msi.vmi_flag, 0),
560 trunc(nvl(nvl(ms.new_wip_start_date,ms.first_unit_start_date),l_plan_start_date)) ) wip_tbl
561 -- where l_plan_type <> 6
562 group by
563 p_plan_id, -- plan_id
564 p_plan_run_id, -- plan_run_id,
565 wip_tbl.sr_instance_id,
566 wip_tbl.organization_id,
567 wip_tbl.inventory_item_id,
568 wip_tbl.vmi_flag,
569 decode(sign(to_number(wip_tbl.wip_start_date-l_plan_start_date)),-1,l_plan_start_date,wip_tbl.wip_start_date);
570
571 l_rowcount2 := l_rowcount2 + sql%rowcount;
572 msc_phub_util.log('msc_item_wips_f, insert='||sql%rowcount||', l_rowcount2='||l_rowcount2);
573 commit;
574 msc_phub_util.gather_table_stats(msc_phub_util.stats_fact, 'MSC_ITEM_WIPS_F', p_plan_run_id);
575
576 if (l_rowcount1 > 0) then
577 summarize_supplies_f(errbuf, retcode, p_plan_id, p_plan_run_id);
578 end if;
579
580 if (l_rowcount2 > 0) then
581 summarize_item_wips_f(errbuf, retcode, p_plan_id, p_plan_run_id);
582 end if;
583
584 msc_phub_util.log('msc_supply_pkg.populate_details.complete, duration='||(systimestamp-l_start_time));
585
586 exception
587 when no_data_found then
588 retcode :=2;
589 errbuf := msc_phub_util.get_planning_hub_message('MSC_HUB_DUPLICATE_DATA')||SQLCODE||' -ERROR- '||sqlerrm;
590 msc_phub_util.log('msc_supply_pkg.populate_details.exception: '||errbuf);
594 retcode :=2;
591
592
593 when dup_val_on_index then
595 errbuf := msc_phub_util.get_planning_hub_message('MSC_HUB_DUPLICATE_DATA')||SQLCODE||' -ERROR- '||sqlerrm;
596 msc_phub_util.log('msc_supply_pkg.populate_details.exception: '||errbuf);
597
598 when others then
599 retcode :=2;
600 errbuf := msc_phub_util.get_planning_hub_message('MSC_HUB_POPULATE_ERROR')||SQLCODE||' -ERROR- '||sqlerrm;
601 msc_phub_util.log('msc_supply_pkg.populate_details.exception: '||errbuf);
602
603
604 end populate_details;
605
606
607 procedure summarize_supplies_f(errbuf out nocopy varchar2, retcode out nocopy varchar2,
608 p_plan_id number, p_plan_run_id number)
609 is
610 l_category_set_id1 number := fnd_profile.value('MSC_HUB_CAT_SET_ID_1');
611 begin
612 msc_phub_util.log('msc_supply_pkg.summarize_supplies_f');
613 retcode := 0;
614 errbuf := '';
615
616 delete from msc_supplies_f
617 where plan_id=p_plan_id and plan_run_id=p_plan_run_id and aggr_type>0;
618 msc_phub_util.log('msc_supply_pkg.summarize_supplies_f, delete='||sql%rowcount);
619 commit;
620
621 -- level 1
622 insert into msc_supplies_f (
623 plan_id, plan_run_id, io_plan_flag,
624 sr_instance_id, organization_id, subinventory_code,
625 owning_inst_id, owning_org_id, inventory_item_id,
626 source_org_instance_id, source_organization_id,
627 end_item_inst_id,
628 end_item_org_id,
629 end_item_id,
630 parent_model_item_id,
631 project_id, task_id,
632 supplier_id, supplier_site_id,
633 ship_method, customer_region_id,
634 part_condition,
635 supply_date,
636 aggr_type, category_set_id, sr_category_id,
637 end_item_cat_id,
638 parent_model_cat_id,
639 supply_type, vmi_flag,
640 supply_qty,
641 planned_order_count,
642 work_order_leadtime,
643 work_order_count,
644 work_order_qty,
645 supply_volume,
646 po_reschedule_count,
647 po_count,
648 po_cancel_count,
649 buy_order_value,
650 buy_order_value2,
651 buy_order_count,
652 drp_supply_as_demand,
653 return_order_qty,
654 return_fcst,
655 created_by, creation_date,
656 last_update_date, last_updated_by, last_update_login,
657 program_id, program_login_id,
658 program_application_id, request_id)
659 -- category (42, 43, 44)
660 select
661 f.plan_id, f.plan_run_id, f.io_plan_flag,
662 f.sr_instance_id, f.organization_id, f.subinventory_code,
663 f.owning_inst_id, to_number(-23453) owning_org_id,
664 to_number(-23453) inventory_item_id,
665 f.source_org_instance_id, f.source_organization_id,
666 f.end_item_inst_id,
667 to_number(-23453) end_item_org_id,
668 to_number(-23453) end_item_id,
669 to_number(-23453) parent_model_item_id,
670 f.project_id, f.task_id,
671 f.supplier_id, f.supplier_site_id,
672 f.ship_method, f.customer_region_id,
673 f.part_condition,
674 f.supply_date,
675 to_number(42) aggr_type,
676 l_category_set_id1 category_set_id,
677 nvl(ic1.sr_category_id, -23453),
678 nvl(ic2.sr_category_id, -23453),
679 nvl(ic3.sr_category_id, -23453),
680 f.supply_type, f.vmi_flag,
681 sum(f.supply_qty),
682 sum(f.planned_order_count),
683 sum(f.work_order_leadtime),
684 sum(f.work_order_count),
685 sum(f.work_order_qty),
686 sum(f.supply_volume),
687 sum(f.po_reschedule_count),
688 sum(f.po_count),
689 sum(f.po_cancel_count),
690 sum(f.buy_order_value),
691 sum(f.buy_order_value2),
692 sum(f.buy_order_count),
693 sum(f.drp_supply_as_demand),
694 sum(f.return_order_qty),
695 sum(f.return_fcst),
696 fnd_global.user_id, sysdate,
697 sysdate, fnd_global.user_id, fnd_global.login_id,
698 fnd_global.conc_program_id, fnd_global.conc_login_id,
699 fnd_global.prog_appl_id, fnd_global.conc_request_id
700 from
701 msc_supplies_f f,
702 msc_phub_item_categories_mv ic1,
703 msc_phub_item_categories_mv ic2,
704 msc_phub_item_categories_mv ic3
705 where f.plan_id = p_plan_id and f.plan_run_id = p_plan_run_id
706 and f.aggr_type=0
707 and f.owning_inst_id=ic1.sr_instance_id(+)
708 and f.owning_org_id=ic1.organization_id(+)
709 and f.inventory_item_id=ic1.inventory_item_id(+)
710 and ic1.category_set_id(+)=l_category_set_id1
711 and f.end_item_inst_id=ic2.sr_instance_id(+)
712 and f.end_item_org_id=ic2.organization_id(+)
713 and f.end_item_id=ic2.inventory_item_id(+)
714 and ic2.category_set_id(+)=l_category_set_id1
715 and f.end_item_inst_id=ic3.sr_instance_id(+)
716 and f.end_item_org_id=ic3.organization_id(+)
717 and f.parent_model_item_id=ic3.inventory_item_id(+)
718 and ic3.category_set_id(+)=l_category_set_id1
719 group by
720 f.plan_id, f.plan_run_id, f.io_plan_flag,
724 f.end_item_inst_id,
721 f.sr_instance_id, f.organization_id, f.subinventory_code,
722 f.owning_inst_id,
723 f.source_org_instance_id, f.source_organization_id,
725 f.project_id, f.task_id,
726 f.supplier_id, f.supplier_site_id,
727 f.ship_method, f.customer_region_id,
728 f.part_condition,
729 f.supply_date,
730 nvl(ic1.sr_category_id, -23453),
731 nvl(ic2.sr_category_id, -23453),
732 nvl(ic3.sr_category_id, -23453),
733 f.supply_type, f.vmi_flag;
734
735 msc_phub_util.log('msc_supply_pkg.summarize_supplies_f, level1='||sql%rowcount);
736 commit;
737
738 -- level 2
739 insert into msc_supplies_f (
740 plan_id, plan_run_id, io_plan_flag,
741 sr_instance_id, organization_id, subinventory_code,
742 owning_inst_id, owning_org_id, inventory_item_id,
743 source_org_instance_id, source_organization_id,
744 end_item_inst_id,
745 end_item_org_id,
746 end_item_id,
747 parent_model_item_id,
748 project_id, task_id,
749 supplier_id, supplier_site_id,
750 ship_method, customer_region_id,
751 part_condition,
752 supply_date,
753 aggr_type, category_set_id, sr_category_id,
754 end_item_cat_id,
755 parent_model_cat_id,
756 supply_type, vmi_flag,
757 supply_qty,
758 planned_order_count,
759 work_order_leadtime,
760 work_order_count,
761 work_order_qty,
762 supply_volume,
763 po_reschedule_count,
764 po_count,
765 po_cancel_count,
766 buy_order_value,
767 buy_order_value2,
768 buy_order_count,
769 drp_supply_as_demand,
770 return_order_qty,
771 return_fcst,
772 created_by, creation_date,
773 last_update_date, last_updated_by, last_update_login,
774 program_id, program_login_id,
775 program_application_id, request_id)
776 -- category-mfg_period (1016, 1017, 1018)
777 select
778 f.plan_id, f.plan_run_id, f.io_plan_flag,
779 f.sr_instance_id, f.organization_id, f.subinventory_code,
780 f.owning_inst_id, f.owning_org_id, f.inventory_item_id,
781 f.source_org_instance_id, f.source_organization_id,
782 f.end_item_inst_id,
783 f.end_item_org_id,
784 f.end_item_id,
785 f.parent_model_item_id,
786 f.project_id, f.task_id,
787 f.supplier_id, f.supplier_site_id,
788 f.ship_method, f.customer_region_id,
789 f.part_condition,
790 d.mfg_period_start_date supply_date,
791 decode(f.aggr_type, 42, 1016, 43, 1017, 1018) aggr_type,
792 f.category_set_id, f.sr_category_id,
793 f.end_item_cat_id,
794 f.parent_model_cat_id,
795 f.supply_type, f.vmi_flag,
796 sum(f.supply_qty),
797 sum(f.planned_order_count),
798 sum(f.work_order_leadtime),
799 sum(f.work_order_count),
800 sum(f.work_order_qty),
801 sum(f.supply_volume),
802 sum(f.po_reschedule_count),
803 sum(f.po_count),
804 sum(f.po_cancel_count),
805 sum(f.buy_order_value),
806 sum(f.buy_order_value2),
807 sum(f.buy_order_count),
808 sum(f.drp_supply_as_demand),
809 sum(f.return_order_qty),
810 sum(f.return_fcst),
811 fnd_global.user_id, sysdate,
812 sysdate, fnd_global.user_id, fnd_global.login_id,
813 fnd_global.conc_program_id, fnd_global.conc_login_id,
814 fnd_global.prog_appl_id, fnd_global.conc_request_id
815 from
816 msc_supplies_f f,
817 msc_phub_dates_mv d
818 where f.plan_id = p_plan_id and f.plan_run_id = p_plan_run_id
819 and f.aggr_type between 42 and 44
820 and f.supply_date = d.calendar_date
821 and d.mfg_period_start_date is not null
822 group by
823 f.plan_id, f.plan_run_id, f.io_plan_flag,
824 f.sr_instance_id, f.organization_id, f.subinventory_code,
825 f.owning_inst_id, f.owning_org_id, f.inventory_item_id,
826 f.source_org_instance_id, f.source_organization_id,
827 f.end_item_inst_id,
828 f.end_item_org_id,
829 f.end_item_id,
830 f.parent_model_item_id,
831 f.project_id, f.task_id,
832 f.supplier_id, f.supplier_site_id,
833 f.ship_method, f.customer_region_id,
834 f.part_condition,
835 d.mfg_period_start_date,
836 decode(f.aggr_type, 42, 1016, 43, 1017, 1018),
837 f.category_set_id, f.sr_category_id,
838 f.end_item_cat_id,
839 f.parent_model_cat_id,
840 f.supply_type, f.vmi_flag
841 union all
842 -- category-fiscal_period (1019, 1020, 1021)
843 select
844 f.plan_id, f.plan_run_id, f.io_plan_flag,
845 f.sr_instance_id, f.organization_id, f.subinventory_code,
846 f.owning_inst_id, f.owning_org_id, f.inventory_item_id,
847 f.source_org_instance_id, f.source_organization_id,
848 f.end_item_inst_id,
849 f.end_item_org_id,
850 f.end_item_id,
851 f.parent_model_item_id,
852 f.project_id, f.task_id,
856 d.fis_period_start_date supply_date,
853 f.supplier_id, f.supplier_site_id,
854 f.ship_method, f.customer_region_id,
855 f.part_condition,
857 decode(f.aggr_type, 42, 1019, 43, 1020, 1021) aggr_type,
858 f.category_set_id, f.sr_category_id,
859 f.end_item_cat_id,
860 f.parent_model_cat_id,
861 f.supply_type, f.vmi_flag,
862 sum(f.supply_qty),
863 sum(f.planned_order_count),
864 sum(f.work_order_leadtime),
865 sum(f.work_order_count),
866 sum(f.work_order_qty),
867 sum(f.supply_volume),
868 sum(f.po_reschedule_count),
869 sum(f.po_count),
870 sum(f.po_cancel_count),
871 sum(f.buy_order_value),
872 sum(f.buy_order_value2),
873 sum(f.buy_order_count),
874 sum(f.drp_supply_as_demand),
875 sum(f.return_order_qty),
876 sum(f.return_fcst),
877 fnd_global.user_id, sysdate,
878 sysdate, fnd_global.user_id, fnd_global.login_id,
879 fnd_global.conc_program_id, fnd_global.conc_login_id,
880 fnd_global.prog_appl_id, fnd_global.conc_request_id
881 from
882 msc_supplies_f f,
883 msc_phub_dates_mv d
884 where f.plan_id = p_plan_id and f.plan_run_id = p_plan_run_id
885 and f.aggr_type between 42 and 44
886 and f.supply_date = d.calendar_date
887 and d.fis_period_start_date is not null
888 group by
889 f.plan_id, f.plan_run_id, f.io_plan_flag,
890 f.sr_instance_id, f.organization_id, f.subinventory_code,
891 f.owning_inst_id, f.owning_org_id, f.inventory_item_id,
892 f.source_org_instance_id, f.source_organization_id,
893 f.end_item_inst_id,
894 f.end_item_org_id,
895 f.end_item_id,
896 f.parent_model_item_id,
897 f.project_id, f.task_id,
898 f.supplier_id, f.supplier_site_id,
899 f.ship_method, f.customer_region_id,
900 f.part_condition,
901 d.fis_period_start_date,
902 decode(f.aggr_type, 42, 1019, 43, 1020, 1021),
903 f.category_set_id, f.sr_category_id,
904 f.end_item_cat_id,
905 f.parent_model_cat_id,
906 f.supply_type, f.vmi_flag;
907
908 msc_phub_util.log('msc_supply_pkg.summarize_supplies_f, level2='||sql%rowcount);
909 commit;
910
911 exception
912 when others then
913 retcode := 2;
914 errbuf := 'msc_supply_pkg.summarize_supplies_f: '||sqlerrm;
915 raise;
916 end summarize_supplies_f;
917
918 procedure summarize_item_wips_f(errbuf out nocopy varchar2, retcode out nocopy varchar2,
919 p_plan_id number, p_plan_run_id number)
920 is
921 l_category_set_id1 number := fnd_profile.value('MSC_HUB_CAT_SET_ID_1');
922 begin
923 msc_phub_util.log('msc_supply_pkg.summarize_item_wips_f');
924 retcode := 0;
925 errbuf := '';
926
927 delete from msc_item_wips_f
928 where plan_id=p_plan_id and plan_run_id=p_plan_run_id and aggr_type>0;
929 msc_phub_util.log('msc_supply_pkg.summarize_item_wips_f, delete='||sql%rowcount);
930 commit;
931
932 -- level 1
933 insert into msc_item_wips_f (
934 plan_id, plan_run_id,
935 sr_instance_id, organization_id, inventory_item_id,
936 vmi_flag, wip_start_date,
937 aggr_type, category_set_id, sr_category_id,
938 wip_qty,
939 created_by, creation_date,
940 last_update_date, last_updated_by, last_update_login,
941 program_id, program_login_id,
942 program_application_id, request_id)
943 -- category (42, 43, 44)
944 select
945 f.plan_id, f.plan_run_id,
946 f.sr_instance_id, f.organization_id,
947 to_number(-23453) inventory_item_id,
948 f.vmi_flag, f.wip_start_date,
949 to_number(42) aggr_type,
950 l_category_set_id1 category_set_id,
951 nvl(q.sr_category_id, -23453),
952 sum(f.wip_qty),
953 fnd_global.user_id, sysdate,
954 sysdate, fnd_global.user_id, fnd_global.login_id,
955 fnd_global.conc_program_id, fnd_global.conc_login_id,
956 fnd_global.prog_appl_id, fnd_global.conc_request_id
957 from
958 msc_item_wips_f f,
959 msc_phub_item_categories_mv q
960 where f.plan_id = p_plan_id and f.plan_run_id = p_plan_run_id
961 and f.aggr_type=0
962 and f.sr_instance_id=q.sr_instance_id(+)
963 and f.organization_id=q.organization_id(+)
964 and f.inventory_item_id=q.inventory_item_id(+)
965 and q.category_set_id(+)=l_category_set_id1
966 group by
967 f.plan_id, f.plan_run_id,
968 f.sr_instance_id, f.organization_id,
969 f.vmi_flag, f.wip_start_date,
970 nvl(q.sr_category_id, -23453);
971
972 msc_phub_util.log('msc_supply_pkg.summarize_item_wips_f, level1='||sql%rowcount);
973 commit;
974
975 -- level 2
976 insert into msc_item_wips_f (
977 plan_id, plan_run_id,
978 sr_instance_id, organization_id, inventory_item_id,
979 vmi_flag, wip_start_date,
980 aggr_type, category_set_id, sr_category_id,
981 wip_qty,
982 created_by, creation_date,
983 last_update_date, last_updated_by, last_update_login,
987 select
984 program_id, program_login_id,
985 program_application_id, request_id)
986 -- category-mfg_period (1016, 1017, 1018)
988 f.plan_id, f.plan_run_id,
989 f.sr_instance_id, f.organization_id, f.inventory_item_id,
990 f.vmi_flag,
991 d.mfg_period_start_date wip_start_date,
992 decode(f.aggr_type, 42, 1016, 43, 1017, 1018) aggr_type,
993 f.category_set_id, f.sr_category_id,
994 sum(f.wip_qty),
995 fnd_global.user_id, sysdate,
996 sysdate, fnd_global.user_id, fnd_global.login_id,
997 fnd_global.conc_program_id, fnd_global.conc_login_id,
998 fnd_global.prog_appl_id, fnd_global.conc_request_id
999 from
1000 msc_item_wips_f f,
1001 msc_phub_dates_mv d
1002 where f.plan_id = p_plan_id and f.plan_run_id = p_plan_run_id
1003 and f.aggr_type between 42 and 44
1004 and f.wip_start_date = d.calendar_date
1005 and d.mfg_period_start_date is not null
1006 group by
1007 f.plan_id, f.plan_run_id,
1008 f.sr_instance_id, f.organization_id, f.inventory_item_id,
1009 f.vmi_flag,
1010 d.mfg_period_start_date,
1011 decode(f.aggr_type, 42, 1016, 43, 1017, 1018),
1012 f.category_set_id, f.sr_category_id
1013 union all
1014 -- category-fiscal_period (1019, 1020, 1021)
1015 select
1016 f.plan_id, f.plan_run_id,
1017 f.sr_instance_id, f.organization_id, f.inventory_item_id,
1018 f.vmi_flag,
1019 d.fis_period_start_date wip_start_date,
1020 decode(f.aggr_type, 42, 1019, 43, 1020, 1021) aggr_type,
1021 f.category_set_id, f.sr_category_id,
1022 sum(f.wip_qty),
1023 fnd_global.user_id, sysdate,
1024 sysdate, fnd_global.user_id, fnd_global.login_id,
1025 fnd_global.conc_program_id, fnd_global.conc_login_id,
1026 fnd_global.prog_appl_id, fnd_global.conc_request_id
1027 from
1028 msc_item_wips_f f,
1029 msc_phub_dates_mv d
1030 where f.plan_id = p_plan_id and f.plan_run_id = p_plan_run_id
1031 and f.aggr_type between 42 and 44
1032 and f.wip_start_date = d.calendar_date
1033 and d.fis_period_start_date is not null
1034 group by
1035 f.plan_id, f.plan_run_id,
1036 f.sr_instance_id, f.organization_id, f.inventory_item_id,
1037 f.vmi_flag,
1038 d.fis_period_start_date,
1039 decode(f.aggr_type, 42, 1019, 43, 1020, 1021),
1040 f.category_set_id, f.sr_category_id;
1041
1042 msc_phub_util.log('msc_supply_pkg.summarize_item_wips_f, level2='||sql%rowcount);
1043 commit;
1044
1045 exception
1046 when others then
1047 retcode := 2;
1048 errbuf := 'msc_supply_pkg.summarize_item_wips_f: '||sqlerrm;
1049 raise;
1050 end summarize_item_wips_f;
1051
1052 procedure export_supplies_f (
1053 errbuf out nocopy varchar2, retcode out nocopy varchar2,
1054 p_st_transaction_id number, p_plan_run_id number,
1055 p_dblink varchar2, p_source_version varchar2)
1056 is
1057 l_sql varchar2(5000);
1058 l_suffix varchar2(32) := msc_phub_util.suffix(p_dblink);
1059 l_apps_schema varchar2(30) := msc_phub_util.apps_schema;
1060 begin
1061 msc_phub_util.log('msc_supply_pkg.export_supplies_f');
1062 retcode := 0;
1063 errbuf := null;
1064
1065 delete from msc_st_supplies_f where st_transaction_id=p_st_transaction_id;
1066 commit;
1067
1068 l_sql :=
1069 ' insert into msc_st_supplies_f('||
1070 ' st_transaction_id,'||
1071 ' error_code,'||
1072 ' sr_instance_id,'||
1073 ' organization_id,'||
1074 ' inventory_item_id,'||
1075 ' supplier_id,'||
1076 ' supplier_site_id,'||
1077 ' project_id,'||
1078 ' task_id,'||
1079 ' organization_code,'||
1080 ' item_name,'||
1081 ' supplier_name,'||
1082 ' supplier_site_code,'||
1083 ' project_number,'||
1084 ' task_number,'||
1085 ' ship_method,'||
1086 ' supply_type,'||
1087 ' owning_org_code,'||
1088 ' owning_inst_id,'||
1089 ' owning_org_id,';
1090 if (p_source_version >= '12.1.3') then l_sql := l_sql||
1091 ' subinventory_code,'||
1092 ' part_condition,'||
1093 ' source_org_code,'||
1094 ' source_org_instance_id,'||
1095 ' source_organization_id,'||
1096 ' customer_region_id,'||
1097 ' customer_zone,'||
1098 ' end_item_inst_id,'||
1099 ' end_item_org_id,'||
1100 ' end_item_id,'||
1101 ' parent_model_item_id,'||
1102 ' end_item_org_code,'||
1103 ' end_item_name,'||
1104 ' parent_model_item_name,';
1105 end if;
1106 l_sql := l_sql||
1107 ' vmi_flag,'||
1108 ' supply_date,'||
1109 ' supply_qty,'||
1110 ' planned_order_count,'||
1111 ' planned_order_itf_count,'||
1112 ' planned_order_gmod_count,'||
1113 ' planned_order_bwo_count,'||
1114 ' work_order_leadtime,'||
1115 ' work_order_count,'||
1116 ' qty_pegged_to_excess,'||
1120 if (p_source_version >= '12.1.3') then l_sql := l_sql||
1117 ' work_order_qty,'||
1118 ' drp_supply_as_demand,'||
1119 ' return_order_qty,';
1121 ' supply_volume,'||
1122 ' po_reschedule_count,'||
1123 ' po_count,'||
1124 ' po_cancel_count,'||
1125 ' buy_order_value,'||
1126 ' buy_order_value2,'||
1127 ' buy_order_count,'||
1128 ' return_fcst,';
1129 end if;
1130 l_sql := l_sql||
1131 ' created_by, creation_date,'||
1132 ' last_updated_by, last_update_date, last_update_login'||
1133 ' )'||
1134 ' select'||
1135 ' :p_st_transaction_id,'||
1136 ' 0,'||
1137 ' f.sr_instance_id,'||
1138 ' f.organization_id,'||
1139 ' f.inventory_item_id,'||
1140 ' f.supplier_id,'||
1141 ' f.supplier_site_id,'||
1142 ' f.project_id,'||
1143 ' f.task_id,'||
1144 ' mtp.organization_code,'||
1145 ' mi.item_name,'||
1146 ' decode(f.supplier_id, -23453, null, smv.supplier_name),'||
1147 ' decode(f.supplier_site_id, -23453, null, smv.supplier_site_code),'||
1148 ' proj.project_number,'||
1149 ' proj.task_number,'||
1150 ' f.ship_method,'||
1151 ' f.supply_type,';
1152 if (p_source_version >= '12.1.3') then l_sql := l_sql||
1153 ' mtp3.organization_code,'||
1154 ' f.owning_inst_id,'||
1155 ' f.owning_org_id,';
1156 else l_sql := l_sql||
1157 ' mtp.organization_code,'||
1158 ' f.sr_instance_id,'||
1159 ' f.organization_id,';
1160 end if;
1161 if (p_source_version >= '12.1.3') then l_sql := l_sql||
1162 ' f.subinventory_code,'||
1163 ' f.part_condition,'||
1164 ' mtp2.organization_code,'||
1165 ' f.source_org_instance_id,'||
1166 ' f.source_organization_id,'||
1167 ' f.customer_region_id,'||
1168 ' decode(f.customer_region_id, -23453, null, cmv.zone),'||
1169 ' f.end_item_inst_id,'||
1170 ' f.end_item_org_id,'||
1171 ' f.end_item_id,'||
1172 ' f.parent_model_item_id,'||
1173 ' mtp4.organization_code,'||
1174 ' mi2.item_name,'||
1175 ' mi3.item_name,';
1176 end if;
1177 l_sql := l_sql||
1178 ' f.vmi_flag,'||
1179 ' f.supply_date,'||
1180 ' f.supply_qty,'||
1181 ' f.planned_order_count,'||
1182 ' f.planned_order_itf_count,'||
1183 ' f.planned_order_gmod_count,'||
1184 ' f.planned_order_bwo_count,'||
1185 ' f.work_order_leadtime,'||
1186 ' f.work_order_count,'||
1187 ' f.qty_pegged_to_excess,'||
1188 ' f.work_order_qty,'||
1189 ' f.drp_supply_as_demand,'||
1190 ' f.return_order_qty,';
1191 if (p_source_version >= '12.1.3') then l_sql := l_sql||
1192 ' f.supply_volume,'||
1193 ' f.po_reschedule_count,'||
1194 ' f.po_count,'||
1195 ' f.po_cancel_count,'||
1196 ' f.buy_order_value,'||
1197 ' f.buy_order_value2,'||
1198 ' f.buy_order_count,'||
1199 ' f.return_fcst,';
1200 end if;
1201 l_sql := l_sql||
1202 ' fnd_global.user_id, sysdate,'||
1203 ' fnd_global.user_id, sysdate, fnd_global.login_id'||
1204 ' from'||
1205 ' '||l_apps_schema||'.msc_supplies_f'||l_suffix||' f,'||
1206 ' '||l_apps_schema||'.msc_trading_partners'||l_suffix||' mtp,';
1207 if (p_source_version >= '12.1.3') then l_sql := l_sql||
1208 ' '||l_apps_schema||'.msc_trading_partners'||l_suffix||' mtp2,'||
1209 ' '||l_apps_schema||'.msc_trading_partners'||l_suffix||' mtp3,'||
1210 ' '||l_apps_schema||'.msc_trading_partners'||l_suffix||' mtp4,'||
1211 ' '||l_apps_schema||'.msc_phub_customers_mv'||l_suffix||' cmv,'||
1212 ' '||l_apps_schema||'.msc_items'||l_suffix||' mi2,'||
1213 ' '||l_apps_schema||'.msc_items'||l_suffix||' mi3,';
1214 end if;
1215 l_sql := l_sql||
1216 ' '||l_apps_schema||'.msc_items'||l_suffix||' mi,'||
1217 ' '||l_apps_schema||'.msc_phub_suppliers_mv'||l_suffix||' smv,';
1218 if (p_source_version >= '12.1.3') then l_sql := l_sql||
1219 ' '||l_apps_schema||'.msc_phub_projects_mv'||l_suffix||' proj';
1220 else l_sql := l_sql||
1221 ' (select p.sr_instance_id, p.organization_id,'||
1222 ' p.project_id, t.task_id, p.project_number, t.task_number'||
1223 ' from '||l_apps_schema||'.msc_projects'||l_suffix||' p, '||l_apps_schema||'.msc_project_tasks'||l_suffix||' t'||
1224 ' where p.project_id=t.project_id'||
1225 ' and p.plan_id=t.plan_id'||
1226 ' and p.sr_instance_id=t.sr_instance_id'||
1227 ' and p.organization_id=t.organization_id'||
1228 ' and p.plan_id=-1) proj';
1229 end if;
1230 l_sql := l_sql||
1231 ' where f.plan_run_id=:p_plan_run_id'||
1232 ' and f.aggr_type=0'||
1233 ' and mtp.partner_type(+)=3'||
1234 ' and mtp.sr_instance_id(+)=f.sr_instance_id'||
1235 ' and mtp.sr_tp_id(+)=f.organization_id';
1239 ' and mtp3.sr_tp_id(+)=f.owning_org_id'||
1236 if (p_source_version >= '12.1.3') then l_sql := l_sql||
1237 ' and mtp3.partner_type(+)=3'||
1238 ' and mtp3.sr_instance_id(+)=f.owning_inst_id'||
1240 ' and mtp2.partner_type(+)=3'||
1241 ' and mtp2.sr_instance_id(+)=f.source_org_instance_id'||
1242 ' and mtp2.sr_tp_id(+)=f.source_organization_id'||
1243 ' and cmv.customer_id(+)=-23453'||
1244 ' and cmv.customer_site_id(+)=-23453'||
1245 ' and cmv.region_id(+)=f.customer_region_id'||
1246 ' and mtp4.partner_type(+)=3'||
1247 ' and mtp4.sr_instance_id(+)=f.end_item_inst_id'||
1248 ' and mtp4.sr_tp_id(+)=f.end_item_org_id'||
1249 ' and mi2.inventory_item_id(+)=f.end_item_id'||
1250 ' and mi3.inventory_item_id(+)=f.parent_model_item_id';
1251 end if;
1252 l_sql := l_sql||
1253 ' and mi.inventory_item_id(+)=f.inventory_item_id'||
1254 ' and smv.supplier_id(+)=f.supplier_id'||
1255 ' and smv.supplier_site_id(+)=f.supplier_site_id'||
1256 ' and proj.project_id(+)=f.project_id'||
1257 ' and proj.task_id(+)=f.task_id'||
1258 ' and proj.sr_instance_id(+)=f.sr_instance_id'||
1259 ' and proj.organization_id(+)=f.organization_id';
1260
1261 execute immediate l_sql using p_st_transaction_id, p_plan_run_id;
1262 msc_phub_util.log('msc_supply_pkg.export_supplies_f: inserted='||sql%rowcount);
1263 commit;
1264 msc_phub_util.log('msc_supply_pkg.export_supplies_f: complete, retcode='||retcode);
1265
1266 exception
1267 when others then
1268 retcode := 2;
1269 errbuf := 'msc_supply_pkg.export_supplies_f: '||sqlerrm;
1270 msc_phub_util.log(errbuf);
1271 end export_supplies_f;
1272
1273 procedure export_item_wips_f (
1274 errbuf out nocopy varchar2, retcode out nocopy varchar2,
1275 p_st_transaction_id number, p_plan_run_id number,
1276 p_dblink varchar2, p_source_version varchar2)
1277 is
1278 l_sql varchar2(5000);
1279 l_suffix varchar2(32) := msc_phub_util.suffix(p_dblink);
1280 l_apps_schema varchar2(30) := msc_phub_util.apps_schema;
1281 begin
1282 msc_phub_util.log('msc_supply_pkg.export_item_wips_f');
1283 retcode := 0;
1284 errbuf := null;
1285
1286 delete from msc_st_item_wips_f where st_transaction_id=p_st_transaction_id;
1287 commit;
1288
1289 l_sql :=
1290 ' insert into msc_st_item_wips_f('||
1291 ' st_transaction_id,'||
1292 ' error_code,'||
1293 ' sr_instance_id,'||
1294 ' organization_id,'||
1295 ' inventory_item_id,'||
1296 ' organization_code,'||
1297 ' item_name,'||
1298 ' vmi_flag,'||
1299 ' wip_start_date,'||
1300 ' wip_qty,'||
1301 ' created_by, creation_date,'||
1302 ' last_updated_by, last_update_date, last_update_login'||
1303 ' )'||
1304 ' select'||
1305 ' :p_st_transaction_id,'||
1306 ' 0,'||
1307 ' f.sr_instance_id,'||
1308 ' f.organization_id,'||
1309 ' f.inventory_item_id,'||
1310 ' mtp.organization_code,'||
1311 ' mi.item_name,'||
1312 ' f.vmi_flag,'||
1313 ' f.wip_start_date,'||
1314 ' f.wip_qty,'||
1315 ' fnd_global.user_id, sysdate,'||
1316 ' fnd_global.user_id, sysdate, fnd_global.login_id'||
1317 ' from'||
1318 ' '||l_apps_schema||'.msc_item_wips_f'||l_suffix||' f,'||
1319 ' '||l_apps_schema||'.msc_trading_partners'||l_suffix||' mtp,'||
1320 ' '||l_apps_schema||'.msc_items'||l_suffix||' mi'||
1321 ' where f.plan_run_id=:p_plan_run_id'||
1322 ' and f.aggr_type=0'||
1323 ' and mtp.partner_type(+)=3'||
1324 ' and mtp.sr_instance_id(+)=f.sr_instance_id'||
1325 ' and mtp.sr_tp_id(+)=f.organization_id'||
1326 ' and mi.inventory_item_id(+)=f.inventory_item_id';
1327
1328 execute immediate l_sql using p_st_transaction_id, p_plan_run_id;
1329 msc_phub_util.log('msc_supply_pkg.export_item_wips_f: inserted='||sql%rowcount);
1330 commit;
1331 msc_phub_util.log('msc_supply_pkg.export_item_wips_f: complete, retcode='||retcode);
1332
1333 exception
1334 when others then
1335 retcode := 2;
1336 errbuf := 'msc_supply_pkg.export_item_wips_f: '||sqlerrm;
1337 msc_phub_util.log(errbuf);
1338 end export_item_wips_f;
1339
1340 procedure import_supplies_f (
1341 errbuf out nocopy varchar2, retcode out nocopy varchar2,
1342 p_st_transaction_id number, p_plan_id number, p_plan_run_id number,
1343 p_plan_type number, p_plan_start_date date, p_plan_cutoff_date date,
1344 p_upload_mode number, p_overwrite_after_date date, p_def_instance_code varchar2)
1345 is
1346 l_staging_table varchar2(30) := 'msc_st_supplies_f';
1347 l_fact_table varchar2(30) := 'msc_supplies_f';
1348 l_result number := 0;
1349 begin
1350 msc_phub_util.log('msc_supply_pkg.import_supplies_f');
1351 retcode := 0;
1352 errbuf := null;
1353
1354 l_result := l_result + msc_phub_util.prepare_staging_dates(
1355 l_staging_table, 'supply_date', p_st_transaction_id,
1356 p_upload_mode, p_overwrite_after_date,
1357 p_plan_start_date, p_plan_cutoff_date);
1358
1362
1359 l_result := l_result + msc_phub_util.prepare_fact_dates(
1360 l_fact_table, 1, 'supply_date', p_plan_id, p_plan_run_id,
1361 p_upload_mode, p_overwrite_after_date);
1363 l_result := l_result + msc_phub_util.decode_organization_key(
1364 l_staging_table, p_st_transaction_id, p_def_instance_code,
1365 'sr_instance_id', 'organization_id', 'organization_code');
1366
1367 l_result := l_result + msc_phub_util.decode_organization_key(
1368 l_staging_table, p_st_transaction_id, p_def_instance_code,
1369 'owning_inst_id', 'owning_org_id', 'owning_org_code');
1370
1371 l_result := l_result + msc_phub_util.decode_organization_key(
1372 l_staging_table, p_st_transaction_id, p_def_instance_code,
1373 'source_org_instance_id', 'source_organization_id', 'source_org_code');
1374
1375 l_result := l_result + msc_phub_util.decode_item_key(
1376 l_staging_table, p_st_transaction_id, 'inventory_item_id', 'item_name');
1377
1378 l_result := l_result + msc_phub_util.decode_supplier_key(
1379 l_staging_table, p_st_transaction_id,
1380 'supplier_id', 'supplier_site_id',
1381 'supplier_name', 'supplier_site_code');
1382
1383 l_result := l_result + msc_phub_util.decode_project_key(
1384 l_staging_table, p_st_transaction_id);
1385
1386 l_result := l_result + msc_phub_util.decode_customer_key(
1387 l_staging_table, p_st_transaction_id,
1388 null, null, 'owning_inst_id', 'customer_region_id',
1389 null, null, 'customer_zone');
1390
1391 l_result := l_result + msc_phub_util.decode_organization_key(
1392 l_staging_table, p_st_transaction_id, p_def_instance_code,
1393 'end_item_inst_id', 'end_item_org_id', 'end_item_org_code');
1394
1395 l_result := l_result + msc_phub_util.decode_item_key(
1396 l_staging_table, p_st_transaction_id, 'end_item_id', 'end_item_name');
1397
1398 l_result := l_result + msc_phub_util.decode_item_key(
1399 l_staging_table, p_st_transaction_id, 'parent_model_item_id', 'parent_model_item_name');
1400
1401 msc_phub_util.log('msc_supply_pkg.import_supplies_f: insert into msc_supplies_f');
1402 insert into msc_supplies_f (
1403 plan_id,
1404 plan_run_id,
1405 sr_instance_id,
1406 organization_id,
1407 subinventory_code,
1408 owning_inst_id,
1409 owning_org_id,
1410 source_org_instance_id,
1411 source_organization_id,
1412 inventory_item_id,
1413 supplier_id,
1414 supplier_site_id,
1415 customer_region_id,
1416 end_item_inst_id,
1417 end_item_org_id,
1418 end_item_id,
1419 parent_model_item_id,
1420 project_id,
1421 task_id,
1422 ship_method,
1423 supply_type,
1424 part_condition,
1425 io_plan_flag,
1426 vmi_flag,
1427 supply_date,
1428 supply_qty,
1429 planned_order_count,
1430 planned_order_itf_count,
1431 planned_order_gmod_count,
1432 planned_order_bwo_count,
1433 work_order_leadtime,
1434 work_order_count,
1435 qty_pegged_to_excess,
1436 work_order_qty,
1437 drp_supply_as_demand,
1438 return_order_qty,
1439 return_fcst,
1440 supply_volume,
1441 po_reschedule_count,
1442 po_count,
1443 po_cancel_count,
1444 buy_order_value,
1445 buy_order_value2,
1446 buy_order_count,
1447 aggr_type, category_set_id, sr_category_id,
1448 created_by, creation_date,
1449 last_updated_by, last_update_date, last_update_login
1450 )
1451 select
1452 p_plan_id,
1453 p_plan_run_id,
1454 nvl(sr_instance_id, -23453),
1455 nvl(organization_id, -23453),
1456 nvl(subinventory_code, '-23453'),
1457 nvl(owning_inst_id, -23453),
1458 nvl(owning_org_id, -23453),
1459 nvl(source_org_instance_id, -23453),
1460 nvl(source_organization_id, -23453),
1461 nvl(inventory_item_id, -23453),
1462 nvl(supplier_id, -23453),
1463 nvl(supplier_site_id, -23453),
1464 nvl(customer_region_id, -23453),
1465 nvl(end_item_inst_id, -23453),
1466 nvl(end_item_org_id, -23453),
1467 nvl(end_item_id, -23453),
1468 nvl(parent_model_item_id, -23453),
1469 nvl(project_id, -23453),
1470 nvl(task_id, -23453),
1471 ship_method,
1472 supply_type,
1473 part_condition,
1474 decode(p_plan_type, 4, 1, 0) io_plan_flag,
1475 vmi_flag,
1476 supply_date,
1477 supply_qty,
1478 planned_order_count,
1479 planned_order_itf_count,
1480 planned_order_gmod_count,
1481 planned_order_bwo_count,
1482 work_order_leadtime,
1483 work_order_count,
1484 qty_pegged_to_excess,
1485 work_order_qty,
1486 drp_supply_as_demand,
1487 return_order_qty,
1488 return_fcst,
1489 supply_volume,
1490 po_reschedule_count,
1491 po_count,
1492 po_cancel_count,
1493 buy_order_value,
1494 buy_order_value2,
1495 buy_order_count,
1496 0, -23453, -23453,
1497 fnd_global.user_id, sysdate,
1498 fnd_global.user_id, sysdate, fnd_global.login_id
1499 from msc_st_supplies_f
1500 where st_transaction_id=p_st_transaction_id and error_code=0;
1501
1502 msc_phub_util.log('msc_supply_pkg.import_supplies_f: inserted='||sql%rowcount);
1503 commit;
1504
1505 summarize_supplies_f(errbuf, retcode, p_plan_id, p_plan_run_id);
1506
1507 if (l_result > 0) then
1508 retcode := -1;
1509 end if;
1510
1511 msc_phub_util.log('msc_supply_pkg.import_supplies_f: complete, retcode='||retcode);
1512
1513 exception
1514 when others then
1515 retcode := 2;
1516 errbuf := 'msc_supply_pkg.import_supplies_f: '||sqlerrm;
1517 raise;
1518 end import_supplies_f;
1519
1520 procedure import_item_wips_f (
1521 errbuf out nocopy varchar2, retcode out nocopy varchar2,
1522 p_st_transaction_id number, p_plan_id number, p_plan_run_id number,
1523 p_plan_type number, p_plan_start_date date, p_plan_cutoff_date date,
1524 p_upload_mode number, p_overwrite_after_date date, p_def_instance_code varchar2)
1525 is
1526 l_staging_table varchar2(30) := 'msc_st_item_wips_f';
1527 l_fact_table varchar2(30) := 'msc_item_wips_f';
1528 l_result number := 0;
1529 begin
1530 msc_phub_util.log('msc_supply_pkg.import_item_wips_f');
1531 retcode := 0;
1532 errbuf := null;
1533
1534 l_result := l_result + msc_phub_util.prepare_staging_dates(
1535 l_staging_table, 'wip_start_date', p_st_transaction_id,
1536 p_upload_mode, p_overwrite_after_date,
1537 p_plan_start_date, p_plan_cutoff_date);
1538
1539 l_result := l_result + msc_phub_util.prepare_fact_dates(
1540 l_fact_table, 1, 'wip_start_date', p_plan_id, p_plan_run_id,
1541 p_upload_mode, p_overwrite_after_date);
1542
1543 l_result := l_result + msc_phub_util.decode_organization_key(
1544 l_staging_table, p_st_transaction_id, p_def_instance_code,
1545 'sr_instance_id', 'organization_id', 'organization_code');
1546
1547 l_result := l_result + msc_phub_util.decode_item_key(
1548 l_staging_table, p_st_transaction_id, 'inventory_item_id', 'item_name');
1549
1550 msc_phub_util.log('msc_supply_pkg.import_item_wips_f: insert into msc_item_wips_f');
1551 insert into msc_item_wips_f (
1552 plan_id,
1553 plan_run_id,
1554 sr_instance_id,
1555 organization_id,
1556 inventory_item_id,
1557 vmi_flag,
1558 wip_start_date,
1559 wip_qty,
1560 aggr_type, category_set_id, sr_category_id,
1561 created_by, creation_date,
1562 last_updated_by, last_update_date, last_update_login
1563 )
1564 select
1565 p_plan_id,
1566 p_plan_run_id,
1567 nvl(sr_instance_id, -23453),
1568 nvl(organization_id, -23453),
1569 nvl(inventory_item_id, -23453),
1570 vmi_flag,
1571 wip_start_date,
1572 wip_qty,
1573 0, -23453, -23453,
1574 fnd_global.user_id, sysdate,
1575 fnd_global.user_id, sysdate, fnd_global.login_id
1576 from msc_st_item_wips_f
1577 where st_transaction_id=p_st_transaction_id and error_code=0;
1578
1579 msc_phub_util.log('msc_supply_pkg.import_item_wips_f: inserted='||sql%rowcount);
1580 commit;
1581
1582 summarize_item_wips_f(errbuf, retcode, p_plan_id, p_plan_run_id);
1583
1584 if (l_result > 0) then
1585 retcode := -1;
1586 end if;
1587
1588 msc_phub_util.log('msc_supply_pkg.import_item_wips_f: complete, retcode='||retcode);
1589
1590 exception
1591 when others then
1592 retcode := 2;
1593 errbuf := 'msc_supply_pkg.import_item_wips_f: '||sqlerrm;
1594 raise;
1595 end import_item_wips_f;
1596
1597 end msc_supply_pkg;