[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER (l_e_process,l_e_process),
DECODE(l_e_extraction_type,'PARTIAL',g_prtl_disp_name,'RBS',g_rbs_disp_name,NULL,NULL,g_incr_disp_name)
into p_request_id,p_prog_name
from dual;
SELECT
DECODE(l_e_extraction_type,'PARTIAL','P','RBS','R',NULL,NULL,'I'),
DECODE(l_e_extraction_type,'PARTIAL',g_prtl_disp_name,'RBS',g_rbs_disp_name,NULL,NULL,g_incr_disp_name),
DECODE(l_e_project_operating_unit,-1,NULL,l_e_project_operating_unit),
DECODE(l_e_project_organization_id,-1,NULL,l_e_project_organization_id),
DECODE(l_e_project_type,'PJI$NULL',NULL,l_e_project_type),
DECODE(l_e_from_project,'PJI$NULL',NULL,l_e_from_project),
DECODE(l_e_to_project,'PJI$NULL',NULL,l_e_to_project),
DECODE(l_e_plan_type_id,-1,NULL,l_e_plan_type_id),
DECODE(l_e_rbs_header_id,-1,NULL,l_e_rbs_header_id),
DECODE(l_e_transaction_type,'PJI$NULL',NULL,l_e_transaction_type),
DECODE(l_e_plan_versions,'PJI$NULL',NULL,l_e_plan_versions)
INTO
l_e_run_mode,
l_e_program,
l_e_project_operating_unit,
l_e_project_organization_id,
l_e_project_type,
l_e_from_project,
l_e_to_project,
l_e_plan_type_id,
l_e_rbs_header_id,
l_e_transaction_type,
l_e_plan_versions
FROM
DUAL;
l_no_selection varchar2(50);
FND_MESSAGE.SET_NAME('PJI', 'PJI_PJP_SUM_NO_SELECTION');
l_no_selection := FND_MESSAGE.GET;
l_project_operating_unit_name := l_no_selection;
select NAME
into l_project_operating_unit_name
from HR_OPERATING_UNITS
where ORGANIZATION_ID = p_operating_unit;
l_project_type := l_no_selection;
l_project_organization := l_no_selection;
select NAME
into l_project_organization
from HR_ALL_ORGANIZATION_UNITS_VL
where ORGANIZATION_ID = p_project_organization_id;
l_from_project := l_no_selection;
l_to_project := l_no_selection;
l_no_selection varchar2(50);
FND_MESSAGE.SET_NAME('PJI', 'PJI_PJP_SUM_NO_SELECTION');
l_no_selection := FND_MESSAGE.GET;
l_project_operating_unit_name := l_no_selection;
select NAME
into l_project_operating_unit_name
from HR_OPERATING_UNITS
where ORGANIZATION_ID = l_project_operating_unit;
l_project_type := l_no_selection;
l_project_organization := l_no_selection;
select NAME
into l_project_organization
from HR_ALL_ORGANIZATION_UNITS_VL
where ORGANIZATION_ID = l_project_organization_id;
l_from_project := l_no_selection;
l_to_project := l_no_selection;
l_plan_type := l_no_selection;
select NAME
into l_plan_type
from PA_FIN_PLAN_TYPES_VL
where FIN_PLAN_TYPE_ID = l_plan_type_id;
l_rbs_header := l_no_selection;
select NAME
into l_rbs_header
from PA_RBS_HEADERS_VL
where RBS_HEADER_ID = l_rbs_header_id;
l_transaction_type := l_no_selection;
select MEANING
into l_transaction_type
from fnd_lookup_values_vl
where LOOKUP_TYPE = 'PJI_REF_TXN_TYPE' and
LOOKUP_CODE =l_transaction_type_id;
l_plan_versions := l_no_selection;
select MEANING
into l_plan_versions
from fnd_lookup_values_vl
where LOOKUP_TYPE = 'PJI_REF_PLAN_VERSION' and
LOOKUP_CODE =l_plan_versions_id;
l_only_pt_projects := l_no_selection;
select MEANING
into l_only_pt_projects
from FND_LOOKUPS
where LOOKUP_TYPE = 'YES_NO' and
LOOKUP_CODE = l_only_pt_projects;
select pa.segment1,map.act_err_msg
from pji_pjp_proj_batch_map map,pa_projects_all pa
where pa.project_id=map.project_id
and map.worker_id=p_worker_id
and map.act_err_msg is not null
order by segment1;
select pa.segment1,pa.name
from pji_pjp_proj_batch_map map,pa_projects_all pa
where pa.project_id=map.project_id
and map.worker_id=p_worker_id
and map.act_err_msg is null
order by segment1;
l_last_update_date date := SYSDATE;
l_last_updated_by NUMBER := FND_GLOBAL.USER_ID;
l_last_update_login NUMBER := FND_GLOBAL.LOGIN_ID;
select /*+ INDEX_FFS (den PJI_XBS_DENORM_N3) */
SUP_LEVEL
from
PJI_XBS_DENORM den
where
STRUCT_TYPE = 'PRG' and
SUB_LEVEL = SUP_LEVEL and
EXISTS ( SELECT 1 from PJI_FM_EXTR_PLNVER4 ver where ver.worker_id = p_worker_id
and ver.project_id = den.SUP_PROJECT_ID)
group by
SUP_LEVEL
order by
SUP_LEVEL desc;
select count(bmap.project_id) into l_count
from PA_PJI_PROJ_EVENTS_LOG elog,
PJI_PJP_PROJ_BATCH_MAP bmap
where elog.event_object = to_char(bmap.project_id)
and elog.event_type = 'PLANTYPE_UPG'
and bmap.worker_id = p_worker_id;
DELETE PJI_FM_EXTR_PLNVER4 where worker_id = p_worker_id;
DELETE PJI_FP_AGGR_PJP1 where worker_id = p_worker_id;
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
DISTINCT
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
, NULL time_dangling_flag
, NULL rate_dangling_flag
, NULL PROJECT_TYPE_CLASS
, is_wp_flag
, current_flag , original_flag
, current_original_flag
, baselined_flag
, SECONDARY_RBS_FLAG
, lp_flag
FROM
(
SELECT p_worker_id worker_id,
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)
) wbs_struct_version_id
, fpo.rbs_version_id rbs_struct_version_id -- extract for this rbs version id
, DECODE (bv.version_type, 'COST' ,'C' , 'REVENUE', 'R', 'A') plan_type_code
, 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
, 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) -- IN NUMBER
-- , PA_PROJECT_STRUCTURE_UTILS.GET_FIN_STRUC_VER_ID(bv.project_id)
, 'Y'
, 'N')
, 'N'
) lp_flag
FROM
pa_budget_versions bv
, pa_proj_fp_options fpo
, (select distinct bmap.project_id,elog.ATTRIBUTE1 plan_type_id from
PA_PJI_PROJ_EVENTS_LOG elog,
PJI_PJP_PROJ_BATCH_MAP bmap
where elog.event_object = to_char(bmap.project_id)
and elog.EVENT_TYPE = 'PLANTYPE_UPG'
and bmap.worker_id = p_worker_id) logmap
WHERE 1=1
AND logmap.project_id = bv.project_id
AND bv.fin_plan_type_id = logmap.plan_type_id
AND bv.version_type is not NULL
AND bv.fin_plan_type_id is not NULL
AND fpo.project_id = bv.project_id
AND bv.fin_plan_type_id = fpo.fin_plan_type_id
AND bv.budget_version_id = fpo.fin_plan_version_id
AND (bv.current_original_flag = 'Y'
OR (bv.current_flag||DECODE(bv.baselined_date, NULL, 'N', 'Y')) = 'YY')
AND fpo.fin_plan_option_level_code = 'PLAN_VERSION'
AND bv.version_type IN ( 'ALL' , 'COST' , 'REVENUE'));
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 DISTINCT bv.worker_id worker_id
, den.sup_project_id project_id
, cbco.plan_version_id
, PA_PROJECT_STRUCTURE_UTILS.GET_FIN_STRUC_VER_ID(den.sup_project_id) wbs_struct_version_id
, bv.rbs_struct_version_id
, bv.plan_type_code
, bv.plan_type_id
, bv.time_phased_type_code
, NULL -- time dangl flg
, NULL -- rate dangl flg
, NULL -- project type class
, 'N' -- wp flag
, DECODE(cbco.plan_version_id, -3, 'Y', 'N') current_flag
, DECODE(cbco.plan_version_id, -4, 'Y', 'N') original_flag
, DECODE(cbco.plan_version_id, -4, 'Y', 'N') curr_original_flag
, DECODE(cbco.plan_version_id, -3, 'Y', 'N') baselined_flag
, bv.SECONDARY_RBS_FLAG
, bv.lp_flag
FROM PJI_FM_EXTR_PLNVER4 bv
, pji_xbs_denorm den
, ( SELECT -3 plan_version_id FROM DUAL
UNION ALL
SELECT -4 FROM DUAL ) cbco
WHERE 1=1
AND bv.plan_version_id > 0
AND bv.wp_flag = 'N'
AND bv.baselined_flag = 'Y'
AND den.struct_version_id IS NULL
AND den.struct_type = 'PRG'
AND den.sub_id = bv.wbs_struct_version_id
AND NVL(den.relationship_type, 'WF') IN ('LF', 'WF');
DELETE pji_rollup_level_status hdr
where hdr.plan_version_id < -1
and exists (select 1 from pji_fm_extr_plnver4 ver3
where ver3.worker_id = p_worker_id
and ver3.project_id = hdr.project_id
and ver3.plan_version_id = hdr.plan_version_id
and ver3.plan_version_id < -1);
DELETE FROM pji_fp_xbs_accum_f fact
WHERE fact.plan_version_id < -1
and exists (select 1 from pji_fm_extr_plnver4 ver3
where ver3.worker_id = p_worker_id
and ver3.project_id = fact.project_id
and ver3.plan_version_id = fact.plan_version_id
and ver3.plan_type_id = fact.plan_type_id
and ver3.plan_version_id < -1);
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
)
select WORKER_ID
, F.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
, F.PLAN_VERSION_ID
, f.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
, 'UPD'
, RATE_DANGLING_FLAG
, TIME_DANGLING_FLAG
, SYSDATE
, SYSDATE
, 0
,ver.PLAN_TYPE_CODE
FROM pji_fp_xbs_accum_f f
, pji_fm_extr_plnver4 ver
WHERE 1 = 1
AND ver.project_id = f.project_id
AND ver.plan_version_id = f.plan_version_id
AND ver.plan_type_id = f.plan_type_id
AND ver.plan_version_id > 0
AND f.rbs_aggr_level IN ( 'L', 'T' )
AND f.wbs_rollup_flag = 'N'
AND f.prg_rollup_flag = 'N'
AND ver.worker_id = p_worker_id;
insert into PJI_FP_AGGR_PJP1
(
WORKER_ID,
RECORD_TYPE,
PRG_LEVEL,
LINE_TYPE,
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,
PLAN_TYPE_CODE,
RAW_COST,
BRDN_COST,
REVENUE,
BILL_RAW_COST,
BILL_BRDN_COST,
BILL_LABOR_RAW_COST,
BILL_LABOR_BRDN_COST,
BILL_LABOR_HRS,
EQUIPMENT_RAW_COST,
EQUIPMENT_BRDN_COST,
CAPITALIZABLE_RAW_COST,
CAPITALIZABLE_BRDN_COST,
LABOR_RAW_COST,
LABOR_BRDN_COST,
LABOR_HRS,
LABOR_REVENUE,
EQUIPMENT_HOURS,
BILLABLE_EQUIPMENT_HOURS,
SUP_INV_COMMITTED_COST,
PO_COMMITTED_COST,
PR_COMMITTED_COST,
OTH_COMMITTED_COST,
ACT_LABOR_HRS,
ACT_EQUIP_HRS,
ACT_LABOR_BRDN_COST,
ACT_EQUIP_BRDN_COST,
ACT_BRDN_COST,
ACT_RAW_COST,
ACT_REVENUE,
ACT_LABOR_RAW_COST,
ACT_EQUIP_RAW_COST,
ETC_LABOR_HRS,
ETC_EQUIP_HRS,
ETC_LABOR_BRDN_COST,
ETC_EQUIP_BRDN_COST,
ETC_BRDN_COST,
ETC_RAW_COST,
ETC_LABOR_RAW_COST,
ETC_EQUIP_RAW_COST,
CUSTOM1,
CUSTOM2,
CUSTOM3,
CUSTOM4,
CUSTOM5,
CUSTOM6,
CUSTOM7,
CUSTOM8,
CUSTOM9,
CUSTOM10,
CUSTOM11,
CUSTOM12,
CUSTOM13,
CUSTOM14,
CUSTOM15
)
select
pjp1_i.WORKER_ID,
pjp1_i.RECORD_TYPE,
pjp1_i.PRG_LEVEL,
pjp1_i.LINE_TYPE,
pjp1_i.PROJECT_ID,
pjp1_i.PROJECT_ORG_ID,
pjp1_i.PROJECT_ORGANIZATION_ID,
pjp1_i.PROJECT_ELEMENT_ID,
pjp1_i.TIME_ID,
pjp1_i.PERIOD_TYPE_ID,
pjp1_i.CALENDAR_TYPE,
pjp1_i.RBS_AGGR_LEVEL,
pjp1_i.WBS_ROLLUP_FLAG,
pjp1_i.PRG_ROLLUP_FLAG,
pjp1_i.CURR_RECORD_TYPE_ID,
pjp1_i.CURRENCY_CODE,
pjp1_i.RBS_ELEMENT_ID,
pjp1_i.RBS_VERSION_ID,
pjp1_i.PLAN_VERSION_ID,
pjp1_i.PLAN_TYPE_ID,
pjp1_i.PLAN_TYPE_CODE,
decode(pjp1_i.RELATIONSHIP_TYPE
|| '_' || pjp1_i.WBS_ROLLUP_FLAG
|| '_' || pjp1_i.PRG_ROLLUP_FLAG
|| '_' || pjp1_i.SUB_STATUS_CODE
|| '_' || pjp1_i.SUP_STATUS_CODE,
'LW_N_Y__', to_number(null),
decode(pjp1_i.RELATIONSHIP_TYPE
|| '_' || pjp1_i.WBS_ROLLUP_FLAG
|| '_' || pjp1_i.PRG_ROLLUP_FLAG
|| '_' || pjp1_i.SUP_VER_ENABLED,
'LW_N_Y_Y', to_number(null),
pjp1_i.RAW_COST)) RAW_COST,
decode(pjp1_i.RELATIONSHIP_TYPE
|| '_' || pjp1_i.WBS_ROLLUP_FLAG
|| '_' || pjp1_i.PRG_ROLLUP_FLAG
|| '_' || pjp1_i.SUB_STATUS_CODE
|| '_' || pjp1_i.SUP_STATUS_CODE,
'LW_N_Y__', to_number(null),
decode(pjp1_i.RELATIONSHIP_TYPE
|| '_' || pjp1_i.WBS_ROLLUP_FLAG
|| '_' || pjp1_i.PRG_ROLLUP_FLAG
|| '_' || pjp1_i.SUP_VER_ENABLED,
'LW_N_Y_Y', to_number(null),
pjp1_i.BRDN_COST)) BRDN_COST,
decode(pjp1_i.RELATIONSHIP_TYPE
|| '_' || pjp1_i.WBS_ROLLUP_FLAG
|| '_' || pjp1_i.PRG_ROLLUP_FLAG
|| '_' || pjp1_i.SUB_STATUS_CODE
|| '_' || pjp1_i.SUP_STATUS_CODE,
'LW_N_Y__', to_number(null),
decode(pjp1_i.RELATIONSHIP_TYPE
|| '_' || pjp1_i.WBS_ROLLUP_FLAG
|| '_' || pjp1_i.PRG_ROLLUP_FLAG
|| '_' || pjp1_i.SUP_VER_ENABLED,
'LW_N_Y_Y', to_number(null),
pjp1_i.REVENUE)) REVENUE,
decode(pjp1_i.RELATIONSHIP_TYPE
|| '_' || pjp1_i.WBS_ROLLUP_FLAG
|| '_' || pjp1_i.PRG_ROLLUP_FLAG
|| '_' || pjp1_i.SUB_STATUS_CODE
|| '_' || pjp1_i.SUP_STATUS_CODE,
'LW_N_Y__', to_number(null),
decode(pjp1_i.RELATIONSHIP_TYPE
|| '_' || pjp1_i.WBS_ROLLUP_FLAG
|| '_' || pjp1_i.PRG_ROLLUP_FLAG
|| '_' || pjp1_i.SUP_VER_ENABLED,
'LW_N_Y_Y', to_number(null),
pjp1_i.BILL_RAW_COST)) BILL_RAW_COST,
decode(pjp1_i.RELATIONSHIP_TYPE
|| '_' || pjp1_i.WBS_ROLLUP_FLAG
|| '_' || pjp1_i.PRG_ROLLUP_FLAG
|| '_' || pjp1_i.SUB_STATUS_CODE
|| '_' || pjp1_i.SUP_STATUS_CODE,
'LW_N_Y__', to_number(null),
decode(pjp1_i.RELATIONSHIP_TYPE
|| '_' || pjp1_i.WBS_ROLLUP_FLAG
|| '_' || pjp1_i.PRG_ROLLUP_FLAG
|| '_' || pjp1_i.SUP_VER_ENABLED,
'LW_N_Y_Y', to_number(null),
pjp1_i.BILL_BRDN_COST)) BILL_BRDN_COST,
decode(pjp1_i.RELATIONSHIP_TYPE
|| '_' || pjp1_i.WBS_ROLLUP_FLAG
|| '_' || pjp1_i.PRG_ROLLUP_FLAG
|| '_' || pjp1_i.SUB_STATUS_CODE
|| '_' || pjp1_i.SUP_STATUS_CODE,
'LW_N_Y__', to_number(null),
decode(pjp1_i.RELATIONSHIP_TYPE
|| '_' || pjp1_i.WBS_ROLLUP_FLAG
|| '_' || pjp1_i.PRG_ROLLUP_FLAG
|| '_' || pjp1_i.SUP_VER_ENABLED,
'LW_N_Y_Y', to_number(null),
pjp1_i.BILL_LABOR_RAW_COST)) BILL_LABOR_RAW_COST,
decode(pjp1_i.RELATIONSHIP_TYPE
|| '_' || pjp1_i.WBS_ROLLUP_FLAG
|| '_' || pjp1_i.PRG_ROLLUP_FLAG
|| '_' || pjp1_i.SUB_STATUS_CODE
|| '_' || pjp1_i.SUP_STATUS_CODE,
'LW_N_Y__', to_number(null),
decode(pjp1_i.RELATIONSHIP_TYPE
|| '_' || pjp1_i.WBS_ROLLUP_FLAG
|| '_' || pjp1_i.PRG_ROLLUP_FLAG
|| '_' || pjp1_i.SUP_VER_ENABLED,
'LW_N_Y_Y', to_number(null),
pjp1_i.BILL_LABOR_BRDN_COST)) BILL_LABOR_BRDN_COST,
decode(pjp1_i.RELATIONSHIP_TYPE
|| '_' || pjp1_i.WBS_ROLLUP_FLAG
|| '_' || pjp1_i.PRG_ROLLUP_FLAG
|| '_' || pjp1_i.SUB_STATUS_CODE
|| '_' || pjp1_i.SUP_STATUS_CODE,
'LW_N_Y__', to_number(null),
decode(pjp1_i.RELATIONSHIP_TYPE
|| '_' || pjp1_i.WBS_ROLLUP_FLAG
|| '_' || pjp1_i.PRG_ROLLUP_FLAG
|| '_' || pjp1_i.SUP_VER_ENABLED,
'LW_N_Y_Y', to_number(null),
pjp1_i.BILL_LABOR_HRS)) BILL_LABOR_HRS,
decode(pjp1_i.RELATIONSHIP_TYPE
|| '_' || pjp1_i.WBS_ROLLUP_FLAG
|| '_' || pjp1_i.PRG_ROLLUP_FLAG
|| '_' || pjp1_i.SUB_STATUS_CODE
|| '_' || pjp1_i.SUP_STATUS_CODE,
'LW_N_Y__', to_number(null),
decode(pjp1_i.RELATIONSHIP_TYPE
|| '_' || pjp1_i.WBS_ROLLUP_FLAG
|| '_' || pjp1_i.PRG_ROLLUP_FLAG
|| '_' || pjp1_i.SUP_VER_ENABLED,
'LW_N_Y_Y', to_number(null),
pjp1_i.EQUIPMENT_RAW_COST)) EQUIPMENT_RAW_COST,
decode(pjp1_i.RELATIONSHIP_TYPE
|| '_' || pjp1_i.WBS_ROLLUP_FLAG
|| '_' || pjp1_i.PRG_ROLLUP_FLAG
|| '_' || pjp1_i.SUB_STATUS_CODE
|| '_' || pjp1_i.SUP_STATUS_CODE,
'LW_N_Y__', to_number(null),
decode(pjp1_i.RELATIONSHIP_TYPE
|| '_' || pjp1_i.WBS_ROLLUP_FLAG
|| '_' || pjp1_i.PRG_ROLLUP_FLAG
|| '_' || pjp1_i.SUP_VER_ENABLED,
'LW_N_Y_Y', to_number(null),
pjp1_i.EQUIPMENT_BRDN_COST)) EQUIPMENT_BRDN_COST,
decode(pjp1_i.RELATIONSHIP_TYPE
|| '_' || pjp1_i.WBS_ROLLUP_FLAG
|| '_' || pjp1_i.PRG_ROLLUP_FLAG
|| '_' || pjp1_i.SUB_STATUS_CODE
|| '_' || pjp1_i.SUP_STATUS_CODE,
'LW_N_Y__', to_number(null),
decode(pjp1_i.RELATIONSHIP_TYPE
|| '_' || pjp1_i.WBS_ROLLUP_FLAG
|| '_' || pjp1_i.PRG_ROLLUP_FLAG
|| '_' || pjp1_i.SUP_VER_ENABLED,
'LW_N_Y_Y', to_number(null),
pjp1_i.CAPITALIZABLE_RAW_COST)) CAPITALIZABLE_RAW_COST,
decode(pjp1_i.RELATIONSHIP_TYPE
|| '_' || pjp1_i.WBS_ROLLUP_FLAG
|| '_' || pjp1_i.PRG_ROLLUP_FLAG
|| '_' || pjp1_i.SUB_STATUS_CODE
|| '_' || pjp1_i.SUP_STATUS_CODE,
'LW_N_Y__', to_number(null),
decode(pjp1_i.RELATIONSHIP_TYPE
|| '_' || pjp1_i.WBS_ROLLUP_FLAG
|| '_' || pjp1_i.PRG_ROLLUP_FLAG
|| '_' || pjp1_i.SUP_VER_ENABLED,
'LW_N_Y_Y', to_number(null),
pjp1_i.CAPITALIZABLE_BRDN_COST)) CAPITALIZABLE_BRDN_COST,
decode(pjp1_i.RELATIONSHIP_TYPE
|| '_' || pjp1_i.WBS_ROLLUP_FLAG
|| '_' || pjp1_i.PRG_ROLLUP_FLAG
|| '_' || pjp1_i.SUB_STATUS_CODE
|| '_' || pjp1_i.SUP_STATUS_CODE,
'LW_N_Y__', to_number(null),
decode(pjp1_i.RELATIONSHIP_TYPE
|| '_' || pjp1_i.WBS_ROLLUP_FLAG
|| '_' || pjp1_i.PRG_ROLLUP_FLAG
|| '_' || pjp1_i.SUP_VER_ENABLED,
'LW_N_Y_Y', to_number(null),
pjp1_i.LABOR_RAW_COST)) LABOR_RAW_COST,
decode(pjp1_i.RELATIONSHIP_TYPE
|| '_' || pjp1_i.WBS_ROLLUP_FLAG
|| '_' || pjp1_i.PRG_ROLLUP_FLAG
|| '_' || pjp1_i.SUB_STATUS_CODE
|| '_' || pjp1_i.SUP_STATUS_CODE,
'LW_N_Y__', to_number(null),
decode(pjp1_i.RELATIONSHIP_TYPE
|| '_' || pjp1_i.WBS_ROLLUP_FLAG
|| '_' || pjp1_i.PRG_ROLLUP_FLAG
|| '_' || pjp1_i.SUP_VER_ENABLED,
'LW_N_Y_Y', to_number(null),
pjp1_i.LABOR_BRDN_COST)) LABOR_BRDN_COST,
decode(pjp1_i.RELATIONSHIP_TYPE
|| '_' || pjp1_i.WBS_ROLLUP_FLAG
|| '_' || pjp1_i.PRG_ROLLUP_FLAG
|| '_' || pjp1_i.SUB_STATUS_CODE
|| '_' || pjp1_i.SUP_STATUS_CODE,
'LW_N_Y__', to_number(null),
decode(pjp1_i.RELATIONSHIP_TYPE
|| '_' || pjp1_i.WBS_ROLLUP_FLAG
|| '_' || pjp1_i.PRG_ROLLUP_FLAG
|| '_' || pjp1_i.SUP_VER_ENABLED,
'LW_N_Y_Y', to_number(null),
pjp1_i.LABOR_HRS)) LABOR_HRS,
decode(pjp1_i.RELATIONSHIP_TYPE
|| '_' || pjp1_i.WBS_ROLLUP_FLAG
|| '_' || pjp1_i.PRG_ROLLUP_FLAG
|| '_' || pjp1_i.SUB_STATUS_CODE
|| '_' || pjp1_i.SUP_STATUS_CODE,
'LW_N_Y__', to_number(null),
decode(pjp1_i.RELATIONSHIP_TYPE
|| '_' || pjp1_i.WBS_ROLLUP_FLAG
|| '_' || pjp1_i.PRG_ROLLUP_FLAG
|| '_' || pjp1_i.SUP_VER_ENABLED,
'LW_N_Y_Y', to_number(null),
pjp1_i.LABOR_REVENUE)) LABOR_REVENUE,
decode(pjp1_i.RELATIONSHIP_TYPE
|| '_' || pjp1_i.WBS_ROLLUP_FLAG
|| '_' || pjp1_i.PRG_ROLLUP_FLAG
|| '_' || pjp1_i.SUB_STATUS_CODE
|| '_' || pjp1_i.SUP_STATUS_CODE,
'LW_N_Y__', to_number(null),
decode(pjp1_i.RELATIONSHIP_TYPE
|| '_' || pjp1_i.WBS_ROLLUP_FLAG
|| '_' || pjp1_i.PRG_ROLLUP_FLAG
|| '_' || pjp1_i.SUP_VER_ENABLED,
'LW_N_Y_Y', to_number(null),
pjp1_i.EQUIPMENT_HOURS)) EQUIPMENT_HOURS,
decode(pjp1_i.RELATIONSHIP_TYPE
|| '_' || pjp1_i.WBS_ROLLUP_FLAG
|| '_' || pjp1_i.PRG_ROLLUP_FLAG
|| '_' || pjp1_i.SUB_STATUS_CODE
|| '_' || pjp1_i.SUP_STATUS_CODE,
'LW_N_Y__', to_number(null),
decode(pjp1_i.RELATIONSHIP_TYPE
|| '_' || pjp1_i.WBS_ROLLUP_FLAG
|| '_' || pjp1_i.PRG_ROLLUP_FLAG
|| '_' || pjp1_i.SUP_VER_ENABLED,
'LW_N_Y_Y', to_number(null),
pjp1_i.BILLABLE_EQUIPMENT_HOURS)) BILLABLE_EQUIPMENT_HOURS,
decode(pjp1_i.RELATIONSHIP_TYPE
|| '_' || pjp1_i.WBS_ROLLUP_FLAG
|| '_' || pjp1_i.PRG_ROLLUP_FLAG
|| '_' || pjp1_i.SUB_STATUS_CODE
|| '_' || pjp1_i.SUP_STATUS_CODE,
'LW_N_Y__', to_number(null),
decode(pjp1_i.RELATIONSHIP_TYPE
|| '_' || pjp1_i.WBS_ROLLUP_FLAG
|| '_' || pjp1_i.PRG_ROLLUP_FLAG
|| '_' || pjp1_i.SUP_VER_ENABLED,
'LW_N_Y_Y', to_number(null),
pjp1_i.SUP_INV_COMMITTED_COST)) SUP_INV_COMMITTED_COST,
decode(pjp1_i.RELATIONSHIP_TYPE
|| '_' || pjp1_i.WBS_ROLLUP_FLAG
|| '_' || pjp1_i.PRG_ROLLUP_FLAG
|| '_' || pjp1_i.SUB_STATUS_CODE
|| '_' || pjp1_i.SUP_STATUS_CODE,
'LW_N_Y__', to_number(null),
decode(pjp1_i.RELATIONSHIP_TYPE
|| '_' || pjp1_i.WBS_ROLLUP_FLAG
|| '_' || pjp1_i.PRG_ROLLUP_FLAG
|| '_' || pjp1_i.SUP_VER_ENABLED,
'LW_N_Y_Y', to_number(null),
pjp1_i.PO_COMMITTED_COST)) PO_COMMITTED_COST,
decode(pjp1_i.RELATIONSHIP_TYPE
|| '_' || pjp1_i.WBS_ROLLUP_FLAG
|| '_' || pjp1_i.PRG_ROLLUP_FLAG
|| '_' || pjp1_i.SUB_STATUS_CODE
|| '_' || pjp1_i.SUP_STATUS_CODE,
'LW_N_Y__', to_number(null),
decode(pjp1_i.RELATIONSHIP_TYPE
|| '_' || pjp1_i.WBS_ROLLUP_FLAG
|| '_' || pjp1_i.PRG_ROLLUP_FLAG
|| '_' || pjp1_i.SUP_VER_ENABLED,
'LW_N_Y_Y', to_number(null),
pjp1_i.PR_COMMITTED_COST)) PR_COMMITTED_COST,
decode(pjp1_i.RELATIONSHIP_TYPE
|| '_' || pjp1_i.WBS_ROLLUP_FLAG
|| '_' || pjp1_i.PRG_ROLLUP_FLAG
|| '_' || pjp1_i.SUB_STATUS_CODE
|| '_' || pjp1_i.SUP_STATUS_CODE,
'LW_N_Y__', to_number(null),
decode(pjp1_i.RELATIONSHIP_TYPE
|| '_' || pjp1_i.WBS_ROLLUP_FLAG
|| '_' || pjp1_i.PRG_ROLLUP_FLAG
|| '_' || pjp1_i.SUP_VER_ENABLED,
'LW_N_Y_Y', to_number(null),
pjp1_i.OTH_COMMITTED_COST)) OTH_COMMITTED_COST,
decode(pjp1_i.RELATIONSHIP_TYPE
|| '_' || pjp1_i.WBS_ROLLUP_FLAG
|| '_' || pjp1_i.PRG_ROLLUP_FLAG
|| '_' || pjp1_i.SUP_STATUS_CODE,
'LW_N_Y_', to_number(null),
pjp1_i.ACT_LABOR_HRS) ACT_LABOR_HRS,
decode(pjp1_i.RELATIONSHIP_TYPE
|| '_' || pjp1_i.WBS_ROLLUP_FLAG
|| '_' || pjp1_i.PRG_ROLLUP_FLAG
|| '_' || pjp1_i.SUP_STATUS_CODE,
'LW_N_Y_', to_number(null),
pjp1_i.ACT_EQUIP_HRS) ACT_EQUIP_HRS,
decode(pjp1_i.RELATIONSHIP_TYPE
|| '_' || pjp1_i.WBS_ROLLUP_FLAG
|| '_' || pjp1_i.PRG_ROLLUP_FLAG
|| '_' || pjp1_i.SUP_STATUS_CODE,
'LW_N_Y_', to_number(null),
pjp1_i.ACT_LABOR_BRDN_COST) ACT_LABOR_BRDN_COST,
decode(pjp1_i.RELATIONSHIP_TYPE
|| '_' || pjp1_i.WBS_ROLLUP_FLAG
|| '_' || pjp1_i.PRG_ROLLUP_FLAG
|| '_' || pjp1_i.SUP_STATUS_CODE,
'LW_N_Y_', to_number(null),
pjp1_i.ACT_EQUIP_BRDN_COST) ACT_EQUIP_BRDN_COST,
decode(pjp1_i.RELATIONSHIP_TYPE
|| '_' || pjp1_i.WBS_ROLLUP_FLAG
|| '_' || pjp1_i.PRG_ROLLUP_FLAG
|| '_' || pjp1_i.SUP_STATUS_CODE,
'LW_N_Y_', to_number(null),
pjp1_i.ACT_BRDN_COST) ACT_BRDN_COST,
decode(pjp1_i.RELATIONSHIP_TYPE
|| '_' || pjp1_i.WBS_ROLLUP_FLAG
|| '_' || pjp1_i.PRG_ROLLUP_FLAG
|| '_' || pjp1_i.SUP_STATUS_CODE,
'LW_N_Y_', to_number(null),
pjp1_i.ACT_RAW_COST) ACT_RAW_COST,
decode(pjp1_i.RELATIONSHIP_TYPE
|| '_' || pjp1_i.WBS_ROLLUP_FLAG
|| '_' || pjp1_i.PRG_ROLLUP_FLAG
|| '_' || pjp1_i.SUP_STATUS_CODE,
'LW_N_Y_', to_number(null),
pjp1_i.ACT_REVENUE) ACT_REVENUE,
decode(pjp1_i.RELATIONSHIP_TYPE
|| '_' || pjp1_i.WBS_ROLLUP_FLAG
|| '_' || pjp1_i.PRG_ROLLUP_FLAG
|| '_' || pjp1_i.SUP_STATUS_CODE,
'LW_N_Y_', to_number(null),
pjp1_i.ACT_LABOR_RAW_COST) ACT_LABOR_RAW_COST,
decode(pjp1_i.RELATIONSHIP_TYPE
|| '_' || pjp1_i.WBS_ROLLUP_FLAG
|| '_' || pjp1_i.PRG_ROLLUP_FLAG
|| '_' || pjp1_i.SUP_STATUS_CODE,
'LW_N_Y_', to_number(null),
pjp1_i.ACT_EQUIP_RAW_COST) ACT_EQUIP_RAW_COST,
decode(pjp1_i.RELATIONSHIP_TYPE
|| '_' || pjp1_i.WBS_ROLLUP_FLAG
|| '_' || pjp1_i.PRG_ROLLUP_FLAG
|| '_' || pjp1_i.SUP_STATUS_CODE,
'LW_N_Y_', to_number(null),
pjp1_i.ETC_LABOR_HRS) ETC_LABOR_HRS,
decode(pjp1_i.RELATIONSHIP_TYPE
|| '_' || pjp1_i.WBS_ROLLUP_FLAG
|| '_' || pjp1_i.PRG_ROLLUP_FLAG
|| '_' || pjp1_i.SUP_STATUS_CODE,
'LW_N_Y_', to_number(null),
pjp1_i.ETC_EQUIP_HRS) ETC_EQUIP_HRS,
decode(pjp1_i.RELATIONSHIP_TYPE
|| '_' || pjp1_i.WBS_ROLLUP_FLAG
|| '_' || pjp1_i.PRG_ROLLUP_FLAG
|| '_' || pjp1_i.SUP_STATUS_CODE,
'LW_N_Y_', to_number(null),
pjp1_i.ETC_LABOR_BRDN_COST) ETC_LABOR_BRDN_COST,
decode(pjp1_i.RELATIONSHIP_TYPE
|| '_' || pjp1_i.WBS_ROLLUP_FLAG
|| '_' || pjp1_i.PRG_ROLLUP_FLAG
|| '_' || pjp1_i.SUP_STATUS_CODE,
'LW_N_Y_', to_number(null),
pjp1_i.ETC_EQUIP_BRDN_COST) ETC_EQUIP_BRDN_COST,
decode(pjp1_i.RELATIONSHIP_TYPE
|| '_' || pjp1_i.WBS_ROLLUP_FLAG
|| '_' || pjp1_i.PRG_ROLLUP_FLAG
|| '_' || pjp1_i.SUP_STATUS_CODE,
'LW_N_Y_', to_number(null),
pjp1_i.ETC_BRDN_COST) ETC_BRDN_COST,
decode(pjp1_i.RELATIONSHIP_TYPE
|| '_' || pjp1_i.WBS_ROLLUP_FLAG
|| '_' || pjp1_i.PRG_ROLLUP_FLAG
|| '_' || pjp1_i.SUP_STATUS_CODE,
'LW_N_Y_', to_number(null),
pjp1_i.ETC_RAW_COST) ETC_RAW_COST,
decode(pjp1_i.RELATIONSHIP_TYPE
|| '_' || pjp1_i.WBS_ROLLUP_FLAG
|| '_' || pjp1_i.PRG_ROLLUP_FLAG
|| '_' || pjp1_i.SUP_STATUS_CODE,
'LW_N_Y_', to_number(null),
pjp1_i.ETC_LABOR_RAW_COST) ETC_LABOR_RAW_COST,
decode(pjp1_i.RELATIONSHIP_TYPE
|| '_' || pjp1_i.WBS_ROLLUP_FLAG
|| '_' || pjp1_i.PRG_ROLLUP_FLAG
|| '_' || pjp1_i.SUP_STATUS_CODE,
'LW_N_Y_', to_number(null),
pjp1_i.ETC_EQUIP_RAW_COST) ETC_EQUIP_RAW_COST,
pjp1_i.CUSTOM1,
pjp1_i.CUSTOM2,
pjp1_i.CUSTOM3,
pjp1_i.CUSTOM4,
pjp1_i.CUSTOM5,
pjp1_i.CUSTOM6,
pjp1_i.CUSTOM7,
pjp1_i.CUSTOM8,
pjp1_i.CUSTOM9,
pjp1_i.CUSTOM10,
pjp1_i.CUSTOM11,
pjp1_i.CUSTOM12,
pjp1_i.CUSTOM13,
pjp1_i.CUSTOM14,
pjp1_i.CUSTOM15
from
(
select
decode(nvl(prg.SUB_EMT_ID, -1), nvl(prg.SUB_ROLLUP_ID, -1),
pjp.INSERT_FLAG, 'Y') INSERT_FLAG,
pjp.RELATIONSHIP_TYPE,
decode(nvl(prg.SUB_EMT_ID, -1), nvl(prg.SUB_ROLLUP_ID, -1),
null, sub_ver.STATUS_CODE) SUB_STATUS_CODE,
decode(nvl(prg.SUB_EMT_ID, -1), nvl(prg.SUB_ROLLUP_ID, -1),
null, sup_ver.STATUS_CODE) SUP_STATUS_CODE,
decode(nvl(prg.SUB_EMT_ID, -1), nvl(prg.SUB_ROLLUP_ID, -1),
null, sup_wpa.WP_ENABLE_VERSION_FLAG)SUP_VER_ENABLED,
decode(nvl(prg.SUB_EMT_ID, -1), nvl(prg.SUB_ROLLUP_ID, -1),
null, decode(pjp.PLAN_VERSION_ID,
-1, prg.SUP_ID,
-3, prg.SUP_ID,
-4, prg.SUP_ID,
null)) SUP_ID,
decode(nvl(prg.SUB_EMT_ID, -1), nvl(prg.SUB_ROLLUP_ID, -1),
null, decode(pjp.PLAN_VERSION_ID,
-1, prg.SUP_EMT_ID,
-3, prg.SUP_EMT_ID,
-4, prg.SUP_EMT_ID,
null)) SUP_EMT_ID,
decode(nvl(prg.SUB_EMT_ID, -1), nvl(prg.SUB_ROLLUP_ID, -1),
null, decode(pjp.PLAN_VERSION_ID,
-1, prg.WP_FLAG,
-3, prg.WP_FLAG,
-4, prg.WP_FLAG,
null)) SUP_WP_FLAG,
-- 1 WORKER_ID,
p_worker_id WORKER_ID,
'W' RECORD_TYPE,
decode(nvl(prg.SUB_EMT_ID, -1), nvl(prg.SUB_ROLLUP_ID, -1),
l_level, prg.SUP_LEVEL) PRG_LEVEL,
pjp.LINE_TYPE,
decode(nvl(prg.SUB_EMT_ID, -1), nvl(prg.SUB_ROLLUP_ID, -1),
pjp.PROJECT_ID, prg.SUP_PROJECT_ID) PROJECT_ID,
decode(nvl(prg.SUB_EMT_ID, -1), nvl(prg.SUB_ROLLUP_ID, -1),
pjp.PROJECT_ORG_ID,
prg.SUP_PROJECT_ORG_ID) PROJECT_ORG_ID,
decode(nvl(prg.SUB_EMT_ID, -1), nvl(prg.SUB_ROLLUP_ID, -1),
pjp.PROJECT_ORGANIZATION_ID,
prg.SUP_PROJECT_ORGANIZATION_ID) PROJECT_ORGANIZATION_ID,
decode(nvl(prg.SUB_EMT_ID, -1), nvl(prg.SUB_ROLLUP_ID, -1),
pjp.PROJECT_ELEMENT_ID,
prg.SUB_ROLLUP_ID) PROJECT_ELEMENT_ID,
pjp.TIME_ID,
pjp.PERIOD_TYPE_ID,
pjp.CALENDAR_TYPE,
pjp.RBS_AGGR_LEVEL,
decode(nvl(prg.SUB_EMT_ID, -1), nvl(prg.SUB_ROLLUP_ID, -1),
pjp.WBS_ROLLUP_FLAG, 'N') WBS_ROLLUP_FLAG,
decode(nvl(prg.SUB_EMT_ID, -1), nvl(prg.SUB_ROLLUP_ID, -1),
pjp.PRG_ROLLUP_FLAG, 'Y') PRG_ROLLUP_FLAG,
pjp.CURR_RECORD_TYPE_ID,
pjp.CURRENCY_CODE,
pjp.RBS_ELEMENT_ID,
pjp.RBS_VERSION_ID,
decode(nvl(prg.SUB_EMT_ID, -1), nvl(prg.SUB_ROLLUP_ID, -1),
pjp.PLAN_VERSION_ID,
decode(pjp.PLAN_VERSION_ID,
-1, pjp.PLAN_VERSION_ID,
-2, pjp.PLAN_VERSION_ID,
-3, pjp.PLAN_VERSION_ID,
-4, pjp.PLAN_VERSION_ID,
wbs_hdr.PLAN_VERSION_ID)) PLAN_VERSION_ID,
decode(nvl(prg.SUB_EMT_ID, -1), nvl(prg.SUB_ROLLUP_ID, -1),
pjp.PLAN_TYPE_ID,
decode(pjp.PLAN_VERSION_ID,
-1, pjp.PLAN_TYPE_ID,
-2, pjp.PLAN_TYPE_ID,
-3, pjp.PLAN_TYPE_ID,
-4, pjp.PLAN_TYPE_ID,
wbs_hdr.PLAN_TYPE_ID)) PLAN_TYPE_ID,
pjp.PLAN_TYPE_CODE,
sum(pjp.RAW_COST) RAW_COST,
sum(pjp.BRDN_COST) BRDN_COST,
sum(pjp.REVENUE) REVENUE,
sum(pjp.BILL_RAW_COST) BILL_RAW_COST,
sum(pjp.BILL_BRDN_COST) BILL_BRDN_COST,
sum(pjp.BILL_LABOR_RAW_COST) BILL_LABOR_RAW_COST,
sum(pjp.BILL_LABOR_BRDN_COST) BILL_LABOR_BRDN_COST,
sum(pjp.BILL_LABOR_HRS) BILL_LABOR_HRS,
sum(pjp.EQUIPMENT_RAW_COST) EQUIPMENT_RAW_COST,
sum(pjp.EQUIPMENT_BRDN_COST) EQUIPMENT_BRDN_COST,
sum(pjp.CAPITALIZABLE_RAW_COST) CAPITALIZABLE_RAW_COST,
sum(pjp.CAPITALIZABLE_BRDN_COST) CAPITALIZABLE_BRDN_COST,
sum(pjp.LABOR_RAW_COST) LABOR_RAW_COST,
sum(pjp.LABOR_BRDN_COST) LABOR_BRDN_COST,
sum(pjp.LABOR_HRS) LABOR_HRS,
sum(pjp.LABOR_REVENUE) LABOR_REVENUE,
sum(pjp.EQUIPMENT_HOURS) EQUIPMENT_HOURS,
sum(pjp.BILLABLE_EQUIPMENT_HOURS) BILLABLE_EQUIPMENT_HOURS,
sum(pjp.SUP_INV_COMMITTED_COST) SUP_INV_COMMITTED_COST,
sum(pjp.PO_COMMITTED_COST) PO_COMMITTED_COST,
sum(pjp.PR_COMMITTED_COST) PR_COMMITTED_COST,
sum(pjp.OTH_COMMITTED_COST) OTH_COMMITTED_COST,
sum(pjp.ACT_LABOR_HRS) ACT_LABOR_HRS,
sum(pjp.ACT_EQUIP_HRS) ACT_EQUIP_HRS,
sum(pjp.ACT_LABOR_BRDN_COST) ACT_LABOR_BRDN_COST,
sum(pjp.ACT_EQUIP_BRDN_COST) ACT_EQUIP_BRDN_COST,
sum(pjp.ACT_BRDN_COST) ACT_BRDN_COST,
sum(pjp.ACT_RAW_COST) ACT_RAW_COST,
sum(pjp.ACT_REVENUE) ACT_REVENUE,
sum(pjp.ACT_LABOR_RAW_COST) ACT_LABOR_RAW_COST,
sum(pjp.ACT_EQUIP_RAW_COST) ACT_EQUIP_RAW_COST,
sum(pjp.ETC_LABOR_HRS) ETC_LABOR_HRS,
sum(pjp.ETC_EQUIP_HRS) ETC_EQUIP_HRS,
sum(pjp.ETC_LABOR_BRDN_COST) ETC_LABOR_BRDN_COST,
sum(pjp.ETC_EQUIP_BRDN_COST) ETC_EQUIP_BRDN_COST,
sum(pjp.ETC_BRDN_COST) ETC_BRDN_COST,
sum(pjp.ETC_RAW_COST) ETC_RAW_COST,
sum(pjp.ETC_LABOR_RAW_COST) ETC_LABOR_RAW_COST,
sum(pjp.ETC_EQUIP_RAW_COST) ETC_EQUIP_RAW_COST,
sum(pjp.CUSTOM1) CUSTOM1,
sum(pjp.CUSTOM2) CUSTOM2,
sum(pjp.CUSTOM3) CUSTOM3,
sum(pjp.CUSTOM4) CUSTOM4,
sum(pjp.CUSTOM5) CUSTOM5,
sum(pjp.CUSTOM6) CUSTOM6,
sum(pjp.CUSTOM7) CUSTOM7,
sum(pjp.CUSTOM8) CUSTOM8,
sum(pjp.CUSTOM9) CUSTOM9,
sum(pjp.CUSTOM10) CUSTOM10,
sum(pjp.CUSTOM11) CUSTOM11,
sum(pjp.CUSTOM12) CUSTOM12,
sum(pjp.CUSTOM13) CUSTOM13,
sum(pjp.CUSTOM14) CUSTOM14,
sum(pjp.CUSTOM15) CUSTOM15
from
(
select /*+ ordered index(wbs PA_XBS_DENORM_N2) */
-- get incremental task level amounts from source and
-- program rollup amounts from interim
to_char(null) LINE_TYPE,
wbs_hdr.WBS_VERSION_ID,
decode(wbs_hdr.WP_FLAG, 'Y', 'LW', 'LF') RELATIONSHIP_TYPE,
decode(wbs_hdr.WP_FLAG
|| '_' || to_char(sign(pjp1.PLAN_VERSION_ID))
|| '_' || nvl(fin_plan.INVERT_ID, 'PRJ'),
'N_1_PRJ', 'N',
'N_-1_PRG', 'N',
decode(top_slice.INVERT_ID,
'PRJ', 'Y',
decode(wbs.SUB_LEVEL,
1, 'Y', 'N'))) PUSHUP_FLAG,
decode(pjp1.RBS_AGGR_LEVEL,
'L', 'N',
decode(wbs_hdr.WP_FLAG
|| '_' || to_char(sign(pjp1.PLAN_VERSION_ID))
|| '_' || fin_plan.INVERT_ID,
'N_1_PRG', decode(top_slice.INVERT_ID,
'PRJ', 'Y',
decode(wbs.SUB_LEVEL,
1, 'Y', 'N')),
'N_-1_PRG', 'N',
decode(wbs_hdr.WP_FLAG
|| '_' || fin_plan.INVERT_ID
|| '_' || fin_plan.CB
|| '_' || fin_plan.CO
|| '_'
|| to_char(fin_plan.PLAN_VERSION_ID),
'N_PRJ_Y_Y_-4', 'N',
'Y'))
) INSERT_FLAG,
pjp1.PROJECT_ID,
pjp1.PROJECT_ORG_ID,
pjp1.PROJECT_ORGANIZATION_ID,
decode(top_slice.INVERT_ID,
'PRJ', prg.SUP_EMT_ID,
decode(wbs.SUB_LEVEL,
1, prg.SUP_EMT_ID,
wbs.SUP_EMT_ID)) PROJECT_ELEMENT_ID,
pjp1.TIME_ID,
pjp1.PERIOD_TYPE_ID,
pjp1.CALENDAR_TYPE,
pjp1.RBS_AGGR_LEVEL,
'Y' WBS_ROLLUP_FLAG,
pjp1.PRG_ROLLUP_FLAG,
pjp1.CURR_RECORD_TYPE_ID,
pjp1.CURRENCY_CODE,
pjp1.RBS_ELEMENT_ID,
pjp1.RBS_VERSION_ID,
decode(wbs_hdr.WP_FLAG || '_' || fin_plan.INVERT_ID,
'N_PRG', fin_plan.PLAN_VERSION_ID,
pjp1.PLAN_VERSION_ID) PLAN_VERSION_ID,
pjp1.PLAN_TYPE_ID,
pjp1.PLAN_TYPE_CODE,
pjp1.RAW_COST,
pjp1.BRDN_COST,
pjp1.REVENUE,
pjp1.BILL_RAW_COST,
pjp1.BILL_BRDN_COST,
pjp1.BILL_LABOR_RAW_COST,
pjp1.BILL_LABOR_BRDN_COST,
pjp1.BILL_LABOR_HRS,
pjp1.EQUIPMENT_RAW_COST,
pjp1.EQUIPMENT_BRDN_COST,
pjp1.CAPITALIZABLE_RAW_COST,
pjp1.CAPITALIZABLE_BRDN_COST,
pjp1.LABOR_RAW_COST,
pjp1.LABOR_BRDN_COST,
pjp1.LABOR_HRS,
pjp1.LABOR_REVENUE,
pjp1.EQUIPMENT_HOURS,
pjp1.BILLABLE_EQUIPMENT_HOURS,
pjp1.SUP_INV_COMMITTED_COST,
pjp1.PO_COMMITTED_COST,
pjp1.PR_COMMITTED_COST,
pjp1.OTH_COMMITTED_COST,
pjp1.ACT_LABOR_HRS,
pjp1.ACT_EQUIP_HRS,
pjp1.ACT_LABOR_BRDN_COST,
pjp1.ACT_EQUIP_BRDN_COST,
pjp1.ACT_BRDN_COST,
pjp1.ACT_RAW_COST,
pjp1.ACT_REVENUE,
pjp1.ACT_LABOR_RAW_COST,
pjp1.ACT_EQUIP_RAW_COST,
pjp1.ETC_LABOR_HRS,
pjp1.ETC_EQUIP_HRS,
pjp1.ETC_LABOR_BRDN_COST,
pjp1.ETC_EQUIP_BRDN_COST,
pjp1.ETC_BRDN_COST,
pjp1.ETC_RAW_COST,
pjp1.ETC_LABOR_RAW_COST,
pjp1.ETC_EQUIP_RAW_COST,
pjp1.CUSTOM1,
pjp1.CUSTOM2,
pjp1.CUSTOM3,
pjp1.CUSTOM4,
pjp1.CUSTOM5,
pjp1.CUSTOM6,
pjp1.CUSTOM7,
pjp1.CUSTOM8,
pjp1.CUSTOM9,
pjp1.CUSTOM10,
pjp1.CUSTOM11,
pjp1.CUSTOM12,
pjp1.CUSTOM13,
pjp1.CUSTOM14,
pjp1.CUSTOM15
from
PJI_FP_AGGR_PJP1 pjp1,
PJI_PJP_WBS_HEADER wbs_hdr,
PA_XBS_DENORM wbs,
PJI_XBS_DENORM prg,
(
select 'Y' CB, 'N' CO, -3 PLAN_VERSION_ID, 'PRG' INVERT_ID
from DUAL union all
select 'Y' CB, 'N' CO, -3 PLAN_VERSION_ID, 'PRJ' INVERT_ID
from DUAL union all
select 'N' CB, 'Y' CO, -4 PLAN_VERSION_ID, 'PRG' INVERT_ID
from DUAL union all
select 'N' CB, 'Y' CO, -4 PLAN_VERSION_ID, 'PRJ' INVERT_ID
from DUAL union all
select 'Y' CB, 'Y' CO, -3 PLAN_VERSION_ID, 'PRG' INVERT_ID
from DUAL union all
select 'Y' CB, 'Y' CO, -3 PLAN_VERSION_ID, 'PRJ' INVERT_ID
from DUAL union all
select 'Y' CB, 'Y' CO, -4 PLAN_VERSION_ID, 'PRG' INVERT_ID
from DUAL union all
select 'Y' CB, 'Y' CO, -4 PLAN_VERSION_ID, 'PRJ' INVERT_ID
from DUAL
) fin_plan,
(
select 1 WBS_SUP_LEVEL,
1 WBS_SUB_LEVEL,
'PRJ' INVERT_ID
from DUAL
union all
select 1 WBS_SUP_LEVEL,
1 WBS_SUB_LEVEL,
'WBS' INVERT_ID
from DUAL
) top_slice
where
prg.STRUCT_TYPE = 'PRG' and
prg.SUP_LEVEL = l_level and
prg.SUB_LEVEL = l_level and
wbs.STRUCT_TYPE = 'WBS' and
((wbs.SUP_LEVEL = 1 and
wbs.SUB_LEVEL = 1) or
(wbs.SUP_LEVEL <> wbs.SUB_LEVEL)) and
wbs.STRUCT_VERSION_ID = prg.SUP_ID and
wbs.SUP_PROJECT_ID = prg.SUP_PROJECT_ID and
pjp1.WORKER_ID = p_worker_id and
pjp1.PRG_LEVEL in (0, l_level) and
pjp1.RBS_AGGR_LEVEL in ('T', 'L') and
pjp1.WBS_ROLLUP_FLAG = 'N' and
pjp1.PRG_ROLLUP_FLAG in ('Y', 'N') and
pjp1.PROJECT_ID = wbs_hdr.PROJECT_ID and
pjp1.PLAN_VERSION_ID = wbs_hdr.PLAN_VERSION_ID and
pjp1.PLAN_TYPE_CODE = wbs_hdr.PLAN_TYPE_CODE and
decode(pjp1.PLAN_VERSION_ID,
-3, pjp1.PLAN_TYPE_ID,
-4, pjp1.PLAN_TYPE_ID,
-1) = decode(pjp1.PLAN_VERSION_ID,
-3, wbs_hdr.PLAN_TYPE_ID,
-4, wbs_hdr.PLAN_TYPE_ID,
-1) and
wbs.STRUCT_VERSION_ID = wbs_hdr.WBS_VERSION_ID and
pjp1.PROJECT_ELEMENT_ID = wbs.SUB_EMT_ID and
wbs_hdr.CB_FLAG = fin_plan.CB (+) and
wbs_hdr.CO_FLAG = fin_plan.CO (+) and
wbs.SUP_LEVEL = top_slice.WBS_SUP_LEVEL (+) and
wbs.SUB_LEVEL <> top_slice.WBS_SUB_LEVEL (+)
union all
select /*+ ordered */
-- get incremental project level amounts from source
to_char(null) LINE_TYPE,
wbs_hdr.WBS_VERSION_ID,
decode(wbs_hdr.WP_FLAG, 'Y', 'LW', 'LF') RELATIONSHIP_TYPE,
'Y' PUSHUP_FLAG,
decode(pjp1.RBS_AGGR_LEVEL,
'L', 'N',
decode(fin_plan.PLAN_VERSION_ID,
null, 'N', 'Y')) INSERT_FLAG,
pjp1.PROJECT_ID,
pjp1.PROJECT_ORG_ID,
pjp1.PROJECT_ORGANIZATION_ID,
pjp1.PROJECT_ELEMENT_ID,
pjp1.TIME_ID,
pjp1.PERIOD_TYPE_ID,
pjp1.CALENDAR_TYPE,
pjp1.RBS_AGGR_LEVEL,
'Y' WBS_ROLLUP_FLAG,
pjp1.PRG_ROLLUP_FLAG,
pjp1.CURR_RECORD_TYPE_ID,
pjp1.CURRENCY_CODE,
pjp1.RBS_ELEMENT_ID,
pjp1.RBS_VERSION_ID,
decode(wbs_hdr.WP_FLAG,
'N', decode(pjp1.PLAN_VERSION_ID,
-1, pjp1.PLAN_VERSION_ID,
-2, pjp1.PLAN_VERSION_ID,
-3, pjp1.PLAN_VERSION_ID, -- won't exist
-4, pjp1.PLAN_VERSION_ID, -- won't exist
fin_plan.PLAN_VERSION_ID),
pjp1.PLAN_VERSION_ID) PLAN_VERSION_ID,
pjp1.PLAN_TYPE_ID,
pjp1.PLAN_TYPE_CODE,
pjp1.RAW_COST,
pjp1.BRDN_COST,
pjp1.REVENUE,
pjp1.BILL_RAW_COST,
pjp1.BILL_BRDN_COST,
pjp1.BILL_LABOR_RAW_COST,
pjp1.BILL_LABOR_BRDN_COST,
pjp1.BILL_LABOR_HRS,
pjp1.EQUIPMENT_RAW_COST,
pjp1.EQUIPMENT_BRDN_COST,
pjp1.CAPITALIZABLE_RAW_COST,
pjp1.CAPITALIZABLE_BRDN_COST,
pjp1.LABOR_RAW_COST,
pjp1.LABOR_BRDN_COST,
pjp1.LABOR_HRS,
pjp1.LABOR_REVENUE,
pjp1.EQUIPMENT_HOURS,
pjp1.BILLABLE_EQUIPMENT_HOURS,
pjp1.SUP_INV_COMMITTED_COST,
pjp1.PO_COMMITTED_COST,
pjp1.PR_COMMITTED_COST,
pjp1.OTH_COMMITTED_COST,
pjp1.ACT_LABOR_HRS,
pjp1.ACT_EQUIP_HRS,
pjp1.ACT_LABOR_BRDN_COST,
pjp1.ACT_EQUIP_BRDN_COST,
pjp1.ACT_BRDN_COST,
pjp1.ACT_RAW_COST,
pjp1.ACT_REVENUE,
pjp1.ACT_LABOR_RAW_COST,
pjp1.ACT_EQUIP_RAW_COST,
pjp1.ETC_LABOR_HRS,
pjp1.ETC_EQUIP_HRS,
pjp1.ETC_LABOR_BRDN_COST,
pjp1.ETC_EQUIP_BRDN_COST,
pjp1.ETC_BRDN_COST,
pjp1.ETC_RAW_COST,
pjp1.ETC_LABOR_RAW_COST,
pjp1.ETC_EQUIP_RAW_COST,
pjp1.CUSTOM1,
pjp1.CUSTOM2,
pjp1.CUSTOM3,
pjp1.CUSTOM4,
pjp1.CUSTOM5,
pjp1.CUSTOM6,
pjp1.CUSTOM7,
pjp1.CUSTOM8,
pjp1.CUSTOM9,
pjp1.CUSTOM10,
pjp1.CUSTOM11,
pjp1.CUSTOM12,
pjp1.CUSTOM13,
pjp1.CUSTOM14,
pjp1.CUSTOM15
from
PJI_FP_AGGR_PJP1 pjp1,
PJI_PJP_WBS_HEADER wbs_hdr,
PJI_XBS_DENORM prg,
(
select 'Y' CB_FLAG,
'N' CO_FLAG,
-3 PLAN_VERSION_ID
from DUAL union all
select 'N' CB_FLAG,
'Y' CO_FLAG,
-4 PLAN_VERSION_ID
from DUAL union all
select 'Y' CB_FLAG,
'Y' CO_FLAG,
-3 PLAN_VERSION_ID
from DUAL union all
select 'Y' CB_FLAG,
'Y' CO_FLAG,
-4 PLAN_VERSION_ID
from DUAL
) fin_plan
where
prg.STRUCT_TYPE = 'PRG' and
prg.SUP_LEVEL = l_level and
prg.SUB_LEVEL = l_level and
pjp1.WORKER_ID = p_worker_id and
pjp1.PROJECT_ID = prg.SUP_PROJECT_ID and
pjp1.PROJECT_ELEMENT_ID = prg.SUP_EMT_ID and
pjp1.PRG_LEVEL = 0 and
pjp1.RBS_AGGR_LEVEL in ('T', 'L') and
pjp1.WBS_ROLLUP_FLAG = 'N' and
pjp1.PRG_ROLLUP_FLAG = 'N' and
wbs_hdr.PROJECT_ID = pjp1.PROJECT_ID and
wbs_hdr.PLAN_VERSION_ID = pjp1.PLAN_VERSION_ID and
wbs_hdr.PLAN_TYPE_CODE = pjp1.PLAN_TYPE_CODE and
decode(wbs_hdr.WP_FLAG,
'N', decode(pjp1.PLAN_VERSION_ID,
-1, 'Y',
-2, 'Y',
-3, 'Y', -- won't exist
-4, 'Y', -- won't exist
decode(wbs_hdr.CB_FLAG || '_' ||
wbs_hdr.CO_FLAG,
'Y_Y', 'Y',
'N_Y', 'Y',
'Y_N', 'Y',
'N')),
'Y') = 'Y' and
wbs_hdr.WBS_VERSION_ID = prg.SUP_ID and
wbs_hdr.CB_FLAG = fin_plan.CB_FLAG (+) and
wbs_hdr.CO_FLAG = fin_plan.CO_FLAG (+)
) pjp,
(
select /*+ index(prg PJI_XBS_DENORM_N3)
index(map PA_PROJECTS_U1) */
prg.SUP_PROJECT_ID,
map.ORG_ID SUP_PROJECT_ORG_ID,
map.CARRYING_OUT_ORGANIZATION_ID SUP_PROJECT_ORGANIZATION_ID,
prg.SUP_ID,
prg.SUP_EMT_ID,
prg.SUP_LEVEL,
prg.SUB_ID,
prg.SUB_EMT_ID,
prg.SUB_ROLLUP_ID,
invert.INVERT_VALUE RELATIONSHIP_TYPE,
decode(prg.RELATIONSHIP_TYPE,
'LW', 'Y',
'LF', 'N') WP_FLAG,
'Y' PUSHUP_FLAG
from
PJI_XBS_DENORM prg,
PA_PROJECTS_ALL map,
(
select 'LF' INVERT_ID, 'LF' INVERT_VALUE from dual union all
select 'LW' INVERT_ID, 'LW' INVERT_VALUE from dual union all
select 'A' INVERT_ID, 'LF' INVERT_VALUE from dual union all
select 'A' INVERT_ID, 'LW' INVERT_VALUE from dual
) invert
where
prg.STRUCT_TYPE = 'PRG' and
prg.SUB_ROLLUP_ID is not null and
prg.SUB_LEVEL = l_level and
-- map.WORKER_ID = p_worker_id and
map.PROJECT_ID = prg.SUP_PROJECT_ID and
decode(prg.SUB_LEVEL,
prg.SUP_LEVEL, 'A',
prg.RELATIONSHIP_TYPE) = invert.INVERT_ID
) prg,
PJI_PJP_WBS_HEADER wbs_hdr,
PA_PROJ_ELEM_VER_STRUCTURE sub_ver,
PA_PROJ_ELEM_VER_STRUCTURE sup_ver,
PA_PROJ_WORKPLAN_ATTR sup_wpa
where
pjp.PROJECT_ID = sub_ver.PROJECT_ID (+) and
pjp.WBS_VERSION_ID = sub_ver.ELEMENT_VERSION_ID (+) and
'STRUCTURE_PUBLISHED' = sub_ver.STATUS_CODE (+) and
pjp.WBS_VERSION_ID = prg.SUB_ID (+) and
pjp.RELATIONSHIP_TYPE = prg.RELATIONSHIP_TYPE (+) and
pjp.PUSHUP_FLAG = prg.PUSHUP_FLAG (+) and
prg.SUP_PROJECT_ID = wbs_hdr.PROJECT_ID (+) and
prg.SUP_ID = wbs_hdr.WBS_VERSION_ID (+) and
prg.WP_FLAG = wbs_hdr.WP_FLAG (+) and
'Y' = wbs_hdr.WP_FLAG (+) and
wbs_hdr.PROJECT_ID = sup_ver.PROJECT_ID (+) and
wbs_hdr.WBS_VERSION_ID = sup_ver.ELEMENT_VERSION_ID (+) and
'STRUCTURE_PUBLISHED' = sup_ver.STATUS_CODE (+) and
'Y' = sup_ver.LATEST_EFF_PUBLISHED_FLAG (+) and
prg.SUP_EMT_ID = sup_wpa.PROJ_ELEMENT_ID (+)
group by
decode(nvl(prg.SUB_EMT_ID, -1), nvl(prg.SUB_ROLLUP_ID, -1),
pjp.INSERT_FLAG, 'Y'),
pjp.RELATIONSHIP_TYPE,
decode(nvl(prg.SUB_EMT_ID, -1), nvl(prg.SUB_ROLLUP_ID, -1),
null, sub_ver.STATUS_CODE),
decode(nvl(prg.SUB_EMT_ID, -1), nvl(prg.SUB_ROLLUP_ID, -1),
null, sup_ver.STATUS_CODE),
decode(nvl(prg.SUB_EMT_ID, -1), nvl(prg.SUB_ROLLUP_ID, -1),
null, sup_wpa.WP_ENABLE_VERSION_FLAG),
decode(nvl(prg.SUB_EMT_ID, -1), nvl(prg.SUB_ROLLUP_ID, -1),
null, decode(pjp.PLAN_VERSION_ID,
-1, prg.SUP_ID,
-3, prg.SUP_ID,
-4, prg.SUP_ID,
null)),
decode(nvl(prg.SUB_EMT_ID, -1), nvl(prg.SUB_ROLLUP_ID, -1),
null, decode(pjp.PLAN_VERSION_ID,
-1, prg.SUP_EMT_ID,
-3, prg.SUP_EMT_ID,
-4, prg.SUP_EMT_ID,
null)),
decode(nvl(prg.SUB_EMT_ID, -1), nvl(prg.SUB_ROLLUP_ID, -1),
null, decode(pjp.PLAN_VERSION_ID,
-1, prg.WP_FLAG,
-3, prg.WP_FLAG,
-4, prg.WP_FLAG,
null)),
decode(nvl(prg.SUB_EMT_ID, -1), nvl(prg.SUB_ROLLUP_ID, -1),
l_level, prg.SUP_LEVEL),
pjp.LINE_TYPE,
decode(nvl(prg.SUB_EMT_ID, -1), nvl(prg.SUB_ROLLUP_ID, -1),
pjp.PROJECT_ID, prg.SUP_PROJECT_ID),
decode(nvl(prg.SUB_EMT_ID, -1), nvl(prg.SUB_ROLLUP_ID, -1),
pjp.PROJECT_ORG_ID,
prg.SUP_PROJECT_ORG_ID),
decode(nvl(prg.SUB_EMT_ID, -1), nvl(prg.SUB_ROLLUP_ID, -1),
pjp.PROJECT_ORGANIZATION_ID,
prg.SUP_PROJECT_ORGANIZATION_ID),
decode(nvl(prg.SUB_EMT_ID, -1), nvl(prg.SUB_ROLLUP_ID, -1),
pjp.PROJECT_ELEMENT_ID,
prg.SUB_ROLLUP_ID),
pjp.TIME_ID,
pjp.PERIOD_TYPE_ID,
pjp.CALENDAR_TYPE,
pjp.RBS_AGGR_LEVEL,
decode(nvl(prg.SUB_EMT_ID, -1), nvl(prg.SUB_ROLLUP_ID, -1),
pjp.WBS_ROLLUP_FLAG, 'N'),
decode(nvl(prg.SUB_EMT_ID, -1), nvl(prg.SUB_ROLLUP_ID, -1),
pjp.PRG_ROLLUP_FLAG, 'Y'),
pjp.CURR_RECORD_TYPE_ID,
pjp.CURRENCY_CODE,
pjp.RBS_ELEMENT_ID,
pjp.RBS_VERSION_ID,
decode(nvl(prg.SUB_EMT_ID, -1), nvl(prg.SUB_ROLLUP_ID, -1),
pjp.PLAN_VERSION_ID,
decode(pjp.PLAN_VERSION_ID,
-1, pjp.PLAN_VERSION_ID,
-2, pjp.PLAN_VERSION_ID,
-3, pjp.PLAN_VERSION_ID,
-4, pjp.PLAN_VERSION_ID,
wbs_hdr.PLAN_VERSION_ID)),
decode(nvl(prg.SUB_EMT_ID, -1), nvl(prg.SUB_ROLLUP_ID, -1),
pjp.PLAN_TYPE_ID,
decode(pjp.PLAN_VERSION_ID,
-1, pjp.PLAN_TYPE_ID,
-2, pjp.PLAN_TYPE_ID,
-3, pjp.PLAN_TYPE_ID,
-4, pjp.PLAN_TYPE_ID,
wbs_hdr.PLAN_TYPE_ID)),
pjp.PLAN_TYPE_CODE
) pjp1_i,
PA_PROJ_ELEM_VER_STRUCTURE sup_fin_ver,
PA_PROJ_WORKPLAN_ATTR sup_wpa
where
pjp1_i.INSERT_FLAG = 'Y' and
pjp1_i.PROJECT_ID = sup_fin_ver.PROJECT_ID (+) and
pjp1_i.SUP_ID = sup_fin_ver.ELEMENT_VERSION_ID (+) and
'STRUCTURE_WORKING' = sup_fin_ver.STATUS_CODE (+) and
pjp1_i.SUP_EMT_ID = sup_wpa.PROJ_ELEMENT_ID (+) and
'N' = sup_wpa.WP_ENABLE_VERSION_FLAG (+) and
(pjp1_i.SUP_ID is null or
(pjp1_i.SUP_ID is not null and
(sup_fin_ver.PROJECT_ID is not null or
sup_wpa.PROJ_ELEMENT_ID is not null)));
UPDATE /*+ index(whdr,PJI_PJP_WBS_HEADER_N1) */
PJI_PJP_WBS_HEADER whdr
SET ( MIN_TXN_DATE
, MAX_TXN_DATE
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, LAST_UPDATE_LOGIN
) = (
SELECT MIN(LEAST(cal.start_date, NVL(whdr.min_txn_date, cal.start_date))) start_date
, MAX(GREATEST(cal.end_date, NVL(whdr.max_txn_date, cal.end_date))) end_date
, l_last_update_date
, l_last_updated_by
, l_last_update_login
FROM PJI_FP_AGGR_PJP1 pjp1
, pji_time_cal_period_v cal
WHERE
pjp1.worker_id = p_worker_id
AND pjp1.plan_version_id = whdr.plan_version_id
AND pjp1.project_id = whdr.project_id
AND pjp1.plan_type_id = whdr.plan_type_id
AND pjp1.time_id = cal.cal_period_id
AND pjp1.calendar_type IN ('P', 'G') -- Non time ph and ent cals don't need to be considered.
)
WHERE exists (select 1 from pji_fp_aggr_pjp1 ver where worker_id = p_worker_id
and ver.project_id = whdr.project_id
and ver.plan_version_id = whdr.plan_version_id
and ver.plan_type_id = whdr.plan_type_id);
INSERT INTO pji_fp_xbs_accum_f fact
(
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
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_LOGIN
, RAW_COST
, BRDN_COST
, REVENUE
, BILL_RAW_COST
, BILL_BRDN_COST
, BILL_LABOR_RAW_COST
, BILL_LABOR_BRDN_COST
, BILL_LABOR_HRS
, EQUIPMENT_RAW_COST
, EQUIPMENT_BRDN_COST
, CAPITALIZABLE_RAW_COST
, CAPITALIZABLE_BRDN_COST
, LABOR_RAW_COST
, LABOR_BRDN_COST
, LABOR_HRS
, LABOR_REVENUE
, EQUIPMENT_HOURS
, BILLABLE_EQUIPMENT_HOURS
, SUP_INV_COMMITTED_COST
, PO_COMMITTED_COST
, PR_COMMITTED_COST
, OTH_COMMITTED_COST
, ACT_LABOR_HRS
, ACT_EQUIP_HRS
, ACT_LABOR_BRDN_COST
, ACT_EQUIP_BRDN_COST
, ACT_BRDN_COST
, ACT_RAW_COST
, ACT_REVENUE
, ACT_LABOR_RAW_COST
, ACT_EQUIP_RAW_COST
, ETC_LABOR_HRS
, ETC_EQUIP_HRS
, ETC_LABOR_BRDN_COST
, ETC_EQUIP_BRDN_COST
, ETC_BRDN_COST
, ETC_RAW_COST
, ETC_LABOR_RAW_COST
, ETC_EQUIP_RAW_COST
, CUSTOM1
, CUSTOM2
, CUSTOM3
, CUSTOM4
, CUSTOM5
, CUSTOM6
, CUSTOM7
, CUSTOM8
, CUSTOM9
, CUSTOM10
, CUSTOM11
, CUSTOM12
, CUSTOM13
, CUSTOM14
, CUSTOM15
, PLAN_TYPE_CODE
)
(
SELECT
tmp.PROJECT_ID
, tmp.PROJECT_ORG_ID
, tmp.PROJECT_ORGANIZATION_ID
, tmp.PROJECT_ELEMENT_ID
, tmp.TIME_ID
, tmp.PERIOD_TYPE_ID
, tmp.CALENDAR_TYPE
, tmp.RBS_AGGR_LEVEL
, tmp.WBS_ROLLUP_FLAG
, tmp.PRG_ROLLUP_FLAG
, tmp.CURR_RECORD_TYPE_ID
, tmp.CURRENCY_CODE
, tmp.RBS_ELEMENT_ID
, tmp.RBS_VERSION_ID
, ver3.PLAN_VERSION_ID
, tmp.PLAN_TYPE_ID
, l_last_update_date
, l_last_updated_by
, l_creation_date
, l_created_by
, l_last_update_login
, RAW_COST
, BRDN_COST
, REVENUE
, BILL_RAW_COST
, BILL_BRDN_COST
, BILL_LABOR_RAW_COST
, BILL_LABOR_BRDN_COST
, BILL_LABOR_HRS
, EQUIPMENT_RAW_COST
, EQUIPMENT_BRDN_COST
, CAPITALIZABLE_RAW_COST
, CAPITALIZABLE_BRDN_COST
, LABOR_RAW_COST
, LABOR_BRDN_COST
, LABOR_HRS
, LABOR_REVENUE
, EQUIPMENT_HOURS
, BILLABLE_EQUIPMENT_HOURS
, SUP_INV_COMMITTED_COST
, PO_COMMITTED_COST
, PR_COMMITTED_COST
, OTH_COMMITTED_COST
, ACT_LABOR_HRS
, ACT_EQUIP_HRS
, ACT_LABOR_BRDN_COST
, ACT_EQUIP_BRDN_COST
, ACT_BRDN_COST
, ACT_RAW_COST
, ACT_REVENUE
, ACT_LABOR_RAW_COST
, ACT_EQUIP_RAW_COST
, DECODE ( ver3.wp_flag
, 'Y'
, DECODE(TO_CHAR(tmp.ETC_LABOR_HRS) -- For Workplan
, NULL
, NVL(tmp.labor_hrs, 0)
, NVL(tmp.ETC_LABOR_HRS, 0)
)
, NVL(tmp.ETC_LABOR_HRS, 0)
) ETC_LABOR_HRS
, DECODE ( ver3.wp_flag
, 'Y'
, DECODE(TO_CHAR(tmp.ETC_EQUIP_HRS)
, NULL
, NVL(tmp.EQUIPMENT_hours, 0)
, NVL(tmp.ETC_EQUIP_HRS, 0)
)
, NVL(tmp.ETC_EQUIP_HRS, 0)
) ETC_EQUIP_HRS
, DECODE ( ver3.wp_flag
, 'Y'
, DECODE(TO_CHAR(tmp.ETC_LABOR_BRDN_COST)
, NULL
, NVL(tmp.labor_BRDN_COST, 0)
, NVL(tmp.ETC_LABOR_BRDN_COST, 0)
)
, NVL(tmp.ETC_LABOR_BRDN_COST, 0)
) ETC_LABOR_BRDN_COST
, DECODE ( ver3.wp_flag
, 'Y'
, DECODE(TO_CHAR(tmp.ETC_EQUIP_BRDN_COST)
, NULL
, NVL(tmp.EQUIPment_BRDN_COST, 0)
, NVL(tmp.ETC_equip_BRDN_COST, 0)
)
, NVL(tmp.ETC_EQUIP_BRDN_COST, 0)
) ETC_equip_BRDN_COST
, DECODE ( ver3.wp_flag
, 'Y'
, DECODE(TO_CHAR(tmp.ETC_BRDN_COST)
, NULL
, NVL(tmp.BRDN_COST, 0)
, NVL(tmp.ETC_BRDN_COST, 0)
)
, NVL(tmp.ETC_BRDN_COST, 0)
) ETC_BRDN_COST
, DECODE ( ver3.wp_flag
, 'Y'
, DECODE(TO_CHAR(tmp.ETC_raw_COST)
, NULL
, NVL(tmp.raw_COST, 0)
, NVL(tmp.ETC_raw_COST, 0)
)
, NVL(tmp.ETC_raw_COST, 0)
) ETC_raw_COST
, DECODE ( ver3.wp_flag
, 'Y'
, DECODE(TO_CHAR(tmp.ETC_labor_raw_COST)
, NULL
, NVL(tmp.labor_raw_COST, 0)
, NVL(tmp.ETC_labor_raw_COST, 0)
)
, NVL(tmp.ETC_labor_raw_COST, 0)
) ETC_labor_raw_COST
, DECODE ( ver3.wp_flag
, 'Y'
, DECODE(TO_CHAR(tmp.ETC_equip_raw_COST)
, NULL
, NVL(tmp.equipment_raw_COST, 0)
, NVL(tmp.ETC_equip_raw_COST, 0)
)
, NVL(tmp.ETC_equip_raw_COST, 0)
) ETC_equip_raw_COST
, CUSTOM1
, CUSTOM2
, CUSTOM3
, CUSTOM4
, CUSTOM5
, CUSTOM6
, CUSTOM7
, CUSTOM8
, CUSTOM9
, CUSTOM10
, CUSTOM11
, CUSTOM12
, CUSTOM13
, CUSTOM14
, CUSTOM15
, tmp.plan_type_code
FROM pji_fp_aggr_pjp1 tmp
, pji_pjp_wbs_header ver3
WHERE 1 = 1
AND ver3.plan_version_id = tmp.plan_version_id
AND ver3.plan_type_code = tmp.plan_type_code /* 4471527 */
AND tmp.project_id = ver3.project_id -- use index.
AND tmp.plan_type_id = NVL(ver3.plan_type_id, -1)
AND tmp.plan_version_id in (-3, -4)
AND tmp.worker_id = p_worker_id
);
DELETE pa_pji_proj_events_log log
where log.event_type = 'PLANTYPE_UPG'
and exists (select 1 from pji_fm_extr_plnver4 ver
where ver.worker_id = p_worker_id and
to_char(ver.project_id) = log.event_object);
DELETE FROM pji_fm_extr_plnver4 where worker_id = p_worker_id;
DELETE FROM pji_fp_aggr_pjp1 where worker_id = p_worker_id;
select 1 into l_count from dual
where exists ( select event_type
from PA_PJI_PROJ_EVENTS_LOG elog
where elog.event_type = 'PLANTYPE_UPG');
select
wbs_hdr.ROWID HDR_ROWID
from
PJI_PJP_PROJ_BATCH_MAP map,
PJI_PJP_WBS_HEADER wbs_hdr
where
map.WORKER_ID = p_worker_id and
wbs_hdr.PROJECT_ID = map.PROJECT_ID and
wbs_hdr.PLAN_VERSION_ID = -1
for update;
select min(segment1) ,max(segment1)
into l_from_project_num, l_to_project_num
from pa_projects_all
where segment1 between nvl(p_from_project,segment1) and nvl(p_to_project,segment1);
/* Get the Project Ids ,this is required to keep the impact minimum , these values will be updated in pji_system_parameters Table */
IF l_from_project_num is not null and p_from_project is not null THEN
select project_id
into p_from_project_id
from pa_projects_all
where segment1= l_from_project_num;
select project_id
into p_to_project_id
from pa_projects_all
where segment1= l_to_project_num;
select
nvl(CONFIG_PROJ_PERF_FLAG, 'N')
into
l_settings_proj_perf_flag
from
PJI_SYSTEM_SETTINGS;
insert into PJI_SYSTEM_CONFIG_HIST
(
REQUEST_ID,
USER_NAME,
PROCESS_NAME,
RUN_TYPE,
PARAMETERS,
CONFIG_PROJ_PERF_FLAG,
CONFIG_COST_FLAG,
CONFIG_PROFIT_FLAG,
CONFIG_UTIL_FLAG,
START_DATE,
END_DATE,
COMPLETION_TEXT
)
select
FND_GLOBAL.CONC_REQUEST_ID REQUEST_ID,
substr(FND_GLOBAL.USER_NAME, 1, 10) USER_NAME,
l_process PROCESS_NAME,
l_extraction_type RUN_TYPE,
substr(p_run_mode || ', ' ||
to_char(p_operating_unit) || ', ' ||
p_project_type || ', ' ||
to_char(p_project_organization_id) || ', ' ||
p_from_project || ', ' ||
p_to_project || ', ' ||
to_char(p_plan_type_id) || ', ' ||
to_char(p_rbs_header_id) || ', ' ||
p_transaction_type || ', ' ||
p_plan_versions || ', ' ||
p_only_pt_projects_flag, 1, 240) PARAMETERS,
null CONFIG_PROJ_PERF_FLAG,
null CONFIG_COST_FLAG,
null CONFIG_PROFIT_FLAG,
null CONFIG_UTIL_FLAG,
sysdate START_DATE,
null END_DATE,
null COMPLETION_TEXT
from
dual;
PJI_PJP_EXTRACTION_UTILS.UPDATE_EXTR_SCOPE;
select 'Y'
into l_prg_exists
from DUAL
where exists (select 1
from PA_PROJ_ELEMENT_VERSIONS proj
where proj.OBJECT_TYPE = 'PA_STRUCTURES' and
proj.PRG_GROUP is not null and
ROWNUM = 1);
insert into PJI_PJP_PROJ_BATCH_MAP
(
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
)
select
p_worker_id,
status.PROJECT_ID,
null PJI_PROJECT_STATUS,
null EXTRACTION_TYPE,
status.EXTRACTION_STATUS,
prj.PROJECT_TYPE,
prj.ORG_ID PROJECT_ORG_ID,
status.PROJECT_ORGANIZATION_ID,
status.PROJECT_TYPE_CLASS,
prj.PROJECT_CURRENCY_CODE,
'Y' PROJECT_ACTIVE_FLAG
/* Processing is not depending on Project status Bug 5057835
decode(active_projects.PROJECT_STATUS_CODE,
null, 'N', 'Y') PROJECT_ACTIVE_FLAG */
from
PJI_PJP_PROJ_EXTR_STATUS status,
PA_PROJECTS_ALL prj /*,
( Processing is not depending on Project status Bug 5057835
select
distinct
stat.PROJECT_STATUS_CODE
from
PA_PROJECT_STATUSES stat
where
stat.STATUS_TYPE = 'PROJECT' and
stat.PROJECT_SYSTEM_STATUS_CODE not in ('CLOSED',
'PENDING_CLOSE',
'PENDING_PURGE',
'PURGED')
) active_projects */
where
status.PROJECT_ID = prj.PROJECT_ID and
prj.PROJECT_TYPE = nvl(p_project_type, prj.PROJECT_TYPE) and
nvl(prj.ORG_ID, -99) = nvl(p_operating_unit, nvl(prj.ORG_ID, -99)) and
status.PROJECT_ORGANIZATION_ID = nvl(p_project_organization_id,
status.PROJECT_ORGANIZATION_ID) and
prj.SEGMENT1 between nvl(p_from_project, prj.SEGMENT1) and
nvl(p_to_project, prj.SEGMENT1) /*and
prj.PROJECT_STATUS_CODE = active_projects.PROJECT_STATUS_CODE (+) */;
insert into PJI_PJP_PROJ_BATCH_MAP
(
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
)
select
p_worker_id,
status.PROJECT_ID,
null PJI_PROJECT_STATUS,
null EXTRACTION_TYPE,
status.EXTRACTION_STATUS,
prj.PROJECT_TYPE,
prj.ORG_ID PROJECT_ORG_ID,
status.PROJECT_ORGANIZATION_ID,
status.PROJECT_TYPE_CLASS,
prj.PROJECT_CURRENCY_CODE,
decode(active_projects.PROJECT_ID, null, 'N', 'Y') PROJECT_ACTIVE_FLAG
from
PJI_PJP_PROJ_EXTR_STATUS status,
PA_PROJECTS_ALL prj,
(
select /*+ ordered
index(prg, PA_XBS_DENORM_N3)
distinct
emt.PROJECT_ID
from
PA_PROJECT_STATUSES stat,
PA_PROJECTS_ALL prj,
PA_XBS_DENORM prg,
PA_PROJ_ELEMENTS emt
where
stat.STATUS_TYPE = 'PROJECT' and
stat.PROJECT_SYSTEM_STATUS_CODE not in ('CLOSED',
'PENDING_CLOSE',
'PENDING_PURGE',
'PURGED') and
prj.PROJECT_STATUS_CODE = stat.PROJECT_STATUS_CODE and
prg.STRUCT_TYPE = 'PRG' and
prg.SUP_PROJECT_ID = prj.PROJECT_ID and
emt.PROJ_ELEMENT_ID = prg.SUB_EMT_ID
) active_projects
where
status.PROJECT_ID = prj.PROJECT_ID and
prj.PROJECT_TYPE = nvl(p_project_type, prj.PROJECT_TYPE) and
nvl(prj.org_id,-99) = nvl(p_operating_unit, nvl(prj.org_id,-99)) and
status.PROJECT_ORGANIZATION_ID = nvl(p_project_organization_id,
status.PROJECT_ORGANIZATION_ID) and
prj.segment1 between nvl(p_from_project,prj.segment1) and nvl(p_to_project,prj.segment1) and
status.PROJECT_ID = active_projects.PROJECT_ID (+); */
select /*+ ordered */
'Y'
into
l_prg_exists
from
PJI_PJP_PROJ_BATCH_MAP map,
PA_PROJ_ELEMENT_VERSIONS proj
where
map.WORKER_ID = p_worker_id and
map.PROJECT_ID = proj.PROJECT_ID and
proj.OBJECT_TYPE = 'PA_STRUCTURES' and
proj.PRG_GROUP is not null and
ROWNUM = 1;
insert into PJI_PJP_PROJ_BATCH_MAP
(
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
)
select /*+ ordered */
p_worker_id,
status.PROJECT_ID,
null PJI_PROJECT_STATUS,
null EXTRACTION_TYPE,
status.EXTRACTION_STATUS,
prj.PROJECT_TYPE,
prj.ORG_ID PROJECT_ORG_ID,
status.PROJECT_ORGANIZATION_ID,
status.PROJECT_TYPE_CLASS,
prj.PROJECT_CURRENCY_CODE,
'Y' PROJECT_ACTIVE_FLAG
from
(
select /*+ ordered */
distinct
ver2.PROJECT_ID
from
PJI_PJP_PROJ_BATCH_MAP map,
PA_PROJ_ELEMENT_VERSIONS ver1,
PA_PROJ_ELEMENT_VERSIONS ver2
where
map.WORKER_ID = p_worker_id and
ver1.PROJECT_ID = map.PROJECT_ID and
ver1.PRG_GROUP is not null and
ver2.OBJECT_TYPE = 'PA_STRUCTURES' and
ver2.PRG_GROUP = ver1.PRG_GROUP
union
select /*+ ordered
index(prg1 PJI_XBS_DENORM_N3)
index(prg2 PJI_XBS_DENORM_N3) */
distinct
prg2.SUP_PROJECT_ID PROJECT_ID
from
PJI_PJP_PROJ_BATCH_MAP map,
PJI_XBS_DENORM prg1,
PJI_XBS_DENORM prg2
where
map.WORKER_ID = p_worker_id and
prg1.STRUCT_TYPE = 'PRG' and
prg1.SUP_PROJECT_ID = map.PROJECT_ID and
prg1.PRG_GROUP is not null and
prg2.STRUCT_TYPE = 'PRG' and
prg2.SUB_LEVEL = prg2.SUP_LEVEL and
prg2.PRG_GROUP = prg1.PRG_GROUP
) map,
PJI_PJP_PROJ_BATCH_MAP existing_projects,
PJI_PJP_PROJ_EXTR_STATUS status,
PA_PROJECTS_ALL prj
where
p_worker_id = existing_projects.WORKER_ID (+) and
map.PROJECT_ID = existing_projects.PROJECT_ID (+) and
existing_projects.PROJECT_ID is null and
map.PROJECT_ID = status.PROJECT_ID and
map.PROJECT_ID = prj.PROJECT_ID;
select
count(*)
into
l_count
from
PJI_PJP_PROJ_BATCH_MAP new_worker
where
new_worker.WORKER_ID = p_worker_id;
insert into PJI_PJP_PROJ_BATCH_MAP
(
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
)
select /*+ ordered
index(log, PA_PJI_PROJ_EVENTS_LOG_N1)
index(rbs_asg, PA_RBS_PRJ_ASSIGNMENTS_N1) */
distinct
p_worker_id,
rbs_asg.PROJECT_ID,
null PJI_PROJECT_STATUS,
null EXTRACTION_TYPE,
'R' EXTRACTION_STATUS,
prj.PROJECT_TYPE,
prj.ORG_ID PROJECT_ORG_ID,
prj.CARRYING_OUT_ORGANIZATION_ID PROJECT_ORGANIZATION_ID,
decode(pt.PROJECT_TYPE_CLASS_CODE,
'CAPITAL', 'C',
'CONTRACT', 'B',
'INDIRECT', 'I') PROJECT_TYPE_CLASS,
prj.PROJECT_CURRENCY_CODE,
'Y' PROJECT_ACTIVE_FLAG
/* decode(active_projects.PROJECT_ID,
null, 'N', 'Y') PROJECT_ACTIVE_FLAG */
from
PA_PJI_PROJ_EVENTS_LOG log,
PA_RBS_PRJ_ASSIGNMENTS rbs_asg,
PA_PROJECTS_ALL prj,
PA_PROJECT_TYPES_ALL pt /*,
( Processing is now not depending on status Bug 5057835
select /*+ ordered
index(prg, PA_XBS_DENORM_N3)
distinct
emt.PROJECT_ID
from
PA_PROJECT_STATUSES stat,
PA_PROJECTS_ALL prj,
PA_XBS_DENORM prg,
PA_PROJ_ELEMENTS emt
where
stat.STATUS_TYPE = 'PROJECT' and
stat.PROJECT_SYSTEM_STATUS_CODE not in ('CLOSED',
'PENDING_CLOSE',
'PENDING_PURGE',
'PURGED') and
prj.PROJECT_STATUS_CODE = stat.PROJECT_STATUS_CODE and
prg.STRUCT_TYPE = 'PRG' and
prg.SUP_PROJECT_ID = prj.PROJECT_ID and
emt.PROJ_ELEMENT_ID = prg.SUB_EMT_ID
) active_projects */
where
log.EVENT_TYPE in ('RBS_PUSH', 'RBS_DELETE') and
-- rbs_asg.RBS_VERSION_ID in (log.EVENT_OBJECT, log.ATTRIBUTE2) and --Commented for Bug#5728852 by VVJOSHI
rbs_asg.RBS_HEADER_ID = nvl(p_rbs_header_id,
rbs_asg.RBS_HEADER_ID) and
nvl(prj.org_id,-99) = nvl(p_operating_unit, nvl(prj.org_id,-99)) and
rbs_asg.PROJECT_ID = prj.PROJECT_ID and
nvl(prj.ORG_ID, -1) = nvl(pt.ORG_ID, -1) and
prj.PROJECT_TYPE = pt.PROJECT_TYPE ; /* and
SELECT 'FULL' INTO l_extraction_type FROM DUAL
WHERE EXISTS
(
SELECT 1
FROM pji_pjp_proj_extr_status extr,
PJI_PJP_PROJ_BATCH_MAP map
WHERE map.project_id=extr.project_id
AND extr.extraction_status='F'
AND WORKER_ID = p_worker_id
);
select distinct
existing_workers.WORKER_ID
from PJI_PJP_PROJ_BATCH_MAP existing_workers
where existing_workers.WORKER_ID <> p_worker_id and
exists (select 1
from PJI_PJP_PROJ_BATCH_MAP new_worker
where new_worker.WORKER_ID = p_worker_id and
new_worker.PROJECT_ID = existing_workers.PROJECT_ID)
) loop
l_count := l_count + 1;
delete
from PJI_PJP_PROJ_BATCH_MAP
where WORKER_ID = p_worker_id and
((l_extraction_type = 'FULL' and
EXTRACTION_STATUS <> 'F') or
(l_extraction_type = 'INCREMENTAL' and
EXTRACTION_STATUS <> 'I') or
(l_extraction_type = 'PARTIAL' and
EXTRACTION_STATUS <> 'I'));
delete
from PA_PJI_PROJ_EVENTS_LOG log
where log.EVENT_TYPE in ('WBS_CHANGE',
'WBS_PUBLISH'/*,
'RBS_ASSOC',
'RBS_PRG'*/ --Commented for bug#6113807 by VVJOSHI
) and
log.ATTRIBUTE1 in (select stat.PROJECT_ID
from PJI_PJP_PROJ_BATCH_MAP map,
PJI_PJP_PROJ_EXTR_STATUS stat
where map.WORKER_ID = p_worker_id and
stat.PROJECT_ID = map.PROJECT_ID and
stat.EXTRACTION_STATUS = 'F');
update PJI_PJP_PROJ_EXTR_STATUS
set EXTRACTION_STATUS = 'I',
LAST_UPDATE_DATE = sysdate
where l_extraction_type = 'FULL' and
EXTRACTION_STATUS = 'F' and
PROJECT_ID in (select PROJECT_ID
from PJI_PJP_PROJ_BATCH_MAP
where WORKER_ID = p_worker_id);
update PJI_PJP_WBS_HEADER wbs_hdr
set wbs_hdr.LOCK_FLAG = 'P'
where wbs_hdr.ROWID = c.HDR_ROWID;
SELECT SUM(REFRESH_CODE) INTO l_refresh_code
FROM (
SELECT
DECODE(p_transaction_type,'ALL_TXN_TYPE',1,'ACTUAL_TXN_TYPE',1,0) REFRESH_CODE
FROM DUAL
UNION ALL
SELECT
(CASE p_plan_versions
WHEN 'ALL_PLAN_VERSION' THEN 62
WHEN 'CB_VERSION' THEN 2
WHEN 'CO_VERSION' THEN 4
WHEN 'LP_VERSION' THEN 8
WHEN 'WK_VERSION' THEN 16
WHEN 'LAT_VERSION' THEN 30
ELSE 0
END) REFRESH_CODE
FROM DUAL
);
select max(ver.RBS_VERSION_ID)
into l_rbs_version_id
from PA_RBS_VERSIONS_B ver,
PJI_PJP_RBS_HEADER rbs_hdr
where ver.RBS_HEADER_ID = p_rbs_header_id and
ver.STATUS_CODE = 'FROZEN' and
ver.RBS_VERSION_ID = rbs_hdr.RBS_VERSION_ID;
PJI_PJP_SUM_ROLLUP.UPDATE_XBS_DENORM_FULL(p_worker_id);
PJI_PJP_SUM_ROLLUP.UPDATE_PROGRAM_WBS(p_worker_id);
PJI_PJP_SUM_ROLLUP.UPDATE_PROGRAM_RBS(p_worker_id);
PJI_PJP_SUM_ROLLUP.PROCESS_PENDING_PLAN_UPDATES(p_worker_id);
PJI_PJP_SUM_ROLLUP.UPDATE_WBS_HDR(p_worker_id);
PJI_PJP_SUM_ROLLUP.UPDATE_FPR_ROWS(p_worker_id);
PJI_PJP_SUM_ROLLUP.INSERT_FPR_ROWS(p_worker_id);
PJI_PJP_SUM_ROLLUP.UPDATE_ACR_ROWS(p_worker_id);
PJI_PJP_SUM_ROLLUP.INSERT_ACR_ROWS(p_worker_id);
PJI_PJP_SUM_ROLLUP.UPDATE_XBS_DENORM(p_worker_id);
PJI_PJP_SUM_ROLLUP.UPDATE_RBS_DENORM(p_worker_id);
PJI_FM_SUM_PSI.BALANCES_UPDATE_DELTA(p_worker_id);
PJI_FM_SUM_PSI.BALANCES_INSERT_DELTA(p_worker_id);
PJI_FM_SUM_PSI.BALANCES_INSERT_DELTA_CMT(p_worker_id);
update PJI_PJP_PROJ_EXTR_STATUS
set LAST_UPDATE_DATE = sysdate
where PROJECT_ID in (select map.PROJECT_ID
from PJI_PJP_PROJ_BATCH_MAP map
where map.WORKER_ID = p_worker_id);
delete from PJI_PJP_PROJ_BATCH_MAP where WORKER_ID = p_worker_id;
update PJI_SYSTEM_CONFIG_HIST
set END_DATE = sysdate,
COMPLETION_TEXT = 'Normal completion'
where PROCESS_NAME = l_process and
END_DATE is null;
update PJI_SYSTEM_CONFIG_HIST
set END_DATE = sysdate,
COMPLETION_TEXT = l_sqlerrm
where PROCESS_NAME = g_process || p_worker_id and
END_DATE is null;