1 package body zpb_drill_info AS
2 /* $Header: zpbdrill.plb 120.1 2007/12/04 15:25:45 mbhat noship $ */
3
4 procedure get_drill_info ( p_view IN VARCHAR2
5 , p_drill_info IN VARCHAR2
6 , x_drill_value OUT NOCOPY VARCHAR2
7 , x_result OUT NOCOPY VARCHAR2
8 , x_msg_out OUT NOCOPY VARCHAR2) AS
9 l_bp_id zpb_analysis_cycles.analysis_cycle_id%TYPE;
10 l_ds_code zpb_cycle_datasets.DATASET_CODE%TYPE;
11 l_ledger_id zpb_busarea_ledgers.ledger_id%TYPE;
12 l_ledger_count NUMBER := 0;
13 l_dataset_count NUMBER := 0;
14
15 -- get the BP id
16 cursor c_bp_id is select instance_id from zpb_measures where type = 'SHARED_VIEW_DATA'
17 and name = p_view;
18
19 -- get the BP id
20 cursor c_ds_id is select DATASET_CODE from zpb_cycle_datasets
21 where (ANALYSIS_CYCLE_ID = l_bp_id) OR
22 (ANALYSIS_CYCLE_ID = (select max(ANALYSIS_CYCLE_ID) from zpb_analysis_cycles
23 where CURRENT_INSTANCE_ID = l_bp_id and
24 status_code = 'COMPLETE'));
25
26 -- get the BP id
27 cursor c_ledger_id(cp_ds_id zpb_cycle_datasets.DATASET_CODE%TYPE) is
28 select distinct ledger_id from fem_data_locations where dataset_code = cp_ds_id;
29
30 BEGIN
31 x_result := 'S';
32
33 IF (p_view is NOT NULL) THEN
34 IF (p_drill_info = 'ENABLE_DRILL') THEN
35
36 OPEN c_bp_id;
37 FETCH c_bp_id into l_bp_id;
38 CLOSE c_bp_id;
39
40 IF (l_bp_id is null) THEN
41
42 x_drill_value := 'N';
43 x_msg_out := 'This view is either invalid or does not have loaded data';
44 x_result := 'E';
45 return;
46 END IF;
47
48 FOR i in c_ds_id LOOP
49 l_dataset_count := l_dataset_count + 1;
50 FOR j in c_ledger_id(i.dataset_code) LOOP
51 l_ledger_count := l_ledger_count + 1;
52 END LOOP;
53 END LOOP;
54
55 IF( l_dataset_count = 0 ) THEN
56 x_drill_value := 'N';
57 x_result := 'E';
58 x_msg_out := 'This view does not have loaded data';
59 return;
60 END IF;
61
62 IF( l_ledger_count = 0 ) THEN
63 x_result := 'E';
64 x_drill_value := 'N';
65 x_msg_out := 'Dataset not mapped to a ledger!';
66 return;
67 ELSIF (l_ledger_count > 1) THEN
68 x_result := 'S';
69 x_drill_value := 'N';
70 x_msg_out := 'There are multilple ledgers being loaded';
71 return;
72 ELSE
73 x_result := 'S';
74 x_drill_value := 'Y';
75 x_msg_out := 'There is a single ledger loaded';
76 return;
77 END IF;
78 ELSE
79 x_msg_out := 'The drill info asked for is not valid';
80 x_result := 'E';
81 x_drill_value := 'N';
82 return;
83 END IF;
84
85 ELSE
86 x_msg_out := 'View passed in is null';
87 x_drill_value := 'N';
88 x_result := 'E';
89 return;
90 END IF;
91 EXCEPTION
92 WHEN OTHERS THEN
93 x_result := 'U';
94 x_drill_value := 'N';
95 x_msg_out := 'Unexpected error '|| sqlerrm;
96 END get_drill_info ;
97
98 END zpb_drill_info ;
[Home] [Help]