on 09-26-2008 1:58 AM
I would like a query for AP credit memo's and AP invoices in the one query - assistance would be greatly appreciated.
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]'
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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]'
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
sorry Gordon - no luck...
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Check it again. I forgot to delete the last comma before from.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Says syntax error.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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]'
CR and Invoice numbers are still not appearing?!?
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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]'
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
How can I include the Credit note and invoice numbers in the query please?
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Credit note number, AP invoice number and value would be a great start.
thanks Gordon
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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]'
Hi Lisa,
What fields do you want to show?
Thanks,
Gordon
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
91 | |
8 | |
7 | |
4 | |
4 | |
3 | |
3 | |
3 | |
3 | |
3 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.