[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
* This function need not be called for non-profile entities as the concept of select/de-select
* datasource is obsoleted for non-profile entities.
*
* For profile entities, DQM sync considers SST record only.
* Thus this function is not required at all.
*
FUNCTION sst_rules_passed (
p_owner_table_name IN VARCHAR2,
p_owner_table_id IN NUMBER
) RETURN BOOLEAN
IS
g_entity_attr_id NUMBER ;
g_selected_datasources VARCHAR2(1000) ;
g_is_datasource_selected VARCHAR2(1);
p_selected_datasources => g_selected_datasources );
( select b.column_name as col_name
from fnd_tables a, fnd_columns b, fnd_primary_key_columns c
where a.table_name = p_owner_table_name
and a.table_id = b.table_id
and b.column_id = c.column_id
)
LOOP
counter := counter + 1 ;
p_sql_str := 'select actual_content_source from '
|| p_owner_table_name || ' where ' || prim_key_column || ' = ' || p_owner_table_id ;
g_is_datasource_selected :=
HZ_MIXNM_UTILITY.isDataSourceSelected (
p_selected_datasources => g_selected_datasources ,
p_actual_content_source => db_actual_content_source);
IF g_mixnmatch_enabled = 'Y' and g_is_datasource_selected = 'Y'
THEN
RETURN TRUE ;
select count(*)
from hz_orig_sys_references
where Owner_table_name = p_owner_table_name
and owner_table_id = p_owner_table_id
and status = 'A';
select count(*)
from hz_orig_sys_references os,hz_org_contacts org
where os.owner_table_id = org.org_contact_id
and os.owner_table_name = p_owner_table_name
and org.org_contact_id = p_owner_table_id
and os.status = 'A';
HZ_ORIG_SYSTEM_REF_PKG.Select_Row (
x_orig_system_ref_id => l_orig_system_ref_id,
x_orig_system => x_orig_sys_reference_rec.orig_system,
x_orig_system_reference => x_orig_sys_reference_rec.orig_system_reference,
x_owner_table_name => x_orig_sys_reference_rec.owner_table_name,
x_owner_table_id => x_orig_sys_reference_rec.owner_table_id,
--raji
x_party_id => x_orig_sys_reference_rec.party_id,
x_status => x_orig_sys_reference_rec.status,
x_reason_code => x_orig_sys_reference_rec.reason_code,
x_old_orig_system_reference => x_orig_sys_reference_rec.old_orig_system_reference,
x_start_date_active => x_orig_sys_reference_rec.start_date_active,
x_end_date_active => x_orig_sys_reference_rec.end_date_active,
x_object_version_number => l_object_version_number,
x_created_by_module => x_orig_sys_reference_rec.created_by_module,
x_application_id => x_orig_sys_reference_rec.application_id,
x_attribute_category => x_orig_sys_reference_rec.attribute_category,
x_attribute1 => x_orig_sys_reference_rec.attribute1,
x_attribute2 => x_orig_sys_reference_rec.attribute2,
x_attribute3 => x_orig_sys_reference_rec.attribute3,
x_attribute4 => x_orig_sys_reference_rec.attribute4,
x_attribute5 => x_orig_sys_reference_rec.attribute5,
x_attribute6 => x_orig_sys_reference_rec.attribute6,
x_attribute7 => x_orig_sys_reference_rec.attribute7,
x_attribute8 => x_orig_sys_reference_rec.attribute8,
x_attribute9 => x_orig_sys_reference_rec.attribute9,
x_attribute10 => x_orig_sys_reference_rec.attribute10,
x_attribute11 => x_orig_sys_reference_rec.attribute11,
x_attribute12 => x_orig_sys_reference_rec.attribute12,
x_attribute13 => x_orig_sys_reference_rec.attribute13,
x_attribute14 => x_orig_sys_reference_rec.attribute14,
x_attribute15 => x_orig_sys_reference_rec.attribute15,
x_attribute16 => x_orig_sys_reference_rec.attribute16,
x_attribute17 => x_orig_sys_reference_rec.attribute17,
x_attribute18 => x_orig_sys_reference_rec.attribute18,
x_attribute19 => x_orig_sys_reference_rec.attribute19,
x_attribute20 => x_orig_sys_reference_rec.attribute20
);
HZ_ORIG_SYS_ENTITY_MAP_PKG.Insert_Row (
x_orig_system => p_orig_sys_entity_map_rec.orig_system,
x_owner_table_name => p_orig_sys_entity_map_rec.owner_table_name,
x_status => p_orig_sys_entity_map_rec.status,
x_multiple_flag => p_orig_sys_entity_map_rec.multiple_flag,
--raji
x_multi_osr_flag => p_orig_sys_entity_map_rec.multi_osr_flag,
x_object_version_number => 1,
x_created_by_module => p_orig_sys_entity_map_rec.created_by_module,
x_application_id => p_orig_sys_entity_map_rec.application_id,
x_attribute_category => p_orig_sys_entity_map_rec.attribute_category,
x_attribute1 => p_orig_sys_entity_map_rec.attribute1,
x_attribute2 => p_orig_sys_entity_map_rec.attribute2,
x_attribute3 => p_orig_sys_entity_map_rec.attribute3,
x_attribute4 => p_orig_sys_entity_map_rec.attribute4,
x_attribute5 => p_orig_sys_entity_map_rec.attribute5,
x_attribute6 => p_orig_sys_entity_map_rec.attribute6,
x_attribute7 => p_orig_sys_entity_map_rec.attribute7,
x_attribute8 => p_orig_sys_entity_map_rec.attribute8,
x_attribute9 => p_orig_sys_entity_map_rec.attribute9,
x_attribute10 => p_orig_sys_entity_map_rec.attribute10,
x_attribute11 => p_orig_sys_entity_map_rec.attribute11,
x_attribute12 => p_orig_sys_entity_map_rec.attribute12,
x_attribute13 => p_orig_sys_entity_map_rec.attribute13,
x_attribute14 => p_orig_sys_entity_map_rec.attribute14,
x_attribute15 => p_orig_sys_entity_map_rec.attribute15,
x_attribute16 => p_orig_sys_entity_map_rec.attribute16,
x_attribute17 => p_orig_sys_entity_map_rec.attribute17,
x_attribute18 => p_orig_sys_entity_map_rec.attribute18,
x_attribute19 => p_orig_sys_entity_map_rec.attribute19,
x_attribute20 => p_orig_sys_entity_map_rec.attribute20
);
PROCEDURE do_update_orig_sys_entity_map(
p_orig_sys_entity_map_rec IN OUT NOCOPY HZ_ORIG_SYSTEM_REF_PUB.ORIG_SYS_ENTITY_MAP_REC_TYPE,
p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
p_object_version_number IN OUT NOCOPY NUMBER,
x_return_status IN OUT NOCOPY VARCHAR2
) is
l_object_version_number NUMBER;
SELECT OBJECT_VERSION_NUMBER
INTO l_object_version_number
FROM HZ_ORIG_SYS_MAPPING
WHERE ORIG_SYSTEM = p_orig_sys_entity_map_rec.orig_system
and owner_table_name = p_orig_sys_entity_map_rec.owner_table_name
FOR UPDATE OF ORIG_SYSTEM NOWAIT;
HZ_ORIG_SYS_ENTITY_MAP_PKG.Update_Row (
x_orig_system => p_orig_sys_entity_map_rec.orig_system,
x_owner_table_name => p_orig_sys_entity_map_rec.owner_table_name,
x_status => p_orig_sys_entity_map_rec.status,
x_multiple_flag => p_orig_sys_entity_map_rec.multiple_flag,
--raji
x_multi_osr_flag => p_orig_sys_entity_map_rec.multi_osr_flag,
x_object_version_number => l_object_version_number,
x_created_by_module => p_orig_sys_entity_map_rec.created_by_module,
x_application_id => p_orig_sys_entity_map_rec.application_id,
x_attribute_category => p_orig_sys_entity_map_rec.attribute_category,
x_attribute1 => p_orig_sys_entity_map_rec.attribute1,
x_attribute2 => p_orig_sys_entity_map_rec.attribute2,
x_attribute3 => p_orig_sys_entity_map_rec.attribute3,
x_attribute4 => p_orig_sys_entity_map_rec.attribute4,
x_attribute5 => p_orig_sys_entity_map_rec.attribute5,
x_attribute6 => p_orig_sys_entity_map_rec.attribute6,
x_attribute7 => p_orig_sys_entity_map_rec.attribute7,
x_attribute8 => p_orig_sys_entity_map_rec.attribute8,
x_attribute9 => p_orig_sys_entity_map_rec.attribute9,
x_attribute10 => p_orig_sys_entity_map_rec.attribute10,
x_attribute11 => p_orig_sys_entity_map_rec.attribute11,
x_attribute12 => p_orig_sys_entity_map_rec.attribute12,
x_attribute13 => p_orig_sys_entity_map_rec.attribute13,
x_attribute14 => p_orig_sys_entity_map_rec.attribute14,
x_attribute15 => p_orig_sys_entity_map_rec.attribute15,
x_attribute16 => p_orig_sys_entity_map_rec.attribute16,
x_attribute17 => p_orig_sys_entity_map_rec.attribute17,
x_attribute18 => p_orig_sys_entity_map_rec.attribute18,
x_attribute19 => p_orig_sys_entity_map_rec.attribute19,
x_attribute20 => p_orig_sys_entity_map_rec.attribute20
);
end do_update_orig_sys_entity_map;
PROCEDURE update_orig_sys_entity_mapping(
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
p_orig_sys_entity_map_rec IN HZ_ORIG_SYSTEM_REF_PUB.ORIG_SYS_ENTITY_MAP_REC_TYPE,
p_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_orig_sys_entity_map_rec HZ_ORIG_SYSTEM_REF_PUB.ORIG_SYS_ENTITY_MAP_REC_TYPE := p_orig_sys_entity_map_rec;
SAVEPOINT update_orig_sys_entity_mapping;
do_update_orig_sys_entity_map(
l_orig_sys_entity_map_rec,
p_validation_level,
p_object_version_number,
x_return_status );
ROLLBACK TO update_orig_sys_entity_mapping;
ROLLBACK TO update_orig_sys_entity_mapping;
ROLLBACK TO update_orig_sys_entity_mapping;
end update_orig_sys_entity_mapping;
HZ_ORIG_SYSTEM_REF_PKG.Insert_Row (
x_orig_system_ref_id => p_orig_sys_reference_rec.orig_system_ref_id,
x_orig_system => p_orig_sys_reference_rec.orig_system,
x_orig_system_reference => p_orig_sys_reference_rec.orig_system_reference,
x_owner_table_name => p_orig_sys_reference_rec.owner_table_name,
x_owner_table_id => p_orig_sys_reference_rec.owner_table_id,
--raji
x_party_id => p_orig_sys_reference_rec.party_id,
x_status => p_orig_sys_reference_rec.status,
x_reason_code => p_orig_sys_reference_rec.reason_code,
x_old_orig_system_reference => p_orig_sys_reference_rec.old_orig_system_reference,
x_start_date_active => p_orig_sys_reference_rec.start_date_active,
x_end_date_active => p_orig_sys_reference_rec.end_date_active,
x_object_version_number => 1,
x_created_by_module => p_orig_sys_reference_rec.created_by_module,
x_application_id => p_orig_sys_reference_rec.application_id,
x_attribute_category => p_orig_sys_reference_rec.attribute_category,
x_attribute1 => p_orig_sys_reference_rec.attribute1,
x_attribute2 => p_orig_sys_reference_rec.attribute2,
x_attribute3 => p_orig_sys_reference_rec.attribute3,
x_attribute4 => p_orig_sys_reference_rec.attribute4,
x_attribute5 => p_orig_sys_reference_rec.attribute5,
x_attribute6 => p_orig_sys_reference_rec.attribute6,
x_attribute7 => p_orig_sys_reference_rec.attribute7,
x_attribute8 => p_orig_sys_reference_rec.attribute8,
x_attribute9 => p_orig_sys_reference_rec.attribute9,
x_attribute10 => p_orig_sys_reference_rec.attribute10,
x_attribute11 => p_orig_sys_reference_rec.attribute11,
x_attribute12 => p_orig_sys_reference_rec.attribute12,
x_attribute13 => p_orig_sys_reference_rec.attribute13,
x_attribute14 => p_orig_sys_reference_rec.attribute14,
x_attribute15 => p_orig_sys_reference_rec.attribute15,
x_attribute16 => p_orig_sys_reference_rec.attribute16,
x_attribute17 => p_orig_sys_reference_rec.attribute17,
x_attribute18 => p_orig_sys_reference_rec.attribute18,
x_attribute19 => p_orig_sys_reference_rec.attribute19,
x_attribute20 => p_orig_sys_reference_rec.attribute20
);
select party_type into l_dummy
from hz_parties
where party_id = p_orig_sys_reference_rec.owner_table_id ;
SELECT status INTO l_status FROM hz_party_sites WHERE party_site_id=p_orig_sys_reference_rec.owner_table_id ;
SELECT status INTO l_status FROM HZ_ORG_CONTACTS WHERE org_contact_id=p_orig_sys_reference_rec.owner_table_id;
SELECT status INTO l_status FROM hz_contact_points WHERE contact_point_id=p_orig_sys_reference_rec.owner_table_id;
SELECT ORIG_SYSTEM_REF_ID
FROM HZ_ORIG_SYS_REFERENCES
WHERE ORIG_SYSTEM = p_orig_system
and ORIG_SYSTEM_REFERENCE = p_orig_system_reference
and owner_table_name = p_owner_table_name
and status = 'A';
SELECT start_date_active
FROM HZ_ORIG_SYS_REFERENCES
WHERE ORIG_SYSTEM = p_orig_system
and ORIG_SYSTEM_REFERENCE = p_orig_system_reference
and owner_table_name = p_owner_table_name
and rownum = 1; -- start/end_date_active only used in update and
PROCEDURE do_update_orig_sys_reference(
p_orig_sys_reference_rec IN OUT NOCOPY HZ_ORIG_SYSTEM_REF_PUB.ORIG_SYS_REFERENCE_REC_TYPE,
p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
p_object_version_number IN OUT NOCOPY NUMBER,
x_return_status IN OUT NOCOPY VARCHAR2
) is
cursor get_pk_by_owner_id is
SELECT ORIG_SYSTEM_REF_ID
FROM HZ_ORIG_SYS_REFERENCES
WHERE ORIG_SYSTEM = p_orig_sys_reference_rec.orig_system
and ORIG_SYSTEM_REFERENCE = p_orig_sys_reference_rec.orig_system_reference
and owner_table_name = p_orig_sys_reference_rec.owner_table_name
and owner_table_id = p_orig_sys_reference_rec.owner_table_id
and status = 'A';
FND_MESSAGE.SET_NAME('AR', 'HZ_MOSR_CANNOT_UPDATE');
SELECT OBJECT_VERSION_NUMBER,
ORIG_SYSTEM,
ORIG_SYSTEM_REFERENCE
INTO l_object_version_number,
l_orig_system,
l_orig_system_reference
FROM HZ_ORIG_SYS_REFERENCES
WHERE orig_system_ref_id = l_orig_system_ref_id
FOR UPDATE OF ORIG_SYSTEM NOWAIT;
FND_MESSAGE.SET_NAME('AR', 'HZ_SSM_NO_UPDATE_PUR');
HZ_ORIG_SYSTEM_REF_PKG.Update_Row (
x_orig_system_ref_id => l_orig_system_ref_id,
x_orig_system => p_orig_sys_reference_rec.orig_system,
x_orig_system_reference => p_orig_sys_reference_rec.orig_system_reference,
x_owner_table_name => p_orig_sys_reference_rec.owner_table_name,
x_owner_table_id => p_orig_sys_reference_rec.owner_table_id,
--raji
x_party_id => p_orig_sys_reference_rec.party_id,
x_status => p_orig_sys_reference_rec.status,
x_reason_code => p_orig_sys_reference_rec.reason_code,
x_old_orig_system_reference => p_orig_sys_reference_rec.old_orig_system_reference,
x_start_date_active => p_orig_sys_reference_rec.start_date_active,
x_end_date_active => p_orig_sys_reference_rec.end_date_active,
x_object_version_number => p_object_version_number,
x_created_by_module => p_orig_sys_reference_rec.created_by_module,
x_application_id => p_orig_sys_reference_rec.application_id,
x_attribute_category => p_orig_sys_reference_rec.attribute_category,
x_attribute1 => p_orig_sys_reference_rec.attribute1,
x_attribute2 => p_orig_sys_reference_rec.attribute2,
x_attribute3 => p_orig_sys_reference_rec.attribute3,
x_attribute4 => p_orig_sys_reference_rec.attribute4,
x_attribute5 => p_orig_sys_reference_rec.attribute5,
x_attribute6 => p_orig_sys_reference_rec.attribute6,
x_attribute7 => p_orig_sys_reference_rec.attribute7,
x_attribute8 => p_orig_sys_reference_rec.attribute8,
x_attribute9 => p_orig_sys_reference_rec.attribute9,
x_attribute10 => p_orig_sys_reference_rec.attribute10,
x_attribute11 => p_orig_sys_reference_rec.attribute11,
x_attribute12 => p_orig_sys_reference_rec.attribute12,
x_attribute13 => p_orig_sys_reference_rec.attribute13,
x_attribute14 => p_orig_sys_reference_rec.attribute14,
x_attribute15 => p_orig_sys_reference_rec.attribute15,
x_attribute16 => p_orig_sys_reference_rec.attribute16,
x_attribute17 => p_orig_sys_reference_rec.attribute17,
x_attribute18 => p_orig_sys_reference_rec.attribute18,
x_attribute19 => p_orig_sys_reference_rec.attribute19,
x_attribute20 => p_orig_sys_reference_rec.attribute20
);
HZ_BUSINESS_EVENT_V2PVT.update_orig_system_ref_event(l_orig_sys_reference_rec, l_old_orig_sys_reference_rec);
select owner_table_id into l_temp
from hz_orig_sys_references
where orig_system_ref_id = l_orig_system_ref_id;
select party_type into l_dummy
from hz_parties
where party_id = l_temp ;
SELECT status INTO l_status FROM hz_party_sites WHERE party_site_id=l_temp;
SELECT status INTO l_status FROM HZ_ORG_CONTACTS WHERE org_contact_id=l_temp;
SELECT status INTO l_status FROM hz_contact_points WHERE contact_point_id=l_temp;
end do_update_orig_sys_reference;
select multi_osr_flag
from hz_orig_sys_mapping
where owner_table_name = l_orig_sys_reference_rec.owner_table_name
and orig_system = l_orig_sys_reference_rec.orig_system
/*and status ='A'*/;
select 'Y'
from hz_orig_sys_references
where owner_table_id = l_orig_sys_reference_rec.owner_table_id
and owner_table_name = l_orig_sys_reference_rec.owner_table_name
and orig_system = l_orig_sys_reference_rec.orig_system
and status = 'A';
do_update_orig_sys_reference(
l_orig_sys_reference_rec,
p_validation_level,
l_object_version_number,
x_return_status );
SELECT 'Y'
FROM HZ_ORIG_SYS_REFERENCES
WHERE ORIG_SYSTEM = p_orig_system
and ORIG_SYSTEM_REFERENCE = p_orig_system_reference
and owner_table_name = p_owner_table_name
and owner_table_id = p_owner_table_id
and status = 'I'
and rownum = 1;
PROCEDURE update_orig_system_reference(
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
p_orig_sys_reference_rec IN HZ_ORIG_SYSTEM_REF_PUB.ORIG_SYS_REFERENCE_REC_TYPE,
p_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_object_version_number number:= p_object_version_number;
select multi_osr_flag
from hz_orig_sys_mapping
where owner_table_name = l_orig_sys_reference_rec.owner_table_name
and orig_system = l_orig_sys_reference_rec.orig_system
/*and status='A'*/;
select 'Y'
from hz_orig_sys_references
where owner_table_id = l_orig_sys_reference_rec.owner_table_id
and owner_table_name = l_orig_sys_reference_rec.owner_table_name
and orig_system = l_orig_sys_reference_rec.orig_system
and status = 'A';
SAVEPOINT update_orig_sys_reference;
if l_inactive_mosr_exist = 'Y' and p_orig_sys_reference_rec.status = 'A' -- update inactive to active
then
l_orig_sys_reference_rec.orig_system_ref_id := null; -- need to get it from sequence
do_update_orig_sys_reference(
lc_orig_sys_reference_rec,
p_validation_level,
l_object_version_number,
x_return_status );
do_update_orig_sys_reference(
lc_orig_sys_reference_rec,
p_validation_level,
l_object_version_number,
x_return_status );
if l_inactive_mosr_exist = 'Y' and p_orig_sys_reference_rec.status = 'I' -- update active to inactive
then
l_orig_sys_reference_rec.old_orig_system_reference := null;
do_update_orig_sys_reference(
l_orig_sys_reference_rec,
p_validation_level,
l_object_version_number,
x_return_status );
do_update_orig_sys_reference(
l_orig_sys_reference_rec,
p_validation_level,
l_object_version_number,
x_return_status );
do_update_orig_sys_reference(
l_orig_sys_reference_rec,
p_validation_level,
l_object_version_number,
x_return_status );
ROLLBACK TO update_orig_sys_reference;
ROLLBACK TO update_orig_sys_reference;
ROLLBACK TO update_orig_sys_reference;
end update_orig_system_reference;
select orig_system, orig_system_reference,orig_system_ref_id
from hz_orig_sys_references
where owner_table_id = p_old_owner_table_id
and owner_table_name = p_owner_table_name
and status = 'A'; /* Bug 3235877 */
select multi_osr_flag
from hz_orig_sys_mapping
where owner_table_name = p_owner_table_name
and orig_system = (select orig_system from hz_orig_sys_references
where owner_table_id = p_old_owner_table_id
and owner_table_name = p_owner_table_name
and status ='A'
and rownum =1
);
select 'Y'
from hz_orig_sys_references
where owner_table_id = p_new_owner_table_id
and owner_table_name = p_owner_table_name
-- Bug 3863486
and orig_system = (select orig_system from hz_orig_sys_references
where owner_table_id = p_old_owner_table_id
and owner_table_name = p_owner_table_name
and status ='A'
and rownum =1)
and status = 'A';
select 'Y'
from hz_orig_sys_references
where owner_table_id = p_new_owner_table_id
and owner_table_name = p_owner_table_name
and orig_system || orig_system_reference = l_orig_system||l_orig_system_reference
and status = 'A';
update_orig_system_reference(
FND_API.G_FALSE,
p_validation_level,
l_orig_sys_reference_rec,
l_object_version_number,
x_return_status,
x_msg_count,
x_msg_data);
update_orig_system_reference(
FND_API.G_FALSE,
p_validation_level,
l_orig_sys_reference_rec,
l_object_version_number,
x_return_status,
x_msg_count,
x_msg_data);
select orig_system, orig_system_reference, created_by_module
from hz_orig_sys_references
WHERE owner_table_name = p_owner_table_name
and owner_table_id = p_owner_table_id
and status = 'A';
select multi_osr_flag
from hz_orig_sys_mapping
where owner_table_name = p_owner_table_name
and orig_system = (select orig_system from hz_orig_sys_references
where owner_table_id = p_owner_table_id
and owner_table_name = p_owner_table_name
and status = 'A'
and rownum=1
);
select party_id
from hz_party_sites
where party_site_id = p_owner_table_id;
select party_id
from hz_cust_accounts
where cust_account_id = p_owner_table_id;
select cust.party_id
from hz_cust_accounts cust,hz_cust_acct_sites sites
where sites.cust_acct_site_id = p_owner_table_id
and cust.cust_account_id = sites.cust_account_id;
select cust.party_id
from hz_cust_accounts cust,hz_cust_acct_sites sites,hz_cust_site_uses uses
where uses.site_use_id = p_owner_table_id
and uses.cust_acct_site_id = sites.cust_acct_site_id
and sites.cust_account_id = cust.cust_account_id;
select party.party_id
from hz_parties party,hz_contact_points cont
where cont.contact_point_id = p_owner_table_id
and cont.owner_table_id = party.party_id
--and cont.status = 'A' -- Bug 3196803 : Removed the check of status = 'A'
union
select psite.party_site_id
from hz_party_sites psite,hz_contact_points cont
where cont.contact_point_id = p_owner_table_id
and cont.owner_table_id = psite.party_site_id;
select rel.object_id
from hz_org_contacts org ,hz_relationships rel
where org.org_contact_id = p_owner_table_id
and org.party_relationship_id = rel.relationship_id
and rel.directional_flag = 'F';
select rel.object_id
from hz_org_contact_roles roles,hz_org_contacts org,hz_relationships rel
where roles.org_contact_role_id = p_owner_table_id
and roles.org_contact_id = org.org_contact_id
and org.party_relationship_id = rel.relationship_id
and rel.directional_flag = 'F';
select cust.party_id
from hz_cust_account_roles role,hz_cust_accounts cust
where role.cust_account_role_id = p_owner_table_id
and role.cust_account_id = cust.cust_account_id;
SELECT ENTITY_NAME,
ATTRIBUTE_NAME
FROM HZ_ENTITY_ATTRIBUTES;
HZ_ORIG_SYSTEMS_PKG.Insert_Row (
x_orig_system_id => p_orig_sys_rec.orig_system_id,
x_orig_system => p_orig_sys_rec.orig_system,
x_orig_system_name => p_orig_sys_rec.orig_system_name,
x_description => p_orig_sys_rec.description,
x_orig_system_type => p_orig_sys_rec.orig_system_type,
x_sst_flag => p_orig_sys_rec.sst_flag,
x_status => p_orig_sys_rec.status,
x_object_version_number => 1,
x_created_by_module => p_orig_sys_rec.created_by_module,
x_attribute_category => p_orig_sys_rec.attribute_category,
x_attribute1 => p_orig_sys_rec.attribute1,
x_attribute2 => p_orig_sys_rec.attribute2,
x_attribute3 => p_orig_sys_rec.attribute3,
x_attribute4 => p_orig_sys_rec.attribute4,
x_attribute5 => p_orig_sys_rec.attribute5,
x_attribute6 => p_orig_sys_rec.attribute6,
x_attribute7 => p_orig_sys_rec.attribute7,
x_attribute8 => p_orig_sys_rec.attribute8,
x_attribute9 => p_orig_sys_rec.attribute9,
x_attribute10 => p_orig_sys_rec.attribute10,
x_attribute11 => p_orig_sys_rec.attribute11,
x_attribute12 => p_orig_sys_rec.attribute12,
x_attribute13 => p_orig_sys_rec.attribute13,
x_attribute14 => p_orig_sys_rec.attribute14,
x_attribute15 => p_orig_sys_rec.attribute15,
x_attribute16 => p_orig_sys_rec.attribute16,
x_attribute17 => p_orig_sys_rec.attribute17,
x_attribute18 => p_orig_sys_rec.attribute18,
x_attribute19 => p_orig_sys_rec.attribute19,
x_attribute20 => p_orig_sys_rec.attribute20
);
/* Create records in HZ_SELECT_DATA_SOURCES for this orig_system and all entities + attributes */
IF p_orig_sys_rec.sst_flag = 'Y' THEN
OPEN c_data_sources;
PROCEDURE do_update_orig_system(
p_orig_sys_rec IN OUT NOCOPY HZ_ORIG_SYSTEM_REF_PVT.ORIG_SYS_REC_TYPE,
p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
p_object_version_number IN OUT NOCOPY NUMBER,
x_return_status IN OUT NOCOPY VARCHAR2
) is
l_object_version_number NUMBER;
SELECT ENTITY_NAME,
ATTRIBUTE_NAME
FROM HZ_ENTITY_ATTRIBUTES;
SELECT OBJECT_VERSION_NUMBER, SST_FLAG, ORIG_SYSTEM
INTO l_object_version_number, l_sst_flag, l_orig_system
FROM HZ_ORIG_SYSTEMS_B
WHERE orig_system_id = p_orig_sys_rec.orig_system_id
FOR UPDATE OF ORIG_SYSTEM NOWAIT;
HZ_ORIG_SYSTEMS_PKG.Update_Row (
x_orig_system_id => p_orig_sys_rec.orig_system_id,
x_orig_system => p_orig_sys_rec.orig_system,
x_orig_system_name => p_orig_sys_rec.orig_system_name,
x_description => p_orig_sys_rec.description,
x_orig_system_type => p_orig_sys_rec.orig_system_type,
x_sst_flag => p_orig_sys_rec.sst_flag,
x_status => p_orig_sys_rec.status,
x_object_version_number => p_object_version_number,
x_created_by_module => p_orig_sys_rec.created_by_module,
x_attribute_category => p_orig_sys_rec.attribute_category,
x_attribute1 => p_orig_sys_rec.attribute1,
x_attribute2 => p_orig_sys_rec.attribute2,
x_attribute3 => p_orig_sys_rec.attribute3,
x_attribute4 => p_orig_sys_rec.attribute4,
x_attribute5 => p_orig_sys_rec.attribute5,
x_attribute6 => p_orig_sys_rec.attribute6,
x_attribute7 => p_orig_sys_rec.attribute7,
x_attribute8 => p_orig_sys_rec.attribute8,
x_attribute9 => p_orig_sys_rec.attribute9,
x_attribute10 => p_orig_sys_rec.attribute10,
x_attribute11 => p_orig_sys_rec.attribute11,
x_attribute12 => p_orig_sys_rec.attribute12,
x_attribute13 => p_orig_sys_rec.attribute13,
x_attribute14 => p_orig_sys_rec.attribute14,
x_attribute15 => p_orig_sys_rec.attribute15,
x_attribute16 => p_orig_sys_rec.attribute16,
x_attribute17 => p_orig_sys_rec.attribute17,
x_attribute18 => p_orig_sys_rec.attribute18,
x_attribute19 => p_orig_sys_rec.attribute19,
x_attribute20 => p_orig_sys_rec.attribute20
);
end do_update_orig_system;
PROCEDURE update_orig_system(
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
p_orig_sys_rec IN ORIG_SYS_REC_TYPE,
p_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_orig_sys_rec ORIG_SYS_REC_TYPE := p_orig_sys_rec;
SAVEPOINT update_orig_system;
do_update_orig_system(
l_orig_sys_rec,
p_validation_level,
l_object_version_number,
x_return_status );
ROLLBACK TO update_orig_system;
ROLLBACK TO update_orig_system;
ROLLBACK TO update_orig_system;
end update_orig_system;