Dynamics GP - SQL Trigger - Track Customer Class, Item Type, Item Price Changes

--Create Changes log table


CREATE TABLE [dbo].[BI_ChangeTracking](

[Timestamp] [datetime] NULL,

[TableChanged] [nvarchar](50) NULL,

[FieldChanged] [nvarchar](50) NULL,

[FromValue] [nvarchar](50) NULL,

[ToValue] [nvarchar](50) NULL,

[UserChanged] [nvarchar](50) NULL,

[RecordID] [nvarchar](50) NULL,

[Description] [nvarchar](250) NULL

) ON [PRIMARY]




GO
 
 
--Create Trigger on customer table
  

CREATE TRIGGER [dbo].[after_update_class]

ON [dbo].[RM00101]

AFTER UPDATE

AS




BEGIN

Insert into BI_ChangeTracking(

[Timestamp], TableChanged, FieldChanged,

fromvalue,tovalue,

userchanged,recordid,[Description]

)

Select

getdate(),'RM00101','CUSTCLAS',

d.CUSTCLAS, i.CUSTCLAS,

USER_NAME(USER_ID()),i.CUSTNMBR,'Customer Class Changed'

from Inserted i inner join deleted d on (i.CUSTNMBR = d.CUSTNMBR)

Where (i.custclas <> '' or d.custclas <> '') and i.custclas <> d.custclas




END
  



GO
  

-- Create Trigger on item table
CREATE TRIGGER [dbo].[after_update_status]

ON [dbo].[IV00101]

AFTER UPDATE

AS




BEGIN
 
 


Insert into BI_ChangeTracking(

[Timestamp], TableChanged, FieldChanged,

fromvalue,tovalue,

userchanged,recordid,[Description]

)

Select

getdate(),'IV00101','ITEMTYPE',

d.ITEMTYPE, i.ITEMTYPE,

USER_NAME(USER_ID()),i.ITEMNMBR,'Item Type Changed 1-sales inv,2-discontinued, 3-kit, 4-misc charges, 5-services, 6-flatfee ,'

from Inserted i inner join deleted d on (i.ITEMNMBR = d.ITEMNMBR)

Where (i.ITEMTYPE <> '' or d.ITEMTYPE <> '') and i.ITEMTYPE <> d.ITEMTYPE




END
GO

--Create trigger on item price table

　

CREATE TRIGGER [dbo].[after_update_price]

ON [dbo].[IV00108]

AFTER UPDATE

AS




BEGIN
   -- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

-- Insert statements for trigger here

Insert into BI_ChangeTracking(

[Timestamp], TableChanged, FieldChanged,

fromvalue,tovalue,

userchanged,recordid,[Description]

)

Select

getdate(),'IV00108','UOMPRICE',

d.UOMPRICE, i.UOMPRICE,

USER_NAME(USER_ID()),rtrim(i.ITEMNMBR) + ' | ' + i.PRCLEVEL,'Item Price Changed'

from Inserted i inner join deleted d on (i.ITEMNMBR = d.ITEMNMBR and i.PRCLEVEL = d.PRCLEVEL)

Where (i.UOMPRICE <> 0 or d.UOMPRICE <> 0) and i.UOMPRICE <> d.UOMPRICE




END
  




  




　
 GO

Dynamics GP - SQL View - SOP Sales History

CREATE VIEW [dbo].[BI_Voided]
AS
SELECT     TOP (100) PERCENT rm.CUSTNMBR AS Customer_ID, rm.DOCDATE AS Doc_Date, rm.DOCNUMBR AS Doc_Number,
                      CASE s.SOPTYPE WHEN 3 THEN 'Invoice' ELSE 'Return' END AS Doc_Type, CASE rm.VOIDSTTS WHEN 0 THEN 'No' ELSE 'Yes' END AS Voided_in_RM,
                      CASE s.VOIDSTTS WHEN 0 THEN 'No' ELSE 'Yes' END AS Voided_in_SOP
FROM         (SELECT     CUSTNMBR, DOCDATE, DOCNUMBR, CASE RMDTYPAL WHEN 1 THEN 3 ELSE 4 END AS SOPTYPE, VOIDSTTS
                       FROM          dbo.RM20101
                       WHERE      (RMDTYPAL IN (1, 8))
                       UNION
                       SELECT     CUSTNMBR, DOCDATE, DOCNUMBR, CASE RMDTYPAL WHEN 1 THEN 3 ELSE 4 END AS SOPTYPE, VOIDSTTS
                       FROM         dbo.RM30101
                       WHERE     (RMDTYPAL IN (1, 8))) AS rm INNER JOIN
                      dbo.SOP30200 AS s ON rm.SOPTYPE = s.SOPTYPE AND rm.DOCNUMBR = s.SOPNUMBE AND rm.VOIDSTTS <> s.VOIDSTTS
ORDER BY Doc_Date DESC

GO



CREATE VIEW [dbo].[BI_Cust_First_Sale]
AS
SELECT     CUSTNMBR, MIN(DOCDATE) AS FirstSalesDate
FROM         dbo.SOP30200
GROUP BY CUSTNMBR
GO

CREATE VIEW [dbo].[BI_Sales_History0]
AS
SELECT     TOP (100) PERCENT dbo.SOP30200.SOPNUMBE AS [SOP Number], dbo.SOP30200.DOCDATE AS [Document Date], dbo.SOP30200.GLPOSTDT AS [GL Post Date],
                      CASE WHEN sop30200.soptype = 4 THEN (sop30300.XTNDPRCE - SOP30300.TRDISAMT) * - 1 ELSE (sop30300.xtndprce - SOP30300.TRDISAMT) END AS Sales,
                      CASE WHEN sop30200.soptype = 4 THEN sop30300.EXTDCOST * - 1 ELSE sop30300.extdcost END AS Costs,
                      CASE WHEN sop30200.soptype = 4 THEN sop30300.quantity * - 1 ELSE sop30300.quantity END AS Qty, dbo.SOP30200.CUSTNMBR AS [Customer Number],
                      CASE WHEN sop30200.soptype = 1 THEN 'Quote' WHEN sop30200.soptype = 2 THEN 'Order' WHEN sop30200.soptype = 3 THEN 'Invoice' WHEN sop30200.soptype =
                       4 THEN 'Return' WHEN sop30200.soptype = 5 THEN 'BackOrder' WHEN sop30200.soptype = 6 THEN 'FulfilmentOrder' END AS [SOP Type],
                      dbo.IV40400.ITMCLSDC AS [Item Class Description], dbo.IV40400.ITMCLSCD AS [Item Class Code], dbo.RM00101.CUSTNAME AS [Customer Name],
                      dbo.RM00101.CUSTCLAS AS [Customer Class], dbo.SOP30300.QTYBSUOM AS [Base Unit Of Measure],
                      (CASE WHEN sop30200.soptype = 4 THEN sop30300.quantity * - 1 ELSE sop30300.quantity END) * dbo.SOP30300.QTYBSUOM AS [Units Sold], dbo.SOP30300.STATE,
                      dbo.SOP30300.PRSTADCD, ISNULL(dbo.SOP30200.SLPRSNID, '(none)') AS [Salesperson ID], dbo.IV00101.USCATVLS_1, dbo.IV00101.USCATVLS_2,
                      dbo.IV00101.USCATVLS_3, dbo.IV00101.ITEMNMBR AS [Item Number], dbo.IV00101.ITEMDESC AS [Item Description], dbo.SOP30200.ShipToName,
                      dbo.IV00101.USCATVLS_2 AS Category, dbo.SOP30300.UNITCOST, dbo.RM00101.CREATDDT, dbo.BI_Cust_First_Sale.FirstSalesDate,
                      LEFT(dbo.SOP30300.ITEMNMBR, 2) AS AB, dbo.SOP30300.UNITPRCE, dbo.RM00301.ZIP AS [Sales Person Department],
                      dbo.RM00301.SLPRSNFN AS [Sales Person First Name], dbo.RM00301.SPRSNSLN AS [Sales Person Last Name], YEAR(dbo.SOP30200.DOCDATE) AS DocYear,
                      RIGHT('00' + CAST(MONTH(dbo.SOP30200.DOCDATE) AS VARCHAR), 2) AS DocMonth, CAST(YEAR(dbo.SOP30200.DOCDATE) AS VARCHAR)
                      + '-' + CAST(RIGHT('00' + CAST(MONTH(dbo.SOP30200.DOCDATE) AS VARCHAR), 2) AS VARCHAR) AS DocYrMonth, RTRIM(dbo.SOP30200.CUSTNMBR)
                      + ' | ' + RTRIM(dbo.SOP30200.CUSTNAME) AS CusIdName, RTRIM(dbo.SOP30300.ITEMNMBR) + ' | ' + RTRIM(dbo.SOP30300.ITEMDESC) AS ItemIdName,
                      dbo.SOP30300.UOFM, CASE LEFT(dbo.SOP30200.CUSTNMBR, 1) WHEN '2' THEN LEFT(rm00101.Custclas, 4)
                      WHEN '4' THEN 'MP' WHEN '6' THEN 'MABELS' WHEN '8' THEN 'FROZEN' WHEN 'E' THEN dbo.RM00201.CLASDSCR ELSE LEFT(dbo.SOP30200.CUSTNMBR, 1)
                      END AS Div, ISNULL(dbo.RM00301.SALSTERR, '(none)') AS Salsterr, RTRIM(dbo.RM00301.SLPRSNFN) + ' ' + RTRIM(dbo.RM00301.SPRSNSLN) AS SRNm,
                      LEFT(dbo.RM00201.CLASDSCR, 3) AS CusClassDiv, dbo.RM00201.CLASDSCR, dbo.IV00101.MODIFDT, DATEDIFF(d, dbo.IV00101.CREATDDT, GETDATE()) AS ItemAge,
                      dbo.RM00101.COUNTRY, 0 AS BQty, CASE WHEN rm00101.CPRCSTNM = '' THEN rm00101.custname ELSE rm00101_1.custname END AS NatCusNm,
                      CASE WHEN rm00101.CPRCSTNM = '' THEN rm00101.custnmbr ELSE rm00101.cprcstnm END AS NatCus, dbo.BI_Voided.Doc_Number
FROM         dbo.RM00101 INNER JOIN
                      dbo.SOP30200 INNER JOIN
                      dbo.SOP30300 ON dbo.SOP30200.SOPTYPE = dbo.SOP30300.SOPTYPE AND dbo.SOP30200.SOPNUMBE = dbo.SOP30300.SOPNUMBE ON
                      dbo.RM00101.CUSTNMBR = dbo.SOP30200.CUSTNMBR LEFT OUTER JOIN
                      dbo.IV40400 INNER JOIN
                      dbo.IV00101 ON dbo.IV40400.ITMCLSCD = dbo.IV00101.ITMCLSCD ON dbo.SOP30300.ITEMNMBR = dbo.IV00101.ITEMNMBR LEFT OUTER JOIN
                      dbo.RM00301 ON dbo.SOP30200.SLPRSNID = dbo.RM00301.SLPRSNID AND dbo.SOP30300.SLPRSNID = dbo.RM00301.SLPRSNID LEFT OUTER JOIN
                      dbo.BI_Voided ON dbo.SOP30200.SOPNUMBE = dbo.BI_Voided.Doc_Number LEFT OUTER JOIN
                      dbo.RM00101 AS RM00101_1 ON dbo.RM00101.CPRCSTNM = RM00101_1.CUSTNMBR LEFT OUTER JOIN
                      dbo.RM00201 ON dbo.RM00101.CUSTCLAS = dbo.RM00201.CLASSID RIGHT OUTER JOIN
                      dbo.BI_Cust_First_Sale ON dbo.RM00101.CUSTNMBR = dbo.BI_Cust_First_Sale.CUSTNMBR
WHERE     (dbo.SOP30200.VOIDSTTS = 0) AND (dbo.SOP30200.SOPTYPE IN (3, 4)) AND (dbo.SOP30300.CMPNTSEQ = 0)
ORDER BY [GL Post Date] DESC

GO

SSRS - Multi Value parameter prevents report from running

Multi value parameter stops report from running or loading
Multi value parameter blanks out after you attempt to run the report causing the report to not run

The SSRS instance is corrupted.
Repair/Reinstall the SSRS instance.

Dynamics NAV - Transactions do not respect item status block flags

Ensure that you are entering the correct store group code on the item link.

Dynamics GP - Migrate OLE Notes from pre-GP2013

Use the OLE Migration tool on the Product Release page here

https://mbs.microsoft.com/partnersource/global/deployment/downloads/product-releases/MDGP2015_Release_Download

You will need:

• Access to the OLE folder where all of the original OLE notes were stored
• Modify the dex.ini and add the path of the OLE notes if it does not exist
• Install all software required to read the attachments you intend to migrate
• If a filetype does not have an associated program, it will not extract

Dynamics NAV - Reports with Blank Pages at the end

The extra blank pages are usually caused by

• Some field that is falling outside of the Page Size
• To test if it is your page size settings
• click on the report body
• Properties>Page
• Change the Page Size to something large
• Print the report
• If you get the report fitting on a single page, then there is some whitespace on the Interactive Size that is spilling over your Page Size and causing a new page
• Increase or Reduce the Interactive Size until the whitespace disappears

Dynamics GP - Cannot edit item information or change status. Changes to items are not being saved.

Cause:
The Purchase Tax Option field was blank. For some reason, this prevented any additional changes from being saved to the item

Resolution:

• Select a value for the Purchase Tax option for all items with blank purchase tax options.