[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT DISTINCT
excp.object_id project_id
, excp.calendar_type
, excp.currency_type
BULK COLLECT INTO
l_project_ids_tbl
, l_calendar_type_tbl
, l_currency_type_tbl
FROM
pa_perf_temp_obj_measure excp
WHERE 1=1
AND excp.object_type = 'PA_PROJECTS';
SELECT
ROWIDTOCHAR(excp.ROWID)
, excp.measure_id
, msrs.measure_set_code
, msrs.xtd_type
, NULL
BULK COLLECT INTO
l_rowids_tbl
, l_measure_ids_tbl
, l_measure_set_codes_tbl
, l_xtd_types_tbl
, l_measure_values_tbl --will contain the values for the requested measures
FROM
pa_perf_temp_obj_measure excp
, pji_mt_measures_b msrs
WHERE 1=1
AND excp.object_type = 'PA_PROJECTS'
AND msrs.measure_id = excp.measure_id
AND excp.object_id = l_project_ids_tbl(i)
AND excp.calendar_type = l_calendar_type_tbl(i)
AND excp.currency_type = l_currency_type_tbl(i)
ORDER BY msrs.measure_set_code;
SELECT projfunc_currency_code
INTO l_Currency_Code
FROM pa_projects_all
WHERE project_id = l_project_ids_tbl(i);
SELECT project_currency_code
INTO l_Currency_Code
FROM pa_projects_all
WHERE project_id = l_project_ids_tbl(i);
UPDATE pa_perf_temp_obj_measure
SET measure_value = l_measure_values_tbl(j)
, period_name = l_period_name
WHERE ROWID = CHARTOROWID(l_rowids_tbl(j));
SELECT NAME INTO l_measure_label
FROM pji_mt_measures_vl
WHERE measure_code = p_measure_code;
SELECT NAME INTO l_measure_labels_tbl(i)
FROM pji_mt_measures_vl
WHERE measure_code = p_measure_codes_tbl(i);
SELECT
measure_set_code
, xtd_type
, measure_id
INTO
x_measure_set_codes_tbl(i)
,x_timeslices_tbl(i)
,x_measure_id_tbl(i)
FROM PJI_MT_MEASURES_B
WHERE measure_code = p_measure_codes_tbl(i);
SELECT
measure_set_code
, xtd_type
, measure_id
INTO
l_measure_set_codes_tbl(i)
,l_timeslices_tbl(i)
,l_measure_id_tbl(i)
FROM PJI_MT_MEASURES_B
WHERE measure_code = p_measure_codes_tbl(i);
SELECT
pbv.fin_plan_type_id curr_plan_type
,orig_pbv.budget_version_id orig_plan_version
INTO
xCstForecastPlanTypeId
,xOrigCstForecastVersionId
FROM
pa_budget_versions pbv
, pa_budget_versions orig_pbv
, pa_fin_plan_types_b orig_pfptb
-- , pa_fin_plan_types_tl orig_pfptt
WHERE 1=1
AND pbv.budget_version_id = pCstForecastVersionId
AND pbv.fin_plan_type_id = orig_pbv.fin_plan_type_id
AND orig_pbv.project_id = p_project_id
AND orig_pbv.budget_status_code = 'B'
AND orig_pbv.current_original_flag = 'Y'
AND orig_pbv.version_type IN ('COST','ALL')
AND orig_pbv.fin_plan_type_id = orig_pfptb.fin_plan_type_id
-- AND orig_pfptb.fin_plan_type_id = orig_pfptt.fin_plan_type_id
AND orig_pfptb.plan_class_code IN ('FORECAST')
-- AND orig_pfptt.LANGUAGE = USERENV('LANG')
;
SELECT
pbv.fin_plan_type_id curr_plan_type
,orig_pbv.budget_version_id orig_plan_version
INTO
xCstBudgetPlanTypeId
,xOrigCstBudgetVersionId
FROM
pa_budget_versions pbv
, pa_budget_versions orig_pbv
, pa_fin_plan_types_b orig_pfptb
--, pa_fin_plan_types_tl orig_pfptt
WHERE 1=1
AND pbv.budget_version_id = pCstBudgetVersionId
AND pbv.fin_plan_type_id = orig_pbv.fin_plan_type_id
AND orig_pbv.project_id = p_project_id
AND orig_pbv.budget_status_code = 'B'
AND orig_pbv.current_original_flag = 'Y'
AND orig_pbv.version_type IN ('COST','ALL')
AND orig_pbv.fin_plan_type_id = orig_pfptb.fin_plan_type_id
-- AND orig_pfptb.fin_plan_type_id = orig_pfptt.fin_plan_type_id
AND orig_pfptb.plan_class_code IN ('BUDGET')
--AND orig_pfptt.LANGUAGE = USERENV('LANG')
;
SELECT
pbv.fin_plan_type_id curr_plan_type
,orig_pbv.budget_version_id orig_plan_version
INTO
xCstBudget2PlanTypeId
,xOrigCstBudget2VersionId
FROM
pa_budget_versions pbv
, pa_budget_versions orig_pbv
, pa_fin_plan_types_b orig_pfptb
--, pa_fin_plan_types_tl orig_pfptt
WHERE 1=1
AND pbv.budget_version_id = pCstBudget2VersionId
AND pbv.fin_plan_type_id = orig_pbv.fin_plan_type_id
AND orig_pbv.project_id = p_project_id
AND orig_pbv.budget_status_code = 'B'
AND orig_pbv.current_original_flag = 'Y'
AND orig_pbv.version_type IN ('COST','ALL')
AND orig_pbv.fin_plan_type_id = orig_pfptb.fin_plan_type_id
-- AND orig_pfptb.fin_plan_type_id = orig_pfptt.fin_plan_type_id
AND orig_pfptb.plan_class_code IN ('BUDGET')
--AND orig_pfptt.LANGUAGE = USERENV('LANG')
;
SELECT
pbv.fin_plan_type_id curr_plan_type
,orig_pbv.budget_version_id orig_plan_version
INTO
xRevForecastPlanTypeId
,xOrigRevForecastVersionId
FROM
pa_budget_versions pbv
, pa_budget_versions orig_pbv
, pa_fin_plan_types_b orig_pfptb
-- , pa_fin_plan_types_tl orig_pfptt
WHERE 1=1
AND pbv.budget_version_id = pRevForecastVersionId
AND pbv.fin_plan_type_id = orig_pbv.fin_plan_type_id
AND orig_pbv.project_id = p_project_id
AND orig_pbv.budget_status_code = 'B'
AND orig_pbv.current_original_flag = 'Y'
AND orig_pbv.version_type IN ('REVENUE','ALL')
AND orig_pbv.fin_plan_type_id = orig_pfptb.fin_plan_type_id
-- AND orig_pfptb.fin_plan_type_id = orig_pfptt.fin_plan_type_id
AND orig_pfptb.plan_class_code IN ('FORECAST')
-- AND orig_pfptt.LANGUAGE = USERENV('LANG')
;
SELECT
pbv.fin_plan_type_id curr_plan_type
,orig_pbv.budget_version_id orig_plan_version
INTO
xRevBudgetPlanTypeId
,xOrigRevBudgetVersionId
FROM
pa_budget_versions pbv
, pa_budget_versions orig_pbv
, pa_fin_plan_types_b orig_pfptb
--, pa_fin_plan_types_tl orig_pfptt
WHERE 1=1
AND pbv.budget_version_id = pRevBudgetVersionId
AND pbv.fin_plan_type_id = orig_pbv.fin_plan_type_id
AND orig_pbv.project_id = p_project_id
AND orig_pbv.budget_status_code = 'B'
AND orig_pbv.current_original_flag = 'Y'
AND orig_pbv.version_type IN ('REVENUE','ALL')
AND orig_pbv.fin_plan_type_id = orig_pfptb.fin_plan_type_id
-- AND orig_pfptb.fin_plan_type_id = orig_pfptt.fin_plan_type_id
AND orig_pfptb.plan_class_code IN ('BUDGET')
-- AND orig_pfptt.LANGUAGE = USERENV('LANG')
;
SELECT
pbv.fin_plan_type_id curr_plan_type
,orig_pbv.budget_version_id orig_plan_version
INTO
xRevBudget2PlanTypeId
,xOrigRevBudget2VersionId
FROM
pa_budget_versions pbv
, pa_budget_versions orig_pbv
, pa_fin_plan_types_b orig_pfptb
-- , pa_fin_plan_types_tl orig_pfptt
WHERE 1=1
AND pbv.budget_version_id = pRevBudget2VersionId
AND pbv.fin_plan_type_id = orig_pbv.fin_plan_type_id
AND orig_pbv.project_id = p_project_id
AND orig_pbv.budget_status_code = 'B'
AND orig_pbv.current_original_flag = 'Y'
AND orig_pbv.version_type IN ('REVENUE','ALL')
AND orig_pbv.fin_plan_type_id = orig_pfptb.fin_plan_type_id
-- AND orig_pfptb.fin_plan_type_id = orig_pfptt.fin_plan_type_id
AND orig_pfptb.plan_class_code IN ('BUDGET')
-- AND orig_pfptt.LANGUAGE = USERENV('LANG')
;
-- Delete the existing records in temporary table.
--
DELETE PJI_REP_XTD_MEASURES_TMP;
SELECT measure_set_code
, NULL -- calculated later in the code -- measure_format
, name
, NULL -- calculated later in the code
, NULL -- calculated later in the code
, NULL -- calculated later in the code
, NULL -- calculated later in the code
, NULL -- calculated later in the code
, NULL -- calculated later in the code
BULK COLLECT INTO x_measure_set_code
, x_measure_type
, x_measure_name
, x_ptd_value
, x_qtd_value
, x_ytd_value
, x_itd_value
, x_ac_value
, x_prp_value
FROM pji_mt_measure_sets_vl
WHERE 1=1
AND measure_set_type IN ('SEEDED', 'CUSTOM_STORED', 'CUSTOM_CALC')
AND measure_set_code LIKE 'PPF%'
ORDER BY measure_set_code;
-- Bulk Insert the amounts into the temporary table.
--
FORALL i IN 1..x_measure_set_code.LAST
INSERT INTO PJI_REP_XTD_MEASURES_TMP
(
MEASURE_SET_CODE, MEASURE_TYPE, MEASURE_LABEL,
PTD_VALUE, QTD_VALUE, YTD_VALUE, ITD_VALUE, AC_VALUE, PRP_VALUE,
PTD_HTML, QTD_HTML, YTD_HTML, ITD_HTML, AC_HTML, PRP_HTML,
PTD_TRANS_ID, QTD_TRANS_ID, YTD_TRANS_ID, ITD_TRANS_ID, AC_TRANS_ID, PRP_TRANS_ID,
PTD_MEANING,QTD_MEANING,YTD_MEANING,ITD_MEANING,AC_MEANING,PRP_MEANING
)
VALUES (
x_measure_set_code(i), x_measure_type(i), x_measure_name(i),
x_ptd_value(i), x_qtd_value(i), x_ytd_value(i), x_itd_value(i), x_ac_value(i), x_prp_value(i),
x_ptd_html(i), x_qtd_html(i), x_ytd_html(i), x_itd_html(i), x_ac_html(i), x_prp_html(i),
x_ptd_trans_id(i), x_qtd_trans_id(i), x_ytd_trans_id(i), x_itd_trans_id(i), x_ac_trans_id(i), x_prp_trans_id(i),
x_ptd_meaning(i), x_qtd_meaning(i), x_ytd_meaning(i), x_itd_meaning(i), x_ac_meaning(i), x_prp_meaning(i)
);
SELECT pia.fte_day
INTO l_hours_per_day
FROM pa_implementations_all pia, pa_projects_all ppa WHERE pia.org_id = ppa.org_id and ppa.project_id = pProjectId;
SELECT DISTINCT UPPER(t.project_type_class_code)
INTO l_project_type_class
FROM pa_projects_all p
, pa_project_types_all t
WHERE 1=1
AND p.project_id = pProjectId
AND p.project_type = t.project_type
AND p.org_id = t.org_id;
SELECT start_date, billing_cycle_id,
billing_offset
INTO l_start_date, l_billing_cycle_id, l_billing_offset
FROM pa_projects_all
WHERE project_id = pProjectId;
SELECT
pji_rep_overview_type(
plan_version_id
,plan_type_id
,SUM(ptd_mask * raw_cost)
,SUM(ptd_mask * brdn_cost)
,SUM(ptd_mask * revenue * l_contract_proj_mask)
,SUM(ptd_mask * bill_raw_cost * l_contract_proj_mask)
,SUM(ptd_mask * bill_brdn_cost * l_contract_proj_mask)
,SUM(ptd_mask * billable_equipment_hours * l_contract_proj_mask)
,SUM(ptd_mask * bill_labor_raw_cost * l_contract_proj_mask)
,SUM(ptd_mask * bill_labor_brdn_cost * l_contract_proj_mask)
,SUM(ptd_mask * bill_labor_hrs * l_contract_proj_mask)
,SUM(ptd_mask * capitalizable_raw_cost * l_capital_proj_mask)
,SUM(ptd_mask * capitalizable_brdn_cost * l_capital_proj_mask)
,SUM(ptd_mask * equipment_hours)
,SUM(ptd_mask * equipment_raw_cost)
,SUM(ptd_mask * equipment_brdn_cost)
,SUM(ptd_mask * labor_raw_cost)
,SUM(ptd_mask * labor_brdn_cost)
,SUM(ptd_mask * labor_hrs)
,SUM(ptd_mask * labor_revenue * l_contract_proj_mask)
,SUM(ptd_mask * 0) --unbilled_cost
,SUM(ptd_mask * 0) --capitalized_cost
,SUM(itd_mask * sup_inv_committed_cost)
,SUM(itd_mask * po_committed_cost)
,SUM(itd_mask * pr_committed_cost)
,SUM(itd_mask * oth_committed_cost)
,SUM(qtd_mask * raw_cost)
,SUM(qtd_mask * brdn_cost)
,SUM(qtd_mask * revenue * l_contract_proj_mask)
,SUM(qtd_mask * bill_raw_cost * l_contract_proj_mask)
,SUM(qtd_mask * bill_brdn_cost * l_contract_proj_mask)
,SUM(qtd_mask * billable_equipment_hours * l_contract_proj_mask)
,SUM(qtd_mask * bill_labor_raw_cost * l_contract_proj_mask)
,SUM(qtd_mask * bill_labor_brdn_cost * l_contract_proj_mask)
,SUM(qtd_mask * bill_labor_hrs * l_contract_proj_mask)
,SUM(qtd_mask * capitalizable_raw_cost * l_capital_proj_mask)
,SUM(qtd_mask * capitalizable_brdn_cost * l_capital_proj_mask)
,SUM(qtd_mask * equipment_hours)
,SUM(qtd_mask * equipment_raw_cost)
,SUM(qtd_mask * equipment_brdn_cost)
,SUM(qtd_mask * labor_raw_cost)
,SUM(qtd_mask * labor_brdn_cost)
,SUM(qtd_mask * labor_hrs)
,SUM(qtd_mask * labor_revenue * l_contract_proj_mask)
,SUM(qtd_mask * 0) --unbilled_cost
,SUM(qtd_mask * 0) --capitalized_cost
,SUM(itd_mask * sup_inv_committed_cost) -- added for 6864037
,SUM(itd_mask * po_committed_cost) -- added for 6864037
,SUM(itd_mask * pr_committed_cost) -- added for 6864037
,SUM(itd_mask * oth_committed_cost) -- added for 6864037
,SUM(ytd_mask * raw_cost)
,SUM(ytd_mask * brdn_cost)
,SUM(ytd_mask * revenue * l_contract_proj_mask)
,SUM(ytd_mask * bill_raw_cost * l_contract_proj_mask)
,SUM(ytd_mask * bill_brdn_cost * l_contract_proj_mask)
,SUM(ytd_mask * billable_equipment_hours * l_contract_proj_mask)
,SUM(ytd_mask * bill_labor_raw_cost * l_contract_proj_mask)
,SUM(ytd_mask * bill_labor_brdn_cost * l_contract_proj_mask)
,SUM(ytd_mask * bill_labor_hrs * l_contract_proj_mask)
,SUM(ytd_mask * capitalizable_raw_cost * l_capital_proj_mask)
,SUM(ytd_mask * capitalizable_brdn_cost * l_capital_proj_mask)
,SUM(ytd_mask * equipment_hours)
,SUM(ytd_mask * equipment_raw_cost)
,SUM(ytd_mask * equipment_brdn_cost)
,SUM(ytd_mask * labor_raw_cost)
,SUM(ytd_mask * labor_brdn_cost)
,SUM(ytd_mask * labor_hrs)
,SUM(ytd_mask * labor_revenue * l_contract_proj_mask)
,SUM(ytd_mask * 0) --unbilled_cost
,SUM(ytd_mask * 0) --capitalized_cost
,SUM(itd_mask * sup_inv_committed_cost) -- added for 6864037
,SUM(itd_mask * po_committed_cost) -- added for 6864037
,SUM(itd_mask * pr_committed_cost) -- added for 6864037
,SUM(itd_mask * oth_committed_cost) -- added for 6864037
,SUM(itd_mask * raw_cost)
,SUM(itd_mask * brdn_cost)
,SUM(itd_mask * revenue * l_contract_proj_mask)
,SUM(itd_mask * bill_raw_cost * l_contract_proj_mask)
,SUM(itd_mask * bill_brdn_cost * l_contract_proj_mask)
,SUM(itd_mask * billable_equipment_hours * l_contract_proj_mask)
,SUM(itd_mask * bill_labor_raw_cost * l_contract_proj_mask)
,SUM(itd_mask * bill_labor_brdn_cost * l_contract_proj_mask)
,SUM(itd_mask * bill_labor_hrs * l_contract_proj_mask)
,SUM(itd_mask * capitalizable_raw_cost * l_capital_proj_mask)
,SUM(itd_mask * capitalizable_brdn_cost * l_capital_proj_mask)
,SUM(itd_mask * equipment_hours)
,SUM(itd_mask * equipment_raw_cost)
,SUM(itd_mask * equipment_brdn_cost)
,SUM(itd_mask * labor_raw_cost)
,SUM(itd_mask * labor_brdn_cost)
,SUM(itd_mask * labor_hrs)
,SUM(itd_mask * labor_revenue * l_contract_proj_mask)
,SUM(itd_mask * 0) --unbilled_cost
,SUM(itd_mask * 0) --capitalized_cost
,SUM(ac_mask * sup_inv_committed_cost)
,SUM(ac_mask * po_committed_cost)
,SUM(ac_mask * pr_committed_cost)
,SUM(ac_mask * oth_committed_cost)
,SUM(ac_mask * raw_cost)
,SUM(ac_mask * brdn_cost)
,SUM(ac_mask * revenue * l_contract_proj_mask)
,SUM(ac_mask * bill_raw_cost * l_contract_proj_mask)
,SUM(ac_mask * bill_brdn_cost * l_contract_proj_mask)
,SUM(ac_mask * billable_equipment_hours * l_contract_proj_mask)
,SUM(ac_mask * bill_labor_raw_cost * l_contract_proj_mask)
,SUM(ac_mask * bill_labor_brdn_cost * l_contract_proj_mask)
,SUM(ac_mask * bill_labor_hrs * l_contract_proj_mask)
,SUM(ac_mask * capitalizable_raw_cost * l_capital_proj_mask)
,SUM(ac_mask * capitalizable_brdn_cost * l_capital_proj_mask)
,SUM(ac_mask * equipment_hours)
,SUM(ac_mask * equipment_raw_cost)
,SUM(ac_mask * equipment_brdn_cost)
,SUM(ac_mask * labor_raw_cost)
,SUM(ac_mask * labor_brdn_cost)
,SUM(ac_mask * labor_hrs)
,SUM(ac_mask * labor_revenue * l_contract_proj_mask)
,SUM(ac_mask * 0) --unbilled_cost
,SUM(ac_mask * 0) --capitalized_cost
,SUM(ac_mask * sup_inv_committed_cost)
,SUM(ac_mask * po_committed_cost)
,SUM(ac_mask * pr_committed_cost)
,SUM(ac_mask * oth_committed_cost)
,SUM(prp_mask * raw_cost)
,SUM(prp_mask * brdn_cost)
,SUM(prp_mask * revenue * l_contract_proj_mask)
,SUM(prp_mask * bill_raw_cost * l_contract_proj_mask)
,SUM(prp_mask * bill_brdn_cost * l_contract_proj_mask)
,SUM(prp_mask * billable_equipment_hours * l_contract_proj_mask)
,SUM(prp_mask * bill_labor_raw_cost * l_contract_proj_mask)
,SUM(prp_mask * bill_labor_brdn_cost * l_contract_proj_mask)
,SUM(prp_mask * bill_labor_hrs * l_contract_proj_mask)
,SUM(prp_mask * capitalizable_raw_cost * l_capital_proj_mask)
,SUM(prp_mask * capitalizable_brdn_cost * l_capital_proj_mask)
,SUM(prp_mask * equipment_hours)
,SUM(prp_mask * equipment_raw_cost)
,SUM(prp_mask * equipment_brdn_cost)
,SUM(prp_mask * labor_raw_cost)
,SUM(prp_mask * labor_brdn_cost)
,SUM(prp_mask * labor_hrs)
,SUM(prp_mask * labor_revenue * l_contract_proj_mask)
,SUM(prp_mask * 0) --unbilled_cost
,SUM(prp_mask * 0) --capitalized_cost
,NULL
,NULL
,NULL
,NULL
,SUM(ptd_mask * fct.custom1)
,SUM(ptd_mask * fct.custom2)
,SUM(ptd_mask * fct.custom3)
,SUM(ptd_mask * fct.custom4)
,SUM(ptd_mask * fct.custom5)
,SUM(ptd_mask * fct.custom6)
,SUM(ptd_mask * fct.custom7)
,SUM(ptd_mask * fct.custom8)
,SUM(ptd_mask * fct.custom9)
,SUM(ptd_mask * fct.custom10)
,SUM(itd_mask * fct.custom11) -- Modified for bug 13713726
,SUM(itd_mask * fct.custom12) -- Modified for bug 13713726
,SUM(itd_mask * fct.custom13) -- Modified for bug 13713726
,SUM(itd_mask * fct.custom14) -- Modified for bug 13713726
,SUM(itd_mask * fct.custom15) -- Modified for bug 13713726
,SUM(ptd_mask * fct.custom16)
,SUM(ptd_mask * fct.custom17)
,SUM(ptd_mask * fct.custom18)
,SUM(ptd_mask * fct.custom19)
,SUM(ptd_mask * fct.custom20)
,SUM(ptd_mask * fct.custom21)
,SUM(ptd_mask * fct.custom22)
,SUM(ptd_mask * fct.custom23)
,SUM(ptd_mask * fct.custom24)
,SUM(ptd_mask * fct.custom25)
,SUM(ptd_mask * fct.custom26)
,SUM(ptd_mask * fct.custom27)
,SUM(ptd_mask * fct.custom28)
,SUM(ptd_mask * fct.custom29)
,SUM(ptd_mask * fct.custom30)
,SUM(qtd_mask * fct.custom1)
,SUM(qtd_mask * fct.custom2)
,SUM(qtd_mask * fct.custom3)
,SUM(qtd_mask * fct.custom4)
,SUM(qtd_mask * fct.custom5)
,SUM(qtd_mask * fct.custom6)
,SUM(qtd_mask * fct.custom7)
,SUM(qtd_mask * fct.custom8)
,SUM(qtd_mask * fct.custom9)
,SUM(qtd_mask * fct.custom10)
,SUM(itd_mask * fct.custom11) -- Modified for bug 13713726
,SUM(itd_mask * fct.custom12) -- Modified for bug 13713726
,SUM(itd_mask * fct.custom13) -- Modified for bug 13713726
,SUM(itd_mask * fct.custom14) -- Modified for bug 13713726
,SUM(itd_mask * fct.custom15) -- Modified for bug 13713726
,SUM(qtd_mask * fct.custom16)
,SUM(qtd_mask * fct.custom17)
,SUM(qtd_mask * fct.custom18)
,SUM(qtd_mask * fct.custom19)
,SUM(qtd_mask * fct.custom20)
,SUM(qtd_mask * fct.custom21)
,SUM(qtd_mask * fct.custom22)
,SUM(qtd_mask * fct.custom23)
,SUM(qtd_mask * fct.custom24)
,SUM(qtd_mask * fct.custom25)
,SUM(qtd_mask * fct.custom26)
,SUM(qtd_mask * fct.custom27)
,SUM(qtd_mask * fct.custom28)
,SUM(qtd_mask * fct.custom29)
,SUM(qtd_mask * fct.custom30)
,SUM(ytd_mask * fct.custom1)
,SUM(ytd_mask * fct.custom2)
,SUM(ytd_mask * fct.custom3)
,SUM(ytd_mask * fct.custom4)
,SUM(ytd_mask * fct.custom5)
,SUM(ytd_mask * fct.custom6)
,SUM(ytd_mask * fct.custom7)
,SUM(ytd_mask * fct.custom8)
,SUM(ytd_mask * fct.custom9)
,SUM(ytd_mask * fct.custom10)
,SUM(itd_mask * fct.custom11) -- Modified for bug 13713726
,SUM(itd_mask * fct.custom12) -- Modified for bug 13713726
,SUM(itd_mask * fct.custom13) -- Modified for bug 13713726
,SUM(itd_mask * fct.custom14) -- Modified for bug 13713726
,SUM(itd_mask * fct.custom15) -- Modified for bug 13713726
,SUM(ytd_mask * fct.custom16)
,SUM(ytd_mask * fct.custom17)
,SUM(ytd_mask * fct.custom18)
,SUM(ytd_mask * fct.custom19)
,SUM(ytd_mask * fct.custom20)
,SUM(ytd_mask * fct.custom21)
,SUM(ytd_mask * fct.custom22)
,SUM(ytd_mask * fct.custom23)
,SUM(ytd_mask * fct.custom24)
,SUM(ytd_mask * fct.custom25)
,SUM(ytd_mask * fct.custom26)
,SUM(ytd_mask * fct.custom27)
,SUM(ytd_mask * fct.custom28)
,SUM(ytd_mask * fct.custom29)
,SUM(ytd_mask * fct.custom30)
,SUM(itd_mask * fct.custom1)
,SUM(itd_mask * fct.custom2)
,SUM(itd_mask * fct.custom3)
,SUM(itd_mask * fct.custom4)
,SUM(itd_mask * fct.custom5)
,SUM(itd_mask * fct.custom6)
,SUM(itd_mask * fct.custom7)
,SUM(itd_mask * fct.custom8)
,SUM(itd_mask * fct.custom9)
,SUM(itd_mask * fct.custom10)
,SUM(ac_mask * fct.custom11) -- Modified for bug 13713726
,SUM(ac_mask * fct.custom12) -- Modified for bug 13713726
,SUM(ac_mask * fct.custom13) -- Modified for bug 13713726
,SUM(ac_mask * fct.custom14) -- Modified for bug 13713726
,SUM(ac_mask * fct.custom15) -- Modified for bug 13713726
,SUM(itd_mask * fct.custom16)
,SUM(itd_mask * fct.custom17)
,SUM(itd_mask * fct.custom18)
,SUM(itd_mask * fct.custom19)
,SUM(itd_mask * fct.custom20)
,SUM(itd_mask * fct.custom21)
,SUM(itd_mask * fct.custom22)
,SUM(itd_mask * fct.custom23)
,SUM(itd_mask * fct.custom24)
,SUM(itd_mask * fct.custom25)
,SUM(itd_mask * fct.custom26)
,SUM(itd_mask * fct.custom27)
,SUM(itd_mask * fct.custom28)
,SUM(itd_mask * fct.custom29)
,SUM(itd_mask * fct.custom30)
,SUM(ac_mask * fct.custom1)
,SUM(ac_mask * fct.custom2)
,SUM(ac_mask * fct.custom3)
,SUM(ac_mask * fct.custom4)
,SUM(ac_mask * fct.custom5)
,SUM(ac_mask * fct.custom6)
,SUM(ac_mask * fct.custom7)
,SUM(ac_mask * fct.custom8)
,SUM(ac_mask * fct.custom9)
,SUM(ac_mask * fct.custom10)
,SUM(ac_mask * fct.custom11)
,SUM(ac_mask * fct.custom12)
,SUM(ac_mask * fct.custom13)
,SUM(ac_mask * fct.custom14)
,SUM(ac_mask * fct.custom15)
,SUM(ac_mask * fct.custom16)
,SUM(ac_mask * fct.custom17)
,SUM(ac_mask * fct.custom18)
,SUM(ac_mask * fct.custom19)
,SUM(ac_mask * fct.custom20)
,SUM(ac_mask * fct.custom21)
,SUM(ac_mask * fct.custom22)
,SUM(ac_mask * fct.custom23)
,SUM(ac_mask * fct.custom24)
,SUM(ac_mask * fct.custom25)
,SUM(ac_mask * fct.custom26)
,SUM(ac_mask * fct.custom27)
,SUM(ac_mask * fct.custom28)
,SUM(ac_mask * fct.custom29)
,SUM(ac_mask * fct.custom30)
,SUM(prp_mask * fct.custom1)
,SUM(prp_mask * fct.custom2)
,SUM(prp_mask * fct.custom3)
,SUM(prp_mask * fct.custom4)
,SUM(prp_mask * fct.custom5)
,SUM(prp_mask * fct.custom6)
,SUM(prp_mask * fct.custom7)
,SUM(prp_mask * fct.custom8)
,SUM(prp_mask * fct.custom9)
,SUM(prp_mask * fct.custom10)
,SUM(prp_mask * fct.custom11)
,SUM(prp_mask * fct.custom12)
,SUM(prp_mask * fct.custom13)
,SUM(prp_mask * fct.custom14)
,SUM(prp_mask * fct.custom15)
,SUM(prp_mask * fct.custom16)
,SUM(prp_mask * fct.custom17)
,SUM(prp_mask * fct.custom18)
,SUM(prp_mask * fct.custom19)
,SUM(prp_mask * fct.custom20)
,SUM(prp_mask * fct.custom21)
,SUM(prp_mask * fct.custom22)
,SUM(prp_mask * fct.custom23)
,SUM(prp_mask * fct.custom24)
,SUM(prp_mask * fct.custom25)
,SUM(prp_mask * fct.custom26)
,SUM(prp_mask * fct.custom27)
,SUM(prp_mask * fct.custom28)
,SUM(prp_mask * fct.custom29)
,SUM(prp_mask * fct.custom30)
)
BULK COLLECT INTO l_temp_overview_type
FROM PJI_FP_XBS_ACCUM_F_V fct --PA_REPORTING_LINES
, (SELECT time_id, period_type_id, pCalendarType calendar_type
,SIGN(bitand(record_type_id,g_ptd_record_type)) ptd_mask
,SIGN(bitand(record_type_id,g_qtd_record_type)) qtd_mask
,SIGN(bitand(record_type_id,g_ytd_record_type)) ytd_mask
,SIGN(bitand(record_type_id,g_itd_record_type)) itd_mask
,0 ac_mask
,0 prp_mask
FROM
PJI_TIME_CAL_RPT_STRUCT
WHERE 1=1
AND bitand(record_type_id,pTimeSlice) = record_type_id --the records above the selected slice are excluded
AND calendar_id = pCalendarId
AND report_date = TO_DATE(pPeriodDateJulian,l_julianFormat)
AND pCalendarType <> l_calendarType_E
UNION ALL
SELECT time_id, period_type_id, pCalendarType calendar_type
,SIGN(bitand(record_type_id,g_ptd_record_type)) ptd_mask
,SIGN(bitand(record_type_id,g_qtd_record_type)) qtd_mask
,SIGN(bitand(record_type_id,g_ytd_record_type)) ytd_mask
,SIGN(bitand(record_type_id,g_itd_record_type)) itd_mask
,0 ac_mask
,0 prp_mask
FROM
PJI_TIME_RPT_STRUCT
WHERE 1=1
AND bitand(record_type_id,pTimeSlice) = record_type_id
AND report_date = TO_DATE(pPeriodDateJulian,l_julianFormat)
AND pCalendarType = l_calendarType_E
UNION ALL
-- At Completion:
SELECT -1 time_id, 2048 period_type_id, l_calendarType_A calendar_type
,0 ptd_mask
,0 qtd_mask
,0 ytd_mask
,0 itd_mask
,1 ac_mask
,0 prp_mask
FROM dual
UNION ALL
-- Prior Period:
SELECT cal_period_id time_id, 32 period_type_id, pCalendarType calendar_type
,0 ptd_mask
,0 qtd_mask
,0 ytd_mask
,0 itd_mask
,0 ac_mask
,1 prp_mask
FROM
pji_time_cal_period_V
WHERE 1=1
--this is the first date of the period - 1 day, so I get the previous period, otherwise i'd get the curr. period
AND TO_DATE(pPeriodDateJulian,l_julianFormat)-1 BETWEEN start_date AND end_date
AND calendar_id = pCalendarId
AND pCalendarType <> l_calendarType_E
UNION ALL
SELECT ent_period_id time_id, 32 period_type_id, pCalendarType calendar_type
,0 ptd_mask
,0 qtd_mask
,0 ytd_mask
,0 itd_mask
,0 ac_mask
,1 prp_mask
FROM
pji_time_ent_period_V
WHERE 1=1
AND TO_DATE(pPeriodDateJulian,l_julianFormat)-1 BETWEEN start_date AND end_date
AND pCalendarType = l_calendarType_E
) TIME
WHERE 1=1
AND fct.prg_rollup_flag IN (l_PrgRollupFlag1,l_PrgRollupFlag2)
AND (
(pReportType = l_reportType_TS AND fct.rbs_aggr_level = l_rbs_aggr_level_T)
OR
((pReportType IN (l_reportType_TA, l_reportType_RA, l_reportType_RS)) AND (fct.rbs_aggr_level IN (l_rbs_aggr_level, l_rbs_aggr_level_L)))
)
AND fct.wbs_rollup_flag IN (l_WBSRollupFlag_N, pWBSRollupFlag)
AND fct.project_id = pProjectId
AND fct.project_element_id = pWBSElementId
AND fct.rbs_version_id = pRBSVersionId
AND fct.rbs_element_id = pRBSElementId
AND fct.currency_code = pCurrencyCode
AND bitand(fct.curr_record_type_id,pCurrencyRecordType) = pCurrencyRecordType
AND fct.period_type_id = TIME.period_type_id
AND fct.time_id = TIME.time_id
AND (fct.calendar_type = TIME.calendar_type OR TIME.calendar_type = l_calendarType_A)
AND fct.plan_version_id IN (
pActualVersionId
, pCstForecastVersionId
, pCstBudgetVersionId
, pCstBudget2VersionId
, pRevForecastVersionId
, pRevBudgetVersionId
, pRevBudget2VersionId
, pOrigCstForecastVersionId
, pOrigCstBudgetVersionId
, pOrigCstBudget2VersionId
, pOrigRevForecastVersionId
, pOrigRevBudgetVersionId
, pOrigRevBudget2VersionId
, pPriorCstForecastVersionId
, pPriorRevForecastVersionId
, DECODE(fct.prg_rollup_flag, 'Y', -3,-99)
, DECODE(fct.prg_rollup_Flag, 'Y', -4,-99)
)
AND fct.plan_type_id IN (
-1
, pCstForecastPlanTypeId
, pCstBudgetPlanTypeId
, pCstBudget2PlanTypeId
, pRevForecastPlanTypeId
, pRevBudgetPlanTypeId
, pRevBudget2PlanTypeId
)
GROUP BY plan_version_id, fct.plan_type_id;
SELECT
SUM(ptd_mask * fct.active_backlog)
,SUM(ptd_mask * fct.additional_funding_amount)
,SUM(ptd_mask * fct.ar_cash_applied_amount)
,SUM(ptd_mask * fct.ar_credit_memo_amount)
,SUM(ptd_mask * fct.ar_invoice_amount)
,SUM(ptd_mask * fct.ar_invoice_write_off_amount)
,SUM(ptd_mask * fct.ar_invoice_count)
,SUM(ptd_mask * fct.ar_amount_due)
,SUM(ptd_mask * fct.ar_amount_overdue)
,SUM(ptd_mask * fct.cancelled_funding_amount)
,SUM(ptd_mask * fct.dormant_backlog_inactiv)
,SUM(ptd_mask * fct.dormant_backlog_start)
,SUM(ptd_mask * fct.funding_adjustment_amount)
,SUM(ptd_mask * fct.initial_funding_amount)
,SUM(ptd_mask * fct.lost_backlog)
,SUM(ptd_mask * fct.revenue * l_contract_proj_mask)
,SUM(ptd_mask * fct.revenue_at_risk * l_contract_proj_mask)
,SUM(ptd_mask * fct.revenue_writeoff)
,SUM(ptd_mask * fct.unbilled_receivables)
,SUM(ptd_mask * fct.unearned_revenue)
,SUM(qtd_mask * fct.active_backlog)
,SUM(qtd_mask * fct.additional_funding_amount)
,SUM(qtd_mask * fct.ar_cash_applied_amount)
,SUM(qtd_mask * fct.ar_credit_memo_amount)
,SUM(qtd_mask * fct.ar_invoice_amount)
,SUM(qtd_mask * fct.ar_invoice_write_off_amount)
,SUM(qtd_mask * fct.ar_invoice_count)
,SUM(qtd_mask * fct.ar_amount_due)
,SUM(qtd_mask * fct.ar_amount_overdue)
,SUM(qtd_mask * fct.cancelled_funding_amount)
,SUM(qtd_mask * fct.dormant_backlog_inactiv)
,SUM(qtd_mask * fct.dormant_backlog_start)
,SUM(qtd_mask * fct.funding_adjustment_amount)
,SUM(qtd_mask * fct.initial_funding_amount)
,SUM(qtd_mask * fct.lost_backlog)
,SUM(qtd_mask * fct.revenue * l_contract_proj_mask)
,SUM(qtd_mask * fct.revenue_at_risk * l_contract_proj_mask)
,SUM(qtd_mask * fct.revenue_writeoff)
,SUM(qtd_mask * fct.unbilled_receivables)
,SUM(qtd_mask * fct.unearned_revenue)
,SUM(ytd_mask * fct.active_backlog)
,SUM(ytd_mask * fct.additional_funding_amount)
,SUM(ytd_mask * fct.ar_cash_applied_amount)
,SUM(ytd_mask * fct.ar_credit_memo_amount)
,SUM(ytd_mask * fct.ar_invoice_amount)
,SUM(ytd_mask * fct.ar_invoice_write_off_amount)
,SUM(ytd_mask * fct.ar_invoice_count)
,SUM(ytd_mask * fct.ar_amount_due)
,SUM(ytd_mask * fct.ar_amount_overdue)
,SUM(ytd_mask * fct.cancelled_funding_amount)
,SUM(ytd_mask * fct.dormant_backlog_inactiv)
,SUM(ytd_mask * fct.dormant_backlog_start)
,SUM(ytd_mask * fct.funding_adjustment_amount)
,SUM(ytd_mask * fct.initial_funding_amount)
,SUM(ytd_mask * fct.lost_backlog)
,SUM(ytd_mask * fct.revenue * l_contract_proj_mask)
,SUM(ytd_mask * fct.revenue_at_risk * l_contract_proj_mask)
,SUM(ytd_mask * fct.revenue_writeoff)
,SUM(ytd_mask * fct.unbilled_receivables)
,SUM(ytd_mask * fct.unearned_revenue)
,SUM(itd_mask * fct.active_backlog)
,SUM(itd_mask * fct.additional_funding_amount)
,SUM(itd_mask * fct.ar_cash_applied_amount)
,SUM(itd_mask * fct.ar_credit_memo_amount)
,SUM(itd_mask * fct.ar_invoice_amount)
,SUM(itd_mask * fct.ar_invoice_write_off_amount)
,SUM(itd_mask * fct.ar_invoice_count)
,SUM(itd_mask * fct.ar_amount_due)
,SUM(itd_mask * fct.ar_amount_overdue)
,SUM(itd_mask * fct.cancelled_funding_amount)
,SUM(itd_mask * fct.dormant_backlog_inactiv)
,SUM(itd_mask * fct.dormant_backlog_start)
,SUM(itd_mask * fct.funding_adjustment_amount)
,SUM(itd_mask * fct.initial_funding_amount)
,SUM(itd_mask * fct.lost_backlog)
,SUM(itd_mask * fct.revenue * l_contract_proj_mask)
,SUM(itd_mask * fct.revenue_at_risk * l_contract_proj_mask)
,SUM(itd_mask * fct.revenue_writeoff)
,SUM(itd_mask * fct.unbilled_receivables)
,SUM(itd_mask * fct.unearned_revenue)
,SUM(ac_mask * fct.active_backlog)
,SUM(ac_mask * fct.additional_funding_amount)
,SUM(ac_mask * fct.ar_cash_applied_amount)
,SUM(ac_mask * fct.ar_credit_memo_amount)
,SUM(ac_mask * fct.ar_invoice_amount)
,SUM(ac_mask * fct.ar_invoice_write_off_amount)
,SUM(ac_mask * fct.ar_invoice_count)
,SUM(ac_mask * fct.ar_amount_due)
,SUM(ac_mask * fct.ar_amount_overdue)
,SUM(ac_mask * fct.cancelled_funding_amount)
,SUM(ac_mask * fct.dormant_backlog_inactiv)
,SUM(ac_mask * fct.dormant_backlog_start)
,SUM(ac_mask * fct.funding_adjustment_amount)
,SUM(ac_mask * fct.initial_funding_amount)
,SUM(ac_mask * fct.lost_backlog)
,SUM(ac_mask * fct.revenue * l_contract_proj_mask)
,SUM(ac_mask * fct.revenue_at_risk * l_contract_proj_mask)
,SUM(ac_mask * fct.revenue_writeoff)
,SUM(ac_mask * fct.unbilled_receivables)
,SUM(ac_mask * fct.unearned_revenue)
,SUM(prp_mask * fct.active_backlog)
,SUM(prp_mask * fct.additional_funding_amount)
,SUM(prp_mask * fct.ar_cash_applied_amount)
,SUM(prp_mask * fct.ar_credit_memo_amount)
,SUM(prp_mask * fct.ar_invoice_amount)
,SUM(prp_mask * fct.ar_invoice_write_off_amount)
,SUM(prp_mask * fct.ar_invoice_count)
,SUM(prp_mask * fct.ar_amount_due)
,SUM(prp_mask * fct.ar_amount_overdue)
,SUM(prp_mask * fct.cancelled_funding_amount)
,SUM(prp_mask * fct.dormant_backlog_inactiv)
,SUM(prp_mask * fct.dormant_backlog_start)
,SUM(prp_mask * fct.funding_adjustment_amount)
,SUM(prp_mask * fct.initial_funding_amount)
,SUM(prp_mask * fct.lost_backlog)
,SUM(prp_mask * fct.revenue * l_contract_proj_mask)
,SUM(prp_mask * fct.revenue_at_risk * l_contract_proj_mask)
,SUM(prp_mask * fct.revenue_writeoff)
,SUM(prp_mask * fct.unbilled_receivables)
,SUM(prp_mask * fct.unearned_revenue)
,SUM(ptd_mask * fct.custom1)
,SUM(ptd_mask * fct.custom2)
,SUM(ptd_mask * fct.custom3)
,SUM(ptd_mask * fct.custom4)
,SUM(ptd_mask * fct.custom5)
,SUM(ptd_mask * fct.custom6)
,SUM(ptd_mask * fct.custom7)
,SUM(ptd_mask * fct.custom8)
,SUM(ptd_mask * fct.custom9)
,SUM(ptd_mask * fct.custom10)
,SUM(itd_mask * fct.custom11) -- Modified for bug 13713726
,SUM(itd_mask * fct.custom12) -- Modified for bug 13713726
,SUM(itd_mask * fct.custom13) -- Modified for bug 13713726
,SUM(itd_mask * fct.custom14) -- Modified for bug 13713726
,SUM(itd_mask * fct.custom15) -- Modified for bug 13713726
,SUM(ptd_mask * fct.custom16)
,SUM(ptd_mask * fct.custom17)
,SUM(ptd_mask * fct.custom18)
,SUM(ptd_mask * fct.custom19)
,SUM(ptd_mask * fct.custom20)
,SUM(ptd_mask * fct.custom21)
,SUM(ptd_mask * fct.custom22)
,SUM(ptd_mask * fct.custom23)
,SUM(ptd_mask * fct.custom24)
,SUM(ptd_mask * fct.custom25)
,SUM(ptd_mask * fct.custom26)
,SUM(ptd_mask * fct.custom27)
,SUM(ptd_mask * fct.custom28)
,SUM(ptd_mask * fct.custom29)
,SUM(ptd_mask * fct.custom30)
,SUM(qtd_mask * fct.custom1)
,SUM(qtd_mask * fct.custom2)
,SUM(qtd_mask * fct.custom3)
,SUM(qtd_mask * fct.custom4)
,SUM(qtd_mask * fct.custom5)
,SUM(qtd_mask * fct.custom6)
,SUM(qtd_mask * fct.custom7)
,SUM(qtd_mask * fct.custom8)
,SUM(qtd_mask * fct.custom9)
,SUM(qtd_mask * fct.custom10)
,SUM(itd_mask * fct.custom11) -- Modified for bug 13713726
,SUM(itd_mask * fct.custom12) -- Modified for bug 13713726
,SUM(itd_mask * fct.custom13) -- Modified for bug 13713726
,SUM(itd_mask * fct.custom14) -- Modified for bug 13713726
,SUM(itd_mask * fct.custom15) -- Modified for bug 13713726
,SUM(qtd_mask * fct.custom16)
,SUM(qtd_mask * fct.custom17)
,SUM(qtd_mask * fct.custom18)
,SUM(qtd_mask * fct.custom19)
,SUM(qtd_mask * fct.custom20)
,SUM(qtd_mask * fct.custom21)
,SUM(qtd_mask * fct.custom22)
,SUM(qtd_mask * fct.custom23)
,SUM(qtd_mask * fct.custom24)
,SUM(qtd_mask * fct.custom25)
,SUM(qtd_mask * fct.custom26)
,SUM(qtd_mask * fct.custom27)
,SUM(qtd_mask * fct.custom28)
,SUM(qtd_mask * fct.custom29)
,SUM(qtd_mask * fct.custom30)
,SUM(ytd_mask * fct.custom1)
,SUM(ytd_mask * fct.custom2)
,SUM(ytd_mask * fct.custom3)
,SUM(ytd_mask * fct.custom4)
,SUM(ytd_mask * fct.custom5)
,SUM(ytd_mask * fct.custom6)
,SUM(ytd_mask * fct.custom7)
,SUM(ytd_mask * fct.custom8)
,SUM(ytd_mask * fct.custom9)
,SUM(ytd_mask * fct.custom10)
,SUM(itd_mask * fct.custom11) -- Modified for bug 13713726
,SUM(itd_mask * fct.custom12) -- Modified for bug 13713726
,SUM(itd_mask * fct.custom13) -- Modified for bug 13713726
,SUM(itd_mask * fct.custom14) -- Modified for bug 13713726
,SUM(itd_mask * fct.custom15) -- Modified for bug 13713726
,SUM(ytd_mask * fct.custom16)
,SUM(ytd_mask * fct.custom17)
,SUM(ytd_mask * fct.custom18)
,SUM(ytd_mask * fct.custom19)
,SUM(ytd_mask * fct.custom20)
,SUM(ytd_mask * fct.custom21)
,SUM(ytd_mask * fct.custom22)
,SUM(ytd_mask * fct.custom23)
,SUM(ytd_mask * fct.custom24)
,SUM(ytd_mask * fct.custom25)
,SUM(ytd_mask * fct.custom26)
,SUM(ytd_mask * fct.custom27)
,SUM(ytd_mask * fct.custom28)
,SUM(ytd_mask * fct.custom29)
,SUM(ytd_mask * fct.custom30)
,SUM(itd_mask * fct.custom1)
,SUM(itd_mask * fct.custom2)
,SUM(itd_mask * fct.custom3)
,SUM(itd_mask * fct.custom4)
,SUM(itd_mask * fct.custom5)
,SUM(itd_mask * fct.custom6)
,SUM(itd_mask * fct.custom7)
,SUM(itd_mask * fct.custom8)
,SUM(itd_mask * fct.custom9)
,SUM(itd_mask * fct.custom10)
,SUM(ac_mask * fct.custom11) -- Modified for bug 13713726
,SUM(ac_mask * fct.custom12) -- Modified for bug 13713726
,SUM(ac_mask * fct.custom13) -- Modified for bug 13713726
,SUM(ac_mask * fct.custom14) -- Modified for bug 13713726
,SUM(ac_mask * fct.custom15) -- Modified for bug 13713726
,SUM(itd_mask * fct.custom16)
,SUM(itd_mask * fct.custom17)
,SUM(itd_mask * fct.custom18)
,SUM(itd_mask * fct.custom19)
,SUM(itd_mask * fct.custom20)
,SUM(itd_mask * fct.custom21)
,SUM(itd_mask * fct.custom22)
,SUM(itd_mask * fct.custom23)
,SUM(itd_mask * fct.custom24)
,SUM(itd_mask * fct.custom25)
,SUM(itd_mask * fct.custom26)
,SUM(itd_mask * fct.custom27)
,SUM(itd_mask * fct.custom28)
,SUM(itd_mask * fct.custom29)
,SUM(itd_mask * fct.custom30)
,SUM(ac_mask * fct.custom1)
,SUM(ac_mask * fct.custom2)
,SUM(ac_mask * fct.custom3)
,SUM(ac_mask * fct.custom4)
,SUM(ac_mask * fct.custom5)
,SUM(ac_mask * fct.custom6)
,SUM(ac_mask * fct.custom7)
,SUM(ac_mask * fct.custom8)
,SUM(ac_mask * fct.custom9)
,SUM(ac_mask * fct.custom10)
,SUM(ac_mask * fct.custom11)
,SUM(ac_mask * fct.custom12)
,SUM(ac_mask * fct.custom13)
,SUM(ac_mask * fct.custom14)
,SUM(ac_mask * fct.custom15)
,SUM(ac_mask * fct.custom16)
,SUM(ac_mask * fct.custom17)
,SUM(ac_mask * fct.custom18)
,SUM(ac_mask * fct.custom19)
,SUM(ac_mask * fct.custom20)
,SUM(ac_mask * fct.custom21)
,SUM(ac_mask * fct.custom22)
,SUM(ac_mask * fct.custom23)
,SUM(ac_mask * fct.custom24)
,SUM(ac_mask * fct.custom25)
,SUM(ac_mask * fct.custom26)
,SUM(ac_mask * fct.custom27)
,SUM(ac_mask * fct.custom28)
,SUM(ac_mask * fct.custom29)
,SUM(ac_mask * fct.custom30)
,SUM(prp_mask * fct.custom1)
,SUM(prp_mask * fct.custom2)
,SUM(prp_mask * fct.custom3)
,SUM(prp_mask * fct.custom4)
,SUM(prp_mask * fct.custom5)
,SUM(prp_mask * fct.custom6)
,SUM(prp_mask * fct.custom7)
,SUM(prp_mask * fct.custom8)
,SUM(prp_mask * fct.custom9)
,SUM(prp_mask * fct.custom10)
,SUM(prp_mask * fct.custom11)
,SUM(prp_mask * fct.custom12)
,SUM(prp_mask * fct.custom13)
,SUM(prp_mask * fct.custom14)
,SUM(prp_mask * fct.custom15)
,SUM(prp_mask * fct.custom16)
,SUM(prp_mask * fct.custom17)
,SUM(prp_mask * fct.custom18)
,SUM(prp_mask * fct.custom19)
,SUM(prp_mask * fct.custom20)
,SUM(prp_mask * fct.custom21)
,SUM(prp_mask * fct.custom22)
,SUM(prp_mask * fct.custom23)
,SUM(prp_mask * fct.custom24)
,SUM(prp_mask * fct.custom25)
,SUM(prp_mask * fct.custom26)
,SUM(prp_mask * fct.custom27)
,SUM(prp_mask * fct.custom28)
,SUM(prp_mask * fct.custom29)
,SUM(prp_mask * fct.custom30)
INTO l_pji_facts
FROM PJI_AC_XBS_ACCUM_F_V fct
,(SELECT time_id, period_type_id, pCalendarType calendar_type
,SIGN(bitand(record_type_id,g_ptd_record_type)) ptd_mask
,SIGN(bitand(record_type_id,g_qtd_record_type)) qtd_mask
,SIGN(bitand(record_type_id,g_ytd_record_type)) ytd_mask
,SIGN(bitand(record_type_id,g_itd_record_type)) itd_mask
,0 ac_mask
,0 prp_mask
FROM
PJI_TIME_CAL_RPT_STRUCT
WHERE 1=1
AND bitand(record_type_id,pTimeSlice) = record_type_id
AND calendar_id = pCalendarId
AND report_date = TO_DATE(pPeriodDateJulian,l_julianFormat)
AND pCalendarType <> l_calendarType_E
UNION ALL
SELECT time_id, period_type_id, pCalendarType
,SIGN(bitand(record_type_id,g_ptd_record_type)) ptd_mask
,SIGN(bitand(record_type_id,g_qtd_record_type)) qtd_mask
,SIGN(bitand(record_type_id,g_ytd_record_type)) ytd_mask
,SIGN(bitand(record_type_id,g_itd_record_type)) itd_mask
,0 ac_mask
,0 prp_mask
FROM
PJI_TIME_RPT_STRUCT
WHERE 1=1
AND bitand(record_type_id,pTimeSlice) = record_type_id
AND report_date = TO_DATE(pPeriodDateJulian,l_julianFormat)
AND pCalendarType = l_calendarType_E
UNION ALL
-- at completion
SELECT -1 time_id, 2048 period_type_id, l_calendarType_A calendar_type
,0 ptd_mask
,0 qtd_mask
,0 ytd_mask
,0 itd_mask
,1 ac_mask
,0 prp_mask
FROM dual
UNION ALL
-- prior period
SELECT cal_period_id time_id, 32 period_type_id, pCalendarType calendar_type
,0 ptd_mask
,0 qtd_mask
,0 ytd_mask
,0 itd_mask
,0 ac_mask
,1 prp_mask
FROM
pji_time_cal_period_V
WHERE 1=1
AND TO_DATE(pPeriodDateJulian,l_julianFormat)-1 BETWEEN start_date AND end_date -- report_date
AND calendar_id = pCalendarId
AND pCalendarType <> l_calendarType_E
UNION ALL
SELECT ent_period_id time_id, 32 period_type_id, pCalendarType calendar_type
,0 ptd_mask
,0 qtd_mask
,0 ytd_mask
,0 itd_mask
,0 ac_mask
,1 prp_mask
FROM
pji_time_ent_period_V
WHERE 1=1
AND TO_DATE(pPeriodDateJulian,l_julianFormat)-1 BETWEEN start_date AND end_date -- report_date
AND pCalendarType = l_calendarType_E
) TIME
WHERE 1=1
AND fct.project_id = pProjectId
AND fct.project_element_id = pWBSElementId
AND fct.currency_code = pCurrencyCode
AND bitand(fct.curr_record_type_id,pCurrencyRecordType) = pCurrencyRecordType
AND fct.prg_rollup_flag IN (l_PrgRollupFlag1,l_PrgRollupFlag2)
AND fct.time_id = TIME.time_id
AND fct.period_type_id = TIME.period_type_id
AND (fct.calendar_type = TIME.calendar_type OR TIME.calendar_type = l_calendarType_A);
SELECT completed_percentage/100
INTO l_completed_percentage
FROM pji_rep_xbs_denorm
WHERE project_id = pProjectId
--AND parent_element_id = pWBSElementId
AND child_element_id = pWBSElementId
AND rollup_flag = 'Y'
AND wbs_version_id = pWBSVersionId;
SELECT
wq_planned_quantity
, wq_planned_quantity
, wq_planned_quantity
, wq_planned_quantity
, wq_planned_quantity
, wq_planned_quantity
INTO l_planned_work_qt
FROM pa_proj_elem_ver_schedule
WHERE project_id = pProjectId
AND calendar_id = pCalendarId
AND element_version_id = pWBSElementId;
SELECT
wq_planned_quantity
, wq_planned_quantity
, wq_planned_quantity
, wq_planned_quantity
, wq_planned_quantity
, wq_planned_quantity
INTO l_incr_work_qt
FROM
pa_proj_elem_ver_schedule pevs
, pa_percent_completes pc
WHERE pevs.project_id = pProjectId
AND calendar_id = pCalendarId
AND element_version_id = pWBSElementId
AND pevs.project_id = pc.project_id
AND pc.object_type = 'PA_TASKS'
AND pc.published_flag = 'Y'
AND pc.OBJECT_VERSION_ID = pWBSElementId;
SELECT
report_date.end_date - start_date +1
, org_id
INTO
xDaysSinceITD
, l_org_id
FROM pa_projects_all,
( SELECT end_date
FROM pji_time_ent_period_v
WHERE 1=1
AND TO_DATE(pPeriodDateJulian,'j') BETWEEN start_date AND end_date
AND pCalendarType = 'E'
UNION ALL
SELECT end_date
FROM pji_time_cal_period_v
WHERE 1=1
AND (TO_DATE(pPeriodDateJulian,'j') BETWEEN start_date AND end_date)
AND calendar_id = pCalendarId
AND pCalendarType <> 'E'
) report_date
WHERE project_id = pProjectId;
SELECT days
INTO xDaysInPeriod
FROM (
SELECT (end_date - start_date+1) days
FROM pji_time_ent_period_v
WHERE 1=1
AND TO_DATE(pPeriodDateJulian,'j') BETWEEN start_date AND end_date
AND pCalendarType = 'E'
UNION ALL
SELECT (end_date - start_date+1) days
FROM pji_time_cal_period_v
WHERE 1=1
AND (TO_DATE(pPeriodDateJulian,'j') BETWEEN start_date AND end_date)
AND calendar_id = pCalendarId
AND pCalendarType <> 'E'
);
SELECT
MAX(DECODE (XTD_TYPE,'PTD',m.MEASURE_ID,NULL)),
MAX(DECODE (XTD_TYPE,'QTD',m.MEASURE_ID,NULL)),
MAX(DECODE (XTD_TYPE,'YTD',m.MEASURE_ID,NULL)),
MAX(DECODE (XTD_TYPE,'ITD',m.MEASURE_ID,NULL)),
MAX(DECODE (XTD_TYPE,'AC',m.MEASURE_ID,NULL)),
MAX(DECODE (XTD_TYPE,'PRP',m.MEASURE_ID,NULL))
BULK COLLECT INTO
ptd_measure_ids_tbl
, qtd_measure_ids_tbl
, ytd_measure_ids_tbl
, itd_measure_ids_tbl
, ac_measure_ids_tbl
, prp_measure_ids_tbl
FROM
pji_mt_measures_b m,
pji_mt_measure_sets_b S
WHERE 1=1
AND m.measure_set_code = S.measure_set_code
AND S.measure_set_type IN ('SEEDED', 'CUSTOM_STORED', 'CUSTOM_CALC')
AND m.measure_set_code LIKE 'PPF%'
GROUP BY m.measure_set_code
ORDER BY m.measure_set_code;
SELECT measure_format
BULK COLLECT INTO l_fp_cus_meas_formats
FROM pji_mt_measure_sets_b
WHERE 1=1
AND measure_set_type IN ('CUSTOM_CALC','CUSTOM_STORED')
AND measure_source = 'FP'
ORDER BY TO_NUMBER(SUBSTR(measure_set_code,16,2));
SELECT measure_format
BULK COLLECT INTO l_ac_cus_meas_formats
FROM pji_mt_measure_sets_b
WHERE 1=1
AND measure_set_type IN ('CUSTOM_CALC','CUSTOM_STORED')
AND measure_source = 'AC'
ORDER BY TO_NUMBER(SUBSTR(measure_set_code,16,2));