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

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

Line: 377


      SELECT party_type INTO l_party_type 
      FROM HZ_PARTIES
      WHERE party_id = p_record_id;

Line: 381


        SELECT PARTY_NAME INTO l_matched_value 
        FROM HZ_PARTIES
        WHERE party_id = p_record_id 
        and rownum = 1;

Line: 386


        SELECT PARTY_NAME INTO l_matched_value 
        FROM HZ_PARTIES
        WHERE party_id = p_record_id 
        and rownum = 1;

Line: 395


      SELECT party_type INTO l_party_type 
      FROM HZ_PARTIES
      WHERE party_id = p_record_id;

Line: 399


        SELECT DUNS_NUMBER_C INTO l_matched_value 
        FROM HZ_ORGANIZATION_PROFILES
        WHERE party_id = p_record_id 
        and effective_end_date is null
        and rownum = 1;

Line: 405


        SELECT DUNS_NUMBER_C INTO l_matched_value 
        FROM HZ_ORGANIZATION_PROFILES
        WHERE party_id = p_record_id 
        and rownum = 1;

Line: 414


      SELECT party_type INTO l_party_type 
      FROM HZ_PARTIES
      WHERE party_id = p_record_id;

Line: 418


        SELECT JGZZ_FISCAL_CODE INTO l_matched_value 
        FROM HZ_ORGANIZATION_PROFILES
        WHERE party_id = p_record_id 
        and effective_end_date is null
        and rownum = 1;

Line: 424


        SELECT JGZZ_FISCAL_CODE INTO l_matched_value 
        FROM HZ_PERSON_PROFILES
        WHERE party_id = p_record_id 
        and effective_end_date is null 
        and rownum = 1;

Line: 441


       SELECT HZ_LOCATIONS.CITY
       INTO l_matched_value 
       FROM HZ_PARTY_SITES, HZ_LOCATIONS
       WHERE HZ_PARTY_SITES.party_site_id = p_record_id
       AND HZ_PARTY_SITES.location_id = HZ_LOCATIONS.location_id and rownum=1;

Line: 454


       SELECT HZ_LOCATIONS.STATE
       INTO l_matched_value 
       FROM HZ_PARTY_SITES, HZ_LOCATIONS
       WHERE HZ_PARTY_SITES.party_site_id = p_record_id
       AND HZ_PARTY_SITES.location_id = HZ_LOCATIONS.location_id and rownum=1;

Line: 467


       SELECT HZ_LOCATIONS.COUNTRY
       INTO l_matched_value 
       FROM HZ_PARTY_SITES, HZ_LOCATIONS
       WHERE HZ_PARTY_SITES.party_site_id = p_record_id
       AND HZ_PARTY_SITES.location_id = HZ_LOCATIONS.location_id and rownum=1;

Line: 487


       SELECT HZ_ORG_CONTACTS.JOB_TITLE
       INTO l_matched_value 
       FROM HZ_ORG_CONTACTS, HZ_RELATIONSHIPS, HZ_PERSON_PROFILES
       WHERE HZ_ORG_CONTACTS.org_contact_id = p_record_id
       AND HZ_RELATIONSHIPS.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
       AND HZ_RELATIONSHIPS.OBJECT_TABLE_NAME = 'HZ_PARTIES'
       AND HZ_RELATIONSHIPS.DIRECTIONAL_FLAG = 'F'
       AND HZ_ORG_CONTACTS.party_relationship_id = HZ_RELATIONSHIPS.relationship_id
       AND HZ_RELATIONSHIPS.subject_id = HZ_PERSON_PROFILES.party_id
       AND HZ_PERSON_PROFILES.effective_end_date IS NULL and rownum=1;

Line: 508


       SELECT EMAIL_ADDRESS INTO l_matched_value 
       FROM HZ_CONTACT_POINTS 
       WHERE contact_point_id = p_record_id and rownum=1;

Line: 519


       SELECT URL INTO l_matched_value 
       FROM HZ_CONTACT_POINTS 
       WHERE contact_point_id = p_record_id and rownum=1;

Line: 535


  PROCEDURE INSERT_PARTY_SCORE (
       p_party_id IN  NUMBER
       ,p_record_id IN  NUMBER
       ,p_search_ctx_id IN  NUMBER
       ,p_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type
       ,p_stage_rec IN HZ_PARTY_STAGE.party_stage_rec_type
      ,p_table_TX2 VARCHAR2
      ,p_table_TX59 VARCHAR2
      ,p_table_TX41 VARCHAR2
      ,p_table_TX45 VARCHAR2
        ,p_idx IN NUMBER) IS
    l_current_score NUMBER:=0;

Line: 551


      hz_utility_v2pub.debug(p_message=>'Inside Calling Procedure - INSERT_PARTY_SCORE',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);

Line: 572


      hz_utility_v2pub.debug(p_message=>'Inserting into HZ_PARTY_SCORE_DTLS_GT ...',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);

Line: 574


      INSERT INTO HZ_PARTY_SCORE_DTLS_GT (PARTY_ID, RECORD_ID, SEARCH_CONTEXT_ID,
                ATTRIBUTE,ENTITY,ENTERED_VALUE, MATCHED_VALUE, ASSIGNED_SCORE)
      VALUES (
           p_party_id,p_record_id,p_search_ctx_id,'PARTY_NAME',
           'PARTY', p_search_rec.PARTY_NAME,
           l_attrib_value,l_score);

Line: 581


      hz_utility_v2pub.debug(p_message=>'Inserting into HZ_PARTY_SCORE_DTLS_GT ... Done',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);

Line: 597


      hz_utility_v2pub.debug(p_message=>'Inserting into HZ_PARTY_SCORE_DTLS_GT ...',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);

Line: 599


      INSERT INTO HZ_PARTY_SCORE_DTLS_GT (PARTY_ID, RECORD_ID, SEARCH_CONTEXT_ID,
                ATTRIBUTE,ENTITY,ENTERED_VALUE, MATCHED_VALUE, ASSIGNED_SCORE)
      VALUES (
           p_party_id,p_record_id,p_search_ctx_id,'DUNS_NUMBER_C',
           'PARTY', p_search_rec.DUNS_NUMBER_C,
           l_attrib_value,l_score);

Line: 606


      hz_utility_v2pub.debug(p_message=>'Inserting into HZ_PARTY_SCORE_DTLS_GT ... Done',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);

Line: 622


      hz_utility_v2pub.debug(p_message=>'Inserting into HZ_PARTY_SCORE_DTLS_GT ...',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);

Line: 624


      INSERT INTO HZ_PARTY_SCORE_DTLS_GT (PARTY_ID, RECORD_ID, SEARCH_CONTEXT_ID,
                ATTRIBUTE,ENTITY,ENTERED_VALUE, MATCHED_VALUE, ASSIGNED_SCORE)
      VALUES (
           p_party_id,p_record_id,p_search_ctx_id,'JGZZ_FISCAL_CODE',
           'PARTY', p_search_rec.JGZZ_FISCAL_CODE,
           l_attrib_value,l_score);

Line: 631


      hz_utility_v2pub.debug(p_message=>'Inserting into HZ_PARTY_SCORE_DTLS_GT ... Done',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);

Line: 636


  PROCEDURE INSERT_PARTY_SITES_SCORE (
       p_party_id IN  NUMBER
       ,p_record_id IN  NUMBER
       ,p_search_ctx_id IN  NUMBER
       ,p_search_rec IN HZ_PARTY_SEARCH.party_site_search_rec_type
       ,p_stage_rec IN HZ_PARTY_STAGE.party_site_stage_rec_type
      ,p_table_TX26 VARCHAR2
      ,p_table_TX9 VARCHAR2
      ,p_table_TX14 VARCHAR2
      ,p_table_TX22 VARCHAR2
        ,p_idx IN NUMBER) IS
    l_current_score NUMBER:=0;

Line: 652


      hz_utility_v2pub.debug(p_message=>'Inside Calling Procedure - INSERT_PARTY_SITES_SCORE',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);

Line: 667


      hz_utility_v2pub.debug(p_message=>'Inserting into HZ_PARTY_SCORE_DTLS_GT ...',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);

Line: 669


      INSERT INTO HZ_PARTY_SCORE_DTLS_GT (PARTY_ID, RECORD_ID, SEARCH_CONTEXT_ID,
                ATTRIBUTE,ENTITY,ENTERED_VALUE, MATCHED_VALUE, ASSIGNED_SCORE)
      VALUES (
           p_party_id,p_record_id,p_search_ctx_id,'ADDRESS',
           'PARTY_SITES', p_search_rec.ADDRESS,
           l_attrib_value,l_score);

Line: 676


      hz_utility_v2pub.debug(p_message=>'Inserting into HZ_PARTY_SCORE_DTLS_GT ... Done',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);

Line: 692


      hz_utility_v2pub.debug(p_message=>'Inserting into HZ_PARTY_SCORE_DTLS_GT ...',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);

Line: 694


      INSERT INTO HZ_PARTY_SCORE_DTLS_GT (PARTY_ID, RECORD_ID, SEARCH_CONTEXT_ID,
                ATTRIBUTE,ENTITY,ENTERED_VALUE, MATCHED_VALUE, ASSIGNED_SCORE)
      VALUES (
           p_party_id,p_record_id,p_search_ctx_id,'CITY',
           'PARTY_SITES', p_search_rec.CITY,
           l_attrib_value,l_score);

Line: 701


      hz_utility_v2pub.debug(p_message=>'Inserting into HZ_PARTY_SCORE_DTLS_GT ... Done',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);

Line: 717


      hz_utility_v2pub.debug(p_message=>'Inserting into HZ_PARTY_SCORE_DTLS_GT ...',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);

Line: 719


      INSERT INTO HZ_PARTY_SCORE_DTLS_GT (PARTY_ID, RECORD_ID, SEARCH_CONTEXT_ID,
                ATTRIBUTE,ENTITY,ENTERED_VALUE, MATCHED_VALUE, ASSIGNED_SCORE)
      VALUES (
           p_party_id,p_record_id,p_search_ctx_id,'STATE',
           'PARTY_SITES', p_search_rec.STATE,
           l_attrib_value,l_score);

Line: 726


      hz_utility_v2pub.debug(p_message=>'Inserting into HZ_PARTY_SCORE_DTLS_GT ... Done',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);

Line: 742


      hz_utility_v2pub.debug(p_message=>'Inserting into HZ_PARTY_SCORE_DTLS_GT ...',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);

Line: 744


      INSERT INTO HZ_PARTY_SCORE_DTLS_GT (PARTY_ID, RECORD_ID, SEARCH_CONTEXT_ID,
                ATTRIBUTE,ENTITY,ENTERED_VALUE, MATCHED_VALUE, ASSIGNED_SCORE)
      VALUES (
           p_party_id,p_record_id,p_search_ctx_id,'COUNTRY',
           'PARTY_SITES', p_search_rec.COUNTRY,
           l_attrib_value,l_score);

Line: 751


      hz_utility_v2pub.debug(p_message=>'Inserting into HZ_PARTY_SCORE_DTLS_GT ... Done',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);

Line: 756


  PROCEDURE INSERT_CONTACTS_SCORE (
       p_party_id IN  NUMBER
       ,p_record_id IN  NUMBER
       ,p_search_ctx_id IN  NUMBER
       ,p_search_rec IN HZ_PARTY_SEARCH.contact_search_rec_type
       ,p_stage_rec IN HZ_PARTY_STAGE.contact_stage_rec_type
      ,p_table_TX2 VARCHAR2
      ,p_table_TX23 VARCHAR2
      ,p_table_TX22 VARCHAR2
        ,p_idx IN NUMBER) IS
    l_current_score NUMBER:=0;

Line: 771


      hz_utility_v2pub.debug(p_message=>'Inside Calling Procedure - INSERT_CONTACTS_SCORE',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);

Line: 792


      hz_utility_v2pub.debug(p_message=>'Inserting into HZ_PARTY_SCORE_DTLS_GT ...',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);

Line: 794


      INSERT INTO HZ_PARTY_SCORE_DTLS_GT (PARTY_ID, RECORD_ID, SEARCH_CONTEXT_ID,
                ATTRIBUTE,ENTITY,ENTERED_VALUE, MATCHED_VALUE, ASSIGNED_SCORE)
      VALUES (
           p_party_id,p_record_id,p_search_ctx_id,'CONTACT_NAME',
           'CONTACTS', p_search_rec.CONTACT_NAME,
           l_attrib_value,l_score);

Line: 801


      hz_utility_v2pub.debug(p_message=>'Inserting into HZ_PARTY_SCORE_DTLS_GT ... Done',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);

Line: 817


      hz_utility_v2pub.debug(p_message=>'Inserting into HZ_PARTY_SCORE_DTLS_GT ...',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);

Line: 819


      INSERT INTO HZ_PARTY_SCORE_DTLS_GT (PARTY_ID, RECORD_ID, SEARCH_CONTEXT_ID,
                ATTRIBUTE,ENTITY,ENTERED_VALUE, MATCHED_VALUE, ASSIGNED_SCORE)
      VALUES (
           p_party_id,p_record_id,p_search_ctx_id,'JOB_TITLE',
           'CONTACTS', p_search_rec.JOB_TITLE,
           l_attrib_value,l_score);

Line: 826


      hz_utility_v2pub.debug(p_message=>'Inserting into HZ_PARTY_SCORE_DTLS_GT ... Done',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);

Line: 831


  PROCEDURE INSERT_CONTACT_POINTS_SCORE (
       p_party_id IN  NUMBER
       ,p_record_id IN  NUMBER
       ,p_search_ctx_id IN  NUMBER
       ,p_search_rec IN HZ_PARTY_SEARCH.contact_point_search_rec_type
       ,p_stage_rec IN HZ_PARTY_STAGE.contact_pt_stage_rec_type
      ,p_table_TX5 VARCHAR2
      ,p_table_TX7 VARCHAR2
      ,p_table_TX10 VARCHAR2
      ,p_table_TX158 VARCHAR2
        ,p_idx IN NUMBER) IS
    l_current_score NUMBER:=0;

Line: 847


      hz_utility_v2pub.debug(p_message=>'Inside Calling Procedure - INSERT_CONTACT_POINTS_SCORE',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);

Line: 862


      hz_utility_v2pub.debug(p_message=>'Inserting into HZ_PARTY_SCORE_DTLS_GT ...',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);

Line: 864


      INSERT INTO HZ_PARTY_SCORE_DTLS_GT (PARTY_ID, RECORD_ID, SEARCH_CONTEXT_ID,
                ATTRIBUTE,ENTITY,ENTERED_VALUE, MATCHED_VALUE, ASSIGNED_SCORE)
      VALUES (
           p_party_id,p_record_id,p_search_ctx_id,'EMAIL_ADDRESS',
           'CONTACT_POINTS', p_search_rec.EMAIL_ADDRESS,
           l_attrib_value,l_score);

Line: 871


      hz_utility_v2pub.debug(p_message=>'Inserting into HZ_PARTY_SCORE_DTLS_GT ... Done',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);

Line: 887


      hz_utility_v2pub.debug(p_message=>'Inserting into HZ_PARTY_SCORE_DTLS_GT ...',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);

Line: 889


      INSERT INTO HZ_PARTY_SCORE_DTLS_GT (PARTY_ID, RECORD_ID, SEARCH_CONTEXT_ID,
                ATTRIBUTE,ENTITY,ENTERED_VALUE, MATCHED_VALUE, ASSIGNED_SCORE)
      VALUES (
           p_party_id,p_record_id,p_search_ctx_id,'URL',
           'CONTACT_POINTS', p_search_rec.URL,
           l_attrib_value,l_score);

Line: 896


      hz_utility_v2pub.debug(p_message=>'Inserting into HZ_PARTY_SCORE_DTLS_GT ... Done',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);

Line: 918


      hz_utility_v2pub.debug(p_message=>'Inserting into HZ_PARTY_SCORE_DTLS_GT ...',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);

Line: 920


      INSERT INTO HZ_PARTY_SCORE_DTLS_GT (PARTY_ID, RECORD_ID, SEARCH_CONTEXT_ID,
                ATTRIBUTE,ENTITY,ENTERED_VALUE, MATCHED_VALUE, ASSIGNED_SCORE)
      VALUES (
           p_party_id,p_record_id,p_search_ctx_id,'RAW_PHONE_NUMBER',
           'CONTACT_POINTS', p_search_rec.RAW_PHONE_NUMBER,
           l_attrib_value,l_score);

Line: 927


      hz_utility_v2pub.debug(p_message=>'Inserting into HZ_PARTY_SCORE_DTLS_GT ... Done',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);

Line: 1073


    g_mappings.DELETE;

Line: 1074


    g_party_site_stage_list.DELETE;

Line: 1075


    g_contact_stage_list.DELETE;

Line: 1076


    g_contact_pt_stage_list.DELETE;

Line: 1077


    call_order.DELETE;

Line: 1078


    call_max_score.DELETE;

Line: 1165


        SELECT PARTY_ID , TX2, TX59, lpad(rtrim(TX41),9,chr(48)), TX45
        FROM HZ_STAGED_PARTIES stage
        WHERE PARTY_ID = p_dup_party_id;

Line: 1171


          SELECT /*+ ORDERED */ stage.PARTY_ID , stage.TX2, stage.TX59, lpad(rtrim(stage.TX41),9,chr(48)), stage.TX45
          FROM HZ_SRCH_PARTIES srch, HZ_STAGED_PARTIES stage
          WHERE
            srch.batch_id = -1
            AND
            (
-------PARTY LEVEL ACQUISITION ON NON-FILTER ATTRIBUTES USING B-TREE INDEXES ---------
-- do an or between all the transformations of an attribute -- 
(
(srch.TX59 is not null and stage.TX59 like srch.TX59 || '%')
)
 OR 
-- do an or between all the transformations of an attribute -- 
(
(srch.TX41 is not null and stage.TX41 like srch.TX41 || '%')
)
 OR 
-- do an or between all the transformations of an attribute -- 
(
(srch.TX45 is not null and stage.TX45 like srch.TX45 || '%')
)
)
          AND ((g_party_stage_rec.TX36 IS NULL OR g_party_stage_rec.TX36||' ' =  stage.TX36) AND (g_party_stage_rec.TX46 IS NULL OR g_party_stage_rec.TX46||' ' =  stage.TX46))
          AND (nvl(p_search_merged,'N')='Y' OR nvl(stage.status,'A') in ('A'))
          AND (p_dup_party_id IS NULL OR stage.party_id <> p_dup_party_id);

Line: 1198


            SELECT /*+ ORDERED INDEX(stage HZ_STAGED_PARTIES_U1) */ stage.PARTY_ID , stage.TX2, stage.TX59, lpad(rtrim(stage.TX41),9,chr(48)), stage.TX45
            FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_PARTIES stage, HZ_SRCH_PARTIES srch
            WHERE
            srch.batch_id = -1
            AND
            (
-------PARTY LEVEL ACQUISITION ON NON-FILTER ATTRIBUTES USING B-TREE INDEXES ---------
-- do an or between all the transformations of an attribute -- 
(
(srch.TX59 is not null and stage.TX59 like srch.TX59 || '%')
)
 OR 
-- do an or between all the transformations of an attribute -- 
(
(srch.TX41 is not null and stage.TX41 like srch.TX41 || '%')
)
 OR 
-- do an or between all the transformations of an attribute -- 
(
(srch.TX45 is not null and stage.TX45 like srch.TX45 || '%')
)
)
            AND d.SEARCH_CONTEXT_ID=p_search_ctx_id
            AND d.party_id = stage.party_id
            AND ((g_party_stage_rec.TX36 IS NULL OR g_party_stage_rec.TX36||' ' =  stage.TX36) AND (g_party_stage_rec.TX46 IS NULL OR g_party_stage_rec.TX46||' ' =  stage.TX46))
            AND (p_dup_party_id IS NULL OR stage.party_id <> p_dup_party_id)
            AND (nvl(p_search_merged,'N')='Y' OR nvl(stage.status,'A') in ('A'));

Line: 1228


       l_sqlstr := 'SELECT /*+ ORDERED */ stage.PARTY_ID , stage.TX2, stage.TX59, lpad(rtrim(stage.TX41),9,chr(48)), stage.TX45'||
                   ' FROM HZ_SRCH_PARTIES srch, HZ_STAGED_PARTIES stage'||
                   ' WHERE'||
            'srch.batch_id = -1'||
            'AND'||
            '('||
-------PARTY LEVEL ACQUISITION ON NON-FILTER ATTRIBUTES USING B-TREE INDEXES ---------
-- do an or between all the transformations of an attribute -- 
'('||
'(srch.TX59 is not null and stage.TX59 like srch.TX59 || ''%'')'||
')'||
' OR '||
-- do an or between all the transformations of an attribute -- 
'('||
'(srch.TX41 is not null and stage.TX41 like srch.TX41 || ''%'')'||
')'||
' OR '||
-- do an or between all the transformations of an attribute -- 
'('||
'(srch.TX45 is not null and stage.TX45 like srch.TX45 || ''%'')'||
')'||
')'||
                   ' AND ((:TX36 IS NULL OR :TX36||'' '' =  stage.TX36) AND (:TX46 IS NULL OR :TX46||'' '' =  stage.TX46))'||
                   ' AND ('||p_restrict_sql||')' ||
                   ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';

Line: 1284


          SELECT /*+ INDEX(stage HZ_STAGED_PARTY_SITES_N1) */ stage.PARTY_SITE_ID, stage.PARTY_ID, stage.ORG_CONTACT_ID, stage.TX26, stage.TX9, stage.TX14, stage.TX22
          FROM HZ_STAGED_PARTY_SITES stage,HZ_SRCH_PSITES srch
WHERE
            srch.batch_id = -1
            AND
            (
-------PARTY LEVEL ACQUISITION ON NON-FILTER ATTRIBUTES USING B-TREE INDEXES ---------
-- do an or between all the transformations of an attribute -- 
(
(srch.TX26 is not null and stage.TX26 like srch.TX26 || '%')
)
)
        AND EXISTS (
          SELECT  /*+ INDEX(p HZ_STAGED_PARTIES_U1) */  1 FROM HZ_STAGED_PARTIES p
          WHERE p.PARTY_ID = stage.PARTY_ID
          AND ((g_party_stage_rec.TX36 IS NULL OR g_party_stage_rec.TX36||' ' =  p.TX36) AND (g_party_stage_rec.TX46 IS NULL OR g_party_stage_rec.TX46||' ' =  p.TX46)))
          AND stage.party_id = p_party_id;

Line: 1304


          SELECT /*+ ORDERED USE_NL(srch stage p) */  stage.PARTY_SITE_ID, stage.PARTY_ID, stage.ORG_CONTACT_ID, stage.TX26, stage.TX9, stage.TX14, stage.TX22
          FROM  HZ_SRCH_PSITES srch, HZ_STAGED_PARTY_SITES stage, HZ_STAGED_PARTIES p
WHERE
            srch.batch_id = -1
            AND
            (
-------PARTY LEVEL ACQUISITION ON NON-FILTER ATTRIBUTES USING B-TREE INDEXES ---------
-- do an or between all the transformations of an attribute -- 
(
(srch.TX26 is not null and stage.TX26 like srch.TX26 || '%')
)
)
    AND  p.PARTY_ID = stage.PARTY_ID
    AND ((g_party_stage_rec.TX36 IS NULL OR g_party_stage_rec.TX36||' ' =  p.TX36) AND (g_party_stage_rec.TX46 IS NULL OR g_party_stage_rec.TX46||' ' =  p.TX46))
          AND (p_dup_party_id IS NULL OR stage.party_id <> p_dup_party_id);

Line: 1321


            SELECT  stage.PARTY_SITE_ID, stage.PARTY_ID, stage.ORG_CONTACT_ID, stage.TX26, stage.TX9, stage.TX14, stage.TX22
            FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_PARTY_SITES stage, HZ_SRCH_PSITES srch
WHERE
            srch.batch_id = -1
            AND
            (
-------PARTY LEVEL ACQUISITION ON NON-FILTER ATTRIBUTES USING B-TREE INDEXES ---------
-- do an or between all the transformations of an attribute -- 
(
(srch.TX26 is not null and stage.TX26 like srch.TX26 || '%')
)
)
            AND d.search_context_id = p_search_ctx_id
            AND d.party_id = stage.party_id
            AND (p_dup_party_id IS NULL OR stage.party_id <> p_dup_party_id);

Line: 1338


       l_sqlstr := 'SELECT /*+ ORDERED USE_NL(srch stage p) */ stage.PARTY_SITE_ID, stage.PARTY_ID, stage.ORG_CONTACT_ID , stage.TX26, stage.TX9, stage.TX14, stage.TX22'||
                   ' FROM HZ_SRCH_PSITES srch, HZ_STAGED_PARTY_SITES stage, HZ_STAGED_PARTIES p'||
                   ' WHERE' ||
            'srch.batch_id = -1'||
            'AND'||
            '('||
-------PARTY_SITES LEVEL ACQUISITION ON NON-FILTER ATTRIBUTES USING B-TREE INDEXES ---------
-- do an or between all the transformations of an attribute -- 
'('||
'(srch.TX26 is not null and stage.TX26 like srch.TX26 || ''%'')'||
')'||
')'||
                 ' AND p.party_id = stage.party_id ' || 
                 ' AND ((:TX36 IS NULL OR :TX36||'' '' =  p.TX36) AND (:TX46 IS NULL OR :TX46||'' '' =  p.TX46))) ' || 
                   ' AND ('||p_restrict_sql||')' ||
                   ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';

Line: 1381


          SELECT /*+ INDEX(stage HZ_STAGED_CONTACTS_N1) */ stage.ORG_CONTACT_ID, stage.PARTY_ID, stage.TX2, stage.TX23, stage.TX22
          FROM HZ_STAGED_CONTACTS stage, HZ_SRCH_CONTACTS srch
          WHERE
            srch.batch_id = -1
            AND
            (
-------PARTY LEVEL ACQUISITION ON NON-FILTER ATTRIBUTES USING B-TREE INDEXES ---------
-- do an or between all the transformations of an attribute -- 
(
(srch.TX23 is not null and stage.TX23 like srch.TX23 || '%')
)
)
        AND EXISTS (
          SELECT /*+ INDEX(p HZ_STAGED_PARTIES_U1) */  1 FROM HZ_STAGED_PARTIES p
          WHERE p.PARTY_ID = stage.PARTY_ID
          AND ((g_party_stage_rec.TX36 IS NULL OR g_party_stage_rec.TX36||' ' =  p.TX36) AND (g_party_stage_rec.TX46 IS NULL OR g_party_stage_rec.TX46||' ' =  p.TX46)))
          AND stage.party_id = p_party_id;

Line: 1401


          SELECT /*+ USE_NL(srch stage)  */ stage.ORG_CONTACT_ID, stage.PARTY_ID, stage.TX2, stage.TX23, stage.TX22
          FROM  HZ_SRCH_CONTACTS srch, HZ_STAGED_CONTACTS stage
          WHERE
            srch.batch_id = -1
            AND
            (
-------PARTY LEVEL ACQUISITION ON NON-FILTER ATTRIBUTES USING B-TREE INDEXES ---------
-- do an or between all the transformations of an attribute -- 
(
(srch.TX23 is not null and stage.TX23 like srch.TX23 || '%')
)
)
        AND EXISTS (
          SELECT /*+ INDEX(p HZ_STAGED_PARTIES_U1) */ 1 FROM HZ_STAGED_PARTIES p
          WHERE p.PARTY_ID = stage.PARTY_ID
          AND ((g_party_stage_rec.TX36 IS NULL OR g_party_stage_rec.TX36||' ' =  p.TX36) AND (g_party_stage_rec.TX46 IS NULL OR g_party_stage_rec.TX46||' ' =  p.TX46)))
          AND (p_dup_party_id IS NULL OR stage.party_id <> p_dup_party_id);

Line: 1420


            SELECT /*+ ORDERED INDEX(stage HZ_STAGED_CONTACTS_N1) */ stage.ORG_CONTACT_ID, stage.PARTY_ID, stage.TX2, stage.TX23, stage.TX22
            FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACTS stage, HZ_SRCH_CONTACTS srch
            WHERE
            srch.batch_id = -1
            AND
            (
-------PARTY LEVEL ACQUISITION ON NON-FILTER ATTRIBUTES USING B-TREE INDEXES ---------
-- do an or between all the transformations of an attribute -- 
(
(srch.TX23 is not null and stage.TX23 like srch.TX23 || '%')
)
)
            AND d.search_context_id = p_search_ctx_id
            AND d.party_id = stage.party_id
            AND (p_dup_party_id IS NULL OR stage.party_id <> p_dup_party_id);

Line: 1437


       l_sqlstr := 'SELECT /*+ USE_NL(srch stage)  */  stage.ORG_CONTACT_ID, stage.PARTY_ID , stage.TX2, stage.TX23, stage.TX22'||
                   ' FROM HZ_SRCH_CONTACTS srch, HZ_STAGED_CONTACTS stage'||
                   ' WHERE '||
            'srch.batch_id = -1'||
            'AND'||
            '('||
-------CONTACTS LEVEL ACQUISITION ON NON-FILTER ATTRIBUTES USING B-TREE INDEXES ---------
-- do an or between all the transformations of an attribute -- 
'('||
'(srch.TX23 is not null and stage.TX23 like srch.TX23 || ''%'')'||
')'||
')'||
                 ' AND EXISTS ('||
                 ' SELECT /*+ INDEX(p HZ_STAGED_PARTIES_U1) */ 1 FROM HZ_STAGED_PARTIES p ' || 
                 ' WHERE p.party_id = stage.party_id ' || 
                 ' AND ((:TX36 IS NULL OR :TX36||'' '' =  p.TX36) AND (:TX46 IS NULL OR :TX46||'' '' =  p.TX46))) ' || 
                   ' AND ('||p_restrict_sql||')' ||
                   ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';

Line: 1482


          SELECT /*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N1) */ stage.CONTACT_POINT_ID, stage.PARTY_ID,stage.PARTY_SITE_ID, stage.ORG_CONTACT_ID , stage.TX5, stage.TX7, stage.TX10, stage.TX158
          FROM HZ_STAGED_CONTACT_POINTS stage, HZ_SRCH_CPTS srch
            WHERE
            srch.batch_id = -1
            AND
            (
-------PARTY LEVEL ACQUISITION ON NON-FILTER ATTRIBUTES USING B-TREE INDEXES ---------
-- do an or between all the transformations of an attribute -- 
(
(srch.TX5 is not null and stage.TX5 like srch.TX5 || '%')
)
 OR 
-- do an or between all the transformations of an attribute -- 
(
(srch.TX7 is not null and stage.TX7 like srch.TX7 || '%')
)
 OR 
-- do an or between all the transformations of an attribute -- 
(
(srch.TX158 is not null and stage.TX158 like srch.TX158 || '%')
)
)
        AND EXISTS (
          SELECT /*+ INDEX(p HZ_STAGED_PARTIES_U1) */ 1 FROM HZ_STAGED_PARTIES p
          WHERE p.PARTY_ID = stage.PARTY_ID
          AND ((g_party_stage_rec.TX36 IS NULL OR g_party_stage_rec.TX36||' ' =  p.TX36) AND (g_party_stage_rec.TX46 IS NULL OR g_party_stage_rec.TX46||' ' =  p.TX46)))
          AND stage.party_id = p_party_id;

Line: 1512


          SELECT /*+ USE_NL(srch stage)  */  stage.CONTACT_POINT_ID, stage.PARTY_ID, stage.PARTY_SITE_ID, stage.ORG_CONTACT_ID , stage.TX5, stage.TX7, stage.TX10, stage.TX158
          FROM  HZ_SRCH_CPTS srch, HZ_STAGED_CONTACT_POINTS stage
            WHERE
            srch.batch_id = -1
            AND
            (
-------PARTY LEVEL ACQUISITION ON NON-FILTER ATTRIBUTES USING B-TREE INDEXES ---------
-- do an or between all the transformations of an attribute -- 
(
(srch.TX5 is not null and stage.TX5 like srch.TX5 || '%')
)
 OR 
-- do an or between all the transformations of an attribute -- 
(
(srch.TX7 is not null and stage.TX7 like srch.TX7 || '%')
)
 OR 
-- do an or between all the transformations of an attribute -- 
(
(srch.TX158 is not null and stage.TX158 like srch.TX158 || '%')
)
)
        AND EXISTS (
          SELECT  /*+ INDEX(p HZ_STAGED_PARTIES_U1) */ 1 FROM HZ_STAGED_PARTIES p
          WHERE p.PARTY_ID = stage.PARTY_ID
          AND ((g_party_stage_rec.TX36 IS NULL OR g_party_stage_rec.TX36||' ' =  p.TX36) AND (g_party_stage_rec.TX46 IS NULL OR g_party_stage_rec.TX46||' ' =  p.TX46)))
          AND (p_dup_party_id IS NULL OR stage.party_id <> p_dup_party_id);

Line: 1541


            SELECT /*+ ORDERED INDEX(stage HZ_STAGED_CONTACT_POINTS_N1) */ stage.CONTACT_POINT_ID, stage.PARTY_ID, stage.PARTY_SITE_ID, stage.ORG_CONTACT_ID , stage.TX5, stage.TX7, stage.TX10, stage.TX158
            FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage, HZ_SRCH_CPTS srch
            WHERE
            srch.batch_id = -1
            AND
            (
-------PARTY LEVEL ACQUISITION ON NON-FILTER ATTRIBUTES USING B-TREE INDEXES ---------
-- do an or between all the transformations of an attribute -- 
(
(srch.TX5 is not null and stage.TX5 like srch.TX5 || '%')
)
 OR 
-- do an or between all the transformations of an attribute -- 
(
(srch.TX7 is not null and stage.TX7 like srch.TX7 || '%')
)
 OR 
-- do an or between all the transformations of an attribute -- 
(
(srch.TX158 is not null and stage.TX158 like srch.TX158 || '%')
)
)
            AND d.search_context_id = p_search_ctx_id
            AND d.party_id = stage.party_id
            AND (p_dup_party_id IS NULL OR stage.party_id <> p_dup_party_id);

Line: 1568


       l_sqlstr := 'SELECT /*+ USE_NL(srch stage)  */ stage.CONTACT_POINT_ID, stage.PARTY_ID, stage.PARTY_SITE_ID, stage.ORG_CONTACT_ID  , stage.TX5, stage.TX7, stage.TX10, stage.TX158'||
                   ' FROM HZ_SRCH_CPTS srch, HZ_STAGED_CONTACT_POINTS stage'||
                   ' WHERE'||
            'srch.batch_id = -1'||
            'AND'||
            '('||
-------CONTACT_POINTS LEVEL ACQUISITION ON NON-FILTER ATTRIBUTES USING B-TREE INDEXES ---------
-- do an or between all the transformations of an attribute -- 
'('||
'(srch.TX5 is not null and stage.TX5 like srch.TX5 || ''%'')'||
')'||
' OR '||
-- do an or between all the transformations of an attribute -- 
'('||
'(srch.TX7 is not null and stage.TX7 like srch.TX7 || ''%'')'||
')'||
' OR '||
-- do an or between all the transformations of an attribute -- 
'('||
'(srch.TX158 is not null and stage.TX158 like srch.TX158 || ''%'')'||
')'||
')'||
                 ' AND EXISTS ('||
                 ' SELECT  /*+ INDEX(p HZ_STAGED_PARTIES_U1) */ 1 FROM HZ_STAGED_PARTIES p ' || 
                 ' WHERE p.party_id = stage.party_id ' || 
                 ' AND ((:TX36 IS NULL OR :TX36||'' '' =  p.TX36) AND (:TX46 IS NULL OR :TX46||'' '' =  p.TX46))) ' || 
                   ' AND ('||p_restrict_sql||')' ||
                   ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';

Line: 1760


    H_PARTY_ID.DELETE;

Line: 1761


    H_PARTY_ID_LIST.DELETE;

Line: 1762


    H_SCORES.DELETE;

Line: 1763


    g_mappings.DELETE;

Line: 1780


         INSERT INTO HZ_DQM_PARTIES_GT (SEARCH_CONTEXT_ID,PARTY_ID) VALUES (
             l_search_ctx_id,H_PARTY_ID(I));

Line: 1782


      H_PARTY_ID.DELETE;

Line: 1783


      H_PARTY_ID_LIST.DELETE;

Line: 1787


         INSERT INTO HZ_DQM_PARTIES_GT (SEARCH_CONTEXT_ID,PARTY_ID) VALUES (
             l_search_ctx_id,H_PARTY_ID(I));

Line: 1846


         INSERT INTO HZ_DQM_PARTIES_GT (SEARCH_CONTEXT_ID,PARTY_ID) VALUES (
             l_search_ctx_id,H_PARTY_ID(I));

Line: 1848


      H_PARTY_ID.DELETE;

Line: 1849


      H_PARTY_ID_LIST.DELETE;

Line: 1853


         INSERT INTO HZ_DQM_PARTIES_GT (SEARCH_CONTEXT_ID,PARTY_ID) VALUES (
             l_search_ctx_id,H_PARTY_ID(I));

Line: 1923


         INSERT INTO HZ_DQM_PARTIES_GT (SEARCH_CONTEXT_ID,PARTY_ID) VALUES (
             l_search_ctx_id,H_PARTY_ID(I));

Line: 1925


      H_PARTY_ID.DELETE;

Line: 1926


      H_PARTY_ID_LIST.DELETE;

Line: 1930


         INSERT INTO HZ_DQM_PARTIES_GT (SEARCH_CONTEXT_ID,PARTY_ID) VALUES (
             l_search_ctx_id,H_PARTY_ID(I));

Line: 1998


         INSERT INTO HZ_DQM_PARTIES_GT (SEARCH_CONTEXT_ID,PARTY_ID) VALUES (
             l_search_ctx_id,H_PARTY_ID(I));

Line: 2000


      H_PARTY_ID.DELETE;

Line: 2001


      H_PARTY_ID_LIST.DELETE;

Line: 2005


         INSERT INTO HZ_DQM_PARTIES_GT (SEARCH_CONTEXT_ID,PARTY_ID) VALUES (
             l_search_ctx_id,H_PARTY_ID(I));

Line: 2130


    SELECT use_contact_addr_flag, use_contact_cpt_flag 
    INTO l_use_contact_addr_flag, l_use_contact_cpt_flag 
    FROM hz_match_rules_b 
    WHERE match_rule_id = 52;

Line: 2158


    SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;

Line: 2204


            INSERT INTO HZ_MATCHED_PARTIES_GT (SEARCH_CONTEXT_ID, PARTY_ID, SCORE) 
            VALUES (l_search_ctx_id,H_SCORES(l_party_id).PARTY_ID,H_SCORES(l_party_id).TOTAL_SCORE);

Line: 2209


              SELECT 1 INTO l_tmp FROM HZ_DUP_SET_PARTIES
              WHERE DUP_PARTY_ID = H_SCORES(l_party_id).PARTY_ID
              AND DUP_SET_BATCH_ID = p_dup_batch_id 
              AND ROWNUM=1;

Line: 2220


                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) 
                VALUES (H_SCORES(l_party_id).PARTY_ID,p_dup_set_id,0,0,
                    H_SCORES(l_party_id).TOTAL_SCORE, l_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,p_dup_batch_id);

Line: 2243


        INSERT INTO HZ_MATCHED_PARTY_SITES_GT (SEARCH_CONTEXT_ID,PARTY_SITE_ID,PARTY_ID,SCORE) VALUES (
          l_search_ctx_id, h_ps_id(I), h_ps_party_id(I), h_ps_score(I));

Line: 2249


        INSERT INTO HZ_MATCHED_CONTACTS_GT (SEARCH_CONTEXT_ID,ORG_CONTACT_ID,PARTY_ID,SCORE) VALUES (
          l_search_ctx_id, h_ct_id(I), h_ct_party_id(I), h_ct_score(I));

Line: 2255


        INSERT INTO HZ_MATCHED_CPTS_GT (SEARCH_CONTEXT_ID,CONTACT_POINT_ID,PARTY_ID,SCORE) VALUES (
          l_search_ctx_id, h_cpt_id(I), h_cpt_party_id(I), h_cpt_score(I));

Line: 2398


    SELECT party_type INTO l_party_type from hz_parties where party_id = p_party_id;

Line: 2405


        SELECT subject_id INTO l_person_id FROM HZ_RELATIONSHIPS r, HZ_ORG_CONTACTS oc, hz_parties p
        WHERE oc.org_contact_id = p_contact_id
        AND r.relationship_id = oc.party_relationship_id 
        AND r.object_id = p_party_id
        AND p.party_id = r.subject_id 
        AND p.party_type = 'PERSON'
        AND ROWNUM=1;

Line: 2423


    H_PARTY_ID.DELETE;

Line: 2424


    H_PARTY_ID_LIST.DELETE;

Line: 2425


    H_SCORES.DELETE;

Line: 2426


    g_mappings.DELETE;

Line: 2444


         INSERT INTO HZ_DQM_PARTIES_GT (SEARCH_CONTEXT_ID,PARTY_ID) VALUES (
             l_search_ctx_id,H_PARTY_ID(I));

Line: 2446


      H_PARTY_ID.DELETE;

Line: 2447


      H_PARTY_ID_LIST.DELETE;

Line: 2451


         INSERT INTO HZ_DQM_PARTIES_GT (SEARCH_CONTEXT_ID,PARTY_ID) VALUES (
             l_search_ctx_id,H_PARTY_ID(I));

Line: 2506


      SELECT /*+ INDEX(stage HZ_STAGED_CONTACTS_U1) */ ORG_CONTACT_ID, PARTY_ID, TX2, TX23, TX22
      FROM HZ_STAGED_CONTACTS stage
      WHERE contains( concat_col, p_contains_str)>0
      AND ORG_CONTACT_ID in (
            SELECT  /*+ ORDERED INDEX(d hz_dqm_parties_gt_n1) USE_NL(d r)*/ 
            org_contact_id
            from hz_dqm_parties_gt d, hz_relationships r, hz_org_contacts oc
            where d.party_id = r.subject_id
            and oc.party_relationship_id = r.relationship_id
            and d.search_context_id = p_search_ctx_id);

Line: 2542


         INSERT INTO HZ_DQM_PARTIES_GT (SEARCH_CONTEXT_ID,PARTY_ID) VALUES (
             l_search_ctx_id,H_PARTY_ID(I));

Line: 2544


      INSERT INTO HZ_DQM_PARTIES_GT (SEARCH_CONTEXT_ID,PARTY_ID)
      SELECT distinct l_search_ctx_id,r.subject_id from HZ_DQM_PARTIES_GT d, HZ_ORG_CONTACTS oc, 
                               HZ_RELATIONSHIPS r
      WHERE oc.party_relationship_id = r.relationship_id
      AND r.object_id = d.party_id
      AND d.SEARCH_CONTEXT_ID=l_search_ctx_id;

Line: 2550


      H_PARTY_ID.DELETE;

Line: 2551


      H_PARTY_ID_LIST.DELETE;

Line: 2555


         INSERT INTO HZ_DQM_PARTIES_GT (SEARCH_CONTEXT_ID,PARTY_ID) VALUES (
             l_search_ctx_id,H_PARTY_ID(I));

Line: 2557


      INSERT INTO HZ_DQM_PARTIES_GT (SEARCH_CONTEXT_ID,PARTY_ID)
      SELECT distinct l_search_ctx_id,r.subject_id from HZ_DQM_PARTIES_GT d, HZ_ORG_CONTACTS oc, 
                               HZ_RELATIONSHIPS r
      WHERE oc.party_relationship_id = r.relationship_id
      AND r.object_id = d.party_id
      AND d.SEARCH_CONTEXT_ID=l_search_ctx_id;

Line: 2634


         INSERT INTO HZ_DQM_PARTIES_GT (SEARCH_CONTEXT_ID,PARTY_ID) VALUES (
             l_search_ctx_id,H_PARTY_ID(I));

Line: 2679


         INSERT INTO HZ_DQM_PARTIES_GT (SEARCH_CONTEXT_ID,PARTY_ID) VALUES (
             l_search_ctx_id,H_PARTY_ID(I));

Line: 2681


      INSERT INTO HZ_DQM_PARTIES_GT (SEARCH_CONTEXT_ID,PARTY_ID)
      SELECT distinct l_search_ctx_id,r.subject_id from HZ_DQM_PARTIES_GT d, HZ_ORG_CONTACTS oc, 
                               HZ_RELATIONSHIPS r
      WHERE oc.party_relationship_id = r.relationship_id
      AND r.object_id = d.party_id
      AND d.SEARCH_CONTEXT_ID=l_search_ctx_id;

Line: 2687


      H_PARTY_ID.DELETE;

Line: 2688


      H_PARTY_ID_LIST.DELETE;

Line: 2692


         INSERT INTO HZ_DQM_PARTIES_GT (SEARCH_CONTEXT_ID,PARTY_ID) VALUES (
             l_search_ctx_id,H_PARTY_ID(I));

Line: 2694


      INSERT INTO HZ_DQM_PARTIES_GT (SEARCH_CONTEXT_ID,PARTY_ID)
      SELECT distinct l_search_ctx_id,r.subject_id from HZ_DQM_PARTIES_GT d, HZ_ORG_CONTACTS oc, 
                               HZ_RELATIONSHIPS r
      WHERE oc.party_relationship_id = r.relationship_id
      AND r.object_id = d.party_id
      AND d.SEARCH_CONTEXT_ID=l_search_ctx_id;

Line: 2762


        INSERT INTO HZ_MATCHED_CPTS_GT (SEARCH_CONTEXT_ID,CONTACT_POINT_ID,PARTY_ID,SCORE) VALUES (
          l_search_ctx_id, h_cpt_id(I), h_cpt_party_id(I), h_cpt_score(I));

Line: 2836


    SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;

Line: 2884


            INSERT INTO HZ_MATCHED_PARTIES_GT (SEARCH_CONTEXT_ID, PARTY_ID, SCORE) 
            VALUES (l_search_ctx_id,H_SCORES(l_party_id).PARTY_ID,round((H_SCORES(l_party_id).TOTAL_SCORE/l_entered_max_score)*100));

Line: 2898


        INSERT INTO HZ_MATCHED_PARTY_SITES_GT (SEARCH_CONTEXT_ID,PARTY_SITE_ID,PARTY_ID,SCORE) VALUES (
          l_search_ctx_id, h_ps_id(I), h_ps_party_id(I), h_ps_score(I));

Line: 2904


        INSERT INTO HZ_MATCHED_CONTACTS_GT (SEARCH_CONTEXT_ID,ORG_CONTACT_ID,PARTY_ID,SCORE) VALUES (
          l_search_ctx_id, h_ct_id(I), h_ct_party_id(I), h_ct_score(I));

Line: 2910


        INSERT INTO HZ_MATCHED_CPTS_GT (SEARCH_CONTEXT_ID,CONTACT_POINT_ID,PARTY_ID,SCORE) VALUES (
          l_search_ctx_id, h_cpt_id(I), h_cpt_party_id(I), h_cpt_score(I));

Line: 2979


  DELETE FROM HZ_MATCHED_PARTY_SITES_GT ps WHERE SEARCH_CONTEXT_ID = x_search_ctx_id 
  AND NOT EXISTS 
       (SELECT 1 FROM HZ_MATCHED_PARTIES_GT p WHERE SEARCH_CONTEXT_ID = x_search_ctx_id AND p.PARTY_ID = ps.PARTY_ID);

Line: 2982


  DELETE FROM HZ_MATCHED_CONTACTS_GT ct WHERE SEARCH_CONTEXT_ID = x_search_ctx_id 
  AND NOT EXISTS 
       (SELECT 1 FROM HZ_MATCHED_PARTIES_GT p WHERE SEARCH_CONTEXT_ID = x_search_ctx_id AND p.PARTY_ID = ct.PARTY_ID);

Line: 2985


  DELETE FROM HZ_MATCHED_CPTS_GT cpt WHERE SEARCH_CONTEXT_ID = x_search_ctx_id 
  AND NOT EXISTS 
       (SELECT 1 FROM HZ_MATCHED_PARTIES_GT p WHERE SEARCH_CONTEXT_ID = x_search_ctx_id AND p.PARTY_ID = cpt.PARTY_ID);

Line: 3051


    SELECT HZ_MERGE_BATCH_S.nextval INTO x_dup_set_id FROM DUAL;

Line: 3058


    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) 
    VALUES (x_dup_set_id, p_dup_batch_id, p_party_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);

Line: 3066


    INSERT INTO HZ_DUP_SET_PARTIES (DUP_PARTY_ID,DUP_SET_ID,MERGE_SEQ_ID,
      MERGE_BATCH_ID,merge_flag,SCORE,CREATED_BY,CREATION_DATE,LAST_UPDATE_LOGIN,
      LAST_UPDATE_DATE,LAST_UPDATED_BY,DUP_SET_BATCH_ID) 
    VALUES (p_party_id,x_dup_set_id,0,0,
      'Y',100,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_dup_batch_id);

Line: 3113


   cursor get_cpts_for_party_sites is select contact_point_id  
                         from hz_contact_points 
                         where owner_table_name = 'HZ_PARTY_SITES' 
                         and primary_flag='Y'
                         and owner_table_id = p_party_site_id;

Line: 3201


 cursor get_cpt_for_contact_id is select  contact_point_id 
   from hz_org_contacts a, hz_relationships b, hz_contact_points c 
   where a.party_relationship_id = b.relationship_id 
     and c.owner_table_name = 'HZ_PARTIES' 
     and c.primary_flag='Y'
     and c.owner_table_id = b.party_id 
     and b.directional_flag = 'F'  
     and a.org_contact_id = p_org_contact_id;

Line: 3840


    SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;

Line: 3933


            INSERT INTO HZ_MATCHED_PARTY_SITES_GT (SEARCH_CONTEXT_ID, PARTY_ID, PARTY_SITE_ID, SCORE) 
            VALUES (l_search_ctx_id,H_SCORES(l_party_site_id).PARTY_ID, H_SCORES(l_party_site_id).PARTY_SITE_ID, (H_SCORES(l_party_site_id).TOTAL_SCORE/l_entered_max_score)*100);

Line: 3941


          INSERT INTO HZ_MATCHED_PARTY_SITES_GT (SEARCH_CONTEXT_ID, PARTY_ID, PARTY_SITE_ID, SCORE) 
          VALUES (l_search_ctx_id,H_SCORES(l_party_site_id).PARTY_ID, H_SCORES(l_party_site_id).PARTY_SITE_ID, round((H_SCORES(l_party_site_id).TOTAL_SCORE/l_entered_max_score)*100));

Line: 4066


    SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;

Line: 4156


            INSERT INTO HZ_MATCHED_CONTACTS_GT (SEARCH_CONTEXT_ID, PARTY_ID, ORG_CONTACT_ID, SCORE) 
            VALUES (l_search_ctx_id,H_SCORES(l_org_contact_id).PARTY_ID, H_SCORES(l_org_contact_id).ORG_CONTACT_ID, (H_SCORES(l_org_contact_id).TOTAL_SCORE/l_entered_max_score)*100);

Line: 4164


          INSERT INTO HZ_MATCHED_CONTACTS_GT (SEARCH_CONTEXT_ID, PARTY_ID, ORG_CONTACT_ID, SCORE) 
          VALUES (l_search_ctx_id,H_SCORES(l_org_contact_id).PARTY_ID, H_SCORES(l_org_contact_id).ORG_CONTACT_ID, round((H_SCORES(l_org_contact_id).TOTAL_SCORE/l_entered_max_score)*100));

Line: 4271


    SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;

Line: 4313


        INSERT INTO HZ_MATCHED_CPTS_GT(SEARCH_CONTEXT_ID,CONTACT_POINT_ID,PARTY_ID,SCORE) VALUES (
            l_search_ctx_id,H_CONTACT_POINT_ID(I),H_PARTY_ID(I),round(H_SCORE(I)/l_entered_max_score)*100);

Line: 4442


      SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;

Line: 4453


          INSERT_PARTY_SCORE(p_party_id, p_party_id, l_search_ctx_id, p_party_search_rec, g_party_stage_rec, l_TX2,l_TX59,l_TX41,l_TX45,1);

Line: 4479


        INSERT_PARTY_SITES_SCORE(p_party_id,l_max_id,l_search_ctx_id, p_party_site_list(l_max_idx), g_party_site_stage_list(l_max_idx) ,l_max_TX26,l_max_TX9,l_max_TX14,l_max_TX22,l_max_idx);

Line: 4504


        INSERT_CONTACTS_SCORE(p_party_id,l_max_id,l_search_ctx_id, p_contact_list(l_max_idx), g_contact_stage_list(l_max_idx) ,l_max_TX2,l_max_TX23,l_max_TX22,l_max_idx);

Line: 4529


        INSERT_CONTACT_POINTS_SCORE(p_party_id,l_max_id,l_search_ctx_id, p_contact_point_list(l_max_idx), g_contact_pt_stage_list(l_max_idx) ,l_max_TX5,l_max_TX7,l_max_TX10,l_max_TX158,l_max_idx);

Line: 4534


    DELETE FROM HZ_SRCH_PARTIES WHERE batch_id = -1 ;

Line: 4535


    DELETE FROM HZ_SRCH_PSITES WHERE batch_id = -1 ;

Line: 4536


    DELETE FROM HZ_SRCH_CONTACTS WHERE batch_id = -1 ;

Line: 4537


    DELETE FROM HZ_SRCH_CPTS WHERE batch_id = -1 ;

Line: 4544


    DELETE FROM HZ_SRCH_PARTIES WHERE batch_id = -1 ;

Line: 4545


    DELETE FROM HZ_SRCH_PSITES WHERE batch_id = -1 ;

Line: 4546


    DELETE FROM HZ_SRCH_CONTACTS WHERE batch_id = -1 ;

Line: 4547


    DELETE FROM HZ_SRCH_CPTS WHERE batch_id = -1 ;

Line: 4551


    DELETE FROM HZ_SRCH_PARTIES WHERE batch_id = -1 ;

Line: 4552


    DELETE FROM HZ_SRCH_PSITES WHERE batch_id = -1 ;

Line: 4553


    DELETE FROM HZ_SRCH_CONTACTS WHERE batch_id = -1 ;

Line: 4554


    DELETE FROM HZ_SRCH_CPTS WHERE batch_id = -1 ;

Line: 4558


    DELETE FROM HZ_SRCH_PARTIES WHERE batch_id = -1 ;

Line: 4559


    DELETE FROM HZ_SRCH_PSITES WHERE batch_id = -1 ;

Line: 4560


    DELETE FROM HZ_SRCH_CONTACTS WHERE batch_id = -1 ;

Line: 4561


    DELETE FROM HZ_SRCH_CPTS WHERE batch_id = -1 ;

Line: 4598


 SELECT party_site_id FROM (
     SELECT party_site_id,identifying_address_flag
      FROM HZ_PARTY_SITES
      WHERE party_id = p_party_id
      AND (status is null OR status = 'A') 
      AND identifying_address_flag='Y'
      UNION

     SELECT party_site_id,NVL(identifying_address_flag,'N') identifying_address_flag
      FROM HZ_PARTY_SITES
      WHERE party_id = p_party_id
      AND (status is null OR status = 'A') 
      AND (identifying_address_flag IS NULL OR identifying_address_flag = 'N')
      UNION

     SELECT party_site_id,NVL(identifying_address_flag,'N') identifying_address_flag
      FROM HZ_PARTY_SITES
      WHERE (status is null OR status = 'A') 
      AND party_id in (
        SELECT party_id
        FROM HZ_ORG_CONTACTS, HZ_RELATIONSHIPS
        WHERE HZ_RELATIONSHIPS.object_id = p_party_id
        AND HZ_RELATIONSHIPS.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
        AND HZ_RELATIONSHIPS.OBJECT_TABLE_NAME = 'HZ_PARTIES'
        AND HZ_ORG_CONTACTS.party_relationship_id = HZ_RELATIONSHIPS.relationship_id
        and l_use_contact_info = 'Y'
     ) 
) order by identifying_address_flag desc
    ) LOOP
      l_party_site_ids(ps) := PARTY_SITES.party_site_id;

Line: 4631


      SELECT org_contact_id
      FROM HZ_ORG_CONTACTS, HZ_RELATIONSHIPS
      WHERE HZ_RELATIONSHIPS.object_id = p_party_id
      AND HZ_RELATIONSHIPS.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
      AND HZ_RELATIONSHIPS.OBJECT_TABLE_NAME = 'HZ_PARTIES'
      AND HZ_RELATIONSHIPS.DIRECTIONAL_FLAG = 'F'
      AND HZ_ORG_CONTACTS.party_relationship_id = HZ_RELATIONSHIPS.relationship_id
    ) LOOP
      l_contact_ids(ct) := CONTACTS.org_contact_id;

Line: 4643


      SELECT CONTACT_POINT_ID
      FROM HZ_CONTACT_POINTS 
      WHERE PRIMARY_FLAG = 'Y'
      AND owner_table_name = 'HZ_PARTIES'
      AND owner_table_id = p_party_id

      UNION

      SELECT CONTACT_POINT_ID
      FROM HZ_CONTACT_POINTS,HZ_ORG_CONTACTS, HZ_RELATIONSHIPS 
      WHERE PRIMARY_FLAG = 'Y'
      AND owner_table_name = 'HZ_PARTIES'
      AND OWNER_TABLE_ID = HZ_RELATIONSHIPS.party_id
      AND HZ_RELATIONSHIPS.object_id = p_party_id
      AND HZ_RELATIONSHIPS.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
      AND HZ_RELATIONSHIPS.OBJECT_TABLE_NAME = 'HZ_PARTIES'
      AND HZ_ORG_CONTACTS.party_relationship_id = HZ_RELATIONSHIPS.relationship_id
        and l_use_contact_info = 'Y'
      UNION

      SELECT CONTACT_POINT_ID
      FROM HZ_CONTACT_POINTS,HZ_PARTY_SITES  
      WHERE PRIMARY_FLAG = 'Y'
      AND owner_table_name = 'HZ_PARTY_SITES'
      AND owner_table_id = party_site_id 
      AND PARTY_ID = p_party_id 
      AND IDENTIFYING_ADDRESS_FLAG = 'Y') LOOP
      l_contact_pt_ids(cpt) := CONTACT_POINTS.CONTACT_POINT_ID;

Line: 4756


    SELECT PARTY_TYPE INTO l_party_type
    FROM HZ_PARTIES
    WHERE PARTY_ID = p_party_id;

Line: 4764


      SELECT 
        translate(HZ_ORGANIZATION_PROFILES.DUNS_NUMBER_C, '%',' ')
       ,translate(HZ_ORGANIZATION_PROFILES.JGZZ_FISCAL_CODE, '%',' ')
       ,translate(HZ_PARTIES.PARTY_NAME, '%',' ')
       ,translate(HZ_PARTIES.PARTY_TYPE, '%',' ')
       ,translate(HZ_ORGANIZATION_PROFILES.SIC_CODE, '%',' ')
      INTO 
        x_party_search_rec.DUNS_NUMBER_C
       ,x_party_search_rec.JGZZ_FISCAL_CODE
       ,x_party_search_rec.PARTY_NAME
       ,x_party_search_rec.PARTY_TYPE
       ,x_party_search_rec.SIC_CODE
      FROM HZ_PARTIES, HZ_ORGANIZATION_PROFILES
      WHERE HZ_PARTIES.party_id = HZ_ORGANIZATION_PROFILES.party_id
      AND HZ_ORGANIZATION_PROFILES.effective_end_date is NULL
      AND HZ_PARTIES.party_id = p_party_id;

Line: 4781


      SELECT 
        translate(HZ_PERSON_PROFILES.JGZZ_FISCAL_CODE, '%',' ')
       ,translate(HZ_PARTIES.PARTY_NAME, '%',' ')
       ,translate(HZ_PARTIES.PARTY_TYPE, '%',' ')
      INTO 
        x_party_search_rec.JGZZ_FISCAL_CODE
       ,x_party_search_rec.PARTY_NAME
       ,x_party_search_rec.PARTY_TYPE
      FROM HZ_PARTIES, HZ_PERSON_PROFILES
      WHERE HZ_PARTIES.party_id = HZ_PERSON_PROFILES.party_id
      AND HZ_PERSON_PROFILES.effective_end_date is NULL
      AND HZ_PARTIES.party_id = p_party_id;

Line: 4819


    SELECT party_site_id
      ,translate(HZ_LOCATIONS.CITY, '%',' ')
      ,translate(HZ_LOCATIONS.COUNTRY, '%',' ')
      ,translate(HZ_LOCATIONS.POSTAL_CODE, '%',' ')
      ,translate(HZ_LOCATIONS.STATE, '%',' ')
    FROM HZ_PARTY_SITES, HZ_LOCATIONS
    WHERE HZ_PARTY_SITES.party_site_id = cp_party_site_id
    AND   HZ_PARTY_SITES.location_id = HZ_LOCATIONS.location_id;

Line: 4881


    SELECT org_contact_id
      ,translate(HZ_ORG_CONTACTS.JOB_TITLE, '%',' ')
    FROM HZ_ORG_CONTACTS, HZ_RELATIONSHIPS, HZ_PERSON_PROFILES
    WHERE HZ_ORG_CONTACTS.org_contact_id = cp_org_contact_id
    AND HZ_RELATIONSHIPS.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
    AND HZ_RELATIONSHIPS.OBJECT_TABLE_NAME = 'HZ_PARTIES'
    AND HZ_RELATIONSHIPS.DIRECTIONAL_FLAG = 'F'
    AND HZ_ORG_CONTACTS.party_relationship_id = HZ_RELATIONSHIPS.relationship_id
    AND HZ_RELATIONSHIPS.subject_id = HZ_PERSON_PROFILES.party_id
    AND HZ_PERSON_PROFILES.effective_end_date IS NULL;

Line: 4943


    SELECT contact_point_id, contact_point_type
      ,translate(HZ_CONTACT_POINTS.EMAIL_ADDRESS, '%',' ')
      ,translate(HZ_CONTACT_POINTS.URL, '%',' ')
    FROM HZ_CONTACT_POINTS
    WHERE contact_point_id = cp_contact_point_id;

Line: 5106


     insert into HZ_SRCH_PARTIES(batch_id,party_id, party_osr,party_os,TX41,TX45,TX59,TX2,TX36,TX46) values(-1,-1,-1,-1,x_stage_rec.TX41,x_stage_rec.TX45,x_stage_rec.TX59,x_stage_rec.TX2,x_stage_rec.TX36,x_stage_rec.TX46);

Line: 5258


               insert into HZ_SRCH_PSITES(batch_id,party_id, party_osr,party_os, party_site_id,party_site_osr, party_site_os,new_party_flag ,TX26,TX9,TX22,TX11,TX14) values(-1,-1,-1,-1,-1,-1,-1,'Y',x_stage_list(I).TX26,x_stage_list(I).TX9,x_stage_list(I).TX22,x_stage_list(I).TX11,x_stage_list(I).TX14);

Line: 5359


              insert into HZ_SRCH_CONTACTS(batch_id,party_id, party_osr,party_os, org_contact_id,contact_osr, contact_os,new_party_flag ,TX23,TX2,TX22) values(-1,-1,-1,-1,-1,-1,-1,'Y',x_stage_list(I).TX23,x_stage_list(I).TX2,x_stage_list(I).TX22);

Line: 5477


              insert into HZ_SRCH_CPTS(batch_id,party_id, party_osr,party_os, contact_point_id,contact_pt_osr, contact_pt_os,contact_point_type,new_party_flag ,TX5,TX158,TX10,TX7) values(-1,-1,-1,-1,-1,-1,-1,-1,'Y',x_stage_list(I).TX5,x_stage_list(I).TX158,x_stage_list(I).TX10,x_stage_list(I).TX7);

Line: 5733


    SELECT 1 FROM HZ_TRANS_FUNCTIONS_VL 
    WHERE nvl(STAGED_FLAG,'N') = 'N' 
    AND FUNCTION_ID in (
                6
                ,40
                ,45
                ,50
                ,51
                ,72
                ,74
                ,77
                ,85
                ,89
                ,109
                ,114
                ,116
                ,120
                ,128
                ,136
                ,138
                ,140
    );