SELECT 'N' NX, NVL(C.TSYS00,0) TSYS00,nvl(C.SHBZ00,'N') SHBZ00 ,decode(nvl(C.SHBZ00,'N'),'Y','审核','M','退回','未审核') SHQK00,C.CFLSH0,C.GHH000,C.BRXM00,C.XB0000,C.BRID00,nvl(C.CFDGS0,'5') CFDGS0,
C.CSRQ00,C.YSXM00,M.BMMC00 AS GHKS00,C.ZYTS00,C.ZYDJS0,C.GHKS00 GHKSBM,
C.SRRQ00,C.JZRQ00,C.CFZJE0,C.CFZT00,C.YPDLBH,G.ZWXM00 AS LRY000,
substr(SF_XT_GETMZZD(C.GHH000),1,150) /*(SELECT Trim(ZDMC00) FROM YS_BRZDXX  WHERE GHH000=C.GHH000
 AND ZZDBZ0='3'  and rownum='1') */ as ZZDMC0,
(SELECT BRBLH0 FROM SF_BRXXB0 WHERE GHH000=C.GHH000 
 AND ROWNUM=1) BRBLH0,NVL(C.CFDGS0,'5') CFDGS0,C.BZ0000,c.fyrq00,c.fysj00,
 decode(substr(SF_PY_ZYPYLB(C.CFLSH0),1,10),'2','中草药','6','中草药','4','全成分','5','全成分','0','西药','1','成药') PYLBBH,
 decode(C.PSZT00,'1','需皮试','2','皮试未通过不能发药','3','皮试通过可发药','0','非皮试') PSJG00
 FROM YF_MZCF00 C,BM_BMBM00 M,BM_YGBM00 G
 WHERE
 C.BRID00=
 And C.SRRQ00 between '20250630' and '20250702' 
 AND C.YFBMBH=122
 AND ((C.CFZT00 = '1'
 AND C.JZRQ00>TO_CHAR(SYSDATE-1/3-30,'YYYYMMDD')
 AND NOT EXISTS (SELECT 1 FROM YF_MZCF00 D WHERE C.CFLSH0=D.BCCFH0 AND BRID00= AND CFZT00 in ('0','1')))
 OR (C.CFZT00 in ('0','4')
 AND C.SRRQ00>=TO_CHAR(SYSDATE-1/3-3,'YYYYMMDD')
 AND C.BCCFH0 IS NULL))
 and c.CFZJE0>=0
 AND C.GHKS00=M.BMBH00
 AND C.CZY000=G.YGBH00
 AND C.SFKSLY='Y'
 AND C.GHH000=''
 ORDER BY C.CFLSH0 DESC