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