Dynamics NAV - LS Retail - POS Transactions - IsReturnSale, Voided, Training

• "Transaction Header"."Sale Is Return Sale" - True Is return
• "Transaction Header"."Entry Status" - Option set  ,Voided,Posted,Training
• 
  

Dynamics NAV - LS Retail - Item Import

• Create a Retail Vendor
• From the Vendor Card, go to Vendor Setup to define the file import format (You can copy this from any existing vendor
• Place the file to be imported in the location defined in the vendor setup
• Click Import File
• This will create a file in NAV in       Franchise/Item Import Files
• Select your file, click “Set Status as OK”
• Go to Departments/LS Retail/Replenishment/Item Import/Item Import Journal
• Click Navigate>Get Import Lines
• Select your item import file>Click OK
• Click Navigate>Create/Process
• If there are any errors, you deal with the errors accordingly

Objects used

• Item Import Journal Line (T10012359) - Item Import Journal (P10012359)
• Create/Process Button - C10012352 Item Import Create
• Attribute Setup (T10000788) - Attribute Setup (P10000906)

• After an upgrade, the Hard Attributes from the item import function will no longer work.
• Item Import function must be modified to insert attribute-item assignment instead to Attribute Value (10000786)
• Use Attribute Setup instead of Retail Setup
• Attribute Option Values column names change

Dynamics NAV - LS Retail - Hotkeys do not work. F keys do not work.

For LS NAV 2017, if the hotkeys do not work, you have to set the rows and columns for the Fixed menu. It has to be a "fixed key" menu type.

SQL - How to temporarily pause replication

https://cavemansblog.wordpress.com/2012/03/12/startstop-sql-server-replication-agent/







--STOP the Distribution Agent:

sp_MSstopdistribution_agent @publisher, @publisher_db, @publication, @subscriber, @subscriber_db

--START the Distribution Agent:

sp_MSstartdistribution_agent @publisher, @publisher_db, @publication, @subscriber, @subscriber_db</pre>










exec distribution.dbo.sp_MSstartdistribution_agent @publisher  = 'PUBLISHERSERVER',

@publisher_db   = 'PUBLISHERDB',

@publication    = 'TABLE_EMPLOYEE',

@subscriber     = 'SUBSCRIBERSERVER',

@subscriber_db  = 'WAREHOUSEDB'

SQL - How to setup Replication

• Prerequisites
• Windows Server 2012
• SQL 2016 Standard
• Machine1 Primary
• Machine2 Secondary
• Create a folder on Machine2 called REPL, share it
• Connect to Machine1>Right click on Replication>Configure Distribution
• Tick It's own distributor
• Set network path to snapshot folder
• leave default distribution database name
• Finish
• Under security>select or create the user account being used for the replication service
• Assign user as db_owner to distribution and company db to replicate
• Publish a db
• Expand Replication>Publication>New Publication
• Transactional - each transaction is sent, only works on tables with primary keys
• Snapshot - entire db is restored over replica
• Select all objects to publish
• Enter your replication account
• Create Publication
• Right Click on Publication>Properties>Add Service account to access list
• 
  
• Connect to Machine2>Expand Replication>Subscription
• Create new subscription,point to publisher
• Choose Pull to allow the subscription machine to do the processing
• Enter name of destination db to replicate into
• Enter your replication account
• Run Continuously
• Initialize Immediately
• Assign service account as db_owner on destination db
• Connect to Machine1
• Right click publication>Snapshot agent status
• After the Snapshot has been generated, Click Start
• 
  
• NOTES
• If new objects are added to the Source database, the Publication needs to be updated to include the new objects as Articles before it can publish them
• If your log agent is not starting, use a different account that has full access to all the databases

LS Retail - Data Director - SQL Timeout, Failed to Insert, Failed to Update

• Causes
• The DBFileSize is too small, and SQL is taking too long to resize itself.
• DD gives up and rolls back
• SQL gives up and doesn't resize
• Infinite loop of trying and failing
• Multiple jobs accessing the same table cause table locks
• Ensure you only have one job per table running at a time
• If a large job fails, it will send a rollback request to SQL which takes forever to execute
• This will generally lock up the table and prevent any further transactions for an extremely long time
• You cannot kill the rollback process
• If you restart the service, the database goes into recovery mode until it clears the rollback, and will then be available again
• DO NOT Increase Data File Size in DD Options
• Controls size of data file generated on local drive
• Max is around 2gb
• DO NOT Increase Message Size in DD Options
• Controls the number of records that can be sent in a single job
• Max is around 2million records
• 
  
• Solution
• Manually resize the database file size by going to properties>file size> Set it to something larger that will not resize constantly (+20gb)
• Use the table filters to reduce the records to around 2 million, or 2gb

Dynamics NAV - Inventory Valuation View - Historical Stock Status Equivalent

USE [CRONUS]
drop table BIT_Temp_InvVal
drop table BIT_Temp_InvVal_CombA
drop table BIT_Temp_InvVal_CombB



DECLARE @D Date Set @D = '07/12/17'
DECLARE @L varchar(5) Set @L = 'WM'

--Precalc CombA
Select * Into BIT_Temp_InvVal_CombA from (
SELECT     [Item No_], [Location Code], CAST(SUM(Q) + SUM(Q2) AS float) AS Qty, CAST(SUM(InventoryValue) AS float) AS InventoryValue
                                                    FROM          (SELECT     [Entry No_], [Item No_], [Location Code], Quantity AS Q,
                                                                                                       (SELECT     ISNULL(SUM(IAE.Quantity), 0) AS EXPR1
                                                                                                         FROM          [CRONUS$Item Application Entry] AS IAE INNER JOIN
                                                                                                                                [CRONUS$Item Ledger Entry] AS ILE2 ON IAE.[Inbound Item Entry No_] = ILE2.[Entry No_]
                                                                                                         WHERE      (IAE.[Posting Date] <= @D) AND (ILE2.[Posting Date] <= @D) AND (ILE.[Item No_] = ILE2.[Item No_]) AND
                                                                                                                                (IAE.[Outbound Item Entry No_] = ILE.[Entry No_]) AND (IAE.[Item Ledger Entry No_] = ILE.[Entry No_])AND [Location Code] = @L) AS Q2,
                                                                                                       (SELECT     SUM([Cost Amount (Expected)] + [Cost Amount (Actual)]) AS EXPR1
                                                                                                         FROM          [CRONUS$Value Entry] AS V
                                                                                                         WHERE      ([Posting Date] <= @D) AND ([Item Ledger Entry No_] = ILE.[Entry No_])AND [Location Code] = @L) AS InventoryValue
                                                                            FROM          [CRONUS$Item Ledger Entry] AS ILE
                                                                            WHERE      ([Posting Date] <= @D) AND (Positive = 0) AND [Location Code] = @L) AS OutBoundEntries
                                                    GROUP BY [Item No_], [Location Code]) as CombA


--DECLARE @D Date Set @D = '07/12/17'
--DECLARE @L varchar(5) Set @L = 'WM'
--Precalc CombB
Select * Into BIT_Temp_InvVal_CombB from (
         SELECT     [Item No_], [Location Code], CAST(SUM(Q) - SUM(Q2) AS float) AS Qty, CAST(SUM(InventoryValue) AS float) AS InventoryValue
                                                    FROM         (SELECT     [Item No_], [Location Code], Quantity AS Q,
                                                                                                      (SELECT     ISNULL(SUM(IAE.Quantity), 0) AS EXPR1
                                                                                                        FROM          [CRONUS$Item Application Entry] AS IAE INNER JOIN
                                                                                                                               [CRONUS$Item Ledger Entry] AS ILE2 ON IAE.[Item Ledger Entry No_] = ILE2.[Entry No_]
                                                                                                        WHERE      (IAE.[Posting Date] <= @D) AND (ILE2.[Posting Date] <= @D) AND (ILE.[Entry No_] = IAE.[Inbound Item Entry No_]) AND
                                                                                                                               (IAE.[Outbound Item Entry No_] <> 0) AND (IAE.[Item Ledger Entry No_] <> ILE.[Entry No_]) AND [Location Code] = @L) AS Q2,
                                                                                                      (SELECT     ISNULL(SUM([Cost Amount (Expected)] + [Cost Amount (Actual)]), 0) AS EXPR1
                                                                                                        FROM          [CRONUS$Value Entry] AS V
                                                                                                        WHERE      ([Posting Date] <= @D) AND ([Item Ledger Entry No_] = ILE.[Entry No_])AND [Location Code] = @L) AS InventoryValue
                                                                           FROM          [CRONUS$Item Ledger Entry] AS ILE
                                                                           WHERE      ([Posting Date] <= @D) AND (Positive = 1)AND [Location Code] = @L) AS InBoundEntries
                                                    GROUP BY [Item No_], [Location Code]) as CombB

--Combine Final Report

select * into BIT_Temp_InvVal from (
SELECT     CRONUS$Item.No_ AS ItemNo, CRONUS$Item.[Vendor No_], CRONUS$Vendor.Name AS VendorName, CRONUS$Item.Description,
                      Detail.[Location Code], CRONUS$Item.[Division Code], CRONUS$Item.[Attrib 4 Code] AS CatReportingGrp,
                      CRONUS$Vendor.[Vendor Posting Group], Detail.Qty, Detail.InventoryValue, ISNULL(ROUND(Detail.InventoryValue / NULLIF (Detail.Qty, 0), 2), 0)
                      AS UnitCost
FROM         CRONUS$Item INNER JOIN
                          (SELECT     [Item No_], [Location Code], SUM(Qty) AS Qty, SUM(InventoryValue) AS InventoryValue
                            FROM          (SELECT * from BIT_Temp_InvVal_CombA
                                                    UNION
                                           SELECT * from BIT_Temp_InvVal_CombB) AS CombinedEntries
                            GROUP BY [Item No_], [Location Code]) AS Detail ON CRONUS$Item.No_ = Detail.[Item No_] LEFT OUTER JOIN
                      CRONUS$Vendor ON CRONUS$Item.[Vendor No_] = CRONUS$Vendor.No_
--ORDER BY ItemNo
) as a
GO

Dynamics GP - Auto Age AR - Automatically Age Receivables

--Set this up as a sql job to run daily

USE TWO
DECLARE @O_iErrorState int, @I_dAgingDate datetime
SELECT @I_dAgingDate = CONVERT(VARCHAR(10), GETDATE(), 102)
EXEC dbo.rmAgeCustomer 0, '', 'þþþþþþþþþþþþþþþ', @I_dAgingDate,
          127, 0, 0, '', @O_iErrorState OUT

SSRS - Value expression error: 'RdlObjectModel' is not a member of 'Reporting Services'

https://stackoverflow.com/questions/25022276/value-expression-error-bc30456-rdlobjectmodel-is-not-a-member-of-reportin

When you copy/paste some functions, the act of pasting resets the name of the function, and adds extra characters.
Go into your formulas, and remove any instances of "Microsoft.ReportingServices.RdlObjectModel.ExpressionParser.VBFunctions."

SQL - Get Time from Datetime or Date field

RIGHT (CAST(dbo.SOP10200.DEX_ROW_TS AS varchar), 7)

Dynamics GP - eOne Smartconnect - GL Transaction

• Do not use a sequence
• Use a column for journal numbers
• Key by journal numbers
• Create Journal - Group by journal numbers
• Tick update existing for add distribution and create journal
• 
  

Dynamics GP - Insert values into Comment Tables. SOP Comment Tables.

insert into sy04200(cmtsries,commntid,cmmttext) values(3,'C6','WRITE OFF')

Tables involved

SOP10106 - Sales Document Comments

SY04200 - Comment ID's

SOP10202 - Sales Line Comments