Skip to Content
0
Aug 01, 2013 at 05:17 PM

Query shows invoice # twice because I have two lines but shows balance after payment with the total on each line and not the individual amount on each line.

16 Views

I was wondering how the query could be written so I do not have to manually edit some of the lines. Currently I run the query and in the formula for invoice total I say Total +Vat = Invoice total. And for "Balance after payment" if I have two lines on an invoice I have to delete one of the amounts as it duplicated the amount on both lines of the invoice. How can I have the report show this information without me having to manipulate the data? I have attached a screen shot of the fields and where I wipe out the amount on the balance after payment if I have two lines on an invoice.

Select T4.[SlpName] as 'Sales Employee', T0.cardname as 'Customer',T0.Docdate as 'Invoice Date', T0.docnum as 'Invoice Number', T0.[U_AIS_DVIInvName] as 'SF Invoice Name', T0.Taxdate as 'Month Of Service',isnull(T0.U_AIS_DVISFSO,T3.U_AIS_DVISFSO) as 'SO#',T0.NumAtCard as 'PO#',

isnull(T0.U_AIS_DVIAdvNm,T3.U_AIS_DVIAdvNm) as 'Advertiser', isnull(T0.U_AIS_DVIOpptyNm,T3.U_AIS_DVIOpptyNm) as 'Campaign',T1.Dscription,-T1.Quantity as 'Impressions',T1.Pricebefdi as 'CPM', -T1.linetotal as 'Total', -T1.[LineVat] as 'Vat Tax',

-T0.doctotal as 'Invoice Total',-(T0.Doctotal-T0.Paidsum) as 'Balance after payment',T0.docstatus, 'Credit' AS TransactionType, T0.CurSource, T0.DocCur, T0.[Comments]

from ORIN T0

left outer join RIN1 T1 on T0.docentry = T1.docentry

left outer join RDR1 T2 on T1.Baseentry = T2.docentry and T1.baseline = T2.linenum

left outer join ORDR T3 on T2.docentry = T3.docentry

left outer join OSLP T4 ON T0.Slpcode= T4.Slpcode

Where T0.[DocDate] >= '[%1]'

AND T0.[DocDate] <= '[%2]'

UNION ALL

Select T4.[SlpName] as 'Sales Employee', T0.cardname as 'Customer',T0.Docdate as 'Month of Service', T0.docnum as 'Invoice Number', T0.[U_AIS_DVIInvName] as 'SF Invoice Name', T0.Taxdate as 'Date Sent',isnull(T0.U_AIS_DVISFSO,T3.U_AIS_DVISFSO) as 'SO#',T0.NumAtCard as 'PO#',

isnull(T0.U_AIS_DVIAdvNm,T3.U_AIS_DVIAdvNm) as 'Advertiser', isnull(T0.U_AIS_DVIOpptyNm,T3.U_AIS_DVIOpptyNm) as 'Campaign',T1.Dscription,T1.Quantity as 'Impressions',T1.Pricebefdi as 'CPM', T1.linetotal as 'Total',T1.[LineVat] as 'Vat Tax',

T0.doctotal as 'Invoice Total',(T0.Doctotal-T0.Paidsum) as 'Balance after payment',T0.docstatus, 'Invoice' AS TransactionType, T0.CurSource, T0.DocCur, T0.[Comments]

from OINV T0

left outer join INV1 T1 on T0.docentry = T1.docentry

left outer join RDR1 T2 on T1.Baseentry = T2.docentry and T1.baseline = T2.linenum

left outer join ORDR T3 on T2.docentry = T3.docentry

left outer join OSLP T4 ON T0.Slpcode= T4.Slpcode

Where T0.[DocDate] >= '[%1]'

AND T0.[DocDate] <= '[%2]'