APPS.GCS_CREATE_LEVELS_PKG dependencies on GCS_EPB_LEVELS_GT

Line 112: DELETE FROM GCS_EPB_LEVELS_GT;

108: END IF;
109:
110:
111: -- Shouldn't need this but I noticed some strange behavior on fin115p1 so adding this delete
112: DELETE FROM GCS_EPB_LEVELS_GT;
113:
114: -- fetch the parents and populate the temp table for each parent
115: OPEN get_parent;
116: LOOP

Line 122: INSERT INTO GCS_EPB_LEVELS_GT

118: FETCH get_parent INTO parent;
119: EXIT WHEN get_parent%NOTFOUND OR get_parent%NOTFOUND IS NULL;
120:
121: -- insert into global temp table
122: INSERT INTO GCS_EPB_LEVELS_GT
123: ( dim_display_code, dim_value_set_display_code, dim_group_display_code, dim_group_level)
124: SELECT DISTINCT hier.parent_display_code, hier.parent_vs_display_code, p_dimension || level*100, level*100
125: FROM GCS_HIER_MEMBERS_T hier
126: WHERE hier.sequence_num = p_sequence_num

Line 136: FROM GCS_EPB_LEVELS_GT;

132:
133: -- Get the max depth for the child nodes
134: SELECT max(dim_group_level) + 100
135: INTO max_depth
136: FROM GCS_EPB_LEVELS_GT;
137:
138: -- Insert the level info for leaf nodes in the temp table
139: INSERT INTO GCS_EPB_LEVELS_GT
140: ( dim_display_code, dim_value_set_display_code, dim_group_display_code, dim_group_level )

Line 139: INSERT INTO GCS_EPB_LEVELS_GT

135: INTO max_depth
136: FROM GCS_EPB_LEVELS_GT;
137:
138: -- Insert the level info for leaf nodes in the temp table
139: INSERT INTO GCS_EPB_LEVELS_GT
140: ( dim_display_code, dim_value_set_display_code, dim_group_display_code, dim_group_level )
141: SELECT DISTINCT hier.child_display_code, hier.child_vs_display_code, p_dimension || max_depth, max_depth
142: FROM GCS_HIER_MEMBERS_T hier
143: WHERE hier.sequence_num = p_sequence_num

Line 158: ( SELECT dimension_group_display_code FROM GCS_EPB_LEVELS_GT );

154: -- Delete orphan records from the _T table
155: DELETE FROM fem_dimension_grps_b_t
156: WHERE dimension_varchar_label = p_dimension
157: AND dimension_group_display_code IN
158: ( SELECT dimension_group_display_code FROM GCS_EPB_LEVELS_GT );
159:
160: DELETE FROM fem_dimension_grps_tl_t
161: WHERE dimension_varchar_label = p_dimension
162: AND dimension_group_display_code IN

Line 163: ( SELECT dimension_group_display_code FROM GCS_EPB_LEVELS_GT );

159:
160: DELETE FROM fem_dimension_grps_tl_t
161: WHERE dimension_varchar_label = p_dimension
162: AND dimension_group_display_code IN
163: ( SELECT dimension_group_display_code FROM GCS_EPB_LEVELS_GT );
164:
165: -- Populate the dimension groups interface tables
166: INSERT INTO fem_dimension_grps_b_t
167: (dimension_group_display_code,

Line 176: FROM GCS_EPB_LEVELS_GT;

172: dim_group_display_code,
173: p_dimension,
174: dim_group_level,
175: 'LOAD'
176: FROM GCS_EPB_LEVELS_GT;
177:
178: INSERT INTO fem_dimension_grps_tl_t
179: (dimension_group_display_code,
180: language,

Line 192: FROM GCS_EPB_LEVELS_GT;

188: dim_group_display_code,
189: dim_group_display_code,
190: 'LOAD',
191: p_dimension
192: FROM GCS_EPB_LEVELS_GT;
193:
194: FEM_DIM_MEMBER_LOADER_PKG.Main(
195: errbuf => errbuf,
196: retcode => retcode,

Line 208: FROM ' || l_btable || ' dimb, fem_dimension_grps_b dimgrpb, gcs_epb_levels_gt tempsp, fem_value_sets_b val

204:
205:
206: -- Check whether levels conflict with existing assigned levels
207: stmt := 'SELECT count(*)
208: FROM ' || l_btable || ' dimb, fem_dimension_grps_b dimgrpb, gcs_epb_levels_gt tempsp, fem_value_sets_b val
209: WHERE nvl(dimb.dimension_group_id, dimgrpb.dimension_group_id) <> dimgrpb.dimension_group_id
210: AND tempsp.dim_group_display_code = dimgrpb.dimension_group_display_code
211: AND tempsp.dim_display_code = dimb.' || l_displaycol || ' AND dimb.value_set_id = val.value_set_id AND val.value_set_display_code = tempsp.dim_value_set_display_code ';
212:

Line 239: ( SELECT dimension_group_display_code FROM GCS_EPB_LEVELS_GT );

235: -- get rid of orphan records
236: DELETE FROM fem_hier_dim_grps_t
237: WHERE hierarchy_object_name = p_hierarchy_name
238: AND dimension_group_display_code IN
239: ( SELECT dimension_group_display_code FROM GCS_EPB_LEVELS_GT );
240:
241: INSERT INTO fem_hier_dim_grps_t (
242: hierarchy_object_name,
243: language,

Line 250: FROM GCS_EPB_LEVELS_GT levelgt;

246: SELECT DISTINCT p_hierarchy_name,
247: USERENV('LANG'),
248: 'LOAD',
249: levelgt.dim_group_display_code
250: FROM GCS_EPB_LEVELS_GT levelgt;
251: END IF;
252:
253: IF retcode is null or retcode <> '2' THEN
254:

Line 257: FROM GCS_EPB_LEVELS_GT hier, FEM_VALUE_SETS_B val, FEM_DIMENSION_GRPS_B grp

253: IF retcode is null or retcode <> '2' THEN
254:
255: stmt := 'UPDATE ' || l_btable || ' dimb SET dimb.dimension_group_id =
256: (SELECT grp.dimension_group_id
257: FROM GCS_EPB_LEVELS_GT hier, FEM_VALUE_SETS_B val, FEM_DIMENSION_GRPS_B grp
258: WHERE grp.dimension_group_display_code = hier.dim_group_display_code
259: AND hier.dim_value_set_display_code = val.value_set_display_code
260: AND val.value_set_id = dimb.value_set_id
261: AND hier.dim_display_code = dimb.' || l_displaycol || ')

Line 263: ( SELECT dim_display_code FROM GCS_EPB_LEVELS_GT)

259: AND hier.dim_value_set_display_code = val.value_set_display_code
260: AND val.value_set_id = dimb.value_set_id
261: AND hier.dim_display_code = dimb.' || l_displaycol || ')
262: WHERE dimb.' || l_displaycol || ' IN
263: ( SELECT dim_display_code FROM GCS_EPB_LEVELS_GT)
264: AND dimb.value_set_id IN
265: ( SELECT val2.value_set_id
266: FROM GCS_EPB_LEVELS_GT hier2, FEM_VALUE_SETS_B val2
267: WHERE hier2.dim_value_set_display_code = val2.value_set_display_code) ';

Line 266: FROM GCS_EPB_LEVELS_GT hier2, FEM_VALUE_SETS_B val2

262: WHERE dimb.' || l_displaycol || ' IN
263: ( SELECT dim_display_code FROM GCS_EPB_LEVELS_GT)
264: AND dimb.value_set_id IN
265: ( SELECT val2.value_set_id
266: FROM GCS_EPB_LEVELS_GT hier2, FEM_VALUE_SETS_B val2
267: WHERE hier2.dim_value_set_display_code = val2.value_set_display_code) ';
268:
269:
270: EXECUTE IMMEDIATE stmt;