[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select sum(sc) sum_score , max(sc) max_score , min(sc) min_score
from
(select entity_name ename, sum(score) sc
from hz_trans_attributes_vl a, hz_match_rule_secondary s
where s.match_rule_id = p_match_rule_id
and s.attribute_id = a.attribute_id
group by entity_name
order by sum(score) desc
);
select match_score
from hz_match_rules_vl
where match_rule_id = p_match_rule_id;
select auto_merge_score
from hz_match_rules_vl
where match_rule_id = p_match_rule_id;
select match_all_flag
from hz_match_rules_vl
where match_rule_id = p_match_rule_id;
SELECT count(1) into filter_count
FROM HZ_MATCH_RULE_PRIMARY p, HZ_TRANS_ATTRIBUTES_VL a
where p.match_rule_id=p_match_rule_id
and p.attribute_id=a.attribute_id
and a.entity_name = 'PARTY'
and p.filter_flag = 'Y';
SELECT count(1) into filter_count
FROM HZ_MATCH_RULE_PRIMARY p, HZ_TRANS_ATTRIBUTES_VL a
where p.match_rule_id=p_match_rule_id
and p.attribute_id=a.attribute_id
and a.entity_name = p_entity_name
and p.filter_flag = 'Y';
SELECT s.attribute_id
FROM HZ_MATCH_RULE_SECONDARY s, HZ_TRANS_ATTRIBUTES_VL a
where a.attribute_id=s.attribute_id
and s.match_rule_id=p_match_rule_id
and a.entity_name = p_entity_name
)
LOOP
temp := TRUE ;
FUNCTION get_insert_threshold ( p_match_rule_id NUMBER)
return number
IS
CURSOR entity_cur IS
select entity_name, sum(score) sc
from hz_trans_attributes_vl a, hz_match_rule_secondary s
where s.match_rule_id = p_match_rule_id
and s.attribute_id = a.attribute_id
group by entity_name
order by sum(score) desc ;
insert_threshold number;
select match_score, match_all_flag into match_score, match_all_flag
from hz_match_rules_vl
where match_rule_id = p_match_rule_id;
insert_threshold := match_score ;
insert_threshold := insert_threshold - entity_cur_rec.sc ;
insert_threshold := insert_threshold - entity_cur_rec.sc ;
insert_threshold := insert_threshold - entity_cur_rec.sc ;
insert_threshold := insert_threshold - entity_cur_rec.sc ;
return insert_threshold;
l('PROCEDURE tca_join_entities(trap_explosion in varchar2, rows_in_chunk in number,inserted_duplicates out number);');
l('PROCEDURE tca_join_entities(trap_explosion in varchar2, rows_in_chunk in number, inserted_duplicates out number)');
temp := get_insert_threshold(p_match_rule_id);
l(' x_insert_threshold number := ' || temp || ';');
l('FND_FILE.put_line(FND_FILE.log,''Start time of insert of Parties ''||to_char(sysdate,''hh24:mi:ss''));');
l('insert into hz_dup_results(fid, tid, ord_fid, ord_tid, score)');
l('select f, t, least(f,t), greatest(f,t), sum(score) score from (');
l(' inserted_duplicates := -1;');
PROCEDURE gen_insert_template_tca(
p_table VARCHAR2,
p_match_rule_id NUMBER,
p_entity VARCHAR2,
p_match_all_flag VARCHAR2
)
IS
FIRST BOOLEAN ;
l('select f, t, max(score) score from (');
l('select /*+ ORDERED */ s1.party_id f, s2.party_id t,');
SELECT count(1) into outer_row_count
FROM HZ_MATCH_RULE_SECONDARY s, HZ_TRANS_ATTRIBUTES_VL a
where a.attribute_id=s.attribute_id
and s.match_rule_id=p_match_rule_id
and a.entity_name = p_entity;
SELECT score,s.attribute_id , secondary_attribute_id
FROM HZ_MATCH_RULE_SECONDARY s, HZ_TRANS_ATTRIBUTES_VL a
where a.attribute_id=s.attribute_id
and s.match_rule_id=p_match_rule_id
and a.entity_name = p_entity)
LOOP
outer_row_counter := outer_row_counter + 1;
SELECT round(transformation_weight/100*attrs.score) score, staged_attribute_column
FROM hz_secondary_trans st, hz_trans_functions_vl f
where f.function_id=st.function_id
and st.secondary_attribute_id = attrs.secondary_attribute_id
order by transformation_weight desc)
LOOP
inner_row_counter := inner_row_counter + 1;
l('and exists(SELECT 1 from hz_staged_parties q where q.party_id = s2.party_id and nvl(q.status,''A'') = ''A'') ');
SELECT primary_attribute_id
FROM HZ_MATCH_RULE_PRIMARY p, HZ_TRANS_ATTRIBUTES_VL a
where p.match_rule_id=p_match_rule_id
and p.attribute_id=a.attribute_id
and a.entity_name = p_entity
and nvl(p.filter_flag,'N') = 'N' )
LOOP
-- between attributes
IF FIRST1
THEN
FIRST1 := FALSE;
FOR trans in ( SELECT staged_attribute_column
FROM hz_primary_trans pt, hz_trans_functions_vl f
where f.function_id = pt.function_id
and pt.primary_attribute_id = attrs.primary_attribute_id
)
LOOP
IF FIRST
THEN
l('-- do an or between all the transformations of an attribute -- ');
SELECT primary_attribute_id
FROM HZ_MATCH_RULE_PRIMARY p, HZ_TRANS_ATTRIBUTES_VL a
where p.match_rule_id=p_match_rule_id
and p.attribute_id=a.attribute_id
and a.entity_name = p_entity
and nvl(p.filter_flag,'N') = 'Y' )
LOOP
IF FIRST1
THEN
FIRST1 := FALSE;
FOR trans in ( SELECT staged_attribute_column
FROM hz_primary_trans pt, hz_trans_functions_vl f
where f.function_id = pt.function_id
and pt.primary_attribute_id = attrs.primary_attribute_id
)
LOOP
IF FIRST
THEN
l('-- do an or between all the transformations of an attribute -- ');
PROCEDURE gen_insert_footer_tca(p_match_rule_id number)
IS
FIRST1 boolean;
l('SELECT 1 FROM HZ_STAGED_PARTIES p1, HZ_STAGED_PARTIES p2');
SELECT primary_attribute_id
FROM HZ_MATCH_RULE_PRIMARY p, HZ_TRANS_ATTRIBUTES_VL a
where p.match_rule_id=p_match_rule_id
and p.attribute_id=a.attribute_id
and a.entity_name = 'PARTY'
and nvl(p.filter_flag,'N') = 'Y' )
LOOP
IF FIRST1
THEN
FIRST1 := FALSE;
FOR trans in ( SELECT staged_attribute_column
FROM hz_primary_trans pt, hz_trans_functions_vl f
where f.function_id = pt.function_id
and pt.primary_attribute_id = attrs.primary_attribute_id
)
LOOP
IF FIRST
THEN
l('-- do an or between all the transformations of an attribute -- ');
IF get_insert_threshold(p_match_rule_id) > 0
THEN
l('having sum(score) >= x_insert_threshold');
l('inserted_duplicates := (SQL%ROWCOUNT);');
l('FND_FILE.put_line(FND_FILE.log,''Number of parties inserted ''||SQL%ROWCOUNT);');
l('FND_FILE.put_line(FND_FILE.log,''End time of insert ''||to_char(sysdate,''hh24:mi:ss''));');
PROCEDURE gen_update_template_tca (
p_table VARCHAR2,
p_match_rule_id NUMBER,
p_entity VARCHAR2,
p_match_all_flag VARCHAR2
)
IS
FIRST BOOLEAN ;
l('FND_FILE.put_line(FND_FILE.log,'||''''|| 'Beginning update of Parties on the basis of ' || p_entity || ''''|| ');');
l('FND_FILE.put_line(FND_FILE.log,''Start time of update ''||to_char(sysdate,''hh24:mi:ss''));');
l('select f, t, max(score) score from (');
l(' select /*+ ORDERED */ s1.party_id f, s2.party_id t,');
SELECT count(1) into outer_row_count
FROM HZ_MATCH_RULE_SECONDARY s, HZ_TRANS_ATTRIBUTES_VL a
where a.attribute_id=s.attribute_id
and s.match_rule_id=p_match_rule_id
and a.entity_name = p_entity;
SELECT score,s.attribute_id , secondary_attribute_id
FROM HZ_MATCH_RULE_SECONDARY s, HZ_TRANS_ATTRIBUTES_VL a
where a.attribute_id=s.attribute_id
and s.match_rule_id=p_match_rule_id
and a.entity_name = p_entity)
LOOP
outer_row_counter := outer_row_counter + 1;
SELECT round(transformation_weight/100*attrs.score) score, staged_attribute_column
FROM hz_secondary_trans st, hz_trans_functions_vl f
where f.function_id=st.function_id
and st.secondary_attribute_id = attrs.secondary_attribute_id
order by transformation_weight desc)
LOOP
inner_row_counter := inner_row_counter + 1;
SELECT primary_attribute_id
FROM HZ_MATCH_RULE_PRIMARY p, HZ_TRANS_ATTRIBUTES_VL a
where p.match_rule_id=p_match_rule_id
and p.attribute_id=a.attribute_id
and a.entity_name = p_entity
and nvl(p.filter_flag,'N') = 'N' )
LOOP
-- between attributes
IF FIRST1
THEN
FIRST1 := FALSE;
FOR trans in ( SELECT staged_attribute_column
FROM hz_primary_trans pt, hz_trans_functions_vl f
where f.function_id = pt.function_id
and pt.primary_attribute_id = attrs.primary_attribute_id
)
LOOP
IF FIRST
THEN
l('-- do an or between all the transformations of an attribute -- ');
SELECT primary_attribute_id
FROM HZ_MATCH_RULE_PRIMARY p, HZ_TRANS_ATTRIBUTES_VL a
where p.match_rule_id=p_match_rule_id
and p.attribute_id=a.attribute_id
and a.entity_name = p_entity
and nvl(p.filter_flag,'N') = 'Y' )
LOOP
-- between attributes
IF FIRST1
THEN
FIRST1 := FALSE;
FOR trans in ( SELECT staged_attribute_column
FROM hz_primary_trans pt, hz_trans_functions_vl f
where f.function_id = pt.function_id
and pt.primary_attribute_id = attrs.primary_attribute_id
)
LOOP
IF FIRST
THEN
l('-- do an or between all the transformations of an attribute -- ');
l('HZ_DQM_DUP_ID_PKG.update_hz_dup_results(x_ent_cur);');
l('FND_FILE.put_line(FND_FILE.log,''Number of parties updated ''||SQL%ROWCOUNT);');
l('FND_FILE.put_line(FND_FILE.log,''End time to update ''||to_char(sysdate,''hh24:mi:ss''));');
l('FND_FILE.put_line(FND_FILE.log,'||''''|| 'Ending update of Parties on the basis of ' || p_entity || ''''|| ');');
select entity_name, entity_table_name, sc, att_flag
from
(select entity_name, decode(entity_name,
'PARTY','HZ_STAGED_PARTIES',
'PARTY_SITES', 'HZ_STAGED_PARTY_SITES',
'CONTACTS','HZ_STAGED_CONTACTS',
'CONTACT_POINTS', 'HZ_STAGED_CONTACT_POINTS') entity_table_name,
sum(score) sc, 'S' att_flag
from hz_trans_attributes_vl a, hz_match_rule_secondary s
where s.match_rule_id = p_match_rule_id
and s.attribute_id = a.attribute_id
group by entity_name
union all
select entity_name, decode(entity_name,
'PARTY','HZ_STAGED_PARTIES',
'PARTY_SITES', 'HZ_STAGED_PARTY_SITES',
'CONTACTS','HZ_STAGED_CONTACTS',
'CONTACT_POINTS', 'HZ_STAGED_CONTACT_POINTS') entity_table_name,
0 sc, 'P' att_flag
from hz_trans_attributes_vl a, hz_match_rule_primary p
where p.match_rule_id = p_match_rule_id
and p.attribute_id = a.attribute_id
group by entity_name
)
where att_flag = p_att_flag
order by sc desc ;
insert_stmt_is_open boolean;
select match_score, match_all_flag into threshold, match_all_flag
from hz_match_rules_vl
where match_rule_id = p_match_rule_id;
insert_stmt_is_open := false;
gen_insert_template_tca(entity_cur_rec.entity_table_name, p_match_rule_id, entity_cur_rec.entity_name,
match_all_flag);
insert_stmt_is_open := true;
gen_insert_template_tca(entity_cur_rec.entity_table_name, p_match_rule_id, entity_cur_rec.entity_name,
match_all_flag);
IF insert_stmt_is_open
THEN
gen_insert_footer_tca(p_match_rule_id);
insert_stmt_is_open := false;
gen_update_template_tca(entity_cur_rec.entity_table_name, p_match_rule_id, entity_cur_rec.entity_name,
match_all_flag);
gen_insert_template_tca(entity_cur_rec.entity_table_name, p_match_rule_id, entity_cur_rec.entity_name,
match_all_flag);
IF insert_stmt_is_open
THEN
gen_insert_footer_tca(p_match_rule_id);
insert_stmt_is_open := false;
gen_update_template_tca(entity_cur_rec.entity_table_name, p_match_rule_id, entity_cur_rec.entity_name,
match_all_flag);
gen_insert_template_tca(entity_cur_rec.entity_table_name, p_match_rule_id, entity_cur_rec.entity_name,
match_all_flag);
IF insert_stmt_is_open
THEN
gen_insert_footer_tca(p_match_rule_id);
insert_stmt_is_open := false;
gen_update_template_tca(entity_cur_rec.entity_table_name, p_match_rule_id, entity_cur_rec.entity_name,
match_all_flag);
gen_insert_template_tca(entity_cur_rec.entity_table_name, p_match_rule_id, entity_cur_rec.entity_name,
match_all_flag);
IF insert_stmt_is_open
THEN
gen_insert_footer_tca(p_match_rule_id);
insert_stmt_is_open := false;
gen_update_template_tca(entity_cur_rec.entity_table_name, p_match_rule_id, entity_cur_rec.entity_name,
match_all_flag);
IF insert_stmt_is_open
THEN
gen_insert_footer_tca(p_match_rule_id);
insert_stmt_is_open := false;
temp := get_insert_threshold(p_match_rule_id);
l('x_insert_threshold number := ' || temp || ';');
l('FND_FILE.put_line(FND_FILE.log,''Start time of insert of Parties ''||to_char(sysdate,''hh24:mi:ss''));');
l('insert into hz_imp_dup_parties(party_id,dup_party_id, score, party_osr, party_os, batch_id, auto_merge_flag');
l(',created_by,creation_date,last_update_login,last_update_date,last_updated_by)');
l('select f, t, sum(score) sc, party_osr, party_os, p_batch_id, ''N'' ');
l(',hz_utility_v2pub.created_by,hz_utility_v2pub.creation_date,hz_utility_v2pub.last_update_login');
l(',hz_utility_v2pub.last_update_date,hz_utility_v2pub.last_updated_by');
PROCEDURE gen_insert_template_int_tca(
s_table VARCHAR2,
p_table VARCHAR2,
p_match_rule_id NUMBER,
p_entity VARCHAR2,
p_match_all_flag VARCHAR2
)
IS
FIRST BOOLEAN ;
l('select f, t, max(score) score, party_osr, party_os from (');
l('select /*+ USE_CONCAT */ s1.party_id f, s2.party_id t,');
SELECT count(1) into outer_row_count
FROM HZ_MATCH_RULE_SECONDARY s, HZ_TRANS_ATTRIBUTES_VL a
where a.attribute_id=s.attribute_id
and s.match_rule_id=p_match_rule_id
and a.entity_name = p_entity;
SELECT score,s.attribute_id , secondary_attribute_id
FROM HZ_MATCH_RULE_SECONDARY s, HZ_TRANS_ATTRIBUTES_VL a
where a.attribute_id=s.attribute_id
and s.match_rule_id=p_match_rule_id
and a.entity_name = p_entity)
LOOP
outer_row_counter := outer_row_counter + 1;
SELECT round(transformation_weight/100*attrs.score) score, staged_attribute_column
FROM hz_secondary_trans st, hz_trans_functions_vl f
where f.function_id=st.function_id
and st.secondary_attribute_id = attrs.secondary_attribute_id
order by transformation_weight desc)
LOOP
inner_row_counter := inner_row_counter + 1;
l('and exists(SELECT 1 from hz_staged_parties q where q.party_id = s2.party_id and nvl(q.status,''A'') = ''A'') ');
SELECT primary_attribute_id
FROM HZ_MATCH_RULE_PRIMARY p, HZ_TRANS_ATTRIBUTES_VL a
where p.match_rule_id=p_match_rule_id
and p.attribute_id=a.attribute_id
and a.entity_name = p_entity
and nvl(p.filter_flag,'N') = 'N' )
LOOP
-- between attributes
IF FIRST1
THEN
FIRST1 := FALSE;
FOR trans in ( SELECT staged_attribute_column
FROM hz_primary_trans pt, hz_trans_functions_vl f
where f.function_id = pt.function_id
and pt.primary_attribute_id = attrs.primary_attribute_id
)
LOOP
IF FIRST
THEN
l('-- do an or between all the transformations of an attribute -- ');
SELECT primary_attribute_id
FROM HZ_MATCH_RULE_PRIMARY p, HZ_TRANS_ATTRIBUTES_VL a
where p.match_rule_id=p_match_rule_id
and p.attribute_id=a.attribute_id
and a.entity_name = p_entity
and nvl(p.filter_flag,'N') = 'Y' )
LOOP
-- between attributes
IF FIRST1
THEN
FIRST1 := FALSE;
FOR trans in ( SELECT staged_attribute_column
FROM hz_primary_trans pt, hz_trans_functions_vl f
where f.function_id = pt.function_id
and pt.primary_attribute_id = attrs.primary_attribute_id
)
LOOP
IF FIRST
THEN
l('-- do an or between all the transformations of an attribute -- ');
PROCEDURE gen_dl_insert_template_int_tca(
s_table VARCHAR2,
p_table VARCHAR2,
p_match_rule_id NUMBER,
p_entity VARCHAR2,
p_entity_id_name VARCHAR2,
p_entity_osr_name VARCHAR2,
p_entity_os_name VARCHAR2,
p_match_all_flag VARCHAR2
)
IS
FIRST BOOLEAN ;
l('FND_FILE.put_line(FND_FILE.log,'||''''|| 'Beginning insert of ' || p_entity || ''''|| ');');
l('FND_FILE.put_line(FND_FILE.log,''Start time of insert ''||to_char(sysdate,''hh24:mi:ss''));');
l('insert into hz_imp_dup_details(party_id, score, party_osr, party_os, batch_id, entity, record_id, record_osr, record_os, dup_record_id');
l(',created_by,creation_date,last_update_login,last_update_date,last_updated_by)');
l('select /*+ USE_CONCAT */ s1.party_id f,');
SELECT count(1) into outer_row_count
FROM HZ_MATCH_RULE_SECONDARY s, HZ_TRANS_ATTRIBUTES_VL a
where a.attribute_id=s.attribute_id
and s.match_rule_id=p_match_rule_id
and a.entity_name = p_entity;
SELECT score,s.attribute_id , secondary_attribute_id
FROM HZ_MATCH_RULE_SECONDARY s, HZ_TRANS_ATTRIBUTES_VL a
where a.attribute_id=s.attribute_id
and s.match_rule_id=p_match_rule_id
and a.entity_name = p_entity)
LOOP
outer_row_counter := outer_row_counter + 1;
SELECT round(transformation_weight/100*attrs.score) score, staged_attribute_column
FROM hz_secondary_trans st, hz_trans_functions_vl f
where f.function_id=st.function_id
and st.secondary_attribute_id = attrs.secondary_attribute_id
order by transformation_weight desc)
LOOP
inner_row_counter := inner_row_counter + 1;
l(',hz_utility_v2pub.created_by,hz_utility_v2pub.creation_date,hz_utility_v2pub.last_update_login');
l(',hz_utility_v2pub.last_update_date,hz_utility_v2pub.last_updated_by');
SELECT primary_attribute_id
FROM HZ_MATCH_RULE_PRIMARY p, HZ_TRANS_ATTRIBUTES_VL a
where p.match_rule_id=p_match_rule_id
and p.attribute_id=a.attribute_id
and a.entity_name = p_entity
and nvl(p.filter_flag,'N') = 'N' )
LOOP
-- between attributes
IF FIRST1
THEN
FIRST1 := FALSE;
FOR trans in ( SELECT staged_attribute_column
FROM hz_primary_trans pt, hz_trans_functions_vl f
where f.function_id = pt.function_id
and pt.primary_attribute_id = attrs.primary_attribute_id
)
LOOP
IF FIRST
THEN
l('-- do an or between all the transformations of an attribute -- ');
SELECT primary_attribute_id
FROM HZ_MATCH_RULE_PRIMARY p, HZ_TRANS_ATTRIBUTES_VL a
where p.match_rule_id=p_match_rule_id
and p.attribute_id=a.attribute_id
and a.entity_name = p_entity
and nvl(p.filter_flag,'N') = 'Y'
and HZ_IMP_DQM_STAGE.EXIST_COL(a.attribute_name, a.entity_name ) = 'Y'
)
LOOP
-- between attributes
IF FIRST1
THEN
FIRST1 := FALSE;
FOR trans in ( SELECT staged_attribute_column
FROM hz_primary_trans pt, hz_trans_functions_vl f
where f.function_id = pt.function_id
and pt.primary_attribute_id = attrs.primary_attribute_id
)
LOOP
IF FIRST
THEN
l('-- do an or between all the transformations of an attribute -- ');
l('--------UPDATE DQM ACTION FLAG IN ' || p_entity ||' INTERFACE/STAGING TABLES --------------');
l('select distinct a.record_osr, a.record_os');
l('HZ_DQM_DUP_ID_PKG.update_detail_dqm_action_flag(''' ||p_entity ||''',p_batch_id, x_ent_cur);');
l('FND_FILE.put_line(FND_FILE.log,'||''''|| 'Ending insert of ' || p_entity || ''''|| ');');
l('FND_FILE.put_line(FND_FILE.log,''Number of records inserted ''||SQL%ROWCOUNT);');
l('FND_FILE.put_line(FND_FILE.log,''End time to insert ''||to_char(sysdate,''hh24:mi:ss''));');
PROCEDURE gen_insert_footer_int_tca(p_match_rule_id number)
IS
FIRST1 boolean;
l('SELECT 1 FROM HZ_SRCH_PARTIES p1, HZ_STAGED_PARTIES p2');
SELECT primary_attribute_id
FROM HZ_MATCH_RULE_PRIMARY p, HZ_TRANS_ATTRIBUTES_VL a
where p.match_rule_id=p_match_rule_id
and p.attribute_id=a.attribute_id
and a.entity_name = 'PARTY'
and nvl(p.filter_flag,'N') = 'Y'
and HZ_IMP_DQM_STAGE.EXIST_COL(a.attribute_name, a.entity_name ) = 'Y'
)
LOOP
IF FIRST1
THEN
FIRST1 := FALSE;
FOR trans in ( SELECT staged_attribute_column
FROM hz_primary_trans pt, hz_trans_functions_vl f
where f.function_id = pt.function_id
and pt.primary_attribute_id = attrs.primary_attribute_id
)
LOOP
IF FIRST
THEN
l('-- do an or between all the transformations of an attribute -- ');
IF get_insert_threshold(p_match_rule_id) > 0
THEN
l('having sum(score) >= x_insert_threshold');
l('FND_FILE.put_line(FND_FILE.log,''Number of parties inserted ''||SQL%ROWCOUNT);');
l('FND_FILE.put_line(FND_FILE.log,''End time of insert ''||to_char(sysdate,''hh24:mi:ss''));');
PROCEDURE gen_update_template_int_tca (
s_table VARCHAR2,
p_table VARCHAR2,
p_match_rule_id NUMBER,
p_entity VARCHAR2,
p_match_all_flag VARCHAR2
)
IS
FIRST BOOLEAN ;
l('FND_FILE.put_line(FND_FILE.log,'||''''|| 'Beginning update of Parties on the basis of ' || p_entity || ''''|| ');');
l('FND_FILE.put_line(FND_FILE.log,''Start time of update ''||to_char(sysdate,''hh24:mi:ss''));');
l('select f,t,max(score) from (');
l(' select /*+ USE_CONCAT */ s1.party_id f, s2.party_id t,');
SELECT count(1) into outer_row_count
FROM HZ_MATCH_RULE_SECONDARY s, HZ_TRANS_ATTRIBUTES_VL a
where a.attribute_id=s.attribute_id
and s.match_rule_id=p_match_rule_id
and a.entity_name = p_entity;
SELECT score,s.attribute_id , secondary_attribute_id
FROM HZ_MATCH_RULE_SECONDARY s, HZ_TRANS_ATTRIBUTES_VL a
where a.attribute_id=s.attribute_id
and s.match_rule_id=p_match_rule_id
and a.entity_name = p_entity)
LOOP
outer_row_counter := outer_row_counter + 1;
SELECT round(transformation_weight/100*attrs.score) score, staged_attribute_column
FROM hz_secondary_trans st, hz_trans_functions_vl f
where f.function_id=st.function_id
and st.secondary_attribute_id = attrs.secondary_attribute_id
order by transformation_weight desc)
LOOP
inner_row_counter := inner_row_counter + 1;
SELECT primary_attribute_id
FROM HZ_MATCH_RULE_PRIMARY p, HZ_TRANS_ATTRIBUTES_VL a
where p.match_rule_id=p_match_rule_id
and p.attribute_id=a.attribute_id
and a.entity_name = p_entity
and nvl(p.filter_flag,'N') = 'N' )
LOOP
-- between attributes
IF FIRST1
THEN
FIRST1 := FALSE;
FOR trans in ( SELECT staged_attribute_column
FROM hz_primary_trans pt, hz_trans_functions_vl f
where f.function_id = pt.function_id
and pt.primary_attribute_id = attrs.primary_attribute_id
)
LOOP
IF FIRST
THEN
l('-- do an or between all the transformations of an attribute -- ');
SELECT primary_attribute_id
FROM HZ_MATCH_RULE_PRIMARY p, HZ_TRANS_ATTRIBUTES_VL a
where p.match_rule_id=p_match_rule_id
and p.attribute_id=a.attribute_id
and a.entity_name = p_entity
and nvl(p.filter_flag,'N') = 'Y'
and HZ_IMP_DQM_STAGE.EXIST_COL(a.attribute_name, a.entity_name ) = 'Y'
)
LOOP
-- between attributes
IF FIRST1
THEN
FIRST1 := FALSE;
FOR trans in ( SELECT staged_attribute_column
FROM hz_primary_trans pt, hz_trans_functions_vl f
where f.function_id = pt.function_id
and pt.primary_attribute_id = attrs.primary_attribute_id
)
LOOP
IF FIRST
THEN
l('-- do an or between all the transformations of an attribute -- ');
l('HZ_DQM_DUP_ID_PKG.update_hz_imp_dup_parties(p_batch_id, x_ent_cur);');
l('FND_FILE.put_line(FND_FILE.log,''Number of parties updated ''||SQL%ROWCOUNT);');
l('FND_FILE.put_line(FND_FILE.log,''End time to update ''||to_char(sysdate,''hh24:mi:ss''));');
l('FND_FILE.put_line(FND_FILE.log,'||''''|| 'Ending update of Parties on the basis of ' || p_entity || ''''|| ');');
l('--------DELETE ON THRESHOLD AND REMOVE INDIRECT TRANSITIVITY ---------------------');
l('FND_FILE.put_line(FND_FILE.log,''DELETE ON THRESHOLD AND INDIRECT TRANSITIVITY '');');
l('FND_FILE.put_line(FND_FILE.log,''Begin time to delete ''||to_char(sysdate,''hh24:mi:ss''));');
l('delete from hz_imp_dup_parties a');
l('-- delete the party id whose duplicate is a bigger number, when scores are same');
l(' (Select 1 from hz_imp_dup_parties b');
l('-- delete the party id with least score, if scores are different');
l(' (Select 1 from hz_imp_dup_parties b');
l('FND_FILE.put_line(FND_FILE.log,''Number of records deleted from hz_imp_dup_parties ''||SQL%ROWCOUNT);');
l('FND_FILE.put_line(FND_FILE.log,''End time to delete ''||to_char(sysdate,''hh24:mi:ss''));');
l('--------UPDATE AUTO MERGE FLAG --------------');
l('update hz_imp_dup_parties a');
l('--------UPDATE DQM ACTION FLAG IN INTERFACE/STAGING TABLES --------------');
l('select a.party_osr, a.party_os, a.auto_merge_flag');
l('HZ_DQM_DUP_ID_PKG.update_party_dqm_action_flag(p_batch_id, x_ent_cur);');
select entity_name, search_table_name, entity_table_name, entity_id_name, entity_osr_name,
entity_os_name, sc, att_flag
from
( select entity_name, decode(entity_name,
'PARTY','HZ_SRCH_PARTIES',
'PARTY_SITES', 'HZ_SRCH_PSITES',
'CONTACTS','HZ_SRCH_CONTACTS',
'CONTACT_POINTS', 'HZ_SRCH_CPTS') search_table_name,
decode(entity_name,
'PARTY','HZ_STAGED_PARTIES',
'PARTY_SITES', 'HZ_STAGED_PARTY_SITES',
'CONTACTS','HZ_STAGED_CONTACTS',
'CONTACT_POINTS', 'HZ_STAGED_CONTACT_POINTS') entity_table_name,
decode(entity_name,
'PARTY','PARTY_ID',
'PARTY_SITES', 'PARTY_SITE_ID',
'CONTACTS','ORG_CONTACT_ID',
'CONTACT_POINTS', 'CONTACT_POINT_ID') entity_id_name,
decode(entity_name,
'PARTY','PARTY_OSR',
'PARTY_SITES', 'PARTY_SITE_OSR',
'CONTACTS','CONTACT_OSR',
'CONTACT_POINTS', 'CONTACT_PT_OSR') entity_osr_name,
decode(entity_name,
'PARTY','PARTY_OS',
'PARTY_SITES', 'PARTY_SITE_OS',
'CONTACTS','CONTACT_OS',
'CONTACT_POINTS', 'CONTACT_PT_OS') entity_os_name,
sum(score) sc, 'S' att_flag
from hz_trans_attributes_vl a, hz_match_rule_secondary s
where s.match_rule_id = p_match_rule_id
and s.attribute_id = a.attribute_id
group by entity_name
union all
select entity_name, decode(entity_name,
'PARTY','HZ_SRCH_PARTIES',
'PARTY_SITES', 'HZ_SRCH_PSITES',
'CONTACTS','HZ_SRCH_CONTACTS',
'CONTACT_POINTS', 'HZ_SRCH_CPTS') search_table_name,
decode(entity_name,
'PARTY','HZ_STAGED_PARTIES',
'PARTY_SITES', 'HZ_STAGED_PARTY_SITES',
'CONTACTS','HZ_STAGED_CONTACTS',
'CONTACT_POINTS', 'HZ_STAGED_CONTACT_POINTS') entity_table_name,
decode(entity_name,
'PARTY','PARTY_ID',
'PARTY_SITES', 'PARTY_SITE_ID',
'CONTACTS','ORG_CONTACT_ID',
'CONTACT_POINTS', 'CONTACT_POINT_ID') entity_id_name,
decode(entity_name,
'PARTY','PARTY_OSR',
'PARTY_SITES', 'PARTY_SITE_OSR',
'CONTACTS','CONTACT_OSR',
'CONTACT_POINTS', 'CONTACT_PT_OSR') entity_osr_name,
decode(entity_name,
'PARTY','PARTY_OS',
'PARTY_SITES', 'PARTY_SITE_OS',
'CONTACTS','CONTACT_OS',
'CONTACT_POINTS', 'CONTACT_PT_OS') entity_os_name,
0 sc, 'P' att_flag
from hz_trans_attributes_vl a, hz_match_rule_primary p
where p.match_rule_id = p_match_rule_id
and p.attribute_id = a.attribute_id
group by entity_name
) where att_flag = p_att_flag
order by sc desc ;
insert_stmt_is_open boolean;
select match_score, match_all_flag into threshold, match_all_flag
from hz_match_rules_vl
where match_rule_id = p_match_rule_id;
insert_stmt_is_open := false;
gen_insert_template_int_tca(entity_cur_rec.search_table_name,entity_cur_rec.entity_table_name, p_match_rule_id,
entity_cur_rec.entity_name, match_all_flag);
insert_stmt_is_open := true;
gen_insert_template_int_tca(entity_cur_rec.search_table_name,entity_cur_rec.entity_table_name, p_match_rule_id,
entity_cur_rec.entity_name, match_all_flag);
IF insert_stmt_is_open
THEN
gen_insert_footer_int_tca(p_match_rule_id) ;
insert_stmt_is_open := false;
gen_update_template_int_tca(entity_cur_rec.search_table_name,entity_cur_rec.entity_table_name, p_match_rule_id,
entity_cur_rec.entity_name, match_all_flag);
gen_insert_template_int_tca(entity_cur_rec.search_table_name,entity_cur_rec.entity_table_name,
p_match_rule_id, entity_cur_rec.entity_name, match_all_flag);
IF insert_stmt_is_open
THEN
gen_insert_footer_int_tca(p_match_rule_id) ;
insert_stmt_is_open := false;
gen_update_template_int_tca(entity_cur_rec.search_table_name,entity_cur_rec.entity_table_name, p_match_rule_id,
entity_cur_rec.entity_name, match_all_flag);
gen_insert_template_int_tca(entity_cur_rec.search_table_name,entity_cur_rec.entity_table_name, p_match_rule_id,
entity_cur_rec.entity_name, match_all_flag);
IF insert_stmt_is_open
THEN
gen_insert_footer_int_tca(p_match_rule_id) ;
insert_stmt_is_open := false;
gen_update_template_int_tca(entity_cur_rec.search_table_name,entity_cur_rec.entity_table_name,
p_match_rule_id, entity_cur_rec.entity_name, match_all_flag);
gen_insert_template_int_tca(entity_cur_rec.search_table_name,entity_cur_rec.entity_table_name,
p_match_rule_id, entity_cur_rec.entity_name, match_all_flag);
IF insert_stmt_is_open
THEN
gen_insert_footer_int_tca(p_match_rule_id);
insert_stmt_is_open := false;
gen_update_template_int_tca(entity_cur_rec.search_table_name, entity_cur_rec.entity_table_name,
p_match_rule_id, entity_cur_rec.entity_name, match_all_flag);
IF insert_stmt_is_open
THEN
gen_insert_footer_int_tca(p_match_rule_id) ;
insert_stmt_is_open := false;
gen_dl_insert_template_int_tca(entity_cur_rec.search_table_name,
entity_cur_rec.entity_table_name,
p_match_rule_id,
entity_cur_rec.entity_name,
entity_cur_rec.entity_id_name,
entity_cur_rec.entity_osr_name,
entity_cur_rec.entity_os_name,
match_all_flag );
temp := get_insert_threshold(p_match_rule_id);
l(' x_insert_threshold number := ' || temp || ';');
l('FND_FILE.put_line(FND_FILE.log,''Start time of insert of Parties ''||to_char(sysdate,''hh24:mi:ss''));');
l('insert into hz_int_dup_results(batch_id, f_osr,t_osr,ord_f_osr,ord_t_osr,score,f_os, t_os)');
l('select p_batch_id, f, t, least(f,t), greatest(f,t), sum(score) score, fos, tos from (');
PROCEDURE gen_insert_template_int(
s_table VARCHAR2,
p_table VARCHAR2,
p_match_rule_id NUMBER,
p_entity VARCHAR2,
p_match_all_flag VARCHAR2
)
IS
FIRST BOOLEAN ;
l('select f, t, max(score) score, fos, tos from (');
l('select /*+ USE_CONCAT */ s1.party_osr f, s2.party_osr t,');
SELECT count(1) into outer_row_count
FROM HZ_MATCH_RULE_SECONDARY s, HZ_TRANS_ATTRIBUTES_VL a
where a.attribute_id=s.attribute_id
and s.match_rule_id=p_match_rule_id
and a.entity_name = p_entity;
SELECT score,s.attribute_id , secondary_attribute_id
FROM HZ_MATCH_RULE_SECONDARY s, HZ_TRANS_ATTRIBUTES_VL a
where a.attribute_id=s.attribute_id
and s.match_rule_id=p_match_rule_id
and a.entity_name = p_entity)
LOOP
outer_row_counter := outer_row_counter + 1;
SELECT round(transformation_weight/100*attrs.score) score, staged_attribute_column
FROM hz_secondary_trans st, hz_trans_functions_vl f
where f.function_id=st.function_id
and st.secondary_attribute_id = attrs.secondary_attribute_id
order by transformation_weight desc)
LOOP
inner_row_counter := inner_row_counter + 1;
l('and s2.batch_id = p_batch_id and not exists (select 1 from HZ_INT_DUP_RESULTS WHERE t_osr = s1.party_osr and batch_id = p_batch_id)');
SELECT primary_attribute_id
FROM HZ_MATCH_RULE_PRIMARY p, HZ_TRANS_ATTRIBUTES_VL a
where p.match_rule_id=p_match_rule_id
and p.attribute_id=a.attribute_id
and a.entity_name = p_entity
and nvl(p.filter_flag,'N') = 'N' )
LOOP
-- between attributes
IF FIRST1
THEN
FIRST1 := FALSE;
FOR trans in ( SELECT staged_attribute_column
FROM hz_primary_trans pt, hz_trans_functions_vl f
where f.function_id = pt.function_id
and pt.primary_attribute_id = attrs.primary_attribute_id
)
LOOP
IF FIRST
THEN
l('-- do an or between all the transformations of an attribute -- ');
SELECT primary_attribute_id
FROM HZ_MATCH_RULE_PRIMARY p, HZ_TRANS_ATTRIBUTES_VL a
where p.match_rule_id=p_match_rule_id
and p.attribute_id=a.attribute_id
and a.entity_name = p_entity
and nvl(p.filter_flag,'N') = 'Y' )
LOOP
-- between attributes
IF FIRST1
THEN
FIRST1 := FALSE;
FOR trans in ( SELECT staged_attribute_column
FROM hz_primary_trans pt, hz_trans_functions_vl f
where f.function_id = pt.function_id
and pt.primary_attribute_id = attrs.primary_attribute_id
)
LOOP
IF FIRST
THEN
l('-- do an or between all the transformations of an attribute -- ');
PROCEDURE gen_dl_insert_template_int(
s_table VARCHAR2,
p_table VARCHAR2,
p_match_rule_id NUMBER,
p_entity VARCHAR2,
p_entity_osr_name VARCHAR2,
p_entity_os_name VARCHAR2,
p_match_all_flag VARCHAR2
)
IS
FIRST BOOLEAN ;
l('FND_FILE.put_line(FND_FILE.log,'||''''|| 'Beginning insert of ' || p_entity || ''''|| ');');
l('FND_FILE.put_line(FND_FILE.log,''Start time of insert ''||to_char(sysdate,''hh24:mi:ss''));');
l('insert into hz_imp_int_dedup_results(batch_id, winner_record_osr, winner_record_os,');
l('dup_creation_date,dup_last_update_date');
l(',created_by,creation_date,last_update_login,last_update_date,last_updated_by)');
l('select /*+ USE_CONCAT */ p_batch_id, s1.' || p_entity_osr_name || ', s1.' || p_entity_os_name || ',');
SELECT count(1) into outer_row_count
FROM HZ_MATCH_RULE_SECONDARY s, HZ_TRANS_ATTRIBUTES_VL a
where a.attribute_id=s.attribute_id
and s.match_rule_id=p_match_rule_id
and a.entity_name = p_entity;
SELECT score,s.attribute_id , secondary_attribute_id
FROM HZ_MATCH_RULE_SECONDARY s, HZ_TRANS_ATTRIBUTES_VL a
where a.attribute_id=s.attribute_id
and s.match_rule_id=p_match_rule_id
and a.entity_name = p_entity)
LOOP
outer_row_counter := outer_row_counter + 1;
SELECT round(transformation_weight/100*attrs.score) score, staged_attribute_column
FROM hz_secondary_trans st, hz_trans_functions_vl f
where f.function_id=st.function_id
and st.secondary_attribute_id = attrs.secondary_attribute_id
order by transformation_weight desc)
LOOP
inner_row_counter := inner_row_counter + 1;
l('score ,hz_utility_v2pub.creation_date, hz_utility_v2pub.last_update_date');
l(',hz_utility_v2pub.created_by,hz_utility_v2pub.creation_date,hz_utility_v2pub.last_update_login');
l(',hz_utility_v2pub.last_update_date,hz_utility_v2pub.last_updated_by');
SELECT primary_attribute_id
FROM HZ_MATCH_RULE_PRIMARY p, HZ_TRANS_ATTRIBUTES_VL a
where p.match_rule_id=p_match_rule_id
and p.attribute_id=a.attribute_id
and a.entity_name = p_entity
and nvl(p.filter_flag,'N') = 'N' )
LOOP
-- between attributes
IF FIRST1
THEN
FIRST1 := FALSE;
FOR trans in ( SELECT staged_attribute_column
FROM hz_primary_trans pt, hz_trans_functions_vl f
where f.function_id = pt.function_id
and pt.primary_attribute_id = attrs.primary_attribute_id
)
LOOP
IF FIRST
THEN
l('-- do an or between all the transformations of an attribute -- ');
SELECT primary_attribute_id
FROM HZ_MATCH_RULE_PRIMARY p, HZ_TRANS_ATTRIBUTES_VL a
where p.match_rule_id=p_match_rule_id
and p.attribute_id=a.attribute_id
and a.entity_name = p_entity
and nvl(p.filter_flag,'N') = 'Y'
and HZ_IMP_DQM_STAGE.EXIST_COL(a.attribute_name, a.entity_name ) = 'Y'
)
LOOP
-- between attributes
IF FIRST1
THEN
FIRST1 := FALSE;
FOR trans in ( SELECT staged_attribute_column
FROM hz_primary_trans pt, hz_trans_functions_vl f
where f.function_id = pt.function_id
and pt.primary_attribute_id = attrs.primary_attribute_id
)
LOOP
IF FIRST
THEN
l('-- do an or between all the transformations of an attribute -- ');
l('FND_FILE.put_line(FND_FILE.log,'||''''|| 'Ending insert of ' || p_entity || ''''|| ');');
l('FND_FILE.put_line(FND_FILE.log,''Number of records inserted ''||SQL%ROWCOUNT);');
l('FND_FILE.put_line(FND_FILE.log,''End time to insert ''||to_char(sysdate,''hh24:mi:ss''));');
PROCEDURE gen_insert_footer_int(p_match_rule_id number)
IS
FIRST1 boolean;
l('SELECT 1 FROM HZ_SRCH_PARTIES p1, HZ_SRCH_PARTIES p2');
SELECT primary_attribute_id
FROM HZ_MATCH_RULE_PRIMARY p, HZ_TRANS_ATTRIBUTES_VL a
where p.match_rule_id=p_match_rule_id
and p.attribute_id=a.attribute_id
and a.entity_name = 'PARTY'
and nvl(p.filter_flag,'N') = 'Y'
and HZ_IMP_DQM_STAGE.EXIST_COL(a.attribute_name, a.entity_name ) = 'Y'
)
LOOP
IF FIRST1
THEN
FIRST1 := FALSE;
FOR trans in ( SELECT staged_attribute_column
FROM hz_primary_trans pt, hz_trans_functions_vl f
where f.function_id = pt.function_id
and pt.primary_attribute_id = attrs.primary_attribute_id
)
LOOP
IF FIRST
THEN
l('-- do an or between all the transformations of an attribute -- ');
IF get_insert_threshold(p_match_rule_id) > 0
THEN
l('having sum(score) >= x_insert_threshold');
l('FND_FILE.put_line(FND_FILE.log,''Number of parties inserted ''||SQL%ROWCOUNT);');
l('FND_FILE.put_line(FND_FILE.log,''End time of insert ''||to_char(sysdate,''hh24:mi:ss''));');
PROCEDURE gen_update_template_int (
s_table VARCHAR2,
p_table VARCHAR2,
p_match_rule_id NUMBER,
p_entity VARCHAR2,
p_match_all_flag VARCHAR2
)
IS
FIRST BOOLEAN ;
l('FND_FILE.put_line(FND_FILE.log,'||''''|| 'Beginning update of Parties on the basis of ' || p_entity || ''''|| ');');
l('FND_FILE.put_line(FND_FILE.log,''Start time of update ''||to_char(sysdate,''hh24:mi:ss''));');
l('select f,t,max(score) from (');
l(' select /*+ USE_CONCAT */ s1.party_osr f, s2.party_osr t,');
SELECT count(1) into outer_row_count
FROM HZ_MATCH_RULE_SECONDARY s, HZ_TRANS_ATTRIBUTES_VL a
where a.attribute_id=s.attribute_id
and s.match_rule_id=p_match_rule_id
and a.entity_name = p_entity;
SELECT score,s.attribute_id , secondary_attribute_id
FROM HZ_MATCH_RULE_SECONDARY s, HZ_TRANS_ATTRIBUTES_VL a
where a.attribute_id=s.attribute_id
and s.match_rule_id=p_match_rule_id
and a.entity_name = p_entity)
LOOP
outer_row_counter := outer_row_counter + 1;
SELECT round(transformation_weight/100*attrs.score) score, staged_attribute_column
FROM hz_secondary_trans st, hz_trans_functions_vl f
where f.function_id=st.function_id
and st.secondary_attribute_id = attrs.secondary_attribute_id
order by transformation_weight desc)
LOOP
inner_row_counter := inner_row_counter + 1;
SELECT primary_attribute_id
FROM HZ_MATCH_RULE_PRIMARY p, HZ_TRANS_ATTRIBUTES_VL a
where p.match_rule_id=p_match_rule_id
and p.attribute_id=a.attribute_id
and a.entity_name = p_entity
and nvl(p.filter_flag,'N') = 'N' )
LOOP
-- between attributes
IF FIRST1
THEN
FIRST1 := FALSE;
FOR trans in ( SELECT staged_attribute_column
FROM hz_primary_trans pt, hz_trans_functions_vl f
where f.function_id = pt.function_id
and pt.primary_attribute_id = attrs.primary_attribute_id
)
LOOP
IF FIRST
THEN
l('-- do an or between all the transformations of an attribute -- ');
SELECT primary_attribute_id
FROM HZ_MATCH_RULE_PRIMARY p, HZ_TRANS_ATTRIBUTES_VL a
where p.match_rule_id=p_match_rule_id
and p.attribute_id=a.attribute_id
and a.entity_name = p_entity
and nvl(p.filter_flag,'N') = 'Y' )
LOOP
-- between attributes
IF FIRST1
THEN
FIRST1 := FALSE;
FOR trans in ( SELECT staged_attribute_column
FROM hz_primary_trans pt, hz_trans_functions_vl f
where f.function_id = pt.function_id
and pt.primary_attribute_id = attrs.primary_attribute_id
)
LOOP
IF FIRST
THEN
l('-- do an or between all the transformations of an attribute -- ');
l('HZ_DQM_DUP_ID_PKG.update_hz_int_dup_results(p_batch_id,x_ent_cur);');
l('FND_FILE.put_line(FND_FILE.log,''Number of parties updated ''||SQL%ROWCOUNT);');
l('FND_FILE.put_line(FND_FILE.log,''End time to update ''||to_char(sysdate,''hh24:mi:ss''));');
l('FND_FILE.put_line(FND_FILE.log,'||''''|| 'Ending update of Parties on the basis of ' || p_entity || ''''|| ');');
select entity_name, search_table_name, entity_table_name, entity_osr_name, entity_os_name, sc, att_flag
from
(select entity_name, decode(entity_name,
'PARTY','HZ_SRCH_PARTIES',
'PARTY_SITES', 'HZ_SRCH_PSITES',
'CONTACTS','HZ_SRCH_CONTACTS',
'CONTACT_POINTS', 'HZ_SRCH_CPTS') search_table_name,
decode(entity_name,
'PARTY','HZ_STAGED_PARTIES',
'PARTY_SITES', 'HZ_STAGED_PARTY_SITES',
'CONTACTS','HZ_STAGED_CONTACTS',
'CONTACT_POINTS', 'HZ_STAGED_CONTACT_POINTS') entity_table_name,
decode(entity_name,
'PARTY','PARTY_OSR',
'PARTY_SITES', 'PARTY_SITE_OSR',
'CONTACTS','CONTACT_OSR',
'CONTACT_POINTS', 'CONTACT_PT_OSR') entity_osr_name,
decode(entity_name,
'PARTY','PARTY_OS',
'PARTY_SITES', 'PARTY_SITE_OS',
'CONTACTS','CONTACT_OS',
'CONTACT_POINTS', 'CONTACT_PT_OS') entity_os_name,
sum(score) sc, 'S' att_flag
from hz_trans_attributes_vl a, hz_match_rule_secondary s
where s.match_rule_id = p_match_rule_id
and s.attribute_id = a.attribute_id
group by entity_name
union all
select entity_name, decode(entity_name,
'PARTY','HZ_SRCH_PARTIES',
'PARTY_SITES', 'HZ_SRCH_PSITES',
'CONTACTS','HZ_SRCH_CONTACTS',
'CONTACT_POINTS', 'HZ_SRCH_CPTS') search_table_name,
decode(entity_name,
'PARTY','HZ_STAGED_PARTIES',
'PARTY_SITES', 'HZ_STAGED_PARTY_SITES',
'CONTACTS','HZ_STAGED_CONTACTS',
'CONTACT_POINTS', 'HZ_STAGED_CONTACT_POINTS') entity_table_name,
decode(entity_name,
'PARTY','PARTY_OSR',
'PARTY_SITES', 'PARTY_SITE_OSR',
'CONTACTS','CONTACT_OSR',
'CONTACT_POINTS', 'CONTACT_PT_OSR') entity_osr_name,
decode(entity_name,
'PARTY','PARTY_OS',
'PARTY_SITES', 'PARTY_SITE_OS',
'CONTACTS','CONTACT_OS',
'CONTACT_POINTS', 'CONTACT_PT_OS') entity_os_name,
0 sc, 'P' att_flag
from hz_trans_attributes_vl a, hz_match_rule_primary p
where p.match_rule_id = p_match_rule_id
and p.attribute_id = a.attribute_id
group by entity_name
) where att_flag = p_att_flag
order by sc desc ;
insert_stmt_is_open boolean;
select match_score, match_all_flag into threshold, match_all_flag
from hz_match_rules_vl
where match_rule_id = p_match_rule_id;
insert_stmt_is_open := false;
gen_insert_template_int(entity_cur_rec.search_table_name,entity_cur_rec.entity_table_name, p_match_rule_id,
entity_cur_rec.entity_name, match_all_flag);
insert_stmt_is_open := true;
gen_insert_template_int(entity_cur_rec.search_table_name,entity_cur_rec.entity_table_name, p_match_rule_id,
entity_cur_rec.entity_name, match_all_flag);
IF insert_stmt_is_open
THEN
gen_insert_footer_int(p_match_rule_id) ;
insert_stmt_is_open := false;
gen_update_template_int(entity_cur_rec.search_table_name,entity_cur_rec.entity_table_name, p_match_rule_id,
entity_cur_rec.entity_name, match_all_flag);
gen_insert_template_int(entity_cur_rec.search_table_name,entity_cur_rec.entity_table_name,
p_match_rule_id, entity_cur_rec.entity_name, match_all_flag);
IF insert_stmt_is_open
THEN
gen_insert_footer_int(p_match_rule_id) ;
insert_stmt_is_open := false;
gen_update_template_int(entity_cur_rec.search_table_name,entity_cur_rec.entity_table_name, p_match_rule_id,
entity_cur_rec.entity_name, match_all_flag);
gen_insert_template_int(entity_cur_rec.search_table_name,entity_cur_rec.entity_table_name, p_match_rule_id,
entity_cur_rec.entity_name, match_all_flag);
IF insert_stmt_is_open
THEN
gen_insert_footer_int(p_match_rule_id) ;
insert_stmt_is_open := false;
gen_update_template_int(entity_cur_rec.search_table_name,entity_cur_rec.entity_table_name,
p_match_rule_id, entity_cur_rec.entity_name, match_all_flag);
gen_insert_template_int(entity_cur_rec.search_table_name,entity_cur_rec.entity_table_name,
p_match_rule_id, entity_cur_rec.entity_name, match_all_flag);
IF insert_stmt_is_open
THEN
gen_insert_footer_int(p_match_rule_id);
insert_stmt_is_open := false;
gen_update_template_int(entity_cur_rec.search_table_name, entity_cur_rec.entity_table_name,
p_match_rule_id, entity_cur_rec.entity_name, match_all_flag);
IF insert_stmt_is_open
THEN
gen_insert_footer_int(p_match_rule_id) ;
insert_stmt_is_open := false;
gen_dl_insert_template_int(entity_cur_rec.search_table_name,entity_cur_rec.entity_table_name,
p_match_rule_id, entity_cur_rec.entity_name, entity_cur_rec.entity_osr_name,
entity_cur_rec.entity_os_name, match_all_flag );