Friday 28 September 2012

Creating Form Functions in Oracle APPS using API

DECLARE
l_new_menu_id     NUMBER; 
l_last_menu_id    NUMBER; 
l_row_id          VARCHAR2(100); 
l_descr           VARCHAR2(400); 
l_flag_exist_menu NUMBER := 0;
x_function_id     NUMBER; 
x_row_id          ROWID;
x_form_id         NUMBER;
lc_status         varchar2(1);
CURSOR C1
IS
SELECT * FROM
XX_FND_FUNCTIONS
where FUNCTION_NAME NOT in
(select FUNCTION_NAME from FND_FORM_FUNCTIONS);

 BEGIN

   FOR CREC IN C1
    LOOP   
        lc_status := 'Y';
       
        BEGIN
        select fnd_form_functions_s.nextval
          into x_function_id
          from sys.dual;
            Exception
                When others then
                dbms_output.put_line('failed to fnd_form_functions_s');
                lc_status := 'E';
            
          END;
      
       IF CREC.form_id IS NOT NULL THEN
   
        BEGIN
        SELECT  form_id
          into  x_form_id
          FROM  fnd_form
          where form_id=CREC.form_id;
           Exception
                When others then
                dbms_output.put_line('failed to get x_form_id');
                lc_status := 'E';          
          END;
       
       END IF;
       
         if lc_status = 'Y'then
        
         FND_FORM_FUNCTIONS_PKG.INSERT_ROW (
                    X_ROWID => x_row_id,
                    X_FUNCTION_ID => X_FUNCTION_ID,
                    X_WEB_HOST_NAME => null,
                    X_WEB_AGENT_NAME => null,
                    X_WEB_HTML_CALL => CREC.WEB_HTML_CALL,
                    X_WEB_ENCRYPT_PARAMETERS =>'N',
                    X_WEB_SECURED =>'N',
                    X_WEB_ICON =>null,
                    X_OBJECT_ID =>null,
                    X_REGION_APPLICATION_ID =>null,
                    X_REGION_CODE =>null,
                    X_FUNCTION_NAME =>CREC.FUNCTION_NAME, -- find unique function name using query
                    X_APPLICATION_ID =>CREC.APPLICATION_ID, -- find application id using the query for appliaion names and pass 'FND'
                    X_FORM_ID => x_form_id, -- find from id using formname 'XXCO'
                    X_PARAMETERS => CREC.PARAMETERS,
                    X_TYPE => CREC.TYPE,
                    X_USER_FUNCTION_NAME =>CREC.USER_FUNCTION_NAME,
                    X_DESCRIPTION =>CREC.DESCRIPTION,
                    X_CREATION_DATE =>sysdate,
                    X_CREATED_BY => -1,
                    X_LAST_UPDATE_DATE =>sysdate,
                    X_LAST_UPDATED_BY =>-1,
                    X_LAST_UPDATE_LOGIN =>0
                    );
       
         dbms_output.put_line('FUNCTION_NAME '||CREC.FUNCTION_NAME);        
        END IF;   
   END LOOP;
  
   commit;       
       
 end;

No comments:

Post a Comment