Datanory Production » Datanory - 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 (

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