Error APP-FF-33980: Internal error attempting to generate package

Error "APP-FF-33980: Internal error attempting to generate package". While saving the fast formula.

How to diagnosis:
Compile the fast formula using FFXBCP command
go to
$cd $FF_TOP/bin
$FFXBCP apps/apps 0 Y -k %% 'ENTER_THE_NAME_OF_THE_FAST_FORMUALA'

it will generate 2 files one .log and .out

Please check the log file to see the package name
Connect using
$sqlplus apps/apps
SQL> alter package packagename compile body;
SQL> show error

fix the error and compile the fast formula

Calculating Employee Service years

Below script return Number of years, Months and days of an employee

Select ppos.person_id,
trunc(months_between(coalesce(ppos.projected_termination_date,sysdate),ppos.date_start )/12) ||' Years '||
       TO_CHAR (FLOOR(MOD (months_between(coalesce(ppos.projected_termination_date,sysdate),ppos.date_start ), 12)) ) ||' Months '||
       (To_Date (coalesce(ppos.projected_termination_date,sysdate))-Add_Months (ppos.date_start,Trunc (Months_Between (coalesce(ppos.projected_termination_date,sysdate), ppos.date_start)) ) )      ||' Days ' yearOfService
      
From   per_periods_of_service ppos

HR: Create Person Extra infromation using API

Following HR (11i) api can be used for creating person extra information, below example show you how to insert a employee passport details into extra information tables using api.

Procedure create_pp_details(p_pp_country In Varchar2 -- passport issuing country
                            ,p_pp_number  In Varchar2 -- passport number
                            ,p_pp_issue_date In date -- passport issue date
                            ,p_pp_expiry_date In Date -- passport expiry date
                            ,p_pp_issue_place In Varchar2 -- passport issue place
                            ,p_person_id In Number
                            )

Is
  l_person_extra_info_id  Number;
   l_object_version_number Number;
 

Begin
 

 hr_person_extra_info_api.create_person_extra_info
    (p_validate                    => false
    ,p_person_id                   => p_person_id     -- employee person id
    ,p_information_type            => 'PASSPORT_DETAILS' -- this is same as your extra person information flex filed code.
    ,p_pei_attribute_category      => null
    ,p_pei_information_category    => 'PASSPORT_DETAILS' -- this is same as your extra person information flex filed code.
    ,p_pei_information1            =>  p_pp_country
    ,p_pei_information2            => p_pp_number
    ,p_pei_information3            => to_char(p_pp_issue_date,'RRRR/MM/DD HH:MI:SS')
    ,p_pei_information4            => to_char(p_pp_expiry_date,'RRRR/MM/DD HH:MI:SS')
    ,p_pei_information5            => p_pp_issue_place
    ,p_person_extra_info_id        => l_person_extra_info_id  -- out values
    ,p_object_version_number       => l_object_version_number -- out values

    );
 commit;
end;

adpreclone 0% completed Error

adpreclone 0% completed Error

Below error is started after upgrading to 11.2.0.3 database

perl adpreclone.pl dbTier
Running Rapid Clone with command...
perl /u01/app/oracle/PROD/db/tech_st/11.2.0/appsutil/bin/adclone.pl java=/u01/app/oracle/PROD/db/tech_st/11.2.0/jdk mode=stage stage=/u01/app/oracle/PROD/db/tech_st/11.2.0/appsutil/clone component=dbTier method=CUSTOM dbctx=/u01/app/oracle/PROD/db/tech_st/11.2.0/appsutil/PROD_bhmanapp09r12.xml showProgress
Beginning database tier Stage - Mon Apr 23 10:20:23 2012
APPS Password : apps
Log file located at /u01/app/oracle/PROD/db/tech_st/11.2.0/appsutil/log/PROD_bhmanapp09r12/StageDBTier_04231020.log
- 0% completed
ERROR while running Stage...
ERROR while running perl /u01/app/oracle/PROD/db/tech_st/11.2.0/appsutil/bin/adclone.pl java=/u01/app/oracle/PROD/db/tech_st/11.2.0/jdk mode=stage stage=/u01/app/oracle/PROD/db/tech_st/11.2.0/appsutil/clone component=dbTier method=CUSTOM dbctx=/u01/app/oracle/PROD/db/tech_st/11.2.0/appsutil/PROD_bhmanapp09r12.xml showProgress ...

There was no error meessage in log file.

Solution:

Set the following debug parameters  (Linux environment)
TIMEDPROCESS_DEBUG=YES
export TIMEDPROCESS_DEBUG

Re-run the adpreclone. now you can view the error message in log file.

Zip 3.0 is used (delivered with 11g) instead of the 2.3 which has been delivered with 10g.

modify the PATH and so that zip2.3 was in the first.

to make this chnage permenant , edit the $ORACLE_HOME/.env file PATH
Re-run perl adpreclone dbTier.

How to Create Oracle Worflow Business Events

How to Create Oracle Worflow Business Events

Purpose: Below I am going to show you how we can use a business event to execute custom plsql function.

Go to system Administrator Responsiblity => Worflow Administrator

Select Business Events Tab

Click Create Event Button



    Enter the required values.
    Click Apply
Create Event Subscription by clicking Event Subscription

       

Click on Create Subscription button

System will be the name of your enviroment instance , selection available

Action type custom

Click Next

Create a PL/SQL function  that will be execute when the event is raised

Function ts_cancel_event(p_subscription_guid In RAW

,p_event In Out NoCopy Wf_Event_t
)
Return Varchar2
Is
l_doc_id Number;
Begin
l_doc_id := p_event.getValueForParameter('XXPM_DOC_ID');
-- your own logic example like inserting data to table or some action
----
----
 RETURN 'SUCCESS';
End ts_cancel_event;

getValueForParameter is used to retrieve parameter value from event.
Attach the procedure to Event Action window

Enter all required values and Click Apply

Next step is how to raise the event:

Sample code is below

Procedure raise_approval_cancel_event(p_doc_id In Number)

Is
l_event_param_list wf_parameter_list_t;
l_param_table wf_parameter_t;
l_index Number;
Begin
l_event_param_list := wf_parameter_list_t();
-- lets add the value to event parameters
l_param_table := wf_parameter_t(Null,Null);
l_event_param_list.Extend;
l_param_table .setName('XXPM_DOC_ID');
l_param_table .setValue(p_doc_id);
l_index := coalesce(l_index ,0) + 1;
l_event_param_list (l_index ) := l_param_table ;
wf_event.RAISE(p_event_name => 'sat.oracle.apps.xxpm.ts.cancelApprovalProcess'
,p_event_key => 'XXPMSTS'||to_char(p_doc_id)
,p_parameters => l_event_param_list
 );
ENd raise_approval_cancel_event;

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;

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 ).