The following lines contain the word 'select', 'insert', 'update' or 'delete':
delete from as_leads_log log
where log.log_id <>
( select max(log1.log_id)
from as_leads_log log1
where log1.lead_id = log.lead_id
and log1.last_update_date = log.last_update_date);
update as_leads_log
set object_version_number = nvl(object_version_number,0) + 1, log_start_date = last_update_date,
log_end_date = null,
log_active_days = null,
endday_log_flag = 'N',
current_log = 0;
update as_leads_log log
set object_version_number = nvl(object_version_number,0) + 1, log.log_end_date =
( select min(log1.last_update_date)
from as_leads_log log1
where log1.lead_id = log.lead_id
and log1.last_update_date > log.last_update_date);
update as_leads_log log
set object_version_number = nvl(object_version_number,0) + 1, log.log_end_date = log.log_start_date
where log.log_end_date is null;
update as_leads_log
set object_version_number = nvl(object_version_number,0) + 1, log_active_days = trunc(log_end_date) - trunc(log_start_date)
where log_active_days is null;
update as_leads_log log
set object_version_number = nvl(object_version_number,0) + 1, log.endday_log_flag = 'Y'
where log.last_update_date =
( select max(log1.last_update_date)
from as_leads_log log1
where log1.lead_id = log.lead_id
and trunc(log1.last_update_date) = trunc(log.last_update_date) );
update as_leads_log log
set object_version_number = nvl(object_version_number,0) + 1, log.current_log = 1
where log.last_update_date =
( select max(log1.last_update_date)
from as_leads_log log1
where log1.lead_id = log.lead_id );
select PROGRAM_UPDATE_DATE
from as_last_run_dates
where PROGRAM_NAME = 'ASXRSSM';
select min(LAST_UPDATE_DATE)
from as_leads_log;
DELETE FROM AS_LLOG_STATUS_SUMMARY
WHERE lead_id in
( select lead_id
from as_leads_log
where last_update_date >= l_last_refresh_date );
INSERT INTO AS_LLOG_STATUS_SUMMARY
( CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
REQUEST_ID,
PROGRAM_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_UPDATE_DATE,
LEAD_ID,
DECISION_DATE,
STATUS_CODE,
STATUS_START_DATE,
STATUS_END_DATE,
STATUS_DAYS,
CURRENT_STATUS
)
SELECT SYSDATE CREATION_DATE,
FND_GLOBAL.USER_ID CREATED_BY,
SYSDATE LAST_UPDATE_DATE,
FND_GLOBAL.USER_ID LAST_UPDATED_BY,
FND_GLOBAL.CONC_LOGIN_ID LAST_UPDATE_LOGIN,
FND_GLOBAL.Conc_Request_Id REQUEST_ID,
FND_GLOBAL.Conc_Program_Id PROGRAM_ID,
FND_GLOBAL.Prog_Appl_Id PROGRAM_APPLICATION_ID,
SYSDATE PROGRAM_UPDATE_DATE,
log.LEAD_ID LEAD_ID,
ld.DECISION_DATE DECISION_DATE,
log.STATUS_CODE STATUS_CODE,
min(log.LOG_START_DATE) STATUS_START_DATE,
max(log.LOG_END_DATE) STATUS_END_DATE,
sum(log.LOG_ACTIVE_DAYS) STATUS_DAYS,
sum(log.CURRENT_LOG) CURRENT_STATUS
FROM AS_LEADS_LOG log,
AS_LEADS_ALL ld
WHERE log.ENDDAY_LOG_FLAG = 'Y'
AND ld.LEAD_ID = log.LEAD_ID
AND log.LEAD_ID IN
( select lead_id
from as_leads_log
where last_update_date >= l_last_refresh_date )
GROUP BY log.LEAD_ID, ld.DECISION_DATE, log.STATUS_CODE;
UPDATE AS_LAST_RUN_DATES
SET PROGRAM_UPDATE_DATE = l_this_refresh_date,
LAST_UPDATE_DATE = sysdate,
LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
LAST_UPDATE_LOGIN = FND_GLOBAL.CONC_LOGIN_ID,
REQUEST_ID = FND_GLOBAL.Conc_Request_Id,
PROGRAM_APPLICATION_ID = FND_GLOBAL.Prog_Appl_Id,
PROGRAM_ID = FND_GLOBAL.Conc_Program_Id
WHERE PROGRAM_NAME = 'ASXRSSM';
INSERT INTO AS_LAST_RUN_DATES
( LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
PROGRAM_NAME
)
VALUES
( sysdate,
FND_GLOBAL.USER_ID,
sysdate,
FND_GLOBAL.USER_ID,
FND_GLOBAL.CONC_LOGIN_ID,
FND_GLOBAL.Conc_Request_Id,
FND_GLOBAL.Prog_Appl_Id,
FND_GLOBAL.Conc_Program_Id,
l_this_refresh_date,
'ASXRSSM'
);