IF exists (select * from INFORMATION_SCHEMA.ROUTINES where ROUTINE_NAME = 'dd_RecreateCheckStub') begin
DROP proc dd_RecreateCheckStub
end
GO
CREATE proc dd_RecreateCheckStub
-- dd_RecreateCheckStub '113787'
@CHECKSTART VARCHAR(10), @CHECKEND VARCHAR(10)
AS
set transaction isolation level read uncommitted
--declare our output table
DECLARE @APY aS TABLE (OurVoucherNumber VARCHAR(21),YourVoucherNumber VARCHAR(21),Date1 date, VCHRNMBR VARCHAR(21), DOCTYPE int, APFRDCNM VARCHAR(21), APTVCHNM VARCHAR(21), APTODCTY int, APTODCNM VARCHAR(21), Amount numeric(19,5),AmountPaid numeric(19,5),Net numeric(19,5), Level int)
--insert apply lines for all documents paid by checks in the range
INSERT INTO @APY (OurVoucherNumber, YourVoucherNumber,Date1 , VCHRNMBR , DOCTYPE , APFRDCNM , APTVCHNM , APTODCTY , APTODCNM , Amount , AmountPaid , Net , Level)
SELECT apy.APTVCHNM , apy.APTODCNM ,apy.aptodcdt, apy.VCHRNMBR, apy.DOCTYPE, APY.APFRDCNM, apy.APTVCHNM, apy.APTODCTY, APY.APTODCNM, inv.DOCAMNT , APY.APPLDAMT,APY.APPLDAMT, 0
from PM30300 apy --apply
join PM30200 inv on apy.APTVCHNM = inv.VCHRNMBR and apy.APTODCTY = inv.DOCTYPE
where apy.DOCTYPE = 6
and apy.APFRDCNM BETWEEN @CHECKSTART AND @CHECKEND
ORDER BY APY.APFRDCNM
declare @Exit int
set @Exit = 0
declare @level int
set @level = 10
while @Exit = 0 begin
--loop, and get credits...
--insert apply lines where the invoice has missing credits
INSERT INTO @APY (OurVoucherNumber, YourVoucherNumber,Date1 , VCHRNMBR , DOCTYPE , APFRDCNM , APTVCHNM , APTODCTY , APTODCNM , Amount , AmountPaid , Net , Level)
SELECT apy.VCHRNMBR , apy.APFRDCNM ,apy.DOCDATE, apy.VCHRNMBR, apy.DOCTYPE, APY.APFRDCNM, apy.APTVCHNM, apy.APTODCTY, APY.APTODCNM, -APY.APPLDAMT, 0 , 0 ,@level
from PM30300 apy
--notice that we're getting distince invoices
join (select distinct APTVCHNM, APTODCTY from @apy) apy2 on apy.APTVCHNM = apy2.APTVCHNM and apy.APTODCTY = apy2.APTODCTY
--don't get lines that are already in our dataset
left join @apy apy3 on apy3.VCHRNMBR = apy.VCHRNMBR and apy3.DOCTYPE = apy.DOCTYPE and apy3.APTVCHNM = apy.APTVCHNM and apy3.APTODCTY = apy.APTODCTY
where apy3.VCHRNMBR is null
--if there are none, exit
if @@ROWCOUNT = 0 begin
set @Exit = 1
end
--we just added lines for the credits we found above. No
--insert apply lines where the payments have missing invoices
INSERT INTO @APY (OurVoucherNumber, YourVoucherNumber, Date1 , VCHRNMBR , DOCTYPE , APFRDCNM , APTVCHNM , APTODCTY , APTODCNM , Amount , AmountPaid , Net , Level)
SELECT apy.VCHRNMBR , apy.APFRDCNM , apy.docdate, apy.VCHRNMBR, apy.DOCTYPE, APY.APFRDCNM, apy.APTVCHNM, apy.APTODCTY, APY.APTODCNM, -APY.APPLDAMT, 0 , 0 , @level + 1
from PM30300 apy
--now were using distinct payments
join (select distinct vchrnmbr, doctype from @apy) apy2 on apy.VCHRNMBR = apy2.VCHRNMBR and apy.doctype = apy2.DOCTYPE --and apy.doctype in (5,6)
--don't get lines that are already in our dataset
left join @apy apy3 on apy3.VCHRNMBR = apy.VCHRNMBR and apy3.DOCTYPE = apy.DOCTYPE and apy3.APTVCHNM = apy.APTVCHNM and apy3.APTODCTY = apy.APTODCTY
where apy3.VCHRNMBR is null
--the 'level' just makes reading the output easier to understand.
--in our test data we got as high as 3 passes through this loop
set @level = @level + 10
end
--get the last set of invoices
--all othere inserts have been apply lines, this on is an invoice insert.
--in our test data one invoice had been paid by two credits and we needed just one line to mimic the source, not two,
--so we're pulling from PM30200
INSERT INTO @APY (OurVoucherNumber, YourVoucherNumber, Date1 , VCHRNMBR , DOCTYPE , APFRDCNM , APTVCHNM , APTODCTY , APTODCNM , Amount , AmountPaid , Net , Level)
SELECT inv.VCHRNMBR , inv.DOCNUMBR , inv.docdate, '' , 0 , '' , inv.VCHRNMBR, inv.DOCTYPE , inv.DOCNUMBR, inv.DOCAMNT , 0 , 0 , @level
from PM30200 inv
join (select distinct APTVCHNM, APTODCTY from @apy where level = @level - 29) apy2 on inv.VCHRNMBR = apy2.APTVCHNM and inv.doctype = apy2.APTODCTY --and apy.doctype in (5,6)
select OurVoucherNumber, YourVoucherNumber,Date1, Amount , AmountPaid , Net, level from @APY
GO
GRANT EXEC ON dd_RecreateCheckStub TO PUBLIC