[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
-- insert into sri_log values ( fnd_log_messages_s.nextval, p_msg);
select
-- tmp7.TXN_ACCUM_HEADER_ID,
-- tmp7.PROJECT_ID,
tmp7.element_id ,
------------------
sum(tmp7.PRJ_RAW_COST),
sum(tmp7.PRJ_BILL_RAW_COST),
sum(tmp7.PRJ_BRDN_COST),
sum(tmp7.PRJ_BILL_BRDN_COST),
sum(tmp7.PRJ_REVENUE),
--------------
sum( tmp7.LABOR_PRJ_RAW_COST) ,
sum( tmp7.LABOR_PRJ_BILL_RAW_COST),
sum( tmp7.LABOR_PRJ_BRDN_COST),
sum( tmp7.LABOR_PRJ_BILL_BRDN_COST),
sum( tmp7.LABOR_PRJ_REVENUE),
--------------------
sum(tmp7.EQUIP_PRJ_RAW_COST ),
sum(tmp7.EQUIP_PRJ_BRDN_COST),
------------------
sum(tmp7.PRJ_SUP_INV_COMMITTED_COST),
sum(tmp7.PRJ_PO_COMMITTED_COST),
sum(tmp7.PRJ_PR_COMMITTED_COST),
sum(tmp7.PRJ_OTH_COMMITTED_COST),
------------------- BELOW POU---------------------------
sum(tmp7.POU_RAW_COST),
sum(tmp7.POU_BILL_RAW_COST),
sum(tmp7.POU_BRDN_COST),
sum(tmp7.POU_BILL_BRDN_COST),
sum(tmp7.POU_REVENUE),
----------------------
sum( tmp7.LABOR_POU_RAW_COST ) ,
sum( tmp7.LABOR_POU_BILL_RAW_COST),
sum( tmp7.LABOR_POU_BRDN_COST),
sum( tmp7.LABOR_POU_BILL_BRDN_COST),
sum( tmp7.LABOR_POU_REVENUE),
--------------------
sum( tmp7.EQUIP_POU_RAW_COST ),
sum( tmp7.EQUIP_POU_BRDN_COST),
---------------------
sum(tmp7.POU_SUP_INV_COMMITTED_COST),
sum(tmp7.POU_PO_COMMITTED_COST),
sum(tmp7.POU_PR_COMMITTED_COST),
sum(tmp7.POU_OTH_COMMITTED_COST),
----------------------
sum( tmp7.LABOR_QUANTITY),
sum( tmp7.LABOR_BILL_QUANTITY),
----------------------
sum( tmp7.EQUIP_QUANTITY),
sum( tmp7.EQUIP_BILL_QUANTITY),
----------------------
sum(tmp7.QUANTITY),
sum(tmp7.BILL_QUANTITY),
----------------------
b.connect_path ,
b.leaf_node ,
b.wbs_rbs_level,
0,0,0,
0,0,0
from
(
SELECT
map1.element_id , ---tmp7.TASK_ID,
nvl(tmp7.PRJ_RAW_COST,0) PRJ_RAW_COST ,
nvl(tmp7.PRJ_BILL_RAW_COST,0) PRJ_BILL_RAW_COST ,
nvl(tmp7.PRJ_BRDN_COST,0) PRJ_BRDN_COST,
nvl(tmp7.PRJ_BILL_BRDN_COST,0) PRJ_BILL_BRDN_COST,
nvl(tmp7.PRJ_REVENUE,0) PRJ_REVENUE,
--------------
decode(tmp7.RESOURCE_CLASS_ID,1,nvl(tmp7.PRJ_RAW_COST,0) ,0 ) LABOR_PRJ_RAW_COST ,
decode(tmp7.RESOURCE_CLASS_ID,1,nvl(tmp7.PRJ_BILL_RAW_COST,0),0) LABOR_PRJ_BILL_RAW_COST,
decode(tmp7.RESOURCE_CLASS_ID,1,nvl(tmp7.PRJ_BRDN_COST,0),0) LABOR_PRJ_BRDN_COST,
decode(tmp7.RESOURCE_CLASS_ID,1,nvl(tmp7.PRJ_BILL_BRDN_COST,0),0) LABOR_PRJ_BILL_BRDN_COST,
decode(tmp7.RESOURCE_CLASS_ID,1,nvl(tmp7.PRJ_REVENUE,0),0) LABOR_PRJ_REVENUE,
--------------------
decode(tmp7.RESOURCE_CLASS_ID,2,nvl(tmp7.PRJ_RAW_COST,0) ,0 ) EQUIP_PRJ_RAW_COST,
decode(tmp7.RESOURCE_CLASS_ID,2,nvl(tmp7.PRJ_BRDN_COST,0),0) EQUIP_PRJ_BRDN_COST,
------------------
nvl(tmp7.PRJ_SUP_INV_COMMITTED_COST,0) PRJ_SUP_INV_COMMITTED_COST,
nvl(tmp7.PRJ_PO_COMMITTED_COST,0) PRJ_PO_COMMITTED_COST,
nvl(tmp7.PRJ_PR_COMMITTED_COST,0) PRJ_PR_COMMITTED_COST,
nvl(tmp7.PRJ_OTH_COMMITTED_COST,0) PRJ_OTH_COMMITTED_COST,
------------------- BELOW POU---------------------------
nvl(tmp7.POU_RAW_COST,0) POU_RAW_COST,
nvl(tmp7.POU_BILL_RAW_COST,0) POU_BILL_RAW_COST,
nvl(tmp7.POU_BRDN_COST,0) POU_BRDN_COST,
nvl(tmp7.POU_BILL_BRDN_COST,0) POU_BILL_BRDN_COST,
nvl(tmp7.POU_REVENUE,0) POU_REVENUE,
----------------------
decode(tmp7.RESOURCE_CLASS_ID,1,nvl(tmp7.POU_RAW_COST,0) ,0 ) LABOR_POU_RAW_COST ,
decode(tmp7.RESOURCE_CLASS_ID,1,nvl(tmp7.POU_BILL_RAW_COST,0),0) LABOR_POU_BILL_RAW_COST,
decode(tmp7.RESOURCE_CLASS_ID,1,nvl(tmp7.POU_BRDN_COST,0),0) LABOR_POU_BRDN_COST,
decode(tmp7.RESOURCE_CLASS_ID,1,nvl(tmp7.POU_BILL_BRDN_COST,0),0) LABOR_POU_BILL_BRDN_COST,
decode(tmp7.RESOURCE_CLASS_ID,1,nvl(tmp7.POU_REVENUE,0),0) LABOR_POU_REVENUE,
--------------------
decode(tmp7.RESOURCE_CLASS_ID,2,nvl(tmp7.POU_RAW_COST,0) ,0 ) EQUIP_POU_RAW_COST,
decode(tmp7.RESOURCE_CLASS_ID,2,nvl(tmp7.POU_BRDN_COST,0),0) EQUIP_POU_BRDN_COST,
---------------------
nvl(tmp7.POU_SUP_INV_COMMITTED_COST,0) POU_SUP_INV_COMMITTED_COST,
nvl(tmp7.POU_PO_COMMITTED_COST,0) POU_PO_COMMITTED_COST,
nvl(tmp7.POU_PR_COMMITTED_COST,0) POU_PR_COMMITTED_COST,
nvl(tmp7.POU_OTH_COMMITTED_COST,0) POU_OTH_COMMITTED_COST,
----------------------
decode(tmp7.RESOURCE_CLASS_ID,1,nvl(tmp7.QUANTITY,0),0) LABOR_QUANTITY,
decode(tmp7.RESOURCE_CLASS_ID,1,nvl(tmp7.BILL_QUANTITY,0),0) LABOR_BILL_QUANTITY,
----------------------
decode(tmp7.RESOURCE_CLASS_ID,2,nvl(tmp7.QUANTITY,0),0) EQUIP_QUANTITY,
decode(tmp7.RESOURCE_CLASS_ID,2,nvl(tmp7.BILL_QUANTITY,0),0) EQUIP_BILL_QUANTITY,
----------------------
nvl(tmp7.QUANTITY,0) QUANTITY,
nvl(tmp7.BILL_QUANTITY,0) BILL_QUANTITY
FROM
PJI_FM_AGGR_FIN7 tmp7,
PA_RBS_TXN_ACCUM_MAP map1
WHERE project_id = p_project_id
AND p_calling_mode IN ( 'INCREMENTAL' , 'FULL' )
AND recvr_period_type = 'GL'
AND map1.txn_accum_header_id = tmp7.txn_accum_header_id
AND map1.struct_version_id = l_rbs_cbs_version_id
UNION ALL
SELECT
map1.element_id, --accum.TASK_ID,
nvl(accum.PRJ_RAW_COST,0) PRJ_RAW_COST ,
nvl(accum.PRJ_BILL_RAW_COST,0) PRJ_BILL_RAW_COST ,
nvl(accum.PRJ_BRDN_COST,0) PRJ_BRDN_COST,
nvl(accum.PRJ_BILL_BRDN_COST,0) PRJ_BILL_BRDN_COST,
nvl(accum.PRJ_REVENUE,0) PRJ_REVENUE,
--------------
decode(accum.RESOURCE_CLASS_ID,1,nvl(accum.PRJ_RAW_COST,0) ,0 ) LABOR_PRJ_RAW_COST ,
decode(accum.RESOURCE_CLASS_ID,1,nvl(accum.PRJ_BILL_RAW_COST,0),0) LABOR_PRJ_BILL_RAW_COST,
decode(accum.RESOURCE_CLASS_ID,1,nvl(accum.PRJ_BRDN_COST,0),0) LABOR_PRJ_BRDN_COST,
decode(accum.RESOURCE_CLASS_ID,1,nvl(accum.PRJ_BILL_BRDN_COST,0),0) LABOR_PRJ_BILL_BRDN_COST,
decode(accum.RESOURCE_CLASS_ID,1,nvl(accum.PRJ_REVENUE,0),0) LABOR_PRJ_REVENUE,
--------------------
decode(accum.RESOURCE_CLASS_ID,2,nvl(accum.PRJ_RAW_COST,0) ,0 ) EQUIP_PRJ_RAW_COST,
decode(accum.RESOURCE_CLASS_ID,2,nvl(accum.PRJ_BRDN_COST,0),0) EQUIP_PRJ_BRDN_COST,
------------------
0 PRJ_SUP_INV_COMMITTED_COST,
0 PRJ_PO_COMMITTED_COST,
0 PRJ_PR_COMMITTED_COST,
0 PRJ_OTH_COMMITTED_COST,
------------------- BELOW POU---------------------------
nvl(accum.POU_RAW_COST,0) POU_RAW_COST,
nvl(accum.POU_BILL_RAW_COST,0) POU_BILL_RAW_COST,
nvl(accum.POU_BRDN_COST,0) POU_BRDN_COST,
nvl(accum.POU_BILL_BRDN_COST,0) POU_BILL_BRDN_COST,
nvl(accum.POU_REVENUE,0) POU_REVENUE,
----------------------
decode(accum.RESOURCE_CLASS_ID,1,nvl(accum.POU_RAW_COST,0) ,0 ) LABOR_POU_RAW_COST ,
decode(accum.RESOURCE_CLASS_ID,1,nvl(accum.POU_BILL_RAW_COST,0),0) LABOR_POU_BILL_RAW_COST,
decode(accum.RESOURCE_CLASS_ID,1,nvl(accum.POU_BRDN_COST,0),0) LABOR_POU_BRDN_COST,
decode(accum.RESOURCE_CLASS_ID,1,nvl(accum.POU_BILL_BRDN_COST,0),0) LABOR_POU_BILL_BRDN_COST,
decode(accum.RESOURCE_CLASS_ID,1,nvl(accum.POU_REVENUE,0),0) LABOR_POU_REVENUE,
--------------------
decode(accum.RESOURCE_CLASS_ID,2,nvl(accum.POU_RAW_COST,0) ,0 ) EQUIP_POU_RAW_COST,
decode(accum.RESOURCE_CLASS_ID,2,nvl(accum.POU_BRDN_COST,0),0) EQUIP_POU_BRDN_COST,
---------------------
0 POU_SUP_INV_COMMITTED_COST,
0 POU_PO_COMMITTED_COST,
0 POU_PR_COMMITTED_COST,
0 POU_OTH_COMMITTED_COST,
----------------------
decode(accum.RESOURCE_CLASS_ID,1,nvl(accum.QUANTITY,0),0) LABOR_QUANTITY,
decode(accum.RESOURCE_CLASS_ID,1,nvl(accum.BILL_QUANTITY,0),0) LABOR_BILL_QUANTITY,
----------------------
decode(accum.RESOURCE_CLASS_ID,2,nvl(accum.QUANTITY,0),0) EQUIP_QUANTITY,
decode(accum.RESOURCE_CLASS_ID,2,nvl(accum.BILL_QUANTITY,0),0) EQUIP_BILL_QUANTITY,
----------------------
nvl(accum.QUANTITY,0) QUANTITY,
nvl(accum.BILL_QUANTITY,0) BILL_QUANTITY
FROM
PJI_FP_TXN_ACCUM accum,
PA_RBS_TXN_ACCUM_MAP map1
WHERE project_id = p_project_id
AND p_calling_mode = 'FULL'
AND recvr_period_type = 'GL'
AND map1.txn_accum_header_id = accum.txn_accum_header_id
AND map1.struct_version_id = l_rbs_cbs_version_id
UNION ALL
SELECT
map1.element_id, ---- accum1.TASK_ID,
0 PRJ_RAW_COST ,
0 PRJ_BILL_RAW_COST ,
0 PRJ_BRDN_COST,
0 PRJ_BILL_BRDN_COST,
0 PRJ_REVENUE,
--------------
0 LABOR_PRJ_RAW_COST ,
0 LABOR_PRJ_BILL_RAW_COST,
0 LABOR_PRJ_BRDN_COST,
0 LABOR_PRJ_BILL_BRDN_COST,
0 LABOR_PRJ_REVENUE,
--------------------
0 EQUIP_PRJ_RAW_COST,
0 EQUIP_PRJ_BRDN_COST,
------------------
nvl(accum1.PRJ_SUP_INV_COMMITTED_COST,0) PRJ_SUP_INV_COMMITTED_COST,
nvl(accum1.PRJ_PO_COMMITTED_COST,0) PRJ_PO_COMMITTED_COST,
nvl(accum1.PRJ_PR_COMMITTED_COST,0) PRJ_PR_COMMITTED_COST,
nvl(accum1.PRJ_OTH_COMMITTED_COST,0) PRJ_OTH_COMMITTED_COST,
------------------- BELOW POU---------------------------
0 POU_RAW_COST,
0 POU_BILL_RAW_COST,
0 POU_BRDN_COST,
0 POU_BILL_BRDN_COST,
0 POU_REVENUE,
----------------------
0 LABOR_POU_RAW_COST ,
0 LABOR_POU_BILL_RAW_COST,
0 LABOR_POU_BRDN_COST,
0 LABOR_POU_BILL_BRDN_COST,
0 LABOR_POU_REVENUE,
--------------------
0 EQUIP_POU_RAW_COST,
0 EQUIP_POU_BRDN_COST,
---------------------
nvl(accum1.POU_SUP_INV_COMMITTED_COST,0) POU_SUP_INV_COMMITTED_COST,
nvl(accum1.POU_PO_COMMITTED_COST,0) POU_PO_COMMITTED_COST,
nvl(accum1.POU_PR_COMMITTED_COST,0) POU_PR_COMMITTED_COST,
nvl(accum1.POU_OTH_COMMITTED_COST,0) POU_OTH_COMMITTED_COST,
----------------------
0 LABOR_QUANTITY,
0 LABOR_BILL_QUANTITY,
----------------------
0 EQUIP_QUANTITY,
0 EQUIP_BILL_QUANTITY,
----------------------
0 QUANTITY,
0 BILL_QUANTITY
FROM
PJI_FP_TXN_ACCUM1 accum1,
PA_RBS_TXN_ACCUM_MAP map1
WHERE project_id = p_project_id
AND p_calling_mode = 'FULL'
AND recvr_period_type = 'GL'
AND map1.txn_accum_header_id = accum1.txn_accum_header_id
AND map1.struct_version_id = l_rbs_cbs_version_id
) tmp7 ,
pa_ppr_obj_tmp b
where
tmp7.element_id = b.object_id
group by -- tmp7.TXN_ACCUM_HEADER_ID,
-- tmp7.PROJECT_ID,
tmp7.element_id,
b.connect_path ,
b.leaf_node ,
b.wbs_rbs_level;
select
-- tmp7.TXN_ACCUM_HEADER_ID,
-- tmp7.PROJECT_ID,
tmp7.TASK_ID ,
------------------
sum(tmp7.PRJ_RAW_COST),
sum(tmp7.PRJ_BILL_RAW_COST),
sum(tmp7.PRJ_BRDN_COST),
sum(tmp7.PRJ_BILL_BRDN_COST),
sum(tmp7.PRJ_REVENUE),
--------------
sum( tmp7.LABOR_PRJ_RAW_COST) ,
sum( tmp7.LABOR_PRJ_BILL_RAW_COST),
sum( tmp7.LABOR_PRJ_BRDN_COST),
sum( tmp7.LABOR_PRJ_BILL_BRDN_COST),
sum( tmp7.LABOR_PRJ_REVENUE),
--------------------
sum(tmp7.EQUIP_PRJ_RAW_COST ),
sum(tmp7.EQUIP_PRJ_BRDN_COST),
------------------
sum(tmp7.PRJ_SUP_INV_COMMITTED_COST),
sum(tmp7.PRJ_PO_COMMITTED_COST),
sum(tmp7.PRJ_PR_COMMITTED_COST),
sum(tmp7.PRJ_OTH_COMMITTED_COST),
------------------- BELOW POU---------------------------
sum(tmp7.POU_RAW_COST),
sum(tmp7.POU_BILL_RAW_COST),
sum(tmp7.POU_BRDN_COST),
sum(tmp7.POU_BILL_BRDN_COST),
sum(tmp7.POU_REVENUE),
----------------------
sum( tmp7.LABOR_POU_RAW_COST ) ,
sum( tmp7.LABOR_POU_BILL_RAW_COST),
sum( tmp7.LABOR_POU_BRDN_COST),
sum( tmp7.LABOR_POU_BILL_BRDN_COST),
sum( tmp7.LABOR_POU_REVENUE),
--------------------
sum( tmp7.EQUIP_POU_RAW_COST ),
sum( tmp7.EQUIP_POU_BRDN_COST),
---------------------
sum(tmp7.POU_SUP_INV_COMMITTED_COST),
sum(tmp7.POU_PO_COMMITTED_COST),
sum(tmp7.POU_PR_COMMITTED_COST),
sum(tmp7.POU_OTH_COMMITTED_COST),
----------------------
sum( tmp7.LABOR_QUANTITY),
sum( tmp7.LABOR_BILL_QUANTITY),
----------------------
sum( tmp7.EQUIP_QUANTITY),
sum( tmp7.EQUIP_BILL_QUANTITY),
----------------------
sum(tmp7.QUANTITY),
sum(tmp7.BILL_QUANTITY),
----------------------
b.connect_path ,
b.leaf_node ,
b.wbs_rbs_level,
0,0,0,
0,0,0
from
(
SELECT
tmp7.TASK_ID,
nvl(tmp7.PRJ_RAW_COST,0) PRJ_RAW_COST ,
nvl(tmp7.PRJ_BILL_RAW_COST,0) PRJ_BILL_RAW_COST ,
nvl(tmp7.PRJ_BRDN_COST,0) PRJ_BRDN_COST,
nvl(tmp7.PRJ_BILL_BRDN_COST,0) PRJ_BILL_BRDN_COST,
nvl(tmp7.PRJ_REVENUE,0) PRJ_REVENUE,
--------------
decode(tmp7.RESOURCE_CLASS_ID,1,nvl(tmp7.PRJ_RAW_COST,0) ,0 ) LABOR_PRJ_RAW_COST ,
decode(tmp7.RESOURCE_CLASS_ID,1,nvl(tmp7.PRJ_BILL_RAW_COST,0),0) LABOR_PRJ_BILL_RAW_COST,
decode(tmp7.RESOURCE_CLASS_ID,1,nvl(tmp7.PRJ_BRDN_COST,0),0) LABOR_PRJ_BRDN_COST,
decode(tmp7.RESOURCE_CLASS_ID,1,nvl(tmp7.PRJ_BILL_BRDN_COST,0),0) LABOR_PRJ_BILL_BRDN_COST,
decode(tmp7.RESOURCE_CLASS_ID,1,nvl(tmp7.PRJ_REVENUE,0),0) LABOR_PRJ_REVENUE,
--------------------
decode(tmp7.RESOURCE_CLASS_ID,2,nvl(tmp7.PRJ_RAW_COST,0) ,0 ) EQUIP_PRJ_RAW_COST,
decode(tmp7.RESOURCE_CLASS_ID,2,nvl(tmp7.PRJ_BRDN_COST,0),0) EQUIP_PRJ_BRDN_COST,
------------------
nvl(tmp7.PRJ_SUP_INV_COMMITTED_COST,0) PRJ_SUP_INV_COMMITTED_COST,
nvl(tmp7.PRJ_PO_COMMITTED_COST,0) PRJ_PO_COMMITTED_COST,
nvl(tmp7.PRJ_PR_COMMITTED_COST,0) PRJ_PR_COMMITTED_COST,
nvl(tmp7.PRJ_OTH_COMMITTED_COST,0) PRJ_OTH_COMMITTED_COST,
------------------- BELOW POU---------------------------
nvl(tmp7.POU_RAW_COST,0) POU_RAW_COST,
nvl(tmp7.POU_BILL_RAW_COST,0) POU_BILL_RAW_COST,
nvl(tmp7.POU_BRDN_COST,0) POU_BRDN_COST,
nvl(tmp7.POU_BILL_BRDN_COST,0) POU_BILL_BRDN_COST,
nvl(tmp7.POU_REVENUE,0) POU_REVENUE,
----------------------
decode(tmp7.RESOURCE_CLASS_ID,1,nvl(tmp7.POU_RAW_COST,0) ,0 ) LABOR_POU_RAW_COST ,
decode(tmp7.RESOURCE_CLASS_ID,1,nvl(tmp7.POU_BILL_RAW_COST,0),0) LABOR_POU_BILL_RAW_COST,
decode(tmp7.RESOURCE_CLASS_ID,1,nvl(tmp7.POU_BRDN_COST,0),0) LABOR_POU_BRDN_COST,
decode(tmp7.RESOURCE_CLASS_ID,1,nvl(tmp7.POU_BILL_BRDN_COST,0),0) LABOR_POU_BILL_BRDN_COST,
decode(tmp7.RESOURCE_CLASS_ID,1,nvl(tmp7.POU_REVENUE,0),0) LABOR_POU_REVENUE,
--------------------
decode(tmp7.RESOURCE_CLASS_ID,2,nvl(tmp7.POU_RAW_COST,0) ,0 ) EQUIP_POU_RAW_COST,
decode(tmp7.RESOURCE_CLASS_ID,2,nvl(tmp7.POU_BRDN_COST,0),0) EQUIP_POU_BRDN_COST,
---------------------
nvl(tmp7.POU_SUP_INV_COMMITTED_COST,0) POU_SUP_INV_COMMITTED_COST,
nvl(tmp7.POU_PO_COMMITTED_COST,0) POU_PO_COMMITTED_COST,
nvl(tmp7.POU_PR_COMMITTED_COST,0) POU_PR_COMMITTED_COST,
nvl(tmp7.POU_OTH_COMMITTED_COST,0) POU_OTH_COMMITTED_COST,
----------------------
decode(tmp7.RESOURCE_CLASS_ID,1,nvl(tmp7.QUANTITY,0),0) LABOR_QUANTITY,
decode(tmp7.RESOURCE_CLASS_ID,1,nvl(tmp7.BILL_QUANTITY,0),0) LABOR_BILL_QUANTITY,
----------------------
decode(tmp7.RESOURCE_CLASS_ID,2,nvl(tmp7.QUANTITY,0),0) EQUIP_QUANTITY,
decode(tmp7.RESOURCE_CLASS_ID,2,nvl(tmp7.BILL_QUANTITY,0),0) EQUIP_BILL_QUANTITY,
----------------------
nvl(tmp7.QUANTITY,0) QUANTITY,
nvl(tmp7.BILL_QUANTITY,0) BILL_QUANTITY
FROM
PJI_FM_AGGR_FIN7 tmp7
WHERE project_id = p_project_id
AND p_calling_mode = 'INCREMENTAL'
AND recvr_period_type = 'GL'
UNION ALL
SELECT
accum.TASK_ID,
nvl(accum.PRJ_RAW_COST,0) PRJ_RAW_COST ,
nvl(accum.PRJ_BILL_RAW_COST,0) PRJ_BILL_RAW_COST ,
nvl(accum.PRJ_BRDN_COST,0) PRJ_BRDN_COST,
nvl(accum.PRJ_BILL_BRDN_COST,0) PRJ_BILL_BRDN_COST,
nvl(accum.PRJ_REVENUE,0) PRJ_REVENUE,
--------------
decode(accum.RESOURCE_CLASS_ID,1,nvl(accum.PRJ_RAW_COST,0) ,0 ) LABOR_PRJ_RAW_COST ,
decode(accum.RESOURCE_CLASS_ID,1,nvl(accum.PRJ_BILL_RAW_COST,0),0) LABOR_PRJ_BILL_RAW_COST,
decode(accum.RESOURCE_CLASS_ID,1,nvl(accum.PRJ_BRDN_COST,0),0) LABOR_PRJ_BRDN_COST,
decode(accum.RESOURCE_CLASS_ID,1,nvl(accum.PRJ_BILL_BRDN_COST,0),0) LABOR_PRJ_BILL_BRDN_COST,
decode(accum.RESOURCE_CLASS_ID,1,nvl(accum.PRJ_REVENUE,0),0) LABOR_PRJ_REVENUE,
--------------------
decode(accum.RESOURCE_CLASS_ID,2,nvl(accum.PRJ_RAW_COST,0) ,0 ) EQUIP_PRJ_RAW_COST,
decode(accum.RESOURCE_CLASS_ID,2,nvl(accum.PRJ_BRDN_COST,0),0) EQUIP_PRJ_BRDN_COST,
------------------
0 PRJ_SUP_INV_COMMITTED_COST,
0 PRJ_PO_COMMITTED_COST,
0 PRJ_PR_COMMITTED_COST,
0 PRJ_OTH_COMMITTED_COST,
------------------- BELOW POU---------------------------
nvl(accum.POU_RAW_COST,0) POU_RAW_COST,
nvl(accum.POU_BILL_RAW_COST,0) POU_BILL_RAW_COST,
nvl(accum.POU_BRDN_COST,0) POU_BRDN_COST,
nvl(accum.POU_BILL_BRDN_COST,0) POU_BILL_BRDN_COST,
nvl(accum.POU_REVENUE,0) POU_REVENUE,
----------------------
decode(accum.RESOURCE_CLASS_ID,1,nvl(accum.POU_RAW_COST,0) ,0 ) LABOR_POU_RAW_COST ,
decode(accum.RESOURCE_CLASS_ID,1,nvl(accum.POU_BILL_RAW_COST,0),0) LABOR_POU_BILL_RAW_COST,
decode(accum.RESOURCE_CLASS_ID,1,nvl(accum.POU_BRDN_COST,0),0) LABOR_POU_BRDN_COST,
decode(accum.RESOURCE_CLASS_ID,1,nvl(accum.POU_BILL_BRDN_COST,0),0) LABOR_POU_BILL_BRDN_COST,
decode(accum.RESOURCE_CLASS_ID,1,nvl(accum.POU_REVENUE,0),0) LABOR_POU_REVENUE,
--------------------
decode(accum.RESOURCE_CLASS_ID,2,nvl(accum.POU_RAW_COST,0) ,0 ) EQUIP_POU_RAW_COST,
decode(accum.RESOURCE_CLASS_ID,2,nvl(accum.POU_BRDN_COST,0),0) EQUIP_POU_BRDN_COST,
---------------------
0 POU_SUP_INV_COMMITTED_COST,
0 POU_PO_COMMITTED_COST,
0 POU_PR_COMMITTED_COST,
0 POU_OTH_COMMITTED_COST,
----------------------
decode(accum.RESOURCE_CLASS_ID,1,nvl(accum.QUANTITY,0),0) LABOR_QUANTITY,
decode(accum.RESOURCE_CLASS_ID,1,nvl(accum.BILL_QUANTITY,0),0) LABOR_BILL_QUANTITY,
----------------------
decode(accum.RESOURCE_CLASS_ID,2,nvl(accum.QUANTITY,0),0) EQUIP_QUANTITY,
decode(accum.RESOURCE_CLASS_ID,2,nvl(accum.BILL_QUANTITY,0),0) EQUIP_BILL_QUANTITY,
----------------------
nvl(accum.QUANTITY,0) QUANTITY,
nvl(accum.BILL_QUANTITY,0) BILL_QUANTITY
FROM
PJI_FP_TXN_ACCUM accum
WHERE project_id = p_project_id
AND p_calling_mode = 'FULL'
AND recvr_period_type = 'GL'
UNION ALL
SELECT
accum1.TASK_ID,
0 PRJ_RAW_COST ,
0 PRJ_BILL_RAW_COST ,
0 PRJ_BRDN_COST,
0 PRJ_BILL_BRDN_COST,
0 PRJ_REVENUE,
--------------
0 LABOR_PRJ_RAW_COST ,
0 LABOR_PRJ_BILL_RAW_COST,
0 LABOR_PRJ_BRDN_COST,
0 LABOR_PRJ_BILL_BRDN_COST,
0 LABOR_PRJ_REVENUE,
--------------------
0 EQUIP_PRJ_RAW_COST,
0 EQUIP_PRJ_BRDN_COST,
------------------
nvl(accum1.PRJ_SUP_INV_COMMITTED_COST,0) PRJ_SUP_INV_COMMITTED_COST,
nvl(accum1.PRJ_PO_COMMITTED_COST,0) PRJ_PO_COMMITTED_COST,
nvl(accum1.PRJ_PR_COMMITTED_COST,0) PRJ_PR_COMMITTED_COST,
nvl(accum1.PRJ_OTH_COMMITTED_COST,0) PRJ_OTH_COMMITTED_COST,
------------------- BELOW POU---------------------------
0 POU_RAW_COST,
0 POU_BILL_RAW_COST,
0 POU_BRDN_COST,
0 POU_BILL_BRDN_COST,
0 POU_REVENUE,
----------------------
0 LABOR_POU_RAW_COST ,
0 LABOR_POU_BILL_RAW_COST,
0 LABOR_POU_BRDN_COST,
0 LABOR_POU_BILL_BRDN_COST,
0 LABOR_POU_REVENUE,
--------------------
0 EQUIP_POU_RAW_COST,
0 EQUIP_POU_BRDN_COST,
---------------------
nvl(accum1.POU_SUP_INV_COMMITTED_COST,0) POU_SUP_INV_COMMITTED_COST,
nvl(accum1.POU_PO_COMMITTED_COST,0) POU_PO_COMMITTED_COST,
nvl(accum1.POU_PR_COMMITTED_COST,0) POU_PR_COMMITTED_COST,
nvl(accum1.POU_OTH_COMMITTED_COST,0) POU_OTH_COMMITTED_COST,
----------------------
0 LABOR_QUANTITY,
0 LABOR_BILL_QUANTITY,
----------------------
0 EQUIP_QUANTITY,
0 EQUIP_BILL_QUANTITY,
----------------------
0 QUANTITY,
0 BILL_QUANTITY
FROM
PJI_FP_TXN_ACCUM1 accum1
WHERE project_id = p_project_id
AND p_calling_mode = 'FULL'
AND recvr_period_type = 'GL'
) tmp7 ,
pa_ppr_obj_tmp b
where
tmp7.task_id = b.object_id
group by -- tmp7.TXN_ACCUM_HEADER_ID,
-- tmp7.PROJECT_ID,
tmp7.TASK_ID,
b.connect_path ,
b.leaf_node ,
b.wbs_rbs_level;
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
into l_version_type , l_struct_ver_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 (+);
delete from pa_ppr_obj_tmp2;
insert into pa_ppr_obj_tmp2 ( object_id , parent_object_id )
select /*+ ordered */
a.proj_element_id , nvl(c.proj_element_id,0)
from PA_PROJ_ELEMENT_VERSIONS a , pa_tasks c , pa_object_relationships b , PA_PROJ_ELEMENT_VERSIONS c
where a.project_id = l_project_id
and a.object_type = 'PA_TASK' -- SRI_BUG add this new condition
and a.parent_structure_version_id = l_struct_ver_id
and a.proj_element_id = c.task_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_tmp2 ( object_id , parent_object_id )
select c.task_id , nvl(c.parent_task_id,0)
from pa_tasks c
where c.project_id = l_project_id ;
insert into pa_ppr_obj_tmp2 ( object_id , parent_object_id )
select rbs_element_id , nvl(parent_element_id,0)
from pa_rbs_elements
where rbs_version_id = l_rbs_cbs_version_id_2 ;
/* 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_out_task_arr(i) ) ;
l_task_arr.delete;
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 c.task_id , nvl(c.parent_task_id,0)
from pa_tasks c
where c.project_id = l_project_id ;
insert into pa_ppr_obj_tmp ( object_id , parent_object_id )
select rbs_element_id , nvl(parent_element_id,0)
from pa_rbs_elements
where rbs_version_id = l_rbs_cbs_version_id ;
insert into pa_ppr_obj_tmp ( object_id , parent_object_id ) values ( 0 , null );
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_leaf_node_arr.delete;
l_prj_amount1_arr.delete; l_prj_amount2_arr.delete; l_prj_amount3_arr.delete;
l_prj_amount4_arr.delete; l_prj_amount5_arr.delete;
l_prj_amount6_arr.delete; l_prj_amount7_arr.delete; l_prj_amount8_arr.delete;
l_prj_amount9_arr.delete; l_prj_amount10_arr.delete;
l_prj_amount11_arr.delete; l_prj_amount12_arr.delete;
l_prj_amount13_arr.delete; l_prj_amount14_arr.delete; l_prj_amount15_arr.delete;
l_prj_amount16_arr.delete;
l_func_amount1_arr.delete; l_func_amount2_arr.delete; l_func_amount3_arr.delete;
l_func_amount4_arr.delete; l_func_amount5_arr.delete;
l_func_amount6_arr.delete; l_func_amount7_arr.delete; l_func_amount8_arr.delete;
l_func_amount9_arr.delete; l_func_amount10_arr.delete;
l_func_amount11_arr.delete; l_func_amount12_arr.delete;
l_func_amount13_arr.delete; l_func_amount14_arr.delete; l_prj_amount15_arr.delete;
l_func_amount16_arr.delete;
l_func_amount17_arr.delete; l_func_amount18_arr.delete; l_prj_amount19_arr.delete;
l_qty1_arr.delete; l_qty2_arr.delete;
l_qty3_arr.delete; l_qty4_arr.delete;
l_qty5_arr.delete; l_qty6_arr.delete;
l_upd_task_flag_arr.delete;
l_upd_task_arr.delete;
l_upd_task_arr_2.delete;
UPDATE pa_pjt_wbs_fact fact /* bulk bind */
SET (
----
-----
RAW_COST,bill_RAW_COST, BRDN_COST, bill_BRDN_COST , REVENUE
,LABOR_RAW_COST,bill_LABOR_RAW_COST,LABOR_BRDN_COST, bill_LABOR_BRDN_COST,LABOR_REVENUE
,EQUIPMENT_RAW_COST, EQUIPMENT_BRDN_COST
,SUP_INV_COMMITTED_COST, PO_COMMITTED_COST, PR_COMMITTED_COST, OTH_COMMITTED_COST
------
------
,LABOR_HRS, BILL_LABOR_HRS, EQUIPMENT_HOURS, BILLABLE_EQUIPMENT_HOURS
------
------
,CAPITALIZABLE_RAW_COST, CAPITALIZABLE_BRDN_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_amount4_arr(i),p_fact_amount5_arr(i)
,p_fact_amount6_arr(i),p_fact_amount7_arr(i),p_fact_amount8_arr(i), p_fact_amount9_arr(i),p_fact_amount10_arr(i)
,p_fact_amount11_arr(i),p_fact_amount12_arr(i)
,p_fact_amount13_arr(i),p_fact_amount14_arr(i),p_fact_amount15_arr(i), p_fact_amount16_arr(i)
----
----
,p_fact_qty1_arr(i),p_fact_qty2_arr(i),p_fact_qty3_arr(i),p_fact_qty4_arr(i)
----
----
,0,0
----
----
,0,0,0,0,0
,0,0,0,0
----
----
,0,0,0,0,0
,0,0,0
----
----
,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 p_fact_slice = 'WBS'
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_fact fact /* bulk bind */
SET (
----
-----
RAW_COST,bill_RAW_COST, BRDN_COST, bill_BRDN_COST , REVENUE
,LABOR_RAW_COST,bill_LABOR_RAW_COST,LABOR_BRDN_COST, bill_LABOR_BRDN_COST,LABOR_REVENUE
,EQUIPMENT_RAW_COST, EQUIPMENT_BRDN_COST
,SUP_INV_COMMITTED_COST, PO_COMMITTED_COST, PR_COMMITTED_COST, OTH_COMMITTED_COST
------
------
,LABOR_HRS, BILL_LABOR_HRS, EQUIPMENT_HOURS, BILLABLE_EQUIPMENT_HOURS
------
------
,CAPITALIZABLE_RAW_COST, CAPITALIZABLE_BRDN_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
RAW_COST+p_fact_amount1_arr(i),bill_RAW_COST+p_fact_amount2_arr(i), BRDN_COST+p_fact_amount3_arr(i),
bill_BRDN_COST +p_fact_amount4_arr(i), REVENUE + p_fact_amount5_arr(i),
LABOR_RAW_COST+p_fact_amount6_arr(i),bill_LABOR_RAW_COST+p_fact_amount7_arr(i),
LABOR_BRDN_COST+p_fact_amount8_arr(i),bill_LABOR_BRDN_COST+p_fact_amount9_arr(i),
LABOR_REVENUE+p_fact_amount10_arr(i),EQUIPMENT_RAW_COST+p_fact_amount11_arr(i),
EQUIPMENT_BRDN_COST+p_fact_amount12_arr(i),
------
------
p_fact_amount13_arr(i), -- SUP_INV_COMMITTED_COST
p_fact_amount14_arr(i), --- PO_COMMITTED_COST
p_fact_amount15_arr(i), --- PR_COMMITTED_COST
p_fact_amount16_arr(i) --- OTH_COMMITTED_COST
----
-----
,LABOR_HRS+p_fact_qty1_arr(i), BILL_LABOR_HRS+p_fact_qty2_arr(i), EQUIPMENT_HOURS+p_fact_qty3_arr(i),
BILLABLE_EQUIPMENT_HOURS+p_fact_qty4_arr(i)
----
----
,0,0
----
----
,0,0,0,0,0
,0,0,0,0
----
----
,0,0,0,0,0
,0,0,0
----
----
,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 p_fact_slice = 'WBS'
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_rbs_cbs_fact fact /* bulk bind */
SET (
----
-----
RAW_COST,bill_RAW_COST, BRDN_COST, bill_BRDN_COST , REVENUE
,LABOR_RAW_COST,bill_LABOR_RAW_COST,LABOR_BRDN_COST, bill_LABOR_BRDN_COST,LABOR_REVENUE
,EQUIPMENT_RAW_COST, EQUIPMENT_BRDN_COST
,SUP_INV_COMMITTED_COST, PO_COMMITTED_COST, PR_COMMITTED_COST, OTH_COMMITTED_COST
------
------
,LABOR_HRS, BILL_LABOR_HRS, EQUIPMENT_HOURS, BILLABLE_EQUIPMENT_HOURS
------
------
,CAPITALIZABLE_RAW_COST, CAPITALIZABLE_BRDN_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_amount4_arr(i),p_fact_amount5_arr(i)
,p_fact_amount6_arr(i),p_fact_amount7_arr(i),p_fact_amount8_arr(i), p_fact_amount9_arr(i),p_fact_amount10_arr(i)
,p_fact_amount11_arr(i),p_fact_amount12_arr(i)
,p_fact_amount13_arr(i),p_fact_amount14_arr(i),p_fact_amount15_arr(i), p_fact_amount16_arr(i)
----
----
,p_fact_qty1_arr(i),p_fact_qty2_arr(i),p_fact_qty3_arr(i),p_fact_qty4_arr(i)
----
----
,0,0
----
----
,0,0,0,0,0
,0,0,0,0
----
----
,0,0,0,0,0
,0,0,0
----
----
,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 p_fact_slice = 'RBS'
AND RBS_AGGR_LEVEL = 'Y'
AND PRG_ROLLUP_FLAG = 'N'
AND PLAN_VERSION_ID = -1
AND CURRENCY_CODE = p_currency_code
AND rbs_cbs_version_id = p_rbs_cbs_version_id
RETURNING rbs_cbs_element_id
BULK COLLECT INTO l_upd_task_arr;
UPDATE pa_pjt_rbs_cbs_fact fact /* bulk bind */
SET (
----
-----
RAW_COST,bill_RAW_COST, BRDN_COST, bill_BRDN_COST , REVENUE
,LABOR_RAW_COST,bill_LABOR_RAW_COST,LABOR_BRDN_COST, bill_LABOR_BRDN_COST,LABOR_REVENUE
,EQUIPMENT_RAW_COST, EQUIPMENT_BRDN_COST
,SUP_INV_COMMITTED_COST, PO_COMMITTED_COST, PR_COMMITTED_COST, OTH_COMMITTED_COST
------
------
,LABOR_HRS, BILL_LABOR_HRS, EQUIPMENT_HOURS, BILLABLE_EQUIPMENT_HOURS
------
------
,CAPITALIZABLE_RAW_COST, CAPITALIZABLE_BRDN_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
RAW_COST+p_fact_amount1_arr(i),bill_RAW_COST+p_fact_amount2_arr(i), BRDN_COST+p_fact_amount3_arr(i),
bill_BRDN_COST +p_fact_amount4_arr(i), REVENUE + p_fact_amount5_arr(i),
LABOR_RAW_COST+p_fact_amount6_arr(i),bill_LABOR_RAW_COST+p_fact_amount7_arr(i),
LABOR_BRDN_COST+p_fact_amount8_arr(i),bill_LABOR_BRDN_COST+p_fact_amount9_arr(i),
LABOR_REVENUE+p_fact_amount10_arr(i),EQUIPMENT_RAW_COST+p_fact_amount11_arr(i),
EQUIPMENT_BRDN_COST+p_fact_amount12_arr(i),
------
------
p_fact_amount13_arr(i), -- SUP_INV_COMMITTED_COST
p_fact_amount14_arr(i), --- PO_COMMITTED_COST
p_fact_amount15_arr(i), --- PR_COMMITTED_COST
p_fact_amount16_arr(i) --- OTH_COMMITTED_COST
----
-----
,LABOR_HRS+p_fact_qty1_arr(i), BILL_LABOR_HRS+p_fact_qty2_arr(i), EQUIPMENT_HOURS+p_fact_qty3_arr(i),
BILLABLE_EQUIPMENT_HOURS+p_fact_qty4_arr(i)
----
----
,0,0
----
----
,0,0,0,0,0
,0,0,0,0
----
----
,0,0,0,0,0
,0,0,0
----
----
,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 p_fact_slice = 'RBS'
AND RBS_AGGR_LEVEL = 'Y'
AND PRG_ROLLUP_FLAG = 'N'
AND PLAN_VERSION_ID = -1
AND CURRENCY_CODE = p_currency_code
AND rbs_cbs_version_id = p_rbs_cbs_version_id
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_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,bill_RAW_COST, BRDN_COST, bill_BRDN_COST , REVENUE
,LABOR_RAW_COST,bill_LABOR_RAW_COST,LABOR_BRDN_COST, bill_LABOR_BRDN_COST,LABOR_REVENUE
,EQUIPMENT_RAW_COST, EQUIPMENT_BRDN_COST
,SUP_INV_COMMITTED_COST, PO_COMMITTED_COST, PR_COMMITTED_COST, OTH_COMMITTED_COST
------
------
,LABOR_HRS, BILL_LABOR_HRS, EQUIPMENT_HOURS, BILLABLE_EQUIPMENT_HOURS
------
------
,CAPITALIZABLE_RAW_COST, CAPITALIZABLE_BRDN_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
,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_amount4_arr(i),p_fact_amount5_arr(i)
,p_fact_amount6_arr(i),p_fact_amount7_arr(i),p_fact_amount8_arr(i), p_fact_amount9_arr(i),p_fact_amount10_arr(i)
,p_fact_amount11_arr(i),p_fact_amount12_arr(i)
,p_fact_amount13_arr(i),p_fact_amount14_arr(i),p_fact_amount15_arr(i), p_fact_amount16_arr(i)
----
----
,p_fact_qty1_arr(i),p_fact_qty2_arr(i),p_fact_qty3_arr(i),p_fact_qty4_arr(i)
----
----
,0,0
----
----
,0,0,0,0,0
,0,0,0,0
----
----
,0,0,0,0,0
,0,0,0
----
----
,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 , rbs_cbs_element_id , rbs_cbs_version_id , rollup_type
, RBS_AGGR_LEVEL, 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,bill_RAW_COST, BRDN_COST, bill_BRDN_COST , REVENUE
,LABOR_RAW_COST,bill_LABOR_RAW_COST,LABOR_BRDN_COST, bill_LABOR_BRDN_COST,LABOR_REVENUE
,EQUIPMENT_RAW_COST, EQUIPMENT_BRDN_COST
,SUP_INV_COMMITTED_COST, PO_COMMITTED_COST, PR_COMMITTED_COST, OTH_COMMITTED_COST
------
------
,LABOR_HRS, BILL_LABOR_HRS, EQUIPMENT_HOURS, BILLABLE_EQUIPMENT_HOURS
------
------
,CAPITALIZABLE_RAW_COST, CAPITALIZABLE_BRDN_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),p_rbs_cbs_version_id , 'RBS'
,'Y','N',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_amount4_arr(i),p_fact_amount5_arr(i)
,p_fact_amount6_arr(i),p_fact_amount7_arr(i),p_fact_amount8_arr(i), p_fact_amount9_arr(i),p_fact_amount10_arr(i)
,p_fact_amount11_arr(i),p_fact_amount12_arr(i)
,p_fact_amount13_arr(i),p_fact_amount14_arr(i),p_fact_amount15_arr(i), p_fact_amount16_arr(i)
----
----
,p_fact_qty1_arr(i),p_fact_qty2_arr(i),p_fact_qty3_arr(i),p_fact_qty4_arr(i)
----
----
,0,0
----
----
,0,0,0,0,0
,0,0,0,0
----
----
,0,0,0,0,0
,0,0,0
----
----
,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';