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_STYLES_PKG

Source


1 package body HZ_STYLES_PKG as
2 /* $Header: ARHPSTYB.pls 115.9 2004/02/25 23:16:25 geliu noship $ */
3 
4 L_USER_ID_FOR_SEED NUMBER := NULL;
5 
6 
7 procedure INSERT_ROW (
8   X_ROWID			IN OUT	NOCOPY VARCHAR2,
9   X_STYLE_CODE			IN	VARCHAR2,
10   X_DATABASE_OBJECT_NAME	IN	VARCHAR2,
11   X_STYLE_NAME			IN	VARCHAR2,
12   X_DESCRIPTION			IN	VARCHAR2,
13   X_OBJECT_VERSION_NUMBER       IN      NUMBER
14 ) is
15   cursor C is select ROWID from HZ_STYLES_B
16     where STYLE_CODE = X_STYLE_CODE
17     ;
18 begin
19   insert into HZ_STYLES_B (
20     STYLE_CODE,
21     DATABASE_OBJECT_NAME,
22     CREATED_BY,
23     CREATION_DATE,
24     LAST_UPDATE_LOGIN,
25     LAST_UPDATE_DATE,
26     LAST_UPDATED_BY,
27     OBJECT_VERSION_NUMBER
28   ) values (
29     DECODE( X_STYLE_CODE, FND_API.G_MISS_CHAR, NULL, X_STYLE_CODE ),
30     DECODE( X_DATABASE_OBJECT_NAME, FND_API.G_MISS_CHAR, NULL, X_DATABASE_OBJECT_NAME ),
31     NVL(L_USER_ID_FOR_SEED,HZ_UTILITY_V2PUB.CREATED_BY),
32     HZ_UTILITY_V2PUB.CREATION_DATE,
33     NVL(L_USER_ID_FOR_SEED,HZ_UTILITY_V2PUB.LAST_UPDATE_LOGIN),
34     HZ_UTILITY_V2PUB.LAST_UPDATE_DATE,
35     NVL(L_USER_ID_FOR_SEED,HZ_UTILITY_V2PUB.LAST_UPDATED_BY),
36     DECODE( X_OBJECT_VERSION_NUMBER, FND_API.G_MISS_NUM, NULL, X_OBJECT_VERSION_NUMBER )
37   );
38 
39   insert into HZ_STYLES_TL (
40     STYLE_CODE,
41     STYLE_NAME,
42     DESCRIPTION,
43     LANGUAGE,
44     SOURCE_LANG,
45     CREATED_BY,
46     CREATION_DATE,
47     LAST_UPDATE_LOGIN,
48     LAST_UPDATE_DATE,
49     LAST_UPDATED_BY
50   ) select
51     DECODE( X_STYLE_CODE, FND_API.G_MISS_CHAR, NULL, X_STYLE_CODE ),
52     DECODE( X_STYLE_NAME, FND_API.G_MISS_CHAR, NULL, X_STYLE_NAME ),
53     DECODE( X_DESCRIPTION, FND_API.G_MISS_CHAR, NULL, X_DESCRIPTION ),
54     L.LANGUAGE_CODE,
55     userenv('LANG'),
56     NVL(L_USER_ID_FOR_SEED,HZ_UTILITY_V2PUB.CREATED_BY),
57     HZ_UTILITY_V2PUB.CREATION_DATE,
58     NVL(L_USER_ID_FOR_SEED,HZ_UTILITY_V2PUB.LAST_UPDATE_LOGIN),
59     HZ_UTILITY_V2PUB.LAST_UPDATE_DATE,
60     NVL(L_USER_ID_FOR_SEED,HZ_UTILITY_V2PUB.LAST_UPDATED_BY)
61   from FND_LANGUAGES L
62   where L.INSTALLED_FLAG in ('I', 'B')
63   and not exists
64     (select NULL
65     from HZ_STYLES_TL T
66     where T.STYLE_CODE = X_STYLE_CODE
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   X_STYLE_CODE in VARCHAR2,
81   X_DATABASE_OBJECT_NAME in VARCHAR2,
82   X_STYLE_NAME in VARCHAR2,
83   X_DESCRIPTION in VARCHAR2
84 ) is
85   cursor c is select
86       DATABASE_OBJECT_NAME
87     from HZ_STYLES_B
88     where STYLE_CODE = X_STYLE_CODE
89     for update of STYLE_CODE nowait;
90   recinfo c%rowtype;
91 
92   cursor c1 is select
93       STYLE_NAME,
94       DESCRIPTION,
95       decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
96     from HZ_STYLES_TL
97     where STYLE_CODE = X_STYLE_CODE
98     and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
99     for update of STYLE_CODE nowait;
100 begin
101   open c;
102   fetch c into recinfo;
103   if (c%notfound) then
104     close c;
105     fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
106     app_exception.raise_exception;
107   end if;
108   close c;
109   if (    (recinfo.DATABASE_OBJECT_NAME = X_DATABASE_OBJECT_NAME)
110   ) then
111     null;
112   else
113     fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
114     app_exception.raise_exception;
115   end if;
116 
117   for tlinfo in c1 loop
118     if (tlinfo.BASELANG = 'Y') then
119       if (    (tlinfo.STYLE_NAME = X_STYLE_NAME)
120           AND ((tlinfo.DESCRIPTION = X_DESCRIPTION)
121                OR ((tlinfo.DESCRIPTION is null) AND (X_DESCRIPTION is null)))
122       ) then
123         null;
124       else
125         fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
126         app_exception.raise_exception;
127       end if;
128     end if;
129   end loop;
130   return;
131 end LOCK_ROW;
132 
133 procedure UPDATE_ROW (
134   X_STYLE_CODE			IN VARCHAR2,
135   X_DATABASE_OBJECT_NAME	IN VARCHAR2,
136   X_STYLE_NAME			IN VARCHAR2,
137   X_DESCRIPTION			IN VARCHAR2,
138   X_OBJECT_VERSION_NUMBER       IN NUMBER
139 ) is
140 begin
141   update HZ_STYLES_B set
142     DATABASE_OBJECT_NAME = DECODE( X_DATABASE_OBJECT_NAME, NULL, DATABASE_OBJECT_NAME, FND_API.G_MISS_CHAR, NULL, X_DATABASE_OBJECT_NAME ),
143     LAST_UPDATE_DATE = HZ_UTILITY_V2PUB.LAST_UPDATE_DATE,
144     LAST_UPDATED_BY = NVL(L_USER_ID_FOR_SEED,HZ_UTILITY_V2PUB.LAST_UPDATED_BY),
145     LAST_UPDATE_LOGIN = NVL(L_USER_ID_FOR_SEED,HZ_UTILITY_V2PUB.LAST_UPDATE_LOGIN),
146     OBJECT_VERSION_NUMBER = DECODE(X_OBJECT_VERSION_NUMBER, NULL, OBJECT_VERSION_NUMBER,
147                                   FND_API.G_MISS_NUM, NULL, X_OBJECT_VERSION_NUMBER)
148 
149   where STYLE_CODE = X_STYLE_CODE;
150 
151   if (sql%notfound) then
152     raise no_data_found;
153   end if;
154 
155   update HZ_STYLES_TL set
156     STYLE_NAME =  DECODE( X_STYLE_NAME, NULL, STYLE_NAME, FND_API.G_MISS_CHAR, NULL, X_STYLE_NAME ),
157     DESCRIPTION = DECODE( X_DESCRIPTION, NULL, DESCRIPTION, FND_API.G_MISS_CHAR, NULL, X_DESCRIPTION ),
158     LAST_UPDATE_DATE = HZ_UTILITY_V2PUB.LAST_UPDATE_DATE,
159     LAST_UPDATED_BY = NVL(L_USER_ID_FOR_SEED,HZ_UTILITY_V2PUB.LAST_UPDATED_BY),
160     LAST_UPDATE_LOGIN = NVL(L_USER_ID_FOR_SEED,HZ_UTILITY_V2PUB.LAST_UPDATE_LOGIN),
161     SOURCE_LANG = userenv('LANG')
162   where STYLE_CODE = X_STYLE_CODE
163   and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
164 
165   if (sql%notfound) then
166     raise no_data_found;
167   end if;
168 end UPDATE_ROW;
169 
170 PROCEDURE SELECT_ROW (
171   X_STYLE_CODE			IN OUT NOCOPY VARCHAR2,
172   X_DATABASE_OBJECT_NAME	OUT    NOCOPY VARCHAR2,
173   X_STYLE_NAME			OUT    NOCOPY VARCHAR2,
174   X_DESCRIPTION			OUT    NOCOPY VARCHAR2
175 ) IS
176 BEGIN
177 
178     SELECT
179         NVL( B.STYLE_CODE, FND_API.G_MISS_CHAR ),
180         NVL( B.DATABASE_OBJECT_NAME, FND_API.G_MISS_CHAR ),
181         NVL( T.STYLE_NAME, FND_API.G_MISS_CHAR ),
182         NVL( T.DESCRIPTION, FND_API.G_MISS_CHAR )
183     INTO X_STYLE_CODE,
184          X_DATABASE_OBJECT_NAME,
185 	 X_STYLE_NAME,
186 	 X_DESCRIPTION
187     FROM HZ_STYLES_B B, HZ_STYLES_TL T
188     WHERE B.STYLE_CODE = X_STYLE_CODE
189     AND   T.STYLE_CODE = X_STYLE_CODE
190     AND   T.LANGUAGE = userenv('LANG');
191 
192 EXCEPTION
193     WHEN NO_DATA_FOUND THEN
194         FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_NO_RECORD' );
195         FND_MESSAGE.SET_TOKEN( 'RECORD', 'style_rec');
196         FND_MESSAGE.SET_TOKEN( 'VALUE', 'STYLE_CODE' );
197         FND_MSG_PUB.ADD;
198         RAISE FND_API.G_EXC_ERROR;
199 
200 END SELECT_ROW;
201 
202 
203 procedure DELETE_ROW (
204   X_STYLE_CODE in VARCHAR2
205 ) is
206 begin
207   delete from HZ_STYLES_TL
208   where STYLE_CODE = X_STYLE_CODE;
209 
210   if (sql%notfound) then
211     raise no_data_found;
212   end if;
213 
214   delete from HZ_STYLES_B
215   where STYLE_CODE = X_STYLE_CODE;
216 
217   if (sql%notfound) then
218     raise no_data_found;
219   end if;
220 end DELETE_ROW;
221 
222 procedure ADD_LANGUAGE
223 is
224 begin
225   delete from HZ_STYLES_TL T
226   where not exists
227     (select NULL
228     from HZ_STYLES_B B
229     where B.STYLE_CODE = T.STYLE_CODE
230     );
231 
232   update HZ_STYLES_TL T set (
233       STYLE_NAME,
234       DESCRIPTION
235     ) = (select
236       B.STYLE_NAME,
237       B.DESCRIPTION
238     from HZ_STYLES_TL B
239     where B.STYLE_CODE = T.STYLE_CODE
240     and B.LANGUAGE = T.SOURCE_LANG)
241   where (
242       T.STYLE_CODE,
243       T.LANGUAGE
244   ) in (select
245       SUBT.STYLE_CODE,
246       SUBT.LANGUAGE
247     from HZ_STYLES_TL SUBB, HZ_STYLES_TL SUBT
248     where SUBB.STYLE_CODE = SUBT.STYLE_CODE
249     and SUBB.LANGUAGE = SUBT.SOURCE_LANG
250     and (SUBB.STYLE_NAME <> SUBT.STYLE_NAME
251       or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
252       or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
253       or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
254   ));
255 
256   insert into HZ_STYLES_TL (
257     STYLE_CODE,
258     STYLE_NAME,
259     DESCRIPTION,
260     LAST_UPDATE_DATE,
261     LAST_UPDATED_BY,
262     CREATION_DATE,
263     CREATED_BY,
264     LAST_UPDATE_LOGIN,
265     LANGUAGE,
266     SOURCE_LANG
267   ) select
268     B.STYLE_CODE,
269     B.STYLE_NAME,
270     B.DESCRIPTION,
271     B.LAST_UPDATE_DATE,
272     B.LAST_UPDATED_BY,
273     B.CREATION_DATE,
274     B.CREATED_BY,
275     B.LAST_UPDATE_LOGIN,
276     L.LANGUAGE_CODE,
277     B.SOURCE_LANG
278   from HZ_STYLES_TL B, FND_LANGUAGES L
279   where L.INSTALLED_FLAG in ('I', 'B')
280   and B.LANGUAGE = userenv('LANG')
281   and not exists
282     (select NULL
283     from HZ_STYLES_TL T
284     where T.STYLE_CODE = B.STYLE_CODE
285     and T.LANGUAGE = L.LANGUAGE_CODE);
286 end ADD_LANGUAGE;
287 
288 procedure LOAD_ROW (
289   X_STYLE_CODE in VARCHAR2,
290   X_DATABASE_OBJECT_NAME in VARCHAR2,
291   X_STYLE_NAME in VARCHAR2,
292   X_DESCRIPTION in VARCHAR2,
293   X_OWNER in VARCHAR2,  -- "SEED" or "CUSTOM"
294   X_LAST_UPDATE_DATE in DATE,
295   X_CUSTOM_MODE in VARCHAR2
296 ) is
297   l_f_luby    number;  -- entity owner in file
298   l_f_ludate  date;    -- entity update date in file
299   l_db_luby   number;  -- entity owner in db
300   l_db_ludate date;    -- entity update date in db
301   l_rowid     varchar2(64);
302   l_object_version_number number;
303 begin
304 
305   -- Translate owner to file_last_updated_by
306   if (x_owner = 'SEED') then
307     l_f_luby := 1;
308     L_USER_ID_FOR_SEED := 1;
309   else
310     l_f_luby := 0;
311   end if;
312 
313   -- Get last update date of ldt entity
314   l_f_ludate := nvl(x_last_update_date, sysdate);
315 
316   begin
317     select LAST_UPDATED_BY, LAST_UPDATE_DATE, OBJECT_VERSION_NUMBER
318          into l_db_luby, l_db_ludate, l_object_version_number
319          from HZ_STYLES_B
320          where STYLE_CODE = x_style_code;
321 
322     l_object_version_number := nvl(l_object_version_number, 1) + 1;
323 
324     -- Update record, honoring customization mode.
325     -- Record should be updated only if:
326     -- a. CUSTOM_MODE = FORCE, or
327     -- b. file owner is CUSTOM, db owner is SEED
328     -- c. owners are the same, and file_date > db_date
329 
330     if ((x_custom_mode = 'FORCE') or
331        ((l_f_luby = 0) and (l_db_luby = 1)) or
332        ((l_f_luby = l_db_luby) and (l_f_ludate > l_db_ludate)))
333     then
334       hz_styles_pkg.update_row (
335         X_STYLE_CODE           => X_STYLE_CODE,
336         X_DATABASE_OBJECT_NAME => X_DATABASE_OBJECT_NAME,
337         X_STYLE_NAME           => X_STYLE_NAME,
338         X_DESCRIPTION          => X_DESCRIPTION,
339 	X_OBJECT_VERSION_NUMBER => l_object_version_number
340       );
341     end if;
342 
343   exception
344     when no_data_found then
345       -- record not found, insert in all cases
346       hz_styles_pkg.insert_row(
347           x_rowid                => l_rowid,
348           x_style_code           => X_STYLE_CODE,
349           x_database_object_name => X_DATABASE_OBJECT_NAME,
350           x_style_name           => X_STYLE_NAME,
351           x_description          => X_DESCRIPTION,
352 	  x_object_version_number => 1
353       );
354   end;
355 
356 end LOAD_ROW;
357 
358 procedure TRANSLATE_ROW (
359   X_STYLE_CODE in VARCHAR2,
360   X_STYLE_NAME in VARCHAR2,
361   X_DESCRIPTION in VARCHAR2,
362   X_OWNER in VARCHAR2,  -- "SEED" or "CUSTOM"
363   X_LAST_UPDATE_DATE in DATE,
364   X_CUSTOM_MODE in VARCHAR2
365 ) is
366   l_f_luby    number;  -- entity owner in file
367   l_f_ludate  date;    -- entity update date in file
368   l_db_luby   number;  -- entity owner in db
369   l_db_ludate date;    -- entity update date in db
370 begin
371   -- Translate owner to file_last_updated_by
372   if (x_owner = 'SEED') then
373     l_f_luby := 1;
374   else
375     l_f_luby := 0;
376   end if;
377 
378   -- Get last update date of ldt entity
379   l_f_ludate := nvl(x_last_update_date, sysdate);
380 
381   begin
382     select LAST_UPDATED_BY, LAST_UPDATE_DATE
383          into l_db_luby, l_db_ludate
384          from HZ_STYLES_TL
385          where STYLE_CODE = x_style_code
386            and LANGUAGE = userenv('LANG');
387 
388     -- Update record, honoring customization mode.
389     -- Record should be updated only if:
390     -- a. CUSTOM_MODE = FORCE, or
391     -- b. file owner is CUSTOM, db owner is SEED
392     -- c. owners are the same, and file_date > db_date
393 
394     if ((x_custom_mode = 'FORCE') or
395        ((l_f_luby = 0) and (l_db_luby = 1)) or
396        ((l_f_luby = l_db_luby) and (l_f_ludate > l_db_ludate)))
397     then
398       update HZ_STYLES_TL
399          set STYLE_NAME        = nvl(X_STYLE_NAME,STYLE_NAME),
400              DESCRIPTION       = nvl(X_DESCRIPTION,DESCRIPTION),
401              LAST_UPDATE_DATE  = l_f_ludate,
402              LAST_UPDATED_BY   = l_f_luby,
403              LAST_UPDATE_LOGIN = 0,
404              SOURCE_LANG       = userenv('LANG')
405        where STYLE_CODE = X_STYLE_CODE
406          and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
407     end if;
408   exception
409     when no_data_found then
410       null;  -- no translation found.  standards say do nothing.
411   end;
412 
413 end TRANSLATE_ROW;
414 
415 end HZ_STYLES_PKG;