Shipments Landed Cost Report - Item Direct and Indirect Unit Cost

Shipments could have additional charges such as Freight, Customs, Insurance ...etc. These charges are called Landed Cost in Dynamics GP and can be build up on the item level, through which, each item could be assigned to a specific set of landed cost charges. In this essence, the item cost includes both; direct and indirect unit cost. 

 

In this article, I am providing an SQL script which provides sufficient details for both cost factors (direct and indirect unit cost) in details as illustrated below:

 

 

Receipt Direct Unit Cost

Receipt Indirect Unit Cost (Landed Cost)

Below is an SQL script which retrieves data primarily from several purchase sources (POP Tables), along with the Landed Cost table in order to provide a detailed view of the direct and indirect unit cost details.

Sample Data Set Result

 The details included in this report are:

VENDORID

VENDNAME

BACHNUMB

VNDDOCNM

receiptdate

SUBTOTAL

POPRCTNM

PONUMBER

ITEMNMBR

ITEMDESC

LOCNCODE

VNDITNUM

VNDITDSC

UOFM

QTY Shipped

Unit Cost Before Landed Cost

Unit Landed Cost

Extended Cost Before Landed Cost

Landed_Cost_ID

Long_Description

Landed_Cost_Type

Orig_Landed_Cost_Amount

Calculation_Percentage

Total_Landed_Cost_Amount

Orig_TotalLandedCostAmt

 

 

 

SELECT  Z.VENDORID ,

        Z.VENDNAME ,

        Z.BACHNUMB ,

        Z.VNDDOCNM ,

        Z.receiptdate ,

        Z.SUBTOTAL ,

        X.POPRCTNM ,

        X.PONUMBER ,

        X.ITEMNMBR ,

        X.ITEMDESC ,

        X.LOCNCODE ,

        X.VNDITNUM ,

        X.VNDITDSC ,

        X.UOFM ,

        N.[QTY Shipped] ,

        X.UNITCOST AS 'Unit Cost Before Landed Cost' ,

             Y.Total_Landed_Cost_Amount / N.[QTY Shipped] AS 'Unit Landed Cost' ,

        X.EXTDCOST AS 'Extended Cost Before Landed Cost' ,

        Y.Landed_Cost_ID ,

        Y.Long_Description ,

        Y.Landed_Cost_Type ,

             Y.Orig_Landed_Cost_Amount  AS Orig_Landed_Cost_Amount ,

        Y.Calculation_Percentage ,

        Y.Total_Landed_Cost_Amount,

             Y.Orig_TotalLandedCostAmt

FROM    POP30310 AS X

        LEFT OUTER JOIN POP30700 AS Y ON X.POPRCTNM = Y.POPRCTNM

                                         AND X.RCPTLNNM = Y.RCPTLNNM

        RIGHT JOIN dbo.POP30300 AS Z ON X.POPRCTNM = Z.POPRCTNM

        LEFT OUTER JOIN ( SELECT DISTINCT

                                    [POP Receipt Number] ,

                                    [Vendor ID] ,

                                    [POP Type] ,

                                    [Item Number] ,

                                    [QTY Shipped]

                          FROM      ReceivingsLineItems

                        ) AS N ON ( CASE Z.POPTYPE

                                      WHEN 1 THEN 'Shipment'

                                      WHEN 2 THEN 'Invoice'

                                      WHEN 3 THEN 'Shipment/Invoice'

                                      ELSE ''

                                    END ) = N.[POP Type]

                                  AND N.[POP Receipt Number] = Z.POPRCTNM

                                  AND N.[Item Number] = X.ITEMNMBR

                                  AND N.[Vendor ID] = Z.VENDORID

--WHERE   X.POPRCTNM = 'RCT1167'

AND Y.LCHDRNUMBER <> 0 AND Y.VENDORID <> ''

 

 

 

 

Best Regards,

Mahmoud M. AlSaadi