CREATE OR REPLACE FORCE VIEW "COMPL_ANL"."RPT_ACTION_ITEM_CLASS_TOOL" ("ACTION_ITEM_ID", "SOURCE_COUNTRY", "YEAR", "QUARTER", "AREA", "ANALYSIS", "OBSERVATIONS", "CONCLUSIONS", "OWNER", "DATE_OPENED", "DUE_DATE", "DATE_CLOSED", "REMEDIATION", "KEY_INFORMATION", "CCMS_ID", "CREATED_BY_ISID", "CLOSED_BY_ISID", "TABLE_NAME", "MARKING", "STATUS", "ACTION_ITEM_TIMESTAMP", "EXPLANATION", "SPECIFIC", "DETAILED", "REVIEW_FLAG_LEVEL1", "REVIEW_FLAG_LEVEL2", "FOLLOWUPCONDUCTED", "REMEDIATIONWELLCLASSIFIED", "WELLCLASSIFIED", "EMPLOYEEDISCIPLINE", "CLOSED_BY_USERBAND", "CREATED_BY_USERBAND", "COMMENTS") AS
  ( SELECT
        k.action_item_id,
        k.source_country,
        k.YEAR,
        k.quarter,
        k.area,
        k.analysis,
        k.observations,
        k.conclusions,
        k.owner,
        k.date_opened,
        k.due_date,
        k.date_closed,
        k.remediation,
        k.key_information,
        k.ccms_id,
        k.created_by_isid,
        k.closed_by_isid,
        k.TABLE_NAME,
        k.marking,
        k.STATUS,
        k.action_item_timestamp,
        k.explanation,
        k.specific,
        k.detailed,
        k.review_flag_level1,
        k.review_flag_level2,
        k.followupconducted,
        k.remediationwellclassified,
        k.wellclassified,
        k.employeediscipline,
        k.closed_by_userband,
        k.created_by_userband,
        k.COMMENTS
      FROM
        (
            SELECT
                c.*,
                d.closed_by_userband
            FROM
                (
                    SELECT
                        a.*,
                        b.created_by_userband
                    FROM
                        cmpl_action_item_summary_class a
                        LEFT JOIN (
                            SELECT
                                isid,
                                MAX(date_of_extraction),
                                MAX(band) AS created_by_userband
                            FROM
                                raw_htr_data
                            WHERE
                                employment_status_text = 'Active'
                            GROUP BY
                                isid
                        ) b ON a.created_by_isid = b.isid
                ) c
                LEFT JOIN (
                    SELECT
                        isid,
                        MAX(date_of_extraction),
                        MAX(band) AS closed_by_userband
                    FROM
                        raw_htr_data
                    WHERE
                        employment_status_text = 'Active'
                    GROUP BY
                        isid
                ) d ON c.closed_by_isid = d.isid
        ) k
    );