Skip to Content
0
Former Member
Dec 10, 2022 at 11:51 AM

Wants customer refernce number field in Sales Invoice Query

103 Views

Hello Experts,

Below is the query for which i wants customer reference number field..

Please help

SELECT 1, T4.SeriesName, T0.DOCNUM AS 'Invoice No.', T0.DocEntry 'Actual Invoice No.', (Case When T0.DocStatus='C' Then 'Closed' Else 'Open' End) 'Status', T0.jrnlMemo as 'Document Status', T0.DOCDATE, T0.DocDueDate, T0.CARDCODE AS 'Customer Code', T0.CARDNAME AS 'Customer Name', T1.ITEMCODE, T1.SubCatNum 'BP Catalogue No.', T1.FreeTxt AS 'Free Text', T1.DSCRIPTION AS 'Item Description', T1.QUANTITY AS 'Ordered Quantity', T1.PriceBefDi 'Price Before Discount', T1.DiscPrcnt, T1.Price 'Price After Discount', T0.DocCur, (T1.QUANTITY-T1.OPENQTY) AS 'Received Quantity', T1.OPENQTY AS 'Balanced Quantity', T0.DOCTOTAL-T0.VATSUM AS 'Amount Before Tax', T0.VATSUM AS 'Tax', T0.DOCTOTAL AS 'Net Amount After Tax', T3.SLPNAME AS 'Buyer Name' FROM OINV T0, INV1 T1, OCRD T2, OSLP T3, NNM1 T4 WHERE T0.DOCENTRY=T1.DOCENTRY AND T0.CARDCODE=T2.CARDCODE AND T0.SLPCODE=T3.SLPCODE AND T0.Series=T4.Series And T0.DOCDATE>='[%0]' AND T0.DOCDATE<='[%1]'

Regards

Malay Gandhi