[Home] [Help]
PACKAGE BODY: APPS.MSC_SUPPLY_PKG
Source
1 package body msc_supply_pkg as
2 /* $Header: MSCHBSUB.pls 120.28.12010000.4 2008/09/03 14:43:37 wexia ship $ */
3 /*
4 create table msc_supplies_f (
5 plan_id number not null,
6 plan_run_id number not null,
7 sr_instance_id number not null,
8 organization_id number not null ,
9 inventory_item_id number not null,
10 project_id number,
11 task_id number,
12 supplier_id number,
13 supplier_site_id number,
14 supply_date date,
15 supply_type number,
16 supply_qty number,
17 Planned_order_cnt number,
18 Planned_order_itf_cnt number,
19 Planned_order_gmod_cnt number,
20 Planned_order_bwo_cnt number,
21 purchasing_cost number,
22 purchasing_cost1 number
23 )
24
25 create index msc_supply_f_n1 on msc_supplies_f(plan_id,plan_run_id,sr_instance_id,
26 organization_id,inventory_item_id,
27 supply_date,supply_type);
28 create index msc_supply_f_n2 on msc_supplies_f(project_id,task_id);
29 create index msc_supply_f_n3 on msc_supplies_f(supplier_id,supplier_site_id);
30 */
31
32
33 procedure populate_details(errbuf out nocopy varchar2, retcode out nocopy varchar2,
34 p_plan_id number, p_plan_run_id number default null) is
35
36 l_api_name varchar2(100);
37 l_stmt_id number ;
38 l_sysdate date;
39 l_user_id number;
40 l_user_login_id number;
41 l_cp_login_id number;
42 l_program_id number;
43 l_appl_id number;
44 l_request_id number;
45 l_qid_vmi number;
46 l_curr_start_date date;
47 l_curr_cutoff_date date;
48 l_plan_type number;
49 begin
50
51 --dbms_output.put_line('msc_supplies_f.populate_details '||p_plan_id||', '||p_plan_run_id);
52 retcode :=0; -- this means successfully
53 errbuf :='';
54 -- Successfully populated msc_supplies_f table for plan_id =' || p_plan_id || ',plan_run_id=' || p_plan_run_id;
55 -- initial there is no error message
56 l_api_name := 'msc_supply_f_pkg.populate_details';
57
58 l_user_id := fnd_global.user_id;
59 l_sysdate :=sysdate;
60 l_user_login_id :=fnd_global.login_id;
61 l_cp_login_id :=FND_GLOBAL.CONC_LOGIN_ID;
62 l_program_id := FND_GLOBAL.CONC_PROGRAM_ID;
63 l_appl_id := FND_GLOBAL.PROG_APPL_ID;
64 l_request_id := FND_GLOBAL.CONC_REQUEST_ID;
65 l_plan_type := msc_phub_util.get_plan_type(p_plan_id);
66
67
68 l_stmt_id :=5;
69 select trunc(mp.curr_start_date),trunc(mp.curr_cutoff_date)
70 into l_curr_start_date,l_curr_cutoff_date
71 from msc_plans mp where mp.plan_id=p_plan_id;
72
73
74 l_stmt_id :=10;
75 ----------------------------------------------------
76
77 select msc_hub_query_s.nextval into l_qid_vmi from dual;
78
79 insert into msc_hub_query(
80 query_id,
81 last_update_date,
82 last_updated_by,
83 creation_date,
84 created_by,
85 last_update_login,
86 number1, -- plan_id
87 number3, -- sr_instance_id
88 number4, -- organization_id
89 number5, -- inventory_item_id
90 number10 -- vmi flag
91 )
92 select
93 unique l_qid_vmi,l_sysdate,1,l_sysdate,1,1,
94 p_plan_id,
95 msi.sr_instance_id,
96 msi.organization_id,
97 msi.inventory_item_id,
98 nvl(mis.vmi_flag,0)
99 from msc_item_suppliers mis,
100 msc_system_items msi
101 where msi.plan_id = mis.plan_id
102 and msi.sr_instance_id = mis.sr_instance_id
103 and msi.organization_id = mis.organization_id
104 and msi.inventory_item_id = mis.inventory_item_id
105 and msi.plan_id=p_plan_id
106 and nvl(mis.vmi_flag,0)=1;
107
108
109 commit;
110
111
112
113
114 l_stmt_id :=20;
115 ----------------------------------------------------
116 Insert into msc_supplies_f (
117 created_by,
118 creation_date,
119 last_updated_by,
120 last_update_date,
121 last_update_login,
122 PROGRAM_ID ,
123 PROGRAM_LOGIN_ID ,
124 PROGRAM_APPLICATION_ID ,
125 REQUEST_ID ,
126 -----------------------------------------------------
127 plan_id, -- plan_id
128 plan_run_id,
129 io_plan_flag, --- this flag indidate whether it is an io plan
130 sr_instance_id,
131 organization_id,
132 inventory_item_id,
133 project_id,
134 task_id,
135 supplier_id,
136 supplier_site_id,
137 region_id,
138 ship_method,
139 supply_date,
140 aggr_type, category_set_id, sr_category_id,
141 supply_type,
142 vmi_flag,
143 supply_qty,
144 Planned_order_count,
145 work_order_leadtime , --- for work order (work order, planned work order)
146 work_order_count,
147 work_order_qty,
148 stockout_days , -- this is for vmi measure 'stockout day'
149 drp_supply_as_demand,
150 return_order_Qty
151 )
152 select
153 l_user_id,
154 l_sysdate,
155 l_user_id,
156 l_sysdate,
157 l_user_login_id,
158 l_program_id,
159 l_cp_login_id,
160 l_appl_id,
161 l_request_id,
162 ----------------------------------------------
163 p_plan_id, -- plan_id
164 p_plan_run_id, -- plan_run_id,
165 decode(mp.plan_type,4,1,9,1,0) io_plan_flag,
166 supply_tbl.sr_instance_id,
167 supply_tbl.organization_id,
168 supply_tbl.inventory_item_id,
169 supply_tbl.project_id,
170 supply_tbl.task_id,
171 supply_tbl.supplier_id,
172 supply_tbl.supplier_site_id,
173 mps.region_id,
174 --- if supply_date <l_curr_start_date, supply_date =l_curr_start_date-1
175 --- else if supply_date>l_curr_cutoff_date, supply_date=l_curr_cutoff_date+1
176 --- else supply_date
177 --- we can not simply put it at plan start date,
178 --- should be at the last working day of the bucket where plan start date is
179
180 supply_tbl.ship_method,
181 decode(sign(to_number(supply_tbl.supply_date-l_curr_start_date)),
182 -1, msc_hub_calendar.last_work_date(p_plan_id,l_curr_start_date),
183 decode(supply_tbl.supply_type,
184 18, msc_hub_calendar.last_work_date(p_plan_id,supply_tbl.supply_date),
185 supply_tbl.supply_date)),
186 to_number(0) aggr_type,
187 to_number(-23453) category_set_id,
188 to_number(-23453) sr_category_id,
189 supply_tbl.supply_type,
190 supply_tbl.vmi_flag,
191 sum(supply_tbl.supply_qty),
192 sum(supply_tbl.Planned_order_count),
193 sum(supply_tbl.work_order_leadtime),
194 sum(supply_tbl.work_order_count),
195 sum(supply_tbl.work_order_qty),
196 sum(supply_tbl.stockout_days),
197 sum(supply_tbl.drp_supply_as_demand) ,
198 sum(supply_tbl.return_order_qty)
199
200 from
201 (select
202 ms.sr_instance_id,
203 ms.organization_id,
204 ms.inventory_item_id,
205 nvl(ms.project_id,-23453) project_id,
206 nvl(ms.task_id,-23453) task_id,
207 nvl(ms.supplier_id,-23453) supplier_id,
208 nvl(ms.supplier_site_id,-23453) supplier_site_id,
209 nvl(ms.zone_id,-23453) region_id,
210 nvl(ms.ship_method, '-23453') ship_method,
211 trunc(nvl(ms.firm_date,ms.new_schedule_date)) supply_date,
212 ms.order_type supply_type,
213 msi.vmi_flag,
214
215 sum(decode(msi.base_item_id,null,
216 decode(ms.disposition_status_type,2, 0,
217 decode(ms.last_unit_completion_date,null, ms.new_order_quantity,ms.daily_rate) ),
218 decode(ms.last_unit_completion_date,null, ms.new_order_quantity,ms.daily_rate) )) supply_qty,
219
220 sum(decode(ms.order_type,5,
221 decode(msi.base_item_id,null,
222 decode(ms.disposition_status_type, 2, 0,1),1),to_number(null))) Planned_order_count,
223
224
225 ---------------------------------------------------------------------------
226 --- ??? exclude if new_schedule_date is null
227 --- decode(nvl(ms.source_organization_id, ms.organization_id),
228 -- ms.organization_id,
229 -- PLANNED_MAKE_OFF,
230 -- PLANNED_BUY_OFF)
231 -- make order 3,7,14,15,27,28,
232 -- 4,13 ?? do we need to include Repetitive schdule as make order??
233 -- make planned order
234 -- 3,4,5,7,13,14,15,16,17,27,28,30
235 ---------------------------------------------------------------------------
236 sum(decode(ms.order_type,3, nvl(ms.NEW_SCHEDULE_DATE,null)-nvl(ms.new_wip_start_date,null),
237 5,decode(msc_supply_pkg.implement_code(ms.source_organization_id,ms.organization_id,
238 msi.repetitive_type,ms.source_supplier_id,
239 msi.planning_make_buy_code,msi.build_in_wip_flag),
240 3,nvl(ms.NEW_SCHEDULE_DATE,null)-nvl(ms.new_wip_start_date,null),
241 4,nvl(ms.NEW_SCHEDULE_DATE,null)- nvl(ms.FIRST_UNIT_START_DATE,null),
242 0),
243 7,nvl(ms.NEW_SCHEDULE_DATE,null)-nvl(ms.new_wip_start_date,null),
244 14,nvl(ms.NEW_SCHEDULE_DATE,null)-nvl(ms.new_wip_start_date,null),
245 15,nvl(ms.NEW_SCHEDULE_DATE,null)-nvl(ms.new_wip_start_date,null),
246 27,nvl(ms.NEW_SCHEDULE_DATE,null)-nvl(ms.new_wip_start_date,null),
247 28,nvl(ms.NEW_SCHEDULE_DATE,null)-nvl(ms.new_wip_start_date,null),
248 4,nvl(ms.NEW_SCHEDULE_DATE,null)- nvl(ms.FIRST_UNIT_START_DATE,null),
249 13,nvl(ms.NEW_SCHEDULE_DATE,null)- nvl(ms.FIRST_UNIT_START_DATE,null),
250 16,nvl(ms.NEW_SCHEDULE_DATE,null)- nvl(ms.FIRST_UNIT_START_DATE,null),
251 17,nvl(ms.NEW_SCHEDULE_DATE,null)-nvl(ms.new_wip_start_date,null),
252 30,nvl(ms.NEW_SCHEDULE_DATE,null)- nvl(ms.FIRST_UNIT_START_DATE,null),
253 88,nvl(ms.NEW_SCHEDULE_DATE,null)- nvl(ms.FIRST_UNIT_START_DATE,null),
254 to_number(null))) work_order_leadtime,
255
256 sum(decode(ms.order_type,3,1,
257 5,decode(msc_supply_pkg.implement_code(ms.source_organization_id,ms.organization_id,
258 msi.repetitive_type,ms.source_supplier_id,
259 msi.planning_make_buy_code,msi.build_in_wip_flag),
260 3,1,0),
261 7,1,
262 14,1,
263 15,1,
264 27,1,
265 28,1,
266 4,1,
267 13,1,
268 16,1,
269 17,1,
270 30,1,
271 88,1,
272 to_number(null))) work_order_count,
273 sum(decode(ms.order_type,
274 3, decode(msi.base_item_id,null,
275 decode(ms.disposition_status_type,2, 0,
276 decode(ms.last_unit_completion_date,null, ms.new_order_quantity,ms.daily_rate) ),
277 decode(ms.last_unit_completion_date,null, ms.new_order_quantity,ms.daily_rate) ),
278 5, decode(msc_supply_pkg.implement_code(ms.source_organization_id,ms.organization_id,
279 msi.repetitive_type,ms.source_supplier_id,
280 msi.planning_make_buy_code,msi.build_in_wip_flag),
281 3,decode(msi.base_item_id,null,
282 decode(ms.disposition_status_type,2, 0,
283 decode(ms.last_unit_completion_date,null, ms.new_order_quantity,ms.daily_rate) ),
284 decode(ms.last_unit_completion_date,null, ms.new_order_quantity,ms.daily_rate) ),
285 4,decode(msi.base_item_id,null,
286 decode(ms.disposition_status_type,2, 0,
287 decode(ms.last_unit_completion_date,null, ms.new_order_quantity,ms.daily_rate) ),
288 decode(ms.last_unit_completion_date,null, ms.new_order_quantity,ms.daily_rate) ),
289 0),
290 7,decode(msi.base_item_id,null,
291 decode(ms.disposition_status_type,2, 0,
292 decode(ms.last_unit_completion_date,null, ms.new_order_quantity,ms.daily_rate) ),
293 decode(ms.last_unit_completion_date,null, ms.new_order_quantity,ms.daily_rate) ),
294 14,decode(msi.base_item_id,null,
295 decode(ms.disposition_status_type,2, 0,
296 decode(ms.last_unit_completion_date,null, ms.new_order_quantity,ms.daily_rate) ),
297 decode(ms.last_unit_completion_date,null, ms.new_order_quantity,ms.daily_rate) ),
298 15,decode(msi.base_item_id,null,
299 decode(ms.disposition_status_type,2, 0,
300 decode(ms.last_unit_completion_date,null, ms.new_order_quantity,ms.daily_rate) ),
301 decode(ms.last_unit_completion_date,null, ms.new_order_quantity,ms.daily_rate) ),
302 27,decode(msi.base_item_id,null,
303 decode(ms.disposition_status_type,2, 0,
304 decode(ms.last_unit_completion_date,null, ms.new_order_quantity,ms.daily_rate) ),
305 decode(ms.last_unit_completion_date,null, ms.new_order_quantity,ms.daily_rate) ),
306 28,decode(msi.base_item_id,null,
307 decode(ms.disposition_status_type,2, 0,
308 decode(ms.last_unit_completion_date,null, ms.new_order_quantity,ms.daily_rate) ),
309 decode(ms.last_unit_completion_date,null, ms.new_order_quantity,ms.daily_rate) ),
310 4,decode(msi.base_item_id,null,
311 decode(ms.disposition_status_type,2, 0,
312 decode(ms.last_unit_completion_date,null, ms.new_order_quantity,ms.daily_rate) ),
313 decode(ms.last_unit_completion_date,null, ms.new_order_quantity,ms.daily_rate) ),
314 13,decode(msi.base_item_id,null,
315 decode(ms.disposition_status_type,2, 0,
316 decode(ms.last_unit_completion_date,null, ms.new_order_quantity,ms.daily_rate) ),
317 decode(ms.last_unit_completion_date,null, ms.new_order_quantity,ms.daily_rate) ),
318 16,decode(msi.base_item_id,null,
319 decode(ms.disposition_status_type,2, 0,
320 decode(ms.last_unit_completion_date,null, ms.new_order_quantity,ms.daily_rate) ),
321 decode(ms.last_unit_completion_date,null, ms.new_order_quantity,ms.daily_rate) ),
322 17,decode(msi.base_item_id,null,
323 decode(ms.disposition_status_type,2, 0,
324 decode(ms.last_unit_completion_date,null, ms.new_order_quantity,ms.daily_rate) ),
325 decode(ms.last_unit_completion_date,null, ms.new_order_quantity,ms.daily_rate) ),
326 30,decode(msi.base_item_id,null,
327 decode(ms.disposition_status_type,2, 0,
328 decode(ms.last_unit_completion_date,null, ms.new_order_quantity,ms.daily_rate) ),
329 decode(ms.last_unit_completion_date,null, ms.new_order_quantity,ms.daily_rate) ),
330 88,decode(msi.base_item_id,null,
331 decode(ms.disposition_status_type,2, 0,
332 decode(ms.last_unit_completion_date,null, ms.new_order_quantity,ms.daily_rate) ),
333 decode(ms.last_unit_completion_date,null, ms.new_order_quantity,ms.daily_rate) ),
334 to_number(null))) work_order_qty,
335
336 --- order_type in (1,2,18) and nvl(item_type_id,401) = 401 and nvl(item_type_value,1) = 2
337
338 sum(decode(mp.plan_type,8,
339 decode(ms.order_type,1,decode(nvl(item_type_id,401),401,
340 decode(nvl(item_type_value,1),
341 2,decode(msi.base_item_id,null,
342 decode(ms.disposition_status_type,2, 0,
343 decode(ms.last_unit_completion_date,null, ms.new_order_quantity,ms.daily_rate) ),
344 decode(ms.last_unit_completion_date,null, ms.new_order_quantity,ms.daily_rate) ),
345 0),
346 0),
347 2,decode(nvl(item_type_id,401),401,
348 decode(nvl(item_type_value,1),
349 2,decode(msi.base_item_id,null,
350 decode(ms.disposition_status_type,2, 0,
351 decode(ms.last_unit_completion_date,null, ms.new_order_quantity,ms.daily_rate) ),
352 decode(ms.last_unit_completion_date,null, ms.new_order_quantity,ms.daily_rate) ),
353 0),
354 0),
355 18,decode(nvl(item_type_id,401),401,
356 decode(nvl(item_type_value,1),
357 2,decode(msi.base_item_id,null,
358 decode(ms.disposition_status_type,2, 0,
359 decode(ms.last_unit_completion_date,null, ms.new_order_quantity,ms.daily_rate) ),
360 decode(ms.last_unit_completion_date,null, ms.new_order_quantity,ms.daily_rate) ),
361 0),
362 0),
363 0),
364 9,
365 decode(ms.order_type,1,decode(nvl(item_type_id,401),401,
366 decode(nvl(item_type_value,1),
367 2,decode(msi.base_item_id,null,
368 decode(ms.disposition_status_type,2, 0,
369 decode(ms.last_unit_completion_date,null, ms.new_order_quantity,ms.daily_rate) ),
370 decode(ms.last_unit_completion_date,null, ms.new_order_quantity,ms.daily_rate) ),
371 0),
372 0),
373 2,decode(nvl(item_type_id,401),401,
374 decode(nvl(item_type_value,1),
375 2,decode(msi.base_item_id,null,
376 decode(ms.disposition_status_type,2, 0,
377 decode(ms.last_unit_completion_date,null, ms.new_order_quantity,ms.daily_rate) ),
378 decode(ms.last_unit_completion_date,null, ms.new_order_quantity,ms.daily_rate) ),
379 0),
380 0),
381 18,decode(nvl(item_type_id,401),401,
382 decode(nvl(item_type_value,1),
383 2,decode(msi.base_item_id,null,
384 decode(ms.disposition_status_type,2, 0,
385 decode(ms.last_unit_completion_date,null, ms.new_order_quantity,ms.daily_rate) ),
386 decode(ms.last_unit_completion_date,null, ms.new_order_quantity,ms.daily_rate) ),
387 0),
388 0),
389 0),
390 0)) return_order_qty,
391 to_number(null) drp_supply_as_demand,
392
393
394 to_number(null) stockout_days
395
396 from
397 msc_supplies ms,
398 ( select msi_1.plan_id,
399 msi_1.sr_instance_id,
400 msi_1.organization_id,
401 msi_1.inventory_item_id,
402 msi_1.base_item_id,
403 msi_1.repetitive_type,
404 msi_1.planning_make_buy_code,
405 msi_1.build_in_wip_flag,
406 nvl(f_1.number10,0) vmi_flag
407 from msc_system_items msi_1,
408 msc_hub_query f_1
409 where f_1.query_id(+) = l_qid_vmi
410 and f_1.number1(+) = msi_1.plan_id
411 and f_1.number3(+) = msi_1.sr_instance_id
412 and f_1.number4(+) = msi_1.organization_id
413 and f_1.number5(+) = msi_1.inventory_item_id) msi, msc_plans mp
414 where ms.plan_id = msi.plan_id
415 and ms.sr_instance_id = msi.sr_instance_id
416 and ms.organization_id =msi.organization_id
417 and ms.inventory_item_id = msi.inventory_item_id
418 and ms.plan_id=p_plan_id
419 and ms.plan_id=mp.plan_id
420 group by
421 ms.sr_instance_id,
422 ms.organization_id,
423 ms.inventory_item_id,
424 nvl(ms.project_id,-23453),
425 nvl(ms.task_id,-23453),
426 nvl(ms.supplier_id,-23453),
427 nvl(ms.supplier_site_id,-23453),
428 nvl(ms.zone_id,-23453),
429 nvl(ms.ship_method, '-23453'),
430 trunc(nvl(ms.firm_date,ms.new_schedule_date)),
431 ms.order_type,
432 msi.vmi_flag
433
434
435 union all
436
437 select
438 med.sr_instance_id,
439 med.organization_id,
440 med.inventory_item_id,
441 -23453 project_id,
442 -23453 task_id,
443 nvl(med.supplier_id, -23453) supplier_id,
444 nvl(med.supplier_site_id, -23453) supplier_id,
445 nvl(med.zone_id, -23453) region_id,
446 '-23453' ship_method,
447 trunc(med.date1) supply_date, -- exception date
448 -23453 supply_type,
449 nvl(f_2.number10,0) vmi_flag,
450 -----------------------------------------------------------------------
451 to_number(null) supply_qty,
452 to_number(null) Planned_order_count,
453 to_number(null) work_order_leadtime,
454 to_number(null) work_order_count,
455 to_number(null) work_order_qty,
456 to_number(null) return_order_qty,
457 to_number(null) drp_supply_as_demand,
458 sum(MED.DATE2 - MED.DATE1) stockout_days --- should be to_date - from_date
459 -- may get from msc_exception_f if this has performance issue
460 -- table from_date - to_date
461 from
462 msc_exception_details med,
463 msc_hub_query f_2
464 where f_2.query_id(+) = l_qid_vmi
465 and f_2.number1(+) = med.plan_id
466 and f_2.number3(+) = med.sr_instance_id
467 and f_2.number4(+) = med.organization_id
468 and f_2.number5(+) = med.inventory_item_id
469 and med.exception_type =2
470 and med.plan_id = p_plan_id
471 group by
472 med.sr_instance_id,
473 med.organization_id,
474 med.inventory_item_id,
475 -23453,
476 -23453,
477 nvl(med.supplier_id, -23453),
478 nvl(med.supplier_site_id, -23453),
479 nvl(med.zone_id, -23453),
480 trunc(med.date1),
481 -23453,
482 nvl(f_2.number10,0)
483
484 union all
485 select
486 ms2.sr_instance_id,
487 ms2.source_organization_id organization_id,
488 ms2.inventory_item_id,
489 nvl(ms2.project_id,-23453) project_id,
490 nvl(ms2.task_id,-23453) task_id,
491 nvl(ms2.supplier_id,-23453) supplier_id,
492 nvl(ms2.supplier_site_id,-23453) supplier_site_id,
493 nvl(ms2.zone_id,-23453) supplier_site_id,
494 nvl(ms2.ship_method, '-23453') ship_method,
495 trunc(nvl(ms2.firm_date,ms2.new_schedule_date)) supply_date,
496 ms2.order_type supply_type,
497 msi2.vmi_flag,
498
499 -----------------------------------------------------------------------
500 to_number(null) supply_qty,
501 to_number(null) Planned_order_count,
502 to_number(null) work_order_leadtime,
503 to_number(null) work_order_count,
504 to_number(null) work_order_qty,
505 to_number(null) return_order_qty,
506
507
508 /* ms.source_organization_id <> ms.organization_id
509 and (ms.order_type <> PURCH_REQ or
510 (ms.order_type = PURCH_REQ and ms.supplier_id is not null))*/
511
512
513 sum(decode(mp.plan_type,5,decode(ms2.order_type,1,
514 decode(ms2.organization_id,ms2.source_organization_id,0,
515 decode(msi2.base_item_id,null,
516 decode(ms2.disposition_status_type,2, 0,
517 decode(ms2.last_unit_completion_date,null, ms2.new_order_quantity,ms2.daily_rate) ),
518 decode(ms2.last_unit_completion_date,null, ms2.new_order_quantity,ms2.daily_rate) )),
519 51,decode(ms2.organization_id, ms2.source_organization_id,0,
520 decode(msi2.base_item_id,null,
521 decode(ms2.disposition_status_type,2, 0,
522 decode(ms2.last_unit_completion_date,null, ms2.new_order_quantity,ms2.daily_rate) ),
523 decode(ms2.last_unit_completion_date,null, ms2.new_order_quantity,ms2.daily_rate) )),
524 2,decode(ms2.supplier_id,null,0,
525 decode(ms2.organization_id, ms2.source_organization_id,0,
526 decode(msi2.base_item_id,null,
527 decode(ms2.disposition_status_type,2, 0,
528 decode(ms2.last_unit_completion_date,null, ms2.new_order_quantity,ms2.daily_rate) ),
529 decode(ms2.last_unit_completion_date,null, ms2.new_order_quantity,ms2.daily_rate) ))),
530 0),0)) drp_supply_as_demand,
531 to_number(null) stockout_days
532 from msc_supplies ms2,msc_plans mp,
533 ( select msi_2.plan_id,
534 msi_2.sr_instance_id,
535 msi_2.organization_id,
536 msi_2.inventory_item_id,
537 msi_2.base_item_id,
538 msi_2.planning_make_buy_code,
539 msi_2.build_in_wip_flag,
540 nvl(f_3.number10,0) vmi_flag
541 from msc_system_items msi_2,
542 msc_hub_query f_3
543 where f_3.query_id(+) = l_qid_vmi
544 and f_3.number1(+) = msi_2.plan_id
545 and f_3.number3(+) = msi_2.sr_instance_id
546 and f_3.number4(+) = msi_2.organization_id
547 and f_3.number5(+) = msi_2.inventory_item_id) msi2
548 where ms2.plan_id = msi2.plan_id
549 and ms2.sr_instance_id = msi2.sr_instance_id
550 and ms2.source_organization_id =msi2.organization_id
551 and ms2.inventory_item_id = msi2.inventory_item_id
552 and ms2.plan_id=p_plan_id
553 and ms2.order_type in (1,2,51)
554 and ms2.plan_id=mp.plan_id
555 group by
556 ms2.sr_instance_id,
557 ms2.source_organization_id,
558 ms2.inventory_item_id,
559 nvl(ms2.project_id,-23453),
560 nvl(ms2.task_id,-23453),
561 nvl(ms2.supplier_id,-23453),
562 nvl(ms2.supplier_site_id,-23453),
563 nvl(ms2.zone_id,-23453),
564 nvl(ms2.ship_method, '-23453'),
565 trunc(nvl(ms2.firm_date,ms2.new_schedule_date)),
566 ms2.order_type,
567 msi2.vmi_flag
568 order by 1,2,3,4,5,6,7,8,9,10) supply_tbl,
569 msc_plans mp,
570 msc_phub_suppliers_mv mps
571 where mp.plan_id=p_plan_id --- we need this to separate io measure from other measures
572 and mps.supplier_id = nvl(supply_tbl.supplier_id, -23453)
573 and mps.supplier_site_id = nvl(supply_tbl.supplier_site_id, -23453)
574 and mps.region_id = decode(nvl(supply_tbl.supplier_id, -23453),
575 -23453, nvl(supply_tbl.region_id, -23453), mps.region_id)
576 group by
577 decode(mp.plan_type,4,1,9,1,0),
578 supply_tbl.sr_instance_id,
579 supply_tbl.organization_id,
580 supply_tbl.inventory_item_id,
581 supply_tbl.project_id,
582 supply_tbl.task_id,
583 supply_tbl.supplier_id,
584 supply_tbl.supplier_site_id,
585 mps.region_id,
586 supply_tbl.ship_method,
587 decode(sign(to_number(supply_tbl.supply_date-l_curr_start_date)),
588 -1, msc_hub_calendar.last_work_date(p_plan_id,l_curr_start_date),
589 decode(supply_tbl.supply_type,
590 18, msc_hub_calendar.last_work_date(p_plan_id,supply_tbl.supply_date),
591 supply_tbl.supply_date)),
592 supply_tbl.supply_type,
593 supply_tbl.vmi_flag;
594
595 --dbms_output.put_line('insert count='||SQL%ROWCOUNT);
596 commit;
597
598
599
600
601 --- populate wip start qty
602 --- msc_item_wip_f
603 ---
604
605 l_stmt_id:=30;
606 Insert into msc_item_wips_f (
607 created_by,
608 creation_date,
609 last_updated_by,
610 last_update_date,
611 last_update_login,
612 PROGRAM_ID ,
613 PROGRAM_LOGIN_ID ,
614 PROGRAM_APPLICATION_ID ,
615 REQUEST_ID ,
616 -----------------------------------------------------
617 plan_id, -- plan_id
618 plan_run_id,
619 sr_instance_id,
620 organization_id,
621 inventory_item_id,
622 vmi_flag,
623 wip_start_date,
624 aggr_type, category_set_id, sr_category_id,
625 wip_qty
626 )
627 select
628 l_user_id,
629 l_sysdate,
630 l_user_id,
631 l_sysdate,
632 l_user_login_id,
633 l_program_id,
634 l_cp_login_id,
635 l_appl_id,
636 l_request_id,
637 ----------------------------------------------
638 p_plan_id, -- plan_id
639 p_plan_run_id, -- plan_run_id,
640 wip_tbl.sr_instance_id,
641 wip_tbl.organization_id,
642 wip_tbl.inventory_item_id,
643 wip_tbl.vmi_flag,
644 decode(sign(to_number(wip_tbl.wip_start_date-l_curr_start_date)),-1,l_curr_start_date,wip_tbl.wip_start_date),
645 to_number(0) aggr_type,
646 to_number(-23453) category_set_id,
647 to_number(-23453) sr_category_id,
648 sum(wip_tbl.wip_qty)
649 from
650 (select
651 ms.sr_instance_id,
652 ms.organization_id,
653 ms.inventory_item_id,
654 msi.vmi_flag,
655 trunc(nvl(nvl(ms.new_wip_start_date,ms.FIRST_UNIT_START_DATE),l_curr_start_date)) wip_start_date,
656 -- make order 3,7,14,15,27,28,
657 -- 4,13 ?? do we need to include Repetitive schdule as make order??
658 -- make planned order
659 ---------------------------------------------------------------------------
660 sum(decode(ms.order_type,
661 3, decode(msi.base_item_id,null,
662 decode(ms.disposition_status_type,2, 0,
663 decode(ms.last_unit_completion_date,null, ms.new_order_quantity,ms.daily_rate) ),
664 decode(ms.last_unit_completion_date,null, ms.new_order_quantity,ms.daily_rate) ),
665 5, decode(msc_supply_pkg.implement_code(ms.source_organization_id,ms.organization_id,
666 msi.repetitive_type,ms.source_supplier_id,
667 msi.planning_make_buy_code,msi.build_in_wip_flag),
668 3,decode(msi.base_item_id,null,
669 decode(ms.disposition_status_type,2, 0,
670 decode(ms.last_unit_completion_date,null, ms.new_order_quantity,ms.daily_rate) ),
671 decode(ms.last_unit_completion_date,null, ms.new_order_quantity,ms.daily_rate) ),
672 4,decode(msi.base_item_id,null,
673 decode(ms.disposition_status_type,2, 0,
674 decode(ms.last_unit_completion_date,null, ms.new_order_quantity,ms.daily_rate) ),
675 decode(ms.last_unit_completion_date,null, ms.new_order_quantity,ms.daily_rate) ),
676 0),
677 7,decode(msi.base_item_id,null,
678 decode(ms.disposition_status_type,2, 0,
679 decode(ms.last_unit_completion_date,null, ms.new_order_quantity,ms.daily_rate) ),
680 decode(ms.last_unit_completion_date,null, ms.new_order_quantity,ms.daily_rate) ),
681 14,decode(msi.base_item_id,null,
682 decode(ms.disposition_status_type,2, 0,
683 decode(ms.last_unit_completion_date,null, ms.new_order_quantity,ms.daily_rate) ),
684 decode(ms.last_unit_completion_date,null, ms.new_order_quantity,ms.daily_rate) ),
685 15,decode(msi.base_item_id,null,
686 decode(ms.disposition_status_type,2, 0,
687 decode(ms.last_unit_completion_date,null, ms.new_order_quantity,ms.daily_rate) ),
688 decode(ms.last_unit_completion_date,null, ms.new_order_quantity,ms.daily_rate) ),
689 27,decode(msi.base_item_id,null,
690 decode(ms.disposition_status_type,2, 0,
691 decode(ms.last_unit_completion_date,null, ms.new_order_quantity,ms.daily_rate) ),
692 decode(ms.last_unit_completion_date,null, ms.new_order_quantity,ms.daily_rate) ),
693 28,decode(msi.base_item_id,null,
694 decode(ms.disposition_status_type,2, 0,
695 decode(ms.last_unit_completion_date,null, ms.new_order_quantity,ms.daily_rate) ),
696 decode(ms.last_unit_completion_date,null, ms.new_order_quantity,ms.daily_rate) ),
697 4,decode(msi.base_item_id,null,
698 decode(ms.disposition_status_type,2, 0,
699 decode(ms.last_unit_completion_date,null, ms.new_order_quantity,ms.daily_rate) ),
700 decode(ms.last_unit_completion_date,null, ms.new_order_quantity,ms.daily_rate) ),
701 13,decode(msi.base_item_id,null,
702 decode(ms.disposition_status_type,2, 0,
703 decode(ms.last_unit_completion_date,null, ms.new_order_quantity,ms.daily_rate) ),
704 decode(ms.last_unit_completion_date,null, ms.new_order_quantity,ms.daily_rate) ),
705 16,decode(msi.base_item_id,null,
706 decode(ms.disposition_status_type,2, 0,
707 decode(ms.last_unit_completion_date,null, ms.new_order_quantity,ms.daily_rate) ),
708 decode(ms.last_unit_completion_date,null, ms.new_order_quantity,ms.daily_rate) ),
709 17,decode(msc_supply_pkg.implement_code(ms.source_organization_id,ms.organization_id,
710 msi.repetitive_type,ms.source_supplier_id,
711 msi.planning_make_buy_code,msi.build_in_wip_flag),
712 3,decode(msi.base_item_id,null,
713 decode(ms.disposition_status_type,2, 0,
714 decode(ms.last_unit_completion_date,null, ms.new_order_quantity,ms.daily_rate) ),
715 decode(ms.last_unit_completion_date,null, ms.new_order_quantity,ms.daily_rate) ),
716 4,decode(msi.base_item_id,null,
717 decode(ms.disposition_status_type,2, 0,
718 decode(ms.last_unit_completion_date,null, ms.new_order_quantity,ms.daily_rate) ),
719 decode(ms.last_unit_completion_date,null, ms.new_order_quantity,ms.daily_rate) ),
720 0),
721 30,decode(msi.base_item_id,null,
722 decode(ms.disposition_status_type,2, 0,
723 decode(ms.last_unit_completion_date,null, ms.new_order_quantity,ms.daily_rate) ),
724 decode(ms.last_unit_completion_date,null, ms.new_order_quantity,ms.daily_rate) ),
725 88,decode(msi.base_item_id,null,
726 decode(ms.disposition_status_type,2, 0,
727 decode(ms.last_unit_completion_date,null, ms.new_order_quantity,ms.daily_rate) ),
728 decode(ms.last_unit_completion_date,null, ms.new_order_quantity,ms.daily_rate) ),
729 to_number(null))) wip_qty
730 from
731 msc_supplies ms,
732 ( select msi_1.plan_id,
733 msi_1.sr_instance_id,
734 msi_1.organization_id,
735 msi_1.inventory_item_id,
736 msi_1.base_item_id,
737 msi_1.repetitive_type,
738 msi_1.planning_make_buy_code,
739 msi_1.build_in_wip_flag,
740 nvl(f_1.number10,0) vmi_flag
741 from msc_system_items msi_1,
742 msc_hub_query f_1
743 where f_1.query_id(+) = l_qid_vmi
744 and f_1.number1(+) = msi_1.plan_id
745 and f_1.number3(+) = msi_1.sr_instance_id
746 and f_1.number4(+) = msi_1.organization_id
747 and f_1.number5(+) = msi_1.inventory_item_id) msi,
748 msc_plans mp
749 where ms.plan_id = msi.plan_id
750 and ms.sr_instance_id = msi.sr_instance_id
751 and ms.organization_id =msi.organization_id
752 and ms.inventory_item_id = msi.inventory_item_id
753 and ms.plan_id=p_plan_id
754 and mp.plan_id=ms.plan_id
755 and mp.plan_type not in (4,9) --- exclude io plan
756 group by
757 ms.sr_instance_id,
758 ms.organization_id,
759 ms.inventory_item_id,
760 msi.vmi_flag,
761 trunc(nvl(nvl(ms.new_wip_start_date,ms.FIRST_UNIT_START_DATE),l_curr_start_date)) ) wip_tbl
762 -- where l_plan_type <> 6
763 group by
764 l_user_id,
765 l_sysdate,
766 l_user_id,
767 l_sysdate,
768 l_user_login_id,
769 l_program_id,
770 l_cp_login_id,
771 l_appl_id,
772 l_request_id,
773 -----------------------------------------------
774 p_plan_id, -- plan_id
775 p_plan_run_id, -- plan_run_id,
776 wip_tbl.sr_instance_id,
777 wip_tbl.organization_id,
778 wip_tbl.inventory_item_id,
779 wip_tbl.vmi_flag,
780 decode(sign(to_number(wip_tbl.wip_start_date-l_curr_start_date)),-1,l_curr_start_date,wip_tbl.wip_start_date);
781
782 --dbms_output.put_line('insert count='||SQL%ROWCOUNT);
783
784
785 commit;
786
787
788 populate_summary(errbuf, retcode, p_plan_id, p_plan_run_id);
789
790
791 exception
792 when no_data_found then
793
794 --dbms_output.put_line('exception '||SQLCODE||', '||SQLERRM);
795 retcode :=2;
796 errbuf := msc_phub_util.get_planning_hub_message('MSC_HUB_DUPLICATE_DATA')||SQLCODE||' -ERROR- '||SQLERRM;
797
798 if( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
799 fnd_log.string(fnd_log.LEVEL_UNEXPECTED,
800 l_api_name,
801 to_char(SQLCODE) || ':' || SQLERRM || ' in stmt_id=' || l_stmt_id);
802 end if;
803
804
805 when dup_val_on_index then
806 --dbms_output.put_line('exception '||SQLCODE||', '||SQLERRM);
807 retcode :=2;
808 errbuf := msc_phub_util.get_planning_hub_message('MSC_HUB_DUPLICATE_DATA')||SQLCODE||' -ERROR- '||SQLERRM;
809 if( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
810 fnd_log.string(fnd_log.LEVEL_UNEXPECTED,
811 l_api_name,
812 to_char(SQLCODE) || ':' || SQLERRM || ' in stmt_id=' || l_stmt_id);
813 end if;
814
815 when others then
816 --dbms_output.put_line('exception '||SQLCODE||', '||SQLERRM);
817 retcode :=2;
818 errbuf := msc_phub_util.get_planning_hub_message('MSC_HUB_POPULATE_ERROR')||SQLCODE||' -ERROR- '||SQLERRM;
819 if( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
820 fnd_log.string(fnd_log.LEVEL_UNEXPECTED,
821 l_api_name,
822 to_char(SQLCODE) || ':' || SQLERRM || ' in stmt_id=' || l_stmt_id);
823 end if;
824
825
826 end populate_details;
827
828 procedure populate_summary(errbuf out nocopy varchar2, retcode out nocopy varchar2,
829 p_plan_id number, p_plan_run_id number)
830 is
831 l_category_set_id1 number := fnd_profile.value('MSC_HUB_CAT_SET_ID_1');
832 begin
833 -- level 1
834 insert into msc_supplies_f (
835 plan_id, plan_run_id, io_plan_flag,
836 sr_instance_id, organization_id, inventory_item_id,
837 project_id, task_id,
838 supplier_id, supplier_site_id, region_id,
839 ship_method,
840 supply_date,
841 aggr_type, category_set_id, sr_category_id,
842 supply_type, vmi_flag,
843 supply_qty,
844 planned_order_count,
845 work_order_leadtime,
846 work_order_count,
847 work_order_qty,
848 stockout_days,
849 drp_supply_as_demand,
850 return_order_qty,
851 created_by, creation_date,
852 last_update_date, last_updated_by, last_update_login,
853 program_id, program_login_id,
854 program_application_id, request_id)
855 -- category (42, 43, 44)
856 select
857 f.plan_id, f.plan_run_id, f.io_plan_flag,
858 f.sr_instance_id, f.organization_id,
859 to_number(-23453) inventory_item_id,
860 f.project_id, f.task_id,
861 f.supplier_id, f.supplier_site_id, f.region_id,
862 f.ship_method,
863 f.supply_date,
864 to_number(42) aggr_type,
865 l_category_set_id1 category_set_id,
866 nvl(q.sr_category_id, -23453),
867 f.supply_type, f.vmi_flag,
868 sum(f.supply_qty),
869 sum(f.planned_order_count),
870 sum(f.work_order_leadtime),
871 sum(f.work_order_count),
872 sum(f.work_order_qty),
873 sum(f.stockout_days),
874 sum(f.drp_supply_as_demand),
875 sum(f.return_order_qty),
876 fnd_global.user_id, sysdate,
877 sysdate, fnd_global.user_id, fnd_global.login_id,
878 fnd_global.conc_program_id, fnd_global.conc_login_id,
879 fnd_global.prog_appl_id, fnd_global.conc_request_id
880 from
881 msc_supplies_f f,
882 msc_phub_item_categories_mv q
883 where f.plan_id = p_plan_id and f.plan_run_id = p_plan_run_id
884 and f.aggr_type=0
885 and f.sr_instance_id=q.sr_instance_id(+)
886 and f.organization_id=q.organization_id(+)
887 and f.inventory_item_id=q.inventory_item_id(+)
888 and q.category_set_id(+)=l_category_set_id1
889 group by
890 f.plan_id, f.plan_run_id, f.io_plan_flag,
891 f.sr_instance_id, f.organization_id,
892 f.project_id, f.task_id,
893 f.supplier_id, f.supplier_site_id, f.region_id,
894 f.ship_method,
895 f.supply_date,
896 nvl(q.sr_category_id, -23453),
897 f.supply_type, f.vmi_flag;
898
899 commit;
900
901 -- level 1
902 insert into msc_item_wips_f (
903 plan_id, plan_run_id,
904 sr_instance_id, organization_id, inventory_item_id,
905 vmi_flag, wip_start_date,
906 aggr_type, category_set_id, sr_category_id,
907 wip_qty,
908 created_by, creation_date,
909 last_update_date, last_updated_by, last_update_login,
910 program_id, program_login_id,
911 program_application_id, request_id)
912 -- category (42, 43, 44)
913 select
914 f.plan_id, f.plan_run_id,
915 f.sr_instance_id, f.organization_id,
916 to_number(-23453) inventory_item_id,
917 f.vmi_flag, f.wip_start_date,
918 to_number(42) aggr_type,
919 l_category_set_id1 category_set_id,
920 nvl(q.sr_category_id, -23453),
921 sum(f.wip_qty),
922 fnd_global.user_id, sysdate,
923 sysdate, fnd_global.user_id, fnd_global.login_id,
924 fnd_global.conc_program_id, fnd_global.conc_login_id,
925 fnd_global.prog_appl_id, fnd_global.conc_request_id
926 from
927 msc_item_wips_f f,
928 msc_phub_item_categories_mv q
929 where f.plan_id = p_plan_id and f.plan_run_id = p_plan_run_id
930 and f.aggr_type=0
931 and f.sr_instance_id=q.sr_instance_id(+)
932 and f.organization_id=q.organization_id(+)
933 and f.inventory_item_id=q.inventory_item_id(+)
934 and q.category_set_id(+)=l_category_set_id1
935 group by
936 f.plan_id, f.plan_run_id,
937 f.sr_instance_id, f.organization_id,
938 f.vmi_flag, f.wip_start_date,
939 nvl(q.sr_category_id, -23453);
940
941 commit;
942
943 -- level 2
944 insert into msc_supplies_f (
945 plan_id, plan_run_id, io_plan_flag,
946 sr_instance_id, organization_id, inventory_item_id,
947 project_id, task_id,
948 supplier_id, supplier_site_id, region_id,
949 ship_method,
950 supply_date,
951 aggr_type, category_set_id, sr_category_id,
952 supply_type, vmi_flag,
953 supply_qty,
954 planned_order_count,
955 work_order_leadtime,
956 work_order_count,
957 work_order_qty,
958 stockout_days,
959 drp_supply_as_demand,
960 return_order_qty,
961 created_by, creation_date,
962 last_update_date, last_updated_by, last_update_login,
963 program_id, program_login_id,
964 program_application_id, request_id)
965 -- category-mfg_period (1016, 1017, 1018)
966 select
967 f.plan_id, f.plan_run_id, f.io_plan_flag,
968 f.sr_instance_id, f.organization_id, f.inventory_item_id,
969 f.project_id, f.task_id,
970 f.supplier_id, f.supplier_site_id, f.region_id,
971 f.ship_method,
972 mp.period_start_date supply_date,
973 decode(f.aggr_type, 42, 1016, 43, 1017, 1018) aggr_type,
974 f.category_set_id, f.sr_category_id,
975 f.supply_type, f.vmi_flag,
976 sum(f.supply_qty),
977 sum(f.planned_order_count),
978 sum(f.work_order_leadtime),
979 sum(f.work_order_count),
980 sum(f.work_order_qty),
981 sum(f.stockout_days),
982 sum(f.drp_supply_as_demand),
983 sum(f.return_order_qty),
984 fnd_global.user_id, sysdate,
985 sysdate, fnd_global.user_id, fnd_global.login_id,
986 fnd_global.conc_program_id, fnd_global.conc_login_id,
987 fnd_global.prog_appl_id, fnd_global.conc_request_id
988 from
989 msc_supplies_f f,
990 msc_phub_mfg_cal_periods_mv mp
991 where f.plan_id = p_plan_id and f.plan_run_id = p_plan_run_id
992 and f.aggr_type between 42 and 44
993 and f.supply_date between mp.period_start_date and mp.period_end_date
994 group by
995 f.plan_id, f.plan_run_id, f.io_plan_flag,
996 f.sr_instance_id, f.organization_id, f.inventory_item_id,
997 f.project_id, f.task_id,
998 f.supplier_id, f.supplier_site_id, f.region_id,
999 f.ship_method,
1000 mp.period_start_date,
1001 decode(f.aggr_type, 42, 1016, 43, 1017, 1018),
1002 f.category_set_id, f.sr_category_id,
1003 f.supply_type, f.vmi_flag
1004 union all
1005 -- category-fiscal_period (1019, 1020, 1021)
1006 select
1007 f.plan_id, f.plan_run_id, f.io_plan_flag,
1008 f.sr_instance_id, f.organization_id, f.inventory_item_id,
1009 f.project_id, f.task_id,
1010 f.supplier_id, f.supplier_site_id, f.region_id,
1011 f.ship_method,
1012 fp.start_date supply_date,
1013 decode(f.aggr_type, 42, 1019, 43, 1020, 1021) aggr_type,
1014 f.category_set_id, f.sr_category_id,
1015 f.supply_type, f.vmi_flag,
1016 sum(f.supply_qty),
1017 sum(f.planned_order_count),
1018 sum(f.work_order_leadtime),
1019 sum(f.work_order_count),
1020 sum(f.work_order_qty),
1021 sum(f.stockout_days),
1022 sum(f.drp_supply_as_demand),
1023 sum(f.return_order_qty),
1024 fnd_global.user_id, sysdate,
1025 sysdate, fnd_global.user_id, fnd_global.login_id,
1026 fnd_global.conc_program_id, fnd_global.conc_login_id,
1027 fnd_global.prog_appl_id, fnd_global.conc_request_id
1028 from
1029 msc_supplies_f f,
1030 msc_phub_fiscal_periods_mv fp
1031 where f.plan_id = p_plan_id and f.plan_run_id = p_plan_run_id
1032 and f.aggr_type between 42 and 44
1033 and f.supply_date between fp.start_date and fp.end_date
1034 group by
1035 f.plan_id, f.plan_run_id, f.io_plan_flag,
1036 f.sr_instance_id, f.organization_id, f.inventory_item_id,
1037 f.project_id, f.task_id,
1038 f.supplier_id, f.supplier_site_id, f.region_id,
1039 f.ship_method,
1040 fp.start_date,
1041 decode(f.aggr_type, 42, 1019, 43, 1020, 1021),
1042 f.category_set_id, f.sr_category_id,
1043 f.supply_type, f.vmi_flag;
1044
1045 commit;
1046
1047 -- level 2
1048 insert into msc_item_wips_f (
1049 plan_id, plan_run_id,
1050 sr_instance_id, organization_id, inventory_item_id,
1051 vmi_flag, wip_start_date,
1052 aggr_type, category_set_id, sr_category_id,
1053 wip_qty,
1054 created_by, creation_date,
1055 last_update_date, last_updated_by, last_update_login,
1056 program_id, program_login_id,
1057 program_application_id, request_id)
1058 -- category-mfg_period (1016, 1017, 1018)
1059 select
1060 f.plan_id, f.plan_run_id,
1061 f.sr_instance_id, f.organization_id, f.inventory_item_id,
1062 f.vmi_flag,
1063 mp.period_start_date wip_start_date,
1064 decode(f.aggr_type, 42, 1016, 43, 1017, 1018) aggr_type,
1065 f.category_set_id, f.sr_category_id,
1066 sum(f.wip_qty),
1067 fnd_global.user_id, sysdate,
1068 sysdate, fnd_global.user_id, fnd_global.login_id,
1069 fnd_global.conc_program_id, fnd_global.conc_login_id,
1070 fnd_global.prog_appl_id, fnd_global.conc_request_id
1071 from
1072 msc_item_wips_f f,
1073 msc_phub_mfg_cal_periods_mv mp
1074 where f.plan_id = p_plan_id and f.plan_run_id = p_plan_run_id
1075 and f.aggr_type between 42 and 44
1076 and f.wip_start_date between mp.period_start_date and mp.period_end_date
1077 group by
1078 f.plan_id, f.plan_run_id,
1079 f.sr_instance_id, f.organization_id, f.inventory_item_id,
1080 f.vmi_flag,
1081 mp.period_start_date,
1082 decode(f.aggr_type, 42, 1016, 43, 1017, 1018),
1083 f.category_set_id, f.sr_category_id
1084 union all
1085 -- category-fiscal_period (1019, 1020, 1021)
1086 select
1087 f.plan_id, f.plan_run_id,
1088 f.sr_instance_id, f.organization_id, f.inventory_item_id,
1089 f.vmi_flag,
1090 fp.start_date wip_start_date,
1091 decode(f.aggr_type, 42, 1019, 43, 1020, 1021) aggr_type,
1092 f.category_set_id, f.sr_category_id,
1093 sum(f.wip_qty),
1094 fnd_global.user_id, sysdate,
1095 sysdate, fnd_global.user_id, fnd_global.login_id,
1096 fnd_global.conc_program_id, fnd_global.conc_login_id,
1097 fnd_global.prog_appl_id, fnd_global.conc_request_id
1098 from
1099 msc_item_wips_f f,
1100 msc_phub_fiscal_periods_mv fp
1101 where f.plan_id = p_plan_id and f.plan_run_id = p_plan_run_id
1102 and f.aggr_type between 42 and 44
1103 and f.wip_start_date between fp.start_date and fp.end_date
1104 group by
1105 f.plan_id, f.plan_run_id,
1106 f.sr_instance_id, f.organization_id, f.inventory_item_id,
1107 f.vmi_flag,
1108 fp.start_date,
1109 decode(f.aggr_type, 42, 1019, 43, 1020, 1021),
1110 f.category_set_id, f.sr_category_id;
1111
1112 commit;
1113
1114 exception
1115 when dup_val_on_index then
1116 errbuf := msc_phub_util.get_planning_hub_message('MSC_HUB_DUPLICATE_DATA')||
1117 SQLCODE||' -ERROR- '||SQLERRM;
1118 retcode := 2;
1119 --dbms_output.put_line(errbuf);
1120 when others then
1121 errbuf := msc_phub_util.get_planning_hub_message('MSC_HUB_POPULATE_ERROR')||
1122 SQLCODE||' -ERROR- '||SQLERRM;
1123 retcode := 2;
1124 --dbms_output.put_line(errbuf);
1125
1126 end populate_summary;
1127
1128 procedure purge_details(errbuf out nocopy varchar2, retcode out nocopy varchar2,
1129 p_plan_id number, p_plan_run_id number default null,
1130 p_commit_size in number default 1000) is
1131
1132
1133 l_num number;
1134 l_api_name varchar2(100);
1135 l_stmt_id number;
1136 begin
1137
1138 retcode :=0; -- this means successfully
1139 errbuf :='';
1140 ---Successfully populated msc_supplies_f table for plan_id =' || p_plan_id || ',plan_run_id=' || p_plan_run_id;
1141 -- initial there is no error message
1142 l_api_name := 'msc_supply_f_pkg.purge_details';
1143
1144
1145 while true loop
1146 l_stmt_id :=1;
1147 delete /*+ PARALLEL(mos) */ from msc_supplies_f
1148 where rownum<=p_commit_size and plan_id=p_plan_id and plan_run_id=nvl(p_plan_run_id,plan_run_id);
1149 l_stmt_id:=2;
1150 commit;
1151 -- exit when there are no more rows to delete.
1152 l_stmt_id:=3;
1153 select count(1) into l_num from msc_supplies_f
1154 where plan_id=p_plan_id and plan_run_id=nvl(p_plan_run_id,plan_run_id);
1155 if (l_num=0) then
1156 commit;
1157 exit;
1158 end if;
1159 end loop;
1160 exception
1161 when others then
1162 retcode :=2;
1163 errbuf := msc_phub_util.get_planning_hub_message('MSC_HUB_PURGE_ERROR')||SQLCODE||' -ERROR- '||SQLERRM;
1164 if( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
1165 fnd_log.string(fnd_log.LEVEL_UNEXPECTED,
1166 l_api_name,
1167 to_char(SQLCODE) || ':' || SQLERRM || ' in stmt_id=' || l_stmt_id);
1168 end if;
1169
1170 return;
1171 end purge_details;
1172
1173
1174 function implement_code (p_source_org_id in number,
1175 p_org_id in number,
1176 p_repetitive_type in number,
1177 p_source_supplier_id in number,
1178 p_planning_make_buy_code in number,
1179 p_build_in_wip_flag in number) return number is
1180
1181 begin
1182
1183 if (p_source_org_id is NULL) and (p_source_supplier_id is null) THEN
1184 if (p_planning_make_buy_code = 1) and (p_build_in_wip_flag= 1) THEN
1185 return 3;
1186 else
1187 return 2;
1188 end if;
1189 elsif (p_org_id = p_source_org_id ) then
1190 if (p_repetitive_type=2) then return 4;
1191 elsif (p_build_in_wip_flag=1) then return 3;
1192 else return 3;
1193 end if;
1194 elsif (p_source_org_id <>p_org_id ) then return 2;
1195 else return 2;
1196 end if;
1197 end implement_code;
1198
1199 end msc_supply_pkg;