APPS.CSM_AD_SRCH_REGION_EVENT_PKG SQL Statements

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

Line: 19

    g_access_list.DELETE;

Line: 34

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

Line: 39

CURSOR c_delete IS
  SELECT ACC.ACCESS_ID,
         ACC.ID
  FROM   CSM_AD_SEARCH_REGION_ACC ACC
  WHERE  NOT EXISTS (SELECT 1 FROM  CSM_AD_SEARCH_REGION_VIEW B
                     WHERE  B.ID=ACC.ID
                     AND EXISTS  (SELECT 1 FROM  CSM_AD_SEARCH_TITLE_VIEW C
                     WHERE C.ID= B.SEARCH_TYPE_ID));

Line: 48

CURSOR c_update(b_lastrundate DATE) IS
   SELECT  ACC.ACCESS_ID,
           ACC.ID
   FROM    CSM_AD_SEARCH_REGION_ACC ACC
   WHERE   EXISTS (SELECT 1 FROM CSM_AD_SEARCH_REGION_VIEW B
                   WHERE  B.ID=ACC.ID
                   AND    B.LAST_UPDATE_DATE > b_lastrundate);

Line: 57

CURSOR c_insert IS
   SELECT 1 ACCESS_ID,
          B.ID
   FROM   CSM_AD_SEARCH_REGION_VIEW B
   WHERE  NOT EXISTS (SELECT 1 FROM  CSM_AD_SEARCH_REGION_ACC ACC
                      WHERE  ACC.ID=B.ID
                      OR  NOT EXISTS(SELECT 1 FROM  CSM_AD_SEARCH_TITLE_VIEW C
                      WHERE C.ID= B.SEARCH_TYPE_ID));

Line: 67

 SELECT CSM_AD_SEARCH_REGION_ACC_S.NEXTVAL
 FROM   DUAL;

Line: 71

   SELECT USER_ID
   FROM   ASG_USER
   WHERE  USER_ID =OWNER_ID
   AND    ENABLED='Y';

Line: 117

   OPEN  c_delete;

Line: 118

   FETCH c_delete BULK COLLECT INTO l_tab;

Line: 119

   CLOSE c_delete;

Line: 121

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

Line: 130

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

Line: 139

    l_tab.DELETE;

Line: 143

   OPEN  c_update(l_lastrundate.Last_run_date);

Line: 144

   FETCH c_update BULK COLLECT INTO l_tab;

Line: 145

   CLOSE c_update;

Line: 147

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

Line: 156

     UPDATE CSM_AD_SEARCH_REGION_ACC
      SET
          LAST_UPDATE_DATE=SYSDATE,
          LAST_UPDATED_BY=1,
          LAST_UPDATE_LOGIN=1
      WHERE  ACCESS_ID=l_tab(I).ACCESS_ID;

Line: 166

   CSM_UTIL_PKG.LOG('UPDATE Successful ',
                              'CSM_AD_SEARCH_REGION_EVENT_PKG.REFRESH_ACC',FND_LOG.LEVEL_PROCEDURE);

Line: 169

    l_tab.DELETE;

Line: 173

   OPEN c_insert;

Line: 174

   FETCH c_insert BULK COLLECT INTO l_tab;

Line: 175

   CLOSE c_insert;

Line: 177

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

Line: 186

      INSERT INTO CSM_AD_SEARCH_REGION_ACC
      ( ACCESS_ID,
        ID,
        USER_ID,
        COUNTER,
        CREATION_DATE,
        CREATED_BY,
        LAST_UPDATE_DATE,
        LAST_UPDATED_BY,
        LAST_UPDATE_LOGIN
      )
      VALUES
      ( l_access_id,
        l_tab(I).ID,
        1,
        1,
        SYSDATE,
        1,
        SYSDATE,
        1,
        1
      );

Line: 218

    l_tab.DELETE;

Line: 221

   CSM_UTIL_PKG.LOG('INSERTION Successful ',
                              'CSM_AD_SEARCH_REGION_EVENT_PKG.REFRESH_ACC',FND_LOG.LEVEL_PROCEDURE);

Line: 224

   UPDATE JTM_CON_REQUEST_DATA
   SET LAST_RUN_DATE = SYSDATE
   WHERE  package_name =  g_ad_sch_region_pkg_name
   AND    procedure_name = g_ad_sch_region_api_name;