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...
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;
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'