[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT object_id , LEVEL,
SYS_CONNECT_BY_PATH(object_id, '/') connect_path,
connect_by_isleaf leaf_node
FROM pa_ppr_obj_tmp r
START WITH r.parent_object_id is null CONNECT BY
PRIOR r.object_id = r.parent_object_id ;
delete from pa_ppr_obj_tmp;
insert into pa_ppr_obj_tmp ( object_id , parent_object_id )
select rbs_element_id , nvl(parent_element_id,p_rbs_version_id)
from pa_rbs_elements
where rbs_version_id = p_rbs_version_id;
insert into pa_ppr_obj_tmp ( object_id , parent_object_id ) values ( p_rbs_version_id , null );
UPDATE pa_rbs_elements
SET connect_path = l_connect_path_arr(k),
leaf_node = l_leaf_node_arr(k)
WHERE rbs_element_id = l_task_arr(k);
procedure plan_update
(
p_plan_version_id IN NUMBER ,
p_project_id IN NUMBER ,
p_rbs_version_id In NUMBER ,
p_cbs_version_id IN NUMBER ,
x_msg_code OUT NOCOPY VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2 ) as
l_msg_count number;
select cbs_version_id into l_cbs_version_id from pa_projects_all where project_id = p_project_id;
select cbs_version_id into l_cbs_version_id from pa_projects_all where project_id = p_project_id;
-- insert into sri_log values ( fnd_log_messages_s.nextval, p_msg);
SELECT /*+ ordered */
ra.rate_based_flag billable_flag ,
ra.task_id wbs_element_id ,
ra.resource_class_code resource_class ,
sum(nvl(bl.project_raw_cost,0)) ,
sum(nvl(bl.project_burdened_COST,0)) ,
sum(nvl(bl.project_revenue,0)) ,
sum(nvl(bl.project_init_raw_cost,0)) ,
sum(nvl(bl.project_init_burdened_cost,0)) ,
sum(nvl(bl.project_init_revenue,0)) ,
sum( decode( l_version_type,'FINANCIAL', nvl(bl.project_raw_cost,0) - nvl(bl.project_init_raw_cost,0), 0) ) ,
sum(decode( l_version_type,'FINANCIAL', nvl(bl.project_burdened_COST,0) - nvl(bl.project_init_burdened_cost,0), 0)) ,
sum(nvl(bl.raw_cost,0) ) func_raw_cost,
sum(nvl(bl.burdened_COST,0)) func_BRDN_COST ,
sum(nvl(bl.revenue,0)) func_revenue ,
sum(nvl(bl.init_raw_cost,0)) func_actual_raw_cost ,
sum(nvl(bl.init_burdened_cost,0)) func_actual_brdn_cost ,
sum(nvl(bl.init_revenue,0)) func_actual_revenue ,
sum(decode( l_version_type,'FINANCIAL', nvl(bl.raw_cost,0) - nvl(bl.init_raw_cost,0), 0)) func_etc_raw_cost ,
sum(decode( l_version_type,'FINANCIAL', nvl(bl.burdened_COST,0) - nvl(bl.init_burdened_cost,0), 0)) func_etc_brdn_cost ,
sum(nvl(bl.quantity,0) ) quantity,
sum(nvl(bl.init_quantity,0)) actual_quantity ,
sum(decode( l_version_type,'FINANCIAL', nvl(bl.quantity,0) - nvl(bl.init_quantity,0), 0)) etc_quantity ,
nvl(b.connect_path , '/'||DECODE(ra.task_id, 0, l_struct_elem_id, ra.task_id) ) ,
nvl(b.leaf_node,1) ,
nvl(b.wbs_rbs_level,1)
FROM PA_RESOURCE_ASSIGNMENTS ra ,
PA_BUDGET_LINES bl ,
pa_ppr_obj_tmp b
WHERE ra.resource_asSIGNment_id = bl.resource_asSIGNment_id
and ra.budget_version_id = l_budget_version_id
and ra.task_id = b.object_id
group by
ra.rate_based_flag ,
ra.task_id,
ra.resource_class_code,
nvl(b.connect_path , '/'||DECODE(ra.task_id, 0, l_struct_elem_id, ra.task_id) ) ,
nvl(b.leaf_node,1) ,
nvl(b.wbs_rbs_level,1) ;
SELECT
billable_flag ,
wbs_element_id ,
resource_class ,
sum(project_raw_cost) ,
sum(project_burdened_COST) ,
sum(project_revenue) ,
sum(project_init_raw_cost) ,
sum(project_init_burdened_cost) ,
sum(project_init_revenue),
sum(etc_prj_raw_cost) ,
sum(etc_prj_burdened_cost) ,
sum(func_raw_cost),
sum(func_BRDN_COST) ,
sum(func_revenue ),
sum(func_actual_raw_cost) ,
sum(func_actual_brdn_cost) ,
sum(func_actual_revenue) ,
sum(func_etc_raw_cost) ,
sum(func_etc_brdn_cost) ,
sum(quantity),
sum(actual_quantity) ,
sum(etc_quantity) ,
connect_path,
leaf_node,
task_level_arr
from ( SELECT /*+ ordered */
ra.rate_based_flag billable_flag ,
ra.task_id wbs_element_id ,
ra.resource_class_code resource_class ,
sum(nvl(bl.project_raw_cost,0)) project_raw_cost ,
sum(nvl(bl.project_burdened_COST,0)) project_burdened_COST ,
sum(nvl(bl.project_revenue,0)) project_revenue ,
sum(nvl(bl.project_init_raw_cost,0)) project_init_raw_cost ,
sum(nvl(bl.project_init_burdened_cost,0)) project_init_burdened_cost ,
sum(nvl(bl.project_init_revenue,0)) project_init_revenue ,
0 etc_prj_raw_cost ,
0 etc_prj_burdened_cost ,
sum(nvl(bl.raw_cost,0) ) func_raw_cost,
sum(nvl(bl.burdened_COST,0)) func_BRDN_COST ,
sum(nvl(bl.revenue,0)) func_revenue ,
sum(nvl(bl.init_raw_cost,0)) func_actual_raw_cost ,
sum(nvl(bl.init_burdened_cost,0)) func_actual_brdn_cost ,
sum(nvl(bl.init_revenue,0)) func_actual_revenue ,
0 func_etc_raw_cost ,
0 func_etc_brdn_cost ,
sum(nvl(bl.quantity,0) ) quantity,
sum(nvl(bl.init_quantity,0)) actual_quantity ,
0 etc_quantity ,
nvl(b.connect_path , '/'||DECODE(ra.task_id, 0, l_struct_elem_id, ra.task_id) ) connect_path,
nvl(b.leaf_node,1) leaf_node,
nvl(b.wbs_rbs_level,1) task_level_arr
FROM PA_RESOURCE_ASSIGNMENTS ra ,
PA_BUDGET_LINES bl ,
pa_ppr_obj_tmp b
WHERE ra.resource_asSIGNment_id = bl.resource_asSIGNment_id
and ra.budget_version_id = l_budget_version_id
and ra.task_id = b.object_id
group by
ra.rate_based_flag,
ra.task_id,
ra.resource_class_code,
nvl(b.connect_path , '/'||DECODE(ra.task_id, 0, l_struct_elem_id, ra.task_id) ) ,
nvl(b.leaf_node,1) ,
nvl(b.wbs_rbs_level,1)
UNION ALL
SELECT /*+ ordered */
bl2.rate_based_flag billable_flag ,
bl2.project_element_id wbs_element_id ,
bl2.resource_class_code resource_class ,
0 project_raw_cost ,
0 project_burdened_COST ,
0 project_revenue ,
0 project_init_raw_cost ,
0 project_init_burdened_cost ,
0 project_init_revenue,
bl2.etc_prj_raw_cost ,
bl2.etc_prj_burdened_cost ,
0 func_raw_cost,
0 func_BRDN_COST ,
0 func_revenue ,
0 func_actual_raw_cost ,
0 func_actual_brdn_cost ,
0 func_actual_revenue ,
bl2.ETC_PFC_RAW_COST func_etc_raw_cost ,
bl2.etc_pfc_burdened_cost func_etc_brdn_cost ,
0 quantity,
0 actual_quantity ,
bl2.etc_quantity etc_quantity ,
nvl(b.connect_path , '/'||DECODE(bl2.project_element_id, 0, l_struct_elem_id, bl2.project_element_id) ) connect_path,
nvl(b.leaf_node,1) leaf_node,
nvl(b.wbs_rbs_level,1) task_level_arr
FROM
PJI_FM_EXTR_PLAN_LINES bl2,
pa_ppr_obj_tmp b
WHERE
bl2.plan_version_id = l_budget_version_id
and bl2.project_element_id = b.object_id ) t1
group by
billable_flag ,
wbs_element_id ,
resource_class ,
connect_path,
leaf_node,
task_level_arr ;
cursor c2_wbs_plan_update is
SELECT
t.billable_flag
,t.task_id
,t.resource_class_code
,sum(nvl(t.prj_raw_cost,0))
,sum(nvl(t.prj_BRDN_COST,0))
,sum(nvl(t.prj_revenue,0))
,sum(nvl(t.act_prj_raw_cost,0))
,sum(nvl(t.act_prj_brdn_cost,0))
,sum(nvl(t.act_prj_revenue,0))
,sum(nvl(t.etc_prj_raw_cost,0))
,sum(nvl(t.etc_prj_brdn_cost,0))
,sum(nvl(t.pfc_raw_cost,0))
,sum(nvl(t.pfc_BRDN_COST,0))
,sum(nvl(t.pfc_revenue,0))
,sum(nvl(t.act_pfc_raw_cost,0))
,sum(nvl(t.act_pfc_brdn_cost,0))
,sum(nvl(t.act_pfc_revenue,0))
,sum(nvl(t.etc_pfc_raw_cost,0))
,sum(nvl(t.etc_pfc_brdn_cost,0))
,sum(nvl(t.quantity,0))
,sum(nvl(t.act_quantity,0))
,sum(nvl(t.etc_quantity,0))
, nvl(b.connect_path , '/'||DECODE(t.task_id, 0, l_struct_elem_id,t.task_id) ) connect_path
, nvl(b.leaf_node,1) leaf_node
, nvl(b.wbs_rbs_level,1) wbs_rbs_level
FROM
(
SELECT
bl.rate_based_flag billable_flag
, bl.project_element_id task_id
, bl.resource_class_code resource_class_code
, bl.prj_raw_cost prj_raw_cost
, bl.prj_burdened_cost prj_BRDN_COST
, bl.prj_revenue prj_revenue
, bl.act_prj_raw_cost act_prj_raw_cost
, bl.act_prj_burdened_cost act_prj_brdn_cost
, bl.act_prj_revenue act_prj_revenue
, bl.etc_prj_raw_cost etc_prj_raw_cost
, bl.etc_prj_burdened_cost etc_prj_brdn_cost
, bl.pfc_raw_cost pfc_raw_cost
, bl.pfc_burdened_cost pfc_BRDN_COST
, bl.pfc_revenue pfc_revenue
, bl.act_pfc_raw_cost act_pfc_raw_cost
, bl.act_pfc_burdened_cost act_pfc_brdn_cost
, bl.act_pfc_revenue act_pfc_revenue
, bl.ETC_PFC_RAW_COST etc_pfc_raw_cost
, bl.etc_pfc_burdened_cost etc_pfc_brdn_cost
, bl.quantity quantity
, bl.act_quantity act_quantity
, bl.etc_quantity etc_quantity
FROM
PJI_FM_EXTR_PLAN_LINES bl
WHERE 1=1
AND bl.project_id = l_project_id
AND bl.plan_version_id = l_budget_version_id
) t , pa_ppr_obj_tmp b
WHERE t.task_id = b.object_id (+)
group by
t.billable_flag
,t. task_id
,t.resource_class_code
, nvl(b.connect_path , '/'||DECODE(t.task_id, 0, l_struct_elem_id, t.task_id) )
, nvl(b.leaf_node,1)
, nvl(b.wbs_rbs_level,1) ;
cursor c2_rbs_cbs_plan_update is
SELECT /*+ ordered */
t.billable_flag
,t.task_id
,t.resource_class_code
,sum(nvl(t.prj_raw_cost,0))
,sum(nvl(t.prj_BRDN_COST,0))
,sum(nvl(t.prj_revenue,0))
,sum(nvl(t.act_prj_raw_cost,0))
,sum(nvl(t.act_prj_brdn_cost,0))
,sum(nvl(t.act_prj_revenue,0))
,sum(nvl(t.etc_prj_raw_cost,0))
,sum(nvl(t.etc_prj_brdn_cost,0))
,sum(nvl(t.pfc_raw_cost,0))
,sum(nvl(t.pfc_BRDN_COST,0))
,sum(nvl(t.pfc_revenue,0))
,sum(nvl(t.act_pfc_raw_cost,0))
,sum(nvl(t.act_pfc_brdn_cost,0))
,sum(nvl(t.act_pfc_revenue,0))
,sum(nvl(t.etc_pfc_raw_cost,0))
,sum(nvl(t.etc_pfc_brdn_cost,0))
,sum(nvl(t.quantity,0))
,sum(nvl(t.act_quantity,0))
,sum(nvl(t.etc_quantity,0))
, nvl(b.connect_path , '/'||DECODE(t.task_id, 0, l_struct_elem_id,t.task_id) ) connect_path
, nvl(b.leaf_node,1) leaf_node
, nvl(b.wbs_rbs_level,1) wbs_rbs_level
, '/'||DECODE(t.task_id, 0, l_struct_elem_id,t.task_id)
FROM
(
SELECT
bl.rate_based_flag billable_flag
, decode(p_fact_slice,'RBS',bl.rbs_element_id,'CBS',bl.cbs_element_id,null) task_id
, bl.resource_class_code resource_class_code
, bl.prj_raw_cost prj_raw_cost
, bl.prj_burdened_cost prj_BRDN_COST
, bl.prj_revenue prj_revenue
, bl.act_prj_raw_cost act_prj_raw_cost
, bl.act_prj_burdened_cost act_prj_brdn_cost
, bl.act_prj_revenue act_prj_revenue
, bl.etc_prj_raw_cost etc_prj_raw_cost
, bl.etc_prj_burdened_cost etc_prj_brdn_cost
, bl.pfc_raw_cost pfc_raw_cost
, bl.pfc_burdened_cost pfc_BRDN_COST
, bl.pfc_revenue pfc_revenue
, bl.act_pfc_raw_cost act_pfc_raw_cost
, bl.act_pfc_burdened_cost act_pfc_brdn_cost
, bl.act_pfc_revenue act_pfc_revenue
, bl.ETC_PFC_RAW_COST etc_pfc_raw_cost
, bl.etc_pfc_burdened_cost etc_pfc_brdn_cost
, bl.quantity quantity
, bl.act_quantity act_quantity
, bl.etc_quantity etc_quantity
, bl.rbs_element_id
, bl.cbs_element_id
FROM
PJI_FM_EXTR_PLAN_LINES bl
WHERE 1=1
AND bl.project_id = l_project_id
AND bl.plan_version_id = l_budget_version_id
) t , pa_ppr_obj_tmp b
WHERE t.task_id = b.object_id (+)
and (
( p_fact_slice = 'RBS' AND t.rbs_element_id = b.object_id )
OR
( p_fact_slice = 'CBS' AND t.cbs_element_id = b.object_id )
)
group by
t.billable_flag
,t.task_id
,t.resource_class_code
, nvl(b.connect_path , '/'||DECODE(t.task_id, 0, l_struct_elem_id, t.task_id) )
, nvl(b.leaf_node,1)
, nvl(b.wbs_rbs_level,1);
SELECT /*+ ordered */
ra.rate_based_flag billable_flag ,
decode(p_fact_slice,'RBS',ra.rbs_element_id,'CBS',ra.cbs_element_id,null) rbs_element_id ,
ra.resource_class_code resource_class ,
sum(nvl(bl.project_raw_cost,0)) ,
sum(nvl(bl.project_burdened_COST,0)) ,
sum(nvl(bl.project_revenue,0)) ,
sum(nvl(bl.project_init_raw_cost,0)) ,
sum(nvl(bl.project_init_burdened_cost,0)) ,
sum(nvl(bl.project_init_revenue,0)) ,
sum( decode( l_version_type,'FINANCIAL', nvl(bl.project_raw_cost,0) - nvl(bl.project_init_raw_cost,0), 0) ) ,
sum(decode( l_version_type,'FINANCIAL', nvl(bl.project_burdened_COST,0) - nvl(bl.project_init_burdened_cost,0), 0)) ,
sum(nvl(bl.raw_cost,0) ) func_raw_cost,
sum(nvl(bl.burdened_COST,0)) func_BRDN_COST ,
sum(nvl(bl.revenue,0)) func_revenue ,
sum(nvl(bl.init_raw_cost,0)) func_actual_raw_cost ,
sum(nvl(bl.init_burdened_cost,0)) func_actual_brdn_cost ,
sum(nvl(bl.init_revenue,0)) func_actual_revenue ,
sum(decode( l_version_type,'FINANCIAL', nvl(bl.raw_cost,0) - nvl(bl.init_raw_cost,0), 0)) func_etc_raw_cost ,
sum(decode( l_version_type,'FINANCIAL', nvl(bl.burdened_COST,0) - nvl(bl.init_burdened_cost,0), 0)) func_etc_brdn_cost ,
sum(nvl(bl.quantity,0) ) quantity,
sum(nvl(bl.init_quantity,0)) actual_quantity ,
sum(decode( l_version_type,'FINANCIAL', nvl(bl.quantity,0) - nvl(bl.init_quantity,0), 0)) etc_quantity ,
nvl(b.connect_path , '/'||decode(p_fact_slice,'RBS',ra.rbs_element_id,'CBS',ra.cbs_element_id,null) ) ,
nvl(b.leaf_node,1) ,
nvl(b.wbs_rbs_level,1),
'/'||decode(p_fact_slice,'RBS',ra.rbs_element_id,'CBS',ra.cbs_element_id,null)
FROM PA_RESOURCE_ASSIGNMENTS ra ,
PA_BUDGET_LINES bl ,
pa_ppr_obj_tmp b
WHERE ra.resource_asSIGNment_id = bl.resource_asSIGNment_id
and ra.budget_version_id = l_budget_version_id
and (
( p_fact_slice = 'RBS' AND ra.rbs_element_id = b.object_id )
OR
( p_fact_slice = 'CBS' AND ra.cbs_element_id = b.object_id )
)
group by
ra.rate_based_flag,
decode(p_fact_slice,'RBS',ra.rbs_element_id,'CBS',ra.cbs_element_id,null),
ra.resource_class_code,
nvl(b.connect_path , '/'||decode(p_fact_slice,'RBS',ra.rbs_element_id,'CBS',ra.cbs_element_id,null) ) ,
nvl(b.leaf_node,1) ,
nvl(b.wbs_rbs_level,1) ;
SELECT /*+ ordered */
ra.rate_based_flag billable_flag ,
decode(p_fact_slice,'CBS_TO_WBS_TO_RBS',ra.rbs_element_id, 'WBS_TO_RBS',ra.rbs_element_id,'WBS_TO_CBS',ra.cbs_element_id,null) rbs_element_id ,
ra.resource_class_code resource_class ,
sum(nvl(bl.project_raw_cost,0)) ,
sum(nvl(bl.project_burdened_COST,0)) ,
sum(nvl(bl.project_revenue,0)) ,
sum(nvl(bl.project_init_raw_cost,0)) ,
sum(nvl(bl.project_init_burdened_cost,0)) ,
sum(nvl(bl.project_init_revenue,0)) ,
sum(nvl(bl.project_raw_cost,0) - nvl(bl.project_init_raw_cost,0)) ,
sum(nvl(bl.project_burdened_cost,0) - nvl(bl.project_init_burdened_cost,0)) ,
sum(nvl(bl.raw_cost,0) ) func_raw_cost,
sum(nvl(bl.burdened_COST,0)) func_BRDN_COST ,
sum(nvl(bl.revenue,0)) func_revenue ,
sum(nvl(bl.init_raw_cost,0)) func_actual_raw_cost ,
sum(nvl(bl.init_burdened_cost,0)) func_actual_brdn_cost ,
sum(nvl(bl.init_revenue,0)) func_actual_revenue ,
sum(nvl(bl.raw_cost,0) - nvl(bl.init_raw_cost,0)) func_etc_raw_cost ,
sum(nvl(bl.burdened_cost,0) - nvl(bl.init_burdened_cost,0)) func_etc_brdn_cost ,
sum(nvl(bl.quantity,0) ) quantity,
sum(nvl(bl.init_quantity,0)) actual_quantity ,
sum(nvl(bl.quantity,0) - nvl(bl.init_quantity,0)) etc_quantity ,
nvl(b.connect_path , '/'||decode(p_fact_slice,'WBS_TO_RBS',ra.rbs_element_id,'WBS_TO_CBS',ra.cbs_element_id,null) ) ,
nvl(b.leaf_node,1) ,
nvl(b.wbs_rbs_level,1),
'/'||decode(p_fact_slice,'WBS_TO_RBS',ra.rbs_element_id,'WBS_TO_CBS',ra.cbs_element_id,null)
FROM PA_RESOURCE_ASSIGNMENTS ra ,
PA_BUDGET_LINES bl ,
pa_ppr_obj_tmp b,
pa_ppr_obj_tmp2 c
WHERE ra.resource_asSIGNment_id = bl.resource_asSIGNment_id
and ra.budget_version_id = l_budget_version_id
and ra.task_id = c.object_id
and (
( p_fact_slice = 'CBS_TO_WBS_TO_RBS' AND ra.rbs_element_id = b.object_id and ra.cbs_element_id = l_3_level_object_id ) OR
( p_fact_slice = 'WBS_TO_RBS' AND ra.rbs_element_id = b.object_id ) OR
( p_fact_slice = 'WBS_TO_CBS' AND ra.cbs_element_id = b.object_id )
)
group by
ra.rate_based_flag ,
decode(p_fact_slice,'CBS_TO_WBS_TO_RBS',ra.rbs_element_id, 'WBS_TO_RBS',ra.rbs_element_id,'WBS_TO_CBS',ra.cbs_element_id,null) ,
ra.resource_class_code ,
nvl(b.connect_path , '/'||decode(p_fact_slice,'WBS_TO_RBS',ra.rbs_element_id,'WBS_TO_CBS',ra.cbs_element_id,null) ) ,
nvl(b.leaf_node,1) ,
nvl(b.wbs_rbs_level,1),
'/'||decode(p_fact_slice,'WBS_TO_RBS',ra.rbs_element_id,'WBS_TO_CBS',ra.cbs_element_id,null) ;
SELECT /*+ ordered */
ra.rate_based_flag billable_flag ,
ra.task_id task_id ,
ra.resource_class_code resource_class ,
sum(nvl(bl.project_raw_cost,0)) ,
sum(nvl(bl.project_burdened_COST,0)) ,
sum(nvl(bl.project_revenue,0)) ,
sum(nvl(bl.project_init_raw_cost,0)) ,
sum(nvl(bl.project_init_burdened_cost,0)) ,
sum(nvl(bl.project_init_revenue,0)) ,
sum(nvl(bl.project_raw_cost,0) - nvl(bl.project_init_raw_cost,0)) ,
sum(nvl(bl.project_burdened_cost,0) - nvl(bl.project_init_burdened_cost,0)) ,
sum(nvl(bl.raw_cost,0) ) func_raw_cost,
sum(nvl(bl.burdened_COST,0)) func_BRDN_COST ,
sum(nvl(bl.revenue,0)) func_revenue ,
sum(nvl(bl.init_raw_cost,0)) func_actual_raw_cost ,
sum(nvl(bl.init_burdened_cost,0)) func_actual_brdn_cost ,
sum(nvl(bl.init_revenue,0)) func_actual_revenue ,
sum(nvl(bl.raw_cost,0) - nvl(bl.init_raw_cost,0)) func_etc_raw_cost ,
sum(nvl(bl.burdened_cost,0) - nvl(bl.init_burdened_cost,0)) func_etc_brdn_cost ,
sum(nvl(bl.quantity,0) ) quantity,
sum(nvl(bl.init_quantity,0)) actual_quantity ,
sum(nvl(bl.quantity,0) - nvl(bl.init_quantity,0)) etc_quantity ,
nvl(b.connect_path , '/'||DECODE(ra.task_id, 0, l_struct_elem_id, ra.task_id) ) ,
nvl(b.leaf_node,1) ,
nvl(b.wbs_rbs_level,1)
FROM PA_RESOURCE_ASSIGNMENTS ra ,
PA_BUDGET_LINES bl ,
pa_ppr_obj_tmp b,
pa_ppr_obj_tmp2 c
WHERE ra.resource_asSIGNment_id = bl.resource_asSIGNment_id
and ra.budget_version_id = l_budget_version_id
and ra.rbs_element_id = c.object_id
and ra.task_id = b.object_id
group by
ra.rate_based_flag ,
ra.task_id ,
ra.resource_class_code,
nvl(b.connect_path , '/'||DECODE(ra.task_id, 0, l_struct_elem_id, ra.task_id) ) ,
nvl(b.leaf_node,1) ,
nvl(b.wbs_rbs_level,1) ;
SELECT /*+ ordered */
ra.rate_based_flag billable_flag ,
decode(p_fact_slice,'WBS_TO_CBS_TO_RBS',ra.rbs_element_id,'CBS_TO_RBS',ra.rbs_element_id,'CBS_TO_WBS',ra.task_id,null) element_id ,
ra.resource_class_code resource_class ,
sum(nvl(bl.project_raw_cost,0)) ,
sum(nvl(bl.project_burdened_COST,0)) ,
sum(nvl(bl.project_revenue,0)) ,
sum(nvl(bl.project_init_raw_cost,0)) ,
sum(nvl(bl.project_init_burdened_cost,0)) ,
sum(nvl(bl.project_init_revenue,0)) ,
sum(nvl(bl.project_raw_cost,0) - nvl(bl.project_init_raw_cost,0)) ,
sum(nvl(bl.project_burdened_cost,0) - nvl(bl.project_init_burdened_cost,0)) ,
sum(nvl(bl.raw_cost,0) ) func_raw_cost,
sum(nvl(bl.burdened_COST,0)) func_BRDN_COST ,
sum(nvl(bl.revenue,0)) func_revenue ,
sum(nvl(bl.init_raw_cost,0)) func_actual_raw_cost ,
sum(nvl(bl.init_burdened_cost,0)) func_actual_brdn_cost ,
sum(nvl(bl.init_revenue,0)) func_actual_revenue ,
sum(nvl(bl.raw_cost,0) - nvl(bl.init_raw_cost,0)) func_etc_raw_cost ,
sum(nvl(bl.burdened_cost,0) - nvl(bl.init_burdened_cost,0)) func_etc_brdn_cost ,
sum(nvl(bl.quantity,0) ) quantity,
sum(nvl(bl.init_quantity,0)) actual_quantity ,
sum(nvl(bl.quantity,0) - nvl(bl.init_quantity,0)) etc_quantity ,
nvl(b.connect_path , '/'||DECODE(ra.task_id, 0, l_struct_elem_id, ra.task_id) ) ,
nvl(b.leaf_node,1) ,
nvl(b.wbs_rbs_level,1)
FROM PA_RESOURCE_ASSIGNMENTS ra ,
PA_BUDGET_LINES bl ,
pa_ppr_obj_tmp b,
pa_ppr_obj_tmp2 c
WHERE ra.resource_asSIGNment_id = bl.resource_asSIGNment_id
and ra.budget_version_id = l_budget_version_id
and ra.cbs_element_id = c.object_id
and (
( p_fact_slice = 'CBS_TO_RBS' AND ra.rbs_element_id = b.object_id ) OR
( p_fact_slice = 'CBS_TO_WBS' AND ra.task_id = b.object_id ) OR
( p_fact_slice = 'WBS_TO_CBS_TO_RBS' AND ra.rbs_element_id = b.object_id AND ra.task_id = l_3_level_object_id )
)
group by
ra.rate_based_flag ,
decode(p_fact_slice,'WBS_TO_CBS_TO_RBS',ra.rbs_element_id,'CBS_TO_RBS',ra.rbs_element_id,'CBS_TO_WBS',ra.task_id,null),
ra.resource_class_code,
nvl(b.connect_path , '/'||DECODE(ra.task_id, 0, l_struct_elem_id, ra.task_id) ) ,
nvl(b.leaf_node,1) ,
nvl(b.wbs_rbs_level,1) ;
SELECT
t.billable_flag
,t.task_id
,t.resource_class_code
,sum(nvl(t.prj_raw_cost,0))
,sum(nvl(t.prj_BRDN_COST,0))
,sum(nvl(t.prj_revenue,0))
,sum(nvl(t.act_prj_raw_cost,0))
,sum(nvl(t.act_prj_brdn_cost,0))
,sum(nvl(t.act_prj_revenue,0))
,sum(nvl(t.etc_prj_raw_cost,0))
,sum(nvl(t.etc_prj_brdn_cost,0))
,sum(nvl(t.pfc_raw_cost,0))
,sum(nvl(t.pfc_BRDN_COST,0))
,sum(nvl(t.pfc_revenue,0))
,sum(nvl(t.act_pfc_raw_cost,0))
,sum(nvl(t.act_pfc_brdn_cost,0))
,sum(nvl(t.act_pfc_revenue,0))
,sum(nvl(t.etc_pfc_raw_cost,0))
,sum(nvl(t.etc_pfc_brdn_cost,0))
,sum(nvl(t.quantity,0))
,sum(nvl(t.act_quantity,0))
,sum(nvl(t.etc_quantity,0))
, nvl(b.connect_path , '/'||DECODE(t.task_id, 0, l_struct_elem_id,t.task_id) ) connect_path
, nvl(b.leaf_node,1) leaf_node
, nvl(b.wbs_rbs_level,1) wbs_rbs_level
FROM
(
SELECT
bl.rate_based_flag billable_flag
, bl.project_element_id task_id
, bl.resource_class_code resource_class_code
, bl.prj_raw_cost prj_raw_cost
, bl.prj_burdened_cost prj_BRDN_COST
, bl.prj_revenue prj_revenue
, TO_NUMBER(NULL) act_prj_raw_cost
, TO_NUMBER(NULL) act_prj_brdn_cost
, TO_NUMBER(NULL) act_prj_revenue
, NVL(bl.prj_raw_cost, 0)+NVL(bl.etc_prj_raw_cost, 0) etc_prj_raw_cost
, NVL(bl.prj_burdened_cost, 0)+NVL(bl.etc_prj_burdened_cost, 0) etc_prj_brdn_cost
, bl.pfc_raw_cost pfc_raw_cost
, bl.pfc_burdened_cost pfc_BRDN_COST
, bl.pfc_revenue pfc_revenue
, TO_NUMBER(NULL) act_pfc_raw_cost
, TO_NUMBER(NULL) act_pfc_brdn_cost
, TO_NUMBER(NULL) act_pfc_revenue
, NVL(bl.pfc_raw_cost, 0)+NVL(bl.etc_pfc_raw_cost, 0) etc_pfc_raw_cost
, NVL(bl.pfc_burdened_cost, 0)+NVL(bl.etc_pfc_burdened_cost, 0) etc_pfc_brdn_cost
, bl.quantity quantity
, TO_NUMBER(NULL) act_quantity
, NVL(bl.quantity, 0)+NVL(bl.etc_quantity, 0) etc_quantity
FROM
PJI_FM_EXTR_PLAN_LINES bl
WHERE 1=1
AND bl.project_id = l_project_id
AND bl.plan_version_id = l_budget_version_id
UNION ALL
SELECT
ra.rate_based_flag billable_flag
, ra.task_id task_id
, ra.resource_class_code resource_class_code
, TO_NUMBER(NULL) prj_raw_cost
, TO_NUMBER(NULL) prj_BRDN_COST
, TO_NUMBER(NULL) prj_revenue
, bl.project_init_raw_cost act_prj_raw_cost -- new
, bl.project_init_burdened_cost act_prj_brdn_cost -- new
, bl.project_init_revenue act_prj_revenue -- new
, TO_NUMBER(NULL) etc_prj_raw_cost -- new
, TO_NUMBER(NULL) etc_prj_brdn_cost -- new
, TO_NUMBER(NULL) pfc_raw_cost
, TO_NUMBER(NULL) pfc_BRDN_COST
, TO_NUMBER(NULL) pfc_revenue
, bl.init_raw_cost act_pfc_raw_cost -- new
, bl.init_burdened_cost act_pfc_brdn_cost -- new
, bl.init_revenue act_pfc_revenue -- new
, TO_NUMBER(NULL) etc_pfc_raw_cost -- new
, TO_NUMBER(NULL) etc_pfc_brdn_cost -- new
, TO_NUMBER(NULL) quantity
, bl.init_quantity act_quantity -- new
, TO_NUMBER(NULL) etc_quantity -- new
FROM PA_RESOURCE_ASSIGNMENTS ra ,
PA_BUDGET_LINES bl
WHERE ra.resource_asSIGNment_id = bl.resource_asSIGNment_id
and ra.budget_version_id = l_budget_version_id
) t , pa_ppr_obj_tmp b
WHERE t.task_id = b.object_id (+)
group by
t.billable_flag
,t. task_id
,t.resource_class_code
, nvl(b.connect_path , '/'||DECODE(t.task_id, 0, l_struct_elem_id, t.task_id) )
, nvl(b.leaf_node,1)
, nvl(b.wbs_rbs_level,1) ;
SELECT object_id
FROM pa_ppr_obj_tmp2 r
START WITH r.parent_object_id = l_object_id CONNECT BY
PRIOR r.object_id = r.parent_object_id ;
SELECT object_id , LEVEL,
SYS_CONNECT_BY_PATH(object_id, '/') connect_path,
connect_by_isleaf leaf_node
FROM pa_ppr_obj_tmp r
START WITH r.parent_object_id is null CONNECT BY
PRIOR r.object_id = r.parent_object_id ;
select project_currency_code , projfunc_currency_code
into l_project_currency, l_functional_currency
from pa_projects_All
where project_id = l_project_id;
select /*+ ordered */ distinct decode(bud.fin_plan_type_id,10,'WORKPLAN','FINANCIAL') ,
s.element_version_id, s.proj_element_id
into l_version_type , l_struct_ver_id , l_struct_elem_id
from pa_budget_versions bud,
pa_proj_elem_ver_structure s,
pa_proj_structure_types ppst,
pa_structure_types pst
where
bud.budget_version_id = l_budget_version_id
and s.proj_element_id = ppst.proj_element_id (+)
and ppst.structure_type_id = pst.structure_type_id (+)
and pst.structure_type_class_code (+) = 'WORKPLAN'
and bud.project_structure_version_id = s.element_version_id (+);
select /*+ ordered */
distinct 0, s.proj_element_id
into l_struct_ver_id , l_struct_elem_id
from
pa_proj_elem_ver_structure s,
pa_proj_structure_types ppst,
pa_structure_types pst
where
s.project_id = l_project_id
and s.proj_element_id = ppst.proj_element_id
and ppst.structure_type_id = pst.structure_type_id
and pst.structure_type_class_code = 'FINANCIAL';
delete from pa_ppr_obj_tmp2;
ppr_log('inserting into pa_ppr_obj_tmp2 workplan '|| p_fact_slice);
insert into pa_ppr_obj_tmp2 ( object_id , parent_object_id )
select /*+ ordered */
a.proj_element_id , nvl(c.proj_element_id,l_struct_elem_id)
from PA_PROJ_ELEMENT_VERSIONS a , pa_object_relationships b , PA_PROJ_ELEMENT_VERSIONS c
where a.project_id = l_project_id
and a.parent_structure_version_id = l_struct_ver_id
and a.element_version_id = b.object_id_to1 (+)
and b.object_type_From (+) = 'PA_TASKS'
and b.relationship_type (+) = 'S'
and b.object_id_from1 = c.element_version_id (+)
AND ( p_wbs_rollup_flag = 'Y' or
( p_wbs_rollup_flag = 'N' and a.PROJ_element_id = p_proj_element_id ) ); --16767868 ;
ppr_log('inserting into tmp2 financial '|| p_fact_slice);
insert into pa_ppr_obj_tmp2 ( object_id , parent_object_id )
select /*+ ordered */
p.task_id , nvl(p.parent_task_id, l_struct_elem_id )
from pa_tasks p
where p.project_id = l_project_id
and ( p_wbs_rollup_flag = 'Y' or ( p_wbs_rollup_flag = 'N' and p.task_id = p_proj_element_id ) ); --16767868 ;
ppr_log( 'Inserting into pa_ppr_obj_tmp2');
insert into pa_ppr_obj_tmp2 ( object_id , parent_object_id )
select rbs_element_id , nvl(parent_element_id,l_rbs_cbs_version_id_2)
from pa_rbs_elements
where rbs_version_id = l_rbs_cbs_version_id_2;
ppr_log( 'Inserted into pa_ppr_obj_tmp2 count '||SQL%rowcount);
/* l_task_arr.delete;
SELECT object_id
BULK COLLECT INTO l_task_arr
FROM pa_ppr_obj_tmp2;
l_task_arr.delete;
delete from pa_ppr_obj_tmp2 ;
INSERT INTO pa_ppr_obj_tmp2 ( object_id ) values ( l_task_arr(i) ) ;
l_task_arr.delete;
ppr_log('before inserting l_object_id '||l_object_id);
INSERT INTO pa_ppr_obj_tmp2 ( object_id ) values ( l_object_id ) ;
delete from pa_ppr_obj_tmp;
insert into pa_ppr_obj_tmp ( object_id , parent_object_id )
select /*+ ordered */
a.proj_element_id , nvl(c.proj_element_id,l_struct_elem_id)
from PA_PROJ_ELEMENT_VERSIONS a , pa_object_relationships b , PA_PROJ_ELEMENT_VERSIONS c
where a.project_id = l_project_id
and a.object_type = 'PA_TASKS'
and a.parent_structure_version_id = l_struct_ver_id
and a.element_version_id = b.object_id_to1 (+)
and b.object_type_From (+) = 'PA_TASKS'
and b.relationship_type (+) = 'S'
and b.object_id_from1 = c.element_version_id (+) ;
insert into pa_ppr_obj_tmp ( object_id , parent_object_id ) values ( l_struct_elem_id , null );
ppr_log('TASKS INSERTED ',0);
insert into pa_ppr_obj_tmp ( object_id , parent_object_id )
select /*+ ordered */
p.task_id , nvl(p.parent_task_id, l_struct_elem_id )
from pa_tasks p
where p.project_id = l_project_id ;
insert into pa_ppr_obj_tmp ( object_id , parent_object_id ) values ( l_struct_elem_id , null );
insert into pa_ppr_obj_tmp ( object_id , parent_object_id )
select rbs_element_id , nvl(parent_element_id,l_rbs_cbs_version_id)
from pa_rbs_elements
where rbs_version_id = l_rbs_cbs_version_id;
ppr_log('first Insert into pa_ppr_obj_tmp '||SQL%rowcount,0);
insert into pa_ppr_obj_tmp ( object_id , parent_object_id ) values ( l_rbs_cbs_version_id , null );
ppr_log('second Inserted into pa_ppr_obj_tmp '||SQL%rowcount,0);
UPDATE pa_ppr_obj_tmp
SET connect_path = l_connect_path_arr(k),
leaf_node = l_leaf_node_arr(k),
wbs_rbs_level = l_task_level_arr(k)
WHERE object_id = l_task_arr(k);
l_task_arr.delete;
l_task_level_arr.delete;
l_connect_path_arr.delete;
l_line_connect_path_arr.delete;
l_leaf_node_arr.delete;
OPEN c2_wbs_plan_update;
FETCH c2_wbs_plan_update
BULK COLLECT INTO l_billable_flag_arr, l_task_arr, l_res_class_arr,
l_prj_amount1_arr,l_prj_amount2_arr,l_prj_amount3_arr,l_prj_amount4_arr,
l_prj_amount5_arr,l_prj_amount6_arr,l_prj_amount7_arr,l_prj_amount8_arr,
l_func_amount1_arr,l_func_amount2_arr,l_func_amount3_arr,l_func_amount4_arr,
l_func_amount5_arr,l_func_amount6_arr,l_func_amount7_arr,l_func_amount8_arr,
l_qty1_arr, l_qty2_arr, l_qty3_arr,
l_connect_path_arr ,
l_leaf_node_arr ,
l_task_level_arr;
CLOSE c2_wbs_plan_update;
ppr_log('into RBS C2 cursor plan update ',0);
OPEN c2_rbs_cbs_plan_update;
FETCH c2_rbs_cbs_plan_update
BULK COLLECT INTO l_billable_flag_arr, l_task_arr, l_res_class_arr,
l_prj_amount1_arr,l_prj_amount2_arr,l_prj_amount3_arr,l_prj_amount4_arr,
l_prj_amount5_arr,l_prj_amount6_arr,l_prj_amount7_arr,l_prj_amount8_arr,
l_func_amount1_arr,l_func_amount2_arr,l_func_amount3_arr,l_func_amount4_arr,
l_func_amount5_arr,l_func_amount6_arr,l_func_amount7_arr,l_func_amount8_arr,
l_qty1_arr, l_qty2_arr, l_qty3_arr,
l_connect_path_arr ,
l_leaf_node_arr ,
l_task_level_arr, l_line_connect_path_arr;
CLOSE c2_rbs_cbs_plan_update;
l_out_qty1_arr.delete;l_out_qty2_arr.delete;l_out_qty3_arr.delete;l_out_labor_qty1_arr.delete;
l_out_labor_qty2_arr.delete;l_out_labor_qty3_arr.delete;
l_out_equip_qty1_arr.delete;l_out_equip_qty2_arr.delete;l_out_equip_qty3_arr.delete;
l_out_amount1_arr.delete;l_out_amount2_arr.delete;l_out_amount3_arr.delete;
l_out_amount4_arr.delete;l_out_amount5_arr.delete;l_out_amount6_arr.delete;
l_out_amount7_arr.delete;l_out_amount8_arr.delete;
l_out_bill_labor_Qty1_arr.delete;l_out_bill_equip_Qty1_arr.delete;l_out_bill_labor_Qty2_arr.delete;
l_out_bill_equip_Qty2_arr.delete;l_out_bill_labor_Qty3_arr.delete;l_out_bill_equip_Qty3_arr.delete;
l_out_bill_amt1_arr.delete;l_out_bill_amt2_arr.delete;l_out_bill_labor_amt1_arr.delete;
l_out_bill_labor_amt2_arr.delete;l_out_equip_amt1_arr.delete;l_out_equip_amt2_arr.delete;
l_out_capit_amt1_arr.delete;l_out_capit_amt2_arr.delete;l_out_labor_amt1_arr.delete;
l_out_labor_amt2_arr.delete;
l_out_labor_amt3_arr.delete;l_out_labor_amt4_arr.delete;l_out_labor_amt5_arr.delete;l_out_labor_amt6_arr.delete;
l_out_labor_amt7_arr.delete;l_out_labor_amt8_arr.delete;
l_out_equip_amt3_arr.delete;l_out_equip_amt4_arr.delete;
l_out_equip_amt5_arr.delete;l_out_equip_amt6_arr.delete;
l_out_equip_amt7_arr.delete;l_out_equip_amt8_arr.delete;
l_out_qty1_arr.delete;l_out_qty2_arr.delete;l_out_qty3_arr.delete;l_out_labor_qty1_arr.delete;
l_out_labor_qty2_arr.delete;l_out_labor_qty3_arr.delete;
l_out_equip_qty1_arr.delete;l_out_equip_qty2_arr.delete;l_out_equip_qty3_arr.delete;
l_out_amount1_arr.delete;l_out_amount2_arr.delete;l_out_amount3_arr.delete;
l_out_amount4_arr.delete;l_out_amount5_arr.delete;l_out_amount6_arr.delete;
l_out_amount7_arr.delete;l_out_amount8_arr.delete;
l_out_bill_labor_Qty1_arr.delete;l_out_bill_equip_Qty1_arr.delete;l_out_bill_labor_Qty2_arr.delete;
l_out_bill_equip_Qty2_arr.delete;l_out_bill_labor_Qty3_arr.delete;l_out_bill_equip_Qty3_arr.delete;
l_out_bill_amt1_arr.delete;l_out_bill_amt2_arr.delete;l_out_bill_labor_amt1_arr.delete;
l_out_bill_labor_amt2_arr.delete;l_out_equip_amt1_arr.delete;l_out_equip_amt2_arr.delete;
l_out_capit_amt1_arr.delete;l_out_capit_amt2_arr.delete;l_out_labor_amt1_arr.delete;
l_out_labor_amt2_arr.delete;
l_out_labor_amt3_arr.delete;l_out_labor_amt4_arr.delete;l_out_labor_amt5_arr.delete;l_out_labor_amt6_arr.delete;
l_out_labor_amt7_arr.delete;l_out_labor_amt8_arr.delete;
l_out_equip_amt3_arr.delete;l_out_equip_amt4_arr.delete;
l_out_equip_amt5_arr.delete;l_out_equip_amt6_arr.delete;
l_out_equip_amt7_arr.delete;l_out_equip_amt8_arr.delete;
l_line_task_level_arr.delete; -- satya
l_out_qty1_arr.delete;l_out_qty2_arr.delete;l_out_qty3_arr.delete;l_out_labor_qty1_arr.delete;
l_out_labor_qty2_arr.delete;l_out_labor_qty3_arr.delete;
l_out_equip_qty1_arr.delete;l_out_equip_qty2_arr.delete;l_out_equip_qty3_arr.delete;
l_out_amount1_arr.delete;l_out_amount2_arr.delete;l_out_amount3_arr.delete;
l_out_amount4_arr.delete;l_out_amount5_arr.delete;l_out_amount6_arr.delete;
l_out_amount7_arr.delete;l_out_amount8_arr.delete;
l_out_bill_labor_Qty1_arr.delete;l_out_bill_equip_Qty1_arr.delete;l_out_bill_labor_Qty2_arr.delete;
l_out_bill_equip_Qty2_arr.delete;l_out_bill_labor_Qty3_arr.delete;l_out_bill_equip_Qty3_arr.delete;
l_out_bill_amt1_arr.delete;l_out_bill_amt2_arr.delete;l_out_bill_labor_amt1_arr.delete;
l_out_bill_labor_amt2_arr.delete;l_out_equip_amt1_arr.delete;l_out_equip_amt2_arr.delete;
l_out_capit_amt1_arr.delete;l_out_capit_amt2_arr.delete;l_out_labor_amt1_arr.delete;
l_out_labor_amt2_arr.delete;
l_out_labor_amt3_arr.delete;l_out_labor_amt4_arr.delete;l_out_labor_amt5_arr.delete;l_out_labor_amt6_arr.delete;
l_out_labor_amt7_arr.delete;l_out_labor_amt8_arr.delete;
l_out_equip_amt3_arr.delete;l_out_equip_amt4_arr.delete;
l_out_equip_amt5_arr.delete;l_out_equip_amt6_arr.delete;
l_out_equip_amt7_arr.delete;l_out_equip_amt8_arr.delete;
l_out_qty1_arr.delete;l_out_qty2_arr.delete;l_out_qty3_arr.delete;l_out_labor_qty1_arr.delete;
l_out_labor_qty2_arr.delete;l_out_labor_qty3_arr.delete;
l_out_equip_qty1_arr.delete;l_out_equip_qty2_arr.delete;l_out_equip_qty3_arr.delete;
l_out_amount1_arr.delete;l_out_amount2_arr.delete;l_out_amount3_arr.delete;
l_out_amount4_arr.delete;l_out_amount5_arr.delete;l_out_amount6_arr.delete;
l_out_amount7_arr.delete;l_out_amount8_arr.delete;
l_out_bill_labor_Qty1_arr.delete;l_out_bill_equip_Qty1_arr.delete;l_out_bill_labor_Qty2_arr.delete;
l_out_bill_equip_Qty2_arr.delete;l_out_bill_labor_Qty3_arr.delete;l_out_bill_equip_Qty3_arr.delete;
l_out_bill_amt1_arr.delete;l_out_bill_amt2_arr.delete;l_out_bill_labor_amt1_arr.delete;
l_out_bill_labor_amt2_arr.delete;l_out_equip_amt1_arr.delete;l_out_equip_amt2_arr.delete;
l_out_capit_amt1_arr.delete;l_out_capit_amt2_arr.delete;l_out_labor_amt1_arr.delete;
l_out_labor_amt2_arr.delete;
l_out_labor_amt3_arr.delete;l_out_labor_amt4_arr.delete;l_out_labor_amt5_arr.delete;l_out_labor_amt6_arr.delete;
l_out_labor_amt7_arr.delete;l_out_labor_amt8_arr.delete;
l_out_equip_amt3_arr.delete;l_out_equip_amt4_arr.delete;
l_out_equip_amt5_arr.delete;l_out_equip_amt6_arr.delete;
l_out_equip_amt7_arr.delete;l_out_equip_amt8_arr.delete;
l_line_task_level_arr.delete; -- satya
l_upd_task_flag_arr.delete;
l_upd_task_arr.delete;
l_upd_task_arr_2.delete;
UPDATE pa_pjt_wbs_rbs_cbs_fact fact /* bulk bind */
SET (
----
RAW_COST, BRDN_COST, REVENUE
, bill_RAW_COST, bill_BRDN_COST, bill_LABOR_RAW_COST, bill_LABOR_BRDN_COST
, bill_LABOR_HRS, EQUIPMENT_RAW_COST, EQUIPMENT_BRDN_COST
--------
, CAPITALIZABLE_RAW_COST, CAPITALIZABLE_BRDN_COST
, LABOR_RAW_COST, LABOR_BRDN_COST, LABOR_HRS, LABOR_REVENUE
, EQUIPMENT_HOURS, BILLABLE_EQUIPMENT_HOURS
----
, SUP_INV_COMMITTED_COST, PO_COMMITTED_COST, PR_COMMITTED_COST, OTH_COMMITTED_COST
, ACT_LABOR_HRS, ACT_EQUIP_HRS, ACT_LABOR_BRDN_COST
, ACT_EQUIP_BRDN_COST, ACT_BRDN_COST, ACT_RAW_COST
-------
, ACT_REVENUE, ACT_LABOR_RAW_COST,ACT_EQUIP_RAW_COST
, ETC_LABOR_HRS, ETC_EQUIP_HRS, ETC_LABOR_BRDN_COST
----
, ETC_EQUIP_BRDN_COST, ETC_BRDN_COST, ETC_RAW_COST
, ETC_LABOR_RAW_COST, ETC_EQUIP_RAW_COST
-----
, CUSTOM1, CUSTOM2, CUSTOM3, CUSTOM4, CUSTOM5, CUSTOM6,CUSTOM7, CUSTOM8, CUSTOM9, CUSTOM10, CUSTOM11
, CUSTOM12, CUSTOM13, CUSTOM14, CUSTOM15 ) =
(
SELECT
----
p_fact_amount1_arr(i),p_fact_amount2_arr(i),p_fact_amount3_arr(i)
,p_fact_bill_amt1_arr(i),p_fact_bill_amt2_arr(i),p_fact_bill_labor_amt1_arr(i),p_fact_bill_labor_amt2_arr(i)
,p_fact_bill_labor_Qty1_arr(i),p_fact_equip_amt1_arr(i),p_fact_equip_amt2_arr(i)
-----
,p_fact_capit_amt1_arr(i),p_fact_capit_amt2_arr(i)
,p_fact_labor_amt1_arr(i),p_fact_labor_amt2_arr(i),p_fact_labor_qty1_arr(i), p_fact_labor_amt3_arr(i)
, p_fact_equip_qty1_arr(i),p_fact_bill_equip_Qty1_arr(i)
----
,0,0,0,0
,p_fact_labor_qty2_arr(i),p_fact_equip_qty2_arr(i),p_fact_labor_amt5_arr(i)
,p_fact_equip_amt5_arr(i) ,p_fact_amount5_arr(i),p_fact_amount4_arr(i)
-------
,p_fact_amount6_arr(i),p_fact_labor_amt4_arr(i),p_fact_equip_amt4_arr(i)
,p_fact_labor_qty3_arr(i),p_fact_equip_qty3_arr(i),p_fact_labor_amt8_arr(i)
----
,p_fact_equip_amt8_arr(i),p_fact_amount8_arr(i),p_fact_amount7_arr(i)
, p_fact_labor_amt7_arr(i),p_fact_equip_amt7_arr(i)
-------
-------
,0,0,0,0,0,0,0,0,0,0,0
,0,0,0,0
FROM DUAL
)
WHERE PROJECT_ID = p_project_id
AND ( ( PROJECT_ELEMENT_ID = p_3_level_object_id AND
RBS_ELEMENT_ID = p_fact_task_arr(i) and RBS_VERSION_ID = p_rbs_cbs_version_id and p_fact_slice = 'WBS_TO_CBS_TO_RBS' )
OR ( RBS_ELEMENT_ID = p_fact_task_arr(i) and RBS_VERSION_ID = p_rbs_cbs_version_id and p_fact_slice = 'CBS_TO_RBS' )
OR ( PROJECT_ELEMENT_ID = p_fact_task_arr(i) and p_fact_slice = 'CBS_TO_WBS' ) )
AND CBS_VERSION_ID = decode(p_fact_slice,'CBS_TO_WBS',p_rbs_cbs_version_id,'CBS_TO_RBS',p_rbs_cbs_version_id_2,'WBS_TO_CBS_TO_RBS',p_rbs_cbs_version_id_2,-1)
AND CBS_ELEMENT_ID = p_object_id
AND RBS_AGGR_LEVEL = G_RBS_AGGR_LEVEL --'T'
AND WBS_ROLLUP_FLAG = G_WBS_ROLLUP_FLAG -- 'Y'
AND PRG_ROLLUP_FLAG = G_PRG_ROLLUP_FLAG -- 'N'
AND PLAN_VERSION_ID = p_budget_version_id
AND CURRENCY_CODE = p_currency_code
RETURNING RBS_ELEMENT_ID, PROJECT_ELEMENT_ID
BULK COLLECT INTO l_upd_task_arr, l_upd_task_arr_2;
l_upd_task_arr.delete;
UPDATE pa_pjt_wbs_rbs_cbs_fact fact /* bulk bind */
SET (
----
RAW_COST, BRDN_COST, REVENUE
, bill_RAW_COST, bill_BRDN_COST
, bill_LABOR_RAW_COST, bill_LABOR_BRDN_COST
, bill_LABOR_HRS, EQUIPMENT_RAW_COST, EQUIPMENT_BRDN_COST
--------
, CAPITALIZABLE_RAW_COST, CAPITALIZABLE_BRDN_COST
, LABOR_RAW_COST, LABOR_BRDN_COST
, LABOR_HRS, LABOR_REVENUE
, EQUIPMENT_HOURS, BILLABLE_EQUIPMENT_HOURS
----
, SUP_INV_COMMITTED_COST, PO_COMMITTED_COST, PR_COMMITTED_COST, OTH_COMMITTED_COST
, ACT_LABOR_HRS, ACT_EQUIP_HRS, ACT_LABOR_BRDN_COST
, ACT_EQUIP_BRDN_COST, ACT_BRDN_COST, ACT_RAW_COST
-------
, ACT_REVENUE, ACT_LABOR_RAW_COST,ACT_EQUIP_RAW_COST
, ETC_LABOR_HRS, ETC_EQUIP_HRS, ETC_LABOR_BRDN_COST
----
, ETC_EQUIP_BRDN_COST, ETC_BRDN_COST, ETC_RAW_COST
, ETC_LABOR_RAW_COST, ETC_EQUIP_RAW_COST
-----
, CUSTOM1, CUSTOM2, CUSTOM3, CUSTOM4, CUSTOM5, CUSTOM6,CUSTOM7, CUSTOM8, CUSTOM9, CUSTOM10, CUSTOM11
, CUSTOM12, CUSTOM13, CUSTOM14, CUSTOM15 ) =
(
SELECT
----
p_fact_amount1_arr(i)+RAW_COST,p_fact_amount2_arr(i)+BRDN_COST,p_fact_amount3_arr(i)+REVENUE
,p_fact_bill_amt1_arr(i)+bill_RAW_COST,p_fact_bill_amt2_arr(i)+bill_BRDN_COST
,p_fact_bill_labor_amt1_arr(i)+bill_LABOR_RAW_COST,p_fact_bill_labor_amt2_arr(i)+bill_LABOR_BRDN_COST
,p_fact_bill_labor_Qty1_arr(i)+bill_LABOR_HRS,p_fact_equip_amt1_arr(i)+EQUIPMENT_RAW_COST,p_fact_equip_amt2_arr(i)+EQUIPMENT_BRDN_COST
-----
,p_fact_capit_amt1_arr(i)+CAPITALIZABLE_RAW_COST,p_fact_capit_amt2_arr(i)+CAPITALIZABLE_BRDN_COST
,p_fact_labor_amt1_arr(i)+LABOR_RAW_COST,p_fact_labor_amt2_arr(i)+LABOR_BRDN_COST
,p_fact_labor_qty1_arr(i)+LABOR_HRS, p_fact_labor_amt3_arr(i)+LABOR_REVENUE
, p_fact_equip_qty1_arr(i)+EQUIPMENT_HOURS,p_fact_bill_equip_Qty1_arr(i)+BILLABLE_EQUIPMENT_HOURS
----
,0,0,0,0
,p_fact_labor_qty2_arr(i)+ACT_LABOR_HRS,p_fact_equip_qty2_arr(i)+ACT_EQUIP_HRS,p_fact_labor_amt5_arr(i)+ACT_LABOR_BRDN_COST
,p_fact_equip_amt5_arr(i)+ACT_EQUIP_BRDN_COST ,p_fact_amount5_arr(i)+ACT_BRDN_COST,p_fact_amount4_arr(i)+ACT_RAW_COST
-------
,p_fact_amount6_arr(i)+ACT_REVENUE,p_fact_labor_amt4_arr(i)+ACT_LABOR_RAW_COST,p_fact_equip_amt4_arr(i)+ACT_EQUIP_RAW_COST
,p_fact_labor_qty3_arr(i),p_fact_equip_qty3_arr(i),p_fact_labor_amt8_arr(i)
----
,p_fact_equip_amt8_arr(i),p_fact_amount8_arr(i),p_fact_amount7_arr(i)
, p_fact_labor_amt7_arr(i),p_fact_equip_amt7_arr(i)
-------
-------
,0,0,0,0,0,0,0,0,0,0,0
,0,0,0,0
FROM DUAL
)
WHERE PROJECT_ID = p_project_id
AND ( ( PROJECT_ELEMENT_ID = p_3_level_object_id AND
RBS_ELEMENT_ID = p_fact_task_arr(i) and RBS_VERSION_ID = p_rbs_cbs_version_id and p_fact_slice = 'WBS_TO_CBS_TO_RBS' )
OR ( RBS_ELEMENT_ID = p_fact_task_arr(i) and RBS_VERSION_ID = p_rbs_cbs_version_id and p_fact_slice = 'CBS_TO_RBS' )
OR ( PROJECT_ELEMENT_ID = p_fact_task_arr(i) and p_fact_slice = 'CBS_TO_WBS' ) )
AND CBS_VERSION_ID = decode(p_fact_slice,'CBS_TO_WBS',p_rbs_cbs_version_id,'CBS_TO_RBS',p_rbs_cbs_version_id_2,'WBS_TO_CBS_TO_RBS',p_rbs_cbs_version_id_2,-1)
AND CBS_ELEMENT_ID = p_object_id
AND RBS_AGGR_LEVEL = G_RBS_AGGR_LEVEL --'T'
AND WBS_ROLLUP_FLAG = G_WBS_ROLLUP_FLAG -- 'Y'
AND PRG_ROLLUP_FLAG = G_PRG_ROLLUP_FLAG -- 'N'
AND PLAN_VERSION_ID = p_budget_version_id
AND CURRENCY_CODE = p_currency_code
RETURNING RBS_ELEMENT_ID, PROJECT_ELEMENT_ID
BULK COLLECT INTO l_upd_task_arr, l_upd_task_arr_2;
l_upd_task_arr.delete;
UPDATE pa_pjt_wbs_rbs_cbs_fact fact /* bulk bind */
SET (
----
RAW_COST, BRDN_COST, REVENUE
, bill_RAW_COST, bill_BRDN_COST, bill_LABOR_RAW_COST, bill_LABOR_BRDN_COST
, bill_LABOR_HRS, EQUIPMENT_RAW_COST, EQUIPMENT_BRDN_COST
--------
, CAPITALIZABLE_RAW_COST, CAPITALIZABLE_BRDN_COST
, LABOR_RAW_COST, LABOR_BRDN_COST, LABOR_HRS, LABOR_REVENUE
, EQUIPMENT_HOURS, BILLABLE_EQUIPMENT_HOURS
----
, SUP_INV_COMMITTED_COST, PO_COMMITTED_COST, PR_COMMITTED_COST, OTH_COMMITTED_COST
, ACT_LABOR_HRS, ACT_EQUIP_HRS, ACT_LABOR_BRDN_COST
, ACT_EQUIP_BRDN_COST, ACT_BRDN_COST, ACT_RAW_COST
-------
, ACT_REVENUE, ACT_LABOR_RAW_COST,ACT_EQUIP_RAW_COST
, ETC_LABOR_HRS, ETC_EQUIP_HRS, ETC_LABOR_BRDN_COST
----
, ETC_EQUIP_BRDN_COST, ETC_BRDN_COST, ETC_RAW_COST
, ETC_LABOR_RAW_COST, ETC_EQUIP_RAW_COST
-----
, CUSTOM1, CUSTOM2, CUSTOM3, CUSTOM4, CUSTOM5, CUSTOM6,CUSTOM7, CUSTOM8, CUSTOM9, CUSTOM10, CUSTOM11
, CUSTOM12, CUSTOM13, CUSTOM14, CUSTOM15 ) =
(
SELECT
----
p_fact_amount1_arr(i),p_fact_amount2_arr(i),p_fact_amount3_arr(i)
,p_fact_bill_amt1_arr(i),p_fact_bill_amt2_arr(i),p_fact_bill_labor_amt1_arr(i),p_fact_bill_labor_amt2_arr(i)
,p_fact_bill_labor_Qty1_arr(i),p_fact_equip_amt1_arr(i),p_fact_equip_amt2_arr(i)
-----
,p_fact_capit_amt1_arr(i),p_fact_capit_amt2_arr(i)
,p_fact_labor_amt1_arr(i),p_fact_labor_amt2_arr(i),p_fact_labor_qty1_arr(i), p_fact_labor_amt3_arr(i)
, p_fact_equip_qty1_arr(i),p_fact_bill_equip_Qty1_arr(i)
----
,0,0,0,0
,p_fact_labor_qty2_arr(i),p_fact_equip_qty2_arr(i),p_fact_labor_amt5_arr(i)
,p_fact_equip_amt5_arr(i) ,p_fact_amount5_arr(i),p_fact_amount4_arr(i)
-------
,p_fact_amount6_arr(i),p_fact_labor_amt4_arr(i),p_fact_equip_amt4_arr(i)
,p_fact_labor_qty3_arr(i),p_fact_equip_qty3_arr(i),p_fact_labor_amt8_arr(i)
----
,p_fact_equip_amt8_arr(i),p_fact_amount8_arr(i),p_fact_amount7_arr(i)
, p_fact_labor_amt7_arr(i),p_fact_equip_amt7_arr(i)
-------
-------
,0,0,0,0,0,0,0,0,0,0,0
,0,0,0,0
FROM DUAL
)
WHERE PROJECT_ID = p_project_id
AND RBS_ELEMENT_ID = p_object_id
AND PROJECT_ELEMENT_ID = p_fact_task_arr(i)
AND RBS_VERSION_ID = p_rbs_cbs_version_id
AND RBS_AGGR_LEVEL = G_RBS_AGGR_LEVEL --'T'
AND WBS_ROLLUP_FLAG = G_WBS_ROLLUP_FLAG -- 'Y'
AND PRG_ROLLUP_FLAG = G_PRG_ROLLUP_FLAG -- 'N'
-- AND RBS_AGGR_LEVEL = 'T'
-- AND WBS_ROLLUP_FLAG = 'Y'
-- AND PRG_ROLLUP_FLAG = 'N'
AND PLAN_VERSION_ID = p_budget_version_id
AND CURRENCY_CODE = p_currency_code
RETURNING PROJECT_ELEMENT_ID
BULK COLLECT INTO l_upd_task_arr;
UPDATE pa_pjt_wbs_rbs_cbs_fact fact /* bulk bind */
SET (
----
RAW_COST, BRDN_COST, REVENUE
, bill_RAW_COST, bill_BRDN_COST, bill_LABOR_RAW_COST, bill_LABOR_BRDN_COST
, bill_LABOR_HRS, EQUIPMENT_RAW_COST, EQUIPMENT_BRDN_COST
--------
, CAPITALIZABLE_RAW_COST, CAPITALIZABLE_BRDN_COST
, LABOR_RAW_COST, LABOR_BRDN_COST, LABOR_HRS, LABOR_REVENUE
, EQUIPMENT_HOURS, BILLABLE_EQUIPMENT_HOURS
----
, SUP_INV_COMMITTED_COST, PO_COMMITTED_COST, PR_COMMITTED_COST, OTH_COMMITTED_COST
, ACT_LABOR_HRS, ACT_EQUIP_HRS, ACT_LABOR_BRDN_COST
, ACT_EQUIP_BRDN_COST, ACT_BRDN_COST, ACT_RAW_COST
-------
, ACT_REVENUE, ACT_LABOR_RAW_COST,ACT_EQUIP_RAW_COST
, ETC_LABOR_HRS, ETC_EQUIP_HRS, ETC_LABOR_BRDN_COST
----
, ETC_EQUIP_BRDN_COST, ETC_BRDN_COST, ETC_RAW_COST
, ETC_LABOR_RAW_COST, ETC_EQUIP_RAW_COST
-----
, CUSTOM1, CUSTOM2, CUSTOM3, CUSTOM4, CUSTOM5, CUSTOM6,CUSTOM7, CUSTOM8, CUSTOM9, CUSTOM10, CUSTOM11
, CUSTOM12, CUSTOM13, CUSTOM14, CUSTOM15 ) =
(
SELECT
----
p_fact_amount1_arr(i)+RAW_COST,p_fact_amount2_arr(i)+BRDN_COST,p_fact_amount3_arr(i)+REVENUE
,p_fact_bill_amt1_arr(i)+bill_RAW_COST,p_fact_bill_amt2_arr(i)+bill_BRDN_COST
,p_fact_bill_labor_amt1_arr(i)+bill_LABOR_RAW_COST,p_fact_bill_labor_amt2_arr(i)+bill_LABOR_BRDN_COST
,p_fact_bill_labor_Qty1_arr(i)+bill_LABOR_HRS,p_fact_equip_amt1_arr(i)+EQUIPMENT_RAW_COST,p_fact_equip_amt2_arr(i)+EQUIPMENT_BRDN_COST
-----
,p_fact_capit_amt1_arr(i)+CAPITALIZABLE_RAW_COST,p_fact_capit_amt2_arr(i)+CAPITALIZABLE_BRDN_COST
,p_fact_labor_amt1_arr(i)+LABOR_RAW_COST,p_fact_labor_amt2_arr(i)+LABOR_BRDN_COST
,p_fact_labor_qty1_arr(i)+LABOR_HRS, p_fact_labor_amt3_arr(i)+LABOR_REVENUE
, p_fact_equip_qty1_arr(i)+EQUIPMENT_HOURS,p_fact_bill_equip_Qty1_arr(i)+BILLABLE_EQUIPMENT_HOURS
----
,0,0,0,0
,p_fact_labor_qty2_arr(i)+ACT_LABOR_HRS,p_fact_equip_qty2_arr(i)+ACT_EQUIP_HRS,p_fact_labor_amt5_arr(i)+ACT_LABOR_BRDN_COST
,p_fact_equip_amt5_arr(i)+ACT_EQUIP_BRDN_COST ,p_fact_amount5_arr(i)+ACT_BRDN_COST,p_fact_amount4_arr(i)+ACT_RAW_COST
-------
,p_fact_amount6_arr(i)+ACT_REVENUE,p_fact_labor_amt4_arr(i)+ACT_LABOR_RAW_COST,p_fact_equip_amt4_arr(i)+ACT_EQUIP_RAW_COST
,p_fact_labor_qty3_arr(i),p_fact_equip_qty3_arr(i),p_fact_labor_amt8_arr(i)
----
,p_fact_equip_amt8_arr(i),p_fact_amount8_arr(i),p_fact_amount7_arr(i)
, p_fact_labor_amt7_arr(i),p_fact_equip_amt7_arr(i)
-------
-------
,0,0,0,0,0,0,0,0,0,0,0
,0,0,0,0
FROM DUAL
)
WHERE PROJECT_ID = p_project_id
AND RBS_ELEMENT_ID = p_object_id
AND PROJECT_ELEMENT_ID = p_fact_task_arr(i)
AND RBS_VERSION_ID = p_rbs_cbs_version_id
AND RBS_AGGR_LEVEL = G_RBS_AGGR_LEVEL --'T'
AND WBS_ROLLUP_FLAG = G_WBS_ROLLUP_FLAG -- 'Y'
AND PRG_ROLLUP_FLAG = G_PRG_ROLLUP_FLAG -- 'N'
-- AND RBS_AGGR_LEVEL = 'T'
-- AND WBS_ROLLUP_FLAG = 'Y'
-- AND PRG_ROLLUP_FLAG = 'N'
AND PLAN_VERSION_ID = p_budget_version_id
AND CURRENCY_CODE = p_currency_code
RETURNING PROJECT_ELEMENT_ID
BULK COLLECT INTO l_upd_task_arr;
UPDATE pa_pjt_wbs_rbs_cbs_fact fact /* bulk bind */
SET (
----
RAW_COST, BRDN_COST, REVENUE
, bill_RAW_COST, bill_BRDN_COST, bill_LABOR_RAW_COST, bill_LABOR_BRDN_COST
, bill_LABOR_HRS, EQUIPMENT_RAW_COST, EQUIPMENT_BRDN_COST
--------
, CAPITALIZABLE_RAW_COST, CAPITALIZABLE_BRDN_COST
, LABOR_RAW_COST, LABOR_BRDN_COST, LABOR_HRS, LABOR_REVENUE
, EQUIPMENT_HOURS, BILLABLE_EQUIPMENT_HOURS
----
, SUP_INV_COMMITTED_COST, PO_COMMITTED_COST, PR_COMMITTED_COST, OTH_COMMITTED_COST
, ACT_LABOR_HRS, ACT_EQUIP_HRS, ACT_LABOR_BRDN_COST
, ACT_EQUIP_BRDN_COST, ACT_BRDN_COST, ACT_RAW_COST
-------
, ACT_REVENUE, ACT_LABOR_RAW_COST,ACT_EQUIP_RAW_COST
, ETC_LABOR_HRS, ETC_EQUIP_HRS, ETC_LABOR_BRDN_COST
----
, ETC_EQUIP_BRDN_COST, ETC_BRDN_COST, ETC_RAW_COST
, ETC_LABOR_RAW_COST, ETC_EQUIP_RAW_COST
-----
, CUSTOM1, CUSTOM2, CUSTOM3, CUSTOM4, CUSTOM5, CUSTOM6,CUSTOM7, CUSTOM8, CUSTOM9, CUSTOM10, CUSTOM11
, CUSTOM12, CUSTOM13, CUSTOM14, CUSTOM15 ) =
(
SELECT
----
p_fact_amount1_arr(i) ,p_fact_amount2_arr(i) ,p_fact_amount3_arr(i)
,p_fact_bill_amt1_arr(i) ,p_fact_bill_amt2_arr(i)
,p_fact_bill_labor_amt1_arr(i) ,p_fact_bill_labor_amt2_arr(i)
,p_fact_bill_labor_Qty1_arr(i) ,p_fact_equip_amt1_arr(i) ,p_fact_equip_amt2_arr(i)
-----
,p_fact_capit_amt1_arr(i) ,p_fact_capit_amt2_arr(i)
,p_fact_labor_amt1_arr(i) ,p_fact_labor_amt2_arr(i)
,p_fact_labor_qty1_arr(i) , p_fact_labor_amt3_arr(i)
, p_fact_equip_qty1_arr(i) ,p_fact_bill_equip_Qty1_arr(i)
----
,0,0,0,0
,p_fact_labor_qty2_arr(i) ,p_fact_equip_qty2_arr(i) ,p_fact_labor_amt5_arr(i)
,p_fact_equip_amt5_arr(i) ,p_fact_amount5_arr(i) ,p_fact_amount4_arr(i)
-------
,p_fact_amount6_arr(i) ,p_fact_labor_amt4_arr(i) ,p_fact_equip_amt4_arr(i)
,p_fact_labor_qty3_arr(i),p_fact_equip_qty3_arr(i),p_fact_labor_amt8_arr(i)
----
,p_fact_equip_amt8_arr(i),p_fact_amount8_arr(i),p_fact_amount7_arr(i)
, p_fact_labor_amt7_arr(i),p_fact_equip_amt7_arr(i)
-------
-------
,0,0,0,0,0,0,0,0,0,0,0
,0,0,0,0
FROM DUAL
)
WHERE PROJECT_ID = p_project_id
AND ( ( cbs_element_id = p_3_level_object_id AND RBS_ELEMENT_ID = p_fact_task_arr(i) and p_fact_slice = 'CBS_TO_WBS_TO_RBS' )
OR ( RBS_ELEMENT_ID = p_fact_task_arr(i) and p_fact_slice = 'WBS_TO_RBS' )
OR ( CBS_ELEMENT_ID = p_fact_task_arr(i) and p_fact_slice = 'WBS_TO_CBS' ) )
AND ( ( RBS_VERSION_ID = p_rbs_cbs_version_id and p_fact_slice = 'WBS_TO_RBS' )
OR ( CBS_VERSION_ID = p_rbs_cbs_version_id and p_fact_slice = 'WBS_TO_CBS' ) )
AND PROJECT_ELEMENT_ID = p_object_id
-- AND RBS_AGGR_LEVEL = 'T'
-- AND WBS_ROLLUP_FLAG = 'Y'
-- AND PRG_ROLLUP_FLAG = 'N'
AND RBS_AGGR_LEVEL = G_RBS_AGGR_LEVEL --'T'
AND WBS_ROLLUP_FLAG = G_WBS_ROLLUP_FLAG -- 'Y'
AND PRG_ROLLUP_FLAG = G_PRG_ROLLUP_FLAG -- 'N'
AND PLAN_VERSION_ID = p_budget_version_id
AND CURRENCY_CODE = p_currency_code
RETURNING RBS_ELEMENT_ID,CBS_ELEMENT_ID
BULK COLLECT INTO l_upd_task_arr,l_upd_task_arr_2;
l_upd_task_arr.delete;
UPDATE pa_pjt_wbs_rbs_cbs_fact fact /* bulk bind */
SET (
----
RAW_COST, BRDN_COST, REVENUE
, bill_RAW_COST, bill_BRDN_COST, bill_LABOR_RAW_COST, bill_LABOR_BRDN_COST
, bill_LABOR_HRS, EQUIPMENT_RAW_COST, EQUIPMENT_BRDN_COST
--------
, CAPITALIZABLE_RAW_COST, CAPITALIZABLE_BRDN_COST
, LABOR_RAW_COST, LABOR_BRDN_COST, LABOR_HRS, LABOR_REVENUE
, EQUIPMENT_HOURS, BILLABLE_EQUIPMENT_HOURS
----
, SUP_INV_COMMITTED_COST, PO_COMMITTED_COST, PR_COMMITTED_COST, OTH_COMMITTED_COST
, ACT_LABOR_HRS, ACT_EQUIP_HRS, ACT_LABOR_BRDN_COST
, ACT_EQUIP_BRDN_COST, ACT_BRDN_COST, ACT_RAW_COST
-------
, ACT_REVENUE, ACT_LABOR_RAW_COST,ACT_EQUIP_RAW_COST
, ETC_LABOR_HRS, ETC_EQUIP_HRS, ETC_LABOR_BRDN_COST
----
, ETC_EQUIP_BRDN_COST, ETC_BRDN_COST, ETC_RAW_COST
, ETC_LABOR_RAW_COST, ETC_EQUIP_RAW_COST
-----
, CUSTOM1, CUSTOM2, CUSTOM3, CUSTOM4, CUSTOM5, CUSTOM6,CUSTOM7, CUSTOM8, CUSTOM9, CUSTOM10, CUSTOM11
, CUSTOM12, CUSTOM13, CUSTOM14, CUSTOM15 ) =
(
SELECT
----
p_fact_amount1_arr(i)+RAW_COST,p_fact_amount2_arr(i)+BRDN_COST,p_fact_amount3_arr(i)+REVENUE
,p_fact_bill_amt1_arr(i)+bill_RAW_COST,p_fact_bill_amt2_arr(i)+bill_BRDN_COST
,p_fact_bill_labor_amt1_arr(i)+bill_LABOR_RAW_COST,p_fact_bill_labor_amt2_arr(i)+bill_LABOR_BRDN_COST
,p_fact_bill_labor_Qty1_arr(i)+bill_LABOR_HRS,p_fact_equip_amt1_arr(i)+EQUIPMENT_RAW_COST,p_fact_equip_amt2_arr(i)+EQUIPMENT_BRDN_COST
-----
,p_fact_capit_amt1_arr(i)+CAPITALIZABLE_RAW_COST,p_fact_capit_amt2_arr(i)+CAPITALIZABLE_BRDN_COST
,p_fact_labor_amt1_arr(i)+LABOR_RAW_COST,p_fact_labor_amt2_arr(i)+LABOR_BRDN_COST
,p_fact_labor_qty1_arr(i)+LABOR_HRS, p_fact_labor_amt3_arr(i)+LABOR_REVENUE
, p_fact_equip_qty1_arr(i)+EQUIPMENT_HOURS,p_fact_bill_equip_Qty1_arr(i)+BILLABLE_EQUIPMENT_HOURS
----
,0,0,0,0
,p_fact_labor_qty2_arr(i)+ACT_LABOR_HRS,p_fact_equip_qty2_arr(i)+ACT_EQUIP_HRS,p_fact_labor_amt5_arr(i)+ACT_LABOR_BRDN_COST
,p_fact_equip_amt5_arr(i)+ACT_EQUIP_BRDN_COST ,p_fact_amount5_arr(i)+ACT_BRDN_COST,p_fact_amount4_arr(i)+ACT_RAW_COST
-------
,p_fact_amount6_arr(i)+ACT_REVENUE,p_fact_labor_amt4_arr(i)+ACT_LABOR_RAW_COST,p_fact_equip_amt4_arr(i)+ACT_EQUIP_RAW_COST
,p_fact_labor_qty3_arr(i),p_fact_equip_qty3_arr(i),p_fact_labor_amt8_arr(i)
----
,p_fact_equip_amt8_arr(i),p_fact_amount8_arr(i),p_fact_amount7_arr(i)
, p_fact_labor_amt7_arr(i),p_fact_equip_amt7_arr(i)
-------
-------
,0,0,0,0,0,0,0,0,0,0,0
,0,0,0,0
FROM DUAL
)
WHERE PROJECT_ID = p_project_id
AND ( ( cbs_element_id = p_3_level_object_id and RBS_ELEMENT_ID = p_fact_task_arr(i) and p_fact_slice = 'CBS_TO_WBS_TO_RBS' )
OR ( RBS_ELEMENT_ID = p_fact_task_arr(i) and p_fact_slice = 'WBS_TO_RBS' )
OR ( CBS_ELEMENT_ID = p_fact_task_arr(i) and p_fact_slice = 'WBS_TO_CBS' ) )
AND ( ( RBS_VERSION_ID = p_rbs_cbs_version_id and p_fact_slice = 'WBS_TO_RBS' )
OR ( CBS_VERSION_ID = p_rbs_cbs_version_id and p_fact_slice = 'WBS_TO_CBS' ) )
AND PROJECT_ELEMENT_ID = p_object_id
AND RBS_AGGR_LEVEL = G_RBS_AGGR_LEVEL --'T'
AND WBS_ROLLUP_FLAG = G_WBS_ROLLUP_FLAG -- 'Y'
AND PRG_ROLLUP_FLAG = G_PRG_ROLLUP_FLAG -- 'N'
-- AND RBS_AGGR_LEVEL = 'T'
-- AND WBS_ROLLUP_FLAG = 'Y'
-- AND PRG_ROLLUP_FLAG = 'N'
AND PLAN_VERSION_ID = p_budget_version_id
AND CURRENCY_CODE = p_currency_code
RETURNING RBS_ELEMENT_ID,CBS_ELEMENT_ID
BULK COLLECT INTO l_upd_task_arr,l_upd_task_arr_2;
l_upd_task_arr.delete;
UPDATE pa_pjt_wbs_fact fact /* bulk bind */
SET (
----
RAW_COST, BRDN_COST, REVENUE
, bill_RAW_COST, bill_BRDN_COST, bill_LABOR_RAW_COST, bill_LABOR_BRDN_COST
, bill_LABOR_HRS, EQUIPMENT_RAW_COST, EQUIPMENT_BRDN_COST
--------
, CAPITALIZABLE_RAW_COST, CAPITALIZABLE_BRDN_COST
, LABOR_RAW_COST, LABOR_BRDN_COST, LABOR_HRS, LABOR_REVENUE
, EQUIPMENT_HOURS, BILLABLE_EQUIPMENT_HOURS
----
, SUP_INV_COMMITTED_COST, PO_COMMITTED_COST, PR_COMMITTED_COST, OTH_COMMITTED_COST
, ACT_LABOR_HRS, ACT_EQUIP_HRS, ACT_LABOR_BRDN_COST
, ACT_EQUIP_BRDN_COST, ACT_BRDN_COST, ACT_RAW_COST
-------
, ACT_REVENUE, ACT_LABOR_RAW_COST,ACT_EQUIP_RAW_COST
, ETC_LABOR_HRS, ETC_EQUIP_HRS, ETC_LABOR_BRDN_COST
----
, ETC_EQUIP_BRDN_COST, ETC_BRDN_COST, ETC_RAW_COST
, ETC_LABOR_RAW_COST, ETC_EQUIP_RAW_COST
-----
, CUSTOM1, CUSTOM2, CUSTOM3, CUSTOM4, CUSTOM5, CUSTOM6,CUSTOM7, CUSTOM8, CUSTOM9, CUSTOM10, CUSTOM11
, CUSTOM12, CUSTOM13, CUSTOM14, CUSTOM15 ) =
(
SELECT
----
p_fact_amount1_arr(i),p_fact_amount2_arr(i),p_fact_amount3_arr(i)
,p_fact_bill_amt1_arr(i),p_fact_bill_amt2_arr(i),p_fact_bill_labor_amt1_arr(i),p_fact_bill_labor_amt2_arr(i)
,p_fact_bill_labor_Qty1_arr(i),p_fact_equip_amt1_arr(i),p_fact_equip_amt2_arr(i)
-----
,p_fact_capit_amt1_arr(i),p_fact_capit_amt2_arr(i)
,p_fact_labor_amt1_arr(i),p_fact_labor_amt2_arr(i),p_fact_labor_qty1_arr(i), p_fact_labor_amt3_arr(i)
, p_fact_equip_qty1_arr(i),p_fact_bill_equip_Qty1_arr(i)
----
,0,0,0,0
,p_fact_labor_qty2_arr(i),p_fact_equip_qty2_arr(i),p_fact_labor_amt5_arr(i)
,p_fact_equip_amt5_arr(i) ,p_fact_amount5_arr(i),p_fact_amount4_arr(i)
-------
,p_fact_amount6_arr(i),p_fact_labor_amt4_arr(i),p_fact_equip_amt4_arr(i)
,p_fact_labor_qty3_arr(i),p_fact_equip_qty3_arr(i),p_fact_labor_amt8_arr(i)
----
,p_fact_equip_amt8_arr(i),p_fact_amount8_arr(i),p_fact_amount7_arr(i)
, p_fact_labor_amt7_arr(i),p_fact_equip_amt7_arr(i)
-------
-------
,0,0,0,0,0,0,0,0,0,0,0
,0,0,0,0
FROM DUAL
)
WHERE PROJECT_ID = p_project_id
AND PROJECT_ELEMENT_ID = p_fact_task_arr(i)
-- AND RBS_AGGR_LEVEL = 'T'
-- AND WBS_ROLLUP_FLAG = 'Y'
-- AND PRG_ROLLUP_FLAG = 'N'
-- AND RBS_AGGR_LEVEL = G_RBS_AGGR_LEVEL --'T'
AND WBS_ROLLUP_FLAG = G_WBS_ROLLUP_FLAG -- 'Y'
AND PRG_ROLLUP_FLAG = G_PRG_ROLLUP_FLAG -- 'N'
AND PLAN_VERSION_ID = p_budget_version_id
AND CURRENCY_CODE = p_currency_code
RETURNING PROJECT_ELEMENT_ID
BULK COLLECT INTO l_upd_task_arr;
UPDATE pa_pjt_wbs_fact fact /* bulk bind */
SET (
----
RAW_COST, BRDN_COST, REVENUE
, bill_RAW_COST, bill_BRDN_COST, bill_LABOR_RAW_COST, bill_LABOR_BRDN_COST
, bill_LABOR_HRS, EQUIPMENT_RAW_COST, EQUIPMENT_BRDN_COST
--------
, CAPITALIZABLE_RAW_COST, CAPITALIZABLE_BRDN_COST
, LABOR_RAW_COST, LABOR_BRDN_COST, LABOR_HRS, LABOR_REVENUE
, EQUIPMENT_HOURS, BILLABLE_EQUIPMENT_HOURS
----
, SUP_INV_COMMITTED_COST, PO_COMMITTED_COST, PR_COMMITTED_COST, OTH_COMMITTED_COST
, ACT_LABOR_HRS, ACT_EQUIP_HRS, ACT_LABOR_BRDN_COST
, ACT_EQUIP_BRDN_COST, ACT_BRDN_COST, ACT_RAW_COST
-------
, ACT_REVENUE, ACT_LABOR_RAW_COST,ACT_EQUIP_RAW_COST
, ETC_LABOR_HRS, ETC_EQUIP_HRS, ETC_LABOR_BRDN_COST
----
, ETC_EQUIP_BRDN_COST, ETC_BRDN_COST, ETC_RAW_COST
, ETC_LABOR_RAW_COST, ETC_EQUIP_RAW_COST
-----
, CUSTOM1, CUSTOM2, CUSTOM3, CUSTOM4, CUSTOM5, CUSTOM6,CUSTOM7, CUSTOM8, CUSTOM9, CUSTOM10, CUSTOM11
, CUSTOM12, CUSTOM13, CUSTOM14, CUSTOM15 ) =
(
SELECT
----
p_fact_amount1_arr(i)+RAW_COST,p_fact_amount2_arr(i)+BRDN_COST,p_fact_amount3_arr(i)+REVENUE
,p_fact_bill_amt1_arr(i)+bill_RAW_COST,p_fact_bill_amt2_arr(i)+bill_BRDN_COST
,p_fact_bill_labor_amt1_arr(i)+bill_LABOR_RAW_COST,p_fact_bill_labor_amt2_arr(i)+bill_LABOR_BRDN_COST
,p_fact_bill_labor_Qty1_arr(i)+bill_LABOR_HRS,p_fact_equip_amt1_arr(i)+EQUIPMENT_RAW_COST,p_fact_equip_amt2_arr(i)+EQUIPMENT_BRDN_COST
-----
,p_fact_capit_amt1_arr(i)+CAPITALIZABLE_RAW_COST,p_fact_capit_amt2_arr(i)+CAPITALIZABLE_BRDN_COST
,p_fact_labor_amt1_arr(i)+LABOR_RAW_COST,p_fact_labor_amt2_arr(i)+LABOR_BRDN_COST
,p_fact_labor_qty1_arr(i)+LABOR_HRS, p_fact_labor_amt3_arr(i)+LABOR_REVENUE
, p_fact_equip_qty1_arr(i)+EQUIPMENT_HOURS,p_fact_bill_equip_Qty1_arr(i)+BILLABLE_EQUIPMENT_HOURS
----
,0,0,0,0
,p_fact_labor_qty2_arr(i)+ACT_LABOR_HRS,p_fact_equip_qty2_arr(i)+ACT_EQUIP_HRS,p_fact_labor_amt5_arr(i)+ACT_LABOR_BRDN_COST
,p_fact_equip_amt5_arr(i)+ACT_EQUIP_BRDN_COST ,p_fact_amount5_arr(i)+ACT_BRDN_COST,p_fact_amount4_arr(i)+ACT_RAW_COST
-------
,p_fact_amount6_arr(i)+ACT_REVENUE,p_fact_labor_amt4_arr(i)+ACT_LABOR_RAW_COST,p_fact_equip_amt4_arr(i)+ACT_EQUIP_RAW_COST
,p_fact_labor_qty3_arr(i),p_fact_equip_qty3_arr(i),p_fact_labor_amt8_arr(i)
----
,p_fact_equip_amt8_arr(i),p_fact_amount8_arr(i),p_fact_amount7_arr(i)
, p_fact_labor_amt7_arr(i),p_fact_equip_amt7_arr(i)
-------
-------
,0,0,0,0,0,0,0,0,0,0,0
,0,0,0,0
FROM DUAL
)
WHERE PROJECT_ID = p_project_id
AND PROJECT_ELEMENT_ID = p_fact_task_arr(i)
-- AND RBS_AGGR_LEVEL = 'T'
-- AND WBS_ROLLUP_FLAG = 'Y'
-- AND PRG_ROLLUP_FLAG = 'N'
-- AND RBS_AGGR_LEVEL = G_RBS_AGGR_LEVEL --'T'
AND WBS_ROLLUP_FLAG = G_WBS_ROLLUP_FLAG -- 'Y'
AND PRG_ROLLUP_FLAG = G_PRG_ROLLUP_FLAG -- 'N'
AND PLAN_VERSION_ID = p_budget_version_id
AND CURRENCY_CODE = p_currency_code
RETURNING PROJECT_ELEMENT_ID
BULK COLLECT INTO l_upd_task_arr;
UPDATE pa_pjt_rbs_cbs_fact fact /* bulk bind */
SET (
----
RAW_COST, BRDN_COST, REVENUE
, bill_RAW_COST, bill_BRDN_COST, bill_LABOR_RAW_COST, bill_LABOR_BRDN_COST
, bill_LABOR_HRS, EQUIPMENT_RAW_COST, EQUIPMENT_BRDN_COST
--------
, CAPITALIZABLE_RAW_COST, CAPITALIZABLE_BRDN_COST
, LABOR_RAW_COST, LABOR_BRDN_COST, LABOR_HRS, LABOR_REVENUE
, EQUIPMENT_HOURS, BILLABLE_EQUIPMENT_HOURS
----
, SUP_INV_COMMITTED_COST, PO_COMMITTED_COST, PR_COMMITTED_COST, OTH_COMMITTED_COST
, ACT_LABOR_HRS, ACT_EQUIP_HRS, ACT_LABOR_BRDN_COST
, ACT_EQUIP_BRDN_COST, ACT_BRDN_COST, ACT_RAW_COST
-------
, ACT_REVENUE, ACT_LABOR_RAW_COST,ACT_EQUIP_RAW_COST
, ETC_LABOR_HRS, ETC_EQUIP_HRS, ETC_LABOR_BRDN_COST
----
, ETC_EQUIP_BRDN_COST, ETC_BRDN_COST, ETC_RAW_COST
, ETC_LABOR_RAW_COST, ETC_EQUIP_RAW_COST
-----
, CUSTOM1, CUSTOM2, CUSTOM3, CUSTOM4, CUSTOM5, CUSTOM6,CUSTOM7, CUSTOM8, CUSTOM9, CUSTOM10, CUSTOM11
, CUSTOM12, CUSTOM13, CUSTOM14, CUSTOM15 ) =
(
SELECT
----
p_fact_amount1_arr(i),p_fact_amount2_arr(i),p_fact_amount3_arr(i)
,p_fact_bill_amt1_arr(i),p_fact_bill_amt2_arr(i),p_fact_bill_labor_amt1_arr(i),p_fact_bill_labor_amt2_arr(i)
,p_fact_bill_labor_Qty1_arr(i),p_fact_equip_amt1_arr(i),p_fact_equip_amt2_arr(i)
-----
,p_fact_capit_amt1_arr(i),p_fact_capit_amt2_arr(i)
,p_fact_labor_amt1_arr(i),p_fact_labor_amt2_arr(i),p_fact_labor_qty1_arr(i), p_fact_labor_amt3_arr(i)
, p_fact_equip_qty1_arr(i),p_fact_bill_equip_Qty1_arr(i)
----
,0,0,0,0
,p_fact_labor_qty2_arr(i),p_fact_equip_qty2_arr(i),p_fact_labor_amt5_arr(i)
,p_fact_equip_amt5_arr(i) ,p_fact_amount5_arr(i),p_fact_amount4_arr(i)
-------
,p_fact_amount6_arr(i),p_fact_labor_amt4_arr(i),p_fact_equip_amt4_arr(i)
,p_fact_labor_qty3_arr(i),p_fact_equip_qty3_arr(i),p_fact_labor_amt8_arr(i)
----
,p_fact_equip_amt8_arr(i),p_fact_amount8_arr(i),p_fact_amount7_arr(i)
, p_fact_labor_amt7_arr(i),p_fact_equip_amt7_arr(i)
-------
-------
,0,0,0,0,0,0,0,0,0,0,0
,0,0,0,0
FROM DUAL
)
WHERE PROJECT_ID = p_project_id
AND RBS_CBS_ELEMENT_ID = p_fact_task_arr(i)
AND RBS_CBS_VERSION_ID = p_rbs_cbs_version_id
AND ROLLUP_TYPE = p_fact_slice
-- AND RBS_AGGR_LEVEL = 'T'
-- AND WBS_ROLLUP_FLAG = 'Y'
-- AND PRG_ROLLUP_FLAG = 'N'
AND RBS_AGGR_LEVEL = G_RBS_AGGR_LEVEL --'T'
-- AND WBS_ROLLUP_FLAG = G_WBS_ROLLUP_FLAG -- 'Y'
AND PRG_ROLLUP_FLAG = G_PRG_ROLLUP_FLAG -- 'N'
AND PLAN_VERSION_ID = p_budget_version_id
AND CURRENCY_CODE = p_currency_code
RETURNING RBS_CBS_ELEMENT_ID
BULK COLLECT INTO l_upd_task_arr;
UPDATE pa_pjt_rbs_cbs_fact fact /* bulk bind */
SET (
----
RAW_COST, BRDN_COST, REVENUE
, bill_RAW_COST, bill_BRDN_COST, bill_LABOR_RAW_COST, bill_LABOR_BRDN_COST
, bill_LABOR_HRS, EQUIPMENT_RAW_COST, EQUIPMENT_BRDN_COST
--------
, CAPITALIZABLE_RAW_COST, CAPITALIZABLE_BRDN_COST
, LABOR_RAW_COST, LABOR_BRDN_COST, LABOR_HRS, LABOR_REVENUE
, EQUIPMENT_HOURS, BILLABLE_EQUIPMENT_HOURS
----
, SUP_INV_COMMITTED_COST, PO_COMMITTED_COST, PR_COMMITTED_COST, OTH_COMMITTED_COST
, ACT_LABOR_HRS, ACT_EQUIP_HRS, ACT_LABOR_BRDN_COST
, ACT_EQUIP_BRDN_COST, ACT_BRDN_COST, ACT_RAW_COST
-------
, ACT_REVENUE, ACT_LABOR_RAW_COST,ACT_EQUIP_RAW_COST
, ETC_LABOR_HRS, ETC_EQUIP_HRS, ETC_LABOR_BRDN_COST
----
, ETC_EQUIP_BRDN_COST, ETC_BRDN_COST, ETC_RAW_COST
, ETC_LABOR_RAW_COST, ETC_EQUIP_RAW_COST
-----
, CUSTOM1, CUSTOM2, CUSTOM3, CUSTOM4, CUSTOM5, CUSTOM6,CUSTOM7, CUSTOM8, CUSTOM9, CUSTOM10, CUSTOM11
, CUSTOM12, CUSTOM13, CUSTOM14, CUSTOM15 ) =
(
SELECT
----
p_fact_amount1_arr(i)+RAW_COST,p_fact_amount2_arr(i)+BRDN_COST,p_fact_amount3_arr(i)+REVENUE
,p_fact_bill_amt1_arr(i)+bill_RAW_COST,p_fact_bill_amt2_arr(i)+bill_BRDN_COST
,p_fact_bill_labor_amt1_arr(i)+bill_LABOR_RAW_COST,p_fact_bill_labor_amt2_arr(i)+bill_LABOR_BRDN_COST
,p_fact_bill_labor_Qty1_arr(i)+bill_LABOR_HRS,p_fact_equip_amt1_arr(i)+EQUIPMENT_RAW_COST,p_fact_equip_amt2_arr(i)+EQUIPMENT_BRDN_COST
-----
,p_fact_capit_amt1_arr(i)+CAPITALIZABLE_RAW_COST,p_fact_capit_amt2_arr(i)+CAPITALIZABLE_BRDN_COST
,p_fact_labor_amt1_arr(i)+LABOR_RAW_COST,p_fact_labor_amt2_arr(i)+LABOR_BRDN_COST
,p_fact_labor_qty1_arr(i)+LABOR_HRS, p_fact_labor_amt3_arr(i)+LABOR_REVENUE
, p_fact_equip_qty1_arr(i)+EQUIPMENT_HOURS,p_fact_bill_equip_Qty1_arr(i)+BILLABLE_EQUIPMENT_HOURS
----
,0,0,0,0
,p_fact_labor_qty2_arr(i)+ACT_LABOR_HRS,p_fact_equip_qty2_arr(i)+ACT_EQUIP_HRS,p_fact_labor_amt5_arr(i)+ACT_LABOR_BRDN_COST
,p_fact_equip_amt5_arr(i)+ACT_EQUIP_BRDN_COST ,p_fact_amount5_arr(i)+ACT_BRDN_COST,p_fact_amount4_arr(i)+ACT_RAW_COST
-------
,p_fact_amount6_arr(i)+ACT_REVENUE,p_fact_labor_amt4_arr(i)+ACT_LABOR_RAW_COST,p_fact_equip_amt4_arr(i)+ACT_EQUIP_RAW_COST
,p_fact_labor_qty3_arr(i),p_fact_equip_qty3_arr(i),p_fact_labor_amt8_arr(i)
----
,p_fact_equip_amt8_arr(i),p_fact_amount8_arr(i),p_fact_amount7_arr(i)
, p_fact_labor_amt7_arr(i),p_fact_equip_amt7_arr(i)
-------
-------
,0,0,0,0,0,0,0,0,0,0,0
,0,0,0,0
FROM DUAL
)
WHERE PROJECT_ID = p_project_id
AND RBS_CBS_ELEMENT_ID = p_fact_task_arr(i)
AND RBS_CBS_VERSION_ID = p_rbs_cbs_version_id
AND ROLLUP_TYPE = p_fact_slice
-- AND RBS_AGGR_LEVEL = 'T'
-- AND WBS_ROLLUP_FLAG = 'Y'
-- AND PRG_ROLLUP_FLAG = 'N'
AND RBS_AGGR_LEVEL = G_RBS_AGGR_LEVEL --'T'
-- AND WBS_ROLLUP_FLAG = G_WBS_ROLLUP_FLAG -- 'Y'
AND PRG_ROLLUP_FLAG = G_PRG_ROLLUP_FLAG -- 'N'
AND PLAN_VERSION_ID = p_budget_version_id
AND CURRENCY_CODE = p_currency_code
RETURNING RBS_CBS_ELEMENT_ID
BULK COLLECT INTO l_upd_task_arr;
l_ins_task_flag_arr.delete;
ppr_log('UPDATE_INSERT p_fact_slice '||p_fact_slice||'.... l_upd_task_arr '||l_upd_task_arr.count||' l_ins_task_flag_arr '||l_ins_task_flag_arr.count||' p_fact_task_arr.count '||p_fact_task_arr.count );
INSERT INTO pa_pjt_wbs_rbs_cbs_fact (
----
PROJECT_ID , PROJECT_ELEMENT_ID
, RBS_AGGR_LEVEL , WBS_ROLLUP_FLAG, PRG_ROLLUP_FLAG,CURRENCY_CODE --,RBS_ELEMENT_ID, RBS_VERSION_ID
, PLAN_VERSION_ID, LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN
----
,RAW_COST, BRDN_COST, REVENUE
, bill_RAW_COST, bill_BRDN_COST, bill_LABOR_RAW_COST, bill_LABOR_BRDN_COST
, bill_LABOR_HRS, EQUIPMENT_RAW_COST, EQUIPMENT_BRDN_COST
--------
, CAPITALIZABLE_RAW_COST, CAPITALIZABLE_BRDN_COST
, LABOR_RAW_COST, LABOR_BRDN_COST, LABOR_HRS, LABOR_REVENUE
, EQUIPMENT_HOURS, BILLABLE_EQUIPMENT_HOURS
----
, SUP_INV_COMMITTED_COST, PO_COMMITTED_COST, PR_COMMITTED_COST, OTH_COMMITTED_COST
, ACT_LABOR_HRS, ACT_EQUIP_HRS, ACT_LABOR_BRDN_COST
, ACT_EQUIP_BRDN_COST, ACT_BRDN_COST, ACT_RAW_COST
-------
, ACT_REVENUE, ACT_LABOR_RAW_COST,ACT_EQUIP_RAW_COST
, ETC_LABOR_HRS, ETC_EQUIP_HRS, ETC_LABOR_BRDN_COST
----
, ETC_EQUIP_BRDN_COST, ETC_BRDN_COST, ETC_RAW_COST
, ETC_LABOR_RAW_COST, ETC_EQUIP_RAW_COST
-----
, CUSTOM1, CUSTOM2, CUSTOM3, CUSTOM4, CUSTOM5, CUSTOM6,CUSTOM7, CUSTOM8, CUSTOM9, CUSTOM10, CUSTOM11
, CUSTOM12, CUSTOM13, CUSTOM14, CUSTOM15
----
, RBS_ELEMENT_ID, RBS_VERSION_ID
, CBS_ELEMENT_ID, CBS_VERSION_ID
)
SELECT
p_project_id ,DECODE( p_fact_slice,'CBS_TO_WBS',p_fact_task_arr(i),'WBS_TO_CBS_TO_RBS',p_3_level_object_id ,-1)
-- ,'T','Y','N',p_currency_code --,0,0
, G_RBS_AGGR_LEVEL , G_WBS_ROLLUP_FLAG, G_PRG_ROLLUP_FLAG,p_currency_code --,0,0
,p_budget_version_id , sysdate, -1 , sysdate , -1, -1
----
, p_fact_amount1_arr(i),p_fact_amount2_arr(i),p_fact_amount3_arr(i)
,p_fact_bill_amt1_arr(i),p_fact_bill_amt2_arr(i),p_fact_bill_labor_amt1_arr(i),p_fact_bill_labor_amt2_arr(i)
,p_fact_bill_labor_Qty1_arr(i),p_fact_equip_amt1_arr(i),p_fact_equip_amt2_arr(i)
-----
,p_fact_capit_amt1_arr(i),p_fact_capit_amt2_arr(i)
,p_fact_labor_amt1_arr(i),p_fact_labor_amt2_arr(i),p_fact_labor_qty1_arr(i), p_fact_labor_amt3_arr(i)
, p_fact_equip_qty1_arr(i),p_fact_bill_equip_Qty1_arr(i)
----
,0,0,0,0
,p_fact_labor_qty2_arr(i),p_fact_equip_qty2_arr(i),p_fact_labor_amt5_arr(i)
,p_fact_equip_amt5_arr(i) ,p_fact_amount5_arr(i),p_fact_amount4_arr(i)
-------
,p_fact_amount6_arr(i),p_fact_labor_amt4_arr(i),p_fact_equip_amt4_arr(i)
,p_fact_labor_qty3_arr(i),p_fact_equip_qty3_arr(i),p_fact_labor_amt8_arr(i)
----
,p_fact_equip_amt8_arr(i),p_fact_amount8_arr(i),p_fact_amount7_arr(i)
, p_fact_labor_amt7_arr(i),p_fact_equip_amt7_arr(i)
-------
-------
,0,0,0,0,0,0,0,0,0,0,0
,0,0,0,0
,DECODE( p_fact_slice,'WBS_TO_CBS_TO_RBS',p_fact_task_arr(i),'CBS_TO_RBS',p_fact_task_arr(i),-1),decode(p_fact_slice,'WBS_TO_CBS_TO_RBS',p_rbs_cbs_version_id,'CBS_TO_RBS',p_rbs_cbs_version_id,-1)
,p_object_id, decode(p_fact_slice,'CBS_TO_WBS',p_rbs_cbs_version_id,'CBS_TO_RBS',p_rbs_cbs_version_id_2,'WBS_TO_CBS_TO_RBS',p_rbs_cbs_version_id_2,-1)
from dual
where l_ins_task_flag_arr(i) = 'Y';
INSERT INTO pa_pjt_wbs_rbs_cbs_fact (
----
PROJECT_ID , PROJECT_ELEMENT_ID
, RBS_AGGR_LEVEL , WBS_ROLLUP_FLAG, PRG_ROLLUP_FLAG,CURRENCY_CODE --,RBS_ELEMENT_ID, RBS_VERSION_ID
, PLAN_VERSION_ID, LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN
----
,RAW_COST, BRDN_COST, REVENUE
, bill_RAW_COST, bill_BRDN_COST, bill_LABOR_RAW_COST, bill_LABOR_BRDN_COST
, bill_LABOR_HRS, EQUIPMENT_RAW_COST, EQUIPMENT_BRDN_COST
--------
, CAPITALIZABLE_RAW_COST, CAPITALIZABLE_BRDN_COST
, LABOR_RAW_COST, LABOR_BRDN_COST, LABOR_HRS, LABOR_REVENUE
, EQUIPMENT_HOURS, BILLABLE_EQUIPMENT_HOURS
----
, SUP_INV_COMMITTED_COST, PO_COMMITTED_COST, PR_COMMITTED_COST, OTH_COMMITTED_COST
, ACT_LABOR_HRS, ACT_EQUIP_HRS, ACT_LABOR_BRDN_COST
, ACT_EQUIP_BRDN_COST, ACT_BRDN_COST, ACT_RAW_COST
-------
, ACT_REVENUE, ACT_LABOR_RAW_COST,ACT_EQUIP_RAW_COST
, ETC_LABOR_HRS, ETC_EQUIP_HRS, ETC_LABOR_BRDN_COST
----
, ETC_EQUIP_BRDN_COST, ETC_BRDN_COST, ETC_RAW_COST
, ETC_LABOR_RAW_COST, ETC_EQUIP_RAW_COST
-----
, CUSTOM1, CUSTOM2, CUSTOM3, CUSTOM4, CUSTOM5, CUSTOM6,CUSTOM7, CUSTOM8, CUSTOM9, CUSTOM10, CUSTOM11
, CUSTOM12, CUSTOM13, CUSTOM14, CUSTOM15
----
, RBS_ELEMENT_ID, RBS_VERSION_ID
, CBS_ELEMENT_ID, CBS_VERSION_ID
)
SELECT
p_project_id ,p_fact_task_arr(i)
--,'T','Y','N',p_currency_code --,0,0
, G_RBS_AGGR_LEVEL , G_WBS_ROLLUP_FLAG, G_PRG_ROLLUP_FLAG,p_currency_code --,0,0
,p_budget_version_id , sysdate, -1 , sysdate , -1, -1
----
----
, p_fact_amount1_arr(i),p_fact_amount2_arr(i),p_fact_amount3_arr(i)
,p_fact_bill_amt1_arr(i),p_fact_bill_amt2_arr(i),p_fact_bill_labor_amt1_arr(i),p_fact_bill_labor_amt2_arr(i)
,p_fact_bill_labor_Qty1_arr(i),p_fact_equip_amt1_arr(i),p_fact_equip_amt2_arr(i)
-----
,p_fact_capit_amt1_arr(i),p_fact_capit_amt2_arr(i)
,p_fact_labor_amt1_arr(i),p_fact_labor_amt2_arr(i),p_fact_labor_qty1_arr(i), p_fact_labor_amt3_arr(i)
, p_fact_equip_qty1_arr(i),p_fact_bill_equip_Qty1_arr(i)
----
,0,0,0,0
,p_fact_labor_qty2_arr(i),p_fact_equip_qty2_arr(i),p_fact_labor_amt5_arr(i)
,p_fact_equip_amt5_arr(i) ,p_fact_amount5_arr(i),p_fact_amount4_arr(i)
-------
,p_fact_amount6_arr(i),p_fact_labor_amt4_arr(i),p_fact_equip_amt4_arr(i)
,p_fact_labor_qty3_arr(i),p_fact_equip_qty3_arr(i),p_fact_labor_amt8_arr(i)
----
,p_fact_equip_amt8_arr(i),p_fact_amount8_arr(i),p_fact_amount7_arr(i)
, p_fact_labor_amt7_arr(i),p_fact_equip_amt7_arr(i)
-------
-------
,0,0,0,0,0,0,0,0,0,0,0
,0,0,0,0
,p_object_id, p_rbs_cbs_version_id
,-1,-1
from dual
where l_ins_task_flag_arr(i) = 'Y';
INSERT INTO pa_pjt_wbs_rbs_cbs_fact (
----
PROJECT_ID , PROJECT_ELEMENT_ID
, RBS_AGGR_LEVEL , WBS_ROLLUP_FLAG, PRG_ROLLUP_FLAG,CURRENCY_CODE --,RBS_ELEMENT_ID, RBS_VERSION_ID
, PLAN_VERSION_ID, LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN
----
,RAW_COST, BRDN_COST, REVENUE
, bill_RAW_COST, bill_BRDN_COST, bill_LABOR_RAW_COST, bill_LABOR_BRDN_COST
, bill_LABOR_HRS, EQUIPMENT_RAW_COST, EQUIPMENT_BRDN_COST
--------
, CAPITALIZABLE_RAW_COST, CAPITALIZABLE_BRDN_COST
, LABOR_RAW_COST, LABOR_BRDN_COST, LABOR_HRS, LABOR_REVENUE
, EQUIPMENT_HOURS, BILLABLE_EQUIPMENT_HOURS
----
, SUP_INV_COMMITTED_COST, PO_COMMITTED_COST, PR_COMMITTED_COST, OTH_COMMITTED_COST
, ACT_LABOR_HRS, ACT_EQUIP_HRS, ACT_LABOR_BRDN_COST
, ACT_EQUIP_BRDN_COST, ACT_BRDN_COST, ACT_RAW_COST
-------
, ACT_REVENUE, ACT_LABOR_RAW_COST,ACT_EQUIP_RAW_COST
, ETC_LABOR_HRS, ETC_EQUIP_HRS, ETC_LABOR_BRDN_COST
----
, ETC_EQUIP_BRDN_COST, ETC_BRDN_COST, ETC_RAW_COST
, ETC_LABOR_RAW_COST, ETC_EQUIP_RAW_COST
-----
, CUSTOM1, CUSTOM2, CUSTOM3, CUSTOM4, CUSTOM5, CUSTOM6,CUSTOM7, CUSTOM8, CUSTOM9, CUSTOM10, CUSTOM11
, CUSTOM12, CUSTOM13, CUSTOM14, CUSTOM15
----
, RBS_ELEMENT_ID, RBS_VERSION_ID
, CBS_ELEMENT_ID, CBS_VERSION_ID
)
SELECT
p_project_id ,p_object_id
-- ,'T','Y','N',p_currency_code --,0,0
, G_RBS_AGGR_LEVEL , G_WBS_ROLLUP_FLAG, G_PRG_ROLLUP_FLAG,p_currency_code --,0,0
,p_budget_version_id , sysdate, -1 , sysdate , -1, -1
----
----
, p_fact_amount1_arr(i),p_fact_amount2_arr(i),p_fact_amount3_arr(i)
,p_fact_bill_amt1_arr(i),p_fact_bill_amt2_arr(i),p_fact_bill_labor_amt1_arr(i),p_fact_bill_labor_amt2_arr(i)
,p_fact_bill_labor_Qty1_arr(i),p_fact_equip_amt1_arr(i),p_fact_equip_amt2_arr(i)
-----
,p_fact_capit_amt1_arr(i),p_fact_capit_amt2_arr(i)
,p_fact_labor_amt1_arr(i),p_fact_labor_amt2_arr(i),p_fact_labor_qty1_arr(i), p_fact_labor_amt3_arr(i)
, p_fact_equip_qty1_arr(i),p_fact_bill_equip_Qty1_arr(i)
----
,0,0,0,0
,p_fact_labor_qty2_arr(i),p_fact_equip_qty2_arr(i),p_fact_labor_amt5_arr(i)
,p_fact_equip_amt5_arr(i) ,p_fact_amount5_arr(i),p_fact_amount4_arr(i)
-------
,p_fact_amount6_arr(i),p_fact_labor_amt4_arr(i),p_fact_equip_amt4_arr(i)
,p_fact_labor_qty3_arr(i),p_fact_equip_qty3_arr(i),p_fact_labor_amt8_arr(i)
----
,p_fact_equip_amt8_arr(i),p_fact_amount8_arr(i),p_fact_amount7_arr(i)
, p_fact_labor_amt7_arr(i),p_fact_equip_amt7_arr(i)
-------
-------
,0,0,0,0,0,0,0,0,0,0,0
,0,0,0,0
,DECODE( p_fact_slice,'CBS_TO_WBS_TO_RBS',p_fact_task_arr(i),'WBS_TO_RBS',p_fact_task_arr(i),-1), DECODE( p_fact_slice,'CBS_TO_WBS_TO_RBS',p_rbs_cbs_version_id,'WBS_TO_RBS',p_rbs_cbs_version_id,-1)
,DECODE( p_fact_slice,'CBS_TO_WBS_TO_RBS',p_3_level_object_id , 'WBS_TO_CBS',p_fact_task_arr(i),-1), DECODE( p_fact_slice,'CBS_TO_WBS_TO_RBS',p_3_level_version_id , 'WBS_TO_CBS',p_rbs_cbs_version_id,-1)
from dual
where l_ins_task_flag_arr(i) = 'Y';
INSERT INTO pa_pjt_wbs_fact (
----
PROJECT_ID , PROJECT_ELEMENT_ID
,WBS_ROLLUP_FLAG, PRG_ROLLUP_FLAG,CURRENCY_CODE --,RBS_ELEMENT_ID, RBS_VERSION_ID
, PLAN_VERSION_ID, LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN
----
,RAW_COST, BRDN_COST, REVENUE
, bill_RAW_COST, bill_BRDN_COST, bill_LABOR_RAW_COST, bill_LABOR_BRDN_COST
, bill_LABOR_HRS, EQUIPMENT_RAW_COST, EQUIPMENT_BRDN_COST
--------
, CAPITALIZABLE_RAW_COST, CAPITALIZABLE_BRDN_COST
, LABOR_RAW_COST, LABOR_BRDN_COST, LABOR_HRS, LABOR_REVENUE
, EQUIPMENT_HOURS, BILLABLE_EQUIPMENT_HOURS
----
, SUP_INV_COMMITTED_COST, PO_COMMITTED_COST, PR_COMMITTED_COST, OTH_COMMITTED_COST
, ACT_LABOR_HRS, ACT_EQUIP_HRS, ACT_LABOR_BRDN_COST
, ACT_EQUIP_BRDN_COST, ACT_BRDN_COST, ACT_RAW_COST
-------
, ACT_REVENUE, ACT_LABOR_RAW_COST,ACT_EQUIP_RAW_COST
, ETC_LABOR_HRS, ETC_EQUIP_HRS, ETC_LABOR_BRDN_COST
----
, ETC_EQUIP_BRDN_COST, ETC_BRDN_COST, ETC_RAW_COST
, ETC_LABOR_RAW_COST, ETC_EQUIP_RAW_COST
-----
, CUSTOM1, CUSTOM2, CUSTOM3, CUSTOM4, CUSTOM5, CUSTOM6,CUSTOM7, CUSTOM8, CUSTOM9, CUSTOM10, CUSTOM11
, CUSTOM12, CUSTOM13, CUSTOM14, CUSTOM15
----
)
SELECT
p_project_id ,p_fact_task_arr(i)
-- ,'Y','N',p_currency_code --,0,0
, G_WBS_ROLLUP_FLAG, G_PRG_ROLLUP_FLAG,p_currency_code --,0,0
,p_budget_version_id , sysdate, -1 , sysdate , -1, -1
----
----
, p_fact_amount1_arr(i),p_fact_amount2_arr(i),p_fact_amount3_arr(i)
,p_fact_bill_amt1_arr(i),p_fact_bill_amt2_arr(i),p_fact_bill_labor_amt1_arr(i),p_fact_bill_labor_amt2_arr(i)
,p_fact_bill_labor_Qty1_arr(i),p_fact_equip_amt1_arr(i),p_fact_equip_amt2_arr(i)
-----
,p_fact_capit_amt1_arr(i),p_fact_capit_amt2_arr(i)
,p_fact_labor_amt1_arr(i),p_fact_labor_amt2_arr(i),p_fact_labor_qty1_arr(i), p_fact_labor_amt3_arr(i)
, p_fact_equip_qty1_arr(i),p_fact_bill_equip_Qty1_arr(i)
----
,0,0,0,0
,p_fact_labor_qty2_arr(i),p_fact_equip_qty2_arr(i),p_fact_labor_amt5_arr(i)
,p_fact_equip_amt5_arr(i) ,p_fact_amount5_arr(i),p_fact_amount4_arr(i)
-------
,p_fact_amount6_arr(i),p_fact_labor_amt4_arr(i),p_fact_equip_amt4_arr(i)
,p_fact_labor_qty3_arr(i),p_fact_equip_qty3_arr(i),p_fact_labor_amt8_arr(i)
----
,p_fact_equip_amt8_arr(i),p_fact_amount8_arr(i),p_fact_amount7_arr(i)
, p_fact_labor_amt7_arr(i),p_fact_equip_amt7_arr(i)
-------
-------
,0,0,0,0,0,0,0,0,0,0,0
,0,0,0,0
from dual
where l_ins_task_flag_arr(i) = 'Y';
INSERT INTO pa_pjt_rbs_cbs_fact (
----
PROJECT_ID , ROLLUP_TYPE
, RBS_AGGR_LEVEL , PRG_ROLLUP_FLAG,CURRENCY_CODE,RBS_CBS_ELEMENT_ID, RBS_CBS_VERSION_ID
, PLAN_VERSION_ID, LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN
----
,RAW_COST, BRDN_COST, REVENUE
, bill_RAW_COST, bill_BRDN_COST, bill_LABOR_RAW_COST, bill_LABOR_BRDN_COST
, bill_LABOR_HRS, EQUIPMENT_RAW_COST, EQUIPMENT_BRDN_COST
--------
, CAPITALIZABLE_RAW_COST, CAPITALIZABLE_BRDN_COST
, LABOR_RAW_COST, LABOR_BRDN_COST, LABOR_HRS, LABOR_REVENUE
, EQUIPMENT_HOURS, BILLABLE_EQUIPMENT_HOURS
----
, SUP_INV_COMMITTED_COST, PO_COMMITTED_COST, PR_COMMITTED_COST, OTH_COMMITTED_COST
, ACT_LABOR_HRS, ACT_EQUIP_HRS, ACT_LABOR_BRDN_COST
, ACT_EQUIP_BRDN_COST, ACT_BRDN_COST, ACT_RAW_COST
-------
, ACT_REVENUE, ACT_LABOR_RAW_COST,ACT_EQUIP_RAW_COST
, ETC_LABOR_HRS, ETC_EQUIP_HRS, ETC_LABOR_BRDN_COST
----
, ETC_EQUIP_BRDN_COST, ETC_BRDN_COST, ETC_RAW_COST
, ETC_LABOR_RAW_COST, ETC_EQUIP_RAW_COST
-----
, CUSTOM1, CUSTOM2, CUSTOM3, CUSTOM4, CUSTOM5, CUSTOM6,CUSTOM7, CUSTOM8, CUSTOM9, CUSTOM10, CUSTOM11
, CUSTOM12, CUSTOM13, CUSTOM14, CUSTOM15
----
)
SELECT
p_project_id , p_fact_slice
-- ,'T','N',p_currency_code,p_fact_task_arr(i),p_rbs_cbs_version_id
, G_RBS_AGGR_LEVEL , G_PRG_ROLLUP_FLAG,p_currency_code ,p_fact_task_arr(i),p_rbs_cbs_version_id
,p_budget_version_id , sysdate, -1 , sysdate , -1, -1
----
----
, p_fact_amount1_arr(i),p_fact_amount2_arr(i),p_fact_amount3_arr(i)
,p_fact_bill_amt1_arr(i),p_fact_bill_amt2_arr(i),p_fact_bill_labor_amt1_arr(i),p_fact_bill_labor_amt2_arr(i)
,p_fact_bill_labor_Qty1_arr(i),p_fact_equip_amt1_arr(i),p_fact_equip_amt2_arr(i)
-----
,p_fact_capit_amt1_arr(i),p_fact_capit_amt2_arr(i)
,p_fact_labor_amt1_arr(i),p_fact_labor_amt2_arr(i),p_fact_labor_qty1_arr(i), p_fact_labor_amt3_arr(i)
, p_fact_equip_qty1_arr(i),p_fact_bill_equip_Qty1_arr(i)
----
,0,0,0,0
,p_fact_labor_qty2_arr(i),p_fact_equip_qty2_arr(i),p_fact_labor_amt5_arr(i)
,p_fact_equip_amt5_arr(i) ,p_fact_amount5_arr(i),p_fact_amount4_arr(i)
-------
,p_fact_amount6_arr(i),p_fact_labor_amt4_arr(i),p_fact_equip_amt4_arr(i)
,p_fact_labor_qty3_arr(i),p_fact_equip_qty3_arr(i),p_fact_labor_amt8_arr(i)
----
,p_fact_equip_amt8_arr(i),p_fact_amount8_arr(i),p_fact_amount7_arr(i)
, p_fact_labor_amt7_arr(i),p_fact_equip_amt7_arr(i)
-------
-------
,0,0,0,0,0,0,0,0,0,0,0
,0,0,0,0
from dual
where l_ins_task_flag_arr(i) = 'Y';