[Home] [Help]
PACKAGE BODY: APPS.FEM_ADMIN_UTIL_PKG
Source
1 PACKAGE BODY FEM_Admin_Util_Pkg AS
2 -- $Header: fem_adm_utl.plb 120.18 2008/02/06 23:15:26 ghall ship $
3
4 -------------------------------
5 -- Declare Package Variables --
6 -------------------------------
7
8 c_user_id CONSTANT NUMBER := FND_GLOBAL.USER_ID;
9 c_success CONSTANT VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
10 c_error CONSTANT VARCHAR2(1) := FND_API.G_RET_STS_ERROR;
11 c_unexp CONSTANT VARCHAR2(1) := FND_API.G_RET_STS_UNEXP_ERROR;
12 G_PKG_NAME CONSTANT VARCHAR2(30) := 'FEM_ADMIN_UTIL_PKG';
13
14 bad_gvsc_id EXCEPTION;
15 e_unexp EXCEPTION;
16 e_error EXCEPTION;
17
18 --------------------------------------------------------------
19 -- Private Procedures
20 --------------------------------------------------------------
21
22 -- "Table handler" procedures for the Table Classification
23 -- Logging global temporary tables.
24
25 PROCEDURE Trunc_Table_Class_Log_Tables (
26 p_tab_name IN VARCHAR2) IS
27 C_MODULE CONSTANT FND_LOG_MESSAGES.module%TYPE :=
28 'fem.plsql.fem_admin_util_pkg.trunc_table_class_log_tables';
29 BEGIN
30
31 BEGIN
32 DELETE FROM fem_tab_class_status_gt
33 WHERE table_name = p_tab_name;
34 EXCEPTION
35 WHEN others THEN
36 IF FND_LOG.level_exception >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
37 FEM_ENGINES_PKG.TECH_MESSAGE(
38 p_severity => FND_LOG.level_exception,
39 p_module => C_MODULE,
40 p_msg_text => 'Delete from FEM_TAB_CLASS_STATUS_GT failed with: '||SQLERRM);
41 END IF;
42 END;
43
44 BEGIN
45 DELETE FROM fem_tab_class_errors_gt
46 WHERE table_name = p_tab_name;
47 EXCEPTION
48 WHEN others THEN
49 IF FND_LOG.level_exception >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
50 FEM_ENGINES_PKG.TECH_MESSAGE(
51 p_severity => FND_LOG.level_exception,
52 p_module => C_MODULE,
53 p_msg_text => 'Delete from FEM_TAB_CLASS_ERRORS_GT failed with: '||SQLERRM);
54 END IF;
55 END;
56
57 END Trunc_Table_Class_Log_Tables;
58
59
60 PROCEDURE Log_Table_Class_Error (
61 p_tab_name IN VARCHAR2,
62 p_tab_class_cd IN VARCHAR2,
63 p_msg_name IN VARCHAR2 DEFAULT NULL,
64 p_msg_count IN NUMBER DEFAULT NULL,
65 p_token1 IN VARCHAR2 DEFAULT NULL,
66 p_value1 IN VARCHAR2 DEFAULT NULL,
67 p_token2 IN VARCHAR2 DEFAULT NULL,
68 p_value2 IN VARCHAR2 DEFAULT NULL,
69 p_token3 IN VARCHAR2 DEFAULT NULL,
70 p_value3 IN VARCHAR2 DEFAULT NULL,
71 p_token4 IN VARCHAR2 DEFAULT NULL,
72 p_value4 IN VARCHAR2 DEFAULT NULL
73 ) IS
74 C_MODULE CONSTANT FND_LOG_MESSAGES.module%TYPE :=
75 'fem.plsql.fem_admin_util_pkg.log_table_class_error';
76
77 TYPE msg_array IS VARRAY(8) OF VARCHAR2(2000);
78 tokens_values msg_array;
79
80 v_token VARCHAR2(30);
81 v_value VARCHAR2(2000);
82 v_msg_data VARCHAR2(2000);
83 v_dummy NUMBER;
84 BEGIN
85
86 IF p_msg_count IS NOT NULL THEN
87 FOR i IN 1..p_msg_count LOOP
88 FND_MSG_PUB.Get(
89 p_msg_index => i,
90 p_encoded => c_false,
91 p_data => v_msg_data,
92 p_msg_index_out => v_dummy);
93
94 INSERT INTO fem_tab_class_errors_gt
95 (TABLE_NAME, TABLE_CLASSIFICATION_CODE, MESSAGE_TEXT)
96 VALUES
97 (p_tab_name, p_tab_class_cd, nvl(v_msg_data,'?'));
98 END LOOP;
99
100 FND_MSG_PUB.initialize;
101 ELSE
102 -- Get message from dictionary
103 fnd_message.set_name('FEM',p_msg_name);
104
105 -- Load token/value array
106 tokens_values := msg_array
107 (p_token1,p_value1,
108 p_token2,p_value2,
109 p_token3,p_value3,
110 p_token4,p_value4);
111
112 -- Substitute values for tokens
113 FOR i IN 1..8 LOOP
114 IF (MOD(i,2) = 1) THEN
115 v_token := tokens_values(i);
116 IF (v_token IS NOT NULL) THEN
117 v_value := tokens_values(i+1);
118 fnd_message.set_token(v_token,v_value);
119 ELSE
120 EXIT;
121 END IF;
122 END IF;
123 END LOOP;
124
125 v_msg_data := FND_MESSAGE.Get;
126
127 INSERT INTO fem_tab_class_errors_gt
128 (TABLE_NAME, TABLE_CLASSIFICATION_CODE, MESSAGE_TEXT)
129 VALUES
130 (p_tab_name, p_tab_class_cd, nvl(v_msg_data,'?'));
131
132 END IF; -- p_msg_txt IS NOT NULL
133
134 EXCEPTION
135 WHEN others THEN
136 IF FND_LOG.level_exception >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
137 FEM_ENGINES_PKG.TECH_MESSAGE(
138 p_severity => FND_LOG.level_exception,
139 p_module => C_MODULE,
140 p_msg_text => 'Insert into FEM_TAB_CLASS_ERRORS_GT failed with: '||SQLERRM);
141 END IF;
142 END Log_Table_Class_Error;
143
144
145 PROCEDURE Log_Table_Class_Status (
146 p_tab_name IN VARCHAR2,
147 p_tab_class_cd IN VARCHAR2,
148 p_passed_validation IN VARCHAR2) IS
149 C_MODULE CONSTANT FND_LOG_MESSAGES.module%TYPE :=
150 'fem.plsql.fem_admin_util_pkg.log_table_class_status';
151 BEGIN
152
153 INSERT INTO fem_tab_class_status_gt
154 (TABLE_NAME, TABLE_CLASSIFICATION_CODE, VALID_FLAG)
155 VALUES
156 (p_tab_name, p_tab_class_cd, decode(p_passed_validation,c_true,'Y','N'));
157
158 EXCEPTION
159 WHEN others THEN
160 IF FND_LOG.level_exception >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
161 FEM_ENGINES_PKG.TECH_MESSAGE(
162 p_severity => FND_LOG.level_exception,
163 p_module => C_MODULE,
164 p_msg_text => 'Insert into FEM_TAB_CLASS_STATUS_GT failed with: '||SQLERRM);
165 END IF;
166 END Log_Table_Class_Status;
167
168 ------------------------------------
169 -- Bug 4534907. Short term solution to create the extra metadata necessary
170 -- for a table to be supported by the Detail Client Data Loader program.
171 -- This metadata consists of:
172 -- * Rule and rule definintion for the table (FEM_OBJECT_CATALOG_VL,
173 -- FEM_OBJECT_DEFINITION_VL)
174 -- * Multiprocessing options for the new rule (FEM_MP_PROCESS_OPTIONS)
175 -- * Link between the new rule and client data table (FEM_DATA_LOADER_OBJECTS)
176
177 -- This procedure enables user-defined tables that qualify as a
178 -- clieant data loader table to be supported by the loader.
179 ------------------------------------
180
181 FUNCTION Create_Data_Loader_Rule (
182 p_tab_name IN VARCHAR2,
183 p_tab_class_cd IN VARCHAR2
184 ) RETURN VARCHAR2
185 AS
186 C_MODULE CONSTANT FND_LOG_MESSAGES.module%TYPE :=
187 'fem.plsql.fem_admin_util_pkg.create_data_loader_rule';
188
189 v_count INTEGER;
190 v_obj_name FEM_OBJECT_CATALOG_TL.object_name%TYPE;
191 v_obj_id FEM_OBJECT_CATALOG_B.object_id%TYPE;
192 v_obj_def_id FEM_OBJECT_DEFINITION_B.object_id%TYPE;
193 v_tab_disp_name FEM_TABLES_TL.display_name%TYPE;
194 v_msg_count NUMBER;
195 v_msg_data VARCHAR2(4000);
196 v_return_status VARCHAR2(1);
197 BEGIN
198
199 SAVEPOINT create_data_loader_rule_pub;
200
201 IF FND_LOG.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
202 FEM_ENGINES_PKG.TECH_MESSAGE(
203 p_severity => FND_LOG.level_procedure,
204 p_module => C_MODULE,
205 p_msg_text => 'Begin Procedure');
206 END IF;
207
208 SELECT count(*)
209 INTO v_count
210 FROM fem_data_loader_objects
211 WHERE table_name = p_tab_name;
212
213 IF v_count = 0 THEN
214 BEGIN
215 SELECT display_name
216 INTO v_tab_disp_name
217 FROM fem_tables_vl
218 WHERE table_name = p_tab_name;
219 EXCEPTION WHEN others THEN null;
220 END;
221 FND_MESSAGE.Set_Name('FEM','FEM_SD_LDR_OBJECT_NAME_TXT');
222 FND_MESSAGE.Set_Token('TAB_DISP_NAME',nvl(v_tab_disp_name,p_tab_name));
223 FND_MESSAGE.Set_Token('TAB_NAME',p_tab_name);
224 v_obj_name := substr(FND_MESSAGE.Get,1,150);
225
226 fem_object_catalog_util_pkg.create_object(p_api_version => 1.0,
227 p_commit => c_false,
228 p_object_type_code => 'SOURCE_DATA_LOADER',
229 p_folder_id => 1000, -- hardcoded to Data Intg folder
230 p_local_vs_combo_id => NULL,
231 p_object_access_code => 'R',
232 p_object_origin_code => 'USER',
233 p_object_name => v_obj_name,
234 p_description => v_obj_name,
235 p_effective_start_date => sysdate,
236 p_effective_end_date => to_date('9999/01/01','YYYY/MM/DD'),
237 p_obj_def_name => v_obj_name,
238 x_object_id => v_obj_id,
239 x_object_definition_id => v_obj_def_id,
240 x_msg_count => v_msg_count,
241 x_msg_data => v_msg_data,
242 x_return_status => v_return_status);
243
244 IF v_return_status <> c_success THEN
245 IF FND_LOG.level_error >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
246 FEM_ENGINES_PKG.TECH_MESSAGE(
247 p_severity => FND_LOG.level_error,
248 p_module => C_MODULE,
249 p_msg_text => 'Call to fem_object_catalog_util_pkg.create_object '
250 ||'did not return successfully: status = '
251 ||v_return_status);
252 END IF;
253
254 IF v_return_status = c_unexp OR nvl(v_msg_count,0) = 0 THEN
255 ROLLBACK TO create_data_loader_rule_pub;
256 RAISE e_unexp;
257 END IF;
258
259 Log_Table_Class_Error(
260 p_tab_name => p_tab_name,
261 p_tab_class_cd => p_tab_class_cd,
262 p_msg_count => v_msg_count);
263
264 RETURN c_false;
265 ELSE
266 IF FND_LOG.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
267 FEM_ENGINES_PKG.TECH_MESSAGE(
268 p_severity => FND_LOG.level_statement,
269 p_module => C_MODULE,
270 p_msg_text => 'New loader object id = '||v_obj_id
271 ||' , object def id = '||v_obj_def_id);
272 END IF;
273
274 INSERT INTO fem_mp_process_options (
275 OBJECT_TYPE_CODE,
276 STEP_NAME,
277 OBJECT_ID,
278 DATA_SLICE_TYPE_CODE,
279 PROCESS_DATA_SLICES_CD,
280 PROCESS_PARTITION_CD,
281 NUM_OF_PROCESSES,
282 CREATED_BY,
283 CREATION_DATE,
284 LAST_UPDATED_BY,
285 LAST_UPDATE_DATE,
286 LAST_UPDATE_LOGIN,
287 OBJECT_VERSION_NUMBER )
288 VALUES (
289 'SOURCE_DATA_LOADER',
290 'ALL',
291 v_obj_id,
292 2,
293 1000,
294 0,
295 1,
296 c_user_id,
297 sysdate,
298 c_user_id,
299 sysdate,
300 FND_GLOBAL.Login_Id,
301 1 );
302
303 INSERT INTO fem_data_loader_objects (
304 OBJECT_ID,
305 TABLE_NAME,
306 CREATED_BY,
307 CREATION_DATE,
308 LAST_UPDATED_BY,
309 LAST_UPDATE_DATE,
310 LAST_UPDATE_LOGIN )
311 VALUES (
312 v_obj_id,
313 p_tab_name,
314 c_user_id,
315 sysdate,
316 c_user_id,
317 sysdate,
318 FND_GLOBAL.Login_Id );
319
320 END IF;
321 END IF; -- v_count = 0
322
323 IF FND_LOG.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
324 FEM_ENGINES_PKG.TECH_MESSAGE(
325 p_severity => FND_LOG.level_procedure,
326 p_module => C_MODULE,
327 p_msg_text => 'End Procedure');
328 END IF;
329
330 RETURN c_true;
331
332 EXCEPTION
333 WHEN others THEN
334 IF FND_LOG.level_exception >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
335 FEM_ENGINES_PKG.TECH_MESSAGE(
336 p_severity => FND_LOG.level_exception,
337 p_module => C_MODULE,
338 p_msg_text => 'Create_Data_Loader_Rule failed unexpectedly: '||SQLERRM);
339 END IF;
340 Log_Table_Class_Error(
341 p_tab_name => p_tab_name,
342 p_tab_class_cd => p_tab_class_cd,
343 p_msg_name => 'FEM_RSM_UNEXPECTED_ERROR',
344 p_token1 => 'ROUTINE_NAME',
345 p_value1 => C_MODULE);
346
347 ROLLBACK TO create_data_loader_rule_pub;
348 RETURN c_false;
349 END Create_Data_Loader_Rule;
350
351
352 -- Validate_Prop_Col_Req:
353 -- Procedure to validate the column requirements a
354 -- table property may have.
355
356 PROCEDURE Validate_Prop_Col_Req (
357 p_tab_name IN VARCHAR2,
358 p_tab_class_cd IN VARCHAR2,
359 p_tab_prop_cd IN VARCHAR2,
360 p_prop_type IN VARCHAR2,
361 p_col_req_type IN VARCHAR2,
362 x_passed_validation OUT NOCOPY VARCHAR2
363 ) IS
364 C_MODULE CONSTANT FND_LOG_MESSAGES.module%TYPE :=
365 'fem.plsql.fem_admin_util_pkg.validate_prop_col_req';
366
367 TYPE CurTyp IS REF CURSOR;
368 cols_cv CurTyp;
369
370 -- Table Property required columns
371 CURSOR c_req_cols IS
372 SELECT column_name
373 FROM fem_table_prop_col_req
374 WHERE table_property_code = p_tab_prop_cd;
375
376 v_sql VARCHAR2(4000);
377 v_col_list VARCHAR2(2000);
378 v_col_name ALL_TAB_COLUMNS.column_name%TYPE;
379 v_obj_name FND_LOOKUP_VALUES.meaning%TYPE;
380 v_col_prop_cd FEM_TAB_COLUMN_PROP.column_property_code%TYPE;
381 v_msg_name FND_NEW_MESSAGES.message_name%TYPE;
382 v_vsr_flag FEM_XDIM_DIMENSIONS.value_set_required_flag%TYPE;
383 v_count NUMBER;
384 v_vsr_count NUMBER;
385 v_passed_valid VARCHAR2(1);
386 BEGIN
387 IF FND_LOG.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
388 FEM_ENGINES_PKG.TECH_MESSAGE(
389 p_severity => FND_LOG.level_procedure,
390 p_module => C_MODULE,
391 p_msg_text => 'Begin Procedure');
392 END IF;
393 IF FND_LOG.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
394 FEM_ENGINES_PKG.TECH_MESSAGE(
395 p_severity => FND_LOG.level_statement,
396 p_module => C_MODULE,
397 p_msg_text => 'p_tab_name = '||p_tab_name);
398 FEM_ENGINES_PKG.TECH_MESSAGE(
399 p_severity => FND_LOG.level_statement,
400 p_module => C_MODULE,
401 p_msg_text => 'p_tab_class_cd = '||p_tab_class_cd);
402 FEM_ENGINES_PKG.TECH_MESSAGE(
403 p_severity => FND_LOG.level_statement,
404 p_module => C_MODULE,
405 p_msg_text => 'p_tab_prop_cd = '||p_tab_prop_cd);
409 p_msg_text => 'p_prop_type = '||p_prop_type);
406 FEM_ENGINES_PKG.TECH_MESSAGE(
407 p_severity => FND_LOG.level_statement,
408 p_module => C_MODULE,
410 FEM_ENGINES_PKG.TECH_MESSAGE(
411 p_severity => FND_LOG.level_statement,
412 p_module => C_MODULE,
413 p_msg_text => 'p_col_req_type = '||p_col_req_type);
414 END IF;
415
416 -- Init var
417 v_passed_valid := c_true;
418 -- Hard-coding this for now - may change if validation
419 -- gets extended to other "column properties"
420 v_col_prop_cd := 'PROCESSING_KEY';
421
422 -- Object that is being validated against
423 SELECT meaning
424 INTO v_obj_name
425 FROM fnd_lookup_values_vl
426 WHERE lookup_type = 'FEM_TAB_CLASS_OBJECTS'
427 AND lookup_code = p_prop_type;
428
429 -- First, test for the case where a fixed column set is required.
430 IF substr(p_col_req_type,1,5) = 'FIXED' THEN
431 v_sql := 'SELECT column_name'
432 ||' FROM fem_table_prop_col_req'
433 ||' WHERE table_property_code = :1 ';
434 IF p_prop_type = 'TABLE_COLUMN' THEN
435 v_sql := v_sql||'AND column_name NOT IN '
436 ||'(SELECT column_name'
437 ||' FROM fem_tab_columns_b'
438 ||' WHERE table_name = :2'
439 ||' AND enabled_flag = ''Y'')';
440 ELSE
441 v_sql := v_sql||'AND column_name NOT IN '
442 ||'(SELECT column_name'
443 ||' FROM fem_tab_column_prop'
444 ||' WHERE table_name = :2'
445 ||' AND column_property_code = '''||v_col_prop_cd||''')';
446 END IF; -- p_prop_type
447
448 IF FND_LOG.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
449 FEM_ENGINES_PKG.TECH_MESSAGE(
450 p_severity => FND_LOG.level_statement,
451 p_module => C_MODULE,
452 p_msg_text => 'v_sql(10) = '||v_sql);
453 END IF;
454
455 v_count := 0;
456 OPEN cols_cv FOR v_sql USING p_tab_prop_cd,p_tab_name;
457 LOOP
458 FETCH cols_cv INTO v_col_name;
459 EXIT WHEN cols_cv%NOTFOUND;
460 IF v_count = 0 THEN
461 v_col_list := v_col_name;
462 ELSE
463 v_col_list := v_col_list||', '||v_col_name;
464 END IF;
465 v_count := v_count+1;
466 END LOOP;
467 CLOSE cols_cv;
468
469 IF v_count > 0 THEN
470 Log_Table_Class_Error(
471 p_tab_name => p_tab_name,
472 p_tab_class_cd => p_tab_class_cd,
473 p_msg_name => 'FEM_TABCLASS_MISS_REQ_COL',
474 p_token1 => 'OBJECT',
475 p_value1 => v_obj_name,
476 p_token2 => 'COL_LIST',
477 p_value2 => v_col_list);
478 v_passed_valid := c_false;
479 END IF;
480
481 ELSIF p_col_req_type = 'ONE_ANY' THEN
482 v_sql := 'SELECT count(*)';
483 IF p_prop_type = 'TABLE_COLUMN' THEN
484 v_sql := v_sql||' FROM fem_tab_columns_b'
485 ||' WHERE table_name = :1'
486 ||' AND enabled_flag = ''Y''';
487 ELSE
488 v_sql := v_sql||' FROM fem_tab_column_prop'
489 ||' WHERE table_name = :1'
490 ||' AND column_property_code = '''||v_col_prop_cd||'''';
491 END IF; -- p_prop_type
492
493 IF FND_LOG.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
494 FEM_ENGINES_PKG.TECH_MESSAGE(
495 p_severity => FND_LOG.level_statement,
496 p_module => C_MODULE,
497 p_msg_text => 'v_sql(20) = '||v_sql);
498 END IF;
499
500 EXECUTE IMMEDIATE v_sql INTO v_count USING p_tab_name;
501 IF v_count = 0 THEN
502 Log_Table_Class_Error(
503 p_tab_name => p_tab_name,
504 p_tab_class_cd => p_tab_class_cd,
505 p_msg_name => 'FEM_TABCLASS_NO_COLS',
506 p_token1 => 'OBJECT',
507 p_value1 => v_obj_name);
508 v_passed_valid := c_false;
509 END IF;
510 END IF; -- substr(p_col_req_type,1,5) = 'FIXED'
511
512 -- Continue validation only if previous validation succeeded.
513 -- If yes, then for Column Req Type other than FIXED_ANY and ONE_ANY,
514 -- validate the columns defined outside the set of
515 -- columns defined in FEM_TABLE_PROP_COL_REQ.
516 IF v_passed_valid = c_true AND
517 p_col_req_type NOT IN ('FIXED_ANY','ONE_ANY') THEN
518
519 v_sql := 'SELECT t.column_name, nvl(x.value_set_required_flag,''N'')'
520 ||' FROM fem_tab_columns_b t, fem_xdim_dimensions x'
521 ||' WHERE t.dimension_id = x.dimension_id(+)'
522 ||' AND t.table_name = :1'
523 ||' AND enabled_flag = ''Y'''
524 ||' AND t.column_name NOT IN '
525 ||'(SELECT column_name'
526 ||' FROM fem_table_prop_col_req'
527 ||' WHERE table_property_code = :2) ';
528 IF p_prop_type <> 'TABLE_COLUMN' THEN
529 v_sql := v_sql||'AND t.column_name IN '
530 ||'(SELECT column_name'
531 ||' FROM fem_tab_column_prop'
532 ||' WHERE table_name = '''||p_tab_name||''''
533 ||' AND column_property_code = '''||v_col_prop_cd||''')';
534 END IF;
538 v_sql := v_sql||' AND nvl(x.value_set_required_flag,''N'') = ''N''';
535 IF p_col_req_type = 'FIXED_AND_ONE_NUMBER' THEN
536 v_sql := v_sql||' AND t.fem_data_type_code = ''NUMBER''';
537 ELSIF p_col_req_type = 'FIXED_AND_ANY_VSR_DIM_COL' THEN
539 END IF;
540
541 IF FND_LOG.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
542 FEM_ENGINES_PKG.TECH_MESSAGE(
543 p_severity => FND_LOG.level_statement,
544 p_module => C_MODULE,
545 p_msg_text => 'v_sql(30) = '||v_sql);
546 END IF;
547
548 v_count := 0;
549 v_vsr_count := 0;
550 v_col_list := NULL;
551 OPEN cols_cv FOR v_sql USING p_tab_name, p_tab_prop_cd;
552 LOOP
553 FETCH cols_cv INTO v_col_name, v_vsr_flag;
554 EXIT WHEN cols_cv%NOTFOUND;
555 v_count := v_count + 1;
556
557 -- For Column Req Type of FIXED, there should be no
558 -- other columns defined outside the set of required columns.
559 -- Exit loop once one is found.
560 -- For Column Req Type of FIXED_AND_ONE_NUMBER and OTHER_THAN,
561 -- just need to verify at least one exists. Exit loop
562 -- once one is found.
563 EXIT WHEN (p_col_req_type IN ('FIXED',
564 'FIXED_AND_ONE_NUMBER',
565 'OTHER_THAN'));
566
567 -- For Column Req Type of FIXED_AND_ONE_VSR_DIM_COL,
568 -- need to make sure that at least one VSR column is
569 -- defined, outside the required columns
570 IF p_col_req_type = 'FIXED_AND_ONE_VSR_DIM_COL' THEN
571 IF v_vsr_flag = 'Y' THEN
572 v_vsr_count := 1;
573 END IF;
574 END IF;
575
576 -- For Column Req Type of FIXED_AND_ONE_VSR_DIM_COL and
577 -- FIXED_AND_ANY_VSR_DIM_COL, need to make sure that all columns
578 -- outside the required columns are VSR dimension columns.
579 IF p_col_req_type IN ('FIXED_AND_ONE_VSR_DIM_COL',
580 'FIXED_AND_ANY_VSR_DIM_COL') THEN
581 IF v_vsr_flag = 'N' THEN
582 -- Bug 4645761: For LEDGER_PK property, CURRENCY_TYPE_CODE column
583 -- is an exception in that it is not required but is also not a
584 -- VSR dimension column. To resolve this in the short term,
585 -- we will just mark as error if the non-VSR column is
586 -- CURRENCY_TYPE_CODE. Remove this code once the long term
587 -- solution (bug 4669790) is implemented.
588
589 -- Start bug code --
590 IF p_tab_prop_cd = 'LEDGER_PK'
591 AND v_col_name = 'CURRENCY_TYPE_CODE' THEN
592 null;
593 -- End bug code --
594
595 ELSIF v_col_list IS NULL THEN
596 v_col_list := v_col_name;
597 ELSE
598 v_col_list := v_col_list||', '||v_col_name;
599 END IF;
600 END IF;
601 END IF; -- p_col_req_type
602 END LOOP;
603 CLOSE cols_cv;
604
605 IF FND_LOG.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
606 FEM_ENGINES_PKG.TECH_MESSAGE(
607 p_severity => FND_LOG.level_statement,
608 p_module => C_MODULE,
609 p_msg_text => 'v_count = '||v_count||'; v_vsr_count = '||v_vsr_count);
610 END IF;
611
612 -- For Column Req Type of FIXED_AND_ONE_VSR_DIM_COL and
613 -- FIXED_AND_ANY_VSR_DIM_COL, see if any non-VSR dim cols exist
614 IF p_col_req_type IN ('FIXED_AND_ONE_VSR_DIM_COL',
615 'FIXED_AND_ANY_VSR_DIM_COL') AND
616 v_col_list IS NOT NULL THEN
617 Log_Table_Class_Error(
618 p_tab_name => p_tab_name,
619 p_tab_class_cd => p_tab_class_cd,
620 p_msg_name => 'FEM_TABCLASS_NOT_VSR_DIM_COL',
621 p_token1 => 'OBJECT',
622 p_value1 => v_obj_name,
623 p_token2 => 'COL_LIST',
624 p_value2 => v_col_list);
625 v_passed_valid := c_false;
626 END IF;
627
628 v_msg_name := NULL;
629 IF v_vsr_count = 0 AND
630 p_col_req_type = 'FIXED_AND_ONE_VSR_DIM_COL' THEN
631 v_msg_name := 'FEM_TABCLASS_MISS_VSRDIM_COL';
632 ELSIF v_count = 0 THEN
633 -- For Column Req Type of OTHER_THAN or FIXED_AND_ONE%,
634 -- there were no column defined outside
635 -- the set of "other than" or "required" columns
636 IF p_col_req_type = 'OTHER_THAN' THEN
637 v_msg_name := 'FEM_TABCLASS_MISS_OTHER_COL';
638 ELSIF p_col_req_type = 'FIXED_AND_ONE_NUMBER' THEN
639 v_msg_name := 'FEM_TABCLASS_MISS_NUMBER_COL';
640 END IF; -- p_col_req_type
641 ELSE -- v_count > 0
642 -- For Column Req Type of FIXED, there were other
643 -- columns defined outside the set of required columns.
644 IF p_col_req_type = 'FIXED' THEN
645 v_msg_name := 'FEM_TABCLASS_EXTRA_COLS';
646 END IF;
647 END IF; -- v_count = 0
648
649 IF v_msg_name IS NOT NULL THEN
650 -- Generate list of required (or excluded) columns
651 -- only if the list has not already been generated.
652 v_count := 0;
653 FOR req_cols IN c_req_cols LOOP
654 IF v_count = 0 THEN
655 v_col_list := req_cols.column_name;
656 ELSE
657 v_col_list := v_col_list||', '||req_cols.column_name;
661
658 END IF;
659 v_count := v_count + 1;
660 END LOOP;
662 Log_Table_Class_Error(
663 p_tab_name => p_tab_name,
664 p_tab_class_cd => p_tab_class_cd,
665 p_msg_name => v_msg_name,
666 p_token1 => 'OBJECT',
667 p_value1 => v_obj_name,
668 p_token2 => 'COL_LIST',
669 p_value2 => v_col_list);
670 v_passed_valid := c_false;
671 END IF; -- v_msg_name IS NOT NULL
672
673 END IF; -- p_col_req_type NOT IN ('FIXED_ANY','ONE_ANY')
674
675 x_passed_validation := v_passed_valid;
676
677 IF FND_LOG.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
678 FEM_ENGINES_PKG.TECH_MESSAGE(
679 p_severity => FND_LOG.level_statement,
680 p_module => C_MODULE,
681 p_msg_text => 'x_passed_validation = '||x_passed_validation);
682 END IF;
683 IF FND_LOG.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
684 FEM_ENGINES_PKG.TECH_MESSAGE(
685 p_severity => FND_LOG.level_procedure,
686 p_module => C_MODULE,
687 p_msg_text => 'End Procedure');
688 END IF;
689
690 EXCEPTION
691 WHEN others THEN
692 IF FND_LOG.level_exception >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
693 FEM_ENGINES_PKG.TECH_MESSAGE(
694 p_severity => FND_LOG.level_exception,
695 p_module => C_MODULE,
696 p_msg_text => 'Validate_Prop_Col_Req failed unexpectedly: '||SQLERRM);
697 END IF;
698 x_passed_validation := c_false;
699
700 END Validate_Prop_Col_Req;
701
702
703 ------------------------------------
704 -- Non-Generic Validation Procedures
705 ------------------------------------
706
707 -- Validate_PK_Cols_Not_Null:
708 -- Makes sure that all columns in the Processing Key is not null.
709
710 PROCEDURE Validate_PK_Cols_Not_Null (
711 p_tab_name IN VARCHAR2,
712 p_tab_class_cd IN VARCHAR2,
713 p_tab_owner IN VARCHAR2,
714 p_db_tab_name IN VARCHAR2,
715 x_passed_validation OUT NOCOPY VARCHAR2) IS
716 C_MODULE CONSTANT FND_LOG_MESSAGES.module%TYPE :=
717 'fem.plsql.fem_admin_util_pkg.validate_pk_cols_not_null';
718
719 CURSOR c_null_cols (p_tab_name VARCHAR2, p_owner VARCHAR2) IS
720 SELECT a.column_name
721 FROM all_tab_columns a, fem_tab_column_prop p
722 WHERE a.table_name = p_tab_name
723 AND a.owner = p_owner
724 AND a.nullable = 'Y'
725 AND a.table_name = p.table_name
726 AND a.column_name = p.column_name
727 AND p.column_property_code = 'PROCESSING_KEY'
728 ORDER BY a.column_name;
729
730 v_count NUMBER;
731 v_col_list VARCHAR2(2000);
732 BEGIN
733 IF FND_LOG.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
734 FEM_ENGINES_PKG.TECH_MESSAGE(
735 p_severity => FND_LOG.level_procedure,
736 p_module => C_MODULE,
737 p_msg_text => 'Begin Procedure');
738 END IF;
739
740 v_col_list := null;
741 v_count := 1;
742 FOR null_cols IN c_null_cols(p_db_tab_name, p_tab_owner) LOOP
743 IF v_count = 1 THEN
744 v_col_list := null_cols.column_name;
745 ELSE
746 v_col_list := v_col_list || ', '||null_cols.column_name;
747 END IF;
748 v_count := v_count + 1;
749 END LOOP;
750
751 IF v_col_list IS NULL THEN
752 x_passed_validation := c_true;
753 ELSE
754 Log_Table_Class_Error(
755 p_tab_name => p_tab_name,
756 p_tab_class_cd => p_tab_class_cd,
757 p_msg_name => 'FEM_TABCLASS_PK_COLS_NULL',
758 p_token1 => 'COLS',
759 p_value1 => v_col_list);
760 x_passed_validation := c_false;
761 END IF;
762
763 IF FND_LOG.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
764 FEM_ENGINES_PKG.TECH_MESSAGE(
765 p_severity => FND_LOG.level_statement,
766 p_module => C_MODULE,
767 p_msg_text => 'x_passed_validation = '||x_passed_validation);
768 END IF;
769 IF FND_LOG.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
770 FEM_ENGINES_PKG.TECH_MESSAGE(
771 p_severity => FND_LOG.level_procedure,
772 p_module => C_MODULE,
773 p_msg_text => 'End Procedure');
774 END IF;
775 EXCEPTION
776 WHEN others THEN
777 IF FND_LOG.level_exception >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
778 FEM_ENGINES_PKG.TECH_MESSAGE(
779 p_severity => FND_LOG.level_exception,
780 p_module => C_MODULE,
781 p_msg_text => 'Validate_PK_Cols_Not_Null failed unexpectedly: '||SQLERRM);
782 END IF;
783 x_passed_validation := c_false;
784
785 END Validate_PK_Cols_Not_Null;
786
787 -- Validate_Editable:
788 -- Makes sure all object columns are updateable, insertable, deletable
789
790 PROCEDURE Validate_Editable (
791 p_tab_name IN VARCHAR2,
792 p_tab_class_cd IN VARCHAR2,
793 x_passed_validation OUT NOCOPY VARCHAR2) IS
794 C_MODULE CONSTANT FND_LOG_MESSAGES.module%TYPE :=
795 'fem.plsql.fem_admin_util_pkg.validate_editable';
796
797 v_obj_type USER_OBJECTS.object_type%TYPE;
801 FEM_ENGINES_PKG.TECH_MESSAGE(
798 v_count NUMBER;
799 BEGIN
800 IF FND_LOG.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
802 p_severity => FND_LOG.level_procedure,
803 p_module => C_MODULE,
804 p_msg_text => 'Begin Procedure');
805 END IF;
806
807 SELECT object_type
808 INTO v_obj_type
809 FROM user_objects
810 WHERE object_name = p_tab_name;
811
812 IF FND_LOG.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
813 FEM_ENGINES_PKG.TECH_MESSAGE(
814 p_severity => FND_LOG.level_statement,
815 p_module => C_MODULE,
816 p_msg_text => 'v_obj_type = '||v_obj_type);
817 END IF;
818
819 IF v_obj_type = 'SYNONYM' THEN
820 x_passed_validation := c_true;
821 ELSIF v_obj_type = 'VIEW' THEN
822 SELECT count(*)
823 INTO v_count
824 FROM user_updatable_columns
825 WHERE table_name = p_tab_name
826 AND (updatable = 'NO'
827 OR insertable = 'NO'
828 OR deletable = 'NO');
829
830 IF v_count > 0 THEN
831 Log_Table_Class_Error(
832 p_tab_name => p_tab_name,
833 p_tab_class_cd => p_tab_class_cd,
834 p_msg_name => 'FEM_TABCLASS_NOT_EDITABLE');
835 x_passed_validation := c_false;
836 ELSE
837 x_passed_validation := c_true;
838 END IF;
839 ELSE
840 x_passed_validation := c_false;
841 END IF; -- v_obj_type
842
843 IF FND_LOG.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
844 FEM_ENGINES_PKG.TECH_MESSAGE(
845 p_severity => FND_LOG.level_statement,
846 p_module => C_MODULE,
847 p_msg_text => 'x_passed_validation = '||x_passed_validation);
848 END IF;
849 IF FND_LOG.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
850 FEM_ENGINES_PKG.TECH_MESSAGE(
851 p_severity => FND_LOG.level_procedure,
852 p_module => C_MODULE,
853 p_msg_text => 'End Procedure');
854 END IF;
855 EXCEPTION
856 WHEN others THEN
857 IF FND_LOG.level_exception >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
858 FEM_ENGINES_PKG.TECH_MESSAGE(
859 p_severity => FND_LOG.level_exception,
860 p_module => C_MODULE,
861 p_msg_text => 'Validate_Editable failed unexpectedly: '||SQLERRM);
862 END IF;
863 x_passed_validation := c_false;
864
865 END Validate_Editable;
866
867 -- Validate_Table_Name_Restrict:
868 -- Makes sure table name conforms to the requirements of the classification.
869 -- Currently, this is not metadata driven and only applies to the Ledger
870 -- classifications. This checks to make sure that only FEM_BALANCES can
871 -- receive the Ledger classifications.
872
873 PROCEDURE Validate_Table_Name_Restrict (
874 p_tab_name IN VARCHAR2,
875 p_tab_class_cd IN VARCHAR2,
876 x_passed_validation OUT NOCOPY VARCHAR2) IS
877 C_MODULE CONSTANT FND_LOG_MESSAGES.module%TYPE :=
878 'fem.plsql.fem_admin_util_pkg.validate_table_name_restriction';
879 BEGIN
880 IF FND_LOG.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
881 FEM_ENGINES_PKG.TECH_MESSAGE(
882 p_severity => FND_LOG.level_procedure,
883 p_module => C_MODULE,
884 p_msg_text => 'Begin Procedure');
885 END IF;
886
887 -- Init as passed
888 x_passed_validation := c_true;
889
890 IF p_tab_name <> 'FEM_BALANCES' THEN
891 Log_Table_Class_Error(
892 p_tab_name => p_tab_name,
893 p_tab_class_cd => p_tab_class_cd,
894 p_msg_name => 'FEM_TABCLASS_TAB_NAME_RESTRICT',
895 p_token1 => 'TAB_NAME',
896 p_value1 => 'FEM_BALANCES');
897
898 x_passed_validation := c_false;
899 END IF;
900
901 IF FND_LOG.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
902 FEM_ENGINES_PKG.TECH_MESSAGE(
903 p_severity => FND_LOG.level_procedure,
904 p_module => C_MODULE,
905 p_msg_text => 'End Procedure');
906 END IF;
907 EXCEPTION
908 WHEN others THEN
909 IF FND_LOG.level_exception >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
910 FEM_ENGINES_PKG.TECH_MESSAGE(
911 p_severity => FND_LOG.level_exception,
912 p_module => C_MODULE,
913 p_msg_text => 'Validate_Table_Name_Restriction failed unexpectedly: '||SQLERRM);
914 END IF;
915 x_passed_validation := c_false;
916
917 END Validate_Table_Name_Restrict;
918
919 /* Change history
920 06/12/2007 RFLIPPO Bug#6034150 allow non-dimension nullable columns
921 to be unmapped.
922 */
923 -- Validate_Data_Loader:
924 -- Procedure to perform validation specific to the
925 -- SOURCE_DATA_TABLE classification.
926 -- At this time, this procedure does not test to see if the
927 -- mapped interface columns have compatible data types base columns.
928
929 -----------------------------------------------------------
930 -- Bug#5226300 Verify that the interface column characteristics
931 -- match those of the target column
932 --
933 -- The logic is as follows:
934 -- For all cols in the target table where col_name not in
938 -- (fem_data_type_code= 'DIMENSION' AND
935 -- ('CAL_PERIOD_ID','CREATED_BY_REQUEST_ID','CREATED_BY_OBJECT_ID'
936 -- 'LAST_UPDATED_BY_REQUEST_ID','LAST_UPDATED_BY_OBJECT_ID') LOOP
937 -- IF fem_data_type_code <> 'DIMENSION' OR
939 -- the dimension has no surrogate key) THEN
940 -- compare col characteristics of the interface col
941 -- directly with target col. If anything doesn't
942 -- match, give error.
943 -- ELSE fem_data_type_code = 'DIMENSION' AND
944 -- dimension has surrogate key THEN
945 -- verify that the interface column characteristics match the
946 -- display_code col for that dimension in the dimension _B table
947 -- END IF;
948 -- END LOOP;
949 --------------------------------------------------------------
950
951
952 PROCEDURE Validate_Data_Loader (
953 p_tab_name IN VARCHAR2,
954 p_tab_class_cd IN VARCHAR2,
955 p_tab_owner IN VARCHAR2,
956 p_db_tab_name IN VARCHAR2,
957 x_passed_validation OUT NOCOPY VARCHAR2) IS
958 C_MODULE CONSTANT FND_LOG_MESSAGES.module%TYPE :=
959 'fem.plsql.fem_admin_util_pkg.validate_data_loader';
960
961 CURSOR c_uniq_idx(p_owner VARCHAR2, p_name VARCHAR2) IS
962 SELECT index_name
963 FROM all_indexes
964 WHERE table_name = p_name
965 AND table_owner = p_owner
966 AND uniqueness = 'UNIQUE';
967
968 CURSOR c_int_col (p_owner VARCHAR2, p_name VARCHAR2) IS
969 SELECT column_name, data_type, data_length, data_precision
970 FROM all_tab_columns
971 WHERE owner = p_owner
972 AND table_name = p_name
973 AND nullable = 'N'
974 AND column_name IN ('CAL_PERIOD_NUMBER',
975 'CALP_DIM_GRP_DISPLAY_CODE',
976 'CAL_PERIOD_END_DATE',
977 'STATUS')
978 ORDER BY column_name;
979
980 /*Bug#6034150 only require DIMENSION cols or NOT NULL cols
981 to have interface col mapping. For all other cols it is
982 optional*/
983 CURSOR c_missing_int_col (p_owner VARCHAR2, p_name VARCHAR2) IS
984 SELECT f.column_name
985 FROM fem_tab_columns_v f, all_tab_columns a
986 WHERE f.table_name = p_name
987 AND f.column_name NOT IN ('CREATED_BY_REQUEST_ID',
988 'LAST_UPDATED_BY_REQUEST_ID',
989 'CREATED_BY_OBJECT_ID',
990 'LAST_UPDATED_BY_OBJECT_ID',
991 'CAL_PERIOD_ID')
992 AND f.interface_column_name IS NULL
993 AND f.table_name = a.table_name
994 AND a.owner = p_owner
995 AND a.column_name = f.column_name
996 AND (f.fem_data_type_code = 'DIMENSION' OR a.nullable = 'N')
997 ORDER BY f.column_name;
998
999 CURSOR c_tgt_col (p_name IN VARCHAR2, p_owner IN VARCHAR2) IS
1000 SELECT F.column_name, F.interface_column_name, F.fem_data_type_code, F.dimension_id,
1001 A.data_type, A.data_length, A.data_precision, A.data_scale
1002 FROM fem_tab_columns_b F, all_tab_columns A
1003 WHERE F.table_name = p_name
1004 AND F.column_name NOT IN ('CREATED_BY_REQUEST_ID',
1005 'LAST_UPDATED_BY_REQUEST_ID',
1006 'CREATED_BY_OBJECT_ID',
1007 'LAST_UPDATED_BY_OBJECT_ID',
1008 'CAL_PERIOD_ID')
1009 AND F.interface_column_name IS NOT NULL
1010 AND F.table_name = A.table_name
1011 AND F.column_name = A.column_name
1012 AND A.owner = p_tab_owner;
1013
1014
1015 v_count NUMBER;
1016 v_passed_valid VARCHAR2(1);
1017 v_return_status VARCHAR2(1);
1018 v_int_tab_name ALL_TABLES.table_name%TYPE;
1019 v_int_db_tab_name ALL_TABLES.table_name%TYPE;
1020 v_int_owner ALL_TABLES.owner%TYPE;
1021 v_col_prop FEM_TAB_COLUMN_PROP.column_property_code%TYPE;
1022 v_matched VARCHAR2(1);
1023 v_msg_data VARCHAR2(2000);
1024 v_col_list VARCHAR2(4000);
1025
1026 -- Bug#5226300
1027 v_sql_stmt VARCHAR2(4000);
1028 v_surrogate_key_flag VARCHAR2(1);
1029 v_dim_mbr_table VARCHAR2(30);
1030 v_dim_mbr_col VARCHAR2(30);
1031 v_dim_mbr_dc_col VARCHAR2(30);
1032 v_dim_label VARCHAR2(30);
1033
1034 v_data_type ALL_TAB_COLUMNS.data_type%TYPE;
1035 v_data_length ALL_TAB_COLUMNS.data_length%TYPE;
1036 v_data_precision ALL_TAB_COLUMNS.data_precision%TYPE;
1037 v_data_scale ALL_TAB_COLUMNS.data_scale%TYPE;
1038
1039 v_dim_dc_data_length ALL_TAB_COLUMNS.data_length%TYPE;
1040 v_dim_dc_data_type ALL_TAB_COLUMNS.data_type%TYPE;
1041 v_mbr_b_owner ALL_TABLES.owner%TYPE;
1042 v_mbr_b_table_name ALL_TABLES.table_name%TYPE;
1043 BEGIN
1044 IF FND_LOG.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1045 FEM_ENGINES_PKG.TECH_MESSAGE(
1046 p_severity => FND_LOG.level_procedure,
1047 p_module => C_MODULE,
1048 p_msg_text => 'Begin Procedure');
1049 END IF;
1050
1051 -- Init variables
1052 v_passed_valid := c_true;
1053 v_col_prop := 'PROCESSING_KEY';
1054
1058 INTO v_int_tab_name
1055 -- Interface table must be registered
1056 BEGIN
1057 SELECT interface_table_name
1059 FROM fem_tables_b
1060 WHERE table_name = p_tab_name
1061 AND interface_table_name IS NOT NULL;
1062
1063 -- Get interface table name and owner
1064 FEM_Database_Util_Pkg.Get_Table_Owner (
1065 x_return_status => v_return_status,
1066 x_msg_count => v_count,
1067 x_msg_data => v_msg_data,
1068 p_syn_name => v_int_tab_name,
1069 x_tab_name => v_int_db_tab_name,
1070 x_tab_owner => v_int_owner);
1071
1072 IF v_return_status <> c_success THEN
1073 IF FND_LOG.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1074 FEM_ENGINES_PKG.TECH_MESSAGE(
1075 p_severity => FND_LOG.level_statement,
1076 p_module => C_MODULE,
1077 p_msg_text => 'Call to FEM_Database_Util_Pkg.Get_Table_Owner failed');
1078 END IF;
1079 RAISE e_unexp;
1080 ELSE
1081 IF FND_LOG.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1082 FEM_ENGINES_PKG.TECH_MESSAGE(
1083 p_severity => FND_LOG.level_statement,
1084 p_module => C_MODULE,
1085 p_msg_text => 'v_int_db_tab_name = '||v_int_db_tab_name);
1086 END IF;
1087 IF FND_LOG.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1088 FEM_ENGINES_PKG.TECH_MESSAGE(
1089 p_severity => FND_LOG.level_statement,
1090 p_module => C_MODULE,
1091 p_msg_text => 'v_int_owner = '||v_int_owner);
1092 END IF;
1093 END IF;
1094 EXCEPTION
1095 WHEN others THEN
1096 Log_Table_Class_Error(
1097 p_tab_name => p_tab_name,
1098 p_tab_class_cd => p_tab_class_cd,
1099 p_msg_name => 'FEM_TABCLASS_NO_INTERFACE_TAB');
1100
1101 RAISE e_error;
1102 END;
1103
1104 -- Only continue the validation if a valid interface table was designated.
1105 IF v_passed_valid = c_true THEN
1106 -- All dimension and not null columns should have an interface column mapping.
1107 v_col_list := null;
1108 v_count := 0;
1109 FOR missing_int_cols IN c_missing_int_col(p_tab_owner, p_tab_name) LOOP
1110 v_count := v_count + 1;
1111 IF v_count = 1 THEN
1112 v_col_list := missing_int_cols.column_name;
1113 ELSIF v_count < 134 THEN -- ceil(4000/30) = 134
1114 v_col_list := v_col_list||', '||missing_int_cols.column_name;
1115 ELSE
1116 EXIT;
1117 END IF;
1118 END LOOP;
1119
1120 IF v_col_list IS NOT NULL THEN
1121 Log_Table_Class_Error(
1122 p_tab_name => p_tab_name,
1123 p_tab_class_cd => p_tab_class_cd,
1124 p_msg_name => 'FEM_TABCLASS_REQCOL_REQINTCOL',
1125 p_token1 => 'COLS',
1126 p_value1 => v_col_list);
1127 RAISE e_error;
1128 END IF;
1129
1130 -- Make sure the three required Cal Period columns
1131 -- and the Status column
1132 -- are on the interface table with the proper data types.
1133 v_count := 0;
1134 FOR int_cols IN c_int_col (v_int_owner, v_int_db_tab_name) LOOP
1135 IF int_cols.column_name = 'CAL_PERIOD_NUMBER' THEN
1136 IF int_cols.data_type = 'NUMBER' AND
1137 nvl(int_cols.data_precision,38) <= 15 THEN
1138 v_count := v_count + 1;
1139 END IF;
1140 ELSIF int_cols.column_name = 'CALP_DIM_GRP_DISPLAY_CODE' THEN
1141 IF int_cols.data_type = 'VARCHAR2' AND
1142 int_cols.data_length <= 150 THEN
1143 v_count := v_count + 10;
1144 END IF;
1145 ELSIF int_cols.column_name = 'CAL_PERIOD_END_DATE' THEN
1146 IF int_cols.data_type = 'DATE' THEN
1147 v_count := v_count + 100;
1148 END IF;
1149 ELSIF int_cols.column_name = 'STATUS' THEN
1150 IF int_cols.data_type = 'VARCHAR2' AND
1151 int_cols.data_length = 60 THEN
1152 v_count := v_count + 1000;
1153 END IF;
1154 END IF;
1155 END LOOP;
1156
1157 IF v_count <> 1111 THEN
1158 IF FND_LOG.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1159 FEM_ENGINES_PKG.TECH_MESSAGE(
1160 p_severity => FND_LOG.level_statement,
1161 p_module => C_MODULE,
1162 p_msg_text => 'v_count (int_reqcol) = '||v_count);
1163 END IF;
1164 Log_Table_Class_Error(
1165 p_tab_name => p_tab_name,
1166 p_tab_class_cd => p_tab_class_cd,
1167 p_msg_name => 'FEM_TABCLASS_LDR_REQCOLS');
1168
1169 RAISE e_error;
1170 END IF;
1171
1172 -- Bug#5226300 Verify that the interface column characteristics
1173 FOR col IN c_tgt_col (p_tab_name, p_tab_owner) LOOP
1174 v_surrogate_key_flag := '';
1175 IF col.fem_data_type_code = 'DIMENSION' THEN
1176 SELECT member_b_table_name, member_col, member_display_code_col, dimension_varchar_label
1177 INTO v_dim_mbr_table, v_dim_mbr_col, v_dim_mbr_dc_col, v_dim_label
1178 FROM fem_xdim_dimensions_vl
1179 WHERE dimension_id = col.dimension_id;
1180
1181 IF v_dim_mbr_col = v_dim_mbr_dc_col THEN
1182 v_surrogate_key_flag := 'N';
1183 ELSE v_surrogate_key_flag := 'Y';
1184 END IF;
1185 END IF;
1186
1187 -- get the interface col characteristics
1191 INTO v_data_type, v_data_length, v_data_precision, v_data_scale
1188
1189 BEGIN
1190 SELECT data_type, data_length, data_precision, data_scale
1192 FROM all_tab_columns
1193 WHERE owner = v_int_owner
1194 AND table_name = v_int_db_tab_name
1195 AND column_name = col.interface_column_name;
1196 EXCEPTION
1197 WHEN no_data_found THEN -- this should never occur since the UI requires the interface_col to exist
1198 Log_Table_Class_Error(
1199 p_tab_name => p_tab_name,
1200 p_tab_class_cd => p_tab_class_cd,
1201 p_msg_name => 'FEM_TABCLASS_LDR_NOINTFCOL',
1202 p_token1 => 'INTFCOL',
1203 p_value1 => col.interface_column_name,
1204 p_token2 => 'COL',
1205 p_value2 => col.column_name,
1206 p_token3 => 'TAB',
1207 p_value3 => v_int_db_tab_name);
1208
1209 RAISE e_error;
1210
1211 END;
1212 IF (col.fem_data_type_code <> 'DIMENSION') OR
1213 (col.fem_data_type_code = 'DIMENSION' AND v_surrogate_key_flag = 'N') THEN
1214
1215 IF col.data_type <> v_data_type THEN
1216 Log_Table_Class_Error(
1217 p_tab_name => p_tab_name,
1218 p_tab_class_cd => p_tab_class_cd,
1219 p_msg_name => 'FEM_TABCLASS_LDR_INTFDATATYPE',
1220 p_token1 => 'INTFCOL',
1221 p_value1 => col.interface_column_name,
1222 p_token2 => 'COL',
1223 p_value2 => col.column_name);
1224
1225
1226 RAISE e_error;
1227 ELSIF col.data_length < v_data_length
1228 OR NVL(col.data_precision,col.data_length) < NVL(v_data_precision,v_data_length)
1229 OR NVL(col.data_scale,col.data_length) < NVL(v_data_scale,v_data_length) THEN
1230
1231 Log_Table_Class_Error(
1232 p_tab_name => p_tab_name,
1233 p_tab_class_cd => p_tab_class_cd,
1234 p_msg_name => 'FEM_TABCLASS_LDR_INTFCHAR',
1235 p_token1 => 'INTFCOL',
1236 p_value1 => col.interface_column_name,
1237 p_token2 => 'COL',
1238 p_value2 => col.column_name);
1239
1240
1241 RAISE e_error;
1242 END IF;
1243 ELSE -- column is for a surrogate key DIMENSION
1244 -- Get Dimension Member _B table name and owner
1245 BEGIN
1246 FEM_Database_Util_Pkg.Get_Table_Owner (
1247 x_return_status => v_return_status,
1248 x_msg_count => v_count,
1249 x_msg_data => v_msg_data,
1250 p_syn_name => v_dim_mbr_table,
1251 x_tab_name => v_mbr_b_table_name,
1252 x_tab_owner => v_mbr_b_owner);
1253
1254 IF v_return_status <> c_success THEN
1255 IF FND_LOG.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1256 FEM_ENGINES_PKG.TECH_MESSAGE(
1257 p_severity => FND_LOG.level_statement,
1258 p_module => C_MODULE,
1259 p_msg_text => 'Call to FEM_Database_Util_Pkg.Get_Table_Owner failed');
1260 END IF;
1261 RAISE e_unexp;
1262 ELSE
1263 IF FND_LOG.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1264 FEM_ENGINES_PKG.TECH_MESSAGE(
1265 p_severity => FND_LOG.level_statement,
1266 p_module => C_MODULE,
1267 p_msg_text => 'v_mbr_b_table_name = '||v_mbr_b_table_name);
1268 END IF;
1269 IF FND_LOG.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1270 FEM_ENGINES_PKG.TECH_MESSAGE(
1271 p_severity => FND_LOG.level_statement,
1272 p_module => C_MODULE,
1273 p_msg_text => 'v_mbr_b_owner = '||v_mbr_b_owner);
1274 END IF;
1275 END IF;
1276 EXCEPTION
1277 WHEN others THEN
1278 Log_Table_Class_Error(
1279 p_tab_name => p_tab_name,
1280 p_tab_class_cd => p_tab_class_cd,
1281 p_msg_name => 'FEM_TABCLASS_LDR_DIM_METADATA',
1282 p_token1 => 'TAB',
1283 p_value1 => v_mbr_b_table_name);
1284
1285 RAISE e_error;
1286 END;
1287
1288 -- get display_code metadata for the dimension
1289 -- we only need to get the data_type and data length, because all
1290 -- display_code columns are varchar2 by definition
1291 SELECT data_type,data_length
1292 INTO v_dim_dc_data_type,v_dim_dc_data_length
1293 FROM all_tab_columns
1294 WHERE owner = v_mbr_b_owner
1295 AND table_name = v_mbr_b_table_name
1296 AND column_name = v_dim_mbr_dc_col;
1297
1298
1299 IF v_data_type <> v_dim_dc_data_type THEN
1300
1301 Log_Table_Class_Error(
1302 p_tab_name => p_tab_name,
1303 p_tab_class_cd => p_tab_class_cd,
1304 p_msg_name => 'FEM_TABCLASS_LDR_DIMDATATYPE',
1308 p_value2 => col.column_name,
1305 p_token1 => 'INTFCOL',
1306 p_value1 => col.interface_column_name,
1307 p_token2 => 'COL',
1309 p_token3 => 'DIM',
1310 p_value3 => v_dim_label);
1311
1312
1313 RAISE e_error;
1314 ELSIF v_dim_dc_data_length < v_data_length THEN
1315 Log_Table_Class_Error(
1316 p_tab_name => p_tab_name,
1317 p_tab_class_cd => p_tab_class_cd,
1318 p_msg_name => 'FEM_TABCLASS_LDR_DIMCHAR',
1319 p_token1 => 'INTFCOL',
1320 p_value1 => col.interface_column_name,
1321 p_token2 => 'COL',
1322 p_value2 => col.column_name,
1323 p_token3 => 'DIM',
1324 p_value3 => v_dim_label);
1325
1326
1327 RAISE e_error;
1328 END IF;
1329
1330 END IF;
1331
1332 END LOOP;
1333
1334 -- Make sure a unique key of the interface table matches
1335 -- with the processing key of the base table.
1336 v_matched := c_false;
1337 FOR idx IN c_uniq_idx(v_int_owner, v_int_db_tab_name) LOOP
1338 -- First see if any columns are in the interface index but not in the
1339 -- proc key. Need to exclude columns in the proc key if they do not
1340 -- have a direct mapping (CAL_PERIOD_ID) or no mapping at all
1341 -- (Undo WHO columns).
1342 SELECT count(*)
1343 INTO v_count
1344 FROM all_ind_columns i, fem_tab_columns_b c
1345 WHERE i.index_name = idx.index_name
1346 AND i.table_owner = v_int_owner
1347 AND i.table_name = v_int_db_tab_name
1348 AND c.table_name = p_tab_name
1349 AND i.column_name = c.interface_column_name
1350 AND i.column_name NOT IN ('CAL_PERIOD_NUMBER',
1351 'CALP_DIM_GRP_DISPLAY_CODE',
1352 'CAL_PERIOD_END_DATE')
1353 AND c.column_name NOT IN
1354 (SELECT column_name
1355 FROM fem_tab_column_prop
1356 WHERE table_name = p_tab_name
1357 AND column_property_code = v_col_prop
1358 AND column_name NOT IN ('CAL_PERIOD_ID',
1359 'CREATED_BY_REQUEST_ID',
1360 'LAST_UPDATED_BY_REQUEST_ID',
1361 'CREATED_BY_OBJECT_ID',
1362 'LAST_UPDATED_BY_OBJECT_ID'));
1363
1364 IF v_count = 0 THEN
1365 v_matched := c_true;
1366 END IF;
1367
1368 -- Then see if any columns are in the proc key but not in the index
1369 IF v_matched = c_true THEN
1370 SELECT count(*)
1371 INTO v_count
1372 FROM fem_tab_column_prop
1373 WHERE table_name = p_tab_name
1374 AND column_property_code = v_col_prop
1375 AND column_name NOT IN ('CAL_PERIOD_ID',
1376 'CREATED_BY_REQUEST_ID',
1377 'LAST_UPDATED_BY_REQUEST_ID',
1378 'CREATED_BY_OBJECT_ID',
1379 'LAST_UPDATED_BY_OBJECT_ID')
1380 AND column_name NOT IN
1381 (SELECT c.column_name
1382 FROM all_ind_columns i, fem_tab_columns_b c
1383 WHERE i.index_name = idx.index_name
1384 AND i.table_owner = v_int_owner
1385 AND i.table_name = v_int_db_tab_name
1386 AND c.table_name = p_tab_name
1387 AND i.column_name = c.interface_column_name
1388 AND i.column_name NOT IN ('CAL_PERIOD_NUMBER',
1389 'CALP_DIM_GRP_DISPLAY_CODE','CAL_PERIOD_END_DATE'));
1390
1391 IF v_count > 0 THEN
1392 v_matched := c_false;
1393 END IF;
1394 END IF; -- v_matched = c_true
1395
1396 -- Check to make sure that if CAL_PERIOD_ID is part
1397 -- of the processing key of the base table, the corresponding
1398 -- three cal period interface columns are part of the
1399 -- matching unique index.
1400 IF v_matched = c_true THEN
1401 SELECT count(*)
1402 INTO v_count
1403 FROM fem_tab_column_prop
1404 WHERE table_name = p_tab_name
1405 AND column_property_code = v_col_prop
1406 AND column_name = 'CAL_PERIOD_ID';
1407
1408 IF v_count > 0 THEN
1409 SELECT count(*)
1410 INTO v_count
1411 FROM all_ind_columns i
1412 WHERE i.index_name = idx.index_name
1413 AND i.table_owner = v_int_owner
1414 AND i.table_name = v_int_db_tab_name
1415 AND i.column_name IN ('CAL_PERIOD_NUMBER',
1416 'CALP_DIM_GRP_DISPLAY_CODE','CAL_PERIOD_END_DATE');
1417
1418 IF v_count <> 3 THEN
1419 v_matched := c_false;
1420 END IF;
1421 END IF;
1422 END IF; -- v_matched = c_true
1423
1424 EXIT WHEN (v_matched = c_true);
1425 END LOOP; -- c_uniq_idx
1426
1427 IF v_matched = c_false THEN
1428 Log_Table_Class_Error(
1429 p_tab_name => p_tab_name,
1430 p_tab_class_cd => p_tab_class_cd,
1434 END IF; -- v_passed_valid = c_true
1431 p_msg_name => 'FEM_TABCLASS_LDR_PK_NOMATCH');
1432 v_passed_valid := c_false;
1433 END IF;
1435
1436 -- Bug 4534907: Create client data loader rule related metadata
1437 IF v_passed_valid = c_true THEN
1438 v_passed_valid := Create_Data_Loader_Rule(
1439 p_tab_name => p_tab_name,
1440 p_tab_class_cd => p_tab_class_cd);
1441 END IF;
1442
1443 x_passed_validation := v_passed_valid;
1444
1445 IF FND_LOG.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1446 FEM_ENGINES_PKG.TECH_MESSAGE(
1447 p_severity => FND_LOG.level_statement,
1448 p_module => C_MODULE,
1449 p_msg_text => 'x_passed_validation = '||x_passed_validation);
1450 END IF;
1451 IF FND_LOG.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1452 FEM_ENGINES_PKG.TECH_MESSAGE(
1453 p_severity => FND_LOG.level_procedure,
1454 p_module => C_MODULE,
1455 p_msg_text => 'End Procedure');
1456 END IF;
1457 EXCEPTION
1458 WHEN e_error THEN
1459 x_passed_validation := c_false;
1460
1461 WHEN others THEN
1462 IF FND_LOG.level_exception >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1463 FEM_ENGINES_PKG.TECH_MESSAGE(
1464 p_severity => FND_LOG.level_exception,
1465 p_module => C_MODULE,
1466 p_msg_text => 'Validate_Data_Loader failed unexpectedly: '||SQLERRM);
1467 END IF;
1468 Log_Table_Class_Error(
1469 p_tab_name => p_tab_name,
1470 p_tab_class_cd => p_tab_class_cd,
1471 p_msg_name => 'FEM_RSM_UNEXPECTED_ERROR',
1472 p_token1 => 'ROUTINE_NAME',
1473 p_value1 => C_MODULE);
1474
1475 x_passed_validation := c_false;
1476
1477 END Validate_Data_Loader;
1478
1479
1480 /***************************************************************************
1481 ***************************************************************************
1482 *
1483 * ====================================
1484 * Procedure: Delete_Obj_Tuning_Options
1485 * ====================================
1486 *
1487 ***************************************************************************
1488 **************************************************************************/
1489
1490 PROCEDURE Delete_Obj_Tuning_Options (
1491 p_api_version IN NUMBER DEFAULT c_api_version,
1492 p_init_msg_list IN VARCHAR2 DEFAULT c_false,
1493 p_commit IN VARCHAR2 DEFAULT c_false,
1494 p_encoded IN VARCHAR2 DEFAULT c_true,
1495 x_return_status OUT NOCOPY VARCHAR2,
1496 x_msg_count OUT NOCOPY NUMBER,
1497 x_msg_data OUT NOCOPY VARCHAR2,
1498 p_object_id IN NUMBER
1499 ) IS
1500
1501 C_MODULE CONSTANT FND_LOG_MESSAGES.module%TYPE :=
1502 'fem.plsql.fem_admin_util_pkg.delete_obj_tuning_options';
1503 C_API_NAME CONSTANT VARCHAR2(30) := 'Delete_Obj_Tuning_Options';
1504
1505 v_process_ds_cd NUMBER;
1506
1507 -- Select all multiprocessing option assignments specific to the given Object ID.
1508 CURSOR c1 (cp_object_id IN NUMBER) IS
1509 SELECT process_data_slices_cd
1510 FROM fem_mp_process_options
1511 WHERE object_id = cp_object_id
1512 FOR UPDATE;
1513
1514 BEGIN
1515
1516 -- Initialize return status to unexpected error
1517 x_return_status := c_unexp;
1518
1519 IF FND_LOG.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1520 FEM_ENGINES_PKG.TECH_MESSAGE(
1521 p_severity => FND_LOG.level_procedure,
1522 p_module => C_MODULE,
1523 p_msg_text => 'Begin Procedure');
1524 END IF;
1525
1526 IF p_init_msg_list = c_true THEN
1527 FND_MSG_PUB.Initialize;
1528 END IF;
1529
1530 -- Check for call compatibility.
1531 IF NOT FND_API.Compatible_API_Call (c_api_version,
1532 p_api_version,
1533 C_API_NAME,
1534 G_PKG_NAME)
1535 THEN
1536 IF FND_LOG.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1537 FEM_ENGINES_PKG.TECH_MESSAGE(
1538 p_severity => FND_LOG.level_statement,
1539 p_module => C_MODULE,
1540 p_msg_text => 'API Version ('||C_API_VERSION||') not compatible with '
1541 ||'passed in version ('||p_api_version||')');
1542 END IF;
1543 RAISE e_unexp;
1544 END IF;
1545
1546 -- --------------------------------------------------------------------------
1547 -- Delete each MP process option assignment for the given Object ID, and if
1548 -- it references a data slice definition that is not referenced by any other
1549 -- MP process option assignment, then delete it too.
1550 -- --------------------------------------------------------------------------
1551
1552 FOR process_option IN c1 (p_object_id) LOOP
1553
1554 v_process_ds_cd := process_option.process_data_slices_cd;
1555
1556 DELETE FROM fem_mp_process_options
1557 WHERE CURRENT OF c1;
1558
1559 DELETE FROM fem_mp_data_slices
1560 WHERE process_data_slices_cd = v_process_ds_cd
1561 AND NOT EXISTS
1562 (SELECT NULL
1563 FROM fem_mp_process_options
1564 WHERE process_data_slices_cd = v_process_ds_cd);
1565
1566 DELETE FROM fem_mp_data_slice_cols
1570 FROM fem_mp_process_options
1567 WHERE process_data_slices_cd = v_process_ds_cd
1568 AND NOT EXISTS
1569 (SELECT NULL
1571 WHERE process_data_slices_cd = v_process_ds_cd);
1572
1573 END LOOP;
1574
1575 -- --------------------------------------------------------------------------
1576 -- Delete Object ID-specific process behavior parameter assignments for the
1577 -- current object.
1578 -- --------------------------------------------------------------------------
1579
1580 DELETE FROM fem_pb_parameters p
1581 WHERE object_id = p_object_id;
1582
1583 IF (p_commit = c_true) THEN
1584 COMMIT;
1585 END IF;
1586
1587 -- In case any messages are generated
1588 FND_MSG_PUB.Count_And_Get(p_encoded => p_encoded,
1589 p_count => x_msg_count,
1590 p_data => x_msg_data);
1591
1592 x_return_status := c_success;
1593
1594 IF FND_LOG.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1595 FEM_ENGINES_PKG.TECH_MESSAGE(
1596 p_severity => FND_LOG.level_procedure,
1597 p_module => C_MODULE,
1598 p_msg_text => 'End Procedure');
1599 END IF;
1600
1601 EXCEPTION
1602 WHEN others THEN
1603 IF FND_LOG.level_exception >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1604 FEM_ENGINES_PKG.TECH_MESSAGE(
1605 p_severity => FND_LOG.level_exception,
1606 p_module => C_MODULE,
1607 p_msg_text => 'Unexpected error: '||SQLERRM);
1608 END IF;
1609 IF FND_LOG.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1610 FEM_ENGINES_PKG.TECH_MESSAGE(
1611 p_severity => FND_LOG.level_procedure,
1612 p_module => C_MODULE,
1613 p_msg_text => 'End Procedure');
1614 END IF;
1615 FND_MSG_PUB.Count_And_Get(p_encoded => p_encoded,
1616 p_count => x_msg_count,
1617 p_data => x_msg_data);
1618
1619 x_return_status := c_unexp;
1620
1621 END Delete_Obj_Tuning_Options;
1622
1623
1624 /***************************************************************************
1625 ***************************************************************************
1626 * *
1627 * ================================ *
1628 * Procedure: New Local_VS_Combo_ID *
1629 * ================================ *
1630 * *
1631 ***************************************************************************
1632 **************************************************************************/
1633
1634 PROCEDURE New_Local_VS_Combo_ID (
1635 p_api_version IN NUMBER DEFAULT c_api_version,
1636 p_init_msg_list IN VARCHAR2 DEFAULT c_false,
1637 p_commit IN VARCHAR2 DEFAULT c_false,
1638 p_encoded IN VARCHAR2 DEFAULT c_true,
1639 x_return_status OUT NOCOPY VARCHAR2,
1640 x_msg_count OUT NOCOPY NUMBER,
1641 x_msg_data OUT NOCOPY VARCHAR2,
1642 p_gvsc_id IN NUMBER
1643 )
1644 IS
1645 BEGIN
1646 x_return_status := c_success;
1647 END New_Local_VS_Combo_ID;
1648
1649
1650 /***************************************************************************
1651 ***************************************************************************
1652 * *
1653 * ================================= *
1654 * Procedure: Validate Table Columns *
1655 * ================================= *
1656 * *
1657 ***************************************************************************
1658 ***************************************************************************
1659
1660 This procedure validates that table property required columns
1661 satifies the column requirements as defined by FEM_COLUMN_REQUIREMNT_B.
1662
1663 **************************************************************************/
1664
1665 PROCEDURE Validate_Column_Req (
1666 p_tab_name IN VARCHAR2,
1667 p_tab_class_cd IN VARCHAR2,
1668 p_tab_owner IN VARCHAR2,
1669 p_db_tab_name IN VARCHAR2,
1670 x_passed_validation OUT NOCOPY VARCHAR2
1671 )
1672 IS
1673 C_MODULE CONSTANT FND_LOG_MESSAGES.module%TYPE :=
1674 'fem.plsql.fem_admin_util_pkg.validate_column_req';
1675
1676 -- Cursor to fetch all registered columns that match
1677 -- those in FEM_COLUMN_REQUIREMNT_B
1678 CURSOR c_req_col (p_owner VARCHAR2, p_syn_name VARCHAR2,
1679 p_db_name VARCHAR2, p_tab_class_cd IN VARCHAR2) IS
1680 SELECT DISTINCT t.column_name,
1681 t.fem_data_type_code col_fem_data_type_code,
1682 r.fem_data_type_code req_fem_data_type_code,
1683 a.data_length||', '||a.data_precision||', '||a.data_scale col_data_length,
1684 r.data_length||', '||r.data_precision||', '||r.data_scale req_data_length,
1685 a.data_type col_data_type, r.data_type req_data_type,
1689 WHERE t.table_name = p_syn_name
1686 a.nullable col_nullable, tp.nullable_flag req_nullable
1687 FROM fem_tab_columns_b t, fem_column_requiremnt_b r, all_tab_columns a,
1688 fem_table_prop_col_req tp, fem_table_class_prop tc
1690 AND t.column_name = r.column_name
1691 AND a.table_name = p_db_name
1692 AND a.owner = p_tab_owner
1693 AND t.column_name = a.column_name
1694 AND t.column_name = tp.column_name
1695 AND tp.table_property_code = tc.table_property_code
1696 AND tc.table_classification_code = p_tab_class_cd;
1697
1698 v_passed_valid VARCHAR2(1);
1699 v_fem_data_type_name FEM_TAB_COLUMNS_B.fem_data_type_code%TYPE;
1700 BEGIN
1701
1702 IF FND_LOG.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1703 FEM_ENGINES_PKG.TECH_MESSAGE(
1704 p_severity => FND_LOG.level_procedure,
1705 p_module => C_MODULE,
1706 p_msg_text => 'Begin Procedure');
1707 END IF;
1708 IF FND_LOG.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1709 FEM_ENGINES_PKG.TECH_MESSAGE(
1710 p_severity => FND_LOG.level_statement,
1711 p_module => C_MODULE,
1712 p_msg_text => 'p_tab_class_cd = '||p_tab_class_cd);
1713 END IF;
1714
1715 -- Init var
1716 v_passed_valid := c_true;
1717
1718 FOR req_cols IN c_req_col(p_tab_owner, p_tab_name,
1719 p_db_tab_name, p_tab_class_cd) LOOP
1720 -- Check FEM Data Type Code
1721 IF req_cols.col_fem_data_type_code <> req_cols.req_fem_data_type_code THEN
1722 BEGIN
1723 SELECT fem_data_type_name
1724 INTO v_fem_data_type_name
1725 FROM fem_data_types_vl
1726 WHERE fem_data_type_code = req_cols.req_fem_data_type_code;
1727 EXCEPTION
1728 WHEN others THEN
1729 v_fem_data_type_name := req_cols.req_fem_data_type_code;
1730 END;
1731 Log_Table_Class_Error(
1732 p_tab_name => p_tab_name,
1733 p_tab_class_cd => p_tab_class_cd,
1734 p_msg_name => 'FEM_ADM_BAD_DATA_CODE_ERR',
1735 p_token1 => 'PROP_REQ_COL',
1736 p_value1 => req_cols.column_name,
1737 p_token2 => 'FEM_DATA_TYPE',
1738 p_value2 => v_fem_data_type_name);
1739 v_passed_valid := c_false;
1740 END IF;
1741
1742 IF req_cols.col_data_type = req_cols.req_data_type THEN
1743 IF req_cols.col_data_length <> req_cols.req_data_length THEN
1744 Log_Table_Class_Error(
1745 p_tab_name => p_tab_name,
1746 p_tab_class_cd => p_tab_class_cd,
1747 p_msg_name => 'FEM_ADM_BAD_DATA_LENGTH_ERR',
1748 p_token1 => 'PROP_REQ_COL',
1749 p_value1 => req_cols.column_name,
1750 p_token2 => 'DATA_LENGTH',
1751 p_value2 => req_cols.req_data_length);
1752 v_passed_valid := c_false;
1753 END IF;
1754 ELSE
1755 Log_Table_Class_Error(
1756 p_tab_name => p_tab_name,
1757 p_tab_class_cd => p_tab_class_cd,
1758 p_msg_name => 'FEM_ADM_BAD_DATA_TYPE_ERR',
1759 p_token1 => 'PROP_REQ_COL',
1760 p_value1 => req_cols.column_name,
1761 p_token2 => 'DATA_TYPE',
1762 p_value2 => req_cols.req_data_type);
1763 v_passed_valid := c_false;
1764 END IF;
1765
1766 IF req_cols.req_nullable = 'N' AND
1767 req_cols.col_nullable = 'Y' THEN
1768 Log_Table_Class_Error(
1769 p_tab_name => p_tab_name,
1770 p_tab_class_cd => p_tab_class_cd,
1771 p_msg_name => 'FEM_ADM_BAD_NULLABILITY_ERR',
1772 p_token1 => 'PROP_REQ_COL',
1773 p_value1 => req_cols.column_name);
1774 v_passed_valid := c_false;
1775 END IF;
1776
1777
1778 END LOOP; -- c_req_col
1779
1780 x_passed_validation := v_passed_valid;
1781
1782 IF FND_LOG.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1783 FEM_ENGINES_PKG.TECH_MESSAGE(
1784 p_severity => FND_LOG.level_statement,
1785 p_module => C_MODULE,
1786 p_msg_text => 'x_passed_validation = '||x_passed_validation);
1787 END IF;
1788 IF FND_LOG.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1789 FEM_ENGINES_PKG.TECH_MESSAGE(
1790 p_severity => FND_LOG.level_procedure,
1791 p_module => C_MODULE,
1792 p_msg_text => 'End Procedure');
1793 END IF;
1794 EXCEPTION
1795 WHEN others THEN
1796 IF FND_LOG.level_exception >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1797 FEM_ENGINES_PKG.TECH_MESSAGE(
1798 p_severity => FND_LOG.level_exception,
1799 p_module => C_MODULE,
1800 p_msg_text => 'Validate_Column_Req failed unexpectedly: '||SQLERRM);
1801 END IF;
1802 Log_Table_Class_Error(
1803 p_tab_name => p_tab_name,
1804 p_tab_class_cd => p_tab_class_cd,
1805 p_msg_name => 'FEM_RSM_UNEXPECTED_ERROR',
1806 p_token1 => 'ROUTINE_NAME',
1807 p_value1 => C_MODULE);
1808
1809 x_passed_validation := c_false;
1810
1811 END Validate_Column_Req;
1812
1813 /***************************************************************************
1814 ***************************************************************************
1815 * *
1816 * ================================== *
1820 ***************************************************************************
1817 * Procedure: Validate Processing Key *
1818 * ================================== *
1819 * *
1821 ***************************************************************************
1822
1823 This procedure validates that the set of processing key columns
1824 (as stored in the FEM_TAB_COLUMN_PROP metadata table)
1825 match the set of unique index columns designated as the processing key index.
1826
1827 **************************************************************************/
1828
1829 PROCEDURE Validate_Processing_Key_Idx (
1830 p_tab_name IN VARCHAR2,
1831 p_tab_class_cd IN VARCHAR2,
1832 p_tab_owner IN VARCHAR2,
1833 p_db_tab_name IN VARCHAR2,
1834 x_passed_validation OUT NOCOPY VARCHAR2
1835 )
1836 IS
1837 C_MODULE CONSTANT FND_LOG_MESSAGES.module%TYPE :=
1838 'fem.plsql.fem_admin_util_pkg.validate_processing_key_idx';
1839
1840 v_count NUMBER;
1841 v_idx_name FEM_TABLES_B.proc_key_index_name%TYPE;
1842 v_idx_owner FEM_TABLES_B.proc_key_index_owner%TYPE;
1843 v_matched VARCHAR2(1);
1844
1845 e_no_idx EXCEPTION;
1846 e_exit_successfully EXCEPTION;
1847 e_mismatch_idx EXCEPTION;
1848 BEGIN
1849
1850 IF FND_LOG.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1851 FEM_ENGINES_PKG.TECH_MESSAGE(
1852 p_severity => FND_LOG.level_procedure,
1853 p_module => C_MODULE,
1854 p_msg_text => 'Begin Procedure');
1855 END IF;
1856
1857 v_matched := c_false;
1858
1859 -- First make sure the index actually exists
1860 BEGIN
1861 SELECT proc_key_index_name, proc_key_index_owner
1862 INTO v_idx_name, v_idx_owner
1863 FROM fem_tables_b
1864 WHERE table_name = p_tab_name;
1865
1866 IF FND_LOG.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1867 FEM_ENGINES_PKG.TECH_MESSAGE(
1868 p_severity => FND_LOG.level_statement,
1869 p_module => C_MODULE,
1870 p_msg_text => 'Processing key index = '||v_idx_owner||'.'||v_idx_name);
1871 END IF;
1872
1873 IF v_idx_name IS NULL AND v_idx_owner IS NULL THEN
1874 SELECT count(*)
1875 INTO v_count
1876 FROM fem_tab_column_prop
1877 WHERE table_name = p_tab_name
1878 AND column_property_code = 'PROCESSING_KEY';
1879
1880 -- Only error if no index is specified but proc key metadata is populated
1881 IF v_count = 0 THEN
1882 RAISE e_exit_successfully;
1883 ELSE
1884 RAISE e_mismatch_idx;
1885 END IF;
1886 END IF; -- v_idx_name IS NULL AND ...
1887 END;
1888
1889 -- If PROC_KEY_INDEX_OWNER is null, default index owner to table owner
1890 IF v_idx_owner IS NULL THEN
1891 v_idx_owner := p_tab_owner;
1892 END IF;
1893
1894 -- Make sure index exists
1895 SELECT count(*)
1896 INTO v_count
1897 FROM all_indexes
1898 WHERE index_name= v_idx_name
1899 AND owner = v_idx_owner
1900 AND table_name = p_db_tab_name
1901 AND table_owner = p_tab_owner
1902 AND uniqueness = 'UNIQUE';
1903
1904 IF v_count = 0 THEN
1905 RAISE e_no_idx;
1906 END IF;
1907
1908 -- Then make sure the Proc Key metadata matches with the index
1909 SELECT count(*)
1910 INTO v_count
1911 FROM all_ind_columns
1912 WHERE index_owner = v_idx_owner
1913 AND index_name = v_idx_name
1914 AND table_owner = p_tab_owner
1915 AND table_name = p_db_tab_name
1916 AND column_name NOT IN
1917 (SELECT column_name
1918 FROM fem_tab_column_prop
1919 WHERE table_name = p_tab_name
1920 AND column_property_code = 'PROCESSING_KEY');
1921
1922 IF v_count = 0 THEN
1923 v_matched := c_true;
1924 END IF;
1925
1926 IF v_matched = c_true THEN
1927 SELECT count(*)
1928 INTO v_count
1929 FROM fem_tab_column_prop
1930 WHERE table_name = p_tab_name
1931 AND column_property_code = 'PROCESSING_KEY'
1932 AND column_name NOT IN
1933 (SELECT column_name
1934 FROM all_ind_columns
1935 WHERE index_owner = v_idx_owner
1936 AND index_name = v_idx_name
1937 AND table_owner = p_tab_owner
1938 AND table_name = p_db_tab_name);
1939
1940 IF v_count > 0 THEN
1941 v_matched := c_false;
1942 END IF;
1943 END IF; -- v_matched = c_true
1944
1945 IF v_matched = c_false THEN
1946 RAISE e_mismatch_idx;
1947 END IF;
1948
1949 x_passed_validation := v_matched;
1950
1951 IF FND_LOG.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1952 FEM_ENGINES_PKG.TECH_MESSAGE(
1953 p_severity => FND_LOG.level_statement,
1954 p_module => C_MODULE,
1955 p_msg_text => 'x_passed_validation = '||x_passed_validation);
1956 END IF;
1957 IF FND_LOG.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1958 FEM_ENGINES_PKG.TECH_MESSAGE(
1959 p_severity => FND_LOG.level_procedure,
1960 p_module => C_MODULE,
1961 p_msg_text => 'End Procedure');
1965 x_passed_validation := c_true;
1962 END IF;
1963 EXCEPTION
1964 WHEN e_exit_successfully THEN
1966
1967 WHEN e_mismatch_idx THEN
1968 Log_Table_Class_Error(
1969 p_tab_name => p_tab_name,
1970 p_tab_class_cd => p_tab_class_cd,
1971 p_msg_name => 'FEM_ADM_NO_UI_PK_MATCH_ERR',
1972 p_token1 => 'INDEX_NAME',
1973 p_value1 => v_idx_owner||'.'||v_idx_name);
1974
1975 x_passed_validation := c_false;
1976
1977 WHEN e_no_idx THEN
1978 Log_Table_Class_Error(
1979 p_tab_name => p_tab_name,
1980 p_tab_class_cd => p_tab_class_cd,
1981 p_msg_name => 'FEM_TABCLASS_PK_NO_INDEX',
1982 p_token1 => 'INDEX_NAME',
1983 p_value1 => v_idx_owner||'.'||v_idx_name);
1984
1985 x_passed_validation := c_false;
1986
1987 WHEN others THEN
1988 IF FND_LOG.level_exception >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1989 FEM_ENGINES_PKG.TECH_MESSAGE(
1990 p_severity => FND_LOG.level_exception,
1991 p_module => C_MODULE,
1992 p_msg_text => 'Validate_Column_Req failed unexpectedly: '||SQLERRM);
1993 END IF;
1994 Log_Table_Class_Error(
1995 p_tab_name => p_tab_name,
1996 p_tab_class_cd => p_tab_class_cd,
1997 p_msg_name => 'FEM_RSM_UNEXPECTED_ERROR',
1998 p_token1 => 'ROUTINE_NAME',
1999 p_value1 => C_MODULE);
2000
2001 x_passed_validation := c_false;
2002
2003 END Validate_Processing_Key_Idx;
2004
2005
2006 /***************************************************************************
2007 ***************************************************************************
2008 * *
2009 * ============================= *
2010 * Procedure: Validate OGL DimCol *
2011 * ============================= *
2012 * *
2013 ***************************************************************************
2014 ***************************************************************************
2015
2016 This procedure validates that all of the dimension columns on FEM_BALANCES
2017 that the OGL Integration uses have been assigned to a VSR dimension.
2018
2019 **************************************************************************/
2020
2021 PROCEDURE Validate_OGL_Dimcol (
2022 p_tab_name IN VARCHAR2,
2023 p_tab_class_cd IN VARCHAR2,
2024 x_passed_validation OUT NOCOPY VARCHAR2) IS
2025 C_MODULE CONSTANT FND_LOG_MESSAGES.module%TYPE :=
2026 'fem.plsql.fem_admin_util_pkg.validate_ogl_dimcol';
2027
2028 CURSOR c_dimcol (p_name VARCHAR2) IS
2029 SELECT column_name, dimension_id
2030 FROM fem_tab_columns_b
2031 WHERE table_name = p_name
2032 AND fem_data_type_code = 'DIMENSION'
2033 AND column_name IN ('COMPANY_COST_CENTER_ORG_ID'
2034 ,'NATURAL_ACCOUNT_ID'
2035 ,'LINE_ITEM_ID'
2036 ,'PRODUCT_ID'
2037 ,'CHANNEL_ID'
2038 ,'PROJECT_ID'
2039 ,'CUSTOMER_ID'
2040 ,'ENTITY_ID'
2041 ,'INTERCOMPANY_ID'
2042 ,'TASK_ID'
2043 ,'USER_DIM1_ID'
2044 ,'USER_DIM2_ID'
2045 ,'USER_DIM3_ID'
2046 ,'USER_DIM4_ID'
2047 ,'USER_DIM5_ID'
2048 ,'USER_DIM6_ID'
2049 ,'USER_DIM7_ID'
2050 ,'USER_DIM8_ID'
2051 ,'USER_DIM9_ID'
2052 ,'USER_DIM10_ID')
2053 ORDER BY column_name;
2054
2055
2056 v_count NUMBER;
2057 v_col_list VARCHAR2(4000);
2058 v_col_count NUMBER;
2059 v_not_vsr_flag VARCHAR2(1); -- a 'Y' designates that a non-VSR dim was found
2060 v_passed_valid VARCHAR2(1);
2061 v_return_status VARCHAR2(1);
2062 v_matched VARCHAR2(1);
2063 v_msg_data VARCHAR2(2000);
2064
2065 BEGIN
2066 IF FND_LOG.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2067 FEM_ENGINES_PKG.TECH_MESSAGE(
2068 p_severity => FND_LOG.level_procedure,
2069 p_module => C_MODULE,
2070 p_msg_text => 'Begin Procedure');
2071 END IF;
2072
2073 -- Init variables
2074 v_passed_valid := c_true;
2075
2076
2077 v_col_list := null;
2078 v_col_count := 1;
2079 v_not_vsr_flag := 'N';
2080
2081 -- Verify that the column maps to a value set dimension
2082 -- with the caveat, that COMPANY and COST_CENTER dimensions
2083 -- are not valid targets (OGL integration requires that
2084 -- these are not mapped directly on FEM_BALANCES because
2085 -- they are attributes of the Company Cost Center Org
2086 -- dimension
2087 FOR col IN c_dimcol (p_tab_name) LOOP
2088 SELECT count(*)
2089 INTO v_count
2090 FROM fem_xdim_dimensions_vl
2091 WHERE dimension_id = col.dimension_id
2092 AND value_set_required_flag = 'Y'
2093 AND dimension_varchar_label not in ('COMPANY','COST_CENTER');
2094
2095
2096 IF v_count = 0 THEN
2097 v_not_vsr_flag := 'Y';
2098 IF v_col_count = 1 THEN
2099 v_col_list := col.column_name;
2100 ELSE
2101 v_col_list := v_col_list||', '||col.column_name;
2102 END IF;
2106 v_col_count := v_col_count + 1;
2103
2104 END IF;
2105
2107 END LOOP;
2108
2109 IF v_not_vsr_flag = 'Y' THEN
2110
2111 Log_Table_Class_Error(
2112 p_tab_name => p_tab_name,
2113 p_tab_class_cd => p_tab_class_cd,
2114 p_msg_name => 'FEM_TABCLASS_OGL_BAD_DIMCOL',
2115 p_token1 => 'COL',
2116 p_value1 => v_col_list);
2117
2118 RAISE e_error;
2119 END IF;
2120
2121
2122 x_passed_validation := v_passed_valid;
2123
2124 IF FND_LOG.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2125 FEM_ENGINES_PKG.TECH_MESSAGE(
2126 p_severity => FND_LOG.level_statement,
2127 p_module => C_MODULE,
2128 p_msg_text => 'x_passed_validation = '||x_passed_validation);
2129 END IF;
2130 IF FND_LOG.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2131 FEM_ENGINES_PKG.TECH_MESSAGE(
2132 p_severity => FND_LOG.level_procedure,
2133 p_module => C_MODULE,
2134 p_msg_text => 'End Procedure');
2135 END IF;
2136 EXCEPTION
2137 WHEN e_error THEN
2138 x_passed_validation := c_false;
2139
2140 WHEN others THEN
2141 IF FND_LOG.level_exception >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2142 FEM_ENGINES_PKG.TECH_MESSAGE(
2143 p_severity => FND_LOG.level_exception,
2144 p_module => C_MODULE,
2145 p_msg_text => 'Validate_OGL_Dimcol failed unexpectedly: '||SQLERRM);
2146 END IF;
2147 Log_Table_Class_Error(
2148 p_tab_name => p_tab_name,
2149 p_tab_class_cd => p_tab_class_cd,
2150 p_msg_name => 'FEM_RSM_UNEXPECTED_ERROR',
2151 p_token1 => 'ROUTINE_NAME',
2152 p_value1 => C_MODULE);
2153
2154 x_passed_validation := c_false;
2155
2156 END Validate_OGL_Dimcol;
2157
2158
2159
2160 /***************************************************************************
2161 ***************************************************************************
2162 * *
2163 * ============================= *
2164 * Procedure: Validate Tab Class *
2165 * ============================= *
2166 * *
2167 ***************************************************************************
2168 ***************************************************************************
2169
2170 This procedure is a wrapper that calls other table validation APIs in
2171 this package.
2172
2173 History:
2174 09/05/06 Rob Flippo Bug#5500573 Added validate_ogl_dimcol to the procedure
2175 list
2176
2177 **************************************************************************/
2178
2179 PROCEDURE Validate_Tab_Class (
2180 p_tab_name IN VARCHAR2,
2181 p_tab_class_cd IN VARCHAR2,
2182 x_passed_validation OUT NOCOPY VARCHAR2
2183 )
2184 IS
2185 C_MODULE CONSTANT FND_LOG_MESSAGES.module%TYPE :=
2186 'fem.plsql.fem_admin_util_pkg.validate_tab_class';
2187
2188 CURSOR c_class_prop (p_class VARCHAR2) IS
2189 SELECT p.table_property_code, p.property_type, p.column_req_type
2190 FROM fem_table_class_prop c, fem_table_properties p
2191 WHERE c.table_classification_code = p_class
2192 AND c.table_property_code = p.table_property_code
2193 AND p.property_type <> 'OTHER';
2194
2195 CURSOR c_procedure (p_class VARCHAR2) IS
2196 SELECT DISTINCT S.stored_procedure_name
2197 FROM fem_table_class_prop C,
2198 fem_table_prop_stp S
2199 WHERE C.table_classification_code = p_class
2200 AND C.table_property_code = S.table_property_code;
2201
2202 v_return_status VARCHAR2(1);
2203 v_passed_valid_out VARCHAR2(1);
2204 v_passed VARCHAR2(1);
2205 v_msg_count NUMBER;
2206 v_msg_data VARCHAR2(2000);
2207
2208 v_db_tab_name ALL_TABLES.table_name%TYPE;
2209 v_tab_owner ALL_TABLES.owner%TYPE;
2210 v_procedure FEM_TABLE_PROP_STP.stored_procedure_name%TYPE;
2211 BEGIN
2212 IF FND_LOG.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2213 FEM_ENGINES_PKG.TECH_MESSAGE(
2214 p_severity => FND_LOG.level_procedure,
2215 p_module => C_MODULE,
2216 p_msg_text => 'Begin Procedure');
2217 END IF;
2218
2219 -- Init var
2220 v_passed_valid_out := c_true;
2221 v_passed := c_true;
2222
2223 -- Get table name and owner
2224 FEM_Database_Util_Pkg.Get_Table_Owner (
2225 x_return_status => v_return_status,
2226 x_msg_count => v_msg_count,
2227 x_msg_data => v_msg_data,
2228 p_syn_name => p_tab_name,
2229 x_tab_name => v_db_tab_name,
2230 x_tab_owner => v_tab_owner);
2231
2232 IF v_return_status <> c_success THEN
2233 IF FND_LOG.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2234 FEM_ENGINES_PKG.TECH_MESSAGE(
2235 p_severity => FND_LOG.level_statement,
2236 p_module => C_MODULE,
2237 p_msg_text => 'Call to FEM_Database_Util_Pkg.Get_Table_Owner failed');
2238 END IF;
2239 RAISE e_unexp;
2240 END IF;
2241
2242 -- Check if column requirements are met
2246 p_tab_owner => v_tab_owner,
2243 Validate_Column_Req (
2244 p_tab_name => p_tab_name,
2245 p_tab_class_cd => p_tab_class_cd,
2247 p_db_tab_name => v_db_tab_name,
2248 x_passed_validation => v_passed_valid_out);
2249
2250 IF v_passed_valid_out = c_false THEN
2251 v_passed := c_false;
2252 END IF;
2253
2254 -- Make sure the processing key columns matches up with a unique index
2255 /* Validate_Processing_Key_Idx (
2256 p_tab_name => p_tab_name,
2257 p_tab_class_cd => p_tab_class_cd,
2258 p_tab_owner => v_tab_owner,
2259 p_db_tab_name => v_db_tab_name,
2260 x_passed_validation => v_passed_valid_out);
2261 */
2262 IF v_passed_valid_out = c_false THEN
2263 v_passed := c_false;
2264 END IF;
2265
2266 FOR p IN c_class_prop (p_tab_class_cd) LOOP
2267 Validate_Prop_Col_Req (
2268 p_tab_name => p_tab_name,
2269 p_tab_class_cd => p_tab_class_cd,
2270 p_tab_prop_cd => p.table_property_code,
2271 p_prop_type => p.property_type,
2272 p_col_req_type => p.column_req_type,
2273 x_passed_validation => v_passed_valid_out);
2274
2275 IF v_passed_valid_out = c_false THEN
2276 v_passed := c_false;
2277 END IF;
2278 END LOOP;
2279
2280 -- Run other specified procedures for this table class
2281 FOR r IN c_procedure (p_tab_class_cd) LOOP
2282 v_procedure := r.stored_procedure_name;
2283
2284 IF (v_procedure = 'FEM_ADMIN_UTIL_PKG.VALIDATE_TABLE_NAME_RESTRICT') THEN
2285 Validate_Table_Name_Restrict (
2286 p_tab_name => p_tab_name,
2287 p_tab_class_cd => p_tab_class_cd,
2288 x_passed_validation => v_passed_valid_out);
2289 ELSIF (v_procedure = 'FEM_ADMIN_UTIL_PKG.VALIDATE_EDITABLE') THEN
2290 Validate_Editable (
2291 p_tab_name => p_tab_name,
2292 p_tab_class_cd => p_tab_class_cd,
2293 x_passed_validation => v_passed_valid_out);
2294 ELSIF (v_procedure = 'FEM_ADMIN_UTIL_PKG.VALIDATE_DATA_LOADER') THEN
2295 Validate_Data_Loader (
2296 p_tab_name => p_tab_name,
2297 p_tab_class_cd => p_tab_class_cd,
2298 p_tab_owner => v_tab_owner,
2299 p_db_tab_name => v_db_tab_name,
2300 x_passed_validation => v_passed_valid_out);
2301 ELSIF (v_procedure = 'FEM_ADMIN_UTIL_PKG.VALIDATE_PK_COLS_NOT_NULL') THEN
2302 Validate_PK_Cols_Not_Null (
2303 p_tab_name => p_tab_name,
2304 p_tab_class_cd => p_tab_class_cd,
2305 p_tab_owner => v_tab_owner,
2306 p_db_tab_name => v_db_tab_name,
2307 x_passed_validation => v_passed_valid_out);
2308 ELSIF (v_procedure = 'FEM_ADMIN_UTIL_PKG.VALIDATE_OGL_DIMCOL') THEN
2309 Validate_OGL_Dimcol (
2310 p_tab_name => p_tab_name,
2311 p_tab_class_cd => p_tab_class_cd,
2312 x_passed_validation => v_passed_valid_out);
2313 ELSIF (v_procedure = 'FEM_ADMIN_UTIL_PKG.VALIDATE_PROCESSING_KEY_IDX') THEN
2314 Validate_Processing_Key_Idx (
2315 p_tab_name => p_tab_name,
2316 p_tab_class_cd => p_tab_class_cd,
2317 p_tab_owner => v_tab_owner,
2318 p_db_tab_name => v_db_tab_name,
2319 x_passed_validation => v_passed_valid_out);
2320 ELSE
2321 IF FND_LOG.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2322 FEM_ENGINES_PKG.TECH_MESSAGE(
2323 p_severity => FND_LOG.level_statement,
2324 p_module => C_MODULE,
2325 p_msg_text => 'No such procedure: '||v_procedure);
2326 END IF;
2327 RAISE e_unexp;
2328 END IF;
2329
2330 IF v_passed_valid_out = c_false THEN
2331 v_passed := c_false;
2332 END IF;
2333 END LOOP;
2334
2335 x_passed_validation := v_passed;
2336
2337 EXCEPTION
2338 WHEN others THEN
2339 IF FND_LOG.level_exception >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2340 FEM_ENGINES_PKG.TECH_MESSAGE(
2341 p_severity => FND_LOG.level_exception,
2342 p_module => C_MODULE,
2343 p_msg_text => 'Validate_Tab_Class failed unexpectedly: '||SQLERRM);
2344 END IF;
2345 Log_Table_Class_Error(
2346 p_tab_name => p_tab_name,
2347 p_tab_class_cd => p_tab_class_cd,
2348 p_msg_name => 'FEM_RSM_UNEXPECTED_ERROR',
2349 p_token1 => 'ROUTINE_NAME',
2350 p_value1 => C_MODULE);
2351
2352 x_passed_validation := c_false;
2353
2354 END Validate_Tab_Class;
2355
2356
2357 PROCEDURE Validate_Tab_Class_Assignmt (
2358 p_api_version IN NUMBER DEFAULT c_api_version,
2359 p_init_msg_list IN VARCHAR2 DEFAULT c_false,
2360 p_commit IN VARCHAR2 DEFAULT c_false,
2361 p_encoded IN VARCHAR2 DEFAULT c_true,
2362 x_return_status OUT NOCOPY VARCHAR2,
2363 x_msg_count OUT NOCOPY NUMBER,
2364 x_msg_data OUT NOCOPY VARCHAR2,
2365 p_tab_name IN VARCHAR2
2366 )
2367 IS
2368 C_MODULE CONSTANT FND_LOG_MESSAGES.module%TYPE :=
2369 'fem.plsql.fem_admin_util_pkg.validate_tab_class_assignmt';
2370 C_API_NAME CONSTANT VARCHAR2(30) := 'Validate_Tab_Class_Assignmt';
2371
2375 CURSOR c_table_classes IS
2372 v_passed_validation VARCHAR2(1);
2373 v_tab_class_cd VARCHAR2(30);
2374
2376 SELECT lookup_code
2377 FROM fnd_lookup_values_vl
2378 WHERE lookup_type = 'FEM_TABLE_CLASSIFICATION_DSC';
2379
2380
2381 BEGIN
2382 IF FND_LOG.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2383 FEM_ENGINES_PKG.TECH_MESSAGE(
2384 p_severity => FND_LOG.level_procedure,
2385 p_module => C_MODULE,
2386 p_msg_text => 'Begin Procedure');
2387 END IF;
2388
2389 -- Initialize return status to unexpected error
2390 x_return_status := c_unexp;
2391
2392 -- Check for call compatibility.
2393 IF NOT FND_API.Compatible_API_Call (c_api_version,
2394 p_api_version,
2395 C_API_NAME,
2396 G_PKG_NAME)
2397 THEN
2398 IF FND_LOG.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2399 FEM_ENGINES_PKG.TECH_MESSAGE(
2400 p_severity => FND_LOG.level_statement,
2401 p_module => C_MODULE,
2402 p_msg_text => 'API Version ('||C_API_VERSION||') not compatible with '
2403 ||'passed in version ('||p_api_version||')');
2404 END IF;
2405 RAISE e_unexp;
2406 END IF;
2407
2408 -- Clean out the log tables before each run
2409 Trunc_Table_Class_Log_Tables(p_tab_name);
2410
2411 FOR table_classes IN c_table_classes LOOP
2412 Validate_Tab_Class (
2413 p_tab_name => p_tab_name,
2414 p_tab_class_cd => table_classes.lookup_code,
2415 x_passed_validation => v_passed_validation);
2416
2417 Log_Table_Class_Status(
2418 p_tab_name => p_tab_name,
2419 p_tab_class_cd => table_classes.lookup_code,
2420 p_passed_validation => v_passed_validation);
2421 END LOOP;
2422
2423 IF (p_commit = c_true) THEN
2424 COMMIT;
2425 END IF;
2426
2427 -- In case any messages are generated
2428 FND_MSG_PUB.Count_And_Get(p_encoded => p_encoded,
2429 p_count => x_msg_count,
2430 p_data => x_msg_data);
2431
2432 x_return_status := c_success;
2433
2434 IF FND_LOG.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2435 FEM_ENGINES_PKG.TECH_MESSAGE(
2436 p_severity => FND_LOG.level_procedure,
2437 p_module => C_MODULE,
2438 p_msg_text => 'End Procedure');
2439 END IF;
2440
2441 EXCEPTION
2442 WHEN others THEN
2443 IF FND_LOG.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2444 FEM_ENGINES_PKG.TECH_MESSAGE(
2445 p_severity => FND_LOG.level_statement,
2446 p_module => C_MODULE,
2447 p_msg_text => 'Unexpected error: '||SQLERRM);
2448 END IF;
2449 IF FND_LOG.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2450 FEM_ENGINES_PKG.TECH_MESSAGE(
2451 p_severity => FND_LOG.level_procedure,
2452 p_module => C_MODULE,
2453 p_msg_text => 'End Procedure');
2454 END IF;
2455 FND_MSG_PUB.Count_And_Get(p_encoded => p_encoded,
2456 p_count => x_msg_count,
2457 p_data => x_msg_data);
2458
2459 x_return_status := c_unexp;
2460
2461 END Validate_Tab_Class_Assignmt;
2462
2463
2464 /**************************************************************************/
2465
2466 /***************************************************************************
2467 ***************************************************************************
2468 * *
2469 * ============================= *
2470 * Procedure: Validate_obj_Class_Assignmt *
2471 * ============================= *
2472 * *
2473 ***************************************************************************
2474 ***************************************************************************
2475
2476 This procedure calls the Validate_Tab_Class_Assignmt if the object type is
2477 a table loads the classifications for views.
2478
2479 **************************************************************************/
2480
2481 PROCEDURE Validate_obj_Class_Assignmt (
2482 p_api_version IN NUMBER DEFAULT c_api_version,
2483 p_init_msg_list IN VARCHAR2 DEFAULT c_false,
2484 p_commit IN VARCHAR2 DEFAULT c_false,
2485 p_encoded IN VARCHAR2 DEFAULT c_true,
2486 x_return_status OUT NOCOPY VARCHAR2,
2487 x_msg_count OUT NOCOPY NUMBER,
2488 x_msg_data OUT NOCOPY VARCHAR2,
2489 p_obj_name IN VARCHAR2,
2490 p_obj_type IN VARCHAR2
2491 )
2492 IS
2493 C_MODULE CONSTANT FND_LOG_MESSAGES.module%TYPE :=
2494 'fem.plsql.fem_admin_util_pkg.validate_obj_class_assignmt';
2495 C_API_NAME CONSTANT VARCHAR2(30) := 'Validate_obj_Class_Assignmt';
2496
2497 v_passed_validation VARCHAR2(1);
2498 v_tab_class_cd VARCHAR2(30);
2499
2500 /* Select only those classifications for which properties do not need validation
2501 EXCEPT(
2505 */
2502 'FEM_ADMIN_UTIL_PKG.VALIDATE_TABLE_NAME_RESTRICT',
2503 'FEM_ADMIN_UTIL_PKG.VALIDATE_DATA_LOADER',
2504 'FEM_ADMIN_UTIL_PKG.VALIDATE_EDITABLE')
2506
2507 CURSOR c_table_classes IS
2508 SELECT lookup_code
2509 FROM fnd_lookup_values_vl flv
2510 WHERE flv.lookup_type = 'FEM_TABLE_CLASSIFICATION_DSC'
2511 AND not exists(
2512 select ftcp.table_classification_code
2513 from fem_table_class_prop ftcp,
2514 fem_table_prop_stp ftps
2515 where ftcp.table_classification_code = flv.lookup_code
2516 and ftcp.table_property_code = ftps.table_property_code
2517 and ftps.stored_procedure_name in(
2518 'FEM_ADMIN_UTIL_PKG.VALIDATE_TABLE_NAME_RESTRICT',
2519 'FEM_ADMIN_UTIL_PKG.VALIDATE_DATA_LOADER',
2520 'FEM_ADMIN_UTIL_PKG.VALIDATE_EDITABLE')
2521 );
2522
2523 BEGIN
2524
2525 IF p_obj_type = 'FEM_TABLE' THEN
2526
2527 Validate_Tab_Class_Assignmt (
2528 x_return_status=> x_return_status,
2529 x_msg_count => x_msg_count,
2530 x_msg_data => x_msg_data,
2531 p_tab_name => p_obj_name
2532 );
2533
2534 ELSE IF p_obj_type = 'FEM_VIEW' THEN
2535
2536 IF FND_LOG.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2537 FEM_ENGINES_PKG.TECH_MESSAGE(
2538 p_severity => FND_LOG.level_procedure,
2539 p_module => C_MODULE,
2540 p_msg_text => 'Begin Procedure');
2541 END IF;
2542
2543 -- Initialize return status to unexpected error
2544 x_return_status := c_unexp;
2545
2546 -- Check for call compatibility.
2547 IF NOT FND_API.Compatible_API_Call (c_api_version,
2548 p_api_version,
2549 C_API_NAME,
2550 G_PKG_NAME)
2551 THEN
2552 IF FND_LOG.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2553 FEM_ENGINES_PKG.TECH_MESSAGE(
2554 p_severity => FND_LOG.level_statement,
2555 p_module => C_MODULE,
2556 p_msg_text => 'API Version ('||C_API_VERSION||') not compatible with '
2557 ||'passed in version ('||p_api_version||')');
2558 END IF;
2559 RAISE e_unexp;
2560 END IF;
2561
2562 -- Clean out the log tables before each run
2563 Trunc_Table_Class_Log_Tables(p_obj_name);
2564
2565 FOR table_classes IN c_table_classes LOOP
2566
2567 Validate_View_Class (
2568 p_view_name => p_obj_name,
2569 p_tab_class_cd => table_classes.lookup_code,
2570 x_passed_validation => v_passed_validation);
2571
2572 Log_Table_Class_Status(
2573 p_tab_name => p_obj_name,
2574 p_tab_class_cd => table_classes.lookup_code,
2575 p_passed_validation => v_passed_validation);
2576
2577 END LOOP;
2578
2579
2580 IF (p_commit = c_true) THEN
2581 COMMIT;
2582 END IF;
2583
2584 -- In case any messages are generated
2585 FND_MSG_PUB.Count_And_Get(p_encoded => p_encoded,
2586 p_count => x_msg_count,
2587 p_data => x_msg_data);
2588
2589 x_return_status := c_success;
2590
2591 IF FND_LOG.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2592 FEM_ENGINES_PKG.TECH_MESSAGE(
2593 p_severity => FND_LOG.level_procedure,
2594 p_module => C_MODULE,
2595 p_msg_text => 'End Procedure');
2596 END IF;
2597
2598 END IF;
2599
2600 END IF;
2601 EXCEPTION
2602 WHEN others THEN
2603 IF FND_LOG.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2604 FEM_ENGINES_PKG.TECH_MESSAGE(
2605 p_severity => FND_LOG.level_statement,
2606 p_module => C_MODULE,
2607 p_msg_text => 'Unexpected error: '||SQLERRM);
2608 END IF;
2609 IF FND_LOG.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2610 FEM_ENGINES_PKG.TECH_MESSAGE(
2611 p_severity => FND_LOG.level_procedure,
2612 p_module => C_MODULE,
2613 p_msg_text => 'End Procedure');
2614 END IF;
2615 FND_MSG_PUB.Count_And_Get(p_encoded => p_encoded,
2616 p_count => x_msg_count,
2617 p_data => x_msg_data);
2618
2619 x_return_status := c_unexp;
2620
2621 END Validate_obj_Class_Assignmt;
2622
2623 /*============================================================================+
2624 | PROCEDURE
2625 | Get_Table_Owner_for_View
2626 |
2627 | DESCRIPTION
2628 | This Procedure returns the underlying base table and owner for passed view name
2629 |
2630 | SCOPE - PUBLIC
2631 +============================================================================*/
2632
2633 PROCEDURE Get_Table_Owner_for_View (
2634 p_api_version IN NUMBER DEFAULT c_api_version,
2635 p_init_msg_list IN VARCHAR2 DEFAULT c_false,
2636 p_commit IN VARCHAR2 DEFAULT c_false,
2637 p_encoded IN VARCHAR2 DEFAULT c_true,
2638 x_return_status OUT NOCOPY VARCHAR2,
2639 x_msg_count OUT NOCOPY NUMBER,
2640 x_msg_data OUT NOCOPY VARCHAR2,
2641 p_view_name IN VARCHAR2,
2642 x_tab_name OUT NOCOPY VARCHAR2,
2646 -- =========================================================================
2643 x_tab_owner OUT NOCOPY VARCHAR2 ) IS
2644 -- =========================================================================
2645 -- Returns the underlying table name and table owner for a specified user defined view for the selected index.
2647
2648 C_MODULE CONSTANT FND_LOG_MESSAGES.module%TYPE :=
2649 'fem.plsql.fem_admin_util_pkg.Get_Table_Owner_for_View';
2650 BEGIN
2651
2652 x_return_status := c_success;
2653
2654 ---------------------------------
2655 -- Get underlying table name and table owner
2656 ---------------------------------
2657 --need to think abt this
2658 SELECT tiig.table_name, tig.owner
2659 INTO x_tab_name,x_tab_owner
2660 FROM fem_tab_indx_info_gt tiig,
2661 fem_tables_b ftb,
2662 fem_tab_info_gt tig
2663 WHERE ftb.table_name = p_view_name
2664 AND ftb.PROC_KEY_INDEX_NAME = tiig.INDEX_NAME
2665 AND tig.table_name = tiig.table_name
2666 AND tig.owner is NOT NULL
2667 AND ROWNUM = 1;
2668
2669 EXCEPTION
2670
2671 WHEN no_data_found THEN
2672
2673 x_tab_owner:= NULL;
2674 x_tab_name:= NULL;
2675
2676 WHEN others THEN
2677
2678 FEM_ENGINES_PKG.TECH_MESSAGE(
2679 p_severity => FND_LOG.level_procedure,
2680 p_module => C_MODULE,
2681 p_msg_text => 'Unexpected Exception: '||SQLERRM);
2682
2683 x_return_status := c_error;
2684
2685 END Get_Table_Owner_for_view;
2686
2687 /*============================================================================+
2688 | PROCEDURE
2689 | Get_Index_Owner_for_View
2690 |
2691 | DESCRIPTION
2692 | This Procedure returns the underlying base table and Index owner for passed
2693 | view name and Index Name
2694 |
2695 | SCOPE - PUBLIC
2696 +============================================================================*/
2697
2698 PROCEDURE Get_Index_Owner_for_View (
2699 p_api_version IN NUMBER DEFAULT c_api_version,
2700 p_init_msg_list IN VARCHAR2 DEFAULT c_false,
2701 p_commit IN VARCHAR2 DEFAULT c_false,
2702 p_encoded IN VARCHAR2 DEFAULT c_true,
2703 x_return_status OUT NOCOPY VARCHAR2,
2704 x_msg_count OUT NOCOPY NUMBER,
2705 x_msg_data OUT NOCOPY VARCHAR2,
2706 p_view_name IN VARCHAR2,
2707 p_index_name IN VARCHAR2,
2708 x_tab_name OUT NOCOPY VARCHAR2,
2709 x_tab_owner OUT NOCOPY VARCHAR2 ) IS
2710 -- =========================================================================
2711 -- Returns the underlying table name and Index owner for a specified user defined view and the selected index.
2712 -- =========================================================================
2713
2714 C_MODULE CONSTANT FND_LOG_MESSAGES.module%TYPE :=
2715 'fem.plsql.fem_admin_util_pkg.Get_Index_Owner_for_View';
2716
2717
2718 BEGIN
2719
2720 x_return_status := c_success;
2721
2722 ---------------------------------
2723 -- Get underlying table name and table owner
2724 ---------------------------------
2725
2726 SELECT tiig.table_name, tig.owner
2727 INTO x_tab_name,x_tab_owner
2728 FROM fem_tab_indx_info_gt tiig,
2729 fem_tab_info_gt tig
2730 WHERE tig.table_name = tiig.table_name
2731 AND tiig.INDEX_NAME = p_index_name
2732 AND tig.owner is NOT NULL
2733 AND ROWNUM = 1;
2734
2735 EXCEPTION
2736
2737 WHEN no_data_found THEN
2738
2739 x_tab_owner:= NULL;
2740 x_tab_name:= NULL;
2741
2742 WHEN others THEN
2746 p_msg_text => 'Unexpected Exception: '||SQLERRM);
2743 FEM_ENGINES_PKG.TECH_MESSAGE(
2744 p_severity => FND_LOG.level_procedure,
2745 p_module => C_MODULE,
2747
2748 x_return_status := c_error;
2749
2750 END Get_Index_Owner_for_view;
2751
2752
2753 /***************************************************************************
2754 ***************************************************************************
2755 * *
2756 * ============================= *
2757 * Procedure: Validate View Class *
2758 * ============================= *
2762
2759 * *
2760 ***************************************************************************
2761 ***************************************************************************
2763 This procedure is a wrapper that calls other View validation APIs in
2764 this package.
2765
2766 **************************************************************************/
2767
2768 PROCEDURE Validate_View_Class (
2769 p_view_name IN VARCHAR2,
2770 p_tab_class_cd IN VARCHAR2,
2771 x_passed_validation OUT NOCOPY VARCHAR2
2772 )
2773 IS
2774 C_MODULE CONSTANT FND_LOG_MESSAGES.module%TYPE :=
2775 'fem.plsql.fem_admin_util_pkg.validate_view_class';
2776
2777 CURSOR c_class_prop (p_class VARCHAR2) IS
2778 SELECT p.table_property_code, p.property_type, p.column_req_type
2779 FROM fem_table_class_prop c, fem_table_properties p
2780 WHERE c.table_classification_code = p_class
2781 AND c.table_property_code = p.table_property_code
2782 AND p.property_type <> 'OTHER';
2783
2784 CURSOR c_procedure (p_class VARCHAR2) IS
2785 SELECT DISTINCT S.stored_procedure_name
2786 FROM fem_table_class_prop C,
2787 fem_table_prop_stp S
2788 WHERE C.table_classification_code = p_class
2789 AND C.table_property_code = S.table_property_code;
2790
2791 v_return_status VARCHAR2(1);
2792 v_passed_valid_out VARCHAR2(1);
2793 v_passed VARCHAR2(1);
2794 v_msg_count NUMBER;
2795 v_msg_data VARCHAR2(2000);
2796
2797 v_db_tab_name VARCHAR2(30);
2798 v_tab_owner VARCHAR2(30);
2799 v_procedure FEM_TABLE_PROP_STP.stored_procedure_name%TYPE;
2800 l_apps VARCHAR2(30):=USER;
2801
2802 BEGIN
2803 IF FND_LOG.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2804 FEM_ENGINES_PKG.TECH_MESSAGE(
2805 p_severity => FND_LOG.level_procedure,
2806 p_module => C_MODULE,
2807 p_msg_text => 'Begin Procedure');
2808 END IF;
2809
2810 -- Init var
2811 v_passed_valid_out := c_true;
2812 v_passed := c_true;
2813
2814 -----------------------------
2815 -- get the owner for the underlying base table of processing key.
2816 -- Get table name and owner
2817 ----------------------------
2818
2819 Get_Table_Owner_for_View (
2820 x_return_status => v_return_status,
2821 x_msg_count => v_msg_count,
2822 x_msg_data => v_msg_data,
2823 p_view_name => p_view_name,
2824 x_tab_name => v_db_tab_name,
2825 x_tab_owner => v_tab_owner);
2826
2827 IF v_return_status <> c_success THEN
2828 IF FND_LOG.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2829 FEM_ENGINES_PKG.TECH_MESSAGE(
2830 p_severity => FND_LOG.level_statement,
2831 p_module => C_MODULE,
2832 p_msg_text => 'Call to Get_Table_Owner_for_view failed');
2833 END IF;
2834 RAISE e_unexp;
2835 END IF;
2836
2837 -- Check if column requirements are met
2838 Validate_Column_Req (
2839 p_tab_name => p_view_name,
2840 p_tab_class_cd => p_tab_class_cd,
2841 p_tab_owner => l_apps,-- pass owner as 'APPS' because we will check for view columns and pass
2842 p_db_tab_name => p_view_name,--p_db_tab_name = view_name because it will be mapped to all_tab_columns
2843 x_passed_validation => v_passed_valid_out);
2844
2845 IF v_passed_valid_out = c_false THEN
2846 v_passed := c_false;
2847 END IF;
2848
2849 -- Make sure the processing key columns matches up with a unique index
2850 -- This validation is done in the last
2851 /* Validate_Processing_Key_Idx (
2852 p_tab_name => p_view_name,
2853 p_tab_class_cd => p_tab_class_cd,
2854 p_tab_owner => v_tab_owner,
2855 p_db_tab_name => v_db_tab_name,
2856 x_passed_validation => v_passed_valid_out);
2857 */
2858
2859 IF v_passed_valid_out = c_false THEN
2860 v_passed := c_false;
2861 END IF;
2862
2863 FOR p IN c_class_prop (p_tab_class_cd) LOOP
2864 Validate_Prop_Col_Req (
2865 p_tab_name => p_view_name,
2866 p_tab_class_cd => p_tab_class_cd,
2867 p_tab_prop_cd => p.table_property_code,
2868 p_prop_type => p.property_type,
2869 p_col_req_type => p.column_req_type,
2870 x_passed_validation => v_passed_valid_out);
2871
2872 IF v_passed_valid_out = c_false THEN
2873 v_passed := c_false;
2874 END IF;
2875 END LOOP;
2876
2877 -- Run other specified procedures for this table class
2878 FOR r IN c_procedure (p_tab_class_cd) LOOP
2879 v_procedure := r.stored_procedure_name;
2880
2881 IF (v_procedure = 'FEM_ADMIN_UTIL_PKG.VALIDATE_PK_COLS_NOT_NULL') THEN
2882 Validate_PK_Cols_Not_Null (
2883 p_tab_name => p_view_name,
2884 p_tab_class_cd => p_tab_class_cd,
2885 p_tab_owner => l_apps,
2886 p_db_tab_name => p_view_name,
2887 x_passed_validation => v_passed_valid_out);
2888
2889 ELSIF (v_procedure = 'FEM_ADMIN_UTIL_PKG.VALIDATE_PROCESSING_KEY_IDX') THEN
2890 Validate_Processing_Key_Idx (
2891 p_tab_name => p_view_name,
2892 p_tab_class_cd => p_tab_class_cd,
2893 p_tab_owner => v_tab_owner,
2894 p_db_tab_name => v_db_tab_name,
2895 x_passed_validation => v_passed_valid_out);
2896
2897 ELSIF(FND_LOG.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2898 FEM_ENGINES_PKG.TECH_MESSAGE(
2899 p_severity => FND_LOG.level_statement,
2900 p_module => C_MODULE,
2901 p_msg_text => 'No such procedure: '||v_procedure);
2902
2906 END IF;
2903 RAISE e_unexp;
2904
2905
2907
2908 IF v_passed_valid_out = c_false THEN
2909 v_passed := c_false;
2910 END IF;
2911 END LOOP;
2912
2913 x_passed_validation := v_passed;
2914
2915 EXCEPTION
2916 WHEN others THEN
2917 IF FND_LOG.level_exception >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2918 FEM_ENGINES_PKG.TECH_MESSAGE(
2919 p_severity => FND_LOG.level_exception,
2920 p_module => C_MODULE,
2921 p_msg_text => 'Validate_Tab_Class failed unexpectedly: '||SQLERRM);
2922 END IF;
2923 Log_Table_Class_Error(
2924 p_tab_name => p_view_name,
2925 p_tab_class_cd => p_tab_class_cd,
2926 p_msg_name => 'FEM_RSM_UNEXPECTED_ERROR',
2927 p_token1 => 'ROUTINE_NAME',
2928 p_value1 => C_MODULE);
2929
2930 x_passed_validation := c_false;
2931
2932 END Validate_view_Class;
2933
2934
2935 /**************************************************************************/
2936
2937
2938 END FEM_Admin_Util_Pkg;