APPS.CSM_QUERY_EVENT_PKG SQL Statements

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

Line: 31

l_prog_update_date				  jtm_con_request_data.last_run_date%TYPE;

Line: 40

SELECT 	CSM_QUERY_ACC_S.NEXTVAL,
        au.USER_ID,
        b.QUERY_ID
FROM 	CSM_QUERY_B b,
      ASG_USER    au
WHERE (  (b.LEVEL_ID = 10003 AND   b.LEVEL_VALUE = au.responsibility_id)--Support for Responsiblity
      OR (b.LEVEL_ID = 10004 AND   b.LEVEL_VALUE = au.USER_ID)
      OR (b.LEVEL_ID = 10001 AND   b.LEVEL_VALUE =0)   )
AND   au.USER_ID  = au.OWNER_ID
AND   au.ENABLED= 'Y'
AND   NVL(b.DELETE_FLAG,'N') = 'N'
AND   NOT EXISTS
    	(
        SELECT	1
      	FROM 	  CSM_QUERY_ACC acc
        WHERE 	acc.QUERY_ID = b.QUERY_ID
        AND     acc.USER_ID  = au.USER_ID
    	);

Line: 61

SELECT 	CSM_QUERY_VARIABLES_ACC_S.NEXTVAL,
        qacc.USER_ID,
        b.QUERY_ID,
        b.VARIABLE_ID
FROM 	CSM_QUERY_VARIABLES_B b,
      CSM_QUERY_ACC    qacc
WHERE qacc.QUERY_ID = b.QUERY_ID
AND   NOT EXISTS
    	(
        SELECT	1
      	FROM 	  CSM_QUERY_VARIABLES_ACC vacc
        WHERE 	vacc.QUERY_ID = qacc.QUERY_ID
        AND     vacc.USER_ID  = qacc.USER_ID
        AND     b.VARIABLE_ID = vacc.VARIABLE_ID
    	);

Line: 81

SELECT 	acc.ACCESS_ID,
        acc.USER_ID
FROM 	  CSM_QUERY_ACC		acc,
        CSM_QUERY_B 	b
WHERE 	acc.QUERY_ID 		 = b.QUERY_ID
AND 	  b.LAST_UPDATE_DATE >= p_lastrundate;

Line: 90

SELECT 	acc.ACCESS_ID,
        acc.USER_ID
FROM 	  CSM_QUERY_VARIABLES_ACC		acc,
        CSM_QUERY_VARIABLES_B 	b
WHERE 	acc.QUERY_ID 		 = b.QUERY_ID
AND     acc.VARIABLE_ID  = b.VARIABLE_ID
AND 	  b.LAST_UPDATE_DATE >= p_lastrundate;

Line: 102

SELECT 	acc.ACCESS_ID,
        acc.USER_ID
FROM 	  CSM_QUERY_ACC		acc
WHERE	EXISTS
		(SELECT 1
		  FROM	CSM_QUERY_B b
		  WHERE b.QUERY_ID 	=  acc.QUERY_ID
      AND   NVL(b.DELETE_FLAG,'N') ='Y'
		 );

Line: 114

SELECT 	acc.ACCESS_ID,
        acc.USER_ID
FROM 	  CSM_QUERY_VARIABLES_ACC		acc,
      	CSM_QUERY_B b
WHERE   b.QUERY_ID 	=  acc.QUERY_ID
AND     NVL(b.DELETE_FLAG,'N') ='Y';

Line: 124

SELECT 	acc.ACCESS_ID,
        acc.USER_ID
FROM 	  CSM_QUERY_INSTANCES_ACC		acc,
      	CSM_QUERY_B b
WHERE   b.QUERY_ID 	=  acc.QUERY_ID
AND    ( NVL(b.DELETE_FLAG,'N') ='Y'
      OR  (   UPPER(b.RETENTION_POLICY) = 'AUTOMATIC'
          AND acc.LAST_UPDATE_DATE      < (SYSDATE-nvl(b.RETENTION_DAYS,1000))
       )) ;

Line: 137

SELECT 	acc.ACCESS_ID,
        acc.USER_ID
FROM 	  CSM_QUERY_VARIABLE_VALUES_ACC		acc
WHERE   NOT EXISTS
        (SELECT 1 FROM CSM_QUERY_INSTANCES_ACC iacc
         WHERE iacc.USER_ID     = acc.USER_ID
         AND   iacc.INSTANCE_ID = acc.INSTANCE_ID
         AND   iacc.QUERY_ID    = acc.QUERY_ID);

Line: 149

SELECT 	acc.ACCESS_ID,
        acc.USER_ID
FROM 	  CSM_QUERY_RESULTS_ACC		acc
WHERE   NOT EXISTS
        (SELECT 1 FROM CSM_QUERY_INSTANCES_ACC iacc
         WHERE iacc.USER_ID     = acc.USER_ID
         AND   iacc.INSTANCE_ID = acc.INSTANCE_ID
         AND   iacc.QUERY_ID    = acc.QUERY_ID);

Line: 160

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

Line: 175

 FETCH  l_last_run_date_csr INTO l_prog_update_date;

Line: 178

 CSM_UTIL_PKG.LOG('Entering deletes ', 'CSM_QUERY_EVENT_PKG.REFRESH_ACC',FND_LOG.LEVEL_STATEMENT);

Line: 187

  l_access_list.delete;

Line: 190

  l_user_list.delete;

Line: 205

     DELETE FROM csm_query_acc WHERE access_id = l_access_list(i);

Line: 211

CSM_UTIL_PKG.LOG('Completed Query Delete', 'CSM_QUERY_EVENT_PKG.REFRESH_ACC',FND_LOG.LEVEL_PROCEDURE);

Line: 217

  l_access_list.delete;

Line: 220

  l_user_list.delete;

Line: 235

     DELETE FROM csm_query_variables_acc WHERE access_id = l_access_list(i);

Line: 241

CSM_UTIL_PKG.LOG('Completed Query Variables Delete', 'CSM_QUERY_EVENT_PKG.REFRESH_ACC',FND_LOG.LEVEL_PROCEDURE);

Line: 247

  l_access_list.delete;

Line: 250

  l_user_list.delete;

Line: 265

     DELETE FROM CSM_QUERY_INSTANCES_ACC WHERE access_id = l_access_list(i);

Line: 271

CSM_UTIL_PKG.LOG('Completed Query Instances Delete', 'CSM_QUERY_EVENT_PKG.REFRESH_ACC',FND_LOG.LEVEL_PROCEDURE);

Line: 277

  l_access_list.delete;

Line: 280

  l_user_list.delete;

Line: 295

     DELETE FROM CSM_QUERY_VARIABLE_VALUES_ACC WHERE access_id = l_access_list(i);

Line: 301

CSM_UTIL_PKG.LOG('Completed Query Variable Values Delete', 'CSM_QUERY_EVENT_PKG.REFRESH_ACC',FND_LOG.LEVEL_PROCEDURE);

Line: 307

  l_access_list.delete;

Line: 310

  l_user_list.delete;

Line: 325

     DELETE FROM CSM_QUERY_RESULTS_ACC WHERE access_id = l_access_list(i);

Line: 331

 CSM_UTIL_PKG.LOG('Completed Query Results Delete', 'CSM_QUERY_EVENT_PKG.REFRESH_ACC',FND_LOG.LEVEL_PROCEDURE);

Line: 333

 CSM_UTIL_PKG.LOG('Leaving deletes and entering updates', 'CSM_QUERY_EVENT_PKG.REFRESH_ACC',FND_LOG.LEVEL_PROCEDURE);

Line: 338

 OPEN 	c_query_upd(l_prog_update_date);

Line: 341

  l_access_list.delete;

Line: 344

  l_user_list.delete;

Line: 362

CSM_UTIL_PKG.LOG('Completed Query Updates', 'CSM_QUERY_EVENT_PKG.REFRESH_ACC',FND_LOG.LEVEL_PROCEDURE);

Line: 365

 OPEN 	c_query_var_upd(l_prog_update_date);

Line: 368

  l_access_list.delete;

Line: 371

  l_user_list.delete;

Line: 389

 CSM_UTIL_PKG.LOG('Completed Query Variable Updates', 'CSM_QUERY_EVENT_PKG.REFRESH_ACC',FND_LOG.LEVEL_PROCEDURE);

Line: 390

 CSM_UTIL_PKG.LOG('Leaving updates and entering inserts', 'CSM_QUERY_EVENT_PKG.REFRESH_ACC',FND_LOG.LEVEL_PROCEDURE);

Line: 397

  l_access_list.delete;

Line: 400

  l_user_list.delete;

Line: 403

  l_query_id_list.delete;

Line: 417

     INSERT INTO CSM_QUERY_ACC
	 			(ACCESS_ID,
         USER_ID,
         QUERY_ID,
				 CREATED_BY, CREATION_DATE,
				 LAST_UPDATED_BY,
				 LAST_UPDATE_DATE,
				 LAST_UPDATE_LOGIN)
                 VALUES
				 (l_access_list(i),
          l_user_list(i),
				  l_query_id_list(i),
				  fnd_global.user_id,
				  l_run_date,
				  fnd_global.user_id,
				  l_run_date,
				  fnd_global.login_id);

Line: 439

 CSM_UTIL_PKG.LOG('Completed Query Inserts', 'CSM_QUERY_EVENT_PKG.REFRESH_ACC',FND_LOG.LEVEL_PROCEDURE);

Line: 444

  l_access_list.delete;

Line: 447

  l_user_list.delete;

Line: 450

  l_query_id_list.delete;

Line: 453

  l_variable_id_list.delete;

Line: 469

     INSERT INTO CSM_QUERY_VARIABLES_ACC
	 			(ACCESS_ID,
         USER_ID,
         QUERY_ID,
         VARIABLE_ID,
				 CREATED_BY,
         CREATION_DATE,
				 LAST_UPDATED_BY,
				 LAST_UPDATE_DATE,
				 LAST_UPDATE_LOGIN,
         GEN_PK)
                 VALUES
				 (l_access_list(i),
          l_user_list(i),
				  l_query_id_list(i),
          l_variable_id_list(i),
				  fnd_global.user_id,
				  l_run_date,
				  fnd_global.user_id,
				  l_run_date,
				  fnd_global.login_id,
          l_access_list(i));

Line: 496

  CSM_UTIL_PKG.LOG('Completed Query Variable Inserts', 'CSM_QUERY_EVENT_PKG.REFRESH_ACC',FND_LOG.LEVEL_PROCEDURE);

Line: 499

  DELETE FROM CSM_QUERY_B
  WHERE LEVEL_ID =10004
  AND   SAVED_QUERY ='Y'
  AND   DELETE_FLAG ='Y';

Line: 505

  UPDATE	jtm_con_request_data
  SET 	last_run_date 	= l_run_date
  WHERE 	package_name 	= 'CSM_QUERY_EVENT_PKG'
  AND 	procedure_name 	= 'REFRESH_ACC';

Line: 544

SELECT 	CSM_QUERY_ACC_S.NEXTVAL,
        au.USER_ID,
        b.QUERY_ID
FROM 	CSM_QUERY_B b,
      ASG_USER    au
WHERE (  (b.LEVEL_ID = 10003 AND   b.LEVEL_VALUE = au.responsibility_id)--Support for Responsiblity
      OR (b.LEVEL_ID = 10004 AND   b.LEVEL_VALUE = au.USER_ID)--Saved Query
      OR (b.LEVEL_ID = 10001 AND   b.LEVEL_VALUE =0)   )--Site level
AND   au.USER_ID             = c_user_id
AND   au.USER_ID             = au.OWNER_ID
AND   NVL(b.DELETE_FLAG,'N') = 'N'
AND   NOT EXISTS
    	(
        SELECT	1
      	FROM 	  CSM_QUERY_ACC acc
        WHERE 	acc.QUERY_ID = b.QUERY_ID
        AND     acc.USER_ID  = au.user_ID
    	);

Line: 565

SELECT 	CSM_QUERY_VARIABLES_ACC_S.NEXTVAL,
        qacc.USER_ID,
        b.QUERY_ID,
        b.VARIABLE_ID
FROM 	CSM_QUERY_VARIABLES_B b,
      CSM_QUERY_ACC    qacc
WHERE qacc.QUERY_ID = b.QUERY_ID
AND   qacc.USER_ID  = c_user_id
AND   NOT EXISTS
    	(
        SELECT	1
      	FROM 	  CSM_QUERY_VARIABLES_ACC vacc
        WHERE 	vacc.QUERY_ID = qacc.QUERY_ID
        AND     vacc.USER_ID  = qacc.USER_ID
        AND     b.VARIABLE_ID = vacc.VARIABLE_ID
    	);

Line: 589

  l_access_list.delete;

Line: 592

  l_user_list.delete;

Line: 595

  l_query_id_list.delete;

Line: 602

     INSERT INTO CSM_QUERY_ACC
	 			(ACCESS_ID,
         USER_ID,
         QUERY_ID,
				 CREATED_BY, CREATION_DATE,
				 LAST_UPDATED_BY,
				 LAST_UPDATE_DATE,
				 LAST_UPDATE_LOGIN)
                 VALUES
				 (l_access_list(i),
          l_user_list(i),
				  l_query_id_list(i),
				  fnd_global.user_id,
				  sysdate,
				  fnd_global.user_id,
				  sysdate,
				  fnd_global.login_id);

Line: 630

 CSM_UTIL_PKG.LOG('Completed Query Inserts for User id :'|| p_user_id, 'CSM_QUERY_EVENT_PKG.REFRESH_USER',FND_LOG.LEVEL_PROCEDURE);

Line: 637

  l_access_list.delete;

Line: 640

  l_user_list.delete;

Line: 643

  l_query_id_list.delete;

Line: 646

  l_variable_id_list.delete;

Line: 654

     INSERT INTO CSM_QUERY_VARIABLES_ACC
	 			(ACCESS_ID,
         USER_ID,
         QUERY_ID,
         VARIABLE_ID,
				 CREATED_BY,
         CREATION_DATE,
				 LAST_UPDATED_BY,
				 LAST_UPDATE_DATE,
				 LAST_UPDATE_LOGIN,
         GEN_PK)
                 VALUES
				 (l_access_list(i),
          l_user_list(i),
				  l_query_id_list(i),
          l_variable_id_list(i),
				  fnd_global.user_id,
				  sysdate,
				  fnd_global.user_id,
				  sysdate,
				  fnd_global.login_id,
          l_access_list(i));

Line: 688

  CSM_UTIL_PKG.LOG('Completed Query Variable Inserts for User id: '|| p_user_id, 'CSM_QUERY_EVENT_PKG.REFRESH_USER',FND_LOG.LEVEL_PROCEDURE);