Evaluating validation rule types

The EXECUTE_VALIDATION program validates planning data in each table in scope against the validation rules defined for that table. The program performs the following actions:

• Logs a new task log record for the current table. Uses task_name format: <current table name>.
• Generates the validation SQL using the following templates:

  Overview of the templates:

  SELECT TOP <SCP_VALIDATION_MAX_NUMBER_LOGS> 
      <rule.table_name>                   AS table_name,  
      <rule.rule_id>                      AS validation_rule_id,  
      CASE                                -- optional, based on <condition>  
          WHEN <condition> THEN           -- optional, based on <condition>  
              <rule evaluation in SQL>    AS validation_rule_status,  
          ELSE 'S'                        -- optional, based on <condition>  
      END                                 -- optional, based on <condition>  
      t.*                           
  FROM  
      <rule.table_name>   t  
  WHERE  
      t.source = <SOURCE>                 -- optional, based on <SOURCE> input parameter  
  

  The "TOP’’ statement is added to the query when:

  • The SCP_VALIDATION_MAX_NUMBER_LOGS SCV configuration parameter exists, and value of the SCP_VALIDATION_MAX_NUMBER_LOGS parameter is greater than 0.

  The "TOP’’ statement is not added to the query when:

  • The SCP_VALIDATION_MAX_NUMBER_LOGS SCV configuration parameter does exist, or value of the SCP_VALIDATION_MAX_NUMBER_LOGS parameter is Null, Empty, or, 0.

  Templates for validation rule types:

  1. CONSTANT-type:

     SELECT TOP <SCP_VALIDATION_MAX_NUMBER_LOGS>  
         <rule.table_name>           AS table_name,  
         <rule.rule_id>              AS validation_rule_id,  
         CASE                          
             WHEN CAST(t.<column_name> AS NVARCHAR(150)) = <rule.constant_value> THEN 'S'  
             ELSE 'F'  
         END                         AS validation_rule_status,  
         t.*                           
     FROM  
         <rule.table_name>   t  
     WHERE  
         t.source = <SOURCE>         -- optional, based on <SOURCE> input parameter  
     

  2. REFERENCE-type:

     The REFERENCE-checks can be made to multiple tables, depending on the existence of inbox and core tables. If the referencedConstrainedId of the rule points to a planning configuration table (starting with SM_*), and if the base entity has inbox and core tables, the reference check is performed using planning configuration, inbox, and core tables.

     If the target table is inbox and the INCLUDE_USER_DATA parameter is set to Y, or if the target table is user date table, the following logic is used:

     SELECT TOP <SCP_VALIDATION_MAX_NUMBER_LOGS>  
         <rule.table_name>           AS table_name,  
         <rule.rule_id>              AS validation_rule_id,  
         CASE  
             WHEN ri.<referenced_constraint.column(1)> IS NOT NULL OR  
                  rc.<referenced_constraint.column(1)> IS NOT NULL OR  
                  ru.<referenced_constraint.column(1)> IS NOT NULL 
             THEN 'S'  
             ELSE 'F'  
         END                         AS validation_rule_status,  
         t.*                           
     FROM  
         <rule.table_name>   t  
         LEFT OUTER JOIN <referenced_constraint.table_name>  ri  
             ON t.<constraint.column(n)> = ri.<referenced_constraint.column(n)>
             -- Optionally: only if CHECK_REFERENCE_STATUS = "Y" and reference_validation_status is not null  
             AND ri.scp_validation_status IN <rule.reference_validation_status>      
             -- Optionally: only if CHECK_REFERENCE_STATUS = "Y" and reference_validation_status is null and config parameter SCP_DEFAULT_REFERENCE_STATUS is not empty  
             AND ri.scp_validation_status IN <list of values extracted from config parameter SCP_DEFAULT_REFERENCE_STATUS>      
         LEFT OUTER JOIN <referenced_constraint.CORE table_name> rc  
             ON t.<constraint.column(n)> = rc.<referenced_constraint.column(n)>  
             AND rc.deleted_flag != 'Y'  
         LEFT OUTER JOIN <referenced_constraint.Planning Configuration table_name>   ru  
             ON t.<constraint.column(n)> = ru.<referenced_constraint.column(n)>  
             AND COALESCE(ru.deleted_flag, 'N') = 'N' 
     WHERE  
         t.source = <SOURCE>         -- optional, based on <SOURCE> input parameter  
     

     If the target table is inbox and the INCLUDE_USER_DATA parameter is set to N, the following logic is used:

     SELECT TOP <SCP_VALIDATION_MAX_NUMBER_LOGS>  
         <rule.table_name>           AS table_name,  
         <rule.rule_id>              AS validation_rule_id,  
         CASE  
             WHEN ri.<referenced_constraint.column(1)> IS NOT NULL OR  
                  rc.<referenced_constraint.column(1)> IS NOT NULL 
             THEN 'S'  
             ELSE 'F'  
         END                         AS validation_rule_status,  
         t.*                           
     FROM  
         <rule.table_name>   t  
         LEFT OUTER JOIN <referenced_constraint.table_name>  ri  
             ON t.<constraint.column(n)> = ri.<referenced_constraint.column(n)>  
             -- Optionally: only if CHECK_REFERENCE_STATUS = "Y" and reference_validation_status is not null  
             AND ri.scp_validation_status IN <rule.reference_validation_status>      
             -- Optionally: only if CHECK_REFERENCE_STATUS = "Y" and reference_validation_status is null and config parameter SCP_DEFAULT_REFERENCE_STATUS is not empty  
             AND ri.scp_validation_status IN <list of values extracted from config parameter SCP_DEFAULT_REFERENCE_STATUS>      
         LEFT OUTER JOIN <referenced_constraint.CORE table_name> rc  
             ON t.<constraint.column(n)> = rc.<referenced_constraint.column(n)>  
             AND rc.deleted_flag != 'Y'  
     WHERE  
         t.source = <SOURCE>         -- optional, based on <SOURCE> input parameter  
     

     If the referenced table does not contain inbox and core tables, the validation rule is applied to user data only:

     SELECT TOP <SCP_VALIDATION_MAX_NUMBER_LOGS>  
         <rule.table_name>           AS table_name,  
         <rule.rule_id>              AS validation_rule_id,  
         CASE
             WHEN ru.<referenced_constraint.column(1)> IS NOT NULL  
             THEN 'S'  
             ELSE 'F'  
         END                         AS validation_rule_status,  
         t.*                           
     FROM  
         <rule.table_name>   t  
         LEFT OUTER JOIN <referenced_constraint.table_name>  ru  
             ON t.<constraint.column(n)> = ru.<referenced_constraint.column(n)>  
             -- Optionally: only if CHECK_REFERENCE_STATUS = "Y" and reference_validation_status is not null  
             AND ru.scp_validation_status IN <rule.reference_validation_status>      
             -- Optionally: only if CHECK_REFERENCE_STATUS = "Y" and reference_validation_status is null and config parameter SCP_DEFAULT_REFERENCE_STATUS is not empty  
             AND ru.scp_validation_status IN <list of values extracted from config parameter SCP_DEFAULT_REFERENCE_STATUS>      
     WHERE  
         t.source = <SOURCE>         -- optional, based on <SOURCE> input parameter  
     

  3. LOV-type:

     SELECT TOP <SCP_VALIDATION_MAX_NUMBER_LOGS>  
         <rule.table_name>           AS table_name,  
         <rule.rule_id>              AS validation_rule_id,  
         CASE  
             WHEN t.<column_name> IN (SELECT value FROM SZ_VALIDATION_LOV WHERE lov_id = <rule.lov_id>) THEN 'S'  
             ELSE 'F'  
         END                         AS validation_rule_status,  
         t.*                           
     FROM  
         <rule.table_name>   t  
     WHERE  
         t.source = <SOURCE>         -- optional, based on <SOURCE> input parameter  
     

  4. NOTNULL-type:

     SELECT TOP <SCP_VALIDATION_MAX_NUMBER_LOGS>  
         <rule.table_name>           AS table_name,  
         <rule.rule_id>              AS validation_rule_id,  
         CASE  
             WHEN t.<column_name> IS NOT NULL THEN 'S'  
             ELSE 'F'  
         END                         AS validation_rule_status,  
         t.*                           
     FROM  
         <rule.table_name>   t  
     WHERE  
         t.source = <SOURCE>         -- optional, based on <SOURCE> input parameter  
     

  5. UNIQUE-type:

     SELECT TOP <SCP_VALIDATION_MAX_NUMBER_LOGS>  
         <rule.table_name>           AS table_name,  
         <rule.rule_id>              AS validation_rule_id,  
         CASE  
             WHEN (COUNT(*) OVER (PARTITION BY t.<constraint.column(n))) = 1 THEN 'S'  
             ELSE 'F'  
         END                         AS validation_rule_status,  
         t.*                           
     FROM  
         <rule.table_name>   t  
     WHERE  
         t.source = <SOURCE>     -- optional, based on <SOURCE> input parameter  
     

  6. EXPRESSION-type:

     SELECT TOP <SCP_VALIDATION_MAX_NUMBER_LOGS>  
         <rule.table_name>           AS table_name,  
         <rule.rule_id>              AS validation_rule_id,  
         CASE  
             WHEN (<rule.expression>) THEN 'S'  
             ELSE 'F'  
         END                         AS validation_rule_status,  
         t.*                           
     FROM  
         <rule.table_name>   t  
     WHERE  
         t.source = <SOURCE>         -- optional, based on <SOURCE> input parameter  
     

  7. CUSTOM-type:

     SELECT TOP <SCP_VALIDATION_MAX_NUMBER_LOGS>  
         <rule.table_name>           AS table_name,  
         <rule.rule_id>              AS validation_rule_id,  
         (<rule.custom_function_name>(<execution id>, <data_selection_id>, <source>, <rule.table_name, t.id))
                                     AS validation_rule_status,  
         t.*                           
     FROM  
         <rule.table_name>   t  
     WHERE  
         t.source = <SOURCE>         -- optional, based on <SOURCE> input parameter  
     

  8. USERDATA-type:

     SELECT TOP <SCP_VALIDATION_MAX_NUMBER_LOGS>  
         <rule.table_name>           AS table_name,  
         <rule.rule_id>              AS validation_rule_id,  
         CASE
             WHEN EXISTS (   SELECT 'X' 
                             FROM  <corresponding planning configuration table> ru
                             WHERE ru.<PK constraint.column(n) on t> = t.<PK constraint.column(n) on t>
                               AND COALESCE(ru.deleted_flag,'N') = 'N'  
                               AND ru.scp_validation_passed_flag = 'Y' ) 
             THEN 'F'
             ELSE 'S'
         END     validation_rule_status,
         t.*                           
     FROM  
         <rule.table_name>   t  
     WHERE  
         t.source = <SOURCE>         -- optional, based on <SOURCE> input parameter  
     
     where n=1,2,. . .
     

  9. CHILDREN-type:

     This type of checks apply to multiple tables based on the existence of the inbox and core tables. If the referencedConstrainedId points to a planning configuration (SM_*) table, and if the base entity has inbox and core, the reference check is performed using planning configuration, inbox, and core tables.

     If the target table is inbox and the INCLUDE_USER_DATA parameter is set to Y, or if the target table is user data table, the following logic is used:

     SELECT TOP <SCP_VALIDATION_MAX_NUMBER_LOGS>  
         <rule.table_name>           AS table_name,  
         <rule.rule_id>              AS validation_rule_id,  
         CASE  
             WHEN ri.<referenced_constraint.column(1)> IS NOT NULL OR  
                  rc.<referenced_constraint.column(1)> IS NOT NULL OR  
                  ru.<referenced_constraint.column(1)> IS NOT NULL 
             THEN 'S'  
             ELSE 'F'  
         END                         AS validation_rule_status,  
         t.*                           
     FROM  
         <rule.table_name>   t  
         LEFT OUTER JOIN <referenced_constraint.table_name>  ri  
             ON t.<constraint.column(n)> = ri.<referenced_constraint.column(n)>  
         LEFT OUTER JOIN <referenced_constraint.CORE table_name> rc  
             ON t.<constraint.column(n)> = rc.<referenced_constraint.column(n)>  
             AND rc.deleted_flag != 'Y'  
         LEFT OUTER JOIN <referenced_constraint.Planning Configuration table_name>   ru  
             ON t.<constraint.column(n)> = ru.<referenced_constraint.column(n)>  
             AND COALESCE(ru.deleted_flag, 'N') = 'N' 
     WHERE  
         t.source = <SOURCE>         -- optional, based on <SOURCE> input parameter  
     

     If the target table is inbox and the INCLUDE_USER_DATA parameter is set to N, the following logic is used:

     SELECT TOP <SCP_VALIDATION_MAX_NUMBER_LOGS>  
         <rule.table_name>           AS table_name,  
         <rule.rule_id>              AS validation_rule_id,  
         CASE  
             WHEN ri.<referenced_constraint.column(1)> IS NOT NULL OR  
                  rc.<referenced_constraint.column(1)> IS NOT NULL 
             THEN 'S'  
             ELSE 'F'  
         END                         AS validation_rule_status,  
         t.*                           
     FROM  
         <rule.table_name>   t  
         LEFT OUTER JOIN <referenced_constraint.table_name>  ri  
             ON t.<constraint.column(n)> = ri.<referenced_constraint.column(n)>  
         LEFT OUTER JOIN <referenced_constraint.CORE table_name> rc  
             ON t.<constraint.column(n)> = rc.<referenced_constraint.column(n)>  
             AND rc.deleted_flag != 'Y'  
     WHERE  
         t.source = <SOURCE>         -- optional, based on <SOURCE> input parameter  
     

     If the child referenced table does not have inbox and core tables, the validation rule is applied to user data only:

     SELECT TOP <SCP_VALIDATION_MAX_NUMBER_LOGS>  
         <rule.table_name>           AS table_name,  
         <rule.rule_id>              AS validation_rule_id,  
         CASE
             WHEN ru.<referenced_constraint.column(1)> IS NOT NULL  
             THEN 'S'  
             ELSE 'F'  
         END                         AS validation_rule_status,  
         t.*                           
     FROM  
         <rule.table_name>   t  
         LEFT OUTER JOIN <referenced_constraint.table_name>  ru  
             ON t.<constraint.column(n)> = ru.<referenced_constraint.column(n)>  
     WHERE  
         t.source = <SOURCE>         -- optional, based on <SOURCE> input parameter  
     

  10. DUMMY-type:

      When no validation rules are defined for a table, the scp_validation_status column is set to Valid for all records in that table. To allow validation reports to run normally, the program uses a dummy template. This template inserts one record in validation log for each data record in the table.

      SELECT  
          <table_name>    AS table_name,  
          <-999>          AS validation_rule_id,  
          'S'             AS validation_rule_status,  
          t.*                           
      FROM  
          <table_name>    t  
      WHERE  
          t.source = <SOURCE>     -- optional, based on <SOURCE> input parameter  
      

• Generates the validation SQL by concatenating the sub-SQLs generated for each validation rule, using UNION ALL.
• Executes the generated validation SQL, which returns one result record per validated data record per validation rule.
• Inserts the query result records into the validation log table, where each log message contains the validation status for one specific validation rule.
  Note: 

  • If the ‘’SCP_VALIDATION_LOG_LEVEL’’ SCV configuration parameter is set to All, all result records are inserted into validation log table.
  • If the “SCP_VALIDATION_LOG_LEVEL” SCV configuration parameter is set to Failure, only the result records for failed checks are inserted into validation log table.
• Determines the overall validation status for each validated planning data record as follows:
  • Evaluate all validation rules defined for the table holding the current record being validated.
  • If all validation rules returned success, the record is set to Valid.
  • For each failed validation rule:

  Use the “recommended record status” property of validation rule.

  • If the “recommended record status” property is Reject for at least one validation rule, the record is set to Reject.
  • Else if the “recommended record status” property is Hold for at least one validation rule; the record is set to Hold.
  • The record is set to Warning.
• Updates the record in the Inbox table using the overall validation status. Updates only the records that are validated (based on source parameter or if SCP_VALIDATION_MAX_NUMBER_LOGS > 0).
• Commits changes if the actions on the <current table> are successfully executed.
• Logs message when SCP_VALIDATION_MAX_NUMBER_LOGS > 0 and generated log records reach the specified limit for any validation rule. Indicate that the maximum log limit is reached and not all records are updated.
• Completes the task log record and determines the task end status as follows:
  • If the task execution completed normally, however, at least one record failed validation, the task ends with Warning status.
  • If the task execution completed normally and all records passed validation, the task ends with Success status.