1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Object_version_number

Discussion in 'Oracle HRMS & Payroll' started by Rolf Brink, Mar 6, 2018.

  1. Rolf Brink

    Rolf Brink Newly Initiated

    Messages:
    2
    Likes Received:
    1
    Trophy Points:
    40
    Location:
    Denmark
    Can someone explain this strange behavior when updating assignments-table.
    I am using hr_assignment_api.update_emp_asg to update ONE record , changing assignment_number.
    There are 4 history-records and only one call to the api, updates all 4 records.


    /* BEFORE the change of assignment_number */
    "PERSON_ID" "OBJECT_VERSION_NUMBER" "ASSIGNMENT_ID" "ASSIGNMENT_NUMBER" "EFFECTIVE_START_DATE"
    23391 7 36295 "6805590767" 01-01-2005
    23391 19 36295 "6805590767" 01-01-2011
    23391 21 36295 "6805590767" 01-08-2011
    23391 35 36295 "6805590767" 01-01-2012
    */

    /* AFTER hr_assignment_api.update_emp_asg */
    "PERSON_ID" "OBJECT_VERSION_NUMBER" "ASSIGNMENT_ID" "ASSIGNMENT_NUMBER" "EFFECTIVE_START_DATE"
    23391 36 36295 "9999" 01-01-2005
    23391 37 36295 "9999" 01-01-2011
    23391 37 36295 "9999" 01-08-2011
    23391 37 36295 "9999" 01-01-2012


    Here is the code:

    declare

    x_cagr_grade_def_id number;
    x_cagr_concatenated_segments varchar2(500);
    x_concatenated_segments varchar2(500);
    x_soft_coding_keyflex_id number;
    x_comment_id number;
    x_effective_start_date date;
    x_effective_end_date date;
    x_no_managers_warning boolean;
    x_other_manager_warning boolean;
    x_hourly_salaried_warning boolean;
    x_gsp_post_process_warning varchar2(100);
    l_validate boolean := false;
    l_datetrack_update_mode varchar2(30) := 'CORRECTION';
    l_object_version_number per_all_assignments_f.object_version_number%type;
    l_assignment_id per_all_assignments_f.assignment_id%type;
    l_old_assignment_number per_all_assignments_f.assignment_number%type:= '6805590767';
    l_new_assignment_number per_all_assignments_f.assignment_number%type:= '9999';
    l_person_id per_all_assignments_f.person_id%type := 23391;
    l_effective_start_date date := to_date('01-01-2005','DD-MM-YYYY');
    begin

    select a.object_version_number
    , a.assignment_id
    into l_object_version_number
    , l_assignment_id
    from per_all_assignments_f a
    where person_id = l_person_id
    and a.assignment_number = l_old_assignment_number
    and a.effective_start_date = l_effective_start_date
    ;


    /* Header: peasgapi.pkb 120.20.12010000.16 2010/04/29 12:29:11 sudsahu ship $ */
    hr_assignment_api.update_emp_asg
    (p_validate => l_validate -- in boolean default false
    ,p_effective_date => l_effective_start_date -- in date
    ,p_datetrack_update_mode => l_datetrack_update_mode -- in varchar2
    ,p_assignment_id => l_assignment_id -- in number
    ,p_object_version_number => l_object_version_number -- in out nocopy number
    ,p_assignment_number => l_new_assignment_number -- in varchar2 default hr_api.g_varchar2
    --,p_ass_attribute1 => l_sls_lbnr
    -- out
    ,p_cagr_grade_def_id => x_cagr_grade_def_id -- in out nocopy number -- bug 2359997
    ,p_cagr_concatenated_segments => x_cagr_concatenated_segments -- out nocopy varchar2
    ,p_concatenated_segments => x_concatenated_segments -- out nocopy varchar2
    ,p_soft_coding_keyflex_id => x_soft_coding_keyflex_id -- in out nocopy number -- bug 2359997
    ,p_comment_id => x_comment_id -- out nocopy number
    ,p_effective_start_date => x_effective_start_date -- out nocopy date
    ,p_effective_end_date => x_effective_end_date -- out nocopy date
    ,p_no_managers_warning => x_no_managers_warning -- out nocopy boolean
    ,p_other_manager_warning => x_other_manager_warning -- out nocopy boolean
    ,p_hourly_salaried_warning => x_hourly_salaried_warning -- out nocopy boolean
    ,p_gsp_post_process_warning => x_gsp_post_process_warning -- out nocopy varchar2
    );

    end;
    /
     
  2. Mohammad Zubair Umar

    Mohammad Zubair Umar Active Member

    Messages:
    7
    Likes Received:
    1
    Trophy Points:
    85
    Location:
    New Delhi
    Hi Rofl Brink,

    I tesed on the above scenario and could see that when you are trying to change the assignment number on the very first record for an assignment id, Oracle API is synchronizing the assignment number for all records for that assignment id and hence the object version number as well the assignment number is getting changed.

    However if you just try to change the ass_Attribute1 it just changes the object version number of first record and dont change anything on the next record.

    You try the same at your end after commenting assignment number parameter and uncommenting p_ass_attribute1 in the api call.

    Result at my end while just changing ass_attribute1

    Before API Call
    Assignment Number Effective Date Object Version Number Assignment ID Last Update Date
    2938-2 31-03-14 12:00:00 22 101 07-03-18 12:43:14
    2938-2 31-05-14 12:00:00 23 101 07-03-18 12:43:14

    After API call
    Assignment Number Effective Date Object Version Number Assignment ID Last Update Date
    2938-2 31-03-14 12:00:00 25 101 07-03-18 12:45:53
    2938-2 31-05-14 12:00:00 23 101 07-03-18 12:43:14

    Thanks,
    Zubair
     
    Rolf Brink likes this.
  3. Rolf Brink

    Rolf Brink Newly Initiated

    Messages:
    2
    Likes Received:
    1
    Trophy Points:
    40
    Location:
    Denmark
    Thanks Zubair !
    Thats explains it ....assignment_number is central information and need to be in sync. for the given assignment_id
     
    Mohammad Zubair Umar likes this.
  4. Mohammad Zubair Umar

    Mohammad Zubair Umar Active Member

    Messages:
    7
    Likes Received:
    1
    Trophy Points:
    85
    Location:
    New Delhi
    My Pleasure!