APPS.CSM_AD_SRCH_RESULT_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_result_pkg_name
   AND    procedure_name = g_ad_sch_result_api_name;

Line: 41

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

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

Line: 59

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

   SELECT CSM_AD_SEARCH_RESULT_ACC_S.NEXTVAL
   FROM   DUAL;

Line: 73

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

Line: 119

   OPEN  c_delete;

Line: 120

   FETCH c_delete BULK COLLECT INTO l_tab;

Line: 121

   CLOSE c_delete;

Line: 123

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

Line: 132

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

Line: 140

    l_tab.DELETE;

Line: 145

   OPEN  c_update(l_lastrundate.Last_run_date);

Line: 146

   FETCH c_update BULK COLLECT INTO l_tab;

Line: 147

   CLOSE c_update;

Line: 149

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

Line: 158

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

Line: 168

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

Line: 171

    l_tab.DELETE;

Line: 175

   OPEN c_insert;

Line: 176

   FETCH c_insert BULK COLLECT INTO l_tab;

Line: 177

   CLOSE c_insert;

Line: 179

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

Line: 188

      INSERT INTO CSM_AD_SEARCH_RESULT_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: 223

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

Line: 226

   UPDATE JTM_CON_REQUEST_DATA
   SET LAST_RUN_DATE = SYSDATE
   WHERE  package_name =  g_ad_sch_result_pkg_name
   AND    procedure_name = g_ad_sch_result_api_name;