NC休假加班存储过程

来源：互联网发布：古罗马实力知乎编辑：程序博客网时间：2024/05/16 15:37

CREATE OR REPLACE PACKAGE BODY PKG_LEAVE AS
/*新

-- Add/modify columns
alter table HR_EMPLOYEE_ENJOY_LEAVE add PK_ORG char(20);
update HR_EMPLOYEE_ENJOY_LEAVE h set h.PK_ORG=(select chg.pk_hrorg from bd_psndoc psn inner join hi_psnorg chg on psn.pk_psndoc=chg.pk_psndoc and chg.lastflag='Y' where psn.pk_psndoc=h.pk_psndoc)

ERP数据库连接：
-- Drop existing database link
drop public database link ERP_DBL;
-- Create database link
create public database link ERP_DBL connect to CCIS0601 identified by TEST using '10.200.135.13:1521/orc9';

delete from HR_ELEAVE_MAPPING;
delete from HR_EMPLOYEE_ENJOY_LEAVE;

select * from tbm_timeitem;

生产系统：
1002Z710000000021ZLJ 年假
10018G1000000000BM37 上年年假
10018G1000000000BM2T 调整年假
10018G1000000000BM2P 年假延期

10018G1000000000BM2N 司龄假
10018G1000000000BM39 上年司龄假
10018G1000000000BM2R 司龄假延期
10018G1000000000BM2V 调整司龄假

10018G1000000000BM2L 补休
10018G1000000000BM33 调整补休
10018G1000000000BM35 补休延期

*/

/*********************************************************************************************************************
NAME: Auto_Gen_Employee_Leave

Declare
i Integer;
Begin
dbms_job.submit(i,'PKG_LEAVE.Auto_Gen_Employee_Leave;',Sysdate,'TRUNC(LAST_DAY(SYSDATE))+1+1/24');
end;
---查看定时任务
select * from user_jobs;

HISOTORY:
DATE NAME DESCRIPTION
2016-03-25 River PROCEDURE CREATED
***********************************************************************************************************************/

/*********************************************************************************************************************
NAME: pro_Supplement_Leave_Mapping

Declare
i Integer;
Begin
dbms_job.submit(i,'pkg_leave.pro_supplement_leave_mapping(presult => :presult);',Sysdate,'TRUNC(LAST_DAY(SYSDATE))+1+1/24');
end;
---查看定时任务
select * from user_jobs;

HISOTORY:
DATE NAME DESCRIPTION
2017-10-12 Waite PROCEDURE CREATED
***********************************************************************************************************************/

PROCEDURE Auto_Gen_Employee_Leave AS
vResult varchar(20);

begin

--当月1日生成员工假期后入职的同事，在下月生成假期时补生成上月1号后入职同事的假期
--公司所有部门使用后，开启此检测功能
--PKG_LEAVE.Gen_Employee_Leave(to_char(add_months(sysdate,-1),'YYYY'),to_char(add_months(sysdate,-1),'MM'),vResult);
PKG_LEAVE.Gen_Employee_Leave(to_char(sysdate,'YYYY'),to_char(add_months(sysdate,0),'MM'),vResult);--当前月份减一
pkg_leave.pro_supplement_leave_mapping(vResult);

end Auto_Gen_Employee_Leave;

/*********************************************************************************************************************
NAME: Auto_Gen_Try_Employee_Leave 追回试用期员工假期

Declare
i Integer;
Begin
dbms_job.submit(i,'PKG_LEAVE.Auto_Gen_Employee_Leave;',Sysdate,'TRUNC(LAST_DAY(SYSDATE))+1+1/24');
end;
---查看定时任务
select * from user_jobs;

HISOTORY:
DATE NAME DESCRIPTION
2016-03-25 River PROCEDURE CREATED
***********************************************************************************************************************/
PROCEDURE Auto_Gen_Try_Employee_Leave AS
vResult varchar(20);

begin

--当月1日生成员工假期后入职的同事，在下月生成假期时补生成上月1号后入职同事的假期
--公司所有部门使用后，开启此检测功能
--PKG_LEAVE.Gen_Employee_Leave(to_char(add_months(sysdate,-6),'YYYY'),to_char(add_months(sysdate,-6),'MM'),vResult);
--PKG_LEAVE.Gen_Employee_Leave(to_char(add_months(sysdate,-5),'YYYY'),to_char(add_months(sysdate,-5),'MM'),vResult);
--PKG_LEAVE.Gen_Employee_Leave(to_char(add_months(sysdate,-4),'YYYY'),to_char(add_months(sysdate,-4),'MM'),vResult);
--PKG_LEAVE.Gen_Employee_Leave(to_char(add_months(sysdate,-3),'YYYY'),to_char(add_months(sysdate,-3),'MM'),vResult);
--PKG_LEAVE.Gen_Employee_Leave(to_char(add_months(sysdate,-2),'YYYY'),to_char(add_months(sysdate,-2),'MM'),vResult);
--PKG_LEAVE.Gen_Employee_Leave(to_char(add_months(sysdate,-1),'YYYY'),to_char(add_months(sysdate,-1),'MM'),vResult);
PKG_LEAVE.Gen_Employee_Leave(to_char(sysdate,'YYYY'),to_char(sysdate,'MM'),vResult);

end Auto_Gen_Try_Employee_Leave;

/*********************************************************************************************************************

NAME: Auto_Gen_Employee_Leave

DESCRIPTION: Auto Gen Employee Leave /每月1日执行一次
DATE NAME DESCRIPTION
2015-12-28 River PROCEDURE CREATED

------------
--员工编号
--员工转正日期
--员工Hire_Date
--员工所在地：中、港、台
--员工职级
--员工年资
--员工每月应有年假
--员工每月应有司龄假
------------
***********************************************************************************************************************/
PROCEDURE Gen_Employee_Leave(pLEAVE_YEAR number ,pLEAVE_MONTH number,presult out varchar2) IS
CURSOR TEMP_CUR IS
select
psn.PK_PSNDOC,--员工 ID
psn.code psn_Code,--员工编号
psn.name psn_name,--员工名称
job.pk_org PK_HRORG, --使用最新的工作记录的组织
org.name org_name,--组织
org.code org_code,
to_date(chg.begindate,'YYYY-MM-DD') Working_age,--工龄Hire_Date---计算享有假期福利
to_date(job.begindate,'YYYY-MM-DD') Hire_Date, --当前岗位入职日期
to_date(job.jobglbdef29,'YYYY-MM-DD') Positive_Date, ---计算享有假期福利
--早期数据尚好转正日期，系统默认为入职日期+3个月
--to_date(nvl(job.jobglbdef29,to_char(add_months(to_date(chg.begindate,'YYYY-MM-DD'),3),'YYYY-MM-DD')),'YYYY-MM-DD') Positive_Date,
bd_defdoc1.name Grade ,---当前岗位职级计算享有假期福利
(case when psn.code='0013' then 20 when psn.code='0151' then 17 else he.annual_day end) annual_day , --年假 ---香港特殊人员休假不按规则表 -- 鄧麗貞
he.working_age_day, --司龄假
he.extended_day,--可延假（适用HK）
he.region,he.grade as rank,
-- job1.recordnum ,--记录序号
-- job1.endflag ,--是否结束
-- job1.lastflag ,--是否最新工作记录
-- bd_defdoc2.name ,--该记录岗位职级
to_date(job.begindate ,'YYYY-MM-DD') Adjustment_Date --变动日期
from bd_psndoc psn
inner join hi_psnorg chg on psn.pk_psndoc=chg.pk_psndoc and chg.lastflag='Y'
inner join hi_psnjob job on psn.pk_psndoc=job.pk_psndoc and job.lastflag='Y' and job.ismainjob='Y'--主职
inner join bd_defdoc bd_defdoc1 on job.jobglbdef28 = bd_defdoc1.pk_defdoc
inner join org_orgs org on job.pk_org = org.pk_org
inner join HR_EMPLOYEE_LEAVE_BENEFITS he on he.status=1
and he.grade=decode(upper(substr(bd_defdoc1.name,1,1)),'A',1,'B',2,'C',3,'D',4,'E',5,'F',6,'G',7)
and he.region =decode(substr(org.code,1,2),'HK',decode(job.pk_dept,'10016H100000000006XZ',4,2),'TA',3,1)
and decode(floor((to_date((to_char(pLEAVE_YEAR) || substr('0' || to_char(pLEAVE_MONTH),-2) || '01'),'YYYY-MM-DD')-to_date(chg.begindate,'YYYY-MM-DD'))/365+1),0,1,floor((to_date((to_char(pLEAVE_YEAR) || substr('0' || to_char(pLEAVE_MONTH),-2) || '01'),'YYYY-MM-DD')-to_date(chg.begindate,'YYYY-MM-DD'))/365+1)) between he.work_year_from and he.work_year_to
--UW floor((to_date((to_char(pLEAVE_YEAR) || substr('0' || to_char(pLEAVE_MONTH),-2) || '01'),'YYYY-MM-DD')-to_date(chg.begindate,'YYYY-MM-DD'))/365+1) between he.work_year_from and he.work_year_to
where to_char(to_date(chg.begindate,'YYYY-MM-DD'),'YYYYMMDD')<=to_char(add_months(to_date((to_char(pLEAVE_YEAR) || substr('0' || to_char(pLEAVE_MONTH),-2) || '01'),'YYYY-MM-DD'),1)-1, 'YYYYMMDD') and
--job.PK_DEPT IN ('10016H1000000000045F','10016H1000000000045T','10016H100000000002ZA','10016H100000000002ZO','10016H100000000005G2','10016H100000000007Z6','10016H100000000007ZK','10016H10000000000684','10016H100000000006TI','10016H100000000005GG') and -- OR code like '%CCIB%'
job.trnsevent<>4 and --非离职人员
--psn.glbdef30='10018G1000000000I4OV' and --DYX 10018G1000000000I4OV CCIB 10018G1000000000I4OW
to_char(add_months(to_date((to_char(pLEAVE_YEAR) || substr('0' || to_char(pLEAVE_MONTH),-2) || '01'),'YYYY-MM-DD'),1)-1, 'YYYYMMDD')
between replace(job.begindate,'-','') and
decode(job.enddate,null,to_char(add_months(to_date((to_char(pLEAVE_YEAR) || substr('0' || to_char(pLEAVE_MONTH),-2) || '01'),'YYYY-MM-DD'),1)-1, 'YYYYMMDD'),replace(job.enddate,'-','')) and
job.begindate is not null and --当前岗位Hire_Date
job.jobglbdef29 is not null and --入职转正日期不为空
--to_date(job.jobglbdef29,'YYYY-MM-DD')>to_date('2017-06-01','YYYY-MM-DD') and ---计算享有假期福利
--添加转正日期大于于6月1日的
bd_defdoc1.name is not null and --内部级别不为空
--台湾人员2018年1月前不需每月分配，兼职人员不需分配假期
substr(org.code,1,2) not in('TA') --and psn.pk_psndoc=(select pk_psndoc from bd_psndoc where code='0151')--betty
order by psn.PK_PSNDOC;
/*
1、员工享有假期基础数据表 HR_Employee_leave_benefits
drop table HR_EMPLOYEE_LEAVE_BENEFITS;
-- Create table
create table HR_EMPLOYEE_LEAVE_BENEFITS
(
ID NUMBER not null,
REGION NUMBER not null, -- 1、PRC 2、HK 3、TW
GRADE NUMBER not null, ---1、A 2、B 3、C 4、D 5、E 6、F 7、G
WORK_YEAR_FROM NUMBER not null, --工作年限最小 1，6，11，16，21，22，23，24，25，26，27
WORK_YEAR_TO NUMBER not null, --工作年限最大 5，10，15，20，21，22，23，24，25，26，27
annual_day NUMBER not null, --年假
Working_age_day NUMBER not null, --司龄假
extended_day NUMBER not null, --可延假
STATUS NUMBER default 1 not null, --null 无效 --1、有效
CREATE_DATE DATE default sysdate not null
);

-- Create sequence
create sequence HR_EMPLOYEE_LEAVE_BENEFITS_SEQ
minvalue 1
maxvalue 999999999999
start with 1
increment by 1
cache 30;

-- Create/Recreate primary, unique and foreign key constraints
alter table HR_EMPLOYEE_LEAVE_BENEFITS add constraint HR_EMPLOYEE_LEAVE_BENEFITS_PK primary key (ID);
alter table HR_EMPLOYEE_LEAVE_BENEFITS add constraint HR_EMPLOYEE_LEAVE_BENEFITS_UK unique (GRADE, REGION, WORK_YEAR_FROM, WORK_YEAR_TO, STATUS);

2、员工享有假期数据表 HR_EMPLOYEE_ENJOY_LEAVE
-- Create table
drop table HR_EMPLOYEE_ENJOY_LEAVE;
create table HR_EMPLOYEE_ENJOY_LEAVE
(
ID NUMBER not null,
PK_PSNDOC CHAR(20) not null, --员工 ID
PK_TIMEITEM CHAR(20) not null,--1、年假 2、司龄假 3、上年年假 4、上年司龄假 5、调整年假 6、调整司龄假 7、年假延期 8、司龄假延期 (在tbm_timeitem建立假期类型，再改ID)
LEAVE_YYYY NUMBER(4) not null, --YYYY
LEAVE_MM NUMBER(2) not null, --MM
LEAVE_QTY NUMBER(5,3) not null, --年假（天）、司龄假（天）、补休（小时）
LEAVE_UNIT NUMBER(1) default 1, --1、天 2、小时
START_DATE DATE not null,
END_DATE DATE not null,
FIRST_AUDIOR varchar(20), --主管审批sm_user
FIRST_AUDIT_DATE DATE,
FIRST_AUDIT_REMARK varchar(200),
SECOND_AUDIOR varchar(20), --HR审批sm_user
SECOND_AUDIT_DATE DATE,
SECOND_AUDIT_REMARK varchar(200),
THIRD_AUDIOR varchar(20), --sm_user
THIRD_AUDIT_DATE DATE,
THIRD_AUDIT_REMARK varchar(200),
FOURTH_AUDIOR varchar(20), --sm_user
FOURTH_AUDIT_DATE DATE,
FOURTH_AUDIT_REMARK varchar(200),
CREATE_DATE DATE default sysdate not null,
CREATOR varchar(20), --sm_user)
TS CHAR(19) default to_char(sysdate,'yyyy-mm-dd hh24:mi:ss')
);

-- Create sequence
drop sequence HR_EMPLOYEE_ENJOY_LEAVE_SEQ;
create sequence HR_EMPLOYEE_ENJOY_LEAVE_SEQ
minvalue 1
maxvalue 999999999999
start with 1
increment by 1
cache 30;

-- Create/Recreate primary, unique and foreign key constraints
alter table HR_EMPLOYEE_ENJOY_LEAVE add constraint HR_EMPLOYEE_ENJOY_LEAVE_PK primary key (ID);
alter table HR_EMPLOYEE_ENJOY_LEAVE add constraint HR_EMPLOYEE_ENJOY_LEAVE_UK unique (PK_PSNDOC, PK_TIMEITEM, LEAVE_YYYY,LEAVE_MM);

*/

vResult varchar(20);
vRegion varchar(3);
vWorking_Year_Percent number; --本年可工作时间比例
vAverage_monthly_holidays number; --每月平均可有假期

vAnnual_day_addyear number; --年资加一年年假
vAnnual_day number; --年假
vWorking_age_day number; --司龄假

vPre_Extended_day number; --上年可延假
vPre_Working_Year_Percent number; --上年可工作时间比例

vTotal_Annual_day number; --已享有年假
vTotal_Working_age_day number; --已享有司龄假

vRecord_QTY number;
vCur_date date; --当前计假日期
vCur_End_date date; --当前年最后一天
vHire_Date_FirstMon_T_Day number; --试用期当月总天数

vCur_Date_YM number; --当前计假时间 YYYYMM
vCur_Month_Total_Day number; --当前计假月总天数

vEmp_Date_YM number; --员工入职时间 YYYYMM
vEmp_Date_Year number; --员工入职时间 YYYY
vEmp_Date_Month number; --员工入职时间 MM
vEmp_Date_Day number; --员工入职时间 DD

vPositive_Year number; --员工转正时间 YYYY
vPK_TIMEITEM CHAR(20) ; --当前假期类型 1、年假 2、司龄假 3、上年年假 4、上年司龄假 5、调整年假 6、调整司龄假 7、年假延期 8、司龄假延期 9、补休 10、调整补休 11、补休延期

vLEAVE_QTY number; --享有假期天数
vSTART_DATE date; --开始日期
vEND_DATE date; --结束日期
vTW_END_DATE date; --TW 结束日期
begin
presult:='Failed';
if pLEAVE_YEAR>2016 and pLEAVE_MONTH>0 and pLEAVE_MONTH <13 then
vCur_date:=to_date(to_char(pLEAVE_YEAR) || '-' || substr('00' || to_char(pLEAVE_MONTH),-2) || '-01','YYYY-MM-DD');--//当前计假日期

vCur_Date_YM:=to_number(to_char(vCur_date,'YYYYMM'));--//当前计假时间 YYYYMM
vCur_Month_Total_Day:=to_number(to_char(last_day(vCur_date),'DD'));--//当前计假月总天数

vCur_End_date:=to_date(to_char(vCur_date,'YYYY') || '-12-31','YYYY-MM-DD');--//当前年最后一天
vTW_END_DATE:=to_date(to_char(pLEAVE_YEAR+1) || '-03-31','YYYY-MM-DD');--//TW 结束日期

/*
1、区分1、PRC 2、HK 3、TW
2、是否当年1月，计算剩余假和延期数
3、根据员工职级、年资、计算假期、是否转正

11、PRC、HK、TW假期计算统一为：
a）每月1号，系统自动计算分给员工当月假期；若员工A在3月10日由E职级升到D级，即其在3月1日用E级假期福利计算3月份的假，4月1日用D级假期福利计算4月份的假；
b）员工试用期间的假期在试用期过后才能申请使用；若PRC或HK员工A在9月1日入职（享D级5天年假，7天司龄假），第二年1月1日转正，在1月其可用年假为4天；
c）员工入职第一个月的应有假期，按当月工作天数比例计算；若员工A在1月10日由入职（享D级5天年假，7天司龄假），即1月应有假期为（31-10+1）/31 * （5+7）/12=0.704天年假
d) 若员工A有3天假期［年假/司龄假/补休］在2015年12月31日到期，系统允许在12月31日～1月5日(1、2、3日为公众假期)申请休这3天年假;
e）假期工龄计算：［1到5年］入职～未满5年［6到10年］满5年~未满10年［11到15年］满10年~未满15年 ,如此类推..；
员工A 入职日期：2015/9/10 转正日期：2015/12/9 1～5年工龄 6～10年工龄 …
PRC 2015/9/10～2020/9/9 2020/9/10～2025/9/9 …
HK 2015/9/10～2020/9/9 2020/9/10～2025/9/9 …
TW 2015/9/10～2020/9/9 2020/9/10～2025/9/9 …
f）年假/司龄假/补休假期保留3位小数；
g）增加假期延期申请功能、假期调整功能，具体权限分配由HR决定；

12、1月1日～6月30日PRC员工年资变动导致假期变化的，系统按员工最新假期福利先分摊年假，再分摊司龄假。7月1日～12月31日发生变化的全以司龄假分摊；
13、TW可预支不超过当年剩余的总年假，HK、PRC不能预支假期；
14、PRC、HK、TW员工在年底申请中的年假和司龄假，若没审批完，系统在1月1日将自动审批通过，否则影响延假计算。（此问题待HR提供解决办法）

--当前假期类型 1、年假 2、司龄假 3、上年年假 4、上年司龄假 5、调整年假 6、调整司龄假 7、年假延期 8、司龄假延期 9、补休 10、调整补休 11、补休延期
1002Z710000000021ZLJ 年假
10018G1000000000BM37 上年年假
10018G1000000000BM2T 调整年假
10018G1000000000BM2P 年假延期

10018G1000000000BM2N 司龄假
10018G1000000000BM39 上年司龄假
10018G1000000000BM2R 调整司龄假
10018G1000000000BM2V 司龄假延期

10018G1000000000BM2L 补休
10018G1000000000BM33 调整补休
10018G1000000000BM35 补休延期

*/
For TEMP_CUR_RECORD in TEMP_CUR loop
---员工所在地区
if substr(TEMP_CUR_RECORD.org_code,1,2)='HK' then
vRegion:= 'HK';
elsif substr(TEMP_CUR_RECORD.org_code,1,3)='TAI' then
vRegion:= 'TW';
else
vRegion:= 'PRC';
end if ;

--员工入职时间年月日
vEmp_Date_Year:=to_number(to_char(TEMP_CUR_RECORD.Working_age,'YYYY')); --员工入职时间 YYYY-mm-dd --//由Hire_Date改为Working_age
vEmp_Date_Month:=to_number(to_char(TEMP_CUR_RECORD.Working_age,'MM')); --员工入职时间 MM --//由Hire_Date改为Working_age
vEmp_Date_Day:=to_number(to_char(TEMP_CUR_RECORD.Working_age,'DD'));--员工入职时间 DD --//由Hire_Date改为Working_age
vEmp_Date_YM:=to_number(to_char(TEMP_CUR_RECORD.Working_age,'YYYYMM')); --员工入职时间 YYYYMM --//由Hire_Date改为Working_age

--计算假期的开始日期 & 结束日期
if TEMP_CUR_RECORD.Positive_Date>vCur_date then --转正日期>当前日期，员工还没转正，假期生效时间为转正日期
vSTART_DATE:=TEMP_CUR_RECORD.Positive_Date;--TEMP_CUR_RECORD.Positive_Date;--UW
--TW有效期15个月，PRC、HK当年有效
if vRegion= 'TW' then
vEND_DATE:=vTW_END_DATE;
else --
vPositive_Year:=to_number(to_char(TEMP_CUR_RECORD.Positive_Date,'YYYY')); ----员工转正时间 YYYY
if vPositive_Year>pLEAVE_YEAR then --跨年转正
vEND_DATE:=to_date(to_char(vPositive_Year) || '-12-31','YYYY-MM-DD');
else
vEND_DATE:=vCur_End_date;
end if;
end if;
else ---已经转正
vSTART_DATE:=vCur_date;
vEND_DATE:=vCur_End_date;
end if;
-------------跨年假期结转-------------------------------------
if pLEAVE_MONTH=1 then ---计算可延假 ,可延假>0 and 月份=1
--计算当年假期
if vEmp_Date_Year<(pLEAVE_YEAR-1) then --当前年大天其入职年
vPre_Working_Year_Percent:=1; --本年可工作时间比例
else--本年入职
vHire_Date_FirstMon_T_Day:=to_number(to_char(last_day(TEMP_CUR_RECORD.Working_age),'DD')); --试用期当月总天数 --//由Hire_Date改为Working_age
vPre_Working_Year_Percent:=(12-vEmp_Date_Month+(vHire_Date_FirstMon_T_Day-vEmp_Date_Day+1)/vHire_Date_FirstMon_T_Day)/12; --上年可工作时间比例
end if;

--he.annual_day, --年假
--he.working_age_day, --司龄假
--vPre_Extended_day:=TEMP_CUR_RECORD.extended_day*vPre_Working_Year_Percent; --上年可延假
--vPre_Extended_day:=TEMP_CUR_RECORD.working_age_day*vPre_Working_Year_Percent; --上年可延假
--if vPre_Extended_day>0 then ---计算可延假 ,可延假>0 and 月份=1
if vRegion= 'PRC' then
vPre_Extended_day:=TEMP_CUR_RECORD.working_age_day*vPre_Working_Year_Percent; --上年可延司龄假
vPK_TIMEITEM :='10018G1000000000BM39'; --当前假期类型 1、年假 2、司龄假 3、上年年假 4、上年司龄假 5、调整年假 6、调整司龄假 7、年假延期 8、司龄假延期
---检查数据是否已存在
select count(1) into vRecord_QTY from HR_EMPLOYEE_ENJOY_LEAVE h inner join HR_ELEAVE_MAPPING m on m.hr_employee_enjoy_leave_id=h.id where PK_PSNDOC=TEMP_CUR_RECORD.PK_PSNDOC and PK_ORG=TEMP_CUR_RECORD.PK_HRORG and LEAVE_YYYY=pLEAVE_YEAR and PK_TIMEITEM=vPK_TIMEITEM;
if vRecord_QTY=0 then--假期记录不存在可以新增
delete from HR_EMPLOYEE_ENJOY_LEAVE where PK_PSNDOC=TEMP_CUR_RECORD.PK_PSNDOC and PK_ORG=TEMP_CUR_RECORD.PK_HRORG and LEAVE_YYYY=pLEAVE_YEAR and PK_TIMEITEM=vPK_TIMEITEM;
--计算剩余假期
select nvl(sum(lastdayorhour+realdayorhour -yidayorhour-freezedayorhour), 0) into vLEAVE_QTY from tbm_leavebalance where pk_psndoc=TEMP_CUR_RECORD.PK_PSNDOC and PK_ORG=TEMP_CUR_RECORD.PK_HRORG and curyear=to_char(pLEAVE_YEAR-1) and pk_timeitem='10018G1000000000BM2N';
if vLEAVE_QTY>vPre_Extended_day then -- 剩余假期 vLEAVE_QTY 若大于可延假，即只可带可延假数
vLEAVE_QTY:=vPre_Extended_day;
end if;
--新增加可延司龄假
insert into HR_EMPLOYEE_ENJOY_LEAVE( ID, PK_PSNDOC,PK_ORG,LEAVE_YYYY,LEAVE_MM,PK_TIMEITEM,LEAVE_QTY,START_DATE,END_DATE) values(HR_EMPLOYEE_ENJOY_LEAVE_SEQ.NEXTVAL,TEMP_CUR_RECORD.PK_PSNDOC,TEMP_CUR_RECORD.PK_HRORG,pLEAVE_YEAR,pLEAVE_MONTH,vPK_TIMEITEM, Ceil(vLEAVE_QTY * 1000)/1000,vCur_date,vCur_End_date);
----当前假期类型 2、司龄假 4、可延司龄假（注意假期有效期）
update tbm_leavebalance set lastdayorhour=vLEAVE_QTY
where pk_psndoc=TEMP_CUR_RECORD.PK_PSNDOC and PK_ORG=TEMP_CUR_RECORD.PK_HRORG and curyear=to_char(pLEAVE_YEAR) and pk_timeitem='10018G1000000000BM2N';
end if;

vPre_Extended_day:=TEMP_CUR_RECORD.annual_day*vPre_Working_Year_Percent; --上年可延年假
vPK_TIMEITEM :='10018G1000000000BM37'; --当前假期类型 1、年假 2、司龄假 3、上年年假 4、上年司龄假 5、调整年假 6、调整司龄假 7、年假延期 8、司龄假延期
---检查数据是否已存在
select count(1) into vRecord_QTY from HR_EMPLOYEE_ENJOY_LEAVE h inner join HR_ELEAVE_MAPPING m on m.hr_employee_enjoy_leave_id=h.id where PK_PSNDOC=TEMP_CUR_RECORD.PK_PSNDOC and PK_ORG=TEMP_CUR_RECORD.PK_HRORG and LEAVE_YYYY=pLEAVE_YEAR and PK_TIMEITEM=vPK_TIMEITEM;
if vRecord_QTY=0 then--假期记录不存在可以新增
delete from HR_EMPLOYEE_ENJOY_LEAVE where PK_PSNDOC=TEMP_CUR_RECORD.PK_PSNDOC and PK_ORG=TEMP_CUR_RECORD.PK_HRORG and LEAVE_YYYY=pLEAVE_YEAR and PK_TIMEITEM=vPK_TIMEITEM;
--计算剩余假期
select nvl(sum(lastdayorhour+realdayorhour -yidayorhour-freezedayorhour), 0) into vLEAVE_QTY from tbm_leavebalance where pk_psndoc=TEMP_CUR_RECORD.PK_PSNDOC and PK_ORG=TEMP_CUR_RECORD.PK_HRORG and curyear=to_char(pLEAVE_YEAR-1) and pk_timeitem='1002Z710000000021ZLJ';
if vLEAVE_QTY>0 then -- 剩余假期 vLEAVE_QTY 若大于可延假，即只可带可延假数
if vLEAVE_QTY>vPre_Extended_day then
vLEAVE_QTY:=vPre_Extended_day;
end if;
--新增加可延年假
insert into HR_EMPLOYEE_ENJOY_LEAVE( ID, PK_PSNDOC,PK_ORG,LEAVE_YYYY,LEAVE_MM,PK_TIMEITEM,LEAVE_QTY,START_DATE,END_DATE) values(HR_EMPLOYEE_ENJOY_LEAVE_SEQ.NEXTVAL,TEMP_CUR_RECORD.PK_PSNDOC,TEMP_CUR_RECORD.PK_HRORG,pLEAVE_YEAR,pLEAVE_MONTH,vPK_TIMEITEM,Ceil(vLEAVE_QTY * 1000)/1000,vCur_date,vCur_End_date);

update tbm_leavebalance set lastdayorhour=vLEAVE_QTY
where pk_psndoc=TEMP_CUR_RECORD.PK_PSNDOC and PK_ORG=TEMP_CUR_RECORD.PK_HRORG and curyear=to_char(pLEAVE_YEAR) and pk_timeitem='1002Z710000000021ZLJ';
end if;
end if;
elsif vRegion= 'HK' then --vRegion= 'HK'
vPre_Extended_day:=TEMP_CUR_RECORD.extended_day*vPre_Working_Year_Percent; --上年可延假
vPK_TIMEITEM :='10018G1000000000BM37'; --当前假期类型 1、年假 2、司龄假 3、上年年假 4、上年司龄假 5、调整年假 6、调整司龄假 7、年假延期 8、司龄假延期
---检查数据是否已存在
select count(1) into vRecord_QTY from HR_EMPLOYEE_ENJOY_LEAVE h inner join HR_ELEAVE_MAPPING m on m.hr_employee_enjoy_leave_id=h.id where PK_PSNDOC=TEMP_CUR_RECORD.PK_PSNDOC and PK_ORG=TEMP_CUR_RECORD.PK_HRORG and LEAVE_YYYY=pLEAVE_YEAR and PK_TIMEITEM=vPK_TIMEITEM;
if vRecord_QTY=0 then--假期记录不存在可以新增
delete from HR_EMPLOYEE_ENJOY_LEAVE where PK_PSNDOC=TEMP_CUR_RECORD.PK_PSNDOC and PK_ORG=TEMP_CUR_RECORD.PK_HRORG and LEAVE_YYYY=pLEAVE_YEAR and PK_TIMEITEM=vPK_TIMEITEM;
--计算剩余假期
select nvl(sum(lastdayorhour+realdayorhour -yidayorhour-freezedayorhour), 0) into vLEAVE_QTY from tbm_leavebalance where pk_psndoc=TEMP_CUR_RECORD.PK_PSNDOC and PK_ORG=TEMP_CUR_RECORD.PK_HRORG and curyear=to_char(pLEAVE_YEAR-1) and pk_timeitem='1002Z710000000021ZLJ';
if vLEAVE_QTY>0 then -- 剩余假期 vLEAVE_QTY 若大于可延假，即只可带可延假数
if vLEAVE_QTY>vPre_Extended_day then
vLEAVE_QTY:=vPre_Extended_day;
end if;
--新增加可延年假
insert into HR_EMPLOYEE_ENJOY_LEAVE( ID, PK_PSNDOC,PK_ORG,LEAVE_YYYY,LEAVE_MM,PK_TIMEITEM,LEAVE_QTY,START_DATE,END_DATE) values(HR_EMPLOYEE_ENJOY_LEAVE_SEQ.NEXTVAL,TEMP_CUR_RECORD.PK_PSNDOC,TEMP_CUR_RECORD.PK_HRORG,pLEAVE_YEAR,pLEAVE_MONTH,vPK_TIMEITEM,Ceil(vLEAVE_QTY * 1000)/1000,vCur_date,vCur_End_date);

update tbm_leavebalance set lastdayorhour=round(vLEAVE_QTY*2,0)/2
where pk_psndoc=TEMP_CUR_RECORD.PK_PSNDOC and PK_ORG=TEMP_CUR_RECORD.PK_HRORG and curyear=to_char(pLEAVE_YEAR) and pk_timeitem='1002Z710000000021ZLJ';
end if;
end if;
elsif vRegion= 'TW' then --台湾假期，有效期15个月
--vPre_Extended_day:=TEMP_CUR_RECORD.extended_day*vPre_Working_Year_Percent; --上年可延假(新增)
vPK_TIMEITEM :='10018G1000000000BM37'; --当前假期类型 1、年假 2、司龄假 3、上年年假 4、上年司龄假 5、调整年假 6、调整司龄假 7、年假延期 8、司龄假延期
---检查数据是否已存在
select count(1) into vRecord_QTY from HR_EMPLOYEE_ENJOY_LEAVE where PK_PSNDOC=TEMP_CUR_RECORD.PK_PSNDOC and LEAVE_YYYY=pLEAVE_YEAR and PK_TIMEITEM=vPK_TIMEITEM;
if vRecord_QTY=0 then--假期记录不存在可以新增
--计算剩余假期
--select nvl(sum(lastdayorhour+realdayorhour -yidayorhour-freezedayorhour), 0) into vLEAVE_QTY from tbm_leavebalance where pk_psndoc=TEMP_CUR_RECORD.PK_PSNDOC and PK_ORG=TEMP_CUR_RECORD.PK_HRORG and curyear=to_char(pLEAVE_YEAR-1) and pk_timeitem='1002Z710000000021ZLJ';
select nvl(sum(lastdayorhour+realdayorhour -yidayorhour-freezedayorhour), 0) into vLEAVE_QTY from tbm_leavebalance where pk_psndoc=TEMP_CUR_RECORD.PK_PSNDOC and PK_ORG=TEMP_CUR_RECORD.PK_HRORG and curyear=to_char(pLEAVE_YEAR-1) and pk_timeitem='1002Z710000000021ZLJ';
--if vLEAVE_QTY>vPre_Extended_day then -- 剩余假期 vLEAVE_QTY 若大于可延假，即只可带可延假数
-- vLEAVE_QTY:=vPre_Extended_day;
--end if;
--新增加可延年假 --TW 上年年假可延到下一年3月31日
insert into HR_EMPLOYEE_ENJOY_LEAVE( ID, PK_PSNDOC,PK_ORG,LEAVE_YYYY,LEAVE_MM,PK_TIMEITEM,LEAVE_QTY,START_DATE,END_DATE) values(HR_EMPLOYEE_ENJOY_LEAVE_SEQ.NEXTVAL,TEMP_CUR_RECORD.PK_PSNDOC,TEMP_CUR_RECORD.PK_HRORG,pLEAVE_YEAR,pLEAVE_MONTH,vPK_TIMEITEM,Ceil(vLEAVE_QTY * 1000)/1000,vCur_date,to_date(to_char(pLEAVE_YEAR) || '-03-31','YYYY-MM-DD'));
update tbm_leavebalance set lastdayorhour=vLEAVE_QTY
where pk_psndoc=TEMP_CUR_RECORD.PK_PSNDOC and PK_ORG=TEMP_CUR_RECORD.PK_HRORG and curyear=to_char(pLEAVE_YEAR) and pk_timeitem='1002Z710000000021ZLJ';
end if;

end if;
--end if;
end if;
--------------------------------------------------

--计算上年可延假
if vEmp_Date_Year<pLEAVE_YEAR-1 then --当上年大天其入职年
vPre_Working_Year_Percent:=1; --上年可工作时间比例
--else--上年入职
-- vHire_Date_FirstMon_T_Day:=to_number(to_char(last_day(TEMP_CUR_RECORD.Hire_Date),'DD')); --试用期当月总天数
-- vPre_Working_Year_Percent:=(vHire_Date_FirstMon_T_Day-vEmp_Date_Day+1)/vHire_Date_FirstMon_T_Day+(12-vEmp_Date_Month)/12; --本年可工作时间比例
end if;

--计算当年假期
--判断是否大陆，大陆有司龄假
if vEmp_Date_Year<pLEAVE_YEAR then --当前年大天其入职年
if vRegion= 'PRC' then
vWorking_Year_Percent:=1; --本年可工作时间比例
elsif vRegion= 'TW' then
vWorking_Year_Percent:=(12-vEmp_Date_Month+1)/12;--本年未分配应享有年假比例,当年年资增加前比例
--比例需要分开，入职月份作为区分 vEmp_Date_Month
else
vWorking_Year_Percent:=1;--本年未分配应享有年假比例
--比例需要分开，入职月份作为区分 vEmp_Date_Month
end if;
else--本年入职
vHire_Date_FirstMon_T_Day:=to_number(to_char(last_day(TEMP_CUR_RECORD.Working_age),'DD')); --试用期当月总天数--//由Hire_Date改为Working_age
vWorking_Year_Percent:=(12-vEmp_Date_Month+(vHire_Date_FirstMon_T_Day-vEmp_Date_Day+1)/vHire_Date_FirstMon_T_Day)/12; --本年可工作时间比例
end if;
if vRegion= 'PRC' then
vAnnual_day:=TEMP_CUR_RECORD.annual_day*vWorking_Year_Percent; --本年应有年假
vWorking_age_day:=TEMP_CUR_RECORD.working_age_day*vWorking_Year_Percent; --本年应有司龄假
---====add by waite
elsif vRegion= 'TW' then
--计算本年已分配的年假
select nvl(sum(LEAVE_QTY),0) into vTotal_Annual_day from HR_EMPLOYEE_ENJOY_LEAVE where PK_PSNDOC=TEMP_CUR_RECORD.PK_PSNDOC and PK_ORG=TEMP_CUR_RECORD.PK_HRORG and PK_TIMEITEM='1002Z710000000021ZLJ' and LEAVE_YYYY=pLEAVE_YEAR;
--入职月份小于当前月份
if vEmp_Date_Month<pLEAVE_MONTH then
vAnnual_day:=TEMP_CUR_RECORD.annual_day*((12-pLEAVE_MONTH+1)/12)+vTotal_Annual_day; --本年应有年假,已分配加上未分配
else
select annual_day into vAnnual_day_addyear from HR_EMPLOYEE_LEAVE_BENEFITS where region=TEMP_CUR_RECORD.region and grade=TEMP_CUR_RECORD.rank and floor((to_date((to_char(pLEAVE_YEAR) || substr('0' || to_char(pLEAVE_MONTH),-2) || '01'),'YYYY-MM-DD')-TEMP_CUR_RECORD.Working_age)/365+2) between work_year_from and work_year_to;
vAnnual_day:=TEMP_CUR_RECORD.annual_day*((vEmp_Date_Month-pLEAVE_MONTH+1)/12)+vTotal_Annual_day+vAnnual_day_addyear*((12-vEmp_Date_Month)/12); --本年应有年假加年资加一未分配
end if;
else--HK
--计算本年已分配的年假
--select nvl(sum(LEAVE_QTY),0) into vTotal_Annual_day from HR_EMPLOYEE_ENJOY_LEAVE where PK_PSNDOC=TEMP_CUR_RECORD.PK_PSNDOC and PK_ORG=TEMP_CUR_RECORD.PK_HRORG and PK_TIMEITEM='1002Z710000000021ZLJ' and LEAVE_YYYY=pLEAVE_YEAR;
vAnnual_day:=TEMP_CUR_RECORD.annual_day*vWorking_Year_Percent;--本年应有年假
end if;

--计算本月工作天数，可享的假期
if vCur_Date_YM>vEmp_Date_YM then --计假年月大于入职年月，即员工全月工作，享当月假
vAverage_monthly_holidays:=(TEMP_CUR_RECORD.working_age_day+TEMP_CUR_RECORD.annual_day)/12; --每月平均可有假期
elsif vCur_Date_YM=vEmp_Date_YM then --计假年月等于入职年月，即员工可能不是全月工作，享当月比例的假
vAverage_monthly_holidays:=((vCur_Month_Total_Day-vEmp_Date_Day+1)/vCur_Month_Total_Day) * (TEMP_CUR_RECORD.working_age_day+TEMP_CUR_RECORD.annual_day)/12;
end if;

--计算本年已分配的年假
select nvl(sum(LEAVE_QTY),0) into vTotal_Annual_day from HR_EMPLOYEE_ENJOY_LEAVE where PK_PSNDOC=TEMP_CUR_RECORD.PK_PSNDOC and PK_ORG=TEMP_CUR_RECORD.PK_HRORG and PK_TIMEITEM='1002Z710000000021ZLJ' and LEAVE_YYYY=pLEAVE_YEAR;
--计算本年已分配的司龄假
select nvl(sum(LEAVE_QTY),0) into vTotal_Working_age_day from HR_EMPLOYEE_ENJOY_LEAVE where PK_PSNDOC=TEMP_CUR_RECORD.PK_PSNDOC and PK_ORG=TEMP_CUR_RECORD.PK_HRORG and PK_TIMEITEM='10018G1000000000BM2N' and LEAVE_YYYY=pLEAVE_YEAR;

---检查数据是否已存在
select count(1) into vRecord_QTY from HR_EMPLOYEE_ENJOY_LEAVE where PK_PSNDOC=TEMP_CUR_RECORD.PK_PSNDOC and PK_ORG=TEMP_CUR_RECORD.PK_HRORG and PK_TIMEITEM in ('1002Z710000000021ZLJ','10018G1000000000BM2N') and LEAVE_YYYY=pLEAVE_YEAR and LEAVE_MM=pLEAVE_MONTH;
if vRecord_QTY=0 then--假期记录不存在可以新增
/*
vAnnual_day --本年应有年假
vWorking_age_day --本年应有司龄假
vAverage_monthly_holidays --每月平均可有假期
vTotal_Annual_day--已分配的年假
vTotal_Working_age_day--已分配的司龄假

1月1日～6月30日PRC员工年资变动导致假期变化的，系统按员工最新假期福利先分摊年假，再分摊司龄假。7月1日～12月31日发生变化的全以司龄假分摊；
*/
if to_number(to_char(TEMP_CUR_RECORD.Working_age,'YYYY'))>=pLEAVE_YEAR then --入职日期大于等于计算假歁的年度，应享假期按比例计算
--2016-08上线 PRC地区分配司龄假
if (vRegion='PRC' and pLEAVE_YEAR>2016 and pLEAVE_MONTH>6)
or (TEMP_CUR_RECORD.Adjustment_Date>TEMP_CUR_RECORD.Positive_Date and vRegion='PRC' and to_number(to_char(TEMP_CUR_RECORD.Adjustment_Date,'YYYY'))=pLEAVE_YEAR and to_number(to_char(TEMP_CUR_RECORD.Adjustment_Date,'MM'))>6 and pLEAVE_MONTH>6)
then
vPK_TIMEITEM :='10018G1000000000BM2N'; --当前假期类型 1、年假 2、司龄假 3、上年年假 4、上年司龄假 5、调整年假 6、调整司龄假 7、年假延期 8、司龄假延期
vLEAVE_QTY:=vAverage_monthly_holidays;
--新增加司龄假
insert into HR_EMPLOYEE_ENJOY_LEAVE( ID, PK_PSNDOC,PK_ORG,LEAVE_YYYY,LEAVE_MM,PK_TIMEITEM,LEAVE_QTY,START_DATE,END_DATE) values(HR_EMPLOYEE_ENJOY_LEAVE_SEQ.NEXTVAL,TEMP_CUR_RECORD.PK_PSNDOC,TEMP_CUR_RECORD.PK_HRORG,pLEAVE_YEAR,pLEAVE_MONTH,vPK_TIMEITEM,Ceil(vLEAVE_QTY * 1000)/1000,vSTART_DATE,vEND_DATE);
--判断是否转正
-- if TEMP_CUR_RECORD.Positive_Date<vCur_date then--UW
PKG_LEAVE.Update_Leave_By_PK_PSNDOC(TEMP_CUR_RECORD.PK_PSNDOC,TEMP_CUR_RECORD.PK_HRORG,vPK_TIMEITEM,pLEAVE_YEAR,vAnnual_day+vWorking_age_day,vResult);
-- end if;
else
if vAnnual_day>=(vTotal_Annual_day+vAverage_monthly_holidays) then --本年应有年假>=每月平均可有假期+已分配的年假
vPK_TIMEITEM :='1002Z710000000021ZLJ'; --当前假期类型 1、年假 2、司龄假 3、上年年假 4、上年司龄假 5、调整年假 6、调整司龄假 7、年假延期 8、司龄假延期
vLEAVE_QTY:=vAverage_monthly_holidays;
--新增加年假
insert into HR_EMPLOYEE_ENJOY_LEAVE( ID, PK_PSNDOC,PK_ORG,LEAVE_YYYY,LEAVE_MM,PK_TIMEITEM,LEAVE_QTY,START_DATE,END_DATE) values(HR_EMPLOYEE_ENJOY_LEAVE_SEQ.NEXTVAL,TEMP_CUR_RECORD.PK_PSNDOC,TEMP_CUR_RECORD.PK_HRORG,pLEAVE_YEAR,pLEAVE_MONTH,vPK_TIMEITEM,Ceil(vLEAVE_QTY * 1000)/1000,vSTART_DATE,vEND_DATE);
--判断是否转正
-- if TEMP_CUR_RECORD.Positive_Date<vCur_date then--UW
PKG_LEAVE.Update_Leave_By_PK_PSNDOC(TEMP_CUR_RECORD.PK_PSNDOC,TEMP_CUR_RECORD.PK_HRORG,vPK_TIMEITEM,pLEAVE_YEAR,vAnnual_day,vResult);
-- end if;
else
if vAnnual_day>vTotal_Annual_day then --当月出现部分看年假，部分司龄假
--新增加年假
vPK_TIMEITEM :='1002Z710000000021ZLJ'; --当前假期类型 1、年假 2、司龄假 3、上年年假 4、上年司龄假 5、调整年假 6、调整司龄假 7、年假延期 8、司龄假延期
vLEAVE_QTY:=vAnnual_day-vTotal_Annual_day;
insert into HR_EMPLOYEE_ENJOY_LEAVE( ID, PK_PSNDOC,PK_ORG,LEAVE_YYYY,LEAVE_MM,PK_TIMEITEM,LEAVE_QTY,START_DATE,END_DATE) values(HR_EMPLOYEE_ENJOY_LEAVE_SEQ.NEXTVAL,TEMP_CUR_RECORD.PK_PSNDOC,TEMP_CUR_RECORD.PK_HRORG,pLEAVE_YEAR,pLEAVE_MONTH,vPK_TIMEITEM,Ceil(vLEAVE_QTY * 1000)/1000,vSTART_DATE,vEND_DATE);
--判断是否转正
-- if TEMP_CUR_RECORD.Positive_Date<vCur_date then--UW
PKG_LEAVE.Update_Leave_By_PK_PSNDOC(TEMP_CUR_RECORD.PK_PSNDOC,TEMP_CUR_RECORD.PK_HRORG,vPK_TIMEITEM,pLEAVE_YEAR,vAnnual_day,vResult);
-- end if;
--新增加司龄假
vPK_TIMEITEM :='10018G1000000000BM2N'; --当前假期类型 1、年假 2、司龄假 3、上年年假 4、上年司龄假 5、调整年假 6、调整司龄假 7、年假延期 8、司龄假延期
vLEAVE_QTY:=vAverage_monthly_holidays-(vAnnual_day-vTotal_Annual_day);
insert into HR_EMPLOYEE_ENJOY_LEAVE( ID, PK_PSNDOC,PK_ORG,LEAVE_YYYY,LEAVE_MM,PK_TIMEITEM,LEAVE_QTY,START_DATE,END_DATE) values(HR_EMPLOYEE_ENJOY_LEAVE_SEQ.NEXTVAL,TEMP_CUR_RECORD.PK_PSNDOC,TEMP_CUR_RECORD.PK_HRORG,pLEAVE_YEAR,pLEAVE_MONTH,vPK_TIMEITEM,Ceil(vLEAVE_QTY * 1000)/1000,vSTART_DATE,vEND_DATE);
-- if TEMP_CUR_RECORD.Positive_Date<vCur_date then--UW
PKG_LEAVE.Update_Leave_By_PK_PSNDOC(TEMP_CUR_RECORD.PK_PSNDOC,TEMP_CUR_RECORD.PK_HRORG,vPK_TIMEITEM,pLEAVE_YEAR,vWorking_age_day,vResult);
-- end if;
else --年假已分派完，紧剩司龄假
vPK_TIMEITEM :='10018G1000000000BM2N'; --当前假期类型 1、年假 2、司龄假 3、上年年假 4、上年司龄假 5、调整年假 6、调整司龄假 7、年假延期 8、司龄假延期
vLEAVE_QTY:=vAverage_monthly_holidays;
--新增加司龄假
insert into HR_EMPLOYEE_ENJOY_LEAVE( ID, PK_PSNDOC,PK_ORG,LEAVE_YYYY,LEAVE_MM,PK_TIMEITEM,LEAVE_QTY,START_DATE,END_DATE) values(HR_EMPLOYEE_ENJOY_LEAVE_SEQ.NEXTVAL,TEMP_CUR_RECORD.PK_PSNDOC,TEMP_CUR_RECORD.PK_HRORG,pLEAVE_YEAR,pLEAVE_MONTH,vPK_TIMEITEM,Ceil(vLEAVE_QTY * 1000)/1000,vSTART_DATE,vEND_DATE);
--判断是否转正
--if TEMP_CUR_RECORD.Positive_Date<vCur_date then--UW
PKG_LEAVE.Update_Leave_By_PK_PSNDOC(TEMP_CUR_RECORD.PK_PSNDOC,TEMP_CUR_RECORD.PK_HRORG,vPK_TIMEITEM,pLEAVE_YEAR,vWorking_age_day,vResult);
-- end if;
end if;
end if;
end if;
else --入职日期早于计算假歁的年度，应享假期按比例计算
-- if TEMP_CUR_RECORD.Adjustment_Date>TEMP_CUR_RECORD.Positive_Date and vRegion='PRC' and to_number(to_char(TEMP_CUR_RECORD.Adjustment_Date,'YYYY'))=pLEAVE_YEAR and to_number(to_char(TEMP_CUR_RECORD.Adjustment_Date,'MM'))>6 and pLEAVE_MONTH>6 then
if (vRegion='PRC' and pLEAVE_YEAR>2016 and pLEAVE_MONTH>6)
or TEMP_CUR_RECORD.Adjustment_Date>TEMP_CUR_RECORD.Positive_Date and vRegion='PRC' and to_number(to_char(TEMP_CUR_RECORD.Adjustment_Date,'YYYY'))=pLEAVE_YEAR and to_number(to_char(TEMP_CUR_RECORD.Adjustment_Date,'MM'))>6 and pLEAVE_MONTH>6 then
--Adjustment_Date
vPK_TIMEITEM :='10018G1000000000BM2N'; --当前假期类型 1、年假 2、司龄假 3、上年年假 4、上年司龄假 5、调整年假 6、调整司龄假 7、年假延期 8、司龄假延期
vLEAVE_QTY:=vAverage_monthly_holidays;
--新增加司龄假
insert into HR_EMPLOYEE_ENJOY_LEAVE( ID, PK_PSNDOC,PK_ORG,LEAVE_YYYY,LEAVE_MM,PK_TIMEITEM,LEAVE_QTY,START_DATE,END_DATE) values(HR_EMPLOYEE_ENJOY_LEAVE_SEQ.NEXTVAL,TEMP_CUR_RECORD.PK_PSNDOC,TEMP_CUR_RECORD.PK_HRORG,pLEAVE_YEAR,pLEAVE_MONTH,vPK_TIMEITEM,Ceil(vLEAVE_QTY * 1000)/1000,vSTART_DATE,vEND_DATE);
PKG_LEAVE.Update_Leave_By_PK_PSNDOC(TEMP_CUR_RECORD.PK_PSNDOC,TEMP_CUR_RECORD.PK_HRORG,vPK_TIMEITEM,pLEAVE_YEAR,TEMP_CUR_RECORD.working_age_day,vResult);
else
--判断是否是香港地区 add by waite 2018年1月取消
if substr(TEMP_CUR_RECORD.org_code,1,2)='HK' then
--if pPK_ORG in('00018G1000000000I1A7','00016H10000000000BIO','00016H10000000000BI8','00016H10000000000BI5','00016H10000000000BIC','00016H10000000000BIF','00016H10000000000BII','00016H10000000000BIL') then
vPK_TIMEITEM :='1002Z710000000021ZLJ'; --当前假期类型 1、年假 2、司龄假 3、上年年假 4、上年司龄假 5、调整年假 6、调整司龄假 7、年假延期 8、司龄假延期
vLEAVE_QTY:=vAverage_monthly_holidays;
--新增加年假
insert into HR_EMPLOYEE_ENJOY_LEAVE( ID, PK_PSNDOC,PK_ORG,LEAVE_YYYY,LEAVE_MM,PK_TIMEITEM,LEAVE_QTY,START_DATE,END_DATE) values(HR_EMPLOYEE_ENJOY_LEAVE_SEQ.NEXTVAL,TEMP_CUR_RECORD.PK_PSNDOC,TEMP_CUR_RECORD.PK_HRORG,pLEAVE_YEAR,pLEAVE_MONTH,vPK_TIMEITEM,Ceil(vLEAVE_QTY * 1000)/1000,vSTART_DATE,vEND_DATE);
PKG_LEAVE.Update_Leave_By_PK_PSNDOC(TEMP_CUR_RECORD.PK_PSNDOC,TEMP_CUR_RECORD.PK_HRORG,vPK_TIMEITEM,pLEAVE_YEAR,vAnnual_day,vResult);
else
------------=========
if vAnnual_day>=(vTotal_Annual_day+vAverage_monthly_holidays) then --本年应有年假>=每月平均可有假期+已分配的年假
vPK_TIMEITEM :='1002Z710000000021ZLJ'; --当前假期类型 1、年假 2、司龄假 3、上年年假 4、上年司龄假 5、调整年假 6、调整司龄假 7、年假延期 8、司龄假延期
vLEAVE_QTY:=vAverage_monthly_holidays;
--新增加年假
insert into HR_EMPLOYEE_ENJOY_LEAVE( ID, PK_PSNDOC,PK_ORG,LEAVE_YYYY,LEAVE_MM,PK_TIMEITEM,LEAVE_QTY,START_DATE,END_DATE) values(HR_EMPLOYEE_ENJOY_LEAVE_SEQ.NEXTVAL,TEMP_CUR_RECORD.PK_PSNDOC,TEMP_CUR_RECORD.PK_HRORG,pLEAVE_YEAR,pLEAVE_MONTH,vPK_TIMEITEM,Ceil(vLEAVE_QTY * 1000)/1000,vSTART_DATE,vEND_DATE);
PKG_LEAVE.Update_Leave_By_PK_PSNDOC(TEMP_CUR_RECORD.PK_PSNDOC,TEMP_CUR_RECORD.PK_HRORG,vPK_TIMEITEM,pLEAVE_YEAR,vAnnual_day,vResult);
else
if vAnnual_day>vTotal_Annual_day then --当月出现部分看年假，部分司龄假
--新增加年假
vPK_TIMEITEM :='1002Z710000000021ZLJ'; --当前假期类型 1、年假 2、司龄假 3、上年年假 4、上年司龄假 5、调整年假 6、调整司龄假 7、年假延期 8、司龄假延期
vLEAVE_QTY:=vAnnual_day-vTotal_Annual_day;
insert into HR_EMPLOYEE_ENJOY_LEAVE( ID, PK_PSNDOC,PK_ORG,LEAVE_YYYY,LEAVE_MM,PK_TIMEITEM,LEAVE_QTY,START_DATE,END_DATE) values(HR_EMPLOYEE_ENJOY_LEAVE_SEQ.NEXTVAL,TEMP_CUR_RECORD.PK_PSNDOC,TEMP_CUR_RECORD.PK_HRORG,pLEAVE_YEAR,pLEAVE_MONTH,vPK_TIMEITEM,Ceil(vLEAVE_QTY * 1000)/1000,vSTART_DATE,vEND_DATE);
PKG_LEAVE.Update_Leave_By_PK_PSNDOC(TEMP_CUR_RECORD.PK_PSNDOC,TEMP_CUR_RECORD.PK_HRORG,vPK_TIMEITEM,pLEAVE_YEAR,vAnnual_day,vResult);
--新增加司龄假
vPK_TIMEITEM :='10018G1000000000BM2N'; --当前假期类型 1、年假 2、司龄假 3、上年年假 4、上年司龄假 5、调整年假 6、调整司龄假 7、年假延期 8、司龄假延期
vLEAVE_QTY:=vAverage_monthly_holidays-(vAnnual_day-vTotal_Annual_day);
insert into HR_EMPLOYEE_ENJOY_LEAVE( ID, PK_PSNDOC,PK_ORG,LEAVE_YYYY,LEAVE_MM,PK_TIMEITEM,LEAVE_QTY,START_DATE,END_DATE) values(HR_EMPLOYEE_ENJOY_LEAVE_SEQ.NEXTVAL,TEMP_CUR_RECORD.PK_PSNDOC,TEMP_CUR_RECORD.PK_HRORG,pLEAVE_YEAR,pLEAVE_MONTH,vPK_TIMEITEM,Ceil(vLEAVE_QTY * 1000)/1000,vSTART_DATE,vEND_DATE);
PKG_LEAVE.Update_Leave_By_PK_PSNDOC(TEMP_CUR_RECORD.PK_PSNDOC,TEMP_CUR_RECORD.PK_HRORG,vPK_TIMEITEM,pLEAVE_YEAR,TEMP_CUR_RECORD.working_age_day,vResult);
else --年假已分派完，紧剩司龄假
vPK_TIMEITEM :='10018G1000000000BM2N'; --当前假期类型 1、年假 2、司龄假 3、上年年假 4、上年司龄假 5、调整年假 6、调整司龄假 7、年假延期 8、司龄假延期
vLEAVE_QTY:=vAverage_monthly_holidays;
--新增加司龄假
insert into HR_EMPLOYEE_ENJOY_LEAVE( ID, PK_PSNDOC,PK_ORG,LEAVE_YYYY,LEAVE_MM,PK_TIMEITEM,LEAVE_QTY,START_DATE,END_DATE) values(HR_EMPLOYEE_ENJOY_LEAVE_SEQ.NEXTVAL,TEMP_CUR_RECORD.PK_PSNDOC,TEMP_CUR_RECORD.PK_HRORG,pLEAVE_YEAR,pLEAVE_MONTH,vPK_TIMEITEM,Ceil(vLEAVE_QTY * 1000)/1000,vSTART_DATE,vEND_DATE);
PKG_LEAVE.Update_Leave_By_PK_PSNDOC(TEMP_CUR_RECORD.PK_PSNDOC,TEMP_CUR_RECORD.PK_HRORG,vPK_TIMEITEM,pLEAVE_YEAR,TEMP_CUR_RECORD.working_age_day,vResult);
end if;
end if;
end if;
end if;
end if;
----当前假期类型 1、年假 2、司龄假 3、上年年假 4、上年司龄假 5、调整年假 6、调整司龄假 7、年假延期 8、司龄假延期（注意假期有效期）
/*select pk_psndoc 人员基本信息,
pk_psnorg 人员基本信息,
ce.pk_org 人员组织关系,
org.name 组织,
item.timeitemname 休假类别,
settlementdate 结算日期,
curyear 年度,
curmonth 期间,
lastdayorhour 上期结余,
curdayorhour 享有,
realdayorhour 当前享有, 10天
yidayorhour 已休, 3
freezedayorhour 冻结 3
restdayorhour 剩余

from tbm_leavebalance ce --(假期计算)
inner join tbm_timeitem item
on item.pk_timeitem = ce.pk_timeitem
inner join org_orgs org
on org.pk_org = ce.pk_org */
end if;
end loop;
presult:='Success';
end if;
commit;

end Gen_Employee_Leave;

/*********************************************************************************************************************
NAME: Update_Employee_Leave

DESCRIPTION: 每日定时更新员工年假司龄假

PARAMETERS: 计算假期年份 pLEAVE_MONTH 计算假期月份

Return: Success/Failed

USAGE: PKG_LEAVE.Update_Employee_Leave();

HISOTORY:
DATE NAME DESCRIPTION
2016-1-15 River PROCEDURE CREATED
***********************************************************************************************************************/
PROCEDURE Update_All_Employee_Leave(presult out varchar2) IS
begin

/*
1002Z710000000021ZLJ 年假
10018G1000000000BM37 上年年假
10018G1000000000BM2T 调整年假
10018G1000000000BM2P 年假延期

10018G1000000000BM2N 司龄假
10018G1000000000BM39 上年司龄假
10018G1000000000BM2R 调整司龄假
10018G1000000000BM2V 司龄假延期

10018G1000000000BM2L 补休
10018G1000000000BM33 调整补休
10018G1000000000BM35 补休延期

*/

--年假
--非香港地区
update tbm_leavebalance t set
t.realdayorhour=nvl((select nvl(sum(e.LEAVE_QTY),0)
from HR_EMPLOYEE_ENJOY_LEAVE e
where e.pk_psndoc=t.pk_psndoc
and e.pk_org=t.pk_org and e.start_date<sysdate
and e.PK_TIMEITEM in ('1002Z710000000021ZLJ','10018G1000000000BM2T','10018G1000000000BM2P')
and t.curyear = to_char(e.START_DATE,'YYYY')) ,0)
,
t.restdayorhour=nvl((select nvl(sum(e.LEAVE_QTY),0)
from HR_EMPLOYEE_ENJOY_LEAVE e
where e.pk_psndoc=t.pk_psndoc and e.pk_org=t.pk_org and e.start_date<sysdate and e.PK_TIMEITEM in ('1002Z710000000021ZLJ','10018G1000000000BM2T','10018G1000000000BM2P') and t.curyear = to_char(e.START_DATE,'YYYY')),0)
+t.lastdayorhour-t.yidayorhour

where t.pk_psndoc in (select psn.PK_PSNDOC from bd_psndoc psn inner join hi_psnjob job on psn.pk_psndoc=job.pk_psndoc and job.lastflag='Y'and job.ismainjob='Y' where job.begindate is not null) and

t.curyear=to_char(sysdate,'YYYY') and
t.pk_timeitem='1002Z710000000021ZLJ' and t.pk_org not in('00018G1000000000I1A7','00016H10000000000BIO','00016H10000000000BI8','00016H10000000000BI5','00016H10000000000BIC','00016H10000000000BIF','00016H10000000000BII','00016H10000000000BIL');
--香港地区，以0.5为单位显示
update tbm_leavebalance t set
t.realdayorhour=round(nvl((select nvl(sum(e.LEAVE_QTY),0)
from HR_EMPLOYEE_ENJOY_LEAVE e
where e.pk_psndoc=t.pk_psndoc
and e.pk_org=t.pk_org and e.start_date<sysdate
and e.PK_TIMEITEM in ('1002Z710000000021ZLJ','10018G1000000000BM2T','10018G1000000000BM2P')
and t.curyear = to_char(e.START_DATE,'YYYY')) ,0)*2,0)/2
,
t.restdayorhour=round(nvl((select nvl(sum(e.LEAVE_QTY),0)
from HR_EMPLOYEE_ENJOY_LEAVE e
where e.pk_psndoc=t.pk_psndoc and e.pk_org=t.pk_org and e.start_date<sysdate and e.PK_TIMEITEM in ('1002Z710000000021ZLJ','10018G1000000000BM2T','10018G1000000000BM2P') and t.curyear = to_char(e.START_DATE,'YYYY')),0)*2,0)/2
+t.lastdayorhour-t.yidayorhour

where t.pk_psndoc in (select psn.PK_PSNDOC from bd_psndoc psn inner join hi_psnjob job on psn.pk_psndoc=job.pk_psndoc and job.lastflag='Y'and job.ismainjob='Y' where job.begindate is not null ) and

t.curyear=to_char(sysdate,'YYYY') and
t.pk_timeitem='1002Z710000000021ZLJ' and
t.pk_org in('00018G1000000000I1A7','00016H10000000000BIO','00016H10000000000BI8','00016H10000000000BI5','00016H10000000000BIC','00016H10000000000BIF','00016H10000000000BII','00016H10000000000BIL');

--司龄假
update tbm_leavebalance t set
t.realdayorhour=nvl((select nvl(sum(e.LEAVE_QTY),0)
from HR_EMPLOYEE_ENJOY_LEAVE e
where e.pk_psndoc=t.pk_psndoc and e.pk_org=t.pk_org and e.start_date<sysdate and e.PK_TIMEITEM in ('10018G1000000000BM2N','10018G1000000000BM2R','10018G1000000000BM2V') and t.curyear = to_char(e.START_DATE,'YYYY')),0)
,
t.restdayorhour=nvl((select nvl(sum(e.LEAVE_QTY),0)
from HR_EMPLOYEE_ENJOY_LEAVE e
where e.pk_psndoc=t.pk_psndoc and e.pk_org=t.pk_org and e.start_date<sysdate and e.PK_TIMEITEM in ('10018G1000000000BM2N','10018G1000000000BM2R','10018G1000000000BM2V') and t.curyear = to_char(e.START_DATE,'YYYY')),0)
+t.lastdayorhour-t.yidayorhour
where t.pk_psndoc in (select psn.PK_PSNDOC from bd_psndoc psn inner join hi_psnjob job on psn.pk_psndoc=job.pk_psndoc and job.lastflag='Y'and job.ismainjob='Y' where job.begindate is not null) and
t.curyear=to_char(sysdate,'YYYY') and
t.pk_timeitem='10018G1000000000BM2N';

--补休
/* update tbm_leavebalance t set
t.realdayorhour=(select nvl(sum(e.LEAVE_QTY),0)
from HR_EMPLOYEE_ENJOY_LEAVE e
where e.pk_psndoc=t.pk_psndoc
and e.pk_org=t.pk_org
and e.PK_TIMEITEM in ('10018G1000000000BM2L','10018G1000000000BM33','10018G1000000000BM35')
and t.curyear = to_char(e.START_DATE,'YYYY'))
,
t.restdayorhour=(select nvl(sum(e.LEAVE_QTY),0)
from HR_EMPLOYEE_ENJOY_LEAVE e
where e.pk_psndoc=t.pk_psndoc
and e.pk_org=t.pk_org
and e.PK_TIMEITEM in ('10018G1000000000BM2L','10018G1000000000BM33','10018G1000000000BM35')
and t.curyear = to_char(e.START_DATE,'YYYY'))
+t.lastdayorhour-t.yidayorhour
where t.pk_psndoc in (select psn.PK_PSNDOC from bd_psndoc psn inner join hi_psnjob job on psn.pk_psndoc=job.pk_psndoc and job.lastflag='Y' where job.begindate is not null) and
t.curyear=to_char(sysdate,'YYYY') and
t.pk_timeitem='10018G1000000000BM2L';

/* lastdayorhour 上期结余,
curdayorhour 享有,
realdayorhour 当前享有, 10天
yidayorhour 已休, 3
freezedayorhour 冻结 3
restdayorhour 结余 7-3

restdayorhour 剩余 4
*/

update tbm_leavebalance t set t.lastdayorhour=0,
t.curdayorhour=0,
t.realdayorhour=nvl(( select
sum(h.leave_qty-nvl((select sum(m.COMPENSATION_QTY) from HR_ELEAVE_MAPPING m where m.hr_employee_enjoy_leave_id = h.id),0))
from HR_EMPLOYEE_ENJOY_LEAVE h
where h.pk_psndoc=t.pk_psndoc and h.end_date>sysdate and h.pk_timeitem in ('10018G1000000000BM2L','10018G1000000000BM33','10018G1000000000BM35')
),0)+t.freezedayorhour+t.yidayorhour,
t.restdayorhour=nvl(( select
sum(h.leave_qty-nvl((select sum(m.COMPENSATION_QTY) from HR_ELEAVE_MAPPING m where m.hr_employee_enjoy_leave_id = h.id),0))
from HR_EMPLOYEE_ENJOY_LEAVE h
where h.pk_psndoc=t.pk_psndoc and h.end_date>sysdate and h.pk_timeitem in ('10018G1000000000BM2L','10018G1000000000BM33','10018G1000000000BM35')
),0)+t.freezedayorhour

where t.pk_psndoc in (select psn.PK_PSNDOC from bd_psndoc psn inner join hi_psnjob job on psn.pk_psndoc=job.pk_psndoc and job.lastflag='Y'and job.ismainjob='Y' where job.begindate is not null) and
t.curyear=to_char(sysdate,'YYYY') and
t.pk_timeitem='10018G1000000000BM2L';


commit;
presult:='Success';
end Update_All_Employee_Leave;

/*********************************************************************************************************************
NAME: Update_Leave_By_PK_PSNDOC

DESCRIPTION: 每日员工年假司龄假补休

PARAMETERS: 计算假期年份 pLEAVE_MONTH 计算假期月份

Return: Success/Failed

USAGE: PKG_LEAVE.Update_Leave_By_PK_PSNDOC();

HISOTORY:
DATE NAME DESCRIPTION
2016-1-19 River PROCEDURE CREATED
***********************************************************************************************************************/

PROCEDURE Update_Leave_By_PK_PSNDOC(pPK_PSNDOC CHAR,pPK_ORG char,pPK_Timeitem char,pCuryear char,pCurdayorhour number,presult out varchar2) IS
begin

/*
select pk_psndoc 人员基本信息,
pk_psnorg 人员基本信息,
ce.pk_org 人员组织关系,
org.name 组织,
item.timeitemname 休假类别,
settlementdate 结算日期,
curyear 年度,
curmonth 期间,
lastdayorhour 上期结余,
curdayorhour 享有,
realdayorhour 当前享有, 10天
yidayorhour 已休, 3
freezedayorhour 冻结 3
restdayorhour 剩余

from tbm_leavebalance ce --(假期计算)
inner join tbm_timeitem item
on item.pk_timeitem = ce.pk_timeitem
inner join org_orgs org
on org.pk_org = ce.pk_org

--tbm_leavebalance
select lh.pk_psndoc 员工,
item.timeitemname 假期类型,
lh.approve_state 审批状态,-- 0 审批未通过 -1 自由态 1 审批通过 2 审批进行中 3 提交 ps:除审批通过的单据都为冻结状态
lb.pk_leaveb,
lb.leavebegintime 休假开始时间,
lb.leaveendtime 休假结束时间,
lb.leavehour 休假时长
from tbm_leaveb lb
left join tbm_leaveh lh on lb.pk_leaveh = lh.pk_leaveh
left join tbm_timeitem item on lh.pk_leavetype = item.pk_timeitem
where item.timeitemname in ('事假', '司龄假', '补休')

结余=冻结+可用
上期结余+当前享有=已休+结余
lastdayorhour 上期结余,
realdayorhour 当前享有, 10天
yidayorhour 已休, 3
freezedayorhour 冻结 3
restdayorhour 结余

当前享有=已休+结余-上期结余 *- 冻结
结余=当前享有+上期结余-已休

t.realdayorhour=t.yidayorhour+(select nvl(sum(e.LEAVE_QTY-nvl((select sum(m.compensation_qty) from HR_ELEAVE_MAPPING m where m.hr_employee_enjoy_leave_id=e.id),0)),0)
from HR_EMPLOYEE_ENJOY_LEAVE e
where e.pk_psndoc=t.pk_psndoc and e.PK_TIMEITEM in ('1002Z710000000021ZLJ','10018G1000000000BM2T','10018G1000000000BM2P') and t.curyear = to_char(e.START_DATE,'YYYY')) --若假期跨年会前后2年重复计假t.curyear between to_char(e.START_DATE,'YYYY') and to_char(e.END_DATE,'YYYY'))
-t.lastdayorhour,

t.restdayorhour=(select nvl(sum(e.LEAVE_QTY-nvl((select sum(m.compensation_qty) from HR_ELEAVE_MAPPING m where m.hr_employee_enjoy_leave_id=e.id),0)),0)
from HR_EMPLOYEE_ENJOY_LEAVE e
where e.pk_psndoc=t.pk_psndoc and e.PK_TIMEITEM in ('1002Z710000000021ZLJ','10018G1000000000BM37','10018G1000000000BM2T','10018G1000000000BM2P') and t.curyear = to_char(e.START_DATE,'YYYY')) --若假期跨年会前后2年重复计假t.curyear between to_char(e.START_DATE,'YYYY') and to_char(e.END_DATE,'YYYY'))

*/

presult:='Failed';
if pPK_Timeitem='1002Z710000000021ZLJ' or pPK_Timeitem='10018G1000000000BM37' or pPK_Timeitem='10018G1000000000BM2T' or pPK_Timeitem='10018G1000000000BM2P' then --年假
--判断是否是香港地区
--substr(TEMP_CUR_RECORD.org_code,1,2)='HK'
if pPK_ORG in('00018G1000000000I1A7','00016H10000000000BIO','00016H10000000000BI8','00016H10000000000BI5','00016H10000000000BIC','00016H10000000000BIF','00016H10000000000BII','00016H10000000000BIL') then
--更新香港人员年假，0.5为最小单位
update tbm_leavebalance t set
t.curdayorhour=round(decode(pCurdayorhour,-999,t.curdayorhour,pCurdayorhour)*2,0)/2,
t.realdayorhour=round((select nvl(sum(e.LEAVE_QTY),0)*2
from HR_EMPLOYEE_ENJOY_LEAVE e
where e.pk_psndoc=t.pk_psndoc and e.pk_org=t.pk_org and e.start_date<sysdate and e.PK_TIMEITEM in ('1002Z710000000021ZLJ','10018G1000000000BM2T','10018G1000000000BM2P') and t.curyear = to_char(e.START_DATE,'YYYY')),0)/2 --若假期跨年会前后2年重复计假t.curyear between to_char(e.START_DATE,'YYYY') and to_char(e.END_DATE,'YYYY'))
,

t.restdayorhour=round((select nvl(sum(e.LEAVE_QTY),0)*2
from HR_EMPLOYEE_ENJOY_LEAVE e
where e.pk_psndoc=t.pk_psndoc and e.pk_org=t.pk_org and e.start_date<sysdate and e.PK_TIMEITEM in ('1002Z710000000021ZLJ','10018G1000000000BM2T','10018G1000000000BM2P') and t.curyear = to_char(e.START_DATE,'YYYY')),0)/2 --若假期跨年会前后2年重复计假t.curyear between to_char(e.START_DATE,'YYYY') and to_char(e.END_DATE,'YYYY'))
+t.lastdayorhour-t.yidayorhour
where t.pk_psndoc =pPK_PSNDOC and t.pk_org=pPK_ORG and t.curyear=pCuryear and t.pk_timeitem='1002Z710000000021ZLJ';

presult:='Success';
else
--非香港地区年假更新
update tbm_leavebalance t set
t.curdayorhour=decode(pCurdayorhour,-999,t.curdayorhour,pCurdayorhour),
t.realdayorhour=(select nvl(sum(e.LEAVE_QTY),0)
from HR_EMPLOYEE_ENJOY_LEAVE e
where e.pk_psndoc=t.pk_psndoc and e.pk_org=t.pk_org and e.start_date<sysdate and e.PK_TIMEITEM in ('1002Z710000000021ZLJ','10018G1000000000BM2T','10018G1000000000BM2P') and t.curyear = to_char(e.START_DATE,'YYYY')) --若假期跨年会前后2年重复计假t.curyear between to_char(e.START_DATE,'YYYY') and to_char(e.END_DATE,'YYYY'))
,

t.restdayorhour=(select nvl(sum(e.LEAVE_QTY),0)
from HR_EMPLOYEE_ENJOY_LEAVE e
where e.pk_psndoc=t.pk_psndoc and e.pk_org=t.pk_org and e.start_date<sysdate and e.PK_TIMEITEM in ('1002Z710000000021ZLJ','10018G1000000000BM2T','10018G1000000000BM2P') and t.curyear = to_char(e.START_DATE,'YYYY')) --若假期跨年会前后2年重复计假t.curyear between to_char(e.START_DATE,'YYYY') and to_char(e.END_DATE,'YYYY'))
+t.lastdayorhour-t.yidayorhour
where t.pk_psndoc =pPK_PSNDOC and t.pk_org=pPK_ORG and t.curyear=pCuryear and t.pk_timeitem='1002Z710000000021ZLJ';

presult:='Success';

end if;




elsif pPK_Timeitem='10018G1000000000BM2N' or pPK_Timeitem='10018G1000000000BM39' or pPK_Timeitem='10018G1000000000BM2R' or pPK_Timeitem='10018G1000000000BM2V' then --司龄假
update tbm_leavebalance t set
t.curdayorhour=decode(pCurdayorhour,-999,t.curdayorhour,pCurdayorhour),
t.realdayorhour=(select nvl(sum(e.LEAVE_QTY),0)
from HR_EMPLOYEE_ENJOY_LEAVE e
where e.pk_psndoc=t.pk_psndoc and e.pk_org=t.pk_org and e.start_date<sysdate and e.PK_TIMEITEM in ('10018G1000000000BM2N','10018G1000000000BM2R','10018G1000000000BM2V') and t.curyear = to_char(e.START_DATE,'YYYY'))
,

t.restdayorhour=(select nvl(sum(e.LEAVE_QTY),0)
from HR_EMPLOYEE_ENJOY_LEAVE e
where e.pk_psndoc=t.pk_psndoc and e.pk_org=t.pk_org and e.start_date<sysdate and e.PK_TIMEITEM in ('10018G1000000000BM2N','10018G1000000000BM2R','10018G1000000000BM2V') and t.curyear = to_char(e.START_DATE,'YYYY'))
+t.lastdayorhour-t.yidayorhour
where t.pk_psndoc =pPK_PSNDOC and t.pk_org=pPK_ORG and t.curyear=pCuryear and t.pk_timeitem='10018G1000000000BM2N';

/*update tbm_leavebalance t set
t.curdayorhour=decode(pCurdayorhour,-999,t.curdayorhour,pCurdayorhour),
t.realdayorhour=(select nvl(sum(e.LEAVE_QTY),0)
from HR_EMPLOYEE_ENJOY_LEAVE e
where e.pk_psndoc=t.pk_psndoc and e.pk_org=t.pk_org and e.start_date<sysdate and e.PK_TIMEITEM in ('1002Z710000000021ZLJ','10018G1000000000BM2T','10018G1000000000BM2P') and t.curyear = to_char(e.START_DATE,'YYYY')) --若假期跨年会前后2年重复计假t.curyear between to_char(e.START_DATE,'YYYY') and to_char(e.END_DATE,'YYYY'))
,

t.restdayorhour=(select nvl(sum(e.LEAVE_QTY),0)
from HR_EMPLOYEE_ENJOY_LEAVE e
where e.pk_psndoc=t.pk_psndoc and e.pk_org=t.pk_org and e.start_date<sysdate and e.PK_TIMEITEM in ('1002Z710000000021ZLJ','10018G1000000000BM2T','10018G1000000000BM2P') and t.curyear = to_char(e.START_DATE,'YYYY')) --若假期跨年会前后2年重复计假t.curyear between to_char(e.START_DATE,'YYYY') and to_char(e.END_DATE,'YYYY'))
+t.lastdayorhour-t.yidayorhour
where t.pk_psndoc =pPK_PSNDOC and t.pk_org=pPK_ORG and t.curyear=pCuryear and t.pk_timeitem='1002Z710000000021ZLJ';*/

presult:='Success';

elsif pPK_Timeitem='10018G1000000000BM2L' or pPK_Timeitem='10018G1000000000BM33' or pPK_Timeitem='10018G1000000000BM35' then--补休
/* update tbm_leavebalance t set
t.realdayorhour=(select nvl(sum(e.LEAVE_QTY),0)
from HR_EMPLOYEE_ENJOY_LEAVE e
where e.pk_psndoc=t.pk_psndoc and e.pk_org=t.pk_org and e.PK_TIMEITEM in ('10018G1000000000BM2L','10018G1000000000BM33','10018G1000000000BM35') and t.curyear = to_char(e.START_DATE,'YYYY'))
,

t.restdayorhour=(select nvl(sum(e.LEAVE_QTY),0)
from HR_EMPLOYEE_ENJOY_LEAVE e
where e.pk_psndoc=t.pk_psndoc and e.pk_org=t.pk_org and e.PK_TIMEITEM in ('10018G1000000000BM2L','10018G1000000000BM33','10018G1000000000BM35') and t.curyear = to_char(e.START_DATE,'YYYY'))
+t.lastdayorhour-t.yidayorhour
where t.pk_psndoc =pPK_PSNDOC and t.pk_org=pPK_ORG and t.curyear=pCuryear and t.pk_timeitem='10018G1000000000BM2L';*/

/* lastdayorhour 上期结余,
curdayorhour 享有,
realdayorhour 当前享有, 10天
yidayorhour 已休, 3
freezedayorhour 冻结 3
restdayorhour 剩余 7*/

update tbm_leavebalance t set
t.lastdayorhour=0,
t.curdayorhour=0,
t.realdayorhour=nvl(( select
sum(h.leave_qty-nvl((select sum(m.COMPENSATION_QTY) from HR_ELEAVE_MAPPING m where m.hr_employee_enjoy_leave_id = h.id),0))
from HR_EMPLOYEE_ENJOY_LEAVE h
where h.pk_psndoc=t.pk_psndoc and h.end_date>sysdate and h.pk_timeitem in ('10018G1000000000BM2L','10018G1000000000BM33','10018G1000000000BM35')
),0)+t.freezedayorhour+t.yidayorhour,
t.restdayorhour=nvl(( select
sum(h.leave_qty-nvl((select sum(m.COMPENSATION_QTY) from HR_ELEAVE_MAPPING m where m.hr_employee_enjoy_leave_id = h.id),0))
from HR_EMPLOYEE_ENJOY_LEAVE h
where h.pk_psndoc=t.pk_psndoc and h.end_date>sysdate and h.pk_timeitem in ('10018G1000000000BM2L','10018G1000000000BM33','10018G1000000000BM35')
),0)+t.freezedayorhour
where t.pk_psndoc =pPK_PSNDOC and t.pk_org=pPK_ORG and t.curyear=pCuryear and t.pk_timeitem='10018G1000000000BM2L';

presult:='Success';

end if;

end Update_Leave_By_PK_PSNDOC;

/*********************************************************************************************************************
NAME: Insert_Leave_Mapping

DESCRIPTION: 假期享有与休假抵扣

PARAMETERS: pk_leaveb休假ID

Return: Success/Failed

USAGE: PKG_LEAVE.Insert_Leave_Mapping();

HISOTORY:
DATE NAME DESCRIPTION
2016-1-19 River PROCEDURE CREATED
-------------------------------------------------------------------------------
-- Create table
drop table HR_ELEAVE_MAPPING;
create table HR_ELEAVE_MAPPING
(
ID NUMBER not null,
HR_EMPLOYEE_ENJOY_LEAVE_ID NUMBER, --享有假期ID
pk_leaveb CHAR(20) not null, --休假ID
COMPENSATION_QTY NUMBER, --抵扣享有假期数量，假期单位与对应的享有假期ID一样
CREATE_DATE DATE default sysdate
);
-- Create/Recreate primary, unique and foreign key constraints
alter table HR_ELEAVE_MAPPING add constraint HR_ELEAVE_MAPPING_PK primary key (ID);
alter table HR_ELEAVE_MAPPING add constraint HR_ELEAVE_MAPPING_FK1 foreign key (pk_leaveb) references tbm_leaveb (pk_leaveb);
alter table HR_ELEAVE_MAPPING add constraint HR_ELEAVE_MAPPING_FK2 foreign key (HR_EMPLOYEE_ENJOY_LEAVE_ID) references HR_EMPLOYEE_ENJOY_LEAVE (ID);

drop sequence HR_ELEAVE_MAPPING_SEQ;

create sequence HR_ELEAVE_MAPPING_SEQ
minvalue 1
maxvalue 999999999999
start with 1
increment by 1
cache 30;
-------------------------------------------------------------------------------

--tbm_leavebalance 冻结+可用=结余；当前享有=实际享有；上期结余+调整+当前享有=结余

select lh.pk_psndoc 员工,
item.timeitemname 假期类型,
lh.approve_state 审批状态,-- 0 审批未通过 -1 自由态 1 审批通过 2 审批进行中 3 提交 ps:除审批通过的单据都为冻结状态
lb.leavebegintime 休假开始时间,
lb.leaveendtime 休假结束时间,
lb.leavehour 休假时长
from tbm_leaveb lb
left join tbm_leaveh lh on lb.pk_leaveh = lh.pk_leaveh
left join tbm_timeitem item on lh.pk_leavetype = item.pk_timeitem
where item.timeitemname in ('事假', '司龄假', '补休')

1002Z710000000021ZLJ 年假
10018G1000000000BM37 上年年假
10018G1000000000BM2T 调整年假
10018G1000000000BM2P 年假延期

10018G1000000000BM2N 司龄假
10018G1000000000BM39 上年司龄假
10018G1000000000BM2R 调整司龄假
10018G1000000000BM2V 司龄假延期

10018G1000000000BM2L 补休
10018G1000000000BM33 调整补休
10018G1000000000BM35 补休延期

tbm_leavereg

***********************************************************************************************************************/

PROCEDURE Insert_Leave_Mapping_OT(ppk_leaveb CHAR,result out varchar2)
AS
CURSOR TEMP_CUR IS
select
tmp.ENJOY_LEAVE_ID,
tmp.pk_org,
(tmp.leave_qty-tmp.Use_QTY) as leave_balance,
tmp.leave_unit
from (
select
t.id as ENJOY_LEAVE_ID,
t.pk_psndoc,
t.pk_org,
t.end_date,
nvl((select sum(m.COMPENSATION_QTY) from HR_ELEAVE_MAPPING m where m.hr_employee_enjoy_leave_id = t.id and m.pk_leaveb<>ppk_leaveb),0) as Use_QTY,
t.leave_qty,
t.leave_unit
from HR_EMPLOYEE_ENJOY_LEAVE t
inner join tbm_leaveh h on h.pk_psndoc=t.pk_psndoc and h.pk_org=t.pk_org --and h.pk_leavetype=t.pk_timeitem
inner join tbm_leaveb l on l.pk_leaveh = h.pk_leaveh
where
l.pk_leaveb=ppk_leaveb and
t.pk_timeitem in ('10018G1000000000BM2L','10018G1000000000BM33','10018G1000000000BM35') and
h.pk_leavetype in ('10018G1000000000BM2L','10018G1000000000BM33','10018G1000000000BM35') and
to_date(l.leaveenddate,'YYYY-MM-DD') between t.start_date and t.end_date
)tmp where tmp.leave_qty>tmp.Use_QTY
order by tmp.end_date;

vUse_Qty number;
vTotal_Enjoy_Qty number; --可休假
--vUse_Unit number; --1、天 2、小时
vResult varchar2(20);
vPK_PSNDOC CHAR(20);
vpk_org CHAR(20);
vCuryear CHAR(4);
vRecord_QTY number;

begin
result:='Failed';
select count(1) into vRecord_QTY from tbm_leaveb a where a.pk_leaveb=ppk_leaveb;

if vRecord_QTY>0 then--是否存在请假记录
--计算可休假
select
sum(tmp.leave_qty-tmp.Use_QTY) into vTotal_Enjoy_Qty
from (
select
nvl((select sum(m.COMPENSATION_QTY) from HR_ELEAVE_MAPPING m where m.hr_employee_enjoy_leave_id = t.id and m.pk_leaveb<>ppk_leaveb),0) as Use_QTY,
t.leave_qty
from HR_EMPLOYEE_ENJOY_LEAVE t
inner join tbm_leaveh h on h.pk_psndoc=t.pk_psndoc and h.pk_org=t.pk_org --and h.pk_leavetype=t.pk_timeitem
inner join tbm_leaveb l on l.pk_leaveh = h.pk_leaveh
where
l.pk_leaveb=ppk_leaveb and
t.pk_timeitem in ('10018G1000000000BM2L','10018G1000000000BM33','10018G1000000000BM35') and
h.pk_leavetype in ('10018G1000000000BM2L','10018G1000000000BM33','10018G1000000000BM35') and
to_date(l.leaveenddate,'YYYY-MM-DD') between t.start_date and t.end_date
)tmp where tmp.leave_qty>tmp.Use_QTY;


--计算休假
select h.pk_psndoc,h.pk_org,
substr(l.leaveenddate,1,4),
(l.leavehour+
nvl((select sum(o.differencehour)
from tbm_leavereg r
inner join tbm_leaveoff o on o.pk_leavereg=r.pk_leavereg
where r.pk_billsourceb=l.pk_leaveb),0)
)
into vPK_PSNDOC,vpk_org,vCuryear,vUse_Qty
from tbm_leaveb l
inner join tbm_leaveh h on h.pk_leaveh = l.pk_leaveh
where l.pk_leaveb=ppk_leaveb;


if vTotal_Enjoy_Qty>=vUse_Qty then --有足够假期抵扣
delete from HR_ELEAVE_MAPPING t where t.pk_leaveb=ppk_leaveb;

For TEMP_CUR_RECORD in TEMP_CUR loop
if TEMP_CUR_RECORD.leave_unit=1 then --天数作单位
if TEMP_CUR_RECORD.leave_balance>=vUse_Qty then
insert into HR_ELEAVE_MAPPING(ID,HR_EMPLOYEE_ENJOY_LEAVE_ID,pk_leaveb,COMPENSATION_QTY) VALUES(HR_ELEAVE_MAPPING_SEQ.NEXTVAL,TEMP_CUR_RECORD.ENJOY_LEAVE_ID,ppk_leaveb,vUse_Qty);

exit;
else
insert into HR_ELEAVE_MAPPING(ID,HR_EMPLOYEE_ENJOY_LEAVE_ID,pk_leaveb,COMPENSATION_QTY) VALUES(HR_ELEAVE_MAPPING_SEQ.NEXTVAL,TEMP_CUR_RECORD.ENJOY_LEAVE_ID,ppk_leaveb,TEMP_CUR_RECORD.leave_balance);

vUse_Qty:=vUse_Qty-TEMP_CUR_RECORD.leave_balance;

end if;

else --小时作单位
if TEMP_CUR_RECORD.leave_balance>=vUse_Qty then
insert into HR_ELEAVE_MAPPING(ID,HR_EMPLOYEE_ENJOY_LEAVE_ID,pk_leaveb,COMPENSATION_QTY) VALUES(HR_ELEAVE_MAPPING_SEQ.NEXTVAL,TEMP_CUR_RECORD.ENJOY_LEAVE_ID,ppk_leaveb,vUse_Qty);

exit;
else
insert into HR_ELEAVE_MAPPING(ID,HR_EMPLOYEE_ENJOY_LEAVE_ID,pk_leaveb,COMPENSATION_QTY) VALUES(HR_ELEAVE_MAPPING_SEQ.NEXTVAL,TEMP_CUR_RECORD.ENJOY_LEAVE_ID,ppk_leaveb,TEMP_CUR_RECORD.leave_balance);

vUse_Qty:=vUse_Qty-TEMP_CUR_RECORD.leave_balance;

end if;

end if;

end loop;

--更新员工假期
PKG_LEAVE.Update_Leave_By_PK_PSNDOC(vPK_PSNDOC,vpk_org,'10018G1000000000BM2L',vCuryear,-999,vResult);

commit;
result:='Success';
end if;

end if;

end Insert_Leave_Mapping_OT;

/*********************************************************************************************************************
NAME: Insert_Leave_Mapping_AL

alter table HR_EMPLOYEE_ENJOY_LEAVE add constraint HR_EMPLOYEE_ENJOY_LEAVE_UK unique (PK_PSNDOC, PK_TIMEITEM, START_DATE);

DESCRIPTION: 假期享有与年假抵扣 annual leave

PARAMETERS: pk_leaveb年假ID

Return: Success/Failed

USAGE: PKG_LEAVE.Insert_Leave_Mapping_AL();

1002Z710000000021ZLJ 年假
10018G1000000000BM37 上年年假
10018G1000000000BM2T 调整年假
10018G1000000000BM2P 年假延期

10018G1000000000BM2N 司龄假
10018G1000000000BM39 上年司龄假
10018G1000000000BM2R 调整司龄假
10018G1000000000BM2V 司龄假延期

10018G1000000000BM2L 补休
10018G1000000000BM33 调整补休
10018G1000000000BM35 补休延期

HISOTORY:
DATE NAME DESCRIPTION
2016-1-19 River PROCEDURE CREATED
***********************************************************************************************************************/

PROCEDURE Insert_Leave_Mapping_AL(ppk_leaveb CHAR,result out varchar2)
AS
CURSOR TEMP_CUR IS
--1.查询数据查询未抵扣记录
select
tmp.ENJOY_LEAVE_ID,
tmp.pk_org,
(tmp.leave_qty-tmp.Use_QTY) as leave_balance,--剩余未抵扣的假期
tmp.leave_unit
from (
select
t.id as ENJOY_LEAVE_ID,
t.pk_psndoc,
t.pk_org,
t.end_date,
--排除当前单据的数据，考虑修改的情况
nvl((select sum(m.COMPENSATION_QTY) from HR_ELEAVE_MAPPING m where m.hr_employee_enjoy_leave_id = t.id and m.pk_leaveb<>ppk_leaveb),0) as Use_QTY,
t.leave_qty,
t.leave_unit
from HR_EMPLOYEE_ENJOY_LEAVE t
inner join tbm_leaveh h on h.pk_psndoc=t.pk_psndoc and h.pk_org=t.pk_org --and h.pk_leavetype=t.pk_timeitem
inner join tbm_leaveb l on l.pk_leaveh = h.pk_leaveh
where
l.pk_leaveb=ppk_leaveb and
t.pk_timeitem in ('1002Z710000000021ZLJ','10018G1000000000BM37','10018G1000000000BM2T','10018G1000000000BM2P') and
h.pk_leavetype in ('1002Z710000000021ZLJ','10018G1000000000BM37','10018G1000000000BM2T','10018G1000000000BM2P') and
(to_date(l.leaveenddate,'YYYY-MM-DD') between t.start_date and t.end_date or sysdate between t.start_date and t.end_date)
)tmp where tmp.leave_qty>tmp.Use_QTY
order by tmp.end_date;

vUse_Qty number;
vTotal_Enjoy_Qty number; --可休假
vRestdayorhour number; --TW 可预支假
vHR_EMPLOYEE_ENJOY_LEAVE_ID number; --TW 可预支假ID
--vUse_Unit number; --1、天 2、小时

vPK_PSNDOC CHAR(20);
vorg_code CHAR(20);--isTW?
vpk_org CHAR(20);
vResult varchar2(20);
vCuryear CHAR(4);
vTW_Record_QTY number;
vRecord_QTY number;

begin
result:='Failed';
--是否为TW ，可预支假期给组织赋值
select substr(org.code,1,2) into vorg_code
from tbm_leaveh h
inner join tbm_leaveb l on l.pk_leaveh = h.pk_leaveh
inner join org_orgs org on h.pk_org = org.pk_org --?waite
where l.pk_leaveb=ppk_leaveb;


if vorg_code='TA'or vorg_code='HK' then --TW 可预支假，
--计算可预支年假本年应有+上年-已休-冻结+本单据的数据
select t.curdayorhour+t.lastdayorhour-t.yidayorhour-t.freezedayorhour+nvl((select leavehour from tbm_leaveb m where m.pk_leaveb=ppk_leaveb),0)
into vRestdayorhour
from tbm_leaveh h
inner join tbm_leaveb l on l.pk_leaveh = h.pk_leaveh
inner join tbm_leavebalance t on t.pk_psndoc=h.pk_psndoc and t.pk_org=h.pk_org
where l.pk_leaveb=ppk_leaveb and t.pk_timeitem='1002Z710000000021ZLJ' and t.curyear=to_char(sysdate,'YYYY');


--剩余假期抵扣数
select count(1) into vTW_Record_QTY
from (
select
nvl((select sum(m.COMPENSATION_QTY) from HR_ELEAVE_MAPPING m where m.hr_employee_enjoy_leave_id = t.id and m.pk_leaveb<>ppk_leaveb),0) as Use_QTY,
t.leave_qty
from HR_EMPLOYEE_ENJOY_LEAVE t
inner join tbm_leaveh h on h.pk_psndoc=t.pk_psndoc and h.pk_org=t.pk_org --and h.pk_leavetype=t.pk_timeitem
inner join tbm_leaveb l on l.pk_leaveh = h.pk_leaveh
where
l.pk_leaveb=ppk_leaveb and
t.pk_timeitem in ('1002Z710000000021ZLJ','10018G1000000000BM37','10018G1000000000BM2T','10018G1000000000BM2P') and
h.pk_leavetype in ('1002Z710000000021ZLJ','10018G1000000000BM37','10018G1000000000BM2T','10018G1000000000BM2P') and
(to_date(l.leaveenddate,'YYYY-MM-DD') between t.start_date and t.end_date or sysdate between t.start_date and t.end_date)
)tmp where tmp.leave_qty>tmp.Use_QTY;

end if;

select count(1) into vRecord_QTY from tbm_leaveb a where a.pk_leaveb=ppk_leaveb;

if vRecord_QTY>0 then--是否存在请假记录
--计算可休假
select
sum(tmp.leave_qty-tmp.Use_QTY) into vTotal_Enjoy_Qty
from (
select
t.id as ENJOY_LEAVE_ID,
t.pk_psndoc,
t.pk_org,
t.end_date,
nvl((select sum(m.COMPENSATION_QTY) from HR_ELEAVE_MAPPING m where m.hr_employee_enjoy_leave_id = t.id and m.pk_leaveb<>ppk_leaveb),0) as Use_QTY,
t.leave_qty,
t.leave_unit
from HR_EMPLOYEE_ENJOY_LEAVE t
inner join tbm_leaveh h on h.pk_psndoc=t.pk_psndoc and h.pk_org=t.pk_org --and h.pk_leavetype=t.pk_timeitem
inner join tbm_leaveb l on l.pk_leaveh = h.pk_leaveh
where
l.pk_leaveb=ppk_leaveb and
t.pk_timeitem in ('1002Z710000000021ZLJ','10018G1000000000BM37','10018G1000000000BM2T','10018G1000000000BM2P') and
h.pk_leavetype in ('1002Z710000000021ZLJ','10018G1000000000BM37','10018G1000000000BM2T','10018G1000000000BM2P') and
(to_date(l.leaveenddate,'YYYY-MM-DD') between t.start_date and t.end_date or sysdate between t.start_date and t.end_date)
)tmp where tmp.leave_qty>tmp.Use_QTY;


--计算休假
select h.pk_psndoc,h.pk_org,
substr(l.leaveenddate,1,4),
(l.leavehour+
nvl((select sum(o.differencehour) --differencehour 差异时长负数
from tbm_leavereg r
inner join tbm_leaveoff o on o.pk_leavereg=r.pk_leavereg
where r.pk_billsourceb=l.pk_leaveb),0)
)
into vPK_PSNDOC,vpk_org,vCuryear,vUse_Qty
from tbm_leaveb l
inner join tbm_leaveh h on h.pk_leaveh = l.pk_leaveh
where l.pk_leaveb=ppk_leaveb;


if vTotal_Enjoy_Qty>=vUse_Qty or (vRestdayorhour>=vUse_Qty and vorg_code='TA') OR (vRestdayorhour>=vUse_Qty and vorg_code='HK') then --TW 可预支假，但不能超出， PRC HK 检测是否有足够假期抵扣
delete from HR_ELEAVE_MAPPING t where t.pk_leaveb=ppk_leaveb;

/*if (vTW_Record_QTY=0 and vorg_code='TA') OR (vTW_Record_QTY=0 and vorg_code='HK') then --TW 预支假期，没抵扣判断无效
select HR_EMPLOYEE_ENJOY_LEAVE_SEQ.NEXTVAL into vHR_EMPLOYEE_ENJOY_LEAVE_ID from dual;

insert into HR_EMPLOYEE_ENJOY_LEAVE( ID, PK_PSNDOC,PK_ORG,LEAVE_YYYY,LEAVE_MM,PK_TIMEITEM,LEAVE_QTY,START_DATE,END_DATE) values(vHR_EMPLOYEE_ENJOY_LEAVE_ID,vPK_PSNDOC,vpk_org,vCuryear,to_char(sysdate,'MM'),'1002Z710000000021ZLJ',0,sysdate,add_months(sysdate,1));

insert into HR_ELEAVE_MAPPING(ID,HR_EMPLOYEE_ENJOY_LEAVE_ID,pk_leaveb,COMPENSATION_QTY) VALUES(HR_ELEAVE_MAPPING_SEQ.NEXTVAL,vHR_EMPLOYEE_ENJOY_LEAVE_ID,ppk_leaveb,vUse_Qty);

else*/
For TEMP_CUR_RECORD in TEMP_CUR loop
--小时作单位
if TEMP_CUR_RECORD.leave_balance>=vUse_Qty then
insert into HR_ELEAVE_MAPPING(ID,HR_EMPLOYEE_ENJOY_LEAVE_ID,pk_leaveb,COMPENSATION_QTY) VALUES(HR_ELEAVE_MAPPING_SEQ.NEXTVAL,TEMP_CUR_RECORD.ENJOY_LEAVE_ID,ppk_leaveb,vUse_Qty);

exit;
else
insert into HR_ELEAVE_MAPPING(ID,HR_EMPLOYEE_ENJOY_LEAVE_ID,pk_leaveb,COMPENSATION_QTY) VALUES(HR_ELEAVE_MAPPING_SEQ.NEXTVAL,TEMP_CUR_RECORD.ENJOY_LEAVE_ID,ppk_leaveb,TEMP_CUR_RECORD.leave_balance);

vUse_Qty:=vUse_Qty-TEMP_CUR_RECORD.leave_balance;

end if;

end loop;

--end if;

--更新员工假期
PKG_LEAVE.Update_Leave_By_PK_PSNDOC(vPK_PSNDOC,vpk_org,'1002Z710000000021ZLJ',vCuryear,-999,vResult);

commit;
result:='Success';
end if;

end if;

end Insert_Leave_Mapping_AL;

/*********************************************************************************************************************
NAME: Insert_Leave_Mapping_WL

DESCRIPTION: 假期享有与司龄假抵扣 annual leave

PARAMETERS: pk_leaveb司龄假ID

Return: Success/Failed

USAGE: PKG_LEAVE.Insert_Leave_Mapping_WL();

1002Z710000000021ZLJ 年假
10018G1000000000BM37 上年年假
10018G1000000000BM2T 调整年假
10018G1000000000BM2P 年假延期

10018G1000000000BM2N 司龄假
10018G1000000000BM39 上年司龄假
10018G1000000000BM2R 调整司龄假
10018G1000000000BM2V 司龄假延期

10018G1000000000BM2L 补休
10018G1000000000BM33 调整补休
10018G1000000000BM35 补休延期

HISOTORY:
DATE NAME DESCRIPTION
2016-1-19 River PROCEDURE CREATED
***********************************************************************************************************************/

PROCEDURE Insert_Leave_Mapping_WL(ppk_leaveb CHAR,result out varchar2)
AS
CURSOR TEMP_CUR IS
select
tmp.ENJOY_LEAVE_ID,
tmp.pk_org,
(tmp.leave_qty-tmp.Use_QTY) as leave_balance,
tmp.leave_unit
from (
select
t.id as ENJOY_LEAVE_ID,
t.pk_psndoc,
t.pk_org,
t.end_date,
nvl((select sum(m.COMPENSATION_QTY) from HR_ELEAVE_MAPPING m where m.hr_employee_enjoy_leave_id = t.id and m.pk_leaveb<>ppk_leaveb),0) as Use_QTY,
t.leave_qty,
t.leave_unit
from HR_EMPLOYEE_ENJOY_LEAVE t
inner join tbm_leaveh h on h.pk_psndoc=t.pk_psndoc and h.pk_org=t.pk_org --and h.pk_leavetype=t.pk_timeitem
inner join tbm_leaveb l on l.pk_leaveh = h.pk_leaveh
where
l.pk_leaveb=ppk_leaveb and
t.pk_timeitem in ('10018G1000000000BM2N','10018G1000000000BM39','10018G1000000000BM2R','10018G1000000000BM2V') and
h.pk_leavetype in ('10018G1000000000BM2N','10018G1000000000BM39','10018G1000000000BM2R','10018G1000000000BM2V') and
(to_date(l.leaveenddate,'YYYY-MM-DD') between t.start_date and t.end_date or sysdate between t.start_date and t.end_date)
)tmp where tmp.leave_qty>tmp.Use_QTY
order by tmp.end_date;

vUse_Qty number;
vTotal_Enjoy_Qty number; --可休假
--vUse_Unit number; --1、天 2、小时

vPK_PSNDOC CHAR(20);
vpk_org CHAR(20);
vResult varchar2(20);
vCuryear CHAR(4);
vRecord_QTY number;

begin
result:='Failed';
select count(1) into vRecord_QTY from tbm_leaveb a where a.pk_leaveb=ppk_leaveb;
if vRecord_QTY>0 then --是否存在请假记录
--计算可休假
select
sum(tmp.leave_qty-tmp.Use_QTY) into vTotal_Enjoy_Qty
from (
select
nvl((select sum(m.COMPENSATION_QTY) from HR_ELEAVE_MAPPING m where m.hr_employee_enjoy_leave_id = t.id and m.pk_leaveb<>ppk_leaveb),0) as Use_QTY,
t.leave_qty
from HR_EMPLOYEE_ENJOY_LEAVE t
inner join tbm_leaveh h on h.pk_psndoc=t.pk_psndoc and h.pk_org=t.pk_org --and h.pk_leavetype=t.pk_timeitem
inner join tbm_leaveb l on l.pk_leaveh = h.pk_leaveh
where
l.pk_leaveb=ppk_leaveb and
t.pk_timeitem in ('10018G1000000000BM2N','10018G1000000000BM39','10018G1000000000BM2R','10018G1000000000BM2V') and
h.pk_leavetype in ('10018G1000000000BM2N','10018G1000000000BM39','10018G1000000000BM2R','10018G1000000000BM2V') and
(to_date(l.leaveenddate,'YYYY-MM-DD') between t.start_date and t.end_date or sysdate between t.start_date and t.end_date)
)tmp where tmp.leave_qty>tmp.Use_QTY;

--计算休假
select h.pk_psndoc,h.pk_org,
substr(l.leaveenddate,1,4),
(l.leavehour+
nvl((select sum(o.differencehour)
from tbm_leavereg r
inner join tbm_leaveoff o on o.pk_leavereg=r.pk_leavereg
where r.pk_billsourceb=l.pk_leaveb),0)
)
into vPK_PSNDOC,vpk_org,vCuryear,vUse_Qty
from tbm_leaveb l
inner join tbm_leaveh h on h.pk_leaveh = l.pk_leaveh
where l.pk_leaveb=ppk_leaveb;


if vTotal_Enjoy_Qty>=vUse_Qty then --有足够假期抵扣
delete from HR_ELEAVE_MAPPING t where t.pk_leaveb=ppk_leaveb;

For TEMP_CUR_RECORD in TEMP_CUR loop
if TEMP_CUR_RECORD.leave_unit=1 then --天数作单位
if TEMP_CUR_RECORD.leave_balance>=vUse_Qty then
insert into HR_ELEAVE_MAPPING(ID,HR_EMPLOYEE_ENJOY_LEAVE_ID,pk_leaveb,COMPENSATION_QTY) VALUES(HR_ELEAVE_MAPPING_SEQ.NEXTVAL,TEMP_CUR_RECORD.ENJOY_LEAVE_ID,ppk_leaveb,vUse_Qty);

exit;
else
insert into HR_ELEAVE_MAPPING(ID,HR_EMPLOYEE_ENJOY_LEAVE_ID,pk_leaveb,COMPENSATION_QTY) VALUES(HR_ELEAVE_MAPPING_SEQ.NEXTVAL,TEMP_CUR_RECORD.ENJOY_LEAVE_ID,ppk_leaveb,TEMP_CUR_RECORD.leave_balance);

vUse_Qty:=vUse_Qty-TEMP_CUR_RECORD.leave_balance;

end if;

else --小时作单位
if TEMP_CUR_RECORD.leave_balance>=vUse_Qty then
insert into HR_ELEAVE_MAPPING(ID,HR_EMPLOYEE_ENJOY_LEAVE_ID,pk_leaveb,COMPENSATION_QTY) VALUES(HR_ELEAVE_MAPPING_SEQ.NEXTVAL,TEMP_CUR_RECORD.ENJOY_LEAVE_ID,ppk_leaveb,vUse_Qty);

exit;
else
insert into HR_ELEAVE_MAPPING(ID,HR_EMPLOYEE_ENJOY_LEAVE_ID,pk_leaveb,COMPENSATION_QTY) VALUES(HR_ELEAVE_MAPPING_SEQ.NEXTVAL,TEMP_CUR_RECORD.ENJOY_LEAVE_ID,ppk_leaveb,TEMP_CUR_RECORD.leave_balance);

vUse_Qty:=vUse_Qty-TEMP_CUR_RECORD.leave_balance;

end if;

end if;

end loop;

--更新员工假期
PKG_LEAVE.Update_Leave_By_PK_PSNDOC(vPK_PSNDOC,vpk_org,'10018G1000000000BM2N',vCuryear,-999,vResult);
commit;
result:='Success';
end if;

end if;

end Insert_Leave_Mapping_WL;

/*******************************************************************************************************************
NAME: Insert_Leave_Mapping

DESCRIPTION: 假期享有与休假抵扣

PARAMETERS: pPK_PSNDOC
pPK_TIMEITEM:
pLeave_StartDate

RETURN: Success/Failed

USAGE: PKG_LEAVE.Insert_Leave_Mapping(pPK_PSNDOC IN Char,pPK_TIMEITEM IN Char,pLeave_StartDate IN Char);

HISOTORY:
DATE NAME DESCRIPTION
2016-02-02 RIVER HE FUNCTION CREATED

********************************************************************************************************************/

FUNCTION Insert_Leave_Mapping (pLeave_No IN varchar2) RETURN varchar2
AS
CURSOR TEMP_CUR IS
select
h.pk_leavetype,
l.pk_leaveb
from tbm_leaveh h
inner join tbm_leaveb l on l.pk_leaveh = h.pk_leaveh
where h.bill_code=pLeave_No;

-- where h.pk_psndoc=pPK_PSNDOC and h.pk_leavetype=pPK_TIMEITEM and to_char(l.leavebegindate,'YYYYMMDD')=pLeave_StartDate;

vResult varchar2(50);

begin
vResult:='Success';
For TEMP_CUR_RECORD in TEMP_CUR loop
if TEMP_CUR_RECORD.pk_leavetype='1002Z710000000021ZLJ' or TEMP_CUR_RECORD.pk_leavetype='10018G1000000000BM37' or TEMP_CUR_RECORD.pk_leavetype='10018G1000000000BM2T' or TEMP_CUR_RECORD.pk_leavetype='10018G1000000000BM2P' then --年假
PKG_LEAVE.Insert_Leave_Mapping_AL(TEMP_CUR_RECORD.pk_leaveb,vResult);
elsif TEMP_CUR_RECORD.pk_leavetype='10018G1000000000BM2N' or TEMP_CUR_RECORD.pk_leavetype='10018G1000000000BM39' or TEMP_CUR_RECORD.pk_leavetype='10018G1000000000BM2R' or TEMP_CUR_RECORD.pk_leavetype='10018G1000000000BM2V' then --司龄假
PKG_LEAVE.Insert_Leave_Mapping_WL(TEMP_CUR_RECORD.pk_leaveb,vResult);

elsif TEMP_CUR_RECORD.pk_leavetype='10018G1000000000BM2L' or TEMP_CUR_RECORD.pk_leavetype='10018G1000000000BM33' or TEMP_CUR_RECORD.pk_leavetype='10018G1000000000BM35' then --补休
PKG_LEAVE.Insert_Leave_Mapping_OT(TEMP_CUR_RECORD.pk_leaveb,vResult);

end if;

vResult:=vResult;
end loop;

return vResult;
end Insert_Leave_Mapping;

/*******************************************************************************************************************
NAME: Insert_Leave_Mapping

DESCRIPTION: 假期享有与休假抵扣

PARAMETERS: pPK_PSNDOC
pPK_TIMEITEM:
pLeave_StartDate YYYYMMDD

RETURN: Success/Failed

USAGE: PKG_LEAVE.pro_Insert_Leave_Mapping(pLeave_No);

HISOTORY:
DATE NAME DESCRIPTION
2016-02-05 RIVER HE FUNCTION CREATED
********************************************************************************************************************/

PROCEDURE pro_Insert_Leave_Mapping(pLeave_No varchar2,pResult out varchar2)
AS
CURSOR TEMP_CUR IS
select
h.pk_leavetype,
l.pk_leaveb
from tbm_leaveh h
inner join tbm_leaveb l on l.pk_leaveh = h.pk_leaveh
where h.bill_code=pLeave_No;

-- where h.pk_psndoc=pPK_PSNDOC and h.pk_leavetype=pPK_TIMEITEM and to_char(l.leavebegindate,'YYYYMMDD')=pLeave_StartDate;

begin
pResult:='Success';
For TEMP_CUR_RECORD in TEMP_CUR loop
if TEMP_CUR_RECORD.pk_leavetype='1002Z710000000021ZLJ' or TEMP_CUR_RECORD.pk_leavetype='10018G1000000000BM37' or TEMP_CUR_RECORD.pk_leavetype='10018G1000000000BM2T' or TEMP_CUR_RECORD.pk_leavetype='10018G1000000000BM2P' then --年假
PKG_LEAVE.Insert_Leave_Mapping_AL(TEMP_CUR_RECORD.pk_leaveb,pResult);
elsif TEMP_CUR_RECORD.pk_leavetype='10018G1000000000BM2N' or TEMP_CUR_RECORD.pk_leavetype='10018G1000000000BM39' or TEMP_CUR_RECORD.pk_leavetype='10018G1000000000BM2R' or TEMP_CUR_RECORD.pk_leavetype='10018G1000000000BM2V' then --司龄假
PKG_LEAVE.Insert_Leave_Mapping_WL(TEMP_CUR_RECORD.pk_leaveb,pResult);

elsif TEMP_CUR_RECORD.pk_leavetype='10018G1000000000BM2L' or TEMP_CUR_RECORD.pk_leavetype='10018G1000000000BM33' or TEMP_CUR_RECORD.pk_leavetype='10018G1000000000BM35' then --补休
PKG_LEAVE.Insert_Leave_Mapping_OT(TEMP_CUR_RECORD.pk_leaveb,pResult);

end if;

end loop;

end pro_Insert_Leave_Mapping;

/* 需要使用
CREATE OR REPLACE TRIGGER TRI_TBM_LEAVEB_D BEFORE DELETE ON TBM_LEAVEB FOR EACH ROW
declare
vPK_LEAVETYPE char(20);
vPK_PSNDOC char(20);
vCuryear char(4);
vResult varchar2(50);
BEGIN

select h.pk_leavetype,h.pk_psnjob,h.leaveyear into vPK_LEAVETYPE,vPK_PSNDOC,vCuryear from tbm_leaveh h where h.pk_leaveh=:OLD.pk_leaveh;

delete from HR_ELEAVE_MAPPING H WHERE H.PK_LEAVEB =:OLD.pk_leaveb;
PKG_LEAVE.Update_Leave_By_PK_PSNDOC(vPK_PSNDOC,vPK_LEAVETYPE,vCuryear,-999,vResult);

DBMS_OUTPUT.PUT_LINE('成功更新员工假期！');

END TRI_TBM_LEAVEB_D;

以下不用
-------------------------------
CREATE OR REPLACE TRIGGER TRI_TBM_LEAVEB AFTER INSERT OR UPDATE OR DELETE ON TBM_LEAVEB FOR EACH ROW
declare
vPK_LEAVETYPE char(20);
vresult varchar(30);
vPK_PSNDOC char(20);
vCuryear char(4);
BEGIN

--10018G1000000000BM2N 司龄假
--10018G1000000000BM39 上年司龄假
--10018G1000000000BM2R 调整司龄假
--10018G1000000000BM2V 司龄假延期

--1002Z710000000021ZLJ 年假
--10018G1000000000BM37 上年年假
--10018G1000000000BM2T 调整年假
--10018G1000000000BM2P 年假延期

--10018G1000000000BM2L 补休
--10018G1000000000BM33 调整补休
--10018G1000000000BM35 补休延期

select h.pk_leavetype,h.pk_psnjob,h.leaveyear into vPK_LEAVETYPE,vPK_PSNDOC,vCuryear from tbm_leaveh h where h.pk_leaveh=:NEW.pk_leaveh;

IF DELETING THEN
PKG_LEAVE.Update_Leave_By_PK_PSNDOC(vPK_PSNDOC,vPK_LEAVETYPE,vCuryear,-999);
DBMS_OUTPUT.PUT_LINE('成功更新员工假期！');
ELSE --INSERTING UPDATING
if vPK_LEAVETYPE='1002Z710000000021ZLJ' then
PKG_LEAVE.Insert_Leave_Mapping_AL(:NEW.pk_leaveb,vresult);
dbms_output.PUT_LINE('1 TRI_TBM_LEAVEB=' || vPK_LEAVETYPE);
elsif vPK_LEAVETYPE='10018G1000000000BM2N' then
PKG_LEAVE.Insert_Leave_Mapping_WL(:NEW.pk_leaveb,vresult);
dbms_output.PUT_LINE('2 TRI_TBM_LEAVEB=' || vPK_LEAVETYPE);
elsif vPK_LEAVETYPE='10018G1000000000BM2L' then
PKG_LEAVE.Insert_Leave_Mapping_OT(:NEW.pk_leaveb,vresult);
dbms_output.PUT_LINE('3 TRI_TBM_LEAVEB=' || vPK_LEAVETYPE);
end if;
END IF;

END TRI_TBM_LEAVEB;

CREATE OR REPLACE TRIGGER TRI_TBM_LEAVEB_D BEFORE DELETE ON TBM_LEAVEB FOR EACH ROW
BEGIN
delete from HR_ELEAVE_MAPPING H WHERE H.PK_LEAVEB=:OLD.pk_leaveb;
DBMS_OUTPUT.PUT_LINE('成功更新员工假期！');
END TRI_TBM_LEAVEB_D;

----------------------------------
CREATE OR REPLACE TRIGGER TRI_HR_EMPLOYEE_ENJOY_LEAVE AFTER INSERT OR UPDATE OR DELETE ON HR_EMPLOYEE_ENJOY_LEAVE FOR EACH ROW
declare
vPK_LEAVETYPE char(20);
vresult varchar(30);
BEGIN
---
1002Z710000000021ZLJ 年假
10018G1000000000BM37 上年年假
10018G1000000000BM2T 调整年假
10018G1000000000BM2P 年假延期

10018G1000000000BM2N 司龄假
10018G1000000000BM39 上年司龄假
10018G1000000000BM2R 调整司龄假
10018G1000000000BM2V 司龄假延期

10018G1000000000BM2L 补休
10018G1000000000BM33 调整补休
10018G1000000000BM35 补休延期
---
if :NEW.PK_TIMEITEM='10018G1000000000BM2T' or :NEW.PK_TIMEITEM='10018G1000000000BM2R' or :NEW.PK_TIMEITEM='10018G1000000000BM2P' or :NEW.PK_TIMEITEM='10018G1000000000BM2V' or :NEW.PK_TIMEITEM='10018G1000000000BM2L' or :NEW.PK_TIMEITEM='10018G1000000000BM33' or :NEW.PK_TIMEITEM='10018G1000000000BM35' then
PKG_LEAVE.Update_Leave_By_PK_PSNDOC(:NEW.PK_PSNDOC,:NEW.PK_TIMEITEM,:NEW.LEAVE_YYYY,-999);
--dbms_output.PUT_LINE('1 TRI_TBM_LEAVEB=' || vPK_LEAVETYPE);
end if;
END TRI_HR_EMPLOYEE_ENJOY_LEAVE;

-------------
declare
vresult varchar2(20);
begin

PKG_LEAVE.Auto_Gen_Employee_Leave(to_number(to_char(add_months(sysdate,-1),'YYYY')) ,to_number(to_char(add_months(sysdate,-1),'YYYY')),vresult);

PKG_LEAVE.Auto_Gen_Employee_Leave(to_number(to_char(sysdate,'YYYY')) ,to_number(to_char(sysdate,'YYYY')),vresult);

end;
-------------
select pk_timeitem,timeitemname from tbm_timeitem where timeitemname in ('年假','司龄假','上年年假','上年司龄假','调整年假','调整司龄假','年假延期','司龄假延期','补休','调整补休','补休延期')

*/
---add by waite
---定时追补预支假期抵扣
PROCEDURE pro_Supplement_Leave_Mapping(pResult out varchar2)
AS
CURSOR TEMP_CUR IS
--查询假期未插入Mapping的数据
select p.code, p.name, p.glbdef1,h.pk_leavetype,h.bill_code
from tbm_leaveb b
inner join tbm_leaveh h
on b.pk_leaveh = h.pk_leaveh
inner join bd_psndoc p
on p.pk_psndoc = h.pk_psndoc
where b.pk_leaveb not in (select m.pk_leaveb from hr_eleave_mapping m)and h.pk_leavetype in('1002Z710000000021ZLJ','10018G1000000000BM2N','10018G1000000000BM2L') ;

-- where h.pk_psndoc=pPK_PSNDOC and h.pk_leavetype=pPK_TIMEITEM and to_char(l.leavebegindate,'YYYYMMDD')=pLeave_StartDate;

begin
pResult:='Success';
For TEMP_CUR_RECORD in TEMP_CUR loop
PKG_LEAVE.pro_Insert_Leave_Mapping(TEMP_CUR_RECORD.BILL_CODE,pResult);
end loop;

end pro_Supplement_Leave_Mapping;

END PKG_LEAVE;

阅读全文

0 0