[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_RM_DNGL_RES;
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;
l_sum_fm_fail varchar2(255) := 'The process has failed because process ''Update Project Financial Data'' failed.';
l_sum_fm_running varchar2(255) := 'The process has failed because process ''Update Project Financial Data'' is currently running';
l_sum_rm_running varchar2(255) := 'The process has failed because process ''Update Project Resource Management Data'' is currently running.';
/* select count(*)
into l_org_count
from PJI_ORG_EXTR_STATUS
where ROWNUM = 1;
select count(*)
into l_project_count
from PJI_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_prtl_schedule || ', ' ||
to_char(p_organization_id) || ', ' ||
p_include_sub_org || ', ' ||
p_prtl_financial || ', ' ||
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;
/* insert into PJI_ORG_EXTR_STATUS
(
ORGANIZATION_ID,
STATUS,
CREATION_DATE,
LAST_UPDATE_DATE
)
select
org.ORGANIZATION_ID,
null,
sysdate,
sysdate
from
(
select /*+ ordered full(stat) use_hash(stat) *
distinct
org.ORGANIZATION_ID
from
PA_ALL_ORGANIZATIONS org,
PJI_ORG_EXTR_STATUS stat
where
org.ORGANIZATION_ID = stat.ORGANIZATION_ID (+) and
stat.ORGANIZATION_ID is null
) org
where
exists (select /*+ index_ffs(fid, PA_FORECAST_ITEM_DETAILS_N2)
parallel_index(fid, PA_FORECAST_ITEM_DETAILS_N2) *1
from PA_FORECAST_ITEM_DETAILS fid
where fid.EXPENDITURE_ORGANIZATION_ID > 0 and
fid.EXPENDITURE_ORGANIZATION_ID = org.ORGANIZATION_ID);
insert into pji_rm_org_batch_map (
worker_id,
organization_id,
start_date,
end_date,
extraction_type,
row_count)
select
1,
sts.ORGANIZATION_ID,
g_min_date,
g_max_date,
case when sts.STATUS is null
then 'F'
else 'I'
end,
null
from
PJI_ORG_EXTR_STATUS sts;
insert into PJI_RM_ORG_BATCH_MAP
(
WORKER_ID,
ORGANIZATION_ID,
START_DATE,
END_DATE,
EXTRACTION_TYPE,
ROW_COUNT
)
select
1,
sts.ORGANIZATION_ID,
g_min_date,
g_max_date,
'P',
null
from
PJI_ORG_EXTR_STATUS sts,
PJI_ORG_DENORM orgs
where
p_prtl_schedule = 'Y' and
orgs.ORGANIZATION_ID = p_organization_id and
sts.ORGANIZATION_ID = orgs.SUB_ORGANIZATION_ID and
sts.STATUS = 'X';
insert into PJI_RM_ORG_BATCH_MAP
(
WORKER_ID,
ORGANIZATION_ID,
START_DATE,
END_DATE,
EXTRACTION_TYPE,
ROW_COUNT
)
select
1,
sts.ORGANIZATION_ID,
g_min_date,
g_max_date,
'P',
null
from
PJI_ORG_EXTR_STATUS sts
where
p_prtl_schedule = 'Y' and
sts.ORGANIZATION_ID = p_organization_id and
sts.STATUS = 'X';
insert into PJI_RM_ORG_BATCH_MAP
(
WORKER_ID,
ORGANIZATION_ID,
EXTRACTION_TYPE,
ROW_COUNT,
START_DATE,
END_DATE
)
select
1,
extr.ORGANIZATION_ID,
'F',
null,
g_min_date,
g_max_date
from
PJI_ORG_EXTR_STATUS extr
where
extr.STATUS is null;
update PJI_ORG_EXTR_STATUS
set STATUS = 'X'
where l_extraction_type in ('FULL', 'INCREMENTAL') and
ORGANIZATION_ID IN (select map.ORGANIZATION_ID
from PJI_RM_ORG_BATCH_MAP map
where map.WORKER_ID = 1) and
STATUS is null;
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);
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;
PJI_PJI_EXTRACTION_UTILS.UPDATE_PJI_EXTR_SCOPE;
insert into PJI_PJI_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(extr.EXTRACTION_STATUS, 'F', '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)),
decode(extr.EXTRACTION_STATUS, 'I', extr.PROJECT_ORGANIZATION_ID,
prj.CARRYING_OUT_ORGANIZATION_ID),
decode(extr.EXTRACTION_STATUS, 'I', extr.CLOSED_DATE, prj.CLOSED_DATE),
'N',
'N'
from
PJI_PJI_PROJ_EXTR_STATUS extr,
PA_PROJECTS_ALL prj
where
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 = 'F') and
not (l_extraction_type = 'PARTIAL' and
p_prtl_financial = 'N');
update PJI_PJI_PROJ_EXTR_STATUS
set EXTRACTION_STATUS = 'I',
LAST_UPDATE_DATE = sysdate
where l_extraction_type in ('FULL', 'INCREMENTAL') and
EXTRACTION_STATUS = 'F' and
PROJECT_ID in (select PROJECT_ID
from PJI_PJI_PROJ_BATCH_MAP
where WORKER_ID = 1);
select
distinct
decode(sign(bitand(to_number(log.RECORD_TYPE_CODE), 3)),
1, to_date('1999/01/01', 'YYYY/MM/DD'),
log.FROM_DATE) FROM_DATE,
info.PF_CURRENCY_CODE PF_CURRENCY_CODE,
decode(invert.INVERT_ID,
'G1', p_g1_currency_code,
'G2', p_g2_currency_code) G_CURRENCY_CODE,
decode(invert.INVERT_ID,
'G1', PJI_UTILS.GET_RATE_TYPE,
'G2', FND_PROFILE.VALUE('BIS_SECONDARY_RATE_TYPE')) RATE_TYPE
from
PJI_FM_EXTR_PLN_LOG log,
PJI_ORG_EXTR_INFO info,
(
select 'G1' INVERT_ID from dual union all
select 'G2' INVERT_ID from dual
) invert
where
bitand(to_number(log.RECORD_TYPE_CODE), 15) > 0 and
log.PROJECT_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
info.CALENDAR_ID,
to_date(info.CALENDAR_MIN_DATE, 'J') CALENDAR_MIN_DATE,
to_date(info.CALENDAR_MAX_DATE, 'J') CALENDAR_MAX_DATE,
min(log.FROM_DATE) FROM_DATE,
max(log.TO_DATE) TO_DATE
from
PJI_FM_EXTR_PLN_LOG log,
(
select
distinct
decode(invert.INVERT_ID,
'EN', p_calendar_id,
'GL', info.GL_CALENDAR_ID,
'PA', info.PA_CALENDAR_ID) CALENDAR_ID,
decode(invert.INVERT_ID,
'EN', info.EN_CALENDAR_MIN_DATE,
'GL', info.GL_CALENDAR_MIN_DATE,
'PA', info.PA_CALENDAR_MIN_DATE) CALENDAR_MIN_DATE,
decode(invert.INVERT_ID,
'EN', info.EN_CALENDAR_MAX_DATE,
'GL', info.GL_CALENDAR_MAX_DATE,
'PA', info.PA_CALENDAR_MAX_DATE) CALENDAR_MAX_DATE
from
PJI_ORG_EXTR_INFO info,
(
select 'EN' INVERT_ID from dual union all
select 'GL' INVERT_ID from dual union all
select 'PA' INVERT_ID from dual
) invert
where
info.ORG_ID <> -1
) info
where
bitand(to_number(log.RECORD_TYPE_CODE), 16) > 0 and
nvl(log.CALENDAR_ID, -1) = info.CALENDAR_ID
group by
info.CALENDAR_ID,
to_date(info.CALENDAR_MIN_DATE, 'J'),
to_date(info.CALENDAR_MAX_DATE, 'J')
union all
select
tmp1.CALENDAR_ID,
to_date(tmp1.CALENDAR_MIN_DATE, 'J') CALENDAR_MIN_DATE,
to_date(tmp1.CALENDAR_MAX_DATE, 'J') CALENDAR_MAX_DATE,
min(tmp1.FROM_DATE) FROM_DATE,
max(tmp1.TO_DATE) TO_DATE
from
(
select
case when tmp1.CALENDAR_TYPE = 'C'
then p_calendar_id
when tmp1.CALENDAR_TYPE = 'P'
then info.PA_CALENDAR_ID
when tmp1.CALENDAR_TYPE = 'G'
then info.GL_CALENDAR_ID
end CALENDAR_ID,
case when tmp1.CALENDAR_TYPE = 'C'
then info.EN_CALENDAR_MIN_DATE
when tmp1.CALENDAR_TYPE = 'P'
then info.PA_CALENDAR_MIN_DATE
when tmp1.CALENDAR_TYPE = 'G'
then info.GL_CALENDAR_MIN_DATE
end CALENDAR_MIN_DATE,
case when tmp1.CALENDAR_TYPE = 'C'
then info.EN_CALENDAR_MAX_DATE
when tmp1.CALENDAR_TYPE = 'P'
then info.PA_CALENDAR_MAX_DATE
when tmp1.CALENDAR_TYPE = 'G'
then info.GL_CALENDAR_MAX_DATE
end CALENDAR_MAX_DATE,
to_date(to_char(min(tmp1.FROM_TIME_ID)), 'J') FROM_DATE,
to_date(to_char(max(tmp1.TO_TIME_ID)), 'J') TO_DATE
from
PJI_ORG_EXTR_INFO info,
(
select
distinct
tmp1.EXPENDITURE_ORG_ID ORG_ID,
tmp1.CALENDAR_TYPE,
tmp1.TIME_ID FROM_TIME_ID,
tmp1.TIME_ID TO_TIME_ID,
tmp1.DANGLING_FLAG
from
PJI_RM_DNGL_RES tmp1
where
tmp1.WORKER_ID = 0
) tmp1
where
tmp1.DANGLING_FLAG = 'T' and
tmp1.ORG_ID = info.ORG_ID
group by
case when tmp1.CALENDAR_TYPE = 'C'
then p_calendar_id
when tmp1.CALENDAR_TYPE = 'P'
then info.PA_CALENDAR_ID
when tmp1.CALENDAR_TYPE = 'G'
then info.GL_CALENDAR_ID
end,
case when tmp1.CALENDAR_TYPE = 'C'
then info.EN_CALENDAR_MIN_DATE
when tmp1.CALENDAR_TYPE = 'P'
then info.PA_CALENDAR_MIN_DATE
when tmp1.CALENDAR_TYPE = 'G'
then info.GL_CALENDAR_MIN_DATE
end,
case when tmp1.CALENDAR_TYPE = 'C'
then info.EN_CALENDAR_MAX_DATE
when tmp1.CALENDAR_TYPE = 'P'
then info.PA_CALENDAR_MAX_DATE
when tmp1.CALENDAR_TYPE = 'G'
then info.GL_CALENDAR_MAX_DATE
end
) tmp1
group by
tmp1.CALENDAR_ID,
to_date(tmp1.CALENDAR_MIN_DATE, 'J'),
to_date(tmp1.CALENDAR_MAX_DATE, 'J')
) tmp2,
FII_TIME_CAL_NAME name,
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;Commented for bug 13011859 */
select CALENDAR_ID
into l_calendar_id
from FII_TIME_CAL_NAME
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
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.';
SELECT count(*)
INTO l_org_count
FROM pji_org_extr_status;
PJI_PJI_EXTRACTION_UTILS.UPDATE_PJI_RM_WORK_TYPE_INFO(g_process || 1);
PJI_PJI_EXTRACTION_UTILS.UPDATE_RESOURCE_DATA(g_process || 1);