Paid Transaction Removal Stored Procedure

(1) Share

Report

Posted on by Community Member

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

I have the same question (0)

All responses (9)

Answers (0)

Suggested answer

Community Member on at

Like (1)

Report
You've probably already found the answer, but try the following proc:

rmPaidTransactionRemoval

Was this reply helpful? Yes No
Suggested answer

soma 24,410 on at

Like (0)

Report

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!!!

Was this reply helpful? Yes No
Suggested answer

Almas Mahfooz 11,009 User Group Leader on at

Like (0)

Report

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

Was this reply helpful? Yes No
Suggested answer

Almas Mahfooz 11,009 User Group Leader on at

Like (2)

Report
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

Was this reply helpful? Yes No
Almas Mahfooz 11,009 User Group Leader on at

Like (0)

Report

btw are you also doing Balance forward consolidation?

Was this reply helpful? Yes No
DavidMO 1,030 on at

Like (0)

Report

Almas,

Could I possibly get a copy of this SQL job?

I need to do this for 8 companies and it would be wonderful to not have to reinvent the wheel.

Thank You!

David.Morinello@ascendlearning.com

Was this reply helpful? Yes No
sandipdjadhav 18,306 on at

Like (0)

Report

Great Post Almas..

Thanks for sharing.

Was this reply helpful? Yes No
Almas Mahfooz 11,009 User Group Leader on at

Like (0)

Report

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.

Was this reply helpful? Yes No
Almas Mahfooz 11,009 User Group Leader on at

Like (0)

Report

Sandip, you are welcome. : )

Was this reply helpful? Yes No