[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
g_per_selected_datasources VARCHAR2(600);
g_org_selected_datasources VARCHAR2(600);
PROCEDURE do_update_person_profile(
p_person_rec IN PERSON_REC_TYPE,
p_old_person_rec IN PERSON_REC_TYPE,
p_data_source_type IN VARCHAR2,
x_profile_id OUT NOCOPY NUMBER
);
PROCEDURE do_update_org_profile(
p_organization_rec IN ORGANIZATION_REC_TYPE,
p_old_organization_rec IN ORGANIZATION_REC_TYPE,
p_data_source_type IN VARCHAR2,
x_profile_id OUT NOCOPY NUMBER
);
PROCEDURE do_update_party_profile (
p_party_type IN VARCHAR2,
p_person_rec IN PERSON_REC_TYPE,
p_old_person_rec IN PERSON_REC_TYPE := G_MISS_PERSON_REC,
p_organization_rec IN ORGANIZATION_REC_TYPE,
p_old_organization_rec IN ORGANIZATION_REC_TYPE := G_MISS_ORGANIZATION_REC,
p_data_source_type IN VARCHAR2,
x_profile_id OUT NOCOPY NUMBER
);
PROCEDURE do_create_update_party_only(
p_create_update_flag IN VARCHAR2,
p_party_type IN VARCHAR2,
-- p_party_id is used in update mode only.
p_party_id IN NUMBER := NULL,
p_check_object_version_number IN VARCHAR2 := 'Y',
p_party_object_version_number IN OUT NOCOPY NUMBER,
p_person_rec IN PERSON_REC_TYPE := G_MISS_PERSON_REC,
p_old_person_rec IN PERSON_REC_TYPE := G_MISS_PERSON_REC,
p_organization_rec IN ORGANIZATION_REC_TYPE := G_MISS_ORGANIZATION_REC,
p_old_organization_rec IN ORGANIZATION_REC_TYPE := G_MISS_ORGANIZATION_REC,
p_group_rec IN GROUP_REC_TYPE := G_MISS_GROUP_REC,
p_old_group_rec IN GROUP_REC_TYPE := G_MISS_GROUP_REC,
-- x_party_id and x_party_number are used in create mode.
x_party_id OUT NOCOPY NUMBER,
x_party_number OUT NOCOPY VARCHAR2
);
PROCEDURE do_update_party (
p_party_type IN VARCHAR2,
p_person_rec IN OUT NOCOPY PERSON_REC_TYPE,
p_old_person_rec IN PERSON_REC_TYPE := G_MISS_PERSON_REC,
p_organization_rec IN OUT NOCOPY ORGANIZATION_REC_TYPE,
p_old_organization_rec IN ORGANIZATION_REC_TYPE := G_MISS_ORGANIZATION_REC,
p_group_rec IN OUT NOCOPY GROUP_REC_TYPE,
p_old_group_rec IN GROUP_REC_TYPE := G_MISS_GROUP_REC,
p_party_object_version_number IN OUT NOCOPY NUMBER,
x_profile_id OUT NOCOPY NUMBER,
x_return_status IN OUT NOCOPY VARCHAR2
);
PROCEDURE do_update_party_rel_name(
p_party_id IN NUMBER,
p_party_name IN HZ_PARTIES.PARTY_NAME%TYPE
);
p_create_update_flag IN VARCHAR2,
p_party_type IN VARCHAR2,
p_organization_rec IN ORGANIZATION_REC_TYPE := G_MISS_ORGANIZATION_REC,
p_old_organization_rec IN ORGANIZATION_REC_TYPE := G_MISS_ORGANIZATION_REC,
p_person_rec IN PERSON_REC_TYPE := G_MISS_PERSON_REC,
p_old_person_rec IN PERSON_REC_TYPE := G_MISS_PERSON_REC,
p_group_rec IN GROUP_REC_TYPE := G_MISS_GROUP_REC,
p_old_group_rec IN GROUP_REC_TYPE := G_MISS_GROUP_REC,
p_data_source_type IN VARCHAR2,
x_return_status IN OUT NOCOPY VARCHAR2
);
p_create_update_flag IN VARCHAR2,
p_organization_rec IN ORGANIZATION_REC_TYPE,
x_credit_rating_rec OUT NOCOPY hz_party_info_pub.credit_ratings_rec_type
);
p_create_update_flag IN VARCHAR2,
p_organization_rec IN ORGANIZATION_REC_TYPE,
x_return_status IN OUT NOCOPY VARCHAR2
);
PROCEDURE update_party_search(p_party_id IN NUMBER,
p_old_party_name IN VARCHAR2,
p_new_party_name IN VARCHAR2,
p_old_tax_reference IN VARCHAR2,
p_new_tax_reference IN VARCHAR2);
PROCEDURE update_rel_person_search(p_old_person_rec IN HZ_PARTY_V2PUB.PERSON_REC_TYPE,
p_new_person_rec IN HZ_PARTY_V2PUB.PERSON_REC_TYPE);
SELECT party_type, party_number
FROM hz_parties
WHERE party_id = p_party_id;
* PRIVATE PROCEDURE do_update_party_rel_name
*
* DESCRIPTION
* update party relationships' party name when subject or object
* party's name has been changed. The procedure should be
* a recursive one because we might have a party relationship
* whose subject or object party is a relationship too.
*
* EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
*
* ARGUMENTS
* IN:
* p_party_id
* p_party_name
* OUT:
* IN/ OUT:
*
* NOTES
*
* MODIFICATION HISTORY
*
*/
PROCEDURE do_update_party_rel_name(
p_party_id IN NUMBER,
p_party_name IN HZ_PARTIES.PARTY_NAME%TYPE
) IS
l_party_name HZ_PARTIES.party_name%TYPE;
SELECT r.party_id, r.object_id, o.party_name, r.subject_id, s.party_name,
rel.party_number, rel.party_name
FROM hz_relationships r, hz_parties s, hz_parties o, hz_parties rel
WHERE (r.subject_id = p_party_id OR r.object_id = p_party_id)
AND r.party_id IS NOT NULL
AND r.subject_table_name = 'HZ_PARTIES'
AND r.object_table_name = 'HZ_PARTIES'
AND r.directional_flag = 'F'
AND r.subject_id = s.party_id
AND r.object_id = o.party_id
AND r.party_id = rel.party_id;
hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,'do_update_party_rel_name (+)');
hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,p_message=>'do_update_party_rel_name (+)',
p_msg_level=>fnd_log.level_procedure);
SELECT 'Y'
INTO l_dummy
FROM hz_parties
WHERE PARTY_ID = i_party_id(i)
FOR UPDATE NOWAIT;
UPDATE hz_parties
SET party_name = l_party_name
WHERE party_id = i_party_id(i);
do_update_party_rel_name(i_party_id(i), l_party_name);
hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,p_message=>'do_update_party_rel_name (-)',
p_msg_level=>fnd_log.level_procedure);
END do_update_party_rel_name;
SELECT 'Y'
FROM hz_organization_profiles
WHERE party_id = p_party_id
AND actual_content_source = p_data_source_type
AND effective_end_date IS NULL;
SELECT 'Y'
FROM hz_person_profiles
WHERE party_id = p_party_id
AND actual_content_source = p_data_source_type
AND effective_end_date IS NULL;
UPDATE hz_person_profiles
SET actual_content_source = G_MISS_CONTENT_SOURCE_TYPE
WHERE party_id = p_party_id
AND actual_content_source = G_SST_SOURCE_TYPE
AND effective_end_date IS NULL
AND NOT EXISTS (
SELECT 'Y'
FROM hz_person_profiles
WHERE party_id = p_party_id
AND actual_content_source = G_MISS_CONTENT_SOURCE_TYPE
AND effective_end_date IS NULL );
UPDATE hz_organization_profiles
SET actual_content_source = G_MISS_CONTENT_SOURCE_TYPE
WHERE party_id = p_party_id
AND actual_content_source = G_SST_SOURCE_TYPE
AND effective_end_date IS NULL
AND NOT EXISTS (
SELECT 'Y'
FROM hz_organization_profiles
WHERE party_id = p_party_id
AND actual_content_source = G_MISS_CONTENT_SOURCE_TYPE
AND effective_end_date IS NULL );
* p_create_update_flag
* p_organization_rec
* p_old_organization_rec
* p_data_source_type
* OUT:
* IN/ OUT:
* x_return_status
*
* NOTES
*
* MODIFICATION HISTORY
*/
PROCEDURE do_process_classification(
p_create_update_flag IN VARCHAR2,
p_party_type IN VARCHAR2,
p_organization_rec IN ORGANIZATION_REC_TYPE := G_MISS_ORGANIZATION_REC,
p_old_organization_rec IN ORGANIZATION_REC_TYPE := G_MISS_ORGANIZATION_REC,
p_person_rec IN PERSON_REC_TYPE := G_MISS_PERSON_REC,
p_old_person_rec IN PERSON_REC_TYPE := G_MISS_PERSON_REC,
p_group_rec IN GROUP_REC_TYPE := G_MISS_GROUP_REC,
p_old_group_rec IN GROUP_REC_TYPE := G_MISS_GROUP_REC,
p_data_source_type IN VARCHAR2,
x_return_status IN OUT NOCOPY VARCHAR2
) IS
l_code_assignment_id NUMBER;
IF (p_create_update_flag = 'C' AND
l_party_rec.category_code IS NOT NULL AND
l_party_rec.category_code <> FND_API.G_MISS_CHAR) OR
(p_create_update_flag = 'U'AND
l_party_rec.category_code IS NOT NULL AND
-- Bug 3876180.
-- l_party_rec.category_code <> FND_API.G_MISS_CHAR AND
l_party_rec.category_code <> l_old_party_rec.category_code) AND
l_data_source_type = G_MISS_CONTENT_SOURCE_TYPE
THEN
hz_classification_v2pub.set_primary_code_assignment(
p_owner_table_name => 'HZ_PARTIES',
p_owner_table_id => l_party_rec.party_id,
p_class_category => 'CUSTOMER_CATEGORY',
p_class_code => l_party_rec.category_code,
p_content_source_type => l_data_source_type,
-- Bug 3856348
-- p_created_by_module => nvl(p_organization_rec.created_by_module,
-- p_old_organization_rec.created_by_module),
--4232060
p_created_by_module => l_created_by_module,
x_code_assignment_id => l_code_assignment_id,
x_return_status => x_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data );
IF p_create_update_flag = 'C' THEN
l_sic_code := NVL(p_organization_rec.sic_code, FND_API.G_MISS_CHAR);
(p_create_update_flag = 'C' OR
(p_create_update_flag = 'U' AND
NVL(p_organization_rec.sic_code, FND_API.G_MISS_CHAR) <>
p_old_organization_rec.sic_code
OR -- Bug 4043346
NVL(p_organization_rec.sic_code_type, FND_API.G_MISS_CHAR) <>
p_old_organization_rec.sic_code_type))
THEN
hz_classification_v2pub.set_primary_code_assignment(
p_owner_table_name => 'HZ_PARTIES',
p_owner_table_id => p_organization_rec.party_rec.party_id,
p_class_category => l_sic_code_type,
p_class_code => l_sic_code,
p_content_source_type => l_data_source_type,
-- Bug 3856348
-- p_created_by_module => nvl(p_organization_rec.created_by_module,
-- p_old_organization_rec.created_by_module),
--bug 4232060
p_created_by_module => l_created_by_module,
x_code_assignment_id => l_code_assignment_id,
x_return_status => x_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data );
IF (p_create_update_flag = 'C' AND
p_organization_rec.local_activity_code IS NOT NULL AND
p_organization_rec.local_activity_code <> FND_API.G_MISS_CHAR) OR
(p_create_update_flag = 'U'AND
p_organization_rec.local_activity_code IS NOT NULL AND
p_organization_rec.local_activity_code <> FND_API.G_MISS_CHAR AND
p_organization_rec.local_activity_code <> p_old_organization_rec.local_activity_code)
THEN
-- Bug 3040565 : Modified the parameter p_class_category to set_primary_code_assignment to pass
-- actual local_activity_code_type.
l_local_activity_code_type := nvl(p_organization_rec.local_activity_code_type, p_old_organization_rec.local_activity_code_type);
* hz_person_profiles_pkg.Insert_Row
*
* ARGUMENTS
* IN:
* p_party_id
* OUT:
* x_profile_id
* IN/ OUT:
* p_person_rec
*
* NOTES
*
* MODIFICATION HISTORY
*
* 06-MAY-2003 Sisir o Bug 2970763: Modified for profile
* versioning project;added version_number
'hz_person_profiles_pkg.Insert_Row (+)', l_debug_prefix);
hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,p_message=>'hz_person_profiles_pkg.Insert_Row (+)',
p_msg_level=>fnd_log.level_procedure);
HZ_person_profiles_pkg.Insert_Row (
x_rowid => l_rowid,
x_person_profile_id => l_person_profile_id,
x_party_id => p_party_id,
x_person_name => do_create_person_name(p_person_rec),
x_attribute_category => p_person_rec.attribute_category,
x_attribute1 => p_person_rec.attribute1,
x_attribute2 => p_person_rec.attribute2,
x_attribute3 => p_person_rec.attribute3,
x_attribute4 => p_person_rec.attribute4,
x_attribute5 => p_person_rec.attribute5,
x_attribute6 => p_person_rec.attribute6,
x_attribute7 => p_person_rec.attribute7,
x_attribute8 => p_person_rec.attribute8,
x_attribute9 => p_person_rec.attribute9,
x_attribute10 => p_person_rec.attribute10,
x_attribute11 => p_person_rec.attribute11,
x_attribute12 => p_person_rec.attribute12,
x_attribute13 => p_person_rec.attribute13,
x_attribute14 => p_person_rec.attribute14,
x_attribute15 => p_person_rec.attribute15,
x_attribute16 => p_person_rec.attribute16,
x_attribute17 => p_person_rec.attribute17,
x_attribute18 => p_person_rec.attribute18,
x_attribute19 => p_person_rec.attribute19,
x_attribute20 => p_person_rec.attribute20,
x_internal_flag => p_person_rec.internal_flag,
x_person_pre_name_adjunct => p_person_rec.person_pre_name_adjunct,
x_person_first_name => p_person_rec.person_first_name,
x_person_middle_name => p_person_rec.person_middle_name,
x_person_last_name => p_person_rec.person_last_name,
x_person_name_suffix => p_person_rec.person_name_suffix,
x_person_title => p_person_rec.person_title,
x_person_academic_title => p_person_rec.person_academic_title,
x_person_previous_last_name => p_person_rec.person_previous_last_name,
x_person_initials => p_person_rec.person_initials,
x_known_as => p_person_rec.known_as,
x_person_name_phonetic => p_person_rec.person_name_phonetic,
x_person_first_name_phonetic => p_person_rec.person_first_name_phonetic,
x_person_last_name_phonetic => p_person_rec.person_last_name_phonetic,
x_tax_reference => p_person_rec.tax_reference,
x_jgzz_fiscal_code => p_person_rec.jgzz_fiscal_code,
x_person_iden_type => p_person_rec.person_iden_type,
x_person_identifier => p_person_rec.person_identifier,
x_date_of_birth => p_person_rec.date_of_birth,
x_place_of_birth => p_person_rec.place_of_birth,
x_date_of_death => p_person_rec.date_of_death,
x_deceased_flag => p_person_rec.deceased_flag,
x_gender => p_person_rec.gender,
x_declared_ethnicity => p_person_rec.declared_ethnicity,
x_marital_status => p_person_rec.marital_status,
x_marital_status_eff_date => p_person_rec.marital_status_effective_date,
x_personal_income => p_person_rec.personal_income,
x_head_of_household_flag => p_person_rec.head_of_household_flag,
x_household_income => p_person_rec.household_income,
x_household_size => p_person_rec.household_size,
x_rent_own_ind => p_person_rec.rent_own_ind,
x_last_known_gps => p_person_rec.last_known_gps,
x_effective_start_date => trunc(hz_utility_pub.creation_date),
x_effective_end_date => null,
x_content_source_type => p_person_rec.content_source_type,
x_known_as2 => p_person_rec.known_as2,
x_known_as3 => p_person_rec.known_as3,
x_known_as4 => p_person_rec.known_as4,
x_known_as5 => p_person_rec.known_as5,
x_middle_name_phonetic => p_person_rec.middle_name_phonetic,
x_object_version_number => 1,
x_created_by_module => p_person_rec.created_by_module,
x_application_id => p_person_rec.application_id,
x_actual_content_source => p_person_rec.actual_content_source,
x_version_number => p_version_number
);
'hz_person_profiles_pkg.Insert_Row (-) ' ||
'x_profile_id = ' || x_profile_id, l_debug_prefix);
hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,p_message=>'hz_person_profiles_pkg.Insert_Row (-) '||'x_profile_id = ' || x_profile_id,
p_msg_level=>fnd_log.level_procedure);
* PRIVATE PROCEDURE do_update_person_profile
*
* DESCRIPTION
* Updates person profile.
*
* EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
* hz_person_profiles_pkg.Update_Row
*
* ARGUMENTS
* IN:
* p_data_source_type
* OUT:
* x_profile_id
* IN/ OUT:
* p_person_rec
* p_old_person_rec
*
* NOTES
*
* MODIFICATION HISTORY
*
*/
PROCEDURE do_update_person_profile(
p_person_rec IN PERSON_REC_TYPE,
p_old_person_rec IN PERSON_REC_TYPE,
p_data_source_type IN VARCHAR2,
x_profile_id OUT NOCOPY NUMBER
) IS
l_rowid ROWID := NULL;
SELECT person_profile_id, rowid,object_version_number,
version_number, effective_start_date
FROM hz_person_profiles
WHERE party_id = p_person_rec.party_rec.party_id
AND actual_content_source = p_data_source_type
AND effective_end_date is null
FOR UPDATE NOWAIT;
l_create_update_flag VARCHAR2(1);
hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,'do_update_person_profile (+)');
hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,p_message=>'do_update_person_profile (+)',
p_msg_level=>fnd_log.level_procedure);
l_create_update_flag := 'C';
l_create_update_flag := 'U';
l_create_update_flag := 'C';
l_create_update_flag := 'U';
IF l_create_update_flag = 'C' THEN
-- Always End date the existing profile and create a new profile
l_version_number := nvl(l_version_number,1)+1;
UPDATE hz_person_profiles
SET effective_end_date = decode(trunc(effective_start_date),trunc(sysdate),trunc(sysdate),TRUNC (SYSDATE-1)),
object_version_number = NVL(l_object_version_number, 1) + 1
--,version_number = NVL(version_number,1)+1
WHERE rowid = l_rowid;
UPDATE hz_person_profiles
SET effective_end_date = TRUNC(SYSDATE-1),
object_version_number = NVL(object_version_number, 1) + 1
WHERE rowid = l_rowid;
'hz_person_profiles_pkg.Update_Row (+) ',l_debug_prefix);
hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,p_message=>'hz_person_profiles_pkg.Update_Row (+)',
p_msg_level=>fnd_log.level_procedure);
HZ_person_profiles_pkg.Update_Row (
x_rowid => l_rowid,
x_person_profile_id => x_profile_id,
x_party_id => null,
x_person_name => l_person_name,
x_attribute_category => p_person_rec.attribute_category,
x_attribute1 => p_person_rec.attribute1,
x_attribute2 => p_person_rec.attribute2,
x_attribute3 => p_person_rec.attribute3,
x_attribute4 => p_person_rec.attribute4,
x_attribute5 => p_person_rec.attribute5,
x_attribute6 => p_person_rec.attribute6,
x_attribute7 => p_person_rec.attribute7,
x_attribute8 => p_person_rec.attribute8,
x_attribute9 => p_person_rec.attribute9,
x_attribute10 => p_person_rec.attribute10,
x_attribute11 => p_person_rec.attribute11,
x_attribute12 => p_person_rec.attribute12,
x_attribute13 => p_person_rec.attribute13,
x_attribute14 => p_person_rec.attribute14,
x_attribute15 => p_person_rec.attribute15,
x_attribute16 => p_person_rec.attribute16,
x_attribute17 => p_person_rec.attribute17,
x_attribute18 => p_person_rec.attribute18,
x_attribute19 => p_person_rec.attribute19,
x_attribute20 => p_person_rec.attribute20,
x_internal_flag => p_person_rec.internal_flag,
x_person_pre_name_adjunct => p_person_rec.person_pre_name_adjunct,
x_person_first_name => p_person_rec.person_first_name,
x_person_middle_name => p_person_rec.person_middle_name,
x_person_last_name => p_person_rec.person_last_name,
x_person_name_suffix => p_person_rec.person_name_suffix,
x_person_title => p_person_rec.person_title,
x_person_academic_title => p_person_rec.person_academic_title,
x_person_previous_last_name => p_person_rec.person_previous_last_name,
x_person_initials => p_person_rec.person_initials,
x_known_as => p_person_rec.known_as,
x_person_name_phonetic => p_person_rec.person_name_phonetic,
x_person_first_name_phonetic => p_person_rec.person_first_name_phonetic,
x_person_last_name_phonetic => p_person_rec.person_last_name_phonetic,
x_tax_reference => p_person_rec.tax_reference,
x_jgzz_fiscal_code => p_person_rec.jgzz_fiscal_code,
x_person_iden_type => p_person_rec.person_iden_type,
x_person_identifier => p_person_rec.person_identifier,
x_date_of_birth => p_person_rec.date_of_birth,
x_place_of_birth => p_person_rec.place_of_birth,
x_date_of_death => p_person_rec.date_of_death,
x_deceased_flag => p_person_rec.deceased_flag,
x_gender => p_person_rec.gender,
x_declared_ethnicity => p_person_rec.declared_ethnicity,
x_marital_status => p_person_rec.marital_status,
x_marital_status_eff_date => p_person_rec.marital_status_effective_date,
x_personal_income => p_person_rec.personal_income,
x_head_of_household_flag => p_person_rec.head_of_household_flag,
x_household_income => p_person_rec.household_income,
x_household_size => p_person_rec.household_size,
x_rent_own_ind => p_person_rec.rent_own_ind,
x_last_known_gps => p_person_rec.last_known_gps,
x_effective_start_date => null,
x_effective_end_date => null,
x_content_source_type => null, -- the column is non-updateable
x_known_as2 => p_person_rec.known_as2,
x_known_as3 => p_person_rec.known_as3,
x_known_as4 => p_person_rec.known_as4,
x_known_as5 => p_person_rec.known_as5,
x_middle_name_phonetic => p_person_rec.middle_name_phonetic,
x_object_version_number => l_object_version_number,
x_created_by_module => p_person_rec.created_by_module,
x_application_id => p_person_rec.application_id,
x_actual_content_source => null, -- the column is non-updateable
x_version_number => l_version_number
);
'hz_person_profiles_pkg.Update_Row (-) ',
l_debug_prefix);
hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,p_message=>'hz_person_profiles_pkg.Update_Row (-)',
p_msg_level=>fnd_log.level_procedure);
hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,'do_update_person_profile (-)');
hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,p_message=>'do_update_person_profile (-)',
p_msg_level=>fnd_log.level_procedure);
END do_update_person_profile;
* hz_organization_profiles_pkg.Insert_Row
*
* ARGUMENTS
* IN:
* p_party_id
* OUT:
* x_profile_id
* IN/ OUT:
* p_organization_rec
*
* NOTES
*
* MODIFICATION HISTORY
* 26-NOV-2001 Joe del Callar Bug 2116225: added support
* for banks.
* Bug 2117973: modified to
* conform to PL/SQL coding stds
*/
PROCEDURE do_create_org_profile(
p_organization_rec IN ORGANIZATION_REC_TYPE,
p_party_id IN NUMBER,
x_profile_id OUT NOCOPY NUMBER,
p_version_number IN NUMBER,
x_rowid OUT NOCOPY ROWID
) IS
l_organization_profile_id NUMBER;
hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,'hz_organization_profiles_pkg.Insert_Row (+)',
l_debug_prefix);
hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,p_message=>'hz_organization_profiles_pkg.Insert_Row (+)',
p_msg_level=>fnd_log.level_procedure);
HZ_organization_profiles_pkg.insert_row(
x_rowid => l_rowid,
x_organization_profile_id => l_organization_profile_id,
x_party_id => p_party_id,
x_organization_name => p_organization_rec.organization_name,
x_attribute_category => p_organization_rec.attribute_category,
x_attribute1 => p_organization_rec.attribute1,
x_attribute2 => p_organization_rec.attribute2,
x_attribute3 => p_organization_rec.attribute3,
x_attribute4 => p_organization_rec.attribute4,
x_attribute5 => p_organization_rec.attribute5,
x_attribute6 => p_organization_rec.attribute6,
x_attribute7 => p_organization_rec.attribute7,
x_attribute8 => p_organization_rec.attribute8,
x_attribute9 => p_organization_rec.attribute9,
x_attribute10 => p_organization_rec.attribute10,
x_attribute11 => p_organization_rec.attribute11,
x_attribute12 => p_organization_rec.attribute12,
x_attribute13 => p_organization_rec.attribute13,
x_attribute14 => p_organization_rec.attribute14,
x_attribute15 => p_organization_rec.attribute15,
x_attribute16 => p_organization_rec.attribute16,
x_attribute17 => p_organization_rec.attribute17,
x_attribute18 => p_organization_rec.attribute18,
x_attribute19 => p_organization_rec.attribute19,
x_attribute20 => p_organization_rec.attribute20,
x_enquiry_duns => p_organization_rec.enquiry_duns,
x_ceo_name => p_organization_rec.ceo_name,
x_ceo_title => p_organization_rec.ceo_title,
x_principal_name => p_organization_rec.principal_name,
x_principal_title => p_organization_rec.principal_title,
x_legal_status => p_organization_rec.legal_status,
x_control_yr => p_organization_rec.control_yr,
x_employees_total => p_organization_rec.employees_total,
x_hq_branch_ind => p_organization_rec.hq_branch_ind,
x_branch_flag => p_organization_rec.branch_flag,
x_oob_ind => p_organization_rec.oob_ind,
x_line_of_business => p_organization_rec.line_of_business,
x_cong_dist_code => p_organization_rec.cong_dist_code,
x_sic_code => p_organization_rec.sic_code,
x_import_ind => p_organization_rec.import_ind,
x_export_ind => p_organization_rec.export_ind,
x_labor_surplus_ind => p_organization_rec.labor_surplus_ind,
x_debarment_ind => p_organization_rec.debarment_ind,
x_minority_owned_ind => p_organization_rec.minority_owned_ind,
x_minority_owned_type => p_organization_rec.minority_owned_type,
x_woman_owned_ind => p_organization_rec.woman_owned_ind,
x_disadv_8a_ind => p_organization_rec.disadv_8a_ind,
x_small_bus_ind => p_organization_rec.small_bus_ind,
x_rent_own_ind => p_organization_rec.rent_own_ind,
x_debarments_count => p_organization_rec.debarments_count,
x_debarments_date => p_organization_rec.debarments_date,
x_failure_score => p_organization_rec.failure_score,
x_failure_score_override_code => p_organization_rec.failure_score_override_code,
x_failure_score_commentary => p_organization_rec.failure_score_commentary,
x_global_failure_score => p_organization_rec.global_failure_score,
x_db_rating => p_organization_rec.db_rating,
x_credit_score => p_organization_rec.credit_score,
x_credit_score_commentary => p_organization_rec.credit_score_commentary,
x_paydex_score => p_organization_rec.paydex_score,
x_paydex_three_months_ago => p_organization_rec.paydex_three_months_ago,
x_paydex_norm => p_organization_rec.paydex_norm,
x_best_time_contact_begin => p_organization_rec.best_time_contact_begin,
x_best_time_contact_end => p_organization_rec.best_time_contact_end,
x_organization_name_phonetic => p_organization_rec.organization_name_phonetic,
x_tax_reference => p_organization_rec.tax_reference,
x_gsa_indicator_flag => p_organization_rec.gsa_indicator_flag,
x_jgzz_fiscal_code => p_organization_rec.jgzz_fiscal_code,
x_analysis_fy => p_organization_rec.analysis_fy,
x_fiscal_yearend_month => p_organization_rec.fiscal_yearend_month,
x_curr_fy_potential_revenue => p_organization_rec.curr_fy_potential_revenue,
x_next_fy_potential_revenue => p_organization_rec.next_fy_potential_revenue,
x_year_established => p_organization_rec.year_established,
x_mission_statement => p_organization_rec.mission_statement,
x_organization_type => p_organization_rec.organization_type,
x_business_scope => p_organization_rec.business_scope,
x_corporation_class => p_organization_rec.corporation_class,
x_known_as => p_organization_rec.known_as,
x_local_bus_iden_type => p_organization_rec.local_bus_iden_type,
x_local_bus_identifier => p_organization_rec.local_bus_identifier,
x_pref_functional_currency => p_organization_rec.pref_functional_currency,
x_registration_type => p_organization_rec.registration_type,
x_total_employees_text => p_organization_rec.total_employees_text,
x_total_employees_ind => p_organization_rec.total_employees_ind,
x_total_emp_est_ind => p_organization_rec.total_emp_est_ind,
x_total_emp_min_ind => p_organization_rec.total_emp_min_ind,
x_parent_sub_ind => p_organization_rec.parent_sub_ind,
x_incorp_year => p_organization_rec.incorp_year,
x_content_source_type => p_organization_rec.content_source_type,
x_content_source_number => p_organization_rec.content_source_number,
x_effective_start_date => TRUNC(hz_utility_pub.creation_date),
x_effective_end_date => NULL,
x_sic_code_type => p_organization_rec.sic_code_type,
x_public_private_ownership => p_organization_rec.public_private_ownership_flag,
x_local_activity_code_type => p_organization_rec.local_activity_code_type,
x_local_activity_code => p_organization_rec.local_activity_code,
x_emp_at_primary_adr => p_organization_rec.emp_at_primary_adr,
x_emp_at_primary_adr_text => p_organization_rec.emp_at_primary_adr_text,
x_emp_at_primary_adr_est_ind => p_organization_rec.emp_at_primary_adr_est_ind,
x_emp_at_primary_adr_min_ind => p_organization_rec.emp_at_primary_adr_min_ind,
x_internal_flag => p_organization_rec.internal_flag,
x_high_credit => p_organization_rec.high_credit,
x_avg_high_credit => p_organization_rec.avg_high_credit,
x_total_payments => p_organization_rec.total_payments,
x_known_as2 => p_organization_rec.known_as2,
x_known_as3 => p_organization_rec.known_as3,
x_known_as4 => p_organization_rec.known_as4,
x_known_as5 => p_organization_rec.known_as5,
x_credit_score_class => p_organization_rec.credit_score_class,
x_credit_score_natl_percentile => p_organization_rec.credit_score_natl_percentile,
x_credit_score_incd_default => p_organization_rec.credit_score_incd_default,
x_credit_score_age => p_organization_rec.credit_score_age,
x_credit_score_date => p_organization_rec.credit_score_date,
x_failure_score_class => p_organization_rec.failure_score_class,
x_failure_score_incd_default => p_organization_rec.failure_score_incd_default,
x_failure_score_age => p_organization_rec.failure_score_age,
x_failure_score_date => p_organization_rec.failure_score_date,
x_failure_score_commentary2 => p_organization_rec.failure_score_commentary2,
x_failure_score_commentary3 => p_organization_rec.failure_score_commentary3,
x_failure_score_commentary4 => p_organization_rec.failure_score_commentary4,
x_failure_score_commentary5 => p_organization_rec.failure_score_commentary5,
x_failure_score_commentary6 => p_organization_rec.failure_score_commentary6,
x_failure_score_commentary7 => p_organization_rec.failure_score_commentary7,
x_failure_score_commentary8 => p_organization_rec.failure_score_commentary8,
x_failure_score_commentary9 => p_organization_rec.failure_score_commentary9,
x_failure_score_commentary10 => p_organization_rec.failure_score_commentary10,
x_credit_score_commentary2 => p_organization_rec.credit_score_commentary2,
x_credit_score_commentary3 => p_organization_rec.credit_score_commentary3,
x_credit_score_commentary4 => p_organization_rec.credit_score_commentary4,
x_credit_score_commentary5 => p_organization_rec.credit_score_commentary5,
x_credit_score_commentary6 => p_organization_rec.credit_score_commentary6,
x_credit_score_commentary7 => p_organization_rec.credit_score_commentary7,
x_credit_score_commentary8 => p_organization_rec.credit_score_commentary8,
x_credit_score_commentary9 => p_organization_rec.credit_score_commentary9,
x_credit_score_commentary10 => p_organization_rec.credit_score_commentary10,
x_maximum_credit_recomm => p_organization_rec.maximum_credit_recommendation,
x_maximum_credit_currency_code => p_organization_rec.maximum_credit_currency_code,
x_displayed_duns_party_id => p_organization_rec.displayed_duns_party_id,
x_failure_score_natnl_perc => p_organization_rec.failure_score_natnl_percentile,
x_duns_number_c => p_organization_rec.duns_number_c,
x_bank_or_branch_number => NULL,
x_bank_code => NULL,
x_branch_code => NULL,
x_object_version_number => 1,
x_created_by_module => p_organization_rec.created_by_module,
x_application_id => p_organization_rec.application_id,
x_do_not_confuse_with => p_organization_rec.do_not_confuse_with,
x_actual_content_source => p_organization_rec.actual_content_source,
x_version_number => p_version_number,
x_home_country => p_organization_rec.home_country
);
hz_utility_v2pub.debug ('hz_organization_profiles_pkg.Insert_Row (-) ',
l_debug_prefix);
hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,p_message=>'hz_organization_profiles_pkg.Insert_Row (-) ',
p_msg_level=>fnd_log.level_procedure);
* PRIVATE PROCEDURE do_update_org_profile
*
* DESCRIPTION
* Updates organization profile.
*
* EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
* hz_organization_profiles_pkg.Update_Row
*
* ARGUMENTS
* IN:
* p_party_id
* p_data_source_type
* OUT:
* x_profile_id
* IN/ OUT:
* p_organization_rec
* p_old_organization_rec
*
* NOTES
*
* MODIFICATION HISTORY
* 26-NOV-2001 Joe del Callar Bug 2116225: modified for
* consolidated bank support.
* Bug 2117973: modified to conform
* to PL/SQL coding standards.
* Changed selects into cursors.
*/
PROCEDURE do_update_org_profile(
p_organization_rec IN ORGANIZATION_REC_TYPE,
p_old_organization_rec IN ORGANIZATION_REC_TYPE,
p_data_source_type IN VARCHAR2,
x_profile_id OUT NOCOPY NUMBER
) IS
l_rowid ROWID := NULL;
SELECT rowid, organization_profile_id,object_version_number,
version_number,effective_start_date
FROM hz_organization_profiles
WHERE party_id = p_organization_rec.party_rec.party_id
AND actual_content_source = p_data_source_type
AND effective_end_date is null
FOR UPDATE NOWAIT;
l_create_update_flag VARCHAR2(1);
hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,'do_update_org_profile (+)');
hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,p_message=>'do_update_org_profile (+)',
p_msg_level=>fnd_log.level_procedure);
l_create_update_flag := 'C';
l_create_update_flag := 'U';
l_create_update_flag := 'C';
l_create_update_flag := 'U';
IF l_create_update_flag = 'C' THEN
-- Always End date the existing profile and create a new profile
--l_object_version_number := NVL(l_object_version_number, 1) + 1;
UPDATE hz_organization_profiles
SET effective_end_date = decode(trunc(effective_start_date),trunc(sysdate),trunc(sysdate),TRUNC (SYSDATE-1)),
object_version_number = NVL(l_object_version_number, 1) + 1
--,version_number = NVL(version_number,1)+1
WHERE rowid = l_rowid;
SELECT business_report INTO l_business_report
FROM hz_organization_profiles
WHERE rowid = l_rowid;
UPDATE hz_organization_profiles
SET business_report = l_business_report
WHERE organization_profile_id = x_profile_id;
UPDATE hz_organization_profiles
SET effective_end_date = TRUNC(SYSDATE-1),
object_version_number = NVL(object_version_number, 1) + 1
WHERE rowid = l_rowid;
hz_utility_v2pub.debug ('hz_organization_profiles_pkg.Update_Row (+) ',
l_debug_prefix);
hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,p_message=>'hz_organization_profiles_pkg.Update_Row (+) ',
p_msg_level=>fnd_log.level_procedure);
HZ_organization_profiles_pkg.update_row (
x_rowid => l_rowid,
x_organization_profile_id => x_profile_id,
x_party_id => NULL,
x_organization_name => p_organization_rec.organization_name,
x_attribute_category => p_organization_rec.attribute_category,
x_attribute1 => p_organization_rec.attribute1,
x_attribute2 => p_organization_rec.attribute2,
x_attribute3 => p_organization_rec.attribute3,
x_attribute4 => p_organization_rec.attribute4,
x_attribute5 => p_organization_rec.attribute5,
x_attribute6 => p_organization_rec.attribute6,
x_attribute7 => p_organization_rec.attribute7,
x_attribute8 => p_organization_rec.attribute8,
x_attribute9 => p_organization_rec.attribute9,
x_attribute10 => p_organization_rec.attribute10,
x_attribute11 => p_organization_rec.attribute11,
x_attribute12 => p_organization_rec.attribute12,
x_attribute13 => p_organization_rec.attribute13,
x_attribute14 => p_organization_rec.attribute14,
x_attribute15 => p_organization_rec.attribute15,
x_attribute16 => p_organization_rec.attribute16,
x_attribute17 => p_organization_rec.attribute17,
x_attribute18 => p_organization_rec.attribute18,
x_attribute19 => p_organization_rec.attribute19,
x_attribute20 => p_organization_rec.attribute20,
x_enquiry_duns => p_organization_rec.enquiry_duns,
x_ceo_name => p_organization_rec.ceo_name,
x_ceo_title => p_organization_rec.ceo_title,
x_principal_name => p_organization_rec.principal_name,
x_principal_title => p_organization_rec.principal_title,
x_legal_status => p_organization_rec.legal_status,
x_control_yr => p_organization_rec.control_yr,
x_employees_total => p_organization_rec.employees_total,
x_hq_branch_ind => p_organization_rec.hq_branch_ind,
x_branch_flag => p_organization_rec.branch_flag,
x_oob_ind => p_organization_rec.oob_ind,
x_line_of_business => p_organization_rec.line_of_business,
x_cong_dist_code => p_organization_rec.cong_dist_code,
x_sic_code => p_organization_rec.sic_code,
x_import_ind => p_organization_rec.import_ind,
x_export_ind => p_organization_rec.export_ind,
x_labor_surplus_ind => p_organization_rec.labor_surplus_ind,
x_debarment_ind => p_organization_rec.debarment_ind,
x_minority_owned_ind => p_organization_rec.minority_owned_ind,
x_minority_owned_type => p_organization_rec.minority_owned_type,
x_woman_owned_ind => p_organization_rec.woman_owned_ind,
x_disadv_8a_ind => p_organization_rec.disadv_8a_ind,
x_small_bus_ind => p_organization_rec.small_bus_ind,
x_rent_own_ind => p_organization_rec.rent_own_ind,
x_debarments_count => p_organization_rec.debarments_count,
x_debarments_date => p_organization_rec.debarments_date,
x_failure_score => p_organization_rec.failure_score,
x_failure_score_override_code => p_organization_rec.failure_score_override_code,
x_failure_score_commentary => p_organization_rec.failure_score_commentary,
x_global_failure_score => p_organization_rec.global_failure_score,
x_db_rating => p_organization_rec.db_rating,
x_credit_score => p_organization_rec.credit_score,
x_credit_score_commentary => p_organization_rec.credit_score_commentary,
x_paydex_score => p_organization_rec.paydex_score,
x_paydex_three_months_ago => p_organization_rec.paydex_three_months_ago,
x_paydex_norm => p_organization_rec.paydex_norm,
x_best_time_contact_begin => p_organization_rec.best_time_contact_begin,
x_best_time_contact_end => p_organization_rec.best_time_contact_end,
x_organization_name_phonetic => p_organization_rec.organization_name_phonetic,
x_tax_reference => p_organization_rec.tax_reference,
x_gsa_indicator_flag => p_organization_rec.gsa_indicator_flag,
x_jgzz_fiscal_code => p_organization_rec.jgzz_fiscal_code,
x_analysis_fy => p_organization_rec.analysis_fy,
x_fiscal_yearend_month => p_organization_rec.fiscal_yearend_month,
x_curr_fy_potential_revenue => p_organization_rec.curr_fy_potential_revenue,
x_next_fy_potential_revenue => p_organization_rec.next_fy_potential_revenue,
x_year_established => p_organization_rec.year_established,
x_mission_statement => p_organization_rec.mission_statement,
x_organization_type => p_organization_rec.organization_type,
x_business_scope => p_organization_rec.business_scope,
x_corporation_class => p_organization_rec.corporation_class,
x_known_as => p_organization_rec.known_as,
x_local_bus_iden_type => p_organization_rec.local_bus_iden_type,
x_local_bus_identifier => p_organization_rec.local_bus_identifier,
x_pref_functional_currency => p_organization_rec.pref_functional_currency,
x_registration_type => p_organization_rec.registration_type,
x_total_employees_text => p_organization_rec.total_employees_text,
x_total_employees_ind => p_organization_rec.total_employees_ind,
x_total_emp_est_ind => p_organization_rec.total_emp_est_ind,
x_total_emp_min_ind => p_organization_rec.total_emp_min_ind,
x_parent_sub_ind => p_organization_rec.parent_sub_ind,
x_incorp_year => p_organization_rec.incorp_year,
x_content_source_type => null, -- the column is non-updateable
x_content_source_number => p_organization_rec.content_source_number,
x_effective_start_date => NULL,
x_effective_end_date => NULL,
x_sic_code_type => p_organization_rec.sic_code_type,
x_public_private_ownership => p_organization_rec.public_private_ownership_flag,
x_local_activity_code_type => p_organization_rec.local_activity_code_type,
x_local_activity_code => p_organization_rec.local_activity_code,
x_emp_at_primary_adr => p_organization_rec.emp_at_primary_adr,
x_emp_at_primary_adr_text => p_organization_rec.emp_at_primary_adr_text,
x_emp_at_primary_adr_est_ind => p_organization_rec.emp_at_primary_adr_est_ind,
x_emp_at_primary_adr_min_ind => p_organization_rec.emp_at_primary_adr_min_ind,
x_internal_flag => p_organization_rec.internal_flag,
x_high_credit => p_organization_rec.high_credit,
x_avg_high_credit => p_organization_rec.avg_high_credit,
x_total_payments => p_organization_rec.total_payments,
x_known_as2 => p_organization_rec.known_as2,
x_known_as3 => p_organization_rec.known_as3,
x_known_as4 => p_organization_rec.known_as4,
x_known_as5 => p_organization_rec.known_as5,
x_credit_score_class => p_organization_rec.credit_score_class,
x_credit_score_natl_percentile => p_organization_rec.credit_score_natl_percentile,
x_credit_score_incd_default => p_organization_rec.credit_score_incd_default,
x_credit_score_age => p_organization_rec.credit_score_age,
x_credit_score_date => p_organization_rec.credit_score_date,
x_failure_score_class => p_organization_rec.failure_score_class,
x_failure_score_incd_default => p_organization_rec.failure_score_incd_default,
x_failure_score_age => p_organization_rec.failure_score_age,
x_failure_score_date => p_organization_rec.failure_score_date,
x_failure_score_commentary2 => p_organization_rec.failure_score_commentary2,
x_failure_score_commentary3 => p_organization_rec.failure_score_commentary3,
x_failure_score_commentary4 => p_organization_rec.failure_score_commentary4,
x_failure_score_commentary5 => p_organization_rec.failure_score_commentary5,
x_failure_score_commentary6 => p_organization_rec.failure_score_commentary6,
x_failure_score_commentary7 => p_organization_rec.failure_score_commentary7,
x_failure_score_commentary8 => p_organization_rec.failure_score_commentary8,
x_failure_score_commentary9 => p_organization_rec.failure_score_commentary9,
x_failure_score_commentary10 => p_organization_rec.failure_score_commentary10,
x_credit_score_commentary2 => p_organization_rec.credit_score_commentary2,
x_credit_score_commentary3 => p_organization_rec.credit_score_commentary3,
x_credit_score_commentary4 => p_organization_rec.credit_score_commentary4,
x_credit_score_commentary5 => p_organization_rec.credit_score_commentary5,
x_credit_score_commentary6 => p_organization_rec.credit_score_commentary6,
x_credit_score_commentary7 => p_organization_rec.credit_score_commentary7,
x_credit_score_commentary8 => p_organization_rec.credit_score_commentary8,
x_credit_score_commentary9 => p_organization_rec.credit_score_commentary9,
x_credit_score_commentary10 => p_organization_rec.credit_score_commentary10,
x_maximum_credit_recomm => p_organization_rec.maximum_credit_recommendation,
x_maximum_credit_currency_code => p_organization_rec.maximum_credit_currency_code,
x_displayed_duns_party_id => p_organization_rec.displayed_duns_party_id,
x_failure_score_natnl_perc => p_organization_rec.failure_score_natnl_percentile,
x_duns_number_c => p_organization_rec.duns_number_c,
x_object_version_number => l_object_version_number,
x_created_by_module => p_organization_rec.created_by_module,
x_application_id => p_organization_rec.application_id,
x_do_not_confuse_with => p_organization_rec.do_not_confuse_with,
x_actual_content_source => null, -- the column is non-updateable
x_version_number => l_version_number,
x_home_country => p_organization_rec.home_country
);
hz_utility_v2pub.debug ('hz_organization_profiles_pkg.Update_Row (-) ',
l_debug_prefix);
hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,p_message=>'hz_organization_profiles_pkg.Update_Row (-) ',
p_msg_level=>fnd_log.level_procedure);
hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,'do_update_org_profile (-)');
hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,p_message=>'do_update_org_profile (-)',
p_msg_level=>fnd_log.level_procedure);
END do_update_org_profile;
SELECT 'Y'
FROM hz_orig_sys_references
WHERE owner_table_id = p_party_id
AND owner_table_name = 'HZ_PARTIES'
AND orig_system = l_orig_system
AND orig_system_reference = l_orig_system_reference
AND trunc(nvl(end_date_active, sysdate)) >= trunc(sysdate)
AND status = 'A';
* PRIVATE PROCEDURE do_update_party_profile
*
* DESCRIPTION
* Updates party profile.
*
* EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
*
* ARGUMENTS
* IN:
* p_party_type
* p_data_source_type
* p_person_rec
* p_old_person_rec
* p_organization_rec
* p_old_organization_rec
* OUT:
* x_profile_id
* IN/ OUT:
*
* NOTES
*
* MODIFICATION HISTORY
*/
PROCEDURE do_update_party_profile (
p_party_type IN VARCHAR2,
p_person_rec IN PERSON_REC_TYPE,
p_old_person_rec IN PERSON_REC_TYPE := G_MISS_PERSON_REC,
p_organization_rec IN ORGANIZATION_REC_TYPE,
p_old_organization_rec IN ORGANIZATION_REC_TYPE := G_MISS_ORGANIZATION_REC,
p_data_source_type IN VARCHAR2,
x_profile_id OUT NOCOPY NUMBER
) IS
l_debug_prefix VARCHAR2(30) := '';
hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,'do_update_party_profile (+)');
hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,p_message=>'do_update_party_profile (+)',
p_msg_level=>fnd_log.level_procedure);
do_update_person_profile (
p_person_rec => p_person_rec,
p_old_person_rec => p_old_person_rec,
p_data_source_type => p_data_source_type,
x_profile_id => x_profile_id );
do_update_org_profile (
p_organization_rec => p_organization_rec,
p_old_organization_rec => p_old_organization_rec,
p_data_source_type => p_data_source_type,
x_profile_id => x_profile_id );
hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,'do_update_party_profile (-)');
hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,p_message=>'do_update_party_profile (-)',
p_msg_level=>fnd_log.level_procedure);
END do_update_party_profile;
* PRIVATE PROCEDURE do_create_update_party_only
*
* DESCRIPTION
* Creates / Updates only party.
*
* EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
*
* ARGUMENTS
* IN:
* p_create_update_flag
* p_party_type
* p_party_id
* p_check_object_version_number
* OUT:
* IN/ OUT:
* p_party_object_version_number
* p_person_rec
* p_organization_rec
* p_group_rec
* x_party_id
* x_party_number
*
* NOTES
*
* MODIFICATION HISTORY
*/
PROCEDURE do_create_update_party_only(
p_create_update_flag IN VARCHAR2,
p_party_type IN VARCHAR2,
-- p_party_id is used in update mode only.
p_party_id IN NUMBER := NULL,
p_check_object_version_number IN VARCHAR2 := 'Y',
p_party_object_version_number IN OUT NOCOPY NUMBER,
p_person_rec IN PERSON_REC_TYPE := G_MISS_PERSON_REC,
p_old_person_rec IN PERSON_REC_TYPE := G_MISS_PERSON_REC,
p_organization_rec IN ORGANIZATION_REC_TYPE := G_MISS_ORGANIZATION_REC,
p_old_organization_rec IN ORGANIZATION_REC_TYPE := G_MISS_ORGANIZATION_REC,
p_group_rec IN GROUP_REC_TYPE := G_MISS_GROUP_REC,
p_old_group_rec IN GROUP_REC_TYPE := G_MISS_GROUP_REC,
-- x_party_id and x_party_number are used in create mode.
x_party_id OUT NOCOPY NUMBER,
x_party_number OUT NOCOPY VARCHAR2
) IS
l_party_rec PARTY_REC_TYPE;
SELECT party_name, object_version_number, rowid,
created_by_module
FROM hz_parties
WHERE party_id = p_party_id
FOR UPDATE NOWAIT;
SELECT 'Y'
FROM hz_parties
WHERE party_number = p_party_number;
hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,'do_create_update_party_only (+)');
hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,p_message=>'do_create_update_party_only (+)',
p_msg_level=>fnd_log.level_procedure);
/* if p_create_update_flag = 'U' THEN
IF (l_party_rec.orig_system is not null
and l_party_rec.orig_system <>fnd_api.g_miss_char)
and (l_party_rec.orig_system_reference is not null
and l_party_rec.orig_system_reference <>fnd_api.g_miss_char)
and (l_party_rec.party_id = FND_API.G_MISS_NUM or l_party_rec.party_id is null) THEN
hz_orig_system_ref_pub.get_owner_table_id
(p_orig_system => l_party_rec.orig_system,
p_orig_system_reference => l_party_rec.orig_system_reference,
p_owner_table_name => 'HZ_PARTIES',
x_owner_table_id => l_party_rec.party_id,
x_return_status => l_return_status);
IF p_create_update_flag = 'C' THEN
-- if GENERATE_PARTY_NUMBER is 'N', then if party_number is
-- not passed or is a duplicate raise error.
IF NVL(fnd_profile.value('HZ_GENERATE_PARTY_NUMBER'), 'Y') = 'N' THEN
IF l_party_rec.party_number = FND_API.G_MISS_CHAR OR
l_party_rec.party_number IS NULL
THEN
fnd_message.set_name('AR', 'HZ_API_MISSING_COLUMN');
ELSE -- update mode
-- check whether record has been updated by another user.
-- if not, lock it.
OPEN c_party;
IF p_create_update_flag = 'C' THEN
-- Check "backwards compatability" profile option and determine
-- whether to stick with the old-style name formatting, or call
-- the new dynamic name formatting routines.
-- use new routines
IF nvl(fnd_profile.value(g_profile_fmt_bkwd_compatible),'Y') = 'N' THEN
-- use the new name formatting routine
l_party_name := substrb( do_create_person_name(p_person_rec), 1, 360 );
ELSE -- p_create_update_flag = 'U'
-- Bug 3999044
IF p_person_rec.person_title IS NULL AND
p_person_rec.person_first_name IS NULL AND
p_person_rec.person_middle_name IS NULL AND
p_person_rec.person_last_name IS NULL AND
p_person_rec.person_name_suffix IS NULL AND
p_person_rec.known_as IS NULL AND
p_person_rec.person_first_name_phonetic IS NULL AND
p_person_rec.middle_name_phonetic IS NULL AND
p_person_rec.person_last_name_phonetic IS NULL
THEN
l_process_party_name := 'N';
p_create_update_flag, l_party_rec, l_old_party_rec,
NVL(db_created_by_module, fnd_api.g_miss_char), l_return_status);
IF p_create_update_flag = 'C' THEN
-- Debug info.
/*IF g_debug THEN
hz_utility_v2pub.debug (
'hz_parties_pkg.Insert_Row (+)',l_debug_prefix);
hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,p_message=>'hz_parties_pkg.Insert_Row (+)',
p_msg_level=>fnd_log.level_procedure);
hz_parties_pkg.insert_row (
x_party_id => l_party_rec.party_id,
x_party_number => l_party_rec.party_number,
x_party_name => l_party_name,
x_party_type => p_party_type,
x_validated_flag => l_party_rec.validated_flag,
x_attribute_category => l_party_rec.attribute_category,
x_attribute1 => l_party_rec.attribute1,
x_attribute2 => l_party_rec.attribute2,
x_attribute3 => l_party_rec.attribute3,
x_attribute4 => l_party_rec.attribute4,
x_attribute5 => l_party_rec.attribute5,
x_attribute6 => l_party_rec.attribute6,
x_attribute7 => l_party_rec.attribute7,
x_attribute8 => l_party_rec.attribute8,
x_attribute9 => l_party_rec.attribute9,
x_attribute10 => l_party_rec.attribute10,
x_attribute11 => l_party_rec.attribute11,
x_attribute12 => l_party_rec.attribute12,
x_attribute13 => l_party_rec.attribute13,
x_attribute14 => l_party_rec.attribute14,
x_attribute15 => l_party_rec.attribute15,
x_attribute16 => l_party_rec.attribute16,
x_attribute17 => l_party_rec.attribute17,
x_attribute18 => l_party_rec.attribute18,
x_attribute19 => l_party_rec.attribute19,
x_attribute20 => l_party_rec.attribute20,
x_attribute21 => l_party_rec.attribute21,
x_attribute22 => l_party_rec.attribute22,
x_attribute23 => l_party_rec.attribute23,
x_attribute24 => l_party_rec.attribute24,
x_orig_system_reference => l_party_rec.orig_system_reference,
x_sic_code => l_party_dup_rec.sic_code,
x_hq_branch_ind => l_party_dup_rec.hq_branch_ind,
x_customer_key => l_customer_key,
x_tax_reference => l_party_dup_rec.tax_reference,
x_jgzz_fiscal_code => l_party_dup_rec.jgzz_fiscal_code,
x_person_pre_name_adjunct => l_party_dup_rec.pre_name_adjunct,
x_person_first_name => l_party_dup_rec.first_name,
x_person_middle_name => l_party_dup_rec.middle_name,
x_person_last_name => l_party_dup_rec.last_name,
x_person_name_suffix => l_party_dup_rec.name_suffix,
x_person_title => l_party_dup_rec.title,
x_person_academic_title => l_party_dup_rec.academic_title,
x_person_previous_last_name => l_party_dup_rec.previous_last_name,
x_known_as => l_party_dup_rec.known_as,
x_person_iden_type => l_party_dup_rec.person_iden_type,
x_person_identifier => l_party_dup_rec.person_identifier,
x_group_type => p_group_rec.group_type,
x_country => null,
x_address1 => null,
x_address2 => null,
x_address3 => null,
x_address4 => null,
x_city => null,
x_postal_code => null,
x_state => null,
x_province => null,
x_status => l_party_rec.status,
x_county => null,
x_sic_code_type => l_party_dup_rec.sic_code_type,
x_url => null,
x_email_address => null,
x_analysis_fy => l_party_dup_rec.analysis_fy,
x_fiscal_yearend_month => l_party_dup_rec.fiscal_yearend_month,
x_employees_total => l_party_dup_rec.employees_total,
x_curr_fy_potential_revenue => l_party_dup_rec.curr_fy_potential_revenue,
x_next_fy_potential_revenue => l_party_dup_rec.next_fy_potential_revenue,
x_year_established => l_party_dup_rec.year_established,
x_gsa_indicator_flag => l_party_dup_rec.gsa_indicator_flag,
x_mission_statement => l_party_dup_rec.mission_statement,
x_organization_name_phonetic => l_party_dup_rec.organization_name_phonetic,
x_person_first_name_phonetic => l_party_dup_rec.person_first_name_phonetic,
x_person_last_name_phonetic => l_party_dup_rec.person_last_name_phonetic,
x_language_name => null,
x_category_code => l_party_rec.category_code,
x_salutation => l_party_rec.salutation,
x_known_as2 => l_party_dup_rec.known_as2,
x_known_as3 => l_party_dup_rec.known_as3,
x_known_as4 => l_party_dup_rec.known_as4,
x_known_as5 => l_party_dup_rec.known_as5,
x_object_version_number => 1,
x_duns_number_c => l_party_dup_rec.duns_number_c,
x_created_by_module => l_party_dup_rec.created_by_module,
x_application_id => l_party_dup_rec.application_id
);
'hz_parties_pkg.Insert_Row (-)', l_debug_prefix);
hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,p_message=>'hz_parties_pkg.Insert_Row (-)',
p_msg_level=>fnd_log.level_procedure);
ZX_PARTY_TAX_PROFILE_PKG.insert_row (
P_COLLECTING_AUTHORITY_FLAG => null,
P_PROVIDER_TYPE_CODE => null,
P_CREATE_AWT_DISTS_TYPE_CODE => null,
P_CREATE_AWT_INVOICES_TYPE_COD => null,
P_TAX_CLASSIFICATION_CODE => null,
P_SELF_ASSESS_FLAG => null,
P_ALLOW_OFFSET_TAX_FLAG => null,
P_REP_REGISTRATION_NUMBER => l_party_dup_rec.tax_reference,
P_EFFECTIVE_FROM_USE_LE => null,
P_RECORD_TYPE_CODE => null,
P_REQUEST_ID => null,
P_ATTRIBUTE1 => null,
P_ATTRIBUTE2 => null,
P_ATTRIBUTE3 => null,
P_ATTRIBUTE4 => null,
P_ATTRIBUTE5 => null,
P_ATTRIBUTE6 => null,
P_ATTRIBUTE7 => null,
P_ATTRIBUTE8 => null,
P_ATTRIBUTE9 => null,
P_ATTRIBUTE10 => null,
P_ATTRIBUTE11 => null,
P_ATTRIBUTE12 => null,
P_ATTRIBUTE13 => null,
P_ATTRIBUTE14 => null,
P_ATTRIBUTE15 => null,
P_ATTRIBUTE_CATEGORY => null,
P_PARTY_ID => x_party_id,
P_PROGRAM_LOGIN_ID => null,
P_PARTY_TYPE_CODE => 'THIRD_PARTY',
P_SUPPLIER_FLAG => null,
P_CUSTOMER_FLAG => null,
P_SITE_FLAG => null,
P_PROCESS_FOR_APPLICABILITY_FL => null,
P_ROUNDING_LEVEL_CODE => null,
P_ROUNDING_RULE_CODE => null,
P_WITHHOLDING_START_DATE => null,
P_INCLUSIVE_TAX_FLAG => null,
P_ALLOW_AWT_FLAG => null,
P_USE_LE_AS_SUBSCRIBER_FLAG => null,
P_LEGAL_ESTABLISHMENT_FLAG => null,
P_FIRST_PARTY_LE_FLAG => null,
P_REPORTING_AUTHORITY_FLAG => null,
X_RETURN_STATUS => l_return_status,
P_REGISTRATION_TYPE_CODE => null,--4742586
P_COUNTRY_CODE => null--4742586
);
ELSE -- p_create_update_flag = 'U'
-- Debug info.
/*IF g_debug THEN
hz_utility_v2pub.debug (
'hz_parties_pkg.Update_Row (+)',l_debug_prefix);
hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,p_message=>'hz_parties_pkg.Update_Row (+)',
p_msg_level=>fnd_log.level_procedure);
hz_parties_pkg.update_row (
x_rowid => l_rowid,
x_party_id => l_party_rec.party_id,
x_party_number => l_party_rec.party_number,
x_party_name => l_party_name,
x_party_type => p_party_type,
x_validated_flag => l_party_rec.validated_flag,
x_attribute_category => l_party_rec.attribute_category,
x_attribute1 => l_party_rec.attribute1,
x_attribute2 => l_party_rec.attribute2,
x_attribute3 => l_party_rec.attribute3,
x_attribute4 => l_party_rec.attribute4,
x_attribute5 => l_party_rec.attribute5,
x_attribute6 => l_party_rec.attribute6,
x_attribute7 => l_party_rec.attribute7,
x_attribute8 => l_party_rec.attribute8,
x_attribute9 => l_party_rec.attribute9,
x_attribute10 => l_party_rec.attribute10,
x_attribute11 => l_party_rec.attribute11,
x_attribute12 => l_party_rec.attribute12,
x_attribute13 => l_party_rec.attribute13,
x_attribute14 => l_party_rec.attribute14,
x_attribute15 => l_party_rec.attribute15,
x_attribute16 => l_party_rec.attribute16,
x_attribute17 => l_party_rec.attribute17,
x_attribute18 => l_party_rec.attribute18,
x_attribute19 => l_party_rec.attribute19,
x_attribute20 => l_party_rec.attribute20,
x_attribute21 => l_party_rec.attribute21,
x_attribute22 => l_party_rec.attribute22,
x_attribute23 => l_party_rec.attribute23,
x_attribute24 => l_party_rec.attribute24,
x_orig_system_reference => l_party_rec.orig_system_reference,
x_sic_code => l_party_dup_rec.sic_code,
x_hq_branch_ind => l_party_dup_rec.hq_branch_ind,
x_customer_key => l_customer_key,
x_tax_reference => l_party_dup_rec.tax_reference,
x_jgzz_fiscal_code => l_party_dup_rec.jgzz_fiscal_code,
x_person_pre_name_adjunct => l_party_dup_rec.pre_name_adjunct,
x_person_first_name => l_party_dup_rec.first_name,
x_person_middle_name => l_party_dup_rec.middle_name,
x_person_last_name => l_party_dup_rec.last_name,
x_person_name_suffix => l_party_dup_rec.name_suffix,
x_person_title => l_party_dup_rec.title,
x_person_academic_title => l_party_dup_rec.academic_title,
x_person_previous_last_name => l_party_dup_rec.previous_last_name,
x_known_as => l_party_dup_rec.known_as,
x_person_iden_type => l_party_dup_rec.person_iden_type,
x_person_identifier => l_party_dup_rec.person_identifier,
x_group_type => p_group_rec.group_type,
x_country => null,
x_address1 => null,
x_address2 => null,
x_address3 => null,
x_address4 => null,
x_city => null,
x_postal_code => null,
x_state => null,
x_province => null,
x_status => l_party_rec.status,
x_county => null,
x_sic_code_type => l_party_dup_rec.sic_code_type,
x_url => null,
x_email_address => null,
x_analysis_fy => l_party_dup_rec.analysis_fy,
x_fiscal_yearend_month => l_party_dup_rec.fiscal_yearend_month,
x_employees_total => l_party_dup_rec.employees_total,
x_curr_fy_potential_revenue => l_party_dup_rec.curr_fy_potential_revenue,
x_next_fy_potential_revenue => l_party_dup_rec.next_fy_potential_revenue,
x_year_established => l_party_dup_rec.year_established,
x_gsa_indicator_flag => l_party_dup_rec.gsa_indicator_flag,
x_mission_statement => l_party_dup_rec.mission_statement,
x_organization_name_phonetic => l_party_dup_rec.organization_name_phonetic,
x_person_first_name_phonetic => l_party_dup_rec.person_first_name_phonetic,
x_person_last_name_phonetic => l_party_dup_rec.person_last_name_phonetic,
x_language_name => null,
x_category_code => l_party_rec.category_code,
x_salutation => l_party_rec.salutation,
x_known_as2 => l_party_dup_rec.known_as2,
x_known_as3 => l_party_dup_rec.known_as3,
x_known_as4 => l_party_dup_rec.known_as4,
x_known_as5 => l_party_dup_rec.known_as5,
x_object_version_number => p_party_object_version_number,
x_duns_number_c => l_party_dup_rec.duns_number_c,
x_created_by_module => l_party_dup_rec.created_by_module,
x_application_id => l_party_dup_rec.application_id
);
do_update_party_rel_name(l_party_rec.party_id, l_party_name);
'hz_parties_pkg.Update_Row (-)',l_debug_prefix);
hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,p_message=>'hz_parties_pkg.Update_Row (-)',
p_msg_level=>fnd_log.level_procedure);
SELECT PARTY_TAX_PROFILE_ID INTO L_PARTY_TAX_PROFILE_ID
FROM ZX_PARTY_TAX_PROFILE
WHERE PARTY_ID = p_party_id
AND PARTY_TYPE_CODE = 'THIRD_PARTY'
AND ROWNUM = 1;
ZX_PARTY_TAX_PROFILE_PKG.update_row (
P_PARTY_TAX_PROFILE_ID => L_PARTY_TAX_PROFILE_ID,
P_COLLECTING_AUTHORITY_FLAG => null,
P_PROVIDER_TYPE_CODE => null,
P_CREATE_AWT_DISTS_TYPE_CODE => null,
P_CREATE_AWT_INVOICES_TYPE_COD => null,
P_TAX_CLASSIFICATION_CODE => null,
P_SELF_ASSESS_FLAG => null,
P_ALLOW_OFFSET_TAX_FLAG => null,
P_REP_REGISTRATION_NUMBER => l_party_dup_rec.tax_reference,
P_EFFECTIVE_FROM_USE_LE => null,
P_RECORD_TYPE_CODE => null,
P_REQUEST_ID => null,
P_ATTRIBUTE1 => null,
P_ATTRIBUTE2 => null,
P_ATTRIBUTE3 => null,
P_ATTRIBUTE4 => null,
P_ATTRIBUTE5 => null,
P_ATTRIBUTE6 => null,
P_ATTRIBUTE7 => null,
P_ATTRIBUTE8 => null,
P_ATTRIBUTE9 => null,
P_ATTRIBUTE10 => null,
P_ATTRIBUTE11 => null,
P_ATTRIBUTE12 => null,
P_ATTRIBUTE13 => null,
P_ATTRIBUTE14 => null,
P_ATTRIBUTE15 => null,
P_ATTRIBUTE_CATEGORY => null,
P_PARTY_ID => null,
P_PROGRAM_LOGIN_ID => null,
P_PARTY_TYPE_CODE => null,
P_SUPPLIER_FLAG => null,
P_CUSTOMER_FLAG => null,
P_SITE_FLAG => null,
P_PROCESS_FOR_APPLICABILITY_FL => null,
P_ROUNDING_LEVEL_CODE => null,
P_ROUNDING_RULE_CODE => null,
P_WITHHOLDING_START_DATE => null,
P_INCLUSIVE_TAX_FLAG => null,
P_ALLOW_AWT_FLAG => null,
P_USE_LE_AS_SUBSCRIBER_FLAG => null,
P_LEGAL_ESTABLISHMENT_FLAG => null,
P_FIRST_PARTY_LE_FLAG => null,
P_REPORTING_AUTHORITY_FLAG => null,
X_RETURN_STATUS => l_return_status,
P_REGISTRATION_TYPE_CODE => null,--4742586
P_COUNTRY_CODE => null--4742586
);
IF p_create_update_flag = 'U' THEN
DECLARE
l_party_usage_code VARCHAR2(30);
SELECT 'Y' INTO l_temp FROM hz_party_usg_assignments WHERE party_id=p_party_id AND party_usage_code='SUPPLIER'
AND ROWNUM=1;
hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,'do_create_update_party_only (-)');
hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,p_message=>'do_create_update_party_only (-)',
p_msg_level=>fnd_log.level_procedure);
END do_create_update_party_only;
* hz_parties_pkg.Insert_Row
*
* ARGUMENTS
* IN:
* p_party_type
* OUT:
* x_party_id
* x_party_number
* x_profile_id
* IN/ OUT:
* p_person_rec
* p_organization_rec
* p_group_rec
* x_return_status
*
* NOTES
*
* MODIFICATION HISTORY
* 02-21-2002 Chris Saulit o Modify to use new name formatting.
* Base Bug #2221071
* 04-Mar-2003 Porkodi C o Bug 2794173, Default value will be assigned to deceased_flag
* depending on the date_of_death value.
* 26-Sep-2003 Rajib Ranjan Borah o Bug Number 3099624.Sensitive HR data will not
* be inserted into HZ_PERSON_PROFILES table.
* 02-APR-2004 Rajib Ranjan Borah o Bug 3317806. If local_activity_code is invalid with respect
* to the position of the decimal point, replace this with
* the correct value from fnd_lookup_values provided that the
* actual_content_source for this record is not 'USER_ENTERED'.
* 31-DEC-2004 Rajib Ranjan Borah o SSM SST Integration and Extension.
* Call HZ_MIXNM_UTILITY.create_exceptions if actual_content_source
* is some third party content source and no prior profile exists..
*/
PROCEDURE do_create_party(
p_party_type IN VARCHAR2,
p_party_usage_code IN VARCHAR2,
p_person_rec IN OUT NOCOPY PERSON_REC_TYPE,
p_organization_rec IN OUT NOCOPY ORGANIZATION_REC_TYPE,
p_group_rec IN OUT NOCOPY GROUP_REC_TYPE,
x_party_id OUT NOCOPY NUMBER,
x_party_number OUT NOCOPY VARCHAR2,
x_profile_id OUT NOCOPY NUMBER,
x_return_status IN OUT NOCOPY VARCHAR2
) IS
l_sst_person_rec PERSON_REC_TYPE;
l_datasource_selected VARCHAR2(1) := 'N';
l_selected_datasources VARCHAR2(600);
l_create_update_sst_flag VARCHAR2(1) := 'C';
l_party_create_update_flag VARCHAR2(1);
l_selected_datasources := g_per_selected_datasources;
SELECT lookup_code
INTO p_organization_rec.local_activity_code
FROM FND_LOOKUP_VALUES
WHERE lookup_type = 'NACE'
AND replace (lookup_code,'.','') = replace (p_organization_rec.local_activity_code,'.','')
AND rownum = 1;
l_selected_datasources := g_org_selected_datasources;
do_create_update_party_only(
p_create_update_flag => 'C',
p_party_type => p_party_type,
p_group_rec => p_group_rec,
x_party_id => x_party_id,
x_party_number => x_party_number,
p_party_object_version_number => l_party_object_version_number);
hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,'l_selected_datasources = '||l_selected_datasources);
hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,p_message=>'l_selected_datasources = '||l_selected_datasources,
p_msg_level=>fnd_log.level_statement);
l_party_create_update_flag := 'C';
do_create_update_party_only(
p_create_update_flag => 'C',
p_party_type => p_party_type,
p_person_rec => p_person_rec,
p_organization_rec => p_organization_rec,
x_party_id => x_party_id,
x_party_number => x_party_number,
p_party_object_version_number => l_party_object_version_number);
l_datasource_selected :=
HZ_MIXNM_UTILITY.isDataSourceSelected(
-- Bug 4376604 : pass entity type
p_party_type,
l_data_source_type);
hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,'l_datasource_selected = '||l_datasource_selected);
hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,p_message=>'l_datasource_selected = '||l_datasource_selected,
p_msg_level=>fnd_log.level_statement);
l_party_create_update_flag := 'C';
do_create_update_party_only(
p_create_update_flag => 'C',
p_party_type => p_party_type,
p_person_rec => p_person_rec,
p_organization_rec => p_organization_rec,
x_party_id => x_party_id,
x_party_number => x_party_number,
p_party_object_version_number => l_party_object_version_number);
l_datasource_selected = 'Y'*/
THEN
l_actual_content_source := G_MISS_CONTENT_SOURCE_TYPE;
IF l_party_create_update_flag = 'C' THEN
do_create_party_profile (
p_party_type => p_party_type,
p_party_id => x_party_id,
p_person_rec => p_person_rec,
p_organization_rec => p_organization_rec,
p_content_source_type => l_content_source_type,
p_actual_content_source => l_actual_content_source,
x_profile_id => l_profile_id );
l_create_update_sst_flag := 'C';
l_create_update_sst_flag := 'U';
IF l_datasource_selected = 'Y' THEN
-- return SST record which we need to use either
-- generate a SST profile or update an existing SST profile.
HZ_MIXNM_UTILITY.updateSSTProfile(
p_create_update_flag => 'C',
p_create_update_sst_flag => l_create_update_sst_flag,
p_raise_error_flag => 'N',
p_party_type => p_party_type,
p_party_id => x_party_id,
p_new_person_rec => p_person_rec,
p_sst_person_rec => l_sst_person_rec,
p_new_sst_person_rec => l_new_sst_person_rec,
p_new_organization_rec => p_organization_rec,
p_sst_organization_rec => l_sst_organization_rec,
p_new_sst_organization_rec => l_new_sst_organization_rec,
p_data_source_type => l_data_source_type,
x_return_status => x_return_status );
IF l_create_update_sst_flag = 'C' THEN
do_create_party_profile (
p_party_type => p_party_type,
p_party_id => x_party_id,
p_person_rec => l_new_sst_person_rec,
p_organization_rec => l_new_sst_organization_rec,
p_content_source_type => l_content_source_type,
p_actual_content_source => l_actual_content_source,
x_profile_id => l_profile_id );
ELSE -- update SST profile.
do_update_party_profile(
p_party_type => p_party_type,
p_person_rec => l_new_sst_person_rec,
p_old_person_rec => l_sst_person_rec,
p_organization_rec => l_new_sst_organization_rec,
p_old_organization_rec => l_sst_organization_rec,
p_data_source_type => G_SST_SOURCE_TYPE,
x_profile_id => l_profile_id );
l_party_create_update_flag := 'U';
do_create_update_party_only(
p_create_update_flag => 'U',
p_party_type => p_party_type,
p_party_id => x_party_id,
p_person_rec => l_new_sst_person_rec,
p_old_person_rec => l_sst_person_rec,
p_organization_rec => l_new_sst_organization_rec,
p_old_organization_rec => l_sst_organization_rec,
p_check_object_version_number => 'N',
p_party_object_version_number => l_party_object_version_number,
x_party_id => l_party_id,
x_party_number => l_party_number);
p_create_update_flag => 'C',
p_party_type => p_party_type,
p_organization_rec => p_organization_rec,
p_old_organization_rec => G_MISS_ORGANIZATION_REC,
p_person_rec => p_person_rec,
p_old_person_rec => G_MISS_PERSON_REC,
p_group_rec => p_group_rec,
p_old_group_rec => G_MISS_GROUP_REC,
p_data_source_type => l_data_source_type,
x_return_status => x_return_status );
IF l_party_create_update_flag IS NOT NULL THEN
IF p_party_type = 'ORGANIZATION' THEN
-- BES
IF l_party_create_update_flag = 'C' THEN
IF(HZ_UTILITY_V2PUB.G_EXECUTE_API_CALLOUTS in ('Y', 'EVENTS_ENABLED')) THEN
HZ_BUSINESS_EVENT_V2PVT.create_organization_event(p_organization_rec);
HZ_BUSINESS_EVENT_V2PVT.update_organization_event(
l_new_sst_organization_rec, l_sst_organization_rec);
HZ_DQM_SYNC.sync_org(x_party_id, l_party_create_update_flag);
IF l_party_create_update_flag = 'C' THEN
IF(HZ_UTILITY_V2PUB.G_EXECUTE_API_CALLOUTS in ('Y', 'EVENTS_ENABLED')) THEN
HZ_BUSINESS_EVENT_V2PVT.create_person_event(p_person_rec);
HZ_BUSINESS_EVENT_V2PVT.update_person_event(
l_new_sst_person_rec, l_sst_person_rec);
HZ_DQM_SYNC.sync_person(x_party_id, l_party_create_update_flag);
IF l_party_create_update_flag = 'C' THEN
l_party_usg_assignment_rec.party_usage_code :=
NVL(p_party_usage_code, fnd_profile.value('HZ_PARTY_USAGE_DEFAULT'));
* PRIVATE PROCEDURE do_update_party
*
* DESCRIPTION
* Updates party.
*
* EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
* hz_parties_pkg.Update_Row
*
* ARGUMENTS
* IN:
* p_party_type
* OUT:
* x_profile_id
* IN/ OUT:
* p_person_rec
* p_old_person_rec
* p_organization_rec
* p_old_organization_rec
* p_group_rec
* p_party_object_version_number
* x_return_status
*
* NOTES
*
* MODIFICATION HISTORY
* 02-21-2002 Chris Saulit o Modify to use new name formatting.
* Base Bug #2221071
* 04-Mar-2003 Porkodi C o Bug 2794173, Default value will be assigned to deceased_flag
* depending on the date_of_death value.
* 26-Sep-2003 Rajib Ranjan Borah o Bug Number 3099624.Sensitive HR data will not
* be updated into HZ_PERSON_PROFILES table.
* 02-APR-2004 Rajib Ranjan Borah o Bug 3317806. If local_activity_code is invalid with respect
* to the position of the decimal point, replace this with
* the correct value from fnd_lookup_values provided that the
* actual_content_source for this record is not 'USER_ENTERED'.
*/
PROCEDURE do_update_party (
p_party_type IN VARCHAR2,
p_person_rec IN OUT NOCOPY PERSON_REC_TYPE,
p_old_person_rec IN PERSON_REC_TYPE := G_MISS_PERSON_REC,
p_organization_rec IN OUT NOCOPY ORGANIZATION_REC_TYPE,
p_old_organization_rec IN ORGANIZATION_REC_TYPE := G_MISS_ORGANIZATION_REC,
p_group_rec IN OUT NOCOPY GROUP_REC_TYPE,
p_old_group_rec IN GROUP_REC_TYPE := G_MISS_GROUP_REC,
p_party_object_version_number IN OUT NOCOPY NUMBER,
x_profile_id OUT NOCOPY NUMBER,
x_return_status IN OUT NOCOPY VARCHAR2
) IS
l_sst_person_rec PERSON_REC_TYPE;
l_update_sst_profile VARCHAR2(1) := 'N';
l_update_party VARCHAR2(1) := 'N';
l_datasource_selected VARCHAR2(1);
l_selected_datasources VARCHAR2(600);
SELECT party_name,tax_reference FROM HZ_PARTIES
WHERE PARTY_ID=p_party_id;
hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,'do_update_party (+)');
hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,p_message=>'do_update_party (+)',
p_msg_level=>fnd_log.level_procedure);
l_selected_datasources := g_per_selected_datasources;
SELECT lookup_code
INTO p_organization_rec.local_activity_code
FROM FND_LOOKUP_VALUES
WHERE lookup_type = 'NACE'
AND replace (lookup_code,'.','') = replace (p_organization_rec.local_activity_code,'.','')
AND rownum = 1;
l_selected_datasources := g_org_selected_datasources;
do_create_update_party_only(
p_create_update_flag => 'U',
p_party_type => p_party_type,
p_party_id => p_group_rec.party_rec.party_id,
p_party_object_version_number => p_party_object_version_number,
p_group_rec => p_group_rec,
p_old_group_rec => p_old_group_rec,
x_party_id => l_party_id,
x_party_number => l_party_number );
do_update_party_profile (
p_party_type => p_party_type,
p_person_rec => p_person_rec,
p_old_person_rec => p_old_person_rec,
p_organization_rec => p_organization_rec,
p_old_organization_rec => p_old_organization_rec,
p_data_source_type => l_data_source_type,
x_profile_id => x_profile_id );
l_update_party := 'Y';
l_update_sst_profile := 'Y';
l_datasource_selected :=
HZ_MIXNM_UTILITY.isDataSourceSelected(
-- Bug 4376604 : pass entity type
p_party_type,
l_data_source_type);
hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,'l_datasource_selected = '||l_datasource_selected);
hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,p_message=>'l_datasource_selected = '||l_datasource_selected,
p_msg_level=>fnd_log.level_statement);
IF l_datasource_selected = 'Y' THEN
l_update_sst_profile := 'Y';
IF l_update_sst_profile = 'Y' THEN
-- return SST record which we need to use to update an existing
-- SST profile.
HZ_MIXNM_UTILITY.updateSSTProfile (
p_create_update_flag => 'U',
p_create_update_sst_flag => 'U',
p_raise_error_flag => l_raise_error,
p_party_type => p_party_type,
p_party_id => l_party_id,
p_new_person_rec => p_person_rec,
p_old_person_rec => p_old_person_rec,
p_sst_person_rec => l_sst_person_rec,
p_new_sst_person_rec => l_new_sst_person_rec,
p_new_organization_rec => p_organization_rec,
p_old_organization_rec => p_old_organization_rec,
p_sst_organization_rec => l_sst_organization_rec,
p_new_sst_organization_rec => l_new_sst_organization_rec,
p_data_source_type => l_data_source_type,
x_return_status => x_return_status );
do_update_party_profile (
p_party_type => p_party_type,
p_person_rec => l_new_sst_person_rec,
p_old_person_rec => l_sst_person_rec,
p_organization_rec => l_new_sst_organization_rec,
p_old_organization_rec => l_sst_organization_rec,
p_data_source_type => G_SST_SOURCE_TYPE,
x_profile_id => l_profile_id );
do_update_party_profile (
p_party_type => p_party_type,
p_person_rec => l_new_sst_person_rec,
p_old_person_rec => l_ue_person_rec,
p_organization_rec => l_new_sst_organization_rec,
p_old_organization_rec => l_ue_organization_rec,
p_data_source_type => G_MISS_CONTENT_SOURCE_TYPE,
x_profile_id => l_profile_id );
l_update_party := 'Y';
IF l_update_party = 'Y' THEN
-- Debug info.
/*IF g_debug THEN
hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,'l_party_id = '||l_party_id);
do_create_update_party_only(
p_create_update_flag => 'U',
p_party_type => p_party_type,
p_party_id => l_party_id,
p_party_object_version_number => p_party_object_version_number,
p_person_rec => l_new_sst_person_rec,
p_old_person_rec => l_sst_person_rec,
p_organization_rec => l_new_sst_organization_rec,
p_old_organization_rec => l_sst_organization_rec,
x_party_id => l_dummy_id,
x_party_number => l_party_number );
p_create_update_flag => 'U',
p_party_type => p_party_type,
p_organization_rec => p_organization_rec,
p_old_organization_rec => p_old_organization_rec,
p_person_rec => p_person_rec,
p_old_person_rec => p_old_person_rec,
p_group_rec => p_group_rec,
p_old_group_rec => p_old_group_rec,
p_data_source_type => l_data_source_type,
x_return_status => x_return_status );
update_party_search(l_party_id,l_old_party_name,l_new_party_name,l_old_tax_reference,l_new_tax_reference);
update_rel_person_search(p_old_person_rec,p_person_rec);
IF l_update_party = 'Y' THEN
IF p_party_type = 'ORGANIZATION' THEN
l_new_sst_organization_rec.party_rec.orig_system := p_organization_rec.party_rec.orig_system;
HZ_BUSINESS_EVENT_V2PVT.update_organization_event(
l_new_sst_organization_rec, l_sst_organization_rec);
HZ_BUSINESS_EVENT_V2PVT.update_person_event(
l_new_sst_person_rec, l_sst_person_rec);
hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,'do_update_party (-)');
hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,p_message=>'do_update_party (-)',
p_msg_level=>fnd_log.level_procedure);
END do_update_party;
g_per_mixnmatch_enabled, g_per_selected_datasources);
p_operation_code => 'INSERT',
p_db_object_name => 'HZ_PARTIES',
p_instance_pk1_value => x_party_id,
p_user_name => fnd_global.user_name,
x_return_status => dss_return_status,
x_msg_count => dss_msg_count,
x_msg_data => dss_msg_data);
FND_MESSAGE.SET_NAME('AR', 'HZ_DSS_NO_INSERT_PRIVILEGE');
* PROCEDURE update_person
*
* DESCRIPTION
* Updates person.
*
* EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
* HZ_BUSINESS_EVENT_V2PVT.update_person_event
*
* ARGUMENTS
* IN:
* p_init_msg_list Initialize message stack if it is set to
* FND_API.G_TRUE. Default is fnd_api.g_false.
* p_person_rec Person record.
* IN/OUT:
* p_party_object_version_number Used for locking the being updated record.
* OUT:
* x_profile_id Person profile ID.
* x_return_status Return status after the call. The status can
* be FND_API.G_RET_STS_SUCCESS (success),
* fnd_api.g_ret_sts_error (error),
* fnd_api.g_ret_sts_unexp_error (unexpected error).
* x_msg_count Number of messages in message stack.
* x_msg_data Message text if x_msg_count is 1.
*
* NOTES
*
* MODIFICATION HISTORY
*
* 07-23-2001 Indrajit Sen o Created.
*
*/
PROCEDURE update_person (
p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
p_person_rec IN PERSON_REC_TYPE,
p_party_object_version_number IN OUT NOCOPY NUMBER,
x_profile_id OUT NOCOPY NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
) IS
l_api_name CONSTANT VARCHAR2(30) := 'update_person';
SAVEPOINT update_person;
hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,'update_person (+)');
hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,p_message=>'update_person (+)',
p_msg_level=>fnd_log.level_procedure);
SELECT 1 INTO l_count
FROM HZ_PARTIES
WHERE PARTY_ID = p_person_rec.party_rec.party_id
AND PARTY_TYPE = 'PERSON';
g_per_mixnmatch_enabled, g_per_selected_datasources);
p_operation_code => 'UPDATE',
p_db_object_name => 'HZ_PARTIES',
p_instance_pk1_value => l_person_rec.party_rec.party_id,
p_user_name => fnd_global.user_name,
x_return_status => dss_return_status,
x_msg_count => dss_msg_count,
x_msg_data => dss_msg_data);
FND_MESSAGE.SET_NAME('AR', 'HZ_DSS_NO_UPDATE_PRIVILEGE');
do_update_party(
p_party_type => 'PERSON',
p_person_rec => l_person_rec,
p_old_person_rec => l_old_person_rec,
p_party_object_version_number => p_party_object_version_number,
x_profile_id => x_profile_id,
x_return_status => x_return_status,
p_organization_rec => G_MISS_ORGANIZATION_REC,
p_group_rec => G_MISS_GROUP_REC );
hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,'update_person (-)');
hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,p_message=>'update_person (-)',
p_msg_level=>fnd_log.level_procedure);
ROLLBACK TO update_person;
hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,'update_person (-)');
hz_utility_v2pub.debug(p_message=>'update_person (-)',
p_prefix=>l_debug_prefix,
p_msg_level=>fnd_log.level_procedure);
ROLLBACK TO update_person;
hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,'update_person (-)');
hz_utility_v2pub.debug(p_message=>'update_person (-)',
p_prefix=>l_debug_prefix,
p_msg_level=>fnd_log.level_procedure);
ROLLBACK TO update_person;
hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,'update_person (-)');
hz_utility_v2pub.debug(p_message=>'update_person (-)',
p_prefix=>l_debug_prefix,
p_msg_level=>fnd_log.level_procedure);
END update_person;
p_operation_code => 'INSERT',
p_db_object_name => 'HZ_PARTIES',
p_instance_pk1_value => x_party_id,
p_user_name => fnd_global.user_name,
x_return_status => dss_return_status,
x_msg_count => dss_msg_count,
x_msg_data => dss_msg_data);
FND_MESSAGE.SET_NAME('AR', 'HZ_DSS_NO_INSERT_PRIVILEGE');
* PROCEDURE update_group
*
* DESCRIPTION
* Updates group.
*
* EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
* HZ_BUSINESS_EVENT_V2PVT.update_group_event
*
* ARGUMENTS
* IN:
* p_init_msg_list Initialize message stack if it is set to
* FND_API.G_TRUE. Default is fnd_api.g_false.
* p_group_rec Group record.
* IN/OUT:
* p_party_object_version_number Used for locking the being updated record.
* OUT:
* x_return_status Return status after the call. The status can
* be FND_API.G_RET_STS_SUCCESS (success),
* fnd_api.g_ret_sts_error (error),
* fnd_api.g_ret_sts_unexp_error (unexpected error).
* x_msg_count Number of messages in message stack.
* x_msg_data Message text if x_msg_count is 1.
*
* NOTES
*
* MODIFICATION HISTORY
*
* 07-23-2001 Indrajit Sen o Created.
*
*/
PROCEDURE update_group (
p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
p_group_rec IN GROUP_REC_TYPE,
p_party_object_version_number IN OUT NOCOPY NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
) IS
l_context VARCHAR2(30);
l_api_name CONSTANT VARCHAR2(30) := 'update_group';
SAVEPOINT update_group;
hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,'update_group (+)');
hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,p_message=>'update_group (+)',
p_msg_level=>fnd_log.level_procedure);
SELECT 1 INTO l_count
FROM HZ_PARTIES
WHERE PARTY_ID = p_group_rec.party_rec.party_id
AND PARTY_TYPE = 'GROUP';
p_operation_code => 'UPDATE',
p_db_object_name => 'HZ_PARTIES',
p_instance_pk1_value => l_group_rec.party_rec.party_id,
p_user_name => fnd_global.user_name,
x_return_status => dss_return_status,
x_msg_count => dss_msg_count,
x_msg_data => dss_msg_data);
FND_MESSAGE.SET_NAME('AR', 'HZ_DSS_NO_UPDATE_PRIVILEGE');
do_update_party(
p_party_type => 'GROUP',
p_group_rec => l_group_rec,
p_old_group_rec => l_old_group_rec,
p_party_object_version_number => p_party_object_version_number,
x_profile_id => l_profile_id,
x_return_status => x_return_status,
p_organization_rec => G_MISS_ORGANIZATION_REC,
p_person_rec => G_MISS_PERSON_REC );
HZ_BUSINESS_EVENT_V2PVT.update_group_event (l_group_rec , l_old_group_rec);
hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,'update_group (-)');
hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,p_message=>'update_group (-)',
p_msg_level=>fnd_log.level_procedure);
ROLLBACK TO update_group;
hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,'update_group (-)');
hz_utility_v2pub.debug(p_message=>'update_group (-)',
p_prefix=>l_debug_prefix,
p_msg_level=>fnd_log.level_procedure);
ROLLBACK TO update_group;
hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,'update_group (-)');
hz_utility_v2pub.debug(p_message=>'update_group (-)',
p_prefix=>l_debug_prefix,
p_msg_level=>fnd_log.level_procedure);
ROLLBACK TO update_group;
hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,'update_group (-)');
hz_utility_v2pub.debug(p_message=>'update_group (-)',
p_prefix=>l_debug_prefix,
p_msg_level=>fnd_log.level_procedure);
END update_group;
* Changed select...intos into cursors.
*
*/
PROCEDURE create_organization (
p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
p_organization_rec IN ORGANIZATION_REC_TYPE,
p_party_usage_code IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
x_party_id OUT NOCOPY NUMBER,
x_party_number OUT NOCOPY VARCHAR2,
x_profile_id OUT NOCOPY NUMBER
) IS
l_api_name CONSTANT VARCHAR2(30) := 'create_organization';
g_org_mixnmatch_enabled, g_org_selected_datasources);
p_create_update_flag => 'C',
p_organization_rec => l_organization_rec,
x_return_status => x_return_status );
p_operation_code => 'INSERT',
p_db_object_name => 'HZ_PARTIES',
p_instance_pk1_value => x_party_id,
p_user_name => fnd_global.user_name,
x_return_status => dss_return_status,
x_msg_count => dss_msg_count,
x_msg_data => dss_msg_data);
FND_MESSAGE.SET_NAME('AR', 'HZ_DSS_NO_INSERT_PRIVILEGE');
* Changed select...intos into cursors.
*
*/
PROCEDURE create_organization (
p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
p_organization_rec IN ORGANIZATION_REC_TYPE,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
x_party_id OUT NOCOPY NUMBER,
x_party_number OUT NOCOPY VARCHAR2,
x_profile_id OUT NOCOPY NUMBER
) IS
BEGIN
create_organization (
p_init_msg_list => p_init_msg_list,
p_organization_rec => p_organization_rec,
p_party_usage_code => null,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
x_party_id => x_party_id,
x_party_number => x_party_number,
x_profile_id => x_profile_id
);
* PROCEDURE update_organization
*
* DESCRIPTION
* Updates organization.
*
* EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
* HZ_BUSINESS_EVENT_V2PVT.update_organization_event
*
* ARGUMENTS
* IN:
* p_init_msg_list Initialize message stack if it is set to
* FND_API.G_TRUE. Default is fnd_api.g_false.
* p_organization_rec Organization record.
* IN/OUT:
* p_party_object_version_number Used for locking the being updated record.
* OUT:
* x_profile_id Organization profile ID.
* x_return_status Return status after the call. The status can
* be fnd_api.g_ret_sts_success (success),
* fnd_api.g_ret_sts_error (error),
* fnd_api.g_ret_sts_unexp_error (unexpected error).
* x_msg_count Number of messages in message stack.
* x_msg_data Message text if x_msg_count is 1.
*
* NOTES
*
* MODIFICATION HISTORY
*
* 07-23-2001 Indrajit Sen o Created.
* 26-NOV-2001 Joe del Callar Bug 2117973: modified to conform to
* PL/SQL coding standards.
* Changed select...intos into cursors.
* 09-03-2002 Jyoti Pandey Added Data security Functionality
*
*/
PROCEDURE update_organization (
p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
p_organization_rec IN ORGANIZATION_REC_TYPE,
p_party_object_version_number IN OUT NOCOPY NUMBER,
x_profile_id OUT NOCOPY NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
) IS
l_api_name CONSTANT VARCHAR2(30) := 'update_organization';
l_create_update_flag VARCHAR2(1);
SELECT 1
FROM hz_parties hp
WHERE hp.party_id = l_organization_rec.party_rec.party_id
AND hp.party_type = 'ORGANIZATION';
SAVEPOINT update_organization;
hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,'update_organization (+)');
hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,p_message=>'update_organization (+)',
p_msg_level=>fnd_log.level_procedure);
g_org_mixnmatch_enabled, g_org_selected_datasources);
p_operation_code => 'UPDATE',
p_db_object_name => 'HZ_PARTIES',
p_instance_pk1_value => l_organization_rec.party_rec.party_id,
p_user_name => fnd_global.user_name,
x_return_status => dss_return_status,
x_msg_count => dss_msg_count,
x_msg_data => dss_msg_data);
FND_MESSAGE.SET_NAME('AR', 'HZ_DSS_NO_UPDATE_PRIVILEGE');
do_update_party(
p_party_type => 'ORGANIZATION',
p_organization_rec => l_organization_rec,
p_old_organization_rec => l_old_organization_rec,
p_party_object_version_number => p_party_object_version_number,
x_profile_id => x_profile_id,
x_return_status => x_return_status,
p_person_rec => G_MISS_PERSON_REC,
p_group_rec => G_MISS_GROUP_REC );
SELECT 'U' INTO l_create_update_flag
FROM hz_credit_ratings
WHERE party_id = p_organization_rec.party_rec.party_id
AND actual_content_source = G_MISS_CONTENT_SOURCE_TYPE
AND ROWNUM = 1;
l_create_update_flag := 'C';
p_create_update_flag => l_create_update_flag,
p_organization_rec => l_organization_rec,
x_return_status => x_return_status );
hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,'update_organization (-)');
hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,p_message=>'update_organization (-)',
p_msg_level=>fnd_log.level_procedure);
ROLLBACK TO update_organization;
hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,'update_organization (-)');
hz_utility_v2pub.debug(p_message=>'update_organization (-)',
p_prefix=>l_debug_prefix,
p_msg_level=>fnd_log.level_procedure);
ROLLBACK TO update_organization;
hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,'update_organization (-)');
hz_utility_v2pub.debug(p_message=>'update_organization (-)',
p_prefix=>l_debug_prefix,
p_msg_level=>fnd_log.level_procedure);
ROLLBACK TO update_organization;
hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,'update_organization (-)');
hz_utility_v2pub.debug(p_message=>'update_organization (-)',
p_prefix=>l_debug_prefix,
p_msg_level=>fnd_log.level_procedure);
END update_organization;
* hz_organization_profiles_pkg.Select_Row
*
* ARGUMENTS
* IN:
* p_init_msg_list Initialize message stack if it is set to
* FND_API.G_TRUE. Default is fnd_api.g_false.
* p_party_id Party ID.
* p_content_source_type Content source type.
* IN/OUT:
* OUT:
* x_organization_rec Returned organization record.
* x_return_status Return status after the call. The status can
* be fnd_api.g_ret_sts_success (success),
* fnd_api.g_ret_sts_error (error),
* fnd_api.g_ret_sts_unexp_error (unexpected error).
* x_msg_count Number of messages in message stack.
* x_msg_data Message text if x_msg_count is 1.
*
* NOTES
*
* MODIFICATION HISTORY
*
* 07-23-2001 Indrajit Sen o Created.
* 26-NOV-2001 Joe del Callar o Bug 2116225: modified for
* consolidated bank support.
* Bug 2117973: modified to conform to
* PL/SQL coding standards.
* Changed select...intos into cursors.
*
*/
PROCEDURE get_organization_rec (
p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
p_party_id IN NUMBER,
p_content_source_type IN VARCHAR2 := g_miss_content_source_type,
x_organization_rec OUT NOCOPY ORGANIZATION_REC_TYPE,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
) IS
l_api_name CONSTANT VARCHAR2(30) := 'get_organization_rec';
SELECT NVL(org2.organization_profile_id,org1.organization_profile_id)
FROM
hz_organization_profiles org1,
(SELECT organization_profile_id, party_id
FROM hz_organization_profiles
WHERE party_id = p_party_id
AND actual_content_source = G_MISS_CONTENT_SOURCE_TYPE
AND effective_end_date IS NULL) org2
WHERE org1.party_id = p_party_id
AND org1.actual_content_source = 'SST'
AND org1.effective_end_date IS NULL
AND org1.party_id = org2.party_id (+);
SELECT organization_profile_id
FROM hz_organization_profiles
WHERE party_id = p_party_id
AND actual_content_source = p_content_source_type
AND effective_end_date IS NULL;
hz_organization_profiles_pkg.Select_Row (
x_organization_profile_id => l_profile_id,
x_party_id => l_party_id,
x_organization_name => x_organization_rec.organization_name,
x_attribute_category => x_organization_rec.attribute_category,
x_attribute1 => x_organization_rec.attribute1,
x_attribute2 => x_organization_rec.attribute2,
x_attribute3 => x_organization_rec.attribute3,
x_attribute4 => x_organization_rec.attribute4,
x_attribute5 => x_organization_rec.attribute5,
x_attribute6 => x_organization_rec.attribute6,
x_attribute7 => x_organization_rec.attribute7,
x_attribute8 => x_organization_rec.attribute8,
x_attribute9 => x_organization_rec.attribute9,
x_attribute10 => x_organization_rec.attribute10,
x_attribute11 => x_organization_rec.attribute11,
x_attribute12 => x_organization_rec.attribute12,
x_attribute13 => x_organization_rec.attribute13,
x_attribute14 => x_organization_rec.attribute14,
x_attribute15 => x_organization_rec.attribute15,
x_attribute16 => x_organization_rec.attribute16,
x_attribute17 => x_organization_rec.attribute17,
x_attribute18 => x_organization_rec.attribute18,
x_attribute19 => x_organization_rec.attribute19,
x_attribute20 => x_organization_rec.attribute20,
x_enquiry_duns => x_organization_rec.enquiry_duns,
x_ceo_name => x_organization_rec.ceo_name,
x_ceo_title => x_organization_rec.ceo_title,
x_principal_name => x_organization_rec.principal_name,
x_principal_title => x_organization_rec.principal_title,
x_legal_status => x_organization_rec.legal_status,
x_control_yr => x_organization_rec.control_yr,
x_employees_total => x_organization_rec.employees_total,
x_hq_branch_ind => x_organization_rec.hq_branch_ind,
x_branch_flag => x_organization_rec.branch_flag,
x_oob_ind => x_organization_rec.oob_ind,
x_line_of_business => x_organization_rec.line_of_business,
x_cong_dist_code => x_organization_rec.cong_dist_code,
x_sic_code => x_organization_rec.sic_code,
x_import_ind => x_organization_rec.import_ind,
x_export_ind => x_organization_rec.export_ind,
x_labor_surplus_ind => x_organization_rec.labor_surplus_ind,
x_debarment_ind => x_organization_rec.debarment_ind,
x_minority_owned_ind => x_organization_rec.minority_owned_ind,
x_minority_owned_type => x_organization_rec.minority_owned_type,
x_woman_owned_ind => x_organization_rec.woman_owned_ind,
x_disadv_8a_ind => x_organization_rec.disadv_8a_ind,
x_small_bus_ind => x_organization_rec.small_bus_ind,
x_rent_own_ind => x_organization_rec.rent_own_ind,
x_debarments_count => x_organization_rec.debarments_count,
x_debarments_date => x_organization_rec.debarments_date,
x_failure_score => x_organization_rec.failure_score,
x_failure_score_override_code => x_organization_rec.failure_score_override_code,
x_failure_score_commentary => x_organization_rec.failure_score_commentary,
x_global_failure_score => x_organization_rec.global_failure_score,
x_db_rating => x_organization_rec.db_rating,
x_credit_score => x_organization_rec.credit_score,
x_credit_score_commentary => x_organization_rec.credit_score_commentary,
x_paydex_score => x_organization_rec.paydex_score,
x_paydex_three_months_ago => x_organization_rec.paydex_three_months_ago,
x_paydex_norm => x_organization_rec.paydex_norm,
x_best_time_contact_begin => x_organization_rec.best_time_contact_begin,
x_best_time_contact_end => x_organization_rec.best_time_contact_end,
x_organization_name_phonetic => x_organization_rec.organization_name_phonetic,
x_tax_reference => x_organization_rec.tax_reference,
x_gsa_indicator_flag => x_organization_rec.gsa_indicator_flag,
x_jgzz_fiscal_code => x_organization_rec.jgzz_fiscal_code,
x_analysis_fy => x_organization_rec.analysis_fy,
x_fiscal_yearend_month => x_organization_rec.fiscal_yearend_month,
x_curr_fy_potential_revenue => x_organization_rec.curr_fy_potential_revenue,
x_next_fy_potential_revenue => x_organization_rec.next_fy_potential_revenue,
x_year_established => x_organization_rec.year_established,
x_mission_statement => x_organization_rec.mission_statement,
x_organization_type => x_organization_rec.organization_type,
x_business_scope => x_organization_rec.business_scope,
x_corporation_class => x_organization_rec.corporation_class,
x_known_as => x_organization_rec.known_as,
x_local_bus_iden_type => x_organization_rec.local_bus_iden_type,
x_local_bus_identifier => x_organization_rec.local_bus_identifier,
x_pref_functional_currency => x_organization_rec.pref_functional_currency,
x_registration_type => x_organization_rec.registration_type,
x_total_employees_text => x_organization_rec.total_employees_text,
x_total_employees_ind => x_organization_rec.total_employees_ind,
x_total_emp_est_ind => x_organization_rec.total_emp_est_ind,
x_total_emp_min_ind => x_organization_rec.total_emp_min_ind,
x_parent_sub_ind => x_organization_rec.parent_sub_ind,
x_incorp_year => x_organization_rec.incorp_year,
x_content_source_type => x_organization_rec.content_source_type,
x_content_source_number => x_organization_rec.content_source_number,
x_effective_start_date => l_effective_start_date,
x_effective_end_date => l_effective_end_date,
x_sic_code_type => x_organization_rec.sic_code_type,
x_public_private_ownership => x_organization_rec.public_private_ownership_flag,
x_local_activity_code_type => x_organization_rec.local_activity_code_type,
x_local_activity_code => x_organization_rec.local_activity_code,
x_emp_at_primary_adr => x_organization_rec.emp_at_primary_adr,
x_emp_at_primary_adr_text => x_organization_rec.emp_at_primary_adr_text,
x_emp_at_primary_adr_est_ind => x_organization_rec.emp_at_primary_adr_est_ind,
x_emp_at_primary_adr_min_ind => x_organization_rec.emp_at_primary_adr_min_ind,
x_internal_flag => x_organization_rec.internal_flag,
x_high_credit => x_organization_rec.high_credit,
x_avg_high_credit => x_organization_rec.avg_high_credit,
x_total_payments => x_organization_rec.total_payments,
x_known_as2 => x_organization_rec.known_as2,
x_known_as3 => x_organization_rec.known_as3,
x_known_as4 => x_organization_rec.known_as4,
x_known_as5 => x_organization_rec.known_as5,
x_credit_score_class => x_organization_rec.credit_score_class,
x_credit_score_natl_percentile => x_organization_rec.credit_score_natl_percentile,
x_credit_score_incd_default => x_organization_rec.credit_score_incd_default,
x_credit_score_age => x_organization_rec.credit_score_age,
x_credit_score_date => x_organization_rec.credit_score_date,
x_failure_score_class => x_organization_rec.failure_score_class,
x_failure_score_incd_default => x_organization_rec.failure_score_incd_default,
x_failure_score_age => x_organization_rec.failure_score_age,
x_failure_score_date => x_organization_rec.failure_score_date,
x_failure_score_commentary2 => x_organization_rec.failure_score_commentary2,
x_failure_score_commentary3 => x_organization_rec.failure_score_commentary3,
x_failure_score_commentary4 => x_organization_rec.failure_score_commentary4,
x_failure_score_commentary5 => x_organization_rec.failure_score_commentary5,
x_failure_score_commentary6 => x_organization_rec.failure_score_commentary6,
x_failure_score_commentary7 => x_organization_rec.failure_score_commentary7,
x_failure_score_commentary8 => x_organization_rec.failure_score_commentary8,
x_failure_score_commentary9 => x_organization_rec.failure_score_commentary9,
x_failure_score_commentary10 => x_organization_rec.failure_score_commentary10,
x_credit_score_commentary2 => x_organization_rec.credit_score_commentary2,
x_credit_score_commentary3 => x_organization_rec.credit_score_commentary3,
x_credit_score_commentary4 => x_organization_rec.credit_score_commentary4,
x_credit_score_commentary5 => x_organization_rec.credit_score_commentary5,
x_credit_score_commentary6 => x_organization_rec.credit_score_commentary6,
x_credit_score_commentary7 => x_organization_rec.credit_score_commentary7,
x_credit_score_commentary8 => x_organization_rec.credit_score_commentary8,
x_credit_score_commentary9 => x_organization_rec.credit_score_commentary9,
x_credit_score_commentary10 => x_organization_rec.credit_score_commentary10,
x_maximum_credit_recomm => x_organization_rec.maximum_credit_recommendation,
x_maximum_credit_currency_code => x_organization_rec.maximum_credit_currency_code,
x_displayed_duns_party_id => x_organization_rec.displayed_duns_party_id,
x_failure_score_natnl_perc => x_organization_rec.failure_score_natnl_percentile,
x_duns_number_c => x_organization_rec.duns_number_c,
x_bank_or_branch_number => l_bank_or_branch_number,
x_bank_code => l_bank_code,
x_branch_code => l_branch_code,
x_created_by_module => x_organization_rec.created_by_module,
x_application_id => x_organization_rec.application_id,
x_do_not_confuse_with => x_organization_rec.do_not_confuse_with,
x_actual_content_source => x_organization_rec.actual_content_source,
x_home_country => x_organization_rec.home_country
);
* hz_person_profiles_pkg.Select_Row
*
* ARGUMENTS
* IN:
* p_init_msg_list Initialize message stack if it is set to
* FND_API.G_TRUE. Default is fnd_api.g_false.
* p_party_id Party ID.
* p_content_source_type Content source type.
* IN/OUT:
* OUT:
* x_person_rec Returned person record.
* x_return_status Return status after the call. The status can
* be fnd_api.g_ret_sts_success (success),
* fnd_api.g_ret_sts_error (error),
* fnd_api.g_ret_sts_unexp_error (unexpected error)
* x_msg_count Number of messages in message stack.
* x_msg_data Message text if x_msg_count is 1.
*
* NOTES
*
* MODIFICATION HISTORY
*
* 07-23-2001 Indrajit Sen o Created.
*
*/
PROCEDURE get_person_rec (
p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
p_party_id IN NUMBER,
p_content_source_type IN VARCHAR2 := G_MISS_CONTENT_SOURCE_TYPE,
x_person_rec OUT NOCOPY PERSON_REC_TYPE,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
) IS
l_api_name CONSTANT VARCHAR2(30) := 'get_person_profile_rec';
SELECT NVL(per2.person_profile_id,per1.person_profile_id)
FROM
hz_person_profiles per1,
(SELECT person_profile_id, party_id
FROM hz_person_profiles
WHERE party_id = p_party_id
AND actual_content_source = G_MISS_CONTENT_SOURCE_TYPE
AND effective_end_date IS NULL) per2
WHERE per1.party_id = p_party_id
AND per1.actual_content_source = 'SST'
AND per1.effective_end_date IS NULL
AND per1.party_id = per2.party_id (+);
SELECT person_profile_id
FROM hz_person_profiles
WHERE party_id = p_party_id
AND actual_content_source = p_content_source_type
AND effective_end_date IS NULL;
HZ_person_profiles_pkg.Select_Row (
x_person_profile_id => l_profile_id,
x_party_id => l_party_id,
x_attribute_category => x_person_rec.attribute_category,
x_attribute1 => x_person_rec.attribute1,
x_attribute2 => x_person_rec.attribute2,
x_attribute3 => x_person_rec.attribute3,
x_attribute4 => x_person_rec.attribute4,
x_attribute5 => x_person_rec.attribute5,
x_attribute6 => x_person_rec.attribute6,
x_attribute7 => x_person_rec.attribute7,
x_attribute8 => x_person_rec.attribute8,
x_attribute9 => x_person_rec.attribute9,
x_attribute10 => x_person_rec.attribute10,
x_attribute11 => x_person_rec.attribute11,
x_attribute12 => x_person_rec.attribute12,
x_attribute13 => x_person_rec.attribute13,
x_attribute14 => x_person_rec.attribute14,
x_attribute15 => x_person_rec.attribute15,
x_attribute16 => x_person_rec.attribute16,
x_attribute17 => x_person_rec.attribute17,
x_attribute18 => x_person_rec.attribute18,
x_attribute19 => x_person_rec.attribute19,
x_attribute20 => x_person_rec.attribute20,
x_person_pre_name_adjunct => x_person_rec.person_pre_name_adjunct,
x_person_first_name => x_person_rec.person_first_name,
x_person_middle_name => x_person_rec.person_middle_name,
x_person_last_name => x_person_rec.person_last_name,
x_person_name_suffix => x_person_rec.person_name_suffix,
x_person_title => x_person_rec.person_title,
x_person_academic_title => x_person_rec.person_academic_title,
x_person_previous_last_name => x_person_rec.person_previous_last_name,
x_person_initials => x_person_rec.person_initials,
x_known_as => x_person_rec.known_as,
x_person_name_phonetic => x_person_rec.person_name_phonetic,
x_person_first_name_phonetic => x_person_rec.person_first_name_phonetic,
x_person_last_name_phonetic => x_person_rec.person_last_name_phonetic,
x_tax_reference => x_person_rec.tax_reference,
x_jgzz_fiscal_code => x_person_rec.jgzz_fiscal_code,
x_person_iden_type => x_person_rec.person_iden_type,
x_person_identifier => x_person_rec.person_identifier,
x_date_of_birth => x_person_rec.date_of_birth,
x_place_of_birth => x_person_rec.place_of_birth,
x_date_of_death => x_person_rec.date_of_death,
x_deceased_flag => x_person_rec.deceased_flag,
x_gender => x_person_rec.gender,
x_declared_ethnicity => x_person_rec.declared_ethnicity,
x_marital_status => x_person_rec.marital_status,
x_marital_status_eff_date => x_person_rec.marital_status_effective_date,
x_personal_income => x_person_rec.personal_income,
x_head_of_household_flag => x_person_rec.head_of_household_flag,
x_household_income => x_person_rec.household_income,
x_household_size => x_person_rec.household_size,
x_rent_own_ind => x_person_rec.rent_own_ind,
x_last_known_gps => x_person_rec.last_known_gps,
x_effective_start_date => l_effective_start_date,
x_effective_end_date => l_effective_end_date,
x_content_source_type => x_person_rec.content_source_type,
x_internal_flag => x_person_rec.internal_flag,
x_known_as2 => x_person_rec.known_as2,
x_known_as3 => x_person_rec.known_as3,
x_known_as4 => x_person_rec.known_as4,
x_known_as5 => x_person_rec.known_as5,
x_middle_name_phonetic => x_person_rec.middle_name_phonetic,
x_created_by_module => x_person_rec.created_by_module,
x_application_id => x_person_rec.application_id,
x_actual_content_source => x_person_rec.actual_content_source
);
SELECT 'Y'
FROM hz_parties
WHERE party_id = p_party_id
AND party_type = 'GROUP';
hz_parties_pkg.Select_Row (
x_party_id => x_group_rec.party_rec.party_id,
x_party_number => x_group_rec.party_rec.party_number,
x_party_name => x_group_rec.group_name,
x_party_type => l_party_type,
x_validated_flag => x_group_rec.party_rec.validated_flag,
x_attribute_category => x_group_rec.party_rec.attribute_category,
x_attribute1 => x_group_rec.party_rec.attribute1,
x_attribute2 => x_group_rec.party_rec.attribute2,
x_attribute3 => x_group_rec.party_rec.attribute3,
x_attribute4 => x_group_rec.party_rec.attribute4,
x_attribute5 => x_group_rec.party_rec.attribute5,
x_attribute6 => x_group_rec.party_rec.attribute6,
x_attribute7 => x_group_rec.party_rec.attribute7,
x_attribute8 => x_group_rec.party_rec.attribute8,
x_attribute9 => x_group_rec.party_rec.attribute9,
x_attribute10 => x_group_rec.party_rec.attribute10,
x_attribute11 => x_group_rec.party_rec.attribute11,
x_attribute12 => x_group_rec.party_rec.attribute12,
x_attribute13 => x_group_rec.party_rec.attribute13,
x_attribute14 => x_group_rec.party_rec.attribute14,
x_attribute15 => x_group_rec.party_rec.attribute15,
x_attribute16 => x_group_rec.party_rec.attribute16,
x_attribute17 => x_group_rec.party_rec.attribute17,
x_attribute18 => x_group_rec.party_rec.attribute18,
x_attribute19 => x_group_rec.party_rec.attribute19,
x_attribute20 => x_group_rec.party_rec.attribute20,
x_attribute21 => x_group_rec.party_rec.attribute21,
x_attribute22 => x_group_rec.party_rec.attribute22,
x_attribute23 => x_group_rec.party_rec.attribute23,
x_attribute24 => x_group_rec.party_rec.attribute24,
x_orig_system_reference => x_group_rec.party_rec.orig_system_reference,
x_sic_code => x_party_dup_rec.sic_code,
x_hq_branch_ind => x_party_dup_rec.hq_branch_ind,
x_customer_key => l_customer_key,
x_tax_reference => x_party_dup_rec.tax_reference,
x_jgzz_fiscal_code => x_party_dup_rec.jgzz_fiscal_code,
x_person_pre_name_adjunct => x_party_dup_rec.pre_name_adjunct,
x_person_first_name => x_party_dup_rec.first_name,
x_person_middle_name => x_party_dup_rec.middle_name,
x_person_last_name => x_party_dup_rec.last_name,
x_person_name_suffix => x_party_dup_rec.name_suffix,
x_person_title => x_party_dup_rec.title,
x_person_academic_title => x_party_dup_rec.academic_title,
x_person_previous_last_name => x_party_dup_rec.previous_last_name,
x_known_as => x_party_dup_rec.known_as,
x_person_iden_type => x_party_dup_rec.person_iden_type,
x_person_identifier => x_party_dup_rec.person_identifier,
x_group_type => x_group_rec.group_type,
x_country => l_country,
x_address1 => l_address1,
x_address2 => l_address2,
x_address3 => l_address3,
x_address4 => l_address4,
x_city => l_city,
x_postal_code => l_postal_code,
x_state => l_state,
x_province => l_province,
x_status => x_group_rec.party_rec.status,
x_county => l_county,
x_sic_code_type => x_party_dup_rec.sic_code_type,
x_url => l_url,
x_email_address => l_email_address,
x_analysis_fy => x_party_dup_rec.analysis_fy,
x_fiscal_yearend_month => x_party_dup_rec.fiscal_yearend_month,
x_employees_total => x_party_dup_rec.employees_total,
x_curr_fy_potential_revenue => x_party_dup_rec.curr_fy_potential_revenue,
x_next_fy_potential_revenue => x_party_dup_rec.next_fy_potential_revenue,
x_year_established => x_party_dup_rec.year_established,
x_gsa_indicator_flag => x_party_dup_rec.gsa_indicator_flag,
-- Bug 2467872
x_mission_statement => x_group_rec.mission_statement,
x_organization_name_phonetic => x_party_dup_rec.organization_name_phonetic,
x_person_first_name_phonetic => x_party_dup_rec.person_first_name_phonetic,
x_person_last_name_phonetic => x_party_dup_rec.person_last_name_phonetic,
x_language_name => l_language_name,
x_category_code => x_group_rec.party_rec.category_code,
x_salutation => x_group_rec.party_rec.salutation,
x_known_as2 => x_party_dup_rec.known_as2,
x_known_as3 => x_party_dup_rec.known_as3,
x_known_as4 => x_party_dup_rec.known_as4,
x_known_as5 => x_party_dup_rec.known_as5,
x_duns_number_c => x_party_dup_rec.duns_number_c,
x_created_by_module => x_group_rec.created_by_module,
x_application_id => x_group_rec.application_id
);
hz_parties_pkg.Select_Row (
x_party_id => x_party_rec.party_id,
x_party_number => x_party_rec.party_number,
x_party_name => l_party_name,
x_party_type => l_party_type,
x_validated_flag => x_party_rec.validated_flag,
x_attribute_category => x_party_rec.attribute_category,
x_attribute1 => x_party_rec.attribute1,
x_attribute2 => x_party_rec.attribute2,
x_attribute3 => x_party_rec.attribute3,
x_attribute4 => x_party_rec.attribute4,
x_attribute5 => x_party_rec.attribute5,
x_attribute6 => x_party_rec.attribute6,
x_attribute7 => x_party_rec.attribute7,
x_attribute8 => x_party_rec.attribute8,
x_attribute9 => x_party_rec.attribute9,
x_attribute10 => x_party_rec.attribute10,
x_attribute11 => x_party_rec.attribute11,
x_attribute12 => x_party_rec.attribute12,
x_attribute13 => x_party_rec.attribute13,
x_attribute14 => x_party_rec.attribute14,
x_attribute15 => x_party_rec.attribute15,
x_attribute16 => x_party_rec.attribute16,
x_attribute17 => x_party_rec.attribute17,
x_attribute18 => x_party_rec.attribute18,
x_attribute19 => x_party_rec.attribute19,
x_attribute20 => x_party_rec.attribute20,
x_attribute21 => x_party_rec.attribute21,
x_attribute22 => x_party_rec.attribute22,
x_attribute23 => x_party_rec.attribute23,
x_attribute24 => x_party_rec.attribute24,
x_orig_system_reference => x_party_rec.orig_system_reference,
x_sic_code => x_party_dup_rec.sic_code,
x_hq_branch_ind => x_party_dup_rec.hq_branch_ind,
x_customer_key => l_customer_key,
x_tax_reference => x_party_dup_rec.tax_reference,
x_jgzz_fiscal_code => x_party_dup_rec.jgzz_fiscal_code,
x_person_pre_name_adjunct => x_party_dup_rec.pre_name_adjunct,
x_person_first_name => x_party_dup_rec.first_name,
x_person_middle_name => x_party_dup_rec.middle_name,
x_person_last_name => x_party_dup_rec.last_name,
x_person_name_suffix => x_party_dup_rec.name_suffix,
x_person_title => x_party_dup_rec.title,
x_person_academic_title => x_party_dup_rec.academic_title,
x_person_previous_last_name => x_party_dup_rec.previous_last_name,
x_known_as => x_party_dup_rec.known_as,
x_person_iden_type => x_party_dup_rec.person_iden_type,
x_person_identifier => x_party_dup_rec.person_identifier,
x_group_type => l_group_type,
x_country => l_country,
x_address1 => l_address1,
x_address2 => l_address2,
x_address3 => l_address3,
x_address4 => l_address4,
x_city => l_city,
x_postal_code => l_postal_code,
x_state => l_state,
x_province => l_province,
x_status => x_party_rec.status,
x_county => l_county,
x_sic_code_type => x_party_dup_rec.sic_code_type,
x_url => l_url,
x_email_address => l_email_address,
x_analysis_fy => x_party_dup_rec.analysis_fy,
x_fiscal_yearend_month => x_party_dup_rec.fiscal_yearend_month,
x_employees_total => x_party_dup_rec.employees_total,
x_curr_fy_potential_revenue => x_party_dup_rec.curr_fy_potential_revenue,
x_next_fy_potential_revenue => x_party_dup_rec.next_fy_potential_revenue,
x_year_established => x_party_dup_rec.year_established,
x_gsa_indicator_flag => x_party_dup_rec.gsa_indicator_flag,
x_mission_statement => x_party_dup_rec.mission_statement,
x_organization_name_phonetic => x_party_dup_rec.organization_name_phonetic,
x_person_first_name_phonetic => x_party_dup_rec.person_first_name_phonetic,
x_person_last_name_phonetic => x_party_dup_rec.person_last_name_phonetic,
x_language_name => l_language_name,
x_category_code => x_party_rec.category_code,
x_salutation => x_party_rec.salutation,
x_known_as2 => x_party_dup_rec.known_as2,
x_known_as3 => x_party_dup_rec.known_as3,
x_known_as4 => x_party_dup_rec.known_as4,
x_known_as5 => x_party_dup_rec.known_as5,
x_duns_number_c => x_party_dup_rec.duns_number_c,
x_created_by_module => l_created_by_module,
x_application_id => l_application_id
);
* insert credit related data to hz_credit_rating
*/
/* 24-Sep-2004 V.Ravichandran o Bug 3868940 : Defaulted the rated_as_of_date to sysdate
* while creating a credit_rating in populate_credit_rating()
* procedure and retained its previous value while updating
* a credit rating populate_credit_rating procedure.
* Called HZ_PARTY_INFO_V2PUB.create/update_credit_rating
* from populate_credit_rating() procedure directly
* instead of calling the V1 APIs
* HZ_PARTY_INFO_PUB.create/updating_credit_rating
* Commented out the procedure org_rec_to_cr_rec.
*/
PROCEDURE populate_credit_rating(
p_create_update_flag IN VARCHAR2,
p_organization_rec IN ORGANIZATION_REC_TYPE,
x_return_status IN OUT NOCOPY VARCHAR2
) IS
l_credit_rating_rec HZ_PARTY_INFO_V2PUB.credit_rating_rec_type;
l_last_update_date DATE := sysdate;
( p_create_update_flag = 'C' AND
NOT(
( p_organization_rec.AVG_HIGH_CREDIT is null OR
p_organization_rec.AVG_HIGH_CREDIT = FND_API.G_MISS_NUM ) AND
( p_organization_rec.CREDIT_SCORE is null OR
p_organization_rec.CREDIT_SCORE = FND_API.G_MISS_CHAR) AND
( p_organization_rec.CREDIT_SCORE_AGE is null OR
p_organization_rec.CREDIT_SCORE_AGE = FND_API.G_MISS_NUM) AND
( p_organization_rec.CREDIT_SCORE_CLASS is null OR
p_organization_rec.CREDIT_SCORE_CLASS = FND_API.G_MISS_NUM) AND
( p_organization_rec.CREDIT_SCORE_COMMENTARY is null OR
p_organization_rec.CREDIT_SCORE_COMMENTARY = FND_API.G_MISS_CHAR) AND
( p_organization_rec.CREDIT_SCORE_COMMENTARY2 is null OR
p_organization_rec.CREDIT_SCORE_COMMENTARY2 = FND_API.G_MISS_CHAR) AND
( p_organization_rec.CREDIT_SCORE_COMMENTARY3 is null OR
p_organization_rec.CREDIT_SCORE_COMMENTARY3 = FND_API.G_MISS_CHAR) AND
( p_organization_rec.CREDIT_SCORE_COMMENTARY4 is null OR
p_organization_rec.CREDIT_SCORE_COMMENTARY4 = FND_API.G_MISS_CHAR) AND
( p_organization_rec.CREDIT_SCORE_COMMENTARY5 is null OR
p_organization_rec.CREDIT_SCORE_COMMENTARY5 = FND_API.G_MISS_CHAR) AND
( p_organization_rec.CREDIT_SCORE_COMMENTARY6 is null OR
p_organization_rec.CREDIT_SCORE_COMMENTARY6 = FND_API.G_MISS_CHAR) AND
( p_organization_rec.CREDIT_SCORE_COMMENTARY7 is null OR
p_organization_rec.CREDIT_SCORE_COMMENTARY7 = FND_API.G_MISS_CHAR) AND
( p_organization_rec.CREDIT_SCORE_COMMENTARY8 is null OR
p_organization_rec.CREDIT_SCORE_COMMENTARY8 = FND_API.G_MISS_CHAR) AND
( p_organization_rec.CREDIT_SCORE_COMMENTARY9 is null OR
p_organization_rec.CREDIT_SCORE_COMMENTARY9 = FND_API.G_MISS_CHAR) AND
( p_organization_rec.CREDIT_SCORE_COMMENTARY10 is null OR
p_organization_rec.CREDIT_SCORE_COMMENTARY10 = FND_API.G_MISS_CHAR) AND
( p_organization_rec.CREDIT_SCORE_DATE is null OR
p_organization_rec.CREDIT_SCORE_DATE = FND_API.G_MISS_DATE) AND
( p_organization_rec.CREDIT_SCORE_INCD_DEFAULT is null OR
p_organization_rec.CREDIT_SCORE_INCD_DEFAULT = FND_API.G_MISS_NUM) AND
( p_organization_rec.CREDIT_SCORE_NATL_PERCENTILE is null OR
p_organization_rec.CREDIT_SCORE_NATL_PERCENTILE = FND_API.G_MISS_NUM) AND
( p_organization_rec.DB_RATING is null OR
p_organization_rec.DB_RATING = FND_API.G_MISS_CHAR) AND
( p_organization_rec.DEBARMENT_IND is null OR
p_organization_rec.DEBARMENT_IND = FND_API.G_MISS_CHAR) AND
( p_organization_rec.DEBARMENTS_COUNT is null OR
p_organization_rec.DEBARMENTS_COUNT = FND_API.G_MISS_NUM) AND
( p_organization_rec.DEBARMENTS_DATE is null OR
p_organization_rec.DEBARMENTS_DATE = FND_API.G_MISS_DATE) AND
( p_organization_rec.HIGH_CREDIT is null OR
p_organization_rec.HIGH_CREDIT = FND_API.G_MISS_NUM) AND
( p_organization_rec.MAXIMUM_CREDIT_CURRENCY_CODE is null OR
p_organization_rec.MAXIMUM_CREDIT_CURRENCY_CODE = FND_API.G_MISS_CHAR) AND
( p_organization_rec.MAXIMUM_CREDIT_RECOMMENDATION is null OR
p_organization_rec.MAXIMUM_CREDIT_RECOMMENDATION = FND_API.G_MISS_NUM) AND
( p_organization_rec.PAYDEX_NORM is null OR
p_organization_rec.PAYDEX_NORM = FND_API.G_MISS_CHAR) AND
( p_organization_rec.PAYDEX_SCORE is null OR
p_organization_rec.PAYDEX_SCORE = FND_API.G_MISS_CHAR) AND
( p_organization_rec.PAYDEX_THREE_MONTHS_AGO is null OR
p_organization_rec.PAYDEX_THREE_MONTHS_AGO = FND_API.G_MISS_CHAR) AND
( p_organization_rec.FAILURE_SCORE is null OR
p_organization_rec.FAILURE_SCORE = FND_API.G_MISS_CHAR ) AND
( p_organization_rec.FAILURE_SCORE_AGE is null OR
p_organization_rec.FAILURE_SCORE_AGE = FND_API.G_MISS_NUM) AND
( p_organization_rec.FAILURE_SCORE_CLASS is null OR
p_organization_rec.FAILURE_SCORE_CLASS = FND_API.G_MISS_NUM) AND
( p_organization_rec.FAILURE_SCORE_COMMENTARY is null OR
p_organization_rec.FAILURE_SCORE_COMMENTARY = FND_API.G_MISS_CHAR) AND
( p_organization_rec.FAILURE_SCORE_COMMENTARY2 is null OR
p_organization_rec.FAILURE_SCORE_COMMENTARY2 = FND_API.G_MISS_CHAR) AND
( p_organization_rec.FAILURE_SCORE_COMMENTARY3 is null OR
p_organization_rec.FAILURE_SCORE_COMMENTARY3 = FND_API.G_MISS_CHAR) AND
( p_organization_rec.FAILURE_SCORE_COMMENTARY4 is null OR
p_organization_rec.FAILURE_SCORE_COMMENTARY4 = FND_API.G_MISS_CHAR) AND
( p_organization_rec.FAILURE_SCORE_COMMENTARY5 is null OR
p_organization_rec.FAILURE_SCORE_COMMENTARY5 = FND_API.G_MISS_CHAR) AND
( p_organization_rec.FAILURE_SCORE_COMMENTARY6 is null OR
p_organization_rec.FAILURE_SCORE_COMMENTARY6 = FND_API.G_MISS_CHAR) AND
( p_organization_rec.FAILURE_SCORE_COMMENTARY7 is null OR
p_organization_rec.FAILURE_SCORE_COMMENTARY7 = FND_API.G_MISS_CHAR) AND
( p_organization_rec.FAILURE_SCORE_COMMENTARY8 is null OR
p_organization_rec.FAILURE_SCORE_COMMENTARY8 = FND_API.G_MISS_CHAR) AND
( p_organization_rec.FAILURE_SCORE_COMMENTARY9 is null OR
p_organization_rec.FAILURE_SCORE_COMMENTARY9 = FND_API.G_MISS_CHAR) AND
( p_organization_rec.FAILURE_SCORE_COMMENTARY10 is null OR
p_organization_rec.FAILURE_SCORE_COMMENTARY10 = FND_API.G_MISS_CHAR) AND
( p_organization_rec.FAILURE_SCORE_DATE is null OR
p_organization_rec.FAILURE_SCORE_DATE = FND_API.G_MISS_DATE) AND
( p_organization_rec.FAILURE_SCORE_INCD_DEFAULT is null OR
p_organization_rec.FAILURE_SCORE_INCD_DEFAULT = FND_API.G_MISS_NUM) AND
( p_organization_rec.FAILURE_SCORE_NATNL_PERCENTILE is null OR
p_organization_rec.FAILURE_SCORE_NATNL_PERCENTILE = FND_API.G_MISS_NUM) AND
( p_organization_rec.FAILURE_SCORE_OVERRIDE_CODE is null OR
p_organization_rec.FAILURE_SCORE_OVERRIDE_CODE = FND_API.G_MISS_CHAR) AND
( p_organization_rec.GLOBAL_FAILURE_SCORE is null OR
p_organization_rec.GLOBAL_FAILURE_SCORE = FND_API.G_MISS_CHAR)
) ) OR
( p_create_update_flag = 'U' AND
( p_organization_rec.AVG_HIGH_CREDIT is not null OR
p_organization_rec.CREDIT_SCORE is not null OR
p_organization_rec.CREDIT_SCORE_AGE is not null OR
p_organization_rec.CREDIT_SCORE_CLASS is not null OR
p_organization_rec.CREDIT_SCORE_COMMENTARY is not null OR
p_organization_rec.CREDIT_SCORE_COMMENTARY2 is not null OR
p_organization_rec.CREDIT_SCORE_COMMENTARY3 is not null OR
p_organization_rec.CREDIT_SCORE_COMMENTARY4 is not null OR
p_organization_rec.CREDIT_SCORE_COMMENTARY5 is not null OR
p_organization_rec.CREDIT_SCORE_COMMENTARY6 is not null OR
p_organization_rec.CREDIT_SCORE_COMMENTARY7 is not null OR
p_organization_rec.CREDIT_SCORE_COMMENTARY8 is not null OR
p_organization_rec.CREDIT_SCORE_COMMENTARY9 is not null OR
p_organization_rec.CREDIT_SCORE_COMMENTARY10 is not null OR
p_organization_rec.CREDIT_SCORE_DATE is not null OR
p_organization_rec.CREDIT_SCORE_INCD_DEFAULT is not null OR
p_organization_rec.CREDIT_SCORE_NATL_PERCENTILE is not null OR
p_organization_rec.DB_RATING is not null OR
p_organization_rec.DEBARMENT_IND is not null OR
p_organization_rec.DEBARMENTS_COUNT is not null OR
p_organization_rec.DEBARMENTS_DATE is not null OR
p_organization_rec.HIGH_CREDIT is not null OR
p_organization_rec.MAXIMUM_CREDIT_CURRENCY_CODE is not null OR
p_organization_rec.MAXIMUM_CREDIT_RECOMMENDATION is not null OR
p_organization_rec.PAYDEX_NORM is not null OR
p_organization_rec.PAYDEX_SCORE is not null OR
p_organization_rec.PAYDEX_THREE_MONTHS_AGO is not null OR
p_organization_rec.FAILURE_SCORE is not null OR
p_organization_rec.FAILURE_SCORE_AGE is not null OR
p_organization_rec.FAILURE_SCORE_CLASS is not null OR
p_organization_rec.FAILURE_SCORE_COMMENTARY is not null OR
p_organization_rec.FAILURE_SCORE_COMMENTARY2 is not null OR
p_organization_rec.FAILURE_SCORE_COMMENTARY3 is not null OR
p_organization_rec.FAILURE_SCORE_COMMENTARY4 is not null OR
p_organization_rec.FAILURE_SCORE_COMMENTARY5 is not null OR
p_organization_rec.FAILURE_SCORE_COMMENTARY6 is not null OR
p_organization_rec.FAILURE_SCORE_COMMENTARY7 is not null OR
p_organization_rec.FAILURE_SCORE_COMMENTARY8 is not null OR
p_organization_rec.FAILURE_SCORE_COMMENTARY9 is not null OR
p_organization_rec.FAILURE_SCORE_COMMENTARY10 is not null OR
p_organization_rec.FAILURE_SCORE_DATE is not null OR
p_organization_rec.FAILURE_SCORE_INCD_DEFAULT is not null OR
p_organization_rec.FAILURE_SCORE_NATNL_PERCENTILE is not null OR
p_organization_rec.FAILURE_SCORE_OVERRIDE_CODE is not null OR
p_organization_rec.GLOBAL_FAILURE_SCORE
is not null) )
THEN
IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,p_message=>'copy record from ORGANIZATION_REC_TYPE '||
'to CREDIT_RATINGS_REC_TYPE (+)',
p_msg_level=>fnd_log.level_procedure);
IF p_create_update_flag='C'
THEN
l_credit_rating_rec.rated_as_of_date := sysdate;
IF p_create_update_flag='C' THEN
/*IF g_debug THEN
hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,'call to create credit_rating (+)');
p_create_update_flag => 'C',
p_organization_rec => p_organization_rec,
x_credit_rating_rec => l_credit_rating_rec );
hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,'call to update credit rating (+)');
hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,p_message=>'call to update credit rating (+)',
p_msg_level=>fnd_log.level_procedure);
SELECT MAX(rated_as_of_date)
INTO l_rated_as_of_date
FROM hz_credit_ratings
WHERE party_id = p_organization_rec.party_rec.party_id
AND actual_content_source = G_MISS_CONTENT_SOURCE_TYPE;
SELECT credit_rating_id, last_update_date,object_version_number
INTO l_credit_rating_id, l_last_update_date,l_object_version_number
FROM hz_credit_ratings
WHERE party_id = p_organization_rec.party_rec.party_id
AND actual_content_source = G_MISS_CONTENT_SOURCE_TYPE
AND(rated_as_of_date = l_rated_as_of_date OR
(rated_as_of_date is null AND
l_rated_as_of_date is null));
SELECT credit_rating_id, last_update_date,object_version_number
INTO l_credit_rating_id, l_last_update_date,l_object_version_number
FROM hz_credit_ratings
WHERE party_id = p_organization_rec.party_rec.party_id
AND actual_content_source = G_MISS_CONTENT_SOURCE_TYPE
AND (rated_as_of_date = l_rated_as_of_date OR
(rated_as_of_date is null AND
l_rated_as_of_date is null))
AND last_update_date = (
SELECT MAX(last_update_date)
FROM hz_credit_ratings
WHERE party_id = p_organization_rec.party_rec.party_id
AND actual_content_source = G_MISS_CONTENT_SOURCE_TYPE
AND (rated_as_of_date = l_rated_as_of_date OR
(rated_as_of_date is null AND
l_rated_as_of_date is null)))
AND ROWNUM = 1;
p_create_update_flag => 'U',
p_organization_rec => p_organization_rec,
x_credit_rating_rec => l_credit_rating_rec );
HZ_PARTY_INFO_PUB.update_credit_ratings(
p_api_version => 1,
p_credit_ratings_rec => l_credit_rating_rec,
p_last_update_date => l_last_update_date,
x_return_status => x_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
HZ_PARTY_INFO_V2PUB.update_credit_rating(
p_init_msg_list => 'F',
p_credit_rating_rec => l_credit_rating_rec,
p_object_version_number=> l_object_version_number,
x_return_status => x_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data
);
hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,'call to update credit rating (-)');
hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,p_message=>'call to update credit rating (-)',
p_msg_level=>fnd_log.level_procedure);
p_create_update_flag IN VARCHAR2,
p_organization_rec IN ORGANIZATION_REC_TYPE,
x_credit_rating_rec OUT NOCOPY HZ_PARTY_INFO_PUB.CREDIT_RATINGS_REC_TYPE
) IS
l_debug_prefix VARCHAR2(30) := '';
IF p_create_update_flag = 'C' THEN
/*IF g_debug THEN
hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,'copy record from ORGANIZATION_REC_TYPE '||
'to CREDIT_RATINGS_REC_TYPE for create (+)');
'to CREDIT_RATINGS_REC_TYPE for update(+)');
'to CREDIT_RATINGS_REC_TYPE for update(+)',
p_msg_level=>fnd_log.level_procedure);
'to CREDIT_RATINGS_REC_TYPE for update(-)');
'to CREDIT_RATINGS_REC_TYPE for update(-)',
p_msg_level=>fnd_log.level_procedure);
| update_party_search |
| |
| DESCRIPTION |
| This procedure updates the address_text column of |
| hz_cust_acct_sites_all with the NULL value |
| only to change the address_text column status |
| so that interMedia index can be created on it to perform text searches. |
| |
| NOTE :- After Calling this procedure the user has to execute the |
| Customer Text Data Creation concurrent program to see the changes. |
| |
| PARAMETERS |
| INPUT |
| p_party_id |
| p_old_party_name |
| p_new_party_name |
| p_old_tax_reference |
| p_new_tax_reference |
| |
| OUTPUT |
| |
| |
| HISTORY |
| 15-Mar-2004 Ramesh Ch Created |
*----------------------------------------------------------------------------*/
PROCEDURE update_party_search(p_party_id IN NUMBER,
p_old_party_name IN VARCHAR2,
p_new_party_name IN VARCHAR2,
p_old_tax_reference IN VARCHAR2,
p_new_tax_reference IN VARCHAR2)
IS
CURSOR c_cust_acct_sites(p_party_id NUMBER) IS
SELECT ac.CUST_ACCT_SITE_ID
FROM HZ_PARTIES p, HZ_CUST_ACCOUNTS c,
HZ_CUST_ACCT_SITES_ALL ac
WHERE p.party_id=p_party_id
AND p.party_id = c.party_id
AND c.cust_account_id = ac.cust_account_id;
SAVEPOINT update_party_search;
update HZ_CUST_ACCT_SITES_ALL set address_text=NULL where cust_acct_site_id=l_siteidtab(i);
ROLLBACK TO update_party_search;
| update_rel_person_search |
| |
| DESCRIPTION |
| This procedure updates the address_text column of |
| hz_cust_acct_sites_all with the NULL value |
| only to change the address_text column status |
| so that interMedia index can be created on it to perform text searches. |
| |
| NOTE :- After Calling this procedure the user has to execute the |
| Customer Text Data Creation concurrent program to see the changes. |
| |
| PARAMETERS |
| INPUT |
| p_old_person_rec |
| p_new_person_rec |
| |
| OUTPUT |
| |
| |
| HISTORY |
| 15-Mar-2004 Ramesh Ch Created |
*----------------------------------------------------------------------------*/
PROCEDURE update_rel_person_search(p_old_person_rec IN HZ_PARTY_V2PUB.PERSON_REC_TYPE,
p_new_person_rec IN HZ_PARTY_V2PUB.PERSON_REC_TYPE)
IS
---(Party level relationship )
CURSOR c_party_cust_acct_sites(p_party_id NUMBER) IS
SELECT distinct ac.CUST_ACCT_SITE_ID
FROM HZ_PARTIES p, HZ_CUST_ACCOUNT_ROLES ar,
HZ_RELATIONSHIPS rel,HZ_CUST_ACCT_SITES_ALL ac
WHERE rel.subject_id=p_party_id
AND ar.ROLE_TYPE = 'CONTACT'
AND rel.party_id=ar.party_id
AND rel.subject_id=p.party_id
AND ar.cust_account_id = ac.cust_account_id
AND (ar.cust_acct_site_id is null);
SELECT distinct ac.CUST_ACCT_SITE_ID
FROM HZ_PARTIES p, HZ_CUST_ACCOUNT_ROLES ar,
HZ_RELATIONSHIPS rel,HZ_CUST_ACCT_SITES_ALL ac
WHERE rel.subject_id=p_party_id
AND ar.ROLE_TYPE = 'CONTACT'
AND ar.party_id = rel.party_id
AND p.party_id = rel.subject_id
AND ar.cust_account_id = ac.cust_account_id
AND ar.cust_acct_site_id = ac.cust_acct_site_id;
SAVEPOINT update_rel_person_search;
update HZ_CUST_ACCT_SITES_ALL set address_text=NULL where cust_acct_site_id=l_siteidtab(i);
update HZ_CUST_ACCT_SITES_ALL set address_text=NULL where cust_acct_site_id=l_siteidtab(i);
ROLLBACK TO update_rel_person_search;
SELECT null
FROM hz_party_usg_assignments pu
WHERE pu.party_id = p_party_id
AND pu.party_usage_code = c_supplier_code
AND ROWNUM = 1;