[Home] [Help]
PACKAGE BODY: APPS.IEC_P_RES_GRP_CAPS_PKG
Source
1 package body IEC_P_RES_GRP_CAPS_PKG as
2 /* $Header: IECHRGCB.pls 115.12 2004/08/06 15:40:56 minwang ship $ */
3 procedure INSERT_ROW (
4 X_ROWID in out NOCOPY VARCHAR2,
5 X_RES_GROUP_CAP_ID in NUMBER,
6 X_OBJECT_VERSION_NUMBER in NUMBER,
7 X_CAP_CODE in VARCHAR2,
8 X_VALUE_TYPE in VARCHAR2,
9 X_VALUE_LENGTH in VARCHAR2,
10 X_CAP_NAME in VARCHAR2,
11 X_CAP_DESCRIPTION in VARCHAR2,
12 X_CREATION_DATE in DATE,
13 X_CREATED_BY in NUMBER,
14 X_LAST_UPDATE_DATE in DATE,
15 X_LAST_UPDATED_BY in NUMBER,
16 X_LAST_UPDATE_LOGIN in NUMBER
17 ) is
18 cursor C is select ROWID from IEC_P_RES_GRP_CAPS_B
19 where RES_GROUP_CAP_ID = X_RES_GROUP_CAP_ID
20 ;
21 begin
22 insert into IEC_P_RES_GRP_CAPS_B (
23 RES_GROUP_CAP_ID,
24 OBJECT_VERSION_NUMBER,
25 CAP_CODE,
26 VALUE_TYPE,
27 VALUE_LENGTH,
28 CREATION_DATE,
29 CREATED_BY,
30 LAST_UPDATE_DATE,
31 LAST_UPDATED_BY,
32 LAST_UPDATE_LOGIN
33 ) values (
34 X_RES_GROUP_CAP_ID,
35 X_OBJECT_VERSION_NUMBER,
36 X_CAP_CODE,
37 X_VALUE_TYPE,
38 X_VALUE_LENGTH,
39 X_CREATION_DATE,
40 X_CREATED_BY,
41 X_LAST_UPDATE_DATE,
42 X_LAST_UPDATED_BY,
43 X_LAST_UPDATE_LOGIN
44 );
45
46 insert into IEC_P_RES_GRP_CAPS_TL (
47 RES_GROUP_CAP_ID,
48 CREATED_BY,
49 CREATION_DATE,
50 LAST_UPDATED_BY,
51 LAST_UPDATE_DATE,
52 LAST_UPDATE_LOGIN,
53 CAP_NAME,
54 CAP_DESCRIPTION,
55 LANGUAGE,
56 SOURCE_LANG
57 ) select
58 X_RES_GROUP_CAP_ID,
59 X_CREATED_BY,
60 X_CREATION_DATE,
61 X_LAST_UPDATED_BY,
62 X_LAST_UPDATE_DATE,
63 X_LAST_UPDATE_LOGIN,
64 X_CAP_NAME,
65 X_CAP_DESCRIPTION,
66 L.LANGUAGE_CODE,
67 userenv('LANG')
68 from FND_LANGUAGES L
69 where L.INSTALLED_FLAG in ('I', 'B')
70 and not exists
71 (select NULL
72 from IEC_P_RES_GRP_CAPS_TL T
73 where T.RES_GROUP_CAP_ID = X_RES_GROUP_CAP_ID
74 and T.LANGUAGE = L.LANGUAGE_CODE);
75 open c;
76 fetch c into X_ROWID;
77 if (c%notfound) then
78 close c;
79 raise no_data_found;
80 end if;
81 close c;
82
83
84 end INSERT_ROW;
85
86
87 procedure LOCK_ROW (
88 X_RES_GROUP_CAP_ID in NUMBER,
89 X_OBJECT_VERSION_NUMBER in NUMBER,
90 X_CAP_CODE in VARCHAR2,
91 X_VALUE_TYPE in VARCHAR2,
92 X_VALUE_LENGTH in VARCHAR2,
93 X_CAP_NAME in VARCHAR2,
94 X_CAP_DESCRIPTION in VARCHAR2
95 ) is
96 cursor c is select
97 OBJECT_VERSION_NUMBER,
98 CAP_CODE,
99 VALUE_TYPE,
100 VALUE_LENGTH
101 from IEC_P_RES_GRP_CAPS_B
102 where RES_GROUP_CAP_ID = X_RES_GROUP_CAP_ID
103 for update of RES_GROUP_CAP_ID nowait;
104 recinfo c%rowtype;
105
106 cursor c1 is select
107 CAP_NAME,
108 CAP_DESCRIPTION,
109 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
110 from IEC_P_RES_GRP_CAPS_TL
111 where RES_GROUP_CAP_ID = X_RES_GROUP_CAP_ID
112 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
113 for update of RES_GROUP_CAP_ID nowait;
114 begin
115 open c;
116 fetch c into recinfo;
117 if (c%notfound) then
118 close c;
119 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
120 app_exception.raise_exception;
121 end if;
122 close c;
123 if ( (recinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER)
124 AND (recinfo.CAP_CODE = X_CAP_CODE)
125 AND ((recinfo.VALUE_TYPE = X_VALUE_TYPE)
126 OR ((recinfo.VALUE_TYPE is null) AND (X_VALUE_TYPE is null)))
127 AND ((recinfo.VALUE_LENGTH = X_VALUE_LENGTH)
128 OR ((recinfo.VALUE_LENGTH is null) AND (X_VALUE_LENGTH is null)))
129 ) then
130 null;
131 else
132 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
133 app_exception.raise_exception;
134 end if;
135
136 for tlinfo in c1 loop
137 if (tlinfo.BASELANG = 'Y') then
138 if ( (tlinfo.CAP_NAME = X_CAP_NAME)
139 AND ((tlinfo.CAP_DESCRIPTION = X_CAP_DESCRIPTION)
140 OR ((tlinfo.CAP_DESCRIPTION is null) AND (X_CAP_DESCRIPTION is null)))
141 ) then
142 null;
143 else
144 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
145 app_exception.raise_exception;
146 end if;
147 end if;
148 end loop;
149 return;
150 end LOCK_ROW;
151
152 procedure UPDATE_ROW (
153 X_RES_GROUP_CAP_ID in NUMBER,
154 X_OBJECT_VERSION_NUMBER in NUMBER,
155 X_CAP_CODE in VARCHAR2,
156 X_VALUE_TYPE in VARCHAR2,
157 X_VALUE_LENGTH in VARCHAR2,
158 X_CAP_NAME in VARCHAR2,
159 X_CAP_DESCRIPTION in VARCHAR2,
160 X_LAST_UPDATE_DATE in DATE,
161 X_LAST_UPDATED_BY in NUMBER,
162 X_LAST_UPDATE_LOGIN in NUMBER
163 ) is
164 begin
165 update IEC_P_RES_GRP_CAPS_B set
166 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
167 CAP_CODE = X_CAP_CODE,
168 VALUE_TYPE = X_VALUE_TYPE,
169 VALUE_LENGTH = X_VALUE_LENGTH,
170 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
171 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
172 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
173 where RES_GROUP_CAP_ID = X_RES_GROUP_CAP_ID;
174
175 if (sql%notfound) then
176 raise no_data_found;
177 end if;
178
179 update IEC_P_RES_GRP_CAPS_TL set
180 CAP_NAME = X_CAP_NAME,
181 CAP_DESCRIPTION = X_CAP_DESCRIPTION,
182 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
183 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
184 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
185 SOURCE_LANG = userenv('LANG')
186 where RES_GROUP_CAP_ID = X_RES_GROUP_CAP_ID
187 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
188
189 if (sql%notfound) then
190 raise no_data_found;
191 end if;
192 end UPDATE_ROW;
193
194 procedure DELETE_ROW (
195 X_RES_GROUP_CAP_ID in NUMBER
196 ) is
197 begin
198 delete from IEC_P_RES_GRP_CAPS_TL
199 where RES_GROUP_CAP_ID = X_RES_GROUP_CAP_ID;
200
201 if (sql%notfound) then
202 raise no_data_found;
203 end if;
204
205 delete from IEC_P_RES_GRP_CAPS_B
206 where RES_GROUP_CAP_ID = X_RES_GROUP_CAP_ID;
207 if (sql%notfound) then
208 raise no_data_found;
209 end if;
210 end DELETE_ROW;
211
212 procedure LOAD_ROW (
213 X_RES_GROUP_CAP_ID in NUMBER,
214 X_CAP_CODE in VARCHAR2,
215 X_VALUE_TYPE in VARCHAR2,
216 X_VALUE_LENGTH in VARCHAR2,
217 X_CAP_NAME in VARCHAR2,
218 X_CAP_DESCRIPTION in VARCHAR2,
219 X_OWNER in VARCHAR2
220 ) is
221
222 USER_ID NUMBER := 0;
223 ROW_ID VARCHAR2(500);
224 begin
225 if (X_OWNER = 'SEED') then
226 USER_ID := 1;
227 end if;
228 UPDATE_ROW ( X_RES_GROUP_CAP_ID
229 , 0
230 , X_CAP_CODE
231 , X_VALUE_TYPE
232 , X_VALUE_LENGTH
233 , X_CAP_NAME
234 , X_CAP_DESCRIPTION
235 , SYSDATE
236 , USER_ID
237 , 0);
238
239 exception
240 when no_data_found then
241 INSERT_ROW (
242 ROW_ID
243 , X_RES_GROUP_CAP_ID
244 , 0
245 , X_CAP_CODE
246 , X_VALUE_TYPE
247 , X_VALUE_LENGTH
248 , X_CAP_NAME
249 , X_CAP_DESCRIPTION
250 , SYSDATE
251 , USER_ID
252 , SYSDATE
253 , USER_ID
254 , 0);
255
256 end LOAD_ROW;
257
258
259 procedure ADD_LANGUAGE
260 is
261 begin
262 delete from IEC_P_RES_GRP_CAPS_TL T
263 where not exists
264 (select NULL
265 from IEC_P_RES_GRP_CAPS_B B
266 where B.RES_GROUP_CAP_ID = T.RES_GROUP_CAP_ID
267 );
268
269 update IEC_P_RES_GRP_CAPS_TL T set (
270 CAP_NAME,
271 CAP_DESCRIPTION
272 ) = (select
273 B.CAP_NAME,
274 B.CAP_DESCRIPTION
275 from IEC_P_RES_GRP_CAPS_TL B
276 where B.RES_GROUP_CAP_ID = T.RES_GROUP_CAP_ID
277 and B.LANGUAGE = T.SOURCE_LANG)
278 where (
279 T.RES_GROUP_CAP_ID,
280 T.LANGUAGE
281 ) in (select
282 SUBT.RES_GROUP_CAP_ID,
283 SUBT.LANGUAGE
284 from IEC_P_RES_GRP_CAPS_TL SUBB, IEC_P_RES_GRP_CAPS_TL SUBT
285 where SUBB.RES_GROUP_CAP_ID = SUBT.RES_GROUP_CAP_ID
286 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
287 and (SUBB.CAP_NAME <> SUBT.CAP_NAME
288 or SUBB.CAP_DESCRIPTION <> SUBT.CAP_DESCRIPTION
289 or (SUBB.CAP_DESCRIPTION is null and SUBT.CAP_DESCRIPTION is not null)
290 or (SUBB.CAP_DESCRIPTION is not null and SUBT.CAP_DESCRIPTION is null)
291 ));
292
293 insert into IEC_P_RES_GRP_CAPS_TL (
294 RES_GROUP_CAP_ID,
295 CREATED_BY,
296 CREATION_DATE,
297 LAST_UPDATED_BY,
298 LAST_UPDATE_DATE,
299 LAST_UPDATE_LOGIN,
300 CAP_NAME,
301 CAP_DESCRIPTION,
302 LANGUAGE,
303 SOURCE_LANG
304 ) select /*+ ORDERED */
305 B.RES_GROUP_CAP_ID,
306 B.CREATED_BY,
307 B.CREATION_DATE,
308 B.LAST_UPDATED_BY,
309 B.LAST_UPDATE_DATE,
310 B.LAST_UPDATE_LOGIN,
311 B.CAP_NAME,
312 B.CAP_DESCRIPTION,
313 L.LANGUAGE_CODE,
314 B.SOURCE_LANG
315 from IEC_P_RES_GRP_CAPS_TL B, FND_LANGUAGES L
316 where L.INSTALLED_FLAG in ('I', 'B')
317 and B.LANGUAGE = userenv('LANG')
318 and not exists
319 (select NULL
320 from IEC_P_RES_GRP_CAPS_TL T
321 where T.RES_GROUP_CAP_ID = B.RES_GROUP_CAP_ID
322 and T.LANGUAGE = L.LANGUAGE_CODE);
323 end ADD_LANGUAGE;
324
325 procedure TRANSLATE_ROW (
326 X_RES_GROUP_CAP_ID in NUMBER,
327 X_CAP_NAME in VARCHAR2,
328 X_CAP_DESCRIPTION in VARCHAR2,
329 X_OWNER in VARCHAR2
330 ) is
331 begin
332
333 -- only UPDATE rows that have not been altered by user
334
335 update IEC_P_RES_GRP_CAPS_TL set
336 CAP_NAME = X_CAP_NAME,
337 SOURCE_LANG = userenv('LANG'),
338 CAP_DESCRIPTION = X_CAP_DESCRIPTION,
339 LAST_UPDATE_DATE = SYSDATE,
340 LAST_UPDATED_BY = DECODE(X_OWNER, 'SEED', 1, 0),
341 LAST_UPDATE_LOGIN = 0
342 where RES_GROUP_CAP_ID = X_RES_GROUP_CAP_ID
343 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
344
345 end TRANSLATE_ROW;
346
347 end IEC_P_RES_GRP_CAPS_PKG;