Skip to main content
Solved

Automated Schedule - adding BranchID = NULL to where clause


Forum|alt.badge.img

Hi All,

Build 23.212.0024

This question has resulted from debugging work we’ve done related to another question here:

https://community.acumatica.com/develop-customizations-288/automated-schedule-of-custom-processing-screen-processes-0-rows-25291?postid=98712#post98712


We ran an SQL profiler to attempt to debug the above issue where a processing screen is acting differently depending on whether it is run manually by a user or run using an automated schedule.

It revolves around an issue we’re finding when the query that builds the list of rows for the processing screen, Acumatica is appending different predicates to the where clause of the SQL depending on whether the it is run by a user or by an automated schedule.

Specifically the BranchID field

When run by a user, the SQL generated includes the following predicate:

( [POReceiptLine].[BranchID] IS NULL  OR ( [POReceiptLine].[BranchID] IN ( 23, 24))

However when run by an automated schedule, the predicate becomes this:

 [POReceiptLine].[BranchID] IS NULL

The tenant in question has two branches and POReceiptLines are being set with the relevant BranchID and not NULL.

Setting the BranchID in the Automated Schedule maintenance screen makes no difference. You would imagine that leaving the Branch unset on Automated Schedule maintenance screen should take all branches into account (but instead it seems to be setting the BranchID IS NULL part of the where clause).

Therefore when the automated schedule is running the processing graph, zero rows are returned because the BranchID is not NULL.

Can anyone explain or provide a way of controlling that particular element of the where clause?

 

 

 

Best answer by neil40

After more digging, this solution appears to tackle the issue.

https://stackoverflow.com/questions/56260358/difference-in-behavior-between-automation-schedule-and-process-all-action-on-pro

However it would be good to know WHY the account executing the automation schedule doesn’t have sufficient permissions to access the branches. How can an end user or administrator resolve this issue via configuration of the account or tenant.

View original

Forum|alt.badge.img
  • Jr Varsity III
  • August 1, 2024

Apologies, the title of the topic should be:

Automated Schedule - adding BranchID IS NULL to where clause


Forum|alt.badge.img
  • Jr Varsity III
  • August 1, 2024

Digging deeper and profiling the same queries across different instances it seems that this behaviour is to do with the set-up of the tenant. Some tenants pass the same BranchID predicate no matter whether the processing screen is run manually by a user or by an automated schedule. However the tenant in question here has this odd behaviour where the BranchID IS NULL predicate is used by the automation schedule (the OR statement is missed off when multiple branches are configured).

So what is it about the tenant set-up that controls this behaviour?


Forum|alt.badge.img
  • Jr Varsity III
  • August 1, 2024

After more digging, this solution appears to tackle the issue.

https://stackoverflow.com/questions/56260358/difference-in-behavior-between-automation-schedule-and-process-all-action-on-pro

However it would be good to know WHY the account executing the automation schedule doesn’t have sufficient permissions to access the branches. How can an end user or administrator resolve this issue via configuration of the account or tenant.


Chris Hackett
Community Manager
Forum|alt.badge.img
  • Acumatica Community Manager
  • August 1, 2024

Thank you for sharing your solution with the community @neil40!


Forum|alt.badge.img
  • Jr Varsity III
  • August 2, 2024

Thanks to Natesan on support who revealed that automated scheduler tasks run as the 'admin' user.

The reason this issue was occurring was because the admin user for the tenant in question didn't have access to the relevant branches and therefore the branch predicate on the clause was quite rightly not including any BranchIDs.

Giving the admin user access to the relevant branches solves the issue without requiring the code in the solution above.


Reply


Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings