19.18 Configuring Quality Control (QC) Sampling Rules

This section describes how to configure Quality Control (QC) Sampling Rules.

You can define one or many sampling rules, based on which cases will move in the workflow for quality analysis.

To add a QC sampling rule:
  1. Add the entries in the KDD_QC_RULE_MASTER table. This table is used to define the overall structure of the sampling rules.

    Table 19-11 KDD_QA_RULE_MASTER Table

    Column Name Primary Key Column Type Nullable
    RULE_ID Y NUMBER(10)
    RULE_NM VARCHAR2(1000 CHAR) Y
    RULE_DESC VARCHAR2(4000 CHAR) Y
    SAMPLE_QNTITY_TYPE VARCHAR2(300 CHAR) Y
    SAMPLE_QNTITY NUMBER(10) Y
    DS_ID NUMBER(10) Y
    ACTIVE_FL VARCHAR2(1 CHAR) Y
    LAST_UPDATED_DT DATE Y
    LAST_UPDATED_BY VARCHAR2(20 CHAR) Y
    COMMENTS VARCHAR2(4000 CHAR) Y
    PRIORITY_CD NUMBER(10) Y
    ACTION_CD VARCHAR2(20 CHAR) Y
    • RULE_ID: Unique identifier of the rule.
    • RULE_NM: Name of the rule. This should be a unique name.
    • RULE_DESC: Description of the rule.
    • SAMPLE_QNTITY_TYPE: Define the quality type as PERCENTAGE or COUNT.
      • Enter PERCENTAGE if the system has to select a percentage of cases for QC from the candidate super set.
      • Enter COUNT if the system has to select a specific count of cases for QC from the candidate super set.
    • SAMPLE_QNTITY: Enter the sampling quality value in numerals. For example,
    • If SAMPLE_QNTITY = 10 and SAMPLE_QNTITY_TYPE = PERCENTAGE, then the system will take 10% of the candidate cases for QC.
    • If SAMPLE_QNTITY =10 and SAMPLE_QNTITY_TYPE = COUNT, then the system will select 10 candidate cases for QC.
    • DS_ID: Unique identifier of the data set that is associated with the rule. The data set contains the sampling logic (SQL) for this rule. This field references the DS_ID column in KDD_QC_DATASET_MASTER and KDD_QC_DATASET_VALUES tables.
    • ACTIVE_FL: This flag indicates whether the rule can be used during the QC batch.
      • If this is set to “Y”, then the system will run this rule during the QC batch.
      • If this is set to “N”, then the system will ignore this rule when the QC batch is executed.
    • LAST_UPDATED_DT: Date when the record was last updated.
    • LAST_UPDATED_BY: User name who last updated the record
    • COMMENTS: Enter the comments
    • PRIORITY_CD: Define the priority of the rule.
    • ACTION_CD:This action code is used to identify the resulting status of a case that is selected for QC as a result of this sampling rule. This should be the same as the ACTION_CD column of the KDD_ACTION table.
  2. Add the data set entries in the KDD_QC_DATASET_MASTER table. This table is used to define the data set associated with each rule.

    Table 19-12 KDD_QC_DATASET_MASTER Table

    Column Name Primary Key Column Type Nullable
    DS_ID Y NUMBER(10)
    DS_NM VARCHAR2(1000 CHAR) Y
    DS_DESC VARCHAR2(4000 CHAR) Y
    LAST_UPDATED_DT DATE Y
    LAST_UPDATED_BY VARCHAR2(20 CHAR) Y
    COMMENTS VARCHAR2(4000 CHAR) Y
    • DS_ID: Unique identifier of the data set that is associated with the rule. The data set contains the sampling logic (SQL query) for the rule. This field references the DS_ID column in KDD_QC_RULE_MASTER and KDD_QC_DATASET_VALUES tables.
    • DS_NM: Name of the data set. This should be a unique name.
    • DS_DESC: Description of data set
    • ACTIVE_FL:This flag indicates whether or not this data set can be used during the QC batch. If set to “Y”, the system will run this rule during the QC batch. If set to “N”, the system will ignore this rule when the QC batch is executed. Ideally, you should make sure that if the ACTIVE_FL on a rule is set to Yes, the ACTIVE_FL on the data set associated with that rule should also be set to Yes.
    • LAST_UPDATED_DT: Date when this record was last updated.
    • LAST_UPDATED_BY: User name who last updated this record.
    • COMMENTS: Enter the comments.
  3. Define the data set values in the KDD_QC_DATASET_VALUES table. This table is used to define the actual SQL logic for each data set.

    Table 19-13 KDD_QC_DATASET_VALUES Table

    Column Name Primary Key Column Type Nullable
    DS_ID NUMBER(10)
    DS_VALUE_TYPE VARCHAR2(1000 CHAR) Y
    DS_VALUE CLOB
    • DS_ID: Unique identifier of the data set that is associated with the rule. The data set contains the sampling logic (SQL query) for the rule. This field references the DS_ID column in KDD_QC_RULE_MASTER and KDD_QC_DATASET_MASTER tables.
    • DS_VALUE_TYPE: Define the type of data set value
    • DS_VALUE: Define the value of the data set.
      • If DS_VALUE_TYPE = USEDTABLES, then this should be the PDM name of the table that will be used in the rule.
      • If DS_VALUE_TYPE = “ANSIJOIN”, then this should be the JOIN relationship.
      • If DS_VALUE_TYPE = “WHERECLAUSE”, then this should be the WHERE condition.
      • If DS_VALUE_TYPE =“ORDERBY”
  4. The entries will be updated in the KDD_QC_RULE_XCUTN_AUDIT table.

Table 19-14 KDD_QC_RULE_XCUTN_AUDIT Table

Column Name Primary Key Column Type Nullable
RULE_ID NUMBER(10) Y
RULE_QUERY CLOB Y
EXECUTION_DT DATE Y
EXECUTION_BY VARCHAR2(20 CHAR) Y
BATCH_ID VARCHAR2(1000 CHAR) Y
  • RULE_ID: Enter the Rule ID
  • RULE_QUERY: Define the SQL query for the rule
  • EXECUTION_DT: Date of batch execution
  • EXECUTION_BY: User name who executed the batch
  • BATCH_ID: ID of the batch

A seeded batch CASE_QC_WORKFLOW_UPDATE is used to trigger the QC process. This batch can be scheduled through the Batch Scheduler like all other batches. When this batch runs, ECM compares every case in the database with every QC sampling rule defined in the system to identify candidate cases for QC. Candidate cases form the super set of cases from which a random set of cases will be selected for QC. Each sampling rule will gather its own super set of candidate cases.

Based on the logic defined for each sampling rule, the system randomly will select a percentage (or number) of cases from the super set to QC.

When the Quality Controlled cases are identified, the system will use Case Allocation rules to determine the case owner and assignee.

Here, the system also uses the action code defined on the sampling rule to call PMF and assign a resulting (QC) status to the case. These workflows need to be defined in PMF. For more information on PMF, see the Configuring Processing Modelling Framework (PMF)chapter. In out of the box workflows, the QC process is not configured.

Audit History tab is also updated with Owner name, Assignee name, and Action (on the sampling rule) that moved the case into the QC process.