cancel
Showing results for 
Search instead for 
Did you mean: 

Credit notes and AP Invoices

Former Member
0 Kudos

I would like a query for AP credit memo's and AP invoices in the one query - assistance would be greatly appreciated.

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

Please check this one and let me know what error messages if any:


SELECT DISTINCT T3.DocNum as 'Credit Note#', T3.DocDate as 
'CRN Date', T3.DocTotal as 'Credit Total',
T0.DocNum as 'Invoice#',  T0.DocDate as 'Invoice Date', 
T0.DocTotal as 'Invoice Total',
T0.CARDCODE as 'BP Code', T0.CardName as 'BP Name', 
T0.NUMATCARD as 'BP Ref'
FROM [dbo].[OPCH] T0
INNER JOIN [dbo].[PCH1] T1 ON T1.DocEntry = T0.DocEntry
INNER JOIN [dbo].[RPC1] T2 ON T2.BaseEntry = T1.DocEntry
INNER JOIN [dbo].[ORPC] T3 ON T3.DocEntry = T2.DocEntry
WHERE T0.DOCDATE >= '[%0]'

Answers (10)

Answers (10)

Former Member
0 Kudos

Works a treat thanks Gordon!

And for those who would like the same query for AR invoices and credit notes use this...

SELECT DISTINCT T3.DocNum as 'Credit Note#', T3.DocDate as

'CRN Date', T3.DocTotal as 'Credit Total',

T0.DocNum as 'Invoice#', T0.DocDate as 'Invoice Date',

T0.DocTotal as 'Invoice Total',

T0.CARDCODE as 'BP Code', T0.CardName as 'BP Name',

T0.NUMATCARD as 'BP Ref'

FROM [dbo].[OINV] T0

INNER JOIN [dbo].[INV1] T1 ON T1.DocEntry = T0.DocEntry

INNER JOIN [dbo].[RIN1] T2 ON T2.BaseEntry = T1.DocEntry

INNER JOIN [dbo].[ORIN] T3 ON T3.DocEntry = T2.DocEntry

WHERE T0.DOCDATE >= '[%0]'

Former Member
0 Kudos

sorry Gordon - no luck...

Former Member
0 Kudos

Check it again. I forgot to delete the last comma before from.

Former Member
0 Kudos

Says syntax error.

Former Member
0 Kudos

Hi Gordon,

The number does not related to a CR or an Invoice number.

Not sure if I was clear initially, I want to be able to run a report for where an AP invoice HAS a linked Credit Note, listing the AP Credit note, number , value, tax date and the AR invoice number, value, tax date etc.

Former Member
0 Kudos

That is for all Invoice and CN within some time.

Here is the linked one:


SELECT DISTINCT T3.DocNum 'CRN#', T3.DocDate 'CRN Date', T3.DocTotal 'Credit Total',T0.DocNum 'Invoice#', T0.CARDCODE 'BP Code', 
T0.CardName 'BP Name', T0.DocDate 'Invoice Date', 
T0.NUMATCARD 'BP Ref', T0.DocTotal 'Invoice Total'
FROM [dbo].[OPCH] T0
INNER JOIN [dbo].[PCH1] T1 ON T1.DocEntry = T0.DocEntry
INNER JOIN [dbo].[RPC1] T2 ON T2.BaseEntry = T1.DocEntry
INNER JOIN [dbo].[ORPC] T3 ON T3.DocEntry = T2.DocEntry
WHERE T0.DOCDATE >= '[%0]'

Former Member
0 Kudos

CR and Invoice numbers are still not appearing?!?

Former Member
0 Kudos

The number is between the text and BP code. Please check your query results again.

Thanks,

Gordon

Former Member
0 Kudos

Here you are:


SELECT 'INVOICE' "Doc Type",T0.DocNum, T0.CARDCODE 'BP Code', 
T0.CardName 'BP Name', T0.DocDate, T0.NUMATCARD 'BP Ref', 
T0.DocDueDate, T0.DocTotal
FROM [dbo].[OPCH] T0 WHERE T0.DOCDATE >= '[%0]'
UNION ALL
SELECT 'CREDIT MEMO', T0.DocNum,T0.CARDCODE, T0.CARDNAME, 
T0.DOCDATE, T0.NUMATCARD, T0.DOCDUEDATE, 
-1*T0.DOCTOTAL
FROM [dbo].[ORPC] T0 WHERE T0.DOCDATE >= '[%0]'

Former Member
0 Kudos

How can I include the Credit note and invoice numbers in the query please?

former_member186095
Active Contributor
0 Kudos

Just put t0.docnum in the query either the query uses the OPCH table and ORPC table.

Rgds,

Former Member
0 Kudos

Credit note number, AP invoice number and value would be a great start.

thanks Gordon

Former Member
0 Kudos

Try this first:


SELECT 'INVOICE' "Doc Type", T0.CARDCODE 'BP Code', 
T0.CardName 'BP Name', T0.DocDate, T0.NUMATCARD 'BP Ref', 
T0.DocDueDate, T0.DocTotal
FROM [dbo].[OPCH] T0 WHERE T0.DOCDATE >= '[%0]'
UNION ALL
SELECT 'CREDIT MEMO', T0.CARDCODE, T0.CARDNAME, 
T0.DOCDATE, T0.NUMATCARD, T0.DOCDUEDATE, 
-1*T0.DOCTOTAL
FROM [dbo].[ORPC] T0 WHERE T0.DOCDATE >= '[%0]'

Former Member
0 Kudos

Hi Lisa,

What fields do you want to show?

Thanks,

Gordon