-- Start of DDL Script for View SD_HOSPITAL.VW_XT_YPMBMX_MZDZBL
-- Generated 25-ʮһÔÂ-2020 11:05:07 from SD_HOSPITAL@30

CREATE OR REPLACE VIEW vw_xt_ypmbmx_mzdzbl (
   mxid00,
   mbid00,
   ypnm00,
   jl0000,
   ypzsl0,
   jldw00,
   zsl000,
   zldw00,
   lsdj00,
   zhl000,
   ypmc00,
   ypgg00,
   lbbh00,
   z2j000,
   m2j000,
   k2j000,
   zyfydw,
   mzfydw,
   kcdw00,
   yysj00,
   yysjdw,
   pcid00,
   zqcs00,
   pcmc00,
   zqcd00,
   zqdw00,
   yfid00,
   yfmc00,
   xmyfid,
   yyzf00,
   xmyfmc,
   zb0000,
   zbbh00,
   znxh00,
   clbz00,
   yplb00,
   ts0000,
   yflbid,
   mrqssj,
   qzfs00,
   yptym0,
   dmbz00,
   mbypmc,
   ypczfl,
   f2j000,
   mzkfdw,
   zjjl2j,
   zjjldw,
   zhcxbh,
   yzlx00 )
AS
SELECT
DISTINCT
A.MXID00, A.MBID00, B.YPNM00,
       A.JL0000, A.YPZSL0, B.JLDW00,
       A.YPZSL0/DECODE(nvl(a.zldw00,b.mzfydw),b.zyfydw,b.z2j000,b.mzfydw,b.m2j000,b.zjjldw,b.zjjl2j,b.mzkfdw,b.f2j000,b.k2j000)ZSL000,
       nvl(a.zldw00,b.mzfydw),
       --ROUND(B.LSJ000/K2J000*DECODE(nvl(a.zldw00,b.mzfydw),b.zyfydw,b.z2j000,b.mzfydw,b.m2j000,b.zjjldw,b.zjjl2j,b.mzkfdw,b.f2j000,b.k2j000)/b.nsbl00,2) LSDJ00,
       decode((select nvl(trim(VALUE0),'Y') from XT_XTCS00 where NAME00='YF_SFABYFFYDWFY'),'Y',to_char(round(F.LSDJ00/(B.K2J000*B.NSBL00)*F.F2J000*1.00,2),'FM99999990.00'),to_char(round(F.LSDJ00/(B.K2J000*B.NSBL00)*B.M2J000*1.00,2),'FM99999990.00')) LSDJ00,
       DECODE(nvl(a.zldw00,b.mzfydw),b.zyfydw,b.z2j000,b.mzfydw,b.m2j000,b.zjjldw,b.zjjl2j,b.mzkfdw,b.f2j000,b.k2j000)ZHL000,
       B.YPMC00, B.YPGG00, B.LBBH00,
       B.Z2J000, B.M2J000, B.K2J000,
       B.ZYFYDW, B.MZFYDW, B.KCDW00,
       A.YYSJ00,
       DECODE(SIGN(D.ZQCD00-1),1,'*'||TO_CHAR(D.ZQCD00)||D.ZQDW00,D.ZQDW00)YYSJDW,
       A.PCID00,D.ZQCS00,D.PCMC00, D.ZQCD00,D.ZQDW00,
       A.YFID00,
       E.YPYFMC YFMC00,
       C.YFID00 XMYFID,
       A.YYZF00,
       A.XMYFMC,
       A.ZB0000,
       A.ZBBH00,
       A.ZNXH00,
       A.CLBZ00,
       A.YPLB00,
       A.TS0000,
       E.YFLBID,
       E.MRQSSJ,
       B.QZFS00,
       B.YPTYM0,
       Decode(B.MZDJ00||B.DPDJ00||decode(B.SJDJ00,'0','0','1'),'000','0','1') DMBZ00,
       A.YPMC00,
       B.YPCZFL,
       B.F2J000,
       B.MZKFDW,
       B.ZJJL2J,
       B.ZJJLDW,
       A.ZHCXBH,
       A.yzlx00
FROM   XT_YPMBMX A,VW_ZS_YD0000 B,VW_BM_PC0000 D ,BM_YPYF00 C,BM_YPYF00 E,YF_YPKCXX F
WHERE  (A.YPNM00=B.YPNM00 or (select nvl(max(VALUE0),'N') from XT_XTCS00 where name00='YS_YYMBYPNMBCZGJMCTH')='Y' and A.YPMC00=B.YPMC00 and A.YPNM00 not in (select YPNM00 from BM_YD0000)) 
             AND A.PCID00=D.PCID00(+) AND A.XMYFMC=C.YPYFMC(+) AND A.YFID00=E.YFID00(+) and c.ypyfbz(+)='3'
             AND B.ypnm00=F.ypnm00
/


-- End of DDL Script for View SD_HOSPITAL.VW_XT_YPMBMX_MZDZBL