Freight Calculation

Hi All,

i want to calculate item wise the freight charges based on AP invoice. Ex. First i have to sum the the all line item QTY, Then divide the document freight by sum of document QTY. How to do this? Can you give the query for this??

Cheers!

Prabakaran R

10|10000 characters needed characters exceeded

Related questions

Posted on Jul 21, 2016 at 08:45 AM

Johan Hakkesteegt replied (in response to prabakaran R) 1 hour ago

Hi Prabakaran,

You can use a sub query like you have used already.

So you would get something like this:

T0.TotalExpns / (SELECT DISTINCT ISNULL (SUM(PCH1.Quantity),0) FROM PCH1 WHERE PCH1.DocEntry = T0.DocEntry)

To get a weighted division of the freight, you could then multiply this value by the row's quantity:

(T0.TotalExpns / (SELECT DISTINCT ISNULL (SUM(PCH1.Quantity),0) FROM PCH1 WHERE PCH1.DocEntry = T0.DocEntry)) * T1.Quantity

Regards,

Johan

This query is working fine....Thanks Johan Hakkesteegt ... 😊

10|10000 characters needed characters exceeded
• Posted on Jul 21, 2016 at 07:11 AM

Dear Prabakaran

Before you add or save as draft, it seems not easy to capture the qty.

Regards

Edmund

10|10000 characters needed characters exceeded
• Edmund Leung prabakaran R

Dear Prabakaran

SELECT T1.[TotalExpns]*T0.quantity/(select sum(T00.[Quantity]) from PCH1 T00 where T00.docentry=T0.docentry), T0.[ItemCode] FROM [dbo].[PCH1] T0 INNER JOIN OPCH T1 ON T0.[DocEntry] = T1.[DocEntry] WHERE T1.[DocNum] =[%0]

I am not sure that you want or not.

Regards

Edmund