[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE delete_merged_records(
p_batch_party_id IN NUMBER,
x_return_status IN OUT NOCOPY VARCHAR2);
PROCEDURE insert_party_site_details (
p_from_party_id IN NUMBER,
p_to_party_id IN NUMBER,
p_batch_party_id IN NUMBER
);
g_user_id HZ_MERGE_PARTY_HISTORY.last_updated_by%TYPE;
g_last_update_login HZ_MERGE_PARTY_HISTORY.last_update_login%TYPE;
g_last_update_date DATE;
g_merge_delete_flag VARCHAR2(1);
g_last_update_login:=hz_utility_pub.last_update_login;
g_last_update_date :=hz_utility_pub.last_update_date;
FND_FILE.put_line(FND_FILE.log,'Last Update Date:'||TO_CHAR(g_last_update_date)||'#');
FND_FILE.put_line(FND_FILE.log,'Last Update Login:'||g_last_update_login||'#');
g_skip_dict_id.DELETE;
SELECT DISTINCT MERGE_DICT_ID, ENTITY_NAME
FROM HZ_MERGE_DICTIONARY WHERE DICT_APPLICATION_ID<>222
AND NVL(batch_merge_flag, 'N') <> 'Y') LOOP
BEGIN
--4534175 EXECUTE IMMEDIATE 'DECLARE x NUMBER; BEGIN SELECT 1 INTO x FROM dual where exists ( select 1 from '||ENTITY.ENTITY_NAME||'); END;';
OPEN c1 FOR 'SELECT 1 FROM jtf_fm_content_history WHERE rownum = 1';
OPEN c1 FOR 'SELECT 1 FROM jtf_fm_processed WHERE rownum = 1';
OPEN c1 FOR 'select 1
from dual
where exists(
SELECT /*+ first_rows(1) index_ffs(hp HZ_PARTY_SITE_USES_N1)*/ 1
FROM hz_party_site_uses hp
WHERE site_use_type = ''SUPPLIER_SHIP_FROM''
AND ROWNUM=1) ';
OPEN c1 FOR 'SELECT 1 FROM '||ENTITY.ENTITY_NAME||' WHERE rownum = 1';
SELECT batch_name, rule_set_name, batch_status, batch_delete, batch_commit
FROM HZ_MERGE_BATCH
WHERE batch_id = cp_batch_id;
SELECT DISTINCT(to_party_id)
FROM HZ_MERGE_PARTIES mp, hz_parties p
WHERE batch_id = cp_batch_id
AND p.party_id = mp.from_party_id
AND p.party_type <> 'PARTY_RELATIONSHIP';
SELECT mp.merge_type
FROM HZ_MERGE_PARTIES mp, hz_parties p
WHERE mp.batch_id = cp_batch_id
AND mp.to_party_id = cp_to_party_id
AND p.party_id = mp.to_party_id
AND p.party_type <> 'PARTY_RELATIONSHIP'
AND mp.merge_type = 'PARTY_MERGE'
AND rownum=1;
SELECT batch_party_id, merge_type, from_party_id, to_party_id, merge_status,party_type,
decode(op.actual_content_source, 'DNB', 'DNB', NULL), merge_reason_code
FROM HZ_MERGE_PARTIES mp, HZ_PARTIES pt, hz_organization_profiles op
WHERE batch_id = cp_batch_id
AND pt.party_id = mp.from_party_id
AND op.party_id(+) = pt.party_id
AND op.actual_content_source(+) = 'DNB'
AND op.effective_end_date IS NULL
AND ( mp.to_party_id = cp_to_party_id
OR (pt.party_type = 'PARTY_RELATIONSHIP'
AND exists (
select 1 FROM HZ_RELATIONSHIPS r
WHERE (r.party_id = mp.to_party_id or r.party_id = mp.from_party_id)
AND r.OBJECT_ID IN (SELECT from_party_id
FROM hz_merge_parties
WHERE batch_id = cp_batch_id AND to_party_id = cp_to_party_id))) )
ORDER BY decode(pt.party_type, 'PARTY_RELATIONSHIP',1,
decode(mp.merge_type, 'PARTY_MERGE',2,
'SAME_PARTY_MERGE',3,4)), 7, op.last_update_date desc; --5000614
SELECT merge_from_entity_id, merge_to_entity_id
FROM HZ_MERGE_PARTY_DETAILS
WHERE batch_party_id = cp_batch_party_id
AND ENTITY_NAME = 'HZ_PARTY_SITES'
AND merge_from_entity_id <> merge_to_entity_id;
SELECT merge_from_entity_id, merge_to_entity_id
FROM HZ_MERGE_PARTY_DETAILS
WHERE batch_party_id = cp_batch_party_id
AND ENTITY_NAME = 'HZ_CONTACT_POINTS'
AND merge_from_entity_id <> merge_to_entity_id;
SELECT merge_from_entity_id, merge_to_entity_id
FROM HZ_MERGE_PARTY_DETAILS
WHERE batch_party_id = cp_batch_party_id
AND ENTITY_NAME = 'HZ_PARTY_RELATIONSHIPS'
AND merge_from_entity_id <> merge_to_entity_id;
SELECT merge_dict_id
FROM HZ_MERGE_DICTIONARY
WHERE RULE_SET_NAME = cp_ruleset_name
AND ENTITY_NAME = cp_entity_name;
select dbat.request_type
from
HZ_DUP_BATCH dbat,
HZ_DUP_SETS dset,
HZ_MERGE_BATCH mb
where
dbat.dup_batch_id = dset.dup_batch_id
and mb.batch_id = dset.dup_set_id
and mb.batch_id = cp_batch_id;
select entity_name, fk_column_name, merge_dict_id, dict_application_id
from hz_merge_dictionary
where fk_data_type is null;
Select application_short_name from fnd_application where application_id=app_id;
select batch_party_id
from hz_merge_parties
where batch_id = p_batch_id
and rownum=1;
select merge_dict_id, entity_name, procedure_name
from hz_merge_dictionary
where merge_dict_id in (
select min(merge_dict_id)
from HZ_MERGE_DICTIONARY where batch_merge_flag = 'Y'
group by procedure_name);
select DISTINCT(to_party_id)
from HZ_MERGE_PARTIES mp
where batch_id = p_batch_id
and merge_status = 'DONE';
l_batch_delete HZ_MERGE_BATCH.BATCH_DELETE%TYPE;
l_dss_update_flag varchar2(1);
l_batch_delete, l_batch_commit;
x_dss_update_flag => l_dss_update_flag,
x_return_status => l_ret_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
update hz_merge_dictionary
set fk_data_type = l_data_type
where merge_dict_id = l_merge_dict_id;
UPDATE HZ_MERGE_BATCH
SET REQUEST_ID = hz_utility_pub.request_id
WHERE batch_id = p_batch_id;
UPDATE HZ_MERGE_BATCH
SET BATCH_STATUS = 'SUBMITTED'
WHERE BATCH_ID = p_batch_id;
UPDATE HZ_DUP_SETS
SET STATUS = 'SUBMITTED'
WHERE dup_set_id = p_batch_id;
SELECT NVL(automerge_flag, 'N') INTO g_automerge_flag
FROM hz_dup_sets ds, hz_dup_batch db
WHERE ds.dup_set_id = l_batch_id
AND db.dup_batch_id = ds.dup_batch_id
AND rownum = 1;
g_merge_delete_flag := l_batch_delete;
SELECT 1 INTO l_tmp
FROM HZ_MERGE_PARTIES
WHERE batch_id = l_batch_id
AND from_party_id = l_from_rel_party_id
AND to_party_id = l_to_rel_party_id
AND merge_status = 'DONE';
IF g_merge_delete_flag = 'Y' THEN
delete_merged_records(l_batch_party_id, l_return_status);
UPDATE HZ_MERGE_PARTIES
SET MERGE_STATUS = 'DONE',
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 = l_batch_party_id;
SELECT mp.batch_party_id, merge_from_entity_id, merge_to_entity_id, ENTITY_NAME
FROM HZ_MERGE_PARTIES mp, HZ_MERGE_PARTY_DETAILS md
WHERE mp.batch_party_id=md.batch_party_id
AND mp.to_party_id = l_pre_merge_to_party_id
AND mp.batch_id = l_batch_id
AND md.mandatory_merge = 'T') LOOP
do_same_party_merge (
p_batch_party_id=>TO_RECORDS.batch_party_id,
p_entity_name=>TO_RECORDS.ENTITY_NAME,
p_from_id=>TO_RECORDS.merge_from_entity_id,
p_to_id=>TO_RECORDS.merge_to_entity_id,
p_rule_set_name=>l_rule_set_name,
p_batch_id=>l_batch_id,
p_batch_commit=>l_batch_commit,
p_preview=>p_preview,
p_log_padding=>' ',
x_error_msg=>l_error_msg,
x_return_status=>l_return_status);
SELECT 1 INTO l_tmp
FROM HZ_MERGE_ENTITY_ATTRIBUTES
WHERE merge_to_party_id = l_pre_merge_to_party_id
AND merge_batch_id = l_batch_id
AND ROWNUM=1;
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_pre_merge_to_party_id;
UPDATE HZ_MERGE_BATCH
SET BATCH_STATUS = 'COMPLETE'
WHERE BATCH_ID = p_batch_id;
UPDATE HZ_DUP_SETS
SET STATUS = 'COMPLETED'
WHERE dup_set_id = p_batch_id;
SELECT count(*) INTO l_num_merged FROM HZ_MERGE_PARTIES
WHERE batch_id = p_batch_id
AND merge_status = 'DONE';
UPDATE HZ_MERGE_BATCH
SET BATCH_STATUS = 'PART_COMPLETE'
WHERE BATCH_ID = p_batch_id;
UPDATE HZ_MERGE_BATCH
SET BATCH_STATUS = 'ERROR'
WHERE BATCH_ID = p_batch_id;
UPDATE HZ_DUP_SETS
SET STATUS = 'ERROR'
WHERE DUP_SET_ID = p_batch_id;
l_list.DELETE;
UPDATE HZ_DUP_SETS
set status = 'ERROR'
where dup_set_id = p_batch_id;
UPDATE HZ_DUP_SETS
set status = 'ERROR'
where dup_set_id = p_batch_id;
UPDATE HZ_DUP_SETS
set status = 'ERROR'
where dup_set_id = p_batch_id;
SELECT merge_dict_id, DESCRIPTION
FROM HZ_MERGE_DICTIONARY
WHERE RULE_SET_NAME = cp_ruleset_name
AND ENTITY_NAME = cp_entity_name;
SELECT PROCEDURE_NAME, PK_COLUMN_NAME,nvl(DESC_COLUMN_NAME,PK_COLUMN_NAME),
FK_COLUMN_NAME, PARENT_ENTITY_NAME
FROM hz_merge_dictionary
WHERE merge_dict_id = cp_merge_dict_id;
SELECT merge_to_entity_id
FROM hz_merge_party_details
WHERE merge_from_entity_id = cp_pk_value AND
batch_party_id = p_batch_party_id AND
entity_name = cp_ent_name;
SELECT mandatory_merge INTO l_mand
FROM hz_merge_party_details
WHERE batch_party_id = p_batch_party_id
AND merge_from_entity_id = p_from_id
AND entity_name = p_entity_name;
'SELECT MERGE_DICT_ID, ENTITY_NAME, FK_COLUMN_NAME, FK_DATA_TYPE, PK_COLUMN_NAME,'||
'JOIN_CLAUSE, DESCRIPTION, PROCEDURE_NAME, BULK_FLAG '||
'FROM HZ_MERGE_DICTIONARY ' ||
'WHERE PARENT_ENTITY_NAME = :pentity' ||
' AND RULE_SET_NAME = :ruleset '||
' AND DICT_APPLICATION_ID IN ' || l_pmerge_apps ||
' AND NVL(BATCH_MERGE_FLAG,''N'') <> ''Y'' '|| --bug4634891
' ORDER BY SEQUENCE_NO ' USING p_entity_name, p_rule_set_name;
'SELECT MERGE_DICT_ID, ENTITY_NAME, FK_COLUMN_NAME, FK_DATA_TYPE, PK_COLUMN_NAME,'||
'JOIN_CLAUSE, DESCRIPTION, PROCEDURE_NAME, BULK_FLAG '||
'FROM HZ_MERGE_DICTIONARY ' ||
'WHERE PARENT_ENTITY_NAME = :pentity' ||
' AND RULE_SET_NAME = :ruleset '||
' AND NVL(BATCH_MERGE_FLAG,''N'') <> ''Y'' '|| --bug4634891
' ORDER BY SEQUENCE_NO ' USING p_entity_name, p_rule_set_name;
l_subrec_str := 'SELECT RELATIONSHIP_ID FROM HZ_RELATIONSHIPS'
||' WHERE '|| l_sub_fk_column_name;
l_subrec_str := 'SELECT ' || l_sub_pk_column_name || ' FROM '
||l_sub_entity_name || ' WHERE '|| l_sub_fk_column_name;
l_subrec_str := 'SELECT ' || l_hint ||' '|| l_sub_pk_column_name || ' FROM '
||l_sub_entity_name || ' S WHERE '|| l_sub_fk_column_name;
EXECUTE IMMEDIATE 'SELECT batch_party_id from HZ_MERGE_PARTY_DETAILS ' ||
'WHERE batch_party_id IN ' ||
' (SELECT batch_party_id from HZ_MERGE_PARTIES '||
' WHERE batch_id = :batchid ) FOR UPDATE NOWAIT' USING p_batch_id;
EXECUTE IMMEDIATE 'SELECT batch_party_id from HZ_MERGE_PARTIES ' ||
'WHERE batch_id = :batchid FOR UPDATE NOWAIT' USING p_batch_id;
EXECUTE IMMEDIATE 'SELECT batch_id from HZ_MERGE_BATCH ' ||
'WHERE batch_id = :batchid FOR UPDATE NOWAIT' USING p_batch_id;
Select application_short_name from fnd_application where application_id=app_id;
lockstr := 'SELECT ' || p_pk_column_name || ' FROM ' ||
p_entity_name || ' WHERE ' ||
p_fk_column_name || ' IN ' || p_join_str || ' AND ' ||
replace(upper(p_join_clause), 'GROUP BY ' ||
upper(p_pk_column_name));
lockstr := 'SELECT ' || p_pk_column_name || ' FROM ' ||
p_entity_name || ' WHERE ' || p_fk_column_name ||
' IN ' || p_join_str;
EXECUTE IMMEDIATE lockstr || ' FOR UPDATE NOWAIT';
'SELECT ENTITY_NAME, PK_COLUMN_NAME, FK_COLUMN_NAME, JOIN_CLAUSE, DICT_APPLICATION_ID'||
'FROM HZ_MERGE_DICTIONARY ' ||
'WHERE PARENT_ENTITY_NAME = :pentity' ||
' AND RULE_SET_NAME = :ruleset '||
' AND DICT_APPLICATION_ID IN ' || l_pmerge_apps ||
' ORDER BY SEQUENCE_NO ' USING p_entity_name, p_rule_set_name;
'SELECT ENTITY_NAME, PK_COLUMN_NAME, FK_COLUMN_NAME, JOIN_CLAUSE, DICT_APPLICATION_ID '||
'FROM HZ_MERGE_DICTIONARY ' ||
'WHERE PARENT_ENTITY_NAME = :pentity' ||
' AND RULE_SET_NAME = :ruleset '||
' ORDER BY SEQUENCE_NO ' USING p_entity_name, p_rule_set_name;
str_lockstr := 'SELECT TO_CHAR(' || p_pk_column_name || ') FROM ' ||
p_entity_name || ' WHERE ' || p_fk_column_name || ' IN ' ||
p_join_str || ' AND ' || l_join_clause;
| Procedure to delete the merged records for a batch party
|------------------------------------------------------------------------------*/
PROCEDURE delete_merged_records(
p_batch_party_id IN NUMBER,
x_return_status IN OUT NOCOPY VARCHAR2) IS
CURSOR c_deleted_records IS
SELECT h.from_entity_id, d.entity_name, d.pk_column_name
FROM HZ_MERGE_PARTY_HISTORY h, HZ_MERGE_DICTIONARY d
WHERE h.merge_dict_id = d.merge_dict_id
AND h.batch_party_id = p_batch_party_id
AND h.request_id = hz_utility_pub.request_id
AND h.operation_type = 'Merge'
ORDER BY h.merge_dict_id desc;
rec_delete VARCHAR2(2000);
OPEN c_deleted_records;
FETCH c_deleted_records INTO l_record_id, l_entity_name, l_pkcol;
EXIT WHEN c_deleted_records%NOTFOUND;
rec_delete := 'UPDATE ' || l_entity_name ||
' SET STATUS = ''D'' WHERE ' ||
l_pkcol || ' = :pk';
rec_delete := 'UPDATE HZ_RELATIONSHIPS' ||
' SET STATUS = ''D'' WHERE ' ||
'RELATIONSHIP_ID' || ' = :pk'||'AND subject_table_name = ''HZ_PARTIES'' AND object_table_name = ''HZ_PARTIES'' AND directional_flag = ''F''';
rec_delete := 'DELETE FROM ' || l_entity_name ||
' WHERE ' || l_pkcol || ' = :pk';
EXECUTE IMMEDIATE rec_delete USING l_record_id;
log('Delete complete');
FND_MESSAGE.SET_NAME('AR', 'HZ_MERGE_DELETE_ERROR');
END delete_merged_records;
rec_query := 'SELECT hz_merge_util.get_party_reln_description(relationship_id) FROM HZ_RELATIONSHIPS'
|| ' WHERE RELATIONSHIP_ID = '|| ':pk'
|| ' AND subject_table_name = ''HZ_PARTIES'' AND object_table_name = ''HZ_PARTIES'' AND directional_flag = ''F''';
rec_query := 'SELECT ' || p_desc_col_name ||
' FROM ' || p_entity_name || ' WHERE ' ||
p_pk_col_name || ' = :pk';
rec_query := 'SELECT ' || p_desc_col_name ||
' FROM ' || p_entity_name || ' WHERE ROWID = :pk';
INSERT INTO HZ_MERGE_PARTY_HISTORY(
batch_party_id,
request_id,
from_entity_id,
to_entity_id,
from_parent_entity_id,
to_parent_entity_id,
from_entity_desc,
to_entity_desc,
merge_dict_id,
operation_type,
created_by,
creation_date,
last_update_login,
last_update_date,
last_updated_by)
VALUES (
H_batch_party_id(I),
g_request_id, -- Bug No : 2998004 hz_utility_pub.request_id,
H_from_id(I),
H_to_id(I),
H_from_fk_id(I),
H_to_fk_id(I),
H_from_desc(I),
H_to_desc(I),
H_merge_dict_id(I),
H_op_type(I),
g_created_by, -- hz_utility_pub.created_by,
g_creation_date, -- hz_utility_pub.creation_date,
g_last_update_login, -- hz_utility_pub.last_update_login,
g_last_update_date, -- hz_utility_pub.last_update_date,
g_user_id -- hz_utility_pub.user_id
);
INSERT INTO HZ_MERGE_PARTY_LOG(
batch_party_id,
request_id,
from_entity_id,
to_entity_id,
from_parent_entity_id,
to_parent_entity_id,
from_entity_desc,
to_entity_desc,
merge_dict_id,
error_messages,
operation_type,
created_by,
creation_date,
last_update_login,
last_update_date,
last_updated_by)
VALUES (
I_batch_party_id(I),
g_request_id, -- Bug No : 2998004 hz_utility_pub.request_id,
I_from_id(I),
I_to_id(I),
I_from_fk_id(I),
I_to_fk_id(I),
I_from_desc(I),
I_to_desc(I),
I_merge_dict_id(I),
I_error(I),
I_op_type(I),
g_created_by, -- hz_utility_pub.created_by,
g_creation_date, -- hz_utility_pub.creation_date,
g_last_update_login, -- hz_utility_pub.last_update_login,
g_last_update_date, -- hz_utility_pub.last_update_date,
g_user_id -- hz_utility_pub.user_id
);
SELECT batch_name, rule_set_name ,batch_status, batch_delete, batch_commit
FROM HZ_MERGE_BATCH
WHERE batch_id = cp_batch_id;
select merge_to_entity_id, p.from_party_id, p.batch_party_id
from hz_merge_party_details d1, hz_merge_parties p
where entity_name = 'HZ_PARTY_SITES'
and p.batch_party_id = d1.batch_party_id
and d1.batch_party_id IN ( select batch_party_id
from hz_merge_parties
where to_party_id = cp_to_party_id
and to_party_id<>from_party_id
and batch_id = cp_batch_id)
and merge_from_entity_id = merge_to_entity_id --transfer operation
and exists ( --it should be a merge-to
select 1 --for another mapping
from hz_merge_party_details d2
where d2.merge_to_entity_id = d1.merge_from_entity_id
and d2.entity_name = 'HZ_PARTY_SITES'
and batch_party_id IN ( select batch_party_id
from hz_merge_parties
where to_party_id = cp_to_party_id
and to_party_id<>from_party_id
and batch_id = cp_batch_id)
and d2.merge_from_entity_id <> d1.merge_to_entity_id );
select merge_to_entity_id, p.from_party_id, p.batch_party_id
from hz_merge_party_details d1, hz_merge_parties p
where entity_name = 'HZ_PARTY_RELATIONSHIPS'
and p.batch_party_id = d1.batch_party_id
and d1.batch_party_id IN ( select batch_party_id
from hz_merge_parties
where to_party_id = cp_to_party_id
and to_party_id<>from_party_id
and batch_id = cp_batch_id)
and merge_from_entity_id = merge_to_entity_id --transfer operation
and exists ( --it should be a merge-to
select 1 --for another mapping
from hz_merge_party_details d2
where d2.merge_to_entity_id = d1.merge_from_entity_id
and d2.entity_name = 'HZ_PARTY_RELATIONSHIPS'
and batch_party_id IN (
select batch_party_id
from hz_merge_parties
where to_party_id = cp_to_party_id
and to_party_id<>from_party_id
and batch_id = cp_batch_id)
and d2.merge_from_entity_id <> d1.merge_to_entity_id );
l_batch_delete HZ_MERGE_BATCH.BATCH_DELETE%TYPE;
l_batch_status, l_batch_delete,l_batch_commit;
UPDATE HZ_PARTY_SITES
SET
STATUS = 'A',
last_update_date = hz_utility_pub.last_update_date,
last_updated_by = hz_utility_pub.user_id,
last_update_login = hz_utility_pub.last_update_login,
request_id = hz_utility_pub.request_id,
program_application_id = hz_utility_pub.program_application_id,
program_id = hz_utility_pub.program_id,
program_update_date = sysdate
WHERE party_site_id = l_merge_to_entity_id;
UPDATE hz_merge_party_details
set merge_to_entity_id = l_new_party_site_id
where batch_party_id IN (select batch_party_id from hz_merge_parties
where batch_id = p_batch_id)
and merge_to_entity_id = l_merge_to_entity_id
and entity_name = 'HZ_PARTY_SITES';
UPDATE hz_merge_party_details
set mandatory_merge = 'C'
where batch_party_id IN (select batch_party_id from hz_merge_parties
where batch_id = p_batch_id)
and merge_from_entity_id = l_merge_to_entity_id
and merge_to_entity_id = l_new_party_site_id
and entity_name = 'HZ_PARTY_SITES';
select subject_id , object_id
into l_subject_id , l_object_id
from HZ_RELATIONSHIPS --4500011
where relationship_id = l_merge_to_entity_id
and subject_table_name = 'HZ_PARTIES'
and object_table_name = 'HZ_PARTIES'
and directional_flag = 'F';
| Procedure for vetoing the delete
|-----------------------------------------------------------------------*/
PROCEDURE veto_delete IS
BEGIN
IF g_merge_delete_flag = 'Y' THEN
g_merge_delete_flag := 'N';
END veto_delete;
SELECT mh.TO_ENTITY_ID, mh.TO_ENTITY_DESC
FROM HZ_MERGE_PARTY_HISTORY mh, HZ_MERGE_DICTIONARY md
WHERE mh.merge_dict_id = md.merge_dict_id
AND md.entity_name = p_entity_name
AND mh.from_entity_id = cp_merge_from_id;
dbms_sql.parse(c, 'select '||p_column||' from '||p_table, dbms_sql.NATIVE);
SELECT DATA_TYPE FROM sys.all_tab_columns
WHERE table_name = p_table
AND COLUMN_NAME = p_column and owner = l_schema1;
SELECT b.batch_id, b.batch_name, b.created_by, b.creation_date
FROM HZ_MERGE_PARTIES p, HZ_MERGE_BATCH b
WHERE b.batch_id = p.batch_id
AND b.batch_status <> 'COMPLETE'
AND (p.from_party_id = p_party_id
OR p.to_party_id = p_party_id);
SELECT party_type
FROM HZ_PARTIES
WHERE party_id = cp_party_id;
SELECT duns_number_c, last_update_date, organization_profile_id,actual_content_source
FROM HZ_ORGANIZATION_PROFILES
WHERE party_id = p_from_id
AND EFFECTIVE_END_DATE IS NULL
AND actual_content_source = 'DNB'
AND nvl(status, 'A') = 'A';
SELECT duns_number_c , last_update_date, organization_profile_id,actual_content_source
FROM HZ_ORGANIZATION_PROFILES
WHERE party_id = x_to_id
AND EFFECTIVE_END_DATE IS NULL
AND actual_content_source = 'DNB'
AND nvl(status, 'A') = 'A';
SELECT 1
FROM HZ_RELATIONSHIPS --4500011
WHERE content_source_type = 'DNB'
AND subject_id = p_from_id
AND object_id = x_to_id
AND RELATIONSHIP_CODE = 'HEADQUARTERS_OF'
AND subject_table_name = 'HZ_PARTIES'
AND object_table_name = 'HZ_PARTIES'
AND directional_flag = 'F';
SELECT object_version_number INTO l_obj_version_number
FROM HZ_PARTIES
WHERE party_id = x_to_id;
HZ_PARTY_V2PUB.update_organization(
FND_API.G_FALSE,
l_organization_rec,
l_obj_version_number,
l_to_profile_id,
l_return_status,
l_msg_count,
l_msg_data);
HZ_MERGE_PARTY_DETAILS_PKG.Insert_Row(
p_batch_party_id,
'HZ_ORGANIZATION_PROFILES',
l_from_profile_id,
l_to_profile_id,
'Y',
hz_utility_pub.created_by,
hz_utility_pub.creation_Date,
hz_utility_pub.last_update_login,
hz_utility_pub.last_update_date,
hz_utility_pub.last_updated_by);
UPDATE HZ_PARTY_SITES ps
SET STATUS = 'M',
end_date_active = trunc(SYSDATE-1),
last_update_date = hz_utility_pub.last_update_date,
last_updated_by = hz_utility_pub.user_id,
last_update_login = hz_utility_pub.last_update_login,
request_id = hz_utility_pub.request_id,
program_application_id = hz_utility_pub.program_application_id,
program_id = hz_utility_pub.program_id,
program_update_date = sysdate
WHERE party_id= p_from_id
AND actual_content_source = 'DNB'
AND nvl(status,'A') in ('A','I');
SELECT relationship_id, relationship_type, subject_id, party_id, start_date, end_date, relationship_code,
direction_code
FROM HZ_RELATIONSHIPS
WHERE actual_content_source = 'DNB'
AND nvl(status, 'A') IN ('A','I')
AND object_id = p_from_id
AND (end_date is null OR end_date>SYSDATE)) LOOP
UPDATE HZ_RELATIONSHIPS
SET end_date = TRUNC(SYSDATE-1),
status = 'M',
last_update_date = hz_utility_pub.last_update_date,
last_updated_by = hz_utility_pub.user_id,
last_update_login = hz_utility_pub.last_update_login,
request_id = hz_utility_pub.request_id,
program_application_id = hz_utility_pub.program_application_id,
program_id = hz_utility_pub.program_id,
program_update_date = sysdate
WHERE relationship_id =FROM_REL.RELATIONSHIP_ID;
UPDATE HZ_ORIG_SYS_REFERENCES
SET STATUS = 'I',
END_DATE_ACTIVE =trunc(SYSDATE-1),
last_update_date = hz_utility_pub.last_update_date,
last_updated_by = hz_utility_pub.user_id,
last_update_login = hz_utility_pub.last_update_login,
request_id = hz_utility_pub.request_id,
program_application_id = hz_utility_pub.program_application_id,
program_id = hz_utility_pub.program_id,
program_update_date = sysdate
WHERE OWNER_TABLE_NAME = 'HZ_PARTIES'
AND OWNER_TABLE_ID = FROM_REL.PARTY_ID
AND ORIG_SYSTEM = 'DNB'
AND STATUS = 'A';
UPDATE HZ_PARTIES
SET status = 'M',
last_update_date = hz_utility_pub.last_update_date,
last_updated_by = hz_utility_pub.user_id,
last_update_login = hz_utility_pub.last_update_login,
request_id = hz_utility_pub.request_id,
program_application_id = hz_utility_pub.program_application_id,
program_id = hz_utility_pub.program_id,
program_update_date = sysdate
WHERE party_id = FROM_REL.party_id;
HZ_HIERARCHY_PUB.update_link(
p_init_msg_list => FND_API.G_FALSE,
p_hierarchy_node_rec => l_hierarchy_rec,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data
);
SELECT contact_point_id, phone_line_type, contact_point_type
FROM HZ_CONTACT_POINTS
WHERE owner_table_name = 'HZ_PARTIES'
AND actual_content_source = 'DNB'
AND nvl(status, 'A') = 'A'
AND owner_table_id = p_from_id) LOOP
case_new := 'FALSE';
SELECT contact_point_id INTO l_to
FROM HZ_CONTACT_POINTS
WHERE owner_table_name = 'HZ_PARTIES'
AND actual_content_source = 'DNB'
AND nvl(phone_line_type,'X') = nvl(FROM_CP.phone_line_type,'X')--bug 5221273
AND contact_point_type = FROM_CP.contact_point_type --bug 5221273
AND nvl(status, 'A') = 'A'
AND owner_table_id = x_to_id
and rownum = 1; --bug 5221273;
Select 'True' INTO case_new FROM HZ_CONTACT_POINTS
WHERE contact_point_id = FROM_CP.contact_point_id
AND (CONTACT_POINT_TYPE ||
STATUS ||
EDI_TRANSACTION_HANDLING ||
EDI_ID_NUMBER ||
EDI_PAYMENT_METHOD ||
EDI_PAYMENT_FORMAT ||
EDI_REMITTANCE_METHOD ||
EDI_REMITTANCE_INSTRUCTION ||
EDI_TP_HEADER_ID ||
EDI_ECE_TP_LOCATION_CODE ||
EMAIL_FORMAT ||
TO_CHAR(BEST_TIME_TO_CONTACT_START, 'DD/MM/YYYY') ||
TO_CHAR(BEST_TIME_TO_CONTACT_END, 'DD/MM/YYYY') ||
PHONE_CALLING_CALENDAR ||
DECLARED_BUSINESS_PHONE_FLAG ||
PHONE_PREFERRED_ORDER ||
TELEPHONE_TYPE ||
TIME_ZONE ||
PHONE_TOUCH_TONE_TYPE_FLAG ||
PHONE_AREA_CODE ||
PHONE_COUNTRY_CODE ||
PHONE_NUMBER ||
PHONE_EXTENSION ||
PHONE_LINE_TYPE ||
TELEX_NUMBER ||
WEB_TYPE )
= (SELECT
CONTACT_POINT_TYPE ||
STATUS ||
EDI_TRANSACTION_HANDLING ||
EDI_ID_NUMBER ||
EDI_PAYMENT_METHOD ||
EDI_PAYMENT_FORMAT ||
EDI_REMITTANCE_METHOD ||
EDI_REMITTANCE_INSTRUCTION ||
EDI_TP_HEADER_ID ||
EDI_ECE_TP_LOCATION_CODE ||
EMAIL_FORMAT ||
TO_CHAR(BEST_TIME_TO_CONTACT_START, 'DD/MM/YYYY') ||
TO_CHAR(BEST_TIME_TO_CONTACT_END, 'DD/MM/YYYY') ||
PHONE_CALLING_CALENDAR ||
DECLARED_BUSINESS_PHONE_FLAG ||
PHONE_PREFERRED_ORDER ||
TELEPHONE_TYPE ||
TIME_ZONE ||
PHONE_TOUCH_TONE_TYPE_FLAG ||
PHONE_AREA_CODE ||
PHONE_COUNTRY_CODE ||
PHONE_NUMBER ||
PHONE_EXTENSION ||
PHONE_LINE_TYPE ||
TELEX_NUMBER ||
WEB_TYPE
FROM HZ_CONTACT_POINTS
WHERE contact_point_id = l_to)
AND nvl(EMAIL_ADDRESS,'NOEMAIL') = (
SELECT nvl(EMAIL_ADDRESS,'NOEMAIL')
FROM HZ_CONTACT_POINTS
WHERE contact_point_id = l_to)
AND nvl(URL, 'NOURL') = (
SELECT nvl(URL, 'NOURL')
FROM HZ_CONTACT_POINTS
WHERE contact_point_id = l_to);
HZ_MERGE_PARTY_DETAILS_PKG.Insert_Row(
p_batch_party_id,
'HZ_CONTACT_POINTS',
l_to,
FROM_CP.contact_point_id,
'T',
hz_utility_pub.created_by,
hz_utility_pub.creation_Date,
hz_utility_pub.last_update_login,
hz_utility_pub.last_update_date,
hz_utility_pub.last_updated_by);
UPDATE HZ_CONTACT_POINTS
SET actual_content_source = 'USER_ENTERED',
last_update_date =hz_utility_pub.last_update_date,
last_updated_by = hz_utility_pub.last_updated_by,
last_update_login=hz_utility_pub.last_update_login,
request_id = hz_utility_pub.request_id,
program_application_id = hz_utility_pub.program_application_id,
program_id = hz_utility_pub.program_id,
program_update_date = sysdate
WHERE contact_point_id = l_to;
UPDATE HZ_ORIG_SYS_REFERENCES
SET STATUS = 'I',
END_DATE_ACTIVE =trunc(SYSDATE-1),
last_update_date = hz_utility_pub.last_update_date,
last_updated_by = hz_utility_pub.user_id,
last_update_login = hz_utility_pub.last_update_login,
request_id = hz_utility_pub.request_id,
program_application_id = hz_utility_pub.program_application_id,
program_id = hz_utility_pub.program_id,
program_update_date = sysdate
WHERE OWNER_TABLE_NAME = 'HZ_CONTACT_POINTS'
AND OWNER_TABLE_ID = l_to
AND ORIG_SYSTEM = 'DNB'
AND STATUS = 'A';
Select 'True' INTO case_new FROM HZ_CONTACT_POINTS
WHERE contact_point_id = FROM_CP.contact_point_id
AND
CONTACT_POINT_TYPE ||
STATUS ||
EDI_TRANSACTION_HANDLING ||
EDI_ID_NUMBER ||
EDI_PAYMENT_METHOD ||
EDI_PAYMENT_FORMAT ||
EDI_REMITTANCE_METHOD ||
EDI_REMITTANCE_INSTRUCTION ||
EDI_TP_HEADER_ID ||
EDI_ECE_TP_LOCATION_CODE ||
EMAIL_FORMAT ||
TO_CHAR(BEST_TIME_TO_CONTACT_START, 'DD/MM/YYYY') ||
TO_CHAR(BEST_TIME_TO_CONTACT_END, 'DD/MM/YYYY') ||
PHONE_CALLING_CALENDAR ||
DECLARED_BUSINESS_PHONE_FLAG ||
PHONE_PREFERRED_ORDER ||
TELEPHONE_TYPE ||
TIME_ZONE ||
PHONE_TOUCH_TONE_TYPE_FLAG ||
PHONE_AREA_CODE ||
PHONE_COUNTRY_CODE ||
PHONE_NUMBER ||
PHONE_EXTENSION ||
PHONE_LINE_TYPE ||
TELEX_NUMBER ||
WEB_TYPE
= (SELECT
CONTACT_POINT_TYPE ||
STATUS ||
EDI_TRANSACTION_HANDLING ||
EDI_ID_NUMBER ||
EDI_PAYMENT_METHOD ||
EDI_PAYMENT_FORMAT ||
EDI_REMITTANCE_METHOD ||
EDI_REMITTANCE_INSTRUCTION ||
EDI_TP_HEADER_ID ||
EDI_ECE_TP_LOCATION_CODE ||
EMAIL_FORMAT ||
TO_CHAR(BEST_TIME_TO_CONTACT_START, 'DD/MM/YYYY') ||
TO_CHAR(BEST_TIME_TO_CONTACT_END, 'DD/MM/YYYY') ||
PHONE_CALLING_CALENDAR ||
DECLARED_BUSINESS_PHONE_FLAG ||
PHONE_PREFERRED_ORDER ||
TELEPHONE_TYPE ||
TIME_ZONE ||
PHONE_TOUCH_TONE_TYPE_FLAG ||
PHONE_AREA_CODE ||
PHONE_COUNTRY_CODE ||
PHONE_NUMBER ||
PHONE_EXTENSION ||
PHONE_LINE_TYPE ||
TELEX_NUMBER ||
WEB_TYPE
FROM HZ_CONTACT_POINTS
WHERE contact_point_id = l_to)
AND nvl(EMAIL_ADDRESS,'NOEMAIL') = (
SELECT nvl(EMAIL_ADDRESS,'NOEMAIL')
FROM HZ_CONTACT_POINTS
WHERE contact_point_id = l_to)
AND nvl(URL, 'NOURL') = (
SELECT nvl(URL, 'NOURL')
FROM HZ_CONTACT_POINTS
WHERE contact_point_id = l_to);
HZ_MERGE_PARTY_DETAILS_PKG.Insert_Row(
p_batch_party_id,
'HZ_CONTACT_POINTS',
FROM_CP.contact_point_id,
l_to,
'Y',
hz_utility_pub.created_by,
hz_utility_pub.creation_Date,
hz_utility_pub.last_update_login,
hz_utility_pub.last_update_date,
hz_utility_pub.last_updated_by);
UPDATE HZ_CONTACT_POINTS
SET actual_content_source = 'USER_ENTERED',
last_update_date =hz_utility_pub.last_update_date,
last_updated_by = hz_utility_pub.last_updated_by,
last_update_login=hz_utility_pub.last_update_login,
request_id = hz_utility_pub.request_id,
program_application_id = hz_utility_pub.program_application_id,
program_id = hz_utility_pub.program_id,
program_update_date = sysdate
WHERE contact_point_id = FROM_CP.contact_point_id;
UPDATE HZ_ORIG_SYS_REFERENCES
SET STATUS = 'I',
END_DATE_ACTIVE =trunc(SYSDATE-1),
last_update_date = hz_utility_pub.last_update_date,
last_updated_by = hz_utility_pub.user_id,
last_update_login = hz_utility_pub.last_update_login,
request_id = hz_utility_pub.request_id,
program_application_id = hz_utility_pub.program_application_id,
program_id = hz_utility_pub.program_id,
program_update_date = sysdate
WHERE OWNER_TABLE_NAME = 'HZ_CONTACT_POINTS'
AND OWNER_TABLE_ID =FROM_CP.contact_point_id
AND ORIG_SYSTEM = 'DNB'
AND STATUS = 'A';
SELECT credit_rating_id, rated_as_of_date
FROM HZ_CREDIT_RATINGS
WHERE actual_content_source = 'DNB'
AND party_id = p_from_id) LOOP
BEGIN
SELECT credit_rating_id INTO l_to
FROM HZ_CREDIT_RATINGS
WHERE actual_content_source = 'DNB'
AND party_id = x_to_id
AND trunc(rated_as_of_date)=trunc(FROM_CR.rated_as_of_date);
DELETE FROM HZ_CREDIT_RATINGS
WHERE credit_rating_id = l_to;
HZ_MERGE_PARTY_DETAILS_PKG.Insert_Row(
p_batch_party_id,
'HZ_CREDIT_RATINGS',
l_to,
FROM_CR.credit_rating_id,
'T',
hz_utility_pub.created_by,
hz_utility_pub.creation_Date,
hz_utility_pub.last_update_login,
hz_utility_pub.last_update_date,
hz_utility_pub.last_updated_by);
HZ_MERGE_PARTY_DETAILS_PKG.Insert_Row(
p_batch_party_id,
'HZ_CREDIT_RATINGS',
FROM_CR.credit_rating_id,
l_to,
'Y',
hz_utility_pub.created_by,
hz_utility_pub.creation_Date,
hz_utility_pub.last_update_login,
hz_utility_pub.last_update_date,
hz_utility_pub.last_updated_by);
SELECT code_assignment_id, class_category, class_code
FROM HZ_CODE_ASSIGNMENTS
WHERE owner_table_name = 'HZ_PARTIES'
AND content_source_type = 'DNB'
AND nvl(status, 'A') = 'A'
AND owner_table_id = p_from_id) LOOP
BEGIN
SELECT code_assignment_id INTO l_to
FROM HZ_CODE_ASSIGNMENTS
WHERE owner_table_name = 'HZ_PARTIES'
AND content_source_type = 'DNB'
AND nvl(status, 'A') = 'A'
AND owner_table_id = x_to_id
AND class_category = FROM_CA.class_category
AND class_code = FROM_CA.class_code
AND rownum=1; --3197084
HZ_MERGE_PARTY_DETAILS_PKG.Insert_Row(
p_batch_party_id,
'HZ_CODE_ASSIGNMENTS',
FROM_CA.code_assignment_id,
l_to,
'Y',
hz_utility_pub.created_by,
hz_utility_pub.creation_Date,
hz_utility_pub.last_update_login,
hz_utility_pub.last_update_date,
hz_utility_pub.last_updated_by);
HZ_MERGE_PARTY_DETAILS_PKG.Insert_Row(
p_batch_party_id,
'HZ_CODE_ASSIGNMENTS',
FROM_CA.code_assignment_id,
l_to,
'Y',
hz_utility_pub.created_by,
hz_utility_pub.creation_Date,
hz_utility_pub.last_update_login,
hz_utility_pub.last_update_date,
hz_utility_pub.last_updated_by);
SELECT financial_report_id,
type_of_financial_report,
TO_CHAR(DATE_REPORT_ISSUED, 'DD/MM/YYYY') ||
DOCUMENT_REFERENCE ||
ISSUED_PERIOD ||
TO_CHAR(REPORT_START_DATE, 'DD/MM/YYYY') ||
TO_CHAR(REPORT_END_DATE, 'DD/MM/YYYY') ||
actual_content_source con_cat --for bug 6600935
FROM HZ_FINANCIAL_REPORTS
WHERE actual_content_source = 'DNB'
AND nvl(status, 'A') = 'A'
AND party_id = p_from_id) LOOP
BEGIN
SELECT financial_report_id INTO l_to
FROM HZ_FINANCIAL_REPORTS
WHERE actual_content_source = 'DNB'
AND nvl(status, 'A') = 'A'
AND type_of_financial_report=FROM_FR.type_of_financial_report
AND TO_CHAR(DATE_REPORT_ISSUED, 'DD/MM/YYYY') ||
DOCUMENT_REFERENCE ||
ISSUED_PERIOD ||
TO_CHAR(REPORT_START_DATE, 'DD/MM/YYYY') ||
TO_CHAR(REPORT_END_DATE, 'DD/MM/YYYY') ||
actual_content_source=FROM_FR.con_cat --for bug 6600935
AND party_id = x_to_id;
DELETE FROM HZ_FINANCIAL_REPORTS
WHERE financial_report_id = l_to;
DELETE FROM HZ_FINANCIAL_NUMBERS
WHERE financial_report_id = l_to;
UPDATE HZ_FINANCIAL_REPORTS
SET status = 'I',
last_update_date = hz_utility_pub.last_update_date,
last_updated_by = hz_utility_pub.user_id,
last_update_login = hz_utility_pub.last_update_login,
request_id = hz_utility_pub.request_id,
program_application_id = hz_utility_pub.program_application_id,
program_id = hz_utility_pub.program_id,
program_update_date = sysdate
WHERE financial_report_id = l_to;
UPDATE HZ_FINANCIAL_NUMBERS
SET status='I',
last_update_date = hz_utility_pub.last_update_date,
last_updated_by = hz_utility_pub.user_id,
last_update_login = hz_utility_pub.last_update_login,
request_id = hz_utility_pub.request_id,
program_application_id = hz_utility_pub.program_application_id,
program_id = hz_utility_pub.program_id,
program_update_date = sysdate
WHERE financial_report_id = l_to;
UPDATE HZ_FINANCIAL_REPORTS
SET status = 'M',
last_update_date = hz_utility_pub.last_update_date,
last_updated_by = hz_utility_pub.user_id,
last_update_login = hz_utility_pub.last_update_login,
request_id = hz_utility_pub.request_id,
program_application_id = hz_utility_pub.program_application_id,
program_id = hz_utility_pub.program_id,
program_update_date = sysdate
WHERE financial_report_id = FROM_FR.financial_report_id;
UPDATE HZ_FINANCIAL_NUMBERS
SET status='M',
last_update_date = hz_utility_pub.last_update_date,
last_updated_by = hz_utility_pub.user_id,
last_update_login = hz_utility_pub.last_update_login,
request_id = hz_utility_pub.request_id,
program_application_id = hz_utility_pub.program_application_id,
program_id = hz_utility_pub.program_id,
program_update_date = sysdate
WHERE financial_report_id = FROM_FR.financial_report_id; -- Bug 3313609
PROCEDURE insert_party_site_details (
p_from_party_id IN NUMBER,
p_to_party_id IN NUMBER,
p_batch_party_id IN NUMBER
) 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') = 'A';
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') = 'A';
HZ_MERGE_PARTY_DETAILS_PKG.Insert_Row(
p_batch_party_id,
'HZ_PARTY_SITES',
l_ps_id,
l_dup_ps_id,
'Y',
hz_utility_pub.created_by,
hz_utility_pub.creation_Date,
hz_utility_pub.last_update_login,
hz_utility_pub.last_update_date,
hz_utility_pub.last_updated_by);
HZ_MERGE_PARTY_DETAILS_PKG.Insert_Row(
p_batch_party_id,
'HZ_PARTY_SITES',
l_ps_id,
l_ps_id,
'Y',
hz_utility_pub.created_by,
hz_utility_pub.creation_Date,
hz_utility_pub.last_update_login,
hz_utility_pub.last_update_date,
hz_utility_pub.last_updated_by);
log('Error in DNB insert rel party site details : '||SQLERRM);
END insert_party_site_details;
select nvl(orgpf.internal_flag, 'N') internal_flag , hp.party_name
from HZ_merge_parties dup, hz_parties hp, hz_organization_profiles orgpf
where dup.batch_id = l_dup_set_id
and (hp.party_id = dup.from_party_id or hp.party_id = dup.to_party_id)
and hp.party_type = 'ORGANIZATION'
and hp.party_id = orgpf.party_id(+)
and sysdate between orgpf.effective_start_date(+) and nvl(orgpf.effective_end_date(+),sysdate);
/* select nvl(orgpf.internal_flag, 'N') internal_flag , hp.party_name
from HZ_DUP_SET_PARTIES dup, hz_parties hp, hz_organization_profiles orgpf
where dup.dup_set_id = l_dup_set_id
and dup.dup_party_id = hp.party_id
and hp.party_type = 'ORGANIZATION'
and hp.party_id = orgpf.party_id(+)
and sysdate between orgpf.effective_start_date(+) and nvl(orgpf.effective_end_date(+),sysdate)
and NVL(dup.MERGE_FLAG,'Y') <> 'N';*/
select nvl(orgpf.internal_flag, 'N') internal_flag , hp.party_name
from HZ_merge_parties dup, hz_parties hp, hz_person_profiles orgpf
where dup.batch_id = l_dup_set_id
and (hp.party_id = dup.from_party_id or hp.party_id = dup.to_party_id)
and hp.party_type = 'PERSON'
and hp.party_id = orgpf.party_id(+)
and sysdate between orgpf.effective_start_date(+) and nvl(orgpf.effective_end_date(+),sysdate);
/* select nvl(orgpf.internal_flag, 'N') internal_flag , hp.party_name
from HZ_DUP_SET_PARTIES dup, hz_parties hp, hz_person_profiles orgpf
where dup.dup_set_id = l_dup_set_id
and dup.dup_party_id = hp.party_id
and hp.party_type = 'PERSON'
and hp.party_id = orgpf.party_id(+)
and sysdate between orgpf.effective_start_date(+) and nvl(orgpf.effective_end_date(+),sysdate)
and NVL(dup.MERGE_FLAG,'Y') <> 'N';*/