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;

 --Drop table #on_behalf