[Home] [Help]
PACKAGE BODY: APPS.FEM_DATA_LOADER_PKG
Source
1 PACKAGE BODY Fem_Data_Loader_Pkg AS
2 /* $Header: FEMDATALEDGERLDR.plb 120.2 2007/07/03 07:20:12 pkakkar ship $ */
3
4 --------------------------------------------------------------------------------
5 -- Declare all global variables --
6 --------------------------------------------------------------------------------
7
8 gs_table_row_tab number_table;
9 gs_table_name_tab char_table;
10 gs_sql_dup_tab char_table;
11 gs_sql_dup_indx_tab number_table;
12 gs_table_obj_id number_table;
13 gs_table_obj_def_id number_table;
14 gs_ss_tab number_table;
15 gs_ds_tab number_table;
16 gs_ledger_tab number_table;
17 gs_ss_code_tab char_table;
18 gs_ds_code_tab char_table;
19 gs_ledger_code_tab char_table;
20 gs_budget_code_tab char_table;
21 gs_enc_code_tab char_table;
22 gs_load_opt_tab char_table;
23 gs_ds_bal_code_tab char_table;
24 gs_cal_grp_tab number_table;
25 gs_sql_stmt_tab sql_stmt_table;
26 g_b_dataset_code number_table;
27 g_e_dataset_code number_table;
28 g_budget_id number_table;
29 g_enc_type_id number_table;
30 g_ledger_id number_table;
31 g_cal_period_id number_table;
32 g_ds_code number_table;
33 g_ss_code number_table;
34 g_invalid_ds_code number_table;
35 gs_valid_rows number_table;
36
37 g_inv_ledger char_table;
38 g_inv_dataset char_table;
39 g_inv_source_system char_table;
40 g_inv_ds_pd_flag char_table;
41 g_inv_table_name char_table;
42 g_inv_table_row char_table;
43
44 g_master_rec master_rec_tab;
45 g_cal_period_rec cal_period_tab;
46 g_interface_data_rec interface_data_tab;
47
48 g_budgets_exist BOOLEAN;
49 g_enc_exist BOOLEAN;
50 g_loader_run BOOLEAN;
51
52 g_request_id NUMBER;
53 g_user_id NUMBER;
54 g_login_id NUMBER;
55 g_object_id NUMBER;
56
57 --------------------------------------------------------------------------------
58 -- Declare private procedures and functions --
59 --------------------------------------------------------------------------------
60
61 PROCEDURE get_parameters(p_obj_def_id IN NUMBER);
62 PROCEDURE process_global_id ;
63 PROCEDURE print_params;
64 PROCEDURE evaluate_parameters;
65 PROCEDURE submit_dimension_loaders;
66 PROCEDURE build_dim_stages;
67 PROCEDURE wait_for_requests(p_wait_for IN VARCHAR2);
68 PROCEDURE populate_cal_periods;
69 PROCEDURE populate_master_table_lldr;
70 PROCEDURE populate_master_table_dldr;
71 PROCEDURE submit_data_loaders;
72 PROCEDURE submit_ledger_loader(p_balance_type IN VARCHAR2);
73 PROCEDURE submit_hierarchy_loaders;
74 PROCEDURE log_dimensions(p_table_name IN VARCHAR2);
75 PROCEDURE log_hierarchies(p_table_name IN VARCHAR2);
76 PROCEDURE log_fact_table(p_table_name IN VARCHAR2, p_table_row IN NUMBER);
77 PROCEDURE populate_log;
78 PROCEDURE cleanup;
79
80 --------------------------------------------------------------------------------
81 -- Public procedures and functions --
82 --------------------------------------------------------------------------------
83
84 --------------------------------------------------------------------------------
85 --
86 -- This is the main procedure that gets called when the LOADER rule is run. It
87 -- calls all the relevant procedures in a sequential manner
88 --
89 --------------------------------------------------------------------------------
90
91
92 PROCEDURE process_request(errbuf OUT NOCOPY VARCHAR2,
93 retcode OUT NOCOPY VARCHAR2,
94 p_obj_def_id IN NUMBER,
95 p_start_date IN VARCHAR2,
96 p_end_date IN VARCHAR2,
97 p_balance_type IN VARCHAR2)
98 IS
99 l_dummy VARCHAR2(10);
100 BEGIN
101 fnd_log_repository.init;
102
103 fem_engines_pkg.tech_message (p_severity => g_log_level_2
104 ,p_module => g_block||'.process_request(PROCEDURE)'
105 ,p_msg_text => 'BEGIN..for process_request');
106
107 fem_engines_pkg.tech_message (p_severity => g_log_level_1
108 ,p_module => g_block||'.process_request(PARAMETERS)'
109 ,p_msg_text => 'p_obj_def_id :: ' || p_obj_def_id);
110
111
112 fem_engines_pkg.tech_message (p_severity => g_log_level_1
113 ,p_module => g_block||'.process_request(PARAMETERS)'
114 ,p_msg_text => 'p_start_date :: ' || p_start_date);
115
116
117 fem_engines_pkg.tech_message (p_severity => g_log_level_1
118 ,p_module => g_block||'.process_request(PARAMETERS)'
119 ,p_msg_text => 'p_end_date :: ' || p_end_date);
120
121
122 fem_engines_pkg.tech_message (p_severity => g_log_level_1
123 ,p_module => g_block||'.process_request(PARAMETERS)'
124 ,p_msg_text => 'p_balance_type :: ' || p_balance_type);
125
126 fem_engines_pkg.tech_message (p_severity => g_log_level_1
127 ,p_module => g_block||'.process_request(PROCEDURE)'
128 ,p_msg_text => 'Calling get_parameters');
129
130 -- DBMS_SESSION.SET_SQL_TRACE (sql_trace => FALSE);
131
132 -- SELECT 'VIVA'
133 -- INTO l_dummy
134 -- FROM dual;
135
136 get_parameters(p_obj_def_id);
137
138 fem_engines_pkg.tech_message (p_severity => g_log_level_1
139 ,p_module => g_block||'.process_request(PROCEDURE)'
140 ,p_msg_text => 'Completed get_parameters');
141
142
143 g_start_date := p_start_date;
144 g_end_date := p_end_date;
145
146 fnd_msg_pub.initialize;
147
148 fem_engines_pkg.tech_message (p_severity => g_log_level_1
149 ,p_module => g_block||'.process_request(PROCEDURE)'
150 ,p_msg_text => 'Calling process_global_id');
151
152 process_global_id;
153
154 fem_engines_pkg.tech_message (p_severity => g_log_level_1
155 ,p_module => g_block||'.process_request(PROCEDURE)'
156 ,p_msg_text => 'Completed process_global_id');
157
158
159 DELETE fem_ld_interface_data_gt;
160 DELETE fem_ld_dim_requests_gt;
161 DELETE fem_ld_hier_requests_gt;
162 DELETE fem_ld_cal_periods_gt;
163
164 fem_engines_pkg.tech_message (p_severity => g_log_level_1
165 ,p_module => g_block||'.process_request(PROCEDURE)'
166 ,p_msg_text => 'Completed deleting the object tables');
167
168 fem_engines_pkg.tech_message (p_severity => g_log_level_1
169 ,p_module => g_block||'.process_request(PROCEDURE)'
170 ,p_msg_text => 'Calling build_dim_stages');
171
172
173 build_dim_stages;
174
175 fem_engines_pkg.tech_message (p_severity => g_log_level_1
176 ,p_module => g_block||'.process_request(PROCEDURE)'
177 ,p_msg_text => 'Completed build_dim_stages');
178
179 fem_engines_pkg.tech_message (p_severity => g_log_level_1
180 ,p_module => g_block||'.process_request(PROCEDURE)'
181 ,p_msg_text => 'Calling evaluate_parameters');
182
183
184 evaluate_parameters;
185
186 fem_engines_pkg.tech_message (p_severity => g_log_level_1
187 ,p_module => g_block||'.process_request(PROCEDURE)'
188 ,p_msg_text => 'Completed evaluate_parameters');
189
190
191 IF g_loader_run AND g_evaluate_parameters THEN
192
193 fem_engines_pkg.tech_message (p_severity => g_log_level_1
194 ,p_module => g_block||'.process_request(PROCEDURE)'
195 ,p_msg_text => 'Calling populate_cal_periods');
196
197
198 populate_cal_periods;
199
200 fem_engines_pkg.tech_message (p_severity => g_log_level_1
201 ,p_module => g_block||'.process_request(PROCEDURE)'
202 ,p_msg_text => 'Completed populate_cal_periods');
203
204 print_params;
205
206 IF g_evaluate_parameters THEN
207
208 IF g_loader_type = 'LEDGER' THEN
209 fem_engines_pkg.tech_message (p_severity => g_log_level_1
210 ,p_module => g_block||'.process_request(PROCEDURE)'
211 ,p_msg_text => 'Calling populate_master_table_lldr');
212
213 populate_master_table_lldr;
214
215 fem_engines_pkg.tech_message (p_severity => g_log_level_1
216 ,p_module => g_block||'.process_request(PROCEDURE)'
217 ,p_msg_text => 'Completed populate_master_table_lldr');
218
219 ELSE
220 fem_engines_pkg.tech_message (p_severity => g_log_level_1
221 ,p_module => g_block||'.process_request(PROCEDURE)'
222 ,p_msg_text => 'Calling populate_master_table_dldr');
223
224 populate_master_table_dldr;
225
226 fem_engines_pkg.tech_message (p_severity => g_log_level_1
227 ,p_module => g_block||'.process_request(PROCEDURE)'
228 ,p_msg_text => 'Completed populate_master_table_dldr');
229
230 END IF;
231
232 IF g_master_rec.COUNT > 0.0 THEN
233 g_request_id := fnd_global.conc_request_id;
234 g_user_id := fnd_global.user_id;
235 g_login_id := fnd_global.login_id;
236
237 IF g_loader_type = 'LEDGER' THEN
238 fem_engines_pkg.tech_message (p_severity => g_log_level_1
239 ,p_module => g_block||'.process_request(PROCEDURE)'
240 ,p_msg_text => 'Calling submit_ledger_loader');
241
242 submit_ledger_loader(p_balance_type);
243
244 fem_engines_pkg.tech_message (p_severity => g_log_level_1
245 ,p_module => g_block||'.process_request(PROCEDURE)'
246 ,p_msg_text => 'Completed submit_ledger_loader');
247 ELSE
248
249 fem_engines_pkg.tech_message (p_severity => g_log_level_1
250 ,p_module => g_block||'.process_request(PROCEDURE)'
251 ,p_msg_text => 'Calling submit_data_loaders');
252
253 submit_data_loaders;
254
255 fem_engines_pkg.tech_message (p_severity => g_log_level_1
256 ,p_module => g_block||'.process_request(PROCEDURE)'
257 ,p_msg_text => 'Completed submit_data_loaders');
258 END IF;
259 ELSE
260 fem_engines_pkg.tech_message (p_severity => g_log_level_1
261 ,p_module => g_block||'.process_request(PROCEDURE)'
262 ,p_msg_text => 'Nothing to process - will not submit the loader CP');
263 END IF;
264
265 ELSE
266 -- The message for printing no valid cal_periods is handled below
267 NULL;
268 END IF; -- g_evaluate_parameters
269
270 ELSE
271 -- What if there was nothing to process ??
272 NULL;
273 END IF; -- g_loader_run and g_evaluate_parameters
274
275 IF g_hierarchy_exists THEN
276 fem_engines_pkg.tech_message (p_severity => g_log_level_1
277 ,p_module => g_block||'.process_request(PROCEDURE)'
278 ,p_msg_text => 'Calling submit_hierarchy_loaders');
279
280 submit_hierarchy_loaders;
281
282 fem_engines_pkg.tech_message (p_severity => g_log_level_1
283 ,p_module => g_block||'.process_request(PROCEDURE)'
284 ,p_msg_text => 'Completed submit_hierarchy_loaders');
285
286 END IF;
287
288 fem_engines_pkg.tech_message (p_severity => g_log_level_1
289 ,p_module => g_block||'.process_request(PROCEDURE)'
290 ,p_msg_text => 'Calling populate_log');
291
292
293 populate_log;
294
295 fem_engines_pkg.tech_message (p_severity => g_log_level_1
296 ,p_module => g_block||'.process_request(PROCEDURE)'
297 ,p_msg_text => 'Completed populate_log');
298
299 fem_engines_pkg.tech_message (p_severity => g_log_level_1
300 ,p_module => g_block||'.process_request(PROCEDURE)'
304
301 ,p_msg_text => 'Calling cleanup');
302
303 cleanup;
305 fem_engines_pkg.tech_message (p_severity => g_log_level_1
306 ,p_module => g_block||'.process_request(PROCEDURE)'
307 ,p_msg_text => 'Completed cleanup');
308
309
310 fem_engines_pkg.tech_message (p_severity => g_log_level_2
311 ,p_module => g_block||'.process_request(PROCEDURE)'
312 ,p_msg_text => 'END process_request');
313
314 EXCEPTION
315 WHEN OTHERS THEN
316 fem_engines_pkg.tech_message (p_severity => g_log_level_6
317 ,p_module => g_block||'.process_request(PROCEDURE)'
318 ,p_msg_text => 'EXCEPTION in process_request ' || sqlerrm);
319 fnd_file.put_line(fnd_file.log, 'Exception - process_request ' || sqlerrm);
320 RAISE;
321
322 END process_request;
323
324 ----------------------
325 -- END process_request
326 ----------------------
327
328 --------------------------------------------------------------------------------
329 --
330 -- This procedure is used for printing information into the output file
331 --
332 --------------------------------------------------------------------------------
333
334 PROCEDURE trace(p_trace_what IN VARCHAR2) IS
335
336 s INTEGER;
337 l_separator VARCHAR2(140) := '===========================================================================================================================================';
338 l_message VARCHAR2(2000);
339
340 BEGIN
341
342 fem_engines_pkg.tech_message (p_severity => g_log_level_2
343 ,p_module => g_block||'.trace(PROCEDURE)'
344 ,p_msg_text => 'BEGIN..for trace ' || p_trace_what);
345
346 IF p_trace_what = 'SEPARATOR' THEN
347 l_message := l_separator;
348 ELSIF p_trace_what = 'MESSAGE' THEN
349 l_message := fnd_message.get;
350 ELSIF p_trace_what = 'BLANKLINE' THEN
351 l_message := '';
352 END IF;
353
354 fnd_file.put_line(FND_FILE.OUTPUT, l_message);
355
356 fem_engines_pkg.tech_message (p_severity => g_log_level_2
357 ,p_module => g_block||'.trace(PROCEDURE)'
358 ,p_msg_text => 'END trace ');
359
360
361 EXCEPTION
362 WHEN OTHERS THEN
363 fem_engines_pkg.tech_message (p_severity => g_log_level_6
364 ,p_module => g_block||'.trace(PROCEDURE)'
365 ,p_msg_text => 'EXCEPTION in trace ' || sqlerrm);
366 fnd_file.put_line(fnd_file.log, 'Exception - trace ' || sqlerrm);
367 RAISE;
368
369 END trace;
370
371 ------------
372 -- END trace
373 ------------
374
375 --------------------------------------------------------------------------------
376 -- Private procedures and functions --
377 --------------------------------------------------------------------------------
378
379 --------------------------------------------------------------------------------
380 --
381 -- This procedure is used to free up all the global pl/sql objects, object tables
382 -- used during the loader run
383 --
384 --------------------------------------------------------------------------------
385
386 PROCEDURE cleanup IS
387
388 BEGIN
389 fem_engines_pkg.tech_message (p_severity => g_log_level_2
390 ,p_module => g_block||'.cleanup(PROCEDURE)'
391 ,p_msg_text => 'BEGIN..for cleanup ');
392
393
394 gs_table_row_tab.DELETE;
395 gs_table_name_tab.DELETE;
396 gs_sql_dup_tab.DELETE;
397 gs_sql_dup_indx_tab.DELETE;
398 gs_ss_tab.DELETE;
399 gs_ds_tab.DELETE;
400 gs_ledger_tab.DELETE;
401 gs_ss_code_tab.DELETE;
402 gs_ds_code_tab.DELETE;
403 gs_ledger_code_tab.DELETE;
404 gs_budget_code_tab.DELETE;
405 gs_enc_code_tab.DELETE;
406 gs_load_opt_tab.DELETE;
407 gs_ds_bal_code_tab.DELETE;
408 gs_cal_grp_tab.DELETE;
409 gs_sql_stmt_tab.DELETE;
410 g_b_dataset_code.DELETE;
411 g_e_dataset_code.DELETE;
412 g_budget_id.DELETE;
413 g_enc_type_id.DELETE;
414 g_ledger_id.DELETE;
415 g_ds_code.DELETE;
416 g_ss_code.DELETE;
417 g_invalid_ds_code.DELETE;
418 gs_valid_rows.DELETE;
419 gs_table_obj_def_id.DELETE;
420
421 g_inv_ledger.DELETE;
422 g_inv_dataset.DELETE;
423 g_inv_source_system.DELETE;
424 g_inv_ds_pd_flag.DELETE;
425 g_inv_table_name.DELETE;
426 g_inv_table_row.DELETE;
427
428 g_master_rec.DELETE;
429 g_cal_period_rec.DELETE;
430 g_interface_data_rec.DELETE;
431
432 DELETE fem_ld_interface_data_gt;
433 DELETE fem_ld_dim_requests_gt;
434 DELETE fem_ld_hier_requests_gt;
435 DELETE fem_ld_cal_periods_gt;
436
440
437 fem_engines_pkg.tech_message (p_severity => g_log_level_2
438 ,p_module => g_block||'.cleanup(PROCEDURE)'
439 ,p_msg_text => 'END cleanup ');
441 EXCEPTION
442 WHEN OTHERS THEN
443 fem_engines_pkg.tech_message (p_severity => g_log_level_6
444 ,p_module => g_block||'.cleanup(PROCEDURE)'
445 ,p_msg_text => 'EXCEPTION in cleanup ' || sqlerrm);
446 fnd_file.put_line(fnd_file.log, 'Exception - cleanup ' || sqlerrm);
447 RAISE;
448 END cleanup;
449
450 --------------
451 -- END cleanup
452 --------------
453
454 --------------------------------------------------------------------------------
455 --
456 -- This procedure is used to query up the selection criteria specified by the
457 -- user in the parameters page.
458 --
459 -- The procedure populates g_loader_type usefule in identifying if it is DATA/
460 -- LEDGER load, identifies if the RULE is approved or not
461 --
462 --------------------------------------------------------------------------------
463
464
465 PROCEDURE get_parameters(p_obj_def_id IN NUMBER) IS
466 l_approval_status VARCHAR2(30);
467 BEGIN
468
469 fem_engines_pkg.tech_message (p_severity => g_log_level_2
470 ,p_module => g_block||'.get_parameters(PROCEDURE)'
471 ,p_msg_text => 'BEGIN..for get_parameters ');
472
473 BEGIN
474 SELECT loader_type,
475 approval_status_code,
476 object_id
477 INTO g_loader_type,
478 l_approval_status,
479 g_object_id
480 FROM fem_data_loader_rules fdlr,
481 fem_object_definition_b fod
482 WHERE fdlr.loader_obj_id = fod.object_id
483 AND fod.object_definition_id = p_obj_def_id;
484
485 EXCEPTION
486 WHEN OTHERS THEN
487 fem_engines_pkg.tech_message (p_severity => g_log_level_6
488 ,p_module => g_block||'.get_parameters (PROCEDURE)'
489 ,p_msg_text => 'EXCEPTION in get_parameters.loader_type ' || sqlerrm);
490 fnd_file.put_line(fnd_file.log, 'Exception - get_parameters ' || sqlerrm);
491 RAISE; -- loader_rule_error;
492 END;
493
494 IF l_approval_status = 'APPROVED' THEN
495 g_approval_flag := TRUE;
496 END IF;
497
498 IF g_loader_type = 'LEDGER' THEN
499 SELECT interface_table_name
500 INTO g_int_table_name
501 FROM fem_tables_b
502 WHERE table_name = 'FEM_BALANCES';
503 END IF;
504
505 -- g_loader_type = CLIENT/LEDGER
506 -- If LEDGER we do not need source_system_code
507
508 IF g_loader_type = 'CLIENT' THEN
509 BEGIN
510 SELECT ROWNUM,
511 fdlp.table_name,
512 source_system_code,
513 dataset_code,
514 ledger_id,
515 load_option,
516 cal_period_grp_id,
517 'SELECT ledger_display_code,
518 dataset_display_code,
519 source_system_display_code,
520 cal_period_number,
521 calp_dim_grp_display_code,
522 cal_period_end_date,' || '''' ||
523 fdlp.table_name || '''' || ',' || 'TO_NUMBER(''' || ROWNUM || ''')' ||
524 ' FROM ' dyn_sql_stmt,
525 fodb.object_id,
526 fodb.object_definition_id
527 BULK COLLECT INTO gs_table_row_tab,
528 gs_table_name_tab,
529 gs_ss_tab,
530 gs_ds_tab,
531 gs_ledger_tab,
532 gs_load_opt_tab,
533 gs_cal_grp_tab,
534 gs_sql_stmt_tab,
535 gs_table_obj_id,
536 gs_table_obj_def_id
537 FROM fem_data_loader_params fdlp,
538 fem_data_loader_objects fdlo,
539 fem_object_definition_b fodb
540 WHERE loader_obj_def_id = p_obj_def_id
541 AND fdlp.table_name = fdlo.table_name
542 AND fdlo.object_id = fodb.object_id;
543 EXCEPTION
544 WHEN OTHERS THEN
545 fem_engines_pkg.tech_message (p_severity => g_log_level_6
546 ,p_module => g_block||'.get_parameters (PROCEDURE)'
547 ,p_msg_text => 'EXCEPTION in get_parameters ' || sqlerrm);
548 fnd_file.put_line(fnd_file.log, 'Exception - get_parameters(DATA LOAD) ' || sqlerrm);
549 RAISE;
550 END;
551 ELSE
552 BEGIN
553 SELECT ROWNUM,
554 table_name,
555 dataset_code,
556 ledger_id,
557 load_option,
561 cal_per_dim_grp_display_code,
558 cal_period_grp_id,
559 'SELECT cal_period_number,
560 cal_period_end_date,
562 ledger_display_code,
563 ds_balance_type_code,
564 budget_display_code,
565 encumbrance_type_code,' || '''' ||
566 table_name || '''' || ',' || 'TO_NUMBER(''' || ROWNUM || ''')' ||
567 ' FROM ' dyn_sql_stmt,
568 1000 object_id
569 BULK COLLECT INTO gs_table_row_tab,
570 gs_table_name_tab,
571 gs_ds_tab,
572 gs_ledger_tab,
573 gs_load_opt_tab,
574 gs_cal_grp_tab,
575 gs_sql_stmt_tab,
576 gs_table_obj_id
577 FROM fem_data_loader_params
578 WHERE loader_obj_def_id = p_obj_def_id;
579
580 EXCEPTION
581 WHEN OTHERS THEN
582 fem_engines_pkg.tech_message (p_severity => g_log_level_6
583 ,p_module => g_block||'.get_parameters (PROCEDURE)'
584 ,p_msg_text => 'EXCEPTION in get_parameters(LEDGER LOAD) ' || sqlerrm);
585 fnd_file.put_line(fnd_file.log, 'Exception - get_parameters ' || sqlerrm);
586 RAISE;
587 END;
588
589 END IF;
590
591 fem_engines_pkg.tech_message (p_severity => g_log_level_2
592 ,p_module => g_block||'.get_parameters(PROCEDURE)'
593 ,p_msg_text => 'END get_parameters');
594
595 END get_parameters;
596
597 ---------------------
598 -- END get_parameters
599 ---------------------
600
601 --------------------------------------------------------------------------------
602 --
603 -- This procedure gets the dimension id's, attribute id's of all the dimensions
604 -- and attributes that get used during the course of LOADER run
605 --
606 --------------------------------------------------------------------------------
607
608 PROCEDURE process_global_id IS
609
610 BEGIN
611
612 fem_engines_pkg.tech_message (p_severity => g_log_level_2
613 ,p_module => g_block||'.process_global_id(PROCEDURE)'
614 ,p_msg_text => 'BEGIN..for process_global_id ');
615
616 BEGIN
617 SELECT dimension_id
618 INTO g_ledger_dim_id
619 FROM fem_dimensions_b
620 WHERE dimension_varchar_label = 'LEDGER';
621 EXCEPTION
622 WHEN OTHERS THEN
623 fem_engines_pkg.tech_message (p_severity => g_log_level_6
624 ,p_module => g_block||'.process_global_id(PROCEDURE)'
625 ,p_msg_text => 'EXCEPTION fetching dimension_id for LEDGER');
626 fnd_file.put_line(fnd_file.log, 'Exception - process_global_id ' || sqlerrm);
627 RAISE;
628 END;
629
630 fem_engines_pkg.tech_message (p_severity => g_log_level_1
631 ,p_module => g_block||'.process_global_id(PROCEDURE)'
632 ,p_msg_text => 'Evaluated dimension_id for LEDGER :: ' || g_ledger_dim_id);
633
634 BEGIN
635 SELECT da.attribute_id
636 INTO g_cal_period_hier_attr
637 FROM fem_dim_attributes_b da,
638 fem_dim_attr_versions_b dav
639 WHERE da.dimension_id = g_ledger_dim_id
640 AND da.attribute_varchar_label = 'CAL_PERIOD_HIER_OBJ_DEF_ID'
641 AND dav.attribute_id = da.attribute_id
642 AND dav.default_version_flag = 'Y';
643 EXCEPTION
644 WHEN OTHERS THEN
645 fem_engines_pkg.tech_message (p_severity => g_log_level_6
646 ,p_module => g_block||'.process_global_id(PROCEDURE)'
647 ,p_msg_text => 'EXCEPTION fetching attribute_id for CAL PERIOD HIERARCHY');
648 fnd_file.put_line(fnd_file.log, 'Exception - process_global_id ' || sqlerrm);
649 RAISE;
650 END;
651
652 fem_engines_pkg.tech_message (p_severity => g_log_level_1
653 ,p_module => g_block||'.process_global_id(PROCEDURE)'
654 ,p_msg_text => 'Evaluated attribute_id for CAL PERIOD HIERARCHY :: ' ||
655 g_cal_period_hier_attr);
656
657
658 BEGIN
659 SELECT dimension_id
660 INTO g_cal_period_dim_id
661 FROM fem_dimensions_b
662 WHERE dimension_varchar_label = 'CAL_PERIOD';
663 EXCEPTION
664 WHEN OTHERS THEN
665 fem_engines_pkg.tech_message (p_severity => g_log_level_6
666 ,p_module => g_block||'.process_global_id(PROCEDURE)'
667 ,p_msg_text => 'EXCEPTION fetching dimension_id for CAL PERIOD');
668 fnd_file.put_line(fnd_file.log, 'Exception - process_global_id ' || sqlerrm);
669 RAISE;
670 END;
671
672 fem_engines_pkg.tech_message (p_severity => g_log_level_1
676
673 ,p_module => g_block||'.process_global_id(PROCEDURE)'
674 ,p_msg_text => 'Evaluated dimension_id for CAL_PERIOD :: ' || g_cal_period_dim_id);
675
677 BEGIN
678 SELECT da.attribute_id
679 INTO g_start_date_attr
680 FROM fem_dim_attributes_b da,
681 fem_dim_attr_versions_b dav
682 WHERE da.dimension_id = g_cal_period_dim_id
683 AND da.attribute_varchar_label = 'CAL_PERIOD_START_DATE'
684 AND dav.attribute_id = da.attribute_id
685 AND dav.default_version_flag = 'Y';
686 EXCEPTION
687 WHEN OTHERS THEN
688 fem_engines_pkg.tech_message (p_severity => g_log_level_6
689 ,p_module => g_block||'.process_global_id(PROCEDURE)'
690 ,p_msg_text => 'EXCEPTION fetching attribute_id for CAL PERIOD START DATE');
691 fnd_file.put_line(fnd_file.log, 'Exception - process_global_id ' || sqlerrm);
692 RAISE;
693 END;
694
695 fem_engines_pkg.tech_message (p_severity => g_log_level_1
696 ,p_module => g_block||'.process_global_id(PROCEDURE)'
697 ,p_msg_text => 'Evaluated attribute_id for CAL PERIOD START DATE :: ' ||
698 g_start_date_attr);
699
700
701 BEGIN
702 SELECT da.attribute_id
703 INTO g_end_date_attr
704 FROM fem_dim_attributes_b da,
705 fem_dim_attr_versions_b dav
706 WHERE da.dimension_id = g_cal_period_dim_id
707 AND da.attribute_varchar_label = 'CAL_PERIOD_END_DATE'
708 AND dav.attribute_id = da.attribute_id
709 AND dav.default_version_flag = 'Y';
710 EXCEPTION
711 WHEN OTHERS THEN
712 fem_engines_pkg.tech_message (p_severity => g_log_level_6
713 ,p_module => g_block||'.process_global_id(PROCEDURE)'
714 ,p_msg_text => 'EXCEPTION fetching attribute_id for CAL PERIOD END DATE');
715 fnd_file.put_line(fnd_file.log, 'Exception - process_global_id ' || sqlerrm);
716 RAISE;
717 END;
718
719 fem_engines_pkg.tech_message (p_severity => g_log_level_1
720 ,p_module => g_block||'.process_global_id(PROCEDURE)'
721 ,p_msg_text => 'Evaluated attribute_id for CAL PERIOD END DATE :: ' || g_end_date_attr);
722
723 BEGIN
724 SELECT dimension_id
725 INTO g_dataset_dim_id
726 FROM fem_dimensions_b
727 WHERE dimension_varchar_label = 'DATASET';
728 EXCEPTION
729 WHEN OTHERS THEN
730 fem_engines_pkg.tech_message (p_severity => g_log_level_6
731 ,p_module => g_block||'.process_global_id(PROCEDURE)'
732 ,p_msg_text => 'EXCEPTION fetching dimension_id for DATASET');
733 fnd_file.put_line(fnd_file.log, 'Exception - process_global_id ' || sqlerrm);
734 RAISE;
735 END;
736
737 fem_engines_pkg.tech_message (p_severity => g_log_level_1
738 ,p_module => g_block||'.process_global_id(PROCEDURE)'
739 ,p_msg_text => 'Evaluated dimension_id for DATASET :: ' || g_dataset_dim_id);
740
741
742 BEGIN
743 SELECT da.attribute_id
744 INTO g_dataset_bal_attr
745 FROM fem_dim_attributes_b da,
746 fem_dim_attr_versions_b dav
747 WHERE da.dimension_id = g_dataset_dim_id
748 AND da.attribute_varchar_label = 'DATASET_BALANCE_TYPE_CODE'
749 AND dav.attribute_id = da.attribute_id
750 AND dav.default_version_flag = 'Y';
751 EXCEPTION
752 WHEN OTHERS THEN
753 fem_engines_pkg.tech_message (p_severity => g_log_level_6
754 ,p_module => g_block||'.process_global_id(PROCEDURE)'
755 ,p_msg_text => 'EXCEPTION fetching attribute_id for DATASET BALANCE TYPE');
756 fnd_file.put_line(fnd_file.log, 'Exception - process_global_id ' || sqlerrm);
757 RAISE;
758 END;
759
760 fem_engines_pkg.tech_message (p_severity => g_log_level_1
761 ,p_module => g_block||'.process_global_id(PROCEDURE)'
762 ,p_msg_text => 'Evaluated attribute_id for DATASET BALANCE TYPE :: ' ||
763 g_dataset_bal_attr);
764
765
766 BEGIN
767 SELECT da.attribute_id
768 INTO g_production_attr
769 FROM fem_dim_attributes_b da,
770 fem_dim_attr_versions_b dav
771 WHERE da.dimension_id = g_dataset_dim_id
772 AND da.attribute_varchar_label = 'PRODUCTION_FLAG'
773 AND dav.attribute_id = da.attribute_id
774 AND dav.default_version_flag = 'Y';
775 EXCEPTION
776 WHEN OTHERS THEN
777 fem_engines_pkg.tech_message (p_severity => g_log_level_6
778 ,p_module => g_block||'.process_global_id(PROCEDURE)'
779 ,p_msg_text => 'EXCEPTION fetching attribute_id for PRODUCTION FLAG');
780 fnd_file.put_line(fnd_file.log, 'Exception - process_global_id ' || sqlerrm);
781 RAISE;
782 END;
783
784 fem_engines_pkg.tech_message (p_severity => g_log_level_1
788
785 ,p_module => g_block||'.process_global_id(PROCEDURE)'
786 ,p_msg_text => 'Evaluated attribute_id for PRODUCTION FLAG :: ' || g_production_attr);
787
789 BEGIN
790 SELECT dim.dimension_id
791 INTO g_budget_dim_id
792 FROM fem_dimensions_b dim
793 WHERE dim.dimension_varchar_label = 'BUDGET';
794 EXCEPTION
795 WHEN OTHERS THEN
796 fem_engines_pkg.tech_message (p_severity => g_log_level_6
797 ,p_module => g_block||'.process_global_id(PROCEDURE)'
798 ,p_msg_text => 'EXCEPTION fetching dimension_id for BUDGET');
799 fnd_file.put_line(fnd_file.log, 'Exception - process_global_id ' || sqlerrm);
800 RAISE;
801 END;
802
803 fem_engines_pkg.tech_message (p_severity => g_log_level_1
804 ,p_module => g_block||'.process_global_id(PROCEDURE)'
805 ,p_msg_text => 'Evaluated dimension_id for BUDGET :: ' || g_budget_dim_id);
806
807
808 fem_engines_pkg.tech_message (p_severity => g_log_level_2
809 ,p_module => g_block||'.process_global_id(PROCEDURE)'
810 ,p_msg_text => 'END process_global_id');
811
812 END process_global_id;
813
814 --------------------------
815 -- END process_global_id
816 --------------------------
817
818 --------------------------------------------------------------------------------
819 --
820 -- This procedure is used to wait for set of concurrent requests to complete,
821 -- capture the request status
822 --
823 --------------------------------------------------------------------------------
824
825 PROCEDURE wait_for_requests(p_wait_for IN VARCHAR2) IS
826 l_request_id NUMBER;
827
828 l_return_status VARCHAR2(1);
829 l_msg_data VARCHAR2(4000);
830 l_msg_count NUMBER;
831
832 l_phase VARCHAR2(200);
833 l_status VARCHAR2(200);
834 l_dev_phase VARCHAR2(200);
835 l_dev_status VARCHAR2(200);
836 l_message VARCHAR2(200);
837 l_ret_code NUMBER;
838 l_err_buff VARCHAR2(1000);
839 BEGIN
840
841 fem_engines_pkg.tech_message (p_severity => g_log_level_2
842 ,p_module => g_block||'.wait_for_requests(PROCEDURE)'
843 ,p_msg_text => 'BEGIN..for wait_for_requests ');
844
845 CASE p_wait_for
846 WHEN c_dim_loader THEN
847 FOR dim_rec IN (SELECT DISTINCT
848 request_id
849 FROM fem_ld_dim_requests_gt
850 WHERE request_id > 0 )
851 LOOP
852 IF fnd_concurrent.wait_for_request(request_id=> dim_rec.request_id,
853 interval => c_interval,
854 max_wait => c_max_wait_time,
855 phase => l_phase,
856 status => l_status,
857 dev_phase => l_dev_phase,
858 dev_status => l_dev_status,
859 message => l_message)
860 THEN
861 IF l_dev_phase || '.' || l_dev_status IN ('COMPLETE.NORMAL','COMPLETE.WARNING') THEN
862 UPDATE fem_ld_dim_requests_gt
863 SET status = 'Y'
864 WHERE request_id = dim_rec.request_id;
865 ELSE
866 UPDATE fem_ld_dim_requests_gt
867 SET status = 'N'
868 WHERE request_id = dim_rec.request_id;
869 END IF;
870
871 END IF; -- fnd_concurrent.wait_for_request (DIMENSIONS)
872
873 END LOOP; -- dim_rec
874
875 WHEN c_data_ledger_loader THEN
876 FOR i IN 1..g_master_rec.COUNT LOOP
877 IF g_master_rec(i).request_id > 0 THEN
878 IF fnd_concurrent.wait_for_request(request_id=> g_master_rec(i).request_id,
879 interval => c_interval,
880 max_wait => c_max_wait_time,
881 phase => l_phase,
882 status => l_status,
883 dev_phase => l_dev_phase,
884 dev_status => l_dev_status,
885 message => l_message)
886 THEN
887 IF l_dev_phase || '.' || l_dev_status IN ('COMPLETE.NORMAL','COMPLETE.WARNING') THEN
888 g_master_rec(i).status := 'Y';
889 ELSE
890 g_master_rec(i).status := 'N';
891 END IF;
892
893 END IF; -- fnd_concurrent.wait_for_request (DATA/LEDGER LOAD)
894
895 END IF; -- g_master_rec(i).request_id > 0
896
897 END LOOP; -- dim_rec
898 ELSE -- 'Hierarchy'
899 FOR hier_rec IN (SELECT DISTINCT
900 request_id
901 FROM fem_ld_hier_requests_gt
902 WHERE request_id > 0 )
903 LOOP
904 IF fnd_concurrent.wait_for_request(request_id=> hier_rec.request_id,
908 status => l_status,
905 interval => c_interval,
906 max_wait => c_max_wait_time,
907 phase => l_phase,
909 dev_phase => l_dev_phase,
910 dev_status => l_dev_status,
911 message => l_message)
912 THEN
913 IF l_dev_phase || '.' || l_dev_status IN ('COMPLETE.NORMAL','COMPLETE.WARNING') THEN
914 UPDATE fem_ld_hier_requests_gt
915 SET status = 'Y'
916 WHERE request_id = hier_rec.request_id;
917 ELSE
918 UPDATE fem_ld_hier_requests_gt
919 SET status = 'N'
920 WHERE request_id = hier_rec.request_id;
921 END IF;
922
923 END IF; -- fnd_concurrent.wait_for_request ('Hierarchy')
924
925 END LOOP; -- hier_rec
926
927 END CASE; -- p_wait_for
928
929 fem_engines_pkg.tech_message (p_severity => g_log_level_2
930 ,p_module => g_block||'.wait_for_requests(PROCEDURE)'
931 ,p_msg_text => 'END wait_for_requests ');
932
933 EXCEPTION
934 WHEN OTHERS THEN
935 fem_engines_pkg.tech_message (p_severity => g_log_level_6
936 ,p_module => g_block||'.wait_for_requests(PROCEDURE)'
937 ,p_msg_text => 'EXCEPTION in wait_for_requests ' || sqlerrm);
938 fnd_file.put_line(fnd_file.log, 'Exception - wait_for_requests ' || sqlerrm);
939 RAISE;
940
941 END wait_for_requests;
942
943 ------------------------
944 -- END wait_for_requests
945 ------------------------
946
947 --------------------------------------------------------------------------------
948 --
949 -- This procedure is used to submit the dimension loader CP. All the requests
950 -- are submitted in parallel. It first checks if there are records in the
951 -- interface table before issuing the call to the CP. If no records are present
952 -- the request_id is set to -10000 facilitating an easy reporting
953 --
954 --------------------------------------------------------------------------------
955
956
957 PROCEDURE submit_dimension_loaders IS
958 l_request_id NUMBER;
959
960 l_table_name VARCHAR2(30);
961
962 l_dim_load_mode VARCHAR2(1);
963 l_return_status VARCHAR2(1);
964 l_msg_data VARCHAR2(4000);
965 l_msg_count NUMBER;
966
967 l_dummy NUMBER;
968 l_at_least_one BOOLEAN;
969 BEGIN
970
971 fem_engines_pkg.tech_message (p_severity => g_log_level_2
972 ,p_module => g_block||'.submit_dimension_loaders(PROCEDURE)'
973 ,p_msg_text => 'BEGIN..for submit_dimension_loaders');
974
975 l_at_least_one := FALSE;
976
977 FOR dim_rec IN (SELECT DISTINCT
978 dimension_id,
979 dim_intf_table_name
980 FROM fem_ld_dim_requests_gt )
981 LOOP
982
983 BEGIN
984 EXECUTE IMMEDIATE 'SELECT 1 FROM ' || dim_rec.dim_intf_table_name || ' WHERE ROWNUM = 1' INTO l_dummy;
985 EXCEPTION
986 WHEN NO_DATA_FOUND THEN
987 l_dummy := 0.0;
988 fem_engines_pkg.tech_message (p_severity => g_log_level_1
989 ,p_module => g_block||'.submit_dimension_loaders(PROCEDURE)'
990 ,p_msg_text => 'No data exists in the interface table '
991 || dim_rec.dim_intf_table_name );
992 WHEN OTHERS THEN
993 fem_engines_pkg.tech_message (p_severity => g_log_level_6
994 ,p_module => g_block||'.submit_dimension_loaders(PROCEDURE)'
995 ,p_msg_text => 'Error while checking if data EXISTS in interface table '
996 || dim_rec.dim_intf_table_name || ' - ' || sqlerrm);
997 fnd_file.put_line(fnd_file.log, 'Exception - submit_dimension_loaders ' || sqlerrm);
998 RAISE;
999 END;
1000
1001 IF l_dummy = 1 THEN
1002
1003 fem_loader_eng_util_pkg.get_dim_loader_exec_mode(c_api_version,
1004 c_false,
1005 c_false,
1006 c_true,
1007 l_return_status,
1008 l_msg_count,
1009 l_msg_data,
1010 dim_rec.dimension_id,
1011 l_dim_load_mode);
1012
1013 l_request_id := fnd_request.submit_request('FEM',
1014 'FEM_DIM_MEMBER_LOADER',
1015 NULL,
1016 NULL,
1017 FALSE,
1018 l_dim_load_mode,
1022
1019 dim_rec.dimension_id);
1020
1021 l_at_least_one := TRUE;
1023 COMMIT;
1024 ELSE
1025 l_request_id := -10000.0; -- No records available in interface table
1026 END IF;
1027
1028 l_dummy := 0.0;
1029
1030 UPDATE fem_ld_dim_requests_gt
1031 SET request_id = l_request_id
1032 WHERE dimension_id = dim_rec.dimension_id;
1033
1034 END LOOP;
1035
1036 IF l_at_least_one THEN
1037 fem_engines_pkg.tech_message (p_severity => g_log_level_1
1038 ,p_module => g_block||'.submit_dimension_loaders(PROCEDURE)'
1039 ,p_msg_text => 'Calling wait_for_requests - DIMENSION');
1040
1041 wait_for_requests(c_dim_loader);
1042
1043 fem_engines_pkg.tech_message (p_severity => g_log_level_1
1044 ,p_module => g_block||'.submit_dimension_loaders(PROCEDURE)'
1045 ,p_msg_text => 'Completed wait_for_requests - DIMENSION');
1046 END IF;
1047
1048 fem_engines_pkg.tech_message (p_severity => g_log_level_2
1049 ,p_module => g_block||'.submit_dimension_loaders(PROCEDURE)'
1050 ,p_msg_text => 'END submit_dimension_loaders');
1051
1052 EXCEPTION
1053 WHEN OTHERS THEN
1054 fem_engines_pkg.tech_message (p_severity => g_log_level_6
1055 ,p_module => g_block||'.submit_dimension_loaders(PROCEDURE)'
1056 ,p_msg_text => 'EXCEPTION in submit_dimension_loaders ' || sqlerrm);
1057 fnd_file.put_line(fnd_file.log, 'Exception - submit_dimension_loaders ' || sqlerrm);
1058 RAISE;
1059
1060 END submit_dimension_loaders;
1061
1062 -------------------------------
1063 -- END submit_dimension_loaders
1064 -------------------------------
1065
1066 --------------------------------------------------------------------------------
1067 --
1068 -- This procedure relies on g_master_rec to submit the data loaders
1069 --
1070 --------------------------------------------------------------------------------
1071
1072 PROCEDURE submit_data_loaders IS
1073
1074 l_return_status VARCHAR2(1);
1075 l_msg_data VARCHAR2(4000);
1076 l_msg_count NUMBER;
1077 l_at_least_one BOOLEAN;
1078
1079 exit_condition BOOLEAN;
1080 l_data_load_mode VARCHAR2(1);
1081
1082 l_rec_count NUMBER:=0;
1083 l_num_loader NUMBER;
1084 l_count NUMBER:=0;
1085
1086 i NUMBER:=1;
1087
1088 e_num_loader_neg EXCEPTION;
1089
1090 BEGIN
1091
1092 fem_engines_pkg.tech_message (p_severity => g_log_level_2
1093 ,p_module => g_block||'.submit_data_loaders(PROCEDURE)'
1094 ,p_msg_text => 'BEGIN..for submit_data_loaders');
1095
1096 l_at_least_one := FALSE;
1097 exit_condition := FALSE;
1098
1099 l_num_loader:=FND_PROFILE.VALUE('FEM_NUM_OF_LOADERS');
1100
1101 IF l_num_loader is NOT NULL AND l_num_loader<=0 THEN
1102
1103 RAISE e_num_loader_neg;
1104
1105 END IF;
1106
1107 l_rec_count :=g_master_rec.COUNT;
1108
1109 IF (l_num_loader is NULL OR l_num_loader>=l_rec_count) THEN
1110
1111 FOR i IN 1..g_master_rec.COUNT LOOP
1112 IF i <> 1.0 THEN
1113 exit_condition := FALSE;
1114 FOR j IN 1..i-1 LOOP
1115 IF ((g_master_rec(j).table_name = g_master_rec(i).table_name) AND
1116 (g_master_rec(j).ledger_id = g_master_rec(i).ledger_id) AND
1117 (g_master_rec(j).dataset_code = g_master_rec(i).dataset_code) AND
1118 (g_master_rec(j).source_system_code = g_master_rec(i).source_system_code) AND
1119 (g_master_rec(j).cal_period_id = g_master_rec(i).cal_period_id))
1120 THEN
1121 g_master_rec(i).request_id := g_master_rec(j).request_id;
1122 exit_condition := TRUE;
1123 END IF;
1124 EXIT WHEN exit_condition = TRUE;
1125 END LOOP;
1126 END IF; -- i<> 1
1127
1128 IF NOT exit_condition THEN
1129
1130 fem_loader_eng_util_pkg.get_fact_loader_exec_mode(c_api_version,
1131 c_false,
1132 c_false,
1133 c_true,
1134 l_return_status,
1135 l_msg_count,
1136 l_msg_data,
1137 g_master_rec(i).cal_period_id,
1138 g_master_rec(i).ledger_id,
1139 g_master_rec(i).dataset_code,
1140 g_master_rec(i).source_system_code,
1141 g_master_rec(i).table_name,
1142 l_data_load_mode);
1143
1144 g_master_rec(i).request_id := fnd_request.submit_request('FEM',
1148 FALSE,
1145 'FEM_SOURCE_DATA_LOADER',
1146 NULL,
1147 NULL,
1149 gs_table_obj_def_id(g_master_rec(i).table_row),
1150 l_data_load_mode,
1151 g_master_rec(i).ledger_id,
1152 g_master_rec(i).cal_period_id,
1153 g_master_rec(i).dataset_code,
1154 g_master_rec(i).source_system_code);
1155 l_at_least_one := TRUE;
1156
1157 COMMIT;
1158
1159 END IF; -- exit_condition
1160
1161 END LOOP; -- g_master_rec
1162
1163 IF l_at_least_one THEN
1164 fem_engines_pkg.tech_message (p_severity => g_log_level_1
1165 ,p_module => g_block||'.submit_data_loaders(PROCEDURE)'
1166 ,p_msg_text => 'Calling wait_for_requests - DATA LOAD');
1167
1168 wait_for_requests(c_data_ledger_loader);
1169
1170 fem_engines_pkg.tech_message (p_severity => g_log_level_1
1171 ,p_module => g_block||'.submit_data_loaders(PROCEDURE)'
1172 ,p_msg_text => 'Completed wait_for_requests - DATA LOAD');
1173
1174 END IF;
1175
1176 fem_engines_pkg.tech_message (p_severity => g_log_level_2
1177 ,p_module => g_block||'.submit_data_loaders(PROCEDURE)'
1178 ,p_msg_text => 'END submit_data_loaders');
1179
1180 END IF;--IF (l_num_loader is NULL OR l_num_loader>=l_rec_count)
1181
1182 IF (l_num_loader is NOT NULL AND l_num_loader<l_rec_count) THEN
1183
1184 i:=1;
1185 l_count:=0;
1186
1187 WHILE(i<=l_rec_count) LOOP
1188
1189 LOOP
1190
1191 IF i <> 1.0 THEN
1192 exit_condition := FALSE;
1193 FOR j IN 1..i-1 LOOP
1194 IF ((g_master_rec(j).table_name = g_master_rec(i).table_name) AND
1195 (g_master_rec(j).ledger_id = g_master_rec(i).ledger_id) AND
1196 (g_master_rec(j).dataset_code = g_master_rec(i).dataset_code) AND
1197 (g_master_rec(j).source_system_code = g_master_rec(i).source_system_code) AND
1198 (g_master_rec(j).cal_period_id = g_master_rec(i).cal_period_id))
1199 THEN
1200 g_master_rec(i).request_id := g_master_rec(j).request_id;
1201 exit_condition := TRUE;
1202
1203 END IF;
1204 EXIT WHEN exit_condition = TRUE;
1205 END LOOP;
1206 END IF; -- i<> 1
1207
1208 IF NOT exit_condition THEN
1209
1210 fem_loader_eng_util_pkg.get_fact_loader_exec_mode(c_api_version,
1211 c_false,
1212 c_false,
1213 c_true,
1214 l_return_status,
1215 l_msg_count,
1216 l_msg_data,
1217 g_master_rec(i).cal_period_id,
1218 g_master_rec(i).ledger_id,
1219 g_master_rec(i).dataset_code,
1220 g_master_rec(i).source_system_code,
1221 g_master_rec(i).table_name,
1222 l_data_load_mode);
1223
1224 g_master_rec(i).request_id := fnd_request.submit_request('FEM',
1225 'FEM_SOURCE_DATA_LOADER',
1226 NULL,
1227 NULL,
1228 FALSE,
1229 gs_table_obj_def_id(g_master_rec(i).table_row),
1230 l_data_load_mode,
1231 g_master_rec(i).ledger_id,
1232 g_master_rec(i).cal_period_id,
1233 g_master_rec(i).dataset_code,
1234 g_master_rec(i).source_system_code);
1235 l_at_least_one := TRUE;
1236
1237 COMMIT;
1238 l_count := l_count+1;
1239
1240 END IF; -- exit_condition
1241
1242 i:=i+1;
1243
1244 EXIT WHEN((l_count>=l_num_loader AND mod(l_count,l_num_loader)=0) OR i>l_rec_count);
1245
1246 END LOOP; -- Internal loop
1247
1248
1249 --wait only for submitted requests
1250 IF l_at_least_one THEN
1251 fem_engines_pkg.tech_message (p_severity => g_log_level_1
1252 ,p_module => g_block||'.submit_data_loaders(PROCEDURE)'
1253 ,p_msg_text => 'Calling wait_for_requests - DATA LOAD');
1254
1255 wait_for_requests(c_data_ledger_loader);
1256
1257 fem_engines_pkg.tech_message (p_severity => g_log_level_1
1258 ,p_module => g_block||'.submit_data_loaders(PROCEDURE)'
1259 ,p_msg_text => 'Completed wait_for_requests - DATA LOAD');
1260
1261 END IF;
1262
1263 fem_engines_pkg.tech_message (p_severity => g_log_level_2
1264 ,p_module => g_block||'.submit_data_loaders(PROCEDURE)'
1265 ,p_msg_text => 'END submit_data_loaders');
1266
1267 END LOOP; --WHILE(i<=l_rec_count) LOOP
1268
1269
1270 END IF;--IF (l_num_loader is NOT NULL AND l_num_loader<l_rec_count)
1271
1272 EXCEPTION
1273 WHEN e_num_loader_neg THEN
1274
1275 FEM_ENGINES_PKG.User_Message (
1276 p_app_name => 'FEM'
1277 ,p_msg_name => 'FEM_NUM_OF_LOADERS_NEG_ERR'
1278 );
1279
1280 RAISE ;
1281 WHEN OTHERS THEN
1282 fem_engines_pkg.tech_message (p_severity => g_log_level_6
1283 ,p_module => g_block||'.submit_data_loaders(PROCEDURE)'
1284 ,p_msg_text => 'EXCEPTION in submit_data_loaders ' || sqlerrm);
1285 fnd_file.put_line(fnd_file.log, 'Exception - submit_data_loader ' || sqlerrm);
1286 RAISE;
1287
1288
1289 END submit_data_loaders;
1290
1291 --------------------------
1292 -- END submit_data_loaders
1293 --------------------------
1294
1295 --------------------------------------------------------------------------------
1296 --
1297 -- This procedure relies on g_master_rec to submit the ledger loader
1298 --
1299 --------------------------------------------------------------------------------
1300
1301
1302 PROCEDURE submit_ledger_loader(p_balance_type IN VARCHAR2) IS
1303 l_return_status VARCHAR2(1);
1304 l_msg_data VARCHAR2(4000);
1305 l_msg_count NUMBER;
1306 l_at_least_one BOOLEAN;
1307
1308 exit_condition BOOLEAN;
1309 l_ledger_load_mode VARCHAR2(1);
1310 BEGIN
1311
1312 fem_engines_pkg.tech_message (p_severity => g_log_level_2
1313 ,p_module => g_block||'.submit_ledger_loader(PROCEDURE)'
1314 ,p_msg_text => 'BEGIN..for submit_ledger_loader');
1315
1316 l_at_least_one := FALSE;
1317 exit_condition := FALSE;
1318
1319 FOR i IN 1..g_master_rec.COUNT LOOP
1320 IF i <> 1.0 THEN
1321 exit_condition := FALSE;
1322 FOR j IN 1..i-1 LOOP
1326 (NVL(g_master_rec(j).budget_id,0) = NVL(g_master_rec(i).budget_id,0)) AND
1323 IF ((g_master_rec(j).ledger_id = g_master_rec(i).ledger_id) AND
1324 (g_master_rec(j).dataset_code = g_master_rec(i).dataset_code) AND
1325 (g_master_rec(j).cal_period_id = g_master_rec(i).cal_period_id) AND
1327 (NVL(g_master_rec(j).enc_type_id,0) = NVL(g_master_rec(i).enc_type_id,0)))
1328 THEN
1329 g_master_rec(i).request_id := g_master_rec(j).request_id;
1330 exit_condition := TRUE;
1331 END IF;
1332 EXIT WHEN exit_condition = TRUE;
1333 END LOOP;
1334 END IF; -- i<> 1
1335
1336 IF NOT exit_condition THEN
1337
1338 fem_loader_eng_util_pkg.get_xgl_loader_exec_mode(c_api_version,
1339 c_false,
1340 c_false,
1341 c_true,
1342 l_return_status,
1343 l_msg_count,
1344 l_msg_data,
1345 g_master_rec(i).cal_period_id,
1346 g_master_rec(i).ledger_id,
1347 g_master_rec(i).dataset_code,
1348 l_ledger_load_mode);
1349
1350 -- The object_def_id fin CP for the external gl loader is currently hard-coded
1351 -- to 1000; maintaining the same here.
1352
1353 g_master_rec(i).request_id := fnd_request.submit_request('FEM',
1354 'FEM_XGL_POST_ENGINE',
1355 NULL,
1356 NULL,
1357 FALSE,
1358 l_ledger_load_mode,
1359 g_master_rec(i).ledger_id,
1360 g_master_rec(i).cal_period_id,
1361 g_master_rec(i).budget_id,
1362 g_master_rec(i).enc_type_id,
1363 g_master_rec(i).dataset_code,
1364 1000,
1365 p_balance_type);
1366
1367 l_at_least_one := TRUE;
1368
1369 COMMIT;
1370
1371 END IF; -- exit_condition
1372
1373 END LOOP; -- g_master_rec
1374
1375 IF l_at_least_one THEN
1376 fem_engines_pkg.tech_message (p_severity => g_log_level_1
1377 ,p_module => g_block||'.submit_ledger_loader(PROCEDURE)'
1378 ,p_msg_text => 'Calling wait_for_requests - LEDGER LOAD');
1379
1380 wait_for_requests(c_data_ledger_loader);
1381
1382 fem_engines_pkg.tech_message (p_severity => g_log_level_1
1383 ,p_module => g_block||'.submit_ledger_loader(PROCEDURE)'
1384 ,p_msg_text => 'Completed wait_for_requests - LEDGER LOAD');
1385
1386 END IF;
1387
1388 fem_engines_pkg.tech_message (p_severity => g_log_level_2
1389 ,p_module => g_block||'.submit_ledger_loader(PROCEDURE)'
1390 ,p_msg_text => 'END submit_ledger_loader');
1391
1392
1393 EXCEPTION
1394 WHEN OTHERS THEN
1395 fem_engines_pkg.tech_message (p_severity => g_log_level_6
1396 ,p_module => g_block||'.submit_ledger_loader(PROCEDURE)'
1397 ,p_msg_text => 'EXCEPTION in submit_ledger_loader ' || sqlerrm);
1398 fnd_file.put_line(fnd_file.log, 'Exception - submit_ledger_loader ' || sqlerrm);
1399 RAISE;
1400
1401 END submit_ledger_loader;
1402
1403 ----------------------------
1404 -- END submit_ledger_loaders
1405 ----------------------------
1406
1407 --------------------------------------------------------------------------------
1408 --
1409 -- This procedure submits the hierarchy loader CP. Only unique combinations are
1410 -- submitted
1411 --
1412 --------------------------------------------------------------------------------
1413
1414
1415 PROCEDURE submit_hierarchy_loaders IS
1416 l_request_id NUMBER;
1417
1418 l_table_name VARCHAR2(30);
1419
1420 l_hier_load_mode VARCHAR2(1);
1421
1422 l_return_status VARCHAR2(1);
1423 l_msg_data VARCHAR2(4000);
1424 l_msg_count NUMBER;
1425
1426 l_at_least_one BOOLEAN;
1427 BEGIN
1428
1429 fem_engines_pkg.tech_message (p_severity => g_log_level_2
1430 ,p_module => g_block||'.submit_hierarchy_loader(PROCEDURE)'
1431 ,p_msg_text => 'BEGIN..for submit_hierarchy_loader');
1432
1433 l_at_least_one := FALSE;
1434
1435 FOR hier_rec IN (SELECT DISTINCT
1436 dimension_id,
1437 dimension_varchar_label,
1441 LOOP
1438 hierarchy_object_name,
1439 hier_obj_def_display_name
1440 FROM fem_ld_hier_requests_gt )
1442
1443 fem_loader_eng_util_pkg.get_hier_loader_exec_mode(c_api_version,
1444 c_false,
1445 c_false,
1446 c_true,
1447 l_return_status,
1448 l_msg_count,
1449 l_msg_data,
1450 hier_rec.dimension_id,
1451 hier_rec.hierarchy_object_name,
1452 l_hier_load_mode);
1453
1454 l_request_id := fnd_request.submit_request('FEM',
1455 'FEM_HIER_LOADER',
1456 NULL,
1457 NULL,
1458 FALSE,
1459 g_hier_object_def_id,
1460 l_hier_load_mode,
1461 hier_rec.dimension_varchar_label,
1462 hier_rec.hierarchy_object_name,
1463 hier_rec.hier_obj_def_display_name);
1464
1465 COMMIT;
1466
1467 l_at_least_one := TRUE;
1468
1469 UPDATE fem_ld_hier_requests_gt
1470 SET request_id = l_request_id
1471 WHERE hier_obj_def_display_name = hier_rec.hier_obj_def_display_name
1472 AND dimension_id = hier_rec.dimension_id;
1473
1474 END LOOP;
1475
1476
1477 IF l_at_least_one THEN
1478 fem_engines_pkg.tech_message (p_severity => g_log_level_1
1479 ,p_module => g_block||'.submit_hierarchy_loaders(PROCEDURE)'
1480 ,p_msg_text => 'Calling wait_for_requests - HIERARCHY');
1481
1482 wait_for_requests(c_hier_loader);
1483
1484 fem_engines_pkg.tech_message (p_severity => g_log_level_1
1485 ,p_module => g_block||'.submit_hierarchy_loaders(PROCEDURE)'
1486 ,p_msg_text => 'Completed wait_for_requests - HIERARCHY');
1487 END IF;
1488
1489 fem_engines_pkg.tech_message (p_severity => g_log_level_2
1490 ,p_module => g_block||'.submit_hierarchy_loader(PROCEDURE)'
1491 ,p_msg_text => 'END submit_hierarchy_loader');
1492
1493
1494 EXCEPTION
1495 WHEN OTHERS THEN
1496 fem_engines_pkg.tech_message (p_severity => g_log_level_6
1497 ,p_module => g_block||'.submit_hierarchy_loader(PROCEDURE)'
1498 ,p_msg_text => 'EXCEPTION in submit_hierarchy_loader ' || sqlerrm);
1499 fnd_file.put_line(fnd_file.log, 'Exception - submit_hierarchy_loaders ' || sqlerrm);
1500 RAISE;
1501
1502 END submit_hierarchy_loaders;
1503
1504 -------------------------------
1505 -- END submit_hierarchy_loaders
1506 -------------------------------
1507
1508 --------------------------------------------------------------------------------
1509 --
1510 -- This procedure populates the list of all dimensions across all the unique
1511 -- tables. If the user has selected the option of loading the hierarchies as
1512 -- well, populates fem_ld_hier_requests_gt with the info.
1513 --
1514 --------------------------------------------------------------------------------
1515
1516 PROCEDURE build_dim_stages IS
1517 l_dummy NUMBER;
1518 l_dimension_load BOOLEAN;
1519 BEGIN
1520
1521 fem_engines_pkg.tech_message (p_severity => g_log_level_2
1522 ,p_module => g_block||'.build_dim_stages(PROCEDURE)'
1523 ,p_msg_text => 'BEGIN..for build_dim_stages');
1524
1525 l_dummy := 0.0;
1526 l_dimension_load := FALSE;
1527
1528 FOR i IN 1..gs_table_name_tab.COUNT LOOP
1529 fem_engines_pkg.tech_message (p_severity => g_log_level_1
1530 ,p_module => g_block||'.build_dim_stages(PROCEDURE)'
1531 ,p_msg_text => 'Building dim stages for table :: ' ||
1532 gs_table_name_tab(i) ||
1533 ' Load Option :: ' ||
1534 gs_load_opt_tab(i) ||
1535 ' for load_type :: ' || g_loader_type );
1536
1537 IF gs_load_opt_tab(i) IN ('DD', 'DDH') THEN
1538 l_dimension_load := TRUE;
1539 IF g_loader_type = 'CLIENT' THEN
1540 BEGIN
1541 SELECT 1.0
1542 INTO l_dummy
1543 FROM fem_ld_dim_requests_gt
1544 WHERE table_name = gs_table_name_tab(i)
1545 AND ROWNUM = 1;
1546 EXCEPTION
1547 WHEN NO_DATA_FOUND THEN
1548 l_dummy := 0.0;
1552 IF l_dummy <> 1 THEN
1549 END;
1550 END IF; -- g_loader_type = 'CLIENT'
1551
1553 BEGIN
1554 INSERT INTO fem_ld_dim_requests_gt(dimension_id,
1555 dimension_varchar_label,
1556 table_name,
1557 dim_intf_table_name,
1558 request_id,
1559 status)
1560 SELECT fdb.dimension_id,
1561 dimension_varchar_label,
1562 gs_table_name_tab(i),
1563 intf_member_b_table_name,
1564 TO_NUMBER(NULL),
1565 'N'
1566 FROM fem_tab_columns_b ftcb,
1567 fem_dimensions_b fdb,
1568 fem_xdim_dimensions fxd
1569 WHERE table_name = gs_table_name_tab(i)
1570 AND fem_data_type_code = 'DIMENSION'
1571 AND fdb.dimension_id = ftcb.dimension_id
1572 AND fxd.dimension_id = fdb.dimension_id
1573 AND intf_member_b_table_name IS NOT NULL;
1574
1575 IF g_loader_type = 'LEDGER' THEN
1576 l_dummy := 1.0;
1577 END IF;
1578
1579 EXCEPTION
1580 WHEN OTHERS THEN
1581 fem_engines_pkg.tech_message (p_severity => g_log_level_6
1582 ,p_module => g_block||'.build_dim_stages(PROCEDURE)'
1583 ,p_msg_text => 'EXCEPTION in build_dim_stages - insert into
1584 fem_ld_dim_requests_gt');
1585 fnd_file.put_line(fnd_file.log, 'Exception - build_dim_stages ' || sqlerrm);
1586 RAISE;
1587 END;
1588
1589 END IF; -- l_dummy <> 1
1590
1591 END IF; -- gs_load_opt_tab(i) = 'DD'
1592
1593 l_dummy := 0.0;
1594
1595 IF gs_load_opt_tab(i) = 'DDH' THEN
1596 g_hierarchy_exists := TRUE;
1597 IF g_hier_object_def_id IS NULL THEN
1598 BEGIN
1599 SELECT object_definition_id
1600 INTO g_hier_object_def_id
1601 FROM fem_object_definition_vl d
1602 WHERE d.object_id in (SELECT o.object_id
1603 FROM fem_object_catalog_vl o
1604 WHERE o.object_type_code = 'HIERARCHY_LOADER'
1605 AND o.folder_id in (SELECT f.folder_id
1606 FROM fem_user_folders f
1607 WHERE f.user_id = fnd_global.user_id)
1608 )
1609 AND d.old_approved_copy_flag = 'N'
1610 AND d.approval_status_code NOT IN ('SUBMIT_DELETE','SUBMIT_APPROVAL');
1611 EXCEPTION
1612 WHEN OTHERS THEN
1613 fem_engines_pkg.tech_message (p_severity => g_log_level_6
1614 ,p_module => g_block||'.build_dim_stages(PROCEDURE)'
1615 ,p_msg_text => 'EXCEPTION in build_dim_stages - fetching
1616 object_def_id for HIER');
1617 fnd_file.put_line(fnd_file.log, 'Exception - build_dim_stages ' || sqlerrm);
1618 RAISE;
1619 END;
1620
1621 END IF; -- l_object_def_id IS NULL
1622
1623 IF g_loader_type = 'CLIENT' THEN
1624 BEGIN
1625 SELECT 1.0
1626 INTO l_dummy
1627 FROM fem_ld_hier_requests_gt
1628 WHERE table_name = gs_table_name_tab(i)
1629 AND ROWNUM = 1 ;
1630 EXCEPTION
1631 WHEN NO_DATA_FOUND THEN
1632 l_dummy := 0.0;
1633 END;
1634 END IF; -- g_loader_type = 'CLIENT'
1635
1636 IF l_dummy <> 1.0 THEN
1637 BEGIN
1638 INSERT INTO fem_ld_hier_requests_gt(dimension_id,
1639 dimension_varchar_label,
1640 hierarchy_object_name,
1641 hier_obj_def_display_name,
1642 table_name,
1643 request_id)
1644 SELECT drt.dimension_id,
1645 drt.dimension_varchar_label,
1646 fht.hierarchy_object_name,
1647 fht.hier_obj_def_display_name,
1648 gs_table_name_tab(i),
1649 TO_NUMBER(NULL)
1650 FROM fem_ld_dim_requests_gt drt,
1651 fem_hierarchies_t fht
1652 WHERE table_name = gs_table_name_tab(i)
1656 fem_engines_pkg.tech_message (p_severity => g_log_level_6
1653 AND drt.dimension_varchar_label = fht.dimension_varchar_label;
1654 EXCEPTION
1655 WHEN OTHERS THEN
1657 ,p_module => g_block||'.build_dim_stages(PROCEDURE)'
1658 ,p_msg_text => 'EXCEPTION in build_dim_stages - insert into
1659 fem_ld_hier_requests_gt');
1660 fnd_file.put_line(fnd_file.log, 'Exception - build_dim_stages ' || sqlerrm);
1661 RAISE;
1662 END;
1663
1664 END IF; -- l_dummy <> 1
1665
1666 END IF; -- gs_load_opt_tab(i) = 'DDH'
1667
1668 END LOOP; -- 1..gs_table_name_tab.COUNT
1669
1670 IF l_dimension_load THEN
1671 fem_engines_pkg.tech_message (p_severity => g_log_level_1
1672 ,p_module => g_block||'.build_dim_stages(PROCEDURE)'
1673 ,p_msg_text => 'Calling submit_dimension_loaders');
1674
1675 submit_dimension_loaders;
1676
1677 fem_engines_pkg.tech_message (p_severity => g_log_level_1
1678 ,p_module => g_block||'.build_dim_stages(PROCEDURE)'
1679 ,p_msg_text => 'Completed submit_dimension_loaders');
1680 END IF;
1681
1682 fem_engines_pkg.tech_message (p_severity => g_log_level_2
1683 ,p_module => g_block||'.build_dim_stages(PROCEDURE)'
1684 ,p_msg_text => 'END build_dim_stages');
1685
1686
1687 EXCEPTION
1688 WHEN OTHERS THEN
1689 fem_engines_pkg.tech_message (p_severity => g_log_level_6
1690 ,p_module => g_block||'.build_dim_stages(PROCEDURE)'
1691 ,p_msg_text => 'EXCEPTION in build_dim_stages ' || sqlerrm);
1692 fnd_file.put_line(fnd_file.log, 'Exception - build_dim_stages ' || sqlerrm);
1693 RAISE;
1694
1695 END build_dim_stages;
1696
1697 -----------------------
1698 -- END build_dim_stages
1699 -----------------------
1700
1701
1702 --------------------------------------------------------------------------------
1703 --
1704 --
1705 --
1706 --
1707 --------------------------------------------------------------------------------
1708
1709 PROCEDURE print_params IS
1710
1711 BEGIN
1712
1713 fnd_file.put_line(FND_FILE.log, '=============================================================================');
1714 fnd_file.put_line(FND_FILE.log, '======================== Printing Parameters ==========================');
1715
1716 FOR i IN 1..gs_table_name_tab.COUNT LOOP
1717 fnd_file.put_line(FND_FILE.log, ' Table Name :: ' || gs_table_name_tab(i));
1718 fnd_file.put_line(FND_FILE.log, ' Ledger :: ' || gs_ledger_tab(i));
1719 fnd_file.put_line(FND_FILE.log, ' Dataset :: ' || gs_ds_tab(i));
1720 fnd_file.put_line(FND_FILE.log, ' SQL Stmt :: ' || gs_sql_stmt_tab(i));
1721 fnd_file.put_line(FND_FILE.log, ' Load Option:: ' || gs_load_opt_tab(i));
1722 IF g_loader_type = 'CLIENT' THEN
1723 fnd_file.put_line(FND_FILE.log, ' Table Obj :: ' || gs_table_obj_def_id(i));
1724 END IF;
1725 END LOOP;
1726
1727 fnd_file.put_line(FND_FILE.log, '======================== End Printing Parameters ======================');
1728 fnd_file.put_line(FND_FILE.log, '=============================================================================');
1729
1730
1731 END print_params;
1732
1733 -------------------
1734 -- END print_params
1735 -------------------
1736
1737 --------------------------------------------------------------------------------
1738 --
1739 -- This is one of the core procedure. Approach is as follows..
1740 --
1741 -- 1. Builds the dynamic SQL to get DISTINCT values
1742 -- 2. If a table name has been repeated more than once, then the distinct values
1743 -- already reside in the fem_ld_interface_data_gt and makes no sense in querying
1744 -- the interface table; updates the g*dup tables for the index
1745 -- 3. Fetches the unique set of records, populates the fem_ld_interface_data_gt
1746 -- for all the tables specified by the user
1747 -- 3a. If a table has been repeated, then it inserts the same set querying on
1748 -- the data for the same table in the I occurence
1749 -- 4. Fetching of unique set differs for the DATA and LEDGER load, while DATA
1750 -- load relies on the table name, LEDGER load relies on the dataset balance
1751 -- type code; otherwise the concept remains the same
1752 -- 5. Then the corresponding ID's are populated for the records fetched into the
1753 -- fem_ld_interface_data_gt table
1754 -- 6. If the above updates fetches 0 records, then there is no point in proceeding
1755 -- captures this info. in g_loader_run
1756 -- 6a. If the update results in more than 0 then proceeds to the next step
1757 -- 7. The next step is to identify all the datasets that are production datasets
1758 -- 8. Once this is done, the fem_ld_interface_data_gt is validated with the i/p params
1759 -- specified in the selection criteria while defining the parameters
1760 -- 9. All the records that match this crietria are marked with status = 'VALID'
1761 -- 9a. gs_valid_rows is updated with the number of records updated
1765 --11. If the rule is a LEDGER load ::
1762 --10. All the records with status = 'INVALID' are deleted, these are stored in
1763 -- g_inv* pl/sql tables. This might help us in the future for better error
1764 -- reporting
1766 -- Populate the encumbrnace_type_id
1767 -- Populate the budget_id
1768 --12. In the last of the steps populates the calendar_id, cal_period
1769 --
1770 --------------------------------------------------------------------------------
1771
1772
1773 PROCEDURE evaluate_parameters IS
1774 l_dummy NUMBER;
1775 l_int_table_name VARCHAR2(30);
1776 l_bal_type_code VARCHAR2(30);
1777
1778 l_ledger_tab char_table;
1779 l_dataset_tab char_table;
1780 l_source_system_tab char_table;
1781 l_cal_period_number_tab number_table;
1782 l_cal_period_level_tab char_table;
1783 l_cal_period_end_date_tab date_table;
1784 l_table_name_tab char_table;
1785 l_table_row_tab number_table;
1786 l_dataset_code_tab number_table;
1787 l_ds_bal_code_tab char_table;
1788 l_budget_display_cd_tab char_table;
1789 l_encumbrance_type_cd_tab char_table;
1790
1791 no_interface_table_exists BOOLEAN;
1792
1793 l_table_name VARCHAR2(30);
1794 l_table_row NUMBER;
1795 l_ledger_rows NUMBER;
1796
1797 l_dupe_count NUMBER;
1798 l_dupe_position NUMBER;
1799 l_dupe_text VARCHAR2(20);
1800 BEGIN
1801
1802 fem_engines_pkg.tech_message (p_severity => g_log_level_2
1803 ,p_module => g_block||'.build_dim_stages(PROCEDURE)'
1804 ,p_msg_text => 'BEGIN..for evaluate_parameters');
1805
1806 l_dummy := 0.0;
1807
1808 IF g_loader_type = 'CLIENT' THEN
1809
1810 FOR i IN 1..gs_table_name_tab.COUNT LOOP
1811
1812 no_interface_table_exists := FALSE;
1813
1814 BEGIN
1815 SELECT interface_table_name
1816 INTO l_int_table_name
1817 FROM fem_tables_b
1818 WHERE table_name = gs_table_name_tab(i);
1819 EXCEPTION
1820 WHEN NO_DATA_FOUND THEN
1821 no_interface_table_exists := TRUE;
1822 END;
1823
1824 -- Need to check for the physical existence of the table
1825
1826 BEGIN
1827 EXECUTE IMMEDIATE 'SELECT 1 FROM ' || gs_table_name_tab(i) || ' WHERE ROWNUM=1';
1828 EXCEPTION
1829 WHEN OTHERS THEN
1830 fnd_file.put_line(fnd_file.log, 'Table ' || gs_table_name_tab(i) || ' does not exist ');
1831 fnd_file.put_line(fnd_file.log, 'Exception - evaluate_parameters ' || sqlerrm);
1832 RAISE;
1833 END;
1834
1835 l_dupe_count := 0.0;
1836 l_dupe_position := -1.0;
1837 l_dupe_text := c_not_dupe_text;
1838
1839 IF NOT no_interface_table_exists THEN
1840 gs_sql_stmt_tab(i) := gs_sql_stmt_tab(i) || l_int_table_name;
1841 gs_sql_stmt_tab(i) := gs_sql_stmt_tab(i) || ' GROUP BY ledger_display_code,
1842 dataset_display_code,
1843 source_system_display_code,
1844 cal_period_number,
1845 calp_dim_grp_display_code,
1846 cal_period_end_date';
1847
1848 -- If the same table is selected multiple times in the parameters
1849 -- screen, we need not get the DISTINCT from the interface table
1850 -- , instead can get it from fem_ld_interface_data_gt.
1851
1852 -- So need to check for the same. The same can be done before this
1853 -- loop; since we loop on the table_name for the first time here in
1854 -- the code, keeping this piece here.
1855
1856 FOR j IN 1..i LOOP
1857 IF gs_table_name_tab(i) = gs_table_name_tab(j) THEN
1858
1859 l_dupe_count := l_dupe_count + 1.0;
1860
1861 IF l_dupe_count = 1.0 THEN
1862 l_dupe_position := j;
1863 END IF;
1864
1865 IF l_dupe_count = 2.0 THEN
1866 l_dupe_text := c_dupe_text;
1867 EXIT;
1868 END IF;
1869
1870 END IF;
1871 END LOOP;
1872
1873 gs_valid_rows(i) := -1.0; -- Dummy initialization
1874
1875 ELSE
1876 gs_sql_stmt_tab(i) := NULL;
1877 gs_valid_rows(i) := -2.0; -- Useful for printing message to the user that there is no
1878 -- interface table defined
1879 END IF;
1880
1881 gs_sql_dup_tab(i) := l_dupe_text;
1882 gs_sql_dup_indx_tab(i) := l_dupe_position;
1883
1884 END LOOP; -- 1..gs_table_name_tab.COUNT
1885
1886 FOR i IN 1..gs_sql_stmt_tab.COUNT LOOP
1890 EXECUTE IMMEDIATE gs_sql_stmt_tab(i) BULK COLLECT INTO
1887 CASE gs_sql_dup_tab(i)
1888 WHEN 'DATA_NOT_FETCHED' THEN
1889 IF gs_sql_stmt_tab(i) IS NOT NULL THEN
1891 l_ledger_tab,
1892 l_dataset_tab,
1893 l_source_system_tab,
1894 l_cal_period_number_tab,
1895 l_cal_period_level_tab,
1896 l_cal_period_end_date_tab,
1897 l_table_name_tab,
1898 l_table_row_tab;
1899 END IF;
1900
1901 -- Check if there is data in the interface table
1902 -- if not flag to the exception report
1903
1904 IF l_ledger_tab.EXISTS(1) THEN
1905 FORALL k IN 1..l_ledger_tab.COUNT
1906 INSERT INTO fem_ld_interface_data_gt
1907 (ledger_display_code,
1908 dataset_display_code,
1909 source_system_display_code,
1910 cal_period_number,
1911 cal_period_level,
1912 cal_period_end_date,
1913 table_name,
1914 table_row,
1915 ds_production_valid_flag,
1916 status)
1917 VALUES
1918 (l_ledger_tab(k),
1919 l_dataset_tab(k),
1920 l_source_system_tab(k),
1921 l_cal_period_number_tab(k),
1922 l_cal_period_level_tab(k),
1923 l_cal_period_end_date_tab(k),
1924 l_table_name_tab(k),
1925 l_table_row_tab(k),
1926 'N',
1927 'INVALID');
1928
1929 gs_valid_rows(i) := 1.0;
1930 ELSE
1931 IF gs_sql_stmt_tab(i) IS NULL THEN
1932 gs_valid_rows(i) := -2.0; -- No interface table exists
1933 ELSE
1934 gs_valid_rows(i) := -1.0; -- No data found in the interface table
1935 END IF;
1936 END IF; -- l_ledger_tab.EXISTS(1)
1937
1938 WHEN 'DATA_FETCHED' THEN
1939
1940 -- Data has been retrieved from the interface table
1941 -- no point in fetching it again; instead copy the same
1942 -- from the fem_ld_interface_data_gt with the new ROW_NUMBER
1943
1944 l_table_name := gs_table_name_tab(i);
1945 l_table_row := gs_sql_dup_indx_tab(i);
1946
1947 IF gs_valid_rows(l_table_row) = 1.0 THEN
1948 FORALL k IN l_table_row+1..gs_table_name_tab.COUNT
1949 INSERT INTO fem_ld_interface_data_gt
1950 (ledger_display_code,
1951 dataset_display_code,
1952 source_system_display_code,
1953 cal_period_number,
1954 cal_period_level,
1955 cal_period_end_date,
1956 table_name,
1957 table_row,
1958 ds_production_valid_flag,
1959 status)
1960 SELECT
1961 ledger_display_code,
1962 dataset_display_code,
1963 source_system_display_code,
1964 cal_period_number,
1965 cal_period_level,
1966 cal_period_end_date,
1967 table_name,
1968 gs_table_row_tab(k),
1969 ds_production_valid_flag,
1970 status
1971 FROM fem_ld_interface_data_gt
1972 WHERE table_name = l_table_name
1973 AND table_row = l_table_row
1974 AND gs_table_name_tab(k) = l_table_name;
1975 END IF;
1976
1977 FOR k IN 1..gs_table_name_tab.COUNT LOOP
1978 IF gs_table_name_tab(k) = l_table_name AND l_table_row <> k THEN
1979 gs_sql_dup_tab(k) := 'DATA_LOADED_MULTIPLE_TIMES';
1980 gs_valid_rows(k) := gs_valid_rows(l_table_row);
1981 END IF;
1982 END LOOP;
1983
1984 ELSE
1985 NULL; -- All conditions handled
1986 END CASE; -- WHEN 'DATA_NOT_FETCHED'
1987
1988 END LOOP; -- 1..gs_sql_stmt_tab.COUNT
1989
1990 FOR k IN 1..gs_table_name_tab.COUNT LOOP
1991 fnd_file.put_line(FND_FILE.log, ' ==========================================================');
1992 fnd_file.put_line(FND_FILE.log, ' In Evaluate parameters(0) ');
1993 fnd_file.put_line(FND_FILE.log, ' Table Name :: ' || gs_table_name_tab(k));
1994 fnd_file.put_line(FND_FILE.log, ' Table Row :: ' || gs_table_row_tab(k));
1995 fnd_file.put_line(FND_FILE.log, ' Dup Row :: ' || gs_sql_dup_indx_tab(k));
1999
1996 fnd_file.put_line(FND_FILE.log, ' Dup SQL :: ' || gs_sql_dup_tab(k) );
1997 fnd_file.put_line(FND_FILE.log, ' Valid Row :: ' || gs_valid_rows(k));
1998 fnd_file.put_line(FND_FILE.log, ' ==========================================================');
2000 fem_engines_pkg.tech_message (p_severity => g_log_level_1
2001 ,p_module => g_block||'.evaluate_parameters(0)'
2002 ,p_msg_text => ' Table Name :: ' || gs_table_name_tab(k) ||
2003 ' Table Row :: ' || gs_table_row_tab(k) ||
2004 ' Dup Row :: ' || gs_sql_dup_indx_tab(k) ||
2005 ' Dup SQL :: ' || gs_sql_dup_tab(k) ||
2006 ' Valid Row :: ' || gs_valid_rows(k));
2007
2008 END LOOP;
2009
2010 UPDATE fem_ld_interface_data_gt idt
2011 SET ledger_id = (SELECT ledger_id
2012 FROM fem_ledgers_b flb
2013 WHERE idt.ledger_display_code = flb.ledger_display_code
2014 AND personal_flag = 'N'
2015 AND enabled_flag = 'Y'),
2016 dataset_code = (SELECT dataset_code
2017 FROM fem_datasets_b fdb
2018 WHERE idt.dataset_display_code = fdb.dataset_display_code
2019 AND personal_flag = 'N'
2020 AND enabled_flag = 'Y'),
2021 source_system_code = (SELECT source_system_code
2022 FROM fem_source_systems_b fssb
2023 WHERE idt.source_system_display_code = fssb.source_system_display_code
2024 AND personal_flag = 'N'
2025 AND enabled_flag = 'Y'),
2026 (dimension_group_id,time_dimension_group_key) = (SELECT dimension_group_id, time_dimension_group_key
2027 FROM fem_dimension_grps_b fdgb
2028 WHERE idt.cal_period_level =
2029 fdgb.dimension_group_display_code
2030 AND personal_flag = 'N'
2031 AND enabled_flag = 'Y');
2032 fem_engines_pkg.tech_message(p_severity => g_log_level_1
2033 ,p_module => g_block||'.evaluate_parameters'
2034 ,p_msg_text => ' Rows updated after ID population(DATA LOAD) :: ' || SQL%ROWCOUNT);
2035
2036 fnd_file.put_line(fnd_file.log, ' Rows updated after ID population :: ' || SQL%ROWCOUNT);
2037
2038 ELSE -- g_loader_type = 'LEDGER'
2039
2040 FOR i IN 1..gs_ledger_tab.COUNT LOOP
2041
2042 l_dupe_count := 0.0;
2043 l_dupe_position := -1.0;
2044 l_dupe_text := c_not_dupe_text;
2045
2046 SELECT dim_attribute_varchar_member
2047 INTO l_bal_type_code
2048 FROM fem_datasets_attr
2049 WHERE dataset_code = gs_ds_tab(i)
2050 AND attribute_id = g_dataset_bal_attr;
2051
2052 gs_ds_bal_code_tab(i) := l_bal_type_code;
2053
2054 gs_sql_stmt_tab(i) := gs_sql_stmt_tab(i) || g_int_table_name;
2055 gs_sql_stmt_tab(i) := gs_sql_stmt_tab(i) || ' WHERE ds_balance_type_code = ' || '''' || l_bal_type_code
2056 ||'''';
2057 gs_sql_stmt_tab(i) := gs_sql_stmt_tab(i) || ' GROUP BY cal_period_number,
2058 cal_period_end_date,
2059 cal_per_dim_grp_display_code,
2060 ledger_display_code,
2061 ds_balance_type_code,
2062 budget_display_code,
2063 encumbrance_type_code';
2064
2065 -- If the same DS bal type is selected multiple times in the parameters
2066 -- screen, we need not get the DISTINCT from the interface table
2067 -- , instead can get it from fem_ld_interface_data_gt.
2068
2069 FOR j IN 1..i LOOP
2070 IF gs_ds_bal_code_tab(i) = gs_ds_bal_code_tab(j) THEN
2071 l_dupe_count := l_dupe_count + 1.0;
2072
2073 IF l_dupe_count = 1.0 THEN
2074 l_dupe_position := j;
2075 END IF;
2076
2077 IF l_dupe_count = 2.0 THEN
2078 l_dupe_text := c_dupe_text;
2079 EXIT;
2080 END IF;
2081
2082 END IF;
2083 END LOOP;
2084
2085 gs_valid_rows(i) := -1.0; -- Dummy initialization
2086
2087 gs_sql_dup_tab(i) := l_dupe_text;
2088 gs_sql_dup_indx_tab(i) := l_dupe_position;
2089
2093
2090 END LOOP; -- 1..gs_table_name_tab.COUNT
2091
2092 l_ledger_rows := gs_ledger_tab.COUNT;
2094 FOR i IN 1..gs_sql_stmt_tab.COUNT LOOP
2095 CASE gs_sql_dup_tab(i)
2096
2097 WHEN 'DATA_NOT_FETCHED' THEN
2098 IF l_ledger_rows > 0.0 THEN
2099 EXECUTE IMMEDIATE gs_sql_stmt_tab(1) BULK COLLECT INTO
2100 l_cal_period_number_tab,
2101 l_cal_period_end_date_tab,
2102 l_cal_period_level_tab,
2103 l_ledger_tab,
2104 l_ds_bal_code_tab,
2105 l_budget_display_cd_tab,
2106 l_encumbrance_type_cd_tab,
2107 l_table_name_tab,
2108 l_table_row_tab;
2109
2110 -- Check if there is data in the interface table
2111 -- if not flag to the exception report
2112
2113 IF l_ledger_tab.EXISTS(1) THEN
2114 FORALL k IN 1..l_ledger_tab.COUNT
2115 INSERT INTO fem_ld_interface_data_gt
2116 (ledger_display_code,
2117 dataset_code,
2118 balance_type_code,
2119 budget_display_code,
2120 encumbrance_type_code,
2121 cal_period_number,
2122 cal_period_level,
2123 cal_period_end_date,
2124 table_name,
2125 table_row,
2126 ds_production_valid_flag,
2127 status)
2128 VALUES
2129 (l_ledger_tab(k),
2130 gs_ds_tab(1),
2131 l_ds_bal_code_tab(k),
2132 l_budget_display_cd_tab(k),
2133 l_encumbrance_type_cd_tab(k),
2134 l_cal_period_number_tab(k),
2135 l_cal_period_level_tab(k),
2136 l_cal_period_end_date_tab(k),
2137 l_table_name_tab(k),
2138 l_table_row_tab(k),
2139 'N',
2140 'INVALID');
2141
2142 gs_valid_rows(1) := 1.0;
2143 ELSE
2144 gs_valid_rows(1) := -1.0; -- No data exists in the interface table
2145 END IF; -- l_ledger_tab.EXISTS(1)
2146
2147 END IF; -- ledger_rows > 0
2148
2149 WHEN 'DATA_FETCHED' THEN
2150
2151 -- Data has been retrieved from the interface table
2152 -- no point in fetching it again; instead copy the same
2153 -- from the fem_ld_interface_data_gt with the new ROW_NUMBER
2154
2155 l_table_name := gs_ds_bal_code_tab(i);
2156 l_table_row := gs_sql_dup_indx_tab(i);
2157
2158 IF gs_valid_rows(l_table_row) = 1.0 THEN
2159 FORALL k IN l_table_row+1..gs_table_name_tab.COUNT
2160 INSERT INTO fem_ld_interface_data_gt
2161 (ledger_display_code,
2162 dataset_code,
2163 balance_type_code,
2164 budget_display_code,
2165 encumbrance_type_code,
2166 cal_period_number,
2167 cal_period_level,
2168 cal_period_end_date,
2169 table_name,
2170 table_row,
2171 ds_production_valid_flag,
2172 status)
2173 SELECT
2174 ledger_display_code,
2175 gs_ds_tab(k),
2176 balance_type_code,
2177 budget_display_code,
2178 encumbrance_type_code,
2179 cal_period_number,
2180 cal_period_level,
2181 cal_period_end_date,
2182 table_name,
2183 gs_table_row_tab(k),
2184 ds_production_valid_flag,
2185 status
2186 FROM fem_ld_interface_data_gt
2187 WHERE balance_type_code = l_table_name
2188 AND table_row = l_table_row
2189 AND gs_ds_bal_code_tab(k) = l_table_name;
2190 END IF;
2191
2192 FOR k IN 1..gs_table_name_tab.COUNT LOOP
2193 IF gs_ds_bal_code_tab(k) = l_table_name AND l_table_row <> k THEN
2194 gs_sql_dup_tab(k) := 'DATA_LOADED_MULTIPLE_TIMES';
2195 gs_valid_rows(k) := gs_valid_rows(l_table_row);
2196 END IF;
2197 END LOOP;
2198
2199 ELSE
2200 NULL; -- All conditions handled
2201 END CASE; -- WHEN 'DATA_NOT_FETCHED'
2202
2203 END LOOP; -- 1..gs_sql_stmt_tab.COUNT
2204
2205 FOR k IN 1..gs_table_name_tab.COUNT LOOP
2206 fnd_file.put_line(FND_FILE.log, ' ==========================================================');
2210 fnd_file.put_line(FND_FILE.log, ' Dup Row :: ' || gs_sql_dup_indx_tab(k));
2207 fnd_file.put_line(FND_FILE.log, ' In Evaluate parameters(0) ');
2208 fnd_file.put_line(FND_FILE.log, ' Table Name :: ' || gs_table_name_tab(k));
2209 fnd_file.put_line(FND_FILE.log, ' Table Row :: ' || gs_table_row_tab(k));
2211 fnd_file.put_line(FND_FILE.log, ' Dup SQL :: ' || gs_sql_dup_tab(k) );
2212 fnd_file.put_line(FND_FILE.log, ' Valid Row :: ' || gs_valid_rows(k));
2213 fnd_file.put_line(FND_FILE.log, ' ==========================================================');
2214
2215 fem_engines_pkg.tech_message (p_severity => g_log_level_1
2216 ,p_module => g_block||'.evaluate_parameters(0)'
2217 ,p_msg_text => ' Table Name :: ' || gs_table_name_tab(k) ||
2218 ' Table Row :: ' || gs_table_row_tab(k) ||
2219 ' Dup Row :: ' || gs_sql_dup_indx_tab(k) ||
2220 ' Dup SQL :: ' || gs_sql_dup_tab(k) ||
2221 ' Valid Row :: ' || gs_valid_rows(k));
2222
2223 END LOOP;
2224
2225
2226 UPDATE fem_ld_interface_data_gt idt
2227 SET ledger_id = (SELECT ledger_id
2228 FROM fem_ledgers_b flb
2229 WHERE idt.ledger_display_code = flb.ledger_display_code
2230 AND personal_flag = 'N'
2231 AND enabled_flag = 'Y'),
2232 dataset_display_code = (SELECT dataset_display_code
2233 FROM fem_datasets_b fdb
2234 WHERE idt.dataset_code = fdb.dataset_code
2235 AND personal_flag = 'N'
2236 AND enabled_flag = 'Y'),
2237 (dimension_group_id,time_dimension_group_key) = (SELECT dimension_group_id, time_dimension_group_key
2238 FROM fem_dimension_grps_b fdgb
2239 WHERE idt.cal_period_level =
2240 fdgb.dimension_group_display_code
2241 AND fdgb.personal_flag = 'N'
2242 AND fdgb.enabled_flag = 'Y');
2243
2244 fem_engines_pkg.tech_message(p_severity => g_log_level_1
2245 ,p_module => g_block||'.evaluate_parameters'
2246 ,p_msg_text => ' Rows updated after ID population(LEDGER LOAD) :: ' || SQL%ROWCOUNT);
2247
2248 fnd_file.put_line(fnd_file.log, ' Rows updated after ID population :: ' || SQL%ROWCOUNT);
2249
2250 END IF; -- g_loader_type = 'LEDGER'
2251
2252 IF SQL%ROWCOUNT > 0 THEN
2253 g_loader_run := TRUE;
2254 ELSE
2255 g_loader_run := FALSE; -- No data found in the interface; no point in proceeding beyond this
2256 END IF;
2257
2258 IF g_loader_run THEN
2259
2260 -- Validate the rule; if NOT APPROVED then need to run the loaders
2261 -- ONLY against the non-production datasets
2262
2263 -- If the rule is approved, can run against any dataset
2264 -- Else cannot run against the production datasets
2265
2266 IF g_approval_flag THEN
2267 UPDATE fem_ld_interface_data_gt
2268 SET ds_production_valid_flag = 'Y';
2269 ELSE
2270 UPDATE fem_ld_interface_data_gt idt
2271 SET ds_production_valid_flag = (SELECT DECODE(dim_attribute_varchar_member,'Y','N','Y')
2272 FROM fem_datasets_attr fda
2273 WHERE fda.attribute_id = g_production_attr
2274 AND idt.dataset_code = fda.dataset_code);
2275 END IF; -- g_approval_flag
2276
2277 FOR int_rec IN (SELECT ledger_id, dataset_code, ds_production_valid_flag, table_name, table_row
2278 FROM fem_ld_interface_data_gt
2279 ORDER BY table_row, table_name)
2280 LOOP
2281 fnd_file.put_line(FND_FILE.log, ' ==========================================================');
2282 fnd_file.put_line(FND_FILE.log, ' In Evaluate parameters(1) ');
2283 fnd_file.put_line(FND_FILE.log, ' Table Name :: ' || int_rec.table_name);
2284 fnd_file.put_line(FND_FILE.log, ' Table Row :: ' || int_rec.table_row);
2285 fnd_file.put_line(FND_FILE.log, ' Ledger :: ' || int_rec.ledger_id);
2286 fnd_file.put_line(FND_FILE.log, ' Dataset :: ' || int_rec.dataset_code);
2287 fnd_file.put_line(FND_FILE.log, ' ds valid :: ' || int_rec.ds_production_valid_flag);
2288 fnd_file.put_line(FND_FILE.log, ' ==========================================================');
2289
2290 fem_engines_pkg.tech_message (p_severity => g_log_level_1
2291 ,p_module => g_block||'.evaluate_parameters(1)'
2292 ,p_msg_text => ' Table Name :: ' || int_rec.table_name ||
2296 ' ds valid :: ' || int_rec.ds_production_valid_flag);
2293 ' Table Row :: ' || int_rec.table_row ||
2294 ' Ledger :: ' || int_rec.ledger_id ||
2295 ' Dataset :: ' || int_rec.dataset_code ||
2297
2298 END LOOP;
2299
2300 FOR i IN 1..gs_table_name_tab.COUNT LOOP
2301 IF gs_valid_rows(i) > 0.0 THEN
2302 IF g_loader_type = 'CLIENT' THEN
2303 UPDATE fem_ld_interface_data_gt
2304 SET status = 'VALID'
2305 WHERE ledger_id = DECODE(gs_ledger_tab(i),-1,ledger_id,gs_ledger_tab(i))
2306 AND dataset_code = DECODE(gs_ds_tab(i),-1,dataset_code,gs_ds_tab(i))
2307 AND source_system_code = DECODE(gs_ss_tab(i),-1,source_system_code,gs_ss_tab(i))
2308 AND dimension_group_id = DECODE(gs_cal_grp_tab(i),-1,dimension_group_id,gs_cal_grp_tab(i))
2309 AND ds_production_valid_flag = 'Y'
2310 AND table_row = gs_table_row_tab(i)
2311 AND table_name = gs_table_name_tab(i);
2312 ELSIF g_loader_type = 'LEDGER' AND gs_valid_rows(i) > 0.0 THEN
2313 UPDATE fem_ld_interface_data_gt
2314 SET status = 'VALID'
2315 WHERE ledger_id = DECODE(gs_ledger_tab(i),-1,ledger_id,gs_ledger_tab(i))
2316 AND dimension_group_id = DECODE(gs_cal_grp_tab(i),-1,dimension_group_id,gs_cal_grp_tab(i))
2317 AND ds_production_valid_flag = 'Y'
2318 AND table_row = gs_table_row_tab(i)
2319 AND table_name = gs_table_name_tab(i);
2320 END IF;
2321
2322 IF SQL%ROWCOUNT > 0 THEN
2323 gs_valid_rows(i) := SQL%ROWCOUNT;
2324 g_evaluate_parameters := TRUE;
2325 ELSE
2326 gs_valid_rows(i) := 0.0;
2327 END IF;
2328
2329 SELECT COUNT(*)
2330 INTO l_dummy
2331 FROM fem_ld_interface_data_gt
2332 WHERE table_name = gs_table_name_tab(i)
2333 AND table_row = gs_table_row_tab(i);
2334
2335 -- The update stmt needs to be changed as there is a direct mention of
2336 -- fla.dim_attribute_numeric_member w/o querying the metadata.
2337
2338 fnd_file.put_line(FND_FILE.log, ' ==========================================================');
2339 fnd_file.put_line(FND_FILE.log, ' In Evaluate parameters ');
2340 fnd_file.put_line(FND_FILE.log, ' After update ' || gs_table_name_tab(i) || ' COUNT(*) = ' || l_dummy);
2341 fnd_file.put_line(FND_FILE.log, ' After update ' || gs_table_name_tab(i) || ' Valid = ' || gs_valid_rows(i));
2342 fnd_file.put_line(FND_FILE.log, ' ==========================================================');
2343
2344 fem_engines_pkg.tech_message (p_severity => g_log_level_1
2345 ,p_module => g_block||'.evaluate_parameters(1.1)'
2346 ,p_msg_text => ' TABLE :: ' || gs_table_name_tab(i) ||
2347 ' ROW :: ' || gs_table_row_tab(i) ||
2348 ' COUNT :: ' || l_dummy ||
2349 ' VALID :: ' || gs_valid_rows(i));
2350
2351
2352 ELSE
2353 fnd_file.put_line(FND_FILE.log, ' ==========================================================');
2354 fnd_file.put_line(FND_FILE.log, ' In Evaluate parameters ');
2355 fnd_file.put_line(FND_FILE.log, ' No valid rows exists for ' || gs_table_name_tab(i));
2356 fnd_file.put_line(FND_FILE.log, ' ==========================================================');
2357
2358 fem_engines_pkg.tech_message (p_severity => g_log_level_1
2359 ,p_module => g_block||'.evaluate_parameters(1.1)'
2360 ,p_msg_text => ' No valid rows exists for ' || gs_table_name_tab(i));
2361
2362 END IF; -- gs_valid_rows(i) > 0.0
2363
2364
2365 END LOOP;
2366
2367 FOR int_rec IN (SELECT ledger_id, dataset_code, ds_production_valid_flag, table_name, table_row, status
2368 FROM fem_ld_interface_data_gt
2369 ORDER BY table_row, table_name)
2370 LOOP
2371 fnd_file.put_line(FND_FILE.log, ' ==========================================================');
2372 fnd_file.put_line(FND_FILE.log, ' In Evaluate parameters(2) ');
2373 fnd_file.put_line(FND_FILE.log, ' Table Name :: ' || int_rec.table_name);
2374 fnd_file.put_line(FND_FILE.log, ' Table Row :: ' || int_rec.table_row);
2375 fnd_file.put_line(FND_FILE.log, ' Ledger :: ' || int_rec.ledger_id);
2376 fnd_file.put_line(FND_FILE.log, ' Dataset :: ' || int_rec.dataset_code);
2377 fnd_file.put_line(FND_FILE.log, ' ds valid :: ' || int_rec.ds_production_valid_flag);
2378 fnd_file.put_line(FND_FILE.log, ' Status :: ' || int_rec.status);
2379 fnd_file.put_line(FND_FILE.log, ' ==========================================================');
2380
2384 ' Table Row :: ' || int_rec.table_row ||
2381 fem_engines_pkg.tech_message (p_severity => g_log_level_1
2382 ,p_module => g_block||'.evaluate_parameters(2)'
2383 ,p_msg_text => ' Table Name :: ' || int_rec.table_name ||
2385 ' Ledger :: ' || int_rec.ledger_id ||
2386 ' Dataset :: ' || int_rec.dataset_code ||
2387 ' ds valid :: ' || int_rec.ds_production_valid_flag ||
2388 ' Status :: ' || int_rec.status);
2389
2390 END LOOP;
2391
2392 -- The deleted records will be used to print the exception report
2393
2394 DELETE fem_ld_interface_data_gt
2395 WHERE status <> 'VALID'
2396 RETURNING ledger_display_code,
2397 dataset_display_code,
2398 source_system_display_code,
2399 ds_production_valid_flag,
2400 table_name,
2401 table_row
2402 BULK COLLECT INTO g_inv_ledger,
2403 g_inv_dataset,
2404 g_inv_source_system,
2405 g_inv_ds_pd_flag,
2406 g_inv_table_name,
2407 g_inv_table_row;
2408
2409 IF g_loader_type = 'LEDGER' THEN
2410
2411 SELECT COUNT(*)
2412 INTO l_dummy
2413 FROM fem_ld_interface_data_gt
2414 WHERE budget_display_code IS NOT NULL;
2415
2416 IF l_dummy > 0.0 THEN
2417 UPDATE fem_ld_interface_data_gt idt
2418 SET budget_id = (SELECT budget_id
2419 FROM fem_budgets_b fdb
2420 WHERE idt.budget_display_code = fdb.budget_display_code
2421 AND personal_flag = 'N'
2422 AND enabled_flag = 'Y')
2423 WHERE budget_display_code IS NOT NULL;
2424 END IF;
2425
2426 SELECT COUNT(*)
2427 INTO l_dummy
2428 FROM fem_ld_interface_data_gt
2429 WHERE encumbrance_type_code IS NOT NULL;
2430
2431 IF l_dummy > 0.0 THEN
2432 UPDATE fem_ld_interface_data_gt idt
2433 SET encumbrance_type_id = (SELECT encumbrance_type_id
2434 FROM fem_encumbrance_types_b fetb
2435 WHERE fetb.enabled_flag = 'Y'
2436 AND fetb.personal_flag = 'N'
2437 AND idt.encumbrance_type_code = fetb.encumbrance_type_code)
2438 WHERE encumbrance_type_code IS NOT NULL;
2439 END IF;
2440
2441 END IF; -- g_loader_type = 'LEDGER' AND g_loader_run
2442
2443 UPDATE fem_ld_interface_data_gt idt
2444 SET calendar_id = (SELECT calendar_id
2445 FROM fem_hierarchies fh,
2446 fem_object_definition_b fodb,
2447 fem_ledgers_attr fla
2448 WHERE fh.hierarchy_obj_id = fodb.object_id
2449 AND fodb.object_definition_id = fla.dim_attribute_numeric_member
2450 AND fla.ledger_id = idt.ledger_id
2451 AND fla.attribute_id = g_cal_period_hier_attr
2452 AND fh.dimension_id = g_cal_period_dim_id);
2453
2454 UPDATE fem_ld_interface_data_gt idt
2455 SET cal_period = TO_CHAR(idt.cal_period_end_date,'J') ||
2456 LPAD(TO_CHAR(idt.cal_period_number),15,'0') ||
2457 LPAD(TO_CHAR(idt.calendar_id),5,'0') ||
2458 LPAD(TO_CHAR(idt.time_dimension_group_key),5,'0');
2459
2460 FOR z IN 1..g_inv_table_name.COUNT LOOP
2461 fnd_file.put_line(FND_FILE.log, ' ==========================================================');
2462 fnd_file.put_line(FND_FILE.log, ' In Evaluate parameters(Invalid section) ');
2463 fnd_file.put_line(FND_FILE.log, ' Table Name :: ' || g_inv_table_name(z));
2464 fnd_file.put_line(FND_FILE.log, ' Table Row :: ' || g_inv_table_row(z));
2465 fnd_file.put_line(FND_FILE.log, ' Ledger :: ' || g_inv_ledger(z));
2466 fnd_file.put_line(FND_FILE.log, ' Dataset :: ' || g_inv_dataset(z));
2467 fnd_file.put_line(FND_FILE.log, ' Production :: ' || g_inv_ds_pd_flag(z));
2468 fnd_file.put_line(FND_FILE.log, ' ==========================================================');
2469
2470 fem_engines_pkg.tech_message (p_severity => g_log_level_1
2471 ,p_module => g_block||'.evaluate_parameters(Invalid section)'
2472 ,p_msg_text => ' Table Name :: ' || g_inv_table_name(z) ||
2473 ' Table Row :: ' || g_inv_table_row(z) ||
2474 ' Ledger :: ' || g_inv_ledger(z) ||
2475 ' Dataset :: ' || g_inv_dataset(z) ||
2476 ' Production :: ' || g_inv_ds_pd_flag(z));
2477
2478 END LOOP;
2479
2480 END IF; -- g_loader_run (second occurence)
2481
2485
2482 fem_engines_pkg.tech_message (p_severity => g_log_level_2
2483 ,p_module => g_block||'.evaluate_parameters(PROCEDURE)'
2484 ,p_msg_text => 'END evaluate_parameters');
2486
2487 EXCEPTION
2488 WHEN OTHERS THEN
2489 fem_engines_pkg.tech_message (p_severity => g_log_level_6
2490 ,p_module => g_block||'.evaluate_parameters(PROCEDURE)'
2491 ,p_msg_text => 'EXCEPTION in evaluate_parameters ' || sqlerrm);
2492 fnd_file.put_line(fnd_file.log, 'Exception - evaluate_params ' || sqlerrm);
2493 RAISE;
2494
2495 END evaluate_parameters;
2496
2497 ---------------------------
2498 -- END evaluate_parameters
2499 ---------------------------
2500
2501 --------------------------------------------------------------------------------
2502 --
2503 -- This is second core procedure. This queries all the VALID cal_periods to be
2504 -- used while issuing the DATA/LEDGER load CP
2505 --
2506 -- Approach is as follows ..
2507 --
2508 -- 1. Operates only on the table and table row whose gs_valid_rows > 0
2509 -- 2. Has 4 loops to handle
2510 -- a. LEDGER = ALL, LEVEL = ALL
2511 -- b. LEDGER = ALL, LEVEL = specific
2512 -- c. LEDGER = specific, LEVEL = specific
2513 -- d. LEDGER = specific, LEVEL = ALL
2514 -- 3. If there are no records that match the ledger/level combination the INSERT
2515 -- gs_valid_rows is updated to -3.0 for use in reporting
2516 -- 4. Finally, if there are no records in fem_ld_cal_periods_gt there is no point in
2517 -- proceeding. Signals the g_evaluate_parameters to FALSE
2518 --
2519 --------------------------------------------------------------------------------
2520
2521
2522 PROCEDURE populate_cal_periods IS
2523 l_dummy NUMBER;
2524 l_dummy1 NUMBER;
2525
2526 BEGIN
2527
2528 fem_engines_pkg.tech_message (p_severity => g_log_level_2
2529 ,p_module => g_block||'.populate_cal_periods(PROCEDURE)'
2530 ,p_msg_text => 'BEGIN..for populate_cal_periods');
2531
2532 FOR i IN 1..gs_ledger_tab.COUNT LOOP
2533
2534 IF gs_ledger_tab(i) <> - 1 AND gs_cal_grp_tab(i) <> -1 AND gs_valid_rows(i) > 0.0 THEN
2535
2536 INSERT INTO fem_ld_cal_periods_gt(table_name, table_row, ledger_id, cal_period_id, dim_grp_id, valid)
2537 SELECT gs_table_name_tab(i), gs_table_row_tab(i), gs_ledger_tab(i), fcpb.cal_period_id,
2538 fcpb.dimension_group_id, 'VALID'
2539 FROM fem_calendars_b fcb,
2540 fem_dimension_grps_b fdgb,
2541 fem_cal_periods_b fcpb
2542 WHERE fcpb.calendar_id = fcb.calendar_id
2543 AND fcpb.dimension_group_id = fdgb.dimension_group_id
2544 AND EXISTS ( SELECT 1
2545 FROM fem_ld_interface_data_gt idt
2546 WHERE fdgb.dimension_group_id = idt.dimension_group_id
2547 AND idt.ledger_id = gs_ledger_tab(i)
2548 AND idt.dimension_group_id = gs_cal_grp_tab(i)
2549 AND fcb.calendar_id = idt.calendar_id
2550 AND fcpb.cal_period_id = idt.cal_period
2551 AND table_name = gs_table_name_tab(i)
2552 AND table_row = gs_table_row_tab(i) )
2553 AND EXISTS ( SELECT 1
2554 FROM fem_cal_periods_attr a,
2555 fem_cal_periods_attr b
2556 WHERE a.attribute_id = g_start_date_attr
2557 AND b.attribute_id = g_end_date_attr
2558 AND a.cal_period_id = fcpb.cal_period_id
2559 AND a.cal_period_id = b.cal_period_id
2560 AND fnd_date.date_to_canonical(a.date_assign_value) >= g_start_date
2561 AND fnd_date.date_to_canonical(b.date_assign_value) <= g_end_date );
2562
2563 ELSIF gs_ledger_tab(i) <> - 1 AND gs_cal_grp_tab(i) = -1 AND gs_valid_rows(i) > 0.0 THEN
2564
2565 INSERT INTO fem_ld_cal_periods_gt(table_name, table_row, ledger_id, cal_period_id, dim_grp_id, valid)
2566 SELECT gs_table_name_tab(i), gs_table_row_tab(i), gs_ledger_tab(i), fcpb.cal_period_id,
2567 fcpb.dimension_group_id, 'VALID'
2568 FROM fem_calendars_b fcb,
2569 fem_dimension_grps_b fdgb,
2570 fem_cal_periods_b fcpb
2571 WHERE fcpb.calendar_id = fcb.calendar_id
2572 AND fcpb.dimension_group_id = fdgb.dimension_group_id
2573 AND EXISTS ( SELECT 1
2574 FROM fem_ld_interface_data_gt idt
2575 WHERE fdgb.dimension_group_id = idt.dimension_group_id
2576 AND idt.ledger_id = gs_ledger_tab(i)
2577 AND fcb.calendar_id = idt.calendar_id
2578 AND fcpb.cal_period_id = idt.cal_period
2579 AND table_name = gs_table_name_tab(i)
2580 AND table_row = gs_table_row_tab(i) )
2581 AND EXISTS ( SELECT 1
2582 FROM fem_cal_periods_attr a,
2583 fem_cal_periods_attr b
2584 WHERE a.attribute_id = g_start_date_attr
2588 AND fnd_date.date_to_canonical(a.date_assign_value) >= g_start_date
2585 AND b.attribute_id = g_end_date_attr
2586 AND a.cal_period_id = fcpb.cal_period_id
2587 AND a.cal_period_id = b.cal_period_id
2589 AND fnd_date.date_to_canonical(b.date_assign_value) <= g_end_date );
2590
2591 ELSIF gs_ledger_tab(i) = - 1 AND gs_cal_grp_tab(i) = -1 AND gs_valid_rows(i) > 0.0 THEN
2592
2593 INSERT INTO fem_ld_cal_periods_gt(table_name, table_row, ledger_id, cal_period_id, dim_grp_id, valid)
2594 SELECT gs_table_name_tab(i), gs_table_row_tab(i), idt.ledger_id, fcpb.cal_period_id,
2595 fcpb.dimension_group_id, 'VALID'
2596 FROM fem_calendars_b fcb,
2597 fem_dimension_grps_b fdgb,
2598 fem_cal_periods_b fcpb,
2599 (SELECT DISTINCT ledger_id,
2600 dimension_group_id,
2601 calendar_id,
2602 cal_period
2603 FROM fem_ld_interface_data_gt
2604 WHERE table_name = gs_table_name_tab(i)
2605 AND table_row = gs_table_row_tab(i)) idt
2606 WHERE fcpb.calendar_id = fcb.calendar_id
2607 AND fcpb.dimension_group_id = fdgb.dimension_group_id
2608 AND fdgb.dimension_group_id = idt.dimension_group_id
2609 AND fcb.calendar_id = idt.calendar_id
2610 AND fcpb.cal_period_id = idt.cal_period
2611 AND EXISTS ( SELECT 1
2612 FROM fem_cal_periods_attr a,
2613 fem_cal_periods_attr b
2614 WHERE a.attribute_id = g_start_date_attr
2615 AND b.attribute_id = g_end_date_attr
2616 AND a.cal_period_id = fcpb.cal_period_id
2617 AND a.cal_period_id = b.cal_period_id
2618 AND fnd_date.date_to_canonical(a.date_assign_value) >= g_start_date
2619 AND fnd_date.date_to_canonical(b.date_assign_value) <= g_end_date );
2620
2621 ELSIF gs_ledger_tab(i) = - 1 AND gs_cal_grp_tab(i) <> -1 AND gs_valid_rows(i) > 0.0 THEN
2622
2623 INSERT INTO fem_ld_cal_periods_gt(table_name, table_row, ledger_id, cal_period_id, dim_grp_id, valid)
2624 SELECT gs_table_name_tab(i), gs_table_row_tab(i), idt.ledger_id, fcpb.cal_period_id,
2625 fcpb.dimension_group_id, 'VALID'
2626 FROM fem_calendars_b fcb,
2627 fem_dimension_grps_b fdgb,
2628 fem_cal_periods_b fcpb,
2629 (SELECT DISTINCT ledger_id,
2630 calendar_id,
2631 cal_period
2632 FROM fem_ld_interface_data_gt
2633 WHERE table_name = gs_table_name_tab(i)
2634 AND table_row = gs_table_row_tab(i)
2635 AND dimension_group_id = gs_cal_grp_tab(i)) idt
2636 WHERE fcpb.calendar_id = fcb.calendar_id
2637 AND fcpb.dimension_group_id = fdgb.dimension_group_id
2638 AND fdgb.dimension_group_id = gs_cal_grp_tab(i)
2639 AND fcb.calendar_id = idt.calendar_id
2640 AND fcpb.cal_period_id = idt.cal_period
2641 AND EXISTS ( SELECT 1
2642 FROM fem_cal_periods_attr a,
2643 fem_cal_periods_attr b
2644 WHERE a.attribute_id = g_start_date_attr
2645 AND b.attribute_id = g_end_date_attr
2646 AND a.cal_period_id = fcpb.cal_period_id
2647 AND a.cal_period_id = b.cal_period_id
2648 AND fnd_date.date_to_canonical(a.date_assign_value) >= g_start_date
2649 AND fnd_date.date_to_canonical(b.date_assign_value) <= g_end_date );
2650
2651 END IF;
2652
2653 IF SQL%ROWCOUNT = 0.0 THEN
2654 gs_valid_rows(i) := -3.0;
2655 END IF;
2656
2657 END LOOP;
2658
2659 FOR cal_rec IN (SELECT ledger_id, table_name, table_row, cal_period_id, valid
2660 FROM fem_ld_cal_periods_gt)
2661 LOOP
2662 fnd_file.put_line(FND_FILE.log, ' ==========================================================');
2663 fnd_file.put_line(FND_FILE.log, ' In populate cal periods ');
2664 fnd_file.put_line(FND_FILE.log, ' Ledger :: ' || cal_rec.ledger_id);
2665 fnd_file.put_line(FND_FILE.log, ' Cal Period :: ' || cal_rec.cal_period_id);
2666 fnd_file.put_line(FND_FILE.log, ' Table Name :: ' || cal_rec.table_name);
2667 fnd_file.put_line(FND_FILE.log, ' Table Row :: ' || cal_rec.table_row);
2668 fnd_file.put_line(FND_FILE.log, ' Valid :: ' || cal_rec.valid);
2669 fnd_file.put_line(FND_FILE.log, ' ==========================================================');
2670
2671 fem_engines_pkg.tech_message (p_severity => g_log_level_1
2672 ,p_module => g_block||'.populate_cal_periods'
2673 ,p_msg_text => ' Table Name :: ' || cal_rec.table_name ||
2674 ' Table Row :: ' || cal_rec.table_row ||
2678 END LOOP;
2675 ' Ledger :: ' || cal_rec.ledger_id ||
2676 ' Cal Period :: ' || cal_rec.cal_period_id ||
2677 ' Valid :: ' || cal_rec.valid);
2679
2680 SELECT COUNT(1)
2681 INTO l_dummy1
2682 FROM fem_ld_cal_periods_gt
2683 WHERE ROWNUM = 1;
2684
2685 IF l_dummy1 = 0.0 THEN
2686 g_evaluate_parameters := FALSE;
2687 fem_engines_pkg.tech_message (p_severity => g_log_level_1
2688 ,p_module => g_block||'.populate_cal_periods'
2689 ,p_msg_text => ' No valid CAL PERIODS');
2690 fnd_file.put_line(FND_FILE.log, ' No valid cal Periods');
2691 ELSE
2692 fem_engines_pkg.tech_message (p_severity => g_log_level_1
2693 ,p_module => g_block||'.populate_cal_periods'
2694 ,p_msg_text => 'Some valid CAL PERIODS exist');
2695 END IF; -- l_dummy = 0.0
2696
2697 fem_engines_pkg.tech_message (p_severity => g_log_level_2
2698 ,p_module => g_block||'.populate_cal_periods(PROCEDURE)'
2699 ,p_msg_text => 'END populate_cal_periods');
2700
2701
2702 EXCEPTION
2703 WHEN OTHERS THEN
2704 fem_engines_pkg.tech_message (p_severity => g_log_level_6
2705 ,p_module => g_block||'.populate_cal_periods(PROCEDURE)'
2706 ,p_msg_text => 'EXCEPTION in populate_cal_periods ' || sqlerrm);
2707 fnd_file.put_line(fnd_file.log, 'Exception - populate_cal_periods ' || sqlerrm);
2708 RAISE;
2709
2710 END populate_cal_periods;
2711
2712 ---------------------------
2713 -- END populate_cal_periods
2714 ---------------------------
2715
2716 --------------------------------------------------------------------------------
2717 --
2718 -- This populates the g_master_rec used for submitting the LEDGER load CP
2719 --
2720 --------------------------------------------------------------------------------
2721
2722
2723 PROCEDURE populate_master_table_lldr IS
2724 indx PLS_INTEGER;
2725 BEGIN
2726
2727 fem_engines_pkg.tech_message (p_severity => g_log_level_2
2728 ,p_module => g_block||'.populate_master_table_lldr(PROCEDURE)'
2729 ,p_msg_text => 'BEGIN..for populate_master_table_lldr');
2730
2731
2732 indx := 1.0;
2733
2734 FOR i IN 1..gs_valid_rows.COUNT LOOP
2735 IF gs_valid_rows(i) > 0.0 THEN
2736
2737 BEGIN
2738
2739 SELECT DISTINCT idt.ledger_id, cal_period_id, dataset_code,
2740 budget_id, encumbrance_type_id,
2741 idt.ledger_display_code, idt.dataset_display_code,
2742 idt.encumbrance_type_code, idt.budget_display_code
2743 BULK COLLECT INTO g_ledger_id, g_cal_period_id, g_ds_code,
2744 g_budget_id, g_enc_type_id,
2745 gs_ledger_code_tab, gs_ds_code_tab, gs_budget_code_tab,
2746 gs_enc_code_tab
2747 FROM fem_ld_interface_data_gt idt,
2748 fem_ld_cal_periods_gt cpt
2749 WHERE idt.table_name = gs_table_name_tab(i)
2750 AND idt.table_row = gs_table_row_tab(i)
2751 AND idt.table_name = cpt.table_name
2752 AND idt.table_row = cpt.table_row
2753 AND idt.ledger_id = cpt.ledger_id;
2754
2755 EXCEPTION
2756 WHEN OTHERS THEN
2757 gs_valid_rows(i) := 0.0;
2758 END;
2759
2760 IF g_ledger_id.COUNT > 0.0 THEN
2761
2762 FOR master_rec_rows IN 1..g_ledger_id.COUNT LOOP
2763 g_master_rec(indx).table_name := gs_table_name_tab(i);
2764 g_master_rec(indx).table_row := gs_table_row_tab(i);
2765 g_master_rec(indx).request_id := 0.0;
2766 g_master_rec(indx).status := 'N';
2767 g_master_rec(indx).ledger_id := g_ledger_id(master_rec_rows);
2768 g_master_rec(indx).cal_period_id := g_cal_period_id(master_rec_rows);
2769 g_master_rec(indx).dataset_code := g_ds_code(master_rec_rows);
2770 g_master_rec(indx).budget_id := g_budget_id(master_rec_rows);
2771 g_master_rec(indx).enc_type_id := g_enc_type_id(master_rec_rows);
2772 g_master_rec(indx).ledger_display_code := gs_ledger_code_tab(master_rec_rows);
2773 g_master_rec(indx).dataset_display_code := gs_ds_code_tab(master_rec_rows);
2774 g_master_rec(indx).budget_display_code := gs_budget_code_tab(master_rec_rows);
2775 g_master_rec(indx).enc_type_code := gs_enc_code_tab(master_rec_rows);
2776
2777 indx := indx + 1.0;
2778 END LOOP; -- master_rec_rows
2779
2780 END IF; -- g_ledger_id.COUNT > 0.0
2781
2782 END IF; -- gs_valid_rows(i) = 0
2783
2784 END LOOP; -- gs_valid_rows.COUNT
2785
2786 FOR j IN 1..g_master_rec.COUNT LOOP
2787
2788 fnd_file.put_line(fnd_file.log, '=================================================================');
2792 fnd_file.put_line(fnd_file.log, 'Ledger ID :: ' || g_master_rec(j).ledger_id);
2789 fnd_file.put_line(fnd_file.log, '===================== MASTER INDEX TABLE ========================');
2790 fnd_file.put_line(fnd_file.log, 'Table Name :: ' || g_master_rec(j).table_name);
2791 fnd_file.put_line(fnd_file.log, 'Table Row :: ' || g_master_rec(j).table_row);
2793 fnd_file.put_line(fnd_file.log, 'Cal Period :: ' || g_master_rec(j).cal_period_id);
2794 fnd_file.put_line(fnd_file.log, 'Dataset Code :: ' || g_master_rec(j).dataset_code);
2795 fnd_file.put_line(fnd_file.log, 'Budget ID :: ' || g_master_rec(j).budget_id);
2796 fnd_file.put_line(fnd_file.log, 'Enc Type ID :: ' || g_master_rec(j).enc_type_id);
2797 fnd_file.put_line(fnd_file.log, '=================================================================');
2798
2799 fem_engines_pkg.tech_message (p_severity => g_log_level_1
2800 ,p_module => g_block||'.populate_master_table_lldr(MASTER INDEX TABLE)'
2801 ,p_msg_text => ' Table Name :: ' || g_master_rec(j).table_name ||
2802 ' Table Row :: ' || g_master_rec(j).table_row ||
2803 ' Ledger ID :: ' || g_master_rec(j).ledger_id ||
2804 ' Cal Period :: ' || g_master_rec(j).cal_period_id ||
2805 ' Dataset Code :: ' || g_master_rec(j).dataset_code ||
2806 ' Budget ID :: ' || g_master_rec(j).budget_id ||
2807 ' Enc Type ID :: ' || g_master_rec(j).enc_type_id);
2808
2809 END LOOP;
2810
2811 fem_engines_pkg.tech_message (p_severity => g_log_level_2
2812 ,p_module => g_block||'.populate_master_table_lldr(PROCEDURE)'
2813 ,p_msg_text => 'END populate_master_table_lldr');
2814
2815
2816 EXCEPTION
2817 WHEN OTHERS THEN
2818 fem_engines_pkg.tech_message (p_severity => g_log_level_6
2819 ,p_module => g_block||'.populate_master_table_lldr(PROCEDURE)'
2820 ,p_msg_text => 'EXCEPTION in populate_master_table_lldr ' || sqlerrm);
2821 fnd_file.put_line(fnd_file.log, 'Exception - populate_master_table_lldr ' || sqlerrm);
2822 RAISE;
2823
2824 END populate_master_table_lldr;
2825
2826 ---------------------------------
2827 -- END populate_master_table_lldr
2828 ---------------------------------
2829
2830 --------------------------------------------------------------------------------
2831 --
2832 -- This populates the g_master_rec used for submitting the DATA load CP
2833 --
2834 --------------------------------------------------------------------------------
2835
2836 PROCEDURE populate_master_table_dldr IS
2837 indx PLS_INTEGER;
2838 BEGIN
2839
2840 fem_engines_pkg.tech_message (p_severity => g_log_level_2
2841 ,p_module => g_block||'.populate_master_table_dldr(PROCEDURE)'
2842 ,p_msg_text => 'BEGIN..for populate_master_table_dldr');
2843
2844 indx := 1.0;
2845
2846 FOR i IN 1..gs_valid_rows.COUNT LOOP
2847 IF gs_valid_rows(i) > 0.0 THEN
2848
2849 BEGIN
2850
2851 SELECT DISTINCT idt.ledger_id, cal_period_id, source_system_code, dataset_code,
2852 idt.ledger_display_code, idt.dataset_display_code,
2853 idt.source_system_display_code
2854 BULK COLLECT INTO g_ledger_id, g_cal_period_id, g_ss_code, g_ds_code,
2855 gs_ledger_code_tab, gs_ds_code_tab, gs_ss_code_tab
2856 FROM fem_ld_interface_data_gt idt,
2857 fem_ld_cal_periods_gt cpt
2858 WHERE idt.table_name = gs_table_name_tab(i)
2859 AND idt.table_row = gs_table_row_tab(i)
2860 AND idt.table_name = cpt.table_name
2861 AND idt.table_row = cpt.table_row
2862 AND idt.ledger_id = cpt.ledger_id;
2863
2864 EXCEPTION
2865 WHEN OTHERS THEN
2866 gs_valid_rows(i) := 0.0;
2867 END;
2868
2869 IF g_ledger_id.COUNT > 0.0 THEN
2870
2871 FOR master_rec_rows IN 1..g_ledger_id.COUNT LOOP
2872 g_master_rec(indx).table_name := gs_table_name_tab(i);
2873 g_master_rec(indx).table_row := gs_table_row_tab(i);
2874 g_master_rec(indx).ledger_id := g_ledger_id(master_rec_rows);
2875 g_master_rec(indx).cal_period_id := g_cal_period_id(master_rec_rows);
2876 g_master_rec(indx).dataset_code := g_ds_code(master_rec_rows);
2877 g_master_rec(indx).source_system_code := g_ss_code(master_rec_rows);
2878 g_master_rec(indx).request_id := 0;
2879 g_master_rec(indx).status := 'N';
2880 g_master_rec(indx).ledger_display_code := gs_ledger_code_tab(master_rec_rows);
2881 g_master_rec(indx).dataset_display_code := gs_ds_code_tab(master_rec_rows);
2882 g_master_rec(indx).source_system_display_code := gs_ss_code_tab(master_rec_rows);
2883
2884 indx := indx + 1.0;
2885
2886 END LOOP; -- l_cal_period_rows
2887
2891
2888 END IF; -- g_ledger_id.COUNT > 0.0
2889
2890 END IF; -- gs_valid_rows(i) = 0
2892 END LOOP; -- gs_valid_rows.COUNT
2893
2894 FOR j IN 1..g_master_rec.COUNT LOOP
2895
2896 fnd_file.put_line(fnd_file.log, '=================================================================');
2897 fnd_file.put_line(fnd_file.log, '===================== MASTER INDEX TABLE ========================');
2898 fnd_file.put_line(fnd_file.log, 'Table Name :: ' || g_master_rec(j).table_name);
2899 fnd_file.put_line(fnd_file.log, 'Table Row :: ' || g_master_rec(j).table_row);
2900 fnd_file.put_line(fnd_file.log, 'Ledger ID :: ' || g_master_rec(j).ledger_id);
2901 fnd_file.put_line(fnd_file.log, 'Cal Period :: ' || g_master_rec(j).cal_period_id);
2902 fnd_file.put_line(fnd_file.log, 'Dataset Code :: ' || g_master_rec(j).dataset_code);
2903 fnd_file.put_line(fnd_file.log, 'Source System :: ' || g_master_rec(j).source_system_code);
2904 fnd_file.put_line(fnd_file.log, '=================================================================');
2905
2906 fem_engines_pkg.tech_message (p_severity => g_log_level_1
2907 ,p_module => g_block||'.populate_master_table_dldr(MASTER INDEX TABLE)'
2908 ,p_msg_text => ' Table Name :: ' || g_master_rec(j).table_name ||
2909 ' Table Row :: ' || g_master_rec(j).table_row ||
2910 ' Ledger ID :: ' || g_master_rec(j).ledger_id ||
2911 ' Cal Period :: ' || g_master_rec(j).cal_period_id ||
2912 ' Dataset Code :: ' || g_master_rec(j).dataset_code ||
2913 ' Source System :: ' || g_master_rec(j).source_system_code);
2914
2915 END LOOP;
2916
2917 fem_engines_pkg.tech_message (p_severity => g_log_level_2
2918 ,p_module => g_block||'.populate_master_table_dldr(PROCEDURE)'
2919 ,p_msg_text => 'END populate_master_table_dldr');
2920
2921
2922 EXCEPTION
2923 WHEN OTHERS THEN
2924 fem_engines_pkg.tech_message (p_severity => g_log_level_6
2925 ,p_module => g_block||'.populate_master_table_dldr(PROCEDURE)'
2926 ,p_msg_text => 'EXCEPTION in populate_master_table_dldr ' || sqlerrm);
2927 fnd_file.put_line(fnd_file.log, 'Exception - populate_master_table_dldr ' || sqlerrm);
2928 RAISE;
2929
2930 END populate_master_table_dldr;
2931
2932 ---------------------------------
2933 -- END populate_master_table_dldr
2934 ---------------------------------
2935
2936 --------------------------------------------------------------------------------
2937 --
2938 -- This procedure is used for printing the outcome of dimension loader CP
2939 --
2940 --------------------------------------------------------------------------------
2941
2942
2943 PROCEDURE log_dimensions(p_table_name IN VARCHAR2) IS
2944 l_status VARCHAR2(200);
2945 l_phase VARCHAR2(200);
2946 l_request_id NUMBER;
2947 l_table_name VARCHAR2(30);
2948 BEGIN
2949
2950 fem_engines_pkg.tech_message (p_severity => g_log_level_2
2951 ,p_module => g_block||'.log_dimensions(PROCEDURE)'
2952 ,p_msg_text => 'BEGIN..for log_dimensions(' || p_table_name || ')');
2953
2954 FOR dim_rec IN ( SELECT dimension_id,
2955 dimension_varchar_label,
2956 table_name,
2957 request_id,
2958 status
2959 FROM fem_ld_dim_requests_gt drt
2960 WHERE table_name = p_table_name )
2961 LOOP
2962 IF l_table_name IS NULL THEN
2963 l_table_name := dim_rec.table_name;
2964 fnd_message.set_name('FEM','FEM_DLLDR_LOAD_DIM_MSG');
2965 fnd_message.set_token('TABLE_NAME',l_table_name);
2966 trace('SEPARATOR');
2967 trace('MESSAGE');
2968 ELSE
2969 IF l_table_name <> dim_rec.table_name THEN
2970 l_table_name := dim_rec.table_name;
2971 fnd_message.set_name('FEM','FEM_DLLDR_LOAD_DIM_MSG');
2972 fnd_message.set_token('TABLE_NAME',l_table_name);
2973 trace('SEPARATOR');
2974 trace('MESSAGE');
2975 END IF;
2976 END IF;
2977
2978 IF dim_rec.request_id > 0 THEN
2979 IF dim_rec.status = 'Y' THEN
2980 fnd_message.set_name('FEM','FEM_DLLDR_DIMENSION_LOADED');
2981 fnd_message.set_token('REQUEST_ID',dim_rec.request_id);
2982 fnd_message.set_token('DIM_LABEL',dim_rec.dimension_varchar_label);
2983 ELSE
2984 fnd_message.set_name('FEM','FEM_DLLDR_DIMENSION_ERR');
2985 fnd_message.set_token('REQUEST_ID',dim_rec.request_id);
2986 fnd_message.set_token('DIM_LABEL',dim_rec.dimension_varchar_label);
2987 END IF;
2988 ELSIF dim_rec.request_id < 0 THEN
2989 fnd_message.set_name('FEM', 'FEM_DLLDR_DIMENSION_NO_DATA');
2990 fnd_message.set_token('DIM_LABEL',dim_rec.dimension_varchar_label);
2991 ELSE
2992 fnd_message.set_name('FEM', 'FEM_DLLDR_DIM_CONC_PGM_ERR');
2993 fnd_message.set_token('DIM_LABEL',dim_rec.dimension_varchar_label);
2994 END IF;
2995
2996 trace('MESSAGE');
3000 fem_engines_pkg.tech_message (p_severity => g_log_level_2
2997
2998 END LOOP; -- dim_rec
2999
3001 ,p_module => g_block||'.log_dimensions(PROCEDURE)'
3002 ,p_msg_text => 'END log_dimensions(' || p_table_name || ')');
3003
3004 EXCEPTION
3005 WHEN OTHERS THEN
3006 fem_engines_pkg.tech_message (p_severity => g_log_level_6
3007 ,p_module => g_block||'.log_dimensions(PROCEDURE)'
3008 ,p_msg_text => 'EXCEPTION in log_dimensions(' || p_table_name || ') - ' || sqlerrm);
3009 fnd_file.put_line(fnd_file.log, 'Exception - log_dimensions ' || sqlerrm);
3010 RAISE;
3011
3012 END log_dimensions;
3013
3014 ---------------------
3015 -- END log_dimensions
3016 ---------------------
3017
3018 --------------------------------------------------------------------------------
3019 --
3020 -- This procedure is used for printing the outcome of hierarchy loader CP
3021 --
3022 --------------------------------------------------------------------------------
3023
3024
3025 PROCEDURE log_hierarchies(p_table_name IN VARCHAR2) IS
3026 l_status VARCHAR2(200);
3027 l_phase VARCHAR2(200);
3028 l_request_id NUMBER;
3029 l_table_name VARCHAR2(30);
3030 BEGIN
3031
3032 fem_engines_pkg.tech_message (p_severity => g_log_level_2
3033 ,p_module => g_block||'.log_hierarchies(PROCEDURE)'
3034 ,p_msg_text => 'BEGIN..for log_hierarchies');
3035
3036 FOR hier_rec IN (SELECT dimension_varchar_label,
3037 hierarchy_object_name,
3038 hier_obj_def_display_name,
3039 table_name,
3040 request_id,
3041 status
3042 FROM fem_ld_hier_requests_gt
3043 WHERE table_name = p_table_name )
3044 LOOP
3045 IF l_table_name IS NULL THEN
3046 l_table_name := hier_rec.table_name;
3047 fnd_message.set_name('FEM','FEM_DLLDR_LOAD_HIER_MSG');
3048 fnd_message.set_token('TABLE_NAME',l_table_name);
3049 trace('SEPARATOR');
3050 trace('MESSAGE');
3051 ELSE
3052 IF l_table_name <> hier_rec.table_name THEN
3053 l_table_name := hier_rec.table_name;
3054 fnd_message.set_name('FEM','FEM_DLLDR_LOAD_HIER_MSG');
3055 fnd_message.set_token('TABLE_NAME',l_table_name);
3056 trace('SEPARATOR');
3057 trace('MESSAGE');
3058 END IF;
3059 END IF;
3060
3061 IF hier_rec.request_id > 0 THEN
3062 IF hier_rec.status = 'Y' THEN
3063 fnd_message.set_name('FEM','FEM_DLLDR_HIERARCHY_LOADED');
3064 fnd_message.set_token('REQUEST_ID',hier_rec.request_id);
3065 fnd_message.set_token('HIERARCHY',hier_rec.hierarchy_object_name || '.' || hier_rec.hier_obj_def_display_name);
3066 ELSE
3067 fnd_message.set_name('FEM','FEM_DLLDR_HIERARCHY_ERR');
3068 fnd_message.set_token('REQUEST_ID',hier_rec.request_id);
3069 fnd_message.set_token('HIERARCHY',hier_rec.hierarchy_object_name || '.' || hier_rec.hier_obj_def_display_name);
3070 fnd_message.set_token('DIM_LABEL',hier_rec.dimension_varchar_label);
3071 END IF;
3072 ELSE
3073 fnd_message.set_name('FEM', 'FEM_DLLDR_HIER_CONC_PGM_ERR');
3074 fnd_message.set_token('HIERARCHY',hier_rec.hierarchy_object_name || '.' || hier_rec.hier_obj_def_display_name);
3075 END IF;
3076
3077 trace('MESSAGE');
3078
3079 END LOOP;
3080
3081 fem_engines_pkg.tech_message (p_severity => g_log_level_2
3082 ,p_module => g_block||'.log_hierarchies(PROCEDURE)'
3083 ,p_msg_text => 'END log_hierarchies');
3084
3085
3086 EXCEPTION
3087 WHEN OTHERS THEN
3088 fem_engines_pkg.tech_message (p_severity => g_log_level_2
3089 ,p_module => g_block||'.log_hierarchies(PROCEDURE)'
3090 ,p_msg_text => 'EXCEPTION in log_hierarchies ' || sqlerrm);
3091 fnd_file.put_line(fnd_file.log, 'Exception - log_hierarchies ' || sqlerrm);
3092 RAISE;
3093
3094 END log_hierarchies;
3095 ----------------------
3096 -- END log_hierarchies
3097 ----------------------
3098
3099
3100 --------------------------------------------------------------------------------
3101 --
3102 -- This procedure is used for printing the outcome of DATA/LEDGER load CP
3103 --
3104 --------------------------------------------------------------------------------
3105
3106
3107 PROCEDURE log_fact_table(p_table_name IN VARCHAR2,
3108 p_table_row IN NUMBER)
3109 IS
3110 l_start NUMBER;
3111 l_end NUMBER;
3112
3113 l_msg_count NUMBER;
3114 l_exception_code VARCHAR2(50);
3115 l_msg_data VARCHAR2(200);
3116 l_return_status VARCHAR2(50);
3117
3118 e_process_single_rule_error EXCEPTION;
3119 BEGIN
3120
3124
3121 fem_engines_pkg.tech_message (p_severity => g_log_level_2
3122 ,p_module => g_block||'.log_fact_table(PROCEDURE)'
3123 ,p_msg_text => 'BEGIN..for log_fact_table(' || p_table_name || ',' || p_table_row || ')');
3125 l_start := NULL;
3126 l_end := NULL;
3127
3128 FOR j IN 1..g_master_rec.COUNT LOOP
3129 IF g_master_rec(j).table_row = p_table_row THEN
3130 IF l_start IS NULL THEN
3131 l_start := j;
3132 END IF;
3133 l_end := j;
3134 END IF;
3135
3136 EXIT WHEN g_master_rec(j).table_row <> p_table_row AND l_start IS NOT NULL;
3137 END LOOP;
3138
3139 FOR i IN l_start..l_end LOOP
3140
3141 IF g_master_rec(i).request_id > 0 THEN
3142 IF g_master_rec(i).status = 'Y' THEN
3143 IF g_loader_type = 'CLIENT' THEN
3144 fnd_message.set_name('FEM', 'FEM_DLLDR_DATA_LOADER_COMPLETE');
3145 fnd_message.set_token('REQUEST_ID', g_master_rec(i).request_id);
3146 fnd_message.set_token('LEDGER_DISPLAY_CODE', g_master_rec(i).ledger_display_code);
3147 fnd_message.set_token('DATASET_DISPLAY_CODE', g_master_rec(i).dataset_display_code);
3148 fnd_message.set_token('SOURCE_SYSTEM_CODE', g_master_rec(i).source_system_display_code);
3149 fnd_message.set_token('TABLE_NAME',p_table_name);
3150 ELSE
3151 IF g_master_rec(i).budget_id IS NULL AND g_master_rec(i).enc_type_id IS NULL THEN
3152 fnd_message.set_name('FEM', 'FEM_DLLDR_LEDGER_LOAD_COMPLETE');
3153 fnd_message.set_token('REQUEST_ID', g_master_rec(i).request_id);
3154 fnd_message.set_token('TABLE_NAME',p_table_name);
3155 fnd_message.set_token('LEDGER_DISPLAY_CODE', g_master_rec(i).ledger_display_code);
3156 fnd_message.set_token('DATASET_DISPLAY_CODE', g_master_rec(i).dataset_display_code);
3157 fnd_message.set_token('ROW',p_table_row);
3158 ELSIF g_master_rec(i).budget_id IS NOT NULL THEN
3159 fnd_message.set_name('FEM', 'FEM_DLLDR_LDGR_LOAD_COMPLETE_B');
3160 fnd_message.set_token('REQUEST_ID', g_master_rec(i).request_id);
3161 fnd_message.set_token('TABLE_NAME',p_table_name);
3162 fnd_message.set_token('LEDGER_DISPLAY_CODE', g_master_rec(i).ledger_display_code);
3163 fnd_message.set_token('DATASET_DISPLAY_CODE', g_master_rec(i).dataset_display_code);
3164 fnd_message.set_token('BUDGET_DISPLAY_CODE', g_master_rec(i).budget_display_code);
3165 fnd_message.set_token('ROW',p_table_row);
3166 ELSIF g_master_rec(i).enc_type_id IS NOT NULL THEN
3167 fnd_message.set_name('FEM', 'FEM_DLLDR_LDGR_LOAD_COMPLETE_E');
3168 fnd_message.set_token('REQUEST_ID', g_master_rec(i).request_id);
3169 fnd_message.set_token('TABLE_NAME',p_table_name);
3170 fnd_message.set_token('LEDGER_DISPLAY_CODE', g_master_rec(i).ledger_display_code);
3171 fnd_message.set_token('DATASET_DISPLAY_CODE', g_master_rec(i).dataset_display_code);
3172 fnd_message.set_token('ENC_TYPE_CODE', g_master_rec(i).enc_type_code);
3173 fnd_message.set_token('ROW',p_table_row);
3174 END IF;
3175 END IF;
3176
3177 fem_engines_pkg.tech_message (p_severity => g_log_level_1
3178 ,p_module => g_block||'.log_fact_table(PROCEDURE)'
3179 ,p_msg_text => 'Register the chain for parent request(' || g_request_id ||
3180 '), child request(' || g_master_rec(i).request_id || ')');
3181
3182 fem_pl_pkg.register_chain(p_api_version => c_api_version
3183 ,p_commit => c_false
3184 ,p_request_id => g_master_rec(i).request_id
3185 ,p_object_id => gs_table_obj_id(g_master_rec(i).table_row)
3186 ,p_source_created_by_request_id => g_request_id
3187 ,p_source_created_by_object_id => g_object_id
3188 ,p_user_id => g_user_id
3189 ,p_last_update_login => g_login_id
3190 ,x_msg_count => l_msg_count
3191 ,x_msg_data => l_msg_data
3192 ,x_return_status => l_return_status);
3193
3194 fem_engines_pkg.tech_message (p_severity => g_log_level_1
3195 ,p_module => g_block||'.log_fact_table(PROCEDURE)'
3196 ,p_msg_text => 'Completed registering the chain for parent request('
3197 || g_request_id ||
3198 '), child request(' || g_master_rec(i).request_id || ')');
3199
3200 IF l_return_status <> c_success THEN
3201 RAISE e_process_single_rule_error;
3202 END IF;
3203
3204 ELSE -- g_master_rec(i).status = 'Y'
3205
3206 IF g_loader_type = 'CLIENT' THEN
3210 fnd_message.set_token('DATASET_DISPLAY_CODE', g_master_rec(i).dataset_display_code);
3207 fnd_message.set_name('FEM', 'FEM_DLLDR_DATA_LOADER_ERR');
3208 fnd_message.set_token('REQUEST_ID', g_master_rec(i).request_id);
3209 fnd_message.set_token('LEDGER_DISPLAY_CODE', g_master_rec(i).ledger_display_code);
3211 fnd_message.set_token('SOURCE_SYSTEM_CODE', g_master_rec(i).source_system_display_code);
3212 fnd_message.set_token('TABLE_NAME',p_table_name);
3213 ELSE
3214 fnd_message.set_name('FEM', 'FEM_DLLDR_LEDGER_LOADER_ERR');
3215 fnd_message.set_token('REQUEST_ID', g_master_rec(i).request_id);
3216 fnd_message.set_token('LEDGER_DISPLAY_CODE', g_master_rec(i).ledger_display_code);
3217 fnd_message.set_token('TABLE_NAME',p_table_name);
3218 fnd_message.set_token('DATASET_DISPLAY_CODE', g_master_rec(i).dataset_display_code);
3219 fnd_message.set_token('ROW',p_table_row);
3220 END IF;
3221
3222 END IF; -- g_master_rec(i).status = 'Y'
3223
3224 ELSE -- g_master_rec(i).request_id > 0
3225 fnd_message.set_name('FEM', 'FEM_DLLDR_DATA_CONC_PGM_ERR');
3226 fnd_message.set_token('TABLE_NAME',p_table_name);
3227 END IF; -- g_master_rec(i).request_id > 0
3228
3229 trace('MESSAGE');
3230
3231 END LOOP;
3232
3233 trace('SEPARATOR');
3234
3235 fem_engines_pkg.tech_message (p_severity => g_log_level_2
3236 ,p_module => g_block||'.log_fact_table(PROCEDURE)'
3237 ,p_msg_text => 'END log_fact_table(' || p_table_name || ',' || p_table_row || ')');
3238
3239 EXCEPTION
3240 WHEN e_process_single_rule_error THEN
3241 fem_engines_pkg.tech_message (
3242 p_severity => g_log_level_5
3243 ,p_module => g_block||'.log_fact_table(PROCEDURE)'
3244 ,p_msg_text => 'EXCEPTION in log_fact_table while registering the chain(' || p_table_name || ',' ||
3245 p_table_row || ')' );
3246 WHEN OTHERS THEN
3247 fem_engines_pkg.tech_message (p_severity => g_log_level_6
3248 ,p_module => g_block||'.log_fact_table(PROCEDURE)'
3249 ,p_msg_text => 'EXCEPTION in log_fact_table(' || p_table_name || ',' ||
3250 p_table_row || ') ' || sqlerrm);
3251 fnd_file.put_line(fnd_file.log, 'Exception - log_fact_table ' || sqlerrm);
3252 RAISE;
3253 END log_fact_table;
3254
3255 ---------------------
3256 -- END log_fact_table
3257 ---------------------
3258
3259
3260 --------------------------------------------------------------------------------
3261 --
3262 -- Main procedure called from process_request to log the outcome of all the CP
3263 -- This in turn branches to dimensions, hierarchies, data/ledger load
3264 --
3265 --------------------------------------------------------------------------------
3266
3267
3268 PROCEDURE populate_log IS
3269 l_status VARCHAR2(200);
3270 l_phase VARCHAR2(200);
3271 l_request_id NUMBER;
3272 l_table_name VARCHAR2(30);
3273 all_str VARCHAR2(30);
3274 BEGIN
3275
3276 fem_engines_pkg.tech_message (p_severity => g_log_level_2
3277 ,p_module => g_block||'.populate_log(PROCEDURE)'
3278 ,p_msg_text => 'BEGIN..for populate_log');
3279
3280 fnd_message.set_name('FEM', 'FEM_ALL_TXT');
3281 all_str := fnd_message.get;
3282
3283 FOR i IN 1..gs_table_name_tab.COUNT LOOP
3284 IF gs_ledger_tab(i) = -1 THEN
3285 gs_ledger_code_tab(i) := all_str;
3286 ELSE
3287 SELECT ledger_display_code
3288 INTO gs_ledger_code_tab(i)
3289 FROM fem_ledgers_b
3290 WHERE ledger_id = gs_ledger_tab(i)
3291 AND personal_flag = 'N'
3292 AND enabled_flag = 'Y';
3293 END IF;
3294
3295 IF g_loader_type = 'CLIENT' THEN
3296 IF gs_ss_tab(i) = -1 THEN
3297 gs_ss_code_tab(i) := all_str;
3298 ELSE
3299 SELECT source_system_display_code
3300 INTO gs_ss_code_tab(i)
3301 FROM fem_source_systems_b
3302 WHERE source_system_code = gs_ss_tab(i)
3303 AND personal_flag = 'N'
3304 AND enabled_flag = 'Y';
3305 END IF;
3306
3307 IF gs_ds_tab(i) = -1 THEN
3308 gs_ds_code_tab(i) := all_str;
3309 ELSE
3310 SELECT dataset_display_code
3311 INTO gs_ds_code_tab(i)
3312 FROM fem_datasets_b
3313 WHERE dataset_code = gs_ds_tab(i)
3314 AND personal_flag = 'N'
3315 AND enabled_flag = 'Y';
3316 END IF;
3317 ELSE
3318 SELECT dataset_display_code
3319 INTO gs_ds_code_tab(i)
3320 FROM fem_datasets_b
3321 WHERE dataset_code = gs_ds_tab(i)
3322 AND personal_flag = 'N'
3323 AND enabled_flag = 'Y';
3324
3325 END IF; -- g_loader_type = 'CLIENT'
3326
3327 END LOOP;
3328
3329 fem_engines_pkg.tech_message (p_severity => g_log_level_1
3333 FOR i IN 1..gs_table_name_tab.COUNT LOOP
3330 ,p_module => g_block||'.populate_log(PROCEDURE)'
3331 ,p_msg_text => 'Fetched all the display codes');
3332
3334 trace('SEPARATOR');
3335 IF g_loader_type = 'CLIENT' THEN
3336 fnd_message.set_name('FEM', 'FEM_DLLDR_LOAD_DATA_MSG');
3337 fnd_message.set_token('TABLE_NAME',gs_table_name_tab(i));
3338 fnd_message.set_token('LEDGER_DISPLAY_CODE', gs_ledger_code_tab(i));
3339 fnd_message.set_token('DATASET_DISPLAY_CODE', gs_ds_code_tab(i));
3340 fnd_message.set_token('SOURCE_SYSTEM_CODE', gs_ss_code_tab(i));
3341 ELSE
3342 fnd_message.set_name('FEM', 'FEM_DLLDR_LOAD_DATA_MSG_L');
3343 fnd_message.set_token('TABLE_NAME',gs_table_name_tab(i));
3344 fnd_message.set_token('LEDGER_DISPLAY_CODE', gs_ledger_code_tab(i));
3345 fnd_message.set_token('DATASET_DISPLAY_CODE', gs_ds_code_tab(i));
3346 END IF;
3347 trace('MESSAGE');
3348 trace('SEPARATOR');
3349 IF gs_load_opt_tab(i) = 'DD' THEN
3350 log_dimensions(gs_table_name_tab(i));
3351 trace('SEPARATOR');
3352 END IF; -- gs_load_opt_tab(i) = 'DD'
3353
3354 IF gs_load_opt_tab(i) = 'DDH' THEN
3355 log_dimensions(gs_table_name_tab(i));
3356 log_hierarchies(gs_table_name_tab(i));
3357 trace('SEPARATOR');
3358 END IF;
3359
3360 FOR j IN 1..g_inv_table_row.COUNT LOOP
3361 IF g_inv_table_row(j) = i THEN
3362 IF g_inv_ds_pd_flag(j) = 'N' THEN
3363 fnd_message.set_name('FEM', 'FEM_DLLDR_LOADER_PROD_DS');
3364 fnd_message.set_token('DATASET',g_inv_dataset(j));
3365 trace('MESSAGE');
3366 trace('SEPARATOR');
3367 END IF;
3368 END IF;
3369 END LOOP;
3370
3371 CASE gs_valid_rows(i)
3372
3373 WHEN 0 THEN
3374 IF g_loader_type = 'CLIENT' THEN
3375 fnd_message.set_name('FEM', 'FEM_DLLDR_DATA_LOADER_WARN');
3376 fnd_message.set_token('LEDGER_DISPLAY_CODE', gs_ledger_code_tab(i));
3377 fnd_message.set_token('DATASET_DISPLAY_CODE', gs_ds_code_tab(i));
3378 fnd_message.set_token('SOURCE_SYSTEM_CODE', gs_ss_code_tab(i));
3379 fnd_message.set_token('TABLE_NAME',gs_table_name_tab(i));
3380 ELSE
3381 fnd_message.set_name('FEM', 'FEM_DLLDR_LEDGER_LOADER_WARN');
3382 fnd_message.set_token('LEDGER_DISPLAY_CODE', gs_ledger_code_tab(i));
3383 fnd_message.set_token('TABLE_NAME',gs_table_name_tab(i));
3384 fnd_message.set_token('DATASET_DISPLAY_CODE', gs_ds_code_tab(i));
3385 fnd_message.set_token('ROW',gs_table_row_tab(i));
3386 END IF;
3387 trace('MESSAGE');
3388 trace('SEPARATOR');
3389 WHEN -1 THEN
3390 IF g_loader_type = 'CLIENT' THEN
3391 fnd_message.set_name('FEM', 'FEM_DLLDR_INTERFACE_NO_DATA');
3392 fnd_message.set_token('TABLE_NAME',gs_table_name_tab(i));
3393 ELSE
3394 fnd_message.set_name('FEM', 'FEM_DLLDR_INTERFACE_NO_DATA_L');
3395 fnd_message.set_token('TABLE_NAME',gs_table_name_tab(i));
3396 fnd_message.set_token('DATASET_TYPE',gs_ds_bal_code_tab(i));
3397 fnd_message.set_token('ROW',gs_table_row_tab(i));
3398 END IF;
3399 trace('MESSAGE');
3400 trace('SEPARATOR');
3401 WHEN -2 THEN
3402 fnd_message.set_name('FEM', 'FEM_DLLDR_INTERFACE_TABLE_ERR');
3403 fnd_message.set_token('TABLE_NAME',gs_table_name_tab(i));
3404 trace('MESSAGE');
3405 trace('SEPARATOR');
3406 WHEN -3 THEN
3407 IF g_loader_type = 'CLIENT' THEN
3408 fnd_message.set_name('FEM', 'FEM_DLLDR_CAL_PERIOD_ERR');
3409 fnd_message.set_token('LEDGER_DISPLAY_CODE', gs_ledger_code_tab(i));
3410 fnd_message.set_token('DATASET_DISPLAY_CODE', gs_ds_code_tab(i));
3411 fnd_message.set_token('SOURCE_SYSTEM_CODE', gs_ss_code_tab(i));
3412 fnd_message.set_token('TABLE_NAME',gs_table_name_tab(i));
3413 ELSE
3414 fnd_message.set_name('FEM', 'FEM_DLLDR_CAL_PERIOD_ERR_L');
3415 fnd_message.set_token('LEDGER_DISPLAY_CODE', gs_ledger_code_tab(i));
3416 fnd_message.set_token('DATASET_DISPLAY_CODE', gs_ds_code_tab(i));
3417 fnd_message.set_token('TABLE_NAME',gs_table_name_tab(i));
3418 fnd_message.set_token('ROW',gs_table_row_tab(i));
3419 END IF;
3420 trace('MESSAGE');
3421 trace('SEPARATOR');
3422 ELSE
3423 log_fact_table(gs_table_name_tab(i), gs_table_row_tab(i));
3424 trace('BLANKLINE');
3425 trace('SEPARATOR');
3426 END CASE; -- gs_valid_rows(i)
3427
3428 END LOOP;
3429
3430 fem_engines_pkg.tech_message (p_severity => g_log_level_2
3431 ,p_module => g_block||'.populate_log(PROCEDURE)'
3435 EXCEPTION
3432 ,p_msg_text => 'END populate_log');
3433
3434
3436 WHEN OTHERS THEN
3437 fem_engines_pkg.tech_message (p_severity => g_log_level_2
3438 ,p_module => g_block||'.populate_log(PROCEDURE)'
3439 ,p_msg_text => 'EXCEPTION in populate_log ' || sqlerrm);
3440 fnd_file.put_line(fnd_file.log, 'Exception - populate_log ' || sqlerrm);
3441 RAISE;
3442
3443 END populate_log;
3444
3445 -------------------
3446 -- END populate_log
3447 -------------------
3448
3449 BEGIN
3450
3451 fem_engines_pkg.tech_message (p_severity => g_log_level_2
3452 ,p_module => g_block||'.MAIN - start'
3453 ,p_msg_text => 'BEGIN MAIN PACKAGE SECTION');
3454
3455 g_approval_flag := FALSE;
3456 g_hierarchy_exists := FALSE;
3457 g_enc_exist := FALSE;
3458 g_budgets_exist := FALSE;
3459
3460 g_loader_run := FALSE;
3461 g_evaluate_parameters := FALSE;
3462
3463 fem_engines_pkg.tech_message (p_severity => g_log_level_2
3464 ,p_module => g_block||'.MAIN - end'
3465 ,p_msg_text => 'END MAIN PACKAGE SECTION');
3466
3467 EXCEPTION
3468 WHEN OTHERS THEN
3469 fem_engines_pkg.tech_message (p_severity => g_log_level_6
3470 ,p_module => g_block||'.MAIN'
3471 ,p_msg_text => 'EXCEPTION in MAIN PACKAGE SECTION ' || sqlerrm);
3472
3473 fnd_file.put_line(fnd_file.log, 'Exception - main ' || sqlerrm);
3474 RAISE;
3475
3476 END Fem_Data_Loader_Pkg;