How to remove Custom DFF

Delete DFF Context
===============
Declare
  Cursor dff_context_cur Is
    Select fdfc.descriptive_flex_context_code
          ,fdfc.descriptive_flexfield_name
          ,fdfc.application_id
    From fnd_descr_flex_contexts fdfc
    Where fdfc.descriptive_flexfield_name = 'Your DFF NAME';
Begin
  For i In dff_context_cur
  Loop
    Fnd_Descr_Flex_Contexts_Pkg.Delete_Row ( i.application_id, i.descriptive_flexfield_name, i.descriptive_flex_context_code);
  End Loop;
END;

Remove Column Usage
=================
Declare
  Cursor dff_col_usage_cur Is
        Select fdfcu.application_column_Name
              ,fdfcu.Descriptive_Flex_Context_Code
              ,fdfcu.Descriptive_Flexfield_Name
              ,fdfcu.Application_id
        From fnd_Descr_Flex_Column_Usages fdfcu
        Where fdfcu.Descriptive_Flexfield_Name = 'Your DFF NAME';
Begin
  For i IN dff_col_usage_cur
  Loop
    Fnd_Descr_Flex_Col_Usage_Pkg.Delete_Row (i.Application_id,i.Descriptive_Flexfield_Name,i.Descriptive_Felx_Context_Code,i.application_column_Name);
End Loop;
End;

Last Remove the registered DFF completely
===============================
Declare
  Cursor dff_flex_cur Is
      Select fdf.Descriptive_Flexfield_Name
              ,fdf.Application_id
      From Fnd_Descriptive_Flexs fdf
      Where fdf.Descriptive_Flexfield_Name = 'Your DFF Name';
Begin
  For i In dff_flex_cur
  Loop
    Fnd_Descriptive_Flexs_Pkg.Delete_Row (i.Application_id,i.Descriptive_Flexfield_Name);
  End Loop;
End;

How to delete Business Event and Subscription

Declare
   Cursor c_event_cur
   Is
     Select we.guid event_guid
           ,wes.guid subscription_guid
     From wf_events we
           ,wf_event_subscriptions wes
     Where we.name = 'sat.oracle.apps.xxpm.generateProposal'  -- your custom event name
     And wes.event_filter_guid = we.guid;  
Begin
   For i in c_event_cur
   Loop
      wf_events_pkg.delete_row(i.event_guid);
      wf_event_subscriptions_pkg.delete_row(i.subscription_guid);
   End Loop;
   commit;
Exception  
   When Others Then
      dbms_output.put_line(sqlerrm);
End;

After Sucessful Cloning: FRM 92101 issues on cloned instance while starting forms

I setup my clone instance successfully, but when login into forms application getting following error message:

Form starting with error

oracle.forms.net.ConnectionException: Forms session <1> aborted: unable to communicate with runtime process.
  at oracle.forms.net.ConnectionException.createConnectionException(Unknown Source)

I checked the following forms log files:
formsstd.err and formsstd.out

formsstd.out:

16/06/10 09:31:36 Oracle Containers for J2EE 10g (10.1.3.5.0)  initialized
16/06/10 09:35:01 ListenerServlet init()
16/06/10 09:35:02 Forms session <1> aborted: unable to communicate with runtime process.
16/06/10 09:54:12 Shutting down...
16/06/10 09:55:14 FormsServlet init():
    configFileName:     /u02/app/oracle/PROD/inst/apps/CLONE_XXXXX/ora/10.1.2/forms/server/appsweb.cfg
    testMode:           false

Solution:

Compile all menus and application forms using adadmin tool

R12.1.3: adcfgclone.pl failed with afcpctx.sh INSTE8_PRF 136

R12.1.3 application tier cloning script was failed with following error:

 [PROFILE PHASE]
  AutoConfig could not successfully execute the following scripts:
    Directory: /u02/app/oracle/PROD/inst/apps/CLONE_<CONTEXT_NAME>/admin/install
      afcpctx.sh              INSTE8_PRF         136


AutoConfig is exiting with status 1

RC-50014: Fatal: Execution of AutoConfig was failed
Raised by oracle.apps.ad.clone.ApplyApplTop
ERROR: AutoConfig completed with errors. Check logfile at /u02/app/oracle/PROD/inst/apps/CLONE_<CONTEXT_NAME>/admin/log/ApplyAppsTier_06100752.log for details.
ApplyApplTop Completed Successfully.

When I execute afcpctx.sh independently: 

Floating point exception| FNDCPUCF $USERNAME 0 Y /u02/app/oracle/PROD/inst/apps/CLONE_<CONTEXT_NAME>/appl/admin/CLONE_<CONTEXT_NAME>.xml

Solution:

source the application environment file
run relink as follows  
    adrelink.sh force=y ranlib=y "AD all"  
    adrelink.sh force=y ranlib=y "fnd FNDCPUCF"

try again

$perl adcfgclone.pl appsTier

now cloning will be completed successfully.

How to disable CC Direct Manager functionality in Expenses Workflow

1. Open the wft file and double click on 'IS_MANAGER_IN_THE_APPR_PROCESS'.
2. Click on the Node Attributes and here we have an attribute named 'CC Direct Manager Notification'.
3. Please update the value to No, save the workflow file to the database.
4. Bounce the workflow listeners as well as apache.

R12.ATG_PF.C.Delta.5 Release Update Pack Features

This release introduces an updated Look and Feel for Oracle Application Framework-based pages, the Oracle Alta UI. Ensure that you either set the Oracle Applications Look and Feel profile option to "Alta Look and Feel" or to a Look and Feel that extends the Alta Look and Feel, or leave this profile option blank to use the standard Alta Look and Feel by default. Oracle supports only this standard Look and Feel, or one that extends it, for R12.ATG_PF.C.Delta.5. Oracle no longer supports the Look and Feel options of previous releases.

API to delete value set values

Use the following api to delete unwanted/unusable value set values

 fnd_flex_values_pkg.delete_row (<<flex_Value_id>>);

Flex value id can be select using the following select statement:

Select a.flex_value_id
From FND_FLEX_VALUES_VL a
  ,fnd_flex_value_sets b
Where a.FLEX_VALUE_SET_ID = b.FLEX_VALUE_SET_ID
And b.FLEX_VALUE_SET_NAME = 'XXPM_VALUE_SETNAME'


Don't use this for deleting application seeded value set values. Above solution can only be used for custom value sets.

Employee Payroll Costing Creation Using API

Employee Payroll Creation Using API

Declare
  Cursor c_cost_emp_cur Is
         Select paaf.assignment_id
         From per_all_assignments_f paaf
             ,per_all_people_f papf
         Where papf.person_id = paaf.person_id
         And sysdate between papf.effective_start_date And papf.effective_end_date
         And sysdate between paaf.effective_start_date and paaf.effective_end_date

         And Not exists (Select 'x'
                                    From pay_cost_Allocations_F pcaf
                                   Where pcaf.assignment_id =  paaf.assignment_id
                                   And sysdate Between pcaf.effective_Start_date and pcaf.effective_end_date);
Type l_emp_rec Is Table of c_cost_emp_cur%rowtype Index By Pls_Integer;
l_emp_table l_emp_rec;
l_flag Boolean;
l_combination_name          varchar2(150);
l_cost_allocation_id        number;
l_cost_effective_start_date date;
l_cost_effective_end_date   date;
l_cost_allocation_keyflex_id pay_cost_allocation_keyflex.cost_allocation_keyflex_id%type;
l_cost_obj_version_number   number;

Begin
l_emp_table.delete;
Open c_cost_emp_cur;
Fetch c_cost_emp_cur Bulk Collect Into l_emp_table;
Close c_cost_emp_cur;

For i In 1..l_emp_table.Count
Loop
 -- We enabled only cost center in cost allocation.
  pay_cost_allocation_api.create_cost_allocation (p_validate => false
                        ,p_effective_date       => '01-nov-2015'
                        ,p_assignment_id        => l_emp_table(i).assignment_id
                        ,p_proportion           => 1 -- the parameter value should be 1 , This is equal to 100% entered via applcation screen
                        ,p_business_group_id    => 101
                       -- ,p_segment1             => l_company
                        ,p_segment2             => '254'
                        --,p_segment3             => l_cost_code
                        --,p_segment4             => l_budget_code
                        --,p_segment5             => l_account
                        -- Out
                        ,p_combination_name     => l_combination_name
                        ,p_cost_allocation_id   => l_cost_allocation_id
                        ,p_effective_start_date => l_cost_effective_start_date
                        ,p_effective_end_date   => l_cost_effective_end_date
                        ,p_object_version_number => l_cost_obj_version_number
                        -- In / Out
                        ,p_cost_allocation_keyflex_id => l_cost_allocation_keyflex_id
                        );

End Loop;

commit;

End;

Update People National Identifier using API

Bulk Update Employee national identifier using oracle hrms api

Declare
 Cursor c_emp_cur (cp_employee_number In Varchar2) Is
        Select papf.employee_number
              ,papf.effective_start_date
              ,papf.object_Version_number
              ,papf.person_id
        From per_All_people_f papf
        Where papf.business_group_id = <<Business Group Id>>
        And papf.person_type_id = <<Active Person Type Id>>
        And papf.employee_number = cp_employee_number
        And sysdate Between papf.effective_start_date And papf.effective_end_date;
  l_emp_rec c_emp_cur%rowtype;
  l_emp_no Varchar2(30);
  l_effective_start_date Date;
  l_effective_end_date Date;
  l_full_name per_all_people_f.employee_number%type;
  l_commnet_id Number;
  l_name_combination_warning Boolean;
  l_assign_payroll_warning Boolean;
  l_orig_hire_warning Boolean;
Begin
 -- initialize app session
 fnd_global.apps_initialize(<<AOL user id>>,<<HRMS Responsibility Id>>,HRMS Application Id (Default Id 800));
 For i In (Select  emp_no,
                           nat_no
              from xxhr_emp_nat )
  Loop
    Open c_emp_cur(cp_employee_number => i.emp_no);
    Fetch c_emp_cur Into l_emp_rec;
    Close c_emp_cur;
  l_emp_no := l_emp_rec.employee_number;
  hr_person_api.update_person
  (p_effective_date               => l_emp_rec.effective_start_date
  ,p_datetrack_update_mode        => 'CORRECTION'
  ,p_person_id                    => l_emp_rec.person_id
  ,p_object_version_number        => l_emp_rec.object_Version_number
  ,p_employee_number              => l_emp_no
  ,p_national_identifier          => i.nat_no
  ,p_effective_start_date         => l_effective_start_date
  ,p_effective_end_date           => l_effective_end_date
  ,p_full_name                    => l_full_name
  ,p_comment_id                   => l_commnet_id
  ,p_name_combination_warning     => l_name_combination_warning
  ,p_assign_payroll_warning       => l_assign_payroll_warning
  ,p_orig_hire_warning            => l_orig_hire_warning
  );
  End Loop;
  commit;
End;

Delete document attachments using API

Begin
l_entity_name FND_ATTACHED_DOCUMENTS.entity_name%type;
l_pk1_value FND_ATTACHED_DOCUMENTS.pk1_Value%type;
l_delete_document_flag varchar2 (1)   :=  'Y';    
l_user_id Number;
l_responsibility_id Number;
l_resp_application_id Number;
BEGIN

fnd_global.apps_initialize ( user_id          =>  l_user_id
                                      ,resp_id          =>  l_responsibility_id
                                      ,resp_appl_id  =>  l_resp_application_id);

fnd_attached_documents2_pkg.delete_attachments
( X_entity_name                 =>  l_entity_name
, X_pk1_value                    =>   l_pk1_value
, X_delete_document_flag   =>   l_delete_document_flag);

commit;
END;

SQL Query to list Active Responsibilities of a Active User

SQL Query to list Active Responsibilities of a Active User

SELECT fu.user_name,
       frv.responsibility_name,
      frv.responsibility_key,
      furgd.start_date,
       furgd.end_date
FROM fnd_user fu,
  fnd_user_resp_groups_direct furgd,
  fnd_responsibility_vl frv
WHERE fu.user_id                     = furgd.user_id
AND furgd.responsibility_id          = frv.responsibility_id
AND furgd.end_date                  IS NULL
AND furgd.start_date                <= sysdate
AND coalesce(furgd.end_date, sysdate + 1) > sysdate
AND fu.start_date                   <= sysdate
AND coalesce(fu.end_date, sysdate + 1)    > sysdate
AND frv.start_date                  <= sysdate
AND coalesce(frv.end_date, sysdate + 1)   > sysdate;

Oracle SQL Query to list all Form Personalizations

Oracle SQL Query to list all Form Personalizations

SELECT ffv.form_id                 ,
  ffv.form_name                     ,
  ffv.user_form_name                ,
  ffv.description ,
  ffcr.SEQUENCE                     ,
  ffcr.description "Rule Name",
  ffcr.trigger_object,
  ffcr.condition
  From fnd_form_vl ffv,
              fnd_form_custom_rules ffcr
  Where ffv.form_name = ffcr.form_name
  Order By ffv.form_name, ffcr.SEQUENCE;

How to delete a concurrent program in APPS

Oracle application allows creation of concurrent programs but does not allow deletion of the concurrent programs. Through form user can disable a concurrent program.

We can use API for deleting concurrent programs and executables:

Delete the concurrent program. The script is

BEGIN
   fnd_program.delete_program (program_short_name   =>'XXPMSJB',
                                                 application          =>'XXPMS');
   COMMIT;
END;

The above concurrent program is deleted and it cannot be queried.


The executable exists. It can be deleted also using PL/SQL,


BEGIN
   fnd_program.delete_executable (executable_short_name   =>'XXPMSJB',
                                  application          =>'XXPMS');
   COMMIT;
END;

Terminate Employees using API

Following API's are used to terminating employees:

hr_ex_employee_api.actual_termination_emp
hr_ex_employee_api.update_term_details_emp
hr_ex_employee_api.final_process_emp

The API's have to be executed in the above order:


Example Code:

Declare
   Cursor c_emp_cur Is
         Select ppos.period_of_service_id
               ,ppos.object_version_number
               ,papf.person_type_id
               ,a.end_date
         From per_all_people_f papf
             ,per_periods_of_service ppos
             ,xxhr_resign_people_interface a
         Where papf.person_id = ppos.person_id
         And sysdate Between papf.effective_start_date and papf.effective_end_date
         And sysdate between ppos.date_start And coalesce(ppos.projected_termination_date,actual_termination_date,sysdate)
         And papf.business_group_id = 700
         And a.person_id = papf.person_id
         And a.end_date < '01-apr-2013';
   l_validate Boolean := False;
   l_period_of_service_id Number;
   l_object_version_number Number;
   l_actual_notice_period_date Date;
   l_effective_date date;
   l_supervisor_warning Boolean;
   l_event_warning Boolean;
   l_interview_warning Boolean;
   l_review_warning Boolean;
   l_recruiter_warning Boolean;
   l_asg_future_changes_warning Boolean;
   l_f_asg_future_changes_warning Boolean;
   l_pay_proposal_warning Boolean;
   l_dod_warning Boolean;
   l_final_process_date Date;
   l_org_now_no_manager_warning Boolean;
   l_entries_changed_warning Varchar2(255);
   l_f_entries_changed_warning Varchar2(255);
   l_alu_change_warning Varchar2(255);
   l_person_type_id Number;
   l_last_std_process_date_out Date;
For c_emp_rec In c_emp_cur
  Loop
     l_period_of_service_id := c_emp_rec.period_of_service_id;
     l_object_version_number := c_emp_rec.object_version_number;
     l_actual_notice_period_date := c_emp_rec.end_date;
     l_effective_date := c_emp_rec.end_date;
     l_person_type_id := c_emp_rec.person_type_id;
     Begin
     hr_ex_employee_api.actual_termination_emp
           (p_validate => l_validate
           ,p_effective_date => l_effective_date
           ,p_period_of_service_id => l_period_of_service_id
           ,p_object_version_number => l_object_version_number
           ,p_actual_termination_date => l_actual_notice_period_date
           ,p_last_standard_process_date => l_actual_notice_period_date
     --      ,p_person_type_id => l_person_type_id
           ,p_leaving_reason => 'RESS'
           ,p_last_std_process_date_out => l_last_std_process_date_out
           ,p_supervisor_warning => l_supervisor_warning
           ,p_event_warning => l_event_warning
           ,p_interview_warning => l_interview_warning
           ,p_review_warning => l_review_warning
           ,p_recruiter_warning => l_recruiter_warning
           ,p_asg_future_changes_warning => l_asg_future_changes_warning
           ,p_entries_changed_warning => l_entries_changed_warning
           ,p_pay_proposal_warning => l_pay_proposal_warning
           ,p_dod_warning => l_dod_warning
           ,p_alu_change_warning => l_alu_change_warning
           );
     hr_ex_employee_api.update_term_details_emp
          (p_validate    => l_validate
          ,p_effective_date          => l_effective_date
          ,p_period_of_service_id        => l_period_of_service_id
          ,p_object_version_number      => l_object_version_number
          ,p_accepted_termination_date  => l_effective_date
          ,p_leaving_reason           => 'RESS'
          ,p_notified_termination_date   => l_effective_date
          ,p_projected_termination_date  => l_effective_date
          );
     l_final_process_date := l_effective_date;

 hr_ex_employee_api.final_process_emp

         (p_validate  => l_validate

         ,p_period_of_service_id  => l_period_of_service_id

         ,p_object_version_number => l_object_version_number

         ,p_final_process_date    => l_final_process_date

         ,p_org_now_no_manager_warning => l_org_now_no_manager_warning

         ,p_asg_future_changes_warning => l_f_asg_future_changes_warning

         ,p_entries_changed_warning => l_f_entries_changed_warning

         );

     Exception

        When Others Then null;

     End;

  End Loop;

  commit;

End;

Cleaning FND_NODES Tables

Cleaning FND_NODES table

To clean the FND_NODES table use the following:

1. Shutdown all the services.
2. EXEC FND_CONC_CLONE.SETUP_CLEAN;
COMMIT;
EXIT;

run autoconfig on all the nodes starting with DB node.