CREATE OR REPLACE PROCEDURE SP_YF_XZYPYKKC AS 

  v_KHHCS0 XT_XTCS00.VALUE0%TYPE; --客户化参数:0默认;1莆二;2泉二;3省二;4南安;5七院;6铁路医院
  v_SFDJG0 XT_XTCS00.VALUE0%TYPE; --是否多价格管理
  V_DJGSFYK XT_XTCS00.VALUE0%TYPE; --单价格是否记帐预扣

-- MODIFICATION HISTORY 3.1
-- Person      Date        Comments
-- JETHUA      2004.07.22  增加门诊总量预扣
-- JETHUA      2005.03.30  增加报损单预扣
-- JETHUA      2005.11.28  增加调整批次预扣
-- CSF         2006.10.09  平潭医院与铁路医院的个性化参数一致，将其分开
-- YANGH       2007.08.15  老年医院要求已记帐而且未超过V_YKYXTS天的处方扣除预扣
-- YANGH       2007.08.17  当天已记帐处方，第二天平预扣情况下，会扣2次预扣（'YF_MZCFSLYKYXTS'>0）
-- YANGH       2007.10.22  处方中如果有成药药品的处方，处方状态必须修改成‘1’（成药处方）
-- zhr         2008.01.11  批次预扣库存应该和隶属药品的预扣库存算法一致,全部用视图VW_YF_YPYKSL
-- zhr         2008.09.05  优化过程 
-- CSF         2010.07.01  yf_ypkcxx.ykkcsl存在约束,那么，在处理数据时候如果ykkcsl<0应直接按0处理
-- CSF         2011.01.21  批次预扣库存算法跟yf_YPKCXX的预扣算法不一，应分开视图处理。
-- qks         2012.03.26  珠海中医院：如果有预扣未记帐处方，第2天自动恢复预扣库存。by MZSF-20120329-002
-- linzy       2012.05.27  珠海中医院：修正夜诊开的处方药品预扣数量大于实际库存数量
-- ---------   ----------  --------------------------------------------------
BEGIN
  --取出客户化参数:0默认;1莆二;2泉二;3省二;4南安;5七院;6铁路医院 JETHUA  2004.11.01
  BEGIN
    SELECT Trim(VALUE0) INTO v_KHHCS0 FROM XT_XTCS00 WHERE NAME00='YF_ZJKFDDDYGS';
  EXCEPTION
    WHEN OTHERS THEN
      v_KHHCS0:='0';
  END;
  --是否多价格管理
  BEGIN
    SELECT Trim(VALUE0) INTO v_SFDJG0 FROM XT_XTCS00 WHERE NAME00='YF_SFDLSJGL';
  EXCEPTION
    WHEN OTHERS THEN
      v_SFDJG0:='0';
  END;
  --取出门诊收费是否预扣  qks      2012.03.26 
  SELECT nvl(max(VALUE0),'N') INTO V_DJGSFYK FROM XT_XTCS00 WHERE NAME00='YF_DJGSFYK';  
  --linzy 2012.05.27 注释 
  --UPDATE yf_ypkcxx z SET ykkcsl=sjkcsl;
  --commit;
  DECLARE
    V_YKYXTS      XT_XTCS00.VALUE0%TYPE;
    --住院部分
  BEGIN
    --取出门诊是否数量预扣  JETHUA      2004.07.22
    BEGIN
      SELECT Trim(VALUE0) INTO V_YKYXTS FROM XT_XTCS00 WHERE NAME00='YF_MZCFSLYKYXTS';
    EXCEPTION
      WHEN OTHERS THEN
        V_YKYXTS:='0';
    END;

    --省二要求:处方中如果有成药药品的处方，处方状态必须修改成‘1’ yangh 2007.10.22
    if v_KHHCS0 = '3' then
      execute immediate 'ALTER TABLE SD_YF.YF_MZCF00 DISABLE ALL TRIGGERS';
      UPDATE YF_MZCF00 C SET YPDLBH='1'
        WHERE jzrq00>=to_char(sysdate-7,'yyyymmdd') and EXISTS (SELECT 1 FROM YF_MZCFMX A,BM_YD0000 B WHERE A.YPNM00=B.YPNM00 AND A.CFLSH0=C.CFLSH0
        AND B.LBBH00='1' GROUP BY CFLSH0 HAVING COUNT(1)>0 ) AND C.YPDLBH<>'1';
      execute immediate 'ALTER TABLE SD_YF.YF_MZCF00 ENABLE ALL TRIGGERS';
    end if;
    --门诊部分
    IF TO_NUMBER(V_YKYXTS)>0 THEN
      --扣除处方预扣，不包括门诊已记帐的处方，已记帐在下面处理
      FOR yp IN (SELECT YPNM00,YFBMBH,SUM(YKSL00) YPZSL0
               FROM VW_YF_YPYKSLKCMZJZ GROUP BY YPNM00,YFBMBH)  LOOP
      UPDATE YF_YPKCXX z SET YKKCSL=decode(sign(YKKCSL-yp.YPZSL0),-1,0,YKKCSL-yp.YPZSL0)
        WHERE  z.YFBMBH=yp.YFBMBH AND z.YPNM00=yp.YPNM00;
      END LOOP;
      --将未记账的处方置未数量预扣标志
      UPDATE YF_MZCFMX SET SFSLYK='N' WHERE CFLSH0 IN
      (SELECT CFLSH0 FROM YF_MZCF00 WHERE CFZT00 IN ('0','4') AND SRRQ00>=TO_CHAR(SYSDATE-5,'YYYYMMDD') AND SRRQ00<=TO_CHAR(SYSDATE-1,'YYYYMMDD'));
      if v_KHHCS0='17' then  --老年医院在备注里面添加*号,
          --将超过预扣有效天数的已记账的处方置未数量预扣标志
          UPDATE YF_MZCFMX SET SFSLYK='N' WHERE CFLSH0 IN
          (SELECT CFLSH0 FROM YF_MZCF00 WHERE CFZT00 ='1' AND JZRQ00<TO_CHAR(SYSDATE-TO_NUMBER(V_YKYXTS),'YYYYMMDD'));
          UPDATE YF_MZCFMX SET SFSLYK='Y' WHERE CFLSH0 IN
          (SELECT CFLSH0 FROM YF_MZCF00 WHERE CFZT00 ='1' AND JZRQ00>=TO_CHAR(SYSDATE-TO_NUMBER(V_YKYXTS),'YYYYMMDD'));
          FOR cf IN (SELECT CFLSH0,BZ0000 FROM YF_MZCF00 F WHERE CFZT00 ='1'
                 AND JZRQ00<TO_CHAR(SYSDATE-TO_NUMBER(V_YKYXTS),'YYYYMMDD')
                 AND NVL(INSTR(BZ0000,'*'),0)=0) LOOP
          UPDATE YF_MZCF00 Z SET BZ0000=TRIM(BZ0000)||'*'
          WHERE Z.CFLSH0=cf.CFLSH0;
          END LOOP;

          FOR cf IN (SELECT CFLSH0,BZ0000 FROM YF_MZCF00 F WHERE CFZT00 ='1'
          AND JZRQ00>=TO_CHAR(SYSDATE-TO_NUMBER(V_YKYXTS),'YYYYMMDD')
          AND NVL(INSTR(BZ0000,'*'),0)>0) LOOP
          UPDATE YF_MZCF00 Z SET BZ0000=REPLACE(BZ0000,'*','')
          WHERE Z.CFLSH0=cf.CFLSH0;
          END LOOP;

          --老年医院以处方的记帐日期为准,去扣除预扣库存
          FOR yp IN (SELECT X.YPNM00,F.YFBMBH,SUM(X.YPZSL0*X.ZHL000) YPZSL0
                 FROM YF_MZCFMX X,YF_MZCF00 F WHERE X.CFLSH0=F.CFLSH0 AND CFZT00 ='1'
                 AND JZRQ00>=TO_CHAR(SYSDATE-TO_NUMBER(V_YKYXTS),'YYYYMMDD')
                 GROUP BY X.YPNM00,F.YFBMBH)  LOOP
          UPDATE YF_YPKCXX z SET YKKCSL=decode(sign(YKKCSL-yp.YPZSL0),-1,0,YKKCSL-yp.YPZSL0)
          WHERE  z.YFBMBH=yp.YFBMBH AND z.YPNM00=yp.YPNM00;
          END LOOP;
      else
          --将超过预扣有效天数的已记账的处方置未数量预扣标志
          UPDATE YF_MZCFMX SET SFSLYK='N' WHERE CFLSH0 IN
          (SELECT CFLSH0 FROM YF_MZCF00 WHERE CFZT00 ='1' AND SRRQ00>=TO_CHAR(SYSDATE-5,'YYYYMMDD')AND SRRQ00<=TO_CHAR(SYSDATE-TO_NUMBER(V_YKYXTS),'YYYYMMDD'));
          --扣除预扣有效天数内的处方的预扣库存
          FOR yp IN (SELECT X.YPNM00,F.YFBMBH,SUM(X.YPZSL0*X.ZHL000) YPZSL0
                   FROM YF_MZCFMX X,YF_MZCF00 F WHERE X.CFLSH0=F.CFLSH0 AND CFZT00 ='1'
                   AND SRRQ00>TO_CHAR(SYSDATE-TO_NUMBER(V_YKYXTS),'YYYYMMDD')
                   GROUP BY X.YPNM00,F.YFBMBH)  LOOP
            UPDATE YF_YPKCXX z SET YKKCSL=decode(sign(YKKCSL-yp.YPZSL0),-1,0,YKKCSL-yp.YPZSL0)
            WHERE  z.YFBMBH=yp.YFBMBH AND z.YPNM00=yp.YPNM00;
          END LOOP;
      end if;
    --2012.03.26 QKS
    elsif V_DJGSFYK = 'Y' then
      /*
      FOR yp IN (SELECT X.YPNM00,F.YFBMBH,SUM(X.YPZSL0*X.ZHL000) YPZSL0
             FROM YF_MZCFMX X,YF_MZCF00 F WHERE X.CFLSH0=F.CFLSH0 AND CFZT00 in ('0','4')
             AND SRRQ00>=TO_CHAR(SYSDATE-3,'YYYYMMDD') AND SRRQ00<=TO_CHAR(SYSDATE-1,'YYYYMMDD')
             AND X.CXBZ00='Z' AND X.SFSLYK = 'Y'
             GROUP BY X.YPNM00,F.YFBMBH)  LOOP
      UPDATE YF_YPKCXX z SET YKKCSL=decode(sign(YKKCSL+yp.YPZSL0),-1,0,YKKCSL+yp.YPZSL0)
      WHERE  z.YFBMBH=yp.YFBMBH AND z.YPNM00=yp.YPNM00;
      END LOOP;    
      */
      --linzy 2012.05.27 修正没有未发药处方的药品才把实际库存更新到预扣库存，防止更新预扣库存后发药导致预扣库存没有减去当前未发药的。出现夜诊的开的药品预扣数量大于实际数量
      update YF_YPKCXX Z SET YKKCSL=SJKCSL; 
      
      --where not exists(
      --select 1 from YF_MZCF00 A,YF_MZCFMX B 
      -- where A.CFLSH0=B.CFLSH0 and A.SRRQ00>=TO_CHAR(SYSDATE-3,'YYYYMMDD')
      --   and A.SRRQ00<=TO_CHAR(SYSDATE-1,'YYYYMMDD') and CFZT00='1' and A.YFBMBH+0=Z.YFBMBH and B.YPNM00=Z.YPNM00); 
      --commit;
      
      --linzy 2012.05.27 修正预扣库存为当前库存减去未发药的药品数量 珠海为收费时预扣  
      --insert into YF_YPKCXX_TEMP select a.*,to_char(sysdate,'YYYYMMDD') from YF_YPKCXX a; 
      FOR yp IN (SELECT X.YPNM00,F.YFBMBH,SUM(X.YPZSL0*X.ZHL000) YPZSL0
             FROM YF_MZCFMX X,YF_MZCF00 F WHERE X.CFLSH0=F.CFLSH0 AND CFZT00 in ('1')
             AND SRRQ00>=TO_CHAR(SYSDATE-3,'YYYYMMDD') AND SRRQ00<=TO_CHAR(SYSDATE,'YYYYMMDD')
             AND X.CXBZ00='Z' AND X.SFSLYK = 'Y'
             GROUP BY X.YPNM00,F.YFBMBH)  LOOP
      UPDATE YF_YPKCXX z SET YKKCSL=decode(sign(SJKCSL-yp.YPZSL0),-1,0,SJKCSL-yp.YPZSL0)
      WHERE  z.YFBMBH=yp.YFBMBH AND z.YPNM00=yp.YPNM00;
      END LOOP;    
      --linzy 2012.05.29 修正三天之前的数据已记账未发药的处方SFSLYK 改为N 
      if sysdate-to_date(to_char(sysdate,'YYYYMM')||'01','YYYYMMDD')>3 then
         UPDATE YF_MZCFMX SET SFSLYK='N' WHERE CFLSH0 IN
        (SELECT CFLSH0 FROM YF_MZCF00 WHERE CFZT00='1' AND SRRQ00>=TO_CHAR(SYSDATE,'YYYYMM')||'01' AND SRRQ00<TO_CHAR(SYSDATE-3,'YYYYMMDD')) AND CXBZ00='Z' AND SFSLYK='Y';    
      end if;    
      --将未记账的处方置未数量预扣标志
      UPDATE YF_MZCFMX SET SFSLYK='N' WHERE CFLSH0 IN
      (SELECT CFLSH0 FROM YF_MZCF00 WHERE CFZT00 IN ('0','4') AND SRRQ00>=TO_CHAR(SYSDATE-3,'YYYYMMDD') AND SRRQ00<=TO_CHAR(SYSDATE-1,'YYYYMMDD')) AND CXBZ00='Z' AND SFSLYK='Y';    
    ELSE
      --扣除处方预扣，包括门诊已记帐的处方?
      FOR yp IN (SELECT YPNM00,YFBMBH,SUM(YKSL00) YPZSL0
               FROM VW_YF_YPYKSL GROUP BY YPNM00,YFBMBH)  LOOP
      UPDATE YF_YPKCXX z SET YKKCSL=decode(sign(YKKCSL-yp.YPZSL0),-1,0,YKKCSL-yp.YPZSL0)
        WHERE  z.YFBMBH=yp.YFBMBH AND z.YPNM00=yp.YPNM00;
      END LOOP;
    END IF;
    --报损出库 －－铁路医院，平潭医院
    IF (v_KHHCS0='6')  or (v_KHHCS0='18')THEN
      FOR yp IN (SELECT X.YPNM00,D.YFBMBH,SUM(X.CKSL00*X.ZHL000) YPZSL0
               FROM YF_YPCKD0 D,YF_YPCKMX X
               WHERE D.CKDH00=X.CKDH00 AND D.CKLXBH='04' AND D.CKZT00='0'
               GROUP BY X.YPNM00,D.YFBMBH)  LOOP
        UPDATE YF_YPKCXX z SET YKKCSL=decode(sign(YKKCSL-yp.YPZSL0),-1,0,YKKCSL-yp.YPZSL0)
        WHERE  z.YFBMBH=yp.YFBMBH AND z.YPNM00=yp.YPNM00;
      END LOOP;
    END IF;
  END;
  COMMIT;
  ----------一下为修正药品批次预扣
  IF v_SFDJG0='Y' THEN
    UPDATE YF_PCKCMX SET YKKCSL=SJKCSL;
    COMMIT;
    DECLARE
      CURSOR C_JZYP00 IS--zhr 08.01.11  批次预扣库存应该和隶属药品的预扣库存算法一致,全部用视图VW_YF_YPYKSL
        SELECT YPNM00,YFBMBH,SUM(SJKCSL) YPZSL0
               FROM VW_YF_PCYKSL GROUP BY YPNM00,YFBMBH;

      V_YFBMBH YF_YPKCXX.YFBMBH%TYPE;    --药房部门编号
      V_YPNM00 YF_YPKCXX.YPNM00%TYPE;    --药品内码
      V_YPZSL0 YF_YZYPSQ.YPZSL0%TYPE;
      -----------------
      CURSOR C_PCKC00 IS
      SELECT KCLSH0,YKKCSL
        FROM YF_PCKCMX
       WHERE YFBMBH=V_YFBMBH AND YPNM00=V_YPNM00
       ORDER BY TO_NUMBER(KCLSH0);

      V_KCLSH0    YF_PCKCMX.KCLSH0%TYPE;
      V_YKKCSL    YF_PCKCMX.YKKCSL%TYPE;
      V_SYCKSL    YF_PCKCMX.SJKCSL%TYPE;    --剩余出库数量
      V_BCCKSL    YF_PCKCMX.SJKCSL%TYPE;    --本次出库数量

    BEGIN
      OPEN C_JZYP00;
      FETCH C_JZYP00 INTO V_YPNM00,V_YFBMBH,V_YPZSL0;
      WHILE C_JZYP00%FOUND LOOP

        OPEN C_PCKC00;
        FETCH C_PCKC00 INTO V_KCLSH0,V_YKKCSL;

        V_SYCKSL:=V_YPZSL0;
        WHILE C_PCKC00%FOUND AND V_SYCKSL>0 LOOP
          IF V_SYCKSL-V_YKKCSL <=0 THEN  --本批次够出剩余出库量V_DQLSDJ
            V_BCCKSL:=V_SYCKSL;
            V_SYCKSL:=0;
          ELSE
            V_BCCKSL:=V_YKKCSL;
            V_SYCKSL:=V_SYCKSL-V_BCCKSL;
          END IF;

          UPDATE YF_PCKCMX SET YKKCSL=decode(sign(YKKCSL-V_BCCKSL),-1,0,YKKCSL-V_BCCKSL)
           WHERE KCLSH0=V_KCLSH0;
          FETCH C_PCKC00 INTO V_KCLSH0,V_YKKCSL;
        END LOOP;
        CLOSE C_PCKC00;

        FETCH C_JZYP00 INTO V_YPNM00,V_YFBMBH,V_YPZSL0;
      END LOOP;
      CLOSE C_JZYP00;
      COMMIT;
    END;
  END IF;
EXCEPTION
  WHEN OTHERS THEN
    ROLLBACK;
  --dbms_output.put_line(to_char(vypnm00));
END;
/