APPS.CSM_CUSTMZ_VIEWS_EVENT_PKG SQL Statements

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

Line: 23

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

Line: 29

CURSOR 	c_delete IS
--Delete the records that have been removed from the base table
SELECT 	 ACC.USER_ID,
         ACC.CUST_VIEW_ID,
         ACC.ACCESS_ID
FROM 	 CSM_CUSTOMIZATION_VIEWS_ACC ACC
WHERE NOT EXISTS (SELECT 1
                  FROM 	CSM_CUSTOMIZATION_VIEWS B
                  WHERE  B.CUST_VIEW_ID = ACC.CUST_VIEW_ID)
UNION ALL
--Delete the records that have been personalized at a "higher" level
SELECT 	 ACC.USER_ID,
         ACC.CUST_VIEW_ID,
         ACC.ACCESS_ID
FROM 	 CSM_CUSTOMIZATION_VIEWS_ACC ACC,
         CSM_CUSTOMIZATION_VIEWS BACC  --to get cust_view_key,level_id,level_value for that cust_view_id
WHERE   BACC.CUST_VIEW_ID=ACC.CUST_VIEW_ID
AND     EXISTS(SELECT 1
               FROM  CSM_CUSTOMIZATION_VIEWS B,
                     ASG_USER AU
               WHERE B.PAGE_NAME=BACC.PAGE_NAME
               AND   B.REGION_NAME=BACC.REGION_NAME
               AND   B.CUST_VIEW_KEY=BACC.CUST_VIEW_KEY
               AND   ACC.USER_ID = AU.USER_ID
               AND   AU.USER_ID  = AU.OWNER_ID
               AND  (
                     (BACC.LEVEL_ID = 10001
                      AND BACC.LEVEL_VALUE = 0
                      AND B.LEVEL_ID=10003
                      AND B.LEVEL_VALUE = AU.RESPONSIBILITY_ID) --Site to Resp
                  OR (BACC.LEVEL_ID = 10001
                      AND BACC.LEVEL_VALUE = 0
                      AND B.LEVEL_ID=10004
                      AND B.LEVEL_VALUE = AU.USER_ID) -- Site to User
           	  OR (BACC.LEVEL_ID = 10003
                      AND BACC.LEVEL_VALUE = AU.RESPONSIBILITY_ID
           	      AND B.LEVEL_ID=10004
                      AND B.LEVEL_VALUE = AU.USER_ID)--Resp to User
	             )
              ) ;

Line: 72

CURSOR 	c_update(b_lastrundate DATE) IS
SELECT 	ACC.USER_ID,
        ACC.CUST_VIEW_ID,
        ACC.ACCESS_ID
FROM 	CSM_CUSTOMIZATION_VIEWS_ACC ACC
WHERE  EXISTS
       (SELECT 1 FROM CSM_CUSTOMIZATION_VIEWS B
        WHERE  B.CUST_VIEW_ID = ACC.CUST_VIEW_ID
        AND    B.LAST_UPDATE_DATE > b_lastrundate );

Line: 84

CURSOR 	c_insert IS
 SELECT  AU.USER_ID,
         B.CUST_VIEW_ID,
         1 ACCESS_ID
 FROM  CSM_CUSTOMIZATION_VIEWS B,
       ASG_USER AU
 WHERE AU.USER_ID  = AU.OWNER_ID
 AND   (
        (B.LEVEL_ID=10004 AND B.LEVEL_VALUE = AU.USER_ID)
        OR
	--If perz at resp level, verify that no User level perz exists
        (B.LEVEL_ID=10003 AND B.LEVEL_VALUE = AU.RESPONSIBILITY_ID
         AND NOT EXISTS( SELECT 1
	                 FROM CSM_CUSTOMIZATION_VIEWS B1
	  	 	 WHERE B.PAGE_NAME = B1.PAGE_NAME
			 AND   B.REGION_NAME = B1.REGION_NAME
			 AND   B.CUST_VIEW_KEY = B1.CUST_VIEW_KEY
   	                 AND   B1.LEVEL_ID = 10004
	                 AND   B1.LEVEL_VALUE = AU.USER_ID)
                       )
        OR
	--If perz at site level, verify that no resp and User level perz exists
        (B.LEVEL_ID=10001 AND B.LEVEL_VALUE=0
         AND NOT EXISTS( SELECT 1
	                 FROM CSM_CUSTOMIZATION_VIEWS B1
                         WHERE B.PAGE_NAME=B1.PAGE_NAME
                         AND   B.REGION_NAME=B1.REGION_NAME
                         AND   B.CUST_VIEW_KEY=B1.CUST_VIEW_KEY
                         AND   B1.LEVEL_ID=10004
                         AND   B1.LEVEL_VALUE=AU.USER_ID
                       )
         AND NOT EXISTS( SELECT 1
                         FROM CSM_CUSTOMIZATION_VIEWS B1
                         WHERE B.PAGE_NAME=B1.PAGE_NAME
                         AND   B.REGION_NAME=B1.REGION_NAME
                         AND   B.CUST_VIEW_KEY=B1.CUST_VIEW_KEY
                         AND   B1.LEVEL_ID=10003
                         AND   B1.LEVEL_VALUE=AU.RESPONSIBILITY_ID)
		       )
       )
 AND   NOT EXISTS (SELECT 1
                   FROM   CSM_CUSTOMIZATION_VIEWS_ACC ACC
                   WHERE  B.CUST_VIEW_ID = ACC.CUST_VIEW_ID
                   AND    AU.USER_ID = ACC.USER_ID );

Line: 158

   OPEN   c_delete;

Line: 159

   FETCH  c_delete BULK COLLECT INTO l_tab;

Line: 160

   CLOSE  c_delete;

Line: 163

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

Line: 168

      CSM_ACC_PKG.DELETE_ACC(
      p_publication_item_names => g_cust_view_pubi_name,
	  p_acc_table_name         => g_cust_view_acc_tab_name ,
	  p_user_id                => l_tab(I).USER_ID,
	  p_pk1_name               => g_cust_view_pk1_name,
	  p_pk1_num_value          => l_tab(I).CUST_VIEW_ID);

Line: 181

  l_tab.DELETE;

Line: 185

  OPEN  c_update(l_lastrundate.LAST_RUN_DATE);

Line: 186

  FETCH c_update BULK COLLECT INTO l_tab;

Line: 187

  CLOSE c_update;

Line: 189

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

Line: 194

    CSM_ACC_PKG.UPDATE_ACC(
    p_publication_item_names => g_cust_view_pubi_name,
    p_acc_table_name         => g_cust_view_acc_tab_name ,
    p_user_id                => l_tab(I).USER_ID,
    p_access_id              => l_tab(I).ACCESS_ID);

Line: 203

  CSM_UTIL_PKG.LOG('UPDATE Successful ',
                             'CSM_CUSTMZ_VIEWS_EVENT_PKG.Refresh_Acc',FND_LOG.LEVEL_PROCEDURE);

Line: 205

  l_tab.DELETE;

Line: 208

  OPEN  c_insert;

Line: 209

  FETCH c_insert BULK COLLECT INTO l_tab;

Line: 210

  CLOSE c_insert;

Line: 212

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

Line: 217

    CSM_ACC_PKG.INSERT_ACC(
      p_publication_item_names => g_cust_view_pubi_name,
	  p_acc_table_name         => g_cust_view_acc_tab_name ,
	  p_seq_name               => g_cust_view_seq_name,
	  p_user_id                => l_tab(I).USER_ID,
	  p_pk1_name               => g_cust_view_pk1_name,
	  p_pk1_num_value          => l_tab(I).CUST_VIEW_ID);

Line: 228

  CSM_UTIL_PKG.LOG('INSERTION Successful ',
                             'CSM_CUSTMZ_VIEWS_EVENT_PKG.Refresh_Acc',FND_LOG.LEVEL_PROCEDURE);

Line: 232

  UPDATE jtm_con_request_data
  SET    last_run_date   = sysdate
  WHERE  package_name =  g_cust_view_pkg_name
  AND    procedure_name = g_cust_view_api_name;

Line: 262

CURSOR 	c_insert(b_user_id NUMBER) IS
 SELECT  AU.USER_ID,
         B.CUST_VIEW_ID,
         1 ACCESS_ID
 FROM  CSM_CUSTOMIZATION_VIEWS B,
       ASG_USER AU
 WHERE AU.USER_ID  = AU.OWNER_ID
 AND   AU.USER_ID = b_user_id
 AND   (
        (B.LEVEL_ID=10004 AND B.LEVEL_VALUE = AU.USER_ID)
        OR
	--If perz at resp level, verify that no User level perz exists
        (B.LEVEL_ID=10003 AND B.LEVEL_VALUE = AU.RESPONSIBILITY_ID
         AND NOT EXISTS( SELECT 1
	                 FROM CSM_CUSTOMIZATION_VIEWS B1
	  	 	 WHERE B.PAGE_NAME = B1.PAGE_NAME
			 AND   B.REGION_NAME = B1.REGION_NAME
			 AND   B.CUST_VIEW_KEY = B1.CUST_VIEW_KEY
   	                 AND   B1.LEVEL_ID = 10004
	                 AND   B1.LEVEL_VALUE = AU.USER_ID)
                       )
        OR
	--If perz at site level, verify that no resp and User level perz exists
        (B.LEVEL_ID=10001 AND B.LEVEL_VALUE=0
         AND NOT EXISTS( SELECT 1
	                 FROM CSM_CUSTOMIZATION_VIEWS B1
                         WHERE B.PAGE_NAME=B1.PAGE_NAME
                         AND   B.REGION_NAME=B1.REGION_NAME
                         AND   B.CUST_VIEW_KEY=B1.CUST_VIEW_KEY
                         AND   B1.LEVEL_ID=10004
                         AND   B1.LEVEL_VALUE=AU.USER_ID
                       )
         AND NOT EXISTS( SELECT 1
                         FROM CSM_CUSTOMIZATION_VIEWS B1
                         WHERE B.PAGE_NAME=B1.PAGE_NAME
                         AND   B.REGION_NAME=B1.REGION_NAME
                         AND   B.CUST_VIEW_KEY=B1.CUST_VIEW_KEY
                         AND   B1.LEVEL_ID=10003
                         AND   B1.LEVEL_VALUE=AU.RESPONSIBILITY_ID)
		       )
       );

Line: 322

  DELETE FROM CSM_CUSTOMIZATION_VIEWS_ACC WHERE USER_ID=p_user_id;

Line: 325

  OPEN c_insert(p_user_id);

Line: 326

  FETCH c_insert BULK COLLECT INTO l_tab;

Line: 327

  CLOSE c_insert;

Line: 329

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

Line: 334

    CSM_ACC_PKG.INSERT_ACC(
      p_publication_item_names => g_cust_view_pubi_name,
	  p_acc_table_name         => g_cust_view_acc_tab_name ,
	  p_seq_name               => g_cust_view_seq_name,
	  p_user_id                => l_tab(I).USER_ID,
	  p_pk1_name               => g_cust_view_pk1_name,
	  p_pk1_num_value          => l_tab(I).CUST_VIEW_ID);

Line: 343

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