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_STYLE_FORMATS_PKG

Source


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