GL CROSS VALIDATION RULE
DECLARE
--apps intilization
G_USER_NAME VARCHAR2(100) := 'SYSADMIN';
G_RESP_KEY VARCHAR2(100) := 'APPLICATION_DEVELOPER';
l_user_id NUMBER;
l_resp_id NUMBER;
l_resp_appl_id NUMBER;
--Initiliazing values for fnd_flex_validation_rules
APPLICATION_ID NUMBER(10) :=101;
ID_FLEX_CODE VARCHAR2(4) :='GL#';
ID_FLEX_NUM NUMBER(15) :=50214;
X_FLEX_VALIDATION_RULE_NAME VARCHAR2(15) :='Example2';
LAST_UPDATE_DATE DATE :=sysdate;
LAST_UPDATED_BY NUMBER(15) :=null;
CREATION_DATE DATE :=sysdate;
CREATED_BY NUMBER(15) :=null;
LAST_UPDATE_LOGIN NUMBER(10) :=null;
ENABLED_FLAG VARCHAR2(1) :='Y';
ERROR_SEGMENT_COLUMN_NAME VARCHAR2(30) :=null;
START_DATE_ACTIVE DATE :=sysdate;
END_DATE_ACTIVE DATE :=null;
SECURITY_GROUP_ID NUMBER :=null;
--Initilizing values for fnd_flex_vdation_rules_tl
LANGUAGE VARCHAR2(30) :='US';
ERROR_MESSAGE_TEXT VARCHAR2(240) :='You are trying to insert sample rule';
SOURCE_LANG VARCHAR2(4) :='US';
DESCRIPTION VARCHAR2(240) :=null;
CONCATENATED_SEGMENTS_LOW VARCHAR2(2000) :='01.100.1100.1050.113' ;
CONCATENATED_SEGMENTS_HIGH VARCHAR2(2000) :='01.100.1100.1050.113' ;
INCLUDE_EXCLUDE_INDICATOR VARCHAR2(1) :='I' ;
-- Initilizing values for fnd_flex_validation_rule_lines
RULE_LINE_ID NUMBER(15) :=10906;
X_NAME VARCHAR2(150);
X_COUNT NUMBER :=0;
BEGIN
BEGIN
select FLEX_VALIDATION_RULE_NAME into x_name
from fnd_flex_validation_rules
where FLEX_VALIDATION_RULE_NAME=X_FLEX_VALIDATION_RULE_NAME;
EXCEPTION WHEN NO_DATA_FOUND THEN
X_COUNT:=X_COUNT+1;
WHEN TOO_MANY_ROWS THEN
NUll;
END;
BEGIN
SELECT user_id
INTO l_user_id
FROM fnd_user
WHERE user_name=G_USER_NAME;
EXCEPTION WHEN OTHERS THEN
null;
END;
BEGIN
SELECT responsibility_id
,application_id
INTO l_resp_id
,l_resp_appl_id
FROM fnd_responsibility_vl
WHERE responsibility_key=G_RESP_KEY;
EXCEPTION WHEN OTHERS THEN
null;
END;
FND_GLOBAL.APPS_INITIALIZE(user_id=>l_user_id,
resp_id=>l_resp_id,
resp_appl_id=>l_resp_appl_id);
LAST_UPDATED_BY :=l_user_id;
CREATED_BY :=l_user_id;
LAST_UPDATE_LOGIN:=5590465;
IF (x_count !=1) THEN
dbms_output.put_line('Trying to insert duplicate value');
ELSE
--inserting values into fnd_flex_validation_rules
insert into fnd_flex_validation_rules values(
APPLICATION_ID
,ID_FLEX_CODE
,ID_FLEX_NUM
,X_FLEX_VALIDATION_RULE_NAME
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_LOGIN
,ENABLED_FLAG
,ERROR_SEGMENT_COLUMN_NAME
,START_DATE_ACTIVE
,END_DATE_ACTIVE
,SECURITY_GROUP_ID );
--inserting values into fnd_flex_vdation_rules_tl
INSERT INTO fnd_flex_vdation_rules_tl VALUES(
APPLICATION_ID
,ID_FLEX_CODE
,ID_FLEX_NUM
,X_FLEX_VALIDATION_RULE_NAME
,LANGUAGE
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_LOGIN
,ERROR_MESSAGE_TEXT
,SOURCE_LANG
,DESCRIPTION
,SECURITY_GROUP_ID );
--inserting values into fnd_flex_validation_rule_lines
INSERT INTO fnd_flex_validation_rule_lines VALUES(
APPLICATION_ID
,ID_FLEX_CODE
,ID_FLEX_NUM
,X_FLEX_VALIDATION_RULE_NAME
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_LOGIN
,ENABLED_FLAG
,CONCATENATED_SEGMENTS_LOW
,CONCATENATED_SEGMENTS_HIGH
,INCLUDE_EXCLUDE_INDICATOR
,DESCRIPTION
,RULE_LINE_ID
,SECURITY_GROUP_ID );
COMMIT;
DBMS_OUTPUT.PUT_LINE('Records inserted successfully');
END IF;
EXCEPTION WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('The error is:'||SQLERRM);
END;
--apps intilization
G_USER_NAME VARCHAR2(100) := 'SYSADMIN';
G_RESP_KEY VARCHAR2(100) := 'APPLICATION_DEVELOPER';
l_user_id NUMBER;
l_resp_id NUMBER;
l_resp_appl_id NUMBER;
--Initiliazing values for fnd_flex_validation_rules
APPLICATION_ID NUMBER(10) :=101;
ID_FLEX_CODE VARCHAR2(4) :='GL#';
ID_FLEX_NUM NUMBER(15) :=50214;
X_FLEX_VALIDATION_RULE_NAME VARCHAR2(15) :='Example2';
LAST_UPDATE_DATE DATE :=sysdate;
LAST_UPDATED_BY NUMBER(15) :=null;
CREATION_DATE DATE :=sysdate;
CREATED_BY NUMBER(15) :=null;
LAST_UPDATE_LOGIN NUMBER(10) :=null;
ENABLED_FLAG VARCHAR2(1) :='Y';
ERROR_SEGMENT_COLUMN_NAME VARCHAR2(30) :=null;
START_DATE_ACTIVE DATE :=sysdate;
END_DATE_ACTIVE DATE :=null;
SECURITY_GROUP_ID NUMBER :=null;
--Initilizing values for fnd_flex_vdation_rules_tl
LANGUAGE VARCHAR2(30) :='US';
ERROR_MESSAGE_TEXT VARCHAR2(240) :='You are trying to insert sample rule';
SOURCE_LANG VARCHAR2(4) :='US';
DESCRIPTION VARCHAR2(240) :=null;
CONCATENATED_SEGMENTS_LOW VARCHAR2(2000) :='01.100.1100.1050.113' ;
CONCATENATED_SEGMENTS_HIGH VARCHAR2(2000) :='01.100.1100.1050.113' ;
INCLUDE_EXCLUDE_INDICATOR VARCHAR2(1) :='I' ;
-- Initilizing values for fnd_flex_validation_rule_lines
RULE_LINE_ID NUMBER(15) :=10906;
X_NAME VARCHAR2(150);
X_COUNT NUMBER :=0;
BEGIN
BEGIN
select FLEX_VALIDATION_RULE_NAME into x_name
from fnd_flex_validation_rules
where FLEX_VALIDATION_RULE_NAME=X_FLEX_VALIDATION_RULE_NAME;
EXCEPTION WHEN NO_DATA_FOUND THEN
X_COUNT:=X_COUNT+1;
WHEN TOO_MANY_ROWS THEN
NUll;
END;
BEGIN
SELECT user_id
INTO l_user_id
FROM fnd_user
WHERE user_name=G_USER_NAME;
EXCEPTION WHEN OTHERS THEN
null;
END;
BEGIN
SELECT responsibility_id
,application_id
INTO l_resp_id
,l_resp_appl_id
FROM fnd_responsibility_vl
WHERE responsibility_key=G_RESP_KEY;
EXCEPTION WHEN OTHERS THEN
null;
END;
FND_GLOBAL.APPS_INITIALIZE(user_id=>l_user_id,
resp_id=>l_resp_id,
resp_appl_id=>l_resp_appl_id);
LAST_UPDATED_BY :=l_user_id;
CREATED_BY :=l_user_id;
LAST_UPDATE_LOGIN:=5590465;
IF (x_count !=1) THEN
dbms_output.put_line('Trying to insert duplicate value');
ELSE
--inserting values into fnd_flex_validation_rules
insert into fnd_flex_validation_rules values(
APPLICATION_ID
,ID_FLEX_CODE
,ID_FLEX_NUM
,X_FLEX_VALIDATION_RULE_NAME
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_LOGIN
,ENABLED_FLAG
,ERROR_SEGMENT_COLUMN_NAME
,START_DATE_ACTIVE
,END_DATE_ACTIVE
,SECURITY_GROUP_ID );
--inserting values into fnd_flex_vdation_rules_tl
INSERT INTO fnd_flex_vdation_rules_tl VALUES(
APPLICATION_ID
,ID_FLEX_CODE
,ID_FLEX_NUM
,X_FLEX_VALIDATION_RULE_NAME
,LANGUAGE
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_LOGIN
,ERROR_MESSAGE_TEXT
,SOURCE_LANG
,DESCRIPTION
,SECURITY_GROUP_ID );
--inserting values into fnd_flex_validation_rule_lines
INSERT INTO fnd_flex_validation_rule_lines VALUES(
APPLICATION_ID
,ID_FLEX_CODE
,ID_FLEX_NUM
,X_FLEX_VALIDATION_RULE_NAME
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_LOGIN
,ENABLED_FLAG
,CONCATENATED_SEGMENTS_LOW
,CONCATENATED_SEGMENTS_HIGH
,INCLUDE_EXCLUDE_INDICATOR
,DESCRIPTION
,RULE_LINE_ID
,SECURITY_GROUP_ID );
COMMIT;
DBMS_OUTPUT.PUT_LINE('Records inserted successfully');
END IF;
EXCEPTION WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('The error is:'||SQLERRM);
END;
No comments:
Post a Comment