create or replace view VW_ZY_XJJKPZ (
   JKDH00,JKRQ00,JKRQN0,JKRQY0,JKRQR0,SKRMC0,SKRZH0,SKKHYH,
   KXLY00,XJJKJE,XJJEDX,YWXXJE,QWXXJE,BWXXJE,SWXXJE,WXXJE0,
   QXXJE0,BXXJE0,SXXJE0,YXXJE0,JXXJE0,FXXJE0,YWDXJE,QWDXJE,
   BWDXJE,SWDXJE,WDXJE0,QDXJE0,BDXJE0,SDXJE0,YDXJE0,JDXJE0,
   FDXJE0,JKYXM0,QTXX00,SJXJJE 
   )
as
-- MODIFICATION HISTORY
-- Person      Date        Comments
--zhangyc     2017.08.15  created   by ZYSF-20170217-002
--zhangyc     2019.07.22  现金缴款未负数转为0 by ZYSF-20190610-001
select 
AA.JKDH00,                                       --交款单号
AA.JKRQ00,                                       --交款日期
substrb(AA.JKRQ00,1,4) as JKRQN0,                --交款日期_年
substrb(AA.JKRQ00,5,2) as JKRQY0,                --交款日期_月
substrb(AA.JKRQ00,7,2) as JKRQR0,                --交款日期_日
AA.SKRMC0,                                       --收款人名称
AA.SKRZH0,                                       --收款人账号
AA.SKKHYH,                                       --收款人开户银行
AA.KXLY00,                                       --款 项 来 源
AA.XJJKJE,                                       --现金交款金额
SF_ZY_MONEY(AA.XJJKJE)||'('||AA.XJJKJE||')',     --现金交款金额大写
substrb(SF_ZY_JKJEZH(AA.XJJKJE),1,1)  as YWXXJE, --亿位金额小写
substrb(SF_ZY_JKJEZH(AA.XJJKJE),2,1)  as QWXXJE, --千万位金额小写
substrb(SF_ZY_JKJEZH(AA.XJJKJE),2,1)  as BWXXJE, --百万位金额小写
substrb(SF_ZY_JKJEZH(AA.XJJKJE),4,1)  as SWXXJE, --十万位金额小写
substrb(SF_ZY_JKJEZH(AA.XJJKJE),5,1)  as WXXJE0, --万位金额小写
substrb(SF_ZY_JKJEZH(AA.XJJKJE),6,1)  as QXXJE0, --千位金额小写
substrb(SF_ZY_JKJEZH(AA.XJJKJE),7,1)  as BXXJE0, --百位金额小写
substrb(SF_ZY_JKJEZH(AA.XJJKJE),8,1)  as SXXJE0, --十位金额小写
substrb(SF_ZY_JKJEZH(AA.XJJKJE),9,1)  as YXXJE0, --个位金额小写
substrb(SF_ZY_JKJEZH(AA.XJJKJE),10,1) as JXXJE0, --角位金额小写
substrb(SF_ZY_JKJEZH(AA.XJJKJE),11,1) as FXXJE0, --分位金额小写
'零' as YWDXJE,                                  --亿位金额大写
'零' as QWDXJE,                                  --千万位金额大写
'零' as BWDXJE,                                  --百万位金额大写
substr(SP_BM_RMBDX0(AA.XJJKJE),1,1) as SWDXJE,   --十万位金额大写
substr(SP_BM_RMBDX0(AA.XJJKJE),2,1) as WDXJE0,   --万位金额大写
substr(SP_BM_RMBDX0(AA.XJJKJE),3,1) as QDXJE0,   --千位金额大写
substr(SP_BM_RMBDX0(AA.XJJKJE),4,1) as BDXJE0,   --百位金额大写
substr(SP_BM_RMBDX0(AA.XJJKJE),5,1) as SDXJE0,   --十位金额大写
substr(SP_BM_RMBDX0(AA.XJJKJE),6,1) as YDXJE0,   --个位金额大写
substr(SP_BM_RMBDX0(AA.XJJKJE),7,1) as JDXJE0,   --角位金额大写
substr(SP_BM_RMBDX0(AA.XJJKJE),8,1) as FDXJE0,   --分位金额大写
AA.JKYXM0,                                       --缴款员姓名
AA.QTXX00,                                       --其他信息
AA.SJXJJE                                        --实际现金金额
from(
select JKDH00,JKRQ00,SKRMC0,SKRZH0,SKKHYH,KXLY00,
  decode(sign(XJJKJE),-1,0,XJJKJE)XJJKJE,JKYXM0,QTXX00,XJJKJE as SJXJJE
from (
select 
A.JKDH00,
max(A.JKRQ00)JKRQ00,
'福建省福州结核病防治院' as SKRMC0,
'118310100100080281'     as SKRZH0,
'兴业银行福州江滨支行'   as SKKHYH,
'住院收入'               as KXLY00,
nvl((select  sum(B.XMJE00)  from  BM_JKKJMX B  where B.JKDH00 =A.JKDH00 and  BZ0000 = '1' and B.XMBH00='35'),0) XJJKJE,
max((select ZWXM00 from BM_YGBM00 where YGBH00=A.CZY000 and rownum=1)) as JKYXM0,
'' as QTXX00

from  BM_JKB000 A
where 1=1
and MZBZ00=1
--and A.JKDH00=70901
group by A.JKDH00
)
) AA
/
show error;

--%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%