APPS.CSM_AD_SRCH_TITLE_EVENT_PKG SQL Statements

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

Line: 19

      g_access_list.DELETE;

Line: 36

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

Line: 41

CURSOR c_delete IS
  SELECT ACC.ACCESS_ID,
         ACC.SEARCH_TYPE
  FROM   CSM_AD_SEARCH_TITLE_ACC ACC
  WHERE  NOT EXISTS (SELECT 1 FROM  CSM_AD_SEARCH_TITLE_VIEW B
                     WHERE  B.SEARCH_TYPE=ACC.SEARCH_TYPE);

Line: 48

CURSOR c_update(b_lastrundate DATE) IS
   SELECT  ACC.ACCESS_ID,
           ACC.SEARCH_TYPE
   FROM    CSM_AD_SEARCH_TITLE_ACC ACC
   WHERE   EXISTS (SELECT 1 FROM  CSM_AD_SEARCH_TITLE_VIEW B
                   WHERE  B.SEARCH_TYPE=ACC.SEARCH_TYPE
   AND    B.LAST_UPDATE_DATE > b_lastrundate);

Line: 57

CURSOR c_insert IS
   SELECT 1 ACCESS_ID,
          B.SEARCH_TYPE
   FROM   CSM_AD_SEARCH_TITLE_VIEW B
   WHERE  NOT EXISTS (SELECT 1 FROM  CSM_AD_SEARCH_TITLE_ACC ACC
                      WHERE  ACC.SEARCH_TYPE=B.SEARCH_TYPE);

Line: 65

   SELECT CSM_AD_SEARCH_TITLE_ACC_S.NEXTVAL
   FROM   DUAL;

Line: 69

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

Line: 115

   OPEN  c_delete;

Line: 116

   FETCH c_delete BULK COLLECT INTO l_tab;

Line: 117

   CLOSE c_delete;

Line: 119

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

Line: 128

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

Line: 137

    l_tab.DELETE;

Line: 141

   OPEN  c_update(l_lastrundate.Last_run_date);

Line: 142

   FETCH c_update BULK COLLECT INTO l_tab;

Line: 143

   CLOSE c_update;

Line: 145

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

Line: 155

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

Line: 165

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

Line: 168

    l_tab.DELETE;

Line: 172

   OPEN c_insert;

Line: 173

   FETCH c_insert BULK COLLECT INTO l_tab;

Line: 174

   CLOSE c_insert;

Line: 176

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

Line: 185

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

Line: 218

    l_tab.DELETE;

Line: 221

   CSM_UTIL_PKG.LOG('INSERTION Successful ',
                             'CSM_AD_SRCH_TITLE_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_title_pkg_name
   AND    procedure_name = g_ad_sch_title_api_name;