Configurator Query — Rules for a given Workspace

Dear All,

The following query retrieves data for the rules defined in the Configurator module of Oracle Fusion within a workspace.

CZ_WORKSPACES stores the header records for Configurator Workspaces. Workspaces organize and group Model, UI Template, and UI Template Map changes for testing and release.

CZ_WS_PARTICIPANTS Configurator workspace participant definitions.

The CZ_OBJECT_VERSIONS table contains rows representing versions or drafts of Configurator Versioned Objects in Oracle Fusion. These objects include Configurator Models, UI Templates, UI Template mappings, and more.

The CZ_MODELS table contains rows, with each representing a Configurator Model entity in Oracle Fusion.

The CZ_RULES table stores data related to Configuration Rules in Oracle Configurator.

WITH t1 AS (
    SELECT
        czw.description
      , czw.status
      , czv.object_id
      , czv.baseline_version
   , czw.name
    FROM
        cz_workspaces      czw
      , cz_ws_participants czp
      , cz_object_versions czv
    WHERE
            1 = 1

        AND czw.status = 'DEVELOPMENT'
        AND czp.participant_type = 'MODEL'
        AND czv.object_type = 'MODEL'
        AND czv.version_status = 'DRAFT'
 --
        AND czw.workspace_id = czp.workspace_id
 --
        AND czp.workspace_id = czv.workspace_id
 --  join has been to get correct value if not then need to remove 
        AND czp.pk1_value = czv.object_version_id
-- AND czv.version > 0
), t2 AS (
    SELECT
        czm1.model_id
      , egp.organization_code
      , egp.item_number
    FROM
        (
            SELECT
                model_id
              , regexp_substr(pim_item_key, '[^:]+', 1, 2) organization_id
              , regexp_substr(pim_item_key, '[^:]+', 1, 1) inventory_item_id
            FROM
                cz_models czm
        )                   czm1
      , egp_system_items_vl egp
    WHERE
            czm1.organization_id = egp.organization_id
        AND czm1.inventory_item_id = egp.inventory_item_id
), czr_t AS (
    SELECT
        rule_id
      , MAX(start_model_version) max_start_model_version
    FROM
        cz_rules
    GROUP BY
        rule_id
), czr_ft AS (
    SELECT
        rule_id
      , MAX(start_model_version) max_start_model_version
    FROM
        cz_rules czr_f
    WHERE
        czr_f.rule_object_type = 'FOLDER'
    GROUP BY
        rule_id
), czr_ft1 AS (
    SELECT
        rule_id
      , name folder_name
    FROM
        cz_rules czr_f
    WHERE
            czr_f.rule_object_type = 'FOLDER'
        AND EXISTS (
            SELECT
                1
            FROM
                czr_ft
            WHERE
                    czr_f.rule_id = czr_ft.rule_id
                AND czr_f.start_model_version = czr_ft.max_start_model_version
        )
)
SELECT
    czm.name                                model_name
  , t1.description                          work_space_desc
  , t2.organization_code                    organization_code
  , t2.item_number                          pdh_item_model
  , czr.name                                rule_name
  , czr_ft1.folder_name                            folder_name
  , czr.rule_status
  , rule_object_type
  , dbms_lob.substr(czr.rule_text, 4000, 1) rule_text
  , czr.disabled_flag
  , czr.rule_class
  , to_char(czr.creation_date,'DD/MON/YYYY Hh24:MI:SS') creation_date
  , czr.created_by
  , czr.last_updated_by
  , to_char(czr.last_update_date,'DD/MON/YYYY Hh24:MI:SS')  last_update_date
  --

  /*
  , czr.rule_id
  , czr.start_model_version
  , czr.object_version_number
  , czr.model_id
-- ,czr.NAME
  , czr.parent_folder_id
-- ,czr.RULE_OBJECT_TYPE
-- ,czr.RULE_TEXT
-- ,czr.RULE_STATUS
  , czr.invalid_flag
  , czr.model_node_path
  , czr.rule_class_seq
  , czr.reason_type
-- ,czr.DISABLED_FLAG
  , czr.signature_code
  , czr.presentation_type
  , czr.cx_class_name
  , czr.cx_class_inst_mode
  , czr.rule_class
  , czr.description
  , czr.end_model_version
  , czr.trans_attr_id
  , czr.deleted_flag
  , czr.creation_date
  , czr.last_update_date
  , czr.created_by
  , czr.last_updated_by
  , czr.last_update_login
  , czr.node_name_path
  , czr.cx_class_info
  */
FROM
    cz_models czm
  , cz_rules  czr
  , czr_ft1
  , t1
  , t2
WHERE
    ( czm.name = :p_model
      OR 'ALL' = :p_model || 'ALL' )--  
    AND czm.model_id = czr.model_id
    AND t1.object_id = czm.model_id
    AND czr.rule_object_type = 'STATEMENT'
    AND czr.RULE_STATUS not in ( 'VALIDATED' ) -- Stores current status of the rule - UNKNOWN, VALIDATED, VALIDATION_FAILED, LOGIC_GENERATED, LOGIC_FAILED. Ravi mention on 27-Dec-24 -- Validated not reqd. as these are not matching with his excel 
    AND czr.deleted_flag = 0  -- This flag indicates the logical deletion of the row. The Non-Zero value represents deleted state and 0 represents not-deleted state. 
  -- A user controlled background purge process will actually delete the orphaned rows with the Non-Zero flag value.

-- AND END_MODEL_VERSION is  null 
  --  AND t1.baseline_version = czr.start_model_version -- not giving correct result as there might be some rules carried fwd.

    AND nvl(czr.parent_folder_id, - 1) = czr_ft1.rule_id (+) -- Identifier of the parent rule folder.
    AND czm.model_id = t2.model_id
    AND EXISTS (
        SELECT
            1
        FROM
            czr_t
        WHERE
                czr_t.rule_id = czr.rule_id
            AND czr_t.max_start_model_version = czr.start_model_version
    )

configuratorrulesreport #pdh #oraclefusion #rules