[Home] [Help]
PACKAGE BODY: APPS.FEM_INTF_DIM_VALIDATION_PKG
Source
1 PACKAGE BODY FEM_INTF_DIM_VALIDATION_PKG AS
2 /* $Header: fem_intf_val_eng.plb 120.1 2006/08/14 11:42:50 hkaniven ship $*/
3
4 -- ======================================================================
5 -- Private Package Variables
6 -- ======================================================================
7
8 pc_log_level_statement CONSTANT NUMBER := FND_LOG.level_statement;
9 pc_log_level_procedure CONSTANT NUMBER := FND_LOG.level_procedure;
10 pc_log_level_event CONSTANT NUMBER := FND_LOG.level_event;
11 pc_log_level_exception CONSTANT NUMBER := FND_LOG.level_exception;
12 pc_log_level_error CONSTANT NUMBER := FND_LOG.level_error;
13 pc_log_level_unexpected CONSTANT NUMBER := FND_LOG.level_unexpected;
14 pc_date_format CONSTANT VARCHAR2(10) := 'DD-MM-YYYY';
15 pc_default_dim_grp_size CONSTANT NUMBER := 5;
16
17 pc_req_id CONSTANT NUMBER := FND_GLOBAL.Conc_Request_Id ;
18 pc_user_id CONSTANT NUMBER := FND_GLOBAL.User_Id;
19 pc_login_id CONSTANT NUMBER := FND_GLOBAL.Login_Id;
20 pc_pgm_id CONSTANT NUMBER := FND_GLOBAL.Conc_Program_Id;
21 pc_pgm_app_id CONSTANT NUMBER := FND_GLOBAL.Prog_Appl_ID;
22
23 pv_num_rec_to_print NUMBER := 500;
24 pv_log_current_level NUMBER;
25 pv_ledger_id NUMBER;
26 pv_cal_period_id NUMBER;
27 pv_dataset_code NUMBER;
28 pv_source_system_code NUMBER;
29 pv_num_dims NUMBER;
30 pv_table_name VARCHAR2(30);
31 pv_interface_table_name VARCHAR2(30);
32 pv_print_report BOOLEAN := FALSE;
33 pv_cal_per_number FEM_CAL_PERIODS_ATTR.number_assign_value%TYPE;
34 pv_ledger_dc FEM_LEDGERS_B.ledger_display_code%TYPE;
35 pv_time_dim_grp_dc FEM_DIMENSION_GRPS_B.dimension_group_display_code%TYPE;
36 pv_cal_per_end_date FEM_CAL_PERIODS_ATTR.date_assign_value%TYPE;
37 pv_dataset_dc FEM_DATASETS_B.dataset_display_code%TYPE;
38 pv_source_system_dc FEM_SOURCE_SYSTEMS_B.source_system_display_code%TYPE;
39 pv_num_rows NUMBER;
40
41 pv_obj_def_id NUMBER;
42
43 TYPE xdim_info_rec IS RECORD
44 (
45 int_disp_code_col FEM_INT_COLUMN_MAP.interface_column_name%TYPE,
46 vs_required_flag FEM_XDIM_DIMENSIONS.value_set_required_flag%TYPE,
47 vs_id FEM_GLOBAL_VS_COMBO_DEFS.value_set_id%TYPE,
48 member_b_table_name FEM_XDIM_DIMENSIONS.member_b_table_name%TYPE,
49 member_col FEM_XDIM_DIMENSIONS.member_col%TYPE,
50 member_disp_code_col FEM_XDIM_DIMENSIONS.member_display_code_col%TYPE,
51 target_col_data_type FEM_XDIM_DIMENSIONS.member_data_type_code%TYPE,
52 target_col FEM_TAB_COLUMNS_V.column_name%TYPE);
53
54 TYPE xdim_info_table_type IS TABLE OF xdim_info_rec INDEX BY BINARY_INTEGER;
55
56 pv_xdim_info_tbl xdim_info_table_type;
57 e_inv_obj_def EXCEPTION;
58
59 G_API_VERSION CONSTANT NUMBER := 1.0;
60 G_RET_STS_SUCCESS CONSTANT VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
61 G_RET_STS_ERROR CONSTANT VARCHAR2(1) := FND_API.G_RET_STS_ERROR;
62 G_RET_STS_UNEXP_ERROR CONSTANT VARCHAR2(1) := FND_API.G_RET_STS_UNEXP_ERROR;
63
64 -- ======================================================================
65 -- Procedure
66 -- Main
67 --
68 -- Purpose
69 -- This is the main procedure that controls the flow of the program
70 --
71 -- It initializes the package level variables and then calls the
72 -- sub-routines to validate parameters and to validate dimensional
73 -- information of the interface records.
74 --
75 -- If any of the sub-routine returns with an error status, this routine
76 -- will end the concurrent program in an error status with the
77 -- appropriate message.
78 --
79 -- Arguments
80 -- x_errbuf : Standard Concurrent Program parameter
81 -- x_retcode : Standard Concurrent Program parameter
82 -- p_obj_def_id : Detail Client Data Table Name
83 -- p_ledger_id : Ledger ID
84 -- p_cal_period_id : Calendar Period ID
85 -- p_dataset_code : Dataset Code
86 -- p_source_system_code : Source System Code
87 -- p_num_rows : Number of rows to be validated
88 -- p_print_report_flag : Flag indicating if program should print
89 -- errors to concurrent output file
90 -- p_num_rec_to_print : Maximum number of errors to print to the
91 -- concurrent output file
92 --
93 -- HISTORY
94 -- 04-21-06 Harikiran Bug 5115380 - Inserted Value Too Large for Col
95 -- 07-18-06 Harikiran Bug 5398129 - No_data_found case will show up a
96 -- new message and end with a 'WARNING'
97 -- status and Invalid Dimensions found
98 -- case will end with a 'ERROR'
99 -- Formatted the reporting sql that is
100 -- displayed in the request log
101 -- 07-24-06 Harikiran Bug 5406315 - Max value of records that can be
102 -- validated and printed increased to
103 -- 99999 from 9999
104 --
105 -- ======================================================================
106
107 PROCEDURE Main (
108 x_errbuf OUT NOCOPY VARCHAR2,
109 x_retcode OUT NOCOPY VARCHAR2,
110 p_obj_def_id IN VARCHAR2,
111 p_ledger_id IN VARCHAR2,
112 p_cal_period_id IN VARCHAR2,
113 p_dataset_code IN VARCHAR2,
114 p_source_system_code IN VARCHAR2,
115 p_num_rows IN VARCHAR2,
116 p_print_report_flag IN VARCHAR2 default 'N',
117 p_num_rec_to_print IN VARCHAR2 default '500'
118 ) IS
119
120 vc_module CONSTANT FND_LOG_MESSAGES.module%TYPE :=
121 'fem.plsql.fem_intf_dim_validation_pkg.main';
122
123 v_no_err_code VARCHAR2(80);
124 v_rec_count NUMBER;
125 v_id_num NUMBER;
126 v_warning_status BOOLEAN := FALSE;
127 v_has_id_number BOOLEAN := TRUE;
128 v_dummy_boolean BOOLEAN;
129 v_sql_stmt VARCHAR2(32767);
130 v_dummy VARCHAR2(1);
131 v_rowid ROWID;
132
133 TYPE v_err_rec_type IS REF CURSOR;
134 v_err_records v_err_rec_type;
135
136 v_dimension_name VARCHAR2(32767);
137 v_return_status VARCHAR2(1);
138 v_completion_code NUMBER;
139 v_start NUMBER;
140 v_old NUMBER;
141 v_end NUMBER;
142 v_next_occurrence NUMBER;
143 v_dimension_name_part VARCHAR2(200);
144 v_req_id FND_CONCURRENT_REQUESTS.request_id%TYPE;
145 v_main_sql_stmt VARCHAR2(32767);
146 v_table_update_stmt VARCHAR2(32767);
147 v_table_select_stmt VARCHAR2(32767);
148 v_count NUMBER;
149 -- Bug 5398129 hkaniven start
150 v_no_data_found BOOLEAN := FALSE;
151 v_invalid_dims_found BOOLEAN := FALSE;
152 v_no_of_chars NUMBER;
153 -- Bug 5398129 hkaniven end
154
155
156 FEM_PGM_fatal_err EXCEPTION;
157
158 BEGIN
159
160 pv_log_current_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
161 v_return_status := G_RET_STS_UNEXP_ERROR;
162
163 IF pc_log_level_procedure >= pv_log_current_level THEN
164 FEM_ENGINES_PKG.Tech_Message
165 ( p_severity => pc_log_level_procedure,
166 p_module => vc_module,
167 p_app_name => 'FEM',
168 p_msg_name => 'FEM_GL_POST_201',
169 p_token1 => 'FUNC_NAME',
170 p_value1 => vc_module ,
171 p_token2 => 'TIME',
172 p_value2 => TO_CHAR(SYSDATE)||' '||TO_CHAR(SYSDATE,'HH24:MI:SS'));
173 END IF;
174
175 --
176 -- Initalize package level Variables
177 --
178 pv_ledger_id := to_number(p_ledger_id);
179 pv_cal_period_id := to_number(p_cal_period_id);
180 pv_dataset_code := to_number(p_dataset_code);
181 pv_source_system_code := to_number(p_source_system_code);
182 pv_obj_def_id := to_number(p_obj_def_id);
183 pv_num_rec_to_print := NULL;
184 pv_num_rows := NULL;
185
186 IF p_print_report_flag = 'Y' THEN
187 pv_print_report := TRUE;
188 ELSE
189 pv_print_report := FALSE;
190 END IF;
191
192
193 --
194 -- Validate all input parameters
195 --
196 Validate_Params(x_completion_code => v_completion_code);
197
198 --
199 -- If any of the parameters is invalid error out the program
200 --
201 IF v_completion_code = 1 OR v_completion_code = 2 THEN
202 RAISE FEM_PGM_fatal_err;
203 END IF;
204
205 --
206 -- Check whether p_num_rec_to_print is a valid positive number or not
207 --
208 IF p_num_rec_to_print IS NOT NULL THEN
209 Is_Number(p_num_rec_to_print, pv_num_rec_to_print);
210 ELSE
211 pv_num_rec_to_print := 99999; --Bug 5406315 hkaniven
212 END IF;
213
214 --
215 -- Check whether p_num_rows is a valid positive number or not
216 --
217 IF p_num_rows IS NOT NULL THEN
218 Is_Number(p_num_rows, pv_num_rows);
219 ELSE
220 pv_num_rows := 99999; --Bug 5406315 hkaniven
221 END IF;
222
223 --
224 -- Error out the program if pv_num_rows in not a valid positive number
225 --
226 IF pv_num_rows IS NULL OR pv_num_rows = 0 THEN
227 FEM_ENGINES_PKG.USER_MESSAGE(
228 p_app_name => 'FEM',
229 p_msg_name => 'FEM_INTF_DIM_INVALID_POS_NUM',
230 p_token1 => 'PARAM_NAME',
231 p_value1 => 'FEM_INTF_DIM_PARAM_NAME1',
232 p_trans1 => 'Y');
233
234 RAISE FEM_PGM_fatal_err;
235 END IF;
236
237 --
238 -- Error out the program if pv_num_rec_to_print in not a valid positive number
239 --
240 IF pv_num_rec_to_print IS NULL THEN
241 FEM_ENGINES_PKG.USER_MESSAGE(
242 p_app_name => 'FEM',
243 p_msg_name => 'FEM_INTF_DIM_INVALID_POS_NUM',
244 p_token1 => 'PARAM_NAME',
245 p_value1 => 'FEM_INTF_DIM_PARAM_NAME2',
246 p_trans1 => 'Y');
247
248 RAISE FEM_PGM_fatal_err;
249 END IF;
250
251 --Bug 5406315 hkaniven start
252 IF pv_num_rows = 99999 THEN
253 FEM_ENGINES_PKG.User_Message
254 (p_app_name => 'FEM',
255 p_msg_name => 'FEM_INTF_PARAM_DEF_VAL',
256 p_token1 => 'PARAM_NAME',
257 p_value1 => 'Number of records to be Validated');
258
259 FEM_ENGINES_PKG.Tech_Message
260 (p_severity => pc_log_level_statement,
261 p_module => vc_module,
262 p_app_name => 'FEM',
263 p_msg_name => 'FEM_INTF_PARAM_DEF_VAL',
264 p_token1 => 'PARAM_NAME',
265 p_value1 => 'Number of records to be Validated');
266 END IF;
267
268 IF pv_num_rec_to_print = 99999 THEN
269 FEM_ENGINES_PKG.User_Message
270 (p_app_name => 'FEM',
271 p_msg_name => 'FEM_INTF_PARAM_DEF_VAL',
272 p_token1 => 'PARAM_NAME',
273 p_value1 => 'Number of records to be Printed');
274
275 FEM_ENGINES_PKG.Tech_Message
276 (p_severity => pc_log_level_statement,
277 p_module => vc_module,
278 p_app_name => 'FEM',
279 p_msg_name => 'FEM_INTF_PARAM_DEF_VAL',
280 p_token1 => 'PARAM_NAME',
281 p_value1 => 'Number of records to be Printed');
282 END IF;
283 --Bug 5406315 hkaniven end
284
285
286 FEM_ENGINES_PKG.User_Message
287 (p_app_name => 'FEM',
288 p_msg_name => 'FEM_INTF_VALIDATE_ERR_NUM',
289 p_token1 => 'NUM',
290 p_value1 => pv_num_rows);
291 FEM_ENGINES_PKG.User_Message
292 (p_app_name => 'FEM',
293 p_msg_name => 'FEM_INTF_PRINT_ERR_NUM',
294 p_token1 => 'NUM',
295 p_value1 => pv_num_rec_to_print);
296
297
298 -- Bug 5398129 hkaniven start
299 -- List out the parameter values
300 FEM_ENGINES_PKG.User_Message
301 (p_app_name => 'FEM',
302 p_msg_name => 'FEM_INTF_PARAMS',
303 p_token1 => 'DIM_GRP',
304 p_value1 => pv_time_dim_grp_dc,
305 p_token2 => 'PER_NUM',
306 p_value2 => pv_cal_per_number,
307 p_token3 => 'END_DATE',
308 p_value3 => pv_cal_per_end_date,
309 p_token4 => 'LEDGER_DC',
310 p_value4 => pv_ledger_dc,
311 p_token5 => 'DATASET_DC',
312 p_value5 => pv_dataset_dc,
313 p_token6 => 'SOURCE_DC',
314 p_value6 => pv_source_system_dc,
315 p_token7 => 'NUM_ROWS',
316 p_value7 => pv_num_rows,
317 p_token8 => 'NUM_REC_TO_PRINT',
318 p_value8 => pv_num_rec_to_print,
319 p_token9 => 'TABLE_NAME',
320 p_value9 => pv_table_name);
321
322 FEM_ENGINES_PKG.Tech_Message
323 (p_severity => pc_log_level_statement,
324 p_module => vc_module,
325 p_app_name => 'FEM',
326 p_msg_name => 'FEM_INTF_PARAMS',
327 p_token1 => 'DIM_GRP',
328 p_value1 => pv_time_dim_grp_dc,
329 p_token2 => 'PER_NUM',
330 p_value2 => pv_cal_per_number,
331 p_token3 => 'END_DATE',
332 p_value3 => pv_cal_per_end_date,
333 p_token4 => 'LEDGER_DC',
334 p_value4 => pv_ledger_dc,
335 p_token5 => 'DATASET_DC',
336 p_value5 => pv_dataset_dc,
337 p_token6 => 'SOURCE_DC',
338 p_value6 => pv_source_system_dc,
339 p_token7 => 'NUM_ROWS',
340 p_value7 => pv_num_rows,
341 p_token8 => 'NUM_REC_TO_PRINT',
342 p_value8 => pv_num_rec_to_print,
343 p_token9 => 'TABLE_NAME',
344 p_value9 => pv_table_name);
345
346 -- Bug 5398129 hkaniven end
347
348 FEM_ENGINES_PKG.USER_MESSAGE(
349 p_app_name => 'FEM',
350 p_msg_text => 'PRINT_REPORT: ' || p_print_report_flag );
351
352 FND_FILE.put_line(FND_FILE.LOG,null);
353
354 --
355 -- Validate the dimensional information of all interface records
356 --
357 Validate_Dims(x_completion_code => v_completion_code);
358
359 -- Bug 5398129 hkaniven start
360 IF v_completion_code = 1 THEN
361 v_no_data_found := TRUE;
362 ELSIF v_completion_code = 2 THEN
363 v_invalid_dims_found := TRUE;
364 ELSIF v_completion_code = 3 THEN
365 RAISE FEM_PGM_fatal_err;
366 END IF;
367 -- Bug 5398129 hkaniven end
368
369 --
370 -- Only if there are invalid dimension values populate the fem_interface_fact_errs
371 -- table and output the report containing invalid dimension values.
372 --
373 IF v_invalid_dims_found THEN
374 --
375 -- Check if the fact table contains the column ID_NUMBER
376 --
377 BEGIN
378 v_sql_stmt := 'SELECT ''y''
379 FROM FEM_TAB_COLUMNS_B
380 WHERE table_name = '''||upper(pv_table_name)||'''
381 AND column_name = ''ID_NUMBER''';
382
383 IF pc_log_level_statement >= pv_log_current_level THEN
384 FEM_ENGINES_PKG.TECH_MESSAGE(
385 p_severity => pc_log_level_statement,
386 p_module => vc_module,
387 p_msg_text => 'SQL to check if ID_NUMBER column exist '||v_sql_stmt);
388 END IF;
389
390 EXECUTE IMMEDIATE v_sql_stmt into v_dummy;
391
392 EXCEPTION
393 WHEN NO_DATA_FOUND THEN
394 v_has_id_number := FALSE;
395 END;
396
397 -- Update Statement to transfer invalid values to the fem_interface_fact_errs will
398 -- be constructed as two parts
399 -- v_table_update_stmt - contains the first part - the columns which have to be
400 -- updated.
401 -- v_table_select_stmt - contains the second part - the invalid values with which
402 -- the columns will be updated
403 -- The full statement will be v_main_sql_stmt
404
405 --
406 -- Sample v_main_sql_stmt for the first five dimensions of 'FEM_CHECKING' table
407 --
408 -- UPDATE
409 -- FEM_INTERFACE_FACT_ERRS fide
410 -- SET
411 -- (
412 -- ID_NUMBER ,
413 -- DIM1_NAME ,
414 -- DIM1_VALUE ,
415 -- DIM2_NAME ,
416 -- DIM2_VALUE ,
417 -- DIM3_NAME ,
418 -- DIM3_VALUE ,
419 -- DIM4_NAME ,
420 -- DIM4_VALUE ,
421 -- DIM5_NAME ,
422 -- DIM5_VALUE
423 -- )
424 -- =
425 -- (
426 -- SELECT
427 -- id_number ,
428 -- 'CURRENCY_CODE',
429 -- DECODE(substr(error_code,1,1),'1',CURRENCY_CODE, NULL) ,
430 -- 'ATM_CARD_FLG',
431 -- DECODE(substr(error_code,2,1),'1',ATM_CARD_FLG, NULL) ,
432 -- 'CREDIT_LINE_FLG',
433 -- DECODE(substr(error_code,3,1),'1',CREDIT_LINE_FLG, NULL) ,
434 -- 'EMBEDDED_OPTIONS_FLG',
435 -- DECODE(substr(error_code,4,1),'1',EMBEDDED_OPTIONS_FLG, NULL) ,
436 -- 'JOINT_ACCOUNT_FLG',
437 -- DECODE(substr(error_code,5,1),'1',JOINT_ACCOUNT_FLG, NULL) ,
438 -- 'OPEN_ACCOUNT_FLG'
439 -- FROM FEM_CHECKING_T t
440 -- WHERE t.rowid = fide.interface_rowid
441 -- )
442 -- WHERE fide.request_id = 3171104
443 --
444
445 v_table_update_stmt := 'UPDATE FEM_INTERFACE_FACT_ERRS fide SET (ID_NUMBER ';
446
447 --
448 -- Build v_sql_stmt - dynamic SQL statement to get problematic interface records
449 -- from the fem_interface_fact_errs table
450 -- This statement will be executed to display the problematic interface records
451 -- stored in the fem_interface_fact_errs table, into the conc request output file
452 --
453 -- Sample reporting stmt - v_sql_stmt
454 --
455 -- SELECT
456 -- id_number,
457 -- DECODE(substr(error_code,1,1),'1',DIM1_NAME || '(' || DIM1_VALUE || '), ', NULL)
458 -- || DECODE(substr(error_code,2,1),'1',DIM2_NAME || '(' || DIM2_VALUE || '), ', NULL)
459 -- || DECODE(substr(error_code,3,1),'1',DIM3_NAME || '(' || DIM3_VALUE || ' ), ', NULL)
460 -- || DECODE(substr(error_code,4,1),'1',DIM4_NAME || '(' || DIM4_VALUE || '), ', NULL)
461 -- || DECODE(substr(error_code,5,1),'1',DIM5_NAME || '(' || DIM5_VALUE || '), ', NULL)
462 -- FROM fem_interface_dim _errs
463 -- WHERE request_id = 3119007
464 -- AND rownum <= 2
465 --
466
467 --
468 -- Build v_table_select_stmt - dynamic SQL statement to locate problematic interface
469 -- records data from the detail client interface table.
470 -- This statement will form the second part of the Update statement which will
471 -- populate invalid data into the fem_interface_fact_errs table.
472 --
473
474 FOR v_dim_index IN 1..pv_num_dims LOOP
475 IF v_dim_index = 1 THEN
476 IF v_has_id_number THEN
477 v_sql_stmt := 'SELECT id_number,';
478 v_table_select_stmt := ' = (SELECT id_number ';
479 ELSE
480 v_sql_stmt := 'SELECT interface_rowid,';
481 v_table_select_stmt := ' = (SELECT NULL ';
482 END IF;
483 ELSE
484 v_sql_stmt := v_sql_stmt||'|| ';
485 END IF;
486
487 v_sql_stmt := v_sql_stmt||' DECODE(substr(error_code,'||to_char(v_dim_index)||',1),''1'','|| 'DIM' || to_char(v_dim_index) || '_NAME || ''(''' || ' || ' ;
488 v_table_select_stmt := v_table_select_stmt || ', ' || '''' || pv_xdim_info_tbl(v_dim_index).int_disp_code_col || ''', ' ||
489 ' DECODE(substr(error_code,'||to_char(v_dim_index)||',1),''1'',' ;
490
491 v_sql_stmt := v_sql_stmt || 'DIM' || to_char(v_dim_index) || '_VALUE' || ' || ' || ''')'', NULL) ';
492 v_table_select_stmt := v_table_select_stmt || pv_xdim_info_tbl(v_dim_index).int_disp_code_col || ', NULL) ';
493
494 v_table_update_stmt := v_table_update_stmt || ', DIM' || v_dim_index || '_NAME '
495 || ', DIM' || v_dim_index || '_VALUE ';
496
497 END LOOP;
498
499 v_table_update_stmt := v_table_update_stmt || ' ) ';
500
501 v_sql_stmt := v_sql_stmt ||
502 'FROM FEM_INTERFACE_FACT_ERRS '
503 || ' WHERE request_id = ' || pc_req_id
504 || ' AND rownum <= ' || pv_num_rec_to_print;
505
506 v_table_select_stmt := v_table_select_stmt ||
507 ' FROM '|| pv_interface_table_name || ' t '
508 || ' WHERE t.rowid = fide.interface_rowid )';
509
510
511 --
512 -- Form the full update statement by combining the the two parts
513 --
514 v_main_sql_stmt := v_table_update_stmt || v_table_select_stmt
515 || ' WHERE fide.request_id = ' || pc_req_id ;
516
517 IF pc_log_level_statement >= pv_log_current_level THEN
518 FEM_ENGINES_PKG.TECH_MESSAGE(
519 p_severity => pc_log_level_statement,
520 p_module => vc_module,
521 p_msg_text => 'SQL to report is ');
522 END IF;
523
524 FND_FILE.put_line(FND_FILE.LOG,null);
525
526 FEM_ENGINES_PKG.USER_MESSAGE(
527 p_app_name => 'FEM',
528 p_msg_text => 'SQL to report is ');
529
530 FND_FILE.put_line(FND_FILE.LOG,null);
531
532 -- Bug 5398129 hkaniven start
533 v_count := 1;
534 LOOP
535 v_no_of_chars := INSTR(v_sql_stmt, 'NULL)', v_count);
536
537 IF v_no_of_chars = 0 THEN
538
539 IF pc_log_level_statement >= pv_log_current_level THEN
540 FEM_ENGINES_PKG.TECH_MESSAGE(
541 p_severity => pc_log_level_statement,
542 p_module => vc_module,
543 p_msg_text => SUBSTR(v_sql_stmt,v_count,LENGTH(v_sql_stmt) - v_count + 1));
544 END IF;
545
546 FEM_ENGINES_PKG.USER_MESSAGE(
547 p_app_name => 'FEM',
548 p_msg_text => SUBSTR(v_sql_stmt,v_count,LENGTH(v_sql_stmt) - v_count + 1));
549
550 EXIT;
551 END IF;
552
553 v_no_of_chars := v_no_of_chars - v_count + 5 ;
554
555
556 IF pc_log_level_statement >= pv_log_current_level THEN
557 FEM_ENGINES_PKG.TECH_MESSAGE(
558 p_severity => pc_log_level_statement,
559 p_module => vc_module,
560 p_msg_text => substr(v_sql_stmt,v_count,v_no_of_chars));
561 END IF;
562
563 FEM_ENGINES_PKG.USER_MESSAGE(
564 p_app_name => 'FEM',
565 p_msg_text => substr(v_sql_stmt,v_count,v_no_of_chars));
566
567 v_count := v_count + v_no_of_chars ;
568
569 END LOOP;
570 -- Bug 5398129 hkaniven end
571
572 IF pc_log_level_statement >= pv_log_current_level THEN
573 FEM_ENGINES_PKG.TECH_MESSAGE(
574 p_severity => pc_log_level_statement,
575 p_module => vc_module,
576 p_msg_text => 'SQL to update target table is ');
577 END IF;
578
579 v_count := 1;
580 LOOP
581 IF pc_log_level_statement >= pv_log_current_level THEN
582 FEM_ENGINES_PKG.TECH_MESSAGE(
583 p_severity => pc_log_level_statement,
584 p_module => vc_module,
585 p_msg_text => substr(v_main_sql_stmt,v_count,255));
586 END IF;
587
588 EXIT WHEN LENGTH(v_main_sql_stmt) < v_count ;
589 v_count := v_count + 255;
590 END LOOP;
591
592 EXECUTE IMMEDIATE v_main_sql_stmt;
593
594 COMMIT;
595
596 --
597 -- Print out invalid dimensions in concurrent program output file
598 --
599
600 IF pv_print_report THEN
601 FND_MESSAGE.set_name('FEM','FEM_INTF_VALIDATE_ERR_NUM');
602 FND_MESSAGE.set_token('NUM',pv_num_rows);
603 FND_FILE.put_line(FND_FILE.OUTPUT, FND_MESSAGE.get);
604 FND_MESSAGE.set_name('FEM','FEM_INTF_PRINT_ERR_NUM');
605 FND_MESSAGE.set_token('NUM',pv_num_rec_to_print);
606 FND_FILE.put_line(FND_FILE.OUTPUT, FND_MESSAGE.get);
607
608 FND_FILE.put_line(FND_FILE.OUTPUT,null);
609
610 IF v_has_id_number THEN
611 FND_MESSAGE.set_name('FEM','FEM_INTF_VALIDATE_REP1');
612 FND_FILE.put_line(FND_FILE.OUTPUT, FND_MESSAGE.get);
613 ELSE
614 FND_MESSAGE.set_name('FEM','FEM_INTF_VALIDATE_REP2');
615 FND_FILE.put_line(FND_FILE.OUTPUT, FND_MESSAGE.get);
616 END IF;
617
618 FND_FILE.put_line(FND_FILE.OUTPUT,'------------------ ----------------------------------------------------------------------------------------------------------------');
619 FND_FILE.put_line(FND_FILE.OUTPUT,null);
620
621 v_rec_count := 0;
622
623 IF v_has_id_number THEN
624
625 OPEN v_err_records FOR v_sql_stmt;
626 LOOP
627 FETCH v_err_records INTO v_id_num, v_dimension_name;
628 EXIT WHEN v_err_records%NOTFOUND or
629 v_rec_count > pv_num_rec_to_print;
630
631 --
632 -- Print only one full dimension name/value pair on a single line in the
633 -- output report listing
634 --
635 v_start := 1;
636
637 LOOP
638 v_end := INSTR(v_dimension_name, ')', v_start, 1);
639 EXIT WHEN v_end = 0;
640
641 v_dimension_name_part := SUBSTR(v_dimension_name, v_start, v_end - v_start + 1);
642 EXIT WHEN v_dimension_name_part IS NULL;
643
644 IF v_start = 1 THEN
645 FND_FILE.put_line(FND_FILE.OUTPUT, rpad(v_id_num,20,' ') || v_dimension_name_part);
646 ELSE
647 FND_FILE.put_line(FND_FILE.OUTPUT,' ' || v_dimension_name_part);
648 END IF;
649
650 v_start := v_end + 1 ;
651 END LOOP;
652 v_rec_count := v_rec_count + 1;
653 END LOOP;
654 CLOSE v_err_records;
655 ELSE
656 OPEN v_err_records FOR v_sql_stmt;
657 LOOP
658 FETCH v_err_records INTO v_rowid, v_dimension_name;
659 EXIT WHEN v_err_records%NOTFOUND or
660 v_rec_count > pv_num_rec_to_print;
661
662 --
663 -- Print only one full dimension name/value pairs on a single line in the
664 -- output report listing
665 --
666 v_start := 1;
667
668 LOOP
669 v_end := INSTR(v_dimension_name, ')', v_start, 1);
670 EXIT WHEN v_end = 0;
671
672 v_dimension_name_part := SUBSTR(v_dimension_name, v_start, v_end - v_start + 1);
673 EXIT WHEN v_dimension_name_part IS NULL;
674
675 IF v_start = 1 THEN
676 FND_FILE.put_line(FND_FILE.OUTPUT, rpad(v_rowid,20,' ') || v_dimension_name_part);
677 ELSE
678 FND_FILE.put_line(FND_FILE.OUTPUT,' ' || v_dimension_name_part);
679 END IF;
680
681 v_start := v_end + 1 ;
682 END LOOP;
683 v_rec_count := v_rec_count + 1;
684 END LOOP;
685 CLOSE v_err_records;
686 END IF;
687 END IF;
688 ELSIF NOT v_no_data_found THEN
689 FND_MESSAGE.set_name('FEM','FEM_INTF_VALIDATE_SUCC');
690 FND_FILE.put_line(FND_FILE.OUTPUT, FND_MESSAGE.get);
691 FND_FILE.put_line(FND_FILE.OUTPUT,null);
692
693 FEM_ENGINES_PKG.User_Message
694 (p_app_name => 'FEM',
695 p_msg_name => 'FEM_INTF_VALIDATE_SUCC');
696 END IF;
697
698 IF pc_log_level_procedure >= pv_log_current_level THEN
699 FEM_ENGINES_PKG.Tech_Message
700 ( p_severity => pc_log_level_procedure,
701 p_module => vc_module,
702 p_app_name => 'FEM',
703 p_msg_name => 'FEM_GL_POST_202',
704 p_token1 => 'FUNC_NAME',
705 p_value1 => vc_module ,
706 p_token2 => 'TIME',
707 p_value2 => TO_CHAR(SYSDATE)||' '||TO_CHAR(SYSDATE,'HH24:MI:SS'));
708 END IF;
709
710 --
711 -- Bug 5398129 hkaniven start
712 --
713 -- End the concurrent program with the 'WARNING' 'ERROR' or 'NORMAL' status
714 --
715 IF v_no_data_found THEN
716 v_dummy_boolean := FND_CONCURRENT.Set_Completion_Status
717 (status => 'WARNING', message => NULL);
718 ELSIF v_invalid_dims_found THEN
719 v_dummy_boolean := FND_CONCURRENT.Set_Completion_Status
720 (status => 'ERROR', message => NULL);
721 ELSE
722 v_dummy_boolean := FND_CONCURRENT.Set_Completion_Status
723 (status => 'NORMAL', message => NULL);
724 END IF;
725 -- Bug 5398129 hkaniven end
726
727 EXCEPTION
728 WHEN FEM_PGM_fatal_err THEN
729 ROLLBACK;
730
731 IF pc_log_level_procedure >= pv_log_current_level THEN
732 FEM_ENGINES_PKG.Tech_Message
733 (p_severity => pc_log_level_procedure,
734 p_module => vc_module,
735 p_app_name => 'FEM',
736 p_msg_name => 'FEM_GL_POST_203',
737 p_token1 => 'FUNC_NAME',
738 p_value1 => vc_module,
739 p_token2 => 'TIME',
740 p_value2 => TO_CHAR(SYSDATE)||' '||TO_CHAR(SYSDATE,'HH24:MI:SS'));
741 END IF;
742
743 v_dummy_boolean := FND_CONCURRENT.Set_Completion_Status
744 (status => 'ERROR', message => NULL);
745 WHEN OTHERS THEN
746 ROLLBACK;
747
748 IF pc_log_level_unexpected >= pv_log_current_level THEN
749 FEM_ENGINES_PKG.Tech_Message
750 (p_severity => pc_log_level_unexpected,
751 p_module => vc_module,
752 p_app_name => 'FEM',
753 p_msg_name => 'FEM_GL_POST_215',
754 p_token1 => 'ERR_MSG',
755 p_value1 => SQLERRM);
756 END IF;
757
758 FEM_ENGINES_PKG.User_Message
759 (p_app_name => 'FEM',
760 p_msg_name => 'FEM_GL_POST_215',
761 p_token1 => 'ERR_MSG',
762 p_value1 => SQLERRM);
763
764 IF pc_log_level_procedure >= pv_log_current_level THEN
765 FEM_ENGINES_PKG.Tech_Message
766 (p_severity => pc_log_level_procedure,
767 p_module => vc_module,
768 p_app_name => 'FEM',
769 p_msg_name => 'FEM_GL_POST_203',
770 p_token1 => 'FUNC_NAME',
771 p_value1 => vc_module,
772 p_token2 => 'TIME',
773 p_value2 => TO_CHAR(SYSDATE)||' '||TO_CHAR(SYSDATE,'HH24:MI:SS'));
774 END IF;
775
776 v_dummy_boolean := FND_CONCURRENT.Set_Completion_Status
777 (status => 'ERROR', message => NULL);
778 END Main;
779
780 -- ======================================================================
781 -- Procedure
782 -- Validate_Dims
783 --
784 -- Purpose
785 -- This routine will first move interface records into a global
786 -- temporary table (FEM_SOURCE_DATA_INTERIM_GT), run dimension validation
787 -- and mark interface records with invalid dimension information.
788 -- Records will be marked as long as one or more dimension columns are
789 -- found to contain invalid dimension members.
790 --
791 -- Arguments
792 -- x_completion_code : Returning status of the routine
793 --
794 -- History
795 -- 04-11-06 Harikiran Bug 5106205 - Shouldn't filter on STATUS='LOAD'
796 -- 07-18-06 Harikiran Bug 5398129 - No_data_found case will show up a
797 -- new message and end with a 'WARNING'
798 -- status and Invalid Dimensions found
799 -- case will end with a 'ERROR'
800 -- ======================================================================
801
802 PROCEDURE Validate_Dims (
803 x_completion_code OUT NOCOPY NUMBER
804 ) IS
805 vc_module CONSTANT FND_LOG_MESSAGES.module%TYPE :=
806 'fem.plsql.fem_intf_dim_validation_pkg.validate_dims';
807
808 v_dim_grp_size NUMBER;
809 v_curr_dim_count NUMBER;
810 v_return_status VARCHAR2(1);
811 v_dynamic_sql VARCHAR2(30000);
812 v_dummy1_sql VARCHAR2(30000);
813 v_dummy2_sql VARCHAR2(30000);
814 v_dummy3_sql VARCHAR2(30000);
815 v_dummy4_sql VARCHAR2(30000);
816 v_insert_interim_sql VARCHAR2(30000);
817 v_update_interim_error_sql VARCHAR2(30000);
818 v_insert_target_sql VARCHAR2(30000);
819
820 FEM_INTF_INV_DIM_INFO EXCEPTION;
821 FEM_PGM_FATAL_ERR EXCEPTION;
822 v_completion_code NUMBER;
823 v_count NUMBER;
824
825 BEGIN
826
827 IF pc_log_level_procedure >= pv_log_current_level THEN
828 FEM_ENGINES_PKG.Tech_Message
829 ( p_severity => pc_log_level_procedure,
830 p_module => vc_module,
831 p_app_name => 'FEM',
832 p_msg_name => 'FEM_GL_POST_201',
833 p_token1 => 'FUNC_NAME',
834 p_value1 => vc_module ,
835 p_token2 => 'TIME',
836 p_value2 => TO_CHAR(SYSDATE)||' '||TO_CHAR(SYSDATE,'HH24:MI:SS'));
837 END IF;
838
839 x_completion_code := 0;
840
841 --
842 -- Populate dimension information into the PLSQL table
843 --
844 populate_dim_info(
845 x_completion_code => v_completion_code);
846
847 --
848 -- Build SQL to insert into the interim table
849 --
850
851 -- for x_insert_interim_sql
852 -- Bug 5106205 hkaniven start
853
854 v_insert_interim_sql :=
855 'INSERT INTO fem_source_data_interim_gt (INTERFACE_ROWID)
856 SELECT rowid
857 FROM '||pv_interface_table_name ||
858 ' WHERE calp_dim_grp_display_code = '''||pv_time_dim_grp_dc||''''
859 ||' AND cal_period_end_date = TO_DATE('''
860 ||TO_CHAR(pv_cal_per_end_date, pc_date_format)
861 ||''','''||pc_date_format||''')'
862 ||' AND cal_period_number = '||TO_CHAR(pv_cal_per_number)
863 ||' AND source_system_display_code = '''||pv_source_system_dc||''''
864 ||' AND dataset_display_code = '''||pv_dataset_dc||''''
865 ||' AND ledger_display_code = '''||pv_ledger_dc||''''
866 ||' AND ROWNUM <= '|| pv_num_rows;
867
868 -- Bug 5106205 hkaniven end
869
870 -- Step 1:
871 -- Copy rowid to interim table
872
873 IF pc_log_level_statement >= pv_log_current_level THEN
874 FEM_ENGINES_PKG.TECH_MESSAGE(
875 p_severity => pc_log_level_statement,
876 p_module => vc_module,
877 p_msg_text => 'SQL to insert interim table is '||v_insert_interim_sql);
878 END IF;
879
880 EXECUTE IMMEDIATE v_insert_interim_sql;
881
882 -- Bug 5398129 hkaniven start
883 IF SQL%ROWCOUNT = 0 THEN
884 FEM_ENGINES_PKG.TECH_MESSAGE(
885 p_severity => pc_log_level_statement,
886 p_module => vc_module,
887 p_msg_name => 'FEM_INTF_NO_DATA_FOUND' );
888
889 FEM_ENGINES_PKG.User_Message
890 (p_app_name => 'FEM',
891 p_msg_name => 'FEM_INTF_NO_DATA_FOUND');
892
893 x_completion_code := 1;
894
895 RETURN;
896 END IF;
897 -- Bug 5398129 hkaniven end
898
899 -- Step 2:
900 -- Update interim table with dimension value
901
902 v_dim_grp_size := to_number(FND_PROFILE.Value('FEM_LOADER_DIM_GRP_SIZE'));
903
904 IF nvl(v_dim_grp_size,0) <= 0 THEN
905 v_dim_grp_size := pc_default_dim_grp_size;
906 ELSIF nvl(v_dim_grp_size,0) > pv_num_dims THEN
907 v_dim_grp_size := pv_num_dims;
908 END IF;
909
910 IF pc_log_level_statement >= pv_log_current_level THEN
911 FEM_ENGINES_PKG.TECH_MESSAGE(
912 p_severity => pc_log_level_statement,
913 p_module => vc_module,
914 p_msg_text => 'Dimension grouping size is '||v_dim_grp_size);
915 END IF;
916
917 v_curr_dim_count := 1;
918
919 --
920 -- Sample v_dynamic_sql for the first five dimensions of 'FEM_CHECKING' table
921 --
922 -- UPDATE
923 -- fem_source_data_interim_gt g
924 -- SET
925 -- (
926 -- g.DIM1,
927 -- g.DIM2,
928 -- g.DIM3,
929 -- g.DIM4,
930 -- g.DIM5
931 -- )
932 -- =
933 -- (
934 -- SELECT
935 -- d1.CURRENCY_CODE,
936 -- d2.FLAG_CODE,
937 -- d3.FLAG_CODE,
938 -- d4.FLAG_CODE,
939 -- d5.FLAG_CODE
940 -- FROM FEM_CHECKING_T i,
941 -- FEM_CURRENCIES_VL d1,
942 -- FEM_FLAGS_B d2,
943 -- FEM_FLAGS_B d3,
944 -- FEM_FLAGS_B d4,
945 -- FEM_FLAGS_B d5
946 -- WHERE i.rowid=g.interface_rowid
947 -- AND d1.CURRENCY_CODE(+)=i.CURRENCY_CODE
948 -- AND d1.personal_flag(+)='N'
949 -- AND d2.FLAG_CODE(+)=i.ATM_CARD_FLG
950 -- AND d2.personal_flag(+)='N'
951 -- AND d3.FLAG_CODE(+)=i.CREDIT_LINE_FLG
952 -- AND d3.personal_flag(+)='N'
953 -- AND d4.FLAG_CODE(+)=i.EMBEDDED_OPTIONS_FLG
954 -- AND d4.personal_flag(+)='N'
955 -- AND d5.FLAG_CODE(+)=i.JOINT_ACCOUNT_FLG
956 -- AND d5.personal_flag(+)='N'
957 -- )
958 --
959 --
960
961 FOR v_dim_index IN 1..pv_num_dims LOOP
962 IF v_curr_dim_count = 1 THEN
963 v_dynamic_sql := 'UPDATE fem_source_data_interim_gt g SET (';
964 v_dummy1_sql := '(SELECT ';
965 v_dummy2_sql := ' FROM '||pv_interface_table_name||' i';
966 v_dummy3_sql := ' WHERE i.rowid=g.interface_rowid';
967 END IF;
968
969 -- UPDATE SET clause
970 v_dynamic_sql := v_dynamic_sql||'g.DIM'||to_char(v_dim_index);
971
972 -- SELECT clause (dimension ID lookup)
973 -- Explicitly convert the data type of the member col to
974 -- that of the DIMx columns (VARCHAR2) where necessary.
975
976 IF pv_xdim_info_tbl(v_dim_index).target_col_data_type = 'NUMBER' THEN
977 v_dummy1_sql := v_dummy1_sql||'to_char(d'||to_char(v_dim_index)||'.'
978 ||pv_xdim_info_tbl(v_dim_index).member_col||')';
979 ELSIF pv_xdim_info_tbl(v_dim_index).target_col_data_type = 'DATE' THEN
980 v_dummy1_sql := v_dummy1_sql||'to_char(d'||to_char(v_dim_index)||'.'
981 ||pv_xdim_info_tbl(v_dim_index).member_col
982 ||','''||pc_date_format||''')';
983 ELSE
984 v_dummy1_sql := v_dummy1_sql||'d'||to_char(v_dim_index)||'.'
985 ||pv_xdim_info_tbl(v_dim_index).member_col;
986 END IF;
987
988 -- FROM clause
989 v_dummy2_sql := v_dummy2_sql||', '
990 ||pv_xdim_info_tbl(v_dim_index).member_b_table_name
991 ||' d'||to_char(v_dim_index);
992
993 -- WHERE clause
994 -- match display codes
995 v_dummy3_sql := v_dummy3_sql||' AND d'||to_char(v_dim_index)||'.'
996 ||pv_xdim_info_tbl(v_dim_index).member_disp_code_col||'(+)'
997 ||'=i.'||pv_xdim_info_tbl(v_dim_index).int_disp_code_col;
998 -- make sure personal flag is N
999 v_dummy3_sql := v_dummy3_sql
1000 ||' AND d'||to_char(v_dim_index)||'.'||'personal_flag(+)=''N''';
1001 -- if dimension has value set associated with it, make sure
1002 -- it matches with the value set tied to the global value set combo
1003
1004 IF pv_xdim_info_tbl(v_dim_index).vs_id IS NOT NULL THEN
1005 v_dummy3_sql := v_dummy3_sql||' AND d'||to_char(v_dim_index)||'.'
1006 ||'value_set_id(+)'
1007 ||'='||to_char(pv_xdim_info_tbl(v_dim_index).vs_id);
1008 END IF;
1009
1010 -- Execute the update statement when number of dimenions reach
1011 -- dimension group size
1012
1013 IF v_curr_dim_count = v_dim_grp_size OR v_dim_index = pv_num_dims THEN
1014
1015 v_dynamic_sql := v_dynamic_sql||')='||v_dummy1_sql||v_dummy2_sql
1016 ||v_dummy3_sql||')';
1017
1018 IF pc_log_level_statement >= pv_log_current_level THEN
1019 FEM_ENGINES_PKG.TECH_MESSAGE(
1020 p_severity => pc_log_level_statement,
1021 p_module => vc_module,
1022 p_msg_text => 'SQL to update interim table '||v_dynamic_sql);
1023 END IF;
1024
1025 EXECUTE IMMEDIATE v_dynamic_sql;
1026 -- Reset the dimension group counter after update statement executes
1027 v_curr_dim_count := 1;
1028
1029 -- Commit to release any reserved rollback space.
1030 COMMIT;
1031 ELSE
1032 v_curr_dim_count := v_curr_dim_count+1;
1033 v_dynamic_sql := v_dynamic_sql||', ';
1034 v_dummy1_sql := v_dummy1_sql||', ';
1035 END IF;
1036 END LOOP;
1037
1038 --
1039 -- Build a dyanamic sql to insert only those rowids which are there in the interim
1040 -- table and which have invalid dimension information into the fem_interface_fact_errs
1041 -- table
1042 --
1043
1044 --
1045 -- Sample SQL statement for the first five dimensions of the 'FEM_CHECKING' table
1046 --
1047 -- INSERT
1048 -- INTO FEM_INTERFACE_FACT_ERRS
1049 -- (
1050 -- request_id,
1051 -- interface_rowid,
1052 -- interface_table_name,
1053 -- error_code
1054 -- )
1055 -- SELECT
1056 -- 3119007,
1057 -- interface_rowid,
1058 -- 'FEM_CHECKING_T',
1059 -- DECODE(gt.dim1, NULL,DECODE(t.CURRENCY_CODE, NULL,'0','1'),'0')
1060 -- || DECODE(gt.dim2, NULL,DECODE(t.ATM_CARD_FLG, NULL,'0','1'),'0')
1061 -- || DECODE(gt.dim3, NULL,DECODE(t.CREDIT_LINE_FLG, NULL,'0','1'),'0')
1062 -- || DECODE(gt.dim4, NULL,DECODE(t.EMBEDDED_OPTIONS_FLG, NULL,'0','1'),'0')
1063 -- || DECODE(gt.dim5, NULL,DECODE(t.JOINT_ACCOUNT_FLG, NULL,'0','1'),'0')
1064 -- FROM fem_source_data_interim_gt gt,
1065 -- FEM_CHECKING_T t
1066 -- WHERE gt.interface_rowid = t.rowid
1067 -- AND RPAD('0',5,'0') <>
1068 -- DECODE(gt.dim1, NULL,DECODE(t.CURRENCY_CODE, NULL,'0','1'),'0')
1069 -- || DECODE(gt.dim2, NULL,DECODE(t.ATM_CARD_FLG, NULL,'0','1'),'0')
1070 -- || DECODE(gt.dim3, NULL,DECODE(t.CREDIT_LINE_FLG, NULL,'0','1'),'0')
1071 -- || DECODE(gt.dim4, NULL,DECODE(t.EMBEDDED_OPTIONS_FLG, NULL,'0','1'),'0')
1072 -- || DECODE(gt.dim5, NULL,DECODE(t.JOINT_ACCOUNT_FLG, NULL,'0','1'),'0')
1073 --
1074 --
1075
1076 FOR v_dim_index IN 1..pv_num_dims LOOP
1077
1078 IF v_dim_index = 1 THEN
1079 v_dynamic_sql := 'INSERT INTO FEM_INTERFACE_FACT_ERRS(request_id, interface_rowid, interface_table_name, error_code) ';
1080 v_dummy1_sql := 'SELECT ' || pc_req_id || ', interface_rowid, '
1081 || '''' || pv_interface_table_name || '''' || ', ';
1082 v_dummy2_sql := 'FROM fem_source_data_interim_gt gt, ' || pv_interface_table_name || ' t ';
1083 v_dummy3_sql := 'WHERE gt.interface_rowid = t.rowid '
1084 || ' AND RPAD(' || '''' || '0' || '''' || ',' || pv_num_dims || ',' || '''' || '0' || '''' || ') <> ';
1085 v_dummy4_sql := '';
1086 ELSE
1087 v_dummy4_sql := v_dummy4_sql || ' || ';
1088 END IF;
1089
1090 --
1091 -- The error code combination is required at two places in the statement so
1092 -- create a separate variable to hold the error_code
1093 --
1094 v_dummy4_sql := v_dummy4_sql || ' DECODE(gt.dim'||to_char(v_dim_index)
1095 ||', NULL,DECODE(t.'||pv_xdim_info_tbl(v_dim_index).int_disp_code_col
1096 ||', NULL,''0'',''1''),''0'') ';
1097
1098 END LOOP;
1099 v_dummy1_sql := v_dummy1_sql || v_dummy4_sql;
1100 v_dummy3_sql := v_dummy3_sql || v_dummy4_sql;
1101
1102 v_dynamic_sql := v_dynamic_sql || v_dummy1_sql || v_dummy2_sql || v_dummy3_sql;
1103
1104 IF pc_log_level_statement >= pv_log_current_level THEN
1105 FEM_ENGINES_PKG.TECH_MESSAGE(
1106 p_severity => pc_log_level_statement,
1107 p_module => vc_module,
1108 p_msg_text => 'SQL to insert target table error_code column is ');
1109 END IF;
1110
1111 v_count := 1;
1112 LOOP
1113 IF pc_log_level_statement >= pv_log_current_level THEN
1114 FEM_ENGINES_PKG.TECH_MESSAGE(
1115 p_severity => pc_log_level_statement,
1116 p_module => vc_module,
1117 p_msg_text => substr(v_dynamic_sql,v_count,255));
1118 END IF;
1119
1120 EXIT WHEN LENGTH(v_dynamic_sql) < v_count ;
1121 v_count := v_count + 255;
1122 END LOOP;
1123
1124 EXECUTE IMMEDIATE v_dynamic_sql;
1125
1126 -- Bug 5398129 hkaniven start
1127
1128 IF SQL%ROWCOUNT > 0 THEN
1129 x_completion_code := 2;
1130 RAISE FEM_INTF_INV_DIM_INFO;
1131 END IF;
1132
1133 -- Bug 5398129 hkaniven end
1134
1135 COMMIT;
1136
1137 EXCEPTION
1138
1139 WHEN FEM_INTF_INV_DIM_INFO THEN
1140
1141 IF pc_log_level_exception >= pv_log_current_level THEN
1142 FEM_ENGINES_PKG.Tech_Message
1143 (p_severity => pc_log_level_exception,
1144 p_module => vc_module,
1145 p_app_name => 'FEM',
1146 p_msg_name => 'FEM_INTF_INV_DIM_INFO');
1147 END IF;
1148
1149 FEM_ENGINES_PKG.User_Message
1150 (p_app_name => 'FEM',
1151 p_msg_name => 'FEM_INTF_INV_DIM_INFO');
1152
1153 IF pc_log_level_procedure >= pv_log_current_level THEN
1154 FEM_ENGINES_PKG.Tech_Message
1155 ( p_severity => pc_log_level_procedure,
1156 p_module => vc_module,
1157 p_app_name => 'FEM',
1158 p_msg_name => 'FEM_GL_POST_202',
1159 p_token1 => 'FUNC_NAME',
1160 p_value1 => vc_module ,
1161 p_token2 => 'TIME',
1162 p_value2 => TO_CHAR(SYSDATE)||' '||TO_CHAR(SYSDATE,'HH24:MI:SS'));
1163 END IF;
1164
1165 RETURN;
1166
1167 WHEN OTHERS THEN
1168 ROLLBACK;
1169 IF pc_log_level_unexpected >= pv_log_current_level THEN
1170 FEM_ENGINES_PKG.Tech_Message
1171 (p_severity => pc_log_level_unexpected,
1172 p_module => vc_module,
1173 p_app_name => 'FEM',
1174 p_msg_name => 'FEM_GL_POST_215',
1175 p_token1 => 'ERR_MSG',
1176 p_value1 => SQLERRM);
1177 END IF;
1178
1179 FEM_ENGINES_PKG.User_Message
1180 (p_app_name => 'FEM',
1181 p_msg_name => 'FEM_GL_POST_215',
1182 p_token1 => 'ERR_MSG',
1183 p_value1 => SQLERRM);
1184
1185 IF pc_log_level_unexpected >= pv_log_current_level THEN
1186 FEM_ENGINES_PKG.Tech_Message
1187 (p_severity => pc_log_level_unexpected,
1188 p_module => vc_module,
1189 p_app_name => 'FEM',
1190 p_msg_name => 'FEM_GL_POST_203',
1191 p_token1 => 'FUNC_NAME',
1192 p_value1 => vc_module,
1193 p_token2 => 'TIME',
1194 p_value2 => TO_CHAR(SYSDATE)||' '||TO_CHAR(SYSDATE,'HH24:MI:SS'));
1195 END IF;
1196
1197 -- Bug 5398129 hkaniven start
1198 x_completion_code := 3;
1199 -- Bug 5398129 hkaniven end
1200
1201 RETURN;
1202
1203 END Validate_Dims;
1204
1205 -- ======================================================================
1206 -- Procedure
1207 -- Populate_Dim_Info
1208 -- Purpose
1209 -- This routine will run a set of queries that populate the following
1210 -- information about each dimension column in the selected PLSQL table:
1211 -- Interface display code column
1212 -- Dimension value set ID
1213 -- Dimension member_B table name
1214 -- Dimension member column name
1215 -- Dimension member display code column name
1216 -- Target column data type
1217 -- Target column name
1218 --
1219 -- Arguments
1220 -- x_completion_code : Returning status of the rountine
1221 -- ======================================================================
1222
1223 PROCEDURE Populate_Dim_Info (
1224 x_completion_code OUT NOCOPY NUMBER
1225 ) IS
1226 vc_module CONSTANT FND_LOG_MESSAGES.module%TYPE :=
1227 'fem.plsql.fem_intf_dim_validation_pkg.populate_dim_info';
1228
1229 v_gvsc_id NUMBER;
1230 v_msg_count NUMBER;
1231 v_return_status VARCHAR2(1);
1232 v_msg_data VARCHAR2(4000);
1233
1234 FEM_PGM_FATAL_ERR EXCEPTION;
1235
1236 BEGIN
1237
1238 IF pc_log_level_procedure >= pv_log_current_level THEN
1239 FEM_ENGINES_PKG.Tech_Message
1240 ( p_severity => pc_log_level_procedure,
1241 p_module => vc_module,
1242 p_app_name => 'FEM',
1243 p_msg_name => 'FEM_GL_POST_201',
1244 p_token1 => 'FUNC_NAME',
1245 p_value1 => vc_module ,
1246 p_token2 => 'TIME',
1247 p_value2 => TO_CHAR(SYSDATE)||' '||TO_CHAR(SYSDATE,'HH24:MI:SS'));
1248 END IF;
1249
1250 x_completion_code := 0;
1251 pv_num_dims :=0;
1252
1253 -- In case this procedure is called twice in the same session
1254 -- make sure to remove the previous dimension elements.
1255 IF pv_xdim_info_tbl.COUNT > 0 THEN
1256 IF pc_log_level_statement >= pv_log_current_level THEN
1257 FEM_ENGINES_PKG.Tech_Message
1258 (p_severity => pc_log_level_statement,
1259 p_module => vc_module,
1260 p_msg_text => 'pv_xdim_info_tbl is not empty. Deleting all elements'
1261 ||' to ensure a fresh start when loading new dimension info.');
1262 END IF;
1263 pv_xdim_info_tbl.DELETE;
1264 END IF;
1265
1266 --
1267 -- lookup the global value set combination id tied to the ledger
1268 --
1269 v_gvsc_id := FEM_DIMENSION_UTIL_PKG.GLOBAL_VS_COMBO_ID
1270 (p_encoded => FND_API.G_FALSE,
1271 x_return_status => v_return_status,
1272 x_msg_count => v_msg_count,
1273 x_msg_data => v_msg_data,
1274 p_ledger_id => pv_ledger_id);
1275
1276 IF v_return_status = FND_API.G_RET_STS_SUCCESS THEN
1277 IF pc_log_level_statement >= pv_log_current_level THEN
1278 FEM_ENGINES_PKG.Tech_Message
1279 (p_severity => pc_log_level_statement,
1280 p_module => vc_module,
1281 p_msg_text => 'Global Value Set Combination ID is '
1282 ||to_char(v_gvsc_id));
1283 END IF;
1284 ELSE
1285 FEM_ENGINES_PKG.Tech_Message
1286 (p_severity => pc_log_level_statement,
1287 p_module => vc_module,
1288 p_msg_text => 'Could not find the Global Value Set Combination ID '
1289 ||'associated with the ledger');
1290 RAISE FEM_PGM_FATAL_ERR;
1291 END IF;
1292
1293 --
1294 -- populate the dimension properties record table
1295 --
1296 BEGIN
1297 SELECT
1298 cm.interface_column_name ,
1299 xd.value_set_required_flag,
1300 gv.value_set_id,
1301 xd.member_b_table_name,
1302 xd.member_col,
1303 xd.member_display_code_col,
1304 xd.member_data_type_code,
1305 tc.column_name
1306 BULK COLLECT INTO pv_xdim_info_tbl
1307 FROM fem_tab_columns_v tc,
1308 fem_int_column_map cm,
1309 fem_xdim_dimensions xd,
1310 fem_global_vs_combo_defs gv
1311 WHERE tc.table_name = pv_table_name
1312 AND tc.fem_data_type_code = 'DIMENSION'
1313 AND tc.column_name NOT IN
1314 ('CREATED_BY_OBJECT_ID','LAST_UPDATED_BY_OBJECT_ID',
1315 'LEDGER_ID', 'SOURCE_SYSTEM_CODE', 'DATASET_CODE')
1316 AND cm.target_column_name = tc.column_name
1317 AND cm.object_type_code = 'SOURCE_DATA_LOADER'
1318 AND xd.dimension_id = tc.dimension_id
1319 AND xd.dimension_id = gv.dimension_id (+)
1320 AND gv.global_vs_combo_id (+) = v_gvsc_id;
1321
1322
1323 EXCEPTION
1324 WHEN no_data_found THEN
1325 pv_num_dims := 0;
1326 END;
1327
1328 pv_num_dims := SQL%ROWCOUNT;
1329
1330 IF pc_log_level_statement >= pv_log_current_level THEN
1331
1332 FEM_ENGINES_PKG.Tech_Message
1333 (p_severity => pc_log_level_statement,
1334 p_module => vc_module,
1335 p_app_name => 'FEM',
1336 p_msg_name => 'FEM_GL_POST_214');
1337
1338 FEM_ENGINES_PKG.Tech_Message
1339 (p_severity => pc_log_level_statement,
1340 p_module => vc_module,
1341 p_msg_text => 'Number of dimenions is '
1342 ||to_char(pv_num_dims));
1343 END IF;
1344
1345 IF pv_num_dims > 80 THEN
1346 FEM_ENGINES_PKG.TECH_MESSAGE(
1347 p_severity => pc_log_level_statement,
1348 p_module => vc_module,
1349 p_msg_text => 'Number of dimensions for this table ('||to_number(pv_num_dims)
1350 ||') exceeds the maximum number of supported dimensions (80).');
1351 RAISE FEM_PGM_FATAL_ERR;
1352 END IF;
1353
1354 IF pc_log_level_procedure >= pv_log_current_level THEN
1355 FEM_ENGINES_PKG.Tech_Message
1356 ( p_severity => pc_log_level_procedure,
1357 p_module => vc_module,
1358 p_app_name => 'FEM',
1359 p_msg_name => 'FEM_GL_POST_202',
1360 p_token1 => 'FUNC_NAME',
1361 p_value1 => vc_module ,
1362 p_token2 => 'TIME',
1363 p_value2 => TO_CHAR(SYSDATE)||' '||TO_CHAR(SYSDATE,'HH24:MI:SS'));
1364 END IF;
1365
1366 EXCEPTION
1367 WHEN FEM_PGM_FATAL_ERR THEN
1368
1369 ROLLBACK;
1370
1371 IF pc_log_level_unexpected >= pv_log_current_level THEN
1372 FEM_ENGINES_PKG.Tech_Message
1373 (p_severity => pc_log_level_unexpected,
1374 p_module => vc_module,
1375 p_app_name => 'FEM',
1376 p_msg_name => 'FEM_GL_POST_203',
1377 p_token1 => 'FUNC_NAME',
1378 p_value1 => vc_module,
1379 p_token2 => 'TIME',
1380 p_value2 => TO_CHAR(SYSDATE)||' '||TO_CHAR(SYSDATE,'HH24:MI:SS'));
1381 END IF;
1382
1383 x_completion_code := 2;
1384
1385 RETURN;
1386
1387 WHEN OTHERS THEN
1388
1389 ROLLBACK;
1390
1391 IF pc_log_level_unexpected >= pv_log_current_level THEN
1392 FEM_ENGINES_PKG.Tech_Message
1393 (p_severity => pc_log_level_unexpected,
1394 p_module => vc_module,
1395 p_app_name => 'FEM',
1396 p_msg_name => 'FEM_GL_POST_215',
1397 p_token1 => 'ERR_MSG',
1398 p_value1 => SQLERRM);
1399 END IF;
1400
1401 FEM_ENGINES_PKG.User_Message
1402 (p_app_name => 'FEM',
1403 p_msg_name => 'FEM_GL_POST_215',
1404 p_token1 => 'ERR_MSG',
1405 p_value1 => SQLERRM);
1406
1407 IF pc_log_level_unexpected >= pv_log_current_level THEN
1408 FEM_ENGINES_PKG.Tech_Message
1409 (p_severity => pc_log_level_unexpected,
1410 p_module => vc_module,
1411 p_app_name => 'FEM',
1412 p_msg_name => 'FEM_GL_POST_203',
1413 p_token1 => 'FUNC_NAME',
1414 p_value1 => vc_module,
1415 p_token2 => 'TIME',
1416 p_value2 => TO_CHAR(SYSDATE)||' '||TO_CHAR(SYSDATE,'HH24:MI:SS'));
1417 END IF;
1418
1419 x_completion_code := 2;
1420
1421 RETURN;
1422 END Populate_Dim_Info;
1423
1424
1425 -- ======================================================================
1426 -- Procedure
1427 -- Validate_Params
1428 --
1429 -- Purpose
1430 -- This is the routine that validate program parameters and set
1431 -- package level variables used throughout the program.
1432 --
1433 -- If any of the query fails, the routine will report the SQL
1434 -- error and return 2 as the completion code.
1435 --
1436 -- Arguments
1437 -- x_completion_code : Returning status of the routine
1438 -- ======================================================================
1439
1440 PROCEDURE Validate_Params (
1441 x_completion_code OUT NOCOPY NUMBER
1442 ) IS
1443 vc_module CONSTANT FND_LOG_MESSAGES.module%TYPE :=
1444 'fem.plsql.fem_intf_dim_validation_pkg.validate_params';
1445 v_count NUMBER := 0;
1446 v_cal_per_dim_grp_id NUMBER;
1447 v_ledger_dim_id NUMBER;
1448 v_dim_attr_id NUMBER;
1449 v_dim_attr_ver_id NUMBER;
1450 v_calp_hier_obj_def_id NUMBER;
1451 v_calp_hier_obj_id NUMBER;
1452 v_ledger_calendar_id NUMBER;
1453 v_cal_per_calendar_id NUMBER;
1454 v_time_dim_grp_id NUMBER;
1455 v_cal_per_dim_id NUMBER;
1456 v_dummy VARCHAR2(1);
1457
1458 FEM_INTF_INVALID_LEDGER EXCEPTION;
1459 FEM_INTF_INVALID_CAL_PERIOD EXCEPTION;
1460 FEM_INTF_MISMATCH_CALENDAR EXCEPTION;
1461 FEM_INTG_CAL_PER_NOT_IN_HIER EXCEPTION;
1462
1463 C_OBJECT_TYPE CONSTANT VARCHAR2(18) := 'SOURCE_DATA_LOADER';
1464 C_TABLE_CLASSIFICATION CONSTANT VARCHAR2(17) := 'SOURCE_DATA_TABLE';
1465
1466
1467 v_object_id FEM_OBJECT_CATALOG_B.object_id%TYPE;
1468 v_table_name FEM_TABLE_CLASS_ASSIGNMT_V.table_name%TYPE;
1469 v_ledger_dc FEM_LEDGERS_B.ledger_display_code%TYPE;
1470 v_calp_dim_grp_dc FEM_DIMENSION_GRPS_B.dimension_group_display_code%TYPE;
1471 v_cal_per_end_date FEM_CAL_PERIODS_ATTR.date_assign_value%TYPE;
1472 v_cal_per_number FEM_CAL_PERIODS_ATTR.number_assign_value%TYPE;
1473 v_dataset_dc FEM_DATASETS_B.dataset_display_code%TYPE;
1474 v_source_system_dc FEM_SOURCE_SYSTEMS_B.source_system_display_code%TYPE;
1475 v_ledger_per_hier_obj_def_id NUMBER;
1476 v_return_status VARCHAR2(1);
1477 v_msg_count NUMBER;
1478 v_msg_data VARCHAR2(4000);
1479
1480 BEGIN
1481
1482 IF pc_log_level_procedure >= pv_log_current_level THEN
1483 FEM_ENGINES_PKG.Tech_Message
1484 ( p_severity => pc_log_level_procedure,
1485 p_module => vc_module,
1486 p_app_name => 'FEM',
1487 p_msg_name => 'FEM_GL_POST_201',
1488 p_token1 => 'FUNC_NAME',
1489 p_value1 => vc_module ,
1490 p_token2 => 'TIME',
1491 p_value2 => TO_CHAR(SYSDATE)||' '||TO_CHAR(SYSDATE,'HH24:MI:SS'));
1492 END IF;
1493
1494 x_completion_code := 0;
1495
1496 FEM_SOURCE_DATA_LOADER_PKG.Validate_Obj_Def(
1497 p_api_version => G_API_VERSION,
1498 p_object_type => C_OBJECT_TYPE,
1499 p_obj_def_id => pv_obj_def_id,
1500 x_object_id => v_object_id,
1501 x_table_name => v_table_name,
1502 x_msg_count => v_msg_count,
1503 x_msg_data => v_msg_data,
1504 x_return_status => v_return_status);
1505
1506 IF v_msg_count > 0 THEN
1507 FEM_SOURCE_DATA_LOADER_PKG.Get_Put_Messages (
1508 p_msg_count => v_msg_count,
1509 p_msg_data => v_msg_data);
1510 END IF;
1511
1512 pv_table_name := v_table_name;
1513 pv_interface_table_name := v_table_name||'_T';
1514
1515 IF v_return_status = G_RET_STS_SUCCESS THEN
1516 FEM_SOURCE_DATA_LOADER_PKG.Validate_Table(
1517 p_api_version => G_API_VERSION,
1518 p_object_type => C_OBJECT_TYPE,
1519 p_table_name => pv_table_name,
1520 p_table_classification => C_TABLE_CLASSIFICATION,
1521 x_msg_count => v_msg_count,
1522 x_msg_data => v_msg_data,
1523 x_return_status => v_return_status);
1524
1525 IF v_msg_count > 0 THEN
1526 FEM_SOURCE_DATA_LOADER_PKG.Get_Put_Messages (
1527 p_msg_count => v_msg_count,
1528 p_msg_data => v_msg_data);
1529 END IF;
1530
1531 END IF;
1532
1533 IF v_return_status = G_RET_STS_SUCCESS THEN
1534 FEM_SOURCE_DATA_LOADER_PKG.Validate_Ledger(
1535 p_api_version => G_API_VERSION,
1536 p_object_type => C_OBJECT_TYPE,
1537 p_ledger_id => pv_ledger_id,
1538 x_ledger_dc => v_ledger_dc,
1539 x_ledger_calendar_id => v_ledger_calendar_id,
1540 x_ledger_per_hier_obj_def_id => v_ledger_per_hier_obj_def_id,
1541 x_msg_count => v_msg_count,
1542 x_msg_data => v_msg_data,
1543 x_return_status => v_return_status);
1544
1545 IF v_msg_count > 0 THEN
1546 FEM_SOURCE_DATA_LOADER_PKG.Get_Put_Messages (
1547 p_msg_count => v_msg_count,
1548 p_msg_data => v_msg_data);
1549 END IF;
1550
1551 END IF;
1552
1553 IF v_return_status = G_RET_STS_SUCCESS THEN
1554 FEM_SOURCE_DATA_LOADER_PKG.Validate_Cal_Period(
1555 p_api_version => G_API_VERSION,
1556 p_object_type => C_OBJECT_TYPE,
1557 p_cal_period_id => pv_cal_period_id,
1558 p_ledger_id => pv_ledger_id,
1559 p_ledger_calendar_id => v_ledger_calendar_id,
1560 p_ledger_per_hier_obj_def_id => v_ledger_per_hier_obj_def_id,
1561 x_calp_dim_grp_dc => v_calp_dim_grp_dc,
1562 x_cal_per_end_date => v_cal_per_end_date,
1563 x_cal_per_number => v_cal_per_number,
1564 x_msg_count => v_msg_count,
1565 x_msg_data => v_msg_data,
1566 x_return_status => v_return_status);
1567
1568 IF v_msg_count > 0 THEN
1569 FEM_SOURCE_DATA_LOADER_PKG.Get_Put_Messages (
1570 p_msg_count => v_msg_count,
1571 p_msg_data => v_msg_data);
1572 END IF;
1573 END IF;
1574
1575 IF v_return_status = G_RET_STS_SUCCESS THEN
1576 FEM_SOURCE_DATA_LOADER_PKG.Validate_Dataset(
1577 p_api_version => G_API_VERSION,
1578 p_object_type => C_OBJECT_TYPE,
1579 p_dataset_code => pv_dataset_code,
1580 x_dataset_dc => v_dataset_dc,
1581 x_msg_count => v_msg_count,
1582 x_msg_data => v_msg_data,
1583 x_return_status => v_return_status);
1584
1585 IF v_msg_count > 0 THEN
1586 FEM_SOURCE_DATA_LOADER_PKG.Get_Put_Messages (
1587 p_msg_count => v_msg_count,
1588 p_msg_data => v_msg_data);
1589 END IF;
1590 END IF;
1591
1592 IF v_return_status = G_RET_STS_SUCCESS THEN
1593 FEM_SOURCE_DATA_LOADER_PKG.Validate_Source_System(
1594 p_api_version => G_API_VERSION,
1595 p_object_type => C_OBJECT_TYPE,
1596 p_source_system_code => pv_source_system_code,
1597 x_source_system_dc => v_source_system_dc,
1598 x_msg_count => v_msg_count,
1599 x_msg_data => v_msg_data,
1600 x_return_status => v_return_status);
1601
1602 IF v_msg_count > 0 THEN
1603 FEM_SOURCE_DATA_LOADER_PKG.Get_Put_Messages (
1604 p_msg_count => v_msg_count,
1605 p_msg_data => v_msg_data);
1606 END IF;
1607 END IF;
1608
1609 --
1610 -- Initialize package level variables
1611 --
1612 pv_cal_per_number := v_cal_per_number;
1613 pv_ledger_dc := v_ledger_dc;
1614 pv_time_dim_grp_dc := v_calp_dim_grp_dc;
1615 pv_cal_per_end_date := v_cal_per_end_date;
1616 pv_dataset_dc := v_dataset_dc;
1617 pv_source_system_dc := v_source_system_dc;
1618
1619 IF v_return_status = G_RET_STS_ERROR THEN
1620 x_completion_code := 1;
1621 ELSIF v_return_status = G_RET_STS_UNEXP_ERROR THEN
1622 x_completion_code := 2;
1623 END IF;
1624
1625 IF pc_log_level_procedure >= pv_log_current_level THEN
1626 FEM_ENGINES_PKG.Tech_Message
1627 ( p_severity => pc_log_level_procedure,
1628 p_module => vc_module,
1629 p_app_name => 'FEM',
1630 p_msg_name => 'FEM_GL_POST_202',
1631 p_token1 => 'FUNC_NAME',
1632 p_value1 => vc_module ,
1633 p_token2 => 'TIME',
1634 p_value2 => TO_CHAR(SYSDATE)||' '||TO_CHAR(SYSDATE,'HH24:MI:SS'));
1635 END IF;
1636
1637 EXCEPTION
1638
1639 WHEN OTHERS THEN
1640 ROLLBACK;
1641
1642 IF pc_log_level_unexpected >= pv_log_current_level THEN
1643
1644 FEM_ENGINES_PKG.Tech_Message
1645 (p_severity => pc_log_level_unexpected,
1646 p_module => vc_module,
1647 p_app_name => 'FEM',
1648 p_msg_name => 'FEM_GL_POST_215',
1649 p_token1 => 'ERR_MSG',
1650 p_value1 => SQLERRM);
1651 END IF;
1652
1653 FEM_ENGINES_PKG.User_Message
1654 (p_app_name => 'FEM',
1655 p_msg_name => 'FEM_GL_POST_215',
1656 p_token1 => 'ERR_MSG',
1657 p_value1 => SQLERRM);
1658
1659 IF pc_log_level_procedure >= pv_log_current_level THEN
1660 FEM_ENGINES_PKG.Tech_Message
1661 ( p_severity => pc_log_level_procedure,
1662 p_module => vc_module,
1663 p_app_name => 'FEM',
1664 p_msg_name => 'FEM_GL_POST_203',
1665 p_token1 => 'FUNC_NAME',
1666 p_value1 => vc_module ,
1667 p_token2 => 'TIME',
1668 p_value2 => TO_CHAR(SYSDATE)||' '||TO_CHAR(SYSDATE,'HH24:MI:SS'));
1669 END IF;
1670
1671 x_completion_code := 2;
1672
1673 RETURN;
1674
1675 END Validate_Params;
1676
1677 -- ======================================================================
1678 -- Procedure
1679 -- Is_Number
1680 --
1681 -- Purpose
1682 -- This is the procedure that validates whether a string is a valid
1683 -- positive number or not.
1684 --
1685 -- It checks whether it is a valid positive number and if it is then it
1686 -- returns that value and if not it returns NULL for the OUT parameter
1687 --
1688 -- Arguments
1689 -- p_string : String which has to be validated as a positive
1690 -- number
1691 -- x_string_value : Contains the positive number value if the string
1692 -- is a positive number and if not it contains NULL
1693 -- =========================================================================
1694
1695 PROCEDURE Is_Number(
1696 p_string IN VARCHAR2,
1697 x_string_value OUT NOCOPY NUMBER)
1698 IS
1699 v_check VARCHAR2(10);
1700 vc_module CONSTANT FND_LOG_MESSAGES.module%TYPE :=
1701 'fem.plsql.fem_intf_dim_validation_pkg.Is_Number';
1702
1703 BEGIN
1704 v_check := 0;
1705
1706 IF pc_log_level_procedure >= pv_log_current_level THEN
1707 FEM_ENGINES_PKG.Tech_Message
1708 ( p_severity => pc_log_level_procedure,
1709 p_module => vc_module,
1710 p_app_name => 'FEM',
1711 p_msg_name => 'FEM_GL_POST_201',
1712 p_token1 => 'FUNC_NAME',
1713 p_value1 => vc_module ,
1714 p_token2 => 'TIME',
1715 p_value2 => TO_CHAR(SYSDATE)||' '||TO_CHAR(SYSDATE,'HH24:MI:SS'));
1716 END IF;
1717
1718 SELECT DECODE(REPLACE(TRANSLATE(p_string, '0123456789','0000000000'),'0',''),'',1)
1719 INTO v_check
1720 FROM dual;
1721
1722 --
1723 -- If v_check = 1 it is a valid positive number
1724 --
1725 IF v_check = 1 THEN
1726 x_string_value := to_number( p_string );
1727 ELSE
1728 x_string_value := NULL;
1729 END IF;
1730
1731 IF pc_log_level_procedure >= pv_log_current_level THEN
1732 FEM_ENGINES_PKG.Tech_Message
1733 ( p_severity => pc_log_level_procedure,
1734 p_module => vc_module,
1735 p_app_name => 'FEM',
1736 p_msg_name => 'FEM_GL_POST_202',
1737 p_token1 => 'FUNC_NAME',
1738 p_value1 => vc_module ,
1739 p_token2 => 'TIME',
1740 p_value2 => TO_CHAR(SYSDATE)||' '||TO_CHAR(SYSDATE,'HH24:MI:SS'));
1741 END IF;
1742
1743 EXCEPTION
1744 WHEN OTHERS THEN
1745 x_string_value := NULL;
1746 END;
1747
1748 END FEM_INTF_DIM_VALIDATION_PKG;