Skip to Content

Item wise Arrival Cost

Hi All,

I am purchasing raw materials from Local and import vendors. Now i want to know the item arriving cost based on the AP invoice including all tax & Freight Etc. Because i am purchasing same item from different vendor, so i want to know the invoice wise item arriving cost......

Cheers!

Prabakaran R

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

1 Answer

  • Posted on Jul 19, 2016 at 05:29 PM

    Hi,

    Use the purchase analysis report to identify the purchase price of the item based on suppliers.

    PURCHASING --> PURCHASING REPORTS --> PURCHASE ANALYSIS

    in the selection criteria select items tab and enter the particular from and to item code. then select ok. the report will generate with the total purchase value.

    Then double click on the serial no such that you can get the list of purchases happened for the particular item code from different suppliers

    Regards,

    Vinoth

    Add a comment
    10|10000 characters needed characters exceeded

    • Hi Vinoth,

      Your query shows some error in my data base......below is my query can you plz give the idea for this. In this query i got all the fields what i want. But now i want calculate line wise freight and Tax per QTY. Then only i can find the arrival cost of raw materials.

      DECLARE

      @StartDate DATETIME,

      @EndDate DATETIME,

      @Dummy INTEGER

      SELECT TOP 1 @Dummy = DocNum

      FROM OPCH T0

      WHERE T0.DocDate >= [%1]

      AND T0.DocDate <= [%2]

      SELECT

      @StartDate= '[%1]' ,

      @EndDate = '[%2]'

      SELECT DISTINCT

      T0.DocEntry,

      T0.DocNum AS 'AP Inv Num',

      CONVERT(VARCHAR(10), T0.DocDate, 3) AS 'Bill Date',

      T0.CardCode AS 'Vendor Code',

      T2.CardName AS 'Vendor Name',

      T3.GroupName AS 'Vendor Group',

      T1.ItemCode AS 'ItemCode',

      T1.Dscription AS 'Item Description',

      T1.Quantity,

      T1.Price AS 'Price',

      T1.TaxCode,

      T0.TotalExpns AS 'Freight',

      (SELECT DISTINCT ISNULL (SUM(PCH4.TaxSum),0)

      FROM PCH4

      WHERE PCH4.StaType = -90 AND PCH4.Taxrate=12.5

      AND PCH4.DocEntry = T0.DocEntry

      AND PCH4.LineNum = T1.LineNum) AS 'BED 12.5%',

      (SELECT DISTINCT ISNULL (SUM(PCH4.TaxSum),0)

      FROM PCH4

      WHERE PCH4.StaType = -90 AND PCH4.Taxrate=12

      AND PCH4.DocEntry = T0.DocEntry

      AND PCH4.LineNum = T1.LineNum) AS 'BED 12%',

      (SELECT DISTINCT ISNULL (SUM(PCH4.TaxSum),0)

      FROM PCH4

      WHERE PCH4.StaType = -90 AND PCH4.Taxrate=10

      AND PCH4.DocEntry = T0.DocEntry

      AND PCH4.LineNum = T1.LineNum) AS 'BED 10%',

      (SELECT DISTINCT ISNULL (SUM(PCH4.TaxSum),0)

      FROM PCH4

      WHERE PCH4.StaType = -90 AND PCH4.Taxrate=8

      AND PCH4.DocEntry = T0.DocEntry

      AND PCH4.LineNum = T1.LineNum) AS 'BED 8%',

      (SELECT DISTINCT ISNULL (SUM(PCH4.TaxSum),0)

      FROM PCH4

      WHERE PCH4.StaType = -90 AND PCH4.Taxrate=6

      AND PCH4.DocEntry = T0.DocEntry

      AND PCH4.LineNum = T1.LineNum) AS 'BED 6%',

      (SELECT DISTINCT ISNULL (SUM(PCH4.TaxSum),0)

      FROM PCH4

      WHERE PCH4.StaType = -60

      AND PCH4.DocEntry = T0.DocEntry

      AND PCH4.LineNum = T1.LineNum) AS 'Cess',

      (SELECT DISTINCT ISNULL (SUM(PCH4.TaxSum),0)

      FROM PCH4

      WHERE PCH4.StaType = -55

      AND PCH4.DocEntry = T0.DocEntry

      AND PCH4.LineNum = T1.LineNum) AS 'HeCess',

      (SELECT DISTINCT ISNULL (SUM(PCH4.TaxSum),0)

      FROM PCH4

      WHERE PCH4.StaType = 1 and PCH4.Taxrate = 14.5

      AND PCH4.DocEntry = T0.DocEntry

      AND PCH4.LineNum = T1.LineNum) AS 'VAT 14.5%',

      (SELECT DISTINCT ISNULL (SUM(PCH4.TaxSum),0)

      FROM PCH4

      WHERE PCH4.StaType = 1 and PCH4.Taxrate = 5

      AND PCH4.DocEntry = T0.DocEntry

      AND PCH4.LineNum = T1.LineNum) AS 'VAT 5%',

      (SELECT DISTINCT ISNULL (SUM(PCH4.TaxSum),0)

      FROM PCH4

      WHERE PCH4.StaType = 4

      AND PCH4.DocEntry = T0.DocEntry

      AND PCH4.LineNum = T1.LineNum) AS 'CST 2%',

      (SELECT DISTINCT ISNULL (SUM(PCH4.TaxSum),0)

      FROM PCH4

      WHERE PCH4.StaType = -80

      AND PCH4.DocEntry = T0.DocEntry

      AND PCH4.LineNum = T1.LineNum) AS 'AED',

      (SELECT DISTINCT ISNULL (SUM(PCH4.TaxSum),0)

      FROM PCH4

      WHERE PCH4.StaType = -70

      AND PCH4.DocEntry = T0.DocEntry

      AND PCH4.LineNum = T1.LineNum) AS 'SED',

      T1.[VatSum],T1.[LineTotal]

      FROM OPCH T0

      INNER JOIN PCH1 T1

      ON T0.DocEntry = T1.DocEntry INNER JOIN OCRD T2 ON T0.CardCode=T2.CardCode INNER JOIN OCRG T3 ON T3.GroupCode=T2.GroupCode

      WHERE T0.DocType = 'I' AND T0.DocDate >= @StartDate

      AND T0.DocDate <= @EndDate

      AND T0.DocType ='I'

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.