hongtaiyang/runtime/server/properties/script_ztfl/6.0/sql/hr/hr_clean.sql

--EASHR账套分离脚本
--接口信息
--executeClearSpeciallyData (Connection   conn, String  org_ID, String period_ID, Date starttime, Date endtime ,int clearType)
--1.conn数据连接信息
--2.$ORGID删除财务组织的FID
--3.$FPERIOD_ID会计期间
--4.$starttime开始时间
--5.$endtime结束时间
--6.clearType用以标志帐套清理类型，1。按组织删除，2。按组织＋期间删除，3。按期间删除；

--
--遗留问题:
--1.调薪单如何按照日期?
--2.薪酬方案如何删除?动态表如何删除?
--3.薪点方案如何删除?动态表如何删除?

--删除费用分配单对应的botp关系分录
delete from T_BOT_RelationEntry where fkeyid in(
  select fid from T_BOT_Relation where FSrcObjectID in(
    select fid from t_hr_grant where FCmpPeriod IN (SELECT FID FROM T_HR_CMPPERIOD WHERE FFiscalPeriod IN ($FPERIOD_ID) AND FHROrgUnitID IN (
		  SELECT "ORGUNITRELATION".FFromUnitID FROM T_ORG_UnitRelation AS "ORGUNITRELATION"
		  INNER JOIN T_ORG_TypeRelation AS "TYPERELATION" ON "ORGUNITRELATION".FTypeRelationID = "TYPERELATION".FID
		  WHERE "TYPERELATION".FFromType = 16 AND "TYPERELATION".FToType = 1 and "ORGUNITRELATION".FToUnitID IN ($ORGID)
    )) 
    AND FHROrgUnitID in(
		  SELECT "ORGUNITRELATION".FFromUnitID FROM T_ORG_UnitRelation AS "ORGUNITRELATION"
		  INNER JOIN T_ORG_TypeRelation AS "TYPERELATION" ON "ORGUNITRELATION".FTypeRelationID = "TYPERELATION".FID
		  WHERE "TYPERELATION".FFromType = 16 AND "TYPERELATION".FToType = 1 and "ORGUNITRELATION".FToUnitID IN ($ORGID)
    )));
--删除费用分配单对应的botp关系
delete from T_BOT_Relation where FSrcObjectID in(
   select fid from t_hr_grant where FCmpPeriod IN (SELECT FID FROM T_HR_CMPPERIOD WHERE FFiscalPeriod IN ($FPERIOD_ID) AND FHROrgUnitID IN (
		  SELECT "ORGUNITRELATION".FFromUnitID FROM T_ORG_UnitRelation AS "ORGUNITRELATION"
		  INNER JOIN T_ORG_TypeRelation AS "TYPERELATION" ON "ORGUNITRELATION".FTypeRelationID = "TYPERELATION".FID
		  WHERE "TYPERELATION".FFromType = 16 AND "TYPERELATION".FToType = 1 and "ORGUNITRELATION".FToUnitID IN ($ORGID)
    )) 
   AND FHROrgUnitID in(
		  SELECT "ORGUNITRELATION".FFromUnitID FROM T_ORG_UnitRelation AS "ORGUNITRELATION"
		  INNER JOIN T_ORG_TypeRelation AS "TYPERELATION" ON "ORGUNITRELATION".FTypeRelationID = "TYPERELATION".FID
		  WHERE "TYPERELATION".FFromType = 16 AND "TYPERELATION".FToType = 1 and "ORGUNITRELATION".FToUnitID IN ($ORGID)
    )
);

--删除费用分配单分录
delete from  T_HR_GRANTENTRY where FGrantID in (
  select fid from t_hr_grant  where FCmpPeriod IN (SELECT FID FROM T_HR_CMPPERIOD WHERE FFiscalPeriod IN ($FPERIOD_ID) AND FHROrgUnitID IN (
		  SELECT "ORGUNITRELATION".FFromUnitID FROM T_ORG_UnitRelation AS "ORGUNITRELATION"
		  INNER JOIN T_ORG_TypeRelation AS "TYPERELATION" ON "ORGUNITRELATION".FTypeRelationID = "TYPERELATION".FID
		  WHERE "TYPERELATION".FFromType = 16 AND "TYPERELATION".FToType = 1 and "ORGUNITRELATION".FToUnitID IN ($ORGID)
    )) 
  AND FHROrgUnitID in(
	SELECT "FROMUNIT".FID 
	FROM T_ORG_UnitRelation AS "ORGUNITRELATION"
	INNER JOIN T_ORG_BaseUnit AS "FROMUNIT" ON "ORGUNITRELATION".FFromUnitID = "FROMUNIT".FID
	INNER JOIN T_ORG_TypeRelation AS "TYPERELATION" ON "ORGUNITRELATION".FTypeRelationID = "TYPERELATION".FID
	INNER JOIN T_ORG_BaseUnit AS "TOUNIT" ON "ORGUNITRELATION".FToUnitID = "TOUNIT".FID
	WHERE "TYPERELATION".FFromType = 16 AND "TYPERELATION".FToType = 1 and "TOUNIT".FID IN ($ORGID)
  )
);
--删除费用分配单
delete from t_hr_grant  where FCmpPeriod IN (SELECT FID FROM T_HR_CMPPERIOD WHERE FFiscalPeriod IN ($FPERIOD_ID) AND FHROrgUnitID IN (
		  SELECT "ORGUNITRELATION".FFromUnitID FROM T_ORG_UnitRelation AS "ORGUNITRELATION"
		  INNER JOIN T_ORG_TypeRelation AS "TYPERELATION" ON "ORGUNITRELATION".FTypeRelationID = "TYPERELATION".FID
		  WHERE "TYPERELATION".FFromType = 16 AND "TYPERELATION".FToType = 1 and "ORGUNITRELATION".FToUnitID IN ($ORGID)
    )) 
AND FHROrgUnitID in(
	SELECT "FROMUNIT".FID 
	FROM T_ORG_UnitRelation AS "ORGUNITRELATION"
	INNER JOIN T_ORG_BaseUnit AS "FROMUNIT"	ON "ORGUNITRELATION".FFromUnitID = "FROMUNIT".FID
	INNER JOIN T_ORG_TypeRelation AS "TYPERELATION" ON "ORGUNITRELATION".FTypeRelationID = "TYPERELATION".FID
	INNER JOIN T_ORG_BaseUnit AS "TOUNIT" ON "ORGUNITRELATION".FToUnitID = "TOUNIT".FID
	WHERE "TYPERELATION".FFromType = 16 AND "TYPERELATION".FToType = 1 and "TOUNIT".FID	IN ($ORGID)
);

--删除补扣补发所得税
delete from T_HR_FillFillTax where FCmpSchemeID in(
   select fid from T_HR_CMPSCHEME where FHROrgUnitID in(
	SELECT 
	
	"FROMUNIT".FID 
	
	FROM T_ORG_UnitRelation AS "ORGUNITRELATION"
	
	INNER JOIN T_ORG_BaseUnit AS "FROMUNIT"
	ON "ORGUNITRELATION".FFromUnitID = "FROMUNIT".FID
	
	INNER JOIN T_ORG_TypeRelation AS "TYPERELATION"
	ON "ORGUNITRELATION".FTypeRelationID = "TYPERELATION".FID
	
	INNER JOIN T_ORG_BaseUnit AS "TOUNIT"
	ON "ORGUNITRELATION".FToUnitID = "TOUNIT".FID
	
	WHERE "TYPERELATION".FFromType = 16 AND "TYPERELATION".FToType = 1 and "TOUNIT".FID
	IN ($ORGID) AND FCmpPeriod IN (SELECT FID FROM T_HR_CMPPERIOD WHERE FFiscalPeriod IN ($FPERIOD_ID) AND FHROrgUnitID IN (
		  SELECT "ORGUNITRELATION".FFromUnitID FROM T_ORG_UnitRelation AS "ORGUNITRELATION"
		  INNER JOIN T_ORG_TypeRelation AS "TYPERELATION" ON "ORGUNITRELATION".FTypeRelationID = "TYPERELATION".FID
		  WHERE "TYPERELATION".FFromType = 16 AND "TYPERELATION".FToType = 1 and "ORGUNITRELATION".FToUnitID IN ($ORGID)
    ))
   )
);

--删除补扣补发明细
delete from T_HR_FillFillDetail where FFillFillID in(
  select fid from T_HR_FillFill where FPeriodEndInputID in(
     select fid from T_HR_PeriodEndInput where FCmpPeriodID IN (SELECT FID FROM T_HR_CMPPERIOD WHERE FFiscalPeriod IN ($FPERIOD_ID) AND FHROrgUnitID IN (
		  SELECT "ORGUNITRELATION".FFromUnitID FROM T_ORG_UnitRelation AS "ORGUNITRELATION"
		  INNER JOIN T_ORG_TypeRelation AS "TYPERELATION" ON "ORGUNITRELATION".FTypeRelationID = "TYPERELATION".FID
		  WHERE "TYPERELATION".FFromType = 16 AND "TYPERELATION".FToType = 1 and "ORGUNITRELATION".FToUnitID IN ($ORGID)
    )) 
    AND FHROrgUnitID in(
	SELECT 
	
	"FROMUNIT".FID 
	
	FROM T_ORG_UnitRelation AS "ORGUNITRELATION"
	
	INNER JOIN T_ORG_BaseUnit AS "FROMUNIT"
	ON "ORGUNITRELATION".FFromUnitID = "FROMUNIT".FID
	
	INNER JOIN T_ORG_TypeRelation AS "TYPERELATION"
	ON "ORGUNITRELATION".FTypeRelationID = "TYPERELATION".FID
	
	INNER JOIN T_ORG_BaseUnit AS "TOUNIT"
	ON "ORGUNITRELATION".FToUnitID = "TOUNIT".FID
	
	WHERE "TYPERELATION".FFromType = 16 AND "TYPERELATION".FToType = 1 and "TOUNIT".FID
	IN ($ORGID)
     )
  )
);
--删除补扣补发
delete from T_HR_FillFill where FPeriodEndInputID in(
  select fid from T_HR_PeriodEndInput where FCmpPeriodID IN (SELECT FID FROM T_HR_CMPPERIOD WHERE FFiscalPeriod IN ($FPERIOD_ID) AND FHROrgUnitID IN (
		  SELECT "ORGUNITRELATION".FFromUnitID FROM T_ORG_UnitRelation AS "ORGUNITRELATION"
		  INNER JOIN T_ORG_TypeRelation AS "TYPERELATION" ON "ORGUNITRELATION".FTypeRelationID = "TYPERELATION".FID
		  WHERE "TYPERELATION".FFromType = 16 AND "TYPERELATION".FToType = 1 and "ORGUNITRELATION".FToUnitID IN ($ORGID)
    )) 
    AND FHROrgUnitID in(
	SELECT 
	
	"FROMUNIT".FID 
	
	FROM T_ORG_UnitRelation AS "ORGUNITRELATION"
	
	INNER JOIN T_ORG_BaseUnit AS "FROMUNIT"
	ON "ORGUNITRELATION".FFromUnitID = "FROMUNIT".FID
	
	INNER JOIN T_ORG_TypeRelation AS "TYPERELATION"
	ON "ORGUNITRELATION".FTypeRelationID = "TYPERELATION".FID
	
	INNER JOIN T_ORG_BaseUnit AS "TOUNIT"
	ON "ORGUNITRELATION".FToUnitID = "TOUNIT".FID
	
	WHERE "TYPERELATION".FFromType = 16 AND "TYPERELATION".FToType = 1 and "TOUNIT".FID
	IN ($ORGID)
  )
);



--删除期未输入
delete from T_HR_PeriodEndInput where FCmpPeriodID IN (SELECT FID FROM T_HR_CMPPERIOD WHERE FFiscalPeriod IN ($FPERIOD_ID) AND FHROrgUnitID IN (
		  SELECT "ORGUNITRELATION".FFromUnitID FROM T_ORG_UnitRelation AS "ORGUNITRELATION"
		  INNER JOIN T_ORG_TypeRelation AS "TYPERELATION" ON "ORGUNITRELATION".FTypeRelationID = "TYPERELATION".FID
		  WHERE "TYPERELATION".FFromType = 16 AND "TYPERELATION".FToType = 1 and "ORGUNITRELATION".FToUnitID IN ($ORGID)
    )) 
    AND FHROrgUnitID in(
        SELECT 
	
	"FROMUNIT".FID 
	
	FROM T_ORG_UnitRelation AS "ORGUNITRELATION"
	
	INNER JOIN T_ORG_BaseUnit AS "FROMUNIT"
	ON "ORGUNITRELATION".FFromUnitID = "FROMUNIT".FID
	
	INNER JOIN T_ORG_TypeRelation AS "TYPERELATION"
	ON "ORGUNITRELATION".FTypeRelationID = "TYPERELATION".FID
	
	INNER JOIN T_ORG_BaseUnit AS "TOUNIT"
	ON "ORGUNITRELATION".FToUnitID = "TOUNIT".FID
	
	WHERE "TYPERELATION".FFromType = 16 AND "TYPERELATION".FToType = 1 and "TOUNIT".FID
	IN ($ORGID)
);


--删除部门内分配方案明细
delete from T_HR_DeptInnerSchemeEntry where FDeptInnerSchemeID in(
  select fid from T_HR_DeptInnerScheme where fid in(
   select FDeptInnerSchemeID from T_HR_DeptSchemeEntry where FCmpPeriodID IN (SELECT FID FROM T_HR_CMPPERIOD WHERE FFiscalPeriod IN ($FPERIOD_ID) AND FHROrgUnitID IN (
		  SELECT "ORGUNITRELATION".FFromUnitID FROM T_ORG_UnitRelation AS "ORGUNITRELATION"
		  INNER JOIN T_ORG_TypeRelation AS "TYPERELATION" ON "ORGUNITRELATION".FTypeRelationID = "TYPERELATION".FID
		  WHERE "TYPERELATION".FFromType = 16 AND "TYPERELATION".FToType = 1 and "ORGUNITRELATION".FToUnitID IN ($ORGID)
    )) 
    AND FDeptSchemeID in(
       select fid from  T_HR_DeptScheme where FHROrgUnitID in(
	       SELECT 
		
		"FROMUNIT".FID 
		
		FROM T_ORG_UnitRelation AS "ORGUNITRELATION"
		
		INNER JOIN T_ORG_BaseUnit AS "FROMUNIT"
		ON "ORGUNITRELATION".FFromUnitID = "FROMUNIT".FID
		
		INNER JOIN T_ORG_TypeRelation AS "TYPERELATION"
		ON "ORGUNITRELATION".FTypeRelationID = "TYPERELATION".FID
		
		INNER JOIN T_ORG_BaseUnit AS "TOUNIT"
		ON "ORGUNITRELATION".FToUnitID = "TOUNIT".FID
		
		WHERE "TYPERELATION".FFromType = 16 AND "TYPERELATION".FToType = 1 and "TOUNIT".FID
		IN ($ORGID)
       )
   )
));
--删除部门内分配方案
delete from T_HR_DeptInnerScheme where fid in(
   select FDeptInnerSchemeID from T_HR_DeptSchemeEntry where FCmpPeriodID IN (SELECT FID FROM T_HR_CMPPERIOD WHERE FFiscalPeriod IN ($FPERIOD_ID) AND FHROrgUnitID IN (
		  SELECT "ORGUNITRELATION".FFromUnitID FROM T_ORG_UnitRelation AS "ORGUNITRELATION"
		  INNER JOIN T_ORG_TypeRelation AS "TYPERELATION" ON "ORGUNITRELATION".FTypeRelationID = "TYPERELATION".FID
		  WHERE "TYPERELATION".FFromType = 16 AND "TYPERELATION".FToType = 1 and "ORGUNITRELATION".FToUnitID IN ($ORGID)
    )) 
    AND FDeptSchemeID in(
       select fid from  T_HR_DeptScheme where FHROrgUnitID in(
	       SELECT 
		
		"FROMUNIT".FID 
		
		FROM T_ORG_UnitRelation AS "ORGUNITRELATION"
		
		INNER JOIN T_ORG_BaseUnit AS "FROMUNIT"
		ON "ORGUNITRELATION".FFromUnitID = "FROMUNIT".FID
		
		INNER JOIN T_ORG_TypeRelation AS "TYPERELATION"
		ON "ORGUNITRELATION".FTypeRelationID = "TYPERELATION".FID
		
		INNER JOIN T_ORG_BaseUnit AS "TOUNIT"
		ON "ORGUNITRELATION".FToUnitID = "TOUNIT".FID
		
		WHERE "TYPERELATION".FFromType = 16 AND "TYPERELATION".FToType = 1 and "TOUNIT".FID
		IN ($ORGID)
	       )
   )
);

--按财务组织删除费用分配单
delete from T_BOT_RelationEntry where fkeyid in(
   select fid from T_BOT_Relation where FSrcObjectID in(
     select fid from  t_hr_grant  where FCompanyID IN ($ORGID)
     AND FCmpPeriod IN (SELECT FID FROM T_HR_CMPPERIOD WHERE FFiscalPeriod IN ($FPERIOD_ID) AND FHROrgUnitID IN (
		  SELECT "ORGUNITRELATION".FFromUnitID FROM T_ORG_UnitRelation AS "ORGUNITRELATION"
		  INNER JOIN T_ORG_TypeRelation AS "TYPERELATION" ON "ORGUNITRELATION".FTypeRelationID = "TYPERELATION".FID
		  WHERE "TYPERELATION".FFromType = 16 AND "TYPERELATION".FToType = 1 and "ORGUNITRELATION".FToUnitID IN ($ORGID)
    ))
  ));

delete from T_BOT_Relation where FSrcObjectID in(
   select fid from  t_hr_grant where FCompanyID IN ($ORGID)
    AND FCmpPeriod IN (SELECT FID FROM T_HR_CMPPERIOD WHERE FFiscalPeriod IN ($FPERIOD_ID) AND FHROrgUnitID IN (
		  SELECT "ORGUNITRELATION".FFromUnitID FROM T_ORG_UnitRelation AS "ORGUNITRELATION"
		  INNER JOIN T_ORG_TypeRelation AS "TYPERELATION" ON "ORGUNITRELATION".FTypeRelationID = "TYPERELATION".FID
		  WHERE "TYPERELATION".FFromType = 16 AND "TYPERELATION".FToType = 1 and "ORGUNITRELATION".FToUnitID IN ($ORGID)
    ))
); 

delete from  T_HR_GRANTENTRY where FGrantID in (
   select fid from t_hr_grant  where FCompanyID IN ($ORGID)
    AND FCmpPeriod IN (SELECT FID FROM T_HR_CMPPERIOD WHERE FFiscalPeriod IN ($FPERIOD_ID) AND FHROrgUnitID IN (
		  SELECT "ORGUNITRELATION".FFromUnitID FROM T_ORG_UnitRelation AS "ORGUNITRELATION"
		  INNER JOIN T_ORG_TypeRelation AS "TYPERELATION" ON "ORGUNITRELATION".FTypeRelationID = "TYPERELATION".FID
		  WHERE "TYPERELATION".FFromType = 16 AND "TYPERELATION".FToType = 1 and "ORGUNITRELATION".FToUnitID IN ($ORGID)
    ))
);

delete from t_hr_grant where FCompanyID IN ($ORGID)
 AND FCmpPeriod IN (SELECT FID FROM T_HR_CMPPERIOD WHERE FFiscalPeriod IN ($FPERIOD_ID) AND FHROrgUnitID IN (
		  SELECT "ORGUNITRELATION".FFromUnitID FROM T_ORG_UnitRelation AS "ORGUNITRELATION"
		  INNER JOIN T_ORG_TypeRelation AS "TYPERELATION" ON "ORGUNITRELATION".FTypeRelationID = "TYPERELATION".FID
		  WHERE "TYPERELATION".FFromType = 16 AND "TYPERELATION".FToType = 1 and "ORGUNITRELATION".FToUnitID IN ($ORGID)
    ));