[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
Procedure update_plan (p_demand_plan_id in number,
p_ret_code in number);
SELECT base_uom,
demand_plan_id,
demand_plan_name,
enable_fcst_explosion,
g_min_tim_lvl_id,
f_min_tim_lvl_id,
c_min_tim_lvl_id,
m_min_tim_lvl_id,
use_org_specific_bom_flag,
stripe_sr_level_pk,
stripe_stream_name
FROM msd_demand_plans
WHERE demand_plan_id = p_demand_plan_id;
SELECT dimension_code
FROM msd_dp_dimensions_v
WHERE demand_plan_id = p_demand_plan_id
AND dp_dimension_code = p_dp_dimension_code
AND dimension_code = p_dimension_code;
SELECT COUNT(DISTINCT dp_dimension_code)
FROM msd_dp_dimensions_v
WHERE demand_plan_id = p_demand_plan_id;
SELECT COUNT(1)
FROM (
SELECT dp_dimension_code, count(1)
FROM msd_dp_dimensions_v
WHERE demand_plan_id = p_demand_plan_id
GROUP BY dp_dimension_code
HAVING COUNT(1) > 1);
SELECT DISTINCT dp_dimension_code
FROM msd_dp_dimensions_v mddv
WHERE demand_plan_id = p_demand_plan_id
and not exists (
select 1
from msd_cs_definitions mcd, msd_cs_defn_dim_dtls mcdd, msd_dp_parameters mdp
where mcd.cs_definition_id = mcdd.cs_definition_id
and mdp.parameter_type = mcd.name
and mdp.demand_plan_id = p_demand_plan_id
and mcdd.dimension_code = mddv.dp_dimension_code
and mcdd.collect_flag = C_YES_FLAG);
SELECT DISTINCT dp_dimension dp_dimension
FROM msd_dp_dimensions_v pd
WHERE demand_plan_id = p_demand_plan_id
AND dp_dimension_code <> 'TIM'
AND NOT EXISTS
(SELECT hierarchy_id
FROM msd_dp_hierarchies_v dh
WHERE pd.demand_plan_id = dh.demand_plan_id
AND pd.dp_dimension_code = dh.dp_dimension_code);
SELECT DISTINCT dimension dimension_code
FROM msd_dp_dimensions_v pd
WHERE demand_plan_id = p_demand_plan_id
AND dp_dimension_code <> 'TIM'
AND NOT EXISTS
(select hierarchy_id
from msd_dp_hierarchies_v dh
where pd.demand_plan_id = dh.demand_plan_id
and pd.dimension_code = dh.owning_dimension_code);
SELECT DISTINCT dp_dimension
FROM msd_dp_dimensions_v dd1
WHERE demand_plan_id = p_demand_plan_id
AND NOT EXISTS
(select 1
from msd_dp_dimensions_v dd2
where dd2.demand_plan_id = p_demand_plan_id
and dd1.dp_dimension_code = dd2.dimension_code
and dd1.dp_dimension_code = dd2.dp_dimension_code
);
select
dh.hierarchy_name,
hi.dimension_code
from
msd_dp_hierarchies_v dh,
msd_hierarchies hi
where
dh.hierarchy_id = hi.hierarchy_id and
dh.demand_plan_id = p_demand_plan_id and
hi.valid_flag <> '1';
SELECT DISTINCT dp_dimension_code,
hl. hierarchy_name,
level_name
FROM msd_dp_hierarchies_v dh,
msd_hierarchy_levels_v hl
WHERE demand_plan_id = p_demand_plan_id
AND dp_dimension_code <> 'TIM'
AND dh.hierarchy_id = hl.hierarchy_id
AND level_id NOT IN
(select distinct level_id
from msd_level_values lv);
SELECT MIN(day) min_date, MAX(day) max_date
FROM msd_time dp
WHERE dp.calendar_type = p_calendar_type
AND dp.calendar_code = p_calendar_code
AND day between p_start_date and p_end_date;
SELECT calendar_type, calendar_code, decode(calendar_type,
1, initcap(calendar_code),
calendar_code) op_cal_code
FROM msd_dp_calendars
WHERE demand_plan_id = p_demand_plan_id;
SELECT parameter_type,
parameter_name,
forecast_date_used,
count(*)
FROM msd_dp_parameters_cs_v
WHERE demand_plan_id = p_demand_plan_id
AND parameter_type_id <> '7'
GROUP BY parameter_type,
parameter_name,
forecast_date_used
HAVING COUNT(*) > 1;
SELECT mds.scenario_id,
mds.forecast_based_on,
mds.parameter_name,
mds.scenario_name,
mds.history_start_date,
mds.history_end_date,
mds.horizon_start_date,
mds.horizon_end_date,
mds.publish_flag,
mds.output_period_type,
csd.cs_definition_id,
csd.system_flag,
csd.multiple_stream_flag,
csd.allocation_allowed_flag,
csd.lowest_level_flag,
mdp.input_demand_plan_id,
mdp.input_scenario_id,
mdp.forecast_date_used,
mdp.start_date prm_start_date,
mdp.end_date prm_end_date,
nvl(mdp.view_name, nvl(csd.planning_server_view_name,'MSD_CS_DATA_V')) view_name,
msd_cs_dfn_utl.get_planning_server_clmn(csd.cs_definition_id, mdp.FORECAST_DATE_USED) date_planning_view_clmn,
cdd.collect_level_id
FROM msd_dp_scenarios mds,
msd_dp_parameters mdp,
msd_cs_definitions csd,
msd_cs_defn_dim_dtls cdd
WHERE mds.demand_plan_id = p_demand_plan_id
AND mds.enable_flag = 'Y'
AND mdp.demand_plan_id (+) = mds.demand_plan_id
AND mdp.parameter_type (+) = mds.forecast_based_on
AND mdp.forecast_date_used (+) = mds.forecast_date_used
AND nvl(mdp.parameter_name, '-*()') = nvl(mds.parameter_name, '-*()')
AND csd.name (+) = mdp.parameter_type
AND cdd.cs_definition_id (+) = csd.cs_definition_id
AND cdd.dimension_code (+) = 'TIM'
and cdd.collect_flag (+) = 'Y';
select count( distinct decode(mlv.level_id, 3, 1, mlv.level_id))
from msd_levels mlv,
msd_dp_scenario_output_levels mdsol,
msd_demand_plans mdp
where mdsol.demand_plan_id = p_demand_plan_id
and mdsol.demand_plan_id = mdp.demand_plan_id
and nvl(mlv.plan_type,'DP') = decode(mdp.plan_type,'SOP','DP','','DP',mdp.plan_type)
and mlv.level_id = mdsol.level_id
and mdsol.scenario_id = p_scenario_id
and mlv.level_id in (7, 1, 3);
select count( distinct decode(mlv.level_id, 3, 1, mlv.level_id))
from msd_levels mlv,
msd_dp_scenario_output_levels mdsol,
msd_demand_plans mdp
where mdsol.demand_plan_id = p_demand_plan_id
and mdsol.demand_plan_id = mdp.demand_plan_id
and nvl(mlv.plan_type,'DP') = decode(mdp.plan_type,'SOP','DP','','DP',mdp.plan_type)
and mlv.level_id = mdsol.level_id
and mdsol.scenario_id = p_scenario_id
and mlv.level_id in (1, 3);
SELECT scen.scenario_name,
ml.level_name,
ml.dimension_code
FROM msd_dp_scenario_output_levels a, msd_levels ml, msd_dp_scenarios scen, msd_demand_plans mdp
WHERE a.demand_plan_id = p_demand_plan_id
and a.demand_plan_id = mdp.demand_plan_id
and nvl(ml.plan_type,'DP') = decode(mdp.plan_type,'SOP','DP','','DP',mdp.plan_type)
AND a.level_id = ml.level_id
AND a.scenario_id = scen.scenario_id
AND scen.enable_flag = 'Y'
AND a.level_id not in
( select b.level_id
from msd_hierarchy_levels b,
msd_dp_hierarchies_v c
where b.hierarchy_id = c.hierarchy_id
and c.demand_plan_id = p_demand_plan_id
union
select b.parent_level_id
from msd_hierarchy_levels b,
msd_dp_hierarchies_v c
where b.hierarchy_id = c.hierarchy_id
and c.demand_plan_id = p_demand_plan_id
);
SELECT scen.scenario_name,ml.dimension_code, count(*)
FROM msd_dp_scenario_output_levels a, msd_levels ml, msd_dp_scenarios scen, msd_demand_plans mdp
WHERE a.level_id = ml.level_id
and a.demand_plan_id = mdp.demand_plan_id
and nvl(ml.plan_type,'DP') = decode(mdp.plan_type,'SOP','DP','','DP',mdp.plan_type)
AND a.scenario_id = scen.scenario_id
AND scen.enable_flag = 'Y'
and scen.demand_plan_id = p_demand_plan_id
group by scen.scenario_name,ml.dimension_code
having count(*) >1 ;
SELECT start_date, end_date
FROM msd_dp_parameters
WHERE demand_plan_id = p_demand_plan_id
AND parameter_type = p_parameter_type
AND (((parameter_name IS NULL) AND (p_cs_name IS NULL))
OR
((parameter_name IS NOT NULL) AND (p_cs_name IS NOT NULL)
AND (parameter_name = p_cs_name)))
AND (((forecast_date_used is NULL) and (p_date_used IS NULL))
OR
((forecast_date_used IS NOT NULL) AND (p_date_used IS NOT NULL)
AND (forecast_date_used = p_date_used)))
AND parameter_type <> '7';
SELECT DISTINCT mcd.description,
mcdd.dimension_code,
mcdd.collect_level_id level_id,
mcd.cs_definition_id,
mcd.multiple_stream_flag,
nvl(mcd.planning_server_view_name,'MSD_CS_DATA_V') planning_server_view_name,
mdp.parameter_name,
mdp.start_date,
mdp.end_date,
mcd.system_flag,
MSD_CS_DFN_UTL.get_planning_server_clmn(mcd.cs_definition_id, nvl(mdp.FORECAST_DATE_USED, 'END_DATE')) date_clmn,
mdp.input_demand_plan_id,
mdp.input_scenario_id,
mdp.revision
FROM msd_cs_defn_dim_dtls mcdd,
msd_dp_parameters mdp,
msd_cs_definitions mcd,
msd_dp_dimensions_v mdd
WHERE mdp.demand_plan_id = p_demand_plan_id
AND mdd.demand_plan_id = p_demand_plan_id
AND mdd.dimension_code = mcdd.dimension_code
AND mcdd.cs_definition_id = mcd.cs_definition_id
AND mcd.name = mdp.parameter_type
AND nvl(mcdd.collect_level_id, 0) not in
( select b.level_id
from msd_hierarchy_levels b,
msd_dp_hierarchies_v c
where b.hierarchy_id = c.hierarchy_id
and c.demand_plan_id = p_demand_plan_id
and mcdd.dimension_code <> 'TIM'
union
select b.parent_level_id
from msd_hierarchy_levels b,
msd_dp_hierarchies_v c
where b.hierarchy_id = c.hierarchy_id
and c.demand_plan_id = p_demand_plan_id
and mcdd.dimension_code <> 'TIM'
);
select 1
from msd_hierarchy_levels b,
msd_dp_hierarchies_v c
where b.hierarchy_id = c.hierarchy_id
and c.demand_plan_id = p_demand_plan_id
and (b.level_id = p_lvl_id
or
b.parent_level_id = p_lvl_id);
select meaning
from fnd_lookup_values_vl
where lookup_type = 'MSD_PERIOD_TYPE'
and lookup_code = p_lvl_id
and not exists (
select 1
from msd_dp_calendars
where demand_plan_id = p_demand_plan_id
and calendar_type = decode(p_lvl_id,
1,2,2,2,3,3,4,3,5,3,6,1,7,1,8,1,10,4,11,4,12,4,13,4,calendar_type));
SELECT 1
FROM msd_uom_conversions
WHERE (from_uom_code = p_base_uom OR to_uom_code = p_base_uom)
AND ROWNUM < 2;
SELECT 1
FROM msd_currency_conversions
WHERE from_currency = NVL(p_curr, from_currency)
AND conversion_date BETWEEN p_from_date AND p_to_date
AND ROWNUM < 2;
SELECT mdp.demand_plan_id,
mdp.parameter_id,
mdp.cs_definition_id,
mdp.system_flag, /* Add to view */
mdp.cs_type,
mdp.parameter_type_id,
mdp.parameter_type,
mdp.parameter_name,
mdp.multiple_stream_flag,
mdp.forecast_date_used,
mdp.date_planning_view_clmn,
mdp.start_date,
mdp.end_date,
mdp.view_name,
mdp.input_demand_plan_id,
mdp.input_scenario_id,
mdp.revision,
mdp.allo_agg_basis_stream_id,
mdp.ascp_stream_flag
FROM msd_dp_parameters_cs_v mdp
WHERE mdp.demand_plan_id = p_demand_plan_id
AND mdp.parameter_type_id <> '7';
SELECT decode(stripe_stream_name,
null, decode(stripe_sr_level_pk,
null,
nvl(planning_server_view_name,'MSD_CS_DATA_V'),
planning_server_view_name_ds),
planning_server_view_name_ds),
planning_server_view_name
FROM msd_cs_definitions_vl,
msd_demand_plans mdp
WHERE cs_definition_id = p_cs_def_id
and mdp.demand_plan_id = p_demand_plan_id;
SELECT count(1)
FROM msd_cs_defn_column_dtls a
WHERE a.cs_definition_id = p_cs_def_id
AND ((a.allocation_type IN ('AVG', 'WGT'))
OR (a.aggregation_type = 'WGT')
OR exists (select 1
from msd_cs_clmn_dim_dtls b
where a.cs_column_dtls_id = b.cs_column_dtls_id
and ((b.allocation_type IN ('AVG', 'WGT'))
OR (b.aggregation_type = 'WGT'))));
SELECT cs_definition_id
FROM msd_dp_parameters_cs_v
WHERE demand_plan_id = p_demand_plan_id
AND parameter_type = p_parameter_type
AND parameter_type <> '7';
SELECT DISTINCT DEF2.DESCRIPTION DESCRIPTION,
DEF2.COMPOSITE_GROUP_CODE COMPOSITE_GROUP_CODE
FROM MSD_CS_DEFINITIONS DEF1,
MSD_CS_DEFINITIONS DEF2,
MSD_CS_DEFN_DIM_DTLS DIM1,
MSD_CS_DEFN_DIM_DTLS DIM2,
MSD_DP_PARAMETERS MPV1,
MSD_DP_PARAMETERS MPV2
WHERE MPV1.DEMAND_PLAN_ID = p_demand_plan_id
AND DEF1.name = MPV1.parameter_type
AND DEF1.COMPOSITE_GROUP_CODE IS NOT NULL
AND NVL(DEF1.ENABLE_FLAG,'Y') = 'Y'
AND DIM1.CS_DEFINITION_ID = DEF1.CS_DEFINITION_ID
AND NVL(DEF2.ENABLE_FLAG,'Y') = 'Y'
AND NVL(DEF2.COMPOSITE_GROUP_CODE,-9999) = NVL(DEF1.COMPOSITE_GROUP_CODE,-9998)
AND DIM2.CS_DEFINITION_ID = DEF2.CS_DEFINITION_ID
AND DIM2.DIMENSION_CODE = DIM1.DIMENSION_CODE
AND DIM2.COLLECT_FLAG <> DIM1.COLLECT_FLAG
AND MPV2.DEMAND_PLAN_ID = p_demand_plan_id
AND MPV2.parameter_type <> MPV1.parameter_type
AND MPV2.parameter_type = DEF2.name;
SELECT DEF1.DESCRIPTION DESCRIPTION1,
DEF2.DESCRIPTION DESCRIPTION2,
DEF2.NAME NAME2,
DEF1.NAME NAME1,
DEF2.COMPOSITE_GROUP_CODE COMPOSITE_GROUP_CODE,
NVL(DIM1.COLLECT_LEVEL_ID,-1234) LEVEL_ID1,
NVL(DIM2.COLLECT_LEVEL_ID,-9999) LEVEL_ID2,
DIM1.DIMENSION_CODE DIM1_CODE,
DIM2.DIMENSION_CODE DIM2_CODE,
DEF1.CS_DEFINITION_ID CS_ID1,
DEF2.CS_DEFINITION_ID CS_ID2,
nvl(DEF1.PLANNING_SERVER_VIEW_NAME,'MSD_CS_DATA_V') VIEW_NAME1,
nvl(DEF2.PLANNING_SERVER_VIEW_NAME,'MSD_CS_DATA_V') VIEW_NAME2,
MPV1.START_DATE START_DATE1,
MPV1.END_DATE END_DATE1,
MPV2.START_DATE START_DATE2,
MPV2.END_DATE END_DATE2,
MPV1.INPUT_DEMAND_PLAN_ID INPUT_DEMAND_PLAN_ID1,
MPV2.INPUT_DEMAND_PLAN_ID INPUT_DEMAND_PLAN_ID2,
MPV1.INPUT_SCENARIO_ID INPUT_SCENARIO_ID1,
MPV2.INPUT_SCENARIO_ID INPUT_SCENARIO_ID2,
DECODE(DEF1.NAME,'MSD_INPUT_SCENARIO',MPV1.REVISION,MPV1.PARAMETER_NAME) PARAM1,
DECODE(DEF2.NAME,'MSD_INPUT_SCENARIO',MPV2.REVISION,MPV2.PARAMETER_NAME) PARAM2,
DECODE(DEF1.NAME,'MSD_INPUT_SCENARIO','TIME_LVL_VAL_FROM',MSD_CS_DFN_UTL.get_planning_server_clmn(def1.cs_definition_id, mpv1.FORECAST_DATE_USED)) DATE_CLMN1,
DECODE(DEF2.NAME,'MSD_INPUT_SCENARIO','TIME_LVL_VAL_FROM',MSD_CS_DFN_UTL.get_planning_server_clmn(def2.cs_definition_id, mpv2.FORECAST_DATE_USED)) DATE_CLMN2,
DEF1.SYSTEM_FLAG SYSTEM_FLAG1,
DEF2.SYSTEM_FLAG SYSTEM_FLAG2,
DEF1.MULTIPLE_STREAM_FLAG MULTI_STREAM_FLAG1,
DEF2.MULTIPLE_STREAM_FLAG MULTI_STREAM_FLAG2
FROM MSD_CS_DEFINITIONS DEF1,
MSD_CS_DEFINITIONS DEF2,
MSD_CS_DEFN_DIM_DTLS DIM1,
MSD_CS_DEFN_DIM_DTLS DIM2,
MSD_DP_PARAMETERS MPV1,
MSD_DP_PARAMETERS MPV2
WHERE MPV1.DEMAND_PLAN_ID = p_demand_plan_id
AND DEF1.name = MPV1.parameter_type
AND NVL(DEF1.ENABLE_FLAG,'Y') = 'Y'
--AND NVL(DEF1.LOWEST_LEVEL_FLAG,1) = 0
AND DIM1.CS_DEFINITION_ID = DEF1.CS_DEFINITION_ID
AND DIM1.COLLECT_FLAG = 'Y'
AND NVL(DEF2.ENABLE_FLAG,'Y') = 'Y'
--AND NVL(DEF2.LOWEST_LEVEL_FLAG,1) = 0
AND NVL(DEF2.COMPOSITE_GROUP_CODE,-9999) = NVL(DEF1.COMPOSITE_GROUP_CODE,-1234)
AND DIM2.CS_DEFINITION_ID = DEF2.CS_DEFINITION_ID
AND DIM2.DIMENSION_CODE = DIM1.DIMENSION_CODE
AND DIM2.COLLECT_FLAG = 'Y' -- Bug# 4562757
AND NVL(DIM2.COLLECT_LEVEL_ID,-9999) <> NVL(DIM1.COLLECT_LEVEL_ID,-1234)
AND MPV2.DEMAND_PLAN_ID = p_demand_plan_id
AND MPV2.parameter_type <> MPV1.parameter_type
AND MPV2.parameter_type = DEF2.name
ORDER BY NAME2;
SELECT decode(planning_view_column_name,'TIM_LEVEL_ID','TIME_LEVEL_ID',
planning_view_column_name)
FROM msd_cs_defn_column_dtls_v
WHERE column_identifier = upper(p_dim_code)||'_LEVEL_ID'
AND identifier_type = 'DIMENSION_ID'
AND cs_definition_id = p_cs_id;
SELECT count(1)
FROM msd_time mtv, msd_dp_calendars mdc
WHERE mtv.day = p_date
AND rownum = 1
AND mtv.calendar_type = mdc.calendar_type
AND mtv.calendar_code = mdc.calendar_code
AND mdc.demand_plan_id = p_demand_plan_id;
select
--scen.scenario_name,
dim1||decode(dim2,NULL,'',','||dim2)||decode(dim3,NULL,'',','||dim3)||decode(dim4,NULL,'',','||dim4)||decode(dim5,NULL,'',','||dim5)||decode(dim6,NULL,'',','||dim6)||decode(dim7,NULL,'',','||dim7)||decode(dim8,NULL,'',','||dim8)
from (
select demand_plan_id,scenario_id,level_id as dim1,
LEAD(level_id,1) over (partition by scenario_id order by level_id) as dim2,
LEAD(level_id,2) over (partition by scenario_id order by level_id) as dim3,
LEAD(level_id,3) over (partition by scenario_id order by level_id) as dim4,
LEAD(level_id,4) over (partition by scenario_id order by level_id) as dim5,
LEAD(level_id,5) over (partition by scenario_id order by level_id) as dim6,
LEAD(level_id,6) over (partition by scenario_id order by level_id) as dim7,
LEAD(level_id,7) over (partition by scenario_id order by level_id) as dim8,
row_number() over (partition by scenario_id order by level_id) as rno
from msd_dp_scenario_output_levels
) a,
msd_dp_scenarios scen
where a.rno=1
and a.scenario_id = scen.scenario_id
and a.demand_plan_id = scen.demand_plan_id
and scen.demand_plan_id = p_demand_plan_id
and scen.scenario_id = p_scenario_id;
select a.scenario_id ,a.scenario_name fcst_scenario_name,a.dmd_priority_scenario_id,b.scenario_name pri_scenario_name
from msd_dp_scenarios a,
msd_dp_scenarios b
where a.demand_plan_id = p_demand_plan_id
and a.dmd_priority_scenario_id is not null
and b.scenario_id = a.dmd_priority_scenario_id
and b.demand_plan_id = p_demand_plan_id;
SELECT plan_type
INTO l_plan_type
FROM msd_demand_plans
WHERE demand_plan_id = p_demand_plan_id;
/* Build/Update Stripe for demand plan */
msd_stripe_demand_plan.stripe_demand_plan(errbuf,
retcode,
p_demand_plan_id);
update_plan(p_demand_plan_id, g_ret_code);
select name
from msd_cs_definitions
where cs_definition_id = p_cs_definition_id;
SELECT MDD.DP_DIMENSION_CODE base_dim_code FROM msd_dP_dimensions MDD
where MDD.demand_plan_id =p_demand_plan_id
and MDD.dimension_code<>MDD.dp_dimension_code
AND MDD.DP_DIMENSION_CODE NOT IN
(SELECT MCD.DIMENSION_CODE
FROM MSD_CS_DEFN_DIM_DTLS MCD
WHERE MCD.CS_DEFINITION_ID IN
(select MCDS.CS_DEFINITION_ID
FROM MSD_CS_DEFINITIONS MCDS
WHERE MCDS.NAME = P_PARA_TYPE)
and mcd.collect_flag='Y')
AND MDD.DIMENSION_CODE IN
(SELECT MCD.DIMENSION_CODE
FROM MSD_CS_DEFN_DIM_DTLS MCD
WHERE MCD.CS_DEFINITION_ID IN
(select MCDS.CS_DEFINITION_ID
FROM MSD_CS_DEFINITIONS MCDS
WHERE MCDS.NAME = P_PARA_TYPE)
and mcd.collect_flag='Y');
Select decode(output_period_type,
9, 0,
8, 1,
7, 1,
6, 1,
5, 3,
4, 3,
3, 3,
2, 2,
1, 2,
4)
from msd_dp_scenarios md
where md.scenario_id = p_sc_id;
select count(1)
from msd_dp_calendars
where demand_plan_id = p_demand_plan_id;
select count(1)
from msd_dp_calendars
where demand_plan_id = p_demand_plan_id
and calendar_type = p_cal_type;
Select decode(p_lvl_id,
9, 0,
8, 1,
7, 1,
6, 1,
5, 3,
4, 3,
3, 3,
2, 2,
1, 2,
4)
from dual;
|| 'has more than one levels selected.', ERROR);
select
min(start_date), max(end_date)
from
msd_dp_parameters_cs_v
where
demand_plan_id = p_demand_plan_id;
select mdp.parameter_id
from msd_dp_parameters mdp,
msd_cs_definitions mcd,
msc_plans mp
where mdp.demand_plan_id = p_demand_plan_id
and mdp.parameter_type = mcd.name
and nvl(mcd.MULTIPLE_STREAM_FLAG,'N') = 'Y'
and mdp.parameter_name = mp.compile_designator;
select parameter_id
from msd_dp_parameters
where demand_plan_id = p_demand_plan_id
and parameter_id not in ( select mdp.parameter_id
from msd_dp_parameters mdp,
msd_cs_definitions mcd,
msc_plans mp
where mdp.demand_plan_id = p_demand_plan_id
and mdp.parameter_type = mcd.name
and nvl(mcd.MULTIPLE_STREAM_FLAG,'N') = 'Y'
and mdp.parameter_name = mp.compile_designator);
update msd_dp_parameters
set ascp_stream_flag='Y'
where demand_plan_id=p_demand_plan_id
and parameter_id =l_parameter_id;
update msd_dp_parameters
set ascp_stream_flag='N'
where demand_plan_id=p_demand_plan_id
and parameter_id =l_parameter_id;
* which rows are deleted...for Net Change
*/
cursor get_stream_type(p_cs_definition_id number) is
select cs_type
from msd_cs_definitions
where cs_definition_id = p_cs_definition_id;
select collect_level_id
from msd_cs_defn_dim_dtls
where cs_definition_id = p_cs_definition_id
and dimension_code = p_dim_code;
select decode(p_lvl_id,
'1', 'WEEK_END_DATE ',
'2', 'MONTH_END_DATE ',
'3', 'MONTH_END_DATE ',
'4', 'QUARTER_END_DATE ',
'5', 'YEAR_END_DATE ',
'6', 'MONTH_END_DATE ',
'7', 'QUARTER_END_DATE ',
'8', 'YEAR_END_DATE ',
'9', 'DAY',
'10', 'WEEK_END_DATE ',
'11', 'MONTH_END_DATE ',
'12', 'QUARTER_END_DATE ',
'13', 'YEAR_END_DATE ',
'DAY ')
from dual;
select decode (p_time_level_id,
1, '2',
2, '2',
3, '3',
4, '3',
5, '3',
6, '1',
7, '1',
8, '1',
10, '4',
11, '4',
12, '4',
13, '4',
'tim.calendar_type')
from dual;
* SELECT count(1)
* FROM "User Defined Planning Server View Name"
* WHERE "User Defined Server View Date Column" IS BETWEEN "Start Date" AND "End Date"
*
*
* Structure of the Dynamic SQL String is as follows for Streams using Custom Stream Fact Tables:
*
* SELECT count(1)
* FROM "Custom Stream Planning Server View Name"
* WHERE "Custom Stream Planning Server View Date Column" IS BETWEEN "Start Date" AND "End Date"
* AND Custom_Stream_Definition = "Parameter Custom Stream Definition"
*
* Structure of the Dynamic SQL String is as follows for Input Scenario Streams
*
* SELECT count(1)
* FROM msd_dp_scenario_entries
* WHERE tim_lvl_val_to IS BETWEEN "Start Date" AND "End Date"
* AND scenario_id = input_scenario_id
* AND ...
**************************************************************************************/
IF ( ((l_source IS NOT NULL) AND
(l_date_col IS NOT NULL) AND
(
(nvl(c_input_rec.multiple_stream_flag,'N') = 'Y' AND l_name IS NOT NULL)
OR
(nvl(c_input_rec.multiple_stream_flag,'N') <> 'Y')
)
)
OR
(c_input_rec.parameter_type_id in ('MSD_INPUT_SCENARIO'))) THEN
BEGIN --2
/* History Start Date and End Date */
l_start_date := 'TO_DATE(''' || TO_CHAR(c_input_rec.start_date, 'ddmmyyyy') || ''', ''ddmmyyyy'')';
/* Select, FROM portions of Dynamic Statement */
l_stmt := 'SELECT COUNT(*) FROM ' || l_source || ' src WHERE ';
l_new_stmt := 'SELECT COUNT(*) FROM ' || l_source || ' src, msc_plans desig WHERE ';
l_stmt := l_stmt || ' AND EXISTS (select 1 from msd_time tim where src.';
l_stmt := l_stmt || ' and (tim.calendar_type, tim.calendar_code) in (select mdc.calendar_type, mdc.calendar_code from msd_dp_calendars mdc where mdc.demand_plan_id = ' || p_demand_plan_id || ')';
l_new_stmt := l_new_stmt || ' AND EXISTS (select 1 from msd_time tim where src.';
l_new_stmt := l_new_stmt || ' and (tim.calendar_type, tim.calendar_code) in (select mdc.calendar_type, mdc.calendar_code from msd_dp_calendars mdc where mdc.demand_plan_id = ' || p_demand_plan_id || ')';
update msd_dp_parameters
set ascp_stream_flag='N'
where demand_plan_id = p_demand_plan_id
and parameter_id = c_input_rec.parameter_id;
Procedure update_plan(p_demand_plan_id in number, p_ret_code in number) is
Begin
--
if l_debug = 'Y' then
debug_out( 'Entering update_plan ' || to_char(sysdate, 'hh24:mi:ss'));
update msd_demand_plans
set valid_flag = '1'
where demand_plan_id = p_demand_plan_id;
update msd_demand_plans
set valid_flag = '0'
where demand_plan_id = p_demand_plan_id;
debug_out( 'Exiting update_plan ' || to_char(sysdate, 'hh24:mi:ss'));
select mev.event_name, mev.event_id, mep.product_lvl_id, mep.product_lvl_name, mep.product_lvl_val
from msd_event_products_v mep, msd_events_v mev
where
mep.event_id in ((select mdse.event_id
from msd_dp_scenario_events mdse, msd_dp_scenarios b
where mdse.demand_plan_id = p_demand_plan_id
and mdse.scenario_id = b.scenario_id
and b.enable_flag = 'Y')
union
(select mde.event_id
from msd_dp_events_v mde
where mde.demand_plan_id = p_demand_plan_id))
and
0 = (select count(1) from msd_npi_related_products_v mnrp where mnrp.seq_id = mep.seq_id)
and
mep.event_id = mev.event_id
and mev.event_type_id = '3';
select 1
from msd_dp_calendars
where calendar_type = p_calendar_type
and demand_plan_id = p_demand_plan_id;
SELECT count(1)
FROM msd_dp_calendars
WHERE demand_plan_id = p_demand_plan_id
AND calendar_type = p_calendar_type;
select decode(p_lowest_lvl_id,
'1', ' WEEK_END_DATE ',
'2', ' MONTH_END_DATE ',
'3', ' MONTH_END_DATE ',
'4', ' QUARTER_END_DATE ',
'5', ' YEAR_END_DATE ',
'6', ' MONTH_END_DATE ',
'7', ' QUARTER_END_DATE ',
'8', ' YEAR_END_DATE ',
'9', ' DAY',
'10', ' WEEK_END_DATE ',
'11', ' MONTH_END_DATE ',
'12', ' QUARTER_END_DATE ',
'13', ' YEAR_END_DATE ',
' DAY ')
from dual;
select meaning
from fnd_lookup_values_vl
where lookup_type = p_lookup_type
and lookup_code = p_lookup_code;
select meaning
from fnd_lookup_values_vl
where lookup_type = p_lookup_type
and lookup_code = to_char(p_lookup_code);
l_stmt := 'SELECT ' || p_date_clmn;
l_stmt := l_stmt || '(SELECT calendar_code FROM msd_dp_calendars';
select name
from msd_cs_definitions
where cs_definition_id = p_cs_definition_id;
lv_sql_stmt := 'SELECT decode('||p_View_level_col || ',0,null,' || p_View_level_col || ')'
||' FROM '||p_view_name
||' WHERE '||p_view_date_col||' >= '||lv_start_date
||' AND '||p_view_date_col||' <= '||lv_end_date
||' AND rownum < 2';
lv_sql_stmt := 'SELECT '||p_View_level_col
||' FROM '||p_view_name
||' WHERE '||p_view_date_col||' >= '||lv_start_date
||' AND '||p_view_date_col||' <= '||lv_end_date
||' AND rownum < 2'
||' AND action_code <> ''D''';
SELECT demand_plan_name
FROM msd_demand_plans
WHERE demand_plan_id = p_demand_plan_id
FOR UPDATE of demand_plan_name NOWAIT;
SELECT planning_view_column_name
FROM msd_cs_defn_column_dtls_v
WHERE cs_definition_id = p_id
AND identifier_type = 'CSIDEN';
select instance_code
from msc_apps_instances
where validation_org_id is null;
select to_char(instance_id), to_char(validation_org_id) sr_level_pk
from msc_apps_instances
minus
select instance, sr_level_pk
from msd_level_values_ds
where demand_plan_id = p_demand_plan_id
and level_id = 7;
select stripe_level_id,build_stripe_stream_name
from msd_demand_plans
where demand_plan_id = p_demand_plan_id;
select mdio.sr_organization_id, mlv.level_value
from msd_dp_iso_organizations mdio,
-- msd_level_values_ds geo,
msd_level_values_ds org,
msd_level_values mlv
where mdio.demand_plan_id = p_demand_plan_id
and mdio.demand_plan_id = org.demand_plan_id
and mdio.sr_instance_id = org.instance
--and geo.level_id = 15
--and geo.system_attribute1 = 'I'
and org.sr_level_pk = mdio.sr_organization_id
and org.level_id = 7
--and org.instance = geo.instance
--and org.demand_plan_id = geo.demand_plan_id
--and org.sr_level_pk = geo.sr_level_pk
and mlv.instance = org.instance
and mlv.sr_level_pk = org.sr_level_pk
and mlv.level_id = org.level_id;
SELECT
price_list_name
FROM
msd_dp_price_lists
WHERE
demand_plan_id = p_demand_plan_id;
SELECT
scenario_id,
scenario_name,
horizon_start_date,
horizon_end_date,
price_list_name
FROM
msd_dp_scenarios
WHERE
demand_plan_id = p_demand_plan_id
AND price_list_name IS NOT NULL;
SELECT
mdp.parameter_id,
mdp.parameter_type_id,
mdp.parameter_type,
mdp.parameter_name,
mdp.multiple_stream_flag,
mdp.forecast_date_used,
mdp.date_planning_view_clmn,
mdp.start_date,
mdp.end_date,
mdp.price_list_name
FROM
msd_dp_parameters_cs_v mdp
WHERE mdp.demand_plan_id = p_demand_plan_id
AND mdp.parameter_type_id <> '7'
AND nvl(mdp.stream_type,'ABCD') not in ('ARCHIVED','ARCHIVED_TIM','CALCULATED','PLACEHOLDER')
AND mdp.price_list_name IS NOT NULL;
SELECT 1
INTO x_price_list_data_found
FROM dual
WHERE EXISTS (SELECT 1
FROM msd_price_list_ds_v
WHERE price_list_name = c_price_list_rec.price_list_name
AND ( ( nvl(start_date, X_MIN_DATE) <= l_dp_min_date
AND nvl(end_date, X_MAX_DATE) >= l_dp_min_date)
OR ( nvl(start_date, X_MIN_DATE) >= l_dp_min_date
AND nvl(end_date, X_MAX_DATE) <= l_dp_max_date)
OR ( nvl(start_date, X_MIN_DATE) <= l_dp_max_date
AND nvl(end_date, X_MAX_DATE) >= l_dp_max_date))
AND rownum < 2);
SELECT 1
INTO x_price_list_data_found
FROM dual
WHERE EXISTS (SELECT 1
FROM msd_price_list_ds_v
WHERE price_list_name = c_scn_with_pls.price_list_name
AND ( ( nvl(start_date, X_MIN_DATE) <= c_scn_with_pls.horizon_start_date
AND nvl(end_date, X_MAX_DATE) >= c_scn_with_pls.horizon_start_date)
OR ( nvl(start_date, X_MIN_DATE) >= c_scn_with_pls.horizon_start_date
AND nvl(end_date, X_MAX_DATE) <= c_scn_with_pls.horizon_end_date)
OR ( nvl(start_date, X_MIN_DATE) <= c_scn_with_pls.horizon_end_date
AND nvl(end_date, X_MAX_DATE) >= c_scn_with_pls.horizon_end_date))
AND rownum < 2);
SELECT 1
INTO x_price_list_data_found
FROM dual
WHERE EXISTS (SELECT 1
FROM msd_price_list_ds_v
WHERE price_list_name = c_param_with_pls.price_list_name
AND ( ( nvl(start_date, X_MIN_DATE) <= c_param_with_pls.start_date
AND nvl(end_date, X_MAX_DATE) >= c_param_with_pls.start_date)
OR ( nvl(start_date, X_MIN_DATE) >= c_param_with_pls.start_date
AND nvl(end_date, X_MAX_DATE) <= c_param_with_pls.end_date)
OR ( nvl(start_date, X_MIN_DATE) <= c_param_with_pls.end_date
AND nvl(end_date, X_MAX_DATE) >= c_param_with_pls.end_date))
AND rownum < 2);
SELECT 1
INTO x_price_list_data_found
FROM dual
WHERE EXISTS (SELECT 1
FROM msd_price_list_v
WHERE price_list_name = c_price_list_rec.price_list_name
AND ( ( nvl(start_date, X_MIN_DATE) <= l_dp_min_date
AND nvl(end_date, X_MAX_DATE) >= l_dp_min_date)
OR ( nvl(start_date, X_MIN_DATE) >= l_dp_min_date
AND nvl(end_date, X_MAX_DATE) <= l_dp_max_date)
OR ( nvl(start_date, X_MIN_DATE) <= l_dp_max_date
AND nvl(end_date, X_MAX_DATE) >= l_dp_max_date))
AND rownum < 2);
SELECT 1
INTO x_price_list_data_found
FROM dual
WHERE EXISTS (SELECT 1
FROM msd_price_list_v
WHERE price_list_name = c_scn_with_pls.price_list_name
AND ( ( nvl(start_date, X_MIN_DATE) <= c_scn_with_pls.horizon_start_date
AND nvl(end_date, X_MAX_DATE) >= c_scn_with_pls.horizon_start_date)
OR ( nvl(start_date, X_MIN_DATE) >= c_scn_with_pls.horizon_start_date
AND nvl(end_date, X_MAX_DATE) <= c_scn_with_pls.horizon_end_date)
OR ( nvl(start_date, X_MIN_DATE) <= c_scn_with_pls.horizon_end_date
AND nvl(end_date, X_MAX_DATE) >= c_scn_with_pls.horizon_end_date))
AND rownum < 2);
SELECT 1
INTO x_price_list_data_found
FROM dual
WHERE EXISTS (SELECT 1
FROM msd_price_list_v
WHERE price_list_name = c_param_with_pls.price_list_name
AND ( ( nvl(start_date, X_MIN_DATE) <= c_param_with_pls.start_date
AND nvl(end_date, X_MAX_DATE) >= c_param_with_pls.start_date)
OR ( nvl(start_date, X_MIN_DATE) >= c_param_with_pls.start_date
AND nvl(end_date, X_MAX_DATE) <= c_param_with_pls.end_date)
OR ( nvl(start_date, X_MIN_DATE) <= c_param_with_pls.end_date
AND nvl(end_date, X_MAX_DATE) >= c_param_with_pls.end_date))
AND rownum < 2);