[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
procedure UPDATE_EXTR_SCOPE is
l_count number;
l_last_update_date date;
l_last_updated_by number;
l_last_update_login number;
l_last_update_date := sysdate;
l_last_updated_by := FND_GLOBAL.USER_ID;
l_last_update_login := FND_GLOBAL.LOGIN_ID;
select count(*)
into l_count
from PJI_PJP_PROJ_EXTR_STATUS
where ROWNUM = 1;
insert into PA_PJI_PROJ_EVENTS_LOG
(
EVENT_TYPE,
EVENT_ID,
EVENT_OBJECT,
OPERATION_TYPE,
STATUS,
ATTRIBUTE1,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN
)
select
'PRG_CHANGE',
PA_PJI_PROJ_EVENTS_LOG_S.NEXTVAL,
-1,
'I',
'X',
prj.PROJECT_ID,
l_last_update_date,
l_last_updated_by,
l_creation_date,
l_created_by,
l_last_update_login
from
PA_PROJECTS_ALL prj,
PJI_PJP_PROJ_EXTR_STATUS pjp_status
where
prj.TEMPLATE_FLAG = 'N' and
prj.PROJECT_ID = pjp_status.PROJECT_ID (+) and
pjp_status.PROJECT_ID is null;
delete
from PJI_PJP_PROJ_EXTR_STATUS pjp
where not exists (select 1
from PA_PROJECTS_ALL prj
where prj.PROJECT_ID = pjp.PROJECT_ID);
update PJI_PJP_PROJ_EXTR_STATUS sts
set sts.PROJECT_ORGANIZATION_ID =
(
select prj.CARRYING_OUT_ORGANIZATION_ID
from PA_PROJECTS_ALL prj
where prj.PROJECT_ID = sts.PROJECT_ID
)
where exists
(
select 1
from PA_PROJECTS_ALL prj
where prj.PROJECT_ID = sts.PROJECT_ID and
prj.CARRYING_OUT_ORGANIZATION_ID <>
sts.PROJECT_ORGANIZATION_ID
);
insert into PJI_PJP_PROJ_EXTR_STATUS
(
PROJECT_ID,
PROJECT_ORGANIZATION_ID,
PROJECT_NAME,
PROJECT_TYPE_CLASS,
EXTRACTION_STATUS,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN
)
select
prj.PROJECT_ID,
prj.CARRYING_OUT_ORGANIZATION_ID PROJECT_ORGANIZATION_ID,
'PJI$NULL' PROJECT_NAME,
decode(pt.PROJECT_TYPE_CLASS_CODE,
'CAPITAL', 'C',
'CONTRACT', 'B',
'INDIRECT', 'I') PROJECT_TYPE_CLASS,
'F' EXTRACTION_STATUS,
l_last_update_date,
l_last_updated_by,
l_creation_date,
l_created_by,
l_last_update_login
from
PA_PROJECTS_ALL prj,
PA_PROJECT_TYPES_ALL pt,
PJI_PJP_PROJ_EXTR_STATUs pjp_status
where
prj.TEMPLATE_FLAG = 'N' and
prj.ORG_ID = pt.ORG_ID and /*5377131*/
prj.PROJECT_TYPE = pt.PROJECT_TYPE and
prj.PROJECT_ID = pjp_status.PROJECT_ID (+) and
pjp_status.PROJECT_ID is null;
delete
from PA_PJI_PROJ_EVENTS_LOG
where EVENT_TYPE in ('WBS_CHANGE',
'WBS_PUBLISH',
'PRG_CHANGE'
-- 'RBS_ASSOC', The source system depends on
-- 'RBS_PRG', updates from Project Performance
-- 'RBS_PUSH', processing of these events, so
-- 'RBS_DELETE' they must persist after truncate.
);
insert into PA_PJI_PROJ_EVENTS_LOG
(
EVENT_TYPE,
EVENT_ID,
EVENT_OBJECT,
OPERATION_TYPE,
STATUS,
ATTRIBUTE1,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN
)
select
'PRG_CHANGE',
PA_PJI_PROJ_EVENTS_LOG_S.NEXTVAL,
-1,
'I',
'X',
prj.PROJECT_ID,
l_last_update_date,
l_last_updated_by,
l_creation_date,
l_created_by,
l_last_update_login
from
PA_PROJECTS_ALL prj
where
prj.TEMPLATE_FLAG = 'N' and
not exists (select 1
from PA_XBS_DENORM den
where den.STRUCT_TYPE = 'PRG' and
den.SUP_PROJECT_ID = prj.PROJECT_ID) and
not exists (select 1
from PA_PJI_PROJ_EVENTS_LOG log
where log.EVENT_TYPE = 'PRG_CHANGE' and
log.EVENT_OBJECT = -1 and
log.ATTRIBUTE1 = prj.PROJECT_ID);
insert into PJI_PJP_PROJ_EXTR_STATUS pjp_i
(
PROJECT_ID,
PROJECT_ORGANIZATION_ID,
PROJECT_NAME,
PROJECT_TYPE_CLASS,
EXTRACTION_STATUS,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN
)
select
prj.PROJECT_ID,
prj.CARRYING_OUT_ORGANIZATION_ID PROJECT_ORGANIZATION_ID,
'PJI$NULL' PROJECT_NAME,
decode(pt.PROJECT_TYPE_CLASS_CODE,
'CAPITAL', 'C',
'CONTRACT', 'B',
'INDIRECT', 'I') PROJECT_TYPE_CLASS,
'F' EXTRACTION_STATUS,
l_last_update_date,
l_last_updated_by,
l_creation_date,
l_created_by,
l_last_update_login
from
PA_PROJECTS_ALL prj,
PA_PROJECT_TYPES_ALL pt
where
prj.TEMPLATE_FLAG = 'N' and
prj.ORG_ID = pt.ORG_ID and /*5377131*/
prj.PROJECT_TYPE = pt.PROJECT_TYPE;
end UPDATE_EXTR_SCOPE;
procedure UPDATE_ORG_EXTR_INFO is
begin
PJI_EXTRACTION_UTIL.UPDATE_ORG_EXTR_INFO;
end UPDATE_ORG_EXTR_INFO;
select to_number(value)
into l_db_block_size
from v$parameter
where name = 'db_block_size';
insert into PJI_SYSTEM_CONFIG_HIST
(
REQUEST_ID,
USER_NAME,
PROCESS_NAME,
RUN_TYPE,
PARAMETERS,
CONFIG_PROJ_PERF_FLAG,
CONFIG_COST_FLAG,
CONFIG_PROFIT_FLAG,
CONFIG_UTIL_FLAG,
START_DATE,
END_DATE,
COMPLETION_TEXT
)
select
FND_GLOBAL.CONC_REQUEST_ID REQUEST_ID,
substr(FND_GLOBAL.USER_NAME, 1, 10) USER_NAME,
'STAGE3' PROCESS_NAME,
'CLEANALL' RUN_TYPE,
substr(p_check || ', ' ||
p_fpm_upgrade || ', ' ||
p_recover, 1, 240) PARAMETERS,
null CONFIG_PROJ_PERF_FLAG,
null CONFIG_COST_FLAG,
null CONFIG_PROFIT_FLAG,
null CONFIG_UTIL_FLAG,
sysdate START_DATE,
null END_DATE,
null COMPLETION_TEXT
from
dual;
update FND_PROFILE_OPTION_VALUES
set PROFILE_OPTION_VALUE = 'N'
where APPLICATION_ID = 1292 and
-- LEVEL_ID = 10001 and
PROFILE_OPTION_ID in
(select PROFILE_OPTION_ID
from FND_PROFILE_OPTIONS
where APPLICATION_ID = 1292 and
PROFILE_OPTION_NAME = 'PJI_SUM_CLEANALL');
delete from PJI_MT_PRC_STEPS where PROCESS_NAME like (PJI_PJP_SUM_MAIN.g_process || '%');
delete from PJI_SYSTEM_PARAMETERS where NAME like (PJI_PJP_SUM_MAIN.g_process || '%$%') or
NAME like 'PJI_FPM_UPGRADE' or
NAME like 'PJI_PTC_UPGRADE' or /*4882640 */
NAME like 'PJP_FPM_UPGRADE_DATE' or
NAME like 'LAST_PJP_EXTR_DATE%';
delete from PJI_SYSTEM_PRC_STATUS where PROCESS_NAME like (PJI_PJP_SUM_MAIN.g_process || '%');
delete from PA_PJI_PROJ_EVENTS_LOG where event_type = 'PLANTYPE_UPG'; /*4882640 */
update PJI_SYSTEM_CONFIG_HIST
set END_DATE = sysdate,
COMPLETION_TEXT = 'Normal completion'
where PROCESS_NAME = 'STAGE3' and
END_DATE is null;
update PJI_SYSTEM_CONFIG_HIST
set END_DATE = sysdate,
COMPLETION_TEXT = l_sqlerrm
where PROCESS_NAME = 'STAGE3' and
END_DATE is null;
select trunc(last_update_date)
into l_last_proj_extr_date
from PJI_PJP_PROJ_EXTR_STATUS
where project_id = p_project_id ;