Skip to Content
Mar 26, 2015 at 10:41 PM

Margin Check Against BOM


Hello All

I need a query that will check sales orders by date

Results display the price items were sold for, the profit the item cost when the sales order was entered

I also need it to display the current product price from the BOM

That is my problem

I can't figure out how to connect the table OITT to the query and display the value in the Product Price field

SELECT T0.[DocNum] as 'S/O No',

T0.[CardCode] as 'Sales Partner',

T0.[DocDate] as 'Posting Date',

T1.[ShipDate] as 'Shipped Date',

T1.[LineStatus] as 'Line Status',

T0.[U_Desc] as 'Project Name',

T1.[ItemCode] as 'Item Code',

T1.[Dscription] as 'Item Description',

T1.[Quantity] as 'Qty',

T1.[PriceBefDi] as 'Contract Price',

T1.[DiscPrcnt] as 'Discount %',

T1.[Price] as 'Price After Discount', T1.[LineTotal] as 'Total',

T1.[GrssProfit] as 'Gross Profit',

CASE WHEN T1.[LineTotal] = 0 THEN 0 ELSE T1.[GrssProfit]/T1.[LineTotal] END *100 as 'Profit %',

T1.[StockPrice] as 'Item Cost'

I need the Item Tree Matrix field to show here and after that the Product Price field

FROM ORDR T0 INNER JOIN RDR1 T1 ON T0.DocEntry = T1.DocEntry

WHERE T0.[DocDate] >=[%0] AND T0.[DocDate] <=[%1]

GROUP BY T0.[DocNum], T0.[CardCode], T0.[DocDate], T1.[ShipDate], T1.[LineStatus], T0.[U_Desc], T1.[ItemCode], T1.[Dscription], T1.[Quantity], T1.[PriceBefDi], T1.[DiscPrcnt], T1.[Price], T1.[LineTotal], T1.[GrssProfit], T1.[INMPrice], T1.[StockPrice] ORDER BY T0.[DocNum]

Would someone please advise me if this is possible or not

If possible, how do i go about getting the result i want

Thanks and Regards