The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT PP.PARTY_ID
FROM HZ_PERSON_PROFILES PP,HZ_DUP_SETS DS, HZ_DUP_SET_PARTIES DSP, HZ_DUP_BATCH DB
WHERE PP.PARTY_ID =DSP.DUP_PARTY_ID
AND DB.DUP_BATCH_ID = DS.DUP_BATCH_ID
AND DS.DUP_SET_ID = DSP.DUP_SET_ID
AND sysdate between pp.effective_start_date and nvl(pp.effective_end_date,sysdate)
AND DSP.DUP_SET_ID= p_merge_batch_id
AND PP.CREATED_BY_MODULE = 'CTB_PERSON_REGISTRY_SERVICES'
AND NVL(DSP.MERGE_FLAG,'Y') <> 'N'
AND ROWNUM = 1;
SELECT PP.PARTY_ID
FROM HZ_PERSON_PROFILES PP,HZ_DUP_SETS DS, HZ_DUP_SET_PARTIES DSP, HZ_DUP_BATCH DB
WHERE PP.PARTY_ID =DSP.DUP_PARTY_ID
AND DB.DUP_BATCH_ID = DS.DUP_BATCH_ID
AND DS.DUP_SET_ID = DSP.DUP_SET_ID
AND sysdate between pp.effective_start_date and nvl(pp.effective_end_date,sysdate)
AND DSP.DUP_SET_ID= p_merge_batch_id
AND PP.INTERNAL_FLAG = 'Y'
AND NVL(DSP.MERGE_FLAG,'Y') <> 'N'
AND ROWNUM = 1;
SELECT OP.PARTY_ID
FROM HZ_ORGANIZATION_PROFILES OP,HZ_DUP_SETS DS, HZ_DUP_SET_PARTIES DSP, HZ_DUP_BATCH DB
WHERE OP.PARTY_ID =DSP.DUP_PARTY_ID
AND DB.DUP_BATCH_ID = DS.DUP_BATCH_ID
AND DS.DUP_SET_ID = DSP.DUP_SET_ID
AND sysdate between op.effective_start_date and nvl(op.effective_end_date,sysdate)
AND DSP.DUP_SET_ID= p_merge_batch_id
AND OP.INTERNAL_FLAG = 'Y'
AND NVL(DSP.MERGE_FLAG,'Y') <> 'N'
AND ROWNUM = 1;
select attribute_name, attribute_value, attribute_type, attribute_party_id
from HZ_MERGE_ENTITY_ATTRIBUTES
where merge_to_party_id = p_merge_to_party_id
and entity_name = 'HZ_ORGANIZATION_PROFILES'
and merge_batch_id = p_merge_batch_id;
/* ELSIF L_ATTRIBUTE_NAME = 'LAST_UPDATE_DATE' THEN
L_ORGANIZATION_REC.LAST_UPDATE_DATE := L_ATTRIBUTE_VALUE;
ELSIF L_ATTRIBUTE_NAME = 'LAST_UPDATE_LOGIN' THEN
L_ORGANIZATION_REC.LAST_UPDATE_LOGIN := L_ATTRIBUTE_VALUE;
ELSIF L_ATTRIBUTE_NAME = 'WH_UPDATE_DATE' THEN
L_ORGANIZATION_REC.WH_UPDATE_DATE := L_ATTRIBUTE_VALUE;
ELSIF L_ATTRIBUTE_NAME = 'PROGRAM_UPDATE_DATE' THEN
L_ORGANIZATION_REC.PROGRAM_UPDATE_DATE := L_ATTRIBUTE_VALUE;
--ELSIF L_ATTRIBUTE_NAME = 'LAST_UPDATED_BY' THEN
-- L_ORGANIZATION_REC.LAST_UPDATED_BY := L_ATTRIBUTE_VALUE;
select attribute_name, attribute_value, attribute_type
from HZ_MERGE_ENTITY_ATTRIBUTES
where merge_to_party_id = p_merge_to_party_id
and entity_name = 'HZ_PERSON_PROFILES'
and merge_batch_id = p_merge_batch_id;
IF (NVL(FND_PROFILE.VALUE('HZ_PROTECT_HR_PERSON_INFO'),'N') ='Y') THEN --Update HR sensitive columns
IF L_ATTRIBUTE_NAME = 'GENDER' THEN
L_PERSON_REC.GENDER := L_ATTRIBUTE_VALUE;
--update other attributes
IF L_ATTRIBUTE_NAME = 'MARITAL_STATUS_EFFECTIVE_DATE' THEN
L_PERSON_REC.MARITAL_STATUS_EFFECTIVE_DATE := l_attribute_date_value; --L_ATTRIBUTE_VALUE; -- Bug No:3729832
ELSIF L_ATTRIBUTE_NAME = 'PROGRAM_UPDATE_DATE' THEN
L_PERSON_REC.PROGRAM_UPDATE_DATE := L_ATTRIBUTE_VALUE; */
/* ELSIF L_ATTRIBUTE_NAME = 'LAST_UPDATE_DATE' THEN
L_PERSON_REC.LAST_UPDATE_DATE := L_ATTRIBUTE_VALUE;
ELSIF L_ATTRIBUTE_NAME = 'LAST_UPDATED_BY' THEN
L_PERSON_REC.LAST_UPDATED_BY := L_ATTRIBUTE_VALUE;
ELSIF L_ATTRIBUTE_NAME = 'LAST_UPDATE_LOGIN' THEN
L_PERSON_REC.LAST_UPDATE_LOGIN := L_ATTRIBUTE_VALUE;
ELSIF L_ATTRIBUTE_NAME = 'WH_UPDATE_DATE' THEN
L_PERSON_REC.WH_UPDATE_DATE := L_ATTRIBUTE_VALUE;
select object_version_number,orig_system_reference
from hz_parties
where party_id = p_merge_to_party_id;
select attribute_party_id
from HZ_MERGE_ENTITY_ATTRIBUTES
where merge_to_party_id = p_merge_to_party_id
and entity_name = 'HZ_ORGANIZATION_PROFILES'
and merge_batch_id = p_merge_batch_id
and attribute_name = 'BUSINESS_REPORT';
select nvl(db.automerge_flag,'N')
from hz_dup_batch db, hz_dup_sets ds
where db.dup_batch_id = ds.dup_batch_id
and ds.dup_set_id = p_merge_batch_id
and rownum=1;
Hz_party_v2pub.update_organization (
p_init_msg_list => FND_API.G_FALSE,
p_organization_rec => l_org_rec,
p_party_object_version_number =>l_object_version_number,
x_profile_id => l_profile_id,
x_return_status => x_return_status,
x_msg_count => l_msg_count,
x_msg_data =>l_msg_data
);
UPDATE hz_organization_profiles
SET business_report = (select business_report
from hz_organization_profiles
where party_id = l_attribute_party_id
and sysdate between effective_start_date and nvl(effective_end_date,sysdate)
and rownum=1),
last_update_date = SYSDATE,
last_update_login = FND_GLOBAL.LOGIN_ID,
last_updated_by = FND_GLOBAL.USER_ID,
object_version_number = nvl(object_version_number,1)+1
where party_id = p_merge_to_party_id
and sysdate between effective_start_date and nvl(effective_end_date,sysdate);
Hz_party_v2pub.update_person (
p_init_msg_list => FND_API.G_FALSE,
p_person_rec => l_person_rec,
p_party_object_version_number => l_object_version_number,
x_profile_id => l_profile_id,
x_return_status => x_return_status,
x_msg_count => l_msg_count,
x_msg_data =>l_msg_data
);
-- update_org/person api doesn't allow update created_by_module
-- per chris, call update directly.
l_party_id :=null;
update hz_person_profiles
set created_by_module = 'CTB_PERSON_REGISTRY_SERVICES',
last_update_date = SYSDATE,
last_update_login = FND_GLOBAL.LOGIN_ID,
last_updated_by = FND_GLOBAL.USER_ID,
object_version_number = nvl(object_version_number,1)+1
where party_id = p_merge_to_party_id
and sysdate between effective_start_date and nvl(effective_end_date,sysdate);
update HZ_MERGE_ENTITY_ATTRIBUTES
set attribute_value = 'Y',
attribute_party_id = l_party_id,
last_update_date = SYSDATE,
last_update_login = FND_GLOBAL.LOGIN_ID,
last_updated_by = FND_GLOBAL.USER_ID,
object_version_number = nvl(object_version_number,1)+1
where attribute_name = 'INTERNAL_FLAG'
and merge_batch_id = p_merge_batch_id
and merge_to_party_id = p_merge_to_party_id;
and (p_attribute_name not in ('LAST_UPDATED_BY', 'LAST_UPDATE_LOGIN')) -- bug 4726700
then
l_str := 'select to_char('||p_attribute_name||','||'''YYYY/MM/DD'')'||
' from '||p_entity_name||
' where party_id = :party_id'||
' and sysdate between effective_start_date and nvl(effective_end_date,sysdate)';
l_str := 'select to_char('||p_attribute_name||')'||
' from '||p_entity_name||
' where party_id = :party_id'||
' and sysdate between effective_start_date and nvl(effective_end_date,sysdate)';
procedure update_group_attribute_value(p_attribute_name in varchar2,
p_candidate_party_id in number,
p_merge_batch_id IN NUMBER,
p_merge_to_party_id IN NUMBER,
p_derived_last_update_date IN DATE,
p_entity_name IN VARCHAR2,
p_proc_type IN VARCHAR2 DEFAULT 'C') is
-- get all attributes for the passing in group leader attribute name
cursor get_group_attributes_csr(p_attribute_name varchar2, p_lookup_type varchar2) is
select c1.lookup_code
from ar_lookups c, ar_lookups t, ar_lookups c1
where c.lookup_type = t.lookup_code
and c1.lookup_type = t.lookup_code
and substrb(c.externally_visible_flag,6,3) = substrb(c1.externally_visible_flag,6,3)
and c.lookup_type = c1.lookup_type
and t.lookup_type = p_lookup_type
and c.lookup_code = p_attribute_name
and c1.enabled_flag = 'Y';
update hz_merge_entity_attributes
set attribute_value = l_value, -- l_value has already formatted
--attribute_value = decode(attribute_type,'D',to_char(to_date(l_value),'YYYY/MM/DD'),l_value), -- Bug No:3729832
attribute_party_id = p_candidate_party_id,
derived_last_update_date = p_derived_last_update_date,
last_update_date = SYSDATE,
last_update_login = FND_GLOBAL.LOGIN_ID,
last_updated_by = FND_GLOBAL.USER_ID,
object_version_number = nvl(object_version_number,1)+1
where attribute_name = l_group_attribute_name
and merge_batch_id = p_merge_batch_id
and entity_name = p_entity_name
and merge_to_party_id =p_merge_to_party_id
and attribute_value is null;
update hz_merge_entity_attributes
set attribute_value = l_value, -- l_value has already formatted
--attribute_value = decode(attribute_type,'D',to_char(to_date(l_value),'YYYY/MM/DD'),l_value), -- Bug No:3729832
attribute_party_id = p_candidate_party_id,
derived_last_update_date = p_derived_last_update_date,
last_update_date = SYSDATE,
last_update_login = FND_GLOBAL.LOGIN_ID,
last_updated_by = FND_GLOBAL.USER_ID,
object_version_number = nvl(object_version_number,1)+1
where attribute_name = l_group_attribute_name
and merge_batch_id = p_merge_batch_id
and entity_name = p_entity_name
and merge_to_party_id =p_merge_to_party_id;
Default to selected party profile attribute values which are not null.
For nullable leader attribute values, default to candidate party
profile attribute values based on profile values, also, update master party
profile to leader attribute group values.*/
procedure default_attribute_values(
p_merge_batch_id IN NUMBER,
p_merge_to_party_id IN NUMBER,
p_entity_name IN VARCHAR2) is
-- get master party attributes where leader is null
cursor get_leader_attribute_name_csr(p_lookup_type varchar2) is
select attri.attribute_name
from hz_merge_entity_attributes attri, ar_lookups c, ar_lookups t
where attri.attribute_name = c.lookup_code
and c.lookup_type = t.lookup_code
and t.lookup_type = p_lookup_type
and attribute_value is null and substrb(c.externally_visible_flag,10,1) = 'L'
and merge_batch_id = p_merge_batch_id
and merge_to_party_id = p_merge_to_party_id
and c.enabled_flag = 'Y';
-- get candidate parties with desc order for last update date
cursor get_org_candidate_party_csr is
select mp.from_party_id, pro.last_update_date
from hz_organization_profiles pro, hz_merge_parties mp
where pro.party_id = mp.from_party_id
and mp.batch_id = p_merge_batch_id
and sysdate between pro.effective_start_date and nvl(pro.effective_end_date,sysdate)
order by pro.last_update_date desc;
select mp.from_party_id, pro.last_update_date
from hz_organization_profiles pro, hz_merge_parties mp
where pro.party_id = mp.from_party_id
and mp.batch_id = p_merge_batch_id
and sysdate between pro.effective_start_date and nvl(pro.effective_end_date,sysdate)
order by pro.creation_date desc, pro.party_id desc;
select mp.from_party_id, pro.last_update_date
from hz_organization_profiles pro, hz_merge_parties mp
where pro.party_id = mp.from_party_id
and mp.batch_id = p_merge_batch_id
and sysdate between pro.effective_start_date and nvl(pro.effective_end_date,sysdate)
order by pro.creation_date, pro.party_id;
-- get candidate parties with desc order for last update date
cursor get_per_candidate_party_csr is
select mp.from_party_id, pro.last_update_date
from hz_person_profiles pro, hz_merge_parties mp
where pro.party_id = mp.from_party_id
and mp.batch_id = p_merge_batch_id
and sysdate between pro.effective_start_date and nvl(pro.effective_end_date,sysdate)
order by pro.last_update_date desc;
select mp.from_party_id, pro.last_update_date
from hz_person_profiles pro, hz_merge_parties mp
where pro.party_id = mp.from_party_id
and mp.batch_id = p_merge_batch_id
and sysdate between pro.effective_start_date and nvl(pro.effective_end_date,sysdate)
order by pro.creation_date desc, pro.party_id desc;
select mp.from_party_id, pro.last_update_date
from hz_person_profiles pro, hz_merge_parties mp
where pro.party_id = mp.from_party_id
and mp.batch_id = p_merge_batch_id
and sysdate between pro.effective_start_date and nvl(pro.effective_end_date,sysdate)
order by pro.creation_date, pro.party_id;
l_last_update_date DATE;
l_attri_def_next_prof := nvl(fnd_profile.value('HZ_PROF_ATTR_DEFAULT_NEXT'), 'LATEST_UPDATE_DATE');
/************* LATEST_UPDATE_DATE CASE **************/
if l_attri_def_next_prof = 'LATEST_UPDATE_DATE'
then
open get_org_candidate_party_csr;
fetch get_org_candidate_party_csr into l_party_id,l_last_update_date;
update_group_attribute_value(p_attribute_name=>l_attribute_name,
p_candidate_party_id => l_party_id,
p_merge_batch_id => p_merge_batch_id,
p_merge_to_party_id =>p_merge_to_party_id,
p_derived_last_update_date => l_last_update_date,
p_entity_name => p_entity_name);
end loop; -- for each candidate party desc on last update date
end if; -- latest update date case
fetch get_org_candidate_party_lc_csr into l_party_id,l_last_update_date;
update_group_attribute_value(p_attribute_name=>l_attribute_name,
p_candidate_party_id => l_party_id,
p_merge_batch_id => p_merge_batch_id,
p_merge_to_party_id =>p_merge_to_party_id,
p_derived_last_update_date => l_last_update_date,
p_entity_name => p_entity_name);
fetch get_org_candidate_party_ec_csr into l_party_id,l_last_update_date;
update_group_attribute_value(p_attribute_name=>l_attribute_name,
p_candidate_party_id => l_party_id,
p_merge_batch_id => p_merge_batch_id,
p_merge_to_party_id =>p_merge_to_party_id,
p_derived_last_update_date => l_last_update_date,
p_entity_name => p_entity_name);
/************* LATEST_UPDATE_DATE CASE **************/
if (l_attri_def_next_prof = 'LATEST_UPDATE_DATE')
then
open get_per_candidate_party_csr;
fetch get_per_candidate_party_csr into l_party_id,l_last_update_date;
update_group_attribute_value(p_attribute_name=>l_attribute_name,
p_candidate_party_id => l_party_id,
p_merge_batch_id => p_merge_batch_id,
p_merge_to_party_id => p_merge_to_party_id,
p_derived_last_update_date => l_last_update_date,
p_entity_name => p_entity_name);
end loop; -- for each candidate party desc on last update date
end if; -- person lastest update date case
fetch get_per_candidate_party_lc_csr into l_party_id,l_last_update_date;
update_group_attribute_value(p_attribute_name=>l_attribute_name,
p_candidate_party_id => l_party_id,
p_merge_batch_id => p_merge_batch_id,
p_merge_to_party_id => p_merge_to_party_id,
p_derived_last_update_date => l_last_update_date,
p_entity_name => p_entity_name);
fetch get_per_candidate_party_ec_csr into l_party_id,l_last_update_date;
update_group_attribute_value(p_attribute_name=>l_attribute_name,
p_candidate_party_id => l_party_id,
p_merge_batch_id => p_merge_batch_id,
p_merge_to_party_id => p_merge_to_party_id,
p_derived_last_update_date => l_last_update_date,
p_entity_name => p_entity_name);
select 'x'
from hz_organization_profiles
where party_id = p_merge_to_party_id
and business_report is not null
and sysdate between effective_start_date and nvl(effective_end_date,sysdate);
-- get candidate parties with desc order for last update date
cursor get_business_report_party_csr is
select mp.from_party_id, pro.last_update_date
from hz_organization_profiles pro, hz_merge_parties mp
where pro.party_id = mp.from_party_id
and mp.batch_id = p_merge_batch_id
and pro.business_report is not null
and sysdate between pro.effective_start_date and nvl(pro.effective_end_date,sysdate)
order by pro.last_update_date desc;
l_last_update_date date;
fetch get_business_report_party_csr into l_from_party_id, l_last_update_date;
update hz_merge_entity_attributes
set attribute_party_id = l_from_party_id,
last_update_date = SYSDATE,
last_update_login = FND_GLOBAL.LOGIN_ID,
last_updated_by = FND_GLOBAL.USER_ID,
object_version_number = nvl(object_version_number,1)+1,
derived_last_update_date = l_last_update_date
where attribute_name = 'BUSINESS_REPORT'
and merge_batch_id = p_merge_batch_id
and entity_name = 'HZ_ORGANIZATION_PROFILES'
and merge_to_party_id =p_merge_to_party_id;
return; -- only need to get the first party_id which has latest update date.
select col.column_name, col.column_type
from fnd_tables tbl, fnd_columns col
where tbl.table_id = col.table_id
and tbl.table_name = l_table_name
-- adding the following clause for performance 4956759
and tbl.application_id = 222
and tbl.application_id = col.application_id;
l_attri_def_next_prof := nvl(fnd_profile.value('HZ_PROF_ATTR_DEFAULT_NEXT'), 'LATEST_UPDATE_DATE');
if l_attri_def_next_prof = 'LATEST_UPDATE_DATE'
then
l_date_clause := 'LAST_UPDATE_DATE';
l_order_by_clause := ' order by LAST_UPDATE_DATE DESC';
l_str := 'select party_id, decode('''||l_col_type||''',''D'',to_char(attri_value,''YYYY/MM/DD''),attri_value) from '||
'(SELECT party_id, attri_value, rank() over (order by cntAttri desc) rank, '||l_date_clause || ' from '||
'(SELECT OP.PARTY_ID, OP.'||l_col_name||' attri_value, count(*) over (partition by OP.'||l_col_name||') as cntAttri, '||'op.'||l_date_clause||
' from HZ_ORGANIZATION_PROFILES OP,HZ_PARTIES PARTY, HZ_DUP_SETS DS, HZ_DUP_SET_PARTIES DSP, HZ_DUP_BATCH DB '||
'WHERE OP.PARTY_ID =DSP.DUP_PARTY_ID AND DB.DUP_BATCH_ID = DS.DUP_BATCH_ID '||
'AND DS.DUP_SET_ID = DSP.DUP_SET_ID AND OP.PARTY_ID = PARTY.PARTY_ID '||
'AND sysdate between effective_start_date and nvl(effective_end_date,sysdate) '||
'AND OP.'||l_col_name||' is not null '||
'AND nvl(merge_flag, ''Y'') <> ''N'' AND DSP.DUP_SET_ID= '||':p_merge_batch_id'||')' || l_order_by_clause||')'
||' where rank = 1 and rownum=1' ; --added rownum=1, in case same create/update date for rank = 1
l_str := 'select party_id, decode('''||l_col_type||''',''D'',to_char(attri_value,''YYYY/MM/DD''),attri_value) from '||
'(SELECT party_id, attri_value, rank() over (order by cntAttri desc) rank, '||l_date_clause || ' from '||
'(SELECT OP.PARTY_ID, OP.'||l_col_name||' attri_value, count(*) over (partition by OP.'||l_col_name||') as cntAttri, '||'op.'||l_date_clause||
' from HZ_PERSON_PROFILES OP,HZ_PARTIES PARTY, HZ_DUP_SETS DS, HZ_DUP_SET_PARTIES DSP, HZ_DUP_BATCH DB '||
'WHERE OP.PARTY_ID =DSP.DUP_PARTY_ID AND DB.DUP_BATCH_ID = DS.DUP_BATCH_ID '||
'AND DS.DUP_SET_ID = DSP.DUP_SET_ID AND OP.PARTY_ID = PARTY.PARTY_ID '||
'AND sysdate between effective_start_date and nvl(effective_end_date,sysdate) '||
'AND OP.'||l_col_name||' is not null '||
'AND nvl(merge_flag, ''Y'') <> ''N'' AND DSP.DUP_SET_ID= '||':p_merge_batch_id'||')' || l_order_by_clause||')'
||' where rank = 1 and rownum=1' ; --added rownum=1, in case same create/update date for rank = 1
insert into hz_merge_entity_attributes(
MERGE_BATCH_ID,
MERGE_TO_PARTY_ID,
ATTRIBUTE_NAME,
ATTRIBUTE_VALUE,
ATTRIBUTE_TYPE,
ATTRIBUTE_PARTY_ID,
ENTITY_NAME,
DERIVED_LAST_UPDATE_DATE,
OBJECT_VERSION_NUMBER,
CREATED_BY ,
CREATION_DATE ,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
LAST_UPDATE_DATE) values
(p_merge_batch_id,
p_merge_to_party_id,
l_col_name,
l_attri_value,
l_col_type,
l_attri_party_id,
p_entity_name,
l_derived_date,
1,
nvl(fnd_global.user_id,-1),
sysdate,
nvl(fnd_global.user_id,-1),
nvl(fnd_global.login_id,-1),
sysdate);
select col.column_name, col.column_type
from fnd_tables tbl, fnd_columns col
where tbl.table_id = col.table_id
and tbl.table_name = l_table_name
-- adding the following clause for performance 4956759
and tbl.application_id = 222
and tbl.application_id = col.application_id;
-- get party with latest update date
cursor get_party_id_lu_csr is
select party_id
from(
select pro.party_id party_id,
RANK() OVER (ORDER BY pro.last_update_date desc ) rank
from hz_parties pro, hz_dup_set_parties mp
where pro.party_id = mp.dup_party_id
and mp.dup_set_id = p_merge_batch_id)
where rank = 1 and rownum=1;
select party_id
from(
select pro.party_id party_id,
RANK() OVER (ORDER BY pro.creation_date desc, pro.party_id desc ) rank
from hz_parties pro, hz_dup_set_parties mp
where pro.party_id = mp.dup_party_id
and mp.dup_set_id = p_merge_batch_id)
where rank = 1;
select party_id
from(
select pro.party_id party_id,
RANK() OVER (ORDER BY pro.creation_date, pro.party_id ) rank
from hz_parties pro, hz_dup_set_parties mp
where pro.party_id = mp.dup_party_id
and mp.dup_set_id = p_merge_batch_id)
where rank = 1;
l_selected_party_id number;
then l_selected_party_id := p_merge_to_party_id;
elsif l_prof_value = 'LATEST_UPDATE_DATE'
then
open get_party_id_lu_csr;
fetch get_party_id_lu_csr into l_selected_party_id;
fetch get_party_id_lc_csr into l_selected_party_id;
fetch get_party_id_ec_csr into l_selected_party_id;
insert into hz_merge_entity_attributes(
MERGE_BATCH_ID,
MERGE_TO_PARTY_ID,
ATTRIBUTE_NAME,
ATTRIBUTE_VALUE,
ATTRIBUTE_TYPE,
ATTRIBUTE_PARTY_ID,
ENTITY_NAME,
DERIVED_LAST_UPDATE_DATE,
OBJECT_VERSION_NUMBER,
CREATED_BY ,
CREATION_DATE ,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
LAST_UPDATE_DATE) select
p_merge_batch_id,
p_merge_to_party_id,
'BUSINESS_REPORT',
'CLOB',
l_col_type,
p_merge_to_party_id,
p_entity_name,
last_update_date,
1,
nvl(fnd_global.user_id,-1),
sysdate,
nvl(fnd_global.user_id,-1),
nvl(fnd_global.login_id,-1),
sysdate
from HZ_ORGANIZATION_PROFILES
where party_id = l_selected_party_id
and sysdate between effective_start_date and nvl(effective_end_date,sysdate);
-- ex. select to_char(SIC_CODE) from hz_organization_profiles to get attribute_value
str := 'insert into hz_merge_entity_attributes( '||
'MERGE_BATCH_ID,'||
'MERGE_TO_PARTY_ID,'||
'ATTRIBUTE_NAME,'||
'ATTRIBUTE_VALUE,'||
'ATTRIBUTE_TYPE,'||
'ATTRIBUTE_PARTY_ID,'||
'ENTITY_NAME ,'||
'DERIVED_LAST_UPDATE_DATE ,'||
'OBJECT_VERSION_NUMBER,'||
'CREATED_BY ,'||
'CREATION_DATE ,'||
'LAST_UPDATED_BY,'||
'LAST_UPDATE_LOGIN,'||
'LAST_UPDATE_DATE) '||
'select '||
p_merge_batch_id ||','||
p_merge_to_party_id ||','||
''''||l_col_name||''''||','||
'decode('||''''||l_col_type||''''||',''D'','||'to_char('||l_col_name||','||'''YYYY/MM/DD'')'||','||'to_char('||l_col_name||')'||')'||','||
''''||l_col_type||''''||','||
l_selected_party_id ||','||
''''||p_entity_name||''''||','||
'last_update_date'||','||
'1,'||
'nvl(fnd_global.user_id,-1)'||','||
'sysdate'||','||
'nvl(fnd_global.user_id,-1)'||','||
'nvl(fnd_global.login_id,-1)'||','||
'sysdate'||
' from '|| p_entity_name ||
' where party_id = '||':selected_party_id'||
' and sysdate between effective_start_date and nvl(effective_end_date,sysdate)';
EXECUTE IMMEDIATE str using l_selected_party_id;
update HZ_MERGE_ENTITY_ATTRIBUTES
set attribute_value = 'CTB_PERSON_REGISTRY_SERVICES',
attribute_party_id = l_party_id,
last_update_date = SYSDATE,
last_update_login = FND_GLOBAL.LOGIN_ID,
last_updated_by = FND_GLOBAL.USER_ID,
object_version_number = nvl(object_version_number,1)+1
where attribute_name = 'CREATED_BY_MODULE'
and merge_batch_id = p_merge_batch_id
and merge_to_party_id = p_merge_to_party_id;
update HZ_MERGE_ENTITY_ATTRIBUTES
set attribute_value = 'Y',
attribute_party_id = l_party_id,
last_update_date = SYSDATE,
last_update_login = FND_GLOBAL.LOGIN_ID,
last_updated_by = FND_GLOBAL.USER_ID,
object_version_number = nvl(object_version_number,1)+1
where attribute_name = 'INTERNAL_FLAG'
and merge_batch_id = p_merge_batch_id
and merge_to_party_id = p_merge_to_party_id;
select 'X'
from ar_lookups c,
ar_lookups t
where c.lookup_type = t.lookup_code
and c.enabled_flag = 'Y'
and t.lookup_type = p_lookup_type
and substrb(c.externally_visible_flag,10,1) = 'L'
and c.lookup_code = p_attribute_name;
PROCEDURE update_merge_attribute (
p_merge_batch_id IN NUMBER,
p_merge_to_party_id IN NUMBER,
p_attribute_name IN VARCHAR2,
p_attribute_value IN VARCHAR2,
p_attribute_party_id IN NUMBER,
p_entity_name IN VARCHAR2,
px_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
cursor get_obj_version_number_csr is
select object_version_number
from HZ_MERGE_ENTITY_ATTRIBUTES
where merge_batch_id = p_merge_batch_id
and merge_to_party_id = p_merge_to_party_id
and entity_name = p_entity_name
and attribute_name = p_attribute_name;
cursor get_org_last_update_date_csr is
select last_update_date
from hz_organization_profiles
where party_id = p_attribute_party_id
and sysdate between effective_start_date and nvl(effective_end_date,sysdate);
cursor get_per_last_update_date_csr is
select last_update_date
from hz_person_profiles
where party_id = p_attribute_party_id
and sysdate between effective_start_date and nvl(effective_end_date,sysdate);
l_last_update_date date;
savepoint update_merge_attributes_pvt;
/* if pass in attribute name is not leading attri, no update action */
return;
open get_org_last_update_date_csr;
fetch get_org_last_update_date_csr into l_last_update_date;
close get_org_last_update_date_csr;
open get_per_last_update_date_csr;
fetch get_per_last_update_date_csr into l_last_update_date;
close get_per_last_update_date_csr;
update HZ_MERGE_ENTITY_ATTRIBUTES
set attribute_value = p_attribute_value,
attribute_party_id = p_attribute_party_id,
derived_last_update_date = decode(p_attribute_party_id,null,SYSDATE,l_last_update_date),
last_update_date = SYSDATE,
last_update_login = FND_GLOBAL.LOGIN_ID,
last_updated_by = FND_GLOBAL.USER_ID,
object_version_number = px_object_version_number
where attribute_name = p_attribute_name
and merge_batch_id = p_merge_batch_id
and merge_to_party_id = p_merge_to_party_id
and entity_name = p_entity_name;
update_group_attribute_value(p_attribute_name=>p_attribute_name,
p_candidate_party_id => p_attribute_party_id,
p_merge_batch_id => p_merge_batch_id,
p_merge_to_party_id =>p_merge_to_party_id,
p_derived_last_update_date => l_last_update_date,
p_entity_name => p_entity_name,
p_proc_type => 'U');
update HZ_MERGE_ENTITY_ATTRIBUTES
set object_version_number = px_object_version_number
where attribute_name = p_attribute_name
and merge_batch_id = p_merge_batch_id
and merge_to_party_id = p_merge_to_party_id
and entity_name = p_entity_name;
UPDATE HZ_DUP_SETS
SET STATUS = 'MAPPING',
LAST_UPDATE_LOGIN = HZ_UTILITY_V2PUB.LAST_UPDATE_LOGIN,
LAST_UPDATE_DATE = HZ_UTILITY_V2PUB.LAST_UPDATE_DATE,
LAST_UPDATED_BY = HZ_UTILITY_V2PUB.LAST_UPDATED_BY,
object_version_number = nvl(object_version_number,1)+1
WHERE DUP_SET_ID = p_merge_batch_id;
ROLLBACK TO update_merge_attributes_pvt;
ROLLBACK TO update_merge_attributes_pvt ;
ROLLBACK TO update_merge_attributes_pvt;
end update_merge_attribute;
cursor get_org_last_update_date_csr is
select last_update_date
from hz_organization_profiles
where party_id = p_attribute_party_id
and sysdate between effective_start_date and nvl(effective_end_date,sysdate);
cursor get_per_last_update_date_csr is
select last_update_date
from hz_person_profiles
where party_id = p_attribute_party_id
and sysdate between effective_start_date and nvl(effective_end_date,sysdate);
l_last_update_date date;
open get_org_last_update_date_csr;
fetch get_org_last_update_date_csr into l_last_update_date;
close get_org_last_update_date_csr;
open get_per_last_update_date_csr;
fetch get_per_last_update_date_csr into l_last_update_date;
close get_per_last_update_date_csr;
return l_last_update_date;
select 'x'
from hz_merge_entity_attributes
where merge_batch_id = p_merge_batch_id
and attribute_name <> 'BUSINESS_REPORT'
and attribute_party_id = l_attribute_party_id and rownum = 1
and derived_last_update_date <> (select last_update_date
from hz_organization_profiles
where party_Id =l_attribute_party_id
and sysdate between effective_start_date and nvl(effective_end_date,sysdate));
select 'x'
from hz_merge_entity_attributes
where merge_batch_id = p_merge_batch_id
and attribute_party_id = l_attribute_party_id and rownum = 1
and derived_last_update_date <> (select last_update_date
from hz_person_profiles
where party_Id = l_attribute_party_id
and sysdate between effective_start_date and nvl(effective_end_date,sysdate));
select distinct attribute_party_id
from hz_merge_entity_attributes
where merge_batch_id = p_merge_batch_id;
select distinct attribute_party_id, derived_last_update_date
from hz_merge_entity_attributes
where merge_batch_id = p_merge_batch_id
and merge_to_party_id = p_merge_to_party_id
and attribute_name <> 'BUSINESS_REPORT';
select attribute_name, entity_name
from hz_merge_entity_attributes attri
where merge_batch_id = p_merge_batch_id
and merge_to_party_id = p_merge_to_party_id
and attribute_party_id = l_attribute_party_id
and attribute_name <> 'BUSINESS_REPORT';
select count(distinct attribute_party_id)
from hz_merge_entity_attributes
where merge_batch_id = p_merge_batch_id
and merge_to_party_id = p_merge_to_party_id
and attribute_party_id <> p_merge_to_party_id;
delete from hz_merge_entity_attributes
where merge_batch_id = p_merge_batch_id;
else -- data has been updated by the user
open get_attri_party_id_csr;
update hz_merge_entity_attributes
set attribute_value = l_value, -- value formatted already
derived_last_update_date = l_party_last_upd_date,
last_update_date = SYSDATE,
last_update_login = FND_GLOBAL.LOGIN_ID,
last_updated_by = FND_GLOBAL.USER_ID,
object_version_number = nvl(object_version_number,1)+1
where attribute_name = l_attribute_name
and merge_batch_id = p_merge_batch_id
and entity_name = p_entity_name
and merge_to_party_id =p_merge_to_party_id
and attribute_party_id = l_attri_party_id;