ORA-01400: cannot insert NULL into DWH_FIN_PLAN PLAN_START_DATE or PLAN_END_DATE

Document ID : KB000031334
Last Modified Date : 14/02/2018
Show Technical Document Details

Issue: 

The 'Load Data Warehouse' job is failing with one of the following error messages.  How can I resolve this error?

[CA Clarity][Oracle JDBC Driver][Oracle]ORA-01400: cannot insert NULL into ("PPMDBSCHEMA"."DWH_FIN_PLAN"."PLAN_START_DATE")  

OR

[CA Clarity][Oracle JDBC Driver][Oracle]ORA-01400: cannot insert NULL into ("PPMDBSCHEMA"."DWH_FIN_PLAN"."PLAN_END_DATE")   

Cause: 

Caused by CLRT-78721.

The job is failing because the Fiscal Time Period on the financial plan is invalid and does not exist in the Fiscal Time Periods table.  When a Fiscal Time Period is activated and used in Financial Plans, the application does not allow for that period to be deactivated and deleted.  We have seen some customers report this job failure message and it is caused by manipulating the the 'active' field on the record directly in the database to inactive.  Once the record is inactivated, the user can modify the record or delete it and this will then cause a data integrity issue on existing financial plans that use the period.

Resolution:  

Targeted for resolution in the next release (Armstrong).

As part of the fix, the job will have a check of the Financial Plans Start and End Periods.  If there is a data problem, the job will fail with an error message stating 'Financial Plans have invalid start or end periods', otherwise the job will continue and execute the core code to load the data into the warehouse.   If the job fails with this message, you can determine which periods are affected by using the queries in the steps below to identify the plans with data issues.

Workaround:   

For SaaS Customers: You can use the steps below to identify the financial plans affected by this issue and correct any Cost Plans or Benefit Plans as needed.  If any of the plans are BUDGET plans, please log a case with CA Technical Support for assistance in resolving the issue.  

For On Premise Customers: Use the following information to locate and correct the records with the bad fiscal periods.  The Cost Plans and Benefit Plans can be corrected through the application user interface.  The Budget Plans will need a direct database update to correct the bad period ID value.

  1. Execute the following query to identify any financial plans that have invalid Fiscal Time Period Id values:
    • select * from fin_plans where START_period_id NOT IN (select id from biz_com_periods) or END_period_id NOT IN (select id from biz_com_periods)
    • Results might give you a FIN_PLANS.ID = 5005411 with a PLAN_TYPE_CODE = BUDGET and an END_PERIOD_ID = 5002324
  2. You can go into the application and update the plan, if any of the records have a PLAN_TYPE_CODE = 'FORECAST' (for Cost Plan) or NULL (for Benefit Plan) 
    • The OBJECT_ID provides the internal INVESTMENT ID 
    • The OBJECT_CODE provide the type of INVESTMENT
  3. While you are looking at these investment Cost Plans and Benefit Plans, look at the Budget Plans if they are on the list.  Make a note of the new Fiscal Time period that should be used for the Budget Plans.
  4. Go to Administration, Finance: Setup, Entities - Open the appropriate Entity associated with the Investment to find and open an Active Fiscal Time Period.  Look at the URL for the internal ID value of the record and make a note of it.  To verify this is the correct ID value from the BIZ_COM_PERIODS table, execute the following query:  
    • SELECT * FROM BIZ_COM_PERIODS WHERE ID = ??????
    • Results might give you a valid BIZ_COM_PERIODS.ID = 5002508
  5. Once you have identified the Budget FIN_PLANS record and the correct BIZ_COM_PERIODS record, you can do an update statement to that specific record using the following as an example:
    • Be sure to make a back up and to test on a non-production system first so that you can verify the outcome of the selected values you are updating is correct
    • For updating the END_PERIOD_ID
      • UPDATE FIN_PLANS SET FIN_PLANS.END_PERIOD_ID = <new BIZ_COM_PERIODS.ID> WHERE FIN_PLANS.ID = <the Budget FIN_PLANS.ID>
      • Using the example from the steps above : 
        • UPDATE FIN_PLANS SET FIN_PLANS.END_PERIOD_ID = 5002508  WHERE FIN_PLANS.ID = 5005411 ;
      • Be sure to 'commit' the update statement
    • For updating the START_PERIOD_ID
      • If this issue is present also for the financial plan start period, follow the same steps to update this field 
  6. Execute the statement again from Step 1 - If no records are returned, then it is okay to execute the 'Load Data Warehouse' job and it should not fail for this reason