Why Is SSP_DB_admin Deleting ALL WOs instead of Only Complete WOs as Expected (Doc ID 2375636.1)

Last updated on JULY 05, 2022

Applies to:

Goal

On : ASAP 7.2.0 version
We want to purge only completed orders in the SARM schema.
When we run SARM database purge by using the script , it delete all WorkOrder records of x days instead of only completed orders.



//Here is the script used for the database purge

declare
  result number;
  begin
  result := ssp_db_admin (X);
  end;
  /

  
// Here is stored procedure in sarm.sql


prompt 'Loading procs/q3_agent_name.i';
CREATE OR REPLACE FUNCTION SSP_db_admin(
days INTEGER )
RETURN INTEGER
AS
StoO_selcnt INTEGER;
StoO_error INTEGER;
StoO_rowcnt INTEGER;
StoO_errmsg VARCHAR2(255);
cutoff_dts DATE;
retval integer;
BEGIN
 IF (SSP_db_admin.days IS NOT NULL AND SSP_db_admin.days > 0) THEN
 BEGIN
  SSP_db_admin.cutoff_dts := SYSDATE-SSP_db_admin.days;
  BEGIN
  StoO_error := 0;
  StoO_rowcnt := 0;
  LOOP
  DELETE tbl_wrk_ord
  WHERE comp_dts < SSP_db_admin.cutoff_dts
  AND wo_stat = 104
  AND rownum <= 1000;
  EXIT WHEN SQL%ROWCOUNT = 0;
  COMMIT;
  StoO_rowcnt := StoO_rowcnt + SQL%ROWCOUNT;
  END LOOP;
  EXCEPTION
  WHEN OTHERS THEN
  StoO_error := SQLCODE;
  END;
 END;
 END IF;
 BEGIN
 retval := SSP_orphan_purge;
 EXCEPTION
  WHEN OTHERS THEN
  StoO_error := SQLCODE;
  StoO_errmsg := SQLERRM;
 END;
RETURN 0;
END;
/
 

Solution

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.