APPS.HZ_BATCH_ACTION_PUB SQL Statements

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

Line: 27

PROCEDURE update_int_tables ( p_batch_id                IN NUMBER,
                              p_tab_name                IN VARCHAR2,
                              p_orig_system             IN VARCHAR2,
                              p_orig_system_osr   IN VARCHAR2,
                              p_dup_os_val              IN VARCHAR2,
                              p_dup_osr_val             IN VARCHAR2,
                              p_action_on_entity        IN VARCHAR2
                            );

Line: 210

 SELECT dup_record_os, dup_record_osr
 FROM     HZ_IMP_INT_DEDUP_RESULTS
 WHERE batch_id = p_batch_id
 AND entity = p_entity ;

Line: 216

 select distinct winner_record_os, winner_record_osr
 from hz_imp_int_dedup_results
 where batch_id = p_batch_id
 and entity = p_entity
 group by winner_record_os,winner_record_osr;

Line: 224

 SELECT DUP_RECORD_OS, DUP_RECORD_OSR
 FROM   HZ_IMP_INT_DEDUP_RESULTS
 WHERE  batch_id  = p_batch_id
 AND  entity  =  p_entity
 AND  nvl(dup_last_update_date,sysdate) = (  SELECT MAX( nvl(DUP_LAST_UPDATE_DATE,sysdate))
 			     FROM  HZ_IMP_INT_DEDUP_RESULTS
 			     WHERE   batch_id =p_batch_id
                             and entity = p_entity
                             AND winner_record_os = p_winner_record_os
                             AND winner_record_osr = p_winner_record_osr   )
AND winner_record_os = p_winner_record_os
AND winner_record_osr = p_winner_record_osr
AND rownum =1;

Line: 240

 SELECT DUP_RECORD_OS, DUP_RECORD_OSR
 FROM   HZ_IMP_INT_DEDUP_RESULTS
 WHERE  batch_id  = p_batch_id
 AND  entity  =  p_entity
 AND  nvl(dup_creation_date,sysdate) = (  SELECT MAX( nvl(DUP_CREATION_DATE,sysdate))
                             FROM  HZ_IMP_INT_DEDUP_RESULTS
                             WHERE   batch_id =p_batch_id
                             and entity = p_entity
                             AND winner_record_os = p_winner_record_os
                             AND winner_record_osr = p_winner_record_osr   )
AND winner_record_os = p_winner_record_os
AND winner_record_osr = p_winner_record_osr
AND rownum =1;

Line: 256

 SELECT DUP_RECORD_OS, DUP_RECORD_OSR
 FROM   HZ_IMP_INT_DEDUP_RESULTS
 WHERE  batch_id  = p_batch_id
 AND  entity  =  p_entity
 AND  nvl(dup_creation_date,sysdate) = (  SELECT MIN( nvl(DUP_CREATION_DATE,sysdate))
                             FROM  HZ_IMP_INT_DEDUP_RESULTS
                             WHERE   batch_id =p_batch_id
                             and entity = p_entity
                             AND winner_record_os = p_winner_record_os
                             AND winner_record_osr = p_winner_record_osr   )
AND winner_record_os = p_winner_record_os
AND winner_record_osr = p_winner_record_osr
AND rownum =1;

Line: 273

 SELECT party_orig_system, party_orig_system_reference
 FROM    HZ_IMP_PARTIES_INT
 WHERE   batch_id = p_batch_id
 AND     interface_status = 'R'
 ;

Line: 281

 SELECT site_orig_system, site_orig_system_reference
 FROM    HZ_IMP_ADDRESSES_INT
 WHERE   batch_id = p_batch_id
 AND     interface_status = 'R';

Line: 288

 SELECT contact_orig_system, contact_orig_system_reference
 FROM    HZ_IMP_CONTACTS_INT
 WHERE   batch_id = p_batch_id
 AND     interface_status = 'R';

Line: 306

	hz_utility_v2pub.debug(p_message=>'update interface tables for action=remove_all (+)',
	                       p_prefix=>l_debug_prefix,
			       p_msg_level=>fnd_log.level_procedure);

Line: 316

          UPDATE HZ_IMP_PARTIES_INT
          SET interface_status = 'R'
          WHERE batch_id = p_batch_id
	   AND( party_orig_system, party_orig_system_reference ) in
                ( select dup_record_os, dup_record_osr
                  FROM HZ_IMP_INT_DEDUP_RESULTS result
                  WHERE result.batch_id = p_batch_id
		  --AND   result.dup_record_os = result.winner_record_os --Bug3339642.
		  --AND   result.dup_record_osr <> result.winner_record_osr --Bug3339642.
                  AND result.entity = 'PARTY');

Line: 329

           UPDATE HZ_IMP_ADDRESSES_INT
           SET interface_status = 'R'
           WHERE batch_id = p_batch_id
           AND( site_orig_system, site_orig_system_reference ) in
                ( select dup_record_os, dup_record_osr
                  FROM HZ_IMP_INT_DEDUP_RESULTS result
                  WHERE result.batch_id = p_batch_id
		  --AND   result.dup_record_os = result.winner_record_os --Bug3339642.
		  --AND   result.dup_record_osr <> result.winner_record_osr --Bug3339642.
                  AND result.entity = 'PARTY_SITES');

Line: 342

           UPDATE HZ_IMP_CONTACTS_INT
           SET interface_status = 'R'
           WHERE batch_id = p_batch_id
	   AND( contact_orig_system,contact_orig_system_reference) in
                ( select dup_record_os, dup_record_osr
                  FROM HZ_IMP_INT_DEDUP_RESULTS result
                  WHERE result.batch_id = p_batch_id
		  --AND   result.dup_record_os = result.winner_record_os    --Bug3339642.
		  --AND   result.dup_record_osr <> result.winner_record_osr --Bug3339642.
                  AND result.entity = 'CONTACTS');

Line: 355

           UPDATE HZ_IMP_CONTACTPTS_INT
           SET interface_status = 'R'
           WHERE batch_id = p_batch_id
	   AND(cp_orig_system ,cp_orig_system_reference) in
                ( select dup_record_os, dup_record_osr
                  FROM HZ_IMP_INT_DEDUP_RESULTS result
                  WHERE result.batch_id = p_batch_id
		  --AND   result.dup_record_os = result.winner_record_os --Bug3339642.
		  --AND   result.dup_record_osr <> result.winner_record_osr --Bug3339642.
                  AND result.entity = 'CONTACT_POINTS');

Line: 390

        IF p_action_on_entity = 'KEEP_LATEST_UPDATED' THEN
--Select the record with max dup_last_update_date

             OPEN max_last_date(p_batch_id, p_entity, l_winner_record_os(i), l_winner_record_osr(i));

Line: 418

                UPDATE HZ_IMP_INT_DEDUP_RESULTS
                SET WINNER_RECORD_OS = l_rec_os ,WINNER_RECORD_OSR = l_rec_osr
                WHERE batch_id = p_batch_id
                AND entity = p_entity
                AND WINNER_RECORD_OS = l_winner_record_os(i)
                AND WINNER_RECORD_OSR = l_winner_record_osr(i) ;

Line: 429

                UPDATE HZ_IMP_PARTIES_INT party
                SET INTERFACE_STATUS = 'R'
                WHERE batch_id = p_batch_id
                AND( party_orig_system, party_orig_system_reference ) in
                ( select dup_record_os, dup_record_osr
                  FROM HZ_IMP_INT_DEDUP_RESULTS result
                  WHERE result.batch_id = p_batch_id
                  AND result.entity = 'PARTY'
                  AND result.dup_record_osr <> l_rec_osr
                  AND WINNER_RECORD_OS = l_rec_os
                  AND WINNER_RECORD_OSR = l_rec_osr );

Line: 442

                UPDATE HZ_IMP_ADDRESSES_INT
                SET INTERFACE_STATUS = 'R'
                WHERE batch_id = p_batch_id
                AND( site_orig_system, site_orig_system_reference ) in
                   ( select dup_record_os, dup_record_osr
                     FROM HZ_IMP_INT_DEDUP_RESULTS result
                     WHERE result.batch_id = p_batch_id
                     AND result.entity = 'PARTY_SITES'
                     AND result.dup_record_osr <> l_rec_osr
                     AND WINNER_RECORD_OS = l_rec_os
                     AND WINNER_RECORD_OSR = l_rec_osr ) ;

Line: 455

                UPDATE HZ_IMP_CONTACTS_INT
                SET interface_status = 'R'
                WHERE batch_id = p_batch_id
                AND ( contact_orig_system,contact_orig_system_reference ) in
                    ( select dup_record_os, dup_record_osr
                      FROM HZ_IMP_INT_DEDUP_RESULTS result
                      WHERE result.batch_id = p_batch_id
                      AND result.entity = 'CONTACTS'
                      AND result.dup_record_osr <> l_rec_osr
                      AND WINNER_RECORD_OS = l_rec_os
                      AND WINNER_RECORD_OSR = l_rec_osr ) ;

Line: 468

                 UPDATE HZ_IMP_CONTACTPTS_INT
                 SET interface_status = 'R'
                 WHERE batch_id = p_batch_id
                 AND ( cp_orig_system, cp_orig_system_reference ) in
                    ( select dup_record_os, dup_record_osr
                      FROM HZ_IMP_INT_DEDUP_RESULTS result
                      WHERE result.batch_id = p_batch_id
                      AND result.entity = 'CONTACT_POINTS'
                      AND result.dup_record_osr <> l_rec_osr
                      AND WINNER_RECORD_OS = l_rec_os
                      AND WINNER_RECORD_OSR = l_rec_osr ) ;

Line: 522

               UPDATE HZ_IMP_ADDRESSES_INT
               SET interface_status = 'R'
               WHERE batch_id = p_batch_id
               AND party_orig_system = l_record_os(i)
               AND party_orig_system_reference = l_record_osr(i);

Line: 530

               UPDATE HZ_IMP_ADDRESSUSES_INT
               SET interface_status = 'R'
               WHERE batch_id = p_batch_id
               AND party_orig_system = l_record_os(i)
               AND party_orig_system_reference = l_record_osr(i);

Line: 539

               UPDATE HZ_IMP_CONTACTS_INT
               SET interface_status = 'R'
               WHERE batch_id = p_batch_id
               AND ((sub_orig_system = l_record_os(i)
                    AND sub_orig_system_reference = l_record_osr(i))
		    OR
		    (obj_orig_system = l_record_os(i)
		     AND obj_orig_system_reference = l_record_osr(i))
		   );

Line: 552

               UPDATE HZ_IMP_CONTACTPTS_INT
               SET interface_status = 'R'
               WHERE batch_id = p_batch_id
               AND party_orig_system = l_record_os(i)
               AND party_orig_system_reference = l_record_osr(i);

Line: 560

               UPDATE HZ_IMP_RELSHIPS_INT
               SET interface_status = 'R'
               WHERE batch_id = p_batch_id
               AND ((sub_orig_system = l_record_os(i)
                    AND sub_orig_system_reference = l_record_osr(i))
		    OR
		    (obj_orig_system = l_record_os(i)
		     AND obj_orig_system_reference = l_record_osr(i))
		   );

Line: 574

               UPDATE HZ_IMP_CLASSIFICS_INT
               SET interface_status = 'R'
               WHERE batch_id = p_batch_id
               AND party_orig_system = l_record_os(i)
               AND party_orig_system_reference = l_record_osr(i);

Line: 582

               UPDATE HZ_IMP_CREDITRTNGS_INT
               SET interface_status = 'R'
               WHERE batch_id = p_batch_id
               AND party_orig_system = l_record_os(i)
               AND party_orig_system_reference = l_record_osr(i);

Line: 590

               UPDATE HZ_IMP_FINNUMBERS_INT
               SET interface_status = 'R'
               WHERE batch_id = p_batch_id
               AND party_orig_system = l_record_os(i)
               AND party_orig_system_reference = l_record_osr(i);

Line: 598

               UPDATE HZ_IMP_FINREPORTS_INT
               SET interface_status = 'R'
               WHERE batch_id = p_batch_id
               AND party_orig_system = l_record_os(i)
               AND party_orig_system_reference = l_record_osr(i);

Line: 638

              UPDATE HZ_IMP_ADDRESSUSES_INT
	      SET interface_status = 'R'
              WHERE batch_id = p_batch_id
              AND site_orig_system = l_record_os(i)
              AND site_orig_system_reference = l_record_osr(i);

Line: 678

              UPDATE HZ_IMP_CONTACTROLES_INT
              SET interface_status = 'R'
              WHERE batch_id = p_batch_id
              AND contact_orig_system = l_record_os(i)
              AND contact_orig_system_reference = l_record_osr(i);

Line: 701

 * PRIVATE PROCEDURE update_int_tables
 *
 * DESCRIPTION
 *     private procedure to update the interface_status of
 *     interface tables.
 *
 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
 *
 *   ARGUMENTS
 *   IN:
 *    p_batch_id         Interface Batch ID.
 *    p_tab_name         Interface table name.
 *    p_orig_system      Orig System of the duplicate record in
 *                       Interface table.
 *    p_orig_system_reference Orig system reference of the duplicate
 *                            record in Interface table.
 *    p_dup_os_val       Orig System Value
 *    p_dup_osr_val      Orig system reference Value.
 *    p_action_on_entity Action on entities.
 *
 * MODIFICATION HISTORY
 *
 *   08-17-2003    Rajeshwari P       o Created.
 *
 */
/* Commented out for bug 4673725. Also removed the lines that caused
   SQL literal problem to avoid false positive.
PROCEDURE update_int_tables (
    p_batch_id          IN NUMBER,
    p_tab_name          IN VARCHAR2,
    p_orig_system       IN VARCHAR2,
    p_orig_system_osr   IN VARCHAR2,
    p_dup_os_val             IN VARCHAR2,
    p_dup_osr_val            IN VARCHAR2,
    p_action_on_entity       IN VARCHAR2
 ) IS
 l_debug_prefix		       VARCHAR2(30) := '';

Line: 745

	hz_utility_v2pub.debug(p_message=>'update interface tables (+)',
	                       p_prefix=>l_debug_prefix,
			       p_msg_level=>fnd_log.level_procedure);

Line: 752

	hz_utility_v2pub.debug(p_message=>'update interface tables (-)',
	                       p_prefix=>l_debug_prefix,
			       p_msg_level=>fnd_log.level_procedure);

Line: 764

END update_int_tables;

Line: 771

 *     private procedure to update the interface tables with
 *     appropriate actions after DQM has performed registry
 *     de-duplication.
 *
 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
 *
 *   ARGUMENTS
 *   IN:
 *    p_batch_id                Interface Batch ID.
 *    p_action_new_parties      Action on new parties.
 *                              Insert - Default
 *                              Remove - remove from parties interface table
 *                                       and all its child entities.
 *    p_action__existing_parties Action on existing parties.
 *                               Update - Default
 *                               Remove - remove from parties interface table
 *                                       and all its child entities.
 *    p_action_dup_parties      Action on duplicate parties.
 *                              Auto Merge - Default
 *                              Request Merge
 *                              Insert
 *                              Remove
 *   p_action_pot_dup_parties   Action on potential duplicates.
 *                              Request Merge - Default
 *                              Insert
 *                              Remove
 *
 *    OUT:
 *     x_return_status         Return status after the call.
 *
 * MODIFICATION HISTORY
 *
 *   08-25-2003    Rajeshwari P       o Created.
 *
 */

 PROCEDURE reg_action_on_party( p_batch_id                 IN NUMBER,
                               p_action_new_parties       IN VARCHAR2,
                               p_action_existing_parties  IN VARCHAR2,
                               p_action_dup_parties       IN VARCHAR2,
                               p_action_pot_dup_parties   IN VARCHAR2,
                               x_return_status            OUT NOCOPY VARCHAR2
                             ) IS

 new_party_sql                  VARCHAR2(4000);

Line: 829

    new_party_sql := 'select int.party_id,int.party_orig_system,int.party_orig_system_reference '||
                 'from hz_imp_parties_int int, hz_imp_parties_sg stage '||
                 'where int.batch_id = :p_batch_id ' ||
                 'and int.batch_id = stage.batch_id '||
                 'and int.rowid = stage.int_row_id '||
                 'and int.dqm_action_flag IS NULL '||
                 'AND stage.action_flag = ''I'' ';

Line: 837

    existing_party_sql := 'select int.party_id,int.party_orig_system,int.party_orig_system_reference '||
                      'from hz_imp_parties_int int, hz_imp_parties_sg stage '||
                      'where int.batch_id = :p_batch_id '||
                      'and int.batch_id = stage.batch_id '||
                      'and int.rowid = stage.int_row_id '||
                      'AND stage.action_flag = ''U'' ';

Line: 844

    dup_party_sql   := 'select int.party_id,int.party_orig_system,int.party_orig_system_reference '||
                   'from hz_imp_parties_int int, hz_imp_parties_sg stage '||
                   'where int.batch_id = :p_batch_id '||
                   'and int.batch_id = stage.batch_id '||
                   'and int.rowid = stage.int_row_id '||
                   'and int.dqm_action_flag = ''D'' ' ||
                   'AND stage.action_flag = ''I'' ';

Line: 852

    pot_dup_party_sql  := 'select int.party_id,int.party_orig_system,int.party_orig_system_reference '||
                      'from hz_imp_parties_int int, hz_imp_parties_sg stage '||
                      'where int.batch_id = :p_batch_id '||
                      'and int.batch_id = stage.batch_id '||
                      'and int.rowid = stage.int_row_id '||
                      'and int.dqm_action_flag = ''P'' '||
                      'AND stage.action_flag = ''I'' ';

Line: 886

 *     private procedure to update the interface tables with
 *     appropriate actions after DQM has performed registry
 *     de-duplication.
 *
 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
 *
 *   ARGUMENTS
 *   IN:
 *   p_batch_id                Interface Batch ID.
 *   p_action_new_addrs      Action on new sites.
 *                              Insert - Default
 *                              Remove - remove from Address interface table
 *                                       and all its child entities.
 *   p_action__existing_addrs Action on existing sites.
 *                               Update - Default
 *                               Remove - remove from parties interface table
 *                                       and all its child entities.
 *   p_action_pot_dup_addrs   Action on potential duplicates.
 *                              Request Merge - Default
 *                              Insert
 *                              Remove
 *
 *    OUT:
 *     x_return_status         Return status after the call.
 *
 * MODIFICATION HISTORY
 *
 *   08-25-2003    Rajeshwari P       o Created.
 *
 */

 PROCEDURE reg_action_on_sites ( p_batch_id                 IN NUMBER,
                               p_action_new_addrs       IN VARCHAR2,
                               p_action_existing_addrs  IN VARCHAR2,
                               p_action_pot_dup_addrs   IN VARCHAR2,
                               x_return_status            OUT NOCOPY VARCHAR2
                             ) IS

  new_site_sql                  VARCHAR2(4000);

Line: 936

    new_site_sql := 'select int.site_orig_system,int.site_orig_system_reference '||
                 'from hz_imp_addresses_int int, hz_imp_addresses_sg stage '||
                 'where int.batch_id = :p_batch_id ' ||
                 'and int.batch_id = stage.batch_id '||
                 'and int.rowid = stage.int_row_id '||
                 'and int.dqm_action_flag IS NULL '||
                 'AND stage.action_flag = ''I'' ';

Line: 944

    existing_site_sql := 'select int.site_orig_system,int.site_orig_system_reference '||
                      'from hz_imp_addresses_int int, hz_imp_addresses_sg stage '||
                      'where int.batch_id = :p_batch_id '||
                      'and int.batch_id = stage.batch_id '||
                      'and int.rowid = stage.int_row_id '||
                      'AND stage.action_flag = ''U'' ';

Line: 951

    pot_dup_site_sql  := 'select int.site_orig_system,int.site_orig_system_reference '||
                      'from hz_imp_addresses_int int, hz_imp_addresses_sg stage '||
                      'where int.batch_id = :p_batch_id '||
                      'and int.batch_id = stage.batch_id '||
                      'and int.rowid = stage.int_row_id '||
                      'and int.dqm_action_flag = ''P'' '||
                      'AND stage.action_flag = ''I'' ';

Line: 980

 *     private procedure to update the interface tables with
 *     appropriate actions after DQM has performed registry
 *     de-duplication.
 *
 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
 *
 *   ARGUMENTS
 *   IN:
 *   p_batch_id                Interface Batch ID.
 *   p_action_new_contacts      Action on new contacts.
 *                              Insert - Default
 *                              Remove - remove from Contact interface table
 *                                       and all its child entities.
 *   p_action__existing_contacts Action on existing contacts.
 *                               Update - Default
 *                               Remove - remove from parties interface table
 *                                       and all its child entities.
 *   p_action_pot_dup_contacts   Action on potential duplicates.
 *                              Request Merge - Default
 *                              Insert
 *                              Remove
 *
 *    OUT:
 *     x_return_status         Return status after the call.
 *
 * MODIFICATION HISTORY
 *
 *   08-25-2003    Rajeshwari P       o Created.
 *
 */

  PROCEDURE reg_action_on_cont ( p_batch_id            IN NUMBER,
                               p_action_new_contacts       IN VARCHAR2,
                               p_action_existing_contacts  IN VARCHAR2,
                               p_action_pot_dup_contacts   IN VARCHAR2,
                               x_return_status         OUT NOCOPY VARCHAR2
                             ) IS

  new_cont_sql                  VARCHAR2(4000);

Line: 1030

    new_cont_sql := 'select int.contact_orig_system,int.contact_orig_system_reference '||
                 'from hz_imp_contacts_int int, hz_imp_contacts_sg stage '||
                 'where int.batch_id = :p_batch_id ' ||
                 'and int.batch_id = stage.batch_id '||
                 'and int.rowid= stage.int_row_id '||
                 'and int.dqm_action_flag IS NULL '||
                 'AND stage.action_flag = ''I'' ';

Line: 1038

    existing_cont_sql := 'select int.contact_orig_system,int.contact_orig_system_reference '||
                      'from hz_imp_contacts_int int, hz_imp_contacts_sg stage '||
                      'where int.batch_id = :p_batch_id '||
                      'and int.batch_id = stage.batch_id '||
                      'and int.rowid= stage.int_row_id '||
                      'AND stage.action_flag = ''U'' ';

Line: 1045

     pot_dup_cont_sql  := 'select int.contact_orig_system,int.contact_orig_system_reference '||
                      'from hz_imp_contacts_int int, hz_imp_contacts_sg stage '||
                      'where int.batch_id = :p_batch_id '||
                      'and int.batch_id = stage.batch_id '||
                      'and int.rowid= stage.int_row_id '||
                      'and int.dqm_action_flag = ''P'' '||
                      'AND stage.action_flag = ''I'' ';

Line: 1075

 *     private procedure to update the interface tables with
 *     appropriate actions after DQM has performed registry
 *     de-duplication.
 *
 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
 *
 *   ARGUMENTS
 *   IN:
 *   p_batch_id                Interface Batch ID.
 *   p_action_new_cpts      Action on new contacts.
 *                              Insert - Default
 *                              Remove - remove from Contact interface table
 *                                       and all its child entities.
 *   p_action__existing_cpts Action on existing contacts.
 *                               Update - Default
 *                               Remove - remove from parties interface table
 *                                       and all its child entities.
 *   p_action_pot_dup_cpts   Action on potential duplicates.
 *                              Request Merge - Default
 *                              Insert
 *                              Remove
 *
 *    OUT:
 *     x_return_status         Return status after the call.
 *
 * MODIFICATION HISTORY
 *
 *   08-25-2003    Rajeshwari P       o Created.
 *
 */
PROCEDURE reg_action_on_cpts ( p_batch_id                  IN NUMBER,
                               p_action_new_cpts       IN VARCHAR2,
                               p_action_existing_cpts  IN VARCHAR2,
                               p_action_pot_dup_cpts   IN VARCHAR2,
                               x_return_status             OUT NOCOPY VARCHAR2
                             ) IS

  new_cpts_sql                  VARCHAR2(4000);

Line: 1124

    new_cpts_sql := 'select int.cp_orig_system,int.cp_orig_system_reference '||
                 'from hz_imp_contactpts_int int, hz_imp_contactpts_sg stage '||
                 'where int.batch_id = :p_batch_id ' ||
                 'and int.batch_id = stage.batch_id '||
                 'and int.rowid = stage.int_row_id '||
                 'and int.dqm_action_flag IS NULL '||
                 'AND stage.action_flag = ''I'' ';

Line: 1132

    existing_cpts_sql := 'select int.cp_orig_system,int.cp_orig_system_reference '||
                      'from hz_imp_contactpts_int int, hz_imp_contactpts_sg stage '||
                      'where int.batch_id = :p_batch_id '||
                      'and int.batch_id = stage.batch_id '||
                      'and int.rowid = stage.int_row_id '||
                      'AND stage.action_flag = ''U'' ';

Line: 1139

    pot_dup_cpts_sql  := 'select int.cp_orig_system,int.cp_orig_system_reference '||
                      'from hz_imp_contactpts_int int, hz_imp_contactpts_sg stage '||
                      'where int.batch_id = :p_batch_id '||
                      'and int.batch_id = stage.batch_id '||
                      'and int.rowid = stage.int_row_id '||
                      'and int.dqm_action_flag = ''P'' '||
                      'AND stage.action_flag = ''I'' ';

Line: 1168

 *     private procedure to update the interface tables with
 *     appropriate actions after DQM has performed registry
 *     de-duplication.
 *
 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
 *
 *   ARGUMENTS
 *   IN:
 *
 *
 *   OUT:
 *     x_return_status         Return status after the call.
 *
 * MODIFICATION HISTORY
 *
 *   08-26-2003    Rajeshwari P       o Created.
 *
 */

 PROCEDURE reg_action_on_supents (x_return_status OUT NOCOPY VARCHAR2) is

TYPE sel_cur           IS REF CURSOR;

Line: 1218

      sel_use := 'SELECT int_row_id FROM HZ_IMP_ADDRESSUSES_SG WHERE action_flag = ''I'' ';

Line: 1220

      sel_use := 'SELECT int_row_id FROM HZ_IMP_ADDRESSUSES_SG WHERE action_flag = ''U'' ';

Line: 1238

    UPDATE HZ_IMP_ADDRESSUSES_INT
    SET interface_status = 'R'
    WHERE rowid = l_int_rowid(i) ;

Line: 1264

      sel_class := 'SELECT int_row_id FROM HZ_IMP_CLASSIFICS_SG WHERE action_flag = ''I'' ';

Line: 1266

      sel_class := 'SELECT int_row_id FROM HZ_IMP_CLASSIFICS_SG WHERE action_flag = ''U'' ';

Line: 1284

    UPDATE HZ_IMP_CLASSIFICS_INT
    SET interface_status = 'R'
    WHERE rowid = l_int_rowid(i) ;

Line: 1311

      sel_roles := 'SELECT int_row_id FROM HZ_IMP_CONTACTROLES_SG WHERE action_flag = ''I'' ';

Line: 1313

      sel_roles := 'SELECT int_row_id FROM HZ_IMP_CONTACTROLES_SG WHERE action_flag = ''U'' ';

Line: 1331

    UPDATE HZ_IMP_CONTACTROLES_INT
    SET interface_status = 'R'
    WHERE rowid = l_int_rowid(i) ;

Line: 1357

      sel_rel := 'SELECT int_row_id FROM HZ_IMP_RELSHIPS_SG WHERE action_flag = ''I'' ';

Line: 1359

      sel_rel := 'SELECT int_row_id FROM HZ_IMP_RELSHIPS_SG WHERE action_flag = ''U'' ';

Line: 1377

    UPDATE HZ_IMP_RELSHIPS_INT
    SET interface_status = 'R'
    WHERE rowid = l_int_rowid(i) ;

Line: 1404

 *     private procedure to update the interface tables with
 *     appropriate actions after DQM has performed registry
 *     de-duplication.
 *
 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
 *
 *   ARGUMENTS
 *   IN:
 *
 *
 *   OUT:
 *     x_return_status         Return status after the call.
 *
 * MODIFICATION HISTORY
 *
 *   08-25-2003    Rajeshwari P       o Created.
 *
 */

PROCEDURE reg_action_on_finents (
                                  x_return_status OUT NOCOPY VARCHAR2
                                ) is

  TYPE sel_cur           IS REF CURSOR;

Line: 1455

      sel_finreports := 'SELECT int_row_id FROM HZ_IMP_FINREPORTS_SG WHERE action_flag = ''I'' ';

Line: 1457

      sel_finreports := 'SELECT int_row_id FROM HZ_IMP_FINREPORTS_SG WHERE action_flag = ''U'' ';

Line: 1475

    UPDATE HZ_IMP_FINREPORTS_INT
    SET interface_status = 'R'
    WHERE rowid = l_int_rowid(i) ;

Line: 1500

      sel_finnumbers := 'SELECT int_row_id FROM HZ_IMP_FINNUMBERS_SG WHERE action_flag = ''I'' ';

Line: 1502

      sel_finnumbers := 'SELECT int_row_id FROM HZ_IMP_FINNUMBERS_SG WHERE action_flag = ''U'' ';

Line: 1520

    UPDATE HZ_IMP_FINNUMBERS_INT
    SET interface_status = 'R'
    WHERE rowid = l_int_rowid(i) ;

Line: 1547

      sel_credit := 'SELECT int_row_id FROM HZ_IMP_CREDITRTNGS_SG WHERE action_flag = ''I'' ';

Line: 1549

      sel_credit := 'SELECT int_row_id FROM HZ_IMP_CREDITRTNGS_SG WHERE action_flag = ''U'' ';

Line: 1567

    UPDATE HZ_IMP_CREDITRTNGS_INT
    SET interface_status = 'R'
    WHERE rowid = l_int_rowid(i) ;

Line: 1620

  Cursor select_party_rec(p_batch_id IN NUMBER ,p_entity IN VARCHAR2 ) is
	select dup_record_os,dup_record_osr
	FROM HZ_IMP_INT_DEDUP_RESULTS
	WHERE batch_id = p_batch_id
	AND ENTITY = p_entity
	UNION
	SELECT party_osr,party_os
	FROM HZ_IMP_DUP_PARTIES
	WHERE batch_id= p_batch_id
          ;

Line: 1631

   Cursor select_detail_rec(p_batch_id IN NUMBER ,p_entity IN VARCHAR2 ) is
	select dup_record_os,dup_record_osr
	FROM HZ_IMP_INT_DEDUP_RESULTS
	WHERE batch_id = p_batch_id
	AND ENTITY = p_entity
	UNION
	SELECT record_os,record_osr
	FROM HZ_IMP_DUP_DETAILS
	WHERE batch_id= p_batch_id
	AND entity= p_entity
         ;

Line: 1682

        Open select_party_rec( p_batch_id , 'PARTY' ) ;

Line: 1685

        Fetch select_party_rec BULK COLLECT into
              l_dup_os_party,l_dup_osr_party
        LIMIT commit_counter;

Line: 1689

        IF select_party_rec%NOTFOUND THEN
           l_last_fetch := TRUE;

Line: 1699

         UPDATE HZ_IMP_PARTIES_INT
         SET interface_status = NULL,dqm_action_flag = NULL
         WHERE batch_id = p_batch_id
         AND party_orig_system = l_dup_os_party(i)
         AND party_orig_system_reference = l_dup_osr_party(i)
         AND ( interface_status = 'R' OR dqm_action_flag is not NULL );

Line: 1708

         UPDATE HZ_IMP_CLASSIFICS_INT
         SET interface_status = NULL
         WHERE batch_id = p_batch_id
         AND party_orig_system = l_dup_os_party(i)
         AND party_orig_system_reference = l_dup_osr_party(i)
         AND interface_status = 'R' ;

Line: 1717

         UPDATE HZ_IMP_CREDITRTNGS_INT
         SET interface_status = NULL
         WHERE batch_id = p_batch_id
         AND party_orig_system = l_dup_os_party(i)
         AND party_orig_system_reference = l_dup_osr_party(i)
         AND interface_status = 'R' ;

Line: 1726

         UPDATE HZ_IMP_FINNUMBERS_INT
         SET interface_status = NULL
         WHERE batch_id = p_batch_id
         AND party_orig_system = l_dup_os_party(i)
         AND party_orig_system_reference = l_dup_osr_party(i)
         AND interface_status = 'R' ;

Line: 1735

         UPDATE HZ_IMP_FINREPORTS_INT
         SET interface_status = NULL
         WHERE batch_id = p_batch_id
         AND party_orig_system = l_dup_os_party(i)
         AND party_orig_system_reference = l_dup_osr_party(i)
         AND interface_status = 'R' ;

Line: 1748

        close select_party_rec;

Line: 1756

          Open select_detail_rec ( p_batch_id ,'PARTY_SITES' );

Line: 1759

          FETCH select_detail_rec BULK COLLECT INTO
                l_dup_os_sites,l_dup_osr_sites
          LIMIT commit_counter;

Line: 1763

          IF select_detail_rec%NOTFOUND THEN
             l_last_fetch := TRUE;

Line: 1772

          UPDATE HZ_IMP_ADDRESSES_INT
          SET interface_status = NULL,dqm_action_flag = NULL
          WHERE batch_id = p_batch_id
          AND site_orig_system = l_dup_os_sites(i)
          AND site_orig_system_reference = l_dup_osr_sites(i)
          AND ( interface_status = 'R' OR dqm_action_flag is not NULL );

Line: 1782

          UPDATE HZ_IMP_ADDRESSUSES_INT
          SET interface_status = NULL
          WHERE batch_id = p_batch_id
          AND site_orig_system = l_dup_os_sites(i)
          AND site_orig_system_reference = l_dup_osr_sites(i)
          AND interface_status = 'R' ;

Line: 1795

          close select_detail_rec;

Line: 1803

          Open select_detail_rec ( p_batch_id ,'CONTACTS' );

Line: 1806

          FETCH select_detail_rec BULK COLLECT INTO
                l_dup_os_cont,l_dup_osr_cont
          LIMIT commit_counter;

Line: 1810

          IF select_detail_rec%NOTFOUND THEN
             l_last_fetch := TRUE;

Line: 1819

          UPDATE HZ_IMP_CONTACTS_INT
          SET interface_status = NULL,dqm_action_flag = NULL
          WHERE batch_id = p_batch_id
          AND contact_orig_system = l_dup_os_cont(i)
          AND contact_orig_system_reference = l_dup_osr_cont(i)
          AND ( interface_status = 'R' OR dqm_action_flag is not NULL );

Line: 1828

          UPDATE HZ_IMP_CONTACTROLES_INT
          SET interface_status = NULL
          WHERE batch_id = p_batch_id
          AND contact_orig_system = l_dup_os_cont(i)
          AND contact_orig_system_reference = l_dup_osr_cont(i)
          AND interface_status = 'R' ;

Line: 1841

          close select_detail_rec;

Line: 1850

          Open select_detail_rec ( p_batch_id ,'CONTACT_POINTS' );

Line: 1853

          FETCH select_detail_rec BULK COLLECT INTO
                l_dup_os_cp,l_dup_osr_cp
          LIMIT commit_counter;

Line: 1857

          IF select_detail_rec%NOTFOUND THEN
             l_last_fetch := TRUE;

Line: 1866

          UPDATE HZ_IMP_CONTACTPTS_INT
          SET interface_status = NULL,dqm_action_flag = NULL
          WHERE batch_id = p_batch_id
          AND cp_orig_system = l_dup_os_cp(i)
          AND cp_orig_system_reference = l_dup_osr_cp(i)
          AND ( interface_status = 'R' OR dqm_action_flag is not NULL );

Line: 1879

          close select_detail_rec;

Line: 1938

       SELECT  BD_ACTION_ON_PARTIES,BD_ACTION_ON_ADDRESSES,
               BD_ACTION_ON_CONTACTS,BD_ACTION_ON_CONTACT_POINTS
       FROM hz_imp_batch_summary
       WHERE batch_id = p_batch_id;

Line: 1958

	  hz_utility_v2pub.debug(p_message=>'update the interface tables after batch deduplication (+) ',
	                       p_prefix=>l_debug_prefix,
			       p_msg_level=>fnd_log.level_procedure);

Line: 1976

        UPDATE   HZ_IMP_BATCH_SUMMARY
        SET BD_ACTION_ON_PARTIES = p_action_on_parties
        WHERE batch_id = p_batch_id;

Line: 1986

         UPDATE   HZ_IMP_BATCH_SUMMARY
         SET BD_ACTION_ON_ADDRESSES = p_action_on_addresses
         WHERE batch_id = p_batch_id;

Line: 1996

         UPDATE   HZ_IMP_BATCH_SUMMARY
         SET BD_ACTION_ON_CONTACTS = p_action_on_contacts
         WHERE batch_id = p_batch_id;

Line: 2006

         UPDATE   HZ_IMP_BATCH_SUMMARY
         SET BD_ACTION_ON_CONTACT_POINTS = p_action_on_contact_points
         WHERE batch_id = p_batch_id;

Line: 2096

	 hz_utility_v2pub.debug(p_message=>'update the interface tables after batch deduplication (+) ',
	                       p_prefix=>l_debug_prefix,
			       p_msg_level=>fnd_log.level_procedure);

Line: 2199

 SELECT DUP_RECORD_OS, DUP_RECORD_OSR
 FROM   HZ_IMP_INT_DEDUP_RESULTS
 WHERE  batch_id  = p_batch_id
 AND  entity  =  p_entity
 AND  nvl(dup_last_update_date,sysdate) = (  SELECT MAX( nvl(DUP_LAST_UPDATE_DATE,sysdate))
					     FROM  HZ_IMP_INT_DEDUP_RESULTS
					     WHERE   batch_id =p_batch_id
					     and entity = p_entity
					     AND winner_record_os = p_winner_record_os
					     AND winner_record_osr = p_winner_record_osr)
AND winner_record_os = p_winner_record_os
AND winner_record_osr = p_winner_record_osr
AND rownum =1;

Line: 2215

 SELECT DUP_RECORD_OS, DUP_RECORD_OSR
 FROM   HZ_IMP_INT_DEDUP_RESULTS
 WHERE  batch_id  = p_batch_id
 AND  entity  =  p_entity
 AND  nvl(dup_creation_date,sysdate) = (  SELECT MAX( nvl(DUP_CREATION_DATE,sysdate))
					  FROM  HZ_IMP_INT_DEDUP_RESULTS
					  WHERE   batch_id =p_batch_id
					  AND entity = p_entity
					  AND winner_record_os = p_winner_record_os
					  AND winner_record_osr = p_winner_record_osr)
AND winner_record_os = p_winner_record_os
AND winner_record_osr = p_winner_record_osr
AND rownum =1;

Line: 2231

 SELECT DUP_RECORD_OS, DUP_RECORD_OSR
 FROM   HZ_IMP_INT_DEDUP_RESULTS
 WHERE  batch_id  = p_batch_id
 AND  entity  =  p_entity
 AND  nvl(dup_creation_date,sysdate) = (  SELECT MIN( nvl(DUP_CREATION_DATE,sysdate))
   				          FROM  HZ_IMP_INT_DEDUP_RESULTS
					  WHERE   batch_id =p_batch_id
					  AND entity = p_entity
					  AND winner_record_os = p_winner_record_os
					  AND winner_record_osr = p_winner_record_osr)
AND winner_record_os = p_winner_record_os
AND winner_record_osr = p_winner_record_osr
AND rownum =1;

Line: 2247

SELECT MEANING FROM FND_LOOKUP_VALUES lkp
WHERE  lkp.lookup_code=p_lkp_code
AND    lkp.lookup_type='HZ_IMP_BATCH_DEDUP_STATUS'
AND    lkp.language  = userenv('LANG')
AND    lkp.view_application_id  = 222
AND    lkp.security_group_id  =fnd_global.lookup_security_group('HZ_IMP_BATCH_DEDUP_STATUS', 222)
AND    rownum=1;

Line: 2263

IF p_action_on_entity='KEEP_LATEST_UPDATED' THEN
   OPEN  max_last_date;

Line: 2348

       UPDATE HZ_IMP_PARTIES_INT party
       SET INTERFACE_STATUS = 'R', dqm_action_flag = NULL
       WHERE batch_id = p_batch_id
       AND ((party_id IS NULL and l_party_id(i) IS NULL)
             OR (party_id IS NOT NULL and l_party_id(i) IS NOT NULL and party_id=l_party_id(i)))
       AND party_orig_system = l_party_orig_os(i)
       AND party_orig_system_reference = l_party_orig_osr(i) ;

Line: 2360

       UPDATE HZ_IMP_CLASSIFICS_INT
       SET interface_status = 'R'
       WHERE batch_id = p_batch_id
       AND party_orig_system = l_party_orig_os(i)
       AND party_orig_system_reference = l_party_orig_osr(i) ;

Line: 2369

       UPDATE HZ_IMP_CREDITRTNGS_INT
       SET interface_status = 'R'
       WHERE batch_id = p_batch_id
       AND party_orig_system = l_party_orig_os(i)
       AND party_orig_system_reference = l_party_orig_osr(i) ;

Line: 2378

       UPDATE HZ_IMP_FINNUMBERS_INT
       SET interface_status = 'R'
       WHERE batch_id = p_batch_id
       AND party_orig_system = l_party_orig_os(i)
       AND party_orig_system_reference = l_party_orig_osr(i) ;

Line: 2387

       UPDATE HZ_IMP_FINREPORTS_INT
       SET interface_status = 'R'
       WHERE batch_id = p_batch_id
       AND party_orig_system = l_party_orig_os(i)
       AND party_orig_system_reference = l_party_orig_osr(i) ;

Line: 2394

          (p_action_dup_parties = 'REMOVE' or p_action_dup_parties = 'INSERT' ) )
                  or
          (p_action_pot_dup_parties <> NULL and
          (p_action_pot_dup_parties = 'REMOVE' or p_action_pot_dup_parties = 'INSERT' ) )
       THEN

       FORALL i in l_party_orig_osr.FIRST..l_party_orig_osr.LAST
       UPDATE HZ_IMP_DUP_PARTIES
       SET auto_merge_flag = 'R'
       WHERE batch_id = p_batch_id
       AND party_id = l_party_id(i)
       AND party_os = l_party_orig_os(i)
       AND party_osr = l_party_orig_osr(i) ;

Line: 2413

       UPDATE HZ_IMP_ADDRESSES_INT
       SET interface_status = 'R'
       WHERE batch_id = p_batch_id
       AND party_orig_system = l_party_orig_os(i)
       AND party_orig_system_reference = l_party_orig_osr(i)
       RETURNING site_orig_system,site_orig_system_Reference BULK COLLECT into l_site_orig_os,l_site_orig_osr;

Line: 2422

       UPDATE HZ_IMP_ADDRESSUSES_INT
       SET INTERFACE_STATUS = 'R'
       WHERE batch_id = p_batch_id
       AND site_orig_system = l_site_orig_os(i)
       AND site_orig_system_reference = l_site_orig_osr(i) ;

Line: 2431

       UPDATE HZ_IMP_CONTACTPTS_INT
       SET interface_status = 'R'
       WHERE batch_id = p_batch_id
       AND party_orig_system = l_party_orig_os(i)
       AND party_orig_system_reference = l_party_orig_osr(i) ;

Line: 2440

       UPDATE HZ_IMP_RELSHIPS_INT
       SET interface_status = 'R'
       WHERE batch_id = p_batch_id
       AND (sub_orig_system = l_party_orig_os(i)
       AND sub_orig_system_reference = l_party_orig_osr(i))
       OR
       (obj_orig_system = l_party_orig_os(i)
       AND obj_orig_system_reference = l_party_orig_osr(i));

Line: 2452

       UPDATE HZ_IMP_CONTACTS_INT
       SET interface_status = 'R'
       WHERE batch_id = p_batch_id
       AND (sub_orig_system = l_party_orig_os(i)
       AND sub_orig_system_reference = l_party_orig_osr(i))
       OR
       (obj_orig_system = l_party_orig_os(i)
       AND obj_orig_system_reference = l_party_orig_osr(i))
       RETURNING contact_orig_system,contact_orig_system_reference BULK COLLECT into l_cont_orig_os,l_cont_orig_osr;

Line: 2464

       UPDATE HZ_IMP_CONTACTROLES_INT
       SET INTERFACE_STATUS = 'R'
       WHERE batch_id = p_batch_id
       AND contact_orig_system = l_cont_orig_os(i)
       AND contact_orig_system_reference = l_cont_orig_osr(i) ;

Line: 2473

       IF ( (p_action_dup_parties <> NULL and p_action_dup_parties = 'INSERT' )
                        OR
            (p_action_pot_dup_parties <> NULL and p_action_pot_dup_parties = 'INSERT' )
          )
       THEN

       FORALL i in l_party_orig_osr.FIRST..l_party_orig_osr.LAST
       UPDATE HZ_IMP_PARTIES_INT
       SET dqm_action_flag = NULL
       WHERE batch_id = p_batch_id
       AND party_id = l_party_id(i)
       AND party_orig_system = l_party_orig_os(i)
       AND party_orig_system_reference = l_party_orig_osr(i) ;

Line: 2491

       UPDATE HZ_IMP_PARTIES_INT
       SET dqm_action_flag = 'P'
       WHERE batch_id = p_batch_id
       AND party_id = l_party_id(i)
       AND party_orig_system = l_party_orig_os(i)
       AND party_orig_system_reference = l_party_orig_osr(i) ;

Line: 2500

       UPDATE HZ_IMP_DUP_PARTIES
       SET auto_merge_flag = 'N'
       WHERE batch_id = p_batch_id
       AND party_id = l_party_id(i)
       AND party_os = l_party_orig_os(i)
       AND party_osr = l_party_orig_osr(i) ;

Line: 2562

       UPDATE HZ_IMP_ADDRESSES_INT
       SET INTERFACE_STATUS = 'R', dqm_action_flag = NULL
       WHERE batch_id = p_batch_id
       AND site_orig_system = l_site_orig_os(i)
       AND site_orig_system_reference = l_site_orig_osr(i) ;

Line: 2570

       UPDATE HZ_IMP_ADDRESSUSES_INT
       SET INTERFACE_STATUS = 'R'
       WHERE batch_id = p_batch_id
       AND site_orig_system = l_site_orig_os(i)
       AND site_orig_system_reference = l_site_orig_osr(i) ;

Line: 2578

          (p_action_pot_dup_addrs = 'REMOVE' or p_action_pot_dup_addrs = 'INSERT' ) )
       THEN

       FORALL i in l_site_orig_osr.FIRST..l_site_orig_osr.LAST
       DELETE FROM HZ_IMP_DUP_DETAILS
       WHERE batch_id = p_batch_id
       AND record_os = l_site_orig_os(i)
       AND record_osr = l_site_orig_osr(i) ;

Line: 2589

       IF (p_action_pot_dup_addrs <> NULL and p_action_pot_dup_addrs = 'INSERT' )
       THEN

       FORALL i in l_site_orig_osr.FIRST..l_site_orig_osr.LAST
       UPDATE HZ_IMP_ADDRESSES_INT
       SET dqm_action_flag = NULL
       WHERE batch_id = p_batch_id
       AND site_orig_system = l_site_orig_os(i)
       AND site_orig_system_reference = l_site_orig_osr(i) ;

Line: 2655

       UPDATE HZ_IMP_CONTACTS_INT
       SET INTERFACE_STATUS = 'R', dqm_action_flag = NULL
       WHERE batch_id = p_batch_id
       AND contact_orig_system = l_cont_orig_os(i)
       AND contact_orig_system_reference = l_cont_orig_osr(i) ;

Line: 2663

       UPDATE HZ_IMP_CONTACTROLES_INT
       SET INTERFACE_STATUS = 'R'
       WHERE batch_id = p_batch_id
       AND contact_orig_system = l_cont_orig_os(i)
       AND contact_orig_system_reference = l_cont_orig_osr(i) ;

Line: 2671

          (p_action_pot_dup_contacts = 'REMOVE' or p_action_pot_dup_contacts = 'INSERT' ) )
       THEN

       FORALL i in l_cont_orig_osr.FIRST..l_cont_orig_osr.LAST
       DELETE FROM HZ_IMP_DUP_DETAILS
       WHERE batch_id = p_batch_id
       AND record_os = l_cont_orig_os(i)
       AND record_osr = l_cont_orig_osr(i) ;

Line: 2682

       IF (p_action_pot_dup_contacts <> NULL and p_action_pot_dup_contacts = 'INSERT' )
       THEN

       FORALL i in l_cont_orig_osr.FIRST..l_cont_orig_osr.LAST
       UPDATE HZ_IMP_CONTACTS_INT
       SET dqm_action_flag = NULL
       WHERE batch_id = p_batch_id
       AND contact_orig_system = l_cont_orig_os(i)
       AND contact_orig_system_reference = l_cont_orig_osr(i) ;

Line: 2748

       UPDATE HZ_IMP_CONTACTPTS_INT
       SET INTERFACE_STATUS = 'R', dqm_action_flag = NULL
       WHERE batch_id = p_batch_id
       AND cp_orig_system = l_cpts_orig_os(i)
       AND cp_orig_system_reference = l_cpts_orig_osr(i) ;

Line: 2756

          (p_action_pot_dup_cpts = 'REMOVE' or p_action_pot_dup_cpts = 'INSERT' ) )
       THEN

       FORALL i in l_cpts_orig_osr.FIRST..l_cpts_orig_osr.LAST
       DELETE FROM HZ_IMP_DUP_DETAILS
       WHERE batch_id = p_batch_id
       AND record_os = l_cpts_orig_os(i)
       AND record_osr = l_cpts_orig_osr(i) ;

Line: 2767

       IF (p_action_pot_dup_cpts <> NULL and p_action_pot_dup_cpts = 'INSERT' )
       THEN

       FORALL i in l_cpts_orig_osr.FIRST..l_cpts_orig_osr.LAST
       UPDATE HZ_IMP_CONTACTPTS_INT
       SET dqm_action_flag = NULL
       WHERE batch_id = p_batch_id
       AND cp_orig_system = l_cpts_orig_os(i)
       AND cp_orig_system_reference = l_cpts_orig_osr(i) ;