Dynamics GP

Script to insert a line into the batch table (SY00500)

2010-03-10T13:21:00Z

This isn't set to work against all modules, but if you look and see what's going on, it can be easily adopted.

 -- =============================================
-- Steve Gray
-- 4Penny.net
-- 941-747-3669
-- =============================================


IF EXISTS (SELECT name 
       FROM   sysobjects 
       WHERE  name = N'_4P_sy00500_UPD' 
       AND    type = 'P')
    DROP PROCEDURE _4P_sy00500_UPD
GO


CREATE PROCEDURE _4P_sy00500_UPD


--  _4P_sy00500_UPD 'import','RM_Sales', 'sgray',3


@vchrBatchNumber varchar(15),
@bchsourc varchar(15),
@vchrUserID varchar(15) ,
@intSeries int = 3


AS


declare @currDocAmount as money
declare @intDocCount as int
declare @dtDate as datetime


set @dtDate = convert(varchar(10), getdate(),101)


--============================================================================
-- create the batch, if necessary
--============================================================================


--see if the batch exists
if not exists (select dex_row_id 
                    from sy00500 with (nolock) 
                    where bachnumb = @vchrBatchNumber 
                        and bchsourc = @bchsourc)
    insert into sy00500 (GLPOSTDT,  BCHSOURC  ,BACHNUMB        ,  SERIES    ,  MKDTOPST,  NUMOFTRX,  RECPSTGS,  DELBACH,  MSCBDINC,  BACHFREQ,  RCLPSTDT  ,  NOFPSTGS,  BCHCOMNT,  BRKDNALL,  CHKSPRTD,  RVRSBACH,  USERID     ,  CHEKBKID      ,  BCHTOTAL,  BCHEMSG1,  BCHEMSG2,  BACHDATE  ,  BCHSTRG1,  BCHSTRG2,  POSTTOGL,  MODIFDT,  CREATDDT,  NOTEINDX,  CURNCYID,  BCHSTTUS,  CNTRLTRX,  CNTRLTOT,  PETRXCNT,  APPROVL,  APPRVLDT,  APRVLUSERID,  ORIGIN,  ERRSTATE,  GLBCHVAL,  Computer_Check_Doc_Date,  Sort_Checks_By,  SEPRMTNC,  REPRNTED,  CHKFRMTS,  TRXSORCE,  PmtMethod,  EFTFileFormat) 
        values          (@dtDate ,  @bchsourc ,@vchrBatchNumber,  @intSeries,  0       ,  0       ,  0       ,  0      ,  0       ,  1       ,  '1/1/1900',  0       ,  ''      ,  0       ,  0       ,  0       ,  @vchrUserID,  'ST OPERATING',  0.00    ,  0       ,  0       ,  '1/1/1900',  ''      ,  ''      ,  0       ,  @dtDate,  @dtDate ,  0       ,  0       ,  0       ,  0       ,  0       ,  0       ,  0      ,  @dtDate ,  ''         ,  1     ,  0       ,  0       ,  '1/1/1900'             ,  0             ,  0       ,  0       ,  0       ,  ''      ,  0        ,  1)


 


set @intDocCount = 0
set @currDocAmount = 0


if @bchsourc = 'Sales Entry' begin
    --get the number of docs and the sum
    select 
            @intDocCount = count(docamnt), 
            @currDocAmount = isnull(sum(docamnt),0)
        from sop10100 with (nolock)
        where bachnumb = @vchrBatchNumber
            and voidstts = 0
end 
if @bchsourc = 'RM_Sales' begin
    --get the number of docs and the sum
    select 
            @intDocCount = count(docamnt), 
            @currDocAmount = isnull(sum(dbo.f_4p_Modifier2(docamnt, rmdtypal)),0)
        from rm10301 with (nolock)
        where bachnumb = @vchrBatchNumber
end 
       
if @bchsourc = 'IV_Trxent'
    select 
            @intDocCount = count(trxqtytl), 
            @currDocAmount = sum(trxqtytl)
        from iv10000 with (nolock)
        where bachnumb = @vchrBatchNumber
    
if @bchsourc = 'PM_Trxent'
    select 
            @intDocCount = count(DOCAMNT), 
            @currDocAmount = sum(DOCAMNT)
        from pm10000 with (nolock)
        where bachnumb = @vchrBatchNumber
      


--update the batch with that info
if @currDocAmount = 0 begin
    delete sy00500
        where bachnumb = @vchrBatchNumber and bchsourc = @bchsourc
end else begin
    update sy00500 set 
            numoftrx = @intDocCount, 
            nofpstgs = 0, 
            bchtotal = @currDocAmount
        where bachnumb = @vchrBatchNumber and bchsourc = @bchsourc
    
end         



GO


grant all on _4P_sy00500_UPD to public

Script to increment the next SOP number

2010-03-05T15:35:00Z

This will work in most any module with small modifications.

It will return the next SOP number

To run, execute it in a company database. At the top of the procedure is a comment section, edit the values for DOCID and SOPTYPE and execute those four lines

-- =============================================
-- Steve Gray
-- 4Penny.net
-- 
-- =============================================


IF EXISTS (SELECT name 
       FROM   sysobjects 
       WHERE  name = N'FP_getNextOrderNumber' 
       AND    type = 'P')
    DROP PROCEDURE FP_getNextOrderNumber
GO


CREATE PROCEDURE FP_getNextOrderNumber 


/*


declare @DOCID varchar(15), @sopnumbe varchar(21), @soptype int
select @DOCID = 'STANDARD', @sopnumbe = '', @soptype = 3


execute FP_getNextOrderNumber @DOCID, @sopnumbe = @sopnumbe output, @SOPTYPE = @soptype
select @sopnumbe,@@trancount


*/



@DOCID varchar(15),
@sopnumbe varchar(21) output,
@SOPTYPE int


AS
    
declare @intPos int
declare @vchrSopnumbeBegin varchar(21)
declare @vchrSopnumbeEnd varchar(21)
declare @vchrSopnumbeNew varchar(21)
declare @vchrTemp varchar(21)
declare @intTemp int
declare @intRowCount int



-- turn off the rowcount display
set nocount on


--get a new sop number
declare @blnOKtoExit bit
set @blnOKtoExit = 0


begin tran
    while @blnOKtoExit = 0
    begin
        --assume that this is all going to work
        set @blnOKtoExit = 1


        select 
                @sopnumbe = rtrim(sopnumbe)
            from sop40200  with (TABLOCKX HOLDLOCK)
            where docid = @DOCID
                and soptype = @soptype


        --loop through the sopnumber backwards, find the first non-numeric character.
        set @intPos = len(@sopnumbe)
        while isnumeric(substring(@sopnumbe,@intPos,1)) = 1
        begin
            set @intPos = @intPos - 1
        end


        --get the numeric portion       
        set @vchrSopnumbeEnd = right(rtrim(@sopnumbe),len(@sopnumbe) - @intPos)
        set @vchrSopnumbeBegin = left(@sopnumbe,@intPos)
        
        --convert to int
        set @intTemp = @vchrSopnumbeEnd


        --increment
        set @intTemp = @intTemp + 1
        
        --stick the new number back on the prefix
        set @vchrSopnumbeEnd = right('000000000000000000000000000' + convert(varchar(21),@intTemp),len(@sopnumbe) - @intPos)
        set @vchrSopnumbeNew = @vchrSopnumbeBegin + @vchrSopnumbeEnd
        
        --update sop40200       
        update sop40200 set sopnumbe = @vchrSopnumbeNew where docid = @DOCID and sopnumbe = @sopnumbe


        -- if there is no rowcount, do it again
        set @intRowCount = @@rowcount
        if @intRowCount = 0 begin
            set @blnOKtoExit = 0
        end


        --make sure this number is not already assigned
        --be sure that an index exists in sop10100 for this
        if exists (select sopnumbe from sop10100 with (nolock) where sopnumbe = @sopnumbe and soptype = @soptype) begin
            set @blnOKtoExit = 0
        end 


        --be sure that an index exists in sop30200 for this
        if exists (select sopnumbe from sop30200 with (nolock) where sopnumbe = @sopnumbe and soptype = @soptype) begin
            set @blnOKtoExit = 0
        end 
    end


commit tran


 


GO


grant all on FP_getNextOrderNumber to public



    
--select * from sop40200

GP10: Cannot access this form because the dictionary containing it is not loaded

2010-01-20T20:34:00Z

Cannot access this form because the dictionary containing it is not loaded

Two issues:

Go into Tools > Setup > System > Alternate/Modified Forms and Reports, select a user, select a product, select 'Windows'. Set all security back to the default form.

Go into Tools > Utilities > System > Reconcile and reconcile 'Security Assignment Alternate/Modified Operations

That'll fix it.