1 package body BSC_DB_MEASURE_COLS_PKG as
2 /* $Header: BSCMSCOB.pls 120.1 2006/01/09 23:17:11 ppandey noship $ */
3
4 -- mdamle 09/03/03 - Validate measure col
5 function Validate_Measure_Col
6 ( p_measure_col IN VARCHAR2
7 ) return boolean;
8
9
10 procedure INSERT_ROW (
11 X_MEASURE_COL in VARCHAR2,
12 X_MEASURE_GROUP_ID in NUMBER,
13 X_PROJECTION_ID in NUMBER,
14 X_MEASURE_TYPE in NUMBER,
15 X_HELP in VARCHAR2
16 ) is
17 l_count number;
18 l_msg_data varchar2(2000);
19 l_msg_count number;
20 begin
21
22 -- mdamle 09/03/03 - Validate measure col
23 if not validate_measure_col(X_Measure_Col) then
24 FND_MESSAGE.SET_NAME('BSC','BSC_MEASURE_INV_SOURCE_NAME');
25 FND_MSG_PUB.ADD;
26 RAISE FND_API.G_EXC_ERROR;
27 end if;
28
29 -- mdamle 4/23/2003 - Measure Definer - Added default for measure_group_id and projection_id
30 insert into BSC_DB_MEASURE_COLS_TL (
31 MEASURE_COL,
32 HELP,
33 MEASURE_GROUP_ID,
34 PROJECTION_ID,
35 MEASURE_TYPE,
36 LANGUAGE,
37 SOURCE_LANG
38 ) select
39 X_MEASURE_COL,
40 nvl(X_HELP, 'Internal Column'),
41 nvl(X_MEASURE_GROUP_ID, -1),
42 nvl(X_PROJECTION_ID, 3),
43 nvl(X_MEASURE_TYPE, 1),
44 L.LANGUAGE_CODE,
45 USERENV('LANG')
46 from FND_LANGUAGES L
47 where L.INSTALLED_FLAG in ('I', 'B')
48 and not exists
49 (select NULL
50 from BSC_DB_MEASURE_COLS_TL T
51 where UPPER(T.MEASURE_COL) = UPPER(X_MEASURE_COL)
52 and T.LANGUAGE = L.LANGUAGE_CODE);
53
54 end INSERT_ROW;
55
56 procedure LOCK_ROW (
57 X_MEASURE_COL in VARCHAR2,
58 X_MEASURE_GROUP_ID in NUMBER,
59 X_PROJECTION_ID in NUMBER,
60 X_MEASURE_TYPE in NUMBER,
61 X_HELP in VARCHAR2
62 ) is
63 cursor c1 is select
64 MEASURE_GROUP_ID,
65 PROJECTION_ID,
66 MEASURE_TYPE,
67 HELP,
68 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
69 from BSC_DB_MEASURE_COLS_TL
70 where MEASURE_COL = X_MEASURE_COL
71 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
72 for update of MEASURE_COL nowait;
73 begin
74 for tlinfo in c1 loop
75 if (tlinfo.BASELANG = 'Y') then
76 if ( (tlinfo.HELP = X_HELP)
77 AND (tlinfo.MEASURE_GROUP_ID = X_MEASURE_GROUP_ID)
78 AND (tlinfo.PROJECTION_ID = X_PROJECTION_ID)
79 AND ((tlinfo.MEASURE_TYPE = X_MEASURE_TYPE)
80 OR ((tlinfo.MEASURE_TYPE is null) AND (X_MEASURE_TYPE is null)))
81 ) then
82 null;
83 else
84 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
85 app_exception.raise_exception;
86 end if;
87 end if;
88 end loop;
89 return;
90 end LOCK_ROW;
91
92 procedure TRANSLATE_ROW (
93 X_MEASURE_COL in VARCHAR2,
94 X_HELP in VARCHAR2
95 ) is
96 begin
97 update BSC_DB_MEASURE_COLS_TL set
98 HELP = nvl(X_HELP,HELP),
99 SOURCE_LANG = userenv('LANG')
100 where MEASURE_COL = X_MEASURE_COL
101 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
102
103 if (sql%notfound) then
104 raise no_data_found;
105 end if;
106 end TRANSLATE_ROW;
107
108 procedure UPDATE_ROW (
109 X_MEASURE_COL in VARCHAR2,
110 X_MEASURE_GROUP_ID in NUMBER,
111 X_PROJECTION_ID in NUMBER,
112 X_MEASURE_TYPE in NUMBER,
113 X_HELP in VARCHAR2
114 ) is
115 begin
116
117 TRANSLATE_ROW(X_MEASURE_COL, X_HELP);
118
119 update BSC_DB_MEASURE_COLS_TL set
120 MEASURE_GROUP_ID = X_MEASURE_GROUP_ID,
121 PROJECTION_ID = X_PROJECTION_ID,
122 MEASURE_TYPE = X_MEASURE_TYPE
123 where MEASURE_COL = X_MEASURE_COL;
124
125 if (sql%notfound) then
126 raise no_data_found;
127 end if;
128 end UPDATE_ROW;
129
130 procedure DELETE_ROW (
131 X_MEASURE_COL in VARCHAR2
132 ) is
133 begin
134 delete from BSC_DB_MEASURE_COLS_TL
135 where MEASURE_COL = X_MEASURE_COL;
136
137 if (sql%notfound) then
138 raise no_data_found;
139 end if;
140
141 end DELETE_ROW;
142
143 procedure ADD_LANGUAGE
144 is
145 begin
146 update BSC_DB_MEASURE_COLS_TL T set (
147 HELP
148 ) = (select
149 B.HELP
150 from BSC_DB_MEASURE_COLS_TL B
151 where B.MEASURE_COL = T.MEASURE_COL
152 and B.LANGUAGE = T.SOURCE_LANG)
153 where (
154 T.MEASURE_COL,
155 T.LANGUAGE
156 ) in (select
157 SUBT.MEASURE_COL,
158 SUBT.LANGUAGE
159 from BSC_DB_MEASURE_COLS_TL SUBB, BSC_DB_MEASURE_COLS_TL SUBT
160 where SUBB.MEASURE_COL = SUBT.MEASURE_COL
161 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
162 and (SUBB.HELP <> SUBT.HELP
163 ));
164
165 insert into BSC_DB_MEASURE_COLS_TL (
166 MEASURE_COL,
167 HELP,
168 MEASURE_GROUP_ID,
169 PROJECTION_ID,
170 MEASURE_TYPE,
171 LANGUAGE,
172 SOURCE_LANG
173 ) select
174 B.MEASURE_COL,
175 B.HELP,
176 B.MEASURE_GROUP_ID,
177 B.PROJECTION_ID,
178 B.MEASURE_TYPE,
179 L.LANGUAGE_CODE,
180 B.SOURCE_LANG
181 from BSC_DB_MEASURE_COLS_TL B, FND_LANGUAGES L
182 where L.INSTALLED_FLAG in ('I', 'B')
183 and B.LANGUAGE = userenv('LANG')
184 and not exists
185 (select NULL
186 from BSC_DB_MEASURE_COLS_TL T
187 where T.MEASURE_COL = B.MEASURE_COL
188 and T.LANGUAGE = L.LANGUAGE_CODE);
189 end ADD_LANGUAGE;
190
191
192 -- mdamle 09/03/03 - Validate measure col
193 function validate_measure_col
194 (p_measure_col IN VARCHAR2) return boolean is
195 l_valid boolean := TRUE;
196 l_asc number;
197 l_cursor number;
198 BEGIN
199 if p_measure_col is not null then
200
201 -- Check if first letter is a number or an underscore
202 l_asc := ascii(substr(p_measure_col, 1, 1));
203
204 if (l_asc >= 48 and l_asc <= 57) or (l_asc = 95) then
205 l_valid := false;
206 else
207 -- Valid values - numbers/alphabets/underscore
208 for i in 1..length(p_measure_col) loop
209 l_asc := ascii(substr(p_measure_col, i, 1));
210 If Not (l_asc >= 48 And l_asc <= 57) And
211 Not (l_asc >= 65 And l_asc <= 90) And
212 Not (l_asc >= 97 And l_asc <= 122) and
213 Not (l_asc = 95) Then
214 l_valid := false;
215 end if;
216 end loop;
217
218 if (l_valid) then
219 -- Parse to check if it's not a reserved word
220 l_cursor := dbms_sql.open_cursor;
221 dbms_sql.parse(l_cursor, 'select null ' || p_measure_col || ' from dual', dbms_sql.native);
222 dbms_sql.close_cursor(l_cursor);
223 end if;
224
225 end if;
226
227 else
228 l_valid := false;
229 end if;
230
231
232 return l_valid;
233 EXCEPTION
234 when others then return false;
235
236 END validate_measure_col;
237
238
239 -- added for Bug#3817894 (POSCO)
240 /**************************************************************************
241 Update_Measure_Column_Help
242
243 This API updates the help column in BSC_DB_MEASURE_COLS_TL table
244 for the appropriate session language.
245
246 Returns 'S' on successful update else returns 'E' or 'U' in x_Return_Status
247 with an appropriate error message readable from x_Msg_Data
248 ***************************************************************************/
249
250 PROCEDURE Update_Measure_Column_Help (
251 p_Measure_Col IN VARCHAR2
252 , p_Help IN VARCHAR2
253 , x_Return_Status OUT NOCOPY VARCHAR2
254 , x_Msg_Count OUT NOCOPY NUMBER
255 , x_Msg_Data OUT NOCOPY VARCHAR2
256 ) IS
257 BEGIN
258 SAVEPOINT UPDMEASCOLHELP;
259
260 x_Return_Status := FND_API.G_RET_STS_SUCCESS;
261 FND_MSG_PUB.INITIALIZE;
262
263 UPDATE BSC_DB_MEASURE_COLS_TL
264 SET HELP = NVL(p_Help, HELP),
265 SOURCE_LANG = USERENV('LANG')
266 WHERE MEASURE_COL = p_Measure_Col
267 AND USERENV('LANG') IN (LANGUAGE, SOURCE_LANG);
268
269 IF (SQL%NOTFOUND) THEN
270 FND_MESSAGE.SET_NAME('BSC','BSC_MUSER_DELETE_MESSAGE');
271 FND_MESSAGE.SET_TOKEN('TYPE', NVL(p_Measure_Col, BSC_APPS.Get_Lookup_Value('BSC_UI_KPIDESIGNER', 'SOURCE_COLUMNS')));
272 FND_MSG_PUB.ADD;
273 RAISE NO_DATA_FOUND;
274 END IF;
275
276 EXCEPTION
277 WHEN NO_DATA_FOUND THEN
278 ROLLBACK TO UPDMEASCOLHELP;
279 FND_MSG_PUB.Count_And_Get
280 ( p_encoded => FND_API.G_FALSE
281 , p_count => x_msg_count
282 , p_data => x_msg_data
283 );
284 x_Return_Status := FND_API.G_RET_STS_ERROR;
285
286 WHEN OTHERS THEN
287 ROLLBACK TO UPDMEASCOLHELP;
288 x_Return_Status := FND_API.G_RET_STS_UNEXP_ERROR;
289 x_msg_data := 'Error occured at BSC_DB_MEASURE_COLS_PKG.Update_Measure_Column_Help - ' || SQLERRM;
290 END Update_Measure_Column_Help;
291
292 end BSC_DB_MEASURE_COLS_PKG;
[Home] [Help]