Infolinks

Friday 6 July 2012

GL CROSS VALIDATION RULE

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;  

No comments:

Post a Comment