[Home] [Help]
73: -- save owning organization's functional currency
74: begin
75: select o.currency_code
76: into l_owning_currency_code
77: from msc_trading_partners o, msc_plans p
78: where o.sr_instance_id=p.sr_instance_id
79: and o.sr_tp_id=p.organization_id
80: and o.partner_type=3
81: and p.plan_id=p_plan_id;
91: -----------------------------------------------------------------
92:
93: l_stmt_id :=0;
94: select curr_cutoff_date - curr_start_date +1 into l_plan_days
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;
196:
197: mpb.bucket_type,
198: mpb.days_in_bkt
199: from msc_plan_buckets mpb,
200: msc_plans mp
201: where mpb.plan_id =p_plan_id
202: and mp.plan_id=mpb.plan_id
203: and mpb.sr_instance_id = mp.sr_instance_id
204: and mpb.organization_id = mp.organization_id
228: l_sysdate,1,l_sysdate,1,1,
229: trunc(mw.week_start_date),
230: trunc(mw.WEEK_END_DATE) -- need trunc since end_date is in time stamp 23:59:59
231: from msc_phub_mfg_cal_weeks_mv mw,
232: msc_plans mp
233: where mp.curr_start_date<=mw.week_end_date
234: and mp.curr_cutoff_date >=mw.week_start_date -- curr_cutoff_date is in 00:00:00,
235: -- cutoff date >=week_start_date instead of week_end date
236: -- this is important to include the last week where
256: )
257: select unique l_qid_bis_week,
258: l_sysdate,1,l_sysdate,1,1,
259: trunc(mbid.detail_date)
260: from msc_bis_inv_detail mbid, msc_plans mp
261: where mbid.plan_id=p_plan_id
262: and mbid.plan_id=mp.plan_id
263: and (nvl(mbid.detail_level,0)=1 or mp.plan_type=6)
264: and nvl(mbid.period_type,0)=1;
546: from msc_supplies_f msf
547: where msf.plan_id = p_plan_id
548: and msf.plan_run_id = p_plan_run_id
549: and msf.aggr_type=0) sd,
550: msc_plans mp
551: where sd.activity_date = f.date3
552: and sd.plan_id = mp.plan_id
553: and f.query_id =l_qid_bucket;
554:
613: from msc_supplies_f msf
614: where msf.plan_id = p_plan_id
615: and msf.plan_run_id = p_plan_run_id
616: and msf.aggr_type=0) sd,
617: msc_plans mp
618: where l.query_id = l_qid_last_date
619: and sd.plan_id = mp.plan_id
620: union
621: select l_qid_pab_item,
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(+)
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(+)
865: and mfq1.number4 = mdf.organization_id(+)
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,
1238: msc_plans mp --- bug
1239: where pab.plan_id = m1.plan_id(+)
1240: and pab.sr_instance_id = m1.sr_instance_id(+)
1241: and pab.organization_id = m1.organization_id(+)
1242: and pab.inventory_item_id = m1.inventory_item_id(+)
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
1470: and map.date3 = trunc(mbid.detail_date)
1471: and nvl(mbid.detail_level,0)=1 and mp.plan_type<>6
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
1576: map.query_id = l_qid_week_map
1577: and map.date3 = trunc(mbid.detail_date)
1585: and mbid.organization_id = bis_mtp.sr_tp_id(+)
1586: and bis_mtp.partner_type(+) = 3
1587: and mbid.plan_id=mp.plan_id
1588:
1589: ) pab_tbl,msc_plans mp_tbl,
1590: MSC_CURRENCY_CONV_MV mcc
1591: where mcc.FROM_CURRENCY(+) =pab_tbl.currency_code --- make sure 'xxx' is not a valid currency code
1592: and mcc.TO_CURRENCY(+) = fnd_profile.value('MSC_HUB_CUR_CODE_RPT')
1593: and mcc.CALENDAR_DATE(+) = pab_tbl.order_date
1870: me.INVENTORY_ITEM_ID,
1871: nvl(vmi1.number6,0),
1872: me.ANALYSIS_DATE
1873:
1874: ) order_tbl,msc_plans mp_tbl
1875: where mp_tbl.plan_id = p_plan_id
1876: group by
1877: l_user_id,
1878: l_sysdate,