[Home] [Help]
PACKAGE BODY: APPS.FEM_SOURCE_DATA_LOADER_PKG
Source
1 PACKAGE BODY FEM_SOURCE_DATA_LOADER_PKG AS
2 /* $Header: fem_srcdata_ldr.plb 120.7 2008/01/23 19:08:28 gcheng ship $ */
3
4 --
5 -- Private Package Variables and exceptions
6 --
7
8 G_LOG_LEVEL_STATEMENT CONSTANT NUMBER := FND_LOG.level_statement;
9 G_LOG_LEVEL_PROCEDURE CONSTANT NUMBER := FND_LOG.level_procedure;
10 G_LOG_LEVEL_EVENT CONSTANT NUMBER := FND_LOG.level_event;
11 G_LOG_LEVEL_ERROR CONSTANT NUMBER := FND_LOG.level_error;
12 G_LOG_LEVEL_UNEXPECTED CONSTANT NUMBER := FND_LOG.level_unexpected;
13
14 G_API_VERSION CONSTANT NUMBER := 1.0;
15 G_FALSE CONSTANT VARCHAR2(1) := FND_API.G_FALSE;
16 G_TRUE CONSTANT VARCHAR2(1) := FND_API.G_TRUE;
17 G_RET_STS_SUCCESS CONSTANT VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
18 G_RET_STS_ERROR CONSTANT VARCHAR2(1) := FND_API.G_RET_STS_ERROR;
19 G_RET_STS_UNEXP_ERROR CONSTANT VARCHAR2(1) := FND_API.G_RET_STS_UNEXP_ERROR;
20 G_PKG_NAME CONSTANT VARCHAR2(30) := 'FEM_SOURCE_DATA_LOADER_PKG';
21
22 G_DEFAULT_DIM_GRP_SIZE CONSTANT NUMBER := 5;
23 G_MAX_DIMS CONSTANT NUMBER := 150;
24 G_INSERT_STMT_TYPE CONSTANT VARCHAR2(10) := 'INSERT';
25 G_INCOMPLETE_LOAD_STATUS CONSTANT VARCHAR2(10) := 'INCOMPLETE';
26 G_COMPLETE_L0AD_STATUS CONSTANT VARCHAR2(10) := 'COMPLETE';
27 G_DATETIME_FORMAT CONSTANT VARCHAR2(24) := 'DD-MM-YYYY HH24:MI:SS';
28 G_NULL_VALUE CONSTANT VARCHAR2(8) := '-1=2_3~4';
29
30 g_log_current_level NUMBER;
31
32 TYPE xdim_info_rec_type IS RECORD
33 (vs_id FEM_GLOBAL_VS_COMBO_DEFS.value_set_id%TYPE,
34 member_b_table_name FEM_XDIM_DIMENSIONS.member_b_table_name%TYPE,
35 member_col FEM_XDIM_DIMENSIONS.member_col%TYPE,
36 member_disp_code_col FEM_XDIM_DIMENSIONS.member_display_code_col%TYPE,
37 target_col_data_type FEM_XDIM_DIMENSIONS.member_data_type_code%TYPE,
38 target_col FEM_TAB_COLUMNS_V.column_name%TYPE,
39 int_disp_code_col FEM_TAB_COLUMNS_V.interface_column_name%TYPE);
40
41 TYPE xdim_info_tbl_type IS TABLE OF xdim_info_rec_type INDEX BY BINARY_INTEGER;
42
43 g_xdim_info_tbl xdim_info_tbl_type;
44 g_num_dims NUMBER;
45
46 e_unexp_error EXCEPTION;
47
48 -- Start of Replacement Enhancement
49 TYPE proc_keys_info_rec_type IS RECORD
50 (
51 target_col FEM_TAB_COLUMNS_V.column_name%TYPE,
52 interface_col FEM_TAB_COLUMNS_V.interface_column_name%TYPE,
53 target_nullable ALL_TAB_COLUMNS.nullable%TYPE);
54
55 TYPE g_proc_keys_info_type IS TABLE OF proc_keys_info_rec_type INDEX BY BINARY_INTEGER;
56 g_proc_keys_tbl g_proc_keys_info_type;
57 g_proc_key_dim_num NUMBER;
58
59 v_param_list wf_parameter_list_t; -- Parameter list for Business Event
60 -- End of Replacement Enhancement
61
62
63 --
64 -- Procedure
65 -- Main
66 -- Purpose
67 -- This is the program executable for the Source Data Loader
68 -- concurrent program. It controls the flow of the loading process.
69 -- History
70 -- 09-07-04 GCHENG Created
71 -- 02-26-06 HKANIVEN Made modification to the code for raising
72 -- Business event upon succesful loading of data.
73 -- Arguments
74 -- errbuf : Message to return to conc mgr
75 -- retcode : Return code to conc mgr
76 -- p_obj_def_id : Object Definition ID that identifies the rule
77 -- version being executed.
78 -- p_exec_mode : Execution mode, S (Snapshot)/R (Replacement)
79 -- E (Error Reprocessing)
80 -- p_ledger_id : Ledger to load data for
81 -- p_cal_period_id : Period to load data for
82 -- p_dataset_code : Dataset to load data for
83 -- p_source_system_code : Source system to load data for
84 -- Notes
85 --
86 PROCEDURE Main (
87 errbuf OUT NOCOPY VARCHAR2,
88 retcode OUT NOCOPY VARCHAR2,
89 p_obj_def_id IN VARCHAR2,
90 p_exec_mode IN VARCHAR2,
91 p_ledger_id IN VARCHAR2,
92 p_cal_period_id IN VARCHAR2,
93 p_dataset_code IN VARCHAR2,
94 p_source_system_code IN VARCHAR2
95 ) IS
96
97 C_MODULE CONSTANT FND_LOG_MESSAGES.module%TYPE :=
98 'fem.plsql.fem_source_data_loader_pkg.main';
99
100 l_obj_def_id NUMBER;
101 l_ledger_id NUMBER;
102 l_cal_period_id NUMBER;
103 l_dataset_code NUMBER;
104 l_source_system_code NUMBER;
105
106 l_object_id FEM_OBJECT_CATALOG_B.object_id%TYPE;
107 l_table_name FEM_TABLE_CLASS_ASSIGNMT_V.table_name%TYPE;
108 l_ledger_dc FEM_LEDGERS_B.ledger_display_code%TYPE;
109 l_calp_dim_grp_dc FEM_DIMENSION_GRPS_B.dimension_group_display_code%TYPE;
110 l_cal_per_end_date FEM_CAL_PERIODS_ATTR.date_assign_value%TYPE;
111 l_cal_per_number FEM_CAL_PERIODS_ATTR.number_assign_value%TYPE;
112 l_dataset_dc FEM_DATASETS_B.dataset_display_code%TYPE;
113 l_source_system_dc FEM_SOURCE_SYSTEMS_B.source_system_display_code%TYPE;
114
115 l_condition VARCHAR2(1000);
116 l_dummy_boolean BOOLEAN;
117 l_process_registered BOOLEAN := FALSE;
118 l_prev_req_id FND_CONCURRENT_REQUESTS.request_id%TYPE;
119 l_exec_state VARCHAR2(30);
120 l_reuse_slices VARCHAR2(1);
121 l_prg_stat VARCHAR2(30);
122 l_exec_status VARCHAR2(30);
123 l_exception_code VARCHAR2(30);
124 l_eng_step FEM_MP_OBJ_STEP_METHODS.step_name%type;
125 l_interface_table_name VARCHAR2(30);
126 l_status FND_PRODUCT_INSTALLATIONS.status%TYPE;
127 l_industry FND_PRODUCT_INSTALLATIONS.industry%TYPE;
128 l_schema_name FND_ORACLE_USERID.oracle_username%TYPE;
129 l_return_status VARCHAR2(1);
130 l_request_id NUMBER;
131
132 e_exp_error EXCEPTION;
133 BEGIN
134 -- set value of debug log current level
135 g_log_current_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
136 l_request_id := FND_GLOBAL.Conc_Request_Id;
137
138 IF G_LOG_LEVEL_PROCEDURE >= g_log_current_level THEN
139 FEM_ENGINES_PKG.TECH_MESSAGE(
140 p_severity => G_LOG_LEVEL_PROCEDURE,
141 p_module => C_MODULE,
142 p_msg_text => 'Begin Procedure');
143 END IF;
144
145 -- Init var
146 l_return_status := G_RET_STS_UNEXP_ERROR;
147 l_obj_def_id := to_number(p_obj_def_id);
148 l_ledger_id := to_number(p_ledger_id);
149 l_cal_period_id := to_number(p_cal_period_id);
150 l_dataset_code := to_number(p_dataset_code);
151 l_source_system_code := to_number(p_source_system_code);
152
153 Validate_Loader_Parameters(
154 p_obj_def_id => l_obj_def_id,
155 p_exec_mode => p_exec_mode,
156 p_ledger_id => l_ledger_id,
157 p_cal_period_id => l_cal_period_id,
158 p_dataset_code => l_dataset_code,
159 p_source_system_code => l_source_system_code,
160 x_object_id => l_object_id,
161 x_table_name => l_table_name,
162 x_calp_dim_grp_dc => l_calp_dim_grp_dc,
163 x_cal_per_end_date => l_cal_per_end_date,
164 x_cal_per_number => l_cal_per_number,
165 x_dataset_dc => l_dataset_dc,
166 x_source_system_dc => l_source_system_dc,
167 x_ledger_dc => l_ledger_dc,
168 x_return_status => l_return_status);
169
170 -- Bug 5124844 hkaniven start
171 -- Start of Replacement enhancement
172
173 FEM_ENGINES_PKG.User_Message
174 (p_app_name => 'FEM',
175 p_msg_name => 'FEM_SD_LDR_PARAMETERS',
176 p_token1 => 'DIM_GRP',
177 p_value1 => l_calp_dim_grp_dc,
178 p_token2 => 'PER_NUM',
179 p_value2 => l_cal_per_number,
180 p_token3 => 'END_DATE',
181 p_value3 => l_cal_per_end_date,
182 p_token4 => 'LEDGER_DC',
183 p_value4 => l_ledger_dc,
184 p_token5 => 'DATASET_DC',
185 p_value5 => l_dataset_dc,
186 p_token6 => 'SOURCE_DC',
187 p_value6 => l_source_system_dc,
188 p_token7 => 'EXEC_MODE',
189 p_value7 => p_exec_mode,
190 p_token8 => 'TABLE_NAME',
191 p_value8 => l_table_name);
192
193 FEM_ENGINES_PKG.Tech_Message
194 (p_severity => G_LOG_LEVEL_STATEMENT,
195 p_module => C_MODULE,
196 p_app_name => 'FEM',
197 p_msg_name => 'FEM_SD_LDR_PARAMETERS',
198 p_token1 => 'DIM_GRP',
199 p_value1 => l_calp_dim_grp_dc,
200 p_token2 => 'PER_NUM',
201 p_value2 => l_cal_per_number,
202 p_token3 => 'END_DATE',
203 p_value3 => l_cal_per_end_date,
204 p_token4 => 'LEDGER_DC',
205 p_value4 => l_ledger_dc,
206 p_token5 => 'DATASET_DC',
207 p_value5 => l_dataset_dc,
208 p_token6 => 'SOURCE_DC',
209 p_value6 => l_source_system_dc,
210 p_token7 => 'EXEC_MODE',
211 p_value7 => p_exec_mode,
212 p_token8 => 'TABLE_NAME',
213 p_value8 => l_table_name);
214
215 -- End of Replacement enhancement
216 -- Bug 5124844 hkaniven end
217
218 IF l_return_status = G_RET_STS_ERROR THEN
219 RAISE e_exp_error;
220 ELSIF l_return_status = G_RET_STS_UNEXP_ERROR THEN
221 RAISE e_unexp_error;
222 END IF;
223
224 Register_Process_Execution (
225 p_object_id => l_object_id,
226 p_obj_def_id => l_obj_def_id,
227 p_table_name => l_table_name,
228 p_exec_mode => p_exec_mode,
229 p_ledger_id => l_ledger_id,
230 p_cal_period_id => l_cal_period_id,
231 p_dataset_code => l_dataset_code,
232 p_source_system_code => l_source_system_code,
233 p_request_id => FND_GLOBAL.Conc_Request_Id,
234 p_user_id => FND_GLOBAL.User_Id,
235 p_login_id => FND_GLOBAL.Login_Id,
236 p_program_id => FND_GLOBAL.Conc_Program_Id,
237 p_program_application_id => FND_GLOBAL.Prog_Appl_ID,
238 x_prev_req_id => l_prev_req_id,
239 x_exec_state => l_exec_state,
240 x_return_status => l_return_status);
241
242 IF l_return_status = G_RET_STS_SUCCESS THEN
243 l_process_registered := TRUE;
244 ELSIF l_return_status = G_RET_STS_ERROR THEN
245 RAISE e_exp_error;
246 ELSE
247 RAISE e_unexp_error;
248 END IF;
249
250 -- Find the schema name for FEM to allow truncation of the global
251 -- temporary table FEM_SOURCE_DATA_INTERIM_GT.
252 IF NOT FND_INSTALLATION.Get_App_Info
253 (application_short_name => 'FEM',
254 status => l_status,
255 industry => l_industry,
256 oracle_schema => l_schema_name) THEN
257 IF G_LOG_LEVEL_PROCEDURE >= g_log_current_level THEN
258 FEM_ENGINES_PKG.TECH_MESSAGE(
259 p_severity => G_LOG_LEVEL_PROCEDURE,
260 p_module => C_MODULE,
261 p_msg_text => 'Failed in obtaining the schema name for FEM');
262 END IF;
263
264 RAISE e_unexp_error;
265 END IF;
266
267 -- Get the interface table name
268 SELECT interface_table_name
269 INTO l_interface_table_name
270 FROM fem_tables_b
271 WHERE table_name = l_table_name;
272
273 Set_MP_Condition (
274 p_exec_mode => p_exec_mode,
275 p_calp_dim_grp_dc => l_calp_dim_grp_dc,
276 p_cal_per_end_date => l_cal_per_end_date,
277 p_cal_per_number => l_cal_per_number,
278 p_dataset_dc => l_dataset_dc,
279 p_source_system_dc => l_source_system_dc,
280 p_ledger_dc => l_ledger_dc,
281 x_condition => l_condition,
282 x_return_status => l_return_status);
283
284 IF l_return_status = G_RET_STS_ERROR THEN
285 RAISE e_exp_error;
286 ELSIF l_return_status = G_RET_STS_UNEXP_ERROR THEN
287 RAISE e_unexp_error;
288 END IF;
289
290 -- If the object execution state is RESTART, then the MP framework
291 -- picks up where it left off in the previous attempt, with the
292 -- next unprocessed data slice.
293 --
294 -- For object execution states of NORMAL or RERUN, the MP framework
295 -- must re-compute the data slices. Some RERUN cases may be able to
296 -- reuse the previous run's slices, but it's not guaranteed that the
297 -- data in the table hasn't changed between runs.
298 IF l_exec_state = 'RESTART' THEN
299 l_reuse_slices := 'Y';
300 ELSE
301 l_reuse_slices := 'N';
302 END IF;
303
304 FEM_MULTI_PROC_PKG.Master (
305 x_prg_stat => l_prg_stat,
306 x_exception_code => l_exception_code,
307 p_rule_id => l_object_id,
308 p_eng_step => 'ALL',
309 p_data_table => l_interface_table_name,
310 p_eng_sql => null,
311 p_eng_prg => 'FEM_SOURCE_DATA_LOADER_PKG.Process_Data',
312 p_condition => l_condition,
313 p_failed_req_id => l_prev_req_id,
314 p_reuse_slices => l_reuse_slices,
315 p_arg1 => FND_GLOBAL.Conc_Request_Id,
316 p_arg2 => p_exec_mode,
317 p_arg3 => l_table_name,
318 p_arg4 => l_interface_table_name,
319 p_arg5 => l_object_id,
320 p_arg6 => l_ledger_id,
321 p_arg7 => l_cal_period_id,
322 p_arg8 => l_dataset_code,
323 p_arg9 => l_source_system_code,
324 p_arg10 => l_schema_name,
325 p_arg11 => l_condition);
326
327 IF G_LOG_LEVEL_PROCEDURE >= g_log_current_level THEN
328 FEM_ENGINES_PKG.TECH_MESSAGE(
329 p_severity => G_LOG_LEVEL_PROCEDURE,
330 p_module => C_MODULE,
331 p_msg_text => 'MP Master return with status '||l_prg_stat);
332 END IF;
333
334
335 IF l_prg_stat = 'COMPLETE:NORMAL' THEN
336 l_exec_status := 'SUCCESS';
337
338 -- Start of Replacement enhancement
339
340 -- Business Event should be raised after the successful completion
341 -- Prepare the parameter list
342
343 WF_EVENT.addparametertolist
344 (p_name => 'REQUEST_ID',
345 p_value => l_request_id,
346 p_parameterlist => v_param_list);
347 WF_EVENT.addparametertolist
348 (p_name => 'OBJECT_ID',
349 p_value => l_object_id,
350 p_parameterlist => v_param_list);
351 WF_EVENT.addparametertolist
352 (p_name => 'EXEC_MODE',
353 p_value => p_exec_mode,
354 p_parameterlist => v_param_list);
355 WF_EVENT.addparametertolist
356 (p_name => 'TABLE_NAME',
357 p_value => l_table_name,
358 p_parameterlist => v_param_list);
359 WF_EVENT.addparametertolist
360 (p_name => 'LEDGER_ID',
361 p_value => p_ledger_id,
362 p_parameterlist => v_param_list);
363 WF_EVENT.addparametertolist
364 (p_name => 'CAL_PERIOD_ID',
365 p_value => p_cal_period_id,
366 p_parameterlist => v_param_list);
367 WF_EVENT.addparametertolist
368 (p_name => 'DATASET_CODE',
369 p_value => p_dataset_code,
370 p_parameterlist => v_param_list);
371 WF_EVENT.addparametertolist
372 (p_name => 'SOURCE_SYSTEM_CODE',
373 p_value => p_source_system_code,
374 p_parameterlist => v_param_list);
375 WF_EVENT.addparametertolist
376 (p_name => 'LOAD_STATUS',
377 p_value => 'COMPLETE',
378 p_parameterlist => v_param_list);
379
380 WF_EVENT.RAISE
381 (p_event_name => 'oracle.apps.fem.loader.fact.execute',
382 p_event_key => NULL,
383 p_parameters => v_param_list );
384
385 v_param_list.DELETE;
386
387 -- End of Replacement enhancement
388
389 ELSE
390 IF l_exception_code = 'FEM_MP_NO_DATA_SLICES_ERR' THEN
391 FEM_ENGINES_PKG.User_Message
392 (p_app_name => 'FEM',
393 p_msg_name => 'FEM_SD_LDR_NO_SLICES',
394 p_token1 => 'DIM_GRP',
395 p_value1 => l_calp_dim_grp_dc,
396 p_token2 => 'PER_NUM',
397 p_value2 => l_cal_per_number,
398 p_token3 => 'END_DATE',
399 p_value3 => FND_DATE.date_to_displayDT(l_cal_per_end_date),
400 p_token4 => 'LEDGER_DC',
401 p_value4 => l_ledger_dc,
402 p_token5 => 'DATASET_DC',
403 p_value5 => l_dataset_dc,
404 p_token6 => 'SOURCE_DC',
405 p_value6 => l_source_system_dc,
406 p_token7 => 'EXEC_MODE',
407 p_value7 => p_exec_mode);
408 FEM_ENGINES_PKG.Tech_Message
409 (p_severity => G_LOG_LEVEL_STATEMENT,
410 p_module => C_MODULE,
411 p_app_name => 'FEM',
412 p_msg_name => 'FEM_SD_LDR_NO_SLICES',
413 p_token1 => 'DIM_GRP',
414 p_value1 => l_calp_dim_grp_dc,
415 p_token2 => 'PER_NUM',
416 p_value2 => l_cal_per_number,
417 p_token3 => 'END_DATE',
418 p_value3 => FND_DATE.date_to_displayDT(l_cal_per_end_date),
419 p_token4 => 'LEDGER_DC',
420 p_value4 => l_ledger_dc,
421 p_token5 => 'DATASET_DC',
422 p_value5 => l_dataset_dc,
423 p_token6 => 'SOURCE_DC',
424 p_value6 => l_source_system_dc,
425 p_token7 => 'EXEC_MODE',
426 p_value7 => p_exec_mode);
427 END IF;
428 l_exec_status := 'ERROR_RERUN';
429 END IF;
430
431 Post_Process (
432 p_object_id => l_object_id,
433 p_obj_def_id => l_obj_def_id,
434 p_table_name => l_table_name,
435 p_exec_mode => p_exec_mode,
436 p_ledger_id => l_ledger_id,
437 p_cal_period_id => l_cal_period_id,
438 p_dataset_code => l_dataset_code,
439 p_source_system_code => l_source_system_code,
440 p_exec_status => l_exec_status,
441 p_request_id => FND_GLOBAL.Conc_Request_Id,
442 p_user_id => FND_GLOBAL.User_Id,
443 p_login_id => FND_GLOBAL.Login_Id,
444 x_return_status => l_return_status);
445
446 IF G_LOG_LEVEL_PROCEDURE >= g_log_current_level THEN
447 FEM_ENGINES_PKG.TECH_MESSAGE(
448 p_severity => G_LOG_LEVEL_PROCEDURE,
449 p_module => C_MODULE,
450 p_msg_text => 'End Procedure');
451 END IF;
452
453 EXCEPTION
454 WHEN e_exp_error THEN
455 -- Process locks clean up is only necessary if registration
456 -- had already taken place.
457 IF l_process_registered THEN
458 Post_Process (
459 p_object_id => l_object_id,
460 p_obj_def_id => l_obj_def_id,
461 p_table_name => l_table_name,
462 p_exec_mode => p_exec_mode,
463 p_ledger_id => l_ledger_id,
464 p_cal_period_id => l_cal_period_id,
465 p_dataset_code => l_dataset_code,
466 p_source_system_code => l_source_system_code,
467 p_exec_status => 'ERROR_RERUN',
468 p_request_id => FND_GLOBAL.Conc_Request_Id,
469 p_user_id => FND_GLOBAL.User_Id,
470 p_login_id => FND_GLOBAL.Login_Id,
471 x_return_status => l_return_status);
472 ELSE
473 -- Post error rerun message to concurrent log
474 FEM_ENGINES_PKG.User_Message
475 (p_app_name => 'FEM',
476 p_msg_name => 'FEM_EXEC_RERUN');
477 FEM_ENGINES_PKG.Tech_Message
478 (p_severity => G_LOG_LEVEL_STATEMENT,
479 p_module => C_MODULE,
480 p_app_name => 'FEM',
481 p_msg_name => 'FEM_EXEC_RERUN');
482 END IF;
483
484 -- set request status to error
485 l_dummy_boolean := FND_CONCURRENT.Set_Completion_Status
486 (status => 'ERROR', message => NULL);
487 WHEN others THEN
488 FEM_ENGINES_PKG.User_Message
489 (p_app_name => 'FEM',
490 p_msg_name => 'FEM_UNEXPECTED_ERROR',
491 p_token1 => 'ERR_MSG',
492 p_value1 => SQLERRM);
493 FEM_ENGINES_PKG.Tech_Message
494 (p_severity => G_LOG_LEVEL_UNEXPECTED,
495 p_module => C_MODULE,
496 p_app_name => 'FEM',
497 p_msg_name => 'FEM_UNEXPECTED_ERROR',
498 p_token1 => 'ERR_MSG',
499 p_value1 => SQLERRM);
500
501 -- Process locks clean up is only necessary if registration
502 -- had already taken place.
503 IF l_process_registered THEN
504 Post_Process (
505 p_object_id => l_object_id,
506 p_obj_def_id => l_obj_def_id,
507 p_table_name => l_table_name,
508 p_exec_mode => p_exec_mode,
509 p_ledger_id => l_ledger_id,
510 p_cal_period_id => l_cal_period_id,
511 p_dataset_code => l_dataset_code,
512 p_source_system_code => l_source_system_code,
513 p_exec_status => 'ERROR_RERUN',
514 p_request_id => FND_GLOBAL.Conc_Request_Id,
515 p_user_id => FND_GLOBAL.User_Id,
516 p_login_id => FND_GLOBAL.Login_Id,
517 x_return_status => l_return_status);
518 ELSE
519 -- Post error rerun message to concurrent log
520 FEM_ENGINES_PKG.User_Message
521 (p_app_name => 'FEM',
522 p_msg_name => 'FEM_EXEC_RERUN');
523 FEM_ENGINES_PKG.Tech_Message
524 (p_severity => G_LOG_LEVEL_STATEMENT,
525 p_module => C_MODULE,
526 p_app_name => 'FEM',
527 p_msg_name => 'FEM_EXEC_RERUN');
528 END IF;
529
530 -- set request status to error
531 l_dummy_boolean := FND_CONCURRENT.Set_Completion_Status
532 (status => 'ERROR', message => NULL);
533 END Main;
534
535 --
536 -- Procedure
537 -- Validate_Loader_Parameters
538 -- Purpose
539 -- Validates the set of parameters passed into the Main module.
540 -- History
541 -- 09-07-04 GCHENG Created
542 -- Arguments
543 -- p_obj_def_id : Object Definition ID that identifies the rule
544 -- version being executed.
545 -- p_exec_mode : Execution mode, S (Snapshot)/R (Replacement)/
546 -- : E (Error Reprocessing)
547 -- p_ledger_id : Ledger to load data for
548 -- p_cal_period_id : Period to load data for
549 -- p_dataset_code : Dataset to load data for
550 -- p_source_system_code : Source system to load data for
551 -- x_object_id : Object ID that identifies the rule being executed.
552 -- x_table_name : Source data target table name
553 -- x_calp_dim_grp_dc : Cal period dimension group display code
554 -- x_cal_per_end_date : Cal period end period date
555 -- x_cal_per_number : Cal period number
556 -- x_dataset_dc : Dataset display code
557 -- x_source_system_dc : Source system display code
558 -- x_ledger_dc : Ledger display code
559 -- x_return_status : Return status
560 -- (look at FND_API for the possible statuses)
561 --
562 -- Notes
563 --
564 PROCEDURE Validate_Loader_Parameters (
565 p_obj_def_id IN NUMBER,
566 p_exec_mode IN VARCHAR2,
567 p_ledger_id IN NUMBER,
568 p_cal_period_id IN NUMBER,
569 p_dataset_code IN NUMBER,
570 p_source_system_code IN NUMBER,
571 x_object_id OUT NOCOPY NUMBER,
572 x_table_name OUT NOCOPY VARCHAR2,
573 x_calp_dim_grp_dc OUT NOCOPY VARCHAR2,
574 x_cal_per_end_date OUT NOCOPY DATE,
575 x_cal_per_number OUT NOCOPY NUMBER,
576 x_dataset_dc OUT NOCOPY VARCHAR2,
577 x_source_system_dc OUT NOCOPY VARCHAR2,
578 x_ledger_dc OUT NOCOPY VARCHAR2,
579 x_return_status OUT NOCOPY VARCHAR2
580 ) IS
581 C_OBJECT_TYPE CONSTANT VARCHAR2(18) := 'SOURCE_DATA_LOADER';
582 C_TABLE_CLASSIFICATION CONSTANT VARCHAR2(17) := 'SOURCE_DATA_TABLE';
583 C_MODULE CONSTANT FND_LOG_MESSAGES.module%TYPE :=
584 'fem.plsql.fem_source_data_loader_pkg.validate_loader_parameters';
585
586 l_msg_count NUMBER;
587 l_msg_data VARCHAR2(4000);
588 l_ledger_calendar_id NUMBER;
589 l_ledger_per_hier_obj_def_id NUMBER;
590 BEGIN
591
592 IF G_LOG_LEVEL_PROCEDURE >= g_log_current_level THEN
593 FEM_ENGINES_PKG.TECH_MESSAGE(
594 p_severity => G_LOG_LEVEL_PROCEDURE,
595 p_module => C_MODULE,
596 p_msg_text => 'Begin Procedure');
597 END IF;
598
599 Validate_Obj_Def(
600 p_api_version => G_API_VERSION,
601 p_object_type => C_OBJECT_TYPE,
602 p_obj_def_id => p_obj_def_id,
603 x_object_id => x_object_id,
604 x_table_name => x_table_name,
605 x_msg_count => l_msg_count,
606 x_msg_data => l_msg_data,
607 x_return_status => x_return_status);
608
609 IF l_msg_count > 0 THEN
610 Get_Put_Messages (
611 p_msg_count => l_msg_count,
612 p_msg_data => l_msg_data);
613 END IF;
614
615 IF x_return_status = G_RET_STS_SUCCESS THEN
616 Validate_Table(
617 p_api_version => G_API_VERSION,
618 p_object_type => C_OBJECT_TYPE,
619 p_table_name => x_table_name,
620 p_table_classification => C_TABLE_CLASSIFICATION,
621 x_msg_count => l_msg_count,
622 x_msg_data => l_msg_data,
623 x_return_status => x_return_status);
624
625 IF l_msg_count > 0 THEN
626 Get_Put_Messages (
627 p_msg_count => l_msg_count,
628 p_msg_data => l_msg_data);
629 END IF;
630 END IF;
631
632 IF x_return_status = G_RET_STS_SUCCESS THEN
633 Validate_Exec_Mode(
634 p_api_version => G_API_VERSION,
635 p_object_type => C_OBJECT_TYPE,
636 p_exec_mode => p_exec_mode,
637 x_msg_count => l_msg_count,
638 x_msg_data => l_msg_data,
639 x_return_status => x_return_status);
640
641 IF l_msg_count > 0 THEN
642 Get_Put_Messages (
643 p_msg_count => l_msg_count,
644 p_msg_data => l_msg_data);
645 END IF;
646 END IF;
647
648 IF x_return_status = G_RET_STS_SUCCESS THEN
649 Validate_Ledger(
650 p_api_version => G_API_VERSION,
651 p_object_type => C_OBJECT_TYPE,
652 p_ledger_id => p_ledger_id,
653 x_ledger_dc => x_ledger_dc,
654 x_ledger_calendar_id => l_ledger_calendar_id,
655 x_ledger_per_hier_obj_def_id => l_ledger_per_hier_obj_def_id,
656 x_msg_count => l_msg_count,
657 x_msg_data => l_msg_data,
658 x_return_status => x_return_status);
659
660 IF l_msg_count > 0 THEN
661 Get_Put_Messages (
662 p_msg_count => l_msg_count,
663 p_msg_data => l_msg_data);
664 END IF;
665 END IF;
666
667 IF x_return_status = G_RET_STS_SUCCESS THEN
668 Validate_Cal_Period(
669 p_api_version => G_API_VERSION,
670 p_object_type => C_OBJECT_TYPE,
671 p_cal_period_id => p_cal_period_id,
672 p_ledger_id => p_ledger_id,
673 p_ledger_calendar_id => l_ledger_calendar_id,
674 p_ledger_per_hier_obj_def_id => l_ledger_per_hier_obj_def_id,
675 x_calp_dim_grp_dc => x_calp_dim_grp_dc,
676 x_cal_per_end_date => x_cal_per_end_date,
677 x_cal_per_number => x_cal_per_number,
678 x_msg_count => l_msg_count,
679 x_msg_data => l_msg_data,
680 x_return_status => x_return_status);
681
682 IF l_msg_count > 0 THEN
683 Get_Put_Messages (
684 p_msg_count => l_msg_count,
685 p_msg_data => l_msg_data);
686 END IF;
687 END IF;
688
689 IF x_return_status = G_RET_STS_SUCCESS THEN
690 Validate_Dataset(
691 p_api_version => G_API_VERSION,
692 p_object_type => C_OBJECT_TYPE,
693 p_dataset_code => p_dataset_code,
694 x_dataset_dc => x_dataset_dc,
695 x_msg_count => l_msg_count,
696 x_msg_data => l_msg_data,
697 x_return_status => x_return_status);
698
699 IF l_msg_count > 0 THEN
700 Get_Put_Messages (
701 p_msg_count => l_msg_count,
702 p_msg_data => l_msg_data);
703 END IF;
704 END IF;
705
706 IF x_return_status = G_RET_STS_SUCCESS THEN
707 Validate_Source_System(
708 p_api_version => G_API_VERSION,
709 p_object_type => C_OBJECT_TYPE,
710 p_source_system_code => p_source_system_code,
711 x_source_system_dc => x_source_system_dc,
712 x_msg_count => l_msg_count,
713 x_msg_data => l_msg_data,
714 x_return_status => x_return_status);
715
716 IF l_msg_count > 0 THEN
717 Get_Put_Messages (
718 p_msg_count => l_msg_count,
719 p_msg_data => l_msg_data);
720 END IF;
721 END IF;
722
723 IF G_LOG_LEVEL_STATEMENT >= g_log_current_level THEN
724 FEM_ENGINES_PKG.TECH_MESSAGE(
725 p_severity => G_LOG_LEVEL_STATEMENT,
726 p_module => C_MODULE,
727 p_msg_text => 'Returning with status of '||x_return_status);
728 END IF;
729 IF G_LOG_LEVEL_PROCEDURE >= g_log_current_level THEN
730 FEM_ENGINES_PKG.TECH_MESSAGE(
731 p_severity => G_LOG_LEVEL_PROCEDURE,
732 p_module => C_MODULE,
733 p_msg_text => 'End Procedure');
734 END IF;
735
736 EXCEPTION
737 WHEN others THEN
738 x_return_status := G_RET_STS_UNEXP_ERROR;
739
740 END Validate_Loader_Parameters;
741
742 --
743 -- Procedure
744 -- Register Process Execution
745 -- Purpose
746 -- This procedure performs the initial process execution registration
747 -- mandated by the FEM process lock architecture. Details on the
748 -- the architecture can be found in the Process Lock APIs
749 -- detail design document.
750 --
751 -- History
752 -- 09-07-04 GCHENG Created
753 -- Arguments
754 -- p_object_id : Object ID that identifies the rule being executed.
755 -- p_obj_def_id : Object Definition ID that identifies the rule
756 -- version being executed.
757 -- p_table_name : Source Data Table Name
758 -- p_exec_mode : Execution mode, S (Snapshot)/R (Replacement)
759 -- E (Error Reprocessing)
760 -- p_ledger_id : Ledger to load data for
761 -- p_cal_period_id : Period to load data for
762 -- p_dataset_code : Dataset to load data for
763 -- p_source_system_code : Source system to load data for
764 -- p_request_id : Concurrent request ID
765 -- p_user_id : User ID
766 -- p_login_id : Login ID
767 -- p_program_id : Concurrent program ID
768 -- p_program_application_id : Concurrent program application ID
769 -- x_prev_req_id : Previously run (and now restarted) concurrent
770 -- request ID
771 -- x_exec_state : Object execution status set by call to
772 -- FEM_PL_PKG.Register_Object_Execution
773 -- x_return_status : Return status
774 -- (look at FND_API for the possible statuses)
775 -- Notes
776 --
777 PROCEDURE Register_Process_Execution (
778 p_object_id IN NUMBER,
779 p_obj_def_id IN NUMBER,
780 p_table_name IN VARCHAR2,
781 p_exec_mode IN VARCHAR2,
782 p_ledger_id IN NUMBER,
783 p_cal_period_id IN NUMBER,
784 p_dataset_code IN NUMBER,
785 p_source_system_code IN NUMBER,
786 p_request_id IN NUMBER,
787 p_user_id IN NUMBER,
788 p_login_id IN NUMBER,
789 p_program_id IN NUMBER,
790 p_program_application_id IN NUMBER,
791 x_prev_req_id OUT NOCOPY NUMBER,
792 x_exec_state OUT NOCOPY VARCHAR2,
793 x_return_status OUT NOCOPY VARCHAR2
794 ) IS
795 C_MODULE CONSTANT FND_LOG_MESSAGES.module%TYPE :=
796 'fem.plsql.fem_source_data_loader_pkg.register_process_execution';
797 l_obj_exec_failed VARCHAR2(1);
798 l_msg_count NUMBER;
799 l_msg_data VARCHAR2(4000);
800 l_return_status VARCHAR2(1);
801
802 e_process_lock_error EXCEPTION;
803 BEGIN
804
805 IF G_LOG_LEVEL_PROCEDURE >= g_log_current_level THEN
806 FEM_ENGINES_PKG.TECH_MESSAGE(
807 p_severity => G_LOG_LEVEL_PROCEDURE,
808 p_module => C_MODULE,
809 p_msg_text => 'Begin Procedure');
810 END IF;
811 -- Log parameters
812 IF G_LOG_LEVEL_STATEMENT >= g_log_current_level THEN
813 FEM_ENGINES_PKG.TECH_MESSAGE(
814 p_severity => G_LOG_LEVEL_STATEMENT,
815 p_module => C_MODULE,
816 p_msg_text => 'Parameter: Object ID is '||p_object_id);
817 FEM_ENGINES_PKG.TECH_MESSAGE(
818 p_severity => G_LOG_LEVEL_STATEMENT,
819 p_module => C_MODULE,
820 p_msg_text => 'Parameter: Object Definition ID is '||p_obj_def_id);
821 FEM_ENGINES_PKG.TECH_MESSAGE(
822 p_severity => G_LOG_LEVEL_STATEMENT,
823 p_module => C_MODULE,
824 p_msg_text => 'Parameter: Table Name is '||p_table_name);
825 FEM_ENGINES_PKG.TECH_MESSAGE(
826 p_severity => G_LOG_LEVEL_STATEMENT,
827 p_module => C_MODULE,
828 p_msg_text => 'Parameter: Exec Mode is '||p_exec_mode);
829 FEM_ENGINES_PKG.TECH_MESSAGE(
830 p_severity => G_LOG_LEVEL_STATEMENT,
831 p_module => C_MODULE,
832 p_msg_text => 'Parameter: Ledger ID is '||p_ledger_id);
833 FEM_ENGINES_PKG.TECH_MESSAGE(
834 p_severity => G_LOG_LEVEL_STATEMENT,
835 p_module => C_MODULE,
836 p_msg_text => 'Parameter: Calendar Period ID is '||p_cal_period_id);
837 FEM_ENGINES_PKG.TECH_MESSAGE(
838 p_severity => G_LOG_LEVEL_STATEMENT,
839 p_module => C_MODULE,
840 p_msg_text => 'Parameter: Datset Code is '||p_dataset_code);
841 FEM_ENGINES_PKG.TECH_MESSAGE(
842 p_severity => G_LOG_LEVEL_STATEMENT,
843 p_module => C_MODULE,
844 p_msg_text => 'Parameter: Source System Code is '||p_source_system_code);
845 FEM_ENGINES_PKG.TECH_MESSAGE(
846 p_severity => G_LOG_LEVEL_STATEMENT,
847 p_module => C_MODULE,
848 p_msg_text => 'Parameter: Concurrent Request ID is '||p_request_id);
849 FEM_ENGINES_PKG.TECH_MESSAGE(
850 p_severity => G_LOG_LEVEL_STATEMENT,
851 p_module => C_MODULE,
852 p_msg_text => 'Parameter: User ID is '||p_user_id);
853 FEM_ENGINES_PKG.TECH_MESSAGE(
854 p_severity => G_LOG_LEVEL_STATEMENT,
855 p_module => C_MODULE,
856 p_msg_text => 'Parameter: Login ID is '||p_login_id);
857 FEM_ENGINES_PKG.TECH_MESSAGE(
858 p_severity => G_LOG_LEVEL_STATEMENT,
859 p_module => C_MODULE,
860 p_msg_text => 'Parameter: Concurrent Program ID is '||p_program_id);
861 FEM_ENGINES_PKG.TECH_MESSAGE(
862 p_severity => G_LOG_LEVEL_STATEMENT,
863 p_module => C_MODULE,
864 p_msg_text => 'Parameter: Concurrent Program Application ID is '||
865 p_program_application_id);
866 END IF;
867
868 -- Init var
869 l_obj_exec_failed := G_FALSE;
870
871 FEM_PL_PKG.Register_Request
872 (p_api_version => G_API_VERSION,
873 p_commit => G_TRUE,
874 p_cal_period_id => p_cal_period_id,
875 p_ledger_id => p_ledger_id,
876 p_output_dataset_code => p_dataset_code,
877 p_source_system_code => p_source_system_code,
878 p_request_id => p_request_id,
879 p_user_id => p_user_id,
880 p_last_update_login => p_login_id,
881 p_program_id => p_program_id,
882 p_program_login_id => p_login_id,
883 p_program_application_id => p_program_application_id,
884 p_exec_mode_code => p_exec_mode,
885 p_table_name => p_table_name,
886 x_msg_count => l_msg_count,
887 x_msg_data => l_msg_data,
888 x_return_status => l_return_status);
889
890 IF G_LOG_LEVEL_STATEMENT >= g_log_current_level THEN
891 FEM_ENGINES_PKG.Tech_Message
892 (p_severity => G_LOG_LEVEL_STATEMENT,
893 p_module => C_MODULE,
894 p_msg_text => 'Call to FEM_PL_PKG.Register_Request returned with status '
895 ||l_return_status);
896 END IF;
897
898 IF l_msg_count > 0 THEN
899 Get_Put_Messages (
900 p_msg_count => l_msg_count,
901 p_msg_data => l_msg_data);
902 END IF;
903
904 IF l_return_status <> G_RET_STS_SUCCESS THEN
905 RAISE e_process_lock_error;
906 END IF;
907
908 FEM_PL_PKG.Register_Object_Execution
909 (p_api_version => G_API_VERSION,
910 p_commit => G_TRUE,
911 p_request_id => p_request_id,
912 p_object_id => p_object_id,
913 p_exec_object_definition_id => p_obj_def_id,
914 p_user_id => p_user_id,
915 p_last_update_login => p_login_id,
916 p_exec_mode_code => p_exec_mode,
917 x_exec_state => x_exec_state,
918 x_prev_request_id => x_prev_req_id,
919 x_msg_count => l_msg_count,
920 x_msg_data => l_msg_data,
921 x_return_status => l_return_status);
922
923 IF G_LOG_LEVEL_STATEMENT >= g_log_current_level THEN
924 FEM_ENGINES_PKG.Tech_Message
925 (p_severity => G_LOG_LEVEL_STATEMENT,
926 p_module => C_MODULE,
927 p_msg_text => 'Call to FEM_PL_PKG.Register_Object_Execution returned with '
928 ||' status as '||l_return_status
929 ||', execution state as '||x_exec_state
930 ||' and previous request ID as '||to_char(x_prev_req_id));
931 END IF;
932
933 IF l_msg_count > 0 THEN
934 Get_Put_Messages (
935 p_msg_count => l_msg_count,
936 p_msg_data => l_msg_data);
937 END IF;
938
939 IF l_return_status <> G_RET_STS_SUCCESS THEN
940 -- Regardless of the reason why object execution did not succeed
941 -- we want to flag it to allow for cleanup in the exceptions block
942 l_obj_exec_failed := G_TRUE;
943 RAISE e_process_lock_error;
944 END IF;
945
946 FEM_PL_PKG.Register_Object_Def
947 (p_api_version => G_API_VERSION,
948 p_commit => G_TRUE,
949 p_request_id => p_request_id,
950 p_object_id => p_object_id,
951 p_object_definition_id => p_obj_def_id,
952 p_user_id => p_user_id,
953 p_last_update_login => p_login_id,
954 x_msg_count => l_msg_count,
955 x_msg_data => l_msg_data,
956 x_return_status => l_return_status);
957
958 IF G_LOG_LEVEL_STATEMENT >= g_log_current_level THEN
959 FEM_ENGINES_PKG.Tech_Message
960 (p_severity => G_LOG_LEVEL_STATEMENT,
961 p_module => C_MODULE,
962 p_msg_text => 'Call to FEM_PL_PKG.Register_Object_Def returned with status '
963 ||l_return_status);
964 END IF;
965
966 IF l_msg_count > 0 THEN
967 Get_Put_Messages (
968 p_msg_count => l_msg_count,
969 p_msg_data => l_msg_data);
970 END IF;
971
972 IF l_return_status <> G_RET_STS_SUCCESS THEN
973 RAISE e_process_lock_error;
974 END IF;
975
976 FEM_PL_PKG.Register_Table
977 (p_api_version => G_API_VERSION,
978 p_commit => G_TRUE,
979 p_request_id => p_request_id,
980 p_object_id => p_object_id,
981 p_table_name => p_table_name,
982 p_statement_type => G_INSERT_STMT_TYPE,
983 p_num_of_output_rows => 0,
984 p_user_id => p_user_id,
985 p_last_update_login => p_login_id,
986 x_msg_count => l_msg_count,
987 x_msg_data => l_msg_data,
988 x_return_status => l_return_status);
989
990 IF G_LOG_LEVEL_STATEMENT >= g_log_current_level THEN
991 FEM_ENGINES_PKG.Tech_Message
992 (p_severity => G_LOG_LEVEL_STATEMENT,
993 p_module => C_MODULE,
994 p_msg_text => 'Call to FEM_PL_PKG.Register_Table returned with status '
995 ||l_return_status);
996 END IF;
997
998 IF l_msg_count > 0 THEN
999 Get_Put_Messages (
1000 p_msg_count => l_msg_count,
1001 p_msg_data => l_msg_data);
1002 END IF;
1003
1004 IF l_return_status <> G_RET_STS_SUCCESS THEN
1005 RAISE e_process_lock_error;
1006 END IF;
1007
1008 -- Register Data Location
1009 BEGIN
1010 FEM_DIMENSION_UTIL_PKG.register_data_location
1011 (p_request_id => p_request_id,
1012 p_object_id => p_object_id,
1013 p_table_name => p_table_name,
1014 p_ledger_id => p_ledger_id,
1015 p_cal_per_id => p_cal_period_id,
1016 p_dataset_cd => p_dataset_code,
1017 p_source_cd => p_source_system_code,
1018 p_load_status => G_INCOMPLETE_LOAD_STATUS);
1019 EXCEPTION
1020 WHEN others THEN
1021 x_return_status := G_RET_STS_UNEXP_ERROR;
1022
1023 FEM_ENGINES_PKG.Tech_Message
1024 (p_severity => G_LOG_LEVEL_STATEMENT,
1025 p_module => C_MODULE,
1026 p_msg_text => 'Call to FEM_DIMENSION_UTIL_PKG.register_data_location '
1027 ||'failed unexpectedly.');
1028 RAISE;
1029 END;
1030
1031 -- This commit is for the call to Register Data Locations.
1032 -- All other API's commit work as it completes successfully.
1033 COMMIT;
1034
1035 x_return_status := G_RET_STS_SUCCESS;
1036
1037 IF G_LOG_LEVEL_STATEMENT >= g_log_current_level THEN
1038 FEM_ENGINES_PKG.TECH_MESSAGE(
1039 p_severity => G_LOG_LEVEL_STATEMENT,
1040 p_module => C_MODULE,
1041 p_msg_text => 'Procedure returning with status of '||x_return_status);
1042 END IF;
1043 IF G_LOG_LEVEL_PROCEDURE >= g_log_current_level THEN
1044 FEM_ENGINES_PKG.TECH_MESSAGE(
1045 p_severity => G_LOG_LEVEL_PROCEDURE,
1046 p_module => C_MODULE,
1047 p_msg_text => 'End Procedure');
1048 END IF;
1049 EXCEPTION
1050 WHEN e_process_lock_error THEN
1051 x_return_status := G_RET_STS_ERROR;
1052 -- No need to post error messages as it was taken care of by the calls
1053 -- to get_push_messages after every API call.
1054
1055 IF G_LOG_LEVEL_STATEMENT >= g_log_current_level THEN
1056 FEM_ENGINES_PKG.Tech_Message
1057 (p_severity => G_LOG_LEVEL_STATEMENT,
1058 p_module => C_MODULE,
1059 p_msg_text => 'Exception e_process_lock_error raised.');
1060 END IF;
1061
1062 IF l_obj_exec_failed = G_TRUE THEN
1063 -- Unregister the concurrent request to clean up data
1064 FEM_PL_PKG.Unregister_Request
1065 (p_api_version => G_API_VERSION,
1066 p_commit => G_FALSE,
1067 p_request_id => p_request_id,
1068 x_msg_count => l_msg_count,
1069 x_msg_data => l_msg_data,
1070 x_return_status => l_return_status);
1071
1072 FEM_ENGINES_PKG.TECH_MESSAGE(
1073 p_severity => G_LOG_LEVEL_STATEMENT,
1074 p_module => C_MODULE,
1075 p_msg_text => 'Call to FEM_PL_PKG.Unregister_Request returned with status '
1076 ||l_return_status);
1077 END IF;
1078
1079 WHEN others THEN
1080 -- Unexpected exceptions
1081 x_return_status := G_RET_STS_UNEXP_ERROR;
1082
1083 END Register_Process_Execution;
1084
1085 --
1086 -- Procedure
1087 -- Populate_xDim_Info_Tbl
1088 -- Purpose
1089 -- Populates the global table g_xdim_info_tbl.
1090 -- g_xdim_info_tbl stores the xdim properties for those dimensions
1091 -- that belong to the table as specified by the parameter.
1092 -- History
1093 -- 09-07-04 GCHENG Created
1094 -- Arguments
1095 -- p_table_name : Table for which xdim info will be looked up
1096 -- p_ledger_id : Ledger ID need to find the Global VS Combo
1097 -- x_return_status : Return status
1098 -- (look at FND_API for the possible statuses)
1099 -- Notes
1100 --
1101 PROCEDURE Populate_xDim_Info_Tbl(
1102 p_table_name IN VARCHAR2,
1103 p_ledger_id IN NUMBER,
1104 x_return_status OUT NOCOPY VARCHAR2
1105 ) IS
1106 C_MODULE CONSTANT FND_LOG_MESSAGES.module%TYPE :=
1107 'fem.plsql.fem_source_data_loader_pkg.populate_xdim_info_tbl';
1108
1109 l_global_vs_combo_id NUMBER;
1110 l_msg_count NUMBER;
1111 l_msg_data VARCHAR2(4000);
1112 BEGIN
1113 IF G_LOG_LEVEL_PROCEDURE >= g_log_current_level THEN
1114 FEM_ENGINES_PKG.TECH_MESSAGE(
1115 p_severity => G_LOG_LEVEL_PROCEDURE,
1116 p_module => C_MODULE,
1117 p_msg_text => 'Begin Procedure');
1118 END IF;
1119
1120 -- Init to zero
1121 g_num_dims := 0;
1122
1123 -- In case this procedure is called twice in the same session
1124 -- make sure to remove the previous dimension elements.
1125 IF g_xdim_info_tbl.COUNT > 0 THEN
1126 IF G_LOG_LEVEL_STATEMENT >= g_log_current_level THEN
1127 FEM_ENGINES_PKG.Tech_Message
1128 (p_severity => G_LOG_LEVEL_STATEMENT,
1129 p_module => C_MODULE,
1130 p_msg_text => 'g_xdim_info_tbl is not empty. Deleting all elements'
1131 ||' to ensure a fresh start when loading new dimension info.');
1132 END IF;
1133
1134 g_xdim_info_tbl.DELETE;
1135 END IF;
1136
1137 --
1138 -- lookup the global value set combination id tied to the ledger
1139 --
1140 l_global_vs_combo_id := FEM_DIMENSION_UTIL_PKG.GLOBAL_VS_COMBO_ID
1141 (p_encoded => G_FALSE,
1142 x_return_status => x_return_status,
1143 x_msg_count => l_msg_count,
1144 x_msg_data => l_msg_data,
1145 p_ledger_id => p_ledger_id);
1146
1147 IF l_msg_count > 0 THEN
1148 Get_Put_Messages (
1149 p_msg_count => l_msg_count,
1150 p_msg_data => l_msg_data);
1151 END IF;
1152
1153 IF x_return_status = G_RET_STS_SUCCESS THEN
1154 IF G_LOG_LEVEL_STATEMENT >= g_log_current_level THEN
1155 FEM_ENGINES_PKG.Tech_Message
1156 (p_severity => G_LOG_LEVEL_STATEMENT,
1157 p_module => C_MODULE,
1158 p_msg_text => 'Global Value Set Combination ID is '
1159 ||to_char(l_global_vs_combo_id));
1160 END IF;
1161 ELSE
1162 FEM_ENGINES_PKG.Tech_Message
1163 (p_severity => G_LOG_LEVEL_STATEMENT,
1164 p_module => C_MODULE,
1165 p_msg_text => 'Could not find the Global Value Set Combination ID '
1166 ||'associated with the ledger');
1167
1168 RAISE e_unexp_error;
1169 END IF;
1170
1171 --
1172 -- populate the dimension properties record table
1173 --
1174 BEGIN
1175 SELECT gv.value_set_id,
1176 xd.member_b_table_name,
1177 xd.member_col,
1178 xd.member_display_code_col,
1179 xd.member_data_type_code,
1180 tc.column_name,
1181 tc.interface_column_name
1182 BULK COLLECT INTO g_xdim_info_tbl
1183 FROM fem_tab_columns_v tc,
1184 fem_xdim_dimensions xd,
1185 fem_global_vs_combo_defs gv
1186 WHERE tc.table_name = p_table_name
1187 AND tc.fem_data_type_code = 'DIMENSION'
1188 AND tc.column_name NOT IN ('CREATED_BY_OBJECT_ID','LAST_UPDATED_BY_OBJECT_ID','LEDGER_ID','CAL_PERIOD_ID','DATASET_CODE','SOURCE_SYSTEM_CODE')
1189 AND xd.dimension_id = tc.dimension_id
1190 AND xd.dimension_id = gv.dimension_id (+)
1191 AND gv.global_vs_combo_id (+) = l_global_vs_combo_id;
1192 EXCEPTION
1193 WHEN no_data_found THEN
1194 g_num_dims := 0;
1195 END;
1196
1197 g_num_dims := SQL%ROWCOUNT;
1198
1199 IF G_LOG_LEVEL_STATEMENT >= g_log_current_level THEN
1200 FEM_ENGINES_PKG.Tech_Message
1201 (p_severity => G_LOG_LEVEL_STATEMENT,
1202 p_module => C_MODULE,
1203 p_msg_text => 'Number of dimenions is '
1204 ||to_char(g_num_dims));
1205 END IF;
1206
1207 IF g_num_dims > G_MAX_DIMS THEN
1208 FEM_ENGINES_PKG.TECH_MESSAGE(
1209 p_severity => G_LOG_LEVEL_STATEMENT,
1210 p_module => C_MODULE,
1211 p_msg_text => 'Number of dimensions for this table ('||to_number(g_num_dims)
1212 ||') exceeds the maximum number of supported dimensions ('
1213 ||to_number(G_MAX_DIMS)||').');
1214 RAISE e_unexp_error;
1215 END IF;
1216
1217 x_return_status := G_RET_STS_SUCCESS;
1218
1219 IF G_LOG_LEVEL_STATEMENT >= g_log_current_level THEN
1220 FEM_ENGINES_PKG.TECH_MESSAGE(
1221 p_severity => G_LOG_LEVEL_STATEMENT,
1222 p_module => C_MODULE,
1223 p_msg_text => 'Procedure returning with status of '||x_return_status);
1224 END IF;
1225 IF G_LOG_LEVEL_PROCEDURE >= g_log_current_level THEN
1226 FEM_ENGINES_PKG.TECH_MESSAGE(
1227 p_severity => G_LOG_LEVEL_PROCEDURE,
1228 p_module => C_MODULE,
1229 p_msg_text => 'End Procedure');
1230 END IF;
1231
1232 EXCEPTION
1233 WHEN others THEN
1234 x_return_status := G_RET_STS_UNEXP_ERROR;
1235
1236 END Populate_xDim_Info_Tbl;
1237
1238
1239 --
1240 -- Procedure
1241 -- Set_MP_Condition
1242 -- Purpose
1243 -- Sets the condition SQL to be passed into MP Master
1244 -- History
1245 -- 09-14-04 GCHENG Created
1246 -- 02-27-06 HKANIVEN Made modifications to the condition
1247 -- support Replacement mode
1248 -- Arguments
1249 -- p_exec_mode : Execution mode, S (Snapshot)/R (Replacement)
1250 -- E (Error Reprocessing)
1251 -- p_calp_dim_grp_dc : Cal period dimension group display code
1252 -- p_cal_per_end_date : Cal period end period date
1253 -- p_cal_per_number : Cal period number
1254 -- p_dataset_dc : Dataset display code
1255 -- p_source_system_dc : Source system display code
1256 -- p_ledger_dc : Ledger display code
1257 -- x_condition : Multiprocessing p_condition
1258 -- dimension validation
1259 -- x_return_status : Return status
1260 -- (look at FND_API for the possible statuses)
1261 -- Notes
1262 --
1263 PROCEDURE Set_MP_Condition (
1264 p_exec_mode IN VARCHAR2,
1265 p_calp_dim_grp_dc IN VARCHAR2,
1266 p_cal_per_end_date IN DATE,
1267 p_cal_per_number IN NUMBER,
1268 p_dataset_dc IN VARCHAR2,
1269 p_source_system_dc IN VARCHAR2,
1270 p_ledger_dc IN VARCHAR2,
1271 x_condition OUT NOCOPY VARCHAR2,
1272 x_return_status OUT NOCOPY VARCHAR2
1273 ) IS
1274 C_MODULE CONSTANT FND_LOG_MESSAGES.module%TYPE :=
1275 'fem.plsql.fem_source_data_loader_pkg.set_mp_condition';
1276
1277 BEGIN
1278 IF G_LOG_LEVEL_PROCEDURE >= g_log_current_level THEN
1279 FEM_ENGINES_PKG.TECH_MESSAGE(
1280 p_severity => G_LOG_LEVEL_PROCEDURE,
1281 p_module => C_MODULE,
1282 p_msg_text => 'Begin Procedure');
1283 END IF;
1284
1285 --
1286 -- set the condition to be passed to the multiprocessing master
1287 --
1288 x_condition :=
1289 ' calp_dim_grp_display_code = '''||p_calp_dim_grp_dc||''''
1290 ||' AND cal_period_end_date = TO_DATE('''
1291 ||TO_CHAR(p_cal_per_end_date, G_DATETIME_FORMAT)
1292 ||''','''||G_DATETIME_FORMAT||''')'
1293 ||' AND cal_period_number = '||TO_CHAR(p_cal_per_number)
1294 ||' AND source_system_display_code = '''||p_source_system_dc||''''
1295 ||' AND dataset_display_code = '''||p_dataset_dc||''''
1296 ||' AND ledger_display_code = '''||p_ledger_dc||'''';
1297
1298
1299 -- Start of Replacement mode enhancement
1300 -- In Replacement mode all the rows irrespective of their 'STATUS'
1301 -- column value should be processed
1302 IF (p_exec_mode = 'S') THEN
1303 x_condition := x_condition||' AND status = ''LOAD''';
1304 ELSIF p_exec_mode = 'E' THEN
1305 x_condition := x_condition||' AND status <> ''LOAD''';
1306 ELSE
1307 NULL;
1308 END IF;
1309 -- End of Replacement mode enhancement
1310
1311 x_return_status := G_RET_STS_SUCCESS;
1312
1313 IF G_LOG_LEVEL_STATEMENT >= g_log_current_level THEN
1314 FEM_ENGINES_PKG.TECH_MESSAGE(
1315 p_severity => G_LOG_LEVEL_STATEMENT,
1316 p_module => C_MODULE,
1317 p_msg_text => 'Procedure returning with status of '||x_return_status);
1318 END IF;
1319 IF G_LOG_LEVEL_PROCEDURE >= g_log_current_level THEN
1320 FEM_ENGINES_PKG.TECH_MESSAGE(
1321 p_severity => G_LOG_LEVEL_PROCEDURE,
1322 p_module => C_MODULE,
1323 p_msg_text => 'End Procedure');
1324 END IF;
1325
1326 EXCEPTION
1327 WHEN others THEN
1328 x_return_status := G_RET_STS_UNEXP_ERROR;
1329
1330 END Set_MP_Condition;
1331
1332
1333 --
1334 -- Procedure
1335 -- Prepare_Dynamic_Sql
1336 -- Purpose
1337 -- Sets the following dynamic sql:
1338 -- * SQL to copy slice from interface table to interim table
1339 -- * SQL to update the interim error_code for dimension validation
1340 -- errors
1341 -- * SQL to insert/replace validated rows into the target table
1342 -- History
1343 -- 09-07-04 GCHENG Created
1344 -- 02-26-06 HKANIVEN Added Replacement mode support
1345 -- 04-18-06 HKANIVEN Bug 5114554
1346 -- 06-08-07 RFLIPPO bug#6034150
1347 -- Arguments
1348 -- p_object_id : Object ID that identifies the rule being executed.
1349 -- p_request_id : Concurrent request ID of the Main process
1350 -- p_ledger_id : Ledger to load data for
1351 -- p_cal_period_id : Period to load data for
1352 -- p_dataset_code : Dataset to load data for
1353 -- p_source_system_code : Source system to load data for
1354 -- p_interface_table_name : Source data interface table name
1355 -- p_table_name : Source data target table name
1356 -- p_condition : Where clause for the interface table
1357 -- x_insert_interim_sql : SQL to copy slice from interface table
1358 -- to interim table
1359 -- x_update_interim_error_sql : SQL to update the interim error_code for
1360 -- dimension validation errors
1361 -- x_insert_target_sql : SQL to insert/replace validated rows into the
1362 -- target table
1363 -- x_return_status : Return status
1364 -- (look at FND_API for the possible statuses)
1365 -- Notes
1366 --
1367 PROCEDURE Prepare_Dynamic_Sql (
1368 p_object_id IN NUMBER,
1369 p_exec_mode IN VARCHAR2,
1370 p_request_id IN NUMBER,
1371 p_ledger_id IN NUMBER,
1372 p_cal_period_id IN NUMBER,
1373 p_dataset_code IN NUMBER,
1374 p_source_system_code IN NUMBER,
1375 p_interface_table_name IN VARCHAR2,
1376 p_target_table_name IN VARCHAR2,
1377 p_condition IN VARCHAR2,
1378 x_insert_interim_sql OUT NOCOPY VARCHAR2,
1379 x_update_interim_error_sql OUT NOCOPY VARCHAR2,
1380 x_insert_target_sql OUT NOCOPY VARCHAR2,
1381 x_return_status OUT NOCOPY VARCHAR2
1382 ) IS
1383 C_MODULE CONSTANT FND_LOG_MESSAGES.module%TYPE :=
1384 'fem.plsql.fem_source_data_loader_pkg.prepare_dynamic_sql';
1385
1386 TYPE nondim_col_tbl_type IS TABLE OF FEM_TAB_COLUMNS_V.column_name%TYPE;
1387 l_nondim_target_col_tbl nondim_col_tbl_type;
1388 l_nondim_int_col_tbl nondim_col_tbl_type;
1389 l_num_nondims NUMBER;
1390
1391 l_dummy1_sql VARCHAR2(18000);
1392 l_dummy2_sql VARCHAR2(18000);
1393
1394 -- Start of Replacement enhancement
1395
1396 l_merge_stmt_part1 VARCHAR2(18000);
1397 l_merge_stmt_part2 VARCHAR2(18000);
1398 l_merge_stmt_part3 VARCHAR2(18000);
1399 l_merge_stmt_part4 VARCHAR2(18000);
1400 l_merge_stmt_part5 VARCHAR2(18000);
1401 l_merge_stmt VARCHAR2(32000);
1402 l_dummy3_sql VARCHAR2(18000);
1403 l_dummy4_sql VARCHAR2(18000);
1404 l_dummy5_sql VARCHAR2(18000);
1405 l_dummy6_sql VARCHAR2(3500);
1406 l_found NUMBER;
1407 str_length NUMBER;
1408 v_count NUMBER;
1409
1410 -- End of Replacement enhancement
1411
1412 l_return_status VARCHAR2(1);
1413 l_msg_count NUMBER;
1414 l_msg_data VARCHAR2(4000);
1415 l_db_tab_name ALL_TABLES.table_name%TYPE;
1416 l_tab_owner ALL_TABLES.owner%TYPE;
1417
1418 BEGIN
1419 IF G_LOG_LEVEL_PROCEDURE >= g_log_current_level THEN
1420 FEM_ENGINES_PKG.TECH_MESSAGE(
1421 p_severity => G_LOG_LEVEL_PROCEDURE,
1422 p_module => C_MODULE,
1423 p_msg_text => 'Begin Procedure');
1424 END IF;
1425
1426 -- Populate g_xdim_info_tbl and g_num_dims
1427 Populate_xDim_Info_Tbl(
1428 p_table_name => p_target_table_name,
1429 p_ledger_id => p_ledger_id,
1430 x_return_status => x_return_status);
1431
1432 IF x_return_status <> G_RET_STS_SUCCESS THEN
1433 RAISE e_unexp_error;
1434 END IF;
1435
1436 --
1437 -- Build SQL to insert to interim table
1438 -- and SQL to update error_code in the interim table
1439 --
1440 --
1441
1442 -- for x_insert_interim_sql
1443 l_dummy1_sql :=
1444 'INSERT INTO fem_source_data_interim_gt (INTERFACE_ROWID';
1445 l_dummy2_sql :=
1446 'SELECT rowid';
1447
1448 -- for x_update_interim_error_sql
1449 x_update_interim_error_sql :=
1450 'UPDATE fem_source_data_interim_gt g SET g.error_code = '
1451 ||'''FEM_SD_LDR_INV_DIM_MEMBER'' WHERE EXISTS'
1452 ||'(SELECT null FROM '||p_interface_table_name||' i'
1453 ||' WHERE i.rowid = g.interface_rowid AND ( ';
1454
1455 FOR i IN 1..g_num_dims LOOP
1456 -- for x_insert_interim_sql
1457 l_dummy1_sql := l_dummy1_sql||',DIM'||to_char(i);
1458 l_dummy2_sql := l_dummy2_sql||','''||G_NULL_VALUE||'''';
1459 -- for x_update_interim_error_sql
1460 IF i>1 THEN
1461 x_update_interim_error_sql := x_update_interim_error_sql||' OR ';
1462 END IF;
1463 x_update_interim_error_sql := x_update_interim_error_sql
1464 ||'(i.'||g_xdim_info_tbl(i).int_disp_code_col
1465 ||' IS NOT NULL AND g.DIM'||to_char(i)||' IS NULL)';
1466 END LOOP;
1467 -- for x_insert_interim_sql
1468 l_dummy1_sql := l_dummy1_sql||') ';
1469 l_dummy2_sql := l_dummy2_sql||' FROM '||p_interface_table_name
1470 ||' WHERE '||p_condition;
1471 x_insert_interim_sql := l_dummy1_sql || l_dummy2_sql;
1472
1473 -- for x_update_interim_error_sql
1474 x_update_interim_error_sql := x_update_interim_error_sql||' ) )';
1475
1476
1477 IF G_LOG_LEVEL_STATEMENT >= g_log_current_level THEN
1478 FEM_ENGINES_PKG.Tech_Message
1479 (p_severity => G_LOG_LEVEL_STATEMENT,
1480 p_module => C_MODULE,
1481 p_msg_text => 'SQL to copy data from interface to interim is:');
1482 FEM_ENGINES_PKG.Tech_Message
1483 (p_severity => G_LOG_LEVEL_STATEMENT,
1484 p_module => C_MODULE,
1485 p_msg_text => l_dummy1_sql);
1486 FEM_ENGINES_PKG.Tech_Message
1487 (p_severity => G_LOG_LEVEL_STATEMENT,
1488 p_module => C_MODULE,
1489 p_msg_text => l_dummy2_sql);
1490 FEM_ENGINES_PKG.Tech_Message
1491 (p_severity => G_LOG_LEVEL_STATEMENT,
1492 p_module => C_MODULE,
1493 p_msg_text => 'SQL to set validation errors in the Interim Table is '
1494 ||x_update_interim_error_sql);
1495 END IF;
1496
1497
1498 --
1499 -- Lookup the non-dimension column names
1500 --
1501 -- 06/08/2007 RFLIPPO bug#6034150 exclude cols that don't have
1502 -- an interface col mapping. Rely on table class
1503 -- validation to ensure that all not null cols
1504 -- have an interface col mapping
1505 BEGIN
1506 SELECT tc.column_name, tc.interface_column_name
1507 BULK COLLECT INTO l_nondim_target_col_tbl, l_nondim_int_col_tbl
1508 FROM fem_tab_columns_v tc
1509 WHERE tc.table_name = p_target_table_name
1510 AND tc.fem_data_type_code <> 'DIMENSION'
1511 AND tc.interface_column_name is not null
1512 AND tc.column_name NOT IN ('CREATED_BY_REQUEST_ID','LAST_UPDATED_BY_REQUEST_ID');
1513 EXCEPTION
1514 WHEN no_data_found THEN
1515 l_num_nondims := 0;
1516 END;
1517
1518 l_num_nondims := SQL%ROWCOUNT;
1519
1520
1521
1522 IF G_LOG_LEVEL_STATEMENT >= g_log_current_level THEN
1523 FEM_ENGINES_PKG.Tech_Message
1524 (p_severity => G_LOG_LEVEL_STATEMENT,
1525 p_module => C_MODULE,
1526 p_msg_text => 'Number of non-dimenion columns is '
1527 ||to_char(l_num_nondims));
1528 END IF;
1529
1530 --
1531 -- Build SQL to insert to target table
1532 --
1533 x_insert_target_sql := null;
1534
1535 IF l_num_nondims = 0 AND g_num_dims = 0 THEN
1536 FEM_ENGINES_PKG.Tech_Message
1537 (p_severity => G_LOG_LEVEL_UNEXPECTED,
1538 p_module => C_MODULE,
1539 p_msg_text => 'This table has no columns registered in FEM_TAB_COLUMNS_V !');
1540 RAISE e_unexp_error;
1541 END IF;
1542
1543 l_dummy1_sql :=
1544 'INSERT INTO '||p_target_table_name
1545 ||' (CREATED_BY_OBJECT_ID,LAST_UPDATED_BY_OBJECT_ID,CREATED_BY_REQUEST_ID,LAST_UPDATED_BY_REQUEST_ID'
1546 ||',LEDGER_ID,CAL_PERIOD_ID,DATASET_CODE,SOURCE_SYSTEM_CODE';
1547 l_dummy2_sql :=
1548 'SELECT '||to_char(p_object_id)||','||to_char(p_object_id)||','
1549 ||to_char(p_request_id)||','||to_char(p_request_id)||','
1550 ||to_char(p_ledger_id)||','||to_char(p_cal_period_id)||','
1551 ||to_char(p_dataset_code)||','||to_char(p_source_system_code);
1552
1553
1554 IF g_num_dims > 0 THEN
1555 FOR i IN 1..g_num_dims LOOP
1556 IF g_xdim_info_tbl(i).target_col NOT IN ('LEDGER_ID','CAL_PERIOD_ID',
1557 'DATASET_CODE','SOURCE_SYSTEM_CODE') THEN
1558 l_dummy1_sql := l_dummy1_sql||','||g_xdim_info_tbl(i).target_col;
1559 IF g_xdim_info_tbl(i).target_col_data_type = 'NUMBER' THEN
1560 l_dummy2_sql := l_dummy2_sql||',to_number(g.DIM'||to_char(i)||')';
1561 ELSIF g_xdim_info_tbl(i).target_col_data_type = 'DATE' THEN
1562 l_dummy2_sql := l_dummy2_sql||',TO_DATE(g.DIM'||to_char(i)
1563 ||','''||G_DATETIME_FORMAT||''')';
1564 ELSE
1565 l_dummy2_sql := l_dummy2_sql||',g.DIM'||to_char(i);
1566 END IF;
1567 END IF;
1568 END LOOP;
1569 END IF;
1570
1571 IF l_num_nondims > 0 THEN
1572 FOR i IN 1..l_num_nondims LOOP
1573 l_dummy1_sql := l_dummy1_sql||','||l_nondim_target_col_tbl(i);
1574 l_dummy2_sql := l_dummy2_sql||',i.'||l_nondim_int_col_tbl(i);
1575 END LOOP;
1576 END IF;
1577
1578 l_dummy1_sql := l_dummy1_sql||') ';
1579 l_dummy2_sql := l_dummy2_sql||' FROM fem_source_data_interim_gt g, '
1580 ||p_interface_table_name||' i'
1581 ||' WHERE i.rowid = g.interface_rowid'
1582 ||' AND g.error_code IS NULL';
1583
1584 x_insert_target_sql := l_dummy1_sql || l_dummy2_sql;
1585
1586 IF G_LOG_LEVEL_STATEMENT >= g_log_current_level THEN
1587 FEM_ENGINES_PKG.Tech_Message
1588 (p_severity => G_LOG_LEVEL_STATEMENT,
1589 p_module => C_MODULE,
1590 p_msg_text => 'SQL to insert to target is:');
1591 FEM_ENGINES_PKG.Tech_Message
1592 (p_severity => G_LOG_LEVEL_STATEMENT,
1593 p_module => C_MODULE,
1594 p_msg_text => l_dummy1_sql);
1595 FEM_ENGINES_PKG.Tech_Message
1596 (p_severity => G_LOG_LEVEL_STATEMENT,
1597 p_module => C_MODULE,
1598 p_msg_text => l_dummy2_sql);
1599 END IF;
1600
1601
1602 -- Start of Replacement enhancement
1603 -- Prepare Dynamic 'MERGE' Statement.
1604
1605 -- MERGE statement is divided into five parts
1606 -- Finally all of them will be combined together to form
1607 -- the complete MERGE statement
1608
1609 -- MERGE statement parts are as follows
1610 -- l_merge_stmt_part1 - MERGE clause
1611 -- l_merge_stmt_part2 - SELECT clause
1612 -- l_merge_stmt_part3 - ON clause
1613 -- l_merge_stmt_part4 - WHEN MATCHED THEN clause
1614 -- l_merge_stmt_part5 - WHEN NOT MATCHED THEN clause
1615
1616
1617 IF p_exec_mode = 'R' OR p_exec_mode = 'E' THEN
1618
1619 IF G_LOG_LEVEL_STATEMENT >= g_log_current_level THEN
1620 FEM_ENGINES_PKG.TECH_MESSAGE(
1621 p_severity => G_LOG_LEVEL_STATEMENT,
1622 p_module => C_MODULE,
1623 p_msg_text => 'Start of MERGE statement creation');
1624 END IF;
1625
1626 l_merge_stmt_part1 := 'MERGE INTO ' || p_target_table_name || ' D USING (' ;
1627
1628 l_merge_stmt_part2 := ' SELECT ' ;
1629
1630 l_dummy2_sql := l_merge_stmt_part2 ;
1631
1632 -- Get all the dimension values
1633 IF g_num_dims > 0 THEN
1634 FOR i IN 1..g_num_dims LOOP
1635 IF i = 1 THEN
1636 IF g_xdim_info_tbl(i).target_col_data_type = 'NUMBER' THEN
1637 l_dummy2_sql := l_dummy2_sql||' to_number(g.DIM'||to_char(i)||') DIM' || to_char(i);
1638 ELSIF g_xdim_info_tbl(i).target_col_data_type = 'DATE' THEN
1639 l_dummy2_sql := l_dummy2_sql||' TO_DATE(g.DIM'||to_char(i) ||','''||G_DATETIME_FORMAT||''') DIM'
1640 || to_char(i);
1641 ELSE
1642 l_dummy2_sql := l_dummy2_sql||' g.DIM'||to_char(i) || ' DIM' || to_char(i);
1643 END IF;
1644
1645 ELSE
1646 IF g_xdim_info_tbl(i).target_col_data_type = 'NUMBER' THEN
1647 l_dummy2_sql := l_dummy2_sql||', to_number(g.DIM'||to_char(i)||') DIM' || to_char(i);
1648 ELSIF g_xdim_info_tbl(i).target_col_data_type = 'DATE' THEN
1649 l_dummy2_sql := l_dummy2_sql||', TO_DATE(g.DIM'||to_char(i) ||','''||G_DATETIME_FORMAT||''') DIM'
1650 || to_char(i);
1651 ELSE
1652 l_dummy2_sql := l_dummy2_sql||', g.DIM'||to_char(i) || ' DIM' || to_char(i);
1653 END IF;
1654 END IF;
1655 END LOOP;
1656 END IF;
1657
1658 --
1659 -- Get processing key information
1660 --
1661
1662 -- Get interface table name and owner
1663 FEM_Database_Util_Pkg.Get_Table_Owner (
1664 x_return_status => l_return_status,
1665 x_msg_count => l_msg_count,
1666 x_msg_data => l_msg_data,
1667 p_syn_name => p_target_table_name,
1668 x_tab_name => l_db_tab_name,
1669 x_tab_owner => l_tab_owner);
1670
1671 IF l_return_status <> G_RET_STS_SUCCESS THEN
1672 IF l_msg_count > 0 THEN
1673 Get_Put_Messages (
1674 p_msg_count => l_msg_count,
1675 p_msg_data => l_msg_data);
1676 END IF;
1677
1678 IF FND_LOG.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1679 FEM_ENGINES_PKG.TECH_MESSAGE(
1680 p_severity => FND_LOG.level_statement,
1681 p_module => C_MODULE,
1682 p_msg_text => 'Call to FEM_Database_Util_Pkg.Get_Table_Owner failed');
1683 END IF;
1684 RAISE e_unexp_error;
1685 ELSE
1686 IF FND_LOG.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1687 FEM_ENGINES_PKG.TECH_MESSAGE(
1688 p_severity => FND_LOG.level_statement,
1689 p_module => C_MODULE,
1690 p_msg_text => 'l_db_tab_name = '||l_db_tab_name);
1691 END IF;
1692 IF FND_LOG.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1693 FEM_ENGINES_PKG.TECH_MESSAGE(
1694 p_severity => FND_LOG.level_statement,
1695 p_module => C_MODULE,
1696 p_msg_text => 'l_tab_owner = '||l_tab_owner);
1697 END IF;
1698 END IF;
1699
1700 BEGIN
1701 SELECT ftc.column_name, ftc.interface_column_name, atc.nullable
1702 BULK COLLECT INTO g_proc_keys_tbl
1703 FROM fem_tab_columns_b ftc, all_tab_columns atc, fem_tab_column_prop tcp
1704 WHERE atc.table_name = l_db_tab_name
1705 AND atc.owner = l_tab_owner
1706 AND ftc.table_name = p_target_table_name
1707 AND atc.column_name = ftc.column_name
1708 AND ftc.table_name = tcp.table_name
1709 AND ftc.column_name = tcp.column_name
1710 AND tcp.column_property_code = 'PROCESSING_KEY';
1711 EXCEPTION
1712 WHEN NO_DATA_FOUND THEN
1713 IF G_LOG_LEVEL_STATEMENT >= g_log_current_level THEN
1714 FEM_ENGINES_PKG.Tech_Message
1715 (p_severity => G_LOG_LEVEL_STATEMENT,
1716 p_module => C_MODULE,
1717 p_msg_text => 'Processing key columns not defined');
1718 END IF;
1719
1720 WHEN OTHERS THEN
1721 RAISE e_unexp_error;
1722 END;
1723
1724 g_proc_key_dim_num := g_proc_keys_tbl.count;
1725
1726 -- Get all the non dimension values
1727 IF l_num_nondims > 0 THEN
1728 FOR i IN 1..l_num_nondims
1729 LOOP
1730 -- Bug 6040133 (FP:6019542): Since the processing key columns are not
1731 -- being added in the select clause in the next section, we should not
1732 -- exclude those non-dim PK columns from being added to the select
1733 -- clause here. Commenting out this code...
1734 --l_found := 0;
1735
1736 --FOR v IN 1..g_proc_key_dim_num
1737 --LOOP
1738 -- IF g_proc_keys_tbl(v).interface_col = l_nondim_int_col_tbl(i) THEN
1739 -- l_found := 1;
1740 -- EXIT;
1741 -- END IF;
1742 --END LOOP;
1743
1744 --IF l_found <> 1 THEN
1745 -- l_dummy2_sql := l_dummy2_sql||',i.'||l_nondim_int_col_tbl(i);
1746 -- l_found := 0;
1747 --END IF;
1748
1749 l_dummy2_sql := l_dummy2_sql||',i.'||l_nondim_int_col_tbl(i);
1750
1751 END LOOP;
1752 END IF;
1753
1754 -- Bug 6040133 (FP:6019542): Dimension columns part of the processing key
1755 -- are not needed in the SELECT clause because they are not being used
1756 -- in the MERGE stmt. Commenting out this code...
1757 --
1758 -- Append the processing key columns to the SELECT clause
1759 --FOR v IN 1..g_proc_key_dim_num
1760 --LOOP
1761 -- IF g_proc_keys_tbl(v).interface_col IS NOT NULL THEN
1762 -- l_dummy2_sql := l_dummy2_sql||', i.'||g_proc_keys_tbl(v).interface_col;
1763 -- END IF;
1764 --END LOOP;
1765
1766 l_dummy2_sql := l_dummy2_sql||' FROM fem_source_data_interim_gt g, '
1767 || p_interface_table_name||' i '
1768 ||' WHERE i.rowid = g.interface_rowid '
1769 ||' AND g.error_code IS NULL ';
1770
1771 l_merge_stmt_part2 := l_dummy2_sql || ' ) T ';
1772
1773 l_merge_stmt_part3 := ' ON ( ';
1774
1775 l_dummy3_sql := l_merge_stmt_part3;
1776
1777 -- Bug 5114554 hkaniven start
1778 -- Append the processing keys to the ON clause
1779 -- Append the dimension columns first
1780
1781 -- Bug 6040133 (FP:6019542): Should only add the 'IS NULL' check if the
1782 -- target column is nullable. Otherwise, we cannot take advantage of the
1783 -- unique index in the target table.
1784 FOR v IN 1..g_proc_key_dim_num
1785 LOOP
1786
1787 FOR i IN 1..g_num_dims
1788 LOOP
1789 IF g_proc_keys_tbl(v).target_col = g_xdim_info_tbl(i).target_col THEN
1790 IF l_merge_stmt_part3 <> l_dummy3_sql THEN
1791 l_merge_stmt_part3 := l_merge_stmt_part3 || ' AND';
1792 END IF;
1793
1794 IF g_proc_keys_tbl(v).target_nullable = 'Y' THEN
1795 l_merge_stmt_part3 := l_merge_stmt_part3
1796 || ' ((D.' || g_proc_keys_tbl(v).target_col || ' IS NULL'
1797 || ' AND T.DIM' || to_char(i) || ' IS NULL) OR ' || '(';
1798 END IF;
1799
1800 l_merge_stmt_part3 := l_merge_stmt_part3
1801 || ' D.' || g_proc_keys_tbl(v).target_col
1802 || ' = ' || ' T.DIM' || to_char(i);
1803
1804 IF g_proc_keys_tbl(v).target_nullable = 'Y' THEN
1805 l_merge_stmt_part3 := l_merge_stmt_part3 || '))';
1806 END IF;
1807 END IF;
1808 END LOOP;
1809 END LOOP;
1810
1811
1812 -- Append columns CAL_PERIOD_ID, LEDGER_ID,
1813 -- SOURCE_SYSTEM_CODE, DATASET_CODE to the ON Clause
1814
1815 FOR v IN 1..g_proc_key_dim_num
1816 LOOP
1817 IF g_proc_keys_tbl(v).target_col = 'CAL_PERIOD_ID' THEN
1818 IF l_merge_stmt_part3 = l_dummy3_sql THEN
1819 l_merge_stmt_part3 := l_merge_stmt_part3 || ' D.' || g_proc_keys_tbl(v).target_col
1820 || ' = ' || p_cal_period_id ;
1821 ELSE
1822 l_merge_stmt_part3 := l_merge_stmt_part3 || ' AND D.' || g_proc_keys_tbl(v).target_col
1823 || ' = ' || p_cal_period_id ;
1824 END IF;
1825 ELSIF g_proc_keys_tbl(v).target_col = 'LEDGER_ID' THEN
1826 IF l_merge_stmt_part3 = l_dummy3_sql THEN
1827 l_merge_stmt_part3 := l_merge_stmt_part3 || ' D.' || g_proc_keys_tbl(v).target_col
1828 || ' = ' || p_ledger_id ;
1829 ELSE
1830 l_merge_stmt_part3 := l_merge_stmt_part3 || ' AND D.' || g_proc_keys_tbl(v).target_col
1831 || ' = ' || p_ledger_id ;
1832 END IF;
1833 ELSIF g_proc_keys_tbl(v).target_col = 'SOURCE_SYSTEM_CODE' THEN
1834 IF l_merge_stmt_part3 = l_dummy3_sql THEN
1835 l_merge_stmt_part3 := l_merge_stmt_part3 || ' D.' || g_proc_keys_tbl(v).target_col
1836 || ' = ' || p_source_system_code ;
1837 ELSE
1838 l_merge_stmt_part3 := l_merge_stmt_part3 || ' AND D.' || g_proc_keys_tbl(v).target_col
1839 || ' = ' || p_source_system_code ;
1840 END IF;
1841 ELSIF g_proc_keys_tbl(v).target_col = 'DATASET_CODE' THEN
1842 IF l_merge_stmt_part3 = l_dummy3_sql THEN
1843 l_merge_stmt_part3 := l_merge_stmt_part3 || ' D.' || g_proc_keys_tbl(v).target_col
1844 || ' = ' || p_dataset_code ;
1845 ELSE
1846 l_merge_stmt_part3 := l_merge_stmt_part3 || ' AND D.' || g_proc_keys_tbl(v).target_col
1847 || ' = ' || p_dataset_code ;
1848 END IF;
1849 END IF;
1850 END LOOP;
1851
1852
1853 -- Append non-dimension columns processing keys to the ON Clause
1854 FOR v IN 1..g_proc_key_dim_num
1855 LOOP
1856 FOR i IN 1..l_num_nondims
1857 LOOP
1858 IF g_proc_keys_tbl(v).target_col = l_nondim_target_col_tbl(i) THEN
1859 IF l_merge_stmt_part3 <> l_dummy3_sql THEN
1860 l_merge_stmt_part3 := l_merge_stmt_part3 || ' AND';
1861 END IF;
1862
1863 IF g_proc_keys_tbl(v).target_nullable = 'Y' THEN
1864 l_merge_stmt_part3 := l_merge_stmt_part3
1865 || ' (( D.' || l_nondim_target_col_tbl(i) || ' IS NULL'
1866 || ' AND T.' || l_nondim_target_col_tbl(i) || ' IS NULL)'
1867 || ' OR ' || '(';
1868 END IF;
1869
1870 l_merge_stmt_part3 := l_merge_stmt_part3
1871 || ' D.' || l_nondim_target_col_tbl(i)
1872 || ' = ' || ' T.' || l_nondim_target_col_tbl(i);
1873
1874 IF g_proc_keys_tbl(v).target_nullable = 'Y' THEN
1875 l_merge_stmt_part3 := l_merge_stmt_part3 || '))';
1876 END IF;
1877 END IF;
1878 END LOOP;
1879 END LOOP;
1880
1881 -- Bug 5114554 hkaniven end
1882
1883 l_merge_stmt_part3 := l_merge_stmt_part3 || ' ) ';
1884
1885 l_merge_stmt_part4 := ' WHEN MATCHED THEN UPDATE SET ' ;
1886
1887 l_dummy4_sql := l_merge_stmt_part4;
1888
1889 --
1890 -- Creating the UPDATE clause
1891 --
1892
1893 -- Bug 5897807 (FP:5871562): Include the UNDO WHO first so we do not need to
1894 -- worry about whether we need to add a "," before each subsequence column
1895 -- being added to the update list - since "," will always be required
1896 -- going forward.
1897
1898 -- Append the columns LAST_UPDATED_BY_OBJECT_ID, LAST_UPDATED_BY_REQUEST_ID,
1899 -- LEDGER_ID, CAL_PERIOD_ID, DATASET_CODE, SOURCE_SYSTEM_CODE
1900 -- if they dont belong to the processing key columns.
1901 -- The plsql table doesnt contain these values since they need a different
1902 -- way of handling.
1903 -- So all these columns if not included in the processing key combination
1904 -- should get updated.
1905
1906 l_dummy4_sql := l_dummy4_sql || 'D.LAST_UPDATED_BY_OBJECT_ID = ' || TO_CHAR(p_object_id);
1907 l_dummy4_sql := l_dummy4_sql || ', D.LAST_UPDATED_BY_REQUEST_ID = ' || TO_CHAR(p_request_id);
1908
1909 IF l_num_nondims > 0 THEN
1910 FOR i IN 1..l_num_nondims LOOP
1911 l_found := 0;
1912 FOR v IN 1..g_proc_key_dim_num
1913 LOOP
1914 IF g_proc_keys_tbl(v).target_col = l_nondim_target_col_tbl(i) THEN
1915 l_found := 1;
1916 EXIT;
1917 END IF;
1918 END LOOP;
1919
1920 IF l_found <> 1 THEN
1921 l_dummy4_sql := l_dummy4_sql || ', D.' || l_nondim_target_col_tbl(i)
1922 || ' = T.' || l_nondim_int_col_tbl(i);
1923 l_found := 0;
1924 END IF;
1925 END LOOP;
1926 END IF;
1927
1928 l_found := 0;
1929 FOR v IN 1..g_proc_key_dim_num
1930 LOOP
1931 IF g_proc_keys_tbl(v).target_col = 'LEDGER_ID' THEN
1932 l_found := 1;
1933 EXIT;
1934 END IF;
1935 END LOOP;
1936
1937 IF l_found <> 1 THEN
1938 l_dummy4_sql := l_dummy4_sql || ', D.LEDGER_ID = ' || TO_CHAR(p_ledger_id);
1939 l_found := 0;
1940 END IF;
1941
1942
1943 FOR v IN 1..g_proc_key_dim_num
1944 LOOP
1945 IF g_proc_keys_tbl(v).target_col = 'CAL_PERIOD_ID' THEN
1946 l_found := 1;
1947 EXIT;
1948 END IF;
1949 END LOOP;
1950
1951 IF l_found <> 1 THEN
1952 l_dummy4_sql := l_dummy4_sql || ', D.CAL_PERIOD_ID = ' || TO_CHAR(p_cal_period_id);
1953 l_found := 0;
1954 END IF;
1955
1956 FOR v IN 1..g_proc_key_dim_num
1957 LOOP
1958 IF g_proc_keys_tbl(v).target_col = 'DATASET_CODE' THEN
1959 l_found := 1;
1960 EXIT;
1961 END IF;
1962 END LOOP;
1963
1964 IF l_found <> 1 THEN
1965 l_dummy4_sql := l_dummy4_sql || ', D.DATASET_CODE = ' || TO_CHAR(p_dataset_code);
1966 l_found := 0;
1967 END IF;
1968
1969 FOR v IN 1..g_proc_key_dim_num
1970 LOOP
1971 IF g_proc_keys_tbl(v).target_col = 'SOURCE_SYSTEM_CODE' THEN
1972 l_found := 1;
1973 EXIT;
1974 END IF;
1975 END LOOP;
1976
1977 IF l_found <> 1 THEN
1978 l_dummy4_sql := l_dummy4_sql || ', D.SOURCE_SYSTEM_CODE = ' || TO_CHAR(p_source_system_code);
1979 l_found := 0;
1980 END IF;
1981
1982
1983 -- Append the dimension columns
1984 IF g_num_dims > 0 THEN
1985 FOR i IN 1..g_num_dims LOOP
1986 l_found := 0;
1987 FOR v IN 1..g_proc_key_dim_num
1988 LOOP
1989 IF g_proc_keys_tbl(v).target_col = g_xdim_info_tbl(i).target_col THEN
1990 l_found := 1;
1991 EXIT;
1992 END IF;
1993 END LOOP;
1994
1995 IF l_found <> 1 THEN
1996 l_dummy4_sql := l_dummy4_sql || ', D.' || g_xdim_info_tbl(i).target_col
1997 || ' = T.DIM' || to_char(i);
1998 END IF;
1999 END LOOP;
2000 END IF;
2001
2002 l_merge_stmt_part4 := l_dummy4_sql ;
2003
2004 l_dummy6_sql := ' FROM fem_source_data_interim_gt g, '
2005 ||p_interface_table_name||' i'
2006 ||' WHERE i.rowid = g.interface_rowid'
2007 ||' AND g.error_code IS NULL';
2008
2009 -- Modify the INSERT statement created to suit the MERGE statement
2010 l_dummy5_sql := 'INTO ' || p_target_table_name ;
2011 l_dummy5_sql := REPLACE (x_insert_target_sql, l_dummy5_sql);
2012 l_dummy5_sql := REPLACE ( l_dummy5_sql, 'SELECT', 'VALUES (');
2013 l_dummy5_sql := REPLACE (l_dummy5_sql, l_dummy6_sql);
2014
2015 l_dummy5_sql := REPLACE (l_dummy5_sql, 'g.', 'T.');
2016 l_dummy5_sql := REPLACE (l_dummy5_sql, 'i.', 'T.');
2017
2018 l_merge_stmt_part5 := ' WHEN NOT MATCHED THEN ' || l_dummy5_sql || ')' ;
2019
2020 l_merge_stmt := l_merge_stmt_part1 || l_merge_stmt_part2 || l_merge_stmt_part3 ||
2021 l_merge_stmt_part4 || l_merge_stmt_part5;
2022
2023 -- Assigning the value of 'MERGE' statement to the same variable which holds 'INSERT' statement
2024 -- since this is 'Replacement' mode or 'Error Reprocessing' mode.
2025 x_insert_target_sql := l_merge_stmt;
2026
2027
2028 IF G_LOG_LEVEL_STATEMENT >= g_log_current_level THEN
2029 FEM_ENGINES_PKG.TECH_MESSAGE(
2030 p_severity => G_LOG_LEVEL_STATEMENT,
2031 p_module => C_MODULE,
2032 p_msg_text => 'Dynamic MERGE statement in parts');
2033
2034 FEM_ENGINES_PKG.TECH_MESSAGE(
2035 p_severity => G_LOG_LEVEL_STATEMENT,
2036 p_module => C_MODULE,
2037 p_msg_text => 'MERGE INTO clause - l_merge_stmt_part1 ');
2038
2039 str_length := 0;
2040
2041 LOOP
2042 EXIT WHEN str_length > LENGTH(l_merge_stmt_part1);
2043
2044 FEM_ENGINES_PKG.TECH_MESSAGE(
2045 p_severity => G_LOG_LEVEL_STATEMENT,
2046 p_module => C_MODULE,
2047 p_msg_text => SUBSTR(l_merge_stmt_part1,str_length+1,2000));
2048
2049 str_length := str_length + 2000;
2050 END LOOP;
2051
2052 FEM_ENGINES_PKG.TECH_MESSAGE(
2053 p_severity => G_LOG_LEVEL_STATEMENT,
2054 p_module => C_MODULE,
2055 p_msg_text => 'SELECT clause - l_merge_stmt_part2 ');
2056
2057 str_length := 0;
2058
2059 LOOP
2060 EXIT WHEN str_length > LENGTH(l_merge_stmt_part2);
2061
2062 FEM_ENGINES_PKG.TECH_MESSAGE(
2063 p_severity => G_LOG_LEVEL_STATEMENT,
2064 p_module => C_MODULE,
2065 p_msg_text => SUBSTR(l_merge_stmt_part2,str_length+1,2000));
2066
2067 str_length := str_length + 2000;
2068 END LOOP;
2069
2070 FEM_ENGINES_PKG.TECH_MESSAGE(
2071 p_severity => G_LOG_LEVEL_STATEMENT,
2072 p_module => C_MODULE,
2073 p_msg_text => 'ON clause - l_merge_stmt_part3 ');
2074
2075 str_length := 0;
2076
2077 LOOP
2078 EXIT WHEN str_length > LENGTH(l_merge_stmt_part3);
2079
2080 FEM_ENGINES_PKG.TECH_MESSAGE(
2081 p_severity => G_LOG_LEVEL_STATEMENT,
2082 p_module => C_MODULE,
2083 p_msg_text => SUBSTR(l_merge_stmt_part3,str_length+1,2000));
2084
2085 str_length := str_length + 2000;
2086 END LOOP;
2087
2088 FEM_ENGINES_PKG.TECH_MESSAGE(
2089 p_severity => G_LOG_LEVEL_STATEMENT,
2090 p_module => C_MODULE,
2091 p_msg_text => 'WHEN MATCHED THEN clause - l_merge_stmt_part4 ');
2092
2093 str_length := 0;
2094
2095 LOOP
2096 EXIT WHEN str_length > LENGTH(l_merge_stmt_part4);
2097
2098 FEM_ENGINES_PKG.TECH_MESSAGE(
2099 p_severity => G_LOG_LEVEL_STATEMENT,
2100 p_module => C_MODULE,
2101 p_msg_text => SUBSTR(l_merge_stmt_part4,str_length+1,2000));
2102
2103 str_length := str_length + 2000;
2104 END LOOP;
2105
2106 FEM_ENGINES_PKG.TECH_MESSAGE(
2107 p_severity => G_LOG_LEVEL_STATEMENT,
2108 p_module => C_MODULE,
2109 p_msg_text => 'WHEN NOT MATCHED THEN clause - l_merge_stmt_part5 ' );
2110
2111 str_length := 0;
2112
2113 LOOP
2114 EXIT WHEN str_length > LENGTH(l_merge_stmt_part5);
2115
2116 FEM_ENGINES_PKG.TECH_MESSAGE(
2117 p_severity => G_LOG_LEVEL_STATEMENT,
2118 p_module => C_MODULE,
2119 p_msg_text => SUBSTR(l_merge_stmt_part5,str_length+1,2000));
2120
2121 str_length := str_length + 2000;
2122 END LOOP;
2123
2124 FEM_ENGINES_PKG.TECH_MESSAGE(
2125 p_severity => G_LOG_LEVEL_STATEMENT,
2126 p_module => C_MODULE,
2127 p_msg_text => 'Complete MERGE statement - l_merge_stmt');
2128
2129 str_length := 0;
2130
2131 LOOP
2132 EXIT WHEN str_length > LENGTH(l_merge_stmt);
2133
2134 FEM_ENGINES_PKG.TECH_MESSAGE(
2135 p_severity => G_LOG_LEVEL_STATEMENT,
2136 p_module => C_MODULE,
2137 p_msg_text => SUBSTR(l_merge_stmt,str_length+1,2000));
2138
2139 str_length := str_length + 2000;
2140 END LOOP;
2141
2142 END IF;
2143
2144 g_proc_keys_tbl.DELETE;
2145
2146 END IF;
2147
2148 -- End of Replacement enhancement
2149
2150 l_nondim_target_col_tbl.DELETE;
2151 l_nondim_int_col_tbl.DELETE;
2152
2153 x_return_status := G_RET_STS_SUCCESS;
2154
2155 IF G_LOG_LEVEL_STATEMENT >= g_log_current_level THEN
2156 FEM_ENGINES_PKG.TECH_MESSAGE(
2157 p_severity => G_LOG_LEVEL_STATEMENT,
2158 p_module => C_MODULE,
2159 p_msg_text => 'Procedure returning with status of '||x_return_status);
2160 END IF;
2161 IF G_LOG_LEVEL_PROCEDURE >= g_log_current_level THEN
2162 FEM_ENGINES_PKG.TECH_MESSAGE(
2163 p_severity => G_LOG_LEVEL_PROCEDURE,
2164 p_module => C_MODULE,
2165 p_msg_text => 'End Procedure');
2166 END IF;
2167
2168 EXCEPTION
2169 WHEN others THEN
2170 x_return_status := G_RET_STS_UNEXP_ERROR;
2171
2172 END Prepare_Dynamic_Sql;
2173
2174
2175 --
2176 -- Procedure
2177 -- Process_Data
2178 -- Purpose
2179 -- Performs dimension validation and dimension numeric ID lookup
2180 -- (for those dimensions that need it), and finally move the data
2181 -- from the interface table to the target table.
2182 --
2183 -- This module is called by the Multiprocessing (MP) framework
2184 -- "Master" module.
2185 -- History
2186 -- 09-07-04 GCHENG Created
2187 -- Arguments
2188 -- p_eng_sql : Required by MP. Not currently used.
2189 -- p_data_slice_predicate : MP data slice predicate
2190 -- p_process_number : Number assigned by MP Master to each subrequest.
2191 -- p_partition_code : Process Partition Code that is set in MP Options
2192 -- p_fetch_limit : Fetch limit for any bulk fetch operations
2193 -- p_request_id : Concurrent request ID of the Main process
2194 -- p_exec_mode : Execution mode, S (Snapshot)/R (Replacement)
2195 -- E (Error Reprocessing)
2196 -- p_table_name : Source data target table name
2197 -- p_interface_table_name : Source data interface table name
2198 -- p_ledger_id : Ledger to load data for
2199 -- p_insert_interim_sql : SQL to copy slice from interface table
2200 -- to interim table
2201 -- p_update_interim_error_sql : SQL to update the interim error_code for
2202 -- dimension validation errors
2203 -- p_insert_target_sql : SQL to update the interim error_code for
2204 -- dimension validation
2205 -- p_schema_name : Schema name of FEM_SOURCE_DATA_INTERIM_GT.
2206 -- Notes
2207 --
2208 PROCEDURE Process_Data (
2209 p_eng_sql IN VARCHAR2,
2210 p_data_slice_predicate IN VARCHAR2,
2211 p_process_number IN NUMBER,
2212 p_partition_code IN NUMBER,
2213 p_fetch_limit IN NUMBER,
2214 p_request_id IN VARCHAR2,
2215 p_exec_mode IN VARCHAR2,
2216 p_target_table_name IN VARCHAR2,
2217 p_interface_table_name IN VARCHAR2,
2218 p_object_id IN NUMBER,
2219 p_ledger_id IN VARCHAR2,
2220 p_cal_period_id IN NUMBER,
2221 p_dataset_code IN NUMBER,
2222 p_source_system_code IN NUMBER,
2223 p_schema_name IN VARCHAR2,
2224 p_condition IN VARCHAR2
2225 ) IS
2226 C_MODULE CONSTANT FND_LOG_MESSAGES.module%TYPE :=
2227 'fem.plsql.fem_source_data_loader_pkg.process_data';
2228
2229 l_slc_id NUMBER;
2230 l_slc_val1 VARCHAR2(240);
2231 l_slc_val2 VARCHAR2(240);
2232 l_slc_val3 VARCHAR2(240);
2233 l_slc_val4 VARCHAR2(240);
2234 l_num_vals NUMBER;
2235 l_part_name VARCHAR2(30);
2236
2237 l_slc_status NUMBER;
2238 l_slc_msg VARCHAR2(80);
2239 l_slc_num_errors_reprocessed NUMBER;
2240 l_slc_num_rows_loaded NUMBER;
2241 l_slc_num_rows_rejected NUMBER;
2242 l_slc_num_rows_copied NUMBER;
2243 l_dummy_num NUMBER;
2244
2245 l_insert_interim_sql VARCHAR2(30000);
2246 l_update_interim_error_sql VARCHAR2(30000);
2247 l_insert_target_sql VARCHAR2(30000);
2248 l_dynamic_sql VARCHAR2(30000);
2249 l_dummy1_sql VARCHAR2(2000);
2250 l_dummy2_sql VARCHAR2(2000);
2251 l_dummy3_sql VARCHAR2(8000);
2252 l_dim_grp_size NUMBER;
2253 l_dim_grp_count NUMBER;
2254 l_dup_index_error BOOLEAN;
2255 l_dummy_boolean BOOLEAN;
2256 l_request_status VARCHAR2(10);
2257 l_return_status VARCHAR2(2);
2258 BEGIN
2259 -- set value of debug log current level
2260 g_log_current_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
2261
2262 IF G_LOG_LEVEL_PROCEDURE >= g_log_current_level THEN
2263 FEM_ENGINES_PKG.TECH_MESSAGE(
2264 p_severity => G_LOG_LEVEL_PROCEDURE,
2265 p_module => C_MODULE,
2266 p_msg_text => 'Begin Procedure');
2267 END IF;
2268
2269 -- Init var
2270 l_request_status := 'NORMAL';
2271
2272 -- Prepares all the dynamic SQL to be used in this session.
2273 -- This used to be called from the Main procedure so that
2274 -- the three dynamic SQL statements are only built once, as
2275 -- opposed to for each MP subprocess. However, that required
2276 -- the SQL statements to be passed through MP.
2277 -- Unfortunately, MP has a 32K restriction on the param list
2278 -- that gets passed to the engine program
2279 Prepare_Dynamic_Sql (
2280 p_object_id => p_object_id,
2281 p_exec_mode => p_exec_mode,
2282 p_request_id => p_request_id,
2283 p_ledger_id => p_ledger_id,
2284 p_cal_period_id => p_cal_period_id,
2285 p_dataset_code => p_dataset_code,
2286 p_source_system_code => p_source_system_code,
2287 p_interface_table_name => p_interface_table_name,
2288 p_target_table_name => p_target_table_name,
2289 p_condition => p_condition,
2290 x_insert_interim_sql => l_insert_interim_sql,
2291 x_update_interim_error_sql => l_update_interim_error_sql,
2292 x_insert_target_sql => l_insert_target_sql,
2293 x_return_status => l_return_status);
2294
2295 IF l_return_status <> G_RET_STS_SUCCESS THEN
2296 IF G_LOG_LEVEL_STATEMENT >= g_log_current_level THEN
2297 FEM_ENGINES_PKG.TECH_MESSAGE(
2298 p_severity => G_LOG_LEVEL_STATEMENT,
2299 p_module => C_MODULE,
2300 p_msg_text => 'Call to prepare_dynamic_sql failed.');
2301 END IF;
2302 RAISE e_unexp_error;
2303 END IF;
2304
2305 IF p_data_slice_predicate IS NOT NULL THEN
2306 l_insert_interim_sql := l_insert_interim_sql||' AND '||p_data_slice_predicate;
2307 END IF;
2308
2309 IF G_LOG_LEVEL_STATEMENT >= g_log_current_level THEN
2310 FEM_ENGINES_PKG.TECH_MESSAGE(
2311 p_severity => G_LOG_LEVEL_STATEMENT,
2312 p_module => C_MODULE,
2313 p_msg_text => 'SQL to insert into interim is '||l_insert_interim_sql);
2314 END IF;
2315
2316 -- Keep looping until no more data slices are left to process
2317 LOOP
2318 -- Initialize variables
2319 l_dup_index_error := FALSE;
2320 l_slc_num_errors_reprocessed := 0;
2321 l_slc_num_rows_loaded := 0;
2322 l_slc_num_rows_rejected := 0;
2323 l_slc_num_rows_copied := 0;
2324 l_slc_id := to_number(null);
2325 l_slc_msg := null;
2326
2327 FEM_MULTI_PROC_PKG.Get_Data_Slice(
2328 x_slc_id => l_slc_id,
2329 x_slc_val1 => l_slc_val1,
2330 x_slc_val2 => l_slc_val2,
2331 x_slc_val3 => l_slc_val3,
2332 x_slc_val4 => l_slc_val4,
2333 x_num_vals => l_num_vals,
2334 x_part_name => l_part_name,
2335 p_req_id => p_request_id,
2336 p_proc_num => p_process_number);
2337
2338 EXIT WHEN (l_slc_id IS NULL);
2339
2340 IF G_LOG_LEVEL_STATEMENT >= g_log_current_level THEN
2341 FEM_ENGINES_PKG.TECH_MESSAGE(
2342 p_severity => G_LOG_LEVEL_STATEMENT,
2343 p_module => C_MODULE,
2344 p_msg_text => 'Begin processing slice id '||to_char(l_slc_id));
2345 FEM_ENGINES_PKG.TECH_MESSAGE(
2346 p_severity => G_LOG_LEVEL_STATEMENT,
2347 p_module => C_MODULE,
2348 p_msg_text => 'Number of binding values is '||to_char(l_num_vals));
2349 END IF;
2350
2351 -- Step 1:
2352 -- Copy rowid to interim table
2353 --
2354 IF (l_num_vals = 4) THEN
2355 EXECUTE IMMEDIATE l_insert_interim_sql
2356 USING l_slc_val1,l_slc_val2,l_slc_val3,l_slc_val4;
2357 ELSIF (l_num_vals = 3) THEN
2358 EXECUTE IMMEDIATE l_insert_interim_sql
2359 USING l_slc_val1,l_slc_val2,l_slc_val3;
2360 ELSIF (l_num_vals = 2) THEN
2361 EXECUTE IMMEDIATE l_insert_interim_sql
2362 USING l_slc_val1,l_slc_val2;
2363 ELSIF (l_num_vals = 1) THEN
2364 EXECUTE IMMEDIATE l_insert_interim_sql
2365 USING l_slc_val1;
2366 ELSE
2367 IF G_LOG_LEVEL_STATEMENT >= g_log_current_level THEN
2368 FEM_ENGINES_PKG.TECH_MESSAGE(
2369 p_severity => G_LOG_LEVEL_STATEMENT,
2370 p_module => C_MODULE,
2371 p_msg_text => 'Multiprocessing engine generated an unexpected number '
2372 ||'of binding values: '||to_char(l_num_vals));
2373 END IF;
2374 RAISE e_unexp_error;
2375 END IF;
2376
2377 l_slc_num_rows_copied := SQL%ROWCOUNT;
2378
2379 -- Commit to release any reserved rollback space.
2380 -- Three is no harm is commiting now since the only transaction done
2381 -- so far is copying data into the global temporary interim table.
2382 -- If an error occurs that ends this process prematurely, there are no
2383 -- negative side effects as all data in global temporary tables
2384 -- are session specific. If this process aborts, its session ends
2385 -- and all data is wiped out anyway.
2386 COMMIT;
2387
2388 IF G_LOG_LEVEL_STATEMENT >= g_log_current_level THEN
2389 FEM_ENGINES_PKG.TECH_MESSAGE(
2390 p_severity => G_LOG_LEVEL_STATEMENT,
2391 p_module => C_MODULE,
2392 p_msg_text => 'Number of rows copied to the interim table is '
2393 ||to_char(l_slc_num_rows_copied));
2394 END IF;
2395
2396 -- Steps 2, 3, 4:
2397 -- Perform dimension lookup and validiation.
2398 -- If there are no dimensions, skip these steps.
2399 --
2400 IF g_num_dims = 0 THEN
2401 IF G_LOG_LEVEL_STATEMENT >= g_log_current_level THEN
2402 FEM_ENGINES_PKG.TECH_MESSAGE(
2403 p_severity => G_LOG_LEVEL_STATEMENT,
2404 p_module => C_MODULE,
2405 p_msg_text => 'There are no dimensions. No dimension validation to perform.');
2406 END IF;
2407 ELSE
2408 -- Step 2:
2409 -- Perform dimension lookup
2410 l_dim_grp_size := to_number(FND_PROFILE.Value('FEM_LOADER_DIM_GRP_SIZE'));
2411 IF nvl(l_dim_grp_size,0) <= 0 THEN
2412 l_dim_grp_size := G_DEFAULT_DIM_GRP_SIZE;
2413 ELSIF nvl(l_dim_grp_size,0) > g_num_dims THEN
2414 l_dim_grp_size := g_num_dims;
2415 END IF;
2416
2417 IF G_LOG_LEVEL_STATEMENT >= g_log_current_level THEN
2418 FEM_ENGINES_PKG.TECH_MESSAGE(
2419 p_severity => G_LOG_LEVEL_STATEMENT,
2420 p_module => C_MODULE,
2421 p_msg_text => 'Dimension grouping size is '||l_dim_grp_size);
2422 END IF;
2423
2424 -- Initialize counter that keeps track of how many dimensions
2425 -- that have been grouped
2426 l_dim_grp_count := 1;
2427
2428 FOR dim_index IN 1..g_num_dims LOOP
2429 IF l_dim_grp_count = 1 THEN
2430 l_dynamic_sql := 'UPDATE fem_source_data_interim_gt g SET (';
2431 l_dummy1_sql := '(SELECT ';
2432 l_dummy2_sql := ' FROM '||p_interface_table_name||' i';
2433 l_dummy3_sql := ' WHERE i.rowid=g.interface_rowid';
2434 END IF;
2435
2436 -- UPDATE SET clause
2437 l_dynamic_sql := l_dynamic_sql||'g.DIM'||to_char(dim_index);
2438
2439 -- SELECT clause (dimension ID lookup)
2440 -- Explicitly convert the data type of the member col to
2441 -- that of the DIMx columns (VARCHAR2) where necessary.
2442 IF g_xdim_info_tbl(dim_index).target_col_data_type = 'NUMBER' THEN
2443 l_dummy1_sql := l_dummy1_sql||'to_char(d'||to_char(dim_index)||'.'
2444 ||g_xdim_info_tbl(dim_index).member_col||')';
2445 ELSIF g_xdim_info_tbl(dim_index).target_col_data_type = 'DATE' THEN
2446 l_dummy1_sql := l_dummy1_sql||'to_char(d'||to_char(dim_index)||'.'
2447 ||g_xdim_info_tbl(dim_index).member_col
2448 ||','''||G_DATETIME_FORMAT||''')';
2449 ELSE
2450 l_dummy1_sql := l_dummy1_sql||'d'||to_char(dim_index)||'.'
2451 ||g_xdim_info_tbl(dim_index).member_col;
2452 END IF;
2453
2454 -- FROM clause
2455 l_dummy2_sql := l_dummy2_sql||','
2456 ||g_xdim_info_tbl(dim_index).member_b_table_name||' d'||to_char(dim_index);
2457
2458 -- WHERE clause
2459 -- match display codes
2460 l_dummy3_sql := l_dummy3_sql||' AND d'||to_char(dim_index)||'.'
2461 ||g_xdim_info_tbl(dim_index).member_disp_code_col||'(+)'
2462 ||'=i.'||g_xdim_info_tbl(dim_index).int_disp_code_col;
2463
2464 -- make sure personal flag is N
2465 l_dummy3_sql := l_dummy3_sql
2466 ||' AND d'||to_char(dim_index)||'.'||'personal_flag(+)=''N''';
2467 -- if dimension has value set associated with it, make sure
2468 -- it matches with the value set tied to the global value set combo
2469 IF g_xdim_info_tbl(dim_index).vs_id IS NOT NULL THEN
2470 l_dummy3_sql := l_dummy3_sql||' AND d'||to_char(dim_index)||'.'
2471 ||'value_set_id(+)'
2472 ||'='||to_char(g_xdim_info_tbl(dim_index).vs_id);
2473 END IF;
2474
2475 -- Execute the update statement when number of dimenions reach
2476 -- dimension group size
2477 IF l_dim_grp_count = l_dim_grp_size OR dim_index = g_num_dims THEN
2478
2479 -- If number of dimensions to group is 1, set the error_code
2480 -- with the interface column name that failed validation.
2481 IF l_dim_grp_size = 1 THEN
2482 l_dynamic_sql := l_dynamic_sql||',g.error_code)='||l_dummy1_sql
2483 ||',decode(i.'
2484 ||g_xdim_info_tbl(dim_index).int_disp_code_col||',null,null,'
2485 ||'decode(d'||to_char(dim_index)||'.'
2486 ||g_xdim_info_tbl(dim_index).member_disp_code_col||',null,'
2487 ||''''||g_xdim_info_tbl(dim_index).int_disp_code_col||'''))'
2488 ||l_dummy2_sql||l_dummy3_sql||')'
2489 ||'WHERE g.error_code IS NULL';
2490
2491 -- Else, do not try to detect error as it involves O(n^2) decode
2492 -- statements.
2493 ELSE
2494 l_dynamic_sql := l_dynamic_sql||')='||l_dummy1_sql||l_dummy2_sql
2495 ||l_dummy3_sql||')';
2496 END IF;
2497
2498 IF G_LOG_LEVEL_STATEMENT >= g_log_current_level THEN
2499 FEM_ENGINES_PKG.TECH_MESSAGE(
2500 p_severity => G_LOG_LEVEL_STATEMENT,
2501 p_module => C_MODULE,
2502 p_msg_text => 'SQL to update interim errors is '||l_dynamic_sql);
2503 END IF;
2504
2505 EXECUTE IMMEDIATE l_dynamic_sql;
2506
2507 -- Reset the dimension group counter after update statement executes
2508 l_dim_grp_count := 1;
2509
2510 -- Commit to release any reserved rollback space.
2511 COMMIT;
2512 ELSE
2513 l_dim_grp_count := l_dim_grp_count+1;
2514 l_dynamic_sql := l_dynamic_sql||',';
2515 l_dummy1_sql := l_dummy1_sql||',';
2516 END IF;
2517 END LOOP;
2518
2519 -- Step 3:
2520 -- If dim group size <> 1.
2521 -- perform dimension validation.
2522 -- Mark those rows in interim tables that failed dimension validation.
2523 -- If dim group size = 1, the dimension validation was preformed
2524 -- at the same time of the dimension ID lookup - via decode stmts.
2525 IF l_dim_grp_size <> 1 THEN
2526 EXECUTE IMMEDIATE l_update_interim_error_sql;
2527 l_slc_num_rows_rejected := SQL%ROWCOUNT;
2528 -- Commit to release any reserved rollback space.
2529 COMMIT;
2530
2531 IF G_LOG_LEVEL_STATEMENT >= g_log_current_level THEN
2532 FEM_ENGINES_PKG.TECH_MESSAGE(
2533 p_severity => G_LOG_LEVEL_STATEMENT,
2534 p_module => C_MODULE,
2535 p_msg_text => 'Number of rows that failed dimension validation in interim is '
2536 ||to_char(l_slc_num_rows_rejected));
2537 END IF;
2538 END IF;
2539
2540
2541 -- Step 4
2542 -- Update the interface table to reflect any errors found during
2543 -- dimension validation.
2544 --
2545 -- If any rows failed validation,
2546 -- copy the error_code from the interim table over as status
2547 -- in the interface table.
2548 -- Currently, the only error_code being set in the interim table
2549 -- is 'FEM_SD_LDR_INV_DIM_MEMBER' so it is hard coded to that
2550 -- for performance reasons. Otherwise, the set status clause will need
2551 -- to be a subquery to the effects of:
2552 -- (SELECT g.error_code FROM fem_source_data_interim_gt g
2553 -- WHERE i.rowid = g.interface_rowid AND g.error_code IS NOT NULL)
2554
2555 -- If dim group size = 1, append the specific dimension column that
2556 -- failed validation to FEM_SD_LDR_INV_DIM_MEMBER in the status column.
2557 IF l_dim_grp_size = 1 THEN
2558 l_dynamic_sql := 'UPDATE '||p_interface_table_name||' i'
2559 ||' SET i.status='
2560 ||' (SELECT ''FEM_SD_LDR_INV_DIM_MEMBER: ''||t.error_code'
2561 ||' FROM fem_source_data_interim_gt t'
2562 ||' WHERE t.interface_rowid=i.rowid'
2563 ||' AND t.error_code IS NOT NULL)'
2564 ||' WHERE i.rowid IN'
2565 ||' (SELECT g.interface_rowid FROM fem_source_data_interim_gt g'
2566 ||' WHERE g.error_code IS NOT NULL)';
2567
2568 EXECUTE IMMEDIATE l_dynamic_sql;
2569 l_slc_num_rows_rejected := SQL%ROWCOUNT;
2570
2571 IF G_LOG_LEVEL_STATEMENT >= g_log_current_level THEN
2572 FEM_ENGINES_PKG.TECH_MESSAGE(
2573 p_severity => G_LOG_LEVEL_STATEMENT,
2574 p_module => C_MODULE,
2575 p_msg_text => 'Number of rows in interface table set with dimension validation error is '
2576 ||to_char(l_slc_num_rows_rejected));
2577 END IF;
2578
2579 -- If dim group size > 1, then just set the status with error code
2580 -- FEM_SD_LDR_INV_DIM_MEMBER - the user has to figure out which
2581 -- dimension column failed validation.
2582 ELSE
2583 IF l_slc_num_rows_rejected > 0 THEN
2584 l_dynamic_sql := 'UPDATE '||p_interface_table_name||' i'
2585 ||' SET i.status = ''FEM_SD_LDR_INV_DIM_MEMBER'''
2586 ||' WHERE i.rowid IN'
2587 ||' (SELECT g.interface_rowid FROM fem_source_data_interim_gt g'
2588 ||' WHERE g.error_code IS NOT NULL)';
2589
2590 EXECUTE IMMEDIATE l_dynamic_sql;
2591 l_dummy_num := SQL%ROWCOUNT;
2592
2593 IF G_LOG_LEVEL_STATEMENT >= g_log_current_level THEN
2594 FEM_ENGINES_PKG.TECH_MESSAGE(
2595 p_severity => G_LOG_LEVEL_STATEMENT,
2596 p_module => C_MODULE,
2597 p_msg_text => 'Number of rows in interface table set with dimension validation error is '
2598 ||to_char(l_dummy_num));
2599 END IF;
2600
2601 -- See if number of error rows updated in interface table is
2602 -- consistent with number of error rows updated in interim table.
2603 IF l_dummy_num <> l_slc_num_rows_rejected THEN
2604 l_slc_num_rows_rejected := l_dummy_num;
2605
2606 IF G_LOG_LEVEL_STATEMENT >= g_log_current_level THEN
2607 FEM_ENGINES_PKG.TECH_MESSAGE(
2608 p_severity => G_LOG_LEVEL_STATEMENT,
2609 p_module => C_MODULE,
2610 p_msg_text => 'Number of error rows updated in interface table is '
2611 ||' not the same as the number of error rows updated in the interim table.');
2612 END IF;
2613 RAISE e_unexp_error;
2614 END IF;
2615 END IF;
2616
2617 -- Commit to release any reserved rollback space.
2618 COMMIT;
2619 END IF;
2620 END IF;
2621
2622 -- Step 5
2623 -- Insert validated rows in the target table if there are
2624 -- actually validated rows to insert.
2625 IF l_slc_num_rows_rejected < l_slc_num_rows_copied THEN
2626 BEGIN
2627 EXECUTE IMMEDIATE l_insert_target_sql;
2628 l_slc_num_rows_loaded := SQL%ROWCOUNT;
2629
2630 IF G_LOG_LEVEL_STATEMENT >= g_log_current_level THEN
2631 FEM_ENGINES_PKG.TECH_MESSAGE(
2632 p_severity => G_LOG_LEVEL_STATEMENT,
2633 p_module => C_MODULE,
2634 p_msg_text => 'Number of rows inserted into the target table is '
2635 ||to_char(l_slc_num_rows_loaded));
2636 END IF;
2637 EXCEPTION
2638 WHEN dup_val_on_index THEN
2639 l_slc_msg := 'Duplicate rows exist in this slice.';
2640 l_slc_num_rows_loaded := 0;
2641 l_dup_index_error := TRUE;
2642 WHEN others THEN
2643 IF G_LOG_LEVEL_STATEMENT >= g_log_current_level THEN
2644 FEM_ENGINES_PKG.TECH_MESSAGE(
2645 p_severity => G_LOG_LEVEL_STATEMENT,
2646 p_module => C_MODULE,
2647 p_msg_text => 'Unexpected error occured when inserting into'
2648 ||' the target table.');
2649 END IF;
2650 RAISE;
2651 END;
2652 END IF;
2653
2654
2655 -- Step 6a
2656 -- If the insert to target table raised a DUP_VAL_ON_INDEX error,
2657 -- mark those rows with status of 'FEM_SD_LDR_DUPLICATE_ROW'.
2658 IF l_dup_index_error THEN
2659 l_dynamic_sql := 'UPDATE '||p_interface_table_name||' i'
2660 ||' SET i.status = ''FEM_SD_LDR_DUPLICATE_ROW'''
2661 ||' WHERE i.rowid IN'
2662 ||' (SELECT g.interface_rowid FROM fem_source_data_interim_gt g'
2663 ||' WHERE g.error_code IS NULL)';
2664
2665 EXECUTE IMMEDIATE l_dynamic_sql;
2666 -- Add to the number of rows rejected counter
2667 l_slc_num_rows_rejected := l_slc_num_rows_rejected + SQL%ROWCOUNT;
2668
2669 IF G_LOG_LEVEL_STATEMENT >= g_log_current_level THEN
2670 FEM_ENGINES_PKG.TECH_MESSAGE(
2671 p_severity => G_LOG_LEVEL_STATEMENT,
2672 p_module => C_MODULE,
2673 p_msg_text => 'Number of rows in interface set with duplicate error is '
2674 ||to_char(SQL%ROWCOUNT));
2675 END IF;
2676
2677 -- Step 6a
2678 -- If the insert to target table was successful,
2679 -- delete those inserted rows from the interface table.
2680 ELSIF l_slc_num_rows_rejected < l_slc_num_rows_copied THEN
2681 l_dynamic_sql := 'DELETE FROM '||p_interface_table_name||' i'
2682 ||' WHERE i.rowid IN (SELECT g.interface_rowid'
2683 ||' FROM fem_source_data_interim_gt g WHERE g.error_code IS NULL)';
2684 EXECUTE IMMEDIATE l_dynamic_sql;
2685 l_dummy_num := SQL%ROWCOUNT;
2686
2687 IF G_LOG_LEVEL_STATEMENT >= g_log_current_level THEN
2688 FEM_ENGINES_PKG.TECH_MESSAGE(
2689 p_severity => G_LOG_LEVEL_STATEMENT,
2690 p_module => C_MODULE,
2691 p_msg_text => 'Number of rows deleted from the interface table is '
2692 ||to_char(l_dummy_num));
2693 END IF;
2694
2695 -- See if number of rows deleted from the interface table is
2696 -- consistent with number of rows inserted into the target table.
2697 IF l_dummy_num <> l_slc_num_rows_loaded THEN
2698 l_slc_num_rows_loaded := l_dummy_num;
2699
2700 IF G_LOG_LEVEL_STATEMENT >= g_log_current_level THEN
2701 FEM_ENGINES_PKG.TECH_MESSAGE(
2702 p_severity => G_LOG_LEVEL_STATEMENT,
2703 p_module => C_MODULE,
2704 p_msg_text => 'The number of rows deleted from the interface table'
2705 ||' is not the same as the number of rows inserted into the target table.');
2706 END IF;
2707 RAISE e_unexp_error;
2708 END IF;
2709
2710 END IF;
2711
2712 -- If the number of rows rejected + loaded <> copied, raise unexp error
2713 IF (l_slc_num_rows_rejected+l_slc_num_rows_loaded)
2714 <> l_slc_num_rows_copied THEN
2715 IF G_LOG_LEVEL_STATEMENT >= g_log_current_level THEN
2716 FEM_ENGINES_PKG.TECH_MESSAGE(
2717 p_severity => G_LOG_LEVEL_STATEMENT,
2718 p_module => C_MODULE,
2719 p_msg_text => 'Num rows copied ('
2720 ||to_char(l_slc_num_rows_copied)
2721 ||') does not equal num rows rejected ('
2722 ||to_char(l_slc_num_rows_rejected)||') plus num rows loaded ('
2723 ||to_char(l_slc_num_rows_loaded)||').');
2724 END IF;
2725
2726 RAISE e_unexp_error;
2727 END IF;
2728
2729 IF p_exec_mode = 'S' THEN
2730 l_slc_num_errors_reprocessed := 0;
2731 ELSE
2732 l_slc_num_errors_reprocessed := l_slc_num_rows_loaded;
2733 END IF;
2734
2735 -- Need to make sure these two steps are in the same transaction:
2736 -- 5. Insert valid rows into the target table
2737 -- 6.
2738 -- a. Mark group of rows in interface table that contain at least one
2739 -- duplicate row.
2740 -- - or -
2741 -- b. Delete valid rows from the interface table.
2742 COMMIT;
2743
2744 IF G_LOG_LEVEL_STATEMENT >= g_log_current_level THEN
2745 FEM_ENGINES_PKG.TECH_MESSAGE(
2746 p_severity => G_LOG_LEVEL_STATEMENT,
2747 p_module => C_MODULE,
2748 p_msg_text => 'Slice process summary: '
2749 ||to_char(l_slc_num_errors_reprocessed)||' error rows reprocessed, '
2750 ||to_char(l_slc_num_rows_loaded)||' rows loaded, '
2751 ||to_char(l_slc_num_rows_rejected)||' rows rejected.');
2752 END IF;
2753
2754 IF l_slc_num_rows_rejected > 0 THEN
2755 -- set slice and request status as warning so next slice can be processed
2756 l_slc_status := 1;
2757 l_request_status := 'WARNING';
2758 ELSE
2759 l_slc_status := 0;
2760 END IF;
2761
2762 -- ------------------------------------------------------------
2763 -- Truncate FEM_SOURCE_DATA_INTERIM_GT for the next data slice
2764 -- ------------------------------------------------------------
2765
2766 IF G_LOG_LEVEL_STATEMENT >= g_log_current_level THEN
2767 FEM_ENGINES_PKG.TECH_MESSAGE(
2768 p_severity => G_LOG_LEVEL_STATEMENT,
2769 p_module => C_MODULE,
2770 p_msg_text =>
2771 'Truncating '||p_schema_name||'.FEM_SOURCE_DATA_INTERIM_GT for the next data slice.');
2772 END IF;
2773
2774 EXECUTE IMMEDIATE
2775 'TRUNCATE TABLE ' || p_schema_name || '.fem_source_data_interim_gt';
2776 COMMIT;
2777
2778 FEM_MULTI_PROC_PKG.Post_Data_Slice(
2779 p_req_id => p_request_id,
2780 p_slc_id => l_slc_id,
2781 p_status => l_slc_status,
2782 p_message => l_slc_msg,
2783 p_rows_processed => l_slc_num_errors_reprocessed,
2784 p_rows_loaded => l_slc_num_rows_loaded,
2785 p_rows_rejected => l_slc_num_rows_rejected);
2786
2787 IF G_LOG_LEVEL_STATEMENT >= g_log_current_level THEN
2788 FEM_ENGINES_PKG.TECH_MESSAGE(
2789 p_severity => G_LOG_LEVEL_STATEMENT,
2790 p_module => C_MODULE,
2791 p_msg_text => 'Finished processing slice id '||to_char(l_slc_id)
2792 ||' with status of '||l_slc_status);
2793 END IF;
2794 END LOOP;
2795
2796 -- set request status
2797 l_dummy_boolean := FND_CONCURRENT.Set_Completion_Status
2798 (status => l_request_status, message => NULL);
2799
2800 IF G_LOG_LEVEL_PROCEDURE >= g_log_current_level THEN
2801 FEM_ENGINES_PKG.TECH_MESSAGE(
2802 p_severity => G_LOG_LEVEL_PROCEDURE,
2803 p_module => C_MODULE,
2804 p_msg_text => 'End Procedure');
2805 END IF;
2806
2807 EXCEPTION
2808 WHEN others THEN
2809 ROLLBACK;
2810 l_slc_status := 2;
2811 IF l_slc_msg IS NULL THEN
2812 l_slc_msg := 'Unexpected error. Abort processing.';
2813 END IF;
2814
2815 FEM_MULTI_PROC_PKG.Post_Data_Slice(
2816 p_req_id => p_request_id,
2817 p_slc_id => l_slc_id,
2818 p_status => l_slc_status,
2819 p_message => l_slc_msg,
2820 p_rows_processed => l_slc_num_errors_reprocessed,
2821 p_rows_loaded => l_slc_num_rows_loaded,
2822 p_rows_rejected => l_slc_num_rows_rejected);
2823
2824 -- set request status
2825 l_dummy_boolean := FND_CONCURRENT.Set_Completion_Status
2826 (status => 'ERROR', message => NULL);
2827
2828 FEM_ENGINES_PKG.User_Message
2829 (p_app_name => 'FEM',
2830 p_msg_name => 'FEM_UNEXPECTED_ERROR',
2831 p_token1 => 'ERR_MSG',
2832 p_value1 => SQLERRM);
2833 FEM_ENGINES_PKG.Tech_Message
2834 (p_severity => G_LOG_LEVEL_UNEXPECTED,
2835 p_module => C_MODULE,
2836 p_app_name => 'FEM',
2837 p_msg_name => 'FEM_UNEXPECTED_ERROR',
2838 p_token1 => 'ERR_MSG',
2839 p_value1 => SQLERRM);
2840 END Process_Data;
2841
2842 --
2843 -- Procedure
2844 -- Post_Process
2845 -- Purpose
2846 -- Performs post-process execution logging. Certain logging
2847 -- operations are required only for successful completion or are
2848 -- done differently for success and failure, so a parameter is
2849 -- passed to indicate the mode.
2850 -- Upon successful completion, this module is called from the
2851 -- body of the Main module. Upon cancellation or fatal error
2852 -- it is called from the exception handler of the Main module.
2853 -- History
2854 -- 09-07-04 GCHENG Created
2855 -- Arguments
2856 -- p_object_id : Object ID that identifies the rule being executed.
2857 -- p_obj_def_id : Object Definition ID that identifies the rule
2858 -- version being executed.
2859 -- p_table_name : Source Data Table Name
2860 -- p_exec_mode : Execution mode, S (Snapshot)/R (Replacement)
2861 -- E (Error Reprocessing)
2862 -- p_ledger_id : Ledger to load data for
2863 -- p_cal_period_id : Period to load data for
2864 -- p_dataset_code : Dataset to load data for
2865 -- p_source_system_code : Source system to load data for
2866 -- p_request_id : Concurrent request ID
2867 -- p_user_id : User ID
2868 -- p_login_id : Login ID
2869 -- x_return_status : Return status
2870 -- (look at FND_API for the possible statuses)
2871 -- Notes
2872 --
2873 PROCEDURE Post_Process (
2874 p_object_id IN NUMBER,
2875 p_obj_def_id IN NUMBER,
2876 p_table_name IN VARCHAR2,
2877 p_exec_mode IN VARCHAR2,
2878 p_ledger_id IN NUMBER,
2879 p_cal_period_id IN NUMBER,
2880 p_dataset_code IN NUMBER,
2881 p_source_system_code IN NUMBER,
2882 p_exec_status IN VARCHAR2,
2883 p_request_id IN NUMBER,
2884 p_user_id IN NUMBER,
2885 p_login_id IN NUMBER,
2886 x_return_status OUT NOCOPY VARCHAR2
2887 ) IS
2888 C_MODULE CONSTANT FND_LOG_MESSAGES.module%TYPE :=
2889 'fem.plsql.fem_source_data_loader_pkg.post_process';
2890
2891 l_num_errors_reprocessed NUMBER;
2892 l_num_rows_loaded NUMBER;
2893 l_num_rows_rejected NUMBER;
2894 l_msg_count NUMBER;
2895 l_msg_data VARCHAR2(4000);
2896 l_return_status VARCHAR2(1);
2897 l_dummy_boolean BOOLEAN;
2898 BEGIN
2899 IF G_LOG_LEVEL_PROCEDURE >= g_log_current_level THEN
2900 FEM_ENGINES_PKG.TECH_MESSAGE(
2901 p_severity => G_LOG_LEVEL_PROCEDURE,
2902 p_module => C_MODULE,
2903 p_msg_text => 'Begin Procedure');
2904 END IF;
2905 -- Log parameters
2906 IF G_LOG_LEVEL_STATEMENT >= g_log_current_level THEN
2907 FEM_ENGINES_PKG.TECH_MESSAGE(
2908 p_severity => G_LOG_LEVEL_STATEMENT,
2909 p_module => C_MODULE,
2910 p_msg_text => 'Parameter: Execution Status is '||p_exec_status);
2911 END IF;
2912
2913 SELECT nvl(SUM(rows_processed),0), nvl(SUM(rows_loaded),0), nvl(SUM(rows_rejected),0)
2914 INTO l_num_errors_reprocessed, l_num_rows_loaded, l_num_rows_rejected
2915 FROM fem_mp_process_ctl_t
2916 WHERE req_id = p_request_id;
2917
2918 IF G_LOG_LEVEL_STATEMENT >= g_log_current_level THEN
2919 FEM_ENGINES_PKG.Tech_Message
2920 (p_severity => G_LOG_LEVEL_STATEMENT,
2921 p_module => C_MODULE,
2922 p_msg_text => to_char(l_num_rows_loaded)||' total rows loaded.');
2923 FEM_ENGINES_PKG.Tech_Message
2924 (p_severity => G_LOG_LEVEL_STATEMENT,
2925 p_module => C_MODULE,
2926 p_msg_text => to_char(l_num_errors_reprocessed)||' total error rows reprocessed.');
2927 FEM_ENGINES_PKG.Tech_Message
2928 (p_severity => G_LOG_LEVEL_STATEMENT,
2929 p_module => C_MODULE,
2930 p_msg_text => to_char(l_num_rows_rejected)||' total rows rejected.');
2931 END IF;
2932
2933 -- Print to concurrent log the number of rows loaded and rejected
2934 FEM_ENGINES_PKG.User_Message
2935 (p_app_name => 'FEM',
2936 p_msg_name => 'FEM_SD_LDR_PROCESS_SUMMARY',
2937 p_token1 => 'LOADNUM',
2938 p_value1 => l_num_rows_loaded,
2939 p_token2 => 'REJECTNUM',
2940 p_value2 => l_num_rows_rejected);
2941
2942 -- Update Number of Output Rows
2943 FEM_PL_PKG.Update_Num_of_Output_Rows(
2944 p_api_version => G_API_VERSION,
2945 p_commit => G_TRUE,
2946 p_request_id => p_request_id,
2947 p_object_id => p_object_id,
2948 p_table_name => p_table_name,
2949 p_statement_type => G_INSERT_STMT_TYPE,
2950 p_num_of_output_rows => l_num_rows_loaded,
2951 p_user_id => p_user_id,
2952 p_last_update_login => p_login_id,
2953 x_msg_count => l_msg_count,
2954 x_msg_data => l_msg_data,
2955 x_return_status => l_return_status);
2956
2957 IF G_LOG_LEVEL_STATEMENT >= g_log_current_level THEN
2958 FEM_ENGINES_PKG.Tech_Message
2959 (p_severity => G_LOG_LEVEL_STATEMENT,
2960 p_module => C_MODULE,
2961 p_msg_text => 'Call to FEM_PL_PKG.Update_Num_of_Output_Rows returned with status '
2962 ||l_return_status);
2963 END IF;
2964
2965 IF l_msg_count > 0 THEN
2966 Get_Put_Messages (
2967 p_msg_count => l_msg_count,
2968 p_msg_data => l_msg_data);
2969 END IF;
2970
2971 IF l_return_status <> G_RET_STS_SUCCESS THEN
2972 RAISE e_unexp_error;
2973 END IF;
2974
2975 -- Update Object Execution Status
2976 FEM_PL_PKG.Update_Obj_Exec_Status(
2977 p_api_version => G_API_VERSION,
2978 p_commit => G_TRUE,
2979 p_request_id => p_request_id,
2980 p_object_id => p_object_id,
2981 p_exec_status_code => p_exec_status,
2982 p_user_id => p_user_id,
2983 p_last_update_login => p_login_id,
2984 x_msg_count => l_msg_count,
2985 x_msg_data => l_msg_data,
2986 x_return_status => l_return_status);
2987
2988 IF G_LOG_LEVEL_STATEMENT >= g_log_current_level THEN
2989 FEM_ENGINES_PKG.Tech_Message
2990 (p_severity => G_LOG_LEVEL_STATEMENT,
2991 p_module => C_MODULE,
2992 p_msg_text => 'Call to FEM_PL_PKG.pdate_Obj_Exec_Status returned with status '
2993 ||l_return_status);
2994 END IF;
2995
2996 IF l_msg_count > 0 THEN
2997 Get_Put_Messages (
2998 p_msg_count => l_msg_count,
2999 p_msg_data => l_msg_data);
3000 END IF;
3001
3002 IF l_return_status <> G_RET_STS_SUCCESS THEN
3003 RAISE e_unexp_error;
3004 END IF;
3005
3006 -- Update Object Execution Errors
3007 FEM_PL_PKG.Update_Obj_Exec_Errors(
3008 p_api_version => G_API_VERSION,
3009 p_commit => G_TRUE,
3010 p_request_id => p_request_id,
3011 p_object_id => p_object_id,
3012 p_errors_reported => l_num_rows_rejected,
3013 p_errors_reprocessed => l_num_errors_reprocessed,
3014 p_user_id => p_user_id,
3015 p_last_update_login => p_login_id,
3016 x_msg_count => l_msg_count,
3017 x_msg_data => l_msg_data,
3018 x_return_status => l_return_status);
3019
3020 IF G_LOG_LEVEL_STATEMENT >= g_log_current_level THEN
3021 FEM_ENGINES_PKG.Tech_Message
3022 (p_severity => G_LOG_LEVEL_STATEMENT,
3023 p_module => C_MODULE,
3024 p_msg_text => 'Call to FEM_PL_PKG.Update_Obj_Exec_Errors returned with status '
3025 ||l_return_status);
3026 END IF;
3027
3028 IF l_msg_count > 0 THEN
3029 Get_Put_Messages (
3030 p_msg_count => l_msg_count,
3031 p_msg_data => l_msg_data);
3032 END IF;
3033
3034 IF l_return_status <> G_RET_STS_SUCCESS THEN
3035 RAISE e_unexp_error;
3036 END IF;
3037
3038 -- Update Request Status --
3039 FEM_PL_PKG.Update_Request_Status(
3040 p_api_version => G_API_VERSION,
3041 p_commit => G_TRUE,
3042 p_request_id => p_request_id,
3043 p_exec_status_code => p_exec_status,
3044 p_user_id => p_user_id,
3045 p_last_update_login => p_login_id,
3046 x_msg_count => l_msg_count,
3047 x_msg_data => l_msg_data,
3048 x_return_status => l_return_status);
3049
3050 IF G_LOG_LEVEL_STATEMENT >= g_log_current_level THEN
3051 FEM_ENGINES_PKG.Tech_Message
3052 (p_severity => G_LOG_LEVEL_STATEMENT,
3053 p_module => C_MODULE,
3054 p_msg_text => 'Call to FEM_PL_PKG.Update_Request_Status returned with status '
3055 ||l_return_status);
3056 END IF;
3057
3058 IF l_msg_count > 0 THEN
3059 Get_Put_Messages (
3060 p_msg_count => l_msg_count,
3061 p_msg_data => l_msg_data);
3062 END IF;
3063
3064 IF l_return_status <> G_RET_STS_SUCCESS THEN
3065 RAISE e_unexp_error;
3066 END IF;
3067
3068 -- Register Data Locations
3069 --
3070 -- Bug 4904687: Only set status to COMPLETE if both rows rejected is 0
3071 -- and rows loaded is greater than 0.
3072 --
3073 IF l_num_rows_rejected = 0 AND l_num_rows_loaded > 0 THEN
3074 BEGIN
3075 FEM_DIMENSION_UTIL_PKG.register_data_location
3076 (p_request_id => p_request_id,
3077 p_object_id => p_object_id,
3078 p_table_name => p_table_name,
3079 p_ledger_id => p_ledger_id,
3080 p_cal_per_id => p_cal_period_id,
3081 p_dataset_cd => p_dataset_code,
3082 p_source_cd => p_source_system_code,
3083 p_load_status => G_COMPLETE_L0AD_STATUS);
3084
3085 -- This commit is for the call to Register Data Locations.
3086 -- All other API's commit work as it completes successfully.
3087 COMMIT;
3088
3089 EXCEPTION
3090 WHEN others THEN
3091 FEM_ENGINES_PKG.Tech_Message
3092 (p_severity => G_LOG_LEVEL_STATEMENT,
3093 p_module => C_MODULE,
3094 p_msg_text => 'Call to FEM_DIMENSION_UTIL_PKG.register_data_location '
3095 ||'failed unexpectedly.');
3096 RAISE;
3097 END;
3098
3099 FEM_ENGINES_PKG.Tech_Message
3100 (p_severity => G_LOG_LEVEL_STATEMENT,
3101 p_module => C_MODULE,
3102 p_msg_text => 'Updated Data Location status to COMPLETE');
3103 END IF;
3104
3105 --
3106 -- Log the final message to the concurrent log
3107 --
3108 IF p_exec_status = 'SUCCESS' THEN
3109 -- Post success message to concurrent log
3110 FEM_ENGINES_PKG.User_Message
3111 (p_app_name => 'FEM',
3112 p_msg_name => 'FEM_EXEC_SUCCESS');
3113 FEM_ENGINES_PKG.Tech_Message
3114 (p_severity => G_LOG_LEVEL_STATEMENT,
3115 p_module => C_MODULE,
3116 p_app_name => 'FEM',
3117 p_msg_name => 'FEM_EXEC_SUCCESS');
3118
3119 -- set request status to SUCCESS
3120 l_dummy_boolean := FND_CONCURRENT.Set_Completion_Status
3121 (status => 'NORMAL', message => NULL);
3122 ELSE
3123 -- Post error rerun message to concurrent log
3124 FEM_ENGINES_PKG.User_Message
3125 (p_app_name => 'FEM',
3126 p_msg_name => 'FEM_EXEC_RERUN');
3127 FEM_ENGINES_PKG.Tech_Message
3128 (p_severity => G_LOG_LEVEL_STATEMENT,
3129 p_module => C_MODULE,
3130 p_app_name => 'FEM',
3131 p_msg_name => 'FEM_EXEC_RERUN');
3132
3133 -- set request status to ERROR_RERUN
3134 l_dummy_boolean := FND_CONCURRENT.Set_Completion_Status
3135 (status => 'ERROR', message => NULL);
3136 END IF;
3137
3138 --
3139 -- Perform cleanup
3140 --
3141
3142 -- If all goes well, Delete_Data_Slices
3143 FEM_MULTI_PROC_PKG.Delete_Data_Slices(
3144 p_req_id => p_request_id);
3145
3146 g_xdim_info_tbl.DELETE;
3147
3148 x_return_status := G_RET_STS_SUCCESS;
3149
3150 IF G_LOG_LEVEL_STATEMENT >= g_log_current_level THEN
3151 FEM_ENGINES_PKG.TECH_MESSAGE(
3152 p_severity => G_LOG_LEVEL_STATEMENT,
3153 p_module => C_MODULE,
3154 p_msg_text => 'Procedure returning with status of '||x_return_status);
3155 END IF;
3156 IF G_LOG_LEVEL_PROCEDURE >= g_log_current_level THEN
3157 FEM_ENGINES_PKG.TECH_MESSAGE(
3158 p_severity => G_LOG_LEVEL_PROCEDURE,
3159 p_module => C_MODULE,
3160 p_msg_text => 'End Procedure');
3161 END IF;
3162
3163 EXCEPTION
3164 WHEN others THEN
3165 x_return_status := G_RET_STS_UNEXP_ERROR;
3166
3167 FEM_ENGINES_PKG.User_Message
3168 (p_app_name => 'FEM',
3169 p_msg_name => 'FEM_UNEXPECTED_ERROR',
3170 p_token1 => 'ERR_MSG',
3171 p_value1 => SQLERRM);
3172 FEM_ENGINES_PKG.Tech_Message
3173 (p_severity => G_LOG_LEVEL_UNEXPECTED,
3174 p_module => C_MODULE,
3175 p_app_name => 'FEM',
3176 p_msg_name => 'FEM_UNEXPECTED_ERROR',
3177 p_token1 => 'ERR_MSG',
3178 p_value1 => SQLERRM);
3179
3180 -- set request status to ERROR_RERUN
3181 l_dummy_boolean := FND_CONCURRENT.Set_Completion_Status
3182 (status => 'ERROR', message => NULL);
3183
3184 END Post_Process;
3185
3186
3187 PROCEDURE Get_Put_Messages (
3188 p_msg_count IN NUMBER,
3189 p_msg_data IN VARCHAR2
3190 )
3191 IS
3192 C_MODULE CONSTANT FND_LOG_MESSAGES.module%TYPE :=
3193 'fem.plsql.fem_source_data_loader_pkg.get_put_messages';
3194 l_msg_count NUMBER;
3195 l_msg_data VARCHAR2(4000);
3196 l_msg_out NUMBER;
3197 l_message VARCHAR2(4000);
3198 BEGIN
3199
3200 FEM_ENGINES_PKG.TECH_MESSAGE
3201 (p_severity => G_LOG_LEVEL_STATEMENT,
3202 p_module => C_MODULE,
3203 p_msg_text => 'Message count is '||to_char(p_msg_count));
3204
3205 l_msg_data := p_msg_data;
3206
3207 IF (p_msg_count = 1) THEN
3208 FND_MESSAGE.Set_Encoded(l_msg_data);
3209 l_message := FND_MESSAGE.Get;
3210
3211 FEM_ENGINES_PKG.User_Message(
3212 p_msg_text => l_message);
3213
3214 FEM_ENGINES_PKG.TECH_MESSAGE
3215 (p_severity => G_LOG_LEVEL_STATEMENT,
3216 p_module => C_MODULE,
3217 p_msg_text => 'Message is '||l_message);
3218
3219 ELSIF (p_msg_count > 1) THEN
3220 FOR i IN 1..p_msg_count LOOP
3221 FND_MSG_PUB.Get(
3222 p_msg_index => i,
3223 p_encoded => G_FALSE,
3224 p_data => l_message,
3225 p_msg_index_out => l_msg_out);
3226
3227 FEM_ENGINES_PKG.User_Message(
3228 p_msg_text => l_message);
3229
3230 FEM_ENGINES_PKG.TECH_MESSAGE
3231 (p_severity => G_LOG_LEVEL_STATEMENT,
3232 p_module => C_MODULE,
3233 p_msg_text => 'Message is '||l_message);
3234 END LOOP;
3235 END IF;
3236
3237 FND_MSG_PUB.Initialize;
3238
3239 EXCEPTION
3240 WHEN others THEN
3241 FEM_ENGINES_PKG.Tech_Message
3242 (p_severity => G_LOG_LEVEL_UNEXPECTED,
3243 p_module => C_MODULE,
3244 p_app_name => 'FEM',
3245 p_msg_name => 'FEM_UNEXPECTED_ERROR',
3246 p_token1 => 'ERR_MSG',
3247 p_value1 => SQLERRM);
3248 RAISE;
3249
3250 END Get_Put_Messages;
3251
3252 --
3253 -- Procedure
3254 -- Validate_Obj_Def
3255 -- Purpose
3256 -- Validates the object definition ID
3257 -- History
3258 -- 09-07-04 GCHENG Created
3259 -- Arguments
3260 -- p_api_version : API version
3261 -- p_object_type : Object type code
3262 -- p_obj_def_id : Object definition_ID
3263 -- p_object_id : Object ID
3264 -- x_table_name : Source data target table name
3265 -- x_msg_count : Message count
3266 -- x_msg_data : Message text (if msg count = 1)
3267 -- x_return_status : Return status
3268 -- (look at FND_API for the possible statuses)
3269 -- Notes
3270 --
3271 PROCEDURE Validate_Obj_Def (
3272 p_api_version IN NUMBER,
3273 p_object_type IN VARCHAR2,
3274 p_obj_def_id IN NUMBER,
3275 x_object_id OUT NOCOPY NUMBER,
3276 x_table_name OUT NOCOPY VARCHAR2,
3277 x_msg_count OUT NOCOPY NUMBER,
3278 x_msg_data OUT NOCOPY VARCHAR2,
3279 x_return_status OUT NOCOPY VARCHAR2
3280 ) IS
3281 C_MODULE CONSTANT FND_LOG_MESSAGES.module%TYPE :=
3282 'fem.plsql.fem_source_data_loader_pkg.validate_obj_def';
3283 C_API_VERSION CONSTANT NUMBER := 1.0;
3284 C_API_NAME CONSTANT VARCHAR2(30) := 'Validate_Obj_Def';
3285
3286 l_object_type_code FEM_OBJECT_TYPES.object_type_code%TYPE;
3287 e_inv_obj_def EXCEPTION;
3288 BEGIN
3289 IF G_LOG_LEVEL_PROCEDURE >= g_log_current_level THEN
3290 FEM_ENGINES_PKG.TECH_MESSAGE(
3291 p_severity => G_LOG_LEVEL_PROCEDURE,
3292 p_module => C_MODULE,
3293 p_msg_text => 'Begin Procedure');
3294 END IF;
3295 IF G_LOG_LEVEL_STATEMENT >= g_log_current_level THEN
3296 FEM_ENGINES_PKG.TECH_MESSAGE(
3297 p_severity => G_LOG_LEVEL_STATEMENT,
3298 p_module => C_MODULE,
3299 p_msg_text => 'Parameter: Object type code is '||p_object_type);
3300 FEM_ENGINES_PKG.TECH_MESSAGE(
3301 p_severity => G_LOG_LEVEL_STATEMENT,
3302 p_module => C_MODULE,
3303 p_msg_text => 'Parameter: Object definition ID is '||p_obj_def_id);
3304 END IF;
3305
3306 -- Check for call compatibility.
3307 IF NOT FND_API.Compatible_API_Call (C_API_VERSION,
3308 p_api_version,
3309 C_API_NAME,
3310 G_PKG_NAME)
3311 THEN
3312 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3313 END IF;
3314
3315 -- --------------------------------------------------------------------
3316 -- Validate the Object Definition ID engine parameter by making sure it
3317 -- exists or is an old approved copy.
3318 -- Bug 4107295: Check for folder security.
3319 -- Bug 3995222: Rely on FEM_DATA_LOADER_OBJECTS to determine the relationship
3320 -- between loader objects and the tables being loaded.
3321 -- --------------------------------------------------------------------
3322 BEGIN
3323 SELECT o.object_id, t.table_name
3324 INTO x_object_id, x_table_name
3325 FROM fem_object_definition_b od, fem_object_catalog_b o,
3326 fem_user_folders f, fem_data_loader_objects d,
3327 fem_table_class_assignmt_v t
3328 WHERE od.object_definition_id = p_obj_def_id
3329 AND od.object_id = o.object_id
3330 AND o.object_type_code = 'SOURCE_DATA_LOADER'
3331 AND o.folder_id = f.folder_id
3332 AND f.user_id = FND_GLOBAL.user_id
3333 AND d.object_id = o.object_id
3334 AND d.table_name = t.table_name
3335 AND table_classification_code = 'SOURCE_DATA_TABLE'
3336 AND old_approved_copy_flag = 'N';
3337
3338 IF G_LOG_LEVEL_STATEMENT >= g_log_current_level THEN
3339 FEM_ENGINES_PKG.TECH_MESSAGE(
3340 p_severity => G_LOG_LEVEL_STATEMENT,
3341 p_module => C_MODULE,
3342 p_msg_text => 'Object ID is '||to_char(x_object_id));
3343
3344 FEM_ENGINES_PKG.TECH_MESSAGE(
3345 p_severity => G_LOG_LEVEL_STATEMENT,
3346 p_module => C_MODULE,
3347 p_msg_text => 'Table name is '||x_table_name);
3348 END IF;
3349 EXCEPTION
3350 WHEN no_data_found THEN
3351 FEM_ENGINES_PKG.TECH_MESSAGE(
3352 p_severity => G_LOG_LEVEL_STATEMENT,
3353 p_module => C_MODULE,
3354 p_msg_text => 'Object def ID '||to_char(p_obj_def_id)
3355 ||' either cannot be found or is an old approved copy'
3356 ||' or the associated table is not enabled.');
3357 RAISE e_inv_obj_def;
3358 END;
3359
3360 -- --------------------------------------------------------------------
3361 -- Check to make sure object type matches what is passed in.
3362 -- --------------------------------------------------------------------
3363
3364 BEGIN
3365 SELECT object_type_code
3366 INTO l_object_type_code
3367 FROM fem_object_catalog_b
3368 WHERE object_id = x_object_id
3369 AND object_type_code = p_object_type;
3370
3371 EXCEPTION
3372 WHEN NO_DATA_FOUND THEN
3373 FEM_ENGINES_PKG.TECH_MESSAGE(
3374 p_severity => G_LOG_LEVEL_STATEMENT,
3375 p_module => C_MODULE,
3376 p_msg_text => 'Object ID '||to_char(x_object_id)
3377 ||' cannot be found, or is not of type '||p_object_type);
3378 RAISE e_inv_obj_def;
3379 END;
3380
3381 x_return_status := G_RET_STS_SUCCESS;
3382
3383 IF G_LOG_LEVEL_STATEMENT >= g_log_current_level THEN
3384 FEM_ENGINES_PKG.TECH_MESSAGE(
3385 p_severity => G_LOG_LEVEL_STATEMENT,
3386 p_module => C_MODULE,
3387 p_msg_text => 'Returning with status of '||x_return_status);
3388 END IF;
3389 IF G_LOG_LEVEL_PROCEDURE >= g_log_current_level THEN
3390 FEM_ENGINES_PKG.TECH_MESSAGE(
3391 p_severity => G_LOG_LEVEL_PROCEDURE,
3392 p_module => C_MODULE,
3393 p_msg_text => 'End Procedure');
3394 END IF;
3395
3396 EXCEPTION
3397 WHEN e_inv_obj_def THEN
3398 x_return_status := G_RET_STS_ERROR;
3399
3400 FEM_ENGINES_PKG.put_message(
3401 p_app_name =>'FEM',
3402 p_msg_name =>'FEM_SD_LDR_INV_OBJ_DEF',
3403 p_token1 => 'OBJ_DEF_ID',
3404 p_value1 => p_obj_def_id);
3405
3406 FEM_ENGINES_PKG.Tech_Message
3407 (p_severity => G_LOG_LEVEL_ERROR,
3408 p_module => C_MODULE,
3409 p_app_name => 'FEM',
3410 p_msg_name => 'FEM_SD_LDR_INV_OBJ_DEF',
3411 p_token1 => 'OBJ_DEF_ID',
3412 p_value1 => p_obj_def_id);
3413
3414 FND_MSG_PUB.Count_And_Get
3415 (p_count => x_msg_count,
3416 p_data => x_msg_data);
3417
3418 WHEN others THEN
3419 x_return_status := G_RET_STS_UNEXP_ERROR;
3420
3421 END Validate_Obj_Def;
3422
3423 --
3424 -- Procedure
3425 -- Validate_Table
3426 -- Purpose
3427 -- Validates the table
3428 -- History
3429 -- 09-07-04 GCHENG Created
3430 -- Arguments
3431 -- p_api_version : API version
3432 -- p_object_type : Object type code
3433 -- p_table_name : Table name
3434 -- p_table_classification : Table classification
3435 -- x_msg_count : Message count
3436 -- x_msg_data : Message text (if msg count = 1)
3437 -- x_return_status : Return status
3438 -- (look at FND_API for the possible statuses)
3439 -- Notes
3440 --
3441 PROCEDURE Validate_Table (
3442 p_api_version IN NUMBER,
3443 p_object_type IN VARCHAR2,
3444 p_table_name IN VARCHAR2,
3445 p_table_classification IN VARCHAR2,
3446 x_msg_count OUT NOCOPY NUMBER,
3447 x_msg_data OUT NOCOPY VARCHAR2,
3448 x_return_status OUT NOCOPY VARCHAR2
3449 ) IS
3450 C_MODULE CONSTANT FND_LOG_MESSAGES.module%TYPE :=
3451 'fem.plsql.fem_source_data_loader_pkg.validate_table';
3452 C_API_VERSION CONSTANT NUMBER := 1.0;
3453 C_API_NAME CONSTANT VARCHAR2(30) := 'Validate_Table';
3454 l_count NUMBER := 0;
3455 BEGIN
3456 IF G_LOG_LEVEL_PROCEDURE >= g_log_current_level THEN
3457 FEM_ENGINES_PKG.TECH_MESSAGE(
3458 p_severity => G_LOG_LEVEL_PROCEDURE,
3459 p_module => C_MODULE,
3460 p_msg_text => 'Begin Procedure');
3461 END IF;
3462 IF G_LOG_LEVEL_STATEMENT >= g_log_current_level THEN
3463 FEM_ENGINES_PKG.TECH_MESSAGE(
3464 p_severity => G_LOG_LEVEL_STATEMENT,
3465 p_module => C_MODULE,
3466 p_msg_text => 'Parameter: Object type code is '||p_object_type);
3467 FEM_ENGINES_PKG.TECH_MESSAGE(
3468 p_severity => G_LOG_LEVEL_STATEMENT,
3469 p_module => C_MODULE,
3470 p_msg_text => 'Parameter: Table name is '||p_table_name);
3471 FEM_ENGINES_PKG.TECH_MESSAGE(
3472 p_severity => G_LOG_LEVEL_STATEMENT,
3473 p_module => C_MODULE,
3474 p_msg_text => 'Parameter: Table classification is '||p_table_classification);
3475 END IF;
3476
3477 -- Check for call compatibility.
3478 IF NOT FND_API.Compatible_API_Call (C_API_VERSION,
3479 p_api_version,
3480 C_API_NAME,
3481 G_PKG_NAME)
3482 THEN
3483 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3484 END IF;
3485
3486 SELECT COUNT(*)
3487 INTO l_count
3488 FROM fem_table_class_assignmt_v
3489 WHERE table_name = p_table_name
3490 AND table_classification_code = p_table_classification;
3491
3492 IF G_LOG_LEVEL_STATEMENT >= g_log_current_level THEN
3493 FEM_ENGINES_PKG.TECH_MESSAGE(
3494 p_severity => G_LOG_LEVEL_STATEMENT,
3495 p_module => C_MODULE,
3496 p_msg_text => 'Table classification '||p_table_classification||' has '
3497 ||to_char(l_count)||' instances of table '||p_table_name);
3498 END IF;
3499
3500 IF l_count = 1 THEN
3501 x_return_status := G_RET_STS_SUCCESS;
3502 ELSE
3503 x_return_status := G_RET_STS_ERROR;
3504
3505 FEM_ENGINES_PKG.put_message(
3506 p_app_name =>'FEM',
3507 p_msg_name =>'FEM_SD_LDR_INV_TABLE',
3508 p_token1 => 'TABLE',
3509 p_value1 => p_table_name);
3510
3511 FEM_ENGINES_PKG.Tech_Message
3512 (p_severity => G_LOG_LEVEL_ERROR,
3513 p_module => C_MODULE,
3514 p_app_name => 'FEM',
3515 p_msg_name => 'FEM_SD_LDR_INV_TABLE',
3516 p_token1 => 'TABLE',
3517 p_value1 => p_table_name);
3518 END IF;
3519
3520 FND_MSG_PUB.Count_And_Get
3521 (p_count => x_msg_count,
3522 p_data => x_msg_data);
3523
3524 IF G_LOG_LEVEL_STATEMENT >= g_log_current_level THEN
3525 FEM_ENGINES_PKG.TECH_MESSAGE(
3526 p_severity => G_LOG_LEVEL_STATEMENT,
3527 p_module => C_MODULE,
3528 p_msg_text => 'Returning with status of '||x_return_status);
3529 END IF;
3530 IF G_LOG_LEVEL_PROCEDURE >= g_log_current_level THEN
3531 FEM_ENGINES_PKG.TECH_MESSAGE(
3532 p_severity => G_LOG_LEVEL_PROCEDURE,
3533 p_module => C_MODULE,
3534 p_msg_text => 'End Procedure');
3535 END IF;
3536
3537 EXCEPTION
3538 WHEN others THEN
3539 x_return_status := G_RET_STS_UNEXP_ERROR;
3540
3541 END Validate_Table;
3542
3543 --
3544 -- Procedure
3545 -- Validate_Exec_Mode
3546 -- Purpose
3547 -- Validates the execution mode.
3548 -- History
3549 -- 09-07-04 GCHENG Created
3550 -- 02-26-06 HKANIVEN Modified the code to handle
3551 -- 'R'(Reprocessing mode) as a valid mode
3552 -- Arguments
3553 -- p_api_version : API version
3554 -- p_object_type : Object type code
3555 -- p_exec_mode : Execution mode
3556 -- x_msg_count : Message count
3557 -- x_msg_data : Message text (if msg count = 1)
3558 -- x_return_status : Return status
3559 -- (look at FND_API for the possible statuses)
3560 -- Notes
3561 --
3562 PROCEDURE Validate_Exec_Mode (
3563 p_api_version IN NUMBER,
3564 p_object_type IN VARCHAR2,
3565 p_exec_mode IN VARCHAR2,
3566 x_msg_count OUT NOCOPY NUMBER,
3567 x_msg_data OUT NOCOPY VARCHAR2,
3568 x_return_status OUT NOCOPY VARCHAR2
3569 ) IS
3570 C_MODULE CONSTANT FND_LOG_MESSAGES.module%TYPE :=
3571 'fem.plsql.fem_source_data_loader_pkg.validate_exec_mode';
3572 C_API_VERSION CONSTANT NUMBER := 1.0;
3573 C_API_NAME CONSTANT VARCHAR2(30) := 'Validate_Exec_Mode';
3574 l_count NUMBER := 0;
3575
3576 e_invalid_object_type EXCEPTION;
3577 BEGIN
3578
3579 IF G_LOG_LEVEL_PROCEDURE >= g_log_current_level THEN
3580 FEM_ENGINES_PKG.TECH_MESSAGE(
3581 p_severity => G_LOG_LEVEL_PROCEDURE,
3582 p_module => C_MODULE,
3583 p_msg_text => 'Begin Procedure');
3584 END IF;
3585 IF G_LOG_LEVEL_STATEMENT >= g_log_current_level THEN
3586 FEM_ENGINES_PKG.TECH_MESSAGE(
3587 p_severity => G_LOG_LEVEL_STATEMENT,
3588 p_module => C_MODULE,
3589 p_msg_text => 'Parameter: Object type code is '||p_object_type);
3590 FEM_ENGINES_PKG.TECH_MESSAGE(
3591 p_severity => G_LOG_LEVEL_STATEMENT,
3592 p_module => C_MODULE,
3593 p_msg_text => 'Parameter: Exec Mode is '||p_exec_mode);
3594 END IF;
3595
3596 -- Check for call compatibility.
3597 IF NOT FND_API.Compatible_API_Call (C_API_VERSION,
3598 p_api_version,
3599 C_API_NAME,
3600 G_PKG_NAME)
3601 THEN
3602 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3603 END IF;
3604
3605 -- Start of Replacement mode enhancement
3606
3607 IF p_object_type = 'SOURCE_DATA_LOADER' THEN
3608 SELECT COUNT(*)
3609 INTO l_count
3610 FROM fnd_lookup_values
3611 WHERE lookup_type = 'FEM_PL_EXEC_MODE_DSC'
3612 AND lookup_code = p_exec_mode
3613 AND language = USERENV('LANG')
3614 AND view_application_id = 274
3615 AND security_group_id =
3616 fnd_global.lookup_security_group(lookup_type, view_application_id)
3617 AND enabled_flag = 'Y'
3618 AND lookup_code IN ('R','E','S');
3619
3620 -- End of Replacement mode enhancement
3621 ELSE
3622 FEM_ENGINES_PKG.TECH_MESSAGE(
3623 p_severity => G_LOG_LEVEL_UNEXPECTED,
3624 p_module => C_MODULE,
3625 p_msg_text => 'Object type code '||p_object_type||' is not valid!');
3626 RAISE e_invalid_object_type;
3627 END IF;
3628
3629 IF G_LOG_LEVEL_STATEMENT >= g_log_current_level THEN
3630 FEM_ENGINES_PKG.TECH_MESSAGE(
3631 p_severity => G_LOG_LEVEL_STATEMENT,
3632 p_module => C_MODULE,
3633 p_msg_text => 'Execution mode lookup type FEM_SRCDATA_LOADER_EXEC_MODE has '
3634 ||to_char(l_count)||' instances of lookup code '||p_exec_mode);
3635 END IF;
3636
3637 IF l_count = 1 THEN
3638 x_return_status := G_RET_STS_SUCCESS;
3639 ELSE
3640 x_return_status := G_RET_STS_ERROR;
3641
3642 FEM_ENGINES_PKG.put_message(
3643 p_app_name =>'FEM',
3644 p_msg_name =>'FEM_SD_LDR_INV_EXEC_MODE',
3645 p_token1 => 'EXEC_MODE',
3646 p_value1 => p_exec_mode);
3647
3648 FEM_ENGINES_PKG.Tech_Message
3649 (p_severity => G_LOG_LEVEL_ERROR,
3650 p_module => C_MODULE,
3651 p_app_name => 'FEM',
3652 p_msg_name => 'FEM_SD_LDR_INV_EXEC_MODE',
3653 p_token1 => 'EXEC_MODE',
3654 p_value1 => p_exec_mode);
3655 END IF;
3656
3657 FND_MSG_PUB.Count_And_Get
3658 (p_count => x_msg_count,
3659 p_data => x_msg_data);
3660
3661 IF G_LOG_LEVEL_STATEMENT >= g_log_current_level THEN
3662 FEM_ENGINES_PKG.TECH_MESSAGE(
3663 p_severity => G_LOG_LEVEL_STATEMENT,
3664 p_module => C_MODULE,
3665 p_msg_text => 'Returning with status of '||x_return_status);
3666 END IF;
3667 IF G_LOG_LEVEL_PROCEDURE >= g_log_current_level THEN
3668 FEM_ENGINES_PKG.TECH_MESSAGE(
3669 p_severity => G_LOG_LEVEL_PROCEDURE,
3670 p_module => C_MODULE,
3671 p_msg_text => 'End Procedure');
3672 END IF;
3673
3674 EXCEPTION
3675 WHEN others THEN
3676 x_return_status := G_RET_STS_UNEXP_ERROR;
3677
3678 END Validate_Exec_Mode;
3679
3680 --
3681 -- Procedure
3682 -- Validate_Ledger
3683 -- Purpose
3684 -- Validates the ledger ID
3685 -- History
3686 -- 09-07-04 GCHENG Created
3687 -- Arguments
3688 -- p_api_version : API version
3689 -- p_object_type : Object type code
3690 -- p_ledger_id : Ledger ID
3691 -- x_ledger_dc : Ledger display code
3692 -- x_msg_count : Message count
3693 -- x_msg_data : Message text (if msg count = 1)
3694 -- x_return_status : Return status
3695 -- (look at FND_API for the possible statuses)
3696 -- Notes
3697 --
3698 PROCEDURE Validate_Ledger (
3699 p_api_version IN NUMBER,
3700 p_object_type IN VARCHAR2,
3701 p_ledger_id IN NUMBER,
3702 x_ledger_dc OUT NOCOPY VARCHAR2,
3703 x_ledger_calendar_id OUT NOCOPY NUMBER,
3704 x_ledger_per_hier_obj_def_id OUT NOCOPY NUMBER,
3705 x_msg_count OUT NOCOPY NUMBER,
3706 x_msg_data OUT NOCOPY VARCHAR2,
3707 x_return_status OUT NOCOPY VARCHAR2
3708 ) IS
3709 C_MODULE CONSTANT FND_LOG_MESSAGES.module%TYPE :=
3710 'fem.plsql.fem_source_data_loader_pkg.validate_ledger';
3711 C_API_VERSION CONSTANT NUMBER := 1.0;
3712 C_API_NAME CONSTANT VARCHAR2(30) := 'Validate_Ledger';
3713 l_ledger_dim_id NUMBER;
3714 l_dim_attr_id NUMBER;
3715 l_dim_attr_ver_id NUMBER;
3716 l_ledger_per_hier_obj_id NUMBER;
3717 l_return_code NUMBER; -- 0 if success, 2 if error
3718
3719 e_inv_ledger EXCEPTION;
3720 BEGIN
3721
3722 -- Check for call compatibility.
3723 IF NOT FND_API.Compatible_API_Call (C_API_VERSION,
3724 p_api_version,
3725 C_API_NAME,
3726 G_PKG_NAME)
3727 THEN
3728 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3729 END IF;
3730
3731 IF G_LOG_LEVEL_PROCEDURE >= g_log_current_level THEN
3732 FEM_ENGINES_PKG.TECH_MESSAGE(
3733 p_severity => G_LOG_LEVEL_PROCEDURE,
3734 p_module => C_MODULE,
3735 p_msg_text => 'Begin Procedure');
3736 END IF;
3737 IF G_LOG_LEVEL_STATEMENT >= g_log_current_level THEN
3738 FEM_ENGINES_PKG.TECH_MESSAGE(
3739 p_severity => G_LOG_LEVEL_STATEMENT,
3740 p_module => C_MODULE,
3741 p_msg_text => 'Parameter: Object type code is '||p_object_type);
3742 FEM_ENGINES_PKG.TECH_MESSAGE(
3743 p_severity => G_LOG_LEVEL_STATEMENT,
3744 p_module => C_MODULE,
3745 p_msg_text => 'Parameter: Ledger ID is '||p_ledger_id);
3746 END IF;
3747
3748 x_return_status := G_RET_STS_SUCCESS;
3749
3750 BEGIN
3751 SELECT ledger_display_code
3752 INTO x_ledger_dc
3753 FROM fem_ledgers_b
3754 WHERE ledger_id = p_ledger_id
3755 AND enabled_flag = 'Y'
3756 AND personal_flag = 'N';
3757
3758 IF G_LOG_LEVEL_STATEMENT >= g_log_current_level THEN
3759 FEM_ENGINES_PKG.TECH_MESSAGE(
3760 p_severity => G_LOG_LEVEL_STATEMENT,
3761 p_module => C_MODULE,
3762 p_msg_text => 'Ledger display code is '||x_ledger_dc);
3763 END IF;
3764 EXCEPTION
3765 WHEN no_data_found THEN
3766 FEM_ENGINES_PKG.TECH_MESSAGE(
3767 p_severity => G_LOG_LEVEL_STATEMENT,
3768 p_module => C_MODULE,
3769 p_msg_text => 'Ledger ID '||to_char(p_ledger_id)
3770 ||' either cannot be found or is not enabled or is personal.');
3771 RAISE e_inv_ledger;
3772 END;
3773
3774 --
3775 -- Get the Hierarchy Object Definition ID of the Time hierarchy assigned
3776 -- to the given ledger. It is stored as a row-based ledger attribute.
3777 --
3778
3779 -- Get the Dimension ID for Ledger.
3780 -- If this returns no data, this is an unexpected error
3781 -- and hence is not caught until the end of the procedure.
3782 SELECT dimension_id
3783 INTO l_ledger_dim_id
3784 FROM fem_dimensions_b
3785 WHERE dimension_varchar_label = 'LEDGER';
3786
3787 IF G_LOG_LEVEL_STATEMENT >= g_log_current_level THEN
3788 FEM_ENGINES_PKG.TECH_MESSAGE(
3789 p_severity => G_LOG_LEVEL_STATEMENT,
3790 p_module => C_MODULE,
3791 p_msg_text => 'Dimension ID of Ledger dimenions is '
3792 ||to_char(l_ledger_dim_id));
3793 END IF;
3794
3795 FEM_DIMENSION_UTIL_PKG.get_dim_attr_id_ver_id
3796 (p_dim_id => l_ledger_dim_id,
3797 p_attr_label => 'CAL_PERIOD_HIER_OBJ_DEF_ID',
3798 x_attr_id => l_dim_attr_id,
3799 x_ver_id => l_dim_attr_ver_id,
3800 x_err_code => l_return_code);
3801
3802 IF G_LOG_LEVEL_STATEMENT >= g_log_current_level THEN
3803 FEM_ENGINES_PKG.TECH_MESSAGE(
3804 p_severity => G_LOG_LEVEL_STATEMENT,
3805 p_module => C_MODULE,
3806 p_msg_text => 'FEM_DIMENSION_UTIL_PKG.get_dim_attr_id_ver_id'
3807 ||'(CAL_PERIOD_HIER_OBJ_DEF_ID) returned with error code of '
3808 ||to_char(l_return_code));
3809 END IF;
3810
3811 IF l_return_code <> 0 THEN -- if not success
3812 RAISE e_inv_ledger;
3813 END IF;
3814
3815 BEGIN
3816 SELECT dim_attribute_numeric_member
3817 INTO x_ledger_per_hier_obj_def_id
3818 FROM fem_ledgers_attr
3819 WHERE attribute_id = l_dim_attr_id
3820 AND version_id = l_dim_attr_ver_id
3821 AND ledger_id = p_ledger_id;
3822
3823 IF G_LOG_LEVEL_STATEMENT >= g_log_current_level THEN
3824 FEM_ENGINES_PKG.Tech_Message
3825 (p_severity => G_LOG_LEVEL_STATEMENT,
3826 p_module => C_MODULE,
3827 p_msg_text => 'The Ledger Calendar Period Hierarchy object definition ID is '
3828 ||to_char(x_ledger_per_hier_obj_def_id));
3829 END IF;
3830 EXCEPTION
3831 WHEN no_data_found THEN
3832 FEM_ENGINES_PKG.TECH_MESSAGE(
3833 p_severity => G_LOG_LEVEL_STATEMENT,
3834 p_module => C_MODULE,
3835 p_msg_text => 'Ledger is missing a Calendar Period Hierarchy attribute.');
3836
3837 RAISE e_inv_ledger;
3838 END;
3839
3840 BEGIN
3841 SELECT object_id
3842 INTO l_ledger_per_hier_obj_id
3843 FROM fem_object_definition_b
3844 WHERE object_definition_id = x_ledger_per_hier_obj_def_id;
3845
3846 IF G_LOG_LEVEL_STATEMENT >= g_log_current_level THEN
3847 FEM_ENGINES_PKG.Tech_Message
3848 (p_severity => G_LOG_LEVEL_STATEMENT,
3849 p_module => C_MODULE,
3850 p_msg_text => 'The Ledger Calendar Period Hierarchy object ID is '
3851 ||to_char(l_ledger_per_hier_obj_id));
3852 END IF;
3853 EXCEPTION
3854 WHEN no_data_found THEN
3855 FEM_ENGINES_PKG.TECH_MESSAGE(
3856 p_severity => G_LOG_LEVEL_STATEMENT,
3857 p_module => C_MODULE,
3858 p_msg_text => 'The Legder Calendar Period Hierarchy object definition '
3859 ||'does not have an associated object ID.');
3860
3861 RAISE e_inv_ledger;
3862 END;
3863
3864 -- Look up the Calendar ID for that hierarchy.
3865 BEGIN
3866 SELECT calendar_id
3867 INTO x_ledger_calendar_id
3868 FROM fem_hierarchies
3869 WHERE hierarchy_obj_id = l_ledger_per_hier_obj_id;
3870
3871 IF G_LOG_LEVEL_STATEMENT >= g_log_current_level THEN
3872 FEM_ENGINES_PKG.Tech_Message
3873 (p_severity => G_LOG_LEVEL_STATEMENT,
3874 p_module => C_MODULE,
3875 p_msg_text => 'The Calendar ID associated with the Ledger is '
3876 ||to_char(x_ledger_calendar_id));
3877 END IF;
3878 EXCEPTION
3879 WHEN no_data_found THEN
3880 FEM_ENGINES_PKG.TECH_MESSAGE(
3881 p_severity => G_LOG_LEVEL_STATEMENT,
3882 p_module => C_MODULE,
3883 p_msg_text => 'The Legder Calendar Period Hierarchy object '
3884 ||'is not a valid Hierarchy.');
3885
3886 RAISE e_inv_ledger;
3887 END;
3888
3889 FND_MSG_PUB.Count_And_Get
3890 (p_count => x_msg_count,
3891 p_data => x_msg_data);
3892
3893 IF G_LOG_LEVEL_STATEMENT >= g_log_current_level THEN
3894 FEM_ENGINES_PKG.TECH_MESSAGE(
3895 p_severity => G_LOG_LEVEL_STATEMENT,
3896 p_module => C_MODULE,
3897 p_msg_text => 'Returning with status of '||x_return_status);
3898 END IF;
3899 IF G_LOG_LEVEL_PROCEDURE >= g_log_current_level THEN
3900 FEM_ENGINES_PKG.TECH_MESSAGE(
3901 p_severity => G_LOG_LEVEL_PROCEDURE,
3902 p_module => C_MODULE,
3903 p_msg_text => 'End Procedure');
3904 END IF;
3905
3906 EXCEPTION
3907 WHEN e_inv_ledger THEN
3908 x_return_status := G_RET_STS_ERROR;
3909
3910 FEM_ENGINES_PKG.put_message(
3911 p_app_name =>'FEM',
3912 p_msg_name =>'FEM_SD_LDR_INV_LEDGER',
3913 p_token1 => 'LEDGER',
3914 p_value1 => p_ledger_id);
3915
3916 FEM_ENGINES_PKG.Tech_Message
3917 (p_severity => G_LOG_LEVEL_ERROR,
3918 p_module => C_MODULE,
3919 p_app_name => 'FEM',
3920 p_msg_name => 'FEM_SD_LDR_INV_LEDGER',
3921 p_token1 => 'LEDGER',
3922 p_value1 => p_ledger_id);
3923
3924 FND_MSG_PUB.Count_And_Get
3925 (p_count => x_msg_count,
3926 p_data => x_msg_data);
3927
3928 WHEN others THEN
3929 x_return_status := G_RET_STS_UNEXP_ERROR;
3930
3931 END Validate_Ledger;
3932
3933 --
3934 -- Procedure
3935 -- Validate_Cal_Period
3936 -- Purpose
3937 -- Validates the calendar period ID
3938 -- History
3939 -- 09-07-04 GCHENG Created
3940 -- Arguments
3941 -- p_api_version : API version
3942 -- p_object_type : Object type code
3943 -- p_cal_period_id : Calendar period ID
3944 -- p_ledger_id : Object ID that identifies the rule being executed.
3945 -- p_ledger_calendar_id : Calendar ID associated to the ledger
3946 -- p_ledger_per_hier_obj_def_id : Hierarchy object def ID
3947 -- x_calp_dim_grp_dc : Cal period dimension group display code
3948 -- x_cal_per_end_date : Cal period end period date
3949 -- x_cal_per_number : Cal period number
3950 -- x_msg_count : Message count
3951 -- x_msg_data : Message text (if msg count = 1)
3952 -- x_return_status : Return status
3953 -- (look at FND_API for the possible statuses)
3954 -- Notes
3955 --
3956 PROCEDURE Validate_Cal_Period (
3957 p_api_version IN NUMBER,
3958 p_object_type IN VARCHAR2,
3959 p_cal_period_id IN NUMBER,
3960 p_ledger_id IN NUMBER,
3961 p_ledger_calendar_id IN NUMBER,
3962 p_ledger_per_hier_obj_def_id IN NUMBER,
3963 x_calp_dim_grp_dc OUT NOCOPY VARCHAR2,
3964 x_cal_per_end_date OUT NOCOPY DATE,
3965 x_cal_per_number OUT NOCOPY NUMBER,
3966 x_msg_count OUT NOCOPY NUMBER,
3967 x_msg_data OUT NOCOPY VARCHAR2,
3968 x_return_status OUT NOCOPY VARCHAR2
3969 ) IS
3970 C_MODULE CONSTANT FND_LOG_MESSAGES.module%TYPE :=
3971 'fem.plsql.fem_source_data_loader_pkg.validate_cal_period';
3972 C_API_VERSION CONSTANT NUMBER := 1.0;
3973 C_API_NAME CONSTANT VARCHAR2(30) := 'Validate_Cal_Period';
3974 l_cal_per_calendar_id NUMBER;
3975 l_cal_per_dim_grp_id NUMBER;
3976 l_dummy VARCHAR2(1);
3977 l_cal_per_dim_id NUMBER;
3978 l_dim_attr_id NUMBER;
3979 l_dim_attr_ver_id NUMBER;
3980 l_return_code NUMBER; -- 0 if success, 2 if error
3981
3982 e_inv_cal_period EXCEPTION;
3983 e_mismatch_calendar EXCEPTION;
3984 e_per_not_in_ledger EXCEPTION;
3985 BEGIN
3986
3987 IF G_LOG_LEVEL_PROCEDURE >= g_log_current_level THEN
3988 FEM_ENGINES_PKG.TECH_MESSAGE(
3989 p_severity => G_LOG_LEVEL_PROCEDURE,
3990 p_module => C_MODULE,
3991 p_msg_text => 'Begin Procedure');
3992 END IF;
3993 IF G_LOG_LEVEL_STATEMENT >= g_log_current_level THEN
3994 FEM_ENGINES_PKG.TECH_MESSAGE(
3995 p_severity => G_LOG_LEVEL_STATEMENT,
3996 p_module => C_MODULE,
3997 p_msg_text => 'Parameter: Object type code is '||p_object_type);
3998 FEM_ENGINES_PKG.TECH_MESSAGE(
3999 p_severity => G_LOG_LEVEL_STATEMENT,
4000 p_module => C_MODULE,
4001 p_msg_text => 'Parameter: Calendar Period ID is '||p_cal_period_id);
4002 END IF;
4003
4004 -- Check for call compatibility.
4005 IF NOT FND_API.Compatible_API_Call (C_API_VERSION,
4006 p_api_version,
4007 C_API_NAME,
4008 G_PKG_NAME)
4009 THEN
4010 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4011 END IF;
4012
4013 -- Get the calendar of the cal period
4014 BEGIN
4015 SELECT calendar_id, dimension_group_id
4016 INTO l_cal_per_calendar_id, l_cal_per_dim_grp_id
4017 FROM fem_cal_periods_b
4018 WHERE cal_period_id = p_cal_period_id
4019 AND enabled_flag = 'Y'
4020 AND personal_flag = 'N';
4021
4022 IF G_LOG_LEVEL_STATEMENT >= g_log_current_level THEN
4023 FEM_ENGINES_PKG.Tech_Message
4024 (p_severity => G_LOG_LEVEL_STATEMENT,
4025 p_module => C_MODULE,
4026 p_msg_text => 'The Calendar Period Calendar ID is '
4027 ||to_char(l_cal_per_calendar_id)
4028 ||'and the Calendar Period Dimension Group ID is '
4029 ||to_char(l_cal_per_dim_grp_id));
4030 END IF;
4031 EXCEPTION
4032 WHEN no_data_found THEN
4033 FEM_ENGINES_PKG.TECH_MESSAGE(
4034 p_severity => G_LOG_LEVEL_STATEMENT,
4035 p_module => C_MODULE,
4036 p_msg_text => 'Calendar Period ID '||to_char(p_cal_period_id)
4037 ||' either cannot be found or is not enabled or is personal.');
4038 RAISE e_inv_cal_period;
4039 END;
4040
4041 -- Make sure the calendar period and ledger share the same calendar
4042 IF l_cal_per_calendar_id <> p_ledger_calendar_id THEN
4043 RAISE e_mismatch_calendar;
4044 END IF;
4045
4046 BEGIN
4047 SELECT dimension_group_display_code
4048 INTO x_calp_dim_grp_dc
4049 FROM fem_dimension_grps_b
4050 WHERE dimension_group_id = l_cal_per_dim_grp_id;
4051
4052 IF G_LOG_LEVEL_STATEMENT >= g_log_current_level THEN
4053 FEM_ENGINES_PKG.Tech_Message
4054 (p_severity => G_LOG_LEVEL_STATEMENT,
4055 p_module => C_MODULE,
4056 p_msg_text => 'The Calendar Period Dimension Group Display Code is '
4057 ||x_calp_dim_grp_dc);
4058 END IF;
4059
4060 EXCEPTION
4061 WHEN no_data_found THEN
4062 FEM_ENGINES_PKG.Tech_Message
4063 (p_severity => G_LOG_LEVEL_STATEMENT,
4064 p_module => C_MODULE,
4065 p_msg_text => 'The Calendar Period Dimension Group cannot be found.');
4066
4067 RAISE e_inv_cal_period;
4068 END;
4069
4070 --
4071 -- Get the Dimension ID for Ledger.
4072 -- If this returns no data, this is an unexpected error
4073 -- and hence is not caught until the end of the procedure.
4074 --
4075
4076 SELECT dimension_id
4077 INTO l_cal_per_dim_id
4078 FROM fem_dimensions_b
4079 WHERE dimension_varchar_label = 'CAL_PERIOD';
4080
4081 IF G_LOG_LEVEL_STATEMENT >= g_log_current_level THEN
4082 FEM_ENGINES_PKG.TECH_MESSAGE(
4083 p_severity => G_LOG_LEVEL_STATEMENT,
4084 p_module => C_MODULE,
4085 p_msg_text => 'Dimension ID of Calendar Period dimenions is '
4086 ||to_char(l_cal_per_dim_id));
4087 END IF;
4088
4089 -- Retrieve the CAL_PERIOD_END_DATE attribute of the Cal Period ID
4090 -- and set it into a package variable.
4091 FEM_DIMENSION_UTIL_PKG.get_dim_attr_id_ver_id
4092 (p_dim_id => l_cal_per_dim_id,
4093 p_attr_label => 'CAL_PERIOD_END_DATE',
4094 x_attr_id => l_dim_attr_id,
4095 x_ver_id => l_dim_attr_ver_id,
4096 x_err_code => l_return_code);
4097
4098 IF G_LOG_LEVEL_STATEMENT >= g_log_current_level THEN
4099 FEM_ENGINES_PKG.TECH_MESSAGE(
4100 p_severity => G_LOG_LEVEL_STATEMENT,
4101 p_module => C_MODULE,
4102 p_msg_text => 'FEM_DIMENSION_UTIL_PKG.get_dim_attr_id_ver_id'
4103 ||'(CAL_PERIOD_END_DATE) returned with error code of '
4104 ||to_char(l_return_code));
4105 END IF;
4106
4107 IF l_return_code <> 0 THEN -- did not succeed
4108 RAISE e_inv_cal_period;
4109 END IF;
4110
4111 BEGIN
4112 SELECT date_assign_value
4113 INTO x_cal_per_end_date
4114 FROM fem_cal_periods_attr
4115 WHERE attribute_id = l_dim_attr_id
4116 AND version_id = l_dim_attr_ver_id
4117 AND cal_period_id = p_cal_period_id;
4118
4119 IF G_LOG_LEVEL_STATEMENT >= g_log_current_level THEN
4120 FEM_ENGINES_PKG.Tech_Message
4121 (p_severity => G_LOG_LEVEL_STATEMENT,
4122 p_module => C_MODULE,
4123 p_msg_text => 'The Calendar Period End Date is '
4124 ||FND_DATE.date_to_displayDT(x_cal_per_end_date));
4125 END IF;
4126 EXCEPTION
4127 WHEN no_data_found THEN
4128 FEM_ENGINES_PKG.TECH_MESSAGE(
4129 p_severity => G_LOG_LEVEL_STATEMENT,
4130 p_module => C_MODULE,
4131 p_msg_text => 'Calendar Period is missing Calendar Period End Date attribute.');
4132
4133 RAISE e_inv_cal_period;
4134 END;
4135
4136 -- Get the Calendar Period Number from the GL_PERIOD_NUM attribute of the
4137 -- Cal Period ID and set it into a package variable.
4138 FEM_DIMENSION_UTIL_PKG.get_dim_attr_id_ver_id
4139 (p_dim_id => l_cal_per_dim_id,
4140 p_attr_label => 'GL_PERIOD_NUM',
4141 x_attr_id => l_dim_attr_id,
4142 x_ver_id => l_dim_attr_ver_id,
4143 x_err_code => l_return_code);
4144
4145 IF G_LOG_LEVEL_STATEMENT >= g_log_current_level THEN
4146 FEM_ENGINES_PKG.TECH_MESSAGE(
4147 p_severity => G_LOG_LEVEL_STATEMENT,
4148 p_module => C_MODULE,
4149 p_msg_text => 'FEM_DIMENSION_UTIL_PKG.get_dim_attr_id_ver_id'
4150 ||'(CGL_PERIOD_NUM) returned with error code of '
4151 ||to_char(l_return_code));
4152 END IF;
4153
4154 IF l_return_code <> 0 THEN -- did not succeed
4155 RAISE e_inv_cal_period;
4156 END IF;
4157
4158 BEGIN
4159 SELECT number_assign_value
4160 INTO x_cal_per_number
4161 FROM fem_cal_periods_attr
4162 WHERE attribute_id = l_dim_attr_id
4163 AND version_id = l_dim_attr_ver_id
4164 AND cal_period_id = p_cal_period_id;
4165
4166 IF G_LOG_LEVEL_STATEMENT >= g_log_current_level THEN
4167 FEM_ENGINES_PKG.Tech_Message
4168 (p_severity => G_LOG_LEVEL_STATEMENT,
4169 p_module => C_MODULE,
4170 p_msg_text => 'The Calendar Period Number is '
4171 ||to_char(x_cal_per_number));
4172 END IF;
4173 EXCEPTION
4174 WHEN no_data_found THEN
4175 FEM_ENGINES_PKG.TECH_MESSAGE(
4176 p_severity => G_LOG_LEVEL_STATEMENT,
4177 p_module => C_MODULE,
4178 p_msg_text => 'Calendar Period is missing Calendar Period Number attribute.');
4179
4180 RAISE e_inv_cal_period;
4181 END;
4182
4183 x_return_status := G_RET_STS_SUCCESS;
4184
4185 FND_MSG_PUB.Count_And_Get
4186 (p_count => x_msg_count,
4187 p_data => x_msg_data);
4188
4189 IF G_LOG_LEVEL_STATEMENT >= g_log_current_level THEN
4190 FEM_ENGINES_PKG.TECH_MESSAGE(
4191 p_severity => G_LOG_LEVEL_STATEMENT,
4192 p_module => C_MODULE,
4193 p_msg_text => 'Returning with status of '||x_return_status);
4194 END IF;
4195 IF G_LOG_LEVEL_PROCEDURE >= g_log_current_level THEN
4196 FEM_ENGINES_PKG.TECH_MESSAGE(
4197 p_severity => G_LOG_LEVEL_PROCEDURE,
4198 p_module => C_MODULE,
4199 p_msg_text => 'End Procedure');
4200 END IF;
4201
4202 EXCEPTION
4203 WHEN e_inv_cal_period THEN
4204 x_return_status := G_RET_STS_ERROR;
4205
4206 FEM_ENGINES_PKG.put_message(
4207 p_app_name =>'FEM',
4208 p_msg_name =>'FEM_SD_LDR_INV_CAL_PER',
4209 p_token1 => 'CAL_PER',
4210 p_value1 => p_cal_period_id);
4211
4212 FEM_ENGINES_PKG.Tech_Message
4213 (p_severity => G_LOG_LEVEL_ERROR,
4214 p_module => C_MODULE,
4215 p_app_name => 'FEM',
4216 p_msg_name =>'FEM_SD_LDR_INV_CAL_PER',
4217 p_token1 => 'CAL_PER',
4218 p_value1 => p_cal_period_id);
4219
4220 FND_MSG_PUB.Count_And_Get
4221 (p_count => x_msg_count,
4222 p_data => x_msg_data);
4223
4224 WHEN e_mismatch_calendar THEN
4225 x_return_status := G_RET_STS_ERROR;
4226
4227 FEM_ENGINES_PKG.put_message(
4228 p_app_name =>'FEM',
4229 p_msg_name =>'FEM_SD_LDR_MISMATCH_CALENDAR',
4230 p_token1 => 'LEDGER',
4231 p_value1 => p_ledger_id,
4232 p_token2 => 'CAL_PER',
4233 p_value2 => p_cal_period_id);
4234
4235 FEM_ENGINES_PKG.Tech_Message
4236 (p_severity => G_LOG_LEVEL_ERROR,
4237 p_module => C_MODULE,
4238 p_app_name => 'FEM',
4239 p_msg_name => 'FEM_SD_LDR_MISMATCH_CALENDAR',
4240 p_token1 => 'LEDGER',
4241 p_value1 => p_ledger_id,
4242 p_token2 => 'CAL_PER',
4243 p_value2 => p_cal_period_id);
4244
4245 FND_MSG_PUB.Count_And_Get
4246 (p_count => x_msg_count,
4247 p_data => x_msg_data);
4248
4249 WHEN others THEN
4250 x_return_status := G_RET_STS_UNEXP_ERROR;
4251
4252 END Validate_Cal_Period;
4253
4254 --
4255 -- Procedure
4256 -- Validate_Dataset
4257 -- Purpose
4258 -- Validates the dataset code
4259 -- History
4260 -- 09-07-04 GCHENG Created
4261 -- Arguments
4262 -- p_api_version : API version
4263 -- p_object_type : Object type code
4264 -- p_datset_code : Dataset code
4265 -- x_dateset_dc : Dataset display code
4266 -- x_msg_count : Message count
4267 -- x_msg_data : Message text (if msg count = 1)
4268 -- x_return_status : Return status
4269 -- (look at FND_API for the possible statuses)
4270 -- Notes
4271 --
4272 PROCEDURE Validate_Dataset (
4273 p_api_version IN NUMBER,
4274 p_object_type IN VARCHAR2,
4275 p_dataset_code IN NUMBER,
4276 x_dataset_dc OUT NOCOPY VARCHAR2,
4277 x_msg_count OUT NOCOPY NUMBER,
4278 x_msg_data OUT NOCOPY VARCHAR2,
4279 x_return_status OUT NOCOPY VARCHAR2
4280 ) IS
4281 C_MODULE CONSTANT FND_LOG_MESSAGES.module%TYPE :=
4282 'fem.plsql.fem_source_data_loader_pkg.validate_dataset';
4283 C_API_VERSION CONSTANT NUMBER := 1.0;
4284 C_API_NAME CONSTANT VARCHAR2(30) := 'Validate_Dataset';
4285 l_dataset_dim_id NUMBER;
4286 l_dim_attr_id NUMBER;
4287 l_dim_attr_ver_id NUMBER;
4288 l_return_code NUMBER; -- 0 if success, 2 if error
4289 BEGIN
4290
4291 IF G_LOG_LEVEL_PROCEDURE >= g_log_current_level THEN
4292 FEM_ENGINES_PKG.TECH_MESSAGE(
4293 p_severity => G_LOG_LEVEL_PROCEDURE,
4294 p_module => C_MODULE,
4295 p_msg_text => 'Begin Procedure');
4296 END IF;
4297 IF G_LOG_LEVEL_STATEMENT >= g_log_current_level THEN
4298 FEM_ENGINES_PKG.TECH_MESSAGE(
4299 p_severity => G_LOG_LEVEL_STATEMENT,
4300 p_module => C_MODULE,
4301 p_msg_text => 'Parameter: Object type code is '||p_object_type);
4302 FEM_ENGINES_PKG.TECH_MESSAGE(
4303 p_severity => G_LOG_LEVEL_STATEMENT,
4304 p_module => C_MODULE,
4305 p_msg_text => 'Parameter: Dataset code is '||p_dataset_code);
4306 END IF;
4307
4308 -- Check for call compatibility.
4309 IF NOT FND_API.Compatible_API_Call (C_API_VERSION,
4310 p_api_version,
4311 C_API_NAME,
4312 G_PKG_NAME)
4313 THEN
4314 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4315 END IF;
4316
4317 x_return_status := G_RET_STS_SUCCESS;
4318
4319 --
4320 -- Check to make sure dataset code that is enabled and not personal exists
4321 --
4322
4323 BEGIN
4324 SELECT dataset_display_code
4325 INTO x_dataset_dc
4326 FROM fem_datasets_b
4327 WHERE dataset_code = p_dataset_code
4328 AND enabled_flag = 'Y'
4329 AND personal_flag = 'N';
4330 EXCEPTION
4331 WHEN no_data_found THEN
4332 x_return_status := G_RET_STS_ERROR;
4333
4334 FEM_ENGINES_PKG.put_message(
4335 p_app_name =>'FEM',
4336 p_msg_name =>'FEM_SD_LDR_INV_DATASET',
4337 p_token1 => 'DATASET',
4338 p_value1 => p_dataset_code);
4339
4340 FEM_ENGINES_PKG.Tech_Message
4341 (p_severity => G_LOG_LEVEL_ERROR,
4342 p_module => C_MODULE,
4343 p_app_name => 'FEM',
4344 p_msg_name => 'FEM_SD_LDR_INV_DATASET',
4345 p_token1 => 'DATASET',
4346 p_value1 => p_dataset_code);
4347 END;
4348
4349 --
4350 -- Make sure Dataset has a Balance Type attribute value.
4351 -- Unless users go "under the hood" this should never fail.
4352 --
4353 IF x_return_status = G_RET_STS_SUCCESS THEN
4354 -- If this returns no data, this is an unexpected error
4355 -- and hence is not caught until the end of the procedure.
4356 SELECT dimension_id
4357 INTO l_dataset_dim_id
4358 FROM fem_dimensions_b
4359 WHERE dimension_varchar_label = 'DATASET';
4360
4361 IF G_LOG_LEVEL_STATEMENT >= g_log_current_level THEN
4362 FEM_ENGINES_PKG.TECH_MESSAGE(
4363 p_severity => G_LOG_LEVEL_STATEMENT,
4364 p_module => C_MODULE,
4365 p_msg_text => 'Dimension ID of Dataset dimenions is '
4366 ||to_char(l_dataset_dim_id));
4367 END IF;
4368
4369 FEM_DIMENSION_UTIL_PKG.get_dim_attr_id_ver_id
4370 (p_dim_id => l_dataset_dim_id,
4371 p_attr_label => 'DATASET_BALANCE_TYPE_CODE',
4372 x_attr_id => l_dim_attr_id,
4373 x_ver_id => l_dim_attr_ver_id,
4374 x_err_code => l_return_code);
4375
4376 IF G_LOG_LEVEL_STATEMENT >= g_log_current_level THEN
4377 FEM_ENGINES_PKG.TECH_MESSAGE(
4378 p_severity => G_LOG_LEVEL_STATEMENT,
4379 p_module => C_MODULE,
4380 p_msg_text => 'FEM_DIMENSION_UTIL_PKG.get_dim_attr_id_ver_id'
4381 ||'(DATASET_BALANCE_TYPE_CODE) returned with error code of '
4382 ||to_char(l_return_code));
4383 END IF;
4384
4385 IF l_return_code = 0 THEN
4386 BEGIN
4387 SELECT 0
4388 INTO l_return_code
4389 FROM fem_datasets_attr
4390 WHERE attribute_id = l_dim_attr_id
4391 AND version_id = l_dim_attr_ver_id
4392 AND dataset_code = p_dataset_code;
4393 EXCEPTION
4394 WHEN no_data_found THEN
4395 l_return_code := 2; -- error
4396
4397 FEM_ENGINES_PKG.TECH_MESSAGE(
4398 p_severity => G_LOG_LEVEL_STATEMENT,
4399 p_module => C_MODULE,
4400 p_msg_text => 'Dataset is missing a balance type attribute.');
4401 END;
4402 END IF;
4403
4404 IF l_return_code <> 0 THEN
4405 x_return_status := G_RET_STS_ERROR;
4406
4407 FEM_ENGINES_PKG.put_message(
4408 p_app_name =>'FEM',
4409 p_msg_name =>'FEM_SD_LDR_INV_BAL_TYPE',
4410 p_token1 => 'DATASET',
4411 p_value1 => p_dataset_code);
4412
4413 FEM_ENGINES_PKG.Tech_Message
4414 (p_severity => G_LOG_LEVEL_ERROR,
4415 p_module => C_MODULE,
4416 p_app_name => 'FEM',
4417 p_msg_name => 'FEM_SD_LDR_INV_BAL_TYPE',
4418 p_token1 => 'DATASET',
4419 p_value1 => p_dataset_code);
4420 END IF;
4421 END IF;
4422
4423
4424 FND_MSG_PUB.Count_And_Get
4425 (p_count => x_msg_count,
4426 p_data => x_msg_data);
4427
4428 IF G_LOG_LEVEL_STATEMENT >= g_log_current_level THEN
4429 FEM_ENGINES_PKG.TECH_MESSAGE(
4430 p_severity => G_LOG_LEVEL_STATEMENT,
4431 p_module => C_MODULE,
4432 p_msg_text => 'Returning with status of '||x_return_status);
4433 END IF;
4434 IF G_LOG_LEVEL_PROCEDURE >= g_log_current_level THEN
4435 FEM_ENGINES_PKG.TECH_MESSAGE(
4436 p_severity => G_LOG_LEVEL_PROCEDURE,
4437 p_module => C_MODULE,
4438 p_msg_text => 'End Procedure');
4439 END IF;
4440
4441 EXCEPTION
4442 WHEN others THEN
4443 x_return_status := G_RET_STS_UNEXP_ERROR;
4444
4445 END Validate_Dataset;
4446
4447 --
4448 -- Procedure
4449 -- Validate_Source_System
4450 -- Purpose
4451 -- Validates the source system code
4452 -- History
4453 -- 09-07-04 GCHENG Created
4454 -- Arguments
4455 -- p_api_version : API version
4456 -- p_object_type : Object type code
4457 -- p_source_system_code : Source system code
4458 -- x_source_system_dc : Source system display code
4459 -- x_msg_count : Message count
4460 -- x_msg_data : Message text (if msg count = 1)
4461 -- x_return_status : Return status
4462 -- (look at FND_API for the possible statuses)
4463 -- Notes
4464 --
4465 PROCEDURE Validate_Source_System (
4466 p_api_version IN NUMBER,
4467 p_object_type IN VARCHAR2,
4468 p_source_system_code IN NUMBER,
4469 x_source_system_dc OUT NOCOPY VARCHAR2,
4470 x_msg_count OUT NOCOPY NUMBER,
4471 x_msg_data OUT NOCOPY VARCHAR2,
4472 x_return_status OUT NOCOPY VARCHAR2
4473 ) IS
4474 C_MODULE CONSTANT FND_LOG_MESSAGES.module%TYPE :=
4475 'fem.plsql.fem_source_data_loader_pkg.validate_source_system';
4476 C_API_VERSION CONSTANT NUMBER := 1.0;
4477 C_API_NAME CONSTANT VARCHAR2(30) := 'Validate_Source_System';
4478 BEGIN
4479
4480 IF G_LOG_LEVEL_PROCEDURE >= g_log_current_level THEN
4481 FEM_ENGINES_PKG.TECH_MESSAGE(
4482 p_severity => G_LOG_LEVEL_PROCEDURE,
4483 p_module => C_MODULE,
4484 p_msg_text => 'Begin Procedure');
4485 END IF;
4486 IF G_LOG_LEVEL_STATEMENT >= g_log_current_level THEN
4487 FEM_ENGINES_PKG.TECH_MESSAGE(
4488 p_severity => G_LOG_LEVEL_STATEMENT,
4489 p_module => C_MODULE,
4490 p_msg_text => 'Parameter: Object type code is '||p_object_type);
4491 FEM_ENGINES_PKG.TECH_MESSAGE(
4492 p_severity => G_LOG_LEVEL_STATEMENT,
4493 p_module => C_MODULE,
4494 p_msg_text => 'Parameter: Source system code is '||p_source_system_code);
4495 END IF;
4496
4497 -- Check for call compatibility.
4498 IF NOT FND_API.Compatible_API_Call (C_API_VERSION,
4499 p_api_version,
4500 C_API_NAME,
4501 G_PKG_NAME)
4502 THEN
4503 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4504 END IF;
4505
4506 x_return_status := G_RET_STS_SUCCESS;
4507
4508 BEGIN
4509 SELECT source_system_display_code
4510 INTO x_source_system_dc
4511 FROM fem_source_systems_b
4512 WHERE source_system_code = p_source_system_code
4513 AND enabled_flag = 'Y'
4514 AND personal_flag = 'N';
4515 EXCEPTION
4516 WHEN no_data_found THEN
4517 x_return_status := G_RET_STS_ERROR;
4518
4519 FEM_ENGINES_PKG.put_message(
4520 p_app_name =>'FEM',
4521 p_msg_name =>'FEM_SD_LDR_INV_SOURCE',
4522 p_token1 => 'SOURCE',
4523 p_value1 => p_source_system_code);
4524
4525 FEM_ENGINES_PKG.Tech_Message
4526 (p_severity => G_LOG_LEVEL_ERROR,
4527 p_module => C_MODULE,
4528 p_app_name => 'FEM',
4529 p_msg_name => 'FEM_SD_LDR_INV_SOURCE',
4530 p_token1 => 'SOURCE',
4531 p_value1 => p_source_system_code);
4532 END;
4533
4534 FND_MSG_PUB.Count_And_Get
4535 (p_count => x_msg_count,
4536 p_data => x_msg_data);
4537
4538 IF G_LOG_LEVEL_STATEMENT >= g_log_current_level THEN
4539 FEM_ENGINES_PKG.TECH_MESSAGE(
4540 p_severity => G_LOG_LEVEL_STATEMENT,
4541 p_module => C_MODULE,
4542 p_msg_text => 'Returning with status of '||x_return_status);
4543 END IF;
4544 IF G_LOG_LEVEL_PROCEDURE >= g_log_current_level THEN
4545 FEM_ENGINES_PKG.TECH_MESSAGE(
4546 p_severity => G_LOG_LEVEL_PROCEDURE,
4547 p_module => C_MODULE,
4548 p_msg_text => 'End Procedure');
4549 END IF;
4550
4551 EXCEPTION
4552 WHEN others THEN
4553 x_return_status := G_RET_STS_UNEXP_ERROR;
4554
4555 END Validate_Source_System;
4556
4557 END FEM_SOURCE_DATA_LOADER_PKG;