ORA-01476: Divisor is equal to zero Error when Creating an Offer for a Contractor

(Doc ID 1924072.1)

Last updated on MARCH 08, 2017

Applies to:

Oracle iRecruitment - Version 12.1 HRMS RUP5 to 12.1 HRMS RUP8 [Release 12.1]
Information in this document applies to any platform.


When attempting to create an offer, the following error occurs when clicking on applicant name:


Error Page
You have encountered an unexpected error. Please contact your System Administrator for assistance. Click here for exception details.

Error Page
Exception Details.
oracle.apps.fnd.framework.OAException: oracle.jbo.SQLStmtException: JBO-27122: SQL error during statement preparation. Statement: SELECT * FROM (SELECT job.name job
  ,grade.name grade
  ,round (((c.proposed_salary_n * decode (cpb.pay_annualization_factor
  ,cpb.pay_annualization_factor)) / (decode (cpb.pay_basis
  ,decode (fnd_profile.value ('PER_ANNUAL_SALARY_ON_FTE')
  , :1
  ,2) annualized_salary
  ,petf.input_currency_code currency
  ,cpb.name salary_basis
  ,c.proposed_salary_n salary_rate
  ,pay_basis.meaning salary_frequency
  , ((c.proposed_salary_n * decode (cpb.pay_annualization_factor
  ,cpb.pay_annualization_factor)) - (o.proposed_salary_n * decode (opb.pay_annualization_factor
  ) increase_amount
  ,round (((c.proposed_salary_n * decode (cpb.pay_annualization_factor
  ,cpb.pay_annualization_factor)) - (o.proposed_salary_n * decode (opb.pay_annualization_factor
  ,opb.pay_annualization_factor))) / (decode (o.proposed_salary_n * decode (opb.pay_annualization_factor
  ,o.proposed_salary_n * decode (opb.pay_annualization_factor
  ,opb.pay_annualization_factor))) * 100
  ,2) increase_percentage
  ,prop_reason.meaning proposal_reason
  ,paf.sal_review_period salary_review_period
  ,sal_frequency.meaning salary_review_frequency
  ,c.next_sal_review_date next_review_date
  ,ppf.effective_start_date period_start_date
  ,o.proposed_salary_n * decode (opb.pay_annualization_factor
  ,opb.pay_annualization_factor) last_anual_sal
  ,cpb.grade_annualization_factor * fnd_number.canonical_to_number (gr.maximum) maximum
  ,cpb.grade_annualization_factor * fnd_number.canonical_to_number (gr.minimum) minimum
  ,cpb.grade_annualization_factor * fnd_number.canonical_to_number (mid_value) mid_value
  , hlk1.meaning approved
  ,perf_frequency.meaning perf_review_period_frequency
  ,nvl (paf.effective_start_date
  ,ppf.effective_start_date) assg_start_date
  ,asg_type.meaning primary_flag
  ,(round (100 * (round (((c.proposed_salary_n * decode (cpb.pay_annualization_factor
  ,cpb.pay_annualization_factor) * :2 ) / (decode (cpb.pay_basis
  ,decode (fnd_profile.value ('PER_ANNUAL_SALARY_ON_FTE')
  , :3
  ,2)) / (fnd_number.canonical_to_number (gr.mid_value) * cpb.grade_annualization_factor)
  ,2)) comparatio
FROM per_all_people_f ppf
  ,per_pay_proposals c
  ,per_pay_proposals o
  ,per_all_assignments_f paf
  ,per_pay_bases cpb
  ,per_all_assignments_f opaf
  ,per_pay_bases opb
  ,pay_all_payrolls_f payroll
  ,per_grades_tl grade
  ,pay_grade_rules_f gr
  ,per_jobs_tl job
  ,pay_input_values_f ivf
  ,pay_element_types_f petf
  ,hr_lookups asg_type
  ,hr_lookups hlk1
  ,hr_lookups perf_frequency
  ,hr_lookups sal_frequency
  ,hr_lookups prop_reason
  ,hr_lookups pay_basis
WHERE c.assignment_id = o.assignment_id (+)
AND paf.primary_flag = 'Y'
AND c.last_change_date = o.change_date (+)
AND o.assignment_id = opaf.assignment_id (+)
AND o.change_date BETWEEN opaf.effective_start_date (+)
  AND opaf.effective_end_date (+)
AND paf.effective_start_date BETWEEN payroll.effective_start_date (+)
  AND payroll.effective_end_date (+)
AND paf.payroll_id = payroll.payroll_id (+)
AND opaf.pay_basis_id = opb.pay_basis_id (+)
AND c.assignment_id = paf.assignment_id
AND paf.person_id = ppf.person_id
AND paf.pay_basis_id = cpb.pay_basis_id
AND cpb.input_value_id = ivf.input_value_id
AND c.change_date BETWEEN ivf.effective_start_date
  AND ivf.effective_end_date
AND ivf.element_type_id = petf.element_type_id
AND c.change_date BETWEEN petf.effective_start_date
  AND petf.effective_end_date
AND gr.rowid (+) = hr_mee_views_gen.getasggraderule (c.pay_proposal_id)
AND paf.grade_id = grade.grade_id (+)
AND grade.language (+) = userenv ('LANG')
AND paf.job_id = job.job_id (+)
AND job.language (+) = userenv ('LANG')
AND c.change_date BETWEEN paf.effective_start_date
  AND paf.effective_end_date
AND c.change_date BETWEEN ppf.effective_start_date
  AND ppf.effective_end_date
AND paf.period_of_service_id =
  SELECT max (paf2.period_of_service_id)
  FROM per_all_assignments_f paf2
  WHERE paf2.person_id = paf.person_id
AND c.change_date AND c.approved = 'Y'
AND asg_type.lookup_type(+) = 'YES_NO'
AND asg_type.lookup_code (+) = paf.primary_flag
AND hlk1.lookup_type(+) = 'YES_NO'
AND hlk1.lookup_code (+) = c.approved
AND perf_frequency.lookup_type(+) = 'FREQUENCY'
AND perf_frequency.lookup_code (+) = paf.perf_review_period_frequency
AND sal_frequency.lookup_type(+) = 'FREQUENCY'
AND sal_frequency.lookup_code (+) = paf.sal_review_period_frequency
AND prop_reason.lookup_type(+) = 'PROPOSAL_REASON'
AND prop_reason.lookup_code (+) = c.proposal_reason
AND pay_basis.lookup_type(+) = 'PAY_BASIS'
AND pay_basis.lookup_code (+) = cpb.pay_basis
AND ppf.person_id IN
  SELECT to_number (:4) person_id
  FROM dual
  SELECT from_person_id person_d
  FROM hr_person_deployments dep
  WHERE dep.to_person_id = :5
  AND permanent = 'Y'
at oracle.apps.fnd.framework.OAException.wrapperException(OAException.java:912)
at oracle.apps.fnd.framework.OAException.wrapperException(OAException.java:886)
at oracle.apps.fnd.framework.OAException.wrapperInvocationTargetException(OAException.java:1009)
at oracle.apps.fnd.framework.server.OAUtility.invokeMethod(OAUtility.java:211)
at oracle.apps.fnd.framework.server.OAApplicationModuleImpl.invokeMethod(OAApplicationModuleImpl.java:720)
at oracle.apps.irc.common.webui.EmployeeDetailsRNCO.processRequest(EmployeeDetailsRNCO.java:96)
at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processRequest(OAWebBeanHelper.java:604)
at oracle.apps.fnd.framework.webui.OAWebBeanContainerHelper.processRequest(OAWebBeanContainerHelper.java:252)
at oracle.apps.fnd.framework.webui.beans.layout.OAHeaderBean.processRequest(OAHeaderBean.java:391)


## Detail 0 ##
java.sql.SQLDataException: ORA-01476: divisor is equal to zero

at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:439)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:395)
at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:802)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:436)
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:186)
at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:521)

The issue can be reproduced at will with the following steps:
1. Responsibility: iRecruitment Recruiter.
2. Navigation: Offers Workbench.
3. Search for applicant.
4. Press Go button.
5. Click on applicant name.
6. Get error message.


Most offers can be created. This is an issue only if the applicant is a contractor employee who has a job grade with zero average salary.


Temporarily set the average salary of the job grade to non-zero.

The issue has the following business impact:
Due to this issue, users cannot convert contractor employees to regular employees.


Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms