[Home] [Help]
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;