eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.HZ_IMP_MATCH_RULE_51 SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 78


  select decode(a.party_type, 'ORGANIZATION', a.organization_name, 'PERSON', a.person_first_name || ' ' || a.person_last_name) as PARTY_NAME , a.PARTY_TYPE, a.JGZZ_FISCAL_CODE, a.party_orig_system, a.party_orig_system_reference, b.party_id, a.rowid, a.party_type
    		from hz_imp_parties_int a, hz_imp_parties_sg b 
    		where  b.action_flag = 'I'
    		and b.int_row_id = a.rowid 
            and a.batch_id = p_batch_id 
            and b.party_orig_system_reference >=  p_from_osr 
            and b.party_orig_system_reference <= p_to_osr  
            and b.batch_mode_flag = p_batch_mode_flag 
            and interface_status is null ;

Line: 109


        INSERT INTO HZ_SRCH_PARTIES (
          TX2, TX36, TX45, TX59, PARTY_OS, PARTY_OSR, PARTY_ID, BATCH_ID, INT_ROW_ID
        ) VALUES ( 
          H_TX2(I), H_TX36(I), H_TX45(I), H_TX59(I),  H_P_PARTY_OS(I),  H_P_PARTY_OSR(I),  H_P_PARTY_ID(I), P_BATCH_ID, H_P_ROW_ID(I)
            );

Line: 142


  select  decode(accept_standardized_flag, 'Y', a.CITY_STD, a.CITY),  decode(accept_standardized_flag, 'Y', a.POSTAL_CODE_STD, a.POSTAL_CODE),  a.STATE,  decode(accept_standardized_flag, 'Y', a.COUNTRY_STD, a.COUNTRY), a.party_orig_system, a.party_orig_system_reference, a.site_orig_system, a.site_orig_system_reference, b.party_id, b.party_site_id, b.party_action_flag, a.rowid, decode(accept_standardized_flag, 'Y', a.ADDRESS1_STD, a.ADDRESS1) || ' ' || decode(accept_standardized_flag, 'Y', a.ADDRESS2_STD, a.ADDRESS2) || ' ' || decode(accept_standardized_flag, 'Y', a.ADDRESS3_STD, a.ADDRESS3) || ' ' || decode(accept_standardized_flag, 'Y', a.ADDRESS4_STD, a.ADDRESS4) as address 
            from hz_imp_addresses_int a, hz_imp_addresses_sg b 
            where a.batch_id = p_batch_id 
            and b.action_flag = 'I' 
            and b.int_row_id = a.rowid 
            and a.party_orig_system_reference >= p_from_osr 
            and a.party_orig_system_reference <= p_to_osr 
            and b.batch_mode_flag = p_batch_mode_flag 
            and interface_status is null ;

Line: 176


        INSERT INTO HZ_SRCH_PSITES ( 
          TX11, TX14, TX22, TX9, PARTY_SITE_ID, PARTY_ID, PARTY_OS, PARTY_OSR, PARTY_SITE_OS, PARTY_SITE_OSR, NEW_PARTY_FLAG, BATCH_ID, INT_ROW_ID , TX26
        ) VALUES (  
          H_TX11(I), H_TX14(I), H_TX22(I), H_TX9(I), H_P_PARTY_SITE_ID(I), H_P_PARTY_ID(I), H_P_PARTY_OS(I), H_P_PARTY_OSR(I), H_P_PS_OS(I), H_P_PS_OSR(I), H_P_N_PARTY(I), P_BATCH_ID, H_P_ROW_ID(I) , H_PS_CUST_TX26(I)
            );

Line: 207


  select a.EMAIL_ADDRESS, a.party_orig_system, a.party_orig_system_reference, a.cp_orig_system, a.cp_orig_system_reference, a.site_orig_system, a.site_orig_system_reference, b.party_site_id, b.contact_point_id, b.party_id, b.party_action_flag, a.rowid, a.contact_point_type, decode(a.raw_phone_number, null, a.PHONE_COUNTRY_CODE||a.PHONE_AREA_CODE ||a.phone_number, a.raw_phone_number) as raw_phone_number 
    	from  HZ_IMP_CONTACTPTS_INT a,  HZ_IMP_CONTACTPTS_SG b --
    	where a.batch_id = p_batch_id  
    	and b.action_flag = 'I' 
 		and b.int_row_id = a.rowid 
    	and b.party_orig_system_reference >= p_from_osr 
    	and b.party_orig_system_reference <= p_to_osr 
       and b.batch_mode_flag = p_batch_mode_flag 
       and interface_status is null ;

Line: 236


         INSERT INTO HZ_SRCH_CPTS  ( 
          TX5, PARTY_SITE_ID, PARTY_ID, PARTY_OS, PARTY_OSR, PARTY_SITE_OS, PARTY_SITE_OSR, CONTACT_POINT_ID, CONTACT_PT_OS, CONTACT_PT_OSR, NEW_PARTY_FLAG, BATCH_ID, INT_ROW_ID, CONTACT_POINT_TYPE , TX10, TX158
        ) VALUES ( 
          H_TX5(I), H_P_PARTY_SITE_ID(I), H_P_PARTY_ID(I), H_P_PARTY_OS(I), H_P_PARTY_OSR(I), H_P_PS_OS(I), H_P_PS_OSR(I) , H_P_CONTACT_POINT_ID(I), H_P_CP_OS(I), H_P_CP_OSR(I), H_P_N_PARTY(I), P_BATCH_ID, H_P_ROW_ID(I), H_P_CP_TYPE(I) , H_CP_CUST_TX10(I), H_CP_CUST_TX158(I)
            );

Line: 264


      CURSOR c1 is    select 'Y' 
      from hz_trans_attributes_vl  
      where entity_name = 'CONTACTS'   
      and attribute_name = 'CONTACT_NAME' 
      and attribute_id in (    
      select attribute_id 
      from hz_match_rule_primary b 
      where match_rule_id = 51
      union 
      select attribute_id 
      from hz_match_rule_secondary b 
      where match_rule_id = 51 ) and rownum = 1;

Line: 307


  select decode(a.party_type, 'ORGANIZATION', a.organization_name, 'PERSON', a.person_first_name || ' ' || a.person_last_name) as PARTY_NAME , a.PARTY_TYPE, a.JGZZ_FISCAL_CODE, a.party_orig_system, a.party_orig_system_reference, a.rowid, a.party_type  , a.party_id 
    		from hz_imp_parties_int a  
    		where a.batch_id = p_batch_id  
         and a.party_orig_system_reference >= p_from_osr 
         and a.party_orig_system_reference <= p_to_osr 
         and a.party_orig_system = l_os;

Line: 335


        INSERT INTO HZ_SRCH_PARTIES ( 
          TX2, TX36, TX45, TX59, PARTY_OS, PARTY_OSR, BATCH_ID, INT_ROW_ID , PARTY_ID 
        ) VALUES ( 
          H_TX2(I), H_TX36(I), H_TX45(I), H_TX59(I),  H_P_PARTY_OS(I),  H_P_PARTY_OSR(I), P_BATCH_ID, H_P_ROW_ID(I) , H_P_PARTY_ID(I) 
            );

Line: 368


  select a.CITY, a.POSTAL_CODE, a.STATE, a.COUNTRY, a.party_orig_system, a.party_orig_system_reference, a.site_orig_system, a.site_orig_system_reference, a.rowid,  a.address1 || ' ' || a.address2 || ' ' || a.address3 || ' ' || a.address4 as address   , a.party_id 
             from hz_imp_addresses_int a 
             where a.batch_id = p_batch_id 
             and a.party_orig_system_reference >= p_from_osr 
             and a.party_orig_system_reference <= p_to_osr 
             and a.party_orig_system = l_os;

Line: 398


         INSERT INTO HZ_SRCH_PSITES ( 
          TX11, TX14, TX22, TX9, PARTY_OS, PARTY_OSR, PARTY_SITE_OS, PARTY_SITE_OSR, BATCH_ID, INT_ROW_ID, TX26 , PARTY_ID 
         ) VALUES ( 
          H_TX11(I), H_TX14(I), H_TX22(I), H_TX9(I), H_P_PARTY_OS(I), H_P_PARTY_OSR(I), H_P_PS_OS(I), H_P_PS_OSR(I), P_BATCH_ID, H_P_ROW_ID(I), H_PS_CUST_TX26(I) , H_P_PARTY_ID(I) 
             );

Line: 430


  select a.EMAIL_ADDRESS, a.party_orig_system, a.party_orig_system_reference, a.cp_orig_system, a.cp_orig_system_reference, a.site_orig_system, a.site_orig_system_reference, a.rowid, a.contact_point_type,decode(a.raw_phone_number, null, a.PHONE_COUNTRY_CODE||a.PHONE_AREA_CODE ||a.phone_number,a.raw_phone_number) as raw_phone_number  , a.party_id 
     	from HZ_IMP_CONTACTPTS_INT a 
     	where a.batch_id = p_batch_id  
     	and a.party_orig_system_reference >= p_from_osr 
     	and a.party_orig_system_reference <= p_to_osr 
         and a.party_orig_system = l_os;

Line: 457


         INSERT INTO HZ_SRCH_CPTS ( 
          TX5, PARTY_OS, PARTY_OSR, PARTY_SITE_OS, PARTY_SITE_OSR, CONTACT_PT_OS, CONTACT_PT_OSR, BATCH_ID, INT_ROW_ID, CONTACT_POINT_TYPE, TX10, TX158 , PARTY_ID 
         ) VALUES ( 
          H_TX5(I), H_P_PARTY_OS(I), H_P_PARTY_OSR(I), H_P_PS_OS(I), H_P_PS_OSR(I), H_P_CP_OS(I), H_P_CP_OSR(I), P_BATCH_ID, H_P_ROW_ID(I), H_P_CP_TYPE(I), H_CP_CUST_TX10(I), H_CP_CUST_TX158(I) ,H_P_PARTY_ID(I) 
             );

Line: 485


      CURSOR c1 is    select 'Y' 
      from hz_trans_attributes_vl  
      where entity_name = 'CONTACTS'   
      and attribute_name = 'CONTACT_NAME' 
      and attribute_id in (    
      select attribute_id 
      from hz_match_rule_primary b 
      where match_rule_id = 51
      union 
      select attribute_id 
      from hz_match_rule_secondary b 
      where match_rule_id = 51 ) and rownum = 1;

Line: 522


PROCEDURE tca_join_entities(trap_explosion in varchar2, rows_in_chunk in number, inserted_duplicates out number)
IS
    x_ent_cur	HZ_DQM_DUP_ID_PKG.EntityCur;

Line: 525


    x_insert_threshold number := 60;

Line: 529


FND_FILE.put_line(FND_FILE.log,'Start time of insert of Parties '||to_char(sysdate,'hh24:mi:ss'));

Line: 530


insert into hz_dup_results(fid, tid, ord_fid, ord_tid, score)
select f, t, least(f,t), greatest(f,t), sum(score) score  from (
select /*+ ORDERED */ s1.party_id f, s2.party_id t,
-------PARTY ENTITY: SCORING SECTION ---------
decode(instrb(s2.TX2,s1.TX2),1,80,
decode(instrb(s2.TX59,s1.TX59),1,72,
0
)
)
 +  
decode(instrb(s2.TX45,s1.TX45),1,200,
0
)
 score 
from hz_dup_worker_chunk_gt p, HZ_STAGED_PARTIES s1, HZ_STAGED_PARTIES s2
where p.party_id = s1.party_id and s1.party_id<>s2.party_id 
and nvl(s1.status,'A') = 'A' and nvl(s2.status,'A') = 'A' 
and 1=decode(trap_explosion,'N',1,decode(rownum,l_party_limit,to_number('A'),1))
and (
-------PARTY ENTITY: ACQUISITION ON NON-FILTER ATTRIBUTES---------
-- do an or between all the transformations of an attribute -- 
(
(s1.TX45 is not null and s2.TX45 like s1.TX45 || decode(sign(lengthb(s1.TX45)-3),1,'%',''))
)
or
-- do an or between all the transformations of an attribute -- 
(
(s1.TX59 is not null and s2.TX59 like s1.TX59 || decode(sign(lengthb(s1.TX59)-3),1,'%',''))
)
)
union all
select f, t, max(score) score from (
select /*+ ORDERED */ s1.party_id f, s2.party_id t,
-------CONTACT_POINTS ENTITY: SCORING SECTION ---------
decode(instrb(s2.TX5,s1.TX5),1,60,
0
)
 +  
decode(instrb(s2.TX158,s1.TX158),1,70,
decode(instrb(s2.TX10,s1.TX10),1,70,
0
)
)
 score 
from hz_dup_worker_chunk_gt p, HZ_STAGED_CONTACT_POINTS s1, HZ_STAGED_CONTACT_POINTS s2
where p.party_id = s1.party_id and s1.party_id<>s2.party_id 
and exists(SELECT 1 from hz_staged_parties q where q.party_id = s2.party_id and nvl(q.status,'A') = 'A') 
and 1=decode(trap_explosion,'N',1,decode(rownum,l_detail_limit,to_number('A'),1))
and (
-------CONTACT_POINTS ENTITY: ACQUISITION ON NON-FILTER ATTRIBUTES---------
-- do an or between all the transformations of an attribute -- 
(
(s1.TX158 is not null and s2.TX158 like s1.TX158 || decode(sign(lengthb(s1.TX158)-3),1,'%',''))
)
or
-- do an or between all the transformations of an attribute -- 
(
(s1.TX5 is not null and s2.TX5 like s1.TX5 || decode(sign(lengthb(s1.TX5)-3),1,'%',''))
)
)
 ) group by f, t 
 )
------- ONE TIME CHECK FOR PARTY LEVEL FILTER ATTRIBUTES---------
where EXISTS (
SELECT 1 FROM HZ_STAGED_PARTIES p1, HZ_STAGED_PARTIES p2
WHERE p1.party_id = f and p2.party_id = t
and
-- do an or between all the transformations of an attribute -- 
(
((p1.TX36 is null and p2.TX36 is null) or p2.TX36 = p1.TX36)
)
)
group by f, t 
having sum(score) >= x_insert_threshold
;

Line: 605


inserted_duplicates := (SQL%ROWCOUNT);

Line: 606


FND_FILE.put_line(FND_FILE.log,'Number of parties inserted '||SQL%ROWCOUNT);

Line: 607


FND_FILE.put_line(FND_FILE.log,'End time of insert '||to_char(sysdate,'hh24:mi:ss'));

Line: 612


FND_FILE.put_line(FND_FILE.log,'Beginning update of Parties on the basis of PARTY_SITES');

Line: 613


FND_FILE.put_line(FND_FILE.log,'Start time of update '||to_char(sysdate,'hh24:mi:ss'));

Line: 615


select f, t, max(score) score from (
 select /*+ ORDERED */ s1.party_id f, s2.party_id t,
decode(instrb(s2.TX26,s1.TX26),1,100,
0
)
+
decode(instrb(s2.TX9,s1.TX9),1,5,
0
)
+
decode(instrb(s2.TX14,s1.TX14),1,5,
0
)
+
decode(instrb(s2.TX22,s1.TX22),1,5,
0
)
score
from hz_dup_worker_chunk_gt p, hz_dup_results h1, HZ_STAGED_PARTY_SITES s1, HZ_STAGED_PARTY_SITES s2
where p.party_id=h1.fid and s1.party_id = h1.fid and s2.party_id = h1.tid
and ( 
------------ NON FILTER ATTRIBUTES SECTION ------------------------
-- do an or between all the transformations of an attribute -- 
(
(s1.TX26 is not null and s2.TX26 like s1.TX26 || decode(sign(lengthb(s1.TX26)-3),1,'%',''))
)
)
------------ FILTER ATTRIBUTES SECTION ------------------------
and 
-- do an or between all the transformations of an attribute -- 
(
((s1.TX11 is null and s2.TX11 is null) or s2.TX11 = s1.TX11)
)
) group by f,t ;

Line: 649


HZ_DQM_DUP_ID_PKG.update_hz_dup_results(x_ent_cur);

Line: 651


FND_FILE.put_line(FND_FILE.log,'Number of parties updated '||SQL%ROWCOUNT);

Line: 652


FND_FILE.put_line(FND_FILE.log,'End time to update '||to_char(sysdate,'hh24:mi:ss'));

Line: 653


FND_FILE.put_line(FND_FILE.log,'Ending update of Parties on the basis of PARTY_SITES');

Line: 662


             inserted_duplicates := -1;

Line: 682


x_insert_threshold number := 60;

Line: 686


FND_FILE.put_line(FND_FILE.log,'Start time of insert of Parties '||to_char(sysdate,'hh24:mi:ss'));

Line: 687


insert into hz_imp_dup_parties(party_id,dup_party_id, score, party_osr, party_os, batch_id, auto_merge_flag
,created_by,creation_date,last_update_login,last_update_date,last_updated_by)
select f, t, sum(score) sc, party_osr, party_os, p_batch_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
from (
------------------ PARTY LEVEL DUPLICATE IDENTIFICATION BEGINS --------------------
select /*+ USE_CONCAT */ s1.party_id f, s2.party_id t,
-------PARTY ENTITY: SCORING SECTION ---------
decode(instrb(s2.TX2,s1.TX2),1,80,
decode(instrb(s2.TX59,s1.TX59),1,72,
0
)
)
+
decode(instrb(s2.TX45,s1.TX45),1,200,
0
)
score , s1.party_osr party_osr, s1.party_os party_os
from HZ_SRCH_PARTIES s1, HZ_STAGED_PARTIES s2 
where s1.party_id is not null and s1.batch_id = p_batch_id and s1.party_osr between from_osr and to_osr
and nvl(s2.status,'A') = 'A' 
and ( 
-------PARTY ENTITY: ACQUISITION ON NON-FILTER ATTRIBUTES ---------
-- do an or between all the transformations of an attribute -- 
(
(s1.TX45 is not null and s2.TX45 like s1.TX45 || decode(sign(lengthb(s1.TX45)-3),1,'%',''))
)
or
-- do an or between all the transformations of an attribute -- 
(
(s1.TX59 is not null and s2.TX59 like s1.TX59 || decode(sign(lengthb(s1.TX59)-3),1,'%',''))
)
)
union all
select f, t, max(score) score, party_osr, party_os from (
select /*+ USE_CONCAT */ s1.party_id f, s2.party_id t,
-------CONTACT_POINTS ENTITY: SCORING SECTION ---------
decode(instrb(s2.TX5,s1.TX5),1,60,
0
)
+
decode(instrb(s2.TX158,s1.TX158),1,70,
decode(instrb(s2.TX10,s1.TX10),1,70,
0
)
)
score , s1.party_osr party_osr, s1.party_os party_os
from HZ_SRCH_CPTS s1, HZ_STAGED_CONTACT_POINTS s2
where s1.party_id is not null and s1.batch_id = p_batch_id and s1.party_osr between from_osr and to_osr and s1.new_party_flag = 'I'
and exists(SELECT 1 from hz_staged_parties q where q.party_id = s2.party_id and nvl(q.status,'A') = 'A') 
and ( 
-------CONTACT_POINTS ENTITY: ACQUISITION ON NON-FILTER ATTRIBUTES ---------
-- do an or between all the transformations of an attribute -- 
(
(s1.TX158 is not null and s2.TX158 like s1.TX158 || decode(sign(lengthb(s1.TX158)-3),1,'%',''))
)
or
-- do an or between all the transformations of an attribute -- 
(
(s1.TX5 is not null and s2.TX5 like s1.TX5 || decode(sign(lengthb(s1.TX5)-3),1,'%',''))
)
)
)
group by f, t, party_osr, party_os
)
------- ONE TIME CHECK FOR PARTY LEVEL FILTER ATTRIBUTES---------
where EXISTS (
SELECT 1 FROM HZ_SRCH_PARTIES p1, HZ_STAGED_PARTIES p2
WHERE p1.batch_id = p_batch_id and p1.party_osr = party_osr and p1.party_os = party_os
and p2.party_id = t
and
-- do an or between all the transformations of an attribute -- 
(
((p1.TX36 is null and p2.TX36 is null) or p2.TX36 = p1.TX36 || ' ' )
)
)
group by f, t, party_osr, party_os
having sum(score) >= x_insert_threshold
;

Line: 767


FND_FILE.put_line(FND_FILE.log,'Number of parties inserted '||SQL%ROWCOUNT);

Line: 768


FND_FILE.put_line(FND_FILE.log,'End time of insert '||to_char(sysdate,'hh24:mi:ss'));

Line: 772


FND_FILE.put_line(FND_FILE.log,'Beginning update of Parties on the basis of PARTY_SITES');

Line: 773


FND_FILE.put_line(FND_FILE.log,'Start time of update '||to_char(sysdate,'hh24:mi:ss'));

Line: 775


select f,t,max(score) from (
 select /*+ USE_CONCAT */ s1.party_id f, s2.party_id t,
decode(instrb(s2.TX26,s1.TX26),1,100,
0
)
+
decode(instrb(s2.TX9,s1.TX9),1,5,
0
)
+
decode(instrb(s2.TX14,s1.TX14),1,5,
0
)
+
decode(instrb(s2.TX22,s1.TX22),1,5,
0
)
score
from hz_imp_dup_parties h1, HZ_SRCH_PSITES s1, HZ_STAGED_PARTY_SITES s2
where h1.batch_id = p_batch_id and s1.party_osr between from_osr and to_osr
and s1.batch_id = h1.batch_id and s1.party_osr = h1.party_osr and s1.party_os = h1.party_os and s2.party_id = h1.dup_party_id
and ( 
------------ NON FILTER ATTRIBUTES SECTION ------------------------
-- do an or between all the transformations of an attribute -- 
(
(s1.TX26 is not null and s2.TX26 like s1.TX26 || decode(sign(lengthb(s1.TX26)-3),1,'%',''))
)
)
------------ FILTER ATTRIBUTES SECTION ------------------------
and 
-- do an or between all the transformations of an attribute -- 
(
((s1.TX11 is null and s2.TX11 is null) or s2.TX11 = s1.TX11 || ' ' )
)
) group by f,t ;

Line: 810


HZ_DQM_DUP_ID_PKG.update_hz_imp_dup_parties(p_batch_id, x_ent_cur);

Line: 812


FND_FILE.put_line(FND_FILE.log,'Number of parties updated '||SQL%ROWCOUNT);

Line: 813


FND_FILE.put_line(FND_FILE.log,'End time to update '||to_char(sysdate,'hh24:mi:ss'));

Line: 814


FND_FILE.put_line(FND_FILE.log,'Ending update of Parties on the basis of PARTY_SITES');

Line: 819


FND_FILE.put_line(FND_FILE.log,'DELETE ON THRESHOLD AND INDIRECT TRANSITIVITY ');

Line: 820


FND_FILE.put_line(FND_FILE.log,'Begin time to delete '||to_char(sysdate,'hh24:mi:ss'));

Line: 822


delete from hz_imp_dup_parties a
where (a.party_osr >= from_osr and a.party_osr <= to_osr
and a.batch_id = p_batch_id)
and (
a.score < p_threshold
or
-- delete the party id whose duplicate is a bigger number, when scores are same
exists
      (Select 1 from hz_imp_dup_parties b
       where b.batch_id=p_batch_id and a.party_id=b.party_id and a.dup_party_id > b.dup_party_id and a.score = b.score)
or
-- delete the party id with least score, if scores are different
exists
      (Select 1 from hz_imp_dup_parties b
       where b.batch_id=p_batch_id and a.party_id=b.party_id and a.score < b.score)
);

Line: 839


FND_FILE.put_line(FND_FILE.log,'Number of records deleted from hz_imp_dup_parties '||SQL%ROWCOUNT);

Line: 840


FND_FILE.put_line(FND_FILE.log,'End time to delete '||to_char(sysdate,'hh24:mi:ss'));

Line: 842


update hz_imp_dup_parties a
set a.auto_merge_flag = 'Y'
where a.score >= p_auto_merge_threshold
and a.party_osr >= from_osr and a.party_osr <= to_osr
and a.batch_id = p_batch_id ;

Line: 850


select a.party_osr, a.party_os, a.auto_merge_flag
from hz_imp_dup_parties a
where a.batch_id = p_batch_id
and a.party_osr between from_osr and to_osr ;

Line: 854


HZ_DQM_DUP_ID_PKG.update_party_dqm_action_flag(p_batch_id, x_ent_cur);

Line: 860


FND_FILE.put_line(FND_FILE.log,'Beginning insert  of CONTACT_POINTS');

Line: 861


FND_FILE.put_line(FND_FILE.log,'Start time of insert '||to_char(sysdate,'hh24:mi:ss'));

Line: 862


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
,created_by,creation_date,last_update_login,last_update_date,last_updated_by)
select /*+ USE_CONCAT */ s1.party_id f,
decode(instrb(s2.TX5,s1.TX5),1,60,
0
)
+
decode(instrb(s2.TX158,s1.TX158),1,70,
decode(instrb(s2.TX10,s1.TX10),1,70,
0
)
)
score , s1.party_osr, s1.party_os, p_batch_id,'CONTACT_POINTS', s1.CONTACT_POINT_ID, s1.CONTACT_PT_OSR, s1.CONTACT_PT_OS,
                                                                      s2.CONTACT_POINT_ID
,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
from HZ_SRCH_CPTS s1, HZ_STAGED_CONTACT_POINTS s2 
where s1.batch_id = p_batch_id and s1.party_osr between from_osr and to_osr and s1.new_party_flag = 'U'
and s1.party_id = s2.party_id
and ( 
------------ NON FILTER ATTRIBUTES SECTION ------------------------
-- do an or between all the transformations of an attribute -- 
(
(s1.TX158 is not null and s2.TX158 like s1.TX158 || decode(sign(lengthb(s1.TX158)-3),1,'%',''))
)
or
-- do an or between all the transformations of an attribute -- 
(
(s1.TX5 is not null and s2.TX5 like s1.TX5 || decode(sign(lengthb(s1.TX5)-3),1,'%',''))
)
)
;

Line: 898


select distinct a.record_osr, a.record_os
from hz_imp_dup_details a
where a.batch_id = p_batch_id
and a.party_osr between from_osr and to_osr and a.entity ='CONTACT_POINTS';

Line: 902


HZ_DQM_DUP_ID_PKG.update_detail_dqm_action_flag('CONTACT_POINTS',p_batch_id, x_ent_cur);

Line: 904


FND_FILE.put_line(FND_FILE.log,'Ending insert of CONTACT_POINTS');

Line: 905


FND_FILE.put_line(FND_FILE.log,'Number of records inserted '||SQL%ROWCOUNT);

Line: 906


FND_FILE.put_line(FND_FILE.log,'End time to insert '||to_char(sysdate,'hh24:mi:ss'));

Line: 912


FND_FILE.put_line(FND_FILE.log,'Beginning insert  of PARTY_SITES');

Line: 913


FND_FILE.put_line(FND_FILE.log,'Start time of insert '||to_char(sysdate,'hh24:mi:ss'));

Line: 914


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
,created_by,creation_date,last_update_login,last_update_date,last_updated_by)
select /*+ USE_CONCAT */ s1.party_id f,
decode(instrb(s2.TX26,s1.TX26),1,100,
0
)
+
decode(instrb(s2.TX9,s1.TX9),1,5,
0
)
+
decode(instrb(s2.TX14,s1.TX14),1,5,
0
)
+
decode(instrb(s2.TX22,s1.TX22),1,5,
0
)
score , s1.party_osr, s1.party_os, p_batch_id,'PARTY_SITES', s1.PARTY_SITE_ID, s1.PARTY_SITE_OSR, s1.PARTY_SITE_OS,
                                                                      s2.PARTY_SITE_ID
,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
from HZ_SRCH_PSITES s1, HZ_STAGED_PARTY_SITES s2 
where s1.batch_id = p_batch_id and s1.party_osr between from_osr and to_osr and s1.new_party_flag = 'U'
and s1.party_id = s2.party_id
and ( 
------------ NON FILTER ATTRIBUTES SECTION ------------------------
-- do an or between all the transformations of an attribute -- 
(
(s1.TX26 is not null and s2.TX26 like s1.TX26 || decode(sign(lengthb(s1.TX26)-3),1,'%',''))
)
)
------------ FILTER ATTRIBUTES SECTION ------------------------
and 
-- do an or between all the transformations of an attribute -- 
(
((s1.TX11 is null and s2.TX11 is null) or s2.TX11 = s1.TX11 || ' ' )
)
;

Line: 957


select distinct a.record_osr, a.record_os
from hz_imp_dup_details a
where a.batch_id = p_batch_id
and a.party_osr between from_osr and to_osr and a.entity ='PARTY_SITES';

Line: 961


HZ_DQM_DUP_ID_PKG.update_detail_dqm_action_flag('PARTY_SITES',p_batch_id, x_ent_cur);

Line: 963


FND_FILE.put_line(FND_FILE.log,'Ending insert of PARTY_SITES');

Line: 964


FND_FILE.put_line(FND_FILE.log,'Number of records inserted '||SQL%ROWCOUNT);

Line: 965


FND_FILE.put_line(FND_FILE.log,'End time to insert '||to_char(sysdate,'hh24:mi:ss'));

Line: 989


    x_insert_threshold number := 60;

Line: 993


FND_FILE.put_line(FND_FILE.log,'Start time of insert of Parties '||to_char(sysdate,'hh24:mi:ss'));

Line: 994


insert into hz_int_dup_results(batch_id, f_osr,t_osr,ord_f_osr,ord_t_osr,score,f_os, t_os)
select p_batch_id, f, t, least(f,t), greatest(f,t), sum(score) score, fos, tos from (
------------------ PARTY LEVEL DUPLICATE IDENTIFICATION BEGINS --------------------
select /*+ USE_CONCAT */ s1.party_osr f, s2.party_osr t,
-------PARTY ENTITY: SCORING SECTION ---------
decode(instrb(s2.TX2,s1.TX2),1,80,
decode(instrb(s2.TX59,s1.TX59),1,72,
0
)
)
+
decode(instrb(s2.TX45,s1.TX45),1,200,
0
)
score, s1.party_os fos, s2.party_os tos
from HZ_SRCH_PARTIES s1, HZ_SRCH_PARTIES s2 
where s1.batch_id = p_batch_id and s1.party_osr between from_osr and to_osr and s1.party_osr <> s2.party_osr
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)
and (
-------PARTY ENTITY: ACQUISITION ON NON-FILTER ATTRIBUTES ---------
-- do an or between all the transformations of an attribute -- 
(
(s1.TX45 is not null and s2.TX45 like s1.TX45 || decode(sign(lengthb(s1.TX45)-3),1,'%',''))
)
or
-- do an or between all the transformations of an attribute -- 
(
(s1.TX59 is not null and s2.TX59 like s1.TX59 || decode(sign(lengthb(s1.TX59)-3),1,'%',''))
)
)
union all
select f, t, max(score) score, fos, tos from (
select /*+ USE_CONCAT */ s1.party_osr f, s2.party_osr t,
-------CONTACT_POINTS ENTITY: SCORING SECTION ---------
decode(instrb(s2.TX5,s1.TX5),1,60,
0
)
+
decode(instrb(s2.TX158,s1.TX158),1,70,
decode(instrb(s2.TX10,s1.TX10),1,70,
0
)
)
score, s1.party_os fos, s2.party_os tos
from HZ_SRCH_CPTS s1, HZ_SRCH_CPTS s2
where s1.batch_id = p_batch_id and s1.party_osr between from_osr and to_osr and s1.party_osr <> s2.party_osr
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)
and s1.contact_point_type = s2.contact_point_type
and (
-------CONTACT_POINTS ENTITY: ACQUISITION ON NON-FILTER ATTRIBUTES ---------
-- do an or between all the transformations of an attribute -- 
(
(s1.TX158 is not null and s2.TX158 like s1.TX158 || decode(sign(lengthb(s1.TX158)-3),1,'%',''))
)
or
-- do an or between all the transformations of an attribute -- 
(
(s1.TX5 is not null and s2.TX5 like s1.TX5 || decode(sign(lengthb(s1.TX5)-3),1,'%',''))
)
)
)
group by f, t, fos, tos
)
------- ONE TIME CHECK FOR PARTY LEVEL FILTER ATTRIBUTES---------
where EXISTS (
SELECT 1 FROM HZ_SRCH_PARTIES p1, HZ_SRCH_PARTIES p2
WHERE p1.batch_id = p_batch_id and p1.party_osr = f and p1.party_os = fos
and p2.batch_id = p_batch_id and p2.party_osr = t and p2.party_os = tos
and
-- do an or between all the transformations of an attribute -- 
(
((p1.TX36 is null and p2.TX36 is null) or p2.TX36 = p1.TX36)
)
)
group by f, t, fos, tos
having sum(score) >= x_insert_threshold
;

Line: 1071


FND_FILE.put_line(FND_FILE.log,'Number of parties inserted '||SQL%ROWCOUNT);

Line: 1072


FND_FILE.put_line(FND_FILE.log,'End time of insert '||to_char(sysdate,'hh24:mi:ss'));

Line: 1077


FND_FILE.put_line(FND_FILE.log,'Beginning update of Parties on the basis of PARTY_SITES');

Line: 1078


FND_FILE.put_line(FND_FILE.log,'Start time of update '||to_char(sysdate,'hh24:mi:ss'));

Line: 1080


select f,t,max(score) from (
 select /*+ USE_CONCAT */ s1.party_osr f, s2.party_osr t,
decode(instrb(s2.TX26,s1.TX26),1,100,
0
)
+
decode(instrb(s2.TX9,s1.TX9),1,5,
0
)
+
decode(instrb(s2.TX14,s1.TX14),1,5,
0
)
+
decode(instrb(s2.TX22,s1.TX22),1,5,
0
)
score
from hz_int_dup_results h1, HZ_SRCH_PSITES s1, HZ_SRCH_PSITES s2
where
s1.party_osr = h1.f_osr and s2.party_osr = h1.t_osr and h1.batch_id = p_batch_id
and s1.party_osr between from_osr and to_osr
and ( 
------------ NON FILTER ATTRIBUTES SECTION ------------------------
-- do an or between all the transformations of an attribute -- 
(
(s1.TX26 is not null and s2.TX26 like s1.TX26 || decode(sign(lengthb(s1.TX26)-3),1,'%',''))
)
)
------------ FILTER ATTRIBUTES SECTION ------------------------
and 
-- do an or between all the transformations of an attribute -- 
(
((s1.TX11 is null and s2.TX11 is null) or s2.TX11 = s1.TX11)
)
) group by f,t ;

Line: 1116


HZ_DQM_DUP_ID_PKG.update_hz_int_dup_results(p_batch_id,x_ent_cur);

Line: 1118


FND_FILE.put_line(FND_FILE.log,'Number of parties updated '||SQL%ROWCOUNT);

Line: 1119


FND_FILE.put_line(FND_FILE.log,'End time to update '||to_char(sysdate,'hh24:mi:ss'));

Line: 1120


FND_FILE.put_line(FND_FILE.log,'Ending update of Parties on the basis of PARTY_SITES');

Line: 1124


FND_FILE.put_line(FND_FILE.log,'Beginning insert  of CONTACT_POINTS');

Line: 1125


FND_FILE.put_line(FND_FILE.log,'Start time of insert '||to_char(sysdate,'hh24:mi:ss'));

Line: 1126


insert into hz_imp_int_dedup_results(batch_id, winner_record_osr, winner_record_os,
dup_record_osr, dup_record_os, detail_party_osr, detail_party_os, entity, score,
dup_creation_date,dup_last_update_date
,created_by,creation_date,last_update_login,last_update_date,last_updated_by)
select /*+ USE_CONCAT */ p_batch_id, s1.CONTACT_PT_OSR, s1.CONTACT_PT_OS,
s2.CONTACT_PT_OSR, s2.CONTACT_PT_OS,
s1.party_osr, s2.party_os,'CONTACT_POINTS',
decode(nvl(s1.TX5,'N1'),nvl(substrb(s2.TX5,1,length(s1.TX5)),'N2'),60, 
0
)
+
decode(nvl(s1.TX158,'N1'),nvl(substrb(s2.TX158,1,length(s1.TX158)),'N2'),70, 
decode(nvl(s1.TX10,'N1'),nvl(substrb(s2.TX10,1,length(s1.TX10)),'N2'),70, 
0
)
)
score ,hz_utility_v2pub.creation_date, hz_utility_v2pub.last_update_date
,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
from HZ_SRCH_CPTS s1, HZ_SRCH_CPTS s2 
where s1.batch_id = p_batch_id and s1.party_osr between from_osr and to_osr 
 and ( ( (s1.party_osr = s2.party_osr) and ( nvl(s1.party_id, 1) = nvl(s2.party_id,1) ) ) OR ( s1.party_id = s2.party_id) ) 
and s2.batch_id = p_batch_id and s1.CONTACT_PT_OSR < s2.CONTACT_PT_OSR
and s1.contact_point_type = s2.contact_point_type
and ( 
------------ NON FILTER ATTRIBUTES SECTION ------------------------
-- do an or between all the transformations of an attribute -- 
(
(s1.TX158 is not null and s2.TX158 like s1.TX158 || decode(sign(lengthb(s1.TX158)-3),1,'%',''))
)
or
-- do an or between all the transformations of an attribute -- 
(
(s1.TX5 is not null and s2.TX5 like s1.TX5 || decode(sign(lengthb(s1.TX5)-3),1,'%',''))
)
)
;

Line: 1163


FND_FILE.put_line(FND_FILE.log,'Ending insert of CONTACT_POINTS');

Line: 1164


FND_FILE.put_line(FND_FILE.log,'Number of records inserted '||SQL%ROWCOUNT);

Line: 1165


FND_FILE.put_line(FND_FILE.log,'End time to insert '||to_char(sysdate,'hh24:mi:ss'));

Line: 1169


FND_FILE.put_line(FND_FILE.log,'Beginning insert  of PARTY_SITES');

Line: 1170


FND_FILE.put_line(FND_FILE.log,'Start time of insert '||to_char(sysdate,'hh24:mi:ss'));

Line: 1171


insert into hz_imp_int_dedup_results(batch_id, winner_record_osr, winner_record_os,
dup_record_osr, dup_record_os, detail_party_osr, detail_party_os, entity, score,
dup_creation_date,dup_last_update_date
,created_by,creation_date,last_update_login,last_update_date,last_updated_by)
select /*+ USE_CONCAT */ p_batch_id, s1.PARTY_SITE_OSR, s1.PARTY_SITE_OS,
s2.PARTY_SITE_OSR, s2.PARTY_SITE_OS,
s1.party_osr, s2.party_os,'PARTY_SITES',
decode(nvl(s1.TX26,'N1'),nvl(substrb(s2.TX26,1,length(s1.TX26)),'N2'),100, 
0
)
+
decode(nvl(s1.TX9,'N1'),nvl(substrb(s2.TX9,1,length(s1.TX9)),'N2'),5, 
0
)
+
decode(nvl(s1.TX14,'N1'),nvl(substrb(s2.TX14,1,length(s1.TX14)),'N2'),5, 
0
)
+
decode(nvl(s1.TX22,'N1'),nvl(substrb(s2.TX22,1,length(s1.TX22)),'N2'),5, 
0
)
score ,hz_utility_v2pub.creation_date, hz_utility_v2pub.last_update_date
,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
from HZ_SRCH_PSITES s1, HZ_SRCH_PSITES s2 
where s1.batch_id = p_batch_id and s1.party_osr between from_osr and to_osr 
 and ( ( (s1.party_osr = s2.party_osr) and ( nvl(s1.party_id, 1) = nvl(s2.party_id,1) ) ) OR ( s1.party_id = s2.party_id) ) 
and s2.batch_id = p_batch_id and s1.PARTY_SITE_OSR < s2.PARTY_SITE_OSR
and ( 
------------ NON FILTER ATTRIBUTES SECTION ------------------------
-- do an or between all the transformations of an attribute -- 
(
(s1.TX26 is not null and s2.TX26 like s1.TX26 || decode(sign(lengthb(s1.TX26)-3),1,'%',''))
)
)
------------ FILTER ATTRIBUTES SECTION ------------------------
and 
-- do an or between all the transformations of an attribute -- 
(
((s1.TX11 is null and s2.TX11 is null) or s2.TX11 = s1.TX11)
)
;

Line: 1214


FND_FILE.put_line(FND_FILE.log,'Ending insert of PARTY_SITES');

Line: 1215


FND_FILE.put_line(FND_FILE.log,'Number of records inserted '||SQL%ROWCOUNT);

Line: 1216


FND_FILE.put_line(FND_FILE.log,'End time to insert '||to_char(sysdate,'hh24:mi:ss'));