APPS.INV_CMERGE_ITEMS SQL Statements

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

Line: 58

        SELECT distinct CUSTOMER_MERGE_HEADER_ID
              ,CUSTOMER_ITEM_ID
--Selecting the customer_id,address_id according to functionality
              ,decode(yt.CUSTOMER_ID,m.DUPLICATE_ID,m.CUSTOMER_ID,yt.CUSTOMER_ID)
              ,decode(yt.ADDRESS_ID,m.DUPLICATE_ADDRESS_ID,
		decode(yt.item_definition_level,3,m.CUSTOMER_ADDRESS_ID,yt.ADDRESS_ID),yt.ADDRESS_ID)
	      , customer_item_number
	      , customer_category_code
	      , item_definition_level
         FROM MTL_CUSTOMER_ITEMS yt, ra_customer_merges m
         WHERE (
            yt.CUSTOMER_ID = m.DUPLICATE_ID
            OR yt.ADDRESS_ID = m.DUPLICATE_ADDRESS_ID
         ) AND    m.process_flag = 'N'
         AND    m.request_id = req_id
         AND    m.set_number = set_num;

Line: 115

     /* inserting in log table */
	FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
         INSERT INTO HZ_CUSTOMER_MERGE_LOG (
           MERGE_LOG_ID,
           TABLE_NAME,
           MERGE_HEADER_ID,
           PRIMARY_KEY_ID,
           NUM_COL1_ORIG,
           NUM_COL1_NEW,
           NUM_COL2_ORIG,
           NUM_COL2_NEW,
           ACTION_FLAG,
           REQUEST_ID,
           CREATED_BY,
           CREATION_DATE,
           LAST_UPDATE_LOGIN,
           LAST_UPDATE_DATE,
           LAST_UPDATED_BY
      ) VALUES (         HZ_CUSTOMER_MERGE_LOG_s.nextval,
         'MTL_CUSTOMER_ITEMS',
         MERGE_HEADER_ID_LIST(I),
         PRIMARY_KEY_ID_LIST(I),
         NUM_COL1_ORIG_LIST(I),
         NUM_COL1_NEW_LIST(I),
         NUM_COL2_ORIG_LIST(I),
         NUM_COL2_NEW_LIST(I),
         'U',
         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: 155

       DELETE FROM MTL_CUSTOMER_ITEMS mci
       WHERE CUSTOMER_ITEM_ID=PRIMARY_KEY_ID_LIST(I)
       AND EXISTS (SELECT 1 FROM MTL_CUSTOMER_ITEMS yt
                   WHERE yt.CUSTOMER_ID = NUM_COL1_NEW_LIST(I)
		   AND  NVL(yt.ADDRESS_ID, -999) = NVL(NUM_COL2_NEW_LIST(I), -999)
		   AND  yt.CUSTOMER_ITEM_NUMBER = CUST_ITEM_NUM_LIST(I)
		   AND  NVL(yt.CUSTOMER_CATEGORY_CODE, '@@@') = NVL(CUST_CATEGORY_CODE_LIST(I), '@@@')
		   AND  yt.ITEM_DEFINITION_LEVEL = ITEM_DEF_LEVEL_LIST(I)
		   AND  yt.rowid <> mci.rowid                 --Bug: 5054179 Added this clause based on rowids
                   );

Line: 166

    /* customer level update */
    FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
      UPDATE MTL_CUSTOMER_ITEMS yt SET
           CUSTOMER_ID=NUM_COL1_NEW_LIST(I)
          ,ADDRESS_ID=NUM_COL2_NEW_LIST(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 CUSTOMER_ITEM_ID=PRIMARY_KEY_ID_LIST(I)
         ;

Line: 185

   /* Number of rows updates */
    arp_message.set_line( 'INV_CMERGE_ITEMS.MERGE()-' );

Line: 187

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