We have a new Business Role with 226 Entitlements. When applying the changes, the request that is generated has all 226 Change Items even though we have the Admin > System > Max items per change request set to 10.
The custom Approval workflow is where the error occurs, it is the same Approval Workflow that has been functioning flawlessly for two years for all our other requests. The error is:
Exception caught during script evaluation. Script 'SQL Query', ScriptID=17:WPDS, JobID=343624:WPDS, JobRef='62275', ProcessRef='WF_AP_MISO_ITBG', JobRowVersion=4, NodeName='Inspect Activities in CR', ProcessNodeUUID=6693562d-4a95-4d53-98e9-f4ba4709eae5, JobNodeID=3827829:WPDS, WorkItem=2916970:WPDS:1, FIFO=12050990, Sort Order=1. Exception was: com.workpoint.common.exception.ScriptEngineException: java.sql.SQLException: ORA-01460: unimplemented or unreasonable conversion requested
Error occurred in Statement #1 in Script ID 17:WPDS, Script Name = 'SQL Query', Script type = 'Action'. Job ID = 343624:WPDS, Process Ref = 'WF_AP_MISO_ITBG', Node Name = 'Inspect Activities in CR', WorkItem = 2916970:WPDS:1
I also tested another Role which has the same issue but with 89 Entitlements. I added 70 Entitlements and applied the changes and the request didn't fail. Then I added the remaining 19 and those also worked with no problem. So it seems 70 is the magic number.
It appears that for some reason the Max items per change request is not being enforced for the Role changes.
Anyone else experiencing this?
You have encountered a practical limit to the complexity of the SQL in the SQL node. The limitation is not a specific number, but the aggregate size in bytes of an intermediate object that is used internally to hold the data for the SQL statement.
Is this a custom SQL node?
You may have to reconsider the design of the SQL in this node to avoid this limitation. For example you may need to use a store procedure, or split the logic across multiple nodes in some way.
This really is a workflow design issue and I cannot really advise you without knowing the actual workflow.