Stored procedure:
CREATE OR REPLACE PROCEDURE NIKU.SP_name
AS
/******************************************************************************
NAME: SP_name
PURPOSE: To fill in the needed fields prior to XOG of TriZetto Timesheet
REVISIONS:
Ver Date Author Description
--------- ---------- --------------- ------------------------------------
1.0 1/20/2013 r616330 1. Created this procedure.
NOTES:
--SP_name created by Lowell Wetzel 01/12/2013
--This populates the columns needed in order to XOG timesheet values into the system
Automatically available Auto Replace Keywords:
Object Name: SP_name
Sysdate: 1/20/2013
Date and Time: 1/20/2013, 7:08:40 PM, and 1/20/2013 7:08:40 PM
Username:
Table Name: Table_name
******************************************************************************/
--First Populate the PRTIMEPERIOD
cursor c_timeperiod is
select WEEK_START
from TRG_TRZ_TIME_STAGE
where WEEK_START is not null;
--Change in Requirements removed "TriZetto resources" from Cambia system
--Removed, Resource ID is a "static" Pooled Resource and all hours are to be grouped into this pooled resource
--Next Populate the PRRESOURCEID
--cursor c_prresourceid is
-- select RESOURCEID
-- from TRG_TRZ_TIME_STAGE
-- where RESOURCEID is not null;
--Change in Requirements populates "project_id" from PCR (Work Order) value
--Next Populate the PRPROJECTID
cursor c_prproject is
select PRPROJECTID, TASKINTERNALTASKID, TASKPRCHARGECODEID, PCR
from TRG_TRZ_TIME_STAGE
where PCR is not null;
--Next Populate the TASKINTERNALTASKID, TASKPRCHARGECODEID
--Removed due to values being set by PCR (Work Order) value
--cursor c_prtask is
-- select TASK_ID, PROJECT_ID
-- from TRG_TRZ_TIME_STAGE
-- where TASKID is not null
-- and PRPROJECTID is not null;
--Next Populate the TASKCHARGECODEID
cursor c_prchargecode is
select TASKPRCHARGECODEID
from TRG_TRZ_TIME_STAGE
where TASKPRCHARGECODEID is not null;
--Next Populate the ASSIGNMENTID
cursor c_prassignment is
select TASKINTERNALTASKID, PRRESOURCEID
from TRG_TRZ_TIME_STAGE
where TASKINTERNALTASKID is not null
and PRRESOURCEID is not null;
--Next Populate the PR_ROLE
cursor c_pr_role is
select PRROLE_ID
from TRG_TRZ_TIME_STAGE
where PRROLE_ID is not null;
--Next Populate the TIMESHEETID
cursor c_timesheetid is
select PRTIMEPERIODID, PRRESOURCEID
from TRG_TRZ_TIME_STAGE
where PRTIMEPERIODID is not null
and PRRESOURCEID is not null;
--Next Populate the TIMESHEETID
cursor c_timeshtstatver is
select TIMESHEETID
from TRG_TRZ_TIME_STAGE
where TIMESHEETID is not null;
--Finally, Populate TYPECODEID, TYPECODENAME
cursor c_typecode is
select PRRESOURCEID
from TRG_TRZ_TIME_STAGE
where PRRESOURCEID is not null;
BEGIN
--PRTIMEPERIOD
for c_timeperiod_rec in c_timeperiod loop
UPDATE (select T.week_start, T.prtimeperiodid FROM TRG_TRZ_TIME_STAGE T where T.week_start = c_timeperiod_rec.WEEK_START)
SET PRTIMEPERIODID = (select P.prid from prtimeperiod P where P.prstart = c_timeperiod_rec.WEEK_START);
end loop;
COMMIT;
--RESOURCEID
--Removed Due to use of "Pooled" Resource
--for c_prresourceid_rec in c_prresourceid loop
--UPDATE (select T.resourceid, T.prresourceid FROM TRG_TRZ_TIME_STAGE T where T.resourceid = c_prresourceid_rec.resourceid)
--SET PRRESOURCEID = (select R.id from srm_resources R where R.unique_name = c_prresourceid_rec.resourceid);
--end loop;
--COMMIT;
--PRPROJECTID
--Original
--for c_prproject_rec in c_prproject loop
--UPDATE (select T.PRPROJECTID, T.TASKINTERNALTASKID, T.TASKPRCHARGECODEID, T.PCR FROM TRG_TRZ_TIME_STAGE T
--where T.PCR = c_prproject_rec.PCR)
--SET (PRPROJECTID, TASKINTERNALTASKID, TASKPRCHARGECODEID) = (select p.ID, t.PRID, t.PRCHARGECODEID
--from srm_projects p inner join prtask t on t.PRPROJECTID = p.ID
--inner join ODF_CA_TASK tsk on tsk.ID = t.PRID
--Where t.IS_OPEN_TE = 1
--and tsk.TRG_PCR = c_prproject_rec.PCR);
--end loop;
for c_prproject_rec in c_prproject loop
UPDATE (select T.PRPROJECTID, T.TASKINTERNALTASKID, T.TASKPRCHARGECODEID, T.PCR, T.PROJECTID, T.TASKID FROM TRG_TRZ_TIME_STAGE T
where T.PCR = c_prproject_rec.PCR)
SET (PRPROJECTID, TASKINTERNALTASKID, TASKPRCHARGECODEID, PROJECTID, TASKID) = (select p.ID, t.PRID, t.PRCHARGECODEID, P.UNIQUE_NAME, T.PREXTERNALID
from srm_projects p inner join prtask t on t.PRPROJECTID = p.ID
inner join ODF_CA_TASK tsk on tsk.ID = t.PRID
Where t.IS_OPEN_TE = 1
and tsk.TRG_PCR = c_prproject_rec.PCR);
end loop;
COMMIT;
--Removed due to tasks identified by "PCR" field
--TASKINTERNALTASKID, TASKPRCHARGECODEID
--for c_prtask_rec in c_prtask loop
--UPDATE (select T.TASKINTERNALTASKID, T.TASKPRCHARGECODEID FROM TRG_TRZ_TIME_STAGE T
-- where T.taskid = c_prtask_rec.TASKID and T.prprojectid = c_prtask_rec.PRPROJECTID)
--SET (TASKINTERNALTASKID, TASKPRCHARGECODEID) = (select TSK.prid, TSK.prchargecodeid from prtask TSK
-- WHERE TSK.prexternalid = c_prtask_rec.TASKID AND TSK.prprojectid = c_prtask_rec.PRPROJECTID);
--end loop;
--COMMIT;
--TASKCHARGECODEID
for c_prchargecode_rec in c_prchargecode loop
UPDATE (select T.taskprchargecodeid, T.taskchargecodeid FROM TRG_TRZ_TIME_STAGE T where T.taskprchargecodeid = c_prchargecode_rec.TASKPRCHARGECODEID)
SET TASKCHARGECODEID = (select PC.prexternalid from prchargecode PC where PC.prid = c_prchargecode_rec.TASKPRCHARGECODEID);
end loop;
COMMIT;
--ASSIGNMENTID
for c_prassignment_rec in c_prassignment loop
UPDATE (select T.taskinternaltaskid, T.prresourceid, T.taskassignmentid, T.prrole_id FROM TRG_TRZ_TIME_STAGE T
where T.taskinternaltaskid = c_prassignment_rec.TASKINTERNALTASKID AND T.prresourceid = c_prassignment_rec.PRRESOURCEID)
SET (TASKASSIGNMENTID, PRROLE_ID) = (select PA.prid, PA.role_id from prassignment PA
where PA.prtaskid = c_prassignment_rec.TASKINTERNALTASKID and PA.prresourceid = c_prassignment_rec.PRRESOURCEID);
end loop;
COMMIT;
--ROLE_ID
for c_pr_role_rec in c_pr_role loop
UPDATE (select T.prrole_id, T.role_id FROM TRG_TRZ_TIME_STAGE T
where T.prrole_id = c_pr_role_rec.PRROLE_ID)
SET ROLE_ID = (select S.unique_name from PRJ_RESOURCES R, srm_resources S where S.id = R.prid
and R.prid = c_pr_role_rec.PRROLE_ID);
end loop;
COMMIT;
--TIMESHEETID
for c_timesheetid_rec in c_timesheetid loop
UPDATE (select T.timesheetid, T.prtimeperiodid, T.prresourceid, T.prstatus, T.prversion FROM TRG_TRZ_TIME_STAGE T
where T.prtimeperiodid = c_timesheetid_rec.PRTIMEPERIODID AND T.prresourceid = c_timesheetid_rec.PRRESOURCEID)
SET TIMESHEETID = (select max(TS.prid) from prtimesheet TS where TS.prtimeperiodid = c_timesheetid_rec.PRTIMEPERIODID
and TS.prresourceid = c_timesheetid_rec.PRRESOURCEID);
end loop;
COMMIT;
--Timesheet(PRSTATUS, PRVERSION)
for c_timeshtstatver_rec in c_timeshtstatver loop
UPDATE (select T.prstatus, T.prversion, T.timesheetid FROM TRG_TRZ_TIME_STAGE T
where T.timesheetid = c_timeshtstatver_rec.TIMESHEETID)
SET (PRSTATUS, PRVERSION) = (select TS.prstatus, TS.prversion from prtimesheet TS
where TS.prid = c_timeshtstatver_rec.TIMESHEETID);
end loop;
COMMIT;
--(TYPECODEID, TYPECODENAME)
for c_typecode_rec in c_typecode loop
UPDATE (select T.prresourceid, T.typecodeid, T.typecodename FROM TRG_TRZ_TIME_STAGE T
where T.prresourceid = c_typecode_rec.PRRESOURCEID)
SET (TYPECODEID, TYPECODENAME) = (SELECT P.prexternalid, P.prname
FROM PRJ_RESOURCES R left outer join PRTYPECODE P on R.prtypecodeid = P.prid
where R.prid = c_typecode_rec.PRRESOURCEID);
end loop;
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
raise_application_error (-20000,
'Error in SP_name - ' || SQLERRM
);
END SP_name; -- Procedure
/