-- Start of DDL Script for Procedure SD_HOSPITAL.SP_SB_DAXX00_DYSBTQ -- Generated 3-一月-2018 19:22:04 from SD_HOSPITAL@FKHOS CREATE OR REPLACE PROCEDURE sp_sb_daxx00_dysbtq ( ad_TQNF00 in number, --折旧提取的4位的年份 ad_TQYF00 in number, --折旧提取的月份 ad_TQR000 in number, --折旧提取的操作员 ad_TQKS00 in number, ad_DAXXID in number --提取的设备 ) as ls_TQYZ00 SB_GDZCZJ.TQYZ00%TYPE; --提取原值 ls_TQYZJL SB_GDZCZJ.TQYZJL%TYPE; --提取的月折旧率(%),注意要除100 ls_BYZJE0 SB_GDZCZJ.BYZJE0%TYPE; --本月折旧 ls_LJZJ00 SB_DA0000.LJZJ00%TYPE; --原累计折旧 ls_YZCZZJ SB_GDZCZJ.YZCZZJ%TYPE; --提取原值 财政资金形成部分 ls_YZKJXM SB_GDZCZJ.YZKJXM%TYPE; --提取原值 科教项目资金形成部分 ls_YZQTZJ SB_GDZCZJ.YZQTZJ%TYPE; --提取原值 其他资金形成部分 ls_BYCZZJ SB_GDZCZJ.BYZJE0%TYPE; --本月折旧 财政资金形成部分 ls_BYKJXM SB_GDZCZJ.BYZJE0%TYPE; --本月折旧 科教项目资金形成部分 ls_BYQTZJ SB_GDZCZJ.BYZJE0%TYPE; --本月折旧 其他资金形成部分 ls_LJCZZJ SB_DA0000.LJCZZJ%TYPE; --原累计折旧 财政资金形成部分 (本科室) ls_LJKJXM SB_DA0000.LJKJXM%TYPE; --原累计折旧 科教项目资金形成部分 (本科室) ls_LJQTZJ SB_DA0000.LJQTZJ%TYPE; --原累计折旧 其他资金形成部分 (本科室) ls_LJZJ11 SB_DA0000.LJZJ00%TYPE; --原累计折旧(检验) 财政资金形成部分(本科室) ls_LJZJ12 SB_DA0000.LJZJ00%TYPE; --原累计折旧(检验) 科教项目资金形成部分 (本科室) ls_LJZJ13 SB_DA0000.LJZJ00%TYPE; --原累计折旧(检验) 其他资金形成部分 (本科室) ls_LJZJ21 SB_DA0000.LJZJ00%TYPE; --原累计折旧 财政资金形成部分(全部) ls_LJZJ22 SB_DA0000.LJZJ00%TYPE; --原累计折旧 科教项目资金形成部分 (全部) ls_LJZJ23 SB_DA0000.LJZJ00%TYPE; --原累计折旧 其他资金形成部分 (全部) ls_KSFTZJ SB_DA0000.LJZJ00%TYPE; --分摊科室折旧金额 ls_YKHJZ1 SB_GDZCZJ.ZJHJZ0%type; --调拨之前科室的净值 财政资金形成部分 ls_YKHJZ2 SB_GDZCZJ.ZJHJZ0%type; --调拨之前科室的净值 科教项目资金形成部分 ls_YKHJZ3 SB_GDZCZJ.ZJHJZ0%type; --调拨之前科室的净值 其他资金形成部分 ls_YKHJZ11 SB_GDZCZJ.ZJHJZ0%type; --上个月财政累计 ls_YKHJZ21 SB_GDZCZJ.ZJHJZ0%type; --上个月科教累计 ls_YKHJZ31 SB_GDZCZJ.ZJHJZ0%type; --上个月其他累计 ls_SYCZYZ SB_GDZCZJ.YZCZZJ%type; --上个月财政原值 ls_SYKJYZ SB_GDZCZJ.YZKJXM%type; --上个月科教原值 ls_SYQTYZ SB_GDZCZJ.YZQTZJ%type; --上个月其他原值 ls_YKHJZ11_1 SB_GDZCZJ.ZJHJZ0%type; --上个月财政累计 ls_YKHJZ21_1 SB_GDZCZJ.ZJHJZ0%type; --上个月科教累计 ls_YKHJZ31_1 SB_GDZCZJ.ZJHJZ0%type; --上个月其他累计 ls_ZJ0000 SB_DA0000.JZ0000%TYPE; --提取资产剩余净值 ls_LJZJ01 SB_DA0000.LJZJ00%TYPE; --原累计折旧(检验) (本科室) ls_LJZJ02 SB_DA0000.LJZJ00%TYPE; --原累计折旧(sb_da0000) (全部) ls_TQRQ00 char(8); --提取日期 ls_QYRQ00 SB_DA0000.QYRQ00%type; --设备启用日期 ls_SCTQNF SB_GDZCZJ.TQNF00%TYPE; --上次提取的年份 ls_SCTQYF SB_GDZCZJ.TQYF00%TYPE; --上次提取的月份 ls_i0000 number(10); ls_icount number(10); --总共需提取的月份总数 ls_SJTQNF SB_GDZCZJ.TQNF00%TYPE; --真正提取的年份 ls_SJTQYF SB_GDZCZJ.TQYF00%TYPE; --真正提取的月份 ls_TMPYF0 char(2); ls_cd0000 number(2); ls_tempic number; ls_djblz0 number(3,2); ls_LJECYS SB_DA0000.LJECYS%TYPE; --累计二次折旧月数 ls_LJECZJ SB_DA0000.LJECZJ%TYPE; --累计二次折旧金额 ls_YZ0000 SB_DA0000.YZ0000%TYPE; --二次折旧使用到的原值 ls_ECZJBL SB_GDZCZJ.ECZJBL%TYPE; --二次折旧比例值 ls_ECZJJE SB_GDZCZJ.BYZJE0%TYPE; --二次折旧金额 ls_SYBFB0 SB_SYKS00.SYBFB0%TYPE; --使用百分比 ls_TQKS00 SB_GDZCZJ.TQKS00%type; --使用科室 ls_DBYKS0 SB_DB0000.YKSBH0%type; --调拨原科室 ls_YKHJZ0 SB_GDZCZJ.ZJHJZ0%type; --调拨之前科室的净值； ls_kscount number(2); --科室数量 ls_BYDLKS SB_DB0000.YKSBH0%type; --本月调入科室 ls_LJZJJE SB_GDZCZJ.LJZJJE%type; --累计折旧金额 ls_XCWHS0 XT_XTCS00.VALUE0%type; --是否启用新财务核算 ls_JTZJ00 XT_XTCS00.VALUE0%type; --是否计提折旧 ls_WXZCBZ SB_DA0000.WXZCBZ%type; --无形资产标志 0不属于,1属于 ls_WZXCQC number(1); --无形资产首次折旧标志 0不属于,1属于 --ls_GDZCQC number(1); --固定资产首次折旧标志 0不属于,1属于 ls_Count0 number(2); V_params varchar2(255); --参数变量 V_errmsg varchar2(255); --错误提示变量 E_custom exception; --错误变量 ls_SYZT00 SB_DA0000.SYZT00%type; ls_SHJSZJ SB_DA0000.SHJSZJ%type; ls_SYNX00 SB_DA0000.SYNX00%type; ls_LJZJFT SB_DA0000.LJZJ00%type; --累计折旧金额 ls_LJZJCZ SB_DA0000.LJZJ00%type; --累计折旧财政 ls_LJZJKJ SB_DA0000.LJZJ00%type; --累计折旧科教 ls_LJZJQT SB_DA0000.LJZJ00%type; --累计折旧其他 ls_CKBH00 SB_DA0000.CKBH00%type; --设备仓库编号 ld_DBID00 SB_DB0000.DBID00%TYPE; --设备调拨ID RT_SB_SYKS00 SB_SYKS00%ROWTYPE; ls_MAXKSRQKSSJ varchar2(20); --ls_ZCQYRQ SB_DA0000.CKBH00%type; ls_ZCQYRQ SB_DA0000.QYRQ00%type; cursor qyks_cur is select BMBH00 from SB_SYKS00 where DAXXID=ad_daxxid and KSRQ00||KSSJ00=ls_MAXKSRQKSSJ union all --要求入帐未开始使用的资产，第二个月开始也要计提折旧 select CKBH00 from Sb_DA0000 where DAXXID=ad_daxxid and SYZT00='1' and ls_SYZT00=1 and RZRQ00 is not null ; cursor tqks_cur is select BMBH00 from SB_SYKS00 where DAXXID=ad_daxxid and SYZT00='Y' union all --要求入帐未开始使用的资产，第二个月开始也要计提折旧 select CKBH00 from Sb_DA0000 where DAXXID=ad_daxxid and SYZT00='1' and ls_SYZT00=1 and RZRQ00 is not null ; -- MODIFICATION HISTORY -- Person Date Comments -- JETHUA 2006.01.19 取上次折旧额时，要处理设备首次折旧未有上次折旧额 -- qks 2008.02.19 条件ls_TQYZ00=ls_LJZJ00改为ls_TQYZ00<=ls_LJZJ00 -- qks 2008.03.14 修改：在用资产调拨或者直接修改使用科室后，折旧金额会从这个资产的启用日期重新算 -- qks 2008.06.18 修改档案表的信息,LJZJ00 =LJZJ00 + ls_BYZJE0改为LJZJ00 =nvl(LJZJ00,0) + ls_BYZJE0 -- qks 2008.11.15 新增参数SB_JXZJDJBLZ(继续折旧递减比例值) -- qks 2008.12.15 新增字段ls_SYBFB0以及修改二次折旧写入SB_GDZCZJ值 -- chenyw 2009.03.05 增加检验累计折旧字段ls_LJZJ01,累计折旧去sb_da0000中的LJZJ00,修改了sb_da0000 中的LJZJ00和YZ0000的计算。 -- chenyw 2009.03.11 TQYF00<=ls_TMPYF0来处理共用时产生的一些问题。 -- chenyw 2009.07.03 对折旧过程进行调整 -- chenyw 2009.07.08 对调拨之后的总净值继续计算。 -- chenyw 2009.09.25 限制有效的使用科室进行折旧； -- linzy 2010.12.13 资产多次调拨后取第一次调出科室。 -- linzy 2011.02.11 提取最后一次折旧金额及日期 -- linzy 2011.03.09 增加对提取最后一次折旧金额及日期null处理 -- linzy 2011.04.13 修正当资产被大多科室的使用导致各科室本月折旧为零 -- linzy 2011.05.04 修正调拨资产累计折旧错误 -- linzy 2011.06.08 修正当设备第一次使用时进行多次调拨造成多次折旧的问题 -- qks 2011.10.17 表SB_GDZCZJ增加YZCZZJ,YZKJXM,YZQTZJ,BYCZZJ,BYKJXM,BYQTZJ,BTCZZJ,BTKJXM,BTQTZJ,LJCZZJ,LJKJXM,LJQTZJ; --表SB_DA0000增加LJCZZJ、LJKJXM、LJQTZJ; --固定资产当月购入下月提折旧,报废时当月照提折旧下月不提;无形资产当月购入当月摊销,报废时当月不摊销。 -- qks 2012.02.02 肺科医院：要求入帐未开始使用的资产，第二个月开始也要计提折旧。GDZC-20120213-001 -- qks 2012.02.16 增加新参数GDZC_WQYSFJTZJ控制：入帐未开始使用的资产，第二个月开始是否进行计提折旧。GDZC-20120216-001 -- qks 2012.02.23 资产首次折旧写入折旧表SB_GDZCZJ时，JZCZZJ,JZKJXM,JZQTZJ字段值也需要填写; 判断累计折旧额ls_LJZJ02改为从SB_DA0000.LJZJ00取值。by GDZC-20120224-001 -- qks 2012.03.02 资产计提折旧之前验证SB_SYKS00.FTJE00=FTCZZJ+FTKJXM+FTQTZJ,SB_DA0000.LJZJ00=LJCZZJ+LJKJXM+LJQTZJ; --写入SB_GDZCZJ.LJZJ00改为=SB_DA0000.LJZJ00*SB_SYKS00.SYBFB0/100+本月折旧金额。 by GDZC-20120302-001 -- qks 2012.03.08 肺科医院：当参数为GDZC_WQYSFJTZJ='Y'时,已开始使用,未进行计提折旧的资产,计算折旧也要从入帐日期开始。by GDZC-20120308-001 -- qks 2012.03.14 修改ls_ZCQYRQ定义错误。by GDZC-20120308-001 -- linzy 2013.07.11 修正当资产被大多科室使用所分摊的科室原值小于资产折旧额外导致各科室本月折旧为零。by GDZC-20130711-001 -- linzy 2013.11.13 修正资产折旧识误差值及设备使用一段时间后资产原值进行重新分配后净值分配出错的问题 by GDZC-20131113-002 -- linzy 2013.12.11 修正调拨后折旧累计折旧金额为0 by GDZC-20131205-001 -- linzy 2014.01.10 修正启用新财政参数在库资产进行折旧时判断SB_SYKS00导致错误 by GDZC-20140102-001 -- linzy 2014.01.22 修正无形资产折旧较早入账后会重复入账当月的折旧记录。 BY GDZC-20140110-001 -- linzy 2014.05.26 1.修正新建档在库资产入库折旧提示null无法写入，取剩余净值SB_DA0000的LJQTZJ为NULL -- 2.折旧过程加入折旧表与档案表折旧金额与净值的校验。 GDZC-20140526-001 -- linzy 2014.06.05 修正当净值为空时默认由0改为资产原值,避免折旧报错。 by GDZC-20140604-001 -- linzy 2014.07.24 修正如果调拨过的设备调拨回原科室，设备折旧会发生错误当月折旧会从上次调拨日期往后计算，导致折旧报错。by GDZC-20140711-001 -- linzy 2014.09.02 修正调拨判断，如6月份调拨已折旧 GDZC-20140901-001 -- linzy 2015.01.04 修正设备资产启用后未进行折旧，在后面的月份中进行更改使用科室，会导致设备折旧出错 by GDZC-20150104-001 -- linzy 2015.07.13 归档 by GDZC-20150112-001 -- --------- ---------- ------------------------------------------------------------------------------------------------------------------------------- begin V_params:='调用过程: SP_SB_DAXX00_DYSBTQ('||to_char(ad_TQNF00)||','||to_char(ad_TQYF00)||','||to_char(ad_TQKS00)||','||to_char(ad_DAXXID)||');'; begin select to_number(VALUE0) into ls_djblz0 from XT_XTCS00 where name00='SB_JXZJDJBLZ';--继续折旧递减比例值 exception when others then ls_djblz0:=0; end; select nvl(max(value0),'N') into ls_XCWHS0 from XT_XTCS00 where NAME00='GDZC_SFQYXCWHS'; select nvl(max(value0),'N') into ls_JTZJ00 from XT_XTCS00 where NAME00='GDZC_WQYSFJTZJ'; select count(*) into ls_tempic from SB_GDZCZJ where DAXXID=ad_daxxid and TQKS00=ad_TQKS00 ; --为零 有三中情况 调拨 直接修改科室，或者半路分科室，好几个月没有折旧了 select nvl(YZJL00,0),nvl(WXZCBZ,'0'),SYZT00,SHJSZJ,nvl(SYNX00,0),nvl(LJZJ00,0),nvl(LJCZZJ,0),nvl(LJKJXM,0),nvl(LJQTZJ,0),nvl(CKBH00,0),nvl(QYRQ00,'20010101') into ls_TQYZJL,ls_WXZCBZ,ls_SYZT00,ls_SHJSZJ,ls_SYNX00,ls_LJZJFT,ls_LJZJCZ,ls_LJZJKJ,ls_LJZJQT,ls_CKBH00,ls_ZCQYRQ from SB_DA0000 where DAXXID=ad_daxxid; --这里可能有点问题，如果月折旧率有改动。 if (ls_SHJSZJ<>'1') or (ls_SHJSZJ='1' and ls_SYZT00=1 and ls_JTZJ00='N') then return; end if; ls_WZXCQC := 0; --ls_GDZCQC := 0; if ls_XCWHS0<>'W' then ls_WXZCBZ:='0'; end if; if ls_XCWHS0='N' then ls_SYZT00:=0; end if; if ls_SYNX00=0 then V_errmsg := '资产DAXXID= '||to_char(ad_daxxid)||'使用年限为0，请先修改再计提折旧！'; raise E_custom; end if; if ls_TQYZJL=0 then ls_TQYZJL:=SP_SB_GetYZJL00(ad_daxxid); update sb_da0000 set YZJL00=ls_TQYZJL where DAXXID=ad_daxxid; end if; --找出最后一次的提取时间。 --下面是改进的提取过程 if ls_tempic=0 then --该设备刚起用时，提取折旧等于零。 begin --修改在用资产的使用科室或者调拨，折旧应该在原来已折旧基础上延续。 select count(1) into ls_icount from SB_GDZCZJ where DAXXID=ad_daxxid; if ls_icount>0 then select count(*) into ls_i0000 from SB_SYKS00 where DAXXID=ad_daxxid and syzt00='Y'; if ls_i0000 = 1 then select max(TQNF00) into ls_SCTQNF from SB_GDZCZJ where DAXXID=ad_daxxid; select max(TQYF00) into ls_SCTQYF from SB_GDZCZJ where DAXXID=ad_daxxid and TQNF00=ls_SCTQNF; else --多个科室使用 select min (rq0000) into ls_QYRQ00 from (select max(tqnf00||LPAD(tqyf00,2,'0')) rq0000 From SB_GDZCZJ where daxxid=ad_daxxid group by TQKS00); ls_SCTQNF:=substr(ls_QYRQ00,1,4); ls_SCTQYF:=substr(ls_QYRQ00,5,2); -- if ls_WXZCBZ = '1' then -- ls_WZXCQC := 1; -- end if; -- ls_GDZCQC := 1; end if; else --未使用资产 --if ls_SYZT00 = 1 then if ls_JTZJ00 = 'Y' then --modified by 2012.03.08 select nvl(RZRQ00,to_char(sysdate,'YYYYMMDD')) into ls_QYRQ00 from SB_DA0000 where DAXXID=ad_daxxid; else select nvl(QYRQ00,to_char(sysdate,'YYYYMMDD')) into ls_QYRQ00 from SB_DA0000 where DAXXID=ad_daxxid; end if; ls_SCTQNF:=substr(ls_QYRQ00,1,4); ls_SCTQYF:=substr(ls_QYRQ00,5,2); select max(KSRQ00||KSSJ00) into ls_MAXKSRQKSSJ from SB_SYKS00 where DAXXID=ad_daxxid and KSRQ00>=ls_SCTQNF||lpad(ls_SCTQYF,2,'0')||'01' and KSRQ00<=ls_SCTQNF||lpad(ls_SCTQYF,2,'0')||'31'; ls_i0000 := 1; open qyks_cur; fetch qyks_cur into ls_TQKS00; while qyks_cur%found loop if (ad_TQNF00>ls_SCTQNF) or ((ad_TQNF00=ls_SCTQNF) and (ad_TQYF00>ls_SCTQYF)) then --提取的时间大于起用时间 --当月购入当月摊销,报废时当月不摊销 if ls_WXZCBZ = '1' then ls_WZXCQC := 1; else --if ls_SYZT00 = 1 then if ls_JTZJ00 = 'Y' then if substrb(ls_ZCQYRQ,1,6)<>substrb(ls_QYRQ00,1,6) then ls_TQKS00 := ls_CKBH00; end if; insert into SB_GDZCZJ(DAXXID,DAH000,TQNF00,TQYF00,TQKS00,TQYZ00,TQYZJL,BYZJE0, BYBTJE,TQRQ00,TQSJ00,TQR000,TQBZ00,ZJHJZ0,LJZJJE, YZCZZJ,YZKJXM,YZQTZJ,BYCZZJ,BYKJXM,BYQTZJ, BTCZZJ,BTKJXM,BTQTZJ, JZCZZJ,JZKJXM,JZQTZJ, LJCZZJ,LJKJXM,LJQTZJ ) select A.DAXXID,A.DAH000,ls_SCTQNF,ls_SCTQYF,ls_TQKS00,A.YZ0000,ls_TQYZJL,0, 0,to_char(sysdate,'YYYYMMDD'),to_char(sysdate,'HH24:MI:SS'),ad_TQR000,'1', nvl(A.JZ0000,A.YZ0000),NVL(A.LJZJ00,0), NVL(A.XBJE00,0),NVL(A.QTJFJE,0),NVL(A.ZCJE00,0),0,0,0, 0,0,0, NVL(A.XBJE00,0)-NVL(A.LJCZZJ,0),NVL(A.QTJFJE,0)-NVL(A.LJKJXM,0),NVL(A.ZCJE00,0)-NVL(A.LJQTZJ,0), NVL(A.LJCZZJ,0),NVL(A.LJKJXM,0),NVL(A.LJQTZJ,0) from SB_DA0000 A where A.DAXXID=ad_daxxid and ls_i0000=1; ls_i0000:=0; else insert into SB_GDZCZJ(DAXXID,DAH000,TQNF00,TQYF00,TQKS00,TQYZ00,TQYZJL,BYZJE0, BYBTJE,TQRQ00,TQSJ00,TQR000,TQBZ00,ZJHJZ0,LJZJJE, YZCZZJ,YZKJXM,YZQTZJ,BYCZZJ,BYKJXM,BYQTZJ, BTCZZJ,BTKJXM,BTQTZJ, JZCZZJ,JZKJXM,JZQTZJ, LJCZZJ,LJKJXM,LJQTZJ ) select A.DAXXID,A.DAH000,ls_SCTQNF,ls_SCTQYF,ls_TQKS00,B.FTJE00,ls_TQYZJL,0, 0,to_char(sysdate,'YYYYMMDD'),to_char(sysdate,'HH24:MI:SS'),ad_TQR000,'1', (B.FTJE00-nvl(A.LJZJ00,0)*B.SYBFB0/100),NVL(A.LJZJ00,0)*B.SYBFB0/100, NVL(A.XBJE00,0)*B.SYBFB0/100,NVL(A.QTJFJE,0)*B.SYBFB0/100,NVL(A.ZCJE00,0)*B.SYBFB0/100,0,0,0, 0,0,0, (NVL(A.XBJE00,0)-NVL(A.LJCZZJ,0))*B.SYBFB0/100,(NVL(A.QTJFJE,0)-NVL(A.LJKJXM,0))*B.SYBFB0/100,(NVL(A.ZCJE00,0)-NVL(A.LJQTZJ,0))*B.SYBFB0/100, NVL(A.LJCZZJ,0)*B.SYBFB0/100,NVL(A.LJKJXM,0)*B.SYBFB0/100,NVL(A.LJQTZJ,0)*B.SYBFB0/100 from sb_syks00 B,SB_DA0000 A where B.DAXXID=ad_daxxid and B.BMBH00=ls_TQKS00 and A.DAXXID=B.DAXXID and B.KSRQ00||B.KSSJ00=ls_MAXKSRQKSSJ; --and B.syzt00='Y'; end if; end if; --修正净值 update sb_da0000 set jz0000=nvl(jz0000,yz0000) where DAXXID=ad_daxxid; end if; fetch qyks_cur into ls_TQKS00; end loop; close qyks_cur; end if; end; else begin --加科室过滤 避免有多个科室在用时 第一个科室折旧后 其他科室无法折旧 linzy select max(TQNF00) into ls_SCTQNF from SB_GDZCZJ where DAXXID=ad_daxxid and TQKS00=ad_TQKS00 ; select max(TQYF00) into ls_SCTQYF from SB_GDZCZJ where DAXXID=ad_daxxid and TQKS00=ad_TQKS00 and TQNF00=ls_SCTQNF; end; end if; --查找设备在该科室的分摊金额 XBJE00 财政 QTJFJE 科教 ZCJE00 其他 if ls_SYZT00 = 1 then select YZ0000,100,nvl(XBJE00,0),nvl(QTJFJE,0),nvl(ZCJE00,0) into ls_TQYZ00,ls_SYBFB0,ls_YZCZZJ,ls_YZKJXM,ls_YZQTZJ from SB_DA0000 where DAXXID=ad_daxxid; else select FTJE00,SYBFB0,nvl(FTCZZJ,0),nvl(FTKJXM,0),nvl(FTQTZJ,0) into ls_TQYZ00,ls_SYBFB0,ls_YZCZZJ,ls_YZKJXM,ls_YZQTZJ from SB_SYKS00 where DAXXID=ad_daxxid and BMBH00=ad_TQKS00 and syzt00='Y'; end if; --取上次的累计折旧额 添加修改使用科室->调拨->折旧,导致记录未找到的处理 linzy /* --modified by qks 2012.03.02 begin select LJZJJE,LJCZZJ,LJKJXM,LJQTZJ into ls_LJZJ00,ls_LJCZZJ,ls_LJKJXM,ls_LJQTZJ from SB_GDZCZJ where DAXXID=ad_daxxid and TQKS00=ad_TQKS00 AND TQNF00=ls_SCTQNF and TQYF00=ls_SCTQYF;--科室累计折旧 exception when others then ls_LJZJ00:=0; ls_LJCZZJ:=0; ls_LJKJXM:=0; ls_LJQTZJ:=0; end; */ --add by qks 2012.03.02 --linzy 算法存在问题 当多个科室使用时 ls_LJZJFT折旧额包含上第一个科室的折旧额 导致下个科室折旧分摊多出来 在下面代码重新取值 ls_LJZJ00:=ls_LJZJFT*ls_SYBFB0/100; ls_LJCZZJ:=ls_LJZJCZ*ls_SYBFB0/100; ls_LJKJXM:=ls_LJZJKJ*ls_SYBFB0/100; ls_LJQTZJ:=ls_LJZJQT*ls_SYBFB0/100; if ls_XCWHS0<>'N' then if ls_TQYZ00<>ls_YZCZZJ+ls_YZKJXM+ls_YZQTZJ then V_errmsg := '资产使用科室表或设备档案表DAXXID= '||to_char(ad_daxxid)||'资产原值'||to_char(ls_TQYZ00)||'不等于其他三个合计'||to_char(ls_YZCZZJ+ls_YZKJXM+ls_YZQTZJ); raise E_custom; end if; if ls_LJZJ00<>ls_LJCZZJ+ls_LJKJXM+ls_LJQTZJ then V_errmsg := '设备档案表DAXXID= '||to_char(ad_daxxid)||'累计折旧总额'||to_char(ls_LJZJ00)||'不等于其他三个合计'||to_char(ls_LJCZZJ+ls_LJKJXM+ls_LJQTZJ); raise E_custom; end if; end if; --判断在本次提取时间之前是存在调拨 如果是调拨后未做计提后再做调拨？ select nvl(min(DBID00),0) into ld_DBID00 from SB_DB0000 where DBSPRQ>=decode(ad_tqyf00,1,to_char(ad_TQNF00)-1,to_char(ad_TQNF00))||lpad(decode(ad_TQYF00,1,13,ad_TQYF00)-1,2,'0')||'01' --wzy前方to_char(ad_TQNF00)改为decode(ad_tqyf00,1,to_char(ad_TQNF00)-1,to_char(ad_TQNF00)) and DBSPRQ<=to_char(ad_TQNF00)||lpad(decode(ad_TQYF00,1,13,ad_TQYF00)-1,2,'0')||'31' and DAXXID=ad_daxxid; --如果调拨后已有折旧则不做处理 避免重复折旧 select count(*) into ls_count0 from SB_GDZCZJ where DAXXID=ad_daxxid and TQNF00=ad_TQNF00 and TQYF00>=decode(ad_TQYF00,1,13,ad_TQYF00)-1; --wzy改-2为-1 if ld_DBID00>0 and ls_count0<=0 then select YKSBH0 into ls_DBYKS0 from SB_DB0000 where DBID00=ld_DBID00; --取调拨之前科室最后折旧的月份 设备有可能半年调拨之前 有可能造成从半年再开始计算折旧 select nvl(max(TQNF00),ls_SCTQNF),nvl(max(TQYF00),ls_SCTQYF) into ls_SCTQNF,ls_SCTQYF from ( select * from SB_GDZCZJ where DAXXID=ad_daxxid --and TQKS00=ls_DBYKS0 order by TQNF00||lpad(TQYF00,2,'0') desc) where rownum=1; end if; ls_icount:=(ad_TQNF00-ls_SCTQNF)*12+ad_TQYF00-ls_SCTQYF;--算出需要提取的月份 begin ls_i0000:=1-ls_WZXCQC; while ls_i000012 then begin ls_SJTQNF:=ls_SCTQNF+1; ls_SJTQYF:=LS_SCTQYF-12; ls_SCTQNF:=ls_SCTQNF+1; ls_SCTQYF:=LS_SCTQYF-12; end; else begin ls_SJTQNF:=ls_SCTQNF; ls_SJTQYF:=LS_SCTQYF; end; end if; ls_TMPYF0:=to_char(LS_SJTQYF); ls_cd0000:=length(rtrim(ls_TMPYF0)); if ls_cd0000 = 1 then ls_TMPYF0:='0'||rtrim(ls_TMPYF0); end if; --折旧后不算首次折旧否则当月一直重复折旧 BY linzy 2013.01.16 ls_WZXCQC:=0; --修正当资产被大多科室的使用导致各科室本月折旧为零 --如上月该科室有进行折旧 -- linzy 增加ls_KSFTZJ 避免了大资产多个科室使用分摊比例较小时 出现提取原值小于资产的所有折旧 导致折旧金额为0 累计折旧取自对应科室折旧额 LS_SCTQYF-1 减1 否则出现无法找到上月折旧记录 select count(*) into ls_Count0 from SB_GDZCZJ where DAXXID=AD_DAXXID and TQKS00=ad_TQKS00 and TQNF00=decode(LS_SCTQYF-1,0,ls_SCTQNF-1,ls_SCTQNF) and TQYF00=decode(LS_SCTQYF-1,0,12,LS_SCTQYF-1); if ls_Count0>0 then select nvl(sum(LJZJJE),0),nvl(sum(LJCZZJ),0),nvl(sum(LJKJXM),0),nvl(sum(LJQTZJ),0),nvl(sum(JZCZZJ),0),nvl(sum(JZKJXM),0),nvl(sum(JZQTZJ),0) into ls_LJZJ01,ls_LJZJ11,ls_LJZJ12,ls_LJZJ13,ls_YKHJZ1,ls_YKHJZ2,ls_YKHJZ3 from SB_GDZCZJ where DAXXID=AD_DAXXID and TQKS00=ad_TQKS00 and TQNF00=decode(LS_SCTQYF-1,0,ls_SCTQNF-1,ls_SCTQNF) and TQYF00=decode(LS_SCTQYF-1,0,12,LS_SCTQYF-1); select nvl(ljzj00,0),nvl(LJCZZJ,0),nvl(LJKJXM,0),nvl(LJQTZJ,0) into ls_LJZJ02,ls_LJZJ21,ls_LJZJ22,ls_LJZJ23 from sb_da0000 where daxxid=ad_daxxid; ls_KSFTZJ:= ls_LJZJ01; ls_LJZJ00:= ls_LJZJ01; ls_LJCZZJ:= ls_LJZJ11; ls_LJKJXM:= ls_LJZJ12; ls_LJQTZJ:= ls_LJZJ13; else --科室调拨或第一次折旧 select nvl(ljzj00,0),nvl(LJCZZJ,0),nvl(LJKJXM,0),nvl(LJQTZJ,0) into ls_LJZJ01,ls_LJZJ11,ls_LJZJ12,ls_LJZJ13 from sb_da0000 where daxxid=ad_daxxid; ls_LJZJ02:=ls_LJZJ01; ls_LJZJ21:=ls_LJZJ11; ls_LJZJ22:=ls_LJZJ12; ls_LJZJ23:=ls_LJZJ13; ls_KSFTZJ:=ls_LJZJ01; ls_LJZJ00:=ls_LJZJ01; ls_LJCZZJ:=ls_LJZJ11; ls_LJKJXM:=ls_LJZJ12; ls_LJQTZJ:=ls_LJZJ13; ls_YKHJZ1:= 0; ls_YKHJZ2:= 0; ls_YKHJZ3:= 0; end if; ls_BYZJE0:= ls_TQYZ00*ls_TQYZJL/100; --本月折旧额 ls_BYCZZJ:= ls_YZCZZJ*ls_TQYZJL/100; --本月折旧额 财政资金形成部分 ls_BYKJXM:= ls_YZKJXM*ls_TQYZJL/100; --本月折旧额 科教项目资金形成部分 ls_BYQTZJ:= ls_YZQTZJ*ls_TQYZJL/100; --本月折旧额 其他资金形成部分 --若本月的折旧额+原累计折旧超过资产的原值,则本月折旧额不能按公式计算,只能是原值-累计折旧 if ((ls_TQYZ00-ls_LJZJ01-ls_BYZJE0-ls_BYZJE0) < 0) and ((ls_TQYZ00-ls_LJZJ01)>0) and (ls_YKHJZ1-ls_BYCZZJ<=0 or ls_YKHJZ2-ls_BYKJXM<=0 or ls_YKHJZ3-ls_BYQTZJ<=0) then --wzy 改前面(ls_TQYZ00-ls_LJZJ01-ls_BYZJE0) < 0为(ls_TQYZ00-ls_LJZJ01-ls_BYZJE0-ls_BYZJE0) < 0完善规则不出bug 并加or后数据 判断当月资金组成部分折旧完且再下个月全部折旧完毕按次方法 ls_BYZJE0:=ls_TQYZ00 - ls_LJZJ01; --（这里的ls_ljzj00取档案表中的） ls_BYCZZJ:=ls_YZCZZJ - ls_LJZJ11; ls_BYKJXM:=ls_YZKJXM - ls_LJZJ12; ls_BYQTZJ:=ls_YZQTZJ - ls_LJZJ13; --可能是其他科室折旧完毕,增加全部科室折旧合计金额判断 modified by qks 2011.10.17 elsif ((ls_TQYZ00-ls_LJZJ02-ls_BYZJE0) < 0) and ((ls_TQYZ00-ls_LJZJ02)>0) then ls_BYZJE0:=ls_TQYZ00 - ls_LJZJ02; ls_BYCZZJ:=ls_YZCZZJ - ls_LJZJ21; ls_BYKJXM:=ls_YZKJXM - ls_LJZJ22; ls_BYQTZJ:=ls_YZQTZJ - ls_LJZJ23; end if; --增加全部科室折旧判断 or (ls_TQYZ00<=ls_LJZJ02) modified by qks 2011.10.17 --调拨就出现问题了，解决方案（ls_ljzj00取档案表） -- linzy ls_LJZJ02 改为 ls_KSFTZJ 当存在多个科室使用的时候 取科室累计折旧额 存在科室原值小于资产的累计折旧额导致科室无法折旧。 if (ls_TQYZ00<=ls_LJZJ01) or (ls_TQYZ00<=ls_KSFTZJ) then ls_BYZJE0:=0; ls_BYCZZJ:=0; ls_BYKJXM:=0; ls_BYQTZJ:=0; end if; --资产在乘以月折旧率四舍五入存在误差值 linzy 2013.11.05 if ls_BYZJE0<>ls_BYCZZJ+ls_BYKJXM+ls_BYQTZJ then if ls_YZQTZJ<>0 then ls_BYQTZJ:=ls_BYZJE0-ls_BYCZZJ-ls_BYKJXM; elsif ls_BYKJXM<>0 then ls_BYKJXM:=ls_BYZJE0-ls_BYCZZJ-ls_BYQTZJ; elsif ls_YZCZZJ<>0 then ls_YZCZZJ:=ls_BYZJE0-ls_BYKJXM-ls_BYQTZJ; end if; end if; if ls_BYZJE0<>ls_BYCZZJ+ls_BYKJXM+ls_BYQTZJ and ls_XCWHS0<>'N' then --启用新财政核算 V_errmsg:='资产累计折旧存在误差！误差值：'||to_char(ls_BYZJE0-(ls_BYCZZJ+ls_BYKJXM+ls_BYQTZJ)); V_params:='提取年份：'||ls_SJTQNF||'提取月份：'||ls_SJTQYF ||'DAXXID:'||to_char(ad_daxxid); raise E_custom; end if; ls_ECZJBL:=0; if (ls_djblz0>0) and (ls_BYZJE0=0) then --注意比例每年(按12个月计算)递减,同时要考虑多个科室共用 select nvl(LJECZJ,0),nvl(LJECYS,0),YZ0000 into ls_LJECZJ,ls_LJECYS,ls_YZ0000 from sb_da0000 where daxxid=ad_daxxid; if ls_YZ0000>ls_LJECZJ then select count(1) into ls_tempic from SB_GDZCZJ where DAXXID=ad_daxxid and TQNF00=ls_SJTQNF and TQYF00=ls_SJTQYF; if ls_tempic=0 then --不存在其他科室本月已折旧 ls_LJECYS:=ls_LJECYS+1; end if; select 1-(floor(ls_LJECYS/12)+1)*ls_djblz0 into ls_ECZJBL from dual; if ls_ECZJBL>0 then --二次折旧比例值>0 ls_ECZJJE:=round(ls_TQYZ00*ls_TQYZJL*ls_ECZJBL/100,2); update sb_da0000 set LJECYS=ls_LJECYS,LJECZJ=nvl(LJECZJ,0)+ls_ECZJJE where DAXXID=ad_daxxid; end if; end if; end if; ls_LJZJ00:=ls_LJZJ00+ls_BYZJE0; --本月累计折旧额 ls_LJCZZJ:=ls_LJCZZJ+ls_BYCZZJ; ls_LJKJXM:=ls_LJKJXM+ls_BYKJXM; ls_LJQTZJ:=ls_LJQTZJ+ls_BYQTZJ; --2014.01.03 增加判断在库资产折旧 linzy 改正重新分配资产组成部分时出现错误 select count(1) into ls_Count0 from SB_SYKS00 where DAXXID=ad_daxxid and BMBH00=ad_TQKS00 and SYZT00='Y'; ls_YKHJZ1:=0; ls_YKHJZ2:=0; ls_YKHJZ3:=0; -- linzy 启用新财政 数据核验 if ls_Count0>0 and ls_XCWHS0<>'N' then --提取科室使用情况及资产剩余净值 2013.11.11 linzy select * into RT_SB_SYKS00 from SB_SYKS00 where DAXXID=ad_daxxid and BMBH00=ad_TQKS00 and SYZT00='Y'; select JZ0000 into ls_ZJ0000 from SB_DA0000 where DAXXID=ad_daxxid; select nvl(max(LJCZZJ),0),nvl(max(LJKJXM),0),nvl(max(LJQTZJ),0),nvl(max(YZCZZJ),0),nvl(max(YZKJXM),0),nvl(max(YZQTZJ),0) into ls_YKHJZ1,ls_YKHJZ2,ls_YKHJZ3,ls_SYCZYZ,ls_SYKJYZ,ls_SYQTYZ from ( select * from SB_GDZCZJ where DAXXID=ad_daxxid order by TQNF00 desc,TQYF00 desc,LJZJJE desc ) where rownum=1; --计算各资产组成部分的净取 linzy (资产组成部分) - (最后一次各组成部分的折旧金额) = 剩余未折旧的净值 --从未进行折旧 if nvl(RT_SB_SYKS00.FTCZZJ,0)=0 and ls_YKHJZ1=0 then ls_YKHJZ11:=0; --资产重新合并 累计折旧金额不为0 以负数进行抵消 elsif nvl(RT_SB_SYKS00.FTCZZJ,0)=0 and ls_YKHJZ1>0 then ls_YKHJZ11:=-ls_YKHJZ1; elsif nvl(RT_SB_SYKS00.FTCZZJ,0)>0 then ls_YKHJZ11:=nvl(RT_SB_SYKS00.FTCZZJ,0)-ls_YKHJZ1; if ls_YKHJZ11-ls_byczzj<0 then --wzy加 -ls_byczzj排除调整过资产小于0的情况 V_errmsg:='资产折旧后净值存在误差！财政剩余净值：'||to_char(ls_YKHJZ11)||',财政资产:'||to_char(nvl(RT_SB_SYKS00.FTCZZJ,0)); V_params:='提取年份：'||ls_SJTQNF||'提取月份：'||ls_SJTQYF ||'DAXXID:'||to_char(ad_daxxid); raise E_custom; end if; end if; --从未进行折旧 if nvl(RT_SB_SYKS00.FTKJXM,0)=0 and ls_YKHJZ2=0 then ls_YKHJZ21:=0; --资产重新合并 分摊金额为0 累计折旧金额不为0 以负数进行抵消 elsif nvl(RT_SB_SYKS00.FTKJXM,0)=0 and ls_YKHJZ2<>0 then ls_YKHJZ21:=-ls_YKHJZ2; elsif nvl(RT_SB_SYKS00.FTKJXM,0)>0 then ls_YKHJZ21:=nvl(RT_SB_SYKS00.FTKJXM,0)-ls_YKHJZ2; if ls_YKHJZ21<0 then V_errmsg:='资产折旧后净值存在误差！科教剩余净值：'||to_char(ls_YKHJZ21)||',科教资产:'||to_char(nvl(RT_SB_SYKS00.FTKJXM,0)); V_params:='提取年份：'||ls_SJTQNF||'提取月份：'||ls_SJTQYF ||'DAXXID:'||to_char(ad_daxxid); raise E_custom; end if; end if; --从未进行折旧 if nvl(RT_SB_SYKS00.FTQTZJ,0)=0 and ls_YKHJZ3=0 then ls_YKHJZ31:=0; --资产重新合并 分摊金额为0 累计折旧金额不为0 以负数进行抵消 elsif nvl(RT_SB_SYKS00.FTQTZJ,0)=0 and ls_YKHJZ3>0 then ls_YKHJZ31:=-ls_YKHJZ3; --有分摊金额则减去累计折旧 elsif nvl(RT_SB_SYKS00.FTQTZJ,0)>0 then ls_YKHJZ31:=nvl(RT_SB_SYKS00.FTQTZJ,0)-ls_YKHJZ3; if ls_YKHJZ31<0 then V_errmsg:='资产折旧后净值存在误差！其他剩余净值：'||to_char(ls_YKHJZ3)||',其他资产:'||to_char(nvl(RT_SB_SYKS00.FTQTZJ,0)); V_params:='提取年份：'||ls_SJTQNF||'提取月份：'||ls_SJTQYF ||'DAXXID:'||to_char(ad_daxxid); raise E_custom; end if; end if; --存在当剩余资产不多的时候 重新分配资产组成 会出现总累计折旧合计大于原值。 if ls_YKHJZ11+ls_YKHJZ21+ls_YKHJZ31+ls_YKHJZ1+ls_YKHJZ2+ls_YKHJZ3<>RT_SB_SYKS00.FTCZZJ+RT_SB_SYKS00.FTKJXM+RT_SB_SYKS00.FTQTZJ then V_errmsg:='资产折旧后净值存在误差！剩余净值+上个月折旧额：'||to_char(ls_YKHJZ11+ls_YKHJZ21+ls_YKHJZ31+ls_YKHJZ1+ls_YKHJZ2+ls_YKHJZ3)||',总资产:'||to_char(nvl(RT_SB_SYKS00.FTCZZJ,0)+nvl(RT_SB_SYKS00.FTKJXM,0)+nvl(RT_SB_SYKS00.FTQTZJ,0)); V_params:='提取年份：'||ls_SJTQNF||'提取月份：'||ls_SJTQYF ||'DAXXID:'||to_char(ad_daxxid); raise E_custom; end if; --判断各资产组成部分是否有变动 linzy 2014.02.11 存在财政、其他 合并成财政 或 财政、其他合并成科教 或 财政拆分为财政 其他 ls_YKHJZ11_1:=ls_YKHJZ1; ls_YKHJZ21_1:=ls_YKHJZ2; ls_YKHJZ31_1:=ls_YKHJZ3; if nvl(RT_SB_SYKS00.FTCZZJ,0)=0 then ls_YKHJZ1:=0; elsif nvl(RT_SB_SYKS00.FTCZZJ,0)-ls_SYCZYZ>=0 and (ls_YKHJZ21=-ls_YKHJZ2 or ls_YKHJZ31=-ls_YKHJZ3) then if ls_YKHJZ21=-ls_YKHJZ21_1 then ls_YKHJZ1 := ls_YKHJZ11- ls_YKHJZ21_1; end if; if ls_YKHJZ31=-ls_YKHJZ31_1 then ls_YKHJZ1 := ls_YKHJZ11- ls_YKHJZ31_1; end if; if ls_YKHJZ31=-ls_YKHJZ31_1 and ls_YKHJZ21=-ls_YKHJZ21_1 then ls_YKHJZ1 := ls_YKHJZ11 - ls_YKHJZ21_1 -ls_YKHJZ31_1; end if; else ls_YKHJZ1:=ls_YKHJZ11; end if; if nvl(RT_SB_SYKS00.FTKJXM,0)=0 then ls_YKHJZ2:=0; elsif nvl(RT_SB_SYKS00.FTKJXM,0)-ls_SYKJYZ>=0 and (ls_YKHJZ11=-ls_YKHJZ1 or ls_YKHJZ31=-ls_YKHJZ3) then if ls_YKHJZ11=-ls_YKHJZ11_1 then ls_YKHJZ2 := ls_YKHJZ21- ls_YKHJZ11_1; end if; if ls_YKHJZ31=-ls_YKHJZ3 then ls_YKHJZ2 := ls_YKHJZ21- ls_YKHJZ31_1; end if; if ls_YKHJZ31=-ls_YKHJZ31_1 and ls_YKHJZ11=-ls_YKHJZ11_1 then ls_YKHJZ2 := ls_YKHJZ21-ls_YKHJZ11_1-ls_YKHJZ31_1; end if; else ls_YKHJZ2:=ls_YKHJZ21; end if; if nvl(RT_SB_SYKS00.FTQTZJ,0)=0 then ls_YKHJZ3:=0; elsif nvl(RT_SB_SYKS00.FTQTZJ,0)-ls_SYQTYZ>=0 and (ls_YKHJZ11=-ls_YKHJZ1 or ls_YKHJZ21=-ls_YKHJZ2) then if ls_YKHJZ11=-ls_YKHJZ11_1 then ls_YKHJZ3 := ls_YKHJZ31- ls_YKHJZ11_1; end if; if ls_YKHJZ21=-ls_YKHJZ21_1 then ls_YKHJZ3 := ls_YKHJZ31- ls_YKHJZ21_1; end if; if ls_YKHJZ21=-ls_YKHJZ21_1 and ls_YKHJZ11=-ls_YKHJZ11_1 then ls_YKHJZ3 := ls_YKHJZ31-ls_YKHJZ11_1-ls_YKHJZ21_1; end if; else ls_YKHJZ3:=ls_YKHJZ31; end if; --检验数据是否正确 linzy 2013.11.11 本月折旧后的净值 比较 if ls_YKHJZ1-ls_BYCZZJ+ls_YKHJZ2-ls_BYKJXM+ls_YKHJZ3-ls_BYQTZJ<>ls_TQYZ00-ls_LJZJ00 then V_errmsg:='资产折旧后净值存在误差！折后净值：'||to_char(ls_TQYZ00-ls_LJZJ00)||',财政折后净值:'||to_char(ls_YKHJZ1-ls_BYCZZJ)||',科教折后净值:'||to_char(ls_YKHJZ2-ls_BYKJXM)||',其他折后净值:'||to_char(ls_YKHJZ3-ls_BYQTZJ); V_params:='提取年份：'||ls_SJTQNF||'提取月份：'||ls_SJTQYF ||'DAXXID:'||to_char(ad_daxxid); raise E_custom; end if; elsif ls_XCWHS0<>'N' then --启用新财政 在库资产进行折旧 if ls_JTZJ00='Y' then --取剩余净值 linzy 2014.01.07 select nvl(XBJE00,0)-nvl(LJCZZJ,0),nvl(QTJFJE,0)-nvl(LJKJXM,0),nvl(ZCJE00,0)-nvl(LJQTZJ,0) into ls_YKHJZ1,ls_YKHJZ2,ls_YKHJZ3 from SB_DA0000 where DAXXID=ad_daxxid; --检验数据是否正确 linzy 2013.11.11 if ls_YKHJZ1-ls_BYCZZJ+ls_YKHJZ2-ls_BYKJXM+ls_YKHJZ3-ls_BYQTZJ<>ls_TQYZ00-ls_LJZJ00 then V_errmsg:='资产折旧后净值存在误差！折后净值：'||to_char(ls_TQYZ00-ls_LJZJ00)||',财政折后净值:'||to_char(ls_YKHJZ1-ls_BYCZZJ)||',科教折后净值:'||to_char(ls_YKHJZ2-ls_BYKJXM)||',其他折后净值:'||to_char(ls_YKHJZ3-ls_BYQTZJ); V_params:='提取年份：'||ls_SJTQNF||'提取月份：'||ls_SJTQYF ||'DAXXID:'||to_char(ad_daxxid); raise E_custom; end if; else V_errmsg:='未找到对应资产的使用科室信息！'; V_params:='提取年份：'||ls_SJTQNF||'提取月份：'||ls_SJTQYF ||'DAXXID:'||to_char(ad_daxxid); raise E_custom; end if; end if; -- linzy 2011.02.11 提取最后一次折旧金额及日期 select nvl(max(TQRQ00),to_char(sysdate,'YYYYMMDD')) into ls_TQRQ00 from SB_GDZCZJ where DAXXID=ad_daxxid; select nvl(max(LJZJJE),0) into ls_LJZJJE from SB_GDZCZJ where DAXXID=ad_daxxid; --二次折旧TQBZ00用'3'表示,增加ECZJBL IF ls_ECZJBL>0 then insert into SB_GDZCZJ(DAXXID,DAH000,TQNF00,TQYF00,TQKS00,TQYZ00,TQYZJL,BYZJE0,BYBTJE, TQRQ00,TQSJ00,TQR000,TQBZ00,ZJHJZ0,LJZJJE,ECZJBL)--插入固定资产折旧提取情况明细表 select DAXXID,DAH000,ls_SJTQNF,LS_TMPYF0,ad_TQKS00,ls_TQYZ00,ls_TQYZJL,ls_ECZJJE,0, to_char(sysdate,'YYYYMMDD'),to_char(sysdate,'HH24:MI:SS'),ad_TQR000,'3', --0,ls_LJZJ00+ls_ECZJJE,ls_ECZJBL from SB_DA0000 where DAXXID=ad_DAXXID; --2008.12.15 (YZ0000 - LJECZJ)*ls_SYBFB0/100,LJECZJ*ls_SYBFB0/100,ls_ECZJBL from SB_DA0000 where DAXXID=ad_DAXXID; else --插入固定资产折旧提取情况明细表 insert into SB_GDZCZJ(DAXXID,DAH000,TQNF00,TQYF00,TQKS00,TQYZ00,TQYZJL,BYZJE0,BYBTJE, TQRQ00,TQSJ00,TQR000,TQBZ00,ZJHJZ0,LJZJJE, YZCZZJ,YZKJXM,YZQTZJ,BYCZZJ,BYKJXM,BYQTZJ, BTCZZJ,BTKJXM,BTQTZJ,JZCZZJ,JZKJXM,JZQTZJ, LJCZZJ,LJKJXM,LJQTZJ) --算法修改JZCZZJ=ls_YZCZZJ - ls_LJCZZJ改为ls_YKHJZ1-ls_BYCZZJ; 如中间分滩金额时应剩余净值所占的财政金额,科教及其他类似 linzy 2013.11.11 select DAXXID,DAH000,ls_SJTQNF,LS_TMPYF0,ad_TQKS00,ls_TQYZ00,ls_TQYZJL,ls_BYZJE0,0, to_char(sysdate,'YYYYMMDD'),to_char(sysdate,'HH24:MI:SS'),ad_TQR000,'1',(ls_TQYZ00 - ls_LJZJ00),ls_LJZJ00, ls_YZCZZJ,ls_YZKJXM,ls_YZQTZJ,ls_BYCZZJ,ls_BYKJXM,ls_BYQTZJ, 0,0,0,ls_YKHJZ1 - ls_BYCZZJ,ls_YKHJZ2 - ls_BYKJXM,ls_YKHJZ3 - ls_BYQTZJ, ls_LJCZZJ,ls_LJKJXM,ls_LJQTZJ from SB_DA0000 where DAXXID=ad_DAXXID;--这里的ls_tqyz00和ls_ljzj00取档案表 --修改档案表的信息 ls_LJZJ01取值可能不是全部 modified by qks 2011.10.17 ls_LJZJ01->ls_LJZJ02 --update SB_DA0000 set LJZJ00 =nvl(ls_LJZJ01,0)+round(ls_BYZJE0,2), JZ0000 =YZ0000-nvl(ls_LJZJ01,0)-round(ls_BYZJE0,2) update SB_DA0000 set LJZJ00 =nvl(ls_LJZJ02,0)+round(ls_BYZJE0,2), JZ0000 =YZ0000-nvl(ls_LJZJ02,0)-round(ls_BYZJE0,2), LJCZZJ =ls_LJZJ21+round(ls_BYCZZJ,2),LJKJXM =ls_LJZJ22+round(ls_BYKJXM,2),LJQTZJ =ls_LJZJ23+round(ls_BYQTZJ,2) where DAXXID = ad_daxxid; --修改调拨之后出现的净值的问题，调拨之后要接这调拨的科室的净值继续减少，20090709 chenyw if (ls_LJZJ00=round(LS_BYZJE0,2)) and (LS_BYZJE0<>0) then--把第一月给去除掉 ls_kscount:=0; select count(*) into ls_kscount from SB_DB0000 where daxxid=ad_daxxid and substr(DBSPRQ,1,6)=ls_SJTQNF||lpad(ls_SJTQYF,2,'0'); if ls_kscount<>0 then --资产多次调拨取本月第一次调拨原科室，避免无法对应上月折旧记录。 linzy select YKSBH0 into ls_DBYKS0 from SB_DB0000 where DAXXID=ad_daxxid and substr(DBSPRQ,1,6)=ls_SJTQNF||lpad(ls_SJTQYF,2,'0') and DBSPRQ||DBSPSJ=(select min(DBSPRQ||DBSPSJ) from SB_DB0000 where DAXXID=ad_daxxid and substr(DBSPRQ,1,6)=ls_SJTQNF||lpad(ls_SJTQYF,2,'0') and rownum=1); begin if ls_SJTQYF<>1 then select ZJHJZ0,JZCZZJ,JZKJXM,JZQTZJ into ls_YKHJZ0,ls_YKHJZ1,ls_YKHJZ2,ls_YKHJZ3 from SB_GDZCZJ where DAXXID=ad_daxxid and TQKS00=ls_DBYKS0 and TQNF00=ls_SJTQNF and TQYF00=ls_SJTQYF-1; else select ZJHJZ0,JZCZZJ,JZKJXM,JZQTZJ into ls_YKHJZ0,ls_YKHJZ1,ls_YKHJZ2,ls_YKHJZ3 from SB_GDZCZJ where DAXXID=ad_daxxid and TQKS00=ls_DBYKS0 and TQNF00=ls_SJTQNF-1 and TQYF00=12; end if; exception when others then --资产折旧后，修改存放科室再次调拨，会发现SB_DB0000原科室在SB_GDZCZJ中没有相关记录，则取上一次SB_GDZCZJ折旧科室. ls_DBYKS0:='0'; end; if ls_DBYKS0='0' then select TQKS00 into ls_DBYKS0 from SB_GDZCZJ where DAXXID=ad_daxxid and TQRQ00=ls_TQRQ00 and LJZJJE=ls_LJZJJE; if ls_SJTQYF<>1 then select ZJHJZ0,JZCZZJ,JZKJXM,JZQTZJ into ls_YKHJZ0,ls_YKHJZ1,ls_YKHJZ2,ls_YKHJZ3 from SB_GDZCZJ where DAXXID=ad_daxxid and TQKS00=ls_DBYKS0 and TQNF00=ls_SJTQNF and TQYF00=ls_SJTQYF-1; else select ZJHJZ0,JZCZZJ,JZKJXM,JZQTZJ into ls_YKHJZ0,ls_YKHJZ1,ls_YKHJZ2,ls_YKHJZ3 from SB_GDZCZJ where DAXXID=ad_daxxid and TQKS00=ls_DBYKS0 and TQNF00=ls_SJTQNF-1 and TQYF00=12; end if; end if; --2014.01.03 增加判断在库资产折旧 linzy 改正重新分配资产组成部分时出现错误 select count(1) into ls_Count0 from SB_SYKS00 where DAXXID=ad_daxxid and BMBH00=ad_TQKS00 and SYZT00='Y'; -- linzy 启用新财政 数据核验 if ls_Count0>0 and ls_XCWHS0<>'N' then --提取科室使用情况及资产剩余净值 2013.11.11 linzy select * into RT_SB_SYKS00 from SB_SYKS00 where DAXXID=ad_daxxid and BMBH00=ad_TQKS00; select nvl(max(LJCZZJ),0),nvl(max(LJKJXM),0),nvl(max(LJQTZJ),0) into ls_YKHJZ1,ls_YKHJZ2,ls_YKHJZ3 from ( select * from SB_GDZCZJ where DAXXID=ad_daxxid and TQKS00=ls_DBYKS0 order by TQNF00 desc,TQYF00 desc) where rownum=1; --计算各资产组成部分的净取 linzy (资产组成部分) - (最后一次各组成部分的折旧金额) = 剩余未折旧的净值 --从未进行折旧 if nvl(RT_SB_SYKS00.FTCZZJ,0)=0 and ls_YKHJZ1=0 then ls_YKHJZ11:=0; --资产重新合并 累计折旧金额不为0 以负数进行抵消 elsif nvl(RT_SB_SYKS00.FTCZZJ,0)=0 and ls_YKHJZ1>0 then ls_YKHJZ11:=-ls_YKHJZ1; elsif nvl(RT_SB_SYKS00.FTCZZJ,0)>0 then ls_YKHJZ11:=RT_SB_SYKS00.FTCZZJ-ls_YKHJZ1; if ls_YKHJZ11<0 then V_errmsg:='资产折旧后净值存在误差！财政剩余净值：'||to_char(ls_YKHJZ11)||',财政资产:'||to_char(nvl(RT_SB_SYKS00.FTCZZJ,0)); V_params:='提取年份：'||ls_SJTQNF||'提取月份：'||ls_SJTQYF ||'DAXXID:'||to_char(ad_daxxid); raise E_custom; end if; end if; --从未进行折旧 if nvl(RT_SB_SYKS00.FTKJXM,0)=0 and ls_YKHJZ2=0 then ls_YKHJZ21:=0; --资产重新合并 累计折旧金额不为0 以负数进行抵消 elsif nvl(RT_SB_SYKS00.FTKJXM,0)=0 and ls_YKHJZ2<>0 then ls_YKHJZ21:=-ls_YKHJZ2; elsif nvl(RT_SB_SYKS00.FTKJXM,0)>0 then ls_YKHJZ21:=RT_SB_SYKS00.FTKJXM-ls_YKHJZ2; if ls_YKHJZ21<0 then V_errmsg:='资产折旧后净值存在误差！科教剩余净值：'||to_char(ls_YKHJZ21)||',科教资产:'||to_char(nvl(RT_SB_SYKS00.FTKJXM,0)); V_params:='提取年份：'||ls_SJTQNF||'提取月份：'||ls_SJTQYF ||'DAXXID:'||to_char(ad_daxxid); raise E_custom; end if; end if; --从未进行折旧 if nvl(RT_SB_SYKS00.FTQTZJ,0)=0 and ls_YKHJZ3=0 then ls_YKHJZ31:=0; --资产重新合并 累计折旧金额不为0 以负数进行抵消 elsif nvl(RT_SB_SYKS00.FTQTZJ,0)=0 and ls_YKHJZ3<>0 then ls_YKHJZ31:=-ls_YKHJZ3; elsif nvl(RT_SB_SYKS00.FTQTZJ,0)>0 then ls_YKHJZ31:=nvl(RT_SB_SYKS00.FTQTZJ,0)-ls_YKHJZ3; if ls_YKHJZ31<0 then V_errmsg:='资产折旧后净值存在误差！其他剩余净值：'||to_char(ls_YKHJZ3)||',其他资产:'||to_char(nvl(RT_SB_SYKS00.FTQTZJ,0)); V_params:='提取年份：'||ls_SJTQNF||'提取月份：'||ls_SJTQYF ||'DAXXID:'||to_char(ad_daxxid); raise E_custom; end if; end if; --存在当剩余资产不多的时候 重新分配资产组成 会出现总累计折旧合计大于原值。 if ls_YKHJZ11+ls_YKHJZ21+ls_YKHJZ31+ls_YKHJZ1+ls_YKHJZ2+ls_YKHJZ3<>RT_SB_SYKS00.FTCZZJ+RT_SB_SYKS00.FTKJXM+RT_SB_SYKS00.FTQTZJ then V_errmsg:='资产折旧后净值存在误差！剩余净值+上个月折旧额：'||to_char(ls_YKHJZ11+ls_YKHJZ21+ls_YKHJZ31+ls_YKHJZ1+ls_YKHJZ2+ls_YKHJZ3)||',总资产:'||to_char(nvl(RT_SB_SYKS00.FTCZZJ,0)+nvl(RT_SB_SYKS00.FTKJXM,0)+nvl(RT_SB_SYKS00.FTQTZJ,0)); V_params:='提取年份：'||ls_SJTQNF||'提取月份：'||ls_SJTQYF ||'DAXXID:'||to_char(ad_daxxid); raise E_custom; end if; --本月财政是否增加 ls_YKHJZ11_1:=ls_YKHJZ1; ls_YKHJZ21_1:=ls_YKHJZ2; ls_YKHJZ31_1:=ls_YKHJZ3; if nvl(RT_SB_SYKS00.FTCZZJ,0)=0 then ls_YKHJZ1:=0; elsif nvl(RT_SB_SYKS00.FTCZZJ,0)-ls_SYCZYZ>=0 and (ls_YKHJZ21=-ls_YKHJZ2 or ls_YKHJZ31=-ls_YKHJZ3) then --科教金额合并财政 if ls_YKHJZ21=-ls_YKHJZ21_1 then ls_YKHJZ1 := ls_YKHJZ11- ls_YKHJZ21_1; end if; --其他金额合并财政 if ls_YKHJZ31=-ls_YKHJZ31_1 then ls_YKHJZ1 := ls_YKHJZ11- ls_YKHJZ31_1; end if; --其他、科教合并到财政 if ls_YKHJZ31=-ls_YKHJZ31_1 and ls_YKHJZ21=-ls_YKHJZ21_1 then ls_YKHJZ1 := ls_YKHJZ11-ls_YKHJZ21_1-ls_YKHJZ31_1; end if; else ls_YKHJZ1:=ls_YKHJZ11; end if; if nvl(RT_SB_SYKS00.FTKJXM,0)=0 then ls_YKHJZ2:=0; elsif nvl(RT_SB_SYKS00.FTKJXM,0)-ls_SYKJYZ>=0 and (ls_YKHJZ11=-ls_YKHJZ1 or ls_YKHJZ31=-ls_YKHJZ3) then if ls_YKHJZ11=-ls_YKHJZ11_1 then ls_YKHJZ2 := ls_YKHJZ21- ls_YKHJZ11_1; end if; if ls_YKHJZ31=-ls_YKHJZ31_1 then ls_YKHJZ2 := ls_YKHJZ21- ls_YKHJZ31_1; end if; if ls_YKHJZ31=-ls_YKHJZ31_1 and ls_YKHJZ11=-ls_YKHJZ11_1 then ls_YKHJZ2 := ls_YKHJZ21-ls_YKHJZ1-ls_YKHJZ31_1; end if; else ls_YKHJZ2:=ls_YKHJZ21; end if; if nvl(RT_SB_SYKS00.FTQTZJ,0)=0 then ls_YKHJZ3:=0; elsif nvl(RT_SB_SYKS00.FTQTZJ,0)-ls_SYQTYZ>=0 and (ls_YKHJZ11=-ls_YKHJZ1 or ls_YKHJZ21=-ls_YKHJZ2) then if ls_YKHJZ11=-ls_YKHJZ11_1 then ls_YKHJZ3 := ls_YKHJZ31- ls_YKHJZ11_1; end if; if ls_YKHJZ21=-ls_YKHJZ21_1 then ls_YKHJZ3 := ls_YKHJZ31- ls_YKHJZ21_1; end if; if ls_YKHJZ21=-ls_YKHJZ21_1 and ls_YKHJZ11=-ls_YKHJZ11_1 then ls_YKHJZ3 := ls_YKHJZ31-ls_YKHJZ11_1-ls_YKHJZ21_1; end if; else ls_YKHJZ3:=ls_YKHJZ31; end if; --检验数据是否正确 linzy 2013.11.11 if ls_YKHJZ1-ls_BYCZZJ+ls_YKHJZ2-ls_BYKJXM+ls_YKHJZ3-ls_BYQTZJ<>ls_TQYZ00-ls_LJZJ00 then V_errmsg:='资产折旧后净值存在误差！折后净值：'||to_char(ls_TQYZ00-ls_LJZJ00)||',财政折后净值:'||to_char(ls_YKHJZ1-ls_BYCZZJ)||',科教折后净值:'||to_char(ls_YKHJZ2-ls_BYKJXM)||',其他折后净值:'||to_char(ls_YKHJZ3-ls_BYQTZJ); V_params:='提取年份：'||ls_SJTQNF||'提取月份：'||ls_SJTQYF ||'DAXXID:'||to_char(ad_daxxid); raise E_custom; end if; elsif ls_XCWHS0<>'N' then --如果是启用新财政 在库资产进行折旧 if ls_JTZJ00='Y' then --取剩余净值 linzy 2014.01.07 select nvl(XBJE00,0)-nvl(LJCZZJ,0),nvl(QTJFJE,0)-nvl(LJKJXM,0),nvl(ZCJE00,0)-nvl(LJQTZJ,0) into ls_YKHJZ1,ls_YKHJZ2,ls_YKHJZ3 from SB_DA0000 where DAXXID=ad_daxxid; --检验数据是否正确 linzy 2013.11.11 if ls_YKHJZ1-ls_BYCZZJ+ls_YKHJZ2-ls_BYKJXM+ls_YKHJZ3-ls_BYQTZJ<>ls_TQYZ00-ls_LJZJ00 then V_errmsg:='资产折旧后净值存在误差！折后净值：'||to_char(ls_TQYZ00-ls_LJZJ00)||',财政折后净值:'||to_char(ls_YKHJZ1-ls_BYCZZJ)||',科教折后净值:'||to_char(ls_YKHJZ2-ls_BYKJXM)||',其他折后净值:'||to_char(ls_YKHJZ3-ls_BYQTZJ); V_params:='提取年份：'||ls_SJTQNF||'提取月份：'||ls_SJTQYF ||'DAXXID:'||to_char(ad_daxxid); raise E_custom; end if; else V_errmsg:='未找到对应资产的使用科室信息！'; V_params:='提取年份：'||ls_SJTQNF||'提取月份：'||ls_SJTQYF ||'DAXXID:'||to_char(ad_daxxid); raise E_custom; end if; end if; --round(ls_BYZJE0,2)-->ls_BYZJE0 if nvl(ls_YKHJZ0,0)<>0 then update SB_GDZCZJ set ZJHJZ0=ls_YKHJZ0-LS_BYZJE0,LJZJJE=nvl(ls_LJZJ01,0)+ls_BYZJE0, --round(ls_BYZJE0,2) JZCZZJ=ls_YKHJZ1-ls_BYCZZJ, JZKJXM=ls_YKHJZ2-ls_BYKJXM,JZQTZJ=ls_YKHJZ3-ls_BYQTZJ, LJCZZJ=ls_LJZJ11+ls_BYCZZJ, LJKJXM=ls_LJZJ12+ls_BYKJXM,LJQTZJ=ls_LJZJ13+ls_BYQTZJ where DAXXID=ad_daxxid and TQKS00=ad_TQKS00 and TQNF00=ls_SJTQNF and TQYF00=ls_SJTQYF; end if; end if; end if; end if; ls_i0000:=ls_i0000+1; end loop; end; --档案表最后数据校验 linzy select count(1) into ls_kscount from SB_DA0000 where DAXXID=ad_daxxid and ((nvl(LJZJ00,0)+nvl(JZ0000,YZ0000)<>YZ0000) or nvl(JZ0000,YZ0000)<0); if ls_kscount>0 then V_errmsg:='资产折旧档案表净值存在误差或小于零！累计折旧+净值<>原值'; V_params:='提取年份：'||ls_SJTQNF||'提取月份：'||ls_SJTQYF ||'DAXXID:'||to_char(ad_daxxid); raise E_custom; end if; commit; --end; exception when no_data_found then raise_application_error(-20001,substrb('数据没有找到！*'||SQLERRM||','||V_params,1,120)); rollback; when E_custom then RAISE_APPLICATION_ERROR(-20010,substrb(V_errmsg||'！*'||V_params,1,120)); rollback; when others then raise_application_error(-20266,substrb(nvl(SQLERRM, '原因不明出错')||'！*'||V_params,1,120)); rollback; end; / -- End of DDL Script for Procedure SD_HOSPITAL.SP_SB_DAXX00_DYSBTQ