Dynamics NAV - LS Retail - How to restore a copy of the live server to a test server

1) Stop the TEST NAV service

• 2) Backup live database and restore over test database in SQL Management Studio
• 3) Run these scripts to disable the scheduled jobs
• --------------Disable jobs
• update  [CRONUS$Scheduler Job Header]
• SET [Next Check Date] = '1753-01-01 00:00:00.000', [Next Check Time] = '1753-01-01 00:00:00.000'
• 4) Change the web service setup info 
•  - Departments/LS Retail/Administration/Web Service
•  - Change the Server Computer Name = 192.168.10.226
•  - Change the Server Port = 13047
•  - Change the Server Name = OMNI
• 
  
• 5) Change the TS60 location to point to the test
•  - Departments/LS Retail/Scheduler/Distribution
•  - Change Db Server Name = SECSQLSRV
•  - Change Db Path & Name = CROMNI
•  - Under Web server Fasttab
•  - Change Web Server Computer Name = 192.168.10.226
•  - Change Web Server Port = 13047
•  - Change Web Server Name = OMNI
• 
  
• 6) Restart TEST NAV service

Dynamics NAV - Amount needs to be rounded in G/L Entry

C12 - ApplyCustLedgEntry
Run Cust. Ledger Entry Table
Check Flowfields Original Amount, and Remaining amount and round what is feeding them

Check Detailed Cust_ Ledg_ Entry and round the Amount fields
---------------------------------------------------
  update [Cronus$Detailed Cust_ Ledg_ Entry]
  set [Amount]=Round([Amount],2),[Amount (LCY)]=Round([Amount (LCY)],2),
  [Debit Amount (LCY)] = Round([Debit Amount (LCY)],2),  [Credit Amount (LCY)] = Round([Credit Amount (LCY)],2),
  [Debit Amount] = Round([Debit Amount],2),  [Credit Amount] = Round([Credit Amount],2)

  where [Entry No_] >= '996000'


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

Do this for all unposted entries.
Check the Detailed Customer Ledg. Entries for the entry number

----------------------------------------------
For fixed assets, check the FA Ledger Entry acquisition amounts
----------------------------------------------
update [SUPERPHARMTT$FA Ledger Entry] set [Amount]=Round([Amount],2), [Debit Amount] = Round([Debit Amount],2),  [Credit Amount] = Round([Credit Amount],2) where [FA No_] ='FFF120'

Dynamics NAV - Do not have access to Replenishment Setup Tables

If your license does not have access to the replenishment setup tables, you will get errors when these codeunits run

T9901457 statement-post

Posting Item Reclass. Journal

Dynamics NAV - LS Retail - Cannot Return old pos transaction at pos register

• 99001570 POS Transaction Codeunit,
• VoidPostedTransaction
• Getpostedtransactionex
• Web request not pulling transaction to be returned
• Web request get_transaction
• 99008915 POS Refund Mgt.
• 99009513 Web services mgt

Dynamics NAV-LS Retail - "Table ID's don't match" when printing a copy of receipt after doing a transaction on account

Remove the printc command from the on account button

Dynamics NAV - LS Retail - Number of Times Receipt Printed

T99008985 POS Counter Table
Match Record ID to Transaction Number
First print is Counted as 0
>0 are reprints

Dynamics NAV - SQL View - Check User Permission Role Assignments

SELECT        [User].[User Name], [Access Control].[Role ID], [Access Control].[Company Name]
FROM            [Access Control] INNER JOIN
                         [User] ON [Access Control].[User Security ID] = [User].[User Security ID]

Dynamics NAV - LS Retail - POS Rollout Go-Live Checklist

• Install SQL
• Install NAV
• Install Data Director
• Install LS Toolboxes
• Restore POS DB
• Login as local admin
• Launch SQL configuration manager
• Disable Demo Instance
• Enable protocols Named Pipes, TCP/IP for live instance
• Confirm mixed mode enabled
• Set sa password
• Restart SQL Service
• Launch NAV Administration environment
• Set the database in the nav instance
• Confirm SOAP is enabled
• Restart NAV service
• Launch NAV Client
• Users>Add admin users, Cashier user
• Retail Users>Add admin users, Cashier user
• Set Store, Location, Terminal
• Remove existing manager passwords
• Launch old nav client
• Transaction Header>Check the last transaction number, write it down
• Launch new nav development environment
• Run Transaction Header
• Set the next transaction number to the old transaction number +1
• Enter store, terminal, date
• Go to Terminals
• Check the hardware profile, remove any profile on the terminal
• Confirm the correct hardware profile on the store
• Go to Distribution Locations
• Select the head office, test connection
• Fill in web connection data
• Tick override
• Enter username, password, domain
• Test web connection
• Logout of windows, login as Cashier account
• Launch NAV Start
• Right click on POS button>Edit
• Change company name from Cronus to your company name
• Click Save
• Login to POS
• Test Invoice and print (first invoice and print takes very long)
• Confirm new receipt number
• Test Return and print
• Confirm new receipt number

Dynamics NAV - LS Retail - Discount logic

• Customer Discounts
• 
  
• POS Trans Discounts
• Uses Periodic Discounts
• Discount Offers
• Affected by Regular NAV Discounts
• Pos Functions.Recalcslip uses the regular nav discounts to apply to pos
• Checks POS Transaction and POS Trans Line to see if customer and item discount groups exist
• 99001462 - get retail price
• Checks for any retail promotion offers to adjust price
• Checks for VAT Business Posting Gr. Price vs item vat bus posting group
• 99008906 Calcprice
• Debug 99008900 line 140
• 99009509 POS Offer Ext.RecalcOfferSeq
• 99008981 Pos Trans Line. CalcPrices
• 99008906 CalcTransDiscPercent
• 7000 Sales price mgt. getstandardprice
• 7000 Sales Price Mgt.findsaleslinedisc
• Copies discounts to sales line discount table
• T7004 Sales Line Discount.Line Discount Amount incl vat must be filled in

Dynamics NAV - There is an error in XML document (795,35). Client disconnects when opening page.

Page specification is not well formed

In this case the Retail Item List would throw this error, and crash out the NAV client whenever we tried to access the page.

It turns out, the Retail Item List has the Retail Item Card defined in the CardPageID property.
We had added a field to the Retail Item Card, and promoted a button to the homepage, which apparently caused this to happen.

Note, The Retail Item Card launched correctly with no issues, all items would compile with no issues.

Once we restored a previous version of the Retail Item Card without the promoted button and extra field, the Retail Item List functioned as normal.

Dynamics GP - FAQ - Company Data Archive Tool from Professional Advantage (CDA)

http://www.profad.com/products/company-data-archive/

1. I understand you have modified inquiry windows to show the information for both companies.
1. Are you just adding new windows? Yes, we’ve added a new transaction receivables feature, that includes the archived documents – CDA RM Transaction Inquiry Window
2. Are you modifying the existing inquiry windows? No; however, you can access this window from an existing inquiry window: sales à inquiry à transaction by customer à additional à archive company inquiry.
2. Is your software compatible with Myridas Pricing from Dynavistics? Our standard CDA Professional module would NOT archive that data; however, our CDA Plus add-on allows users to archive any 3^rd party or non-core GP data, which Myridas would fall under: http://www.profad.com/products/company-data-archive/cdaplus/
3. Posting an adjustment to an archived year: the answer is you can’t – you should not be archiving stuff you figure you will be adjusting. Most customers keep 3-5 years of live data in their production company, which should help avoid this issue.
1. Where do I post an adjustment for an archived year?
2. Will it roll up the beginning balances in my live company?
3. Are there restrictions on the General ledger year I choose to archive?
4. How does the tool interact with PSTL (professional services tools library)? We don’t do anything special with PSTL
5. How does the tool interact with the ID modifiers? (i.e. customer id modifier, vendor id modifier, etc.) the tool has an option that allows you to archive Master Data – this would include address ID, shipping methods, tax detail, any of that core customer vendor information.
6. If I need to un-apply an applied payment that is in the historical database: similar answer to question 3
1. Can this be done?
2. Is this a complicated process?
7. Are there restrictions on the transactions that are moved to the archive database? CDA only allows you to archive historical or fully applied data.
1. Will it only move fully applied documents within the period defined? Yes, all related documents in the apply chain must fall after your archive cut-off date for those to be archived.
2. What happens if I pay the documents subsequently? Do I have to run the tool again to move those transactions to the archived database? Yes, CDA will only archive historical data, so if one of those documents is still considered ‘open’, you would need to run the archive again in order to transfer that data to the ‘archive’ company.
8. How does the tool affect running a Historical Inventory Trial Balance report? HATB is impacted the same as if you remove history from GP, so it depends on your inventory costing model.  The GP Inventory documentation covers how removing history impacts each one.
9. What is the impact on historical data when checklinks is run? Check Links should usually only need to be run within Dynamics GP if problems occur, and it should always be run within a test company before the live company in order to know what to expect.  The utility is very powerful and can sometimes make issues worse.  However, we do recommend running it before the first archive and after each archive. There isn’t necessarily a specific order in which Check Links should be run.  Depending on why you are running it, there are specific tables that should be included, but they vary per issue. You can run it for all modules at the same time, but we recommend running it for the modules being archived before the first archive and after each archive. The process can take a long time if you run it for all modules.  Alternatively, you could run it for all of the tables in each module and process one module at a time.
10. Can I post transactions in the archived database? No
11. What is the impact on extracting SSRS reports? You should be fine as you can have SSRS reports that span databases

SSRS - Prevent fields from exporting to excel

=IIF(Globals!RenderFormat.Name = "EXCEL", True, False)

Dynamics NAV - 'You do not have the following permission on Codeunit xxx::Execute'

Check your license details under "Object Assignment" and confirm what range your custom objects are allowed in.
50000 is the default, but it may not be the correct range if the customer has specifically purchased a different range.


Clear data from any tables that they are not registered for

http://help.lsnav.lsretail.com/Content/Installation%20Guide%20LS%20Nav/Clear%20Data%20Tables%20That%20Are.htm?Highlight=data%20license]


Before the customer’s license is activated in the database, it is necessary to check for and delete all data from tables that are not included in the license to prevent possible permission errors. This can be done by running the LS Retail Modules page that is located in the Administration menu.
The page shows what modules are available.  Select the Included in License field for the modules that are in the customer’s license, and click Check Data Usage on the Home action menu.
The system now counts entries in all LS Retail tables outside the modules included in the customer’s license.
Click Used Tables outside License to view the result of the check. This opens a page displaying a list of tables outside the modules included in the license. Click Clear data in the Actions menu to empty those tables. 
Note: This needs to be done for every company in the database.
The process can be repeated by clicking Initialize on the LS Retail Modules page.

----------------------------------------------
If this still does not work, open a support request with LS Retail specifically for license errors.
----------------------------------------------

SSRS - Repeat rows on each page (Does not work if any group is hidden)

https://docs.microsoft.com/en-us/sql/reporting-services/report-design/display-row-and-column-headers-on-multiple-pages-report-builder-and-ssrs

• This only works for the Tablix Header Row outside of your Groups. 
• Group headers will not repeat on each page.
• Remove any unnecessary columns on the left to ensure you can see the correct Static fields in the advanced mode
• Groups>Advanced Mode>Select Field from group list on left, Properties>Keep together=No, Keep with group=After, Repeat on new page = Yes

SPECIAL NOTE

On SQL 2012 and lower, if you hide any group, it breaks the row repeat functionality.

Once any group is hidden, the rows will no longer repeat on the report.
HOWEVER if you enable a visibility toggle for all hidden groups, the header will repeat.

Dynamics GP - SSRS - Location Lookup View

CREATE VIEW [dbo].[BI_Location_Lookup]
AS
SELECT     TOP (100) PERCENT LOCNCODE, LOCNDSCR, RTRIM(LOCNCODE) + ' | ' + RTRIM(LOCNDSCR) AS LocLbl
FROM        dbo.IV40700
ORDER BY LOCNCODE

GO

Dynamics GP - SSRS - Item Class Lookup View

--For use with Item class parameter selection for SSRS reports

CREATE VIEW [dbo].[BI_ItemClass_Lookup]
AS
SELECT     TOP (100) PERCENT ITMCLSCD, ITMCLSDC, RTRIM(ITMCLSCD) + ' | ' + RTRIM(ITMCLSDC) AS ItemClassLbl
FROM        dbo.IV40400
ORDER BY ITMCLSCD

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