I've developed a stored procedure that executes from a form. The form passes a user specified Cutoff Date and the user's username to the stored procedure.
However, I want to effectively close those open vouchers by balancing the open quantities on the vouchers. Has anyone had to do this before? If so, what tables do I need to look at what are the types I need to issue to close these vouchers? The close is these open vouchers?
Below is my stored procedure for reference; thank you in advance!
ALTER Procedure [dbo].[VPCleanupSp] (
@Days integer = 183
,@Username UsernameType
)
as
BEGIN
declare @LastVch as VoucherType
, @adjqty decimal(20,5)
, @RetQty decimal(20,5)
, @ponum ponumtype
, @poline polinetype
, @porelease poreleasetype
, @QtyRec decimal(20,5)
, @QtyVch decimal(20,5)
, @seq int
, @today as datetime
, @cmd nvarchar(4000)
, @db sysname
, @query nvarchar(1000)
, @Datafile nvarchar(1000)
set @today = dbo.midnightof(GetDate())
SET @db = Db_Name()
set @username = dbo.UserNameSp()
-- Cursor logic
declare _voucher cursor for
select poi.po_num, po_line, po_release, poi.qty_received, poi.qty_voucher, poi.qty_returned
from poitem poi
join po on po.po_num = poi.po_num
where datediff(day, po.order_date, getdate()) > @Days
and datediff(day, isnull(poi.rcvd_date, getdate()), getdate()) > @Days
and poi.stat IN ('O', 'F')
and poi.qty_voucher <> poi.qty_received;
open _voucher;
while 1=1
begin
fetch next from _voucher into @ponum, @poline, @porelease, @QtyRec, @QtyVch, @RetQty;
@FETCH_STATUS <> 0
break;
PRINT 'po_num: ' + CAST(@ponum AS VARCHAR(10)) + ', po_line: ' + CAST(@poline AS VARCHAR(10)) + ', po_release: ' + CAST(@porelease AS VARCHAR(10)) + ', qty_received: ' + CAST(@QtyRec AS VARCHAR(20)) + ', qty_voucher: ' + CAST(@QtyVch AS VARCHAR(20)) + ', qty_returned: ' + CAST(@RetQty AS VARCHAR(20));
set @AdjQty = (@QtyRec + @RetQty) - @QtyVch;
update poitem
set poitem.qty_voucher = poitem.qty_voucher + @AdjQty,
poitem.qty_returned = poitem.qty_returned - @RetQty
from poitem
join po on po.po_num = poitem.po_num
where poitem.po_num = @ponum and poitem.po_line = @poline and poitem.po_release = @porelease
select @LastVch = Voucher
from po_vch pv
where pv.po_num = @ponum
and pv.po_line = @poline
and pv.po_release = @porelease
and type = 'V'
order by voucher
set @LastVch = isnull(@LastVch,1)
set @seq = isnull((select top 1 date_seq
from po_vch pv
where pv.po_num = @ponum
and pv.po_line = @poline
and pv.po_release = @porelease
and rcvd_date = @today
order by date_seq desc),0) + 1
-- Insert into po_vch
insert into po_vch (po_num, po_line, po_release, rcvd_date, date_seq, exch_rate, qty_received, qty_vouchered, Type, voucher, qty_returned)
select @ponum, @poline, @porelease, @today, @seq, 1, 0, @AdjQty, 'V', @LastVch, 0
insert into Tmp_VPCleanup (po_num, po_line, po_release, rcvd_date, date_seq, exch_rate, qty_received, qty_vouchered, Type, voucher, qty_returned)
select @ponum, @poline, @porelease, @today, @seq, 1, 0, @AdjQty, 'V', @LastVch, 0
if @RetQty <> 0
begin
-- Insert into po_vch
insert into po_vch (po_num, po_line, po_release, rcvd_date, date_seq, exch_rate, qty_received, qty_vouchered, Type, voucher, qty_returned)
select @ponum, @poline, @porelease, @today, (@seq + 1), 1, 0, 0, 'W', @LastVch, (@RetQty * -1)
-- Insert the same values into Tmp_VPCleanup
insert into Tmp_VPCleanup (po_num, po_line, po_release, rcvd_date, date_seq, exch_rate, qty_received, qty_vouchered, Type, voucher, qty_returned)
select @ponum, @poline, @porelease, @today, (@seq + 1), 1, 0, 0, 'W', @LastVch, (@RetQty * -1)
END
END
close _voucher;
deallocate _voucher;
DECLARE
@cmd nvarchar(4000)
, @query nvarchar(1000)
, @Datafile nvarchar(1000)
, @FileName nvarchar(100)
, @TempDatafile nvarchar(1000)
, @TempHeaderFile nvarchar(1000)
-- Update the @Username variable after the transaction is committed
SET @Username = @Username + ''
print @username
SET @FileName = 'ClosedVouchers_' + FORMAT(GETDATE(), 'yyyy-MM-dd-HH-mm-ss') + '.csv'
SELECT @Datafile = tm_path FROM intranet
SET @Datafile = ISNULL(@Datafile, '') + 'ReportOutputFiles'
SET @TempDatafile = @Datafile + @Username + @FileName
SET @TempHeaderFile = @Datafile + @Username + 'header_tmp.csv'
SET @query = 'SELECT po_num, po_line, po_release, rcvd_date, date_seq, exch_rate, qty_received, qty_vouchered, qty_returned, Type, voucher FROM ' +@db+';..Tmp_VPCleanup'
-- This line exports data from Tmp_VPCleanup to a temporary CSV file
set @cmd = 'bcp "' + @query + '" queryout "' + @SERVERNAME + ' -c -t, -T'
print @cmd
exec xp_cmdshell @cmd
-- This line echoes the headers to another temporary file
set @cmd = 'echo "PO Number","PO Line","PO Release","Received Date","Date Sequence","Exchange Rate","Quantity Received","Quantity Vouchered","Quantity Returned","Voucher Type","Voucher Number" > "' + @TempDatafile + '"'
print 'echo cmd ' + @cmd
exec xp_cmdshell @cmd
-- This line concatenates the two files: the header file and the data file
set @cmd = 'type "' + @TempHeaderFile + '" >> "' + @TempDatafile + '"'
print @cmd
exec xp_cmdshell @cmd
-- Delete the temporary header file
set @cmd = 'del "' + @TempHeaderFile + '"';
print 'Delete Header File Command: ' + @cmd;
exec xp_cmdshell @cmd;
-- Cleanup
TRUNCATE TABLE Tmp_VPCleanup
END
