[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
procedure UPDATE_EXTR_SCOPE is
l_extr_start_date date;
select prj.project_id, sts.project_system_status_code
from pa_projects_all prj
, pa_project_statuses sts
where prj.project_status_code = sts.project_status_code
and sts.project_system_status_code in ('PARTIALLY_PURGED'
,'PURGED'
,'PENDING_PURGE')
;
select count(*)
into l_row_count
from PJI_PROJ_EXTR_STATUS
where ROWNUM = 1;
delete
from PA_PJI_PROJ_EVENTS_LOG
where EVENT_TYPE = 'Projects';
delete
from PJI_PROJ_EXTR_STATUS pji
where not exists (select 1
from PA_PROJECTS_ALL pa
where pa.PROJECT_ID = pji.PROJECT_ID);
insert into PJI_PROJ_EXTR_STATUS
(
PROJECT_ID,
PROJECT_ORGANIZATION_ID,
PROJECT_NAME,
LAST_UPDATE_DATE,
CREATION_DATE,
PURGE_STATUS,
PROJECT_TYPE_CLASS
)
select
prj.PROJECT_ID,
prj.CARRYING_OUT_ORGANIZATION_ID,
'PJI$NULL',
sysdate,
sysdate,
sts.PROJECT_SYSTEM_STATUS_CODE,
DECODE(pt.PROJECT_TYPE_CLASS_CODE,
'CAPITAL', 'C',
'CONTRACT', 'B',
'INDIRECT', 'I')
from
PA_PROJECTS_ALL prj,
PA_PROJECT_STATUSES sts,
PA_PROJECT_TYPES_ALL pt,
(
select
PROJECT_STATUS_CODE
from
(
select /*+ index_ffs(prj, PA_PROJECTS_N4)
parallel_index(prj, PA_PROJECTS_N4) */
distinct
prj.PROJECT_STATUS_CODE
from
PA_PROJECTS_ALL prj
)
where
PA_PROJECT_UTILS.CHECK_PRJ_STUS_ACTION_ALLOWED
(PROJECT_STATUS_CODE, 'STATUS_REPORTING') = 'Y'
) psc
where
nvl(closed_date,l_extr_start_date) >= l_extr_start_date and
prj.project_status_code = psc.project_status_code and
not exists
(
select 1
from PJI_PROJ_EXTR_STATUS ps
where ps.PROJECT_ID = prj.PROJECT_ID
) and
prj.project_status_code = sts.project_status_code and
nvl(prj.ORG_ID, -1) = nvl(pt.ORG_ID, -1) and
prj.PROJECT_TYPE = pt.PROJECT_TYPE;
update PJI_PROJ_EXTR_STATUS extr
set extr.purge_status = rec_purge_projs.project_system_status_code
where extr.project_id = rec_purge_projs.project_id
and NVL(extr.purge_status, 'X') not in ('PARTIALLY_PURGED'
,'PURGED'
,'PENDING_PURGE')
;
end UPDATE_EXTR_SCOPE;
UPDATE_ORG_EXTR_INFO; -- PJI_ORG_EXTR_INFO always maintained incrementally
procedure UPDATE_ORG_EXTR_INFO is
pragma AUTONOMOUS_TRANSACTION;
select
to_number(to_char(min(START_DATE), 'J')),
to_number(to_char(max(END_DATE), 'J'))
into
l_ent_cal_min_date,
l_ent_cal_max_date
from
PJI_TIME_ENT_PERIOD_V;
insert into PJI_ORG_EXTR_INFO
(
ORG_ID,
PF_CURRENCY_CODE,
EN_CALENDAR_MIN_DATE,
EN_CALENDAR_MAX_DATE,
GL_CALENDAR_ID,
GL_CALENDAR_MIN_DATE,
GL_CALENDAR_MAX_DATE,
PA_CALENDAR_ID,
PA_CALENDAR_MIN_DATE,
PA_CALENDAR_MAX_DATE
)
select
-1, -- -1 can be a valid operating unit when a row
'PJI$NULL', -- is only a receiver row or only a provider
to_number(null), -- row. When a row applies to both receiver
to_number(null), -- and provider, ord_id will never be -1.
to_number(null),
to_number(null), -- Added to_number for bug 3621077
to_number(null),
to_number(null),
to_number(null),
to_number(null)
from
dual
where
not exists (select ORG_ID
from PA_IMPLEMENTATIONS_ALL
where ORG_ID is null) and
-1 not in (select ORG_ID
from PJI_ORG_EXTR_INFO)
union all
select
nvl(imp.ORG_ID,-1) ORG_ID,
to_char(null),
to_number(null),
to_number(null),
to_number(null),
to_number(null),
to_number(null),
to_number(null),
to_number(null),
to_number(null)
from
PA_IMPLEMENTATIONS_ALL imp
where
imp.ORG_ID not in (select ORG_ID
from PJI_ORG_EXTR_INFO);
update PJI_ORG_EXTR_INFO info
set (PF_CURRENCY_CODE,
EN_CALENDAR_MIN_DATE,
EN_CALENDAR_MAX_DATE,
GL_CALENDAR_ID,
GL_CALENDAR_MIN_DATE,
GL_CALENDAR_MAX_DATE,
PA_CALENDAR_ID,
PA_CALENDAR_MIN_DATE,
PA_CALENDAR_MAX_DATE) =
(select
gl.CURRENCY_CODE,
l_ent_cal_min_date,
l_ent_cal_max_date,
gl.CALENDAR_ID,
to_number(to_char(gl.START_DATE, 'J')),
to_number(to_char(gl.END_DATE, 'J')),
pa.CALENDAR_ID,
to_number(to_char(pa.START_DATE, 'J')),
to_number(to_char(pa.END_DATE, 'J'))
from
(
select
nvl(imp.ORG_ID,-1) ORG_ID,
sob.CURRENCY_CODE,
min(glp.START_DATE) START_DATE,
max(glp.END_DATE) END_DATE,
fii.CALENDAR_ID
from
PA_IMPLEMENTATIONS_ALL imp,
GL_SETS_OF_BOOKS sob,
GL_PERIODS glp,
FII_TIME_CAL_NAME fii
where
imp.SET_OF_BOOKS_ID = sob.SET_OF_BOOKS_ID and
sob.PERIOD_SET_NAME = glp.PERIOD_SET_NAME and
sob.ACCOUNTED_PERIOD_TYPE = glp.PERIOD_TYPE and
fii.PERIOD_SET_NAME = glp.PERIOD_SET_NAME and
fii.PERIOD_TYPE = glp.PERIOD_TYPE
group by
nvl(imp.ORG_ID,-1),
sob.CURRENCY_CODE,
fii.CALENDAR_ID
) gl,
(
select
nvl(imp.ORG_ID,-1) ORG_ID,
min(glp.START_DATE) START_DATE,
max(glp.END_DATE) END_DATE,
fii.CALENDAR_ID
from
PA_IMPLEMENTATIONS_ALL imp,
GL_PERIODS glp,
FII_TIME_CAL_NAME fii
where
imp.PA_PERIOD_TYPE = glp.PERIOD_TYPE and
imp.PERIOD_SET_NAME = glp.PERIOD_SET_NAME and
fii.PERIOD_SET_NAME = glp.PERIOD_SET_NAME and
fii.PERIOD_TYPE = glp.PERIOD_TYPE
group by
nvl(imp.ORG_ID,-1),
fii.CALENDAR_ID
) pa
where
gl.ORG_ID = pa.ORG_ID and
gl.ORG_ID = info.ORG_ID)
where
(nvl(ORG_ID, -1),
nvl(PF_CURRENCY_CODE, 'PJI$NULL1'),
nvl(EN_CALENDAR_MIN_DATE, 1),
nvl(EN_CALENDAR_MAX_DATE, 1),
nvl(GL_CALENDAR_ID, -1),
nvl(GL_CALENDAR_MIN_DATE, 1),
nvl(GL_CALENDAR_MAX_DATE, 1),
nvl(PA_CALENDAR_ID, -1),
nvl(PA_CALENDAR_MIN_DATE, 1),
nvl(PA_CALENDAR_MAX_DATE, 1)) not in
(select
nvl(gl.ORG_ID, -1),
nvl(gl.CURRENCY_CODE, 'PJI$NULL2'),
nvl(l_ent_cal_min_date, 2),
nvl(l_ent_cal_max_date, 2),
nvl(gl.CALENDAR_ID, -2),
nvl(to_number(to_char(gl.START_DATE, 'J')), 2),
nvl(to_number(to_char(gl.END_DATE, 'J')), 2),
nvl(pa.CALENDAR_ID, -2),
nvl(to_number(to_char(pa.START_DATE, 'J')), 2),
nvl(to_number(to_char(pa.END_DATE, 'J')), 2)
from
(
select
nvl(imp.ORG_ID,-1) ORG_ID,
sob.CURRENCY_CODE,
min(glp.START_DATE) START_DATE,
max(glp.END_DATE) END_DATE,
fii.CALENDAR_ID
from
PA_IMPLEMENTATIONS_ALL imp,
GL_SETS_OF_BOOKS sob,
GL_PERIODS glp,
FII_TIME_CAL_NAME fii
where
imp.SET_OF_BOOKS_ID = sob.SET_OF_BOOKS_ID and
sob.PERIOD_SET_NAME = glp.PERIOD_SET_NAME and
sob.ACCOUNTED_PERIOD_TYPE = glp.PERIOD_TYPE and
fii.PERIOD_SET_NAME = glp.PERIOD_SET_NAME and
fii.PERIOD_TYPE = glp.PERIOD_TYPE
group by
nvl(imp.ORG_ID,-1),
sob.CURRENCY_CODE,
fii.CALENDAR_ID
) gl,
(
select
nvl(imp.ORG_ID,-1) ORG_ID,
min(glp.START_DATE) START_DATE,
max(glp.END_DATE) END_DATE,
fii.CALENDAR_ID
from
PA_IMPLEMENTATIONS_ALL imp,
GL_PERIODS glp,
FII_TIME_CAL_NAME fii
where
imp.PA_PERIOD_TYPE = glp.PERIOD_TYPE and
imp.PERIOD_SET_NAME = glp.PERIOD_SET_NAME and
fii.PERIOD_SET_NAME = glp.PERIOD_SET_NAME and
fii.PERIOD_TYPE = glp.PERIOD_TYPE
group by
nvl(imp.ORG_ID,-1),
fii.CALENDAR_ID
) pa
where
gl.ORG_ID = pa.ORG_ID and
gl.ORG_ID = info.ORG_ID);
update PJI_ORG_EXTR_INFO
set PF_CURRENCY_CODE = 'PJI$NULL'
where ORG_ID = -1 and
nvl(PF_CURRENCY_CODE, 'x') <> 'PJI$NULL';
update PJI_ORG_EXTR_INFO
set EN_CALENDAR_MIN_DATE = l_ent_cal_min_date,
EN_CALENDAR_MAX_DATE = l_ent_cal_max_date
where ORG_ID <> -1 and
(nvl(EN_CALENDAR_MIN_DATE, 1) <> l_ent_cal_min_date or
nvl(EN_CALENDAR_MAX_DATE, 1) <> l_ent_cal_max_date);
end UPDATE_ORG_EXTR_INFO;
select to_number(value)
into l_db_block_size
from v$parameter
where name = 'db_block_size'
;
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_SYSTEM_PARAMETERS where NAME not in ('PJI_PJP_ENT_CURR_REP_PERIOD');
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,
'STAGE1' PROCESS_NAME,
'CLEANALL' RUN_TYPE,
substr(p_check || ', ' ||
p_truncate_pji_tables || ', ' ||
p_truncate_pjp_tables || ', ' ||
p_run_fpm_upgrade, 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 = 'Y'
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');
update FND_PROFILE_OPTION_VALUES
set PROFILE_OPTION_VALUE = 'Y'
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');
update PJI_SYSTEM_CONFIG_HIST
set END_DATE = sysdate,
COMPLETION_TEXT = 'Normal completion'
where PROCESS_NAME = 'STAGE1' and
END_DATE is null;
update PJI_SYSTEM_CONFIG_HIST
set END_DATE = sysdate,
COMPLETION_TEXT = l_sqlerrm
where PROCESS_NAME = 'STAGE1' and
END_DATE is null;