CREATE OR REPLACE VIEW SD_HOSPITAL.VW_YS_BSCFMX_MZDZBL
 (
 CFLSH0, ZB0000, YPMCGG, CSL000, PCMC00, YYCXSJ, YPZSL0, XYYF00, ZJYFMC, 
XMYFMC, BZ0000, ZFFSBZ

 )
 AS 
SELECT
-- MODIFICATION HISTORY
-- Person      Date        Comments
-- ruanbh      2023.05.11  增加ZFFSBZ MZYSSJ5-20230505-001
  A.CFLSH0,A.ZB0000,TRIM(SUBSTR(SF_YS_GETYPMC(A.YPNM00)||DECODE(A.YPGG00,NULL,NULL,'['||TRIM(A.YPGG00)||']'), 1,80)) YPMCGG,
  DECODE(TO_CHAR(A.CSL000),'0','','','',DECODE(SUBSTR(TO_CHAR(A.CSL000),1,1),'.','0','')||TO_CHAR(A.CSL000)||TRIM(A.JLDW00)) CSL000,
  DECODE(C.PCMC00,'','',C.PCMC00) PCMC00,
  DECODE(TO_CHAR(A.YYSJ00),'0','','','',TO_CHAR(A.YYSJ00)||TRIM(A.YYSJDW)) YYCXSJ,
  DECODE(TO_CHAR(A.YPZSL0),'0',NULL,'',NULL,TO_CHAR(A.YPZSL0)||A.CFDW00) YPZSL0,
  DECODE(TO_CHAR(A.CSL000),'0','','','',DECODE(SUBSTR(TO_CHAR(A.CSL000),1,1),'.','0','')||TO_CHAR(A.CSL000)||TRIM(A.JLDW00))||
  DECODE(C.PCMC00,'','','/'||C.PCMC00)||DECODE(TO_CHAR(A.YYSJ00),'0','','','','/'||TO_CHAR(A.YYSJ00)||TRIM(A.YYSJDW))||
  DECODE(B.YPYFMC,'','','/'||B.YPYFMC) XYYF00,B.YPYFMC,a.XMYFMC,a.YYZF00,A.ZFFSBZ
FROM   ys_cfmx00 A,BM_YPYF00 B,BM_PC0000 C
WHERE a.YFID00=B.YFID00(+) and a.PCID00=c.PCID00(+)
union
SELECT
  A.CFLSH0,A.ZB0000,TRIM(SUBSTR(SF_YS_GETYPMC(A.YPNM00)||DECODE(A.YPGG00,NULL,NULL,'['||TRIM(A.YPGG00)||']'), 1,80)) YPMCGG,
  '' CSL000,
  '' PCMC00,
  '' YYCXSJ,
  DECODE(TO_CHAR(A.YPZSL0),'0',NULL,'',NULL,TO_CHAR(A.YPZSL0)||A.CFDW00) YPZSL0,
  '' XYYF00,'' ZJYFMC,'' XMYFMC,'诊疗项目带出处方' BZ0000,A.ZFFSBZ
FROM   YF_MZCFMX A
WHERE A.BZ0000='诊疗带出处方'
/