Friday, January 28, 2011

Get the Basic Idea of Cursor from Given Written Cursor

SET QUOTED_IDENTIFIER ON GOSET ANSI_NULLS ON GO

CREATE proc GET_BIN_RES_A --123--1024--123--668--1363--1128--777--255--33--1137--1386--1137--33--1061 --138--1137--289--99--255--1061--1373 --Get_Bin_cursor @Item int as Declare @rv_no varchar(30) Declare @rv_no1 varchar(30) Declare @rv_no2 varchar(30) Declare @rv_no3 varchar(30) Declare @iv_no varchar(30) Declare @iv_no1 varchar(30) Declare @iv_no2 varchar(30) Declare @rv_date smalldatetime Declare @iv_date1 smalldatetime Declare @iv_date2 smalldatetime Declare @iv_date smalldatetime Declare @rv3 smalldatetime Declare @vendor_name varchar(200) Declare @client_name varchar(200) Declare @client_name1 varchar(200) Declare @bill_no int Declare @podate smalldatetime Declare @bin_no int Declare @itemdesc varchar(250) Declare @pono varchar(100) --Declare @pono varchar(100), declare @recd_qty int declare @issue_qty int declare @issue_qty1 int declare @item_receipt int Declare @Balance int Declare @potrate int Declare @Bal int Declare @po1 varchar(100) declare @aa int Declare @Bal1 int Declare @Bal11 int Declare @Bal12 int Declare @Bal2 int Declare @Bal3 int Declare @Bal4 int Declare @Bal5 int Declare @Bal16 int Declare @Bal17 int Declare @Bal18 int Declare @item_id int Declare @aa12 int Declare @aa1 int Declare @mes varchar(100)Declare @Pack_Size_G varchar(200)declare @aa51 intdeclare @sr1 intdeclare @sr2 intdeclare @cnt intDECLARE bin_cursor CURSOR FOR Select rv_no,rv_date,vendor_name,podate,grnitemid as bin_no,itemdesc,pono,recd_qty,item_receipt,potrate,grnitemid as item_id,mes,Pack_Size_G from Get_BIN_RV_A where grnitemid=@Item order by rv_date delete from tem_bin open bin_cursor FETCH NEXT FROM bin_cursor INTO @rv_no,@rv_date,@vendor_name,@podate,@bin_no,@itemdesc,@pono,@recd_qty,@item_receipt,@potrate,@item_id,@mes,@Pack_Size_G set @po1=@pono set @rv_no1=@rv_no -- WHILE @@FETCH_STATUS = 0 BEGIN
if @rv_no=@rv_no1 begin set @Balance=@recd_qty +@item_receipt insert into tem_bin(rv_no,rv_date,vendor_name,podate,bin_no,itemdesc,pono,recd_qty,item_receipt,potrate,Balance,item_id,mes,Pack_Size_G) values(@rv_no,@rv_date,@vendor_name,@podate,@bin_no,@itemdesc,@pono,@recd_qty,@item_receipt,@potrate,@Balance,@item_id,@mes,@Pack_Size_G) end
if @rv_no<>@rv_no1 begin select @bal4=balance from tem_bin where sr in (select max(sr) as sr from tem_bin)--item_id=@item set @bal3=@Bal4+@recd_qty print 'BaliR' + ' ' + convert(varchar(10),@bal3) insert into tem_bin(rv_no,rv_date,vendor_name,podate,bin_no,itemdesc,pono,recd_qty,item_receipt,potrate,Balance,item_id,mes,Pack_Size_G) values(@rv_no,@rv_date,@vendor_name,@podate,@bin_no,@itemdesc,@pono,@recd_qty,@item_receipt,@potrate,@bal3,@item_id,@mes,@Pack_Size_G) --select @rv3=max(rv_date) from tem_bin where rv_date <=@rv_date -- print @rv3--set @sr1=0select @sr1=max(sr) from tem_bin where item_id=@item --sr in (select max(sr) as sr from tem_bin)select distinct @rv3=max(rv_date) from Get_BIN_RV_A where grnitemid=@item --and rv_date<@rv_date -- in (select rv_no as rv_no from tem_bin)
print @rv3 print @sr1 end
DECLARE binA_cursor CURSOR FOR Select iv_no,iv_date,issue_qty,clientname,itemdesc,item_id from GET_BIN_IV where item_id=@Item --order by iv_date open binA_cursor FETCH NEXT FROM binA_cursor INTO @iv_no,@iv_date1,@issue_qty,@client_name1,@itemdesc,@item_id set @iv_no2=@iv_no WHILE @@FETCH_STATUS =0 begin if @iv_no=@iv_no2 --break begin select top 1 @iv_no=iv_no,@issue_qty=issue_qty,@iv_date=iv_date,@client_name=clientname from GET_BIN_IV where item_id=@item --order by iv_date--and grn_no=@rv_no set @aa12=(Select count(rv_no) as rv_no from Get_BIN_RV_A where grnitemid=@Item ) -- select @rv3=max(rv_date) from tem_bin where rv_date <=@rv_date -- print @rv3 if @aa12=1 begin select @rv3=max(rv_date) from tem_bin where rv_date <=@rv_date print 'aa5' if (@item_receipt =@issue_qty">+@recd_qty)>=@issue_qty -- if 41>=@issue_qty print'check' -- if 41>=@issue_qty begin set @bal=@balance-(case when isnull(@issue_qty,'')='' then 0 else @issue_qty end) -- update tem_bin set issue_qty=@issue_qty ,iv_no=@iv_no,balance=@bal,iv_date=@iv_date,clientname=@client_name where rv_date =@rv3 --and item_id=@item --and rv_date>@rv_date --and rv_no=@rv_no-- item_id=@item --and-and grn_no=@rv_no update tem_bin set issue_qty=@issue_qty ,iv_no=@iv_no,balance=@bal,iv_date=@iv_date,clientname=@client_name where rv_date=@rv3-- sr in (select max(sr) as sr from tem_bin ) --and rv_date>@rv_date --and rv_no=@rv_no-- item_id=@item --and-and grn_no=@rv_no end end if @aa12>1 begin print 'ajay' set @bal=@bal3 -(case when isnull(@issue_qty,'')='' then 0 else @issue_qty end) if @rv_date=@rv3 begin update tem_bin set issue_qty=@issue_qty ,iv_no=@iv_no,balance=@bal,iv_date=@iv_date,clientname=@client_name where sr=@sr1--rv_date =@rv3 --and item_id=@item --and rv_date>@rv_date --and rv_no=@rv_no-- item_id=@item --and-and grn_no=@rv_no endelse begin update tem_bin set issue_qty=@issue_qty ,iv_no=@iv_no,balance=@bal,iv_date=@iv_date,clientname=@client_name where rv_date =@rv3 --and item_id=@item --and rv_date>@rv_date --and rv_no=@rv_no-- item_id=@item --and-and grn_no=@rv_no end end end
------iv no not same---------------------------------------------------------------------------------if @iv_no<>@iv_no2 begin print 'ajay1' select @bal3=balance from tem_bin where sr in (select max(sr) as sr from tem_bin) set @Bal5=@bal3-@issue_qty set @cnt=(select count(iv_no) as iv_no from get_bin_iv where item_id=@item and iv_no in (select iv_no from tem_bin where isnull(iv_no,'')!='' ) and item_id=@item) -- set @cnt=(select count(iv_no) as iv_no from get_bin_iv where iv_no=@iv_no2)-- in (select iv_no from tem_bin where item_id=@item and isnull(iv_no,'')!='')) if @bal5<0>=1 begin print 'ajayaaaaaaa' insert into tem_bin(iv_no,issue_qty,iv_date,clientname,balance,itemdesc,item_id) values(@iv_no,@issue_qty,@iv_date1,@client_name1,@bal5,@itemdesc,@item_id) update tem_bin set item_receipt=@item_receipt end if @cnt=0 begin print 'break1' break end
--else-- begin -- print'aa' -- if @cnt>=2 -- begin -- insert into tem_bin(iv_no,issue_qty,iv_date,clientname,balance,itemdesc,item_id) values(@iv_no,@issue_qty,@iv_date1,@client_name1,@bal5,@itemdesc,@item_id) -- update tem_bin set item_receipt=@item_receipt -- end
-- end

end------------------------------------------------------------------------------------------------------
FETCH NEXT FROM binA_cursor INTO @iv_no,@iv_date1,@issue_qty,@client_name1,@itemdesc,@item_id end CLOSE binA_cursor DEALLOCATE binA_cursor
FETCH NEXT FROM bin_cursor INTO @rv_no,@rv_date,@vendor_name,@podate,@bin_no,@itemdesc,@pono,@recd_qty,@item_receipt,@potrate,@item_id,@mes,@Pack_Size_G
end --update tem_bin set issue_qty=@issue_qty ,iv_no=@iv_no,balance=@bal,iv_date=@iv_date,clientname=@client_name where sr =@sr1--@sr1 --in (select max(sr) as sr from tem_bin ) --and rv_date>@rv_date --and rv_no=@rv_no-- item_id=@item --and-and grn_no=@rv_no CLOSE bin_cursor DEALLOCATE bin_cursor

GOSET QUOTED_IDENTIFIER OFF GOSET ANSI_NULLS ON GO

No comments:

Post a Comment