VIEW: APPS.ISC_MSC_MFG_MEASURES_V

Source

View Text - Preformatted

SELECT sum(NVL(inv.carrying_cost,0)) CARRYING_COST, sum(NVL(inv.demand_penalty_cost,0)) PENALTY_COST, sum(NVL(inv.production_cost,0)) PRODUCTION_COST, sum(NVL(inv.purchasing_cost,0)) PURCHASING_COST, sum(NVL(mds_price, 0)) REVENUE, sum(inv.mds_cost) COST, SUM(nvl(inv.inventory_value,0)) INVENTORY_VALUE, sum(inv.inventory_value_no_post) INVENTORY_VALUE_NO_POST, inv.plan_id PLAN_ID, pl.owning_org_id OWNING_ORG_ID, inv.sr_instance_id||'-'||inv.organization_id ORGANIZATION_ID, NVL(to_char(ic.category_set_id),'NA') CATEGORY_SET_ID, NVL(ic.category_set_id||ic.category_name,'NA') CATEGORY_ID, ic.inventory_item_id ITEM_ID, period.ID PERIOD_ID, qtr.ID QUARTER_ID, year.ID YEAR_ID FROM msc_bis_inv_detail inv, msc_item_categories ic, isc_msc_bis_plans_v pl, isc_msc_bis_periods_v period, isc_msc_bis_quarters_v qtr, isc_msc_bis_years_v year WHERE inv.detail_date between period.start_date and period.end_date AND pl.id = inv.plan_id AND ic.inventory_item_id(+) = inv.inventory_item_id AND ic.organization_id(+) = inv.organization_id AND ic.sr_instance_id(+) = inv.sr_instance_id AND period.organization_id =pl.owning_org_id AND qtr.organization_id = period.organization_id AND qtr.period_year = period.period_year AND period.start_date between qtr.start_date and qtr.end_date AND year.organization_id = period.organization_id AND year.period_year = period.period_year AND (inv.carrying_cost <> 0 or inv.production_cost <> 0 or purchasing_cost <> 0 or mds_price <> 0) GROUP BY inv.plan_id, pl.owning_org_id,inv.sr_instance_id, inv.organization_id,ic.category_set_id,ic.category_name,ic.inventory_item_id,period.id, qtr.id,year.id

View Text - HTML Formatted

SELECT SUM(NVL(INV.CARRYING_COST
, 0)) CARRYING_COST
, SUM(NVL(INV.DEMAND_PENALTY_COST
, 0)) PENALTY_COST
, SUM(NVL(INV.PRODUCTION_COST
, 0)) PRODUCTION_COST
, SUM(NVL(INV.PURCHASING_COST
, 0)) PURCHASING_COST
, SUM(NVL(MDS_PRICE
, 0)) REVENUE
, SUM(INV.MDS_COST) COST
, SUM(NVL(INV.INVENTORY_VALUE
, 0)) INVENTORY_VALUE
, SUM(INV.INVENTORY_VALUE_NO_POST) INVENTORY_VALUE_NO_POST
, INV.PLAN_ID PLAN_ID
, PL.OWNING_ORG_ID OWNING_ORG_ID
, INV.SR_INSTANCE_ID||'-'||INV.ORGANIZATION_ID ORGANIZATION_ID
, NVL(TO_CHAR(IC.CATEGORY_SET_ID)
, 'NA') CATEGORY_SET_ID
, NVL(IC.CATEGORY_SET_ID||IC.CATEGORY_NAME
, 'NA') CATEGORY_ID
, IC.INVENTORY_ITEM_ID ITEM_ID
, PERIOD.ID PERIOD_ID
, QTR.ID QUARTER_ID
, YEAR.ID YEAR_ID
FROM MSC_BIS_INV_DETAIL INV
, MSC_ITEM_CATEGORIES IC
, ISC_MSC_BIS_PLANS_V PL
, ISC_MSC_BIS_PERIODS_V PERIOD
, ISC_MSC_BIS_QUARTERS_V QTR
, ISC_MSC_BIS_YEARS_V YEAR
WHERE INV.DETAIL_DATE BETWEEN PERIOD.START_DATE
AND PERIOD.END_DATE
AND PL.ID = INV.PLAN_ID
AND IC.INVENTORY_ITEM_ID(+) = INV.INVENTORY_ITEM_ID
AND IC.ORGANIZATION_ID(+) = INV.ORGANIZATION_ID
AND IC.SR_INSTANCE_ID(+) = INV.SR_INSTANCE_ID
AND PERIOD.ORGANIZATION_ID =PL.OWNING_ORG_ID
AND QTR.ORGANIZATION_ID = PERIOD.ORGANIZATION_ID
AND QTR.PERIOD_YEAR = PERIOD.PERIOD_YEAR
AND PERIOD.START_DATE BETWEEN QTR.START_DATE
AND QTR.END_DATE
AND YEAR.ORGANIZATION_ID = PERIOD.ORGANIZATION_ID
AND YEAR.PERIOD_YEAR = PERIOD.PERIOD_YEAR
AND (INV.CARRYING_COST <> 0 OR INV.PRODUCTION_COST <> 0 OR PURCHASING_COST <> 0 OR MDS_PRICE <> 0) GROUP BY INV.PLAN_ID
, PL.OWNING_ORG_ID
, INV.SR_INSTANCE_ID
, INV.ORGANIZATION_ID
, IC.CATEGORY_SET_ID
, IC.CATEGORY_NAME
, IC.INVENTORY_ITEM_ID
, PERIOD.ID
, QTR.ID
, YEAR.ID