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;
/