[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select count(*), olap_group
into l_count, l_olap_group
from pa_projects_all
where olap_group is not null
group by olap_group
having count(*) < p_group_size;
update pa_projects_all
set olap_group = l_olap_group --,
--struct_change_flag = 'Y'
where olap_group is null
and rownum <= l_curr_olap_group_count;
select count(*) into l_prj_count from pa_projects_all where olap_group is null;
select ceil(l_prj_count/p_group_size) into l_loop from dual;
select PA_OLAP_GROUP_S.nextval into l_olap_group from dual;
update pa_projects_all
set olap_group = l_olap_group --,
-- struct_change_flag = 'Y'
where olap_group is null
and rownum <= p_group_size;
insert into pa_budget_versions_olap_tmp1
(project_id ,
budget_version_id ,
PROJFUNC_CURRENCY_CODE,
PROJECT_CURRENCY_CODE
)
select p.project_id,
b.budget_version_id,
p.PROJFUNC_CURRENCY_CODE,
p.PROJECT_CURRENCY_CODE
from pa_budget_versions b, pa_projects_all p, pa_pjt_proj_batch batch
where b.olap_change_flag = 'Y'
and b.project_id = p.project_id
and p.project_id = batch.project_id;
insert into pa_budget_versions_olap_tmp2
(curr_type,
project_id,
budget_version_id,
currency_code
)
select 'ALL', p.project_id, b.budget_version_id, p.PROJFUNC_CURRENCY_CODE
from pa_budget_versions_olap_tmp1 b, pa_projects_all p
where b.project_id = p.project_id
and p.PROJFUNC_CURRENCY_CODE = p.PROJECT_CURRENCY_CODE
UNION
select 'PFC', p.project_id, b.budget_version_id, p.PROJFUNC_CURRENCY_CODE
from pa_budget_versions_olap_tmp1 b, pa_projects_all p
where b.project_id = p.project_id
and p.PROJFUNC_CURRENCY_CODE <> p.PROJECT_CURRENCY_CODE
UNION
select 'PC', p.project_id, b.budget_version_id, p.PROJECT_CURRENCY_CODE
from pa_budget_versions_olap_tmp1 b, pa_projects_all p
where b.project_id = p.project_id
and p.PROJFUNC_CURRENCY_CODE <> p.PROJECT_CURRENCY_CODE;
INSERT INTO PJI_FM_EXTR_PLNVER4
(
WORKER_ID ,
PROJECT_ID ,
PLAN_VERSION_ID ,
WBS_STRUCT_VERSION_ID ,
RBS_STRUCT_VERSION_ID ,
PLAN_TYPE_CODE ,
PLAN_TYPE_ID ,
TIME_PHASED_TYPE_CODE ,
TIME_DANGLING_FLAG ,
RATE_DANGLING_FLAG ,
PROJECT_TYPE_CLASS ,
WP_FLAG ,
CURRENT_FLAG ,
ORIGINAL_FLAG ,
CURRENT_ORIGINAL_FLAG ,
BASELINED_FLAG ,
SECONDARY_RBS_FLAG ,
LP_FLAG
)
SELECT
1
, bv.project_id project_id
, bv.budget_version_id plan_version_id
, DECODE ( NVL(bv.wp_version_flag, 'N')
, 'Y', bv.project_structure_version_id
, PA_PROJECT_STRUCTURE_UTILS.GET_FIN_STRUC_VER_ID(bv.project_id) -- -9999 --
) wbs_struct_version_id
, fpo.rbs_version_id rbs_struct_version_id
-- , to_char(fpo.fin_plan_type_id) plan_type_code
, DECODE (bv.version_type, 'COST' ,'C' , 'REVENUE', 'R', 'A') plan_type_code /* 4471527 */
, fpo.fin_plan_type_id plan_type_id
, DECODE(bv.version_type
, 'ALL', fpo.all_time_phased_code
, 'COST', fpo.cost_time_phased_code
, 'REVENUE', fpo.revenue_time_phased_code
) time_phased_type_code
, NULL time_dangling_flag -- to be used for dangling check.
, NULL rate_dangling_flag -- to be used for dangling check.
, NULL PROJECT_TYPE_CLASS
, NVL(bv.wp_version_flag, 'N') is_wp_flag
, bv.current_flag current_flag
, bv.original_flag original_flag
, bv.current_original_flag current_original_flag
, DECODE(bv.baselined_date, NULL, 'N', 'Y') baselined_flag
, 'N' SECONDARY_RBS_FLAG
, DECODE( NVL(bv.wp_version_flag, 'N')
, 'Y'
, DECODE(bv.project_structure_version_id
, PA_PROJECT_STRUCTURE_UTILS.GET_LATEST_WP_VERSION( bv.project_id) -- 4682341
-- , PA_PROJECT_STRUCTURE_UTILS.GET_FIN_STRUC_VER_ID(bv.project_id)
, 'Y'
, 'N')
, 'N'
) lp_flag
FROM
pa_budget_versions bv -- @pjdev115 bv
, pa_proj_fp_options fpo -- @pjdev115 fpo
, pa_projects_all ppa -- @pjdev115 ppa
, pa_budget_versions_olap_tmp1 b1
WHERE 1=1
AND ppa.project_id = bv.project_id
AND bv.budget_version_id = b1.budget_version_id
AND bv.version_type is not NULL -- COST, REVENUE, etc. Should not be null.
AND bv.fin_plan_type_id is not NULL -- Old budgets model data is not picked up with this condition.
AND fpo.project_id = bv.project_id
AND bv.fin_plan_type_id = fpo.fin_plan_type_id
AND bv.budget_version_id = fpo.fin_plan_version_id
AND bv.fin_plan_type_id <> 10 /* Added for bug 8708651*/
AND fpo.fin_plan_option_level_code = 'PLAN_VERSION' -- Other values are: plan type and project.
AND bv.version_type IN ( 'ALL' , 'COST' , 'REVENUE');
INSERT INTO pji_time_cal_period
(
CAL_PERIOD_ID,
CAL_QTR_ID,
CALENDAR_ID,
SEQUENCE,
NAME ,
START_DATE,
END_DATE,
CREATION_DATE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATED_BY,
LAST_UPDATE_LOGIN
)
SELECT -1 /*-x_max_project_id*/ cal_period_id
, -1 /*-x_max_project_id*/ cal_qtr_id
, -1 /*-x_max_project_id*/ calendar_id
, -1 /*-x_max_project_id*/ SEQUENCE
, 'XXX' name
, trunc(sysdate) start_date
, trunc(sysdate) end_date
, sysdate creation_date
, sysdate last_update_date
, -1 last_updated_by
, -1 created_by
, -1 last_update_login
FROM DUAL;
INSERT INTO pji_fp_aggr_pjp1
(
WORKER_ID,
PROJECT_ID
, PROJECT_ORG_ID
, PROJECT_ORGANIZATION_ID
, PROJECT_ELEMENT_ID
, TIME_ID
, PERIOD_TYPE_ID
, CALENDAR_TYPE
, RBS_AGGR_LEVEL
, WBS_ROLLUP_FLAG
, PRG_ROLLUP_FLAG
, CURR_RECORD_TYPE_ID
, CURRENCY_CODE
, RBS_ELEMENT_ID
, RBS_VERSION_ID
, PLAN_VERSION_ID
, PLAN_TYPE_ID
, 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
, LINE_TYPE
, RATE_DANGLING_FLAG
, TIME_DANGLING_FLAG
-- , START_DATE
-- , END_DATE
, PRG_LEVEL
,PLAN_TYPE_CODE
--, cbs_version_id
--, cbs_element_id
)
select
--p1.project_group ,
--p1.olap_group,
10,
b.project_id
, p1.org_id
, 1
--, ra.task_id,
, DECODE(ra.task_id, 0, -1, ra.task_id), --satya
decode(DECODE(b1.version_type
, 'ALL', fpo.all_time_phased_code
, 'COST', fpo.cost_time_phased_code
, 'REVENUE', fpo.revenue_time_phased_code
), 'N'
, -1, cal_period_id
) time_id,
decode(DECODE(b1.version_type
, 'ALL', fpo.all_time_phased_code
, 'COST', fpo.cost_time_phased_code
, 'REVENUE', fpo.revenue_time_phased_code
),
'P', 32
, 'G', 32
, 'N', 2048
) period_type_id,
decode(DECODE(b1.version_type
, 'ALL', fpo.all_time_phased_code
, 'COST', fpo.cost_time_phased_code
, 'REVENUE', fpo.revenue_time_phased_code
),
'P', 'P'
, 'G', 'G'
, 'N', 'A'
) calendar_type,
'L' RBS_AGGR_LEVEL
, 'N' WBS_ROLLUP_FLAG
, 'N' PRG_ROLLUP_FLAG
, 1 CURR_RECORD_TYPE -- curr code missing. --satya
, b.currency_code
, ra.RBS_ELEMENT_ID
, fpo.RBS_VERSION_ID
, b.BUDGET_VERSION_ID
, fpo.fin_plan_type_id
, decode(b.curr_type, 'ALL',bl.raw_cost,'PFC',bl.RAW_COST,'PC',bl.PROJECT_RAW_COST)
, decode(b.curr_type, 'ALL',bl.burdened_cost,'PFC',bl.burdened_cost,'PC',bl.PROJECT_BURDENED_COST)
, decode(b.curr_type, 'ALL',bl.revenue,'PFC',bl.revenue,'PC',bl.PROJECT_REVENUE),
DECODE ( ra.rate_based_flag, 'Y', decode(b.curr_type, 'ALL',bl.raw_cost,'PFC',bl.RAW_COST,'PC',bl.PROJECT_RAW_COST), 0 ) BILL_RAW_COST
, DECODE ( ra.rate_based_flag, 'Y', decode(b.curr_type, 'ALL',bl.burdened_cost,'PFC',bl.burdened_cost,'PC',bl.PROJECT_BURDENED_COST), 0 ) BILL_BRDN_COST
, DECODE ( ra.rate_based_flag || ra.resource_class_code, 'YPEOPLE', decode(b.curr_type, 'ALL',bl.raw_cost,'PFC',bl.RAW_COST,'PC',bl.PROJECT_RAW_COST), 0 ) BILL_LABOR_RAW_COST
, DECODE ( ra.rate_based_flag || ra.resource_class_code, 'YPEOPLE' , decode(b.curr_type, 'ALL',bl.burdened_cost,'PFC',bl.burdened_cost,'PC',bl.PROJECT_BURDENED_COST), 0 ) BILL_LABOR_BRDN_COST
, DECODE ( ra.rate_based_flag || ra.resource_class_code , 'YPEOPLE', DECODE ( ra.rate_based_flag , 'Y' , bl.quantity, 0 ) , 0 ) BILL_LABOR_HRS
, DECODE ( ra.resource_class_code, g_equip_resclass_code , decode(b.curr_type, 'ALL',bl.raw_cost,'PFC',bl.RAW_COST,'PC',bl.PROJECT_RAW_COST), 0 ) EQUIPMENT_RAW_COST
, DECODE ( ra.resource_class_code, g_equip_resclass_code , decode(b.curr_type, 'ALL',bl.burdened_cost,'PFC',bl.burdened_cost,'PC',bl.PROJECT_BURDENED_COST), 0 ) EQUIPMENT_BRDN_COST
, DECODE ( ra.rate_based_flag, 'Y', decode(b.curr_type, 'ALL',bl.raw_cost,'PFC',bl.RAW_COST,'PC',bl.PROJECT_RAW_COST), 0 ) CAPITALIZABLE_RAW_COST
, DECODE ( ra.rate_based_flag, 'Y', decode(b.curr_type, 'ALL',bl.burdened_cost,'PFC',bl.burdened_cost,'PC',bl.PROJECT_BURDENED_COST), 0 ) CAPITALIZABLE_BRDN_COST
, DECODE ( ra.resource_class_code, g_people_resclass_code, decode(b.curr_type, 'ALL',bl.raw_cost,'PFC',bl.RAW_COST,'PC',bl.PROJECT_RAW_COST), 0 ) LABOR_RAW_COST
, DECODE ( ra.resource_class_code, g_people_resclass_code, decode(b.curr_type, 'ALL',bl.burdened_cost,'PFC',bl.burdened_cost,'PC',bl.PROJECT_BURDENED_COST), 0 ) LABOR_BRDN_COST
, DECODE ( ra.resource_class_code, g_people_resclass_code,
DECODE ( ra.rate_based_flag, 'Y',bl.quantity,0), 0 ) labor_hrs -- bug 6039785
, DECODE ( ra.resource_class_code, g_people_resclass_code, decode(b.curr_type, 'ALL',bl.revenue,'PFC',bl.revenue,'PC',bl.PROJECT_REVENUE), 0 ) LABOR_REVENUE
, DECODE ( ra.resource_class_code, g_equip_resclass_code,
DECODE ( ra.rate_based_flag, 'Y',bl.quantity,0)) EQUIPMENT_HOURS -- bug 6039785
, DECODE ( ra.rate_based_flag || ra.resource_class_code, 'YEQUIPMENT',
DECODE ( ra.rate_based_flag , 'Y' , bl.quantity, 0 ) , 0 ) BILLABLE_EQUIPMENT_HOURS -- bug 6039785
, to_number(NULL) SUP_INV_COMMITTED_COST
, to_number(NULL) PO_COMMITTED_COST
, to_number(NULL) PR_COMMITTED_COST
, to_number(NULL) OTH_COMMITTED_COST
, DECODE ( ra.resource_class_code, g_people_resclass_code,
DECODE ( ra.rate_based_flag , 'Y',
DECODE ( ra.rate_based_flag, 'Y',bl.init_quantity,0), 0 ), 0 ) ACT_LABOR_HRS -- bug 6039785
, DECODE ( ra.resource_class_code, g_equip_resclass_code,
DECODE ( ra.rate_based_flag , 'Y',
DECODE ( ra.rate_based_flag, 'Y',bl.init_quantity,0), 0 ), 0 ) ACT_EQUIP_HOURS -- bug 6039785
, DECODE ( ra.resource_class_code, g_people_resclass_code, decode(b.curr_type, 'ALL',bl.init_burdened_cost,'PFC',bl.init_burdened_cost,'PC',bl.PROJECT_init_burdened_cost), 0 ) ACT_LABOR_BRDN_COST
, DECODE ( ra.resource_class_code, g_equip_resclass_code , decode(b.curr_type, 'ALL',bl.init_burdened_cost,'PFC',bl.init_burdened_cost,'PC',bl.PROJECT_init_burdened_cost), 0 ) ACT_EQUIPMENT_BRDN_COST
, decode(b.curr_type, 'ALL',bl.init_burdened_cost,'PFC',bl.init_burdened_cost,'PC',bl.PROJECT_init_burdened_cost) ACT_BRDN_COST
, decode(b.curr_type, 'ALL',bl.init_raw_cost,'PFC',bl.init_raw_cost,'PC',bl.PROJECT_init_raw_cost) ACT_RAW_COST
, decode(b.curr_type, 'ALL',bl.init_revenue,'PFC',bl.init_revenue,'PC',bl.PROJECT_init_revenue) ACT_REVENUE
, DECODE ( ra.resource_class_code, g_people_resclass_code, decode(b.curr_type, 'ALL',bl.init_raw_cost,'PFC',bl.init_raw_cost,'PC',bl.PROJECT_init_raw_cost), 0 ) ACT_LABOR_RAW_COST
, DECODE ( ra.resource_class_code, g_equip_resclass_code , decode(b.curr_type, 'ALL',bl.init_raw_cost,'PFC',bl.init_raw_cost,'PC',bl.PROJECT_init_raw_cost), 0 ) ACT_EQUIPMENT_RAW_COST
, DECODE ( ra.resource_class_code, g_people_resclass_code,
DECODE ( ra.rate_based_flag , 'Y',
DECODE ( ra.rate_based_flag, 'Y',bl.quantity - NVL(bl.init_quantity, 0),0), 0 ), 0 ) ETC_LABOR_HRS -- bug 6039785
, DECODE ( ra.resource_class_code, g_equip_resclass_code,
DECODE ( ra.rate_based_flag , 'Y',
DECODE ( ra.rate_based_flag, 'Y',bl.quantity - NVL(bl.init_quantity,0), 0 ), 0 ) ) ETC_EQUIP_HOURS -- bug 6039785
, DECODE ( ra.resource_class_code, g_people_resclass_code, decode(b.curr_type, 'ALL', bl.burdened_cost - NVL(bl.init_burdened_cost, 0),'PFC', bl.burdened_cost - NVL(bl.init_burdened_cost, 0),
'PC',bl.project_raw_cost - NVL(bl.project_init_raw_cost, 0), 0 )) ETC_LABOR_BRDN_COST
, DECODE ( ra.resource_class_code, g_equip_resclass_code, decode(b.curr_type, 'ALL', bl.burdened_cost - NVL(bl.init_burdened_cost, 0),'PFC', bl.burdened_cost - NVL(bl.init_burdened_cost, 0),
'PC',bl.project_raw_cost - NVL(bl.project_init_raw_cost, 0), 0 )) ETC_EQUIP_BRDN_COST
, decode(b.curr_type, 'ALL', bl.burdened_cost - NVL(bl.init_burdened_cost, 0),'PFC', bl.burdened_cost - NVL(bl.init_burdened_cost, 0),
'PC',bl.project_raw_cost - NVL(bl.project_init_raw_cost, 0), 0 ) ETC_BRDN_COST
, decode(b.curr_type, 'ALL', bl.raw_cost - NVL(bl.init_raw_cost, 0),'PFC', bl.raw_cost - NVL(bl.init_raw_cost, 0),'PC',bl.project_raw_cost - NVL(bl.project_init_raw_cost, 0), 0 ) ETC_RAW_COST
, DECODE ( ra.resource_class_code, g_people_resclass_code, decode(b.curr_type, 'ALL', bl.raw_cost - NVL(bl.init_raw_cost, 0),'PFC', bl.raw_cost - NVL(bl.init_raw_cost, 0),
'PC',bl.project_raw_cost - NVL(bl.project_init_raw_cost, 0), 0 ) ) ETC_LABOR_RAW_COST
, DECODE ( ra.resource_class_code, g_equip_resclass_code, decode(b.curr_type, 'ALL', bl.raw_cost - NVL(bl.init_raw_cost, 0),'PFC', bl.raw_cost - NVL(bl.init_raw_cost, 0),
'PC',bl.project_raw_cost - NVL(bl.project_init_raw_cost, 0), 0 ) ) ETC_EQUIP_RAW_COST
, to_number(NULL) CUSTOM1
, to_number(NULL) CUSTOM2
, to_number(NULL) CUSTOM3
, to_number(NULL) CUSTOM4
, to_number(NULL) CUSTOM5
, to_number(NULL) CUSTOM6
, to_number(NULL) CUSTOM7
, to_number(NULL) CUSTOM8
, to_number(NULL) CUSTOM9
, to_number(NULL) CUSTOM10
, to_number(NULL) CUSTOM11
, to_number(NULL) CUSTOM12
, to_number(NULL) CUSTOM13
, to_number(NULL) CUSTOM14
, to_number(NULL) CUSTOM15
, DECODE(DECODE(b1.version_type
, 'ALL', fpo.all_time_phased_code
, 'COST', fpo.cost_time_phased_code
, 'REVENUE', fpo.revenue_time_phased_code
), 'P', 'OF', 'G', 'OF', 'N', 'NTR', 'X')
, NULL time_dangling_flag
, NULL rate_dangling_flag
-- , plr.start_date
-- , plr.end_date
, 1 prg_level
,DECODE (b1.version_type, 'COST' ,'C' , 'REVENUE', 'R', 'A') plan_type_code
-- , nvl(p1.cbs_version_id,-1) cbs_version_id --satya
-- , nvl(ra.cbs_element_id,-1) cbs_element_id
from
pa_budget_versions_olap_tmp2 b ,
pa_budget_versions b1,
pa_resource_assignments ra,
pa_budget_lines bl,
pji_time_cal_period per,
pa_projects_all p1,
pa_proj_fp_options fpo,
PJI_ORG_EXTR_INFO inf
where
b.budget_version_id = b1.budget_version_id
and b.budget_version_id = ra.budget_version_id
and ra.resource_assignment_id = bl.resource_assignment_id
and p1.project_id = b.project_id
and b.project_id = fpo.project_id
and decode(DECODE(b1.version_type
, 'ALL', fpo.all_time_phased_code
, 'COST', fpo.cost_time_phased_code
, 'REVENUE', fpo.revenue_time_phased_code
), 'P'
, inf.pa_calendar_id
, 'G'
, inf.gl_calendar_id, -b1.project_id
) = per.calendar_id
AND nvl(bl.period_name,'XXX') = per.name
AND bl.budget_version_id = fpo.fin_plan_version_id
--AND fpo.time_phased_type_code IN ('P', 'G')
AND fpo.project_id = p1.project_id
AND p1.org_id = inf.org_id
AND b1.fin_plan_type_id = fpo.fin_plan_type_id
and fpo.fin_plan_type_id <> 10
AND b.budget_version_id = fpo.fin_plan_version_id
AND fpo.fin_plan_option_level_code = 'PLAN_VERSION' -- Other values are: plan type and project.
AND b1.version_type IN ( 'ALL' , 'COST' , 'REVENUE')
AND bl.budget_version_id = b.budget_version_id;
INSERT INTO pji_fp_aggr_pjp1
(
WORKER_ID,
PROJECT_ID
, PROJECT_ORG_ID
, PROJECT_ORGANIZATION_ID
, PROJECT_ELEMENT_ID
, TIME_ID
, PERIOD_TYPE_ID
, CALENDAR_TYPE
, RBS_AGGR_LEVEL
, WBS_ROLLUP_FLAG
, PRG_ROLLUP_FLAG
, CURR_RECORD_TYPE_ID
, CURRENCY_CODE
, RBS_ELEMENT_ID
, RBS_VERSION_ID
, PLAN_VERSION_ID
, PLAN_TYPE_ID
, 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
, LINE_TYPE
, RATE_DANGLING_FLAG
, TIME_DANGLING_FLAG
-- , START_DATE
-- , END_DATE
, PRG_LEVEL
,PLAN_TYPE_CODE
--, cbs_version_id
--, cbs_element_id
)
select
--p1.project_group ,
--p1.olap_group,
10,
b.project_id
, p1.org_id
, 1
--, ra.task_id,
, DECODE(ra.task_id, 0, -1, ra.task_id), --satya
decode(DECODE(b1.version_type
, 'ALL', fpo.all_time_phased_code
, 'COST', fpo.cost_time_phased_code
, 'REVENUE', fpo.revenue_time_phased_code
), 'N'
, -1, cal_period_id
) time_id,
decode(DECODE(b1.version_type
, 'ALL', fpo.all_time_phased_code
, 'COST', fpo.cost_time_phased_code
, 'REVENUE', fpo.revenue_time_phased_code
),
'P', 32
, 'G', 32
, 'N', 2048
) period_type_id,
decode(DECODE(b1.version_type
, 'ALL', fpo.all_time_phased_code
, 'COST', fpo.cost_time_phased_code
, 'REVENUE', fpo.revenue_time_phased_code
),
'P', 'P'
, 'G', 'G'
, 'N', 'A'
) calendar_type,
'L' RBS_AGGR_LEVEL
, 'N' WBS_ROLLUP_FLAG
, 'N' PRG_ROLLUP_FLAG
, 1 CURR_RECORD_TYPE -- curr code missing. --satya
, b.currency_code
, -1
, -1
, b.BUDGET_VERSION_ID
, fpo.fin_plan_type_id
, decode(b.curr_type, 'ALL',bl.raw_cost,'PFC',bl.RAW_COST,'PC',bl.PROJECT_RAW_COST)
, decode(b.curr_type, 'ALL',bl.burdened_cost,'PFC',bl.burdened_cost,'PC',bl.PROJECT_BURDENED_COST)
, decode(b.curr_type, 'ALL',bl.revenue,'PFC',bl.revenue,'PC',bl.PROJECT_REVENUE),
DECODE ( ra.rate_based_flag, 'Y', decode(b.curr_type, 'ALL',bl.raw_cost,'PFC',bl.RAW_COST,'PC',bl.PROJECT_RAW_COST), 0 ) BILL_RAW_COST
, DECODE ( ra.rate_based_flag, 'Y', decode(b.curr_type, 'ALL',bl.burdened_cost,'PFC',bl.burdened_cost,'PC',bl.PROJECT_BURDENED_COST), 0 ) BILL_BRDN_COST
, DECODE ( ra.rate_based_flag || ra.resource_class_code, 'YPEOPLE', decode(b.curr_type, 'ALL',bl.raw_cost,'PFC',bl.RAW_COST,'PC',bl.PROJECT_RAW_COST), 0 ) BILL_LABOR_RAW_COST
, DECODE ( ra.rate_based_flag || ra.resource_class_code, 'YPEOPLE' , decode(b.curr_type, 'ALL',bl.burdened_cost,'PFC',bl.burdened_cost,'PC',bl.PROJECT_BURDENED_COST), 0 ) BILL_LABOR_BRDN_COST
, DECODE ( ra.rate_based_flag || ra.resource_class_code , 'YPEOPLE', DECODE ( ra.rate_based_flag , 'Y' , bl.quantity, 0 ) , 0 ) BILL_LABOR_HRS
, DECODE ( ra.resource_class_code, g_equip_resclass_code , decode(b.curr_type, 'ALL',bl.raw_cost,'PFC',bl.RAW_COST,'PC',bl.PROJECT_RAW_COST), 0 ) EQUIPMENT_RAW_COST
, DECODE ( ra.resource_class_code, g_equip_resclass_code , decode(b.curr_type, 'ALL',bl.burdened_cost,'PFC',bl.burdened_cost,'PC',bl.PROJECT_BURDENED_COST), 0 ) EQUIPMENT_BRDN_COST
, DECODE ( ra.rate_based_flag, 'Y', decode(b.curr_type, 'ALL',bl.raw_cost,'PFC',bl.RAW_COST,'PC',bl.PROJECT_RAW_COST), 0 ) CAPITALIZABLE_RAW_COST
, DECODE ( ra.rate_based_flag, 'Y', decode(b.curr_type, 'ALL',bl.burdened_cost,'PFC',bl.burdened_cost,'PC',bl.PROJECT_BURDENED_COST), 0 ) CAPITALIZABLE_BRDN_COST
, DECODE ( ra.resource_class_code, g_people_resclass_code, decode(b.curr_type, 'ALL',bl.raw_cost,'PFC',bl.RAW_COST,'PC',bl.PROJECT_RAW_COST), 0 ) LABOR_RAW_COST
, DECODE ( ra.resource_class_code, g_people_resclass_code, decode(b.curr_type, 'ALL',bl.burdened_cost,'PFC',bl.burdened_cost,'PC',bl.PROJECT_BURDENED_COST), 0 ) LABOR_BRDN_COST
, DECODE ( ra.resource_class_code, g_people_resclass_code,
DECODE ( ra.rate_based_flag, 'Y',bl.quantity,0), 0 ) labor_hrs -- bug 6039785
, DECODE ( ra.resource_class_code, g_people_resclass_code, decode(b.curr_type, 'ALL',bl.revenue,'PFC',bl.revenue,'PC',bl.PROJECT_REVENUE), 0 ) LABOR_REVENUE
, DECODE ( ra.resource_class_code, g_equip_resclass_code,
DECODE ( ra.rate_based_flag, 'Y',bl.quantity,0)) EQUIPMENT_HOURS -- bug 6039785
, DECODE ( ra.rate_based_flag || ra.resource_class_code, 'YEQUIPMENT',
DECODE ( ra.rate_based_flag , 'Y' , bl.quantity, 0 ) , 0 ) BILLABLE_EQUIPMENT_HOURS -- bug 6039785
, to_number(NULL) SUP_INV_COMMITTED_COST
, to_number(NULL) PO_COMMITTED_COST
, to_number(NULL) PR_COMMITTED_COST
, to_number(NULL) OTH_COMMITTED_COST
, DECODE ( ra.resource_class_code, g_people_resclass_code,
DECODE ( ra.rate_based_flag , 'Y',
DECODE ( ra.rate_based_flag, 'Y',bl.init_quantity,0), 0 ), 0 ) ACT_LABOR_HRS -- bug 6039785
, DECODE ( ra.resource_class_code, g_equip_resclass_code,
DECODE ( ra.rate_based_flag , 'Y',
DECODE ( ra.rate_based_flag, 'Y',bl.init_quantity,0), 0 ), 0 ) ACT_EQUIP_HOURS -- bug 6039785
, DECODE ( ra.resource_class_code, g_people_resclass_code, decode(b.curr_type, 'ALL',bl.init_burdened_cost,'PFC',bl.init_burdened_cost,'PC',bl.PROJECT_init_burdened_cost), 0 ) ACT_LABOR_BRDN_COST
, DECODE ( ra.resource_class_code, g_equip_resclass_code , decode(b.curr_type, 'ALL',bl.init_burdened_cost,'PFC',bl.init_burdened_cost,'PC',bl.PROJECT_init_burdened_cost), 0 ) ACT_EQUIPMENT_BRDN_COST
, decode(b.curr_type, 'ALL',bl.init_burdened_cost,'PFC',bl.init_burdened_cost,'PC',bl.PROJECT_init_burdened_cost) ACT_BRDN_COST
, decode(b.curr_type, 'ALL',bl.init_raw_cost,'PFC',bl.init_raw_cost,'PC',bl.PROJECT_init_raw_cost) ACT_RAW_COST
, decode(b.curr_type, 'ALL',bl.init_revenue,'PFC',bl.init_revenue,'PC',bl.PROJECT_init_revenue) ACT_REVENUE
, DECODE ( ra.resource_class_code, g_people_resclass_code, decode(b.curr_type, 'ALL',bl.init_raw_cost,'PFC',bl.init_raw_cost,'PC',bl.PROJECT_init_raw_cost), 0 ) ACT_LABOR_RAW_COST
, DECODE ( ra.resource_class_code, g_equip_resclass_code , decode(b.curr_type, 'ALL',bl.init_raw_cost,'PFC',bl.init_raw_cost,'PC',bl.PROJECT_init_raw_cost), 0 ) ACT_EQUIPMENT_RAW_COST
, DECODE ( ra.resource_class_code, g_people_resclass_code,
DECODE ( ra.rate_based_flag , 'Y',
DECODE ( ra.rate_based_flag, 'Y',bl.quantity - NVL(bl.init_quantity, 0),0), 0 ), 0 ) ETC_LABOR_HRS -- bug 6039785
, DECODE ( ra.resource_class_code, g_equip_resclass_code,
DECODE ( ra.rate_based_flag , 'Y',
DECODE ( ra.rate_based_flag, 'Y',bl.quantity - NVL(bl.init_quantity,0), 0 ), 0 ) ) ETC_EQUIP_HOURS -- bug 6039785
, DECODE ( ra.resource_class_code, g_people_resclass_code, decode(b.curr_type, 'ALL', bl.burdened_cost - NVL(bl.init_burdened_cost, 0),'PFC', bl.burdened_cost - NVL(bl.init_burdened_cost, 0),
'PC',bl.project_raw_cost - NVL(bl.project_init_raw_cost, 0), 0 )) ETC_LABOR_BRDN_COST
, DECODE ( ra.resource_class_code, g_equip_resclass_code, decode(b.curr_type, 'ALL', bl.burdened_cost - NVL(bl.init_burdened_cost, 0),'PFC', bl.burdened_cost - NVL(bl.init_burdened_cost, 0),
'PC',bl.project_raw_cost - NVL(bl.project_init_raw_cost, 0), 0 )) ETC_EQUIP_BRDN_COST
, decode(b.curr_type, 'ALL', bl.burdened_cost - NVL(bl.init_burdened_cost, 0),'PFC', bl.burdened_cost - NVL(bl.init_burdened_cost, 0),
'PC',bl.project_raw_cost - NVL(bl.project_init_raw_cost, 0), 0 ) ETC_BRDN_COST
, decode(b.curr_type, 'ALL', bl.raw_cost - NVL(bl.init_raw_cost, 0),'PFC', bl.raw_cost - NVL(bl.init_raw_cost, 0),'PC',bl.project_raw_cost - NVL(bl.project_init_raw_cost, 0), 0 ) ETC_RAW_COST
, DECODE ( ra.resource_class_code, g_people_resclass_code, decode(b.curr_type, 'ALL', bl.raw_cost - NVL(bl.init_raw_cost, 0),'PFC', bl.raw_cost - NVL(bl.init_raw_cost, 0),
'PC',bl.project_raw_cost - NVL(bl.project_init_raw_cost, 0), 0 ) ) ETC_LABOR_RAW_COST
, DECODE ( ra.resource_class_code, g_equip_resclass_code, decode(b.curr_type, 'ALL', bl.raw_cost - NVL(bl.init_raw_cost, 0),'PFC', bl.raw_cost - NVL(bl.init_raw_cost, 0),
'PC',bl.project_raw_cost - NVL(bl.project_init_raw_cost, 0), 0 ) ) ETC_EQUIP_RAW_COST
, to_number(NULL) CUSTOM1
, to_number(NULL) CUSTOM2
, to_number(NULL) CUSTOM3
, to_number(NULL) CUSTOM4
, to_number(NULL) CUSTOM5
, to_number(NULL) CUSTOM6
, to_number(NULL) CUSTOM7
, to_number(NULL) CUSTOM8
, to_number(NULL) CUSTOM9
, to_number(NULL) CUSTOM10
, to_number(NULL) CUSTOM11
, to_number(NULL) CUSTOM12
, to_number(NULL) CUSTOM13
, to_number(NULL) CUSTOM14
, to_number(NULL) CUSTOM15
, DECODE(DECODE(b1.version_type
, 'ALL', fpo.all_time_phased_code
, 'COST', fpo.cost_time_phased_code
, 'REVENUE', fpo.revenue_time_phased_code
), 'P', 'OF', 'G', 'OF', 'N', 'NTR', 'X')
, NULL time_dangling_flag
, NULL rate_dangling_flag
-- , plr.start_date
-- , plr.end_date
, 1 prg_level
,DECODE (b1.version_type, 'COST' ,'C' , 'REVENUE', 'R', 'A') plan_type_code
-- , nvl(p1.cbs_version_id,-1) cbs_version_id --satya
-- , nvl(ra.cbs_element_id,-1) cbs_element_id
from
pa_budget_versions_olap_tmp2 b ,
pa_budget_versions b1,
pa_resource_assignments ra,
pa_budget_lines bl,
pji_time_cal_period per,
pa_projects_all p1,
pa_proj_fp_options fpo,
PJI_ORG_EXTR_INFO inf
where
b.budget_version_id = b1.budget_version_id
and b.budget_version_id = ra.budget_version_id
and ra.resource_assignment_id = bl.resource_assignment_id
and p1.project_id = b.project_id
and b.project_id = fpo.project_id
and decode(DECODE(b1.version_type
, 'ALL', fpo.all_time_phased_code
, 'COST', fpo.cost_time_phased_code
, 'REVENUE', fpo.revenue_time_phased_code
), 'P'
, inf.pa_calendar_id
, 'G'
, inf.gl_calendar_id, -b1.project_id
) = per.calendar_id
AND nvl(bl.period_name,'XXX') = per.name
AND bl.budget_version_id = fpo.fin_plan_version_id
--AND fpo.time_phased_type_code IN ('P', 'G')
AND fpo.project_id = p1.project_id
AND p1.org_id = inf.org_id
AND b1.fin_plan_type_id = fpo.fin_plan_type_id
and fpo.fin_plan_type_id <> 10
AND b.budget_version_id = fpo.fin_plan_version_id
AND fpo.fin_plan_option_level_code = 'PLAN_VERSION' -- Other values are: plan type and project.
AND b1.version_type IN ( 'ALL' , 'COST' , 'REVENUE')
AND bl.budget_version_id = b.budget_version_id;
insert into PA_OLAP_STAGE(
OLAP_GROUP ,
PROJECT_ID ,
PROJECT_ELEMENT_ID ,
TIME_ID ,
CALENDAR_TYPE ,
CURRENCY_CODE ,
RBS_ELEMENT_ID ,
RBS_VERSION_ID ,
PLAN_VERSION_ID ,
-- CBS_VERSION_ID,
-- CBS_ELEMENT_ID,
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 ,
CUSTOM1 ,
CUSTOM2 ,
CUSTOM3 ,
CUSTOM4 ,
CUSTOM5 ,
CUSTOM6 ,
CUSTOM7 ,
CUSTOM8 ,
CUSTOM9 ,
CUSTOM10 ,
CUSTOM11 ,
CUSTOM12 ,
CUSTOM13 ,
CUSTOM14 ,
CUSTOM15 ,
ACT_LABOR_HRS ,
ACT_EQUIP_HRS ,
ACT_LABOR_BRDN_COST ,
ACT_EQUIP_BRDN_COST ,
ACT_BRDN_COST ,
ACT_RAW_COST ,
ACT_REVENUE ,
ETC_LABOR_HRS ,
ETC_EQUIP_HRS ,
ETC_LABOR_BRDN_COST ,
ETC_EQUIP_BRDN_COST ,
ETC_BRDN_COST ,
ETC_RAW_COST ,
ACT_LABOR_RAW_COST ,
ACT_EQUIP_RAW_COST ,
ETC_LABOR_RAW_COST ,
ETC_EQUIP_RAW_COST ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
CREATION_DATE ,
CREATED_BY ,
LAST_UPDATE_LOGIN
)
select
OLAP_GROUP ,
bv.PROJECT_ID ,
PROJECT_ELEMENT_ID ,
TIME_ID ,
CALENDAR_TYPE ,
CURRENCY_CODE ,
nvl(RBS_ELEMENT_ID,-1) , --satya2
nvl(RBS_VERSION_ID,-1) , --satya2
PLAN_VERSION_ID ,
-- nvl(p.CBS_VERSION_ID,-1), --satya2
-- nvl(bv.CBS_ELEMENT_ID, -1), --satya2
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 ,
CUSTOM1 ,
CUSTOM2 ,
CUSTOM3 ,
CUSTOM4 ,
CUSTOM5 ,
CUSTOM6 ,
CUSTOM7 ,
CUSTOM8 ,
CUSTOM9 ,
CUSTOM10 ,
CUSTOM11 ,
CUSTOM12 ,
CUSTOM13 ,
CUSTOM14 ,
CUSTOM15 ,
ACT_LABOR_HRS ,
ACT_EQUIP_HRS ,
ACT_LABOR_BRDN_COST ,
ACT_EQUIP_BRDN_COST ,
ACT_BRDN_COST ,
ACT_RAW_COST ,
ACT_REVENUE ,
ETC_LABOR_HRS ,
ETC_EQUIP_HRS ,
ETC_LABOR_BRDN_COST ,
ETC_EQUIP_BRDN_COST ,
ETC_BRDN_COST ,
ETC_RAW_COST ,
ACT_LABOR_RAW_COST ,
ACT_EQUIP_RAW_COST ,
ETC_LABOR_RAW_COST ,
ETC_EQUIP_RAW_COST ,
sysdate ,
-1 ,
sysdate ,
-1 ,
-1
from pji_fp_aggr_pjp1 bv, pa_projects_all p
where bv.project_id = p.project_id;
delete from pji_time_cal_period where cal_period_id = -1;
INSERT INTO pa_olap_stage(
OLAP_GROUP ,
PROJECT_ID ,
PROJECT_ELEMENT_ID ,
TIME_ID ,
CALENDAR_TYPE ,
CURRENCY_CODE ,
RBS_ELEMENT_ID ,
RBS_VERSION_ID ,
PLAN_VERSION_ID ,
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 ,
CUSTOM1 ,
CUSTOM2 ,
CUSTOM3 ,
CUSTOM4 ,
CUSTOM5 ,
CUSTOM6 ,
CUSTOM7 ,
CUSTOM8 ,
CUSTOM9 ,
CUSTOM10 ,
CUSTOM11 ,
CUSTOM12 ,
CUSTOM13 ,
CUSTOM14 ,
CUSTOM15 ,
CBS_VERSION_ID ,
CBS_ELEMENT_ID ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
CREATION_DATE ,
CREATED_BY ,
LAST_UPDATE_LOGIN
)
select
p.olap_group,
tmp1.PROJECT_ID,
tmp1.TASK_ID,
tmp1.RECVR_PERIOD_ID,
substrb(tmp1.RECVR_PERIOD_TYPE, 1,1),
p.project_currency_code,
-1, --satya2
-1,
-1,
tmp1.PRJ_RAW_COST,
tmp1.PRJ_BRDN_COST,
tmp1.PRJ_REVENUE,
decode(tmp1.PROJECT_TYPE_CLASS, 'B', tmp1.PRJ_BILL_RAW_COST, to_number(null)) BILL_RAW_COST,
decode(tmp1.PROJECT_TYPE_CLASS, 'B', tmp1.PRJ_BILL_BRDN_COST, to_number(null)) BILL_BRDN_COST,
decode(tmp1.PROJECT_TYPE_CLASS || '_' || tmp1.RESOURCE_CLASS_ID, 'B_1', tmp1.PRJ_BILL_RAW_COST, to_number(null)) BILL_LABOR_RAW_COST,
decode(tmp1.PROJECT_TYPE_CLASS || '_' || tmp1.RESOURCE_CLASS_ID, 'B_1', tmp1.PRJ_BILL_BRDN_COST, to_number(null)) BILL_LABOR_BRDN_COST,
decode(tmp1.PROJECT_TYPE_CLASS || '_' || tmp1.resource_class_id, 'B_1', tmp1.BILL_QUANTITY, to_number(null)) BILL_LABOR_HRS,
decode(tmp1.resource_class_id, '2', tmp1.PRJ_RAW_COST, to_number(null)) EQUIPMENT_RAW_COST,
decode(tmp1.resource_class_id, '2', tmp1.PRJ_BRDN_COST, to_number(null)) EQUIPMENT_BRDN_COST,
decode(tmp1.PROJECT_TYPE_CLASS, 'C', tmp1.PRJ_BILL_RAW_COST, to_number(null)) CAPITALIZABLE_RAW_COST,
decode(tmp1.PROJECT_TYPE_CLASS, 'C', tmp1.PRJ_BILL_BRDN_COST, to_number(null))CAPITALIZABLE_BRDN_COST,
decode(tmp1.resource_class_id, '1', tmp1.PRJ_RAW_COST, to_number(null)) LABOR_RAW_COST,
decode(tmp1.resource_class_id, '1', tmp1.PRJ_BRDN_COST, to_number(null)) LABOR_BRDN_COST,
decode(tmp1.resource_class_id, '1', tmp1.QUANTITY, to_number(null)) LABOR_HRS,
decode(tmp1.resource_class_id, '1', tmp1.PRJ_REVENUE, to_number(null)) LABOR_REVENUE,
decode(tmp1.resource_class_id, '2', tmp1.QUANTITY, to_number(null)) EQUIPMENT_HOURS,
decode(tmp1.resource_class_id, '2', tmp1.BILL_QUANTITY, to_number(null)) BILLABLE_EQUIPMENT_HOURS,
tmp1.PRJ_SUP_INV_COMMITTED_COST,
tmp1.PRJ_PO_COMMITTED_COST,
tmp1.PRJ_PR_COMMITTED_COST,
tmp1.PRJ_OTH_COMMITTED_COST,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
nvl(tmp1.CBS_VERSION_ID,-1), --satya2
nvl(tmp1.CBS_ELEMENT_ID,-1), --satya2
sysdate ,
-1 ,
sysdate ,
-1 ,
-1
from
(
select -- initial actuals data
bal.TXN_ACCUM_HEADER_ID,
bal.RESOURCE_CLASS_ID,
bal.PROJECT_ID,
bal.PROJECT_TYPE_CLASS,
nvl(bal.TASK_ID, -1) TASK_ID,
bal.RECVR_PERIOD_TYPE,
bal.RECVR_PERIOD_ID,
-1 RBS_VERSION_ID,
bal.TXN_CURRENCY_CODE,
bal.TXN_RAW_COST,
bal.TXN_BILL_RAW_COST,
bal.TXN_BRDN_COST,
bal.TXN_BILL_BRDN_COST,
bal.TXN_REVENUE,
to_number(null) TXN_SUP_INV_COMMITTED_COST,
to_number(null) TXN_PO_COMMITTED_COST,
to_number(null) TXN_PR_COMMITTED_COST,
to_number(null) TXN_OTH_COMMITTED_COST,
bal.PRJ_RAW_COST,
bal.PRJ_BILL_RAW_COST,
bal.PRJ_BRDN_COST,
bal.PRJ_BILL_BRDN_COST,
bal.PRJ_REVENUE,
to_number(null) PRJ_SUP_INV_COMMITTED_COST,
to_number(null) PRJ_PO_COMMITTED_COST,
to_number(null) PRJ_PR_COMMITTED_COST,
to_number(null) PRJ_OTH_COMMITTED_COST,
bal.POU_RAW_COST,
bal.POU_BILL_RAW_COST,
bal.POU_BRDN_COST,
bal.POU_BILL_BRDN_COST,
bal.POU_REVENUE,
to_number(null) POU_SUP_INV_COMMITTED_COST,
to_number(null) POU_PO_COMMITTED_COST,
to_number(null) POU_PR_COMMITTED_COST,
to_number(null) POU_OTH_COMMITTED_COST,
bal.EOU_RAW_COST,
bal.EOU_BILL_RAW_COST,
bal.EOU_BRDN_COST,
bal.EOU_BILL_BRDN_COST,
to_number(null) EOU_SUP_INV_COMMITTED_COST,
to_number(null) EOU_PO_COMMITTED_COST,
to_number(null) EOU_PR_COMMITTED_COST,
to_number(null) EOU_OTH_COMMITTED_COST,
bal.G1_RAW_COST,
bal.G1_BILL_RAW_COST,
bal.G1_BRDN_COST,
bal.G1_BILL_BRDN_COST,
bal.G1_REVENUE,
to_number(null) G1_SUP_INV_COMMITTED_COST,
to_number(null) G1_PO_COMMITTED_COST,
to_number(null) G1_PR_COMMITTED_COST,
to_number(null) G1_OTH_COMMITTED_COST,
bal.G2_RAW_COST,
bal.G2_BILL_RAW_COST,
bal.G2_BRDN_COST,
bal.G2_BILL_BRDN_COST,
bal.G2_REVENUE,
to_number(null) G2_SUP_INV_COMMITTED_COST,
to_number(null) G2_PO_COMMITTED_COST,
to_number(null) G2_PR_COMMITTED_COST,
to_number(null) G2_OTH_COMMITTED_COST,
bal.QUANTITY,
bal.BILL_QUANTITY,
bal.cbs_version_id,
bal.cbs_element_id
from
PA_STAGE1_BATCH_OLAP map,
PJI_FP_TXN_ACCUM bal,
pa_projects_all pa,
pa_pjt_proj_batch batch
where
g_extraction_type in ('FULL', 'PARTIAL') and
-- decode(l_extraction_type,'FULL','Y','PARTIAL',decode(bitand(l_refresh_code,1),1,'Y','N'),'N') ='Y' and -- Bug#5099574
-- l_plan_type_id IS NULL and -- Bug#5099574
bal.PROJECT_ID = map.PROJECT_ID and
bal.project_id = pa.project_id and
pa.project_id = batch.project_id and
pa.pjt_rollup_enabled_flag = 'Y'
union all -- initial commitments data
select
bal.TXN_ACCUM_HEADER_ID,
to_number(null) RESOURCE_CLASS_ID,
bal.PROJECT_ID,
to_char(null) PROJECT_TYPE_CLASS,
nvl(bal.TASK_ID, -1) TASK_ID,
bal.RECVR_PERIOD_TYPE,
bal.RECVR_PERIOD_ID,
-1 RBS_VERSION_ID,
bal.TXN_CURRENCY_CODE,
to_number(null) TXN_RAW_COST,
to_number(null) TXN_BILL_RAW_COST,
to_number(null) TXN_BRDN_COST,
to_number(null) TXN_BILL_BRDN_COST,
to_number(null) TXN_REVENUE,
bal.TXN_SUP_INV_COMMITTED_COST,
bal.TXN_PO_COMMITTED_COST,
bal.TXN_PR_COMMITTED_COST,
bal.TXN_OTH_COMMITTED_COST,
to_number(null) PRJ_RAW_COST,
to_number(null) PRJ_BILL_RAW_COST,
to_number(null) PRJ_BRDN_COST,
to_number(null) PRJ_BILL_BRDN_COST,
to_number(null) PRJ_REVENUE,
bal.PRJ_SUP_INV_COMMITTED_COST,
bal.PRJ_PO_COMMITTED_COST,
bal.PRJ_PR_COMMITTED_COST,
bal.PRJ_OTH_COMMITTED_COST,
to_number(null) POU_RAW_COST,
to_number(null) POU_BILL_RAW_COST,
to_number(null) POU_BRDN_COST,
to_number(null) POU_BILL_BRDN_COST,
to_number(null) POU_REVENUE,
bal.POU_SUP_INV_COMMITTED_COST,
bal.POU_PO_COMMITTED_COST,
bal.POU_PR_COMMITTED_COST,
bal.POU_OTH_COMMITTED_COST,
to_number(null) EOU_RAW_COST,
to_number(null) EOU_BILL_RAW_COST,
to_number(null) EOU_BRDN_COST,
to_number(null) EOU_BILL_BRDN_COST,
bal.EOU_SUP_INV_COMMITTED_COST,
bal.EOU_PO_COMMITTED_COST,
bal.EOU_PR_COMMITTED_COST,
bal.EOU_OTH_COMMITTED_COST,
to_number(null) G1_RAW_COST,
to_number(null) G1_BILL_RAW_COST,
to_number(null) G1_BRDN_COST,
to_number(null) G1_BILL_BRDN_COST,
to_number(null) G1_REVENUE,
bal.G1_SUP_INV_COMMITTED_COST,
bal.G1_PO_COMMITTED_COST,
bal.G1_PR_COMMITTED_COST,
bal.G1_OTH_COMMITTED_COST,
to_number(null) G2_RAW_COST,
to_number(null) G2_BILL_RAW_COST,
to_number(null) G2_BRDN_COST,
to_number(null) G2_BILL_BRDN_COST,
to_number(null) G2_REVENUE,
bal.G2_SUP_INV_COMMITTED_COST,
bal.G2_PO_COMMITTED_COST,
bal.G2_PR_COMMITTED_COST,
bal.G2_OTH_COMMITTED_COST,
to_number(null) QUANTITY,
to_number(null) BILL_QUANTITY,
bal.cbs_version_id,
bal.cbs_element_id
from
PA_STAGE1_BATCH_OLAP map,
PJI_FP_TXN_ACCUM1 bal,
pa_projects_all pa,
pa_pjt_proj_batch batch
where
g_extraction_type in ('FULL', 'PARTIAL') and
-- decode(l_extraction_type,'FULL','Y','PARTIAL',decode(bitand(l_refresh_code,1),1,'Y','N'),'N') ='Y' and -- Bug#5099574
-- l_plan_type_id IS NULL and -- Bug#5099574
bal.PROJECT_ID = map.PROJECT_ID and
bal.project_id = pa.project_id and
pa.project_id = batch.project_id and
pa.pjt_rollup_enabled_flag = 'Y'
union all -- incremental data
select
tmp7.TXN_ACCUM_HEADER_ID,
tmp7.RESOURCE_CLASS_ID,
tmp7.PROJECT_ID,
tmp7.PROJECT_TYPE_CLASS,
nvl(tmp7.TASK_ID, -1) TASK_ID,
tmp7.RECVR_PERIOD_TYPE,
tmp7.RECVR_PERIOD_ID,
-1 RBS_VERSION_ID,
tmp7.TXN_CURRENCY_CODE,
tmp7.TXN_RAW_COST,
tmp7.TXN_BILL_RAW_COST,
tmp7.TXN_BRDN_COST,
tmp7.TXN_BILL_BRDN_COST,
tmp7.TXN_REVENUE,
tmp7.TXN_SUP_INV_COMMITTED_COST,
tmp7.TXN_PO_COMMITTED_COST,
tmp7.TXN_PR_COMMITTED_COST,
tmp7.TXN_OTH_COMMITTED_COST,
tmp7.PRJ_RAW_COST,
tmp7.PRJ_BILL_RAW_COST,
tmp7.PRJ_BRDN_COST,
tmp7.PRJ_BILL_BRDN_COST,
tmp7.PRJ_REVENUE,
decode(nvl(proj_batch.project_id, -1), -1, tmp7.PRJ_SUP_INV_COMMITTED_COST,0),
decode(nvl(proj_batch.project_id, -1), -1, tmp7.PRJ_PO_COMMITTED_COST,0),
decode(nvl(proj_batch.project_id, -1), -1, tmp7.PRJ_PR_COMMITTED_COST,0),
decode(nvl(proj_batch.project_id, -1), -1, tmp7.PRJ_OTH_COMMITTED_COST,0),
tmp7.POU_RAW_COST,
tmp7.POU_BILL_RAW_COST,
tmp7.POU_BRDN_COST,
tmp7.POU_BILL_BRDN_COST,
tmp7.POU_REVENUE,
decode(nvl(proj_batch.project_id, -1), -1, tmp7.POU_SUP_INV_COMMITTED_COST,0),
decode(nvl(proj_batch.project_id, -1), -1, tmp7.POU_PO_COMMITTED_COST,0),
decode(nvl(proj_batch.project_id, -1), -1, tmp7.POU_PR_COMMITTED_COST,0),
decode(nvl(proj_batch.project_id, -1), -1, tmp7.POU_OTH_COMMITTED_COST,0),
tmp7.EOU_RAW_COST,
tmp7.EOU_BILL_RAW_COST,
tmp7.EOU_BRDN_COST,
tmp7.EOU_BILL_BRDN_COST,
decode(nvl(proj_batch.project_id, -1), -1, tmp7.EOU_SUP_INV_COMMITTED_COST,0),
decode(nvl(proj_batch.project_id, -1), -1, tmp7.EOU_PO_COMMITTED_COST,0),
decode(nvl(proj_batch.project_id, -1), -1, tmp7.EOU_PR_COMMITTED_COST,0),
decode(nvl(proj_batch.project_id, -1), -1, tmp7.EOU_OTH_COMMITTED_COST,0),
tmp7.G1_RAW_COST,
tmp7.G1_BILL_RAW_COST,
tmp7.G1_BRDN_COST,
tmp7.G1_BILL_BRDN_COST,
tmp7.G1_REVENUE,
decode(nvl(proj_batch.project_id, -1), -1, tmp7.G1_SUP_INV_COMMITTED_COST,0),
decode(nvl(proj_batch.project_id, -1), -1, tmp7.G1_PO_COMMITTED_COST,0),
decode(nvl(proj_batch.project_id, -1), -1, tmp7.G1_PR_COMMITTED_COST,0),
decode(nvl(proj_batch.project_id, -1), -1, tmp7.G1_OTH_COMMITTED_COST,0),
tmp7.G2_RAW_COST,
tmp7.G2_BILL_RAW_COST,
tmp7.G2_BRDN_COST,
tmp7.G2_BILL_BRDN_COST,
tmp7.G2_REVENUE,
decode(nvl(proj_batch.project_id, -1), -1, tmp7.G2_SUP_INV_COMMITTED_COST,0),
decode(nvl(proj_batch.project_id, -1), -1, tmp7.G2_PO_COMMITTED_COST,0),
decode(nvl(proj_batch.project_id, -1), -1, tmp7.G2_PR_COMMITTED_COST,0),
decode(nvl(proj_batch.project_id, -1), -1, tmp7.G2_OTH_COMMITTED_COST,0),
tmp7.QUANTITY,
tmp7.BILL_QUANTITY,
tmp7.cbs_version_id,
tmp7.cbs_element_id
from
PA_FIN7_OLAP tmp7, PA_STAGE1_BATCH_OLAP proj_batch,
pa_projects_all pa, pa_pjt_proj_batch batch
where
g_extraction_type = 'INCREMENTAL'
and tmp7.project_id = proj_batch.project_id (+) and
tmp7.project_id = pa.project_id and
pa.project_id = batch.project_id and
pa.pjt_rollup_enabled_flag = 'Y'
union all
select
bal.TXN_ACCUM_HEADER_ID,
bal.RESOURCE_CLASS_ID,
bal.PROJECT_ID,
bal.PROJECT_TYPE_CLASS,
nvl(bal.TASK_ID, -1) TASK_ID,
bal.RECVR_PERIOD_TYPE,
bal.RECVR_PERIOD_ID,
-1 RBS_VERSION_ID,
bal.TXN_CURRENCY_CODE,
bal.TXN_RAW_COST,
bal.TXN_BILL_RAW_COST,
bal.TXN_BRDN_COST,
bal.TXN_BILL_BRDN_COST,
bal.TXN_REVENUE,
to_number(null) TXN_SUP_INV_COMMITTED_COST,
to_number(null) TXN_PO_COMMITTED_COST,
to_number(null) TXN_PR_COMMITTED_COST,
to_number(null) TXN_OTH_COMMITTED_COST,
bal.PRJ_RAW_COST,
bal.PRJ_BILL_RAW_COST,
bal.PRJ_BRDN_COST,
bal.PRJ_BILL_BRDN_COST,
bal.PRJ_REVENUE,
to_number(null) PRJ_SUP_INV_COMMITTED_COST,
to_number(null) PRJ_PO_COMMITTED_COST,
to_number(null) PRJ_PR_COMMITTED_COST,
to_number(null) PRJ_OTH_COMMITTED_COST,
bal.POU_RAW_COST,
bal.POU_BILL_RAW_COST,
bal.POU_BRDN_COST,
bal.POU_BILL_BRDN_COST,
bal.POU_REVENUE,
to_number(null) POU_SUP_INV_COMMITTED_COST,
to_number(null) POU_PO_COMMITTED_COST,
to_number(null) POU_PR_COMMITTED_COST,
to_number(null) POU_OTH_COMMITTED_COST,
bal.EOU_RAW_COST,
bal.EOU_BILL_RAW_COST,
bal.EOU_BRDN_COST,
bal.EOU_BILL_BRDN_COST,
to_number(null) EOU_SUP_INV_COMMITTED_COST,
to_number(null) EOU_PO_COMMITTED_COST,
to_number(null) EOU_PR_COMMITTED_COST,
to_number(null) EOU_OTH_COMMITTED_COST,
bal.G1_RAW_COST,
bal.G1_BILL_RAW_COST,
bal.G1_BRDN_COST,
bal.G1_BILL_BRDN_COST,
bal.G1_REVENUE,
to_number(null) G1_SUP_INV_COMMITTED_COST,
to_number(null) G1_PO_COMMITTED_COST,
to_number(null) G1_PR_COMMITTED_COST,
to_number(null) G1_OTH_COMMITTED_COST,
bal.G2_RAW_COST,
bal.G2_BILL_RAW_COST,
bal.G2_BRDN_COST,
bal.G2_BILL_BRDN_COST,
bal.G2_REVENUE,
to_number(null) G2_SUP_INV_COMMITTED_COST,
to_number(null) G2_PO_COMMITTED_COST,
to_number(null) G2_PR_COMMITTED_COST,
to_number(null) G2_OTH_COMMITTED_COST,
bal.QUANTITY,
bal.BILL_QUANTITY,
bal.cbs_version_id,
bal.cbs_element_id
from PJI_FP_TXN_ACCUM bal,
PA_FIN7_OLAP fin7,
pa_projects_all pa,
pa_pjt_proj_batch batch
where
g_extraction_type = 'INCREMENTAL' and
bal.project_id = fin7.project_id and
bal.RECVR_PERIOD_TYPE = fin7.RECVR_PERIOD_TYPE and
bal.RECVR_PERIOD_ID = fin7.RECVR_PERIOD_ID and
bal.project_id = pa.project_id and
pa.project_id = batch.project_id and
pa.pjt_rollup_enabled_flag = 'Y'
) tmp1, pa_projects_all p
where tmp1.project_id = p.project_id;
INSERT INTO pa_olap_stage(
OLAP_GROUP ,
PROJECT_ID ,
PROJECT_ELEMENT_ID ,
TIME_ID ,
CALENDAR_TYPE ,
CURRENCY_CODE ,
RBS_ELEMENT_ID ,
RBS_VERSION_ID ,
PLAN_VERSION_ID ,
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 ,
CUSTOM1 ,
CUSTOM2 ,
CUSTOM3 ,
CUSTOM4 ,
CUSTOM5 ,
CUSTOM6 ,
CUSTOM7 ,
CUSTOM8 ,
CUSTOM9 ,
CUSTOM10 ,
CUSTOM11 ,
CUSTOM12 ,
CUSTOM13 ,
CUSTOM14 ,
CUSTOM15 ,
CBS_VERSION_ID ,
CBS_ELEMENT_ID ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
CREATION_DATE ,
CREATED_BY ,
LAST_UPDATE_LOGIN
)
select
p.olap_group,
tmp1.PROJECT_ID,
tmp1.TASK_ID,
tmp1.RECVR_PERIOD_ID,
substrb(tmp1.RECVR_PERIOD_TYPE, 1,1),
p.project_currency_code,
nvl(rbs.ELEMENT_ID,-1), --satya2
tmp1.RBS_VERSION_ID,
-1,
tmp1.PRJ_RAW_COST,
tmp1.PRJ_BRDN_COST,
tmp1.PRJ_REVENUE,
decode(tmp1.PROJECT_TYPE_CLASS, 'B', tmp1.PRJ_BILL_RAW_COST, to_number(null)) BILL_RAW_COST,
decode(tmp1.PROJECT_TYPE_CLASS, 'B', tmp1.PRJ_BILL_BRDN_COST, to_number(null)) BILL_BRDN_COST,
decode(tmp1.PROJECT_TYPE_CLASS || '_' || tmp1.RESOURCE_CLASS_ID, 'B_1', tmp1.PRJ_BILL_RAW_COST, to_number(null)) BILL_LABOR_RAW_COST,
decode(tmp1.PROJECT_TYPE_CLASS || '_' || tmp1.RESOURCE_CLASS_ID, 'B_1', tmp1.PRJ_BILL_BRDN_COST, to_number(null)) BILL_LABOR_BRDN_COST,
decode(tmp1.PROJECT_TYPE_CLASS || '_' || tmp1.resource_class_id, 'B_1', tmp1.BILL_QUANTITY, to_number(null)) BILL_LABOR_HRS,
decode(tmp1.resource_class_id, '2', tmp1.PRJ_RAW_COST, to_number(null)) EQUIPMENT_RAW_COST,
decode(tmp1.resource_class_id, '2', tmp1.PRJ_BRDN_COST, to_number(null)) EQUIPMENT_BRDN_COST,
decode(tmp1.PROJECT_TYPE_CLASS, 'C', tmp1.PRJ_BILL_RAW_COST, to_number(null)) CAPITALIZABLE_RAW_COST,
decode(tmp1.PROJECT_TYPE_CLASS, 'C', tmp1.PRJ_BILL_BRDN_COST, to_number(null))CAPITALIZABLE_BRDN_COST,
decode(tmp1.resource_class_id, '1', tmp1.PRJ_RAW_COST, to_number(null)) LABOR_RAW_COST,
decode(tmp1.resource_class_id, '1', tmp1.PRJ_BRDN_COST, to_number(null)) LABOR_BRDN_COST,
decode(tmp1.resource_class_id, '1', tmp1.QUANTITY, to_number(null)) LABOR_HRS,
decode(tmp1.resource_class_id, '1', tmp1.PRJ_REVENUE, to_number(null)) LABOR_REVENUE,
decode(tmp1.resource_class_id, '2', tmp1.QUANTITY, to_number(null)) EQUIPMENT_HOURS,
decode(tmp1.resource_class_id, '2', tmp1.BILL_QUANTITY, to_number(null)) BILLABLE_EQUIPMENT_HOURS,
tmp1.PRJ_SUP_INV_COMMITTED_COST,
tmp1.PRJ_PO_COMMITTED_COST,
tmp1.PRJ_PR_COMMITTED_COST,
tmp1.PRJ_OTH_COMMITTED_COST,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
nvl(tmp1.CBS_VERSION_ID,-1), --satya2
nvl(tmp1.CBS_ELEMENT_ID,-1), --satya2
sysdate ,
-1 ,
sysdate ,
-1 ,
-1
from
(
select -- initial actuals data
bal.TXN_ACCUM_HEADER_ID,
bal.RESOURCE_CLASS_ID,
bal.PROJECT_ID,
bal.PROJECT_TYPE_CLASS,
nvl(bal.TASK_ID, -1) TASK_ID,
bal.RECVR_PERIOD_TYPE,
bal.RECVR_PERIOD_ID,
asg.RBS_VERSION_ID RBS_VERSION_ID,
bal.TXN_CURRENCY_CODE,
bal.TXN_RAW_COST,
bal.TXN_BILL_RAW_COST,
bal.TXN_BRDN_COST,
bal.TXN_BILL_BRDN_COST,
bal.TXN_REVENUE,
to_number(null) TXN_SUP_INV_COMMITTED_COST,
to_number(null) TXN_PO_COMMITTED_COST,
to_number(null) TXN_PR_COMMITTED_COST,
to_number(null) TXN_OTH_COMMITTED_COST,
bal.PRJ_RAW_COST,
bal.PRJ_BILL_RAW_COST,
bal.PRJ_BRDN_COST,
bal.PRJ_BILL_BRDN_COST,
bal.PRJ_REVENUE,
to_number(null) PRJ_SUP_INV_COMMITTED_COST,
to_number(null) PRJ_PO_COMMITTED_COST,
to_number(null) PRJ_PR_COMMITTED_COST,
to_number(null) PRJ_OTH_COMMITTED_COST,
bal.POU_RAW_COST,
bal.POU_BILL_RAW_COST,
bal.POU_BRDN_COST,
bal.POU_BILL_BRDN_COST,
bal.POU_REVENUE,
to_number(null) POU_SUP_INV_COMMITTED_COST,
to_number(null) POU_PO_COMMITTED_COST,
to_number(null) POU_PR_COMMITTED_COST,
to_number(null) POU_OTH_COMMITTED_COST,
bal.EOU_RAW_COST,
bal.EOU_BILL_RAW_COST,
bal.EOU_BRDN_COST,
bal.EOU_BILL_BRDN_COST,
to_number(null) EOU_SUP_INV_COMMITTED_COST,
to_number(null) EOU_PO_COMMITTED_COST,
to_number(null) EOU_PR_COMMITTED_COST,
to_number(null) EOU_OTH_COMMITTED_COST,
bal.G1_RAW_COST,
bal.G1_BILL_RAW_COST,
bal.G1_BRDN_COST,
bal.G1_BILL_BRDN_COST,
bal.G1_REVENUE,
to_number(null) G1_SUP_INV_COMMITTED_COST,
to_number(null) G1_PO_COMMITTED_COST,
to_number(null) G1_PR_COMMITTED_COST,
to_number(null) G1_OTH_COMMITTED_COST,
bal.G2_RAW_COST,
bal.G2_BILL_RAW_COST,
bal.G2_BRDN_COST,
bal.G2_BILL_BRDN_COST,
bal.G2_REVENUE,
to_number(null) G2_SUP_INV_COMMITTED_COST,
to_number(null) G2_PO_COMMITTED_COST,
to_number(null) G2_PR_COMMITTED_COST,
to_number(null) G2_OTH_COMMITTED_COST,
bal.QUANTITY,
bal.BILL_QUANTITY,
bal.cbs_version_id,
bal.cbs_element_id
from
PA_STAGE1_BATCH_OLAP map,
PJI_FP_TXN_ACCUM bal,
PA_RBS_PRJ_ASSIGNMENTS asg,
pa_projects_all pa,
pa_pjt_proj_batch batch
where
g_extraction_type in ('FULL', 'PARTIAL') and
-- decode(l_extraction_type,'FULL','Y','PARTIAL',decode(bitand(l_refresh_code,1),1,'Y','N'),'N') ='Y' and -- Bug#5099574
-- l_plan_type_id IS NULL and -- Bug#5099574
bal.PROJECT_ID = map.PROJECT_ID and
asg.PROJECT_ID = map.PROJECT_ID and
asg.REPORTING_USAGE_FLAG = 'Y' and
bal.project_id = pa.project_id and
pa.project_id = batch.project_id and
pa.pjt_rollup_enabled_flag = 'Y'
union all -- initial commitments data
select
bal.TXN_ACCUM_HEADER_ID,
to_number(null) RESOURCE_CLASS_ID,
bal.PROJECT_ID,
to_char(null) PROJECT_TYPE_CLASS,
nvl(bal.TASK_ID, -1) TASK_ID,
bal.RECVR_PERIOD_TYPE,
bal.RECVR_PERIOD_ID,
asg.RBS_VERSION_ID RBS_VERSION_ID,
bal.TXN_CURRENCY_CODE,
to_number(null) TXN_RAW_COST,
to_number(null) TXN_BILL_RAW_COST,
to_number(null) TXN_BRDN_COST,
to_number(null) TXN_BILL_BRDN_COST,
to_number(null) TXN_REVENUE,
bal.TXN_SUP_INV_COMMITTED_COST,
bal.TXN_PO_COMMITTED_COST,
bal.TXN_PR_COMMITTED_COST,
bal.TXN_OTH_COMMITTED_COST,
to_number(null) PRJ_RAW_COST,
to_number(null) PRJ_BILL_RAW_COST,
to_number(null) PRJ_BRDN_COST,
to_number(null) PRJ_BILL_BRDN_COST,
to_number(null) PRJ_REVENUE,
bal.PRJ_SUP_INV_COMMITTED_COST,
bal.PRJ_PO_COMMITTED_COST,
bal.PRJ_PR_COMMITTED_COST,
bal.PRJ_OTH_COMMITTED_COST,
to_number(null) POU_RAW_COST,
to_number(null) POU_BILL_RAW_COST,
to_number(null) POU_BRDN_COST,
to_number(null) POU_BILL_BRDN_COST,
to_number(null) POU_REVENUE,
bal.POU_SUP_INV_COMMITTED_COST,
bal.POU_PO_COMMITTED_COST,
bal.POU_PR_COMMITTED_COST,
bal.POU_OTH_COMMITTED_COST,
to_number(null) EOU_RAW_COST,
to_number(null) EOU_BILL_RAW_COST,
to_number(null) EOU_BRDN_COST,
to_number(null) EOU_BILL_BRDN_COST,
bal.EOU_SUP_INV_COMMITTED_COST,
bal.EOU_PO_COMMITTED_COST,
bal.EOU_PR_COMMITTED_COST,
bal.EOU_OTH_COMMITTED_COST,
to_number(null) G1_RAW_COST,
to_number(null) G1_BILL_RAW_COST,
to_number(null) G1_BRDN_COST,
to_number(null) G1_BILL_BRDN_COST,
to_number(null) G1_REVENUE,
bal.G1_SUP_INV_COMMITTED_COST,
bal.G1_PO_COMMITTED_COST,
bal.G1_PR_COMMITTED_COST,
bal.G1_OTH_COMMITTED_COST,
to_number(null) G2_RAW_COST,
to_number(null) G2_BILL_RAW_COST,
to_number(null) G2_BRDN_COST,
to_number(null) G2_BILL_BRDN_COST,
to_number(null) G2_REVENUE,
bal.G2_SUP_INV_COMMITTED_COST,
bal.G2_PO_COMMITTED_COST,
bal.G2_PR_COMMITTED_COST,
bal.G2_OTH_COMMITTED_COST,
to_number(null) QUANTITY,
to_number(null) BILL_QUANTITY,
bal.cbs_version_id,
bal.cbs_element_id
from
PA_STAGE1_BATCH_OLAP map,
PJI_FP_TXN_ACCUM1 bal,
PA_RBS_PRJ_ASSIGNMENTS asg,
pa_projects_all pa,
pa_pjt_proj_batch batch
where
g_extraction_type in ('FULL', 'PARTIAL') and
-- decode(l_extraction_type,'FULL','Y','PARTIAL',decode(bitand(l_refresh_code,1),1,'Y','N'),'N') ='Y' and -- Bug#5099574
-- l_plan_type_id IS NULL and -- Bug#5099574
bal.PROJECT_ID = map.PROJECT_ID and
asg.PROJECT_ID = map.PROJECT_ID and
asg.REPORTING_USAGE_FLAG = 'Y' and
bal.project_id = pa.project_id and
pa.project_id = batch.project_id and
pa.pjt_rollup_enabled_flag = 'Y'
union all -- incremental data
select
tmp7.TXN_ACCUM_HEADER_ID,
tmp7.RESOURCE_CLASS_ID,
tmp7.PROJECT_ID,
tmp7.PROJECT_TYPE_CLASS,
nvl(tmp7.TASK_ID, -1) TASK_ID,
tmp7.RECVR_PERIOD_TYPE,
tmp7.RECVR_PERIOD_ID,
asg.RBS_VERSION_ID RBS_VERSION_ID,
tmp7.TXN_CURRENCY_CODE,
tmp7.TXN_RAW_COST,
tmp7.TXN_BILL_RAW_COST,
tmp7.TXN_BRDN_COST,
tmp7.TXN_BILL_BRDN_COST,
tmp7.TXN_REVENUE,
tmp7.TXN_SUP_INV_COMMITTED_COST,
tmp7.TXN_PO_COMMITTED_COST,
tmp7.TXN_PR_COMMITTED_COST,
tmp7.TXN_OTH_COMMITTED_COST,
tmp7.PRJ_RAW_COST,
tmp7.PRJ_BILL_RAW_COST,
tmp7.PRJ_BRDN_COST,
tmp7.PRJ_BILL_BRDN_COST,
tmp7.PRJ_REVENUE,
decode(nvl(proj_batch.project_id, -1), -1, tmp7.PRJ_SUP_INV_COMMITTED_COST,0),
decode(nvl(proj_batch.project_id, -1), -1, tmp7.PRJ_PO_COMMITTED_COST,0),
decode(nvl(proj_batch.project_id, -1), -1, tmp7.PRJ_PR_COMMITTED_COST,0),
decode(nvl(proj_batch.project_id, -1), -1, tmp7.PRJ_OTH_COMMITTED_COST,0),
tmp7.POU_RAW_COST,
tmp7.POU_BILL_RAW_COST,
tmp7.POU_BRDN_COST,
tmp7.POU_BILL_BRDN_COST,
tmp7.POU_REVENUE,
decode(nvl(proj_batch.project_id, -1), -1, tmp7.POU_SUP_INV_COMMITTED_COST,0),
decode(nvl(proj_batch.project_id, -1), -1, tmp7.POU_PO_COMMITTED_COST,0),
decode(nvl(proj_batch.project_id, -1), -1, tmp7.POU_PR_COMMITTED_COST,0),
decode(nvl(proj_batch.project_id, -1), -1, tmp7.POU_OTH_COMMITTED_COST,0),
tmp7.EOU_RAW_COST,
tmp7.EOU_BILL_RAW_COST,
tmp7.EOU_BRDN_COST,
tmp7.EOU_BILL_BRDN_COST,
decode(nvl(proj_batch.project_id, -1), -1, tmp7.EOU_SUP_INV_COMMITTED_COST,0),
decode(nvl(proj_batch.project_id, -1), -1, tmp7.EOU_PO_COMMITTED_COST,0),
decode(nvl(proj_batch.project_id, -1), -1, tmp7.EOU_PR_COMMITTED_COST,0),
decode(nvl(proj_batch.project_id, -1), -1, tmp7.EOU_OTH_COMMITTED_COST,0),
tmp7.G1_RAW_COST,
tmp7.G1_BILL_RAW_COST,
tmp7.G1_BRDN_COST,
tmp7.G1_BILL_BRDN_COST,
tmp7.G1_REVENUE,
decode(nvl(proj_batch.project_id, -1), -1, tmp7.G1_SUP_INV_COMMITTED_COST,0),
decode(nvl(proj_batch.project_id, -1), -1, tmp7.G1_PO_COMMITTED_COST,0),
decode(nvl(proj_batch.project_id, -1), -1, tmp7.G1_PR_COMMITTED_COST,0),
decode(nvl(proj_batch.project_id, -1), -1, tmp7.G1_OTH_COMMITTED_COST,0),
tmp7.G2_RAW_COST,
tmp7.G2_BILL_RAW_COST,
tmp7.G2_BRDN_COST,
tmp7.G2_BILL_BRDN_COST,
tmp7.G2_REVENUE,
decode(nvl(proj_batch.project_id, -1), -1, tmp7.G2_SUP_INV_COMMITTED_COST,0),
decode(nvl(proj_batch.project_id, -1), -1, tmp7.G2_PO_COMMITTED_COST,0),
decode(nvl(proj_batch.project_id, -1), -1, tmp7.G2_PR_COMMITTED_COST,0),
decode(nvl(proj_batch.project_id, -1), -1, tmp7.G2_OTH_COMMITTED_COST,0),
tmp7.QUANTITY,
tmp7.BILL_QUANTITY,
tmp7.cbs_version_id,
tmp7.cbs_element_id
from
PA_FIN7_OLAP tmp7, PA_STAGE1_BATCH_OLAP proj_batch,
PA_RBS_PRJ_ASSIGNMENTS asg, pa_projects_all pa, pa_pjt_proj_batch batch
where
g_extraction_type = 'INCREMENTAL'
and tmp7.project_id = proj_batch.project_id (+) and
asg.PROJECT_ID = tmp7.PROJECT_ID and
asg.REPORTING_USAGE_FLAG = 'Y' and
tmp7.project_id = pa.project_id and
pa.project_id = batch.project_id and
pa.pjt_rollup_enabled_flag = 'Y'
union all
select
bal.TXN_ACCUM_HEADER_ID,
bal.RESOURCE_CLASS_ID,
bal.PROJECT_ID,
bal.PROJECT_TYPE_CLASS,
nvl(bal.TASK_ID, -1) TASK_ID,
bal.RECVR_PERIOD_TYPE,
bal.RECVR_PERIOD_ID,
asg.RBS_VERSION_ID RBS_VERSION_ID,
bal.TXN_CURRENCY_CODE,
bal.TXN_RAW_COST,
bal.TXN_BILL_RAW_COST,
bal.TXN_BRDN_COST,
bal.TXN_BILL_BRDN_COST,
bal.TXN_REVENUE,
to_number(null) TXN_SUP_INV_COMMITTED_COST,
to_number(null) TXN_PO_COMMITTED_COST,
to_number(null) TXN_PR_COMMITTED_COST,
to_number(null) TXN_OTH_COMMITTED_COST,
bal.PRJ_RAW_COST,
bal.PRJ_BILL_RAW_COST,
bal.PRJ_BRDN_COST,
bal.PRJ_BILL_BRDN_COST,
bal.PRJ_REVENUE,
to_number(null) PRJ_SUP_INV_COMMITTED_COST,
to_number(null) PRJ_PO_COMMITTED_COST,
to_number(null) PRJ_PR_COMMITTED_COST,
to_number(null) PRJ_OTH_COMMITTED_COST,
bal.POU_RAW_COST,
bal.POU_BILL_RAW_COST,
bal.POU_BRDN_COST,
bal.POU_BILL_BRDN_COST,
bal.POU_REVENUE,
to_number(null) POU_SUP_INV_COMMITTED_COST,
to_number(null) POU_PO_COMMITTED_COST,
to_number(null) POU_PR_COMMITTED_COST,
to_number(null) POU_OTH_COMMITTED_COST,
bal.EOU_RAW_COST,
bal.EOU_BILL_RAW_COST,
bal.EOU_BRDN_COST,
bal.EOU_BILL_BRDN_COST,
to_number(null) EOU_SUP_INV_COMMITTED_COST,
to_number(null) EOU_PO_COMMITTED_COST,
to_number(null) EOU_PR_COMMITTED_COST,
to_number(null) EOU_OTH_COMMITTED_COST,
bal.G1_RAW_COST,
bal.G1_BILL_RAW_COST,
bal.G1_BRDN_COST,
bal.G1_BILL_BRDN_COST,
bal.G1_REVENUE,
to_number(null) G1_SUP_INV_COMMITTED_COST,
to_number(null) G1_PO_COMMITTED_COST,
to_number(null) G1_PR_COMMITTED_COST,
to_number(null) G1_OTH_COMMITTED_COST,
bal.G2_RAW_COST,
bal.G2_BILL_RAW_COST,
bal.G2_BRDN_COST,
bal.G2_BILL_BRDN_COST,
bal.G2_REVENUE,
to_number(null) G2_SUP_INV_COMMITTED_COST,
to_number(null) G2_PO_COMMITTED_COST,
to_number(null) G2_PR_COMMITTED_COST,
to_number(null) G2_OTH_COMMITTED_COST,
bal.QUANTITY,
bal.BILL_QUANTITY,
bal.cbs_version_id,
bal.cbs_element_id
from PJI_FP_TXN_ACCUM bal,
PA_RBS_PRJ_ASSIGNMENTS asg,
PA_FIN7_OLAP fin7,
pa_projects_all pa,
pa_pjt_proj_batch batch
where
g_extraction_type = 'INCREMENTAL' and
asg.PROJECT_ID = bal.PROJECT_ID and
asg.REPORTING_USAGE_FLAG = 'Y' and
bal.project_id = fin7.project_id and
bal.RECVR_PERIOD_TYPE = fin7.RECVR_PERIOD_TYPE and
bal.RECVR_PERIOD_ID = fin7.RECVR_PERIOD_ID and
bal.project_id = pa.project_id and
pa.project_id = batch.project_id and
pa.pjt_rollup_enabled_flag = 'Y'
) tmp1,
pa_rbs_txn_accum_map rbs, pa_projects_all p
where tmp1.project_id = p.project_id and
tmp1.txn_accum_header_id = rbs.txn_accum_header_id (+) and
tmp1.rbs_version_id = rbs.struct_version_id(+)
;
INSERT INTO pa_olap_stage(
OLAP_GROUP ,
PROJECT_ID ,
PROJECT_ELEMENT_ID ,
TIME_ID ,
CALENDAR_TYPE ,
CURRENCY_CODE ,
RBS_ELEMENT_ID ,
RBS_VERSION_ID ,
PLAN_VERSION_ID ,
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 ,
CUSTOM1 ,
CUSTOM2 ,
CUSTOM3 ,
CUSTOM4 ,
CUSTOM5 ,
CUSTOM6 ,
CUSTOM7 ,
CUSTOM8 ,
CUSTOM9 ,
CUSTOM10 ,
CUSTOM11 ,
CUSTOM12 ,
CUSTOM13 ,
CUSTOM14 ,
CUSTOM15 ,
CBS_VERSION_ID ,
CBS_ELEMENT_ID ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
CREATION_DATE ,
CREATED_BY ,
LAST_UPDATE_LOGIN
)
select
p.olap_group,
tmp1.PROJECT_ID,
tmp1.TASK_ID,
tmp1.RECVR_PERIOD_ID,
substrb(tmp1.RECVR_PERIOD_TYPE, 1,1),
p.projfunc_currency_code,
-1, --satya2
-1,
-1,
tmp1.PRJ_RAW_COST,
tmp1.PRJ_BRDN_COST,
tmp1.PRJ_REVENUE,
decode(tmp1.PROJECT_TYPE_CLASS, 'B', tmp1.PRJ_BILL_RAW_COST, to_number(null)) BILL_RAW_COST,
decode(tmp1.PROJECT_TYPE_CLASS, 'B', tmp1.PRJ_BILL_BRDN_COST, to_number(null)) BILL_BRDN_COST,
decode(tmp1.PROJECT_TYPE_CLASS || '_' || tmp1.RESOURCE_CLASS_ID, 'B_1', tmp1.PRJ_BILL_RAW_COST, to_number(null)) BILL_LABOR_RAW_COST,
decode(tmp1.PROJECT_TYPE_CLASS || '_' || tmp1.RESOURCE_CLASS_ID, 'B_1', tmp1.PRJ_BILL_BRDN_COST, to_number(null)) BILL_LABOR_BRDN_COST,
decode(tmp1.PROJECT_TYPE_CLASS || '_' || tmp1.resource_class_id, 'B_1', tmp1.BILL_QUANTITY, to_number(null)) BILL_LABOR_HRS,
decode(tmp1.resource_class_id, '2', tmp1.PRJ_RAW_COST, to_number(null)) EQUIPMENT_RAW_COST,
decode(tmp1.resource_class_id, '2', tmp1.PRJ_BRDN_COST, to_number(null)) EQUIPMENT_BRDN_COST,
decode(tmp1.PROJECT_TYPE_CLASS, 'C', tmp1.PRJ_BILL_RAW_COST, to_number(null)) CAPITALIZABLE_RAW_COST,
decode(tmp1.PROJECT_TYPE_CLASS, 'C', tmp1.PRJ_BILL_BRDN_COST, to_number(null))CAPITALIZABLE_BRDN_COST,
decode(tmp1.resource_class_id, '1', tmp1.PRJ_RAW_COST, to_number(null)) LABOR_RAW_COST,
decode(tmp1.resource_class_id, '1', tmp1.PRJ_BRDN_COST, to_number(null)) LABOR_BRDN_COST,
decode(tmp1.resource_class_id, '1', tmp1.QUANTITY, to_number(null)) LABOR_HRS,
decode(tmp1.resource_class_id, '1', tmp1.PRJ_REVENUE, to_number(null)) LABOR_REVENUE,
decode(tmp1.resource_class_id, '2', tmp1.QUANTITY, to_number(null)) EQUIPMENT_HOURS,
decode(tmp1.resource_class_id, '2', tmp1.BILL_QUANTITY, to_number(null)) BILLABLE_EQUIPMENT_HOURS,
tmp1.PRJ_SUP_INV_COMMITTED_COST,
tmp1.PRJ_PO_COMMITTED_COST,
tmp1.PRJ_PR_COMMITTED_COST,
tmp1.PRJ_OTH_COMMITTED_COST,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
nvl(tmp1.CBS_VERSION_ID,-1), --satya2
nvl(tmp1.CBS_ELEMENT_ID,-1), --satya2
sysdate ,
-1 ,
sysdate ,
-1 ,
-1
from
(
select -- initial actuals data
bal.TXN_ACCUM_HEADER_ID,
bal.RESOURCE_CLASS_ID,
bal.PROJECT_ID,
bal.PROJECT_TYPE_CLASS,
nvl(bal.TASK_ID, -1) TASK_ID,
bal.RECVR_PERIOD_TYPE,
bal.RECVR_PERIOD_ID,
-1 RBS_VERSION_ID,
bal.TXN_CURRENCY_CODE,
bal.TXN_RAW_COST,
bal.TXN_BILL_RAW_COST,
bal.TXN_BRDN_COST,
bal.TXN_BILL_BRDN_COST,
bal.TXN_REVENUE,
to_number(null) TXN_SUP_INV_COMMITTED_COST,
to_number(null) TXN_PO_COMMITTED_COST,
to_number(null) TXN_PR_COMMITTED_COST,
to_number(null) TXN_OTH_COMMITTED_COST,
bal.PRJ_RAW_COST,
bal.PRJ_BILL_RAW_COST,
bal.PRJ_BRDN_COST,
bal.PRJ_BILL_BRDN_COST,
bal.PRJ_REVENUE,
to_number(null) PRJ_SUP_INV_COMMITTED_COST,
to_number(null) PRJ_PO_COMMITTED_COST,
to_number(null) PRJ_PR_COMMITTED_COST,
to_number(null) PRJ_OTH_COMMITTED_COST,
bal.POU_RAW_COST,
bal.POU_BILL_RAW_COST,
bal.POU_BRDN_COST,
bal.POU_BILL_BRDN_COST,
bal.POU_REVENUE,
to_number(null) POU_SUP_INV_COMMITTED_COST,
to_number(null) POU_PO_COMMITTED_COST,
to_number(null) POU_PR_COMMITTED_COST,
to_number(null) POU_OTH_COMMITTED_COST,
bal.EOU_RAW_COST,
bal.EOU_BILL_RAW_COST,
bal.EOU_BRDN_COST,
bal.EOU_BILL_BRDN_COST,
to_number(null) EOU_SUP_INV_COMMITTED_COST,
to_number(null) EOU_PO_COMMITTED_COST,
to_number(null) EOU_PR_COMMITTED_COST,
to_number(null) EOU_OTH_COMMITTED_COST,
bal.G1_RAW_COST,
bal.G1_BILL_RAW_COST,
bal.G1_BRDN_COST,
bal.G1_BILL_BRDN_COST,
bal.G1_REVENUE,
to_number(null) G1_SUP_INV_COMMITTED_COST,
to_number(null) G1_PO_COMMITTED_COST,
to_number(null) G1_PR_COMMITTED_COST,
to_number(null) G1_OTH_COMMITTED_COST,
bal.G2_RAW_COST,
bal.G2_BILL_RAW_COST,
bal.G2_BRDN_COST,
bal.G2_BILL_BRDN_COST,
bal.G2_REVENUE,
to_number(null) G2_SUP_INV_COMMITTED_COST,
to_number(null) G2_PO_COMMITTED_COST,
to_number(null) G2_PR_COMMITTED_COST,
to_number(null) G2_OTH_COMMITTED_COST,
bal.QUANTITY,
bal.BILL_QUANTITY,
bal.cbs_version_id,
bal.cbs_element_id
from
PA_STAGE1_BATCH_OLAP map,
PJI_FP_TXN_ACCUM bal,
pa_projects_all pa,
pa_pjt_proj_batch batch
where
g_extraction_type in ('FULL', 'PARTIAL') and
-- decode(l_extraction_type,'FULL','Y','PARTIAL',decode(bitand(l_refresh_code,1),1,'Y','N'),'N') ='Y' and -- Bug#5099574
-- l_plan_type_id IS NULL and -- Bug#5099574
bal.PROJECT_ID = map.PROJECT_ID and
bal.project_id = pa.project_id and
pa.pjt_rollup_enabled_flag = 'Y' and
pa.project_id = batch.project_id and
pa.project_currency_code <> pa.projfunc_currency_code
union all -- initial commitments data
select
bal.TXN_ACCUM_HEADER_ID,
to_number(null) RESOURCE_CLASS_ID,
bal.PROJECT_ID,
to_char(null) PROJECT_TYPE_CLASS,
nvl(bal.TASK_ID, -1) TASK_ID,
bal.RECVR_PERIOD_TYPE,
bal.RECVR_PERIOD_ID,
-1 RBS_VERSION_ID,
bal.TXN_CURRENCY_CODE,
to_number(null) TXN_RAW_COST,
to_number(null) TXN_BILL_RAW_COST,
to_number(null) TXN_BRDN_COST,
to_number(null) TXN_BILL_BRDN_COST,
to_number(null) TXN_REVENUE,
bal.TXN_SUP_INV_COMMITTED_COST,
bal.TXN_PO_COMMITTED_COST,
bal.TXN_PR_COMMITTED_COST,
bal.TXN_OTH_COMMITTED_COST,
to_number(null) PRJ_RAW_COST,
to_number(null) PRJ_BILL_RAW_COST,
to_number(null) PRJ_BRDN_COST,
to_number(null) PRJ_BILL_BRDN_COST,
to_number(null) PRJ_REVENUE,
bal.PRJ_SUP_INV_COMMITTED_COST,
bal.PRJ_PO_COMMITTED_COST,
bal.PRJ_PR_COMMITTED_COST,
bal.PRJ_OTH_COMMITTED_COST,
to_number(null) POU_RAW_COST,
to_number(null) POU_BILL_RAW_COST,
to_number(null) POU_BRDN_COST,
to_number(null) POU_BILL_BRDN_COST,
to_number(null) POU_REVENUE,
bal.POU_SUP_INV_COMMITTED_COST,
bal.POU_PO_COMMITTED_COST,
bal.POU_PR_COMMITTED_COST,
bal.POU_OTH_COMMITTED_COST,
to_number(null) EOU_RAW_COST,
to_number(null) EOU_BILL_RAW_COST,
to_number(null) EOU_BRDN_COST,
to_number(null) EOU_BILL_BRDN_COST,
bal.EOU_SUP_INV_COMMITTED_COST,
bal.EOU_PO_COMMITTED_COST,
bal.EOU_PR_COMMITTED_COST,
bal.EOU_OTH_COMMITTED_COST,
to_number(null) G1_RAW_COST,
to_number(null) G1_BILL_RAW_COST,
to_number(null) G1_BRDN_COST,
to_number(null) G1_BILL_BRDN_COST,
to_number(null) G1_REVENUE,
bal.G1_SUP_INV_COMMITTED_COST,
bal.G1_PO_COMMITTED_COST,
bal.G1_PR_COMMITTED_COST,
bal.G1_OTH_COMMITTED_COST,
to_number(null) G2_RAW_COST,
to_number(null) G2_BILL_RAW_COST,
to_number(null) G2_BRDN_COST,
to_number(null) G2_BILL_BRDN_COST,
to_number(null) G2_REVENUE,
bal.G2_SUP_INV_COMMITTED_COST,
bal.G2_PO_COMMITTED_COST,
bal.G2_PR_COMMITTED_COST,
bal.G2_OTH_COMMITTED_COST,
to_number(null) QUANTITY,
to_number(null) BILL_QUANTITY,
bal.cbs_version_id,
bal.cbs_element_id
from
PA_STAGE1_BATCH_OLAP map,
PJI_FP_TXN_ACCUM1 bal,
pa_projects_all pa,
pa_pjt_proj_batch batch
where
g_extraction_type in ('FULL', 'PARTIAL') and
-- decode(l_extraction_type,'FULL','Y','PARTIAL',decode(bitand(l_refresh_code,1),1,'Y','N'),'N') ='Y' and -- Bug#5099574
-- l_plan_type_id IS NULL and -- Bug#5099574
bal.PROJECT_ID = map.PROJECT_ID and
bal.project_id = pa.project_id and
pa.project_id = batch.project_id and
pa.pjt_rollup_enabled_flag = 'Y' and
pa.project_currency_code <> pa.projfunc_currency_code
union all -- incremental data
select
tmp7.TXN_ACCUM_HEADER_ID,
tmp7.RESOURCE_CLASS_ID,
tmp7.PROJECT_ID,
tmp7.PROJECT_TYPE_CLASS,
nvl(tmp7.TASK_ID, -1) TASK_ID,
tmp7.RECVR_PERIOD_TYPE,
tmp7.RECVR_PERIOD_ID,
-1 RBS_VERSION_ID,
tmp7.TXN_CURRENCY_CODE,
tmp7.TXN_RAW_COST,
tmp7.TXN_BILL_RAW_COST,
tmp7.TXN_BRDN_COST,
tmp7.TXN_BILL_BRDN_COST,
tmp7.TXN_REVENUE,
tmp7.TXN_SUP_INV_COMMITTED_COST,
tmp7.TXN_PO_COMMITTED_COST,
tmp7.TXN_PR_COMMITTED_COST,
tmp7.TXN_OTH_COMMITTED_COST,
tmp7.PRJ_RAW_COST,
tmp7.PRJ_BILL_RAW_COST,
tmp7.PRJ_BRDN_COST,
tmp7.PRJ_BILL_BRDN_COST,
tmp7.PRJ_REVENUE,
decode(nvl(proj_batch.project_id, -1), -1, tmp7.PRJ_SUP_INV_COMMITTED_COST,0),
decode(nvl(proj_batch.project_id, -1), -1, tmp7.PRJ_PO_COMMITTED_COST,0),
decode(nvl(proj_batch.project_id, -1), -1, tmp7.PRJ_PR_COMMITTED_COST,0),
decode(nvl(proj_batch.project_id, -1), -1, tmp7.PRJ_OTH_COMMITTED_COST,0),
tmp7.POU_RAW_COST,
tmp7.POU_BILL_RAW_COST,
tmp7.POU_BRDN_COST,
tmp7.POU_BILL_BRDN_COST,
tmp7.POU_REVENUE,
decode(nvl(proj_batch.project_id, -1), -1, tmp7.POU_SUP_INV_COMMITTED_COST,0),
decode(nvl(proj_batch.project_id, -1), -1, tmp7.POU_PO_COMMITTED_COST,0),
decode(nvl(proj_batch.project_id, -1), -1, tmp7.POU_PR_COMMITTED_COST,0),
decode(nvl(proj_batch.project_id, -1), -1, tmp7.POU_OTH_COMMITTED_COST,0),
tmp7.EOU_RAW_COST,
tmp7.EOU_BILL_RAW_COST,
tmp7.EOU_BRDN_COST,
tmp7.EOU_BILL_BRDN_COST,
decode(nvl(proj_batch.project_id, -1), -1, tmp7.EOU_SUP_INV_COMMITTED_COST,0),
decode(nvl(proj_batch.project_id, -1), -1, tmp7.EOU_PO_COMMITTED_COST,0),
decode(nvl(proj_batch.project_id, -1), -1, tmp7.EOU_PR_COMMITTED_COST,0),
decode(nvl(proj_batch.project_id, -1), -1, tmp7.EOU_OTH_COMMITTED_COST,0),
tmp7.G1_RAW_COST,
tmp7.G1_BILL_RAW_COST,
tmp7.G1_BRDN_COST,
tmp7.G1_BILL_BRDN_COST,
tmp7.G1_REVENUE,
decode(nvl(proj_batch.project_id, -1), -1, tmp7.G1_SUP_INV_COMMITTED_COST,0),
decode(nvl(proj_batch.project_id, -1), -1, tmp7.G1_PO_COMMITTED_COST,0),
decode(nvl(proj_batch.project_id, -1), -1, tmp7.G1_PR_COMMITTED_COST,0),
decode(nvl(proj_batch.project_id, -1), -1, tmp7.G1_OTH_COMMITTED_COST,0),
tmp7.G2_RAW_COST,
tmp7.G2_BILL_RAW_COST,
tmp7.G2_BRDN_COST,
tmp7.G2_BILL_BRDN_COST,
tmp7.G2_REVENUE,
decode(nvl(proj_batch.project_id, -1), -1, tmp7.G2_SUP_INV_COMMITTED_COST,0),
decode(nvl(proj_batch.project_id, -1), -1, tmp7.G2_PO_COMMITTED_COST,0),
decode(nvl(proj_batch.project_id, -1), -1, tmp7.G2_PR_COMMITTED_COST,0),
decode(nvl(proj_batch.project_id, -1), -1, tmp7.G2_OTH_COMMITTED_COST,0),
tmp7.QUANTITY,
tmp7.BILL_QUANTITY,
tmp7.cbs_version_id,
tmp7.cbs_element_id
from
PA_FIN7_OLAP tmp7, PA_STAGE1_BATCH_OLAP proj_batch,
pa_projects_all pa, pa_pjt_proj_batch batch
where
g_extraction_type = 'INCREMENTAL'
and tmp7.project_id = proj_batch.project_id (+) and
tmp7.project_id = pa.project_id and
pa.project_id = batch.project_id and
pa.pjt_rollup_enabled_flag = 'Y' and
pa.project_currency_code <> pa.projfunc_currency_code
union all
select
bal.TXN_ACCUM_HEADER_ID,
bal.RESOURCE_CLASS_ID,
bal.PROJECT_ID,
bal.PROJECT_TYPE_CLASS,
nvl(bal.TASK_ID, -1) TASK_ID,
bal.RECVR_PERIOD_TYPE,
bal.RECVR_PERIOD_ID,
-1 RBS_VERSION_ID,
bal.TXN_CURRENCY_CODE,
bal.TXN_RAW_COST,
bal.TXN_BILL_RAW_COST,
bal.TXN_BRDN_COST,
bal.TXN_BILL_BRDN_COST,
bal.TXN_REVENUE,
to_number(null) TXN_SUP_INV_COMMITTED_COST,
to_number(null) TXN_PO_COMMITTED_COST,
to_number(null) TXN_PR_COMMITTED_COST,
to_number(null) TXN_OTH_COMMITTED_COST,
bal.PRJ_RAW_COST,
bal.PRJ_BILL_RAW_COST,
bal.PRJ_BRDN_COST,
bal.PRJ_BILL_BRDN_COST,
bal.PRJ_REVENUE,
to_number(null) PRJ_SUP_INV_COMMITTED_COST,
to_number(null) PRJ_PO_COMMITTED_COST,
to_number(null) PRJ_PR_COMMITTED_COST,
to_number(null) PRJ_OTH_COMMITTED_COST,
bal.POU_RAW_COST,
bal.POU_BILL_RAW_COST,
bal.POU_BRDN_COST,
bal.POU_BILL_BRDN_COST,
bal.POU_REVENUE,
to_number(null) POU_SUP_INV_COMMITTED_COST,
to_number(null) POU_PO_COMMITTED_COST,
to_number(null) POU_PR_COMMITTED_COST,
to_number(null) POU_OTH_COMMITTED_COST,
bal.EOU_RAW_COST,
bal.EOU_BILL_RAW_COST,
bal.EOU_BRDN_COST,
bal.EOU_BILL_BRDN_COST,
to_number(null) EOU_SUP_INV_COMMITTED_COST,
to_number(null) EOU_PO_COMMITTED_COST,
to_number(null) EOU_PR_COMMITTED_COST,
to_number(null) EOU_OTH_COMMITTED_COST,
bal.G1_RAW_COST,
bal.G1_BILL_RAW_COST,
bal.G1_BRDN_COST,
bal.G1_BILL_BRDN_COST,
bal.G1_REVENUE,
to_number(null) G1_SUP_INV_COMMITTED_COST,
to_number(null) G1_PO_COMMITTED_COST,
to_number(null) G1_PR_COMMITTED_COST,
to_number(null) G1_OTH_COMMITTED_COST,
bal.G2_RAW_COST,
bal.G2_BILL_RAW_COST,
bal.G2_BRDN_COST,
bal.G2_BILL_BRDN_COST,
bal.G2_REVENUE,
to_number(null) G2_SUP_INV_COMMITTED_COST,
to_number(null) G2_PO_COMMITTED_COST,
to_number(null) G2_PR_COMMITTED_COST,
to_number(null) G2_OTH_COMMITTED_COST,
bal.QUANTITY,
bal.BILL_QUANTITY,
bal.cbs_version_id,
bal.cbs_element_id
from PJI_FP_TXN_ACCUM bal,
PA_FIN7_OLAP fin7,
pa_projects_all pa,
pa_pjt_proj_batch batch
where
g_extraction_type = 'INCREMENTAL' and
bal.project_id = fin7.project_id and
bal.RECVR_PERIOD_TYPE = fin7.RECVR_PERIOD_TYPE and
bal.RECVR_PERIOD_ID = fin7.RECVR_PERIOD_ID and
bal.project_id = pa.project_id and
pa.project_id = batch.project_id and
pa.pjt_rollup_enabled_flag = 'Y' and
pa.project_currency_code <> pa.projfunc_currency_code
) tmp1, pa_projects_all p
where tmp1.project_id = p.project_id;
INSERT INTO pa_olap_stage(
OLAP_GROUP ,
PROJECT_ID ,
PROJECT_ELEMENT_ID ,
TIME_ID ,
CALENDAR_TYPE ,
CURRENCY_CODE ,
RBS_ELEMENT_ID ,
RBS_VERSION_ID ,
PLAN_VERSION_ID ,
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 ,
CUSTOM1 ,
CUSTOM2 ,
CUSTOM3 ,
CUSTOM4 ,
CUSTOM5 ,
CUSTOM6 ,
CUSTOM7 ,
CUSTOM8 ,
CUSTOM9 ,
CUSTOM10 ,
CUSTOM11 ,
CUSTOM12 ,
CUSTOM13 ,
CUSTOM14 ,
CUSTOM15 ,
CBS_VERSION_ID ,
CBS_ELEMENT_ID ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
CREATION_DATE ,
CREATED_BY ,
LAST_UPDATE_LOGIN
)
select
p.olap_group,
tmp1.PROJECT_ID,
tmp1.TASK_ID,
tmp1.RECVR_PERIOD_ID,
substrb(tmp1.RECVR_PERIOD_TYPE, 1,1),
p.PROJFUNC_CURRENCY_CODE,
nvl(rbs.ELEMENT_ID,-1), --satya2
nvl(tmp1.RBS_VERSION_ID, -1), --satya2
-1,
tmp1.POU_RAW_COST,
tmp1.POU_BRDN_COST,
tmp1.POU_REVENUE,
decode(tmp1.PROJECT_TYPE_CLASS, 'B', tmp1.POU_BILL_RAW_COST, to_number(null)) BILL_RAW_COST,
decode(tmp1.PROJECT_TYPE_CLASS, 'B', tmp1.POU_BILL_BRDN_COST, to_number(null)) BILL_BRDN_COST,
decode(tmp1.PROJECT_TYPE_CLASS || '_' || tmp1.RESOURCE_CLASS_ID, 'B_1', tmp1.POU_BILL_RAW_COST, to_number(null)) BILL_LABOR_RAW_COST,
decode(tmp1.PROJECT_TYPE_CLASS || '_' || tmp1.RESOURCE_CLASS_ID, 'B_1', tmp1.POU_BILL_BRDN_COST, to_number(null)) BILL_LABOR_BRDN_COST,
decode(tmp1.PROJECT_TYPE_CLASS || '_' || tmp1.resource_class_id, 'B_1', tmp1.BILL_QUANTITY, to_number(null)) BILL_LABOR_HRS,
decode(tmp1.resource_class_id, '2', tmp1.POU_RAW_COST, to_number(null)) EQUIPMENT_RAW_COST,
decode(tmp1.resource_class_id, '2', tmp1.POU_BRDN_COST, to_number(null)) EQUIPMENT_BRDN_COST,
decode(tmp1.PROJECT_TYPE_CLASS, 'C', tmp1.POU_BILL_RAW_COST, to_number(null)) CAPITALIZABLE_RAW_COST,
decode(tmp1.PROJECT_TYPE_CLASS, 'C', tmp1.POU_BILL_BRDN_COST, to_number(null))CAPITALIZABLE_BRDN_COST,
decode(tmp1.resource_class_id, '1', tmp1.POU_RAW_COST, to_number(null)) LABOR_RAW_COST,
decode(tmp1.resource_class_id, '1', tmp1.POU_BRDN_COST, to_number(null)) LABOR_BRDN_COST,
decode(tmp1.resource_class_id, '1', tmp1.QUANTITY, to_number(null)) LABOR_HRS,
decode(tmp1.resource_class_id, '1', tmp1.POU_REVENUE, to_number(null)) LABOR_REVENUE,
decode(tmp1.resource_class_id, '2', tmp1.QUANTITY, to_number(null)) EQUIPMENT_HOURS,
decode(tmp1.resource_class_id, '2', tmp1.BILL_QUANTITY, to_number(null)) BILLABLE_EQUIPMENT_HOURS,
tmp1.POU_SUP_INV_COMMITTED_COST,
tmp1.POU_PO_COMMITTED_COST,
tmp1.POU_PR_COMMITTED_COST,
tmp1.POU_OTH_COMMITTED_COST,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
nvl(tmp1.CBS_VERSION_ID,-1), --satya2
nvl(tmp1.CBS_ELEMENT_ID,-1), --satya2
sysdate ,
-1 ,
sysdate ,
-1 ,
-1
from
(
select -- initial actuals data
bal.TXN_ACCUM_HEADER_ID,
bal.RESOURCE_CLASS_ID,
bal.PROJECT_ID,
bal.PROJECT_TYPE_CLASS,
nvl(bal.TASK_ID, -1) TASK_ID,
bal.RECVR_PERIOD_TYPE,
bal.RECVR_PERIOD_ID,
nvl(asg.RBS_VERSION_ID, -1) RBS_VERSION_ID,
bal.TXN_CURRENCY_CODE,
bal.TXN_RAW_COST,
bal.TXN_BILL_RAW_COST,
bal.TXN_BRDN_COST,
bal.TXN_BILL_BRDN_COST,
bal.TXN_REVENUE,
to_number(null) TXN_SUP_INV_COMMITTED_COST,
to_number(null) TXN_PO_COMMITTED_COST,
to_number(null) TXN_PR_COMMITTED_COST,
to_number(null) TXN_OTH_COMMITTED_COST,
bal.PRJ_RAW_COST,
bal.PRJ_BILL_RAW_COST,
bal.PRJ_BRDN_COST,
bal.PRJ_BILL_BRDN_COST,
bal.PRJ_REVENUE,
to_number(null) PRJ_SUP_INV_COMMITTED_COST,
to_number(null) PRJ_PO_COMMITTED_COST,
to_number(null) PRJ_PR_COMMITTED_COST,
to_number(null) PRJ_OTH_COMMITTED_COST,
bal.POU_RAW_COST,
bal.POU_BILL_RAW_COST,
bal.POU_BRDN_COST,
bal.POU_BILL_BRDN_COST,
bal.POU_REVENUE,
to_number(null) POU_SUP_INV_COMMITTED_COST,
to_number(null) POU_PO_COMMITTED_COST,
to_number(null) POU_PR_COMMITTED_COST,
to_number(null) POU_OTH_COMMITTED_COST,
bal.EOU_RAW_COST,
bal.EOU_BILL_RAW_COST,
bal.EOU_BRDN_COST,
bal.EOU_BILL_BRDN_COST,
to_number(null) EOU_SUP_INV_COMMITTED_COST,
to_number(null) EOU_PO_COMMITTED_COST,
to_number(null) EOU_PR_COMMITTED_COST,
to_number(null) EOU_OTH_COMMITTED_COST,
bal.G1_RAW_COST,
bal.G1_BILL_RAW_COST,
bal.G1_BRDN_COST,
bal.G1_BILL_BRDN_COST,
bal.G1_REVENUE,
to_number(null) G1_SUP_INV_COMMITTED_COST,
to_number(null) G1_PO_COMMITTED_COST,
to_number(null) G1_PR_COMMITTED_COST,
to_number(null) G1_OTH_COMMITTED_COST,
bal.G2_RAW_COST,
bal.G2_BILL_RAW_COST,
bal.G2_BRDN_COST,
bal.G2_BILL_BRDN_COST,
bal.G2_REVENUE,
to_number(null) G2_SUP_INV_COMMITTED_COST,
to_number(null) G2_PO_COMMITTED_COST,
to_number(null) G2_PR_COMMITTED_COST,
to_number(null) G2_OTH_COMMITTED_COST,
bal.QUANTITY,
bal.BILL_QUANTITY,
bal.cbs_version_id,
bal.cbs_element_id
from
PA_STAGE1_BATCH_OLAP map,
PJI_FP_TXN_ACCUM bal,
PA_RBS_PRJ_ASSIGNMENTS asg,
pa_projects_all pa,
pa_pjt_proj_batch batch
where
g_extraction_type in ('FULL', 'PARTIAL') and
-- decode(l_extraction_type,'FULL','Y','PARTIAL',decode(bitand(l_refresh_code,1),1,'Y','N'),'N') ='Y' and -- Bug#5099574
-- l_plan_type_id IS NULL and -- Bug#5099574
bal.PROJECT_ID = map.PROJECT_ID and
asg.PROJECT_ID = map.PROJECT_ID and
asg.REPORTING_USAGE_FLAG = 'Y' and
bal.project_id = pa.project_id and
pa.project_id = batch.project_id and
pa.pjt_rollup_enabled_flag = 'Y' and
pa.project_currency_code <> pa.projfunc_currency_code
union all -- initial commitments data
select
bal.TXN_ACCUM_HEADER_ID,
to_number(null) RESOURCE_CLASS_ID,
bal.PROJECT_ID,
to_char(null) PROJECT_TYPE_CLASS,
nvl(bal.TASK_ID, -1) TASK_ID,
bal.RECVR_PERIOD_TYPE,
bal.RECVR_PERIOD_ID,
nvl(asg.RBS_VERSION_ID, -1) RBS_VERSION_ID,
bal.TXN_CURRENCY_CODE,
to_number(null) TXN_RAW_COST,
to_number(null) TXN_BILL_RAW_COST,
to_number(null) TXN_BRDN_COST,
to_number(null) TXN_BILL_BRDN_COST,
to_number(null) TXN_REVENUE,
bal.TXN_SUP_INV_COMMITTED_COST,
bal.TXN_PO_COMMITTED_COST,
bal.TXN_PR_COMMITTED_COST,
bal.TXN_OTH_COMMITTED_COST,
to_number(null) PRJ_RAW_COST,
to_number(null) PRJ_BILL_RAW_COST,
to_number(null) PRJ_BRDN_COST,
to_number(null) PRJ_BILL_BRDN_COST,
to_number(null) PRJ_REVENUE,
bal.PRJ_SUP_INV_COMMITTED_COST,
bal.PRJ_PO_COMMITTED_COST,
bal.PRJ_PR_COMMITTED_COST,
bal.PRJ_OTH_COMMITTED_COST,
to_number(null) POU_RAW_COST,
to_number(null) POU_BILL_RAW_COST,
to_number(null) POU_BRDN_COST,
to_number(null) POU_BILL_BRDN_COST,
to_number(null) POU_REVENUE,
bal.POU_SUP_INV_COMMITTED_COST,
bal.POU_PO_COMMITTED_COST,
bal.POU_PR_COMMITTED_COST,
bal.POU_OTH_COMMITTED_COST,
to_number(null) EOU_RAW_COST,
to_number(null) EOU_BILL_RAW_COST,
to_number(null) EOU_BRDN_COST,
to_number(null) EOU_BILL_BRDN_COST,
bal.EOU_SUP_INV_COMMITTED_COST,
bal.EOU_PO_COMMITTED_COST,
bal.EOU_PR_COMMITTED_COST,
bal.EOU_OTH_COMMITTED_COST,
to_number(null) G1_RAW_COST,
to_number(null) G1_BILL_RAW_COST,
to_number(null) G1_BRDN_COST,
to_number(null) G1_BILL_BRDN_COST,
to_number(null) G1_REVENUE,
bal.G1_SUP_INV_COMMITTED_COST,
bal.G1_PO_COMMITTED_COST,
bal.G1_PR_COMMITTED_COST,
bal.G1_OTH_COMMITTED_COST,
to_number(null) G2_RAW_COST,
to_number(null) G2_BILL_RAW_COST,
to_number(null) G2_BRDN_COST,
to_number(null) G2_BILL_BRDN_COST,
to_number(null) G2_REVENUE,
bal.G2_SUP_INV_COMMITTED_COST,
bal.G2_PO_COMMITTED_COST,
bal.G2_PR_COMMITTED_COST,
bal.G2_OTH_COMMITTED_COST,
to_number(null) QUANTITY,
to_number(null) BILL_QUANTITY,
bal.cbs_version_id,
bal.cbs_element_id
from
PA_STAGE1_BATCH_OLAP map,
PJI_FP_TXN_ACCUM1 bal,
PA_RBS_PRJ_ASSIGNMENTS asg,
pa_projects_all pa,
pa_pjt_proj_batch batch
where
g_extraction_type in ('FULL', 'PARTIAL') and
-- decode(l_extraction_type,'FULL','Y','PARTIAL',decode(bitand(l_refresh_code,1),1,'Y','N'),'N') ='Y' and -- Bug#5099574
-- l_plan_type_id IS NULL and -- Bug#5099574
bal.PROJECT_ID = map.PROJECT_ID and
asg.PROJECT_ID = map.PROJECT_ID and
asg.REPORTING_USAGE_FLAG = 'Y' and
bal.project_id = pa.project_id and
pa.project_id = batch.project_id and
pa.pjt_rollup_enabled_flag = 'Y' and
pa.project_currency_code <> pa.projfunc_currency_code
union all -- incremental data
select
tmp7.TXN_ACCUM_HEADER_ID,
tmp7.RESOURCE_CLASS_ID,
tmp7.PROJECT_ID,
tmp7.PROJECT_TYPE_CLASS,
nvl(tmp7.TASK_ID, -1) TASK_ID,
tmp7.RECVR_PERIOD_TYPE,
tmp7.RECVR_PERIOD_ID,
nvl(asg.RBS_VERSION_ID, -1) RBS_VERSION_ID,
tmp7.TXN_CURRENCY_CODE,
tmp7.TXN_RAW_COST,
tmp7.TXN_BILL_RAW_COST,
tmp7.TXN_BRDN_COST,
tmp7.TXN_BILL_BRDN_COST,
tmp7.TXN_REVENUE,
tmp7.TXN_SUP_INV_COMMITTED_COST,
tmp7.TXN_PO_COMMITTED_COST,
tmp7.TXN_PR_COMMITTED_COST,
tmp7.TXN_OTH_COMMITTED_COST,
tmp7.PRJ_RAW_COST,
tmp7.PRJ_BILL_RAW_COST,
tmp7.PRJ_BRDN_COST,
tmp7.PRJ_BILL_BRDN_COST,
tmp7.PRJ_REVENUE,
decode(nvl(proj_batch.project_id, -1), -1, tmp7.PRJ_SUP_INV_COMMITTED_COST,0),
decode(nvl(proj_batch.project_id, -1), -1, tmp7.PRJ_PO_COMMITTED_COST,0),
decode(nvl(proj_batch.project_id, -1), -1, tmp7.PRJ_PR_COMMITTED_COST,0),
decode(nvl(proj_batch.project_id, -1), -1, tmp7.PRJ_OTH_COMMITTED_COST,0),
tmp7.POU_RAW_COST,
tmp7.POU_BILL_RAW_COST,
tmp7.POU_BRDN_COST,
tmp7.POU_BILL_BRDN_COST,
tmp7.POU_REVENUE,
decode(nvl(proj_batch.project_id, -1), -1, tmp7.POU_SUP_INV_COMMITTED_COST,0),
decode(nvl(proj_batch.project_id, -1), -1, tmp7.POU_PO_COMMITTED_COST,0),
decode(nvl(proj_batch.project_id, -1), -1, tmp7.POU_PR_COMMITTED_COST,0),
decode(nvl(proj_batch.project_id, -1), -1, tmp7.POU_OTH_COMMITTED_COST,0),
tmp7.EOU_RAW_COST,
tmp7.EOU_BILL_RAW_COST,
tmp7.EOU_BRDN_COST,
tmp7.EOU_BILL_BRDN_COST,
decode(nvl(proj_batch.project_id, -1), -1, tmp7.EOU_SUP_INV_COMMITTED_COST,0),
decode(nvl(proj_batch.project_id, -1), -1, tmp7.EOU_PO_COMMITTED_COST,0),
decode(nvl(proj_batch.project_id, -1), -1, tmp7.EOU_PR_COMMITTED_COST,0),
decode(nvl(proj_batch.project_id, -1), -1, tmp7.EOU_OTH_COMMITTED_COST,0),
tmp7.G1_RAW_COST,
tmp7.G1_BILL_RAW_COST,
tmp7.G1_BRDN_COST,
tmp7.G1_BILL_BRDN_COST,
tmp7.G1_REVENUE,
decode(nvl(proj_batch.project_id, -1), -1, tmp7.G1_SUP_INV_COMMITTED_COST,0),
decode(nvl(proj_batch.project_id, -1), -1, tmp7.G1_PO_COMMITTED_COST,0),
decode(nvl(proj_batch.project_id, -1), -1, tmp7.G1_PR_COMMITTED_COST,0),
decode(nvl(proj_batch.project_id, -1), -1, tmp7.G1_OTH_COMMITTED_COST,0),
tmp7.G2_RAW_COST,
tmp7.G2_BILL_RAW_COST,
tmp7.G2_BRDN_COST,
tmp7.G2_BILL_BRDN_COST,
tmp7.G2_REVENUE,
decode(nvl(proj_batch.project_id, -1), -1, tmp7.G2_SUP_INV_COMMITTED_COST,0),
decode(nvl(proj_batch.project_id, -1), -1, tmp7.G2_PO_COMMITTED_COST,0),
decode(nvl(proj_batch.project_id, -1), -1, tmp7.G2_PR_COMMITTED_COST,0),
decode(nvl(proj_batch.project_id, -1), -1, tmp7.G2_OTH_COMMITTED_COST,0),
tmp7.QUANTITY,
tmp7.BILL_QUANTITY,
tmp7.cbs_version_id,
tmp7.cbs_element_id
from
PA_FIN7_OLAP tmp7, PA_STAGE1_BATCH_OLAP proj_batch,
PA_RBS_PRJ_ASSIGNMENTS asg, pa_projects_all pa, pa_pjt_proj_batch batch
where
g_extraction_type = 'INCREMENTAL'
and tmp7.project_id = proj_batch.project_id (+) and
asg.PROJECT_ID = tmp7.PROJECT_ID and
asg.REPORTING_USAGE_FLAG = 'Y' and
tmp7.project_id = pa.project_id and
pa.project_id = batch.project_id and
pa.pjt_rollup_enabled_flag = 'Y' and
pa.project_currency_code <> pa.projfunc_currency_code
union all
select
bal.TXN_ACCUM_HEADER_ID,
bal.RESOURCE_CLASS_ID,
bal.PROJECT_ID,
bal.PROJECT_TYPE_CLASS,
nvl(bal.TASK_ID, -1) TASK_ID,
bal.RECVR_PERIOD_TYPE,
bal.RECVR_PERIOD_ID,
nvl(asg.RBS_VERSION_ID, -1) RBS_VERSION_ID,
bal.TXN_CURRENCY_CODE,
bal.TXN_RAW_COST,
bal.TXN_BILL_RAW_COST,
bal.TXN_BRDN_COST,
bal.TXN_BILL_BRDN_COST,
bal.TXN_REVENUE,
to_number(null) TXN_SUP_INV_COMMITTED_COST,
to_number(null) TXN_PO_COMMITTED_COST,
to_number(null) TXN_PR_COMMITTED_COST,
to_number(null) TXN_OTH_COMMITTED_COST,
bal.PRJ_RAW_COST,
bal.PRJ_BILL_RAW_COST,
bal.PRJ_BRDN_COST,
bal.PRJ_BILL_BRDN_COST,
bal.PRJ_REVENUE,
to_number(null) PRJ_SUP_INV_COMMITTED_COST,
to_number(null) PRJ_PO_COMMITTED_COST,
to_number(null) PRJ_PR_COMMITTED_COST,
to_number(null) PRJ_OTH_COMMITTED_COST,
bal.POU_RAW_COST,
bal.POU_BILL_RAW_COST,
bal.POU_BRDN_COST,
bal.POU_BILL_BRDN_COST,
bal.POU_REVENUE,
to_number(null) POU_SUP_INV_COMMITTED_COST,
to_number(null) POU_PO_COMMITTED_COST,
to_number(null) POU_PR_COMMITTED_COST,
to_number(null) POU_OTH_COMMITTED_COST,
bal.EOU_RAW_COST,
bal.EOU_BILL_RAW_COST,
bal.EOU_BRDN_COST,
bal.EOU_BILL_BRDN_COST,
to_number(null) EOU_SUP_INV_COMMITTED_COST,
to_number(null) EOU_PO_COMMITTED_COST,
to_number(null) EOU_PR_COMMITTED_COST,
to_number(null) EOU_OTH_COMMITTED_COST,
bal.G1_RAW_COST,
bal.G1_BILL_RAW_COST,
bal.G1_BRDN_COST,
bal.G1_BILL_BRDN_COST,
bal.G1_REVENUE,
to_number(null) G1_SUP_INV_COMMITTED_COST,
to_number(null) G1_PO_COMMITTED_COST,
to_number(null) G1_PR_COMMITTED_COST,
to_number(null) G1_OTH_COMMITTED_COST,
bal.G2_RAW_COST,
bal.G2_BILL_RAW_COST,
bal.G2_BRDN_COST,
bal.G2_BILL_BRDN_COST,
bal.G2_REVENUE,
to_number(null) G2_SUP_INV_COMMITTED_COST,
to_number(null) G2_PO_COMMITTED_COST,
to_number(null) G2_PR_COMMITTED_COST,
to_number(null) G2_OTH_COMMITTED_COST,
bal.QUANTITY,
bal.BILL_QUANTITY,
bal.cbs_version_id,
bal.cbs_element_id
from PJI_FP_TXN_ACCUM bal,
PA_RBS_PRJ_ASSIGNMENTS asg,
PA_FIN7_OLAP fin7,
pa_projects_all pa,
pa_pjt_proj_batch batch
where
g_extraction_type = 'INCREMENTAL' and
asg.PROJECT_ID = bal.PROJECT_ID and
asg.REPORTING_USAGE_FLAG = 'Y' and
bal.project_id = fin7.project_id and
bal.RECVR_PERIOD_TYPE = fin7.RECVR_PERIOD_TYPE and
bal.RECVR_PERIOD_ID = fin7.RECVR_PERIOD_ID and
bal.project_id = pa.project_id and
pa.project_id = batch.project_id and
pa.pjt_rollup_enabled_flag = 'Y' and
pa.project_currency_code <> pa.projfunc_currency_code
) tmp1,
pa_rbs_txn_accum_map rbs, pa_projects_all p
where tmp1.project_id = p.project_id and
tmp1.txn_accum_header_id = rbs.txn_accum_header_id (+) and
tmp1.rbs_version_id = rbs.struct_version_id(+)
;
insert into pa_ac_olap_stage
(OLAP_GROUP,
PROJECT_ID ,
PROJECT_ELEMENT_ID ,
TIME_ID ,
CALENDAR_TYPE ,
CURRENCY_CODE ,
REVENUE ,
INITIAL_FUNDING_AMOUNT ,
INITIAL_FUNDING_COUNT ,
ADDITIONAL_FUNDING_AMOUNT ,
ADDITIONAL_FUNDING_COUNT ,
CANCELLED_FUNDING_AMOUNT ,
CANCELLED_FUNDING_COUNT ,
FUNDING_ADJUSTMENT_AMOUNT ,
FUNDING_ADJUSTMENT_COUNT ,
REVENUE_WRITEOFF ,
AR_INVOICE_AMOUNT ,
AR_INVOICE_COUNT ,
AR_CASH_APPLIED_AMOUNT ,
AR_INVOICE_WRITE_OFF_AMOUNT ,
AR_INVOICE_WRITEOFF_COUNT ,
AR_CREDIT_MEMO_AMOUNT ,
AR_CREDIT_MEMO_COUNT ,
UNBILLED_RECEIVABLES ,
UNEARNED_REVENUE ,
AR_UNAPPR_INVOICE_AMOUNT ,
AR_UNAPPR_INVOICE_COUNT ,
AR_APPR_INVOICE_AMOUNT ,
AR_APPR_INVOICE_COUNT ,
AR_AMOUNT_DUE ,
AR_COUNT_DUE ,
AR_AMOUNT_OVERDUE ,
AR_COUNT_OVERDUE ,
--DORMANT_BACKLOG_INACTIV ,
--DORMANT_BACKLOG_START ,
--LOST_BACKLOG ,
--ACTIVE_BACKLOG ,
--REVENUE_AT_RISK ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
CREATION_DATE ,
CREATED_BY ,
LAST_UPDATE_LOGIN ,
CUSTOM1 ,
CUSTOM2 ,
CUSTOM3 ,
CUSTOM4 ,
CUSTOM5 ,
CUSTOM6 ,
CUSTOM7 ,
CUSTOM8 ,
CUSTOM9 ,
CUSTOM10 ,
CUSTOM11 ,
CUSTOM12 ,
CUSTOM13 ,
CUSTOM14 ,
CUSTOM15 )
select /*+ ordered */
prj.olap_group,
src.PROJECT_ID,
nvl(src.TASK_ID, -1) TASK_ID,
src.PERIOD_ID,
substrb(src.PERIOD_TYPE,1,1) CALENDAR_TYPE,
prj.PROJECT_CURRENCY_CODE PRJ_CURRENCY_CODE,
sum(src.PRJ_REVENUE) PRJ_REVENUE,
-- sum(src.PRJ_FUNDING) PRJ_FUNDING,
sum(src.PRJ_INITIAL_FUNDING_AMOUNT) PRJ_INITIAL_FUNDING_AMOUNT,
sum(src.INITIAL_FUNDING_COUNT) INITIAL_FUNDING_COUNT,
sum(src.PRJ_ADDITIONAL_FUNDING_AMOUNT)PRJ_ADDITIONAL_FUNDING_AMOUNT,
sum(src.ADDITIONAL_FUNDING_COUNT) ADDITIONAL_FUNDING_COUNT,
sum(src.PRJ_CANCELLED_FUNDING_AMOUNT) PRJ_CANCELLED_FUNDING_AMOUNT,
sum(src.CANCELLED_FUNDING_COUNT) CANCELLED_FUNDING_COUNT,
sum(src.PRJ_FUNDING_ADJUSTMENT_AMOUNT)PRJ_FUNDING_ADJUSTMENT_AMOUNT,
sum(src.FUNDING_ADJUSTMENT_COUNT) FUNDING_ADJUSTMENT_COUNT,
sum(src.PRJ_REVENUE_WRITEOFF) PRJ_REVENUE_WRITEOFF,
sum(src.PRJ_AR_INVOICE_AMOUNT) PRJ_AR_INVOICE_AMOUNT,
sum(src.AR_INVOICE_COUNT) AR_INVOICE_COUNT,
--sum(src.PRJ_AR_CASH_APPLIED_AMOUNT) PRJ_AR_CASH_APPLIED_AMOUNT,
sum(src.AR_CASH_APPLIED_COUNT) AR_CASH_APPLIED_COUNT,
sum(src.PRJ_AR_INVOICE_WRITEOFF_AMOUNT)
PRJ_AR_INVOICE_WRITEOFF_AMOUNT,
sum(src.AR_INVOICE_WRITEOFF_COUNT) AR_INVOICE_WRITEOFF_COUNT,
sum(src.PRJ_AR_CREDIT_MEMO_AMOUNT) PRJ_AR_CREDIT_MEMO_AMOUNT,
sum(src.AR_CREDIT_MEMO_COUNT) AR_CREDIT_MEMO_COUNT,
sum(src.PRJ_UNBILLED_RECEIVABLES) PRJ_UNBILLED_RECEIVABLES,
sum(src.PRJ_UNEARNED_REVENUE) PRJ_UNEARNED_REVENUE,
sum(src.PRJ_AR_UNAPPR_INVOICE_AMOUNT) PRJ_AR_UNAPPR_INVOICE_AMOUNT,
sum(src.AR_UNAPPR_INVOICE_COUNT) AR_UNAPPR_INVOICE_COUNT,
sum(src.PRJ_AR_APPR_INVOICE_AMOUNT) PRJ_AR_APPR_INVOICE_AMOUNT,
sum(src.AR_APPR_INVOICE_COUNT) AR_APPR_INVOICE_COUNT,
sum(src.PRJ_AR_AMOUNT_DUE) PRJ_AR_AMOUNT_DUE,
sum(src.AR_COUNT_DUE) AR_COUNT_DUE,
sum(src.PRJ_AR_AMOUNT_OVERDUE) PRJ_AR_AMOUNT_OVERDUE,
sum(src.AR_COUNT_OVERDUE) AR_COUNT_OVERDUE,
sysdate,
-1,
sysdate,
-1,
-1,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0
from
PJI_FM_AGGR_ACT4 src,
PA_PROJECTS_ALL prj,
PJI_ORG_EXTR_INFO info,
pa_pjt_proj_batch batch
where
src.PROJECT_ID = prj.PROJECT_ID and
prj.ORG_ID = info.ORG_ID and
prj.project_id = batch.project_id and
prj.pjt_rollup_enabled_flag = 'Y'
group by
prj.olap_group,
src.PROJECT_ID,
src.PROJECT_ORG_ID,
src.PROJECT_ORGANIZATION_ID,
nvl(src.TASK_ID, -1),
src.PERIOD_TYPE,
src.PERIOD_ID,
prj.PROJECT_CURRENCY_CODE;
insert into pa_ac_olap_stage
(OLAP_GROUP,
PROJECT_ID ,
PROJECT_ELEMENT_ID ,
TIME_ID ,
CALENDAR_TYPE ,
CURRENCY_CODE ,
REVENUE ,
INITIAL_FUNDING_AMOUNT ,
INITIAL_FUNDING_COUNT ,
ADDITIONAL_FUNDING_AMOUNT ,
ADDITIONAL_FUNDING_COUNT ,
CANCELLED_FUNDING_AMOUNT ,
CANCELLED_FUNDING_COUNT ,
FUNDING_ADJUSTMENT_AMOUNT ,
FUNDING_ADJUSTMENT_COUNT ,
REVENUE_WRITEOFF ,
AR_INVOICE_AMOUNT ,
AR_INVOICE_COUNT ,
AR_CASH_APPLIED_AMOUNT ,
AR_INVOICE_WRITE_OFF_AMOUNT ,
AR_INVOICE_WRITEOFF_COUNT ,
AR_CREDIT_MEMO_AMOUNT ,
AR_CREDIT_MEMO_COUNT ,
UNBILLED_RECEIVABLES ,
UNEARNED_REVENUE ,
AR_UNAPPR_INVOICE_AMOUNT ,
AR_UNAPPR_INVOICE_COUNT ,
AR_APPR_INVOICE_AMOUNT ,
AR_APPR_INVOICE_COUNT ,
AR_AMOUNT_DUE ,
AR_COUNT_DUE ,
AR_AMOUNT_OVERDUE ,
AR_COUNT_OVERDUE ,
--DORMANT_BACKLOG_INACTIV ,
--DORMANT_BACKLOG_START ,
--LOST_BACKLOG ,
--ACTIVE_BACKLOG ,
--REVENUE_AT_RISK ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
CREATION_DATE ,
CREATED_BY ,
LAST_UPDATE_LOGIN ,
CUSTOM1 ,
CUSTOM2 ,
CUSTOM3 ,
CUSTOM4 ,
CUSTOM5 ,
CUSTOM6 ,
CUSTOM7 ,
CUSTOM8 ,
CUSTOM9 ,
CUSTOM10 ,
CUSTOM11 ,
CUSTOM12 ,
CUSTOM13 ,
CUSTOM14 ,
CUSTOM15 )
select /*+ ordered */
prj.olap_group,
src.PROJECT_ID,
nvl(src.TASK_ID, -1) TASK_ID,
src.PERIOD_ID,
substrb(src.PERIOD_TYPE,1,1) CALENDAR_TYPE,
prj.PROJFUNC_CURRENCY_CODE PF_CURRENCY_CODE,
sum(src.POU_REVENUE) POU_REVENUE,
-- sum(src.POU_FUNDING) POU_FUNDING,
sum(src.POU_INITIAL_FUNDING_AMOUNT) POU_INITIAL_FUNDING_AMOUNT,
sum(src.INITIAL_FUNDING_COUNT) INITIAL_FUNDING_COUNT,
sum(src.POU_ADDITIONAL_FUNDING_AMOUNT)POU_ADDITIONAL_FUNDING_AMOUNT,
sum(src.ADDITIONAL_FUNDING_COUNT) ADDITIONAL_FUNDING_COUNT,
sum(src.POU_CANCELLED_FUNDING_AMOUNT) POU_CANCELLED_FUNDING_AMOUNT,
sum(src.CANCELLED_FUNDING_COUNT) CANCELLED_FUNDING_COUNT,
sum(src.POU_FUNDING_ADJUSTMENT_AMOUNT)POU_FUNDING_ADJUSTMENT_AMOUNT,
sum(src.FUNDING_ADJUSTMENT_COUNT) FUNDING_ADJUSTMENT_COUNT,
sum(src.POU_REVENUE_WRITEOFF) POU_REVENUE_WRITEOFF,
sum(src.POU_AR_INVOICE_AMOUNT) POU_AR_INVOICE_AMOUNT,
sum(src.AR_INVOICE_COUNT) AR_INVOICE_COUNT,
--sum(src.POU_AR_CASH_APPLIED_AMOUNT) POU_AR_CASH_APPLIED_AMOUNT,
sum(src.AR_CASH_APPLIED_COUNT) AR_CASH_APPLIED_COUNT,
sum(src.POU_AR_INVOICE_WRITEOFF_AMOUNT)
POU_AR_INVOICE_WRITEOFF_AMOUNT,
sum(src.AR_INVOICE_WRITEOFF_COUNT) AR_INVOICE_WRITEOFF_COUNT,
sum(src.POU_AR_CREDIT_MEMO_AMOUNT) POU_AR_CREDIT_MEMO_AMOUNT,
sum(src.AR_CREDIT_MEMO_COUNT) AR_CREDIT_MEMO_COUNT,
sum(src.POU_UNBILLED_RECEIVABLES) POU_UNBILLED_RECEIVABLES,
sum(src.POU_UNEARNED_REVENUE) POU_UNEARNED_REVENUE,
sum(src.POU_AR_UNAPPR_INVOICE_AMOUNT) POU_AR_UNAPPR_INVOICE_AMOUNT,
sum(src.AR_UNAPPR_INVOICE_COUNT) AR_UNAPPR_INVOICE_COUNT,
sum(src.POU_AR_APPR_INVOICE_AMOUNT) POU_AR_APPR_INVOICE_AMOUNT,
sum(src.AR_APPR_INVOICE_COUNT) AR_APPR_INVOICE_COUNT,
sum(src.POU_AR_AMOUNT_DUE) POU_AR_AMOUNT_DUE,
sum(src.AR_COUNT_DUE) AR_COUNT_DUE,
sum(src.POU_AR_AMOUNT_OVERDUE) POU_AR_AMOUNT_OVERDUE,
sum(src.AR_COUNT_OVERDUE) AR_COUNT_OVERDUE,
sysdate,
-1,
sysdate,
-1,
-1,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0
from
PJI_FM_AGGR_ACT4 src,
PA_PROJECTS_ALL prj,
PJI_ORG_EXTR_INFO info,
pa_pjt_proj_batch batch
where
src.PROJECT_ID = prj.PROJECT_ID and
prj.ORG_ID = info.ORG_ID and
prj.project_id = batch.project_id and
prj.pjt_rollup_enabled_flag = 'Y' and
prj.project_currency_code <> prj.projfunc_currency_code
group by
prj.olap_group,
src.PROJECT_ID,
src.PROJECT_ORG_ID,
src.PROJECT_ORGANIZATION_ID,
nvl(src.TASK_ID, -1),
src.PERIOD_TYPE,
src.PERIOD_ID,
prj.PROJFUNC_CURRENCY_CODE;
select project_id
from pa_projects_all
where template_flag = 'N';
g_rbs_version_id_arr.delete;
g_rbs_version_id_index_arr.delete;
g_rbs_version_id_rule_arr.delete;
insert into pa_pjt_proj_batch
(
WORKER_ID,
PROJECT_ID,
PJI_PROJECT_STATUS,
EXTRACTION_TYPE,
EXTRACTION_STATUS,
PROJECT_TYPE,
PROJECT_ORG_ID,
PROJECT_ORGANIZATION_ID,
PROJECT_TYPE_CLASS,
PRJ_CURRENCY_CODE,
PROJECT_ACTIVE_FLAG
)
values(1,
p_project_id_tbl(i),
null,
null,
NULL,
null,
null,
NULL,
NULL,
null,
'Y' );
p_project_id_tbl.delete;
delete from pa_pjt_proj_batch;
PA_olap_PVT.update_cube;