APPS.INVP_CMERGE_TXHI SQL Statements

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

Line: 14

   SELECT NULL
   FROM   MTL_UNIT_TRANSACTIONS
   WHERE  customer_id in  (select racm.duplicate_id
                           from   ra_customer_merges  racm
                           where  racm.process_flag = 'N'
                           and    racm.request_id = req_id
			   and    racm.set_number = set_num)
      FOR UPDATE NOWAIT;

Line: 31

 /*Bug 13795366 Added the index_ffs after SELECT to increase the performace significantly for
   customer merge*/
 BEGIN
   SELECT 1
   INTO mut_cust_flag
   FROM dual
   WHERE EXISTS ( SELECT /*+ index_ffs(MTL_UNIT_TRANSACTIONS MTL_UNIT_TRANSACTIONS_N3)*/ 1
                  FROM  mtl_unit_transactions
                  WHERE customer_id <> 0);

Line: 58

/* customer level update */

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

Line: 64

    UPDATE MTL_UNIT_TRANSACTIONS  yt
    set    customer_id = (select distinct racm.customer_id
                          from   ra_customer_merges racm
                          where  yt.customer_id =
                                    racm.duplicate_id
                          and    racm.process_flag = 'N'
                          and    racm.request_id = req_id
			  and    racm.set_number = set_num),
           last_update_date = sysdate,
           last_updated_by = arp_standard.profile.user_id,
           last_update_login = arp_standard.profile.last_update_login
    where  customer_id in (select racm.duplicate_id
                           from   ra_customer_merges  racm
                           where  racm.process_flag = 'N'
                           and    racm.request_id = req_id
			   and    racm.set_number = set_num);

Line: 83

   /* Number of rows updates */
     arp_message.set_name('AR', 'AR_ROWS_UPDATED');

Line: 105

    SELECT   movement_id
    FROM     mtl_movement_statistics
      WHERE  ship_to_customer_id IN (
                     SELECT rcm.duplicate_id
                     FROM   ra_customer_merges rcm
                     WHERE  rcm.process_flag = 'N'
                       AND  rcm.request_id   = req_id
                       AND  rcm.set_number   = set_num)
                 OR bill_to_customer_id IN (
                     SELECT rcm.duplicate_id
                     FROM   ra_customer_merges rcm
                     WHERE  rcm.process_flag = 'N'
                       AND  rcm.request_id   = req_id
                       AND  rcm.set_number   = set_num)
       FOR UPDATE NOWAIT;

Line: 122

    SELECT   movement_id
    FROM     mtl_movement_statistics
      WHERE ship_to_site_use_id IN (
                     SELECT rcm.duplicate_site_id
                     FROM   ra_customer_merges rcm
                     WHERE  rcm.process_flag = 'N'
                       AND  rcm.request_id   = req_id
                       AND  rcm.set_number   = set_num)
                 OR bill_to_site_use_id IN (
                     SELECT rcm.duplicate_site_id
                     FROM   ra_customer_merges rcm
                     WHERE  rcm.process_flag = 'N'
                       AND  rcm.request_id   = req_id
                       AND  rcm.set_number   = set_num)
        FOR UPDATE NOWAIT;

Line: 170

          UPDATE mtl_movement_statistics mtl
             SET (ship_to_customer_id,
                  bill_to_customer_id) = (
                   SELECT distinct
                          decode(mtl.ship_to_customer_id, rcm.duplicate_id,
                                 rcm.customer_id, mtl.ship_to_customer_id),
                          decode(mtl.bill_to_customer_id, rcm.duplicate_id,
                                 rcm.customer_id, mtl.bill_to_customer_id)
                   FROM  ra_customer_merges rcm
                   WHERE mtl.ship_to_customer_id = rcm.duplicate_id
                      OR mtl.bill_to_customer_id = rcm.duplicate_id),
                 last_update_date = sysdate,
                 last_updated_by = arp_standard.profile.user_id,
                 last_update_login = arp_standard.profile.last_update_login
              WHERE ship_to_customer_id IN (
                     SELECT rcm.duplicate_id
                     FROM   ra_customer_merges rcm
                     WHERE  rcm.process_flag = 'N'
                       AND  rcm.request_id   = req_id
                       AND  rcm.set_number   = set_num)
                 OR bill_to_customer_id IN (
                     SELECT rcm.duplicate_id
                     FROM   ra_customer_merges rcm
                     WHERE  rcm.process_flag = 'N'
                       AND  rcm.request_id   = req_id
                       AND  rcm.set_number   = set_num);

Line: 199

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

Line: 208

          UPDATE mtl_movement_statistics mtl
             SET (ship_to_site_use_id,
                  bill_to_site_use_id) = (
                   SELECT distinct
                         decode(mtl.ship_to_site_use_id, rcm.duplicate_site_id,
                                rcm.customer_site_id, mtl.ship_to_site_use_id),
                         decode(mtl.bill_to_site_use_id, rcm.duplicate_site_id,
                                rcm.customer_site_id, mtl.bill_to_site_use_id)
                   FROM  ra_customer_merges rcm
                   WHERE mtl.ship_to_site_use_id = rcm.duplicate_site_id
                      OR mtl.bill_to_site_use_id = rcm.duplicate_site_id),
                 last_update_date = sysdate,
                 last_updated_by = arp_standard.profile.user_id,
                 last_update_login = arp_standard.profile.last_update_login
               WHERE ship_to_site_use_id IN (
                     SELECT rcm.duplicate_site_id
                     FROM   ra_customer_merges rcm
                     WHERE  rcm.process_flag = 'N'
                       AND  rcm.request_id   = req_id
                       AND  rcm.set_number   = set_num)
                 OR bill_to_site_use_id IN (
                     SELECT rcm.duplicate_site_id
                     FROM   ra_customer_merges rcm
                     WHERE  rcm.process_flag = 'N'
                       AND  rcm.request_id   = req_id
                       AND  rcm.set_number   = set_num);

Line: 239

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