cancel
Showing results for 
Search instead for 
Did you mean: 

A/P invoice/Credit memo

shikin
Participant
0 Kudos

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

Accepted Solutions (1)

Accepted Solutions (1)

kothandaraman_nagarajan
Active Contributor
0 Kudos

Hi,

Try this query,

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] and T0.CardCode = '[%2]' 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 WHERE a.DocDate between [%0] and [%1] and a.Cardcode = '[%2]' group by a.docdate,a.DocDueDate,a.docnum,b.BaseDocNum,a.numatcard,a.cardcode,a.cardname,b.Currency,b.VatGroup,b.Dscription

Regards,

Nagarajan

shikin
Participant
0 Kudos

Hi Nagarajan,

Than you so much! It works!

Answers (0)