on 03-07-2015 2:24 PM
Hello All,
i am trying to figure out to exclude A/P Invoice that it was already Credit Memo with my existing Query
SELECT
max(T0.DocDate) as 'Date',
max(T1.CardName) as 'Vendor Name',
max(T0.Address) as 'Vendor Address',
count(T0.DocNum) as 'No. of Invoices',
max(T1.LicTradNum) as 'TIN No.',
sum(T0.BaseAmnt) as 'Base Amount',
sum(T0.VatSum) as 'Input Tax',
max(T1.ECVatGroup ) as 'TaxCode',
max(T2.WTCode) as 'WtaxCode'
FROM OPCH T0 INNER JOIN OCRD T1 ON T0.CardCode = T1.CardCode
left join PCH5 T2 on T0.DocEntry = T2.AbsEntry
WHERE T0.DocDate BETWEEN '[%0]' and '[%1]' and T0.VatSum <> 0
GROUP BY T1.CardCode
Please help mew revise my Query.
this is connected with my previous question but i cannot figure out how to incorporate PCH1.TargetEntry
Please Help.
Thank You.
Fidel
Hi,
Your query already written without considering Credit memo. There is no link between AP invoice and credit memo in above query.
Thanks & Regards,
Nagarajan
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hello Nagarajan,
thanks for the response.
My query retrieve A/P Invoices that is been credit memo, what i want is to exclude or NOT to include the A/P Invoices that was been Credit Memo. what i only want is the A/P invoices that was not Credit Memo.
will help me to revise my query to the my requirement?
thanks
Fidel
try the below query
SELECT
max(T0.DocDate) as 'Date',
max(T1.CardName) as 'Vendor Name',
max(T0.Address) as 'Vendor Address',
count(T0.DocNum) as 'No. of Invoices',
COUNT(T5.DocNum) AS 'No of Credit Memos',
max(T1.LicTradNum) as 'TIN No.',
sum(T0.BaseAmnt) as 'Base Amount',
sum(T0.VatSum) as 'Input Tax',
max(T1.ECVatGroup ) as 'TaxCode',
max(T2.WTCode) as 'WtaxCode'
FROM OPCH T0 INNER JOIN OCRD T1 ON T0.CardCode = T1.CardCode
INNER JOIN pch1 t3 ON t3.DocEntry=t0.DocEntry
LEFT JOIN dbo.RPC1 T4 ON T4.BaseEntry=T3.DocEntry left JOIN dbo.ORPC T5 ON T5.DocEntry=T4.DocEntry
left join PCH5 T2 on T0.DocEntry = T2.AbsEntry
WHERE T0.DocDate BETWEEN '[%0]' and '[%1]' and T0.VatSum <> 0
GROUP BY T1.CardCode
--Manish
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
108 | |
12 | |
11 | |
6 | |
5 | |
4 | |
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.