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
