APPS.CSM_PAGE_PERZ_DELTA_EVENT_PKG SQL Statements

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

Line: 20

  SELECT NVL(LAST_RUN_DATE, to_date('1','J')) LAST_RUN_DATE
  FROM   JTM_CON_REQUEST_DATA
  WHERE  package_name =  g_page_perz_delta_pkg_name
  AND    procedure_name = g_page_perz_delta_api_name;

Line: 26

CURSOR c_delete IS
  SELECT ACC.ACCESS_ID,
         ACC.PAGE_PERZ_ID,
         ACC.LEVEL_ID,
         ACC.LEVEL_VALUE,
         ACC.DELTA_FILE_NAME,
         ACC.DELTA_FILE_TYPE,
         ACC.DELTA_SERVER_VERSION,
         ACC.DELTA_CLIENT_VERSION,
         ACC.FILE_DATA,
         ACC.USER_ID
  FROM  CSM_PAGE_PERZ_DELTA_ACC ACC,
        ASG_USER AU
  WHERE ACC.USER_ID=AU.USER_ID
  AND   AU.USER_ID =AU.OWNER_ID
  AND   EXISTS(SELECT 1
               FROM  CSM_PAGE_PERZ_DELTA CPPD
               WHERE CPPD.PAGE_PERZ_ID=ACC.PAGE_PERZ_ID
               AND   CPPD.LEVEL_ID<>10001
               AND  (
                     (ACC.LEVEL_ID = 10001
                      AND ACC.LEVEL_VALUE = 0
                      AND CPPD.LEVEL_ID=10003
                      AND CPPD.LEVEL_VALUE = AU.RESPONSIBILITY_ID) --Site to Resp
                  OR (ACC.LEVEL_ID = 10001
                      AND ACC.LEVEL_VALUE = 0
                      AND CPPD.LEVEL_ID=10004
                      AND CPPD.LEVEL_VALUE = AU.USER_ID) -- Site to User
            	  OR (ACC.LEVEL_ID = 10003
                      AND ACC.LEVEL_VALUE = AU.RESPONSIBILITY_ID
             	      AND CPPD.LEVEL_ID=10004
                      AND CPPD.LEVEL_VALUE = AU.USER_ID)--Resp to User
	               )
              )
  UNION ALL
  SELECT ACC.ACCESS_ID,
         ACC.PAGE_PERZ_ID,
         ACC.LEVEL_ID,
         ACC.LEVEL_VALUE,
         ACC.DELTA_FILE_NAME,
         ACC.DELTA_FILE_TYPE,
         ACC.DELTA_SERVER_VERSION,
         ACC.DELTA_CLIENT_VERSION,
         ACC.FILE_DATA,
         ACC.USER_ID
  FROM  CSM_PAGE_PERZ_DELTA_ACC ACC
  WHERE NOT EXISTS (SELECT 1 FROM CSM_PAGE_PERZ CPP
                    WHERE CPP.PAGE_PERZ_ID= ACC.PAGE_PERZ_ID)
        OR
        NOT EXISTS (SELECT 1 FROM CSM_PAGE_PERZ_DELTA CPPD
                    WHERE CPPD.PAGE_PERZ_ID= ACC.PAGE_PERZ_ID
         		    AND   CPPD.LEVEL_ID    = ACC.LEVEL_ID
	         	    AND   CPPD.LEVEL_VALUE = ACC.LEVEL_VALUE);

Line: 82

CURSOR 	c_update(b_lastrundate DATE) IS
  SELECT ACC.ACCESS_ID,
         ACC.PAGE_PERZ_ID,
         ACC.LEVEL_ID,
         ACC.LEVEL_VALUE,
         CPPD.DELTA_FILE_NAME,
         CPPD.DELTA_FILE_TYPE,
         CPPD.DELTA_SERVER_VERSION,
         CPPD.DELTA_CLIENT_VERSION,
         CPPD.FILE_DATA,
         ACC.USER_ID
  FROM  CSM_PAGE_PERZ_DELTA_ACC ACC,
        CSM_PAGE_PERZ_DELTA CPPD
  WHERE CPPD.PAGE_PERZ_ID= ACC.PAGE_PERZ_ID
  AND   CPPD.LEVEL_ID    = ACC.LEVEL_ID
  AND   CPPD.LEVEL_VALUE = ACC.LEVEL_VALUE
  AND   CPPD.LAST_UPDATE_DATE > b_lastrundate ;

Line: 102

CURSOR  c_insert IS
  SELECT 1 ACCESS_ID,
         CPPD.PAGE_PERZ_ID,
         CPPD.LEVEL_ID,
         CPPD.LEVEL_VALUE,
         CPPD.DELTA_FILE_NAME,
         CPPD.DELTA_FILE_TYPE,
         CPPD.DELTA_SERVER_VERSION,
         CPPD.DELTA_CLIENT_VERSION,
         CPPD.FILE_DATA,
         AU.USER_ID
  FROM CSM_PAGE_PERZ CPP,
       CSM_PAGE_PERZ_DELTA CPPD,
       ASG_USER AU
  WHERE CPP.PAGE_PERZ_ID = CPPD.PAGE_PERZ_ID
  AND   AU.USER_ID =AU.OWNER_ID
  AND ( (CPPD.LEVEL_VALUE = AU.USER_ID
         AND CPPD.LEVEL_ID = 10004)
      OR
        (CPPD.LEVEL_VALUE = AU.RESPONSIBILITY_ID
         AND CPPD.LEVEL_ID = 10003
 	     AND NOT EXISTS (SELECT 1
		                 FROM CSM_PAGE_PERZ_DELTA CPPD1
	                     WHERE CPPD1.PAGE_PERZ_ID = CPPD.PAGE_PERZ_ID
	                     AND   CPPD1.LEVEL_ID = 10004
	                     AND   CPPD1.LEVEL_VALUE= AU.USER_ID ))
      OR
       (CPPD.LEVEL_VALUE=0
        AND  CPPD.LEVEL_ID = 10001
 	    AND NOT EXISTS (SELECT 1
		                FROM CSM_PAGE_PERZ_DELTA CPPD1
	                    WHERE CPPD1.PAGE_PERZ_ID = CPPD.PAGE_PERZ_ID
	                    AND   CPPD1.LEVEL_ID = 10003
	                    AND   CPPD1.LEVEL_VALUE= AU.RESPONSIBILITY_ID )
 	    AND NOT EXISTS (SELECT 1
	                    FROM CSM_PAGE_PERZ_DELTA CPPD1
	                    WHERE CPPD1.PAGE_PERZ_ID = CPPD.PAGE_PERZ_ID
	                    AND   CPPD1.LEVEL_ID = 10004
	                    AND   CPPD1.LEVEL_VALUE= AU.USER_ID ))
      )
  AND NOT EXISTS (SELECT 1
                  FROM CSM_PAGE_PERZ_DELTA_ACC ACC
                  WHERE ACC.PAGE_PERZ_ID = CPPD.PAGE_PERZ_ID
		          AND   ACC.LEVEL_ID = CPPD.LEVEL_ID
                  AND   ACC.LEVEL_VALUE = CPPD.LEVEL_VALUE
                  AND   ACC.USER_ID = AU.USER_ID);

Line: 150

 SELECT CSM_PAGE_PERZ_DELTA_ACC_S.NEXTVAL
 FROM DUAL;

Line: 189

  OPEN  c_delete;

Line: 190

  FETCH  c_delete BULK COLLECT INTO l_tab;

Line: 191

  CLOSE  c_delete;

Line: 193

  CSM_UTIL_PKG.LOG('Entering DELETE to remove ' || l_tab.count||' records',
                             'CSM_PAGE_PERZ_DELTA_EVENT_PKG.REFRESH_PAGE_PERZ_DELTA',FND_LOG.LEVEL_PROCEDURE);

Line: 200

   DELETE FROM CSM_PAGE_PERZ_DELTA_ACC WHERE ACCESS_ID=l_tab(I).ACCESS_ID;

Line: 208

  l_tab.DELETE;

Line: 212

  OPEN c_update(l_lastrundate.LAST_RUN_DATE);

Line: 213

  FETCH c_update BULK COLLECT INTO l_tab;

Line: 214

  CLOSE c_update;

Line: 216

  CSM_UTIL_PKG.LOG('Entering UPDATE to update ' || l_tab.count||' records',
                             'CSM_PAGE_PERZ_DELTA_EVENT_PKG.REFRESH_PAGE_PERZ_DELTA',FND_LOG.LEVEL_PROCEDURE);

Line: 223

    UPDATE CSM_PAGE_PERZ_DELTA_ACC
	SET
         DELTA_FILE_NAME=l_tab(I).DELTA_FILE_NAME,
         DELTA_FILE_TYPE=l_tab(I).DELTA_FILE_TYPE,
         DELTA_SERVER_VERSION=l_tab(I).DELTA_SERVER_VERSION,
         DELTA_CLIENT_VERSION=l_tab(I).DELTA_CLIENT_VERSION,
         FILE_DATA=l_tab(I).FILE_DATA,
         LAST_UPDATE_DATE=SYSDATE,
         LAST_UPDATED_BY=1,
         LAST_UPDATE_LOGIN=1
   	WHERE  ACCESS_ID=l_tab(I).ACCESS_ID;

Line: 238

  CSM_UTIL_PKG.LOG('UPDATE Successful ',
                             'CSM_PAGE_PERZ_DELTA_EVENT_PKG.REFRESH_PAGE_PERZ_DELTA',FND_LOG.LEVEL_PROCEDURE);

Line: 240

  l_tab.DELETE;

Line: 243

  OPEN c_insert;

Line: 244

  FETCH c_insert BULK COLLECT INTO l_tab;

Line: 245

  CLOSE c_insert;

Line: 247

  CSM_UTIL_PKG.LOG('Entering INSERT to add ' || l_tab.count||' records',
                            'CSM_PAGE_PERZ_DELTA_EVENT_PKG.REFRESH_PAGE_PERZ_DELTA',FND_LOG.LEVEL_PROCEDURE);

Line: 256

     INSERT INTO CSM_PAGE_PERZ_DELTA_ACC
     ( ACCESS_ID,
       PAGE_PERZ_ID,
       LEVEL_ID,
       LEVEL_VALUE,
       DELTA_FILE_NAME,
       DELTA_FILE_TYPE,
       DELTA_SERVER_VERSION,
       DELTA_CLIENT_VERSION,
       FILE_DATA,
       USER_ID,
       COUNTER,
       CREATED_BY,
       CREATION_DATE,
       LAST_UPDATED_BY,
       LAST_UPDATE_DATE,
       LAST_UPDATE_LOGIN
	 )
     VALUES
	 ( l_access_id,
           l_tab(I).PAGE_PERZ_ID,
           l_tab(I).LEVEL_ID,
           l_tab(I).LEVEL_VALUE,
           l_tab(I).DELTA_FILE_NAME,
           l_tab(I).DELTA_FILE_TYPE,
           l_tab(I).DELTA_SERVER_VERSION,
           l_tab(I).DELTA_CLIENT_VERSION,
           l_tab(I).FILE_DATA,
           l_tab(I).USER_ID,
 	   1,
 	   1,
 	   SYSDATE,
 	   1,
 	   SYSDATE,
 	   1
     );

Line: 299

  CSM_UTIL_PKG.LOG('INSERTION Successful ',
                             'CSM_PAGE_PERZ_DELTA_EVENT_PKG.REFRESH_PAGE_PERZ_DELTA',FND_LOG.LEVEL_PROCEDURE);

Line: 302

  UPDATE JTM_CON_REQUEST_DATA
  SET LAST_RUN_DATE = SYSDATE
  WHERE  package_name =  g_page_perz_delta_pkg_name
  AND    procedure_name = g_page_perz_delta_api_name;

Line: 331

CURSOR  c_insert(b_user_id NUMBER) IS
  SELECT 1 ACCESS_ID,
         CPPD.PAGE_PERZ_ID,
         CPPD.LEVEL_ID,
         CPPD.LEVEL_VALUE,
         CPPD.DELTA_FILE_NAME,
         CPPD.DELTA_FILE_TYPE,
         CPPD.DELTA_SERVER_VERSION,
         CPPD.DELTA_CLIENT_VERSION,
         CPPD.FILE_DATA,
         AU.USER_ID
  FROM CSM_PAGE_PERZ CPP,
       CSM_PAGE_PERZ_DELTA CPPD,
       ASG_USER AU
  WHERE AU.USER_ID=b_user_id
  AND   AU.USER_ID =AU.OWNER_ID
  AND CPP.PAGE_PERZ_ID = CPPD.PAGE_PERZ_ID
  AND ( (CPPD.LEVEL_VALUE = AU.USER_ID
         AND CPPD.LEVEL_ID = 10004)
      OR
        (CPPD.LEVEL_VALUE = AU.RESPONSIBILITY_ID
         AND CPPD.LEVEL_ID = 10003
 	     AND NOT EXISTS (SELECT 1
		                 FROM CSM_PAGE_PERZ_DELTA CPPD1
	                     WHERE CPPD1.PAGE_PERZ_ID = CPPD.PAGE_PERZ_ID
	                     AND   CPPD1.LEVEL_ID = 10004
	                     AND   CPPD1.LEVEL_VALUE= AU.USER_ID ))
      OR
       (CPPD.LEVEL_VALUE=0
        AND  CPPD.LEVEL_ID = 10001
 	    AND NOT EXISTS (SELECT 1
		                FROM CSM_PAGE_PERZ_DELTA CPPD1
	                    WHERE CPPD1.PAGE_PERZ_ID = CPPD.PAGE_PERZ_ID
	                    AND   CPPD1.LEVEL_ID = 10003
	                    AND   CPPD1.LEVEL_VALUE= AU.RESPONSIBILITY_ID )
 	    AND NOT EXISTS (SELECT 1
	                    FROM CSM_PAGE_PERZ_DELTA CPPD1
	                    WHERE CPPD1.PAGE_PERZ_ID = CPPD.PAGE_PERZ_ID
	                    AND   CPPD1.LEVEL_ID = 10004
	                    AND   CPPD1.LEVEL_VALUE= AU.USER_ID ))
      );

Line: 374

 SELECT CSM_PAGE_PERZ_DELTA_ACC_S.NEXTVAL
 FROM DUAL;

Line: 403

  DELETE FROM CSM_PAGE_PERZ_DELTA_ACC WHERE USER_ID=p_user_id;

Line: 406

  OPEN c_insert(p_user_id);

Line: 407

  FETCH c_insert BULK COLLECT INTO l_tab;

Line: 408

  CLOSE c_insert;

Line: 410

  CSM_UTIL_PKG.LOG('Entering INSERT to add ' || l_tab.count||' records for user',
                            'CSM_PAGE_PERZ_DELTA_EVENT_PKG.REFRESH_USER',FND_LOG.LEVEL_PROCEDURE);

Line: 419

     INSERT INTO CSM_PAGE_PERZ_DELTA_ACC
     ( ACCESS_ID,
       PAGE_PERZ_ID,
       LEVEL_ID,
       LEVEL_VALUE,
       DELTA_FILE_NAME,
       DELTA_FILE_TYPE,
       DELTA_SERVER_VERSION,
       DELTA_CLIENT_VERSION,
       FILE_DATA,
       USER_ID,
       COUNTER,
       CREATED_BY,
       CREATION_DATE,
       LAST_UPDATED_BY,
       LAST_UPDATE_DATE,
       LAST_UPDATE_LOGIN
	 )
     VALUES
	 ( l_access_id,
       l_tab(I).PAGE_PERZ_ID,
       l_tab(I).LEVEL_ID,
       l_tab(I).LEVEL_VALUE,
       l_tab(I).DELTA_FILE_NAME,
       l_tab(I).DELTA_FILE_TYPE,
       l_tab(I).DELTA_SERVER_VERSION,
       l_tab(I).DELTA_CLIENT_VERSION,
       l_tab(I).FILE_DATA,
       l_tab(I).USER_ID,
 	   1,
 	   1,
 	   SYSDATE,
 	   1,
 	   SYSDATE,
 	   1
     );

Line: 460

  CSM_UTIL_PKG.LOG('INSERTION Successful ',
                             'CSM_PAGE_PERZ_DELTA_EVENT_PKG.REFRESH_USER',FND_LOG.LEVEL_PROCEDURE);