Announcements
Hi, my customer would like to know the Stored procedure to preform the Paid Transaction removal from the Sales Routine. He would like to start the procedure without having to go into Dynamics. Do you know if it's possible to automate this Stored proc? Please let me know / Thanks in advance for all your answers or suggestions.
*This post is locked for comments
Sandip, you are welcome. : )
I haven't created SQL job, actually I automate through login to Dynamics GP at some specific day and time and run the process for multiple companies. I have emailed you a rough example of SQL job though, please adjust it according to your needs. also edit server and db name in job with your values, it's showing my system name and database name in the script I emailed you.
Great Post Almas..
Thanks for sharing.
btw are you also doing Balance forward consolidation?
Luckily we can do a SQL job for this particular Routine. It's not heavily depended over Sanscript. We need to be careful for below things.
1. Need to check SY00800 for possible active activity of paid transaction removal. You have to check for
BCHSOUR= 'RMPaidTrnRemove' AND BACHNUMB = 'RMPaidTrnRemove' AND CMPNYNAM = 'Your Company Name' .
If record exist then it means process is already running just stop and end transaction. If no record then go to next step.
2. rmPaidTransactionRemoval stored procedure is using two SQL global temporary tables. So you have to create two temp tables , before creating them check if they exist or not, if exist drop them. Give them some name like ##PaidTrxRemovalJobTemp1 and ##PaidTrxRemovalJobTemp2, use the same create syntax as of GP procedure. You can get it from SQL Profiler.
3. Now call the stored procedure, providing it your parameters.
BEGIN DECLARE @stored_proc_name char(32) DECLARE @retstat int DECLARE @param22 int set nocount on SELECT @stored_proc_name = 'TWO.dbo.rmPaidTransactionRemoval' EXEC @retstat = @stored_proc_name '##PaidTrxRemovalJobTemp1', '##PaidTrxRemovalJobTemp2', 0, 1, 1, 1, 1, 1, 0, 1, '2017.04.12', '2017.04.12', 1, '', 'ÞÞÞÞÞÞÞÞÞÞÞÞÞÞÞÞÞÞÞÞÞÞÞÞÞÞÞÞÞÞÞÞÞÞÞÞÞÞÞÞÞÞÞÞÞÞÞÞÞÞÞÞÞÞÞÞÞÞÞÞÞÞÞÞÞ', '', 'ÞÞÞÞÞÞÞÞÞÞÞÞÞÞÞ', 1, '2017.04.12', 1, 'Z-US$', @param22 OUT SELECT @retstat, @param22 set nocount on END
We have automate this procedure for one of our client, they removed paid transaction at then end of each month. You can discuss with in details. almas@evinciblesolutions.com
The procedure used for Paid Transaction Removal is "rmPaidTransactionRemoval". But, it is not a recommended suggestion if you calling this procedure through backend. You need to consider many validations. GP is calling this stored procedure after all the validation has been completed.
Please go through this procedure and you will get some idea. Also first try this on test environment.
Hope this helps!!!
You've probably already found the answer, but try the following proc:
rmPaidTransactionRemoval
#1
Almas Mahfooz
3
User Group Leader
Share
Report
All responses (
Answers (
