Absent Member.. MdomingoB Absent Member..
Absent Member..
461 views

Automatically delete reports

Hello,

 

I need deleted old reports automatically but I can´t find if PPM has some functionality for it.  I known that i can deleted manually but I would like deleted the reports for example with a retention parameter.

 

Could anyone let me know if there is any automated script or parameter which can deleted periodically? 

 

 

Thanks

Marta Domingo

0 Likes
1 Reply
Derek Giedd Honored Contributor.
Honored Contributor.

Re: Automatically delete reports

PPM does not provide this functionality.  However, I have written a PPM Report that deletes other reports and deletes copies of itself older than 1 day.  To implement this you will create a new report and a database procedure and a SQL Script stored on the App Server.  The report has 2 fields:

Days to Keep, TOKEN = RPT_DUR, Validation = Numeric Text Field (length=4), Always Required

Reports to Purge: TOKEN=RPT_LST, Validation = PPM - Report Type ID, Always Required

The report has 3 commands. They are:

Define Variables 

ksc_itg_run_sql QUERY_STRING="SELECT db_connection_string from kenv_environments WHERE environment_name = 'KINTANA_SERVER'"
ksc_set DB_TNS=[SQL_OUTPUT]

Remove Reports 

ksc_comment ************************************************************************
ksc_comment Deleting records from PPM tables and creating script to delete Linux Files
ksc_comment for selected reports.
ksc_comment ************************************************************************
ksc_local_exec [AS.ORACLE_HOME]/bin/[AS.SQLPLUS] [AS.DB_USERNAME]/[AS.DB_PASSWORD]@[AS.DB_CONNECTION_STRING] @[AS.SCRIPT_DIR]remove_reports.sql '[P.RPT_LST]' '[P.RPT_DUR]' '[AS.REPORT_DIR]' '[RP.FILENAME]' '[RP.REPORT_SUBMISSION_ID]'

 

Delete Server Files 

ksc_connect_source_server SOURCE_ENV="KINTANA_SERVER"
cd /opt/ppm/app/upf/upf_shared/reports
ksc_comment ************************************************************************
ksc_comment Ignore any messages below about files that cannot be deleted.  Not all reports have 
ksc_comment these files.  But they are being attempted to be deleted just to be sure.
ksc_comment ************************************************************************
sh rep_[RP.REPORT_SUBMISSION_ID].sh
ksc_comment Shell script execution completed
ksc_exit

 

The database procedure is: 

PROCEDURE REMOVE_REPORT (rpt_list IN VARCHAR2, dur IN NUMBER, 
                         P_REPORT_ID IN NUMBER) AS

/********************************************************************
 Developer : Derek Giedd / Cox Communications, Inc.
 Parameters: List of Report IDs to be removed, Number of days to retain 
           : the report, report ID
 Purpose   : This procedure searches for all report types specified in
           : 'rem_rpt_list', creates an output file containing 
           : Linux shell script commands to delete the files associated with
           : the reports that are older than 'dur' days, and deletes the 
           : associated records in PPM.
 Usage     : This procedure is called from the PPM Report "REMOVE REPORTS".
****************************************************************************/

X_REPORT_ID         NUMBER := NULL;           -- report_id
X_LINE_NO           NUMBER := 0;              -- report line no
X_STMT_NUM          NUMBER := 0;              -- statement no
X_SQL_ERR_MSG       VARCHAR2(300);            -- sql error message
SUCCESS             CONSTANT NUMBER := 0;     -- return status value
ORA_ERROR           CONSTANT NUMBER := 1;     -- oracle error value
v_status            VARCHAR2(250) := ' ';     -- report status

/*****************************
 Get all reports to be deleted
 *****************************/
CURSOR Fnd_Rpts IS
-- Build a list of reports to remove
  SELECT rs.report_submission_id rpt_ID, rt.report_type_name Report_Type, 
         ku.full_name Created_By, 
         (substr(rs.report_name,1,instr(rs.report_name,'.')-1)) report_name,
         rs.release_date, rs.scheduled_time Sched, rs.repeat_until_date Repeat_Date, 
         rs.repeat_frequency || ' ' ||rs.recurrence_pattern_code Frequency, 
         rs.status_code Status
    FROM knta_report_submissions rs, knta_report_types rt, knta_users ku
   WHERE rs.report_type_id = rt.report_type_id 
     AND ku.user_id        = rs.created_by
     AND ((SYSDATE - rs.creation_date > dur)
      AND INSTR(rpt_list, rt.report_type_id) > 0
-- Always delete the "Remove Reports" reports older than 1 day
      OR (rt.report_type_name = 'REMOVE REPORTS' AND (sysdate - rs.creation_date > 1))) 
   ORDER BY rs.report_submission_id;

BEGIN
-- Create a new report if the report # is not found
  x_report_id := p_report_id;
  IF (P_REPORT_ID IS NULL) THEN
    SELECT KNTA_REPORTS_S.nextval INTO x_report_id FROM dual;
  END IF
  X_STMT_NUM  := 20;
  DBMS_OUTPUT.PUT_LINE('Report id = ' || X_REPORT_ID );
-- Script lines to move to the proper folder on the linux server
  KNTA_REPORT.ADD_TEXT(X_REPORT_ID,X_LINE_NO,'Y', 'cd /opt/ppm/app/upf/upf_shared');

-- Loop through the reports and output the lines to delete the files.  An .xls file 
-- may not exist but check anyway.
  FOR r IN fnd_Rpts LOOP
    KNTA_REPORT.ADD_TEXT(X_REPORT_ID,X_LINE_NO,'Y', 'rm reports/'||r.report_name||'.html');
    KNTA_REPORT.ADD_TEXT(X_REPORT_ID,X_LINE_NO,'Y', 'rm reports/'||r.report_name||'.xls');
    KNTA_REPORT.ADD_TEXT(X_REPORT_ID,X_LINE_NO,'Y', 'rm logs/reports/rep_log_'||r.rpt_id||
      '.html');
-- Delete the associated records in PPM
    DELETE FROM knta_report_submissions WHERE report_submission_id = r.rpt_id;
    DELETE FROM knta_report_output WHERE report_id = r.rpt_id;
  END LOOP;
  COMMIT;

EXCEPTION
WHEN NO_DATA_FOUND THEN
  DBMS_OUTPUT.PUT_LINE('Status = '||ORA_ERROR);
  DBMS_OUTPUT.PUT_LINE('Error Msg = '||X_SQL_ERR_MSG);

WHEN OTHERS THEN
  X_SQL_ERR_MSG := SUBSTR(TO_CHAR(X_STMT_NUM) || ': ' ||  SQLERRM,1,80);
  DBMS_OUTPUT.PUT_LINE('Status = '||ORA_ERROR);
  DBMS_OUTPUT.PUT_LINE('Error Msg = '||X_SQL_ERR_MSG);
END REMOVE_REPORT;

 SQL Script:

/****************************************************************************
 Developer : Derek Giedd / Cox Communications, Inc.
 Parameters:  report_lst
           :  report_dur
           :  report_no
           :  report_dir
           :  report_name
 Purpose   : This Scripts calls a procedure REMOVE_REPORT which generates
           :  the Linux script to remove report files.
 Usage     : This function is called from the 'Remove Reports' report
****************************************************************************/

WHENEVER SQLERROR EXIT 1;
SET serveroutput ON SIZE 9999;
define report_lst    = '&1';
define report_dur    = '&2';
define report_dir    = '&3';
define report_name   = '&4';
define report_no     = '&5';
define extension     = '.sh';
define xls_extension = '.xls';

variable report_id NUMBER;

BEGIN
  :report_id := &report_no;
  REMOVE_REPORT(rpt_list       => '&report_lst',
                dur            => &report_dur,
                P_REPORT_ID    => &report_no);
END; 
/
set lines 150;
set heading off;
set feedback off;
set term off;
set pagesize 0;
set trimspool on;

col line_text format a150;
spool &report_dir&report_name&extension;

SELECT line_text
  FROM knta_report_output
 WHERE report_id = :report_id
 ORDER BY line_num;

spool OFF;

ROLLBACK;
exit;
-- Remember to give Kudos to answers! (click the KUDOS star)
The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.