[Home] [Help]
PACKAGE BODY: APPS.FEM_CHECK_BR_DIS_MBRS_PKG
Source
1 PACKAGE BODY FEM_CHECK_BR_DIS_MBRS_PKG AS
2 /* $Header: fem_chk_dis_mbrs.plb 120.3.12010000.2 2008/10/10 22:27:08 huli ship $ */
3
4 -------------------------------------------------------------------------------
5 -- PRIVATE VARIABLES
6 -------------------------------------------------------------------------------
7
8 pv_report_row_counter NUMBER;
9
10 -------------------------------------------------------------------------------
11 -- PRIVATE SPECIFICATIONS
12 -------------------------------------------------------------------------------
13
14 PROCEDURE Find_Root_Rules (
15 p_rule_type IN VARCHAR2,
16 p_ledger_id IN NUMBER,
17 p_effective_date IN DATE,
18 p_folder_id IN NUMBER,
19 p_object_id IN NUMBER,
20 p_global_vs_id IN NUMBER);
21
22 PROCEDURE Validate_Root_Rule (
23 p_obj_def_id IN NUMBER,
24 p_parent_report_row_id IN NUMBER,
25 p_effective_date IN DATE,
26 p_global_vs_id IN NUMBER,
27 p_param_dim_id IN NUMBER,
28 p_stack_level IN NUMBER DEFAULT 0,
29 x_all_rules_are_valid OUT NOCOPY VARCHAR2);
30
31 PROCEDURE Validate_Rule_Wrapper (
32 p_obj_def_id IN NUMBER,
33 p_global_vs_id IN NUMBER,
34 p_param_dim_id IN NUMBER,
35 x_this_is_valid OUT NOCOPY VARCHAR2);
36
37 PROCEDURE Validate_Mapping (
38 p_obj_def_id IN NUMBER,
39 p_global_vs_id IN NUMBER,
40 p_param_dim_id IN NUMBER,
41 x_this_is_valid OUT NOCOPY VARCHAR2);
42
43 PROCEDURE Validate_Dim_Component (
44 p_obj_def_id IN NUMBER,
45 p_global_vs_id IN NUMBER,
46 p_param_dim_id IN NUMBER,
47 x_this_is_valid OUT NOCOPY VARCHAR2);
48
49 PROCEDURE Validate_Data_Component (
50 p_obj_def_id IN NUMBER,
51 p_global_vs_id IN NUMBER,
52 p_param_dim_id IN NUMBER,
53 x_this_is_valid OUT NOCOPY VARCHAR2);
54
55 PROCEDURE Validate_Statistic (
56 p_obj_def_id IN NUMBER,
57 p_global_vs_id IN NUMBER,
58 p_param_dim_id IN NUMBER,
59 x_this_is_valid OUT NOCOPY VARCHAR2);
60
61 PROCEDURE Validate_Hierarchy (
62 p_obj_def_id IN NUMBER,
63 p_global_vs_id IN NUMBER,
64 p_param_dim_id IN NUMBER,
65 x_this_is_valid OUT NOCOPY VARCHAR2);
66
67 PROCEDURE Validate_Factor_Table (
68 p_obj_def_id IN NUMBER,
69 p_global_vs_id IN NUMBER,
70 p_param_dim_id IN NUMBER,
71 x_this_is_valid OUT NOCOPY VARCHAR2);
72
73 PROCEDURE Get_Put_Messages;
74
75 PROCEDURE Populate_Dim_Info;
76
77 -------------------------------------------------------------------------------
78 -- PRIVATE BODIES
79 -------------------------------------------------------------------------------
80
81 -------------------------------------------------------------------------------
82 --
83 -- PROCEDURE
84 -- Find_Root_Rules
85 --
86 -- DESCRIPTION
87 -- The procedure needs to find the set of root rules that needs
88 -- to be checked for disabled dimension members. If the Rule Name
89 -- parameter is specified and the rule given is a rule set,
90 -- this procedure will need to flatten the rule set to determine the
91 -- list of rules to validate. If a rule set is not specified,
92 -- then the list of root rules will be based on the other runtime
93 -- parameters such as Ledger and Folder.
94 --
95 -------------------------------------------------------------------------------
96 PROCEDURE Find_Root_Rules (
97 p_rule_type IN VARCHAR2,
98 p_ledger_id IN NUMBER,
99 p_effective_date IN DATE,
100 p_folder_id IN NUMBER,
101 p_object_id IN NUMBER,
102 p_global_vs_id IN NUMBER)
103 -------------------------------------------------------------------------------
104 IS
105 --
106 C_MODULE CONSTANT FND_LOG_MESSAGES.module%TYPE :=
107 'fem.plsql.fem_check_dis_mbr_pkg.find_root_rules';
108 --
109 v_return_status VARCHAR2(1);
110 v_msg_count NUMBER;
111 v_msg_data VARCHAR2(4000);
112 v_rule_type FEM_OBJECT_TYPES_B.object_type_code%TYPE;
113 v_folder_id NUMBER;
114 v_global_vs_id NUMBER;
115 v_obj_name FEM_OBJECT_CATALOG_VL.object_name%TYPE;
116 v_is_rule_set VARCHAR2(1);
117 v_ds_io_def_id NUMBER;
118 v_obj_def_id NUMBER;
119 v_err_code NUMBER;
120 --
121 -- Get all root rules as limited by the runtime parameters.
122 CURSOR c_all_root_rules IS
123 SELECT object_id
124 FROM fem_object_catalog_b
125 WHERE object_type_code = p_rule_type
126 AND local_vs_combo_id = p_global_vs_id
127 AND folder_id = Nvl(p_folder_id, folder_id)
128 AND object_id = Nvl(p_object_id, object_id);
129 --
130 BEGIN
131 --
132 IF FND_LOG.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
133 FEM_ENGINES_PKG.TECH_MESSAGE(
134 p_severity => FND_LOG.level_procedure,
135 p_module => C_MODULE,
136 p_msg_text => 'Begin Procedure');
137 END IF;
138
139 -- Initinalize var:
140 v_is_rule_set := 'N';
141
142 -- If p_object_id is given,
143 -- Make sure it is either a rule or rule set of the type p_rule_type.
144 -- Make sure its Global Value Set Combination corresponds to the
145 -- Ledger parameter.
146 -- Make sure its Folder corresponds to the Folder parameter (if specified)
147 IF (p_object_id IS NOT NULL) THEN
148 SELECT Decode(object_type_code, 'RULE_SET',
149 (SELECT rs.rule_set_object_type_code
150 FROM fem_object_definition_b od, fem_rule_sets rs
151 WHERE rs.rule_set_obj_def_id = od.object_definition_id
152 AND od.object_id = p_object_id),
153 object_type_code) rule_type,
154 local_vs_combo_id, folder_id, object_name,
155 Decode(object_type_code, 'RULE_SET', 'Y', 'N')
156 INTO v_rule_type, v_global_vs_id, v_folder_id, v_obj_name, v_is_rule_set
157 FROM fem_object_catalog_vl
158 WHERE object_id = p_object_id;
159
160 IF (v_global_vs_id <> p_global_vs_id) THEN
161 IF FND_LOG.level_unexpected >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
162 FEM_ENGINES_PKG.TECH_MESSAGE(
163 p_severity => FND_LOG.level_unexpected,
164 p_module => C_MODULE,
165 p_msg_text => 'UNEXP ERROR: For rule name ('
166 || v_obj_name||'), its GVSC ('
167 || v_global_vs_id||') does not match the'
168 ||' GVSC for ledger.');
169 END IF;
170
171 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
172 END IF;
173
174 IF (v_folder_id <> Nvl(p_folder_id, v_folder_id)) THEN
175 IF FND_LOG.level_unexpected >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
176 FEM_ENGINES_PKG.TECH_MESSAGE(
177 p_severity => FND_LOG.level_unexpected,
178 p_module => C_MODULE,
179 p_msg_text => 'UNEXP ERROR: For rule name ('
180 || v_obj_name||'), its folder id ('
181 || v_folder_id||') does not match the'
182 ||' parameter folder id.');
183 END IF;
184
185 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
186 END IF;
187
188 IF (v_rule_type <> p_rule_type) THEN
189 IF FND_LOG.level_unexpected >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
190 FEM_ENGINES_PKG.TECH_MESSAGE(
191 p_severity => FND_LOG.level_unexpected,
192 p_module => C_MODULE,
193 p_msg_text => 'UNEXP ERROR: For rule name ('
194 || v_obj_name||'), its rule type ('
195 || v_rule_type||') does not match the'
196 ||' parameter rule type.');
197 END IF;
198
199 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
200 END IF;
201 END IF; -- IF (p_object_id IS NOT NULL)
202
203 -- Get a non-production dataset if one exists.
204 -- If not, get a production one. If none exist, raise unexp error.
205 BEGIN
206 SELECT io.dataset_io_obj_def_id
207 INTO v_ds_io_def_id
208 FROM fem_datasets_attr dsa, fem_dim_attributes_b dma,
209 fem_datasets_b d, fem_ds_input_output_defs io
210 WHERE io.output_dataset_code = d.dataset_code
211 AND dsa.DATASET_CODE = io.output_dataset_code
212 AND dma.attribute_id = dsa.attribute_id
213 AND dma.attribute_varchar_label = 'PRODUCTION_FLAG'
214 AND rownum = 1
215 ORDER BY dsa.dim_attribute_varchar_member;
216 EXCEPTION
217 WHEN no_data_found THEN
218 -- This message will be caught by the top calling routine and
219 -- displayed in the conc program UI.
220 FEM_ENGINES_PKG.Put_Message(
221 p_app_name => 'FEM',
222 p_msg_name => 'FEM_MISSING_DATASET_GROUP');
223
224 IF FND_LOG.level_unexpected >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
225 FEM_ENGINES_PKG.Tech_Message(
226 p_severity => FND_LOG.level_unexpected,
227 p_module => C_MODULE,
228 p_app_name => 'FEM',
229 p_msg_name => 'FEM_MISSING_DATASET_GROUP');
230 END IF;
231
232 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
233 END;
234
235 IF FND_LOG.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
236 FEM_ENGINES_PKG.TECH_MESSAGE(
237 p_severity => FND_LOG.level_statement,
238 p_module => C_MODULE,
239 p_msg_text => 'v_ds_io_def_id = '||v_ds_io_def_id);
240 FEM_ENGINES_PKG.TECH_MESSAGE(
241 p_severity => FND_LOG.level_statement,
242 p_module => C_MODULE,
243 p_msg_text => 'v_is_ruleset = '||v_is_rule_set);
244 END IF;
245
246 -- If rule type is ruleset, flatten the ruleset into FEM_RULESET_PROCESS_DATA
247 -- then copy it to FEM_BR_ROOT_RULES_GT before cleaning it back up.
248 IF (v_is_rule_set = 'Y') THEN
249 -- Flatten the ruleset
250 FEM_RULE_SET_MANAGER.FEM_Preprocess_RuleSet_PVT(
251 p_api_version => 1.0,
252 x_return_status => v_return_status,
253 x_msg_count => v_msg_count,
254 x_msg_data => v_msg_data,
255 p_Orig_RuleSet_Object_ID => p_object_id,
256 p_DS_IO_Def_ID => v_ds_io_def_id,
257 p_Rule_Effective_Date => FND_DATE.Date_To_Canonical(p_effective_date),
258 p_Output_Period_ID => NULL,
259 p_Ledger_ID => p_ledger_id,
260 p_Continue_Process_On_Err_Flg => 'Y',
261 p_Execution_Mode => 'E');
262
263 IF FND_LOG.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
264 FEM_ENGINES_PKG.TECH_MESSAGE(
265 p_severity => FND_LOG.level_statement,
266 p_module => C_MODULE,
267 p_msg_text => 'FEM_RULE_SET_MANAGER.FEM_Preprocess_RuleSet_PVT'
268 ||' returned with status: '||v_return_status);
269 END IF;
270
271 IF v_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
272 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
273 END IF;
274
275 -- Move valid flattened rule versions from the
276 -- FEM_RULESET_PROCESS_DATA table to the FEM_BR_ROOT_RULES_GT table.
277 INSERT INTO fem_br_root_rules_gt(object_definition_id)
278 SELECT child_obj_def_id
279 FROM fem_ruleset_process_data
280 WHERE rule_set_obj_id = p_object_id;
281
282 -- Clean up the FEM_RULESET_PROCESS_DATA table
283 FEM_RULE_SET_MANAGER.FEM_DeleteFlatRuleList_PVT(
284 p_api_version => 1.0,
285 x_return_status => v_return_status,
286 x_msg_count => v_msg_count,
287 x_msg_data => v_msg_data,
288 p_RuleSet_Object_ID => p_object_id);
289
290 IF v_return_status <> FND_API.G_RET_STS_SUCCESS THEN
291 IF FND_LOG.level_unexpected >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
292 FEM_ENGINES_PKG.TECH_MESSAGE(
293 p_severity => FND_LOG.level_unexpected,
294 p_module => C_MODULE,
295 p_msg_text => 'INTERNAL ERROR: Call to'
296 ||' FEM_RULE_SET_MANAGER.FEM_DeleteFlatRuleList_PVT'
297 ||' failed with return status: '||v_return_status);
298 END IF;
299
300 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
301 END IF;
302
303 ELSE -- ELSIF (v_is_ruleset = 'N')
304 -- If ruleset is not provided, find all rules limited by the
305 -- Rule Type, Ledger and Folder parameters.
306 FOR all_root_rules IN c_all_root_rules LOOP
307 IF FND_LOG.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
308 FEM_ENGINES_PKG.TECH_MESSAGE(
309 p_severity => FND_LOG.level_statement,
310 p_module => C_MODULE,
311 p_msg_text => 'all_root_rules.object_id = '||all_root_rules.object_id);
312 END IF;
313
314 -- Validate each rule
315 FEM_RULE_SET_MANAGER.Validate_Rule_Public(
316 p_api_version => 1.0,
317 x_return_status => v_return_status,
318 x_msg_count => v_msg_count,
319 x_msg_data => v_msg_data,
320 p_Rule_Object_ID => all_root_rules.object_id,
321 p_DS_IO_Def_ID => v_ds_io_def_id,
322 p_Rule_Effective_Date => FND_DATE.Date_To_Canonical(p_effective_date),
323 p_Reference_Period_ID => NULL,
324 p_Ledger_ID => p_ledger_id);
325
326 IF FND_LOG.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
327 FEM_ENGINES_PKG.TECH_MESSAGE(
328 p_severity => FND_LOG.level_statement,
332 END IF;
329 p_module => C_MODULE,
330 p_msg_text => 'FEM_RULE_SET_MANAGER.Validate_Rule_Public'
331 ||' returned with status: '||v_return_status);
333
334 -- If rule is valid, get its version info
335 IF v_return_status = FND_API.G_RET_STS_SUCCESS THEN
336 FEM_RULE_SET_MANAGER.Get_ValidDefinition_Pub(
337 p_Object_ID => all_root_rules.object_id,
338 p_Rule_Effective_Date => FND_DATE.Date_To_Canonical(p_effective_date),
339 x_Object_Definition_ID => v_obj_def_id,
340 x_Err_Code => v_err_code,
341 x_Err_Msg => v_msg_data);
342
343 IF (v_err_code = 0) THEN
344 INSERT INTO fem_br_root_rules_gt(object_definition_id)
345 VALUES(v_obj_def_id);
346 ELSE
347 -- If the rule passed validation, this routine should never error.
348 -- If it does, raise unexp error.
349 IF FND_LOG.level_unexpected >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
350 FEM_ENGINES_PKG.TECH_MESSAGE(
351 p_severity => FND_LOG.level_unexpected,
352 p_module => C_MODULE,
353 p_msg_text => 'UNEXPECTED ERROR: Call to'
354 ||' FEM_RULE_SET_MANAGER.Get_ValidDefinition_Pub'
355 ||' failed with x_err_msg: '||v_msg_data);
356 END IF;
357
358 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
359 END IF;
360
361 ELSIF v_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
362 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
363
364 END IF;
365
366 END LOOP; -- c_all_root_rules
367
368 END IF; -- IF (v_is_ruleset = 'Y')
369
370 IF FND_LOG.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
371 FEM_ENGINES_PKG.TECH_MESSAGE(
372 p_severity => FND_LOG.level_procedure,
373 p_module => C_MODULE,
374 p_msg_text => 'End Procedure');
375 END IF;
376 --
377 END Find_Root_Rules;
378 -------------------------------------------------------------------------------
379
380
381 -------------------------------------------------------------------------------
382 --
383 -- PROCEDURE
384 -- Validate_Root_Rule
385 --
386 -- DESCRIPTION
387 -- Given a rule version, this procedure will do a recursive traversal
388 -- through the rule dependency structure so that it and all of its
389 -- referenced rules are validated. In the process, it will populate
390 -- the reporting tables either directly or through calls to the
391 -- individual rule type validation routines.
392 --
393 -------------------------------------------------------------------------------
394 PROCEDURE Validate_Root_Rule (
395 p_obj_def_id IN NUMBER,
396 p_parent_report_row_id IN NUMBER,
397 p_effective_date IN DATE,
398 p_global_vs_id IN NUMBER,
399 p_param_dim_id IN NUMBER,
400 p_stack_level IN NUMBER DEFAULT 0,
401 x_all_rules_are_valid OUT NOCOPY VARCHAR2)
402 -------------------------------------------------------------------------------
403 IS
404 --
405 C_MODULE CONSTANT FND_LOG_MESSAGES.module%TYPE :=
406 'fem.plsql.fem_check_dis_mbr_pkg.validate_root_rule';
407 --
408 v_module FND_LOG_MESSAGES.module%TYPE :=
409 'fem.plsql.fem_check_dis_mbr_pkg.validate_root_rule.'||p_stack_level;
410 v_curr_report_id NUMBER;
411 v_ref_obj_def_id NUMBER;
412 v_err_code NUMBER;
413 v_msg_data VARCHAR2(4000);
414 v_ref_rule_is_valid VARCHAR2(1);
415 v_all_ref_rules_valid VARCHAR2(1);
416 v_this_rule_is_valid VARCHAR2(1);
417 --
418 -- Bug 6972946: Ignore any objects left in the Object Dependencies table
419 -- if they no longer exists as objects (to prevent unexpected errors).
420 CURSOR c_ref_rules(cv_obj_def_id NUMBER) IS
421 SELECT required_object_id
422 FROM fem_object_dependencies d
423 WHERE object_definition_id = cv_obj_def_id
424 AND EXISTS (
425 SELECT null
426 FROM fem_object_catalog_b c
427 WHERE c.object_id = d.required_object_id);
428 --
429 BEGIN
430 --
431 IF FND_LOG.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
432 FEM_ENGINES_PKG.TECH_MESSAGE(
433 p_severity => FND_LOG.level_procedure,
434 p_module => v_module,
435 p_msg_text => 'Begin Procedure: '||p_stack_level);
436 END IF;
437 --
438 IF FND_LOG.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
439 FEM_ENGINES_PKG.TECH_MESSAGE(
440 p_severity => FND_LOG.level_statement,
441 p_module => v_module,
442 p_msg_text => 'p_obj_def_id = '||p_obj_def_id
443 ||'; p_parent_report_row_id = '||p_parent_report_row_id);
444 END IF;
445 --
446
447 -- Initialize var:
448 v_all_ref_rules_valid := 'Y';
449
450 -- Get a unique report row ID for this current rule
451 v_curr_report_id := Get_Unique_Report_Row();
452
453 -- Loop through all rules referenced by this current rule
454 -- and recursively check those rules for disabled members.
455 FOR ref_rules IN c_ref_rules(p_obj_def_id) LOOP
459 p_module => v_module,
456 IF FND_LOG.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
457 FEM_ENGINES_PKG.TECH_MESSAGE(
458 p_severity => FND_LOG.level_statement,
460 p_msg_text => 'ref_rules.required_object_id = '
461 ||ref_rules.required_object_id);
462 END IF;
463
464 -- First get the obj def id of the referenced rule
465 FEM_RULE_SET_MANAGER.Get_ValidDefinition_Pub(
466 p_Object_ID => ref_rules.required_object_id,
467 p_Rule_Effective_Date => FND_DATE.Date_To_Canonical(p_effective_date),
468 x_Object_Definition_ID => v_ref_obj_def_id,
469 x_Err_Code => v_err_code,
470 x_Err_Msg => v_msg_data);
471
472 IF (v_err_code <> 0) THEN
473 -- If the rule passed validation, this routine should never error.
474 -- If it does, raise unexp error.
475 IF FND_LOG.level_unexpected >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
476 FEM_ENGINES_PKG.TECH_MESSAGE(
477 p_severity => FND_LOG.level_unexpected,
478 p_module => v_module,
479 p_msg_text => 'UNEXPECTED ERROR: Call to'
480 ||' FEM_RULE_SET_MANAGER.Get_ValidDefinition_Pub'
481 ||' failed with x_err_msg: '||v_msg_data);
482 END IF;
483
484 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
485 END IF;
486
487 -- Now recurse on the referenced rule
488 Validate_Root_Rule(p_obj_def_id => v_ref_obj_def_id,
489 p_parent_report_row_id => v_curr_report_id,
490 p_effective_date => p_effective_date,
491 p_global_vs_id => p_global_vs_id,
492 p_param_dim_id => p_param_dim_id,
493 p_stack_level => p_stack_level+1,
494 x_all_rules_are_valid => v_ref_rule_is_valid);
495
496 IF (v_ref_rule_is_valid = 'N') THEN
497 v_all_ref_rules_valid := 'N';
498 END IF;
499 END LOOP; -- FOR ref_rules IN c_ref_rules LOOP
500
501 IF FND_LOG.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
502 FEM_ENGINES_PKG.TECH_MESSAGE(
503 p_severity => FND_LOG.level_statement,
504 p_module => v_module,
505 p_msg_text => 'v_all_ref_rules_valid = '||v_all_ref_rules_valid);
506 END IF;
507
508 -- Call the validation wrapper routine that in turn calls the
509 -- validation procedure that corresopnds to the rule type.
510 Validate_Rule_Wrapper(
511 p_obj_def_id => p_obj_def_id,
512 p_global_vs_id => p_global_vs_id,
513 p_param_dim_id => p_param_dim_id,
514 x_this_is_valid => v_this_rule_is_valid);
515
516 IF FND_LOG.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
517 FEM_ENGINES_PKG.TECH_MESSAGE(
518 p_severity => FND_LOG.level_statement,
519 p_module => v_module,
520 p_msg_text => 'v_this_rule_is_valid = '||v_this_rule_is_valid);
521 END IF;
522
523 -- If the current rule is invalid or any of the referenced rules are invalid,
524 -- 1. Insert the current rule into FEM_BR_DIS_MBR_CONTEXTS
525 -- without the actual context information to simply serve as
526 -- the parent row for the rows that either contain the context
527 -- information or referenced rules that are invalid.
528 -- 2. If the current rule is invalid,
529 -- insert the context information into FEM_BR_DIS_MBR_CONTEXTS
530 -- from FEM_BR_DISABLED_MBRS_GT.
531 IF (v_this_rule_is_valid = 'N' OR v_all_ref_rules_valid = 'N') THEN
532
533 INSERT INTO fem_br_dis_mbr_contexts
534 (request_id, report_row_id, parent_report_row_id,
535 object_id, object_name, object_type_code, object_type_name,
536 folder_id, folder_name, object_definition_id, object_definition_name,
537 effective_start_date, effective_end_date,
538 creation_date, created_by,
539 last_updated_by, last_update_date, last_update_login)
540 SELECT
541 FND_GLOBAL.Conc_Request_ID, v_curr_report_id, p_parent_report_row_id,
542 c.object_id, c.object_name, c.object_type_code, t.object_type_name,
543 c.folder_id, f.folder_name, d.object_definition_id, d.display_name,
544 d.effective_start_date, d.effective_end_date,
545 sysdate, FND_GLOBAL.User_ID,
546 FND_GLOBAL.User_ID, sysdate, FND_GLOBAL.Login_ID
547 FROM fem_object_definition_vl d, fem_object_catalog_vl c,
548 fem_object_types_vl t, fem_folders_vl f
549 WHERE d.object_definition_id = p_obj_def_id
550 AND d.object_id = c.object_id
551 AND c.object_type_code = t.object_type_code
552 AND c.folder_id = f.folder_id;
553
554 IF (v_this_rule_is_valid = 'N') THEN
555 -- When inserting into FEM_BR_DIS_MBR_CONTEXTS, get the translated
556 -- context meaning and value set name. The dimension member names
557 -- will need to be filled in the Report_Invalid_Rules procedure
558 -- after this procedure returns so we can populate all member names for
559 -- a given dimension at a time. Otherwise, we would have to populate
560 -- the member names row by row here.
561
562 INSERT INTO fem_br_dis_mbr_contexts
563 (request_id, report_row_id, parent_report_row_id,
564 object_id, object_name, object_type_code, object_type_name,
568 dimension_member, value_set_id, value_set_name,
565 folder_id, folder_name, object_definition_id, object_definition_name,
566 effective_start_date, effective_end_date,
567 context, dimension_id, dimension_name,
569 creation_date, created_by,
570 last_updated_by, last_update_date, last_update_login)
571 SELECT FND_GLOBAL.Conc_Request_ID,
572 FEM_CHECK_BR_DIS_MBRS_PKG.Get_Unique_Report_Row(), v_curr_report_id,
573 c.object_id, c.object_name, c.object_type_code, t.object_type_name,
574 c.folder_id, f.folder_name, d.object_definition_id, d.display_name,
575 d.effective_start_date, d.effective_end_date,
576 l.meaning context, b.dimension_id, dim.dimension_name,
577 b.dimension_member, b.value_set_id, v.value_set_name,
578 sysdate, FND_GLOBAL.User_ID,
579 FND_GLOBAL.User_ID, sysdate, FND_GLOBAL.Login_ID
580 FROM fem_br_disabled_mbrs_gt b, FEM_LOOKUPS l, fem_value_sets_vl v,
581 fem_object_definition_vl d, fem_object_catalog_vl c,
582 fem_object_types_vl t, fem_folders_vl f, fem_dimensions_vl dim
583 WHERE b.object_definition_id = p_obj_def_id
584 AND b.object_definition_id = d.object_definition_id
585 AND d.object_id = c.object_id
586 AND c.object_type_code = t.object_type_code
587 AND c.folder_id = f.folder_id
588 AND b.context_code = l.lookup_code
589 AND l.lookup_type = 'FEM_DISABLED_MEMBER_CONTEXT'
590 AND b.dimension_id = dim.dimension_id
591 AND b.value_set_id = v.value_set_id(+);
592 END IF;
593
594 x_all_rules_are_valid := 'N';
595 ELSE
596 x_all_rules_are_valid := 'Y';
597 END IF; -- IF (v_this_rule_is_valid = 'N' OR v_all_ref_rules_valid = 'N')
598
599 IF FND_LOG.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
600 FEM_ENGINES_PKG.TECH_MESSAGE(
601 p_severity => FND_LOG.level_statement,
602 p_module => v_module,
603 p_msg_text => 'x_all_rules_are_valid = '||x_all_rules_are_valid);
604 END IF;
605 --
606 IF FND_LOG.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
607 FEM_ENGINES_PKG.TECH_MESSAGE(
608 p_severity => FND_LOG.level_procedure,
609 p_module => v_module,
610 p_msg_text => 'End Procedure: '||p_stack_level);
611 END IF;
612 --
613 END Validate_Root_Rule;
614
615 -------------------------------------------------------------------------------
616 --
617 -- PROCEDURE
618 -- Validate_Rule_Wrapper
619 --
620 -- DESCRIPTION
621 -- Wrapper routine that calls the individual validation routines specific
622 -- to each rule type. Before it validates a rule, it first checks to
623 -- see if it the rule has been validated before. If yes, then just
624 -- return the results from the previous run. After a call to a
625 -- validation routine, it inserts a row in FEM_BR_VALID_STATUS_GT
626 -- to record the validation result for the rule being checked.
627 --
628 -------------------------------------------------------------------------------
629 PROCEDURE Validate_Rule_Wrapper (
630 p_obj_def_id IN NUMBER,
631 p_global_vs_id IN NUMBER,
632 p_param_dim_id IN NUMBER,
633 x_this_is_valid OUT NOCOPY VARCHAR2)
634 -------------------------------------------------------------------------------
635 IS
636 --
637 C_MODULE CONSTANT FND_LOG_MESSAGES.module%TYPE :=
638 'fem.plsql.fem_check_disabled_mbrs_pkg.validate_rule_wrapper';
639 --
640 v_rule_type FEM_OBJECT_TYPES_B.object_type_code%TYPE;
641 v_this_rule_is_valid VARCHAR2(1);
642 --
643 BEGIN
644 --
645 IF FND_LOG.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
646 FEM_ENGINES_PKG.TECH_MESSAGE(
647 p_severity => FND_LOG.level_procedure,
648 p_module => C_MODULE,
649 p_msg_text => 'Begin Procedure');
650 END IF;
651 --
652 -- First check if this rule has been validated previously.
653 -- If yes, then just return the previous results.
654 BEGIN
655 SELECT valid_flag
656 INTO x_this_is_valid
657 FROM fem_br_valid_status_gt
658 WHERE object_definition_id = p_obj_def_id;
659 EXCEPTION
660 WHEN others THEN NULL;
661 END;
662
663 IF (x_this_is_valid IS NOT NULL) THEN
664 IF FND_LOG.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
665 -- Add this to make debug log more readable
666 SELECT oc.object_type_code
667 INTO v_rule_type
668 FROM fem_object_catalog_b oc, fem_object_definition_b od
669 WHERE oc.object_id = od.object_id
670 AND od.object_definition_id = p_obj_def_id;
671
672 FEM_ENGINES_PKG.TECH_MESSAGE(
673 p_severity => FND_LOG.level_statement,
674 p_module => C_MODULE,
675
676 p_msg_text => 'p_obj_def_id = '||p_obj_def_id
677 ||'; v_rule_type = '||v_rule_type);
678 END IF;
679
680 IF FND_LOG.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
681 FEM_ENGINES_PKG.TECH_MESSAGE(
682 p_severity => FND_LOG.level_procedure,
683 p_module => C_MODULE,
684 p_msg_text => 'End Procedure: RETURN: x_this_is_valid = '
685 ||x_this_is_valid);
686 END IF;
687
688 RETURN;
689 END IF;
690
694 FROM fem_object_catalog_b oc, fem_object_definition_b od
691 -- Get rule type of current rule
692 SELECT oc.object_type_code
693 INTO v_rule_type
695 WHERE oc.object_id = od.object_id
696 AND od.object_definition_id = p_obj_def_id;
697
698 IF FND_LOG.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
699 FEM_ENGINES_PKG.TECH_MESSAGE(
700 p_severity => FND_LOG.level_statement,
701 p_module => C_MODULE,
702 p_msg_text => 'v_rule_type = '||v_rule_type);
703 END IF;
704
705 -- Call the validation procedure that corresopnds to the rule type.
706 IF (v_rule_type = 'MAPPING_RULE') THEN
707 Validate_Mapping(
708 p_obj_def_id => p_obj_def_id,
709 p_global_vs_id => p_global_vs_id,
710 p_param_dim_id => p_param_dim_id,
711 x_this_is_valid => v_this_rule_is_valid);
712
713 ELSIF (v_rule_type = 'CONDITION_DIMENSION_COMPONENT') THEN
714 Validate_Dim_Component(
715 p_obj_def_id => p_obj_def_id,
716 p_global_vs_id => p_global_vs_id,
717 p_param_dim_id => p_param_dim_id,
718 x_this_is_valid => v_this_rule_is_valid);
719
720 ELSIF (v_rule_type = 'CONDITION_DATA_COMPONENT') THEN
721 Validate_Data_Component(
722 p_obj_def_id => p_obj_def_id,
723 p_global_vs_id => p_global_vs_id,
724 p_param_dim_id => p_param_dim_id,
725 x_this_is_valid => v_this_rule_is_valid);
726
727 ELSIF (v_rule_type = 'STAT_LOOKUP') THEN
728 Validate_Statistic(
729 p_obj_def_id => p_obj_def_id,
730 p_global_vs_id => p_global_vs_id,
731 p_param_dim_id => p_param_dim_id,
732 x_this_is_valid => v_this_rule_is_valid);
733
734 ELSIF (v_rule_type = 'HIERARCHY') THEN
735 Validate_Hierarchy(
736 p_obj_def_id => p_obj_def_id,
737 p_global_vs_id => p_global_vs_id,
738 p_param_dim_id => p_param_dim_id,
739 x_this_is_valid => v_this_rule_is_valid);
740
741 ELSIF (v_rule_type IN ('FACTOR_TABLE')) THEN
742 Validate_Factor_Table(
743 p_obj_def_id => p_obj_def_id,
744 p_global_vs_id => p_global_vs_id,
745 p_param_dim_id => p_param_dim_id,
746 x_this_is_valid => v_this_rule_is_valid);
747
748 ELSIF (v_rule_type IN ('CONDITION','CONDITION_MAPPING')) THEN
749 -- The condition rule itself does not reference any dimension members
750 -- and so there is no need to call its validation procedure.
751 v_this_rule_is_valid := 'Y';
752
753 ELSE
754 -- Unsupported rule type - raise unexpected error
755 IF FND_LOG.level_unexpected >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
756 FEM_ENGINES_PKG.TECH_MESSAGE(
757 p_severity => FND_LOG.level_unexpected,
758 p_module => C_MODULE,
759 p_msg_text => 'This rule type is not supported: '||v_rule_type);
760 END IF;
761
762 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
763 END IF; -- IF (v_rule_type = 'MAPPING_RULE') THEN
764
765 -- Insert validation status into FEM_BR_VALID_STATUS_GT
766 INSERT INTO fem_br_valid_status_gt (object_definition_id, valid_flag)
767 VALUES (p_obj_def_id, v_this_rule_is_valid);
768
769 x_this_is_valid := v_this_rule_is_valid;
770 --
771 IF FND_LOG.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
772 FEM_ENGINES_PKG.TECH_MESSAGE(
773 p_severity => FND_LOG.level_procedure,
774 p_module => C_MODULE,
775 p_msg_text => 'End Procedure: x_this_is_valid = '||x_this_is_valid);
776 END IF;
777 --
778 END Validate_Rule_Wrapper;
779
780
781
782 -------------------------------------------------------------------------------
783 --
784 -- PROCEDURE
785 -- Validate_Mapping
786 --
787 -- DESCRIPTION
788 -- Validates the Mapping Rule asociated with given object definition id.
789 -- Optionally a value set or dimension id can be provided, which further
790 -- restricts the scope of the search to asceratin if the rule references and
791 -- disabled members.
792 -- If the rule references any disabled members, x_this_is_valid will return
793 -- 'N' and the identifying information for each disabled member found will
794 -- be logged in the global temporary table fem_br_disabled_mbrs_gt. If it
795 -- does not reference any disabled members, x_this_is_valid will return 'Y'.
796 --
797 -------------------------------------------------------------------------------
798 PROCEDURE Validate_Mapping (
799 p_obj_def_id IN NUMBER,
800 p_global_vs_id IN NUMBER,
801 p_param_dim_id IN NUMBER,
802 x_this_is_valid OUT NOCOPY VARCHAR2)
803 -------------------------------------------------------------------------------
804 IS
805 --
806 C_MODULE CONSTANT FND_LOG_MESSAGES.module%TYPE :=
807 'fem.plsql.fem_check_disabled_mbrs_pkg.validate_mapping';
808
809 v_member_table FEM_TAB_COLUMNS_B.TABLE_NAME%TYPE;
810 v_member_column FEM_TAB_COLUMNS_B.COLUMN_NAME%TYPE;
811 v_value_set_id FEM_GLOBAL_VS_COMBO_DEFS.VALUE_SET_ID%TYPE;
812 v_dim_id FEM_TAB_COLUMNS_B.DIMENSION_ID%TYPE;
813 v_func_cd FEM_ALLOC_BR_DIMENSIONS.FUNCTION_CD%TYPE;
814 v_datatype FEM_XDIM_DIMENSIONS.MEMBER_DATA_TYPE_CODE%TYPE;
815 v_value_set_flag VARCHAR2(1);
816 v_val_stmt VARCHAR2(100);
817 v_insert_stmt VARCHAR2(1000);
821 --object definition id. This ensures that each is checked for
818 C_CONTEXT FEM_BR_DISABLED_MBRS_GT.context_code%TYPE;
819
820 --there can be multiple column/table/dimension combinations for each
822 --disabled members.
823 cursor c_credit_debit (p_obj_def_id IN NUMBER, function_code IN VARCHAR2,
824 p_param_dim_id IN NUMBER) IS
825 SELECT c.dimension_id, d.alloc_dim_col_name, d.post_to_balances_flag
826 FROM fem_alloc_br_formula f, fem_alloc_br_dimensions d,
827 fem_tab_columns_b c, fem_xdim_dimensions x
828 WHERE f.object_definition_id = p_obj_def_id
829 AND f.function_cd = function_code
830 AND f.enable_flg = 'Y'
831 AND f.table_name = c.table_name
832 AND f.object_definition_id = d.object_definition_id
833 AND f.function_cd = d.function_cd
834 AND d.post_to_balances_flag = 'N'
835 AND d.alloc_dim_usage_code = 'VALUE'
836 AND d.alloc_dim_col_name = c.column_name
837 AND c.dimension_id = nvl(p_param_dim_id, c.dimension_id)
838 AND c.dimension_id = x.dimension_id
839 AND x.hier_editor_managed_flag = 'Y'
840 UNION ALL
841 SELECT c.dimension_id, d.alloc_dim_col_name, d.post_to_balances_flag
842 FROM fem_alloc_br_formula f, fem_alloc_br_dimensions d,
843 fem_tab_columns_b c, fem_xdim_dimensions x
844 WHERE f.object_definition_id = p_obj_def_id
845 AND f.function_cd = function_code
846 AND f.enable_flg = 'Y'
847 AND f.post_to_ledger_flg = 'Y'
848 AND c.table_name = 'FEM_BALANCES'
849 AND f.object_definition_id = d.object_definition_id
850 AND f.function_cd = d.function_cd
851 AND d.post_to_balances_flag = 'Y'
852 AND d.alloc_dim_usage_code = 'VALUE'
853 AND d.alloc_dim_col_name = c.column_name
854 AND c.dimension_id = nvl(p_param_dim_id, c.dimension_id)
855 AND c.dimension_id = x.dimension_id
856 AND x.hier_editor_managed_flag = 'Y';
857
858 --need to get each of the contexts associated with the obj_def_id
859 cursor c_get_context (p_obj_def_id IN NUMBER) IS
860 SELECT f.function_cd, f.function_seq
861 FROM fem_alloc_br_formula f
862 WHERE ((f.function_cd = 'CREDIT' and f.enable_flg = 'Y')
863 OR (f.function_cd = 'DEBIT' and f.enable_flg = 'Y')
864 OR (f.function_cd = 'MACRO'))
865 AND f.object_definition_id = p_obj_def_id;
866
867 BEGIN
868
869 IF FND_LOG.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
870 FEM_ENGINES_PKG.TECH_MESSAGE(
871 p_severity => FND_LOG.level_procedure,
872 p_module => C_MODULE,
873 p_msg_text => 'Begin Procedure');
874
875 FEM_ENGINES_PKG.TECH_MESSAGE(
876 p_severity => FND_LOG.level_procedure,
877 p_module => C_MODULE,
878 p_msg_text => 'p_obj_def_id is ' || p_obj_def_id);
879
880 FEM_ENGINES_PKG.TECH_MESSAGE(
881 p_severity => FND_LOG.level_procedure,
882 p_module => C_MODULE,
883 p_msg_text => 'p_param_dim_id is ' || p_param_dim_id);
884
885 FEM_ENGINES_PKG.TECH_MESSAGE(
886 p_severity => FND_LOG.level_procedure,
887 p_module => C_MODULE,
888 p_msg_text => 'p_global_vs_id is ' || p_global_vs_id);
889
890 END IF;
891
892 --loop through all of the contexts associated with the obj_def id
893 FOR context_string IN c_get_context(p_obj_def_id) LOOP
894 v_func_cd := context_string.function_cd;
895
896
897 IF FND_LOG.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
898 FEM_ENGINES_PKG.TECH_MESSAGE(
899 p_severity => FND_LOG.level_statement,
900 p_module => C_MODULE,
901 p_msg_text => 'the context code is := '
902 || v_func_cd);
903 END IF;
904
905
906 --check each if it references a disabled member
907 IF (v_func_cd = 'MACRO') THEN
908 C_CONTEXT := 'MAP_ACCRUAL';
909 SELECT dimension_id
910 INTO v_dim_id
911 FROM fem_dimensions_b
912 WHERE dimension_varchar_label = 'ACCRUAL_BASIS';
913
914 IF (v_dim_id = p_param_dim_id OR p_param_dim_id is null) THEN
915 SELECT member_b_table_name, member_col, value_set_required_flag
916 INTO v_member_table, v_member_column, v_value_set_flag
917 FROM fem_xdim_dimensions
918 WHERE dimension_id = v_dim_id;
919
920 IF (v_value_set_flag = 'Y' AND p_global_vs_id is not null) THEN
921 SELECT value_set_id
922 INTO v_value_set_id
923 FROM fem_global_vs_combo_defs
924 WHERE GLOBAL_VS_COMBO_ID = p_global_vs_id
925 AND dimension_id = v_dim_id;
926 v_val_stmt := 'and value_set_id = ' || v_value_set_id;
927 ELSE
928 v_val_stmt := '';
929 v_value_set_id := NULL;
930 END IF;
931
932 v_insert_stmt :=
933 'INSERT INTO fem_br_disabled_mbrs_gt (object_definition_id,
934 dimension_id, dimension_member, value_set_id, context_code)
935 SELECT ' || p_obj_def_id || ',' || v_dim_id || ', value, '
936 || nvl(to_char(v_value_set_id), 'NULL') || ', ''' || C_CONTEXT ||
937 ''' FROM FEM_ALLOC_BR_FORMULA
938 WHERE object_definition_id = :obj_def
939 AND function_seq = :func_seq
940 AND value IN
941 (SELECT ' || v_member_column ||
942 ' FROM ' || v_member_table ||
943 ' WHERE enabled_flag = ''N'' ' || v_val_stmt || ')';
944
945 IF FND_LOG.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
946 FEM_ENGINES_PKG.TECH_MESSAGE(
950 END IF;
947 p_severity => FND_LOG.level_statement,
948 p_module => C_MODULE,
949 p_msg_text => 'dynamic SQL statement is := ' || v_insert_stmt);
951
952 EXECUTE IMMEDIATE v_insert_stmt USING p_obj_def_id,
953 context_string.function_seq;
954
955 --if a disabled member is found it will be inserted into the table
956 IF(SQL%ROWCOUNT > 0) THEN
957 x_this_is_valid := 'N';
958 END IF;
959 END IF;
960 ELSE
961 --else v_func_cd = credit or debit
962 IF (v_func_cd = 'CREDIT') THEN
963 C_CONTEXT := 'MAP_CREDIT';
964 ELSE
965 C_CONTEXT := 'MAP_DEBIT';
966 END IF;
967
968 FOR data_row IN c_credit_debit(p_obj_def_id, v_func_cd,
969 p_param_dim_id) LOOP
970 v_dim_id := data_row.dimension_id;
971
972 IF (v_dim_id = p_param_dim_id OR p_param_dim_id is null) THEN
973 SELECT member_b_table_name, member_col, value_set_required_flag,
974 member_data_type_code
975 INTO v_member_table, v_member_column, v_value_set_flag, v_datatype
976 FROM fem_xdim_dimensions
977 WHERE dimension_id = v_dim_id;
978
979 IF (v_value_set_flag = 'Y' AND p_global_vs_id is not null) THEN
980 SELECT value_set_id
981 INTO v_value_set_id
982 FROM fem_global_vs_combo_defs
983 WHERE GLOBAL_VS_COMBO_ID = p_global_vs_id
984 AND dimension_id = v_dim_id;
985 v_val_stmt := 'and value_set_id = ' || v_value_set_id;
986 ELSE
987 v_val_stmt := '';
988 v_value_set_id := NULL;
989 END IF;
990
991 --the v_datatype found in fem_xdim_dimensions decides whether
992 --dimension_value or dimension_value_char is used
993 v_insert_stmt :=
994 'INSERT INTO fem_br_disabled_mbrs_gt (object_definition_id,
995 dimension_id, dimension_member, value_set_id, context_code)
996 SELECT ' || p_obj_def_id || ',' || v_dim_id || ', ';
997
998 IF (v_datatype = 'NUMBER') THEN
999 v_insert_stmt := v_insert_stmt || 'dimension_value, ';
1000 ELSE
1001 v_insert_stmt := v_insert_stmt || 'dimension_value_char, ';
1002 END IF;
1003
1004 v_insert_stmt := v_insert_stmt
1005 || nvl(to_char(v_value_set_id), 'NULL') || ', ''' || C_CONTEXT ||
1006 ''' FROM FEM_ALLOC_BR_DIMENSIONS
1007 WHERE object_definition_id = :obj_def_id
1008 AND function_seq = :func_seq
1009 AND alloc_dim_col_name = :dim_col_name
1010 AND post_to_balances_flag = :post_to_bal_flag
1011 AND ';
1012
1013 IF (v_datatype = 'NUMBER') THEN
1014 v_insert_stmt := v_insert_stmt || 'dimension_value ';
1015 ELSE
1016 v_insert_stmt := v_insert_stmt || 'dimension_value_char ';
1017 END IF;
1018
1019 v_insert_stmt := v_insert_stmt ||
1020 'IN (SELECT ' || v_member_column || ' FROM ' ||
1021 v_member_table || ' WHERE enabled_flag = ''N'' ' || v_val_stmt || ')';
1022
1023 IF FND_LOG.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1024 FEM_ENGINES_PKG.TECH_MESSAGE(
1025 p_severity => FND_LOG.level_statement,
1026 p_module => C_MODULE,
1027 p_msg_text => 'dynamic SQL statement is := ' || v_insert_stmt);
1028 END IF;
1029
1030 EXECUTE IMMEDIATE v_insert_stmt USING p_obj_def_id,
1031 context_string.function_seq, data_row.alloc_dim_col_name,
1032 data_row.post_to_balances_flag;
1033
1034 --if a disabled member is found it will be inserted into the table
1035 IF(SQL%ROWCOUNT > 0) THEN
1036 x_this_is_valid := 'N';
1037 END IF;
1038 END IF;
1039 END LOOP;
1040 END IF;
1041 END LOOP;
1042 IF (x_this_is_valid is null) THEN
1043 x_this_is_valid := 'Y';
1044 END IF;
1045
1046 IF FND_LOG.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1047 FEM_ENGINES_PKG.TECH_MESSAGE(
1048 p_severity => FND_LOG.level_procedure,
1049 p_module => C_MODULE,
1050 p_msg_text => 'End Procedure; x_this_is_valid: ' || x_this_is_valid);
1051 END IF;
1052 --
1053 END Validate_Mapping;
1054 -------------------------------------------------------------------------------
1055 --
1056 -- PROCEDURE
1057 -- Validate_Dim_Component
1058 --
1059 -- DESCRIPTION
1060 -- Validates the Dimension Component asociated with given object definition
1061 -- id. Optionally a value set or dimension id can be provided, which further
1062 -- restricts the scope of the search to asceratin if the rule references and
1063 -- disabled members.
1064 -- If the rule references any disabled members, x_this_is_valid will return
1065 -- 'N' and the identifying information for each disabled member found will
1066 -- be logged in the global temporary table fem_br_disabled_mbrs_gt. If it
1067 -- does not reference any disabled members, x_this_is_valid will return 'Y'.
1068 --
1069 -------------------------------------------------------------------------------
1070 PROCEDURE Validate_Dim_Component (
1071 p_obj_def_id IN NUMBER,
1072 p_global_vs_id IN NUMBER,
1073 p_param_dim_id IN NUMBER,
1074 x_this_is_valid OUT NOCOPY VARCHAR2)
1075 -------------------------------------------------------------------------------
1076 IS
1077 --
1081 v_member_table FEM_TAB_COLUMNS_B.TABLE_NAME%TYPE;
1078 C_MODULE CONSTANT FND_LOG_MESSAGES.module%TYPE :=
1079 'fem.plsql.fem_check_disabled_mbrs_pkg.validate_dim_component';
1080
1082 v_member_column FEM_TAB_COLUMNS_B.COLUMN_NAME%TYPE;
1083 v_column_name FEM_XDIM_DIMENSIONS.MEMBER_COL%TYPE;
1084 v_table_name FEM_XDIM_DIMENSIONS.MEMBER_B_TABLE_NAME%TYPE;
1085 v_value_set_id FEM_GLOBAL_VS_COMBO_DEFS.VALUE_SET_ID%TYPE;
1086 v_dim_id FEM_TAB_COLUMNS_B.DIMENSION_ID%TYPE;
1087 v_criteria_seq FEM_COND_DIM_CMP_DTL.CRITERIA_SEQUENCE%TYPE;
1088 v_value_set_flag VARCHAR2(1);
1089 v_context_flag VARCHAR2(1);
1090 v_val_stmt VARCHAR2(100);
1091 v_insert_stmt VARCHAR2(1000);
1092 C_CONTEXT FEM_BR_DISABLED_MBRS_GT.context_code%TYPE;
1093
1094 --for each object definition id there can be several attributes referenced
1095 --all need to be checked to see if they reference disabled members.
1096 cursor c_attr_dim (p_obj_def_id IN NUMBER, p_param_dim_id IN NUMBER) IS
1097 SELECT a.attribute_dimension_id, d.criteria_sequence
1098 FROM fem_dim_attributes_b a, fem_cond_dim_components c,
1099 fem_cond_dim_cmp_dtl d, fem_xdim_dimensions x
1100 WHERE a.dimension_id = c.dim_id
1101 AND a.attribute_dimension_id = x.dimension_id
1102 AND x.hier_editor_managed_flag = 'Y'
1103 AND a.attribute_varchar_label = d.dim_attr_varchar_label
1104 AND c.cond_dim_cmp_obj_def_id = d.cond_dim_cmp_obj_def_id
1105 AND c.cond_dim_cmp_obj_def_id = p_obj_def_id
1106 AND c.dim_id = nvl(p_param_dim_id, c.dim_id);
1107
1108 BEGIN
1109
1110 IF FND_LOG.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1111 FEM_ENGINES_PKG.TECH_MESSAGE(
1112 p_severity => FND_LOG.level_procedure,
1113 p_module => C_MODULE,
1114 p_msg_text => 'Begin Procedure');
1115
1116 FEM_ENGINES_PKG.TECH_MESSAGE(
1117 p_severity => FND_LOG.level_procedure,
1118 p_module => C_MODULE,
1119 p_msg_text => 'p_obj_def_id is ' || p_obj_def_id);
1120
1121 FEM_ENGINES_PKG.TECH_MESSAGE(
1122 p_severity => FND_LOG.level_procedure,
1123 p_module => C_MODULE,
1124 p_msg_text => 'p_param_dim_id is ' || p_param_dim_id);
1125
1126 FEM_ENGINES_PKG.TECH_MESSAGE(
1127 p_severity => FND_LOG.level_procedure,
1128 p_module => C_MODULE,
1129 p_msg_text => 'p_global_vs_id is ' || p_global_vs_id);
1130
1131 END IF;
1132
1133 --get the context, dimension id
1134 SELECT dim_comp_type, dim_id
1135 INTO v_context_flag, v_dim_id
1136 FROM fem_cond_dim_components
1137 WHERE cond_dim_cmp_obj_def_id = p_obj_def_id;
1138
1139 --We dont check for hierarchies. If its a hier, return valid.
1140 IF (v_context_flag = 'H') THEN
1141 x_this_is_valid := 'Y';
1142 IF FND_LOG.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1143 FEM_ENGINES_PKG.TECH_MESSAGE(
1144 p_severity => FND_LOG.level_statement,
1145 p_module => C_MODULE,
1146 p_msg_text => 'context is hierarchy, x_this_is_valid is returning
1147 with value of ' || x_this_is_valid);
1148 END IF;
1149 RETURN;
1150 END IF;
1151 IF (v_context_flag = 'V') THEN
1152 C_CONTEXT := 'DIM_VALUE';
1153
1154 IF (v_dim_id = p_param_dim_id OR p_param_dim_id is null) THEN
1155 SELECT member_b_table_name, member_col, value_set_required_flag
1156 INTO v_member_table, v_member_column, v_value_set_flag
1157 FROM fem_xdim_dimensions
1158 WHERE dimension_id = v_dim_id;
1159
1160 IF (v_value_set_flag = 'Y' AND p_global_vs_id is not null) THEN
1161 SELECT value_set_id
1162 INTO v_value_set_id
1163 FROM fem_global_vs_combo_defs
1164 WHERE GLOBAL_VS_COMBO_ID = p_global_vs_id
1165 AND dimension_id = v_dim_id;
1166 v_val_stmt := 'and value_set_id = ' || v_value_set_id;
1167 ELSE
1168 v_val_stmt := '';
1169 v_value_set_id := NULL;
1170 END IF;
1171
1172 ELSE
1173 x_this_is_valid := 'Y';
1174 RETURN;
1175 END IF;
1176 v_insert_stmt :=
1177 'INSERT INTO fem_br_disabled_mbrs_gt (object_definition_id,
1178 dimension_id, dimension_member, value_set_id, context_code)
1179 SELECT ' || p_obj_def_id || ',' || v_dim_id || ', value, '
1180 || nvl(to_char(v_value_set_id), 'NULL') || ', ''' || C_CONTEXT ||
1181 ''' FROM FEM_COND_DIM_COMPONENTS
1182 WHERE cond_dim_cmp_obj_def_id = :obj_def_id
1183 AND value IN
1184 (SELECT to_char(' || v_member_column ||
1185 ') FROM ' || v_member_table ||
1186 ' WHERE enabled_flag = ''N'' ' || v_val_stmt || ') ';
1187
1188 IF FND_LOG.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1189 FEM_ENGINES_PKG.TECH_MESSAGE(
1190 p_severity => FND_LOG.level_statement,
1191 p_module => C_MODULE,
1192 p_msg_text => 'dynamic SQL statement for context('
1193 || C_CONTEXT || ') is := ' || v_insert_stmt);
1194 END IF;
1195 EXECUTE IMMEDIATE v_insert_stmt USING p_obj_def_id;
1196
1197 --if a disabled member is found it will be inserted into the table
1198 IF(SQL%ROWCOUNT > 0) THEN
1199 x_this_is_valid := 'N';
1200 END IF;
1201
1202
1203 ELSE
1204 IF (v_context_flag = 'A') THEN
1205 C_CONTEXT := 'DIM_ATTR';
1206 FOR attr_row IN c_attr_dim(p_obj_def_id, p_param_dim_id) LOOP
1207 v_dim_id := attr_row.attribute_dimension_id;
1211 SELECT member_b_table_name, member_col, value_set_required_flag
1208 v_criteria_seq := attr_row.criteria_sequence;
1209
1210 IF (v_dim_id = p_param_dim_id OR p_param_dim_id is null) THEN
1212 INTO v_member_table, v_member_column, v_value_set_flag
1213 FROM fem_xdim_dimensions
1214 WHERE dimension_id = v_dim_id;
1215
1216 IF (v_value_set_flag = 'Y' AND p_global_vs_id is not null) THEN
1217 SELECT value_set_id
1218 INTO v_value_set_id
1219 FROM fem_global_vs_combo_defs
1220 WHERE GLOBAL_VS_COMBO_ID = p_global_vs_id
1221 AND dimension_id = v_dim_id;
1222 v_val_stmt := 'and value_set_id = ' || v_value_set_id;
1223 ELSE
1224 v_val_stmt := '';
1225 v_value_set_id := NULL;
1226 END IF;
1227
1228 v_insert_stmt :=
1229 'INSERT INTO fem_br_disabled_mbrs_gt (object_definition_id,
1230 dimension_id, dimension_member, value_set_id, context_code)
1231 SELECT ' || p_obj_def_id || ',' || v_dim_id ||
1232 ', dim_attr_value,' || nvl(to_char(v_value_set_id), 'NULL') || ', ''' || C_CONTEXT ||
1233 ''' FROM FEM_COND_DIM_CMP_DTL
1234 WHERE cond_dim_cmp_obj_def_id = :obj_def_id
1235 AND criteria_sequence = :criteria_seq
1236 AND dim_attr_value IN
1237 (SELECT to_char(' || v_member_column ||
1238 ') FROM ' || v_member_table ||
1239 ' WHERE enabled_flag = ''N'' ' || v_val_stmt || ') ';
1240
1241 IF FND_LOG.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1242 FEM_ENGINES_PKG.TECH_MESSAGE(
1243 p_severity => FND_LOG.level_statement,
1244 p_module => C_MODULE,
1245 p_msg_text => 'dynamic SQL statement for context('
1246 || C_CONTEXT || ') is := ' || v_insert_stmt);
1247 END IF;
1248 END IF;
1249
1250 IF (v_insert_stmt is not NULL) THEN
1251 EXECUTE IMMEDIATE v_insert_stmt USING p_obj_def_id, v_criteria_seq;
1252 END IF;
1253
1254 --if a disabled member is found it will be inserted into the table
1255 IF(SQL%ROWCOUNT > 0) THEN
1256 x_this_is_valid := 'N';
1257 END IF;
1258 END LOOP;
1259 END IF;
1260 END IF;
1261
1262 IF (x_this_is_valid is null) THEN
1263 x_this_is_valid := 'Y';
1264 END IF;
1265
1266 IF FND_LOG.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1267 FEM_ENGINES_PKG.TECH_MESSAGE(
1268 p_severity => FND_LOG.level_procedure,
1269 p_module => C_MODULE,
1270 p_msg_text => 'End Procedure; x_this_is_valid: ' || x_this_is_valid);
1271 END IF;
1272 --
1273 END Validate_Dim_Component;
1274 -------------------------------------------------------------------------------
1275 --
1276 -- PROCEDURE
1277 -- Validate_Data_Component
1278 --
1279 -- DESCRIPTION
1280 -- Validates the Data Component asociated with given object definition
1281 -- id. Optionally a value set or dimension id can be provided, which further
1282 -- restricts the scope of the search to asceratin if the rule references and
1283 -- disabled members.
1284 -- If the rule references any disabled members, x_this_is_valid will return
1285 -- 'N' and the identifying information for each disabled member found will
1286 -- be logged in the global temporary table fem_br_disabled_mbrs_gt. If it
1287 -- does not reference any disabled members, x_this_is_valid will return 'Y'.
1288 --
1289 -------------------------------------------------------------------------------
1290 PROCEDURE Validate_Data_Component (
1291 p_obj_def_id IN NUMBER,
1292 p_global_vs_id IN NUMBER,
1293 p_param_dim_id IN NUMBER,
1294 x_this_is_valid OUT NOCOPY VARCHAR2)
1295 -------------------------------------------------------------------------------
1296 IS
1297 --
1298 C_MODULE CONSTANT FND_LOG_MESSAGES.module%TYPE :=
1299 'fem.plsql.fem_check_disabled_mbrs_pkg.validate_data_component';
1300
1301 v_member_table FEM_TAB_COLUMNS_B.TABLE_NAME%TYPE;
1302 v_member_column FEM_TAB_COLUMNS_B.COLUMN_NAME%TYPE;
1303 v_column_name FEM_XDIM_DIMENSIONS.MEMBER_COL%TYPE;
1304 v_table_name FEM_XDIM_DIMENSIONS.MEMBER_B_TABLE_NAME%TYPE;
1305 v_sequence FEM_COND_DATA_CMP_STEPS.STEP_SEQUENCE%TYPE;
1306 v_criteria_seq FEM_COND_DATA_CMP_ST_DTL.CRITERIA_SEQUENCE%TYPE;
1307 v_value_set_id FEM_GLOBAL_VS_COMBO_DEFS.VALUE_SET_ID%TYPE;
1308 v_dim_id FEM_TAB_COLUMNS_B.DIMENSION_ID%TYPE;
1309 v_value_set_flag VARCHAR2(1);
1310 v_val_stmt VARCHAR2(100);
1311 v_insert_stmt VARCHAR2(1000);
1312 C_CONTEXT CONSTANT FEM_BR_DISABLED_MBRS_GT.context_code%TYPE
1313 := 'DATA_VALUE';
1314
1315 --there can be multiple column/table/dimension combinations for each
1316 --object definition id. This ensures that each is checked for
1317 --disabled members.
1318 -- Note: hier_editor_managed_flag denotes if a dimension is managed by DHM
1319 cursor c_objdef (p_obj_def_id IN NUMBER, p_param_dim_id IN NUMBER) IS
1320 SELECT A.column_name, A.table_name, A.step_sequence, B.dimension_id,
1321 D.criteria_sequence
1322 FROM fem_cond_data_cmp_steps A, fem_tab_columns_b B,
1323 fem_cond_data_cmp_st_dtl D, fem_xdim_dimensions X
1324 WHERE A.column_name = B.column_name
1325 AND A.table_name = B.table_name
1326 AND A.cond_data_cmp_obj_def_id = p_obj_def_id
1327 AND B.dimension_id = X.dimension_id
1328 AND X.hier_editor_managed_flag = 'Y'
1329 AND D.cond_data_cmp_obj_def_id = p_obj_def_id
1333
1330 AND B.dimension_id = nvl(p_param_dim_id, B.dimension_id);
1331
1332 BEGIN
1334 IF FND_LOG.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1335 FEM_ENGINES_PKG.TECH_MESSAGE(
1336 p_severity => FND_LOG.level_procedure,
1337 p_module => C_MODULE,
1338 p_msg_text => 'Begin Procedure');
1339
1340 FEM_ENGINES_PKG.TECH_MESSAGE(
1341 p_severity => FND_LOG.level_procedure,
1342 p_module => C_MODULE,
1343 p_msg_text => 'p_obj_def_id is ' || p_obj_def_id);
1344
1345 FEM_ENGINES_PKG.TECH_MESSAGE(
1346 p_severity => FND_LOG.level_procedure,
1347 p_module => C_MODULE,
1348 p_msg_text => 'p_param_dim_id is ' || p_param_dim_id);
1349
1350 FEM_ENGINES_PKG.TECH_MESSAGE(
1351 p_severity => FND_LOG.level_procedure,
1352 p_module => C_MODULE,
1353 p_msg_text => 'p_global_vs_id is ' || p_global_vs_id);
1354 END IF;
1355
1356 --check each if it references a disabled member
1357 FOR data_row IN c_objdef(p_obj_def_id, p_param_dim_id) LOOP
1358 v_column_name := data_row.column_name;
1359 v_table_name := data_row.table_name;
1360 v_sequence := data_row.step_sequence;
1361 v_dim_id := data_row.dimension_id;
1362 v_criteria_seq := data_row.criteria_sequence;
1363
1364 IF (v_dim_id = p_param_dim_id OR p_param_dim_id is null) THEN
1365 SELECT member_b_table_name, member_col, value_set_required_flag
1366 INTO v_member_table, v_member_column, v_value_set_flag
1367 FROM fem_xdim_dimensions
1368 WHERE dimension_id = v_dim_id;
1369
1370 IF (v_value_set_flag = 'Y' AND p_global_vs_id is not null) THEN
1371 SELECT value_set_id
1372 INTO v_value_set_id
1373 FROM fem_global_vs_combo_defs
1374 WHERE GLOBAL_VS_COMBO_ID = p_global_vs_id
1375 AND dimension_id = v_dim_id;
1376 v_val_stmt := 'and value_set_id = ' || v_value_set_id;
1377 ELSE
1378 v_val_stmt := '';
1379 v_value_set_id := NULL;
1380 END IF;
1381
1382 v_insert_stmt :=
1383 'INSERT INTO fem_br_disabled_mbrs_gt (object_definition_id,
1384 dimension_id, dimension_member, value_set_id, context_code)
1385 SELECT ' || p_obj_def_id || ',' || v_dim_id || ', value, '
1386 || nvl(to_char(v_value_set_id), 'NULL') || ', ''' || C_CONTEXT ||
1387 ''' FROM FEM_COND_DATA_CMP_ST_DTL
1388 WHERE step_sequence = :seq
1389 AND table_name = :tab_name
1390 AND criteria_sequence = :c_seq
1391 AND cond_data_cmp_obj_def_id = :obj_def_id
1392 AND value IN
1393 (SELECT to_char(' || v_member_column ||
1394 ') FROM ' || v_member_table ||
1395 ' WHERE enabled_flag = ''N'' ' || v_val_stmt || ') ';
1396
1397 IF FND_LOG.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1398 FEM_ENGINES_PKG.TECH_MESSAGE(
1399 p_severity => FND_LOG.level_statement,
1400 p_module => C_MODULE,
1401 p_msg_text => 'dynamic SQL statement is := ' || v_insert_stmt);
1402 END IF;
1403
1404 EXECUTE IMMEDIATE v_insert_stmt USING v_sequence,v_table_name,
1405 v_criteria_seq, p_obj_def_id;
1406
1407 --if a disabled member is found it will be inserted into the table
1408 IF(SQL%ROWCOUNT > 0) THEN
1409 x_this_is_valid := 'N';
1410 END IF;
1411 END IF;
1412 END LOOP;
1413 IF (x_this_is_valid is null) THEN
1414 x_this_is_valid := 'Y';
1415 END IF;
1416
1417 IF FND_LOG.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1418 FEM_ENGINES_PKG.TECH_MESSAGE(
1419 p_severity => FND_LOG.level_procedure,
1420 p_module => C_MODULE,
1421 p_msg_text => 'End Procedure; x_this_is_valid: ' || x_this_is_valid);
1422 END IF;
1423 --
1424 END Validate_Data_Component;
1425 -------------------------------------------------------------------------------
1426 --
1427 -- PROCEDURE
1428 -- Validate_Statistic
1429 --
1430 -- DESCRIPTION
1431 -- Validates the Statistic Rule asociated with given object definition id.
1432 -- Optionally a value set or dimension id can be provided, which further
1433 -- restricts the scope of the search to asceratin if the rule references and
1434 -- disabled members.
1435 -- If the rule references any disabled members, x_this_is_valid will return
1436 -- 'N' and the identifying information for each disabled member found will
1437 -- be logged in the global temporary table fem_br_disabled_mbrs_gt. If it
1438 -- does not reference any disabled members, x_this_is_valid will return 'Y'.
1439 --
1440 -------------------------------------------------------------------------------
1441 PROCEDURE Validate_Statistic (
1442 p_obj_def_id IN NUMBER,
1443 p_global_vs_id IN NUMBER,
1444 p_param_dim_id IN NUMBER,
1445 x_this_is_valid OUT NOCOPY VARCHAR2)
1446 -------------------------------------------------------------------------------
1447 IS
1448 --
1449 C_MODULE CONSTANT FND_LOG_MESSAGES.module%TYPE :=
1450 'fem.plsql.fem_check_disabled_mbrs_pkg.validate_statistic';
1451
1452 v_member_table FEM_TAB_COLUMNS_B.TABLE_NAME%TYPE;
1453 v_member_column FEM_TAB_COLUMNS_B.COLUMN_NAME%TYPE;
1454 v_column_name FEM_XDIM_DIMENSIONS.MEMBER_COL%TYPE;
1455 v_table_name FEM_XDIM_DIMENSIONS.MEMBER_B_TABLE_NAME%TYPE;
1456 v_value_set_id FEM_GLOBAL_VS_COMBO_DEFS.VALUE_SET_ID%TYPE;
1460 v_insert_stmt VARCHAR2(1000);
1457 v_dim_id FEM_TAB_COLUMNS_B.DIMENSION_ID%TYPE;
1458 v_value_set_flag VARCHAR2(1);
1459 v_val_stmt VARCHAR2(100);
1461 C_CONTEXT CONSTANT FEM_BR_DISABLED_MBRS_GT.context_code%TYPE
1462 := 'STATS_VALUE';
1463
1464 --there can be multiple column/table/dimension combinations for each
1465 --object definition id. This ensures that each is checked for
1466 --disabled members.
1467 cursor c_objdef (p_obj_def_id IN NUMBER, p_param_dim_id IN NUMBER) IS
1468 SELECT C.column_name, C.table_name, C.dimension_id
1469 FROM fem_stat_lookups A, fem_stat_lookup_rel B, fem_tab_columns_b C,
1470 fem_xdim_dimensions X
1471 WHERE A.stat_lookup_obj_def_id = p_obj_def_id
1472 AND A.stat_lookup_obj_def_id = B.stat_lookup_obj_def_id
1473 AND A.stat_lookup_table = C.table_name
1474 AND B.stat_lookup_tbl_col = C.column_name
1475 AND C.dimension_id = X.dimension_id
1476 AND X.hier_editor_managed_flag = 'Y'
1477 AND C.dimension_id = nvl(p_param_dim_id, C.dimension_id);
1478
1479 BEGIN
1480
1481 IF FND_LOG.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1482 FEM_ENGINES_PKG.TECH_MESSAGE(
1483 p_severity => FND_LOG.level_procedure,
1484 p_module => C_MODULE,
1485 p_msg_text => 'Begin Procedure');
1486
1487 FEM_ENGINES_PKG.TECH_MESSAGE(
1488 p_severity => FND_LOG.level_procedure,
1489 p_module => C_MODULE,
1490 p_msg_text => 'p_obj_def_id is ' || p_obj_def_id);
1491
1492 FEM_ENGINES_PKG.TECH_MESSAGE(
1493 p_severity => FND_LOG.level_procedure,
1494 p_module => C_MODULE,
1495 p_msg_text => 'p_param_dim_id is ' || p_param_dim_id);
1496
1497 FEM_ENGINES_PKG.TECH_MESSAGE(
1498 p_severity => FND_LOG.level_procedure,
1499 p_module => C_MODULE,
1500 p_msg_text => 'p_global_vs_id is ' || p_global_vs_id);
1501
1502 END IF;
1503
1504 --check each if it references a disabled member
1505 FOR data_row IN c_objdef(p_obj_def_id, p_param_dim_id) LOOP
1506 v_column_name := data_row.column_name;
1507 v_table_name := data_row.table_name;
1508 v_dim_id := data_row.dimension_id;
1509
1510 IF (v_dim_id = p_param_dim_id OR p_param_dim_id is null) THEN
1511 SELECT member_b_table_name, member_col, value_set_required_flag
1512 INTO v_member_table, v_member_column, v_value_set_flag
1513 FROM fem_xdim_dimensions
1514 WHERE dimension_id = v_dim_id;
1515
1516 IF (v_value_set_flag = 'Y' AND p_global_vs_id is not null) THEN
1517 SELECT value_set_id
1518 INTO v_value_set_id
1519 FROM fem_global_vs_combo_defs
1520 WHERE GLOBAL_VS_COMBO_ID = p_global_vs_id
1521 AND dimension_id = v_dim_id;
1522 v_val_stmt := 'and value_set_id = ' || v_value_set_id;
1523 ELSE
1524 v_val_stmt := '';
1525 v_value_set_id := NULL;
1526 END IF;
1527
1528
1529 v_insert_stmt :=
1530 'INSERT INTO fem_br_disabled_mbrs_gt (object_definition_id,
1531 dimension_id, dimension_member, value_set_id, context_code)
1532 SELECT ' || p_obj_def_id || ',' || v_dim_id || ', value, '
1533 || nvl(to_char(v_value_set_id), 'NULL') || ', ''' || C_CONTEXT ||
1534 ''' FROM FEM_STAT_LOOKUP_REL
1535 WHERE stat_lookup_obj_def_id = :obj_def_id
1536 AND stat_lookup_tbl_col = :column_name
1537 AND value IN
1538 (SELECT to_char(' || v_member_column ||
1539 ') FROM ' || v_member_table ||
1540 ' WHERE enabled_flag = ''N'' ' || v_val_stmt || ') ';
1541
1542 IF FND_LOG.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1543 FEM_ENGINES_PKG.TECH_MESSAGE(
1544 p_severity => FND_LOG.level_statement,
1545 p_module => C_MODULE,
1546 p_msg_text => 'dynamic SQL statement is := ' || v_insert_stmt);
1547 END IF;
1548
1549 EXECUTE IMMEDIATE v_insert_stmt USING p_obj_def_id, v_column_name;
1550
1551 --if a disabled member is found it will be inserted into the table
1552 IF(SQL%ROWCOUNT > 0) THEN
1553 x_this_is_valid := 'N';
1554 END IF;
1555 END IF;
1556 END LOOP;
1557 IF (x_this_is_valid is null) THEN
1558 x_this_is_valid := 'Y';
1559 END IF;
1560
1561 IF FND_LOG.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1562 FEM_ENGINES_PKG.TECH_MESSAGE(
1563 p_severity => FND_LOG.level_procedure,
1564 p_module => C_MODULE,
1565 p_msg_text => 'End Procedure; x_this_is_valid: ' || x_this_is_valid);
1566 END IF;
1567 --
1568 END Validate_Statistic;
1569 -------------------------------------------------------------------------------
1570 --
1571 -- PROCEDURE
1572 -- Validate_Hierarchy
1573 --
1574 -- DESCRIPTION
1575 -- Validates the Hierarchy asociated with given object definition id.
1576 -- Optionally a value set or dimension id can be provided, which further
1577 -- restricts the scope of the search to asceratin if the rule references and
1578 -- disabled members.
1579 -- If the rule references any disabled members, x_this_is_valid will return
1580 -- 'N' and the identifying information for each disabled member found will
1581 -- be logged in the global temporary table fem_br_disabled_mbrs_gt. If it
1582 -- does not reference any disabled members, x_this_is_valid will return 'Y'.
1583 --
1587 p_global_vs_id IN NUMBER,
1584 -------------------------------------------------------------------------------
1585 PROCEDURE Validate_Hierarchy (
1586 p_obj_def_id IN NUMBER,
1588 p_param_dim_id IN NUMBER,
1589 x_this_is_valid OUT NOCOPY VARCHAR2)
1590 -------------------------------------------------------------------------------
1591 IS
1592 --
1593 C_MODULE CONSTANT FND_LOG_MESSAGES.module%TYPE :=
1594 'fem.plsql.fem_check_disabled_mbrs_pkg.validate_hierarchy';
1595
1596 v_member_table FEM_TAB_COLUMNS_B.TABLE_NAME%TYPE;
1597 v_member_column FEM_TAB_COLUMNS_B.COLUMN_NAME%TYPE;
1598 v_value_set_id FEM_GLOBAL_VS_COMBO_DEFS.VALUE_SET_ID%TYPE;
1599 v_dim_id FEM_TAB_COLUMNS_B.DIMENSION_ID%TYPE;
1600 v_hier_table_name FEM_XDIM_DIMENSIONS.HIERARCHY_TABLE_NAME%TYPE;
1601 v_hier_flattened FEM_HIER_DEFINITIONS.
1602 FLATTENED_ROWS_COMPLETION_CODE%TYPE;
1603 v_hier_obj_id FEM_HIERARCHIES.HIERARCHY_OBJ_ID%TYPE;
1604 v_hier_obj_name FEM_OBJECT_CATALOG_VL.OBJECT_NAME%TYPE;
1605 v_hier_obj_def_name FEM_OBJECT_DEFINITION_VL.DISPLAY_NAME%TYPE;
1606 v_mbr_disp_cd_col FEM_XDIM_DIMENSIONS.MEMBER_DISPLAY_CODE_COL%TYPE;
1607 v_value_set_name FEM_VALUE_SETS_VL.VALUE_SET_NAME%TYPE;
1608 v_parent_disp_cd FEM_XDIM_DIMENSIONS.MEMBER_DISPLAY_CODE_COL%TYPE;
1609 v_parent_name FEM_OBJECT_CATALOG_VL.OBJECT_NAME%TYPE;
1610 v_child_disp_cd FEM_XDIM_DIMENSIONS.MEMBER_DISPLAY_CODE_COL%TYPE;
1611 v_child_name FEM_OBJECT_CATALOG_VL.OBJECT_NAME%TYPE;
1612 v_mbr_vl_tab_name FEM_XDIM_DIMENSIONS.MEMBER_VL_OBJECT_NAME%TYPE;
1613 v_member_name_col FEM_XDIM_DIMENSIONS.MEMBER_NAME_COL%TYPE;
1614 v_value_set_flag VARCHAR2(1);
1615 v_flat_flag VARCHAR2(1);
1616 v_insert_stmt VARCHAR2(18000);
1617 C_CONTEXT FEM_BR_DISABLED_MBRS_GT.context_code%TYPE;
1618
1619
1620 BEGIN
1621
1622 IF FND_LOG.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1623 FEM_ENGINES_PKG.TECH_MESSAGE(
1624 p_severity => FND_LOG.level_procedure,
1625 p_module => C_MODULE,
1626 p_msg_text => 'Begin Procedure');
1627
1628 FEM_ENGINES_PKG.TECH_MESSAGE(
1629 p_severity => FND_LOG.level_procedure,
1630 p_module => C_MODULE,
1631 p_msg_text => 'p_obj_def_id is ' || p_obj_def_id);
1632
1633 FEM_ENGINES_PKG.TECH_MESSAGE(
1634 p_severity => FND_LOG.level_procedure,
1635 p_module => C_MODULE,
1636 p_msg_text => 'p_param_dim_id is ' || p_param_dim_id);
1637
1638 FEM_ENGINES_PKG.TECH_MESSAGE(
1639 p_severity => FND_LOG.level_procedure,
1640 p_module => C_MODULE,
1641 p_msg_text => 'p_global_vs_id is ' || p_global_vs_id);
1642 END IF;
1643
1644 --get dimension id and information for fem_br_dis_mbr_hier table
1645 SELECT h.dimension_id, h.hierarchy_obj_id, a.object_name, od.display_name,
1646 h.flattened_rows_flag
1647 INTO v_dim_id, v_hier_obj_id, v_hier_obj_name, v_hier_obj_def_name,
1648 v_flat_flag
1649 FROM fem_hier_definitions d, fem_object_definition_b o,
1650 fem_object_definition_vl od,
1651 fem_object_catalog_b c, fem_hierarchies h, fem_object_catalog_vl a
1652 WHERE d.hierarchy_obj_def_id = p_obj_def_id
1653 AND d.hierarchy_obj_def_id = o.object_definition_id
1654 AND o.object_definition_id = od.object_definition_id
1655 AND o.object_id = c.object_id
1656 AND c.object_id = h.hierarchy_obj_id
1657 AND a.object_id = c.object_id;
1658
1659 --get the member table and columns
1660 IF (v_dim_id = p_param_dim_id OR p_param_dim_id is null) THEN
1661 SELECT member_b_table_name, member_col, value_set_required_flag,
1662 hierarchy_table_name, member_display_code_col, member_vl_object_name,
1663 member_name_col
1664 INTO v_member_table, v_member_column, v_value_set_flag,
1665 v_hier_table_name, v_mbr_disp_cd_col, v_mbr_vl_tab_name,
1666 v_member_name_col
1667 FROM fem_xdim_dimensions
1668 WHERE dimension_id = v_dim_id;
1669
1670 IF (v_value_set_flag = 'Y' AND p_global_vs_id is not null) THEN
1671 SELECT d.value_set_id, s.value_set_name
1672 INTO v_value_set_id, v_value_set_name
1673 FROM fem_global_vs_combo_defs d, fem_value_sets_vl s
1674 WHERE d.GLOBAL_VS_COMBO_ID = p_global_vs_id
1675 AND d.dimension_id = v_dim_id
1676 AND d.value_set_id = s.value_set_id;
1677 ELSE
1678 v_value_set_name := NULL;
1679 v_value_set_id := NULL;
1680 END IF;
1681
1682 ELSE
1683 x_this_is_valid := 'Y';
1684 RETURN;
1685 END IF;
1686
1687 SELECT flattened_rows_completion_code
1688 INTO v_hier_flattened
1689 FROM fem_hier_definitions
1690 WHERE hierarchy_obj_def_id = p_obj_def_id;
1691
1692 IF FND_LOG.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1693 FEM_ENGINES_PKG.TECH_MESSAGE(
1694 p_severity => FND_LOG.level_statement,
1695 p_module => C_MODULE,
1696 p_msg_text => 'v_hier_flattened is := ' || v_hier_flattened);
1697 END IF;
1698
1699 --whether or not the hier is flattened doesnt effect the first part of
1700 --the dynamic insert stmt.
1701 v_insert_stmt := 'INSERT INTO fem_br_dis_mbr_hier
1702 (request_id, hierarchy_object_id, hierarchy_object_name,
1703 hierarchy_obj_def_name, creation_date, created_by, last_updated_by,
1704 last_update_date, last_update_login, parent_value_set_name,
1708 SELECT FND_GLOBAL.conc_request_id, ' || v_hier_obj_id || ', ''' ||
1705 child_value_set_name, parent_value_set_id, child_value_set_id,
1706 disabled_flag, hierarchy_obj_def_id, parent_member_id, child_member_id,
1707 parent_display_code, child_display_code, parent_name, child_name)
1709 v_hier_obj_name || ''', ''' || v_hier_obj_def_name
1710 || ''', SYSDATE, FND_GLOBAL.user_id, FND_GLOBAL.user_id, SYSDATE,
1711 FND_GLOBAL.login_id, ''' || v_value_set_name || ''', ''' ||
1712 v_value_set_name || ''',' || nvl(to_char(v_value_set_id), 'NULL')
1713 || ',' || nvl(to_char(v_value_set_id), 'NULL') || ',';
1714
1715 IF FND_LOG.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1716 FEM_ENGINES_PKG.TECH_MESSAGE(
1717 p_severity => FND_LOG.level_statement,
1718 p_module => C_MODULE,
1719 p_msg_text => 'dynamic SQL statement is := ' || v_insert_stmt);
1720 END IF;
1721
1722 IF (v_hier_flattened = 'COMPLETED' AND v_flat_flag = 'Y') THEN
1723 --flattened
1724 v_insert_stmt := v_insert_stmt ||
1725 ' ''N'', h.hierarchy_obj_def_id, h.parent_id, h.child_id, p.'
1726 || v_mbr_disp_cd_col || ', c.' || v_mbr_disp_cd_col || ', p.'
1727 || v_member_name_col || ', c.' || v_member_name_col ||
1728 ' FROM ' || v_hier_table_name || ' h, ' || v_mbr_vl_tab_name
1729 || ' p, ' || v_mbr_vl_tab_name || ' c' ||
1730 ' WHERE h.hierarchy_obj_def_id = :obj_def_id' ||
1731 ' AND parent_id = p.' || v_member_column ||
1732 ' AND child_id = c.' || v_member_column ||
1733 ' AND h.single_depth_flag = ''Y''
1734 AND h.child_id IN
1735 (SELECT parent_id
1736 FROM ' || v_hier_table_name ||
1737 ' WHERE hierarchy_obj_def_id = :obj_def_id' ||
1738 ' AND child_id IN
1739 (SELECT ' || v_member_column ||
1740 ' FROM ' || v_member_table ||
1741 ' WHERE enabled_flag = ''N'')
1742 UNION ALL
1743 SELECT child_id
1744 FROM ' || v_hier_table_name ||
1745 ' WHERE hierarchy_obj_def_id = :obj_def_id' ||
1746 ' AND child_id IN
1747 (SELECT ' || v_member_column ||
1748 ' FROM ' || v_member_table ||
1749 ' WHERE enabled_flag = ''N''))';
1750
1751
1752 IF FND_LOG.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1753 FEM_ENGINES_PKG.TECH_MESSAGE(
1754 p_severity => FND_LOG.level_statement,
1755 p_module => C_MODULE,
1756 p_msg_text => 'flattened insert dynamic SQL statement is := '
1757 || v_insert_stmt);
1758 END IF;
1759
1760 EXECUTE IMMEDIATE v_insert_stmt USING p_obj_def_id, p_obj_def_id,
1761 p_obj_def_id;
1762
1763 --need to set valid here because the update that follows will
1764 --wipe out rowcount
1765 --if any rows have been inserted, then it is invalid
1766 IF (SQL%ROWCOUNT > 0) THEN
1767 x_this_is_valid := 'N';
1768 ELSE
1769 x_this_is_valid := 'Y';
1770 END IF;
1771
1772 --for all the members that have been inserted, those that are invalid
1773 --need to be marked.
1774 --the other members are used to show the path from root to disabled
1775 --member
1776 v_insert_stmt :=
1777 'UPDATE fem_br_dis_mbr_hier
1778 SET disabled_flag = ''Y''
1779 WHERE hierarchy_obj_def_id = :obj_def_id' ||
1780 ' AND child_member_id IN
1781 (SELECT ' || v_member_column ||
1782 ' FROM ' || v_member_table ||
1783 ' WHERE enabled_flag = ''N'')';
1784
1785 IF FND_LOG.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1786 FEM_ENGINES_PKG.TECH_MESSAGE(
1787 p_severity => FND_LOG.level_statement,
1788 p_module => C_MODULE,
1789 p_msg_text => 'update dynamic SQL statement is := ' || v_insert_stmt);
1790 END IF;
1791
1792 EXECUTE IMMEDIATE v_insert_stmt USING p_obj_def_id;
1793 ELSE
1794 --the hierarchy is NOT flattened
1795 v_insert_stmt := v_insert_stmt ||
1796 ' ''Y'', h.hierarchy_obj_def_id, h.parent_id, h.child_id, p.'
1797 || v_mbr_disp_cd_col || ', c.' || v_mbr_disp_cd_col || ', p.'
1798 || v_member_name_col || ', c.' || v_member_name_col ||
1799 ' FROM ' || v_hier_table_name || ' h, ' || v_mbr_vl_tab_name
1800 || ' p, ' || v_mbr_vl_tab_name || ' c' ||
1801 ' WHERE h.hierarchy_obj_def_id = :obj_def_id' ||
1802 ' AND parent_id = p.' || v_member_column ||
1803 ' AND child_id = c.' || v_member_column ||
1804 ' AND h.child_id IN
1805 (SELECT ' || v_member_column ||
1806 ' FROM ' || v_member_table ||
1807 ' WHERE enabled_flag = ''N'')';
1808
1809
1810 IF FND_LOG.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1811 FEM_ENGINES_PKG.TECH_MESSAGE(
1812 p_severity => FND_LOG.level_statement,
1813 p_module => C_MODULE,
1814 p_msg_text => 'unflattened insert dynamic SQL statement is := '
1815 || v_insert_stmt);
1816 END IF;
1817 EXECUTE IMMEDIATE v_insert_stmt USING p_obj_def_id;
1818
1819 --need to set valid here because the update in the unflattened case
1820 --wipes out rowcount
1821 --if any rows have been inserted, then it is invalid
1822 IF (SQL%ROWCOUNT > 0) THEN
1823 x_this_is_valid := 'N';
1824 ELSE
1825 x_this_is_valid := 'Y';
1826 END IF;
1827 END IF;
1828
1829 --if there are disabled members, insert them in the dis_mbrs_gt
1830 IF (x_this_is_valid = 'N') THEN
1831 v_insert_stmt :=
1832 'INSERT INTO fem_br_disabled_mbrs_gt
1836 IF FND_LOG.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1833 (object_definition_id, context_code)
1834 VALUES (' || to_char(p_obj_def_id) || ', ''HIERARCHY'')';
1835
1837 FEM_ENGINES_PKG.TECH_MESSAGE(
1838 p_severity => FND_LOG.level_statement,
1839 p_module => C_MODULE,
1840 p_msg_text => 'dis mbrs insert dynamic SQL statement is := '
1841 || v_insert_stmt);
1842 END IF;
1843
1844 EXECUTE IMMEDIATE v_insert_stmt;
1845 END IF;
1846
1847
1848 IF FND_LOG.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1849 FEM_ENGINES_PKG.TECH_MESSAGE(
1850 p_severity => FND_LOG.level_procedure,
1851 p_module => C_MODULE,
1852 p_msg_text => 'End Procedure; x_this_is_valid: ' || x_this_is_valid);
1853 END IF;
1854 --
1855 END Validate_Hierarchy;
1856 -------------------------------------------------------------------------------
1857
1858 -------------------------------------------------------------------------------
1859 --
1860 -- PROCEDURE
1861 -- Validate_Factor_Table
1862 --
1863 -- DESCRIPTION
1864 -- Validates the Factor Table asociated with given object definition id.
1865 -- Optionally a value set or dimension id can be provided, which further
1866 -- restricts the scope of the search to asceratin if the rule references and
1867 -- disabled members.
1868 -- If the rule references any disabled members, x_this_is_valid will return
1869 -- 'N' and the identifying information for each disabled member found will
1870 -- be logged in the global temporary table fem_br_disabled_mbrs_gt. If it
1871 -- does not reference any disabled members, x_this_is_valid will return 'Y'.
1872 --
1873 -------------------------------------------------------------------------------
1874 PROCEDURE Validate_Factor_Table (
1875 p_obj_def_id IN NUMBER,
1876 p_global_vs_id IN NUMBER,
1877 p_param_dim_id IN NUMBER,
1878 x_this_is_valid OUT NOCOPY VARCHAR2)
1879 -------------------------------------------------------------------------------
1880 IS
1881 --
1882 C_MODULE CONSTANT FND_LOG_MESSAGES.module%TYPE :=
1883 'fem.plsql.fem_check_disabled_mbrs_pkg.validate_factor_table';
1884
1885 v_member_table FEM_TAB_COLUMNS_B.TABLE_NAME%TYPE;
1886 v_member_column FEM_TAB_COLUMNS_B.COLUMN_NAME%TYPE;
1887 v_value_set_id FEM_GLOBAL_VS_COMBO_DEFS.VALUE_SET_ID%TYPE;
1888 v_value_set_flag VARCHAR2(1);
1889 v_val_stmt VARCHAR2(100);
1890 v_insert_stmt VARCHAR2(1000);
1891 C_CONTEXT CONSTANT FEM_BR_DISABLED_MBRS_GT.context_code%TYPE
1892 := 'DIM_VALUE';
1893
1894 -- Dimensions referenced by a Factor Table rule.
1895 CURSOR c_dims (p_obj_def_id IN NUMBER, p_param_dim_id IN NUMBER) IS
1896 SELECT DISTINCT dimension_id
1897 FROM fem_factor_table_dims
1898 WHERE object_definition_id = p_obj_def_id
1899 AND dimension_id = nvl(p_param_dim_id, dimension_id);
1900
1901 BEGIN
1902
1903 IF FND_LOG.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1904 FEM_ENGINES_PKG.TECH_MESSAGE(
1905 p_severity => FND_LOG.level_procedure,
1906 p_module => C_MODULE,
1907 p_msg_text => 'Begin Procedure');
1908
1909 FEM_ENGINES_PKG.TECH_MESSAGE(
1910 p_severity => FND_LOG.level_procedure,
1911 p_module => C_MODULE,
1912 p_msg_text => 'p_obj_def_id is ' || p_obj_def_id);
1913
1914 FEM_ENGINES_PKG.TECH_MESSAGE(
1915 p_severity => FND_LOG.level_procedure,
1916 p_module => C_MODULE,
1917 p_msg_text => 'p_param_dim_id is ' || p_param_dim_id);
1918
1919 FEM_ENGINES_PKG.TECH_MESSAGE(
1920 p_severity => FND_LOG.level_procedure,
1921 p_module => C_MODULE,
1922 p_msg_text => 'p_global_vs_id is ' || p_global_vs_id);
1923 END IF;
1924
1925 -- For each dimension, check if disabled members are referenced
1926 FOR dim IN c_dims(p_obj_def_id, p_param_dim_id) LOOP
1927
1928 SELECT member_b_table_name, member_col, value_set_required_flag
1929 INTO v_member_table, v_member_column, v_value_set_flag
1930 FROM fem_xdim_dimensions
1931 WHERE dimension_id = dim.dimension_id;
1932
1933 IF (v_value_set_flag = 'Y' AND p_global_vs_id is not null) THEN
1934 SELECT value_set_id
1935 INTO v_value_set_id
1936 FROM fem_global_vs_combo_defs
1937 WHERE GLOBAL_VS_COMBO_ID = p_global_vs_id
1938 AND dimension_id = dim.dimension_id;
1939
1940 v_val_stmt := 'and value_set_id = ' || v_value_set_id;
1941 ELSE
1942 v_val_stmt := '';
1943 v_value_set_id := NULL;
1944 END IF;
1945
1946 v_insert_stmt :=
1947 'INSERT INTO fem_br_disabled_mbrs_gt(object_definition_id,'
1948 || ' dimension_id, dimension_member, value_set_id, context_code)'
1949 ||' SELECT D.object_definition_id, D.dimension_id, F.dim_member, '
1950 || nvl(to_char(v_value_set_id), 'NULL') || ', ''' || C_CONTEXT ||''''
1951 ||' FROM fem_factor_table_dims D, fem_factor_table_fctrs F'
1952 ||' WHERE D.object_definition_id = :obj_def_id'
1953 ||' AND D.dimension_id = :dim_id'
1954 ||' AND D.object_definition_id = F.object_definition_id'
1955 ||' AND D.level_num = F.level_num'
1956 ||' AND F.dim_member IN '
1957 || ' (SELECT to_char(' || v_member_column || ')'
1958 || ' FROM ' || v_member_table
1962 FEM_ENGINES_PKG.TECH_MESSAGE(
1959 || ' WHERE enabled_flag = ''N'' ' || v_val_stmt || ')';
1960
1961 IF FND_LOG.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1963 p_severity => FND_LOG.level_statement,
1964 p_module => C_MODULE,
1965 p_msg_text => 'dynamic SQL statement is := ' || v_insert_stmt);
1966 END IF;
1967
1968 EXECUTE IMMEDIATE v_insert_stmt USING p_obj_def_id, dim.dimension_id;
1969
1970 --if a disabled member is found it will be inserted into the table
1971 IF(SQL%ROWCOUNT > 0) THEN
1972 x_this_is_valid := 'N';
1973 END IF;
1974
1975 END LOOP;
1976
1977 IF (x_this_is_valid IS NULL) THEN
1978 x_this_is_valid := 'Y';
1979 END IF;
1980
1981 IF FND_LOG.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1982 FEM_ENGINES_PKG.TECH_MESSAGE(
1983 p_severity => FND_LOG.level_procedure,
1984 p_module => C_MODULE,
1985 p_msg_text => 'End Procedure; x_this_is_valid: ' || x_this_is_valid);
1986 END IF;
1987 --
1988 END Validate_Factor_Table;
1989 -------------------------------------------------------------------------------
1990
1991 -------------------------------------------------------------------------------
1992 --
1993 -- PROCEDURE
1994 -- Get_Put_Messages
1995 --
1996 -- DESCRIPTION
1997 -- Checks the FND Message stack and posts any messages from the stack
1998 -- into the concurrent program log.
1999 --
2000 -------------------------------------------------------------------------------
2001 PROCEDURE Get_Put_Messages
2002 -------------------------------------------------------------------------------
2003 IS
2004 --
2005 C_MODULE CONSTANT FND_LOG_MESSAGES.module%TYPE :=
2006 'fem.plsql.fem_check_dis_mbr_pkg.get_put_messages';
2007 v_msg_count NUMBER;
2008 v_msg_data VARCHAR2(4000);
2009 v_msg_out NUMBER;
2010 v_message VARCHAR2(4000);
2011 --
2012 BEGIN
2013 --
2014 IF FND_LOG.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2015 FEM_ENGINES_PKG.TECH_MESSAGE(
2016 p_severity => FND_LOG.level_procedure,
2017 p_module => C_MODULE,
2018 p_msg_text => 'Begin Procedure');
2019 END IF;
2020
2021 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_TRUE,
2022 p_count => v_msg_count,
2023 p_data => v_msg_data);
2024
2025 IF FND_LOG.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2026 FEM_ENGINES_PKG.TECH_MESSAGE(
2027 p_severity => FND_LOG.level_statement,
2028 p_module => C_MODULE,
2029 p_msg_text => 'Message count is '||to_char(v_msg_count));
2030 END IF;
2031
2032 -- If there is only one message, it would be returned in v_msg_data
2033 -- so just decode and output it.
2034 -- Otherwise, loop through the message stack to get each message.
2035 IF (v_msg_count = 1) THEN
2039 FEM_ENGINES_PKG.User_Message(p_msg_text => v_message);
2036 FND_MESSAGE.Set_Encoded(v_msg_data);
2037 v_message := FND_MESSAGE.Get;
2038
2040
2041 IF FND_LOG.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2042 FEM_ENGINES_PKG.TECH_MESSAGE(
2043 p_severity => FND_LOG.level_statement,
2044 p_module => C_MODULE,
2045 p_msg_text => 'Message is '||v_message);
2046 END IF;
2047
2048 ELSIF (v_msg_count > 1) THEN
2049 FOR i IN 1..v_msg_count LOOP
2050 FND_MSG_PUB.Get(
2051 p_msg_index => i,
2052 p_encoded => FND_API.G_FALSE,
2053 p_data => v_message,
2054 p_msg_index_out => v_msg_out);
2055
2056 FEM_ENGINES_PKG.User_Message(p_msg_text => v_message);
2057
2058 IF FND_LOG.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2059 FEM_ENGINES_PKG.TECH_MESSAGE(
2060 p_severity => FND_LOG.level_statement,
2061 p_module => C_MODULE,
2062 p_msg_text => 'Message is '||v_message);
2063 END IF;
2064 END LOOP;
2065 END IF; -- IF (v_msg_count = 1)
2066
2067 FND_MSG_PUB.Initialize;
2068
2069 IF FND_LOG.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2070 FEM_ENGINES_PKG.TECH_MESSAGE(
2071 p_severity => FND_LOG.level_procedure,
2072 p_module => C_MODULE,
2073 p_msg_text => 'End Procedure');
2074 END IF;
2075 --
2076 END Get_Put_Messages;
2077 -------------------------------------------------------------------------------
2078
2079 -------------------------------------------------------------------------------
2080 --
2081 -- PROCEDURE
2082 -- Populate_Dim_Info
2083 --
2084 -- DESCRIPTION
2085 -- Populates the following columns in FEM_BR_DIS_MBR_CONTEXTS:
2086 -- DIMENSION_MEMBER_DC, DIMENSION_MEMBER_NAME
2087 --
2088 -------------------------------------------------------------------------------
2089 PROCEDURE Populate_Dim_Info
2090 -------------------------------------------------------------------------------
2091 IS
2092 --
2093 C_MODULE CONSTANT FND_LOG_MESSAGES.module%TYPE :=
2094 'fem.plsql.fem_check_dis_mbr_pkg.populate_dim_info';
2095 --
2096 v_sql VARCHAR2(18000);
2097 --
2098 CURSOR c_dims(p_request_id NUMBER) IS
2099 SELECT dimension_id, member_data_type_code, member_vl_object_name,
2100 member_col, member_display_code_col, member_name_col,
2101 value_set_required_flag
2102 FROM fem_xdim_dimensions
2103 WHERE dimension_id IN (
2104 SELECT dimension_id
2105 FROM fem_br_dis_mbr_contexts
2106 WHERE request_id = p_request_id);
2107 --
2108 BEGIN
2109 --
2110 IF FND_LOG.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2111 FEM_ENGINES_PKG.TECH_MESSAGE(
2112 p_severity => FND_LOG.level_procedure,
2113 p_module => C_MODULE,
2114 p_msg_text => 'Begin Procedure');
2115 END IF;
2116
2117 -- Populate the dimension_member_dc, dimension_member_name columns
2118 -- in FEM_BR_DIS_MBR_CONTEXTS, one dimension at a time.
2119 -- The assumption here is that only those where where DIMENSION_ID column
2120 -- IS NOT NULL would there be a need to populate the dimension names.
2121 FOR dims IN c_dims(FND_GLOBAL.Conc_Request_ID) LOOP
2122 v_sql := 'UPDATE fem_br_dis_mbr_contexts c'
2123 ||' SET (dimension_member_dc, dimension_member_name) = ('
2124 || 'SELECT '||dims.member_display_code_col
2125 || ','||dims.member_name_col
2126 || ' FROM '||dims.member_vl_object_name||' d';
2127
2128 IF (dims.member_data_type_code = 'VARCHAR') THEN
2129 v_sql := v_sql||' WHERE d.'||dims.member_col||' = c.dimension_member';
2130 ELSE
2131 v_sql := v_sql||' WHERE to_char(d.'||dims.member_col
2132 ||') = c.dimension_member';
2133 END IF;
2134
2135 IF (dims.value_set_required_flag = 'Y') THEN
2136 v_sql := v_sql||' AND d.value_set_id = c.value_set_id';
2137 END IF;
2138
2139 v_sql := v_sql||')'
2140 ||' WHERE dimension_id = :dim_id'
2141 ||' AND request_id = :req_id';
2142
2143 IF FND_LOG.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2144 FEM_ENGINES_PKG.TECH_MESSAGE(
2145 p_severity => FND_LOG.level_statement,
2146 p_module => C_MODULE,
2147 p_msg_text => 'v_sql = '||v_sql);
2148 END IF;
2149
2150 EXECUTE IMMEDIATE v_sql
2151 USING dims.dimension_id, FND_GLOBAL.Conc_Request_ID;
2152
2153 END LOOP;
2154
2155 IF FND_LOG.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2156 FEM_ENGINES_PKG.TECH_MESSAGE(
2157 p_severity => FND_LOG.level_procedure,
2158 p_module => C_MODULE,
2159 p_msg_text => 'End Procedure');
2160 END IF;
2161 --
2162 END Populate_Dim_Info;
2163 -------------------------------------------------------------------------------
2164
2165
2166 -------------------------------------------------------------------------------
2167 -- PUBLIC BODIES
2168 -------------------------------------------------------------------------------
2169
2170 -------------------------------------------------------------------------------
2171 --
2172 -- FUNCTION
2173 -- Get_Unique_Report_Row
2174 --
2175 -- DESCRIPTION
2179 -------------------------------------------------------------------------------
2176 -- The function simply returns a unique report row identifier,
2177 -- using a private package variable as a counter.
2178 --
2180 FUNCTION Get_Unique_Report_Row RETURN NUMBER
2181 -------------------------------------------------------------------------------
2182 IS
2183 --
2184 C_MODULE CONSTANT FND_LOG_MESSAGES.module%TYPE :=
2185 'fem.plsql.fem_check_dis_mbr_pkg.get_unique_report_row';
2186 --
2187 BEGIN
2188 --
2189 IF FND_LOG.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2190 FEM_ENGINES_PKG.TECH_MESSAGE(
2191 p_severity => FND_LOG.level_procedure,
2192 p_module => C_MODULE,
2193 p_msg_text => 'Begin Procedure');
2194 END IF;
2195 --
2196 IF (pv_report_row_counter IS NULL) THEN
2197 pv_report_row_counter := 1;
2198 ELSE
2199 pv_report_row_counter := pv_report_row_counter + 1;
2200 END IF;
2201
2202 IF FND_LOG.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2203 FEM_ENGINES_PKG.TECH_MESSAGE(
2204 p_severity => FND_LOG.level_statement,
2205 p_module => C_MODULE,
2206 p_msg_text => 'pv_report_row_counter = '||pv_report_row_counter);
2207 END IF;
2208
2209 IF FND_LOG.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2210 FEM_ENGINES_PKG.TECH_MESSAGE(
2211 p_severity => FND_LOG.level_procedure,
2212 p_module => C_MODULE,
2213 p_msg_text => 'End Procedure');
2214 END IF;
2215
2216 RETURN pv_report_row_counter;
2217 --
2218 END Get_Unique_Report_Row;
2219 -------------------------------------------------------------------------------
2220
2221 -------------------------------------------------------------------------------
2222 --
2223 -- PROCEDURE
2224 -- Report_Invalid_Rules
2225 --
2226 -- DESCRIPTION
2227 -- This is the entrance to the reporting program and is invoked
2228 -- by the concurrent manager for the executable FEM_CHECK_BRDIS_MBRS.
2229 -- It will make calls to other procedures in the
2230 -- FEM_CHECK_BR_DIS_MBRS_PKG to check the root rules
2231 -- for Disabled Members and report on the results.
2232 --
2233 -------------------------------------------------------------------------------
2234 PROCEDURE Report_Invalid_Rules (
2235 errbuf OUT NOCOPY VARCHAR2,
2236 retcode OUT NOCOPY VARCHAR2,
2237 p_rule_type IN VARCHAR2,
2238 p_ledger_id IN NUMBER,
2239 p_effective_date IN VARCHAR2,
2240 p_folder_id IN NUMBER,
2241 p_object_id IN NUMBER,
2242 p_dim_id IN NUMBER,
2243 p_request_name IN VARCHAR2)
2244 -------------------------------------------------------------------------------
2245 IS
2246 --
2247 C_MODULE CONSTANT FND_LOG_MESSAGES.module%TYPE :=
2248 'fem.plsql.fem_check_br_dis_mbrs_pkg.report_invalid_rules';
2249 --
2250 l_effective_date DATE;
2251 v_return_status VARCHAR2(1);
2252 v_msg_count NUMBER;
2253 v_msg_data VARCHAR2(4000);
2254 v_global_vs_id NUMBER;
2255 v_num_root_rules NUMBER;
2256 v_num_rules_invalid NUMBER;
2257 v_all_rules_are_valid VARCHAR2(1);
2258 v_request_name FEM_BR_DIS_MBR_REQUESTS.request_name%TYPE;
2259 v_request_date DATE;
2260 --
2261 -- Get all root rules to be checked for disabled members
2262 CURSOR c_root_rules IS
2263 SELECT object_definition_id
2264 FROM fem_br_root_rules_gt;
2265 --
2266 BEGIN
2267 --
2268 IF FND_LOG.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2269 FEM_ENGINES_PKG.TECH_MESSAGE(
2270 p_severity => FND_LOG.level_procedure,
2271 p_module => C_MODULE,
2272 p_msg_text => 'Begin Procedure');
2273 END IF;
2274
2275 -- Log procedure param values
2276 IF FND_LOG.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2277 FEM_ENGINES_PKG.TECH_MESSAGE(
2278 p_severity => FND_LOG.level_statement,
2279 p_module => C_MODULE,
2280 p_msg_text => 'p_rule_type = '||p_rule_type);
2281 FEM_ENGINES_PKG.TECH_MESSAGE(
2282 p_severity => FND_LOG.level_statement,
2283 p_module => C_MODULE,
2284 p_msg_text => 'p_ledger_id = '||p_ledger_id);
2285 FEM_ENGINES_PKG.TECH_MESSAGE(
2286 p_severity => FND_LOG.level_statement,
2287 p_module => C_MODULE,
2288 p_msg_text => 'p_effective_date = '||p_effective_date);
2289 FEM_ENGINES_PKG.TECH_MESSAGE(
2290 p_severity => FND_LOG.level_statement,
2291 p_module => C_MODULE,
2292 p_msg_text => 'p_folder_id = '||p_folder_id);
2293 FEM_ENGINES_PKG.TECH_MESSAGE(
2294 p_severity => FND_LOG.level_statement,
2295 p_module => C_MODULE,
2296 p_msg_text => 'p_object_id = '||p_object_id);
2297 FEM_ENGINES_PKG.TECH_MESSAGE(
2298 p_severity => FND_LOG.level_statement,
2299 p_module => C_MODULE,
2300 p_msg_text => 'p_dim_id = '||p_dim_id);
2301 FEM_ENGINES_PKG.TECH_MESSAGE(
2302 p_severity => FND_LOG.level_statement,
2303 p_module => C_MODULE,
2304 p_msg_text => 'p_request_name = '||p_request_name);
2305 END IF; -- IF FND_LOG.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
2306
2307 -- Convert effective date from VARCHAR2 to DATE
2308 l_effective_date := FND_DATE.Canonical_To_Date(p_effective_date);
2309
2310 -- Get GVSC for ledger
2311 v_global_vs_id := FEM_DIMENSION_UTIL_PKG.Global_VS_Combo_ID(
2312 p_api_version => 1.0,
2313 x_return_status => v_return_status,
2314 x_msg_count => v_msg_count,
2315 x_msg_data => v_msg_data,
2316 p_ledger_id => p_ledger_id);
2317
2318 IF FND_LOG.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2319 FEM_ENGINES_PKG.TECH_MESSAGE(
2320 p_severity => FND_LOG.level_statement,
2321 p_module => C_MODULE,
2322 p_msg_text => 'v_global_vs_id = '||v_global_vs_id);
2323 END IF;
2324
2325 IF v_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2326 IF FND_LOG.level_unexpected >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2327 FEM_ENGINES_PKG.TECH_MESSAGE(
2328 p_severity => FND_LOG.level_unexpected,
2329 p_module => C_MODULE,
2330 p_msg_text => 'INTERNAL ERROR: Call to'
2331 ||' FEM_DIMENSION_UTIL_PKG.Global_VS_Combo_ID'
2332 ||' failed with return status: '||v_return_status);
2333 END IF;
2334
2335 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2336 END IF;
2337
2338 -- Find the set of root rules that needs to be checked for
2339 -- disabled dimension members and place them in FEM_BR_ROOT_RULES_GT.
2340 Find_Root_Rules(p_rule_type => p_rule_type,
2341 p_ledger_id => p_ledger_id,
2342 p_effective_date => l_effective_date,
2343 p_folder_id => p_folder_id,
2344 p_object_id => p_object_id,
2345 p_global_vs_id => v_global_vs_id);
2346
2347 -- Initialize vars
2348 v_num_rules_invalid := 0;
2349
2350 -- Loop through each root rule and validate it.
2351 FOR root_rules IN c_root_rules LOOP
2352 Validate_Root_Rule(
2353 p_obj_def_id => root_rules.object_definition_id,
2354 p_parent_report_row_id => NULL,
2355 p_effective_date => l_effective_date,
2356 p_global_vs_id => v_global_vs_id,
2357 p_param_dim_id => p_dim_id,
2358 x_all_rules_are_valid => v_all_rules_are_valid);
2359
2360 -- If any of the root root rules are invalid, record that fact.
2361 IF v_all_rules_are_valid = 'N' THEN
2362 v_num_rules_invalid := v_num_rules_invalid + 1;
2363 END IF;
2364
2365 v_num_root_rules := c_root_rules%ROWCOUNT;
2366 END LOOP;
2367
2368 -- If at least one root rule was invalid,
2369 -- 1. Finish populating denormalized dimension information in
2370 -- FEM_BR_DIS_MBR_CONTEXTS
2371 -- 2. Populate FEM_BR_DIS_MBR_REQUESTS
2372 IF (v_num_rules_invalid > 0) THEN
2373 -- First, populate missing dimension info in FEM_BR_DIS_MBR_CONTEXTS
2374 Populate_Dim_Info;
2375
2376 -- Put request date into var for consistency
2377 v_request_date := sysdate;
2378
2379 IF p_request_name IS NULL THEN
2380 v_request_name := FND_GLOBAL.User_Name || ' '
2381 || FND_DATE.Date_To_DisplayDT(v_request_date);
2382 ELSE
2383 v_request_name := p_request_name;
2384 END IF;
2385
2386 INSERT INTO fem_br_dis_mbr_requests(
2387 request_id, request_date, request_name,
2388 object_type_code, object_type_name,
2389 ledger_id, ledger_name,
2390 effective_date, object_id, object_name,
2391 folder_id, folder_name,
2392 dimension_id, dimension_name,
2393 creation_date, created_by,
2394 last_updated_by, last_update_date, last_update_login)
2395 VALUES(
2396 FND_GLOBAL.Conc_Request_Id, v_request_date, v_request_name,
2397 p_rule_type, (SELECT object_type_name
2398 FROM fem_object_types_vl
2399 WHERE object_type_code = p_rule_type),
2400 p_ledger_id, (SELECT ledger_name
2401 FROM fem_ledgers_vl
2402 WHERE ledger_id = p_ledger_id),
2403 l_effective_date, p_object_id, (SELECT object_name
2404 FROM fem_object_catalog_vl
2405 WHERE object_id = p_object_id),
2406 p_folder_id, (SELECT folder_name
2407 FROM fem_folders_vl
2408 WHERE folder_id = p_folder_id),
2409 p_dim_id, (SELECT dimension_name
2410 FROM fem_dimensions_b
2411 WHERE dimension_id = p_dim_id),
2412 v_request_date, FND_GLOBAL.User_ID,
2413 FND_GLOBAL.User_ID, v_request_date, FND_GLOBAL.Login_ID);
2414 END IF; -- IF (v_num_rules_invalid > 0)
2415
2416 -- If there are any messages on the stack, post them to concurrent log.
2417 Get_Put_Messages;
2418
2419 -- Post some summary statistics on number of root rules processed
2420 -- and invalid.
2424 p_token1 => 'NUM',
2421 FEM_ENGINES_PKG.USER_MESSAGE(
2422 p_app_name =>'FEM',
2423 p_msg_name => 'FEM_BR_DIS_MBR_NUM_ROOT_RULES',
2425 p_value1 => v_num_root_rules);
2426
2427 FEM_ENGINES_PKG.USER_MESSAGE(
2428 p_app_name =>'FEM',
2429 p_msg_name => 'FEM_BR_DIS_MBR_NUM_INVALID',
2430 p_token1 => 'NUM',
2431 p_value1 => v_num_rules_invalid);
2432
2433 COMMIT;
2434
2435 -- Set concurrent status to success (0)
2436 retcode := 0;
2437
2438 IF FND_LOG.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2439 FEM_ENGINES_PKG.TECH_MESSAGE(
2440 p_severity => FND_LOG.level_statement,
2441 p_module => C_MODULE,
2442 p_msg_text => 'retcode = '||retcode);
2443 END IF;
2444 IF FND_LOG.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2445 FEM_ENGINES_PKG.TECH_MESSAGE(
2446 p_severity => FND_LOG.level_procedure,
2447 p_module => C_MODULE,
2448 p_msg_text => 'End Procedure');
2449 END IF;
2450 --
2451 EXCEPTION
2452 --
2453 WHEN others THEN
2454 -- Set concurrent status to error (2)
2455 retcode := 2;
2456
2457 -- If there are any messages on the stack, post them to concurrent log.
2458 Get_Put_Messages;
2459
2460 IF FND_LOG.level_unexpected >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2461 FEM_ENGINES_PKG.TECH_MESSAGE(
2462 p_severity => FND_LOG.level_unexpected,
2463 p_module => C_MODULE,
2464 p_msg_text => 'Unexpected error: '||SQLERRM);
2465 END IF;
2466
2467 -- Log the Oracle error message to the stack.
2468 FEM_ENGINES_PKG.USER_MESSAGE(
2469 p_app_name =>'FEM',
2470 p_msg_name => 'FEM_UNEXPECTED_ERROR',
2471 p_token1 => 'ERR_MSG',
2472 p_value1 => SQLERRM);
2473
2474 ROLLBACK;
2475 --
2476 END Report_Invalid_Rules;
2477 -------------------------------------------------------------------------------
2478
2479 -------------------------------------------------------------------------------
2480 PROCEDURE Purge_Report_Data(errbuf OUT NOCOPY VARCHAR2,
2481 retcode OUT NOCOPY VARCHAR2,
2482 p_execution_start_date IN VARCHAR2,
2483 p_execution_end_date IN VARCHAR2,
2484 p_request_id IN NUMBER) IS
2485
2486 C_MODULE CONSTANT FND_LOG_MESSAGES.module%TYPE :=
2487 'fem.plsql.fem_check_br_dis_mbrs_pkg.purge_report_data';
2488 l_start_date DATE;
2489 l_end_date DATE;
2490
2491 BEGIN
2492
2493 SAVEPOINT Purge_Report_Data;
2494 retcode := 0;
2495
2496 l_start_date := fnd_date.canonical_to_date(p_execution_start_date);
2497 l_end_date := fnd_date.canonical_to_date(p_execution_end_date);
2498
2499 --If request id is provided, ignore the other params and delete
2500 --the specific request from all the three tables.
2501
2502 IF(p_request_id is not null) THEN
2503
2504 DELETE FROM FEM_BR_DIS_MBR_CONTEXTS WHERE REQUEST_ID = p_request_id;
2505 DELETE FROM FEM_BR_DIS_MBR_HIER WHERE REQUEST_ID = p_request_id;
2506 DELETE FROM FEM_BR_DIS_MBR_REQUESTS WHERE REQUEST_ID = p_request_id;
2507
2508
2509 ELSIF(l_start_date IS NOT NULL AND l_end_date IS NOT NULL) THEN
2510
2511 DELETE FROM FEM_BR_DIS_MBR_CONTEXTS WHERE REQUEST_ID IN (SELECT REQUEST_ID FROM
2512 FEM_BR_DIS_MBR_REQUESTS WHERE REQUEST_DATE BETWEEN l_start_date AND
2513 l_end_date);
2514
2515 DELETE FROM FEM_BR_DIS_MBR_HIER WHERE REQUEST_ID IN (SELECT REQUEST_ID FROM
2516 FEM_BR_DIS_MBR_REQUESTS WHERE REQUEST_DATE BETWEEN l_start_date AND
2517 l_end_date);
2518
2519 DELETE FROM FEM_BR_DIS_MBR_REQUESTS WHERE REQUEST_DATE BETWEEN l_start_date AND
2520 l_end_date;
2521
2522 ELSIF(l_start_date IS NOT NULL AND l_end_date IS NULL) THEN
2523
2524 DELETE FROM FEM_BR_DIS_MBR_CONTEXTS WHERE REQUEST_ID IN (SELECT REQUEST_ID FROM
2525 FEM_BR_DIS_MBR_REQUESTS WHERE REQUEST_DATE > l_start_date);
2526
2527 DELETE FROM FEM_BR_DIS_MBR_HIER WHERE REQUEST_ID IN (SELECT REQUEST_ID FROM
2528 FEM_BR_DIS_MBR_REQUESTS WHERE REQUEST_DATE > l_start_date);
2529
2530 DELETE FROM FEM_BR_DIS_MBR_REQUESTS WHERE REQUEST_DATE > l_start_date;
2531
2532 ELSIF(l_start_date IS NULL AND l_end_date IS NOT NULL) THEN
2533
2534 DELETE FROM FEM_BR_DIS_MBR_CONTEXTS WHERE REQUEST_ID IN (SELECT REQUEST_ID FROM
2535 FEM_BR_DIS_MBR_REQUESTS WHERE REQUEST_DATE < l_end_date);
2536
2537 DELETE FROM FEM_BR_DIS_MBR_HIER WHERE REQUEST_ID IN (SELECT REQUEST_ID FROM
2538 FEM_BR_DIS_MBR_REQUESTS WHERE REQUEST_DATE < l_end_date);
2539
2540 DELETE FROM FEM_BR_DIS_MBR_REQUESTS WHERE REQUEST_DATE < l_end_date;
2541
2542 ELSE -- Delete all :-(
2543
2544 DELETE FROM FEM_BR_DIS_MBR_CONTEXTS;
2545 DELETE FROM FEM_BR_DIS_MBR_HIER;
2546 DELETE FROM FEM_BR_DIS_MBR_REQUESTS;
2547
2548 END IF;
2549
2550 COMMIT;
2551
2552 EXCEPTION
2553 WHEN OTHERS THEN
2554 --
2555 ROLLBACK TO Purge_Report_Data;
2556 --
2557
2558 retcode := 2;
2559 fem_engines_pkg.tech_message (p_severity => FND_LOG.level_unexpected
2560 ,p_module => C_MODULE
2561 ,p_msg_text => 'EXCEPTION in Purge_Report_Data: ' || sqlerrm);
2562 fnd_file.put_line(fnd_file.log, 'EXCEPTION in Purge_Report_Data: ' || sqlerrm);
2563
2564 END Purge_Report_Data;
2565 ------------------------------------------------------------------------------
2566
2567
2568 END FEM_CHECK_BR_DIS_MBRS_PKG;