klick:setuser 'BFCRM'|@|go|@|if exists(select * from sysobjects where name='HYK_PROC_HYK_JFFHLPJXCITEM' and type='P')|@| drop Procedure HYK_PROC_HYK_KCCZKBGZITEM|@|go|@|print 'Creating Procedure HYK_PROC_HYK_JFFHLPJXCITEM ...'|@|go|@|setuser 'BFCRM'|@|go|@|Create Procedure HYK_PROC_HYK_JFFHLPJXCITEM|@|as|@|begin|@| declare @JZRQ char(10)|@| declare @HYKTYPE smallint|@| declare @SL int|@| declare @JE money|@| declare @QCYE money|@||@| /*进货*/|@| declare curJK cursor for|@| select D.JZRQ,X.LPDM,X.LPMC,M.SPJJ,M.SPSJ,M.SPSL,M.SPSL*M.SPSJ |@| from BFCRM.HYK_JFFHLPDHDITEM M,BFCRM.HYK_JFFHLPXX X,BFCRM.HYK_JFFHLPDHD D|@| where M.SPCODE=X.LPDM|@| and M.JLBH=D.JLBH|@| and D.JZRQ=convert(char,getdate(),102)|@| for read only|@||@| open curJK|@| fetch curJK into @JZRQ,@LPDM,@LPMC,@SPJJ,@SPSJ,@SPSL,@JHJE|@| while (@@sqlstatus = 0)|@| begin|@| update BFCRM.HYK_JFFHLPJXCITEM|@| set SPJJ=@SPJJ,SPSJ=@SPSJ,SPSL=@SPSL|@| where RQ=@JZRQ|@| and SPCODE = @LPDM|@| if @@rowcount=0|@| insert into BFCRM.HYK_JFFHLPJXCITEM(JZRQ,LPDM,LPMC,LPJJ,LPSJ,JHSL,JHJE,DHSL,DHJE,JCSL,JCJE) |@| values(@JZRQ,@LPDM,@LPMC,@LPJJ,@LPSJ,@JHSL,@JHJE,0,0,@JCSL,@JCJE)|@| fetch curJK into @JZRQ,@LPDM,@LPMC,@SPJJ,@SPSJ,@SPSL,@JHJE|@| end|@| close curJK|@| deallocate Cursor curJK|@||@| /*兑换*/|@| declare curJK cursor for|@| select J.BGDDDM,J.HYKTYPE,isnull(J.QCYE,0),count(I.JLBH) SL,sum(isnull(I.JE,0)) JE|@| from BFCRM.HYKJKJL J,BFCRM.HYKJKJLITEM I|@| where J.JLBH=I.JLBH AND|@| DateDiff(Day,I.XKRQ,@ProcDate) = 0|@| group by J.BGDDDM,J.HYKTYPE,isnull(J.QCYE,0)|@| for read only|@||@| open curJK|@| fetch curJK into @BGDDDM,@HYKTYPE,@QCYE,@SL,@JE|@| while (@@sqlstatus = 0)|@| begin|@| select @JE=isnull(@JE,0)|@| update BFCRM.HYK_KCCZKBGZ|@| set XKSL=XKSL+@SL,XKJE=XKJE+@JE|@| where RQ=@ProcDate|@| and BGDDDM = @BGDDDM|@| and HYKTYPE = @HYKTYPE|@| and MZJE = @QCYE|@| if @@rowcount=0|@| insert into BFCRM.HYK_KCCZKBGZ(RQ,BGDDDM,HYKTYPE,MZJE,XKSL,XKJE,YEARMONTH)|@| values(@ProcDate,@BGDDDM,@HYKTYPE,@QCYE,@SL,@JE,@YEARMONTH)|@| fetch curJK into @BGDDDM,@HYKTYPE,@QCYE,@SL,@JE|@| end|@| close curJK|@| deallocate Cursor curJK|@||@| /*结存*/|@| declare curJK cursor for|@| select J.BGDDDM_BR,J.HYKTYPE,I.JE QCYE,count(I.JLBH) SL,sum(I.JE) JE|@| from BFCRM.CARDLQJL J,BFCRM.CARDLQJLITEM I|@| where J.JLBH=I.JLBH AND|@| DateDiff(Day,J.ZXRQ,@ProcDate) = 0|@| group by J.BGDDDM_BR,J.HYKTYPE,I.JE|@| for read only|@||@| open curJK|@| fetch curJK into @BGDDDM,@HYKTYPE,@QCYE,@SL,@JE|@| while (@@sqlstatus = 0)|@| begin|@| select @SL=isnull(@SL,0)|@| select @JE=isnull(@JE,0)|@| update BFCRM.HYK_KCCZKBGZ|@| set BRSL=BRSL+@SL,BRJE=BRJE+@JE|@| where RQ=@ProcDate|@| and BGDDDM = @BGDDDM|@| and HYKTYPE = @HYKTYPE|@| and MZJE = @QCYE|@| if @@rowcount=0|@| insert into BFCRM.HYK_KCCZKBGZ(RQ,BGDDDM,HYKTYPE,MZJE,BRSL,BRJE,YEARMONTH)|@| values(@ProcDate,@BGDDDM,@HYKTYPE,@QCYE,@SL,@JE,@YEARMONTH)|@| fetch curJK into @BGDDDM,@HYKTYPE,@QCYE,@SL,@JE|@| end|@| close curJK|@| deallocate Cursor curJK|@|end|@|go
bde oracle 商友的流程_ORACLE 商友ERP
最新推荐文章于 2024-06-23 15:03:44 发布