APPS.FUN_CUSTOMERMERGE_PKG SQL Statements

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

Line: 39

        SELECT distinct CUSTOMER_MERGE_HEADER_ID
              ,NETTING_CUSTOMER_ID
              ,CUST_ACCOUNT_ID
              ,CUST_SITE_USE_ID
              ,CUST_PRIORITY
         FROM FUN_NET_CUSTOMERS_ALL yt, ra_customer_merges m
         WHERE (
            yt.CUST_ACCOUNT_ID = m.DUPLICATE_ID
            OR yt.CUST_SITE_USE_ID = m.DUPLICATE_SITE_ID
         )
	 AND    m.process_flag = 'N'
         AND    m.request_id = req_id
         AND    m.set_number = set_num;

Line: 88

         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,
           NUM_COL3_ORIG,
           NUM_COL3_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,
         'FUN_NET_CUSTOMERS_ALL',
         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),
         NUM_COL3_ORIG_LIST(I),
         NUM_COL3_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: 128

      UPDATE FUN_NET_CUSTOMERS_ALL yt SET
           CUST_ACCOUNT_ID=NUM_COL1_NEW_LIST(I)
          ,CUST_SITE_USE_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
      WHERE NETTING_CUSTOMER_ID=PRIMARY_KEY_ID_LIST(I);

Line: 142

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

Line: 145

/* If there is more than one record that has the same agreement_id,cust_account_id and cust_site_use_id then update the customer priority of the records to the highest priority amongst them */
BEGIN
	UPDATE FUN_NET_CUSTOMERS_ALL yt SET
        CUST_PRIORITY= (SELECT MIN(CUST_PRIORITY)
                                FROM FUN_NET_CUSTOMERS_ALL
                                WHERE AGREEMENT_ID = yt.AGREEMENT_ID
                                AND  CUST_ACCOUNT_ID = yt.CUST_ACCOUNT_ID
                                AND nvl(CUST_SITE_USE_ID,0) = DECODE(
                                yt.CUST_SITE_USE_ID,NULL,0,yt.CUST_SITE_USE_ID)
                                )
    	WHERE  EXISTS (SELECT 1
		  FROM FUN_NET_CUSTOMERS_ALL
                  WHERE  yt.agreement_id = agreement_id
                  AND 	 yt.cust_account_id = cust_account_id
                  AND    nvl(CUST_SITE_USE_ID,0) = DECODE(
                                yt.CUST_SITE_USE_ID,NULL
				,0,yt.CUST_SITE_USE_ID)
                 GROUP BY agreement_id,cust_account_id,cust_site_use_id
                 HAVING count(agreement_id) > 1);

Line: 169

 /* If there is more than one row that has the same agreement_id , customer_priority,cust_account_id and cust_site_use_id , delete the record that has the minimum of the netting customer id */

	BEGIN
	        DELETE FROM FUN_NET_CUSTOMERS_ALL yt
        	WHERE NETTING_CUSTOMER_ID  = (
			SELECT MIN(NETTING_CUSTOMER_ID)
                        FROM FUN_NET_CUSTOMERS_ALL
                        WHERE
                         yt.AGREEMENT_ID = AGREEMENT_ID
                        AND yt.CUST_ACCOUNT_ID = CUST_ACCOUNT_ID
                        AND nvl(yt.CUST_SITE_USE_ID,0) = nvl(CUST_SITE_USE_ID,0)
                        AND yt.CUST_PRIORITY = CUST_PRIORITY
                        GROUP BY AGREEMENT_ID,
                              CUST_ACCOUNT_ID,
                             CUST_SITE_USE_ID,
                                CUST_PRIORITY
                        HAVING COUNT(NETTING_CUSTOMER_ID) > 1);