eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.HZ_TRANS_ATTRIBUTES_PKG

Source


1 PACKAGE BODY HZ_TRANS_ATTRIBUTES_PKG AS
2 /*$Header: ARHDQTAB.pls 120.6 2005/10/30 04:19:24 appldev noship $ */
3 
4 procedure INSERT_ROW (
5   X_ATTRIBUTE_ID in OUT NOCOPY NUMBER,
6   X_ENTITY_NAME in VARCHAR2,
7   X_CUSTOM_ATTRIBUTE_PROCEDURE in VARCHAR2,
8   X_SOURCE_TABLE in VARCHAR2,
9   X_ATTRIBUTE_NAME in VARCHAR2,
10   X_USER_DEFINED_ATTRIBUTE_NAME in VARCHAR2,
11   x_DENORM_FLAG 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   X_OBJECT_VERSION_NUMBER in NUMBER
18 ) is
19    CURSOR C2 IS SELECT  HZ_TRANS_ATTRIBUTES_s.nextval FROM sys.dual;
20   l_success VARCHAR2(1) := 'N';
21 begin
22  WHILE l_success = 'N' LOOP
23    BEGIN
24       IF ( X_ATTRIBUTE_ID IS NULL) OR (X_ATTRIBUTE_ID = FND_API.G_MISS_NUM) THEN
25          OPEN C2;
26          FETCH C2 INTO X_ATTRIBUTE_ID;
27          CLOSE C2;
28       END IF;
29 
30       insert into HZ_TRANS_ATTRIBUTES_B (
31        ATTRIBUTE_ID,
32        ATTRIBUTE_NAME,
33        ENTITY_NAME,
34        CUSTOM_ATTRIBUTE_PROCEDURE,
35        SOURCE_TABLE,
36        DENORM_FLAG,
37        CREATION_DATE,
38        CREATED_BY,
39        LAST_UPDATE_DATE,
40        LAST_UPDATED_BY,
41        LAST_UPDATE_LOGIN,
42        OBJECT_VERSION_NUMBER
43         ) values (
44        X_ATTRIBUTE_ID,
45        X_ATTRIBUTE_NAME,
46        X_ENTITY_NAME,
47        X_CUSTOM_ATTRIBUTE_PROCEDURE,
48        X_SOURCE_TABLE,
49        X_DENORM_FLAG,
50        X_CREATION_DATE,
51        X_CREATED_BY,
52        X_LAST_UPDATE_DATE,
53        X_LAST_UPDATED_BY,
54        X_LAST_UPDATE_LOGIN,
55        1
56      );
57 
58       l_success := 'Y';
59       EXCEPTION WHEN DUP_VAL_ON_INDEX THEN
60          IF INSTRB( SQLERRM, 'HZ_TRANS_ATTRIBUTES_B_U1' ) <> 0 THEN
61             DECLARE
62               l_count             NUMBER;
63               l_dummy             VARCHAR2(1);
64             BEGIN
65               l_count := 1;
66               WHILE l_count > 0 LOOP
67                  SELECT  HZ_TRANS_ATTRIBUTES_s.nextval
68 		  into  X_ATTRIBUTE_ID FROM sys.dual;
69                  BEGIN
70                   SELECT 'Y' INTO l_dummy
71                   FROM HZ_TRANS_ATTRIBUTES_B
72                   WHERE ATTRIBUTE_ID =  X_ATTRIBUTE_ID;
73                   l_count := 1;
74                  EXCEPTION WHEN NO_DATA_FOUND THEN
75                   l_count := 0;
76                  END;
77              END LOOP;
78           END;
79         END IF;
80      END;
81   END LOOP;
82 
83    insert into HZ_TRANS_ATTRIBUTES_TL (
84     LAST_UPDATE_DATE,
85     CREATION_DATE,
86     CREATED_BY,
87     LAST_UPDATED_BY,
88     LAST_UPDATE_LOGIN,
89     USER_DEFINED_ATTRIBUTE_NAME,
90     ATTRIBUTE_ID,
91     LANGUAGE,
92     SOURCE_LANG,
93     OBJECT_VERSION_NUMBER
94   ) select
95     X_LAST_UPDATE_DATE,
96     X_CREATION_DATE,
97     X_CREATED_BY,
98     X_LAST_UPDATED_BY,
99     X_LAST_UPDATE_LOGIN,
100     X_USER_DEFINED_ATTRIBUTE_NAME,
101     X_ATTRIBUTE_ID,
102     L.LANGUAGE_CODE,
103     userenv('LANG'),
104     1
105   from FND_LANGUAGES L
106   where L.INSTALLED_FLAG in ('I', 'B')
107   and not exists
108     (select NULL
109     from HZ_TRANS_ATTRIBUTES_TL T
110     where T.ATTRIBUTE_ID = X_ATTRIBUTE_ID
111     and T.LANGUAGE = L.LANGUAGE_CODE);
112 
113 end INSERT_ROW;
114 
115 procedure LOCK_ROW (
116   X_ATTRIBUTE_ID in NUMBER,
117   X_OBJECT_VERSION_NUMBER IN number
118 ) is
119   cursor c is select
120       OBJECT_VERSION_NUMBER
121     from HZ_TRANS_ATTRIBUTES_B
122     where ATTRIBUTE_ID = X_ATTRIBUTE_ID
123     for update of ATTRIBUTE_ID nowait;
124   recinfo c%rowtype;
125 
126   cursor c1 is select
127       OBJECT_VERSION_NUMBER,
128       decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
129     from HZ_TRANS_ATTRIBUTES_TL
130     where ATTRIBUTE_ID = X_ATTRIBUTE_ID
131     and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
132     for update of ATTRIBUTE_ID nowait;
133 begin
134   open c;
135   fetch c into recinfo;
136   if (c%notfound) then
137     close c;
138     fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
139     app_exception.raise_exception;
140   end if;
141   close c;
142   if ( ((recinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER)
143            OR ((recinfo.OBJECT_VERSION_NUMBER is null) AND (X_OBJECT_VERSION_NUMBER is null)))
144   ) then
145     null;
146   else
147     fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
148     app_exception.raise_exception;
149   end if;
150 
151 for tlinfo in c1 loop
152     if (tlinfo.BASELANG = 'Y') then
153       if (    ((tlinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER)
154                OR ((tlinfo.OBJECT_VERSION_NUMBER is null) AND (X_OBJECT_VERSION_NUMBER is null)))
155       ) then
156         null;
157       else
158         fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
159         app_exception.raise_exception;
160       end if;
161     end if;
162   end loop;
163   return;
164 end LOCK_ROW;
165 
166 
167 procedure LOCK_ROW (
168   X_ATTRIBUTE_ID in NUMBER,
169   X_OBJECT_VERSION_NUMBER IN number,
170   X_USER_DEFINED_ATTRIBUTE_NAME IN varchar2
171 ) is
172   cursor c is select
173       OBJECT_VERSION_NUMBER
174     from HZ_TRANS_ATTRIBUTES_B
175     where ATTRIBUTE_ID = X_ATTRIBUTE_ID
176     for update of ATTRIBUTE_ID nowait;
177   recinfo c%rowtype;
178 
179   cursor c1 is select
180       USER_DEFINED_ATTRIBUTE_NAME,
181       decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
182     from HZ_TRANS_ATTRIBUTES_TL
183     where ATTRIBUTE_ID = X_ATTRIBUTE_ID
184     and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
185     for update of ATTRIBUTE_ID nowait;
186 begin
187   open c;
188   fetch c into recinfo;
189   if (c%notfound) then
190     close c;
191     fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
192     app_exception.raise_exception;
193   end if;
194   close c;
195   if ( ((recinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER)
196            OR ((recinfo.OBJECT_VERSION_NUMBER is null) AND (X_OBJECT_VERSION_NUMBER is null)))
197   ) then
198     null;
199   else
200     fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
201     app_exception.raise_exception;
202   end if;
203 
204 for tlinfo in c1 loop
205     if (tlinfo.BASELANG = 'Y') then
206       if (    ((tlinfo.USER_DEFINED_ATTRIBUTE_NAME = X_USER_DEFINED_ATTRIBUTE_NAME)
207                OR ((tlinfo.USER_DEFINED_ATTRIBUTE_NAME is null) AND (X_USER_DEFINED_ATTRIBUTE_NAME is null)))
208       ) then
209         null;
210       else
211         fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
212         app_exception.raise_exception;
213       end if;
214     end if;
215   end loop;
216   return;
217 end LOCK_ROW;
218 
219 
220 procedure UPDATE_ROW (
221   X_ATTRIBUTE_ID in NUMBER,
222   X_ENTITY_NAME in VARCHAR2,
223   X_CUSTOM_ATTRIBUTE_PROCEDURE in VARCHAR2,
224   X_SOURCE_TABLE in VARCHAR2,
225   X_ATTRIBUTE_NAME in VARCHAR2,
226   X_USER_DEFINED_ATTRIBUTE_NAME in VARCHAR2,
227   X_DENORM_FLAG in VARCHAR2,
228   X_LAST_UPDATE_DATE in DATE,
229   X_LAST_UPDATED_BY in NUMBER,
230   X_LAST_UPDATE_LOGIN in NUMBER,
231   X_OBJECT_VERSION_NUMBER IN out nocopy NUMBER
232 ) is
233 
234   l_object_version_number number;
235   l_db_atr_name VARCHAR2(255);
236   l_db_user_def_atr VARCHAR2(255);
237   l_db_cus_atr_proc VARCHAR2(600);
238   l_db_upd_by NUMBER;
239   l_db_denorm_flag VARCHAR2(1);
240   TMP NUMBER;
241 begin
242 
243   SELECT 1 INTO TMP FROM HZ_TRANS_ATTRIBUTES_VL
244   where attribute_id=X_ATTRIBUTE_ID;
245 
246   l_object_version_number := NVL(X_object_version_number, 1) + 1;
247 
248   select ATTRIBUTE_NAME, USER_DEFINED_ATTRIBUTE_NAME,
249          CUSTOM_ATTRIBUTE_PROCEDURE, LAST_UPDATED_BY, nvl(DENORM_FLAG,'N')
250   into l_db_atr_name, l_db_user_def_atr, l_db_cus_atr_proc, l_db_upd_by, l_db_denorm_flag
251   from HZ_TRANS_ATTRIBUTES_VL
252   where attribute_id=X_ATTRIBUTE_ID;
253 
254   IF (X_LAST_UPDATED_BY = 1 AND l_db_upd_by <> 1) THEN
255      -- coming from seed and data modified by user
256      IF (l_db_cus_atr_proc <> X_CUSTOM_ATTRIBUTE_PROCEDURE) THEN
257        update HZ_TRANS_ATTRIBUTES_B set
258          CUSTOM_ATTRIBUTE_PROCEDURE = X_CUSTOM_ATTRIBUTE_PROCEDURE,
259          OBJECT_VERSION_NUMBER = l_object_version_number
260        where ATTRIBUTE_ID = X_ATTRIBUTE_ID;
261 
262        update hz_trans_functions_b
263        set staged_flag = 'N'
264        where attribute_id = X_ATTRIBUTE_ID;
265     END IF;
266   ELSE
267     update HZ_TRANS_ATTRIBUTES_B set
268       ENTITY_NAME = X_ENTITY_NAME,
269       CUSTOM_ATTRIBUTE_PROCEDURE = X_CUSTOM_ATTRIBUTE_PROCEDURE,
270       SOURCE_TABLE = X_SOURCE_TABLE,
271       DENORM_FLAG = X_DENORM_FLAG,
272       LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
273       LAST_UPDATED_BY = X_LAST_UPDATED_BY, -- L_LAST_UPDATED_BY,
274       LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
275       OBJECT_VERSION_NUMBER = l_object_version_number
276     where ATTRIBUTE_ID = X_ATTRIBUTE_ID;
277 
278     update HZ_TRANS_ATTRIBUTES_TL set
279       USER_DEFINED_ATTRIBUTE_NAME = X_USER_DEFINED_ATTRIBUTE_NAME,
280       LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
281       LAST_UPDATED_BY = X_LAST_UPDATED_BY,
282       LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
283 --    OBJECT_VERSION_NUMBER =l_object_version_number,
284       SOURCE_LANG = userenv('LANG')
285     where ATTRIBUTE_ID = X_ATTRIBUTE_ID
286     and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
287 
288     IF (l_db_cus_atr_proc <> X_CUSTOM_ATTRIBUTE_PROCEDURE) THEN
289        update hz_trans_functions_b
290        set staged_flag = 'N'
291        where attribute_id = X_ATTRIBUTE_ID;
292     END IF;
293   END IF;
294   X_object_version_number := l_object_version_number;
295 end UPDATE_ROW;
296 
297 procedure DELETE_ROW (
298   X_ATTRIBUTE_ID in NUMBER
299 ) is
300 begin
301   delete from HZ_TRANS_ATTRIBUTES_TL
302   where ATTRIBUTE_ID = X_ATTRIBUTE_ID;
303 
304   if (sql%notfound) then
305     raise no_data_found;
306   end if;
307 
308   delete from HZ_TRANS_ATTRIBUTES_B
309   where ATTRIBUTE_ID = X_ATTRIBUTE_ID;
310 
311   if (sql%notfound) then
312     raise no_data_found;
313   end if;
314 end DELETE_ROW;
315 
316 procedure ADD_LANGUAGE
317 is
318 begin
319   delete from HZ_TRANS_ATTRIBUTES_TL T
320   where not exists
321     (select NULL
322     from HZ_TRANS_ATTRIBUTES_B B
323     where B.ATTRIBUTE_ID = T.ATTRIBUTE_ID
324     );
325 
326      update HZ_TRANS_ATTRIBUTES_TL T set (
327      USER_DEFINED_ATTRIBUTE_NAME
328     ) = (select
329     B.USER_DEFINED_ATTRIBUTE_NAME
330     from HZ_TRANS_ATTRIBUTES_TL B
331     where B.ATTRIBUTE_ID = T.ATTRIBUTE_ID
332     and B.LANGUAGE = T.SOURCE_LANG)
333   where (
334       T.ATTRIBUTE_ID,
335       T.LANGUAGE
336   ) in (select
337       SUBT.ATTRIBUTE_ID,
338       SUBT.LANGUAGE
339     from HZ_TRANS_ATTRIBUTES_TL SUBB, HZ_TRANS_ATTRIBUTES_TL SUBT
340     where SUBB.ATTRIBUTE_ID = SUBT.ATTRIBUTE_ID
341     and SUBB.LANGUAGE = SUBT.SOURCE_LANG
342     and  SUBB.USER_DEFINED_ATTRIBUTE_NAME <> SUBT.USER_DEFINED_ATTRIBUTE_NAME
343   );
344 
345   insert into HZ_TRANS_ATTRIBUTES_TL (
346     LAST_UPDATE_DATE,
347     CREATION_DATE,
348     CREATED_BY,
349     LAST_UPDATED_BY,
350     LAST_UPDATE_LOGIN,
351     USER_DEFINED_ATTRIBUTE_NAME,
352     ATTRIBUTE_ID,
353     LANGUAGE,
354     SOURCE_LANG
355   ) select
356     B.LAST_UPDATE_DATE,
357     B.CREATION_DATE,
358     B.CREATED_BY,
359     B.LAST_UPDATED_BY,
360     B.LAST_UPDATE_LOGIN,
361     B.USER_DEFINED_ATTRIBUTE_NAME,
362     B.ATTRIBUTE_ID,
363     L.LANGUAGE_CODE,
364     B.SOURCE_LANG
365   from HZ_TRANS_ATTRIBUTES_TL B, FND_LANGUAGES L
366   where L.INSTALLED_FLAG in ('I', 'B')
367   and B.LANGUAGE = userenv('LANG')
368   and not exists
369     (select NULL
370     from HZ_TRANS_ATTRIBUTES_TL T
371     where T.ATTRIBUTE_ID = B.ATTRIBUTE_ID
372     and T.LANGUAGE = L.LANGUAGE_CODE);
373 end ADD_LANGUAGE;
374 
375 procedure LOAD_ROW (
376   X_ATTRIBUTE_ID in NUMBER,
377   X_ENTITY_NAME in VARCHAR2,
378   X_CUSTOM_ATTRIBUTE_PROCEDURE in VARCHAR2,
379   X_SOURCE_TABLE in VARCHAR2,
380   X_ATTRIBUTE_NAME in VARCHAR2,
381   X_USER_DEFINED_ATTRIBUTE_NAME in VARCHAR2,
382   X_DENORM_FLAG in VARCHAR2,
383   X_LAST_UPDATE_DATE in DATE,
384   X_LAST_UPDATED_BY in NUMBER,
385   X_LAST_UPDATE_LOGIN in NUMBER,
386   X_OBJECT_VERSION_NUMBER in NUMBER,
387   X_OWNER in VARCHAR2) IS
388 begin
389 
390   declare
391      user_id		number := 0;
392      row_id     	varchar2(64);
393      L_ATTRIBUTE_ID  NUMBER := X_ATTRIBUTE_ID;
394      L_OBJECT_VERSION_NUMBER number;
395 
396   begin
397 
398      if (X_OWNER = 'SEED') then
399         user_id := 1;
400      end if;
401 
402      L_OBJECT_VERSION_NUMBER := NVL(X_OBJECT_VERSION_NUMBER, 1) + 1;
403 
404      HZ_TRANS_ATTRIBUTES_PKG.UPDATE_ROW(
405      X_ATTRIBUTE_ID => X_ATTRIBUTE_ID ,
406      X_ENTITY_NAME =>  X_ENTITY_NAME,
407      X_CUSTOM_ATTRIBUTE_PROCEDURE =>X_CUSTOM_ATTRIBUTE_PROCEDURE ,
408      X_SOURCE_TABLE => X_SOURCE_TABLE ,
409      X_DENORM_FLAG => X_DENORM_FLAG ,
410      X_ATTRIBUTE_NAME => X_ATTRIBUTE_NAME ,
411      X_USER_DEFINED_ATTRIBUTE_NAME =>X_USER_DEFINED_ATTRIBUTE_NAME ,
412      X_LAST_UPDATE_DATE => sysdate,
413      X_LAST_UPDATED_BY => user_id,
414      X_LAST_UPDATE_LOGIN => 0,
415      X_OBJECT_VERSION_NUMBER => L_OBJECT_VERSION_NUMBER);
416 
417      exception
418        when NO_DATA_FOUND then
419 
420      HZ_TRANS_ATTRIBUTES_PKG.INSERT_ROW(
421      X_ATTRIBUTE_ID => L_ATTRIBUTE_ID ,
422      X_ENTITY_NAME =>  X_ENTITY_NAME,
423      X_CUSTOM_ATTRIBUTE_PROCEDURE =>X_CUSTOM_ATTRIBUTE_PROCEDURE ,
424      X_SOURCE_TABLE => X_SOURCE_TABLE ,
425      X_ATTRIBUTE_NAME => X_ATTRIBUTE_NAME ,
426      X_USER_DEFINED_ATTRIBUTE_NAME =>X_USER_DEFINED_ATTRIBUTE_NAME ,
427      X_DENORM_FLAG => X_DENORM_FLAG ,
428      X_CREATION_DATE=>SYSDATE  ,
429      X_CREATED_BY =>USER_ID,
430      X_LAST_UPDATE_DATE => sysdate,
431      X_LAST_UPDATED_BY => user_id,
432      X_LAST_UPDATE_LOGIN => 0,
433      X_OBJECT_VERSION_NUMBER => 1);
434 
435      end;
436 end LOAD_ROW;
437 
438 procedure TRANSLATE_ROW (
439   X_ATTRIBUTE_ID in NUMBER,
440   X_USER_DEFINED_ATTRIBUTE_NAME in VARCHAR2,
441   X_OWNER in VARCHAR2) IS
442 
443  begin
444     -- only update rows that have not been altered by user
445     update HZ_TRANS_ATTRIBUTES_TL set
446     USER_DEFINED_ATTRIBUTE_NAME= X_USER_DEFINED_ATTRIBUTE_NAME,
447     source_lang = userenv('LANG'),
448     last_update_date = sysdate,
449     last_updated_by = decode(X_OWNER, 'SEED', 1, 0),
450     last_update_login = 0
451     where ATTRIBUTE_ID= X_ATTRIBUTE_ID
452     and   userenv('LANG') in (language, source_lang);
453 
454 end TRANSLATE_ROW;
455 end HZ_TRANS_ATTRIBUTES_PKG;