APPS.CSM_SERIAL_NUMBERS_EVENT_PKG SQL Statements

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

Line: 16

PROCEDURE insert_mtl_serial_numbers(p_organization_id IN number, p_last_run_date IN date, p_resource_id IN number, p_user_id IN number)
IS
TYPE inventory_items_tbl_typ IS TABLE OF mtl_serial_numbers.inventory_item_id%TYPE INDEX BY binary_integer;

Line: 35

SELECT acc.access_id
FROM   csm_mtl_serial_numbers_acc acc
WHERE  acc.user_id = p_userid
--AND acc.current_organization_id = p_organization_id
AND   (acc.inventory_item_id, acc.serial_number, acc.current_organization_id) IN (
        SELECT inventory_item_id, serial_number, current_organization_id
    	FROM mtl_serial_numbers
    	WHERE creation_date >= NVL(p_lastrundate, creation_date)
    	AND current_status =3
    	AND (current_subinventory_code, current_organization_id) IN (
     		SELECT subinventory_code
     		,organization_id
     		FROM csp_inv_loc_assignments
     		WHERE resource_id = p_resourceid
     		AND SYSDATE BETWEEN nvl( effective_date_start, SYSDATE )
                     AND nvl( effective_date_end , SYSDATE ))
    		 );

Line: 55

IS--select  serial numbers in status 3 for both ib non ib items
 SELECT  csm_mtl_serial_numbers_acc_s.nextval, inventory_item_id, serial_number, current_organization_id
 FROM 	 mtl_serial_numbers
 WHERE   current_status =3 -- resides in stores + issued out of subinv
 AND   	 ( current_subinventory_code, current_organization_id ) IN (
       	   SELECT subinventory_code
       	   ,organization_id
       		FROM csp_inv_loc_assignments
       		WHERE resource_id = p_resourceid
       		AND SYSDATE BETWEEN nvl( effective_date_start, SYSDATE )
                     AND nvl( effective_date_end , SYSDATE ))
 AND  	 ( inventory_item_id, serial_number, current_organization_id ) NOT IN (
           SELECT inventory_item_id, serial_number, current_organization_id
       	   FROM csm_mtl_serial_numbers_acc
       	   WHERE user_id = p_userid );

Line: 72

IS--select  serial numbers in status 1,4 for non ib items only
SELECT   csm_mtl_serial_numbers_acc_s.nextval,
 		 ser.inventory_item_id,
		 ser.serial_number,
  		 ser.current_organization_id
FROM   	 mtl_serial_numbers ser
WHERE 	 ser.current_status IN (1,4)     --Not assigned and out of subinv
AND 	 EXISTS (
	  		   SELECT  'x'
			   FROM	   csm_mtl_onhand_qty_acc ohqacc,
			   		   mtl_system_items sys
			   WHERE   ohqacc.inventory_item_id 	 = ser.inventory_item_id
			   AND 	   ohqacc.organization_id 		 = ser.current_organization_id
			   AND	   ohqacc.user_id 				 = p_userid
			   AND     ohqacc.inventory_item_id    	 = sys.inventory_item_id
			   AND     ohqacc.ORGANIZATION_ID 		 = sys.ORGANIZATION_ID
			   AND     NVL(sys.COMMS_NL_TRACKABLE_FLAG,'N') ='N'
   			  )
AND    NOT EXISTS (
               SELECT 'x'
               FROM   csm_mtl_serial_numbers_acc sacc
       		   WHERE  user_id 					   = p_userid
      		   AND    sacc.inventory_item_id 	   = ser.inventory_item_id
      		   AND    sacc.serial_number  	 	   = ser.serial_number
      		   AND    sacc.current_organization_id = ser.current_organization_id);

Line: 99

 CSM_UTIL_PKG.LOG('Entering CSM_SERIAL_NUMBERS_EVENT_PKG.INSERT_MTL_SERIAL_NUMBERS ',
                         'CSM_SERIAL_NUMBERS_EVENT_PKG.INSERT_MTL_SERIAL_NUMBERS',FND_LOG.LEVEL_PROCEDURE);

Line: 103

    l_access_id_tbl.DELETE;

Line: 115

        UPDATE csm_mtl_serial_numbers_acc
        SET    counter = counter + 1
              ,last_update_date = SYSDATE
              ,last_updated_by = fnd_global.user_id
        WHERE access_id = l_access_id_tbl(i);

Line: 121

   l_access_id_tbl.delete;

Line: 132

      INSERT INTO csm_mtl_serial_numbers_acc(access_id, user_id, serial_number, inventory_item_id, current_organization_id,
                               counter, created_by, creation_date, last_updated_by, last_update_date, last_update_login)
                        VALUES (l_access_id_tbl(i), p_user_id, l_serial_numbers_tbl(i), l_inventory_items_tbl(i), l_organizations_tbl(i),
                                1, fnd_global.user_id, l_run_date, fnd_global.user_id, l_run_date, fnd_global.login_id);

Line: 137

    CSM_UTIL_PKG.LOG('Bulk inserted ' || l_access_id_tbl.count || ' records into csm_mtl_serial_numbers_acc for resource ' || p_resource_id ,
                         'CSM_SERIAL_NUMBERS_EVENT_PKG.INSERT_MTL_SERIAL_NUMBERS',FND_LOG.LEVEL_STATEMENT);

Line: 152

 	l_access_id_tbl.delete;

Line: 153

 	l_inventory_items_tbl.delete;

Line: 154

 	l_serial_numbers_tbl.delete;

Line: 155

    l_organizations_tbl.delete;

Line: 166

      INSERT INTO csm_mtl_serial_numbers_acc(access_id, user_id, serial_number, inventory_item_id, current_organization_id,
                               counter, created_by, creation_date, last_updated_by, last_update_date, last_update_login)
                        VALUES (l_access_id_tbl(i), p_user_id, l_serial_numbers_tbl(i), l_inventory_items_tbl(i), l_organizations_tbl(i),
                                1, fnd_global.user_id, l_run_date, fnd_global.user_id, l_run_date, fnd_global.login_id);

Line: 171

    CSM_UTIL_PKG.LOG('Bulk inserted ' || l_access_id_tbl.count || ' records into csm_mtl_serial_numbers_acc for resource ' || p_resource_id ,
                         'CSM_SERIAL_NUMBERS_EVENT_PKG.INSERT_MTL_SERIAL_NUMBERS',FND_LOG.LEVEL_STATEMENT);

Line: 184

 CSM_UTIL_PKG.LOG('Leaving CSM_SERIAL_NUMBERS_EVENT_PKG.INSERT_MTL_SERIAL_NUMBERS ',
                         'CSM_SERIAL_NUMBERS_EVENT_PKG.INSERT_MTL_SERIAL_NUMBERS',FND_LOG.LEVEL_PROCEDURE);

Line: 191

     CSM_UTIL_PKG.LOG('Exception in CSM_SERIAL_NUMBERS_EVENT_PKG.INSERT_MTL_SERIAL_NUMBERS: ' || l_sqlerrno || ':' || l_sqlerrmsg,
                         'CSM_SERIAL_NUMBERS_EVENT_PKG.INSERT_MTL_SERIAL_NUMBERS',FND_LOG.LEVEL_EXCEPTION);

Line: 194

END INSERT_MTL_SERIAL_NUMBERS;

Line: 196

PROCEDURE update_mtl_serial_numbers(p_organization_id IN number, p_last_run_date IN date, p_resource_id IN number, p_user_id IN number)
IS
TYPE access_id_tbl_typ IS TABLE OF csm_mtl_serial_numbers_acc.access_id%TYPE INDEX BY binary_integer;

Line: 208

SELECT access_id
FROM csm_mtl_serial_numbers_acc acc
,    mtl_serial_numbers msn
WHERE msn.inventory_item_id = acc.inventory_item_id
AND   msn.serial_number = acc.serial_number
AND   msn.current_organization_id = acc.current_organization_id
AND   msn.last_update_date  >= p_lastrundate
AND   acc.user_id = p_userid;

Line: 217

     SELECT subinventory_code
     ,      organization_id
     FROM csp_inv_loc_assignments
     WHERE resource_id = p_resourceid
     AND SYSDATE BETWEEN nvl( effective_date_start, SYSDATE )
                     AND nvl( effective_date_end , SYSDATE ));*/

Line: 227

SELECT access_id
FROM csm_mtl_serial_numbers_acc acc
,    mtl_serial_numbers msn
WHERE msn.inventory_item_id = acc.inventory_item_id
AND   msn.serial_number = acc.serial_number
AND   msn.current_organization_id = acc.current_organization_id
AND   acc.user_id = p_userid
AND   msn.current_status =3
AND ( msn.current_subinventory_code, msn.current_organization_id ) NOT IN (
     SELECT subinventory_code
     ,      organization_id
     FROM csp_inv_loc_assignments
     WHERE resource_id = p_resourceid
     AND SYSDATE BETWEEN nvl( effective_date_start, SYSDATE )
                     AND nvl( effective_date_end , SYSDATE ))
UNION
SELECT access_id
FROM  csm_mtl_serial_numbers_acc acc
WHERE acc.user_id = p_user_id
AND NOT EXISTS
 	(SELECT 1
  	  FROM mtl_serial_numbers msn
  	  WHERE msn.serial_number = acc.serial_number
  	  AND  msn.inventory_item_id = acc.inventory_item_id
  	  AND msn.current_organization_id = acc.current_organization_id
  	  AND msn.CURRENT_STATUS =3
 	  )
AND NOT EXISTS
 	( SELECT 1
 	  FROM   mtl_serial_numbers ser,
 	    	 csm_mtl_onhand_qty_acc ohqacc,
	    	 mtl_system_items sys
	  WHERE  ser.current_status in (1,4)
	  AND	 ohqacc.user_id = acc.user_id
	  AND    ser.serial_number  = acc.serial_number
  	  AND	 ser.inventory_item_id =acc.inventory_item_id
	  AND	 ser.inventory_item_id =sys.inventory_item_id
	  AND    ser.CURRENT_ORGANIZATION_ID =  sys.ORGANIZATION_ID
	  AND 	 NVL(sys.COMMS_NL_TRACKABLE_FLAG,'N') ='N'
	  AND 	 ohqacc.inventory_item_id = ser.inventory_item_id
	  AND 	 ohqacc.organization_id = ser.current_organization_id

 	  );

Line: 272

CURSOR l_delete_serial_number_acc(p_userid IN number)
IS
SELECT access_id
FROM csm_mtl_serial_numbers_acc
WHERE user_id = p_userid
AND counter = 0;

Line: 280

 CSM_UTIL_PKG.LOG('Entering CSM_SERIAL_NUMBERS_EVENT_PKG.UPDATE_MTL_SERIAL_NUMBERS ',
                         'CSM_SERIAL_NUMBERS_EVENT_PKG.UPDATE_MTL_SERIAL_NUMBERS',FND_LOG.LEVEL_PROCEDURE);

Line: 286

    l_access_id_tbl.DELETE;

Line: 296

                         'CSM_SERIAL_NUMBERS_EVENT_PKG.UPDATE_MTL_SERIAL_NUMBERS',FND_LOG.LEVEL_STATEMENT);

Line: 307

   l_access_id_tbl.DELETE;

Line: 318

        UPDATE csm_mtl_serial_numbers_acc
        SET    counter = counter - 1
              ,last_update_date = SYSDATE
              ,last_updated_by = fnd_global.user_id
        WHERE access_id = l_access_id_tbl(i);

Line: 324

   l_access_id_tbl.delete;

Line: 328

 OPEN l_delete_serial_number_acc(p_user_id);

Line: 329

 FETCH l_delete_serial_number_acc BULK COLLECT INTO l_access_id_tbl;

Line: 330

 CLOSE l_delete_serial_number_acc;

Line: 334

                         'CSM_SERIAL_NUMBERS_EVENT_PKG.UPDATE_MTL_SERIAL_NUMBERS',FND_LOG.LEVEL_STATEMENT);

Line: 346

      DELETE csm_mtl_serial_numbers_acc WHERE ACCESS_ID = l_access_id_tbl(i);

Line: 348

   l_access_id_tbl.DELETE;

Line: 351

 CSM_UTIL_PKG.LOG('Leaving CSM_SERIAL_NUMBERS_EVENT_PKG.UPDATE_MTL_SERIAL_NUMBERS ',
                         'CSM_SERIAL_NUMBERS_EVENT_PKG.UPDATE_MTL_SERIAL_NUMBERS',FND_LOG.LEVEL_PROCEDURE);

Line: 358

     CSM_UTIL_PKG.LOG('Exception in CSM_SERIAL_NUMBERS_EVENT_PKG.UPDATE_MTL_SERIAL_NUMBERS: ' || l_sqlerrno || ':' || l_sqlerrmsg,
                         'CSM_SERIAL_NUMBERS_EVENT_PKG.UPDATE_MTL_SERIAL_NUMBERS',FND_LOG.LEVEL_EXCEPTION);

Line: 361

END UPDATE_MTL_SERIAL_NUMBERS;

Line: 366

l_prog_update_date 			   jtm_con_request_data.last_run_date%TYPE;

Line: 381

SELECT nvl(last_run_date, (sysdate - 365*50))
FROM jtm_con_request_data
WHERE package_name = 'CSM_SERIAL_NUMBERS_EVENT_PKG'
AND procedure_name = 'REFRESH_MTL_SERIAL_NUMBERS_ACC';

Line: 388

SELECT user_id
FROM asg_user
WHERE resource_id = p_resourceid;

Line: 394

SELECT
au.resource_id,
au.user_id
FROM asg_user_pub_resps aupr,
     asg_user au
WHERE aupr.pub_name = 'SERVICEP'
AND  au.user_name = aupr.user_name
AND  au.owner_id  = au.user_id
AND  au.enabled   = 'Y';

Line: 412

 	l_usr_list_for_serial.DELETE;

Line: 416

 	l_rsrc_list_for_serial.DELETE;

Line: 430

    update_mtl_serial_numbers(l_user_palm_organization_id,l_last_run_date,l_rsrc_list_for_serial(l_count),l_usr_list_for_serial(l_count));

Line: 432

    insert_mtl_serial_numbers(l_user_palm_organization_id,l_last_run_date,l_rsrc_list_for_serial(l_count),l_usr_list_for_serial(l_count));

Line: 439

 UPDATE jtm_con_request_data
 SET last_run_date = l_run_date
 WHERE package_name = 'CSM_SERIAL_NUMBERS_EVENT_PKG'
 AND procedure_name = 'REFRESH_MTL_SERIAL_NUMBERS_ACC';

Line: 460

PROCEDURE DELETE_OLD_ORG_SERIAL_NUMBERS(p_organization_id IN number
                                        , p_user_id     IN number
                                        , p_resource_id IN number)
IS
TYPE access_id_tbl_typ  IS TABLE OF csm_mtl_serial_numbers_acc.access_id%TYPE INDEX BY BINARY_INTEGER;

Line: 476

SELECT access_id
FROM csm_mtl_serial_numbers_acc
WHERE user_id = p_userid
AND current_organization_id = p_organizationid
AND counter = 0;

Line: 483

 CSM_UTIL_PKG.LOG('Entering CSM_MTL_SERIAL_NUMBERS_EVENT_PKG.DELETE_OLD_ORG_SERIAL_NUMBERS ',
                         'CSM_MTL_SERIAL_NUMBERS_EVENT_PKG.DELETE_OLD_ORG_SERIAL_NUMBERS',FND_LOG.LEVEL_PROCEDURE);

Line: 489

 CSM_UTIL_PKG.LOG('Leaving CSM_MTL_SERIAL_NUMBERS_EVENT_PKG.DELETE_OLD_ORG_SERIAL_NUMBERS ',
                         'CSM_MTL_SERIAL_NUMBERS_EVENT_PKG.DELETE_OLD_ORG_SERIAL_NUMBERS',FND_LOG.LEVEL_PROCEDURE);

Line: 497

     CSM_UTIL_PKG.LOG('Exception in CSM_MTL_SERIAL_NUMBERS_EVENT_PKG.DELETE_OLD_ORG_SERIAL_NUMBERS: ' || l_sqlerrno || ':' || l_sqlerrmsg,
                         'CSM_MTL_SERIAL_NUMBERS_EVENT_PKG.DELETE_OLD_ORG_SERIAL_NUMBERS',FND_LOG.LEVEL_EXCEPTION);

Line: 500

END DELETE_OLD_ORG_SERIAL_NUMBERS;

Line: 519

SELECT access_id
FROM csm_mtl_serial_numbers_acc
WHERE user_id = p_userid
AND current_organization_id = p_organizationid
AND counter = 1;