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_52 SQL Statements

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

Line: 91


  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.DUNS_NUMBER_C, 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: 123


        INSERT INTO HZ_SRCH_PARTIES (
          TX2, TX36, TX41, TX45, TX59, PARTY_OS, PARTY_OSR, PARTY_ID, BATCH_ID, INT_ROW_ID
        ) VALUES ( 
          H_TX2(I), H_TX36(I), H_TX41(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: 156


  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: 190


        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: 221


  select a.EMAIL_ADDRESS, a.URL, 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: 251


         INSERT INTO HZ_SRCH_CPTS  ( 
          TX5, TX7, 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_TX7(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: 279


      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 = 52
      union 
      select attribute_id 
      from hz_match_rule_secondary b 
      where match_rule_id = 52 ) and rownum = 1;

Line: 301


  select a.JOB_TITLE, a.obj_orig_system, a.obj_orig_system_reference, a.contact_orig_system, a.contact_orig_system_reference, b.party_action_flag, a.rowid, b.obj_id, c.person_first_name || '  ' || c.person_last_name as person_name
             from HZ_IMP_CONTACTS_INT a, HZ_IMP_CONTACTS_SG b, HZ_IMP_PARTIES_INT c 
         	where a.batch_id = p_batch_id 
         	and b.action_flag = 'I' 
             and b.int_row_id = a.rowid  
             and a.sub_orig_system_reference >= p_from_osr 
             and a.sub_orig_system_reference <= p_to_osr 
             and a.sub_orig_system = c.party_orig_system 
             and a.batch_id = c.batch_id 
             and b.sub_id = c.party_id 
            and b.batch_mode_flag = p_batch_mode_flag 
            and a.interface_status is null  
             union all 
  select a.JOB_TITLE, a.obj_orig_system, a.obj_orig_system_reference, a.contact_orig_system, a.contact_orig_system_reference, b.party_action_flag, a.rowid, b.obj_id,  c.party_name as person_name
             from HZ_IMP_CONTACTS_INT a, HZ_IMP_CONTACTS_SG b, hz_parties c  
         	where a.batch_id = p_batch_id 
         	and b.action_flag = 'I' 
             and b.int_row_id = a.rowid 
             and a.sub_orig_system_reference >= p_from_osr 
             and a.sub_orig_system_reference <= p_to_osr 
             and b.sub_id = c.party_id 
            and b.batch_mode_flag = p_batch_mode_flag 
            and a.interface_status is null  
        ;

Line: 327


  select a.JOB_TITLE, a.obj_orig_system, a.obj_orig_system_reference, a.contact_orig_system, a.contact_orig_system_reference, b.party_action_flag, a.rowid, b.obj_id 
             from HZ_IMP_CONTACTS_INT a, HZ_IMP_CONTACTS_SG b 
         	where a.batch_id = p_batch_id 
         	and b.action_flag = 'I' 
             and b.int_row_id = a.rowid  
             and a.sub_orig_system_reference  >= p_from_osr 
             and a.sub_orig_system_reference  <= p_to_osr    
            and b.batch_mode_flag = p_batch_mode_flag 
            and a.interface_status is null ;

Line: 370


         INSERT INTO HZ_SRCH_CONTACTS ( 
          TX22, PARTY_OS, PARTY_OSR, CONTACT_OS, CONTACT_OSR, NEW_PARTY_FLAG, BATCH_ID, INT_ROW_ID, PARTY_ID , TX2, TX23
         ) VALUES ( 
          H_TX22(I), H_P_SUBJECT_OS(I), H_P_SUBJECT_OSR(I), H_P_CONTACT_OS(I), H_P_CONTACT_OSR(I), H_P_N_PARTY(I), P_BATCH_ID, H_P_ROW_ID(I), H_CT_OBJ_ID(I) , H_CT_CUST_TX2(I), H_CT_CUST_TX23(I)
             );

Line: 404


  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.DUNS_NUMBER_C, 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: 433


        INSERT INTO HZ_SRCH_PARTIES ( 
          TX2, TX36, TX41, TX45, TX59, PARTY_OS, PARTY_OSR, BATCH_ID, INT_ROW_ID , PARTY_ID 
        ) VALUES ( 
          H_TX2(I), H_TX36(I), H_TX41(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: 466


  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: 496


         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: 528


  select a.EMAIL_ADDRESS, a.URL, 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: 556


         INSERT INTO HZ_SRCH_CPTS ( 
          TX5, TX7, 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_TX7(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: 584


      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 = 52
      union 
      select attribute_id 
      from hz_match_rule_secondary b 
      where match_rule_id = 52 ) and rownum = 1;

Line: 607


  select a.JOB_TITLE, a.obj_orig_system, a.obj_orig_system_reference, a.contact_orig_system, a.contact_orig_system_reference, a.rowid, c.person_first_name || '  ' || c.person_last_name as person_name
             from HZ_IMP_CONTACTS_INT a, HZ_IMP_PARTIES_INT c 
         	where a.batch_id = p_batch_id 
             and a.sub_orig_system_reference >= p_from_osr 
             and a.sub_orig_system_reference <= p_to_osr 
             and a.sub_orig_system_reference = c.party_orig_system_reference 
             and a.sub_orig_system = c.party_orig_system 
             and a.batch_id = c.batch_id 
             and a.sub_orig_system = l_os;

Line: 618


  select a.JOB_TITLE, a.obj_orig_system, a.obj_orig_system_reference, a.contact_orig_system, a.contact_orig_system_reference, a.rowid,  null person_name
             from HZ_IMP_CONTACTS_INT a 
         	where a.batch_id = p_batch_id 
             and a.sub_orig_system_reference >= p_from_osr 
             and a.sub_orig_system_reference <= p_to_osr   
             and a.sub_orig_system = l_os;

Line: 656


         INSERT INTO HZ_SRCH_CONTACTS ( 
          TX22, PARTY_OS, PARTY_OSR, CONTACT_OS, CONTACT_OSR, BATCH_ID, INT_ROW_ID , TX2, TX23
         ) VALUES ( 
          H_TX22(I), H_P_SUBJECT_OS(I), H_P_SUBJECT_OSR(I), H_P_CONTACT_OS(I), H_P_CONTACT_OSR(I), P_BATCH_ID, H_P_ROW_ID(I) , H_CT_CUST_TX2(I), H_CT_CUST_TX23(I)
             );

Line: 683


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: 686


    x_insert_threshold number := 20;

Line: 690


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

Line: 691


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.TX41,s1.TX41),1,200,
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,'%',''))
)
or
-- do an or between all the transformations of an attribute -- 
(
(s1.TX41 is not null and s2.TX41 like s1.TX41 || decode(sign(lengthb(s1.TX41)-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.TX7,s1.TX7),1,20,
0
)
 +  
decode(instrb(s2.TX10,s1.TX10),1,70,
decode(instrb(s2.TX158,s1.TX158),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,'%',''))
)
or
-- do an or between all the transformations of an attribute -- 
(
(s1.TX7 is not null and s2.TX7 like s1.TX7 || decode(sign(lengthb(s1.TX7)-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)
)
and
-- do an or between all the transformations of an attribute -- 
(
((p1.TX46 is null and p2.TX46 is null) or p2.TX46 = p1.TX46)
)
)
group by f, t 
having sum(score) >= x_insert_threshold
;

Line: 789


inserted_duplicates := (SQL%ROWCOUNT);

Line: 790


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

Line: 791


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

Line: 796


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

Line: 797


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

Line: 799


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,15,
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: 833


HZ_DQM_DUP_ID_PKG.update_hz_dup_results(x_ent_cur);

Line: 835


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

Line: 836


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

Line: 837


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

Line: 842


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

Line: 843


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

Line: 845


select f, t, max(score) score from (
 select /*+ ORDERED */ s1.party_id f, s2.party_id t,
decode(instrb(s2.TX2,s1.TX2),1,20,
decode(instrb(s2.TX23,s1.TX23),1,18,
0
)
)
+
decode(instrb(s2.TX22,s1.TX22),1,10,
0
)
score
from hz_dup_worker_chunk_gt p, hz_dup_results h1, HZ_STAGED_CONTACTS s1, HZ_STAGED_CONTACTS 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.TX23 is not null and s2.TX23 like s1.TX23 || decode(sign(lengthb(s1.TX23)-3),1,'%',''))
)
)
) group by f,t ;

Line: 867


HZ_DQM_DUP_ID_PKG.update_hz_dup_results(x_ent_cur);

Line: 869


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

Line: 870


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

Line: 871


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

Line: 880


             inserted_duplicates := -1;

Line: 900


x_insert_threshold number := 20;

Line: 904


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

Line: 905


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.TX41,s1.TX41),1,200,
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,'%',''))
)
or
-- do an or between all the transformations of an attribute -- 
(
(s1.TX41 is not null and s2.TX41 like s1.TX41 || decode(sign(lengthb(s1.TX41)-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.TX7,s1.TX7),1,20,
0
)
+
decode(instrb(s2.TX10,s1.TX10),1,70,
decode(instrb(s2.TX158,s1.TX158),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,'%',''))
)
or
-- do an or between all the transformations of an attribute -- 
(
(s1.TX7 is not null and s2.TX7 like s1.TX7 || decode(sign(lengthb(s1.TX7)-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: 1003


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

Line: 1004


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

Line: 1008


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

Line: 1009


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

Line: 1011


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,15,
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: 1046


HZ_DQM_DUP_ID_PKG.update_hz_imp_dup_parties(p_batch_id, x_ent_cur);

Line: 1048


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

Line: 1049


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

Line: 1050


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

Line: 1054


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

Line: 1055


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

Line: 1057


select f,t,max(score) from (
 select /*+ USE_CONCAT */ s1.party_id f, s2.party_id t,
decode(instrb(s2.TX2,s1.TX2),1,20,
decode(instrb(s2.TX23,s1.TX23),1,18,
0
)
)
+
decode(instrb(s2.TX22,s1.TX22),1,10,
0
)
score
from hz_imp_dup_parties h1, HZ_SRCH_CONTACTS s1, HZ_STAGED_CONTACTS 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.TX23 is not null and s2.TX23 like s1.TX23 || decode(sign(lengthb(s1.TX23)-3),1,'%',''))
)
)
) group by f,t ;

Line: 1080


HZ_DQM_DUP_ID_PKG.update_hz_imp_dup_parties(p_batch_id, x_ent_cur);

Line: 1082


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

Line: 1083


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

Line: 1084


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

Line: 1089


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

Line: 1090


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

Line: 1092


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: 1109


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

Line: 1110


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

Line: 1112


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: 1120


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: 1124


HZ_DQM_DUP_ID_PKG.update_party_dqm_action_flag(p_batch_id, x_ent_cur);

Line: 1130


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

Line: 1131


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

Line: 1132


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.TX7,s1.TX7),1,20,
0
)
+
decode(instrb(s2.TX10,s1.TX10),1,70,
decode(instrb(s2.TX158,s1.TX158),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,'%',''))
)
or
-- do an or between all the transformations of an attribute -- 
(
(s1.TX7 is not null and s2.TX7 like s1.TX7 || decode(sign(lengthb(s1.TX7)-3),1,'%',''))
)
)
;

Line: 1177


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: 1181


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

Line: 1183


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

Line: 1184


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

Line: 1185


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

Line: 1191


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

Line: 1192


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

Line: 1193


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,15,
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: 1236


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: 1240


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

Line: 1242


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

Line: 1243


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

Line: 1244


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

Line: 1250


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

Line: 1251


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

Line: 1252


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.TX2,s1.TX2),1,20,
decode(instrb(s2.TX23,s1.TX23),1,18,
0
)
)
+
decode(instrb(s2.TX22,s1.TX22),1,10,
0
)
score , s1.party_osr, s1.party_os, p_batch_id,'CONTACTS', s1.ORG_CONTACT_ID, s1.CONTACT_OSR, s1.CONTACT_OS,
                                                                      s2.ORG_CONTACT_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_CONTACTS s1, HZ_STAGED_CONTACTS 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.TX23 is not null and s2.TX23 like s1.TX23 || decode(sign(lengthb(s1.TX23)-3),1,'%',''))
)
)
;

Line: 1283


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 ='CONTACTS';

Line: 1287


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

Line: 1289


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

Line: 1290


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

Line: 1291


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

Line: 1315


    x_insert_threshold number := 20;

Line: 1319


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

Line: 1320


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.TX41,s1.TX41),1,200,
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,'%',''))
)
or
-- do an or between all the transformations of an attribute -- 
(
(s1.TX41 is not null and s2.TX41 like s1.TX41 || decode(sign(lengthb(s1.TX41)-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.TX7,s1.TX7),1,20,
0
)
+
decode(instrb(s2.TX10,s1.TX10),1,70,
decode(instrb(s2.TX158,s1.TX158),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,'%',''))
)
or
-- do an or between all the transformations of an attribute -- 
(
(s1.TX7 is not null and s2.TX7 like s1.TX7 || decode(sign(lengthb(s1.TX7)-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: 1415


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

Line: 1416


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

Line: 1421


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

Line: 1422


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

Line: 1424


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,15,
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: 1460


HZ_DQM_DUP_ID_PKG.update_hz_int_dup_results(p_batch_id,x_ent_cur);

Line: 1462


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

Line: 1463


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

Line: 1464


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

Line: 1469


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

Line: 1470


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

Line: 1472


select f,t,max(score) from (
 select /*+ USE_CONCAT */ s1.party_osr f, s2.party_osr t,
decode(instrb(s2.TX2,s1.TX2),1,20,
decode(instrb(s2.TX23,s1.TX23),1,18,
0
)
)
+
decode(instrb(s2.TX22,s1.TX22),1,10,
0
)
score
from hz_int_dup_results h1, HZ_SRCH_CONTACTS s1, HZ_SRCH_CONTACTS 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.TX23 is not null and s2.TX23 like s1.TX23 || decode(sign(lengthb(s1.TX23)-3),1,'%',''))
)
)
) group by f,t ;

Line: 1496


HZ_DQM_DUP_ID_PKG.update_hz_int_dup_results(p_batch_id,x_ent_cur);

Line: 1498


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

Line: 1499


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

Line: 1500


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

Line: 1504


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

Line: 1505


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

Line: 1506


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.TX7,'N1'),nvl(substrb(s2.TX7,1,length(s1.TX7)),'N2'),20, 
0
)
+
decode(nvl(s1.TX10,'N1'),nvl(substrb(s2.TX10,1,length(s1.TX10)),'N2'),70, 
decode(nvl(s1.TX158,'N1'),nvl(substrb(s2.TX158,1,length(s1.TX158)),'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,'%',''))
)
or
-- do an or between all the transformations of an attribute -- 
(
(s1.TX7 is not null and s2.TX7 like s1.TX7 || decode(sign(lengthb(s1.TX7)-3),1,'%',''))
)
)
;

Line: 1552


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

Line: 1553


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

Line: 1554


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

Line: 1558


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

Line: 1559


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

Line: 1560


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'),15, 
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: 1603


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

Line: 1604


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

Line: 1605


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

Line: 1609


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

Line: 1610


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

Line: 1611


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_OSR, s1.CONTACT_OS,
s2.CONTACT_OSR, s2.CONTACT_OS,
s1.party_osr, s2.party_os,'CONTACTS',
decode(nvl(s1.TX2,'N1'),nvl(substrb(s2.TX2,1,length(s1.TX2)),'N2'),20, 
decode(nvl(s1.TX23,'N1'),nvl(substrb(s2.TX23,1,length(s1.TX23)),'N2'),18, 
0
)
)
+
decode(nvl(s1.TX22,'N1'),nvl(substrb(s2.TX22,1,length(s1.TX22)),'N2'),10, 
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_CONTACTS s1, HZ_SRCH_CONTACTS 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_OSR < s2.CONTACT_OSR
and ( 
------------ NON FILTER ATTRIBUTES SECTION ------------------------
-- do an or between all the transformations of an attribute -- 
(
(s1.TX23 is not null and s2.TX23 like s1.TX23 || decode(sign(lengthb(s1.TX23)-3),1,'%',''))
)
)
;

Line: 1642


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

Line: 1643


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

Line: 1644


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