Featured Post

Dynamics GP 2019 is now Released

It is now released, Microsoft Dynamics GP 2019 which moves the system to the modern product life cycle   " Beginning with the October...

Sunday, April 27, 2014

Cost Layers Analysis – SQL Script

 

Understanding how cost layers are sorted out according to the valuation method configured is important, it helps anticipating the cost to be withdrawn. In addition, when this is combined with the sales details such as unit price, several indicators can be figured out.

In this post, I am providing an SQL script which sorts out all the inventory purchase receipts (In documents) and associated details (Out documents). In case the out document is a sales invoice, associated sales invoice details are provided. This is illustrated below;

1

Cost layer status can be either one of the following:

  • Closed: All the quantity received have been consumed
  • Open: Partial or all of the quantity received is available for future consumption

Helping Note !

The prefix (In) is used to distinguish details retrieved from purchase receipt work (IV10200). While the prefix (Out) is used to distinguish data retrieved from purchase receipt work details (IV10201). Additionally, (SLS) is used to distinguish data retrieved from (SOP30300).

2

 

 

 

 

Tables Included:

·         IV10200  | Purchase Receipt Work

·         IV10200  | Purchase Receipt Work Details

·         SOP30300 | Sales Transactions Amounts History

 

 

SELECT  CASE A.RCPTSOLD

          WHEN 1 THEN 'Closed'

          WHEN 0 THEN 'Open'

          ELSE 'NA'

        END AS 'Cost Layer Status' ,

        A.RCPTNMBR AS 'In Receipt Number' ,

        CASE A.PCHSRCTY

          WHEN 1 THEN 'Adjustment'

          WHEN 2 THEN 'Variance'

          WHEN 3 THEN 'Transfer'

          WHEN 4 THEN 'Override'

          WHEN 5 THEN 'Receipt'

          WHEN 6 THEN 'Return'

          WHEN 7 THEN 'Assembly'

          WHEN 8 THEN 'In-Transit'

          ELSE 'NA'

        END AS 'In Transaction Type' ,

        A.DATERECD AS 'In Date Received' ,

        A.ITEMNMBR AS 'In Item Number' ,

        A.TRXLOCTN AS 'In Transaction Location' ,

        A.QTYRECVD AS 'In Quantity Received' ,

        A.QTYSOLD AS 'In Quantity Sold' ,

        A.UNITCOST AS 'In Unit Cost' ,

        A.RCTSEQNM AS 'In Receipt Sequence Number' ,

        ISNULL(B.ORIGInDOCID, ' ') AS 'Out Document Number' ,

        ISNULL(B.DOCDATE, ' ') AS 'Out Document Date' ,

        ISNULL(B.ITEMNMBR, ' ') AS 'Out Item Number' ,

        ISNULL(B.TRXLOCTN, ' ') AS 'Out Transaction Location' ,

        ISNULL(B.QTYSOLD, 0) AS 'Out Quantity Sold' ,

        ISNULL(B.UNITCOST, 0) AS 'Out Unit Cost' ,

        ISNULL(B.SRCRCTSEQNM, ' ') AS 'Out Source Receipt Sequence Number' ,

        ISNULL(C.SOPTYPE, ' ') AS 'SLS SOP Type' ,

        ISNULL(C.SOPNUMBE, ' ') AS 'SLS SOP Number' ,

        ISNULL(C.UNITPRCE, 0) AS 'SLS SOP Unit Price'

FROM    IV10200 AS A

        LEFT OUTER JOIN IV10201 AS B ON A.ITEMNMBR = B.ITEMNMBR

                                        AND A.TRXLOCTN = B.TRXLOCTN

                                        AND A.RCTSEQNM = B.SRCRCTSEQNM

        LEFT OUTER JOIN ( SELECT    CASE SOPTYPE

                                      WHEN 1 THEN 'Quote'

                                      WHEN 2 THEN 'Order'

                                      WHEN 3 THEN 'Invoice'

                                      WHEN 4 THEN 'Return'

                                      WHEN 5 THEN 'Back Order'

                                      WHEN 6 THEN 'Fulfillment Order'

                                      ELSE 'NA'

                                    END AS SOPTYPE ,

                                    SOPNUMBE ,

                                    ITEMNMBR ,

                                    LOCNCODE ,

                                    UNITCOST ,

                                    UNITPRCE

                          FROM      SOP30300

                        ) AS C ON B.ORIGInDOCID = C.SOPNUMBE

                                  AND B.ITEMNMBR = C.ITEMNMBR

                                  AND B.TRXLOCTN = C.LOCNCODE

 

 

Best Regards,
Mahmoud M. AlSaadi

7 comments:

  1. Dear Mahmood
    When I making receiving the system is splitting the cost while posting. which cost will taken for invoicing and a assembly transaction.

    ReplyDelete
    Replies
    1. There is absolutely no issue with having the cost layer split over multiple ones, there is only one purpose for that; which is ensuring accurate costing and handle the rounding decimals.

      Check this out and let me know if you have any further concerns or issues
      http://mahmoudsaadi.blogspot.com/2013/07/regardless-of-fact-that-dynamics-gp.html

      Best Regards,
      Mahmoud M. AlSaadi

      Delete
    2. Hi Mahmoud,
      in our case when we sale the item system select second layer cost which is higher so our cost of material is going more than our sales amount.
      Example:
      Item A layer 1 qty 9 cost 0.0033
      Item A layer 2 qty 1 cost 2.0000
      sales invoice
      item A qty 5 cost 5 * 2 =10 sales Amount 5*1=5
      why the system is select higher cost not less cost?

      Delete
    3. and item maintenance window current cost field is updated by 2 SAR Amount

      Delete
    4. The issue has been thoroughly explained on this previous post, check it out
      http://mahmoudsaadi.blogspot.com/2014/12/revisiting-average-costing-in-dynamics.html

      Your feedback is highly appreciated,

      Delete