[Home] [Help]
124: CURSOR get_dim (p_demand_plan_id NUMBER,
125: p_dp_dimension_code VARCHAR2,
126: p_dimension_code VARCHAR2) IS
127: SELECT dimension_code
128: FROM msd_dp_dimensions_v
129: WHERE demand_plan_id = p_demand_plan_id
130: AND dp_dimension_code = p_dp_dimension_code
131: AND dimension_code = p_dimension_code;
132:
134: -- check there are at least three user dimensions and not more than four.
135: --
136: CURSOR chk_user_dim (p_demand_plan_id number) IS
137: SELECT COUNT(DISTINCT dp_dimension_code)
138: FROM msd_dp_dimensions_v
139: WHERE demand_plan_id = p_demand_plan_id;
140:
141: --
142: -- check there is only one collapsed dimension.
144: CURSOR chk_coll_dim (p_demand_plan_id number) IS
145: SELECT COUNT(1)
146: FROM (
147: SELECT dp_dimension_code, count(1)
148: FROM msd_dp_dimensions_v
149: WHERE demand_plan_id = p_demand_plan_id
150: GROUP BY dp_dimension_code
151: HAVING COUNT(1) > 1);
152:
154: -- check all dimensions are used in the demand plan.
155: --
156: CURSOR chk_used_dim (p_demand_plan_id number) IS
157: SELECT DISTINCT dp_dimension_code
158: FROM msd_dp_dimensions_v mddv
159: WHERE demand_plan_id = p_demand_plan_id
160: and not exists (
161: select 1
162: from msd_cs_definitions mcd, msd_cs_defn_dim_dtls mcdd, msd_dp_parameters mdp
170: -- find user dimensions that don't have hierarchies or have invald hierarchies
171: --
172: CURSOR get_usr_dim_with_no_hier (p_demand_plan_id in number) IS
173: SELECT DISTINCT dp_dimension dp_dimension
174: FROM msd_dp_dimensions_v pd
175: WHERE demand_plan_id = p_demand_plan_id
176: AND dp_dimension_code <> 'TIM'
177: AND NOT EXISTS
178: (SELECT hierarchy_id
183: -- find dimensions that don't have hierarchies or have invalid hierarchies
184: --
185: CURSOR get_dim_with_no_hier (p_demand_plan_id IN NUMBER) IS
186: SELECT DISTINCT dimension dimension_code
187: FROM msd_dp_dimensions_v pd
188: WHERE demand_plan_id = p_demand_plan_id
189: AND dp_dimension_code <> 'TIM'
190: AND NOT EXISTS
191: (select hierarchy_id
196:
197: -- Find dimension that exist as dp_dimension code but not as dimension.
198: CURSOR get_hier_collaps (p_demand_plan_id NUMBER) IS
199: SELECT DISTINCT dp_dimension
200: FROM msd_dp_dimensions_v dd1
201: WHERE demand_plan_id = p_demand_plan_id
202: AND NOT EXISTS
203: (select 1
204: from msd_dp_dimensions_v dd2
200: FROM msd_dp_dimensions_v dd1
201: WHERE demand_plan_id = p_demand_plan_id
202: AND NOT EXISTS
203: (select 1
204: from msd_dp_dimensions_v dd2
205: where dd2.demand_plan_id = p_demand_plan_id
206: and dd1.dp_dimension_code = dd2.dimension_code
207: and dd1.dp_dimension_code = dd2.dp_dimension_code
208: );
433: mdp.revision
434: FROM msd_cs_defn_dim_dtls mcdd,
435: msd_dp_parameters mdp,
436: msd_cs_definitions mcd,
437: msd_dp_dimensions_v mdd
438: WHERE mdp.demand_plan_id = p_demand_plan_id
439: AND mdd.demand_plan_id = p_demand_plan_id
440: AND mdd.dimension_code = mcdd.dimension_code
441: AND mcdd.cs_definition_id = mcd.cs_definition_id
1298: where cs_definition_id = p_cs_definition_id;
1299:
1300: /* Bug #5464757 */
1301: Cursor COL_DIM_IN_PARA(P_PARA_TYPE in varchar2) is
1302: SELECT MDD.DIMENSION_CODE dim_code FROM MSD.msd_dP_dimensions MDD
1303: where MDD.demand_plan_id =p_demand_plan_id
1304: and MDD.dimension_code<>MDD.dp_dimension_code
1305: AND MDD.DIMENSION_CODE NOT IN
1306: (SELECT MCD.DIMENSION_CODE
1318: FROM MSD_CS_DEFINITIONS MCDS
1319: WHERE MCDS.NAME = P_PARA_TYPE)
1320: and mcd.collect_flag='Y')
1321: Union All
1322: SELECT MDD.DP_DIMENSION_CODE dim_code FROM MSD.msd_dP_dimensions MDD
1323: where MDD.demand_plan_id =p_demand_plan_id
1324: and MDD.dimension_code<>MDD.dp_dimension_code
1325: AND MDD.DP_DIMENSION_CODE NOT IN
1326: (SELECT MCD.DIMENSION_CODE