Skip to Content

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

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

2 Answers

  • Best Answer
    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 ... 😊

    Add a comment
    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

    Add a comment
    10|10000 characters needed characters exceeded

    • 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

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.