APPS.AR_MCC_MERGE SQL Statements

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

Line: 126

  SELECT
    DISTINCT duplicate_id
  FROM
    RA_CUSTOMER_MERGES
  WHERE process_flag = 'N'
    AND request_id = req_id
    AND set_number = set_number;

Line: 138

  SELECT
    customer_merge_header_id
  FROM
    RA_CUSTOMER_MERGES
  WHERE duplicate_id = c_duplicate_id;

Line: 147

  SELECT credit_usage_id
  FROM
    HZ_CREDIT_USAGES
  WHERE cust_acct_profile_amt_id IN
        (SELECT cust_acct_profile_amt_id
         FROM   HZ_CUST_PROFILE_AMTS
         WHERE  cust_account_id IN
                (SELECT DISTINCT duplicate_id
                 FROM   RA_CUSTOMER_MERGES
                 WHERE  process_flag = 'N'
                   AND  request_id = req_id
                   AND  set_number = set_number))
  FOR UPDATE OF credit_usage_id NOWAIT;

Line: 199

l_delete_flag VARCHAR2(1);

Line: 263

        SELECT
          credit_usage_id
        , credit_profile_amt_id
        , cust_acct_profile_amt_id
        , profile_class_amount_id
        , credit_usage_rule_set_id
        BULK COLLECT INTO
          l_credit_usage_ids
        , l_credit_profile_amt_ids
        , l_cust_acct_profile_amt_ids
        , l_profile_class_amount_ids
        , l_credit_usage_rule_set_ids
        FROM  HZ_CREDIT_USAGES
        WHERE  cust_acct_profile_amt_id IN
               (SELECT cust_acct_profile_amt_id
                FROM   HZ_CUST_PROFILE_AMTS
                WHERE  cust_account_id = l_duplicate_id );

Line: 283

          l_delete_flag := 'Y';

Line: 288

          l_delete_flag := 'N';

Line: 293

           ,'Credit Usage Delete Flag:' || (l_delete_flag)
        );

Line: 300

        IF l_delete_flag = 'Y' THEN
          FORALL l_usage_idx  IN l_min_usage_idx .. l_max_usage_idx
            INSERT INTO hz_customer_merge_log
            ( merge_log_id
            , table_name
            , merge_header_id
            , primary_key_id
            , del_col1
            , del_col2
            , del_col3
            , del_col4
            , action_flag
            , request_id
            , created_by
            , creation_date
            , last_update_login
            , last_update_date
            , last_updated_by
            )
            VALUES
            ( HZ_CUSTOMER_MERGE_LOG_S.nextval
            , 'HZ_CREDIT_USAGES'
            , l_customer_merge_header_id
            , l_credit_usage_ids(l_usage_idx)
            , l_credit_profile_amt_ids(l_usage_idx)
            , l_cust_acct_profile_amt_ids(l_usage_idx)
            , l_profile_class_amount_ids(l_usage_idx)
            , l_credit_usage_rule_set_ids(l_usage_idx)
            , 'D'
            , req_id
            , 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.last_updated_by
            );

Line: 339

              ,'Records inserted in LOG table'
            );

Line: 342

        END IF; -- delete flag check

Line: 347

        IF l_delete_flag = 'Y' THEN
          -- Delete the records in full
          FORALL l_usage_idx  IN l_min_usage_idx .. l_max_usage_idx
            DELETE
              FROM  HZ_CREDIT_USAGES
             WHERE  credit_usage_id = l_credit_usage_ids(l_usage_idx);

Line: 361

                      ,'Total rows deleted: ' || to_char(l_total_count)
                      );

Line: 365

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

Line: 368

       END IF; -- delete flag check