VIEW: APPS.ISC_MSC_POST_COST_TREND_V

Source

View Text - Preformatted

SELECT sum(nvl(inv.inventory_cost_no_post,0)) INVENTORY_COST_NO_POST, sum(nvl(inv.inventory_cost_post,0)) INVENTORY_COST_POST, sum(inv.inventory_value_no_post) INVENTORY_VALUE_NO_POST, sum(inv.inventory_value_post) INVENTORY_VALUE_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 FROM msc_bis_inv_detail inv, msc_item_categories ic, isc_msc_bis_plans_v pl, isc_msc_bis_periods_v period 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 pl.plan_type = 4 AND (inv.inventory_cost_no_post <> 0 or inv.inventory_cost_post <> 0 or inv.inventory_value_no_post <> 0 or inv.inventory_value_post <> 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

View Text - HTML Formatted

SELECT SUM(NVL(INV.INVENTORY_COST_NO_POST
, 0)) INVENTORY_COST_NO_POST
, SUM(NVL(INV.INVENTORY_COST_POST
, 0)) INVENTORY_COST_POST
, SUM(INV.INVENTORY_VALUE_NO_POST) INVENTORY_VALUE_NO_POST
, SUM(INV.INVENTORY_VALUE_POST) INVENTORY_VALUE_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
FROM MSC_BIS_INV_DETAIL INV
, MSC_ITEM_CATEGORIES IC
, ISC_MSC_BIS_PLANS_V PL
, ISC_MSC_BIS_PERIODS_V PERIOD
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 PL.PLAN_TYPE = 4
AND (INV.INVENTORY_COST_NO_POST <> 0 OR INV.INVENTORY_COST_POST <> 0 OR INV.INVENTORY_VALUE_NO_POST <> 0 OR INV.INVENTORY_VALUE_POST <> 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