[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
procedure UPDATE_PJI_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_PJI_PROJ_EXTR_STATUS
where ROWNUM = 1;
insert into PJI_PJI_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_PJI_PROJ_EXTR_STATUS pji_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 = pji_status.PROJECT_ID (+) and
pji_status.PROJECT_ID is null;
insert into PJI_PJI_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_PJI_EXTR_SCOPE;
procedure UPDATE_ORG_EXTR_INFO is
begin
PJI_EXTRACTION_UTIL.UPDATE_ORG_EXTR_INFO;
end UPDATE_ORG_EXTR_INFO;
SELECT
level_1.owner owner
, level_1.name mv_name
, level_1.mview_id mv_id
, max(decode(level_1.ord_bod1
, 1, decode(bod2.DEPEND_OBJECT_TYPE
, 'MV', 2
, 1)
, 0)) ord_bod2
FROM
(
select
rmv.OWNER
, rmv.NAME
, rmv.MVIEW_ID
, bod1.DEPEND_OBJECT_NAME prnt1
, decode(bod1.DEPEND_OBJECT_TYPE
, 'MV' , 1
, 0) ord_bod1
from DBA_REGISTERED_MVIEWS rmv
, BIS_OBJ_DEPENDENCY bod1
where 1=1
and rmv.NAME like 'PJI%'
and bod1.OBJECT_TYPE (+) = 'MV'
and rmv.NAME = bod1.OBJECT_NAME (+)
) level_1
, BIS_OBJ_DEPENDENCY bod2
WHERE 1=1
AND decode(level_1.ord_bod1
, 1, level_1.prnt1
, level_1.name ) = bod2.OBJECT_NAME (+)
--and level_1.name = 'PJI_FP_ORGO_F_MV'
group by level_1.owner
, level_1.name
, level_1.mview_id
order by 4,3
;
* Update tables on which only PJI mviews rely. This way if massive
* changes take place in these tables we can run a full refresh on the
* materialized views rather than an incremental refresh.
*
*/
if (p_refresh_mview_lookups = 'Y') then
PJI_PJ_PROJ_CLASS_EXTR.EXTR_CLASS_CODES;
PJI_PJI_EXTRACTION_UTILS.UPDATE_PJI_ORG_HRCHY;
select to_number(value)
into l_db_block_size
from v$parameter
where name = 'db_block_size'
;
procedure UPDATE_PJI_RM_WORK_TYPE_INFO (p_process in varchar2) is
l_row_count number;
if (not PJI_PROCESS_UTIL.NEED_TO_RUN_STEP(p_process, 'PJI_PJI_EXTRACTION_UTILS.UPDATE_PJI_RM_WORK_TYPE_INFO(p_process);')) then
select count(*)
into l_row_count
from PJI_RM_WORK_TYPE_INFO
where ROWNUM = 1;
delete
from PJI_RM_WORK_TYPE_INFO
where RECORD_TYPE in ( 'CHANGE_NEW', 'CHANGE_OLD');
insert into PJI_RM_WORK_TYPE_ROWID
(
PA_ROWID,
PJI_ROWID,
CHANGE_FLAG
)
select
pa.ROWID,
pji.ROWID,
case when nvl(pa.BILLABLE_CAPITALIZABLE_FLAG,'Y') <> nvl(pji.BILLABLE_CAPITALIZABLE_FLAG,'Y') or
nvl(pa.REDUCE_CAPACITY_FLAG,'Y') <> nvl(pji.REDUCE_CAPACITY_FLAG,'Y') or
nvl(pa.RES_UTILIZATION_PERCENTAGE,0) <> nvl(pji.RES_UTILIZATION_PERCENTAGE,0) or
nvl(pa.ORG_UTILIZATION_PERCENTAGE,0) <> nvl(pji.ORG_UTILIZATION_PERCENTAGE,0) or
nvl(pa.TRAINING_FLAG,'Y') <> nvl(pji.TRAINING_FLAG,'Y')
then 'Y'
else 'N'
end
from
PA_WORK_TYPES_B pa,
PJI_RM_WORK_TYPE_INFO pji
where
pa.WORK_TYPE_ID = pji.WORK_TYPE_ID (+) and
pji.RECORD_TYPE (+)= 'NORMAL';
delete
from PJI_RM_WORK_TYPE_INFO wt
where
wt.ROWID not in
(
select
wt_r.PJI_ROWID
from
PJI_RM_WORK_TYPE_ROWID wt_r
where
wt_r.PJI_ROWID is not null
)
and wt.RECORD_TYPE = 'NORMAL';
pji_utils.write2log(sql%rowcount || ' rows deleted.');
update PJI_RM_WORK_TYPE_INFO wt
set
(
WORK_TYPE_ID,
BILLABLE_CAPITALIZABLE_FLAG,
REDUCE_CAPACITY_FLAG,
RES_UTILIZATION_PERCENTAGE,
ORG_UTILIZATION_PERCENTAGE,
TRAINING_FLAG,
LAST_UPDATE_DATE,
LAST_UPDATED_BY
) =
(
select
pa.WORK_TYPE_ID,
pa.BILLABLE_CAPITALIZABLE_FLAG,
pa.REDUCE_CAPACITY_FLAG,
pa.RES_UTILIZATION_PERCENTAGE,
pa.ORG_UTILIZATION_PERCENTAGE,
pa.TRAINING_FLAG,
pa.LAST_UPDATE_DATE,
pa.LAST_UPDATED_BY
from
PJI_RM_WORK_TYPE_ROWID wt_r,
PA_WORK_TYPES_B pa
where
wt_r.PJI_ROWID = wt.ROWID and
pa.ROWID = wt_r.PA_ROWID
)
where
wt.ROWID in
(
select
wt_r.PJI_ROWID
from
PJI_RM_WORK_TYPE_ROWID wt_r
where
wt_r.PJI_ROWID is not null and
wt_r.CHANGE_FLAG = 'Y'
);
pji_utils.write2log(sql%rowcount || ' rows updated.');
insert into PJI_RM_WORK_TYPE_INFO
(
WORK_TYPE_ID,
BILLABLE_CAPITALIZABLE_FLAG,
REDUCE_CAPACITY_FLAG,
RES_UTILIZATION_PERCENTAGE,
ORG_UTILIZATION_PERCENTAGE,
TRAINING_FLAG,
RECORD_TYPE,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY
)
select /*+ rowid(pa) */
pa.WORK_TYPE_ID,
pa.BILLABLE_CAPITALIZABLE_FLAG,
pa.REDUCE_CAPACITY_FLAG,
pa.RES_UTILIZATION_PERCENTAGE,
pa.ORG_UTILIZATION_PERCENTAGE,
pa.TRAINING_FLAG,
'NORMAL',
pa.CREATION_DATE,
pa.CREATED_BY,
pa.LAST_UPDATE_DATE,
pa.LAST_UPDATED_BY
from
PA_WORK_TYPES_B pa
where
pa.ROWID in
(
select
wt_r.PA_ROWID
from
PJI_RM_WORK_TYPE_ROWID wt_r
where
wt_r.PJI_ROWID is null
);
pji_utils.write2log(sql%rowcount || ' rows inserted.');
select max(event_id)
into l_event_id
from
pa_pji_proj_events_log log
where
log.EVENT_TYPE = 'Work Types' and
log.OPERATION_TYPE = 'U';
delete
from
pa_pji_proj_events_log log
where
log.EVENT_TYPE = 'Work Types' and
log.OPERATION_TYPE = 'U' and
log.EVENT_ID <= l_event_id and
log.EVENT_ID > ( select min(log1.event_id)
from pa_pji_proj_events_log log1
where log1.event_object = log.EVENT_OBJECT
and log1.operation_type = 'U'
group by log1.event_object );
pji_utils.write2log(sql%rowcount || ' rows deleted :2.');
insert into PJI_RM_WORK_TYPE_INFO
(
WORK_TYPE_ID,
BILLABLE_CAPITALIZABLE_FLAG,
REDUCE_CAPACITY_FLAG,
RES_UTILIZATION_PERCENTAGE,
ORG_UTILIZATION_PERCENTAGE,
TRAINING_FLAG,
RECORD_TYPE,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY
)
select
to_number(EVENT_OBJECT),
ATTRIBUTE3,
ATTRIBUTE4,
to_number(ATTRIBUTE1),
to_number(ATTRIBUTE2),
ATTRIBUTE5,
'CHANGE_OLD',
sysdate,
-1,
sysdate,
-1
from pa_pji_proj_events_log
where
EVENT_ID <= l_event_id and
EVENT_TYPE = 'Work Types' and
OPERATION_TYPE = 'U';
pji_utils.write2log(sql%rowcount || ' rows inserted :2.');
delete
from
pa_pji_proj_events_log log
where
log.EVENT_ID <= l_event_id and
log.EVENT_TYPE = 'Work Types' and
log.OPERATION_TYPE = 'U';
pji_utils.write2log(sql%rowcount || ' rows deleted 3.');
insert into PJI_RM_WORK_TYPE_INFO
(
WORK_TYPE_ID,
BILLABLE_CAPITALIZABLE_FLAG,
REDUCE_CAPACITY_FLAG,
RES_UTILIZATION_PERCENTAGE,
ORG_UTILIZATION_PERCENTAGE,
TRAINING_FLAG,
RECORD_TYPE,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY
)
select
WORK_TYPE_ID,
BILLABLE_CAPITALIZABLE_FLAG,
REDUCE_CAPACITY_FLAG,
RES_UTILIZATION_PERCENTAGE,
ORG_UTILIZATION_PERCENTAGE,
TRAINING_FLAG,
'CHANGE_NEW',
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY
from PJI_RM_WORK_TYPE_INFO info
where info.RECORD_TYPE = 'NORMAL'
and info.WORK_TYPE_ID in ( select WORK_TYPE_ID
from PJI_RM_WORK_TYPE_INFO wt
where wt.RECORD_TYPE = 'CHANGE_OLD');
pji_utils.write2log(sql%rowcount || ' rows inserted :3.');
PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION(p_process, 'PJI_PJI_EXTRACTION_UTILS.UPDATE_PJI_RM_WORK_TYPE_INFO(p_process);');
end UPDATE_PJI_RM_WORK_TYPE_INFO;
procedure UPDATE_PJI_ORG_HRCHY is
l_org_structure_version_id number;
select ORG_STRUCTURE_VERSION_ID
into l_org_structure_version_id
from PJI_SYSTEM_SETTINGS;
insert into PJI_ROWID_ORG_DENORM
(
HRI_ROWID,
PJI_ROWID,
CHANGE_FLAG
)
select /*+ ordered full(pji) use_hash(pji)
index(hri, HRI_ORG_HRCHY_SUMMARY_U1) */
hri.ROWID,
pji.ROWID,
case when hri.ORGANIZATION_LEVEL <> pji.ORGANIZATION_LEVEL or
hri.SUB_ORGANIZATION_LEVEL <> pji.SUB_ORGANIZATION_LEVEL
then 'Y'
else 'N'
end
from
HRI_ORG_HRCHY_SUMMARY hri,
PJI_ORG_DENORM pji
where
hri.ORG_STRUCTURE_VERSION_ID = l_org_structure_version_id and
hri.ORGANIZATION_ID = pji.ORGANIZATION_ID (+) and
hri.SUB_ORGANIZATION_ID = pji.SUB_ORGANIZATION_ID (+);
delete /*+ use_nl(denorm) rowid(denorm) */
from PJI_ORG_DENORM denorm
where
denorm.ROWID not in
(
select /*+ index(org_r, PJI_ROWID_ORG_DENORM_N1) */
org_r.PJI_ROWID
from
PJI_ROWID_ORG_DENORM org_r
where
org_r.PJI_ROWID is not null
);
pji_utils.write2log(sql%rowcount || ' rows deleted.');
update /*+ use_nl(denorm) rowid(denorm) */ PJI_ORG_DENORM denorm
set
(
ORGANIZATION_LEVEL,
SUB_ORGANIZATION_LEVEL
) =
(
select /*+ ordered index(org_r, PJI_ROWID_ORG_DENORM_N1) rowid(hri) */
hri.ORGANIZATION_LEVEL,
hri.SUB_ORGANIZATION_LEVEL
from
PJI_ROWID_ORG_DENORM org_r,
HRI_ORG_HRCHY_SUMMARY hri
where
org_r.PJI_ROWID = denorm.ROWID and
hri.ROWID = org_r.HRI_ROWID
)
where
denorm.ROWID in
(
select /*+ index(org_r, PJI_ROWID_ORG_DENORM_N1) */
org_r.PJI_ROWID
from
PJI_ROWID_ORG_DENORM org_r
where
org_r.PJI_ROWID is not null and
org_r.CHANGE_FLAG = 'Y'
);
pji_utils.write2log(sql%rowcount || ' rows updated.');
insert into PJI_ORG_DENORM
(
ORGANIZATION_ID,
ORGANIZATION_LEVEL,
SUB_ORGANIZATION_ID,
SUB_ORGANIZATION_LEVEL
)
select /*+ rowid(hri) */
hri.ORGANIZATION_ID,
hri.ORGANIZATION_LEVEL,
hri.SUB_ORGANIZATION_ID,
hri.SUB_ORGANIZATION_LEVEL
from
HRI_ORG_HRCHY_SUMMARY hri
where
hri.ROWID in
(
select /*+ index(org_r, PJI_ROWID_ORG_DENORM_N1) */
org_r.HRI_ROWID
from
PJI_ROWID_ORG_DENORM org_r
where
org_r.PJI_ROWID is null
);
pji_utils.write2log(sql%rowcount || ' rows inserted.');
end UPDATE_PJI_ORG_HRCHY;
procedure UPDATE_RESOURCE_DATA (p_process in varchar2) is
l_row_count number;
if (not PJI_PROCESS_UTIL.NEED_TO_RUN_STEP(p_process, 'PJI_PJI_EXTRACTION_UTILS.UPDATE_RESOURCE_DATA(p_process);')) then
select count(*)
into l_row_count
from PJI_RESOURCES_DENORM
where ROWNUM = 1;
insert into PJI_ROWID_RESOURCES_DENORM
(
PA_ROWID,
PJI_ROWID,
CHANGE_FLAG
)
select /*+ full(pa) parallel(pa) use_hash(pa)
full(pji) parallel(pji) use_hash(pji) */
pa.ROWID,
pji.ROWID,
case when nvl(pa.JOB_ID, -999) <>
nvl(pji.JOB_ID, -999) or
nvl(pa.UTILIZATION_FLAG, 'PJI$NULL') <>
nvl(pji.UTILIZATION_FLAG, 'PJI$NULL')
then 'Y'
else 'N'
end
from
PA_RESOURCES_DENORM pa,
PJI_RESOURCES_DENORM pji
where
pa.PERSON_ID = pji.PERSON_ID (+) and
pa.RESOURCE_ID = pji.RESOURCE_ID (+) and
pa.RESOURCE_NAME = pji.RESOURCE_NAME (+) and
pa.RESOURCE_ORGANIZATION_ID = pji.ORGANIZATION_ID (+) and
pa.RESOURCE_EFFECTIVE_START_DATE = pji.START_DATE (+) and
nvl(pa.RESOURCE_EFFECTIVE_END_DATE, l_max_date) = pji.END_DATE (+);
insert into PJI_RES_DELTA
(
PERSON_ID,
RESOURCE_ID,
START_DATE,
END_DATE,
CHANGE_TYPE
)
select /*+ use_nl(denorm) rowid(denorm) */ -- old resources
denorm.PERSON_ID,
denorm.RESOURCE_ID,
denorm.START_DATE,
denorm.END_DATE,
'N'
from
PJI_RESOURCES_DENORM denorm
where
denorm.UTILIZATION_FLAG = 'Y' and
denorm.ROWID not in
(
select /*+ index(res_r, PJI_ROWID_RESOURCES_DENORM_N1) */
res_r.PJI_ROWID
from
PJI_ROWID_RESOURCES_DENORM res_r
where
res_r.PJI_ROWID is not null
)
union all -- updated resources
select /*+ ordered
index(res_r, PJI_ROWID_RESOURCES_DENORM_N1)
rowid(pa)
rowid(pji) */
pa.PERSON_ID,
pa.RESOURCE_ID,
pa.RESOURCE_EFFECTIVE_START_DATE,
nvl(pa.RESOURCE_EFFECTIVE_END_DATE, l_max_date),
case when (nvl(pa.UTILIZATION_FLAG, 'N') = 'N' and
nvl(pji.UTILIZATION_FLAG, 'N') = 'Y')
then 'N'
when (nvl(pa.UTILIZATION_FLAG, 'N') = 'Y' and
nvl(pji.UTILIZATION_FLAG, 'N') = 'N')
then 'Y'
end
from
PJI_ROWID_RESOURCES_DENORM res_r,
PA_RESOURCES_DENORM pa,
PJI_RESOURCES_DENORM pji
where
res_r.PJI_ROWID is not null and
res_r.CHANGE_FLAG = 'Y' and
res_r.PA_ROWID = pa.ROWID and
res_r.PJI_ROWID = pji.ROWID and
nvl(pji.UTILIZATION_FLAG, 'N') <> nvl(pa.UTILIZATION_FLAG, 'N')
union all -- new resources
select /*+ rowid(pa) */
pa.PERSON_ID,
pa.RESOURCE_ID,
pa.RESOURCE_EFFECTIVE_START_DATE,
nvl(pa.RESOURCE_EFFECTIVE_END_DATE, l_max_date),
'Y'
from
PA_RESOURCES_DENORM pa
where
pa.UTILIZATION_FLAG = 'Y' and
pa.ROWID in
(
select /*+ index(res_r, PJI_ROWID_RESOURCES_DENORM_N1) */
res_r.PA_ROWID
from
PJI_ROWID_RESOURCES_DENORM res_r
where
res_r.PJI_ROWID is null
);
delete /*+ use_nl(denorm) rowid(denorm) */
from PJI_RESOURCES_DENORM denorm
where
denorm.ROWID not in
(
select /*+ index(res_r, PJI_ROWID_RESOURCES_DENORM_N1) */
res_r.PJI_ROWID
from
PJI_ROWID_RESOURCES_DENORM res_r
where
res_r.PJI_ROWID is not null
);
pji_utils.write2log(sql%rowcount || ' rows deleted.');
update /*+ use_nl(denorm) rowid(denorm) */ PJI_RESOURCES_DENORM denorm
set
(
JOB_ID,
UTILIZATION_FLAG
) =
(
select /*+ordered index(res_r, PJI_ROWID_RESOURCES_DENORM_N1) rowid(pa)*/
pa.JOB_ID,
pa.UTILIZATION_FLAG
from
PJI_ROWID_RESOURCES_DENORM res_r,
PA_RESOURCES_DENORM pa
where
res_r.PJI_ROWID = denorm.ROWID and
pa.ROWID = res_r.PA_ROWID
)
where
denorm.ROWID in
(
select /*+ index(res_r, PJI_ROWID_RESOURCES_DENORM_N1) */
res_r.PJI_ROWID
from
PJI_ROWID_RESOURCES_DENORM res_r
where
res_r.PJI_ROWID is not null and
res_r.CHANGE_FLAG = 'Y'
);
pji_utils.write2log(sql%rowcount || ' rows updated.');
insert into PJI_RESOURCES_DENORM
(
PERSON_ID,
RESOURCE_ID,
RESOURCE_NAME,
START_DATE,
END_DATE,
JOB_ID,
ORGANIZATION_ID,
UTILIZATION_FLAG
)
select /*+ rowid(pa) */
pa.PERSON_ID,
pa.RESOURCE_ID,
pa.RESOURCE_NAME,
pa.RESOURCE_EFFECTIVE_START_DATE,
nvl(pa.RESOURCE_EFFECTIVE_END_DATE, l_max_date),
pa.JOB_ID,
pa.RESOURCE_ORGANIZATION_ID,
pa.UTILIZATION_FLAG
from
PA_RESOURCES_DENORM pa
where
pa.ROWID in
(
select /*+ index(res_r, PJI_ROWID_RESOURCES_DENORM_N1) */
res_r.PA_ROWID
from
PJI_ROWID_RESOURCES_DENORM res_r
where
res_r.PJI_ROWID is null
);
pji_utils.write2log(sql%rowcount || ' rows inserted.');
PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION(p_process, 'PJI_PJI_EXTRACTION_UTILS.UPDATE_RESOURCE_DATA(p_process);');
end UPDATE_RESOURCE_DATA;
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');
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,
'STAGE2' PROCESS_NAME,
'CLEANALL' RUN_TYPE,
substr(p_check, 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;
delete from PJI_MT_PRC_STEPS where PROCESS_NAME like (PJI_RM_SUM_MAIN.g_process || '%');
delete from PJI_SYSTEM_PARAMETERS where NAME like (PJI_RM_SUM_MAIN.g_process || '%$%') or
NAME like 'DANGLING_PJI_ROWS_EXIST' or
NAME like 'LAST_PJI_EXTR_DATE';
delete from PJI_SYSTEM_PRC_STATUS where PROCESS_NAME like (PJI_RM_SUM_MAIN.g_process || '%');
update PJI_SYSTEM_CONFIG_HIST
set END_DATE = sysdate,
COMPLETION_TEXT = 'Normal completion'
where PROCESS_NAME = 'STAGE2' and
END_DATE is null;
update PJI_SYSTEM_CONFIG_HIST
set END_DATE = sysdate,
COMPLETION_TEXT = l_sqlerrm
where PROCESS_NAME = 'STAGE2' and
END_DATE is null;