[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
( SELECT
TXN_ACCUM_HEADER_ID,
RESOURCE_CLASS_ID,
PROJECT_ID,
PROJECT_ORG_ID,
PROJECT_ORGANIZATION_ID,
PROJECT_TYPE_CLASS,
TASK_ID,
ASSIGNMENT_ID,
NAMED_ROLE,
RECVR_PERIOD_TYPE,
RECVR_PERIOD_ID,
TXN_CURRENCY_CODE,
TXN_RAW_COST,
TXN_BILL_RAW_COST,
TXN_BRDN_COST,
TXN_BILL_BRDN_COST,
TXN_REVENUE,
PRJ_RAW_COST,
PRJ_BILL_RAW_COST,
PRJ_BRDN_COST,
PRJ_BILL_BRDN_COST,
PRJ_REVENUE,
POU_RAW_COST,
POU_BILL_RAW_COST,
POU_BRDN_COST,
POU_BILL_BRDN_COST,
POU_REVENUE,
EOU_RAW_COST,
EOU_BILL_RAW_COST,
EOU_BRDN_COST,
EOU_BILL_BRDN_COST,
G1_RAW_COST,
G1_BILL_RAW_COST,
G1_BRDN_COST,
G1_BILL_BRDN_COST,
G1_REVENUE,
G2_RAW_COST,
G2_BILL_RAW_COST,
G2_BRDN_COST,
G2_BILL_BRDN_COST,
G2_REVENUE,
QUANTITY,
BILL_QUANTITY,
sysdate LAST_UPDATE_DATE,
-1 LAST_UPDATED_BY,
sysdate CREATION_DATE,
-1 CREATED_BY,
-1 LAST_UPDATE_LOGIN
FROM PJI_FM_AGGR_FIN7
WHERE project_id = p_project_id ) FIN7
ON
(
FIN7.TXN_ACCUM_HEADER_ID = accum.TXN_ACCUM_HEADER_ID and
FIN7.RESOURCE_CLASS_ID = accum.RESOURCE_CLASS_ID and
FIN7.PROJECT_ID = accum.PROJECT_ID and
FIN7.PROJECT_ORG_ID = accum.PROJECT_ORG_ID and
FIN7.PROJECT_ORGANIZATION_ID = accum.PROJECT_ORGANIZATION_ID and
FIN7.TASK_ID = accum.TASK_ID and
FIN7.ASSIGNMENT_ID = accum.ASSIGNMENT_ID and
FIN7.NAMED_ROLE = accum.NAMED_ROLE and
FIN7.RECVR_PERIOD_TYPE = accum.RECVR_PERIOD_TYPE and
FIN7.RECVR_PERIOD_ID = accum.RECVR_PERIOD_ID and
FIN7.TXN_CURRENCY_CODE = accum.TXN_CURRENCY_CODE
)
WHEN MATCHED THEN
UPDATE
SET
accum.TXN_RAW_COST = nvl(accum.TXN_RAW_COST,0) + nvl(FIN7.TXN_RAW_COST,0)
,accum.TXN_BILL_RAW_COST = nvl(accum.TXN_BILL_RAW_COST,0) + nvl(FIN7.TXN_BILL_RAW_COST,0)
,accum.TXN_BRDN_COST = nvl(accum.TXN_BRDN_COST,0) + nvl(FIN7.TXN_BRDN_COST,0)
,accum.TXN_BILL_BRDN_COST = nvl(accum.TXN_BILL_BRDN_COST,0) + nvl(FIN7.TXN_BILL_BRDN_COST,0)
,accum.TXN_REVENUE = nvl(accum.TXN_REVENUE,0) + nvl(FIN7.TXN_REVENUE,0)
,accum.PRJ_RAW_COST = nvl(accum.PRJ_RAW_COST,0) + nvl(FIN7.PRJ_RAW_COST,0)
,accum.PRJ_BILL_RAW_COST = nvl(accum.PRJ_BILL_RAW_COST,0) + nvl(FIN7.PRJ_BILL_RAW_COST,0)
,accum.PRJ_BRDN_COST = nvl(accum.PRJ_BRDN_COST,0) + nvl(FIN7.PRJ_BRDN_COST,0)
,accum.PRJ_BILL_BRDN_COST = nvl(accum.PRJ_BILL_BRDN_COST,0) + nvl(FIN7.PRJ_BILL_BRDN_COST,0)
,accum.PRJ_REVENUE = nvl(accum.PRJ_REVENUE,0) + nvl(FIN7.PRJ_REVENUE,0)
,accum.POU_RAW_COST = nvl(accum.POU_RAW_COST,0) + nvl(FIN7.POU_RAW_COST,0)
,accum.POU_BILL_RAW_COST = nvl(accum.POU_BILL_RAW_COST,0) + nvl(FIN7.POU_BILL_RAW_COST,0)
,accum.POU_BRDN_COST = nvl(accum.POU_BRDN_COST,0) + nvl(FIN7.POU_BRDN_COST,0)
,accum.POU_BILL_BRDN_COST = nvl(accum.POU_BILL_BRDN_COST,0) + nvl(FIN7.POU_BILL_BRDN_COST,0)
,accum.POU_REVENUE = nvl(accum.POU_REVENUE,0) + nvl(FIN7.POU_REVENUE,0)
/* ,accum.POU_RAW_COST = nvl(accum.POU_RAW_COST,0) + nvl(FIN7.POU_RAW_COST,0)
,accum.POU_BILL_RAW_COST = nvl(accum.POU_BILL_RAW_COST,0) + nvl(FIN7.POU_BILL_RAW_COST,0)
,accum.POU_BRDN_COST = nvl(accum.POU_BRDN_COST,0) + nvl(FIN7.POU_BRDN_COST,0)
,accum.POU_BILL_BRDN_COST = nvl(accum.POU_BILL_BRDN_COST,0) + nvl(FIN7.POU_BILL_BRDN_COST,0)
,accum.POU_REVENUE = nvl(accum.POU_REVENUE,0) + nvl(FIN7.POU_REVENUE,0) */ -- Satya
,accum.EOU_RAW_COST = nvl(accum.EOU_RAW_COST,0) + nvl(FIN7.EOU_RAW_COST,0)
,accum.EOU_BILL_RAW_COST = nvl(accum.EOU_BILL_RAW_COST,0) + nvl(FIN7.EOU_BILL_RAW_COST,0)
,accum.EOU_BRDN_COST = nvl(accum.EOU_BRDN_COST,0) + nvl(FIN7.EOU_BRDN_COST,0)
,accum.EOU_BILL_BRDN_COST = nvl(accum.EOU_BILL_BRDN_COST,0) + nvl(FIN7.EOU_BILL_BRDN_COST,0)
,accum.G1_RAW_COST = nvl(accum.G1_RAW_COST,0) + nvl(FIN7.G1_RAW_COST,0)
,accum.G1_BILL_RAW_COST = nvl(accum.G1_BILL_RAW_COST,0) + nvl(FIN7.G1_BILL_RAW_COST,0)
,accum.G1_BRDN_COST = nvl(accum.G1_BRDN_COST,0) + nvl(FIN7.G1_BRDN_COST,0)
,accum.G1_BILL_BRDN_COST = nvl(accum.G1_BILL_BRDN_COST,0) + nvl(FIN7.G1_BILL_BRDN_COST,0)
,accum.G1_REVENUE = nvl(accum.G1_REVENUE,0) + nvl(FIN7.G1_REVENUE,0)
,accum.G2_RAW_COST = nvl(accum.G2_RAW_COST,0) + nvl(FIN7.G2_RAW_COST,0)
,accum.G2_BILL_RAW_COST = nvl(accum.G2_BILL_RAW_COST,0) + nvl(FIN7.G2_BILL_RAW_COST,0)
,accum.G2_BRDN_COST = nvl(accum.G2_BRDN_COST,0) + nvl(FIN7.G2_BRDN_COST,0)
,accum.G2_BILL_BRDN_COST = nvl(accum.G2_BILL_BRDN_COST,0) + nvl(FIN7.G2_BILL_BRDN_COST,0)
,accum.G2_REVENUE = nvl(accum.G2_REVENUE,0) + nvl(FIN7.G2_REVENUE,0)
,accum.QUANTITY = nvl(accum.QUANTITY,0) + nvl(FIN7.QUANTITY,0)
,accum.BILL_QUANTITY = nvl(accum.BILL_QUANTITY,0) + nvl(FIN7.BILL_QUANTITY,0)
,accum.LAST_UPDATE_DATE = FIN7.LAST_UPDATE_DATE
,accum.LAST_UPDATED_BY = FIN7.LAST_UPDATED_BY
,accum.LAST_UPDATE_LOGIN = FIN7.LAST_UPDATE_LOGIN
WHEN NOT MATCHED THEN
INSERT (
TXN_ACCUM_HEADER_ID,
RESOURCE_CLASS_ID,
PROJECT_ID,
PROJECT_ORG_ID,
PROJECT_ORGANIZATION_ID,
PROJECT_TYPE_CLASS,
TASK_ID,
ASSIGNMENT_ID,
NAMED_ROLE,
RECVR_PERIOD_TYPE,
RECVR_PERIOD_ID,
TXN_CURRENCY_CODE,
TXN_RAW_COST,
TXN_BILL_RAW_COST,
TXN_BRDN_COST,
TXN_BILL_BRDN_COST,
TXN_REVENUE,
PRJ_RAW_COST,
PRJ_BILL_RAW_COST,
PRJ_BRDN_COST,
PRJ_BILL_BRDN_COST,
PRJ_REVENUE,
POU_RAW_COST,
POU_BILL_RAW_COST,
POU_BRDN_COST,
POU_BILL_BRDN_COST,
POU_REVENUE,
EOU_RAW_COST,
EOU_BILL_RAW_COST,
EOU_BRDN_COST,
EOU_BILL_BRDN_COST,
G1_RAW_COST,
G1_BILL_RAW_COST,
G1_BRDN_COST,
G1_BILL_BRDN_COST,
G1_REVENUE,
G2_RAW_COST,
G2_BILL_RAW_COST,
G2_BRDN_COST,
G2_BILL_BRDN_COST,
G2_REVENUE,
QUANTITY,
BILL_QUANTITY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN
)
values
(
FIN7.TXN_ACCUM_HEADER_ID,
FIN7.RESOURCE_CLASS_ID,
FIN7.PROJECT_ID,
FIN7.PROJECT_ORG_ID,
FIN7.PROJECT_ORGANIZATION_ID,
FIN7.PROJECT_TYPE_CLASS,
FIN7.TASK_ID,
FIN7.ASSIGNMENT_ID,
FIN7.NAMED_ROLE,
FIN7.RECVR_PERIOD_TYPE,
FIN7.RECVR_PERIOD_ID,
FIN7.TXN_CURRENCY_CODE,
FIN7.TXN_RAW_COST,
FIN7.TXN_BILL_RAW_COST,
FIN7.TXN_BRDN_COST,
FIN7.TXN_BILL_BRDN_COST,
FIN7.TXN_REVENUE,
FIN7.PRJ_RAW_COST,
FIN7.PRJ_BILL_RAW_COST,
FIN7.PRJ_BRDN_COST,
FIN7.PRJ_BILL_BRDN_COST,
FIN7.PRJ_REVENUE,
FIN7.POU_RAW_COST,
FIN7.POU_BILL_RAW_COST,
FIN7.POU_BRDN_COST,
FIN7.POU_BILL_BRDN_COST,
FIN7.POU_REVENUE,
FIN7.EOU_RAW_COST,
FIN7.EOU_BILL_RAW_COST,
FIN7.EOU_BRDN_COST,
FIN7.EOU_BILL_BRDN_COST,
FIN7.G1_RAW_COST,
FIN7.G1_BILL_RAW_COST,
FIN7.G1_BRDN_COST,
FIN7.G1_BILL_BRDN_COST,
FIN7.G1_REVENUE,
FIN7.G2_RAW_COST,
FIN7.G2_BILL_RAW_COST,
FIN7.G2_BRDN_COST,
FIN7.G2_BILL_BRDN_COST,
FIN7.G2_REVENUE,
FIN7.QUANTITY,
FIN7.BILL_QUANTITY,
FIN7.LAST_UPDATE_DATE,
FIN7.LAST_UPDATED_BY,
FIN7.CREATION_DATE,
FIN7.CREATED_BY,
FIN7.LAST_UPDATE_LOGIN );
delete
from PJI_FP_TXN_ACCUM1 bal
where bal.PROJECT_ID = p_project_id;
insert /*+ append parallel(bal_i) */ into PJI_FP_TXN_ACCUM1 bal_i
(
TXN_ACCUM_HEADER_ID,
PROJECT_ID,
PROJECT_ORG_ID,
PROJECT_ORGANIZATION_ID,
TASK_ID,
RECVR_PERIOD_TYPE,
RECVR_PERIOD_ID,
TXN_CURRENCY_CODE,
TXN_SUP_INV_COMMITTED_COST,
TXN_PO_COMMITTED_COST,
TXN_PR_COMMITTED_COST,
TXN_OTH_COMMITTED_COST,
PRJ_SUP_INV_COMMITTED_COST,
PRJ_PO_COMMITTED_COST,
PRJ_PR_COMMITTED_COST,
PRJ_OTH_COMMITTED_COST,
POU_SUP_INV_COMMITTED_COST,
POU_PO_COMMITTED_COST,
POU_PR_COMMITTED_COST,
POU_OTH_COMMITTED_COST,
EOU_SUP_INV_COMMITTED_COST,
EOU_PO_COMMITTED_COST,
EOU_PR_COMMITTED_COST,
EOU_OTH_COMMITTED_COST,
G1_SUP_INV_COMMITTED_COST,
G1_PO_COMMITTED_COST,
G1_PR_COMMITTED_COST,
G1_OTH_COMMITTED_COST,
G2_SUP_INV_COMMITTED_COST,
G2_PO_COMMITTED_COST,
G2_PR_COMMITTED_COST,
G2_OTH_COMMITTED_COST,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN
)
select
tmp7.TXN_ACCUM_HEADER_ID,
tmp7.PROJECT_ID,
tmp7.PROJECT_ORG_ID,
tmp7.PROJECT_ORGANIZATION_ID,
tmp7.TASK_ID,
tmp7.RECVR_PERIOD_TYPE,
tmp7.RECVR_PERIOD_ID,
tmp7.TXN_CURRENCY_CODE,
sum(tmp7.TXN_SUP_INV_COMMITTED_COST) TXN_SUP_INV_COMMITTED_COST,
sum(tmp7.TXN_PO_COMMITTED_COST) TXN_PO_COMMITTED_COST,
sum(tmp7.TXN_PR_COMMITTED_COST) TXN_PR_COMMITTED_COST,
sum(tmp7.TXN_OTH_COMMITTED_COST) TXN_OTH_COMMITTED_COST,
sum(tmp7.PRJ_SUP_INV_COMMITTED_COST) PRJ_SUP_INV_COMMITTED_COST,
sum(tmp7.PRJ_PO_COMMITTED_COST) PRJ_PO_COMMITTED_COST,
sum(tmp7.PRJ_PR_COMMITTED_COST) PRJ_PR_COMMITTED_COST,
sum(tmp7.PRJ_OTH_COMMITTED_COST) PRJ_OTH_COMMITTED_COST,
sum(tmp7.POU_SUP_INV_COMMITTED_COST) POU_SUP_INV_COMMITTED_COST,
sum(tmp7.POU_PO_COMMITTED_COST) POU_PO_COMMITTED_COST,
sum(tmp7.POU_PR_COMMITTED_COST) POU_PR_COMMITTED_COST,
sum(tmp7.POU_OTH_COMMITTED_COST) POU_OTH_COMMITTED_COST,
sum(tmp7.EOU_SUP_INV_COMMITTED_COST) EOU_SUP_INV_COMMITTED_COST,
sum(tmp7.EOU_PO_COMMITTED_COST) EOU_PO_COMMITTED_COST,
sum(tmp7.EOU_PR_COMMITTED_COST) EOU_PR_COMMITTED_COST,
sum(tmp7.EOU_OTH_COMMITTED_COST) EOU_OTH_COMMITTED_COST,
sum(tmp7.G1_SUP_INV_COMMITTED_COST) G1_SUP_INV_COMMITTED_COST,
sum(tmp7.G1_PO_COMMITTED_COST) G1_PO_COMMITTED_COST,
sum(tmp7.G1_PR_COMMITTED_COST) G1_PR_COMMITTED_COST,
sum(tmp7.G1_OTH_COMMITTED_COST) G1_OTH_COMMITTED_COST,
sum(tmp7.G2_SUP_INV_COMMITTED_COST) G2_SUP_INV_COMMITTED_COST,
sum(tmp7.G2_PO_COMMITTED_COST) G2_PO_COMMITTED_COST,
sum(tmp7.G2_PR_COMMITTED_COST) G2_PR_COMMITTED_COST,
sum(tmp7.G2_OTH_COMMITTED_COST) G2_OTH_COMMITTED_COST,
/* 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 */
sysdate LAST_UPDATE_DATE,
-1 LAST_UPDATED_BY,
sysdate CREATION_DATE,
-1 CREATED_BY,
-1 LAST_UPDATE_LOGIN
from
PJI_FM_AGGR_FIN7 tmp7
where
tmp7.PROJECT_ID = p_project_id
group by
tmp7.TXN_ACCUM_HEADER_ID,
tmp7.PROJECT_ID,
tmp7.PROJECT_ORG_ID,
tmp7.PROJECT_ORGANIZATION_ID,
tmp7.TASK_ID,
tmp7.RECVR_PERIOD_TYPE,
tmp7.RECVR_PERIOD_ID,
tmp7.TXN_CURRENCY_CODE;
delete from PJI_FM_AGGR_FIN7
where project_id = p_project_id;
g_rbs_version_id_arr.delete;
g_rbs_version_id_index_arr.delete;
g_rbs_version_id_rule_arr.delete;
insert into pa_pjt_proj_batch
(
WORKER_ID,
PROJECT_ID,
PJI_PROJECT_STATUS,
EXTRACTION_TYPE,
EXTRACTION_STATUS,
PROJECT_TYPE,
PROJECT_ORG_ID,
PROJECT_ORGANIZATION_ID,
PROJECT_TYPE_CLASS,
PRJ_CURRENCY_CODE,
PROJECT_ACTIVE_FLAG
)
select
l_worker_id,
prj.PROJECT_ID,
null PJI_PROJECT_STATUS,
null EXTRACTION_TYPE,
NULL,
prj.PROJECT_TYPE,
prj.ORG_ID PROJECT_ORG_ID,
NULL,
NULL,
prj.PROJECT_CURRENCY_CODE,
'Y' PROJECT_ACTIVE_FLAG
from
PA_PROJECTS_ALL prj
where
prj.ORG_ID = nvl(p_operating_unit, prj.ORG_ID ) and
prj.SEGMENT1 between nvl(p_from_project, prj.SEGMENT1) and
nvl(p_to_project, prj.SEGMENT1) AND
PA_PROJECT_UTILS.CHECK_PRJ_STUS_ACTION_ALLOWED
(prj.PROJECT_STATUS_CODE, 'STATUS_REPORTING') = 'Y'
and prj.pjt_rollup_enabled_flag = 'Y' ;
insert into pa_pjt_events_02
(
WORKER_ID,
LOG_ROWID,
EVENT_TYPE,
EVENT_ID,
EVENT_OBJECT,
OPERATION_TYPE,
STATUS,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
ATTRIBUTE16,
ATTRIBUTE17,
ATTRIBUTE18,
ATTRIBUTE19,
ATTRIBUTE20,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN
)
select /*+ index(log PA_PJI_PROJ_EVENTS_LOG_N1) */
l_worker_id ,
log.rowid LOG_ROWID,
log.EVENT_TYPE,
log.EVENT_ID,
log.EVENT_OBJECT,
log.OPERATION_TYPE,
log.STATUS,
log.ATTRIBUTE_CATEGORY,
log.ATTRIBUTE1,
nvl(log.ATTRIBUTE2, log.EVENT_OBJECT) ATTRIBUTE2,
log.ATTRIBUTE3,
log.ATTRIBUTE4,
log.ATTRIBUTE5,
log.ATTRIBUTE6,
log.ATTRIBUTE7,
log.ATTRIBUTE8,
log.ATTRIBUTE9,
log.ATTRIBUTE10,
log.ATTRIBUTE11,
log.ATTRIBUTE12,
log.ATTRIBUTE13,
log.ATTRIBUTE14,
log.ATTRIBUTE15,
'Y' ATTRIBUTE16, -- project event flag
log.ATTRIBUTE17, -- chain identifier
log.ATTRIBUTE18, -- push chain flag
prj.project_id, -- project id
log.ATTRIBUTE20,
sysdate LAST_UPDATE_DATE,
-1 LAST_UPDATED_BY,
sysdate CREATION_DATE,
-1 CREATED_BY,
-1 LAST_UPDATE_LOGIN
from
PA_PJI_PROJ_EVENTS_LOG log ,
PA_RBS_PRJ_ASSIGNMENTS rbs_asg,
PA_PROJECTS_ALL prj
where
log.EVENT_TYPE in ('RBS_PUSH', 'RBS_DELETE') and
rbs_asg.RBS_VERSION_ID in (log.EVENT_OBJECT, log.ATTRIBUTE2) and
rbs_asg.RBS_HEADER_ID = p_rbs_header_id and
rbs_asg.PROJECT_ID = prj.PROJECT_ID and
prj.pjt_rollup_enabled_flag = 'Y' and
log.pjt_rollup_flag = 'N'
UNION ALL
select /*+ index(log PA_PJI_PROJ_EVENTS_LOG_N1) */
l_worker_id ,
log.rowid LOG_ROWID,
log.EVENT_TYPE,
log.EVENT_ID,
log.EVENT_OBJECT,
log.OPERATION_TYPE,
log.STATUS,
log.ATTRIBUTE_CATEGORY,
log.ATTRIBUTE1,
nvl(log.ATTRIBUTE2, log.EVENT_OBJECT) ATTRIBUTE2,
log.ATTRIBUTE3,
log.ATTRIBUTE4,
log.ATTRIBUTE5,
log.ATTRIBUTE6,
log.ATTRIBUTE7,
log.ATTRIBUTE8,
log.ATTRIBUTE9,
log.ATTRIBUTE10,
log.ATTRIBUTE11,
log.ATTRIBUTE12,
log.ATTRIBUTE13,
log.ATTRIBUTE14,
log.ATTRIBUTE15,
'Y' ATTRIBUTE16, -- project event flag
log.ATTRIBUTE17, -- chain identifier
log.ATTRIBUTE18, -- push chain flag
prj.project_id, -- project id
log.ATTRIBUTE20 ,
sysdate LAST_UPDATE_DATE,
-1 LAST_UPDATED_BY,
sysdate CREATION_DATE,
-1 CREATED_BY,
-1 LAST_UPDATE_LOGIN
from
pa_pjt_events_02 log ,
PA_RBS_PRJ_ASSIGNMENTS rbs_asg,
PA_PROJECTS_ALL prj
where
log.EVENT_TYPE in ('RBS_PUSH', 'RBS_DELETE') and
rbs_asg.RBS_VERSION_ID in (log.EVENT_OBJECT, log.ATTRIBUTE2) and
rbs_asg.RBS_HEADER_ID = p_rbs_header_id and
rbs_asg.PROJECT_ID = prj.PROJECT_ID and
prj.pjt_rollup_enabled_flag = 'Y' and
log.ATTRIBUTE16 = 'N' ;
update PA_PJI_PROJ_EVENTS_LOG log
set pjt_rollup_flag = 'Y'
where log.EVENT_TYPE in ('RBS_PUSH', 'RBS_DELETE')
and exists ( select 'x' from
PA_RBS_PRJ_ASSIGNMENTS rbs_asg,
PA_PROJECTS_ALL prj
where
rbs_asg.RBS_VERSION_ID in (log.EVENT_OBJECT, log.ATTRIBUTE2) and
rbs_asg.RBS_HEADER_ID = p_rbs_header_id and
rbs_asg.PROJECT_ID = prj.PROJECT_ID and
prj.pjt_rollup_enabled_flag = 'Y' )
and log.pjt_rollup_flag = 'N' ;
delete from pa_pjt_events_02 log
where log.EVENT_TYPE in ('RBS_PUSH', 'RBS_DELETE')
and log.ATTRIBUTE16 = 'N' ;
insert into pa_pjt_proj_batch
(
WORKER_ID,
PROJECT_ID,
PJI_PROJECT_STATUS,
EXTRACTION_TYPE,
EXTRACTION_STATUS,
PROJECT_TYPE,
PROJECT_ORG_ID,
PROJECT_ORGANIZATION_ID,
PROJECT_TYPE_CLASS,
PRJ_CURRENCY_CODE,
PROJECT_ACTIVE_FLAG
)
select /*+ ordered
index(log, PA_PJI_PROJ_EVENTS_LOG_N1)
index(rbs_asg, PA_RBS_PRJ_ASSIGNMENTS_N1) */
distinct
l_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
from
pa_pjt_events_02 log,
PA_RBS_PRJ_ASSIGNMENTS rbs_asg,
PA_PROJECTS_ALL prj,
PA_PROJECT_TYPES_ALL pt
where
log.EVENT_TYPE in ('RBS_PUSH', 'RBS_DELETE') and
rbs_asg.RBS_VERSION_ID in (log.EVENT_OBJECT, log.ATTRIBUTE2) and
rbs_asg.RBS_HEADER_ID = p_rbs_header_id and
rbs_asg.PROJECT_ID = prj.PROJECT_ID and
prj.PROJECT_TYPE = pt.PROJECT_TYPE
and prj.pjt_rollup_enabled_flag = 'Y' ;
select project_id , log.event_type event_type, to_number(log.event_object) rbs_version_id
from pa_pjt_proj_batch b1 , pa_pjt_events_02 log --satya -- delete old_rbs_version from fact -- delete from pa_pjt_events and pa_pjt_events_02
where p_calling_mode = 'RBS'
and b1.worker_id = c_worker_id
and b1.project_id = log.ATTRIBUTE19
and log.event_type = 'RBS_PUSH'
union all
select project_id , 'AAA' event_type, -1 rbs_version_id
from pa_pjt_proj_batch b1
where p_calling_mode IN ( 'FULL','INCREMENTAL' )
and b1.worker_id = c_worker_id ;
select distinct assign.rbs_version_id , nvl(log.event_type,'AAA') event_type
from PA_RBS_PRJ_ASSIGNMENTS ASSIGN, pa_rbs_versions_b rbsv, pa_pjt_events log
where ASSIGN.project_id = p_project_id
and ASSIGN.reporting_usage_flag = 'Y'
AND ASSIGN.rbs_version_id = rbsv.rbs_version_id
AND rbsv.current_reporting_flag = 'Y'
AND log.attribute1 (+)= ASSIGN.project_id
AND log.event_type (+)= 'RBS_ASSOC'
AND log.event_object (+) = assign.rbs_version_id;
select prj.rbs_version_id , log.event_type event_type
from PA_RBS_PRJ_ASSIGNMENTS prj, pa_pjt_events log, pa_rbs_versions_b rbsv
where prj.project_id = p_project_id
and log.event_type = 'RBS_ASSOC'
and log.attribute1 = prj.project_id
and reporting_usage_flag = 'Y'
AND prj.rbs_version_id = rbsv.rbs_version_id
AND rbsv.current_reporting_flag = 'Y'
order by 2;
select
bv.budget_version_id , fpo.rbs_version_id
from
pa_budget_versions bv ,
pa_proj_fp_options fpo
where
bv.project_id = fpo.project_id
AND bv.fin_plan_type_id is not NULL -- Old budgets model data is not picked up with this condition.
AND fpo.project_id = c_project_id
AND bv.fin_plan_type_id = fpo.fin_plan_type_id
AND bv.budget_version_id = fpo.fin_plan_version_id
AND fpo.fin_plan_option_level_code = 'PLAN_VERSION' -- Other values are: plan type and project.
AND bv.version_type IN ( 'ALL' , 'COST' , 'REVENUE') -- Eg of other version type is ORG_FORECAST.
AND fpo.rbs_version_id = c_rbs_version_id ;
select
bv.budget_version_id , nvl(fpo.rbs_version_id ,-1) rbs_version_id
from
pa_budget_versions bv ,
pa_proj_fp_options fpo
where
bv.project_id = fpo.project_id
AND bv.fin_plan_type_id is not NULL -- Old budgets model data is not picked up with this condition.
AND fpo.project_id = c_project_id
AND bv.fin_plan_type_id = fpo.fin_plan_type_id
AND bv.budget_version_id = fpo.fin_plan_version_id
AND fpo.fin_plan_option_level_code = 'PLAN_VERSION' -- Other values are: plan type and project.
AND bv.version_type IN ( 'ALL' , 'COST' , 'REVENUE')
order by fpo.rbs_version_id ;-- Eg of other version type is ORG_FORECAST.
SELECT DECODE(p_calling_mode, 'INCREMENTAL', 'INCREMENTAL', 'FULL', 'INITIAL', 'FULL')
INTO l_extraction_type_wp
FROM DUAL;
l_project_id_tbl.delete;
l_project_id_tbl.delete;
SELECT object_id ,
SYS_CONNECT_BY_PATH(object_id, '/') connect_path
FROM pa_ppr_obj_tmp r
START WITH r.parent_object_id is null CONNECT BY
PRIOR r.object_id = r.parent_object_id ;
delete from pa_ppr_obj_tmp2;
insert into pa_ppr_obj_tmp2 ( object_id , parent_object_id )
select rbs_element_id , nvl(parent_element_id,0)
from pa_rbs_elements
where rbs_version_id = g_rbs_version_id_arr(i) ;
UPDATE pa_rbs_elements
SET connect_path = l_connect_path_arr(k)
WHERE rbs_element_id = l_object_id_arr(k)
and rbs_version_id = g_rbs_version_id_arr(i);
update pa_pjt_proj_batch
set PJI_PROJECT_STATUS = 'Y'
where WORKER_ID = p_worker_id and
PROJECT_ID in (select PROJECT_ID
from PA_PROJECTS_ALL
where STRUCTURE_SHARING_CODE = 'SHARE_FULL');
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
)
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
FROM pji_fm_aggr_fin7 tmp
,pa_proj_fp_options ppfo
WHERE
tmp.PROJECT_ID= p_project_id 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
)
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
FROM pji_fp_txn_accum tmp
,pa_proj_fp_options ppfo
WHERE
tmp.PROJECT_ID= p_project_id 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';
select
distinct
asg.RBS_VERSION_ID
from
pa_pjt_proj_batch map,
PA_RBS_PRJ_ASSIGNMENTS asg
where
map.WORKER_ID = p_worker_id and
asg.PROJECT_ID = map.PROJECT_ID and
asg.REPORTING_USAGE_FLAG = 'Y'
and map.project_id = p_project_id ;
select
distinct
ATTRIBUTE20 RBS_HEADER_ID,
ATTRIBUTE2 OLD_RBS_VERSION_ID,
EVENT_OBJECT NEW_RBS_VERSION_ID
from
pa_pjt_events
where
WORKER_ID = p_worker_id and
EVENT_TYPE = 'RBS_PUSH' AND
attribute19 = p_project_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
1,
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
from pa_pjt_proj_batch
where worker_id = p_worker_id and
project_id = p_project_id;
/* delete from PJI_PJP_PROJ_BATCH_MAP
where worker_id = p_worker_id and
project_id = p_project_id; */
select /*+ ordered index(log PA_PJI_PROJ_EVENTS_LOG_N1) use_hash(log) */ --Bug 7516507
distinct
log.ROWID LOG_ROWID,
log.EVENT_TYPE,
log.EVENT_ID,
log.EVENT_OBJECT,
log.OPERATION_TYPE,
log.STATUS,
log.ATTRIBUTE_CATEGORY,
log.ATTRIBUTE1,
to_char(decode(log.EVENT_TYPE,
'RBS_PRG', ver.RBS_HEADER_ID,
'RBS_ASSOC', ver.RBS_HEADER_ID,
log.ATTRIBUTE2)) ATTRIBUTE2,
log.ATTRIBUTE3,
log.ATTRIBUTE4,
log.ATTRIBUTE5,
log.ATTRIBUTE6,
log.ATTRIBUTE7,
log.ATTRIBUTE8,
log.ATTRIBUTE9,
log.ATTRIBUTE10,
log.ATTRIBUTE11,
log.ATTRIBUTE12,
log.ATTRIBUTE13,
log.ATTRIBUTE14,
log.ATTRIBUTE15,
log.ATTRIBUTE16,
log.ATTRIBUTE17,
log.ATTRIBUTE18,
log.ATTRIBUTE19,
log.ATTRIBUTE20
from
PA_PJI_PROJ_EVENTS_LOG log,
PA_RBS_VERSIONS_B ver
where
-- map.WORKER_ID = p_worker_id and
log.EVENT_TYPE in (-- 'WBS_CHANGE', -- disable bulk processing
-- 'WBS_PUBLISH', -- of WBS events
'RBS_ASSOC'
---- ,'RBS_PRG' -- OLAP OPEN ITEM : to support programs with new model. Need to resolve this.
) and
log.ATTRIBUTE1 = p_project_id and
log.EVENT_OBJECT = ver.RBS_VERSION_ID (+) and
p_calling_mode = 'INCREMENTAL'
union all
select /*+ ordered index(log
PA_PJI_PROJ_EVENTS_LOG_N1) use_hash(log) */ --Bug 7516507
distinct
log.ROWID LOG_ROWID,
log.EVENT_TYPE,
log.EVENT_ID,
log.EVENT_OBJECT,
log.OPERATION_TYPE,
log.STATUS,
log.ATTRIBUTE_CATEGORY,
log.ATTRIBUTE1,
log.ATTRIBUTE2,
log.ATTRIBUTE3,
log.ATTRIBUTE4,
log.ATTRIBUTE5,
log.ATTRIBUTE6,
log.ATTRIBUTE7,
log.ATTRIBUTE8,
log.ATTRIBUTE9,
log.ATTRIBUTE10,
log.ATTRIBUTE11,
log.ATTRIBUTE12,
log.ATTRIBUTE13,
log.ATTRIBUTE14,
log.ATTRIBUTE15,
log.ATTRIBUTE16,
log.ATTRIBUTE17,
log.ATTRIBUTE18,
log.ATTRIBUTE19,
log.ATTRIBUTE20
from
-- pa_pjt_proj_batch map,
PA_PJI_PROJ_EVENTS_LOG log
where
1 = 2 AND --- OLAP OPEN ITEM : to support programs with new model. Need to resolve this,
-- map.WORKER_ID = p_worker_id and
log.EVENT_TYPE = 'PRG_CHANGE' and
log.EVENT_OBJECT = -1 and
log.ATTRIBUTE1 = p_project_id;
select /*+ index(log PA_PJI_PROJ_EVENTS_LOG_N1) */
distinct
log.ROWID LOG_ROWID,
log.EVENT_TYPE,
log.EVENT_ID,
log.EVENT_OBJECT,
log.OPERATION_TYPE,
log.STATUS,
log.ATTRIBUTE_CATEGORY,
log.ATTRIBUTE1,
nvl(log.ATTRIBUTE2, log.EVENT_OBJECT) ATTRIBUTE2,
log.ATTRIBUTE3,
log.ATTRIBUTE4,
log.ATTRIBUTE5,
log.ATTRIBUTE6,
log.ATTRIBUTE7,
log.ATTRIBUTE8,
log.ATTRIBUTE9,
log.ATTRIBUTE10,
log.ATTRIBUTE11,
log.ATTRIBUTE12,
log.ATTRIBUTE13,
log.ATTRIBUTE14,
log.ATTRIBUTE15,
'N' ATTRIBUTE16, -- project event flag
log.ATTRIBUTE17, -- chain identifier
log.ATTRIBUTE18, -- push chain flag
log.ATTRIBUTE19, -- project id
ver.RBS_HEADER_ID ATTRIBUTE20 -- rbs header
from
pa_pjt_events_02 log,
(
select
distinct
asg.RBS_HEADER_ID,
asg.RBS_VERSION_ID
from
-- pa_pjt_proj_batch map,
PA_RBS_PRJ_ASSIGNMENTS asg
where
-- map.WORKER_ID = p_worker_id and
asg.PROJECT_ID = p_project_id
) ver
where
log.EVENT_TYPE IN ( 'RBS_PUSH' , 'RBS_DELETE' ) and
ver.RBS_VERSION_ID in (log.EVENT_OBJECT, log.ATTRIBUTE2) AND
log.attribute19 = p_project_id ;
insert into pa_pjt_events
(
WORKER_ID,
LOG_ROWID,
EVENT_TYPE,
EVENT_ID,
EVENT_OBJECT,
OPERATION_TYPE,
STATUS,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
ATTRIBUTE16,
ATTRIBUTE17,
ATTRIBUTE18,
ATTRIBUTE19,
ATTRIBUTE20,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN
)
values
(
p_worker_id, -- satya
c.LOG_ROWID,
c.EVENT_TYPE,
c.EVENT_ID,
c.EVENT_OBJECT,
c.OPERATION_TYPE,
c.STATUS,
c.ATTRIBUTE_CATEGORY,
c.ATTRIBUTE1,
c.ATTRIBUTE2,
c.ATTRIBUTE3,
c.ATTRIBUTE4,
c.ATTRIBUTE5,
c.ATTRIBUTE6,
c.ATTRIBUTE7,
c.ATTRIBUTE8,
c.ATTRIBUTE9,
c.ATTRIBUTE10,
c.ATTRIBUTE11,
c.ATTRIBUTE12,
c.ATTRIBUTE13,
c.ATTRIBUTE14,
c.ATTRIBUTE15,
c.ATTRIBUTE16,
c.ATTRIBUTE17,
c.ATTRIBUTE18,
c.ATTRIBUTE19,
c.ATTRIBUTE20,
sysdate ,
-1 ,
sysdate ,
-1 ,
-1
);
delete
from PA_PJI_PROJ_EVENTS_LOG a
where ROWID = c.LOG_ROWID;
insert into pa_pjt_events
(
WORKER_ID,
LOG_ROWID,
EVENT_TYPE,
EVENT_ID,
EVENT_OBJECT,
OPERATION_TYPE,
STATUS,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
ATTRIBUTE16,
ATTRIBUTE17,
ATTRIBUTE18,
ATTRIBUTE19,
ATTRIBUTE20,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN
)
values
(
p_worker_id, --satya
c.LOG_ROWID,
c.EVENT_TYPE,
c.EVENT_ID,
c.EVENT_OBJECT,
c.OPERATION_TYPE,
c.STATUS,
c.ATTRIBUTE_CATEGORY,
c.ATTRIBUTE1,
c.ATTRIBUTE2,
c.ATTRIBUTE3,
c.ATTRIBUTE4,
c.ATTRIBUTE5,
c.ATTRIBUTE6,
c.ATTRIBUTE7,
c.ATTRIBUTE8,
c.ATTRIBUTE9,
c.ATTRIBUTE10,
c.ATTRIBUTE11,
c.ATTRIBUTE12,
c.ATTRIBUTE13,
c.ATTRIBUTE14,
c.ATTRIBUTE15,
c.ATTRIBUTE16,
c.ATTRIBUTE17,
c.ATTRIBUTE18,
c.ATTRIBUTE19,
c.ATTRIBUTE20,
sysdate ,
-1 ,
sysdate ,
-1 ,
-1
);
delete
from pa_pjt_events_02 a
where ROWID = c.LOG_ROWID;