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

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

Line: 19


 |      7-JUL-2005  Ramesh Ch  Bug No: 4244529.Modified insert stmts to denormalize dup_batch_id
 |                              into hz_dup_set_parties.
 |     18-OCT-2005 Ravi Epuri : Bug No: 4669400. Modified the 2 instances of OPEN pt_cur CURSOR, in tca_dup_id_worker
 |                              Procedure, to make sure it does not consider Merged and Inactive Parties for duplicate
 |                               identification, by adding the filter condition 'Status = A', in the where clause.
 |      12-JUL-2006 : Raj  Bug 5393826: Made changed to procedure report_int_dup_party_osrs.
 |                                      Deleting the records from hz_imp_int_dedup_results for which
 |                                      no records exists in import party interface table.
 |      18-JUL-2006 : Raj Bug 5393863: Made changes to tca_dup_id_worker procedure.
 |                                     Instead of opening a cursor on hz_parties,inserted all the parties in to
 |                                     HZ_MATCHED_PARTIES_GT table and then opened a cursor on HZ_MATCHED_PARTIES_GT table.
 *=======================================================================*/
--check
 -- Definitions:
-- A pair of parties (a,b) denotes two party ids a, b such that
-- b is a duplicate of a, subject to a given match rule.
-- a will be called the source party and b will be called the duplicate party.
-- Given 2 pairs of duplicate parties (a, b) and (c,d), we define the following terms:
-- Identical pairs: a = c and b =d  ex: (1,2) and (1,2)
-- Reversed pairs: a = d and b = c ex: (1,2) and (2,1)
-- Transitive pairs: b = c  ex: (1,2) and (2,3)
-- Indirect Transitives: (a,b) (c,b)
-- Direct Transitives: (a,b) (b,c)
-- Pre Union Phase : The phase before or during the query that does the union of entities.
-- Post Union Phase : The phase after we do the union of entities.
-- Trivial Dup Set: If a party "a" is found as a duplicate of a party "b" adn there are no other duplicates
--                  for either a or b, then the dup set { a, b} will be called a trivial dup set.
--                  These will be of cardinality 2 and they don't need any transitive derivations.
-- Non Trivial Dup Set: These are the ones that have cardinality > 3. For example, let us say, we find the following:
                                                -- 1 duplicate of 2
                                                -- 2 duplicate of 3
                                                -- 2 duplicate of 6
                                                -- 6 duplicate of 5
                                                -- 6 duplicate of 7
                                                -- 6 duplicate of 8
                                                -- 8 duplicate of 12
                                                -- 8 duplicate of 13
                                                -- 13 duplicate of 1
                                                -- 7 duplicate of 0
 --                       The dup set would be  { 1,2,3,5,6,7,8,12,13,0 } after transitive derivations.



------------------------------------------------------------------------------------
------------------------------------------------------------------------------------
-- TCA DUPLICATE IDENTIFICATION
------------------------------------------------------------------------------------
------------------------------------------------------------------------------------


/**
 * PROCEDURE update_hz_dup_results
 *
 * DESCRIPTION
 *
 *
 *
 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
 *
 *
 * ARGUMENTS
 *
 *
 * NOTES
 *
 * MODIFICATION HISTORY
 *
 *   04-09-2003  Colathur Vijayan       o Created.
 *
 */

--------------------------------------------------------------------------------------
-- update_hz_dup_results ::: This is a generic procedure, that would do a bulk update
--                           of hz_dup_results, using a passed in open cursor
--------------------------------------------------------------------------------------


PROCEDURE update_hz_dup_results (
    p_cur    IN EntityCur )
is
    l_limit NUMBER := 200;

Line: 126


                UPDATE HZ_DUP_RESULTS A
                SET A.SCORE = A.SCORE + H_SCORE(I)
                WHERE
                   ( A.FID = H_FID(I) and
                     A.TID = H_TID(I)
                     );

Line: 143


         FND_MESSAGE.SET_TOKEN('PROC','UPDATE_HZ_DUP_RESULTS');

Line: 161


        select count(1) into p_count
        from hz_dup_results;

Line: 166


        FND_FILE.put_line(FND_FILE.log,'Parties ::: Delete based on subset sql');

Line: 167


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

Line: 184


           EXECUTE IMMEDIATE 'delete from hz_dup_results a where ' ||
           'not exists ' ||
           '(Select 1 from hz_dup_results b, hz_parties parties ' ||
           'where b.ord_tid = parties.party_id ' ||
           'and ' ||
           p_subset_sql || ')' ;

Line: 193


   FND_FILE.put_line(FND_FILE.log,'Parties ::: Number of parties deleted from HZ_DUP_RESULTS '||SQL%ROWCOUNT);

Line: 194


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

Line: 197


   FND_FILE.put_line(FND_FILE.log,'Parties ::: Beginning delete on HZ_DUP_RESULTS, based on threshold, reversed pairs and indirect transitives '||SQL%ROWCOUNT);

Line: 207


          delete from hz_dup_results a
          where
          -- delete anything less than the threshold
          a.score < p_threshold
          or
          -- if scores are same, delete the one with highest source
          -- or if scores are different, delete the one with lower score
          (exists
            (Select 1 from hz_dup_results b
             where

             (
               (
                -- APPLY THE ABOVE PRINCIPLE TO REVERSED PAIRS
                 a.fid=b.tid and b.fid=a.tid and
                 ( (a.score = b.score and a.fid > b.fid) or (a.score < b.score) )
                )

                or
                -- APPLY THE ABOVE PRINCIPLE TO INDIRECT TRANSITIVES
                ( a.ord_tid=b.ord_tid and ((a.score = b.score and a.ord_fid > b.ord_fid) or (a.score < b.score)) )

             )
          ));

Line: 233


   FND_FILE.put_line(FND_FILE.log,'Parties ::: Number of parties deleted from HZ_DUP_RESULTS '||SQL%ROWCOUNT);

Line: 234


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

Line: 251


PROCEDURE update_hz_int_dup_results (
    p_batch_id IN number,
    p_cur    IN EntityCur )
is
    l_limit NUMBER := 200;

Line: 282


                UPDATE HZ_INT_DUP_RESULTS A
                SET A.SCORE = A.SCORE + H_SCORE(I)
                WHERE
                   ( A.F_OSR = H_F_OSR(I) and
                     A.T_OSR = H_T_OSR(I) and
                     A.BATCH_ID = p_batch_id
                     );

Line: 301


         FND_MESSAGE.SET_TOKEN('PROC','UPDATE_HZ_INT_DUP_RESULTS');

Line: 330


     /* -- WE WILL BE REPLACING THIS BY AN INSERT TO A TEMP TABLE
        --   SINCE DELETE IS PERFORMANCE PROHIBITIVE.
        delete from hz_int_dup_results a
        where
          -- delete anything less than the threshold
          (a.score < p_threshold and a.batch_id = p_batch_id);

Line: 337


        delete from hz_int_dup_results a
        where
        a.batch_id = p_batch_id
        and
          -- if scores are same, delete the one with highest source
          -- or if scores are different, delete the one with lower score
          (exists
            (Select 1 from hz_int_dup_results b
             where
               (
                -- APPLY THE ABOVE PRINCIPLE TO REVERSED PAIRS
                 a.f_osr=b.t_osr and b.f_osr=a.t_osr and b.batch_id = p_batch_id and
                 ( (a.score = b.score and a.f_osr > b.f_osr) or (a.score < b.score) )
                )

                or
                -- APPLY THE ABOVE PRINCIPLE TO INDIRECT TRANSITIVES
                ( a.t_osr=b.t_osr and b.batch_id = p_batch_id and
                ((a.score = b.score and a.f_osr > b.f_osr) or (a.score < b.score))
                )
             )
            ) ;

Line: 374


select count(1) into p_count
from hz_int_dup_results
where batch_id = p_batch_id;

Line: 380


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

Line: 384


insert into hz_int_dup_results_gt (batch_id, f_osr, f_os, t_osr, t_os, ord_f_osr, ord_t_osr, score)
select a.batch_id, a.f_osr, a.f_os, a.t_osr, a.t_os, a.ord_f_osr, a.ord_t_osr, a.score
from hz_int_dup_results a
where
(a.score >= p_threshold and a.batch_id = p_batch_id)
and
not exists
   (select 1 from hz_int_dup_results b
         where
	 b.batch_id = p_batch_id
         and
         (
		 (
			  a.f_osr=b.t_osr and b.f_osr=a.t_osr and
			  ( (a.score = b.score and b.f_osr < a.f_osr) or (a.score < b.score) )
		 )

	  or

		(
			  a.t_osr=b.t_osr and
			  ((a.score = b.score and b.f_osr < a.f_osr ) or (a.score < b.score))
		)
          )
    );

Line: 411


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

Line: 412


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

Line: 433


         update hz_imp_batch_summary h
         set dup_parties_in_batch =
         (select count(1) from
            (select distinct winner_record_osr
            from hz_imp_int_dedup_results
            where batch_id =  p_batch_id
            and entity = 'PARTY'
            union
            select distinct dup_record_osr
            from hz_imp_int_dedup_results
            where batch_id =  p_batch_id
            and entity = 'PARTY'
            )
          )
          where
          h.batch_id = p_batch_id ;

Line: 451


         update hz_imp_batch_summary h
         set party_dup_sets_in_batch =
         (select count(1) from
            (select distinct winner_record_osr
            from hz_imp_int_dedup_results
            where batch_id =  p_batch_id
            and entity = 'PARTY'
            )
          )
          where
          h.batch_id = p_batch_id ;

Line: 464


         update hz_imp_batch_summary h
         set dup_addresses_in_batch =
         (select count(1) from
            (select distinct winner_record_osr
            from hz_imp_int_dedup_results
            where batch_id =  p_batch_id
            and entity = 'PARTY_SITES'
            union
            select distinct dup_record_osr
            from hz_imp_int_dedup_results
            where batch_id =  p_batch_id
            and entity = 'PARTY_SITES'
            )
          )
          where
          h.batch_id = p_batch_id ;

Line: 482


         update hz_imp_batch_summary h
         set address_dup_sets_in_batch =
         (select count(1) from
            (select distinct winner_record_osr
            from hz_imp_int_dedup_results
            where batch_id =  p_batch_id
            and entity = 'PARTY_SITES'
            )
          )
          where
          h.batch_id = p_batch_id ;

Line: 495


         update hz_imp_batch_summary h
         set dup_contacts_in_batch =
         (select count(1) from
            (select distinct winner_record_osr
            from hz_imp_int_dedup_results
            where batch_id =  p_batch_id
            and entity = 'CONTACTS'
            union
            select distinct dup_record_osr
            from hz_imp_int_dedup_results
            where batch_id =  p_batch_id
            and entity = 'CONTACTS'
            )
          )
          where
          h.batch_id = p_batch_id ;

Line: 513


         update hz_imp_batch_summary h
         set contact_dup_sets_in_batch =
         (select count(1) from
            (select distinct winner_record_osr
            from hz_imp_int_dedup_results
            where batch_id =  p_batch_id
            and entity = 'CONTACTS'
            )
          )
          where
          h.batch_id = p_batch_id ;

Line: 527


         update hz_imp_batch_summary h
         set dup_contactpoints_in_batch =
         (select count(1) from
            (select distinct winner_record_osr
            from hz_imp_int_dedup_results
            where batch_id =  p_batch_id
            and entity = 'CONTACT_POINTS'
            union
            select distinct dup_record_osr
            from hz_imp_int_dedup_results
            where batch_id =  p_batch_id
            and entity = 'CONTACT_POINTS'
            )
          )
          where
          h.batch_id = p_batch_id ;

Line: 545


         update hz_imp_batch_summary h
         set contactpoint_dup_sets_in_batch =
         (select count(1) from
            (select distinct winner_record_osr
            from hz_imp_int_dedup_results
            where batch_id =  p_batch_id
            and entity = 'CONTACT_POINTS'
            )
          )
          where
          h.batch_id = p_batch_id ;

Line: 570


        select count(1) into p_count
        from hz_imp_int_dedup_results
        where batch_id = p_batch_id
        and entity <> 'PARTY' ;

Line: 577


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

Line: 580


         delete from hz_imp_int_dedup_results a
         where
         (exists
            (Select 1 from hz_imp_int_dedup_results b
             where
               (
                 -- DELETE DIRECT TRANSITIVE DETAIL OSRS FOR THIS BATCH
                 -- WE BASICALLY MAKE SURE THAT A DETAIL RECORD OSR
                 -- CANNOT BE A WINNER RECORD OSR, FOR A GIVEN DETAIL PARTY OSR

                 a.batch_id = p_batch_id and
                 a.entity <> 'PARTY' and
                 a.batch_id = b.batch_id and
                 a.entity = b.entity AND      -- bug 5393826
                 a.winner_record_osr=b.dup_record_osr -- bug 5393826
                )
             )
          );

Line: 600


          FND_FILE.put_line(FND_FILE.log,'Details ::: Number of details deleted in HZ_IMP_INT_DEDUP_RESULTS '||SQL%ROWCOUNT);

Line: 601


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

Line: 604


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

Line: 605


         insert into hz_imp_int_dedup_results
        (batch_id,
         winner_record_osr,
         winner_record_os,
         dup_record_osr,
         dup_record_os,
         detail_party_osr,
         entity,
         score
         )
        select   distinct p_batch_id,
         winner_record_osr,
         winner_record_os,
         winner_record_osr,
         winner_record_os,
         detail_party_osr,
         entity,
         0
       from hz_imp_int_dedup_results a
       where a.entity <> 'PARTY'
       and a.batch_id = p_batch_id ;

Line: 627


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

Line: 628


        FND_FILE.put_line(FND_FILE.log,'Details ::: Number of Winner Detail OSRs inserted '||SQL%ROWCOUNT);

Line: 636


         FND_FILE.put_line(FND_FILE.log,'Details ::: End time of update for getting import table dates '||to_char(sysdate,'hh24:mi:ss'));

Line: 639


         update hz_imp_int_dedup_results a
         set (a.dup_creation_date, a.dup_last_update_date)
              = (select b.creation_date, b.last_update_date
                 from hz_imp_addresses_int b
                 where b.batch_id = p_batch_id
                 and b.site_orig_system_reference = a.dup_record_osr
                 and b.site_orig_system = a.dup_record_os
                  )
          where a.entity = 'PARTY_SITES' and a.batch_id = p_batch_id ;

Line: 650


         update hz_imp_int_dedup_results a
         set (a.dup_creation_date, a.dup_last_update_date)
              = (select b.creation_date, b.last_update_date
                 from hz_imp_contacts_int b
                 where b.batch_id = p_batch_id
                 and b.contact_orig_system_reference = a.dup_record_osr
                 and b.contact_orig_system = a.dup_record_os
                  )
          where a.entity = 'CONTACTS' and a.batch_id = p_batch_id ;

Line: 661


          update hz_imp_int_dedup_results a
          set (a.dup_creation_date, a.dup_last_update_date)
              = (select b.creation_date, b.last_update_date
                 from hz_imp_contactpts_int b
                 where b.batch_id = p_batch_id
                 and b.cp_orig_system_reference = a.dup_record_osr
                 and b.cp_orig_system = a.dup_record_os
                  )
          where a.entity = 'CONTACT_POINTS' and a.batch_id = p_batch_id ;

Line: 671


        FND_FILE.put_line(FND_FILE.log,'Details ::: Number of duplicate details updated in HZ_IMP_INT_DEDUP_RESULTS '||SQL%ROWCOUNT);

Line: 672


        FND_FILE.put_line(FND_FILE.log,'Details ::: End time of update for getting import table dates '||to_char(sysdate,'hh24:mi:ss'));

Line: 710


        select count(1) into p_count
        from hz_dup_results ;

Line: 715


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

Line: 718


insert into hz_dup_sets ( winner_party_id, dup_set_id, dup_batch_id,
                          status, merge_type, created_by, creation_date, last_update_login,
                          last_update_date, last_updated_by)
select win_party_id, HZ_MERGE_BATCH_S.nextval, p_batch_id,
       'SYSBATCH', 'PARTY_MERGE', hz_utility_pub.created_by, hz_utility_pub.creation_date,
       hz_utility_pub.last_update_login, hz_utility_pub.last_update_date,
       hz_utility_pub.user_id
from
(
select distinct d.ord_fid as win_party_id, level as levelu
from hz_dup_results d
start with d.ord_fid not in
   (
        select c.ord_tid
        from hz_dup_results c
   )
connect by prior ord_tid = ord_fid
)
where mod(levelu, 2) = 1 ;

Line: 739


   FND_FILE.put_line(FND_FILE.log,'Parties ::: Number of parties inserted to Dup Sets '||SQL%ROWCOUNT);

Line: 740


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

Line: 746


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

Line: 750


insert into hz_dup_set_parties (dup_party_id, dup_set_id,merge_seq_id,
             merge_batch_id,score,merge_flag, created_by,creation_date,last_update_login,
             last_update_date,last_updated_by,dup_set_batch_id) --Bug No: 4244529
select  a.winner_party_id, a.dup_set_id, 0, 0, 100 ,'Y',
                    hz_utility_pub.created_by,hz_utility_pub.creation_date,
                    hz_utility_pub.last_update_login,hz_utility_pub.last_update_date,
                    hz_utility_pub.user_id,a.dup_batch_id --Bug No: 4244529
from hz_dup_sets a
where a.dup_batch_id = p_batch_id
union all
-- this is the part for all the duplicates of the winner
-- basically compare the winner from dup set to any row which has the winner has its ord_fid
-- and pick up its ord_tid.
select  b.ord_tid, a.dup_set_id, 0, 0, b.score ,decode( sign(b.score - p_auto_merge_threshold),-1,'N','Y'),
                    hz_utility_pub.created_by,hz_utility_pub.creation_date,
                    hz_utility_pub.last_update_login,hz_utility_pub.last_update_date,
                    hz_utility_pub.user_id,a.dup_batch_id
from hz_dup_sets a, hz_dup_results b
where a.dup_batch_id = p_batch_id
and a.winner_party_id = b.ord_fid ;

Line: 771


   FND_FILE.put_line(FND_FILE.log,'Parties ::: Number of parties inserted to Dup Set Parties'||SQL%ROWCOUNT);

Line: 772


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

Line: 798


  open pt_cur for 'select party_id from hz_parties where rownum<3';

Line: 822


x_inserted_duplicates number := 0;

Line: 859


         FND_FILE.put_line(FND_FILE.log,'Start Time before insert to hz_dqm_stage_gt ' || TO_CHAR(SYSDATE, 'MM-DD-YY HH24:MI:SS'));

Line: 863


             'SELECT parties.PARTY_ID FROM HZ_PARTIES parties WHERE parties.PARTY_TYPE <> ''PARTY_RELATIONSHIP'' AND NVL (parties.STATUS,''A'') = ''A'' AND mod(parties.PARTY_ID, :num_workers) = :worker_number' */
             execute immediate
                'insert /*+ APPEND */  into HZ_MATCHED_PARTIES_GT(party_id)
                SELECT  /*+ INDEX(parties HZ_PARTIES_U1) */ parties.PARTY_ID FROM HZ_PARTIES parties WHERE parties.PARTY_TYPE <> ''PARTY_RELATIONSHIP''
                AND NVL(parties.STATUS,''A'') = ''A'' AND mod(parties.PARTY_ID, :num_workers) = :worker_number '
                       USING p_number_of_workers, p_worker_number;

Line: 869


               FND_FILE.put_line(FND_FILE.log,'Number of parties inserted into HZ_MATCHED_PARTIES_GT by worker '||p_worker_number||' is '||SQL%ROWCOUNT );       -- BUG 5351721

Line: 872


            'SELECT PARTY_ID FROM HZ_PARTIES parties WHERE parties.PARTY_TYPE <> ''PARTY_RELATIONSHIP'' AND NVL (parties.STATUS,''A'') = ''A'' AND mod(parties.PARTY_ID, :num_workers) = :worker_number AND '||
                p_subset_sql  */
               execute immediate
                'insert /*+ APPEND */  into HZ_MATCHED_PARTIES_GT(party_id)
                SELECT /*+ INDEX(parties HZ_PARTIES_U1) */ PARTY_ID FROM HZ_PARTIES parties WHERE parties.PARTY_TYPE <> ''PARTY_RELATIONSHIP'' AND NVL(parties.STATUS,''A'') = ''A''
                AND mod(parties.PARTY_ID, :num_workers) = :worker_number AND '||
                p_subset_sql   USING p_number_of_workers, p_worker_number;

Line: 879


                FND_FILE.put_line(FND_FILE.log,'Number of parties inserted into HZ_MATCHED_PARTIES_GT by worker '||p_worker_number||' is '||SQL%ROWCOUNT );      -- BUG 5351721

Line: 881


         FND_FILE.put_line(FND_FILE.log,'End Time after insert to HZ_MATCHED_PARTIES_GT ' || TO_CHAR(SYSDATE, 'MM-DD-YY HH24:MI:SS'));

Line: 885


         OPEN pt_cur FOR 'select party_id from HZ_MATCHED_PARTIES_GT';       -- BUG 5351721

Line: 904


                               /* insert into hz_dup_results(fid, tid, ord_fid, ord_tid, score,chunk_num,chunk_stime)
                               values (-1,-1,-1,-1,chunk_limit,cnt,sysdate);

Line: 913


                                 INSERT INTO hz_dup_worker_chunk_gt values (pid_list(I));

Line: 917


                               DELETE FROM hz_dup_worker_chunk_gt WHERE
                                EXISTS (Select 1 from HZ_DUP_RESULTS t
                                        WHERE t.tid = party_id);

Line: 937


                            pid_list.DELETE;

Line: 952


                         insert into hz_dup_results(fid, tid, ord_fid, ord_tid, score,chunk_num,chunk_stime)
                         values (-1,-1,-1,-1,chunk_limit,cnt,sysdate);

Line: 961


                            INSERT INTO hz_dup_worker_chunk_gt values (pid_list(I));

Line: 971


                         DELETE FROM hz_dup_worker_chunk_gt WHERE
                          EXISTS (Select 1 from HZ_DUP_RESULTS t
                                  WHERE t.tid = party_id);

Line: 987


           anon_str := 'begin ' ||l_pkg_name ||'.tca_join_entities(:x_trap_explosion,:x_rows_in_chunk,:x_inserted_duplicates); end;' ;

Line: 991


           EXECUTE IMMEDIATE anon_str USING IN x_trap_explosion, IN x_rows_in_chunk, OUT x_inserted_duplicates  ;

Line: 1001


           IF x_inserted_duplicates = -1
           THEN
             -- dbms_output.put_line('in chunk explosion ');

Line: 1017


           ELSIF (x_inserted_duplicates < 50 )
           THEN
                chunk_limit:=chunk_limit*2;

Line: 1064


        select match_score, auto_merge_score into x_threshold, x_auto_merge_threshold
        from hz_match_rules_vl
        where match_rule_id = p_match_rule_id;

Line: 1098


 * PROCEDURE update_hz_imp_dup_parties
 *
 * DESCRIPTION
 *
 *
 *
 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
 *
 *
 * ARGUMENTS
 *
 *
 * NOTES
 *
 * MODIFICATION HISTORY
 *
 *   04-09-2003  Colathur Vijayan       o Created.
 *
 */

--------------------------------------------------------------------------------------
-- update_hz_imp_dup_parties ::: This is a generic procedure, that would do a bulk update
--                           of hz_dup_results, using a passed in open cursor
--------------------------------------------------------------------------------------


PROCEDURE update_hz_imp_dup_parties (
    p_batch_id IN number,
    p_cur    IN EntityCur )
is
    l_limit NUMBER := 200;

Line: 1155


                UPDATE HZ_IMP_DUP_PARTIES A
                SET A.SCORE = A.SCORE + H_SCORE(I)
                WHERE
                   ( A.PARTY_ID = H_PARTY_ID(I) and
                     A.DUP_PARTY_ID = H_DUP_PARTY_ID(I) and
                     A.BATCH_ID = p_batch_id
                     );

Line: 1173


         FND_MESSAGE.SET_TOKEN('PROC','UPDATE_HZ_IMP_DUP_PARTIES');

Line: 1193


   select batch_name into p_batch_name
   from hz_imp_batch_summary
   where batch_id = p_batch_id;

Line: 1202


   HZ_DUP_BATCH_PKG.Insert_Row(
      px_dup_batch_id     => l_dup_batch_id
     ,p_dup_batch_name    => p_batch_name
     ,p_match_rule_id     => p_match_rule_id
     ,p_application_id    => '222'
     ,p_request_type      => 'IMPORT'
     ,p_created_by        => HZ_UTILITY_V2PUB.CREATED_BY
     ,p_creation_date     => HZ_UTILITY_V2PUB.CREATION_DATE
     ,p_last_update_login => HZ_UTILITY_V2PUB.LAST_UPDATE_LOGIN
     ,p_last_update_date  => HZ_UTILITY_V2PUB.LAST_UPDATE_DATE
     ,p_last_updated_by   => HZ_UTILITY_V2PUB.LAST_UPDATED_BY
   );

Line: 1218


    update hz_dup_batch
    set automerge_flag = (select automerge_flag
                          from hz_match_rules_vl
                          where match_rule_id = p_match_rule_id)
    where dup_batch_id = l_dup_batch_id ;

Line: 1234


    select count(1) into p_count
    from hz_imp_dup_parties
    where batch_id = p_batch_id;

Line: 1240


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

Line: 1242


    insert into hz_dup_sets ( winner_party_id, dup_set_id, dup_batch_id,
                          status, merge_type, created_by, creation_date, last_update_login,
                          last_update_date, last_updated_by)
    select win_party_id, HZ_MERGE_BATCH_S.nextval, l_dup_batch_id,
       'SYSBATCH', 'PARTY_MERGE', hz_utility_pub.created_by, hz_utility_pub.creation_date,
       hz_utility_pub.last_update_login, hz_utility_pub.last_update_date,
       hz_utility_pub.user_id
    from
        (select distinct h.dup_party_id as win_party_id
         from hz_imp_dup_parties h
         where h.batch_id = p_batch_id
         and exists (select a.party_id
                     from hz_imp_dup_parties a, hz_parties b
                     where a.batch_id = p_batch_id
                     and a.dup_party_id = h.dup_party_id
                     and a.auto_merge_flag <> 'R'
                     and a.party_id = b.party_id
                     and b.request_id = p_request_id
                       )
         ) ;

Line: 1267


   FND_FILE.put_line(FND_FILE.log,'Parties ::: Number of parties inserted to Dup Sets '|| p_count );

Line: 1268


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

Line: 1272


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

Line: 1274


    insert into hz_dup_set_parties (dup_party_id, dup_set_id,merge_seq_id,
                 merge_batch_id,score,merge_flag, created_by,creation_date,last_update_login,
                 last_update_date,last_updated_by,dup_set_batch_id) --Bug No: 4244529
    select  a.winner_party_id, a.dup_set_id, 0, 0, 100 ,'Y',
                        hz_utility_pub.created_by,hz_utility_pub.creation_date,
                        hz_utility_pub.last_update_login,hz_utility_pub.last_update_date,
                        hz_utility_pub.user_id,a.dup_batch_id --Bug No: 4244529
    from hz_dup_sets a
    where a.dup_batch_id = l_dup_batch_id
    union all
    -- this is the part for all the duplicates of the winner
    -- basically compare the winner from dup set to any row which has the winner
    -- as its dup_party_id and pick up the corresponding interface party id,
    -- provided the following conditions are met:
    -- 1. The interface party id has been loaded
    -- 2. The interface party id does not have an automerge flag of 'R'
    select  b.party_id, a.dup_set_id, 0, p_batch_id, b.score , b.auto_merge_flag,
                        hz_utility_pub.created_by,hz_utility_pub.creation_date,
                        hz_utility_pub.last_update_login,hz_utility_pub.last_update_date,
                        hz_utility_pub.user_id,a.dup_batch_id --Bug No: 4244529
    from hz_dup_sets a, hz_imp_dup_parties b, hz_parties c
    where a.dup_batch_id = l_dup_batch_id
    and a.winner_party_id = b.dup_party_id
    and b.party_id = c.party_id
    and c.request_id = p_request_id
    and b.auto_merge_flag <> 'R' ;

Line: 1304


   FND_FILE.put_line(FND_FILE.log,'Parties ::: Number of parties inserted to Dup Set Parties '|| l_party_count);

Line: 1305


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

Line: 1312


   update hz_imp_batch_summary h
   set party_merge_requests = l_party_count
   where h.batch_id = p_batch_id ;

Line: 1316


   FND_FILE.put_line(FND_FILE.log,'Parties ::: Number of merge request parties inserted into batch summary table is '
                                                   || l_party_count);

Line: 1350


    select count(1) into p_count
    from hz_imp_dup_details
    where batch_id = p_batch_id;

Line: 1356


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

Line: 1358


    insert into hz_dup_sets ( winner_party_id, dup_set_id, dup_batch_id,
                          status, merge_type, created_by, creation_date, last_update_login,
                          last_update_date, last_updated_by)
    select win_party_id, HZ_MERGE_BATCH_S.nextval, l_dup_batch_id,
       'SYSBATCH', 'SAME_PARTY_MERGE', hz_utility_pub.created_by, hz_utility_pub.creation_date,
       hz_utility_pub.last_update_login, hz_utility_pub.last_update_date,
       hz_utility_pub.user_id
    from
        (select distinct h.party_id as win_party_id
         from hz_imp_dup_details h
         where h.batch_id = p_batch_id
         and
         (
            exists (select a.dup_record_id
                     from hz_imp_dup_details a, hz_party_sites b
                     where a.party_id = h.party_id
                     and a.batch_id = p_batch_id
                     and a.record_id = b.party_site_id
                     and b.request_id = p_request_id
                       )
             or
             exists (select a.dup_record_id
             from hz_imp_dup_details a, hz_contact_points b
             where a.party_id = h.party_id
             and a.batch_id = p_batch_id
             and a.record_id = b.contact_point_id
             and b.request_id = p_request_id
               )
             or
             exists (select a.dup_record_id
             from hz_imp_dup_details a, hz_org_contacts b
             where a.party_id = h.party_id
             and a.batch_id = p_batch_id
             and a.record_id = b.org_contact_id
             and b.request_id = p_request_id
               )
          )
        ) ;

Line: 1398


    FND_FILE.put_line(FND_FILE.log,'Details ::: Number of parties inserted to Dup Sets '||SQL%ROWCOUNT);

Line: 1399


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

Line: 1403


    insert into hz_dup_set_parties (dup_party_id, dup_set_id,merge_seq_id,
                 merge_batch_id,score,merge_flag, created_by,creation_date,last_update_login,
                 last_update_date,last_updated_by,dup_set_batch_id) --Bug No: 4244529
    select  a.winner_party_id, a.dup_set_id, 0, 0, 100 ,'Y',
                        hz_utility_pub.created_by,hz_utility_pub.creation_date,
                        hz_utility_pub.last_update_login,hz_utility_pub.last_update_date,
                        hz_utility_pub.user_id,a.dup_batch_id --Bug No: 4244529
    from hz_dup_sets a
    where a.dup_batch_id = l_dup_batch_id
    and a.merge_type = 'SAME_PARTY_MERGE';

Line: 1415


    FND_FILE.put_line(FND_FILE.log,'Details ::: Number of parties inserted to Dup Set Parties '||SQL%ROWCOUNT);

Line: 1416


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

Line: 1423


    select count(distinct a.dup_record_id) into ps_count
                                     from hz_imp_dup_details a, hz_party_sites b
                                     where a.batch_id = p_batch_id
                                     and a.record_id = b.party_site_id
                                     and b.request_id = p_request_id ;

Line: 1428


    update hz_imp_batch_summary h
    set address_merge_requests = ps_count
    where h.batch_id = p_batch_id ;

Line: 1434


    select count(distinct a.dup_record_id) into cp_count
                                         from hz_imp_dup_details a, hz_contact_points b
                                         where a.batch_id = p_batch_id
                                         and a.record_id = b.contact_point_id
                                         and b.request_id = p_request_id  ;

Line: 1439


    update hz_imp_batch_summary h
    set contactpoint_merge_requests =  cp_count
    where h.batch_id = p_batch_id ;

Line: 1446


    select count(distinct a.dup_record_id) into c_count
                                       from hz_imp_dup_details a, hz_org_contacts b
                                       where a.batch_id = p_batch_id
                                       and a.record_id = b.org_contact_id
                                       and b.request_id = p_request_id ;

Line: 1452


    update hz_imp_batch_summary h
    set contact_merge_requests =  c_count
    where h.batch_id = p_batch_id ;

Line: 1459


    update hz_imp_batch_summary h
    set total_merge_requests = l_party_count
    where h.batch_id = p_batch_id ;

Line: 1463


    FND_FILE.put_line(FND_FILE.log,'Details ::: Total Number of merge requests inserted into batch summary table is '
                                                   || l_party_count);

Line: 1509


        select match_score, auto_merge_score into x_threshold, x_auto_merge_threshold
        from hz_match_rules_vl
        where match_rule_id = p_match_rule_id;

Line: 1540


PROCEDURE update_party_dqm_action_flag (
    p_batch_id IN number,
    p_cur    IN EntityCur )
is
    l_limit NUMBER := 200;

Line: 1571


                UPDATE HZ_IMP_PARTIES_INT A
                SET A.DQM_ACTION_FLAG = DECODE(H_AM_FLAG(I), 'Y','D','P')
                WHERE
                   ( A.PARTY_ORIG_SYSTEM_REFERENCE = H_POSR(I) and
                     A.PARTY_ORIG_SYSTEM = H_POS(I) and
                     A.BATCH_ID = p_batch_id
                     );

Line: 1590


         FND_MESSAGE.SET_TOKEN('PROC','UPDATE_PARTY_DQM_ACTION_FLAG');

Line: 1596


PROCEDURE update_detail_dqm_action_flag (
    p_entity IN VARCHAR2,
    p_batch_id IN number,
    p_cur    IN EntityCur )
is
    l_limit NUMBER := 200;

Line: 1628


                        UPDATE HZ_IMP_ADDRESSES_INT A
                        SET A.DQM_ACTION_FLAG = 'P'
                        WHERE
                           ( A.SITE_ORIG_SYSTEM_REFERENCE = H_POSR(I) and
                             A.SITE_ORIG_SYSTEM = H_POS(I) and
                             A.BATCH_ID = p_batch_id
                             );

Line: 1639


                        UPDATE HZ_IMP_CONTACTPTS_INT A
                        SET A.DQM_ACTION_FLAG = 'P'
                        WHERE
                           ( A.CP_ORIG_SYSTEM_REFERENCE = H_POSR(I) and
                             A.CP_ORIG_SYSTEM = H_POS(I) and
                             A.BATCH_ID = p_batch_id
                             );

Line: 1650


                                UPDATE HZ_IMP_CONTACTS_INT A
                                SET A.DQM_ACTION_FLAG = 'P'
                                WHERE
                                   ( A.CONTACT_ORIG_SYSTEM_REFERENCE = H_POSR(I) and
                                     A.CONTACT_ORIG_SYSTEM = H_POS(I) and
                                     A.BATCH_ID = p_batch_id
                                     );

Line: 1673


         FND_MESSAGE.SET_TOKEN('PROC','UPDATE_DETAIL_DQM_ACTION_FLAG');

Line: 1687


        update hz_imp_batch_summary h
            set dup_parties =
                        (select count (distinct party_id)
                         from hz_imp_dup_parties
                         where batch_id = p_batch_id
                         and auto_merge_flag = 'Y'
                              )
        where
            h.batch_id = p_batch_id ;

Line: 1699


        update hz_imp_batch_summary h
            set potential_dup_parties =
                        (select count (distinct party_id)
                         from hz_imp_dup_parties
                         where batch_id = p_batch_id
                         and auto_merge_flag = 'N'
                              )
        where
            h.batch_id = p_batch_id ;

Line: 1710


         update hz_imp_batch_summary h
         set potential_dup_addresses =
         (select count(1) from
            (select distinct record_id
            from hz_imp_dup_details
            where batch_id =  p_batch_id
            and entity = 'PARTY_SITES'
            )
          )
          where
          h.batch_id = p_batch_id ;

Line: 1724


         update hz_imp_batch_summary h
         set potential_dup_contacts =
         (select count(1) from
            (select distinct record_id
            from hz_imp_dup_details
            where batch_id =  p_batch_id
            and entity = 'CONTACTS'
            )
          )
          where
          h.batch_id = p_batch_id ;

Line: 1738


         update hz_imp_batch_summary h
         set potential_dup_contactpoints =
         (select count(1) from
            (select distinct record_id
            from hz_imp_dup_details
            where batch_id =  p_batch_id
            and entity = 'CONTACT_POINTS'
            )
          )
          where
          h.batch_id = p_batch_id ;

Line: 1827


        insert into hz_imp_int_dedup_results ( batch_id, winner_record_osr, winner_record_os,
                                               dup_record_osr, dup_record_os, entity,
                                               score, dup_creation_date, dup_last_update_date
                                               ,created_by,creation_date,last_update_login
                                               ,last_update_date,last_updated_by)

        select p_batch_id, win_party_osr,win_party_os, win_party_osr, win_party_os,
               'PARTY', 0, hz_utility_pub.creation_date, hz_utility_pub.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
        (
        select distinct d.ord_f_osr as win_party_osr, d.f_os as win_party_os, level as levelu
        from hz_int_dup_results d
        start with d.ord_f_osr not in
           (
                select c.ord_t_osr
                from hz_int_dup_results c
           )
        connect by prior ord_t_osr = ord_f_osr
        )
        where mod(levelu, 2) = 1 ;

Line: 1854


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

Line: 1860


        insert into hz_imp_int_dedup_results ( batch_id, winner_record_osr, winner_record_os,
                                               dup_record_osr, dup_record_os, entity,
                                               score, dup_creation_date, dup_last_update_date
                                               ,created_by,creation_date,last_update_login
                                               ,last_update_date,last_updated_by)
        select p_batch_id, win_party_osr,win_party_os, win_party_osr, win_party_os,
               'PARTY', 0, hz_utility_pub.creation_date, hz_utility_pub.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
        (
        select distinct d.ord_f_osr as win_party_osr, d.f_os as win_party_os, level as levelu
        from hz_int_dup_results_gt d
        start with d.ord_f_osr not in
           (
                select c.ord_t_osr
                from hz_int_dup_results_gt c
                where c.batch_id = p_batch_id
           )
           and d.batch_id = p_batch_id
        connect by prior ord_t_osr = ord_f_osr  and prior batch_id = batch_id
        )
        where mod(levelu, 2) = 1 ;

Line: 1885


   FND_FILE.put_line(FND_FILE.log,'Parties ::: Number of winner parties inserted to HZ_IMP_INT_DEDUP_RESULTS '||SQL%ROWCOUNT);

Line: 1886


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

Line: 1890


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

Line: 1895


        insert into hz_imp_int_dedup_results ( batch_id, winner_record_osr, winner_record_os,
                                               dup_record_osr, dup_record_os, entity,
                                               score, dup_creation_date, dup_last_update_date
                                               ,created_by,creation_date,last_update_login
                                               ,last_update_date,last_updated_by)
        select p_batch_id, a.winner_record_osr,a.winner_record_os, b.ord_t_osr, b.t_os,
               'PARTY', b.score, hz_utility_pub.creation_date, hz_utility_pub.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_imp_int_dedup_results a, hz_int_dup_results_gt b
        where a.batch_id = p_batch_id and b.batch_id = p_batch_id and a.entity = 'PARTY'
        and b.ord_f_osr = a.winner_record_osr ;

Line: 1909


         FND_FILE.put_line(FND_FILE.log,'Parties ::: Number of duplicate parties inserted to HZ_IMP_INT_DEDUP_RESULTS '||SQL%ROWCOUNT);

Line: 1910


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

Line: 1914


         FND_FILE.put_line(FND_FILE.log,'Parties ::: Begin time of update for getting import table dates '||to_char(sysdate,'hh24:mi:ss'));

Line: 1918


         update hz_imp_int_dedup_results a
         set (a.dup_creation_date, a.dup_last_update_date)
              = (select b.creation_date, b.last_update_date
                 from hz_imp_parties_int b
                 where b.batch_id = p_batch_id
                 and b.party_orig_system_reference = a.dup_record_osr
                 and b.party_orig_system = a.dup_record_os
                  )
          where a.entity = 'PARTY' and a.batch_id = p_batch_id ;

Line: 1928


        FND_FILE.put_line(FND_FILE.log,'Parties ::: Number of duplicate parties updated in HZ_IMP_INT_DEDUP_RESULTS '||SQL%ROWCOUNT);

Line: 1929


        FND_FILE.put_line(FND_FILE.log,'Parties ::: End time of update for getting import table dates '||to_char(sysdate,'hh24:mi:ss'));

Line: 1934


        delete from hz_imp_int_dedup_results a
        where a.entity = 'PARTY'
        and a.batch_id = p_batch_id
        and not exists
             ( select 1
               from hz_imp_parties_int b
               where a.batch_id = b.batch_id
               and a.winner_record_osr = b.party_orig_system_reference
               and a.winner_record_os = b.party_orig_system
              );

Line: 1945


       FND_FILE.put_line(FND_FILE.log,'Parties ::: Delete Complete '||to_char(sysdate,'hh24:mi:ss'));

Line: 1946


       FND_FILE.put_line(FND_FILE.log,'Parties ::: Number of rows deleted is ' || SQL%ROWCOUNT );

Line: 1980


        select match_score into x_threshold
        from hz_match_rules_vl
        where match_rule_id = p_match_rule_id;

Line: 2035


    select match_score into x_threshold
    from hz_match_rules_vl
    where match_rule_id = p_match_rule_id;

Line: 2107


    SELECT DISTINCT a.entity_name
    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
    MINUS
    SELECT DISTINCT a.entity_name
    FROM hz_match_rule_primary p, hz_trans_attributes_vl a
    WHERE a.attribute_id = p.attribute_id
    AND p.match_rule_id = p_match_rule_id;

Line: 2118


    SELECT 1
    FROM hz_match_rule_primary p, hz_primary_trans pt, hz_trans_functions_vl f
    WHERE p.match_rule_id = p_match_rule_id
    AND pt.PRIMARY_ATTRIBUTE_ID = p.PRIMARY_ATTRIBUTE_ID
    AND f.function_id = pt.function_id
    AND nvl(f.ACTIVE_FLAG,'Y') = 'N'
    UNION
    SELECT 1
    FROM hz_match_rule_secondary s, hz_secondary_trans pt, hz_trans_functions_vl f
    WHERE s.match_rule_id = p_match_rule_id
    AND pt.SECONDARY_ATTRIBUTE_ID = s.SECONDARY_ATTRIBUTE_ID
    AND f.function_id = pt.function_id
    AND nvl(f.ACTIVE_FLAG,'Y') = 'N';

Line: 2150


    SELECT 1 INTO l_batch_flag
    FROM HZ_MATCH_RULES_VL
    WHERE match_rule_id = p_match_rule_id;

Line: 2165


     Added update statements since compile_match_rule is public api and commented unnecessary updates in
     compile_all_rules and compile_all_rules_nolog.
 */
     OPEN  check_null_set;

Line: 2223


  UPDATE HZ_MATCH_RULES_B SET COMPILATION_FLAG = 'C' WHERE MATCH_RULE_ID = p_match_rule_id;

Line: 2232


    UPDATE HZ_MATCH_RULES_B SET COMPILATION_FLAG = 'U' WHERE MATCH_RULE_ID = p_match_rule_id;

Line: 2240


    UPDATE HZ_MATCH_RULES_B SET COMPILATION_FLAG = 'U' WHERE MATCH_RULE_ID = p_match_rule_id;

Line: 2254


    UPDATE HZ_MATCH_RULES_B SET COMPILATION_FLAG = 'U' WHERE MATCH_RULE_ID = p_match_rule_id;

Line: 2276


 update hz_dup_results a
 set flag = 'D'
 where exists
    (select 1
     from hz_dup_results b
     where ( a.fid = b.fid and a.tid <> b.tid)
             or (a.tid = b.tid and a.fid <> b.fid)
             or (a.fid = b.tid)
             or (a.tid = b.fid)
     ) ;

Line: 2311


          delete from hz_dup_results a
          where
          a.score < p_threshold
          or
          (exists
            (Select 1 from hz_dup_results b
             where a.fid=b.tid and b.fid=a.tid and a.score = b.score)
             and a.fid < a.tid );

Line: 2321


          delete from hz_dup_results a
          where
          a.score < p_threshold
          or
          exists
            (Select 1 from hz_dup_results b
             where a.fid=b.tid and b.fid=a.tid and a.score < b.score) ;

Line: 2354


select a.fid, a.tid , a.flag, a.dup_set_id
from hz_dup_results a
where (a.flag <> 'D') and
(a.fid = p_fid or a.fid = p_tid or a.tid = p_fid or a.tid = p_tid) and
rownum = 1
order by a.flag ;

Line: 2387


         SELECT HZ_MERGE_BATCH_S.nextval INTO x_dup_set_id FROM DUAL;

Line: 2392


       update hz_dup_results a
       set flag = master_party_id, dup_set_id = x_dup_set_id
       where (a.flag = 'D') and
       (a.fid = p_fid or a.fid = p_tid or a.tid = p_fid or a.tid = p_tid) ;

Line: 2411


       update hz_dup_results a
       set dup_set_id = HZ_MERGE_BATCH_S.nextval
       where a.flag = 'ND' ;

Line: 2433


select fid, tid, dup_set_id, rowid
from hz_dup_results
where flag = 'D'
and rownum = 1
order by flag ;

Line: 2500


           insert into hz_dup_sets ( dup_set_id, dup_batch_id, winner_party_id,
           status, merge_type, created_by, creation_date, last_update_login,
           last_update_date, last_updated_by)
           select dup_set_id, p_batch_id, fid,
                    'SYSBATCH', 'PARTY_MERGE', hz_utility_pub.created_by, hz_utility_pub.creation_date,
                     hz_utility_pub.last_update_login, hz_utility_pub.last_update_date,
                     hz_utility_pub.user_id
                     from hz_dup_results
                     where flag = 'ND' ;

Line: 2511


            insert into hz_dup_set_parties (dup_party_id,dup_set_id,merge_seq_id,
             merge_batch_id,score,merge_flag,created_by,creation_date,last_update_login,
             last_update_date,last_updated_by,dup_set_batch_id) --Bug No: 4244529
            select fid, dup_set_id, 0, p_batch_id, score, decode( sign(score - x_auto_merge_threshold),-1,'N','Y'),
                    hz_utility_pub.created_by,hz_utility_pub.creation_date,
                    hz_utility_pub.last_update_login,hz_utility_pub.last_update_date,
                    hz_utility_pub.user_id,p_batch_id --Bug No: 4244529
            from hz_dup_results
            where flag = 'ND';

Line: 2521


            insert into hz_dup_set_parties (dup_party_id,dup_set_id,merge_seq_id,
             merge_batch_id,score,merge_flag, created_by,creation_date,last_update_login,
             last_update_date,last_updated_by,dup_set_batch_id) --Bug No: 4244529
            select tid, dup_set_id, 0,p_batch_id, score,decode( sign(score - x_auto_merge_threshold),-1,'N','Y'),
                    hz_utility_pub.created_by,hz_utility_pub.creation_date,
                    hz_utility_pub.last_update_login,hz_utility_pub.last_update_date,
                    hz_utility_pub.user_id,p_batch_id --Bug No: 4244529
            from hz_dup_results
            where flag = 'ND';

Line: 2536


           insert into hz_dup_sets ( dup_set_id, dup_batch_id, winner_party_id,
           status, merge_type, created_by, creation_date, last_update_login,
           last_update_date, last_updated_by)
           select distinct dup_set_id, p_batch_id, flag ,
                    'SYSBATCH', 'PARTY_MERGE', hz_utility_pub.created_by, hz_utility_pub.creation_date,
                     hz_utility_pub.last_update_login, hz_utility_pub.last_update_date,
                     hz_utility_pub.user_id
                     from hz_dup_results
                     where flag <> 'ND' ;

Line: 2548


            insert into hz_dup_set_parties (dup_party_id,dup_set_id,merge_seq_id,
             merge_batch_id,score,merge_flag, created_by,creation_date,last_update_login,
             last_update_date,last_updated_by,dup_set_batch_id) --Bug No: 4244529
            select distinct to_number(flag), dup_set_id, 0, p_batch_id, score ,decode( sign(score - x_auto_merge_threshold),-1,'N','Y'),
                    hz_utility_pub.created_by,hz_utility_pub.creation_date,
                    hz_utility_pub.last_update_login,hz_utility_pub.last_update_date,
                    hz_utility_pub.user_id,p_batch_id --Bug No: 4244529
            from hz_dup_results
            where flag <> 'ND'
            union
            select distinct fid, dup_set_id, 0, p_batch_id, score ,decode( sign(score - x_auto_merge_threshold),-1,'N','Y'),
                    hz_utility_pub.created_by,hz_utility_pub.creation_date,
                    hz_utility_pub.last_update_login,hz_utility_pub.last_update_date,
                    hz_utility_pub.user_id,p_batch_id --Bug No: 4244529
            from hz_dup_results
            where flag <> 'ND'
            union
            select distinct tid, dup_set_id, 0, p_batch_id, score ,decode( sign(score - x_auto_merge_threshold),-1,'N','Y'),
                    hz_utility_pub.created_by,hz_utility_pub.creation_date,
                    hz_utility_pub.last_update_login,hz_utility_pub.last_update_date,
                    hz_utility_pub.user_id,p_batch_id --Bug No: 4244529
            from hz_dup_results
            where flag <> 'ND' ;