Tuesday, April 21, 2015

Change Title on iProcurement Page

Dear Reader

In this post, we will see how we can change the title of the page in iProcurement responsibility

Usually, the seeded iProc responsibility will show just the title on the page as iProcurement in the branding bar at the top of the page (in blue background section)

If we wish to change this to maybe show your company name or the OU name, we can use below script which will create multiple branding functions for all your custom iProc responsibilities

DECLARE
   p_resp_name VARCHAR2(240) := '&responsibility_name';
   lv_function_id NUMBER;
   lv_rowid VARCHAR2(240);
   lv_home_menu_id NUMBER;
   lv_top_menu_id NUMBER;
   lv_max_seq NUMBER;
   CURSOR lcur_resp(p_resp VARCHAR2)
   IS
      SELECT   fr.responsibility_name,
               fr.menu_id,
               fr.responsibility_id,
               fr.application_id,
               (
                  SELECT   hou.name
                  FROM     fnd_profile_option_values fpv,
                           fnd_profile_options fpo,
                           hr_operating_units hou
                  WHERE    fpo.profile_option_name = 'ORG_ID'
                  AND      fpo.profile_option_id = fpv.profile_option_id
                  AND      fpv.level_id = 10003
                  AND      fpv.level_value = fr.responsibility_id
                  AND      fpv.profile_option_value = hou.organization_id
               ) ou_name,
               Upper(REPLACE(fr.responsibility_name, 'MyCompany_iProcurement_User_', '')) region_name
      FROM     fnd_responsibility_vl fr
      WHERE    ((p_resp IS NULL AND fr.responsibility_name LIKE 'MyCompany_iProcurement_User%') OR (p_resp IS NOT NULL AND fr.responsibility_name = p_resp));

BEGIN
   SELECT   menu_id
   INTO     lv_home_menu_id
   FROM     fnd_menus_vl
   WHERE    user_menu_name = 'iProcurement: Homepage menu';

   SELECT   menu_id
   INTO     lv_top_menu_id
   FROM     fnd_menus_vl
   WHERE    user_menu_name = 'Internet Procurement Home';

   --Here first we will make copied of branding function and attach it to home menu
   FOR lrec_resp IN lcur_resp(p_resp_name)
   LOOP
      FOR i IN
      (
         SELECT   *
         FROM     fnd_form_functions_vl
         WHERE    function_name = 'ICX_POR_IP_BRAND'
      ) LOOP
         SELECT   fnd_form_functions_s.NEXTVAL
         INTO     lv_function_id
         FROM     DUAL;

         lv_rowid := NULL;

         fnd_form_functions_pkg.INSERT_ROW
         (
            X_ROWID                    => lv_rowid,
            X_FUNCTION_ID              => lv_function_id,
            X_WEB_HOST_NAME            => i.WEB_HOST_NAME         ,
            X_WEB_AGENT_NAME           => i.WEB_AGENT_NAME        ,
            X_WEB_HTML_CALL            => i.WEB_HTML_CALL         ,
            X_WEB_ENCRYPT_PARAMETERS   => i.WEB_ENCRYPT_PARAMETERS,
            X_WEB_SECURED              => i.WEB_SECURED           ,
            X_WEB_ICON                 => i.WEB_ICON              ,
            X_OBJECT_ID                => i.OBJECT_ID             ,
            X_REGION_APPLICATION_ID    => i.REGION_APPLICATION_ID ,
            X_REGION_CODE              => i.REGION_CODE           ,
            X_FUNCTION_NAME            => i.FUNCTION_NAME || '_' || lrec_resp.region_name,
            X_APPLICATION_ID           => i.APPLICATION_ID        ,
            X_FORM_ID                  => i.FORM_ID               ,
            X_PARAMETERS               => i.PARAMETERS            ,
            X_TYPE                     => i.TYPE                  ,
            X_USER_FUNCTION_NAME       => i.USER_FUNCTION_NAME || ' - ' || lrec_resp.ou_name,
            X_DESCRIPTION              => i.DESCRIPTION || ' - ' || lrec_resp.ou_name,
            X_CREATION_DATE            => SYSDATE,
            X_CREATED_BY               => 0,
            X_LAST_UPDATE_DATE         => SYSDATE,
            X_LAST_UPDATED_BY          => 0,
            X_LAST_UPDATE_LOGIN        => 0
         );

         lv_rowid := NULL;

         SELECT   Max(entry_sequence) + 5
         INTO     lv_max_seq
         FROM     fnd_menu_entries_vl
         WHERE    menu_id = lv_home_menu_id;

         fnd_menu_entries_pkg.INSERT_ROW
         (
            X_ROWID           => lv_rowid,
            X_MENU_ID         => lv_home_menu_id,
            X_ENTRY_SEQUENCE  => lv_max_seq,
            X_SUB_MENU_ID     => NULL,
            X_FUNCTION_ID     => lv_function_id,
            X_GRANT_FLAG      => 'Y',
            X_PROMPT          => NULL,
            X_DESCRIPTION     => NULL,
            X_CREATION_DATE   => SYSDATE,
            X_CREATED_BY      => 0,
            X_LAST_UPDATE_DATE => SYSDATE ,
            X_LAST_UPDATED_BY => 0,
            X_LAST_UPDATE_LOGIN => 0
         );
      END LOOP;

      FOR i IN
      (
         SELECT   *
         FROM     fnd_form_functions_vl
         WHERE    function_name = 'POR_SSP_HOME'
      ) LOOP
         SELECT   fnd_form_functions_s.NEXTVAL
         INTO     lv_function_id
         FROM     DUAL;

         lv_rowid := NULL;

         fnd_form_functions_pkg.INSERT_ROW
         (
            X_ROWID                    => lv_rowid,
            X_FUNCTION_ID              => lv_function_id,
            X_WEB_HOST_NAME            => i.WEB_HOST_NAME         ,
            X_WEB_AGENT_NAME           => i.WEB_AGENT_NAME        ,
            X_WEB_HTML_CALL            => REPLACE(i.WEB_HTML_CALL, 'ICX_POR_IP_BRAND', 'ICX_POR_IP_BRAND' || '_' || lrec_resp.region_name),
            X_WEB_ENCRYPT_PARAMETERS   => i.WEB_ENCRYPT_PARAMETERS,
            X_WEB_SECURED              => i.WEB_SECURED           ,
            X_WEB_ICON                 => i.WEB_ICON              ,
            X_OBJECT_ID                => i.OBJECT_ID             ,
            X_REGION_APPLICATION_ID    => i.REGION_APPLICATION_ID ,
            X_REGION_CODE              => i.REGION_CODE           ,
            X_FUNCTION_NAME            => i.FUNCTION_NAME || '_' || lrec_resp.region_name,
            X_APPLICATION_ID           => i.APPLICATION_ID        ,
            X_FORM_ID                  => i.FORM_ID               ,
            X_PARAMETERS               => i.PARAMETERS            ,
            X_TYPE                     => i.TYPE                  ,
            X_USER_FUNCTION_NAME       => i.USER_FUNCTION_NAME || ' - ' || lrec_resp.region_name,
            X_DESCRIPTION              => i.DESCRIPTION || ' - ' || lrec_resp.region_name,
            X_CREATION_DATE            => SYSDATE,
            X_CREATED_BY               => 0,
            X_LAST_UPDATE_DATE         => SYSDATE,
            X_LAST_UPDATED_BY          => 0,
            X_LAST_UPDATE_LOGIN        => 0
         );

         lv_rowid := NULL;

         SELECT   Max(entry_sequence) + 5
         INTO     lv_max_seq
         FROM     fnd_menu_entries_vl
         WHERE    menu_id = lv_top_menu_id;

         fnd_menu_entries_pkg.INSERT_ROW
         (
            X_ROWID           => lv_rowid,
            X_MENU_ID         => lv_top_menu_id,
            X_ENTRY_SEQUENCE  => lv_max_seq,
            X_SUB_MENU_ID     => NULL,
            X_FUNCTION_ID     => lv_function_id,
            X_GRANT_FLAG      => 'Y',
            X_PROMPT          => 'iProcurement Home Page',
            X_DESCRIPTION     => 'Internet Procurement Home Page - ' || lrec_resp.ou_name,
            X_CREATION_DATE   => SYSDATE,
            X_CREATED_BY      => 0,
            X_LAST_UPDATE_DATE => SYSDATE ,
            X_LAST_UPDATED_BY => 0,
            X_LAST_UPDATE_LOGIN => 0
         );
      END LOOP;
   END LOOP;

   FOR lrec_resp IN lcur_resp(p_resp_name) LOOP
      FOR i IN
      (
         SELECT   Upper(REPLACE(fr.responsibility_name, 'MyCompany_iProcurement_User_', '')) region_name
         FROM     fnd_responsibility_vl fr
         WHERE    fr.responsibility_name LIKE 'MyCompany_iProcurement_User%'
         AND      fr.responsibility_id != lrec_resp.responsibility_id
      ) LOOP
         SELECT   function_id
         INTO     lv_function_id
         FROM     fnd_form_functions_vl
         WHERE    function_name = 'POR_SSP_HOME_' || i.region_name;

         lv_rowid := NULL;
         fnd_resp_functions_pkg.INSERT_ROW
         (
            X_ROWID                  => lv_rowid,
            X_APPLICATION_ID        => lrec_resp.application_id,
            X_RESPONSIBILITY_ID    => lrec_resp.responsibility_id,
            X_ACTION_ID            => lv_function_id,
            X_RULE_TYPE            => 'F',
            X_CREATED_BY          => 0,
            X_CREATION_DATE       => SYSDATE,
            X_LAST_UPDATED_BY     => 0,
            X_LAST_UPDATE_DATE    => SYSDATE,
            X_LAST_UPDATE_LOGIN   => 0
         );
      END LOOP;

      SELECT   function_id
      INTO     lv_function_id
      FROM     fnd_form_functions_vl
      WHERE    function_name = 'POR_SSP_HOME';

      lv_rowid := NULL;
      fnd_resp_functions_pkg.INSERT_ROW
      (
         X_ROWID                  => lv_rowid,
         X_APPLICATION_ID        => lrec_resp.application_id,
         X_RESPONSIBILITY_ID    => lrec_resp.responsibility_id,
         X_ACTION_ID            => lv_function_id,
         X_RULE_TYPE            => 'F',
         X_CREATED_BY          => 0,
         X_CREATION_DATE       => SYSDATE,
         X_LAST_UPDATED_BY     => 0,
         X_LAST_UPDATE_DATE    => SYSDATE,
         X_LAST_UPDATE_LOGIN   => 0
      );

   END LOOP;
END;


As can be seen from the script above, the branding comes from the ICX_POR_IP_BRAND function which we create multiple copies of (one for each custom iProc responsibility) and the attach to the main menu.

We also create multiple copies of the POR_SSP_HOME function and modify the branding function name to the custom one we create. Finally we exclude the unwanted functions from each responsibility

Simple isn't it :-)

Cheers
A