hhtoserp_pm.sql

一段过程原码, 一段过程原码,一段过程原码.一段过程原码,

--update project_id--
update pm_workload a
set project_id = (
select project_id from pm_project b
where a.project_code = b.project_code)
commit

--update task_id--
update pm_workload a
set task_id = (
select task_id from pm_prj_phase b
where a.project_code = b.project_code and a.phase_code = b.phase_code)

commit
--update workload_type --
update pm_workload a
set workload_type ='development'
where a.project_code is not null
commit

--update project_code which worktype_id <> 0-- 
update pm_workload t
set project_code = ''
where t.worktype_id <> 0 and t.project_code is not null

commit
--update worktype_id to 5 which project_code is null

update pm_workload t
set worktype_id = 5 
where t.worktype_id = 0 and t.project_code is null
commit

--insert data to pm_prj_workload_lines--
truncate table serp_pm.pm_prj_workload_lines

insert into serp_pm.pm_prj_workload_lines(DESCRIPTION,PROJECT_FLAG,cost_time,project_phase,employee_id,project_id,task_id,workload_type,job_time,workload_id)
select WORK_DESC,CHECK_STATUS,WORK_HOURS,PHASE_CODE,EMPLOYEE_ID,PROJECT_ID,TASK_ID,WORKLOAD_TYPE,CREATE_ON,WORKLOAD_ID
from hh.pm_workload a 
where a.worktype_id=0 and a.project_code is not null
commit

--insert data to pm_prj_employee_date_lines
truncate table serp_pm.pm_prj_employ_day_lines

insert into serp_pm.pm_prj_employ_day_lines(EMPLOYEE_ID,job_type_id,job_time,cost_time,project_flag,description)
select EMPLOYEE_ID,WORKTYPE_ID,CREATE_ON,WORK_HOURS,CHECK_STATUS,WORK_DESC
from hh.pm_workload a
where a.worktype_id <> 0 and a.project_code is null
commit

--update employee_id from 5 to 6--
update serp_pm.pm_prj_workload_lines set employee_id = lpad(employee_id,6,0)
commit
update serp_pm.pm_prj_employ_day_lines set employee_id = lpad(employee_id,6,0)
commit
--add the missing date of workload or employee_daliy --
insert into serp_pm.pm_prj_employ_day_lines(employee_id,job_time,job_type_id)
(select distinct employee_id , job_time ,0 from serp_pm.pm_prj_workload_lines
minus
select distinct employee_id , job_time ,0 from serp_pm.pm_prj_employ_day_lines)

commit

--add the missing date of project --

insert into serp_pm.pm_prj_workload_lines(employee_id,job_time)
(
select distinct employee_id , job_time from serp_pm.pm_prj_employ_day_lines
minus
select distinct employee_id , job_time from serp_pm.pm_prj_workload_lines
)
commit