[Home] [Help]
PACKAGE BODY: APPS.HZ_STYLE_FMT_LAYOUTS_PKG
Source
1 package body HZ_STYLE_FMT_LAYOUTS_PKG as
2 /* $Header: ARHPSFYB.pls 115.9 2004/02/25 23:16:07 geliu noship $ */
3
4 L_USER_ID_FOR_SEED NUMBER := NULL;
5
6 procedure INSERT_ROW (
7 X_ROWID in out NOCOPY VARCHAR2,
8 X_STYLE_FMT_LAYOUT_ID in out NOCOPY NUMBER,
9 X_STYLE_FORMAT_CODE in VARCHAR2,
10 X_VARIATION_NUMBER in NUMBER,
11 X_ATTRIBUTE_CODE in VARCHAR2,
12 X_ATTRIBUTE_APPLICATION_ID in NUMBER,
13 X_LINE_NUMBER in NUMBER,
14 X_POSITION in NUMBER,
15 X_MANDATORY_FLAG in VARCHAR2,
16 X_USE_INITIAL_FLAG in VARCHAR2,
17 X_UPPERCASE_FLAG in VARCHAR2,
18 X_TRANSFORM_FUNCTION in VARCHAR2,
19 X_DELIMITER_BEFORE in VARCHAR2,
20 X_DELIMITER_AFTER in VARCHAR2,
21 X_BLANK_LINES_BEFORE in NUMBER,
22 X_BLANK_LINES_AFTER in NUMBER,
23 X_PROMPT in VARCHAR2,
24 X_START_DATE_ACTIVE in DATE,
25 X_END_DATE_ACTIVE in DATE,
26 X_OBJECT_VERSION_NUMBER IN NUMBER
27 ) is
28 cursor C is select ROWID from HZ_STYLE_FMT_LAYOUTS_B
29 where STYLE_FMT_LAYOUT_ID = X_STYLE_FMT_LAYOUT_ID
30 ;
31 begin
32 insert into HZ_STYLE_FMT_LAYOUTS_B (
33 STYLE_FMT_LAYOUT_ID,
34 STYLE_FORMAT_CODE,
35 VARIATION_NUMBER,
36 ATTRIBUTE_CODE,
37 ATTRIBUTE_APPLICATION_ID,
38 LINE_NUMBER,
39 POSITION,
40 MANDATORY_FLAG,
41 USE_INITIAL_FLAG,
42 UPPERCASE_FLAG,
43 TRANSFORM_FUNCTION,
44 DELIMITER_BEFORE,
45 DELIMITER_AFTER,
46 BLANK_LINES_BEFORE,
47 BLANK_LINES_AFTER,
48 START_DATE_ACTIVE,
49 END_DATE_ACTIVE,
50 CREATED_BY,
51 CREATION_DATE,
52 LAST_UPDATE_LOGIN,
53 LAST_UPDATE_DATE,
54 LAST_UPDATED_BY,
55 OBJECT_VERSION_NUMBER
56 ) values (
57 DECODE( X_STYLE_FMT_LAYOUT_ID, FND_API.G_MISS_NUM, HZ_STYLE_FMT_LAYOUTS_S.NEXTVAL, NULL, HZ_STYLE_FMT_LAYOUTS_S.NEXTVAL, X_STYLE_FMT_LAYOUT_ID ),
58 DECODE( X_STYLE_FORMAT_CODE, FND_API.G_MISS_CHAR, NULL, X_STYLE_FORMAT_CODE ),
59 DECODE( X_VARIATION_NUMBER, FND_API.G_MISS_NUM, NULL, X_VARIATION_NUMBER ),
60 DECODE( X_ATTRIBUTE_CODE, FND_API.G_MISS_CHAR, NULL, X_ATTRIBUTE_CODE ),
61 DECODE( X_ATTRIBUTE_APPLICATION_ID, FND_API.G_MISS_NUM, NULL, X_ATTRIBUTE_APPLICATION_ID ),
62 DECODE( X_LINE_NUMBER, FND_API.G_MISS_NUM, NULL, X_LINE_NUMBER ),
63 DECODE( X_POSITION, FND_API.G_MISS_NUM, NULL, X_POSITION ),
64 DECODE( X_MANDATORY_FLAG, FND_API.G_MISS_CHAR, NULL, X_MANDATORY_FLAG ),
65 DECODE( X_USE_INITIAL_FLAG, FND_API.G_MISS_CHAR, NULL, X_USE_INITIAL_FLAG ),
66 DECODE( X_UPPERCASE_FLAG, FND_API.G_MISS_CHAR, NULL, X_UPPERCASE_FLAG ),
67 DECODE( X_TRANSFORM_FUNCTION, FND_API.G_MISS_CHAR, NULL, X_TRANSFORM_FUNCTION ),
68 DECODE( X_DELIMITER_BEFORE, FND_API.G_MISS_CHAR, NULL, X_DELIMITER_BEFORE ),
69 DECODE( X_DELIMITER_AFTER, FND_API.G_MISS_CHAR, NULL, X_DELIMITER_AFTER ),
70 DECODE( X_BLANK_LINES_BEFORE, FND_API.G_MISS_NUM, NULL, X_BLANK_LINES_BEFORE ),
71 DECODE( X_BLANK_LINES_AFTER, FND_API.G_MISS_NUM, NULL, X_BLANK_LINES_AFTER ),
72 DECODE( X_START_DATE_ACTIVE, FND_API.G_MISS_DATE, NULL, X_START_DATE_ACTIVE ),
73 DECODE( X_END_DATE_ACTIVE, FND_API.G_MISS_DATE, NULL, X_END_DATE_ACTIVE ),
74 NVL(L_USER_ID_FOR_SEED,HZ_UTILITY_V2PUB.CREATED_BY),
75 HZ_UTILITY_V2PUB.CREATION_DATE,
76 NVL(L_USER_ID_FOR_SEED,HZ_UTILITY_V2PUB.LAST_UPDATE_LOGIN),
77 HZ_UTILITY_V2PUB.LAST_UPDATE_DATE,
78 NVL(L_USER_ID_FOR_SEED,HZ_UTILITY_V2PUB.LAST_UPDATED_BY),
79 DECODE( X_OBJECT_VERSION_NUMBER, FND_API.G_MISS_NUM, NULL, X_OBJECT_VERSION_NUMBER )
80 ) RETURNING
81 STYLE_FMT_LAYOUT_ID
82 INTO
83 X_STYLE_FMT_LAYOUT_ID;
84
85 insert into HZ_STYLE_FMT_LAYOUTS_TL (
86 STYLE_FMT_LAYOUT_ID,
87 PROMPT,
88 CREATED_BY,
89 CREATION_DATE,
90 LAST_UPDATE_LOGIN,
91 LAST_UPDATE_DATE,
92 LAST_UPDATED_BY,
93 LANGUAGE,
94 SOURCE_LANG
95 ) select
96 X_STYLE_FMT_LAYOUT_ID,
97 DECODE( X_PROMPT, FND_API.G_MISS_CHAR, NULL, X_PROMPT ),
98 NVL(L_USER_ID_FOR_SEED,HZ_UTILITY_V2PUB.CREATED_BY),
99 HZ_UTILITY_V2PUB.CREATION_DATE,
100 NVL(L_USER_ID_FOR_SEED,HZ_UTILITY_V2PUB.LAST_UPDATE_LOGIN),
101 HZ_UTILITY_V2PUB.LAST_UPDATE_DATE,
102 NVL(L_USER_ID_FOR_SEED,HZ_UTILITY_V2PUB.LAST_UPDATED_BY),
103 L.LANGUAGE_CODE,
104 userenv('LANG')
105 from FND_LANGUAGES L
106 where L.INSTALLED_FLAG in ('I', 'B')
107 and not exists
108 (select NULL
109 from HZ_STYLE_FMT_LAYOUTS_TL T
110 where T.STYLE_FMT_LAYOUT_ID = X_STYLE_FMT_LAYOUT_ID
111 and T.LANGUAGE = L.LANGUAGE_CODE);
112
113 open c;
114 fetch c into X_ROWID;
115 if (c%notfound) then
116 close c;
117 raise no_data_found;
118 end if;
119 close c;
120
121 end INSERT_ROW;
122
123 procedure LOCK_ROW (
124 X_STYLE_FMT_LAYOUT_ID in NUMBER,
125 X_STYLE_FORMAT_CODE in VARCHAR2,
126 X_VARIATION_NUMBER in NUMBER,
127 X_ATTRIBUTE_CODE in VARCHAR2,
128 X_ATTRIBUTE_APPLICATION_ID in NUMBER,
129 X_LINE_NUMBER in NUMBER,
130 X_POSITION in NUMBER,
131 X_MANDATORY_FLAG in VARCHAR2,
132 X_USE_INITIAL_FLAG in VARCHAR2,
133 X_UPPERCASE_FLAG in VARCHAR2,
134 X_TRANSFORM_FUNCTION in VARCHAR2,
135 X_DELIMITER_BEFORE in VARCHAR2,
136 X_DELIMITER_AFTER in VARCHAR2,
137 X_BLANK_LINES_BEFORE in NUMBER,
138 X_BLANK_LINES_AFTER in NUMBER,
139 X_PROMPT in VARCHAR2
140 ) is
141 cursor c is select
142 STYLE_FORMAT_CODE,
143 VARIATION_NUMBER,
144 ATTRIBUTE_CODE,
145 ATTRIBUTE_APPLICATION_ID,
146 LINE_NUMBER,
147 POSITION,
148 MANDATORY_FLAG,
149 USE_INITIAL_FLAG,
150 UPPERCASE_FLAG,
151 TRANSFORM_FUNCTION,
152 DELIMITER_BEFORE,
153 DELIMITER_AFTER,
154 BLANK_LINES_BEFORE,
155 BLANK_LINES_AFTER
156 from HZ_STYLE_FMT_LAYOUTS_B
157 where STYLE_FMT_LAYOUT_ID = X_STYLE_FMT_LAYOUT_ID
158 for update of STYLE_FMT_LAYOUT_ID nowait;
159 recinfo c%rowtype;
160
161 cursor c1 is select
162 PROMPT,
163 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
164 from HZ_STYLE_FMT_LAYOUTS_TL
165 where STYLE_FMT_LAYOUT_ID = X_STYLE_FMT_LAYOUT_ID
166 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
167 for update of STYLE_FMT_LAYOUT_ID nowait;
168 begin
169 open c;
170 fetch c into recinfo;
171 if (c%notfound) then
172 close c;
173 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
174 app_exception.raise_exception;
175 end if;
176 close c;
177 if ( (recinfo.STYLE_FORMAT_CODE = X_STYLE_FORMAT_CODE)
178 AND (recinfo.VARIATION_NUMBER = X_VARIATION_NUMBER)
179 AND (recinfo.ATTRIBUTE_CODE = X_ATTRIBUTE_CODE)
180 AND (recinfo.ATTRIBUTE_APPLICATION_ID = X_ATTRIBUTE_APPLICATION_ID)
181 AND (recinfo.LINE_NUMBER = X_LINE_NUMBER)
182 AND (recinfo.POSITION = X_POSITION)
183 AND (recinfo.MANDATORY_FLAG = X_MANDATORY_FLAG)
184 AND (recinfo.USE_INITIAL_FLAG = X_USE_INITIAL_FLAG)
185 AND (recinfo.UPPERCASE_FLAG = X_UPPERCASE_FLAG)
186 AND ((recinfo.TRANSFORM_FUNCTION = X_TRANSFORM_FUNCTION)
187 OR ((recinfo.TRANSFORM_FUNCTION is null) AND (X_TRANSFORM_FUNCTION is null)))
188 AND ((recinfo.DELIMITER_BEFORE = X_DELIMITER_BEFORE)
189 OR ((recinfo.DELIMITER_BEFORE is null) AND (X_DELIMITER_BEFORE is null)))
190 AND ((recinfo.DELIMITER_AFTER = X_DELIMITER_AFTER)
191 OR ((recinfo.DELIMITER_AFTER is null) AND (X_DELIMITER_AFTER is null)))
192 AND ((recinfo.BLANK_LINES_BEFORE = X_BLANK_LINES_BEFORE)
193 OR ((recinfo.BLANK_LINES_BEFORE is null) AND (X_BLANK_LINES_BEFORE is null)))
194 AND ((recinfo.BLANK_LINES_AFTER = X_BLANK_LINES_AFTER)
195 OR ((recinfo.BLANK_LINES_AFTER is null) AND (X_BLANK_LINES_AFTER is null)))
196 ) then
197 null;
198 else
199 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
200 app_exception.raise_exception;
201 end if;
202
203 for tlinfo in c1 loop
204 if (tlinfo.BASELANG = 'Y') then
205 if ( ((tlinfo.PROMPT = X_PROMPT)
206 OR ((tlinfo.PROMPT is null) AND (X_PROMPT is null)))
207 ) then
208 null;
209 else
210 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
211 app_exception.raise_exception;
212 end if;
213 end if;
214 end loop;
215 return;
216 end LOCK_ROW;
217
218 procedure UPDATE_ROW (
219 X_STYLE_FMT_LAYOUT_ID in NUMBER,
220 X_STYLE_FORMAT_CODE in VARCHAR2,
221 X_VARIATION_NUMBER in NUMBER,
222 X_ATTRIBUTE_CODE in VARCHAR2,
223 X_ATTRIBUTE_APPLICATION_ID in NUMBER,
224 X_LINE_NUMBER in NUMBER,
225 X_POSITION in NUMBER,
226 X_MANDATORY_FLAG in VARCHAR2,
227 X_USE_INITIAL_FLAG in VARCHAR2,
228 X_UPPERCASE_FLAG in VARCHAR2,
229 X_TRANSFORM_FUNCTION in VARCHAR2,
230 X_DELIMITER_BEFORE in VARCHAR2,
231 X_DELIMITER_AFTER in VARCHAR2,
232 X_BLANK_LINES_BEFORE in NUMBER,
233 X_BLANK_LINES_AFTER in NUMBER,
234 X_PROMPT in VARCHAR2,
235 X_START_DATE_ACTIVE in DATE,
236 X_END_DATE_ACTIVE in DATE,
237 X_OBJECT_VERSION_NUMBER IN NUMBER
238 ) is
239 begin
240 update HZ_STYLE_FMT_LAYOUTS_B set
241 STYLE_FORMAT_CODE = DECODE( X_STYLE_FORMAT_CODE, NULL, STYLE_FORMAT_CODE, FND_API.G_MISS_CHAR, NULL, X_STYLE_FORMAT_CODE ),
242 VARIATION_NUMBER = DECODE( X_VARIATION_NUMBER, NULL, VARIATION_NUMBER, FND_API.G_MISS_NUM, NULL, X_VARIATION_NUMBER ),
243 ATTRIBUTE_CODE = DECODE( X_ATTRIBUTE_CODE, NULL, ATTRIBUTE_CODE, FND_API.G_MISS_CHAR, NULL, X_ATTRIBUTE_CODE ),
244 ATTRIBUTE_APPLICATION_ID = DECODE( X_ATTRIBUTE_APPLICATION_ID, NULL, ATTRIBUTE_APPLICATION_ID, FND_API.G_MISS_NUM, NULL, X_ATTRIBUTE_APPLICATION_ID ),
245 LINE_NUMBER = DECODE( X_LINE_NUMBER, NULL, LINE_NUMBER, FND_API.G_MISS_NUM, NULL, X_LINE_NUMBER ),
246 POSITION = DECODE( X_POSITION, NULL, POSITION, FND_API.G_MISS_NUM, NULL, X_POSITION ),
247 MANDATORY_FLAG = DECODE( X_MANDATORY_FLAG, NULL, MANDATORY_FLAG, FND_API.G_MISS_CHAR, NULL, X_MANDATORY_FLAG ),
248 USE_INITIAL_FLAG = DECODE( X_USE_INITIAL_FLAG, NULL, USE_INITIAL_FLAG, FND_API.G_MISS_CHAR, NULL, X_USE_INITIAL_FLAG ),
249 UPPERCASE_FLAG = DECODE( X_UPPERCASE_FLAG, NULL, UPPERCASE_FLAG, FND_API.G_MISS_CHAR, NULL, X_UPPERCASE_FLAG ),
250 TRANSFORM_FUNCTION = DECODE( X_TRANSFORM_FUNCTION, NULL, TRANSFORM_FUNCTION, FND_API.G_MISS_CHAR, NULL, X_TRANSFORM_FUNCTION ),
251 DELIMITER_BEFORE = DECODE( X_DELIMITER_BEFORE, NULL, DELIMITER_BEFORE, FND_API.G_MISS_CHAR, NULL, X_DELIMITER_BEFORE ),
252 DELIMITER_AFTER = DECODE( X_DELIMITER_AFTER, NULL, DELIMITER_AFTER, FND_API.G_MISS_CHAR, NULL, X_DELIMITER_AFTER ),
253 BLANK_LINES_BEFORE = DECODE( X_BLANK_LINES_BEFORE, NULL, BLANK_LINES_BEFORE, FND_API.G_MISS_NUM, NULL, X_BLANK_LINES_BEFORE ),
254 BLANK_LINES_AFTER = DECODE( X_BLANK_LINES_AFTER, NULL, BLANK_LINES_AFTER, FND_API.G_MISS_NUM, NULL, X_BLANK_LINES_AFTER ),
255 START_DATE_ACTIVE = DECODE( X_START_DATE_ACTIVE, NULL, START_DATE_ACTIVE, FND_API.G_MISS_DATE, NULL, X_START_DATE_ACTIVE ),
256 END_DATE_ACTIVE = DECODE( X_END_DATE_ACTIVE, NULL, END_DATE_ACTIVE, FND_API.G_MISS_DATE, NULL, X_END_DATE_ACTIVE ),
257 LAST_UPDATE_DATE = HZ_UTILITY_V2PUB.LAST_UPDATE_DATE,
258 LAST_UPDATED_BY = NVL(L_USER_ID_FOR_SEED,HZ_UTILITY_V2PUB.LAST_UPDATED_BY),
259 LAST_UPDATE_LOGIN = NVL(L_USER_ID_FOR_SEED,HZ_UTILITY_V2PUB.LAST_UPDATE_LOGIN),
260 OBJECT_VERSION_NUMBER = DECODE(X_OBJECT_VERSION_NUMBER, NULL, OBJECT_VERSION_NUMBER,
261 FND_API.G_MISS_NUM, NULL, X_OBJECT_VERSION_NUMBER)
262 where STYLE_FMT_LAYOUT_ID = X_STYLE_FMT_LAYOUT_ID;
263
264 if (sql%notfound) then
265 raise no_data_found;
266 end if;
267
268 update HZ_STYLE_FMT_LAYOUTS_TL set
269 PROMPT = DECODE( X_PROMPT, NULL, PROMPT, FND_API.G_MISS_CHAR, NULL, X_PROMPT ),
270 LAST_UPDATE_DATE = HZ_UTILITY_V2PUB.LAST_UPDATE_DATE,
271 LAST_UPDATED_BY = NVL(L_USER_ID_FOR_SEED,HZ_UTILITY_V2PUB.LAST_UPDATED_BY),
272 LAST_UPDATE_LOGIN = NVL(L_USER_ID_FOR_SEED,HZ_UTILITY_V2PUB.LAST_UPDATE_LOGIN),
273 SOURCE_LANG = userenv('LANG')
274 where STYLE_FMT_LAYOUT_ID = X_STYLE_FMT_LAYOUT_ID
275 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
276
277 if (sql%notfound) then
278 raise no_data_found;
279 end if;
280 end UPDATE_ROW;
281
282 PROCEDURE SELECT_ROW (
283 X_STYLE_FMT_LAYOUT_ID IN OUT NOCOPY NUMBER,
284 X_STYLE_FORMAT_CODE OUT NOCOPY VARCHAR2,
285 X_VARIATION_NUMBER OUT NOCOPY NUMBER,
286 X_ATTRIBUTE_CODE OUT NOCOPY VARCHAR2,
287 X_ATTRIBUTE_APPLICATION_ID OUT NOCOPY NUMBER,
288 X_LINE_NUMBER OUT NOCOPY NUMBER,
289 X_POSITION OUT NOCOPY NUMBER,
290 X_MANDATORY_FLAG OUT NOCOPY VARCHAR2,
291 X_USE_INITIAL_FLAG OUT NOCOPY VARCHAR2,
292 X_UPPERCASE_FLAG OUT NOCOPY VARCHAR2,
293 X_TRANSFORM_FUNCTION OUT NOCOPY VARCHAR2,
294 X_DELIMITER_BEFORE OUT NOCOPY VARCHAR2,
295 X_DELIMITER_AFTER OUT NOCOPY VARCHAR2,
296 X_BLANK_LINES_BEFORE OUT NOCOPY NUMBER,
297 X_BLANK_LINES_AFTER OUT NOCOPY NUMBER,
298 X_PROMPT OUT NOCOPY VARCHAR2,
299 X_START_DATE_ACTIVE OUT NOCOPY DATE,
300 X_END_DATE_ACTIVE OUT NOCOPY DATE
301 ) IS
302 BEGIN
303
304 SELECT
305 NVL( B.STYLE_FMT_LAYOUT_ID, FND_API.G_MISS_NUM ),
306 NVL( B.STYLE_FORMAT_CODE, FND_API.G_MISS_CHAR ),
307 NVL( B.VARIATION_NUMBER, FND_API.G_MISS_NUM ),
308 NVL( B.ATTRIBUTE_CODE, FND_API.G_MISS_CHAR ),
309 NVL( B.ATTRIBUTE_APPLICATION_ID, FND_API.G_MISS_NUM ),
310 NVL( B.LINE_NUMBER, FND_API.G_MISS_NUM ),
311 NVL( B.POSITION, FND_API.G_MISS_NUM ),
312 NVL( B.MANDATORY_FLAG, FND_API.G_MISS_CHAR ),
313 NVL( B.USE_INITIAL_FLAG, FND_API.G_MISS_CHAR ),
314 NVL( B.UPPERCASE_FLAG, FND_API.G_MISS_CHAR ),
315 NVL( B.TRANSFORM_FUNCTION, FND_API.G_MISS_CHAR ),
316 NVL( B.DELIMITER_BEFORE, FND_API.G_MISS_CHAR ),
317 NVL( B.DELIMITER_AFTER, FND_API.G_MISS_CHAR ),
318 NVL( B.BLANK_LINES_BEFORE, FND_API.G_MISS_NUM ),
319 NVL( B.BLANK_LINES_AFTER, FND_API.G_MISS_NUM ),
320 NVL( T.PROMPT, FND_API.G_MISS_CHAR ),
321 NVL( B.START_DATE_ACTIVE, FND_API.G_MISS_DATE ),
322 NVL( B.END_DATE_ACTIVE, FND_API.G_MISS_DATE )
323 INTO
324 X_STYLE_FMT_LAYOUT_ID ,
325 X_STYLE_FORMAT_CODE ,
326 X_VARIATION_NUMBER ,
327 X_ATTRIBUTE_CODE ,
328 X_ATTRIBUTE_APPLICATION_ID ,
329 X_LINE_NUMBER ,
330 X_POSITION ,
331 X_MANDATORY_FLAG ,
332 X_USE_INITIAL_FLAG ,
333 X_UPPERCASE_FLAG ,
334 X_TRANSFORM_FUNCTION ,
335 X_DELIMITER_BEFORE ,
336 X_DELIMITER_AFTER ,
337 X_BLANK_LINES_BEFORE ,
338 X_BLANK_LINES_AFTER ,
339 X_PROMPT,
340 X_START_DATE_ACTIVE ,
341 X_END_DATE_ACTIVE
342 FROM HZ_STYLE_FMT_LAYOUTS_B B, HZ_STYLE_FMT_LAYOUTS_TL T
343 WHERE B.STYLE_FMT_LAYOUT_ID = X_STYLE_FMT_LAYOUT_ID
344 AND T.STYLE_FMT_LAYOUT_ID = X_STYLE_FMT_LAYOUT_ID
345 AND T.LANGUAGE = userenv('LANG');
346
347 EXCEPTION
348 WHEN NO_DATA_FOUND THEN
349 FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_NO_RECORD' );
350 FND_MESSAGE.SET_TOKEN( 'RECORD', 'style_fmt_layout_rec');
351 FND_MESSAGE.SET_TOKEN( 'VALUE', 'STYLE_FMT_LAYOUT_ID' );
352 FND_MSG_PUB.ADD;
353 RAISE FND_API.G_EXC_ERROR;
354 END SELECT_ROW;
355
356
357 procedure DELETE_ROW (
358 X_STYLE_FMT_LAYOUT_ID in NUMBER
359 ) is
360 begin
361 delete from HZ_STYLE_FMT_LAYOUTS_TL
362 where STYLE_FMT_LAYOUT_ID = X_STYLE_FMT_LAYOUT_ID;
363
364 if (sql%notfound) then
365 raise no_data_found;
366 end if;
367
368 delete from HZ_STYLE_FMT_LAYOUTS_B
369 where STYLE_FMT_LAYOUT_ID = X_STYLE_FMT_LAYOUT_ID;
370
371 if (sql%notfound) then
372 raise no_data_found;
373 end if;
374 end DELETE_ROW;
375
376 procedure ADD_LANGUAGE
377 is
378 begin
379 delete from HZ_STYLE_FMT_LAYOUTS_TL T
380 where not exists
381 (select NULL
382 from HZ_STYLE_FMT_LAYOUTS_B B
383 where B.STYLE_FMT_LAYOUT_ID = T.STYLE_FMT_LAYOUT_ID
384 );
385
386 update HZ_STYLE_FMT_LAYOUTS_TL T set (
387 PROMPT
388 ) = (select
389 B.PROMPT
390 from HZ_STYLE_FMT_LAYOUTS_TL B
391 where B.STYLE_FMT_LAYOUT_ID = T.STYLE_FMT_LAYOUT_ID
392 and B.LANGUAGE = T.SOURCE_LANG)
393 where (
394 T.STYLE_FMT_LAYOUT_ID,
395 T.LANGUAGE
396 ) in (select
397 SUBT.STYLE_FMT_LAYOUT_ID,
398 SUBT.LANGUAGE
399 from HZ_STYLE_FMT_LAYOUTS_TL SUBB, HZ_STYLE_FMT_LAYOUTS_TL SUBT
400 where SUBB.STYLE_FMT_LAYOUT_ID = SUBT.STYLE_FMT_LAYOUT_ID
401 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
402 and (SUBB.PROMPT <> SUBT.PROMPT
403 or (SUBB.PROMPT is null and SUBT.PROMPT is not null)
404 or (SUBB.PROMPT is not null and SUBT.PROMPT is null)
405 ));
406
407 insert into HZ_STYLE_FMT_LAYOUTS_TL (
408 STYLE_FMT_LAYOUT_ID,
409 PROMPT,
410 LAST_UPDATE_DATE,
411 LAST_UPDATED_BY,
412 CREATION_DATE,
413 CREATED_BY,
414 LAST_UPDATE_LOGIN,
415 LANGUAGE,
416 SOURCE_LANG
417 ) select
418 B.STYLE_FMT_LAYOUT_ID,
419 B.PROMPT,
420 B.LAST_UPDATE_DATE,
421 B.LAST_UPDATED_BY,
422 B.CREATION_DATE,
423 B.CREATED_BY,
424 B.LAST_UPDATE_LOGIN,
425 L.LANGUAGE_CODE,
426 B.SOURCE_LANG
427 from HZ_STYLE_FMT_LAYOUTS_TL B, FND_LANGUAGES L
428 where L.INSTALLED_FLAG in ('I', 'B')
429 and B.LANGUAGE = userenv('LANG')
430 and not exists
431 (select NULL
432 from HZ_STYLE_FMT_LAYOUTS_TL T
433 where T.STYLE_FMT_LAYOUT_ID = B.STYLE_FMT_LAYOUT_ID
434 and T.LANGUAGE = L.LANGUAGE_CODE);
435 end ADD_LANGUAGE;
436
437 procedure LOAD_ROW (
438 X_STYLE_FORMAT_CODE in VARCHAR2,
439 X_VARIATION_NUMBER in NUMBER,
440 X_ATTRIBUTE_CODE in VARCHAR2,
441 X_ATTRIBUTE_APPLICATION_CODE in VARCHAR2,
442 X_LINE_NUMBER in NUMBER,
443 X_POSITION in NUMBER,
444 X_MANDATORY_FLAG in VARCHAR2,
445 X_USE_INITIAL_FLAG in VARCHAR2,
446 X_UPPERCASE_FLAG in VARCHAR2,
447 X_TRANSFORM_FUNCTION in VARCHAR2,
448 X_DELIMITER_BEFORE in VARCHAR2,
449 X_DELIMITER_AFTER in VARCHAR2,
450 X_BLANK_LINES_BEFORE in NUMBER,
451 X_BLANK_LINES_AFTER in NUMBER,
452 X_PROMPT in VARCHAR2,
453 X_START_DATE_ACTIVE in DATE,
454 X_END_DATE_ACTIVE in DATE,
455 X_OWNER in VARCHAR2, -- "SEED" or "CUSTOM"
456 X_LAST_UPDATE_DATE in DATE,
457 X_CUSTOM_MODE in VARCHAR2
458 ) is
459 l_f_luby number; -- entity owner in file
460 l_f_ludate date; -- entity update date in file
461 l_db_luby number; -- entity owner in db
462 l_db_ludate date; -- entity update date in db
463 l_rowid varchar2(64);
464 l_app_id number;
465 l_id number;
466 l_object_version_number number;
467
468 begin
469
470 -- look up application id
471 select APPLICATION_ID
472 into l_app_id
473 from FND_APPLICATION
474 where APPLICATION_SHORT_NAME = X_ATTRIBUTE_APPLICATION_CODE;
475
476 -- Translate owner to file_last_updated_by
477 if (x_owner = 'SEED') then
478 l_f_luby := 1;
479 L_USER_ID_FOR_SEED := 1;
480 else
481 l_f_luby := 0;
482 end if;
483
484 -- Get last update date of ldt entity
485 l_f_ludate := nvl(x_last_update_date, sysdate);
486
487 begin
488 select STYLE_FMT_LAYOUT_ID, LAST_UPDATED_BY, LAST_UPDATE_DATE, OBJECT_VERSION_NUMBER
489 into l_id, l_db_luby, l_db_ludate, l_object_version_number
490 from HZ_STYLE_FMT_LAYOUTS_B
491 where STYLE_FORMAT_CODE = X_STYLE_FORMAT_CODE
492 and VARIATION_NUMBER = X_VARIATION_NUMBER
493 and ATTRIBUTE_CODE = X_ATTRIBUTE_CODE
494 and ATTRIBUTE_APPLICATION_ID = l_app_id;
495
496
497 -- Update record, honoring customization mode.
498 -- Record should be updated only if:
499 -- a. CUSTOM_MODE = FORCE, or
500 -- b. file owner is CUSTOM, db owner is SEED
501 -- c. owners are the same, and file_date > db_date
502
503 if ((x_custom_mode = 'FORCE') or
504 ((l_f_luby = 0) and (l_db_luby = 1)) or
505 ((l_f_luby = l_db_luby) and (l_f_ludate > l_db_ludate)))
506 then
507 hz_style_fmt_layouts_pkg.update_row (
508 X_STYLE_FMT_LAYOUT_ID => l_id,
509 X_STYLE_FORMAT_CODE => X_STYLE_FORMAT_CODE,
510 X_VARIATION_NUMBER => X_VARIATION_NUMBER,
511 X_ATTRIBUTE_CODE => X_ATTRIBUTE_CODE,
512 X_ATTRIBUTE_APPLICATION_ID => l_app_id,
513 X_LINE_NUMBER => X_LINE_NUMBER,
514 X_POSITION => X_POSITION,
515 X_MANDATORY_FLAG => X_MANDATORY_FLAG,
516 X_USE_INITIAL_FLAG => X_USE_INITIAL_FLAG,
517 X_UPPERCASE_FLAG => X_UPPERCASE_FLAG,
518 X_TRANSFORM_FUNCTION => X_TRANSFORM_FUNCTION,
519 X_DELIMITER_BEFORE => X_DELIMITER_BEFORE,
520 X_DELIMITER_AFTER => X_DELIMITER_AFTER,
521 X_BLANK_LINES_BEFORE => X_BLANK_LINES_BEFORE,
522 X_BLANK_LINES_AFTER => X_BLANK_LINES_AFTER,
523 X_PROMPT => X_PROMPT,
524 X_START_DATE_ACTIVE => X_START_DATE_ACTIVE,
525 X_END_DATE_ACTIVE => X_END_DATE_ACTIVE,
526 X_OBJECT_VERSION_NUMBER => l_object_version_number
527 );
528 end if;
529
530 exception
531 when no_data_found then
532 -- record not found, insert in all cases
533 hz_style_fmt_layouts_pkg.insert_row(
534 x_rowid => l_rowid,
535 x_style_fmt_layout_id => l_id,
536 x_style_format_code => X_STYLE_FORMAT_CODE,
537 x_variation_number => X_VARIATION_NUMBER,
538 x_attribute_code => X_ATTRIBUTE_CODE,
539 x_attribute_application_id => l_app_id,
540 x_line_number => X_LINE_NUMBER,
541 x_position => X_POSITION,
542 x_mandatory_flag => X_MANDATORY_FLAG,
543 x_use_initial_flag => X_USE_INITIAL_FLAG,
544 x_uppercase_flag => X_UPPERCASE_FLAG,
545 x_transform_function => X_TRANSFORM_FUNCTION,
546 x_delimiter_before => X_DELIMITER_BEFORE,
547 x_delimiter_after => X_DELIMITER_AFTER,
548 x_blank_lines_before => X_BLANK_LINES_BEFORE,
549 x_blank_lines_after => X_BLANK_LINES_AFTER,
550 x_prompt => X_PROMPT,
551 x_start_date_active => X_START_DATE_ACTIVE,
552 x_end_date_active => X_END_DATE_ACTIVE,
553 x_object_version_number => 1
554 );
555 end;
556
557 end LOAD_ROW;
558
559 procedure TRANSLATE_ROW (
560 X_STYLE_FORMAT_CODE in VARCHAR2,
561 X_VARIATION_NUMBER in NUMBER,
562 X_ATTRIBUTE_CODE in VARCHAR2,
563 X_ATTRIBUTE_APPLICATION_CODE in VARCHAR2,
564 X_PROMPT in VARCHAR2,
565 X_OWNER in VARCHAR2, -- "SEED" or "CUSTOM"
566 X_LAST_UPDATE_DATE in DATE,
567 X_CUSTOM_MODE in VARCHAR2
568 ) is
569 l_f_luby number; -- entity owner in file
570 l_f_ludate date; -- entity update date in file
571 l_db_luby number; -- entity owner in db
572 l_db_ludate date; -- entity update date in db
573 l_app_id number;
574 l_id number;
575 begin
576
577 -- look up application id
578 select APPLICATION_ID
579 into l_app_id
580 from FND_APPLICATION
581 where APPLICATION_SHORT_NAME = X_ATTRIBUTE_APPLICATION_CODE;
582
583 -- Translate owner to file_last_updated_by
584 if (x_owner = 'SEED') then
585 l_f_luby := 1;
586 else
587 l_f_luby := 0;
588 end if;
589
590 -- Get last update date of ldt entity
591 l_f_ludate := nvl(x_last_update_date, sysdate);
592
593 begin
594 select STYLE_FMT_LAYOUT_ID into l_id
595 from HZ_STYLE_FMT_LAYOUTS_B
596 where STYLE_FORMAT_CODE = x_style_format_code
597 and VARIATION_NUMBER = x_variation_number
598 and ATTRIBUTE_CODE = x_attribute_code
599 and ATTRIBUTE_APPLICATION_ID = l_app_id;
600
601 select LAST_UPDATED_BY, LAST_UPDATE_DATE
602 into l_db_luby, l_db_ludate
603 from HZ_STYLE_FMT_LAYOUTS_TL
604 where STYLE_FMT_LAYOUT_ID = l_id
605 and LANGUAGE = userenv('LANG');
606
607 -- Update record, honoring customization mode.
608 -- Record should be updated only if:
609 -- a. CUSTOM_MODE = FORCE, or
610 -- b. file owner is CUSTOM, db owner is SEED
611 -- c. owners are the same, and file_date > db_date
612
613 if ((x_custom_mode = 'FORCE') or
614 ((l_f_luby = 0) and (l_db_luby = 1)) or
615 ((l_f_luby = l_db_luby) and (l_f_ludate > l_db_ludate)))
616 then
617 update HZ_STYLE_FMT_LAYOUTS_TL
618 set PROMPT = nvl(X_PROMPT,PROMPT),
619 LAST_UPDATE_DATE = l_f_ludate,
620 LAST_UPDATED_BY = l_f_luby,
621 LAST_UPDATE_LOGIN = 0,
622 SOURCE_LANG = userenv('LANG')
623 where style_fmt_layout_id = l_id
624 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
625 end if;
626 exception
627 when no_data_found then
628 null; -- no translation found. standards say do nothing.
629 end;
630
631 end TRANSLATE_ROW;
632
633 end HZ_STYLE_FMT_LAYOUTS_PKG;