[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
g_update_num_rows_limit NUMBER := 1; -- 1000;
g_smart_rows_deleted NUMBER :=0; --maintains the number of rows deleted in delete smart slice api
PROCEDURE GET_HDRS_TOLOCK_FOR_UPDATE (
x_fp_version_ids OUT NOCOPY SYSTEM.pa_num_tbl_type -- := SYSTEM.pa_num_tbl_type(),
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_code OUT NOCOPY VARCHAR2 );
PROCEDURE DELETE_EVENTS (
p_event_ids IN SYSTEM.pa_num_tbl_type
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_code OUT NOCOPY VARCHAR2 );
PROCEDURE INSERT_APPLY_PROG_VD;
select segment1
into l_project
from pa_projects_all
where project_id = p_project_id;
INSERT INTO pji_fm_extr_plan_lines_debug
(
PROJECT_ID ,
PROJECT_ORG_ID ,
PROJECT_ELEMENT_ID ,
STRUCT_VER_ID ,
PERIOD_NAME ,
CALENDAR_TYPE ,
START_DATE ,
END_DATE ,
RBS_ELEMENT_ID ,
RBS_VERSION_ID ,
PLAN_VERSION_ID ,
PLAN_TYPE_ID ,
WP_VERSION_FLAG ,
ROLLUP_TYPE ,
TXN_CURRENCY_CODE ,
TXN_RAW_COST ,
TXN_BURDENED_COST ,
TXN_REVENUE ,
PRJ_CURRENCY_CODE ,
PRJ_RAW_COST ,
PRJ_BURDENED_COST ,
PRJ_REVENUE ,
PFC_CURRENCY_CODE ,
PFC_RAW_COST ,
PFC_BURDENED_COST ,
PFC_REVENUE ,
QUANTITY ,
RESOURCE_CLASS_CODE ,
RATE_BASED_FLAG ,
ACT_QUANTITY ,
ACT_TXN_BURDENED_COST ,
ACT_PRJ_BURDENED_COST ,
ACT_PFC_BURDENED_COST ,
ACT_TXN_RAW_COST ,
ACT_PRJ_RAW_COST ,
ACT_PFC_RAW_COST ,
ACT_TXN_REVENUE ,
ACT_PRJ_REVENUE ,
ACT_PFC_REVENUE ,
ETC_QUANTITY ,
ETC_TXN_BURDENED_COST ,
ETC_PRJ_BURDENED_COST ,
ETC_PFC_BURDENED_COST ,
ETC_TXN_RAW_COST ,
ETC_PRJ_RAW_COST ,
ETC_PFC_RAW_COST ,
CREATION_DATE
)
SELECT
PROJECT_ID ,
PROJECT_ORG_ID ,
PROJECT_ELEMENT_ID ,
STRUCT_VER_ID ,
PERIOD_NAME ,
CALENDAR_TYPE ,
START_DATE ,
END_DATE ,
RBS_ELEMENT_ID ,
RBS_VERSION_ID ,
PLAN_VERSION_ID ,
PLAN_TYPE_ID ,
WP_VERSION_FLAG ,
ROLLUP_TYPE ,
TXN_CURRENCY_CODE ,
TXN_RAW_COST ,
TXN_BURDENED_COST ,
TXN_REVENUE ,
PRJ_CURRENCY_CODE ,
PRJ_RAW_COST ,
PRJ_BURDENED_COST ,
PRJ_REVENUE ,
PFC_CURRENCY_CODE ,
PFC_RAW_COST ,
PFC_BURDENED_COST ,
PFC_REVENUE ,
QUANTITY ,
RESOURCE_CLASS_CODE ,
RATE_BASED_FLAG ,
ACT_QUANTITY ,
ACT_TXN_BURDENED_COST ,
ACT_PRJ_BURDENED_COST ,
ACT_PFC_BURDENED_COST ,
ACT_TXN_RAW_COST ,
ACT_PRJ_RAW_COST ,
ACT_PFC_RAW_COST ,
ACT_TXN_REVENUE ,
ACT_PRJ_REVENUE ,
ACT_PFC_REVENUE ,
ETC_QUANTITY ,
ETC_TXN_BURDENED_COST ,
ETC_PRJ_BURDENED_COST ,
ETC_PFC_BURDENED_COST ,
ETC_TXN_RAW_COST ,
ETC_PRJ_RAW_COST ,
ETC_PFC_RAW_COST ,
SYSDATE
FROM
pji_fm_extr_plan_lines ;
PROCEDURE PLAN_DELETE (
p_fp_version_ids IN SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type(),
x_return_status OUT NOCOPY VARCHAR2,
x_msg_code OUT NOCOPY VARCHAR2 ) IS
l_fp_version_ids SYSTEM.pa_num_tbl_type := p_fp_version_ids;
print_time ( ' PLAN_DELETE api ' );
print_time ( ' plan delete 001 ' );
print_time ( ' plan delete 002 ' );
l_event_rec.event_type := 'PLAN_DELETE';
print_time ( ' plan delete 003 ' );
PLAN_DELETE_PVT (
p_event_id => l_event_rec.event_id
, x_return_status => x_return_status
, x_processing_code => x_msg_code);
print_time ( ' plan delete 004 ' );
print_time ( ' plan delete 005 ' );
, p_procedure_name => 'PLAN_DELETE'
, x_return_status => x_return_status ) ;
PROCEDURE PLAN_DELETE_PVT (
p_event_id IN NUMBER
, x_return_status OUT NOCOPY VARCHAR2
, x_processing_code OUT NOCOPY VARCHAR2 ) IS
l_plan_version_id NUMBER := NULL;
SELECT event_object
INTO l_plan_version_id
FROM PA_PJI_PROJ_EVENTS_LOG
WHERE event_id = p_event_id;
print_time ( ' plan delete pvt 002 ' );
print_time ( ' plan delete pvt 003 ' );
, p_context => 'DELETE'
, x_return_status => x_return_status
, x_msg_code => x_processing_code );
print_time ( ' plan delete pvt 004 ' );
print_time ( ' plan delete pvt 005 ' );
PJI_FM_PLAN_MAINT.DELETE_ALL_PVT ( p_fp_version_ids => l_fp_version_ids);
print_time ( ' plan delete pvt 006 ' );
print_time ( ' plan delete pvt 007 ' );
DELETE_EVENTS (
p_event_ids => l_event_ids
, x_return_status => x_return_status
, x_msg_code => x_processing_code );
print_time ( ' plan delete pvt 008 ' );
, p_procedure_name => 'PLAN_DELETE_PVT'
, x_return_status => x_return_status ) ;
INSERT INTO pji_event_log_debug
( event_type
, event_id
, event_object
, operation_type
, status
, last_update_date
, last_updated_by
, creation_date
, created_by
, last_update_login)
VALUES
( 'Create'
, pa_pji_proj_events_log_s.NEXTVAL
, 'x'
, 'x'
, 'x'
, SYSDATE
, 1
, SYSDATE
, 1
, 1);
PROCEDURE PLAN_UPDATE (
p_plan_version_id IN NUMBER := NULL,
x_msg_code OUT NOCOPY VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2 ) IS
l_processing_code VARCHAR2(12000);
print_time ( 'PLAN_UPDATE begin');
INSERT_APPLY_PROG_VD;
INSERT INTO pji_event_log_debug
( event_type
, event_id
, event_object
, operation_type
, status
, last_update_date
, last_updated_by
, creation_date
, created_by
, last_update_login)
VALUES
( 'Update'
, pa_pji_proj_events_log_s.NEXTVAL
, 'x'
, 'x'
, 'x'
, SYSDATE
, 1
, SYSDATE
, 1
, 1);
PLAN_UPDATE_PVT(
p_plan_version_id => p_plan_version_id,
x_return_status => x_return_status,
x_processing_code => x_msg_code);
print_time ( 'PLAN_UPDATE end');
, p_procedure_name => 'PLAN_UPDATE'
, x_return_status => x_return_status ) ;
PROCEDURE PLAN_UPDATE_PVT
( p_plan_version_id IN NUMBER := NULL,
x_return_status OUT NOCOPY VARCHAR2,
x_processing_code OUT NOCOPY VARCHAR2 ) IS
CURSOR GET_MAINT_SMART_SLICE_PARAMS
IS SELECT DISTINCT rbs_version_id,plan_version_id,struct_ver_id
FROM pji_fm_extr_plan_lines
WHERE plan_version_id = NVL(p_plan_version_id,plan_version_id)
AND ROWID IN ( SELECT extr_lines_rowid FROM pji_fp_rmap_fpr_update_t)
ORDER BY rbs_version_id,struct_ver_id;
l_update_id NUMBER;
print_time ( 'PLAN_UPDATE_PVT begin ' );
INSERT INTO pji_fp_rmap_fpr_update_t (EXTR_LINES_ROWID)
SELECT ROWID FROM pji_fm_extr_plan_lines;
INSERT INTO pji_fp_rmap_fpr_update_t (EXTR_LINES_ROWID)
SELECT ROWID FROM pji_fm_extr_plan_lines
WHERE plan_version_id = p_plan_version_id;
print_time ( 'PLAN_UPDATE_PVT 2 ');
print_time ( 'PLAN_UPDATE_PVT 3 ');
GET_HDRS_TOLOCK_FOR_UPDATE (
x_fp_version_ids => l_fp_version_ids
, x_return_status => x_return_status
, x_msg_code => x_processing_code );
print_time ( 'PLAN_UPDATE_PVT 4 ');
, p_context => 'UPDATE'
, x_return_status => l_return_status
, x_msg_code => l_msg_code );
print_time ( 'PLAN_UPDATE_PVT 4.999 ');
print_time ( 'PLAN_UPDATE_PVT 5 ');
Pji_Fm_Plan_Maint.UPDATE_PRIMARY_PVT (
p_plan_version_ids => l_fp_version_ids
, p_commit => 'F' );
print_time ( 'PLAN_UPDATE_PfVT 5.11 ');
DELETE_SMART_SLICE (
p_online_flag => 'Y'
, x_return_status => x_return_status ) ;
The PJI_FM_EXTR_PLAN_LINES needs to be deleted before calling maintain_smart_slice as there is a commit
in maintain_smart_slice which nulls out pji_fp_rmap_fpr_update_t */
/* commenting as the incremental smart slice will be created */
/*start
OPEN GET_MAINT_SMART_SLICE_PARAMS;
DELETE FROM PJI_FM_EXTR_PLAN_LINES
WHERE 1 = 1
AND ROWID IN ( SELECT extr_lines_rowid FROM pji_fp_rmap_fpr_update_t)
AND TXN_CURRENCY_CODE IS NOT NULL
AND prj_currency_code IS NOT NULL
AND pfc_currency_code IS NOT NULL;
IF (g_smart_rows_deleted >0) THEN
FOR i IN 1..p_rbs_version_id_tbl.COUNT LOOP
l_new_rbs_version_id := p_rbs_version_id_tbl(i);
l_plan_version_id_tbl.DELETE;
l_plan_version_id_tbl.DELETE;
/*g_smart_rows_deleted > 0)*/
print_time ( 'PLAN_UPDATE_PVT end ' );
, p_procedure_name => 'PLAN_UPDATE_PVT'
, x_return_status => x_return_status ) ;
PROCEDURE DELETE_SMART_SLICE (
p_online_flag IN VARCHAR2 := 'Y'
, x_return_status OUT NOCOPY VARCHAR2 ) IS
BEGIN
Pji_Pjp_Fp_Curr_Wrap.INIT_ERR_STACK
( p_package_name => g_package_name
, x_return_status => x_return_status );
/* DELETE FROM pji_rollup_level_status
WHERE plan_version_id IN (SELECT plan_version_id FROM pji_fm_extr_plnver3_t);
DELETE FROM pji_rollup_level_status
WHERE (project_id,plan_version_id,plan_type_code) IN
(SELECT project_id,plan_version_id,plan_type_code FROM pji_fm_extr_plnver3_t); /*4771527 */
DELETE FROM pji_fp_xbs_accum_f
WHERE (project_id,plan_version_id,plan_type_code) IN /*4771527 */
(SELECT project_id, plan_version_id,plan_type_code FROM pji_fm_extr_plnver3_t)
AND (rbs_aggr_level = 'R'
OR (rbs_aggr_level = 'L'
AND wbs_rollup_flag = 'Y')) ;
g_smart_rows_deleted := SQL%ROWCOUNT;
/* DELETE FROM pji_rollup_level_status
WHERE plan_version_id IN (SELECT plan_version_id FROM pji_fm_extr_plnver4);
DELETE FROM pji_rollup_level_status
WHERE (project_id,plan_version_id,plan_type_code ) IN
(SELECT project_id,plan_version_id,plan_type_code FROM pji_fm_extr_plnver4); /*4771527 */
DELETE FROM pji_fp_xbs_accum_f
WHERE (project_id,plan_version_id,plan_type_code ) IN
(SELECT project_id, plan_version_id,plan_type_code FROM pji_fm_extr_plnver4) /*4771527 */
AND (rbs_aggr_level = 'R'
OR (rbs_aggr_level = 'L'
AND wbs_rollup_flag = 'Y')) ;
g_smart_rows_deleted := SQL%ROWCOUNT;
, p_procedure_name => 'DELETE_SMART_SLICE'
, x_return_status => x_return_status ) ;
PROCEDURE PLAN_UPDATE_ACT_ETC (
p_plan_wbs_ver_id IN NUMBER
, p_prev_pub_wbs_ver_id IN NUMBER := NULL
-- p_plan_version_id IN NUMBER
-- , p_prev_pub_version_id IN NUMBER := NULL
, x_msg_code OUT NOCOPY VARCHAR2
, x_return_status OUT NOCOPY VARCHAR2 ) IS
l_plan_version_id NUMBER := NULL;
print_time ( 'PLAN_UPDATE_ACT_ETC begin ');
print_time ( 'PLAN_UPDATE_ACT_ETC p_plan_wbs_ver_id ' || p_plan_wbs_ver_id || ' p_prev_pub_wbs_ver_id ' || p_prev_pub_wbs_ver_id );
SELECT plan_version_id
INTO l_plan_version_id
FROM pji_pjp_wbs_header
WHERE wbs_version_id = p_plan_wbs_ver_id
AND wp_flag = 'Y'; -- AND plan_version_id > 0;
print_time ( 'PLAN_UPDATE_ACT_ETC.. new plan structure version error p_plan_wbs_ver_id = ' || p_plan_wbs_ver_id || ' ' || SQLERRM );
print_time ( 'PLAN_UPDATE_ACT_ETC new structure version id is null, returning.');
SELECT plan_version_id
INTO l_prev_pub_version_id
FROM pji_pjp_wbs_header
WHERE wbs_version_id = p_prev_pub_wbs_ver_id
AND wp_flag = 'Y'; -- AND plan_version_id > 0;
print_time ( 'PLAN_UPDATE_ACT_ETC.. new plan structure version error p_prev_pub_wbs_ver_id = ' || p_prev_pub_wbs_ver_id || ' ' || SQLERRM );
print_time ( 'PLAN_UPDATE_ACT_ETC prev published structure version id is null');
SELECT COUNT(1)
INTO l_temp
FROM pa_budget_versions
WHERE 1=1
AND budget_version_id IN (l_plan_version_id , l_prev_pub_version_id );
print_time ( 'PLAN_UPDATE_PVT_ACT_ETC invalid plan version ids.. l_temp = ' || l_temp || '. Returning..' );
INSERT INTO pji_event_log_debug
( event_type
, event_id
, event_object
, operation_type
, status
, last_update_date
, last_updated_by
, creation_date
, created_by
, last_update_login)
VALUES
( 'Update ACT ETC'
, pa_pji_proj_events_log_s.NEXTVAL
, 'x'
, 'x'
, 'x'
, SYSDATE
, 1
, SYSDATE
, 1
, 1);
PLAN_UPDATE_PVT_ACT_ETC(
p_plan_version_id => l_plan_version_id,
p_prev_pub_version_id => l_prev_pub_version_id,
x_return_status => x_return_status,
x_processing_code => x_msg_code);
print_time ( 'PLAN_UPDATE_ACT_ETC end');
, p_procedure_name => 'PLAN_UPDATE_ACT_ETC'
, x_return_status => x_return_status ) ;
PROCEDURE PLAN_UPDATE_PVT_ACT_ETC (
p_plan_version_id IN NUMBER
, p_prev_pub_version_id IN NUMBER := NULL
, x_return_status OUT NOCOPY VARCHAR2
, x_processing_code OUT NOCOPY VARCHAR2) IS
l_num_rows_extr_lines NUMBER := NULL;
l_update_id NUMBER;
print_time ( 'PLAN_UPDATE_PVT_ACT_ETC begin ' );
print_time ( 'PLAN_UPDATE_PVT_ACT_ETC p_plan_version_id ' || p_plan_version_id || ' p_prev_pub_version_id ' || p_prev_pub_version_id );
print_time ( 'PLAN_UPDATE_PVT_ACT_ETC 1 plan version id is null.. returning ' );
INSERT INTO pji_fp_rmap_fpr_update_t (EXTR_LINES_ROWID)
SELECT ROWID FROM pji_fm_extr_plan_lines
WHERE plan_version_id = p_plan_version_id;
SELECT COUNT(1)
INTO l_temp
FROM pji_fm_extr_plan_lines
WHERE ROWNUM=1; /*Added for bug 3928020*/
print_time ( 'PLAN_UPDATE_PVT_ACT_ETC 1 number of rows in plan lines is .. ' || l_temp);
SELECT COUNT(1)
INTO l_num_rows_extr_lines
FROM pji_fp_rmap_fpr_update_t
WHERE ROWNUM=1; /*Added for bug 3928020*/
print_time ( 'PLAN_UPDATE_PVT_ACT_ETC 1.5 number of rows in rowid table is.. ' || l_num_rows_extr_lines );
print_time ( 'PLAN_UPDATE_PVT_ACT_ETC 2 ');
print_time ( 'PLAN_UPDATE_PVT_ACT_ETC 3 ');
GET_HDRS_TOLOCK_FOR_UPDATE (
x_fp_version_ids => l_fp_version_ids
, x_return_status => x_return_status
, x_msg_code => x_processing_code );
print_time ( 'PLAN_UPDATE_PVT_ACT_ETC 4 ');
, p_context => 'UPDATE' -- 'PUBLISH'
, x_return_status => l_return_status
, x_msg_code => l_msg_code );
print_time ( ' PLAN_UPDATE_PVT_ACT_ETC .. Could not lock all headers.. Exitting..');
print_time ( ' PLAN_UPDATE_PVT_ACT_ETC .. no lines, so no lines/headers to lock.');
print_time ( 'PLAN_UPDATE_PVT_ACT_ETC 5 ');
Pji_Fm_Plan_Maint.UPDATE_PRIMARY_PVT_ACT_ETC (
p_plan_version_id => p_plan_version_id
, p_prev_pub_version_id => p_prev_pub_version_id
, x_return_status => x_return_status
, x_processing_code => x_processing_code);
print_time ( 'PLAN_UPDATE_PVT_ACT_ETC 5.11 ');
DELETE FROM PJI_FM_EXTR_PLAN_LINES
WHERE 1 = 1
AND ROWID IN ( SELECT extr_lines_rowid FROM pji_fp_rmap_fpr_update_t)
AND TXN_CURRENCY_CODE IS NOT NULL
AND prj_currency_code IS NOT NULL
AND pfc_currency_code IS NOT NULL;
print_time ( 'PLAN_UPDATE_PVT_ACT_ETC end ' );
, p_procedure_name => 'PLAN_UPDATE_PVT_ACT_ETC'
, x_return_status => x_return_status ) ;
SELECT attribute1, event_object, attribute2
INTO l_s_plan_version_id, l_d_plan_version_id, l_working_or_baselined
FROM PA_PJI_PROJ_EVENTS_LOG
WHERE event_id = p_event_id;
DELETE_EVENTS (
p_event_ids => l_event_ids
, x_return_status => l_return_status
, x_msg_code => l_msg_code );
SELECT bv1.project_id, bv1.fin_plan_type_id, bv1.wp_version_flag,
DECODE(bv1.version_type, 'COST' , 'C' , 'REVENUE' , 'R' , 'A')
INTO l_project_id, l_plan_type_id, l_wp_version_flag,l_plan_type_code
FROM pa_budget_versions bv1
WHERE bv1.budget_version_id = p_baseline_version_id;
SELECT plan_version_id
INTO l_curr_bl_ver_id
FROM PJI_PJP_WBS_HEADER wbs_hdr
WHERE 1 = 1
AND project_id = l_project_id
AND plan_type_id = l_plan_type_id
AND plan_type_code = l_plan_type_code
AND cb_flag = 'Y'
AND plan_version_id > 0 ;
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 attribute1
, event_object
, attribute3
, attribute2
, attribute4
INTO l_project_id
, l_new_version_id
, l_old_baseline_version_id
, l_plan_type_id
, l_plan_type_code /*4771527*/
FROM PA_PJI_PROJ_EVENTS_LOG
WHERE event_id = p_event_id;
UPDATE pji_pjp_wbs_header
SET cb_flag = DECODE( plan_version_id
, l_old_baseline_version_id, 'N'
, l_new_version_id, 'Y'
, -3, 'Y' )
, LAST_UPDATE_DATE = l_last_update_date
, LAST_UPDATED_BY = l_last_updated_by
, LAST_UPDATE_LOGIN = l_last_update_login
WHERE plan_version_id IN (l_old_baseline_version_id
, l_new_version_id
, -3)
AND project_id = l_project_id
AND plan_type_id = l_plan_type_id
AND plan_type_code = l_plan_type_code; /*4771527 */
DELETE_EVENTS (
p_event_ids => l_event_ids
, x_return_status => l_return_status
, x_msg_code => l_msg_code );
print_time ( ' PLAN_BASELINE_PVT api 008: Deleted events. ' );
SELECT wp_flag
INTO l_wp_flag
FROM pji_pjp_wbs_header
WHERE plan_version_id = p_original_version_id;
SELECT bv1.project_id, bv1.fin_plan_type_id,
DECODE(bv1.version_type, 'COST' , 'C' , 'REVENUE' , 'R' , 'A')
INTO l_project_id, l_plan_type_id, l_plan_type_code
FROM pa_budget_versions bv1
WHERE bv1.budget_version_id = p_original_version_id;
SELECT plan_version_id
INTO l_curr_or_ver_id
FROM PJI_PJP_WBS_HEADER wbs_hdr
WHERE 1 = 1
AND project_id = l_project_id
AND plan_type_id = l_plan_type_id
AND co_flag = 'Y'
AND plan_version_id > 0
AND plan_type_code = l_plan_type_code ; /* 4471527 */
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 attribute1, attribute3, event_object, attribute2,attribute4
INTO l_project_id, l_old_orig_ver_id, l_new_orig_ver_id, l_plan_type_id,l_plan_type_code /*4771527 */
FROM PA_PJI_PROJ_EVENTS_LOG
WHERE event_id = p_event_id;
UPDATE pji_pjp_wbs_header
SET co_flag = DECODE(plan_version_id
, l_old_orig_ver_id, 'N'
, l_new_orig_ver_id, 'Y'
, -4, 'Y')
, LAST_UPDATE_DATE = l_last_update_date
, LAST_UPDATED_BY = l_last_updated_by
, LAST_UPDATE_LOGIN = l_last_update_login
WHERE plan_version_id IN (l_old_orig_ver_id, l_new_orig_ver_id, -4)
AND project_id = l_project_id
AND plan_type_id = l_plan_type_id
AND plan_type_code = l_plan_type_code ; /* 4771527 */
PJI_FM_PLAN_MAINT_T_PVT.UPDATE_WBS_HDR;
print_time('Updated the WBS header table with min max txn dates.');
DELETE_EVENTS (
p_event_ids => l_event_ids
, x_return_status => l_return_status
, x_msg_code => l_msg_code );
select TEMPLATE_FLAG
into l_template_flag
from PA_PROJECTS_ALL
where PROJECT_ID = p_project_id;
select TEMPLATE_FLAG
into l_template_flag
from PA_PROJECTS_ALL
where PROJECT_ID = p_project_id;
PROCEDURE RBS_DELETE (
p_rbs_version_id IN NUMBER
, p_project_id IN NUMBER
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_code OUT NOCOPY VARCHAR2 ) IS
l_event_rec pa_pji_proj_events_log%ROWTYPE;
l_event_rec.event_type := 'RBS_DELETE';
PJI_FM_PLAN_MAINT.FINPLAN_DELETE(
p_fp_version_ids => l_fp_version_ids
, p_commit => 'F' );
DELETE_EVENTS (
p_event_ids => l_event_ids
, x_return_status => l_return_status
, x_msg_code => l_msg_code );
, p_procedure_name => 'RBS_DELETE'
, x_return_status => x_return_status ) ;
p_event_rec.last_update_date := SYSDATE;
p_event_rec.last_updated_by := Fnd_Global.USER_ID;
p_event_rec.last_update_login := Fnd_Global.LOGIN_ID;
SELECT pa_pji_proj_events_log_s.NEXTVAL
INTO p_event_rec.event_id
FROM DUAL;
select 'Y'
into event_already_exists
from (select attribute2
from pa_pji_proj_events_log
where event_type = 'RBS_PUSH'
)a ,
pa_rbs_versions_b b
where to_number(a.attribute2) = b.rbs_version_id
and b.rbs_header_id =
(select rbs_header_id
from pa_rbs_versions_b
where rbs_version_id = p_event_rec.attribute2)
and rownum = 1;
select rbs_header_id
into l_rbs_header_id
from pa_rbs_versions_b
where rbs_version_id = to_number(p_event_rec.attribute2);
UPDATE pa_pji_proj_events_log
SET event_object = p_event_rec.event_object,
last_update_date = p_event_rec.last_update_date,
last_updated_by = p_event_rec.last_updated_by
WHERE event_type = 'RBS_PUSH'
AND attribute2 in (select rbs_version_id
from pa_rbs_versions_b
where rbs_header_id = l_rbs_header_id);
INSERT INTO pa_pji_proj_events_log
( event_type
, event_id
, event_object
, operation_type
, status
, last_update_date
, last_updated_by
, creation_date
, created_by
, last_update_login
, attribute_category
, attribute1
, attribute2
, attribute3
, attribute4
, attribute5
, attribute6
, attribute7
, attribute8
, attribute9
, attribute10
, attribute11
, attribute12
, attribute13
, attribute14
, attribute15
, attribute16
, attribute17
, attribute18
, attribute19
, attribute20
)
VALUES (
p_event_rec.event_type
, p_event_rec.event_id
, p_event_rec.event_object
, p_event_rec.operation_type
, p_event_rec.status
, p_event_rec.last_update_date
, p_event_rec.last_updated_by
, p_event_rec.creation_date
, p_event_rec.created_by
, p_event_rec.last_update_login
, p_event_rec.attribute_category
, p_event_rec.attribute1
, p_event_rec.attribute2
, p_event_rec.attribute3
, p_event_rec.attribute4
, p_event_rec.attribute5
, p_event_rec.attribute6
, p_event_rec.attribute7
, p_event_rec.attribute8
, p_event_rec.attribute9
, p_event_rec.attribute10
, p_event_rec.attribute11
, p_event_rec.attribute12
, p_event_rec.attribute13
, p_event_rec.attribute14
, p_event_rec.attribute15
, p_event_rec.attribute16
, p_event_rec.attribute17
, p_event_rec.attribute18
, p_event_rec.attribute19
, p_event_rec.attribute20
);
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 project_id
FROM pji_pjp_wbs_header
WHERE plan_version_id = p_plan_version_id
AND lock_flag IS NULL
FOR UPDATE NOWAIT;
cursor c_wh_lock_update_cur (p_plan_version_id IN NUMBER,
p_project_id IN NUMBER) IS
select /*+ use_nl(sup_wbs_hdr)
index(sup_wbs_hdr PJI_PJP_WBS_HEADER_N1) */
sup_wbs_hdr.PLAN_VERSION_ID pvi
from
PJI_PJP_WBS_HEADER sup_wbs_hdr
where
(sup_wbs_hdr.PROJECT_ID,
sup_wbs_hdr.WBS_VERSION_ID) in
(
select /*+ ordered
index(prg PJI_XBS_DENORM_N1) */
prg.SUP_PROJECT_ID,
prg.SUP_ID
from
PJI_PJP_WBS_HEADER sub_wbs_hdr,
PA_PROJ_ELEM_VER_STRUCTURE ppevs2,
PJI_XBS_DENORM prg
where
prg.struct_version_id is null and
sub_wbs_hdr.PLAN_VERSION_ID = p_plan_version_id and
sub_wbs_hdr.PROJECT_ID = p_project_id and
sub_wbs_hdr.WBS_VERSION_ID = prg.SUB_ID and
prg.STRUCT_TYPE = 'PRG' and
nvl(prg.RELATIONSHIP_TYPE, 'WF') in ('LW', 'WF') and
sub_wbs_hdr.WP_FLAG = 'Y' and
ppevs2.PROJECT_ID = sub_wbs_hdr.PROJECT_ID and
ppevs2.ELEMENT_VERSION_ID = sub_wbs_hdr.WBS_VERSION_ID and
ppevs2.LATEST_EFF_PUBLISHED_FLAG = 'Y'
) and
sup_wbs_hdr.WP_FLAG = 'Y' and
sup_wbs_hdr.LOCK_FLAG is null and
exists
(
select
1
from
PA_PROJ_ELEM_VER_STRUCTURE ppevs1
where
ppevs1.PROJECT_ID = sup_wbs_hdr.PROJECT_ID and
ppevs1.ELEMENT_VERSION_ID = sup_wbs_hdr.WBS_VERSION_ID and
ppevs1.LATEST_EFF_PUBLISHED_FLAG = 'Y'
)
for update nowait;
select /*+ use_nl(sup_wbs_hdr)
index(sup_wbs_hdr PJI_PJP_WBS_HEADER_N1) */
sup_wbs_hdr.PROJECT_ID,
sup_wbs_hdr.PLAN_VERSION_ID,
sup_wbs_hdr.PLAN_TYPE_ID
from
PJI_PJP_WBS_HEADER sup_wbs_hdr
where
(sup_wbs_hdr.PROJECT_ID,
sup_wbs_hdr.WBS_VERSION_ID,
sup_wbs_hdr.PLAN_VERSION_ID,
sup_wbs_hdr.PLAN_TYPE_ID,
sup_wbs_hdr.PLAN_TYPE_CODE) in
(
select /*+ ordered
index(prg PJI_XBS_DENORM_N1) */
prg.SUP_PROJECT_ID,
prg.SUP_ID,
ver.BUDGET_VERSION_ID,
ver.FIN_PLAN_TYPE_ID,
sub_wbs_hdr.PLAN_TYPE_CODE
from
PA_BUDGET_VERSIONS ver,
PJI_PJP_WBS_HEADER sub_wbs_hdr,
PJI_XBS_DENORM prg
where
ver.BUDGET_VERSION_ID = p_plan_version_id and
ver.PROJECT_ID = sub_wbs_hdr.PROJECT_ID and
ver.FIN_PLAN_TYPE_ID = sub_wbs_hdr.PLAN_TYPE_ID and
decode(ver.VERSION_TYPE,
'COST', 'C',
'REVENUE', 'R',
'A') = sub_wbs_hdr.PLAN_TYPE_CODE and
sub_wbs_hdr.PLAN_VERSION_ID in (-3, -4) and
sub_wbs_hdr.WBS_VERSION_ID = prg.SUB_ID and
prg.STRUCT_TYPE = 'PRG' and
prg.STRUCT_VERSION_ID is null and
nvl(prg.RELATIONSHIP_TYPE, 'WF') in ('LF', 'WF')
)
for update nowait;
IF (p_context NOT IN ('DELETE', 'UPDATE', 'BASELINE', 'ORIGINAL', 'COPY')) THEN -- 'PUBLISH', 'STRUCT_CHANGE',
print_time (' WBS_HEADERS_LOCK: The following value of p_context is invalid: ' || p_context);
SELECT /*+ index_ffs(wbs_hdr PJI_PJP_WBS_HEADER_N1) */ l_count+1
INTO l_count
FROM pji_pjp_wbs_header wbs_hdr
WHERE plan_version_id = p_fp_version_ids(i);
SELECT l_count+1
INTO l_count
FROM pji_pjp_wbs_header
WHERE plan_version_id = p_fp_version_ids(i)
AND lock_flag IS NULL;
IF (p_context NOT IN ('BASELINE', 'DELETE')) THEN
-- Get the plan version properties: is it wp/fp, is it versioning enabled
-- , is the structure working or published, is it latest pub, is it the baselined version?
SELECT DISTINCT
wh.plan_version_id pv
, wh.project_id pi
, wh.wp_flag wf
, ppwa.wp_enable_version_flag vef
, DECODE(ppevs.status_code, 'STRUCTURE_PUBLISHED', 'P', 'STRUCTURE_WORKING', 'W') pf
, ppevs.latest_eff_published_flag lpf
, DECODE(wh.cb_flag, 'Y', 'B', 'W') bf
INTO
l_fp_version_ids(l_count)
, l_project_ids(l_count)
, l_wp_flags(l_count)
, l_vers_enabled_flags(l_count)
, l_published_flags(l_count)
, l_latest_pub_flags(l_count)
, l_baselined_flags(l_count)
FROM PA_PROJ_WORKPLAN_ATTR ppwa
, PA_PROJ_ELEMENTS ppe
, PA_PROJ_STRUCTURE_TYPES ppst
, PA_STRUCTURE_TYPES pst
, PA_PROJ_ELEM_VER_STRUCTURE ppevs
, pji_pjp_wbs_header wh
-- , pa_budget_versions bv -- can't depend on bv in plan delete flow.
WHERE 1=1
AND ppe.project_id = ppwa.project_id
AND ppe.proj_element_id = ppwa.proj_element_id
AND ppe.proj_element_id = ppst.proj_element_id
AND ppe.object_type = 'PA_STRUCTURES' -- Added for perf improvement bug 6430959
AND ppst.structure_type_id = pst.structure_type_id
AND pst.structure_type_class_code = DECODE (wh.wp_flag, 'Y', 'WORKPLAN', 'FINANCIAL')
AND ppevs.project_id = ppe.project_id
AND ppevs.project_id = wh.project_id
AND ppevs.element_version_id = wh.wbs_version_id
AND ppevs.status_code IN ('STRUCTURE_PUBLISHED', 'STRUCTURE_WORKING')
AND wh.plan_version_id = p_fp_version_ids(i) ;
IF ( (p_context = 'UPDATE')
AND (l_fp_version_ids(l_count) > 0)) THEN -- 2
IF ( -- 3
(
( l_wp_flags(l_count) = 'Y' )
AND (
( l_published_flags(l_count) = 'N' )
OR ( l_latest_pub_flags(l_count) = 'Y' )
OR ( l_vers_enabled_flags(l_count) = 'N' )
)
)
OR
(
( l_wp_flags(l_count) = 'N' )
AND ( l_baselined_flags(l_count) = 'N')
)
) THEN
IF ( -- 4
( l_wp_flags(l_count) = 'Y' )
AND (
( l_latest_pub_flags(l_count) = 'Y' )
OR ( l_vers_enabled_flags(l_count) = 'N' )
)
) THEN
l_num_locked := 0;
SELECT COUNT(1)
INTO l_num_locked
FROM pji_xbs_denorm den
, pji_pjp_wbs_header hd1 -- SUP
, pji_pjp_wbs_header hd2 -- SUB
, PA_PROJ_ELEM_VER_STRUCTURE ppevs1
, PA_PROJ_ELEM_VER_STRUCTURE ppevs2
WHERE
den.struct_version_id IS NULL
AND hd2.plan_version_id = l_fp_version_ids(l_count)
AND hd2.project_id = l_project_ids(l_count)
AND hd2.plan_type_id = hd1.plan_type_id
AND hd2.wbs_version_id = den.sub_id -- struct_version_id
AND hd1.wbs_version_id = den.sup_id
AND den.struct_type = 'PRG'
AND NVL(den.relationship_type, 'WF') IN ('LW', 'WF') -- 'LW',
AND hd1.wp_flag = 'Y'
AND ppevs1.element_version_id = hd1.wbs_version_id
AND ppevs1.project_id = hd1.project_id
AND ppevs1.latest_eff_published_flag = 'Y'
AND hd2.wp_flag = 'Y'
AND ppevs2.project_id = hd2.project_id
AND ppevs2.element_version_id = hd2.wbs_version_id
AND ppevs2.latest_eff_published_flag = 'Y'
AND hd1.lock_flag IS NOT NULL;
OPEN c_wh_lock_update_cur (
p_plan_version_id => l_fp_version_ids(l_count)
, p_project_id => l_project_ids(l_count));
CLOSE c_wh_lock_update_cur;
SELECT COUNT(1)
INTO l_num_locked
FROM pji_pjp_wbs_header
WHERE plan_version_id = l_fp_version_ids(l_count)
AND lock_flag IS NOT NULL;
ELSIF ( (p_context IN ('COPY', 'DELETE'))
AND (l_fp_version_ids(l_count) > 0) ) THEN -- 2
-- Only working FPs/WPs can be deleted.
SELECT COUNT(1)
INTO l_num_locked
FROM pji_pjp_wbs_header
WHERE plan_version_id = l_fp_version_ids(l_count)
AND lock_flag IS NOT NULL;
SELECT COUNT(1)
INTO l_num_locked
FROM pji_pjp_wbs_header
WHERE plan_version_id = l_fp_version_ids(l_count)
AND lock_flag IS NOT NULL;
SELECT PA_PROJECT_STRUCTURE_UTILS.GET_FIN_STRUC_VER_ID(bv.project_id)
INTO l_wbs_version_id
FROM pa_budget_versions bv
WHERE budget_version_id = l_fp_version_ids(l_count);
INSERT INTO PJI_FM_EXTR_PLNVER3_T ver3
(
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
den.sup_project_id project_id
, cbco.plan_version_id -- bv.budget_version_id -- -3 --
, 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
, 'Y' -- baselined flag.
, bv.SECONDARY_RBS_FLAG
, bv.lp_flag
FROM pji_fm_extr_plnver3_T 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 = l_fp_version_ids(l_count)
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') -- Excluding 'LW'
;
DELETE FROM pji_fm_extr_plnver3_t
WHERE plan_version_id > 0;
print_time(' Need only -3, -4 records, deleted other plan version. ');
print_time ( ' Inserted -3, -4 RBS headers inserted. ' );
print_time ( ' Inserted -3, -4 WBS headers inserted. ' );
SELECT COUNT(1)
INTO l_count1
FROM pji_xbs_denorm den
, pji_pjp_wbs_header hd1 -- SUB
, pji_pjp_wbs_header hd2 -- SUP
, pa_budget_versions hd3 -- to get plan type id
WHERE
den.struct_version_id IS NULL
AND den.struct_type = 'PRG'
AND hd1.wbs_version_id = den.sub_id -- struct_version_id
AND hd2.wbs_version_id = den.sup_id
-- AND den.sup_level <= den.sub_level
AND hd3.budget_version_id = l_fp_version_ids(l_count)
AND hd3.project_id = hd1.project_id
AND hd3.fin_plan_type_id = hd2.plan_type_id
AND hd3.fin_plan_type_id = hd1.plan_type_id
AND DECODE(hd3.version_type,'COST','C','REVENUE','R','A') = hd1.plan_type_code
AND hd1.plan_type_code = hd2.plan_type_code /*4471527*/
AND NVL(den.relationship_type, 'WF') IN ('LF', 'WF') -- Excluding 'LW'
AND hd1.plan_version_id IN (-3, -4)
AND hd2.plan_version_id IN (-3, -4)
AND hd1.plan_version_id = hd2.plan_version_id
AND hd2.lock_flag IS NOT NULL;
SELECT project_id
FROM pji_fm_extr_plan_lines
WHERE ROWID IN
( SELECT extr_lines_rowid
FROM pji_fp_rmap_fpr_update_t)
FOR UPDATE NOWAIT;
PROCEDURE GET_HDRS_TOLOCK_FOR_UPDATE (
x_fp_version_ids OUT NOCOPY SYSTEM.pa_num_tbl_type -- := SYSTEM.pa_num_tbl_type(),
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_code OUT NOCOPY VARCHAR2 ) IS
l_fp_version_ids SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
SELECT DISTINCT plan_version_id
FROM PJI_FM_EXTR_PLAN_LINES
WHERE ROWID IN
( SELECT extr_lines_rowid
FROM pji_fp_rmap_fpr_update_t);
, p_procedure_name => 'GET_HDRS_TOLOCK_FOR_UPDATE '
, x_return_status => x_return_status ) ;
SELECT event_id
FROM pa_pji_proj_events_log
WHERE event_object = p_event_object
AND operation_type = p_operation_type;
PROCEDURE DELETE_EVENTS (
p_event_ids IN SYSTEM.pa_num_tbl_type
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_code OUT NOCOPY VARCHAR2 ) IS
BEGIN
Pji_Pjp_Fp_Curr_Wrap.INIT_ERR_STACK
( p_package_name => g_package_name
, x_return_status => x_return_status );
print_time ( ' delete events 001 ' );
DELETE FROM pa_pji_proj_events_log
WHERE event_id = p_event_ids(i);
print_time ( ' delete events 002 ' );
, p_procedure_name => 'DELETE_EVENTS'
, x_return_status => x_return_status ) ;
SELECT pji_summarized_flag
INTO l_pji_summarized_flag
FROM pa_budget_versions
WHERE budget_version_id = p_source_fp_version_ids(i);
UPDATE pa_budget_versions
SET pji_summarized_flag = l_pji_summarized_flag
WHERE budget_version_id = p_dest_fp_version_ids(i);
UPDATE pa_budget_versions
SET pji_summarized_flag = 'Y'
WHERE budget_version_id = p_dest_fp_version_ids(i);
UPDATE pa_budget_versions
SET original_flag = 'Y'
, last_update_date = SYSDATE
, last_updated_by = Fnd_Global.USER_ID
, last_update_login = Fnd_Global.LOGIN_ID
WHERE budget_version_id = p_original_version_id ;
l_last_update_date DATE := SYSDATE;
l_last_updated_by NUMBER := Fnd_Global.USER_ID;
l_last_update_login NUMBER := Fnd_Global.LOGIN_ID;
INSERT INTO pji_fp_xbs_accum_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
, 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 /*4471527 */
)
(
SELECT
rl.PROJECT_ID
, rl.PROJECT_ORG_ID
, rl.PROJECT_ORGANIZATION_ID
, rl.PROJECT_ELEMENT_ID
, rl.TIME_ID
, rl.PERIOD_TYPE_ID
, rl.CALENDAR_TYPE
, rl.RBS_AGGR_LEVEL
, rl.WBS_ROLLUP_FLAG
, rl.PRG_ROLLUP_FLAG
, rl.CURR_RECORD_TYPE_ID
, rl.CURRENCY_CODE
, rl.RBS_ELEMENT_ID
, rl.RBS_VERSION_ID
, p_plan_version_id
, rl.PLAN_TYPE_ID
, l_last_update_date
, l_last_updated_by
, l_creation_date
, l_created_by
, l_last_update_login
, rl.RAW_COST
, rl.BRDN_COST
, rl.REVENUE
, rl.BILL_RAW_COST
, rl.BILL_BRDN_COST
, rl.BILL_LABOR_RAW_COST
, rl.BILL_LABOR_BRDN_COST
, rl.BILL_LABOR_HRS
, rl.EQUIPMENT_RAW_COST
, rl.EQUIPMENT_BRDN_COST
, rl.CAPITALIZABLE_RAW_COST
, rl.CAPITALIZABLE_BRDN_COST
, rl.LABOR_RAW_COST
, rl.LABOR_BRDN_COST
, rl.LABOR_HRS
, rl.LABOR_REVENUE
, rl.EQUIPMENT_HOURS
, rl.BILLABLE_EQUIPMENT_HOURS
, rl.SUP_INV_COMMITTED_COST
, rl.PO_COMMITTED_COST
, rl.PR_COMMITTED_COST
, rl.OTH_COMMITTED_COST
, rl.ACT_LABOR_HRS
, rl.ACT_EQUIP_HRS
, rl.ACT_LABOR_BRDN_COST
, rl.ACT_EQUIP_BRDN_COST
, rl.ACT_BRDN_COST
, rl.ACT_RAW_COST
, rl.ACT_REVENUE
, rl.ACT_LABOR_RAW_COST
, rl.ACT_EQUIP_RAW_COST
, rl.ETC_LABOR_HRS
, rl.ETC_EQUIP_HRS
, rl.ETC_LABOR_BRDN_COST
, rl.ETC_EQUIP_BRDN_COST
, rl.ETC_BRDN_COST
, rl.ETC_RAW_COST
, rl.ETC_LABOR_RAW_COST
, rl.ETC_EQUIP_RAW_COST
, rl.CUSTOM1
, rl.CUSTOM2
, rl.CUSTOM3
, rl.CUSTOM4
, rl.CUSTOM5
, rl.CUSTOM6
, rl.CUSTOM7
, rl.CUSTOM8
, rl.CUSTOM9
, rl.CUSTOM10
, rl.CUSTOM11
, rl.CUSTOM12
, rl.CUSTOM13
, rl.CUSTOM14
, rl.CUSTOM15
, rl.PLAN_TYPE_CODE /*4471527 */
FROM
pji_fp_xbs_accum_f rl
, pji_pjp_wbs_header wh
WHERE rl.project_id = wh.project_id
AND rl.project_id = p_project_id
AND rl.plan_type_id = p_plan_type_id
AND wh.plan_version_id = rl.plan_version_id
AND wh.plan_type_code = rl.plan_type_code /*4471527 */
AND DECODE(p_plan_version_id,
-3, wh.cb_flag
-4, wh.co_flag) = 'Y'
);
SELECT
head.PLAN_VERSION_ID
FROM
pji_pjp_wbs_header head,
PA_PJI_PROJ_EVENTS_LOG elog
WHERE
elog.EVENT_ID = l_event_id AND
head.PROJECT_ID = TO_NUMBER(elog.ATTRIBUTE1) AND
head.WBS_VERSION_ID = TO_NUMBER(elog.ATTRIBUTE2) AND
head.PLAN_VERSION_ID = TO_NUMBER(elog.ATTRIBUTE3) AND
head.LOCK_FLAG IS NULL
FOR UPDATE NOWAIT;
SELECT
head.PLAN_VERSION_ID
FROM
pji_pjp_wbs_header head,
PA_PJI_PROJ_EVENTS_LOG elog
WHERE
elog.EVENT_ID = l_event_id AND
head.PROJECT_ID = TO_NUMBER(elog.ATTRIBUTE1) AND
head.WBS_VERSION_ID = TO_NUMBER(elog.ATTRIBUTE2) AND
head.PLAN_VERSION_ID = TO_NUMBER(elog.ATTRIBUTE3) AND
-- elog.EVENT_TYPE = 'WBS_CHANGE' AND
-- need to process both wbs change and publish.
-- publish needs to change wbs version id for fin plans.
head.LOCK_FLAG IS NULL
FOR UPDATE NOWAIT;
select /*+ use_nl(sup_wbs_hdr)
index(sup_wbs_hdr PJI_PJP_WBS_HEADER_N1) */
sup_wbs_hdr.PLAN_VERSION_ID
from
PJI_PJP_WBS_HEADER sup_wbs_hdr
where
(sup_wbs_hdr.PROJECT_ID,
sup_wbs_hdr.WBS_VERSION_ID) in
(
select /*+ ordered
index(prg PJI_XBS_DENORM_N1) */
prg.SUP_PROJECT_ID,
prg.SUP_ID
from
PA_PJI_PROJ_EVENTS_LOG log,
PJI_PJP_WBS_HEADER sub_wbs_hdr,
PA_PROJ_ELEM_VER_STRUCTURE ppevs2,
PJI_XBS_DENORM prg
where
prg.STRUCT_VERSION_ID is null and
sub_wbs_hdr.WBS_VERSION_ID = prg.SUB_ID and
sup_wbs_hdr.WBS_VERSION_ID = prg.SUP_ID and
nvl(prg.SUB_ROLLUP_ID,
prg.SUP_EMT_ID) <> prg.SUP_EMT_ID and
prg.STRUCT_TYPE = 'PRG' and
nvl(prg.RELATIONSHIP_TYPE, 'WF') in ('LW', 'WF') and
sub_wbs_hdr.WP_FLAG = 'Y' and
ppevs2.PROJECT_ID = sub_wbs_hdr.PROJECT_ID and
ppevs2.ELEMENT_VERSION_ID = sub_wbs_hdr.WBS_VERSION_ID and
ppevs2.STATUS_CODE = 'STRUCTURE_PUBLISHED' and
sub_wbs_hdr.LOCK_FLAG is not null and
sub_wbs_hdr.PROJECT_ID = to_number(log.ATTRIBUTE1) and
sub_wbs_hdr.WBS_VERSION_ID = to_number(log.ATTRIBUTE2) and
sub_wbs_hdr.PLAN_VERSION_ID = to_number(log.ATTRIBUTE3) and
log.EVENT_TYPE = 'WBS_PUBLISH' and
log.EVENT_ID = l_event_id
) and
sup_wbs_hdr.WP_FLAG = 'Y' and
sup_wbs_hdr.LOCK_FLAG is not null and
exists
(
select
1
from
PA_PROJ_ELEM_VER_STRUCTURE ppevs1
where
ppevs1.PROJECT_ID = sup_wbs_hdr.PROJECT_ID and
ppevs1.ELEMENT_VERSION_ID = sup_wbs_hdr.WBS_VERSION_ID and
ppevs1.STATUS_CODE = 'STRUCTURE_WORKING'
)
for update nowait;
select /*+ use_nl(sup_wbs_hdr) index(sup_wbs_hdr PJI_PJP_WBS_HEADER_N1) */
sup_wbs_hdr.PROJECT_ID,
sup_wbs_hdr.PLAN_VERSION_ID,
sup_wbs_hdr.PLAN_TYPE_ID
from
PJI_PJP_WBS_HEADER sup_wbs_hdr
where
(sup_wbs_hdr.PROJECT_ID,
sup_wbs_hdr.WBS_VERSION_ID,
sup_wbs_hdr.PLAN_VERSION_ID,
sup_wbs_hdr.PLAN_TYPE_ID,
sup_wbs_hdr.PLAN_TYPE_CODE) in
(
select /*+ ordered
index(prg PJI_XBS_DENORM_N1) */
prg.SUP_PROJECT_ID,
prg.SUP_ID,
sub_wbs_hdr.PLAN_VERSION_ID,
sub_wbs_hdr.PLAN_TYPE_ID,
sub_wbs_hdr.PLAN_TYPE_CODE
from
PA_PJI_PROJ_EVENTS_LOG log,
PJI_PJP_WBS_HEADER wbs_hdr,
PJI_PJP_WBS_HEADER sub_wbs_hdr,
PJI_XBS_DENORM prg
where
prg.STRUCT_VERSION_ID is null and
sub_wbs_hdr.WBS_VERSION_ID = prg.SUB_ID and
prg.STRUCT_TYPE = 'PRG' and
nvl(prg.RELATIONSHIP_TYPE, 'WF') in ('LF', 'WF') and
sub_wbs_hdr.PLAN_VERSION_ID in (-3, -4) and
wbs_hdr.PROJECT_ID = sub_wbs_hdr.PROJECT_ID and
wbs_hdr.PLAN_TYPE_ID = sub_wbs_hdr.PLAN_TYPE_ID and
wbs_hdr.PLAN_TYPE_CODE = sub_wbs_hdr.PLAN_TYPE_CODE and
wbs_hdr.LOCK_FLAG is not null and
wbs_hdr.PROJECT_ID = to_number(log.ATTRIBUTE1) and
wbs_hdr.WBS_VERSION_ID = to_number(log.ATTRIBUTE2) and
wbs_hdr.PLAN_VERSION_ID in (-3, -4) and
log.EVENT_TYPE = 'WBS_PUBLISH' and
log.EVENT_ID = l_event_id
)
for update nowait;
SELECT
head.PLAN_VERSION_ID
FROM
pji_pjp_wbs_header head,
PA_PJI_PROJ_EVENTS_LOG elog
WHERE
head.PROJECT_ID = TO_NUMBER(elog.ATTRIBUTE1) AND
head.WBS_VERSION_ID = TO_NUMBER(elog.ATTRIBUTE2) AND
head.PLAN_VERSION_ID = TO_NUMBER(elog.ATTRIBUTE3) AND
elog.EVENT_TYPE IN ('WBS_CHANGE', 'WBS_PUBLISH') AND
head.LOCK_FLAG IS NULL
FOR UPDATE;
SELECT
head.PLAN_VERSION_ID
FROM
pji_pjp_wbs_header head,
PA_PJI_PROJ_EVENTS_LOG elog
WHERE
head.PROJECT_ID = TO_NUMBER(elog.ATTRIBUTE1) AND
head.WBS_VERSION_ID = TO_NUMBER(elog.ATTRIBUTE2) AND
head.PLAN_VERSION_ID = TO_NUMBER(elog.ATTRIBUTE3) AND
elog.EVENT_TYPE = 'WBS_CHANGE' AND
head.LOCK_FLAG IS NULL
FOR UPDATE;
SELECT hd1.plan_version_id
FROM pji_pjp_wbs_header hd1 -- SUP
WHERE EXISTS (
SELECT 1
FROM pji_xbs_denorm den
, pji_pjp_wbs_header hd2 -- SUB
, PA_PROJ_WORKPLAN_ATTR ppwa1
, PA_PROJ_ELEMENTS ppe1
, PA_PROJ_STRUCTURE_TYPES ppst1
, PA_STRUCTURE_TYPES pst1
, PA_PROJ_ELEM_VER_STRUCTURE ppevs1
, PA_PROJ_WORKPLAN_ATTR ppwa2
, PA_PROJ_ELEMENTS ppe2
, PA_PROJ_STRUCTURE_TYPES ppst2
, PA_STRUCTURE_TYPES pst2
, PA_PROJ_ELEM_VER_STRUCTURE ppevs2
, PA_PJI_PROJ_EVENTS_LOG elog
WHERE
den.struct_version_id IS NULL
AND hd2.wbs_version_id = den.sub_id -- struct_version_id
AND hd1.wbs_version_id = den.sup_id
AND NVL(den.sub_rollup_id, den.sup_emt_id) <> den.sup_emt_id
AND den.struct_type = 'PRG'
AND NVL(den.relationship_type, 'WF') IN ('LW', 'WF') -- 'LW',
AND hd1.wp_flag = 'Y'
AND ppe1.project_id = ppwa1.project_id
AND ppe1.proj_element_id = ppwa1.proj_element_id
AND ppe1.proj_element_id = ppst1.proj_element_id
AND ppst1.structure_type_id = pst1.structure_type_id
AND pst1.structure_type_class_code = 'WORKPLAN'
AND ppevs1.project_id = ppe1.project_id
AND ppevs1.project_id = hd1.project_id
AND ppevs1.element_version_id = hd1.wbs_version_id
AND ppevs1.status_code = 'STRUCTURE_WORKING'
AND hd1.lock_flag IS NOT NULL
AND hd2.wp_flag = 'Y'
AND ppe2.project_id = ppwa2.project_id
AND ppe2.proj_element_id = ppwa2.proj_element_id
AND ppe2.proj_element_id = ppst2.proj_element_id
AND ppst2.structure_type_id = pst2.structure_type_id
AND pst2.structure_type_class_code = 'WORKPLAN'
AND ppevs2.project_id = ppe2.project_id
AND ppevs2.project_id = hd2.project_id
AND ppevs2.element_version_id = hd2.wbs_version_id
-- AND ppwa2.wp_enable_version_flag = 'N' -- Todo: to consider version disabled case.
AND ppevs2.status_code = 'STRUCTURE_PUBLISHED'
AND hd2.lock_flag IS NOT NULL
AND hd2.PROJECT_ID = TO_NUMBER(elog.ATTRIBUTE1)
AND hd2.WBS_VERSION_ID = TO_NUMBER(elog.ATTRIBUTE2)
AND hd2.PLAN_VERSION_ID = TO_NUMBER(elog.ATTRIBUTE3)
AND elog.EVENT_TYPE = 'WBS_PUBLISH'
)
FOR UPDATE;
SELECT
TO_NUMBER(elog.ATTRIBUTE1),
TO_NUMBER(elog.EVENT_OBJECT)
INTO
l_project_id,
l_struct_version_id
FROM
pa_pji_proj_events_log elog
WHERE
elog.EVENT_ID = p_event_id AND
ROWNUM <= 1;
/* UPDATE pji_pjp_wbs_header head
SET LOCK_FLAG = 'P'
WHERE
head.PLAN_VERSION_ID IN (SELECT
TO_NUMBER(elog.ATTRIBUTE3)
FROM
pa_pji_proj_events_log elog
WHERE
head.PROJECT_ID = TO_NUMBER(elog.ATTRIBUTE1) AND
head.WBS_VERSION_ID = TO_NUMBER(elog.ATTRIBUTE2) AND
head.PLAN_VERSION_ID = TO_NUMBER(elog.ATTRIBUTE3) AND
elog.EVENT_TYPE IN ('WBS_CHANGE', 'WBS_PUBLISH'));*/
SELECT project_id, plan_version_id
FROM pji_pjp_wbs_header
WHERE 1 = 1
AND wbs_version_id = p_old_struct_ver_id
AND plan_version_id > 0;
SELECT
TO_NUMBER(elog.ATTRIBUTE1),
TO_NUMBER(elog.EVENT_OBJECT),
TO_NUMBER(elog.ATTRIBUTE2)
INTO
l_project_id,
l_new_wbs_version_id,
l_old_wbs_version_id
FROM
pa_pji_proj_events_log elog
WHERE
elog.EVENT_ID = p_event_id AND
ROWNUM <= 1;
select
PLAN_VERSION_ID
into
l_plan_version_id
from
pji_pjp_wbs_header
where
PROJECT_ID = l_project_id AND
WBS_VERSION_ID = l_new_wbs_version_id AND
WP_FLAG = 'Y';
select 1
into l_prg_event_id
from dual
where exists (select log.event_id
from pji_pa_proj_events_log log,pa_proj_element_versions ver
where log.event_type='PRG_CHANGE'
and log.event_object =to_char(ver.prg_group)
and ver.project_id=l_project_id
union all
select log.event_id
from pa_pji_proj_events_log log,pa_proj_element_versions ver
where log.event_type='PRG_CHANGE'
and log.event_object =to_char(ver.prg_group)
and ver.project_id=l_project_id);
select 1
into l_prg_event_id
from dual
where exists (select log.event_id
from pa_pji_proj_events_log log,pa_proj_element_versions ver
where log.event_type='PRG_CHANGE'
and log.event_object =to_char(ver.prg_group)
and ver.project_id=l_project_id);
UPDATE pji_pjp_wbs_header SET
wbs_version_id = l_new_wbs_version_id
WHERE
wp_flag = 'N' AND
project_id = l_project_id;
UPDATE pji_pjp_wbs_header SET
wbs_version_id = l_new_wbs_version_id
WHERE
wp_flag = 'N' AND
p_struct_type = 'FINANCIAL' AND
project_id = l_project_id;
UPDATE pji_fm_extr_plan_lines
SET ACT_QUANTITY = NULL,
ACT_TXN_BURDENED_COST = NULL,
ACT_PRJ_BURDENED_COST = NULL,
ACT_PFC_BURDENED_COST = NULL,
ACT_TXN_RAW_COST = NULL,
ACT_PRJ_RAW_COST = NULL,
ACT_PFC_RAW_COST = NULL,
ACT_TXN_REVENUE = NULL,
ACT_PRJ_REVENUE = NULL,
ACT_PFC_REVENUE = NULL
WHERE
plan_version_id = l_plan_version_id;
pji_fm_xbs_accum_maint.plan_update (
p_plan_version_id => l_plan_version_id,
x_msg_code => l_msg_data,
x_return_status => l_return_status );
Pji_Utils.write2log('WBS Maint Pvt:' || 'Failure in plan_update call');
savepoint process_wbs_updates_conc;
savepoint process_wbs_updates;
elsif l_chd_dev_status in ('CANCELLED','TERMINATED','DELETED') THEN
l_return_status:='E';
Pji_Pjp_Sum_Rollup.update_xbs_denorm;
UPDATE pji_pjp_wbs_header SET
wbs_version_id = l_new_wbs_version_id
WHERE
wp_flag = 'N' AND
project_id = l_project_id;
UPDATE pji_pjp_wbs_header SET
wbs_version_id = l_new_wbs_version_id
WHERE
wp_flag = 'N' AND
p_struct_type = 'FINANCIAL' AND
project_id = l_project_id;
Pa_Proj_Task_Struc_Pub.set_update_wbs_flag (
p_project_id => l_project_id,
p_structure_version_id => l_new_wbs_version_id,
p_update_wbs_flag => 'N',
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data );
/* 5138049 as updated by MAANSARI
Pa_Proj_Task_Struc_Pub.process_task_weightage (
p_project_id => l_project_id,
p_structure_version_id => l_new_wbs_version_id,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data );
DELETE
FROM pa_pji_proj_events_log LOG
WHERE LOG.EVENT_ID = p_event_id;
UPDATE pji_fm_extr_plan_lines
SET ACT_QUANTITY = NULL,
ACT_TXN_BURDENED_COST = NULL,
ACT_PRJ_BURDENED_COST = NULL,
ACT_PFC_BURDENED_COST = NULL,
ACT_TXN_RAW_COST = NULL,
ACT_PRJ_RAW_COST = NULL,
ACT_PFC_RAW_COST = NULL,
ACT_TXN_REVENUE = NULL,
ACT_PRJ_REVENUE = NULL,
ACT_PFC_REVENUE = NULL
WHERE
plan_version_id = l_plan_version_id;
pji_fm_xbs_accum_maint.plan_update (
p_plan_version_id => l_plan_version_id,
x_msg_code => l_msg_data,
x_return_status => l_return_status );
Pji_Utils.write2log('WBS Maint Pvt:' || 'Failure in plan_update call');
savepoint process_wbs_updates_conc;
savepoint process_wbs_updates;
elsif l_chd_dev_status in ('CANCELLED','TERMINATED','DELETED') THEN
l_return_status:='E';
SELECT
TO_NUMBER(elog.ATTRIBUTE1),
TO_NUMBER(elog.EVENT_OBJECT)
INTO
l_project_id,
l_struct_version_id
FROM
pa_pji_proj_events_log elog
WHERE
elog.EVENT_ID = p_event_id AND
ROWNUM <= 1;
/*UPDATE pa_proj_elem_ver_structure
SET PROCESS_CODE = 'WUE',
CONC_REQUEST_ID = l_request_id
WHERE ELEMENT_VERSION_ID = l_struct_version_id
AND PROJECT_ID = l_project_id;
SELECT
typ.STRUCTURE_TYPE
INTO
l_struct_type
FROM
pa_structure_types typ,
pa_proj_structure_types ptyp,
pa_proj_element_versions ver
WHERE
typ.STRUCTURE_TYPE_ID = ptyp.STRUCTURE_TYPE_ID AND
ptyp.PROJ_ELEMENT_ID = ver.PROJ_ELEMENT_ID AND
ver.ELEMENT_VERSION_ID = p_new_struct_ver_id ; --p_old_struct_ver_id; fix for bug : 4191390
SELECT pa_pji_proj_events_log_s.NEXTVAL
INTO l_event_id
FROM sys.dual;
INSERT INTO pa_pji_proj_events_log (
EVENT_TYPE,
EVENT_ID,
EVENT_OBJECT,
OPERATION_TYPE,
STATUS,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3 )
SELECT
DECODE (p_publish_flag, 'N', 'WBS_CHANGE', 'WBS_PUBLISH'),
l_event_id,
p_new_struct_ver_id,
'I',
'X',
SYSDATE,
-1,
SYSDATE,
-1,
-1,
p_project_id,
p_old_struct_ver_id,
head.PLAN_VERSION_ID
FROM
pji_pjp_wbs_header head
WHERE
head.PROJECT_ID = p_project_id AND
head.WBS_VERSION_ID = p_old_struct_ver_id;
INSERT INTO pa_pji_proj_events_log (
EVENT_TYPE,
EVENT_ID,
EVENT_OBJECT,
OPERATION_TYPE,
STATUS,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3 )
SELECT
DECODE (p_publish_flag, 'N', 'WBS_CHANGE', 'WBS_PUBLISH'),
l_event_id,
p_new_struct_ver_id,
'I',
'X',
SYSDATE,
-1,
SYSDATE,
-1,
-1,
p_project_id,
p_old_struct_ver_id,
NULL
FROM dual;
INSERT INTO pa_pji_proj_events_log (
EVENT_TYPE,
EVENT_ID,
EVENT_OBJECT,
OPERATION_TYPE,
STATUS,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3 )
SELECT
DECODE (p_publish_flag, 'N', 'WBS_CHANGE', 'WBS_PUBLISH'),
l_event_id,
p_new_struct_ver_id,
'I',
'X',
SYSDATE,
-1,
SYSDATE,
-1,
-1,
p_project_id,
p_old_struct_ver_id,
head.PLAN_VERSION_ID
FROM
pji_pjp_wbs_header head
WHERE
head.PROJECT_ID = p_project_id AND
head.WBS_VERSION_ID = p_old_struct_ver_id AND
head.WP_FLAG = 'Y';
INSERT INTO pa_pji_proj_events_log (
EVENT_TYPE,
EVENT_ID,
EVENT_OBJECT,
OPERATION_TYPE,
STATUS,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3 )
SELECT
DECODE (p_publish_flag, 'N', 'WBS_CHANGE', 'WBS_PUBLISH'),
l_event_id,
p_new_struct_ver_id,
'I',
'X',
SYSDATE,
-1,
SYSDATE,
-1,
-1,
p_project_id,
p_old_struct_ver_id,
NULL
FROM dual;
INSERT INTO pa_pji_proj_events_log (
EVENT_TYPE,
EVENT_ID,
EVENT_OBJECT,
OPERATION_TYPE,
STATUS,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3 )
SELECT
'WBS_PUBLISH',
l_event_id,
p_new_struct_ver_id,
'I',
'X',
SYSDATE,
-1,
SYSDATE,
-1,
-1,
p_project_id,
p_old_struct_ver_id,
head.PLAN_VERSION_ID
FROM
pji_pjp_wbs_header head
WHERE
head.PROJECT_ID = p_project_id AND
head.WBS_VERSION_ID = p_old_struct_ver_id AND
head.WP_FLAG = 'N';
INSERT INTO pa_pji_proj_events_log (
EVENT_TYPE,
EVENT_ID,
EVENT_OBJECT,
OPERATION_TYPE,
STATUS,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3 )
SELECT
DECODE (p_publish_flag, 'N', 'WBS_CHANGE', 'WBS_PUBLISH'),
l_event_id,
p_new_struct_ver_id,
'I',
'X',
SYSDATE,
-1,
SYSDATE,
-1,
-1,
p_project_id,
p_old_struct_ver_id,
NULL
FROM dual;
select decode(p_calling_context, 'ONLINE_PUBLISH', 'CONC_PUBLISH'
, 'ONLINE_UPDATE', 'CONC_UPDATE', p_calling_context)
into l_calling_context
from dual;
SELECT
TO_NUMBER(elog.ATTRIBUTE1),
DECODE(elog.EVENT_TYPE, 'WBS_PUBLISH', 'Y', 'N'),
elog.EVENT_OBJECT
INTO
l_project_id,
l_publish_flag,
l_struct_version_id
FROM
pa_pji_proj_events_log elog
WHERE
elog.EVENT_ID = p_event_id AND
ROWNUM <= 1;
UPDATE pa_proj_elem_ver_structure
SET PROCESS_CODE =decode(l_calling_context, 'APPLY_PROGRESS', 'APE'
, 'CONC_PUBLISH', 'PUE'
, 'CONC_UPDATE', 'WUE', null),
process_update_wbs_flag = 'Y', /* 5138049 */
CONC_REQUEST_ID = Fnd_Global.CONC_REQUEST_ID
WHERE ELEMENT_VERSION_ID = l_struct_version_id
AND PROJECT_ID = l_project_id;
UPDATE pa_proj_elem_ver_structure
SET PROCESS_CODE =decode(l_calling_context, 'APPLY_PROGRESS', 'APE'
, 'CONC_PUBLISH', 'PUE'
, 'CONC_UPDATE', 'WUE', null),
process_update_wbs_flag = 'Y', /* 5138049 */
CONC_REQUEST_ID = Fnd_Global.CONC_REQUEST_ID
WHERE ELEMENT_VERSION_ID = l_struct_version_id
AND PROJECT_ID = l_project_id;
UPDATE pa_proj_elem_ver_structure
SET PROCESS_CODE = NULL,
process_update_wbs_flag='N',
CONC_REQUEST_ID = Fnd_Global.CONC_REQUEST_ID
WHERE ELEMENT_VERSION_ID = l_struct_version_id
AND PROJECT_ID = l_project_id;
SELECT
DISTINCT
elog.EVENT_TYPE,
elog.EVENT_ID
FROM
pa_pji_proj_events_log elog
WHERE
elog.EVENT_TYPE IN ('WBS_CHANGE',
'WBS_PUBLISH',
'PLAN_DELETE',
'PLAN_BASELINE',
'PLAN_ORIGINAL',
'PLAN_COPY' );
SELECT
TO_NUMBER(elog.ATTRIBUTE1)
INTO
l_project_id
FROM
pa_pji_proj_events_log elog
WHERE
elog.EVENT_ID = p_event_id AND
ROWNUM <= 1;
SELECT
DECODE (p_event_type, 'WBS_PUBLISH', 'Y', 'N')
INTO
l_publish_flag
FROM dual;
ELSIF p_event_type = 'PLAN_DELETE' THEN
PLAN_DELETE_PVT ( p_event_id => p_event_id,
x_processing_code => l_processing_code ,
x_return_status => x_return_status);
PROCEDURE process_pending_plan_updates (
x_return_status OUT NOCOPY VARCHAR2,
x_msg_data OUT NOCOPY VARCHAR2 )
IS
l_processing_code VARCHAR2(1);
, p_procedure_name => 'PROCESS_PENDING_PLAN_UPDATES'
, x_return_status => x_return_status ) ;
SELECT
evt.EVENT_ID,
evt.EVENT_TYPE
FROM
pa_pji_proj_events_log evt
WHERE
evt.EVENT_TYPE IN ( 'PLAN_DELETE',
'PLAN_BASELINE',
'PLAN_ORIGINAL',
'PLAN_COPY') AND
evt.EVENT_OBJECT = to_char(p_plan_version_id)
ORDER BY evt.EVENT_ID ASC;
SELECT
evt.EVENT_ID,
evt.EVENT_TYPE
FROM
pa_pji_proj_events_log evt,
pji_pjp_wbs_header head
WHERE
evt.EVENT_TYPE IN ( 'WBS_CHANGE',
'WBS_PUBLISH') AND
evt.EVENT_OBJECT = to_char(head.WBS_VERSION_ID) AND
head.PLAN_VERSION_ID = p_plan_version_id AND
to_char(head.PROJECT_ID ) = evt.ATTRIBUTE1 AND --Bug 7591055
to_char(head.plan_version_id) = evt.ATTRIBUTE3 --Bug 7591055
ORDER BY evt.EVENT_ID ASC;
SELECT COUNT(*)
INTO l_child_task_check
FROM pa_xbs_denorm denom,
pji_pjp_wbs_header headr
WHERE denom.sup_project_id = p_project_id
AND headr.project_id = denom.sup_project_id
AND headr.plan_version_id = l_plan_version_id
AND headr.wbs_version_id = denom.struct_version_id
AND struct_type = 'XBS'
AND ROWNUM =1;
SELECT wbs_version_id INTO l_wbs_version_id
FROM pji_pjp_wbs_header
WHERE plan_version_id = l_plan_version_id
AND project_id = p_project_id;
SELECT COUNT(*)
INTO l_child_task_check
FROM pa_object_relationships por
WHERE por.object_id_from1 = l_wbs_version_id
AND por.relationship_type = 'S'
AND rownum = 1;
/* PLAN_UPDATE_PVT
( p_plan_version_id => p_plan_version_id_tbl(i),
x_processing_code => x_processing_code,
x_return_status => x_return_status );*/
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
PROJ_ELEMENT_ID, project_id
INTO
l_struct_element_id, l_project_id
FROM
pa_proj_element_versions
WHERE
ELEMENT_VERSION_ID = p_wbs_version_id;
select distinct head.wbs_version_id,head.plan_version_id,head.plan_type_code BULK COLLECT
into jl_get_wbs_version_id_tbl,jl_get_plan_version_id_tbl,jl_get_plan_type_code_tbl /*4471527 */
from pji_pjp_wbs_header head
where head.plan_version_id = p_plan_version_id_tbl(i)
and head.project_id in (select project_id from pji_pjp_wbs_header head1
where head1.wbs_version_id=p_wbs_version_id );
SELECT distinct head.plan_version_id,head.project_id ,pji.sub_id wbs_version_id,head.plan_type_code BULK COLLECT
into il_plan_version_id_tbl, il_project_id_tbl,il_wbs_version_id_tbl,il_plan_type_code_tbl /*4471527 */
FROM pji_xbs_Denorm pji
,pa_proj_element_versions pa
,pji_pjp_wbs_header head
WHERE pji.struct_type='PRG'
and pji.sup_level<>pji.sub_level
and pji.sup_id in (l_get_wbs_version_id_tbl(1),l_get_wbs_version_id_tbl(2),l_get_wbs_version_id_tbl(3)
,l_get_wbs_version_id_tbl(4),l_get_wbs_version_id_tbl(5),l_get_wbs_version_id_tbl(6)
,l_get_wbs_version_id_tbl(7),l_get_wbs_version_id_tbl(8),l_get_wbs_version_id_tbl(9)
,l_get_wbs_version_id_tbl(10),l_get_wbs_version_id_tbl(11),l_get_wbs_version_id_tbl(12)
,l_get_wbs_version_id_tbl(13),l_get_wbs_version_id_tbl(14),l_get_wbs_version_id_tbl(15)
,l_get_wbs_version_id_tbl(16),l_get_wbs_version_id_tbl(17),l_get_wbs_version_id_tbl(18)
,l_get_wbs_version_id_tbl(19),l_get_wbs_version_id_tbl(20),l_get_wbs_version_id_tbl(21)
,l_get_wbs_version_id_tbl(22),l_get_wbs_version_id_tbl(23),l_get_wbs_version_id_tbl(24)
,l_get_wbs_version_id_tbl(25),l_get_wbs_version_id_tbl(26),l_get_wbs_version_id_tbl(27)
,l_get_wbs_version_id_tbl(28),l_get_wbs_version_id_tbl(29),l_get_wbs_version_id_tbl(30)
,l_get_wbs_version_id_tbl(31))
and pa.ELEMENT_VERSION_ID=pji.SUB_ID
AND head.project_id=pa.project_id
AND pji.sub_id=head.wbs_version_id
and (head.cb_flag='Y'
OR head.co_flag='Y'
OR head.wp_flag='Y'
OR (head.wp_flag='N' and head.plan_version_id=-1)
);
SELECT 'Y' -- , 'Y'
INTO l_exists -- , l_rollup_status_exists
FROM pji_rollup_level_status rst
WHERE rst.RBS_VERSION_ID = l_rbs_version_id_tbl(k) AND
rst.PLAN_VERSION_ID = l_prg_plan_version_id_tbl(i) AND
rst.PLAN_TYPE_CODE = l_prg_plan_type_code_tbl(i) AND
rst.project_id = l_prg_project_id_tbl(i);
INSERT INTO pji_rollup_level_status (
PROJECT_ID,
RBS_VERSION_ID,
PLAN_VERSION_ID,
WBS_ELEMENT_ID,
RBS_AGGR_LEVEL,
WBS_ROLLUP_FLAG,
PRG_ROLLUP_FLAG,
CURR_RECORD_TYPE_ID,
CALENDAR_TYPE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
PLAN_TYPE_CODE /*4471527 */
)
SELECT
l_prg_project_id_tbl(i) project_id,
l_rbs_version_id_tbl(k) RBS_VERSION_ID,
l_prg_plan_version_id_tbl(i) PLAN_VERSION_ID,
-1 WBS_ELEMENT_ID,
'R' RBS_AGGR_LEVEL,
'Y' WBS_ROLLUP_FLAG,
'N' PRG_ROLLUP_FLAG,
31 CURR_RECORD_TYPE_ID,
'X' CALENDAR_TYPE,
l_last_update_date LAST_UPDATE_DATE,
l_last_updated_by LAST_UPDATED_BY,
l_creation_date CREATION_DATE,
l_created_by CREATED_BY,
l_last_update_login LAST_UPDATE_LOGIN,
l_prg_plan_type_code_tbl(i) PLAN_TYPE_CODE /*4471527 */
FROM dual;
INSERT INTO pji_fp_aggr_pjp1_t (
WORKER_ID,
PRG_LEVEL,
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 ,
PLAN_TYPE_CODE) /*4471527 */
SELECT
-1,
0,
fact.PROJECT_ID,
fact.PROJECT_ORG_ID,
fact.PROJECT_ORGANIZATION_ID,
fact.PROJECT_ELEMENT_ID,
fact.TIME_ID,
fact.PERIOD_TYPE_ID,
fact.CALENDAR_TYPE,
fact.RBS_AGGR_LEVEL,
fact.WBS_ROLLUP_FLAG,
fact.PRG_ROLLUP_FLAG,
fact.CURR_RECORD_TYPE_ID,
fact.CURRENCY_CODE,
fact.RBS_ELEMENT_ID,
fact.RBS_VERSION_ID,
fact.PLAN_VERSION_ID,
fact.PLAN_TYPE_ID,
fact.RAW_COST,
fact.BRDN_COST,
fact.REVENUE,
fact.BILL_RAW_COST,
fact.BILL_BRDN_COST,
fact.BILL_LABOR_RAW_COST,
fact.BILL_LABOR_BRDN_COST,
fact.BILL_LABOR_HRS,
fact.EQUIPMENT_RAW_COST,
fact.EQUIPMENT_BRDN_COST,
fact.CAPITALIZABLE_RAW_COST,
fact.CAPITALIZABLE_BRDN_COST,
fact.LABOR_RAW_COST,
fact.LABOR_BRDN_COST,
fact.LABOR_HRS,
fact.LABOR_REVENUE,
fact.EQUIPMENT_HOURS,
fact.BILLABLE_EQUIPMENT_HOURS,
fact.SUP_INV_COMMITTED_COST,
fact.PO_COMMITTED_COST,
fact.PR_COMMITTED_COST,
fact.OTH_COMMITTED_COST,
fact.ACT_LABOR_HRS,
fact.ACT_EQUIP_HRS,
fact.ACT_LABOR_BRDN_COST,
fact.ACT_EQUIP_BRDN_COST,
fact.ACT_BRDN_COST,
fact.ACT_RAW_COST,
fact.ACT_REVENUE,
fact.ACT_LABOR_RAW_COST,
fact.ACT_EQUIP_RAW_COST,
fact.ETC_LABOR_HRS,
fact.ETC_EQUIP_HRS,
fact.ETC_LABOR_BRDN_COST,
fact.ETC_EQUIP_BRDN_COST,
fact.ETC_BRDN_COST,
fact.ETC_RAW_COST,
fact.ETC_LABOR_RAW_COST,
fact.ETC_EQUIP_RAW_COST,
fact.CUSTOM1,
fact.CUSTOM2,
fact.CUSTOM3,
fact.CUSTOM4,
fact.CUSTOM5,
fact.CUSTOM6,
fact.CUSTOM7,
fact.CUSTOM8,
fact.CUSTOM9,
fact.CUSTOM10,
fact.CUSTOM11,
fact.CUSTOM12,
fact.CUSTOM13,
fact.CUSTOM14,
fact.CUSTOM15,
fact.PLAN_TYPE_CODE /*4471527 */
FROM
pji_fp_xbs_accum_f fact,
pji_pjp_wbs_header head
WHERE
fact.RBS_AGGR_LEVEL = 'L' AND
fact.WBS_ROLLUP_FLAG = 'N' AND
fact.PRG_ROLLUP_FLAG in ('Y', 'N') AND
fact.PROJECT_ID = head.PROJECT_ID AND
fact.PLAN_VERSION_ID = head.PLAN_VERSION_ID AND
fact.PLAN_TYPE_CODE = head.PLAN_TYPE_CODE AND /*4471527 */
decode(fact.PLAN_VERSION_ID,
-3, fact.PLAN_TYPE_ID,
-4, fact.PLAN_TYPE_ID,
-1) = decode(fact.PLAN_VERSION_ID,
-3, head.PLAN_TYPE_ID,
-4, head.PLAN_TYPE_ID,
-1) AND
head.WBS_VERSION_ID = l_wbs_version_id_tbl(j) AND
head.project_id = l_project_id_tbl(j) AND
fact.rbs_version_id = l_rbs_version_id_tbl(k) AND
head.PLAN_VERSION_ID = l_plan_version_id_tbl(j) AND
head.PLAN_TYPE_CODE = l_plan_type_code_tbl(j) ;
Pji_Utils.write2log(' Inserted L N N / L N Y slices ' || SQL%ROWCOUNT ,null,3);
INSERT INTO pji_fp_aggr_pjp1_t (
WORKER_ID,
PRG_LEVEL,
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,
PLAN_TYPE_CODE ) /*4471527 */
SELECT /*+ ORDERED INDEX(XBS PJI_XBS_DENORM_N2) */
1,
0,
fact.PROJECT_ID,
fact.PROJECT_ORG_ID,
fact.PROJECT_ORGANIZATION_ID,
xbs.SUP_EMT_ID,
fact.TIME_ID,
fact.PERIOD_TYPE_ID,
fact.CALENDAR_TYPE,
fact.RBS_AGGR_LEVEL,
'Y', --fact.WBS_ROLLUP_FLAG,
fact.PRG_ROLLUP_FLAG,
fact.CURR_RECORD_TYPE_ID,
fact.CURRENCY_CODE,
fact.RBS_ELEMENT_ID,-- rbs.SUP_ID,
fact.RBS_VERSION_ID,
fact.PLAN_VERSION_ID,
fact.PLAN_TYPE_ID,
SUM(fact.RAW_COST),
SUM(fact.BRDN_COST),
SUM(fact.REVENUE),
SUM(fact.BILL_RAW_COST),
SUM(fact.BILL_BRDN_COST),
SUM(fact.BILL_LABOR_RAW_COST),
SUM(fact.BILL_LABOR_BRDN_COST),
SUM(fact.BILL_LABOR_HRS),
SUM(fact.EQUIPMENT_RAW_COST),
SUM(fact.EQUIPMENT_BRDN_COST),
SUM(fact.CAPITALIZABLE_RAW_COST),
SUM(fact.CAPITALIZABLE_BRDN_COST),
SUM(fact.LABOR_RAW_COST),
SUM(fact.LABOR_BRDN_COST),
SUM(fact.LABOR_HRS),
SUM(fact.LABOR_REVENUE),
SUM(fact.EQUIPMENT_HOURS),
SUM(fact.BILLABLE_EQUIPMENT_HOURS),
SUM(fact.SUP_INV_COMMITTED_COST),
SUM(fact.PO_COMMITTED_COST),
SUM(fact.PR_COMMITTED_COST),
SUM(fact.OTH_COMMITTED_COST),
SUM(fact.ACT_LABOR_HRS ),
SUM(fact.ACT_EQUIP_HRS ),
SUM(fact.ACT_LABOR_BRDN_COST ),
SUM(fact.ACT_EQUIP_BRDN_COST ),
SUM(fact.ACT_BRDN_COST ),
SUM(fact.ACT_RAW_COST ),
SUM(fact.ACT_REVENUE ),
SUM(fact.ACT_LABOR_RAW_COST),
SUM(fact.ACT_EQUIP_RAW_COST),
SUM(fact.ETC_LABOR_HRS ),
SUM(fact.ETC_EQUIP_HRS ),
SUM(fact.ETC_LABOR_BRDN_COST ),
SUM(fact.ETC_EQUIP_BRDN_COST ),
SUM(fact.ETC_BRDN_COST ),
SUM(fact.ETC_RAW_COST ),
SUM(fact.ETC_LABOR_RAW_COST),
SUM(fact.ETC_EQUIP_RAW_COST),
SUM(fact.CUSTOM1),
SUM(fact.CUSTOM2),
SUM(fact.CUSTOM3),
SUM(fact.CUSTOM4),
SUM(fact.CUSTOM5),
SUM(fact.CUSTOM6),
SUM(fact.CUSTOM7),
SUM(fact.CUSTOM8),
SUM(fact.CUSTOM9),
SUM(fact.CUSTOM10),
SUM(fact.CUSTOM11),
SUM(fact.CUSTOM12),
SUM(fact.CUSTOM13),
SUM(fact.CUSTOM14),
SUM(fact.CUSTOM15),
fact.PLAN_TYPE_CODE /*4471527 */
FROM
pji_fp_aggr_pjp1_t fact,
pji_pjp_wbs_header head,
pji_xbs_denorm xbs
WHERE
xbs.STRUCT_VERSION_ID = head.WBS_VERSION_ID AND
xbs.STRUCT_TYPE = 'WBS' AND
xbs.SUP_LEVEL <> xbs.SUB_LEVEL AND
xbs.SUB_EMT_ID = fact.PROJECT_ELEMENT_ID AND
fact.PROJECT_ID = head.PROJECT_ID AND
fact.PLAN_VERSION_ID = head.PLAN_VERSION_ID AND
fact.PLAN_TYPE_CODE = head.PLAN_TYPE_CODE AND /*4471527 */
head.WBS_VERSION_ID = l_roll_wbs_version_id_tbl(j) AND
decode(fact.PLAN_VERSION_ID,
-3, fact.PLAN_TYPE_ID,
-4, fact.PLAN_TYPE_ID,
-1) = decode(fact.PLAN_VERSION_ID,
-3, head.PLAN_TYPE_ID,
-4, head.PLAN_TYPE_ID,
-1)
GROUP BY
fact.PROJECT_ID,
fact.PROJECT_ORG_ID,
fact.PROJECT_ORGANIZATION_ID,
xbs.SUP_EMT_ID,
fact.TIME_ID,
fact.PERIOD_TYPE_ID,
fact.CALENDAR_TYPE,
fact.RBS_AGGR_LEVEL,
'Y', --fact.WBS_ROLLUP_FLAG,
fact.PRG_ROLLUP_FLAG,
fact.CURR_RECORD_TYPE_ID,
fact.CURRENCY_CODE,
fact.RBS_ELEMENT_ID,-- rbs.SUP_ID,
fact.RBS_VERSION_ID,
fact.PLAN_VERSION_ID,
fact.PLAN_TYPE_ID,
fact.PLAN_TYPE_CODE; /*4471527 */
Pji_Utils.write2log(' Inserted WBS rollup L N N / L N Y slices ' || SQL%ROWCOUNT ,null,3);
INSERT INTO pji_fp_aggr_pjp1_t (
WORKER_ID,
PRG_LEVEL,
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,
PLAN_TYPE_CODE ) /*4471527 */
SELECT /*+ ORDERED INDEX(XBS PJI_XBS_DENORM_N2) */
1,
0,
fact.PROJECT_ID,
fact.PROJECT_ORG_ID,
fact.PROJECT_ORGANIZATION_ID,
xbs.SUP_EMT_ID,
fact.TIME_ID,
fact.PERIOD_TYPE_ID,
fact.CALENDAR_TYPE,
fact.RBS_AGGR_LEVEL,
'Y', --fact.WBS_ROLLUP_FLAG,
fact.PRG_ROLLUP_FLAG,
fact.CURR_RECORD_TYPE_ID,
fact.CURRENCY_CODE,
fact.RBS_ELEMENT_ID,-- rbs.SUP_ID,
fact.RBS_VERSION_ID,
fact.PLAN_VERSION_ID,
fact.PLAN_TYPE_ID,
SUM(fact.RAW_COST),
SUM(fact.BRDN_COST),
SUM(fact.REVENUE),
SUM(fact.BILL_RAW_COST),
SUM(fact.BILL_BRDN_COST),
SUM(fact.BILL_LABOR_RAW_COST),
SUM(fact.BILL_LABOR_BRDN_COST),
SUM(fact.BILL_LABOR_HRS),
SUM(fact.EQUIPMENT_RAW_COST),
SUM(fact.EQUIPMENT_BRDN_COST),
SUM(fact.CAPITALIZABLE_RAW_COST),
SUM(fact.CAPITALIZABLE_BRDN_COST),
SUM(fact.LABOR_RAW_COST),
SUM(fact.LABOR_BRDN_COST),
SUM(fact.LABOR_HRS),
SUM(fact.LABOR_REVENUE),
SUM(fact.EQUIPMENT_HOURS),
SUM(fact.BILLABLE_EQUIPMENT_HOURS),
SUM(fact.SUP_INV_COMMITTED_COST),
SUM(fact.PO_COMMITTED_COST),
SUM(fact.PR_COMMITTED_COST),
SUM(fact.OTH_COMMITTED_COST),
SUM(fact.ACT_LABOR_HRS ),
SUM(fact.ACT_EQUIP_HRS ),
SUM(fact.ACT_LABOR_BRDN_COST ),
SUM(fact.ACT_EQUIP_BRDN_COST ),
SUM(fact.ACT_BRDN_COST ),
SUM(fact.ACT_RAW_COST ),
SUM(fact.ACT_REVENUE ),
SUM(fact.ACT_LABOR_RAW_COST),
SUM(fact.ACT_EQUIP_RAW_COST),
SUM(fact.ETC_LABOR_HRS ),
SUM(fact.ETC_EQUIP_HRS ),
SUM(fact.ETC_LABOR_BRDN_COST ),
SUM(fact.ETC_EQUIP_BRDN_COST ),
SUM(fact.ETC_BRDN_COST ),
SUM(fact.ETC_RAW_COST ),
SUM(fact.ETC_LABOR_RAW_COST),
SUM(fact.ETC_EQUIP_RAW_COST),
SUM(fact.CUSTOM1),
SUM(fact.CUSTOM2),
SUM(fact.CUSTOM3),
SUM(fact.CUSTOM4),
SUM(fact.CUSTOM5),
SUM(fact.CUSTOM6),
SUM(fact.CUSTOM7),
SUM(fact.CUSTOM8),
SUM(fact.CUSTOM9),
SUM(fact.CUSTOM10),
SUM(fact.CUSTOM11),
SUM(fact.CUSTOM12),
SUM(fact.CUSTOM13),
SUM(fact.CUSTOM14),
SUM(fact.CUSTOM15),
fact.PLAN_TYPE_CODE /*4471527 */
FROM
pji_fp_aggr_pjp1_t fact,
pji_pjp_wbs_header head,
pji_xbs_denorm xbs
WHERE
xbs.STRUCT_VERSION_ID = head.WBS_VERSION_ID AND
xbs.STRUCT_TYPE = 'XBS' AND
xbs.SUP_LEVEL <> xbs.SUB_LEVEL AND
xbs.SUB_EMT_ID = fact.PROJECT_ELEMENT_ID AND
fact.PROJECT_ID = head.PROJECT_ID AND
fact.PLAN_VERSION_ID = head.PLAN_VERSION_ID AND
fact.PLAN_TYPE_CODE = head.PLAN_TYPE_CODE AND /*4471527 */
head.WBS_VERSION_ID = l_roll_wbs_version_id_tbl(j) AND
decode(fact.PLAN_VERSION_ID,
-3, fact.PLAN_TYPE_ID,
-4, fact.PLAN_TYPE_ID,
-1) = decode(fact.PLAN_VERSION_ID,
-3, head.PLAN_TYPE_ID,
-4, head.PLAN_TYPE_ID,
-1)
GROUP BY
fact.PROJECT_ID,
fact.PROJECT_ORG_ID,
fact.PROJECT_ORGANIZATION_ID,
xbs.SUP_EMT_ID,
fact.TIME_ID,
fact.PERIOD_TYPE_ID,
fact.CALENDAR_TYPE,
fact.RBS_AGGR_LEVEL,
'Y', --fact.WBS_ROLLUP_FLAG,
fact.PRG_ROLLUP_FLAG,
fact.CURR_RECORD_TYPE_ID,
fact.CURRENCY_CODE,
fact.RBS_ELEMENT_ID,-- rbs.SUP_ID,
fact.RBS_VERSION_ID,
fact.PLAN_VERSION_ID,
fact.PLAN_TYPE_ID,
fact.PLAN_TYPE_CODE; /*4471527 */
Pji_Utils.write2log(' Inserted XBS rollup for WBS slices ' || SQL%ROWCOUNT ,null,3);
INSERT INTO pji_fp_aggr_pjp1_t (
WORKER_ID,
PRG_LEVEL,
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,
PLAN_TYPE_CODE ) /*4471527 */
SELECT
1,
0,
fact.PROJECT_ID,
fact.PROJECT_ORG_ID,
fact.PROJECT_ORGANIZATION_ID,
fact.PROJECT_ELEMENT_ID,
fact.TIME_ID,
fact.PERIOD_TYPE_ID,
fact.CALENDAR_TYPE,
'R', --fact.RBS_AGGR_LEVEL,
fact.WBS_ROLLUP_FLAG,
fact.PRG_ROLLUP_FLAG,
fact.CURR_RECORD_TYPE_ID,
fact.CURRENCY_CODE,
rbs.SUP_ID,
fact.RBS_VERSION_ID,
fact.PLAN_VERSION_ID,
fact.PLAN_TYPE_ID,
SUM(fact.RAW_COST),
SUM(fact.BRDN_COST),
SUM(fact.REVENUE),
SUM(fact.BILL_RAW_COST),
SUM(fact.BILL_BRDN_COST),
SUM(fact.BILL_LABOR_RAW_COST),
SUM(fact.BILL_LABOR_BRDN_COST),
SUM(fact.BILL_LABOR_HRS),
SUM(fact.EQUIPMENT_RAW_COST),
SUM(fact.EQUIPMENT_BRDN_COST),
SUM(fact.CAPITALIZABLE_RAW_COST),
SUM(fact.CAPITALIZABLE_BRDN_COST),
SUM(fact.LABOR_RAW_COST),
SUM(fact.LABOR_BRDN_COST),
SUM(fact.LABOR_HRS),
SUM(fact.LABOR_REVENUE),
SUM(fact.EQUIPMENT_HOURS),
SUM(fact.BILLABLE_EQUIPMENT_HOURS),
SUM(fact.SUP_INV_COMMITTED_COST),
SUM(fact.PO_COMMITTED_COST),
SUM(fact.PR_COMMITTED_COST),
SUM(fact.OTH_COMMITTED_COST),
SUM(fact.ACT_LABOR_HRS ),
SUM(fact.ACT_EQUIP_HRS ),
SUM(fact.ACT_LABOR_BRDN_COST ),
SUM(fact.ACT_EQUIP_BRDN_COST ),
SUM(fact.ACT_BRDN_COST ),
SUM(fact.ACT_RAW_COST ),
SUM(fact.ACT_REVENUE ),
SUM(fact.ACT_LABOR_RAW_COST),
SUM(fact.ACT_EQUIP_RAW_COST),
SUM(fact.ETC_LABOR_HRS ),
SUM(fact.ETC_EQUIP_HRS ),
SUM(fact.ETC_LABOR_BRDN_COST ),
SUM(fact.ETC_EQUIP_BRDN_COST ),
SUM(fact.ETC_BRDN_COST ),
SUM(fact.ETC_RAW_COST ),
SUM(fact.ETC_LABOR_RAW_COST),
SUM(fact.ETC_EQUIP_RAW_COST),
SUM(fact.CUSTOM1),
SUM(fact.CUSTOM2),
SUM(fact.CUSTOM3),
SUM(fact.CUSTOM4),
SUM(fact.CUSTOM5),
SUM(fact.CUSTOM6),
SUM(fact.CUSTOM7),
SUM(fact.CUSTOM8),
SUM(fact.CUSTOM9),
SUM(fact.CUSTOM10),
SUM(fact.CUSTOM11),
SUM(fact.CUSTOM12),
SUM(fact.CUSTOM13),
SUM(fact.CUSTOM14),
SUM(fact.CUSTOM15),
fact.PLAN_TYPE_CODE /*4471527 */
FROM
pji_fp_aggr_pjp1_t fact,
pji_rbs_denorm rbs
WHERE
rbs.STRUCT_VERSION_ID = l_rbs_version_id_tbl(j) AND
rbs.SUP_LEVEL <> rbs.SUB_LEVEL AND
rbs.SUB_ID = fact.RBS_ELEMENT_ID
GROUP BY
fact.PROJECT_ID,
fact.PROJECT_ORG_ID,
fact.PROJECT_ORGANIZATION_ID,
fact.PROJECT_ELEMENT_ID,--xbs.SUB_ELEMENT_ID,
fact.TIME_ID,
fact.PERIOD_TYPE_ID,
fact.CALENDAR_TYPE,
'R',
fact.WBS_ROLLUP_FLAG,
fact.PRG_ROLLUP_FLAG,
fact.CURR_RECORD_TYPE_ID,
fact.CURRENCY_CODE,
rbs.SUP_ID,
fact.RBS_VERSION_ID,
fact.PLAN_VERSION_ID,
fact.PLAN_TYPE_ID,
fact.PLAN_TYPE_CODE;
Pji_Utils.write2log(' Inserted RBS rollup for all slices ' || SQL%ROWCOUNT ,null,3);
DELETE FROM pji_fp_aggr_pjp1_t;
DELETE FROM PJI_FM_EXTR_PLNVER3_T;
DELETE FROM pji_fp_rmap_fpr_update_t;
SELECT PROJECT_ID
INTO l_project_id
FROM pa_budget_versions
WHERE budget_version_id = p_fp_version_ids(i);
SELECT /*+ index_ffs(wbs_hdr PJI_PJP_WBS_HEADER_N1) */ PROJECT_ID
INTO l_project_id
FROM pji_pjp_wbs_header wbs_hdr
WHERE plan_version_id = p_fp_version_ids(i);
PROCEDURE INSERT_APPLY_PROG_VD IS
BEGIN
IF PA_PROGRESS_PUB.G_WBS_APPLY_PROG IS NOT NULL THEN
--------------------------------------------------------
--If the ETC column is null and the PLAN column has some
--value, we copy the plan value to the ETC columns
--The 0 rows for ETC in this case will ensure that in the
--incremental change in plan value is not copied to the
--ETC columns
--------------------------------------------------------
INSERT INTO pji_fm_extr_plan_lines (
PROJECT_ID, PROJECT_ORG_ID, PROJECT_ELEMENT_ID, STRUCT_VER_ID,
PERIOD_NAME, CALENDAR_TYPE, START_DATE, END_DATE, RBS_ELEMENT_ID,
RBS_VERSION_ID, PLAN_VERSION_ID, PLAN_TYPE_ID, WP_VERSION_FLAG,
ROLLUP_TYPE, TXN_CURRENCY_CODE, PRJ_CURRENCY_CODE, PFC_CURRENCY_CODE,
RESOURCE_CLASS_CODE, RATE_BASED_FLAG,
ETC_PFC_BURDENED_COST,ETC_PFC_RAW_COST, ETC_PRJ_BURDENED_COST,
ETC_PRJ_RAW_COST, ETC_QUANTITY, ETC_TXN_BURDENED_COST, ETC_TXN_RAW_COST )
SELECT
PROJECT_ID, PROJECT_ORG_ID, PROJECT_ELEMENT_ID, STRUCT_VER_ID,
PERIOD_NAME, CALENDAR_TYPE, START_DATE, END_DATE, RBS_ELEMENT_ID,
RBS_VERSION_ID, PLAN_VERSION_ID, PLAN_TYPE_ID, WP_VERSION_FLAG,
ROLLUP_TYPE, TXN_CURRENCY_CODE, PRJ_CURRENCY_CODE, PFC_CURRENCY_CODE,
RESOURCE_CLASS_CODE, RATE_BASED_FLAG,
0, 0, 0,
0, 0, 0, 0
FROM
pji_fm_extr_plan_lines
WHERE
STRUCT_VER_ID = PA_PROGRESS_PUB.G_WBS_APPLY_PROG
GROUP BY
PROJECT_ID, PROJECT_ORG_ID, PROJECT_ELEMENT_ID, STRUCT_VER_ID,
PERIOD_NAME, CALENDAR_TYPE, START_DATE, END_DATE, RBS_ELEMENT_ID,
RBS_VERSION_ID, PLAN_VERSION_ID, PLAN_TYPE_ID, WP_VERSION_FLAG,
ROLLUP_TYPE, TXN_CURRENCY_CODE, PRJ_CURRENCY_CODE, PFC_CURRENCY_CODE,
RESOURCE_CLASS_CODE, RATE_BASED_FLAG;