Procdure for wanfo business report

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

 

posted @ 2016-12-19 18:58  韩梦芫  阅读(369)  评论(0编辑  收藏  举报