CREATE PROCEDURE [dbo].[create_fact_package]
AS
BEGIN
    DROP TABLE IF EXISTS fact_package;
    create table fact_package
(
    DateKey                   int not null,
    crp_id                    int not null,
    parent_id                 int not null,
    inr_rate                  float null,
    active_calendar_days_in_month int null,
    billing_method_id         int,
    is_started                bit   default 0,
    is_actual_started                bit   default 0,
    is_discontinued           bit   default 0,
    is_actual_discontinued    bit   default 0,
    package_status_id         int,
    package_rate              money default 0,
    old_package_rate          money default 0,
    duration_in_months        int   default 0,
    duration_in_months_actual int   default 0,
    duration_since_started int   default 0,
    duration_since_started_actual int   default 0,
    region_type_id                 int,
    relationship_id int default 0,
    location_id               int,
    customer_id int,
    account_id                int not null,
    sales_type_id_original             int null,
    sales_type_id           int null,
    actual_sales_type_id int null,
    region_id int null,
    actual_sales_person_id int null,
    actual_shared_with int null,
    actual_leadgen_id int null,
    actual_temp_shared_with int null,
    actual_collection_user_id int null,
    actual_mentor_id int null,
    tl_id int null,
    inv_count_in_month int null default 0,
    actual_inv_count int null default 0,
    billed_amount money null,
    actual_billed_amount money null,
    billed_amount_by_month money null,
    billed_days int default 0,
    actual_billed_days int default 0,
    timesheet_count_in_month int null default 0,
    actual_timesheet_count int null default 0,
    unbilled_amount money null,
    actual_unbilled_amount money null,
    unbilled_days int default 0,
    actual_unbilled_days int default 0,
    ignored_timesheets int null default 0,
    ignored_timesheets_amt money null,
    ignore_from_outstanding bit default 0 ,
    expected_timesheets int null default 0,
    expected_timesheets_amt money null,
    billed_pending money null,
    billed_lost money null,
    client_grade_id int null,
    pkg_tech_id int null,
    res_tech_id int null,
    last_resource_id int null,
    last_resource_next_increment_date int null,
     last_resource_employee_level int null,
    no_of_packages int null,
    salary_days int null,
    res_salary money null,
    row_number int
    );
    create index fact_package_DateKey
        on fact_package (DateKey);
    create index fact_package_customer_id
on fact_package (customer_id);
    create CLUSTERED columnstore index cci_fact_package__index
	on fact_package;
create index fact_package_crp_id on fact_package (crp_id);
    DECLARE @CurrentMonth DATE = CAST('2005-01-01' AS DATE)
    DECLARE @EndMonth DATE = EOMONTH(CAST(CONVERT(numeric,YEAR(getdate()))+1 AS VARCHAR)+'-12-31')
    WHILE @CurrentMonth < @EndMonth
    BEGIN
        INSERT INTO fact_package
--         select * from(
--         SELECT
--            (YEAR(@CurrentMonth) * 10000 + MONTH(@CurrentMonth) * 100 + DAY(@CurrentMonth)) AS DateKey,
--             p.crp_id,
--             p.parent_id,
--             p.billing_method_id,
--             CASE WHEN p.start_date between dbo.convertDateToInt(@CurrentMonth) and dbo.convertDateToInt(EOMONTH(@CurrentMonth)) THEN 1 ELSE 0 END as is_started,
--             CASE WHEN p.end_date between dbo.convertDateToInt(@CurrentMonth) and dbo.convertDateToInt(EOMONTH(@CurrentMonth)) THEN 1 ELSE 0 END as is_discontinued,
--             CASE WHEN p.actual_end_date between dbo.convertDateToInt(@CurrentMonth) and dbo.convertDateToInt(EOMONTH(@CurrentMonth)) THEN 1 ELSE 0 END as is_actual_discontinued,
--             p.package_status_id,
--             p.package_rate,
--             p.old_package_rate,
--             DATEDIFF(month,startDim.Date,case when end_date is null then getdate() else endDim.Date end) as duration_in_months,
--             DATEDIFF(month,startDim.Date,case when actual_end_date is null then getdate() else actualEndDim.Date end) as duration_in_months_actual,
--             DATEDIFF(month,startDim.Date,case when actualEndDim.Date < EOMONTH(@CurrentMonth) then actualEndDim.Date else EOMONTH(@CurrentMonth) END) as duration_since_started,
--             p.region_type_id,
--             p.location_id,
--            p.customer_id,
--             p.account_id,
--             p.sales_type_id,
--             NULL as actual_sales_type_id,
--             cus.region_id,
--            (SELECT top 1 user_id FROM mis_customer_sales_person_logs
--                 WHERE customer_id = p.customer_id and
--               (@CurrentMonth between from_date and iif(to_date is null, getdate(),to_date)
-- or EOMONTH(@CurrentMonth) between from_date and iif(to_date is null, getdate(),to_date)) order by sales_person_log_id desc) as actual_sales_person_id,
--             (SELECT top 1 user_id FROM mis_customer_shared_with_logs
--                 WHERE customer_id = p.customer_id and
--               (@CurrentMonth between from_date and iif(to_date is null, getdate(),to_date)
-- or EOMONTH(@CurrentMonth) between from_date and iif(to_date is null, getdate(),to_date)) order by shared_with_log_id desc) as actual_shared_with,
--             (SELECT top 1 user_id FROM mis_customer_leadgen_logs
--                 WHERE customer_id = p.customer_id and
--               (@CurrentMonth between from_date and iif(to_date is null, getdate(),to_date)
-- or EOMONTH(@CurrentMonth) between from_date and iif(to_date is null, getdate(),to_date)) order by leadgen_log_id desc) as actual_leadgen_id,
--                (SELECT top 1 user_id FROM mis_customer_temp_shared_with_logs
--                 WHERE customer_id = p.customer_id and
--               (@CurrentMonth between from_date and iif(to_date is null, getdate(),to_date)
-- or EOMONTH(@CurrentMonth) between from_date and iif(to_date is null, getdate(),to_date)) order by temp_shared_with_log_id desc) as actual_temp_shared_with,
--                (SELECT top 1 user_id FROM mis_customer_collection_user_logs
--                 WHERE customer_id = p.customer_id and
--               (@CurrentMonth between from_date and iif(to_date is null, getdate(),to_date)
-- or EOMONTH(@CurrentMonth) between from_date and iif(to_date is null, getdate(),to_date)) order by collection_log_id desc) as actual_collection_user_id,
--                 (SELECT top 1 user_id FROM mis_customer_mentor_logs
--                 WHERE customer_id = p.customer_id and
--               (@CurrentMonth between from_date and iif(to_date is null, getdate(),to_date)
-- or EOMONTH(@CurrentMonth) between from_date and iif(to_date is null, getdate(),to_date)) order by mentor_log_id desc) as actual_mentor_id,
--             (SELECT COUNT(id) FROM dim_invoices where start_date between dbo.convertDateToInt('2022-01-01') and dbo.convertDateToInt(EOMONTH('2022-01-01')) and p.parent_id = dim_invoices.package_id) as inv_count_in_month,
--                (SELECT COUNT(id) FROM dim_invoices where (start_date between dbo.convertDateToInt('2022-01-01') and dbo.convertDateToInt(EOMONTH('2022-01-01'))
--                    or end_date between dbo.convertDateToInt('2022-01-01') and dbo.convertDateToInt(EOMONTH('2022-01-01'))
--                    or dbo.convertDateToInt('2022-01-01') between start_date and end_date)
--                                                      and p.parent_id = dim_invoices.package_id) as actual_inv_count,
--                (SELECT SUM(total_inr) FROM dim_invoices where start_date between dbo.convertDateToInt('2022-01-01') and dbo.convertDateToInt(EOMONTH('2022-01-01')) and p.parent_id = dim_invoices.package_id) as billed_amount,
--                (SELECT SUM(((iif(dim_invoices.end_date > dbo.convertDateToInt(EOMONTH('2022-01-01')),dbo.convertDateToInt(EOMONTH('2022-01-01')),dim_invoices.end_date))-(iif(dim_invoices.start_date < dbo.convertDateToInt('2022-01-01'),dbo.convertDateToInt('2022-01-01'), dim_invoices.start_date))+1)*per_day_amount)
--                FROM dim_invoices where (start_date between dbo.convertDateToInt('2022-01-01') and dbo.convertDateToInt(EOMONTH('2022-01-01'))
--                    or end_date between dbo.convertDateToInt('2022-01-01') and dbo.convertDateToInt(EOMONTH('2022-01-01'))
--                    or dbo.convertDateToInt('2022-01-01') between start_date and end_date)
--                                                      and p.parent_id = dim_invoices.package_id) as actual_billed_amount,
--                (SELECT COUNT(timesheet_id) FROM dim_timesheets as t WHERE p.parent_id = t.crp_parent_id and timesheet_status_id IN (1,7) and t.is_deleted = 0 and
--                                                                           t.start_date between dbo.convertDateToInt('2022-01-01') and dbo.convertDateToInt(EOMONTH('2022-01-01'))) as timesheet_count_in_month,
--                (SELECT COUNT(timesheet_id) FROM dim_timesheets as t WHERE p.parent_id = t.crp_parent_id and timesheet_status_id IN (1,7) and t.is_deleted = 0 and
--                                                                           (t.start_date between dbo.convertDateToInt('2022-01-01') and dbo.convertDateToInt(EOMONTH('2022-01-01'))
--                    or t.end_date between dbo.convertDateToInt('2022-01-01') and dbo.convertDateToInt(EOMONTH('2022-01-01'))
--                    or dbo.convertDateToInt('2022-01-01') between t.start_date and t.end_date)
--                                                                           ) as actual_timesheet_count,
--                 (SELECT SUM(amount_after_adjustment * t.inr_rate) FROM dim_timesheets as t WHERE p.parent_id = t.crp_parent_id and timesheet_status_id IN (1,7) and t.is_deleted = 0 and
--                                                                           t.start_date between dbo.convertDateToInt('2022-01-01') and dbo.convertDateToInt(EOMONTH('2022-01-01'))) as unbilled_amount,
--                (SELECT
--                SUM(((iif(t.end_date > dbo.convertDateToInt(EOMONTH('2022-01-01')),dbo.convertDateToInt(EOMONTH('2022-01-01')),t.end_date))-(iif(t.start_date < dbo.convertDateToInt('2022-01-01'),dbo.convertDateToInt('2022-01-01'), t.start_date))+1)*per_day_amount)
--                FROM dim_timesheets as t WHERE p.parent_id = t.crp_parent_id and timesheet_status_id IN (1,7) and t.is_deleted = 0 and
--                                                                           (t.start_date between dbo.convertDateToInt('2022-01-01') and dbo.convertDateToInt(EOMONTH('2022-01-01'))
--                    or t.end_date between dbo.convertDateToInt('2022-01-01') and dbo.convertDateToInt(EOMONTH('2022-01-01'))
--                    or dbo.convertDateToInt('2022-01-01') between t.start_date and t.end_date)
--                                                                           ) as actual_unbilled_amount,
--
--             row_number() over ( partition by parent_id order by start_date desc) as row_number
--             from dim_customer_package as p
--             join dim_customer as cus on cus.customer_id = p.customer_id
--             join dim_date as startDim on startDim.DateKey = p.start_date
--             left join dim_date as endDim on endDim.DateKey = p.end_date
--             left join dim_date as actualEndDim on actualEndDim.DateKey = p.actual_end_date
--             where
--                     p.start_date <= dbo.convertDateToInt(EOMONTH(@CurrentMonth))
--                             and (p.end_date IS NULL OR end_date >= dbo.convertDateToInt(@CurrentMonth))
--             ) as pkg where row_number = 1;
        select DateKey,
               crp_id,
               parent_id,
               inr_rate,
               active_calendar_days_in_month,
               billing_method_id,
               is_started,
               is_actual_started,
               is_discontinued,
               is_actual_discontinued,
               package_status_id,
               package_rate,
               old_package_rate,
               duration_in_months,
               duration_in_months_actual,
               duration_since_started,
               duration_since_started_actual,
               region_type_id,
               relationship_id,
               location_id,
               customer_id,
               account_id,
               sales_type_id_original,
               sales_type_id,
               actual_sales_type_id,
               region_id,
               actual_sales_person_id,
               actual_shared_with,
               actual_leadgen_id,
               actual_temp_shared_with,
               actual_collection_user_id,
               actual_mentor_id,
               tl_id,
               inv_count_in_month,
               actual_inv_count,
               billed_amount,
               actual_billed_amount,
               billed_amount_by_month,
               billed_days,
               actual_billed_days,
               timesheet_count_in_month,
               actual_timesheet_count,
               iif(unbilled_amount is not null,unbilled_amount, 0),
               iif(actual_unbilled_amount is not null,actual_unbilled_amount, 0),
               unbilled_days,
               actual_unbilled_days,
               ignored_timesheets,
               ignored_timesheets_amt,
               ignore_from_outstanding,
               expected_timesheets,
               expected_timesheets_amt,
               billed_pending,
               billed_lost,
               client_grade_id,
               pkg_technology,
               res_technology,
               last_resource_id,
               last_resource_next_increment_date,
               last_resource_employee_level,
               no_of_packages,
               salary_days,
               res_salary,
               row_number
               from(
        SELECT
           (YEAR(@CurrentMonth) * 10000 + MONTH(@CurrentMonth) * 100 + DAY(@CurrentMonth)) AS DateKey,
            p.crp_id,
            p.parent_id,
            p.inr_rate,
            p.billing_method_id,
            (DATEDIFF(day,
                CASE WHEN p.start_date < dbo.convertDateToInt(@CurrentMonth) THEN @CurrentMonth ELSE CAST(CAST(p.start_date AS VARCHAR) AS DATE) END,
                CASE WHEN p.end_date IS NULL OR p.end_date > dbo.convertDateToInt(EOMONTH(@CurrentMonth)) THEN (EOMONTH(@CurrentMonth)) ELSE CAST(CAST(p.end_date AS VARCHAR) AS DATE) END
                ))+1 as active_calendar_days_in_month,
            (CASE WHEN p.new_start_date between dbo.convertDateToInt(@CurrentMonth) and dbo.convertDateToInt(EOMONTH(@CurrentMonth)) THEN 1 ELSE 0 END) as is_started,
            CASE WHEN (p.actual_start_date between dbo.convertDateToInt(@CurrentMonth) and dbo.convertDateToInt(EOMONTH(@CurrentMonth))) THEN 1 ELSE 0 END as is_actual_started,
            CASE WHEN p.end_date between dbo.convertDateToInt(@CurrentMonth) and dbo.convertDateToInt(EOMONTH(@CurrentMonth)) THEN 1 ELSE 0 END as is_discontinued,
            CASE WHEN p.actual_end_date between dbo.convertDateToInt(@CurrentMonth) and dbo.convertDateToInt(EOMONTH(@CurrentMonth)) THEN 1 ELSE 0 END as is_actual_discontinued,
            p.package_status_id,
            p.package_rate,
            p.old_package_rate,
            DATEDIFF(month,startDim.Date,case when end_date is null then getdate() else endDim.Date end) as duration_in_months,
            DATEDIFF(month,startDim.Date,case when actual_end_date is null then getdate() else actualEndDim.Date end) as duration_in_months_actual,
            DATEDIFF(month,actualStartDim.Date,case when actualEndDim.Date < EOMONTH(@CurrentMonth) then actualEndDim.Date else EOMONTH(@CurrentMonth) END) as duration_since_started,
            DATEDIFF(month,actualStartDim.Date,case when actual_end_date is null then getdate() else actualEndDim.Date end) as duration_since_started_actual,
            p.region_type_id,
               cus.relationship_id,
            p.location_id,
           p.customer_id,
            p.account_id,
               p.sales_type_id as sales_type_id_original,
            (CASE WHEN p.actual_start_date between dbo.convertDateToInt(@CurrentMonth) and dbo.convertDateToInt(EOMONTH(@CurrentMonth)) THEN p.sales_type_id ELSE 6 END) as sales_type_id ,
            NULL as actual_sales_type_id,
            cus.region_id,
            fcsh.sales_person_id as actual_sales_person_id,
               fcsh.shared_with as actual_shared_with,
               fcsh.leadgen_id as actual_leadgen_id,
               fcsh.temp_shared_with as actual_temp_shared_with,
               fcsh.collection_user_id as actual_collection_user_id,
               fcsh.mentor_id as actual_mentor_id,
               fcsh.tl_id,
            (SELECT COUNT(id) FROM dim_invoices where start_date between dbo.convertDateToInt(@CurrentMonth) and dbo.convertDateToInt(EOMONTH(@CurrentMonth)) and p.parent_id = dim_invoices.package_id) as inv_count_in_month,
               (SELECT COUNT(id) FROM dim_invoices where (start_date between dbo.convertDateToInt(@CurrentMonth) and dbo.convertDateToInt(EOMONTH(@CurrentMonth))
                   or end_date between dbo.convertDateToInt(@CurrentMonth) and dbo.convertDateToInt(EOMONTH(@CurrentMonth))
                   or dbo.convertDateToInt(@CurrentMonth) between start_date and end_date)
                                                     and p.parent_id = dim_invoices.package_id) as actual_inv_count,
               (SELECT SUM(amount_inr) FROM dim_invoices where start_date between dbo.convertDateToInt(@CurrentMonth) and dbo.convertDateToInt(EOMONTH(@CurrentMonth)) and p.parent_id = dim_invoices.package_id) as billed_amount,
               (SELECT SUM(((iif(dim_invoices.end_date > dbo.convertDateToInt(EOMONTH(@CurrentMonth)),dbo.convertDateToInt(EOMONTH(@CurrentMonth)),dim_invoices.end_date))-(iif(dim_invoices.start_date < dbo.convertDateToInt(@CurrentMonth),dbo.convertDateToInt(@CurrentMonth), dim_invoices.start_date))+1)*per_day_amount_without_tax)
               FROM dim_invoices where (start_date between dbo.convertDateToInt(@CurrentMonth) and dbo.convertDateToInt(EOMONTH(@CurrentMonth))
                   or end_date between dbo.convertDateToInt(@CurrentMonth) and dbo.convertDateToInt(EOMONTH(@CurrentMonth))
                   or dbo.convertDateToInt(@CurrentMonth) between start_date and end_date)
                                                     and p.parent_id = dim_invoices.package_id) as actual_billed_amount,
               (SELECT SUM(amount_inr) FROM dim_invoices where date_generated between dbo.convertDateToInt(@CurrentMonth) and dbo.convertDateToInt(EOMONTH(@CurrentMonth))
                   and p.parent_id = dim_invoices.package_id) billed_amount_by_month,
               (SELECT SUM(billed_days) FROM dim_invoices
               where start_date between dbo.convertDateToInt(@CurrentMonth) and dbo.convertDateToInt(EOMONTH(@CurrentMonth)) and p.parent_id = dim_invoices.package_id) as billed_days,
              (SELECT SUM(dbo.returnNumOfWorkingDays(iif(dim_invoices.start_date < dbo.convertDateToInt(@CurrentMonth),@CurrentMonth, CAST(CAST(dim_invoices.start_date AS VARCHAR) AS date)),iif(dim_invoices.end_date > dbo.convertDateToInt(EOMONTH(@CurrentMonth)),(EOMONTH(@CurrentMonth)),CAST(CAST(dim_invoices.end_date AS VARCHAR) AS date)) )) from dim_invoices
                  where (start_date between dbo.convertDateToInt(@CurrentMonth) and dbo.convertDateToInt(EOMONTH(@CurrentMonth))
                   or end_date between dbo.convertDateToInt(@CurrentMonth) and dbo.convertDateToInt(EOMONTH(@CurrentMonth))
                   or dbo.convertDateToInt(@CurrentMonth) between start_date and end_date)
                                                     and p.parent_id = dim_invoices.package_id
                  ) as actual_billed_days,

               (SELECT COUNT(timesheet_id) FROM dim_timesheets as t WHERE p.parent_id = t.crp_parent_id and timesheet_status_id IN (1,4,7) and t.is_deleted = 0 and
                                                                          t.start_date between dbo.convertDateToInt(@CurrentMonth) and dbo.convertDateToInt(EOMONTH(@CurrentMonth))) as timesheet_count_in_month,
               (SELECT COUNT(timesheet_id) FROM dim_timesheets as t WHERE p.parent_id = t.crp_parent_id and timesheet_status_id IN (2) and t.is_deleted = 0 and
                                                                          t.start_date <= dbo.convertDateToInt(EOMONTH(@CurrentMonth)) and end_date >= dbo.convertDateToInt((@CurrentMonth))) as ignored_timesheets,

               (SELECT COUNT(timesheet_id) FROM dim_timesheets as t WHERE p.parent_id = t.crp_parent_id and timesheet_status_id IN (1,4,7) and t.is_deleted = 0 and
                                                                          (t.start_date between dbo.convertDateToInt(@CurrentMonth) and dbo.convertDateToInt(EOMONTH(@CurrentMonth))
                   or t.end_date between dbo.convertDateToInt(@CurrentMonth) and dbo.convertDateToInt(EOMONTH(@CurrentMonth))
                   or dbo.convertDateToInt(@CurrentMonth) between t.start_date and t.end_date)
                                                                          ) as actual_timesheet_count,

                (SELECT SUM(amount_after_adjustment * t.inr_rate) FROM dim_timesheets as t WHERE p.parent_id = t.crp_parent_id and timesheet_status_id IN (1,4,7) and t.is_deleted = 0 and
                                                                          t.start_date between dbo.convertDateToInt(@CurrentMonth) and dbo.convertDateToInt(EOMONTH(@CurrentMonth))) as unbilled_amount,
                (SELECT SUM(amount_after_adjustment * t.inr_rate) FROM dim_timesheets as t WHERE p.parent_id = t.crp_parent_id and timesheet_status_id IN (2) and t.is_deleted = 0 and
                                                                          t.start_date <= dbo.convertDateToInt(EOMONTH(@CurrentMonth)) and end_date >= dbo.convertDateToInt((@CurrentMonth))) as ignored_timesheets_amt,
            (SELECT COUNT(timesheet_id) FROM dim_timesheets_expected as et WHERE p.parent_id = et.crp_parent_id and et.end_date >= dbo.convertDateToInt((@CurrentMonth)) and
                                                                          et.start_date <= dbo.convertDateToInt(EOMONTH(@CurrentMonth)) )   as expected_timesheets,
                (SELECT SUM(amount_after_adjustment * et.inr_rate) FROM dim_timesheets_expected as et WHERE p.parent_id = et.crp_parent_id and
                                                                          et.start_date <= dbo.convertDateToInt(EOMONTH(@CurrentMonth)) and end_date >= dbo.convertDateToInt((@CurrentMonth))) as expected_timesheets_amt,

               (SELECT
               SUM(((iif(t.end_date > dbo.convertDateToInt(EOMONTH(@CurrentMonth)),dbo.convertDateToInt(EOMONTH(@CurrentMonth)),t.end_date))-(iif(t.start_date < dbo.convertDateToInt(@CurrentMonth),dbo.convertDateToInt(@CurrentMonth), t.start_date))+1)*per_day_amount)
               FROM dim_timesheets as t WHERE p.parent_id = t.crp_parent_id and timesheet_status_id IN (1,4,7) and t.is_deleted = 0 and
                                                                          (t.start_date between dbo.convertDateToInt(@CurrentMonth) and dbo.convertDateToInt(EOMONTH(@CurrentMonth))
                   or t.end_date between dbo.convertDateToInt(@CurrentMonth) and dbo.convertDateToInt(EOMONTH(@CurrentMonth))
                   or dbo.convertDateToInt(@CurrentMonth) between t.start_date and t.end_date)
                                                                          ) as actual_unbilled_amount,
               (SELECT SUM(t.unbilled_days) FROM dim_timesheets as t WHERE p.parent_id = t.crp_parent_id and
                                                                          t.start_date between dbo.convertDateToInt(@CurrentMonth) and dbo.convertDateToInt(EOMONTH(@CurrentMonth)) and
                   timesheet_status_id IN (1,4,7) and t.is_deleted = 0) as unbilled_days,

               (SELECT SUM(dbo.returnNumOfWorkingDays(iif(t.start_date < dbo.convertDateToInt(@CurrentMonth),@CurrentMonth, CAST(CAST(t.start_date AS VARCHAR) AS date)),iif(t.end_date > dbo.convertDateToInt(EOMONTH(@CurrentMonth)),(EOMONTH(@CurrentMonth)),CAST(CAST(t.end_date AS VARCHAR) AS date)) ))
               FROM dim_timesheets as t WHERE p.parent_id = t.crp_parent_id and timesheet_status_id IN (1,4,7) and t.is_deleted = 0 and
                                                                          (t.start_date between dbo.convertDateToInt(@CurrentMonth) and dbo.convertDateToInt(EOMONTH(@CurrentMonth))
                   or t.end_date between dbo.convertDateToInt(@CurrentMonth) and dbo.convertDateToInt(EOMONTH(@CurrentMonth))
                   or dbo.convertDateToInt(@CurrentMonth) between t.start_date and t.end_date)
                                                                          ) as actual_unbilled_days,
            p.ignore_from_outstanding,
                (SELECT SUM(balance_inr) FROM dim_invoices where start_date between dbo.convertDateToInt(@CurrentMonth) and dbo.convertDateToInt(EOMONTH(@CurrentMonth)) and p.parent_id = dim_invoices.package_id) as billed_pending,
                (SELECT SUM(lost_amount) FROM dim_invoices where start_date between dbo.convertDateToInt(@CurrentMonth) and dbo.convertDateToInt(EOMONTH(@CurrentMonth)) and p.parent_id = dim_invoices.package_id) as billed_lost,
               cus.client_grade_id,
                p.technology_id as pkg_technology,
               p.res_tech_id as res_technology,
               p.user_id as last_resource_id,
               last_sal.next_increment as last_resource_next_increment_date,
               employee_level.id as last_resource_employee_level,
--                0 as res_salary,
               (select
       count(p1.crp_id)
               from dim_customer_package p1
join fact_user_salary as sal on sal.user_id = p1.user_id and sal.DateKey = dbo.convertDateToInt(@CurrentMonth)
where p1.parent_id = p.parent_id and
                             p1.start_date <= dbo.convertDateToInt(EOMONTH(@CurrentMonth))
                            and (p1.end_date IS NULL OR p1.end_date >= dbo.convertDateToInt(@CurrentMonth))
) as no_of_packages,
               (select
       SUM((datediff(day,case when start_date <= dbo.convertDateToInt(@CurrentMonth) then @CurrentMonth else CAST(CAST(start_date as varchar) as DATE) end,case when end_date is null or end_date > dbo.convertDateToInt(EOMONTH(@CurrentMonth)) then EOMONTH(@CurrentMonth) else CAST(CAST(end_date as varchar) as DATE) end )+1))
from dim_customer_package p1
join fact_user_salary as sal on sal.user_id = p1.user_id and sal.DateKey = dbo.convertDateToInt(@CurrentMonth)
where p1.parent_id = p.parent_id and
                             p1.start_date <= dbo.convertDateToInt(EOMONTH(@CurrentMonth))
                            and (p1.end_date IS NULL OR p1.end_date >= dbo.convertDateToInt(@CurrentMonth))
) as salary_days,
               (select
       SUM((datediff(day,case when start_date <= dbo.convertDateToInt(@CurrentMonth) then @CurrentMonth else CAST(CAST(start_date as varchar) as DATE) end,case when end_date is null or end_date > dbo.convertDateToInt(EOMONTH(@CurrentMonth)) then EOMONTH(@CurrentMonth) else CAST(CAST(end_date as varchar) as DATE) end )+1)*sal.per_day_salary_calendar*p1.resource_contribution_factor)
from dim_customer_package p1
join fact_user_salary as sal on sal.user_id = p1.user_id and sal.DateKey = dbo.convertDateToInt(@CurrentMonth)
where p1.parent_id = p.parent_id and
                             p1.start_date <= dbo.convertDateToInt(EOMONTH(@CurrentMonth))
                            and (p1.end_date IS NULL OR p1.end_date >= dbo.convertDateToInt(@CurrentMonth))
) as res_salary,
            row_number() over ( partition by parent_id order by start_date desc) as row_number
            from dim_customer_package as p
            join dim_customer as cus on cus.customer_id = p.customer_id
            left join fact_user_salary as last_sal on last_sal.user_id = p.user_id and last_sal.DateKey = dbo.convertDateToInt(@CurrentMonth)
            left join fact_employee_monthly_details as last_resource on last_resource.user_id = p.user_id and last_resource.DateKey = dbo.convertDateToInt(@CurrentMonth)
            left join dim_employee_experience_level as employee_level on employee_level.level = last_resource.employee_experience_level
            join dim_date as startDim on startDim.DateKey = p.start_date
            left join dim_date as endDim on endDim.DateKey = p.end_date
            left join dim_date as actualEndDim on actualEndDim.DateKey = p.actual_end_date
            left join dim_date as actualStartDim on actualStartDim.DateKey = p.actual_start_date
--              left join (select *,row_number() over ( partition by customer_id order by from_date desc) as last
--  from fact_customer_sales_history where DateKey <= dbo.convertDateToInt(EOMONTH(@CurrentMonth))) as fcsh on fcsh.last = 1 and fcsh.customer_id = p.customer_id
            left join fact_customer_sales_history fcsh on fcsh.history_id = (
                select top 1 fcsh1.history_id from fact_customer_sales_history as fcsh1 where fcsh1.customer_id = p.customer_id and fcsh1.DateKey <= dbo.convertDateToInt (EOMONTH(@CurrentMonth))
                order by fcsh1.DateKey desc
                )
            where
                    p.start_date <= dbo.convertDateToInt(EOMONTH(@CurrentMonth))
                            and (p.end_date IS NULL OR end_date >= dbo.convertDateToInt(@CurrentMonth))
            ) as pkg where row_number = 1;

        SET @CurrentMonth = DATEADD(MONTH , 1, @CurrentMonth)
    END
    update fact_package
    set actual_sales_type_id = (case when duration_since_started <= 6 then sales_type_id_original else 4 end);

     UPDATE last_updated_at_history set last_updated_at = getdate() where sp_name = 'create_fact_package';
    --update fact_package
    --set actual_sales_type_id = (case when duration_in_months_actual >= 1 then
    --(CASE WHEN duration_since_started <= 6 THEN 2 ELSE 4 END)
    --else 5 END);
--WHERE sales_type_id != 3;
END;
go

