[Home] [Help]
PACKAGE BODY: APPS.HZ_PARTY_INFO_VAL
Source
1 package body HZ_PARTY_INFO_VAL AS
2 /*$Header: ARHPTIVB.pls 120.4 2006/05/26 10:03:38 vravicha ship $ */
3
4
5 --------------------------------------
6 -- declaration of private global varibles
7 --------------------------------------
8
9 --G_DEBUG BOOLEAN := FALSE;
10 g_debug_count NUMBER := 0;
11
12 --------------------------------------
13 -- declaration of private procedures and functions
14 --------------------------------------
15
16 /*PROCEDURE enable_debug;
17
18 PROCEDURE disable_debug;
19 */
20
21 --------------------------------------
22 -- private procedures and functions
23 --------------------------------------
24
25 /**
26 * PRIVATE PROCEDURE enable_debug
27 *
28 * DESCRIPTION
29 * Turn on debug mode.
30 *
31 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
32 * HZ_UTILITY_V2PUB.enable_debug
33 *
34 * MODIFICATION HISTORY
35 *
36 * 07-23-2001 Jianying Huang o Created.
37 *
38 */
39
40 /*PROCEDURE enable_debug IS
41
42 BEGIN
43
44 g_debug_count := g_debug_count + 1;
45
46 IF g_debug_count = 1 THEN
47 IF fnd_profile.value('HZ_API_FILE_DEBUG_ON') = 'Y' OR
48 fnd_profile.value('HZ_API_DBMS_DEBUG_ON') = 'Y'
49 THEN
50 hz_utility_v2pub.enable_debug;
51 g_debug := TRUE;
52 END IF;
53 END IF;
54
55 END enable_debug;
56 */
57
58 /**
59 * PRIVATE PROCEDURE disable_debug
60 *
61 * DESCRIPTION
62 * Turn off debug mode.
63 *
64 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
65 * hz_utility_v2pub.disable_debug
66 *
67 * MODIFICATION HISTORY
68 *
69 * 07-23-2001 Jianying Huang o Created.
70 *
71 */
72
73 /*PROCEDURE disable_debug IS
74
75 BEGIN
76
77 IF g_debug THEN
78 g_debug_count := g_debug_count - 1;
79
80 IF g_debug_count = 0 THEN
81 hz_utility_v2pub.disable_debug;
82 g_debug := FALSE;
83 END IF;
84 END IF;
85
86 END disable_debug;
87 */
88
89 /*======================================================================
90 | PROCEDURE (public)
91 | validate_credit_ratings
92 |
93 | DESCRIPTION
94 | Validates on:
95 | mandatory columns
96 | non-updateable fields
97 | foreign key
98 | lookup types
99 |
100 | ARGUMENTS : IN:
101 | p_emp_history_rec
102 | create_update_flag
103 | OUT:
104 |
105 | RETURNS : NONE
106 |
107 | MODIFICATION HISTORY
108 | YKONG 26-OCT-1999 Created
109 | 02-AUG-2000 Jianying Huang Fixed Bug 1363124: Validation#2
110 | hz_credit_ratings.content_source_type
111 |
112 | 19-Feb-2002 DNB V3 enhancement base bug 2188696
113 | check obsoleted column suit_judge_ind
114 | validate colums migrated from hz_organization_profiles
115 | 01-03-2005 Rajib Ranjan Borah o SSM SST Integration and Extension.
116 | Modified call to HZ_MIXNM_UTILITY.
117 | ValidateContentSource according to
118 | modified signature
119 +======================================================================*/
120
121 procedure validate_credit_ratings(
122 p_credit_ratings_rec IN HZ_PARTY_INFO_PUB.credit_ratings_rec_type,
123 p_create_update_flag IN VARCHAR2,
124 x_return_status IN OUT NOCOPY VARCHAR2
125 ) IS
126
127 l_dummy VARCHAR2(1);
128 l_count NUMBER;
129 l_party_id NUMBER;
130 l_credit_score_commentary VARCHAR2(30);
131 l_credit_score_commentary2 VARCHAR2(30);
132 l_credit_score_commentary3 VARCHAR2(30);
133 l_credit_score_commentary4 VARCHAR2(30);
134 l_credit_score_commentary5 VARCHAR2(30);
135 l_credit_score_commentary6 VARCHAR2(30);
136 l_credit_score_commentary7 VARCHAR2(30);
137 l_credit_score_commentary8 VARCHAR2(30);
138 l_credit_score_commentary9 VARCHAR2(30);
139 l_credit_score_commentary10 VARCHAR2(30);
140 l_suit_ind VARCHAR2(1);
141 l_lien_ind VARCHAR2(1);
142 l_judgement_ind VARCHAR2(1);
143 l_bankruptcy_ind VARCHAR2(1);
144 l_no_trade_ind VARCHAR2(1);
145 l_prnt_hq_bkcy_ind VARCHAR2(1);
146 l_credit_score_override_code hz_credit_ratings.credit_score_override_code%TYPE;
147 l_debarment_ind VARCHAR2(1);
148 l_maximum_credit_currency_code hz_credit_ratings.maximum_credit_currency_code%TYPE;
149
150 db_content_source_type hz_credit_ratings.content_source_type%TYPE;
151
152 -- Bug 2197181: added for mix-n-match
153 db_actual_content_source hz_credit_ratings.actual_content_source%TYPE;
154 l_debug_prefix VARCHAR2(30) := '';
155
156 BEGIN
157
158 --enable_debug;
159
160 -- Debug info.
161 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
162 hz_utility_v2pub.debug(p_message=>'validate_credit_ratings (+)',
163 p_prefix=>l_debug_prefix,
164 p_msg_level=>fnd_log.level_procedure);
165 END IF;
166
167 IF FND_PROFILE.VALUE( 'HZ_API_ERR_ON_OBSOLETE_COLUMN' ) = 'Y' THEN
168
169 IF ( p_create_update_flag = 'C' AND
170 p_credit_ratings_rec.suit_judge_ind IS NOT NULL AND
171 p_credit_ratings_rec.suit_judge_ind <> FND_API.G_MISS_CHAR )
172 OR
173 ( p_create_update_flag = 'U' AND
174 ( p_credit_ratings_rec.suit_judge_ind is null OR
175 p_credit_ratings_rec.suit_judge_ind <> FND_API.G_MISS_CHAR
176 ) )
177 THEN
178 FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_OBSOLETE_COLUMN' );
179 FND_MESSAGE.SET_TOKEN( 'COLUMN', 'suit_judge_ind' );
180 FND_MSG_PUB.ADD;
181 x_return_status := FND_API.G_RET_STS_ERROR;
182 END IF;
183
184 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
185 RAISE FND_API.G_EXC_ERROR;
186 END IF;
187 END IF;
188
189 -- mandatory fields: party_id, content_source_type
190 IF (p_create_update_flag = 'C' AND
191 (p_credit_ratings_rec.party_id IS NULL OR
192 p_credit_ratings_rec.party_id = FND_API.G_MISS_NUM))
193 OR
194 (p_create_update_flag = 'U' AND
195 p_credit_ratings_rec.party_id IS NULL) THEN
196
197 FND_MESSAGE.SET_NAME('AR', 'HZ_API_MISSING_COLUMN');
198 FND_MESSAGE.SET_TOKEN('COLUMN', 'party_id');
199 FND_MSG_PUB.ADD;
200 x_return_status := FND_API.G_RET_STS_ERROR;
201
202 END IF;
203
204 -- Bug 2197181: removed validation for mix-n-match
205 /*
206 IF (p_create_update_flag = 'C' AND
207 (p_credit_ratings_rec.content_source_type IS NULL OR
208 p_credit_ratings_rec.content_source_type = FND_API.G_MISS_CHAR))
209 OR
210 (p_create_update_flag = 'U' AND
211 p_credit_ratings_rec.content_source_type IS NULL) THEN
212
213 FND_MESSAGE.SET_NAME('AR', 'HZ_API_MISSING_COLUMN');
214 FND_MESSAGE.SET_TOKEN('COLUMN', 'content_source_type');
215 FND_MSG_PUB.ADD;
216 x_return_status := FND_API.G_RET_STS_ERROR;
217
218 END IF;
219 */
220 -- non updateable field
221
222 IF (p_create_update_flag = 'U') THEN
223
224
225 -- Bug 2197181: selecting actual_content_source for mix-n-match
226
227 BEGIN
228 SELECT party_id,
229 content_source_type,
230 suit_ind,
231 lien_ind,
232 judgement_ind,
233 bankruptcy_ind,
234 no_trade_ind,
235 prnt_hq_bkcy_ind,
236 credit_score_override_code,
237 debarment_ind,
238 credit_score_commentary,
239 credit_score_commentary2,
240 credit_score_commentary3,
241 credit_score_commentary4,
242 credit_score_commentary5,
243 credit_score_commentary6,
244 credit_score_commentary7,
245 credit_score_commentary8,
246 credit_score_commentary9,
247 credit_score_commentary10,
248 maximum_credit_currency_code,
249 actual_content_source
250 INTO l_party_id,
251 db_content_source_type,
252 l_suit_ind,
253 l_lien_ind,
254 l_judgement_ind,
255 l_bankruptcy_ind,
256 l_no_trade_ind,
257 l_prnt_hq_bkcy_ind,
258 l_credit_score_override_code,
259 l_debarment_ind,
260 l_credit_score_commentary,
261 l_credit_score_commentary2,
262 l_credit_score_commentary3,
263 l_credit_score_commentary4,
264 l_credit_score_commentary5,
265 l_credit_score_commentary6,
266 l_credit_score_commentary7,
267 l_credit_score_commentary8,
268 l_credit_score_commentary9,
269 l_credit_score_commentary10,
270 l_maximum_credit_currency_code,
271 db_actual_content_source
272 FROM hz_credit_ratings
273 where credit_rating_id = p_credit_ratings_rec.credit_rating_id;
274
275 IF (p_credit_ratings_rec.party_id <> FND_API.G_MISS_NUM) AND
276 (p_credit_ratings_rec.party_id <> l_party_id) THEN
277 FND_MESSAGE.SET_NAME('AR', 'HZ_API_NONUPDATEABLE_COLUMN');
278 FND_MESSAGE.SET_TOKEN('COLUMN', 'party_id');
279 FND_MSG_PUB.ADD;
280 x_return_status := FND_API.G_RET_STS_ERROR;
281 end if;
282
283 -- Bug 2197181: removed validation for mix-n-match
284 /*
285 IF p_credit_ratings_rec.content_source_type <> l_content_source_type THEN
286 FND_MESSAGE.SET_NAME('AR', 'HZ_API_NONUPDATEABLE_COLUMN');
287 FND_MESSAGE.SET_TOKEN('COLUMN', 'content_source_type');
288 FND_MSG_PUB.ADD;
289 x_return_status := FND_API.G_RET_STS_ERROR;
290 END IF;
291 */
292
293 EXCEPTION WHEN NO_DATA_FOUND THEN
294 FND_MESSAGE.SET_NAME('AR', 'HZ_API_NO_RECORD');
295 FND_MESSAGE.SET_TOKEN('RECORD', 'credit rating');
296 FND_MESSAGE.SET_TOKEN('VALUE', to_char(p_credit_ratings_rec.credit_rating_id));
297 FND_MSG_PUB.ADD;
298 x_return_status := FND_API.G_RET_STS_ERROR;
299 END;
300
301 END IF;
302
303 -- foreign key : party_id to hz_parties
304
305 IF (p_credit_ratings_rec.party_id is NOT NULL AND
306 p_credit_ratings_rec.party_id <> FND_API.G_MISS_NUM) THEN
307
308 SELECT count(*)
309 INTO l_count
310 FROM hz_parties
311 WHERE party_id = p_credit_ratings_rec.party_id;
312
313 IF l_count = 0 THEN
314 FND_MESSAGE.SET_NAME('AR', 'HZ_API_INVALID_FK');
315 FND_MESSAGE.SET_TOKEN('FK', 'party_id');
316 FND_MESSAGE.SET_TOKEN('COLUMN', 'party_id');
317 FND_MESSAGE.SET_TOKEN('TABLE', 'hz_parties');
318 FND_MSG_PUB.ADD;
319 x_return_status := FND_API.G_RET_STS_ERROR;
320 END IF;
321
322 END IF;
323
324 -- Bug 2197181: removed validation for mix-n-match
325 /*
326 --content_source_type validations.
327 --Bug 1363124: validation#2 of content_source_type
328
329 hz_common_pub.validate_lookup(
330 p_lookup_type => 'CONTENT_SOURCE_TYPE',
331 p_column => 'content_source_type',
332 p_column_value => p_credit_ratings_rec.content_source_type,
333 x_return_status => x_return_status
334 );
335 */
336
337 --Status Validation
338 hz_common_pub.validate_lookup('REGISTRY_STATUS','status',p_credit_ratings_rec.status,x_return_status);
339
340
341 -------------------------
342 -- validate SUIT_IND
343 -------------------------
344
345 -- suit_ind is lookup code in lookup type YES/NO
346 IF p_credit_ratings_rec.suit_ind IS NOT NULL
347 AND
348 p_credit_ratings_rec.suit_ind <> fnd_api.g_miss_char
349 AND
350 (p_create_update_flag = 'C'
351 OR
352 (p_create_update_flag = 'U'
353 AND
354 p_credit_ratings_rec.suit_ind <> NVL(l_suit_ind, fnd_api.g_miss_char)
355 )
356 )
357 THEN
358
359 HZ_UTILITY_V2PUB.validate_lookup (
360 p_column => 'suit_ind',
361 p_lookup_type => 'YES/NO',
362 p_column_value => p_credit_ratings_rec.suit_ind,
363 x_return_status => x_return_status);
364
365 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
366 hz_utility_v2pub.debug(p_message=>'suit_ind should be in lookup YES/NO. ' ||
367 'x_return_status = ' || x_return_status,
368 p_prefix =>l_debug_prefix,
369 p_msg_level=>fnd_log.level_statement);
370 END IF;
371 END IF;
372
373 -------------------------
374 -- validate LIEN_IND
375 -------------------------
376
377 -- lien_ind is lookup code in lookup type YES/NO
378 IF p_credit_ratings_rec.lien_ind IS NOT NULL
379 AND
380 p_credit_ratings_rec.lien_ind <> fnd_api.g_miss_char
381 AND
382 (p_create_update_flag = 'C'
383 OR
384 (p_create_update_flag = 'U'
385 AND
386 p_credit_ratings_rec.lien_ind <> NVL(l_lien_ind, fnd_api.g_miss_char)
387 )
388 )
389 THEN
390
391 HZ_UTILITY_V2PUB.validate_lookup (
392 p_column => 'lien_ind',
393 p_lookup_type => 'YES/NO',
394 p_column_value => p_credit_ratings_rec.lien_ind,
395 x_return_status => x_return_status);
396
397 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
398 hz_utility_v2pub.debug(p_message=>'lien_ind should be in lookup YES/NO. ' ||
399 'x_return_status = ' || x_return_status,
400 p_prefix =>l_debug_prefix,
401 p_msg_level=>fnd_log.level_statement);
402 END IF;
403 END IF;
404
405 -------------------------
406 -- validate JUDGEMENT_IND
407 -------------------------
408
409 -- judgement_ind is lookup code in lookup type YES/NO
410 IF p_credit_ratings_rec.judgement_ind IS NOT NULL
411 AND
412 p_credit_ratings_rec.judgement_ind <> fnd_api.g_miss_char
413 AND
414 (p_create_update_flag = 'C'
415 OR
416 (p_create_update_flag = 'U'
417 AND
418 p_credit_ratings_rec.judgement_ind <> NVL(l_judgement_ind, fnd_api.g_miss_char)
419 )
420 )
421 THEN
422
423 HZ_UTILITY_V2PUB.validate_lookup (
424 p_column => 'judgement_ind',
425 p_lookup_type => 'YES/NO',
426 p_column_value => p_credit_ratings_rec.judgement_ind,
427 x_return_status => x_return_status);
428
429 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
430 hz_utility_v2pub.debug(p_message=>'judgement_ind should be in lookup YES/NO. ' ||
431 'x_return_status = ' || x_return_status,
432 p_prefix =>l_debug_prefix,
433 p_msg_level=>fnd_log.level_statement);
434 END IF;
435 END IF;
436
437 -------------------------
438 -- validate BANKRUPTCY_IND
439 -------------------------
440
441 -- bankruptcy_ind is lookup code in lookup type YES/NO
442 IF p_credit_ratings_rec.bankruptcy_ind IS NOT NULL
443 AND
444 p_credit_ratings_rec.bankruptcy_ind <> fnd_api.g_miss_char
445 AND
446 (p_create_update_flag = 'C'
447 OR
448 (p_create_update_flag = 'U'
449 AND
450 p_credit_ratings_rec.bankruptcy_ind <> NVL(l_bankruptcy_ind, fnd_api.g_miss_char)
451 )
452 )
453 THEN
454
455 HZ_UTILITY_V2PUB.validate_lookup (
456 p_column => 'bankruptcy_ind',
457 p_lookup_type => 'YES/NO',
458 p_column_value => p_credit_ratings_rec.bankruptcy_ind,
459 x_return_status => x_return_status);
460
461 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
462 hz_utility_v2pub.debug(p_message=>'bankruptcy_ind should be in lookup YES/NO. ' ||
463 'x_return_status = ' || x_return_status,
464 p_prefix =>l_debug_prefix,
465 p_msg_level=>fnd_log.level_statement);
466 END IF;
467 END IF;
468
469 -------------------------
470 -- validate NO_TRADE_IND
471 -------------------------
472
473 -- no_trade_ind is lookup code in lookup type YES/NO
474 IF p_credit_ratings_rec.no_trade_ind IS NOT NULL
475 AND
476 p_credit_ratings_rec.no_trade_ind <> fnd_api.g_miss_char
477 AND
478 (p_create_update_flag = 'C'
479 OR
480 (p_create_update_flag = 'U'
481 AND
482 p_credit_ratings_rec.no_trade_ind <> NVL(l_no_trade_ind, fnd_api.g_miss_char)
483 )
484 )
485 THEN
486
487 HZ_UTILITY_V2PUB.validate_lookup (
488 p_column => 'no_trade_ind',
489 p_lookup_type => 'YES/NO',
490 p_column_value => p_credit_ratings_rec.no_trade_ind,
491 x_return_status => x_return_status);
492
493 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
494 hz_utility_v2pub.debug(p_message=>'no_trade_ind should be in lookup YES/NO. ' ||
495 'x_return_status = ' || x_return_status,
496 p_prefix =>l_debug_prefix,
497 p_msg_level=>fnd_log.level_statement);
498 END IF;
499 END IF;
500 -------------------------
501 -- validate PRNT_HQ_BKCY_IND
502 -------------------------
503
504 -- prnt_hq_bkcy_ind is lookup code in PRNT_HQ_IND
505 IF p_credit_ratings_rec.prnt_hq_bkcy_ind IS NOT NULL
506 AND
507 p_credit_ratings_rec.prnt_hq_bkcy_ind <> fnd_api.g_miss_char
508 AND
509 (p_create_update_flag = 'C'
510 OR
511 (p_create_update_flag = 'U'
512 AND
513 p_credit_ratings_rec.prnt_hq_bkcy_ind <> NVL(l_prnt_hq_bkcy_ind, fnd_api.g_miss_char)
514 )
515 )
516 THEN
517
518 HZ_UTILITY_V2PUB.validate_lookup (
519 p_column => 'prnt_hq_bkcy_ind',
520 p_lookup_type => 'PRNT_HQ_IND',
521 p_column_value => p_credit_ratings_rec.prnt_hq_bkcy_ind,
522 x_return_status => x_return_status );
523
524 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
525 hz_utility_v2pub.debug(p_message=>'prnt_hq_bkcy_ind should be in lookup PRNT_HQ_IND. ' ||
526 'x_return_status = ' || x_return_status,
527 p_prefix =>l_debug_prefix,
528 p_msg_level=>fnd_log.level_statement);
529 END IF;
530 END IF;
531
532 -------------------------
533 -- validate CREDIT_SCORE_OVERRIDE_CODE
534 -------------------------
535
536 -- credit_score_override_code is lookup code in FAILURE_SCORE_OVERRIDE_CODE
537 IF p_credit_ratings_rec.credit_score_override_code IS NOT NULL
538 AND
539 p_credit_ratings_rec.credit_score_override_code <> fnd_api.g_miss_char
540 AND
541 (p_create_update_flag = 'C'
542 OR
543 (p_create_update_flag = 'U'
544 AND
545 p_credit_ratings_rec.credit_score_override_code <> NVL(l_credit_score_override_code, fnd_api.g_miss_char)
546 )
547 )
548 THEN
549
550 HZ_UTILITY_V2PUB.validate_lookup (
551 p_column => 'credit_score_override_code',
552 p_lookup_type => 'FAILURE_SCORE_OVERRIDE_CODE',
553 p_column_value => p_credit_ratings_rec.credit_score_override_code,
554 x_return_status => x_return_status );
555
556 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
557 hz_utility_v2pub.debug(p_message=>'credit_score_override_code should be in lookup FAILURE_SCORE_OVERRIDE_CODE. ' ||
558 'x_return_status = ' || x_return_status,
559 p_prefix =>l_debug_prefix,
560 p_msg_level=>fnd_log.level_statement);
561 END IF;
562 END IF;
563 -------------------------
564 -- validate debarment_ind
565 -------------------------
566
567 -- debarment_ind is lookup code in lookup type YES/NO
568 IF p_credit_ratings_rec.debarment_ind IS NOT NULL
569 AND
570 p_credit_ratings_rec.debarment_ind <> fnd_api.g_miss_char
571 AND
572 (p_create_update_flag = 'C'
573 OR
574 (p_create_update_flag = 'U'
575 AND
576 p_credit_ratings_rec.debarment_ind <> NVL(l_debarment_ind, fnd_api.g_miss_char)
577 )
578 )
579 THEN
580
581 HZ_UTILITY_V2PUB.validate_lookup (
582 p_column => 'debarment_ind',
583 p_lookup_type => 'YES/NO',
584 p_column_value => p_credit_ratings_rec.debarment_ind,
585 x_return_status => x_return_status);
586
587 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
588 hz_utility_v2pub.debug(p_message=>'debarment_ind should be in lookup YES/NO. ' ||
589 'x_return_status = ' || x_return_status,
590 p_prefix =>l_debug_prefix,
591 p_msg_level=>fnd_log.level_statement);
592 END IF;
593 END IF;
594
595 -----------------------------------
596 -- validate credit_score_commentary
597 -----------------------------------
598
599 -- credit_score_commentary is lookup code in lookup type CREDIT_SCORE_COMMENTARY
600 IF p_credit_ratings_rec.credit_score_commentary IS NOT NULL
601 AND
602 p_credit_ratings_rec.credit_score_commentary <> fnd_api.g_miss_char
603 AND
604 (p_create_update_flag = 'C'
605 OR
606 (p_create_update_flag = 'U'
607 AND
608 p_credit_ratings_rec.credit_score_commentary <> NVL(l_credit_score_commentary, fnd_api.g_miss_char)
609 )
610 )
611 THEN
612
613 HZ_UTILITY_V2PUB.validate_lookup (
614 p_column => 'credit_score_commentary',
615 p_lookup_type => 'CREDIT_SCORE_COMMENTARY',
616 p_column_value => p_credit_ratings_rec.credit_score_commentary,
617 x_return_status => x_return_status);
618
619 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
620 hz_utility_v2pub.debug(p_message=>'credit_score_commentary is lookup code in lookup type CREDIT_SCORE_COMMENTARY. ' ||
621 'x_return_status = ' || x_return_status,
622 p_prefix =>l_debug_prefix,
623 p_msg_level=>fnd_log.level_statement);
624 END IF;
625
626 END IF;
627
628 ------------------------------------
629 -- validate credit_score_commentary2
630 ------------------------------------
631
632 -- credit_score_commentary is lookup code in lookup type CREDIT_SCORE_COMMENTARY
633 IF p_credit_ratings_rec.credit_score_commentary2 IS NOT NULL
634 AND
635 p_credit_ratings_rec.credit_score_commentary2 <> fnd_api.g_miss_char
636 AND
637 (p_create_update_flag = 'C'
638 OR
639 (p_create_update_flag = 'U'
640 AND
641 p_credit_ratings_rec.credit_score_commentary2 <> NVL(l_credit_score_commentary2, fnd_api.g_miss_char)
642 )
643 )
644 THEN
645
646 HZ_UTILITY_V2PUB.validate_lookup (
647 p_column => 'credit_score_commentary2',
648 p_lookup_type => 'CREDIT_SCORE_COMMENTARY',
649 p_column_value => p_credit_ratings_rec.credit_score_commentary2,
650 x_return_status => x_return_status);
651
652 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
653 hz_utility_v2pub.debug(p_message=>'credit_score_commentary2 is lookup code in lookup type CREDIT_SCORE_COMMENTARY. ' ||
654 'x_return_status = ' || x_return_status,
655 p_prefix =>l_debug_prefix,
656 p_msg_level=>fnd_log.level_statement);
657 END IF;
658 END IF;
659
660 ------------------------------------
661 -- validate credit_score_commentary3
662 ------------------------------------
663
664 -- credit_score_commentary3 is lookup code in lookup type CREDIT_SCORE_COMMENTARY
665 IF p_credit_ratings_rec.credit_score_commentary3 IS NOT NULL
666 AND
667 p_credit_ratings_rec.credit_score_commentary3 <> fnd_api.g_miss_char
668 AND
669 (p_create_update_flag = 'C'
670 OR
671 (p_create_update_flag = 'U'
672 AND
673 p_credit_ratings_rec.credit_score_commentary3 <> NVL(l_credit_score_commentary3, fnd_api.g_miss_char)
674 )
675 )
676 THEN
677
678 HZ_UTILITY_V2PUB.validate_lookup (
679 p_column => 'credit_score_commentary3',
680 p_lookup_type => 'CREDIT_SCORE_COMMENTARY',
681 p_column_value => p_credit_ratings_rec.credit_score_commentary3,
682 x_return_status => x_return_status);
683
684 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
685 hz_utility_v2pub.debug(p_message=>'credit_score_commentary3 is lookup code in lookup type CREDIT_SCORE_COMMENTARY. ' ||
686 'x_return_status = ' || x_return_status,
687 p_prefix =>l_debug_prefix,
688 p_msg_level=>fnd_log.level_statement);
689 END IF;
690 END IF;
691
692 ------------------------------------
693 -- validate credit_score_commentary4
694 ------------------------------------
695
696 -- credit_score_commentary4 is lookup code in lookup type CREDIT_SCORE_COMMENTARY
697 IF p_credit_ratings_rec.credit_score_commentary4 IS NOT NULL
698 AND
699 p_credit_ratings_rec.credit_score_commentary4 <> fnd_api.g_miss_char
700 AND
701 (p_create_update_flag = 'C'
702 OR
703 (p_create_update_flag = 'U'
704 AND
705 p_credit_ratings_rec.credit_score_commentary4 <> NVL(l_credit_score_commentary4, fnd_api.g_miss_char)
706 )
707 )
708 THEN
709
710 HZ_UTILITY_V2PUB.validate_lookup (
711 p_column => 'credit_score_commentary4',
712 p_lookup_type => 'CREDIT_SCORE_COMMENTARY',
713 p_column_value => p_credit_ratings_rec.credit_score_commentary4,
714 x_return_status => x_return_status);
715
716 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
717 hz_utility_v2pub.debug(p_message=>'credit_score_commentary4 is lookup code in lookup type CREDIT_SCORE_COMMENTARY. ' ||
718 'x_return_status = ' || x_return_status,
719 p_prefix =>l_debug_prefix,
720 p_msg_level=>fnd_log.level_statement);
721 END IF;
722 END IF;
723
724 ------------------------------------
725 -- validate credit_score_commentary5
726 ------------------------------------
727
728 -- credit_score_commentary5 is lookup code in lookup type CREDIT_SCORE_COMMENTARY
729 IF p_credit_ratings_rec.credit_score_commentary5 IS NOT NULL
730 AND
731 p_credit_ratings_rec.credit_score_commentary5 <> fnd_api.g_miss_char
732 AND
733 (p_create_update_flag = 'C'
734 OR
735 (p_create_update_flag = 'U'
736 AND
737 p_credit_ratings_rec.credit_score_commentary5 <> NVL(l_credit_score_commentary5, fnd_api.g_miss_char)
738 )
739 )
740 THEN
741
742 HZ_UTILITY_V2PUB.validate_lookup (
743 p_column => 'credit_score_commentary5',
744 p_lookup_type => 'CREDIT_SCORE_COMMENTARY',
745 p_column_value => p_credit_ratings_rec.credit_score_commentary5,
746 x_return_status => x_return_status);
747
748 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
749 hz_utility_v2pub.debug(p_message=>'credit_score_commentary5 is lookup code in lookup type CREDIT_SCORE_COMMENTARY. ' ||
750 'x_return_status = ' || x_return_status,
751 p_prefix =>l_debug_prefix,
752 p_msg_level=>fnd_log.level_statement);
753 END IF;
754 END IF;
755
756 ------------------------------------
757 -- validate credit_score_commentary6
758 ------------------------------------
759
760 -- credit_score_commentary6 is lookup code in lookup type CREDIT_SCORE_COMMENTARY
761 IF p_credit_ratings_rec.credit_score_commentary6 IS NOT NULL
762 AND
763 p_credit_ratings_rec.credit_score_commentary6 <> fnd_api.g_miss_char
764 AND
765 (p_create_update_flag = 'C'
766 OR
767 (p_create_update_flag = 'U'
768 AND
769 p_credit_ratings_rec.credit_score_commentary6 <> NVL(l_credit_score_commentary6, fnd_api.g_miss_char)
770 )
771 )
772 THEN
773
774 HZ_UTILITY_V2PUB.validate_lookup (
775 p_column => 'credit_score_commentary6',
776 p_lookup_type => 'CREDIT_SCORE_COMMENTARY',
777 p_column_value => p_credit_ratings_rec.credit_score_commentary6,
778 x_return_status => x_return_status);
779
780 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
781 hz_utility_v2pub.debug(p_message=>'credit_score_commentary6 is lookup code in lookup type CREDIT_SCORE_COMMENTARY. ' ||
782 'x_return_status = ' || x_return_status,
783 p_prefix =>l_debug_prefix,
784 p_msg_level=>fnd_log.level_statement);
785 END IF;
786 END IF;
787
788 ------------------------------------
789 -- validate credit_score_commentary7
790 ------------------------------------
791
792 -- credit_score_commentary7 is lookup code in lookup type CREDIT_SCORE_COMMENTARY
793 IF p_credit_ratings_rec.credit_score_commentary7 IS NOT NULL
794 AND
795 p_credit_ratings_rec.credit_score_commentary7 <> fnd_api.g_miss_char
796 AND
797 (p_create_update_flag = 'C'
798 OR
799 (p_create_update_flag = 'U'
800 AND
801 p_credit_ratings_rec.credit_score_commentary7 <> NVL(l_credit_score_commentary7, fnd_api.g_miss_char)
802 )
803 )
804 THEN
805
806 HZ_UTILITY_V2PUB.validate_lookup (
807 p_column => 'credit_score_commentary7',
808 p_lookup_type => 'CREDIT_SCORE_COMMENTARY',
809 p_column_value => p_credit_ratings_rec.credit_score_commentary7,
810 x_return_status => x_return_status);
811
812 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
813 hz_utility_v2pub.debug(p_message=>'credit_score_commentary7 is lookup code in lookup type CREDIT_SCORE_COMMENTARY. ' ||
814 'x_return_status = ' || x_return_status,
815 p_prefix =>l_debug_prefix,
816 p_msg_level=>fnd_log.level_statement);
817 END IF;
818 END IF;
819
820 ------------------------------------
821 -- validate credit_score_commentary8
822 ------------------------------------
823
824 -- credit_score_commentary8 is lookup code in lookup type CREDIT_SCORE_COMMENTARY
825 IF p_credit_ratings_rec.credit_score_commentary8 IS NOT NULL
826 AND
827 p_credit_ratings_rec.credit_score_commentary8 <> fnd_api.g_miss_char
828 AND
829 (p_create_update_flag = 'C'
830 OR
831 (p_create_update_flag = 'U'
832 AND
833 p_credit_ratings_rec.credit_score_commentary8 <> NVL(l_credit_score_commentary8, fnd_api.g_miss_char)
834 )
835 )
836 THEN
837
838 HZ_UTILITY_V2PUB.validate_lookup (
839 p_column => 'credit_score_commentary8',
840 p_lookup_type => 'CREDIT_SCORE_COMMENTARY',
841 p_column_value => p_credit_ratings_rec.credit_score_commentary8,
842 x_return_status => x_return_status);
843
844 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
845 hz_utility_v2pub.debug(p_message=>'credit_score_commentary8 is lookup code in lookup type CREDIT_SCORE_COMMENTARY. ' ||
846 'x_return_status = ' || x_return_status,
847 p_prefix =>l_debug_prefix,
848 p_msg_level=>fnd_log.level_statement);
849 END IF;
850 END IF;
851
852 ------------------------------------
853 -- validate credit_score_commentary9
854 ------------------------------------
855
856 -- credit_score_commentary9 is lookup code in lookup type CREDIT_SCORE_COMMENTARY
857 IF p_credit_ratings_rec.credit_score_commentary9 IS NOT NULL
858 AND
859 p_credit_ratings_rec.credit_score_commentary9 <> fnd_api.g_miss_char
860 AND
861 (p_create_update_flag = 'C'
862 OR
863 (p_create_update_flag = 'U'
864 AND
865 p_credit_ratings_rec.credit_score_commentary9 <> NVL(l_credit_score_commentary9, fnd_api.g_miss_char)
866 )
867 )
868 THEN
869
870 HZ_UTILITY_V2PUB.validate_lookup (
871 p_column => 'credit_score_commentary9',
872 p_lookup_type => 'CREDIT_SCORE_COMMENTARY',
873 p_column_value => p_credit_ratings_rec.credit_score_commentary9,
874 x_return_status => x_return_status);
875
876 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
877 hz_utility_v2pub.debug(p_message=>'credit_score_commentary9 is lookup code in lookup type CREDIT_SCORE_COMMENTARY. ' ||
878 'x_return_status = ' || x_return_status,
879 p_prefix =>l_debug_prefix,
880 p_msg_level=>fnd_log.level_statement);
881 END IF;
882 END IF;
883
884 -------------------------------------
885 -- validate credit_score_commentary10
886 -------------------------------------
887
888 -- credit_score_commentary10 is lookup code in lookup type CREDIT_SCORE_COMMENTARY
889 IF p_credit_ratings_rec.credit_score_commentary10 IS NOT NULL
890 AND
891 p_credit_ratings_rec.credit_score_commentary10 <> fnd_api.g_miss_char
892 AND
893 (p_create_update_flag = 'C'
894 OR
895 (p_create_update_flag = 'U'
896 AND
897 p_credit_ratings_rec.credit_score_commentary10 <> NVL(l_credit_score_commentary10, fnd_api.g_miss_char)
898 )
899 )
900 THEN
901
902 HZ_UTILITY_V2PUB.validate_lookup (
903 p_column => 'credit_score_commentary10',
904 p_lookup_type => 'CREDIT_SCORE_COMMENTARY',
905 p_column_value => p_credit_ratings_rec.credit_score_commentary10,
906 x_return_status => x_return_status);
907
908 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
909 hz_utility_v2pub.debug(p_message=>'credit_score_commentary10 is lookup code in lookup type CREDIT_SCORE_COMMENTARY. ' ||
910 'x_return_status = ' || x_return_status,
911 p_prefix =>l_debug_prefix,
912 p_msg_level=>fnd_log.level_statement);
913 END IF;
914 END IF;
915
916 ------------------------------------
917 -- validate failure_score_commentary
918 ------------------------------------
919
920 -- failure_score_commentary is lookup code in lookup type FAILURE_SCORE_COMMENTARY
921 IF p_credit_ratings_rec.failure_score_commentary IS NOT NULL
922 AND
923 p_credit_ratings_rec.failure_score_commentary <> fnd_api.g_miss_char
924 AND
925 (p_create_update_flag = 'C'
926 OR
927 (p_create_update_flag = 'U'
928 AND
929 p_credit_ratings_rec.failure_score_commentary <> NVL(l_credit_score_commentary, fnd_api.g_miss_char)
930 )
931 )
932 THEN
933 HZ_UTILITY_V2PUB.validate_lookup (
934 p_column => 'failure_score_commentary',
935 p_lookup_type => 'FAILURE_SCORE_COMMENTARY',
936 p_column_value => p_credit_ratings_rec.failure_score_commentary,
937 x_return_status => x_return_status);
938
939 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
940 hz_utility_v2pub.debug(p_message=>'failure_score_commentary is lookup code in lookup type FAILURE_SCORE_COMMENTARY. ' ||
941 'x_return_status = ' || x_return_status,
942 p_prefix =>l_debug_prefix,
943 p_msg_level=>fnd_log.level_statement);
944 END IF;
945 END IF;
946
947 -------------------------------------
948 -- validate failure_score_commentary2
949 -------------------------------------
950
951 -- failure_score_commentary2 is lookup code in lookup type FAILURE_SCORE_COMMENTARY
952 IF p_credit_ratings_rec.failure_score_commentary2 IS NOT NULL
953 AND
954 p_credit_ratings_rec.failure_score_commentary2 <> fnd_api.g_miss_char
955 AND
956 (p_create_update_flag = 'C'
957 OR
958 (p_create_update_flag = 'U'
959 AND
960 p_credit_ratings_rec.failure_score_commentary2 <> NVL(l_credit_score_commentary2, fnd_api.g_miss_char)
961 )
962 )
963 THEN
964
965 HZ_UTILITY_V2PUB.validate_lookup (
966 p_column => 'failure_score_commentary2',
967 p_lookup_type => 'FAILURE_SCORE_COMMENTARY',
968 p_column_value => p_credit_ratings_rec.failure_score_commentary2,
969 x_return_status => x_return_status);
970
971 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
972 hz_utility_v2pub.debug(p_message=>'failure_score_commentary2 is lookup code in lookup type FAILURE_SCORE_COMMENTARY. ' ||
973 'x_return_status = ' || x_return_status,
974 p_prefix =>l_debug_prefix,
975 p_msg_level=>fnd_log.level_statement);
976 END IF;
977 END IF;
978
979 -------------------------------------
980 -- validate failure_score_commentary3
981 -------------------------------------
982
983 -- failure_score_commentary3 is lookup code in lookup type FAILURE_SCORE_COMMENTARY
984 IF p_credit_ratings_rec.failure_score_commentary3 IS NOT NULL
985 AND
986 p_credit_ratings_rec.failure_score_commentary3 <> fnd_api.g_miss_char
987 AND
988 (p_create_update_flag = 'C'
989 OR
990 (p_create_update_flag = 'U'
991 AND
992 p_credit_ratings_rec.failure_score_commentary3 <> NVL(l_credit_score_commentary3, fnd_api.g_miss_char)
993 )
994 )
995 THEN
996
997 HZ_UTILITY_V2PUB.validate_lookup (
998 p_column => 'failure_score_commentary3',
999 p_lookup_type => 'FAILURE_SCORE_COMMENTARY',
1000 p_column_value => p_credit_ratings_rec.failure_score_commentary3,
1001 x_return_status => x_return_status);
1002
1003 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1004 hz_utility_v2pub.debug(p_message=>'failure_score_commentary3 is lookup code in lookup type FAILURE_SCORE_COMMENTARY. ' ||
1005 'x_return_status = ' || x_return_status,
1006 p_prefix =>l_debug_prefix,
1007 p_msg_level=>fnd_log.level_statement);
1008 END IF;
1009 END IF;
1010
1011 -------------------------------------
1012 -- validate failure_score_commentary4
1013 -------------------------------------
1014
1015 -- failure_score_commentary4 is lookup code in lookup type FAILURE_SCORE_COMMENTARY
1016 IF p_credit_ratings_rec.failure_score_commentary4 IS NOT NULL
1017 AND
1018 p_credit_ratings_rec.failure_score_commentary4 <> fnd_api.g_miss_char
1019 AND
1020 (p_create_update_flag = 'C'
1021 OR
1022 (p_create_update_flag = 'U'
1023 AND
1024 p_credit_ratings_rec.failure_score_commentary4 <> NVL(l_credit_score_commentary4, fnd_api.g_miss_char)
1025 )
1026 )
1027 THEN
1028
1029 HZ_UTILITY_V2PUB.validate_lookup (
1030 p_column => 'failure_score_commentary4',
1031 p_lookup_type => 'FAILURE_SCORE_COMMENTARY',
1032 p_column_value => p_credit_ratings_rec.failure_score_commentary4,
1033 x_return_status => x_return_status);
1034
1035 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1036 hz_utility_v2pub.debug(p_message=>'failure_score_commentary4 is lookup code in lookup type FAILURE_SCORE_COMMENTARY. ' ||
1037 'x_return_status = ' || x_return_status,
1038 p_prefix =>l_debug_prefix,
1039 p_msg_level=>fnd_log.level_statement);
1040 END IF;
1041 END IF;
1042
1043 -------------------------------------
1044 -- validate failure_score_commentary5
1045 -------------------------------------
1046
1047 -- failure_score_commentary5 is lookup code in lookup type FAILURE_SCORE_COMMENTARY
1048 IF p_credit_ratings_rec.failure_score_commentary5 IS NOT NULL
1049 AND
1050 p_credit_ratings_rec.failure_score_commentary5 <> fnd_api.g_miss_char
1051 AND
1052 (p_create_update_flag = 'C'
1053 OR
1054 (p_create_update_flag = 'U'
1055 AND
1056 p_credit_ratings_rec.failure_score_commentary5 <> NVL(l_credit_score_commentary5, fnd_api.g_miss_char)
1057 )
1058 )
1059 THEN
1060
1061 HZ_UTILITY_V2PUB.validate_lookup (
1062 p_column => 'failure_score_commentary5',
1063 p_lookup_type => 'FAILURE_SCORE_COMMENTARY',
1064 p_column_value => p_credit_ratings_rec.failure_score_commentary5,
1065 x_return_status => x_return_status);
1066
1067 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1068 hz_utility_v2pub.debug(p_message=>'failure_score_commentary5 is lookup code in lookup type FAILURE_SCORE_COMMENTARY. ' ||
1069 'x_return_status = ' || x_return_status,
1070 p_prefix =>l_debug_prefix,
1071 p_msg_level=>fnd_log.level_statement);
1072 END IF;
1073 END IF;
1074
1075 -------------------------------------
1076 -- validate failure_score_commentary6
1077 -------------------------------------
1078
1079 -- failure_score_commentary6 is lookup code in lookup type FAILURE_SCORE_COMMENTARY
1080 IF p_credit_ratings_rec.failure_score_commentary6 IS NOT NULL
1081 AND
1082 p_credit_ratings_rec.failure_score_commentary6 <> fnd_api.g_miss_char
1083 AND
1084 (p_create_update_flag = 'C'
1085 OR
1086 (p_create_update_flag = 'U'
1087 AND
1088 p_credit_ratings_rec.failure_score_commentary6 <> NVL(l_credit_score_commentary6, fnd_api.g_miss_char)
1089 )
1090 )
1091 THEN
1092
1093 HZ_UTILITY_V2PUB.validate_lookup (
1094 p_column => 'failure_score_commentary6',
1095 p_lookup_type => 'FAILURE_SCORE_COMMENTARY',
1096 p_column_value => p_credit_ratings_rec.failure_score_commentary6,
1097 x_return_status => x_return_status);
1098
1099 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1100 hz_utility_v2pub.debug(p_message=>'failure_score_commentary6 is lookup code in lookup type FAILURE_SCORE_COMMENTARY. ' ||
1101 'x_return_status = ' || x_return_status,
1102 p_prefix =>l_debug_prefix,
1103 p_msg_level=>fnd_log.level_statement);
1104 END IF;
1105 END IF;
1106
1107 -------------------------------------
1108 -- validate failure_score_commentary7
1109 -------------------------------------
1110
1111 -- failure_score_commentary7 is lookup code in lookup type FAILURE_SCORE_COMMENTARY
1112 IF p_credit_ratings_rec.failure_score_commentary7 IS NOT NULL
1113 AND
1114 p_credit_ratings_rec.failure_score_commentary7 <> fnd_api.g_miss_char
1115 AND
1116 (p_create_update_flag = 'C'
1117 OR
1118 (p_create_update_flag = 'U'
1119 AND
1120 p_credit_ratings_rec.failure_score_commentary7 <> NVL(l_credit_score_commentary7, fnd_api.g_miss_char)
1121 )
1122 )
1123 THEN
1124
1125 HZ_UTILITY_V2PUB.validate_lookup (
1126 p_column => 'failure_score_commentary7',
1127 p_lookup_type => 'FAILURE_SCORE_COMMENTARY',
1128 p_column_value => p_credit_ratings_rec.failure_score_commentary7,
1129 x_return_status => x_return_status);
1130
1131 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1132 hz_utility_v2pub.debug(p_message=>'failure_score_commentary7 is lookup code in lookup type FAILURE_SCORE_COMMENTARY. ' ||
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 END IF;
1138
1139 -------------------------------------
1140 -- validate failure_score_commentary8
1141 -------------------------------------
1142
1143 -- failure_score_commentary8 is lookup code in lookup type FAILURE_SCORE_COMMENTARY
1144 IF p_credit_ratings_rec.failure_score_commentary8 IS NOT NULL
1145 AND
1146 p_credit_ratings_rec.failure_score_commentary8 <> fnd_api.g_miss_char
1147 AND
1148 (p_create_update_flag = 'C'
1149 OR
1150 (p_create_update_flag = 'U'
1151 AND
1152 p_credit_ratings_rec.failure_score_commentary8 <> NVL(l_credit_score_commentary8, fnd_api.g_miss_char)
1153 )
1154 )
1155 THEN
1156
1157 HZ_UTILITY_V2PUB.validate_lookup (
1158 p_column => 'failure_score_commentary8',
1159 p_lookup_type => 'FAILURE_SCORE_COMMENTARY',
1160 p_column_value => p_credit_ratings_rec.failure_score_commentary8,
1161 x_return_status => x_return_status);
1162
1163 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1164 hz_utility_v2pub.debug(p_message=>'failure_score_commentary8 is lookup code in lookup type FAILURE_SCORE_COMMENTARY. ' ||
1165 'x_return_status = ' || x_return_status,
1166 p_prefix =>l_debug_prefix,
1167 p_msg_level=>fnd_log.level_statement);
1168 END IF;
1169 END IF;
1170
1171 -------------------------------------
1172 -- validate failure_score_commentary9
1173 -------------------------------------
1174
1175 -- failure_score_commentary9 is lookup code in lookup type FAILURE_SCORE_COMMENTARY
1176 IF p_credit_ratings_rec.failure_score_commentary9 IS NOT NULL
1177 AND
1178 p_credit_ratings_rec.failure_score_commentary9 <> fnd_api.g_miss_char
1179 AND
1180 (p_create_update_flag = 'C'
1181 OR
1182 (p_create_update_flag = 'U'
1183 AND
1184 p_credit_ratings_rec.failure_score_commentary9 <> NVL(l_credit_score_commentary9, fnd_api.g_miss_char)
1185 )
1186 )
1187 THEN
1188
1189 HZ_UTILITY_V2PUB.validate_lookup (
1190 p_column => 'failure_score_commentary9',
1191 p_lookup_type => 'FAILURE_SCORE_COMMENTARY',
1192 p_column_value => p_credit_ratings_rec.failure_score_commentary9,
1193 x_return_status => x_return_status);
1194
1195 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1196 hz_utility_v2pub.debug(p_message=>'failure_score_commentary9 is lookup code in lookup type FAILURE_SCORE_COMMENTARY. ' ||
1197 'x_return_status = ' || x_return_status,
1198 p_prefix =>l_debug_prefix,
1199 p_msg_level=>fnd_log.level_statement);
1200 END IF;
1201 END IF;
1202
1203 --------------------------------------
1204 -- validate failure_score_commentary10
1205 --------------------------------------
1206
1207 -- failure_score_commentary10 is lookup code in lookup type FAILURE_SCORE_COMMENTARY
1208 IF p_credit_ratings_rec.failure_score_commentary10 IS NOT NULL
1209 AND
1210 p_credit_ratings_rec.failure_score_commentary10 <> fnd_api.g_miss_char
1211 AND
1212 (p_create_update_flag = 'C'
1213 OR
1214 (p_create_update_flag = 'U'
1215 AND
1216 p_credit_ratings_rec.failure_score_commentary10 <> NVL(l_credit_score_commentary10, fnd_api.g_miss_char)
1217 )
1218 )
1219 THEN
1220
1221 HZ_UTILITY_V2PUB.validate_lookup (
1222 p_column => 'failure_score_commentary10',
1223 p_lookup_type => 'FAILURE_SCORE_COMMENTARY',
1224 p_column_value => p_credit_ratings_rec.failure_score_commentary10,
1225 x_return_status => x_return_status);
1226
1227 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1228 hz_utility_v2pub.debug(p_message=>'failure_score_commentary10 is lookup code in lookup type FAILURE_SCORE_COMMENTARY. ' ||
1229 'x_return_status = ' || x_return_status,
1230 p_prefix =>l_debug_prefix,
1231 p_msg_level=>fnd_log.level_statement);
1232 END IF;
1233 END IF;
1234
1235
1236 ----------------------------------------
1237 -- validate maximum_credit_currency_code
1238 ----------------------------------------
1239
1240 -- maximum_credit_currency_code is foreign key of fnd_currencies.currency_code
1241
1242 IF p_credit_ratings_rec.maximum_credit_currency_code IS NOT NULL
1243 AND
1244 p_credit_ratings_rec.maximum_credit_currency_code <> fnd_api.g_miss_char
1245 THEN
1246 BEGIN
1247 SELECT 'Y'
1248 INTO l_dummy
1249 FROM FND_CURRENCIES
1250 WHERE CURRENCY_CODE = p_credit_ratings_rec.maximum_credit_currency_code
1251 AND CURRENCY_FLAG = 'Y'
1252 AND ENABLED_FLAG in ('Y', 'N');
1253 EXCEPTION
1254 WHEN NO_DATA_FOUND THEN
1255 fnd_message.set_name('AR', 'HZ_API_INVALID_FK');
1256 fnd_message.set_token('FK', 'maximum_credit_currency_code');
1257 fnd_message.set_token('COLUMN', 'currency_code');
1258 fnd_message.set_token('TABLE', 'fnd_currencies');
1259 fnd_msg_pub.add;
1260 x_return_status := fnd_api.g_ret_sts_error;
1261 END;
1262
1263 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1264 hz_utility_v2pub.debug(p_message=>'maximum_credit_currency_code is foreign key of fnd_currencies.currency_code. ' ||
1265 'x_return_status = ' || x_return_status,
1266 p_prefix =>l_debug_prefix,
1267 p_msg_level=>fnd_log.level_statement);
1268 END IF;
1269
1270 END IF;
1271
1272 -- Bug 2197181: Added validation for mix-n-match
1273
1274 ----------------------------------------
1275 -- validate content_source_type and actual_content_source_type
1276 ----------------------------------------
1277 -- SSM SST Integration and Extension
1278 -- Passed HZ_CREDIT_RATINGS for newly added paratemer p_entity_name
1279
1280 HZ_MIXNM_UTILITY.ValidateContentSource (
1281 p_api_version => 'V1',
1282 p_create_update_flag => p_create_update_flag,
1283 p_check_update_privilege => 'Y',
1284 p_content_source_type => p_credit_ratings_rec.content_source_type,
1285 p_old_content_source_type => db_content_source_type,
1286 p_actual_content_source => p_credit_ratings_rec.actual_content_source,
1287 p_old_actual_content_source => db_actual_content_source,
1288 p_entity_name => 'HZ_CREDIT_RATINGS',
1289 x_return_status => x_return_status );
1290
1291 --disable_debug;
1292
1293 END validate_credit_ratings;
1294
1295 /*======================================================================
1296 | PROCEDURE (public)
1297 | validate_financial_profile
1298 |
1299 | DESCRIPTION
1300 | Validates on:
1301 | mandatory columns
1302 | non-updateable fields
1303 | foreign key
1304 | lookup types
1305 |
1306 | ARGUMENTS : IN:
1307 | p_financial_profile_rec
1308 | create_update_flag
1309 | OUT:
1310 |
1311 | RETURNS : NONE
1312 |
1313 | MODIFICATION HISTORY
1314 | YKONG 26-OCT-1999 Created
1315 |
1316 +======================================================================*/
1317
1318 procedure validate_financial_profile(
1319 p_financial_profile_rec IN HZ_PARTY_INFO_PUB.financial_profile_rec_type,
1320 p_create_update_flag IN VARCHAR2,
1321 x_return_status IN OUT NOCOPY VARCHAR2
1322 ) IS
1323 l_party_id NUMBER;
1324 l_party_type HZ_PARTIES.PARTY_TYPE%TYPE;
1325
1326 BEGIN
1327 -- mandatory fields: party_id
1328 IF (p_create_update_flag = 'C' AND
1329 (p_financial_profile_rec.party_id IS NULL OR
1330 p_financial_profile_rec.party_id = FND_API.G_MISS_NUM))
1331 OR
1332 (p_create_update_flag = 'U' AND
1333 p_financial_profile_rec.party_id IS NULL) THEN
1334
1335 FND_MESSAGE.SET_NAME('AR', 'HZ_API_MISSING_COLUMN');
1336 FND_MESSAGE.SET_TOKEN('COLUMN', 'party_id');
1337 FND_MSG_PUB.ADD;
1338 x_return_status := FND_API.G_RET_STS_ERROR;
1339
1340 END IF;
1341
1342 -- non updateable field
1343
1344 IF (p_create_update_flag = 'U' AND
1345 p_financial_profile_rec.party_id <> FND_API.G_MISS_NUM) THEN
1346
1347 SELECT party_id
1348 INTO l_party_id
1349 FROM hz_financial_profile
1350 where financial_profile_id
1351 = p_financial_profile_rec.financial_profile_id ;
1352
1353 if l_party_id <> p_financial_profile_rec.party_id then
1354 FND_MESSAGE.SET_NAME('AR', 'HZ_API_NONUPDATEABLE_COLUMN');
1355 FND_MESSAGE.SET_TOKEN('COLUMN', 'party_id');
1356 FND_MSG_PUB.ADD;
1357 x_return_status := FND_API.G_RET_STS_ERROR;
1358 end if;
1359
1360 END IF;
1361
1362 -- foreign key : party_id to hz_parties
1363
1364 IF (p_financial_profile_rec.party_id is NOT NULL AND
1365 p_financial_profile_rec.party_id <> FND_API.G_MISS_NUM) THEN
1366
1367 -- Bug 4461511.
1368 BEGIN
1369 SELECT party_type
1370 INTO l_party_type
1371 FROM hz_parties
1372 WHERE party_id = p_financial_profile_rec.party_id;
1373
1374 IF p_create_update_flag = 'C'
1375 AND l_party_type <> 'PERSON'
1376 AND l_party_type <> 'ORGANIZATION'
1377 THEN
1378 FND_MESSAGE.SET_NAME('AR', 'HZ_API_INVALID_PARTY_TYPE');
1379 FND_MESSAGE.SET_TOKEN('PARTY_ID', to_char(p_financial_profile_rec.party_id));
1380 FND_MESSAGE.SET_TOKEN('TYPE', 'ORGANIZATION or PERSON');
1381 FND_MSG_PUB.ADD;
1382 x_return_status := FND_API.G_RET_STS_ERROR;
1383 END IF;
1384
1385 EXCEPTION
1386 WHEN NO_DATA_FOUND THEN
1387 FND_MESSAGE.SET_NAME('AR', 'HZ_API_INVALID_FK');
1388 FND_MESSAGE.SET_TOKEN('FK', 'party_id');
1389 FND_MESSAGE.SET_TOKEN('COLUMN', 'party_id');
1390 FND_MESSAGE.SET_TOKEN('TABLE', 'hz_parties');
1391 FND_MSG_PUB.ADD;
1392 x_return_status := FND_API.G_RET_STS_ERROR;
1393 END;
1394 END IF;
1395
1396
1397 -- lookup type validation : access_authority_granted
1398
1399 hz_common_pub.validate_lookup('YES/NO', 'ACCESS_AUTHORITY_GRANTED',
1400 p_financial_profile_rec.access_authority_granted, x_return_status);
1401 --Status Validation
1402 hz_common_pub.validate_lookup('REGISTRY_STATUS','status',p_financial_profile_rec.status,x_return_status);
1403 END validate_financial_profile;
1404
1405 END HZ_PARTY_INFO_VAL;