Datanory Production » Datanory - SNEKKU

--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