Dynamics GP - Historical Stock Status - Divide by zero error

There are corrupted item transaction records for that date range.
Solution here

http://support.microsoft.com/kb/855945


In my case, there was a single record for 0 qty and no value in the TRXSORCE column.
There were some transactions with no UofM, but these were all cost adjustments, and had no impact on the report.


I deleted the single blank record from the iv30300, and the issue was resolved.

SQL - SSAS - Time dimension table and Auto-fill procedure. Auto Generate date and time index values.

CREATE TABLE [dbo].[DimTime](
[TransactionDate] [datetime] NOT NULL,
[DateText] [varchar](10) NULL,
[CalenderYear] [int] NULL,
[CalenderQuarter] [varchar](50) NULL,
[CalenderMonth] [varchar](50) NULL,
[CalenderMonthNumber] [int] NULL,
[CalenderWeek] [varchar](50) NULL,
[CalenderWeekNumber] [int] NULL,
[FiscalYear] [int] NULL,
[FiscalQuarter] [varchar](50) NULL,
[FiscalPeriod] [varchar](50) NULL,
[FiscalPeriodNumber] [int] NULL,
[Day] [int] NULL,
[DayOfWeek] [tinyint] NULL,
 CONSTRAINT [PK_DimTime] PRIMARY KEY CLUSTERED
(
[TransactionDate] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
--------------------------------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[buildDimTime]
-- Add the parameters for the stored procedure here
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

    -- Insert statements for procedure here
declare @date datetime
declare @EndDate datetime
--set @date = '01/01/2006'
 set @date = DATEADD(yy, DATEDIFF(yy, 0, GETDATE())-3, 0) --Only calculates last 3 years
set @EndDate = (DATEADD(ms, -3, DATEADD(yy, DATEDIFF(yy, 0, GETDATE()) + 2, 0)))
delete DimTime
while @date <= @EndDate
begin
insert into dimTime
select @date as TransactionDate,convert(varchar(10),@date,111),
DatePart(year,@date) as Year,
cast(DatePart(year,@date) as varchar(4)) + ' ' + 'Q' + convert(CHAR,DatePart(quarter,@date)) as QuarterNumber,
cast(DatePart(year,@date) as varchar(4)) + ' ' + convert(CHAR,DateName(month,@date)) as MonthName,
DatePart(month,@date) as MonthNumber,
'Week' + convert(CHAR, DATEPART(wk, @date)) as CalenderWeek,
DATEPART(wk, @date) as CalenderWeekNumber,
--'Week' as WeekNumber,
--DateName(day,@date) as DayNumber,
DatePart(year,@date) as FiscalYear,
'Q' + convert(CHAR,DatePart(quarter,@date)) as FiscalQuarter,
convert(CHAR,DateName(month,@date)) as FiscalPeriod,
DatePart(month,@date) as FiscalPeriodNumber,
(select datediff(d, (select DATEADD(yy, DATEDIFF(yy,0,@date), 0)),@date)) as DayNumber,
DATEPART(dw, @date) as DayOfWeek
set @date = @date + 1
end
--update dimTime set DateText = convert(varchar(10),TransactionDate,111)
--from dimTime

END

Dynamics GP - SQL View - Field Service Invoices

SELECT        dbo.SVC00200.SRVTYPE, SVCHist.SRVRECTYPE, SVCHist.CALLNBR, SVCHist.SOPTYPE, SVCHist.SOPNUMBE, SVCHist.Invoiced_Amount, SVCHist.Amount_To_Invoice, SVCHist.CREATDDT, SVCHist.CREATETIME,
                         dbo.SVC00200.CUSTNMBR, dbo.SVC00200.CUSTNAME, dbo.SVC00200.PORDNMBR, dbo.SVC00200.Customer_Reference, dbo.SVC00200.COMPDTE, dbo.SVC00200.COMPTME, SOP.DOCDATE AS FinalDocDate,
                         SOP.DocAmt AS FinalDocAmt
FROM            (SELECT        SRVRECTYPE, CALLNBR, SOPTYPE, SOPNUMBE, Invoiced_Amount, Amount_To_Invoice, CREATDDT, CREATETIME, DEX_ROW_ID
                          FROM            dbo.SVC00220
                          UNION
                          SELECT        SRVRECTYPE, CALLNBR, SOPTYPE, SOPNUMBE, Invoiced_Amount, Amount_To_Invoice, CREATDDT, CREATETIME, DEX_ROW_ID
                          FROM            dbo.SVC30220) AS SVCHist INNER JOIN
                         dbo.SVC00200 ON SVCHist.CALLNBR = dbo.SVC00200.CALLNBR INNER JOIN
                             (SELECT        SOPTYPE, DOCID, SOPNUMBE, DOCDATE, CASE WHEN soptype = 4 THEN docamnt * - 1 ELSE docamnt END AS DocAmt
                               FROM            dbo.SOP10100
                               UNION
                               SELECT        SOPTYPE, DOCID, SOPNUMBE, DOCDATE, CASE WHEN soptype = 4 THEN docamnt * - 1 ELSE docamnt END AS DocAmt
                               FROM            dbo.SOP30200) AS SOP ON SVCHist.SOPNUMBE = SOP.SOPNUMBE

Dynamics GP - Add SOP PO Number to RM Aged trial Balance

Join the RM Open file and RM Hist File to the RM Statement Temporary file

Use a conditional expression to display the po number from the open file, and if blank, then display the po number from the hist file.

Dynamics GP - Extended Pricing View - list of all prices on all sheets

SELECT     IV10402.PRCSHID, IV10402.EPITMTYP, IV10402.ITEMNMBR, IV00101.ITEMDESC, IV10402.UOFM, IV10402.QTYFROM, IV10402.QTYTO, IV10402.PSITMVAL, IV10402.EQUOMQTY,

                      IV10402.QTYBSUOM, IV10402.SEQNUMBR, IV10402.DEX_ROW_ID, IV00101.ITMCLSCD, IV10401.PRODTCOD

FROM         IV10402 INNER JOIN

                      IV00101 ON IV10402.ITEMNMBR = IV00101.ITEMNMBR INNER JOIN

                      IV10401 ON IV10402.PRCSHID = IV10401.PRCSHID AND IV10402.ITEMNMBR = IV10401.ITEMNMBR

[Microsoft][SQL Server Native Client 10.0][SQL Server]The EXECUTE permission was denied on the object 'zDP_SY02100SS_1', database 'master', schema 'dbo'.

http://support.microsoft.com/kb/2870546

Dynamics GP - Unable to Find a Table when installing a chunk file

Your account does not have access to the Dynamics GP program files folder.
Give your account full access and roll down permissions on all subfolders.