Thursday, October 29, 2015

get/change operation on table 'gpCustomerPOSetup' cannot find the table

Cause:
This error is related to PSTL and the SOP PO Number check tool.
It affects table DYNAMICS.dbo.gpCustomerPOSetup

Related Errors:
Clicking on PSTL throws error "Invalid Access"
solution: Delete the PSTL shortcut and re-add it

Resolution:
Launch PSTL at least once
It will install all missing tables

Tuesday, October 27, 2015

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