API to Purge/Delete Employee from HRMS application

API to Purge/Delete Employee from HRMS application

You can use API HR_PERSON_API.DELETE_PERSON to delete an employee(person) and the associated person related records.

Example:

DECLARE
  -- Input Variables
  l_validate BOOLEAN    := FALSE;
  l_effective_date DATE := sysdate;
  l_person_id                 NUMBER    := 123048;
  l_perform_predel_validation BOOLEAN   := FALSE;
  -- Output Variables
  l_person_org_manager_warning VARCHAR2(2000);
BEGIN
  --  Calling API HR_PERSON_API.DELETE_PERSON
  hr_person_api.delete_person(p_validate                   => l_validate ,
                              p_effective_date             => l_effective_date ,
                              p_person_id                  => l_person_id ,
                              p_perform_predel_validation  => l_perform_predel_validation ,
                              p_person_org_manager_warning => l_person_org_manager_warning );
  --
  dbms_output.put_line('Employee deleted successfully');
  --
EXCEPTION
WHEN OTHERS THEN
  dbms_output.put_line('Error : ' || sqlerrm);
END;
/
Some more information on the API parameters
p_perform_predel_validation:
When this parameter is set to TRUE, the API performs a check to see if any data in addition to that set up by default, e.g. data in per_all_people_f, per_all_assignments_f, per_periods_of_service exists for the person. If no additional data exists, then the API cascade deletes the person data. If any additional data exists for this person in any non-HRMS tables, then this person will not be deleted irrespective of whether only default data exists. When this parameter is set to FALSE, the API cascade deletes all data held in HRMS tables for this person, provided no additional data exists in any non-HRMS tables.

p_person_org_manager_warning:
If the person being deleted is an organization manager, then a warning message will be returned otherwise no value will be returned.

Prerequisites:
Employee and fnd_user link must be removed by removing the person in users form
No Payroll should be processed on employee till-date
Note:
If the employee has an active payroll then we cannot purge the record. The alternative way is to either end date the employee using the termination screen or you need to change the person from ‘Employee’ to ‘Applicant’ and then use the above API again to purge the record.

Costing in Oracle Payroll: Concept

This is one of those topics which has often eluded us from understanding it in a better way than any other part of Oracle Payroll. But, this is one of the most important and vital feature of Oracle Payroll and HRMS on the whole.

It’s through the Costing that Human Resources interact with the Oracle Financials (GL) out of the box.

Let take an insight into this concept of Costing.

Costing as a concept is to account the cost incurred on an employee in terms of compensation provided to him/her by the organization. Oracle achieves this through three processes;


Costing
Costing of Payment
Transfer to Ledger(GL/SLA)
 We'll see each of these briefly;

Costing
Generates accounting entries for elements processed in Run/Quick Pay
Debits the Costing account and Credits the Clearing/Control account.
Accounting Entries are determined by the values of Cost Allocation KFF at different levels.
Whether to transfer to GL or not is determined by the “Transfer to GL” checkbox at Element Link level.
 Costing of Payments
Generates accounting entries based on the Prepayments. If no prepayments, no Costing of Payment for the employee.
Debits the Clearing/Control Account and Credits the Cash Clearing Account.
Accounting Entries are determined by the values provided at the Payment Method level.
Whether to transfer to GL or not is determined by the “Transfer to GL” checkbox at Payment Method level.
Transfer to Ledger (GL/SLA)
Based on whether the “Transfer to GL” checkbox at Element Link and Payment Method Level are checked.
Will transfer all the entries generated by the Costing Process to the GL_INTERFACE table.
Will transfer only those entries generated by Payment of Costing for whom there is a payment process (Cash, Cheque, Direct Deposit, etc) to the GL_INTERFACE table.

People Group Flexfield

People Group Flexfield

Key Flexfield: People Group Flexfield


Flexfield Title or Name : People Group Flexfield
Application Name: Payroll 

Table Name	Unique Column	Description
PAY_PEOPLE_GROUPS	PEOPLE_GROUP_ID	People Group Flexfield

How to know user password from Back End in Oracle apps R12

How to know user password from Back End in Oracle apps R12

1.

CREATE OR REPLACE PACKAGE get_pwd
AS
   FUNCTION decrypt (KEY IN VARCHAR2, VALUE IN VARCHAR2)
      RETURN VARCHAR2;
END get_pwd;
2.

CREATE OR REPLACE PACKAGE BODY get_pwd
AS
   FUNCTION decrypt (KEY IN VARCHAR2, VALUE IN VARCHAR2)
      RETURN VARCHAR2
   AS
      LANGUAGE JAVA
      NAME 'oracle.apps.fnd.security.WebSessionManagerProc.decrypt(java.lang.String,java.lang.String) return java.lang.String';
END get_pwd;
/
3. run below select query you will get user_name and password.

select usr.user_name,
       get_pwd.decrypt
          ((select (select get_pwd.decrypt
                              (fnd_web_sec.get_guest_username_pwd,
                               usertable.encrypted_foundation_password
                              )
                      from dual) as apps_password
              from fnd_user usertable
             where usertable.user_name =
                      (select substr
                                  (fnd_web_sec.get_guest_username_pwd,
                                   1,
                                     instr
                                          (fnd_web_sec.get_guest_username_pwd,
                                           '/'
                                          )
                                   - 1
                                  )
                         from dual)),
           usr.encrypted_user_password
          ) password
  from fnd_user usr
 where upper(usr.user_name) = upper('&user_name');

User Hooks In R12

User Hooks

Oracle has provided user hooks to implement custom logic or validation on standard processes.

For example:
Creating a element entry when creating an absence
validating the DFF segments in Absence before creating a absence.
Validation on creating EITs, Element entries, absence, termination of employee etc
Custom hook package (User Hook package) is a custom package where we write procedures for doing the customizations. The user hook procedure should have same parameters as standard API module procedure which invokes the user hook.

Now lets go through the steps to attach a user hook. I am assuming the user hook for create absence after process.

Step 1 :
Get the Module id from table HR_API_MODULES. In my case the module name is like 'CREATE%ABSENCE%'. Hence I query for the module using the below query.

SELECT * FROM hr_api_modules WHERE module_name LIKE 'CREATE%ABSENCE%';

I get the api_module_id as 1731.
---------------------------------------------------------------------------
Step 2:
Next I query for hook id in table hr_api_hooks for after process. Note that 'AP' means After Process hook and 'BP' is Before Process hook.

SELECT * FROM hr_api_hooks WHERE api_module_id = 1731;

 I get  the api_hook_id  as 3840

--------------------------------------------------------------------------
Step 3:
If you know the Module name , hook package name and hook procedure , you can use the script below .
Script for attaching the hook:

--set serveroutput on size 1000000
DECLARE
--
ln_api_hook_call_id            number;
ln_object_version_number       number;
ln_api_hook_id                 number;
--
BEGIN
   --
   select ahk.api_hook_id
     into ln_api_hook_id
     from hr_api_hooks ahk, hr_api_modules ahm
    where ahm.module_name = 'CREATE_PERSON_ABSENCE'
      and ahm.api_module_type = 'BP'
      and ahk.hook_package = 'HR_PERSON_ABSENCE_BK1'
      and ahk.hook_procedure = 'CREATE_PERSON_ABSENCE_A'
      and ahk.api_hook_type = 'AP'               -- After Process
      and ahk.api_module_id = ahm.api_module_id;
   --
   -- insert a row into HR_API_HOOK_CALLS
   --
   hr_api_hook_call_api.create_api_hook_call
      (p_effective_date        => to_date('14-MAR-2014','DD-MON-YYYY')
      ,p_api_hook_id           => ln_api_hook_id
      ,p_api_hook_call_type    => 'PP'
      ,p_sequence              => 3029
      ,p_enabled_flag          => 'Y'
      ,p_call_package          => 'XXMUD_DISCIPLINARY_ACTION_PKG'--Custom Hook PKG
      ,p_call_procedure        => 'CREATE_UNAUTHORIZED_ABS_ENTRY'--Procedure for creating entries
      ,p_api_hook_call_id      => ln_api_hook_call_id
      ,p_object_version_number => ln_object_version_number);
   --
   DBMS_OUTPUT.PUT_LINE('Registered HOOK...'|| ln_api_hook_call_id );
   --
EXCEPTION
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE(SUBSTR('Error: '||SQLERRM,1,255));
END;

-------------------------------------------------------
Step 4:
Next step is to run the pre-processor to the hook. Without running the pre-processor the user hook will not work.

DECLARE
l_module_id  NUMBER; --Pass the module id
BEGIN
hr_api_user_hooks_utility.create_hooks_one_module (1282);
END;

COMMIT;
--------------------------------------------------------
Step 5:
Next step is to verify if hook is registered.

SELECT * FROM hr_api_hook_calls
WHERE call_package = 'XXMUD_DISCIPLINARY_ACTION_PKG';

If STATUS column is 'V' (Stands for Valid) and ENABLED_FLAG = 'Y' then you have successfully registered the user hook.

----------------------------------------------------------------------------
Deleting User Hook:

DECLARE
ln_object_version_number       NUMBER;
ln_api_hook_call_id            NUMBER;
BEGIN

-- Get the api_hook_call_id and object_version_number
SELECT api_hook_call_id, object_version_number
  INTO ln_api_hook_call_id,ln_object_version_number
  FROM hr_api_hook_calls
 WHERE hook_call_package = 'XXMUD_DISCIPLINARY_ACTION_PKG'
   AND hook_procedure = 'CREATE_UNAUTHORIZED_ABS_ENTRY';

--API to delete the user hook
hr_api_hook_call_api.delete_api_hook_call
               (p_api_hook_call_id     =>    ln_api_hook_call_id, -- API_HOOK_CALL_ID
                p_object_version_number => ln_object_version_number );  -- Object_version_number
   DBMS_OUTPUT.PUT_LINE('DELETED HOOK...');
 
EXCEPTION
WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE(SUBSTR('Error: '||SQLERRM,1,255));
END;