CREATE OR REPLACE PROCEDURE PROC_TZ_EXEC_N_YEARREPORT(ssrq
varchar2 )
as
-----声明变量
v_raise EXCEPTION;
begin
--类型定义
--begin
--execute PROC_TZ_N_YEARREPORT('201601','2015-12-26','2016-01-25');
--end;
commit;
--异常处理--
EXCEPTION
WHEN NO_DATA_FOUND
THEN
RAISE_APPLICATION_ERROR (-20011,
'存储过程发生了异常！' );
ROLLBACK;
end PROC_TZ_EXEC_N_YEARREPORT;
/
CREATE OR REPLACE PROCEDURE WANFO.PROC_TZ_QUERY_DETAIL(beginTime
in varchar2,endTime
varchar2)
as
-----声明变量
v_raise EXCEPTION;
begin
--类型定义
begin
--预定--
INSERT INTO TZ_QUERY_YDMXTZ(SSSQ,DJH,YWLX,YWDJH,GXRXM,MW,JE,JBR,DBR,BLRQ,XXLY,ZZFLAG,BZ)
--退定--
SELECT null,t2.djh,
'退定' as ywlx,t2.id
as ywdjh,t1.gxrxm,t1.mwflmc
||t1.mwmc
as mw,t2.yjje
as yjje,t1.czyxm
as czyxm,t1.dbrxm,t2.rq
AS BLRQ,t1.xxly,t2.ZZFLAG,T2.TXYY
AS BZ
FROM ywdjb_yj t1,ywdjb_yj_1 t2
WHERE t1.djh
=t2.djh
and t1.mwflmc
not like '%CU%' AND t2.zxflag
<>'1' AND t2.sfflag
='1' AND (t2.ZZFLAG
='2' )
--二次缴费,预订金算租用金额--
AND to_char(t2.rq,
'yyyy-mm-dd')
>=''||beginTime
||'' AND to_char(t2.rq,
'yyyy-mm-dd')
<=''||endTime
||'' AND (T2.TXYY
NOT like '%合一%' )
UNION ALL
--二次缴费--
SELECT null,t2.djh,
'二次缴费' as ywlx,t2.id
as ywdjh,t1.gxrxm,t1.mwflmc
||t1.mwmc
as mw,t2.yjje
as yjje,t1.czyxm
as czyxm,t1.dbrxm,t2.rq
AS BLRQ,t1.xxly,t2.ZZFLAG,T2.TXYY
AS BZ
FROM ywdjb_yj t1,ywdjb_yj_1 t2
WHERE t1.djh
=t2.djh
and t1.mwflmc
not like '%CU%' AND t2.zxflag
<>'1' AND t2.sfflag
='1' AND ( t2.ZZFLAG
='3')
--二次缴费,预订金算租用金额--
AND to_char(t2.rq,
'yyyy-mm-dd')
>=''||beginTime
||'' AND to_char(t2.rq,
'yyyy-mm-dd')
<=''||endTime
||''
AND ID
NOT IN (
SELECT ( ID
-1)
AS ID
FROM YWDJB_YJ_1
WHERE to_char(t2.rq,
'yyyy-mm-dd')
>=''||beginTime
||'' AND to_char(t2.rq,
'yyyy-mm-dd')
<=''||endTime
||'' AND T2.TXYY
like '%合一%' )
-- 合并发生的二次缴费不计算
AND t1.djh
NOT IN (
SELECT djh
FROM YWDJB_YJ_1
WHERE to_char(rq,
'yyyy-mm-dd')
>=''||beginTime
||'' AND to_char(rq,
'yyyy-mm-dd')
<=''||endTime
||'' AND TXYY
like '%合一%' and t1.djh
=djh)
AND t1.djh
NOT IN (
SELECT yzdjh
FROM ywdjb
WHERE zxflag
<>'1' AND to_char(rq,
'yyyy-mm-dd')
>=''||beginTime
||'' AND to_char(rq,
'yyyy-mm-dd')
<=''||endTime
||'' AND yzdjh
is not null)
-- 二次缴款 非同期
UNION ALL
--预定--
SELECT null AS SSSQ,t2.djh,
'预定' as ywlx,t2.id
as ywdjh,t1.gxrxm,t1.mwflmc
||t1.mwmc
as mw,t2.yjje
as yjje,t1.czyxm
as czyxm,t1.dbrxm,t2.rq
AS BLRQ,t1.xxly,t2.ZZFLAG,T2.TXYY
AS BZ
FROM ywdjb_yj t1,ywdjb_yj_1 t2
WHERE t1.djh
=t2.djh
and t1.mwflmc
not like '%CU%' AND t1.zxflag
<>'1' AND t2.zxflag
<>'1' AND t1.azfsdm
='01' AND t2.ZZFLAG
='0' AND t1.djh
NOT IN (
SELECT yzdjh
FROM ywdjb
WHERE zxflag
<>'1' AND to_char(rq,
'yyyy-mm-dd')
>=''||beginTime
||''
AND to_char(rq,
'yyyy-mm-dd')
<=''||endTime
||'' AND yzdjh
is not null)
AND to_char(t2.rq,
'yyyy-mm-dd')
>=''||beginTime
||'' AND to_char(t2.rq,
'yyyy-mm-dd')
<=''||endTime
||''
AND t2.dabh
not in (
SELECT dabh
FROM ywdjb_yj_1
WHERE zxflag
<>'1' AND to_char(rq,
'yyyy-mm-dd')
>=''||beginTime
||'' AND to_char(rq,
'yyyy-mm-dd')
<=''||endTime
||'' and TXYY
like '%合一%')
;
--暂存款台账--
INSERT INTO TZ_QUERY_ZCKTZ( JBR, ZCK, TZCK, ZCKRQ, YDR, YDAZRQ, RQ,FLAG)
SELECT CZYXM,
SUM(ZCK),
SUM(TZCK),ZCKRQ,YDR,TO_CHAR(YDAZRQ,
'YYYY-MM-DD'),TO_CHAR(RQ,
'YYYY-MM-DD'),FLAG
FROM (
--暂存款--
SELECT RQ,YDAZRQ,CZYXM,GXRXM
AS ydr,YJJE
AS ZCK,
0 AS TZCK,ZCKRQ,
'1' AS FLAG
FROM V_BS_RES_ZCK
WHERE to_char(zckrq,
'yyyy-mm-dd')
>=''||beginTime
||'' AND to_char(zckrq,
'yyyy-mm-dd')
<=''||endTime
||''
UNION ALL
--退暂存款--
SELECT (
SELECT RQ
FROM YWDJB_YJ
WHERE DJH
=T1.DJH)
AS RQ,(
SELECT YDAZRQ
FROM YWDJB_YJ
WHERE DJH
=T1.DJH)
AS YDAZRQ,
(SELECT CZYXM
FROM YWDJB_YJ
WHERE DJH
=T1.DJH)
AS CZYXM,(
SELECT GXRXM
FROM YWDJB_YJ
WHERE DJH
=T1.DJH)
AS YDR,
0,
t1.yjje AS YJJE,t1.rq
as ZCKRQ,
'0' AS FLAG
FROM ywdjb_yj_1 t1 ,ywdjb_yj t2
WHERE t1.djh
=t2.djh
and t1.zxflag
= '0' AND t2.zckflag
= '1' AND t1.zzflag
='5' and to_char(t1.rq,
'yyyy-mm-dd')
>=''||beginTime
||'' AND to_char(t1.rq,
'yyyy-mm-dd')
<=''||endTime
||''
)
GROUP BY RQ,YDAZRQ,CZYXM,YDR,ZCKRQ,FLAG;
--租用--
INSERT INTO TZ_QUERY_ZYMXTZ(DJH,AZFSDM,BLRQ,SSSQ,MW,GXRXM,JBR,MXTJFL,STAZ,LTAZ,MXMJ,XXLY,ZMJ,BCJKJE,GMK,YHK,BCK,DABH,YD_Z_JKJE,YD_Y_JKJE,YD_N_JKJE,YZDJH,BZ)
--本期租用收入--
SELECT t1.DJH,
'01' as AZFSDM,t1.rq
AS BLRQ,
null AS SSSQ,t1.mwflmc
||t1.mwmc
AS MW,t1.gxrxm,t1.CZYXM
,(SELECT SCMXTJFL1
FROM spmldm
WHERE spmldm
=t1.mxdm)
as MXTJFL
,(SELECT STAZBZ
from azfs
WHERE azfsdm
=t1.azfsdm )
AS STAZ
,(SELECT LTAZBZ
from azfs
WHERE azfsdm
=t1.azfsdm )
AS LTAZ
,nvl(t1.mj,0)
as mj,t1.city,nvl(t1.zmj,
0)
as zmj
,nvl((SELECT SUM(SSJE)
FROM FYB_MX
WHERE DJH
=t1.DJH
AND YWDM
='01' AND (FMDM
='0101' OR FMDM
='0102' OR FMDM
='0103' OR FMDM
='5001')),
0)
AS BCJKJE
,nvl((SELECT SUM(SSJE)
FROM FYB_MX
WHERE DJH
=t1.DJH
AND YWDM
='01' AND FMDM
='0101'),
0)
AS GMK
,nvl((SELECT SUM(SSJE)
FROM FYB_MX
WHERE DJH
=t1.DJH
AND YWDM
='01' AND FMDM
='0102'),
0)
AS YHK
,nvl((SELECT SUM(SSJE)
FROM FYB_MX
WHERE DJH
=t1.DJH
AND YWDM
='01' AND FMDM
='0103'),
0)
AS BCK
,t1.DABH
,nvl((SELECT SUM(yjje)
FROM YWDJB_YJ_1 T3
WHERE (ZZFLAG
='0' OR ZZFLAG
='3')
AND DJH
=t1.YZDJH
AND NOT EXISTS(
SELECT DJH
FROM YWDJB_YJ_1
WHERE (ID
= T3.ID
+1)
AND TXYY
LIKE '%合%')
AND (TO_CHAR(RQ,
'YYYY-MM-DD')
>=''||beginTime
||'' AND
TO_CHAR(RQ,'YYYY-MM-DD')
<=''||endTime
||''))
+ +fn_getyjje(t1.yzdjh,
''||beginTime
||'',
''||endTime
||''),
0)
AS YD_Z_JKJE
,nvl((SELECT SUM(yjje)
FROM YWDJB_YJ_1 T3
WHERE (ZZFLAG
='0' OR ZZFLAG
='3')
AND DJH
=t1.YZDJH
AND NOT EXISTS(
SELECT DJH
FROM YWDJB_YJ_1
WHERE (ID
= T3.ID
+1)
AND TXYY
LIKE '%合%')
AND (SUBSTR(FUN_ACCOUNT_DATE(RQ),
1,
6)
=
SUBSTR(replace(
''||beginTime
||'',
'-',
''),
1,
6)))
+ +fn_getyjje(t1.yzdjh,
''||beginTime
||'',
''||endTime
||''),
0)
AS YD_Y_JKJE
,nvl((SELECT SUM(yjje)
FROM YWDJB_YJ_1 T3
WHERE (ZZFLAG
='0' OR ZZFLAG
='3')
AND DJH
=t1.YZDJH
AND NOT EXISTS(
SELECT DJH
FROM YWDJB_YJ_1
WHERE (ID
= T3.ID
+1)
AND TXYY
LIKE '%合%')
AND (SUBSTR(FUN_ACCOUNT_DATE(RQ),
1,
4)
=
SUBSTR(''||beginTime
||'',
1,
4)))
+ +fn_getyjje(t1.yzdjh,
''||beginTime
||'',
''||endTime
||''),
0)
AS YD_N_JKJE
,t1.YZDJH,T1.BZ
FROM YWDJB t1
WHERE t1.mwflmc
not like '%CU%' AND t1.AZFSDM
='01' AND t1.ZXFLAG
<>'1' AND t1.djh
not in(
SELECT djh
FROM FYB_MX
WHERE zxflag
<>'1' and TO_CHAR(SFRQ,
'YYYY/MM/DD')
= '0001/01/01')
AND TO_CHAR(T1.RQ,
'YYYY-MM-DD')
>=''||beginTime
||'' AND TO_CHAR(t1.RQ,
'YYYY-MM-DD')
<=''||endTime
||''
;
--退墓 迁出--
INSERT INTO TZ_QUERY_TQMXTZ(DJH,YWDJH,YWLX,BLRQ,SSSQ,BLRXM,MW,MXMC,JE,JBR,GMK,YHK,BCK,ZNJ,MBGHK,MXTJFL,STAZ,LTAZ,DABH)
--退墓--
SELECT t1.djh,t1.ywdjh,
'退墓' AS ywlx,t1.rq
as blrq,
'20151009' as sssq,t1.gxrxm
as BLRXM,t1.mwflmc
||t1.mwmc
AS MW,t1.mxmc,t1.ssje
as je,t1.czyxm
as jbr
,nvl((SELECT SUM(SSJE)
FROM fyb_mx
WHERE djh
=t1.djh
and ywdjh
=t1.ywdjh
AND FMDM
='0101' AND YWDM
='12' ),
0)
AS GMK
,nvl((SELECT SUM(SSJE)
FROM fyb_mx
WHERE djh
=t1.djh
and ywdjh
=t1.ywdjh
AND FMDM
='0102' AND YWDM
='12' ),
0)
AS YHK
,nvl((SELECT SUM(SSJE)
FROM fyb_mx
WHERE djh
=t1.djh
and ywdjh
=t1.ywdjh
AND FMDM
='0103' AND YWDM
='12' ),
0)
AS BCK
,nvl((SELECT SUM(SSJE)
FROM fyb_mx
WHERE djh
=t1.djh
and ywdjh
=t1.ywdjh
AND FMDM
='1101' AND YWDM
='12' ),
0)
AS ZNJ
,nvl((SELECT SUM(SSJE)
FROM fyb_mx
WHERE djh
=t1.djh
and ywdjh
=t1.ywdjh
AND FMDM
='1001' AND YWDM
='12' ),
0)
AS MBGHK
,(SELECT SCMXTJFL1
FROM spmldm
WHERE spmldm
=t1.mxdm)
as MXTJFL
,(SELECT STAZBZ
from azfs
WHERE azfsdm
=t1.azfsdm )
AS STAZ
,(SELECT LTAZBZ
from azfs
WHERE azfsdm
=t1.azfsdm )
AS LTAZ
,t1.dabh
FROM qtywb t1
WHERE mwflmc
not like '%CU%' and ywdm
= '12' AND to_char(rq ,
'yyyy-mm-dd')
>= ''||beginTime
||'' AND to_char(rq,
'yyyy-mm-dd')
<=''||endTime
||'' AND azfsdm
= '01' AND zxflag
<> '1'
--迁出--
UNION ALL
SELECT t1.djh,t1.ywdjh,
'迁出' AS ywlx,t1.rq
as blrq,
'20151009' as sssq,t1.gxrxm
as BLRXM,t1.mwflmc
||t1.mwmc
AS MW,t1.mxmc,t1.ssje
as je,t1.czyxm
as jbr
,nvl((SELECT SUM(SSJE)
FROM fyb_mx
WHERE djh
=t1.djh
and ywdjh
=t1.ywdjh
AND FMDM
='0101' AND YWDM
='12' ),
0)
AS GMK
,nvl((SELECT SUM(SSJE)
FROM fyb_mx
WHERE djh
=t1.djh
and ywdjh
=t1.ywdjh
AND FMDM
='0102' AND YWDM
='12' ),
0)
AS YHK
,nvl((SELECT SUM(SSJE)
FROM fyb_mx
WHERE djh
=t1.djh
and ywdjh
=t1.ywdjh
AND FMDM
='0103' AND YWDM
='12' ),
0)
AS BCK
,nvl((SELECT SUM(SSJE)
FROM fyb_mx
WHERE djh
=t1.djh
and ywdjh
=t1.ywdjh
AND FMDM
='1101' AND YWDM
='12' ),
0)
AS ZNJ
,nvl((SELECT SUM(SSJE)
FROM fyb_mx
WHERE djh
=t1.djh
and ywdjh
=t1.ywdjh
AND FMDM
='1001' AND YWDM
='12' ),
0)
AS MBGHK
,(SELECT SCMXTJFL1
FROM spmldm
WHERE spmldm
=t1.mxdm)
as MXTJFL
,(SELECT STAZBZ
from azfs
WHERE azfsdm
=t1.azfsdm )
AS STAZ
,(SELECT LTAZBZ
from azfs
WHERE azfsdm
=t1.azfsdm )
AS LTAZ
,t1.dabh
FROM qtywb t1
WHERE mwflmc
not like '%CU%' and ywdm
= '06' AND to_char(rq ,
'yyyy-mm-dd')
>= ''||beginTime
||'' AND to_char(rq,
'yyyy-mm-dd')
<=''||endTime
||'' AND azfsdm
= '01' AND zxflag
<> '1'
;
--综合业务--
INSERT INTO TZ_QUERY_ZHMXTZ(djh,ywdjh,dabh,BLRQ,BLRXM,MW,MXMC,MXTJFL,JE,JBR,TXF,AZF,KZF,TJF,MJF,ZF,hbf,STAZ,LTAZ)
-- 租用业务表中办理
SELECT t1.djh, djh
as ywdjh ,t1.dabh,(
SELECT sfrq
FROM fyb_mx
WHERE djh
=t1.djh
and ywdjh
=t1.djh
and rownum
=1)
AS BLRQ,t1.gxrxm
AS BLRXM,t1.mwflmc
||t1.mwmc
as MW,t1.mxmc
,(SELECT SCMXTJFL1
FROM spmldm
WHERE spmldm
=t1.mxdm)
as MXTJFL
,nvl((SELECT sum(ssje)
FROM fyb_mx
WHERE djh
=t1.djh
and ywdjh
=t1.djh
and (FMMC
LIKE '%其他费用%' or FMMC
LIKE '%头像费%' or FMMC
LIKE '%安葬费%' or FMMC
LIKE '%刻字费%' or FMMC
LIKE '%贴金费%' or FMMC
LIKE '%描金费%' or FMMC
LIKE '%杂费%' OR FMMC
LIKE'%换碑费%')),
0)
AS JE,
t1.czyxm AS JBR
,nvl((SELECT sum(ssje)
FROM fyb_mx
WHERE djh
=t1.djh
and ywdjh
=t1.djh
and FMMC
LIKE '%头像费%' ),
0)
as TXF
,nvl((SELECT sum(ssje)
FROM fyb_mx
WHERE djh
=t1.djh
and ywdjh
=t1.djh
and FMMC
LIKE '%安葬费%' ),
0)
as AZF
,nvl((SELECT sum(ssje)
FROM fyb_mx
WHERE djh
=t1.djh
and ywdjh
=t1.djh
and FMMC
LIKE '%刻字费%'),
0)
as KZF
,nvl((SELECT sum(ssje)
FROM fyb_mx
WHERE djh
=t1.djh
and ywdjh
=t1.djh
and FMMC
LIKE '%贴金费%'),
0)
as TJF
,nvl((SELECT sum(ssje)
FROM fyb_mx
WHERE djh
=t1.djh
and ywdjh
=t1.djh
and FMMC
LIKE '%描金费%'),
0)
as MJF
,nvl((SELECT sum(ssje)
FROM fyb_mx
WHERE djh
=t1.djh
and ywdjh
=t1.djh
and (FMMC
LIKE '%杂费%' OR FMMC
LIKE '%其他费用%')),
0)
as ZF
,nvl((SELECT sum(ssje)
FROM fyb_mx
WHERE djh
=t1.djh
and ywdjh
=t1.djh
and FMMC
LIKE '%换碑费%' ),
0)
as hbf
,(SELECT STAZBZ
from azfs
WHERE azfsdm
=t1.azfsdm )
AS STAZ
,(SELECT LTAZBZ
from azfs
WHERE azfsdm
=t1.azfsdm )
AS LTAZ
FROM ywdjb t1
WHERE t1.zxflag
<> '1' and t1. mwflmc
not like '%CU%' AND t1.azfsdm
= '01'
and (t1.djh,t1.djh)
in (
select djh,ywdjh
from fyb_mx t1
where (FMMC
LIKE '%其他费用%' or FMMC
LIKE '%头像费%' or FMMC
LIKE '%安葬费%' or FMMC
LIKE '%刻字费%' or FMMC
LIKE '%贴金费%' or FMMC
LIKE '%描金费%' or FMMC
LIKE '%杂费%' OR FMMC
LIKE'%换碑费%')
and to_char(t1.sfrq,
'yyyy-mm-dd')
>=''||beginTime
||'' AND to_char (t1.sfrq,
'yyyy-mm-dd')
<=''||endTime
||'' and zxflag
<>'1' and azfsdm
='01' )
UNION ALL
-- 其他业务表中办理
SELECT t1.djh, ywdjh
as ywdjh ,t1.dabh,(
SELECT sfrq
FROM fyb_mx
WHERE djh
=t1.djh
and ywdjh
=t1.ywdjh
and rownum
=1)
AS BLRQ,t1.gxrxm
AS BLRXM,t1.mwflmc
||t1.mwmc
as MW,t1.mxmc
,(SELECT SCMXTJFL1
FROM spmldm
WHERE spmldm
=t1.mxdm)
as MXTJFL
,nvl((SELECT sum(ssje)
FROM fyb_mx
WHERE djh
=t1.djh
and ywdjh
=t1.ywdjh
and (FMMC
LIKE '%其他费用%' or FMMC
LIKE '%头像费%' or FMMC
LIKE '%安葬费%' or FMMC
LIKE '%刻字费%' or FMMC
LIKE '%贴金费%' or FMMC
LIKE '%描金费%' or FMMC
LIKE '%杂费%' OR FMMC
LIKE'%换碑费%')),
0)
AS JE,
t1.czyxm AS JBR
,nvl((SELECT sum(ssje)
FROM fyb_mx
WHERE djh
=t1.djh
and ywdjh
=t1.ywdjh
and FMMC
LIKE '%头像费%' ),
0)
as TXF
,nvl((SELECT sum(ssje)
FROM fyb_mx
WHERE djh
=t1.djh
and ywdjh
=t1.ywdjh
and FMMC
LIKE '%安葬费%' ),
0)
as AZF
,nvl((SELECT sum(ssje)
FROM fyb_mx
WHERE djh
=t1.djh
and ywdjh
=t1.ywdjh
and FMMC
LIKE '%刻字费%'),
0)
as KZF
,nvl((SELECT sum(ssje)
FROM fyb_mx
WHERE djh
=t1.djh
and ywdjh
=t1.ywdjh
and FMMC
LIKE '%贴金费%' ),
0)
as TJF
,nvl((SELECT sum(ssje)
FROM fyb_mx
WHERE djh
=t1.djh
and ywdjh
=t1.ywdjh
and FMMC
LIKE '%描金费%' ),
0)
as MJF
,nvl((SELECT sum(ssje)
FROM fyb_mx
WHERE djh
=t1.djh
and ywdjh
=t1.ywdjh
and (FMMC
LIKE '%杂费%' OR FMMC
LIKE '%其他费用%') ),
0)
as ZF
,nvl((SELECT sum(ssje)
FROM fyb_mx
WHERE djh
=t1.djh
and ywdjh
=t1.ywdjh
and FMMC
LIKE '%换碑费%' ),
0)
as hbf
,(SELECT STAZBZ
from azfs
WHERE azfsdm
=t1.azfsdm )
AS STAZ
,(SELECT LTAZBZ
from azfs
WHERE azfsdm
=t1.azfsdm )
AS LTAZ
FROM qtywb t1
WHERE t1.zxflag
<> '1' and t1. mwflmc
not like '%CU%' AND t1.azfsdm
= '01' and ywdm
='11'
and (djh,ywdjh)
in (
select djh,ywdjh
from fyb_mx t1
where (FMMC
LIKE '%其他费用%' or FMMC
LIKE '%头像费%' or FMMC
LIKE '%安葬费%' or FMMC
LIKE '%刻字费%' or FMMC
LIKE '%贴金费%' or FMMC
LIKE '%描金费%' or FMMC
LIKE '%杂费%' OR FMMC
LIKE'%换碑费%')
and to_char(t1.sfrq,
'yyyy-mm-dd')
>=''||beginTime
||'' AND to_char (t1.sfrq,
'yyyy-mm-dd')
<=''||endTime
||'' and zxflag
<>'1' and azfsdm
='01' )
;
RETURN;
end;
commit;
--异常处理--
EXCEPTION
WHEN NO_DATA_FOUND
THEN
RAISE_APPLICATION_ERROR(-20011,
'存储过程发生了异常！');
ROLLBACK;
end PROC_TZ_QUERY_DETAIL;
/
转载于:https://www.cnblogs.com/siyunianhua/p/6198765.html
