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.

Symptoms

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

ERROR
-----------------------

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
  ,NULL
  ,1
  ,0
  ,1
  ,cpb.pay_annualization_factor)) / (decode (cpb.pay_basis
  ,'HOURLY'
  ,decode (fnd_profile.value ('PER_ANNUAL_SALARY_ON_FTE')
  ,'Y'
  , :1
  ,1)
  ,1)))
  ,2) annualized_salary
  ,petf.input_currency_code currency
  ,c.change_date
  ,cpb.name salary_basis
  ,c.proposed_salary_n salary_rate
  ,pay_basis.meaning salary_frequency
  , ((c.proposed_salary_n * decode (cpb.pay_annualization_factor
  ,NULL
  ,1
  ,0
  ,1
  ,cpb.pay_annualization_factor)) - (o.proposed_salary_n * decode (opb.pay_annualization_factor
  ,NULL
  ,1
  ,0
  ,1
  ,opb.pay_annualization_factor))
  ) increase_amount
  ,round (((c.proposed_salary_n * decode (cpb.pay_annualization_factor
  ,NULL
  ,1
  ,0
  ,1
  ,cpb.pay_annualization_factor)) - (o.proposed_salary_n * decode (opb.pay_annualization_factor
  ,NULL
  ,1
  ,0
  ,1
  ,opb.pay_annualization_factor))) / (decode (o.proposed_salary_n * decode (opb.pay_annualization_factor
  ,NULL
  ,1
  ,0
  ,1
  ,opb.pay_annualization_factor)
  ,0
  ,NULL
  ,o.proposed_salary_n * decode (opb.pay_annualization_factor
  ,NULL
  ,1
  ,0
  ,1
  ,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
  ,NULL
  ,1
  ,0
  ,1
  ,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
  ,c.next_perf_review_date
  ,paf.perf_review_period
  ,perf_frequency.meaning perf_review_period_frequency
  ,c.pay_proposal_id
  ,ppf.person_id
  ,paf.assignment_id
  ,paf.period_of_service_id
  ,c.attribute_category
  ,c.attribute1
  ,c.attribute2
  ,c.attribute3
  ,c.attribute4
  ,c.attribute5
  ,c.attribute6
  ,c.attribute7
  ,c.attribute8
  ,c.attribute9
  ,c.attribute10
  ,c.attribute11
  ,c.attribute12
  ,c.attribute13
  ,c.attribute14
  ,c.attribute15
  ,c.attribute16
  ,c.attribute17
  ,c.attribute18
  ,c.attribute19
  ,c.attribute20
  ,nvl (paf.effective_start_date
  ,ppf.effective_start_date) assg_start_date
  ,paf.assignment_number
  ,asg_type.meaning primary_flag
  ,payroll.payroll_name
  ,(round (100 * (round (((c.proposed_salary_n * decode (cpb.pay_annualization_factor
  ,NULL
  ,1
  ,0
  ,1
  ,cpb.pay_annualization_factor) * :2 ) / (decode (cpb.pay_basis
  ,'HOURLY'
  ,decode (fnd_profile.value ('PER_ANNUAL_SALARY_ON_FTE')
  ,'Y'
  , :3
  ,1)
  ,1)))
  ,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
  UNION
  SELECT from_person_id person_d
  FROM hr_person_deployments dep
  WHERE dep.to_person_id = :5
  AND permanent = 'Y'
  )) QRSLT ORDER BY CHANGE_DATE DESC
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)
(..)

STEPS
-----------------------
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.

WHAT IS WORKING
---------------------------

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.

WORKAROUND
----------------------

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

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



Cause

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