Date: Wed, 28 Jul 1999 15:09:39 PDT
From: "mike ruff" mikeruff@hotmail.com
To: oraapps-l@cpa.qc.ca
Subject: Database Trigger Code.........Help.....???
Message-ID: 19990728220940.35503.qmail@hotmail.com

Hi guys,


SAMPLE CODE................

CREATE OR REPLACE TRIGGER AUTO_SECURITY_RULE_ASSIGN
AFTER INSERT ON APPS.FND_RESPONSIBILITY
FOR EACH ROW

SELECT APPLICATION_ID FROM FND_RESPONSIBILITY;

IF APPLICATION_ID = 101
THEN

DECLARE

v_security_rule_id  NUMBER;
v_value_set_id NUMBER;
v_responsibility_name VARCHAR2;
v_security_rule_name = VARCHAR2;

BEGIN

SELECT FND_FLEX_VALUE_RULES_S.NEXTVAL
INTO
v_security_rule_id

SELECT flex_value_set_id from FND_FLEX_VALUE_SETS
INTO
v_value_set_id
where flex_value_set_name = 'OPERATING_UNIT'

SELECT responsibility_id from FND_RESPONSIBILITY
---- will it select the responsibility id just created...???
INTO
v_responsibility_id

select responsibility_name from fnd_responsibility
into
v_responsibility_name

v_security_rule_name := v_responsibility_name||_SROU||

INSERT INTO
APPS.FND_FLEX_VALUE_RULES

(flex_value_rule_id,
flex_value_rule_name,
flex_value_set_id,
last_update_date,
last_updated_by,
creation_date,
created_by)

VALUES

(v_security_rule_id,
v_security_rule_name,
v_value_set_id,
SYSDATE,
-1,
SYSDATE,
-1)

INSERT INTO
APPS.FND_FLEX_VALUE_RULE_USAGES

(application_id,
responsibility_id,
flex_value_rule_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
flex_value_set_id)

VALUES

(101,
v_responsibility_id, --  Id of the new responsibility created
v_security_rule_id,
SYSDATE,
-1,
SYSDATE,
-1,
v_value_set_id)

END AUTO_SECURITY_RULE_ASSIGN;

Any kind of help is welcome.............!!

Thanks,

MIKE RUFF