14.46 Archiving/Unarchiving Cases

This section describes how to archive/unarchive Cases.

You can archive old-closed cases in order to improve performance. This Archived Cases page displays only the closed cases. It helps you in loading the search result quicker. However, you cannot archive old business data. You can also bring back the archived cases into production whenever required. All relationship tabs will display archived, and non-archived data.

Status and Non-Status changing actions can be performed in Archived Cases. However, it is not advised to do so.

Note:

  • Archived Cases are available under the Archived Case menu. To view the Archived Cases menu, the user must be mapped with the CMARCSRCH function. For more information, see Managing User Administration and Security Configuration.
  • The Archival utility must only be used for archiving the old-closed cases.

There are three main archival tables:

  • FCC_ARC_METADATA
  • FCC_ARC_RESULTS
  • FCC_ARCHIVE_AUDIT_LOG

Table:

Table 14-9 FCC_ARC_METADATA

Column Name Description Sample
V_APP_ID* Is used to describe the application name. OFS_NGECM
V_TABLE_NAME* Refers the object, which need to be archived and compressed. KDD_CASES
V_SCHEMA_NAME Describes the schema name. BECS_ATOMIC8124
V_COL_COND Condition on which data needs to be filtered to be Archived. Multiple "OR" conditions can be defined on the same table. STATUS_CD = 'CCNSAR' and created_tS<= TO_DATE('03/06/2023','DD/MM/YYYY')
N_PARALLEL_DEGREE Database parallel degree applied in run time for better performance. 16
V_CHILD_TABLE_FLAG Defaulted to N, When set to N - only FK's are considered else FK's+ User defined child metadata will also be picked. N
V_ROLL_BACK Defaulted to N, when set to 'Y', Archival will be disabled and table and all dependents will get to normal state. Y
N_RETENTION_PERIOD This value indicates the data that needs to be compressed automatically if not accessed/modified for "defined" number of days.

* denotes mandatory columns which must be filled in.

Table:

Table 14-10 FCC_ARC_RESULTS

Column Name Description Sample
N_SEQ_ID Generates unique sequence for the insertion. 1000
V_APP_ID* Is used to describe the application name. OFS_NGECM
V_TABLE_NAME* Refers the object, which need to be archived and compressed. KDD_CASES
V_SCHEMA_NAME Describes the schema name. BECS_ATOMIC8124
V_COL_COND Condition on which data has been be filtered to be Archived. This value is inherited from the Metadata table - FCC_ARC_METADATA. STATUS_CD = 'CCNSAR' and created_tS<= TO_DATE('03/06/2023','DD/MM/YYYY')
V_AFTER_ARC_CNT Indicates the total record count after defined table is 'Archived'. 76
V_BEFORE_ARC_CNT Indicates the total record count before defined table is 'Archived'. 95
T_TIMESTAMP Used to capture the run time record insert into the table. 22-MAY-23 12.00.00.000000 AM
V_COMMENTS Displays information about the performed action. KDD_CASES- IS PARENT TABLE
V_CHILD_TABLE_NAME Displays the child table name (both from DB derived FK's or metadata defined in FCC_ARC_CHILD_METADATA).
V_ARCH_STATUS Displays whether archival state is ENABALED OR DISABLED COMPLETED.
N_RETENTION_PERIOD This value is inherited from FCC_ARC_METADATA - N_RETENTION_PERIOD column.

* denotes mandatory columns which must be filled in.

Table 14-11 FCC_ARCHIVE_AUDIT_LOG

Column Name Description
N_RUN_ID A unique run ID for the run.
V_ACTIVITY ''Exception Encountered''
V_ACTIVITY_TIME Holds action performed time stamp.
V_ACTIVITY_STATUS Defines status of the current activity.
V_ERROR_MSG Holds ORA errors.
V_OBJECT Provides information on which process (Chunk process or Chunk re-process)
Sample Statement for Archival:
select * from kdd_cases where STATUS_CD = 'CCNSAR' 
          and created_tS<= TO_DATE('03/06/2023','DD/MM/YYYY')

Configure the FCC_ARC_METADATA table in Atomic Schema and run the below utility with arguments given in the FCC_ARC_METADATA table to archive cases in KDD_CASES table:

All the child table entries that are having FOREIGN_KEY reference to this table will also get archived.
set serveroutput on;beginPKG_FCC_ARCHIVAL_COMPRESS_UTILITY.p_fcc_archival_proc('#APP_ID#','#SCHEMA_NAME#','#TABLE_NAME#');end;/

For example:

set serveroutput on;beginPKG_FCC_ARCHIVAL_COMPRESS_UTILITY.p_fcc_archival_proc('OFS_NGECM','UT_8124ATOM','KDD_CASES');end;/

Here:

V_APP_ID = OFS_NGECM

V_TABLE_NAME = KDD_CASES

V_SCHEMA_NAME = UT_8124ATOM

To view the archival data in database, execute the below command:
ALTER SESSION SET ROW ARCHIVAL VISIBILITY = ALL;
Select * from KDD_CASES where ORA_ARCHIVE_STATE ='1';