Date Tracking in Oracle HRMS

Date Tracking in Oracle HRMS

Update Date Track Modes

To maintain employee data effectively Oracle HCM is using a mechanism called date tracking. The main motive behind the date track mode is to maintain past, present and future data effectively.

The various update date track modes are:

CORRECTION : Over writes the data. No history will maintain.
UPDATE : Keeps the history and new change will effect as of effective date
UPDATE_CHANGE_INSERT : Inserts the record and preserves the future
UPDATE_OVERRIDE : Inserts the record and overrides the future

Action: Created Employee # 22 on 01-JAN-2012

The record in PER_ALL_PEOPLE_F is as shown below.

Effective Start Date	Effective End Date	Employee Number	Marital Status	Object Version Number
01-JAN-2012	31-DEC-4712	24		2

Action: Updated record in CORRECTION mode

Effective Start Date	Effective End Date	Employee Number	Marital Status	Object Version Number
01-JAN-2012	31-DEC-4712	24	Single	3

Action: Updated record in UPDATE mode effective 01-JUN-2012 and 

Marital Status = Married

Effective Start Date	Effective End Date	Employee Number	Marital Status	Object Version Number
01-JAN-2012	31-MAY-2012	24	Single	4
01-JUN-2012	31-DEC-4712	24	Married	5

Action: Updated record in UPDATE mode effective 01-SEP-2012 and 

Marital Status = Divorced

Effective Start Date	Effective End Date	Employee Number	Marital Status	Object Version Number
01-JAN-2012	31-MAY-2012	24	Single	4
01-JUN-2012	31-AUG-2012	24	Married	6
01-SEP-2012	31-DEC-4712	24	Divorced	7

Action: Updated record in UPDATE_CHANGE_INSERT mode effective 01-MAR-2012 and 

Marital Status= Living Together

Effective Start Date	Effective End Date	Employee Number	Marital Status	Object Version Number
01-JAN-2012	29-FEB-2012	24	Single	8
01-MAR-2012	31-MAY-2012	24	Living Together	9
01-JUN-2012	31-AUG-2012	24	Married	6
01-SEP-2012	31-DEC-4712	24	Divorced	7

Action: Updated record in UPDATE_OVERRIDE mode effective 01-AUG-2012 and 

Marital Status = Divorced

Effective Start Date	Effective End Date	Employee Number	Marital Status	Object Version Number
01-JAN-2012	29-FEB-2012	24	Single	8
01-MAR-2012	31-MAY-2012	24	Living Together	9
01-JUN-2012	31-JUL-2012	24	Married	10
01-AUG-2012	31-DEC-4712	24	Divorced	11

Delete Date Track Modes

The various delete date track modes are

ZAP : wipes all records
DELETE : Deletes  current record
FUTURE_CHANGE : Deletes current and future changes.
DELETE_NEXT_CHANGE : Deletes next change

Element Entry records are shown below.

Effective Start Date	Effective End Date	Element Entry Id	Object Version Number
01-JAN-2012	12-OCT-2012	129831	3
13-OCT-2012	19-OCT-2012	129831	5
20-OCT-2012	31-DEC-4712	129831	6

Action: Delete record in ZAP mode effective 14-JAN-2012

No rows

Action: Delete record in DELETE mode effective 14-OCT-2012

Effective Start Date	Effective End Date	Element Entry Id	Object Version Number
01-JAN-2012	12-OCT-2012	129831	3
13-OCT-2012	14-OCT-2012	129831	6

Action: Delete record in FUTURE_CHANGE mode effective 14-JAN-2012

Effective Start Date	Effective End Date	Element Entry Id	Object Version Number
01-JAN-2012	31-DEC-4712	129831	4

Action: Delete record in NEXT_CHANGE mode effective 14-JAN-2012

Effective Start Date	Effective End Date	Element Entry Id	Object Version Number
01-JAN-2012	19-OCT-2012	129831	4
20-OCT-2012	31-DEC-4712	129831	6

Unable to Enter / View Value Set Values in Oracle EBS R12.2.7 or Not Able to Enter Values in Value Sets

In R12.2 value set security has enhanced. By default no body can enter or view values of Value Sets. To enable the user who can enter or view values in value sets need to perform the following steps.

1) Need to login as SYSADMIN
2) Select the "User Management" responsibility
3) Navigate to "User"
4) Find the particular user then click on update icon
5) Click on "Assign Role"
6) Select "Role" as search by and enter value as "Flexfield Value Set Security: All privileges"
or
Select "Code" as search by and enter value as"UMX|FND_FLEX_VSET_ALL_PRIVS_ROLE"
7) Select the role from result
8) Enter a justification text.
9) Click on "Save" and "Apply"
10) Login as the user(applied for role assignment) and check the same

Clear the cache from functional administrator responsibility if required. 

QUERY TO FIND RESPONSIBILITIES ATTACHED TO A USER IN ORACLE R12

SELECT * FROM FND_USER WHERE user_name LIKE 'TEST';

SELECT *
  FROM FND_USER_RESP_GROUPS
 WHERE user_id=1180; --user_id is the link between the FND_USER and the FIND_USER_RESP_GROUPS

SELECT *
  FROM FND_RESPONSIBILITY_VL
 WHERE responsibility_id=50621; --responsibility_id is the link between the FND_RESPONSIBILITY_VL and FND_USER_RESP_GROUPS

-- The query which displays the user name and his responsibility_name will be

SELECT fu.user_name, ppx.employee_number, ppx.full_name,
       frv.responsibility_name,
        fat.APPLICATION_NAME
  FROM fnd_user fu,
       fnd_user_resp_groups furg,
       fnd_responsibility_vl frv,
       per_people_x ppx,
       fnd_application_tl fat
 WHERE fu.user_id = furg.user_id
   AND furg.responsibility_id = frv.responsibility_id
   AND fu.employee_id = ppx.person_id(+)
   and furg.RESPONSIBILITY_APPLICATION_ID=fat.APPLICATION_ID
   and fu.user_name like 'TEST'

How To Enable The Costing Tab On The Organizational Payment Method

How To Enable The Costing Tab On The Organizational Payment Method

Run the following query to Enable Costing Tab in Organizational Payment Method

Note: Provide Proper Legislation Code before running the Query

INSERT INTO pay_legislative_field_info (field_name,

                                        legislation_code,

                                        rule_mode,

                                        rule_type,

                                        validation_type,

                                        validation_name,

                                        target_location)

  VALUES   ('COSTING_TAB',

            'SA', -- Legislation Code

            'Y',

            'DISPLAY',

            'TAB_PAGE_PROPERTY',

            'DISPLAY',

            'PAYWSDPM')

COMMIT;