APPS.HZ_MATCH_RULE_62 SQL Statements

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

Line: 130

    p_person_restrict_sql := 'exists ( SELECT 1 from HZ_ORG_CONTACTS oc, hz_relationships r' 
                               || ' where oc.org_contact_id = stage.org_contact_id and' 
                               || ' r.relationship_id = oc.party_relationship_id' 
                               || ' and r.subject_type = ''PERSON'' AND r.object_type = ''ORGANIZATION'' ' 
                               || ' and exists ( SELECT 1 FROM HZ_PARTIES stage1 where stage1.party_id = r.subject_id' 
                               || ' and ' || p_restrict1_sql || ' ) )' ; 

Line: 415

      SELECT party_type INTO l_party_type 
      FROM HZ_PARTIES
      WHERE party_id = p_record_id;

Line: 419

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

Line: 424

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

Line: 443

      SELECT party_type INTO l_party_type 
      FROM HZ_PARTIES
      WHERE party_id = p_record_id;

Line: 447

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

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

Line: 462

      SELECT party_type INTO l_party_type 
      FROM HZ_PARTIES
      WHERE party_id = p_record_id;

Line: 466

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

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

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

       SELECT HZ_LOCATIONS.POSTAL_CODE
       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: 515

       SELECT HZ_LOCATIONS.PROVINCE
       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: 528

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

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

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

Line: 576

  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_TX34 VARCHAR2
      ,p_table_TX35 VARCHAR2
      ,p_table_TX39 VARCHAR2
      ,p_table_TX40 VARCHAR2
      ,p_table_TX41 VARCHAR2
      ,p_table_TX45 VARCHAR2
        ,p_idx IN NUMBER) IS
    l_current_score NUMBER:=0;

Line: 594

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

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

      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_NUMBER',
           'PARTY', p_search_rec.PARTY_NUMBER,
           l_attrib_value,l_score);

Line: 618

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

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

      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,'ALL_ACCOUNT_NUMBERS',
           'PARTY', p_search_rec.ALL_ACCOUNT_NUMBERS,
           l_attrib_value,l_score);

Line: 643

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

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

      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_ALL_NAMES',
           'PARTY', p_search_rec.PARTY_ALL_NAMES,
           l_attrib_value,l_score);

Line: 674

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

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

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

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

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

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

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

  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_TX3 VARCHAR2
      ,p_table_TX4 VARCHAR2
      ,p_table_TX9 VARCHAR2
      ,p_table_TX10 VARCHAR2
      ,p_table_TX11 VARCHAR2
      ,p_table_TX12 VARCHAR2
      ,p_table_TX13 VARCHAR2
      ,p_table_TX14 VARCHAR2
      ,p_table_TX15 VARCHAR2
      ,p_table_TX22 VARCHAR2
        ,p_idx IN NUMBER) IS
    l_current_score NUMBER:=0;

Line: 751

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

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

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

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

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

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

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

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

      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,'POSTAL_CODE',
           'PARTY_SITES', p_search_rec.POSTAL_CODE,
           l_attrib_value,l_score);

Line: 837

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

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

      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,'PROVINCE',
           'PARTY_SITES', p_search_rec.PROVINCE,
           l_attrib_value,l_score);

Line: 868

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

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

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

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

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

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

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

  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_idx IN NUMBER) IS
    l_current_score NUMBER:=0;

Line: 941

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

  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_TX1 VARCHAR2
      ,p_table_TX8 VARCHAR2
      ,p_table_TX7 VARCHAR2
        ,p_idx IN NUMBER) IS
    l_current_score NUMBER:=0;

Line: 960

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

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

      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,'FLEX_FORMAT_PHONE_NUMBER',
           'CONTACT_POINTS', p_search_rec.FLEX_FORMAT_PHONE_NUMBER,
           l_attrib_value,l_score);

Line: 984

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

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

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

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

    g_mappings.DELETE;

Line: 1176

    g_party_site_stage_list.DELETE;

Line: 1177

    g_contact_stage_list.DELETE;

Line: 1178

    g_contact_pt_stage_list.DELETE;

Line: 1179

    call_order.DELETE;

Line: 1180

    call_max_score.DELETE;

Line: 1700

      SELECT PARTY_ID , TX34, TX35, TX39, TX40, lpad(rtrim(TX41),9,chr(48)), TX45
      FROM hz_staged_parties 
      WHERE TX8 LIKE g_party_stage_rec.TX8||' %'
      AND ((g_party_stage_rec.TX36 IS NULL OR g_party_stage_rec.TX36||' ' =  TX36))
      AND( (l_search_merged ='Y' ) 
           OR (l_search_merged = 'I' AND nvl(status, 'A') in ('A', 'I'))  
           OR (l_search_merged = 'N' AND nvl(status, 'A') in ('A')))  
      AND (p_dup_party_id IS NULL OR party_id <> p_dup_party_id);

Line: 1712

      l_sqlstr := 'SELECT PARTY_ID , TX34, TX35, TX39, TX40, lpad(rtrim(TX41),9,chr(48)), TX45 FROM hz_staged_parties stage ';

Line: 1867

        SELECT PARTY_ID , TX34, TX35, TX39, TX40, lpad(rtrim(TX41),9,chr(48)), TX45
        FROM HZ_STAGED_PARTIES stage
        WHERE PARTY_ID = p_dup_party_id;

Line: 1879

          SELECT /*+ INDEX(stage HZ_STAGE_PARTIES_T1) */ PARTY_ID , TX34, TX35, TX39, TX40, lpad(rtrim(TX41),9,chr(48)), TX45
          FROM HZ_STAGED_PARTIES stage
          WHERE contains( concat_col, p_contains_str)>0
          AND ((g_party_stage_rec.TX36 IS NULL OR g_party_stage_rec.TX36||' ' =  stage.TX36))
          AND( (l_search_merged ='Y' ) 
          OR (l_search_merged = 'I' AND nvl(stage.status, 'A') in ('A', 'I'))  
          OR (l_search_merged = 'N' AND nvl(stage.status, 'A') in ('A'))       ) 
          AND (p_dup_party_id IS NULL OR stage.party_id <> p_dup_party_id);

Line: 1892

            SELECT /*+ ORDERED INDEX(stage HZ_STAGED_PARTIES_U1) */ stage.PARTY_ID , TX34, TX35, TX39, TX40, lpad(rtrim(TX41),9,chr(48)), TX45
            FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_PARTIES stage
            WHERE contains( concat_col, p_contains_str)>0
            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 (p_dup_party_id IS NULL OR stage.party_id <> p_dup_party_id)
            AND( (l_search_merged ='Y' ) 
            OR (l_search_merged = 'I' AND nvl(stage.status, 'A') in ('A', 'I'))  
            OR (l_search_merged = 'N' AND nvl(stage.status, 'A') in ('A'))       ); 

Line: 1907

       l_check := instrb(p_restrict_sql, 'SELECTIVE'); 

Line: 1910

      hz_utility_v2pub.debug(p_message=>'Restrict sql has a Selective Hint',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);

Line: 1917

       l_sqlstr := 'SELECT   ' || l_hint || ' PARTY_ID , TX34, TX35, TX39, TX40, lpad(rtrim(TX41),9,chr(48)), TX45'||
                   ' FROM HZ_STAGED_PARTIES stage'||
                   ' WHERE contains( concat_col, :cont)>0'||
                   ' AND ((:TX36 IS NULL OR :TX36||'' '' =  stage.TX36))'||
                   ' AND ('||p_restrict_sql||')' ||
                   ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) '; 

Line: 1994

          SELECT /*+ INDEX(stage HZ_STAGED_PARTY_SITES_N1) */ PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID, TX3, TX4, TX9, TX10, TX11, TX12, TX13, TX14, TX15, TX22
          FROM HZ_STAGED_PARTY_SITES stage
          WHERE contains( concat_col, p_contains_str)>0
          AND( (l_search_merged ='Y' )  
             OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))  
             OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A')       )  
          AND stage.party_id = p_party_id; 

Line: 2003

          SELECT /*+ INDEX(stage HZ_STAGED_PARTY_SITES_N1) */ PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID, TX3, TX4, TX9, TX10, TX11, TX12, TX13, TX14, TX15, TX22
          FROM HZ_STAGED_PARTY_SITES stage
          WHERE contains( concat_col, p_contains_str)>0
          AND( (l_search_merged ='Y' )  
            OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))  
            OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A')       )  
          AND stage.party_id = p_party_id 
          UNION
          SELECT /*+ INDEX(stage HZ_STAGED_PARTY_SITES_N2) */ stage.PARTY_SITE_ID, stage.PARTY_ID, stage.ORG_CONTACT_ID, TX3, TX4, TX9, TX10, TX11, TX12, TX13, TX14, TX15, TX22
          FROM HZ_STAGED_PARTY_SITES stage, hz_relationships r, hz_org_contacts oc
          WHERE contains( concat_col, p_contains_str)>0
          AND( (l_search_merged ='Y' )  
            OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))  
            OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A')       )  
          AND r.object_id = p_party_id 
          AND r.subject_id = stage.party_id 
          AND r.SUBJECT_TABLE_NAME = 'HZ_PARTIES' 
          AND r.OBJECT_TABLE_NAME = 'HZ_PARTIES' 
          AND r.relationship_id = oc.party_relationship_id
          AND oc.org_contact_id = stage.org_contact_id; 

Line: 2033

          SELECT PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID, TX3, TX4, TX9, TX10, TX11, TX12, TX13, TX14, TX15, TX22
          FROM HZ_STAGED_PARTY_SITES stage
        WHERE contains( concat_col, p_contains_str)>0
        AND EXISTS (
          SELECT 1 FROM HZ_STAGED_PARTIES p
          WHERE p.PARTY_ID = stage.PARTY_ID
          AND( (l_search_merged ='Y' )  
          OR (l_search_merged = 'I' AND nvl(p.status, 'A') in ('A', 'I'))  
          OR (l_search_merged = 'N' AND nvl(p.status, 'A') in ('A'))       )  
          AND ((g_party_stage_rec.TX36 IS NULL OR g_party_stage_rec.TX36||' ' =  p.TX36)))
          AND( (l_search_merged ='Y' )  
           OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))  
           OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A')       )  
          AND (p_dup_party_id IS NULL OR stage.party_id <> p_dup_party_id);

Line: 2053

            SELECT  PARTY_SITE_ID, stage.PARTY_ID, ORG_CONTACT_ID, TX3, TX4, TX9, TX10, TX11, TX12, TX13, TX14, TX15, TX22
            FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_PARTY_SITES stage
            WHERE contains( concat_col, p_contains_str)>0
          AND( (l_search_merged ='Y' )  
          OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))  
          OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A')       )  
            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)
            UNION 
            SELECT /*+ INDEX(stage HZ_STAGED_PARTY_SITES_N2) */ stage.PARTY_SITE_ID, r.subject_id, stage.ORG_CONTACT_ID, TX3, TX4, TX9, TX10, TX11, TX12, TX13, TX14, TX15, TX22
            FROM HZ_DQM_PARTIES_GT d, hz_relationships r,hz_org_contacts oc, HZ_STAGED_PARTY_SITES stage
            WHERE contains( concat_col, p_contains_str)>0
          AND( (l_search_merged ='Y' )  
          OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))  
          OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A')       )  
            AND d.search_context_id = p_search_ctx_id
            AND d.party_id = r.subject_id
            AND r.relationship_id = oc.party_relationship_id
            AND oc.org_contact_id = stage.org_contact_id
            AND (p_dup_party_id IS NULL OR r.subject_id <> p_dup_party_id);

Line: 2076

            SELECT  PARTY_SITE_ID, stage.PARTY_ID, ORG_CONTACT_ID, TX3, TX4, TX9, TX10, TX11, TX12, TX13, TX14, TX15, TX22
            FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_PARTY_SITES stage
            WHERE contains( concat_col, p_contains_str)>0
            AND( (l_search_merged ='Y' )  
             OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))  
             OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A')       )  
            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: 2091

       l_check := instrb(p_restrict_sql, 'SELECTIVE'); 

Line: 2092

       l_check_dt := instrb(p_restrict_sql, 'SELECTIVE_PS'); 

Line: 2095

      hz_utility_v2pub.debug(p_message=>'Restrict sql has the selective_ps  hint',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);

Line: 2100

      hz_utility_v2pub.debug(p_message=>'Restrict sql has the selective hint',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);

Line: 2121

       l_sqlstr := 'SELECT  /*+ INDEX(stage HZ_STAGED_PARTY_SITES_N1) */ PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID , TX3, TX4, TX9, TX10, TX11, TX12, TX13, TX14, TX15, TX22'||
                   ' FROM HZ_STAGED_PARTY_SITES stage'||
                   ' WHERE contains( concat_col, :cont)>0'||
                   '  AND( ('''||l_search_merged||''' =''Y'' )  '|| 
                   '   OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I''))  '|| 
                   '   OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'')       )  '|| 
         ' AND (ORG_CONTACT_ID IS NULL ' ||
       	' AND ('||p_restrict_sql||'))' ||
       	' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ' ||
         ' UNION ' ||
       			 'SELECT /*+ INDEX(stage HZ_STAGED_PARTY_SITES_N2) */ PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID , TX3, TX4, TX9, TX10, TX11, TX12, TX13, TX14, TX15, TX22'||
                   ' FROM HZ_STAGED_PARTY_SITES stage'||
                   ' WHERE contains( concat_col, :cont)>0'||
                   '  AND( ('''||l_search_merged||''' =''Y'' )  '|| 
                   '   OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I''))  '|| 
                   '   OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'')       )  '|| 
         ' AND ORG_CONTACT_ID IN ' ||
         ' ( SELECT org_contact_id from HZ_ORG_CONTACTS oc, (select object_id, relationship_id, subject_id party_id from hz_relationships ' ||
         ' where subject_type = ''PERSON'' AND object_type = ''ORGANIZATION'') stage1 ' ||
         ' where stage1.relationship_id = oc.party_relationship_id ' || 
         ' and ('||p_restrict_sql1|| ') )' ||
         ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) '; 

Line: 2146

       l_sqlstr := 'SELECT PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID , TX3, TX4, TX9, TX10, TX11, TX12, TX13, TX14, TX15, TX22'||
                   ' FROM HZ_STAGED_PARTY_SITES stage'||
                   ' WHERE contains( concat_col, :cont)>0'||
                   '  AND( ('''||l_search_merged||''' =''Y'' )  '|| 
                   '   OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I''))  '|| 
                   '   OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'')       )  '|| 
                 ' AND EXISTS ('||
                 ' SELECT 1 FROM HZ_STAGED_PARTIES p ' || 
                 ' WHERE p.party_id = stage.party_id ' || 
                 ' AND ((:TX36 IS NULL OR :TX36||'' '' =  p.TX36))  '|| l_status_sql ||' ) ' || 
                   ' AND ('||get_adjusted_restrict_sql(p_restrict_sql)||')' ||
                   ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) '; 

Line: 2163

       l_sqlstr := 'SELECT PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID , TX3, TX4, TX9, TX10, TX11, TX12, TX13, TX14, TX15, TX22'||
                   ' FROM HZ_STAGED_PARTY_SITES stage'||
                   ' WHERE contains( concat_col, :cont)>0'||
                   '  AND( ('''||l_search_merged||''' =''Y'' )  '|| 
                   '   OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I''))  '|| 
                   '   OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'')       )  '|| 
                 ' AND EXISTS ('||
                 ' SELECT 1 FROM HZ_STAGED_PARTIES p ' || 
                 ' WHERE p.party_id = stage.party_id ' || 
                 ' AND ((:TX36 IS NULL OR :TX36||'' '' =  p.TX36))  '|| l_status_sql ||' ) ' || 
                   ' AND ('||p_restrict_sql||')' ||
                   ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) '; 

Line: 2235

          SELECT /*+ INDEX(stage HZ_STAGED_CONTACTS_N1) */ ORG_CONTACT_ID, PARTY_ID
          FROM HZ_STAGED_CONTACTS stage
        WHERE contains( concat_col, p_contains_str)>0
        AND EXISTS (
          SELECT 1 FROM HZ_STAGED_PARTIES p
          WHERE p.PARTY_ID = stage.PARTY_ID
          AND( (l_search_merged ='Y' )  
          OR (l_search_merged = 'I' AND nvl(p.status, 'A') in ('A', 'I'))  
          OR (l_search_merged = 'N' AND nvl(p.status, 'A') in ('A'))       )  
          AND ((g_party_stage_rec.TX36 IS NULL OR g_party_stage_rec.TX36||' ' =  p.TX36)))
          AND( (l_search_merged ='Y' )  
           OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))  
           OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A')       )  
          AND stage.party_id = p_party_id;

Line: 2258

          SELECT ORG_CONTACT_ID, PARTY_ID
          FROM HZ_STAGED_CONTACTS stage
        WHERE contains( concat_col, p_contains_str)>0
        AND EXISTS (
          SELECT 1 FROM HZ_STAGED_PARTIES p
          WHERE p.PARTY_ID = stage.PARTY_ID
          AND( (l_search_merged ='Y' )  
          OR (l_search_merged = 'I' AND nvl(p.status, 'A') in ('A', 'I'))  
          OR (l_search_merged = 'N' AND nvl(p.status, 'A') in ('A'))       )  
          AND ((g_party_stage_rec.TX36 IS NULL OR g_party_stage_rec.TX36||' ' =  p.TX36)))
          AND( (l_search_merged ='Y' )  
           OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))  
           OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A')       )  
          AND (p_dup_party_id IS NULL OR stage.party_id <> p_dup_party_id);

Line: 2277

            SELECT /*+ ORDERED INDEX(stage HZ_STAGED_CONTACTS_N1) */ ORG_CONTACT_ID, stage.PARTY_ID
            FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACTS stage
            WHERE contains( concat_col, p_contains_str)>0
            AND d.search_context_id = p_search_ctx_id
            AND d.party_id = stage.party_id
            AND( (l_search_merged ='Y' )  
             OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))  
             OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A')       )  
            AND (p_dup_party_id IS NULL OR stage.party_id <> p_dup_party_id);

Line: 2291

       l_check := instrb(p_restrict_sql, 'SELECTIVE'); 

Line: 2292

       l_check_dt := instrb(p_restrict_sql, 'SELECTIVE_CT'); 

Line: 2295

      hz_utility_v2pub.debug(p_message=>'Restrict sql has the selective_ct hint',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);

Line: 2300

      hz_utility_v2pub.debug(p_message=>'Restrict sql has the selective hint',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);

Line: 2311

       l_sqlstr := 'SELECT   ' || l_hint || ' ORG_CONTACT_ID, PARTY_ID '||
                   ' FROM HZ_STAGED_CONTACTS stage'||
                   ' WHERE contains( concat_col, :cont)>0'||
                 ' AND EXISTS ('||
                 ' SELECT 1 FROM HZ_STAGED_PARTIES p ' || 
                 ' WHERE p.party_id = stage.party_id ' || 
                 ' AND ((:TX36 IS NULL OR :TX36||'' '' =  p.TX36)) '|| l_status_sql ||' ) ' || 
                  ' AND( ('''||l_search_merged||''' =''Y'' )  '||
                  ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I''))  '||
                  ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'')       )  '||
                   ' AND ('||p_restrict_sql||')' ||
                   ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) '; 

Line: 2384

          SELECT /*+ USE_NL(d stage) ORDERED INDEX(stage HZ_STAGED_CONTACT_POINTS_N2) */ CONTACT_POINT_ID, stage.contact_point_type, stage.PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX8, TX7
          FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage 
          WHERE contains( concat_col, p_contains_str)>0 
          AND d.search_context_id = p_search_ctx_id 
          AND( (l_search_merged ='Y' )  
           OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))  
           OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A')       )  
          AND d.party_id = stage.org_contact_id ; 

Line: 2396

          SELECT /*+ USE_NL(d stage) ORDERED INDEX(stage HZ_STAGED_CONTACT_POINTS_N3) */ CONTACT_POINT_ID, stage.contact_point_type, stage.PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX8, TX7
          FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage 
          WHERE contains( concat_col, p_contains_str)>0 
          AND d.search_context_id = p_search_ctx_id 
          AND( (l_search_merged ='Y' )  
           OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))  
           OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A')       )  
          AND d.party_id = stage.party_site_id ; 

Line: 2413

          SELECT /*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N1) */ CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX8, TX7
          FROM HZ_STAGED_CONTACT_POINTS stage
          WHERE contains( concat_col, p_contains_str)>0
          AND( (l_search_merged ='Y' )  
           OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))  
           OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A')       )  
          AND stage.party_id = p_party_id; 

Line: 2422

          SELECT /*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N1) */ CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX8, TX7
          FROM HZ_STAGED_CONTACT_POINTS stage
          WHERE contains( concat_col, p_contains_str)>0
          AND( (l_search_merged ='Y' )  
           OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))  
           OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A')       )  
          AND stage.party_id = p_party_id 
          UNION
          SELECT /*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N2) */ stage.CONTACT_POINT_ID, stage.contact_point_type, stage.PARTY_ID, stage.PARTY_SITE_ID, stage.ORG_CONTACT_ID , TX1, TX8, TX7
          FROM HZ_STAGED_CONTACT_POINTS stage, hz_relationships r, hz_org_contacts oc
          WHERE contains( concat_col, p_contains_str)>0
          AND r.object_id = p_party_id 
          AND( (l_search_merged ='Y' )  
           OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))  
           OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A')       )  
                    AND r.subject_id = stage.party_id 
                    AND r.SUBJECT_TABLE_NAME = 'HZ_PARTIES' 
                    AND r.OBJECT_TABLE_NAME = 'HZ_PARTIES' 
                    AND r.relationship_id = oc.party_relationship_id
                    AND oc.org_contact_id = stage.org_contact_id; 

Line: 2452

          SELECT CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX8, TX7
          FROM HZ_STAGED_CONTACT_POINTS stage
        WHERE contains( concat_col, p_contains_str)>0
        AND EXISTS (
          SELECT 1 FROM HZ_STAGED_PARTIES p
          WHERE p.PARTY_ID = stage.PARTY_ID
          AND( (l_search_merged ='Y' )  
          OR (l_search_merged = 'I' AND nvl(p.status, 'A') in ('A', 'I'))  
          OR (l_search_merged = 'N' AND nvl(p.status, 'A') in ('A'))       )  
          AND ((g_party_stage_rec.TX36 IS NULL OR g_party_stage_rec.TX36||' ' =  p.TX36)))
          AND( (l_search_merged ='Y' )  
           OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))  
           OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A')       )  
          AND (p_dup_party_id IS NULL OR stage.party_id <> p_dup_party_id);

Line: 2472

            SELECT CONTACT_POINT_ID, stage.contact_point_type, stage.PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX8, TX7
            FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
            WHERE contains( concat_col, p_contains_str)>0
            AND d.search_context_id = p_search_ctx_id
            AND d.party_id = stage.party_id
            AND( (l_search_merged ='Y' )  
             OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))  
             OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A')       )  
            AND (p_dup_party_id IS NULL OR stage.party_id <> p_dup_party_id)
            UNION 
            SELECT /*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N2) */ CONTACT_POINT_ID, stage.contact_point_type, r.subject_id, stage.PARTY_SITE_ID, stage.ORG_CONTACT_ID , TX1, TX8, TX7
            FROM HZ_DQM_PARTIES_GT d, HZ_RELATIONSHIPS r, HZ_ORG_CONTACTS oc, HZ_STAGED_CONTACT_POINTS stage
            WHERE contains( concat_col, p_contains_str)>0
            AND d.search_context_id = p_search_ctx_id
            AND d.party_id = r.subject_id
            AND r.relationship_id = oc.party_relationship_id
            AND oc.org_contact_id = stage.org_contact_id
            AND( (l_search_merged ='Y' )  
             OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))  
             OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A')       )  
            AND (p_dup_party_id IS NULL OR r.subject_id <> p_dup_party_id);

Line: 2495

            SELECT  CONTACT_POINT_ID, stage.contact_point_type, stage.PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX8, TX7
            FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
            WHERE contains( concat_col, p_contains_str)>0
            AND d.search_context_id = p_search_ctx_id
            AND d.party_id = stage.party_id
            AND( (l_search_merged ='Y' )  
             OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))  
             OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A')       )  
            AND (p_dup_party_id IS NULL OR stage.party_id <> p_dup_party_id);

Line: 2510

       l_check := instrb(p_restrict_sql, 'SELECTIVE'); 

Line: 2511

       l_check_dt := instrb(p_restrict_sql, 'SELECTIVE_CPT'); 

Line: 2514

      hz_utility_v2pub.debug(p_message=>'Restrict Sql has the selective_cpt hint',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);

Line: 2519

      hz_utility_v2pub.debug(p_message=>'Restrict Sql has the selective hint',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);

Line: 2540

       	l_sqlstr := ' SELECT   /*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N1) */ CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID  , TX1, TX8, TX7'||
                   ' FROM HZ_STAGED_CONTACT_POINTS stage'||
                   ' WHERE contains( concat_col, :cont)>0  '||
                   ' AND (stage.org_contact_id is null '||
                   ' AND( ('''||l_search_merged||''' =''Y'' )  '||
                   '   OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I''))  '||
                   '   OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'')       )  '||
                   ' AND ('||p_restrict_sql||'))' ||
                   ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ' ||
                   ' UNION ' ||
                   ' SELECT   /*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N2) */ CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID  , TX1, TX8, TX7'||
                   ' FROM HZ_STAGED_CONTACT_POINTS stage'||
                   ' WHERE contains( concat_col, :cont)>0  '||
                   ' AND( ('''||l_search_merged||''' =''Y'' )  '||
                   '   OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I''))  '||
                   '   OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'')       )  '||
                 ' AND (stage.org_contact_id in ' || 
                 ' ( SELECT org_contact_id from HZ_ORG_CONTACTS oc, (select object_id, relationship_id, subject_id party_id from hz_relationships r ' ||
                 ' where subject_type = ''PERSON'' AND object_type = ''ORGANIZATION'') stage1 ' || 
                 ' where stage1.relationship_id = oc.party_relationship_id ' || 
                 ' and ('||p_restrict_sql1 || ') ) )' ||
                 ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) '; 

Line: 2565

       	l_sqlstr := 'SELECT   ' || l_hint ||' CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID  , TX1, TX8, TX7'||
                   ' FROM HZ_STAGED_CONTACT_POINTS stage'||
                   ' WHERE contains( concat_col, :cont)>0'||
                 ' AND EXISTS ('||
                 ' SELECT 1 FROM HZ_STAGED_PARTIES p ' || 
                 ' WHERE p.party_id = stage.party_id ' || 
                 ' AND ((:TX36 IS NULL OR :TX36||'' '' =  p.TX36))  '|| l_status_sql ||' ) ' || 
                   ' AND( ('''||l_search_merged||''' =''Y'' )  '||
                   '   OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I''))  '||
                   '   OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'')       )  '||
                   ' AND ('||get_adjusted_restrict_sql(p_restrict_sql)||')' ||
                   ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) '; 

Line: 2582

       	l_sqlstr := 'SELECT   ' || l_hint ||' CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID  , TX1, TX8, TX7'||
                   ' FROM HZ_STAGED_CONTACT_POINTS stage'||
                   ' WHERE contains( concat_col, :cont)>0'||
                 ' AND EXISTS ('||
                 ' SELECT 1 FROM HZ_STAGED_PARTIES p ' || 
                 ' WHERE p.party_id = stage.party_id ' || 
                 ' AND ((:TX36 IS NULL OR :TX36||'' '' =  p.TX36))  '|| l_status_sql ||' ) ' || 
                   ' AND( ('''||l_search_merged||''' =''Y'' )  '||
                   '   OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I''))  '||
                   '   OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'')       )  '||
                   ' AND ('||p_restrict_sql||')' ||
                   ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) '; 

Line: 2636

      INSERT INTO HZ_MATCHED_PARTIES_GT (SEARCH_CONTEXT_ID, PARTY_ID, SCORE) 
      SELECT p_search_ctx_id, PARTY_ID, decode(TX8,g_party_stage_rec.TX8||' ',100,90)
      FROM hz_staged_parties 
      WHERE TX8 LIKE g_party_stage_rec.TX8||' %'
      AND ((g_party_stage_rec.TX36 IS NULL OR g_party_stage_rec.TX36||' ' =  TX36))
      AND( (l_search_merged ='Y' ) 
           OR (l_search_merged = 'I' AND nvl(status, 'A') in ('A', 'I'))  
           OR (l_search_merged = 'N' AND nvl(status, 'A') in ('A')))  
      AND (p_dup_party_id IS NULL OR party_id <> p_dup_party_id)
      AND rownum <= p_thresh;

Line: 2647

      l_sqlstr := 'INSERT INTO HZ_MATCHED_PARTIES_GT (SEARCH_CONTEXT_ID, PARTY_ID, SCORE) SELECT :ctx_id, PARTY_ID, decode(TX8,:TX8||'' '',100,90) FROM hz_staged_parties stage ';

Line: 2817

    H_PARTY_ID.DELETE;

Line: 2818

    H_PARTY_ID_LIST.DELETE;

Line: 2819

    H_SCORES.DELETE;        

Line: 2820

    g_mappings.DELETE;

Line: 2843

      hz_utility_v2pub.debug(p_message=>'Match rule is AND and call type is AND. Inserting into HZ_DQM_PARTIES_GT, from the H_PARTY_ID list',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);

Line: 2846

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

Line: 2848

      H_PARTY_ID.DELETE;

Line: 2849

      H_PARTY_ID_LIST.DELETE;

Line: 2852

      hz_utility_v2pub.debug(p_message=>'Match rule is OR and call type is AND. Inserting into HZ_DQM_PARTIES_GT, from the H_PARTY_ID list',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);

Line: 2856

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

Line: 2860

      IF (p_restrict_sql IS NULL OR instrb(p_restrict_sql, 'SELECTIVE')=0)
         and check_estimate_hits('PARTY',p_party_contains_str)>l_max_thresh THEN
   IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
      hz_utility_v2pub.debug(p_message=>'In eval party level estimated hits exceed threshold',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);

Line: 2978

      hz_utility_v2pub.debug(p_message=>'Match rule is AND and call type is AND. Inserting into HZ_DQM_PARTIES_GT, from the H_PARTY_ID list',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);

Line: 2982

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

Line: 2984

      H_PARTY_ID.DELETE;

Line: 2985

      H_PARTY_ID_LIST.DELETE;

Line: 2988

      hz_utility_v2pub.debug(p_message=>'Match rule is OR and call type is AND. Inserting into HZ_DQM_PARTIES_GT, from the H_PARTY_ID list',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);

Line: 2992

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

Line: 2996

      IF (p_restrict_sql IS NULL OR instrb(p_restrict_sql, 'SELECTIVE')=0)
         and check_estimate_hits('PARTY_SITES',p_party_site_contains_str)>l_max_thresh THEN
   IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
      hz_utility_v2pub.debug(p_message=>'In eval party site level estimated hits exceed threshold',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);

Line: 3066

      hz_utility_v2pub.debug(p_message=>'In eval_party_site_level inserting into HZ_MATCHED_PARTY_SITES_GT from the H_PS_ID list',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);

Line: 3069

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

      hz_utility_v2pub.debug(p_message=>'Match rule is AND and call type is AND. Inserting into HZ_DQM_PARTIES_GT, from the H_PARTY_ID list',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);

Line: 3094

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

Line: 3096

      H_PARTY_ID.DELETE;

Line: 3097

      H_PARTY_ID_LIST.DELETE;

Line: 3100

      hz_utility_v2pub.debug(p_message=>'Match rule is OR and call type is AND. Inserting into HZ_DQM_PARTIES_GT, from the H_PARTY_ID list',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);

Line: 3104

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

Line: 3108

      IF (p_restrict_sql IS NULL OR instrb(p_restrict_sql, 'SELECTIVE')=0)
         and check_estimate_hits('CONTACTS',p_contact_contains_str)>l_max_thresh THEN
   IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
      hz_utility_v2pub.debug(p_message=>'In eval contact level estimated hits exceed threshold',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);

Line: 3175

      hz_utility_v2pub.debug(p_message=>'In eval_contact_level inserting into HZ_MATCHED_CONTACTS_GT from the H_CT_ID list',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);

Line: 3178

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

      hz_utility_v2pub.debug(p_message=>'Match rule is AND and call type is AND. Inserting into HZ_DQM_PARTIES_GT, from the H_PARTY_ID list',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);

Line: 3205

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

Line: 3207

      H_PARTY_ID.DELETE;

Line: 3208

      H_PARTY_ID_LIST.DELETE;

Line: 3211

      hz_utility_v2pub.debug(p_message=>'Match rule is OR and call type is AND. Inserting into HZ_DQM_PARTIES_GT, from the H_PARTY_ID list',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);

Line: 3215

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

Line: 3219

      IF (p_restrict_sql IS NULL OR instrb(p_restrict_sql, 'SELECTIVE')=0)
         and check_estimate_hits('CONTACT_POINTS',p_contact_pt_contains_str)>l_max_thresh THEN
   IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
      hz_utility_v2pub.debug(p_message=>'In eval contact point level estimated hits exceed threshold',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);

Line: 3400

      hz_utility_v2pub.debug(p_message=>'In eval_contact_point_level inserting into HZ_MATCHED_CPTS_GT from the H_CPT_ID list',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);

Line: 3403

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

    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 = 62; 

Line: 3583

    SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;

Line: 3636

      hz_utility_v2pub.debug(p_message=>'In find_parties inserting Search Rule results into HZ_MATCHED_PARTIES_GT',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);

Line: 3639

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

    SELECT party_type INTO l_party_type from hz_parties where party_id = p_party_id;

Line: 3799

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

    H_PARTY_ID.DELETE;

Line: 3818

    H_PARTY_ID_LIST.DELETE;

Line: 3819

    H_SCORES.DELETE;        

Line: 3820

    g_mappings.DELETE;

Line: 3839

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

Line: 3841

      H_PARTY_ID.DELETE;

Line: 3842

      H_PARTY_ID_LIST.DELETE;

Line: 3846

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

Line: 3850

      IF (p_restrict_sql IS NULL OR instrb(p_restrict_sql, 'SELECTIVE')=0)
         and check_estimate_hits('PARTY',p_party_contains_str)>l_max_thresh THEN
   IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
      hz_utility_v2pub.debug(p_message=>'In eval party level estimated hits exceed threshold',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);

Line: 3912

          H_PARTY_ID.DELETE(l_party_id_idx-1);

Line: 3913

          H_PARTY_ID_LIST.DELETE(l_index);

Line: 3914

          H_SCORES.DELETE(l_index);

Line: 3945

      SELECT /*+ INDEX(stage HZ_STAGED_CONTACTS_U1) */ ORG_CONTACT_ID, PARTY_ID
      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: 3985

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

Line: 3987

      H_PARTY_ID.DELETE;

Line: 3988

      H_PARTY_ID_LIST.DELETE;

Line: 3992

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

Line: 4064

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

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

Line: 4122

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

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

Line: 4152

      H_PARTY_ID.DELETE;

Line: 4153

      H_PARTY_ID_LIST.DELETE;

Line: 4156

      hz_utility_v2pub.debug(p_message=>'Match rule is AND and call type is AND. Inserting into HZ_DQM_PARTIES_GT, from the H_PARTY_ID list',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);

Line: 4160

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

Line: 4165

      hz_utility_v2pub.debug(p_message=>'Match rule is OR and call type is AND. Inserting into HZ_DQM_PARTIES_GT, from the H_PARTY_ID list',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);

Line: 4327

      hz_utility_v2pub.debug(p_message=>'Inserting into the final array, the person_id -  '||l_person_id,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);

Line: 4360

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

    SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;

Line: 4513

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

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

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

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

  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 = 62; 

Line: 4678

    SELECT HZ_MERGE_BATCH_S.nextval INTO x_dup_set_id FROM DUAL;

Line: 4693

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

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

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

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

    SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;

Line: 5486

           INSERT INTO HZ_DQM_PARTIES_GT (search_context_id, party_id)
           values (l_search_ctx_id,l_match_ps_list(I));

Line: 5545

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

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

    SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;

Line: 5716

           INSERT INTO HZ_DQM_PARTIES_GT (search_context_id, party_id)
           values (l_search_ctx_id,l_match_contact_list(I));

Line: 5773

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

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

    SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;

Line: 5924

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

      SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;

Line: 6082

          INSERT_PARTY_SCORE(p_party_id, p_party_id, l_search_ctx_id, p_party_search_rec, g_party_stage_rec, l_TX34,l_TX35_new,l_TX39,l_TX40,l_TX41,l_TX45,1);

Line: 6118

        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_TX3,l_max_TX4,l_max_TX9,l_max_TX10,l_max_TX11,l_max_TX12,l_max_TX13,l_max_TX14,l_max_TX15,l_max_TX22,l_max_idx);

Line: 6140

        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_idx);

Line: 6168

        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_TX1,l_max_TX8,l_max_TX7,l_max_idx);

Line: 6217

 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')
      AND ROWNUM<6
      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'
     ) 
     AND ROWNUM<6
) order by identifying_address_flag desc
    ) LOOP
      l_party_site_ids(ps) := PARTY_SITES.party_site_id;

Line: 6252

      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'
      AND ROWNUM<6
      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: 6360

    SELECT PARTY_TYPE INTO l_party_type
    FROM HZ_PARTIES
    WHERE PARTY_ID = p_party_id;

Line: 6368

      SELECT 
        translate(HZ_ORGANIZATION_PROFILES.DUNS_NUMBER_C, '%',' ')
       ,translate(HZ_ORGANIZATION_PROFILES.JGZZ_FISCAL_CODE, '%',' ')
       ,translate(HZ_PARTIES.PARTY_NUMBER, '%',' ')
      INTO 
        x_party_search_rec.DUNS_NUMBER_C
       ,x_party_search_rec.JGZZ_FISCAL_CODE
       ,x_party_search_rec.PARTY_NUMBER
      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: 6381

      SELECT 
        translate(HZ_PERSON_PROFILES.JGZZ_FISCAL_CODE, '%',' ')
       ,translate(HZ_PARTIES.PARTY_NUMBER, '%',' ')
      INTO 
        x_party_search_rec.JGZZ_FISCAL_CODE
       ,x_party_search_rec.PARTY_NUMBER
      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: 6421

    SELECT party_site_id
      ,translate(HZ_LOCATIONS.CITY, '%',' ')
      ,translate(HZ_LOCATIONS.COUNTRY, '%',' ')
      ,translate(HZ_LOCATIONS.POSTAL_CODE, '%',' ')
      ,translate(HZ_LOCATIONS.PROVINCE, '%',' ')
      ,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: 6485

    SELECT org_contact_id
    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: 6543

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

Line: 7449

    SELECT 1 FROM HZ_TRANS_FUNCTIONS_VL 
    WHERE nvl(STAGED_FLAG,'N') = 'N' 
    AND FUNCTION_ID in (
                38
                ,39
                ,43
                ,44
                ,45
                ,50
                ,66
                ,67
                ,72
                ,73
                ,74
                ,75
                ,76
                ,77
                ,78
                ,85
                ,110
                ,116
                ,117
    );