-- Start of DDL Script for Procedure SD_HOSPITAL.SP_ZY_LJ_LJMBTHTZYP_JOB
-- Generated 2016-06-13 17:19:54 from SD_HOSPITAL@fw2

CREATE OR REPLACE 
PROCEDURE sp_zy_lj_ljmbthtzyp_job(ad_LJID00 in number default 0
                                          )
as
  LS_BMBH00        varchar(50);
  BM_YD0000_ROW    BM_YD0000%rowtype;
  LS_COU           number(10);
  VS_CZNR00        varchar2(2000) ;
  ls_YPPZBH        BM_YD0000.YPPZBH%type;
  ls_LBBH00        BM_YD0000.LBBH00%type;
  ls_ypczfl        BM_YD0000.ypczfl%type;
-- MODIFICATION HISTORY
-- Person      Date        Comments
-- DSM         2013.04.10    中药按同名称同规格药品替换 create for  LCLJWH-20130304-001 
-- DSM         2013.06.19    增加停止西药同品种替换(模板，及在径病人未使用的医嘱),注意：判断的药房必须设置在CP_LJJYTZYPYF中 for LCLJWH-20130619-001
-- dsm         2013.07.10    现要求如果中药药品名称相同，就可以做替换 for LCLJWH-20130701-001
-- ---------   ----------  --------------------------------------------------    
begin
     --路径判断停止药品的药房
     begin
       select trim(VALUE0) into LS_BMBH00 from XT_XTCS00 where NAME00='CP_LJJYTZYPYF';
       LS_BMBH00:=','||LS_BMBH00||',';
     exception
       when others then
        LS_BMBH00:=',0,';   
     end; 
     for C_ERZYYP in (
        select distinct b.LJMC00,a.LJID00,a.RCID00,a.YPNM00,c.YPMC00,c.YPGG00,c.YPBM00,a.JHMXID,(select JDMC00 from LJ_MBZLJD where LJID00=a.LJID00
         and RCID00=a.RCID00) JDMC00 from LJ_JDYZMX a,LJ_MB0000 b,BM_YD0000 c where a.YZZT00='0' 
          and a.LJID00=b.LJID00 and a.YPNM00=c.YPNM00 and c.LBBH00 in(0,1,2)
          and (a.LJID00=ad_LJID00 or ad_LJID00=0)
          and b.LJZT00<>'2'
          and a.YPNM00>0
          and( exists(select 1 from BM_YD0000 where YPNM00=a.YPNM00 and  not TZRQ00 is null and TZRQ00<=to_char(sysdate,'YYYYMMDD') )      
               or  not exists(select 1 from YF_YPKCXX d,BM_BMBM00 e  where d.YPNM00=a.YPNM00 and d.ZTBZ00='0' and d.YFBMBH=e.BMBH00 
                 --and e.YYID00=LS_YYID00 
                  and (LS_BMBH00=',0,' or instr(LS_BMBH00,','||e.BMBH00||',')>0))
             )
          order by a.LJID00,RCID00,YPNM00
                     ) loop
         begin 
           ls_cou:=0; 
           select YPPZBH,LBBH00,ypczfl into ls_YPPZBH,ls_LBBH00,ls_ypczfl from BM_YD0000 where YPNM00=C_ERZYYP.YPNM00;
           if ls_LBBH00=2 then
             select * into BM_YD0000_ROW  from  BM_YD0000 a where YPMC00=C_ERZYYP.YPMC00 /*and YPGG00=C_ERZYYP.YPGG00*/ and 
             (TZRQ00 is null or TZRQ00>to_char(sysdate,'YYYYMMDD')) and a.ypczfl=ls_ypczfl
                    and exists(select 1 from YF_YPKCXX d,BM_BMBM00 e  where d.YPNM00=a.YPNM00 and d.ZTBZ00='0' and d.YFBMBH=e.BMBH00 
                    --and e.YYID00=LS_YYID00 
                    and (LS_BMBH00=',0,' or instr(LS_BMBH00,','||e.BMBH00||',')>0)) and rownum=1;
            ls_cou:=1;         
           elsif ls_YPPZBH is not null then 
             select * into BM_YD0000_ROW  from  BM_YD0000 a where YPPZBH=ls_YPPZBH and (TZRQ00 is null or TZRQ00>to_char(sysdate,'YYYYMMDD'))
                    and exists(select 1 from YF_YPKCXX d,BM_BMBM00 e  where d.YPNM00=a.YPNM00 and d.ZTBZ00='0' and d.YFBMBH=e.BMBH00 
                    --and e.YYID00=LS_YYID00 
                    and (LS_BMBH00=',0,' or instr(LS_BMBH00,','||e.BMBH00||',')>0)) and rownum=1;
             ls_cou:=1; 
           end if;         
                 
         exception 
            when others then
              ls_cou:=0;         
         end;         
         if ls_cou>0 then         
            VS_CZNR00:='路径:'||C_ERZYYP.LJMC00||' 阶段：'||C_ERZYYP.JDMC00||'.'||C_ERZYYP.YPMC00||'YPBM00:'||C_ERZYYP.YPBM00||'-->'||BM_YD0000_ROW.YPBM00;
            Insert Into XT_XTCZRZ(CZID00,CZTable,CZLX00,CZRYBM,CZNR00,DATAID,CZRQ00,CZSJ00)
            Values(SQ_XT_XTCZRZ_ID0000.NEXTVAL,'LJ_JDYZMX',1,0,VS_CZNR00,0,to_char(sysdate,'YYYYMMDD'),to_char(sysdate,'HH24:MI:SS'));
            if BM_YD0000_ROW.LBBH00 in(0,1) then
               update LJ_JDYZMX set YPNM00=BM_YD0000_ROW.YPNM00,MCSL00=0,YPGG00=BM_YD0000_ROW.YPGG00,JLDW00=BM_YD0000_ROW.JLDW00,ZLXMJC=BM_YD0000_ROW.YPMC00 where JHMXID=C_ERZYYP.JHMXID;    
            elsif BM_YD0000_ROW.LBBH00=2 then
               update LJ_JDYZMX set YPNM00=BM_YD0000_ROW.YPNM00,YPGG00=BM_YD0000_ROW.YPGG00 where JHMXID=C_ERZYYP.JHMXID;
            end if;
            update LJ_YZMX00 a set MCSL00=0,
                                YPNM00=BM_YD0000_ROW.YPNM00,
                                YPGG00=BM_YD0000_ROW.YPGG00,
                                JLDW00=BM_YD0000_ROW.JLDW00,
                                ZLXMJC=BM_YD0000_ROW.YPMC00 
                                where LJID00=C_ERZYYP.LJID00 and nvl(WBYZID,0)=0 and YPNM00=C_ERZYYP.YPNM00 and KSRQ00>=to_char(sysdate,'YYYYMMDD')
                                      and ZRZCID in(select ZRZCID from LJ_ZRZC00 where JSRQ00 is null and LJID00=a.LJID00);
            
         end if;
            
      end loop;   
      commit;
      
end;
/



-- End of DDL Script for Procedure SD_HOSPITAL.SP_ZY_LJ_LJMBTHTZYP_JOB