create or replace procedure SP_YF_DMYPZJHZ(
-- modification history
-- Person      Date        Comments
-- lintj       2012.07.25  modify
-- qks         2014.03.25  bug:前台显示出现小数,现全部转为最小剂量处理,增加入参P_JLSLCL
-- gzj         2019.03.25  增加入参P_DMLX00过滤统计类型 for YF9-20190322-001 
-- ---------   ----------  -------------------------------------------
P_YFBMBH       in number,         --输入：药房部门编号
P_QSRQ00       in VARchar2,       --起始日期
P_QSSJ00       in VARchar2,       --起始时间
P_JSRQ00       in VARchar2,       --结束日期
P_JSSJ00       in VARchar2,       --结束时间
P_YPPDPC       OUT number ,       --批次ID
P_JLSLCL       in varchar2 default 'N',    --是否转为剂量数量处理 
P_DMLX00       in varchar2 default '0'     --毒麻类型：0全部 1二类精神药品 2不含二类精神药品    
)as

V_DQRQ00    char(8);
V_DQSJ00    char(8);

V_YPNM00  BM_YD0000.YPNM00%type;
V_YPMC00  BM_YD0000.YPMC00%type;
V_YPGG00  BM_YD0000.YPGG00%type;
V_YPBM00  BM_YD0000.YPBM00%type;
V_KCDW00  BM_YD0000.KCDW00%type;
V_K2J000  BM_YD0000.K2J000%type;
V_JCSL00  BM_YD0000.KCSL00%type;
V_Z2J000  BM_YD0000.Z2J000%type;
V_MZFYDW  BM_YD0000.MZFYDW%type;

V_SLSL00  YF_YPMXZ0.SLSL00%type;
V_FCSL00  YF_YPMXZ0.FCSL00%type;
V_YPPDPC  YK_YPPDMX.YPPDPC%type;
v_YYKHH0  XT_XTCS00.VALUE0%type;
V_LSJGL0  XT_XTCS00.VALUE0%type; --药房零售价是否独立管理

begin
  --医院客户化 
  select nvl(max(trim(VALUE0)),'0') into v_YYKHH0 from XT_XTCS00 where NAME00='YF_ZJKFDDDYGS';

  select SQ_YK_YPPD00_YPPDPC.NEXTVAL into V_YPPDPC from dual;
  --变量赋值
  select to_char(sysdate,'yyyymmdd'),to_char(sysdate,'hh24:mi:ss') into V_DQRQ00,V_DQSJ00 from dual;

  --根据医院不同，设置单位:南安医院以门诊发药单位统计
  if v_YYKHH0='4' then
    SP_YF_DMYPZJHZ_NA(P_YFBMBH,         --输入：药房部门编号
                      P_QSRQ00,       --起始日期
                      P_QSSJ00,       --起始时间
                      P_JSRQ00,       --结束日期
                      P_JSSJ00,       --结束时间
                      P_YPPDPC--批次ID
                      );
  ELSE
    insert into YK_YPPDMX(
           YPPDPC ,    --药品盘点批次-->SQ_YK_YPPD00_YPPDPC
           YPNM00 ,    --药品内码
           YPBM00 ,    --药品编码，院内码
           YPMC00 ,    --药品名称
           YPGG00 ,    --规格
           PDDW00 ,    --盘点单位
           ZHL000 ,
           PDDJ00 ,
           ZMSL00 ,    --期初数
           GJJ000 ,    --本期增加
           GJPJDJ ,    --本期减少
           PFJ000)      --结存
    select V_YPPDPC,           X.YPNM00,        D.YPBM00,        D.YPMC00,
           D.YPGG00,           decode(P_JLSLCL,'Y',D.JLDW00,D.KCDW00),        decode(P_JLSLCL,'Y',1,D.K2J000),        0,
           ROUND(X.SJKCSL/decode(P_JLSLCL,'Y',1,D.K2J000),3),                  0,               0,                ROUND(X.SJKCSL/decode(P_JLSLCL,'Y',1,D.K2J000),3)
      from BM_YD0000 D,YF_YPKCXX X
     where X.YFBMBH=P_YFBMBH and X.YPNM00=D.YPNM00  
     and x.sjkcsl<>0 --不显示结存数为0的数据
       and ((P_DMLX00='0' and (D.MZDJ00<>'0' OR D.DPDJ00<>'0' OR D.SJDJ00<>'0')) or (P_DMLX00='1' and D.SJDJ00>'1') or (P_DMLX00='2' and (D.MZDJ00<>'0' OR D.DPDJ00<>'0' OR D.SJDJ00='1')));    
       
    --计算结存 库存数字-查询截止时间到当前查询时间的近入库数
    if  (p_jsrq00<=v_dqrq00)   and (p_jsrq00||p_jssj00<=v_dqrq00||v_dqsj00 ) then    
      update yk_yppdmx x 
      set x.pfj000=(select nvl(X.ZMSL00,0)+sum(nvl(ROUND(fcsl00*Z.ZHL000/X.ZHL000,3),0))-sum(nvl(ROUND(slsl00*Z.ZHL000/X.ZHL000,3),0))
      from yf_ypmxz0 Z 
      where jzrq00>=P_jsrq00 and jzrq00<=v_dqrq00
        and jzrq00||jzsj00>=P_jSRQ00||P_jSSJ00 and jzrq00||jzsj00<=v_dqrq00||v_dqsj00 
        and yfbmbh=p_yfbmbh and ypnm00=x.ypnm00 group by x.zmsl00) where yppdpc=v_yppdpc
        and exists (select 1 from yf_ypmxz0 k where jzrq00>=P_jsrq00 and jzrq00<=v_dqrq00
        and jzrq00||jzsj00>=P_jSRQ00||P_jSSJ00 and jzrq00||jzsj00<=v_dqrq00||v_dqsj00 
        and yfbmbh=p_yfbmbh and k.ypnm00=x.ypnm00);
    end if;

    --计算增减
    update YK_YPPDMX X
       SET (X.GJJ000,X.GJPJDJ)=(select SUM(ROUND(nvl(Z.SLSL00,0)*Z.ZHL000/x.zhl000,3)),
                                       SUM(ROUND(nvl(Z.FCSL00,0)*Z.ZHL000/x.zhl000,3))
                                  from YF_YPMXZ0 Z
                                 where Z.YFBMBH=P_YFBMBH and Z.YPNM00=X.YPNM00
                                   and JZRQ00 >= P_QSRQ00 and JZRQ00<= P_JSRQ00
                                   and JZRQ00||JZSJ00 >= P_QSRQ00||P_QSSJ00
                                   and JZRQ00||JZSJ00 <= P_JSRQ00||P_JSSJ00
                                   )
     where X.YPPDPC=V_YPPDPC and exists (
      select 1 from YF_YPMXZ0 K
                                 where K.YFBMBH=P_YFBMBH 
                                   and JZRQ00 >= P_QSRQ00 and JZRQ00<= P_JSRQ00
                                   and JZRQ00||JZSJ00 >= P_QSRQ00||P_QSSJ00
                                   and JZRQ00||JZSJ00 <= P_JSRQ00||P_JSSJ00
                                   and K.YPNM00=X.YPNM00);
   
    --计算期初
    
    update YK_YPPDMX
       SET PFJ000=nvl(PFJ000,0),
           ZMSL00=nvl(PFJ000,0)+nvl(GJPJDJ,0)-nvl(GJJ000,0),
           GJJ000=nvl(GJJ000,0),
           GJPJDJ=nvl(GJPJDJ,0)
     where YPPDPC=V_YPPDPC;  
    P_YPPDPC:=V_YPPDPC;
--    COMMIT;
  end if;
exception
  when others then
    raise_application_error(-20009, '查询数据时发生意外错误！*返回信息:'||
            '调用过程: SP_YF_DMYPZJHZ('||to_char(P_YFBMBH)||','||
             P_QSRQ00||','||P_QSSJ00||','||P_JSRQ00||','||P_JSSJ00||') ; '||sqlerrm);
    rollback;
end;