Skip to Content
Jun 28, 2020 at 09:27 AM

A/P invoice/Credit memo

92 Views Last edit Jul 08, 2020 at 01:48 AM 2 rev

Hi. can someone change my code so that the output of my selection criteria should be like this --> query.png and the data should display vendor name that i choose.

SELECT T3.[GroupName], T0.[DocDate] as 'InvoiceDate', T0.[DocDueDate] as 'InvoiceDueDate',T0.[DocNum] as 'InvoiceNum', t4.docnum as 'OutgoingPaymentNo.',case when t4.DocDate ='' then null else t4.docdate end as 'OutgoingPaymentDate', T1.[BaseDocNum] as 'GRPO/Invoice', T0.[NumAtCard] as 'Vendor Ref. No.', T0.[CardCode], T0.[CardName], sum(T1.[LineTotal]) as Total, sum(T1.[TotalFrgn]) as TotalFrgn, T1.Currency,T1.[VatGroup],T0.[Comments] FROM OPCH T0 INNER JOIN PCH1 T1 ON T0.[DocEntry] = T1.[DocEntry] INNER JOIN OCRD T2 ON T0.[CardCode] = T2.[CardCode] INNER JOIN OCRG T3 ON T2.[GroupCode] = T3.[GroupCode] left join ovpm t4 on t4.docentry = t0.ReceiptNum WHERE T0.[DocDate] >= [%0] and T0.[DocDate] <=[%1] group by GroupName,T0.DocDate,T0.DocDueDate,t0.DocNum,t4.DocNum,t4.docdate,BaseDocNum,NumAtCard,T0.CardCode,T0.CardName,T1.Currency, T1.[VatGroup],T0.Comments UNION select 'Credit memo' as GroupName, a.DocDate as 'InvoiceDate', a.DocDueDate as 'InvoiceDueDate',a.DocNum as 'InvoiceNum', '','',b.BaseDocNum as 'GRPO/Invoice',a.NumAtCard as 'Vendor Ref. No.', a.CardCode, a.CardName, sum(b.LineTotal*-1) as 'Total', sum(b.TotalFrgn*-1) as 'TotalFrgn',b.Currency,b.VatGroup, b.Dscription as 'Comments' from orpc a inner join rpc1 b on a.DocEntry = b.DocEntry --inner join ovpm c on c.docentry = WHERE a.DocDate between [%0] and [%1] group by a.docdate,a.DocDueDate,a.docnum,b.BaseDocNum,a.numatcard,a.cardcode,a.cardname,b.Currency,b.VatGroup,b.Dscription


query.png (5.6 kB)