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...

Thursday, February 23, 2017

Smartlist Export Failure - Excel Cannot open file because the File Fomrat or File Extension is not valid


I have received recently a quite "weird" support case, in which the smart list is failing to export data into Excel addressing that Excel can not open the file. Here is the exact error message:

"Excel Cannot open file because the  file format or file extension is not valid. Verify that the file has not been corrupted and that the file extension matches the format of the file"


Smart list export error

After several troubleshooting attempts suspecting that Excel is the source root cause, it rather appeared that "data" which is being exported is the root-cause of this error. It was found out that the data contains special characters.  Here is the screen shot of the "corrupted" data record which was failing the whole process:


An arrow Symbol (Special Character)


Best Regards, 
Mahmoud M. AlSaadi

Wednesday, February 22, 2017

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