Site Search:
Sign in | Join | Help

Dynamics GP

Notes, Tips and Tricks on Developing in Dynamics GP
  • Script to insert a line into the batch table (SY00500)

    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

    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

    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.

More Posts Next page »