-- Start of DDL Script for View SD_HOSPITAL.VW_YK_YPPDCX_LB
-- Generated 4-七月-2013 16:19:05 from SD_HOSPITAL@ORCL

CREATE OR REPLACE VIEW vw_yk_yppdcx_lb (
   ykbmbh,
   yppdpc,
   jbrbh0,
   jbrxm0,
   qrrbh0,
   qrrxm0,
   pdrq00,
   pdsj00,
   qrrq00,
   qrsj00,
   sfqr00,
   pdlb00,
   lbbh00,
   ylbh00,
   jxbh00,
   ypnm00,
   ypbm00,
   ypmc00,
   ypgg00,
   pddw00,
   pddj00,
   zhl000,
   gjj000,
   gjpjdj,
   pfj000,
   zmsl00,
   zmje00,
   sjsl00,
   sjje00,
   yksl00,
   ykje00,
   pdxh00,
   bz0000,
   zmgjje,
   sjgjje,
   ykgjje,
   zmpfje,
   sjpfje,
   ykpfje,
   ypmlbz,
   mzfydw,
   m2j000 )
AS
SELECT
  P.YKBMBH,
  P.YPPDPC,
  P.JBRBH0,
  (select Y1.ZWXM00 from BM_YGBM00 Y1 where P.JBRBH0=Y1.YGBH00),
  P.QRRBH0,
  (select Y2.ZWXM00 from BM_YGBM00 Y2 where P.QRRBH0=Y2.YGBH00),
  P.PDRQ00,
  P.PDSJ00,
  P.QRRQ00,
  P.QRSJ00,
  P.SFQR00,
  P.PDLB00,
  P.LBBH00,
  P.YLBH00,
  P.JXBH00,
  X.YPNM00,
  X.YPBM00,
  X.YPMC00,
  X.YPGG00,
  X.PDDW00,
  X.PDDJ00,
  X.ZHL000,
  X.GJJ000,
  X.GJPJDJ,
  X.PFJ000,
  ROUND(X.ZMSL00*X.ZHL000/D.K2J000,3),
  decode((select trim(VALUE0) from xt_xtcs00 where name00='YK_SFPFJPD'),'Y',
          ROUND(ROUND(X.ZMSL00*X.ZHL000/D.K2J000,3)*X.PFJ000,2),
          ROUND(ROUND(X.ZMSL00*X.ZHL000/D.K2J000,3)*X.PDDJ00,2)),
  ROUND(X.SJSL00*X.ZHL000/D.K2J000,3),
  decode((select trim(VALUE0) from xt_xtcs00 where name00='YK_SFPFJPD'),'Y',
          ROUND(ROUND(X.SJSL00*X.ZHL000/D.K2J000,3)*X.PFJ000,2),
          ROUND(ROUND(X.SJSL00*X.ZHL000/D.K2J000,3)*X.PDDJ00,2)),
  ROUND(X.YKSL00*X.ZHL000/D.K2J000,3),
  decode((select trim(VALUE0) from xt_xtcs00 where name00='YK_SFPFJPD'),'Y',
          ROUND(ROUND(X.YKSL00*X.ZHL000/D.K2J000,3)*X.PFJ000,2),
          ROUND(ROUND(X.YKSL00*X.ZHL000/D.K2J000,3)*X.PDDJ00,2)),
  X.PDXH00,
  X.BZ0000,
  ROUND(ROUND(X.ZMSL00*X.ZHL000/D.K2J000,3)*X.GJJ000,2),
  ROUND(ROUND(X.SJSL00*X.ZHL000/D.K2J000,3)*X.GJJ000,2),
  ROUND(ROUND(X.YKSL00*X.ZHL000/D.K2J000,3)*X.GJJ000,2),
  ROUND(ROUND(X.ZMSL00*X.ZHL000/D.K2J000,3)*X.PFJ000,2),
  ROUND(ROUND(X.SJSL00*X.ZHL000/D.K2J000,3)*X.PFJ000,2),
  ROUND(ROUND(X.YKSL00*X.ZHL000/D.K2J000,3)*X.PFJ000,2),
  D.YPMLBZ,d.mzfydw,d.m2j000
FROM YK_YPPD00 P,YK_YPPDMX X,BM_YD0000 D,BM_YPLB00 E
WHERE P.YPPDPC=X.YPPDPC AND P.SFQR00='Y'
  AND X.YPNM00=D.YPNM00
  AND D.LBBH00=E.LBBH00
UNION ALL--类别合计
 SELECT
  0,--P.YKBMBH,
  P.YPPDPC,
  0,--P.JBRBH0,
  ' ',--(select Y1.ZWXM00 from BM_YGBM00 Y1 where P.JBRBH0=Y1.YGBH00),
  0,--P.QRRBH0,
  ' ',--(select Y2.ZWXM00 from BM_YGBM00 Y2 where P.QRRBH0=Y2.YGBH00),
  ' ',--P.PDRQ00,
  ' ',--P.PDSJ00,
  ' ',--P.QRRQ00,
  ' ',--P.QRSJ00,
  ' ',--P.SFQR00,
  ' ',--P.PDLB00,
  ' ',--P.YLBH00,
  ' ',--P.YLBH00,
  ' ',--P.JXBH00,
    0,--X.YPNM00,
  '类别合计：',--X.YPBM00,
  E.LBMC00,--X.YPMC00,
  ' ',--X.YPGG00,
  ' ',--X.PDDW00,
    0,--X.PDDJ00,
    0,--X.ZHL000,
    0,--X.GJJ000,
    0,--X.GJPJDJ,
    0,--X.PFJ000,
  0,--ROUND(X.ZMSL00*X.ZHL000/D.K2J000,3),
  ROUND(sum(decode((select trim(VALUE0) from xt_xtcs00 where name00='YK_SFPFJPD'),'Y',
          ROUND(ROUND(X.ZMSL00*X.ZHL000/D.K2J000,3)*X.PFJ000,2),
          ROUND(ROUND(X.ZMSL00*X.ZHL000/D.K2J000,3)*X.PDDJ00,2))),2),
  0,--ROUND(X.SJSL00*X.ZHL000/D.K2J000,3),
  ROUND(sum(decode((select trim(VALUE0) from xt_xtcs00 where name00='YK_SFPFJPD'),'Y',
          ROUND(ROUND(X.SJSL00*X.ZHL000/D.K2J000,3)*X.PFJ000,2),
          ROUND(ROUND(X.SJSL00*X.ZHL000/D.K2J000,3)*X.PDDJ00,2))),2),
  0,--ROUND(X.YKSL00*X.ZHL000/D.K2J000,3),
  ROUND(sum(decode((select trim(VALUE0) from xt_xtcs00 where name00='YK_SFPFJPD'),'Y',
          ROUND(ROUND(X.YKSL00*X.ZHL000/D.K2J000,3)*X.PFJ000,2),
          ROUND(ROUND(X.YKSL00*X.ZHL000/D.K2J000,3)*X.PDDJ00,2))),2),
  ' ',--X.PDXH00,
  ' ',--X.BZ0000
  ROUND(sum(ROUND(ROUND(X.ZMSL00*X.ZHL000/D.K2J000,3)*X.GJJ000,2)),2),
  ROUND(sum(ROUND(ROUND(X.SJSL00*X.ZHL000/D.K2J000,3)*X.GJJ000,2)),2),
  ROUND(sum(ROUND(ROUND(X.YKSL00*X.ZHL000/D.K2J000,3)*X.GJJ000,2)),2),
  ROUND(sum(ROUND(ROUND(X.ZMSL00*X.ZHL000/D.K2J000,3)*X.PFJ000,2)),2),
  ROUND(sum(ROUND(ROUND(X.SJSL00*X.ZHL000/D.K2J000,3)*X.PFJ000,2)),2),
  ROUND(sum(ROUND(ROUND(X.YKSL00*X.ZHL000/D.K2J000,3)*X.PFJ000,2)),2),
  ' ' , --D.YPMLBZ,
  ' ',0   
FROM YK_YPPD00 P,YK_YPPDMX X,BM_YD0000 D,BM_YPLB00 E
WHERE P.YPPDPC=X.YPPDPC AND P.SFQR00='Y'
  AND X.YPNM00=D.YPNM00
  AND D.LBBH00=E.LBBH00
GROUP BY P.YPPDPC,E.LBMC00 --,D.YPMLBZ
UNION ALL--总合计
 SELECT
  0,--P.YKBMBH,
  P.YPPDPC,
  0,--P.JBRBH0,
  ' ',--(select Y1.ZWXM00 from BM_YGBM00 Y1 where P.JBRBH0=Y1.YGBH00),
  0,--P.QRRBH0,
  ' ',--(select Y2.ZWXM00 from BM_YGBM00 Y2 where P.QRRBH0=Y2.YGBH00),
  ' ',--P.PDRQ00,
  ' ',--P.PDSJ00,
  ' ',--P.QRRQ00,
  ' ',--P.QRSJ00,
  ' ',--P.SFQR00,
  ' ',--P.PDLB00,
  ' ',--P.YLBH00,
  ' ',--P.YLBH00,
  ' ',--P.JXBH00,
    0,--X.YPNM00,
  '总 合 计：',--X.YPBM00,
  ' ',--X.YPMC00,
  ' ',--X.YPGG00,
  ' ',--X.PDDW00,
    0,--X.PDDJ00,
    0,--X.ZHL000,
    0,--X.GJJ000,
    0,--X.GJPJDJ,
    0,--X.PFJ000,
  0,--ROUND(X.ZMSL00*X.ZHL000/D.K2J000,3),
  ROUND(sum(decode((select trim(VALUE0) from xt_xtcs00 where name00='YK_SFPFJPD'),'Y',
          ROUND(ROUND(X.ZMSL00*X.ZHL000/D.K2J000,3)*X.PFJ000,2),
          ROUND(ROUND(X.ZMSL00*X.ZHL000/D.K2J000,3)*X.PDDJ00,2))),2),
  0,--ROUND(X.SJSL00*X.ZHL000/D.K2J000,3),
  ROUND(sum(decode((select trim(VALUE0) from xt_xtcs00 where name00='YK_SFPFJPD'),'Y',
          ROUND(ROUND(X.SJSL00*X.ZHL000/D.K2J000,3)*X.PFJ000,2),
          ROUND(ROUND(X.SJSL00*X.ZHL000/D.K2J000,3)*X.PDDJ00,2))),2),
  0,--ROUND(X.YKSL00*X.ZHL000/D.K2J000,3),
  ROUND(sum(decode((select trim(VALUE0) from xt_xtcs00 where name00='YK_SFPFJPD'),'Y',
          ROUND(ROUND(X.YKSL00*X.ZHL000/D.K2J000,3)*X.PFJ000,2),
          ROUND(ROUND(X.YKSL00*X.ZHL000/D.K2J000,3)*X.PDDJ00,2))),2),
  ' ',--X.PDXH00,
  ' ',--X.BZ0000
  ROUND(sum(ROUND(ROUND(X.ZMSL00*X.ZHL000/D.K2J000,3)*X.GJJ000,2)),2),
  ROUND(sum(ROUND(ROUND(X.SJSL00*X.ZHL000/D.K2J000,3)*X.GJJ000,2)),2),
  ROUND(sum(ROUND(ROUND(X.YKSL00*X.ZHL000/D.K2J000,3)*X.GJJ000,2)),2),
  ROUND(sum(ROUND(ROUND(X.ZMSL00*X.ZHL000/D.K2J000,3)*X.PFJ000,2)),2),
  ROUND(sum(ROUND(ROUND(X.SJSL00*X.ZHL000/D.K2J000,3)*X.PFJ000,2)),2),
  ROUND(sum(ROUND(ROUND(X.YKSL00*X.ZHL000/D.K2J000,3)*X.PFJ000,2)),2),
  ' ', --D.YPMLBZ,
  ' ',0 
FROM YK_YPPD00 P,YK_YPPDMX X,BM_YD0000 D
WHERE P.YPPDPC=X.YPPDPC AND P.SFQR00='Y'
  AND X.YPNM00=D.YPNM00
GROUP BY P.YPPDPC --,D.YPMLBZ
/


-- End of DDL Script for View SD_HOSPITAL.VW_YK_YPPDCX_LB