[Home] [Help]
PACKAGE BODY: APPS.FEM_DATAX_LOADER_PKG
Source
1 PACKAGE BODY Fem_DataX_Loader_Pkg AS
2 -- $Header: fem_data_loader.plb 120.2 2005/08/23 12:13:04 appldev ship $
3
4 ----------------------------------------------------
5 -- Dev Modes: 'D'=development 'P'=production
6 ----------------------------------------------------
7 c_dev_mode CONSTANT VARCHAR2(2) := 'P';
8
9 -----------------------
10 -- Package Constants --
11 -----------------------
12 c_fetch_limit CONSTANT NUMBER := 10000; -- default
13
14 c_user_id CONSTANT NUMBER := FND_GLOBAL.User_ID;
15 c_login_id CONSTANT NUMBER := FND_GLOBAL.Login_ID;
16 c_conc_prg_id CONSTANT NUMBER := FND_GLOBAL.Conc_Program_ID;
17 c_prg_app_id CONSTANT NUMBER := FND_GLOBAL.Prog_Appl_ID;
18
19 c_fem_schema CONSTANT VARCHAR2(3) := 'FEM';
20
21 c_false CONSTANT VARCHAR2(1) := FND_API.G_FALSE;
22 c_true CONSTANT VARCHAR2(1) := FND_API.G_TRUE;
23 c_success CONSTANT VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
24 c_error CONSTANT VARCHAR2(1) := FND_API.G_RET_STS_ERROR;
25 c_unexp CONSTANT VARCHAR2(1) := FND_API.G_RET_STS_UNEXP_ERROR;
26 c_api_version CONSTANT NUMBER := 1.0;
27
28 c_log_level_1 CONSTANT NUMBER := fnd_log.level_statement;
29 c_log_level_2 CONSTANT NUMBER := fnd_log.level_procedure;
30 c_log_level_3 CONSTANT NUMBER := fnd_log.level_event;
31 c_log_level_4 CONSTANT NUMBER := fnd_log.level_exception;
32 c_log_level_5 CONSTANT NUMBER := fnd_log.level_error;
33 c_log_level_6 CONSTANT NUMBER := fnd_log.level_unexpected;
34
35 ---------------------
36 -- Package Globals --
37 ---------------------
38 g_rows_processed NUMBER;
39 g_rows_loaded NUMBER;
40 g_rows_rejected NUMBER;
41 g_exec_status VARCHAR2(80);
42 g_upd_pl_on_err VARCHAR2(1) := 'N';
43
44 f_set_status BOOLEAN;
45 g_req_id NUMBER;
46 g_obj_def_id NUMBER;
47 g_table_name VARCHAR2(30);
48 g_ledger_id NUMBER;
49 g_dataset_cd NUMBER;
50 g_cal_per_id NUMBER;
51 g_source_cd NUMBER;
52 g_exec_mode VARCHAR2(1);
53
54 g_object_id NUMBER;
55 g_data_table VARCHAR2(30);
56 g_data_t_table VARCHAR2(30);
57 g_gvc_id NUMBER;
58
59 g_cctr_org_sql VARCHAR2(4196);
60 g_fin_elem_sql VARCHAR2(4196);
61 g_product_sql VARCHAR2(4196);
62 g_nat_acct_sql VARCHAR2(4196);
63 g_channel_sql VARCHAR2(4196);
64 g_line_item_sql VARCHAR2(4196);
65 g_project_sql VARCHAR2(4196);
66 g_customer_sql VARCHAR2(4196);
67 g_entity_sql VARCHAR2(4196);
68 g_geography_sql VARCHAR2(4196);
69 g_task_sql VARCHAR2(4196);
70 g_interco_sql VARCHAR2(4196);
71 g_user_dim1_sql VARCHAR2(4196);
72 g_user_dim2_sql VARCHAR2(4196);
73 g_user_dim3_sql VARCHAR2(4196);
74 g_user_dim4_sql VARCHAR2(4196);
75 g_user_dim5_sql VARCHAR2(4196);
76 g_user_dim6_sql VARCHAR2(4196);
77 g_user_dim7_sql VARCHAR2(4196);
78 g_user_dim8_sql VARCHAR2(4196);
79 g_user_dim9_sql VARCHAR2(4196);
80 g_user_dim10_sql VARCHAR2(4196);
81
82 g_condition VARCHAR2(4196);
83 g_select_stmt VARCHAR2(32767);
84
85 g_phase_stat VARCHAR2(80);
86 g_message VARCHAR2(4196);
87
88 g_msg_no NUMBER;
89
90 g_block VARCHAR2(30);
91
92 ------------------------
93 -- Package Exceptions --
94 ------------------------
95 e_validation_error EXCEPTION;
96 e_process_lock_error EXCEPTION;
97
98 /***************************************************************************
99 ***************************************************************************
100 * *
101 * ================= *
102 * Master *
103 * ================= *
104 * *
105 ***************************************************************************
106 **************************************************************************/
107
108 PROCEDURE Master (
109 errbuf OUT NOCOPY VARCHAR2,
110 retcode OUT NOCOPY VARCHAR2,
111 p_exec_mode IN VARCHAR2,
112 p_obj_def_id IN NUMBER,
113 p_ledger_id IN NUMBER,
114 p_dataset_cd IN NUMBER,
115 p_source_cd IN NUMBER,
116 p_cal_per_id IN NUMBER
117 )
118 IS
119
120 ---------------------
121 -- Local Variables --
122 ---------------------
123 v_prg_stat VARCHAR2(80);
124 v_exception_code VARCHAR2(80);
125 v_status NUMBER;
126 v_message VARCHAR2(4000);
127
128 v_block CONSTANT VARCHAR2(80) :=
129 'fem.plsql.fem_datax_loader_pkg.master';
130
131 ---------------------
132 -- Execution Block --
133 ---------------------
134 BEGIN
135
136 g_block := 'Master';
137
138 FEM_ENGINES_PKG.TECH_MESSAGE
139 (p_severity => c_log_level_3,
140 p_module => v_block||'.Begin{100}',
141 p_msg_text => 'Begin FEM_DATAX_LOADER.Master');
142
143 g_req_id := FND_GLOBAL.Conc_Request_ID;
144 g_obj_def_id := p_obj_def_id;
145 g_table_name := null; -- := p_table_name (future use)
146 g_ledger_id := p_ledger_id;
147 g_dataset_cd := p_dataset_cd;
148 g_cal_per_id := p_cal_per_id;
149 g_source_cd := p_source_cd;
150 g_exec_mode := p_exec_mode;
151
152 FEM_ENGINES_PKG.TECH_MESSAGE
153 (p_severity => c_log_level_2,
154 p_module => v_block||'.g_req_id{101}',
155 p_msg_text => g_req_id);
156 FEM_ENGINES_PKG.TECH_MESSAGE
157 (p_severity => c_log_level_2,
158 p_module => v_block||'.c_user_id{102}',
159 p_msg_text => c_user_id);
160 FEM_ENGINES_PKG.TECH_MESSAGE
161 (p_severity => c_log_level_2,
162 p_module => v_block||'.g_obj_def_id{103}',
163 p_msg_text => g_obj_def_id);
164 FEM_ENGINES_PKG.TECH_MESSAGE
165 (p_severity => c_log_level_2,
166 p_module => v_block||'.g_ledger_id{104}',
167 p_msg_text => g_ledger_id);
168 FEM_ENGINES_PKG.TECH_MESSAGE
169 (p_severity => c_log_level_2,
170 p_module => v_block||'.g_dataset_cd{105}',
171 p_msg_text => g_dataset_cd);
172 FEM_ENGINES_PKG.TECH_MESSAGE
173 (p_severity => c_log_level_2,
174 p_module => v_block||'.g_cal_per_id{106}',
175 p_msg_text => g_cal_per_id);
176 FEM_ENGINES_PKG.TECH_MESSAGE
177 (p_severity => c_log_level_2,
178 p_module => v_block||'.g_source_cd{107}',
179 p_msg_text => g_source_cd);
180 FEM_ENGINES_PKG.TECH_MESSAGE
181 (p_severity => c_log_level_2,
182 p_module => v_block||'.g_exec_mode{108}',
183 p_msg_text => g_exec_mode);
184
185 -------------------------------
186 -- Call Validation Procedure --
187 -------------------------------
188 g_block := 'Validation';
189 Validation;
190
191 ---------------------------------
192 -- Call Registration Procedure --
193 ---------------------------------
194 g_block := 'Registration';
195 Registration;
196 COMMIT;
197
198 --------------------------------
199 -- Call Pre_Process Procedure --
200 --------------------------------
201 g_block := 'Pre_Process';
202 Pre_Process;
203
204 -- ------------------------------------------------
205 -- Call FEM_MP.Master to run Process_Rows Procedure
206 -- ------------------------------------------------
207 g_block := 'Process_Rows';
208
209 FEM_Multi_Proc_Pkg.Master(
210 x_prg_stat => v_prg_stat,
211 x_exception_code => v_exception_code,
212 p_rule_id => g_object_id,
213 p_eng_step => 'ALL',
214 p_data_table => g_data_t_table,
215 p_eng_sql => g_select_stmt,
216 p_eng_prg => 'FEM_DATAX_LOADER_PKG.Process_Rows',
217 p_condition => g_condition,
218 p_failed_req_id => null,
219 p_arg1 => g_data_table,
220 p_arg2 => g_object_id,
221 p_arg3 => g_ledger_id,
222 p_arg4 => g_dataset_cd,
223 p_arg5 => g_cal_per_id,
224 p_arg6 => g_source_cd,
225 p_arg7 => g_exec_mode,
226 p_arg8 => g_req_id,
227 p_arg9 => g_cctr_org_sql,
228 p_arg10 => g_fin_elem_sql,
229 p_arg11 => g_product_sql,
230 p_arg12 => g_nat_acct_sql,
231 p_arg13 => g_channel_sql,
232 p_arg14 => g_line_item_sql,
233 p_arg15 => g_project_sql,
234 p_arg16 => g_customer_sql,
235 p_arg17 => g_entity_sql,
236 p_arg18 => g_geography_sql,
237 p_arg19 => g_task_sql,
238 p_arg20 => g_interco_sql,
239 p_arg21 => g_user_dim1_sql,
240 p_arg22 => g_user_dim2_sql,
241 p_arg23 => g_user_dim3_sql,
242 p_arg24 => g_user_dim4_sql,
243 p_arg25 => g_user_dim5_sql,
244 p_arg26 => g_user_dim6_sql,
245 p_arg27 => g_user_dim7_sql,
246 p_arg28 => g_user_dim8_sql,
247 p_arg29 => g_user_dim9_sql,
248 p_arg30 => g_user_dim10_sql);
249
250 FEM_ENGINES_PKG.TECH_MESSAGE
251 (p_severity => c_log_level_2,
252 p_module => v_block||'.v_prg_stat{109}',
253 p_msg_text => v_prg_stat);
254 FEM_ENGINES_PKG.TECH_MESSAGE
255 (p_severity => c_log_level_2,
256 p_module => v_block||'.v_exception_code{110}',
257 p_msg_text => v_exception_code);
258
259 IF (v_exception_code = 'FEM_MP_NO_DATA_SLICES_ERR')
260 THEN
261
262 FEM_ENGINES_PKG.PUT_MESSAGE
263 (p_app_name => 'FEM',
264 p_msg_name => 'FEM_DATAX_LDR_NULL_SLICES_ERR',
265 p_token1 => 'TABLE',
266 p_value1 => g_data_t_table);
267 g_message := FND_MSG_PUB.GET(p_encoded => c_false);
268
269 FEM_ENGINES_PKG.TECH_MESSAGE
270 (p_severity => c_log_level_2,
271 p_module => v_block||'.null_slices{111}',
272 p_msg_text => g_message);
273 FEM_ENGINES_PKG.USER_MESSAGE
274 (p_msg_text => g_message);
275 END IF;
276
277 SELECT NVL(SUM(rows_processed),0),
278 NVL(SUM(rows_loaded),0),
279 NVL(SUM(rows_rejected),0)
280 INTO g_rows_processed,
281 g_rows_loaded,
282 g_rows_rejected
283 FROM fem_mp_process_ctl_t
284 WHERE req_id = g_req_id;
285
286 IF (v_prg_stat = 'COMPLETE:NORMAL')
287 THEN
288 IF (g_rows_rejected = 0)
289 THEN
290 v_status := 0;
291 g_exec_status := 'SUCCESS';
292 ELSE
293 v_status := 1;
294 g_exec_status := 'ERROR_RERUN';
295 END IF;
296 ELSIF (v_prg_stat = 'COMPLETE:WARNING')
297 THEN
298 v_status := 1;
299 g_exec_status := 'ERROR_RERUN';
300 f_set_status := FND_CONCURRENT.SET_COMPLETION_STATUS('WARNING',null);
301 ELSE
302 v_status := 2;
303 g_exec_status := 'ERROR_RERUN';
304 f_set_status := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',null);
305 END IF;
306
307 IF (c_dev_mode = 'P') AND
308 (v_status IN (0,1))
309 THEN
310 FEM_Multi_Proc_Pkg.Delete_Data_Slices(
311 p_req_id => g_req_id);
312 END IF;
313
314 FEM_ENGINES_PKG.TECH_MESSAGE
315 (p_severity => c_log_level_2,
316 p_module => v_block||'.g_rows_processed{112}',
317 p_msg_text => g_rows_processed);
318 FEM_ENGINES_PKG.TECH_MESSAGE
319 (p_severity => c_log_level_2,
320 p_module => v_block||'.g_rows_loaded{113}',
321 p_msg_text => g_rows_loaded);
322 FEM_ENGINES_PKG.TECH_MESSAGE
323 (p_severity => c_log_level_2,
324 p_module => v_block||'.g_rows_rejected{114}',
325 p_msg_text => g_rows_rejected);
326 FEM_ENGINES_PKG.TECH_MESSAGE
327 (p_severity => c_log_level_2,
328 p_module => v_block||'.g_exec_status{115}',
329 p_msg_text => g_exec_status);
330
331 g_upd_pl_on_err := 'N';
332
333 ---------------------------------
334 -- Call Post_Process Procedure --
335 ---------------------------------
336 g_block := 'Post_Process';
337 Post_Process;
338
339 g_block := 'Master';
340
341 -------------------
342 -- Post Messages --
343 -------------------
344 IF (g_exec_status = 'SUCCESS')
345 THEN
346 FEM_ENGINES_PKG.PUT_MESSAGE
347 (p_app_name => 'FEM',
348 p_msg_name => 'FEM_EXEC_SUCCESS');
349 ELSE
350 FEM_ENGINES_PKG.PUT_MESSAGE
351 (p_app_name => 'FEM',
352 p_msg_name => 'FEM_EXEC_RERUN');
353 END IF;
354
355 g_message := FND_MSG_PUB.GET(p_encoded => c_false);
356
357 FEM_ENGINES_PKG.TECH_MESSAGE
358 (p_severity => c_log_level_2,
359 p_module => v_block||'.exec_status{120}',
360 p_msg_text => g_message);
361 FEM_ENGINES_PKG.USER_MESSAGE
362 (p_msg_text => g_message);
363
364 IF (g_rows_rejected > 0)
365 THEN
366 FEM_ENGINES_PKG.PUT_MESSAGE
367 (p_app_name => 'FEM',
368 p_msg_name => 'FEM_DATAX_LDR_BAD_DATA_ERR',
369 p_token1 => 'COUNT',
370 p_value1 => g_rows_rejected);
371 g_message := FND_MSG_PUB.GET(p_encoded => c_false);
372
373 FEM_ENGINES_PKG.TECH_MESSAGE
374 (p_severity => c_log_level_2,
375 p_module => v_block||'.exec_status{121}',
376 p_msg_text => g_message);
380 f_set_status := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',null);
377 FEM_ENGINES_PKG.USER_MESSAGE
378 (p_msg_text => g_message);
379
381
382 END IF;
383
384 FEM_ENGINES_PKG.TECH_MESSAGE
385 (p_severity => c_log_level_3,
386 p_module => v_block||'.End{130}',
387 p_msg_text => 'End FEM_DATAX_LOADER.Master');
388
389 ---------------------
390 -- Exception Block --
391 ---------------------
392 EXCEPTION
393
394 WHEN e_process_lock_error THEN
395
396 ------- Clean-up -------
397 g_exec_status := 'ERROR_RERUN';
398
399 FEM_ENGINES_PKG.TECH_MESSAGE
400 (p_severity => c_log_level_2,
401 p_module => v_block||'.g_upd_pl_on_err{190}',
402 p_msg_text => g_upd_pl_on_err);
403
404 IF (g_upd_pl_on_err = 'Y')
405 THEN
406 g_upd_pl_on_err := 'N';
407 Post_Process;
408 END IF;
409
410 ------- Post Error -------
411 FEM_ENGINES_PKG.PUT_MESSAGE
412 (p_app_name => 'FEM',
413 p_msg_name => 'FEM_DATAX_LDR_PROCESS_LOCK_ERR',
414 p_token1 => 'BLOCK',
415 p_value1 => g_block);
416
417 g_message := FND_MSG_PUB.GET(p_encoded => c_false);
418
419 FEM_ENGINES_PKG.TECH_MESSAGE
420 (p_severity => c_log_level_5,
421 p_module => v_block||'.Exception{191}',
422 p_msg_text => g_message);
423
424 FEM_ENGINES_PKG.USER_MESSAGE
425 (p_msg_text => g_message);
426
427 ------- Post Status -------
428 FEM_ENGINES_PKG.PUT_MESSAGE
429 (p_app_name => 'FEM',
430 p_msg_name => 'FEM_EXEC_RERUN');
431
432 g_message := FND_MSG_PUB.GET(p_encoded => c_false);
433
434 FEM_ENGINES_PKG.TECH_MESSAGE
435 (p_severity => c_log_level_2,
436 p_module => v_block||'.Process_Lock_Error{192}',
437 p_msg_text => g_message);
438
439 FEM_ENGINES_PKG.USER_MESSAGE
440 (p_msg_text => g_message);
441
442 f_set_status := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',null);
443
444 WHEN e_validation_error THEN
445
446 ------- Clean-up -------
447 g_exec_status := 'ERROR_RERUN';
448
449 FEM_ENGINES_PKG.TECH_MESSAGE
450 (p_severity => c_log_level_2,
451 p_module => v_block||'.g_upd_pl_on_err{190}',
452 p_msg_text => g_upd_pl_on_err);
453
454 IF (g_upd_pl_on_err = 'Y')
455 THEN
456 g_upd_pl_on_err := 'N';
457 Post_Process;
458 END IF;
459
460 ------- Post Error -------
461 FEM_ENGINES_PKG.TECH_MESSAGE
462 (p_severity => c_log_level_5,
463 p_module => v_block||'.Exception{'||g_msg_no||'}',
464 p_msg_text => g_message);
465
466 FEM_ENGINES_PKG.USER_MESSAGE
467 (p_msg_text => g_message);
468
469 ------- Post Status -------
470 FEM_ENGINES_PKG.PUT_MESSAGE
471 (p_app_name => 'FEM',
472 p_msg_name => 'FEM_EXEC_RERUN');
473
474 g_message := FND_MSG_PUB.GET(p_encoded => c_false);
475
476 FEM_ENGINES_PKG.TECH_MESSAGE
477 (p_severity => c_log_level_2,
478 p_module => v_block||'.Validation_Error{192}',
479 p_msg_text => g_message);
480
481 FEM_ENGINES_PKG.USER_MESSAGE
482 (p_msg_text => g_message);
483
484 f_set_status := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',null);
485
486 WHEN others THEN
487 g_message := sqlerrm;
488
489 ------- Clean-up -------
490 g_exec_status := 'ERROR_RERUN';
491
492 FEM_ENGINES_PKG.TECH_MESSAGE
493 (p_severity => c_log_level_2,
494 p_module => v_block||'.g_upd_pl_on_err{190}',
495 p_msg_text => g_upd_pl_on_err);
496
497 IF (g_upd_pl_on_err = 'Y')
498 THEN
499 g_upd_pl_on_err := 'N';
500 Post_Process;
501 END IF;
502
503 ------- Post Error -------
504 FEM_ENGINES_PKG.PUT_MESSAGE
505 (p_app_name => 'FEM',
506 p_msg_name => 'FEM_UNEXPECTED_ERROR',
507 p_token1 => 'ERR_MSG',
508 p_value1 => g_message);
509
510 g_message := FND_MSG_PUB.GET(p_encoded => c_false);
511
512 FEM_ENGINES_PKG.TECH_MESSAGE
513 (p_severity => c_log_level_2,
514 p_module => v_block||'.Exception{194}',
515 p_msg_text => g_message);
516
517 FEM_ENGINES_PKG.USER_MESSAGE
518 (p_msg_text => g_message);
519
520 ------- Post Status -------
521 FEM_ENGINES_PKG.PUT_MESSAGE
522 (p_app_name => 'FEM',
523 p_msg_name => 'FEM_EXEC_RERUN');
524
525 g_message := FND_MSG_PUB.GET(p_encoded => c_false);
526
527 FEM_ENGINES_PKG.TECH_MESSAGE
528 (p_severity => c_log_level_2,
529 p_module => v_block||'.Unexpected_Error{195}',
530 p_msg_text => g_message);
531
532 FEM_ENGINES_PKG.USER_MESSAGE
533 (p_msg_text => g_message);
534
535 f_set_status := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',null);
536
537 END Master;
538
539
540 /***************************************************************************
544 * Validation *
541 ***************************************************************************
542 * *
543 * ================= *
545 * ================= *
546 * *
547 ***************************************************************************
548 **************************************************************************/
549
550 PROCEDURE Validation
551 IS
552
553 ---------------------
554 -- Local Variables --
555 ---------------------
556 v_proc_col VARCHAR2(30);
557 v_dc_col VARCHAR2(1024);
558 v_proc_cols VARCHAR2(1024);
559 v_uniq_idx VARCHAR2(30);
560 v_idx_col VARCHAR2(30);
561 v_proc_key_ok NUMBER;
562 v_pk_count NUMBER;
563 v_pk_add NUMBER := 0;
564 v_ui_count NUMBER;
565
566 v_ledger_dc VARCHAR2(150);
567 v_ledger_name VARCHAR2(150);
568 v_dataset_dc VARCHAR2(150);
569 v_dataset_name VARCHAR2(150);
570 v_source_dc VARCHAR2(150);
571 v_source_name VARCHAR2(150);
572
573 v_cal_per_end_date DATE;
574 v_dim_grp_cd VARCHAR2(150);
575 v_cal_per_num NUMBER;
576 v_cal_per_name VARCHAR2(150);
577 v_cal_period VARCHAR2(150);
578
579 v_count NUMBER;
580 v_member VARCHAR2(150);
581
582 v_return_status VARCHAR2(1);
583 v_msg_count NUMBER;
584
585 v_block CONSTANT VARCHAR2(80) :=
586 'fem.plsql.fem_datax_loader_pkg.validation';
587
588 ----------------------
589 -- Cursor Variables --
590 ----------------------
591 CURSOR c_proc_key IS
592 SELECT column_name
593 FROM fem_tab_column_prop
594 WHERE table_name = g_data_table
595 AND column_property_code = 'PROCESSING_KEY'
596 AND column_name not in ('CREATED_BY_OBJECT_ID')
597 ORDER BY column_name;
598
599 CURSOR c_uniq_idx IS
600 SELECT index_name
601 FROM all_indexes
602 WHERE owner = c_fem_schema
603 AND table_name = g_data_t_table
604 AND uniqueness = 'UNIQUE';
605
606 CURSOR c_idx_col IS
607 SELECT column_name
608 FROM all_ind_columns
609 WHERE index_owner = c_fem_schema
610 AND index_name = v_uniq_idx
611 ORDER BY column_position;
612
613 ----------------
614 -- Exceptions --
615 ----------------
616 e_bad_exec_mode EXCEPTION;
617 e_bad_obj_def EXCEPTION;
618 e_bad_target_table EXCEPTION;
619 e_no_proc_key EXCEPTION;
620 e_bad_proc_key EXCEPTION;
621 e_no_unq_idx EXCEPTION;
622 e_no_pk_match EXCEPTION;
623 e_bad_ledger_id EXCEPTION;
624 e_bad_ledger_attr EXCEPTION;
625 e_no_ledger_dc EXCEPTION;
626 e_bad_gvc_id EXCEPTION;
627 e_bad_dataset_cd EXCEPTION;
628 e_bad_dataset_attr EXCEPTION;
629 e_no_dataset_dc EXCEPTION;
630 e_bad_source_cd EXCEPTION;
631 e_no_source_dc EXCEPTION;
632 e_bad_cal_per_id EXCEPTION;
633 e_no_cal_period EXCEPTION;
634 e_no_data_match EXCEPTION;
635
636 BEGIN
637
638 FEM_ENGINES_PKG.TECH_MESSAGE
639 (p_severity => c_log_level_3,
640 p_module => v_block||'.Begin{200}',
641 p_msg_text => 'Begin FEM_DATAX_LOADER.Validation');
642
643 ----------------------------
644 -- Verify Processing Mode --
645 ----------------------------
646 CASE g_exec_mode
647 WHEN 'S' THEN NULL;
648 WHEN 'E' THEN NULL;
649 ELSE RAISE e_bad_exec_mode;
650 END CASE;
651
652 ------------------------------
653 -- Verify Object Definition --
654 ------------------------------
655 BEGIN
656 SELECT C.object_id
657 INTO g_object_id
658 FROM fem_object_catalog_vl C,
659 fem_object_definition_vl D
660 WHERE D.object_definition_id = g_obj_def_id
661 AND C.object_id = D.object_id
662 AND C.folder_id IN
663 (SELECT folder_id
664 FROM fem_user_folders
665 WHERE user_id = c_user_id);
666 EXCEPTION
667 WHEN no_data_found THEN
668 RAISE e_bad_obj_def;
669 END;
670
671 FEM_ENGINES_PKG.TECH_MESSAGE
672 (p_severity => c_log_level_2,
673 p_module => v_block||'.g_object_id{201}',
674 p_msg_text => g_object_id);
675
676 CASE g_object_id
677 WHEN 1301 THEN g_data_table := 'FEM_DATA1';
678 WHEN 1302 THEN g_data_table := 'FEM_DATA2';
679 WHEN 1303 THEN g_data_table := 'FEM_DATA3';
680 WHEN 1304 THEN g_data_table := 'FEM_DATA4';
681 WHEN 1305 THEN g_data_table := 'FEM_DATA5';
682 WHEN 1306 THEN g_data_table := 'FEM_DATA6';
683 WHEN 1307 THEN g_data_table := 'FEM_DATA7';
684 WHEN 1308 THEN g_data_table := 'FEM_DATA8';
685 WHEN 1309 THEN g_data_table := 'FEM_DATA9';
686 WHEN 1310 THEN g_data_table := 'FEM_DATA10';
687 WHEN 1311 THEN g_data_table := 'FEM_DATA11';
688 WHEN 1312 THEN g_data_table := 'FEM_DATA12';
692 WHEN 1316 THEN g_data_table := 'FEM_DATA16';
689 WHEN 1313 THEN g_data_table := 'FEM_DATA13';
690 WHEN 1314 THEN g_data_table := 'FEM_DATA14';
691 WHEN 1315 THEN g_data_table := 'FEM_DATA15';
693 WHEN 1317 THEN g_data_table := 'FEM_DATA17';
694 WHEN 1318 THEN g_data_table := 'FEM_DATA18';
695 WHEN 1319 THEN g_data_table := 'FEM_DATA19';
696 WHEN 1320 THEN g_data_table := 'FEM_DATA20';
697 ELSE RAISE e_bad_obj_def;
698 END CASE;
699
700 IF (g_table_name IS NOT NULL)
701 THEN
702 g_data_table := g_table_name;
703 END IF;
704
705 FEM_ENGINES_PKG.TECH_MESSAGE
706 (p_severity => c_log_level_2,
707 p_module => v_block||'.g_data_table{202}',
708 p_msg_text => g_data_table);
709
710 -------------------------
711 -- Verify Target Table --
712 -------------------------
713 SELECT COUNT(*)
714 INTO v_count
715 FROM fem_table_class_assignmt
716 WHERE table_name = g_data_table
717 AND table_classification_code = 'GENERIC_DATA_TABLE';
718
719 IF (v_count <> 1)
720 THEN
721 RAISE e_bad_target_table;
722 END IF;
723 g_data_t_table := g_data_table||'_T';
724
725 ---------------------------
726 -- Verify Processing Key --
727 ---------------------------
728 FOR r_proc_key IN c_proc_key
729 LOOP
730 v_proc_col := r_proc_key.column_name;
731
732 FEM_ENGINES_PKG.TECH_MESSAGE
733 (p_severity => c_log_level_1,
734 p_module => v_block||'.v_proc_col{204}',
735 p_msg_text => v_proc_col);
736
737 IF (v_proc_col = 'CAL_PERIOD_ID')
738 THEN
739 v_pk_add := 2;
740 v_dc_col := 'CALP_DIM_GRP_DISPLAY_CODE'','||
741 '''CAL_PERIOD_END_DATE'','||
742 '''CAL_PERIOD_NUMBER';
743 ELSE
744 CASE v_proc_col
745 WHEN 'DATASET_CODE' THEN
746 v_dc_col := 'DATASET_DISPLAY_CODE';
747 WHEN 'SOURCE_SYSTEM_CODE' THEN
748 v_dc_col := 'SOURCE_SYSTEM_DISPLAY_CODE';
749 WHEN 'LEDGER_ID' THEN
750 v_dc_col := 'LEDGER_DISPLAY_CODE';
751 WHEN 'COMPANY_COST_CENTER_ORG_ID' THEN
752 v_dc_col := 'CCTR_ORG_DISPLAY_CODE';
753 WHEN 'CURRENCY_CODE' THEN
754 v_dc_col := 'CURRENCY_CODE';
755 WHEN 'FINANCIAL_ELEM_ID' THEN
756 v_dc_col := 'FINANCIAL_ELEM_DISPLAY_CODE';
757 WHEN 'PRODUCT_ID' THEN
758 v_dc_col := 'PRODUCT_DISPLAY_CODE';
759 WHEN 'NATURAL_ACCOUNT_ID' THEN
760 v_dc_col := 'NATURAL_ACCOUNT_DISPLAY_CODE';
761 WHEN 'CHANNEL_ID' THEN
762 v_dc_col := 'CHANNEL_DISPLAY_CODE';
763 WHEN 'LINE_ITEM_ID' THEN
764 v_dc_col := 'LINE_ITEM_DISPLAY_CODE';
765 WHEN 'PROJECT_ID' THEN
766 v_dc_col := 'PROJECT_DISPLAY_CODE';
767 WHEN 'CUSTOMER_ID' THEN
768 v_dc_col := 'CUSTOMER_DISPLAY_CODE';
769 WHEN 'ENTITY_ID' THEN
770 v_dc_col := 'ENTITY_DISPLAY_CODE';
771 WHEN 'INTERCOMPANY_ID' THEN
772 v_dc_col := 'INTERCOMPANY_DISPLAY_CODE';
773 WHEN 'GEOGRAPHY_ID' THEN
774 v_dc_col := 'GEOGRAPHY_DISPLAY_CODE';
775 WHEN 'TASK_ID' THEN
776 v_dc_col := 'TASK_DISPLAY_CODE';
777 WHEN 'USER_DIM1_ID' THEN
778 v_dc_col := 'USER_DIM1_DISPLAY_CODE';
779 WHEN 'USER_DIM2_ID' THEN
780 v_dc_col := 'USER_DIM2_DISPLAY_CODE';
781 WHEN 'USER_DIM3_ID' THEN
782 v_dc_col := 'USER_DIM3_DISPLAY_CODE';
783 WHEN 'USER_DIM4_ID' THEN
784 v_dc_col := 'USER_DIM4_DISPLAY_CODE';
785 WHEN 'USER_DIM5_ID' THEN
786 v_dc_col := 'USER_DIM5_DISPLAY_CODE';
787 WHEN 'USER_DIM6_ID' THEN
788 v_dc_col := 'USER_DIM6_DISPLAY_CODE';
789 WHEN 'USER_DIM7_ID' THEN
790 v_dc_col := 'USER_DIM7_DISPLAY_CODE';
791 WHEN 'USER_DIM8_ID' THEN
792 v_dc_col := 'USER_DIM8_DISPLAY_CODE';
793 WHEN 'USER_DIM9_ID' THEN
794 v_dc_col := 'USER_DIM9_DISPLAY_CODE';
795 WHEN 'USER_DIM10_ID' THEN
796 v_dc_col := 'USER_DIM10_DISPLAY_CODE';
797 ELSE
798 RAISE e_bad_proc_key;
799 END CASE;
800
801 END IF;
802
803 FEM_ENGINES_PKG.TECH_MESSAGE
804 (p_severity => c_log_level_1,
805 p_module => v_block||'.v_dc_col{205}',
806 p_msg_text => v_dc_col);
807
808 v_pk_count := c_proc_key%ROWCOUNT;
809 IF (v_pk_count > 1)
810 THEN
811 v_proc_cols := v_proc_cols||','''||v_dc_col||'''';
812 ELSE
813 v_proc_cols := ''''||v_dc_col||'''';
814 END IF;
815 END LOOP;
816 v_pk_count := v_pk_count + v_pk_add;
817
818 FEM_ENGINES_PKG.TECH_MESSAGE
819 (p_severity => c_log_level_2,
820 p_module => v_block||'.v_proc_cols{207}',
821 p_msg_text => v_proc_cols);
822
823 IF (v_proc_cols IS NULL)
824 THEN
825 RAISE e_no_proc_key;
826 END IF;
827
828 FEM_ENGINES_PKG.TECH_MESSAGE
829 (p_severity => c_log_level_2,
830 p_module => v_block||'.v_pk_count{208}',
831 p_msg_text => v_pk_count);
832
836 FOR r_uniq_idx IN c_uniq_idx
833 --------------------------------
834 -- Find Matching Unique Index --
835 --------------------------------
837 LOOP
838 v_uniq_idx := r_uniq_idx.index_name;
839
840 FEM_ENGINES_PKG.TECH_MESSAGE
841 (p_severity => c_log_level_2,
842 p_module => v_block||'.v_uniq_idx{211}',
843 p_msg_text => v_uniq_idx);
844
845 FOR r_idx_col IN c_idx_col
846 LOOP
847 v_idx_col := r_idx_col.column_name;
848
849 BEGIN
850 EXECUTE IMMEDIATE
851 'SELECT 1 FROM dual'||
852 ' WHERE '''||v_idx_col||''' IN ('||v_proc_cols||')'
853 INTO v_proc_key_ok;
854 EXCEPTION
855 WHEN others THEN
856 v_proc_key_ok := 0;
857 EXIT;
858 END;
859 v_ui_count := c_idx_col%ROWCOUNT;
860 END LOOP;
861 IF (v_proc_key_ok = 1)
862 THEN
863 IF (v_pk_count = v_ui_count)
864 THEN
865 EXIT;
866 ELSE
867 v_proc_key_ok := 0;
868 END IF;
869 END IF;
870 END LOOP;
871
872 FEM_ENGINES_PKG.TECH_MESSAGE
873 (p_severity => c_log_level_2,
874 p_module => v_block||'.v_ui_count{212}',
875 p_msg_text => v_ui_count);
876 FEM_ENGINES_PKG.TECH_MESSAGE
877 (p_severity => c_log_level_2,
878 p_module => v_block||'.v_proc_key_ok{213}',
879 p_msg_text => v_proc_key_ok);
880
881 IF (v_uniq_idx IS NULL)
882 THEN
883 RAISE e_no_unq_idx;
884 END IF;
885
886 IF (v_proc_key_ok = 0)
887 THEN
888 RAISE e_no_pk_match;
889 END IF;
890
891 ----------------------
892 -- Verify Ledger ID --
893 ----------------------
894 BEGIN
895 SELECT ledger_display_code,ledger_name
896 INTO v_ledger_dc,v_ledger_name
897 FROM fem_ledgers_vl
898 WHERE ledger_id = g_ledger_id;
899 EXCEPTION
900 WHEN no_data_found THEN
901 RAISE e_bad_ledger_id;
902 END;
903
904 SELECT count(*)
905 INTO v_count
906 FROM fem_ledgers_b B,
907 fem_ledgers_attr A,
908 fem_dim_attributes_b T,
909 fem_dimensions_b D,
910 fem_dim_attr_versions_b V
911 WHERE B.ledger_id = g_ledger_id
912 AND B.enabled_flag = 'Y'
913 AND A.ledger_id = B.ledger_id
914 AND T.attribute_id = A.attribute_id
915 AND T.attribute_varchar_label = 'CAL_PERIOD_HIER_OBJ_DEF_ID'
916 AND V.version_id = A.version_id
917 AND V.default_version_flag = 'Y'
918 AND D.dimension_id = T.dimension_id
919 AND D.dimension_varchar_label = 'LEDGER';
920
921 FEM_ENGINES_PKG.TECH_MESSAGE
922 (p_severity => c_log_level_2,
923 p_module => v_block||'.g_ledger_id{215}',
924 p_msg_text => g_ledger_id||':COUNT='||v_count);
925
926 IF (v_count <> 1)
927 THEN
928 RAISE e_bad_ledger_attr;
929 END IF;
930
931 -------------------------------
932 -- Verify Global VS Combo ID --
933 -------------------------------
934 FND_MSG_PUB.Initialize;
935 g_gvc_id := FEM_DIMENSION_UTIL_PKG.GLOBAL_VS_COMBO_ID
936 (p_encoded => c_false,
937 x_return_status => v_return_status,
938 x_msg_count => v_msg_count,
939 x_msg_data => g_message,
940 p_ledger_id => g_ledger_id);
941
942 FEM_ENGINES_PKG.TECH_MESSAGE
943 (p_severity => c_log_level_2,
944 p_module => v_block||'.g_gvc_id{217}',
945 p_msg_text => g_gvc_id);
946
947 IF (g_gvc_id = -1)
948 THEN
949 RAISE e_bad_gvc_id;
950 END IF;
951
952 -------------------------
953 -- Verify DataSet Code --
954 -------------------------
955 BEGIN
956 SELECT dataset_display_code,dataset_name
957 INTO v_dataset_dc,v_dataset_name
958 FROM fem_datasets_vl
959 WHERE dataset_code = g_dataset_cd;
960 EXCEPTION
961 WHEN no_data_found THEN
962 RAISE e_bad_dataset_cd;
963 END;
964
965 SELECT count(*)
966 INTO v_count
967 FROM fem_datasets_b B,
968 fem_datasets_attr A,
969 fem_dim_attributes_b T,
970 fem_dimensions_b D,
971 fem_dim_attr_versions_b V
972 WHERE B.dataset_code = g_dataset_cd
973 AND B.enabled_flag = 'Y'
974 AND A.dataset_code = B.dataset_code
975 AND T.attribute_id = A.attribute_id
976 AND T.attribute_varchar_label = 'DATASET_BALANCE_TYPE_CODE'
977 AND V.version_id = A.version_id
978 AND V.default_version_flag = 'Y'
979 AND D.dimension_id = T.dimension_id
980 AND D.dimension_varchar_label = 'DATASET';
981
982 FEM_ENGINES_PKG.TECH_MESSAGE
983 (p_severity => c_log_level_2,
984 p_module => v_block||'.g_dataset_cd{218}',
985 p_msg_text => g_dataset_cd||':COUNT='||v_count);
986
987 IF (v_count <> 1)
988 THEN
989 RAISE e_bad_dataset_attr;
990 END IF;
991
992 ------------------------
993 -- Verify Source Code --
994 ------------------------
995 BEGIN
996 SELECT source_system_display_code,source_system_name
997 INTO v_source_dc,v_source_name
998 FROM fem_source_systems_vl
1002 RAISE e_bad_source_cd;
999 WHERE source_system_code = g_source_cd;
1000 EXCEPTION
1001 WHEN no_data_found THEN
1003 END;
1004
1005 FEM_ENGINES_PKG.TECH_MESSAGE
1006 (p_severity => c_log_level_2,
1007 p_module => v_block||'.v_source_dc{220}',
1008 p_msg_text => v_source_dc);
1009
1010 -------------------------------
1011 -- Verify Calendar Period ID --
1012 -------------------------------
1013 BEGIN
1014 SELECT cal_period_name
1015 INTO v_cal_per_name
1016 FROM fem_cal_periods_vl
1017 WHERE cal_period_id = g_cal_per_id;
1018 EXCEPTION
1019 WHEN no_data_found THEN
1020 v_cal_period := g_cal_per_id;
1021 RAISE e_bad_cal_per_id;
1022 END;
1023
1024 BEGIN
1025 SELECT G.dimension_group_display_code,
1026 D.date_assign_value,
1027 N.number_assign_value
1028 INTO v_dim_grp_cd,
1029 v_cal_per_end_date,
1030 v_cal_per_num
1031 FROM fem_cal_periods_b C,
1032 fem_cal_periods_attr N,
1033 fem_cal_periods_attr D,
1034 fem_dimension_grps_b G,
1035 fem_dim_attr_versions_b NV,
1036 fem_dim_attr_versions_b DV
1037 WHERE C.cal_period_id = g_cal_per_id
1038 AND C.dimension_group_id = G.dimension_group_id
1039 AND N.attribute_id =
1040 (SELECT attribute_id FROM fem_dim_attributes_b
1041 WHERE attribute_varchar_label = 'GL_PERIOD_NUM')
1042 AND N.version_id = NV.version_id
1043 AND N.cal_period_id = g_cal_per_id
1044 AND NV.default_version_flag = 'Y'
1045 AND D.attribute_id =
1046 (SELECT attribute_id FROM fem_dim_attributes_b
1047 WHERE attribute_varchar_label = 'CAL_PERIOD_END_DATE')
1048 AND D.version_id = DV.version_id
1049 AND D.cal_period_id = g_cal_per_id
1050 AND DV.default_version_flag = 'Y';
1051
1052 FEM_ENGINES_PKG.TECH_MESSAGE
1053 (p_severity => c_log_level_2,
1054 p_module => v_block||'.v_dim_grp_cd{230}',
1055 p_msg_text => v_dim_grp_cd);
1056 FEM_ENGINES_PKG.TECH_MESSAGE
1057 (p_severity => c_log_level_2,
1058 p_module => v_block||'.v_cal_per_end_date{232}',
1059 p_msg_text => TO_CHAR(v_cal_per_end_date,'YYYY/MM/DD HH24:MI:SS'));
1060 FEM_ENGINES_PKG.TECH_MESSAGE
1061 (p_severity => c_log_level_2,
1062 p_module => v_block||'.v_cal_per_num{234}',
1063 p_msg_text => v_cal_per_num);
1064
1065 EXCEPTION
1066 WHEN no_data_found THEN
1067 v_cal_period := v_cal_per_name;
1068 RAISE e_bad_cal_per_id;
1069 END;
1070
1071 ------------------------------
1072 -- Verify Parameter Data Match
1073 ------------------------------
1074 EXECUTE IMMEDIATE
1075 'SELECT COUNT(*)'||
1076 ' FROM '||g_data_t_table||
1077 ' WHERE calp_dim_grp_display_code = :b_dim_grp_cd'||
1078 ' AND cal_period_end_date = :b_cal_per_end_date'||
1079 ' AND cal_period_number = :b_cal_per_num'||
1080 ' AND ledger_display_code = :b_ledger_dc'||
1081 ' AND dataset_display_code = :b_dataset_dc'||
1082 ' AND source_system_display_code = :b_source_dc'||
1083 ' AND rownum = 1'
1084 INTO v_count
1085 USING v_dim_grp_cd,
1086 TO_DATE(TO_CHAR(v_cal_per_end_date,'YYYY/MM/DD HH24:MI:SS'),'YYYY/MM/DD HH24:MI:SS'),
1087 v_cal_per_num,
1088 v_ledger_dc,
1089 v_dataset_dc,
1090 v_source_dc;
1091
1092 FEM_ENGINES_PKG.TECH_MESSAGE
1093 (p_severity => c_log_level_2,
1094 p_module => v_block||'.data_match{236}',
1095 p_msg_text => 'data_match='||v_count);
1096
1097 IF (v_count = 0)
1098 THEN
1099 RAISE e_no_data_match;
1100 END IF;
1101
1102 ----------------------------------
1103 -- Build Data Slicing Condition --
1104 ----------------------------------
1105 g_condition := ' dataset_display_code = '''||v_dataset_dc||''''||
1106 ' AND calp_dim_grp_display_code = '''||v_dim_grp_cd||''''||
1107 ' AND cal_period_end_date = TO_DATE('''||TO_CHAR(v_cal_per_end_date,'YYYY/MM/DD HH24:MI:SS')||''',''YYYY/MM/DD HH24:MI:SS'')'||
1108 ' AND cal_period_number = '||v_cal_per_num||
1109 ' AND ledger_display_code = '''||v_ledger_dc||''''||
1110 ' AND source_system_display_code = '''||v_source_dc||'''';
1111
1112 IF (g_exec_mode = 'S')
1113 THEN
1114 g_condition := g_condition||' AND status = ''LOAD''';
1115 ELSE
1116 g_condition := g_condition||' AND status <> ''LOAD''';
1117 END IF;
1118
1119 FEM_ENGINES_PKG.TECH_MESSAGE
1120 (p_severity => c_log_level_3,
1121 p_module => v_block||'.End{240}',
1122 p_msg_text => 'End FEM_DATAX_LOADER.Validation');
1123
1124 ---------------------
1125 -- Exception Block --
1126 ---------------------
1127 EXCEPTION
1128
1129 WHEN e_bad_exec_mode THEN
1130 FEM_ENGINES_PKG.PUT_MESSAGE
1131 (p_app_name => 'FEM',
1132 p_msg_name => 'FEM_DATAX_LDR_BAD_EXEC_ERR');
1133
1134 g_message := FND_MSG_PUB.GET(p_encoded => c_false);
1135 g_msg_no := 241;
1136
1137 RAISE e_validation_error;
1138
1139 WHEN e_bad_obj_def THEN
1140 FEM_ENGINES_PKG.PUT_MESSAGE
1141 (p_app_name => 'FEM',
1142 p_msg_name => 'FEM_DATAX_LDR_BAD_OBJ_ERR',
1143 p_token1 => 'OBJECT',
1144 p_value1 => g_obj_def_id);
1145
1149 RAISE e_validation_error;
1146 g_message := FND_MSG_PUB.GET(p_encoded => c_false);
1147 g_msg_no := 242;
1148
1150
1151 WHEN e_bad_target_table THEN
1152 FEM_ENGINES_PKG.PUT_MESSAGE
1153 (p_app_name => 'FEM',
1154 p_msg_name => 'FEM_DATAX_LDR_BAD_TABLE_ERR',
1155 p_token1 => 'TABLE',
1156 p_value1 => g_data_table);
1157
1158 g_message := FND_MSG_PUB.GET(p_encoded => c_false);
1159 g_msg_no := 243;
1160
1161 RAISE e_validation_error;
1162
1163 WHEN e_no_proc_key THEN
1164 FEM_ENGINES_PKG.PUT_MESSAGE
1165 (p_app_name => 'FEM',
1166 p_msg_name => 'FEM_DATAX_LDR_NO_PROC_KEY_ERR',
1167 p_token1 => 'TABLE',
1168 p_value1 => g_data_table);
1169
1170 g_message := FND_MSG_PUB.GET(p_encoded => c_false);
1171 g_msg_no := 244;
1172
1173 RAISE e_validation_error;
1174
1175 WHEN e_bad_proc_key THEN
1176 FEM_ENGINES_PKG.PUT_MESSAGE
1177 (p_app_name => 'FEM',
1178 p_msg_name => 'FEM_DATAX_LDR_BAD_PROC_KEY_ERR',
1179 p_token1 => 'COLUMN',
1180 p_value1 => v_proc_col);
1181
1182 g_message := FND_MSG_PUB.GET(p_encoded => c_false);
1183 g_msg_no := 245;
1184
1185 RAISE e_validation_error;
1186
1187 WHEN e_no_unq_idx THEN
1188 FEM_ENGINES_PKG.PUT_MESSAGE
1189 (p_app_name => 'FEM',
1190 p_msg_name => 'FEM_DATAX_LDR_NO_UNIQ_IDX_ERR',
1191 p_token1 => 'TABLE',
1192 p_value1 => g_data_t_table);
1193
1194 g_message := FND_MSG_PUB.GET(p_encoded => c_false);
1195 g_msg_no := 246;
1196
1197 RAISE e_validation_error;
1198
1199 WHEN e_no_pk_match THEN
1200 FEM_ENGINES_PKG.PUT_MESSAGE
1201 (p_app_name => 'FEM',
1202 p_msg_name => 'FEM_DATAX_LDR_NO_PK_MATCH_ERR');
1203
1204 g_message := FND_MSG_PUB.GET(p_encoded => c_false);
1205 g_msg_no := 247;
1206
1207 RAISE e_validation_error;
1208
1209 WHEN e_bad_ledger_id THEN
1210 FEM_ENGINES_PKG.PUT_MESSAGE
1211 (p_app_name => 'FEM',
1212 p_msg_name => 'FEM_DATAX_LDR_BAD_LEDGER_ERR',
1213 p_token1 => 'LEDGER',
1214 p_value1 => g_ledger_id);
1215
1216 g_message := FND_MSG_PUB.GET(p_encoded => c_false);
1217 g_msg_no := 248;
1218
1219 RAISE e_validation_error;
1220
1221 WHEN e_bad_ledger_attr THEN
1222 FEM_ENGINES_PKG.PUT_MESSAGE
1223 (p_app_name => 'FEM',
1224 p_msg_name => 'FEM_DATAX_LDR_LEDGER_ATTR_ERR',
1225 p_token1 => 'LEDGER',
1226 p_value1 => v_ledger_name);
1227
1228 g_message := FND_MSG_PUB.GET(p_encoded => c_false);
1229 g_msg_no := 249;
1230
1231 RAISE e_validation_error;
1232
1233 WHEN e_bad_gvc_id THEN
1234
1235 g_msg_no := 250;
1236 RAISE e_validation_error;
1237
1238 WHEN e_bad_dataset_cd THEN
1239 FEM_ENGINES_PKG.PUT_MESSAGE
1240 (p_app_name => 'FEM',
1241 p_msg_name => 'FEM_DATAX_LDR_BAD_DATASET_ERR',
1242 p_token1 => 'DATASET',
1243 p_value1 => g_dataset_cd);
1244
1245 g_message := FND_MSG_PUB.GET(p_encoded => c_false);
1246 g_msg_no := 251;
1247
1248 RAISE e_validation_error;
1249
1250 WHEN e_bad_dataset_attr THEN
1251 FEM_ENGINES_PKG.PUT_MESSAGE
1252 (p_app_name => 'FEM',
1253 p_msg_name => 'FEM_DATAX_LDR_DATASET_ATTR_ERR',
1254 p_token1 => 'DATASET',
1255 p_value1 => v_dataset_name);
1256
1257 g_message := FND_MSG_PUB.GET(p_encoded => c_false);
1258 g_msg_no := 252;
1259
1260 RAISE e_validation_error;
1261
1262 WHEN e_bad_source_cd THEN
1263 FEM_ENGINES_PKG.PUT_MESSAGE
1264 (p_app_name => 'FEM',
1265 p_msg_name => 'FEM_DATAX_LDR_BAD_SOURCE_ERR',
1266 p_token1 => 'SOURCE',
1267 p_value1 => g_source_cd);
1268
1269 g_message := FND_MSG_PUB.GET(p_encoded => c_false);
1270 g_msg_no := 253;
1271
1272 RAISE e_validation_error;
1273
1274 WHEN e_bad_cal_per_id THEN
1275 FEM_ENGINES_PKG.PUT_MESSAGE
1276 (p_app_name => 'FEM',
1277 p_msg_name => 'FEM_DATAX_LDR_BAD_CAL_PER_ERR',
1278 p_token1 => 'CAL_PER',
1279 p_value1 => v_cal_period);
1280
1281 g_message := FND_MSG_PUB.GET(p_encoded => c_false);
1282 g_msg_no := 254;
1283
1284 RAISE e_validation_error;
1285
1286 WHEN e_no_data_match THEN
1287 FEM_ENGINES_PKG.PUT_MESSAGE
1288 (p_app_name => 'FEM',
1289 p_msg_name => 'FEM_DATAX_LDR_DATA_MATCH_ERR',
1290 p_token1 => 'TABLE',
1291 p_value1 => g_data_t_table,
1292 p_token2 => 'DIM_GRP',
1293 p_value2 => v_dim_grp_cd,
1294 p_token3 => 'PER_NUM',
1295 p_value3 => v_cal_per_num,
1296 p_token4 => 'END_DATE',
1297 p_value4 => TO_CHAR(v_cal_per_end_date,'YYYY/MM/DD HH24:MI:SS'),
1298 p_token5 => 'LEDGER_DC',
1299 p_value5 => v_ledger_dc,
1300 p_token6 => 'DATASET_DC',
1301 p_value6 => v_dataset_dc,
1302 p_token7 => 'SOURCE_DC',
1303 p_value7 => v_source_dc);
1304
1305 g_message := FND_MSG_PUB.GET(p_encoded => c_false);
1306 g_msg_no := 255;
1307
1308 RAISE e_validation_error;
1309
1310 END Validation;
1311
1312
1316 * ================ *
1313 /***************************************************************************
1314 ***************************************************************************
1315 * *
1317 * Registration *
1318 * ================ *
1319 * *
1320 ***************************************************************************
1321 **************************************************************************/
1322
1323 PROCEDURE Registration
1324 IS
1325
1326 v_exec_state VARCHAR2(30);
1327 v_retcode NUMBER;
1328 v_num_msg NUMBER;
1329 v_msg_count NUMBER;
1330 v_msg_data VARCHAR2(4196);
1331 v_return_status VARCHAR2(1);
1332 v_prev_req_id NUMBER;
1333
1334 v_block CONSTANT VARCHAR2(80) :=
1335 'fem.plsql.fem_datax_loader_pkg.registration';
1336
1337 BEGIN
1338
1339 FEM_ENGINES_PKG.TECH_MESSAGE
1340 (p_severity => c_log_level_3,
1341 p_module => v_block||'.Begin{300}',
1342 p_msg_text => 'Begin FEM_DATAX_LOADER.Registration');
1343
1344 ----------------------
1345 -- Register Request --
1346 ----------------------
1347 FEM_PL_PKG.Register_Request(
1348 p_api_version => c_api_version,
1349 p_cal_period_id => g_cal_per_id,
1350 p_ledger_id => g_ledger_id,
1351 p_dataset_io_obj_def_id => null,
1352 p_output_dataset_code => g_dataset_cd,
1353 p_source_system_code => g_source_cd,
1354 p_effective_date => null,
1355 p_rule_set_obj_def_id => null,
1356 p_rule_set_name => null,
1357 p_request_id => g_req_id,
1358 p_user_id => c_user_id,
1359 p_last_update_login => null,
1360 p_program_id => c_conc_prg_id,
1361 p_program_login_id => c_login_id,
1362 p_program_application_id => c_prg_app_id,
1363 p_exec_mode_code => g_exec_mode,
1364 p_dimension_id => null,
1365 p_table_name => g_data_table,
1366 p_hierarchy_name => null,
1367 x_msg_count => v_msg_count,
1368 x_msg_data => v_msg_data,
1369 x_return_status => v_return_status);
1370
1371 FEM_ENGINES_PKG.TECH_MESSAGE
1372 (p_severity => c_log_level_2,
1373 p_module => v_block||'.register_request.return_status{301}',
1374 p_msg_text => v_return_status);
1375
1376 Get_Put_Messages (
1377 p_msg_count => v_msg_count,
1378 p_msg_data => v_msg_data);
1379
1380 IF (v_return_status <> c_success)
1381 THEN
1382 RAISE e_process_lock_error;
1383 END IF;
1384
1385 -------------------------------
1386 -- Register Object Execution --
1387 -------------------------------
1388 FEM_PL_PKG.Register_Object_Execution(
1389 p_api_version => c_api_version,
1390 p_request_id => g_req_id,
1391 p_object_id => g_object_id,
1392 p_exec_object_definition_id => g_obj_def_id,
1393 p_user_id => c_user_id,
1394 p_last_update_login => null,
1395 p_exec_mode_code => g_exec_mode,
1396 x_exec_state => v_exec_state,
1397 x_prev_request_id => v_prev_req_id,
1398 x_msg_count => v_msg_count,
1399 x_msg_data => v_msg_data,
1400 x_return_status => v_return_status);
1401
1402 FEM_ENGINES_PKG.TECH_MESSAGE
1403 (p_severity => c_log_level_2,
1404 p_module => v_block||'.v_exec_state{302}',
1405 p_msg_text => v_exec_state);
1406 FEM_ENGINES_PKG.TECH_MESSAGE
1407 (p_severity => c_log_level_2,
1408 p_module => v_block||'.v_prev_req_id{303}',
1409 p_msg_text => v_prev_req_id);
1410 FEM_ENGINES_PKG.TECH_MESSAGE
1411 (p_severity => c_log_level_2,
1412 p_module => v_block||'.object_execution.return_status{304}',
1413 p_msg_text => v_return_status);
1414
1415 Get_Put_Messages (
1416 p_msg_count => v_msg_count,
1417 p_msg_data => v_msg_data);
1418
1419 IF (v_return_status <> c_success)
1420 THEN
1421 FEM_PL_PKG.Unregister_Request(
1422 p_api_version => c_api_version,
1423 p_commit => c_true,
1424 p_request_id => g_req_id,
1425 x_msg_count => v_msg_count,
1426 x_msg_data => v_msg_data,
1427 x_return_status => v_return_status);
1428
1429 FEM_ENGINES_PKG.TECH_MESSAGE
1430 (p_severity => c_log_level_2,
1431 p_module => v_block||'.unregister_request.return_status{305}',
1432 p_msg_text => v_return_status);
1433
1434 Get_Put_Messages (
1435 p_msg_count => v_msg_count,
1436 p_msg_data => v_msg_data);
1437
1438 RAISE e_process_lock_error;
1439 END IF;
1440
1441 g_upd_pl_on_err := 'Y';
1442
1443 --------------------------------
1444 -- Register Object Definition --
1445 --------------------------------
1446 FEM_PL_PKG.Register_Object_Def(
1447 p_api_version => c_api_version,
1448 p_request_id => g_req_id,
1449 p_object_id => g_object_id,
1450 p_object_definition_id => g_obj_def_id,
1451 p_user_id => c_user_id,
1452 p_last_update_login => null,
1453 x_msg_count => v_msg_count,
1454 x_msg_data => v_msg_data,
1455 x_return_status => v_return_status);
1456
1457 FEM_ENGINES_PKG.TECH_MESSAGE
1458 (p_severity => c_log_level_2,
1462 Get_Put_Messages (
1459 p_module => v_block||'.register_object_def.return_status{306}',
1460 p_msg_text => v_return_status);
1461
1463 p_msg_count => v_msg_count,
1464 p_msg_data => v_msg_data);
1465
1466 IF (v_return_status <> c_success)
1467 THEN
1468 RAISE e_process_lock_error;
1469 END IF;
1470
1471 --------------------
1472 -- Register Table --
1473 --------------------
1474 FEM_PL_PKG.Register_Table(
1475 p_api_version => c_api_version,
1476 p_request_id => g_req_id,
1477 p_object_id => g_object_id,
1478 p_table_name => g_data_table,
1479 p_statement_type => 'INSERT',
1480 p_num_of_output_rows => 0,
1481 p_user_id => c_user_id,
1482 p_last_update_login => null,
1483 x_msg_count => v_msg_count,
1484 x_msg_data => v_msg_data,
1485 x_return_status => v_return_status);
1486
1487 FEM_ENGINES_PKG.TECH_MESSAGE
1488 (p_severity => c_log_level_2,
1489 p_module => v_block||'.register_table.return_status{307}',
1490 p_msg_text => v_return_status);
1491
1492 Get_Put_Messages (
1493 p_msg_count => v_msg_count,
1494 p_msg_data => v_msg_data);
1495
1496 IF (v_return_status <> c_success)
1497 THEN
1498 RAISE e_process_lock_error;
1499 END IF;
1500
1501 ----------------------------
1502 -- Register Data Location --
1503 ----------------------------
1504 FEM_DIMENSION_UTIL_PKG.REGISTER_DATA_LOCATION
1505 (p_request_id => g_req_id,
1506 p_object_id => g_object_id,
1507 p_table_name => g_data_table,
1508 p_ledger_id => g_ledger_id,
1509 p_cal_per_id => g_cal_per_id,
1510 p_dataset_cd => g_dataset_cd,
1511 p_source_cd => g_source_cd,
1512 p_load_status => 'INCOMPLETE');
1513
1514 FEM_ENGINES_PKG.TECH_MESSAGE
1515 (p_severity => c_log_level_3,
1516 p_module => v_block||'.End{320}',
1517 p_msg_text => 'End FEM_DATAX_LOADER.Registration');
1518
1519 END Registration;
1520
1521
1522 /***************************************************************************
1523 ***************************************************************************
1524 * *
1525 * ================ *
1526 * Pre_Process *
1527 * ================ *
1528 * *
1529 ***************************************************************************
1530 **************************************************************************/
1531
1532 PROCEDURE Pre_Process
1533 IS
1534
1535 v_cctr_org_col VARCHAR2(30);
1536 v_cctr_org_dc_col VARCHAR2(30);
1537 v_cctr_org_b_tab VARCHAR2(30);
1538
1539 v_fin_elem_col VARCHAR2(30);
1540 v_fin_elem_dc_col VARCHAR2(30);
1541 v_fin_elem_b_tab VARCHAR2(30);
1542
1543 v_product_col VARCHAR2(30);
1544 v_product_dc_col VARCHAR2(30);
1545 v_product_b_tab VARCHAR2(30);
1546
1547 v_nat_acct_col VARCHAR2(30);
1548 v_nat_acct_dc_col VARCHAR2(30);
1549 v_nat_acct_b_tab VARCHAR2(30);
1550
1551 v_channel_col VARCHAR2(30);
1552 v_channel_dc_col VARCHAR2(30);
1553 v_channel_b_tab VARCHAR2(30);
1554
1555 v_line_item_col VARCHAR2(30);
1556 v_line_item_dc_col VARCHAR2(30);
1557 v_line_item_b_tab VARCHAR2(30);
1558
1559 v_project_col VARCHAR2(30);
1560 v_project_dc_col VARCHAR2(30);
1561 v_project_b_tab VARCHAR2(30);
1562
1563 v_customer_col VARCHAR2(30);
1564 v_customer_dc_col VARCHAR2(30);
1565 v_customer_b_tab VARCHAR2(30);
1566
1567 v_entity_col VARCHAR2(30);
1568 v_entity_dc_col VARCHAR2(30);
1569 v_entity_b_tab VARCHAR2(30);
1570
1571 v_geography_col VARCHAR2(30);
1572 v_geography_dc_col VARCHAR2(30);
1573 v_geography_b_tab VARCHAR2(30);
1574
1575 v_task_col VARCHAR2(30);
1576 v_task_dc_col VARCHAR2(30);
1577 v_task_b_tab VARCHAR2(30);
1578
1579 v_interco_col VARCHAR2(30);
1580 v_interco_dc_col VARCHAR2(30);
1581 v_interco_b_tab VARCHAR2(30);
1582
1583 v_user_dim1_col VARCHAR2(30);
1584 v_user_dim1_dc_col VARCHAR2(30);
1585 v_user_dim1_b_tab VARCHAR2(30);
1586
1587 v_user_dim2_col VARCHAR2(30);
1588 v_user_dim2_dc_col VARCHAR2(30);
1589 v_user_dim2_b_tab VARCHAR2(30);
1590
1591 v_user_dim3_col VARCHAR2(30);
1592 v_user_dim3_dc_col VARCHAR2(30);
1593 v_user_dim3_b_tab VARCHAR2(30);
1594
1595 v_user_dim4_col VARCHAR2(30);
1596 v_user_dim4_dc_col VARCHAR2(30);
1597 v_user_dim4_b_tab VARCHAR2(30);
1598
1599 v_user_dim5_col VARCHAR2(30);
1600 v_user_dim5_dc_col VARCHAR2(30);
1601 v_user_dim5_b_tab VARCHAR2(30);
1602
1603 v_user_dim6_col VARCHAR2(30);
1604 v_user_dim6_dc_col VARCHAR2(30);
1605 v_user_dim6_b_tab VARCHAR2(30);
1606
1607 v_user_dim7_col VARCHAR2(30);
1608 v_user_dim7_dc_col VARCHAR2(30);
1609 v_user_dim7_b_tab VARCHAR2(30);
1610
1611 v_user_dim8_col VARCHAR2(30);
1612 v_user_dim8_dc_col VARCHAR2(30);
1613 v_user_dim8_b_tab VARCHAR2(30);
1614
1615 v_user_dim9_col VARCHAR2(30);
1616 v_user_dim9_dc_col VARCHAR2(30);
1617 v_user_dim9_b_tab VARCHAR2(30);
1621 v_user_dim10_b_tab VARCHAR2(30);
1618
1619 v_user_dim10_col VARCHAR2(30);
1620 v_user_dim10_dc_col VARCHAR2(30);
1622
1623 v_block CONSTANT VARCHAR2(80) :=
1624 'fem.plsql.fem_datax_loader_pkg.pre_process';
1625
1626 BEGIN
1627
1628 FEM_ENGINES_PKG.TECH_MESSAGE
1629 (p_severity => c_log_level_3,
1630 p_module => v_block||'.Begin{400}',
1631 p_msg_text => 'Begin FEM_DATAX_LOADER.Pre_Process');
1632
1633 ------------------------------------------------------------------
1634 -- Build a SQL statement for each dimension to retrieve ID members
1635 ------------------------------------------------------------------
1636 BEGIN
1637 SELECT member_b_table_name,member_col,member_display_code_col
1638 INTO v_cctr_org_b_tab,v_cctr_org_col,v_cctr_org_dc_col
1639 FROM fem_tab_columns_b C,
1640 fem_xdim_dimensions X
1641 WHERE C.table_name = g_data_table
1642 AND C.column_name = 'COMPANY_COST_CENTER_ORG_ID'
1643 AND C.dimension_id = X.dimension_id;
1644
1645 g_cctr_org_sql :=
1646 'SELECT '||v_cctr_org_col||
1647 ' FROM '||v_cctr_org_b_tab||' B,'||
1648 ' fem_global_vs_combo_defs G'||
1649 ' WHERE G.global_vs_combo_id = '||g_gvc_id||
1650 ' AND G.value_set_id = B.value_set_id'||
1651 ' AND B.'||v_cctr_org_dc_col||' = :b_dc_val'||
1652 ' AND B.enabled_flag = ''Y''';
1653 EXCEPTION
1654 WHEN no_data_found THEN g_cctr_org_sql := '';
1655 END;
1656
1657 BEGIN
1658 SELECT member_b_table_name,member_col,member_display_code_col
1659 INTO v_fin_elem_b_tab,v_fin_elem_col,v_fin_elem_dc_col
1660 FROM fem_tab_columns_b C,
1661 fem_xdim_dimensions X
1662 WHERE C.table_name = g_data_table
1663 AND C.column_name = 'FINANCIAL_ELEM_ID'
1664 AND C.dimension_id = X.dimension_id;
1665
1666 g_fin_elem_sql :=
1667 'SELECT '||v_fin_elem_col||
1668 ' FROM '||v_fin_elem_b_tab||' B,'||
1669 ' fem_global_vs_combo_defs G'||
1670 ' WHERE G.global_vs_combo_id = '||g_gvc_id||
1671 ' AND G.value_set_id = B.value_set_id'||
1672 ' AND B.'||v_fin_elem_dc_col||' = :b_dc_val'||
1673 ' AND B.enabled_flag = ''Y''';
1674 EXCEPTION
1675 WHEN no_data_found THEN g_fin_elem_sql := '';
1676 END;
1677
1678 BEGIN
1679 SELECT member_b_table_name,member_col,member_display_code_col
1680 INTO v_product_b_tab,v_product_col,v_product_dc_col
1681 FROM fem_tab_columns_b C,
1682 fem_xdim_dimensions X
1683 WHERE C.table_name = g_data_table
1684 AND C.column_name = 'PRODUCT_ID'
1685 AND C.dimension_id = X.dimension_id;
1686
1687 g_product_sql :=
1688 'SELECT '||v_product_col||
1689 ' FROM '||v_product_b_tab||' B,'||
1690 ' fem_global_vs_combo_defs G'||
1691 ' WHERE G.global_vs_combo_id = '||g_gvc_id||
1692 ' AND G.value_set_id = B.value_set_id'||
1693 ' AND B.'||v_product_dc_col||' = :b_dc_val'||
1694 ' AND B.enabled_flag = ''Y''';
1695 EXCEPTION
1696 WHEN no_data_found THEN g_product_sql := '';
1697 END;
1698
1699 BEGIN
1700 SELECT member_b_table_name,member_col,member_display_code_col
1701 INTO v_nat_acct_b_tab,v_nat_acct_col,v_nat_acct_dc_col
1702 FROM fem_tab_columns_b C,
1703 fem_xdim_dimensions X
1704 WHERE C.table_name = g_data_table
1705 AND C.column_name = 'NATURAL_ACCOUNT_ID'
1706 AND C.dimension_id = X.dimension_id;
1707
1708 g_nat_acct_sql :=
1709 'SELECT '||v_nat_acct_col||
1710 ' FROM '||v_nat_acct_b_tab||' B,'||
1711 ' fem_global_vs_combo_defs G'||
1712 ' WHERE G.global_vs_combo_id = '||g_gvc_id||
1713 ' AND G.value_set_id = B.value_set_id'||
1714 ' AND B.'||v_nat_acct_dc_col||' = :b_dc_val'||
1715 ' AND B.enabled_flag = ''Y''';
1716 EXCEPTION
1717 WHEN no_data_found THEN g_nat_acct_sql := '';
1718 END;
1719
1720 BEGIN
1721 SELECT member_b_table_name,member_col,member_display_code_col
1722 INTO v_channel_b_tab,v_channel_col,v_channel_dc_col
1723 FROM fem_tab_columns_b C,
1724 fem_xdim_dimensions X
1725 WHERE C.table_name = g_data_table
1726 AND C.column_name = 'CHANNEL_ID'
1727 AND C.dimension_id = X.dimension_id;
1728
1729 g_channel_sql :=
1730 'SELECT '||v_channel_col||
1731 ' FROM '||v_channel_b_tab||' B,'||
1732 ' fem_global_vs_combo_defs G'||
1733 ' WHERE G.global_vs_combo_id = '||g_gvc_id||
1734 ' AND G.value_set_id = B.value_set_id'||
1735 ' AND B.'||v_channel_dc_col||' = :b_dc_val'||
1736 ' AND B.enabled_flag = ''Y''';
1737 EXCEPTION
1738 WHEN no_data_found THEN g_channel_sql := '';
1739 END;
1740
1741 BEGIN
1742 SELECT member_b_table_name,member_col,member_display_code_col
1743 INTO v_line_item_b_tab,v_line_item_col,v_line_item_dc_col
1744 FROM fem_tab_columns_b C,
1745 fem_xdim_dimensions X
1746 WHERE C.table_name = g_data_table
1747 AND C.column_name = 'LINE_ITEM_ID'
1748 AND C.dimension_id = X.dimension_id;
1749
1750 g_line_item_sql :=
1751 'SELECT '||v_line_item_col||
1752 ' FROM '||v_line_item_b_tab||' B,'||
1753 ' fem_global_vs_combo_defs G'||
1754 ' WHERE G.global_vs_combo_id = '||g_gvc_id||
1758 EXCEPTION
1755 ' AND G.value_set_id = B.value_set_id'||
1756 ' AND B.'||v_line_item_dc_col||' = :b_dc_val'||
1757 ' AND B.enabled_flag = ''Y''';
1759 WHEN no_data_found THEN g_line_item_sql := '';
1760 END;
1761
1762 BEGIN
1763 SELECT member_b_table_name,member_col,member_display_code_col
1764 INTO v_project_b_tab,v_project_col,v_project_dc_col
1765 FROM fem_tab_columns_b C,
1766 fem_xdim_dimensions X
1767 WHERE C.table_name = g_data_table
1768 AND C.column_name = 'PROJECT_ID'
1769 AND C.dimension_id = X.dimension_id;
1770
1771 g_project_sql :=
1772 'SELECT '||v_project_col||
1773 ' FROM '||v_project_b_tab||' B,'||
1774 ' fem_global_vs_combo_defs G'||
1775 ' WHERE G.global_vs_combo_id = '||g_gvc_id||
1776 ' AND G.value_set_id = B.value_set_id'||
1777 ' AND B.'||v_project_dc_col||' = :b_dc_val'||
1778 ' AND B.enabled_flag = ''Y''';
1779 EXCEPTION
1780 WHEN no_data_found THEN g_project_sql := '';
1781 END;
1782
1783 BEGIN
1784 SELECT member_b_table_name,member_col,member_display_code_col
1785 INTO v_customer_b_tab,v_customer_col,v_customer_dc_col
1786 FROM fem_tab_columns_b C,
1787 fem_xdim_dimensions X
1788 WHERE C.table_name = g_data_table
1789 AND C.column_name = 'CUSTOMER_ID'
1790 AND C.dimension_id = X.dimension_id;
1791
1792 g_customer_sql :=
1793 'SELECT '||v_customer_col||
1794 ' FROM '||v_customer_b_tab||' B,'||
1795 ' fem_global_vs_combo_defs G'||
1796 ' WHERE G.global_vs_combo_id = '||g_gvc_id||
1797 ' AND G.value_set_id = B.value_set_id'||
1798 ' AND B.'||v_customer_dc_col||' = :b_dc_val'||
1799 ' AND B.enabled_flag = ''Y''';
1800 EXCEPTION
1801 WHEN no_data_found THEN g_customer_sql := '';
1802 END;
1803
1804 BEGIN
1805 SELECT member_b_table_name,member_col,member_display_code_col
1806 INTO v_entity_b_tab,v_entity_col,v_entity_dc_col
1807 FROM fem_tab_columns_b C,
1808 fem_xdim_dimensions X
1809 WHERE C.table_name = g_data_table
1810 AND C.column_name = 'ENTITY_ID'
1811 AND C.dimension_id = X.dimension_id;
1812
1813 g_entity_sql :=
1814 'SELECT '||v_entity_col||
1815 ' FROM '||v_entity_b_tab||' B,'||
1816 ' fem_global_vs_combo_defs G'||
1817 ' WHERE G.global_vs_combo_id = '||g_gvc_id||
1818 ' AND G.value_set_id = B.value_set_id'||
1819 ' AND B.'||v_entity_dc_col||' = :b_dc_val'||
1820 ' AND B.enabled_flag = ''Y''';
1821 EXCEPTION
1822 WHEN no_data_found THEN g_entity_sql := '';
1823 END;
1824
1825 BEGIN
1826 SELECT member_b_table_name,member_col,member_display_code_col
1827 INTO v_geography_b_tab,v_geography_col,v_geography_dc_col
1828 FROM fem_tab_columns_b C,
1829 fem_xdim_dimensions X
1830 WHERE C.table_name = g_data_table
1831 AND C.column_name = 'GEOGRAPHY_ID'
1832 AND C.dimension_id = X.dimension_id;
1833
1834 g_geography_sql :=
1835 'SELECT '||v_geography_col||
1836 ' FROM '||v_geography_b_tab||' B,'||
1837 ' fem_global_vs_combo_defs G'||
1838 ' WHERE G.global_vs_combo_id = '||g_gvc_id||
1839 ' AND G.value_set_id = B.value_set_id'||
1840 ' AND B.'||v_geography_dc_col||' = :b_dc_val'||
1841 ' AND B.enabled_flag = ''Y''';
1842 EXCEPTION
1843 WHEN no_data_found THEN g_geography_sql := '';
1844 END;
1845
1846 BEGIN
1847 SELECT member_b_table_name,member_col,member_display_code_col
1848 INTO v_task_b_tab,v_task_col,v_task_dc_col
1849 FROM fem_tab_columns_b C,
1850 fem_xdim_dimensions X
1851 WHERE C.table_name = g_data_table
1852 AND C.column_name = 'TASK_ID'
1853 AND C.dimension_id = X.dimension_id;
1854
1855 g_task_sql :=
1856 'SELECT '||v_task_col||
1857 ' FROM '||v_task_b_tab||' B,'||
1858 ' fem_global_vs_combo_defs G'||
1859 ' WHERE G.global_vs_combo_id = '||g_gvc_id||
1860 ' AND G.value_set_id = B.value_set_id'||
1861 ' AND B.'||v_task_dc_col||' = :b_dc_val'||
1862 ' AND B.enabled_flag = ''Y''';
1863 EXCEPTION
1864 WHEN no_data_found THEN g_task_sql := '';
1865 END;
1866
1867 BEGIN
1868 SELECT member_b_table_name,member_col,member_display_code_col
1869 INTO v_interco_b_tab,v_interco_col,v_interco_dc_col
1870 FROM fem_tab_columns_b C,
1871 fem_xdim_dimensions X
1872 WHERE C.table_name = g_data_table
1873 AND C.column_name = 'INTERCOMPANY_ID'
1874 AND C.dimension_id = X.dimension_id;
1875
1876 g_interco_sql :=
1877 'SELECT '||v_interco_col||
1878 ' FROM '||v_interco_b_tab||' B,'||
1879 ' fem_global_vs_combo_defs G'||
1880 ' WHERE G.global_vs_combo_id = '||g_gvc_id||
1881 ' AND G.value_set_id = B.value_set_id'||
1882 ' AND B.'||v_interco_dc_col||' = :b_dc_val'||
1883 ' AND B.enabled_flag = ''Y''';
1884 EXCEPTION
1885 WHEN no_data_found THEN g_interco_sql := '';
1886 END;
1887
1888 BEGIN
1889 SELECT member_b_table_name,member_col,member_display_code_col
1890 INTO v_user_dim1_b_tab,v_user_dim1_col,v_user_dim1_dc_col
1891 FROM fem_tab_columns_b C,
1895 AND C.dimension_id = X.dimension_id;
1892 fem_xdim_dimensions X
1893 WHERE C.table_name = g_data_table
1894 AND C.column_name = 'USER_DIM1_ID'
1896
1897 g_user_dim1_sql :=
1898 'SELECT '||v_user_dim1_col||
1899 ' FROM '||v_user_dim1_b_tab||' B,'||
1900 ' fem_global_vs_combo_defs G'||
1901 ' WHERE G.global_vs_combo_id = '||g_gvc_id||
1902 ' AND G.value_set_id = B.value_set_id'||
1903 ' AND B.'||v_user_dim1_dc_col||' = :b_dc_val'||
1904 ' AND B.enabled_flag = ''Y''';
1905 EXCEPTION
1906 WHEN no_data_found THEN g_user_dim1_sql := '';
1907 END;
1908
1909 BEGIN
1910 SELECT member_b_table_name,member_col,member_display_code_col
1911 INTO v_user_dim2_b_tab,v_user_dim2_col,v_user_dim2_dc_col
1912 FROM fem_tab_columns_b C,
1913 fem_xdim_dimensions X
1914 WHERE C.table_name = g_data_table
1915 AND C.column_name = 'USER_DIM2_ID'
1916 AND C.dimension_id = X.dimension_id;
1917
1918 g_user_dim2_sql :=
1919 'SELECT '||v_user_dim2_col||
1920 ' FROM '||v_user_dim2_b_tab||' B,'||
1921 ' fem_global_vs_combo_defs G'||
1922 ' WHERE G.global_vs_combo_id = '||g_gvc_id||
1923 ' AND G.value_set_id = B.value_set_id'||
1924 ' AND B.'||v_user_dim2_dc_col||' = :b_dc_val'||
1925 ' AND B.enabled_flag = ''Y''';
1926 EXCEPTION
1927 WHEN no_data_found THEN g_user_dim2_sql := '';
1928 END;
1929
1930 BEGIN
1931 SELECT member_b_table_name,member_col,member_display_code_col
1932 INTO v_user_dim3_b_tab,v_user_dim3_col,v_user_dim3_dc_col
1933 FROM fem_tab_columns_b C,
1934 fem_xdim_dimensions X
1935 WHERE C.table_name = g_data_table
1936 AND C.column_name = 'USER_DIM3_ID'
1937 AND C.dimension_id = X.dimension_id;
1938
1939 g_user_dim3_sql :=
1940 'SELECT '||v_user_dim3_col||
1941 ' FROM '||v_user_dim3_b_tab||' B,'||
1942 ' fem_global_vs_combo_defs G'||
1943 ' WHERE G.global_vs_combo_id = '||g_gvc_id||
1944 ' AND G.value_set_id = B.value_set_id'||
1945 ' AND B.'||v_user_dim3_dc_col||' = :b_dc_val'||
1946 ' AND B.enabled_flag = ''Y''';
1947 EXCEPTION
1948 WHEN no_data_found THEN g_user_dim3_sql := '';
1949 END;
1950
1951 BEGIN
1952 SELECT member_b_table_name,member_col,member_display_code_col
1953 INTO v_user_dim4_b_tab,v_user_dim4_col,v_user_dim4_dc_col
1954 FROM fem_tab_columns_b C,
1955 fem_xdim_dimensions X
1956 WHERE C.table_name = g_data_table
1957 AND C.column_name = 'USER_DIM4_ID'
1958 AND C.dimension_id = X.dimension_id;
1959
1960 g_user_dim4_sql :=
1961 'SELECT '||v_user_dim4_col||
1962 ' FROM '||v_user_dim4_b_tab||' B,'||
1963 ' fem_global_vs_combo_defs G'||
1964 ' WHERE G.global_vs_combo_id = '||g_gvc_id||
1965 ' AND G.value_set_id = B.value_set_id'||
1966 ' AND B.'||v_user_dim4_dc_col||' = :b_dc_val'||
1967 ' AND B.enabled_flag = ''Y''';
1968 EXCEPTION
1969 WHEN no_data_found THEN g_user_dim4_sql := '';
1970 END;
1971
1972 BEGIN
1973 SELECT member_b_table_name,member_col,member_display_code_col
1974 INTO v_user_dim5_b_tab,v_user_dim5_col,v_user_dim5_dc_col
1975 FROM fem_tab_columns_b C,
1976 fem_xdim_dimensions X
1977 WHERE C.table_name = g_data_table
1978 AND C.column_name = 'USER_DIM5_ID'
1979 AND C.dimension_id = X.dimension_id;
1980
1981 g_user_dim5_sql :=
1982 'SELECT '||v_user_dim5_col||
1983 ' FROM '||v_user_dim5_b_tab||' B,'||
1984 ' fem_global_vs_combo_defs G'||
1985 ' WHERE G.global_vs_combo_id = '||g_gvc_id||
1986 ' AND G.value_set_id = B.value_set_id'||
1987 ' AND B.'||v_user_dim5_dc_col||' = :b_dc_val'||
1988 ' AND B.enabled_flag = ''Y''';
1989 EXCEPTION
1990 WHEN no_data_found THEN g_user_dim5_sql := '';
1991 END;
1992
1993 BEGIN
1994 SELECT member_b_table_name,member_col,member_display_code_col
1995 INTO v_user_dim6_b_tab,v_user_dim6_col,v_user_dim6_dc_col
1996 FROM fem_tab_columns_b C,
1997 fem_xdim_dimensions X
1998 WHERE C.table_name = g_data_table
1999 AND C.column_name = 'USER_DIM6_ID'
2000 AND C.dimension_id = X.dimension_id;
2001
2002 g_user_dim6_sql :=
2003 'SELECT '||v_user_dim6_col||
2004 ' FROM '||v_user_dim6_b_tab||' B,'||
2005 ' fem_global_vs_combo_defs G'||
2006 ' WHERE G.global_vs_combo_id = '||g_gvc_id||
2007 ' AND G.value_set_id = B.value_set_id'||
2008 ' AND B.'||v_user_dim6_dc_col||' = :b_dc_val'||
2009 ' AND B.enabled_flag = ''Y''';
2010 EXCEPTION
2011 WHEN no_data_found THEN g_user_dim6_sql := '';
2012 END;
2013
2014 BEGIN
2015 SELECT member_b_table_name,member_col,member_display_code_col
2016 INTO v_user_dim7_b_tab,v_user_dim7_col,v_user_dim7_dc_col
2017 FROM fem_tab_columns_b C,
2018 fem_xdim_dimensions X
2019 WHERE C.table_name = g_data_table
2020 AND C.column_name = 'USER_DIM7_ID'
2021 AND C.dimension_id = X.dimension_id;
2022
2023 g_user_dim7_sql :=
2024 'SELECT '||v_user_dim7_col||
2025 ' FROM '||v_user_dim7_b_tab||' B,'||
2026 ' fem_global_vs_combo_defs G'||
2030 ' AND B.enabled_flag = ''Y''';
2027 ' WHERE G.global_vs_combo_id = '||g_gvc_id||
2028 ' AND G.value_set_id = B.value_set_id'||
2029 ' AND B.'||v_user_dim7_dc_col||' = :b_dc_val'||
2031 EXCEPTION
2032 WHEN no_data_found THEN g_user_dim7_sql := '';
2033 END;
2034
2035 BEGIN
2036 SELECT member_b_table_name,member_col,member_display_code_col
2037 INTO v_user_dim8_b_tab,v_user_dim8_col,v_user_dim8_dc_col
2038 FROM fem_tab_columns_b C,
2039 fem_xdim_dimensions X
2040 WHERE C.table_name = g_data_table
2041 AND C.column_name = 'USER_DIM8_ID'
2042 AND C.dimension_id = X.dimension_id;
2043
2044 g_user_dim8_sql :=
2045 'SELECT '||v_user_dim8_col||
2046 ' FROM '||v_user_dim8_b_tab||' B,'||
2047 ' fem_global_vs_combo_defs G'||
2048 ' WHERE G.global_vs_combo_id = '||g_gvc_id||
2049 ' AND G.value_set_id = B.value_set_id'||
2050 ' AND B.'||v_user_dim8_dc_col||' = :b_dc_val'||
2051 ' AND B.enabled_flag = ''Y''';
2052 EXCEPTION
2053 WHEN no_data_found THEN g_user_dim8_sql := '';
2054 END;
2055
2056 BEGIN
2057 SELECT member_b_table_name,member_col,member_display_code_col
2058 INTO v_user_dim9_b_tab,v_user_dim9_col,v_user_dim9_dc_col
2059 FROM fem_tab_columns_b C,
2060 fem_xdim_dimensions X
2061 WHERE C.table_name = g_data_table
2062 AND C.column_name = 'USER_DIM9_ID'
2063 AND C.dimension_id = X.dimension_id;
2064
2065 g_user_dim9_sql :=
2066 'SELECT '||v_user_dim9_col||
2067 ' FROM '||v_user_dim9_b_tab||' B,'||
2068 ' fem_global_vs_combo_defs G'||
2069 ' WHERE G.global_vs_combo_id = '||g_gvc_id||
2070 ' AND G.value_set_id = B.value_set_id'||
2071 ' AND B.'||v_user_dim9_dc_col||' = :b_dc_val'||
2072 ' AND B.enabled_flag = ''Y''';
2073 EXCEPTION
2074 WHEN no_data_found THEN g_user_dim9_sql := '';
2075 END;
2076
2077 BEGIN
2078 SELECT member_b_table_name,member_col,member_display_code_col
2079 INTO v_user_dim10_b_tab,v_user_dim10_col,v_user_dim10_dc_col
2080 FROM fem_tab_columns_b C,
2081 fem_xdim_dimensions X
2082 WHERE C.table_name = g_data_table
2083 AND C.column_name = 'USER_DIM10_ID'
2084 AND C.dimension_id = X.dimension_id;
2085
2086 g_user_dim10_sql :=
2087 'SELECT '||v_user_dim10_col||
2088 ' FROM '||v_user_dim10_b_tab||' B,'||
2089 ' fem_global_vs_combo_defs G'||
2090 ' WHERE G.global_vs_combo_id = '||g_gvc_id||
2091 ' AND G.value_set_id = B.value_set_id'||
2092 ' AND B.'||v_user_dim10_dc_col||' = :b_dc_val'||
2093 ' AND B.enabled_flag = ''Y''';
2094 EXCEPTION
2095 WHEN no_data_found THEN g_user_dim10_sql := '';
2096 END;
2097
2098 ----------------------
2099 -- Build Engine SQL --
2100 ----------------------
2101 g_select_stmt :=
2102 'SELECT rowid,'||
2103 ' cctr_org_display_code,'||
2104 ' currency_code,'||
2105 ' financial_elem_display_code,'||
2106 ' product_display_code,'||
2107 ' natural_account_display_code,'||
2108 ' channel_display_code,'||
2109 ' line_item_display_code,'||
2110 ' project_display_code,'||
2111 ' customer_display_code,'||
2112 ' entity_display_code,'||
2113 ' geography_display_code,'||
2114 ' task_display_code,'||
2115 ' intercompany_display_code,'||
2116 ' user_dim1_display_code,'||
2117 ' user_dim2_display_code,'||
2118 ' user_dim3_display_code,'||
2119 ' user_dim4_display_code,'||
2120 ' user_dim5_display_code,'||
2121 ' user_dim6_display_code,'||
2122 ' user_dim7_display_code,'||
2123 ' user_dim8_display_code,'||
2124 ' user_dim9_display_code,'||
2125 ' user_dim10_display_code,'||
2126 ' numeric_measure,'||
2127 ' alphanumeric_measure,'||
2128 ' date_measure,'||
2129 ' status'||
2130 ' FROM '||g_data_t_table||
2131 ' WHERE '||g_condition||
2132 ' AND {{data_slice}} ';
2133
2134 FEM_ENGINES_PKG.TECH_MESSAGE
2135 (p_severity => c_log_level_3,
2136 p_module => v_block||'.End{410}',
2137 p_msg_text => 'End FEM_DATAX_LOADER.Pre_Process');
2138
2139 END Pre_Process;
2140
2141
2142 /***************************************************************************
2143 ***************************************************************************
2144 * *
2145 * ======================= *
2146 * Process Rows *
2147 * ======================= *
2148 * *
2149 ***************************************************************************
2150 **************************************************************************/
2151
2152 PROCEDURE Process_Rows (
2153 p_eng_sql IN VARCHAR2,
2154 p_slc_pred IN VARCHAR2,
2155 p_proc_num IN NUMBER,
2156 p_part_code IN NUMBER,
2160 p_ledger_id IN NUMBER,
2157 p_fetch_limit IN NUMBER,
2158 p_data_table IN VARCHAR2,
2159 p_object_id IN NUMBER,
2161 p_dataset_cd IN NUMBER,
2162 p_cal_per_id IN NUMBER,
2163 p_source_cd IN NUMBER,
2164 p_exec_mode IN VARCHAR2,
2165 p_req_id IN NUMBER,
2166 p_cctr_org_sql IN VARCHAR2,
2167 p_fin_elem_sql IN VARCHAR2,
2168 p_product_sql IN VARCHAR2,
2169 p_nat_acct_sql IN VARCHAR2,
2170 p_channel_sql IN VARCHAR2,
2171 p_line_item_sql IN VARCHAR2,
2172 p_project_sql IN VARCHAR2,
2173 p_customer_sql IN VARCHAR2,
2174 p_entity_sql IN VARCHAR2,
2175 p_geography_sql IN VARCHAR2,
2176 p_task_sql IN VARCHAR2,
2177 p_interco_sql IN VARCHAR2,
2178 p_user_dim1_sql IN VARCHAR2,
2179 p_user_dim2_sql IN VARCHAR2,
2180 p_user_dim3_sql IN VARCHAR2,
2181 p_user_dim4_sql IN VARCHAR2,
2182 p_user_dim5_sql IN VARCHAR2,
2183 p_user_dim6_sql IN VARCHAR2,
2184 p_user_dim7_sql IN VARCHAR2,
2185 p_user_dim8_sql IN VARCHAR2,
2186 p_user_dim9_sql IN VARCHAR2,
2187 p_user_dim10_sql IN VARCHAR2
2188 )
2189 IS
2190
2191 ---------------------
2192 -- Local variables --
2193 ---------------------
2194 v_data_t_table VARCHAR2(30) := p_data_table||'_T';
2195
2196 v_block VARCHAR2(160);
2197
2198 v_slc_id NUMBER;
2199 v_part_name VARCHAR2(30);
2200 p_part_name VARCHAR2(30);
2201
2202 v_slc_num NUMBER;
2203
2204 v_slc_val1 VARCHAR2(240);
2205 v_slc_val2 VARCHAR2(240);
2206 v_slc_val3 VARCHAR2(240);
2207 v_slc_val4 VARCHAR2(240);
2208 v_num_vals NUMBER;
2209
2210 v_fetch_limit NUMBER;
2211
2212 v_rerun_error NUMBER := 0;
2213
2214 v_rows_processed NUMBER;
2215 v_rows_rejected NUMBER;
2216 v_rows_loaded NUMBER;
2217
2218 v_varchar VARCHAR2(150);
2219
2220 v_status NUMBER;
2221 v_message VARCHAR2(4000);
2222
2223 -------------------------------------
2224 -- Declare bulk collection columns --
2225 -------------------------------------
2226
2227 v_last_row NUMBER;
2228
2229 TYPE rowid_type IS TABLE OF ROWID INDEX BY BINARY_INTEGER;
2230 t_rowid rowid_type;
2231
2232 TYPE number_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
2233 t_num_measure number_type;
2234 t_cctr_org_id number_type;
2235 t_fin_elem_id number_type;
2236 t_product_id number_type;
2237 t_nat_acct_id number_type;
2238 t_channel_id number_type;
2239 t_line_item_id number_type;
2240 t_project_id number_type;
2241 t_customer_id number_type;
2242 t_entity_id number_type;
2243 t_geography_id number_type;
2244 t_task_id number_type;
2245 t_interco_id number_type;
2246 t_user_dim1_id number_type;
2247 t_user_dim2_id number_type;
2248 t_user_dim3_id number_type;
2249 t_user_dim4_id number_type;
2250 t_user_dim5_id number_type;
2251 t_user_dim6_id number_type;
2252 t_user_dim7_id number_type;
2253 t_user_dim8_id number_type;
2254 t_user_dim9_id number_type;
2255 t_user_dim10_id number_type;
2256
2257 TYPE date_type IS TABLE OF DATE INDEX BY BINARY_INTEGER;
2258 t_date_measure date_type;
2259
2260 TYPE varchar2_std_type IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
2261 t_status varchar2_std_type;
2262 t_currency_cd varchar2_std_type;
2263
2264 TYPE display_code_type IS TABLE OF VARCHAR2(150) INDEX BY BINARY_INTEGER;
2265 t_cctr_org_dc display_code_type;
2266 t_fin_elem_dc display_code_type;
2267 t_product_dc display_code_type;
2268 t_nat_acct_dc display_code_type;
2269 t_channel_dc display_code_type;
2270 t_line_item_dc display_code_type;
2271 t_project_dc display_code_type;
2272 t_customer_dc display_code_type;
2273 t_entity_dc display_code_type;
2274 t_geography_dc display_code_type;
2275 t_task_dc display_code_type;
2276 t_interco_dc display_code_type;
2277 t_user_dim1_dc display_code_type;
2278 t_user_dim2_dc display_code_type;
2279 t_user_dim3_dc display_code_type;
2280 t_user_dim4_dc display_code_type;
2281 t_user_dim5_dc display_code_type;
2282 t_user_dim6_dc display_code_type;
2283 t_user_dim7_dc display_code_type;
2284 t_user_dim8_dc display_code_type;
2285 t_user_dim9_dc display_code_type;
2286 t_user_dim10_dc display_code_type;
2287 t_alpha_measure display_code_type;
2288
2289 x_select_stmt VARCHAR2(32767);
2290 x_insert_stmt VARCHAR2(32767);
2291 x_update_stmt VARCHAR2(32767);
2292 x_delete_stmt VARCHAR2(32767);
2293
2294 TYPE cv_curs IS REF CURSOR;
2295 cv_get_rows cv_curs;
2296
2297 ---------------------
2298 -- Execution Block --
2299 ---------------------
2300 BEGIN
2301
2302 -- DBMS_SESSION.SET_SQL_TRACE (sql_trace => TRUE);
2303
2304 v_block := 'fem.plsql.fem_datax_loader_pkg.process_rows'||
2305 '{p'||p_proc_num||'}';
2306
2310 p_msg_text => 'Begin FEM_DATAX_LOADER.Process_Rows');
2307 FEM_ENGINES_PKG.TECH_MESSAGE
2308 (p_severity => c_log_level_3,
2309 p_module => v_block||'.Begin{800}',
2311 FEM_ENGINES_PKG.TECH_MESSAGE
2312 (p_severity => c_log_level_1,
2313 p_module => v_block||'.p_eng_sql{801}',
2314 p_msg_text => p_eng_sql);
2315 FEM_ENGINES_PKG.TECH_MESSAGE
2316 (p_severity => c_log_level_2,
2317 p_module => v_block||'.p_slc_pred{802}',
2318 p_msg_text => p_slc_pred);
2319 FEM_ENGINES_PKG.TECH_MESSAGE
2320 (p_severity => c_log_level_2,
2321 p_module => v_block||'.p_proc_num{803}',
2322 p_msg_text => p_proc_num);
2323 FEM_ENGINES_PKG.TECH_MESSAGE
2324 (p_severity => c_log_level_2,
2325 p_module => v_block||'.p_part_code{804}',
2326 p_msg_text => p_part_code);
2327 FEM_ENGINES_PKG.TECH_MESSAGE
2328 (p_severity => c_log_level_2,
2329 p_module => v_block||'.p_fetch_limit{805}',
2330 p_msg_text => p_fetch_limit);
2331 FEM_ENGINES_PKG.TECH_MESSAGE
2332 (p_severity => c_log_level_2,
2333 p_module => v_block||'.p_data_table{806}',
2334 p_msg_text => p_data_table);
2335 FEM_ENGINES_PKG.TECH_MESSAGE
2336 (p_severity => c_log_level_2,
2337 p_module => v_block||'.p_object_id{807}',
2338 p_msg_text => p_object_id);
2339 FEM_ENGINES_PKG.TECH_MESSAGE
2340 (p_severity => c_log_level_2,
2341 p_module => v_block||'.p_ledger_id{808}',
2342 p_msg_text => p_ledger_id);
2343 FEM_ENGINES_PKG.TECH_MESSAGE
2344 (p_severity => c_log_level_2,
2345 p_module => v_block||'.p_dataset_cd{809}',
2346 p_msg_text => p_dataset_cd);
2347 FEM_ENGINES_PKG.TECH_MESSAGE
2348 (p_severity => c_log_level_2,
2349 p_module => v_block||'.p_cal_per_id{810}',
2350 p_msg_text => p_cal_per_id);
2351 FEM_ENGINES_PKG.TECH_MESSAGE
2352 (p_severity => c_log_level_2,
2353 p_module => v_block||'.p_source_cd{811}',
2354 p_msg_text => p_source_cd);
2355 FEM_ENGINES_PKG.TECH_MESSAGE
2356 (p_severity => c_log_level_2,
2357 p_module => v_block||'.p_req_id{812}',
2358 p_msg_text => p_req_id);
2359 FEM_ENGINES_PKG.TECH_MESSAGE
2360 (p_severity => c_log_level_1,
2361 p_module => v_block||'.p_cctr_org_sql{813.1}',
2362 p_msg_text => p_cctr_org_sql);
2363 FEM_ENGINES_PKG.TECH_MESSAGE
2364 (p_severity => c_log_level_1,
2365 p_module => v_block||'.p_fin_elem_sql{813.2}',
2366 p_msg_text => p_fin_elem_sql);
2367 FEM_ENGINES_PKG.TECH_MESSAGE
2368 (p_severity => c_log_level_1,
2369 p_module => v_block||'.p_product_sql{813.3}',
2370 p_msg_text => p_product_sql);
2371 FEM_ENGINES_PKG.TECH_MESSAGE
2372 (p_severity => c_log_level_1,
2373 p_module => v_block||'.p_nat_acct_sql{813.4}',
2374 p_msg_text => p_nat_acct_sql);
2375 FEM_ENGINES_PKG.TECH_MESSAGE
2376 (p_severity => c_log_level_1,
2377 p_module => v_block||'.p_channel_sql{813.5}',
2378 p_msg_text => p_channel_sql);
2379 FEM_ENGINES_PKG.TECH_MESSAGE
2380 (p_severity => c_log_level_1,
2381 p_module => v_block||'.p_line_item_sql{813.6}',
2382 p_msg_text => p_line_item_sql);
2383 FEM_ENGINES_PKG.TECH_MESSAGE
2384 (p_severity => c_log_level_1,
2385 p_module => v_block||'.p_project_sql{813.7}',
2386 p_msg_text => p_project_sql);
2387 FEM_ENGINES_PKG.TECH_MESSAGE
2388 (p_severity => c_log_level_1,
2389 p_module => v_block||'.p_customer_sql{813.8}',
2390 p_msg_text => p_customer_sql);
2391 FEM_ENGINES_PKG.TECH_MESSAGE
2392 (p_severity => c_log_level_1,
2393 p_module => v_block||'.p_entity_sql{813.9}',
2394 p_msg_text => p_entity_sql);
2395
2396 v_status := 0;
2397 v_message := 'Data loaded successfully';
2398
2399 IF (p_fetch_limit IS NOT NULL)
2400 THEN
2401 v_fetch_limit := p_fetch_limit;
2402 ELSE
2403 v_fetch_limit := c_fetch_limit;
2404 END IF;
2405
2406 FEM_ENGINES_PKG.TECH_MESSAGE
2407 (p_severity => c_log_level_1,
2408 p_module => v_block||'.v_fetch_limit{820}',
2409 p_msg_text => v_fetch_limit);
2410
2411 IF (p_slc_pred IS NOT NULL)
2412 THEN
2413 x_select_stmt := REPLACE(p_eng_sql,'{{data_slice}}',p_slc_pred);
2414 ELSE
2415 x_select_stmt := REPLACE(p_eng_sql,'{{data_slice}}','1=1');
2416 END IF;
2417
2418 FEM_ENGINES_PKG.TECH_MESSAGE
2419 (p_severity => c_log_level_1,
2420 p_module => v_block||'.x_select_stmt{821}',
2421 p_msg_text => x_select_stmt);
2422
2423 ------------------------------------
2424 -- Build Dynamic INSERT Statement --
2425 ------------------------------------
2426 x_insert_stmt :=
2427 'INSERT INTO '||p_data_table||
2428 '(created_by_object_id,'||
2429 ' dataset_code,'||
2430 ' cal_period_id,'||
2431 ' source_system_code,'||
2432 ' ledger_id,'||
2433 ' company_cost_center_org_id,'||
2434 ' currency_code,'||
2435 ' financial_elem_id,'||
2436 ' product_id,'||
2437 ' natural_account_id,'||
2438 ' channel_id,'||
2439 ' line_item_id,'||
2440 ' project_id,'||
2441 ' customer_id,'||
2442 ' entity_id,'||
2443 ' geography_id,'||
2444 ' task_id,'||
2445 ' intercompany_id,'||
2446 ' user_dim1_id,'||
2447 ' user_dim2_id,'||
2448 ' user_dim3_id,'||
2449 ' user_dim4_id,'||
2453 ' user_dim8_id,'||
2450 ' user_dim5_id,'||
2451 ' user_dim6_id,'||
2452 ' user_dim7_id,'||
2454 ' user_dim9_id,'||
2455 ' user_dim10_id,'||
2456 ' created_by_request_id,'||
2457 ' last_updated_by_request_id,'||
2458 ' last_updated_by_object_id,'||
2459 ' numeric_measure,'||
2460 ' alphanumeric_measure,'||
2461 ' date_measure)'||
2462 ' SELECT :b_object_id,'||
2463 ' :b_dataset_cd,'||
2464 ' :b_cal_per_id,'||
2465 ' :b_source_cd,'||
2466 ' :b_ledger_id,'||
2467 ' :b_cctr_org_id,'||
2468 ' :b_currency_cd,'||
2469 ' :b_fin_elem_dc,'||
2470 ' :b_product_id,'||
2471 ' :b_nat_acct_id,'||
2472 ' :b_channel_id,'||
2473 ' :b_line_item_id,'||
2474 ' :b_project_id,'||
2475 ' :b_customer_id,'||
2476 ' :b_entity_id,'||
2477 ' :b_geography_id,'||
2478 ' :b_task_id,'||
2479 ' :b_interco_id,'||
2480 ' :b_user_dim1_id,'||
2481 ' :b_user_dim2_id,'||
2482 ' :b_user_dim3_id,'||
2483 ' :b_user_dim4_id,'||
2484 ' :b_user_dim5_id,'||
2485 ' :b_user_dim6_id,'||
2486 ' :b_user_dim7_id,'||
2487 ' :b_user_dim8_id,'||
2488 ' :b_user_dim9_id,'||
2489 ' :b_user_dim10_id,'||
2490 ' :b1_req_id,'||
2491 ' :b2_req_id,'||
2492 ' :b_object_id,'||
2493 ' :b_num_measure,'||
2494 ' :b_alpha_measure,'||
2495 ' :b_date_measure'||
2496 ' FROM dual'||
2497 ' WHERE :b_status = ''LOAD''';
2498
2499 -----------------------------------------------------------------
2500 -- Build Dynamic UPDATE Statement to Update STATUS in FEM_DATAn_T
2501 -- Where Rows have Invalid Dimension Values
2502 -----------------------------------------------------------------
2503 x_update_stmt :=
2504 'UPDATE '||v_data_t_table||
2505 ' SET status = :b1_status'||
2506 ' WHERE rowid = :b_rowid';
2507
2508 -----------------------------------------------------------------
2509 -- Build Dynamic DELETE Statement to Delete Rows
2510 -- from FEM_DATAn_T that were Loaded into FEM_DATAn
2511 -----------------------------------------------------------------
2512 x_delete_stmt :=
2513 'DELETE FROM '||v_data_t_table||
2514 ' WHERE rowid = :b_rowid'||
2515 ' AND :b_status = ''LOAD''';
2516
2517 ------------------------------
2518 -- Loop through data slices --
2519 ------------------------------
2520 LOOP
2521
2522 FEM_Multi_Proc_Pkg.Get_Data_Slice(
2523 x_slc_id => v_slc_id,
2524 x_slc_val1 => v_slc_val1,
2525 x_slc_val2 => v_slc_val2,
2526 x_slc_val3 => v_slc_val3,
2527 x_slc_val4 => v_slc_val4,
2528 x_num_vals => v_num_vals,
2529 x_part_name => p_part_name,
2530 p_req_id => p_req_id,
2531 p_proc_num => p_proc_num);
2532
2533 FEM_ENGINES_PKG.TECH_MESSAGE
2534 (p_severity => c_log_level_2,
2535 p_module => v_block||'.v_slc_id{822.1}',
2536 p_msg_text => v_slc_id);
2537 FEM_ENGINES_PKG.TECH_MESSAGE
2538 (p_severity => c_log_level_2,
2539 p_module => v_block||'.v_num_vals{822.2}',
2540 p_msg_text => v_num_vals);
2541 FEM_ENGINES_PKG.TECH_MESSAGE
2542 (p_severity => c_log_level_2,
2543 p_module => v_block||'.v_slc_val1{823.1}',
2544 p_msg_text => v_slc_val1);
2545 FEM_ENGINES_PKG.TECH_MESSAGE
2546 (p_severity => c_log_level_2,
2547 p_module => v_block||'.v_slc_val2{823.2}',
2548 p_msg_text => v_slc_val2);
2549 FEM_ENGINES_PKG.TECH_MESSAGE
2550 (p_severity => c_log_level_2,
2551 p_module => v_block||'.v_slc_val3{823.3}',
2552 p_msg_text => v_slc_val3);
2553 FEM_ENGINES_PKG.TECH_MESSAGE
2554 (p_severity => c_log_level_2,
2555 p_module => v_block||'.v_slc_val4{823.4}',
2556 p_msg_text => v_slc_val4);
2557 FEM_ENGINES_PKG.TECH_MESSAGE
2558 (p_severity => c_log_level_2,
2559 p_module => v_block||'.p_part_name{824}',
2560 p_msg_text => p_part_name);
2561
2562 EXIT WHEN (v_slc_id IS NULL);
2563
2564 IF (p_part_code > 0) AND
2565 (NVL(v_part_name,'null') <> NVL(v_part_name,'null'))
2566 THEN
2567 v_part_name := p_part_name;
2568 x_select_stmt := REPLACE(x_select_stmt,'{{table_partition}}',v_part_name);
2569 END IF;
2570
2571 IF (v_num_vals = 4)
2572 THEN
2573 OPEN cv_get_rows FOR
2574 x_select_stmt
2575 USING v_slc_val1,v_slc_val2,v_slc_val3,v_slc_val4;
2576 ELSIF (v_num_vals = 3)
2577 THEN
2578 OPEN cv_get_rows FOR
2579 x_select_stmt
2580 USING v_slc_val1,v_slc_val2,v_slc_val3;
2581 ELSIF (v_num_vals = 2)
2582 THEN
2583 OPEN cv_get_rows FOR
2584 x_select_stmt
2585 USING v_slc_val1,v_slc_val2;
2586 ELSIF (v_num_vals = 1)
2587 THEN
2588 OPEN cv_get_rows FOR
2589 x_select_stmt
2590 USING v_slc_val1;
2591 ELSE
2592 EXIT;
2593 END IF;
2594
2595 ---------------------------------
2596 -- Loop through DATA_T Records --
2597 ---------------------------------
2598 v_rows_processed := 0;
2602 LOOP
2599 v_rows_rejected := 0;
2600 v_rows_loaded := 0;
2601
2603
2604 FEM_ENGINES_PKG.TECH_MESSAGE
2605 (p_severity => c_log_level_3,
2606 p_module => v_block||'.Begin fetch{830}',
2607 p_msg_text => v_last_row);
2608
2609 -------------------------------------------
2610 -- Bulk Fetch Rows from FEM_DATAn_T Table
2611 -------------------------------------------
2612 FETCH cv_get_rows BULK COLLECT INTO
2613 t_rowid,
2614 t_cctr_org_dc,
2615 t_currency_cd,
2616 t_fin_elem_dc,
2617 t_product_dc,
2618 t_nat_acct_dc,
2619 t_channel_dc,
2620 t_line_item_dc,
2621 t_project_dc,
2622 t_customer_dc,
2623 t_entity_dc,
2624 t_geography_dc,
2625 t_task_dc,
2626 t_interco_dc,
2627 t_user_dim1_dc,
2628 t_user_dim2_dc,
2629 t_user_dim3_dc,
2630 t_user_dim4_dc,
2631 t_user_dim5_dc,
2632 t_user_dim6_dc,
2633 t_user_dim7_dc,
2634 t_user_dim8_dc,
2635 t_user_dim9_dc,
2636 t_user_dim10_dc,
2637 t_num_measure,
2638 t_alpha_measure,
2639 t_date_measure,
2640 t_status
2641 LIMIT v_fetch_limit;
2642
2643 v_last_row := t_status.LAST;
2644
2645 IF (v_last_row IS NULL)
2646 THEN
2647 EXIT;
2648 END IF;
2649
2650 v_rows_processed := cv_get_rows%ROWCOUNT;
2651
2652 FEM_ENGINES_PKG.TECH_MESSAGE
2653 (p_severity => c_log_level_2,
2654 p_module => v_block||'.Rows this fetch{831}',
2655 p_msg_text => v_last_row);
2656 FEM_ENGINES_PKG.TECH_MESSAGE
2657 (p_severity => c_log_level_2,
2658 p_module => v_block||'.Total rows fetched{832}',
2659 p_msg_text => v_rows_processed);
2660
2661 -------------------------------
2662 -- Validate Dimension Values --
2663 -------------------------------
2664
2665 FEM_ENGINES_PKG.TECH_MESSAGE
2666 (p_severity => c_log_level_3,
2667 p_module => v_block||'.Begin validation{833}',
2668 p_msg_text => v_last_row);
2669
2670 FOR i IN 1..v_last_row
2671 LOOP
2672 t_status(i) := 'LOAD';
2673
2674 IF (t_currency_cd(i) IS NOT NULL)
2675 THEN
2676 BEGIN
2677 SELECT currency_code
2678 INTO v_varchar
2679 FROM fem_currencies_vl
2680 WHERE currency_code = t_currency_cd(i)
2681 AND enabled_flag = 'Y';
2682 EXCEPTION
2683 WHEN no_data_found THEN
2684 t_status(i) := 'INVALID_CURRENCY';
2685 t_currency_cd(i) := '';
2686 END;
2687 ELSE
2688 t_currency_cd(i) := '';
2689 END IF;
2690
2691 IF (t_cctr_org_dc(i) IS NOT NULL) AND
2692 (p_cctr_org_sql IS NOT NULL)
2693 THEN
2694 BEGIN
2695 EXECUTE IMMEDIATE p_cctr_org_sql
2696 INTO t_cctr_org_id(i)
2697 USING t_cctr_org_dc(i);
2698 EXCEPTION
2699 WHEN no_data_found THEN
2700 t_status(i) := 'INVALID_CCTR_ORG';
2701 t_cctr_org_id(i) := '';
2702 END;
2703 ELSE
2704 t_cctr_org_id(i) := '';
2705 END IF;
2706
2707 IF (t_fin_elem_dc(i) IS NOT NULL) AND
2708 (p_fin_elem_sql IS NOT NULL)
2709 THEN
2710 BEGIN
2711 EXECUTE IMMEDIATE p_fin_elem_sql
2712 INTO t_fin_elem_id(i)
2713 USING t_fin_elem_dc(i);
2714 EXCEPTION
2715 WHEN no_data_found THEN
2716 t_status(i) := 'INVALID_FIN_ELEM';
2717 t_fin_elem_id(i) := '';
2718 END;
2719 ELSE
2720 t_fin_elem_id(i) := '';
2721 END IF;
2722
2723 IF (t_product_dc(i) IS NOT NULL) AND
2724 (p_product_sql IS NOT NULL)
2725 THEN
2726 BEGIN
2727 EXECUTE IMMEDIATE p_product_sql
2728 INTO t_product_id(i)
2729 USING t_product_dc(i);
2730 EXCEPTION
2731 WHEN no_data_found THEN
2732 t_status(i) := 'INVALID_PRODUCT';
2733 t_product_id(i) := '';
2734 END;
2735 ELSE
2736 t_product_id(i) := '';
2737 END IF;
2738
2739 IF (t_nat_acct_dc(i) IS NOT NULL) AND
2740 (p_nat_acct_sql IS NOT NULL)
2741 THEN
2742 BEGIN
2743 EXECUTE IMMEDIATE p_nat_acct_sql
2744 INTO t_nat_acct_id(i)
2745 USING t_nat_acct_dc(i);
2746 EXCEPTION
2747 WHEN no_data_found THEN
2748 t_status(i) := 'INVALID_NAT_ACCT';
2749 t_nat_acct_id(i) := '';
2750 END;
2751 ELSE
2752 t_nat_acct_id(i) := '';
2753 END IF;
2754
2755 IF (t_channel_dc(i) IS NOT NULL) AND
2756 (p_channel_sql IS NOT NULL)
2757 THEN
2758 BEGIN
2762 EXCEPTION
2759 EXECUTE IMMEDIATE p_channel_sql
2760 INTO t_channel_id(i)
2761 USING t_channel_dc(i);
2763 WHEN no_data_found THEN
2764 t_status(i) := 'INVALID_CHANNEL';
2765 t_channel_id(i) := '';
2766 END;
2767 ELSE
2768 t_channel_id(i) := '';
2769 END IF;
2770
2771 IF (t_line_item_dc(i) IS NOT NULL) AND
2772 (p_line_item_sql IS NOT NULL)
2773 THEN
2774 BEGIN
2775 EXECUTE IMMEDIATE p_line_item_sql
2776 INTO t_line_item_id(i)
2777 USING t_line_item_dc(i);
2778 EXCEPTION
2779 WHEN no_data_found THEN
2780 t_status(i) := 'INVALID_LINE_ITEM';
2781 t_line_item_id(i) := '';
2782 END;
2783 ELSE
2784 t_line_item_id(i) := '';
2785 END IF;
2786
2787 IF (t_project_dc(i) IS NOT NULL) AND
2788 (p_project_sql IS NOT NULL)
2789 THEN
2790 BEGIN
2791 EXECUTE IMMEDIATE p_project_sql
2792 INTO t_project_id(i)
2793 USING t_project_dc(i);
2794 EXCEPTION
2795 WHEN no_data_found THEN
2796 t_status(i) := 'INVALID_PROJECT';
2797 t_project_id(i) := '';
2798 END;
2799 ELSE
2800 t_project_id(i) := '';
2801 END IF;
2802
2803 IF (t_customer_dc(i) IS NOT NULL) AND
2804 (p_customer_sql IS NOT NULL)
2805 THEN
2806 BEGIN
2807 EXECUTE IMMEDIATE p_customer_sql
2808 INTO t_customer_id(i)
2809 USING t_customer_dc(i);
2810 EXCEPTION
2811 WHEN no_data_found THEN
2812 t_status(i) := 'INVALID_CUSTOMER';
2813 t_customer_id(i) := '';
2814 END;
2815 ELSE
2816 t_customer_id(i) := '';
2817 END IF;
2818
2819 IF (t_entity_dc(i) IS NOT NULL) AND
2820 (p_entity_sql IS NOT NULL)
2821 THEN
2822 BEGIN
2823 EXECUTE IMMEDIATE p_entity_sql
2824 INTO t_entity_id(i)
2825 USING t_entity_dc(i);
2826 EXCEPTION
2827 WHEN no_data_found THEN
2828 t_status(i) := 'INVALID_ENTITY';
2829 t_entity_id(i) := '';
2830 END;
2831 ELSE
2832 t_entity_id(i) := '';
2833 END IF;
2834
2835 IF (t_geography_dc(i) IS NOT NULL) AND
2836 (p_geography_sql IS NOT NULL)
2837 THEN
2838 BEGIN
2839 EXECUTE IMMEDIATE p_geography_sql
2840 INTO t_geography_id(i)
2841 USING t_geography_dc(i);
2842 EXCEPTION
2843 WHEN no_data_found THEN
2844 t_status(i) := 'INVALID_GEOGRAPHY';
2845 t_geography_id(i) := '';
2846 END;
2847 ELSE
2848 t_geography_id(i) := '';
2849 END IF;
2850
2851 IF (t_task_dc(i) IS NOT NULL) AND
2852 (p_task_sql IS NOT NULL)
2853 THEN
2854 BEGIN
2855 EXECUTE IMMEDIATE p_task_sql
2856 INTO t_task_id(i)
2857 USING t_task_dc(i);
2858 EXCEPTION
2859 WHEN no_data_found THEN
2860 t_status(i) := 'INVALID_TASK';
2861 t_task_id(i) := '';
2862 END;
2863 ELSE
2864 t_task_id(i) := '';
2865 END IF;
2866
2867 IF (t_interco_dc(i) IS NOT NULL) AND
2868 (p_interco_sql IS NOT NULL)
2869 THEN
2870 BEGIN
2871 EXECUTE IMMEDIATE p_interco_sql
2872 INTO t_interco_id(i)
2873 USING t_interco_dc(i);
2874 EXCEPTION
2875 WHEN no_data_found THEN
2876 t_status(i) := 'INVALID_INTERCOMPANY';
2877 t_interco_id(i) := '';
2878 END;
2879 ELSE
2880 t_interco_id(i) := '';
2881 END IF;
2882
2883 IF (t_user_dim1_dc(i) IS NOT NULL) AND
2884 (p_user_dim1_sql IS NOT NULL)
2885 THEN
2886 BEGIN
2887 EXECUTE IMMEDIATE p_user_dim1_sql
2888 INTO t_user_dim1_id(i)
2889 USING t_user_dim1_dc(i);
2890 EXCEPTION
2891 WHEN no_data_found THEN
2892 t_status(i) := 'INVALID_USER_DIM1';
2893 t_user_dim1_id(i) := '';
2894 END;
2895 ELSE
2896 t_user_dim1_id(i) := '';
2897 END IF;
2898
2899 IF (t_user_dim2_dc(i) IS NOT NULL) AND
2900 (p_user_dim2_sql IS NOT NULL)
2901 THEN
2902 BEGIN
2903 EXECUTE IMMEDIATE p_user_dim2_sql
2904 INTO t_user_dim2_id(i)
2905 USING t_user_dim2_dc(i);
2906 EXCEPTION
2907 WHEN no_data_found THEN
2908 t_status(i) := 'INVALID_USER_DIM2';
2909 t_user_dim2_id(i) := '';
2910 END;
2911 ELSE
2912 t_user_dim2_id(i) := '';
2913 END IF;
2914
2918 BEGIN
2915 IF (t_user_dim3_dc(i) IS NOT NULL) AND
2916 (p_user_dim3_sql IS NOT NULL)
2917 THEN
2919 EXECUTE IMMEDIATE p_user_dim3_sql
2920 INTO t_user_dim3_id(i)
2921 USING t_user_dim3_dc(i);
2922 EXCEPTION
2923 WHEN no_data_found THEN
2924 t_status(i) := 'INVALID_USER_DIM3';
2925 t_user_dim3_id(i) := '';
2926 END;
2927 ELSE
2928 t_user_dim3_id(i) := '';
2929 END IF;
2930
2931 IF (t_user_dim4_dc(i) IS NOT NULL) AND
2932 (p_user_dim4_sql IS NOT NULL)
2933 THEN
2934 BEGIN
2935 EXECUTE IMMEDIATE p_user_dim4_sql
2936 INTO t_user_dim4_id(i)
2937 USING t_user_dim4_dc(i);
2938 EXCEPTION
2939 WHEN no_data_found THEN
2940 t_status(i) := 'INVALID_USER_DIM4';
2941 t_user_dim4_id(i) := '';
2942 END;
2943 ELSE
2944 t_user_dim4_id(i) := '';
2945 END IF;
2946
2947 IF (t_user_dim5_dc(i) IS NOT NULL) AND
2948 (p_user_dim5_sql IS NOT NULL)
2949 THEN
2950 BEGIN
2951 EXECUTE IMMEDIATE p_user_dim5_sql
2952 INTO t_user_dim5_id(i)
2953 USING t_user_dim5_dc(i);
2954 EXCEPTION
2955 WHEN no_data_found THEN
2956 t_status(i) := 'INVALID_USER_DIM5';
2957 t_user_dim5_id(i) := '';
2958 END;
2959 ELSE
2960 t_user_dim5_id(i) := '';
2961 END IF;
2962
2963 IF (t_user_dim6_dc(i) IS NOT NULL) AND
2964 (p_user_dim6_sql IS NOT NULL)
2965 THEN
2966 BEGIN
2967 EXECUTE IMMEDIATE p_user_dim6_sql
2968 INTO t_user_dim6_id(i)
2969 USING t_user_dim6_dc(i);
2970 EXCEPTION
2971 WHEN no_data_found THEN
2972 t_status(i) := 'INVALID_USER_DIM6';
2973 t_user_dim6_id(i) := '';
2974 END;
2975 ELSE
2976 t_user_dim6_id(i) := '';
2977 END IF;
2978
2979 IF (t_user_dim7_dc(i) IS NOT NULL) AND
2980 (p_user_dim7_sql IS NOT NULL)
2981 THEN
2982 BEGIN
2983 EXECUTE IMMEDIATE p_user_dim7_sql
2984 INTO t_user_dim7_id(i)
2985 USING t_user_dim7_dc(i);
2986 EXCEPTION
2987 WHEN no_data_found THEN
2988 t_status(i) := 'INVALID_USER_DIM7';
2989 t_user_dim7_id(i) := '';
2990 END;
2991 ELSE
2992 t_user_dim7_id(i) := '';
2993 END IF;
2994
2995 IF (t_user_dim8_dc(i) IS NOT NULL) AND
2996 (p_user_dim8_sql IS NOT NULL)
2997 THEN
2998 BEGIN
2999 EXECUTE IMMEDIATE p_user_dim8_sql
3000 INTO t_user_dim8_id(i)
3001 USING t_user_dim8_dc(i);
3002 EXCEPTION
3003 WHEN no_data_found THEN
3004 t_status(i) := 'INVALID_USER_DIM8';
3005 t_user_dim8_id(i) := '';
3006 END;
3007 ELSE
3008 t_user_dim8_id(i) := '';
3009 END IF;
3010
3011 IF (t_user_dim9_dc(i) IS NOT NULL) AND
3012 (p_user_dim9_sql IS NOT NULL)
3013 THEN
3014 BEGIN
3015 EXECUTE IMMEDIATE p_user_dim9_sql
3016 INTO t_user_dim9_id(i)
3017 USING t_user_dim9_dc(i);
3018 EXCEPTION
3019 WHEN no_data_found THEN
3020 t_status(i) := 'INVALID_USER_DIM9';
3021 t_user_dim9_id(i) := '';
3022 END;
3023 ELSE
3024 t_user_dim9_id(i) := '';
3025 END IF;
3026
3027 IF (t_user_dim10_dc(i) IS NOT NULL) AND
3028 (p_user_dim10_sql IS NOT NULL)
3029 THEN
3030 BEGIN
3031 EXECUTE IMMEDIATE p_user_dim10_sql
3032 INTO t_user_dim10_id(i)
3033 USING t_user_dim10_dc(i);
3034 EXCEPTION
3035 WHEN no_data_found THEN
3036 t_status(i) := 'INVALID_USER_DIM10';
3037 t_user_dim10_id(i) := '';
3038 END;
3039 ELSE
3040 t_user_dim10_id(i) := '';
3041 END IF;
3042
3043 IF (t_status(i) <> 'LOAD')
3044 THEN
3045 v_rows_rejected := v_rows_rejected + 1;
3046 END IF;
3047
3048 END LOOP;
3049
3050 -----------------------------------------
3051 -- Bulk Insert Rows in FEM_DATAn table --
3052 -----------------------------------------
3053
3054 FEM_ENGINES_PKG.TECH_MESSAGE
3055 (p_severity => c_log_level_3,
3056 p_module => v_block||'.Begin bulk insert{834}',
3057 p_msg_text => v_last_row);
3058
3059 FORALL i IN 1..v_last_row
3060 EXECUTE IMMEDIATE x_insert_stmt
3061 USING p_object_id,
3062 p_dataset_cd,
3063 p_cal_per_id,
3064 p_source_cd,
3065 p_ledger_id,
3066 t_cctr_org_id(i),
3067 t_currency_cd(i),
3068 t_fin_elem_id(i),
3069 t_product_id(i),
3070 t_nat_acct_id(i),
3074 t_customer_id(i),
3071 t_channel_id(i),
3072 t_line_item_id(i),
3073 t_project_id(i),
3075 t_entity_id(i),
3076 t_geography_id(i),
3077 t_task_id(i),
3078 t_interco_id(i),
3079 t_user_dim1_id(i),
3080 t_user_dim2_id(i),
3081 t_user_dim3_id(i),
3082 t_user_dim4_id(i),
3083 t_user_dim5_id(i),
3084 t_user_dim6_id(i),
3085 t_user_dim7_id(i),
3086 t_user_dim8_id(i),
3087 t_user_dim9_id(i),
3088 t_user_dim10_id(i),
3089 p_req_id,
3090 p_req_id,
3091 p_object_id,
3092 t_num_measure(i),
3093 t_alpha_measure(i),
3094 t_date_measure(i),
3095 t_status(i);
3096
3097 -----------------------------------
3098 -- Update Rows in FEM_DATAn_T Table
3099 -----------------------------------
3100 FEM_ENGINES_PKG.TECH_MESSAGE
3101 (p_severity => c_log_level_3,
3102 p_module => v_block||'.Begin bulk update{835}',
3103 p_msg_text => v_last_row);
3104
3105 FORALL i IN 1..v_last_row
3106 EXECUTE IMMEDIATE x_update_stmt
3107 USING t_status(i),
3108 t_rowid(i);
3109
3110 -------------------------------------
3111 -- Delete Rows from FEM_DATAn_T Table
3112 -------------------------------------
3113 FEM_ENGINES_PKG.TECH_MESSAGE
3114 (p_severity => c_log_level_3,
3115 p_module => v_block||'.Begin bulk delete{836}',
3116 p_msg_text => v_last_row);
3117
3118 FORALL i IN 1..v_last_row
3119 EXECUTE IMMEDIATE x_delete_stmt
3120 USING t_rowid(i),
3121 t_status(i);
3122
3123 --------------------------
3124 -- Commit the transaaction
3125 --------------------------
3126 COMMIT;
3127
3128 --------------------------------------------
3129 -- Delete Collections for Next Bulk Fetch --
3130 --------------------------------------------
3131 FEM_ENGINES_PKG.TECH_MESSAGE
3132 (p_severity => c_log_level_3,
3133 p_module => v_block||'.Begin array delete{837}',
3134 p_msg_text => v_last_row);
3135
3136 t_rowid.DELETE;
3137 t_cctr_org_dc.DELETE;
3138 t_currency_cd.DELETE;
3139 t_fin_elem_dc.DELETE;
3140 t_product_dc.DELETE;
3141 t_nat_acct_dc.DELETE;
3142 t_channel_dc.DELETE;
3143 t_line_item_dc.DELETE;
3144 t_project_dc.DELETE;
3145 t_customer_dc.DELETE;
3146 t_entity_dc.DELETE;
3147 t_geography_dc.DELETE;
3148 t_task_dc.DELETE;
3149 t_interco_dc.DELETE;
3150 t_user_dim1_dc.DELETE;
3151 t_user_dim2_dc.DELETE;
3152 t_user_dim3_dc.DELETE;
3156 t_user_dim7_dc.DELETE;
3153 t_user_dim4_dc.DELETE;
3154 t_user_dim5_dc.DELETE;
3155 t_user_dim6_dc.DELETE;
3157 t_user_dim8_dc.DELETE;
3158 t_user_dim9_dc.DELETE;
3159 t_user_dim10_dc.DELETE;
3160 t_num_measure.DELETE;
3161 t_alpha_measure.DELETE;
3162 t_date_measure.DELETE;
3163 t_status.DELETE;
3164
3165 FEM_ENGINES_PKG.TECH_MESSAGE
3166 (p_severity => c_log_level_3,
3167 p_module => v_block||'.End fetch{838}',
3168 p_msg_text => v_last_row);
3169
3170 END LOOP;
3171 CLOSE cv_get_rows;
3172
3173 FEM_ENGINES_PKG.TECH_MESSAGE
3174 (p_severity => c_log_level_3,
3175 p_module => v_block||'.Close fetch cursor{839}',
3176 p_msg_text => v_rows_processed);
3177
3178 ---------------------
3179 -- Post Statistics --
3180 ---------------------
3181
3182 v_rows_loaded := v_rows_processed - v_rows_rejected;
3183
3184 FEM_ENGINES_PKG.TECH_MESSAGE
3185 (p_severity => c_log_level_2,
3186 p_module => v_block||'.v_rows_processed{840}',
3187 p_msg_text => v_rows_processed);
3188 FEM_ENGINES_PKG.TECH_MESSAGE
3189 (p_severity => c_log_level_2,
3190 p_module => v_block||'.v_rows_loaded{841}',
3191 p_msg_text => v_rows_loaded);
3192 FEM_ENGINES_PKG.TECH_MESSAGE
3193 (p_severity => c_log_level_2,
3194 p_module => v_block||'.v_rows_rejected{842}',
3195 p_msg_text => v_rows_rejected);
3196
3197 IF (v_rows_rejected > 0)
3198 THEN
3199 FEM_ENGINES_PKG.PUT_MESSAGE
3200 (p_app_name => 'FEM',
3201 p_msg_name => 'FEM_DATAX_LDR_BAD_DATA_ERR',
3202 p_token1 => 'COUNT',
3203 p_value1 => v_rows_rejected);
3204 v_message := FND_MSG_PUB.GET(p_encoded => c_false);
3205
3206 FEM_ENGINES_PKG.TECH_MESSAGE
3207 (p_severity => c_log_level_2,
3208 p_module => v_block||'.data_errors{843}',
3209 p_msg_text => v_message);
3210
3211 v_status := 1;
3212 END IF;
3213
3214 FEM_Multi_Proc_Pkg.Post_Data_Slice(
3215 p_req_id => p_req_id,
3216 p_slc_id => v_slc_id,
3217 p_status => v_status,
3218 p_message => v_message,
3219 p_rows_processed => v_rows_processed,
3220 p_rows_loaded => v_rows_loaded,
3221 p_rows_rejected => v_rows_rejected);
3222
3223 END LOOP;
3224
3225 FEM_ENGINES_PKG.TECH_MESSAGE
3226 (p_severity => c_log_level_3,
3227 p_module => v_block||'.End{844}',
3228 p_msg_text => 'End FEM_DATAX_LOADER.Process_Rows');
3229
3230 ---------------------
3231 -- Exception Block --
3232 ---------------------
3233 EXCEPTION
3234
3235 WHEN others THEN
3236 CLOSE cv_get_rows;
3237
3238 v_status := 2;
3239 v_message := sqlerrm;
3240
3241 FEM_Multi_Proc_Pkg.Post_Data_Slice(
3242 p_req_id => p_req_id,
3243 p_slc_id => v_slc_id,
3244 p_status => v_status,
3245 p_message => v_message);
3246
3247 FEM_ENGINES_PKG.TECH_MESSAGE
3248 (p_severity => c_log_level_6,
3249 p_module => v_block||'.Exception{845}',
3250 p_msg_text => sqlerrm);
3251
3252 FEM_ENGINES_PKG.USER_MESSAGE
3253 (p_msg_text => v_message);
3254
3255 END Process_Rows;
3256
3257 /***************************************************************************
3258 ***************************************************************************
3259 * *
3260 * ================ *
3261 * Post_Process *
3262 * ================ *
3263 * *
3264 ***************************************************************************
3265 **************************************************************************/
3266
3267 PROCEDURE Post_Process
3268 IS
3269
3270 v_msg_count NUMBER;
3271 v_msg_data VARCHAR2(4196);
3272 v_return_status VARCHAR2(1);
3273
3274 v_block CONSTANT VARCHAR2(80) :=
3275 'fem.plsql.fem_datax_loader_pkg.post_process_rows';
3276
3277 BEGIN
3278
3279 FEM_ENGINES_PKG.TECH_MESSAGE
3280 (p_severity => c_log_level_3,
3281 p_module => v_block||'.Begin{900}',
3282 p_msg_text => 'Begin FEM_DATAX_LOADER.Post_Process');
3283 FEM_ENGINES_PKG.TECH_MESSAGE
3284 (p_severity => c_log_level_2,
3285 p_module => v_block||'.c_user_id{901}',
3286 p_msg_text => c_user_id);
3287 FEM_ENGINES_PKG.TECH_MESSAGE
3288 (p_severity => c_log_level_2,
3289 p_module => v_block||'.g_req_id{902}',
3290 p_msg_text => g_req_id);
3291 FEM_ENGINES_PKG.TECH_MESSAGE
3292 (p_severity => c_log_level_2,
3293 p_module => v_block||'.g_object_id{903}',
3294 p_msg_text => g_object_id);
3295 FEM_ENGINES_PKG.TECH_MESSAGE
3296 (p_severity => c_log_level_2,
3297 p_module => v_block||'.g_data_table{904}',
3298 p_msg_text => g_data_table);
3299 FEM_ENGINES_PKG.TECH_MESSAGE
3300 (p_severity => c_log_level_2,
3301 p_module => v_block||'.g_data_t_table{905}',
3302 p_msg_text => g_data_t_table);
3303 FEM_ENGINES_PKG.TECH_MESSAGE
3304 (p_severity => c_log_level_2,
3305 p_module => v_block||'.g_rows_processed{906}',
3306 p_msg_text => g_rows_processed);
3307 FEM_ENGINES_PKG.TECH_MESSAGE
3308 (p_severity => c_log_level_2,
3309 p_module => v_block||'.g_rows_loaded{907}',
3310 p_msg_text => g_rows_loaded);
3311 FEM_ENGINES_PKG.TECH_MESSAGE
3312 (p_severity => c_log_level_2,
3313 p_module => v_block||'.g_rows_rejected{908}',
3314 p_msg_text => g_rows_rejected);
3315 FEM_ENGINES_PKG.TECH_MESSAGE
3316 (p_severity => c_log_level_2,
3317 p_module => v_block||'.g_exec_status{909}',
3321 g_rows_loaded := NVL(g_rows_loaded,0);
3318 p_msg_text => g_exec_status);
3319
3320 g_rows_processed := NVL(g_rows_processed,0);
3322 g_rows_rejected := NVL(g_rows_rejected,0);
3323
3324 ----------------------------
3325 -- Register Data Location --
3326 ----------------------------
3327 IF (g_rows_rejected = 0)
3328 THEN
3329 FEM_DIMENSION_UTIL_PKG.REGISTER_DATA_LOCATION
3330 (p_request_id => g_req_id,
3331 p_object_id => g_object_id,
3332 p_table_name => g_data_table,
3333 p_ledger_id => g_ledger_id,
3334 p_cal_per_id => g_cal_per_id,
3335 p_dataset_cd => g_dataset_cd,
3336 p_source_cd => g_source_cd,
3337 p_load_status => 'COMPLETE');
3338 END IF;
3339
3340 ------------------------------------
3341 -- Update Object Execution Errors --
3342 ------------------------------------
3343 FEM_PL_PKG.Update_Obj_Exec_Errors(
3344 p_api_version => c_api_version,
3345 p_request_id => g_req_id,
3346 p_object_id => g_object_id,
3347 p_errors_reported => g_rows_rejected,
3348 p_errors_reprocessed => 0,
3349 p_user_id => c_user_id,
3350 p_last_update_login => null,
3351 x_msg_count => v_msg_count,
3352 x_msg_data => v_msg_data,
3353 x_return_status => v_return_status);
3354
3355 FEM_ENGINES_PKG.TECH_MESSAGE
3356 (p_severity => c_log_level_2,
3357 p_module => v_block||'.update_obj_exec_errors.return_status{910}',
3358 p_msg_text => v_return_status);
3359
3360 Get_Put_Messages (
3361 p_msg_count => v_msg_count,
3362 p_msg_data => v_msg_data);
3363
3364 IF (v_return_status <> c_success)
3365 THEN
3366 RAISE e_process_lock_error;
3367 END IF;
3368
3369 ----------------------------------
3370 -- Update Number of Output Rows --
3371 ----------------------------------
3372 FEM_PL_PKG.Update_Num_of_Output_Rows(
3373 p_api_version => c_api_version,
3374 p_request_id => g_req_id,
3375 p_object_id => g_object_id,
3376 p_table_name => g_data_table,
3377 p_statement_type => 'INSERT',
3378 p_num_of_output_rows => g_rows_loaded,
3379 p_user_id => c_user_id,
3380 p_last_update_login => null,
3381 x_msg_count => v_msg_count,
3382 x_msg_data => v_msg_data,
3383 x_return_status => v_return_status);
3384
3385 FEM_ENGINES_PKG.TECH_MESSAGE
3386 (p_severity => c_log_level_2,
3387 p_module => v_block||'.update_num_of_output_rows.return_status{911}',
3388 p_msg_text => v_return_status);
3389
3390 Get_Put_Messages (
3391 p_msg_count => v_msg_count,
3392 p_msg_data => v_msg_data);
3393
3394 IF (v_return_status <> c_success)
3395 THEN
3396 RAISE e_process_lock_error;
3397 END IF;
3398
3399 ------------------------------------
3400 -- Update Object Execution Status --
3401 ------------------------------------
3402 FEM_PL_PKG.Update_Obj_Exec_Status(
3403 p_api_version => c_api_version,
3404 p_request_id => g_req_id,
3405 p_object_id => g_object_id,
3406 p_exec_status_code => g_exec_status,
3407 p_user_id => c_user_id,
3408 p_last_update_login => null,
3409 x_msg_count => v_msg_count,
3410 x_msg_data => v_msg_data,
3411 x_return_status => v_return_status);
3412
3413 FEM_ENGINES_PKG.TECH_MESSAGE
3414 (p_severity => c_log_level_2,
3415 p_module => v_block||'.update_obj_exec_status.return_status{912}',
3416 p_msg_text => v_return_status);
3417
3418 Get_Put_Messages (
3419 p_msg_count => v_msg_count,
3420 p_msg_data => v_msg_data);
3421
3422 IF (v_return_status <> c_success)
3423 THEN
3424 RAISE e_process_lock_error;
3425 END IF;
3426
3427 ---------------------------
3428 -- Update Request Status --
3429 ---------------------------
3430 FEM_PL_PKG.Update_Request_Status(
3431 p_api_version => c_api_version,
3432 p_request_id => g_req_id,
3433 p_exec_status_code => g_exec_status,
3434 p_user_id => c_user_id,
3435 p_last_update_login => null,
3436 x_msg_count => v_msg_count,
3437 x_msg_data => v_msg_data,
3438 x_return_status => v_return_status);
3439
3440 FEM_ENGINES_PKG.TECH_MESSAGE
3441 (p_severity => c_log_level_2,
3442 p_module => v_block||'.update_request_status.return_status{913}',
3443 p_msg_text => v_return_status);
3444
3445 Get_Put_Messages (
3446 p_msg_count => v_msg_count,
3447 p_msg_data => v_msg_data);
3448
3449 IF (v_return_status <> c_success)
3450 THEN
3451 RAISE e_process_lock_error;
3452 END IF;
3453
3454 FEM_ENGINES_PKG.TECH_MESSAGE
3455 (p_severity => c_log_level_3,
3456 p_module => v_block||'.End{930}',
3457 p_msg_text => 'End FEM_DATAX_LOADER.Post_Process');
3458
3459 END Post_Process;
3460
3461 /***************************************************************************
3462 ***************************************************************************
3463 * *
3464 * ================ *
3465 * Get_Put_Messages *
3466 * ================ *
3467 * *
3468 ***************************************************************************
3469 **************************************************************************/
3470
3471 PROCEDURE Get_Put_Messages (
3472 p_msg_count IN NUMBER,
3473 p_msg_data IN VARCHAR2
3474 )
3475 IS
3476
3477 v_msg_count NUMBER;
3478 v_msg_data VARCHAR2(4196);
3479 v_msg_out NUMBER;
3480 v_message VARCHAR2(4196);
3481
3482 v_block CONSTANT VARCHAR2(80) :=
3483 'fem.plsql.fem_datax_loader_pkg.get_put_messages';
3484
3485 BEGIN
3486
3487 FEM_ENGINES_PKG.TECH_MESSAGE
3488 (p_severity => c_log_level_2,
3489 p_module => v_block||'.msg_count{920}',
3490 p_msg_text => p_msg_count);
3491
3492 v_msg_data := p_msg_data;
3493
3494 IF (p_msg_count = 1)
3495 THEN
3496 FND_MESSAGE.Set_Encoded(v_msg_data);
3497 v_message := FND_MESSAGE.Get;
3498
3499 FEM_ENGINES_PKG.User_Message(
3500 p_msg_text => v_message);
3501
3502 FEM_ENGINES_PKG.TECH_MESSAGE
3503 (p_severity => c_log_level_2,
3504 p_module => v_block||'.msg_data{921}',
3505 p_msg_text => v_message);
3506
3507 ELSIF (p_msg_count > 1)
3508 THEN
3509 FOR i IN 1..p_msg_count
3510 LOOP
3511 FND_MSG_PUB.Get(
3512 p_msg_index => i,
3513 p_encoded => c_false,
3514 p_data => v_message,
3515 p_msg_index_out => v_msg_out);
3516
3517 FEM_ENGINES_PKG.User_Message(
3518 p_msg_text => v_message);
3519
3520 FEM_ENGINES_PKG.TECH_MESSAGE
3521 (p_severity => c_log_level_2,
3522 p_module => v_block||'.msg_data{922}',
3523 p_msg_text => v_message);
3524
3525 END LOOP;
3526 END IF;
3527
3528 FND_MSG_PUB.Initialize;
3529
3530 END Get_Put_Messages;
3531
3532 /***************************************************************************/
3533
3534 END Fem_DataX_Loader_Pkg;