DBA Data[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