CREATE PROCEDURE [dbo].[create_dim_resource_category]
AS
BEGIN
    DROP TABLE IF EXISTS dim_resource_category;
    create table dim_resource_category
    (
        category_id int not null,
        category_name varchar(255) not null,
        description text,
        type_id int null,
        department_id int null,
        is_career_portal bit default 0,
        is_billable bit default 0,
        bu_id int null
    )
    create index dim_resource_category_category_id
        on dim_resource_category (category_id);
    create index dim_resource_category_category_id
        on dim_resource_category (bu_id);
    INSERT INTO dim_resource_category
	SELECT
    resource_leasing_category_id,
    resource_leasing_category_name,
    resource_leasing_category_desc,
    type_id,
    department_id,
    is_career_portal,
    is_billable,
	bu_id
    FROM
    mis_resourceleasingcategory_master left join mis_bu_technology on mis_bu_technology.technology_id = mis_resourceleasingcategory_master.resource_leasing_category_id;;
     UPDATE last_updated_at_history set last_updated_at = getdate() where sp_name = 'create_dim_resource_category';

END;
go

