CREATE OR REPLACE FUNCTION "GET_EMPCENTER"(PI_EMPOID Number,
PI_GROUP Number,
PI_UNITID Number,
PS_YEAR Varchar2,
PS_MONTH Varchar2,
PS_DELIVERNUM Number)
--返回交流、挂职类人员所对应的成本部门(不一定是责任中心)
Return Number Is
li_unit1 Number := 0;
ls_date1 Char(6);
ls_month1 Char(3);
li_count1 Number;
ls_date Char(6);
ls_month Char(3);
li_count Number;
li_dutycenter Number := 0; --财务责任中心
li_orgunit Number := 0; --财务机构
li_id Number;
li_pid Number;
li_parentorgunit Number := 0; --机构oid
Begin
-- 先判断发放记录表中是否已经插入过本发放月的本员工 如果以后反映效率低,此处,以及以后的count(*)都可以改为使用游标代替,可以减少一半的查询量
select Count(*)
into li_count1
from tb_cnb_dutycenterjudge pay
where pay.c_month = PS_MONTH
and pay.c_year = PS_YEAR
and pay.c_unitid = PI_UNITID
and pay.c_groupid = PI_GROUP
and pay.c_empoid = PI_EMPOID
and pay.c_delivernum = PS_DELIVERNUM;
if li_count1 > 0 then
select pay.c_payunitid
into li_dutycenter
from tb_cnb_dutycenterjudge pay
where pay.c_month = PS_MONTH
and pay.c_year = PS_YEAR
and pay.c_unitid = PI_UNITID
and pay.c_groupid = PI_GROUP
and pay.c_empoid = PI_EMPOID
and pay.c_delivernum = PS_DELIVERNUM
and rownum = 1;
return li_dutycenter;
end if;
ls_month1 := '0' || ps_month;
ls_date1 := ps_year || substr(ls_month1, length(ls_month1) - 1);
--判断是否在个人责任中心中维护,如未维护则直接返回员工所在部门
Select Count(*)
Into li_count1
From TB_CNB_EMPDUTY
Where C_EMPID = PI_EMPOID
And C_GROUPID = PI_GROUP
And C_EFECTIVETIME <= ls_date1;
If li_count1 = 0 Then
li_unit1 := PI_UNITID;
End If;
if li_count1 > 0 Then
--如责任中心中维护个人信息,则取生效时间之前最近生效的一条记录
Select C_DUTYCENTREID
Into li_unit1
From (Select *
From TB_CNB_EMPDUTY
Where C_EMPID = PI_EMPOID
And C_GROUPID = PI_GROUP
And C_EFECTIVETIME <= ls_date1
Order By C_EFECTIVETIME Desc) a
Where rownum = 1;
End If;
--以上获取了员工的成本部门信息li_unit1,接下来要用员工的成本部门去获取员工所在的成本中心
ls_month := '0' || ps_month;
ls_date := ps_year || substr(ls_month, length(ls_month) - 1);
li_id := li_unit1;
--
--如果是责任中心,直接返回
Select Count(*)
Into li_count
From tb_cnb_dutycenter d
Where d.c_unitid = li_id
And d.c_efectivetime <= ls_date;
If li_count > 0 Then
Select a.c_unitid, a.c_compid
Into li_dutycenter, li_orgunit
From (Select *
From TB_CNB_DUTYCENTER DC
Where DC.C_UNITID = li_id
And C_EFECTIVETIME <= ls_date
Order By C_EFECTIVETIME Desc) a
Where rownum = 1;
insert into tb_cnb_dutycenterjudge
values
(PI_EMPOID,
PI_GROUP,
PI_UNITID,
PS_YEAR,
PS_MONTH,
li_dutycenter,
PS_DELIVERNUM);
Return li_dutycenter;
End If;
--先根据员工OID查出其对应的机构,放入li_parentorgunit中作为待查条件
select C_ORGID
into li_parentorgunit
from tb_inf_employee emp
where emp.c_oid = PI_EMPOID;
if li_parentorgunit <> PI_UNITID then
Loop
select count(*)
into li_count1
From tb_org_unitrelation
Where c_orgunitid = li_id
And c_hiberarchyid = 1
And c_status = 1;
if li_count1 = 0 then
insert into tb_cnb_dutycenterjudge
values
(PI_EMPOID,
PI_GROUP,
PI_UNITID,
PS_YEAR,
PS_MONTH,
PI_UNITID,
PS_DELIVERNUM);
Return PI_UNITID;
end if;
Select c_parentunitid
Into li_pid
From tb_org_unitrelation
Where c_orgunitid = li_id
And c_hiberarchyid = 1
And c_status = 1;
Exit When li_pid = li_parentorgunit;
--如果不是责任中心,循环查找直到找到责任中心
--先判断是否是责任中心
Select Count(*)
Into li_count
From tb_cnb_dutycenter d
Where d.c_unitid = li_pid
And d.c_efectivetime <= ls_date;
If li_count > 0 Then
Select a.c_unitid, a.c_compid
Into li_dutycenter, li_orgunit
From (Select *
From TB_CNB_DUTYCENTER DC
Where DC.C_UNITID = li_pid
And C_EFECTIVETIME <= ls_date
Order By C_EFECTIVETIME Desc) a
Where rownum = 1;
Exit;
Else
li_id := li_pid;
End If;
End Loop;
end if;
-- 如果最终没有得出责任中心的值,则把员工所在组织机构赋值给责任中心
if li_dutycenter = 0 Then
li_dutycenter := PI_UNITID;
End if;
insert into tb_cnb_dutycenterjudge
values
(PI_EMPOID,
PI_GROUP,
PI_UNITID,
PS_YEAR,
PS_MONTH,
li_dutycenter,
PS_DELIVERNUM);
Return li_dutycenter;
End GET_EMPCENTER;