Dynamics GP - SQL View - All Posted and Unposted GL Transactions Detail and Summary by period and account

See Original view here
http://victoriayudin.com/2011/04/27/sql-view-for-all-gl-transactions-in-dynamics-gp/


--All GL Details
----------------------------------------------------------------------------------------------------------
SELECT     GL.Trx_Status, GL.TRXDATE AS Trx_Date, GL.JRNENTRY AS Journal_Entry, GM.ACTNUMST AS Account_Number,
                      GA.ACTDESCR AS Account_Description, GL.DEBITAMT AS Debit_Amount, GL.CRDTAMNT AS Credit_Amount, GL.REFRENCE AS Reference,
                      GL.SOURCDOC AS Source_Document, GL.ORTRXSRC AS Originating_TRX_Source, GL.ORMSTRID AS Originating_Master_ID,
                      GL.ORMSTRNM AS Originating_Master_Name, GL.ORDOCNUM AS Originating_Doc_Number, GL.CURNCYID AS Currency_ID,
                      GL.LASTUSER AS Last_User, GL.USWHPSTD AS User_Who_Posted, GL.OPENYEAR AS GLYear, GL.PERIODID AS GLPeriod
FROM         (SELECT     ACTINDX, TRXDATE, SOURCDOC, JRNENTRY, ORTRXSRC, REFRENCE, ORDOCNUM, ORMSTRID, ORMSTRNM, DEBITAMT, CRDTAMNT,
                                              CURNCYID, 'Open' AS Trx_Status, LASTUSER, USWHPSTD, OPENYEAR, PERIODID
                       FROM          dbo.GL20000
                       WHERE      (SOURCDOC NOT IN ('BBF', 'P/L')) AND (VOIDED = 0)
                       UNION ALL
                       SELECT     ACTINDX, TRXDATE, SOURCDOC, JRNENTRY, ORTRXSRC, REFRENCE, ORDOCNUM, ORMSTRID, ORMSTRNM, DEBITAMT, CRDTAMNT,
                                             CURNCYID, 'History' AS Trx_Status, LASTUSER, USWHPSTD, HSTYEAR, PERIODID
                       FROM         dbo.GL30000
                       WHERE     (SOURCDOC NOT IN ('BBF', 'P/L')) AND (VOIDED = 0)
                       UNION ALL
                       SELECT     GD.ACTINDX, GH.TRXDATE, GH.SOURCDOC, GH.JRNENTRY, GH.ORTRXSRC, GH.REFRENCE, GD.ORDOCNUM, GD.ORMSTRID,
                                             GD.ORMSTRNM, GD.DEBITAMT, GD.CRDTAMNT, GH.CURNCYID, 'Work' AS Trx_Status, GH.LASTUSER, GH.USWHPSTD, GH.OPENYEAR,
                                             GH.PERIODID
                       FROM         dbo.GL10000 AS GH INNER JOIN
                                             dbo.GL10001 AS GD ON GH.JRNENTRY = GD.JRNENTRY
                       WHERE     (GH.VOIDED = 0)) AS GL INNER JOIN
                      dbo.GL00105 AS GM ON GL.ACTINDX = GM.ACTINDX INNER JOIN
                      dbo.GL00100 AS GA ON GL.ACTINDX = GA.ACTINDX

--All GL Summary
---------------------------------------------------------------------------------------------------------------------
SELECT     GLYear, GLPeriod, Account_Number, Account_Description, SUM(Debit_Amount) AS Debit, SUM(Credit_Amount) AS Credit,
                      SUM(Debit_Amount - Credit_Amount) AS Total
FROM         dbo.BI_GL_Trx
GROUP BY Account_Number, Account_Description, GLYear, GLPeriod

Dynamics GP - SQL VIEW - PO, Received status, Invoiced status

SELECT     POP10100.PONUMBER, a.POPRCTNM, a.VNDDOCNM, a.APStatus, a.BCHSOURC, a.VCHRNMBR, PM00200.VENDORID, PM00200.VENDNAME,
                      CASE WHEN poprctnm IS NOT NULL THEN 'Received' ELSE 'Not Received' END AS RctStatus, CASE WHEN vchrnmbr IS NOT NULL
                      THEN 'Invoiced' ELSE 'Not Invoiced' END AS InvStatus
FROM         POP10100 LEFT OUTER JOIN
                      PM00200 ON POP10100.VENDORID = PM00200.VENDORID LEFT OUTER JOIN
                          (SELECT     'Work' AS APStatus, POP10300.POPRCTNM, POP10300.VNDDOCNM, POP10300.BCHSOURC, POP10310.RCPTLNNM,
                                                   POP10310.PONUMBER, POP10300.VENDORID, POP10300.VCHRNMBR, POP10310.ITEMDESC, POP10310.ITEMNMBR
                            FROM          POP10300 INNER JOIN
                                                   POP10310 ON POP10300.POPRCTNM = POP10310.POPRCTNM
                            UNION
                            SELECT     'Posted' AS APstatus, POP30300.POPRCTNM, POP30300.VNDDOCNM, POP30300.BCHSOURC, POP30310.RCPTLNNM,
                                                  POP30310.PONUMBER, POP30300.VENDORID, POP30300.VCHRNMBR, POP30310.ITEMDESC, POP30310.ITEMNMBR
                            FROM         POP30300 INNER JOIN
                                                  POP30310 ON POP30300.POPRCTNM = POP30310.POPRCTNM) AS a ON POP10100.PONUMBER = a.PONUMBER
GROUP BY a.APStatus, a.POPRCTNM, a.VNDDOCNM, a.BCHSOURC, a.VCHRNMBR, POP10100.PONUMBER, PM00200.VENDORID, PM00200.VENDNAME

Dynamics GP - Report Writer - Display all Comment Lines, even if the comment is larger than 4 lines with Autogrow

Normally this cannot be done.
However, if you switch the report to Text, and use the Comment field (not the array) it will display the full value of the field, and grow and shrink automatically.

Dynamics GP - How to copy / migrate an AFA Financial Report from one company to another

1. Find the next available AFA Report ID in the new company
1. select * from AF40100
2. The next id is the next available number
2. Copy all data from the following tables
1. AF40101, AF40102, AF40103, AF40104, AF40105, AF40106, AF40107, AF40108, AF40200, and AF40201
3. Replace the report id with the new report ID in the new company
1. update af40101 set reportid = [newid] where reportid = [oldid]

You can export the data from pervasive by browsing to each table and exporting to a data files.
However, pervasive will export an additional column of 0's before each actual column of data.
After cleaning, the first column from pervasive is not needed.
you must clean the data and remove these columns to import the data into SQL.
Once cleaned, you can copy and paste directly into the AFA tables.


See original solution here
http://support.microsoft.com/kb/863224

Dynamics NAV - How to import New company Opening Balances

On each account card that you want to update directly, enable the "Direct Posting" tickbox on the account card. This allows direct posting to the account without a subledger transaction.

For AR and AP balances, setup the initial posting groups to debit and credit the same accounts so there is a net 0 movement on the account. The actual balances can then be entered as journals if you tick the "Direct Posting" for those accounts

• Create a General Journal Batch for OPENAR
• Set the Bal type to "account", and the Bal Account No. to your Ar account number
• This will cause the ar entries to cancel themselves out in the gl, and then you can enter your gl balances separately.
• Configure the General Journal Screen to the format of your data
• Copy and paste your entries as rows

For Bank and Inventory Balances
http://www.dynamicsnavconsultant.com/tag/beginning-balance/

Solution here
http://support.microsoft.com/kb/944227

Dynamics NAV 2013 R2 - How to Create a new Company

• Open NAV RTC
• Search for Companies
• Administration>IT Administration>Companies
• Add new company
• Switch companies to login to the new company
• Company will initialize

Populate with default data

• Export these packages from an existing company, then import them to your new company
• NOSERIES
• LS DISTRIBUTION (Only required if using LS Retail in new company)
• BASICCONFIG
• NAVBASIC
• LS BASE (Only required if using LS Retail in new company)

Set Permissions if required

• If creating the company in the same database, the same permissions apply by default across all companies
• To change this, in the User Setup>Permission Set list, there is a company column
• If you select a company for a permission set, then those permissions will apply to that company for that user only.

Related Issues

• "Customer has one of more open ledger entries" when trying to set the IC Partner code
• Review the Customer Ledger Entries screen
• Add a filter where open = yes
• Enter a journal to close all of these transactions
• "Vendor has one of more open ledger entries" when trying to set the IC Partner code
• Review the Vendor Ledger Entries screen
• Add a filter where open = yes
• Enter a journal to close all of these transactions

Dynamics NAV - Rapidstart

• How to Create a Template, insert Data and import to NAV
• Create Package
• Create Worksheet
• Assign Worksheet to Package
• Export Template to Excel from Worksheet or Package Table
• Enter data to import into template
• Import Data from Template
• Apply Data

Dynamics GP - Check to Words shows extra 0's for cents. Check to Words is too long and is truncated at 80 characters.

Issue:
Payables check prints incorrect cents
If your currency has more than 2 decimal places, and you print checks, the check to Words function that prints the check amount in words on the check prints the cents with all of the zeroes for the number of decimal places the currency is set to.

Example. - 5 DP currency prints as

100.20 shows as One Hundred Dollars And 20000 Cents

Solution:

• Open PSTL
• Look under Misc Tools
• Look for Decimal Place Tool
• Tick the Box next to the Blue Tick (It will be a Red Tick after you tick it)

This will force the Check to Words to only display 2 decimal places in the description.

Original Solution
http://support.microsoft.com/kb/961423

However, this field is not on the cheque by default, you must add the "Amount in words" field from PM Payment work.
However, the "Amount in Words" field will throw an error if you use it directly.
Ignore it completely and instead, use the STRING A 255 Field. It will show the correct information once the PSTL tool has been installed.

If you want to wrap the field, you must use two (or more) calculated fields using the rw_parsestring function to split the string.
Then rw_parsestring against that calculated string.

• CalcLine1 = RW_ParseString(Pmt_Work.STRING A 255 50  1)
• CalcLine2 = RW_ParseString(Pmt_Work.STRING A 255 50  2)

OR you can use substring

• CalcLine1 = RW_SubString(Pmt_Work.STRING A 255 1  60)
• CalcLine2 = RW_SubString(Pmt_Work.STRING A 255 61  60)

Dynamics NAV - Install NAV 2013 R2-2018 and LS Retail 7.10-11.02

Dynamics NAV Install guide
https://docs.microsoft.com/en-us/dynamics-nav/installation-options

LS NAV Install guide
http://help.lsnav.lsretail.com/Content/Resources/Documents%20for%20download/Installation%20Guide%20LS%20Nav%202018%20(11.02).pdf

• Install NAV on the SQL server
• Disable UAC
• Add the current account to the local administrator group
• If you are creating a specific service account, and using separate database and app servers, follow this guide
• https://msdn.microsoft.com/en-us/library/hh166150(v=nav.70).aspx
• You will need Excel, install a copy first
• Add Search Service Role
• Add Role>File Services>Windows Search
• or Add role>Features>Windows Search Service
• Install the latest service pack on your OS
• Install Dot Net 3.5 from Roles>Features>Dot net 3.5
• Download and install Dot net 4.0 (required if on windows server 2008 R2)
• http://go.microsoft.com/fwlink/?LinkID=212547
• or dot Net 4.5
• http://go.microsoft.com/fwlink/?LinkID=242919
• You need Powershell 3 and .net 4.5.2 (includes powershell 3.0) (http://go.microsoft.com/fwlink/?LinkId=317819)
• Dot Net 4.5.2 (requires restart)
• http://www.microsoft.com/en-us/download/details.aspx?id=42643
• Windows Management Framework 3.0
• http://www.microsoft.com/en-us/download/details.aspx?id=34595

• Download the W1 install and W1 demo database media from 
• https://mbs.microsoft.com/partnersource/global/deployment/downloads/product-releases/MSDNAV2013R2DownloadPage#MicrosoftDynamicsNAV2013R2Documentation
• Download LS Retail install 
• http://partners.lsretail.com/Partners/tabid/235/language/en-US/Default.aspx
• Download Latest version of LS Retail Data Director
• http://partners.lsretail.com/LSDataDirector/LatestVersion/tabid/872/language/en-US/Default.aspx
• 
  
• Extract DVD, Run setup
• Choose an installation
• If you choose install demo, it will install an instance of SQL 2012 Express with the demo company
• Run the LS install, it will extract all the files you need
• Restore the bak file over your demo company
• If you cannot install the bak file, you can do a database upgrade to install the objects into the existing demo company
• Open the demo database in the NAV dev environment
• Upload your partner keys
• Import the fob from the ls retail>objects directory
• Set SQL user as db_owner in SQL Management Studio
• Add current user to database in NAV Client
• Compile all objects
• Build server application objects
• If you choose custom
• Install All Modules (Deselect what you don't need)
• Install the LS Toolboxes
• Navigate to LS Retail\LS7.10.01\Setup\LS Nav 2013 (7.10.01) Toolbox
• Install the Client Components
• Install the Server Components
• Install Data Director
• Install Data Director>Tick 2013 RTC and 2013 SRV
• Tick first 2 and last 2
• Run the Data Director Configuration Tool>Click Get Config
• Launch the Development Environment
• Open Database
• Click All
• Click Tools>Compile
• Click Tools>Build Server Application Objects
• Launch Dynamics NAV Client
• Search for Users
• Add current user and any other users
• Restart NAV service
• Launch NAV, and use LS Retail
• Run the LS Help installer from Installs>LS>LS>Online Help
• Use Import/Export tool to import the xmls from rapidstart
• Retail Setup>Insert Default Data
• Setup default store in retail setup as HO
• The settings on HO will be used for all Terminals

How to Install the POS

• Launch NAV
• Search for the "Import Export Worksheet"
• For Each Profile (Functionality, Hardware, Menu, Interface, Style)
• Select the profile
• On the top bar, Click Import
• Select the LS Rapidstart Profile xml file
• LS7.10\LS Retail\LS7.10.01\Setup\LS Nav 2013 (7.1) Rapid Start
• For Each Terminal, ensure that the Functionality Profile and Hardware profile are set to ##DEFAULT
• Configure Data Director to push all data to POS location
• Go to physical POS device
• Install NAV server and client
• Install LS retail and toolboxes
• Install Data director
• Create retail user on that machine
• Give access to only that store and terminal
• Log in to main server
• Find job in LS scheduler
• Run job to push data
• Create shortcut that will run the "Run Client" which will load the POS screen

How to import base NAV and LS configuration data

• Search for Import Export Worksheet
• For each line on the worksheet, Import the profile.xml in the LS Retail setup
• Import and apply all Rapidstart Packages
• Update Packages as necessary
• 
  

Issues:

• Nav service starts, but then stops after a while
• In the NAV Admin 
• Enter Company Name
• Enter Time Zone
• Add the service account to the local admin group
• Install the NAV database components on the SQL server
• Install NAV Demo on the SQL Server
• Install NAV Demo on App server
• Connect to the instance at least once on the database server
• You did not install NAV as an administrator
• Ensure you are a local administrator, delete the demo database, run NAV setup and repair NAV.
• Disable the firewall on the database server and the application server or open the relevant ports
• Run the NAV Install repair
• You do not have permission to create a database
• Do not select any database components, and install all other components with no sql connection data
• Create the instance manually in the admin console after the install completes

Dynamics GP - Voided Sales transactions appear in Historical Stock Status Report

The Historical Stock Status Report and the Item Stock Inquiry read their data from the IV30300 table.

Whenever a transaction is voided, the corresponding record is supposed to be removed from the IV30300 table.

If records are showing on your Historical Stock Status that should not be there, the offending records need to be removed.

Look for any doc number and item number in SOP30300 that is not in IV30300
Also look for any Doc numbers in IV30300 with multiple Trxsorce values. Chances are, the lowest trxsorce values are supposed to be removed.

--delete voided SOP30300 stuck in IV30300
--------------------------------------------------------------
delete from iv30300 where dex_row_id in (
select distinct dex_row_id from(
SELECT     IV30300.TRXSORCE, IV30300.DOCTYPE, IV30300.DOCNUMBR, IV30300.DOCDATE, IV30300.HSTMODUL, IV30300.CUSTNMBR, IV30300.ITEMNMBR, IV30300.LNSEQNBR, IV30300.UOFM,
                      IV30300.TRXQTY, IV30300.UNITCOST, IV30300.EXTDCOST, IV30300.TRXLOCTN, IV30300.TRNSTLOC, IV30300.TRFQTYTY, IV30300.TRTQTYTY, IV30300.IVIVINDX, IV30300.IVIVOFIX,
                      IV30300.DECPLCUR, IV30300.DECPLQTY, IV30300.QTYBSUOM, IV30300.Reason_Code, IV30300.DEX_ROW_ID, SOP30200.VOIDSTTS
FROM         SOP30200 INNER JOIN
                      SOP30300 ON SOP30200.SOPTYPE = SOP30300.SOPTYPE AND SOP30200.SOPNUMBE = SOP30300.SOPNUMBE INNER JOIN
                      IV30300 ON SOP30300.SOPNUMBE = IV30300.DOCNUMBR AND SOP30300.ITEMNMBR = IV30300.ITEMNMBR
WHERE     (SOP30200.VOIDSTTS = 1)
) as a)


--Delete IV30300 that have no corresponding SOP30300 lines
-------------------------------------------------------------------

delete from iv30300 where dex_row_id in (
SELECT    IV30300.DEX_ROW_ID
FROM         (SELECT DISTINCT TOP (100) PERCENT DOCNUMBR, COUNT(ITEMNMBR) AS Expr1, DOCTYPE, ITEMNMBR, MIN(TRXSORCE) AS Expr2
                       FROM          (SELECT DISTINCT TOP (100) PERCENT DOCNUMBR, DOCTYPE, TRXSORCE, ITEMNMBR
                                               FROM          IV30300 AS IV30300_1
                                               GROUP BY DOCDATE, ITEMNMBR, DOCNUMBR, DOCTYPE, TRXSORCE
                                               ORDER BY DOCNUMBR) AS derivedtbl_1
                       GROUP BY DOCNUMBR, DOCTYPE, ITEMNMBR
                       HAVING      (DOCTYPE = 6) AND (COUNT(ITEMNMBR) > 1)
                       ORDER BY DOCNUMBR, ITEMNMBR) AS b INNER JOIN
                      IV30300 ON b.DOCTYPE = IV30300.DOCTYPE AND b.DOCNUMBR = IV30300.DOCNUMBR AND b.ITEMNMBR = IV30300.ITEMNMBR AND b.Expr2 = IV30300.TRXSORCE
                      )