3 Replies Latest reply: Jan 31, 2013 5:00 AM by clive_t RSS

    PAY_ELEMENT_ENTRY_API.update_element_entry ERROR


      I have written a script to update two fields within the Pensions Information element.

      I have appended the entire code.

      The problem is with the update to value1 - the error is

      ERROR at line 1:
      ORA-20001: The QuickCode is invalid for GB_PENSION_POSTPONEMENT_TYPES
      ORA-06512: at "APPS.PAY_ELEMENT_ENTRY_API", line 2070
      ORA-06512: at line 100

      If I remove the update to value1, the update to value2 works OK.

      I have made the change through the application and that works OK too.

      I would be grateful for any pointers.



      -- local variables

      l_element_type_id                PAY_ELEMENT_LINKS_F.ELEMENT_TYPE_ID%TYPE;
      l_element_link_id                PAY_ELEMENT_LINKS_F.ELEMENT_LINK_ID%TYPE;
      l_element_entry_id                pay_element_entries_f.element_entry_id%TYPE;
      l_input_value_id1               pay_input_values_f.input_value_id%TYPE;
      l_input_value_id2               pay_input_values_f.input_value_id%TYPE;
      l_value1                     varchar2(22) := 'DB Scheme Postponement';
      l_value2 varchar2(11) := '28-FEB-2017';
      l_datetrack_mode                varchar2(10) := 'CORRECTION';
      l_business_group_id           NUMBER(1):=2;
      l_asst_id                     number;
      l_effective_date                date;
      l_valid_adjustment                boolean;

      --api out variables
      l_out_effective_start_date      date;
      l_out_effective_end_date      date;
      l_out_element_entry_id      number;
      l_object_version_number      number;
      l_out_create_warning     boolean;

      CURSOR csr_emp_details
           select paaf.assignment_id
           , paaf.assignment_number
           , petf.element_type_id
           , pelf.element_link_id
      , peef.object_version_number
           from pay_element_types_f petf
           , pay_element_links_f pelf
           , pay_element_entries_f peef
           , per_all_assignments_f paaf
           where peef.assignment_id = paaf.assignment_id
           and peef.element_link_id = pelf.element_link_id
           and pelf.element_type_id = petf.element_type_id
           and petf.element_name = 'Pensions Information'
           and trunc(sysdate) between trunc(petf.effective_start_date) and trunc(petf.effective_end_date)
           and trunc(sysdate) between trunc(pelf.effective_start_date) and trunc(pelf.effective_end_date)
           and trunc(sysdate) between trunc(peef.effective_start_date) and trunc(peef.effective_end_date)
           and trunc(sysdate) between trunc(paaf.effective_start_date) and trunc(paaf.effective_end_date)
           and paaf.assignment_number = '9999999'


           FOR cur_assignment in csr_emp_details
           SELECT element_entry_id, effective_start_date
                INTO l_element_entry_id, l_effective_date
                from pay_element_entries_f peef
                WHERE peef.assignment_id = cur_assignment.assignment_id
                AND peef.element_link_id = cur_assignment.element_link_id
                AND trunc(sysdate) between peef.effective_start_date and peef.effective_end_date;
                SELECT input_value_id
                INTO l_input_value_id1
                from pay_input_values_f pivf
                WHERE pivf.element_type_id = cur_assignment.element_type_id
                AND pivf.name = 'Postponement Type'
                AND trunc(sysdate) between pivf.effective_start_date and pivf.effective_end_date;     
      dbms_output.put_line('Postponement Type'||' '||l_input_value_id1);

                SELECT input_value_id
                INTO l_input_value_id2
                from pay_input_values_f pivf
                WHERE pivf.element_type_id = cur_assignment.element_type_id
                AND pivf.name = 'Postponement End Date'
                AND trunc(sysdate) between pivf.effective_start_date and pivf.effective_end_date;     
      dbms_output.put_line('Postponement End Date'||' '||l_input_value_id2);

                -- update element entry
                ( p_validate => FALSE
                     ,p_datetrack_update_mode => l_datetrack_mode
                     ,p_effective_date => l_effective_date
                     ,p_business_group_id => l_business_group_id
                     ,p_element_entry_id => l_element_entry_id
      ,p_object_version_number => cur_assignment.object_version_number
                     ,p_input_value_id1 => l_input_value_id1
                     ,p_entry_value1 => l_value1
      ,p_input_value_id2 => l_input_value_id2
                     ,p_entry_value2 => l_value2
                     ,p_effective_start_date => l_out_effective_start_date
                     ,p_effective_end_date => l_out_effective_end_date
                     ,p_update_warning => l_out_create_warning);
      END LOOP;