HR: Bulk Deletion/ending of element entries

HR: Bulk Deletion/ending of element entries
Declare
-- select all active opening balances
Cursor op_end_cur Is
Select pee.element_entry_id
From pay_element_types_f petf
    ,pay_element_entries pee
    ,pay_element_links_f pelf
    ,per_all_assignments_f paaf
Where petf.element_type_id = pee.element_type_id
And pelf.element_type_id = petf.element_type_id
And pelf.element_type_id = pee.element_type_id
And pelf.element_link_id = pee.element_link_id
And petf.element_name = <>
And paaf.assignment_id = pee.assignment_id
And <> between paaf.effective_start_date and paaf.effective_end_date;
l_v number := null;
l_s_date date;
l_e_date date;
l_w boolean;
Begin
pay_db_pay_setup.set_session_date(<>);
-- ending opening balance cursor
For op_end In op_end_cur
Loop
 hr_entry_api.delete_element_entry(p_dt_delete_mode => 'DELETE',
                                   p_session_date => <>,
                                   p_element_entry_id => op_end.element_entry_id);
commit;
End Loop;
End;

Single responsibility assign to bulk of users

How to assign a responsiblity to bulk users

Declare
  v_responsibility_id fnd_responsibility.responsibility_id%type;
  v_application_id    fnd_responsibility.application_id%type;
  v_resp_key  fnd_responsibility.responsibility_key%type;
  Cursor c_fnd_Cur Is
         Select a.user_id
         From fnd_user a
             ,per_all_people_f b
             ,per_all_assignments_f c
         Where a.employee_id = b.person_id
         And sysdate between b.effective_start_date and b.effective_end_date
         And sysdate between c.effective_start_date and c.effective_end_date;
  Type v_fnd_rec Is Table Of c_fnd_Cur%Rowtype;
  v_fnd_table v_fnd_rec;
  v_found               boolean := false;
Begin
  Open c_fnd_Cur;
  Fetch c_fnd_Cur Bulk Collect Into v_fnd_table;
  Close c_fnd_Cur;
  For idx In v_fnd_table.first..v_fnd_table.last
  Loop
     -- get responsiblity details
     v_resp_key := 'XXHR_EMPLOYEE_SELF_SERVICE';
     fnd_oid_subscriptions.get_resp_app_id(p_resp_key => v_resp_key
                                         ,x_responsibility_id=> v_responsibility_id
                                         ,x_application_id=> v_application_id      );
     -- verify assignments existing with user or not
     v_found := fnd_user_resp_groups_api.assignment_exists( user_id => v_fnd_table(idx).user_id
                                                          , responsibility_id   => v_responsibility_id
                                                          , responsibility_application_id => v_application_id
                                                          , security_group_id             => null);
     if (not v_found)  then
            fnd_user_resp_groups_api.insert_assignment( user_id  => v_fnd_table(idx).user_id
                                                      , responsibility_id => v_responsibility_id
                                                      , responsibility_application_id => v_application_id
                                                      , security_group_id  => null
                                                      , start_date  => sysdate
                                                      , end_date    => null
                                                      , description   => 'Employee Self Service'    );
     end if;
  End Loop;
  commit;
End;

ORA-00600: internal error code, arguments: [kcratr1_lastbwr], [], [], [], [

Error:
SQL> startup


ORACLE instance started.

Total System Global Area 167772160 bytes
Fixed Size 1247876 bytes
Variable Size 71304572 bytes
Database Buffers 88080384 bytes
Redo Buffers 7139328 bytes
Database mounted.
ORA-00600: internal error code, arguments: [kcratr1_lastbwr], [], [], [], [],

Solution:
startup mount;

recover database;
alter system open;

Upgrade Oracle Database 10g (10.2.0.4) to 11g(11.1.0.6.0) Release 1 - Redhat Enterprise Linux 5

Pre-Installation Steps:
     Stop all oracle services running on the server.
    Create a new directory for storing oracle database 11g sfitware
    eg: /u01/app/oracle/product/11.1.0

   (Now I have 2 database product directory 1 is 10g another is 11g)
   $ pwd

   /u01/app/oracle/product
   $ls

   10.2.0  11.1.0
  
    11g OS Package requirements
        make-3.81

        binutils-2.17.50.0.6
        gcc-4.1.1
        libaio-0.3.106
        libaio-devel-0.3.106
        libstdc++-4.1.1
        elfutils-libelf-devel-0.125
        sysstat-7.0.0
        compat-libstdc++-33-3.2.3
        libgcc-4.1.1
        libstdc++-devel-4.1.1
        unixODBC-2.2.11
        unixODBC-devel-2.2.11
   Verifying Kernel Parameters
        kernel.sem = 250 32000 100 128
        kernel.shmmax=536870912
        kernel.shmmni=4096
        kernel.shmall=2097152
        fs.file-max=65536
        net.ipv4.ip_local_port_range=1024 - 65000
        net.core.rmem_default=4194304
        net.core.rmem_max=4194304
        net.core.wmem_default=262144
        net.core.wmem_max=262144

        (above mentioned parameters you can found at /etc/sysctl.conf)
        a restart is required if any changes happend to kernel parameters (you can use sysctl -p , if you are not
        interested to restart now, but restart is advisable)

       set the ORACLE_HOME to new path
       ORACLE_HOME=/u01/app/oracle/product/11.1.0;export ORACLE_HOME
       change the PATH and LD_LIBRARY_PATH , becuase existing environemnt directly point to 10g
       oracle home

Installation
      Extract the 11g Database release 1 zip file to temporary location and run the installer using the following command
      $./runInstaller

Select the product you want to install (default oracle 11g)

Select the installation method advanced Installation

Select the Enterprise Option

Install location

Prerequisite Checks

Update existing database to "No"

Install Software only

OS privileged groups

Installtion Summary

Progress Screen

Finish the instllation

     
Upgrade:

set the enviroment variables to point new Oracle 11g Home
(ORACLE_HOME, ORACLE_SID,LD_LIBRARY_PATH,PATH and CLASS_PATH)

start the 11g Home database listner
(Please take the backup before starting the upgrade process)

to start the upgrade process enter the follwing command
$dbua

Introduction Screen

Review or select the database for upgrade

Dump Location (like udump, cdump and bdump locations)

Keep the default selection

Flsh recovery locations

Uncheck the invlid object compilation during upgrade (you can do this after finish the installation)

If you didn't taken backup, then this is the last chance to take the bakup

Upgrade progress

You can safely ignore this message (This issue is tracked with Oracle bug 6322672.)

You can safely ignore this message (These errors do not result in any data loss. Therefore, you can ignore these errors. This issue is tracked with Oracle bug 6705429.)

Finish the upgrade now.

Now we will compile all invalid objects in the database using the follwing command:

$sqlplus / as sysdba

SQL>@$ORACLE_HOME/rdbms/admin/utlrp

After the upgrade process you should set the COMPATITBLE Initialisation parameter to 11.0.0

If you are using SPFILE then use the follwing command
$sqlplus / as sysdba
SQL>ALTER SYSTEM SET COMPATIBLE = '11.0.0' SCOPE=SPFILE;

shutdown and restart the database

if you are using pfile instead of spfile then
shutdown the database  and edit the initialisaion parameter file (initXXX.ora)
COMPATIBLE = 11.0.0
start the database

Personalize Oracle Apps 11i (E-business Suite)

Messages:
Application Developer -> Application -> Messages

FND_SSO_COPYRIGHT_TEXT - Copyright message (Default  Copyright (c) 2006, Oracle. All rights reserved.)
FND_SSO_EBIZ_SUITE - Application Name (Default E-Business Suite)
FND_EBIZ_SUITE - Company and Application Name (Default Oracle E-Business Suite)
FND_SSO_SARBANES_OXLEY_TEXT - Corporate Policy Message

Functions:
Application Developer -> Application -> Functions
If you want to change the default branding "E-business Suite" in other pages

Change the user function name of "FWK_HOMEPAGE_BRAND" (OA Framework E-Business Homepage Branding) to your own application name

Profile:
System Administrator -> Profile -> System
The oracle logo is displayed lot of places in oracle EBS .  If you want to remove this logo globally simply chnage the profile value of "Corporate Branding Image for Oracle Applications" (FND_CORPORATE_BRANDING_IMAGE) to your company logo( copy the gif file to $OA_MEDIA and set the profile value to /OA_MEDIA/xxx.gif)

How to enable Personalization option for  OAFramework Pages
System Administrator -> Profile -> System

Following profile values set to yes at user level
1. Personalize Self-Service Defn (FND_CUSTOM_OA_DEFINTION) - Yes
2. FND: Personalization Region Link Enabled (FND_PERSONALIZATION_REGION_LINK_ENABLED) - Yes

Change Oracle Standard Logo on Oracle APPS Forms Application

How to change the oracle logo on right hand side (Oracle 11i )

Below files can be overwrittten by oracle patch or autoconfig , so you need to back the files before doing any patches on application tier side ,

(<>_<>.cfg means autoconfig enabled on apps server)

 1. Go to $OA_HTML/bin/appsweb.cfg or appsweb_<>_<>.cfg
2. Check the values of baseHTML and baseHTMLJInitiator (%OA_HTML%/US/appsbase.htm)
3. open $OA_HTML/US/appsbase.htm
      search  Forms Applet Parameters
      and add  IEhtml += '<' + 'PARAM name=logo value="' + xlogo + '">';


      you can add above line just below  the else and above the serverPort parameter


      else {
    // Forms Applet Parameters
    IEhtml += '<' + 'PARAM name=logo value="' + xlogo + '">';
    IEhtml += '<' + 'PARAM name=serverPort    value="' + xsport    + '">';

   Search Oracle Applications default Parameters 
   add  var xlogo = "%logo%"

   
    You can add this under // Additional Forms Parameter Names and Values, I have added this under xvp4

    var xvp4          = "%vparam4%"
    var xlogo = "%logo%"

4. Add new applet parameter called "logo" under   $OA_HTML/bin/appsweb.cfg or appsweb_<>_<>.cfg
   Search for UI parameters: DO NOT MODIFY unless so instructed by Oracle

   add logo=oracle/apps/media/xxx.gif 

5. Save the files
6. Copy the logo gif file to $OA_MEDIA and $OA_JAVA

Logout and close the browser and open a new browser section and test the forms. (Normally service restart not required ).