prompt No.037 SP_YF_GETDMCFBH       获取毒麻处方编号
create or replace procedure SP_YF_GETDMCFBH
(
  PMZZYBZ  in varchar2,                     --门诊住院标志，0门诊1住院
  PYFBMBH  in number ,                       --药房部门编号
  PDQKS00  in number ,                      --当前科室挂号科室
  PCFLSH0  in varchar2 default '',          --门诊处方流水
  PYPNM00  in number default null,          --住院用药品内码
  PYYRQ00  in varchar2 default '',          --住院用药日期
  Pdmcfbh  out varchar                      --毒麻处方编号
)
-- MODIFICATION HISTORY
-- Person      Date        Comments
-- luowf       2024.08.20  create YF13-20240821-001
-- luowf       2024.09.10  改成门诊住院分开，一个药房或特殊科室，一个药品一个月序列 YF13-20240910-002
-----------------------------------------------------------
as
  v_DMBMQZ   BM_YD0000.DMBMQZ%type;
  v_frsval   BM_TYZD00.BZ0000%type;
  v_type     number(1);
  v_sql      varchar2(300);
  v_dmbhsql  varchar2(100);
  v_bmbh00   BM_BMBM00.BMBH00%type;
  v_old      varchar2(50);
  v_new      varchar2(50);
  c_value0   number(5);
  v_date     varchar(2);
  v_mzzybz   varchar2(2);
  v_YPNM00   BM_YD0000.YPNM00%type;
begin
  --先分门诊住院去获取毒麻编码前缀，没获取到直接返回空字符串
  --获取科室前缀，没有的话就取获取药房前缀，都没有的话，返回空字符串
  --按门诊或住院+部门（药房或特殊科室）+药品编号（YPNM00）+月份生成 序列，后缀改成4位
  Pdmcfbh := ''; 
  if PMZZYBZ = '0' then
    --v_date := trim(to_char(sysdate,'DD'));
    v_date := trim(to_char(sysdate,'MM'));
    v_mzzybz := 'MZ';
    begin
      select (select DMBMQZ from BM_YD0000 where YPNM00=a.YPNM00),A.YPNM00 into v_DMBMQZ,v_YPNM00 from YF_MZCFMX a where a.CFLSH0=PCFLSH0 and rownum=1;
    exception
       when others then
        v_DMBMQZ:='';
        v_YPNM00:='';
    end;
  else
    --v_date := trim(to_char(to_date(PYYRQ00,'YYYYMMDD'),'DD'));
    v_date := trim(to_char(to_date(PYYRQ00,'YYYYMMDD'),'MM'));
     v_mzzybz := 'ZY';
    begin
      select DMBMQZ,YPNM00 into v_DMBMQZ,v_YPNM00 from BM_YD0000 b  where b.YPNM00=PYPNM00 and (nvl(b.MZDJ00,'0')<>'0' or nvl(b.SJDJ00,'0')='1')   ;                         
    exception
       when others then
        v_DMBMQZ:='';
        v_YPNM00:='';
    end;
  end if;
  if v_DMBMQZ is null then
    return;
  end if;
  v_type := 1;
  --按科室前缀
  begin
    v_bmbh00 := PDQKS00;
    select trim(a.BZ0000) into v_frsval from BM_TYZD00 a where a.BH0000=PDQKS00 and a.ZDMC00='毒麻处方编码规则科室前缀配置' and a.YXBZ00='1';
   exception
     when others then
       v_frsval:='';
       v_type := 0;
  end;
  --按药房前缀
  if v_frsval is null then 
     v_bmbh00 := PYFBMBH;
    begin
       select trim(a.BZ0000) into v_frsval from BM_TYZD00 a where a.BH0000=PYFBMBH and a.ZDMC00='毒麻处方编码规则药房前缀配置' and a.YXBZ00='1';
     exception
      when others then
         v_frsval:='';
    end;
  end if;
  if v_frsval is null then
   return;
  end if;
  begin
    v_dmbhsql:='SQ_YF_DMCF00_'||v_mzzybz||'_'||v_bmbh00||'_'||v_YPNM00||'_'||v_date;
    v_sql:='select '||trim(v_dmbhsql)||'.nextval  value0 from dual';
    execute immediate v_sql  into c_value0;
  exception
    when others then
      v_old:='SQ_YF_DMCF00_'||v_mzzybz||'_'||v_bmbh00||'_'||v_YPNM00||'_'||trim(to_char(sysdate-62,'MM'));
      execute immediate 'create sequence '||trim(v_dmbhsql)||' increment by 1 start with 2 minvalue 1 maxvalue 9999 nocycle order nocache';
      begin
        execute immediate 'drop sequence '||trim(v_old);
      exception  when others then
        c_value0:=1;
      end;
      c_value0:=1;
  end;
  if v_DMBMQZ is not null then
    Pdmcfbh:=v_frsval||v_DMBMQZ||trim(to_char(sysdate,'YYYYMMDD'))||trim(to_char(c_value0,'0000'));
  else
    Pdmcfbh:='';
  end if;
  insert into XT_PROLOG(ID0000,CZRQ00,CZSJ00,IP0000,CZY000,CZYKS0,DXMC00,CZNR00)
      values(SQ_XT_PROLOG_ID0000.nextval,to_char(sysdate,'YYYYMMDD'),to_char(sysdate,'HH24:MI:SS'),
             nvl(sys_context('userenv', 'ip_address'),'127.0.0.1'),
             0,nvl(v_bmbh00,0),'SP_YF_GETDMCFBH',PMZZYBZ||','||PYFBMBH||','||PDQKS00||','||nvl(PCFLSH0,'lsh')||','||nvl(PYPNM00,-1)||','||nvl(PYYRQ00,to_char(sysdate,'YYYYMMDD'))||','||Pdmcfbh);
  
exception
  when others then
    rollback;
    --RAISE_APPLICATION_ERROR(-20020, '生成毒麻处方编码数据错误!*SP_YF_GETDMCFBH('||PCFLSH0||PYZID00||','||PMZZYBZ||')');
    raise_application_error(-20002,substrb(nvl(sqlerrm, '生成毒麻处方编码数据错误')||'！*',1,240));
end;
/
show error;
--%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%