[Home] [Help]
PACKAGE BODY: APPS.FEM_DIM_HIER_LOADER_PKG
Source
1 PACKAGE BODY Fem_Dim_Hier_Loader_Pkg AS
2 /* $Header: FEMDIMHIERLDR.plb 120.2 2008/02/15 18:10:31 gcheng ship $ */
3 --------------------------------------------------------------------------------
4 -- Declare all global variables --
5 --------------------------------------------------------------------------------
6
7 gs_dim_id_tab number_table;
8 gs_dim_label_tab char_table;
9 gs_table_row_tab number_table;
10 gs_hier_dim_id_tab number_table;
11 gs_hier_dim_label_tab char_table;
12 gs_hier_obj_id_tab number_table;
13 gs_hier_obj_def_id_tab number_table;
14
15 g_hier_object_def_id NUMBER;
16
17 g_trc_request_id NUMBER;
18 g_pipe_name VARCHAR2(30);
19 g_all_str VARCHAR2(30);
20
21 g_budgets_exist BOOLEAN;
22 g_enc_exist BOOLEAN;
23 g_loader_run BOOLEAN;
24
25 g_load_dimensions BOOLEAN;
26 g_load_hierarchies BOOLEAN;
27
28 --------------------------------------------------------------------------------
29 -- Declare private procedures and functions --
30 --------------------------------------------------------------------------------
31
32 PROCEDURE get_parameters(p_obj_def_id IN NUMBER);
33 PROCEDURE print_params;
34 PROCEDURE submit_dimension_loaders;
35 PROCEDURE submit_hierarchy_loaders;
36 PROCEDURE build_dim_stages;
37 PROCEDURE build_hier_stages;
38 PROCEDURE wait_for_requests(p_wait_for IN VARCHAR2);
39 PROCEDURE log_dimensions;
40 PROCEDURE log_hierarchies;
41 PROCEDURE cleanup;
42
43 --------------------------------------------------------------------------------
44 -- Public procedures and functions --
45 --------------------------------------------------------------------------------
46
47 --------------------------------------------------------------------------------
48 --
49 -- This is the main procedure that gets called when the LOADER rule is run. It
50 -- calls all the relevant procedures in a sequential manner
51 --
52 --------------------------------------------------------------------------------
53
54
55 PROCEDURE process_request(errbuf OUT NOCOPY VARCHAR2,
56 retcode OUT NOCOPY VARCHAR2,
57 p_obj_def_id IN NUMBER)
58 IS
59
60 BEGIN
61
62 fnd_log_repository.init;
63
64 fem_engines_pkg.tech_message (p_severity => g_log_level_2
65 ,p_module => g_block||'.process_request(PROCEDURE)'
66 ,p_msg_text => 'BEGIN..for process_request');
67
68 fem_engines_pkg.tech_message (p_severity => g_log_level_1
69 ,p_module => g_block||'.process_request(PARAMETERS)'
70 ,p_msg_text => 'p_obj_def_id :: ' || p_obj_def_id);
71
72 fem_engines_pkg.tech_message (p_severity => g_log_level_1
73 ,p_module => g_block||'.process_request(PROCEDURE)'
74 ,p_msg_text => 'Calling get_parameters');
75
76 get_parameters(p_obj_def_id);
77
78 fem_engines_pkg.tech_message (p_severity => g_log_level_1
79 ,p_module => g_block||'.process_request(PROCEDURE)'
80 ,p_msg_text => 'Completed get_parameters');
81
82 fnd_msg_pub.initialize;
83
84 IF g_load_dimensions THEN
85 fem_engines_pkg.tech_message (p_severity => g_log_level_1
86 ,p_module => g_block||'.process_request(PROCEDURE)'
87 ,p_msg_text => 'Calling build_dim_stages');
88
89
90 build_dim_stages;
91
92 fem_engines_pkg.tech_message (p_severity => g_log_level_1
93 ,p_module => g_block||'.process_request(PROCEDURE)'
94 ,p_msg_text => 'Completed build_dim_stages');
95
96 fem_engines_pkg.tech_message (p_severity => g_log_level_1
97 ,p_module => g_block||'.process_request(PROCEDURE)'
98 ,p_msg_text => 'Calling log_dimensions');
99
100 log_dimensions;
101
102 fem_engines_pkg.tech_message (p_severity => g_log_level_1
103 ,p_module => g_block||'.process_request(PROCEDURE)'
104 ,p_msg_text => 'Completed log_dimensions');
105
106 END IF;
107
108 IF g_load_hierarchies THEN
109 fem_engines_pkg.tech_message (p_severity => g_log_level_1
110 ,p_module => g_block||'.process_request(PROCEDURE)'
111 ,p_msg_text => 'Calling build_hier_stages');
112
113 build_hier_stages;
114
115 fem_engines_pkg.tech_message (p_severity => g_log_level_1
116 ,p_module => g_block||'.process_request(PROCEDURE)'
117 ,p_msg_text => 'Completed build_hier_stages');
118
119 fem_engines_pkg.tech_message (p_severity => g_log_level_1
120 ,p_module => g_block||'.process_request(PROCEDURE)'
121 ,p_msg_text => 'Calling log_hierarchies');
122
123
124 log_hierarchies;
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 => 'Completed log_hierarchies');
129
130 END IF;
131
132 IF NOT g_load_dimensions AND NOT g_load_hierarchies THEN
133 fnd_file.put_line(fnd_file.log, ' No data found for loading dimensions and hierarchies');
134 END IF;
135
136 print_params;
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 => 'Calling cleanup');
141
142 cleanup;
143
144 fem_engines_pkg.tech_message (p_severity => g_log_level_1
145 ,p_module => g_block||'.process_request(PROCEDURE)'
146 ,p_msg_text => 'Completed cleanup');
147
148
149 fem_engines_pkg.tech_message (p_severity => g_log_level_2
150 ,p_module => g_block||'.process_request(PROCEDURE)'
151 ,p_msg_text => 'END process_request');
152
153 EXCEPTION
154 WHEN OTHERS THEN
155 fem_engines_pkg.tech_message (p_severity => g_log_level_6
156 ,p_module => g_block||'.process_request(PROCEDURE)'
157 ,p_msg_text => 'EXCEPTION in process_request ' || sqlerrm);
158 fnd_file.put_line(fnd_file.log, 'Exception - process_request ' || sqlerrm);
159 RAISE;
160
161 END process_request;
162
163 ----------------------
164 -- END process_request
165 ----------------------
166
167 --------------------------------------------------------------------------------
168 -- Private procedures and functions --
169 --------------------------------------------------------------------------------
170
171 --------------------------------------------------------------------------------
172 --
173 -- This procedure is used to free up all the global pl/sql objects, object tables
174 -- used during the loader run
175 --
176 --------------------------------------------------------------------------------
177
178 PROCEDURE cleanup IS
179
180 BEGIN
181
182 fem_engines_pkg.tech_message (p_severity => g_log_level_2
183 ,p_module => g_block||'.cleanup(PROCEDURE)'
184 ,p_msg_text => 'BEGIN..for cleanup ');
185
186 gs_dim_id_tab.DELETE;
187 gs_table_row_tab.DELETE;
188 gs_hier_dim_id_tab.DELETE;
189 gs_hier_obj_id_tab.DELETE;
190 gs_hier_obj_def_id_tab.DELETE;
191
192 DELETE fem_ld_dim_requests_gt;
193
194 DELETE fem_ld_hier_requests_gt;
195
196 fem_engines_pkg.tech_message (p_severity => g_log_level_2
197 ,p_module => g_block||'.cleanup(PROCEDURE)'
198 ,p_msg_text => 'END cleanup ');
199
200
201 END cleanup;
202
203 --------------
204 -- END cleanup
205 --------------
206
207 --------------------------------------------------------------------------------
208 --
209 -- This procedure is used to query up the selection criteria specified by the
210 -- user in the parameters page.
211 --
212 --------------------------------------------------------------------------------
213
214
215 PROCEDURE get_parameters(p_obj_def_id IN NUMBER) IS
216 dim_loader_rule_error EXCEPTION;
217 BEGIN
218
219 fem_engines_pkg.tech_message (p_severity => g_log_level_2
220 ,p_module => g_block||'.get_parameters(PROCEDURE)'
221 ,p_msg_text => 'BEGIN..for get_parameters ');
222
223
224 BEGIN
225 SELECT fdb.dimension_id,
226 fdb.dimension_varchar_label
227 BULK COLLECT INTO gs_dim_id_tab,
228 gs_dim_label_tab
229 FROM fem_dim_load_dim_params fdldp,
230 fem_dimensions_b fdb
231 WHERE loader_obj_def_id = p_obj_def_id
232 AND fdb.dimension_id = fdldp.dimension_id;
233 EXCEPTION
234 WHEN NO_DATA_FOUND THEN
235 NULL; -- Dimension page not populated
236 WHEN OTHERS THEN
237 fem_engines_pkg.tech_message (p_severity => g_log_level_6
238 ,p_module => g_block||'.get_parameters (PROCEDURE)'
239 ,p_msg_text => 'EXCEPTION in get_parameters.loading dimensions ' || sqlerrm);
240 fnd_file.put_line(fnd_file.log, 'Error @ get parameters');
241 RAISE; -- dim_loader_rule_error;
242 END;
243
244 IF gs_dim_id_tab.COUNT > 0.0 THEN
245 g_load_dimensions := TRUE;
246 END IF;
247
248 BEGIN
249 SELECT ROWNUM,
250 fdb.dimension_id,
251 fdb.dimension_varchar_label,
252 hier_obj_id,
253 hier_obj_def_id
254 BULK COLLECT INTO gs_table_row_tab,
255 gs_hier_dim_id_tab,
256 gs_hier_dim_label_tab,
257 gs_hier_obj_id_tab,
258 gs_hier_obj_def_id_tab
259 FROM fem_dim_load_hier_params fdlhp,
260 fem_dimensions_b fdb
261 WHERE loader_obj_def_id = p_obj_def_id
262 AND fdb.dimension_id = fdlhp.dimension_id;
263 EXCEPTION
264 WHEN NO_DATA_FOUND THEN
265 NULL; -- Hierarchy page not populated
266 WHEN OTHERS THEN
267 fem_engines_pkg.tech_message (p_severity => g_log_level_6
268 ,p_module => g_block||'.get_parameters (PROCEDURE)'
269 ,p_msg_text => 'EXCEPTION in get_parameters.loading hierarchies ' || sqlerrm);
270
271 fnd_file.put_line(fnd_file.log, 'Error @ get parameters');
272 RAISE; -- dim_loader_rule_error;
273 END;
274
275 IF gs_hier_obj_id_tab.COUNT > 0.0 THEN
276 g_load_hierarchies := TRUE;
277 END IF;
278
279 fem_engines_pkg.tech_message (p_severity => g_log_level_2
280 ,p_module => g_block||'.get_parameters(PROCEDURE)'
281 ,p_msg_text => 'END get_parameters ');
282
283
284 END get_parameters;
285
286 ---------------------
287 -- END get_parameters
288 ---------------------
289
290 --------------------------------------------------------------------------------
291 --
292 -- This procedure is used to wait for set of concurrent requests to complete,
293 -- capture the request status
294 --
295 --------------------------------------------------------------------------------
296
297 PROCEDURE wait_for_requests(p_wait_for IN VARCHAR2) IS
298 l_request_id NUMBER;
299
300 l_return_status VARCHAR2(1);
301 l_msg_data VARCHAR2(4000);
302 l_msg_count NUMBER;
303
304 l_phase VARCHAR2(200);
305 l_status VARCHAR2(200);
306 l_dev_phase VARCHAR2(200);
307 l_dev_status VARCHAR2(200);
308 l_message VARCHAR2(200);
309 l_ret_code NUMBER;
310 l_err_buff VARCHAR2(1000);
311 l_compl_status BOOLEAN;
312 BEGIN
313
314 fem_engines_pkg.tech_message (p_severity => g_log_level_2
315 ,p_module => g_block||'.wait_for_requests(PROCEDURE)'
316 ,p_msg_text => 'BEGIN..for wait_for_requests ');
317
318 CASE p_wait_for
319 WHEN c_dim_loader THEN
320 FOR dim_rec IN (SELECT request_id
321 FROM fem_ld_dim_requests_gt
322 WHERE request_id > 0)
323 LOOP
324 IF fnd_concurrent.wait_for_request(request_id=> dim_rec.request_id,
325 interval => c_interval,
326 max_wait => c_max_wait_time,
327 phase => l_phase,
328 status => l_status,
329 dev_phase => l_dev_phase,
330 dev_status => l_dev_status,
331 message => l_message)
332 THEN
333 IF l_dev_phase || '.' || l_dev_status IN ('COMPLETE.NORMAL','COMPLETE.WARNING') THEN
334 UPDATE fem_ld_dim_requests_gt
335 SET status = 'Y'
336 WHERE request_id = dim_rec.request_id;
337 ELSE
338 UPDATE fem_ld_dim_requests_gt
339 SET status = 'N'
340 WHERE request_id = dim_rec.request_id;
341 l_compl_status:= fnd_concurrent.set_completion_status('ERROR',null);
342 END IF;
343
344 END IF; -- fnd_concurrent.wait_for_request (DIMENSIONS)
345
346 END LOOP; -- dim_rec
347
348 ELSE -- 'Hierarchy'
349 FOR hier_rec IN (SELECT DISTINCT request_id
350 FROM fem_ld_dim_requests_gt
351 WHERE request_id > 0)
352 LOOP
353 IF fnd_concurrent.wait_for_request(request_id=> hier_rec.request_id,
354 interval => c_interval,
355 max_wait => c_max_wait_time,
356 phase => l_phase,
357 status => l_status,
358 dev_phase => l_dev_phase,
359 dev_status => l_dev_status,
360 message => l_message)
361 THEN
362 IF l_dev_phase || '.' || l_dev_status IN ('COMPLETE.NORMAL','COMPLETE.WARNING') THEN
363 UPDATE fem_ld_hier_requests_gt
364 SET status = 'Y'
365 WHERE request_id = hier_rec.request_id;
366 ELSE
367 UPDATE fem_ld_hier_requests_gt
368 SET status = 'N'
369 WHERE request_id = hier_rec.request_id;
370 l_compl_status:= fnd_concurrent.set_completion_status('ERROR',null);
371 END IF;
372
373 END IF; -- fnd_concurrent.wait_for_request (DIMENSIONS)
374
375 END LOOP; -- dim_rec
376
377 END CASE; -- p_wait_for
378
379 fem_engines_pkg.tech_message (p_severity => g_log_level_2
380 ,p_module => g_block||'.wait_for_requests(PROCEDURE)'
381 ,p_msg_text => 'END wait_for_requests ');
382
383 EXCEPTION
384 WHEN OTHERS THEN
385 fem_engines_pkg.tech_message (p_severity => g_log_level_6
386 ,p_module => g_block||'.wait_for_requests(PROCEDURE)'
387 ,p_msg_text => 'EXCEPTION in wait_for_requests ' || sqlerrm);
388 fnd_file.put_line(fnd_file.log, 'Exception - wait_for_requests' || sqlerrm);
389 RAISE;
390 END wait_for_requests;
391
392 ------------------------
393 -- END wait_for_requests
394 ------------------------
395
396 --------------------------------------------------------------------------------
397 --
398 -- This procedure is used to submit the dimension loader CP. All the requests
399 -- are submitted in parallel. It first checks if there are records in the
400 -- interface table before issuing the call to the CP. If no records are present
401 -- the request_id is set to -10000 facilitating an easy reporting
402 --
403 --------------------------------------------------------------------------------
404
405
406 PROCEDURE submit_dimension_loaders IS
407 l_request_id NUMBER;
408
409 l_table_name VARCHAR2(30);
410
411 l_dim_load_mode VARCHAR2(1);
412 l_return_status VARCHAR2(1);
413 l_msg_data VARCHAR2(4000);
414 l_msg_count NUMBER;
415
416 l_dummy NUMBER;
417 l_at_least_one BOOLEAN;
418 l_compl_status BOOLEAN;
419
420 BEGIN
421
422 fem_engines_pkg.tech_message (p_severity => g_log_level_2
423 ,p_module => g_block||'.submit_dimension_loaders(PROCEDURE)'
424 ,p_msg_text => 'BEGIN..for submit_dimension_loaders');
425
426
427 l_at_least_one := FALSE;
428
429 FOR dim_rec IN (SELECT dimension_id,
430 dim_intf_table_name
431 FROM fem_ld_dim_requests_gt )
432 LOOP
433
434 BEGIN
435 EXECUTE IMMEDIATE 'SELECT 1 FROM ' || dim_rec.dim_intf_table_name || ' WHERE ROWNUM = 1' INTO l_dummy;
436 EXCEPTION
437 WHEN NO_DATA_FOUND THEN
438 l_dummy := 0.0;
439 WHEN OTHERS THEN
440 fem_engines_pkg.tech_message (p_severity => g_log_level_6
441 ,p_module => g_block||'.submit_dimension_loaders(PROCEDURE)'
442 ,p_msg_text => 'Error while checking if data EXISTS in interface table '
443 || dim_rec.dim_intf_table_name || ' - ' || sqlerrm);
444 fnd_file.put_line(fnd_file.log, 'Table ' || dim_rec.dim_intf_table_name || ' does not exist ');
445 fnd_file.put_line(fnd_file.log, 'Exception - submit_dimension_loaders ' || sqlerrm);
446
447 RAISE;
448 END;
449
450 IF l_dummy = 1 THEN
451
452 fem_loader_eng_util_pkg.get_dim_loader_exec_mode(c_api_version,
453 c_false,
454 c_false,
455 c_true,
456 l_return_status,
457 l_msg_count,
458 l_msg_data,
459 dim_rec.dimension_id,
460 l_dim_load_mode);
461
462 l_request_id := fnd_request.submit_request('FEM',
463 'FEM_DIM_MEMBER_LOADER',
464 NULL,
465 NULL,
466 FALSE,
467 l_dim_load_mode,
468 dim_rec.dimension_id);
469
470 l_at_least_one := TRUE;
471 COMMIT;
472 ELSE
473 l_request_id := -10000.0; -- No records available in interface table
474 l_compl_status:= fnd_concurrent.set_completion_status('ERROR',null);
475 END IF;
476
477 l_dummy := 0.0;
478
479 UPDATE fem_ld_dim_requests_gt
480 SET request_id = l_request_id
481 WHERE dimension_id = dim_rec.dimension_id;
482
483 END LOOP;
484
485 IF l_at_least_one THEN
486 fem_engines_pkg.tech_message (p_severity => g_log_level_1
487 ,p_module => g_block||'.submit_dimension_loaders(PROCEDURE)'
488 ,p_msg_text => 'Calling wait_for_requests - DIMENSION');
489
490 wait_for_requests(c_dim_loader);
491
492 fem_engines_pkg.tech_message (p_severity => g_log_level_1
493 ,p_module => g_block||'.submit_dimension_loaders(PROCEDURE)'
494 ,p_msg_text => 'Completed wait_for_requests - DIMENSION');
495 END IF;
496
497 fem_engines_pkg.tech_message (p_severity => g_log_level_2
498 ,p_module => g_block||'.submit_dimension_loaders(PROCEDURE)'
499 ,p_msg_text => 'END submit_dimension_loaders');
500
501 EXCEPTION
502 WHEN OTHERS THEN
503 fem_engines_pkg.tech_message (p_severity => g_log_level_6
504 ,p_module => g_block||'.submit_dimension_loaders(PROCEDURE)'
505 ,p_msg_text => 'EXCEPTION in submit_dimension_loaders ' || sqlerrm);
506 fnd_file.put_line(fnd_file.log, 'Exception - submit_dimension_loaders' || sqlerrm);
507 RAISE;
508 END submit_dimension_loaders;
509
510 -------------------------------
511 -- END submit_dimension_loaders
512 -------------------------------
513
514 --------------------------------------------------------------------------------
515 --
516 -- This procedure submits the hierarchy loader CP. Only unique combinations are
517 -- submitted
518 --
519 --------------------------------------------------------------------------------
520
521
522 PROCEDURE submit_hierarchy_loaders IS
523
524 l_request_id NUMBER;
525
526 l_table_name VARCHAR2(30);
527
528 l_hier_load_mode VARCHAR2(1);
529
530 l_return_status VARCHAR2(1);
531 l_msg_data VARCHAR2(4000);
532 l_msg_count NUMBER;
533
534 l_at_least_one BOOLEAN;
535
536 BEGIN
537
538 fem_engines_pkg.tech_message (p_severity => g_log_level_2
539 ,p_module => g_block||'.submit_hierarchy_loader(PROCEDURE)'
540 ,p_msg_text => 'BEGIN..for submit_hierarchy_loader');
541
542 l_at_least_one := FALSE;
543
544 FOR hier_rec IN (SELECT DISTINCT
545 dimension_id,
546 dimension_varchar_label,
547 hierarchy_object_name,
548 hier_obj_def_display_name
549 FROM fem_ld_hier_requests_gt )
550 LOOP
551
552 fem_loader_eng_util_pkg.get_hier_loader_exec_mode(c_api_version,
553 c_false,
554 c_false,
555 c_true,
556 l_return_status,
557 l_msg_count,
558 l_msg_data,
559 hier_rec.dimension_id,
560 hier_rec.hierarchy_object_name,
561 l_hier_load_mode);
562
563 l_request_id := fnd_request.submit_request('FEM',
564 'FEM_HIER_LOADER',
565 NULL,
566 NULL,
567 FALSE,
568 g_hier_object_def_id,
569 l_hier_load_mode,
570 hier_rec.dimension_varchar_label,
571 hier_rec.hierarchy_object_name,
572 hier_rec.hier_obj_def_display_name);
573 COMMIT;
574
575 l_at_least_one := TRUE;
576
577 UPDATE fem_ld_hier_requests_gt
578 SET request_id = l_request_id
579 WHERE dimension_id = hier_rec.dimension_id
580 AND dimension_varchar_label = hier_rec.dimension_varchar_label
581 AND hierarchy_object_name = hier_rec.hierarchy_object_name
582 AND hier_obj_def_display_name = hier_rec.hier_obj_def_display_name;
583
584 END LOOP;
585
586 IF l_at_least_one THEN
587 fem_engines_pkg.tech_message (p_severity => g_log_level_1
588 ,p_module => g_block||'.submit_hierarchy_loaders(PROCEDURE)'
589 ,p_msg_text => 'Calling wait_for_requests - HIERARCHY');
590
591 wait_for_requests(c_hier_loader);
592
593 fem_engines_pkg.tech_message (p_severity => g_log_level_1
594 ,p_module => g_block||'.submit_hierarchy_loaders(PROCEDURE)'
595 ,p_msg_text => 'Completed wait_for_requests - HIERARCHY');
596 END IF;
597
598 fem_engines_pkg.tech_message (p_severity => g_log_level_2
599 ,p_module => g_block||'.submit_hierarchy_loader(PROCEDURE)'
600 ,p_msg_text => 'END submit_hierarchy_loader');
601
602
603 EXCEPTION
604 WHEN OTHERS THEN
605 fem_engines_pkg.tech_message (p_severity => g_log_level_6
606 ,p_module => g_block||'.submit_hierarchy_loader(PROCEDURE)'
607 ,p_msg_text => 'EXCEPTION in submit_hierarchy_loader ' || sqlerrm);
608 fnd_file.put_line(fnd_file.log, 'Exception - submit_hierarchy_loaders' || sqlerrm);
609 RAISE;
610 END submit_hierarchy_loaders;
611
612 -------------------------------
613 -- END submit_hierarchy_loaders
614 -------------------------------
615
616 --------------------------------------------------------------------------------
617 --
618 -- This procedure populates the list of all dimensions across all the unique
619 -- tables. If the user has selected the option of loading the hierarchies as
620 -- well, populates fem_ld_hier_requests_gt with the info.
621 --
622 --------------------------------------------------------------------------------
623
624 PROCEDURE build_dim_stages IS
625 BEGIN
626
627 fem_engines_pkg.tech_message (p_severity => g_log_level_2
628 ,p_module => g_block||'.build_dim_stages(PROCEDURE)'
629 ,p_msg_text => 'BEGIN..for build_dim_stages');
630
631
632 FORALL i IN 1..gs_dim_id_tab.COUNT
633 INSERT INTO fem_ld_dim_requests_gt(dimension_id,
634 dimension_varchar_label,
635 dim_intf_table_name,
636 request_id,
637 status)
638 SELECT gs_dim_id_tab(i),
639 gs_dim_label_tab(i),
640 intf_member_b_table_name,
641 TO_NUMBER(NULL),
642 'N'
643 FROM fem_xdim_dimensions fxd
644 WHERE fxd.dimension_id = gs_dim_id_tab(i)
645 AND intf_member_b_table_name IS NOT NULL;
646
647 IF SQL%ROWCOUNT > 0.0 THEN
648 submit_dimension_loaders;
649 ELSE
650 UPDATE fem_ld_dim_requests_gt
651 SET request_id = -10000;
652 END IF;
653
654 fem_engines_pkg.tech_message (p_severity => g_log_level_2
655 ,p_module => g_block||'.build_dim_stages(PROCEDURE)'
656 ,p_msg_text => 'END build_dim_stages');
657
658
659 EXCEPTION
660 WHEN OTHERS THEN
661 fem_engines_pkg.tech_message (p_severity => g_log_level_6
662 ,p_module => g_block||'.build_dim_stages(PROCEDURE)'
663 ,p_msg_text => 'EXCEPTION in build_dim_stages ' || sqlerrm);
664 fnd_file.put_line(fnd_file.log, 'Exception - build_dim_stages' || sqlerrm);
665 RAISE;
666 END build_dim_stages;
667
668 -----------------------
669 -- END build_dim_stages
670 -----------------------
671
672 --------------------------------------------------------------------------------
673 --
674 -- This procedure populates the list of all hierarchies in fem_ld_hier_requests_gt
675 --
676 --------------------------------------------------------------------------------
677
678 PROCEDURE build_hier_stages IS
679 l_hier_count NUMBER;
680 BEGIN
681
682 IF g_hier_object_def_id IS NULL THEN
683 BEGIN
684 SELECT object_definition_id
685 INTO g_hier_object_def_id
686 FROM fem_object_definition_vl d
687 WHERE d.object_id in (SELECT o.object_id
688 FROM fem_object_catalog_vl o
689 WHERE o.object_type_code = 'HIERARCHY_LOADER'
690 AND o.folder_id in (SELECT f.folder_id
691 FROM fem_user_folders f
692 WHERE f.user_id = fnd_global.user_id)
693 )
694 AND d.old_approved_copy_flag = 'N'
695 AND d.approval_status_code NOT IN ('SUBMIT_DELETE','SUBMIT_APPROVAL');
696 EXCEPTION
697 WHEN OTHERS THEN
698 fem_engines_pkg.tech_message (p_severity => g_log_level_6
699 ,p_module => g_block||'.build_dim_stages(PROCEDURE)'
700 ,p_msg_text => 'EXCEPTION in build_dim_stages -
701 fetching object_def_id for HIER');
702 fem_data_loader_pkg.trace('Error in build dim stages while building HIER'); RAISE;
703 END;
704
705 END IF;
706
707 FORALL i IN 1..gs_hier_dim_id_tab.COUNT
708 INSERT INTO fem_ld_hier_requests_gt(dimension_id,
709 dimension_varchar_label,
710 hierarchy_object_name,
711 hier_obj_def_display_name,
712 request_id,
713 table_row)
714 SELECT gs_hier_dim_id_tab(i),
715 gs_hier_dim_label_tab(i),
716 fht.hierarchy_object_name,
717 fht.hier_obj_def_display_name,
718 TO_NUMBER(NULL),
719 gs_table_row_tab(i)
720 FROM fem_hierarchies_t fht
721 WHERE fht.dimension_varchar_label = gs_hier_dim_label_tab(i)
722 AND gs_hier_obj_def_id_tab(i) = -1
723 AND gs_hier_obj_id_tab(i) = -1;
724
725 FORALL i IN 1..gs_hier_dim_id_tab.COUNT
726 INSERT INTO fem_ld_hier_requests_gt(dimension_id,
727 dimension_varchar_label,
728 hierarchy_object_name,
729 hier_obj_def_display_name,
730 request_id,
731 table_row)
732 SELECT gs_hier_dim_id_tab(i),
733 gs_hier_dim_label_tab(i),
734 fht.hierarchy_object_name,
735 fht.hier_obj_def_display_name,
736 TO_NUMBER(NULL),
737 gs_table_row_tab(i)
738 FROM fem_hierarchies_t fht,
739 fem_object_catalog_vl focb,
740 fem_object_definition_vl fodb
741 WHERE fht.dimension_varchar_label = gs_hier_dim_label_tab(i)
742 AND fht.hierarchy_object_name = focb.object_name
743 AND focb.object_id = gs_hier_obj_id_tab(i)
744 AND fht.hier_obj_def_display_name = fodb.display_name
745 AND focb.object_id = fodb.object_id
746 AND fht.language = USERENV('LANG')
747 AND focb.object_type_code = 'HIERARCHY'
748 AND gs_hier_obj_def_id_tab(i) <> -1
749 AND gs_hier_obj_id_tab(i) = -1;
750
751 FORALL i IN 1..gs_hier_dim_id_tab.COUNT
752 INSERT INTO fem_ld_hier_requests_gt(dimension_id,
753 dimension_varchar_label,
754 hierarchy_object_name,
755 hier_obj_def_display_name,
756 request_id,
757 table_row)
758 SELECT gs_hier_dim_id_tab(i),
759 gs_hier_dim_label_tab(i),
760 fht.hierarchy_object_name,
761 fht.hier_obj_def_display_name,
762 TO_NUMBER(NULL),
763 gs_table_row_tab(i)
764 FROM fem_hierarchies_t fht,
765 fem_hierarchies fh,
766 fem_object_catalog_vl focb,
767 fem_object_definition_vl fodb
768 WHERE fht.dimension_varchar_label = gs_hier_dim_label_tab(i)
769 AND fht.hierarchy_object_name = focb.object_name
770 AND focb.object_id = fh.hierarchy_obj_id
771 AND focb.object_id = gs_hier_obj_id_tab(i)
772 AND fht.hier_obj_def_display_name = fodb.display_name
773 AND focb.object_id = fodb.object_id
774 AND fodb.object_definition_id = gs_hier_obj_def_id_tab(i)
775 AND fht.language = USERENV('LANG')
776 AND gs_hier_obj_def_id_tab(i) <> -1
777 AND gs_hier_obj_id_tab(i) <> -1
778 AND focb.object_type_code = 'HIERARCHY';
779
780 SELECT COUNT(1)
781 INTO l_hier_count
782 FROM fem_ld_hier_requests_gt
783 WHERE ROWNUM = 1;
784
785 IF l_hier_count > 0.0 THEN
786 fem_engines_pkg.tech_message (p_severity => g_log_level_1
787 ,p_module => g_block||'.build_hier_stages(PROCEDURE)'
788 ,p_msg_text => 'Calling submit_hierarchy_loaders');
789
790 submit_hierarchy_loaders;
791
792 fem_engines_pkg.tech_message (p_severity => g_log_level_1
793 ,p_module => g_block||'.build_hier_stages(PROCEDURE)'
794 ,p_msg_text => 'Calling submit_hierarchy_loaders');
795
796 END IF;
797
798 fem_engines_pkg.tech_message (p_severity => g_log_level_2
799 ,p_module => g_block||'.build_hier_stages(PROCEDURE)'
800 ,p_msg_text => 'END build_hier_stages');
801
802
803 EXCEPTION
804 WHEN OTHERS THEN
805 fem_engines_pkg.tech_message (p_severity => g_log_level_6
806 ,p_module => g_block||'.build_hier_stages(PROCEDURE)'
807 ,p_msg_text => 'EXCEPTION in build_hier_stages ' || sqlerrm);
808 fnd_file.put_line(fnd_file.log, 'Exception - build_hier_stages' || sqlerrm);
809 RAISE;
810 END build_hier_stages;
811
812 ------------------------
813 -- END build_hier_stages
814 ------------------------
815
816 --------------------------------------------------------------------------------
817 --
818 --
819 --
820 --
821 --------------------------------------------------------------------------------
822
823 PROCEDURE print_params IS
824
825 BEGIN
826
827 fnd_file.put_line(FND_FILE.log, '=============================================================================');
828 fnd_file.put_line(FND_FILE.log, '======================== Printing Parameters ==========================');
829
830 FOR dim_rec IN (SELECT dimension_id,
831 dim_intf_table_name,
832 dimension_varchar_label
833 FROM fem_ld_dim_requests_gt )
834 LOOP
835 fnd_file.put_line(FND_FILE.log, ' Dimension ID :: ' || dim_rec.dimension_id);
836 fnd_file.put_line(FND_FILE.log, ' Interface TB :: ' || dim_rec.dim_intf_table_name);
837 fnd_file.put_line(FND_FILE.log, ' Dim Label :: ' || dim_rec.dimension_varchar_label);
838 END LOOP;
839
840 fnd_file.put_line(FND_FILE.log, '=============================================================================');
841
842 FOR hier_rec IN (SELECT dimension_id,
843 dimension_varchar_label,
844 hierarchy_object_name,
845 hier_obj_def_display_name,
846 table_row
847 FROM fem_ld_hier_requests_gt )
848 LOOP
849 fnd_file.put_line(FND_FILE.log, ' Table Row :: ' || hier_rec.table_row);
850 fnd_file.put_line(FND_FILE.log, ' Dimension ID :: ' || hier_rec.dimension_id);
851 fnd_file.put_line(FND_FILE.log, ' Dim Label :: ' || hier_rec.dimension_varchar_label);
852 fnd_file.put_line(FND_FILE.log, ' Hier Obj Name:: ' || hier_rec.hierarchy_object_name);
853 fnd_file.put_line(FND_FILE.log, ' Hier Obj Def :: ' || hier_rec.hier_obj_def_display_name);
854 END LOOP;
855
856 fnd_file.put_line(FND_FILE.log, '======================== End Printing Parameters ======================');
857 fnd_file.put_line(FND_FILE.log, '=============================================================================');
858
859
860 END print_params;
861
862 -------------------
863 -- END print_params
864 -------------------
865
866 --------------------------------------------------------------------------------
867 --
868 -- This procedure is used for printing the outcome of dimension loader CP
869 --
870 --------------------------------------------------------------------------------
871
872
873 PROCEDURE log_dimensions IS
874
875 BEGIN
876
877 fem_engines_pkg.tech_message (p_severity => g_log_level_2
878 ,p_module => g_block||'.log_dimensions(PROCEDURE)'
879 ,p_msg_text => 'BEGIN..for log_dimensions');
880
881
882 fem_data_loader_pkg.trace('SEPARATOR');
883
884 FOR dim_rec IN ( SELECT dimension_varchar_label,
885 request_id,
886 status
887 FROM fem_ld_dim_requests_gt)
888 LOOP
889 IF dim_rec.request_id > 0 THEN
890 IF dim_rec.status = 'Y' THEN
891 fnd_message.set_name('FEM','FEM_DLLDR_DIMENSION_LOADED');
892 fnd_message.set_token('REQUEST_ID',dim_rec.request_id);
893 fnd_message.set_token('DIM_LABEL',dim_rec.dimension_varchar_label);
894 ELSE
895 fnd_message.set_name('FEM','FEM_DLLDR_DIMENSION_ERR');
896 fnd_message.set_token('REQUEST_ID',dim_rec.request_id);
897 fnd_message.set_token('DIM_LABEL',dim_rec.dimension_varchar_label);
898 END IF;
899 ELSIF dim_rec.request_id < 0 THEN
900 fnd_message.set_name('FEM', 'FEM_DLLDR_DIMENSION_NO_DATA');
901 fnd_message.set_token('DIM_LABEL',dim_rec.dimension_varchar_label);
902 ELSE
903 fnd_message.set_name('FEM', 'FEM_DLLDR_DIM_CONC_PGM_ERR');
904 fnd_message.set_token('DIM_LABEL',dim_rec.dimension_varchar_label);
905 END IF;
906
907 fem_data_loader_pkg.trace('MESSAGE');
908
909 END LOOP; -- dim_rec
910
911 fem_data_loader_pkg.trace('SEPARATOR');
912
913 fem_engines_pkg.tech_message (p_severity => g_log_level_2
914 ,p_module => g_block||'.log_dimensions(PROCEDURE)'
915 ,p_msg_text => 'END log_dimensions');
916
917 EXCEPTION
918 WHEN OTHERS THEN
919 fem_engines_pkg.tech_message (p_severity => g_log_level_6
920 ,p_module => g_block||'.log_dimensions(PROCEDURE)'
921 ,p_msg_text => 'EXCEPTION in log_dimensions ' || sqlerrm);
922 fnd_file.put_line(fnd_file.log, 'Exception - log_dimensions' || sqlerrm);
923 RAISE;
924
925 END log_dimensions;
926
927 ---------------------
928 -- END log_dimensions
929 ---------------------
930
931 --------------------------------------------------------------------------------
932 --
933 -- This procedure is used for printing the outcome of hierarchy loader CP
934 --
935 --------------------------------------------------------------------------------
936
937
938 PROCEDURE log_hierarchies IS
939
940 l_row_exists BOOLEAN;
941
942 l_hier_str VARCHAR2(600);
943 l_hier_name VARCHAR2(300);
944 l_hier_def_name VARCHAR2(300);
945
946 l_lang VARCHAR2(100);
947
948 BEGIN
949
950 fem_engines_pkg.tech_message (p_severity => g_log_level_2
951 ,p_module => g_block||'.log_hierarchies(PROCEDURE)'
952 ,p_msg_text => 'BEGIN..for log_hierarchies');
953
954 fem_data_loader_pkg.trace('SEPARATOR');
955
956 l_lang := USERENV('LANG');
957
958 l_row_exists := FALSE;
959
960 FOR i IN 1..gs_table_row_tab.COUNT LOOP
961
962 FOR hier_rec IN (SELECT dimension_varchar_label,
963 hierarchy_object_name,
964 hier_obj_def_display_name,
965 request_id,
966 status
967 FROM fem_ld_hier_requests_gt
968 WHERE table_row = gs_table_row_tab(i))
969 LOOP
970
971 l_row_exists := TRUE;
972
973 IF hier_rec.request_id > 0 THEN
974 IF hier_rec.status = 'Y' THEN
975 fnd_message.set_name('FEM','FEM_DLLDR_HIERARCHY_LOADED');
976 fnd_message.set_token('REQUEST_ID',hier_rec.request_id);
977 fnd_message.set_token('HIERARCHY',hier_rec.hierarchy_object_name || '.' ||
978 hier_rec.hier_obj_def_display_name);
979 fnd_message.set_token('DIM_LABEL',gs_hier_dim_label_tab(i));
980 ELSE
981 fnd_message.set_name('FEM','FEM_DLLDR_HIERARCHY_ERR');
982 fnd_message.set_token('REQUEST_ID',hier_rec.request_id);
983 fnd_message.set_token('HIERARCHY',hier_rec.hierarchy_object_name || '.' ||
984 hier_rec.hier_obj_def_display_name);
985 fnd_message.set_token('DIM_LABEL',gs_hier_dim_label_tab(i));
986 END IF;
987 ELSE
988 fnd_message.set_name('FEM', 'FEM_DLLDR_HIER_CONC_PGM_ERR');
989 fnd_message.set_token('HIERARCHY',hier_rec.hierarchy_object_name || '.' ||
990 hier_rec.hier_obj_def_display_name);
991 fnd_message.set_token('DIM_LABEL',gs_hier_dim_label_tab(i));
992 END IF;
993
994 END LOOP; -- hier_rec
995
996 IF l_row_exists THEN
997 l_row_exists := FALSE;
998 ELSE
999 fnd_message.set_name('FEM', 'FEM_DLLDR_HIER_NO_DATA');
1000
1001 IF gs_hier_obj_id_tab(i) = -1 AND gs_hier_obj_def_id_tab(i) = -1 THEN
1002 l_hier_str := g_all_str || '.' || g_all_str;
1003 ELSIF gs_hier_obj_id_tab(i) <> -1 AND gs_hier_obj_def_id_tab(i) = -1 THEN
1004 SELECT object_name
1005 INTO l_hier_str
1006 FROM fem_object_catalog_tl
1007 WHERE object_id = gs_hier_obj_id_tab(i)
1008 AND language = l_lang;
1009
1010 l_hier_str := l_hier_str || '.' || g_all_str;
1011 ELSE
1012 SELECT object_name
1013 INTO l_hier_name
1014 FROM fem_object_catalog_tl
1015 WHERE object_id = gs_hier_obj_id_tab(i)
1016 AND language = l_lang;
1017
1018 SELECT display_name
1019 INTO l_hier_def_name
1020 FROM fem_object_definition_tl
1021 WHERE object_definition_id = gs_hier_obj_def_id_tab(i)
1022 AND language = l_lang;
1023
1024 l_hier_str := l_hier_name || '.' || l_hier_def_name;
1025 END IF; -- gs_hier_obj_id_tab(i)
1026
1027 fnd_message.set_token('HIERARCHY',l_hier_str);
1028 fnd_message.set_token('DIM_LABEL',gs_hier_dim_label_tab(i));
1029 END IF; -- l_row_exists
1030
1031 fem_data_loader_pkg.trace('MESSAGE');
1032
1033 END LOOP; -- 1..gs_table_row_tab
1034
1035 fem_data_loader_pkg.trace('SEPARATOR');
1036
1037 fem_engines_pkg.tech_message (p_severity => g_log_level_2
1038 ,p_module => g_block||'.log_hierarchies(PROCEDURE)'
1039 ,p_msg_text => 'END log_hierarchies');
1040
1041
1042 EXCEPTION
1043 WHEN OTHERS THEN
1044 fem_engines_pkg.tech_message (p_severity => g_log_level_2
1045 ,p_module => g_block||'.log_hierarchies(PROCEDURE)'
1046 ,p_msg_text => 'EXCEPTION in log_hierarchies ' || sqlerrm);
1047 fnd_file.put_line(fnd_file.log, 'Exception - log_hierarchies' || sqlerrm);
1048 RAISE;
1049 END log_hierarchies;
1050
1051 ----------------------
1052 -- END log_hierarchies
1053 ----------------------
1054
1055 BEGIN
1056
1057 fem_engines_pkg.tech_message (p_severity => g_log_level_2
1058 ,p_module => g_block||'.MAIN - start'
1059 ,p_msg_text => 'BEGIN MAIN PACKAGE SECTION');
1060
1061 g_load_dimensions := FALSE;
1062 g_load_hierarchies := FALSE;
1063
1064 fnd_message.set_name('FEM', 'FEM_ALL_TXT');
1065 g_all_str := fnd_message.get;
1066
1067
1068 DELETE fem_ld_dim_requests_gt;
1069
1070 DELETE fem_ld_hier_requests_gt;
1071
1072 fem_engines_pkg.tech_message (p_severity => g_log_level_2
1073 ,p_module => g_block||'.MAIN - end'
1074 ,p_msg_text => 'END MAIN PACKAGE SECTION');
1075
1076
1077 EXCEPTION
1078 WHEN OTHERS THEN
1079 fem_engines_pkg.tech_message (p_severity => g_log_level_6
1080 ,p_module => g_block||'.MAIN'
1081 ,p_msg_text => 'EXCEPTION in MAIN PACKAGE SECTION ' || sqlerrm);
1082 fnd_file.put_line(fnd_file.log, 'Exception - main ' || sqlerrm);
1083 RAISE;
1084
1085 END Fem_Dim_Hier_Loader_Pkg;