[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
procedure update_plan_org_info(p_worker_id number) is
l_process varchar2(30);
(l_process,'PJI_FM_PLAN_EXTR.UPDATE_PLAN_ORG_INFO(p_worker_id);')) then
Update PJI_ORG_EXTR_INFO orginfo
set
( orginfo.PA_FIRST_PERIOD_ID
, orginfo.PA_FIRST_PERIOD_NAME
, orginfo.PA_FIRST_START_DATE
, orginfo.PA_FIRST_END_DATE
, orginfo.PROJFUNC_CURRENCY_MAU
) =
( select
cal_pa.cal_period_id
, cal_pa.name
, to_number(to_char(cal_pa.start_date,'J'))
, to_number(to_char(cal_pa.end_date,'J'))
, nvl( curr.minimum_accountable_unit
, power( 10, (-1 * curr.precision)))
from fii_time_cal_period cal_pa
, fnd_currencies curr
where cal_pa.calendar_id = orginfo.pa_calendar_id
and DECODE(sign(g_global_start_J - orginfo.pa_calendar_min_date)
, 1, g_global_start_date
, to_char(to_date(orginfo.pa_calendar_min_date,'J'),'DD-MON-YYYY')
) between cal_pa.start_date and cal_pa.end_date
and orginfo.pf_currency_code = curr.currency_code
)
;
Update PJI_ORG_EXTR_INFO orginfo
set
( orginfo.GL_FIRST_PERIOD_ID
, orginfo.GL_FIRST_PERIOD_NAME
, orginfo.GL_FIRST_START_DATE
, orginfo.GL_FIRST_END_DATE
) =
( select
cal_gl.cal_period_id
, cal_gl.name
, to_number(to_char(cal_gl.start_date,'J'))
, to_number(to_char(cal_gl.end_date,'J'))
from fii_time_cal_period cal_gl
where cal_gl.calendar_id = orginfo.gl_calendar_id
and DECODE(sign(g_global_start_J - orginfo.gl_calendar_min_date)
, 1, g_global_start_date
, to_char(to_date(orginfo.gl_calendar_min_date,'J'),'DD-MON-YYYY')
) between cal_gl.start_date and cal_gl.end_date
)
;
(l_process, 'PJI_FM_PLAN_EXTR.UPDATE_PLAN_ORG_INFO(p_worker_id);');
end update_plan_org_info;
Insert into PJI_FM_EXTR_PLNVER1
( WORKER_ID
, PROJECT_ID
, PROJECT_ORGANIZATION_ID
, PROJECT_ORG_ID
, VERSION_ID
, PLAN_TYPE_CODE
, TIME_PHASED_TYPE_CODE
, CURRENT_FLAG
, CURRENT_ORIGINAL_FLAG
, DANGLING_FLAG
, PROJECT_TYPE_CLASS
)
Select /*+ ORDERED
full(bv) use_hash(bv) parallel(bv)
full(fpo) use_hash(fpo) swap_join_inputs(fpo)
*/
map.WORKER_id worker_id
, map.project_id project_id
, map.project_organization_id project_organization_id
, map.project_org_id project_org_id
, bv.budget_version_id version_id
, to_char(fpo.fin_plan_type_id) plan_type_code
,DECODE(bv.version_type
, 'ALL', fpo.all_time_phased_code
, 'COST', fpo.cost_time_phased_code
, 'REVENUE', fpo.revenue_time_phased_code
) time_phased_type_code
, bv.current_flag current_flag
, bv.current_original_flag current_original_flag
, null dangling_flag
, map.project_type_class project_type_class
From
PJI_PJI_PROJ_BATCH_MAP map
, pa_budget_versions bv
, pa_proj_fp_options fpo
Where 1=1
and map.worker_id = p_worker_id
and map.project_id = bv.project_id
and (
( bv.current_flag = 'Y'
and to_char(fpo.fin_plan_type_id) in
(
g_cost_fp_type_code,
g_cost_forecast_fp_type_code,
g_rev_fp_type_code,
g_rev_forecast_fp_type_code
)
)
or
(
bv.current_original_flag = 'Y'
and to_char(fpo.fin_plan_type_id) in
(
g_cost_fp_type_code,
g_rev_fp_type_code
)
)
)
and bv.budget_version_id <> map.cost_budget_c_version
and bv.budget_version_id <> map.cost_budget_co_version
and bv.budget_version_id <> map.revenue_budget_c_version
and bv.budget_version_id <> map.revenue_budget_co_version
and bv.budget_version_id <> map.cost_forecast_c_version
and bv.budget_version_id <> map.revenue_forecast_c_version
and bv.version_type is not null
and bv.fin_plan_type_id is not null
and fpo.project_id = bv.project_id
and bv.fin_plan_type_id = fpo.fin_plan_type_id
and bv.budget_version_id = fpo.fin_plan_version_id
and fpo.fin_plan_option_level_code = 'PLAN_VERSION'
;
Insert /*+ APPEND */ into PJI_FM_EXTR_PLNVER1
( WORKER_ID
, PROJECT_ID
, PROJECT_ORGANIZATION_ID
, PROJECT_ORG_ID
, VERSION_ID
, PLAN_TYPE_CODE
, TIME_PHASED_TYPE_CODE
, CURRENT_FLAG
, CURRENT_ORIGINAL_FLAG
, DANGLING_FLAG
, PROJECT_TYPE_CLASS
)
Select /*+ ORDERED
full(bv) use_hash(bv) parallel(bv)
full(bem) use_hash(bem) swap_join_inputs(bem)
full(pln) use_hash(pln) swap_join_inputs(pln)
*/
map.worker_id worker_id
, map.project_id project_id
, map.project_organization_id project_organization_id
, map.project_org_id project_org_id
, bv.budget_version_id version_id
, bv.budget_type_code plan_type_code
, decode(bem.time_phased_type_code
, 'R', 'N'
, bem.time_phased_type_code) time_phased_type_code
, bv.current_flag current_flag
, bv.current_original_flag current_original_flag
, null dangling_flag
, map.project_type_class project_type_class
From
PJI_PJI_PROJ_BATCH_MAP map
, pa_budget_versions bv
, pa_budget_entry_methods bem
, (select distinct
PROJECT_ID
from PJI_FM_EXTR_PLNVER1
where WORKER_ID = p_worker_id) pln
Where 1=1
and map.worker_id = p_worker_id
and map.project_id = bv.project_id
and bem.budget_entry_method_code = bv.budget_entry_method_code
and (
( bv.current_flag = 'Y'
and bv.budget_type_code in
(
g_cost_budget_type_code,
g_cost_forecast_type_code,
g_revenue_budget_type_code,
g_revenue_forecast_type_code
)
)
or
(
bv.current_original_flag = 'Y'
and bv.budget_type_code in
(
g_cost_budget_type_code,
g_revenue_budget_type_code
)
)
)
and bv.budget_version_id <> map.cost_budget_c_version
and bv.budget_version_id <> map.cost_budget_co_version
and bv.budget_version_id <> map.revenue_budget_c_version
and bv.budget_version_id <> map.revenue_budget_co_version
and bv.budget_version_id <> map.cost_forecast_c_version
and bv.budget_version_id <> map.revenue_forecast_c_version
and bv.version_type is null
and bv.fin_plan_type_id is null
and map.project_id = pln.project_id (+)
and pln.project_id is null;
Insert /*+ APPEND PARALLEL(plan_i) */ into PJI_FM_EXTR_PLAN plan_i -- for OF slice
( LINE_TYPE
, CALENDAR_TYPE_CODE
, WORKER_ID
, PROJECT_ID
, PROJECT_ORG_ID
, PF_CURRENCY_CODE
, VERSION_ID
, PLAN_TYPE_CODE
, CURRENCY_TYPE
, PERIOD_ID
, PERIOD_NAME
, START_DATE
, END_DATE
, REVENUE
, RAW_COST
, BURDENED_COST
, LABOR_HRS
, TIME_DANGLING_FLAG
, RATE_DANGLING_FLAG
, RATE2_DANGLING_FLAG
)
select /*+ ORDERED
full(orginfo) use_hash(orginfo) swap_join_inputs(orginfo)
full(vers) use_hash(vers) parallel(vers)
full(fii_time) use_hash(fii_time) swap_join_inputs(fii_time)
*/
decode(vers.time_phased_type_code
, 'P', 'OF'
, 'G', 'OF'
, 'F1') line_type
, decode(vers.time_phased_type_code
, 'P', 'PA'
, 'G', 'GL'
, 'ENT') calendar_type_code
, p_worker_id worker_id
, vers.project_id project_id
, vers.project_org_id project_org_id
, orginfo.pf_currency_code pf_currency_code
, vers.version_id version_id
, vers.plan_type_code plan_type_code
, 'F' currency_type
, decode(vers.time_phased_type_code
, 'P', decode(sign(fii_time.end_date - to_date(orginfo.pa_first_end_date,'J'))
, -1, orginfo.pa_first_period_id
, fii_time.period_id)
, 'G', decode(sign(fii_time.end_date - to_date(orginfo.gl_first_end_date,'J'))
, -1, orginfo.gl_first_period_id
, fii_time.period_id)
, decode(sign(bl.end_date - g_ent_end_date)
, -1, g_ent_start_period_id
, -1)
) period_id
, decode(vers.time_phased_type_code
, 'P', decode(sign(fii_time.end_date - to_date(orginfo.pa_first_end_date,'J'))
, -1, orginfo.pa_first_period_name
, fii_time.period_name)
, 'G', decode(sign(fii_time.end_date - to_date(orginfo.gl_first_end_date,'J'))
, -1, orginfo.gl_first_period_name
, fii_time.period_name)
, decode(sign(bl.end_date - g_ent_start_date)
, -1, g_ent_start_period_name
, PJI_RM_SUM_MAIN.g_null)
) period_name
, decode(vers.time_phased_type_code
, 'P', decode(sign(fii_time.end_date - to_date(orginfo.pa_first_end_date,'J'))
, -1, to_date(orginfo.pa_first_start_date,'J')
, fii_time.start_date)
, 'G', decode(sign(fii_time.end_date - to_date(orginfo.gl_first_end_date,'J'))
, -1, to_date(orginfo.gl_first_start_date,'J')
, fii_time.start_date)
, decode(sign(bl.end_date - g_ent_start_date)
, -1, g_ent_start_date
, bl.start_date)
) start_date
, decode(vers.time_phased_type_code
, 'P', decode(sign(fii_time.end_date - to_date(orginfo.pa_first_end_date,'J'))
, -1, to_date(orginfo.pa_first_end_date,'J')
, fii_time.end_date)
, 'G', decode(sign(fii_time.end_date - to_date(orginfo.gl_first_end_date,'J'))
, -1, to_date(orginfo.gl_first_end_date,'J')
, fii_time.end_date)
, decode(sign(bl.end_date - g_ent_end_date)
, -1, g_ent_end_date
, bl.end_date)
) end_date
, decode(vers.plan_type_code
, g_revenue_budget_type_code , nvl(bl.revenue,to_number(null))
, g_revenue_forecast_type_code , nvl(bl.revenue,to_number(null))
, g_rev_fp_type_code , nvl(bl.revenue,to_number(null))
, g_rev_forecast_fp_type_code , nvl(bl.revenue,to_number(null))
, to_number(null)
) revenue
, decode(vers.plan_type_code
, g_cost_budget_type_code , nvl(bl.raw_cost,to_number(null))
, g_cost_forecast_type_code , nvl(bl.raw_cost,to_number(null))
, g_cost_fp_type_code , nvl(bl.raw_cost,to_number(null))
, g_cost_forecast_fp_type_code , nvl(bl.raw_cost,to_number(null))
, to_number(null)
) raw_cost
, decode(vers.plan_type_code
, g_cost_budget_type_code , nvl(bl.burdened_cost,to_number(null))
, g_cost_forecast_type_code , nvl(bl.burdened_cost,to_number(null))
, g_cost_fp_type_code , nvl(bl.burdened_cost,to_number(null))
, g_cost_forecast_fp_type_code , nvl(bl.burdened_cost,to_number(null))
, to_number(null)
) burdened_cost
, decode(nvl(ra.track_as_labor_flag,'Y')
, 'Y', nvl(bl.quantity,to_number(null))
, to_number(null)
) labor_hrs
, decode(sign(to_date(orginfo.pa_calendar_max_date,'J') - fii_time.end_date)
, -1, 'P'
, null)||
decode(sign(to_date(orginfo.gl_calendar_max_date,'J') - fii_time.end_date)
, -1, 'G'
, null)||
decode(sign(to_date(orginfo.en_calendar_max_date,'J') - bl.end_date)
, -1, 'E'
, null) time_dangling_flag
, null rate_dangling_flag
, null rate2_dangling_flag
from
PJI_ORG_EXTR_INFO orginfo
, PJI_FM_EXTR_PLNVER1 vers
, pa_resource_assignments ra
, pa_budget_lines bl
, ( select -1 calendar_id
, -1 period_id
, PJI_RM_SUM_MAIN.g_null period_name
, null start_date
, null end_date
from dual
union all
select calendar_id calendar_id
, cal_period_id period_id
, name period_name
, start_date start_date
, end_date end_date
from fii_time_cal_period
) fii_time
where 1=1
and orginfo.projfunc_currency_mau is not null
and vers.worker_id = p_worker_id
and vers.project_org_id = orginfo.org_id
and ra.project_id = vers.project_id
and ra.budget_version_id = vers.version_id
and ra.resource_assignment_id = bl.resource_assignment_id
and decode(vers.time_phased_type_code
, 'P', orginfo.pa_calendar_id
, 'G', orginfo.gl_calendar_id
, -1 ) = fii_time.calendar_id
and decode(vers.time_phased_type_code
, 'P', decode(sign(bl.end_date - to_date(orginfo.pa_first_end_date,'J'))
, -1, orginfo.pa_first_period_name
, bl.period_name)
, 'G', decode(sign(bl.end_date - to_date(orginfo.gl_first_end_date,'J'))
, -1, orginfo.gl_first_period_name
, bl.period_name)
, PJI_RM_SUM_MAIN.g_null
) = fii_time.period_name
and decode(vers.time_phased_type_code
, 'P', orginfo.pa_first_period_id
, 'G', orginfo.gl_first_period_id
, -1
) <= fii_time.period_id
;
Insert /*+ APPEND PARALLEL(plan_i) */ into PJI_FM_EXTR_PLAN plan_i -- OF slice for ENT
( LINE_TYPE
, CALENDAR_TYPE_CODE
, WORKER_ID
, PROJECT_ID
, PROJECT_ORG_ID
, PF_CURRENCY_CODE
, VERSION_ID
, PLAN_TYPE_CODE
, CURRENCY_TYPE
, PERIOD_ID
, PERIOD_NAME
, START_DATE
, END_DATE
, REVENUE
, RAW_COST
, BURDENED_COST
, LABOR_HRS
, TIME_DANGLING_FLAG
, RATE_DANGLING_FLAG
, RATE2_DANGLING_FLAG
)
select /*+ ORDERED
full(orginfo) use_hash(orginfo) swap_join_inputs(orginfo)
full(tmp) use_hash(tmp) parallel(tmp)
full(ent) use_hash(ent) swap_join_inputs(ent)
*/
'OF' line_type
, tmp.calendar_type_code calendar_type_code
, tmp.worker_id
, tmp.project_id
, tmp.project_org_id
, tmp.pf_currency_code
, tmp.version_id
, tmp.plan_type_code plan_type_code
, tmp.currency_type currency_type
, ent.ent_period_id period_id
, ent.name period_name
, ent.start_date start_date
, ent.end_date end_date
, round(sum(nvl(case when (tmp.start_date <= ent.start_date) and
(tmp.end_date >= ent.end_date)
then (ent.end_date - ent.start_date + 1) *
tmp.revenue / (tmp.end_date - tmp.start_date+1)
when (ent.start_date <= tmp.start_date) and
(ent.end_date <= tmp.end_date )
then (ent.end_date - tmp.start_date + 1) *
tmp.revenue / (tmp.end_date - tmp.start_date+1)
when (ent.start_date >= tmp.start_date) and
(ent.end_date >= tmp.end_date)
then (tmp.end_date - ent.start_date + 1) *
tmp.revenue / (tmp.end_date - tmp.start_date+1)
when (ent.start_date <= tmp.start_date) and
(ent.end_date >= tmp.end_date)
then tmp.revenue
else to_number(null)
end,to_number(null)))/orginfo.projfunc_currency_mau
)*orginfo.projfunc_currency_mau revenue
, round(sum(nvl(case when (tmp.start_date <= ent.start_date) and
(tmp.end_date >= ent.end_date)
then (ent.end_date - ent.start_date + 1) *
tmp.raw_cost /(tmp.end_date - tmp.start_date+1)
when (ent.start_date <= tmp.start_date) and
(ent.end_date <= tmp.end_date )
then (ent.end_date - tmp.start_date + 1) *
tmp.raw_cost /(tmp.end_date - tmp.start_date+1)
when (ent.start_date >= tmp.start_date) and
(ent.end_date >= tmp.end_date)
then (tmp.end_date - ent.start_date + 1) *
tmp.raw_cost /(tmp.end_date - tmp.start_date+1)
when (ent.start_date <= tmp.start_date) and
(ent.end_date >= tmp.end_date)
then tmp.raw_cost
else to_number(null)
end,to_number(null)))/orginfo.projfunc_currency_mau
)*orginfo.projfunc_currency_mau raw_cost
, round(sum(nvl(case when (tmp.start_date <= ent.start_date) and
(tmp.end_date >= ent.end_date)
then (ent.end_date - ent.start_date + 1) *
tmp.burdened_cost/(tmp.end_date-tmp.start_date+1)
when (ent.start_date <= tmp.start_date) and
(ent.end_date <= tmp.end_date )
then (ent.end_date - tmp.start_date + 1) *
tmp.burdened_cost/(tmp.end_date-tmp.start_date+1)
when (ent.start_date >= tmp.start_date) and
(ent.end_date >= tmp.end_date)
then (tmp.end_date - ent.start_date + 1) *
tmp.burdened_cost/(tmp.end_date-tmp.start_date+1)
when (ent.start_date <= tmp.start_date) and
(ent.end_date >= tmp.end_date)
then tmp.burdened_cost
else to_number(null)
end,to_number(null)))/orginfo.projfunc_currency_mau
)*orginfo.projfunc_currency_mau burdened_cost
, round(sum(nvl(case when (tmp.start_date <= ent.start_date) and
(tmp.end_date >= ent.end_date)
then (ent.end_date - ent.start_date + 1) *
tmp.labor_hrs/(tmp.end_date - tmp.start_date+1)
when (ent.start_date <= tmp.start_date) and
(ent.end_date <= tmp.end_date )
then (ent.end_date - tmp.start_date + 1) *
tmp.labor_hrs/(tmp.end_date - tmp.start_date+1)
when (ent.start_date >= tmp.start_date) and
(ent.end_date >= tmp.end_date)
then (tmp.end_date - ent.start_date + 1) *
tmp.labor_hrs/(tmp.end_date - tmp.start_date+1)
when (ent.start_date <= tmp.start_date) and
(ent.end_date >= tmp.end_date)
then tmp.labor_hrs
else to_number(null)
end,to_number(null)))/g_labor_mau
)*g_labor_mau labor_hrs
, decode(sign(to_date(orginfo.en_calendar_max_date,'J') - ent.end_date)
, -1, 'E'
, null
) time_dangling_flag
, tmp.rate_dangling_flag rate_dangling_flag
, tmp.rate2_dangling_flag rate2_dangling_flag
from
PJI_ORG_EXTR_INFO orginfo
, PJI_FM_EXTR_PLAN tmp
, fii_time_ent_period ent
where tmp.worker_id = p_worker_id
and tmp.end_date >= ent.start_date
and tmp.start_date <= ent.end_date
and tmp.line_type = 'F1'
and tmp.calendar_type_code = 'ENT'
and tmp.project_org_id = orginfo.org_id
and tmp.time_dangling_flag is null
group by
tmp.worker_id
, tmp.project_id
, tmp.project_org_id
, tmp.pf_currency_code
, tmp.version_id
, tmp.plan_type_code
, tmp.calendar_type_code
, tmp.currency_type
, ent.ent_period_id
, ent.name
, ent.start_date
, ent.end_date
, tmp.start_date
, tmp.end_date
, tmp.time_dangling_flag
, tmp.rate_dangling_flag
, tmp.rate2_dangling_flag
, orginfo.projfunc_currency_mau
, orginfo.en_calendar_max_date
;
insert /*+ append */ into PJI_FM_AGGR_DLY_RATES -- for curr conv
(
WORKER_ID,
PF_CURRENCY_CODE,
TIME_ID,
RATE,
MAU,
RATE2,
MAU2
)
select
-1 worker_id,
tmp.pf_currency_code pf_currency_code,
to_char(tmp.curr_date,'J') time_id,
PJI_UTILS.GET_GLOBAL_RATE_PRIMARY(tmp.pf_currency_code,
tmp.curr_date) rate,
l_mau mau,
PJI_UTILS.GET_GLOBAL_RATE_SECONDARY(tmp.pf_currency_code,
tmp.curr_date) rate,
l_mau2 mau2
from
(
select
distinct
tmp.pf_currency_code pf_currency_code,
decode(invert.rule,
'F', tmp.start_date,
'L', tmp.end_date) curr_date
from
PJI_FM_EXTR_PLAN tmp,
(
select 'F' rule from dual union all
select 'L' rule from dual
) invert
where
worker_id = p_worker_id and
time_dangling_flag is null
) tmp;
Insert /*+ APPEND PARALLEL(plan_i) */ into PJI_FM_EXTR_PLAN plan_i -- convert to GLOBAL CURRENCY
( LINE_TYPE
, CALENDAR_TYPE_CODE
, WORKER_ID
, PROJECT_ID
, PROJECT_ORG_ID
, PF_CURRENCY_CODE
, VERSION_ID
, PLAN_TYPE_CODE
, CURRENCY_TYPE
, PERIOD_ID
, PERIOD_NAME
, START_DATE
, END_DATE
, REVENUE
, RAW_COST
, BURDENED_COST
, LABOR_HRS
, TIME_DANGLING_FLAG
, RATE_DANGLING_FLAG
, RATE2_DANGLING_FLAG
)
SELECT /*+ ORDERED
full(rates) use_hash(rates) swap_join_inputs(rates)
full(tmp) use_hash(tmp) parallel(tmp)
*/
'OG' LINE_TYPE
, tmp.calendar_type_code
, tmp.worker_id
, tmp.project_id
, tmp.project_org_id
, tmp.pf_currency_code
, tmp.version_id
, tmp.plan_type_code
, 'G' currency_type
, tmp.period_id
, tmp.period_name
, tmp.start_date
, tmp.end_date
, round(rates.rate*tmp.revenue/rates.mau)*rates.mau revenue
, round(rates.rate*tmp.raw_cost/rates.mau)*rates.mau raw_cost
, round(rates.rate*tmp.burdened_cost/rates.mau)*rates.mau burdened_cost
, tmp.labor_hrs labor_hrs
, tmp.time_dangling_flag
, case when rates.rate > 0
then null
when rates.rate = -3
then 'U' -- EUR conversion rate for 01-JAN-1999 is missing
else
decode(tmp.plan_type_code
, g_cost_budget_type_code, decode(g_cost_budget_curr_rule
, 'F', 'F'
, 'E')
, g_cost_forecast_type_code, decode(g_cost_forecast_curr_rule
, 'F', 'F'
, 'E')
, g_revenue_budget_type_code, decode(g_revenue_budget_curr_rule
, 'F', 'F'
, 'E')
, g_revenue_forecast_type_code, decode(g_revenue_forecast_curr_rule
, 'F', 'F'
, 'E')
, g_cost_fp_type_code, decode(g_cost_budget_curr_rule
, 'F', 'F'
, 'E')
, g_cost_forecast_fp_type_code, decode(g_cost_forecast_curr_rule
, 'F', 'F'
, 'E')
, g_rev_fp_type_code, decode(g_revenue_budget_curr_rule
, 'F', 'F'
, 'E')
, g_rev_forecast_fp_type_code, decode(g_revenue_forecast_curr_rule
, 'F', 'F'
, 'E')
) end rate_dangling_flag
, null rate2_dangling_flag
FROM
PJI_FM_AGGR_DLY_RATES rates
, PJI_FM_EXTR_PLAN tmp
where tmp.WORKER_ID = p_worker_id
and tmp.LINE_TYPE = 'OF'
and rates.worker_id = -1
and tmp.pf_currency_code = rates.pf_currency_code
and decode(tmp.plan_type_code
, g_cost_budget_type_code, decode(g_cost_budget_curr_rule
, 'F', tmp.start_date
, tmp.end_date)
, g_cost_forecast_type_code, decode(g_cost_forecast_curr_rule
, 'F', tmp.start_date
, tmp.end_date)
, g_revenue_budget_type_code, decode(g_revenue_budget_curr_rule
, 'F', tmp.start_date
, tmp.end_date)
, g_revenue_forecast_type_code, decode(g_revenue_forecast_curr_rule
, 'F', tmp.start_date
, tmp.end_date)
, g_cost_fp_type_code, decode(g_cost_budget_curr_rule
, 'F', tmp.start_date
, tmp.end_date)
, g_cost_forecast_fp_type_code, decode(g_cost_forecast_curr_rule
, 'F', tmp.start_date
, tmp.end_date)
, g_rev_fp_type_code, decode(g_revenue_budget_curr_rule
, 'F', tmp.start_date
, tmp.end_date)
, g_rev_forecast_fp_type_code, decode(g_revenue_forecast_curr_rule
, 'F', tmp.start_date
, tmp.end_date)
) = to_date(rates.time_id,'J')
and tmp.time_dangling_flag is null
and rates.time_id > 0
;
Insert /*+ APPEND PARALLEL(plan_i) */ into PJI_FM_EXTR_PLAN plan_i -- convert to GLOBAL CURRENCY
( LINE_TYPE
, CALENDAR_TYPE_CODE
, WORKER_ID
, PROJECT_ID
, PROJECT_ORG_ID
, PF_CURRENCY_CODE
, VERSION_ID
, PLAN_TYPE_CODE
, CURRENCY_TYPE
, PERIOD_ID
, PERIOD_NAME
, START_DATE
, END_DATE
, REVENUE
, RAW_COST
, BURDENED_COST
, LABOR_HRS
, TIME_DANGLING_FLAG
, RATE_DANGLING_FLAG
, RATE2_DANGLING_FLAG
)
SELECT /*+ ORDERED
full(rates) use_hash(rates) swap_join_inputs(rates)
full(tmp) use_hash(tmp) parallel(tmp)
*/
'OG' LINE_TYPE
, tmp.calendar_type_code
, tmp.worker_id
, tmp.project_id
, tmp.project_org_id
, tmp.pf_currency_code
, tmp.version_id
, tmp.plan_type_code
, '2' currency_type
, tmp.period_id
, tmp.period_name
, tmp.start_date
, tmp.end_date
, round(rates.rate2*tmp.revenue/rates.mau2)*rates.mau2 revenue
, round(rates.rate2*tmp.raw_cost/rates.mau2)*rates.mau2 raw_cost
, round(rates.rate2*tmp.burdened_cost/rates.mau2)*rates.mau2 burdened_cost
, tmp.labor_hrs labor_hrs
, tmp.time_dangling_flag
, null rate_dangling_flag
, case when rates.rate2 > 0
then null
when rates.rate2 = -3
then 'U' -- EUR conversion rate for 01-JAN-1999 is missing
else
decode(tmp.plan_type_code
, g_cost_budget_type_code, decode(g_cost_budget_curr_rule
, 'F', 'F'
, 'E')
, g_cost_forecast_type_code, decode(g_cost_forecast_curr_rule
, 'F', 'F'
, 'E')
, g_revenue_budget_type_code, decode(g_revenue_budget_curr_rule
, 'F', 'F'
, 'E')
, g_revenue_forecast_type_code, decode(g_revenue_forecast_curr_rule
, 'F', 'F'
, 'E')
, g_cost_fp_type_code, decode(g_cost_budget_curr_rule
, 'F', 'F'
, 'E')
, g_cost_forecast_fp_type_code, decode(g_cost_forecast_curr_rule
, 'F', 'F'
, 'E')
, g_rev_fp_type_code, decode(g_revenue_budget_curr_rule
, 'F', 'F'
, 'E')
, g_rev_forecast_fp_type_code, decode(g_revenue_forecast_curr_rule
, 'F', 'F'
, 'E')
) end rate2_dangling_flag
FROM
PJI_FM_AGGR_DLY_RATES rates
, PJI_FM_EXTR_PLAN tmp
where tmp.WORKER_ID = p_worker_id
and tmp.LINE_TYPE = 'OF'
and rates.worker_id = -1
and tmp.pf_currency_code = rates.pf_currency_code
and decode(tmp.plan_type_code
, g_cost_budget_type_code, decode(g_cost_budget_curr_rule
, 'F', tmp.start_date
, tmp.end_date)
, g_cost_forecast_type_code, decode(g_cost_forecast_curr_rule
, 'F', tmp.start_date
, tmp.end_date)
, g_revenue_budget_type_code, decode(g_revenue_budget_curr_rule
, 'F', tmp.start_date
, tmp.end_date)
, g_revenue_forecast_type_code, decode(g_revenue_forecast_curr_rule
, 'F', tmp.start_date
, tmp.end_date)
, g_cost_fp_type_code, decode(g_cost_budget_curr_rule
, 'F', tmp.start_date
, tmp.end_date)
, g_cost_forecast_fp_type_code, decode(g_cost_forecast_curr_rule
, 'F', tmp.start_date
, tmp.end_date)
, g_rev_fp_type_code, decode(g_revenue_budget_curr_rule
, 'F', tmp.start_date
, tmp.end_date)
, g_rev_forecast_fp_type_code, decode(g_revenue_forecast_curr_rule
, 'F', tmp.start_date
, tmp.end_date)
) = to_date(rates.time_id,'J')
and tmp.time_dangling_flag is null
and rates.time_id > 0
;
Insert /*+ APPEND PARALLEL(plan_i) */ into PJI_FM_EXTR_PLAN plan_i -- convert to PA periods
( LINE_TYPE
, CALENDAR_TYPE_CODE
, WORKER_ID
, PROJECT_ID
, PROJECT_ORG_ID
, PF_CURRENCY_CODE
, VERSION_ID
, PLAN_TYPE_CODE
, CURRENCY_TYPE
, PERIOD_ID
, PERIOD_NAME
, START_DATE
, END_DATE
, REVENUE
, RAW_COST
, BURDENED_COST
, LABOR_HRS
, TIME_DANGLING_FLAG
, RATE_DANGLING_FLAG
, RATE2_DANGLING_FLAG
)
select /*+ ORDERED
full(orginfo) use_hash(orginfo) swap_join_inputs(orginfo)
full(tmp) use_hash(tmp) parallel(tmp)
full(cal_pa) use_hash(cal_pa) swap_join_inputs(cal_pa)
pq_distribute(cal_pa, none, broadcast)
pq_distribute(tmp, broadcast, none)
*/
decode(tmp.LINE_TYPE
, 'OF', 'CF'
, 'OG', 'CG'
) LINE_TYPE
, 'PA' calendar_type_code
, tmp.worker_id
, tmp.project_id
, tmp.project_org_id project_org_id
, tmp.pf_currency_code pf_currency_code
, tmp.version_id
, tmp.plan_type_code
, tmp.currency_type currency_type
, cal_pa.cal_period_id period_id
, cal_pa.name period_name
, cal_pa.start_date start_date
, cal_pa.end_date end_date
, round((nvl(case when (tmp.start_date <= cal_pa.start_date) and
(tmp.end_date >= cal_pa.end_date)
then (cal_pa.end_date - cal_pa.start_date + 1) *
tmp.revenue / (tmp.end_date - tmp.start_date + 1)
when (cal_pa.start_date <= tmp.start_date) and
(cal_pa.end_date <= tmp.end_date )
then (cal_pa.end_date - tmp.start_date + 1) *
tmp.revenue / (tmp.end_date - tmp.start_date + 1)
when (cal_pa.start_date >= tmp.start_date) and
(cal_pa.end_date >= tmp.end_date)
then (tmp.end_date - cal_pa.start_date + 1) *
tmp.revenue / (tmp.end_date - tmp.start_date + 1)
when (cal_pa.start_date <= tmp.start_date) and
(cal_pa.end_date >= tmp.end_date)
then tmp.revenue
else to_number(null)
end,to_number(null)))/orginfo.projfunc_currency_mau
)*orginfo.projfunc_currency_mau revenue
, round((nvl(case when (tmp.start_date <= cal_pa.start_date) and
(tmp.end_date >= cal_pa.end_date)
then (cal_pa.end_date - cal_pa.start_date + 1) *
tmp.raw_cost / (tmp.end_date - tmp.start_date + 1)
when (cal_pa.start_date <= tmp.start_date) and
(cal_pa.end_date <= tmp.end_date )
then (cal_pa.end_date - tmp.start_date + 1) *
tmp.raw_cost / (tmp.end_date - tmp.start_date + 1)
when (cal_pa.start_date >= tmp.start_date) and
(cal_pa.end_date >= tmp.end_date)
then (tmp.end_date - cal_pa.start_date + 1) *
tmp.raw_cost / (tmp.end_date - tmp.start_date + 1)
when (cal_pa.start_date <= tmp.start_date) and
(cal_pa.end_date >= tmp.end_date)
then tmp.raw_cost
else to_number(null)
end,to_number(null)))/orginfo.projfunc_currency_mau
)*orginfo.projfunc_currency_mau raw_cost
, round((nvl(case when (tmp.start_date <= cal_pa.start_date) and
(tmp.end_date >= cal_pa.end_date)
then (cal_pa.end_date - cal_pa.start_date + 1) *
tmp.burdened_cost/(tmp.end_date-tmp.start_date +1)
when (cal_pa.start_date <= tmp.start_date) and
(cal_pa.end_date <= tmp.end_date )
then (cal_pa.end_date - tmp.start_date + 1) *
tmp.burdened_cost/(tmp.end_date-tmp.start_date +1)
when (cal_pa.start_date >= tmp.start_date) and
(cal_pa.end_date >= tmp.end_date)
then (tmp.end_date - cal_pa.start_date + 1) *
tmp.burdened_cost/(tmp.end_date-tmp.start_date +1)
when (cal_pa.start_date <= tmp.start_date) and
(cal_pa.end_date >= tmp.end_date)
then tmp.burdened_cost
else to_number(null)
end,to_number(null)))/orginfo.projfunc_currency_mau
)*orginfo.projfunc_currency_mau burdened_cost
, round((nvl(case when (tmp.start_date <= cal_pa.start_date) and
(tmp.end_date >= cal_pa.end_date)
then (cal_pa.end_date - cal_pa.start_date + 1) *
tmp.labor_hrs / (tmp.end_date - tmp.start_date +1)
when (cal_pa.start_date <= tmp.start_date) and
(cal_pa.end_date <= tmp.end_date )
then (cal_pa.end_date - tmp.start_date + 1) *
tmp.labor_hrs / (tmp.end_date - tmp.start_date +1)
when (cal_pa.start_date >= tmp.start_date) and
(cal_pa.end_date >= tmp.end_date)
then (tmp.end_date - cal_pa.start_date + 1) *
tmp.labor_hrs / (tmp.end_date - tmp.start_date +1)
when (cal_pa.start_date <= tmp.start_date) and
(cal_pa.end_date >= tmp.end_date)
then tmp.labor_hrs
else to_number(null)
end,to_number(null)))/g_labor_mau
)*g_labor_mau labor_hrs
, decode(sign(to_date(orginfo.pa_calendar_max_date,'J') - cal_pa.end_date)
, -1, 'P'
, tmp.time_dangling_flag
) time_dangling_flag
, tmp.rate_dangling_flag rate_dangling_flag
, tmp.rate2_dangling_flag rate2_dangling_flag
from
PJI_ORG_EXTR_INFO orginfo
, PJI_FM_EXTR_PLAN tmp
, fii_time_cal_period cal_pa
where tmp.worker_id = p_worker_id
and tmp.end_date >= cal_pa.start_date
and tmp.start_date <= cal_pa.end_date
and tmp.calendar_type_code <> 'PA'
and orginfo.pa_calendar_id = cal_pa.calendar_id
and orginfo.org_id = tmp.project_org_id
and tmp.LINE_TYPE in ( 'OF' , 'OG' )
and tmp.time_dangling_flag is null
and tmp.rate_dangling_flag is null
and tmp.rate2_dangling_flag is null
;
Insert /*+ APPEND PARALLEL(plan_i) */ into PJI_FM_EXTR_PLAN plan_i -- convert to GL periods
( LINE_TYPE
, CALENDAR_TYPE_CODE
, WORKER_ID
, PROJECT_ID
, PROJECT_ORG_ID
, PF_CURRENCY_CODE
, VERSION_ID
, PLAN_TYPE_CODE
, CURRENCY_TYPE
, PERIOD_ID
, PERIOD_NAME
, START_DATE
, END_DATE
, REVENUE
, RAW_COST
, BURDENED_COST
, LABOR_HRS
, TIME_DANGLING_FLAG
, RATE_DANGLING_FLAG
, RATE2_DANGLING_FLAG
)
select /*+ ORDERED
full(orginfo) use_hash(orginfo) swap_join_inputs(orginfo)
full(tmp) use_hash(tmp) parallel(tmp)
full(cal_gl) use_hash(cal_gl) swap_join_inputs(cal_gl)
pq_distribute(cal_gl, none, broadcast)
pq_distribute(tmp, broadcast, none)
*/
decode(tmp.LINE_TYPE
, 'OF', 'CF'
, 'OG', 'CG'
) LINE_TYPE
, 'GL' calendar_type_code
, tmp.worker_id
, tmp.project_id
, tmp.project_org_id project_org_id
, tmp.pf_currency_code pf_currency_code
, tmp.version_id
, tmp.plan_type_code
, tmp.currency_type currency_type
, cal_gl.cal_period_id period_id
, cal_gl.name period_name
, cal_gl.start_date start_date
, cal_gl.end_date end_date
, round((nvl(case when (tmp.start_date <= cal_gl.start_date) and
(tmp.end_date >= cal_gl.end_date)
then (cal_gl.end_date - cal_gl.start_date + 1) *
tmp.revenue / (tmp.end_date - tmp.start_date + 1)
when (cal_gl.start_date <= tmp.start_date) and
(cal_gl.end_date <= tmp.end_date )
then (cal_gl.end_date - tmp.start_date + 1) *
tmp.revenue / (tmp.end_date - tmp.start_date + 1)
when (cal_gl.start_date >= tmp.start_date) and
(cal_gl.end_date >= tmp.end_date)
then (tmp.end_date - cal_gl.start_date + 1) *
tmp.revenue / (tmp.end_date - tmp.start_date + 1)
when (cal_gl.start_date <= tmp.start_date) and
(cal_gl.end_date >= tmp.end_date)
then tmp.revenue
else to_number(null)
end,to_number(null)))/orginfo.projfunc_currency_mau
)*orginfo.projfunc_currency_mau revenue
, round((nvl(case when (tmp.start_date <= cal_gl.start_date) and
(tmp.end_date >= cal_gl.end_date)
then (cal_gl.end_date - cal_gl.start_date + 1) *
tmp.raw_cost / (tmp.end_date - tmp.start_date + 1)
when (cal_gl.start_date <= tmp.start_date) and
(cal_gl.end_date <= tmp.end_date )
then (cal_gl.end_date - tmp.start_date + 1) *
tmp.raw_cost / (tmp.end_date - tmp.start_date + 1)
when (cal_gl.start_date >= tmp.start_date) and
(cal_gl.end_date >= tmp.end_date)
then (tmp.end_date - cal_gl.start_date + 1) *
tmp.raw_cost / (tmp.end_date - tmp.start_date + 1)
when (cal_gl.start_date <= tmp.start_date) and
(cal_gl.end_date >= tmp.end_date)
then tmp.raw_cost
else to_number(null)
end,to_number(null)))/orginfo.projfunc_currency_mau
)*orginfo.projfunc_currency_mau raw_cost
, round((nvl(case when (tmp.start_date <= cal_gl.start_date) and
(tmp.end_date >= cal_gl.end_date)
then (cal_gl.end_date - cal_gl.start_date + 1) *
tmp.burdened_cost/(tmp.end_date-tmp.start_date +1)
when (cal_gl.start_date <= tmp.start_date) and
(cal_gl.end_date <= tmp.end_date )
then (cal_gl.end_date - tmp.start_date + 1) *
tmp.burdened_cost/(tmp.end_date-tmp.start_date +1)
when (cal_gl.start_date >= tmp.start_date) and
(cal_gl.end_date >= tmp.end_date)
then (tmp.end_date - cal_gl.start_date + 1) *
tmp.burdened_cost/(tmp.end_date-tmp.start_date +1)
when (cal_gl.start_date <= tmp.start_date) and
(cal_gl.end_date >= tmp.end_date)
then tmp.burdened_cost
else to_number(null)
end,to_number(null)))/orginfo.projfunc_currency_mau
)*orginfo.projfunc_currency_mau burdened_cost
, round((nvl(case when (tmp.start_date <= cal_gl.start_date) and
(tmp.end_date >= cal_gl.end_date)
then (cal_gl.end_date - cal_gl.start_date + 1) *
tmp.labor_hrs / (tmp.end_date - tmp.start_date +1)
when (cal_gl.start_date <= tmp.start_date) and
(cal_gl.end_date <= tmp.end_date )
then (cal_gl.end_date - tmp.start_date + 1) *
tmp.labor_hrs / (tmp.end_date - tmp.start_date +1)
when (cal_gl.start_date >= tmp.start_date) and
(cal_gl.end_date >= tmp.end_date)
then (tmp.end_date - cal_gl.start_date + 1) *
tmp.labor_hrs / (tmp.end_date - tmp.start_date +1)
when (cal_gl.start_date <= tmp.start_date) and
(cal_gl.end_date >= tmp.end_date)
then tmp.labor_hrs
else to_number(null)
end,to_number(null)))/g_labor_mau
)*g_labor_mau labor_hrs
, decode(sign(to_date(orginfo.gl_calendar_max_date,'J') - cal_gl.end_date)
, -1, 'G'
, tmp.time_dangling_flag
) time_dangling_flag
, tmp.rate_dangling_flag rate_dangling_flag
, tmp.rate2_dangling_flag rate2_dangling_flag
from
PJI_ORG_EXTR_INFO orginfo
, PJI_FM_EXTR_PLAN tmp
, fii_time_cal_period cal_gl
where tmp.worker_id = p_worker_id
and tmp.end_date >= cal_gl.start_date
and tmp.start_date <= cal_gl.end_date
and tmp.calendar_type_code <> 'GL'
and orginfo.gl_calendar_id = cal_gl.calendar_id
and orginfo.org_id = tmp.project_org_id
and tmp.LINE_TYPE in ( 'OF' , 'OG' )
and tmp.time_dangling_flag is null
and tmp.rate_dangling_flag is null
and tmp.rate2_dangling_flag is null
;
Insert /*+ APPEND PARALLEL(plan_i) */ into PJI_FM_EXTR_PLAN plan_i -- convert to ENT periods
( LINE_TYPE
, CALENDAR_TYPE_CODE
, WORKER_ID
, PROJECT_ID
, PROJECT_ORG_ID
, PF_CURRENCY_CODE
, VERSION_ID
, PLAN_TYPE_CODE
, CURRENCY_TYPE
, PERIOD_ID
, PERIOD_NAME
, START_DATE
, END_DATE
, REVENUE
, RAW_COST
, BURDENED_COST
, LABOR_HRS
, TIME_DANGLING_FLAG
, RATE_DANGLING_FLAG
, RATE2_DANGLING_FLAG
)
select /*+ ORDERED
full(orginfo) use_hash(orginfo) swap_join_inputs(orginfo)
full(tmp) use_hash(tmp) parallel(tmp)
cache(ent)
pq_distribute(tmp, broadcast, none)
*/
decode(tmp.LINE_TYPE
, 'OF', 'CF'
, 'OG', 'CG'
) LINE_TYPE
, 'ENT' calendar_type_code
, tmp.worker_id
, tmp.project_id
, tmp.project_org_id
, tmp.pf_currency_code
, tmp.version_id
, tmp.plan_type_code
, tmp.currency_type currency_type
, ent.ent_period_id period_id
, ent.name period_name
, ent.start_date start_date
, ent.end_date end_date
, round((nvl(case when (tmp.start_date <= ent.start_date) and
(tmp.end_date >= ent.end_date)
then (ent.end_date - ent.start_date + 1) *
tmp.revenue / (tmp.end_date - tmp.start_date + 1)
when (ent.start_date <= tmp.start_date) and
(ent.end_date <= tmp.end_date )
then (ent.end_date - tmp.start_date + 1) *
tmp.revenue / (tmp.end_date - tmp.start_date + 1)
when (ent.start_date >= tmp.start_date) and
(ent.end_date >= tmp.end_date)
then (tmp.end_date - ent.start_date + 1) *
tmp.revenue / (tmp.end_date - tmp.start_date + 1)
when (ent.start_date <= tmp.start_date) and
(ent.end_date >= tmp.end_date)
then tmp.revenue
else to_number(null)
end,to_number(null)))/orginfo.projfunc_currency_mau
)*orginfo.projfunc_currency_mau revenue
, round((nvl(case when (tmp.start_date <= ent.start_date) and
(tmp.end_date >= ent.end_date)
then (ent.end_date - ent.start_date + 1) *
tmp.raw_cost / (tmp.end_date - tmp.start_date + 1)
when (ent.start_date <= tmp.start_date) and
(ent.end_date <= tmp.end_date )
then (ent.end_date - tmp.start_date + 1) *
tmp.raw_cost / (tmp.end_date - tmp.start_date + 1)
when (ent.start_date >= tmp.start_date) and
(ent.end_date >= tmp.end_date)
then (tmp.end_date - ent.start_date + 1) *
tmp.raw_cost / (tmp.end_date - tmp.start_date + 1)
when (ent.start_date <= tmp.start_date) and
(ent.end_date >= tmp.end_date)
then tmp.raw_cost
else to_number(null)
end,to_number(null)))/orginfo.projfunc_currency_mau
)*orginfo.projfunc_currency_mau raw_cost
, round((nvl(case when (tmp.start_date <= ent.start_date) and
(tmp.end_date >= ent.end_date)
then (ent.end_date - ent.start_date + 1) *
tmp.burdened_cost/(tmp.end_date-tmp.start_date +1)
when (ent.start_date <= tmp.start_date) and
(ent.end_date <= tmp.end_date )
then (ent.end_date - tmp.start_date + 1) *
tmp.burdened_cost/(tmp.end_date-tmp.start_date +1)
when (ent.start_date >= tmp.start_date) and
(ent.end_date >= tmp.end_date)
then (tmp.end_date - ent.start_date + 1) *
tmp.burdened_cost/(tmp.end_date-tmp.start_date +1)
when (ent.start_date <= tmp.start_date) and
(ent.end_date >= tmp.end_date)
then tmp.burdened_cost
else to_number(null)
end,to_number(null)))/orginfo.projfunc_currency_mau
)*orginfo.projfunc_currency_mau burdened_cost
, round((nvl(case when (tmp.start_date <= ent.start_date) and
(tmp.end_date >= ent.end_date)
then (ent.end_date - ent.start_date + 1) *
tmp.labor_hrs / (tmp.end_date - tmp.start_date +1)
when (ent.start_date <= tmp.start_date) and
(ent.end_date <= tmp.end_date )
then (ent.end_date - tmp.start_date + 1) *
tmp.labor_hrs / (tmp.end_date - tmp.start_date +1)
when (ent.start_date >= tmp.start_date) and
(ent.end_date >= tmp.end_date)
then (tmp.end_date - ent.start_date + 1) *
tmp.labor_hrs / (tmp.end_date - tmp.start_date +1)
when (ent.start_date <= tmp.start_date) and
(ent.end_date >= tmp.end_date)
then tmp.labor_hrs
else to_number(null)
end,to_number(null)))/g_labor_mau
)*g_labor_mau labor_hrs
, decode(sign(to_date(orginfo.en_calendar_max_date,'J') - ent.end_date)
, -1, 'G'
, tmp.time_dangling_flag
) time_dangling_flag
, tmp.rate_dangling_flag rate_dangling_flag
, tmp.rate2_dangling_flag rate2_dangling_flag
from
PJI_ORG_EXTR_INFO orginfo
, PJI_FM_EXTR_PLAN tmp
, fii_time_ent_period ent
where tmp.worker_id = p_worker_id
and tmp.end_date >= ent.start_date
and tmp.start_date <= ent.end_date
and tmp.calendar_type_code <> 'ENT'
and tmp.LINE_TYPE in ( 'OF' , 'OG' )
and tmp.project_org_id = orginfo.org_id
and tmp.time_dangling_flag is null
and tmp.rate_dangling_flag is null
and tmp.rate2_dangling_flag is null
;
Insert /*+ APPEND PARALLEL(plan_i) */ into PJI_FM_EXTR_PLAN plan_i -- convert to ENTW periods
( LINE_TYPE
, CALENDAR_TYPE_CODE
, WORKER_ID
, PROJECT_ID
, PROJECT_ORG_ID
, PF_CURRENCY_CODE
, VERSION_ID
, PLAN_TYPE_CODE
, CURRENCY_TYPE
, PERIOD_ID
, PERIOD_NAME
, START_DATE
, END_DATE
, REVENUE
, RAW_COST
, BURDENED_COST
, LABOR_HRS
, TIME_DANGLING_FLAG
, RATE_DANGLING_FLAG
, RATE2_DANGLING_FLAG
)
select /*+ ORDERED
full(orginfo) use_hash(orginfo) swap_join_inputs(orginfo)
full(tmp) use_hash(tmp) parallel(tmp)
cache(entw)
pq_distribute(tmp, broadcast, none)
*/
decode(tmp.LINE_TYPE
, 'OF', 'CF'
, 'OG', 'CG'
) LINE_TYPE
, 'ENTW' calendar_type_code
, tmp.worker_id
, tmp.project_id
, tmp.project_org_id
, tmp.pf_currency_code
, tmp.version_id
, tmp.plan_type_code
, tmp.currency_type currency_type
, entw.week_id period_id
, PJI_RM_SUM_MAIN.g_null period_name
, entw.start_date start_date
, entw.end_date end_date
, round((nvl(case when (tmp.start_date <= entw.start_date) and
(tmp.end_date >= entw.end_date)
then (entw.end_date - entw.start_date + 1) *
tmp.revenue / (tmp.end_date - tmp.start_date + 1)
when (entw.start_date <= tmp.start_date) and
(entw.end_date <= tmp.end_date )
then (entw.end_date - tmp.start_date + 1) *
tmp.revenue / (tmp.end_date - tmp.start_date + 1)
when (entw.start_date >= tmp.start_date) and
(entw.end_date >= tmp.end_date)
then (tmp.end_date - entw.start_date + 1) *
tmp.revenue / (tmp.end_date - tmp.start_date + 1)
when (entw.start_date <= tmp.start_date) and
(entw.end_date >= tmp.end_date)
then tmp.revenue
else to_number(null)
end,to_number(null)))/orginfo.projfunc_currency_mau
)*orginfo.projfunc_currency_mau revenue
, round((nvl(case when (tmp.start_date <= entw.start_date) and
(tmp.end_date >= entw.end_date)
then (entw.end_date - entw.start_date + 1) *
tmp.raw_cost / (tmp.end_date - tmp.start_date + 1)
when (entw.start_date <= tmp.start_date) and
(entw.end_date <= tmp.end_date )
then (entw.end_date - tmp.start_date + 1) *
tmp.raw_cost / (tmp.end_date - tmp.start_date + 1)
when (entw.start_date >= tmp.start_date) and
(entw.end_date >= tmp.end_date)
then (tmp.end_date - entw.start_date + 1) *
tmp.raw_cost / (tmp.end_date - tmp.start_date + 1)
when (entw.start_date <= tmp.start_date) and
(entw.end_date >= tmp.end_date)
then tmp.raw_cost
else to_number(null)
end,to_number(null)))/orginfo.projfunc_currency_mau
)*orginfo.projfunc_currency_mau raw_cost
, round((nvl(case when (tmp.start_date <= entw.start_date) and
(tmp.end_date >= entw.end_date)
then (entw.end_date - entw.start_date + 1) *
tmp.burdened_cost/(tmp.end_date-tmp.start_date +1)
when (entw.start_date <= tmp.start_date) and
(entw.end_date <= tmp.end_date )
then (entw.end_date - tmp.start_date + 1) *
tmp.burdened_cost/(tmp.end_date-tmp.start_date +1)
when (entw.start_date >= tmp.start_date) and
(entw.end_date >= tmp.end_date)
then (tmp.end_date - entw.start_date + 1) *
tmp.burdened_cost/(tmp.end_date-tmp.start_date +1)
when (entw.start_date <= tmp.start_date) and
(entw.end_date >= tmp.end_date)
then tmp.burdened_cost
else to_number(null)
end,to_number(null)))/orginfo.projfunc_currency_mau
)*orginfo.projfunc_currency_mau burdened_cost
, round((nvl(case when (tmp.start_date <= entw.start_date) and
(tmp.end_date >= entw.end_date)
then (entw.end_date - entw.start_date + 1) *
tmp.labor_hrs / (tmp.end_date - tmp.start_date +1)
when (entw.start_date <= tmp.start_date) and
(entw.end_date <= tmp.end_date )
then (entw.end_date - tmp.start_date + 1) *
tmp.labor_hrs / (tmp.end_date - tmp.start_date +1)
when (entw.start_date >= tmp.start_date) and
(entw.end_date >= tmp.end_date)
then (tmp.end_date - entw.start_date + 1) *
tmp.labor_hrs / (tmp.end_date - tmp.start_date +1)
when (entw.start_date <= tmp.start_date) and
(entw.end_date >= tmp.end_date)
then tmp.labor_hrs
else to_number(null)
end,to_number(null)))/g_labor_mau
)*g_labor_mau labor_hrs
, decode(sign(to_date(orginfo.en_calendar_max_date,'J') - entw.end_date)
, -1, 'G'
, tmp.time_dangling_flag
) time_dangling_flag
, tmp.rate_dangling_flag rate_dangling_flag
, tmp.rate2_dangling_flag rate2_dangling_flag
from
PJI_ORG_EXTR_INFO orginfo
, PJI_FM_EXTR_PLAN tmp
, fii_time_week entw
where tmp.worker_id = p_worker_id
and tmp.end_date >= entw.start_date
and tmp.start_date <= entw.end_date
and tmp.calendar_type_code <> 'ENTW'
and tmp.LINE_TYPE in ( 'OF' , 'OG' )
and tmp.project_org_id = orginfo.org_id
and tmp.time_dangling_flag is null
and tmp.rate_dangling_flag is null
and tmp.rate2_dangling_flag is null
;
Insert /*+ APPEND */ into PJI_FM_EXTR_PLN_LOG
(PROJECT_ID
,PROJECT_ORG_ID
,PLAN_TYPE_CODE
,BUDGET_VERSION_ID
,RECORD_TYPE_CODE
,FROM_DATE
,TO_DATE
,CALENDAR_ID
)
select /*+ ORDERED
full(orginfo) use_hash(orginfo) swap_join_inputs(orginfo)
full(tmp) use_hash(tmp) parallel(tmp)
*/
tmp.PROJECT_ID
, tmp.PROJECT_ORG_ID
, tmp.PLAN_TYPE_CODE
, tmp.VERSION_ID
, to_char(decode(tmp.RATE_DANGLING_FLAG,
'U', 1, 0) + -- EUR rate for 01-JAN-1999 missing
decode(tmp.RATE2_DANGLING_FLAG,
'U', 2, 0) + -- EUR rate for 01-JAN-1999 missing
decode(tmp.RATE_DANGLING_FLAG,
null, 0, 4) + -- Global 1 rate missing
decode(tmp.RATE2_DANGLING_FLAG,
null, 0, 8) + -- Global 2 rate missing
decode(tmp.TIME_DANGLING_FLAG,
null, 0, 16) -- Calendar setup missing
) RECORD_TYPE_CODE
, tmp.START_DATE
, tmp.END_DATE
, decode(tmp.CALENDAR_TYPE_CODE
, 'PA', orginfo.PA_CALENDAR_ID
, 'GL', orginfo.GL_CALENDAR_ID
, null) CALENDAR_ID
from PJI_ORG_EXTR_INFO orginfo
, PJI_FM_EXTR_PLAN tmp
where tmp.WORKER_ID = p_worker_id
and ( tmp.TIME_DANGLING_FLAG is not null
or tmp.RATE_DANGLING_FLAG is not null
or tmp.RATE2_DANGLING_FLAG is not null)
and tmp.project_org_id = orginfo.org_id
;
select
TXN_CURR_FLAG,
GLOBAL_CURR2_FLAG
into
l_txn_currency_flag,
l_g2_currency_flag
from
PJI_SYSTEM_SETTINGS;
insert /*+ append parallel(pln_i) */ into PJI_FM_AGGR_PLN pln_i
(
WORKER_ID,
PROJECT_ID,
PROJECT_ORG_ID,
PROJECT_ORGANIZATION_ID,
PROJECT_TYPE_CLASS,
CALENDAR_TYPE_CODE,
CURR_RECORD_TYPE_ID,
CURRENCY_CODE,
TIME_PHASED_TYPE_CODE,
TIME_ID,
PERIOD_NAME,
START_DATE,
END_DATE,
CURR_BGT_REVENUE,
CURR_BGT_RAW_COST,
CURR_BGT_BRDN_COST,
CURR_BGT_LABOR_HRS,
CURR_ORIG_BGT_REVENUE,
CURR_ORIG_BGT_RAW_COST,
CURR_ORIG_BGT_BRDN_COST,
CURR_ORIG_BGT_LABOR_HRS,
CURR_FORECAST_REVENUE,
CURR_FORECAST_RAW_COST,
CURR_FORECAST_BRDN_COST,
CURR_FORECAST_LABOR_HRS
)
select
tmp1.WORKER_ID,
tmp1.PROJECT_ID,
tmp1.PROJECT_ORG_ID,
tmp1.PROJECT_ORGANIZATION_ID,
tmp1.PROJECT_TYPE_CLASS,
tmp1.CALENDAR_TYPE_CODE,
sum(tmp1.CURR_RECORD_TYPE_ID) CURR_RECORD_TYPE_ID,
nvl(tmp1.CURRENCY_CODE, 'PJI$NULL') CURRENCY_CODE,
tmp1.TIME_PHASED_TYPE_CODE,
tmp1.PERIOD_ID,
tmp1.PERIOD_NAME,
tmp1.START_DATE,
tmp1.END_DATE,
max(tmp1.CURR_BGT_REVENUE) CURR_BGT_REVENUE,
max(tmp1.CURR_BGT_RAW_COST) CURR_BGT_RAW_COST,
max(tmp1.CURR_BGT_BRDN_COST) CURR_BGT_BRDN_COST,
max(tmp1.CURR_BGT_LABOR_HRS) CURR_BGT_LABOR_HRS,
max(tmp1.CURR_ORIG_BGT_REVENUE) CURR_ORIG_BGT_REVENUE,
max(tmp1.CURR_ORIG_BGT_RAW_COST) CURR_ORIG_BGT_RAW_COST,
max(tmp1.CURR_ORIG_BGT_BRDN_COST) CURR_ORIG_BGT_BRDN_COST,
max(tmp1.CURR_ORIG_BGT_LABOR_HRS) CURR_ORIG_BGT_LABOR_HRS,
max(tmp1.CURR_FORECAST_REVENUE) CURR_FORECAST_REVENUE,
max(tmp1.CURR_FORECAST_RAW_COST) CURR_FORECAST_RAW_COST,
max(tmp1.CURR_FORECAST_BRDN_COST) CURR_FORECAST_BRDN_COST,
max(tmp1.CURR_FORECAST_LABOR_HRS) CURR_FORECAST_LABOR_HRS
from
(
select /*+ ordered
full(vers) use_hash(vers) swap_join_inputs(vers)
full(tmp) use_hash(tmp) parallel(tmp) */
tmp.worker_id,
tmp.project_id,
tmp.project_org_id,
vers.project_organization_id,
vers.project_type_class,
tmp.calendar_type_code,
decode(tmp.currency_type,
'G', 1,
'2', 2,
'F', 4) curr_record_type_id,
decode(tmp.currency_type,
'G', l_g1_currency_code,
'2', l_g2_currency_code,
'F', tmp.pf_currency_code) currency_code,
vers.time_phased_type_code,
tmp.period_id,
tmp.period_name,
tmp.start_date,
tmp.end_date,
sum(decode(tmp.plan_type_code,
g_revenue_budget_type_code,
decode(vers.current_flag,
'Y', tmp.revenue,
to_number(null)),
g_rev_fp_type_code,
decode(vers.current_flag,
'Y', tmp.revenue,
to_number(null)),
to_number(null))) curr_bgt_revenue,
sum(decode(tmp.plan_type_code,
g_cost_budget_type_code,
decode(vers.current_flag,
'Y', tmp.raw_cost,
to_number(null)),
g_cost_fp_type_code,
decode(vers.current_flag,
'Y', tmp.raw_cost,
to_number(null)),
to_number(null))) curr_bgt_raw_cost,
sum(decode(tmp.plan_type_code,
g_cost_budget_type_code,
decode(vers.current_flag,
'Y', tmp.burdened_cost,
to_number(null)),
g_cost_fp_type_code,
decode(vers.current_flag,
'Y', tmp.burdened_cost,
to_number(null)),
to_number(null))) curr_bgt_brdn_cost,
sum(decode(tmp.plan_type_code,
g_cost_budget_type_code,
decode(vers.current_flag,
'Y', tmp.labor_hrs,
to_number(null)),
g_cost_fp_type_code,
decode(vers.current_flag,
'Y', tmp.labor_hrs,
to_number(null)),
to_number(null))) curr_bgt_labor_hrs,
sum(decode(tmp.plan_type_code,
g_revenue_budget_type_code,
decode(vers.current_original_flag,
'Y', tmp.revenue,
to_number(null)),
g_rev_fp_type_code,
decode(vers.current_original_flag,
'Y', tmp.revenue,
to_number(null)),
to_number(null))) curr_orig_bgt_revenue,
sum(decode(tmp.plan_type_code,
g_cost_budget_type_code,
decode(vers.current_original_flag,
'Y', tmp.raw_cost,
to_number(null)),
g_cost_fp_type_code,
decode(vers.current_original_flag,
'Y', tmp.raw_cost,
to_number(null)),
to_number(null))) curr_orig_bgt_raw_cost,
sum(decode(tmp.plan_type_code,
g_cost_budget_type_code,
decode(vers.current_original_flag,
'Y', tmp.burdened_cost,
to_number(null)),
g_cost_fp_type_code,
decode(vers.current_original_flag,
'Y', tmp.burdened_cost,
to_number(null)),
to_number(null))) curr_orig_bgt_brdn_cost,
sum(decode(tmp.plan_type_code,
g_cost_budget_type_code,
decode(vers.current_original_flag,
'Y', tmp.labor_hrs,
to_number(null)),
g_cost_fp_type_code,
decode(vers.current_original_flag,
'Y', tmp.labor_hrs,
to_number(null)),
to_number(null))) curr_orig_bgt_labor_hrs,
sum(decode(tmp.plan_type_code,
g_revenue_forecast_type_code,
decode(vers.current_flag,
'Y', tmp.revenue,
to_number(null)),
g_rev_forecast_fp_type_code,
decode(vers.current_flag,
'Y', tmp.revenue,
to_number(null)),
to_number(null))) curr_forecast_revenue,
sum(decode(tmp.plan_type_code,
g_cost_forecast_type_code,
decode(vers.current_flag,
'Y', tmp.raw_cost,
to_number(null)),
g_cost_forecast_fp_type_code,
decode(vers.current_flag,
'Y', tmp.raw_cost,
to_number(null)),
to_number(null))) curr_forecast_raw_cost,
sum(decode(tmp.plan_type_code,
g_cost_forecast_type_code,
decode(vers.current_flag,
'Y', tmp.burdened_cost,
to_number(null)),
g_cost_forecast_fp_type_code,
decode(vers.current_flag,
'Y', tmp.burdened_cost,
to_number(null)),
to_number(null))) curr_forecast_brdn_cost,
sum(decode(tmp.plan_type_code,
g_cost_forecast_type_code,
decode(vers.current_flag,
'Y', tmp.labor_hrs,
to_number(null)),
g_cost_forecast_fp_type_code,
decode(vers.current_flag,
'Y', tmp.labor_hrs,
to_number(null)),
to_number(null))) curr_forecast_labor_hrs
from
PJI_FM_EXTR_PLNVER1 vers,
PJI_FM_EXTR_PLAN tmp,
PJI_FM_EXTR_PLN_LOG log
where
tmp.WORKER_ID = p_worker_id and
tmp.LINE_TYPE <> 'F1' and
vers.WORKER_ID = p_worker_id and
tmp.project_id = vers.project_id and
tmp.version_id = vers.version_id and
decode(nvl(g_gl_period_flag, 'N'),
'Y', 'ZZ', 'GL') <> tmp.calendar_type_code and
decode(nvl(g_pa_period_flag, 'N'),
'Y', 'ZZ', 'PA') <> tmp.calendar_type_code and
tmp.version_id = log.budget_version_id (+) and
log.budget_version_id is null
group by
tmp.worker_id,
tmp.project_id,
tmp.project_org_id,
vers.project_organization_id,
vers.project_type_class,
tmp.calendar_type_code,
decode(tmp.currency_type,
'G', 1,
'2', 2,
'F', 4),
decode(tmp.currency_type,
'G', l_g1_currency_code,
'2', l_g2_currency_code,
'F', tmp.pf_currency_code),
vers.time_phased_type_code,
tmp.period_id,
tmp.period_name,
tmp.start_date,
tmp.end_date
) tmp1
group by
tmp1.WORKER_ID,
tmp1.PROJECT_ID,
tmp1.PROJECT_ORG_ID,
tmp1.PROJECT_ORGANIZATION_ID,
tmp1.PROJECT_TYPE_CLASS,
tmp1.CALENDAR_TYPE_CODE,
nvl(tmp1.CURRENCY_CODE, 'PJI$NULL'),
tmp1.TIME_PHASED_TYPE_CODE,
tmp1.PERIOD_ID,
tmp1.PERIOD_NAME,
tmp1.START_DATE,
tmp1.END_DATE;
procedure extract_updated_versions(p_worker_id number) is
l_process varchar2(30);
(l_process,'PJI_FM_PLAN_EXTR.EXTRACT_UPDATED_VERSIONS(p_worker_id);')) then
Insert /*+ APPEND */ into PJI_FM_EXTR_PLNVER2
(
WORKER_ID,
BATCH_MAP_ROWID,
PROJECT_ID,
COST_BUDGET_C_VERSION,
COST_BUDGET_CO_VERSION,
REVENUE_BUDGET_C_VERSION,
REVENUE_BUDGET_CO_VERSION,
COST_FORECAST_C_VERSION,
REVENUE_FORECAST_C_VERSION,
BATCH_ID
)
select p_worker_id
, tmp.batch_map_rowid
, tmp.project_id
, tmp.COST_BUDGET_C_VERSION
, tmp.COST_BUDGET_CO_VERSION
, tmp.REVENUE_BUDGET_C_VERSION
, tmp.REVENUE_BUDGET_CO_VERSION
, tmp.COST_FORECAST_C_VERSION
, tmp.REVENUE_FORECAST_C_VERSION
, ceil(ROWNUM / PJI_RM_SUM_MAIN.g_commit_threshold)
from
(
SELECT tmp.BATCH_MAP_ROWID BATCH_MAP_ROWID
, tmp.PROJECT_ID PROJECT_ID
, max(tmp.COST_BUDGET_C_VERSION) COST_BUDGET_C_VERSION
, max(tmp.COST_BUDGET_CO_VERSION) COST_BUDGET_CO_VERSION
, max(tmp.REVENUE_BUDGET_C_VERSION) REVENUE_BUDGET_C_VERSION
, max(tmp.REVENUE_BUDGET_CO_VERSION) REVENUE_BUDGET_CO_VERSION
, max(tmp.COST_FORECAST_C_VERSION) COST_FORECAST_C_VERSION
, max(tmp.REVENUE_FORECAST_C_VERSION) REVENUE_FORECAST_C_VERSION
FROM
(
select /*+ ORDERED
full(bvs) use_hash(bvs) parallel(bvs) swap_join_inputs(bvs)
full(tmp) use_hash(tmp) parallel(tmp)
*/
map.rowid batch_map_rowid
, tmp.project_id PROJECT_ID
, case
when (tmp.PLAN_TYPE_CODE = g_cost_budget_type_code or
tmp.PLAN_TYPE_CODE = g_cost_fp_type_code)
and bvs.CURRENT_FLAG = 'Y'
then bvs.VERSION_ID
else 0
end COST_BUDGET_C_VERSION
, case
when (tmp.PLAN_TYPE_CODE = g_cost_budget_type_code or
tmp.PLAN_TYPE_CODE = g_cost_fp_type_code)
and bvs.CURRENT_ORIGINAL_FLAG = 'Y'
then bvs.VERSION_ID
else 0
end COST_BUDGET_CO_VERSION
, case
when (tmp.PLAN_TYPE_CODE = g_revenue_budget_type_code or
tmp.PLAN_TYPE_CODE = g_rev_fp_type_code)
and bvs.CURRENT_FLAG = 'Y'
then bvs.VERSION_ID
else 0
end REVENUE_BUDGET_C_VERSION
, case
when (tmp.PLAN_TYPE_CODE = g_revenue_budget_type_code or
tmp.PLAN_TYPE_CODE = g_rev_fp_type_code)
and bvs.CURRENT_ORIGINAL_FLAG = 'Y'
then bvs.VERSION_ID
else 0
end REVENUE_BUDGET_CO_VERSION
, case
when (tmp.PLAN_TYPE_CODE = g_cost_forecast_type_code or
tmp.PLAN_TYPE_CODE = g_cost_forecast_fp_type_code)
and bvs.CURRENT_FLAG = 'Y'
then bvs.VERSION_ID
else 0
end COST_FORECAST_C_VERSION
, case
when (tmp.PLAN_TYPE_CODE =g_rev_forecast_fp_type_code or
tmp.PLAN_TYPE_CODE = g_revenue_forecast_type_code)
and bvs.CURRENT_FLAG = 'Y'
then bvs.VERSION_ID
else 0
end REVENUE_FORECAST_C_VERSION
from
PJI_PJI_PROJ_BATCH_MAP map,
PJI_FM_EXTR_PLNVER1 bvs,
PJI_FM_EXTR_PLAN tmp,
PJI_FM_EXTR_PLN_LOG log
where
map.worker_id = p_worker_id
and map.project_id = bvs.project_id
and bvs.worker_id = p_worker_id
and tmp.project_id = bvs.project_id
and tmp.version_id = bvs.version_id
and tmp.worker_id = bvs.worker_id
and tmp.calendar_type_code = 'ENTW'
and tmp.LINE_TYPE = 'CG'
and tmp.currency_type = 'G'
and tmp.version_id = log.budget_version_id (+)
and log.budget_version_id is null
) tmp
GROUP BY
tmp.PROJECT_ID
, tmp.BATCH_MAP_ROWID
) tmp
;
(l_process, 'PJI_FM_PLAN_EXTR.EXTRACT_UPDATED_VERSIONS(p_worker_id);');
end extract_updated_versions;
procedure update_batch_versions_pre(p_worker_id number) is
l_process varchar2(30);
'PJI_FM_PLAN_EXTR.UPDATE_BATCH_VERSIONS_PRE(p_worker_id);')) then
insert /*+ append */ into PJI_HELPER_BATCH_MAP
(
BATCH_ID,
WORKER_ID,
STATUS
)
select
distinct
BATCH_ID,
null,
null
from
PJI_FM_EXTR_PLNVER2
where
WORKER_ID = p_worker_id;
'PJI_FM_PLAN_EXTR.UPDATE_BATCH_VERSIONS_PRE(p_worker_id);');
end update_batch_versions_pre;
procedure update_batch_versions(p_worker_id number) is
l_process varchar2(30);
'PJI_FM_PLAN_EXTR.UPDATE_BATCH_VERSIONS(p_worker_id);')) then
select count(*)
into l_leftover_batches
from PJI_HELPER_BATCH_MAP
where WORKER_ID = p_worker_id and
STATUS = 'P';
select BATCH_ID
into l_helper_batch_id
from PJI_HELPER_BATCH_MAP
where WORKER_ID = p_worker_id and
STATUS = 'P' and
ROWNUM = 1;
update PJI_HELPER_BATCH_MAP
set WORKER_ID = p_worker_id,
STATUS = 'P'
where WORKER_ID is null and
ROWNUM = 1
returning BATCH_ID
into l_helper_batch_id;
update pji_pji_proj_batch_map map
set (map.COST_BUDGET_N_VERSION,
map.COST_BUDGET_NO_VERSION,
map.REVENUE_BUDGET_N_VERSION,
map.REVENUE_BUDGET_NO_VERSION,
map.COST_FORECAST_N_VERSION,
map.REVENUE_FORECAST_N_VERSION) =
(select
decode(sign(vrs.COST_BUDGET_C_VERSION)
, 0, decode(map.COST_BUDGET_C_VERSION,
-1, -2, map.COST_BUDGET_C_VERSION)
, vrs.COST_BUDGET_C_VERSION
) COST_BUDGET_C_VERSION
, decode(sign(vrs.COST_BUDGET_CO_VERSION)
, 0, decode(map.COST_BUDGET_CO_VERSION,
-1, -2, map.COST_BUDGET_CO_VERSION)
, vrs.COST_BUDGET_CO_VERSION
) COST_BUDGET_CO_VERSION
, decode(sign(vrs.REVENUE_BUDGET_C_VERSION)
, 0, decode(map.REVENUE_BUDGET_C_VERSION,
-1, -2, map.REVENUE_BUDGET_C_VERSION)
, vrs.REVENUE_BUDGET_C_VERSION
) REVENUE_BUDGET_C_VERSION
, decode(sign(vrs.REVENUE_BUDGET_CO_VERSION)
, 0, decode(map.REVENUE_BUDGET_CO_VERSION,
-1, -2, map.REVENUE_BUDGET_CO_VERSION)
, vrs.REVENUE_BUDGET_CO_VERSION
) REVENUE_BUDGET_CO_VERSION
, decode(sign(vrs.COST_FORECAST_C_VERSION)
, 0, decode(map.COST_FORECAST_C_VERSION,
-1, -2, map.COST_FORECAST_C_VERSION)
, vrs.COST_FORECAST_C_VERSION
) COST_FORECAST_C_VERSION
, decode(sign(vrs.REVENUE_FORECAST_C_VERSION)
, 0, decode(map.REVENUE_FORECAST_C_VERSION,
-1, -2, map.REVENUE_FORECAST_C_VERSION)
, vrs.REVENUE_FORECAST_C_VERSION
) REVENUE_FORECAST_C_VERSION
from PJI_FM_EXTR_PLNVER2 vrs
where vrs.batch_map_rowid = map.rowid
)
where map.project_id in (select project_id
from PJI_FM_EXTR_PLNVER2
where WORKER_ID = 1 and
BATCH_ID = l_helper_batch_id)
and map.worker_id = 1;
update PJI_HELPER_BATCH_MAP
set STATUS = 'C'
where WORKER_ID = p_worker_id and
BATCH_ID = l_helper_batch_id;
select count(*)
into l_row_count
from PJI_HELPER_BATCH_MAP
where nvl(STATUS, 'X') <> 'C';
update PJI_SYSTEM_PRC_STATUS
set STEP_STATUS = 'C'
where PROCESS_NAME like PJI_RM_SUM_MAIN.g_process|| to_char(x) and
STEP_NAME =
'PJI_FM_PLAN_EXTR.UPDATE_BATCH_VERSIONS(p_worker_id);' and
'PJI_FM_PLAN_EXTR.UPDATE_BATCH_VERSIONS(p_worker_id);');
end update_batch_versions;
procedure update_batch_versions_post(p_worker_id number) is
l_process varchar2(30);
'PJI_FM_PLAN_EXTR.UPDATE_BATCH_VERSIONS_POST(p_worker_id);')) then
'PJI_FM_PLAN_EXTR.UPDATE_BATCH_VERSIONS_POST(p_worker_id);');
end update_batch_versions_post;
procedure UPDATE_BATCH_STATUSES (p_worker_id in number) is
l_process varchar2(30);
'PJI_FM_PLAN_EXTR.UPDATE_BATCH_STATUSES(p_worker_id);'
update /*+ index(status, PJI_PJI_PROJ_EXTR_STATUS_U1) */
PJI_PJI_PROJ_EXTR_STATUS status
set (CLOSED_DATE,
PROJECT_ORGANIZATION_ID,
COST_BUDGET_C_VERSION,
COST_BUDGET_CO_VERSION,
REVENUE_BUDGET_C_VERSION,
REVENUE_BUDGET_CO_VERSION,
COST_FORECAST_C_VERSION,
REVENUE_FORECAST_C_VERSION) =
(select /*+ index(map, PJI_PJI_PROJ_BATCH_MAP_N1) */
map.NEW_CLOSED_DATE,
map.NEW_PROJECT_ORGANIZATION_ID,
nvl(map.COST_BUDGET_N_VERSION,status.COST_BUDGET_C_VERSION),
nvl(map.COST_BUDGET_NO_VERSION,status.COST_BUDGET_CO_VERSION),
nvl(map.REVENUE_BUDGET_N_VERSION,status.REVENUE_BUDGET_C_VERSION),
nvl(map.REVENUE_BUDGET_NO_VERSION,status.REVENUE_BUDGET_CO_VERSION),
nvl(map.COST_FORECAST_N_VERSION,status.COST_FORECAST_C_VERSION),
nvl(map.REVENUE_FORECAST_N_VERSION,status.REVENUE_FORECAST_C_VERSION)
from PJI_PJI_PROJ_BATCH_MAP map
where map.WORKER_ID = p_worker_id and
map.PROJECT_ID = status.PROJECT_ID)
where PROJECT_ID in (select /*+ index(map, PJI_PJI_PROJ_BATCH_MAP_N1) */
PROJECT_ID
from PJI_PJI_PROJ_BATCH_MAP
where WORKER_ID = p_worker_id);
'PJI_FM_PLAN_EXTR.UPDATE_BATCH_STATUSES(p_worker_id);'
end UPDATE_BATCH_STATUSES;
select ent_period_id,name,start_date,end_date
into g_ent_start_period_id,g_ent_start_period_name,g_ent_start_date,g_ent_end_date
from fii_time_ent_period
where g_global_start_date between start_date AND end_date
;
select week_id,PJI_RM_SUM_MAIN.g_null,start_date,end_date
into g_entw_start_period_id,g_entw_start_period_name,g_entw_start_date,g_entw_end_date
from fii_time_week
where g_global_start_date between start_date AND end_date
;