Remove Job Logs and Report Library Entries job is failing for PostgreSQL

Products

• Clarity PPM SaaS
• Clarity PPM On Premise

Issue/Introduction

On a PostgreSQL environment on 15.9.2, the Job Remove Job Logs and Report Library Entries is failing with Failure message: NJS-0401: Execution of job failed. 

cmn_job_logs_delete_sp is missing, on PostgreSQL only

The  job is failing in both scheduled and manual runs. 

STEPS TO REPRODUCE:

1. Go to Home > Jobs > Scheduled Jobs
2. Run the Remove Job Logs and Report Library entries job for parameters 1 for jobs and 1 for reports

Expected Results: The job completes/removes the entries from Jobs - Log and the immediate entries from Jobs - Scheduled jobs
Actual Results: Job fails with NJS-0401: Execution of job failed.

Per the Bg-ca.log the SP is missing:

ERROR 2021-05-25 11:12:12,036 [Dispatch Remove Job Logs and Report Library entries : [email protected]<servername> (tenant=clarity)] niku.njs (clarity:admin:16964551__C021B1A2-C877-4C99-A40A-AD4A6D349E71:Remove Job Logs and Report Library entries) Error executing job: 5174531
com.niku.union.persistence.PersistenceException: 
SQL error code: 0
Error message: ERROR: procedure cmn_job_logs_delete_sp(integer, integer, integer, integer) does not exist
  Hint: No procedure matches the given name and argument types. You might need to add explicit type casts.
  Position: 6

Environment

Release : 15.9.2

PostgreSQL : On Demand and On Premise, new installs and upgraded environment

Cause

This is caused by DE61091 - cmn_job_logs_delete_sp is missing, on PostgreSQL only

Resolution

This is currently under review by Engineering team as DE61091

Workaround: Create the SP manually on the database with the attached SP (PostgreSQL only)