1 PACKAGE BIS_PMF_GET_DIMLEVELS_PVT AS
2 /* $Header: BISVGDLS.pls 120.5 2007/04/02 10:17:00 ashankar ship $ */
3 /*
4 REM dbdrv: sql ~PROD ~PATH ~FILE none none none package &phase=pls \
5 REM dbdrv: checkfile:~PROD:~PATH:~FILE
6 REM +=======================================================================+
7 REM | Copyright (c) 1998 Oracle Corporation, Redwood Shores, CA, USA |
8 REM | All rights reserved. |
9 REM +=======================================================================+
10 REM | FILENAME |
11 REM | BISVGDLS.pls |
12 REM | |
13 REM | DESCRIPTION |
14 REM | Private API for getting the Select String for DimensionLevelValues|
15 REM | This API will get the Select String from either EDW or BIS
16 REM | depending on the profile option BIS_SOURCE
17 REM | |
18 REM | HISTORY |
19 REM | December-2000 amkulkar Creation |
20 REM | 09-OCT-2002 MAHRAO Fix for 2617369 |
21 REM | 03-JAN-2003 RCHANDRA Bug 2721710, created record |
22 REM | dim_level_select_rec_Type to hold dim level |
23 REM | info from API GET_DIMLEVEL_SELECT_STRING |
24 REM | 09-MAY-2003 arhegde Added record and table types for child and |
25 REM | parent dim level info , enh 2819971
26 REM | 25-JAN-2004 gbhaloti bug#3388371 add support to get select string for |
27 REM | BSC dimension levels |
28 REM | 11-FEB-2004 ankgoel bug#3426427 Added parameter p_add_distinct |
29 REM | 30-JUN-2005 arhegde enh# 4456833 get_dimlevel_select_wrap() and |
30 REM | get_dimlevel_select_string() - added x_is_relation_recursive |
31 REM | 27-Sep-2005 ankgoel Bug#4625598,4626579 Uptake common API to get dim |
32 REM | level values |
33 REM | 09-feb-2007 ashankar Simulation Tree Enhacement 5386112 |
34 REM | 09-Mar-2007 ashankar Fix for the bug #5920996 |
35 REM | 29/03/07 ashankar Bug#5932973 Supporting filters and key items for SM tree |
36 REM +=======================================================================+
37 */
38 --
39 -- Record to hold the results of GET_DIMLEVEL_SELECT_STRING API
40 TYPE dim_level_select_rec_Type IS RECORD
41 (Dim_level_sname VARCHAR2(2000)
42 ,Table_name VARCHAR2(2000)
43 ,Id_name VARCHAR2(2000)
44 ,Value_name VARCHAR2(2000)
45 ,Parent_name VARCHAR2(2000)
46 ,Select_String VARCHAR2(32000)
47 ,Time_level VARCHAR2(2000)
48 );
49
50 TYPE BIS_PMF_QUERY_ATTRIBUTES_TABLE IS TABLE OF BIS_PMF_QUERY_ATTRIBUTES;
51
52 PROCEDURE GET_DIMLEVEL_SELECT_STRING
53 (p_DimLevelName IN VARCHAR2
54 ,p_add_distinct IN VARCHAR2 := 'F'
55 ,x_Select_String OUT NOCOPY VARCHAR2
56 ,x_table_name OUT NOCOPY VARCHAR2
57 ,x_id_name OUT NOCOPY VARCHAR2
58 ,x_value_name OUT NOCOPY VARCHAR2
59 ,x_time_level OUT NOCOPY VARCHAR2
60 ,x_return_status OUT NOCOPY VARCHAR2
61 ,x_msg_count OUT NOCOPY NUMBER
62 ,x_msg_data OUT NOCOPY VARCHAR2
63 );
64 PROCEDURE GET_DIMLEVEL_SELECT_STRING
65 (p_DimLevelName IN VARCHAR2
66 ,p_bis_source IN VARCHAR2
67 ,x_Select_String OUT NOCOPY VARCHAR2
68 ,x_table_name OUT NOCOPY VARCHAR2
69 ,x_id_name OUT NOCOPY VARCHAR2
70 ,x_value_name OUT NOCOPY VARCHAR2
71 ,x_time_level OUT NOCOPY VARCHAR2
72 ,x_return_status OUT NOCOPY VARCHAR2
73 ,x_msg_count OUT NOCOPY NUMBER
74 ,x_msg_data OUT NOCOPY VARCHAR2
75 );
76 FUNCTION isAccounting_Flexfield
77 (p_dim_level_name IN VARCHAR2
78 )
79 RETURN BOOLEAN;
80 PROCEDURE GET_EDW_SELECT_STRING
81 (p_dim_level_name IN VARCHAR2
82 ,p_source IN VARCHAR2 := NULL -- 2617369
83 ,x_table_name OUT NOCOPY VARCHAR2
84 ,x_id_name OUT NOCOPY VARCHAR2
85 ,x_value_name OUT NOCOPY VARCHAR2
86 ,x_edw_select_String OUT NOCOPY VARCHAR2
87 ,x_time_level OUT NOCOPY VARCHAR2
88 ,x_return_status OUT NOCOPY VARCHAR2
89 ,x_msg_count OUT NOCOPY NUMBER
90 ,x_msg_data OUT NOCOPY VARCHAR2
91 );
92 PROCEDURE GET_BIS_SELECT_STRING
93 (p_dim_level_name IN VARCHAR2
94 ,p_source IN VARCHAR2 := NULL -- 2617369
95 ,p_add_distinct IN VARCHAR2 := 'F'
96 ,x_table_name OUT NOCOPY VARCHAR2
97 ,x_id_name OUT NOCOPY VARCHAR2
98 ,x_value_name OUT NOCOPY VARCHAR2
99 ,x_bis_select_string OUT NOCOPY VARCHAR2
100 ,x_time_level OUT NOCOPY VARCHAR2
101 ,x_return_status OUT NOCOPY VARCHAR2
102 ,x_msg_count OUT NOCOPY NUMBER
103 ,x_msg_data OUT NOCOPY VARCHAR2
104 );
105 PROCEDURE GET_DIMLEVEL_VALUES_DATA
106 (p_bis_dimlevel_id IN NUMBER
107 ,x_dimlevel_short_name OUT NOCOPY VARCHAR2
108 ,x_select_String OUT NOCOPY VARCHAR2
109 ,x_table_name OUT NOCOPY VARCHAR2
110 ,x_value_name OUT NOCOPY VARCHAR2
111 ,x_id_name OUT NOCOPY VARCHAR2
112 ,x_level_name OUT NOCOPY VARCHAR2
113 ,x_description OUT NOCOPY VARCHAR2
114 ,x_return_status OUT NOCOPY VARCHAR2
115 ,x_msg_count OUT NOCOPY NUMBER
116 ,x_msg_data OUT NOCOPY VARCHAR2
117 );
118 --===========================================================
119 PROCEDURE get_dimlevel_select_string(
120 p_dimlevel_rec IN BIS_PMF_GET_DIMLEVELS_PUB.dimlvl_rec_Type
121 ,p_param_lists_tbl IN BIS_PMF_GET_DIMLEVELS_PUB.dimlvl_tbl_Type
122 ,p_bis_source IN bis_levels.source%TYPE := NULL
123 ,p_region_code IN ak_regions.region_code%TYPE
124 ,x_select_string OUT NOCOPY VARCHAR2
125 ,x_table_name OUT NOCOPY VARCHAR2
126 ,x_where_clause OUT NOCOPY VARCHAR2
127 ,x_bind_params OUT NOCOPY BIS_PMF_QUERY_ATTRIBUTES_TABLE
128 ,x_id_name OUT NOCOPY VARCHAR2
129 ,x_value_name OUT NOCOPY VARCHAR2
130 ,x_parent_name OUT NOCOPY VARCHAR2
131 ,x_time_level OUT NOCOPY VARCHAR2
132 ,x_is_relation_recursive OUT NOCOPY VARCHAR2
133 ,x_return_status OUT NOCOPY VARCHAR2
134 ,x_msg_count OUT NOCOPY NUMBER
135 ,x_msg_data OUT NOCOPY VARCHAR2
136
137 );
138
139 PROCEDURE get_dimlevel_select_wrap (
140 p_dimLevel IN VARCHAR2 -- can be an object
141 ,p_paramlist IN VARCHAR2 -- can be an object
142 ,p_bis_source IN VARCHAR2
143 ,p_region_code IN ak_regions.region_code%TYPE
144 ,x_select_string OUT NOCOPY VARCHAR2
145 ,x_table_name OUT NOCOPY VARCHAR2
146 ,x_where_clause OUT NOCOPY VARCHAR2
147 ,x_bind_param_string OUT NOCOPY VARCHAR2
148 ,x_id_name OUT NOCOPY VARCHAR2
149 ,x_value_name OUT NOCOPY VARCHAR2
150 ,x_parent_name OUT NOCOPY VARCHAR2
151 ,x_time_level OUT NOCOPY VARCHAR2
152 ,x_is_relation_recursive OUT NOCOPY VARCHAR2
153 ,x_return_status OUT NOCOPY VARCHAR2
154 ,x_msg_count OUT NOCOPY NUMBER
155 ,x_msg_data OUT NOCOPY VARCHAR2
156 );
157
158 PROCEDURE GET_DIMLEVEL_SELECT_STRING
159 (p_DimLevelName IN VARCHAR2
160 ,p_bis_source IN VARCHAR2
161 ,x_Select_String OUT NOCOPY VARCHAR2
162 ,x_table_name OUT NOCOPY VARCHAR2
163 ,x_id_name OUT NOCOPY VARCHAR2
164 ,x_value_name OUT NOCOPY VARCHAR2
165 ,x_parent_name OUT NOCOPY VARCHAR2
166 ,x_time_level OUT NOCOPY VARCHAR2
167 ,x_return_status OUT NOCOPY VARCHAR2
168 ,x_msg_count OUT NOCOPY NUMBER
169 ,x_msg_data OUT NOCOPY VARCHAR2
170 );
171
172 PROCEDURE GET_BIS_SELECT_STRING
173 (p_dim_level_name IN VARCHAR2
174 ,p_source IN VARCHAR2 := NULL -- 2617369
175 ,p_add_distinct IN VARCHAR2 := 'F'
176 ,x_table_name OUT NOCOPY VARCHAR2
177 ,x_id_name OUT NOCOPY VARCHAR2
178 ,x_value_name OUT NOCOPY VARCHAR2
179 ,x_parent_name OUT NOCOPY VARCHAR2
180 ,x_bis_select_string OUT NOCOPY VARCHAR2
181 ,x_time_level OUT NOCOPY VARCHAR2
182 ,x_return_status OUT NOCOPY VARCHAR2
183 ,x_msg_count OUT NOCOPY NUMBER
184 ,x_msg_data OUT NOCOPY VARCHAR2
185 );
186
187 FUNCTION get_dim_level_source (
188 p_dim_level_short_name IN VARCHAR2
189 ) RETURN VARCHAR2;
190
191 --===========================================================
192 END BIS_PMF_GET_DIMLEVELS_PVT;
[Home] [Help]