Skip to main content

Dear Code experts, 

Currently we have granted all sites access to our spare part users in order to see the available stock in each site. 

 

I have a requirement from business that this authorization must be controlled as below; 

we have to divide all stock Clerk into 2 groups;

Group 1 : they can only maintain their default site’s inventory 

Group 2 : They can maintain their default site’s inventory + other sites inventory   

 

In order to achieve this,  I created a custom event on Inventory_part_in_stock_tab,

below is the code and it works for group 1. but for group 2 event doesn’t allowed to do transaction.

I am not a developer so Am I missing something in below code ?   

 

DECLARE

DEF_ VARCHAR2(10);
MULTI_ VARCHAR2(100);


CURSOR C_ALLOW_multi_  IS SELECT CONTRACT  from USER_ALLOWED_SITE_CFV where userid = '&FND_USER' AND CF$_ALLOW_MULTI_SITE_DB = 'Y';

BEGIN

OPEN  C_ALLOW_multi_;
FETCH  C_ALLOW_multi_ INTO MULTI_;
CLOSE  C_ALLOW_multi_;

IF ( '&NEW:CONTRACT' <> '&DEF_USER' ) OR ( '&NEW:CONTRACT' in 'MULTI_')   THEN
ERROR_SYS.RECORD_GENERAL('INVPART','PART: You are not allowed to do transactions for this business site');
END IF;
END;

 

I have added a custom field to Site per user window in order to get group 2 users available sites 

 

I am not a developer either, but looking at your IF statement...

IF ( '&NEW:CONTRACT' <> '&DEF_USER' ) OR ( '&NEW:CONTRACT' in 'MULTI_') 

I can’t see that ‘&DEF_USER is defined within the code (&DEF is), and I also think that you need NOT IN ‘MULTI_’ to make the logic work.

This doesn’t explain why your code works for group 1 users and not group 2 though!

hope this helps and I’ve subscribed as this is a question we get asked for frequently...

 

 

I am not a developer either, but looking at your IF statement...

IF ( '&NEW:CONTRACT' <> '&DEF_USER' ) OR ( '&NEW:CONTRACT' in 'MULTI_') 

I can’t see that ‘&DEF_USER is defined within the code (&DEF is), and I also think that you need NOT IN ‘MULTI_’ to make the logic work.

This doesn’t explain why your code works for group 1 users and not group 2 though!

hope this helps and I’ve subscribed as this is a question we get asked for frequently...

 

 

Hi,

Actually I have defined “DEF_USER” as a custom attribute in the event. My variables naming are pretty mess coz so far I tried different ways  

 

NOT IN also not working 

 

 

Give this a go:

DECLARE

check_site_ NUMBER;

BEGIN

SELECT count(*) into check_site_ from USER_ALLOWED_SITE_CFV a where a.userid = '&FND_USER' AND a.CF$_ALLOW_MULTI_SITE_DB = 'Y' and a.contract = '&NEW:CONTRACT';

IF ('&NEW:CONTRACT' <> '&DEF_USER' ) and (check_site_ <> 1)   THEN
ERROR_SYS.RECORD_GENERAL('INVPART','PART: You are not allowed to do transactions for this business site');
END IF;
END;

 

Give this a go:

DECLARE

check_site_ NUMBER;

BEGIN

SELECT count(*) into check_site_ from USER_ALLOWED_SITE_CFV a where a.userid = '&FND_USER' AND a.CF$_ALLOW_MULTI_SITE_DB = 'Y' and a.contract = '&NEW:CONTRACT';

IF ('&NEW:CONTRACT' <> '&DEF_USER' ) and (check_site_ <> 1)   THEN
ERROR_SYS.RECORD_GENERAL('INVPART','PART: You are not allowed to do transactions for this business site');
END IF;
END;

 

 

Thanks Callum, It works like a charm.. 👍👍👍👌👌

Terms & ConditionsCookie settings