The following lines contain the word 'select', 'insert', 'update' or 'delete':
g_access_list.DELETE;
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;
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));
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);
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));
SELECT CSM_AD_SIMPLE_SEARCH_ACC_S.NEXTVAL
FROM DUAL;
SELECT AU.USER_ID
FROM ASG_USER AU
WHERE AU.USER_ID = AU.OWNER_ID
AND AU.ENABLED='Y' ;
OPEN c_delete;
FETCH c_delete BULK COLLECT INTO l_tab;
CLOSE c_delete;
CSM_UTIL_PKG.LOG('Entering DELETE to remove ' || l_tab.count||' records',
'CSM_AD_SIMPLE_SRCH_EVENT_PKG.REFRESH_ACC',FND_LOG.LEVEL_PROCEDURE);
DELETE FROM CSM_AD_SIMPLE_SEARCH_ACC WHERE ACCESS_ID=l_tab(I).ACCESS_ID;
l_tab.DELETE;
OPEN c_update(l_lastrundate.Last_run_date);
FETCH c_update BULK COLLECT INTO l_tab;
CLOSE c_update;
CSM_UTIL_PKG.LOG('Entering UPDATE to update ' || l_tab.count||' records',
'CSM_AD_SIMPLE_SRCH_EVENT_PKG.REFRESH_ACC',FND_LOG.LEVEL_PROCEDURE);
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;
CSM_UTIL_PKG.LOG('UPDATE Successful ',
'CSM_AD_SIMPLE_SRCH_EVENT_PKG.REFRESH_ACC',FND_LOG.LEVEL_PROCEDURE);
l_tab.DELETE;
OPEN c_insert;
FETCH c_insert BULK COLLECT INTO l_tab;
CLOSE c_insert;
CSM_UTIL_PKG.LOG('Entering INSERT to add ' || l_tab.count||' records',
'CSM_AD_SIMPLE_SRCH_EVENT_PKG.REFRESH_ACC',FND_LOG.LEVEL_PROCEDURE);
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
);
l_tab.DELETE;
CSM_UTIL_PKG.LOG('INSERTION Successful ',
'CSM_AD_SIMPLE_SRCH_EVENT_PKG.REFRESH_ACC',FND_LOG.LEVEL_PROCEDURE);
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;