[Home] [Help]
PACKAGE BODY: APPS.HZ_TRANSFORMATIONS_PKG
Source
1 PACKAGE BODY HZ_TRANSFORMATIONS_PKG as
2 /*$Header: ARHDTFTB.pls 120.3 2006/02/10 01:18:25 schitrap noship $ */
3
4 PROCEDURE Insert_Row (
5 x_transformation_id IN OUT NOCOPY NUMBER,
6 x_transformation_name IN VARCHAR2,
7 x_description IN VARCHAR2,
8 x_procedure_name IN VARCHAR2,
9 x_object_version_number IN NUMBER
10 ) IS
11
12
13 CURSOR C2 IS SELECT HZ_TRANSFORMATIONS_S.nextval FROM sys.dual ;
14 l_success VARCHAR2(1) := 'N';
15 l_object_version_number NUMBER;
16 l_proc_valid VARCHAR2(10);
17 BEGIN
18
19 l_proc_valid:= HZ_DQM_SEARCH_UTIL.validate_trans_proc(x_procedure_name);
20 if(nvl(l_proc_valid,'INVALID') = 'INVALID') then
21 fnd_message.set_name('AR', 'HZ_DQM_INVALID_TRANS_PROC');
22 fnd_message.set_token('PROC',x_procedure_name);
23 app_exception.raise_exception;
24 end if;
25 l_object_version_number := x_object_version_number;
26 IF ( x_transformation_id IS NULL) OR (x_transformation_id = FND_API.G_MISS_NUM) THEN
27 OPEN C2;
28 FETCH C2 INTO x_transformation_id;
29 CLOSE C2;
30 END IF;
31 IF ( l_object_version_number IS NULL) OR (l_object_version_number = FND_API.G_MISS_NUM) THEN
32 l_object_version_number :=1;
33 END IF;
34
35 WHILE l_success = 'N' LOOP
36 BEGIN
37 INSERT INTO HZ_TRANSFORMATIONS_B(
38 transformation_id,
39 procedure_name,
40 object_version_number,
41 created_by,
42 creation_date,
43 last_updated_by,
44 last_update_login,
45 last_update_date
46 )
47 VALUES (
48 x_transformation_id,
49 DECODE(x_procedure_name,FND_API.G_MISS_CHAR, NULL,x_procedure_name),
50 l_object_version_number,
51 hz_utility_v2pub.created_by,
52 hz_utility_v2pub.creation_date,
53 hz_utility_v2pub.last_updated_by,
54 hz_utility_v2pub.last_update_login,
55 hz_utility_v2pub.last_update_date
56 );
57 l_success := 'Y';
58 EXCEPTION WHEN DUP_VAL_ON_INDEX THEN
59 IF INSTRB( SQLERRM, 'HZ_TRANSFORMATIONS_B_U1' ) <> 0 THEN
60 DECLARE
61 l_count NUMBER;
62 l_dummy VARCHAR2(1);
63 BEGIN
64 l_count := 1;
65 WHILE l_count > 0 LOOP
66 SELECT HZ_TRANSFORMATIONS_S.nextval
67 into x_transformation_id FROM sys.dual;
68 BEGIN
69 SELECT 'Y' INTO l_dummy
70 FROM HZ_TRANSFORMATIONS_B
71 WHERE TRANSFORMATION_ID = X_TRANSFORMATION_ID;
72 l_count := 1;
73 EXCEPTION WHEN NO_DATA_FOUND THEN
74 l_count := 0;
75 END;
76 END LOOP;
77 END;
78 END IF;
79 END;
80 END LOOP;
81
82 INSERT INTO HZ_TRANSFORMATIONS_TL (
83 transformation_id,
84 transformation_name,
85 description,
86 language,
87 source_lang,
88 object_version_number,
89 created_by,
90 creation_date,
91 last_updated_by,
92 last_update_login,
93 last_update_date
94 )
95 SELECT
96 x_transformation_id,
97 x_transformation_name,
98 x_description,
99 l.language_code,
100 userenv('LANG'),
101 l_object_version_number,
102 hz_utility_v2pub.created_by,
103 hz_utility_v2pub.creation_date,
104 hz_utility_v2pub.last_updated_by,
105 hz_utility_v2pub.last_update_login,
106 hz_utility_v2pub.last_update_date
107 FROM FND_LANGUAGES L
108 WHERE L.INSTALLED_FLAG in ('I', 'B')
109 AND NOT EXISTS (select NULL from HZ_TRANSFORMATIONS_TL T
110 where T.TRANSFORMATION_ID = X_TRANSFORMATION_ID
111 and T.LANGUAGE = L.LANGUAGE_CODE
112 );
113
114 END Insert_Row;
115
116
117 procedure Update_Row (
118 x_transformation_id IN NUMBER,
119 x_transformation_name IN VARCHAR2,
120 x_description IN VARCHAR2,
121 x_procedure_name IN VARCHAR2,
122 x_object_version_number IN OUT NOCOPY NUMBER
123 )
124 IS
125 p_object_version_number NUMBER ;
126 l_proc_valid VARCHAR2(10);
127 BEGIN
128 p_object_version_number := nvl(x_object_version_number, 1) + 1;
129 l_proc_valid:= HZ_DQM_SEARCH_UTIL.validate_trans_proc(x_procedure_name);
130 if(nvl(l_proc_valid,'INVALID') = 'INVALID') then
131 fnd_message.set_name('AR', 'HZ_DQM_INVALID_TRANS_PROC');
132 fnd_message.set_token('PROC',x_procedure_name);
133 app_exception.raise_exception;
134 end if;
135 UPDATE HZ_TRANSFORMATIONS_B set
136 procedure_name = decode(x_procedure_name,null,procedure_name,fnd_api.g_miss_char,NULL,x_procedure_name),
137 object_version_number = p_object_version_number,
138 last_updated_by = hz_utility_v2pub.last_updated_by,
139 last_update_login = hz_utility_v2pub.last_update_login,
140 last_update_date = hz_utility_v2pub.last_update_date
141 where transformation_id = x_transformation_id;
142
143 if (sql%notfound) then
144 raise no_data_found;
145 end if;
146
147 update HZ_TRANSFORMATIONS_TL set
148 transformation_name = decode(x_transformation_name,null,transformation_name,fnd_api.g_miss_char,NULL,x_transformation_name),
149 description = decode(x_description,null,description,fnd_api.g_miss_char,NULL,x_description),
150 object_version_number = p_object_version_number,
151 last_updated_by = hz_utility_v2pub.last_updated_by,
152 last_update_login = hz_utility_v2pub.last_update_login,
153 last_update_date = hz_utility_v2pub.last_update_date,
154 source_lang = userenv('LANG')
155 where transformation_id = x_transformation_id
156 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
157 x_object_version_number := p_object_version_number ;
158
159 if (sql%notfound) then
160 raise no_data_found;
161 end if;
162
163 END ;
164
165
166 procedure Delete_Row (
167 x_transformation_id in NUMBER
168 )
169 IS
170 BEGIN
171 delete from HZ_TRANSFORMATIONS_B
172 where transformation_id = x_transformation_id ;
173
174 if (sql%notfound) then
175 raise no_data_found;
176 end if;
177
178 delete from HZ_TRANSFORMATIONS_TL
179 where transformation_id = x_transformation_id ;
180
181 if (sql%notfound) then
182 raise no_data_found;
183 end if;
184 END ;
185
186 procedure Lock_Row (
187 x_transformation_id in NUMBER,
188 x_object_version_number in NUMBER
189 )
190 IS
191 cursor c is select object_version_number
192 from HZ_TRANSFORMATIONS_TL
193 where transformation_id = x_transformation_id
194 and language = userenv('lang');
195 cursor c1 is select object_version_number
196 from HZ_TRANSFORMATIONS_B
197 where transformation_id = x_transformation_id
198 for update of transformation_id nowait;
199
200 cursor c2 is select object_version_number
201 from HZ_TRANSFORMATIONS_TL
202 where transformation_id = x_transformation_id
203 for update of transformation_id nowait;
204
205 recinfo c%rowtype;
206
207 BEGIN
208 -- Lock the records
209 open c1;
210 close c1;
211 open c2;
212 close c2;
213
214 open c;
215 fetch c into recinfo;
216 if (c%notfound) then
217 close c;
218 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
219 app_exception.raise_exception;
220 end if;
221 close c;
222
223 if(
224 ( recinfo.object_version_number IS NULL AND x_object_version_number IS NULL )
225 OR ( recinfo.object_version_number IS NOT NULL AND
226 x_object_version_number IS NOT NULL AND
227 recinfo.object_version_number = x_object_version_number )
228 ) then
229 null;
230 else
231 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
232 app_exception.raise_exception;
233 end if;
234
235 return;
236
237 END Lock_Row ;
238
239
240 procedure Add_Language
241 IS
242 BEGIN
243 delete from HZ_TRANSFORMATIONS_TL T
244 where not exists
245 (select NULL
246 from HZ_TRANSFORMATIONS_B B
247 where B.transformation_id = T.transformation_id
248 );
249
250 update HZ_TRANSFORMATIONS_TL T set (
251 transformation_name,
252 description,
253 object_version_number
254 ) = (select B.transformation_name,B.description,
255 NVL(T.object_version_number, 1) + 1
256 from HZ_TRANSFORMATIONS_TL B
257 where B.transformation_id = T.transformation_id
258 and B.language = T.source_lang
259 )
260 where (T.transformation_id,T.language) in (select SUBT.transformation_id,SUBT.language
261 from HZ_TRANSFORMATIONS_TL SUBB, HZ_TRANSFORMATIONS_TL SUBT
262 where SUBB.transformation_id = SUBT.transformation_id
263 and SUBB.language = SUBT.source_lang
264 and (SUBB.transformation_name <> SUBT.transformation_name
265 or SUBB.description <> SUBT.description
266 or (SUBB.description is null and SUBT.description is not null)
267 or (SUBB.description is not null and SUBT.description is null)
268 )
269 );
270
271 insert into HZ_TRANSFORMATIONS_TL (
272 LAST_UPDATE_DATE,
273 CREATION_DATE,
274 CREATED_BY,
275 DESCRIPTION,
276 TRANSFORMATION_NAME,
277 TRANSFORMATION_ID,
278 LAST_UPDATED_BY,
279 LAST_UPDATE_LOGIN,
280 LANGUAGE,
281 SOURCE_LANG,
282 OBJECT_VERSION_NUMBER
283 ) select
284 B.LAST_UPDATE_DATE,
285 B.CREATION_DATE,
286 B.CREATED_BY,
287 B.DESCRIPTION,
288 B.TRANSFORMATION_NAME,
289 B.TRANSFORMATION_ID,
290 B.LAST_UPDATED_BY,
291 B.LAST_UPDATE_LOGIN,
292 L.LANGUAGE_CODE,
293 B.SOURCE_LANG,
294 1
295 from HZ_TRANSFORMATIONS_TL B, FND_LANGUAGES L
296 where L.INSTALLED_FLAG in ('I', 'B')
297 and B.LANGUAGE = userenv('LANG')
298 and L.LANGUAGE_CODE <> B.LANGUAGE
299 and not exists
300 (select NULL
301 from HZ_TRANSFORMATIONS_TL T
302 where T.TRANSFORMATION_ID = B.TRANSFORMATION_ID
303 and T.language = L.language_code );
304 END ;
305
306
307 procedure Load_Row (
308 x_transformation_id IN OUT NOCOPY NUMBER,
309 x_transformation_name IN VARCHAR2,
310 x_description IN VARCHAR2,
311 x_procedure_name IN VARCHAR2,
312 x_object_version_number IN NUMBER,
313 x_last_updated_by IN number,
314 x_last_update_login IN number,
315 x_last_update_date IN date,
316 x_owner IN VARCHAR2,
317 x_custom_mode IN VARCHAR2
318 )
319 IS
320 l_f_luby number; -- entity owner in file
321 l_f_ludate date; -- entity update date in file
322 l_db_luby number; -- entity owner in db
323 l_db_ludate date; -- entity update date in db
324 l_object_version_number number ;
325
326 begin
327
328 -- Translate owner to file_last_updated_by
329 IF (x_owner = 'SEED')
330 THEN
331 l_f_luby := 1;
332 ELSE
333 l_f_luby := 0;
334 END IF ;
335
336 -- Get last update date of ldt entity
337 l_f_ludate := nvl(x_last_update_date, sysdate);
338
339 l_object_version_number := x_object_version_number ;
340
341 BEGIN
342 select LAST_UPDATED_BY, LAST_UPDATE_DATE
343 into l_db_luby, l_db_ludate
344 from HZ_TRANSFORMATIONS_B
345 where transformation_id = x_transformation_id ;
346
347 -- Update record, honoring customization mode.
348 -- Record should be updated only if:
349 -- a. CUSTOM_MODE = FORCE, or
350 -- b. file owner is CUSTOM, db owner is SEED
351 -- c. owners are the same, and file_date > db_date
352
353 IF ((x_custom_mode = 'FORCE') or
354 ((l_f_luby = 0) and (l_db_luby = 1)) or
355 ((l_f_luby = l_db_luby) and (l_f_ludate > l_db_ludate)))
356 THEN
357
358 HZ_TRANSFORMATIONS_PKG.UPDATE_ROW(
359 x_transformation_id => x_transformation_id,
360 x_transformation_name => x_transformation_name,
361 x_description => x_description,
362 x_procedure_name => x_procedure_name,
363 x_object_version_number => l_object_version_number
364 );
365 END IF ;
366
367 EXCEPTION
368 WHEN NO_DATA_FOUND
369 THEN
370 HZ_TRANSFORMATIONS_PKG.INSERT_ROW(
371 x_transformation_id => x_transformation_id,
372 x_transformation_name => x_transformation_name,
373 x_description => x_description,
374 x_procedure_name => x_procedure_name,
375 x_object_version_number => x_object_version_number
376 );
377
378 END ;
379
380 END ;
381
382 -- update rows that have not been altered by user
383 procedure Translate_Row (
384 x_transformation_id in NUMBER,
385 x_transformation_name in VARCHAR2,
386 x_description in VARCHAR2,
387 x_owner in VARCHAR2)
388 IS
389 BEGIN
390
391 UPDATE HZ_TRANSFORMATIONS_TL set
392 transformation_name = x_transformation_name,
393 description = x_description,
394 source_lang = userenv('LANG'),
395 last_update_date = sysdate,
396 last_updated_by = decode(x_owner, 'SEED', 1, 0),
397 last_update_login = 0
398 where transformation_id = x_transformation_id
399 and userenv('LANG') in (language, source_lang);
400
401 END ;
402
403
404 END HZ_TRANSFORMATIONS_PKG ;
405