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/*, Commented for bug 8916168
PJI_PROJ_EXTR_STATUS fm_status Added for bug 8661279 */
where
prj.TEMPLATE_FLAG = 'N' and
prj.PROJECT_ID = pjp_status.PROJECT_ID (+) and
PA_PROJECT_UTILS.CHECK_PRJ_STUS_ACTION_ALLOWED
(prj.PROJECT_STATUS_CODE, 'STATUS_REPORTING') = 'Y' and /* Added for bug 8916168 */
/*prj.PROJECT_ID = fm_status.PROJECT_ID and Added for bug 8661279 Commented for bug 8916168 */
pjp_status.PROJECT_ID is null
and prj.project_type <> 'AWARD_PROJECT'; /* Added for Bug 6450518 */
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);
/* This delete statement is added so that data in PJI_PJP_PROJ_EXTR_STATUS
is always in sync with data in PJI_PROJ_EXTR_STATUS.
Code added for bug 6748705 starts **** Commented for bug 9034593 ****
delete
from PJI_PJP_PROJ_EXTR_STATUS pjp
where not exists (select 1
from PJI_PROJ_EXTR_STATUS prj
where prj.PROJECT_ID = pjp.PROJECT_ID);
/* Below delete added for Bug# 9749751 */
-- Commented the delete for bug 11785181 - When project went to CLOSED status, then the below delete was deleting
-- the project from PJI_PJP_PROJ_EXTR_STATUS. Later if the status of the project was changed to APPROVED, then
-- the insert statement was again inserting the project in 'F' status. Because of this, UPPD was running in FULL
-- mode for this project and was inserting duplicates in UPDATE_PROGRAM_WBS. Hence, we will not delete a project
-- which has been summarized before from PJI_PJP_PROJ_EXTR_STATUS. It will remain in PJI_PJP_PROJ_EXTR_STATUS
-- with extraction status as 'I'. In INIT_PROCESS procedure, while inserting into pji_pjp_proj_batch_map we
-- will select only those projects which are eligible for status reporting.
/*delete
from PJI_PJP_PROJ_EXTR_STATUS pjp
where exists (select 1
from PA_PROJECTS_ALL prj
where prj.PROJECT_ID = pjp.PROJECT_ID
and PA_PROJECT_UTILS.CHECK_PRJ_STUS_ACTION_ALLOWED(prj.PROJECT_STATUS_CODE, 'STATUS_REPORTING') = 'N'); */
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/*, Commented for bug 8916168
PJI_PROJ_EXTR_STATUS fm_status Added for bug 6748705 */
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
PA_PROJECT_UTILS.CHECK_PRJ_STUS_ACTION_ALLOWED
(prj.PROJECT_STATUS_CODE, 'STATUS_REPORTING') = 'Y' and /* Added for bug 8916168 */
/*prj.PROJECT_ID = fm_status.project_id and Added for bug 6748705 Commented for bug 8916168 */
pjp_status.PROJECT_ID is null
and prj.project_type <> 'AWARD_PROJECT'; /* Added for Bug 6450518 */
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,
PJI_PROJ_EXTR_STATUS fm_status /* Added for bug 8661279 */
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) Commented for bug 9340121 */
and prj.PROJECT_ID = fm_status.project_id /* Added for bug 8661279 */
and prj.project_type <> 'AWARD_PROJECT'; /* Added for Bug 6450518 */
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,
PJI_PROJ_EXTR_STATUS fm_status /* Added for bug 6748705 */
where
prj.TEMPLATE_FLAG = 'N' and
prj.ORG_ID = pt.ORG_ID and /*5377131*/
prj.PROJECT_ID = fm_status.project_id and /* Added for bug 6748705 */
prj.PROJECT_TYPE = pt.PROJECT_TYPE
and prj.project_type <> 'AWARD_PROJECT'; /* Added for Bug 6450518 */
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 ;