5 Replies Latest reply: Feb 15, 2013 3:39 AM by Vigneswar Battu RSS

    Salary Administration Error

    874732
      We are not able to add the salary record from SalaryAdmin(SalAdminPG) page.
      When we hit add row for components or apply button we get the error
      Encountered Unexpected error. Please contact the System Administrator.

      We created a custom wrapper for hr_upload_proposal_api.upload_salary_proposal, and created web adi to upload the data to system.
      But we are getting the following error
      PAY HR_51310_PPC_INVAL_PRO_ID : The proposal_id is not valid. Please enter a valid proposal_id.

      code is as below
      PROCEDURE insert_row (
      p_employee_name VARCHAR2,
      p_employee_number VARCHAR2,
      p_change_date IN DATE,
      p_proposed_salary IN NUMBER DEFAULT NULL,
      p_proposal_reason IN VARCHAR2 DEFAULT NULL,
      p_next_sal_review_date IN DATE DEFAULT NULL,
      -- Bug 1620922 ,
      p_forced_ranking IN NUMBER DEFAULT NULL,
      p_date_to IN DATE DEFAULT NULL,
      p_component_reason_1 IN VARCHAR2 DEFAULT NULL,
      p_change_amount_1 IN NUMBER DEFAULT NULL,
      p_change_percentage_1 IN NUMBER DEFAULT NULL,
      p_component_reason_2 IN VARCHAR2 DEFAULT NULL,
      p_change_amount_2 IN NUMBER DEFAULT NULL,
      p_change_percentage_2 IN NUMBER DEFAULT NULL,
      p_component_reason_3 IN VARCHAR2 DEFAULT NULL,
      p_change_amount_3 IN NUMBER DEFAULT NULL,
      p_change_percentage_3 IN NUMBER DEFAULT NULL,
      p_component_reason_4 IN VARCHAR2 DEFAULT NULL,
      p_change_amount_4 IN NUMBER DEFAULT NULL,
      p_change_percentage_4 IN NUMBER DEFAULT NULL,
      p_component_reason_5 IN VARCHAR2 DEFAULT NULL,
      p_change_amount_5 IN NUMBER DEFAULT NULL,
      p_change_percentage_5 IN NUMBER DEFAULT NULL,
      p_component_reason_6 IN VARCHAR2 DEFAULT NULL,
      p_change_amount_6 IN NUMBER DEFAULT NULL,
      p_change_percentage_6 IN NUMBER DEFAULT NULL,
      p_component_reason_7 IN VARCHAR2 DEFAULT NULL,
      p_change_amount_7 IN NUMBER DEFAULT NULL,
      p_change_percentage_7 IN NUMBER DEFAULT NULL,
      p_component_reason_8 IN VARCHAR2 DEFAULT NULL,
      p_change_amount_8 IN NUMBER DEFAULT NULL,
      p_change_percentage_8 IN NUMBER DEFAULT NULL,
      p_component_reason_9 IN VARCHAR2 DEFAULT NULL,
      p_change_amount_9 IN NUMBER DEFAULT NULL,
      p_change_percentage_9 IN NUMBER DEFAULT NULL,
      p_component_reason_10 IN VARCHAR2 DEFAULT NULL,
      p_change_amount_10 IN NUMBER DEFAULT NULL,
      p_change_percentage_10 IN NUMBER DEFAULT NULL
      )
      AS
      g_bg_id NUMBER
      DEFAULT fnd_profile.VALUE ('PER_BUSINESS_GROUP_ID');
      g_validate BOOLEAN DEFAULT FALSE;
      l_pay_proposal_id NUMBER;
      l_object_version_number NUMBER;
      l_component_id_1 NUMBER;
      l_ppc_object_version_number_1 NUMBER;
      l_component_id_2 NUMBER;
      l_ppc_object_version_number_2 NUMBER;
      l_component_id_3 NUMBER;
      l_ppc_object_version_number_3 NUMBER;
      l_component_id_4 NUMBER;
      l_ppc_object_version_number_4 NUMBER;
      l_component_id_5 NUMBER;
      l_ppc_object_version_number_5 NUMBER;
      l_component_id_6 NUMBER;
      l_ppc_object_version_number_6 NUMBER;
      l_component_id_7 NUMBER;
      l_ppc_object_version_number_7 NUMBER;
      l_component_id_8 NUMBER;
      l_ppc_object_version_number_8 NUMBER;
      l_component_id_9 NUMBER;
      l_ppc_object_version_number_9 NUMBER;
      l_component_id_10 NUMBER;
      l_ppc_object_version_number_10 NUMBER;
      l_pyp_proposed_sal_warning BOOLEAN;
      l_additional_comp_warning BOOLEAN;
      l_assignment_id NUMBER;
      --l_create_flag                    BOOLEAN        DEFAULT FALSE;
      l_multiple_components VARCHAR2 (3);
      g_approved VARCHAR2 (2) DEFAULT 'Y';
      l_inv_next_sal_date_warning BOOLEAN;
      l_proposed_salary_warning BOOLEAN;
      l_approved_warning BOOLEAN;
      l_payroll_warning BOOLEAN;
      l_error_text VARCHAR2 (200);
      l_new_salary NUMBER;
      BEGIN

      BEGIN
      SELECT pax.assignment_id
      INTO l_assignment_id
      FROM per_people_x ppx, per_assignments_x pax
      WHERE ppx.person_id = pax.person_id
      AND pax.primary_flag = 'Y'
      AND ppx.employee_number = p_employee_number;
      EXCEPTION
      WHEN OTHERS
      THEN
      raise_application_error (-20001, 'Step 1. Employee ID not found');
      END;

      BEGIN
      SELECT pay_proposal_id, multiple_components
      INTO l_pay_proposal_id, l_multiple_components
      FROM per_pay_proposals
      WHERE assignment_id = l_assignment_id
      AND change_date = p_change_date;
      EXCEPTION
      WHEN OTHERS
      THEN
      l_pay_proposal_id := NULL;
      l_object_version_number := NULL;
      END;

      IF l_pay_proposal_id IS NULL
      THEN
      BEGIN
      hr_upload_proposal_api.upload_salary_proposal
      (p_validate => g_validate,
      p_change_date => p_change_date,
      p_business_group_id => g_bg_id,
      p_assignment_id => l_assignment_id,
      p_proposed_salary => p_proposed_salary,
      p_proposal_reason => p_proposal_reason,
      p_pay_proposal_id => l_pay_proposal_id,
      p_object_version_number => l_object_version_number,
      p_component_reason_1 => p_component_reason_1,
      p_change_amount_1 => p_change_amount_1,
      p_change_percentage_1 => p_change_percentage_1,
      p_approved_1 => g_approved,
      p_component_id_1 => l_component_id_1,
      p_ppc_object_version_number_1 => l_ppc_object_version_number_1,
      p_component_reason_2 => p_component_reason_2,
      p_change_amount_2 => p_change_amount_2,
      p_change_percentage_2 => p_change_percentage_2,
      p_approved_2 => g_approved,
      p_component_id_2 => l_component_id_2,
      p_ppc_object_version_number_2 => l_ppc_object_version_number_2,
      p_component_reason_3 => p_component_reason_3,
      p_change_amount_3 => p_change_amount_3,
      p_change_percentage_3 => p_change_percentage_3,
      p_approved_3 => g_approved,
      p_component_id_3 => l_component_id_3,
      p_ppc_object_version_number_3 => l_ppc_object_version_number_3,
      p_component_reason_4 => p_component_reason_4,
      p_change_amount_4 => p_change_amount_4,
      p_change_percentage_4 => p_change_percentage_4,
      p_approved_4 => g_approved,
      p_component_id_4 => l_component_id_4,
      p_ppc_object_version_number_4 => l_ppc_object_version_number_4,
      p_component_reason_5 => p_component_reason_5,
      p_change_amount_5 => p_change_amount_5,
      p_change_percentage_5 => p_change_percentage_5,
      p_approved_5 => g_approved,
      p_component_id_5 => l_component_id_5,
      p_ppc_object_version_number_5 => l_ppc_object_version_number_5,
      p_component_reason_6 => p_component_reason_6,
      p_change_amount_6 => p_change_amount_6,
      p_change_percentage_6 => p_change_percentage_6,
      p_approved_6 => g_approved,
      p_component_id_6 => l_component_id_6,
      p_ppc_object_version_number_6 => l_ppc_object_version_number_6,
      p_component_reason_7 => p_component_reason_7,
      p_change_amount_7 => p_change_amount_7,
      p_change_percentage_7 => p_change_percentage_7,
      p_approved_7 => g_approved,
      p_component_id_7 => l_component_id_7,
      p_ppc_object_version_number_7 => l_ppc_object_version_number_7,
      p_component_reason_8 => p_component_reason_8,
      p_change_amount_8 => p_change_amount_8,
      p_change_percentage_8 => p_change_percentage_8,
      p_approved_8 => g_approved,
      p_component_id_8 => l_component_id_8,
      p_ppc_object_version_number_8 => l_ppc_object_version_number_8,
      p_component_reason_9 => p_component_reason_9,
      p_change_amount_9 => p_change_amount_9,
      p_change_percentage_9 => p_change_percentage_9,
      p_approved_9 => g_approved,
      p_component_id_9 => l_component_id_9,
      p_ppc_object_version_number_9 => l_ppc_object_version_number_9,
      p_component_reason_10 => p_component_reason_10,
      p_change_amount_10 => p_change_amount_10,
      p_change_percentage_10 => p_change_percentage_10,
      p_approved_10 => g_approved,
      p_component_id_10 => l_component_id_10,
      p_ppc_object_version_number_10 => l_ppc_object_version_number_10,
      p_pyp_proposed_sal_warning => l_pyp_proposed_sal_warning,
      p_additional_comp_warning => l_additional_comp_warning
      );
      EXCEPTION
      WHEN OTHERS
      THEN
      raise_application_error (-20001, 'Step 2.' || SQLERRM);
      END;

      BEGIN
      SELECT proposed_salary_n
      INTO l_new_salary
      FROM per_pay_proposals
      WHERE pay_proposal_id = l_pay_proposal_id;
      EXCEPTION
      WHEN OTHERS
      THEN
      raise_application_error (-20001,
      'Step 3. '
      || p_proposal_reason
      || ' '
      || l_new_salary
      || ' '
      || SQLERRM
      );
      END;

      BEGIN
      hr_maintain_proposal_api.approve_salary_proposal
      (p_pay_proposal_id => l_pay_proposal_id,
      p_change_date => p_change_date,
      p_proposed_salary_n => l_new_salary,
      p_object_version_number => l_object_version_number,
      p_validate => g_validate,
      p_inv_next_sal_date_warning => l_inv_next_sal_date_warning,
      p_proposed_salary_warning => l_proposed_salary_warning,
      p_approved_warning => l_approved_warning,
      p_payroll_warning => l_payroll_warning,
      p_error_text => l_error_text
      );
      EXCEPTION
      WHEN OTHERS
      THEN
      raise_application_error (-20001, 'Step 4. ' || SQLERRM);
      END;
      ELSE
      raise_application_error
      (-20001,
      'Step 5. Salary proposal already exists on '
      || TO_CHAR (p_change_date, 'dd-mon-yyyy')
      );
      END IF;
      END insert_row;

      We are on 11.5.10.2, rup1

      Any help would be greatly appreciated.

      Edited by: 871729 on Feb 13, 2013 1:32 PM
        • 1. Re: Salary Administration Error
          Vigneswar Battu
          Do you intend to check for NOT NULL
          IF l_pay_proposal_id IS NULL
          Check your code mate :)

          Cheers,
          Vignesh
          • 2. Re: Salary Administration Error
            874732
            we are trying to insert the proposal record if new, else skip it
            • 3. Re: Salary Administration Error
              Vigneswar Battu
              oops, my bad..
              The error is raised from line 1362 of - hr_upload_proposal_api.upload_salary_proposal
                    if csr_get_proposal_detail%notfound then
                       hr_utility.set_location(l_proc,45);
                       close  csr_get_proposal_detail;
                       hr_utility.set_message(801,'HR_51310_PPC_INVAL_PRO_ID');
                          hr_utility.raise_error;
                       else
                       close  csr_get_proposal_detail;
              And the cursor definition -
                 cursor csr_get_proposal_detail is
                 select change_date,
                     proposed_salary_n,
                     multiple_components,
                     approved,
                     proposal_reason,
                     object_version_number
                 from   per_pay_proposals
                 where  assignment_id     = p_assignment_id
                 and    pay_proposal_id   = p_pay_proposal_id
                 and    business_group_id = p_business_group_id;
              If the above sql returns no data, you get the error..

              Put some debug messages and see which person this is erring for..

              Hope that helps.

              Cheers,
              Vignesh
              • 4. Re: Salary Administration Error
                874732
                We figured that there are two sequences

                fnd.per_pay_proposals_s
                hr.per_pay_proposals_s

                the first one generated 7445 as next seq value, but there was a
                proposal id 7445 already in the system.

                We believe that is why we got into this issue.

                Do you know of any reason why two sequences of same name but diff schema
                are there in 1st place(is it seeded).
                • 5. Re: Salary Administration Error
                  Vigneswar Battu
                  There is only one seeded sequence -
                  HR.PER_PAY_PROPOSALS_S

                  Cheers,
                  Vignesh