[Home] [Help]
95: from msc_plans where plan_id = p_plan_id;
96:
97:
98: ---------------------------------------------------------------
99: -- insert vmi item in this plan into msc_hub_query by l_qid_vmi_item;
100: -- max possible rows insert =100;
101: -- we can verify the query with plan_id=63
102: -- l_qid_vim_item result will be used later to populate vmi_flag
103: ---------------------------------------------------------------
102: -- l_qid_vim_item result will be used later to populate vmi_flag
103: ---------------------------------------------------------------
104:
105: l_stmt_id :=10;
106: select msc_hub_query_s.nextval into l_qid_vmi_item from dual;
107:
108: insert into msc_hub_query(
109: query_id,
110: last_update_date,
104:
105: l_stmt_id :=10;
106: select msc_hub_query_s.nextval into l_qid_vmi_item from dual;
107:
108: insert into msc_hub_query(
109: query_id,
110: last_update_date,
111: last_updated_by,
112: creation_date,
146: -- in bucket's last working day
147: -- about 200 rows
148: ----------------------------------------------------------------------------
149: l_stmt_id:=20;
150: select msc_hub_query_s.nextval into l_qid_bucket from dual;
151: insert into msc_hub_query(
152: query_id,
153: last_update_date,
154: last_updated_by,
147: -- about 200 rows
148: ----------------------------------------------------------------------------
149: l_stmt_id:=20;
150: select msc_hub_query_s.nextval into l_qid_bucket from dual;
151: insert into msc_hub_query(
152: query_id,
153: last_update_date,
154: last_updated_by,
155: creation_date,
212: --- get week end date/week start date from msc_phub_mfg_cal_weeks_mv
213: --- so that we can merge msc_item_kpi_f with msc_item_inventory_f
214: -------------------------------------------------------------------
215: l_stmt_id:=25;
216: select msc_hub_query_s.nextval into l_qid_hub_week from dual;
217: insert into msc_hub_query (
218: query_id,
219: last_update_date,
220: last_updated_by,
213: --- so that we can merge msc_item_kpi_f with msc_item_inventory_f
214: -------------------------------------------------------------------
215: l_stmt_id:=25;
216: select msc_hub_query_s.nextval into l_qid_hub_week from dual;
217: insert into msc_hub_query (
218: query_id,
219: last_update_date,
220: last_updated_by,
221: creation_date,
243:
244:
245:
246: l_stmt_id:=26;
247: select msc_hub_query_s.nextval into l_qid_bis_week from dual;
248: insert into msc_hub_query (
249: query_id,
250: last_update_date,
251: last_updated_by,
244:
245:
246: l_stmt_id:=26;
247: select msc_hub_query_s.nextval into l_qid_bis_week from dual;
248: insert into msc_hub_query (
249: query_id,
250: last_update_date,
251: last_updated_by,
252: creation_date,
271: --- find the mapping between bis week and mfg week
272: ----------------------------------------------------------------------------
273:
274: l_stmt_id:=27;
275: select msc_hub_query_s.nextval into l_qid_week_map from dual;
276: insert into msc_hub_query (
277: query_id,
278: last_update_date,
279: last_updated_by,
272: ----------------------------------------------------------------------------
273:
274: l_stmt_id:=27;
275: select msc_hub_query_s.nextval into l_qid_week_map from dual;
276: insert into msc_hub_query (
277: query_id,
278: last_update_date,
279: last_updated_by,
280: creation_date,
288: l_sysdate,1,l_sysdate,1,1,
289: mfg.date1 hub_week_start_date,
290: mfg.date2 hub_week_end_date,
291: bis.date1
292: from msc_hub_query mfg,
293: msc_hub_query bis
294: where mfg.query_id=l_qid_hub_week
295: and bis.query_id=l_qid_bis_week
296: and bis.date1>=mfg.date1
289: mfg.date1 hub_week_start_date,
290: mfg.date2 hub_week_end_date,
291: bis.date1
292: from msc_hub_query mfg,
293: msc_hub_query bis
294: where mfg.query_id=l_qid_hub_week
295: and bis.query_id=l_qid_bis_week
296: and bis.date1>=mfg.date1
297: and bis.date1<=mfg.date2;
311: -- about 100 rows max
312: --------------------------------------------------------------------
313:
314: l_stmt_id :=30;
315: select msc_hub_query_s.nextval into l_qid_last_date1 from dual;
316:
317: insert into msc_hub_query (
318: query_id,
319: last_update_date,
313:
314: l_stmt_id :=30;
315: select msc_hub_query_s.nextval into l_qid_last_date1 from dual;
316:
317: insert into msc_hub_query (
318: query_id,
319: last_update_date,
320: last_updated_by,
321: creation_date,
353: -----------------------------------------------------------------
354:
355:
356: l_stmt_id :=35;
357: select msc_hub_query_s.nextval into l_qid_last_date from dual;
358:
359:
360: insert into msc_hub_query (
361: query_id,
356: l_stmt_id :=35;
357: select msc_hub_query_s.nextval into l_qid_last_date from dual;
358:
359:
360: insert into msc_hub_query (
361: query_id,
362: last_update_date,
363: last_updated_by,
364: creation_date,
371: )
372: select l_qid_last_date,
373: l_sysdate,1,l_sysdate,1,1,
374: f1.date1 last_date,
375: (select max(f2.date1) from msc_hub_query f2
376: where f2.date1<=f1.date1 and f2.query_id = l_qid_bucket) ,
377:
378: (select max(f3.date3) from msc_hub_query f3
379: where f3.date3<=f1.date1 and f3.query_id =l_qid_bucket)
374: f1.date1 last_date,
375: (select max(f2.date1) from msc_hub_query f2
376: where f2.date1<=f1.date1 and f2.query_id = l_qid_bucket) ,
377:
378: (select max(f3.date3) from msc_hub_query f3
379: where f3.date3<=f1.date1 and f3.query_id =l_qid_bucket)
380:
381: from msc_hub_query f1 where f1.query_id = l_qid_last_date1;
382:
377:
378: (select max(f3.date3) from msc_hub_query f3
379: where f3.date3<=f1.date1 and f3.query_id =l_qid_bucket)
380:
381: from msc_hub_query f1 where f1.query_id = l_qid_last_date1;
382:
383: --dbms_output.put_line(l_stmt_id||', l_qid_last_date='||l_qid_last_date||', count='||sql%rowcount);
384: commit;
385:
391: -- is included
392: -----------------------------------------------------------
393:
394: l_stmt_id :=40;
395: select msc_hub_query_s.nextval into l_qid_mil_item from dual;
396:
397: for c in c_plan_orgs
398: loop
399: insert into msc_hub_query (
395: select msc_hub_query_s.nextval into l_qid_mil_item from dual;
396:
397: for c in c_plan_orgs
398: loop
399: insert into msc_hub_query (
400: query_id,
401: last_update_date,
402: last_updated_by,
403: creation_date,
445: and sr_instance_id=c.sr_instance_id and organization_id=c.organization_id
446: union
447: select distinct mss.sr_instance_id, mss.organization_id, mss.inventory_item_id,
448: nvl(vmi.number6, 0) vmi_flag
449: from msc_safety_stocks mss, msc_hub_query vmi
450: where mss.plan_id=p_plan_id
451: and mss.plan_id=vmi.number1(+)
452: and mss.sr_instance_id=vmi.number3(+)
453: and mss.organization_id=vmi.number4(+)
463: t_bucket.date4,
464: t_bucket.date5,
465: t_bucket.number10, --bkt_type
466: t_bucket.number11 --days in bucket
467: from msc_hub_query t_bucket, msc_hub_query t_last_date
468: where t_bucket.query_id=l_qid_bucket
469: and t_last_date.query_id=l_qid_last_date
470: and t_bucket.date1=t_last_date.date2) f;
471:
480: -- is included
481: -----------------------------------------------------------
482:
483: l_stmt_id :=50;
484: select msc_hub_query_s.nextval into l_qid_sd_item from dual;
485:
486:
487: insert into msc_hub_query
488: (query_id,
483: l_stmt_id :=50;
484: select msc_hub_query_s.nextval into l_qid_sd_item from dual;
485:
486:
487: insert into msc_hub_query
488: (query_id,
489: last_update_date,
490: last_updated_by,
491: creation_date,
522: f.date2,
523: sd.activity_date,
524: f.number10,
525: f.number11
526: from msc_hub_query f,
527: (select unique
528: mdf.plan_id,
529: mdf.sr_instance_id ,
530: mdf.organization_id,
558:
559:
560: -----------------------------------------------------------------------------
561: l_stmt_id :=55;
562: select msc_hub_query_s.nextval into l_qid_pab_item from dual;
563:
564: insert into msc_hub_query
565: (query_id,
566: last_update_date,
560: -----------------------------------------------------------------------------
561: l_stmt_id :=55;
562: select msc_hub_query_s.nextval into l_qid_pab_item from dual;
563:
564: insert into msc_hub_query
565: (query_id,
566: last_update_date,
567: last_updated_by,
568: creation_date,
591: decode(sign(sd.sr_instance_id), -1, mp.sr_instance_id, sd.sr_instance_id)),
592: sd.organization_id),
593: decode(sign(sd.sr_instance_id), -1, mp.sr_instance_id, sd.sr_instance_id),
594: l.date3
595: from msc_hub_query l,
596: (select unique
597: mdf.plan_id,
598: mdf.sr_instance_id,
599: mdf.organization_id,
628: f.number6,
629: f.number7,
630: f.number8,
631: f.date3 --- activity date
632: from msc_hub_query f where f.query_id=l_qid_sd_item;
633:
634: --dbms_output.put_line(l_stmt_id||', l_qid_pab_item='||l_qid_pab_item||', count='||sql%rowcount);
635: commit;
636:
641: --------------------------------------------------------------------
642:
643:
644: l_stmt_id :=60;
645: select msc_hub_query_s.nextval into l_qid_pab from dual;
646:
647:
648: insert into msc_hub_query (
649: query_id,
644: l_stmt_id :=60;
645: select msc_hub_query_s.nextval into l_qid_pab from dual;
646:
647:
648: insert into msc_hub_query (
649: query_id,
650: last_update_date,
651: last_updated_by,
652: creation_date,
766: 11,nvl(msf.supply_qty,0),
767: 12,nvl(msf.supply_qty,0),
768: 14,nvl(msf.supply_qty,0), --- bug 6797566 include work oder co/by product
769: 0)) Scheduled_rept_qty
770: from msc_supplies_f msf,msc_hub_query mfq,msc_plans mps
771: where mfq.number1 = msf.plan_id(+)
772: and mfq.number2 = msf.plan_run_id(+)
773: and mfq.number3 = msf.sr_instance_id(+)
774: and mfq.number4 = msf.organization_id(+)
856: 0)) sales_order_Qty,
857: sum(decode(nvl(mdf.order_type,0),
858: -29,nvl(mdf.demand_qty,0),
859: 0)) forecast_Qty
860: from msc_demands_f mdf,msc_hub_query mfq1,
861: msc_plans mpd
862: where mfq1.number1 = mdf.plan_id(+)
863: and mfq1.number2 = mdf.plan_run_id(+)
864: and mfq1.number3 = mdf.sr_instance_id(+)
900: --
901: ------------------------------------------------------------
902:
903: l_stmt_id :=70;
904: select msc_hub_query_s.nextval into l_qid_ss_item from dual;
905:
906: insert into msc_hub_query
907: (query_id,
908: last_update_date,
902:
903: l_stmt_id :=70;
904: select msc_hub_query_s.nextval into l_qid_ss_item from dual;
905:
906: insert into msc_hub_query
907: (query_id,
908: last_update_date,
909: last_updated_by,
910: creation_date,
948: t_bucket.date4,
949: t_bucket.date5,
950: t_bucket.number10, --bkt_type
951: t_bucket.number11 --days in bucket
952: from msc_hub_query t_bucket, msc_hub_query t_last_date
953: where t_bucket.query_id=l_qid_bucket
954: and t_last_date.query_id=l_qid_last_date
955: and t_bucket.date1=t_last_date.date2) f2,
956: msc_hub_query vmi
952: from msc_hub_query t_bucket, msc_hub_query t_last_date
953: where t_bucket.query_id=l_qid_bucket
954: and t_last_date.query_id=l_qid_last_date
955: and t_bucket.date1=t_last_date.date2) f2,
956: msc_hub_query vmi
957: where mss.plan_id =p_plan_id
958: and mss.plan_id = vmi.number1(+)
959: and mss.sr_instance_id = vmi.number3(+)
960: and mss.organization_id = vmi.number4(+)
971: --------------------------------------------------------------------
972:
973:
974: l_stmt_id :=80;
975: select msc_hub_query_s.nextval into l_qid_ss from dual;
976:
977:
978: insert into msc_hub_query (
979: query_id,
974: l_stmt_id :=80;
975: select msc_hub_query_s.nextval into l_qid_ss from dual;
976:
977:
978: insert into msc_hub_query (
979: query_id,
980: last_update_date,
981: last_updated_by,
982: creation_date,
1014: LAST_VALUE(mss.user_defined_safety_stocks ignore nulls)
1015: OVER (PARTITION BY f.number1,f.number3,f.number4,f.number5
1016: ORDER by f.date1) user_defined_safety_stock_qty
1017: from msc_safety_stocks mss,
1018: msc_hub_query f
1019: where f.query_id=l_qid_ss_item
1020: and f.number1 = mss.plan_id(+)
1021: and f.number3 = mss.sr_instance_id(+)
1022: and f.number4 = mss.organization_id(+)
1029: ---------------------------------------------------------------------------
1030: --- calculate daily demand
1031: ---------------------------------------------------------------------------
1032: l_stmt_id :=90;
1033: select msc_hub_query_s.nextval into l_qid_others from dual;
1034:
1035:
1036: insert into msc_hub_query (
1037: query_id,
1032: l_stmt_id :=90;
1033: select msc_hub_query_s.nextval into l_qid_others from dual;
1034:
1035:
1036: insert into msc_hub_query (
1037: query_id,
1038: last_update_date,
1039: last_updated_by,
1040: creation_date,
1229: LAST_VALUE(p.number10 ignore nulls)
1230: OVER (PARTITION BY p.number1,p.number2,p.number3,
1231: p.number4,p.number5
1232: ORDER by p.date3) pab_qty
1233: from msc_hub_query l,msc_hub_query p
1234: where l.query_id =l_qid_last_date and p.query_id=l_qid_pab
1235: and l.date3 = p.date3) pab,
1236: msc_system_items m1,
1237: msc_trading_partners mtp,
1278: to_number(null) tp_cost,
1279: to_number(null) carrying_cost,
1280: to_number(null) total_cost,
1281: to_number(null) inv_build_target
1282: from msc_hub_query ss,
1283: msc_hub_query ss_last,
1284: msc_trading_partners ss_mtp
1285: where ss.query_id = l_qid_ss
1286: and ss_last.query_id =l_qid_last_date
1279: to_number(null) carrying_cost,
1280: to_number(null) total_cost,
1281: to_number(null) inv_build_target
1282: from msc_hub_query ss,
1283: msc_hub_query ss_last,
1284: msc_trading_partners ss_mtp
1285: where ss.query_id = l_qid_ss
1286: and ss_last.query_id =l_qid_last_date
1287: and ss_last.date2 = ss.date1 -- for each last day,pick out its bkt_start_date
1336: to_number(null) carrying_cost,
1337: to_number(null) total_cost,
1338: to_number(null) inv_build_target
1339:
1340: from msc_hub_query mil_ss,
1341: msc_hub_query mil_last,
1342: msc_trading_partners mil_mtp,
1343: (select
1344: item.number1 plan_id,
1337: to_number(null) total_cost,
1338: to_number(null) inv_build_target
1339:
1340: from msc_hub_query mil_ss,
1341: msc_hub_query mil_last,
1342: msc_trading_partners mil_mtp,
1343: (select
1344: item.number1 plan_id,
1345: item.number2 plan_run_id,
1351: nvl(mil.MIN_QUANTITY,mil_msi.MIN_MINMAX_QUANTITY) min_inventory_level,
1352: nvl(mil.max_quantity, mil_msi.MAX_MINMAX_QUANTITY) max_inventory_level
1353: from msc_inventory_levels mil,
1354: msc_system_items mil_msi,
1355: msc_hub_query item
1356: where item.query_id = l_qid_mil_item
1357: and item.number1 = mil.plan_id(+)
1358: and item.number3 = mil.sr_instance_id(+)
1359: and item.number4 = mil.organization_id(+)
1409: to_number(null) carrying_cost,
1410: to_number(null) total_cost,
1411: to_number(null) inv_build_target
1412:
1413: from msc_hub_query others,
1414: msc_hub_query others_vmi,
1415: msc_hub_query last_date1,
1416: msc_trading_partners mtp2
1417: where last_date1.query_id =l_qid_last_date
1410: to_number(null) total_cost,
1411: to_number(null) inv_build_target
1412:
1413: from msc_hub_query others,
1414: msc_hub_query others_vmi,
1415: msc_hub_query last_date1,
1416: msc_trading_partners mtp2
1417: where last_date1.query_id =l_qid_last_date
1418: and others_vmi.query_id(+) =l_qid_vmi_item
1411: to_number(null) inv_build_target
1412:
1413: from msc_hub_query others,
1414: msc_hub_query others_vmi,
1415: msc_hub_query last_date1,
1416: msc_trading_partners mtp2
1417: where last_date1.query_id =l_qid_last_date
1418: and others_vmi.query_id(+) =l_qid_vmi_item
1419: and others.query_id = l_qid_others
1460: nvl(mbid.carrying_cost,0) + nvl(mbid.TRANSPORTATION_COST,0) total_cost,
1461: to_number(null) inv_build_target
1462: ----------------------------------------------------------------------------
1463: from msc_bis_inv_detail mbid,
1464: msc_hub_query vmi,
1465: msc_hub_query map,
1466: msc_trading_partners bis_mtp,
1467: msc_plans mp
1468: where vmi.query_id(+) =l_qid_vmi_item
1461: to_number(null) inv_build_target
1462: ----------------------------------------------------------------------------
1463: from msc_bis_inv_detail mbid,
1464: msc_hub_query vmi,
1465: msc_hub_query map,
1466: msc_trading_partners bis_mtp,
1467: msc_plans mp
1468: where vmi.query_id(+) =l_qid_vmi_item
1469: and map.query_id = l_qid_week_map
1561: msi_1.inventory_item_id,
1562: msi_1.standard_cost,
1563: nvl(vmi.number6,0) vmi_flag
1564: from msc_system_items msi_1,
1565: msc_hub_query vmi
1566: where vmi.query_id(+) =l_qid_vmi_item
1567: and vmi.number1(+) = msi_1.plan_id
1568: and vmi.number3(+) = msi_1.sr_instance_id
1569: and vmi.number4(+) = msi_1.organization_id
1567: and vmi.number1(+) = msi_1.plan_id
1568: and vmi.number3(+) = msi_1.sr_instance_id
1569: and vmi.number4(+) = msi_1.organization_id
1570: and vmi.number5(+) = msi_1.inventory_item_id) msi_bis ,
1571: msc_hub_query map,
1572: msc_trading_partners bis_mtp,
1573: msc_plans mp
1574: where
1575: --vmi.query_id(+) =l_qid_vmi_item
1743: --------------------------------------------------------------
1744: to_number(null) no_activity_item_count,
1745: to_number(null) stock_outs_count
1746:
1747: from msc_hub_query sd,
1748: msc_hub_query sd_item,
1749: msc_hub_query others
1750: where sd.query_id =l_qid_pab
1751: and sd_item.query_id =l_qid_sd_item
1744: to_number(null) no_activity_item_count,
1745: to_number(null) stock_outs_count
1746:
1747: from msc_hub_query sd,
1748: msc_hub_query sd_item,
1749: msc_hub_query others
1750: where sd.query_id =l_qid_pab
1751: and sd_item.query_id =l_qid_sd_item
1752: and sd.number1 = sd_item.number1
1745: to_number(null) stock_outs_count
1746:
1747: from msc_hub_query sd,
1748: msc_hub_query sd_item,
1749: msc_hub_query others
1750: where sd.query_id =l_qid_pab
1751: and sd_item.query_id =l_qid_sd_item
1752: and sd.number1 = sd_item.number1
1753: and sd.number2 = sd_item.number2
1797: to_number(null) no_activity_item_count ,
1798: to_number(null) stock_outs_count
1799:
1800: from msc_full_pegging mfp,
1801: msc_hub_query peg_vmi,
1802: msc_supplies ms
1803: where ms.plan_id=mfp.plan_id
1804: and ms.TRANSACTION_ID = mfp.TRANSACTION_ID
1805: and ms.sr_instance_id = mfp.sr_instance_id
1850: to_number(null) qty_pegged_to_excess,
1851: ----------------------------------------------------------
1852: sum(decode(EXCEPTION_TYPE,5,EXCEPTION_COUNT,0) )no_activity_item_count,
1853: sum(decode(EXCEPTION_TYPE,2,exception_count,0)) stock_outs_count
1854: from msc_exceptions_f me, msc_hub_query vmi1
1855: where me.EXCEPTION_TYPE in (5,2)
1856: and me.plan_id = p_plan_id
1857: and me.plan_run_id = p_plan_run_id
1858: and me.aggr_type=0