[Home] [Help]
PACKAGE BODY: APPS.MSC_SUPPLIER_PKG
Source
1 PACKAGE BODY msc_supplier_pkg AS
2 /* $Header: MSCHBSPB.pls 120.31.12010000.3 2008/09/03 14:42:52 wexia ship $ */
3 SYS_YES CONSTANT INTEGER := 1;
4 SYS_NO CONSTANT INTEGER := 2;
5
6 --supply order types begins
7 PLANNED_ORDER CONSTANT INTEGER := 5;
8 PURCHASE_ORDER CONSTANT INTEGER := 1;
9 PURCHASE_REQ CONSTANT INTEGER := 2;
10 PLANNED_ARRIVAL CONSTANT INTEGER := 51;
11 NEW_BUY_POS CONSTANT INTEGER := 76;
12 --supply order types ends
13
14 function is_new_buy_order(p_order_type number, p_plan_type number, p_purchasing_enabled_flag number) return number is
15 begin
16 if (p_plan_type in (1,2,3,5,6,8) and (p_order_type in (1,2,76) or (p_order_type=5 and p_purchasing_enabled_flag =1))) then
17 return 1;
18 end if;
19 return 0;
20 end is_new_buy_order;
21
22 function is_rescheduled_po(p_order_type number, p_rescheduled_flag number,
23 new_schedule_date date, old_schedule_date date) return number is
24 begin
25 if(p_order_type = 1) then
26 if((p_rescheduled_flag IS NOT NULL) and (new_schedule_date <> old_schedule_date))then
27 return 1;
28 end if;
29 end if;
30 return 0;
31 end is_rescheduled_po;
32
33
34 function is_cancelled_po(p_order_type number, p_disposition_status_type number) return number is
35 begin
36 if(p_order_type = 1) then
37 if(p_disposition_status_type = 2) then
38 return 1;
39 end if;
40 end if;
41 return 0;
42 end is_cancelled_po;
43
44 function supplier_spend_value(p_new_order_quantity number,
45 p_list_price number, p_order_type number) return number is
46 begin
47 if (p_order_type in (1,2,5,76)) then
48 return (p_new_order_quantity * p_list_price);
49 end if;
50 return 0;
51 end supplier_spend_value;
52
53
54 /*
55 l_qid_req: organization_id, required_qty, po% etc
56 l_qid_avail_req: avail_qty, net_avail_qty
57 l_qid_avail_cum (dense): avail_qty, net_avail_qty, net_avail_qty_cum
58
59 ETL steps:
60 10: populate l_qid_req from source
61 20: populate l_qid_avail_org from source
62 30: populate l_qid_avail_req from ((l_qid_req grouped to all orgs) union l_qid_avail_org distinct on org)
63 40: populate l_qid_avail_cum from (dense_time_key join l_qid_avail_req)
64 50: populate msc_suppliers_f from ((l_qid_avail_cum join dense_org_key) join l_qid_req)
65 */
66
67 procedure populate_details(errbuf out nocopy varchar2, retcode out nocopy number,
68 p_plan_id number, p_plan_run_id number) AS
69 l_plan_type number;
70 l_plan_constrained number;
71
72 l_stmt_id number ;
73
74 l_qid_req number;
75 l_qid_avail_org number;
76 l_qid_avail_req number;
77 l_qid_avail_cum number;
78
79 begin
80 retcode := 0;
81 errbuf := null;
82
83
84 --select CURR_PLAN_TYPE into g_plan_type from msc_plans where plan_id = p_plan_id;
85 l_plan_constrained := msc_phub_util.is_plan_constrained(p_plan_id);
86 l_plan_type := msc_phub_util.get_plan_type(p_plan_id);
87 --dbms_output.put_line('populate_details '||p_plan_id||', '||p_plan_run_id||', '||l_plan_type);
88
89 l_stmt_id:=10;
90 select msc_hub_query_s.nextval into l_qid_req from dual;
91 insert into msc_hub_query(
92 query_id,
93 last_update_date,
94 last_updated_by,
95 creation_date,
96 created_by,
97 last_update_login,
98 number1, -- plan_id
99 number2, -- plan_run_id
100 number3, -- sr_instance_id
101 number4, -- organization_id
102 char1, -- currency_code
103 number5, -- supplier_id
104 number6, -- supplier_site_id
105 number7, -- region_id
106 number8, -- supplier_site_id
107 date1, -- analysis_date
108 number10, -- required_qty
109 number11, -- po_reschedule_count
110 number12, -- po_count
111 number13, -- po_cancel_count
112 number14, -- buy_order_value
113 number15, -- buy_order_value2
114 number16 -- buy_order_count
115 )
116 select
117 l_qid_req, sysdate, 1, sysdate, 1, 1,
118 p_plan_id,
119 p_plan_run_id,
120 t.sr_instance_id,
121 t.organization_id,
122 t.currency_code,
123 t.supplier_id,
124 t.supplier_site_id,
125 mps.region_id,
126 t.inventory_item_id,
127 t.analysis_date,
128 sum(t.required_qty) required_qty,
129 sum(t.po_reschedule_count) po_reschedule_count,
130 sum(t.po_count) po_count,
131 sum(t.po_cancel_count) po_cancel_count,
132 sum(t.buy_order_value) buy_order_value,
133 sum(t.buy_order_value * decode(t.currency_code,
134 fnd_profile.value('MSC_HUB_CUR_CODE_RPT'), 1, nvl(mcc.conv_rate,0))) buy_order_value2,
135 sum(buy_order_count) buy_order_count
136 from (
137 select
138 msr.sr_instance_id sr_instance_id,
139 msr.organization_id organization_id,
140 nvl(mtp.currency_code, 'XXX') currency_code,
141 msr.supplier_id supplier_id,
142 nvl(msr.supplier_site_id, -23453) supplier_site_id,
143 to_number(-23453) region_id,
144 msr.inventory_item_id inventory_item_id,
145 trunc(msr.consumption_date) analysis_date,
146 sum(msr.consumed_quantity+msr.overloaded_capacity) required_qty,
147 to_number(null) po_reschedule_count,
148 to_number(null) po_count,
149 to_number(null) po_cancel_count,
150 to_number(null) buy_order_value,
151 to_number(null) buy_order_count
152 from msc_supplier_requirements msr,
153 msc_trading_partners mtp
154 where msr.plan_id = p_plan_id
155 and l_plan_constrained = SYS_YES
156 and msr.sr_instance_id = mtp.sr_instance_id
157 and msr.organization_id = mtp.sr_tp_id
158 and mtp.partner_type = 3
159 group by
160 msr.sr_instance_id,
161 msr.organization_id,
162 nvl(mtp.currency_code, 'XXX'),
163 msr.supplier_id,
164 nvl(msr.supplier_site_id,-23453),
165 msr.inventory_item_id,
166 trunc(msr.consumption_date)
167
168 union all
169 select
170 ms.sr_instance_id sr_instance_id,
171 ms.organization_id organization_id,
172 nvl(mtp.currency_code, 'XXX') currency_code,
173 decode(ms.order_type, PLANNED_ORDER, ms.source_supplier_id,
174 PLANNED_ARRIVAL, ms.source_supplier_id,
175 ms.supplier_id) supplier_id,
176 nvl(decode(ms.order_type, PLANNED_ORDER, ms.source_supplier_site_id,
177 PLANNED_ARRIVAL, ms.source_supplier_site_id,
178 ms.supplier_site_id), -23453) supplier_site_id,
179 to_number(-23453) region_id,
180 ms.inventory_item_id inventory_item_id,
181 -- SNO populates new_schedule_date
182 decode(l_plan_type, 6, trunc(ms.new_schedule_date), ms.new_order_placement_date) analysis_date,
183 sum(decode(mp.plan_type,
184 5, decode(nvl(ms.disposition_status_type,1),
185 1, ms.new_order_quantity,
186 0),
187 4, decode(nvl(ms.disposition_status_type,1),
188 1, ms.new_order_quantity,
189 0),
190
191 decode(nvl(ms.disposition_status_type,1),1,
192 decode(l_plan_constrained,2,ms.new_order_quantity,0),0)))required_qty,
193 sum(msc_supplier_pkg.is_rescheduled_po(ms.order_type, ms.reschedule_flag,
194 ms.new_schedule_date, ms.old_schedule_date)) po_rescheduled_count,
195 sum(decode(ms.order_type, 1, 1, 0)) po_count,
196 sum(msc_supplier_pkg.is_cancelled_po(ms.order_type,
197 ms.disposition_status_type)) po_cancel_count,
198 sum(msc_supplier_pkg.supplier_spend_value (ms.new_order_quantity,
199 nvl(ms.DELIVERY_PRICE,msi.list_price), ms.order_type)) buy_order_value,
200 sum(msc_supplier_pkg.is_new_buy_order(ms.order_type, l_plan_type, msi.purchasing_enabled_flag)) buy_order_count
201 from
202 MSC_SUPPLIES ms,
203 MSC_SYSTEM_ITEMS msi,
204 msc_plans mp,
205 msc_trading_partners mtp
206 where
207 mp.plan_id = p_plan_id
208 and l_plan_constrained = SYS_NO -- are we double counting constrained plan with previous?
209 and mp.plan_id = ms.plan_id
210 and ms.supplier_id is not null
211 and ms.plan_id = msi.plan_id
212 and ms.sr_instance_id = msi.sr_instance_id
213 and ms.organization_id = msi.organization_id
214 and ms.inventory_item_id = msi.inventory_item_id
215 and ms.order_type in (PLANNED_ORDER,PURCHASE_ORDER,PURCHASE_REQ,PLANNED_ARRIVAL,NEW_BUY_POS)
216 and ms.organization_id = mtp.sr_tp_id
217 and ms.sr_instance_id = mtp.sr_instance_id
218 and mtp.partner_type = 3
219 group by
220 ms.sr_instance_id,
221 ms.organization_id,
222 nvl(mtp.currency_code, 'XXX'),
223 decode(ms.order_type, PLANNED_ORDER, ms.source_supplier_id,
224 PLANNED_ARRIVAL, ms.source_supplier_id,
225 ms.supplier_id),
226 nvl(decode(ms.order_type, PLANNED_ORDER, ms.source_supplier_site_id,
227 PLANNED_ARRIVAL, ms.source_supplier_site_id,
228 ms.supplier_site_id), -23453),
229 ms.inventory_item_id,
230 decode(l_plan_type, 6, trunc(ms.new_schedule_date), ms.new_order_placement_date)
231
232 union all
233 select
234 mbid.sr_instance_id,
235 mbid.organization_id,
236 nvl(mtp.currency_code, 'XXX') currency_code,
237 mbid.supplier_id,
238 nvl(mbid.supplier_site_id, -23453) supplier_site_id,
239 nvl(mbid.zone_id, -23453) region_id,
240 mbid.inventory_item_id,
241 trunc(mbid.detail_date) analysis_date,
242 mbid.supplier_usage required_qty,
243 to_number(null) po_reschedule_count,
244 to_number(null) po_count,
245 to_number(null) po_cancel_count,
246 to_number(null) buy_order_value,
247 to_number(null) buy_order_count
248 from
249 msc_bis_inv_detail mbid,
250 msc_trading_partners mtp
251 where mbid.plan_id = p_plan_id
252 and mbid.supplier_id is not null
253 and mbid.organization_id = mtp.sr_tp_id
254 and mbid.sr_instance_id = mtp.sr_instance_id
255 and mtp.partner_type = 3
256 and l_plan_type = 6) t,
257
258 msc_currency_conv_mv mcc,
259 msc_phub_suppliers_mv mps
260
261 where mcc.from_currency(+) = t.currency_code
262 and mcc.to_currency(+) = fnd_profile.value('MSC_HUB_CUR_CODE_RPT')
263 and mcc.calendar_date(+) = t.analysis_date
264 and mps.supplier_id = nvl(t.supplier_id, -23453)
265 and mps.supplier_site_id = nvl(t.supplier_site_id, -23453)
266 and mps.region_id = decode(nvl(t.supplier_site_id, -23453),
267 -23453, nvl(t.region_id, -23453), mps.region_id)
268 group by
269 t.sr_instance_id,
270 t.organization_id,
271 t.currency_code,
272 t.supplier_id,
273 t.supplier_site_id,
274 mps.region_id,
275 t.inventory_item_id,
276 t.analysis_date;
277
278 --dbms_output.put_line('l_stmt_id='|| l_stmt_id||' count=' || SQL%ROWCOUNT || ' l_qid_req=' || l_qid_req);
279 commit;
280
281
282 l_stmt_id:=20;
283 select msc_hub_query_s.nextval into l_qid_avail_org from dual;
284 insert into msc_hub_query(
285 query_id,
286 last_update_date,
287 last_updated_by,
288 creation_date,
289 created_by,
290 last_update_login,
291 number1, -- plan_id
292 number2, -- plan_run_id
293 number3, -- sr_instance_id
294 number4, -- organization_id
295 number5, -- supplier_id
296 number6, -- supplier_site_id
297 number7, -- region_id
298 number8, -- supplier_site_id
299 date1, -- analysis_date
300 number20 -- avail_qty
301 )
302 select
303 l_qid_avail_org, sysdate, 1, sysdate, 1, 1,
304 p_plan_id,
305 p_plan_run_id,
306 t.sr_instance_id,
307 t.organization_id,
308 t.supplier_id,
309 t.supplier_site_id,
310 mps.region_id,
311 t.inventory_item_id,
312 t.analysis_date,
313 sum(t.avail_qty)
314 from
315 (select
316 mscp.sr_instance_id sr_instance_id,
317 mscp.organization_id organization_id,
318 mscp.supplier_id supplier_id,
319 nvl(mscp.supplier_site_id, -23453) supplier_site_id,
320 to_number(-23453) region_id,
321 mscp.inventory_item_id inventory_item_id,
322 trunc(mcd.calendar_date) analysis_date,
323 to_number(null) required_qty,
324 nvl(mscp.capacity, 1e20) avail_qty
325 from
326 msc_supplier_capacities mscp,
327 msc_calendar_dates mcd,
328 msc_trading_partners mtp,
329 msc_item_suppliers mis,
330 msc_plans mp
331 where mp.plan_id = mscp.plan_id
332 and mscp.capacity > 0
333 and mis.plan_id = mscp.plan_id
334 and mis.supplier_id = mscp.supplier_id
335 and mis.supplier_site_id = mscp.supplier_site_id
336 and mis.organization_id = mscp.organization_id
337 and mis.inventory_item_id = mscp.inventory_item_id
338 and mis.sr_instance_id = mscp.sr_instance_id
339 and mtp.sr_tp_id = mscp.organization_id
340 and mtp.sr_instance_id = mscp.sr_instance_id
341 and mtp.partner_type = 3
342 and mcd.calendar_date between trunc(mscp.from_date) and trunc(nvl(mscp.to_date,mp.cutoff_date))
343 and mcd.calendar_date between decode(mp.plan_type, 4, trunc(mp.curr_start_date),
344 nvl(trunc(mis.supplier_lead_time_date+1),trunc(mp.curr_start_date)))
345 and trunc(mp.curr_cutoff_date)
346 and (((mis.delivery_calendar_code is not null and mcd.seq_num is not null)
347 or (mis.delivery_calendar_code is null and mp.plan_type <> 4))
348 or (mp.plan_type = 4 and mcd.seq_num is not null))
349 and mcd.calendar_code = nvl(mis.delivery_calendar_code,mtp.calendar_code)
350 and mcd.exception_set_id = mtp.calendar_exception_set_id
351 and mcd.sr_instance_id = mtp.sr_instance_id
352 and mp.plan_id=p_plan_id
353
354 union all
355 select
356 mbid.sr_instance_id,
357 mbid.organization_id,
358 mbid.supplier_id,
359 nvl(mbid.supplier_site_id, -23453) supplier_site_id,
360 nvl(mbid.zone_id, -23453) region_id,
361 mbid.inventory_item_id,
362 trunc(mbid.detail_date) analysis_date,
363 to_number(null) required_qty,
364 mbid.supplier_capacity avail_qty
365 from
366 msc_bis_inv_detail mbid,
367 msc_trading_partners mtp
368 where mbid.plan_id = p_plan_id
369 and mbid.supplier_id is not null
370 and mbid.organization_id = mtp.sr_tp_id
371 and mbid.sr_instance_id = mtp.sr_instance_id
372 and mtp.partner_type = 3
373 and l_plan_type = 6) t,
374
375 msc_phub_suppliers_mv mps
376
377 where mps.supplier_id = nvl(t.supplier_id, -23453)
378 and mps.supplier_site_id = nvl(t.supplier_site_id, -23453)
379 and mps.region_id = decode(nvl(t.supplier_site_id, -23453),
380 -23453, nvl(t.region_id, -23453), mps.region_id)
381 group by
382 t.sr_instance_id,
383 t.organization_id,
384 t.supplier_id,
385 t.supplier_site_id,
386 mps.region_id,
387 t.inventory_item_id,
388 t.analysis_date;
389
390 --dbms_output.put_line('l_stmt_id='|| l_stmt_id||' count=' || SQL%ROWCOUNT || ' l_qid_avail_org=' || l_qid_avail_org);
391 commit;
392
393 l_stmt_id:=30;
394 select msc_hub_query_s.nextval into l_qid_avail_req from dual;
395 insert into msc_hub_query(
396 query_id,
397 last_update_date,
398 last_updated_by,
399 creation_date,
400 created_by,
401 last_update_login,
402 number1, -- plan_id
403 number2, -- plan_run_id
404 number3, -- sr_instance_id
405 number5, -- supplier_id
406 number6, -- supplier_site_id
407 number7, -- region_id
408 number8, -- supplier_site_id
409 date1, -- analysis_date
410 number10, -- required_qty
411 number20, -- avail_qty
412 number21 -- net_avail_qty
413
414 )
415 select
416 l_qid_avail_req, sysdate, 1, sysdate, 1, 1,
417 p_plan_id,
418 p_plan_run_id,
419 t.sr_instance_id,
420 t.supplier_id,
421 t.supplier_site_id,
422 t.region_id,
423 t.inventory_item_id,
424 t.analysis_date,
425 sum(t.required_qty),
426 sum(t.avail_qty),
427 sum(t.avail_qty) - sum(t.required_qty)
428 from
429 (select
430 number3 sr_instance_id,
431 number5 supplier_id,
432 number6 supplier_site_id,
433 number7 region_id,
434 number8 inventory_item_id,
435 date1 analysis_date,
436 sum(number10) required_qty,
437 to_number(null) avail_qty
438 from msc_hub_query
439 where query_id=l_qid_req
440 group by number3, number5, number6, number7, number8, date1
441
442 union all
443 select distinct
444 number3 sr_instance_id,
445 number5 supplier_id,
446 number6 supplier_site_id,
447 number7 region_id,
448 number8 inventory_item_id,
449 date1 analysis_date,
450 to_number(null) required_qty,
451 number20 avail_qty
452 from msc_hub_query
453 where query_id=l_qid_avail_org) t
454
455 group by
456 t.sr_instance_id,
457 t.supplier_id,
458 t.supplier_site_id,
459 t.region_id,
460 t.inventory_item_id,
461 t.analysis_date;
462
463 --dbms_output.put_line('l_stmt_id='|| l_stmt_id||' count=' || SQL%ROWCOUNT || ' l_qid_avail_req=' || l_qid_avail_req);
464 commit;
465
466 l_stmt_id:=40;
467 select msc_hub_query_s.nextval into l_qid_avail_cum from dual;
468 insert into msc_hub_query(
469 query_id,
470 last_update_date,
471 last_updated_by,
472 creation_date,
473 created_by,
474 last_update_login,
475 number1, -- plan_id
476 number2, -- plan_run_id
477 number3, -- sr_instance_id
478 number5, -- supplier_id
479 number6, -- supplier_site_id
480 number7, -- region_id
481 number8, -- supplier_site_id
482 date1, -- analysis_date
483 number10, -- required_qty
484 number20, -- avail_qty
485 number21, -- net_avail_qty
486 number22 -- net_avail_qty_cum
487 )
488 select
489 l_qid_avail_cum, sysdate, 1, sysdate, 1, 1,
490 p_plan_id,
491 p_plan_run_id,
492 k.sr_instance_id,
493 k.supplier_id,
494 k.supplier_site_id,
495 k.region_id,
496 k.inventory_item_id,
497 k.analysis_date,
498 f2.required_qty,
499 f2.avail_qty,
500 f2.net_avail_qty,
501 sum(f2.net_avail_qty) over(
502 partition by k.sr_instance_id, k.supplier_id, k.supplier_site_id, k.region_id, k.inventory_item_id
503 order by k.analysis_date) net_avail_qty_cum
504 from
505 (select
506 number3 sr_instance_id,
507 number5 supplier_id,
508 number6 supplier_site_id,
509 number7 region_id,
510 number8 inventory_item_id,
511 date1 analysis_date
512 from
513 (select distinct number3, number5, number6, number7, number8
514 from msc_hub_query where query_id=l_qid_avail_req),
515
516 (select distinct date1 from msc_hub_query where query_id=l_qid_avail_req)
517 ) k,
518
519 (select
520 number3 sr_instance_id,
521 number5 supplier_id,
522 number6 supplier_site_id,
523 number7 region_id,
524 number8 inventory_item_id,
525 date1 analysis_date,
526 number10 required_qty,
527 number20 avail_qty,
528 number21 net_avail_qty
529 from msc_hub_query
530 where query_id=l_qid_avail_req) f2
531
532 where k.sr_instance_id = f2.sr_instance_id(+)
533 and k.supplier_id = f2.supplier_id(+)
534 and k.supplier_site_id = f2.supplier_site_id(+)
535 and k.region_id = f2.region_id(+)
536 and k.inventory_item_id = f2.inventory_item_id(+)
537 and k.analysis_date = f2.analysis_date(+);
538
539 --dbms_output.put_line('l_stmt_id='|| l_stmt_id||' count=' || SQL%ROWCOUNT || ' l_qid_avail_cum=' || l_qid_avail_cum);
540 commit;
541
542 l_stmt_id:=50;
543 insert into msc_suppliers_f (
544 plan_id,
545 plan_run_id,
546 sr_instance_id,
547 organization_id,
548 supplier_id,
549 supplier_site_id,
550 region_id,
551 inventory_item_id,
552 analysis_date,
553 aggr_type, category_set_id, sr_category_id,
554 required_qty,
555 avail_qty,
556 net_avail_qty,
557 net_avail_qty_cum,
558 po_reschedule_count,
559 po_count,
560 po_cancel_count,
561 buy_order_value,
562 buy_order_value2,
563 buy_order_count,
564 created_by,
565 creation_date,
566 last_update_date,
567 last_updated_by,
568 last_update_login,
569 program_id,
570 program_login_id,
571 program_application_id,
572 request_id
573 )
574 select
575 p_plan_id,
576 p_plan_run_id,
577 f2.sr_instance_id,
578 f2.organization_id,
579 f2.supplier_id,
580 f2.supplier_site_id,
581 f2.region_id,
582 f2.inventory_item_id,
583 f2.analysis_date,
584 to_number(0) aggr_type,
585 to_number(-23453) category_set_id,
586 to_number(-23453) sr_category_id,
587 f1.required_qty,
588 f2.avail_qty,
589 f2.net_avail_qty,
590 f2.net_avail_qty_cum,
591 f1.po_reschedule_count,
592 f1.po_count,
593 f1.po_cancel_count,
594 f1.buy_order_value,
595 f1.buy_order_value2,
596 f1.buy_order_count,
597 fnd_global.user_id,
598 sysdate,
599 sysdate,
600 fnd_global.user_id,
601 fnd_global.login_id,
602 fnd_global.conc_program_id,
603 fnd_global.conc_login_id,
604 fnd_global.prog_appl_id,
605 fnd_global.conc_request_id
606 from
607 (select
608 number3 sr_instance_id,
609 number4 organization_id,
610 number5 supplier_id,
611 number6 supplier_site_id,
612 number7 region_id,
613 number8 inventory_item_id,
614 date1 analysis_date,
615 number10 required_qty,
616 number11 po_reschedule_count,
617 number12 po_count,
618 number13 po_cancel_count,
619 number14 buy_order_value,
620 number15 buy_order_value2,
621 number16 buy_order_count
622 from msc_hub_query
623 where query_id=l_qid_req) f1,
624
625 (select
626 number3 sr_instance_id,
627 number4 organization_id,
628 number5 supplier_id,
629 number6 supplier_site_id,
630 number7 region_id,
631 number8 inventory_item_id,
632 date1 analysis_date,
633 number20 avail_qty,
634 number21 net_avail_qty,
635 number22 net_avail_qty_cum
636 from
637 (select distinct number3, number5, number6, number7, number8, date1, number20, number21, number22
638 from msc_hub_query where query_id=l_qid_avail_cum),
639
640 (select distinct number4 from msc_hub_query where query_id in (l_qid_req, l_qid_avail_org))
641 ) f2
642
643 where f2.sr_instance_id = f1.sr_instance_id(+)
644 and f2.organization_id = f1.organization_id(+)
645 and f2.supplier_id = f1.supplier_id(+)
646 and f2.supplier_site_id = f1.supplier_site_id(+)
647 and f2.region_id = f1.region_id(+)
648 and f2.inventory_item_id = f1.inventory_item_id(+)
649 and f2.analysis_date = f1.analysis_date(+);
650
651 --dbms_output.put_line('l_stmt_id='|| l_stmt_id||' count=' || SQL%ROWCOUNT);
652 commit;
653
654
655 populate_summary(errbuf, retcode, p_plan_id, p_plan_run_id);
656
657 exception
658 when dup_val_on_index then
659 --dbms_output.put_line('exception '||SQLCODE||', '||SQLERRM);
660 errbuf := msc_phub_util.get_planning_hub_message('MSC_HUB_DUPLICATE_DATA')||SQLCODE||' -ERROR- '||SQLERRM;
661 retcode := 2;
662 when others then
663 --dbms_output.put_line('exception '||SQLCODE||', '||SQLERRM);
664 errbuf := msc_phub_util.get_planning_hub_message('MSC_HUB_POPULATE_ERROR')||SQLCODE||' -ERROR- '||SQLERRM;
665 retcode := 2;
666 end populate_details;
667
668 procedure populate_summary(errbuf out nocopy varchar2, retcode out nocopy varchar2,
669 p_plan_id number, p_plan_run_id number)
670 is
671 l_category_set_id1 number := fnd_profile.value('MSC_HUB_CAT_SET_ID_1');
672 begin
673 -- level 1
674 insert into msc_suppliers_f (
675 plan_id, plan_run_id,
676 sr_instance_id, organization_id,
677 supplier_id, supplier_site_id, region_id,
678 inventory_item_id,
679 analysis_date,
680 aggr_type, category_set_id, sr_category_id,
681 required_qty,
682 avail_qty,
683 net_avail_qty,
684 net_avail_qty_cum,
685 po_reschedule_count,
686 po_count,
687 po_cancel_count,
688 buy_order_value,
689 buy_order_value2,
690 buy_order_count,
691 created_by, creation_date,
692 last_update_date, last_updated_by, last_update_login,
693 program_id, program_login_id,
694 program_application_id, request_id)
695 -- category (42, 43, 44)
696 select
697 f.plan_id, f.plan_run_id,
698 f.sr_instance_id, f.organization_id,
699 f.supplier_id, f.supplier_site_id, f.region_id,
700 to_number(-23453) inventory_item_id,
701 f.analysis_date,
702 to_number(42) aggr_type,
703 l_category_set_id1 category_set_id,
704 nvl(q.sr_category_id, -23453),
705 sum(f.required_qty),
706 sum(f.avail_qty),
707 sum(f.net_avail_qty),
708 sum(f.net_avail_qty_cum),
709 sum(f.po_reschedule_count),
710 sum(f.po_count),
711 sum(f.po_cancel_count),
712 sum(f.buy_order_value),
713 sum(f.buy_order_value2),
714 sum(f.buy_order_count),
715 fnd_global.user_id, sysdate,
716 sysdate, fnd_global.user_id, fnd_global.login_id,
717 fnd_global.conc_program_id, fnd_global.conc_login_id,
718 fnd_global.prog_appl_id, fnd_global.conc_request_id
719 from
720 msc_suppliers_f f,
721 msc_phub_item_categories_mv q
722 where f.plan_id = p_plan_id and f.plan_run_id = p_plan_run_id
723 and f.aggr_type=0
724 and f.sr_instance_id=q.sr_instance_id(+)
725 and f.organization_id=q.organization_id(+)
726 and f.inventory_item_id=q.inventory_item_id(+)
727 and q.category_set_id(+)=l_category_set_id1
728 group by
729 f.plan_id, f.plan_run_id,
730 f.sr_instance_id, f.organization_id,
731 f.supplier_id, f.supplier_site_id, f.region_id,
732 f.analysis_date,
733 nvl(q.sr_category_id, -23453);
734
735 commit;
736
737 -- level 2
738 insert into msc_suppliers_f (
739 plan_id, plan_run_id,
740 sr_instance_id, organization_id,
741 supplier_id, supplier_site_id, region_id,
742 inventory_item_id,
743 analysis_date,
744 aggr_type, category_set_id, sr_category_id,
745 required_qty,
746 avail_qty,
747 net_avail_qty,
748 net_avail_qty_cum,
749 po_reschedule_count,
750 po_count,
751 po_cancel_count,
752 buy_order_value,
753 buy_order_value2,
754 buy_order_count,
755 created_by, creation_date,
756 last_update_date, last_updated_by, last_update_login,
757 program_id, program_login_id,
758 program_application_id, request_id)
759 -- category-fiscal_period (1019, 1020, 1021)
760 select
761 t.plan_id, t.plan_run_id,
762 t.sr_instance_id, t.organization_id,
763 t.supplier_id, t.supplier_site_id, t.region_id,
764 t.inventory_item_id,
765 t.analysis_date,
766 t.aggr_type,
767 t.category_set_id, t.sr_category_id,
768 t.required_qty,
769 t.avail_qty,
770 t.net_avail_qty,
771 sum(t.net_avail_qty) over(
772 partition by t.plan_id, t.plan_run_id,
773 t.sr_instance_id, t.organization_id,
774 t.supplier_id, t.supplier_site_id, t.region_id,
775 t.inventory_item_id, t.aggr_type,
776 t.category_set_id, t.sr_category_id
777 order by t.analysis_date) net_avail_qty_cum,
778 t.po_reschedule_count,
779 t.po_count,
780 t.po_cancel_count,
781 t.buy_order_value,
782 t.buy_order_value2,
783 t.buy_order_count,
784 fnd_global.user_id, sysdate,
785 sysdate, fnd_global.user_id, fnd_global.login_id,
786 fnd_global.conc_program_id, fnd_global.conc_login_id,
787 fnd_global.prog_appl_id, fnd_global.conc_request_id
788 from
789 (select
790 f.plan_id, f.plan_run_id,
791 f.sr_instance_id, f.organization_id,
792 f.supplier_id, f.supplier_site_id, f.region_id,
793 f.inventory_item_id,
794 fp.start_date analysis_date,
795 decode(f.aggr_type, 42, 1019, 43, 1020, 1021) aggr_type,
796 f.category_set_id, f.sr_category_id,
797 sum(f.required_qty) required_qty,
798 sum(f.avail_qty) avail_qty,
799 sum(f.net_avail_qty) net_avail_qty,
800 sum(f.po_reschedule_count) po_reschedule_count,
801 sum(f.po_count) po_count,
802 sum(f.po_cancel_count) po_cancel_count,
803 sum(f.buy_order_value) buy_order_value,
804 sum(f.buy_order_value2) buy_order_value2,
805 sum(f.buy_order_count) buy_order_count
806 from
807 msc_suppliers_f f,
808 msc_phub_fiscal_periods_mv fp
809 where f.plan_id = p_plan_id and f.plan_run_id = p_plan_run_id
810 and f.aggr_type between 42 and 44
811 and f.analysis_date between fp.start_date and fp.end_date
812 group by
813 f.plan_id, f.plan_run_id,
814 f.sr_instance_id, f.organization_id,
815 f.supplier_id, f.supplier_site_id, f.region_id,
816 f.inventory_item_id,
817 fp.start_date,
818 decode(f.aggr_type, 42, 1019, 43, 1020, 1021),
819 f.category_set_id, f.sr_category_id) t;
820
821 commit;
822
823 exception
824 when dup_val_on_index then
825 errbuf := msc_phub_util.get_planning_hub_message('MSC_HUB_DUPLICATE_DATA')||
826 SQLCODE||' -ERROR- '||SQLERRM;
827 retcode := 2;
828 --dbms_output.put_line(errbuf);
829 when others then
830 errbuf := msc_phub_util.get_planning_hub_message('MSC_HUB_POPULATE_ERROR')||
831 SQLCODE||' -ERROR- '||SQLERRM;
832 retcode := 2;
833 --dbms_output.put_line(errbuf);
834
835 end populate_summary;
836
837 procedure purge_details(errbuf out nocopy varchar2, retcode out nocopy number,
838 p_plan_id number, p_plan_run_id number) as
839 begin
840 retcode := 0;
841 errbuf := NULL;
842
843 --pabram.. will add code later to delete/truncate table/partition for this plan
844 delete from msc_suppliers_f
845 where plan_id = p_plan_id
846 and plan_run_id = nvl(p_plan_run_id,plan_run_id);
847
848 commit;
849 exception
850 when others then
851 errbuf := msc_phub_util.get_planning_hub_message('MSC_HUB_PURGE_ERROR')||SQLCODE||' -ERROR- '||SQLERRM;
852 retcode := 2;
853 end purge_details;
854 end msc_supplier_pkg;