3 Replies Latest reply: May 26, 2011 8:00 AM by Duncan Casemore RSS

    Updating a record using pay element using the API (Almost Done)

    user278427
      Hi Everyone,

      I have a question about updating a record using the PAY_ELEMENT_ENTRY_API.update_element_entry

      I have process that doesn't error out, but its doesn't seem to update the record. So my question is, is the query below the correct way to get the element name and object version number when using this API? If not, what do I need to do?

      BEGIN
      SELECT MAX(pee.element_entry_id)
      INTO x_element_id_mgr
      FROM pay_element_types_f pet,
      pay_element_links_f pel,
      pay_element_entries_f pee,
      per_all_assignments_f paaf,
      per_all_people_f papf
      WHERE pee.element_link_id = pel.element_link_id
      AND pel.element_type_id = pet.element_type_id
      AND paaf.assignment_id = pee.assignment_id
      AND papf.person_id = paaf.person_id
      AND pet.element_name = 'Mgr Rec Pct'
      AND sysdate BETWEEN pee.effective_start_date AND pee.effective_end_date
      AND sysdate BETWEEN pel.effective_start_date AND pel.effective_end_date
      AND sysdate BETWEEN pet.effective_start_date AND pet.effective_end_date
      AND sysdate BETWEEN paaf.effective_start_date AND paaf.effective_end_date
      AND sysdate BETWEEN papf.effective_start_date AND papf.effective_end_date
      AND paaf.assignment_id = c_staging.assignment_id
      AND papf.person_id = c_staging.person_id;
      EXCEPTION
      WHEN NO_DATA_FOUND THEN
      --ROLLBACK TO s1;
      v_error_message := 'For employee number '||c_staging.employee_number||' No data found for element name Mgr Rec Pct' || SUBSTR(sqlerrm, 1, 200);
      DBMS_OUTPUT.PUT_LINE('Error occurred : ' || v_error_message);
      WHEN OTHERS THEN
      --ROLLBACK TO s1;
      v_error_message := 'For employee number '||c_staging.employee_number||' Error found for element name Mgr Rec Pct' || SUBSTR(sqlerrm, 1, 200);
      DBMS_OUTPUT.PUT_LINE('Error occurred : ' || v_error_message);
      END;
      --- Get OVN
      BEGIN
      SELECT MAX(Object_version_number)
      INTO x_ele_object_version_number
      FROM pay_element_entries_f
      WHERE element_entry_id = x_element_id_mgr;
      EXCEPTION
      WHEN NO_DATA_FOUND THEN
      v_error_message := 'For employee number '||c_staging.employee_number||' Error in getting the OVN for Mgr Rec Pct ' || SUBSTR(sqlerrm, 1, 200);
      DBMS_OUTPUT.PUT_LINE('Error occurred : ' || v_error_message);
      WHEN OTHERS THEN
      v_error_message := 'For employee number '||c_staging.employee_number||' Error in getting the OVN for Mgr Rec Pct' || SUBSTR(sqlerrm, 1, 200);
      DBMS_OUTPUT.PUT_LINE('Error occurred : ' || v_error_message);
      END;
        • 1. Re: Updating a record using pay element using the API (Almost Done)
          Duncan Casemore
          Your SQL isn't bad, although you're including loads of tables you don't need to so it's probably not as fast as it could be. I'm not sure you know what element entry to update so let's look at an example:
          -- | Element Entry Id | OVN | Start Date | End Date    | Pay Value |
          -- +------------------+-----+------------+-------------+-----------+
          -- | 123                2    1-Jan-2009   31-Jan-2011   USD 400
          -- | 123                1    1-Feb-2009   18-Jun-2011   USD 500
          -- | 123                1    19-Jun-2011  25-Jun-2011   USD 600
          -- | 123                5    26-Jun-2011  31-Dec-4712   USD 700
          Here the one element entry (Id 123) has 4 effective rows. The employee gets $400 in Jan 2009, $500 from Feb 2009 to mid-June 2011, $600 for a few days later in June 2011 and then $700 from 26th June 2011 onwards.

          So your question to the forum is which one do I update. Well, that depends which one you want to update. If you want to update the 1st Jan 2009 to 31st Jan 2011 you'd pass element_entry_id => 123, ovn => 2, effective_date => to_date('1-Jan-2009', 'DD-MON-YYYY') and datetrack_mode => 'CORRECTION'.
          If you wanted to update the $700 row you'd pass OVN 5 and an effective date of 26th June 2011. And so-on.

          Now in your SQL you're filtering based on sysdate, which means you'd be updating the $500 row (ovn=1). Is that what you want?

          I have simplified your SQL as follows:
          SELECT pee.element_entry_id
                ,pee.object_version_number
          INTO   x_element_id_mgr
                ,x_ele_object_version_number
          FROM   pay_element_entries_f pee
                ,pay_element_types_f pet
          WHERE  pee.assignment_id = c_staging.assignment_id
          AND    pee.element_type_id = pet.element_type_id
          AND    pet.element_name = 'Mgr Rec Pct'
          AND    trunc(sysdate) BETWEEN
                 pee.effective_start_date AND pee.effective_end_date
          AND    trunc(sysdate) BETWEEN
                 pet.effective_start_date AND pet.effective_end_date;
          This selects both the entry Id and OVN in one go; there's no need to have 2 SQL statements. If multiple entries are allowed, this SQL could return more than one row so just watch out for that. If you don't want the row as of sysdate change the two sysdate joins accordingly.

          I hope that helps.
          • 2. Re: Updating a record using pay element using the API (Almost Done)
            user278427
            Yes it does, but I always will want to update the most recent record the $ 700 record in your example....


            BTW

            YOU HAVE BEEN SUCH A GREAT HELP

            THANK YOU! THANK YOU!
            • 3. Re: Updating a record using pay element using the API (Almost Done)
              Duncan Casemore
              No problem. Then you just tweak the SQL as follows:
              SELECT pee.element_entry_id
                    ,pee.object_version_number
              INTO   x_element_id_mgr
                    ,x_ele_object_version_number
              FROM   pay_element_entries_f pee
                    ,pay_element_types_f pet
              WHERE  pee.assignment_id = c_staging.assignment_id
              AND    pee.element_type_id = pet.element_type_id
              AND    pet.element_name = 'Mgr Rec Pct'
              AND    pee.effective_start_date =
                       (SELECT max(pee2.effective_start_date)
                        FROM   pay_element_entries_f pee2
                        WHERE  pee2.element_entry_id = pee.element_entry_id)
              AND    pee.effective_start_date BETWEEN
                     pet.effective_start_date AND pet.effective_end_date;
              Note we're ensuring that the SQL only returns the latest version of the element entry through max(pee2.effective_start_date).