VIEW: APPS.ISC_MSC_POST_COST_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(nvl(inv.inventory_value_no_post,0))/decode(avg(pl.period_count),0,1,avg(pl.period_count)) INVENTORY_VALUE_NO_POST, sum(nvl(inv.inventory_value_post,0))/decode(avg(pl.period_count),0,1,avg(pl.period_count)) 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 FROM msc_bis_inv_detail inv, msc_item_categories ic, isc_msc_bis_plans_v pl WHERE 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 pl.plan_type = 4 AND (inv.inventory_cost_no_post <> 0 OR inv.inventory_cost_post <> 0 OR inventory_value_post <> 0 OR inventory_value_no_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

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(NVL(INV.INVENTORY_VALUE_NO_POST
, 0))/DECODE(AVG(PL.PERIOD_COUNT)
, 0
, 1
, AVG(PL.PERIOD_COUNT)) INVENTORY_VALUE_NO_POST
, SUM(NVL(INV.INVENTORY_VALUE_POST
, 0))/DECODE(AVG(PL.PERIOD_COUNT)
, 0
, 1
, AVG(PL.PERIOD_COUNT)) 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
FROM MSC_BIS_INV_DETAIL INV
, MSC_ITEM_CATEGORIES IC
, ISC_MSC_BIS_PLANS_V PL
WHERE 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 PL.PLAN_TYPE = 4
AND (INV.INVENTORY_COST_NO_POST <> 0 OR INV.INVENTORY_COST_POST <> 0 OR INVENTORY_VALUE_POST <> 0 OR INVENTORY_VALUE_NO_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