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