Skip to main content

Hi experts,

Please see screenshots and code below.

I would like to be able to restrict the Categories of Sales Contracts that can be awarded by users that have allocated a permission (NP_CM_L1_AWARD) set that allows them to Award Sales Contracts.

For instance I do not want them to be able to Award Contracts category L2 or L3.

I am having the “mutating” issue.

Could you please advise on any ideas on how to overcome this issue?

Many thanks

Miguel

 

 

 

 

DECLARE
invalid_code                EXCEPTION;
invalid_str                   VARCHAR2(100);
CATEGORY1_NAME    VARCHAR2(100);
category_                   VARCHAR2(100);

BEGIN

select CATEGORY1_NAME into category_
from
IFSAPP.SALES_CONTRACT;

            IF category_  in ('NP_STANDARD_L2','NP_STANDARD_L3')
            THEN
            invalid_str := 'NP Error - You cannot Award a Sales Contract Category L2 or L3!';
            RAISE INVALID_CODE;
            END IF;

EXCEPTION
WHEN INVALID_CODE THEN
Error_SYS.system_general('System Error: '||INVALID_STR);
END;

Hi @Miguel,

Rather than writting a select query inside the PL/SQL block to fetch category1_name, can’t u fetch it from the below window?

Example:

Then it’s just the matter of referring to the bind variable in the respective action.

Hope this helps!

Best Regards,
Bhagya

Hi @Miguel,

 

By using pragma autonomous_transaction in the declare section of your code you can query the table that also contains the trigger. So you will be able to fetch the category1_name eventhough the table is mutating. 

You can find more about this here: on https://docs.oracle.com/cd/B19306_01/appdev.102/b14261/autonotransaction_pragma.htm

I hope this helps.

 

Kind regards,

Michael

Hi @Bhagya Wickramasinghe ,

Your pointer was very useful. I modified the angle of the solution and the design shown below is now working for me. The permission set NP_CM_L1_AWARD allows for RMB Award Sales Contract, however the customer event below limit this authority to only to the lowest Category (L1).

Many thanks 

Miguel

 

 

Reply


Terms & ConditionsCookie settings