VIEW: APPS.MSC_COST_BREAKDOWN_V

Source

View Text - Preformatted

SELECT mbid.plan_id, msc_get_name.plan_name(mbid.plan_id), mbid.sr_instance_id, mbid.organization_id, msc_get_name.org_code(mbid.organization_id, mbid.sr_instance_id), mbid.detail_date, mic.category_set_id, mic.sr_category_id, mic.category_name, mbid.inventory_item_id, msc_get_name.item_name(mbid.inventory_item_id, null, null, null), sum(nvl(mbid.production_cost,0)), sum(nvl(mbid.carrying_cost,0)), sum(nvl(mbid.purchasing_cost,0)), sum(nvl(MSC_ANALYSIS_PKG.get_plan_service_level( mbid.plan_id, 3, mbid.sr_instance_id, mbid.organization_id,mbid.inventory_item_id, mfq.date1, mfq.date2),0)), sum(nvl(mbid.int_repair_cost,0)), sum(nvl(mbid.ext_repair_cost,0)), sum(nvl(mbid.production_cost,0)) + sum(nvl(mbid.carrying_cost,0)) + sum(nvl(mbid.purchasing_cost,0)) + sum(nvl(mbid.int_repair_cost,0)) + sum(nvl(mbid.ext_repair_cost,0)) + sum(nvl(MSC_ANALYSIS_PKG.get_plan_service_level( mbid.plan_id, 3, mbid.sr_instance_id, mbid.organization_id,mbid.inventory_item_id, mfq.date1, mfq.date2),0)), mbid.detail_level, mbid.period_type, decode(nvl(mbid.period_type,0), '1', decode(nvl(mbid.detail_level,0),0,'PeriodTypeUrl','PeriodTypeNoUrl'), 'PeriodTypeNoUrl'), sum(nvl(mbid.mds_price,0)), mfq.query_id FROM msc_bis_inv_detail mbid, msc_item_categories mic, msc_form_query mfq WHERE mbid.sr_instance_id = mic.sr_instance_id and mbid.organization_id = mic.organization_id and mbid.inventory_item_id = mic.inventory_item_id and mbid.plan_id = mfq.number1 and nvl(mbid.period_type,0) = nvl(mfq.number2,0) and nvl(mbid.detail_level,0) = nvl(mfq.number3,0) and mbid.detail_date between mfq.date1 and mfq.date2 GROUP BY mbid.plan_id, mbid.sr_instance_id, mbid.organization_id, mbid.detail_date, mic.category_set_id, mic.sr_category_id, mic.category_name, mbid.inventory_item_id, mbid.detail_level, mbid.period_type, mfq.query_id

View Text - HTML Formatted

SELECT MBID.PLAN_ID
, MSC_GET_NAME.PLAN_NAME(MBID.PLAN_ID)
, MBID.SR_INSTANCE_ID
, MBID.ORGANIZATION_ID
, MSC_GET_NAME.ORG_CODE(MBID.ORGANIZATION_ID
, MBID.SR_INSTANCE_ID)
, MBID.DETAIL_DATE
, MIC.CATEGORY_SET_ID
, MIC.SR_CATEGORY_ID
, MIC.CATEGORY_NAME
, MBID.INVENTORY_ITEM_ID
, MSC_GET_NAME.ITEM_NAME(MBID.INVENTORY_ITEM_ID
, NULL
, NULL
, NULL)
, SUM(NVL(MBID.PRODUCTION_COST
, 0))
, SUM(NVL(MBID.CARRYING_COST
, 0))
, SUM(NVL(MBID.PURCHASING_COST
, 0))
, SUM(NVL(MSC_ANALYSIS_PKG.GET_PLAN_SERVICE_LEVEL( MBID.PLAN_ID
, 3
, MBID.SR_INSTANCE_ID
, MBID.ORGANIZATION_ID
, MBID.INVENTORY_ITEM_ID
, MFQ.DATE1
, MFQ.DATE2)
, 0))
, SUM(NVL(MBID.INT_REPAIR_COST
, 0))
, SUM(NVL(MBID.EXT_REPAIR_COST
, 0))
, SUM(NVL(MBID.PRODUCTION_COST
, 0)) + SUM(NVL(MBID.CARRYING_COST
, 0)) + SUM(NVL(MBID.PURCHASING_COST
, 0)) + SUM(NVL(MBID.INT_REPAIR_COST
, 0)) + SUM(NVL(MBID.EXT_REPAIR_COST
, 0)) + SUM(NVL(MSC_ANALYSIS_PKG.GET_PLAN_SERVICE_LEVEL( MBID.PLAN_ID
, 3
, MBID.SR_INSTANCE_ID
, MBID.ORGANIZATION_ID
, MBID.INVENTORY_ITEM_ID
, MFQ.DATE1
, MFQ.DATE2)
, 0))
, MBID.DETAIL_LEVEL
, MBID.PERIOD_TYPE
, DECODE(NVL(MBID.PERIOD_TYPE
, 0)
, '1'
, DECODE(NVL(MBID.DETAIL_LEVEL
, 0)
, 0
, 'PERIODTYPEURL'
, 'PERIODTYPENOURL')
, 'PERIODTYPENOURL')
, SUM(NVL(MBID.MDS_PRICE
, 0))
, MFQ.QUERY_ID
FROM MSC_BIS_INV_DETAIL MBID
, MSC_ITEM_CATEGORIES MIC
, MSC_FORM_QUERY MFQ
WHERE MBID.SR_INSTANCE_ID = MIC.SR_INSTANCE_ID
AND MBID.ORGANIZATION_ID = MIC.ORGANIZATION_ID
AND MBID.INVENTORY_ITEM_ID = MIC.INVENTORY_ITEM_ID
AND MBID.PLAN_ID = MFQ.NUMBER1
AND NVL(MBID.PERIOD_TYPE
, 0) = NVL(MFQ.NUMBER2
, 0)
AND NVL(MBID.DETAIL_LEVEL
, 0) = NVL(MFQ.NUMBER3
, 0)
AND MBID.DETAIL_DATE BETWEEN MFQ.DATE1
AND MFQ.DATE2 GROUP BY MBID.PLAN_ID
, MBID.SR_INSTANCE_ID
, MBID.ORGANIZATION_ID
, MBID.DETAIL_DATE
, MIC.CATEGORY_SET_ID
, MIC.SR_CATEGORY_ID
, MIC.CATEGORY_NAME
, MBID.INVENTORY_ITEM_ID
, MBID.DETAIL_LEVEL
, MBID.PERIOD_TYPE
, MFQ.QUERY_ID