Thursday, September 29, 2016

Dynamics GP - Posting Setup Tables - How to change the File Export Destination Name on the fly

How to get posting reports to print to a file including the audit trail code in the file name

select * from sy02200 - Posting Destination file name
select * from sy01000 - Next Audit Trail Code

Change the Filexpnm field just before printing using vbscript and a sql update query on the print button

Wednesday, September 28, 2016

Dynamics GP - Manufacturing - Receiving Additional Finished goods Against a Completed MO - Backflush Costs go to 0

Problem:

  • Create a BOM, set it to Backflush
  • Create an MO for 100, Release it
  • Create a receipt for 100, increase all backflush quantities
  • Attempt to receive another 1 against the MO, note the component quantities and costs
  • Attempt to receive ANOTHER 1 against the MO, note the component quantities and costs
  • The costs and quantities start calculating incorrectly when receiving additional quantities against a completed MO
  • Closing the MO results in incorrect Item Costs and Component Usage

Resolution:
  • Do not receive against completed MO's if using Backflush

Dynamics GP - How to Reset the System Password

https://community.dynamics.com/gp/b/gpfrankhamelly/archive/2010/01/20/gptip42day-resetting-the-gp-system-password


   UPDATE DYNAMICS.SY02400 
           SET PASSWORD = 0x0202020202020202020202020202020 

Monday, September 26, 2016

Dynamics GP - Error after upgrade "Trigger is not registered"

https://blogs.msdn.microsoft.com/developingfordynamicsgp/2012/01/31/quick-tip-solving-dexterity-trigger-clashes/


  • This problem occurs due to GP customizations that are triggering multiple code units on the same event
  • Changing the order of the customized dictionaries in the Dynamics.set file will ensure the triggers are loaded in the correct order
  • Disable dictionaries in your dynamics.set one by one and try launching GP to determine which dictionary is causing the trigger error
  • Once you figure out which dictionary is causing the error, Edit your Dynamics.set file, and move the offending dictionary up so that it loads first

Dynamics GP - Extended Pricing Views

/****** Object:  View [dbo].[BI_BookSheets]    Script Date: 9/28/2016 9:45:28 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[BI_BookSheets]
AS
SELECT     dbo.RM00500.PRCSHID, dbo.RM00500.PRODTCOD, dbo.RM00500.LINKCODE, dbo.RM00500.SEQNUMBR, dbo.RM00500.PSSEQNUM, dbo.RM00500.DEX_ROW_ID,
                      dbo.SOP10109.DESCEXPR AS PrcBookDesc, CASE WHEN sop10109.isbase = 1 THEN 'Basebook' ELSE '' END AS BaseBkLbl, dbo.SOP10109.ISBASE
FROM         dbo.RM00500 LEFT OUTER JOIN
                      dbo.SOP10109 ON dbo.RM00500.LINKCODE = dbo.SOP10109.PRCBKID
WHERE     (dbo.RM00500.PRODTCOD = 'P')

GO
/****** Object:  View [dbo].[BI_Items]    Script Date: 9/28/2016 9:45:28 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[BI_Items]
AS
SELECT     dbo.IV00101.ITEMNMBR, dbo.IV00101.ITEMDESC, dbo.IV00101.ITMCLSCD, dbo.IV00101.USCATVLS_1, dbo.IV00101.USCATVLS_2, dbo.IV00101.USCATVLS_3,
                      dbo.IV00101.USCATVLS_4, dbo.IV00101.USCATVLS_5, dbo.IV00101.USCATVLS_6, dbo.IV00101.ITEMSHWT / 100 AS Wgt, dbo.IV40201.BASEUOFM,
                      dbo.IV00101.DECPLCUR, dbo.IV00101.DECPLQTY, dbo.IVR10015.ITEMSTATUS_I, dbo.IV00101.ITMTSHID AS ItmTaxSched, dbo.IV00101.ITEMTYPE,
                      CASE WHEN itemstatus_i = 1 THEN 'Item Engineering Active' ELSE 'Item Engrineering INACTIVE' END AS IEStatusLbl,
                      CASE WHEN itemtype = 1 THEN 'Sales Inventory' WHEN itemtype = 2 THEN 'Discontinued' WHEN itemtype = 3 THEN 'Kit' WHEN itemtype = 4 THEN 'Misc Charges' WHEN
                       itemtype = 5 THEN 'Services' WHEN itemtype = 6 THEN 'Flat Fee' END AS ItemTypeLbl
FROM         dbo.IV00101 INNER JOIN
                      dbo.IV40201 ON dbo.IV00101.UOMSCHDL = dbo.IV40201.UOMSCHDL LEFT OUTER JOIN
                      dbo.IVR10015 ON dbo.IV00101.ITEMNMBR = dbo.IVR10015.ITEMNMBR

GO
/****** Object:  View [dbo].[BI_ExtPrcGrpDetails]    Script Date: 9/28/2016 9:45:28 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[BI_ExtPrcGrpDetails]
AS
SELECT     dbo.IV10402.PRCSHID AS PriceSheet, dbo.IV10402.EPITMTYP AS PriceType, dbo.BI_Items.ITEMDESC, dbo.IV10402.UOFM, dbo.IV10402.QTYFROM,
                      dbo.IV10402.QTYTO, dbo.IV10402.PSITMVAL AS Price, dbo.IV10402.EQUOMQTY, dbo.IV10402.QTYBSUOM, dbo.IV10402.SEQNUMBR, dbo.IV10402.DEX_ROW_ID,
                      dbo.IV10401.PRODTCOD, dbo.BI_Items.ITMCLSCD, dbo.BI_Items.USCATVLS_1, dbo.BI_Items.USCATVLS_2, dbo.BI_Items.USCATVLS_3, dbo.BI_Items.USCATVLS_4,
                      dbo.BI_Items.USCATVLS_5, dbo.BI_Items.USCATVLS_6, dbo.BI_Items.Wgt, dbo.BI_Items.BASEUOFM, dbo.BI_Items.DECPLCUR, dbo.BI_Items.DECPLQTY,
                      dbo.BI_Items.ITEMSTATUS_I AS IEItemStatus, dbo.BI_Items.ItmTaxSched, dbo.IV10401.ACTIVE AS PriceIsActive, dbo.IV10401.PROMOTYP, dbo.IV10401.PROMOLVL,
                      dbo.SOP10110.ACTIVE AS PriceShtIsActive, dbo.SOP10110.STRTDATE AS PriceShtStart, dbo.SOP10110.ENDDATE AS PriceShtEnd,
                      dbo.SOP10110.CURNCYID AS PriceShtCurr, dbo.SOP10110.PROMO AS PriceShtShtPromo, dbo.SOP10110.DESCEXPR AS PriceShtDesc,
                      dbo.SOP10110.NTPRONLY AS IsNetPrice, dbo.BI_BookSheets.LINKCODE AS PriceBook, RTRIM(dbo.SOP10110.PRCSHID) + ' | ' + RTRIM(dbo.SOP10110.DESCEXPR)
                      AS PrcShtLbl, dbo.BI_Items.ITEMTYPE, dbo.BI_Items.IEStatusLbl, dbo.BI_Items.ItemTypeLbl,
                      CASE WHEN sop10110.active = 1 THEN 'Price Sheet is Active' ELSE 'Price Sheet INACTIVE' END AS PrcShtActiveLbl,
                      CASE WHEN IV10401.active = 1 THEN 'Price Line is Active' ELSE 'Price Line INACTIVE' END AS PrcLnActiveLbl,
                      CASE WHEN Promo = 1 THEN 'Promotional Sheet' ELSE '' END AS PrcShtPromoLbl, dbo.BI_BookSheets.PrcBookDesc, dbo.BI_BookSheets.BaseBkLbl,
                      dbo.BI_BookSheets.ISBASE, dbo.SOP10110.PRCSHID, dbo.BI_Items.ITEMNMBR, dbo.IV10400.PRCGRPID
FROM         dbo.IV10402 INNER JOIN
                      dbo.IV10401 ON dbo.IV10402.PRCSHID = dbo.IV10401.PRCSHID AND dbo.IV10402.ITEMNMBR = dbo.IV10401.ITEMNMBR INNER JOIN
                      dbo.SOP10110 ON dbo.IV10401.PRCSHID = dbo.SOP10110.PRCSHID INNER JOIN
                      dbo.IV10400 ON dbo.IV10401.ITEMNMBR = dbo.IV10400.PRCGRPID INNER JOIN
                      dbo.BI_Items ON dbo.IV10400.ITEMNMBR = dbo.BI_Items.ITEMNMBR LEFT OUTER JOIN
                      dbo.BI_BookSheets ON dbo.SOP10110.PRCSHID = dbo.BI_BookSheets.PRCSHID
WHERE     (dbo.BI_Items.ITEMTYPE <> 2)

GO
/****** Object:  View [dbo].[BI_ExtPrcDetails]    Script Date: 9/28/2016 9:45:28 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[BI_ExtPrcDetails]
AS
SELECT     dbo.IV10402.PRCSHID AS PriceSheet, dbo.IV10402.EPITMTYP AS PriceType, dbo.BI_Items.ITEMDESC, dbo.IV10402.UOFM, dbo.IV10402.QTYFROM,
                      dbo.IV10402.QTYTO, dbo.IV10402.PSITMVAL AS Price, dbo.IV10402.EQUOMQTY, dbo.IV10402.QTYBSUOM, dbo.IV10402.SEQNUMBR, dbo.IV10402.DEX_ROW_ID,
                      dbo.IV10401.PRODTCOD, dbo.BI_Items.ITMCLSCD, dbo.BI_Items.USCATVLS_1, dbo.BI_Items.USCATVLS_2, dbo.BI_Items.USCATVLS_3, dbo.BI_Items.USCATVLS_4,
                      dbo.BI_Items.USCATVLS_5, dbo.BI_Items.USCATVLS_6, dbo.BI_Items.Wgt, dbo.BI_Items.BASEUOFM, dbo.BI_Items.DECPLCUR, dbo.BI_Items.DECPLQTY,
                      dbo.BI_Items.ITEMSTATUS_I AS IEItemStatus, dbo.BI_Items.ItmTaxSched, dbo.IV10401.ACTIVE AS PriceIsActive, dbo.IV10401.PROMOTYP, dbo.IV10401.PROMOLVL,
                      dbo.SOP10110.ACTIVE AS PriceShtIsActive, dbo.SOP10110.STRTDATE AS PriceShtStart, dbo.SOP10110.ENDDATE AS PriceShtEnd,
                      dbo.SOP10110.CURNCYID AS PriceShtCurr, dbo.SOP10110.PROMO AS PriceShtShtPromo, dbo.SOP10110.DESCEXPR AS PriceShtDesc,
                      dbo.SOP10110.NTPRONLY AS IsNetPrice, dbo.BI_BookSheets.LINKCODE AS PriceBook, RTRIM(dbo.SOP10110.PRCSHID) + ' | ' + RTRIM(dbo.SOP10110.DESCEXPR)
                      AS PrcShtLbl, dbo.BI_Items.ITEMTYPE, dbo.BI_Items.IEStatusLbl, dbo.BI_Items.ItemTypeLbl,
                      CASE WHEN sop10110.active = 1 THEN 'Price Sheet is Active' ELSE 'Price Sheet INACTIVE' END AS PrcShtActiveLbl,
                      CASE WHEN IV10401.active = 1 THEN 'Price Line is Active' ELSE 'Price Line INACTIVE' END AS PrcLnActiveLbl,
                      CASE WHEN Promo = 1 THEN 'Promotional Sheet' ELSE '' END AS PrcShtPromoLbl, dbo.BI_BookSheets.PrcBookDesc, dbo.BI_BookSheets.BaseBkLbl,
                      dbo.BI_BookSheets.ISBASE, dbo.SOP10110.PRCSHID, dbo.BI_Items.ITEMNMBR, '' AS PrcGrpID
FROM         dbo.IV10402 INNER JOIN
                      dbo.IV10401 ON dbo.IV10402.PRCSHID = dbo.IV10401.PRCSHID AND dbo.IV10402.ITEMNMBR = dbo.IV10401.ITEMNMBR INNER JOIN
                      dbo.BI_Items ON dbo.IV10402.ITEMNMBR = dbo.BI_Items.ITEMNMBR INNER JOIN
                      dbo.SOP10110 ON dbo.IV10401.PRCSHID = dbo.SOP10110.PRCSHID LEFT OUTER JOIN
                      dbo.BI_BookSheets ON dbo.SOP10110.PRCSHID = dbo.BI_BookSheets.PRCSHID
WHERE     (dbo.BI_Items.ITEMTYPE <> 2)

GO
/****** Object:  View [dbo].[BI_ExtPrcDetails_All]    Script Date: 9/28/2016 9:45:28 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[BI_ExtPrcDetails_All]
AS
SELECT     PriceSheet, PriceType, ITEMDESC, UOFM, QTYFROM, QTYTO, Price, EQUOMQTY, QTYBSUOM, SEQNUMBR, DEX_ROW_ID, PRODTCOD, ITMCLSCD, USCATVLS_1,
                      USCATVLS_2, USCATVLS_3, USCATVLS_4, USCATVLS_5, USCATVLS_6, Wgt, BASEUOFM, DECPLCUR, DECPLQTY, IEItemStatus, ItmTaxSched, PriceIsActive,
                      PROMOTYP, PROMOLVL, PriceShtIsActive, PriceShtStart, PriceShtEnd, PriceShtCurr, PriceShtShtPromo, PriceShtDesc, IsNetPrice, PriceBook, PrcShtLbl, ITEMTYPE,
                      IEStatusLbl, ItemTypeLbl, PrcShtActiveLbl, PrcLnActiveLbl, PrcShtPromoLbl, PrcBookDesc, BaseBkLbl, ISBASE, PRCSHID, ITEMNMBR, PrcGrpID
FROM         (SELECT     PriceSheet, PriceType, ITEMDESC, UOFM, QTYFROM, QTYTO, Price, EQUOMQTY, QTYBSUOM, SEQNUMBR, DEX_ROW_ID, PRODTCOD, ITMCLSCD,
                                              USCATVLS_1, USCATVLS_2, USCATVLS_3, USCATVLS_4, USCATVLS_5, USCATVLS_6, Wgt, BASEUOFM, DECPLCUR, DECPLQTY, IEItemStatus,
                                              ItmTaxSched, PriceIsActive, PROMOTYP, PROMOLVL, PriceShtIsActive, PriceShtStart, PriceShtEnd, PriceShtCurr, PriceShtShtPromo, PriceShtDesc,
                                              IsNetPrice, PriceBook, PrcShtLbl, ITEMTYPE, IEStatusLbl, ItemTypeLbl, PrcShtActiveLbl, PrcLnActiveLbl, PrcShtPromoLbl, PrcBookDesc, BaseBkLbl,
                                              ISBASE, PRCSHID, ITEMNMBR, PrcGrpID
                       FROM          dbo.BI_ExtPrcDetails
                       UNION
                       SELECT     PriceSheet, PriceType, ITEMDESC, UOFM, QTYFROM, QTYTO, Price, EQUOMQTY, QTYBSUOM, SEQNUMBR, DEX_ROW_ID, PRODTCOD, ITMCLSCD,
                                             USCATVLS_1, USCATVLS_2, USCATVLS_3, USCATVLS_4, USCATVLS_5, USCATVLS_6, Wgt, BASEUOFM, DECPLCUR, DECPLQTY, IEItemStatus,
                                             ItmTaxSched, PriceIsActive, PROMOTYP, PROMOLVL, PriceShtIsActive, PriceShtStart, PriceShtEnd, PriceShtCurr, PriceShtShtPromo, PriceShtDesc,
                                             IsNetPrice, PriceBook, PrcShtLbl, ITEMTYPE, IEStatusLbl, ItemTypeLbl, PrcShtActiveLbl, PrcLnActiveLbl, PrcShtPromoLbl, PrcBookDesc, BaseBkLbl,
                                             ISBASE, PRCSHID, ITEMNMBR, PRCGRPID
                       FROM         dbo.BI_ExtPrcGrpDetails) AS AllPrc

GO
/****** Object:  View [dbo].[BI_SheetCus]    Script Date: 9/28/2016 9:45:28 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[BI_SheetCus]
AS
SELECT     PRCSHID, PRODTCOD, LINKCODE, SEQNUMBR, PSSEQNUM, DEX_ROW_ID
FROM         dbo.RM00500
WHERE     (PRODTCOD = 'C')

GO
/****** Object:  View [dbo].[BI_PriceAssign]    Script Date: 9/28/2016 9:45:28 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[BI_PriceAssign]
AS
SELECT     dbo.RM00101.CUSTNMBR, dbo.RM00101.CUSTNAME, dbo.BI_SheetCus.PRCSHID AS PrcSht, dbo.BI_BookSheets.PRCSHID AS PrcBook,
                      dbo.SOP10110.STRTDATE AS PrcShtStart, dbo.SOP10110.ENDDATE AS PrcShtEnd, dbo.BI_SheetCus.DEX_ROW_ID, dbo.SOP10110.DESCEXPR AS PrcShtDesc,
                      RTRIM(dbo.SOP10110.PRCSHID) + ' | ' + RTRIM(dbo.SOP10110.DESCEXPR) AS PrcShtLBl
FROM         dbo.SOP10110 RIGHT OUTER JOIN
                      dbo.BI_SheetCus ON dbo.SOP10110.PRCSHID = dbo.BI_SheetCus.PRCSHID LEFT OUTER JOIN
                      dbo.BI_BookSheets ON dbo.BI_SheetCus.PRCSHID = dbo.BI_BookSheets.PRCSHID LEFT OUTER JOIN
                      dbo.RM00101 ON dbo.BI_SheetCus.LINKCODE = dbo.RM00101.CUSTNMBR
GROUP BY dbo.RM00101.CUSTNMBR, dbo.RM00101.CUSTNAME, dbo.BI_SheetCus.PRCSHID, dbo.BI_BookSheets.PRCSHID, dbo.SOP10110.STRTDATE,
                      dbo.SOP10110.ENDDATE, dbo.BI_SheetCus.DEX_ROW_ID, dbo.SOP10110.DESCEXPR, RTRIM(dbo.SOP10110.PRCSHID) + ' | ' + RTRIM(dbo.SOP10110.DESCEXPR)

GO
/****** Object:  View [dbo].[BI_ExtPrcDetails_Bycus]    Script Date: 9/28/2016 9:45:28 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[BI_ExtPrcDetails_Bycus]
AS
SELECT     dbo.BI_PriceAssign.CUSTNMBR, dbo.BI_PriceAssign.CUSTNAME, dbo.BI_ExtPrcDetails_All.PriceSheet, dbo.BI_ExtPrcDetails_All.PriceType,
                      dbo.BI_ExtPrcDetails_All.ITEMNMBR, dbo.BI_ExtPrcDetails_All.ITEMDESC, dbo.BI_ExtPrcDetails_All.UOFM, dbo.BI_ExtPrcDetails_All.QTYFROM,
                      dbo.BI_ExtPrcDetails_All.QTYTO, dbo.BI_ExtPrcDetails_All.Price, dbo.BI_ExtPrcDetails_All.EQUOMQTY, dbo.BI_ExtPrcDetails_All.QTYBSUOM,
                      dbo.BI_ExtPrcDetails_All.SEQNUMBR, dbo.BI_ExtPrcDetails_All.DEX_ROW_ID, dbo.BI_ExtPrcDetails_All.PRODTCOD, dbo.BI_ExtPrcDetails_All.ITMCLSCD,
                      dbo.BI_ExtPrcDetails_All.USCATVLS_1, dbo.BI_ExtPrcDetails_All.USCATVLS_2, dbo.BI_ExtPrcDetails_All.USCATVLS_3, dbo.BI_ExtPrcDetails_All.USCATVLS_4,
                      dbo.BI_ExtPrcDetails_All.USCATVLS_5, dbo.BI_ExtPrcDetails_All.USCATVLS_6, dbo.BI_ExtPrcDetails_All.Wgt, dbo.BI_ExtPrcDetails_All.BASEUOFM,
                      dbo.BI_ExtPrcDetails_All.DECPLCUR, dbo.BI_ExtPrcDetails_All.DECPLQTY, dbo.BI_ExtPrcDetails_All.IEItemStatus, dbo.BI_ExtPrcDetails_All.ItmTaxSched,
                      dbo.BI_ExtPrcDetails_All.PriceIsActive, dbo.BI_ExtPrcDetails_All.PROMOTYP, dbo.BI_ExtPrcDetails_All.PROMOLVL, dbo.BI_ExtPrcDetails_All.PriceShtIsActive,
                      dbo.BI_ExtPrcDetails_All.PriceShtStart, dbo.BI_ExtPrcDetails_All.PriceShtEnd, dbo.BI_ExtPrcDetails_All.PriceShtCurr, dbo.BI_ExtPrcDetails_All.PriceShtShtPromo,
                      dbo.BI_ExtPrcDetails_All.PriceShtDesc, dbo.BI_ExtPrcDetails_All.IsNetPrice, dbo.BI_ExtPrcDetails_All.PriceBook, dbo.BI_ExtPrcDetails_All.PrcShtLbl,
                      dbo.BI_ExtPrcDetails_All.ITEMTYPE, dbo.BI_ExtPrcDetails_All.IEStatusLbl, dbo.BI_ExtPrcDetails_All.ItemTypeLbl, dbo.BI_ExtPrcDetails_All.PrcShtActiveLbl,
                      dbo.BI_ExtPrcDetails_All.PrcLnActiveLbl, dbo.BI_ExtPrcDetails_All.PrcShtPromoLbl, dbo.BI_ExtPrcDetails_All.PrcBookDesc, dbo.BI_ExtPrcDetails_All.BaseBkLbl,
                      dbo.BI_ExtPrcDetails_All.ISBASE, dbo.BI_ExtPrcDetails_All.PRCSHID, dbo.BI_ExtPrcDetails_All.PrcGrpID
FROM         dbo.BI_ExtPrcDetails_All INNER JOIN
                      dbo.BI_PriceAssign ON dbo.BI_ExtPrcDetails_All.PriceSheet = dbo.BI_PriceAssign.PrcSht
GROUP BY dbo.BI_PriceAssign.CUSTNMBR, dbo.BI_PriceAssign.CUSTNAME, dbo.BI_ExtPrcDetails_All.PriceSheet, dbo.BI_ExtPrcDetails_All.PriceType,
                      dbo.BI_ExtPrcDetails_All.ITEMNMBR, dbo.BI_ExtPrcDetails_All.ITEMDESC, dbo.BI_ExtPrcDetails_All.UOFM, dbo.BI_ExtPrcDetails_All.QTYFROM,
                      dbo.BI_ExtPrcDetails_All.QTYTO, dbo.BI_ExtPrcDetails_All.Price, dbo.BI_ExtPrcDetails_All.EQUOMQTY, dbo.BI_ExtPrcDetails_All.QTYBSUOM,
                      dbo.BI_ExtPrcDetails_All.SEQNUMBR, dbo.BI_ExtPrcDetails_All.DEX_ROW_ID, dbo.BI_ExtPrcDetails_All.PRODTCOD, dbo.BI_ExtPrcDetails_All.ITMCLSCD,
                      dbo.BI_ExtPrcDetails_All.USCATVLS_1, dbo.BI_ExtPrcDetails_All.USCATVLS_2, dbo.BI_ExtPrcDetails_All.USCATVLS_3, dbo.BI_ExtPrcDetails_All.USCATVLS_4,
                      dbo.BI_ExtPrcDetails_All.USCATVLS_5, dbo.BI_ExtPrcDetails_All.USCATVLS_6, dbo.BI_ExtPrcDetails_All.Wgt, dbo.BI_ExtPrcDetails_All.BASEUOFM,
                      dbo.BI_ExtPrcDetails_All.DECPLCUR, dbo.BI_ExtPrcDetails_All.DECPLQTY, dbo.BI_ExtPrcDetails_All.IEItemStatus, dbo.BI_ExtPrcDetails_All.ItmTaxSched,
                      dbo.BI_ExtPrcDetails_All.PriceIsActive, dbo.BI_ExtPrcDetails_All.PROMOTYP, dbo.BI_ExtPrcDetails_All.PROMOLVL, dbo.BI_ExtPrcDetails_All.PriceShtIsActive,
                      dbo.BI_ExtPrcDetails_All.PriceShtStart, dbo.BI_ExtPrcDetails_All.PriceShtEnd, dbo.BI_ExtPrcDetails_All.PriceShtCurr, dbo.BI_ExtPrcDetails_All.PriceShtShtPromo,
                      dbo.BI_ExtPrcDetails_All.PriceShtDesc, dbo.BI_ExtPrcDetails_All.IsNetPrice, dbo.BI_ExtPrcDetails_All.PriceBook, dbo.BI_ExtPrcDetails_All.PrcShtLbl,
                      dbo.BI_ExtPrcDetails_All.ITEMTYPE, dbo.BI_ExtPrcDetails_All.IEStatusLbl, dbo.BI_ExtPrcDetails_All.ItemTypeLbl, dbo.BI_ExtPrcDetails_All.PrcShtActiveLbl,
                      dbo.BI_ExtPrcDetails_All.PrcLnActiveLbl, dbo.BI_ExtPrcDetails_All.PrcShtPromoLbl, dbo.BI_ExtPrcDetails_All.PrcBookDesc, dbo.BI_ExtPrcDetails_All.BaseBkLbl,
                      dbo.BI_ExtPrcDetails_All.ISBASE, dbo.BI_ExtPrcDetails_All.PRCSHID, dbo.BI_ExtPrcDetails_All.PrcGrpID

GO
/****** Object:  View [dbo].[BI_ExtPrc_BasePrices]    Script Date: 03/29/2017 10:20:21 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [dbo].[BI_ExtPrc_BasePrices]
AS
SELECT     ITEMNMBR, MAX(Price) AS Price, QTYFROM, ISBASE, PriceShtIsActive, PriceShtCurr, MAX(PriceShtStart) AS PriceShtStart, MAX(PriceShtEnd) AS PriceShtEnd,
                      UOFM
FROM         dbo.BI_ExtPrcDetails_All
GROUP BY ITEMNMBR, QTYFROM, ISBASE, PriceShtIsActive, PriceShtCurr, UOFM
HAVING      (QTYFROM <= 1) AND (ISBASE = 1) AND (PriceShtIsActive = 1) AND (PriceShtCurr = 'TTD') AND (MAX(PriceShtStart) <= GETDATE()) AND (MAX(PriceShtEnd)
                      >= GETDATE())

GO


Friday, September 23, 2016

SSRS - Multi-Select Drop Down parameter cannot be resized

This issue happens with IE10

To get the resize triangle to appear on the drop-down list


  • Press F12 to open the Developer Tab in IE10 (Or Click on the Gear> F12 Developer Tools)
  • The developer tab will open on the bottom
  • Click "Emulation" in the Far right on the developer tab
  • Change Document Mode to 9
  • The triangle should work correctly now
  • You cannot save this configuration
For a permanent solution,
Download this and use IE Enterprise mode to force the compatibility view of each website


Wednesday, September 21, 2016

Dynamics GP - Report showing all Invoices paid by a Check - Check Information Report


  • Reports>Check Information>Check Distribution>Modify
  • Click Include Dist Types> Selected
  • Select Purch and Taxes
  • Insert any other criteria
  • Print
  • First line shows Check no
  • Second line Shows Invoice
  • This is repeated for each invoice aplication

Friday, September 9, 2016

MBS - File Transfer Manager does not launch when you try to download somehting from Partersource / Customersource

IE 10 does not support launching File Transfer Manager via an ActiveX script.

To allow this to work, you must use IE9 emulation
http://stealthpuppy.com/work-around-for-getting-file-transfer-manager-to-download-from-technetmsdn/

  1. For Internet Explorer 11, press F12 to bring up the developer tools window
  2. Click the Emulation icon (bottom right) or press Ctrl+8
  3. Select ‘9’ under Document Mode
  4. Choose ‘Internet Explorer 9’ under User Agent String
In addition to this, you must enable all of the options under the Internet Options>Security>Custom Level

AND enable Active content, and Allow software to install under Internet Options>Advanced>Security

Disable any Popup blocker options or blocking software
Disable Protected Mode

Wednesday, September 7, 2016

Dynamics NAV - Demo Scripts - Financial

  • Create Account Schedule 
    • NAV has a totally customizable financial reporting module
      • Account Schedules = Rows
      • Column Layouts = Columns
      • Account Schedules can be displayed using any Column Layout Logic
        • Prior Year Comparison
        • Department (Or any Dimension) Breakdown
        • Budget Comparison
      • Show Balance Sheet Schedule
      • Income Statement Schedule
  • Show Budgets
    • You can import budgets, then make adjustments to them via budget journals, or reupload new budgets if required
    • All budget information can be referenced directly through the Account Schedules
  • Show Bank Reconciliation
    • All Cash receipts, Cheque payments, and Bank Transactions are captured against the bank rec module
    • Imports can be built for Bank statements, or they can be copy/pasted from an excel sheet
    • User must mark all matched transactions