CREATE PROCEDURE SP_YF_SRPDQR(
P_YPPDPC       IN NUMBER,       --输入：药品盘点批次
P_YFBMBH       IN NUMBER,        --输入：药房部门编号
P_YGBH00       IN NUMBER         --输入：员工编号
)AS

V_COUNTER   NUMBER;
V_EXE_SQL   VARCHAR(1000);
V_SFDLSJ    XT_XTCS00.VALUE0%TYPE;           --药房零是否多售价管理
V_BMXZ00    BM_BMBM00.BMXZ00%TYPE;
V_YPPDPC    YF_YPPDMX.YPPDPC%TYPE;

E_NoEdit           EXCEPTION;
-- MODIFICATION HISTORY
-- Person      Date        Comments
-- JETHUA      2004.03.19  增加药房多零售价管理处理
--                          插入盘点西表用药房发药单位
-- JETHUA      2004.03.23  数量增加小数位数
-- JETHUA      2004.06.30  取消SQL构造,修改BUG
-- JETHUA      2004.07.01  零售单价统一保留零位小数,避免判断零售价错误
-- JETHUA      2004.11.15  药房可自定义盘点单位
-- JETHUA      2005.07.07  增加插入批发单价
-- JETHUA      2006.04.25  删除临时表，转到程序中
-- JETHUA      2006.04.26  修改BUG
-- JETHUA      2006.07.03  加行锁
-- csf         2006.11.22  修改bug:sjsl00>0--->sjsl00 is not null
-- CSF         2007.01.15  实际数量改为按最小单位（剂量单位）处理
-- CSF         2007.04.11  广东省中医院 增加 住院发药单位进行盘点
-- csf         2011.12.21  购进价调整成YF_YPKCXX.GJDJ00而作的相关修改。 YF-20111221-002
-- ---------   ----------  -------------------------------------------
BEGIN
  --取出药房零是否多售价管理参数  JETHUA      2004.03.18
  BEGIN
    SELECT Trim(VALUE0) INTO V_SFDLSJ FROM XT_XTCS00 WHERE NAME00='YF_SFDLSJGL';
  EXCEPTION
    WHEN OTHERS THEN
      V_SFDLSJ:='N';
  END;
  --取药房性质
  SELECT BMXZ00 INTO V_BMXZ00 FROM BM_BMBM00 WHERE BMBH00=P_YFBMBH;
  --加锁
  --加锁
  SELECT COUNT(*) INTO V_COUNTER FROM YF_YPPDMX WHERE YPPDPC=P_YPPDPC;
  IF V_COUNTER >0 THEN 
    SELECT YPPDPC INTO V_YPPDPC FROM  YF_YPPDMX WHERE YPPDPC=P_YPPDPC AND ROWNUM=1
    FOR UPDATE NOWAIT;
  ELSE
    V_YPPDPC:=P_YPPDPC;
  END IF;
   ---------功能：将盘点输入的数据(YF_YPPDMX_TEMP)导入到药品盘点明细表中(YF_YPPDMX)
  --修改已经存在的数据
  --构造语句
  V_EXE_SQL:='UPDATE YF_YPPDMX A SET SJSL00=(SELECT NVL(A.SJSL00,0)'
            ||'+ROUND(SUM(NVL(B.SJSL00,0)),3)'||'+ROUND(SUM(NVL(B.ZYFYSL,0)),3)';
  IF V_SFDLSJ='N' THEN
    V_EXE_SQL:=V_EXE_SQL||'FROM YF_YPPDMX_TEMP B WHERE  B.YPPDPC='||P_YPPDPC||
               ' AND A.YPNM00=B.YPNM00 AND SFQR00=0 AND (B.SJSL00 is not null OR B.ZYFYSL is not null) AND YGBH00='||P_YGBH00;
  ELSE --多零售价管理
    V_EXE_SQL:=V_EXE_SQL||'FROM YF_YPPDMX_TEMP B WHERE  B.YPPDPC='||P_YPPDPC||
               ' AND A.YPNM00=B.YPNM00 AND SFQR00=0 AND ROUND(A.PDDJ00,2)=ROUND(B.PDDJ00*A.ZHL000/B.ZHL000,2)'||
               ' AND ROUND(A.GJJ000,2)=ROUND(B.GJJ000*A.ZHL000/B.ZHL000,2) '||
               ' AND (B.SJSL00 is not null or b.ZYFYSL is not null) AND YGBH00='||P_YGBH00;
  END IF;
  V_EXE_SQL:=V_EXE_SQL||' )'|| --GROUP BY B.YPPDPC,B.YPNM00
  'WHERE A.YPPDPC='||P_YPPDPC||
    'AND EXISTS(SELECT 1 FROM YF_YPPDMX_TEMP C '||
                  'WHERE A.YPNM00=C.YPNM00 AND SFQR00=0 AND (C.SJSL00 is not null OR C.ZYFYSL IS NOT NULL) ';
  if V_SFDLSJ='Y' then
     V_EXE_SQL:=V_EXE_SQL||' AND ROUND(C.PDDJ00*A.ZHL000/C.ZHL000,2)=ROUND(A.PDDJ00,2) '||
                           'AND ROUND(A.GJJ000,2)=ROUND(C.GJJ000*A.ZHL000/C.ZHL000,2)';
  end if;
  
  V_EXE_SQL:=V_EXE_SQL||' AND C.YPPDPC='||P_YPPDPC||' AND YGBH00='||P_YGBH00||')';
  --执行语句
  SP_EXECUTE_SQL(V_EXE_SQL);

  --插入YF_YPPDMX中没有的数据
 IF V_SFDLSJ='N' THEN
   INSERT INTO YF_YPPDMX(
        YPPDPC, --药品盘点批次-->SQ_YF_YPPD00_YPPDPC
        YPNM00, --药品内码
        YPBM00, --药品编码，院内码
        YPMC00, --药品名称
        YPGG00, --规格
        PDDW00, --盘点单位
        PDDJ00, --盘点单价(盘点单位)
        ZHL000, --转换率(盘点单位/剂量单位)
        GJJ000, --购进价(盘点单位,最近一批)
        PFJ000,
        GJPJDJ, --购进平均价(盘点单位)
        SJSL00) --实际数量(盘点单位)
    SELECT YPPDPC,YPNM00,YPBM00,YPMC00,YPGG00,PDDW00,PDDJ00,
            ZHL000,GJJ000,PFJ000,GJPJDJ,SUM(SJSL00) FROM
    (SELECT
        A.YPPDPC, --药品盘点批次-->SQ_YF_YPPD00_YPPDPC
        A.YPNM00, --药品内码
        A.YPBM00, --药品编码，院内码
        A.YPMC00, --药品名称
        A.YPGG00, --规格
        X.PDDW00,
        ROUND(A.PDDJ00*X.ZHL000/A.ZHL000,4) PDDJ00,
        X.ZHL000,
        ROUND(A.GJJ000*A.ZHL000/D.K2J000,2) GJJ000, --购进价(盘点单位,最近一批)
        ROUND(D.PFJ000*X.ZHL000/D.K2J000,2) PFJ000, --批发价
        ROUND(D.GJPJDJ*X.ZHL000/D.K2J000,2) GJPJDJ, --购进平均价(盘点单位)
        NVL(A.SJSL00,0)+NVL(A.ZYFYSL,0) SJSL00
   FROM YF_YPPDMX_TEMP A,BM_YD0000 D,YF_YPKCXX X         
   WHERE A.YPPDPC=P_YPPDPC AND A.YPNM00=D.YPNM00 AND A.YPNM00=X.YPNM00 AND X.YFBMBH=P_YFBMBH
    AND A.YGBH00=P_YGBH00 AND (A.SJSL00 is not null  OR A.ZYFYSL IS NOT NULL) AND A.SFQR00='0'
    AND NOT EXISTS(SELECT 1 FROM YF_YPPDMX B
                    WHERE A.YPNM00=B.YPNM00 AND B.YPPDPC=P_YPPDPC))
   GROUP BY YPPDPC,YPNM00,YPBM00,YPMC00,YPGG00,PDDW00,PDDJ00,
       ZHL000,GJJ000,PFJ000,GJPJDJ;
  ELSE --多零售价管理
    INSERT INTO YF_YPPDMX(
        YPPDPC, --药品盘点批次-->SQ_YF_YPPD00_YPPDPC
        YPNM00, --药品内码
        YPBM00, --药品编码，院内码
        YPMC00, --药品名称
        YPGG00, --规格
        PDDW00, --盘点单位
        PDDJ00, --盘点单价(盘点单位)
        ZHL000, --转换率(盘点单位/剂量单位)
        GJJ000, --购进价(盘点单位,最近一批)
        PFJ000,
        GJPJDJ, --购进平均价(盘点单位)
        SJSL00) --实际数量(盘点单位)
    SELECT YPPDPC,YPNM00,YPBM00,YPMC00,YPGG00,PDDW00,PDDJ00,
            ZHL000,GJJ000,PFJ000,GJPJDJ,SUM(SJSL00) FROM
    (SELECT
        A.YPPDPC, --药品盘点批次-->SQ_YF_YPPD00_YPPDPC
        A.YPNM00, --药品内码
        A.YPBM00, --药品编码，院内码
        A.YPMC00, --药品名称
        A.YPGG00, --规格
        X.PDDW00,
        ROUND(A.PDDJ00*X.ZHL000/A.ZHL000,4) PDDJ00,
        X.ZHL000,
        ROUND(A.GJJ000*X.ZHL000/A.ZHL000,2) GJJ000, --购进价(盘点单位,最近一批)
        ROUND(D.PFJ000*X.ZHL000/D.K2J000,2) PFJ000, --批发价
        ROUND(D.GJPJDJ*X.ZHL000/D.K2J000,2) GJPJDJ, --购进平均价(盘点单位)
        NVL(A.SJSL00,0)+NVL(A.ZYFYSL,0) SJSL00
    FROM YF_YPPDMX_TEMP A,BM_YD0000 D,YF_YPKCXX X
    WHERE A.YPPDPC=P_YPPDPC AND A.YPNM00=D.YPNM00 AND A.YPNM00=X.YPNM00
	 AND A.YGBH00=P_YGBH00 AND X.YFBMBH=P_YFBMBH AND A.SFQR00='0'
     AND NOT EXISTS(SELECT 1 FROM YF_YPPDMX B
                    WHERE A.YPNM00=B.YPNM00 AND B.YPPDPC=P_YPPDPC
                    AND ROUND(B.PDDJ00,2)=ROUND(A.PDDJ00*B.ZHL000/A.ZHL000,2)
                    AND ROUND(B.GJJ000,2)=ROUND(A.GJJ000*B.ZHL000/A.ZHL000,2)))
    GROUP BY YPPDPC,YPNM00,YPBM00,YPMC00,YPGG00,PDDW00,PDDJ00,
       ZHL000,GJJ000,PFJ000,GJPJDJ;
  END IF;

  --修改盈亏数
  IF V_SFDLSJ='N' THEN
    UPDATE YF_YPPDMX A SET (ZMSL00,YKSL00)
      =(SELECT B.SJKCSL,A.SJSL00-B.SJKCSL
        FROM YF_YPKCXX B
        WHERE A.YPNM00=B.YPNM00 AND B.YFBMBH=P_YFBMBH)
    WHERE YPPDPC=P_YPPDPC ;
  ELSE
    UPDATE YF_YPPDMX A SET (ZMSL00,YKSL00)
         =(SELECT B.SJKCSL,A.SJSL00-B.SJKCSL
          FROM VW_YF_YPPCPD B
          WHERE A.YPNM00=B.YPNM00 AND B.YFBMBH=P_YFBMBH AND round(A.GJJ000,2)=round(B.GJJ000,2) and round(A.pddj00,2)=round(B.pddj00,2))
    WHERE YPPDPC=P_YPPDPC ;
  END IF;
  --为避免账面数为空，而无法计算盈亏数
  UPDATE YF_YPPDMX
     SET ZMSL00=NVL(ZMSL00,0),
         YKSL00=SJSL00-NVL(ZMSL00,0)
   WHERE YPPDPC=P_YPPDPC;

  UPDATE YF_YPPDMX A SET PDXH00=(SELECT B.PDXH00 FROM YF_YPKCXX B
                                 WHERE B.YFBMBH=P_YFBMBH AND A.YPNM00=B.YPNM00)
   WHERE A.YPPDPC=P_YPPDPC;
  --删除数量为空
  DELETE yf_yppdmx_temp
   WHERE SFQR00='0' AND SJSL00 IS NULL  AND YPPDPC=P_YPPDPC AND ZYFYSL IS NULL
     AND YGBH00=P_YGBH00;
  --修改标志
  UPDATE yf_yppdmx_temp SET SFQR00='1'
   WHERE YPPDPC=P_YPPDPC
     AND YGBH00=P_YGBH00;

  COMMIT;

EXCEPTION
  WHEN OTHERS THEN
    RAISE_APPLICATION_ERROR(-20283,Substr(Trim('盘点数据生成时发生意外错误!*返回信息:'||
            '调用过程: SP_YF_SRPDQR('||TO_CHAR(P_YPPDPC)||
            ','||TO_CHAR(P_YFBMBH)||'; '||SQLERRM),1,200));
    ROLLBACK;
END;
/