[Home] [Help]
PACKAGE BODY: APPS.HZ_NAME_ADDRESS_FMT_VALIDATE
Source
1 PACKAGE BODY HZ_NAME_ADDRESS_FMT_VALIDATE AS
2 /*$Header: ARH2FMVB.pls 120.8 2006/05/10 08:34:30 ansingha noship $ */
3
4 -----------------------------------------
5 -- declaration of private global varibles
6 -----------------------------------------
7
8 --g_debug BOOLEAN := FALSE;
9 g_debug_count NUMBER := 0;
10
11
12 ------------------------------------
13 -- declaration of private procedures
14 ------------------------------------
15
16 /*PROCEDURE enable_debug;
17
18 PROCEDURE disable_debug;
19 */
20
21
22 --------------------------------------
23 -- private procedures and functions
24 --------------------------------------
25
26 /**
27 * PRIVATE PROCEDURE enable_debug
28 *
29 * DESCRIPTION
30 * Turn on debug mode.
31 *
32 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
33 * HZ_UTILITY_V2PUB.enable_debug
34 *
35 * MODIFICATION HISTORY
36 *
37 * 18-Jul-2001 Kate Shan o Created.
38 *
39 */
40
41 /*PROCEDURE enable_debug IS
42 BEGIN
43 g_debug_count := g_debug_count + 1;
44
45 IF g_debug_count = 1 THEN
46 IF fnd_profile.value('HZ_API_FILE_DEBUG_ON') = 'Y' OR
47 fnd_profile.value('HZ_API_DBMS_DEBUG_ON') = 'Y'
48 THEN
49 hz_utility_v2pub.enable_debug;
50 g_debug := TRUE;
51 END IF;
52 END IF;
53 END enable_debug;
54 */
55
56 /**
57 * PRIVATE PROCEDURE disable_debug
58 *
59 * DESCRIPTION
60 * Turn off debug mode.
61 *
62 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
63 * HZ_UTILITY_V2PUB.disable_debug
64 *
65 * MODIFICATION HISTORY
66 *
67 * 18-Jul-2001 Kate Shan o Created.
68 *
69 */
70
71 /*PROCEDURE disable_debug IS
72 BEGIN
73 IF g_debug THEN
74 g_debug_count := g_debug_count - 1;
75 IF g_debug_count = 0 THEN
76 hz_utility_v2pub.disable_debug;
77 g_debug := FALSE;
78 END IF;
79 END IF;
80 END disable_debug;
81 */
82
83 PROCEDURE check_greater_than_zero (
84 p_column IN VARCHAR2,
85 p_column_value IN NUMBER,
86 x_return_status IN OUT NOCOPY VARCHAR2
87 ) IS
88
89 BEGIN
90 IF p_column_value <= 0 THEN
91 FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_GREATER_THAN_ZERO' );
92 FND_MESSAGE.SET_TOKEN( 'COLUMN', p_column );
93 FND_MSG_PUB.ADD;
94 x_return_status := FND_API.G_RET_STS_ERROR;
95 END IF;
96
97 END check_greater_than_zero;
98
99 PROCEDURE get_updated_record (
100 p_style_fmt_locale_id IN NUMBER,
101 p_update_field_rec IN HZ_STYLE_FMT_LOCALE_V2PUB.STYLE_FMT_LOCALE_REC_TYPE,
102 x_updated_rec OUT NOCOPY HZ_STYLE_FMT_LOCALE_V2PUB.STYLE_FMT_LOCALE_REC_TYPE
103 ) IS
104 BEGIN
105 SELECT
106 style_fmt_locale_id,
107 style_format_code,
108 language_code,
109 territory_code,
110 DECODE ( p_update_field_rec.start_date_active, null, start_date_active, fnd_api.g_miss_date, null, p_update_field_rec.start_date_active),
111 DECODE ( p_update_field_rec.end_date_active, null, end_date_active, fnd_api.g_miss_date, null, p_update_field_rec.end_date_active)
112 INTO
113 x_updated_rec.style_fmt_locale_id,
114 x_updated_rec.style_format_code,
115 x_updated_rec.language_code,
116 x_updated_rec.territory_code,
117 x_updated_rec.start_date_active,
118 x_updated_rec.end_date_active
119 FROM HZ_STYLE_FMT_LOCALES
120 WHERE style_fmt_locale_id = p_style_fmt_locale_id;
121
122
123 END get_updated_record;
124
125 PROCEDURE get_updated_record (
126 p_style_fmt_layout_id IN NUMBER,
127 p_update_field_rec IN HZ_STYLE_FMT_LAYOUT_V2PUB.STYLE_FMT_LAYOUT_REC_TYPE,
128 x_updated_rec OUT NOCOPY HZ_STYLE_FMT_LAYOUT_V2PUB.STYLE_FMT_LAYOUT_REC_TYPE
129 ) IS
130 BEGIN
131
132 SELECT
133 b.style_fmt_layout_id,
134 b.style_format_code,
135 b.variation_number,
136 b.attribute_code,
137 b.attribute_application_id,
138 DECODE ( p_update_field_rec.line_number, null, b.line_number, fnd_api.g_miss_num, null, p_update_field_rec.line_number),
139 DECODE ( p_update_field_rec.position, null, b.position, fnd_api.g_miss_num, null, p_update_field_rec.position),
140 DECODE ( p_update_field_rec.mandatory_flag, null, b.mandatory_flag, fnd_api.g_miss_char, null, p_update_field_rec.mandatory_flag),
141 DECODE ( p_update_field_rec.use_initial_flag, null, b.use_initial_flag, fnd_api.g_miss_char, null, p_update_field_rec.use_initial_flag),
142 DECODE ( p_update_field_rec.uppercase_flag, null, b.uppercase_flag, fnd_api.g_miss_char, null, p_update_field_rec.uppercase_flag),
143 DECODE ( p_update_field_rec.transform_function, null, b.transform_function, fnd_api.g_miss_char, null, p_update_field_rec.transform_function),
144 DECODE ( p_update_field_rec.delimiter_before, null, b.delimiter_before, fnd_api.g_miss_char, null, p_update_field_rec.delimiter_before),
145 DECODE ( p_update_field_rec.delimiter_after, null, b.delimiter_after, fnd_api.g_miss_char, null, p_update_field_rec.delimiter_after),
146 DECODE ( p_update_field_rec.blank_lines_before, null, b.blank_lines_before, fnd_api.g_miss_num, null, p_update_field_rec.blank_lines_before),
147 DECODE ( p_update_field_rec.blank_lines_after, null, b.blank_lines_after, fnd_api.g_miss_num, null, p_update_field_rec.blank_lines_after),
148 DECODE ( p_update_field_rec.prompt, null, t.prompt, fnd_api.g_miss_char, null, p_update_field_rec.prompt),
149 DECODE ( p_update_field_rec.start_date_active, null, b.start_date_active, fnd_api.g_miss_date, null, p_update_field_rec.start_date_active),
150 DECODE ( p_update_field_rec.end_date_active, null, b.end_date_active, fnd_api.g_miss_date, null, p_update_field_rec.end_date_active)
151 INTO
152 x_updated_rec.style_fmt_layout_id,
153 x_updated_rec.style_format_code,
154 x_updated_rec.variation_number,
155 x_updated_rec.attribute_code,
156 x_updated_rec.attribute_application_id,
157 x_updated_rec.line_number,
158 x_updated_rec.position,
159 x_updated_rec.mandatory_flag,
160 x_updated_rec.use_initial_flag,
161 x_updated_rec.uppercase_flag,
162 x_updated_rec.transform_function,
163 x_updated_rec.delimiter_before,
164 x_updated_rec.delimiter_after,
165 x_updated_rec.blank_lines_before,
166 x_updated_rec.blank_lines_after,
167 x_updated_rec.prompt,
168 x_updated_rec.start_date_active,
169 x_updated_rec.end_date_active
170 FROM HZ_STYLE_FMT_LAYOUTS_B b , HZ_STYLE_FMT_LAYOUTS_TL t
171 WHERE b.style_fmt_layout_id =t.style_fmt_layout_id AND
172 t.style_fmt_layout_id = p_style_fmt_layout_id AND
173 t.language=userenv('LANG'); ---Bug No. 5178007
174
175
176 END get_updated_record;
177
178 --------------------------------------
179 -- declaration of public procedures and functions
180 --------------------------------------
181
182 --
183 -- PROCEDURE validate_style
184 --
185 -- DESCRIPTION
186 -- Validates style record. Checks for
187 -- uniqueness
188 -- mandatory columns
189 -- non-updateable fields
190 -- foreign key validations
191 -- other validations
192 --
193 -- EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
194 --
195 -- ARGUMENTS
196 -- IN:
197 -- p_create_update_flag Create update flag. 'C' = create. 'U' = update.
198 -- p_style_rec Style record.
199 -- p_rowid Rowid of the record (used only in update mode).
200 -- IN/OUT:
201 -- x_return_status Return status after the call. The status can
202 -- be FND_API.G_RET_STS_SUCCESS (success),
203 -- FND_API.G_RET_STS_ERROR (error),
204 -- FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
205 --
206 -- NOTES
207 --
208 -- MODIFICATION HISTORY
209 --
210 -- 18-JUL-2002 Kate Shan o Created.
211 --
212 --
213
214 PROCEDURE validate_style(
215 p_create_update_flag IN VARCHAR2,
216 p_style_rec IN HZ_STYLE_V2PUB.STYLE_REC_TYPE,
217 p_rowid IN ROWID,
218 x_return_status IN OUT NOCOPY VARCHAR2
219 ) IS
220
221 l_dummy VARCHAR2(1);
222 l_style_code HZ_STYLES_B.style_code%TYPE;
223 l_style_name HZ_STYLES_TL.style_name%TYPE;
224 l_database_object_name HZ_STYLES_B.database_object_name%TYPE;
225 l_debug_prefix VARCHAR2(30) := '';
226
227 BEGIN
228
229 --enable_debug;
230
231 -- Debug info.
232 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
233 hz_utility_v2pub.debug(p_message=>'validate_style (+)',
234 p_prefix=>l_debug_prefix,
235 p_msg_level=>fnd_log.level_procedure);
236 END IF;
237
238 -- select columns needed to be checked from table during update
239
240 IF (p_create_update_flag = 'U') THEN
241 SELECT b.style_code, t.style_name,
242 b.database_object_name
243 INTO l_style_code,
244 l_style_name,
245 l_database_object_name
246 FROM HZ_STYLES_B b , HZ_STYLES_TL t
247 WHERE b.ROWID = p_rowid
248 AND t.style_code = b.style_code
249 AND t.language = userenv('LANG');
250
251 END IF;
252
253 -----------------------------
254 -- validate style_code
255 -----------------------------
256
257 -- style_code is mandatory
258 IF (p_create_update_flag = 'C') THEN
259 HZ_UTILITY_V2PUB.validate_mandatory (
260 p_create_update_flag => p_create_update_flag,
261 p_column => 'style_code',
262 p_column_value => p_style_rec.style_code,
263 x_return_status => x_return_status);
264
265 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
266 hz_utility_v2pub.debug(p_message=>'style_code is mandatory. ' ||
267 'x_return_status = ' || x_return_status,
268 p_prefix =>l_debug_prefix,
269 p_msg_level=>fnd_log.level_statement);
270 END IF;
271 END IF;
272
273 -- style_code is non-updateable field
274 IF p_create_update_flag = 'U' AND
275 p_style_rec.style_code IS NOT NULL
276 THEN
277 HZ_UTILITY_V2PUB.validate_nonupdateable (
278 p_column => 'style_code',
279 p_column_value => p_style_rec.style_code,
280 p_old_column_value => l_style_code,
281 x_return_status => x_return_status);
282
283 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
284 hz_utility_v2pub.debug(p_message=>'style_code is non-updateable. ' ||
285 'x_return_status = ' || x_return_status,
286 p_prefix =>l_debug_prefix,
287 p_msg_level=>fnd_log.level_statement);
288 END IF;
289 END IF;
290
291 -- style_code is unique
292 IF p_create_update_flag = 'C' AND
293 p_style_rec.style_code IS NOT NULL
294 THEN
295 BEGIN
296 select 'Y' into l_dummy
297 from HZ_STYLES_B
298 where style_code = p_style_rec.style_code;
299
300 FND_MESSAGE.SET_NAME('AR', 'HZ_API_DUPLICATE_COLUMN');
301 FND_MESSAGE.SET_TOKEN('COLUMN', 'style_code');
302 FND_MSG_PUB.ADD;
303 x_return_status := FND_API.G_RET_STS_ERROR;
304
305 EXCEPTION
306 WHEN NO_DATA_FOUND THEN
307 NULL;
308 END;
309
310 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
311 hz_utility_v2pub.debug(p_message=>'style_code is unique during creation. ' ||
312 'x_return_status = ' || x_return_status,
313 p_prefix =>l_debug_prefix,
314 p_msg_level=>fnd_log.level_statement);
315 END IF;
316 END IF;
317
318 --------------------------------------
319 -- validate database_object_name
320 --------------------------------------
321
322 -- database_object_name is mandatory
323 IF (p_create_update_flag = 'C') THEN
324 HZ_UTILITY_V2PUB.validate_mandatory (
325 p_create_update_flag => p_create_update_flag,
326 p_column => 'database_object_name',
327 p_column_value => p_style_rec.database_object_name,
328 x_return_status => x_return_status);
329
330 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
331 hz_utility_v2pub.debug(p_message=>'database_object_name is mandatory. ' ||
332 'x_return_status = ' || x_return_status,
333 p_prefix =>l_debug_prefix,
334 p_msg_level=>fnd_log.level_statement);
335 END IF;
336 END IF;
337
338 -- database_object_name is non-updateable field
339 IF p_create_update_flag = 'U' AND
340 p_style_rec.database_object_name IS NOT NULL
341 THEN
342 HZ_UTILITY_V2PUB.validate_nonupdateable (
343 p_column => 'database_object_name',
344 p_column_value => p_style_rec.database_object_name,
345 p_old_column_value => l_database_object_name,
346 x_return_status => x_return_status);
347
348 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
349 hz_utility_v2pub.debug(p_message=>'database_object_name is non-updateable. ' ||
350 'x_return_status = ' || x_return_status,
351 p_prefix =>l_debug_prefix,
352 p_msg_level=>fnd_log.level_statement);
353 END IF;
354 END IF;
355
356 -- database_object_name has foreign key fnd_tables.table_name
357 IF p_style_rec.database_object_name IS NOT NULL
358 AND
359 p_style_rec.database_object_name <> fnd_api.g_miss_char
360 THEN
361 BEGIN
362
363 SELECT 'Y'
364 into l_dummy
365 FROM fnd_tables t
366 where t.table_name = p_style_rec.database_object_name ;
367
368 EXCEPTION
369 WHEN NO_DATA_FOUND THEN
370 fnd_message.set_name('AR', 'HZ_API_INVALID_FK');
371 fnd_message.set_token('FK', 'database_object_name');
372 fnd_message.set_token('COLUMN', 'table_name');
373 fnd_message.set_token('TABLE', 'fnd_tables');
374 fnd_msg_pub.add;
375 x_return_status := fnd_api.g_ret_sts_error;
376 END;
377
378 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
379 hz_utility_v2pub.debug(p_message=>'database_object_name has foreign key fnd_tables.table_name. ' ||
380 'x_return_status = ' || x_return_status,
381 p_prefix =>l_debug_prefix,
382 p_msg_level=>fnd_log.level_statement);
383 END IF;
384 END IF;
385
386 -----------------------------
387 -- validate style_name
388 -----------------------------
389
390 -- style_name is mandatory
391 IF (p_create_update_flag = 'C') THEN
392 HZ_UTILITY_V2PUB.validate_mandatory (
393 p_create_update_flag => p_create_update_flag,
394 p_column => 'style_name',
395 p_column_value => p_style_rec.style_name,
396 x_return_status => x_return_status);
397
398 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
399 hz_utility_v2pub.debug(p_message=>'style_name is mandatory. ' ||
400 'x_return_status = ' || x_return_status,
401 p_prefix =>l_debug_prefix,
402 p_msg_level=>fnd_log.level_statement);
403 END IF;
404 END IF;
405
406 -- style_name cannot be set to null during update
407 IF p_create_update_flag = 'U' THEN
408 HZ_UTILITY_V2PUB.validate_cannot_update_to_null (
409 p_column => 'style_name',
410 p_column_value => p_style_rec.style_name,
411 x_return_status => x_return_status);
412
413 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
414 hz_utility_v2pub.debug(p_message=>'style_name cannot be set to null during update. ' ||
415 'x_return_status = ' || x_return_status,
416 p_prefix =>l_debug_prefix,
417 p_msg_level=>fnd_log.level_statement);
418 END IF;
419
420 END IF;
421
422 -- style_name is unique within language
423 IF p_create_update_flag = 'C' OR
424 (p_create_update_flag = 'U' AND
425 p_style_rec.style_name is not null AND
426 p_style_rec.style_name <> l_style_name)
427 THEN
428 BEGIN
429 select 'Y' into l_dummy
430 from HZ_STYLES_TL
431 where style_name = p_style_rec.style_name
432 and language = userenv('LANG');
433
434 FND_MESSAGE.SET_NAME('AR', 'HZ_STYLE_NAME_DUP');
435 FND_MSG_PUB.ADD;
436 x_return_status := FND_API.G_RET_STS_ERROR;
437
438 EXCEPTION
439 WHEN NO_DATA_FOUND THEN
440 NULL;
441 END;
442 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
443 hz_utility_v2pub.debug(p_message=>'style_name is unique within language. ' ||
444 'x_return_status = ' || x_return_status,
445 p_prefix =>l_debug_prefix,
446 p_msg_level=>fnd_log.level_statement);
447 END IF;
448 END IF;
449 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
450 hz_utility_v2pub.debug(p_message=>'validate_style (-)',
451 p_prefix=>l_debug_prefix,
452 p_msg_level=>fnd_log.level_procedure);
453 END IF;
454
455 --disable_debug;
456
457 END validate_style;
458
459
460 --
461 -- PROCEDURE validate_style_format
462 --
463 -- DESCRIPTION
464 -- Validates style record. Checks for
465 -- uniqueness
466 -- mandatory columns
467 -- non-updateable fields
468 -- foreign key validations
469 -- other validations
470 --
471 -- EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
472 --
473 -- ARGUMENTS
474 -- IN:
475 -- p_create_update_flag Create update flag. 'C' = create. 'U' = update.
476 -- p_style_format_rec Style Format record.
477 -- p_rowid Rowid of the record (used only in update mode).
478 -- IN/OUT:
479 -- x_return_status Return status after the call. The status can
480 -- be FND_API.G_RET_STS_SUCCESS (success),
481 -- FND_API.G_RET_STS_ERROR (error),
482 -- FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
483 --
484 -- NOTES
485 --
486 -- MODIFICATION HISTORY
487 --
488 -- 18-JUL-2002 Kate Shan o Created.
489 --
490 --
491
492 PROCEDURE validate_style_format(
493 p_create_update_flag IN VARCHAR2,
494 p_style_format_rec IN HZ_STYLE_FORMAT_V2PUB.STYLE_FORMAT_REC_TYPE,
495 p_rowid IN ROWID,
496 x_return_status IN OUT NOCOPY VARCHAR2
497 )IS
498 l_dummy VARCHAR2(1);
499 l_style_format_code HZ_STYLE_FORMATS_B.style_format_code%TYPE;
500 l_style_code HZ_STYLE_FORMATS_B.style_code%TYPE;
501 l_default_flag HZ_STYLE_FORMATS_B.default_flag%TYPE;
502 l_style_format_name HZ_STYLE_FORMATS_TL.style_format_name%TYPE;
503 l_debug_prefix VARCHAR2(30) := '';
504
505 BEGIN
506
507 --enable_debug;
508
509 -- Debug info.
510 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
511 hz_utility_v2pub.debug(p_message=>'validate_style_format (+)',
512 p_prefix=>l_debug_prefix,
513 p_msg_level=>fnd_log.level_procedure);
514 END IF;
515
516 -- select columns needed to be checked from table during update
517
518 IF (p_create_update_flag = 'U') THEN
519 SELECT b.style_format_code,
520 b.style_code,
521 b.default_flag,
522 t.style_format_name
523 INTO l_style_format_code,
524 l_style_code,
525 l_default_flag,
526 l_style_format_name
527 FROM HZ_STYLE_FORMATS_B b , HZ_STYLE_FORMATS_TL t
528 WHERE b.ROWID = p_rowid
529 AND t.style_format_code = b.style_format_code
530 AND t.language = userenv('LANG');
531
532 END IF;
533
534 -----------------------------
535 -- validate style_format_code
536 -----------------------------
537
538 -- style_format_code is mandatory
539 IF (p_create_update_flag = 'C') THEN
540 HZ_UTILITY_V2PUB.validate_mandatory (
541 p_create_update_flag => p_create_update_flag,
542 p_column => 'style_format_code',
543 p_column_value => p_style_format_rec.style_format_code,
544 x_return_status => x_return_status);
545
546 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
547 hz_utility_v2pub.debug(p_message=>'style_format_code is mandatory. ' ||
548 'x_return_status = ' || x_return_status,
549 p_prefix =>l_debug_prefix,
550 p_msg_level=>fnd_log.level_statement);
551 END IF;
552 END IF;
553
554 -- style_format_code is non-updateable field
555
556 IF p_create_update_flag = 'U' AND
557 p_style_format_rec.style_format_code IS NOT NULL
558 THEN
559 HZ_UTILITY_V2PUB.validate_nonupdateable (
560 p_column => 'style_format_code',
561 p_column_value => p_style_format_rec.style_format_code,
562 p_old_column_value => l_style_format_code,
563 x_return_status => x_return_status);
564
565 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
566 hz_utility_v2pub.debug(p_message=>'l_style_format_code is non-updateable. ' ||
567 'x_return_status = ' || x_return_status,
568 p_prefix =>l_debug_prefix,
569 p_msg_level=>fnd_log.level_statement);
570 END IF;
571 END IF;
572
573 -- l_style_format_code is unique
574 IF p_create_update_flag = 'C' AND
575 p_style_format_rec.style_format_code IS NOT NULL
576 THEN
577 BEGIN
578 select 'Y' into l_dummy
579 from HZ_STYLE_FORMATS_B
580 where style_format_code = p_style_format_rec.style_format_code;
581
582 FND_MESSAGE.SET_NAME('AR', 'HZ_API_DUPLICATE_COLUMN');
583 FND_MESSAGE.SET_TOKEN('COLUMN', 'style_format_code');
584 FND_MSG_PUB.ADD;
585 x_return_status := FND_API.G_RET_STS_ERROR;
586
587 EXCEPTION
588 WHEN NO_DATA_FOUND THEN
589 NULL;
590 END;
591
592 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
593 hz_utility_v2pub.debug(p_message=>'style_format_code is unique during creation. ' ||
594 'x_return_status = ' || x_return_status,
595 p_prefix =>l_debug_prefix,
596 p_msg_level=>fnd_log.level_statement);
597 END IF;
598 END IF;
599
600 -----------------------------
601 -- validate style_code
602 -----------------------------
603
604 -- style_code is mandatory
605 IF (p_create_update_flag = 'C') THEN
606 HZ_UTILITY_V2PUB.validate_mandatory (
607 p_create_update_flag => p_create_update_flag,
608 p_column => 'style_code',
609 p_column_value => p_style_format_rec.style_code,
610 x_return_status => x_return_status);
611
612 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
613 hz_utility_v2pub.debug(p_message=>'style_code is mandatory. ' ||
614 'x_return_status = ' || x_return_status,
615 p_prefix =>l_debug_prefix,
616 p_msg_level=>fnd_log.level_statement);
617 END IF;
618 END IF;
619
620 -- style_code is non-updateable field
621 IF p_create_update_flag = 'U' AND
622 p_style_format_rec.style_code IS NOT NULL
623 THEN
624 HZ_UTILITY_V2PUB.validate_nonupdateable (
625 p_column => 'style_code',
626 p_column_value => p_style_format_rec.style_code,
627 p_old_column_value => l_style_code,
628 x_return_status => x_return_status);
629
630 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
631 hz_utility_v2pub.debug(p_message=>'style_code is non-updateable. ' ||
632 'x_return_status = ' || x_return_status,
633 p_prefix =>l_debug_prefix,
634 p_msg_level=>fnd_log.level_statement);
635 END IF;
636 END IF;
637
638 -- style_code is foreign key of hz_styles_b
639 -- Do not need to check during update because style_code is
640 -- non-updateable.
641 IF p_create_update_flag = 'C'
642 AND
643 p_style_format_rec.style_code IS NOT NULL
644 AND
645 p_style_format_rec.style_code <> fnd_api.g_miss_CHAR
646 THEN
647 BEGIN
648 SELECT 'Y'
649 INTO l_dummy
650 FROM HZ_STYLES_B
651 WHERE style_code = p_style_format_rec.style_code;
652 EXCEPTION
653 WHEN NO_DATA_FOUND THEN
654 fnd_message.set_name('AR', 'HZ_API_INVALID_FK');
655 fnd_message.set_token('FK', 'style_code');
656 fnd_message.set_token('COLUMN', 'style_code');
657 fnd_message.set_token('TABLE', 'hz_styles_b');
658 fnd_msg_pub.add;
659 x_return_status := fnd_api.g_ret_sts_error;
660 END;
661
662 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
663 hz_utility_v2pub.debug(p_message=>'style_code is foreign key of hz_styles_b. ' ||
664 'x_return_status = ' || x_return_status,
665 p_prefix =>l_debug_prefix,
666 p_msg_level=>fnd_log.level_statement);
667 END IF;
668 END IF;
669
670 -----------------------------
671 -- validate default_flag
672 -----------------------------
673
674 -- default_flag is mandatory
675 IF (p_create_update_flag = 'C') THEN
676 HZ_UTILITY_V2PUB.validate_mandatory (
677 p_create_update_flag => p_create_update_flag,
678 p_column => 'default_flag',
679 p_column_value => p_style_format_rec.default_flag,
680 x_return_status => x_return_status);
681
682
683 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
684 hz_utility_v2pub.debug(p_message=>'default_flag is mandatory. ' ||
685 'x_return_status = ' || x_return_status,
686 p_prefix =>l_debug_prefix,
687 p_msg_level=>fnd_log.level_statement);
688 END IF;
689 END IF;
690
691 -- default_flag is lookup code in lookup type YES/NO
692 hz_utility_v2pub.validate_lookup (
693 p_column => 'default_flag',
694 p_lookup_type => 'YES/NO',
695 p_column_value => p_style_format_rec.default_flag,
696 x_return_status => x_return_status);
697
698 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
699 hz_utility_v2pub.debug(p_message=>'default_flag is lookup code in lookup type YES/NO. ' ||
700 'x_return_status = ' || x_return_status,
701 p_prefix =>l_debug_prefix,
702 p_msg_level=>fnd_log.level_statement);
703 END IF;
704
705 -- default_flag cannot be set to null during update
706 IF p_create_update_flag = 'U' THEN
707 HZ_UTILITY_V2PUB.validate_cannot_update_to_null (
708 p_column => 'default_flag',
709 p_column_value => p_style_format_rec.default_flag,
710 x_return_status => x_return_status);
711
712 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
713 hz_utility_v2pub.debug(p_message=>'default_flag cannot be set to null during update. ' ||
714 'x_return_status = ' || x_return_status,
715 p_prefix =>l_debug_prefix,
716 p_msg_level=>fnd_log.level_statement);
717 END IF;
718
719 END IF;
720
721 -- default_flag cannot be set from Yes to No
722 IF p_create_update_flag = 'U' AND
723 p_style_format_rec.default_flag = 'N' AND
724 l_default_flag = 'Y'
725 THEN
726 fnd_message.set_name('AR', 'HZ_STL_FMT_FLAG_NOT_Y_TO_N');
727 fnd_msg_pub.add;
728 x_return_status := fnd_api.g_ret_sts_error;
729
730 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
731 hz_utility_v2pub.debug(p_message=>'default_flag cannot be set from Yes to No. ' ||
732 'x_return_status = ' || x_return_status,
733 p_prefix =>l_debug_prefix,
734 p_msg_level=>fnd_log.level_statement);
735 END IF;
736
737 END IF;
738 -----------------------------
739 -- validate style_format_name
740 -----------------------------
741
742 -- style_format_name is mandatory
743 IF (p_create_update_flag = 'C') THEN
744 HZ_UTILITY_V2PUB.validate_mandatory (
745 p_create_update_flag => p_create_update_flag,
746 p_column => 'style_format_name',
747 p_column_value => p_style_format_rec.style_format_name,
748 x_return_status => x_return_status);
749
750 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
751 hz_utility_v2pub.debug(p_message=>'style_format_name is mandatory. ' ||
752 'x_return_status = ' || x_return_status,
753 p_prefix =>l_debug_prefix,
754 p_msg_level=>fnd_log.level_statement);
755 END IF;
756 END IF;
757
758 -- style_format_name cannot be set to null during update
759 IF p_create_update_flag = 'U' THEN
760 HZ_UTILITY_V2PUB.validate_cannot_update_to_null (
761 p_column => 'style_format_name',
762 p_column_value => p_style_format_rec.style_format_name,
763 x_return_status => x_return_status);
764
765 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
766 hz_utility_v2pub.debug(p_message=>'style_format_name cannot be set to null during update. ' ||
767 'x_return_status = ' || x_return_status,
768 p_prefix =>l_debug_prefix,
769 p_msg_level=>fnd_log.level_statement);
770 END IF;
771
772 END IF;
773
774 -- style_format_name is unique within language
775 IF p_create_update_flag = 'C' OR
776 (p_create_update_flag = 'U' AND
777 p_style_format_rec.style_format_name is not null AND
778 p_style_format_rec.style_format_name <> l_style_format_name)
779 THEN
780 BEGIN
781 select 'Y' into l_dummy
782 from HZ_STYLE_FORMATS_TL
783 where style_format_name = p_style_format_rec.style_format_name
784 and language = userenv('LANG');
785
786 FND_MESSAGE.SET_NAME('AR', 'HZ_STYLE_FMT_NAME_DUP');
787 FND_MSG_PUB.ADD;
788 x_return_status := FND_API.G_RET_STS_ERROR;
789
790 EXCEPTION
791 WHEN NO_DATA_FOUND THEN
792 NULL;
793 END;
794
795 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
796 hz_utility_v2pub.debug(p_message=>'style_format_name is unique within language. ' ||
797 'x_return_status = ' || x_return_status,
798 p_prefix =>l_debug_prefix,
799 p_msg_level=>fnd_log.level_statement);
800 END IF;
801 END IF;
802
803 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
804 hz_utility_v2pub.debug(p_message=>'validate_style_format (-)',
805 p_prefix=>l_debug_prefix,
806 p_msg_level=>fnd_log.level_procedure);
807 END IF;
808
809 --disable_debug;
810 END validate_style_format;
811
812
813 --
814 -- PROCEDURE validate_style_fmt_locale
815 --
816 -- DESCRIPTION
817 -- Validates style record. Checks for
818 -- uniqueness
819 -- mandatory columns
820 -- non-updateable fields
821 -- foreign key validations
822 -- other validations
823 --
824 -- EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
825 --
826 -- ARGUMENTS
827 -- IN:
828 -- p_create_update_flag Create update flag. 'C' = create. 'U' = update.
829 -- p_style_fmt_locale_rec Style Locale record.
830 -- p_rowid Rowid of the record (used only in update mode).
831 -- IN/OUT:
832 -- x_return_status Return status after the call. The status can
833 -- be FND_API.G_RET_STS_SUCCESS (success),
834 -- FND_API.G_RET_STS_ERROR (error),
835 -- FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
836 --
837 -- NOTES
838 --
839 -- MODIFICATION HISTORY
840 --
841 -- 18-JUL-2002 Kate Shan o Created.
842 --
843 --
844
845 PROCEDURE validate_style_fmt_locale(
846 p_create_update_flag IN VARCHAR2,
847 p_style_fmt_locale_rec IN HZ_STYLE_FMT_LOCALE_V2PUB.STYLE_FMT_LOCALE_REC_TYPE,
848 p_rowid IN ROWID,
849 x_return_status IN OUT NOCOPY VARCHAR2
850 ) IS
851
852 l_dummy VARCHAR2(1);
853 l_style_fmt_locale_id HZ_STYLE_FMT_LOCALES.style_fmt_locale_id%TYPE;
854 l_style_format_code HZ_STYLE_FMT_LOCALES.style_format_code%TYPE;
855 l_language_code HZ_STYLE_FMT_LOCALES.language_code%TYPE;
856 l_territory_code HZ_STYLE_FMT_LOCALES.territory_code%TYPE;
857 l_start_date_active date;
858 l_end_date_active date;
859 l_debug_prefix VARCHAR2(30) := '';
860 l_dup_style_fmt_locale_id HZ_STYLE_FMT_LOCALES.style_fmt_locale_id%TYPE;
861 l_updated_stl_fmt_rec HZ_STYLE_FMT_LOCALE_V2PUB.STYLE_FMT_LOCALE_REC_TYPE;
862
863 CURSOR c_dup (p_style_fmt_locale_id IN NUMBER) IS
864 SELECT 'Y'
865 FROM hz_style_fmt_locales hsfl
866 WHERE hsfl.style_fmt_locale_id = p_style_fmt_locale_id;
867
868
869 BEGIN
870 --enable_debug;
871
872 -- Debug info.
873 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
874 hz_utility_v2pub.debug(p_message=>'validate_style_fmt_locale (+)',
875 p_prefix=>l_debug_prefix,
876 p_msg_level=>fnd_log.level_procedure);
877 END IF;
878
879 -- select columns needed to be checked from table during update
880
881 IF (p_create_update_flag = 'U') THEN
882 SELECT style_fmt_locale_id,
883 style_format_code,
884 language_code,
885 territory_code,
886 start_date_active,
887 end_date_active
888 INTO
889 l_style_fmt_locale_id,
890 l_style_format_code,
891 l_language_code,
892 l_territory_code,
893 l_start_date_active,
894 l_end_date_active
895 FROM HZ_STYLE_FMT_LOCALES
896 WHERE ROWID = p_rowid;
897 END IF;
898
899 -------------------------------
900 -- validate style_fmt_locale_id
901 -------------------------------
902
903 -- If primary key value is passed, check for uniqueness.
904 -- If primary key value is not passed, it will be generated
905 -- from sequence by table handler.
906
907 IF p_create_update_flag = 'C' THEN
908 IF p_style_fmt_locale_rec.style_fmt_locale_id IS NOT NULL AND
909 p_style_fmt_locale_rec.style_fmt_locale_id <> fnd_api.g_miss_num
910 THEN
911 OPEN c_dup (p_style_fmt_locale_rec.style_fmt_locale_id);
912 FETCH c_dup INTO l_dummy;
913 -- key is not unique, push an error onto the stack.
914 IF NVL(c_dup%FOUND, FALSE) THEN
915 fnd_message.set_name('AR', 'HZ_API_DUPLICATE_COLUMN');
916 fnd_message.set_token('COLUMN', 'style_fmt_locale_id');
917 fnd_msg_pub.add;
918 x_return_status := fnd_api.g_ret_sts_error;
919 END IF;
920 CLOSE c_dup;
921 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
922 hz_utility_v2pub.debug(p_message=>'check that style_fmt_locale_id is unique during creation. ' ||
923 ' x_return_status = ' || x_return_status,
924 p_prefix =>l_debug_prefix,
925 p_msg_level=>fnd_log.level_statement);
926 END IF;
927 END IF;
928 END IF;
929
930 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
931 hz_utility_v2pub.debug(p_message=>'(+) after validate style_fmt_locale_id ... ' ||
932 'x_return_status = ' || x_return_status,
933 p_prefix =>l_debug_prefix,
934 p_msg_level=>fnd_log.level_statement);
935 END IF;
936
937 -----------------------------
938 -- validate style_format_code
939 -----------------------------
940
941 -- style_format_code is mandatory
942
943 IF (p_create_update_flag = 'C') THEN
944 HZ_UTILITY_V2PUB.validate_mandatory (
945 p_create_update_flag => p_create_update_flag,
946 p_column => 'style_format_code',
947 p_column_value => p_style_fmt_locale_rec.style_format_code,
948 x_return_status => x_return_status);
949
950 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
951 hz_utility_v2pub.debug(p_message=>'style_format_code is mandatory. ' ||
952 'x_return_status = ' || x_return_status,
953 p_prefix =>l_debug_prefix,
954 p_msg_level=>fnd_log.level_statement);
955 END IF;
956 END IF;
957
958 -- style_format_code is non-updateable field
959 IF p_create_update_flag = 'U' AND
960 p_style_fmt_locale_rec.style_format_code IS NOT NULL
961 THEN
962 HZ_UTILITY_V2PUB.validate_nonupdateable (
963 p_column => 'style_format_code',
964 p_column_value => p_style_fmt_locale_rec.style_format_code,
965 p_old_column_value => l_style_format_code,
966 x_return_status => x_return_status);
967
968 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
969 hz_utility_v2pub.debug(p_message=>'style_format_code is non-updateable. ' ||
970 'x_return_status = ' || x_return_status,
971 p_prefix =>l_debug_prefix,
972 p_msg_level=>fnd_log.level_statement);
973 END IF;
974 END IF;
975
976 -- style_format_code is foreign key of hz_style_formats_b
977 -- Do not need to check during update because style_format_code is
978 -- non-updateable.
979
980 IF p_create_update_flag = 'C'
981 AND
982 p_style_fmt_locale_rec.style_format_code IS NOT NULL
983 AND
984 p_style_fmt_locale_rec.style_format_code <> fnd_api.g_miss_CHAR
985 THEN
986 BEGIN
987 SELECT 'Y'
988 INTO l_dummy
989 FROM HZ_STYLE_FORMATS_B
990 WHERE style_format_code = p_style_fmt_locale_rec.style_format_code;
991 EXCEPTION
992 WHEN NO_DATA_FOUND THEN
993 fnd_message.set_name('AR', 'HZ_API_INVALID_FK');
994 fnd_message.set_token('FK', 'style_format_code');
995 fnd_message.set_token('COLUMN', 'style_format_code');
996 fnd_message.set_token('TABLE', 'hz_style_formats_b');
997 fnd_msg_pub.add;
998 x_return_status := fnd_api.g_ret_sts_error;
999 END;
1000 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1001 hz_utility_v2pub.debug(p_message=>'style_format_code is foreign key of hz_style_formats_b. ' ||
1002 'x_return_status = ' || x_return_status,
1003 p_prefix =>l_debug_prefix,
1004 p_msg_level=>fnd_log.level_statement);
1005 END IF;
1006 END IF;
1007
1008 -----------------------------
1009 -- validate language_code
1010 -----------------------------
1011
1012 -- language_code is non-updateable field
1013
1014 IF p_create_update_flag = 'U' AND
1015 p_style_fmt_locale_rec.language_code IS NOT NULL
1016 THEN
1017 HZ_UTILITY_V2PUB.validate_nonupdateable (
1018 p_column => 'language_code',
1019 p_column_value => p_style_fmt_locale_rec.language_code,
1020 p_old_column_value => l_language_code,
1021 x_return_status => x_return_status);
1022
1023 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1024 hz_utility_v2pub.debug(p_message=>'language_code is non-updateable. ' ||
1025 'x_return_status = ' || x_return_status,
1026 p_prefix =>l_debug_prefix,
1027 p_msg_level=>fnd_log.level_statement);
1028 END IF;
1029 END IF;
1030
1031 -- language_code is foreign key of fnd_languages
1032 -- Do not need to check during update because fnd_languages is
1033 -- non-updateable.
1034 IF p_create_update_flag = 'C'
1035 AND
1036 p_style_fmt_locale_rec.language_code IS NOT NULL
1037 AND
1038 p_style_fmt_locale_rec.language_code <> fnd_api.g_miss_CHAR
1039 THEN
1040 BEGIN
1041 SELECT 'Y'
1042 INTO l_dummy
1043 FROM FND_LANGUAGES
1044 WHERE language_code = p_style_fmt_locale_rec.language_code;
1045 EXCEPTION
1046 WHEN NO_DATA_FOUND THEN
1047 fnd_message.set_name('AR', 'HZ_API_INVALID_FK');
1048 fnd_message.set_token('FK', 'language_code');
1049 fnd_message.set_token('COLUMN', 'language_code');
1050 fnd_message.set_token('TABLE', 'fnd_languages');
1051 fnd_msg_pub.add;
1052 x_return_status := fnd_api.g_ret_sts_error;
1053 END;
1054 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1055 hz_utility_v2pub.debug(p_message=>'language_code is foreign key of fnd_languages. ' ||
1056 'x_return_status = ' || x_return_status,
1057 p_prefix =>l_debug_prefix,
1058 p_msg_level=>fnd_log.level_statement);
1059 END IF;
1060 END IF;
1061
1062 -----------------------------
1063 -- validate territory_code
1064 -----------------------------
1065
1066 -- territory_code is non-updateable field
1067
1068 IF p_create_update_flag = 'U' AND
1069 p_style_fmt_locale_rec.territory_code IS NOT NULL
1070 THEN
1071 HZ_UTILITY_V2PUB.validate_nonupdateable (
1072 p_column => 'territory_code',
1073 p_column_value => p_style_fmt_locale_rec.territory_code,
1074 p_old_column_value => l_territory_code,
1075 x_return_status => x_return_status);
1076
1077 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1078 hz_utility_v2pub.debug(p_message=>'territory_code is non-updateable. ' ||
1079 'x_return_status = ' || x_return_status,
1080 p_prefix =>l_debug_prefix,
1081 p_msg_level=>fnd_log.level_statement);
1082 END IF;
1083 END IF;
1084
1085 -- territory_code is foreign key of fnd_territories
1086 -- Do not need to check during update because fnd_territories is
1087 -- non-updateable.
1088 IF p_create_update_flag = 'C'
1089 AND
1090 p_style_fmt_locale_rec.territory_code IS NOT NULL
1091 AND
1092 p_style_fmt_locale_rec.territory_code <> fnd_api.g_miss_CHAR
1093 THEN
1094 BEGIN
1095 SELECT 'Y'
1096 INTO l_dummy
1097 FROM FND_TERRITORIES
1098 WHERE territory_code = p_style_fmt_locale_rec.territory_code;
1099 EXCEPTION
1100 WHEN NO_DATA_FOUND THEN
1101 fnd_message.set_name('AR', 'HZ_API_INVALID_FK');
1102 fnd_message.set_token('FK', 'territory_code');
1103 fnd_message.set_token('COLUMN', 'territory_code');
1104 fnd_message.set_token('TABLE', 'fnd_territories');
1105 fnd_msg_pub.add;
1106 x_return_status := fnd_api.g_ret_sts_error;
1107 END;
1108 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1109 hz_utility_v2pub.debug(p_message=>'territory_code is foreign key of fnd_territories. ' ||
1110 'x_return_status = ' || x_return_status,
1111 p_prefix =>l_debug_prefix,
1112 p_msg_level=>fnd_log.level_statement);
1113 END IF;
1114 END IF;
1115
1116 -------------------------------------------------------------
1117 -- language_code, territory_code validation
1118 -------------------------------------------------------------
1119
1120 -- Either language_code or territory_code must have a value
1121 IF p_create_update_flag = 'C' AND
1122 not ((p_style_fmt_locale_rec.language_code IS NOT NULL AND
1123 (p_style_fmt_locale_rec.language_code <> fnd_api.g_miss_char) OR
1124 (p_style_fmt_locale_rec.territory_code IS NOT NULL ) AND
1125 p_style_fmt_locale_rec.territory_code <> fnd_api.g_miss_char ) )
1126 THEN
1127 FND_MESSAGE.SET_NAME('AR', 'HZ_STL_FMT_LOC_MISSING_COLUMN');
1128 FND_MSG_PUB.ADD;
1129 x_return_status := FND_API.G_RET_STS_ERROR;
1130
1131 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1132 hz_utility_v2pub.debug(p_message=>'Either language_code or territory_code must have a value. ' ||
1133 'x_return_status = ' || x_return_status,
1134 p_prefix =>l_debug_prefix,
1135 p_msg_level=>fnd_log.level_statement);
1136 END IF;
1137
1138 END IF;
1139
1140
1141 -----------------------------
1142 -- validate start_date_active
1143 -----------------------------
1144
1145 -- start_date_active is mandatory
1146 IF (p_create_update_flag = 'C') THEN
1147 HZ_UTILITY_V2PUB.validate_mandatory (
1148 p_create_update_flag => p_create_update_flag,
1149 p_column => 'start_date_active',
1150 p_column_value => p_style_fmt_locale_rec.start_date_active,
1151 x_return_status => x_return_status);
1152
1153 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1154 hz_utility_v2pub.debug(p_message=>'start_date_active is mandatory. ' ||
1155 'x_return_status = ' || x_return_status,
1156 p_prefix =>l_debug_prefix,
1157 p_msg_level=>fnd_log.level_statement);
1158 END IF;
1159 END IF;
1160
1161 -- start_date_active cannot be set to null during update
1162 IF p_create_update_flag = 'U' THEN
1163 HZ_UTILITY_V2PUB.validate_cannot_update_to_null (
1164 p_column => 'start_date_active',
1165 p_column_value => p_style_fmt_locale_rec.start_date_active,
1166 x_return_status => x_return_status);
1167
1168 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1169 hz_utility_v2pub.debug(p_message=>'start_date_active cannot be set to null during update. ' ||
1170 'x_return_status = ' || x_return_status,
1171 p_prefix =>l_debug_prefix,
1172 p_msg_level=>fnd_log.level_statement);
1173 END IF;
1174
1175 END IF;
1176
1177 ----------------------------------
1178 -- start_date_active, end_date_active validation
1179 ----------------------------------
1180
1181 -- end_date_active must be null or greater than start date
1182 IF (p_create_update_flag = 'C') THEN
1183 IF p_style_fmt_locale_rec.end_date_active IS NOT NULL AND
1184 p_style_fmt_locale_rec.end_date_active <> fnd_api.g_miss_date AND
1185 p_style_fmt_locale_rec.end_date_active < p_style_fmt_locale_rec.start_date_active
1186 THEN
1187 fnd_message.set_name('AR', 'HZ_API_START_DATE_GREATER');
1188 fnd_msg_pub.add;
1189 x_return_status := fnd_api.g_ret_sts_error;
1190 END IF;
1191 ELSIF (p_create_update_flag = 'U') THEN
1192 -- old start_date_active, end_date_active has been selected from table
1193 -- and put into l_start_date_active, l_end_date_active
1194
1195 IF p_style_fmt_locale_rec.start_date_active <> fnd_api.g_miss_date
1196 AND p_style_fmt_locale_rec.start_date_active is not null
1197 THEN
1198 l_start_date_active := p_style_fmt_locale_rec.start_date_active;
1199 END IF;
1200
1201 IF p_style_fmt_locale_rec.end_date_active = fnd_api.g_miss_date
1202 THEN
1203 l_end_date_active := null;
1204 ELSIF p_style_fmt_locale_rec.end_date_active IS NOT NULL THEN
1205 l_end_date_active := p_style_fmt_locale_rec.end_date_active;
1206 END IF;
1207
1208 IF l_end_date_active IS NOT NULL
1209 AND l_end_date_active < l_start_date_active
1210 THEN
1211 fnd_message.set_name('AR', 'HZ_API_START_DATE_GREATER');
1212 fnd_msg_pub.add;
1213 x_return_status := fnd_api.g_ret_sts_error;
1214 END IF;
1215 END IF;
1216 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1217 hz_utility_v2pub.debug(p_message=>'end_date_active must be null or greater than start date. ' ||
1218 'x_return_status = ' || x_return_status,
1219 p_prefix =>l_debug_prefix,
1220 p_msg_level=>fnd_log.level_statement);
1221 END IF;
1222
1223 --------------------------------------------------------------------------------------------------
1224 -- style_format_code, language_code, territory_code validation, start_date_active, end_date_active
1225 --------------------------------------------------------------------------------------------------
1226
1227 -- combination of style_format_code, language_code, territory_code is unique in time range of
1228 -- start_date_active, end_date_active
1229
1230 IF p_create_update_flag = 'C' AND
1231 p_style_fmt_locale_rec.style_format_code IS NOT NULL AND
1232 ( p_style_fmt_locale_rec.language_code IS NOT NULL OR
1233 p_style_fmt_locale_rec.territory_code IS NOT NULL )
1234 THEN
1235 BEGIN
1236 select style_fmt_locale_id into l_dup_style_fmt_locale_id
1237 from HZ_STYLE_FMT_LOCALES
1238 where style_format_code = p_style_fmt_locale_rec.style_format_code AND
1239 decode(language_code, null, fnd_api.g_miss_char, language_code) = NVL( p_style_fmt_locale_rec.language_code, fnd_api.g_miss_char) AND
1240 decode(territory_code, null, fnd_api.g_miss_char, territory_code) = NVL(p_style_fmt_locale_rec.territory_code, fnd_api.g_miss_char) AND
1241 NOT ( ( p_style_fmt_locale_rec.end_date_active is not null and
1242 p_style_fmt_locale_rec.end_date_active <> fnd_api.g_miss_date and
1243 p_style_fmt_locale_rec.end_date_active < start_date_active ) OR
1244 ( end_date_active is not null and
1245 p_style_fmt_locale_rec.start_date_active > end_date_active )) AND
1246 rownum =1;
1247
1248 FND_MESSAGE.SET_NAME('AR', 'HZ_STYLE_LOC_DUPLICATE_RECORD');
1249 FND_MSG_PUB.ADD;
1250 x_return_status := FND_API.G_RET_STS_ERROR;
1251
1252 EXCEPTION
1253 WHEN NO_DATA_FOUND THEN
1254 NULL;
1255 END;
1256
1257 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1258 hz_utility_v2pub.debug(p_message=>'Another record exist for this style format layout with an overlapping date ranger. Please input a unique combination of style_format_code, language_code, territory_code validation' ||
1259 'x_return_status = ' || x_return_status,
1260 p_prefix =>l_debug_prefix,
1261 p_msg_level=>fnd_log.level_statement);
1262 END IF;
1263
1264 ELSIF p_create_update_flag = 'U' AND
1265 (p_style_fmt_locale_rec.start_date_active is not null OR
1266 p_style_fmt_locale_rec.end_date_active is not null )
1267 THEN
1268 get_updated_record (
1269 p_style_fmt_locale_id => p_style_fmt_locale_rec.style_fmt_locale_id,
1270 p_update_field_rec => p_style_fmt_locale_rec,
1271 x_updated_rec => l_updated_stl_fmt_rec );
1272
1273 BEGIN
1274 select style_fmt_locale_id into l_dup_style_fmt_locale_id
1275 from HZ_STYLE_FMT_LOCALES
1276 where style_fmt_locale_id <> l_updated_stl_fmt_rec.style_fmt_locale_id AND
1277 style_format_code = l_updated_stl_fmt_rec.style_format_code AND
1278 decode(language_code, null, fnd_api.g_miss_char, language_code) = NVL( l_updated_stl_fmt_rec.language_code, fnd_api.g_miss_char) AND
1279 decode(territory_code, null, fnd_api.g_miss_char, territory_code) = NVL(l_updated_stl_fmt_rec.territory_code, fnd_api.g_miss_char) AND
1280 NOT ( ( l_updated_stl_fmt_rec.end_date_active is not null and
1281 l_updated_stl_fmt_rec.end_date_active < start_date_active ) OR
1282 ( end_date_active is not null and
1283 l_updated_stl_fmt_rec.start_date_active > end_date_active )) AND
1284 rownum =1;
1285
1286 FND_MESSAGE.SET_NAME('AR', 'HZ_STYLE_DUPLICATE_RECORD');
1287 FND_MESSAGE.SET_TOKEN( 'TYPE', 'style format locale' );
1288 FND_MESSAGE.SET_TOKEN( 'COLUMN', 'style_fmt_locale_id' );
1289 FND_MESSAGE.SET_TOKEN( 'ID', to_char(l_dup_style_fmt_locale_id) );
1290 FND_MESSAGE.SET_TOKEN( 'ALLCOLUMNS', 'style_format_code, language_code, territory_code validation' );
1291
1292 FND_MSG_PUB.ADD;
1293 x_return_status := FND_API.G_RET_STS_ERROR;
1294
1295 EXCEPTION
1296 WHEN NO_DATA_FOUND THEN
1297 NULL;
1298 END;
1299
1300 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1301 hz_utility_v2pub.debug(p_message=>'Another record exist for this style format layout with an overlapping date ranger. Please input a unique combination of style_format_code, language_code, territory_code validation' ||
1302 'x_return_status = ' || x_return_status,
1303 p_prefix =>l_debug_prefix,
1304 p_msg_level=>fnd_log.level_statement);
1305 END IF;
1306
1307
1308 END IF;
1309
1310 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1311 hz_utility_v2pub.debug(p_message=>'validate_style_fmt_locale (-)',
1312 p_prefix=>l_debug_prefix,
1313 p_msg_level=>fnd_log.level_procedure);
1314 END IF;
1315
1316 --disable_debug;
1317 END validate_style_fmt_locale;
1318
1319 --
1320 -- PROCEDURE validate_style_fmt_variation
1321 --
1322 -- DESCRIPTION
1323 -- Validates style record. Checks for
1324 -- uniqueness
1325 -- mandatory columns
1326 -- non-updateable fields
1327 -- foreign key validations
1328 -- other validations
1329 --
1330 -- EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
1331 --
1332 -- ARGUMENTS
1333 -- IN:
1334 -- p_create_update_flag Create update flag. 'C' = create. 'U' = update.
1335 -- p_style_fmt_variation_rec Style Locale record.
1336 -- p_rowid Rowid of the record (used only in update mode).
1337 -- IN/OUT:
1338 -- x_return_status Return status after the call. The status can
1339 -- be FND_API.G_RET_STS_SUCCESS (success),
1340 -- FND_API.G_RET_STS_ERROR (error),
1341 -- FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
1342 --
1343 -- NOTES
1344 --
1345 -- MODIFICATION HISTORY
1346 --
1347 -- 18-JUL-2002 Kate Shan o Created.
1348 --
1349 --
1350
1351 PROCEDURE validate_style_fmt_variation(
1352 p_create_update_flag IN VARCHAR2,
1353 p_style_fmt_variation_rec IN HZ_STYLE_FMT_VARIATION_V2PUB.STYLE_FMT_VARIATION_REC_TYPE,
1354 p_rowid IN ROWID,
1355 x_return_status IN OUT NOCOPY VARCHAR2
1356 )IS
1357 l_style_format_code HZ_STYLE_FMT_VARIATIONS.style_format_code%TYPE;
1358 l_variation_number HZ_STYLE_FMT_VARIATIONS.variation_number%TYPE;
1359 l_variation_rank HZ_STYLE_FMT_VARIATIONS.variation_rank%TYPE;
1360 l_start_date_active HZ_STYLE_FMT_VARIATIONS.start_date_active%TYPE;
1361 l_end_date_active HZ_STYLE_FMT_VARIATIONS.end_date_active%TYPE;
1362 l_dummy VARCHAR2(1);
1363 l_debug_prefix VARCHAR2(30) := '';
1364
1365 BEGIN
1366
1367 --enable_debug;
1368
1369 -- Debug info.
1370 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1371 hz_utility_v2pub.debug(p_message=>'validate_style_fmt_variation (+)',
1372 p_prefix=>l_debug_prefix,
1373 p_msg_level=>fnd_log.level_procedure);
1374 END IF;
1375
1376 -- select columns needed to be checked from table during update
1377
1378 IF (p_create_update_flag = 'U') THEN
1379 SELECT
1380 style_format_code,
1381 variation_number,
1382 variation_rank,
1383 start_date_active,
1384 end_date_active
1385 INTO
1386 l_style_format_code,
1387 l_variation_number,
1388 l_variation_rank,
1389 l_start_date_active,
1390 l_end_date_active
1391 FROM HZ_STYLE_FMT_VARIATIONS
1392 WHERE ROWID = p_rowid;
1393 END IF;
1394
1395 -----------------------------
1396 -- validate style_format_code
1397 -----------------------------
1398
1399 -- style_format_code is mandatory
1400 IF (p_create_update_flag = 'C') THEN
1401 HZ_UTILITY_V2PUB.validate_mandatory (
1402 p_create_update_flag => p_create_update_flag,
1403 p_column => 'style_format_code',
1404 p_column_value => p_style_fmt_variation_rec.style_format_code,
1405 x_return_status => x_return_status);
1406
1407 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1408 hz_utility_v2pub.debug(p_message=>'style_format_code is mandatory. ' ||
1409 'x_return_status = ' || x_return_status,
1410 p_prefix =>l_debug_prefix,
1411 p_msg_level=>fnd_log.level_statement);
1412 END IF;
1413 END IF;
1414
1415 -- style_format_code is non-updateable field
1416
1417 IF p_create_update_flag = 'U' AND
1418 p_style_fmt_variation_rec.style_format_code IS NOT NULL
1419 THEN
1420 HZ_UTILITY_V2PUB.validate_nonupdateable (
1421 p_column => 'style_format_code',
1422 p_column_value => p_style_fmt_variation_rec.style_format_code,
1423 p_old_column_value => l_style_format_code,
1424 x_return_status => x_return_status);
1425
1426 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1427 hz_utility_v2pub.debug(p_message=>'l_style_format_code is non-updateable. ' ||
1428 'x_return_status = ' || x_return_status,
1429 p_prefix =>l_debug_prefix,
1430 p_msg_level=>fnd_log.level_statement);
1431 END IF;
1432 END IF;
1433
1434 -- style_format_code is foreign key of hz_style_formats_b
1435 -- Do not need to check during update because style_format_code is
1436 -- non-updateable.
1437 IF p_create_update_flag = 'C'
1438 AND
1439 p_style_fmt_variation_rec.style_format_code IS NOT NULL
1440 AND
1441 p_style_fmt_variation_rec.style_format_code <> fnd_api.g_miss_CHAR
1442 THEN
1443 BEGIN
1444 SELECT 'Y'
1445 INTO l_dummy
1446 FROM HZ_STYLE_FORMATS_B
1447 WHERE style_format_code = p_style_fmt_variation_rec.style_format_code;
1448 EXCEPTION
1449 WHEN NO_DATA_FOUND THEN
1450 fnd_message.set_name('AR', 'HZ_API_INVALID_FK');
1451 fnd_message.set_token('FK', 'style_format_code');
1452 fnd_message.set_token('COLUMN', 'style_format_code');
1453 fnd_message.set_token('TABLE', 'hz_style_formats_b');
1454 fnd_msg_pub.add;
1455 x_return_status := fnd_api.g_ret_sts_error;
1456 END;
1457 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1458 hz_utility_v2pub.debug(p_message=>'style_format_code is foreign key of hz_style_formats_b. ' ||
1459 'x_return_status = ' || x_return_status,
1460 p_prefix =>l_debug_prefix,
1461 p_msg_level=>fnd_log.level_statement);
1462 END IF;
1463 END IF;
1464
1465 -----------------------------
1466 -- validate variation_number
1467 -----------------------------
1468
1469 -- variation_number is mandatory
1470 IF (p_create_update_flag = 'C') THEN
1471 HZ_UTILITY_V2PUB.validate_mandatory (
1472 p_create_update_flag => p_create_update_flag,
1473 p_column => 'variation_number',
1474 p_column_value => p_style_fmt_variation_rec.variation_number,
1475 x_return_status => x_return_status);
1476
1477 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1478 hz_utility_v2pub.debug(p_message=>'variation_number is mandatory. ' ||
1479 'x_return_status = ' || x_return_status,
1480 p_prefix =>l_debug_prefix,
1481 p_msg_level=>fnd_log.level_statement);
1482 END IF;
1483 END IF;
1484
1485 -- variation_number is non-updateable field
1486
1487 IF p_create_update_flag = 'U' AND
1488 p_style_fmt_variation_rec.variation_number IS NOT NULL
1489 THEN
1490 HZ_UTILITY_V2PUB.validate_nonupdateable (
1491 p_column => 'variation_number',
1492 p_column_value => p_style_fmt_variation_rec.variation_number,
1493 p_old_column_value => l_variation_number,
1494 x_return_status => x_return_status);
1495
1496 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1497 hz_utility_v2pub.debug(p_message=>'l_variation_number is non-updateable. ' ||
1498 'x_return_status = ' || x_return_status,
1499 p_prefix =>l_debug_prefix,
1500 p_msg_level=>fnd_log.level_statement);
1501 END IF;
1502 END IF;
1503
1504 -- The combanition of variation_number style_format_code is unique
1505 IF p_create_update_flag = 'C'
1506 THEN
1507 BEGIN
1508 select 'Y' into l_dummy
1509 from HZ_STYLE_FMT_VARIATIONS
1510 where variation_number = p_style_fmt_variation_rec.variation_number
1511 and style_format_code = p_style_fmt_variation_rec.style_format_code;
1512
1513 FND_MESSAGE.SET_NAME('AR', 'HZ_VARIATION_NO_DUP');
1514 FND_MSG_PUB.ADD;
1515 x_return_status := FND_API.G_RET_STS_ERROR;
1516
1517 EXCEPTION
1518 WHEN NO_DATA_FOUND THEN
1519 NULL;
1520 END;
1521
1522 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1523 hz_utility_v2pub.debug(p_message=>'The combanition of variation_number, style_format_code is unique. ' ||
1524 'x_return_status = ' || x_return_status,
1525 p_prefix =>l_debug_prefix,
1526 p_msg_level=>fnd_log.level_statement);
1527 END IF;
1528 END IF;
1529
1530 -- variation_number should be > 0
1531 IF p_create_update_flag = 'C' AND
1532 p_style_fmt_variation_rec.variation_number is not null
1533 THEN
1534 check_greater_than_zero (
1535 p_column => 'variation_number',
1536 p_column_value => p_style_fmt_variation_rec.variation_number,
1537 x_return_status => x_return_status );
1538
1539 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1540 hz_utility_v2pub.debug(p_message=>'variation_number should be > 0.' ||'x_return_status = ' ||
1541 x_return_status,
1542 p_prefix =>l_debug_prefix,
1543 p_msg_level=>fnd_log.level_statement);
1544 END IF;
1545 END IF;
1546
1547
1548 ------------------------------
1549 -- variation_rank validation
1550 ------------------------------
1551
1552 -- variation_rank is mandatory
1553 IF (p_create_update_flag = 'C') THEN
1554 HZ_UTILITY_V2PUB.validate_mandatory (
1555 p_create_update_flag => p_create_update_flag,
1556 p_column => 'variation_rank',
1557 p_column_value => p_style_fmt_variation_rec.variation_rank,
1558 x_return_status => x_return_status);
1559
1560 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1561 hz_utility_v2pub.debug(p_message=>'variation_rank is mandatory. ' ||
1562 'x_return_status = ' || x_return_status,
1563 p_prefix =>l_debug_prefix,
1564 p_msg_level=>fnd_log.level_statement);
1565 END IF;
1566 END IF;
1567
1568 -- variation_rank is unique within style_format_code
1569 IF p_create_update_flag = 'C' OR
1570 (p_create_update_flag = 'U' AND
1571 p_style_fmt_variation_rec.variation_rank is not null AND
1572 p_style_fmt_variation_rec.variation_rank <> l_variation_rank)
1573 THEN
1574 BEGIN
1575 select 'Y' into l_dummy
1576 from HZ_STYLE_FMT_VARIATIONS
1577 where variation_rank = p_style_fmt_variation_rec.variation_rank
1578 and style_format_code = p_style_fmt_variation_rec.style_format_code;
1579
1580 FND_MESSAGE.SET_NAME('AR', 'HZ_VARIATION_RANK_DUP');
1581 FND_MSG_PUB.ADD;
1582 x_return_status := FND_API.G_RET_STS_ERROR;
1583
1584 EXCEPTION
1585 WHEN NO_DATA_FOUND THEN
1586 NULL;
1587 END;
1588
1589 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1590 hz_utility_v2pub.debug(p_message=>'variation_rank is unique within style_format_code. ' ||
1591 'x_return_status = ' || x_return_status,
1592 p_prefix =>l_debug_prefix,
1593 p_msg_level=>fnd_log.level_statement);
1594 END IF;
1595 END IF;
1596
1597 -- variation_rank cannot be set to null during update
1598 IF p_create_update_flag = 'U' THEN
1599 HZ_UTILITY_V2PUB.validate_cannot_update_to_null (
1600 p_column => 'variation_rank',
1601 p_column_value => p_style_fmt_variation_rec.variation_rank,
1602 x_return_status => x_return_status);
1603
1604 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1605 hz_utility_v2pub.debug(p_message=>'variation_rank cannot be set to null during update. ' ||
1606 'x_return_status = ' || x_return_status,
1607 p_prefix =>l_debug_prefix,
1608 p_msg_level=>fnd_log.level_statement);
1609 END IF;
1610
1611 END IF;
1612
1613 -- variation_rank should be > 0
1614 IF (p_create_update_flag = 'C' AND
1615 p_style_fmt_variation_rec.variation_rank is not null ) OR
1616 (p_create_update_flag = 'U' AND
1617 p_style_fmt_variation_rec.variation_rank is not null AND
1618 p_style_fmt_variation_rec.variation_rank <> fnd_api.g_miss_num AND
1619 p_style_fmt_variation_rec.variation_rank <> l_variation_rank)
1620 THEN
1621 check_greater_than_zero (
1622 p_column => 'variation_rank',
1623 p_column_value => p_style_fmt_variation_rec.variation_rank,
1624 x_return_status => x_return_status );
1625 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1626 hz_utility_v2pub.debug(p_message=>'variation_rank should be > 0.' ||'x_return_status = ' ||
1627 x_return_status,
1628 p_prefix =>l_debug_prefix,
1629 p_msg_level=>fnd_log.level_statement);
1630 END IF;
1631 END IF;
1632
1633 -----------------------------
1634 -- validate start_date_active
1635 -----------------------------
1636
1637 -- start_date_active is mandatory
1638 IF (p_create_update_flag = 'C') THEN
1639 HZ_UTILITY_V2PUB.validate_mandatory (
1640 p_create_update_flag => p_create_update_flag,
1641 p_column => 'start_date_active',
1642 p_column_value => p_style_fmt_variation_rec.start_date_active,
1643 x_return_status => x_return_status);
1644
1645 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1646 hz_utility_v2pub.debug(p_message=>'start_date_active is mandatory. ' ||
1647 'x_return_status = ' || x_return_status,
1648 p_prefix =>l_debug_prefix,
1649 p_msg_level=>fnd_log.level_statement);
1650 END IF;
1651 END IF;
1652
1653 -- start_date_active cannot be set to null during update
1654 IF p_create_update_flag = 'U' THEN
1655 HZ_UTILITY_V2PUB.validate_cannot_update_to_null (
1656 p_column => 'start_date_active',
1657 p_column_value => p_style_fmt_variation_rec.start_date_active,
1658 x_return_status => x_return_status);
1659
1660 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1661 hz_utility_v2pub.debug(p_message=>'start_date_active cannot be set to null during update. ' ||
1662 'x_return_status = ' || x_return_status,
1663 p_prefix =>l_debug_prefix,
1664 p_msg_level=>fnd_log.level_statement);
1665 END IF;
1666
1667 END IF;
1668
1669 -------------------------------------------------
1670 -- start_date_active, end_date_active validation
1671 -------------------------------------------------
1672
1673 -- end_date_active must be null or greater than start date
1674 IF (p_create_update_flag = 'C') THEN
1675 IF p_style_fmt_variation_rec.end_date_active IS NOT NULL AND
1676 p_style_fmt_variation_rec.end_date_active <> fnd_api.g_miss_date AND
1677 p_style_fmt_variation_rec.end_date_active < p_style_fmt_variation_rec.start_date_active
1678 THEN
1679 fnd_message.set_name('AR', 'HZ_API_START_DATE_GREATER');
1680 fnd_msg_pub.add;
1681 x_return_status := fnd_api.g_ret_sts_error;
1682 END IF;
1683 ELSIF (p_create_update_flag = 'U') THEN
1684 -- old start_date_active, end_date_active has been selected from table
1685 -- and put into l_start_date_active, l_end_date_active
1686
1687 IF p_style_fmt_variation_rec.start_date_active <> fnd_api.g_miss_date
1688 AND p_style_fmt_variation_rec.start_date_active is not null
1689 THEN
1690 l_start_date_active := p_style_fmt_variation_rec.start_date_active;
1691 END IF;
1692
1693 IF p_style_fmt_variation_rec.end_date_active = fnd_api.g_miss_date
1694 THEN
1695 l_end_date_active := null;
1696 ELSIF p_style_fmt_variation_rec.end_date_active IS NOT NULL THEN
1697 l_end_date_active := p_style_fmt_variation_rec.end_date_active;
1698 END IF;
1699
1700 IF l_end_date_active IS NOT NULL
1701 AND l_end_date_active < l_start_date_active
1702 THEN
1703 fnd_message.set_name('AR', 'HZ_API_START_DATE_GREATER');
1704 fnd_msg_pub.add;
1705 x_return_status := fnd_api.g_ret_sts_error;
1706 END IF;
1707 END IF;
1708
1709 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1710 hz_utility_v2pub.debug(p_message=>'end_date_active must be null or greater than start date. ' ||
1711 'x_return_status = ' || x_return_status,
1712 p_prefix =>l_debug_prefix,
1713 p_msg_level=>fnd_log.level_statement);
1714 END IF;
1715
1716 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1717 hz_utility_v2pub.debug(p_message=>'validate_style_fmt_variation (+)',
1718 p_prefix=>l_debug_prefix,
1719 p_msg_level=>fnd_log.level_procedure);
1720 END IF;
1721
1722 --disable_debug;
1723
1724 END validate_style_fmt_variation;
1725
1726 --
1727 -- PROCEDURE validate_style_fmt_layout
1728 --
1729 -- DESCRIPTION
1730 -- Validates style record. Checks for
1731 -- uniqueness
1732 -- mandatory columns
1733 -- non-updateable fields
1734 -- foreign key validations
1735 -- other validations
1736 --
1737 -- EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
1738 --
1739 -- ARGUMENTS
1740 -- IN:
1741 -- p_create_update_flag Create update flag. 'C' = create. 'U' = update.
1742 -- p_style_fmt_layout_rec Style Locale record.
1743 -- p_rowid Rowid of the record (used only in update mode).
1744 -- IN/OUT:
1745 -- x_return_status Return status after the call. The status can
1746 -- be FND_API.G_RET_STS_SUCCESS (success),
1747 -- FND_API.G_RET_STS_ERROR (error),
1748 -- FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
1749 --
1750 -- NOTES
1751 --
1752 -- MODIFICATION HISTORY
1753 --
1754 -- 18-JUL-2002 Kate Shan o Created.
1755 --
1756 --
1757
1758 PROCEDURE validate_style_fmt_layout(
1759 p_create_update_flag IN VARCHAR2,
1760 p_style_fmt_layout_rec IN HZ_STYLE_FMT_LAYOUT_V2PUB.STYLE_FMT_LAYOUT_REC_TYPE,
1761 p_rowid IN ROWID,
1762 x_return_status IN OUT NOCOPY VARCHAR2
1763 ) IS
1764
1765 l_dummy VARCHAR2(1);
1766 l_style_fmt_layout_id HZ_STYLE_FMT_LAYOUTS_B.style_fmt_layout_id%TYPE;
1767 l_style_format_code HZ_STYLE_FMT_LAYOUTS_B.style_format_code%TYPE;
1768 l_variation_number HZ_STYLE_FMT_LAYOUTS_B.variation_number%TYPE;
1769 l_attribute_code HZ_STYLE_FMT_LAYOUTS_B.attribute_code%TYPE;
1770 l_attribute_application_id HZ_STYLE_FMT_LAYOUTS_B.attribute_application_id%TYPE;
1771 l_line_number HZ_STYLE_FMT_LAYOUTS_B.line_number%TYPE;
1772 l_position HZ_STYLE_FMT_LAYOUTS_B.position%TYPE;
1773 l_mandatory_flag HZ_STYLE_FMT_LAYOUTS_B.mandatory_flag%TYPE;
1774 l_use_initial_flag HZ_STYLE_FMT_LAYOUTS_B.use_initial_flag%TYPE;
1775 l_uppercase_flag HZ_STYLE_FMT_LAYOUTS_B.uppercase_flag%TYPE;
1776 l_blank_lines_before HZ_STYLE_FMT_LAYOUTS_B.blank_lines_before%TYPE;
1777 l_blank_lines_after HZ_STYLE_FMT_LAYOUTS_B.blank_lines_after%TYPE;
1778 l_start_date_active date;
1779 l_end_date_active date;
1780 l_debug_prefix VARCHAR2(30) := '';
1781 l_dup_style_fmt_layout_id HZ_STYLE_FMT_LAYOUTS_B.style_fmt_layout_id%TYPE;
1782 l_updated_stl_fmt_layout_rec HZ_STYLE_FMT_LAYOUT_V2PUB.STYLE_FMT_LAYOUT_REC_TYPE;
1783 l_max_line_number NUMBER;
1784 l_min_line_number NUMBER;
1785 l_database_object_name VARCHAR2(30);
1786
1787 CURSOR c_dup (p_style_fmt_layout_id IN NUMBER) IS
1788 SELECT 'Y'
1789 FROM hz_style_fmt_layouts_b hsfl
1790 WHERE hsfl.style_fmt_layout_id = p_style_fmt_layout_id;
1791
1792 BEGIN
1793 --enable_debug;
1794
1795 -- Debug info.
1796 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1797 hz_utility_v2pub.debug(p_message=>'validate_style_fmt_layout (+)',
1798 p_prefix=>l_debug_prefix,
1799 p_msg_level=>fnd_log.level_procedure);
1800 END IF;
1801
1802 -- select columns needed to be checked from table during update
1803
1804 IF (p_create_update_flag = 'U') THEN
1805 SELECT
1806 style_fmt_layout_id,
1807 style_format_code,
1808 variation_number,
1809 attribute_code,
1810 attribute_application_id,
1811 line_number,
1812 position,
1813 mandatory_flag,
1814 use_initial_flag,
1815 uppercase_flag,
1816 blank_lines_before,
1817 blank_lines_after,
1818 start_date_active,
1819 end_date_active
1820 INTO
1821 l_style_fmt_layout_id,
1822 l_style_format_code,
1823 l_variation_number,
1824 l_attribute_code,
1825 l_attribute_application_id,
1826 l_line_number,
1827 l_position,
1828 l_mandatory_flag,
1829 l_use_initial_flag,
1830 l_uppercase_flag,
1831 l_blank_lines_before,
1832 l_blank_lines_after,
1833 l_start_date_active,
1834 l_end_date_active
1835 FROM HZ_STYLE_FMT_LAYOUTS_B
1836 WHERE ROWID = p_rowid;
1837
1838 get_updated_record (
1839 p_style_fmt_layout_id => p_style_fmt_layout_rec.style_fmt_layout_id,
1840 p_update_field_rec => p_style_fmt_layout_rec,
1841 x_updated_rec => l_updated_stl_fmt_layout_rec );
1842
1843 END IF;
1844
1845 -------------------------------
1846 -- validate style_fmt_layout_id
1847 -------------------------------
1848
1849 -- If primary key value is passed, check for uniqueness.
1850 -- If primary key value is not passed, it will be generated
1851 -- from sequence by table handler.
1852
1853 IF p_create_update_flag = 'C' THEN
1854 IF p_style_fmt_layout_rec.style_fmt_layout_id IS NOT NULL AND
1855 p_style_fmt_layout_rec.style_fmt_layout_id <> fnd_api.g_miss_num
1856 THEN
1857 OPEN c_dup (p_style_fmt_layout_rec.style_fmt_layout_id);
1858 FETCH c_dup INTO l_dummy;
1859 -- key is not unique, push an error onto the stack.
1860 IF NVL(c_dup%FOUND, FALSE) THEN
1861 fnd_message.set_name('AR', 'HZ_API_DUPLICATE_COLUMN');
1862 fnd_message.set_token('COLUMN', 'style_fmt_layout_id');
1863 fnd_msg_pub.add;
1864 x_return_status := fnd_api.g_ret_sts_error;
1865 END IF;
1866 CLOSE c_dup;
1867 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1868 hz_utility_v2pub.debug(p_message=>'check that style_fmt_layout_id is unique during creation. ' ||
1869 ' x_return_status = ' || x_return_status,
1870 p_prefix =>l_debug_prefix,
1871 p_msg_level=>fnd_log.level_statement);
1872 END IF;
1873 END IF;
1874 END IF;
1875
1876 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1877 hz_utility_v2pub.debug(p_message=>'(+) after validate style_fmt_layout_id ... ' ||
1878 'x_return_status = ' || x_return_status,
1879 p_prefix =>l_debug_prefix,
1880 p_msg_level=>fnd_log.level_statement);
1881 END IF;
1882
1883 -----------------------------
1884 -- validate style_format_code
1885 -----------------------------
1886
1887 -- style_format_code is mandatory
1888 IF (p_create_update_flag = 'C') THEN
1889 HZ_UTILITY_V2PUB.validate_mandatory (
1890 p_create_update_flag => p_create_update_flag,
1891 p_column => 'style_format_code',
1892 p_column_value => p_style_fmt_layout_rec.style_format_code,
1893 x_return_status => x_return_status);
1894
1895 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1896 hz_utility_v2pub.debug(p_message=>'style_format_code is mandatory. ' ||
1897 'x_return_status = ' || x_return_status,
1898 p_prefix =>l_debug_prefix,
1899 p_msg_level=>fnd_log.level_statement);
1900 END IF;
1901 END IF;
1902
1903 -- style_format_code is non-updateable field
1904
1905 IF p_create_update_flag = 'U' AND
1906 p_style_fmt_layout_rec.style_format_code IS NOT NULL
1907 THEN
1908 HZ_UTILITY_V2PUB.validate_nonupdateable (
1909 p_column => 'style_format_code',
1910 p_column_value => p_style_fmt_layout_rec.style_format_code,
1911 p_old_column_value => l_style_format_code,
1912 x_return_status => x_return_status);
1913
1914 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1915 hz_utility_v2pub.debug(p_message=>'l_style_format_code is non-updateable. ' ||
1916 'x_return_status = ' || x_return_status,
1917 p_prefix =>l_debug_prefix,
1918 p_msg_level=>fnd_log.level_statement);
1919 END IF;
1920 END IF;
1921
1922 -----------------------------
1923 -- validate variation_number
1924 -----------------------------
1925
1926 -- variation_number is mandatory
1927 IF (p_create_update_flag = 'C') THEN
1928 HZ_UTILITY_V2PUB.validate_mandatory (
1929 p_create_update_flag => p_create_update_flag,
1930 p_column => 'variation_number',
1931 p_column_value => p_style_fmt_layout_rec.variation_number,
1932 x_return_status => x_return_status);
1933
1934 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1935 hz_utility_v2pub.debug(p_message=>'variation_number is mandatory. ' ||
1936 'x_return_status = ' || x_return_status,
1937 p_prefix =>l_debug_prefix,
1938 p_msg_level=>fnd_log.level_statement);
1939 END IF;
1940 END IF;
1941
1942 -- variation_number is non-updateable field
1943
1944 IF p_create_update_flag = 'U' AND
1945 p_style_fmt_layout_rec.variation_number IS NOT NULL
1946 THEN
1947 HZ_UTILITY_V2PUB.validate_nonupdateable (
1948 p_column => 'variation_number',
1949 p_column_value => p_style_fmt_layout_rec.variation_number,
1950 p_old_column_value => l_variation_number,
1951 x_return_status => x_return_status);
1952
1953 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1954 hz_utility_v2pub.debug(p_message=>'l_variation_number is non-updateable. ' ||
1955 'x_return_status = ' || x_return_status,
1956 p_prefix =>l_debug_prefix,
1957 p_msg_level=>fnd_log.level_statement);
1958 END IF;
1959 END IF;
1960
1961 -- variation_number should be > 0
1962 IF p_create_update_flag = 'C' AND
1963 p_style_fmt_layout_rec.variation_number is not null
1964 THEN
1965 check_greater_than_zero (
1966 p_column => 'variation_number',
1967 p_column_value => p_style_fmt_layout_rec.variation_number,
1968 x_return_status => x_return_status );
1969
1970 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1971 hz_utility_v2pub.debug(p_message=>'variation_number should be > 0.' ||'x_return_status = ' ||
1972 x_return_status,
1973 p_prefix =>l_debug_prefix,
1974 p_msg_level=>fnd_log.level_statement);
1975 END IF;
1976 END IF;
1977
1978 ---------------------------------------------------------------------
1979 -- style_format_code, variation_number validation
1980 ---------------------------------------------------------------------
1981 -- combination of style_format_code, variation_number is foreign key of hz_style_formats_b
1982 -- Do not need to check during update because style_format_code is
1983 -- non-updateable.
1984 IF p_create_update_flag = 'C' AND
1985 p_style_fmt_layout_rec.style_format_code IS NOT NULL AND
1986 p_style_fmt_layout_rec.style_format_code <> fnd_api.g_miss_CHAR AND
1987 p_style_fmt_layout_rec.variation_number IS NOT NULL AND
1988 p_style_fmt_layout_rec.variation_number <> fnd_api.g_miss_num
1989
1990 THEN
1991 BEGIN
1992 SELECT 'Y'
1993 INTO l_dummy
1994 FROM HZ_STYLE_FMT_VARIATIONS
1995 WHERE style_format_code = p_style_fmt_layout_rec.style_format_code AND
1996 variation_number = p_style_fmt_layout_rec.variation_number;
1997
1998 -- Start date and end date should be within the time range of the corresponding style format variation
1999 BEGIN
2000 SELECT 'Y'
2001 INTO l_dummy
2002 FROM HZ_STYLE_FMT_VARIATIONS
2003 WHERE style_format_code = p_style_fmt_layout_rec.style_format_code AND
2004 variation_number = p_style_fmt_layout_rec.variation_number AND
2005 p_style_fmt_layout_rec.start_date_active
2006 BETWEEN start_date_active AND NVL(end_date_active, to_date('12/31/4712','MM/DD/YYYY')) AND
2007 (decode(p_style_fmt_layout_rec.end_date_active, null, to_date('12/31/4712','MM/DD/YYYY'),
2008 fnd_api.g_miss_date, to_date('12/31/4712','MM/DD/YYYY'), p_style_fmt_layout_rec.end_date_active)
2009 BETWEEN start_date_active
2010 AND NVL(end_date_active, to_date('12/31/4712','MM/DD/YYYY')));
2011
2012 EXCEPTION
2013 WHEN NO_DATA_FOUND THEN
2014 fnd_message.set_name('AR', 'HZ_LYT_INVALID_START_END_DATE');
2015 fnd_msg_pub.add;
2016 x_return_status := fnd_api.g_ret_sts_error;
2017 END;
2018 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2019 hz_utility_v2pub.debug(p_message=>'Start date and end date should be within the time range of the corresponding style format variation.' ||
2020 'x_return_status = ' || x_return_status,
2021 p_prefix =>l_debug_prefix,
2022 p_msg_level=>fnd_log.level_statement);
2023 END IF;
2024
2025 EXCEPTION
2026 WHEN NO_DATA_FOUND THEN
2027 fnd_message.set_name('AR', 'HZ_INVALID_FMT_CODE_VAR_NO');
2028 fnd_message.set_token('VARNUM', p_style_fmt_layout_rec.variation_number);
2029 fnd_msg_pub.add;
2030 x_return_status := fnd_api.g_ret_sts_error;
2031 END;
2032 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2033 hz_utility_v2pub.debug(p_message=>'style_format_code and variation_number are foreign key of table hz_style_fmt_variations .' ||
2034 'x_return_status = ' || x_return_status,
2035 p_prefix =>l_debug_prefix,
2036 p_msg_level=>fnd_log.level_statement);
2037 END IF;
2038
2039 ELSIF p_create_update_flag = 'U' AND
2040 x_return_status = fnd_api.g_ret_sts_success
2041 THEN
2042 BEGIN
2043 SELECT 'Y'
2044 INTO l_dummy
2045 FROM HZ_STYLE_FMT_VARIATIONS
2046 WHERE style_format_code = l_updated_stl_fmt_layout_rec.style_format_code AND
2047 variation_number = l_updated_stl_fmt_layout_rec.variation_number AND
2048 l_updated_stl_fmt_layout_rec.start_date_active BETWEEN start_date_active
2049 AND NVL(end_date_active, to_date('12/31/4712','MM/DD/YYYY')) AND
2050 (decode(l_updated_stl_fmt_layout_rec.end_date_active, null, to_date('12/31/4712','MM/DD/YYYY'),
2051 fnd_api.g_miss_date, to_date('12/31/4712','MM/DD/YYYY'), l_updated_stl_fmt_layout_rec.end_date_active)
2052 BETWEEN start_date_active
2053 AND NVL(end_date_active, to_date('12/31/4712','MM/DD/YYYY')));
2054
2055 EXCEPTION
2056 WHEN NO_DATA_FOUND THEN
2057 fnd_message.set_name('AR', 'HZ_LYT_INVALID_START_END_DATE');
2058 fnd_msg_pub.add;
2059 x_return_status := fnd_api.g_ret_sts_error;
2060 END;
2061 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2062 hz_utility_v2pub.debug(p_message=>'Start date and end date should be within the time range of the corresponding style format variation.' ||
2063 'x_return_status = ' || x_return_status,
2064 p_prefix =>l_debug_prefix,
2065 p_msg_level=>fnd_log.level_statement);
2066 END IF;
2067
2068 END IF;
2069
2070
2071 -----------------------------
2072 -- validate attribute_code
2073 -----------------------------
2074
2075 -- attribute_code is mandatory
2076 IF (p_create_update_flag = 'C') THEN
2077 HZ_UTILITY_V2PUB.validate_mandatory (
2078 p_create_update_flag => p_create_update_flag,
2079 p_column => 'attribute_code',
2080 p_column_value => p_style_fmt_layout_rec.attribute_code,
2081 x_return_status => x_return_status);
2082 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2083 hz_utility_v2pub.debug(p_message=>'attribute_code is mandatory. ' ||
2084 'x_return_status = ' || x_return_status,
2085 p_prefix =>l_debug_prefix,
2086 p_msg_level=>fnd_log.level_statement);
2087 END IF;
2088 END IF;
2089
2090 -- attribute_code is non-updateable field
2091
2092 IF p_create_update_flag = 'U' AND
2093 p_style_fmt_layout_rec.attribute_code IS NOT NULL
2094 THEN
2095 HZ_UTILITY_V2PUB.validate_nonupdateable (
2096 p_column => 'attribute_code',
2097 p_column_value => p_style_fmt_layout_rec.attribute_code,
2098 p_old_column_value => l_attribute_code,
2099 x_return_status => x_return_status);
2100
2101 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2102 hz_utility_v2pub.debug(p_message=>'attribute_code is non-updateable. ' ||
2103 'x_return_status = ' || x_return_status,
2104 p_prefix =>l_debug_prefix,
2105 p_msg_level=>fnd_log.level_statement);
2106 END IF;
2107 END IF;
2108
2109 ------------------------------------
2110 -- validate attribute_application_id
2111 ------------------------------------
2112
2113 -- attribute_application_id is mandatory
2114 IF (p_create_update_flag = 'C') THEN
2115 HZ_UTILITY_V2PUB.validate_mandatory (
2116 p_create_update_flag => p_create_update_flag,
2117 p_column => 'attribute_application_id',
2118 p_column_value => p_style_fmt_layout_rec.attribute_application_id,
2119 x_return_status => x_return_status);
2120
2121 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2122 hz_utility_v2pub.debug(p_message=>'attribute_application_id is mandatory. ' ||
2123 'x_return_status = ' || x_return_status,
2124 p_prefix =>l_debug_prefix,
2125 p_msg_level=>fnd_log.level_statement);
2126 END IF;
2127 END IF;
2128
2129 -- attribute_application_id is non-updateable field
2130
2131 IF p_create_update_flag = 'U' AND
2132 p_style_fmt_layout_rec.attribute_application_id IS NOT NULL
2133 THEN
2134 HZ_UTILITY_V2PUB.validate_nonupdateable (
2135 p_column => 'attribute_application_id',
2136 p_column_value => p_style_fmt_layout_rec.attribute_application_id,
2137 p_old_column_value => l_attribute_application_id,
2138 x_return_status => x_return_status);
2139 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2140 hz_utility_v2pub.debug(p_message=>'attribute_application_id is non-updateable. ' ||
2141 'x_return_status = ' || x_return_status,
2142 p_prefix =>l_debug_prefix,
2143 p_msg_level=>fnd_log.level_statement);
2144 END IF;
2145 END IF;
2146
2147 -----------------------------------------------------
2148 -- validate attribute_code, attribute_application_id
2149 -----------------------------------------------------
2150 -- combination of attribute_code, variation_number is foreign key of ak_attributes
2151 -- Do not need to check during update because attribute_code is
2152 -- non-updateable.
2153 IF x_return_status = fnd_api.g_ret_sts_success AND
2154 p_create_update_flag = 'C' AND
2155 p_style_fmt_layout_rec.attribute_code IS NOT NULL AND
2156 p_style_fmt_layout_rec.attribute_code <> fnd_api.g_miss_CHAR
2157 -- p_style_fmt_layout_rec.attribute_application_id IS NOT NULL AND
2158 -- p_style_fmt_layout_rec.attribute_application_id <> fnd_api.g_miss_num
2159 THEN
2160 BEGIN
2161 SELECT database_object_name
2162 INTO l_database_object_name
2163 FROM hz_style_formats_b sf, hz_styles_b s
2164 WHERE sf.style_format_code = p_style_fmt_layout_rec.style_format_code AND
2165 s.style_code = sf.style_code;
2166
2167 SELECT 'Y'
2168 into l_dummy
2169 FROM fnd_columns c, fnd_tables t
2170 where t.table_name = l_database_object_name AND
2171 t.table_id = c.table_id AND
2172 c.application_id = t.application_id AND --Bug No.4942505. SQLID:14450634
2173 c.column_name = p_style_fmt_layout_rec.attribute_code;
2174
2175 EXCEPTION
2176 WHEN NO_DATA_FOUND THEN
2177 fnd_message.set_name('AR', 'HZ_INVALID_ATTR_APPID');
2178 fnd_message.SET_TOKEN('ATTRIBUTE_CODE',p_style_fmt_layout_rec.attribute_code);
2179 fnd_message.SET_TOKEN('TABLE',l_database_object_name);
2180 fnd_msg_pub.add;
2181 x_return_status := fnd_api.g_ret_sts_error;
2182 END;
2183 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2184 hz_utility_v2pub.debug(p_message=>'the ATTRIBUTE_CODE' || p_style_fmt_layout_rec.attribute_code ||
2185 'does not exist in table specified in ' || l_database_object_name ||
2186 ' x_return_status = ' || x_return_status,
2187 p_prefix =>l_debug_prefix,
2188 p_msg_level=>fnd_log.level_statement);
2189 END IF;
2190 END IF;
2191
2192 -------------------------------------------------------------------------------------------
2193 -- style_format_code, variation_number, attribute_code, attribute_application_id validation
2194 -------------------------------------------------------------------------------------------
2195
2196 -- The combanition of variation_number, style_format_code, attribute_code,
2197 -- attribute_application_id is unique in the time range of start_date_active and
2198 -- end_date_active
2199
2200 IF p_create_update_flag = 'C' AND
2201 p_style_fmt_layout_rec.style_format_code IS NOT NULL AND
2202 p_style_fmt_layout_rec.variation_number IS NOT NULL AND
2203 p_style_fmt_layout_rec.attribute_code IS NOT NULL AND
2204 p_style_fmt_layout_rec.attribute_application_id IS NOT NULL
2205 THEN
2206 BEGIN
2207 select style_fmt_layout_id into l_dup_style_fmt_layout_id
2208 from HZ_STYLE_FMT_LAYOUTS_B
2209 where style_format_code = p_style_fmt_layout_rec.style_format_code AND
2210 variation_number = p_style_fmt_layout_rec.variation_number AND
2211 attribute_code = p_style_fmt_layout_rec.attribute_code AND
2212 attribute_application_id = p_style_fmt_layout_rec.attribute_application_id AND
2213 NOT ( ( p_style_fmt_layout_rec.end_date_active is not null and
2214 p_style_fmt_layout_rec.end_date_active <> fnd_api.g_miss_date and
2215 p_style_fmt_layout_rec.end_date_active < start_date_active ) OR
2216 ( end_date_active is not null and
2217 p_style_fmt_layout_rec.start_date_active > end_date_active )) AND
2218 rownum =1;
2219
2220 FND_MESSAGE.SET_NAME('AR', 'HZ_LAYOUT_ATTR_APPID_DUP');
2221 FND_MSG_PUB.ADD;
2222 x_return_status := FND_API.G_RET_STS_ERROR;
2223
2224 EXCEPTION
2225 WHEN NO_DATA_FOUND THEN
2226 NULL;
2227 END;
2228
2229 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2230 hz_utility_v2pub.debug(p_message=>'Another record exist for this style format layout with an overlapping date ranger. Please input a unique combination of style_format_code, variation_number, attribute_code, attribute_application_id.' ||
2231 'x_return_status = ' || x_return_status,
2232 p_prefix =>l_debug_prefix,
2233 p_msg_level=>fnd_log.level_statement);
2234 END IF;
2235
2236 ELSIF p_create_update_flag = 'U' AND
2237 (p_style_fmt_layout_rec.start_date_active is not null OR
2238 p_style_fmt_layout_rec.end_date_active is not null )
2239 THEN
2240
2241 BEGIN
2242 select style_fmt_layout_id into l_dup_style_fmt_layout_id
2243 from HZ_STYLE_FMT_LAYOUTS_B
2244 where style_fmt_layout_id <> l_updated_stl_fmt_layout_rec.style_fmt_layout_id AND
2245 style_format_code = l_updated_stl_fmt_layout_rec.style_format_code AND
2246 variation_number = l_updated_stl_fmt_layout_rec.variation_number AND
2247 attribute_code = l_updated_stl_fmt_layout_rec.attribute_code AND
2248 attribute_application_id = l_updated_stl_fmt_layout_rec.attribute_application_id AND
2249 NOT ( ( l_updated_stl_fmt_layout_rec.end_date_active is not null and
2250 l_updated_stl_fmt_layout_rec.end_date_active < start_date_active ) OR
2251 ( end_date_active is not null and
2252 l_updated_stl_fmt_layout_rec.start_date_active > end_date_active )) AND
2253 rownum =1;
2254
2255 FND_MESSAGE.SET_NAME('AR', 'HZ_LAYOUT_ATTR_APPID_DUP');
2256 FND_MSG_PUB.ADD;
2257 x_return_status := FND_API.G_RET_STS_ERROR;
2258
2259 EXCEPTION
2260 WHEN NO_DATA_FOUND THEN
2261 NULL;
2262 END;
2263 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2264 hz_utility_v2pub.debug(p_message=>'Another record exist for this style format layout with an overlapping date ranger. Please input a unique combination of style_format_code, variation_number, attribute_code, attribute_application_id.' ||
2265 'x_return_status = ' || x_return_status,
2266 p_prefix =>l_debug_prefix,
2267 p_msg_level=>fnd_log.level_statement);
2268 END IF;
2269
2270 END IF;
2271
2272 ------------------------------------
2273 -- validate line_number
2274 ------------------------------------
2275
2276 -- line_number is mandatory
2277 IF (p_create_update_flag = 'C') THEN
2278 HZ_UTILITY_V2PUB.validate_mandatory (
2279 p_create_update_flag => p_create_update_flag,
2280 p_column => 'line_number ',
2281 p_column_value => p_style_fmt_layout_rec.line_number ,
2282 x_return_status => x_return_status);
2283
2284 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2285 hz_utility_v2pub.debug(p_message=>'line_number is mandatory. ' ||
2286 'x_return_status = ' || x_return_status,
2287 p_prefix =>l_debug_prefix,
2288 p_msg_level=>fnd_log.level_statement);
2289 END IF;
2290 END IF;
2291
2292 -- line_number should be > 0
2293 IF p_style_fmt_layout_rec.line_number is not null AND
2294 p_style_fmt_layout_rec.line_number <> fnd_api.g_miss_num
2295 THEN
2296 check_greater_than_zero (
2297 p_column => 'line_number',
2298 p_column_value => p_style_fmt_layout_rec.line_number,
2299 x_return_status => x_return_status );
2300 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2301 hz_utility_v2pub.debug(p_message=>'line_number should be > 0.' ||'x_return_status = ' ||
2302 x_return_status,
2303 p_prefix =>l_debug_prefix,
2304 p_msg_level=>fnd_log.level_statement);
2305 END IF;
2306 END IF;
2307
2308 -- line_number cannot be set to null during update
2309 IF p_create_update_flag = 'U' THEN
2310 HZ_UTILITY_V2PUB.validate_cannot_update_to_null (
2311 p_column => 'line_number',
2312 p_column_value => p_style_fmt_layout_rec.line_number,
2313 x_return_status => x_return_status);
2314 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2315 hz_utility_v2pub.debug(p_message=>'line_number cannot be set to null during update. ' ||
2316 'x_return_status = ' || x_return_status,
2317 p_prefix =>l_debug_prefix,
2318 p_msg_level=>fnd_log.level_statement);
2319 END IF;
2320
2321 END IF;
2322
2323 ------------------------------------
2324 -- validate position
2325 ------------------------------------
2326
2327 -- position is mandatory
2328 IF (p_create_update_flag = 'C') THEN
2329 HZ_UTILITY_V2PUB.validate_mandatory (
2330 p_create_update_flag => p_create_update_flag,
2331 p_column => 'position ',
2332 p_column_value => p_style_fmt_layout_rec.position ,
2333 x_return_status => x_return_status);
2334 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2335 hz_utility_v2pub.debug(p_message=>'position is mandatory. ' ||
2336 'x_return_status = ' || x_return_status,
2337 p_prefix =>l_debug_prefix,
2338 p_msg_level=>fnd_log.level_statement);
2339 END IF;
2340 END IF;
2341
2342 -- position should be > 0
2343 IF p_style_fmt_layout_rec.position is not null AND
2344 p_style_fmt_layout_rec.position <> fnd_api.g_miss_num
2345 THEN
2346 check_greater_than_zero (
2347 p_column => 'position',
2348 p_column_value => p_style_fmt_layout_rec.position,
2349 x_return_status => x_return_status );
2350 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2351 hz_utility_v2pub.debug(p_message=>'position should be > 0.' ||'x_return_status = ' ||
2352 x_return_status,
2353 p_prefix =>l_debug_prefix,
2354 p_msg_level=>fnd_log.level_statement);
2355 END IF;
2356 END IF;
2357
2358 -- position cannot be set to null during update
2359 IF p_create_update_flag = 'U' THEN
2360 HZ_UTILITY_V2PUB.validate_cannot_update_to_null (
2361 p_column => 'position',
2362 p_column_value => p_style_fmt_layout_rec.position,
2363 x_return_status => x_return_status);
2364 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2365 hz_utility_v2pub.debug(p_message=>'position cannot be set to null during update. ' ||
2366 'x_return_status = ' || x_return_status,
2367 p_prefix =>l_debug_prefix,
2368 p_msg_level=>fnd_log.level_statement);
2369 END IF;
2370
2371 END IF;
2372
2373 -------------------------------------------------------------------------------------------
2374 -- style_format_code, variation_number, line_number, position validation
2375 -------------------------------------------------------------------------------------------
2376
2377 -- The combanition of variation_number, style_format_code, line_number,
2378 -- position is unique in the time range of start_date_active and
2379 -- end_date_active
2380
2381 IF p_create_update_flag = 'C' AND
2382 p_style_fmt_layout_rec.style_format_code IS NOT NULL AND
2383 p_style_fmt_layout_rec.variation_number IS NOT NULL AND
2384 p_style_fmt_layout_rec.line_number IS NOT NULL AND
2385 p_style_fmt_layout_rec.position IS NOT NULL
2386 THEN
2387 BEGIN
2388 select style_fmt_layout_id into l_dup_style_fmt_layout_id
2389 from HZ_STYLE_FMT_LAYOUTS_B
2390 where style_format_code = p_style_fmt_layout_rec.style_format_code AND
2391 variation_number = p_style_fmt_layout_rec.variation_number AND
2392 line_number = p_style_fmt_layout_rec.line_number AND
2393 position = p_style_fmt_layout_rec.position AND
2394 NOT ( ( p_style_fmt_layout_rec.end_date_active is not null and
2395 p_style_fmt_layout_rec.end_date_active <> fnd_api.g_miss_date and
2396 p_style_fmt_layout_rec.end_date_active < start_date_active ) OR
2397 ( end_date_active is not null and
2398 p_style_fmt_layout_rec.start_date_active > end_date_active )) AND
2399 rownum =1;
2400
2401 FND_MESSAGE.SET_NAME('AR', 'HZ_LAYOUT_LINE_POSITION_DUP');
2402 FND_MSG_PUB.ADD;
2403 x_return_status := FND_API.G_RET_STS_ERROR;
2404
2405 EXCEPTION
2406 WHEN NO_DATA_FOUND THEN
2407 NULL;
2408 END;
2409
2410 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2411 hz_utility_v2pub.debug(p_message=>'Another record exist for this style format layout with an overlapping date ranger. Please input a unique combination of style_format_code, variation_number, line_number, position.' ||
2412 'x_return_status = ' || x_return_status,
2413 p_prefix =>l_debug_prefix,
2414 p_msg_level=>fnd_log.level_statement);
2415 END IF;
2416
2417 ELSIF p_create_update_flag = 'U' AND
2418 (p_style_fmt_layout_rec.start_date_active is not null OR
2419 p_style_fmt_layout_rec.end_date_active is not null )
2420 THEN
2421
2422 BEGIN
2423 select style_fmt_layout_id into l_dup_style_fmt_layout_id
2424 from HZ_STYLE_FMT_LAYOUTS_B
2425 where style_fmt_layout_id <> l_updated_stl_fmt_layout_rec.style_fmt_layout_id AND
2426 style_format_code = l_updated_stl_fmt_layout_rec.style_format_code AND
2427 variation_number = l_updated_stl_fmt_layout_rec.variation_number AND
2428 line_number = l_updated_stl_fmt_layout_rec.line_number AND
2429 position = l_updated_stl_fmt_layout_rec.position AND
2430 NOT ( ( l_updated_stl_fmt_layout_rec.end_date_active is not null and
2431 l_updated_stl_fmt_layout_rec.end_date_active < start_date_active ) OR
2432 ( end_date_active is not null and
2433 l_updated_stl_fmt_layout_rec.start_date_active > end_date_active )) AND
2434 rownum =1;
2435
2436 FND_MESSAGE.SET_NAME('AR', 'HZ_LAYOUT_LINE_POSITION_DUP');
2437 FND_MSG_PUB.ADD;
2438 x_return_status := FND_API.G_RET_STS_ERROR;
2439
2440 EXCEPTION
2441 WHEN NO_DATA_FOUND THEN
2442 NULL;
2443 END;
2444 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2445 hz_utility_v2pub.debug(p_message=>'Another record exist for this style format layout with an overlapping date ranger. Please input a unique combination of style_format_code, variation_number, line_number, position.' ||
2446 'x_return_status = ' || x_return_status,
2447 p_prefix =>l_debug_prefix,
2448 p_msg_level=>fnd_log.level_statement);
2449 END IF;
2450
2451 END IF;
2452
2453 ------------------------------------
2454 -- validate mandatory_flag
2455 ------------------------------------
2456
2457 -- mandatory_flag is mandatory
2458 IF (p_create_update_flag = 'C') THEN
2459 HZ_UTILITY_V2PUB.validate_mandatory (
2460 p_create_update_flag => p_create_update_flag,
2461 p_column => 'mandatory_flag',
2462 p_column_value => p_style_fmt_layout_rec.mandatory_flag ,
2463 x_return_status => x_return_status);
2464
2465 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2466 hz_utility_v2pub.debug(p_message=>'mandatory_flag is mandatory. ' ||
2467 'x_return_status = ' || x_return_status,
2468 p_prefix =>l_debug_prefix,
2469 p_msg_level=>fnd_log.level_statement);
2470 END IF;
2471 END IF;
2472
2473 -- mandatory_flag cannot be set to null during update
2474 IF p_create_update_flag = 'U' THEN
2475 HZ_UTILITY_V2PUB.validate_cannot_update_to_null (
2476 p_column => 'mandatory_flag',
2477 p_column_value => p_style_fmt_layout_rec.mandatory_flag,
2478 x_return_status => x_return_status);
2479
2480 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2481 hz_utility_v2pub.debug(p_message=>'mandatory_flag cannot be set to null during update. ' ||
2482 'x_return_status = ' || x_return_status,
2483 p_prefix =>l_debug_prefix,
2484 p_msg_level=>fnd_log.level_statement);
2485 END IF;
2486
2487 END IF;
2488
2489
2490 -- mandatory_flag is lookup code in lookup type YES/NO
2491 hz_utility_v2pub.validate_lookup (
2492 p_column => 'mandatory_flag',
2493 p_lookup_type => 'YES/NO',
2494 p_column_value => p_style_fmt_layout_rec.mandatory_flag,
2495 x_return_status => x_return_status);
2496
2497 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2498 hz_utility_v2pub.debug(p_message=>'mandatory_flag is lookup code in lookup type YES/NO. ' ||
2499 'x_return_status = ' || x_return_status,
2500 p_prefix =>l_debug_prefix,
2501 p_msg_level=>fnd_log.level_statement);
2502 END IF;
2503
2504 ------------------------------------
2505 -- validate use_initial_flag
2506 ------------------------------------
2507
2508 -- use_initial_flag is mandatory
2509 IF (p_create_update_flag = 'C') THEN
2510 HZ_UTILITY_V2PUB.validate_mandatory (
2511 p_create_update_flag => p_create_update_flag,
2512 p_column => 'use_initial_flag',
2513 p_column_value => p_style_fmt_layout_rec.use_initial_flag ,
2514 x_return_status => x_return_status);
2515 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2516 hz_utility_v2pub.debug(p_message=>'use_initial_flag is mandatory. ' ||
2517 'x_return_status = ' || x_return_status,
2518 p_prefix =>l_debug_prefix,
2519 p_msg_level=>fnd_log.level_statement);
2520 END IF;
2521 END IF;
2522
2523 -- use_initial_flag cannot be set to null during update
2524 IF p_create_update_flag = 'U' THEN
2525 HZ_UTILITY_V2PUB.validate_cannot_update_to_null (
2526 p_column => 'use_initial_flag',
2527 p_column_value => p_style_fmt_layout_rec.use_initial_flag,
2528 x_return_status => x_return_status);
2529
2530 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2531 hz_utility_v2pub.debug(p_message=>'use_initial_flag cannot be set to null during update. ' ||
2532 'x_return_status = ' || x_return_status,
2533 p_prefix =>l_debug_prefix,
2534 p_msg_level=>fnd_log.level_statement);
2535 END IF;
2536
2537 END IF;
2538
2539
2540 -- use_initial_flag is lookup code in lookup type YES/NO
2541 hz_utility_v2pub.validate_lookup (
2542 p_column => 'use_initial_flag',
2543 p_lookup_type => 'YES/NO',
2544 p_column_value => p_style_fmt_layout_rec.use_initial_flag,
2545 x_return_status => x_return_status);
2546
2547 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2548 hz_utility_v2pub.debug(p_message=>'use_initial_flag is lookup code in lookup type YES/NO. ' ||
2549 'x_return_status = ' || x_return_status,
2550 p_prefix =>l_debug_prefix,
2551 p_msg_level=>fnd_log.level_statement);
2552 END IF;
2553
2554 ------------------------------------
2555 -- validate uppercase_flag
2556 ------------------------------------
2557
2558 -- uppercase_flag is mandatory
2559 IF (p_create_update_flag = 'C') THEN
2560 HZ_UTILITY_V2PUB.validate_mandatory (
2561 p_create_update_flag => p_create_update_flag,
2562 p_column => 'uppercase_flag',
2563 p_column_value => p_style_fmt_layout_rec.uppercase_flag ,
2564 x_return_status => x_return_status);
2565
2566 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2567 hz_utility_v2pub.debug(p_message=>'uppercase_flag is mandatory. ' ||
2568 'x_return_status = ' || x_return_status,
2569 p_prefix =>l_debug_prefix,
2570 p_msg_level=>fnd_log.level_statement);
2571 END IF;
2572 END IF;
2573
2574 -- uppercase_flag cannot be set to null during update
2575 IF p_create_update_flag = 'U' THEN
2576 HZ_UTILITY_V2PUB.validate_cannot_update_to_null (
2577 p_column => 'uppercase_flag',
2578 p_column_value => p_style_fmt_layout_rec.uppercase_flag,
2579 x_return_status => x_return_status);
2580
2581 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2582 hz_utility_v2pub.debug(p_message=>'uppercase_flag cannot be set to null during update. ' ||
2583 'x_return_status = ' || x_return_status,
2584 p_prefix =>l_debug_prefix,
2585 p_msg_level=>fnd_log.level_statement);
2586 END IF;
2587
2588 END IF;
2589
2590
2591 -- uppercase_flag is lookup code in lookup type YES/NO
2592 hz_utility_v2pub.validate_lookup (
2593 p_column => 'uppercase_flag',
2594 p_lookup_type => 'YES/NO',
2595 p_column_value => p_style_fmt_layout_rec.uppercase_flag,
2596 x_return_status => x_return_status);
2597
2598 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2599 hz_utility_v2pub.debug(p_message=>'uppercase_flag is lookup code in lookup type YES/NO. ' ||
2600 'x_return_status = ' || x_return_status,
2601 p_prefix =>l_debug_prefix,
2602 p_msg_level=>fnd_log.level_statement);
2603 END IF;
2604
2605 ------------------------------------
2606 -- validate blank_lines_before
2607 ------------------------------------
2608
2609 IF p_style_fmt_layout_rec.blank_lines_after is not null AND
2610 p_style_fmt_layout_rec.blank_lines_after <> fnd_api.g_miss_num
2611 THEN
2612 check_greater_than_zero (
2613 p_column => 'blank_lines_after',
2614 p_column_value => p_style_fmt_layout_rec.blank_lines_after,
2615 x_return_status => x_return_status );
2616 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2617 hz_utility_v2pub.debug(p_message=>'blank_lines_after should be > 0.' ||'x_return_status = ' ||
2618 x_return_status,
2619 p_prefix =>l_debug_prefix,
2620 p_msg_level=>fnd_log.level_statement);
2621 END IF;
2622 END IF;
2623
2624 ------------------------------------
2625 -- validate blank_lines_after
2626 ------------------------------------
2627 IF p_style_fmt_layout_rec.blank_lines_before is not null AND
2628 p_style_fmt_layout_rec.blank_lines_before <> fnd_api.g_miss_num
2629 THEN
2630 check_greater_than_zero (
2631 p_column => 'blank_lines_before',
2632 p_column_value => p_style_fmt_layout_rec.blank_lines_before,
2633 x_return_status => x_return_status );
2634
2635 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2636 hz_utility_v2pub.debug(p_message=>'blank_lines_before should be > 0.' ||'x_return_status = ' ||
2637 x_return_status,
2638 p_prefix =>l_debug_prefix,
2639 p_msg_level=>fnd_log.level_statement);
2640 END IF;
2641 END IF;
2642
2643 -----------------------------
2644 -- validate start_date_active
2645 -----------------------------
2646
2647 -- start_date_active is mandatory
2648 IF (p_create_update_flag = 'C') THEN
2649 HZ_UTILITY_V2PUB.validate_mandatory (
2650 p_create_update_flag => p_create_update_flag,
2651 p_column => 'start_date_active',
2652 p_column_value => p_style_fmt_layout_rec.start_date_active,
2653 x_return_status => x_return_status);
2654
2655 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2656 hz_utility_v2pub.debug(p_message=>'start_date_active is mandatory. ' ||
2657 'x_return_status = ' || x_return_status,
2658 p_prefix =>l_debug_prefix,
2659 p_msg_level=>fnd_log.level_statement);
2660 END IF;
2661 END IF;
2662
2663 -- start_date_active cannot be set to null during update
2664 IF p_create_update_flag = 'U' THEN
2665 HZ_UTILITY_V2PUB.validate_cannot_update_to_null (
2666 p_column => 'start_date_active',
2667 p_column_value => p_style_fmt_layout_rec.start_date_active,
2668 x_return_status => x_return_status);
2669
2670 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2671 hz_utility_v2pub.debug(p_message=>'start_date_active cannot be set to null during update. ' ||
2672 'x_return_status = ' || x_return_status,
2673 p_prefix =>l_debug_prefix,
2674 p_msg_level=>fnd_log.level_statement);
2675 END IF;
2676
2677 END IF;
2678
2679 -------------------------------------------------
2680 -- start_date_active, end_date_active validation
2681 -------------------------------------------------
2682
2683 -- end_date_active must be null or greater than start date
2684 IF (p_create_update_flag = 'C') THEN
2685 IF p_style_fmt_layout_rec.end_date_active IS NOT NULL AND
2686 p_style_fmt_layout_rec.end_date_active <> fnd_api.g_miss_date AND
2687 p_style_fmt_layout_rec.end_date_active < p_style_fmt_layout_rec.start_date_active
2688 THEN
2689 fnd_message.set_name('AR', 'HZ_API_START_DATE_GREATER');
2690 fnd_msg_pub.add;
2691 x_return_status := fnd_api.g_ret_sts_error;
2692 END IF;
2693 ELSIF (p_create_update_flag = 'U') THEN
2694 -- old start_date_active, end_date_active has been selected from table
2695 -- and put into l_start_date_active, l_end_date_active
2696
2697 IF p_style_fmt_layout_rec.start_date_active <> fnd_api.g_miss_date
2698 AND p_style_fmt_layout_rec.start_date_active is not null
2699 THEN
2700 l_start_date_active := p_style_fmt_layout_rec.start_date_active;
2701 END IF;
2702
2703 IF p_style_fmt_layout_rec.end_date_active = fnd_api.g_miss_date
2704 THEN
2705 l_end_date_active := null;
2706 ELSIF p_style_fmt_layout_rec.end_date_active IS NOT NULL THEN
2707 l_end_date_active := p_style_fmt_layout_rec.end_date_active;
2708 END IF;
2709
2710 IF l_end_date_active IS NOT NULL
2711 AND l_end_date_active < l_start_date_active
2712 THEN
2713 fnd_message.set_name('AR', 'HZ_API_START_DATE_GREATER');
2714 fnd_msg_pub.add;
2715 x_return_status := fnd_api.g_ret_sts_error;
2716 END IF;
2717 END IF;
2718 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2719 hz_utility_v2pub.debug(p_message=>'end_date_active must be null or greater than start date. ' ||
2720 'x_return_status = ' || x_return_status,
2721 p_prefix =>l_debug_prefix,
2722 p_msg_level=>fnd_log.level_statement);
2723 END IF;
2724
2725 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
2726 hz_utility_v2pub.debug(p_message=>'validate_style_fmt_layout (+)',
2727 p_prefix=>l_debug_prefix,
2728 p_msg_level=>fnd_log.level_procedure);
2729 END IF;
2730
2731
2732 --disable_debug;
2733
2734 END validate_style_fmt_layout;
2735
2736 END HZ_NAME_ADDRESS_FMT_VALIDATE;