[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE insert_party_details( cp_batch_party_id IN NUMBER,
cp_from_party_id IN NUMBER,
cp_to_party_id IN NUMBER,
p_def_to_entity IN VARCHAR2 DEFAULT 'N');
PROCEDURE insert_reln_parties( p_batch_party_id IN NUMBER,
p_batch_id IN NUMBER);
PROCEDURE insert_party_site_details (
p_from_party_id IN NUMBER,
p_to_party_id IN NUMBER,
p_batch_party_id IN NUMBER,
p_reln_parties IN VARCHAR2 DEFAULT 'N');
PROCEDURE insert_party_reln_details (
p_from_party_id IN NUMBER,
p_to_party_id IN NUMBER,
p_batch_party_id IN NUMBER,
p_def_mapping IN VARCHAR2 DEFAULT 'N');
PROCEDURE insert_sugg_reln_ps_details (
p_from_party_id IN NUMBER,
p_to_party_id IN NUMBER,
p_batch_party_id IN NUMBER,
p_reln_parties IN VARCHAR2 DEFAULT 'N'
);
PROCEDURE insert_sugg_reln_party(
p_batch_id IN NUMBER,
p_from_rel_party_id IN NUMBER,
p_to_rel_party_id IN NUMBER,
x_batch_party_id OUT NOCOPY NUMBER
);
SELECT substrb(party_name, 1, 60) || ' (' || p_dup_set_id||')',
winner_party_id, merge_type
FROM HZ_DUP_SETS, HZ_PARTIES
WHERE winner_party_id = party_id
AND dup_set_id = p_dup_set_id;
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_dup_set_id
and rownum=1;
SELECT DUP_PARTY_ID
FROM HZ_DUP_SET_PARTIES
WHERE DUP_SET_ID = p_dup_set_id
AND nvl(MERGE_FLAG,'Y')<>'N'
ORDER BY decode(dup_party_id,cp_winner_party_id,2,1);
select count(*)
from hz_dup_batch db, hz_dup_sets ds
where db.dup_batch_id = ds.dup_batch_id
and db.match_rule_id = -1
and db.dup_batch_name like 'SUGG:%'
and ds.dup_set_id = p_dup_set_id;
HZ_MERGE_BATCH_PKG.INSERT_ROW(
px_BATCH_ID => l_batch_id,
p_RULE_SET_NAME => 'DEFAULT',
p_BATCH_NAME => l_batch_name,
p_REQUEST_ID => NULL,
p_BATCH_STATUS => 'MAPPING_PENDING',
p_BATCH_COMMIT => 'B',
p_BATCH_DELETE => 'N',
p_MERGE_REASON_CODE => 'DEDUPE',
p_CREATED_BY => HZ_UTILITY_V2PUB.CREATED_BY,
p_CREATION_DATE => HZ_UTILITY_V2PUB.CREATION_DATE,
p_LAST_UPDATE_LOGIN => HZ_UTILITY_V2PUB.LAST_UPDATE_LOGIN,
p_LAST_UPDATE_DATE => HZ_UTILITY_V2PUB.LAST_UPDATE_DATE,
p_LAST_UPDATED_BY => HZ_UTILITY_V2PUB.LAST_UPDATED_BY);
UPDATE HZ_MERGE_BATCH
SET CREATED_BY_MODULE = l_created_by_module
WHERE batch_id = p_dup_set_id;
HZ_MERGE_PARTIES_PKG.INSERT_ROW(
px_BATCH_PARTY_ID => l_batch_party_id,
p_batch_id => l_batch_id,
p_merge_type => l_merge_type2,
p_from_party_id => l_merge_from,
p_to_party_id => l_merge_to,
p_merge_reason_code => 'DEDUPE',
p_merge_status => 'PENDING',
p_CREATED_BY => HZ_UTILITY_V2PUB.CREATED_BY,
p_CREATION_DATE => HZ_UTILITY_V2PUB.CREATION_DATE,
p_LAST_UPDATE_LOGIN => HZ_UTILITY_V2PUB.LAST_UPDATE_LOGIN,
p_LAST_UPDATE_DATE => HZ_UTILITY_V2PUB.LAST_UPDATE_DATE,
p_LAST_UPDATED_BY => HZ_UTILITY_V2PUB.LAST_UPDATED_BY);
insert_party_details(l_batch_party_id,
l_merge_from ,
l_merge_to ,
'Y' );
insert_party_details(l_batch_party_id,
l_merge_from ,
l_merge_to);
insert_reln_parties(l_batch_party_id ,
l_batch_id );
SELECT decode(party_type,'PERSON','HZ_PERSON_PROFILES',
'ORGANIZATION','HZ_ORGANIZATION_PROFILES',
'HZ_ORGANIZATION_PROFILES') INTO l_party_type
FROM HZ_PARTIES
WHERE party_id=l_merge_to;
HZ_MERGE_PARTIES_PKG.INSERT_ROW(
px_BATCH_PARTY_ID => l_batch_party_id,
p_batch_id => l_batch_id,
p_merge_type => 'SAME_PARTY_MERGE',
p_from_party_id => l_merge_from,
p_to_party_id => l_merge_to,
p_merge_reason_code => 'DEDUPE',
p_merge_status => 'PENDING',
p_CREATED_BY => HZ_UTILITY_V2PUB.CREATED_BY,
p_CREATION_DATE => HZ_UTILITY_V2PUB.CREATION_DATE,
p_LAST_UPDATE_LOGIN => HZ_UTILITY_V2PUB.LAST_UPDATE_LOGIN,
p_LAST_UPDATE_DATE => HZ_UTILITY_V2PUB.LAST_UPDATE_DATE,
p_LAST_UPDATED_BY => HZ_UTILITY_V2PUB.LAST_UPDATED_BY);
SELECT object_version_number
INTO db_object_version_number
FROM hz_dup_sets
WHERE dup_set_id = p_dup_set_id
FOR UPDATE OF dup_set_id;
UPDATE HZ_DUP_SETS
SET object_version_number = l_object_version_number
WHERE dup_set_id = p_dup_set_id;
select count(distinct code.owner_table_id)
from hz_code_assignments code
where code.class_category = 'RELATIONSHIP_TYPE_GROUP'
and code.class_code = 'PARTY_REL_GRP_CONTACTS'
and code.owner_table_name = 'HZ_RELATIONSHIP_TYPES'
and exists ( select 1 from hz_relationship_types rt,
HZ_RELATIONSHIPS r
where r.relationship_id = p_relationship_id
and r.relationship_type = rt.relationship_type
and r.relationship_code = rt.forward_rel_code
and r.subject_type = rt.subject_type
and r.object_type = rt.object_type
and r.directional_flag = decode(rt.direction_code, 'N','F',
r.directional_flag)
and code.owner_table_id = rt.relationship_type_id
);
SELECT l.country,
l.city,
l.state,
l.county,
l.province,
l.postal_code,
ps.status,
ps.location_id --bug 4569674
FROM hz_locations l
, hz_party_sites ps
WHERE ps.party_site_id = cp_ps_id
AND ps.location_id = l.location_id;
select 1 from hz_cust_acct_sites_all where party_site_id=p_from_entity_id;
SELECT distinct(batch_id)
INTO l_batch_id
FROM hz_merge_parties
WHERE batch_party_id = p_batch_party_id;
SELECT mandatory_merge, merge_to_entity_id INTO l_mm, l_merge_to
FROM hz_merge_party_details
where batch_party_id=p_batch_party_id
AND entity_name = p_entity
AND merge_from_entity_id = p_from_entity_id;
SELECT count(1) INTO l_tmp
FROM hz_merge_party_details md1, hz_merge_party_details md2,
hz_merge_parties mp1, hz_merge_parties mp2
where md1.batch_party_id=p_batch_party_id
AND md1.batch_party_id = mp1.batch_party_id
AND mp1.batch_id = mp2.batch_id
AND md2.batch_party_id=mp2.batch_party_id
AND md2.entity_name = p_entity
AND md2.merge_to_entity_id = p_from_entity_id
AND md2.merge_from_entity_id<>p_from_entity_id;
SELECT count(1) INTO l_tmp2
FROM hz_merge_party_details md1, hz_merge_party_details md2,
hz_merge_parties mp1, hz_merge_parties mp2
where md1.batch_party_id=p_batch_party_id
AND md1.batch_party_id = mp1.batch_party_id
AND mp1.batch_id = mp2.batch_id
AND md2.batch_party_id=mp2.batch_party_id
AND md2.entity_name = p_entity
AND md2.merge_from_entity_id = p_to_entity_id
AND md2.merge_to_entity_id<>p_to_entity_id;
SELECT count(*) into l_rel_party_count
FROM hz_merge_parties
WHERE batch_id = l_batch_id
AND merge_type = 'PARTY_MERGE'
AND from_party_id = l_from_rel_party_id;
DELETE FROM HZ_MERGE_PARTIES
WHERE batch_id = l_batch_id
AND merge_type = 'PARTY_MERGE'
AND (from_party_id = l_from_rel_party_id
OR to_party_id = l_from_rel_party_id);
select pr1.relationship_code from_rel_type,
pr2.relationship_code to_rel_type
into l_from_rel_type,l_to_rel_type
from hz_relationships pr1, hz_relationships pr2 --bug 4500011 replaced hz_party_relationships with hz_relationships
where pr1.relationship_id = p_from_entity_id
and pr2.relationship_id = p_to_entity_id
AND pr1.subject_table_name = 'HZ_PARTIES'
AND pr1.object_table_name = 'HZ_PARTIES'
AND pr1.directional_flag = 'F'
AND pr2.subject_table_name = 'HZ_PARTIES'
AND pr2.object_table_name = 'HZ_PARTIES'
AND pr2.directional_flag = 'F';
HZ_MERGE_PARTIES_PKG.Insert_Row(
rel_batch_party_id,
l_BATCH_ID,
'PARTY_MERGE',
l_from_rel_party_id,
l_to_rel_party_id,
'DUPLICATE_RELN_PARTY',
'PENDING',
HZ_UTILITY_V2PUB.CREATED_BY,
HZ_UTILITY_V2PUB.CREATION_DATE,
HZ_UTILITY_V2PUB.LAST_UPDATE_LOGIN,
HZ_UTILITY_V2PUB.LAST_UPDATE_DATE,
HZ_UTILITY_V2PUB.LAST_UPDATED_BY);
insert_party_site_details(l_from_rel_party_id,
l_to_rel_party_id,
rel_batch_party_id,
'Y');
select batch_party_id into rel_batch_party_id
from hz_merge_parties
where batch_id = l_batch_id
and merge_type = 'PARTY_MERGE'
and from_party_id = l_from_rel_party_id;
HZ_MERGE_PARTIES_PKG.delete_Row(rel_batch_party_id);
DELETE FROM hz_merge_party_details
WHERE batch_party_id = rel_batch_party_id;
SELECT object_version_number
INTO db_object_version_number
FROM hz_merge_party_details
WHERE merge_from_entity_id = p_from_entity_id
AND batch_party_id = p_batch_party_id
AND entity_name = p_entity
FOR UPDATE OF merge_from_entity_id, batch_party_id,entity_name nowait;
UPDATE HZ_MERGE_PARTY_DETAILS
SET merge_to_entity_id = p_to_entity_id,
object_version_number = l_object_version_number
WHERE merge_from_entity_id = p_from_entity_id
AND batch_party_id = p_batch_party_id
AND entity_name = p_entity;
SELECT batch_id
INTO l_dup_set_id
FROM hz_merge_parties
WHERE batch_party_id = p_batch_party_id
AND ROWNUM = 1;
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
WHERE DUP_SET_ID = l_dup_set_id;
UPDATE HZ_MERGE_BATCH
SET batch_status = 'IN_PROCESS',
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
WHERE BATCH_ID = l_dup_set_id;
SELECT la.loc_id, la.org_id
FROM HZ_LOC_ASSIGNMENTS la, HZ_PARTY_SITES ps
WHERE ps.party_site_id = cp_from_ps_id
AND la.location_id = ps.location_id
MINUS
SELECT la.loc_id, la.org_id
FROM HZ_LOC_ASSIGNMENTS la, HZ_PARTY_SITES ps
WHERE ps.party_site_id = cp_to_ps_id
AND la.location_id = ps.location_id;
SELECT l.country,
l.city,
l.state,
l.county,
l.province,
l.postal_code,
ps.status,
ps.location_id --bug 4569674
FROM hz_locations l
, hz_party_sites ps
WHERE ps.party_site_id = cp_ps_id
AND ps.location_id = l.location_id;
select 1 from hz_cust_acct_sites_all where party_site_id=p_from_entity_id;
SELECT count(1)
FROM hz_merge_party_details
WHERE batch_party_id = cp_batch_party_id
AND entity_name = cp_entity_name
AND merge_from_entity_id = cp_from_entity_id;
SELECT DISTINCT batch_id, from_party_id, to_party_id ,merge_type
INTO l_batch_id, l_from_party_id, l_to_party_id , l_merge_type
FROM hz_merge_parties
WHERE batch_party_id = p_batch_party_id;
select ps1.party_id from_site_party_id ,
ps2.party_id to_site_party_id
into l_from_site_party_id, l_to_site_party_id
from hz_party_sites ps1 , hz_party_sites ps2
where ps1.party_site_id = p_from_entity_id
and ps2.party_site_id = p_to_entity_id;
HZ_MERGE_PARTY_DETAILS_PKG.Insert_Row(
p_batch_party_id,
'HZ_PARTY_SITES',
p_from_entity_id,
p_to_entity_id,
'N',
hz_utility_v2pub.created_by,
hz_utility_v2pub.creation_Date,
hz_utility_v2pub.last_update_login,
hz_utility_v2pub.last_update_date,
hz_utility_v2pub.last_updated_by);
HZ_MERGE_PARTY_DETAILS_PKG.delete_row(
p_batch_party_id, 'HZ_PARTY_SITES', p_from_entity_id);
select r1.relationship_type from_rel_type ,
r1.directional_flag from_dflag,
r2.relationship_type to_rel_type,
r2.directional_flag to_dflag
into l_from_rel_type, l_from_dflag,
l_to_rel_type , l_to_dflag
from hz_relationships r1, hz_relationships r2
where r1.relationship_id = p_from_entity_id
and r1.object_id = l_from_party_id
and r2.relationship_id = p_to_entity_id
and r2.object_id = l_to_party_id;
HZ_MERGE_PARTY_DETAILS_PKG.Insert_Row(
p_batch_party_id,
'HZ_PARTY_RELATIONSHIPS',
p_from_entity_id,
p_to_entity_id,
'N',
hz_utility_v2pub.created_by,
hz_utility_v2pub.creation_Date,
hz_utility_v2pub.last_update_login,
hz_utility_v2pub.last_update_date,
hz_utility_v2pub.last_updated_by);
SELECT count(1) INTO l_rel_party_count
FROM hz_merge_parties
WHERE batch_id = l_batch_id
AND merge_type = 'PARTY_MERGE'
AND from_party_id = l_from_rel_party_id;
HZ_MERGE_PARTIES_PKG.Insert_Row(
rel_batch_party_id,
l_batch_id,
'PARTY_MERGE',
l_from_rel_party_id,
l_to_rel_party_id,
'DUPLICATE_RELN_PARTY',
'PENDING',
HZ_UTILITY_V2PUB.CREATED_BY,
HZ_UTILITY_V2PUB.CREATION_DATE,
HZ_UTILITY_V2PUB.LAST_UPDATE_LOGIN,
HZ_UTILITY_V2PUB.LAST_UPDATE_DATE,
HZ_UTILITY_V2PUB.LAST_UPDATED_BY);
insert_party_site_details(l_from_rel_party_id,
l_to_rel_party_id,
rel_batch_party_id,'Y');
HZ_MERGE_PARTY_DETAILS_PKG.delete_row(
p_batch_party_id, 'HZ_PARTY_RELATIONSHIPS', p_from_entity_id);
SELECT count(1) INTO l_rel_party_count
FROM hz_merge_parties
WHERE batch_id = l_batch_id
AND merge_type = 'PARTY_MERGE'
AND from_party_id = l_from_rel_party_id;
SELECT batch_party_id into rel_batch_party_id
FROM hz_merge_parties
WHERE batch_id = l_batch_id
AND merge_type = 'PARTY_MERGE'
AND from_party_id = l_from_rel_party_id;
HZ_MERGE_PARTIES_PKG.delete_Row(rel_batch_party_id);
DELETE FROM hz_merge_party_details
WHERE batch_party_id = rel_batch_party_id;
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
WHERE DUP_SET_ID = (SELECT batch_id FROM hz_merge_parties
WHERE batch_party_id = p_batch_party_id
AND ROWNUM = 1);
UPDATE HZ_MERGE_BATCH
SET batch_status = 'IN_PROCESS',
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
WHERE BATCH_ID = (SELECT batch_id FROM hz_merge_parties
WHERE batch_party_id = p_batch_party_id
AND ROWNUM = 1);
SELECT batch_status , request_id
INTO l_batch_status ,l_last_request_id
FROM hz_merge_batch
WHERE batch_id = p_batch_id;
SELECT ds.status
INTO l_dup_set_status
FROM hz_dup_sets ds, hz_merge_batch mb
WHERE ds.dup_set_id = mb.batch_id
AND ds.dup_set_id = p_batch_id;
UPDATE HZ_MERGE_BATCH
SET batch_status = 'SUBMITTED' ,
request_id = l_request_id,
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
WHERE batch_id = p_batch_id;
UPDATE HZ_DUP_SETS
SET status = 'SUBMITTED',
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
WHERE dup_set_id = p_batch_id;
PROCEDURE insert_party_details( cp_batch_party_id IN NUMBER,
cp_from_party_id IN NUMBER,
cp_to_party_id IN NUMBER,
p_def_to_entity IN VARCHAR2 DEFAULT 'N') IS
BEGIN
-----Insert Party Site details
insert_party_site_details(
cp_from_party_id,
cp_to_party_id,
cp_batch_party_id,
p_def_to_entity);
insert_party_reln_details(
cp_from_party_id,
cp_to_party_id,
cp_batch_party_id,
p_def_to_entity);
END insert_party_details;
PROCEDURE insert_reln_parties(p_batch_party_id IN NUMBER,
p_batch_id IN NUMBER)IS
CURSOR merged_relns(cp_batch_party_id NUMBER) IS
SELECT merge_from_entity_id, merge_to_entity_id,
HZ_MERGE_UTIL.get_reln_party_id(merge_from_entity_id) from_reln_party_id,
HZ_MERGE_UTIL.get_reln_party_id(merge_to_entity_id) to_reln_party_id
FROM hz_merge_party_details
WHERE batch_party_id = cp_batch_party_id
AND entity_name = 'HZ_PARTY_RELATIONSHIPS'
AND merge_to_entity_id IS NOT NULL
AND merge_from_entity_id IS NOT NULL
AND merge_from_entity_id <> merge_to_entity_id;
select status into l_rel_status
from hz_parties
where party_id = l_to_reln_party_id;
select status into l_from_rel_status
from hz_parties
where party_id = l_from_reln_party_id;
HZ_MERGE_PARTIES_PKG.Insert_Row(
l_batch_party_id,
p_BATCH_ID,
'PARTY_MERGE',
l_from_reln_party_id,
l_to_reln_party_id,
'DUPLICATE_RELN_PARTY',
'PENDING',
HZ_UTILITY_V2PUB.CREATED_BY,
HZ_UTILITY_V2PUB.CREATION_DATE,
HZ_UTILITY_V2PUB.LAST_UPDATE_LOGIN,
HZ_UTILITY_V2PUB.LAST_UPDATE_DATE,
HZ_UTILITY_V2PUB.LAST_UPDATED_BY);
insert_party_site_details(l_from_reln_party_id,
l_to_reln_party_id,
l_batch_party_id,
'Y');
END insert_reln_parties;
PROCEDURE insert_party_site_details (
p_from_party_id IN NUMBER,
p_to_party_id IN NUMBER,
p_batch_party_id IN NUMBER,
p_reln_parties IN VARCHAR2 DEFAULT 'N'
) IS
--Cursor for inserting Party sites that are non-DNB
CURSOR c_from_ps_loc(merge_type VARCHAR2) IS
SELECT party_site_id, ps.location_id
FROM HZ_PARTY_SITES ps
WHERE ps.party_id = p_from_party_id
AND (merge_type = 'S' OR ps.actual_content_source <>'DNB')--Bug No.4114254
AND nvl(status, 'A') in ('A','I');
SELECT party_site_id
FROM HZ_PARTY_SITES ps
WHERE ps.party_id = p_to_party_id
AND ps.location_id = cp_loc_id
AND (merge_type = 'S' OR ps.actual_content_source <>'DNB')--Bug No. 4114254
AND nvl(status, 'A') in ('A','I');
SELECT dset.merge_type INTO l_merge_type
FROM HZ_DUP_SETS dset,HZ_MERGE_PARTIES mpar
WHERE dset.dup_set_id = mpar.batch_id
AND mpar.batch_party_id = p_batch_party_id;
HZ_MERGE_PARTY_DETAILS_PKG.Insert_Row(
p_batch_party_id,
'HZ_PARTY_SITES',
l_ps_id,
l_to_entity_id,
l_mandatory_merge,
hz_utility_v2pub.created_by,
hz_utility_v2pub.creation_Date,
hz_utility_v2pub.last_update_login,
hz_utility_v2pub.last_update_date,
hz_utility_v2pub.last_updated_by);
HZ_MERGE_PARTY_DETAILS_PKG.Insert_Row(
p_batch_party_id,
'HZ_PARTY_SITES',
l_ps_id,
l_to_entity_id,
'N',
hz_utility_v2pub.created_by,
hz_utility_v2pub.creation_Date,
hz_utility_v2pub.last_update_login,
hz_utility_v2pub.last_update_date,
hz_utility_v2pub.last_updated_by);
END insert_party_site_details;
PROCEDURE insert_party_reln_details (
p_from_party_id IN NUMBER,
p_to_party_id IN NUMBER,
p_batch_party_id IN NUMBER,
p_def_mapping IN VARCHAR2 DEFAULT 'N'
) IS
CURSOR c_from_reln(l_batch_id NUMBER,merge_type VARCHAR2) IS
SELECT relationship_id, subject_id, object_id,
relationship_code, actual_content_source, start_date, nvl(end_date,to_date('12/31/4712','MM/DD/YYYY'))
FROM HZ_RELATIONSHIPS r
WHERE (subject_id = p_from_party_id
OR object_id = p_from_party_id)
AND nvl(status, 'A') IN ('A','I')
AND directional_flag = 'F'
AND subject_table_name = 'HZ_PARTIES'
AND object_table_name = 'HZ_PARTIES'
AND (merge_type ='S' OR actual_content_source <> 'DNB')--Bug No. 4114254
AND not exists
( select 1
from HZ_MERGE_PARTIES a, HZ_MERGE_PARTY_DETAILS b
where a.batch_party_id = b.batch_party_id
and b.merge_from_entity_id = r.relationship_id
and b.entity_name = 'HZ_PARTY_RELATIONSHIPS'
and a.batch_id = l_batch_id );
SELECT relationship_id, start_date, nvl(end_date,to_date('12/31/4712','MM/DD/YYYY'))
FROM HZ_RELATIONSHIPS r
WHERE subject_id = cp_subj_id
AND object_id = cp_obj_id
AND relationship_code = cp_party_rel_code
--OR exists (select 1 from hz_relationship_types where relationship_type = cp_party_relationship_type
--and forward_code=backward_code))
AND ((start_date between from_start_date and from_end_date)
or (nvl(end_date,to_date('12/31/4712','MM/DD/YYYY')) between from_start_date and from_end_date)
or(start_datefrom_end_date))
AND nvl(status, 'A') IN ('A','I') --BugNo:2940087
--AND directional_flag = 'F' --BugNo:2940087
AND subject_table_name = 'HZ_PARTIES'
AND object_table_name = 'HZ_PARTIES'
AND (merge_type ='S' OR actual_content_source <> 'DNB') --Bug No. 4114254
AND not exists --4651128
( select 1
from HZ_MERGE_PARTIES a, HZ_MERGE_PARTY_DETAILS b
where a.batch_party_id = b.batch_party_id
and b.merge_from_entity_id = r.relationship_id
and b.entity_name = 'HZ_PARTY_RELATIONSHIPS'
and a.batch_id = c_batch_id );
SELECT dup_party_id
FROM hz_dup_set_parties
WHERE dup_set_id = c_batch_id
AND dup_party_id <> p_from_party_id
AND dup_party_id <> p_to_party_id;
SELECT relationship_id, start_date, nvl(end_date,to_date('12/31/4712','MM/DD/YYYY'))
FROM HZ_RELATIONSHIPS r
WHERE
relationship_id in (select distinct b.merge_to_entity_id
from HZ_MERGE_PARTIES a, HZ_MERGE_PARTY_DETAILS b
where a.batch_party_id = b.batch_party_id
and b.entity_name = 'HZ_PARTY_RELATIONSHIPS'
and a.batch_id = c_batch_id)
AND subject_id = cp_subj_id
AND relationship_code = cp_party_rel_code
AND ((start_date between from_start_date and from_end_date)
or (nvl(end_date,to_date('12/31/4712','MM/DD/YYYY')) between from_start_date and from_end_date)
or(start_datefrom_end_date))
AND nvl(status, 'A') IN ('A','I')
AND directional_flag = 'F'
AND subject_table_name = 'HZ_PARTIES'
AND object_table_name = 'HZ_PARTIES';
SELECT relationship_id, start_date, nvl(end_date,to_date('12/31/4712','MM/DD/YYYY'))
FROM HZ_RELATIONSHIPS r
WHERE
relationship_id in (select distinct b.merge_to_entity_id
from HZ_MERGE_PARTIES a, HZ_MERGE_PARTY_DETAILS b
where a.batch_party_id = b.batch_party_id
and b.entity_name = 'HZ_PARTY_RELATIONSHIPS'
and a.batch_id = c_batch_id)
AND object_id = cp_obj_id
AND relationship_code = cp_party_rel_code
AND ((start_date between from_start_date and from_end_date)
or (nvl(end_date,to_date('12/31/4712','MM/DD/YYYY')) between from_start_date and from_end_date)
or(start_datefrom_end_date))
AND nvl(status, 'A') IN ('A','I')
AND directional_flag = 'F'
AND subject_table_name = 'HZ_PARTIES'
AND object_table_name = 'HZ_PARTIES';
select 'Y' from hz_relationships where relationship_id=rel_id
and (subject_id IN (SELECT dup_party_id FROM HZ_dup_set_PARTIES WHERE dup_set_id=batch_id))
and (object_id IN (SELECT dup_party_id FROM HZ_dup_set_PARTIES WHERE dup_set_id=batch_id))
AND directional_flag='F';
SELECT relationship_id
FROM HZ_RELATIONSHIPS
WHERE object_id = p_to_party_id
AND subject_id = cp_subj_id
AND relationship_code = cp_party_relationship_type
AND nvl(status, 'A') = 'A'
AND directional_flag = 'F'
AND subject_table_name = 'HZ_PARTIES'
AND object_table_name = 'HZ_PARTIES'
AND actual_content_source <> 'DNB';
select status
from hz_relationships
where relationship_id = cp_id
and rownum = 1;
select merge_to_entity_id
from hz_merge_party_details
where merge_from_entity_id = merge_to_entity_id
and merge_from_entity_id = cp_id;
SELECT dset.merge_type, mpar.batch_id INTO l_merge_type, l_batch_id
FROM HZ_DUP_SETS dset,HZ_MERGE_PARTIES mpar
WHERE dset.dup_set_id = mpar.batch_id
AND mpar.batch_party_id = p_batch_party_id;
HZ_MERGE_PARTY_DETAILS_PKG.Insert_Row(
p_batch_party_id,
'HZ_PARTY_RELATIONSHIPS',
l_pr_id,
l_dup_pr_id,
l_mandatory_merge,
hz_utility_v2pub.created_by,
hz_utility_v2pub.creation_Date,
hz_utility_v2pub.last_update_login,
hz_utility_v2pub.last_update_date,
hz_utility_v2pub.last_updated_by);
HZ_MERGE_PARTY_DETAILS_PKG.Insert_Row(
p_batch_party_id,
'HZ_PARTY_RELATIONSHIPS',
l_pr_id,
l_pr_id,
'N',
hz_utility_v2pub.created_by,
hz_utility_v2pub.creation_Date,
hz_utility_v2pub.last_update_login,
hz_utility_v2pub.last_update_date,
hz_utility_v2pub.last_updated_by);
update HZ_MERGE_PARTY_DETAILS
set
merge_from_entity_id = l_dup_pr_id,
merge_to_entity_id = l_dup_pr_id
where merge_to_entity_id = l_pr_id
and merge_from_entity_id = merge_to_entity_id; --bug 608201
END insert_party_reln_details;
select merge_type
from HZ_DUP_SETS
where dup_set_id = l_dup_set_id;
DELETE FROM HZ_MERGE_PARTYDTLS_SUGG
WHERE batch_party_id in
( SELECT batch_party_id
FROM HZ_MERGE_PARTIES_SUGG
WHERE batch_id = p_batch_id );
DELETE FROM HZ_MERGE_PARTIES_SUGG
WHERE batch_id = p_batch_id;
INSERT INTO HZ_MERGE_PARTIES_SUGG
(
batch_party_id
,batch_id
,merge_type
,from_party_id
,to_party_id
,merge_reason_code
,merge_status
,created_by
,creation_date
,last_update_login
,last_update_date
,last_updated_by
)
SELECT
batch_party_id
,batch_id
,merge_type
,from_party_id
,to_party_id
,merge_reason_code
,merge_status
,created_by
,creation_date
,last_update_login
,last_update_date
,last_updated_by
FROM HZ_MERGE_PARTIES
WHERE batch_id = p_batch_id;
INSERT INTO HZ_MERGE_PARTYDTLS_SUGG
(
batch_party_id
,entity_name
,merge_from_entity_id
,merge_to_entity_id
,mandatory_merge
,created_by
,creation_date
,last_update_login
,last_update_date
,last_updated_by
,object_version_number
)
SELECT
batch_party_id
,entity_name
,merge_from_entity_id
,merge_to_entity_id
,mandatory_merge
,created_by
,creation_date
,last_update_login
,last_update_date
,last_updated_by
,object_version_number
FROM HZ_MERGE_PARTY_DETAILS
WHERE batch_party_id IN
( SELECT batch_party_id
FROM HZ_MERGE_PARTIES_SUGG
WHERE batch_id = p_batch_id );
select min(match_rule_id) into l_default_addr_rule
from HZ_MATCH_RULES_VL
where rule_name = 'DL ADDRESS DEFAULT';
select min(match_rule_id) into l_default_relat_rule
from HZ_MATCH_RULES_VL
where rule_name = 'DL RELATIONSHIP DEFAULT';
select merge_from_entity_id
from HZ_MERGE_PARTYDTLS_SUGG mpd
, HZ_MERGE_PARTIES_SUGG mp
, hz_party_sites ps
where mpd.batch_party_id = mp.batch_party_id
and mp.batch_id = p_batch_id
and ps.party_id = mp.from_party_id
and ps.party_site_id = mpd.merge_from_entity_id
and mpd.entity_name = 'HZ_PARTY_SITES'
and mpd.merge_to_entity_id = mpd.merge_from_entity_id
order by ps.status,mp.merge_type desc; -- make sure to process active sites first;
select batch_party_id, from_party_id
from HZ_MERGE_PARTIES_SUGG
where batch_id = p_batch_id
and merge_type = 'SAME_PARTY_MERGE';
select party_site_id
from HZ_PARTY_SITES ps
where party_id = l_master_party
and status in ('A','I')
and not exists
( select 1
from HZ_MERGE_PARTIES_SUGG mp
, HZ_MERGE_PARTYDTLS_SUGG mpd
where mp.batch_id = p_batch_id
and mp.batch_party_id = mpd.batch_party_id
and mpd.entity_name = 'HZ_PARTY_SITES'
and mpd.merge_from_entity_id = ps.party_site_id)
order by ps.status,decode(ps.actual_content_source,'DNB',1,2); -- make sure to process active sites first;
select 'X'
from HZ_MERGE_PARTIES_SUGG mp
, HZ_MERGE_PARTYDTLS_SUGG mpd
where mpd.merge_from_entity_id = l_from_site_id
and mpd.merge_from_entity_id <> mpd.merge_to_entity_id
and mpd.batch_party_id = mp.batch_party_id
and mp.batch_id = p_batch_id
and mpd.entity_name = 'HZ_PARTY_SITES';
select party_site_id
from HZ_MATCHED_PARTY_SITES_GT mps
where mps.search_context_id = l_search_ctx_id
and mps.party_site_id <> l_master_site
and not exists
( select 1
from HZ_MERGE_PARTIES_SUGG mp
, HZ_MERGE_PARTYDTLS_SUGG mpd
where mpd.merge_to_entity_id = mps.party_site_id
and mpd.batch_party_id = mp.batch_party_id
and mp.batch_id = p_batch_id
and mpd.entity_name = 'HZ_PARTY_SITES');
select 'X'
from HZ_MERGE_PARTIES_SUGG mp
, HZ_MERGE_PARTYDTLS_SUGG mpd
where mpd.merge_from_entity_id = l_from_site_id
and mpd.batch_party_id = mp.batch_party_id
and mp.batch_id = p_batch_id
and mpd.entity_name = 'HZ_PARTY_SITES';
SELECT party_site_id
FROM HZ_MATCHED_PARTY_SITES_GT
WHERE search_context_id = l_search_context_id;
select 1 from hz_cust_acct_sites_all where party_site_id=p_from_entity_id;
select o.orig_system
from hz_party_sites ps, hz_orig_systems_b o
where ps.party_site_id = p_party_site_id
and o.orig_system = ps.actual_content_source
and o.orig_system_type = 'PURCHASED';
,p_restrict_sql => ' PARTY_SITE_ID IN (SELECT /*+ SELECTIVE_PS */ MERGE_FROM_ENTITY_ID' ||
' FROM HZ_MERGE_PARTYDTLS_SUGG mpd, HZ_MERGE_PARTIES_SUGG mp' ||
' WHERE mpd.MERGE_TO_ENTITY_ID = mpd.MERGE_FROM_ENTITY_ID' ||
' AND mpd.BATCH_PARTY_ID = mp.BATCH_PARTY_ID' ||
' AND mpd.ENTITY_NAME = ''HZ_PARTY_SITES''' ||
' AND mp.BATCH_ID = '|| p_batch_id ||')'
,p_match_type => 'OR'
,x_search_ctx_id => l_search_ctx_id
,x_num_matches => l_num_matches
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
);
SELECT location_id into l_from_location_id
FROM hz_party_sites
WHERE party_site_id = l_temp_from_site;
SELECT location_id into l_to_location_id
FROM hz_party_sites
WHERE party_site_id = l_master_site;
DELETE FROM HZ_MATCHED_PARTY_SITES_GT
WHERE search_context_id = l_search_ctx_id
AND party_site_id = l_temp_from_site;
DELETE FROM HZ_MATCHED_PARTY_SITES_GT
WHERE search_context_id = l_search_ctx_id
AND party_site_id = l_temp_from_site;
UPDATE HZ_MERGE_PARTYDTLS_SUGG
SET merge_to_entity_id = l_master_site
, mandatory_merge = 'N'
, last_update_date = hz_utility_v2pub.last_update_date
, last_updated_by = hz_utility_v2pub.last_updated_by
, last_update_login = hz_utility_v2pub.last_update_login
WHERE batch_party_id IN
( SELECT batch_party_id
FROM HZ_MERGE_PARTIES_SUGG
WHERE batch_id = p_batch_id )
AND merge_from_entity_id IN
( SELECT party_site_id
FROM HZ_MATCHED_PARTY_SITES_GT matchps
, HZ_MERGE_PARTYDTLS_SUGG mpd
, HZ_MERGE_PARTIES_SUGG mps
WHERE matchps.search_context_id = l_search_ctx_id
AND matchps.party_site_id = mpd.merge_from_entity_id
AND mpd.entity_name = 'HZ_PARTY_SITES'
AND mpd.merge_to_entity_id = mpd.merge_from_entity_id
AND mpd.batch_party_id = mps.batch_party_id
AND mps.batch_id = p_batch_id
AND NOT EXISTS
( SELECT 1
FROM HZ_MERGE_PARTYDTLS_SUGG mpdi
WHERE mpdi.batch_party_id = mpd.batch_party_id
AND mpdi.merge_to_entity_id = matchps.party_site_id
AND mpdi.merge_to_entity_id <> mpdi.merge_from_entity_id
)
);
SELECT location_id into l_from_location_id
FROM hz_party_sites
WHERE party_site_id = l_merge_from_site;
SELECT location_id into l_to_location_id
FROM hz_party_sites
WHERE party_site_id = l_master_site;
INSERT INTO HZ_MERGE_PARTYDTLS_SUGG
(
batch_party_id
,entity_name
,merge_from_entity_id
,merge_to_entity_id
,mandatory_merge
,created_by
,creation_date
,last_update_login
,last_update_date
,last_updated_by
,object_version_number
)
VALUES
(
l_batch_party_id
,'HZ_PARTY_SITES'
,l_merge_from_site
,l_master_site
,'N'
,hz_utility_v2pub.created_by
,hz_utility_v2pub.creation_date
,hz_utility_v2pub.last_update_login
,hz_utility_v2pub.last_update_date
,hz_utility_v2pub.last_updated_by
,1
);
select rel.relationship_id, rel.relationship_type, rel.relationship_code, rel.object_id, rel.subject_id, rel.subject_type, rel.object_type
from HZ_MERGE_PARTYDTLS_SUGG mpd
, HZ_MERGE_PARTIES_SUGG mp
, HZ_RELATIONSHIPS rel
where mpd.batch_party_id = mp.batch_party_id
and mp.batch_id = p_batch_id
and mpd.entity_name = 'HZ_PARTY_RELATIONSHIPS'
and mpd.merge_from_entity_id = mpd.merge_to_entity_id
and mpd.merge_from_entity_id = rel.relationship_id
and rel.subject_id in (
select dup_party_id
from HZ_DUP_SET_PARTIES
where dup_set_id = p_batch_id
and nvl(merge_flag,'Y') <> 'N'
)
and rel.status = 'A' ; --Bug 13950724
select rel.relationship_id, rel.relationship_type, rel.relationship_code, rel.object_id, rel.subject_type, rel.object_type
from HZ_RELATIONSHIPS rel
where subject_id = l_master_party_id
and not exists
( select 1
from HZ_MERGE_PARTIES_SUGG mp
, HZ_MERGE_PARTYDTLS_SUGG mpd
, HZ_RELATIONSHIPS rel2
where mp.batch_id = p_batch_id
and mp.batch_party_id = mpd.batch_party_id
and mpd.entity_name = 'HZ_PARTY_RELATIONSHIPS'
and mpd.merge_from_entity_id = rel2.relationship_id
and rel2.relationship_id = rel.relationship_id
and rel2.object_id = rel.object_id
and rel2.subject_id = rel.subject_id
and rel2.subject_type = rel.subject_type
and rel2.object_Type = rel.object_type
and rel2.relationship_code = rel.relationship_code ) order by decode(rel.actual_content_source,'DNB',1,2);
select batch_party_id, from_party_id
from HZ_MERGE_PARTIES_SUGG
where batch_id = p_batch_id
and merge_type = 'SAME_PARTY_MERGE';
select relationship_id
from HZ_MATCHED_PARTIES_GT mpgt, HZ_RELATIONSHIPS rel
where mpgt.search_context_id = l_search_ctx_id
and mpgt.party_id <> l_reln_obj_id
and mpgt.party_id = rel.object_id
and rel.subject_id = l_master_party
and rel.relationship_code = l_reln_code
and rel.relationship_type = l_reln_type
and not exists
( select 1
from HZ_MERGE_PARTIES_SUGG mp
, HZ_MERGE_PARTYDTLS_SUGG mpd
where mpd.merge_to_entity_id = rel.relationship_id
and mpd.batch_party_id = mp.batch_party_id
and mp.batch_id = p_batch_id
and mpd.entity_name = 'HZ_PARTY_RELATIONSHIPS');
select 'X'
from HZ_MERGE_PARTIES_SUGG mp
, HZ_MERGE_PARTYDTLS_SUGG mpd
where mpd.merge_from_entity_id = l_from_rel_id
and mpd.batch_party_id = mp.batch_party_id
and mp.batch_id = p_batch_id
and mpd.entity_name = 'HZ_PARTY_RELATIONSHIPS';
select o.orig_system
from hz_orig_systems_b o,hz_relationships r
where r.relationship_id = rel_id
and o.orig_system = r.actual_content_source
and o.orig_system_type = 'PURCHASED'
and directional_flag = 'F';
select status
from hz_relationships
where relationship_id = cp_id
and rownum = 1;
,p_restrict_sql => 'stage.PARTY_ID IN (SELECT /*+ SELECTIVE */ OBJECT_ID' ||
' FROM HZ_RELATIONSHIPS rel' ||
' WHERE rel.SUBJECT_TYPE = '''||l_reln_sbj_type||''''||
' AND rel.OBJECT_TYPE = '''||l_reln_obj_type||''''||
' AND rel.RELATIONSHIP_TYPE = '''||l_reln_type||''''||
' AND rel.SUBJECT_ID IN (select dup_party_id' ||
' from HZ_DUP_SET_PARTIES where dup_set_id = '|| p_batch_id||
' and nvl(merge_flag,''Y'''||')'||' <> ''N'''||')'||
' AND rel.RELATIONSHIP_CODE = '''|| l_reln_code||''' )'
,p_match_type => 'OR'
,p_dup_batch_id => NULL
,p_search_merged => 'N'
,x_dup_set_id => l_dup_set_id
,x_search_ctx_id => l_search_ctx_id
,x_num_matches => l_num_matches
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
);
select o.orig_system,o.orig_system_type into l_to_orig_system,l_to_orig_system_type
from hz_orig_systems_b o,hz_relationships r
where r.relationship_id = l_reln_id
and o.orig_system = r.actual_content_source
and directional_flag = 'F';
select relationship_id into l_reln_id --get active rel id
from hz_relationships
where object_id = l_reln_obj_id
and status = 'A'
and rownum = 1;
UPDATE HZ_MERGE_PARTYDTLS_SUGG
SET merge_to_entity_id = l_reln_id
, mandatory_merge = 'N'
, last_update_date = hz_utility_v2pub.last_update_date
, last_updated_by = hz_utility_v2pub.last_updated_by
, last_update_login = hz_utility_v2pub.last_update_login
WHERE batch_party_id in
( SELECT batch_party_id
FROM HZ_MERGE_PARTIES_SUGG
WHERE batch_id = p_batch_id )
AND merge_from_entity_id IN
( SELECT rel.relationship_id
FROM HZ_MATCHED_PARTIES_GT matchpty
, HZ_MERGE_PARTYDTLS_SUGG mpd
, HZ_MERGE_PARTIES_SUGG mps
, HZ_RELATIONSHIPS rel
, HZ_ORIG_SYSTEMS_B O
WHERE matchpty.search_context_id = l_search_ctx_id
AND matchpty.party_id = rel.object_id
AND rel.relationship_code = l_reln_code
AND rel.relationship_type = l_reln_type
AND rel.relationship_id = mpd.merge_from_entity_id
AND rel.subject_type = l_reln_sbj_type
AND rel.object_type = l_reln_obj_type
AND mpd.entity_name = 'HZ_PARTY_RELATIONSHIPS'
AND mpd.merge_to_entity_id = mpd.merge_from_entity_id
AND mpd.batch_party_id = mps.batch_party_id
AND mps.batch_id = p_batch_id
AND o.orig_system = rel.actual_content_source
AND decode(o.orig_system_type,'PURCHASED',(decode(l_to_orig_system_type,'PURCHASED',(decode(o.orig_system,l_to_orig_system,1,0)),0)),1)= 1
AND NOT EXISTS
( SELECT 1
FROM HZ_MERGE_PARTYDTLS_SUGG mpdi,
HZ_MERGE_PARTIES_SUGG mpsi
WHERE mpdi.batch_party_id = mpsi.batch_party_id
AND mpsi.batch_id = mps.batch_id
AND mpdi.merge_to_entity_id = rel.relationship_id
AND mpdi.merge_to_entity_id <> mpdi.merge_from_entity_id
)
) RETURNING merge_from_entity_id BULK COLLECT INTO l_merge_from_reln_tbl;
select count(1) into l_rel_party_count
from HZ_MERGE_PARTIES
where batch_id = p_batch_id
and merge_type = 'PARTY_MERGE'
and merge_reason_code = 'DUPLICATE_RELN_PARTY'
and from_party_id = l_from_rel_party_id;
insert_sugg_reln_party(p_batch_id
,l_from_rel_party_id
,l_to_rel_party_id
,l_reln_bpty_id);
insert_sugg_reln_ps_details(l_from_rel_party_id
,l_to_rel_party_id
,l_reln_bpty_id, 'Y');
END LOOP; --count the number of reln_id and loop to insert relationship's party
,p_restrict_sql => 'stage.PARTY_ID IN (SELECT /*+ SELECTIVE */ OBJECT_ID' ||
' FROM HZ_RELATIONSHIPS rel' ||
' WHERE rel.SUBJECT_ID = '|| l_master_party||
' AND rel.SUBJECT_TYPE = '''||l_reln_sbj_type||''''||
' AND rel.OBJECT_TYPE = '''||l_reln_obj_type||''''||
' AND rel.relationship_type = '''||l_reln_type||''''||
' AND rel.relationship_code = '''|| l_reln_code||''' )'
,p_match_type => 'OR'
,p_dup_batch_id => NULL
,p_search_merged => 'N'
,x_dup_set_id => l_dup_set_id
,x_search_ctx_id => l_search_ctx_id
,x_num_matches => l_num_matches
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
);
INSERT INTO HZ_MERGE_PARTYDTLS_SUGG
(
batch_party_id
,entity_name
,merge_from_entity_id
,merge_to_entity_id
,mandatory_merge
,created_by
,creation_date
,last_update_login
,last_update_date
,last_updated_by
,object_version_number
)
VALUES
(
l_batch_party_id
,'HZ_PARTY_RELATIONSHIPS'
,l_merge_from_reln
,l_reln_id
,'N'
,hz_utility_v2pub.created_by
,hz_utility_v2pub.creation_date
,hz_utility_v2pub.last_update_login
,hz_utility_v2pub.last_update_date
,hz_utility_v2pub.last_updated_by
,1
);
select count(1) into l_rel_party_count
from HZ_MERGE_PARTIES
where batch_id = p_batch_id
and merge_type = 'PARTY_MERGE'
and merge_reason_code = 'DUPLICATE_RELN_PARTY'
and from_party_id = l_from_rel_party_id;
-- insert party relationship merge record
insert_sugg_reln_party(p_batch_id
,l_from_rel_party_id
,l_to_rel_party_id
,l_reln_bpty_id);
-- insert relationship party's sites record
insert_sugg_reln_ps_details(l_from_rel_party_id
,l_to_rel_party_id
,l_reln_bpty_id, 'Y');
PROCEDURE insert_sugg_reln_ps_details (
p_from_party_id IN NUMBER,
p_to_party_id IN NUMBER,
p_batch_party_id IN NUMBER,
p_reln_parties IN VARCHAR2 DEFAULT 'N'
) IS
--Cursor for inserting Party sites that are non-DNB
CURSOR c_from_ps_loc IS
SELECT party_site_id, ps.location_id
FROM HZ_PARTY_SITES ps
WHERE ps.party_id = p_from_party_id
AND ps.actual_content_source <>'DNB'
AND nvl(status, 'A') in ('A','I');
SELECT party_site_id
FROM HZ_PARTY_SITES ps
WHERE ps.party_id = p_to_party_id
AND ps.location_id = cp_loc_id
AND ps.actual_content_source <>'DNB'
AND nvl(status, 'A') in ('A','I');
INSERT INTO HZ_MERGE_PARTYDTLS_SUGG
(
batch_party_id
,entity_name
,merge_from_entity_id
,merge_to_entity_id
,mandatory_merge
,created_by
,creation_date
,last_update_login
,last_update_date
,last_updated_by
,object_version_number
)
VALUES
(
p_batch_party_id
,'HZ_PARTY_SITES'
,l_ps_id
,l_dup_ps_id
,l_mandatory_merge
,hz_utility_v2pub.created_by
,hz_utility_v2pub.creation_date
,hz_utility_v2pub.last_update_login
,hz_utility_v2pub.last_update_date
,hz_utility_v2pub.last_updated_by
,1
);
INSERT INTO HZ_MERGE_PARTYDTLS_SUGG
(
batch_party_id
,entity_name
,merge_from_entity_id
,merge_to_entity_id
,mandatory_merge
,created_by
,creation_date
,last_update_login
,last_update_date
,last_updated_by
,object_version_number
)
VALUES
(
p_batch_party_id
,'HZ_PARTY_SITES'
,l_ps_id
,null
,'N'
,hz_utility_v2pub.created_by
,hz_utility_v2pub.creation_date
,hz_utility_v2pub.last_update_login
,hz_utility_v2pub.last_update_date
,hz_utility_v2pub.last_updated_by
,1
);
INSERT INTO HZ_MERGE_PARTYDTLS_SUGG
(
batch_party_id
,entity_name
,merge_from_entity_id
,merge_to_entity_id
,mandatory_merge
,created_by
,creation_date
,last_update_login
,last_update_date
,last_updated_by
,object_version_number
)
VALUES
(
p_batch_party_id
,'HZ_PARTY_SITES'
,l_ps_id
,l_ps_id
,'N'
,hz_utility_v2pub.created_by
,hz_utility_v2pub.creation_date
,hz_utility_v2pub.last_update_login
,hz_utility_v2pub.last_update_date
,hz_utility_v2pub.last_updated_by
,1
);
END insert_sugg_reln_ps_details;
PROCEDURE insert_sugg_reln_party(
p_batch_id IN NUMBER,
p_from_rel_party_id IN NUMBER,
p_to_rel_party_id IN NUMBER,
x_batch_party_id OUT NOCOPY NUMBER
) IS
BEGIN
select HZ_MERGE_PARTIES_S.nextval into x_batch_party_id
from dual;
INSERT INTO HZ_MERGE_PARTIES_SUGG
(
BATCH_PARTY_ID
,BATCH_ID
,MERGE_TYPE
,FROM_PARTY_ID
,TO_PARTY_ID
,MERGE_REASON_CODE
,MERGE_STATUS
,created_by
,creation_date
,last_update_login
,last_update_date
,last_updated_by
)
VALUES
(
x_batch_party_id
,p_batch_id
,'PARTY_MERGE'
,p_from_rel_party_id
,p_to_rel_party_id
,'DUPLICATE_RELN_PARTY'
,'PENDING'
,hz_utility_v2pub.created_by
,hz_utility_v2pub.creation_date
,hz_utility_v2pub.last_update_login
,hz_utility_v2pub.last_update_date
,hz_utility_v2pub.last_updated_by
);
END insert_sugg_reln_party;
DELETE FROM HZ_MERGE_PARTY_DETAILS
WHERE BATCH_PARTY_ID IN
( SELECT BATCH_PARTY_ID
FROM HZ_MERGE_PARTIES
WHERE BATCH_ID = p_batch_id )
AND ENTITY_NAME = p_entity_name;
INSERT INTO HZ_MERGE_PARTY_DETAILS
(
batch_party_id
,entity_name
,merge_from_entity_id
,merge_to_entity_id
,mandatory_merge
,created_by
,creation_date
,last_update_login
,last_updated_by
,last_update_date
,object_version_number
)
SELECT
batch_party_id
,entity_name
,merge_from_entity_id
,merge_to_entity_id
,mandatory_merge
,hz_utility_v2pub.created_by
,hz_utility_v2pub.creation_date
,hz_utility_v2pub.last_update_login
,hz_utility_v2pub.last_updated_by
,hz_utility_v2pub.last_update_date
,1
FROM HZ_MERGE_PARTYDTLS_SUGG
WHERE entity_name = p_entity_name
AND batch_party_id IN
( SELECT batch_party_id
FROM HZ_MERGE_PARTIES_SUGG
WHERE batch_id = p_batch_id );
DELETE FROM HZ_MERGE_PARTIES
WHERE batch_id = p_batch_id
AND merge_reason_code = 'DUPLICATE_RELN_PARTY';
INSERT INTO HZ_MERGE_PARTIES
(
batch_party_id
,batch_id
,merge_type
,from_party_id
,to_party_id
,merge_reason_code
,merge_status
,created_by
,creation_date
,last_update_login
,last_updated_by
,last_update_date
)
SELECT
batch_party_id
,batch_id
,merge_type
,from_party_id
,to_party_id
,merge_reason_code
,merge_status
,created_by
,creation_date
,last_update_login
,last_updated_by
,last_update_date
FROM HZ_MERGE_PARTIES_SUGG mp
WHERE mp.batch_id = p_batch_id
AND mp.merge_reason_code = 'DUPLICATE_RELN_PARTY';
SELECT HZ_MERGE_UTIL.get_reln_party_id(a.merge_from_entity_id)
, HZ_MERGE_UTIL.get_reln_party_id(a.merge_from_entity_id)
FROM HZ_MERGE_PARTYDTLS_SUGG a
, HZ_MERGE_PARTIES_SUGG b
WHERE b.batch_id = p_batch_id
AND a.entity_name = 'HZ_PARTY_RELATIONSHIPS'
AND a.batch_party_id = b.batch_party_id
AND a.mandatory_merge = 'Y';
UPDATE HZ_MERGE_PARTY_DETAILS mpd
SET mpd.merge_from_entity_id =
( SELECT merge_from_entity_id
FROM HZ_MERGE_PARTYDTLS_SUGG mps
WHERE mpd.batch_party_id = mps.batch_party_id
AND mpd.merge_from_entity_id = mps.merge_from_entity_id
AND mpd.entity_name = mps.entity_name ),
mpd.mandatory_merge =
( SELECT mandatory_merge
FROM HZ_MERGE_PARTYDTLS_SUGG mps
WHERE mpd.batch_party_id = mps.batch_party_id
AND mpd.merge_from_entity_id = mps.merge_from_entity_id
AND mpd.entity_name = mps.entity_name ),
mpd.last_update_login = HZ_UTILITY_V2PUB.LAST_UPDATE_LOGIN ,
mpd.last_updated_by = HZ_UTILITY_V2PUB.LAST_UPDATED_BY,
mpd.last_update_date = HZ_UTILITY_V2PUB.LAST_UPDATE_DATE,
mpd.object_version_number = nvl(mpd.object_version_number,1)+1
WHERE entity_name = p_entity_name
AND batch_party_id IN
( SELECT batch_party_id
FROM HZ_MERGE_PARTIES_SUGG
WHERE batch_id = p_batch_id );
UPDATE HZ_MERGE_PARTY_DETAILS
SET merge_to_entity_id = merge_from_entity_id
WHERE mandatory_merge <> 'Y'
AND entity_name = p_entity_name
AND batch_party_id IN
( SELECT batch_party_id
FROM HZ_MERGE_PARTIES
WHERE batch_id = p_batch_id );
DELETE HZ_MERGE_PARTY_DETAILS
WHERE entity_name = p_entity_name
AND batch_party_id IN
( SELECT batch_party_id
FROM HZ_MERGE_PARTIES
WHERE batch_id = p_batch_id );
INSERT INTO HZ_MERGE_PARTY_DETAILS
(
batch_party_id
,entity_name
,merge_from_entity_id
,merge_to_entity_id
,mandatory_merge
,created_by
,creation_date
,last_update_login
,last_updated_by
,last_update_date
,object_version_number
)
SELECT
batch_party_id
,entity_name
,merge_from_entity_id
,merge_to_entity_id
,mandatory_merge
,hz_utility_v2pub.created_by
,hz_utility_v2pub.creation_date
,hz_utility_v2pub.last_update_login
,hz_utility_v2pub.last_updated_by
,hz_utility_v2pub.last_update_date
,1
FROM HZ_MERGE_PARTYDTLS_SUGG
WHERE mandatory_merge = 'Y'
AND entity_name = p_entity_name
AND batch_party_id IN
( SELECT batch_party_id
FROM HZ_MERGE_PARTIES_SUGG
WHERE batch_id = p_batch_id );
DELETE FROM HZ_MERGE_PARTIES mp
WHERE mp.batch_id = p_batch_id
AND mp.merge_reason_code = 'DUPLICATE_RELN_PARTY';
insert_sugg_reln_party(p_batch_id
,l_reln_from_pid
,l_reln_to_pid
,l_reln_bpty_id);
insert_sugg_reln_ps_details(l_reln_from_pid
,l_reln_to_pid
,l_reln_bpty_id, 'Y');
select to_rel.subject_id, count(1)
from HZ_MERGE_PARTY_DETAILS mpd, HZ_MERGE_PARTIES mp
, HZ_RELATIONSHIPS from_rel, HZ_RELATIONSHIPS to_rel
, HZ_PARTIES from_pty, HZ_PARTIES to_pty
where mpd.batch_party_id = mp.batch_party_id
and mp.batch_id = p_batch_id
and mpd.entity_name = 'HZ_PARTY_RELATIONSHIPS'
and mpd.merge_from_entity_id = from_rel.relationship_id
and mpd.merge_to_entity_id = to_rel.relationship_id
and from_rel.object_id =
( select winner_party_id
from HZ_DUP_SETS where dup_set_id = p_batch_id )
and to_rel.object_id =
( select winner_party_id
from HZ_DUP_SETS where dup_set_id = p_batch_id )
and from_rel.subject_id = from_pty.party_id
and to_rel.subject_id = to_pty.party_id
group by to_rel.subject_id;
select to_rel.subject_id, count(1)
from HZ_MERGE_PARTY_DETAILS mpd, HZ_MERGE_PARTIES mp
, HZ_RELATIONSHIPS from_rel, HZ_RELATIONSHIPS to_rel
, HZ_PARTIES from_pty, HZ_PARTIES to_pty
where mpd.batch_party_id = mp.batch_party_id
and mpd.merge_from_entity_id <> mpd.merge_to_entity_id
and mp.batch_id = p_batch_id
and mpd.entity_name = 'HZ_PARTY_RELATIONSHIPS'
and mpd.merge_from_entity_id = from_rel.relationship_id
and mpd.merge_to_entity_id = to_rel.relationship_id
and from_rel.object_id in
( select dup_party_id
from HZ_DUP_SET_PARTIES
where dup_set_id = p_batch_id
and nvl(merge_flag,'Y') <> 'N' )
and to_rel.object_id in
( select dup_party_id
from HZ_DUP_SET_PARTIES
where dup_set_id = p_batch_id
and nvl(merge_flag,'Y') <> 'N' )
and from_rel.subject_id = from_pty.party_id
and to_rel.subject_id = to_pty.party_id
group by to_rel.subject_id;
select from_rel.subject_id
from HZ_MERGE_PARTY_DETAILS mpd, HZ_MERGE_PARTIES mp
, HZ_RELATIONSHIPS from_rel, HZ_RELATIONSHIPS to_rel
, HZ_PARTIES from_pty, HZ_PARTIES to_pty
where mpd.batch_party_id = mp.batch_party_id
and mp.batch_id = p_batch_id
and mpd.entity_name = 'HZ_PARTY_RELATIONSHIPS'
and mpd.merge_from_entity_id = from_rel.relationship_id
and mpd.merge_to_entity_id = to_rel.relationship_id
and from_rel.object_id =
( select winner_party_id
from HZ_DUP_SETS where dup_set_id = p_batch_id )
and to_rel.object_id =
( select winner_party_id
from HZ_DUP_SETS where dup_set_id = p_batch_id )
and from_rel.subject_id = from_pty.party_id
and to_rel.subject_id = l_to_party_id
and to_rel.subject_id = to_pty.party_id;
select from_rel.subject_id
from HZ_MERGE_PARTY_DETAILS mpd, HZ_MERGE_PARTIES mp
, HZ_RELATIONSHIPS from_rel, HZ_RELATIONSHIPS to_rel
, HZ_PARTIES from_pty, HZ_PARTIES to_pty
where mpd.batch_party_id = mp.batch_party_id
and mpd.merge_from_entity_id <> mpd.merge_to_entity_id
and mp.batch_id = p_batch_id
and mpd.entity_name = 'HZ_PARTY_RELATIONSHIPS'
and mpd.merge_from_entity_id = from_rel.relationship_id
and mpd.merge_to_entity_id = to_rel.relationship_id
and from_rel.object_id in
( select dup_party_id
from HZ_DUP_SET_PARTIES
where dup_set_id = p_batch_id
and nvl(merge_flag,'Y') <> 'N' )
and to_rel.object_id in
( select dup_party_id
from HZ_DUP_SET_PARTIES
where dup_set_id = p_batch_id
and nvl(merge_flag,'Y') <> 'N' )
and from_rel.subject_id = from_pty.party_id
and to_rel.subject_id = to_pty.party_id
and to_rel.subject_id = l_to_party_id;
select party_name
from HZ_DUP_SETS a, HZ_PARTIES b
where a.winner_party_id = b.party_id
and a.dup_set_id = p_batch_id;
select party_name
from HZ_PARTIES
where party_id = l_party_id;
select count(*)
from hz_merge_batch
where batch_id = p_batch_id
and created_by_module = 'DL_DONESUGG';
update hz_merge_batch
set created_by_module = 'DL_DONESUGG'
where batch_id = p_batch_id;
l_dup_party_tbl.DELETE ;
l_dup_party_tbl.DELETE ;
PROCEDURE delete_mapping (
p_batch_id IN NUMBER
,p_merge_type IN VARCHAR2
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2 )
IS
l_batch_id NUMBER;
savepoint delete_mapping;
DELETE FROM HZ_MERGE_ENTITY_ATTRIBUTES
WHERE merge_batch_id = l_batch_id;
SELECT decode(pty.party_type,'PERSON','HZ_PERSON_PROFILES',
'ORGANIZATION','HZ_ORGANIZATION_PROFILES',
'HZ_ORGANIZATION_PROFILES'),
a.winner_party_id
INTO l_party_type, l_merge_to
FROM HZ_DUP_SETS a, HZ_PARTIES pty
WHERE a.dup_set_id = p_batch_id
AND a.winner_party_id = pty.party_id;
ROLLBACK to delete_mapping;
ROLLBACK to delete_mapping;
ROLLBACK to delete_mapping;
ROLLBACK TO delete_mapping;
ROLLBACK TO delete_mapping;
ROLLBACK TO delete_mapping;
END delete_mapping;
SELECT mpd.merge_from_entity_id, mpd.batch_party_id, mpd.object_version_number
from hz_merge_parties mp, hz_merge_party_details mpd
WHERE mp.batch_id=cp_merge_batch_id
AND mpd.entity_name = p_entity
AND mp.batch_party_id = mpd.batch_party_id
AND mpd.merge_from_entity_id <> cp_entity_id
AND mpd.merge_to_entity_id = cp_entity_id;
select party_number
from hz_parties
where party_id = p_party_id;
/* select 'x'
from hz_merge_parties mp
where mp.batch_id = p_merge_batch_id
and not exists ( select 'x'
from hz_dup_set_parties dsp
where dsp.dup_set_id = mp.batch_id
and mp.batch_id = p_merge_batch_id); */
select 'x'
from hz_merge_batch
where batch_id = p_merge_batch_id
and nvl(created_by_module,'##') <> 'DL';
select party.party_number,ds.object_version_number
from hz_parties party, hz_dup_sets ds, hz_dup_set_parties dsp, hz_dup_batch db
where party.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 party.status = 'M'
and ds.dup_set_id = p_dup_set_id;
select distinct mp.batch_id,dsp.dup_party_id,ds.object_version_number
from hz_merge_batch mb, hz_merge_parties mp,
hz_dup_sets ds, hz_dup_set_parties dsp, hz_dup_batch db
where mp.batch_id <> ds.dup_set_id
and mb.batch_id = mp.batch_id
and db.dup_batch_id = ds.dup_batch_id
and ds.dup_set_id = dsp.dup_set_id
and dsp.dup_party_id = mp.from_party_id -- check only from id overlapping
and nvl(dsp.merge_flag,'Y') <> 'N'
and mb.batch_status not in ('COMPLETE','PART_COMPLETE')
and ds.dup_set_id = p_dup_set_id;
select distinct party.party_number
from hz_parties party, hz_merge_parties mp, hz_merge_batch mb
where (party.party_id = mp.from_party_id or party.party_id = mp.to_party_id)
and party.status = 'M'
and mp.batch_id = p_merge_batch_id
and mb.batch_id = mp.batch_id
and mb.batch_status not in ('COMPLETE','PART_COMPLETE'); --4114041
select object_version_number
from hz_dup_sets
where dup_set_id = p_merge_batch_id;
select mp2.batch_party_id
from hz_parties p1, hz_merge_parties mp2
where p1.party_id = mp2.from_party_id
and p1.status = 'M'
and mp2.merge_reason_code = 'DUPLICATE_RELN_PARTY'
and mp2.batch_id = p_merge_batch_id;
-- Update dup set status to 'Error'
UPDATE HZ_DUP_SETS
SET STATUS = 'ERROR',
OBJECT_VERSION_NUMBER = nvl(OBJECT_VERSION_NUMBER,1)+1,
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
WHERE DUP_SET_ID = p_dup_set_id;
-- bug 5094383: delete merged relationship parties
open get_merged_rel_party_csr;
delete from hz_merge_parties where batch_party_id = l_batch_party_id;
delete from hz_merge_party_details where batch_party_id = l_batch_party_id;
select hz_format_pub.format_address(l.location_id,null,null,', ')
from hz_party_sites ps, hz_locations l
where ps.location_id = l.location_id
and ps.party_site_id = p_to_site_id;
select hz_format_pub.format_address(l.location_id,null,null,', ')
from hz_merge_parties p,
hz_merge_party_details pd,
hz_party_sites s,
hz_locations l,
hz_parties hp
where p.batch_party_id = pd.batch_party_id
and pd.entity_name = 'HZ_PARTY_SITES'
and pd.merge_from_entity_id = s.party_site_id
and s.location_id = l.location_id
and hp.party_id = p.from_party_id
and pd.merge_to_entity_id = p_to_site_id;
SELECT 'Y'
FROM hz_party_sites ps1,
hz_cust_acct_sites_all as1,
hz_cust_accounts ca1,
hz_merge_parties p1,
hz_merge_party_details pd1
WHERE p1.batch_id = p_merge_batch_id
AND ps1.party_site_id = as1.party_site_id
and ca1.cust_account_id = as1.cust_account_id
and p1.batch_party_id = pd1.batch_party_id
and pd1.entity_name = 'HZ_PARTY_SITES'
and pd1.merge_from_entity_id = ps1.party_site_id
and pd1.merge_from_entity_id <> pd1.merge_to_entity_id
AND exists
( select 1 from hz_party_sites ps2,
hz_cust_acct_sites_all as2,
hz_merge_parties p2,
hz_merge_party_details pd2
where p2.batch_id = p_merge_batch_id
and ps2.party_site_id = as2.party_site_id
and as2.cust_account_id = as1.cust_account_id
and as2.org_id = as1.org_id
and p2.batch_party_id = pd2.batch_party_id
and pd2.entity_name = 'HZ_PARTY_SITES'
and pd2.merge_to_entity_id = ps2.party_site_id
and pd2.merge_from_entity_id <> pd2.merge_to_entity_id
and rownum = 1);
SELECT distinct ca1.account_number, pd1.merge_to_entity_id
FROM hz_party_sites ps1,
hz_cust_acct_sites_all as1,
hz_cust_accounts ca1,
hz_merge_parties p1,
hz_merge_party_details pd1
WHERE p1.batch_id = p_merge_batch_id
AND ps1.party_site_id = as1.party_site_id
and ca1.cust_account_id = as1.cust_account_id
and p1.batch_party_id = pd1.batch_party_id
and pd1.entity_name = 'HZ_PARTY_SITES'
and pd1.merge_from_entity_id = ps1.party_site_id
and pd1.merge_from_entity_id <> pd1.merge_to_entity_id
AND exists
( select 1 from hz_party_sites ps2,
hz_cust_acct_sites_all as2,
hz_merge_parties p2,
hz_merge_party_details pd2
where p2.batch_id = p_merge_batch_id
and ps2.party_site_id = as2.party_site_id
and as2.cust_account_id = as1.cust_account_id
and as2.org_id = as1.org_id
and p2.batch_party_id = pd2.batch_party_id
and pd2.entity_name = 'HZ_PARTY_SITES'
and pd2.merge_to_entity_id = ps2.party_site_id
and pd2.merge_from_entity_id <> pd2.merge_to_entity_id
and rownum = 1);