CREATE OR REPLACE VIEW SD_HOSPITAL.VW_YF_MZXYCF_BZ
 (
 CFLSH0, CFID00, CFXH00, YPMC00, CFMX00, JE0000, XMYFMC, JXMC00, YPGG00, 
YPZSL0, YFYL00, LSDJ00, JLBZ00, PCMC00, PCMCSM, YYSJ00, YYSJDW, YFSMMC, 
YPYFMC, ZB0000, CSL000, PSMC00, BZ0000

 )
 AS 
SELECT x.cflsh0, x.cfid00, 0,
             TRIM (   DECODE (TRIM (zb0000), '', '  ', TRIM (zb0000) || '.')
                   || sf_yf_getypmc (x.ypnm00)
                  )
          || '/'
          || TRIM (y.ypgg00),
             RPAD (TRIM (y.ypgg00), 10, ' ')
          || ' '
          || LPAD ((   DECODE (SUBSTR (x.ypzsl0, 1, 1), '.', '0', '')
                    || x.ypzsl0
                    || TRIM (x.cfdw00)
                   ),
                   7,
                   ' '
                  )
          || ' '
          || TRIM (NVL (x.yfsmmc, ' '))
          || TRIM (NVL (x.ypyfmc, ' '))
          || ','
          || TRIM (DECODE (x.csl000,
                           NULL, '',
                           0, '',
                              DECODE (SUBSTR (ABS (x.csl000), 1, 1),
                                      '.', '0',
                                      ''
                                     )
                           || ABS (x.csl000)
                           || TRIM (x.jldw00)
                          )
                  )
          || ','
          || TRIM (x.pcmc00)
          || ','
          || DECODE (x.yysj00, NULL, '', x.yysj00 || TRIM (x.yysjdw)),
          TRIM (ROUND (ypzsl0 * lsdj00, 2)), TRIM (x.xmyfmc),
          (SELECT TRIM (jxmc00)
             FROM bm_ypjx00 a, bm_yd0000 b
            WHERE b.ypnm00 = x.ypnm00
              AND a.lbbh00 = b.lbbh00
              AND a.jxbh00 = b.jxbh00),
          TRIM (y.ypgg00),
          LPAD ((   DECODE (SUBSTR (x.ypzsl0, 1, 1), '.', '0', '')
                 || x.ypzsl0
                 || TRIM (x.cfdw00)
                ),
                7,
                ' '
               ),
             TRIM (NVL (x.yfsmmc, ' '))
          || TRIM (NVL (x.ypyfmc, ' '))
          || TRIM (DECODE (x.csl000,
                           NULL, '',
                           0, '',
                              DECODE (SUBSTR (ABS (x.csl000), 1, 1),
                                      '.', '0',
                                      ''
                                     )
                           || ABS (x.csl000)
                           || TRIM (x.jldw00)
                          )
                  )
          || '/次',
          LPAD (TRIM (TO_CHAR (ROUND (x.lsdj00, 2), '9999990.00')), 5, ' '),
          'Y', TRIM (pcmc00),
          (SELECT SUBSTR (TRIM (sm0000), 1, 4)
             FROM bm_pc0000
            WHERE TRIM (pcmc00) = TRIM (x.pcmc00) AND ROWNUM = 1),
          TO_CHAR (x.yysj00) || TRIM (x.yysjdw), TRIM (x.yysjdw),
          TRIM (x.yfsmmc), TRIM (x.ypyfmc), x.zb0000,
          DECODE (TRIM (jxbh00),
                  '9', '每次'
                   || RTRIM (REPLACE (decode(x.CSL000,0.5,'半',to_char(x.CSL000, 'FM9999990.999'))
                                      || ' ',
                                      '. ',
                                      ''
                                     )
                            )
                   || x.jldw00,
                  '19', '每次'
                   || RTRIM (REPLACE (decode(x.CSL000,0.5,'半',to_char(x.CSL000, 'FM9999990.999'))
                                      || ' ',
                                      '. ',
                                      ''
                                     )
                            )
                   || x.jldw00,
                  '16', '每次'
                   || RTRIM (REPLACE (decode(x.CSL000,0.5,'半',to_char(x.CSL000, 'FM9999990.999'))
                                      || ' ',
                                      '. ',
                                      ''
                                     )
                            )
                   || x.jldw00,
                  '7', '每次'
                   || RTRIM (REPLACE (decode(x.CSL000,0.5,'半',to_char(x.CSL000, 'FM9999990.999'))
                                      || ' ',
                                      '. ',
                                      ''
                                     )
                            )
                   || x.jldw00,
                  '17', '每次'
                   || RTRIM (REPLACE (decode(x.CSL000,0.5,'半',to_char(x.CSL000, 'FM9999990.999'))
                                      || ' ',
                                      '. ',
                                      ''
                                     )
                            )
                   || x.jldw00,
                  '11', '每次'
                   || RTRIM (REPLACE (decode(x.CSL000,0.5,'半',to_char(x.CSL000, 'FM9999990.999'))
                                      || ' ',
                                      '. ',
                                      ''
                                     )
                            )
                   || x.jldw00,
                  '14', '每次'
                   || RTRIM (REPLACE (decode(x.CSL000,0.5,'半',to_char(x.CSL000, 'FM9999990.999'))
                                      || ' ',
                                      '. ',
                                      ''
                                     )
                            )
                   || x.jldw00,

                  '44', '每次'
                   || RTRIM (REPLACE (decode(x.CSL000,0.5,'半',to_char(x.CSL000, 'FM9999990.999'))
                                      || ' ',
                                      '. ',
                                      ''
                                     )
                            )
                   || x.jldw00,
                  '47', '每次'
                   || RTRIM (REPLACE (decode(x.CSL000,0.5,'半',to_char(x.CSL000, 'FM9999990.999'))
                                      || ' ',
                                      '. ',
                                      ''
                                     )
                            )
                   || x.jldw00,
                  DECODE (sf_yf_getcfdgs0 (x.cflsh0, '0'),
                          '2', '每次'
                           || RTRIM (REPLACE (decode(x.CSL000/y.Z2J000,0.5,'半',to_char(x.CSL000/y.Z2J000, 'FM9999990.999'))
                                              || ' ',
                                              '. ',
                                              ''
                                             )
                                    )
                           || y.zyfydw,
                             '每次'
                          || RTRIM (REPLACE (decode(x.CSL000/y.Z2J000,0.5,'半',to_char(x.CSL000/y.Z2J000, 'FM9999990.999'))
                                             || ' ',
                                             '. ',
                                             ''
                                            )
                                   )
                          || y.zyfydw
                         )
                 ),
          (SELECT psmc00
             FROM bm_ps0000
            WHERE psid00 = x.pslbid AND yxbz00 = '1' AND ROWNUM = 1) psmc00,
           -- Z.BZ0000 BZ0000
          (select n.cfwzmc from yf_ypkcxx m,BM_YPCFWZ n where ypnm00=x.ypnm00 and m.cfwz00 =n.cfwzbh and yfbmbh=297 and n.bmbh00 =297 AND ROWNUM = 1) BZ0000
     FROM yf_mzcfmx x, bm_yd0000 y,yf_mzcf00 Z
    WHERE x.ypnm00 = y.ypnm00 AND Z.cflsh0 =X.cflsh0 AND x.sfzby0 = 'N'
/