[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select
CONFIG_PROJ_PERF_FLAG,
CONFIG_COST_FLAG,
CONFIG_PROFIT_FLAG,
CONFIG_UTIL_FLAG
into
l_settings_proj_perf_flag,
l_settings_cost_flag,
l_settings_profit_flag,
l_settings_util_flag
from
PJI_SYSTEM_SETTINGS;
update PJI_SYSTEM_SETTINGS
set CONFIG_COST_FLAG = 'Y';
update PJI_SYSTEM_SETTINGS
set CONFIG_PROJ_PERF_FLAG = 'Y';
select count(*)
into l_row_count
from PJI_FM_DNGL_FIN;
select count(*) + l_row_count
into l_row_count
from PJI_FM_DNGL_ACT;
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,
g_process || 1 PROCESS_NAME,
'TRANSITION' RUN_TYPE,
null PARAMETERS,
l_settings_proj_perf_flag CONFIG_PROJ_PERF_FLAG,
l_settings_cost_flag CONFIG_COST_FLAG,
l_settings_profit_flag CONFIG_PROFIT_FLAG,
l_settings_util_flag CONFIG_UTIL_FLAG,
sysdate START_DATE,
null END_DATE,
null COMPLETION_TEXT
from
dual;
select min(segment1) ,max(segment1)
into l_from_project_num, l_to_project_num
from pa_projects_all
where segment1 between nvl(p_from_project,segment1) and nvl(p_to_project,segment1);
/* Get the Project Ids ,this is required to keep the impact minimum , these values will be updated in pji_system_parameters Table */
IF l_from_project_num is not null THEN
select project_id
into p_from_project_id
from pa_projects_all
where segment1= l_from_project_num;
select project_id
into p_to_project_id
from pa_projects_all
where segment1= l_to_project_num;
select count(*)
into l_project_count
from PJI_PROJ_EXTR_STATUS
where ROWNUM = 1;
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,
g_process || 1 PROCESS_NAME,
l_extraction_type RUN_TYPE,
substr(p_run_mode || ', ' ||
p_extract_commitments || ', ' ||
to_char(p_organization_id) || ', ' ||
to_char(p_include_sub_org) || ', ' ||
to_char(p_operating_unit) || ', ' ||
p_from_project || ', ' ||
p_to_project || ', ' ||
p_plan_type, 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;
PJI_EXTRACTION_UTIL.UPDATE_EXTR_SCOPE;
insert into PJI_FM_PROJ_BATCH_MAP
(
WORKER_ID,
PROJECT_ID,
PROJECT_TYPE_CLASS,
PJI_PROJECT_STATUS,
ROW_COUNT,
START_DATE,
END_DATE,
PROJECT_ORG_ID,
NEW_PROJECT_ORGANIZATION_ID,
NEW_CLOSED_DATE,
EXTRACTION_TYPE,
EXTRACTION_STATUS,
COST_BUDGET_C_VERSION,
COST_BUDGET_CO_VERSION,
REVENUE_BUDGET_C_VERSION,
REVENUE_BUDGET_CO_VERSION,
COST_FORECAST_C_VERSION,
REVENUE_FORECAST_C_VERSION,
PROJECT_ORGANIZATION_ID,
OLD_CLOSED_DATE,
PLAN_EXTRACTION_STATUS,
BACKLOG_EXTRACTION_STATUS
)
select /*+ ordered full(extr) use_hash(extr)
full(prj) use_hash(prj) parallel(prj) */
1 WORKER_ID,
extr.PROJECT_ID,
extr.PROJECT_TYPE_CLASS,
'O',
0,
null,
null,
prj.ORG_ID,
prj.CARRYING_OUT_ORGANIZATION_ID,
prj.CLOSED_DATE,
decode(nvl(extr.EXTRACTION_STATUS, 'Z'), 'Z', 'F',
decode(l_extraction_type, 'FULL', 'F',
'INCREMENTAL', 'I',
'PARTIAL', 'P')),
extr.EXTRACTION_STATUS,
decode(l_extraction_type, 'PARTIAL', -1,
nvl(extr.COST_BUDGET_C_VERSION,-2)),
decode(l_extraction_type, 'PARTIAL', -1,
nvl(extr.COST_BUDGET_CO_VERSION,-2)),
decode(l_extraction_type, 'PARTIAL', -1,
nvl(extr.REVENUE_BUDGET_C_VERSION,-2)),
decode(l_extraction_type, 'PARTIAL', -1,
nvl(extr.REVENUE_BUDGET_CO_VERSION,-2)),
decode(l_extraction_type, 'PARTIAL', -1,
nvl(extr.COST_FORECAST_C_VERSION,-2)),
decode(l_extraction_type, 'PARTIAL', -1,
nvl(extr.REVENUE_FORECAST_C_VERSION,-2)),
prj.CARRYING_OUT_ORGANIZATION_ID,
prj.CLOSED_DATE,
'N',
'N'
from
PJI_PROJ_EXTR_STATUS extr,
PA_PROJECTS_ALL prj
where
nvl(extr.PURGE_STATUS,'X') not in ('PARTIALLY_PURGED',
'PURGED',
'PENDING_PURGE') and
extr.project_id = prj.project_id and
nvl(prj.org_id,-99) = nvl(p_operating_unit,nvl(prj.org_id,-99)) and
(l_extraction_type = 'FULL' or
(prj.segment1 between nvl(p_from_project,prj.segment1) and
nvl(p_to_project,prj.segment1)
)) and
not (l_extraction_type = 'PARTIAL' and
extr.EXTRACTION_STATUS is null);
insert into PJI_FM_PROJ_BATCH_MAP
(
WORKER_ID,
PROJECT_ID,
PROJECT_TYPE_CLASS,
PJI_PROJECT_STATUS,
ROW_COUNT,
START_DATE,
END_DATE,
PROJECT_ORG_ID,
NEW_PROJECT_ORGANIZATION_ID,
NEW_CLOSED_DATE,
EXTRACTION_TYPE,
EXTRACTION_STATUS,
COST_BUDGET_C_VERSION,
COST_BUDGET_CO_VERSION,
REVENUE_BUDGET_C_VERSION,
REVENUE_BUDGET_CO_VERSION,
COST_FORECAST_C_VERSION,
REVENUE_FORECAST_C_VERSION,
PROJECT_ORGANIZATION_ID,
OLD_CLOSED_DATE,
PLAN_EXTRACTION_STATUS,
BACKLOG_EXTRACTION_STATUS
)
select /*+ ordered full(extr) use_hash(extr)
full(prj) use_hash(prj) parallel(prj) */
1 WORKER_ID,
status.PROJECT_ID,
status.PROJECT_TYPE_CLASS,
'O',
0,
null,
null,
prj.ORG_ID,
prj.CARRYING_OUT_ORGANIZATION_ID,
prj.CLOSED_DATE,
decode(nvl(status.EXTRACTION_STATUS, 'Z'), 'Z', 'F',
decode(l_extraction_type, 'FULL', 'F',
'INCREMENTAL', 'I',
'PARTIAL', 'P')),
status.EXTRACTION_STATUS,
decode(l_extraction_type, 'PARTIAL', -1,
nvl(status.COST_BUDGET_C_VERSION,-2)),
decode(l_extraction_type, 'PARTIAL', -1,
nvl(status.COST_BUDGET_CO_VERSION,-2)),
decode(l_extraction_type, 'PARTIAL', -1,
nvl(status.REVENUE_BUDGET_C_VERSION,-2)),
decode(l_extraction_type, 'PARTIAL', -1,
nvl(status.REVENUE_BUDGET_CO_VERSION,-2)),
decode(l_extraction_type, 'PARTIAL', -1,
nvl(status.COST_FORECAST_C_VERSION,-2)),
decode(l_extraction_type, 'PARTIAL', -1,
nvl(status.REVENUE_FORECAST_C_VERSION,-2)),
prj.CARRYING_OUT_ORGANIZATION_ID,
prj.CLOSED_DATE,
'N',
'N'
from
PJI_PROJ_EXTR_STATUS status,
PA_PROJECTS_ALL prj,
(
select /*+ ordered */
distinct
emt.PROJECT_ID
from
PA_PROJECT_STATUSES stat,
PA_PROJECTS_ALL prj,
PA_XBS_DENORM prg,
PA_PROJ_ELEMENTS emt
where
stat.STATUS_TYPE = 'PROJECT' and
stat.PROJECT_SYSTEM_STATUS_CODE not in ('CLOSED',
'PENDING_CLOSE',
'PENDING_PURGE',
'PURGED') and
prj.PROJECT_STATUS_CODE = stat.PROJECT_STATUS_CODE and
prg.STRUCT_TYPE = 'PRG' and
prg.SUP_PROJECT_ID = prj.PROJECT_ID and
emt.PROJ_ELEMENT_ID = prg.SUB_EMT_ID
) active_projects,
PJI_FM_PROJ_BATCH_MAP existing_projects
where
(
(l_extraction_type = 'PARTIAL' and
status.EXTRACTION_STATUS Is not null)
) and
status.PROJECT_ID = prj.PROJECT_ID and
status.PROJECT_ID in
( select
ver1.PROJECT_ID
from
PA_PROJ_ELEMENT_VERSIONS ver1
where
ver1.OBJECT_TYPE = 'PA_STRUCTURES' and
ver1.PRG_GROUP in
(select
ver2.PRG_GROUP
from
PJI_FM_PROJ_BATCH_MAP map,
PA_PROJ_ELEMENT_VERSIONS ver2
where
ver2.PROJECT_ID = map.PROJECT_ID and
ver2.PRG_GROUP is not null
)
union
select /*+ index (prg, PJI_XBS_DENORM_N3) */
prg.SUP_PROJECT_ID PROJECT_ID
from
PJI_XBS_DENORM prg
where
prg.STRUCT_TYPE = 'PRG' and
prg.SUB_LEVEL = prg.SUP_LEVEL and
prg.PRG_GROUP in
(select /*+ ordered */
ver2.PRG_GROUP
from
PJI_FM_PROJ_BATCH_MAP map,
PA_PROJ_ELEMENT_VERSIONS ver2
where
ver2.PROJECT_ID = map.PROJECT_ID and
ver2.PRG_GROUP is not null
)
)
and
status.PROJECT_ID = existing_projects.PROJECT_ID (+) and
existing_projects.PROJECT_ID is null and
status.PROJECT_ID = active_projects.PROJECT_ID (+);
select
count(*)
into
l_count
from
PJI_FM_PROJ_BATCH_MAP ;
update PJI_PROJ_EXTR_STATUS
set EXTRACTION_STATUS = 'X',
LAST_UPDATE_DATE = sysdate
where l_extraction_type <> 'PARTIAL' and
EXTRACTION_STATUS is null and
PROJECT_ID in (select PROJECT_ID
from PJI_FM_PROJ_BATCH_MAP
where WORKER_ID = 1);
select
distinct
to_date(to_char(tmp2.TIME_ID), 'J') FROM_DATE,
info.PF_CURRENCY_CODE,
tmp2.G_CURRENCY_CODE,
tmp2.RATE_TYPE
from
PJI_ORG_EXTR_INFO info,
(
select
distinct
tmp2.PROJECT_ORG_ID ORG_ID,
decode(invert.INVERT_ID,
'RECVR_GL1', decode(tmp2.DANGLING_RECVR_GL_RATE_FLAG,
'E', to_number(to_char(to_date('1999/01/01',
'YYYY/MM/DD'),
'J')),
tmp2.RECVR_GL_TIME_ID),
'RECVR_GL2', decode(tmp2.DANGLING_RECVR_GL_RATE2_FLAG,
'E', to_number(to_char(to_date('1999/01/01',
'YYYY/MM/DD'),
'J')),
tmp2.RECVR_GL_TIME_ID),
'RECVR_PA1', decode(tmp2.DANGLING_RECVR_PA_RATE_FLAG,
'E', to_number(to_char(to_date('1999/01/01',
'YYYY/MM/DD'),
'J')),
tmp2.RECVR_PA_TIME_ID),
'RECVR_PA2', decode(tmp2.DANGLING_RECVR_PA_RATE2_FLAG,
'E', to_number(to_char(to_date('1999/01/01',
'YYYY/MM/DD'),
'J')),
tmp2.RECVR_PA_TIME_ID)) TIME_ID,
decode(invert.INVERT_ID,
'RECVR_GL1', decode(tmp2.DANGLING_RECVR_GL_RATE_FLAG,
'E', 'Y',
tmp2.DANGLING_RECVR_GL_RATE_FLAG),
'RECVR_GL2', decode(tmp2.DANGLING_RECVR_GL_RATE2_FLAG,
'E', 'Y',
tmp2.DANGLING_RECVR_GL_RATE2_FLAG),
'RECVR_PA1', decode(tmp2.DANGLING_RECVR_PA_RATE_FLAG,
'E', 'Y',
tmp2.DANGLING_RECVR_PA_RATE_FLAG),
'RECVR_PA2', decode(tmp2.DANGLING_RECVR_PA_RATE2_FLAG,
'E', 'Y',
tmp2.DANGLING_RECVR_PA_RATE2_FLAG))
DANGLING_FLAG,
decode(invert.INVERT_ID,
'RECVR_GL1', p_g1_currency_code,
'RECVR_GL2', p_g2_currency_code,
'RECVR_PA1', p_g1_currency_code,
'RECVR_PA2', p_g2_currency_code) G_CURRENCY_CODE,
decode(invert.INVERT_ID,
'RECVR_GL1', PJI_UTILS.GET_RATE_TYPE,
'RECVR_GL2', FND_PROFILE.VALUE('PA_SECONDARY_RATE_TYPE'), /* Modified for bug 12979524 */
'RECVR_PA1', PJI_UTILS.GET_RATE_TYPE,
'RECVR_PA2', FND_PROFILE.VALUE('PA_SECONDARY_RATE_TYPE')) /* Modified for bug 12979524 */
RATE_TYPE
from
PJI_FM_DNGL_FIN tmp2,
(
select 'RECVR_GL1' INVERT_ID from dual union all
select 'RECVR_GL2' INVERT_ID from dual union all
select 'RECVR_PA1' INVERT_ID from dual union all
select 'RECVR_PA2' INVERT_ID from dual
) invert
where
tmp2.WORKER_ID = 0
union
select
distinct
tmp2.PROJECT_ORG_ID ORG_ID,
decode(invert.INVERT_ID,
'GL1', decode(tmp2.DANGLING_GL_RATE_FLAG,
'E', to_number(to_char(to_date('1999/01/01',
'YYYY/MM/DD'),
'J')),
tmp2.GL_TIME_ID),
'GL2', decode(tmp2.DANGLING_GL_RATE2_FLAG,
'E', to_number(to_char(to_date('1999/01/01',
'YYYY/MM/DD'),
'J')),
tmp2.GL_TIME_ID),
'PA1', decode(tmp2.DANGLING_PA_RATE_FLAG,
'E', to_number(to_char(to_date('1999/01/01',
'YYYY/MM/DD'),
'J')),
tmp2.PA_TIME_ID),
'PA2', decode(tmp2.DANGLING_PA_RATE2_FLAG,
'E', to_number(to_char(to_date('1999/01/01',
'YYYY/MM/DD'),
'J')),
tmp2.PA_TIME_ID)) TIME_ID,
decode(invert.INVERT_ID,
'GL1', decode(tmp2.DANGLING_GL_RATE_FLAG,
'E', 'Y', tmp2.DANGLING_GL_RATE_FLAG),
'GL2', decode(tmp2.DANGLING_GL_RATE2_FLAG,
'E', 'Y', tmp2.DANGLING_GL_RATE2_FLAG),
'PA1', decode(tmp2.DANGLING_PA_RATE_FLAG,
'E', 'Y', tmp2.DANGLING_PA_RATE_FLAG),
'PA2', decode(tmp2.DANGLING_PA_RATE2_FLAG,
'E', 'Y', tmp2.DANGLING_PA_RATE2_FLAG))
DANGLING_FLAG,
decode(invert.INVERT_ID,
'GL1', p_g1_currency_code,
'GL2', p_g2_currency_code,
'PA1', p_g1_currency_code,
'PA2', p_g2_currency_code) G_CURRENCY_CODE,
decode(invert.INVERT_ID,
'GL1', PJI_UTILS.GET_RATE_TYPE,
'GL2', FND_PROFILE.VALUE('PA_SECONDARY_RATE_TYPE'), /* Modified for bug 12979524 */
'PA1', PJI_UTILS.GET_RATE_TYPE,
'PA2', FND_PROFILE.VALUE('PA_SECONDARY_RATE_TYPE')) /* Modified for bug 12979524 */
RATE_TYPE
from
PJI_FM_DNGL_ACT tmp2,
(
select 'GL1' INVERT_ID from dual union all
select 'GL2' INVERT_ID from dual union all
select 'PA1' INVERT_ID from dual union all
select 'PA2' INVERT_ID from dual
) invert
where
tmp2.WORKER_ID = 0
) tmp2
where
tmp2.DANGLING_FLAG = 'Y' and
tmp2.ORG_ID = info.ORG_ID;
select
name.NAME CALENDAR_NAME,
pt.USER_PERIOD_TYPE,
tmp2.CALENDAR_MIN_DATE,
tmp2.CALENDAR_MAX_DATE,
min(tmp2.FROM_DATE) FROM_DATE,
max(tmp2.TO_DATE) TO_DATE
from
(
select
decode(tmp2.CALENDAR_TYPE,
'E', p_calendar_id,
'G', info.GL_CALENDAR_ID,
'P', info.PA_CALENDAR_ID) CALENDAR_ID,
to_date(decode(tmp2.CALENDAR_TYPE,
'E', info.EN_CALENDAR_MIN_DATE,
'G', info.GL_CALENDAR_MIN_DATE,
'P', info.PA_CALENDAR_MIN_DATE), 'J') CALENDAR_MIN_DATE,
to_date(decode(tmp2.CALENDAR_TYPE,
'E', info.EN_CALENDAR_MAX_DATE,
'G', info.GL_CALENDAR_MAX_DATE,
'P', info.PA_CALENDAR_MAX_DATE), 'J') CALENDAR_MAX_DATE,
to_date(to_char(min(tmp2.FROM_TIME_ID)), 'J') FROM_DATE,
to_date(to_char(max(tmp2.TO_TIME_ID)), 'J') TO_DATE
from
PJI_ORG_EXTR_INFO info,
(
select
distinct
decode(invert.INVERT_ID,
'PRVDR_EN', tmp2.EXPENDITURE_ORG_ID,
'RECVR_EN', tmp2.PROJECT_ORG_ID,
'EXP_EN', tmp2.EXPENDITURE_ORG_ID,
'PRVDR_GL', tmp2.EXPENDITURE_ORG_ID,
'RECVR_GL', tmp2.PROJECT_ORG_ID,
'EXP_GL', tmp2.EXPENDITURE_ORG_ID,
'PRVDR_PA', tmp2.EXPENDITURE_ORG_ID,
'RECVR_PA', tmp2.PROJECT_ORG_ID,
'EXP_PA', tmp2.EXPENDITURE_ORG_ID) ORG_ID,
decode(invert.INVERT_ID,
'PRVDR_EN', 'E',
'RECVR_EN', 'E',
'EXP_EN', 'E',
'PRVDR_GL', 'G',
'RECVR_GL', 'G',
'EXP_GL', 'G',
'PRVDR_PA', 'P',
'RECVR_PA', 'P',
'EXP_PA', 'P') CALENDAR_TYPE,
decode(invert.INVERT_ID,
'PRVDR_EN', tmp2.PRVDR_GL_TIME_ID,
'RECVR_EN', tmp2.RECVR_GL_TIME_ID,
'EXP_EN', tmp2.EXPENDITURE_ITEM_TIME_ID,
'PRVDR_GL', tmp2.PRVDR_GL_TIME_ID,
'RECVR_GL', tmp2.RECVR_GL_TIME_ID,
'EXP_GL', tmp2.EXPENDITURE_ITEM_TIME_ID,
'PRVDR_PA', tmp2.PRVDR_PA_TIME_ID,
'RECVR_PA', tmp2.RECVR_PA_TIME_ID,
'EXP_PA', tmp2.EXPENDITURE_ITEM_TIME_ID) FROM_TIME_ID,
decode(invert.INVERT_ID,
'PRVDR_EN', tmp2.PRVDR_GL_TIME_ID,
'RECVR_EN', tmp2.RECVR_GL_TIME_ID,
'EXP_EN', tmp2.EXPENDITURE_ITEM_TIME_ID,
'PRVDR_GL', tmp2.PRVDR_GL_TIME_ID,
'RECVR_GL', tmp2.RECVR_GL_TIME_ID,
'EXP_GL', tmp2.EXPENDITURE_ITEM_TIME_ID,
'PRVDR_PA', tmp2.PRVDR_PA_TIME_ID,
'RECVR_PA', tmp2.RECVR_PA_TIME_ID,
'EXP_PA', tmp2.EXPENDITURE_ITEM_TIME_ID) TO_TIME_ID,
decode(invert.INVERT_ID,
'PRVDR_EN', tmp2.DANGLING_PRVDR_EN_TIME_FLAG,
'RECVR_EN', tmp2.DANGLING_RECVR_EN_TIME_FLAG,
'EXP_EN', tmp2.DANGLING_EXP_EN_TIME_FLAG,
'PRVDR_GL', tmp2.DANGLING_PRVDR_GL_TIME_FLAG,
'RECVR_GL', tmp2.DANGLING_RECVR_GL_TIME_FLAG,
'EXP_GL', tmp2.DANGLING_EXP_GL_TIME_FLAG,
'PRVDR_PA', tmp2.DANGLING_PRVDR_PA_TIME_FLAG,
'RECVR_PA', tmp2.DANGLING_RECVR_PA_TIME_FLAG,
'EXP_PA', tmp2.DANGLING_EXP_PA_TIME_FLAG) DANGLING_FLAG
from
PJI_FM_DNGL_FIN tmp2,
(
select 'PRVDR_EN' INVERT_ID from dual union all
select 'RECVR_EN' INVERT_ID from dual union all
select 'EXP_EN' INVERT_ID from dual union all
select 'PRVDR_GL' INVERT_ID from dual union all
select 'RECVR_GL' INVERT_ID from dual union all
select 'EXP_GL' INVERT_ID from dual union all
select 'PRVDR_PA' INVERT_ID from dual union all
select 'RECVR_PA' INVERT_ID from dual union all
select 'EXP_PA' INVERT_ID from dual
) invert
where
tmp2.WORKER_ID = 0
union
select
distinct
tmp2.PROJECT_ORG_ID ORG_ID,
decode(invert.INVERT_ID,
'EN', 'E',
'GL', 'G',
'PA', 'P') CALENDAR_TYPE,
decode(invert.INVERT_ID,
'EN', tmp2.GL_TIME_ID,
'GL', tmp2.GL_TIME_ID,
'PA', tmp2.PA_TIME_ID) FROM_TIME_ID,
decode(invert.INVERT_ID,
'EN', tmp2.GL_TIME_ID,
'GL', tmp2.GL_TIME_ID,
'PA', tmp2.PA_TIME_ID) TO_TIME_ID,
decode(invert.INVERT_ID,
'EN', tmp2.DANGLING_EN_TIME_FLAG,
'GL', tmp2.DANGLING_GL_TIME_FLAG,
'PA', tmp2.DANGLING_PA_TIME_FLAG) DANGLING_FLAG
from
PJI_FM_DNGL_ACT tmp2,
(
select 'EN' INVERT_ID from dual union all
select 'GL' INVERT_ID from dual union all
select 'PA' INVERT_ID from dual
) invert
where
tmp2.WORKER_ID = 0
) tmp2
where
tmp2.DANGLING_FLAG = 'Y' and
tmp2.ORG_ID = info.ORG_ID
group by
decode(tmp2.CALENDAR_TYPE,
'E', p_calendar_id,
'G', info.GL_CALENDAR_ID,
'P', info.PA_CALENDAR_ID),
decode(tmp2.CALENDAR_TYPE,
'E', info.EN_CALENDAR_MIN_DATE,
'G', info.GL_CALENDAR_MIN_DATE,
'P', info.PA_CALENDAR_MIN_DATE),
decode(tmp2.CALENDAR_TYPE,
'E', info.EN_CALENDAR_MAX_DATE,
'G', info.GL_CALENDAR_MAX_DATE,
'P', info.PA_CALENDAR_MAX_DATE)
) tmp2,
PA_TIME_CAL_NAME name, /* Modified for bug 12979524 */
GL_PERIOD_TYPES pt
where
name.CALENDAR_ID = tmp2.CALENDAR_ID and
pt.PERIOD_TYPE = name.PERIOD_TYPE
group by
name.NAME,
pt.USER_PERIOD_TYPE,
tmp2.CALENDAR_MIN_DATE,
tmp2.CALENDAR_MAX_DATE;
select CALENDAR_ID
into l_calendar_id
from PA_TIME_CAL_NAME /* Modified for bug 12979524 */
where PERIOD_SET_NAME = PJI_UTILS.GET_PERIOD_SET_NAME and
PERIOD_TYPE = PJI_UTILS.GET_PERIOD_TYPE;
update PJI_SYSTEM_CONFIG_HIST
set END_DATE = sysdate,
COMPLETION_TEXT = 'Normal completion'
where PROCESS_NAME = g_process || 1 and
RUN_TYPE = 'TRANSITION' and
END_DATE is null;
update PJI_SYSTEM_CONFIG_HIST
set END_DATE = sysdate,
COMPLETION_TEXT = 'Normal completion'
where PROCESS_NAME = g_process || 1 and
END_DATE is null;
update PJI_SYSTEM_CONFIG_HIST
set END_DATE = sysdate,
COMPLETION_TEXT = l_sqlerrm
where PROCESS_NAME = g_process || 1 and
END_DATE is null;
l_sum_fail varchar2(255) := 'The process has failed because process ''Update Project Intelligence Data'' is not complete.';
l_sum_refresh_fail varchar2(255) := 'The process has failed because process ''Update Project Intelligence Data'' has not yet been run.';