[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
g_process_update_wbs_flag VARCHAR2(1) := 'N' ;
SELECT rpa.rbs_version_id, bv.plan_version_id
FROM pa_rbs_prj_assignments rpa
, PJI_FM_EXTR_PLNVER4 bv
WHERE 1 = 1
AND bv.project_id = rpa.project_id
AND bv.rbs_struct_version_id <> rpa.rbs_version_id
AND bv.plan_version_id = p_budget_version_id
AND bv.worker_id = g_worker_id;
SELECT rpa.rbs_version_id, bv.plan_version_id
FROM pa_rbs_prj_assignments rpa
, PJI_FM_EXTR_PLNVER3_T bv
WHERE 1 = 1
AND bv.project_id = rpa.project_id
AND bv.rbs_struct_version_id <> rpa.rbs_version_id
AND bv.plan_version_id = p_budget_version_id;
PJI_PJP_SUM_ROLLUP.UPDATE_RBS_DENORM(l_worker_id);
PJI_PJP_SUM_ROLLUP.UPDATE_XBS_DENORM_FULL(l_worker_id);
PJI_PJP_SUM_ROLLUP.UPDATE_WBS_HDR(l_worker_id);
PJI_PJP_SUM_ROLLUP.INSERT_INTO_FP_FACT(l_worker_id);
SELECT DECODE ( src.wp_version_flag
, 'Y'
, src.project_structure_version_id
, Pa_Project_Structure_Utils.GET_FIN_STRUC_VER_ID(src.project_id)
) src_struct_version_id
FROM pa_budget_versions src
WHERE src.budget_version_id = c_fp_src_version_id
);
SELECT DECODE ( tar.wp_version_flag
, 'Y'
, tar.project_structure_version_id
, Pa_Project_Structure_Utils.GET_FIN_STRUC_VER_ID(tar.project_id)
) tar_struct_version_id
FROM pa_budget_versions tar
WHERE tar.budget_version_id = c_fp_tar_version_id
);
print_time(' 2.. Update org/cal tables. ');
Pji_Fm_Plan_Maint_Pvt.DELETE_GLOBAL_EXCHANGE_RATES;
print_time(' Deleted global exchange rates. ');
print_time(' Inserted Reversal records for dangling plans.');
print_time(' Updated calendar and org tables. ');
PJI_FM_PLAN_MAINT_T_PVT.DELETE_GLOBAL_EXCHANGE_RATES;
print_time(' Deleted global exchange rates. ');
PROCEDURE UPDATE_PRIMARY_PVT (
p_plan_version_ids IN SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type()
-- p_plan_version_id IN NUMBER := NULL
, p_commit IN VARCHAR2 := 'F'
)
IS
-- l_plan_version_ids SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type(p_plan_version_id);
print_time('UPDATE_PRIMARY_PVT: begin.. ');
print_time(' 1.a3 Updated org/cal tables. ');
print_time('UPDATE_PRIMARY_PVT: end. successful.. ');
print_time('UPDATE_PRIMARY_PVT: exception.. ');
p_procedure_name => 'UPDATE_PRIMARY_PVT');
PROCEDURE UPDATE_PRIMARY_PVT_ACT_ETC (
p_commit IN VARCHAR2 := 'F'
, p_plan_version_id IN NUMBER := NULL
, p_prev_pub_version_id IN NUMBER := NULL
, x_return_status OUT NOCOPY VARCHAR2
, x_processing_code OUT NOCOPY VARCHAR2) IS
l_fp_version_ids SYSTEM.pa_num_tbl_type;
print_time ( 'UPDATE_PRIMARY_PVT_ACT_ETC: begin.. ');
print_time ( 'UPDATE_PRIMARY_PVT_ACT_ETC: p_plan_version_id ' || p_plan_version_id );
print_time('UPDATE_PRIMARY_PVT_ACT_ETC: end. successful.. ');
, p_procedure_name => 'UPDATE_PRIMARY_PVT_ACT_ETC'
, x_return_status => x_return_status ) ;
PROCEDURE DELETE_ALL_PVT (
p_fp_version_ids IN SYSTEM.pa_num_tbl_type := pji_empty_num_tbl
, p_commit IN VARCHAR2 := 'F') IS
l_project_ids SYSTEM.pa_num_tbl_type := pji_empty_num_tbl;
print_time('DELETE_ALL_PVT: begin.. ');
SELECT /*+ index(wbs_hdr PJI_PJP_WBS_HEADER_N1) */ project_id,wbs_version_id, wp_flag
INTO l_project_ids(i),l_wbs_version_ids(i),l_wp_flags(i)
FROM PJI_PJP_WBS_HEADER wbs_hdr
WHERE plan_version_id = p_fp_version_ids(i);
DELETE FROM PJI_FP_XBS_ACCUM_F
WHERE plan_version_id = p_fp_version_ids(i)
AND project_id = l_project_ids(i);
print_time ( 'deleted from fact. # rows = ' || SQL%ROWCOUNT ) ;
DELETE FROM PJI_PJP_WBS_HEADER
WHERE plan_version_id = p_fp_version_ids(i)
AND project_id = l_project_ids(i);
print_time ( 'deleted from wbs hdr table. # rows = ' || SQL%ROWCOUNT ) ;
DELETE FROM PJI_PJP_RBS_HEADER
WHERE plan_version_id = p_fp_version_ids(i)
AND project_id = l_project_ids(i);
print_time ( 'deleted from rbs header. # rows = ' || SQL%ROWCOUNT ) ;
DELETE FROM PJI_ROLLUP_LEVEL_STATUS
WHERE plan_version_id = p_fp_version_ids(i);
print_time ( 'deleted from rollup level status. # rows = ' || SQL%ROWCOUNT ) ;
DELETE FROM PA_XBS_DENORM
WHERE sup_project_id = l_project_ids(i)
AND l_wp_flags(i) = 'Y'
AND ( struct_version_id = l_wbs_version_ids(i)
OR (struct_type = 'PRG' AND sup_id = l_wbs_version_ids(i) AND sub_id = l_wbs_version_ids(i)));
print_time ( 'deleted from pa_xbs_denorm table. # rows = ' || SQL%ROWCOUNT ) ;
DELETE FROM PJI_XBS_DENORM
WHERE sup_project_id = l_project_ids(i)
AND l_wp_flags(i) = 'Y'
AND ( struct_version_id = l_wbs_version_ids(i)
OR (struct_type = 'PRG' AND sup_id = l_wbs_version_ids(i) AND sub_id = l_wbs_version_ids(i)));
print_time ( 'deleted from pji_xbs_denorm table. # rows = ' || SQL%ROWCOUNT ) ;
print_time('DELETE_ALL_PVT: end.. successful.. ');
print_time('DELETE_ALL_PVT: exception.. ');
p_procedure_name => 'DELETE_ALL_PVT');
PJI_FM_PLAN_MAINT_T_PVT.UPDATE_WBS_HDR;
print_time('Updated the WBS header table with min max txn dates.');
PJI_FM_PLAN_MAINT_T_PVT.UPDATE_WBS_HDR;
print_time('Updated the WBS header table with min max txn dates.');
SELECT /*+ index_ffs(wbs_hdr PJI_PJP_WBS_HEADER_N1) */
DISTINCT PROJECT_ID
INTO l_project_id
FROM pji_pjp_wbs_header wbs_hdr
WHERE plan_version_id = p_fp_version_ids(i);
SELECT DISTINCT wbs_struct_version_id
FROM pji_fm_extr_plnver3_t ver3
WHERE EXISTS (
SELECT /*+ no_unnest */ 1 -- bug 7607077 asahoo - replaced index hint
FROM pa_xbs_denorm pxd
WHERE 1=1
AND struct_version_id IS NULL
AND sup_id = ver3.wbs_struct_version_id
);
SELECT DISTINCT wbs_struct_version_id, project_id
FROM pji_fm_extr_plnver3_t
WHERE wbs_struct_version_id = p_wbs_version_id;
during copy/move/indent/outdent flows during plan update
Structure changes will get processed in process updates flow
IF NVL(Pa_Task_Pub1.G_CALL_PJI_ROLLUP, 'Y') = 'N' THEN
Pa_Task_Pub1.G_CALL_PJI_ROLLUP := NULL;
Pji_Pjp_Sum_Rollup.update_xbs_denorm;
SELECT COUNT(1)
INTO l_count
FROM pji_fm_extr_plnver3_t;
SELECT COUNT(1)
INTO l_count
FROM pji_fm_extr_plnver4
WHERE worker_id = g_worker_id;
SELECT a.process_update_wbs_flag, a.element_version_id
, a.project_id, b.plan_version_id
INTO g_process_update_wbs_flag, l_wbs_version_id
, l_project_id, l_plan_version_id
FROM pa_proj_elem_ver_structure a ,
PJI_FM_EXTR_PLNVER3_T b
WHERE a.element_version_id = b.wbs_struct_version_id
AND a.project_id = b.project_id -- 4902584
AND ROWNUM <= 1;
IF g_process_update_wbs_flag = 'Y' THEN
--
-- Create an event..
--
g_event_rec.event_type := 'WBS_CHANGE';
IF g_process_update_wbs_flag = 'Y' THEN
Pji_Pjp_Sum_Rollup.rollup_acr_wbs;
Pji_Pjp_Sum_Rollup.update_xbs_denorm;
print_time(' Updated PJI xbs denorm. ');
Pa_Proj_Task_Struc_Pub.set_update_wbs_flag (
p_project_id => TO_NUMBER(g_event_rec.attribute1),
p_structure_version_id => TO_NUMBER(g_event_rec.attribute2),
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 update by MAANSARI
Pa_Proj_Task_Struc_Pub.process_task_weightage (
p_project_id => TO_NUMBER(g_event_rec.attribute1),
p_structure_version_id => TO_NUMBER(g_event_rec.attribute2),
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
WHERE EVENT_ID = g_event_rec.event_id;
INSERT INTO pji_fm_aggr_fin8
(
WORKER_ID , RECORD_TYPE , TXN_ACCUM_HEADER_ID ,
RESOURCE_CLASS_ID , PROJECT_ID , PROJECT_ORG_ID ,
PROJECT_ORGANIZATION_ID , PROJECT_TYPE_CLASS , TASK_ID ,
RECVR_PERIOD_TYPE , RECVR_PERIOD_ID , TXN_CURRENCY_CODE ,
TXN_REVENUE , TXN_RAW_COST , TXN_BRDN_COST ,
TXN_BILL_RAW_COST , TXN_BILL_BRDN_COST , TXN_SUP_INV_COMMITTED_COST ,
TXN_PO_COMMITTED_COST , TXN_PR_COMMITTED_COST , TXN_OTH_COMMITTED_COST ,
PRJ_REVENUE , PRJ_RAW_COST , PRJ_BRDN_COST ,
PRJ_BILL_RAW_COST , PRJ_BILL_BRDN_COST , PRJ_REVENUE_WRITEOFF ,
PRJ_SUP_INV_COMMITTED_COST , PRJ_PO_COMMITTED_COST , PRJ_PR_COMMITTED_COST ,
PRJ_OTH_COMMITTED_COST , POU_REVENUE , POU_RAW_COST ,
POU_BRDN_COST , POU_BILL_RAW_COST , POU_BILL_BRDN_COST ,
POU_REVENUE_WRITEOFF , POU_SUP_INV_COMMITTED_COST , POU_PO_COMMITTED_COST ,
POU_PR_COMMITTED_COST , POU_OTH_COMMITTED_COST , EOU_REVENUE ,
EOU_RAW_COST , EOU_BRDN_COST , EOU_BILL_RAW_COST ,
EOU_BILL_BRDN_COST , EOU_SUP_INV_COMMITTED_COST , EOU_PO_COMMITTED_COST ,
EOU_PR_COMMITTED_COST , EOU_OTH_COMMITTED_COST , QUANTITY ,
BILL_QUANTITY , G1_REVENUE , G1_RAW_COST ,
G1_BRDN_COST , G1_BILL_RAW_COST , G1_BILL_BRDN_COST ,
G1_REVENUE_WRITEOFF , G1_SUP_INV_COMMITTED_COST , G1_PO_COMMITTED_COST ,
G1_PR_COMMITTED_COST , G1_OTH_COMMITTED_COST , G2_REVENUE ,
G2_RAW_COST , G2_BRDN_COST , G2_BILL_RAW_COST ,
G2_BILL_BRDN_COST , G2_REVENUE_WRITEOFF , G2_SUP_INV_COMMITTED_COST ,
G2_PO_COMMITTED_COST , G2_PR_COMMITTED_COST , G2_OTH_COMMITTED_COST ,
ASSIGNMENT_ID, NAMED_ROLE, --Bug#4590810
CBS_ELEMENT_ID
)
SELECT
tmp.WORKER_ID , RECORD_TYPE , TXN_ACCUM_HEADER_ID ,
RESOURCE_CLASS_ID , tmp.PROJECT_ID , tmp.PROJECT_ORG_ID ,
tmp.PROJECT_ORGANIZATION_ID , tmp.PROJECT_TYPE_CLASS , TASK_ID ,
RECVR_PERIOD_TYPE , RECVR_PERIOD_ID , TXN_CURRENCY_CODE ,
TXN_REVENUE , TXN_RAW_COST , TXN_BRDN_COST ,
TXN_BILL_RAW_COST , TXN_BILL_BRDN_COST , TXN_SUP_INV_COMMITTED_COST ,
TXN_PO_COMMITTED_COST , TXN_PR_COMMITTED_COST , TXN_OTH_COMMITTED_COST ,
PRJ_REVENUE , PRJ_RAW_COST , PRJ_BRDN_COST ,
PRJ_BILL_RAW_COST , PRJ_BILL_BRDN_COST , PRJ_REVENUE_WRITEOFF ,
PRJ_SUP_INV_COMMITTED_COST , PRJ_PO_COMMITTED_COST , PRJ_PR_COMMITTED_COST ,
PRJ_OTH_COMMITTED_COST , POU_REVENUE , POU_RAW_COST ,
POU_BRDN_COST , POU_BILL_RAW_COST , POU_BILL_BRDN_COST ,
POU_REVENUE_WRITEOFF , POU_SUP_INV_COMMITTED_COST , POU_PO_COMMITTED_COST ,
POU_PR_COMMITTED_COST , POU_OTH_COMMITTED_COST , EOU_REVENUE ,
EOU_RAW_COST , EOU_BRDN_COST , EOU_BILL_RAW_COST ,
EOU_BILL_BRDN_COST , EOU_SUP_INV_COMMITTED_COST , EOU_PO_COMMITTED_COST ,
EOU_PR_COMMITTED_COST , EOU_OTH_COMMITTED_COST , QUANTITY ,
BILL_QUANTITY , G1_REVENUE , G1_RAW_COST ,
G1_BRDN_COST , G1_BILL_RAW_COST , G1_BILL_BRDN_COST ,
G1_REVENUE_WRITEOFF , G1_SUP_INV_COMMITTED_COST , G1_PO_COMMITTED_COST ,
G1_PR_COMMITTED_COST , G1_OTH_COMMITTED_COST , G2_REVENUE ,
G2_RAW_COST , G2_BRDN_COST , G2_BILL_RAW_COST ,
G2_BILL_BRDN_COST , G2_REVENUE_WRITEOFF , G2_SUP_INV_COMMITTED_COST ,
G2_PO_COMMITTED_COST , G2_PR_COMMITTED_COST , G2_OTH_COMMITTED_COST ,
ASSIGNMENT_ID , NAMED_ROLE, --Bug#4590810
tmp.cbs_element_id
FROM pji_fm_aggr_fin7 tmp
,PJI_PJP_PROJ_BATCH_MAP map
,pa_proj_fp_options ppfo
WHERE
tmp.PROJECT_ID=map.PROJECT_ID AND
map.WORKER_ID = p_worker_id AND
map.PJI_PROJECT_STATUS = 'Y' AND
ppfo.PROJECT_ID=tmp.PROJECT_ID AND
SUBSTR(tmp.RECVR_PERIOD_TYPE,1,1) = DECODE (ppfo.COST_TIME_PHASED_CODE,'N','G',ppfo.COST_TIME_PHASED_CODE) AND
ppfo.FIN_PLAN_TYPE_ID = (
SELECT fin_plan_type_id
FROM pa_fin_plan_types_b
WHERE use_for_workplan_flag = 'Y'
) AND
ppfo.FIN_PLAN_OPTION_LEVEL_CODE = 'PLAN_TYPE';
INSERT INTO pji_fm_aggr_fin8
(
TXN_ACCUM_HEADER_ID , RESOURCE_CLASS_ID , PROJECT_ID ,
PROJECT_ORG_ID , PROJECT_ORGANIZATION_ID , PROJECT_TYPE_CLASS ,
TASK_ID , RECVR_PERIOD_TYPE , RECVR_PERIOD_ID ,
TXN_CURRENCY_CODE , TXN_REVENUE , TXN_RAW_COST ,
TXN_BRDN_COST , TXN_BILL_RAW_COST , TXN_BILL_BRDN_COST ,
PRJ_REVENUE , PRJ_RAW_COST , PRJ_BRDN_COST ,
PRJ_BILL_RAW_COST , PRJ_BILL_BRDN_COST , POU_REVENUE ,
POU_RAW_COST , POU_BRDN_COST , POU_BILL_RAW_COST ,
POU_BILL_BRDN_COST , EOU_RAW_COST , EOU_BRDN_COST ,
EOU_BILL_RAW_COST , EOU_BILL_BRDN_COST , QUANTITY ,
BILL_QUANTITY , G1_REVENUE , G1_RAW_COST ,
G1_BRDN_COST , G1_BILL_RAW_COST , G1_BILL_BRDN_COST ,
G2_REVENUE , G2_RAW_COST , G2_BRDN_COST ,
G2_BILL_RAW_COST , G2_BILL_BRDN_COST , ASSIGNMENT_ID ,
WORKER_ID , RECORD_TYPE, NAMED_ROLE, --Bug#4590810
CBS_ELEMENT_ID
)
SELECT
TXN_ACCUM_HEADER_ID , RESOURCE_CLASS_ID , tmp.PROJECT_ID ,
tmp.PROJECT_ORG_ID , tmp.PROJECT_ORGANIZATION_ID , tmp.PROJECT_TYPE_CLASS ,
TASK_ID , RECVR_PERIOD_TYPE , RECVR_PERIOD_ID ,
TXN_CURRENCY_CODE , TXN_REVENUE , TXN_RAW_COST ,
TXN_BRDN_COST , TXN_BILL_RAW_COST , TXN_BILL_BRDN_COST ,
PRJ_REVENUE , PRJ_RAW_COST , PRJ_BRDN_COST ,
PRJ_BILL_RAW_COST , PRJ_BILL_BRDN_COST , POU_REVENUE ,
POU_RAW_COST , POU_BRDN_COST , POU_BILL_RAW_COST ,
POU_BILL_BRDN_COST , EOU_RAW_COST , EOU_BRDN_COST ,
EOU_BILL_RAW_COST , EOU_BILL_BRDN_COST , QUANTITY ,
BILL_QUANTITY , G1_REVENUE , G1_RAW_COST ,
G1_BRDN_COST , G1_BILL_RAW_COST , G1_BILL_BRDN_COST ,
G2_REVENUE , G2_RAW_COST , G2_BRDN_COST ,
G2_BILL_RAW_COST , G2_BILL_BRDN_COST , ASSIGNMENT_ID ,
p_worker_id , 'A' , NAMED_ROLE, --Bug#4590810
tmp.cbs_element_id
FROM pji_fp_txn_accum tmp
,PJI_PJP_PROJ_BATCH_MAP map
,pa_proj_fp_options ppfo
WHERE
tmp.PROJECT_ID=map.PROJECT_ID AND
map.WORKER_ID = p_worker_id AND
map.PJI_PROJECT_STATUS = 'Y' AND
ppfo.PROJECT_ID=tmp.PROJECT_ID AND
SUBSTR(tmp.RECVR_PERIOD_TYPE,1,1) = DECODE (ppfo.COST_TIME_PHASED_CODE,'N','G',ppfo.COST_TIME_PHASED_CODE) AND
ppfo.FIN_PLAN_TYPE_ID = (
SELECT fin_plan_type_id
FROM pa_fin_plan_types_b
WHERE use_for_workplan_flag = 'Y'
) AND
ppfo.FIN_PLAN_OPTION_LEVEL_CODE = 'PLAN_TYPE';