APPS.CSM_AD_SIMPLE_SRCH_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_simple_sch_pkg_name
   AND    procedure_name = g_ad_simple_sch_api_name;

Line: 41

CURSOR c_delete IS
   SELECT  ACC.ACCESS_ID,
           ACC.ID
   FROM    CSM_AD_SIMPLE_SEARCH_ACC ACC
   WHERE   NOT EXISTS (SELECT 1 FROM  CSM_AD_SIMPLE_SEARCH_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: 51

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

Line: 60

CURSOR c_insert IS
   SELECT 1 ACCESS_ID,
          B.ID
   FROM   CSM_AD_SIMPLE_SEARCH_VIEW B
   WHERE  NOT EXISTS (SELECT 1 FROM  CSM_AD_SIMPLE_SEARCH_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: 70

   SELECT CSM_AD_SIMPLE_SEARCH_ACC_S.NEXTVAL
   FROM   DUAL;

Line: 74

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

Line: 120

   OPEN  c_delete;

Line: 121

   FETCH c_delete BULK COLLECT INTO l_tab;

Line: 122

   CLOSE c_delete;

Line: 124

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

Line: 133

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

Line: 142

    l_tab.DELETE;

Line: 147

   OPEN  c_update(l_lastrundate.Last_run_date);

Line: 148

   FETCH c_update BULK COLLECT INTO l_tab;

Line: 149

   CLOSE c_update;

Line: 151

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

Line: 160

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

Line: 170

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

Line: 173

    l_tab.DELETE;

Line: 177

   OPEN c_insert;

Line: 178

   FETCH c_insert BULK COLLECT INTO l_tab;

Line: 179

   CLOSE c_insert;

Line: 181

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

Line: 190

      INSERT INTO CSM_AD_SIMPLE_SEARCH_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: 222

    l_tab.DELETE;

Line: 225

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

Line: 228

   UPDATE JTM_CON_REQUEST_DATA
   SET LAST_RUN_DATE = SYSDATE
   WHERE  package_name =  g_ad_simple_sch_pkg_name
   AND    procedure_name = g_ad_simple_sch_api_name;