[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select 'Y'
from hz_orig_sys_mapping
where orig_system = p_orig_system
and owner_table_name = p_owner_table_name/*
and status = nvl(p_status,status)*/;
SELECT count(*)
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 'Y'
from hz_orig_sys_references
where orig_system = p_orig_system
and orig_system_reference = p_orig_system_ref
and owner_table_name = p_owner_table_name
and status = 'A'
and rownum = 1;
select 'Y'
from hz_orig_sys_references
where orig_system = p_orig_system
and orig_system_reference = p_orig_system_ref
and owner_table_name = p_owner_table_name
and rownum = 1; -- allow update case: update status from 'I' to 'A'
p_create_update_flag IN VARCHAR2,
p_orig_sys_entity_map_rec IN HZ_ORIG_SYSTEM_REF_PUB.ORIG_SYS_ENTITY_MAP_REC_TYPE,
x_return_status IN OUT NOCOPY VARCHAR2
) is
-- Added multi_osr_flag in cursor get_orig_sys_entity_map_csr
cursor get_orig_sys_entity_map_csr is
select multiple_flag, multi_osr_flag,created_by_module, application_id
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
/*and status = 'A'*/;
select 'Y'
from hz_orig_sys_mapping
where created_by = 1
and orig_system = p_orig_sys_entity_map_rec.orig_system
and owner_table_name = p_orig_sys_entity_map_rec.owner_table_name
/*and status = 'A'*/
and rownum=1;
select 'Y'
from hz_orig_sys_references
where orig_system = p_orig_sys_entity_map_rec.orig_system
and owner_table_name = p_orig_sys_entity_map_rec.owner_table_name
-- Bug 4956761 : Corrected to get result if there are multiple MOSR for single entity
and status = 'A'
group by owner_table_id
having count(1) > 1;
select 'Y'
from hz_orig_systems_b
where orig_system = p_orig_sys_entity_map_rec.orig_system
/* and status= 'A'*/;
if p_create_update_flag = 'C'
then
FND_MESSAGE.SET_NAME('AR', 'HZ_API_DUPLICATE_COLUMN');
if p_create_update_flag = 'U'
then
FND_MESSAGE.SET_NAME('AR', 'HZ_API_NO_DATA_FOUND');
IF p_create_update_flag = 'C' THEN
If p_orig_sys_entity_map_rec.owner_table_name in ('HZ_CUST_ACCT_SITES_ALL',
'HZ_CUST_ACCOUNT_ROLES', 'HZ_CUST_SITE_USES_ALL')
then
if p_orig_sys_entity_map_rec.multiple_flag = 'N'
then
fnd_message.set_name('AR', 'HZ_API_VAL_DEP_FIELDS');
IF p_create_update_flag = 'U'
THEN
open get_orig_sys_entity_map_csr;
IF p_create_update_flag = 'U' AND
p_orig_sys_entity_map_rec.multiple_flag IS NOT NULL
THEN
HZ_UTILITY_V2PUB.validate_nonupdateable (
p_column => 'multiple_flag',
p_column_value => p_orig_sys_entity_map_rec.multiple_flag,
p_old_column_value => l_multiple_flag,
p_restricted => 'N',
x_return_status => x_return_status );
IF p_create_update_flag = 'U' AND
p_orig_sys_entity_map_rec.multi_osr_flag IS NOT NULL
THEN
IF (p_orig_sys_entity_map_rec.multi_osr_flag = 'N' and
nvl(l_multi_osr_flag,'Y') = 'Y' )
THEN
OPEN mosr_rec_exists;
p_create_update_flag => p_create_update_flag,
p_created_by_module => p_orig_sys_entity_map_rec.created_by_module,
p_old_created_by_module => l_created_by_module,
x_return_status => x_return_status);
p_create_update_flag => p_create_update_flag,
p_application_id => p_orig_sys_entity_map_rec.application_id,
p_old_application_id => l_application_id,
x_return_status => x_return_status);
select multiple_flag
from hz_orig_sys_mapping
where orig_system = p_orig_system
and owner_table_name = p_owner_table_name
/* and status = 'A'*/;
p_create_update_flag IN VARCHAR2,
p_orig_sys_reference_rec IN HZ_ORIG_SYSTEM_REF_PUB.ORIG_SYS_REFERENCE_REC_TYPE,
x_return_status IN OUT NOCOPY VARCHAR2
) is
cursor get_orig_sys_reference_csr is
select start_date_active,end_date_active
from hz_orig_sys_references
where orig_system = p_orig_sys_reference_rec.orig_system
and orig_system_reference =nvl(p_orig_sys_reference_rec.old_orig_system_reference,
p_orig_sys_reference_rec.orig_system_reference)
and owner_table_name = p_orig_sys_reference_rec.owner_table_name
and rownum = 1; -- start/end_date_active only used in update and
select 'Y'
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';
cursor get_nonupdateable_columns1 is
select created_by_module, application_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';
cursor get_nonupdateable_columns2 is
select created_by_module, application_id
from hz_orig_sys_references
where orig_system_ref_id = p_orig_sys_reference_rec.orig_system_ref_id;
SELECT 'Y'
FROM HZ_ORIG_SYSTEMS_B
WHERE orig_system = p_orig_sys_reference_rec.orig_system
AND status = 'A';
IF p_create_update_flag = 'C'
and
(p_orig_sys_reference_rec.orig_system_reference is null or
p_orig_sys_reference_rec.orig_system_reference = fnd_api.g_miss_char)
THEN
HZ_UTILITY_V2PUB.validate_mandatory (
p_create_update_flag => p_create_update_flag,
p_column => 'orig_system_reference',
p_column_value => p_orig_sys_reference_rec.orig_system_reference,
x_return_status => x_return_status );
IF p_create_update_flag = 'C'
and
(p_orig_sys_reference_rec.owner_table_id is null or
p_orig_sys_reference_rec.owner_table_id = fnd_api.g_miss_num)
THEN
HZ_UTILITY_V2PUB.validate_mandatory (
p_create_update_flag => p_create_update_flag,
p_column => 'owner_table_id',
p_column_value => p_orig_sys_reference_rec.owner_table_id,
x_return_status => x_return_status );
IF p_create_update_flag = 'C'
AND p_orig_sys_reference_rec.owner_table_id IS NOT NULL
AND p_orig_sys_reference_rec.owner_table_id <> fnd_api.g_miss_num
AND p_orig_sys_reference_rec.owner_table_id <> -1
THEN
BEGIN
SELECT 'Y'
INTO l_dummy
FROM HZ_PARTIES
WHERE PARTY_ID = p_orig_sys_reference_rec.owner_table_id;
IF p_create_update_flag = 'C'
AND p_orig_sys_reference_rec.owner_table_id IS NOT NULL
AND p_orig_sys_reference_rec.owner_table_id <> fnd_api.g_miss_num
AND p_orig_sys_reference_rec.owner_table_id <> -1
THEN
BEGIN
SELECT 'Y'
INTO l_dummy
FROM HZ_PARTY_SITES
WHERE party_site_id = p_orig_sys_reference_rec.owner_table_id;
IF p_create_update_flag = 'C'
AND p_orig_sys_reference_rec.owner_table_id IS NOT NULL
AND p_orig_sys_reference_rec.owner_table_id <> fnd_api.g_miss_num
AND p_orig_sys_reference_rec.owner_table_id <> -1
THEN
BEGIN
SELECT 'Y'
INTO l_dummy
FROM HZ_CONTACT_POINTS
WHERE contact_point_id = p_orig_sys_reference_rec.owner_table_id;
IF p_create_update_flag = 'C'
AND p_orig_sys_reference_rec.owner_table_id IS NOT NULL
AND p_orig_sys_reference_rec.owner_table_id <> fnd_api.g_miss_num
AND p_orig_sys_reference_rec.owner_table_id <> -1
THEN
BEGIN
SELECT 'Y'
INTO l_dummy
FROM HZ_CUST_ACCOUNTS
WHERE cust_account_id = p_orig_sys_reference_rec.owner_table_id;
IF p_create_update_flag = 'C'
AND p_orig_sys_reference_rec.owner_table_id IS NOT NULL
AND p_orig_sys_reference_rec.owner_table_id <> fnd_api.g_miss_num
AND p_orig_sys_reference_rec.owner_table_id <> -1
THEN
BEGIN
SELECT 'Y'
INTO l_dummy
FROM HZ_CUST_ACCOUNT_ROLES
WHERE cust_account_role_id = p_orig_sys_reference_rec.owner_table_id;
IF p_create_update_flag = 'C'
AND p_orig_sys_reference_rec.owner_table_id IS NOT NULL
AND p_orig_sys_reference_rec.owner_table_id <> fnd_api.g_miss_num
AND p_orig_sys_reference_rec.owner_table_id <> -1
THEN
BEGIN
SELECT 'Y'
INTO l_dummy
FROM HZ_CUST_ACCT_SITES_ALL -- Bug 3730175
WHERE cust_acct_site_id = p_orig_sys_reference_rec.owner_table_id;
IF p_create_update_flag = 'C'
AND p_orig_sys_reference_rec.owner_table_id IS NOT NULL
AND p_orig_sys_reference_rec.owner_table_id <> fnd_api.g_miss_num
AND p_orig_sys_reference_rec.owner_table_id <> -1
THEN
BEGIN
SELECT 'Y'
INTO l_dummy
FROM HZ_CUST_SITE_USES_ALL
WHERE site_use_id = p_orig_sys_reference_rec.owner_table_id;
IF p_create_update_flag = 'C'
AND p_orig_sys_reference_rec.owner_table_id IS NOT NULL
AND p_orig_sys_reference_rec.owner_table_id <> fnd_api.g_miss_num
AND p_orig_sys_reference_rec.owner_table_id <> -1
THEN
BEGIN
SELECT 'Y'
INTO l_dummy
FROM HZ_LOCATIONS
WHERE location_id = p_orig_sys_reference_rec.owner_table_id;
IF p_create_update_flag = 'C'
AND p_orig_sys_reference_rec.owner_table_id IS NOT NULL
AND p_orig_sys_reference_rec.owner_table_id <> fnd_api.g_miss_num
AND p_orig_sys_reference_rec.owner_table_id <> -1
THEN
BEGIN
SELECT 'Y'
INTO l_dummy
FROM HZ_ORG_CONTACTS
WHERE org_contact_id = p_orig_sys_reference_rec.owner_table_id;
IF p_create_update_flag = 'C'
AND p_orig_sys_reference_rec.owner_table_id IS NOT NULL
AND p_orig_sys_reference_rec.owner_table_id <> fnd_api.g_miss_num
AND p_orig_sys_reference_rec.owner_table_id <> -1
THEN
BEGIN
SELECT 'Y'
INTO l_dummy
FROM HZ_ORG_CONTACT_ROLES
WHERE org_contact_role_id = p_orig_sys_reference_rec.owner_table_id;
if l_multiple_flag = 'Y' and p_create_update_flag = 'C'
then
open get_dup_orig_sys_ref_csr;
if p_create_update_flag = 'C' and l_multiple_flag = 'N' and l_dup_exist = 'Y'
then
FND_MESSAGE.SET_NAME('AR', 'HZ_API_DUPLICATE_COLUMN');
if p_create_update_flag = 'U'
and orig_sys_reference_exist(p_orig_sys_reference_rec.orig_system,
p_orig_sys_reference_rec.orig_system_reference,
p_orig_sys_reference_rec.owner_table_name) = 'N'
and
(p_orig_sys_reference_rec.old_orig_system_reference is null
or p_orig_sys_reference_rec.old_orig_system_reference = fnd_api.g_miss_char)
then
FND_MESSAGE.SET_NAME('AR', 'HZ_API_NO_DATA_FOUND');
IF p_create_update_flag = 'U'
THEN
open get_orig_sys_reference_csr;
open get_nonupdateable_columns1;
fetch get_nonupdateable_columns1 into l_created_by_module, l_application_id;
close get_nonupdateable_columns1;
open get_nonupdateable_columns2;
fetch get_nonupdateable_columns2 into l_created_by_module, l_application_id;
close get_nonupdateable_columns2;
p_create_update_flag => p_create_update_flag,
p_start_date_column_name => 'start_date_active',
p_start_date => trunc(p_orig_sys_reference_rec.start_date_active), /* Bug 3298896 */
p_old_start_date => trunc(l_start_date), /* Bug 3298896 */
p_end_date_column_name => 'end_date_active',
p_end_date => trunc(p_orig_sys_reference_rec.end_date_active), /* Bug 3298896 */
p_old_end_date => trunc(l_end_date), /* Bug 3298896 */
x_return_status => x_return_status );
p_create_update_flag => p_create_update_flag,
p_created_by_module => p_orig_sys_reference_rec.created_by_module,
p_old_created_by_module => l_created_by_module,
x_return_status => x_return_status);
p_create_update_flag => p_create_update_flag,
p_application_id => p_orig_sys_reference_rec.application_id,
p_old_application_id => l_application_id,
x_return_status => x_return_status);
p_create_update_flag IN VARCHAR2,
p_orig_sys_rec IN HZ_ORIG_SYSTEM_REF_PVT.ORIG_SYS_REC_TYPE,
x_return_status IN OUT NOCOPY VARCHAR2
)IS
cursor source_system_exist is
select sst_flag,
created_by_module,
orig_system,
orig_system_type
from hz_orig_systems_b
where orig_system = p_orig_sys_rec.orig_system;
select trim(translate(upper(p_orig_sys_rec.orig_system),
' ABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890_:.-',
'1 '))
into l_check_orig_system
from dual;
IF p_create_update_flag = 'C' THEN
open source_system_exist;
p_create_update_flag => p_create_update_flag,
p_column => 'orig_system',
p_column_value => p_orig_sys_rec.orig_system,
x_return_status => x_return_status );
p_create_update_flag => p_create_update_flag,
p_column => 'sst_flag',
p_column_value => p_orig_sys_rec.sst_flag,
x_return_status => x_return_status );
p_create_update_flag => p_create_update_flag,
p_column => 'orig_system_name',
p_column_value => p_orig_sys_rec.orig_system_name,
x_return_status => x_return_status );
p_create_update_flag => p_create_update_flag,
p_column => 'orig_system_type',
p_column_value => p_orig_sys_rec.orig_system_type,
x_return_status => x_return_status );
p_create_update_flag => p_create_update_flag,
p_column => 'status',
p_column_value => p_orig_sys_rec.status,
x_return_status => x_return_status );
IF p_create_update_flag = 'U' THEN
open source_system_exist;
p_create_update_flag => p_create_update_flag,
p_column => 'orig_system_id',
p_column_value => p_orig_sys_rec.orig_system_id,
x_return_status => x_return_status );
HZ_UTILITY_V2PUB.validate_nonupdateable (
p_column => 'sst_flag',
p_column_value => p_orig_sys_rec.sst_flag,
p_old_column_value => l_sst_flag,
p_restricted => 'N',
x_return_status => x_return_status );
HZ_UTILITY_V2PUB.validate_nonupdateable (
p_column => 'orig_system',
p_column_value => p_orig_sys_rec.orig_system,
p_old_column_value => l_orig_system,
p_restricted => 'N',
x_return_status => x_return_status );
HZ_UTILITY_V2PUB.validate_nonupdateable (
p_column => 'orig_system_type',
p_column_value => p_orig_sys_rec.orig_system_type,
p_old_column_value => l_orig_system_type,
p_restricted => 'N',
x_return_status => x_return_status );
p_create_update_flag => p_create_update_flag,
p_created_by_module => p_orig_sys_rec.created_by_module,
p_old_created_by_module => l_created_by_module,
x_return_status => x_return_status);