The following lines contain the word 'select', 'insert', 'update' or 'delete':
| non-updateable fields
| foreign key
| lookup types
|
| ARGUMENTS : IN:
| p_emp_history_rec
| create_update_flag
| OUT:
|
| RETURNS : NONE
|
| MODIFICATION HISTORY
| YKONG 26-OCT-1999 Created
| 02-AUG-2000 Jianying Huang Fixed Bug 1363124: Validation#2
| hz_credit_ratings.content_source_type
|
| 19-Feb-2002 DNB V3 enhancement base bug 2188696
| check obsoleted column suit_judge_ind
| validate colums migrated from hz_organization_profiles
| 01-03-2005 Rajib Ranjan Borah o SSM SST Integration and Extension.
| Modified call to HZ_MIXNM_UTILITY.
| ValidateContentSource according to
| modified signature
+======================================================================*/
procedure validate_credit_ratings(
p_credit_ratings_rec IN HZ_PARTY_INFO_PUB.credit_ratings_rec_type,
p_create_update_flag IN VARCHAR2,
x_return_status IN OUT NOCOPY VARCHAR2
) IS
l_dummy VARCHAR2(1);
IF ( p_create_update_flag = 'C' AND
p_credit_ratings_rec.suit_judge_ind IS NOT NULL AND
p_credit_ratings_rec.suit_judge_ind <> FND_API.G_MISS_CHAR )
OR
( p_create_update_flag = 'U' AND
( p_credit_ratings_rec.suit_judge_ind is null OR
p_credit_ratings_rec.suit_judge_ind <> FND_API.G_MISS_CHAR
) )
THEN
FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_OBSOLETE_COLUMN' );
IF (p_create_update_flag = 'C' AND
(p_credit_ratings_rec.party_id IS NULL OR
p_credit_ratings_rec.party_id = FND_API.G_MISS_NUM))
OR
(p_create_update_flag = 'U' AND
p_credit_ratings_rec.party_id IS NULL) THEN
FND_MESSAGE.SET_NAME('AR', 'HZ_API_MISSING_COLUMN');
IF (p_create_update_flag = 'C' AND
(p_credit_ratings_rec.content_source_type IS NULL OR
p_credit_ratings_rec.content_source_type = FND_API.G_MISS_CHAR))
OR
(p_create_update_flag = 'U' AND
p_credit_ratings_rec.content_source_type IS NULL) THEN
FND_MESSAGE.SET_NAME('AR', 'HZ_API_MISSING_COLUMN');
IF (p_create_update_flag = 'U') THEN
-- Bug 2197181: selecting actual_content_source for mix-n-match
BEGIN
SELECT party_id,
content_source_type,
suit_ind,
lien_ind,
judgement_ind,
bankruptcy_ind,
no_trade_ind,
prnt_hq_bkcy_ind,
credit_score_override_code,
debarment_ind,
credit_score_commentary,
credit_score_commentary2,
credit_score_commentary3,
credit_score_commentary4,
credit_score_commentary5,
credit_score_commentary6,
credit_score_commentary7,
credit_score_commentary8,
credit_score_commentary9,
credit_score_commentary10,
maximum_credit_currency_code,
actual_content_source
INTO l_party_id,
db_content_source_type,
l_suit_ind,
l_lien_ind,
l_judgement_ind,
l_bankruptcy_ind,
l_no_trade_ind,
l_prnt_hq_bkcy_ind,
l_credit_score_override_code,
l_debarment_ind,
l_credit_score_commentary,
l_credit_score_commentary2,
l_credit_score_commentary3,
l_credit_score_commentary4,
l_credit_score_commentary5,
l_credit_score_commentary6,
l_credit_score_commentary7,
l_credit_score_commentary8,
l_credit_score_commentary9,
l_credit_score_commentary10,
l_maximum_credit_currency_code,
db_actual_content_source
FROM hz_credit_ratings
where credit_rating_id = p_credit_ratings_rec.credit_rating_id;
FND_MESSAGE.SET_NAME('AR', 'HZ_API_NONUPDATEABLE_COLUMN');
FND_MESSAGE.SET_NAME('AR', 'HZ_API_NONUPDATEABLE_COLUMN');
SELECT count(*)
INTO l_count
FROM hz_parties
WHERE party_id = p_credit_ratings_rec.party_id;
(p_create_update_flag = 'C'
OR
(p_create_update_flag = 'U'
AND
p_credit_ratings_rec.suit_ind <> NVL(l_suit_ind, fnd_api.g_miss_char)
)
)
THEN
HZ_UTILITY_V2PUB.validate_lookup (
p_column => 'suit_ind',
p_lookup_type => 'YES/NO',
p_column_value => p_credit_ratings_rec.suit_ind,
x_return_status => x_return_status);
(p_create_update_flag = 'C'
OR
(p_create_update_flag = 'U'
AND
p_credit_ratings_rec.lien_ind <> NVL(l_lien_ind, fnd_api.g_miss_char)
)
)
THEN
HZ_UTILITY_V2PUB.validate_lookup (
p_column => 'lien_ind',
p_lookup_type => 'YES/NO',
p_column_value => p_credit_ratings_rec.lien_ind,
x_return_status => x_return_status);
(p_create_update_flag = 'C'
OR
(p_create_update_flag = 'U'
AND
p_credit_ratings_rec.judgement_ind <> NVL(l_judgement_ind, fnd_api.g_miss_char)
)
)
THEN
HZ_UTILITY_V2PUB.validate_lookup (
p_column => 'judgement_ind',
p_lookup_type => 'YES/NO',
p_column_value => p_credit_ratings_rec.judgement_ind,
x_return_status => x_return_status);
(p_create_update_flag = 'C'
OR
(p_create_update_flag = 'U'
AND
p_credit_ratings_rec.bankruptcy_ind <> NVL(l_bankruptcy_ind, fnd_api.g_miss_char)
)
)
THEN
HZ_UTILITY_V2PUB.validate_lookup (
p_column => 'bankruptcy_ind',
p_lookup_type => 'YES/NO',
p_column_value => p_credit_ratings_rec.bankruptcy_ind,
x_return_status => x_return_status);
(p_create_update_flag = 'C'
OR
(p_create_update_flag = 'U'
AND
p_credit_ratings_rec.no_trade_ind <> NVL(l_no_trade_ind, fnd_api.g_miss_char)
)
)
THEN
HZ_UTILITY_V2PUB.validate_lookup (
p_column => 'no_trade_ind',
p_lookup_type => 'YES/NO',
p_column_value => p_credit_ratings_rec.no_trade_ind,
x_return_status => x_return_status);
(p_create_update_flag = 'C'
OR
(p_create_update_flag = 'U'
AND
p_credit_ratings_rec.prnt_hq_bkcy_ind <> NVL(l_prnt_hq_bkcy_ind, fnd_api.g_miss_char)
)
)
THEN
HZ_UTILITY_V2PUB.validate_lookup (
p_column => 'prnt_hq_bkcy_ind',
p_lookup_type => 'PRNT_HQ_IND',
p_column_value => p_credit_ratings_rec.prnt_hq_bkcy_ind,
x_return_status => x_return_status );
(p_create_update_flag = 'C'
OR
(p_create_update_flag = 'U'
AND
p_credit_ratings_rec.credit_score_override_code <> NVL(l_credit_score_override_code, fnd_api.g_miss_char)
)
)
THEN
HZ_UTILITY_V2PUB.validate_lookup (
p_column => 'credit_score_override_code',
p_lookup_type => 'FAILURE_SCORE_OVERRIDE_CODE',
p_column_value => p_credit_ratings_rec.credit_score_override_code,
x_return_status => x_return_status );
(p_create_update_flag = 'C'
OR
(p_create_update_flag = 'U'
AND
p_credit_ratings_rec.debarment_ind <> NVL(l_debarment_ind, fnd_api.g_miss_char)
)
)
THEN
HZ_UTILITY_V2PUB.validate_lookup (
p_column => 'debarment_ind',
p_lookup_type => 'YES/NO',
p_column_value => p_credit_ratings_rec.debarment_ind,
x_return_status => x_return_status);
(p_create_update_flag = 'C'
OR
(p_create_update_flag = 'U'
AND
p_credit_ratings_rec.credit_score_commentary <> NVL(l_credit_score_commentary, fnd_api.g_miss_char)
)
)
THEN
HZ_UTILITY_V2PUB.validate_lookup (
p_column => 'credit_score_commentary',
p_lookup_type => 'CREDIT_SCORE_COMMENTARY',
p_column_value => p_credit_ratings_rec.credit_score_commentary,
x_return_status => x_return_status);
(p_create_update_flag = 'C'
OR
(p_create_update_flag = 'U'
AND
p_credit_ratings_rec.credit_score_commentary2 <> NVL(l_credit_score_commentary2, fnd_api.g_miss_char)
)
)
THEN
HZ_UTILITY_V2PUB.validate_lookup (
p_column => 'credit_score_commentary2',
p_lookup_type => 'CREDIT_SCORE_COMMENTARY',
p_column_value => p_credit_ratings_rec.credit_score_commentary2,
x_return_status => x_return_status);
(p_create_update_flag = 'C'
OR
(p_create_update_flag = 'U'
AND
p_credit_ratings_rec.credit_score_commentary3 <> NVL(l_credit_score_commentary3, fnd_api.g_miss_char)
)
)
THEN
HZ_UTILITY_V2PUB.validate_lookup (
p_column => 'credit_score_commentary3',
p_lookup_type => 'CREDIT_SCORE_COMMENTARY',
p_column_value => p_credit_ratings_rec.credit_score_commentary3,
x_return_status => x_return_status);
(p_create_update_flag = 'C'
OR
(p_create_update_flag = 'U'
AND
p_credit_ratings_rec.credit_score_commentary4 <> NVL(l_credit_score_commentary4, fnd_api.g_miss_char)
)
)
THEN
HZ_UTILITY_V2PUB.validate_lookup (
p_column => 'credit_score_commentary4',
p_lookup_type => 'CREDIT_SCORE_COMMENTARY',
p_column_value => p_credit_ratings_rec.credit_score_commentary4,
x_return_status => x_return_status);
(p_create_update_flag = 'C'
OR
(p_create_update_flag = 'U'
AND
p_credit_ratings_rec.credit_score_commentary5 <> NVL(l_credit_score_commentary5, fnd_api.g_miss_char)
)
)
THEN
HZ_UTILITY_V2PUB.validate_lookup (
p_column => 'credit_score_commentary5',
p_lookup_type => 'CREDIT_SCORE_COMMENTARY',
p_column_value => p_credit_ratings_rec.credit_score_commentary5,
x_return_status => x_return_status);
(p_create_update_flag = 'C'
OR
(p_create_update_flag = 'U'
AND
p_credit_ratings_rec.credit_score_commentary6 <> NVL(l_credit_score_commentary6, fnd_api.g_miss_char)
)
)
THEN
HZ_UTILITY_V2PUB.validate_lookup (
p_column => 'credit_score_commentary6',
p_lookup_type => 'CREDIT_SCORE_COMMENTARY',
p_column_value => p_credit_ratings_rec.credit_score_commentary6,
x_return_status => x_return_status);
(p_create_update_flag = 'C'
OR
(p_create_update_flag = 'U'
AND
p_credit_ratings_rec.credit_score_commentary7 <> NVL(l_credit_score_commentary7, fnd_api.g_miss_char)
)
)
THEN
HZ_UTILITY_V2PUB.validate_lookup (
p_column => 'credit_score_commentary7',
p_lookup_type => 'CREDIT_SCORE_COMMENTARY',
p_column_value => p_credit_ratings_rec.credit_score_commentary7,
x_return_status => x_return_status);
(p_create_update_flag = 'C'
OR
(p_create_update_flag = 'U'
AND
p_credit_ratings_rec.credit_score_commentary8 <> NVL(l_credit_score_commentary8, fnd_api.g_miss_char)
)
)
THEN
HZ_UTILITY_V2PUB.validate_lookup (
p_column => 'credit_score_commentary8',
p_lookup_type => 'CREDIT_SCORE_COMMENTARY',
p_column_value => p_credit_ratings_rec.credit_score_commentary8,
x_return_status => x_return_status);
(p_create_update_flag = 'C'
OR
(p_create_update_flag = 'U'
AND
p_credit_ratings_rec.credit_score_commentary9 <> NVL(l_credit_score_commentary9, fnd_api.g_miss_char)
)
)
THEN
HZ_UTILITY_V2PUB.validate_lookup (
p_column => 'credit_score_commentary9',
p_lookup_type => 'CREDIT_SCORE_COMMENTARY',
p_column_value => p_credit_ratings_rec.credit_score_commentary9,
x_return_status => x_return_status);
(p_create_update_flag = 'C'
OR
(p_create_update_flag = 'U'
AND
p_credit_ratings_rec.credit_score_commentary10 <> NVL(l_credit_score_commentary10, fnd_api.g_miss_char)
)
)
THEN
HZ_UTILITY_V2PUB.validate_lookup (
p_column => 'credit_score_commentary10',
p_lookup_type => 'CREDIT_SCORE_COMMENTARY',
p_column_value => p_credit_ratings_rec.credit_score_commentary10,
x_return_status => x_return_status);
(p_create_update_flag = 'C'
OR
(p_create_update_flag = 'U'
AND
p_credit_ratings_rec.failure_score_commentary <> NVL(l_credit_score_commentary, fnd_api.g_miss_char)
)
)
THEN
HZ_UTILITY_V2PUB.validate_lookup (
p_column => 'failure_score_commentary',
p_lookup_type => 'FAILURE_SCORE_COMMENTARY',
p_column_value => p_credit_ratings_rec.failure_score_commentary,
x_return_status => x_return_status);
(p_create_update_flag = 'C'
OR
(p_create_update_flag = 'U'
AND
p_credit_ratings_rec.failure_score_commentary2 <> NVL(l_credit_score_commentary2, fnd_api.g_miss_char)
)
)
THEN
HZ_UTILITY_V2PUB.validate_lookup (
p_column => 'failure_score_commentary2',
p_lookup_type => 'FAILURE_SCORE_COMMENTARY',
p_column_value => p_credit_ratings_rec.failure_score_commentary2,
x_return_status => x_return_status);
(p_create_update_flag = 'C'
OR
(p_create_update_flag = 'U'
AND
p_credit_ratings_rec.failure_score_commentary3 <> NVL(l_credit_score_commentary3, fnd_api.g_miss_char)
)
)
THEN
HZ_UTILITY_V2PUB.validate_lookup (
p_column => 'failure_score_commentary3',
p_lookup_type => 'FAILURE_SCORE_COMMENTARY',
p_column_value => p_credit_ratings_rec.failure_score_commentary3,
x_return_status => x_return_status);
(p_create_update_flag = 'C'
OR
(p_create_update_flag = 'U'
AND
p_credit_ratings_rec.failure_score_commentary4 <> NVL(l_credit_score_commentary4, fnd_api.g_miss_char)
)
)
THEN
HZ_UTILITY_V2PUB.validate_lookup (
p_column => 'failure_score_commentary4',
p_lookup_type => 'FAILURE_SCORE_COMMENTARY',
p_column_value => p_credit_ratings_rec.failure_score_commentary4,
x_return_status => x_return_status);
(p_create_update_flag = 'C'
OR
(p_create_update_flag = 'U'
AND
p_credit_ratings_rec.failure_score_commentary5 <> NVL(l_credit_score_commentary5, fnd_api.g_miss_char)
)
)
THEN
HZ_UTILITY_V2PUB.validate_lookup (
p_column => 'failure_score_commentary5',
p_lookup_type => 'FAILURE_SCORE_COMMENTARY',
p_column_value => p_credit_ratings_rec.failure_score_commentary5,
x_return_status => x_return_status);
(p_create_update_flag = 'C'
OR
(p_create_update_flag = 'U'
AND
p_credit_ratings_rec.failure_score_commentary6 <> NVL(l_credit_score_commentary6, fnd_api.g_miss_char)
)
)
THEN
HZ_UTILITY_V2PUB.validate_lookup (
p_column => 'failure_score_commentary6',
p_lookup_type => 'FAILURE_SCORE_COMMENTARY',
p_column_value => p_credit_ratings_rec.failure_score_commentary6,
x_return_status => x_return_status);
(p_create_update_flag = 'C'
OR
(p_create_update_flag = 'U'
AND
p_credit_ratings_rec.failure_score_commentary7 <> NVL(l_credit_score_commentary7, fnd_api.g_miss_char)
)
)
THEN
HZ_UTILITY_V2PUB.validate_lookup (
p_column => 'failure_score_commentary7',
p_lookup_type => 'FAILURE_SCORE_COMMENTARY',
p_column_value => p_credit_ratings_rec.failure_score_commentary7,
x_return_status => x_return_status);
(p_create_update_flag = 'C'
OR
(p_create_update_flag = 'U'
AND
p_credit_ratings_rec.failure_score_commentary8 <> NVL(l_credit_score_commentary8, fnd_api.g_miss_char)
)
)
THEN
HZ_UTILITY_V2PUB.validate_lookup (
p_column => 'failure_score_commentary8',
p_lookup_type => 'FAILURE_SCORE_COMMENTARY',
p_column_value => p_credit_ratings_rec.failure_score_commentary8,
x_return_status => x_return_status);
(p_create_update_flag = 'C'
OR
(p_create_update_flag = 'U'
AND
p_credit_ratings_rec.failure_score_commentary9 <> NVL(l_credit_score_commentary9, fnd_api.g_miss_char)
)
)
THEN
HZ_UTILITY_V2PUB.validate_lookup (
p_column => 'failure_score_commentary9',
p_lookup_type => 'FAILURE_SCORE_COMMENTARY',
p_column_value => p_credit_ratings_rec.failure_score_commentary9,
x_return_status => x_return_status);
(p_create_update_flag = 'C'
OR
(p_create_update_flag = 'U'
AND
p_credit_ratings_rec.failure_score_commentary10 <> NVL(l_credit_score_commentary10, fnd_api.g_miss_char)
)
)
THEN
HZ_UTILITY_V2PUB.validate_lookup (
p_column => 'failure_score_commentary10',
p_lookup_type => 'FAILURE_SCORE_COMMENTARY',
p_column_value => p_credit_ratings_rec.failure_score_commentary10,
x_return_status => x_return_status);
SELECT 'Y'
INTO l_dummy
FROM FND_CURRENCIES
WHERE CURRENCY_CODE = p_credit_ratings_rec.maximum_credit_currency_code
AND CURRENCY_FLAG = 'Y'
AND ENABLED_FLAG in ('Y', 'N');
p_create_update_flag => p_create_update_flag,
p_check_update_privilege => 'Y',
p_content_source_type => p_credit_ratings_rec.content_source_type,
p_old_content_source_type => db_content_source_type,
p_actual_content_source => p_credit_ratings_rec.actual_content_source,
p_old_actual_content_source => db_actual_content_source,
p_entity_name => 'HZ_CREDIT_RATINGS',
x_return_status => x_return_status );
| non-updateable fields
| foreign key
| lookup types
|
| ARGUMENTS : IN:
| p_financial_profile_rec
| create_update_flag
| OUT:
|
| RETURNS : NONE
|
| MODIFICATION HISTORY
| YKONG 26-OCT-1999 Created
|
+======================================================================*/
procedure validate_financial_profile(
p_financial_profile_rec IN HZ_PARTY_INFO_PUB.financial_profile_rec_type,
p_create_update_flag IN VARCHAR2,
x_return_status IN OUT NOCOPY VARCHAR2
) IS
l_party_id NUMBER;
IF (p_create_update_flag = 'C' AND
(p_financial_profile_rec.party_id IS NULL OR
p_financial_profile_rec.party_id = FND_API.G_MISS_NUM))
OR
(p_create_update_flag = 'U' AND
p_financial_profile_rec.party_id IS NULL) THEN
FND_MESSAGE.SET_NAME('AR', 'HZ_API_MISSING_COLUMN');
IF (p_create_update_flag = 'U' AND
p_financial_profile_rec.party_id <> FND_API.G_MISS_NUM) THEN
SELECT party_id
INTO l_party_id
FROM hz_financial_profile
where financial_profile_id
= p_financial_profile_rec.financial_profile_id ;
FND_MESSAGE.SET_NAME('AR', 'HZ_API_NONUPDATEABLE_COLUMN');
SELECT party_type
INTO l_party_type
FROM hz_parties
WHERE party_id = p_financial_profile_rec.party_id;
IF p_create_update_flag = 'C'
AND l_party_type <> 'PERSON'
AND l_party_type <> 'ORGANIZATION'
THEN
FND_MESSAGE.SET_NAME('AR', 'HZ_API_INVALID_PARTY_TYPE');