normunds.sarsuns
May 12, 2020

Creating Flex Business Rule

I am trying to create simple Flex Business rule. I am trying to force the usage of Closing Codes if user is trying to close "Breakdown" type of work. SQL querry (which does not work) is: SELECT NULL

FROM R5EVENTS

WHERE EVT_SQLIDENTITY =:ROWID

AND EVT_JOBTYPE = 'BRKD'

AND EVT_TYPE = 'JOB'

AND EVT_STATUS='C'

AND (EVT_REQM IS NULL

OR EVT_FAILURE IS NULL

OR EVT_CAUSE IS NULL

OR EVT_ACTION IS NULL) Thsi one triggers on any update of R5EVENTS table (WO insert/updates, new Positions etc.) What am I doing wrong? Can somebody pls advise me...

1
jharrison
May 28, 2020

Try putting in a error condition


SELECT count(*) into some_field

FROM R5EVENTS

WHERE EVT_SQLIDENTITY =:ROWID

AND EVT_JOBTYPE = 'BRKD'

AND EVT_TYPE = 'JOB'

AND EVT_STATUS='C'

AND (EVT_REQM IS NULL

OR EVT_FAILURE IS NULL

OR EVT_CAUSE IS NULL

OR EVT_ACTION IS NULL)


if some_field = 0

raise_application_error(-20003, 'Closing Code is required for closing of this work order.');

end if;

normunds.sarsuns
Jun 5, 2020

Thanks for advise!

I've found an answer after many tryouts

Key learning points (for me):

> multiple scenarios (3 in my case) require multiple "EVT_SQLIDENTITY =:ROWID" application in flex rule

> Also SQL statement should be TRUE for Failure message not to pop-up (


SQL statement which works:


SELECT NULL

FROM R5EVENTS

WHERE EVT_SQLIDENTITY =:ROWID

AND EVT_JOBTYPE LIKE 'BRKD'

AND EVT_REQM IS NOT NULL

AND EVT_FAILURE IS NOT NULL

AND EVT_CAUSE LIKE '_%'

AND EVT_ACTION LIKE '_%'


OR EVT_SQLIDENTITY =:ROWID

AND NOT EVT_TYPE='JOB'


OR EVT_SQLIDENTITY =:ROWID

AND NOT EVT_JOBTYPE = 'BRKD'

AND EVT_TYPE='JOB'