1 package body BSC_SYS_DIM_LEVELS_PKG as
2 /* $Header: BSCSDMLB.pls 115.6 2003/02/12 14:29:16 adeulgao ship $ */
3 procedure INSERT_ROW (
4 X_ROWID in out NOCOPY VARCHAR2,
5 X_DIM_LEVEL_ID in NUMBER,
6 X_LEVEL_TABLE_NAME in VARCHAR2,
7 X_TABLE_TYPE in NUMBER,
8 X_LEVEL_PK_COL in VARCHAR2,
9 X_ABBREVIATION in VARCHAR2,
10 X_VALUE_ORDER_BY in NUMBER,
11 X_COMP_ORDER_BY in NUMBER,
12 X_CUSTOM_GROUP in NUMBER,
13 X_USER_KEY_SIZE in NUMBER,
14 X_DISP_KEY_SIZE in NUMBER,
15 X_NAME in VARCHAR2,
16 X_HELP in VARCHAR2,
17 X_TOTAL_DISP_NAME in VARCHAR2,
18 X_COMP_DISP_NAME in VARCHAR2
19 ) is
20 cursor C is select ROWID from BSC_SYS_DIM_LEVELS_B
21 where DIM_LEVEL_ID = X_DIM_LEVEL_ID
22 ;
23 begin
24 insert into BSC_SYS_DIM_LEVELS_B (
25 DIM_LEVEL_ID,
26 LEVEL_TABLE_NAME,
27 TABLE_TYPE,
28 LEVEL_PK_COL,
29 ABBREVIATION,
30 VALUE_ORDER_BY,
31 COMP_ORDER_BY,
32 CUSTOM_GROUP,
33 USER_KEY_SIZE,
34 DISP_KEY_SIZE
35 ) values (
36 X_DIM_LEVEL_ID,
37 X_LEVEL_TABLE_NAME,
38 X_TABLE_TYPE,
39 X_LEVEL_PK_COL,
40 X_ABBREVIATION,
41 X_VALUE_ORDER_BY,
42 X_COMP_ORDER_BY,
43 X_CUSTOM_GROUP,
44 X_USER_KEY_SIZE,
45 X_DISP_KEY_SIZE
46 );
47
48 insert into BSC_SYS_DIM_LEVELS_TL (
49 DIM_LEVEL_ID,
50 NAME,
51 HELP,
52 TOTAL_DISP_NAME,
53 COMP_DISP_NAME,
54 LANGUAGE,
55 SOURCE_LANG
56 ) select
57 X_DIM_LEVEL_ID,
58 X_NAME,
59 X_HELP,
60 X_TOTAL_DISP_NAME,
61 X_COMP_DISP_NAME,
62 L.LANGUAGE_CODE,
63 userenv('LANG')
64 from FND_LANGUAGES L
65 where L.INSTALLED_FLAG in ('I', 'B')
66 and not exists
67 (select NULL
68 from BSC_SYS_DIM_LEVELS_TL T
69 where T.DIM_LEVEL_ID = X_DIM_LEVEL_ID
70 and T.LANGUAGE = L.LANGUAGE_CODE);
71
72 open c;
73 fetch c into X_ROWID;
74 if (c%notfound) then
75 close c;
76 raise no_data_found;
77 end if;
78 close c;
79
80 end INSERT_ROW;
81
82 procedure LOCK_ROW (
83 X_DIM_LEVEL_ID in NUMBER,
84 X_LEVEL_TABLE_NAME in VARCHAR2,
85 X_TABLE_TYPE in NUMBER,
86 X_LEVEL_PK_COL in VARCHAR2,
87 X_ABBREVIATION in VARCHAR2,
88 X_VALUE_ORDER_BY in NUMBER,
89 X_COMP_ORDER_BY in NUMBER,
90 X_CUSTOM_GROUP in NUMBER,
91 X_USER_KEY_SIZE in NUMBER,
92 X_DISP_KEY_SIZE in NUMBER,
93 X_NAME in VARCHAR2,
94 X_HELP in VARCHAR2,
95 X_TOTAL_DISP_NAME in VARCHAR2,
96 X_COMP_DISP_NAME in VARCHAR2
97 ) is
98 cursor c is select
99 LEVEL_TABLE_NAME,
100 TABLE_TYPE,
101 LEVEL_PK_COL,
102 ABBREVIATION,
103 VALUE_ORDER_BY,
104 COMP_ORDER_BY,
105 CUSTOM_GROUP,
106 USER_KEY_SIZE,
107 DISP_KEY_SIZE
108 from BSC_SYS_DIM_LEVELS_B
109 where DIM_LEVEL_ID = X_DIM_LEVEL_ID
110 for update of DIM_LEVEL_ID nowait;
111 recinfo c%rowtype;
112
113 cursor c1 is select
114 NAME,
115 HELP,
116 TOTAL_DISP_NAME,
117 COMP_DISP_NAME,
118 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
119 from BSC_SYS_DIM_LEVELS_TL
120 where DIM_LEVEL_ID = X_DIM_LEVEL_ID
121 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
122 for update of DIM_LEVEL_ID nowait;
123 begin
124 open c;
125 fetch c into recinfo;
126 if (c%notfound) then
127 close c;
128 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
129 app_exception.raise_exception;
130 end if;
131 close c;
132 if ( (recinfo.LEVEL_TABLE_NAME = X_LEVEL_TABLE_NAME)
133 AND (recinfo.TABLE_TYPE = X_TABLE_TYPE)
134 AND (recinfo.LEVEL_PK_COL = X_LEVEL_PK_COL)
135 AND (recinfo.ABBREVIATION = X_ABBREVIATION)
136 AND (recinfo.VALUE_ORDER_BY = X_VALUE_ORDER_BY)
137 AND (recinfo.COMP_ORDER_BY = X_COMP_ORDER_BY)
138 AND (recinfo.CUSTOM_GROUP = X_CUSTOM_GROUP)
139 AND (recinfo.USER_KEY_SIZE = X_USER_KEY_SIZE)
140 AND (recinfo.DISP_KEY_SIZE = X_DISP_KEY_SIZE)
141 ) then
142 null;
143 else
144 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
145 app_exception.raise_exception;
146 end if;
147
148 for tlinfo in c1 loop
149 if (tlinfo.BASELANG = 'Y') then
150 if ( (tlinfo.NAME = X_NAME)
151 AND (tlinfo.HELP = X_HELP)
152 AND ((tlinfo.TOTAL_DISP_NAME = X_TOTAL_DISP_NAME)
153 OR ((tlinfo.TOTAL_DISP_NAME is null) AND (X_TOTAL_DISP_NAME is null)))
154 AND ((tlinfo.COMP_DISP_NAME = X_COMP_DISP_NAME)
155 OR ((tlinfo.COMP_DISP_NAME is null) AND (X_COMP_DISP_NAME is null)))
156 ) then
157 null;
158 else
159 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
160 app_exception.raise_exception;
161 end if;
162 end if;
163 end loop;
164 return;
165 end LOCK_ROW;
166
167 procedure UPDATE_ROW (
168 X_DIM_LEVEL_ID in NUMBER,
169 X_LEVEL_TABLE_NAME in VARCHAR2,
170 X_TABLE_TYPE in NUMBER,
171 X_LEVEL_PK_COL in VARCHAR2,
172 X_ABBREVIATION in VARCHAR2,
173 X_VALUE_ORDER_BY in NUMBER,
174 X_COMP_ORDER_BY in NUMBER,
175 X_CUSTOM_GROUP in NUMBER,
176 X_USER_KEY_SIZE in NUMBER,
177 X_DISP_KEY_SIZE in NUMBER,
178 X_NAME in VARCHAR2,
179 X_HELP in VARCHAR2,
180 X_TOTAL_DISP_NAME in VARCHAR2,
181 X_COMP_DISP_NAME in VARCHAR2
182 ) is
183 begin
184 update BSC_SYS_DIM_LEVELS_B set
185 LEVEL_TABLE_NAME = X_LEVEL_TABLE_NAME,
186 TABLE_TYPE = X_TABLE_TYPE,
187 LEVEL_PK_COL = X_LEVEL_PK_COL,
188 ABBREVIATION = X_ABBREVIATION,
189 VALUE_ORDER_BY = X_VALUE_ORDER_BY,
190 COMP_ORDER_BY = X_COMP_ORDER_BY,
191 CUSTOM_GROUP = X_CUSTOM_GROUP,
192 USER_KEY_SIZE = X_USER_KEY_SIZE,
193 DISP_KEY_SIZE = X_DISP_KEY_SIZE
194 where DIM_LEVEL_ID = X_DIM_LEVEL_ID;
195
196 if (sql%notfound) then
197 raise no_data_found;
198 end if;
199
200 update BSC_SYS_DIM_LEVELS_TL set
201 NAME = X_NAME,
202 HELP = X_HELP,
203 TOTAL_DISP_NAME = X_TOTAL_DISP_NAME,
204 COMP_DISP_NAME = X_COMP_DISP_NAME,
205 SOURCE_LANG = userenv('LANG')
206 where DIM_LEVEL_ID = X_DIM_LEVEL_ID
207 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
208
209 if (sql%notfound) then
210 raise no_data_found;
211 end if;
212 end UPDATE_ROW;
213
214 procedure DELETE_ROW (
215 X_DIM_LEVEL_ID in NUMBER
216 ) is
217 begin
218 delete from BSC_SYS_DIM_LEVELS_TL
219 where DIM_LEVEL_ID = X_DIM_LEVEL_ID;
220
221 if (sql%notfound) then
222 raise no_data_found;
223 end if;
224
225 delete from BSC_SYS_DIM_LEVELS_B
226 where DIM_LEVEL_ID = X_DIM_LEVEL_ID;
227
228 if (sql%notfound) then
229 raise no_data_found;
230 end if;
231 end DELETE_ROW;
232
233 procedure ADD_LANGUAGE
234 is
235 begin
236 delete from BSC_SYS_DIM_LEVELS_TL T
237 where not exists
238 (select NULL
239 from BSC_SYS_DIM_LEVELS_B B
240 where B.DIM_LEVEL_ID = T.DIM_LEVEL_ID
241 );
242
243 update BSC_SYS_DIM_LEVELS_TL T set (
244 NAME,
245 HELP,
246 TOTAL_DISP_NAME,
247 COMP_DISP_NAME
248 ) = (select
249 B.NAME,
250 B.HELP,
251 B.TOTAL_DISP_NAME,
252 B.COMP_DISP_NAME
253 from BSC_SYS_DIM_LEVELS_TL B
254 where B.DIM_LEVEL_ID = T.DIM_LEVEL_ID
255 and B.LANGUAGE = T.SOURCE_LANG)
256 where (
257 T.DIM_LEVEL_ID,
258 T.LANGUAGE
259 ) in (select
260 SUBT.DIM_LEVEL_ID,
261 SUBT.LANGUAGE
262 from BSC_SYS_DIM_LEVELS_TL SUBB, BSC_SYS_DIM_LEVELS_TL SUBT
263 where SUBB.DIM_LEVEL_ID = SUBT.DIM_LEVEL_ID
264 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
265 and (SUBB.NAME <> SUBT.NAME
266 or SUBB.HELP <> SUBT.HELP
267 or SUBB.TOTAL_DISP_NAME <> SUBT.TOTAL_DISP_NAME
268 or (SUBB.TOTAL_DISP_NAME is null and SUBT.TOTAL_DISP_NAME is not null)
269 or (SUBB.TOTAL_DISP_NAME is not null and SUBT.TOTAL_DISP_NAME is null)
270 or SUBB.COMP_DISP_NAME <> SUBT.COMP_DISP_NAME
271 or (SUBB.COMP_DISP_NAME is null and SUBT.COMP_DISP_NAME is not null)
272 or (SUBB.COMP_DISP_NAME is not null and SUBT.COMP_DISP_NAME is null)
273 ));
274
275 insert into BSC_SYS_DIM_LEVELS_TL (
276 DIM_LEVEL_ID,
277 NAME,
278 HELP,
279 TOTAL_DISP_NAME,
280 COMP_DISP_NAME,
281 LANGUAGE,
282 SOURCE_LANG
283 ) select
284 B.DIM_LEVEL_ID,
285 B.NAME,
286 B.HELP,
287 B.TOTAL_DISP_NAME,
288 B.COMP_DISP_NAME,
289 L.LANGUAGE_CODE,
290 B.SOURCE_LANG
291 from BSC_SYS_DIM_LEVELS_TL B, FND_LANGUAGES L
292 where L.INSTALLED_FLAG in ('I', 'B')
293 and B.LANGUAGE = userenv('LANG')
294 and not exists
295 (select NULL
296 from BSC_SYS_DIM_LEVELS_TL T
297 where T.DIM_LEVEL_ID = B.DIM_LEVEL_ID
298 and T.LANGUAGE = L.LANGUAGE_CODE);
299 end ADD_LANGUAGE;
300
301 end BSC_SYS_DIM_LEVELS_PKG;
[Home] [Help]