CREATE OR REPLACE PROCEDURE SP_YF_DMYPZJHZ(
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_DMYPLX       in number default 0  --毒麻药品类型 0 全部 1 麻醉药品 2 精神药品 3 毒品药品
)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; --药房零售价是否独立管理
-- MODIFICATION HISTORY
-- Person      Date        Comments
-- JETHUA      2003.09.18  创建
-- JETHUA      2003.11.07  增加条件，提高效率
-- JETHUA      2004.03.26  数量增加小数位数
-- JETHUA      2004.06.02  增加期初异常判断
-- JETHUA      2005.02.24  根据医院要求 统计单位不同
-- JETHUA      2004.09.22  非多价管理，结存数以库存单位保存，故不必换算，多价管理以剂量单位保存，需换算
-- JETHUA      2004.10.26  出入累计不合理，会出现四舍五入错误
-- csf         2008.09.25  优化过程
-- csf         2010.04.21  南安医院比较特殊还是用门诊发药单位来统计
-- daihq       2014.04.08  增加入参P_DMYPLX毒麻药品类型。神经药品判断条件SJDJ00='1'改成SJDJ00<>'0',以保证和详细菜单统计一致 for YF-20140401-003
-- cx          2019.01.09  YF-20190119-001修正结束时间=YF_YPMXZ0.JZRQ00异常  
---------   ----------  -------------------------------------------
BEGIN
    --医院客户化  JETHUA      2005.02.24
  BEGIN
    SELECT Trim(VALUE0) INTO v_YYKHH0 FROM XT_XTCS00 WHERE NAME00='YF_ZJKFDDDYGS';
  EXCEPTION
    WHEN OTHERS THEN
      v_YYKHH0:='0';
  END;
    --取出药房零售价是否独立管理参数  JETHUA      2003.09.04
 /* BEGIN
    SELECT Trim(VALUE0) INTO V_LSJGL0 FROM XT_XTCS00 WHERE NAME00='YF_SFDLSJGL';
  EXCEPTION
    WHEN OTHERS THEN
      V_LSJGL0:='N';
  END;*/
      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
                      P_DMYPLX --毒麻药品类型
                      );
  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,           D.KCDW00,        D.K2J000,        0,
             ROUND(X.SJKCSL/D.K2J000,3),                  0,               0,                ROUND(X.SJKCSL/D.K2J000,3)
        FROM BM_YD0000 D,YF_YPKCXX X
       WHERE X.YFBMBH=P_YFBMBH AND X.YPNM00=D.YPNM00
        and  (((P_DMYPLX=0) and (D.MZDJ00<>'0' OR D.DPDJ00<>'0' OR D.SJDJ00<>'0'))
        or ((P_DMYPLX=1) and (D.MZDJ00<>'0'))
        or ((P_DMYPLX=2) and (D.SJDJ00<>'0'))
        or ((P_DMYPLX=3) and (D.DPDJ00<>'0')) );



    --计算结存 库存数字-查询截止时间到当前查询时间的近入库数
    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
       --YF-20190119-001  jzrq00||jzsj00>=P_jSRQ00||P_jSSJ00 改  jzrq00||jzsj00>P_jSRQ00||P_jSSJ00  
        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;



    --非多价管理，结存数以库存单位保存，故不必换算
   /* IF V_LSJGL0='N' THEN
      UPDATE YK_YPPDMX X
         SET X.ZMSL00=(SELECT NVL(JCSL00,0) FROM YF_YPMXZ0
                      WHERE MXZLSH IN (SELECT MAX(Z.MXZLSH)
                       FROM YF_YPMXZ0 Z WHERE Z.JZRQ00<= P_QSRQ00
                        AND Z.JZRQ00||Z.JZSJ00 <= P_QSRQ00||P_QSSJ00
                        AND Z.YFBMBH=P_YFBMBH AND Z.YPNM00=X.YPNM00))
       WHERE X.YPPDPC=V_YPPDPC;
     ELSE
       UPDATE YK_YPPDMX X
         SET X.ZMSL00=(SELECT NVL(ROUND(Z.JCSL00/D.K2J000,3),0) FROM YF_YPMXZ0 Z,BM_YD0000 D
                      WHERE Z.YPNM00=D.YPNM00 AND Z.MXZLSH IN (SELECT MAX(Z.MXZLSH)
                       FROM YF_YPMXZ0 Z WHERE Z.JZRQ00<= P_QSRQ00
                        AND Z.JZRQ00||Z.JZSJ00 <= P_QSRQ00||P_QSSJ00
                        AND Z.YFBMBH=P_YFBMBH AND Z.YPNM00=X.YPNM00))
       WHERE X.YPPDPC=V_YPPDPC;
     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;
/