eConnect error 574

574 Apply Amount (APPTOAMT) + Discount Taken Amount (DISTKNAM) + Write Off (WROFAMNT) is > then the amount remaining on the invoice

Sometimes you will get this error when you are calling the eConnect method taRMApply , this issue could occur if your trying to apply amount grater than the remaining amount of an invoice.

System will sum the applied amount , Discount Taken Amount and  Write Off amount which been pass to the taRMApply method and compare it with the remaining invoice amount (CURTRXAM) in the RM Open table (RM20101), if the sum >  the remaining invoice amount system will throw eConnect error 574.

This issue will not occur in  Microsoft Dynamics GP , because if you try to apply cash document which has amount grater than the remaining invoice amount, GP system will partially apply the cash receipt amount . e.g. if the invoice amount was 400 USD and the remaining amount 100 USD and  you applied 500 USD cash receipt , GP will apply 100 USD to the invoice and the cash receipt will have remaining 400 USD can be applied to other invoices. If you try the same scenario above with eConnect system will through an error "Apply Amount (APPTOAMT) + Discount Taken Amount…..." to work around this issue you need to use the following logic:

Before apply the cash receipt amount to an invoice , you need to compare the invoice remaining  amount with cash amount.

If the cash amount >= remaining invoice amount then

applied amount =remaining invoice amount

Else

applied amount =invoice amount

End IF

eConnect error 570

Error No 570, Amount to apply (APPTOAMT) is larger than the invoice amount

Sometimes you will get this error when you are calling the eConnect method taRMApply , this issue could occur if your trying to apply amount grater than the original invoice amount of an invoice.

System will check the applied amount which been pass to the taRMApply method against the original invoice amount (ORTRXAMT) in the RM Open table (RM20101), if the applied amount > original invoice amount then system will throw eConnect 570 error

Actually this issue will not occur in  Microsoft Dynamics GP , because if you try to apply cash document which has amount grater than the invoice amount, GP system will partially apply the cash receipt amount . e.g. if the invoice amount was 560 USD and you applied 1000 USD cash receipt , GP will apply 560 USD to the invoice and the cash receipt will have remaining 440 USD can be applied to other invoices. If you try the same scenario above with eConnect system will through an error "Amount to apply (APPTOAMT) is larger than the invoice amount" to work around this issue you need to use the following logic:

Before apply the cash receipt amount to an invoice , you need to compare the invoice amount and with cash amount.

If the cash amount >= invoice amount then

applied amount =invoice amount

Else

applied amount =cash amount

End If

How to stop / exit SQL process on specific date time condition

If you have an SP been used to run periodically on night and you want to stop the process on the morning time to reduce the load on the system, initially you can configure a SQL job to start run on 11:55 PM  and end at 08:00 AM, this will work fine the process completed before the end time (08:00AM). But if the process not yet completed the SQL will not stop the job as excepted and the process will continue running.  

To achieve this requirement there are two different methods to do that and this will be depend on the SP structure

1- SQL while Loop used:

 In this case we will try to stop the loop on time by using the while condition and to do that we need to define the stop date time as variable and we will set this variable to the next day date and required time, e.g. if we want the SP to be stop on next day on 08:00AM and to today date 22nd November 2011 so the stop date time  should be 08:00AM  23nd November 2011. The following script define the @STOP_TIME and it will set it to the next day and time.

In the while loop system will check two conditions , 1^st next record to be processed available and  the date /time is before the next day 08:00AM, since system getting the record one by one to be processed so every time system will check the date /time condition and the system will stop the process when it reached to next day 08:00AM even if there are records need to be processed.

DECLARE @STOP_TIME DATETIM

SELECT @STOP_TIME=CAST( CONVERT (VARCHAR(10),DATEADD(D,1,GETDATE()),102)+ ' 08:00:00 AM' AS DATETIME)

SELECT  @NextID = MIN(ROWID) from Table_A (nolock) WHERE PROCESSED = 0

WHILE ISNULL(@NextID,0) <> 0 and GETDATE()< @STOP_TIME

BEGIN

      .........Process Data..........

      .........Process Data..........

      .........Process Data..........

      .........Process Data..........

     

      SELECT @NextID = MIN(ROWID) FROM  Table_A  (nolock)WHERE PROCESSED = 0

      if @NextID is null

            begin

                  set @NextID=0

            end

End  

 2-SQL Cursor /SQL while Loop used or not used

The following method can be used if you have while loop in the SP or not, in this method we need to create another SQL job which will be schedule to run daily on 08:00 AM and in this SQL job we will use sp_stop_job to stop the first SQL Job. But there is disadvantage of using this method specially if the If a first SQL is currently executing a step of type CmdExec or PowerShell, the process being run (for example, MyProgram.exe) is forced to end prematurely. Premature ending can result in unpredictable behavior such as files in use by the process being held open. Consequently, sp_stop_job should be used only in extreme circumstances if the job contains steps of type CmdExec or PowerShell.

Aged Trial Balance report for GP AR module

The following view can be used to generate the aged trial balance report for GP AR module, the view will return all the paid / unpaid  receivables transactions in Dynamics GP of the following document types:

·        'Sale / Invoice'

·        'Debit Memo'

·        'Finance Charge'

·        'Service Repair'

·        'Warranty'

 Also this view will calculate how many days overdue unapplied transaction and will allocate the aged bucket of each transaction.

Note: this view will not show the credit documents (payments, return etc)



 CREATE VIEW DYNSOL_View_AR_AgedTB

AS

SELECT CR.CUSTNMBR [Customer No],

       CR.CUSTNAME [Customer Name],

       CR.PYMTRMID [Payment Terms],

       CR.CUSTCLAS [Customer Class],

       CR.PRCLEVEL [Price Level],

       CASE RM.RMDTYPAL

          WHEN 1 THEN 'Sale / Invoice'

          WHEN 3 THEN 'Debit Memo'

          WHEN 4 THEN 'Finance Charge'

          WHEN 5 THEN 'Service Repair'

          WHEN 6 THEN 'Warranty'

          ELSE 'Other'

          END [Document Type],

       RM.DOCNUMBR [Document Number],

       RM.DOCDATE [Document Date],

       RM.DUEDATE [Due Date],

       RM.ORTRXAMT [Document Amount],

       RM.CURTRXAM [Unapplied Amount],

       SUMR.LASTPYDT [Last Payment Date],

       SUMR.LPYMTAMT [Last Payment Amount],

          CASE WHEN CURTRXAM <> 0

          THEN        

               CASE

          WHEN RM.DUEDATE >= GETDATE() THEN 0

          ELSE DATEDIFF(DD, RM.DUEDATE, GETDATE())

          END

       ELSE 0 END   [No of Days Past],

 

       CASE WHEN DATEDIFF(DAY,RM.DOCDATE,GETDATE())+1 between 0 and 30 THEN (RM.CURTRXAM) else 0 END  AS [0-30],

          CASE WHEN DATEDIFF(DAY,RM.DOCDATE,GETDATE())+1 between 31 and 60 THEN (RM.CURTRXAM) else 0 END  AS [31-60],  

          CASE WHEN DATEDIFF(DAY,RM.DOCDATE,GETDATE())+1 between 61 and 90 THEN (RM.CURTRXAM) else 0 END  AS [61-90],  

          CASE WHEN DATEDIFF(DAY,RM.DOCDATE,GETDATE())+1 between 91 and 120 THEN (RM.CURTRXAM) else 0 END  AS [91-120],

          CASE WHEN DATEDIFF(DAY,RM.DOCDATE,GETDATE())+1 between 121 and 150 THEN (RM.CURTRXAM) else 0 END  AS [121-150],

          CASE WHEN DATEDIFF(DAY,RM.DOCDATE,GETDATE())+1 between 151 and 180 THEN (RM.CURTRXAM) else 0 END  AS [151-180],

          CASE WHEN DATEDIFF(DAY,RM.DOCDATE,GETDATE())+1 >= 181  THEN (RM.CURTRXAM) else 0 END  AS [181=>]

FROM   RM20101 RM

     INNER JOIN

       RM00101 CR

       ON RM.CUSTNMBR = CR.CUSTNMBR

     INNER JOIN

       RM00103 SUMR

       ON RM.CUSTNMBR = SUMR.CUSTNMBR

WHERE  RM.VOIDSTTS = 0 AND RM.RMDTYPAL in (1,2,3,4,5,6)



How to use Macros & Scheduled Tasks to perform automated tasks for Microsoft Dynamics GP

In this topic  we will try  to shed light on how to take advantage of the Scheduled Tasks function provided by most operating systems which will  help the System Administrator to carry out the tasks entrusted to him.

in Great Plains  there are many of tasks which can be automated e.g. generating certain reports or run specific tasks such as check links. As example we will see how we can run the check links automatically using GP Macro &  Scheduled Task.

1-Create a Macro to run the Microsoft Dynamics GP:

·        Start the Microsoft Dynamics GP and before you enter the user ID & password click Alt+F8 to begin macro recording. System will show Name the Macro box , give proper name to the macro and save it in GP folder.

·        Continue with entering the user ID & password and select the login company and when you login Microsoft Dynamics GP click Alt+F8 to stop macro recording.

2-Create a Macro to execute the Task (e.g. check links) :

·        Login to  the Microsoft Dynamics GP.

·        Click Alt+F8 to begin macro recording, give proper name to the macro and save it in GP folder.

·        Select Microsoft Dynamics GP> Maintenance > Check Links> Finance > CM Setup & click OK.

·        Close the report.

·        Click Alt+F8 to stop macro recording.

3-Change the Macro Structure to stop the warning messages :

Sometimes the running macros show warning messages to the user , to stop those warning messages we can force the system to save those alerts in the log file rather than show it as warning messages.

Open the Macro files using any text editor program and add the following line to the top of macro file:

Logging file ':C: /TEST/LOGF.TXT '

This will save the alerts to log file LOGF.TXT located in the C\Test folder. You can change the path and file name and save it in proper location e.g. GP folder.

4-Create Batch File :

Batch is a text file that contains a sequence of commands which can allow user to execute commands or run applications etc.

To create the batch file open the notepad and add the following lines :

 dynamics dynamics.set XXX.mac

 cd C:\GP installation path

 dynamics dynamics.set XXX.mac

replace the XXX with macro name and GP installation path with actual GP folder:

dynamics dynamics.set Start.mac

cd C:\Program Files\Microsoft Dynamics\GP$GP10

dynamics dynamics.set Start.mac

Note:

·        when you try to save this file , select save as and select All file, the type file name and use bat extension ,because if you save directly it will be saved with txt extension.

·        You can check & test the batch file by double click on it.

5-Use Scheduled Task :

The last step is to automate the executing of the batch files , to do that we can use windows scheduled task:

Start>All Programs > Accessories\System Tools>Scheduled Tasks

Then double click on Add Scheduled Task and follow the wizard.



How to use the Dexterity Script Debugger to trace bugs and issues in Microsoft Dynamics GP

Dexterity Script Debugger

We can use the Dexterity Script Debugger to trace bugs and performance issues in Microsoft Dynamics GP.

The script debugger is accessed from the Debug menu, which is available in both tools mode and test mode. The following is a list of the debugger-related items in the Debug menu.

But in the live environment this option not available by default and to make enable the Dexterity Script Debugger option in live follow these steps:

1.	Change the Dex.ini file by adding the following lines to the [General] section. ScriptDebugger=TRUE ScriptDebuggerProduct=0 Note:The product ID is typically set to zero for Microsoft Business Solutions - Great Plains, but the product ID can be the product ID of any product in the Dynamics.set file.
2.	Start Microsoft Dynamics GP or Microsoft Business Solutions - Great Plains, and then log on to the application.
3.	In the application, move to a location just before the section of code that you want to analyze.
4.	To analyze the code, follow these steps: a. Click Debug, click Log Scripts, and then specify a path for the Script.log file. b. Click Debug, and then click Profile Scripts. c. Click Debug, and then click Clear Profile.
5.	Perform the action that you want to analyze.
6.	To stop the analysis, follow these steps: a. Click Debug, and then click Log Scripts. b. Click Debug, click Save Profile, and then specify a path for the Profile.txt file. c. Click Debug, and then click Profile Scripts.
7.	Locate the Script.log file and the Profile.txt file, and then send these files to a developer or to the support team for analysis.

The Dexterity Script Debugger uses two methods to trace what is occurring in the code:

1-Script Profiler

The Script Profiler lets you analyze script and table activity during test mode, allowing you to optimize your scripts and streamline your application. Once you’ve started the profiler, it records the name of every script run and how much time is spent running each script. The Script Profiler also tracks how many times tables were accessed and the average and total times for specific operations on each table accessed. The time required by the profiler itself is automatically deducted from the profile, so that the profile shows the time requirements for only your application, for more information refer to dexterity help.

2-Script Logger

The script logger stores a record of all the attached scripts, procedures, user-defined functions, and function library functions that were run while your application was being executed in test mode. It also lists any parameters passed to the procedures or functions that were called. for more information refer to dexterity help.

How to Stop the SQL Jobs

There are different ways to stop the SQL jobs, we can use SQL Server Management Studio or we can use Transact SQL  command to stop SQL jobs also we can use Kill process for special cases to stop the SQL job.
1-SQL Server Management Studio:
    a. Open the SQL Server Management Studio

  
 b. in the Object Explore select SQL Server Agent and expand this node then select/expand  Jobs node and  select the Job name and right click and select Stop Job
2-Use Transact SQL  command:
sp_stop_job
      [@job_name =] 'job_name'
    | [@job_id =] job_id
    | [@originating_server =] 'master_server'
    | [@server_name =] 'target_server'
When SQL Server Agent receives the stop notification, it waits for the job step that is currently running to finish before it processes the cancel request. Some long-running Transact-SQL statements such as BACKUP, RESTORE, and some DBCC commands can take a long time to finish. When these are running, it may take awhile before the job is canceled. Stopping a job causes a "Job Canceled" entry to be recorded in the job history.
If a job is currently executing a step of type CmdExec or PowerShell, the process being run (for example, MyProgram.exe) is forced to end prematurely. Premature ending can result in unpredictable behavior such as files in use by the process being held open. Consequently, sp_stop_job should be used only in extreme circumstances if the job contains steps of type CmdExec or PowerShell.
Pasted from <http://msdn.microsoft.com/en-us/library/ms182793.aspx>
USE msdb ;
GO
EXEC dbo.sp_stop_job
    N'Weekly Sales Data Backup' ;
GO
3-Kill Process:
 sometimes using  the  SQL Server Management Studio or we can use Transact SQL  command failed to stop the SQL jobs (specially if the Job was running for long periods continually and it executing a step of type CmdExec or PowerShell) in this case we can use Kill command to terminate the process:
1. Find the current status of the SQL job by run
    EXEC dbo.sp_help_job @job_name = N'Job Name'
2. From the first step you will get the SQL job ID (job_id)
e.g. 85289469-7A84-41F2-A761-B73E239B7A1C
3. Run sp_who2 active which shows all the sessions that are currently established in the database. These are denoted as SPID’s, or Server process Id’s, in the result of sp_who2 active under ProgramName field look for SQL Job ID which you got it in step (2) you will find some thing like: 
SQLAgent - TSQL JobStep (Job 0x69942885847AF241A761B73E239B7A1C : Step 1)   after you allocate the exact row noted down the  SPID of that row (e.g. 172).       
4. open new query use Kill command to stop the job
kill 172