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


      SELECT party_type INTO l_party_type 
      FROM HZ_PARTIES
      WHERE party_id = p_record_id;

Line: 462


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

Line: 467


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

Line: 486


      SELECT party_type INTO l_party_type 
      FROM HZ_PARTIES
      WHERE party_id = p_record_id;

Line: 490


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

Line: 496


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

Line: 506


      SELECT party_type INTO l_party_type 
      FROM HZ_PARTIES
      WHERE party_id = p_record_id;

Line: 510


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

Line: 516


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

Line: 526


      SELECT party_type INTO l_party_type 
      FROM HZ_PARTIES
      WHERE party_id = p_record_id;

Line: 530


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


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


      SELECT party_type INTO l_party_type 
      FROM HZ_PARTIES
      WHERE party_id = p_record_id;

Line: 550


        SELECT SIC_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: 556


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

Line: 565


      SELECT party_type INTO l_party_type 
      FROM HZ_PARTIES
      WHERE party_id = p_record_id;

Line: 569


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

Line: 575


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

Line: 584


      SELECT party_type INTO l_party_type 
      FROM HZ_PARTIES
      WHERE party_id = p_record_id;

Line: 588


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

Line: 593


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

Line: 602


      SELECT party_type INTO l_party_type 
      FROM HZ_PARTIES
      WHERE party_id = p_record_id;

Line: 606


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

Line: 611


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

Line: 620


      SELECT party_type INTO l_party_type 
      FROM HZ_PARTIES
      WHERE party_id = p_record_id;

Line: 624


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

Line: 630


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

Line: 646


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


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


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


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


       SELECT HZ_LOCATIONS.COUNTY
       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: 711


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


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

Line: 750


  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_TX8 VARCHAR2
      ,p_table_TX32 VARCHAR2
      ,p_table_TX35 VARCHAR2
      ,p_table_TX42 VARCHAR2
      ,p_table_TX44 VARCHAR2
      ,p_table_TX45 VARCHAR2
      ,p_table_TX46 VARCHAR2
      ,p_table_TX47 VARCHAR2
      ,p_table_TX48 VARCHAR2
      ,p_table_TX156 VARCHAR2
      ,p_table_TX157 VARCHAR2
        ,p_idx IN NUMBER) IS
    l_current_score NUMBER:=0;

Line: 773


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


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


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


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


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


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

Line: 822


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


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


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


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


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


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

Line: 872


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


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


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

Line: 897


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


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


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


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


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


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

Line: 947


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


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


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

Line: 972


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


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


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

Line: 997


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


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


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

Line: 1022


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


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


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

Line: 1047


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


  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_TX11 VARCHAR2
      ,p_table_TX12 VARCHAR2
      ,p_table_TX14 VARCHAR2
      ,p_table_TX20 VARCHAR2
      ,p_table_TX22 VARCHAR2
        ,p_idx IN NUMBER) IS
    l_current_score NUMBER:=0;

Line: 1072


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


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


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


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


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


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


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


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


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


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


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


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


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


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


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


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


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


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

Line: 1227


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


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


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


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


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

Line: 1271


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


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


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


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


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

Line: 1320


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


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


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


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


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


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

Line: 1369


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


    g_mappings.DELETE;

Line: 1530


    g_party_site_stage_list.DELETE;

Line: 1531


    g_contact_stage_list.DELETE;

Line: 1532


    g_contact_pt_stage_list.DELETE;

Line: 1533


    call_order.DELETE;

Line: 1534


    call_max_score.DELETE;

Line: 2188


      SELECT PARTY_ID , TX8, TX32, TX35, TX42, TX44, TX45, TX46, TX47, TX48, TX156, TX157
      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: 2200


      l_sqlstr := 'SELECT PARTY_ID , TX8, TX32, TX35, TX42, TX44, TX45, TX46, TX47, TX48, TX156, TX157 FROM hz_staged_parties stage ';

Line: 2404


        SELECT PARTY_ID , TX8, TX32, TX35, TX42, TX44, TX45, TX46, TX47, TX48, TX156, TX157
        FROM HZ_STAGED_PARTIES stage
        WHERE PARTY_ID = p_dup_party_id;

Line: 2416


          SELECT /*+ INDEX(stage HZ_STAGE_PARTIES_T1) */ PARTY_ID , TX8, TX32, TX35, TX42, TX44, TX45, TX46, TX47, TX48, TX156, TX157
          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: 2429


            SELECT /*+ ORDERED INDEX(stage HZ_STAGED_PARTIES_U1) */ stage.PARTY_ID , TX8, TX32, TX35, TX42, TX44, TX45, TX46, TX47, TX48, TX156, TX157
            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: 2444


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

Line: 2447


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


       l_sqlstr := 'SELECT   ' || l_hint || ' PARTY_ID , TX8, TX32, TX35, TX42, TX44, TX45, TX46, TX47, TX48, TX156, TX157'||
                   ' 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: 2531


          SELECT /*+ INDEX(stage HZ_STAGED_PARTY_SITES_N1) */ PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID, TX3, TX4, TX9, TX11, TX12, TX14, TX20, 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: 2540


          SELECT /*+ INDEX(stage HZ_STAGED_PARTY_SITES_N1) */ PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID, TX3, TX4, TX9, TX11, TX12, TX14, TX20, 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, TX11, TX12, TX14, TX20, 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: 2570


          SELECT PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID, TX3, TX4, TX9, TX11, TX12, TX14, TX20, 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: 2590


            SELECT  PARTY_SITE_ID, stage.PARTY_ID, ORG_CONTACT_ID, TX3, TX4, TX9, TX11, TX12, TX14, TX20, 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, TX11, TX12, TX14, TX20, 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: 2613


            SELECT  PARTY_SITE_ID, stage.PARTY_ID, ORG_CONTACT_ID, TX3, TX4, TX9, TX11, TX12, TX14, TX20, 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: 2628


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

Line: 2629


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

Line: 2632


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


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


       l_sqlstr := 'SELECT  /*+ INDEX(stage HZ_STAGED_PARTY_SITES_N1) */ PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID , TX3, TX4, TX9, TX11, TX12, TX14, TX20, 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, TX11, TX12, TX14, TX20, 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: 2683


       l_sqlstr := 'SELECT PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID , TX3, TX4, TX9, TX11, TX12, TX14, TX20, 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: 2700


       l_sqlstr := 'SELECT PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID , TX3, TX4, TX9, TX11, TX12, TX14, TX20, 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: 2772


          SELECT /*+ INDEX(stage HZ_STAGED_CONTACTS_N1) */ ORG_CONTACT_ID, PARTY_ID, TX5, TX2
          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: 2795


          SELECT ORG_CONTACT_ID, PARTY_ID, TX5, TX2
          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: 2814


            SELECT /*+ ORDERED INDEX(stage HZ_STAGED_CONTACTS_N1) */ ORG_CONTACT_ID, stage.PARTY_ID, TX5, TX2
            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: 2828


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

Line: 2829


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

Line: 2832


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


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


       l_sqlstr := 'SELECT   ' || l_hint || ' ORG_CONTACT_ID, PARTY_ID , TX5, TX2'||
                   ' 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: 2921


          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, TX5
          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: 2933


          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, TX5
          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: 2950


          SELECT /*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N1) */ CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX5
          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: 2959


          SELECT /*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N1) */ CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX5
          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, TX5
          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: 2989


          SELECT CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX5
          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: 3009


            SELECT CONTACT_POINT_ID, stage.contact_point_type, stage.PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX5
            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, TX5
            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: 3032


            SELECT  CONTACT_POINT_ID, stage.contact_point_type, stage.PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX5
            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: 3047


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

Line: 3048


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

Line: 3051


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


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


       	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, TX5'||
                   ' 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, TX5'||
                   ' 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: 3102


       	l_sqlstr := 'SELECT   ' || l_hint ||' CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID  , TX1, TX5'||
                   ' 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: 3119


       	l_sqlstr := 'SELECT   ' || l_hint ||' CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID  , TX1, TX5'||
                   ' 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: 3173


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


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


    H_PARTY_ID.DELETE;

Line: 3358


    H_PARTY_ID_LIST.DELETE;

Line: 3359


    H_SCORES.DELETE;

Line: 3360


    g_mappings.DELETE;

Line: 3383


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


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

Line: 3388


      H_PARTY_ID.DELETE;

Line: 3389


      H_PARTY_ID_LIST.DELETE;

Line: 3392


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


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

Line: 3400


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


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


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

Line: 3524


      H_PARTY_ID.DELETE;

Line: 3525


      H_PARTY_ID_LIST.DELETE;

Line: 3528


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


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

Line: 3536


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


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


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


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


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

Line: 3636


      H_PARTY_ID.DELETE;

Line: 3637


      H_PARTY_ID_LIST.DELETE;

Line: 3640


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


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

Line: 3648


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


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


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


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


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

Line: 3747


      H_PARTY_ID.DELETE;

Line: 3748


      H_PARTY_ID_LIST.DELETE;

Line: 3751


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


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

Line: 3759


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


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


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


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

Line: 4123


    SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;

Line: 4176


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


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


    SELECT party_type INTO l_party_type from hz_parties where party_id = p_party_id;

Line: 4342


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


    H_PARTY_ID.DELETE;

Line: 4361


    H_PARTY_ID_LIST.DELETE;

Line: 4362


    H_SCORES.DELETE;

Line: 4363


    g_mappings.DELETE;

Line: 4382


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

Line: 4384


      H_PARTY_ID.DELETE;

Line: 4385


      H_PARTY_ID_LIST.DELETE;

Line: 4389


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

Line: 4393


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


          H_PARTY_ID.DELETE(l_party_id_idx-1);

Line: 4456


          H_PARTY_ID_LIST.DELETE(l_index);

Line: 4457


          H_SCORES.DELETE(l_index);

Line: 4488


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


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

Line: 4530


      H_PARTY_ID.DELETE;

Line: 4531


      H_PARTY_ID_LIST.DELETE;

Line: 4535


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

Line: 4607


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


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

Line: 4665


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


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

Line: 4695


      H_PARTY_ID.DELETE;

Line: 4696


      H_PARTY_ID_LIST.DELETE;

Line: 4699


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


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

Line: 4708


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


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


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


    SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;

Line: 5056


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


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


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


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


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

Line: 5221


    SELECT HZ_MERGE_BATCH_S.nextval INTO x_dup_set_id FROM DUAL;

Line: 5236


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


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


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


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


    SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;

Line: 6089


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

Line: 6148


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


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


    SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;

Line: 6319


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

Line: 6376


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


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


    SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;

Line: 6526


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


      SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;

Line: 6690


          INSERT_PARTY_SCORE(p_party_id, p_party_id, l_search_ctx_id, p_party_search_rec, g_party_stage_rec, l_TX8,l_TX32,l_TX35_new,l_TX42,l_TX44,l_TX45,l_TX46,l_TX47,l_TX48,l_TX156,l_TX157,1);

Line: 6724


        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_TX11,l_max_TX12,l_max_TX14,l_max_TX20,l_max_TX22,l_max_idx);

Line: 6748


        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_TX5,l_max_TX2,l_max_idx);

Line: 6775


        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_TX5,l_max_idx);

Line: 6824


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


      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
      AND ROWNUM<6 
    ) LOOP
      l_contact_ids(ct) := CONTACTS.org_contact_id;

Line: 6872


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


    SELECT PARTY_TYPE INTO l_party_type
    FROM HZ_PARTIES
    WHERE PARTY_ID = p_party_id;

Line: 6994


      SELECT 
        translate(HZ_PARTIES.CATEGORY_CODE, '%',' ')
       ,translate(HZ_ORGANIZATION_PROFILES.CORPORATION_CLASS, '%',' ')
       ,translate(HZ_ORGANIZATION_PROFILES.JGZZ_FISCAL_CODE, '%',' ')
       ,translate(HZ_PARTIES.PARTY_NAME, '%',' ')
       ,translate(HZ_PARTIES.PARTY_TYPE, '%',' ')
       ,translate(HZ_PARTIES.REFERENCE_USE_FLAG, '%',' ')
       ,translate(HZ_ORGANIZATION_PROFILES.SIC_CODE, '%',' ')
       ,translate(HZ_ORGANIZATION_PROFILES.SIC_CODE_TYPE, '%',' ')
       ,translate(HZ_ORGANIZATION_PROFILES.TAX_NAME, '%',' ')
       ,translate(HZ_ORGANIZATION_PROFILES.TAX_REFERENCE, '%',' ')
      INTO 
        x_party_search_rec.CATEGORY_CODE
       ,x_party_search_rec.CORPORATION_CLASS
       ,x_party_search_rec.JGZZ_FISCAL_CODE
       ,x_party_search_rec.PARTY_NAME
       ,x_party_search_rec.PARTY_TYPE
       ,x_party_search_rec.REFERENCE_USE_FLAG
       ,x_party_search_rec.SIC_CODE
       ,x_party_search_rec.SIC_CODE_TYPE
       ,x_party_search_rec.TAX_NAME
       ,x_party_search_rec.TAX_REFERENCE
      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: 7021


      SELECT 
        translate(HZ_PARTIES.CATEGORY_CODE, '%',' ')
       ,translate(HZ_PERSON_PROFILES.JGZZ_FISCAL_CODE, '%',' ')
       ,translate(HZ_PARTIES.PARTY_NAME, '%',' ')
       ,translate(HZ_PARTIES.PARTY_TYPE, '%',' ')
       ,translate(HZ_PARTIES.REFERENCE_USE_FLAG, '%',' ')
       ,translate(HZ_PERSON_PROFILES.TAX_NAME, '%',' ')
       ,translate(HZ_PERSON_PROFILES.TAX_REFERENCE, '%',' ')
      INTO 
        x_party_search_rec.CATEGORY_CODE
       ,x_party_search_rec.JGZZ_FISCAL_CODE
       ,x_party_search_rec.PARTY_NAME
       ,x_party_search_rec.PARTY_TYPE
       ,x_party_search_rec.REFERENCE_USE_FLAG
       ,x_party_search_rec.TAX_NAME
       ,x_party_search_rec.TAX_REFERENCE
      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: 7071


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


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


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

Line: 8196


    SELECT 1 FROM HZ_TRANS_FUNCTIONS_VL 
    WHERE nvl(STAGED_FLAG,'N') = 'N' 
    AND FUNCTION_ID in (
                12
                ,36
                ,39
                ,40
                ,46
                ,49
                ,50
                ,51
                ,52
                ,53
                ,66
                ,67
                ,72
                ,74
                ,75
                ,77
                ,83
                ,85
                ,89
                ,92
                ,110
                ,114
                ,121
                ,122
    );