Wednesday, February 14, 2018

Dynamics GP - PSTL Item Site Combiner - Delete existing transfers between sites

--PSTL Item Site combiner cannot run if there are transfers between the two sites you are trying to combine.
--Delete all transfer transactions in the IV30200 that move stock between the two locations you're trying to combine

IV30200 Trx Hist Header
IV30300 Trx Amount History
IV30301 Trx Amount Detail History
IV30500 Distribution History

--------------------------------------------------------------------------------------
declare @LOC1 as varchar(20) = 'WIP'
,@LOC2 as varchar(20) = 'MACOYA'

Select * from IV30200
--DELETE * from IV30200
WHERE IVDOCTYP = 3 and DOCNUMBR IN (
--Find all Transaction lines between the two locations
select DOCNUMBR from IV30300 where ((TRXLOCTN = @LOC1 and TRNSTLOC = @LOC2 and DOCTYPE = 3) OR
(TRXLOCTN = @LOC2 and TRNSTLOC = @LOC1 and DOCTYPE = 3))
)

Select * from IV30301
--DELETE * from IV30301
WHERE DOCTYP = 3 and DOCNUMBR IN (
--Find all Transaction lines between the two locations
select DOCNUMBR from IV30300 where ((TRXLOCTN = @LOC1 and TRNSTLOC = @LOC2 and DOCTYPE = 3) OR
(TRXLOCTN = @LOC2 and TRNSTLOC = @LOC1 and DOCTYPE = 3))
)

Select * from IV30500
--DELETE * from IV30500
WHERE IVDOCTYP = 3 and DOCNUMBR IN (
--Find all Transaction lines between the two locations
select DOCNUMBR from IV30300 where ((TRXLOCTN = @LOC1 and TRNSTLOC = @LOC2 and DOCTYPE = 3) OR
(TRXLOCTN = @LOC2 and TRNSTLOC = @LOC1 and DOCTYPE = 3))
)

Select * from IV30300
--DELETE * from IV30300
WHERE DOCTYP = 3 and DOCNUMBR IN (
--Find all Transaction lines between the two locations
select DOCNUMBR from IV30300 where ((TRXLOCTN = @LOC1 and TRNSTLOC = @LOC2 and DOCTYPE = 3) OR
(TRXLOCTN = @LOC2 and TRNSTLOC = @LOC1 and DOCTYPE = 3))
)

Friday, February 9, 2018

Dynamics NAV - LS Retail - OMNI - How to setup OMNI


  • Confirm that General Ledger Setup>LCY Code is a Valid code that matches an existing Currency Code
  • Install OMNI Server
  • Install OMNI windows Service - creates omni db
  • Check web service setup
  • Confirm web requests available
  • Import newest web requests
  • Validate web requests
  • Import newest associated objects
  • Create Distribution Group for ECOMM, Webshop, and add only the Web Store WEBSHOP in the subgroup
  • Add member WEBSHOP to the subgroup
  • Create all of the sync jobs by running page "WI Default Data"
  • WI Stores-Set default webshop store
  • Set Store Vat Bus Posting Group
  • Round small decimals to 0.01
  • Create POS Startup Data
  • Run ECOMMERCE JOB (C10012900 WI LS MGt-this always runs for ALL items)
  • Check WI Items with Daily Updates
  • T10012912 is populated with all items to replicate to omni
  • Clear all preactions, then run ecommerce job
  • -ReplEcommItems uses the Preaction table to control the final action sent per item

  • 10012860 WI Mgt 
  • C99009511 WS Request has all WS request codeunits, and fires whenever called


Thursday, February 8, 2018

Dynamics GP - 'An error occurred when updating permanent records. Transaction will not post.' - Check for Duplicates

This happens if the transaction number already exists in the posted transaction table.

To check for duplicates, use this script
https://blogs.msdn.microsoft.com/developingfordynamicsgp/2008/12/04/identifying-duplicate-transactions/


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

-- SOP Duplicates
select SOPTYPE, SOPNUMBE, COUNT(*) as [COUNT] from 
(
select SOPTYPE, SOPNUMBE from SOP10100 W
UNION ALL
select SOPTYPE, SOPNUMBE from SOP30200 H
) C
group by SOPTYPE, SOPNUMBE
having COUNT(*) > 1



-- IVC Duplicates
select DOCTYPE, INVCNMBR, COUNT(*) as [COUNT] from 
(
select DOCTYPE, INVCNMBR from IVC10100 W
UNION ALL
select DOCTYPE, INVCNMBR from IVC30101 H
) C
group by DOCTYPE, INVCNMBR
having COUNT(*) > 1



-- POP PO Duplicates
select PONUMBER, COUNT(*) as [COUNT] from 
(
select PONUMBER from POP10100 W
UNION ALL
select PONUMBER from POP30100 H
) C
group by PONUMBER
having COUNT(*) > 1



-- POP Receivingss Duplicates
select POPRCTNM, COUNT(*) as [COUNT] from 
(
select POPRCTNM from POP10300 W
UNION ALL
select POPRCTNM from POP30300 H
) C
group by POPRCTNM
having COUNT(*) > 1



-- RM Duplicates
select RMDTYPAL, DOCNUMBR, COUNT(*) as [COUNT] from 
(
select RMDTYPAL, RMDNUMWK as DOCNUMBR from RM10301 W
UNION ALL
select RMDTYPAL, DOCNUMBR from RM10201 W
UNION ALL
select RMDTYPAL, DOCNUMBR from RM20101 O
UNION ALL
select RMDTYPAL, DOCNUMBR from RM30101 H
) C
group by RMDTYPAL, DOCNUMBR
having COUNT(*) > 1



-- PM Duplicates
select DOCTYPE, VCHRNMBR, COUNT(*) as [COUNT] from 
(
select DOCTYPE, VCHNUMWK as VCHRNMBR from PM10000 W
UNION ALL
select DOCTYPE, VCHRNMBR from PM10300 P
UNION ALL
select DOCTYPE, VCHRNMBR from PM10400 M
UNION ALL
select DOCTYPE, VCHRNMBR from PM20000 O
UNION ALL
select DOCTYPE, VCHRNMBR from PM30200 H
) C
group by DOCTYPE, VCHRNMBR
having COUNT(*) > 1



-- IV Duplicates
select IVDOCTYP, DOCNUMBR, COUNT(*) as [COUNT] from 
(
select IVDOCTYP, IVDOCNBR as DOCNUMBR from IV10000 W
UNION ALL
select IVDOCTYP, DOCNUMBR from IV30200 H
) C
group by IVDOCTYP, DOCNUMBR
having COUNT(*) > 1



-- GL Duplicates
select JRNENTRY, RCTRXSEQ, SEQNUMBR, ACTINDX, TRXDATE, CRDTAMNT, DEBITAMT, [YEAR], COUNT(*) as [COUNT] from 
(
select WH.JRNENTRY, WH.RCTRXSEQ, WL.SQNCLINE as SEQNUMBR, WL.ACTINDX, TRXDATE, CRDTAMNT, DEBITAMT, WH.OPENYEAR as [YEAR] from GL10000 WH JOIN GL10001 WL ON WL.JRNENTRY = WH.JRNENTRY
UNION ALL
select JRNENTRY, RCTRXSEQ, SEQNUMBR, ACTINDX, TRXDATE, CRDTAMNT, DEBITAMT, OPENYEAR as [YEAR] from GL20000 O
UNION ALL
select JRNENTRY, RCTRXSEQ, SEQNUMBR, ACTINDX, TRXDATE, CRDTAMNT, DEBITAMT, HSTYEAR as [YEAR] from GL30000 H
) C
group by JRNENTRY, RCTRXSEQ, SEQNUMBR, ACTINDX, TRXDATE, CRDTAMNT, DEBITAMT, [YEAR]
having COUNT(*) > 1

Monday, February 5, 2018

Dynamics NAV - LS Retail - OMNI - Enable tracing, Debug and enable logging

You can see the sql statement used in the ReplEcommItems method by enabling the tracing on the Omni server.
C:\LS Retail\LSOmni\LSOmniService
C:\LS Retail\LSOmni\LSOmniWinService
In Nlog.config file on the Omni server change
<logger name="*" minlevel="Debug" writeTo="file" />
to
<logger name="*" minlevel="Trace" writeTo="file" />

Restart the LSOmniService and you can see all sql statements in the Omni server logfile.txt  (C:\LS Retail\LSOmni\logs\logfile.txt)

Friday, February 2, 2018

Dynamics GP - Manufacturing - "You do not have access priveleges to edit this field" after cancelling a linked MO, and trying to cancel the SOP quantity

SOP/MOP Link
https://groups.google.com/forum/#!topic/microsoft.public.greatplains/_MsTrRLp2GQ
http://microsoft.public.greatplains.narkive.com/92kddrvO/sop-mop-linked-orders

Cause:
This occurs when you have created an SOP order, auto-generated an MO, and then cancel the MO, and try to cancel the Order Quantity.
This is because you have not defined a security set to manage the Edit SO Qty option on the SOP Manufacturing setup.

Solution:

  • Create Security Set
  • Manufacturing>Setup>System>Process Security
  • Create set
  • Set to password, set a password
  • Sales Order Processing Setup>Additional>Manufacturing Sales Order
  • Prefs. There's a field called "Edit SO QTY's when MO is attached Process
  • Security". You can choose a "security set" to authorize users to edit the
  • sales line quantities.

Thursday, February 1, 2018

SQL SSRS Sum Error


This happens because data is returning nulls or some other bad data that cannot be summed.
Wrap all values in Cdbl(Salesamt) to convert it to a proper decimal


Sum((IIf(Fields!Capacity.Value Is Nothing, CDbl(0),Fields!Capacity.Value))*(IIf(Fields!EstLF.Value Is Nothing, CDbl(0),Fields!EstLF.Value))*8760)"