Clarity: Post Timesheets job fails with error 'NJS-0401: Execution of job failed.'

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

Description:

If you deactivate the job that is already scheduled the job is not cancelled and is still scheduled to run, which causes job scheduler to stop, causes error message 'NJS-0401: Execution of jobfailed.' to occur when running the post timesheets job.

This is a general problem and can occur for other scheduled reports and jobs.
If you deactivate the job that is already scheduled the job is not cancelled and is still scheduled to run, which causes job scheduler to stop
 
Steps to Reproduce:

  1. Create and activate a job. NOTE: Record the job id from the url
  2. Schedule your job
  3. Inactivate your job
  4. Note that the job no longer shows under available jobs or under scheduled jobs in the ui
  5. Run the following query:
    SELECT * FROM CMN_SCH_JOBS WHERE JOB_DEFINITION_ID = nnnnnnn

Expected Result: The job should have been automatically cancelled when the job was inactivated  
Actual Result: The job is not cancelled and is still scheduled to run in the CMN_SCH_JOBS table

WORKAROUND:
You need to determine which jobs or reports have scheduled tasks but are inactive, and try the following steps.

  1. Activate the job
  2. Cancel all scheduled instances of the job
  3. Inactivate the job again
  4. Until a fix is provided for this problem, you will need to make sure that you cancel and delete all scheduled instances of a job or report before you inactivate it

Alternate Workaround (if customer has many scheduled jobs with is_visible set to 0 and/or is_active set to 0):

  1. Use this query to determine which jobs are scheduled but no longer active or no longer visible in the UI.
    select csj.id, csj.IS_VISIBLE, csjd.IS_ACTIVE from cmn_sch_jobs csj, CMN_SCH_JOB_DEFINITIONS csjd where csj.job_definition_id = csjd.id AND (csj.IS_VISIBLE = 0 OR csjd.IS_ACTIVE=0)

    If no results are returned, this is not causing your problem.

  2. If the user has a small number of jobs that are causing the problem, have them go into Reports and Jobs on the admin side of the UI and activate the jobs causing the problem. If the jobs is_visible set to 0, have them change the IS_VISIBLE field to 1 for those jobs and then have them go into the UI and cancel and then DELETE scheduled instances for those jobs.

    If a large number of jobs are returned by the query, you can use the following sqls to delete the jobs.
    IMPORTANT: Back up or copy all affected tables before doing this.
    NOTE: Some sqls may affect 0 records. That is ok.

    NOTE 2: I have verified these queries for Clarity 8.1.1. If your customer is using any other version of Clarity, you should run an sql trace for deleting a job and verify that these queries are still correct.

    1. Delete from cmn_sch_job_logs
      where job_run_id in (select id
      from cmn_sch_job_runs
      where job_id in (select csj.id from cmn_sch_jobs csj, CMN_SCH_JOB_DEFINITIONS csjd where csj.job_definition_id = csjd.id AND (csj.IS_VISIBLE = 0 OR

      csjd.IS_ACTIVE=0)))

    2. Delete from cmn_sch_job_runs
      where job_id in (select csj.id from cmn_sch_jobs csj, CMN_SCH_JOB_DEFINITIONS csjd where csj.job_definition_id = csjd.id AND (csj.IS_VISIBLE = 0 OR csjd.IS_ACTIVE=0))

    3. Delete cmn_sec_assgnd_obj_perm
      where object_instance_id in (select csj.id from cmn_sch_jobs csj, CMN_SCH_JOB_DEFINITIONS csjd where csj.job_definition_id = csjd.id AND (csj.IS_VISIBLE = 0 OR

      csjd.IS_ACTIVE=0))
      and right_id in ( select id
      from cmn_sec_groups
      where group_code = 'ReportViewRight'
      or group_code = 'JobViewRight')

    4. Delete cmn_sec_assgnd_obj_perm
      where object_instance_id in (select csj.id from cmn_sch_jobs csj, CMN_SCH_JOB_DEFINITIONS csjd where csj.job_definition_id = csjd.id AND (csj.IS_VISIBLE = 0 OR

      csjd.IS_ACTIVE=0))
      and right_id in ( select id
      from cmn_sec_groups
      where group_code = 'ReportCreatorAuto'
      or group_code = 'JobCreatorAuto')

    5. Delete from cmn_sch_jobs
      where id in (select csj.id from cmn_sch_jobs csj, CMN_SCH_JOB_DEFINITIONS csjd where csj.job_definition_id = csjd.id AND (csj.IS_VISIBLE = 0 OR csjd.IS_ACTIVE=0))

      If this is Oracle, don't forget to commit the changes.

  3. Until a fix is provided for this problem, you will need to make sure that you cancel and delete all scheduled instances of a job or report before you inactivate it.

Solution:

Resolved in Clarity 8.1.5
Resolved in Clarity 12.0.3
  
Keywords: CLARITYKB, CLRT-27062, clarity815resolved, clarity12resolved, clarity1203resolved.