1 package BSC_DIMENSION_GROUPS_PUB as
2 /* $Header: BSCPDMGS.pls 120.0 2005/06/01 16:36:41 appldev noship $ */
3 /*
4 +======================================================================================+
5 | Copyright (c) 2001 Oracle Corporation, Redwood Shores, CA, USA |
6 | All rights reserved. |
7 +======================================================================================+
8 | FILENAME |
9 | BSCPDMGS.pls |
10 | |
11 | Creation Date: |
12 | October 9, 2001 |
13 | |
14 | Creator: |
15 | Mario-Jair Campos |
16 | |
17 | Description: |
18 | Public specs version. |
19 | This package creates a Dimension Group in BSC. |
20 | 23-FEB-03 PAJOHRI Added Short_Name to Bsc_Dim_Group_Rec_Type |
21 | Created Overloaded procedures CREATE_DIMENSION_GROUP |
22 | UPDATE_DIMENSION_GROUP |
23 | 29-MAY-03 All Enhancement Phase I- short Name column added |
24 | Functions added "Retrieve_Sys_Dim_Lvls_Grp_Wrap" |
25 | and "set_dim_lvl_grp_prop_wrap" |
26 | 13-JUN-03 Adeulgao fixed Bug#2878840 Added function Get_Next_Value to get |
27 | the next DIM GROUP ID |
28 | 14-JUN-03 mahrao Added Translate_dimesnsion_group procedure |
29 | 22-JUL-2003 arhegde bug# 3050270 Added dim_properties_default_values and calls |
30 | 29-OCT-2003 mahrao bug#3209967 Added a column to bsc_sys_dim_levels_by_group |
31 | 14-NOV-2003 mahrao x_dim_level_where_clause is removed from prcoedure |
32 | Retrieve_Sys_Dim_Lvls_Grp_Wrap as PMF 4.0.7 shouldn't |
33 | pick up any dependency on 5.1.1 |
34 | 07-JAN-2004 rpenneru bug#3459443 Modified for getting where clause from BSC data model|
35 | 21-DEC-04 vtulasi Modified for bug#4045278 - Addtion of LUD |
36 +======================================================================================+
37 */
38
39 c_comp_flag CONSTANT NUMBER := -1; -- existing VB code defaults to this
40 c_default_value CONSTANT VARCHAR2(2) := 'T';
41 c_default_type CONSTANT NUMBER := 0;
42 c_filter_value CONSTANT NUMBER := 0;
43 c_no_items CONSTANT NUMBER := 0;
44 c_parent_in_tot CONSTANT NUMBER := 2;
45 c_total_flag CONSTANT NUMBER := -1;
46 C_DEFAULT_DATA CONSTANT VARCHAR2(2) := 'X';
47
48 TYPE Bsc_Dim_Group_Rec_Type is RECORD(
49 Bsc_Dim_Level_Group_Id BSC_SYS_DIM_GROUPS_TL.dim_group_id%TYPE
50 ,Bsc_Dim_Level_Group_Name BSC_SYS_DIM_GROUPS_TL.name%TYPE
51 ,Bsc_Dim_Level_Group_Short_Name bsc_sys_dim_groups_tl.short_name%TYPE
52 ,Bsc_Dim_Level_Index number
53 ,Bsc_Group_Level_Comp_Flag number -- group
54 ,Bsc_Group_Level_Default_Value varchar2(2) -- group
55 ,Bsc_Group_Level_Default_Type number -- group
56 ,Bsc_Group_Level_Filter_Col varchar2(30) -- group
57 ,Bsc_Group_Level_Filter_Value number -- group
58 ,Bsc_Group_Level_No_Items number -- group
59 ,Bsc_Group_Level_Parent_In_Tot number -- group
60 ,Bsc_Group_Level_Total_Flag number -- group
61 ,Bsc_Group_Level_Where_Clause bsc_sys_dim_levels_by_group.where_clause%TYPE
62 ,Bsc_Language varchar2(5)
63 ,Bsc_Level_Id BSC_SYS_DIM_LEVELS_B.dim_level_id%TYPE
64 ,Bsc_Source_Language varchar2(5)
65 ,Bsc_Created_By BSC_SYS_DIM_GROUPS_TL.created_by%TYPE -- PMD: Dim Level WHO columns
66 ,Bsc_Creation_Date BSC_SYS_DIM_GROUPS_TL.creation_date%TYPE -- For granular locking
67 ,Bsc_Last_Updated_By BSC_SYS_DIM_GROUPS_TL.last_updated_by%TYPE -- PMD
68 ,Bsc_Last_Update_Date BSC_SYS_DIM_GROUPS_TL.last_update_date%TYPE -- PMD
69 ,Bsc_Last_Update_Login BSC_SYS_DIM_GROUPS_TL.last_update_login%TYPE -- PMD
70 );
71
72
73 TYPE Bsc_Dim_Tbl_Type IS TABLE OF Bsc_Dim_Group_Rec_Type
74 INDEX BY BINARY_INTEGER;
75
76 procedure Create_Dimension_Group(
77 p_commit IN varchar2 := FND_API.G_FALSE
78 ,p_Dim_Grp_Rec IN BSC_DIMENSION_GROUPS_PUB.Bsc_Dim_Group_Rec_Type
79 ,p_create_Dim_Levels IN BOOLEAN
80 ,x_return_status OUT NOCOPY varchar2
81 ,x_msg_count OUT NOCOPY number
82 ,x_msg_data OUT NOCOPY varchar2
83 );
84
85 procedure Create_Dimension_Group(
86 p_commit IN varchar2 := FND_API.G_FALSE
87 ,p_Dim_Grp_Rec IN BSC_DIMENSION_GROUPS_PUB.Bsc_Dim_Group_Rec_Type
88 ,x_return_status OUT NOCOPY varchar2
89 ,x_msg_count OUT NOCOPY number
90 ,x_msg_data OUT NOCOPY varchar2
91 );
92
93 procedure Retrieve_Dimension_Group(
94 p_commit IN varchar2 := FND_API.G_FALSE
95 ,p_Dim_Grp_Rec IN BSC_DIMENSION_GROUPS_PUB.Bsc_Dim_Group_Rec_Type
96 ,x_Dim_Grp_Rec IN OUT NOCOPY BSC_DIMENSION_GROUPS_PUB.Bsc_Dim_Group_Rec_Type
97 ,x_return_status OUT NOCOPY varchar2
98 ,x_msg_count OUT NOCOPY number
99 ,x_msg_data OUT NOCOPY varchar2
100 );
101
102 procedure Update_Dimension_Group(
103 p_commit IN varchar2 := FND_API.G_FALSE
104 ,p_Dim_Grp_Rec IN BSC_DIMENSION_GROUPS_PUB.Bsc_Dim_Group_Rec_Type
105 ,p_create_Dim_Levels IN BOOLEAN
106 ,x_return_status OUT NOCOPY varchar2
107 ,x_msg_count OUT NOCOPY number
108 ,x_msg_data OUT NOCOPY varchar2
109 );
110
111 procedure Update_Dimension_Group(
112 p_commit IN varchar2 := FND_API.G_FALSE
113 ,p_Dim_Grp_Rec IN BSC_DIMENSION_GROUPS_PUB.Bsc_Dim_Group_Rec_Type
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 Delete_Dimension_Group(
120 p_commit IN varchar2 := FND_API.G_FALSE
121 ,p_Dim_Grp_Rec IN BSC_DIMENSION_GROUPS_PUB.Bsc_Dim_Group_Rec_Type
122 ,x_return_status OUT NOCOPY varchar2
123 ,x_msg_count OUT NOCOPY number
124 ,x_msg_data OUT NOCOPY varchar2
125 );
126
127 procedure Create_Dim_Levels_In_Group(
128 p_commit IN varchar2 := FND_API.G_FALSE
129 ,p_Dim_Grp_Rec IN BSC_DIMENSION_GROUPS_PUB.Bsc_Dim_Group_Rec_Type
130 ,x_return_status OUT NOCOPY varchar2
131 ,x_msg_count OUT NOCOPY number
132 ,x_msg_data OUT NOCOPY varchar2
133 );
134
135 procedure Retrieve_Dim_Levels_In_Group(
136 p_commit IN varchar2 := FND_API.G_FALSE
137 ,p_Dim_Grp_Rec IN BSC_DIMENSION_GROUPS_PUB.Bsc_Dim_Group_Rec_Type
138 ,x_Dim_Grp_Rec IN OUT NOCOPY BSC_DIMENSION_GROUPS_PUB.Bsc_Dim_Group_Rec_Type
139 ,x_return_status OUT NOCOPY varchar2
140 ,x_msg_count OUT NOCOPY number
141 ,x_msg_data OUT NOCOPY varchar2
142 );
143
144 procedure Update_Dim_Levels_In_Group(
145 p_commit IN varchar2 := FND_API.G_FALSE
146 ,p_Dim_Grp_Rec IN BSC_DIMENSION_GROUPS_PUB.Bsc_Dim_Group_Rec_Type
147 ,x_return_status OUT NOCOPY varchar2
148 ,x_msg_count OUT NOCOPY number
149 ,x_msg_data OUT NOCOPY varchar2
150 );
151
152 procedure Delete_Dim_Levels_In_Group(
153 p_commit IN varchar2 := FND_API.G_FALSE
154 ,p_Dim_Grp_Rec IN BSC_DIMENSION_GROUPS_PUB.Bsc_Dim_Group_Rec_Type
155 ,x_return_status OUT NOCOPY varchar2
156 ,x_msg_count OUT NOCOPY number
157 ,x_msg_data OUT NOCOPY varchar2
158 );
159 -- Code added for ALL starts here
160 PROCEDURE Retrieve_Sys_Dim_Lvls_Grp_Wrap (
161 p_dim_level_shortname IN VARCHAR2
162 ,p_dim_shortname IN VARCHAR2
163 ,x_dim_group_id OUT NOCOPY NUMBER
164 ,x_dim_level_id OUT NOCOPY NUMBER
165 ,x_dim_level_index OUT NOCOPY NUMBER
166 ,x_total_flag OUT NOCOPY NUMBER
167 ,x_total_disp_name OUT NOCOPY VARCHAR2
168 ,x_dim_level_where_clause OUT NOCOPY VARCHAR2
169 ,x_comparison_flag OUT NOCOPY NUMBER
170 ,x_comp_disp_name OUT NOCOPY VARCHAR2
171 ,x_filter_column OUT NOCOPY VARCHAR2
172 ,x_filter_value OUT NOCOPY NUMBER
173 ,x_default_value OUT NOCOPY VARCHAR2
174 ,x_default_type OUT NOCOPY NUMBER
175 ,x_parent_in_total OUT NOCOPY NUMBER
176 ,x_no_items OUT NOCOPY NUMBER
177 ,x_return_status OUT NOCOPY VARCHAR2
178 ,x_msg_count OUT NOCOPY NUMBER
179 ,x_msg_data OUT NOCOPY VARCHAR2
180 );
181
182 PROCEDURE Set_Dim_lvl_grp_prop_wrap (
183 p_dim_level_shortname IN VARCHAR2
184 ,p_dim_shortname IN VARCHAR2
185 ,p_all_id IN NUMBER
186 ,x_return_status OUT NOCOPY VARCHAR2
187 ,x_msg_count OUT NOCOPY NUMBER
188 ,x_msg_data OUT NOCOPY VARCHAR2
189 );
190
191 FUNCTION Get_Next_Value(
192 p_table_name IN varchar2
193 ,p_column_name IN varchar2
194 )RETURN NUMBER;
195
196 PROCEDURE Translate_Dimension_Group
197 ( p_commit IN VARCHAR2 := FND_API.G_FALSE
198 ,p_Dim_Grp_Rec IN BSC_DIMENSION_GROUPS_PUB.Bsc_Dim_Group_Rec_Type
199 ,x_return_status OUT NOCOPY VARCHAR2
200 ,x_msg_count OUT NOCOPY NUMBER
201 ,x_msg_data OUT NOCOPY VARCHAR2
202 );
203 --
204 PROCEDURE get_unique_dim_group_name(
205 p_dim_group_name IN VARCHAR2
206 ,p_dim_group_short_name IN VARCHAR2
207 ,p_counter IN NUMBER
208 ,p_is_insert IN VARCHAR2 := 'Y'
209 ,x_dim_group_name OUT NOCOPY VARCHAR2
210 );
211 --
212
213 PROCEDURE load_dim_levels_in_group(
214 p_commit IN VARCHAR2 := FND_API.G_FALSE
215 ,p_Bsc_Pmf_Dim_Rec IN BSC_PMF_UI_API_PUB.Bsc_Pmf_Dim_Rec_Type
216 ,p_Dim_Grp_Rec IN BSC_DIMENSION_GROUPS_PUB.Bsc_Dim_Group_Rec_Type
217 ,x_return_status OUT NOCOPY VARCHAR2
218 ,x_msg_count OUT NOCOPY NUMBER
219 ,x_msg_data OUT NOCOPY VARCHAR2
220 );
221
222 --
223 PROCEDURE load_dimension_group (
224 p_commit IN VARCHAR2 := FND_API.G_FALSE
225 ,p_Dim_Grp_Rec IN BSC_DIMENSION_GROUPS_PUB.Bsc_Dim_Group_Rec_Type
226 ,x_return_status OUT NOCOPY VARCHAR2
227 ,x_msg_count OUT NOCOPY NUMBER
228 ,x_msg_data OUT NOCOPY VARCHAR2
229 ,p_force_mode IN BOOLEAN := FALSE
230 );
231
232 --
233 PROCEDURE ret_dimgrpid_fr_shname (
234 p_dim_short_name IN VARCHAR2
235 ,x_dim_grp_id OUT NOCOPY VARCHAR2
236 ,x_return_status OUT NOCOPY VARCHAR2
237 ,x_msg_count OUT NOCOPY NUMBER
238 ,x_msg_data OUT NOCOPY VARCHAR2
239 );
240 --
241 PROCEDURE dim_properties_default_values (
242 x_dim_level_index OUT NOCOPY NUMBER
243 ,x_total_flag OUT NOCOPY NUMBER
244 ,x_comparison_flag OUT NOCOPY NUMBER
245 ,x_filter_column OUT NOCOPY VARCHAR2
246 ,x_filter_value OUT NOCOPY NUMBER
247 ,x_default_value OUT NOCOPY VARCHAR2
248 ,x_default_type OUT NOCOPY NUMBER
249 ,x_parent_in_total OUT NOCOPY NUMBER
250 ,x_no_items OUT NOCOPY NUMBER
251 ,x_total_disp_name OUT NOCOPY VARCHAR2
252 ,x_comp_disp_name OUT NOCOPY VARCHAR2
253 );
254 --
255 -- ADDED TO SYNC THE LANGUAGE DATA FROM PMF TO BSC
256
257 procedure Translate_Dim_By_Given_Lang
258 ( p_commit IN VARCHAR2 := FND_API.G_FALSE
259 , p_Dim_Grp_Rec IN BSC_DIMENSION_GROUPS_PUB.Bsc_Dim_Group_Rec_Type
260 , x_return_status OUT NOCOPY VARCHAR2
261 , x_msg_count OUT NOCOPY NUMBER
262 , x_msg_data OUT NOCOPY VARCHAR2
263 );
264
265 end BSC_DIMENSION_GROUPS_PUB;
[Home] [Help]