--Temp table for on_behalf
Select customer_id, oba.member_id as user_id, ob.member_id as salesman_id Into #on_behalf from on_behalf_access_customer obc
Inner Join on_behalf_access oba On oba.access_id = obc.access_id
Inner Join on_behalf ob On ob.on_behalf_id = obc.on_behalf_id Where oba.member_id = '8641a786a4384cd8a318a9d723de95ba' and (GetDate() between ob.start_date and ob.end_date)
declare @orgMasterAccBilling varchar(10)
select top 1 @orgMasterAccBilling = UPPER(master_acc_billing) from org where org_id = 'Snekku'

--Select 
--		 CASE status WHEN 'Active' THEN 'Insert/Update'
--		 WHEN 'Inactive' THEN 'Delete' END as action, 
--		  credit_note_header_id as id, customer_id as customer_id, credit_note_no as docNo, docDate,  cast(currency_value as decimal(18,2))  as docAmount,  cast(credit_note_balance as decimal(18,2))  as unpaid, cast(non_product as int) as nonProduct
--		 from(
--			SELECT  ROW_NUMBER() OVER (ORDER BY credit_note_header_id) AS TotalRowNumber, customer_id,
--			customer_code, status, currency_value, credit_note_balance, docDate,credit_note_header_id,credit_note_no, non_product from(
--				SELECT cn.credit_note_header_id
--				, CASE WHEN @orgMasterAccBilling = 'YES' THEN (CASE WHEN ISNULL(c.master_account, '') = '' THEN c.customer_id ELSE  c.master_account END)
--			ELSE c.customer_id end as customer_id
--			, CASE WHEN @orgMasterAccBilling = 'YES' THEN (CASE WHEN ISNULL(c.master_account, '') = '' THEN c.customer_code ELSE  mc.customer_code END)
--			ELSE c.customer_code end as customer_code
--				, 'Active' as status, cn.currency_value, cn.credit_note_balance, cn.credit_note_date as docDate,credit_note_no, '0' as non_product
--				FROM txn_credit_note_history_header cn
--				left JOIN  customer_to_user_relation crr on crr.customer_id = cn.customer_id
--				and convert(date,getdate()) between start_date and end_date
--				left JOIN customer c on c.customer_id = cn.customer_id
--				left join customer mc on mc.customer_id = c.master_account
--				where crr.user_id = '8641a786a4384cd8a318a9d723de95ba' and cn.tenant_ID = 'Snekku' and cn.updated_date > '2024-05-10 14:15:05.493' and cn.status <> 'Draft'
	
--				UNION
--				SELECT cnn.credit_note_header_id
--				, CASE WHEN @orgMasterAccBilling = 'YES' THEN (CASE WHEN ISNULL(c.master_account, '') = '' THEN c.customer_id ELSE  c.master_account END)
--			ELSE c.customer_id end as customer_id
--			, CASE WHEN @orgMasterAccBilling = 'YES' THEN (CASE WHEN ISNULL(c.master_account, '') = '' THEN c.customer_code ELSE  mc.customer_code END)
--			ELSE c.customer_code end as customer_code
--				, 'Inactive' as status, cnn.currency_value, cnn.credit_note_balance, cnn.credit_note_date as docDate,credit_note_no, '0' as non_product
--				FROM txn_credit_note_history_header_at cnn
--				left JOIN customer c on c.customer_id = cnn.customer_id
--				left join customer mc on mc.customer_id = c.master_account
--				left JOIN customer_to_user_relation cr on cr.customer_id = cnn.customer_id
--				and convert(date,getdate()) between start_date and end_date
--				where  cnn.at_type = 'D' and cnn.tenant_ID = 'Snekku' and cnn.at_date > '2024-05-10 14:15:05.493' and cr.user_id = '8641a786a4384cd8a318a9d723de95ba'
--				and not exists(select 1 from txn_credit_note_history_header ccc where ccc.credit_note_header_id = cnn.credit_note_header_id)
--				Union
--				--Details for on behalf salesman
--				SELECT cn.credit_note_header_id, cn.customer_id, 
--				c.customer_code, 'Active' as status, cn.currency_value, cn.credit_note_balance, cn.credit_note_date as docDate,credit_note_no, '0' as non_product
--				FROM txn_credit_note_history_header cn
--				left JOIN customer c on c.customer_id = cn.customer_id
--				Where exists (Select 1 from #on_behalf obc Where obc.customer_id = cn.customer_id)
--				and cn.tenant_ID = 'Snekku' and cn.updated_date > '2024-05-10 14:15:05.493' and cn.status <> 'Draft'
	
--				UNION
--				SELECT cnn.credit_note_header_id, cnn.customer_id, 
--				c.customer_code, 'Inactive' as status, cnn.currency_value, cnn.credit_note_balance, cnn.credit_note_date as docDate,credit_note_no, '0' as non_product
--				FROM txn_credit_note_history_header_at cnn
--				left JOIN customer c on c.customer_id = cnn.customer_id
--				Where exists (Select 1 from #on_behalf obc Where obc.customer_id = cnn.customer_id)
--				and cnn.at_type = 'D' and cnn.tenant_ID = 'Snekku' and cnn.at_date > '2024-05-10 14:15:05.493'
--				and not exists(select 1 from txn_credit_note_history_header ccc where ccc.credit_note_header_id = cnn.credit_note_header_id)

--				--CN non product
--				UNION
--				SELECT cn.credit_note_id as credit_note_header_id, cn.customer_id, 
--				c.customer_code, 'Active' as status, cn.net_total as currency_value, cn.credit_note_balance, cn.credit_note_date as docDate,credit_note_no, '1' as non_product
--				FROM txn_credit_note_non_product_header cn
--				left JOIN  customer_to_user_relation crr on crr.customer_id = cn.customer_id
--				and convert(date,getdate()) between start_date and end_date
--				left JOIN customer c on c.customer_id = cn.customer_id
--				where crr.user_id = '8641a786a4384cd8a318a9d723de95ba' and cn.tenant_ID = 'Snekku' and cn.updated_date > '2024-05-10 14:15:05.493' and cn.status <> 'Draft'
	
--				UNION
--				SELECT cnn.credit_note_id as credit_note_header_id, cnn.customer_id, 
--				c.customer_code, 'Inactive' as status, cnn.net_total as currency_value, cnn.credit_note_balance, cnn.credit_note_date as docDate,credit_note_no, '1' as non_product
--				FROM txn_credit_note_non_product_header_at cnn
--				left JOIN customer c on c.customer_id = cnn.customer_id
--				left JOIN customer_to_user_relation cr on cr.customer_id = cnn.customer_id
--				and convert(date,getdate()) between start_date and end_date
--				where  cnn.at_type = 'D' and cnn.tenant_ID = 'Snekku' and cnn.at_date > '2024-05-10 14:15:05.493' and cr.user_id = '8641a786a4384cd8a318a9d723de95ba'
--				and not exists(select 1 from txn_credit_note_non_product_header ccc where ccc.credit_note_id = cnn.credit_note_id)
--				Union
--				--Details for on behalf salesman
--				SELECT cn.credit_note_id as credit_note_header_id, cn.customer_id, 
--				c.customer_code, 'Active' as status, cn.net_total as currency_value, cn.credit_note_balance, cn.credit_note_date as docDate,credit_note_no, '1' as non_product
--				FROM txn_credit_note_non_product_header cn
--				left JOIN customer c on c.customer_id = cn.customer_id
--				Where exists (Select 1 from #on_behalf obc Where obc.customer_id = cn.customer_id)
--				and cn.tenant_ID = 'Snekku' and cn.updated_date > '2024-05-10 14:15:05.493' and cn.status <> 'Draft'
	
--				UNION
--				SELECT cnn.credit_note_id as credit_note_header_id, cnn.customer_id, '',
--				c.customer_code, 'Inactive' as status, cnn.net_total as currency_value, cnn.credit_note_balance, cnn.credit_note_date as docDate,credit_note_no, '1' as non_product
--				FROM txn_credit_note_non_product_header_at cnn
--				left JOIN customer c on c.customer_id = cnn.customer_id
--				Where exists (Select 1 from #on_behalf obc Where obc.customer_id = cnn.customer_id)
--				and cnn.at_type = 'D' and cnn.tenant_ID = 'Snekku' and cnn.at_date > '2024-05-10 14:15:05.493'
--				and not exists(select 1 from txn_credit_note_non_product_header ccc where ccc.credit_note_id = cnn.credit_note_id)
--				)x  where Convert(varchar(10), docDate, 120) >= Convert(varchar(10), DATEADD(MONTH, -9, GetDate()), 120)
--		)v 
--		where TotalRowNumber between
--		 (1*9999999)+1-9999999 and 1 * 9999999;

--Drop table #on_behalf		  select * from sync_mobile_token where login_id= 'MC' order by created_date desc
--  select * from member where member_cd='MC' and org_id= 'snekku'
--  select * from txn_credit_note_history_header where customer_id='F80529B316074852B9116AAFE5938803' and credit_note_balance <>0 order by created_date desc




SELECT 
    CASE status 
        WHEN 'Active' THEN 'Insert/Update'
        WHEN 'Inactive' THEN 'Delete' 
    END as action, 
    credit_note_header_id as id, 
    customer_id as customer_id, 
    credit_note_no as docNo, 
    docDate,  
    cast(currency_value as decimal(18,2))  as docAmount,  
    cast(credit_note_balance as decimal(18,2))  as unpaid, 
    cast(non_product as int) as nonProduct
FROM (
    SELECT  
        ROW_NUMBER() OVER (ORDER BY credit_note_header_id) AS TotalRowNumber, 
        customer_id,
        customer_code, 
        status, 
        currency_value, 
        credit_note_balance, 
        docDate,
        credit_note_header_id,
        credit_note_no, 
        non_product 
    FROM (
        -- Active Credit Notes
        SELECT 
            cn.credit_note_header_id,
            CASE 
                WHEN @orgMasterAccBilling = 'YES' 
                THEN (CASE WHEN ISNULL(c.master_account, '') = '' THEN c.customer_id ELSE c.master_account END)
                ELSE c.customer_id 
            END as customer_id,
            CASE 
                WHEN @orgMasterAccBilling = 'YES' 
                THEN (CASE WHEN ISNULL(c.master_account, '') = '' THEN c.customer_code ELSE mc.customer_code END)
                ELSE c.customer_code 
            END as customer_code,
            'Active' as status, 
            cn.currency_value, 
            cn.credit_note_balance, 
            cn.credit_note_date as docDate,
            cn.credit_note_no, 
            '0' as non_product
        FROM txn_credit_note_history_header cn
        LEFT JOIN customer_to_user_relation crr ON crr.customer_id = cn.customer_id
            AND CONVERT(date, GETDATE()) BETWEEN start_date AND end_date
        LEFT JOIN customer c ON c.customer_id = cn.customer_id
        LEFT JOIN customer mc ON mc.customer_id = c.master_account
        WHERE 
            crr.user_id = '8641a786a4384cd8a318a9d723de95ba' 
            AND cn.tenant_ID = 'Snekku' 
            --AND cn.updated_date > '2024-05-10 14:15:05.493' 
            AND cn.status <> 'Draft'
            AND cn.credit_note_no = 'CN24/003134'

        UNION
        
        -- Inactive Credit Notes
        SELECT 
            cnn.credit_note_header_id,
            CASE 
                WHEN @orgMasterAccBilling = 'YES' 
                THEN (CASE WHEN ISNULL(c.master_account, '') = '' THEN c.customer_id ELSE c.master_account END)
                ELSE c.customer_id 
            END as customer_id,
            CASE 
                WHEN @orgMasterAccBilling = 'YES' 
                THEN (CASE WHEN ISNULL(c.master_account, '') = '' THEN c.customer_code ELSE mc.customer_code END)
                ELSE c.customer_code 
            END as customer_code,
            'Inactive' as status, 
            cnn.currency_value, 
            cnn.credit_note_balance, 
            cnn.credit_note_date as docDate,
            cnn.credit_note_no, 
            '0' as non_product
        FROM txn_credit_note_history_header_at cnn
        LEFT JOIN customer c ON c.customer_id = cnn.customer_id
        LEFT JOIN customer mc ON mc.customer_id = c.master_account
        LEFT JOIN customer_to_user_relation cr ON cr.customer_id = cnn.customer_id
            AND CONVERT(date, GETDATE()) BETWEEN start_date AND end_date
        WHERE  
            cnn.at_type = 'D' 
            AND cnn.tenant_ID = 'Snekku' 
           -- AND cnn.at_date > '2024-05-10 14:15:05.493' 
            AND cr.user_id = '8641a786a4384cd8a318a9d723de95ba'
            AND NOT EXISTS (
                SELECT 1 
                FROM txn_credit_note_history_header ccc 
                WHERE ccc.credit_note_header_id = cnn.credit_note_header_id
            )
            --AND cnn.credit_note_no = 'CN24/003134'

        UNION
        
        -- Active Details for on behalf salesman
        SELECT 
            cn.credit_note_header_id, 
            cn.customer_id, 
            c.customer_code, 
            'Active' as status, 
            cn.currency_value, 
            cn.credit_note_balance, 
            cn.credit_note_date as docDate,
            cn.credit_note_no, 
            '0' as non_product
        FROM txn_credit_note_history_header cn
        LEFT JOIN customer c ON c.customer_id = cn.customer_id
        WHERE EXISTS (
            SELECT 1 
            FROM #on_behalf obc 
            WHERE obc.customer_id = cn.customer_id
        )
        AND cn.tenant_ID = 'Snekku' 
        --AND cn.updated_date > '2024-05-10 14:15:05.493' 
        AND cn.status <> 'Draft'
        --AND cn.credit_note_no = 'CN24/003134'

        UNION
        
        -- Inactive Details for on behalf salesman
        SELECT 
            cnn.credit_note_header_id, 
            cnn.customer_id, 
            c.customer_code, 
            'Inactive' as status, 
            cnn.currency_value, 
            cnn.credit_note_balance, 
            cnn.credit_note_date as docDate,
            cnn.credit_note_no, 
            '0' as non_product
        FROM txn_credit_note_history_header_at cnn
        LEFT JOIN customer c ON c.customer_id = cnn.customer_id
        WHERE EXISTS (
            SELECT 1 
            FROM #on_behalf obc 
            WHERE obc.customer_id = cnn.customer_id
        )
        AND cnn.at_type = 'D' 
        AND cnn.tenant_ID = 'Snekku' 
       -- AND cnn.at_date > '2024-05-10 14:15:05.493'
        AND NOT EXISTS (
            SELECT 1 
            FROM txn_credit_note_history_header ccc 
            WHERE ccc.credit_note_header_id = cnn.credit_note_header_id
        )
        --AND cnn.credit_note_no = 'CN24/003134'

        UNION
        
        -- CN non product Active
        SELECT 
            cn.credit_note_id as credit_note_header_id, 
            cn.customer_id, 
            c.customer_code, 
            'Active' as status, 
            cn.net_total as currency_value, 
            cn.credit_note_balance, 
            cn.credit_note_date as docDate,
            cn.credit_note_no, 
            '1' as non_product
        FROM txn_credit_note_non_product_header cn
        LEFT JOIN customer_to_user_relation crr ON crr.customer_id = cn.customer_id
            AND CONVERT(date, GETDATE()) BETWEEN start_date AND end_date
        LEFT JOIN customer c ON c.customer_id = cn.customer_id
        WHERE 
            crr.user_id = '8641a786a4384cd8a318a9d723de95ba' 
            AND cn.tenant_ID = 'Snekku' 
            --AND cn.updated_date > '2024-05-10 14:15:05.493' 
            AND cn.status <> 'Draft'
            AND cn.credit_note_no = 'CN24/003134'

        UNION
        
        -- CN non product Inactive
        SELECT 
            cnn.credit_note_id as credit_note_header_id, 
            cnn.customer_id, 
            c.customer_code, 
            'Inactive' as status, 
            cnn.net_total as currency_value, 
            cnn.credit_note_balance, 
            cnn.credit_note_date as docDate,
            cnn.credit_note_no, 
            '1' as non_product
        FROM txn_credit_note_non_product_header_at cnn
        LEFT JOIN customer c ON c.customer_id = cnn.customer_id
        LEFT JOIN customer_to_user_relation cr ON cr.customer_id = cnn.customer_id
            AND CONVERT(date, GETDATE()) BETWEEN start_date AND end_date
        WHERE  
            cnn.at_type = 'D' 
            AND cnn.tenant_ID = 'Snekku' 
            --AND cnn.at_date > '2024-05-10 14:15:05.493' 
            AND cr.user_id = '8641a786a4384cd8a318a9d723de95ba'
            AND NOT EXISTS (
                SELECT 1 
                FROM txn_credit_note_non_product_header ccc 
                WHERE ccc.credit_note_id = cnn.credit_note_id
            )
            AND cnn.credit_note_no = 'CN24/003134'

        UNION
        
        -- CN non product on behalf salesman Active
        SELECT 
            cn.credit_note_id as credit_note_header_id, 
            cn.customer_id, 
            c.customer_code, 
            'Active' as status, 
            cn.net_total as currency_value, 
            cn.credit_note_balance, 
            cn.credit_note_date as docDate,
            cn.credit_note_no, 
            '1' as non_product
        FROM txn_credit_note_non_product_header cn
        LEFT JOIN customer c ON c.customer_id = cn.customer_id
        WHERE EXISTS (
            SELECT 1 
            FROM #on_behalf obc 
            WHERE obc.customer_id = cn.customer_id
        )
        AND cn.tenant_ID = 'Snekku' 
        --AND cn.updated_date > '2024-05-10 14:15:05.493' 
        AND cn.status <> 'Draft'
        AND cn.credit_note_no = 'CN24/003134'
 
        UNION
        
        -- CN non product on behalf salesman Inactive
        SELECT 
            cnn.credit_note_id as credit_note_header_id, 
            cnn.customer_id, 
            c.customer_code, 
            'Inactive' as status, 
            cnn.net_total as currency_value, 
            cnn.credit_note_balance, 
            cnn.credit_note_date as docDate,
            cnn.credit_note_no, 
            '1' as non_product
        FROM txn_credit_note_non_product_header_at cnn
        LEFT JOIN customer c ON c.customer_id = cnn.customer_id
        WHERE EXISTS (
            SELECT 1 
            FROM #on_behalf obc 
            WHERE obc.customer_id = cnn.customer_id
        )
        AND cnn.tenant_ID = 'Snekku' 
        --AND cnn.at_date > '2024-05-10 14:15:05.493'
        AND NOT EXISTS (
            SELECT 1 
            FROM txn_credit_note_non_product_header ccc 
            WHERE ccc.credit_note_id = cnn.credit_note_id
        )
        --AND cnn.credit_note_no = 'CN24/003134'
    ) AS Combined
) AS Final
WHERE TotalRowNumber BETWEEN 1 AND 999999
ORDER BY TotalRowNumber;

  select * from member where member_cd='admin' and org_id= 'snekku'
 --Drop table #on_behalf