-- Start of DDL Script for Procedure SD_HOSPITAL.SP_WZ_GWKCCX1
-- Generated 2012-2-28 16:04:23 from SD_HOSPITAL@ORCL

CREATE OR REPLACE 
PROCEDURE sp_wz_gwkccx1 --仓库过往库存查询
(      as_KSBH00  IN   NUMBER,               --科室编号
       as_WZLX00  IN   CHAR,                 --物资类别
     as_KCCXRQ  in   char,               -- 查询日期
      as_KCCXSJ  in    char,              -- 查询时间
       ad_ID0000  OUT  NUMBER,               --统计序列号
       as_YHMSG0  OUT  VARCHAR,              --存储过程提示的错误信息
       as_SYSMSG  OUT  VARCHAR               --系统提示的错误信息
)
as
    ls_MC0000  WZ_TEMP00.KSMC00%TYPE;
    ls_wzdmbm  CHAR(20);
    ls_GG0000  WZ_WZXX00.WZGG00%TYPE;
    ls_WZDW00  CHAR(10);
    ls_KCSL00  NUMBER(12,2);
    ls_CGJBZ0  NUMBER(14,4);
    ls_KCZJE0  NUMBER(14,4);
    ls_PYSM00  CHAR(20);
    ls_WBSM00  CHAR(20);


    CURSOR WZ_KSLYMX IS
    select c.wzdmbm,b.wzmc00,b.gg0000,b.wzdw00,b.cgj000,b.jcs000,b.jcs000*b.cgj000 as jcje00,c.wbsm00,
c.pysm00 from vw_wz_rckmx0 b,wz_rck000 a,bm_wzdm00 c,wz_kc0000 d
     where  d.kcid00=b.kcid00 and d.kcbmbh=as_KSBH00 and a.rckid0=b.rckid0 AND c.wzdmid=b.wzdmid and c.wzdmbm like as_WZLX00 AND b.rckmxh in
(select max(y.rckmxh) from wz_rck000 x,wz_rckmx0 y
where x.rckid0=y.rckid0 and x.qrrq00||x.qrsj00<=as_KCCXRQ||as_KCCXSJ  group by y.wzdmid) 
----====修改
and b.jcs000<>0
----====修改
order by c.wzdmbm;
BEGIN
   select  SQ_WZ_TEMP00_GWTJID.nextval into ad_ID0000  from  dual;
   OPEN WZ_KSLYMX;
   LOOP
	 FETCH WZ_KSLYMX INTO ls_WZdmbm,ls_MC0000,ls_GG0000,ls_WZDW00,ls_CGJBZ0,ls_KCSL00,ls_KCZJE0,ls_WBSM00,ls_PYSM00;
	   EXIT WHEN WZ_KSLYMX%NOTFOUND;
             Insert into  WZ_TEMP00(TJID00,TJPX00,WZSXMC,WZMC00,WZGG00,WZDW00,JSJE00,JSSL00,ZJJE00,KSMC00,SHBMMC)
                 values(ad_ID0000,SQ_WZ_TEMP00_TJPX00.nextval,ls_WZdmbm,ls_MC0000,ls_GG0000,ls_WZDW00,ls_CGJBZ0,ls_KCSL00,ls_KCZJE0,ls_PYSM00,ls_WBSM00);
   END LOOP;
   CLOSE WZ_KSLYMX;
   commit;
EXCEPTION
      WHEN OTHERS THEN
                  as_YHMSG0:='数据库错误：出错原因未知';
                  as_SYSMSG:=substr(SQLERRM||'执行存储过程错误:SP_WZ_GWKCCX',1,150);
      rollback;
END;
/



-- End of DDL Script for Procedure SD_HOSPITAL.SP_WZ_GWKCCX1