CREATE OR REPLACE VIEW VW_JY_QDPZ00 AS
select
    YJDJH0,
    zlxmid,
    txm000,
    SF_JY_GET_ZJE000(YJDJH0,ZLXMID) ZJE000,
    ZYHGHH,
    MZZYBZ,
--  (select zlxmjc from bm_zlzd00 where zlxmid=a.zlxmid) zlxmjc,-- 项目名称
    DECODE(nvl((select trim(value0) from xt_xtcs00 where name00='JY_QDSJQZDZ'),'N'),'Y',sf_jy_union_xmmc00(txm000),(select zlxmjc from bm_zlzd00 where zlxmid=a.zlxmid)) zlxmjc,
    (select sjid00 from JY_QDPZ00 where zlxmid=a.zlxmid) sjid00,
    (select ddid00 from JY_QDPZ00 where zlxmid=a.zlxmid) ddid00,
--  SF_JY_GET_QDSJ00(a.jyywid,a.zlxmid) QDSJ00,
    DECODE(nvl((select trim(value0) from xt_xtcs00 where name00='JY_QDSJQZDZ'),'N'),'Y',SF_JY_GETQDSJ_MAX(txm000),'M',sf_jy_getqdsj_zhxyy(txm000),
    SF_JY_GET_QDSJ00(a.jyywid,a.zlxmid)) qdsj00,
    decode(nvl((select trim(value0) from xt_xtcs00 where name00='JY_JYJBSFXMZY'),'N'),'N',
    (select ddmc00 from JY_QDPZ00 b,JY_QDDD00 c where b.zlxmid=a.zlxmid and b.ddid00=c.ddid00),'Y',
     (select ddmc00 from JY_QDPZ00 b,JY_QDDD00 c where b.zlxmid=a.zlxmid and b.ddid00=c.ddid00 and a.zlxmid in (5405,4951,5424,3729,5329,5176,5407,5178,5311,5372,4956,4936,5371,5375)
     union
     select ddmc00 from JY_QDPZ00 b,JY_QDDD00 c where b.zlxmid=a.zlxmid and b.ddid00=c.ddid00 and a.zlxmid not in (3729,5329,5176,5407,5178,5424,5177,5175,4934,4935,4936) and a.zlxmid not in (select e.zlxmid from JY_TXMDY0 e,jy_txmlb0 f where e.txmqz0=f.txmqz0 and f.txmmc0='送检') and a.sqks00<>229
     union
     select ddmc00 from JY_QDPZ00 b,JY_QDDD00 c where b.zlxmid=a.zlxmid and b.ddid00=c.ddid00 and a.zlxmid  in (5177,5175,4934,4935,5371,5375) and a.sqks00<>229
     union
     select '导诊台自助取单机' from JY_QDPZ00 b,JY_QDDD00 c where b.zlxmid=a.zlxmid and b.ddid00=c.ddid00 and a.zlxmid not in (5424,3729,5329,5176,5407,5178,5311,5372,4956,4936) and a.zlxmid not in (select e.zlxmid from JY_TXMDY0 e,jy_txmlb0 f where e.txmqz0=f.txmqz0 and f.txmmc0='送检') and a.sqks00=229
     union
    select decode(a.sqks00,'229','三楼妇保科大厅','230','一楼门诊内科','导诊台自助取单机')  from jy_qdpz00 b  where a.zlxmid=b.zlxmid  and b.zlxmid in (select e.zlxmid from JY_TXMDY0 e,jy_txmlb0 f where e.txmqz0=f.txmqz0 and  f.txmmc0='送检') and b.zlxmid not in (5371,5375)
    )
    ) ddmc00,
    (SELECT QZMC00 FROM JY_QDXHQZ WHERE CJXQ00 = (SELECT to_char(to_date(B.BBCYRQ,'yyyymmdd'),'day') FROM JY_YW0000 B WHERE B.YJDJH0 = A.YJDJH0 AND ROWNUM = 1))
    QZMC00  --取单前缀
from JY_YW0000 a
where mzzybz = '0' and jgzt00='1'
;


create or replace function SF_JY_GETQDSJ_ZHXYY(PTXM000 in varchar2)
return varchar2 is
  Vqdsj00 varchar2(2000);   --取单时间
  v_jyywid jy_yw0000.jyywid%type;
  v_zlxmid jy_yw0000.zlxmid%type;
  v_lx0000 jy_qdsj00.lx0000%type;
  v_counter number(5);
--Person           Date         Comments
--xutz             2016.03.03   create for LIS-20160302-001;
--chenzh           政和县专用函数 
begin
  if PTXM000 is null then
    return null;
  end if;

  select count(*) into v_counter from jy_yw0000 where txm000= PTXM000;
  if v_counter=1 then
       select SF_JY_GET_QDSJ00(jyywid,zlxmid) into Vqdsj00 from jy_yw0000 where txm000= PTXM000;
  else
       select max(SF_JY_GET_QDSJ00(c.jyywid,c.zlxmid)) into Vqdsj00 from jy_yw0000 c,jy_qdpz00 a,jy_qdsj00 b
        where a.sjid00=b.sjid00 and a.zlxmid=c.zlxmid and  c.txm000=PTXM000 AND ROWNUM=1;
  end if;
  return trim(Vqdsj00);

end;