CREATE PROCEDURE [dbo].[fetch_lost_invoices_by_month]
    @monthDate nvarchar(max) = '',
    @whereCondn nvarchar(max) = '',
    @salesId nvarchar(max) = ''
AS
    declare @select_query nvarchar(max) = '';
    SET @select_query = 'SELECT invoice_number, description, di.start_date,
       di.end_date, date_generated,
       (pending_amount / exchange_rate) as lost_amount,
        (invoice_amount / exchange_rate) as total_inr,
       dca.account_id,
       dca.account_name,
       dc.customer_id,
       dc.company_name,
       dcp.package_id,
       fcsh.sales_person_id,
       fcsh.shared_with,
       salesperson.display_name as salesperson_name,
       shared.display_name as shared_name, closed_on' +
                        ' from dim_lost_invoices as di' +
                        ' join dim_customer_accounts dca on di.mis_client_id = dca.account_id
join dim_customer dc on dc.customer_id = dca.customer_id
left join dim_customer_package dcp on dcp.crp_id = (select crp_id from fact_package where DateKey = dbo.convertDateToInt('''+@monthDate+''') and di.package_id = fact_package.parent_id)
' +
                        ' left join mis_bu_technology as pkg_bu on pkg_bu.technology_id = dcp.technology_id' +
                        ' left join mis_bu_technology as res_bu on res_bu.technology_id = dcp.res_tech_id
 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('''+@monthDate+'''))) as fcsh on fcsh.last = 1 and fcsh.customer_id = di.customer_id
left join dim_users as salesperson on salesperson.user_id = fcsh.sales_person_id
left join dim_users as shared on shared.user_id = fcsh.shared_with ';
    SET @select_query = @select_query + @whereCondn
    IF  @salesId != ''
        BEGIN
            SET @select_query = @select_query + ' AND ( fcsh.sales_person_id IN (' + @salesId + ') OR fcsh.shared_with IN ('+ @salesId+'))';
        END
    SET @select_query = @select_query + ' ORDER BY dc.company_name, dca.account_name'
    EXEC sp_executesql @select_query;
go

