WHERE ZS.NSRNBM=DJ.NSRNBM(+) AND ZS.ZSJG_DM = SWJG1.SWJG_DM
AND SWJG1.SJSWJG_DM=SWJG2.SWJG_DM
AND ZS.LRRY_DM = SWRY.SWRY_DM(+) AND (1=1) AND (1=1) AND (1=1) AND (1=1) AND (1=1) AND (1=1) AND (1=1) AND (1=1) AND (1=1) AND (1=1) AND (1=1) AND (1=1) AND (1=1) AND (1=1) AND (1=1)
AND ZS.ZF_BJ='0' AND (1=1) AND (1=1) AND (1=1)
AND EXISTS (SELECT 1
FROM T_XT_SWRY_CX CX
WHERE (DJ.GLJG_DM=CX.SWJG_DM OR ZS.GLJG_DM=SWJG_DM) AND CX.SWRY_DM ='26200000000' ) ) A
GROUP BY ROLLUP() HAVING (1=1)
过程create or replace procedure P_FP_MQKPMX_SL as /* 此过程用于更新门前开票总税率
2015-03-25 李宝瑜 */
sll number(16, 6);
type zjtab_type is varray(5) of libaoyu.t_zj_b%rowtype; zjtab zjtab_type; cursor v_cur is
select * from libaoyu.t_zj_b ;
v_rows int := 5; v_count int := 0; begin
/*清空表*/
delete from libaoyu.t_zj_b; commit;
/*插入开票信息相关联数据,便于更新数据*/ insert /*Append*/ into libaoyu.t_zj_b b
select distinct x.dzsph_xh, x.pzzl_dm, x.pzzb_dm, x.pzhm, x.pz_xh from (select distinct qk.dzsph_xh, qk.pzzl_dm, qk.pzzb_dm, mx.pzhm, xj.pz_xh
from t_zs_pzsyqk qk, t_fp_mqkpmx mx, t_zs_xjjkmx xj where mx.pzzl_dm = qk.pzzl_dm and mx.pzzb_dm = qk.pzzb_dm and mx.pzhm = qk.pzhm and mx.sl is null
and mx.lr_sj>=sysdate-1 and xj.dzsph_xh = qk.dzsph_xh and qk.zfr_dm is null and qk.zf_rq is null and xj.zf_bj = '0' and xj.pz_xh is not null union
select distinct qk.dzsph_xh, qk.pzzl_dm, qk.pzzb_dm, mx.pzhm, zj.pz_xh
from t_zs_pzsyqk qk, t_fp_mqkpmx mx, t_zs_zjjkmx zj where mx.pzzl_dm = qk.pzzl_dm and mx.pzzb_dm = qk.pzzb_dm and mx.lr_sj>=sysdate-1 and mx.sl is null and qk.zfr_dm is null and qk.zf_rq is null and zj.zf_bj = '0' and mx.pzhm = qk.pzhm
and zj.dzsph_xh = qk.dzsph_xh and zj.pz_xh is not null) x; commit;
open v_cur; loop
fetch v_cur bulk collect into zjtab limit v_rows; exit when v_cur%notfound;
for i in 1 .. (v_cur%rowcount - v_count) loop /*计算总税率*/
select max (x.sl) into sll from
(select min(nvl(round(decode(a.js_yjj,0,0,(a.zje + b.fsje) /a.js_yjj),2),0))as sl
from (select yz.pz_xh, yz.zsxm_dm, yz.zspm_dm, yz.yzsf_je, yz.js_yj,
sum(yz.js_yj) over(partition by zsxm_dm) js_yjj, sum(yz.yzsf_je) over(partition by null) zje, sum(yz.js_yj) over(partition by null) zjsyj from t_zs_yzpz yz
where yz.pz_xh =zjtab(i).pz_xh
and yz.zsxm_dm not in ('16', '61', '90', '92') ) a,
(select nvl(sum(yz1.yzsf_je),0) fsje from t_zs_yzpz yz1
where yz1.pz_xh =zjtab(i).pz_xh
and yz1.zsxm_dm in ('16', '61', '90', '92')) b where a.zsxm_dm ='02' union select
min(nvl(round(decode(c.js_yj,0,c.zjsyj,c.zje/c.js_yj),2),0)) as sl
from (select yz.pz_xh, yz.zsxm_dm, yz.zspm_dm, yz.yzsf_je, yz.js_yj,
sum(yz.yzsf_je) over(partition by null) zje, sum(yz.js_yj) over(partition by null) zjsyj from t_zs_yzpz yz
where yz.pz_xh = zjtab(i).pz_xh
and yz.zsxm_dm not in ('16', '61', '90', '92') ) c)x;
/*更新门前开票明细总税率*/
update t_fp_mqkpmx mx set mx.sl = sll
where mx.pzzl_dm = zjtab(i).pzzl_dm and mx.pzhm = zjtab(i).pzhm and mx.pzzb_dm = zjtab(i).pzzb_dm ; commit;
v_count := v_cur%rowcount; end loop; end loop; close v_cur;
exception
when others then rollback; declare
errmsg varchar2(32766); begin
errmsg := SQLERRM(SQLCODE); dbms_output.put_line(errmsg); end;
end P_FP_MQKPMX_SL;
create or replace procedure p_gs_kt_zwyth_xqjg(i_hsjgdm varchar2, /* i_kjnyqs date,
i_kjnyzz date,*/
i_czrydm varchar2) is
/*********************************************************************************
甘肃环境账务一体化加工过程，该过程仅对县区级核算机关进行报表撤账、抽取、加工、记账 过程说明：
--1、撤账：根据核算机关代码及会计年月进行撤销账簿操作。 --2、抽取：根据核算机关代码及会计年月进行数据抽取操作。 --3、加工：根据核算机关代码及会计年月进行数据加工操作。 --4、记账：根据核算机关代码及会计年月进行报表记账 李宝瑜 at 2015-05-13
*********************************************************************************/
l_kjny DATE; --会计年月（临时变量）
/* l_kjnyqs varchar2(10); --会计年月（临时变量） l_kjnyzz varchar2(10); --会计年月（临时变量）*/ v_hsjgdm varchar2(11); --核算机关代码 l_hsjgdm varchar2(11); --核算机关代码(临时）
v_yjbz char(1) default '0'; --记录月结标志，后续判断，如果月结标志为0则不进行报表加工。
begin
--核算机关定义
if i_hsjgdm is NULL or i_hsjgdm = '' then
RAISE_APPLICATION_ERROR(-20005, 'i_hsjgdm' || '不能为空'); else
v_hsjgdm := i_hsjgdm;
end if;
--会计年月起始定义
/* if i_kjnyqs is NULL or i_kjnyqs = '' or i_kjnyzz is NULL or i_kjnyzz = '' then
RAISE_APPLICATION_ERROR(-20006, 'i_kjnyqs,i_kjnyzz' || '不能为空'); else
l_kjnyqs := to_char(i_kjnyqs, 'yyyy-MM-dd'); l_kjnyzz := to_char(i_kjnyzz, 'yyyy-MM-dd');
end if;*/
--如果参数不为空则以参数取会计年月
EXECUTE IMMEDIATE 'TRUNCATE TABLE DB_ZGXT.T_GS_KT_ZWYTH_YTHCZXX'; --
清空账务一体化操作信息
commit;
--获取的核算机关
for jgxx in (select bbjg.hsjg_dm, bbjg.sjjg_dm from T_GS_KT_CS_ZWYTH_XQBBJG bbjg
where not exists(select 1 from T_GS_KT_ZWYTH_YTHCZXX xx where bbjg.hsjg_dm=xx.hsjg_dm ) ) loop --区县级核算机关报表处理
--1.如果传入的核算机关是县区级，v_hsjgdm取县区级核算机关代码
--2.如果传入的核算机关是市级，v_hsjgdm取市所对应的县区级核算机关代码 --3.如果传入的核算机关是省级,v_hsjgdm取所有的县区级核算机关代码 if v_hsjgdm = jgxx.hsjg_dm or v_hsjgdm = jgxx.sjjg_dm or v_hsjgdm = '262000000' then
select jgxx.hsjg_dm into l_hsjgdm from dual; --获取会计年月区间月份
for kjny in ( /*SELECT TO_CHAR(ADD_MONTHS(TO_DATE(l_kjnyqs, 'yyyy-MM-dd'),
ROWNUM - 1), 'yyyy-MM-dd') DAY_ID FROM DUAL
CONNECT BY ROWNUM <=
months_between(to_date(l_kjnyzz, 'yyyy-MM-dd'), to_date(l_kjnyqs, 'yyyy-MM-dd')) + 1*/
select date '2015-09-01' day_id from dual) loop
select kjny.day_id into l_kjny from dual; select count(1) into v_yjbz
from t_kt_ktczxx xx
where xx.hsjg_dm = jgxx.hsjg_dm and xx.ktczzl = '03'
and xx.gs_rq = to_date(to_char(l_kjny - 1, 'yyyy-mm') || '-01', 'yyyy-MM-dd'); ---如果月结标志不为0，则进行核算机关报表加工 if v_yjbz != '0' then
p_gs_kt_zwyth_jg(l_hsjgdm, l_kjny, i_czrydm); end if; end loop; end if; end loop; --异常处理 exception
when others then rollback; declare
errmsg varchar2(32766); begin
errmsg := SQLERRM(SQLCODE); dbms_output.put_line(errmsg); end;
end p_gs_kt_zwyth_xqjg;