VIEW: APPS.ISC_MSC_MFG_SERVICE_V

Source

View Text - Preformatted

SELECT sum(nvl(mfg.carrying_cost,0)) CARRYING_COST, sum(nvl(mfg.production_cost,0)) PRODUCTION_COST, sum(nvl(mfg.purchasing_cost,0)) PURCHASING_COST, sum(nvl(mfg.mds_price,0)) REVENUE, sum(nvl(mfg.inventory_value,0))/decode(avg(pl.period_count),0,1,avg(pl.period_count)) INVENTORY_VALUE, sv.total_demand TOTAL_DEMAND, sv.satisfied_demand SATISFIED_DEMAND, pl.service_level TARGET_SERVICE_LEVEL, mfg.plan_id, org.value owning_org, pl.value PLAN_NAME, pl.category_set_name FROM msc_bis_inv_detail mfg, (select sum(total_demand) total_demand, sum(satisfied_demand) satisfied_demand, plan_id from isc_msc_service_levels_v group by plan_id) sv, isc_msc_bis_plans_v pl, isc_msc_bis_inv_orgs_v org WHERE mfg.plan_id = sv.plan_id AND sv.plan_id = pl.id AND pl.owning_org_id = org.id AND sv.satisfied_demand <> 0 GROUP BY mfg.plan_id,org.value,sv.total_demand,sv.satisfied_demand,pl.value,pl.service_level,pl.category_set_name

View Text - HTML Formatted

SELECT SUM(NVL(MFG.CARRYING_COST
, 0)) CARRYING_COST
, SUM(NVL(MFG.PRODUCTION_COST
, 0)) PRODUCTION_COST
, SUM(NVL(MFG.PURCHASING_COST
, 0)) PURCHASING_COST
, SUM(NVL(MFG.MDS_PRICE
, 0)) REVENUE
, SUM(NVL(MFG.INVENTORY_VALUE
, 0))/DECODE(AVG(PL.PERIOD_COUNT)
, 0
, 1
, AVG(PL.PERIOD_COUNT)) INVENTORY_VALUE
, SV.TOTAL_DEMAND TOTAL_DEMAND
, SV.SATISFIED_DEMAND SATISFIED_DEMAND
, PL.SERVICE_LEVEL TARGET_SERVICE_LEVEL
, MFG.PLAN_ID
, ORG.VALUE OWNING_ORG
, PL.VALUE PLAN_NAME
, PL.CATEGORY_SET_NAME
FROM MSC_BIS_INV_DETAIL MFG
, (SELECT SUM(TOTAL_DEMAND) TOTAL_DEMAND
, SUM(SATISFIED_DEMAND) SATISFIED_DEMAND
, PLAN_ID
FROM ISC_MSC_SERVICE_LEVELS_V GROUP BY PLAN_ID) SV
, ISC_MSC_BIS_PLANS_V PL
, ISC_MSC_BIS_INV_ORGS_V ORG
WHERE MFG.PLAN_ID = SV.PLAN_ID
AND SV.PLAN_ID = PL.ID
AND PL.OWNING_ORG_ID = ORG.ID
AND SV.SATISFIED_DEMAND <> 0 GROUP BY MFG.PLAN_ID
, ORG.VALUE
, SV.TOTAL_DEMAND
, SV.SATISFIED_DEMAND
, PL.VALUE
, PL.SERVICE_LEVEL
, PL.CATEGORY_SET_NAME