Margin Check Against BOM

[RahF]

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

Rahul

[Former Member · Accepted solution]

Hi Rahul,

Try this

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',

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',

SUM(T2.Price*T2.Quantity) as 'ComponentTotal',

T4.Price as 'ProductPrice'

FROM ORDR T0 

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

INNER JOIN ITT1 T2 ON T1.ItemCode=T2.Father

LEFT JOIN OITT T3 ON T1.ItemCode=T3.Code

LEFT JOIN ITM1 T4 ON T1.ItemCode=T4.ItemCode and T3.PriceList=T4.PriceList

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

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

[RahF]

Hi Jun

In this query, in the results the status is displayed in C or O

Would it be possible for it to display the word Closed, Open or Cancelled?

Thanks and Regards

Rahul

[Former Member]

HI Rahul,

You can just use a case statement. case when C then Closed.. something like that

Regards

JP

[RahF]

Hi Jun

I thought of that, but for some reason if a sales order is cancelled

Even that status is displayed as C

So if I use the case statement, it will show everything that has a status as C. Closed or Cancelled as Closed

Thanks and Regards

Rahul

[Former Member]

Hi Rahul,

You can use ORDR.Canceled when Y it means canceled

Hope this helps

JP

[RahF]

Hi Jun

It works

Thanks a lot for your help

Thanks and Regards

Rahul