1 package body BSC_DB_MEASURE_GROUPS_PKG as
2 /* $Header: BSCMSGRB.pls 120.1 2005/08/02 03:09:19 ashankar noship $ */
3
4 -- mdamle 04/23/2003 - Measure Definer - Added sequence for Group Id
5 -- Changed x_measure_group_id to out parameter.
6 PROCEDURE INSERT_ROW
7 (
8 X_MEASURE_GROUP_ID OUT NOCOPY NUMBER
9 , X_HELP IN VARCHAR2
10 , X_SHORT_NAME IN VARCHAR2 := NULL
11 )IS
12 l_count NUMBER;
13 sql_stmt VARCHAR2(2000);
14 BEGIN
15
16 -- mdamle 07/25/2003 - Added check for duplicate name
17 FND_MSG_PUB.INITIALIZE;
18 SELECT COUNT(1)
19 INTO l_count
20 FROM bsc_db_measure_groups_tl
21 WHERE help = x_help;
22
23 IF (l_count > 0) THEN
24 FND_MESSAGE.SET_NAME('BSC','BSC_D_SOURCE_EXIST');
25 FND_MSG_PUB.ADD;
26 ELSE
27 SELECT bsc_db_measure_groups_s.nextVal
28 INTO X_MEASURE_GROUP_ID
29 FROM dual;
30
31 BSC_DB_MEASURE_GROUPS_PKG.Insert_Row_Values
32 (
33 x_Measure_group_id => X_MEASURE_GROUP_ID
34 , x_Help => X_HELP
35 , x_Short_name => X_SHORT_NAME
36 );
37
38 END IF;
39
40 END INSERT_ROW;
41
42 procedure LOCK_ROW (
43 X_MEASURE_GROUP_ID in NUMBER,
44 X_HELP in VARCHAR2
45 ) is
46 cursor c1 is select
47 HELP,
48 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
49 from BSC_DB_MEASURE_GROUPS_TL
50 where MEASURE_GROUP_ID = X_MEASURE_GROUP_ID
51 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
52 for update of MEASURE_GROUP_ID nowait;
53 begin
54 for tlinfo in c1 loop
55 if (tlinfo.BASELANG = 'Y') then
56 if ( (tlinfo.HELP = X_HELP)
57 ) then
58 null;
59 else
60 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
61 app_exception.raise_exception;
62 end if;
63 end if;
64 end loop;
65 return;
66 end LOCK_ROW;
67
68
69 PROCEDURE UPDATE_ROW
70 (
71 X_MEASURE_GROUP_ID IN NUMBER
72 , X_HELP IN VARCHAR2
73 , X_SHORT_NAME IN VARCHAR2:= NULL
74 )IS
75 BEGIN
76
77 UPDATE bsc_db_measure_groups_tl
78 SET help = X_HELP
79 , short_name = X_SHORT_NAME
80 , source_lang = USERENV('LANG')
81 WHERE measure_group_id = X_MEASURE_GROUP_ID
82 AND USERENV('LANG') IN (LANGUAGE, SOURCE_LANG);
83
84 IF (sql%notfound) THEN
85 RAISE NO_DATA_FOUND;
86 END IF;
87
88 END UPDATE_ROW;
89
90 procedure TRANSLATE_ROW (
91 X_MEASURE_GROUP_ID in NUMBER,
92 X_HELP in VARCHAR2
93 ) is
94 begin
95 update BSC_DB_MEASURE_GROUPS_TL set
96 HELP = nvl(X_HELP,HELP),
97 SOURCE_LANG = userenv('LANG')
98 where MEASURE_GROUP_ID = X_MEASURE_GROUP_ID
99 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
100
101 if (sql%notfound) then
102 raise no_data_found;
103 end if;
104 end TRANSLATE_ROW;
105
106
107 procedure DELETE_ROW (
108 X_MEASURE_GROUP_ID in NUMBER
109 ) is
110 begin
111 delete from BSC_DB_MEASURE_GROUPS_TL
112 where MEASURE_GROUP_ID = X_MEASURE_GROUP_ID;
113
114 if (sql%notfound) then
115 raise no_data_found;
116 end if;
117
118 end DELETE_ROW;
119
120
121 PROCEDURE ADD_LANGUAGE
122 IS
123 BEGIN
124
125 UPDATE bsc_db_measure_groups_tl t
126 SET (
127 help
128 ) = (
129 SELECT b.help
130 FROM bsc_db_measure_groups_tl b
131 WHERE b.Measure_group_id = t.Measure_group_id
132 AND b.language = t.Source_lang)
133 WHERE (
134 t.Measure_group_id,
135 t.language
136 ) IN (
137 SELECT subt.Measure_group_id
138 ,subt.language
139 FROM bsc_db_measure_groups_tl subb
140 , bsc_db_measure_groups_tl subt
141 WHERE subb.Measure_group_id = subt.Measure_group_id
142 AND subb.language = subt.Source_lang
143 AND (subb.help <> subt.help));
144
145
146 INSERT INTO bsc_db_measure_groups_tl
147 ( help
148 , measure_group_id
149 , language
150 , source_lang
151 , short_name
152 ) SELECT b.Help
153 , b.Measure_group_id
154 , l.Language_code
155 , b.Source_lang
156 , b.Short_name
157 FROM bsc_db_measure_groups_tl b
158 , fnd_languages l
159 WHERE l.Installed_flag IN ('I', 'B')
160 AND b.language = USERENV('LANG')
161 AND NOT EXISTS (
162 SELECT NULL
163 FROM bsc_db_measure_groups_tl t
164 WHERE t.Measure_group_id = b.Measure_group_id
165 AND t.language = l.language_code);
166
167 END ADD_LANGUAGE;
168
169 /*************************************************
170 Procedure : Insert_Default_Meas_Row
171 Description : This procedure is called from bscmsgrp.lct
172 file.Pls don't modify this procedure.
173 Input : Default measure group id <-1>
174 : <DEFAULT> help
175 Created BY : ashankar 27-JUL-2005
176 /**************************************************/
177
178 PROCEDURE Insert_Default_Meas_Row
179 (
180 x_Measure_group_id IN NUMBER
181 , x_Help IN VARCHAR2
182 )IS
183 BEGIN
184
185 BSC_DB_MEASURE_GROUPS_PKG.Insert_Row_Values
186 (
187 x_Measure_group_id => x_Measure_group_id
188 , x_Help => x_Help
189 , x_Short_name => NULL
190 );
191 END Insert_Default_Meas_Row;
192
193 /*************************************************************************************************
194 Procedure : Insert_Row_Values
195 Description : This procedure insert values into bsc_db_measure_groups_tl table.
196 Input : Measure group Id
197 : help
198 : short_name
199 Created BY : ashankar 27-JUL-2005
200 Note : Removed the dynamic SQL because BSC 5.2 MD/DD xdf files are included with BIS 4.0.9
201 Verified BIS ARU 4122391 it contains bsc_db_measure_groups_tl.xdf version 115.3
202 So SHORT_NAME column will always be there.
203 /***************************************************************************************************/
204
205 PROCEDURE Insert_Row_Values
206 (
207 x_Measure_group_id IN NUMBER
208 , x_Help IN VARCHAR2
209 , x_Short_name IN VARCHAR2
210 )IS
211 BEGIN
212 INSERT INTO bsc_db_measure_groups_tl
213 ( short_name
214 , help
215 , measure_group_id
216 ,language
217 ,source_lang
218 ) SELECT x_Short_name
219 , x_Help
220 , x_Measure_group_id
221 , l.LANGUAGE_CODE
222 , USERENV('LANG')
223 FROM fnd_languages l
224 WHERE l.installed_flag IN ('I', 'B')
225 AND NOT EXISTS (
226 SELECT NULL
227 FROM bsc_db_measure_groups_tl t
228 WHERE t.measure_group_id = x_Measure_group_id
229 AND t.language = l.language_code);
230
231 END Insert_Row_Values;
232
233
234 END BSC_DB_MEASURE_GROUPS_PKG;
[Home] [Help]