Dynamics GP - FIFO Invoicing and Rounding Causes IV00101.CURRCOST field to change unexpectedly. SQL view to Get True last received cost.

When using Average Costing
-Purchase Receipt updates current cost
-Enter/Match invoice price variance only affects gl

When using FIFO
-Purchase Receipt updates current Cost (Every invoice changes the currcost, if you are unlucky and get a rounding entry in the iv00102, this large value can end up as your current cost)
-Enter/Match invoice updates current cost to invoice cost + landed cost, and affects gl

------------------------------------------------------------
CREATE VIEW [dbo].[BI_INV_LastRcvCost0]
AS
SELECT        dbo.IV10200.ITEMNMBR, MAX(dbo.IV10200.ADJUNITCOST) AS LastRcvCost, LastRcvDt.LastRcvDate
FROM            dbo.IV10200 INNER JOIN
                             (SELECT        ITEMNMBR, MAX(DATERECD) AS LastRcvDate
                               FROM            dbo.IV10200 AS IV10200_1
                               GROUP BY ITEMNMBR) AS LastRcvDt ON dbo.IV10200.ITEMNMBR = LastRcvDt.ITEMNMBR AND dbo.IV10200.DATERECD = LastRcvDt.LastRcvDate
GROUP BY dbo.IV10200.QTYRECVD, dbo.IV10200.ITEMNMBR, LastRcvDt.LastRcvDate
HAVING        (dbo.IV10200.QTYRECVD > 1)
GO
------------------------------------------------------------

CREATE VIEW [dbo].[BI_INV_LastRcvCost]
AS
SELECT        dbo.IV00101.ITEMNMBR, MAX(CASE WHEN LastRcvCost IS NULL THEN Currcost WHEN LastRcvCost = 0 THEN Currcost ELSE LastRcvCost END) AS LastRcvCost, MAX(CASE WHEN LastRcvDate IS NULL
                         THEN '1900-01-01' ELSE LastRcvDate END) AS LastRcvDt
FROM            dbo.IV00101 LEFT OUTER JOIN
                         dbo.BI_INV_LastRcvCost0 ON dbo.IV00101.ITEMNMBR = dbo.BI_INV_LastRcvCost0.ITEMNMBR
GROUP BY dbo.IV00101.ITEMNMBR
GO

---------------------------------------------------------------

Dynamics GP - SSRS HITB View and SP

---------------------------------------------------------------------------------------------------
--HITB Summary
---------------------------------------------------------------------------------------------------
/****** Object:  Table [dbo].[BIT_HITBTemp]    Script Date: 10/01/2018 04:57:21 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[BIT_HITBTemp](
[ACTNUMBR_1] [char](6) NOT NULL,
[ACTNUMBR_2] [char](6) NOT NULL,
[ACTNUMBR_3] [char](6) NOT NULL,
[ACTNUMBR_4] [char](6) NOT NULL,
[ACTNUMBR_5] [char](6) NOT NULL,
[ACTNUMBR_6] [char](6) NOT NULL,
[ACTNUMBR_7] [char](6) NOT NULL,
[ACTNUMBR_8] [char](6) NOT NULL,
[ACTNUMBR_9] [char](6) NOT NULL,
[ACTNUMBR_10] [char](6) NOT NULL,
[ITEMNMBR] [char](31) NOT NULL,
[LOCNCODE] [char](11) NOT NULL,
[DOCDATE] [datetime] NOT NULL,
[GLPOSTDT] [datetime] NOT NULL,
[JRNENTRY] [int] NOT NULL,
[SEQNUMBR] [int] NOT NULL,
[ITEMDESC] [char](101) NOT NULL,
[ITMGEDSC] [char](11) NOT NULL,
[DOCNUMBR] [char](21) NOT NULL,
[DOCTYPE] [smallint] NOT NULL,
[TRXSORCE] [char](13) NOT NULL,
[TRXREFERENCE] [smallint] NOT NULL,
[HSTMODUL] [char](3) NOT NULL,
[TRXQTY] [numeric](19, 5) NOT NULL,
[VARIANCEQTY] [numeric](19, 5) NOT NULL,
[QTYTYPE] [smallint] NOT NULL,
[UNITCOST] [numeric](19, 5) NOT NULL,
[EXTDCOST] [numeric](19, 5) NOT NULL,
[CRDTAMNT] [numeric](19, 5) NOT NULL,
[DEBITAMT] [numeric](19, 5) NOT NULL,
[IsLandedCostTrx] [tinyint] NOT NULL,
[IsVarianceTrx] [tinyint] NOT NULL,
[IsOverrideReceipt] [tinyint] NOT NULL,
[VCTNMTHD] [smallint] NOT NULL,
[ASOFDATE] [datetime] NOT NULL,
[STR1] [char](1) NOT NULL,
[DECPLQTY] [smallint] NOT NULL,
[DECPLCUR] [smallint] NOT NULL,
[ACTINDX] [int] NOT NULL,
[DEX_ROW_ID] [int] NOT NULL,
[ACTNUMST] [char](129) NOT NULL
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO


---------------------------------------------------------------------------------------
CREATE VIEW [dbo].[BI_HITBView]
AS
SELECT     ACTNUMBR_1, ACTNUMBR_2, ACTNUMBR_3, ACTNUMBR_4, ACTNUMBR_5, ACTNUMBR_6, ACTNUMBR_7, ACTNUMBR_8, ACTNUMBR_9, ACTNUMBR_10, ITEMNMBR, LOCNCODE, DOCDATE, GLPOSTDT, JRNENTRY, SEQNUMBR, ITEMDESC, ITMGEDSC,
                  DOCNUMBR, DOCTYPE, TRXSORCE, TRXREFERENCE, HSTMODUL, TRXQTY, VARIANCEQTY, QTYTYPE, UNITCOST, EXTDCOST, CRDTAMNT, DEBITAMT, IsLandedCostTrx, IsVarianceTrx, IsOverrideReceipt, VCTNMTHD, ASOFDATE, STR1, DECPLQTY, DECPLCUR,
                  ACTINDX, DEX_ROW_ID, ACTNUMST, DEBITAMT - CRDTAMNT AS StockVal
FROM        BIT_HITBTemp
GO
--------------------------------------------------------------------------------------
CREATE PROCEDURE [dbo].[BI_BuildHITB]
-- Add the parameters for the stored procedure here
@Tdt as datetime
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

    -- Insert statements for procedure here
    delete from BIT_HITBTemp
Insert into BIT_HITBTemp   
exec seeHITB @I_nSortBy=2,@I_nReceiptOptions=2,@I_sStartItemNumber=N'',@I_sEndItemNumber=N'þþþþþþþþþþþþþþþþþþþþþþþþþþþþþþþ',@I_sStartAccountNumber=N'   -    -  ',@I_sEndAccountNumber=N'ÿÿÿ-ÿÿÿÿ-ÿÿ',@I_sStartLocationCode=N'',@I_sEndLocationCode=N'þþþþþþþþþþþ',@I_nStartQTYType=1,@I_nEndQTYType=5,@I_dtStart='1900-01-01 00:00:00',@I_dtEnd=@Tdt,@sStartClass=N'',@sEndClass=N'þþþþþþþþþþþ',@sStartGenericDesc=N'',@sEndGenericDesc=N'þþþþþþþþþþþ',@I_fUseGLPostDate=0,@I_fIncludeZeroQtyItems=1,@O_SQL_Error_State=NULL
select * from BI_HITBView
END

GO

------------------------------------------------------------------------------------
--HITB Detail
---------------------------------------------------------------------------------------
/****** Object:  Table [dbo].[BIT_HitbTemp_Dtl]    Script Date: 10/01/2018 04:58:55 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[BIT_HitbTemp_Dtl](
[ACTNUMBR_1] [char](6) NOT NULL,
[ACTNUMBR_2] [char](6) NOT NULL,
[ACTNUMBR_3] [char](6) NOT NULL,
[ACTNUMBR_4] [char](6) NOT NULL,
[ACTNUMBR_5] [char](6) NOT NULL,
[ACTNUMBR_6] [char](6) NOT NULL,
[ACTNUMBR_7] [char](6) NOT NULL,
[ACTNUMBR_8] [char](6) NOT NULL,
[ACTNUMBR_9] [char](6) NOT NULL,
[ACTNUMBR_10] [char](6) NOT NULL,
[ITEMNMBR] [char](31) NOT NULL,
[LOCNCODE] [char](11) NOT NULL,
[DOCDATE] [datetime] NOT NULL,
[GLPOSTDT] [datetime] NOT NULL,
[JRNENTRY] [int] NOT NULL,
[SEQNUMBR] [int] NOT NULL,
[ITEMDESC] [char](101) NOT NULL,
[ITMGEDSC] [char](11) NOT NULL,
[DOCNUMBR] [char](21) NOT NULL,
[DOCTYPE] [smallint] NOT NULL,
[TRXSORCE] [char](13) NOT NULL,
[TRXREFERENCE] [smallint] NOT NULL,
[HSTMODUL] [char](3) NOT NULL,
[TRXQTY] [numeric](19, 5) NOT NULL,
[VARIANCEQTY] [numeric](19, 5) NOT NULL,
[QTYTYPE] [smallint] NOT NULL,
[UNITCOST] [numeric](19, 5) NOT NULL,
[EXTDCOST] [numeric](19, 5) NOT NULL,
[CRDTAMNT] [numeric](19, 5) NOT NULL,
[DEBITAMT] [numeric](19, 5) NOT NULL,
[IsLandedCostTrx] [tinyint] NOT NULL,
[IsVarianceTrx] [tinyint] NOT NULL,
[IsOverrideReceipt] [tinyint] NOT NULL,
[VCTNMTHD] [smallint] NOT NULL,
[ASOFDATE] [datetime] NOT NULL,
[STR1] [char](1) NOT NULL,
[DECPLQTY] [smallint] NOT NULL,
[DECPLCUR] [smallint] NOT NULL,
[ACTINDX] [int] NOT NULL,
[DEX_ROW_ID] [int] NOT NULL,
[ACTNUMST] [char](129) NOT NULL
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

-----------------------------------------------------------------------------------------
CREATE VIEW [dbo].[BI_HITBView_Dtl]
AS
SELECT     ACTNUMBR_1, ACTNUMBR_2, ACTNUMBR_3, ACTNUMBR_4, ACTNUMBR_5, ACTNUMBR_6, ACTNUMBR_7, ACTNUMBR_8, ACTNUMBR_9, ACTNUMBR_10, ITEMNMBR, LOCNCODE, DOCDATE, GLPOSTDT, JRNENTRY, SEQNUMBR, ITEMDESC, ITMGEDSC,
                  DOCNUMBR, DOCTYPE, TRXSORCE, TRXREFERENCE, HSTMODUL, TRXQTY, VARIANCEQTY, QTYTYPE, UNITCOST, EXTDCOST, CRDTAMNT, DEBITAMT, IsLandedCostTrx, IsVarianceTrx, IsOverrideReceipt, VCTNMTHD, ASOFDATE, STR1, DECPLQTY, DECPLCUR,
                  ACTINDX, DEX_ROW_ID, ACTNUMST, CASE WHEN jrnentry = 0 THEN 0 ELSE dbo.BIT_HitbTemp_Dtl.DEBITAMT - dbo.BIT_HitbTemp_Dtl.CRDTAMNT END AS StockVal
FROM        BIT_HitbTemp_Dtl
GO


-----------------------------------------------------------------------------------------
CREATE PROCEDURE [dbo].[BI_BuildHITB_Dtl]
-- Add the parameters for the stored procedure here
@Tdt as datetime
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

    -- Insert statements for procedure here
    delete from BIT_HITBTemp_Dtl
Insert into BIT_HITBTemp_Dtl   
exec seeHITB @I_nSortBy=2,@I_nReceiptOptions=1,@I_sStartItemNumber=N'',@I_sEndItemNumber=N'þþþþþþþþþþþþþþþþþþþþþþþþþþþþþþþ',@I_sStartAccountNumber=N'   -    -  ',@I_sEndAccountNumber=N'ÿÿÿ-ÿÿÿÿ-ÿÿ',@I_sStartLocationCode=N'',@I_sEndLocationCode=N'þþþþþþþþþþþ',@I_nStartQTYType=1,@I_nEndQTYType=5,@I_dtStart='1900-01-01 00:00:00',@I_dtEnd=@Tdt,@sStartClass=N'',@sEndClass=N'þþþþþþþþþþþ',@sStartGenericDesc=N'',@sEndGenericDesc=N'þþþþþþþþþþþ',@I_fUseGLPostDate=0,@I_fIncludeZeroQtyItems=1,@O_SQL_Error_State=NULL
select * from BI_HITBView_Dtl
END

GO

Dynamics GP - SQL Views - Purchase Order Remaining Quantities

Original View here https://mahmoudsaadi.blogspot.com/2014/09/purchase-order-receiving-and-invoices.html



CREATE VIEW [dbo].[BI_PO_Rcts]
AS
SELECT     A.PONUMBER, CASE A.[POSTATUS] WHEN 1 THEN 'NEW' WHEN 2 THEN 'RELEASED' WHEN 3 THEN 'CHANGE ORDER' WHEN 4 THEN 'RECEIVED' WHEN 5 THEN 'CLOSED' WHEN 6 THEN 'CANCELED' END AS POSTATUS,
                  CASE A.[POTYPE] WHEN 1 THEN 'STANDARD' WHEN 2 THEN 'DROP-SHIP' WHEN 3 THEN 'BLANKET' WHEN 4 THEN 'BLANKET DROP-SHIP ' END AS POTYPE, A.DOCDATE AS Date, A.PRMDATE AS PromiseDate, A.REQDATE AS RequestedDate,
                  A.REMSUBTO AS RemainingSubTotal, A.SUBTOTAL AS SubTotal, A.OREMSUBT AS OriginatingRemainingSubTotal, A.ORSUBTOT AS OriginatingSubTotal, A.VENDORID AS VendorID, A.VENDNAME AS VendorName, A.DUEDATE AS DueDate, A.CURNCYID, A.BUYERID,
                  CASE B.[POLNESTA] WHEN 1 THEN 'NEW' WHEN 2 THEN 'RELEASED' WHEN 3 THEN 'CHANGE ORDER' WHEN 4 THEN 'RECEIVED' WHEN 5 THEN 'CLOSED' WHEN 6 THEN 'CANCELED' END AS POLineStatus, B.ITEMNMBR AS ItemNumber,
                  B.ITEMDESC AS ItemDescrption, B.VENDORID AS LineVendorID, B.VNDITNUM AS VendorItemNumber, B.LOCNCODE, B.UOFM, B.QTYORDER AS QuantityOrdered, B.QTYCANCE AS QuantityCancelled, B.UNITCOST, B.ORUNTCST AS OriginatingUnitCost,
                  B.EXTDCOST AS ExtendedCost, B.OREXTCST AS OriginatingExtendedCost, B.XCHGRATE AS ExchangeRate, ISNULL(C.POPRCTNM, ' ') AS POPRCTNM, ISNULL(C.QTYSHPPD, 0) AS QuantityShipped, ISNULL(C.QTYINVCD, 0) AS QuantityInvoiced, ISNULL(C.QTYREJ, 0)
                  AS QuantityRejected, ISNULL(C.QTYMATCH, 0) AS QuantityMatch, ISNULL(C.QTYRESERVED, 0) AS QuantityReserved, CASE C.POPTYPE WHEN 1 THEN 'SHIPMENT' WHEN 2 THEN 'INVOICE' WHEN 3 THEN 'SHIPMENT/INVOICE' ELSE ' ' END AS DocumentType,
                  ISNULL(C.UOFM, ' ') AS Expr1, ISNULL(C.DATERECD, '') AS DateReceived, ISNULL(C.PCHRPTCT, 0) AS PURCHASERECEIPTCOST, ISNULL(D.ORUNTCST, 0) AS ORGRECUNITCOST, ISNULL(D.OREXTCST, 0) AS ORGRECCOST,
                  B.QTYORDER - B.QTYCANCE - ISNULL(C.QTYSHPPD, 0) - ISNULL(C.QTYREJ, 0) AS POQtyRemaining
FROM        dbo.POP10100 AS A INNER JOIN
                  dbo.POP10110 AS B ON A.PONUMBER = B.PONUMBER LEFT OUTER JOIN
                  dbo.POP10500 AS C ON B.PONUMBER = C.PONUMBER AND B.ORD = C.POLNENUM LEFT OUTER JOIN
                  dbo.POP30310 AS D ON C.PONUMBER = D.PONUMBER AND C.RCPTLNNM = D.RCPTLNNM AND C.POPRCTNM = D.POPRCTNM LEFT OUTER JOIN
                  dbo.POP30300 AS E ON D.POPRCTNM = E.POPRCTNM

GO

Dynamics NAV LS OMNI - Developer notes

• http://mobiledemo.lsretail.com/LSOmniHelp24/html/M_LSOmni_Service_IeCommerceJson_ContactGetById.htm
• The ContactSearch method only reads from the [LSOmni].[dbo].[Contact] table
• This table is not synced with NAV's Contact table.
• It is a one-way sync from OMNI to NAV, and does not pick up any changes done in NAV

Dynamics NAV - "The Array Dimensions must be identical"

https://dynamicsuser.net/nav/b/mark_brummel/posts/inside-nav-2017-improvement-comes-with-a-price

• C365 Format Address requires CustAddress Array input, but this does not exist in most report function coding
• Solution
• Adjust the SalesInvShipTo function to the following
• Remove the CustAddr Parameter

SalesInvShipTo(VAR AddrArray : ARRAY [8] OF Text[50];VAR SalesInvHeader : Record "Sales Invoice Header") ShowShippingAddr : Boolean

WITH SalesInvHeader DO BEGIN

  FormatAddr(

    AddrArray,"Ship-to Name","Ship-to Name 2","Ship-to Contact","Ship-to Address","Ship-to Address 2",

    "Ship-to City","Ship-to Post Code","Ship-to County","Ship-to Country/Region Code");

END;

Dynamics NAV LS OMNI - 10012900 WI Ls Mgt runs for all items and takes too long

10012867 WI Item With Daily Updates will populate based on changes required to sync to OMNI
If for some reason this table does not get cleared, it will always keep trying to update the same items even if no updates are necessary
Clear this table to reset the replication

Dynamics NAV LS OMNI - Retail Selling Prices show up as 0

Sales prices not coming up
- Retail Item>Distribution>Must exist in webstore
- Store must have func profile
- Uses Store.CurrencyCode (must match sales price currency code)
- Store Price group limits price check to that group, else uses item card price
- Uses Store Vat Posting Group - Must match sales price vat posting group


10012900 WI LS Mgt populates WI NC Product and WI NC Price