Restore the RM20101 table. You'll need to edit the table name created in the above script
set nocount on
set transaction isolation level read uncommitted
--get rid of all our imports
delete rm20201 where TRXSORCE = 'import'
--loop through the customers that have open invoices
DECLARE @custnmbr varchar(21), @Payment varchar(21), @PaymentType int, @PaymentAmt numeric(19,5), @CurrentPymt numeric(19,5)
declare @Invoice varchar(21), @InvoiceType int, @InvoiceAmt numeric(19,5)
declare @dt date
select @dt = getdate()
DECLARE CustomerCursor CURSOR LOCAL FAST_FORWARD FOR
select custnmbr
from rm20101
where CURTRXAM > 0
and RMDTYPAL > 5
group by custnmbr
OPEN CustomerCursor
WHILE 1=1
BEGIN
FETCH NEXT FROM CustomerCursor INTO @custnmbr
if @@fetch_status <> 0 begin
break
end
print formatmessage('Processing customer %s',@custnmbr)
--========================================================================================================
--loop through the open payments for these clients
--========================================================================================================
DECLARE PaymentCursor CURSOR LOCAL FAST_FORWARD FOR
select rtrim(docnumbr), RMDTYPAL, CURTRXAM
from rm20101 rt
where rt.RMDTYPAL > 5
and rt.CURTRXAM > 0
and rt.CUSTNMBR = @custnmbr
order by docdate
OPEN PaymentCursor
WHILE 1=1
BEGIN
FETCH NEXT FROM PaymentCursor INTO @payment, @PaymentType, @PaymentAmt
if @@fetch_status <> 0 begin
break
end
print formatmessage(' Processing payment %s %s',@payment, format(@PaymentAmt,'#,###.00'))
--========================================================================================================
-- invoices
--========================================================================================================
DECLARE InvoiceCursor CURSOR LOCAL FAST_FORWARD FOR
select rtrim(docnumbr), RMDTYPAL, CURTRXAM
from rm20101 rt
where rt.RMDTYPAL < 5
and rt.CURTRXAM > 0
and rt.CUSTNMBR = @custnmbr
order by docdate
OPEN InvoiceCursor
WHILE 1=1
BEGIN
FETCH NEXT FROM InvoiceCursor INTO @invoice, @invoicetype, @invoiceamt
if @@fetch_status <> 0 begin
break
end
print formatmessage(' Processing invoice %s, %s',@invoice, format(@invoiceamt,'#,###.00'))
if @InvoiceAmt > @PaymentAmt begin
select @CurrentPymt = @PaymentAmt
select @PaymentAmt = 0
print formatmessage(' Paying %s, exiting',(format(@CurrentPymt,'#,###.00')))
end else begin
select @CurrentPymt = @InvoiceAmt
select @PaymentAmt = @PaymentAmt - @InvoiceAmt
print formatmessage(' Paying %s, continuing',(format(@CurrentPymt,'#,###.00')))
end
insert into rm20201 (CUSTNMBR, DATE1, GLPOSTDT, TRXSORCE, APTODCNM, APTODCTY , aptodcdt, ApplyToGLPostDate, APPTOAMT , ORAPTOAM , APFRDCNM, APFRDCTY , apfrdcdt, ApplyFromGLPostDate, APFRMAPLYAMT, ActualApplyToAmount)
values (@custnmbr,@dt , @dt , 'IMPORT', @Invoice, @InvoiceType, @DT , @dt , @CurrentPymt, @CurrentPymt, @Payment, @PaymentType, @dt , @dt , @CurrentPymt, @CurrentPymt)
update rm20101 set CURTRXAM = CURTRXAM - @CurrentPymt where DOCNUMBR = @Payment and RMDTYPAL = @PaymentType
update rm20101 set CURTRXAM = CURTRXAM - @CurrentPymt where DOCNUMBR = @Invoice and RMDTYPAL = @InvoiceType
if @PaymentAmt = 0 begin
break
end
END
CLOSE InvoiceCursor
DEALLOCATE InvoiceCursor
END
CLOSE PaymentCursor
DEALLOCATE PaymentCursor
END
CLOSE CustomerCursor
DEALLOCATE CustomerCursor