-- no cursors are required here:

-- Construct a table from the list of invoice numbers

IF object_id('TempDB..#InvoiceList') IS NOT NULL

DROP TABLE #InvoiceList

SELECT inv_num

INTO #InvoiceList

FROM (

SELECT inv_num =

dbo.ExpandKy(10, @Inv1) UNION ALL

dbo.ExpandKy(10, @Inv2) UNION ALL

dbo.ExpandKy(10, @Inv3) UNION ALL

dbo.ExpandKy(10, @Inv4) UNION ALL

dbo.ExpandKy(10, @Inv5) UNION ALL

dbo.ExpandKy(10, @Inv6) UNION ALL

dbo.ExpandKy(10, @Inv7) UNION ALL

dbo.ExpandKy(10, @Inv8) UNION ALL

dbo.ExpandKy(10, @Inv9) UNION ALL

dbo.ExpandKy(10, @Inv10) UNION ALL

dbo.ExpandKy(10, @Inv11) UNION ALL

dbo.ExpandKy(10, @Inv12) UNION ALL

dbo.ExpandKy(10, @Inv13) UNION ALL

dbo.ExpandKy(10, @Inv14) UNION ALL

dbo.ExpandKy(10, @Inv15) UNION ALL

dbo.ExpandKy(10, @Inv16) UNION ALL

dbo.ExpandKy(10, @Inv17) UNION ALL

dbo.ExpandKy(10, @Inv18) UNION ALL

dbo.ExpandKy(10, @Inv19) UNION ALL

dbo.ExpandKy(10, @Inv20)

) d

WHERE inv_num IS NOT NULL

-- reconstruct the credits query and run results into temp table #Credits

SELECT

Belegdatum= CONVERT(datetime, ih.inv_date, 104),

Buchungsdatum= GETDATE(), -- GETDATE() doesn't need converting to DATETIME

Buchungstext= ca.name + ' ' + dbo.NonExpandKy(10, ih.inv_num) + '/1', --The concatenation of fields for the @InvText

Buchungsbetrag= ih.price, --@InvAmt

Sollkonto= '',

Habenkonto= (SELECT z.Uf_FTOldCustNo FROM customer z WHERE z.cust_num = c.cust_num and z.cust_seq = 0) --Credit Account Numbers

INTO #Credits

FROM inv_hdr ih

JOIN customer c

ON ih.cust_num = c.cust_num AND c.cust_seq = ih.cust_seq

INNER JOIN custaddr ca

ON ih.cust_num = ca.cust_num AND ih.cust_seq = ca.cust_seq

WHERE ih.inv_num IN (SELECT inv_num FROM #InvoiceList)

AND ih.inv_num like 'G%'

AND ih.price > 0

-----------------------------------------------------------------------

-- reconstruct the debits query and run results into temp table #debits

SELECT

Belegdatum= InvDate,

Buchungsdatum= BookDate,

Buchungstext= InvText,

Buchungsbetrag= ((LinePrice - LineDisc) * LineTax) + (LinePrice - LineDisc),

Sollkonto= DEBIT,

Habenkonto= CREDIT

INTO #Debits

FROM (

SELECT

InvDate= CONVERT(DATETIME, ih.inv_date, 104),

BookDate= GETDATE(),

InvText= ca.name + ' ' + dbo.NonExpandKy(10, ii.inv_num) + '/1',

LinePrice= ii.qty_invoiced * ii.price,

LineDisc= ih.disc_amount / COUNT(ii.inv_line) OVER(PARTITION BY ih.inv_num), --The disc_amount is a whole number in the header table, not line by line

LineTax= CASE

WHEN ih.tax_code1 IN ('EU', 'NON-EU') THEN 0

ELSE ix.tax_rate/100 END,

[DEBIT]= CASE

WHEN ih.tax_code1 = 'EU' THEN x.eu_account

WHEN ih.tax_code1 = 'NON-EU' THEN x.noneu_account

WHEN ih.tax_code1 IS NULL THEN x.inland_account

END,

[CREDIT] = NULL

FROM inv_hdr ih

INNER JOIN inv_item ii on ih.inv_num = ii.inv_num

CROSS APPLY ( -- read the lookup just once

SELECT eu_account, noneu_account, inland_account

FROM ViewForTaxAccts xi

WHERE xi.item = ii.item

) x

LEFT JOIN inv_stax ix on ih.inv_num = ix.inv_num

INNER JOIN item i on ii.item = i.item

INNER JOIN customer c on ih.cust_num = c.cust_num and c.cust_seq = ih.cust_seq

INNER join custaddr ca on ih.cust_num = ca.cust_num and ih.cust_seq = ca.cust_seq

WHERE ih.inv_num IN (SELECT inv_num FROM #InvoiceList)

AND ih.inv_num LIKE 'G%'

AND ii.price > 0

) d

-- now compare the two temp tables, #Credits and #Debits

-- consider aggregating the #debits table