APPS.ARP_CMERGE_ARCPF SQL Statements

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

Line: 50

 |                        delete mode. Move the call to 'delete_rows'.
 |
 +===========================================================================*/

PROCEDURE merge (
          req_id               NUMBER,
          set_num              NUMBER,
          process_mode         VARCHAR2
) IS

BEGIN

    arp_message.set_line( 'ARP_CMERGE_ARCPF.MERGE()+' );

Line: 97

 |                        delete mode. Move the call to 'delete_rows'.
 |     Jianying Huang  09-APR-00  Bug 1725662: Modified 'ar_ch' to use index.
 |
 +===========================================================================*/

PROCEDURE ar_ch (
          req_id                      NUMBER,
          set_num                     NUMBER,
          process_mode                VARCHAR2
) IS

    CURSOR c1 is
       SELECT CREDIT_HISTORY_ID
       FROM AR_CREDIT_HISTORIES yt, ra_customer_merges m
       WHERE yt.customer_id = m.duplicate_id
       AND   yt.site_use_id = m.duplicate_site_id
       AND   m.process_flag = 'N'
       AND   m.request_id = req_id
       AND   m.set_number = set_num
       AND   m.delete_duplicate_flag = 'Y'
       FOR UPDATE NOWAIT;

Line: 120

       SELECT CREDIT_HISTORY_ID
       FROM AR_CREDIT_HISTORIES yt, ra_customer_merges m
       WHERE yt.customer_id = m.duplicate_id
       AND   m.process_flag = 'N'
       AND   m.request_id = req_id
       AND   m.set_number = set_num
       AND   m.delete_duplicate_flag = 'Y'
       AND   site_use_id IS NULL
       AND   NOT EXISTS (
                 SELECT 'accounts exist'
                 FROM   hz_cust_accounts acct
                 WHERE  acct.cust_account_id = yt.customer_id
                 AND    acct.status <> 'D' )
       FOR UPDATE NOWAIT;

Line: 154

    DELETE FROM AR_CREDIT_HISTORIES yt
    WHERE (customer_id, site_use_id) IN (
               SELECT m.duplicate_id, m.duplicate_site_id
               FROM   ra_customer_merges m
               WHERE  m.process_flag = 'N'
               AND    m.request_id = req_id
               AND    m.set_number = set_num
               AND  m.delete_duplicate_flag = 'Y');

Line: 165

    arp_message.set_name( 'AR', 'AR_ROWS_DELETED' );

Line: 173

    DELETE FROM AR_CREDIT_HISTORIES yt
    WHERE customer_id IN (
             SELECT m.duplicate_id
             FROM   ra_customer_merges m
             WHERE  m.process_flag = 'N'
             AND    m.request_id = req_id
             AND    m.set_number = set_num
             AND    m.delete_duplicate_flag = 'Y' )
    AND   site_use_id IS NULL
    AND   NOT EXISTS (
             SELECT 'accounts exist'
             FROM   hz_cust_accounts acct
             WHERE  acct.cust_account_id = yt.customer_id
             AND    acct.status <> 'D' );

Line: 190

    arp_message.set_name( 'AR', 'AR_ROWS_DELETED' );

Line: 227

 |                        products, we need to mark deleted rows here
 |                        first and physically delete them after merging one
 |                        set in 'delete_rows'.
 |
 +===========================================================================*/

PROCEDURE ar_cp (
          req_id                        NUMBER,
          set_num                       NUMBER,
          process_mode                  VARCHAR2
) IS

    --cursor c1 and c2 work in inactive mode.
    CURSOR c1 is
       SELECT cust_account_profile_id
       FROM hz_customer_profiles, ra_customer_merges m
       WHERE site_use_id = m.duplicate_site_id
       AND   m.process_flag = 'N'
       AND   m.request_id = req_id
       AND   m.set_number = set_num
       FOR UPDATE NOWAIT;

Line: 250

       SELECT cust_account_profile_id
       FROM hz_customer_profiles yt, ra_customer_merges m
       WHERE cust_account_id = m.duplicate_id
       AND   m.process_flag = 'N'
       AND   m.request_id = req_id
       AND   m.set_number = set_num
       AND   m.delete_duplicate_flag = 'N'
       AND site_use_id IS NULL
       AND NOT EXISTS (
                  SELECT 'active accounts exist'
                  FROM   hz_cust_accounts acct
                  WHERE  acct.cust_account_id = yt.cust_account_id
                  AND    acct.status = 'A')
       FOR UPDATE NOWAIT;

Line: 267

       SELECT cust_account_profile_id
       FROM hz_customer_profiles yt, ra_customer_merges m
       WHERE cust_account_id = m.duplicate_id
       AND   m.process_flag = 'N'
       AND   m.request_id = req_id
       AND   m.set_number = set_num
       AND   m.delete_duplicate_flag = 'Y'
       AND   site_use_id IS NULL
       AND   NOT EXISTS (
                  SELECT 'accounts exist'
                  FROM   hz_cust_accounts acct
                  WHERE  acct.cust_account_id = yt.cust_account_id
                  AND    acct.status <> 'D' )
       FOR UPDATE NOWAIT;

Line: 308

       UPDATE hz_customer_profiles yt
       SET status = 'I',
           last_update_date = sysdate,
           last_updated_by = arp_standard.profile.user_id,
           last_update_login = arp_standard.profile.last_update_login,
           request_id =  req_id,
           program_application_id = arp_standard.profile.program_application_id,
           program_id = arp_standard.profile.program_id,
           program_update_date = sysdate
       WHERE site_use_id IN (
                  SELECT m.duplicate_site_id
                  FROM   ra_customer_merges m
                  WHERE  m.process_flag = 'N'
                  AND    m.request_id = req_id
                  AND    m.set_number = set_num
                  AND    m.delete_duplicate_flag = 'N' ) ;

Line: 327

       arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );

Line: 334

       UPDATE hz_customer_profiles yt
       SET status = 'I',
           last_update_date = sysdate,
           last_updated_by = arp_standard.profile.user_id,
           last_update_login = arp_standard.profile.last_update_login,
           request_id =  req_id,
           program_application_id = arp_standard.profile.program_application_id,
           program_id = arp_standard.profile.program_id,
           program_update_date = sysdate
       WHERE cust_account_id IN (
                  SELECT m.duplicate_id
                  FROM   ra_customer_merges m
                  WHERE  m.process_flag = 'N'
                  AND    m.request_id = req_id
                  AND    m.set_number = set_num
                  AND    m.delete_duplicate_flag = 'N' )
       AND site_use_id IS NULL
       AND NOT EXISTS (
                  SELECT 'active accounts exist'
                  FROM   hz_cust_accounts acct
                  WHERE  acct.cust_account_id = yt.cust_account_id
                  AND    acct.status = 'A');

Line: 359

       arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );

Line: 362

       /*************** 'delete' mode ***************/
--Bug 1535542: Mark the rows need to be deleted by setting status to 'D'.
--Physically delete them after merge.

       --site level 'delete'
       arp_message.set_name( 'AR', 'AR_UPDATING_TABLE' );

Line: 370

       UPDATE hz_customer_profiles
       SET status = 'D'
       WHERE site_use_id IN (
                  SELECT m.duplicate_site_id
                  FROM   ra_customer_merges m
                  WHERE  m.process_flag = 'N'
                  AND    m.request_id = req_id
                  AND    m.set_number = set_num
                  AND    m.delete_duplicate_flag = 'Y' ) ;

Line: 382

       arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );

Line: 389

       UPDATE hz_customer_profiles yt
       SET status = 'D'
       WHERE cust_account_id IN (
                  SELECT m.duplicate_id
                  FROM   ra_customer_merges m
                  WHERE  m.process_flag = 'N'
                  AND    m.request_id = req_id
                  AND    m.set_number = set_num
                  AND    m.delete_duplicate_flag = 'Y' )
       AND   site_use_id IS NULL
       AND   NOT EXISTS (
                  SELECT 'accounts exist'
                  FROM   hz_cust_accounts acct
                  WHERE  acct.cust_account_id = yt.cust_account_id
                  AND    acct.status <> 'D' );

Line: 407

       arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );

Line: 446

 |                        products, we need to move the delete part to
 |                        'delete rows' in which we do physically delete after
 |                        merging one set.
 |
 +===========================================================================*/

PROCEDURE ar_cpa (
          req_id                      NUMBER,
          set_num                     NUMBER,
          process_mode                VARCHAR2
) IS

    --cursor c1 and c2 work in inactive mode.
    CURSOR c1 is
       SELECT CUST_ACCT_PROFILE_AMT_ID
       FROM HZ_CUST_PROFILE_AMTS, ra_customer_merges m
       WHERE site_use_id = m.duplicate_site_id
       AND   m.process_flag = 'N'
       AND   m.request_id = req_id
       AND   m.set_number = set_num
       AND   m.delete_duplicate_flag = 'N'
       FOR UPDATE NOWAIT;

Line: 470

       SELECT CUST_ACCT_PROFILE_AMT_ID
       FROM HZ_CUST_PROFILE_AMTS yt, ra_customer_merges m
       WHERE cust_account_id = m.duplicate_id
       AND   m.process_flag = 'N'
       AND   m.request_id = req_id
       AND   m.set_number = set_num
       AND   m.delete_duplicate_flag = 'N'
       AND   site_use_id IS NULL
       AND NOT EXISTS (
                  SELECT 'active accounts exist'
                  FROM   hz_cust_accounts acct
                  WHERE  acct.cust_account_id = yt.cust_account_id
                  AND    acct.status = 'A')
       FOR UPDATE NOWAIT;

Line: 508

       UPDATE HZ_CUST_PROFILE_AMTS yt
       SET last_update_date = sysdate,
           last_updated_by = arp_standard.profile.user_id,
           last_update_login = arp_standard.profile.last_update_login,
           request_id =  req_id,
           program_application_id = arp_standard.profile.program_application_id,
           program_id = arp_standard.profile.program_id,
           program_update_date = sysdate
       WHERE site_use_id IN (
                  SELECT m.duplicate_site_id
                  FROM   ra_customer_merges m
                  WHERE  m.process_flag = 'N'
                  AND    m.request_id = req_id
                  AND    m.set_number = set_num
                  AND    m.delete_duplicate_flag = 'N' );

Line: 526

       arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );

Line: 533

       UPDATE HZ_CUST_PROFILE_AMTS yt
       SET last_update_date = sysdate,
           last_updated_by = arp_standard.profile.user_id,
           last_update_login = arp_standard.profile.last_update_login,
           request_id =  req_id,
           program_application_id = arp_standard.profile.program_application_id,
           program_id = arp_standard.profile.program_id,
           program_update_date = sysdate
       WHERE cust_account_id IN (
                  SELECT m.duplicate_id
                  FROM   ra_customer_merges m
                  WHERE  m.process_flag = 'N'
                  AND    m.request_id = req_id
                  AND    m.set_number = set_num
                  AND    m.delete_duplicate_flag = 'N' )
       AND site_use_id IS NULL
       AND NOT EXISTS (
                  SELECT 'active accounts exist'
                  FROM   hz_cust_accounts acct
                  WHERE  acct.cust_account_id = yt.cust_account_id
                  AND    acct.status = 'A') ;

Line: 557

       arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );

Line: 574

 |              delete_rows
 |
 | DESCRIPTION  physically delete the rows we marked in customer tables after
 |              we merging each set.
 |
 | SCOPE - PUBLIC
 |
 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
 |
 | ARGUMENTS  : IN:
 |              OUT:
 |          IN/ OUT:
 |
 | RETURNS    : NONE
 |
 | NOTES
 |
 | MODIFICATION HISTORY
 |     Jianying Huang  20-DEC-00  Created for bug 1535542: physically delete
 |                        rows in customer tables after merging each set.
 |     Jianying Huang  29-DEC-00  Modified 'delete_rows' for performance issue.
 |
 +===========================================================================*/

PROCEDURE delete_rows(
          req_id                    NUMBER,
          set_num                   NUMBER
) IS

    CURSOR profiles IS
       SELECT cust_account_profile_id
       FROM HZ_CUSTOMER_PROFILES, ra_customer_merges m
       WHERE cust_account_id = m.duplicate_id
       AND   m.process_flag = 'N'
       AND   m.request_id = req_id
       AND   m.set_number = set_num
       AND   m.delete_duplicate_flag = 'Y'
       AND status = 'D'
       FOR UPDATE NOWAIT;

Line: 615

       SELECT CUST_ACCT_PROFILE_AMT_ID
       FROM HZ_CUST_PROFILE_AMTS, ra_customer_merges m
       WHERE site_use_id = m.duplicate_site_id
       AND   m.process_flag = 'N'
       AND   m.request_id = req_id
       AND   m.set_number = set_num
       AND   m.delete_duplicate_flag = 'Y'
       FOR UPDATE NOWAIT;

Line: 625

       SELECT CUST_ACCT_PROFILE_AMT_ID
       FROM HZ_CUST_PROFILE_AMTS yt, ra_customer_merges m
       WHERE cust_account_id = m.duplicate_id
       AND   m.process_flag = 'N'
       AND   m.request_id = req_id
       AND   m.set_number = set_num
       AND   m.delete_duplicate_flag = 'Y'
       AND   site_use_id IS NULL
       AND   NOT EXISTS (
                  SELECT 'accounts exist'
                  FROM   hz_cust_acct_sites_all acct --SSUptake
                  WHERE  acct.cust_account_id = yt.cust_account_id
		  AND    acct.org_id  = m.org_id --SSUptake
                  AND    status <> 'D' )

       FOR UPDATE NOWAIT;

Line: 644

    arp_message.set_line( 'ARP_CMERGE_ARCPF.delete_rows()+' );

Line: 661

    DELETE FROM HZ_CUSTOMER_PROFILES
    WHERE cust_account_id IN (
                  SELECT m.duplicate_id
                  FROM   ra_customer_merges m
                  WHERE  m.process_flag = 'N'
                  AND    m.request_id = req_id
                  AND    m.set_number = set_num
                  AND    m.delete_duplicate_flag = 'Y' )
    AND status = 'D';

Line: 673

    arp_message.set_name( 'AR', 'AR_ROWS_DELETED' );

Line: 688

    DELETE FROM HZ_CUST_PROFILE_AMTS yt
    WHERE site_use_id IN (
               SELECT m.duplicate_site_id
               FROM   ra_customer_merges m
               WHERE  m.process_flag = 'N'
               AND    m.request_id = req_id
               AND    m.set_number = set_num
               AND m.delete_duplicate_flag = 'Y' );

Line: 699

    arp_message.set_name( 'AR', 'AR_ROWS_DELETED' );

Line: 706

    DELETE FROM HZ_CUST_PROFILE_AMTS yt
    WHERE cust_account_id IN (
               SELECT m.duplicate_id
               FROM   ra_customer_merges m
               WHERE  m.process_flag = 'N'
	       AND    m.request_id = req_id
               AND    m.set_number = set_num
               AND    m.delete_duplicate_flag = 'Y' )
    AND   site_use_id IS NULL
    AND   NOT EXISTS (
               SELECT 'accounts exist'
               FROM   hz_cust_accounts acct
               WHERE  acct.cust_account_id = yt.cust_account_id
               AND    acct.status <> 'D' );

Line: 723

    arp_message.set_name( 'AR', 'AR_ROWS_DELETED' );

Line: 726

    arp_message.set_line( 'ARP_CMERGE_ARCPF.delete_rows()-' );

Line: 731

      arp_message.set_error( 'ARP_CMERGE_ARCPF.delete_rows' );

Line: 734

END delete_rows;