Thursday, June 5, 2014

EBS R12.2 -- FRM-40735: ON-INSERT ORA-01400

Following errors may occur in HR screens in a freshly installed EBS 12.2.3 ..
The screen navigation for reproducing the error is as follows;

Global HRMS Manager> Recuritment > Requsition and Vacancy

Form Name : PERWSVAC


Note that: Similar problem can arise in different forms, if they are affected from the cause, that I will mention at the end of this document.

So , basically it is a problem that needs to be investigated using sql tracing feature ..
We need to trace using sql trace -- with binds to see the values of the bind variables, and to find the null valued bind variables in order to investigate the underlying problem..
Definition of ORA-01400: cannot insert NULL into (string)

I wont go into details about this tracing, but I will directly jump in to the solution of this particular problem, as you can face with it while implementing HR on EBS 12.2.3 ..

The problem is with nullable columns..
There are problems in some HR tables in EBS 12.2.3 .. The problem is that they are defined as not null but they must be nullable. For example :In 12.2.0 , the problematic columns are nullable, and there are no problem exist on related HR forms, but in 12.2.3 they become nullable and problems arise.
We also have obtained the approval of Oracle Support for the following cause&fix , that we have found after analyzing the trace files;

Cause: 
Following columns are not nullable in 12.2.3.
PER_RECRUITMENT_ACTIVITIES.POSTING_CONTENT_ID PER_RECRUITMENT_ACTIVITIES.RECRUITING_SITE ID 
PER_ALL_VACANCIES.PRIMARY_POSTING_ID    

Fix:
alter table hr.PER_RECRUITMENT_ACTIVITIES modify (POSTING_CONTENT_ID NULL,
RECRUITING_SITE_ID null);
alter table hr.PER_ALL_VACANCIES modify PRIMARY_POSTING_ID NULL;

No comments :

Post a Comment