The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT rowid, '@' || file_product || ':' || file_path || '/' || file_name,
REQUEST_ID, BATCH_ID
FROM FND_IREP_DEFERRED_LOAD_FILES
WHERE load_status = 'R' and REQUEST_ID is not Null
ORDER BY 4,3,2;
select NVL(STATUS, 'E'), START_TIME, FINISH_TIME,
NVL(ERROR_TEXT, DECODE(STATUS, NULL,
NVL(cp_message, 'Request complete but batch not updated.'),
Null)),
LOGFILE
into LDT_Status, LDT_Start, LDT_Finish, LDT_Errors, LDT_Logfile
from FND_LOADER_OPEN_INTERFACE
where BATCH_ID = BatchID_Array(i)
and LDT = LDT_Array(i);
update FND_IREP_DEFERRED_LOAD_FILES
set LOAD_STATUS = DECODE(LDT_Status, 'S', 'C', 'E'),
LOAD_ERRORS = LDT_Errors,
LOAD_START = LDT_Start,
LOAD_FINISH = LDT_Finish,
LOG_FILE = LDT_Logfile,
LAST_UPDATE_DATE = sysdate,
LAST_UPDATE_LOGIN = 0
where rowid = LDT_RowID_Array(i);
FND_LOADER_OPEN_INTERFACE_PKG.DELETE_BATCH(BatchID_Array(i));
FND_LOADER_OPEN_INTERFACE_PKG.DELETE_BATCH(BatchID_Array(i));
SELECT rowid, '@' || file_product || ':' || file_path || '/' || file_name
FROM FND_IREP_DEFERRED_LOAD_FILES
WHERE load_status = pStatus
ORDER BY file_path, file_product, file_name;
BatchID_Array(i-1) := FND_LOADER_OPEN_INTERFACE_PKG.INSERT_BATCH;
update FND_IREP_DEFERRED_LOAD_FILES
set load_status = BatchStatus_Array(LDT2Batch_Map(i)),
request_id = BatchReqID_Array(LDT2Batch_Map(i)),
batch_id = BatchID_Array(LDT2Batch_Map(i)),
LAST_UPDATE_DATE = sysdate,
LAST_UPDATE_LOGIN = 0,
LOAD_ERRORS = BatchError_Array(LDT2Batch_Map(i))
where rowid = LDT_RowID_Array(i);
select to_number(P_APPLTOP_ID)
into appltop_id
from dual;
Select ORACLE_USERNAME
into applsys_user
from fnd_oracle_userid
where oracle_id = 0;
insert into FND_IREP_DEFERRED_LOAD_FILES
(
FILE_PRODUCT, FILE_PATH, FILE_NAME,
FILE_VERSION, LOAD_STATUS, CREATED_BY, CREATION_DATE,
LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN
)
select
PF.PRODUCT_SHORT_NAME, PF.SUBDIR, PF.FILE_BASE || '.' || PF.FILE_EXTENSION,
PF.VERSION, 'N', 120, sysdate,
sysdate, 120, 0
from AD_PROCESSED_FILES_TEMP PF
where not exists
(
select 1
from FND_IREP_DEFERRED_LOAD_FILES LF
where FND_IREP_LOADER_PRIVATE.COMPARE_VERSIONS(
PF.VERSION, LF.FILE_VERSION) in ('=', '<')
and LF.FILE_NAME = PF.FILE_BASE || '.' || PF.FILE_EXTENSION
and LF.FILE_PATH = PF.SUBDIR
and LF.FILE_PRODUCT = PF.PRODUCT_SHORT_NAME
);
update FND_IREP_DEFERRED_LOAD_FILES F1
set LOAD_STATUS = 'X'
where F1.LOAD_STATUS <> 'X'
and F1.LOAD_STATUS <> 'C'
and exists (select Null
from FND_IREP_DEFERRED_LOAD_FILES F2
where F1.FILE_NAME = F2.FILE_NAME
and F1.FILE_PATH = F2.FILE_PATH
and F1.FILE_PRODUCT = F2.FILE_PRODUCT
and FND_IREP_LOADER_PRIVATE.COMPARE_VERSIONS(
F1.FILE_VERSION,F2.FILE_VERSION) = '<');
Update FND_IREP_DEFERRED_LOAD_FILES
Set LOAD_STATUS = 'E',
LOAD_ERRORS = 'Hung in Status R for 24 hours.'
where LOAD_STATUS = 'R'
and LAST_UPDATE_DATE < SYSDATE - 1;
select count(*)
into kount
from FND_IREP_DEFERRED_LOAD_FILES
where LOAD_STATUS = 'R';