CREATE PROCEDURE [dbo].[fetch_previous_outstanding_amount_for_packages]
    @before nvarchar(max) = '2022-01-01',
    @sales_id nvarchar(max) = '',
    @region_type_id nvarchar(max) = '',
    @client_grade nvarchar(max) = '',
    @bu_id nvarchar(max) = '',
    @tech_id nvarchar(max) = '',
    @tech_type nvarchar(max) = 'pkg',
    @relationship_id nvarchar(max) = ''
AS
    declare @select_query nvarchar(max) = '';
    declare @whereCondn nvarchar(max) = '';
    declare @tech_id_column nvarchar(max) = 'pkg_tech_id'
    IF @tech_type = 'res'
        BEGIN
            SET @tech_id_column = 'res_tech_id'
        end
    IF @sales_id != ''
        BEGIN
            SET @whereCondn = @whereCondn + ' AND (actual_sales_person_id IN ('+@sales_id+') OR actual_shared_with IN ('+@sales_id+'))'
        end
    IF @region_type_id != ''
        BEGIN
            SET @whereCondn = @whereCondn + ' and region_type_id = '+@region_type_id
        end
    IF @client_grade != ''
        BEGIN
            SET @whereCondn = @whereCondn + ' and client_grade_id IN ('+@client_grade+')'
        end
    IF @relationship_id != ''
        BEGIN
            SET @whereCondn = @whereCondn + ' and relationship_id IN ('+@relationship_id+')'
        end
    IF @bu_id != ''
        BEGIN
            SET @whereCondn = @whereCondn +' and bu_id = '+@bu_id
        end
    IF @tech_id != '' and @tech_type = 'pkg'
        BEGIN
            SET @whereCondn = @whereCondn +' and pkg_tech_id IN ('+@tech_id+')'
        end
    IF @tech_id != '' and @tech_type = 'res'
        BEGIN
            SET @whereCondn = @whereCondn +' and res_tech_id IN ('+@tech_id+')'
        end
    SET @select_query = 'select sum(unbilled_amount) as unbilled, sum(billed_pending) as billed
from fact_package as fp
left join mis_bu_technology as bu_tech on bu_tech.technology_id = fp.' + @tech_id_column +
                        ' where fp.DateKey < '+CAST(dbo.convertDateToInt (@before) AS nvarchar) + ' '+ @whereCondn

   EXEC sp_executesql    @select_query;
go

