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