CREATE PROCEDURE [dbo].[create_fact_demands]
AS
    BEGIN
        DROP TABLE IF EXISTS fact_demand;
        create table fact_demand
        (
            req_id int not null,
            group_id int not null,
            client_id int,
            created_on int not null,
            deleted_on int,
            status_updated_on int,
            dim_status_id tinyint,
            tech_id int,
            location_id tinyint,
            region_type_id tinyint,
            budget_range_id tinyint,
            client_type tinyint,
            is_replacement tinyint,
            bu_id int,
            city_id int,
            package_id int,
            pkg_start_date int,
            aging int,
            no_action tinyint,
            all_profile_closed tinyint,
            budget_type_id int,
            client_need_id tinyint,
            client_type_id tinyint,
            tech_vet_ful_prob_id tinyint,
            tech_vet_budg_id tinyint,
            tech_vet_done tinyint,
            channel_type_id tinyint,

        )
        create index fact_demand_req_id_index
            on fact_demand (req_id, created_on, status_updated_on)
        insert into fact_demand (req_id,
                                group_id,
                                client_id,
                                created_on,
                                deleted_on,
                                status_updated_on,
                                dim_status_id, 
                                tech_id,
                                location_id,
                                region_type_id,
                                budget_range_id,
                                client_type,
                                is_replacement,
                                bu_id,
                                city_id,
                                package_id,
                                pkg_start_date,
                                aging,
                                no_action,
                                all_profile_closed,
                                budget_type_id,
                                client_need_id,
                                client_type_id,
                                tech_vet_ful_prob_id,
                                tech_vet_budg_id,
                                tech_vet_done,
                                channel_type_id
                                )
        SELECT pm.req_id,
               pm.group_id,
               case when pm.client_id is not null then pm.client_id when pm.client_id is null then pkg.clientId end as client_id,
               convert(varchar,pm.created_on,112) as created_on,
               convert(varchar,pm.deleted_on,112) as deleted_on,
               convert(varchar,pm.status_changed_on,112) as status_updated_on,
               ds.status_id as dim_status_id, 
               pm.category_id as tech_id,
               pm.location_id,
               case when pm.currency_id = 24 then 1 else 2 end as region_type_id,
               case when pm.budget_type_text = 'B1' then 1 when pm.budget_type_text = 'B2' then 2 when pm.budget_type_text = 'B3' then 3 when pm.budget_type_text Like 'Budget Below B1%' then 4 when pm.budget_type_text Like 'Budget Above B3%' then 5
               end as budget_range_id,
               case when pm.client_status = 'client' or (pm.client_status is null and demand_category in (10,11)) then 1 else 2 end as client_type,
               case when demand_category in (10,11) then 1 else 0 end as is_replacement,
               bu.bu_id,
               city_id,
               new_package_id as package_id,
               case when psum.current_status = 10.2 then convert(varchar,stage5_date,112) else null end as pkg_start_date,
               case when pm.demand_status = 'Pending' then DATEDIFF(day, pm.created_on, getDate()) else DATEDIFF(day, pm.created_on, status_changed_on) end as aging,
               case when (select top 1 demand_id from rmg_pipeline_profile_summary where demand_id = pm.req_id) is null then 1 
               else 0 end as no_action,
               profile_closed.is_ended as all_profile_closed,
               bc.category_id as budget_type_id,
               cn.need_id as client_need_id,
               ctd.pc_order as client_type_id,
               vet_ful_prob.id as tech_vet_ful_prob_id,
               vet_budg_type.id as tech_vet_budg_id,
               case when cv.vetting_id is not null then 1 else 0 end as tech_vet_done,
               case when channel.req_id is not null and channel.category is null then 1 
               		when channel.req_id is not null and channel.category is not null then channel_type.id end as channel_type_id
        from mis_resource_requirements_pipeline_master as pm
        left join dim_demand_status ds on ds.status_name = pm.demand_status
        left join mis_bu_technology bu on bu.technology_id = pm.category_id
        left join rmg_pipeline_profile_summary as psum on psum.demand_id = pm.req_id and psum.current_status = 10.2
        left join dim_rmg_budget_categories bc on pm.budget_category_title = bc.category_title
        left join dim_rmg_client_need cn on cn.title = pm.client_need
        left join mis_ClientResourcePackage as pkg on pkg.resourcePackageId = pm.new_package_id
        left join rmg_client_master as cm on cm.mis_client_id = 
        (case when pm.client_id is not null then pm.client_id else pkg.clientId end)
        left join rmg_mis_client_vetting cv on cv.client_id = cm.client_id
        left join rmg_client_type_description as ctd on ctd.pc_id = cv.pc_id
        left join rmg_technical_vetting_form_ans as vet_ans_budget on vet_ans_budget.req_id = pm.req_id and vet_ans_budget.custom_id = 202
        left join rmg_templates_custom_fields_options as vet_ans_budget_opt on vet_ans_budget_opt.options_id = vet_ans_budget.options_id
        left join dim_rmg_vetting_budget_type as vet_budg_type on vet_budg_type.budget_type = vet_ans_budget_opt.options_title
        left join rmg_technical_vetting_form_ans as vet_ans_ful_prob on vet_ans_ful_prob.req_id = pm.req_id and vet_ans_ful_prob.custom_id = 201
        left join rmg_templates_custom_fields_options as vet_ans_ful_prob_opt on vet_ans_ful_prob_opt.options_id = vet_ans_ful_prob.options_id
        left join dim_rmg_vetting_fulfillment_probability as vet_ful_prob on vet_ful_prob.probability = vet_ans_ful_prob_opt.options_value
        left join (Select top 1 req_id,category from rmg_assigned_channel_spoc group by req_id,category) as channel on channel.req_id = pm.req_id
        left join dim_rmg_assigned_channel_type as channel_type on channel_type.channel_category = channel.category
        left join (Select demand_id, case when count(demand_id) = sum(is_ended) then 1 when demand_id is null then 1 else 0 end as is_ended
            from rmg_pipeline_profile_summary as sum
            group by demand_id) as profile_closed on profile_closed.demand_id = pm.req_id;
         UPDATE last_updated_at_history set last_updated_at = getdate() where sp_name = 'create_fact_demands';
 
    END;