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

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

Line: 368


      SELECT party_type INTO l_party_type 
      FROM HZ_PARTIES
      WHERE party_id = p_record_id;

Line: 372


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

Line: 377


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

Line: 386


      SELECT party_type INTO l_party_type 
      FROM HZ_PARTIES
      WHERE party_id = p_record_id;

Line: 390


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


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

Line: 405


      SELECT party_type INTO l_party_type 
      FROM HZ_PARTIES
      WHERE party_id = p_record_id;

Line: 409


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


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


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


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


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


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


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

Line: 515


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


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


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


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


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


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


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


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


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


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


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


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


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


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


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


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


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


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


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


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


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


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


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


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


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


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


      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: 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,'CONTACT_NAME',
           'CONTACTS', p_search_rec.CONTACT_NAME,
           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: 797


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


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


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


  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_TX7 VARCHAR2
      ,p_table_TX158 VARCHAR2
      ,p_table_TX10 VARCHAR2
        ,p_idx IN NUMBER) IS
    l_current_score NUMBER:=0;

Line: 826


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


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


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


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


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


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


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


    g_mappings.DELETE;

Line: 1028


    g_party_site_stage_list.DELETE;

Line: 1029


    g_contact_stage_list.DELETE;

Line: 1030


    g_contact_pt_stage_list.DELETE;

Line: 1031


    call_order.DELETE;

Line: 1032


    call_max_score.DELETE;

Line: 1119


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


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


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


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


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


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


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


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


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


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


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


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


          SELECT /*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N1) */ stage.CONTACT_POINT_ID, stage.PARTY_ID,stage.PARTY_SITE_ID, stage.ORG_CONTACT_ID , stage.TX7, stage.TX158, stage.TX10
          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.TX8 is not null and stage.TX8 like srch.TX8 || '%')
)
 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: 1461


          SELECT /*+ USE_NL(srch stage)  */  stage.CONTACT_POINT_ID, stage.PARTY_ID, stage.PARTY_SITE_ID, stage.ORG_CONTACT_ID , stage.TX7, stage.TX158, stage.TX10
          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.TX8 is not null and stage.TX8 like srch.TX8 || '%')
)
 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: 1485


            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.TX7, stage.TX158, stage.TX10
            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.TX8 is not null and stage.TX8 like srch.TX8 || '%')
)
 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: 1507


       l_sqlstr := 'SELECT /*+ USE_NL(srch stage)  */ stage.CONTACT_POINT_ID, stage.PARTY_ID, stage.PARTY_SITE_ID, stage.ORG_CONTACT_ID  , stage.TX7, stage.TX158, stage.TX10'||
                   ' 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.TX8 is not null and stage.TX8 like srch.TX8 || ''%'')'||
')'||
' 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: 1693


    H_PARTY_ID.DELETE;

Line: 1694


    H_PARTY_ID_LIST.DELETE;

Line: 1695


    H_SCORES.DELETE;

Line: 1696


    g_mappings.DELETE;

Line: 1713


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

Line: 1715


      H_PARTY_ID.DELETE;

Line: 1716


      H_PARTY_ID_LIST.DELETE;

Line: 1720


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

Line: 1779


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

Line: 1781


      H_PARTY_ID.DELETE;

Line: 1782


      H_PARTY_ID_LIST.DELETE;

Line: 1786


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

Line: 1856


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

Line: 1858


      H_PARTY_ID.DELETE;

Line: 1859


      H_PARTY_ID_LIST.DELETE;

Line: 1863


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

Line: 1931


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

Line: 1933


      H_PARTY_ID.DELETE;

Line: 1934


      H_PARTY_ID_LIST.DELETE;

Line: 1938


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

Line: 2063


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

Line: 2091


    SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;

Line: 2137


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


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


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


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


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


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


    SELECT party_type INTO l_party_type from hz_parties where party_id = p_party_id;

Line: 2337


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


    H_PARTY_ID.DELETE;

Line: 2356


    H_PARTY_ID_LIST.DELETE;

Line: 2357


    H_SCORES.DELETE;

Line: 2358


    g_mappings.DELETE;

Line: 2376


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

Line: 2378


      H_PARTY_ID.DELETE;

Line: 2379


      H_PARTY_ID_LIST.DELETE;

Line: 2383


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

Line: 2438


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


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

Line: 2476


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


      H_PARTY_ID.DELETE;

Line: 2483


      H_PARTY_ID_LIST.DELETE;

Line: 2487


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

Line: 2489


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


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

Line: 2611


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

Line: 2613


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


      H_PARTY_ID.DELETE;

Line: 2620


      H_PARTY_ID_LIST.DELETE;

Line: 2624


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

Line: 2626


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


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


    SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;

Line: 2816


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


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


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


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


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


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


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


    SELECT HZ_MERGE_BATCH_S.nextval INTO x_dup_set_id FROM DUAL;

Line: 2990


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


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


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


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


    SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;

Line: 3840


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


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


    SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;

Line: 4062


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


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


    SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;

Line: 4218


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


      SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;

Line: 4356


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


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


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


        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_TX7,l_max_TX158,l_max_TX10,l_max_idx);

Line: 4436


    DELETE FROM HZ_SRCH_PARTIES WHERE batch_id = -1 ;

Line: 4437


    DELETE FROM HZ_SRCH_PSITES WHERE batch_id = -1 ;

Line: 4438


    DELETE FROM HZ_SRCH_CONTACTS WHERE batch_id = -1 ;

Line: 4439


    DELETE FROM HZ_SRCH_CPTS WHERE batch_id = -1 ;

Line: 4446


    DELETE FROM HZ_SRCH_PARTIES WHERE batch_id = -1 ;

Line: 4447


    DELETE FROM HZ_SRCH_PSITES WHERE batch_id = -1 ;

Line: 4448


    DELETE FROM HZ_SRCH_CONTACTS WHERE batch_id = -1 ;

Line: 4449


    DELETE FROM HZ_SRCH_CPTS WHERE batch_id = -1 ;

Line: 4453


    DELETE FROM HZ_SRCH_PARTIES WHERE batch_id = -1 ;

Line: 4454


    DELETE FROM HZ_SRCH_PSITES WHERE batch_id = -1 ;

Line: 4455


    DELETE FROM HZ_SRCH_CONTACTS WHERE batch_id = -1 ;

Line: 4456


    DELETE FROM HZ_SRCH_CPTS WHERE batch_id = -1 ;

Line: 4460


    DELETE FROM HZ_SRCH_PARTIES WHERE batch_id = -1 ;

Line: 4461


    DELETE FROM HZ_SRCH_PSITES WHERE batch_id = -1 ;

Line: 4462


    DELETE FROM HZ_SRCH_CONTACTS WHERE batch_id = -1 ;

Line: 4463


    DELETE FROM HZ_SRCH_CPTS WHERE batch_id = -1 ;

Line: 4500


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


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


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


    SELECT PARTY_TYPE INTO l_party_type
    FROM HZ_PARTIES
    WHERE PARTY_ID = p_party_id;

Line: 4666


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


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


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


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


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


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


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


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


              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 ,TX158,TX10,TX8,TX7) values(-1,-1,-1,-1,-1,-1,-1,-1,'Y',x_stage_list(I).TX158,x_stage_list(I).TX10,x_stage_list(I).TX8,x_stage_list(I).TX7);

Line: 5628


    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
                ,116
                ,117
                ,120
                ,128
                ,136
                ,138
                ,140
    );