-- Start of DDL Script for View SD_HOSPITAL.VW_ZY_LJ_MBXX_ZYDZBL
-- Generated 2020-12-18 17:51:11 from SD_HOSPITAL@FW2

CREATE OR REPLACE VIEW vw_zy_lj_mbxx_zydzbl (
   ljid00,
   ljbm00,
   ljmc00,
   pysm00,
   zyts00,
   zyfy00,
   ssr000,
   sslx00,
   blfx00,
   ksmc00,
   ksbh00,
   ljms00,
   cjrqsj,
   xgrqsj,
   ljzt00,
   cjrbh0,
   cjrxm0,
   xgrbh0,
   xgrxm0,
   xgbz00,
   mblx00,
   mblxmc,
   zlmc00,
   zlml00,
   ver000,
   mints0,
   maxts0,
   rjfs00,
   sszd00,
   sszdms,
   bzlcwj,
   bzyzwj,
   bzhlwj,
   xjclwj,
   ljztmc,
   sfzlj0,
   zljid0,
   zljmc0,
   zdzxjd,
   sftsby,
   sfxsfl,
   zyfysx,
   bbyhsm,
   yyid00,
   qyrqsj,
   qyrxm0,
   zfrqsj,
   zfrxm0,
   ljlx00,
   ljlxmc )
AS
select 
  LJID00  ,     --路径的ID
  LJBM00  ,     --路径编码
  LJMC00  ,     --路径名称
  PYSM00  ,     --拼音首码
  ZYTS00  ,     --住院天数
  ZYFY00  ,     --住院费用
  SSR000  ,     --入院的第几天做手术
  SSLX00  ,     --手术日类型（无，手术，化疗，分娩）
  BLFX00  ,     --病例分型：A型，B型，C型，D型
  (select BMMC00 from BM_BMBM00 where BMBH00=a.KSBH00 and rownum <= 1) as KSMC00,
  nvl(KSBH00,0) KSBH00  ,     --使用科室
  LJMS00  ,     --路径描述
  substr(CJRQ00,1,4)||'.'||substr(CJRQ00,5,2)||'.'||substr(CJRQ00,7,2)||' '||CJSJ00 as CJRQSJ,   --制定日期 
  decode(XGRQ00,null,'', substr(XGRQ00,1,4)||'.'||substr(XGRQ00,5,2)||'.'||substr(XGRQ00,7,2)||' '||XGSJ00) as XGRQSJ,  --修改日期
  LJZT00 ,          --路径状态 '0'创建，'1' 启用，'2'删除
  CJRBH0 ,       --创建人编号
  (select ZWXM00 from BM_YGBM00 where YGBH00=a.CJRBH0 and rownum <= 1) as CJRXM0,
  XGRBH0 ,       --修改人编号
  (select ZWXM00 from BM_YGBM00 where YGBH00=a.XGRBH0 and rownum <= 1) as XGRXM0,
  0 as XGBZ00,
  MBLX00,
  (select LXMC00 from VW_LJ_LJLX00 where MBLX00=a.MBLX00 and rownum <= 1) MBLXMC,
  a.ZLMC00,
  a.ZLML00,
  VER000  ,     --版本号
  MINTS0  ,     --最小住院天数
  MAXTS0  ,      --最大住院天数
  RJFS00  ,      --0 手动选择 1自动选择
  SSZD00  ,       --手术诊断
  SSZDMS  ,      --手术诊断 
  TO_NCHAR(BZLCWJ)  ,      --流程文件名
  BZYZWJ  ,     --医嘱文件名
  BZHLWJ  ,     --护理文件名 
  XJCLWJ  ,     --宣教材料文件名 
  decode(LJZT00,'0','创建中',1,'启用',2,'作废'),
  SFZLJ0  ,      --是否子路径
  ZLJID0  ,      --子路径ID
  SF_ZS_LJ_GETZLJMC(ZLJID0) as ZLJMC0,         --子路径名称  
  ZDZXJD ,  --自动转下阶段
  SFTSBY,
  SFXSFL,
  ZYFYSX,     --住院费用上限
  BBYHSM,
  (select YYID00 from BM_BMBM00 where BMBH00=a.KSBH00 and rownum <= 1) as YYID00, --医院id
  decode(QYRQ00,null,'',substr(QYRQ00,1,4)||'.'||substr(QYRQ00,5,2)||'.'||substr(QYRQ00,7,2)||' '||QYSJ00) as QYRQSJ,   --启用日期 
  (select ZWXM00 from BM_YGBM00 where YGBH00=a.QYCZY0 and rownum <= 1) as QYRXM0,
  
  decode(ZFRQ00,null,'', substr(ZFRQ00,1,4)||'.'||substr(ZFRQ00,5,2)||'.'||substr(ZFRQ00,7,2)||' '||ZFSJ00) as ZFRQSJ,  --作废日期
  (select ZWXM00 from BM_YGBM00 where YGBH00=a.ZFCZY0 and rownum <= 1) as ZFRXM0,
  a.LJLX00,
  decode(nvl(a.LJLX00,'0'),'0','西医路径','中医路径') LJLXMC
 from LJ_MB0000 A
/


-- End of DDL Script for View SD_HOSPITAL.VW_ZY_LJ_MBXX_ZYDZBL