17,086
社区成员
发帖
与我相关
我的任务
分享
create or replace
FUNCTION SYS_FILL_GS
(
evaluationSet in number
)
create or replace
FUNCTION SYS_FILL_GS
(
evaluationSet in number
)
RETURN VARCHAR2 AS
PRAGMA AUTONOMOUS_TRANSACTION;
v_test number(8);
v_masterCounts number(3) ;--部门主管个数
v_lastOfEvaluationSet date ;
v_index number(3);-- for循环指针
v_currentMasterID varchar2(200);--当前部门主管id
BEGIN
select evaluation_date into v_lastOfEvaluationSet from dep_evaluation_set where PK_auto_ID=evaluationSet;
select count(*) into v_test from dep_GS_examin_sup where FK_dep_evaluation_set_ID = evaluationSet;
if v_test = 0 then
-- 1插入督查督导的所有内容(行政正职,书记,主管领导打分表)
insert into dep_GS_examin_sup (FK_sup_record_ID,FK_dep_evaluation_set_ID)
select r.PK_auto_ID, evaluationSet
from sup_record r,sup_Info i
where r.FK_SPI_ID = i.PK_SUP_ID and Is_GSProject = 1 and
last_day(workOut_date) = last_day(v_lastOfEvaluationSet);
--2 插入月综合计划的所有内容(行政正职,书记,主管领导打分表)
insert into dep_GS_examin_complex (FK_complex_plan_detail_ID,FK_dep_evaluation_set_ID)
select d.PK_auto_ID ,evaluationSet
from complex_plan_detail d,complex_month_plan c
where d.FK_complex_ID = c.PK_auto_ID and is_GS_plan = 1
and finish_state_check=3 and last_day(d.end_date) =last_day(v_lastOfEvaluationSet);
--3其它领导打分表打分表 的插入
select count(*) into v_masterCounts from (select distinct(FK_dpt_Master) from department);
for v_index in 1..v_masterCounts loop
--选出一个主管领导
select FK_dpt_Master into v_currentMasterID from
(select FK_dpt_Master,rownum rn from ( select distinct(FK_dpt_Master) from department))
where rn = v_index;
--插入这个主管领导可以打分的督查督导内容
insert into dep_GS_other_charger_sup(FK_dep_evaluation_set_ID,FK_employee_ID,score,FK_sup_record_ID)
select evaluationSet,v_currentMasterID,80,r.PK_auto_ID
from sup_record r,sup_Info i
where r.FK_SPI_ID = i.PK_SUP_ID and Is_GSProject = 1 and
SYS_MASTER_CAN_SCORE_BY_ID(v_currentMasterID,i.cooperate_Dep) > 0
and last_day(workOut_date) = last_day(v_lastOfEvaluationSet);
-- 插入这个主管领导可以打分的月综合计划
insert into dep_GS_other_charger_complex(FK_dep_evaluation_set_ID,FK_employee_ID,score,FK_complex_plan_detail_ID)
select evaluationSet,v_currentMasterID,80 ,d.PK_auto_ID
from complex_plan_detail d,complex_month_plan c
where d.FK_complex_ID = c.PK_auto_ID and is_GS_plan = 1 and SYS_MASTER_CAN_SCORE_By_Name(v_currentMasterID,d.cooperate_dept)>0
and finish_state_check=3 and last_day(d.end_date) =(v_lastOfEvaluationSet);
end loop;
-- 插入月综合计划的所有内容(其它领导打分表打分表)
end if;
COMMIT;
RETURN NULL;
END SYS_FILL_GS;
create or repacle function fun1(vn in varchar2) return varchar2
as
v varchar2;
begin
......
return(v);
end;