Skip to Content
avatar image
Former Member

how to create a query of A/R Invoice Amount with related PO Number and SO number

Hi Experts,

I have written a query with invoice details,below is the query.

Invoice Query:

SELECT T0.DocNum, T1.BaseRef, T0.DocDate, T0.CardCode AS [Customer], T0.CardName, T1.U_OB_CustPO AS [Customer PO], T1.ItemCode AS [Item No.], T1.U_OB_CustItemCode AS [Customer Item Code], T1.Dscription AS [Item/Service Description], T1.Quantity, T1.LINETOTAL AS [LineTotal(SGD)], LINETOTAL - T1.GrssProfit AS [Cost(SGD)], T1.GrssProfit, CASE WHEN T1.linetotal = 0.00 THEN ((t1.GrssProfit / 1) * 100) ELSE ((t1.GrssProfit / T1.linetotal) * 100) END AS [GP%(SGD)], T0.DocCur, T1.TOTALFRGN AS [Linetotal(FC)], TOTALFRGN - T1.GrssProfFC AS [Cost(FC)], T1.GrssProfFC, T1.U_OB_VendorCode AS [Vendor Code], T1.U_OB_Vendor AS [Vendor Name], T4.ItmsGrpNam AS [BU], T6.GroupName AS [Country], T7.SlpName AS [Salesman] FROM OINV T0 INNER JOIN INV1 T1 ON T1.DocEntry = T0.DocEntry INNER JOIN OCRD T5 ON T5.CARDCODE = T0.CARDCODE INNER JOIN OITM T2 ON T2.ItemCode = T1.ItemCode INNER JOIN OMRC T3 ON T3.FirmCode = T2.FirmCode INNER JOIN OITB T4 ON T4.ItmsGrpCod = T2.ItmsGrpCod INNER JOIN OCRg T6 ON T6.GroupCode = T5.GroupCode INNER JOIN OSLP T7 ON T7.SlpCode = T0.SlpCode WHERE T0.DocType = 'I'

I have a requirement that,for each invoice number i have to show the SO Number, PO Number, PO amount in invoice query.

Can any one help me with the query, it's bit urgent.



Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

0 Answers