VIEW: APPS.ISC_MSC_BIS_PLANS_V

Source

View Text - Preformatted

SELECT pl.plan_id ID, pl.compile_designator VALUE, pl.sr_instance_id||'-'||pl.organization_id OWNING_ORG_ID, pl.service_level SERVICE_LEVEL, cat.category_set_id CATEGORY_SET_ID, cat.category_set_name CATEGORY_SET_NAME, pl.curr_plan_type PLAN_TYPE, p.period_count PERIOD_COUNT FROM msc_plans pl, (SELECT category_set_id, category_set_name FROM msc_category_sets WHERE default_flag = 1 AND rownum = 1) cat, (SELECT count(*) period_count, mp.plan_id FROM msc_bis_periods mbp, msc_plans mp WHERE mbp.organization_id = mp.organization_id AND mbp.sr_instance_id = mp.sr_instance_id AND ((mbp.start_date between nvl(mp.data_start_date, sysdate) and mp.cutoff_date or mbp.end_date between nvl(mp.data_start_date,sysdate) and mp.cutoff_date) or (mp.data_start_date between mbp.start_date and mbp.end_date)) and mbp.adjustment_period_flag ='N' GROUP BY mp.plan_id) p WHERE p.plan_id = pl.plan_id

View Text - HTML Formatted

SELECT PL.PLAN_ID ID
, PL.COMPILE_DESIGNATOR VALUE
, PL.SR_INSTANCE_ID||'-'||PL.ORGANIZATION_ID OWNING_ORG_ID
, PL.SERVICE_LEVEL SERVICE_LEVEL
, CAT.CATEGORY_SET_ID CATEGORY_SET_ID
, CAT.CATEGORY_SET_NAME CATEGORY_SET_NAME
, PL.CURR_PLAN_TYPE PLAN_TYPE
, P.PERIOD_COUNT PERIOD_COUNT
FROM MSC_PLANS PL
, (SELECT CATEGORY_SET_ID
, CATEGORY_SET_NAME
FROM MSC_CATEGORY_SETS
WHERE DEFAULT_FLAG = 1
AND ROWNUM = 1) CAT
, (SELECT COUNT(*) PERIOD_COUNT
, MP.PLAN_ID
FROM MSC_BIS_PERIODS MBP
, MSC_PLANS MP
WHERE MBP.ORGANIZATION_ID = MP.ORGANIZATION_ID
AND MBP.SR_INSTANCE_ID = MP.SR_INSTANCE_ID
AND ((MBP.START_DATE BETWEEN NVL(MP.DATA_START_DATE
, SYSDATE)
AND MP.CUTOFF_DATE OR MBP.END_DATE BETWEEN NVL(MP.DATA_START_DATE
, SYSDATE)
AND MP.CUTOFF_DATE) OR (MP.DATA_START_DATE BETWEEN MBP.START_DATE
AND MBP.END_DATE))
AND MBP.ADJUSTMENT_PERIOD_FLAG ='N' GROUP BY MP.PLAN_ID) P
WHERE P.PLAN_ID = PL.PLAN_ID