Exists in source, missing in target

Exists in source, missing in target

Exists in source, missing in target

Exists in source, missing in target

Exists in source, missing in target

CREATE OR REPLACE FUNCTION public.get_leave_records_with_balance(p_company_id uuid, p_fin_year_id integer)
 RETURNS TABLE(leave_record_id uuid, employee_id uuid, employee_name text, manager_id uuid, leave_type_id integer, leave_type_name text, start_date date, end_date date, reason text, leave_status_id integer, leave_status text, total_leaves integer, used_leaves integer, remaining_leaves integer)
 LANGUAGE plpgsql
AS $function$
DECLARE
    financial_year_start date := make_date(p_fin_year_id, 4, 1);
    financial_year_end date := make_date(p_fin_year_id + 1, 3, 31);
BEGIN
    RETURN QUERY
    SELECT 
        lr.id AS leave_record_id,
        lr.employee_id AS employee_id,
        CONCAT(e.first_name, ' ', e.last_name) AS employee_name,
        e.manager_id,                          -- ⬅️ Select manager_id
        lt.id AS leave_type_id,
        lt.name::TEXT AS leave_type_name, 
        lr.start_date,
        lr.end_date,
        lr.reason,
        ls.id AS leave_status_id,
        ls.name::TEXT AS leave_status, 
        COALESCE(elb.total_leaves, 0) AS total_leaves,
        COALESCE(elb.used_leaves, 0) AS used_leaves,
        COALESCE(elb.total_leaves - elb.used_leaves, 0) AS remaining_leaves
    FROM leave_records lr
    INNER JOIN employees e 
        ON lr.employee_id = e.id
    INNER JOIN leave_statuses ls 
        ON lr.leave_status_id = ls.id
    INNER JOIN leave_types lt 
        ON lr.leave_type_id = lt.id
    LEFT JOIN (
        SELECT 
            elb_inner.employee_id, 
            elb_inner.leave_type_id,
            elb_inner.total_leaves,
            elb_inner.used_leaves
        FROM (
            SELECT 
                elb.employee_id,
                elb.leave_type_id,
                elb.total_leaves,
                elb.used_leaves,
                ROW_NUMBER() OVER (
                    PARTITION BY elb.employee_id, elb.leave_type_id 
                    ORDER BY elb.id DESC
                ) AS rn
            FROM employee_leave_balances elb
        ) elb_inner
        WHERE elb_inner.rn = 1
    ) elb 
        ON lr.employee_id = elb.employee_id 
        AND lr.leave_type_id = elb.leave_type_id
    WHERE lr.company_id = p_company_id
      AND (lr.is_deleted IS NULL OR lr.is_deleted = FALSE)
      AND lr.start_date <= financial_year_end
      AND lr.end_date >= financial_year_start;
END;
$function$

CREATE OR REPLACE FUNCTION public.get_leave_records_with_balance_also_check_managerid(p_company_id uuid, p_fin_year_id integer, p_employee_id uuid)
 RETURNS TABLE(leave_record_id uuid, employee_id uuid, employee_name text, manager_id uuid, leave_type_id integer, leave_type_name text, start_date date, end_date date, reason text, leave_status_id integer, leave_status text, total_leaves integer, used_leaves integer, remaining_leaves integer, created_by uuid, modified_by uuid)
 LANGUAGE plpgsql
AS $function$
DECLARE
    financial_year_start date := make_date(p_fin_year_id, 4, 1);
    financial_year_end   date := make_date(p_fin_year_id + 1, 3, 31);
    is_manager           boolean := false;
BEGIN
    -- Is the given employee a manager in this company?
    SELECT EXISTS (
        SELECT 1
        FROM employees e
        WHERE e.manager_id = p_employee_id
          AND e.company_id = p_company_id
    ) INTO is_manager;

    RETURN QUERY
    SELECT 
        lr.id                                   AS leave_record_id,
        lr.employee_id                          AS employee_id,
        CONCAT(e.first_name, ' ', e.last_name)  AS employee_name,
        e.manager_id,
        lt.id                                    AS leave_type_id,
        lt.name::text                            AS leave_type_name, 
        lr.start_date,
        lr.end_date,
        lr.reason,
        ls.id                                    AS leave_status_id,
        ls.name::text                            AS leave_status, 
        COALESCE(elb.total_leaves, 0)            AS total_leaves,
        COALESCE(elb.used_leaves, 0)             AS used_leaves,
        COALESCE(elb.total_leaves - elb.used_leaves, 0) AS remaining_leaves,
        lr.created_by,                           
        lr.modified_by                          
    FROM leave_records lr
    INNER JOIN employees e       ON lr.employee_id = e.id
    INNER JOIN leave_statuses ls ON lr.leave_status_id = ls.id
    INNER JOIN leave_types lt    ON lr.leave_type_id = lt.id
    LEFT JOIN (
        SELECT 
            elb_inner.employee_id, 
            elb_inner.leave_type_id,
            elb_inner.total_leaves,
            elb_inner.used_leaves
        FROM (
            SELECT 
                elb.employee_id,
                elb.leave_type_id,
                elb.total_leaves,
                elb.used_leaves,
                ROW_NUMBER() OVER (
                    PARTITION BY elb.employee_id, elb.leave_type_id 
                    ORDER BY elb.id DESC
                ) AS rn
            FROM employee_leave_balances elb
        ) elb_inner
        WHERE elb_inner.rn = 1
    ) elb 
        ON lr.employee_id = elb.employee_id 
       AND lr.leave_type_id = elb.leave_type_id
    WHERE lr.company_id = p_company_id
      AND (lr.is_deleted IS NULL OR lr.is_deleted = FALSE)
      AND lr.start_date <= financial_year_end
      AND lr.end_date   >= financial_year_start
      AND (
          is_manager = TRUE
          OR lr.employee_id = p_employee_id
      );
END;
$function$

CREATE OR REPLACE FUNCTION public.get_employee_leave_summary(p_company_id uuid, p_fin_year integer)
 RETURNS TABLE(employee_id uuid, employee_name text, total_leaves integer, used_leave_count integer, pending_leave_count integer, remaining_leave integer)
 LANGUAGE plpgsql
AS $function$
BEGIN
    RETURN QUERY
    WITH leave_data AS (
        -- Gathering data related to each employee's leaves and counting leave requests
        SELECT
            elb.employee_id,
            CAST(SUM(elb.total_leaves) AS integer) AS total_leaves,  -- Sum of all leave types for the employee, cast to integer
            CAST(COALESCE(COUNT(CASE WHEN lr.leave_status_id = 2 THEN 1 END), 0) AS integer) AS used_leave_count,  -- Count of approved leave requests, cast to integer
            CAST(COALESCE(COUNT(CASE WHEN lr.leave_status_id = 1 THEN 1 END), 0) AS integer) AS pending_leave_count,  -- Count of pending leave requests, cast to integer
            CAST(SUM(elb.total_leaves) - COALESCE(COUNT(CASE WHEN lr.leave_status_id = 2 THEN 1 END), 0) AS integer) AS remaining_leave  -- Remaining leave (total - approved), cast to integer
        FROM employee_leave_balances elb
        LEFT JOIN leave_records lr
            ON lr.employee_id = elb.employee_id
            AND lr.leave_type_id = elb.leave_type_id
            AND lr.start_date >= make_date(p_fin_year, 1, 1)  -- Start date for the given financial year
            AND lr.end_date <= make_date(p_fin_year, 12, 31)  -- End date for the given financial year
        WHERE elb.company_id = p_company_id
        GROUP BY elb.employee_id
    ),
    employees AS (
        -- Getting the list of employees in the given company
        SELECT e.id AS employee_id, CONCAT(e.first_name, ' ', e.last_name) AS employee_name
        FROM employees e
        WHERE e.company_id = p_company_id
    )
    SELECT
        e.employee_id,
        e.employee_name,
        ld.total_leaves,
        ld.used_leave_count,
        ld.pending_leave_count,
        ld.remaining_leave
    FROM employees e
    LEFT JOIN leave_data ld ON e.employee_id = ld.employee_id
    ORDER BY e.employee_name;  -- Sorting by employee name
END;
$function$

CREATE OR REPLACE FUNCTION public.get_all_leave_records(p_company_id uuid)
 RETURNS TABLE(leave_record_id uuid, employee_id uuid, employee_name text, leave_type_id integer, start_date date, end_date date, reason text, leave_status_id integer, leave_status_name text)
 LANGUAGE plpgsql
AS $function$
BEGIN
    RETURN QUERY
    SELECT 
        lr.id AS leave_record_id,
        lr.employee_id,
        e.first_name || ' ' || e.last_name AS employee_name,
        lr.leave_type_id,
        lr.start_date,
        lr.end_date,
        lr.reason,
        lr.leave_status_id,
        ls.name::TEXT AS leave_status_name  -- Explicit casting to TEXT
    FROM leave_records lr
    JOIN employees e ON lr.employee_id = e.id
    JOIN leave_statuses ls ON lr.leave_status_id = ls.id
    WHERE lr.company_id = p_company_id;
END;
$function$

CREATE OR REPLACE FUNCTION public.grant_full_schema_access(p_schema text, p_user text)
 RETURNS void
 LANGUAGE plpgsql
AS $function$
DECLARE
    obj RECORD;
BEGIN
    -- Grant on tables
    FOR obj IN
        SELECT table_name
        FROM information_schema.tables
        WHERE table_schema = p_schema
          AND table_type = 'BASE TABLE'
    LOOP
        EXECUTE format('GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE %I.%I TO %I;', p_schema, obj.table_name, p_user);
        RAISE NOTICE 'GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE %.% TO %;', p_schema, obj.table_name, p_user;
    END LOOP;

    -- Grant on sequences (USAGE + SELECT + UPDATE: full coverage)
    FOR obj IN
        SELECT c.relname AS sequence_name
        FROM pg_class c
        JOIN pg_namespace n ON n.oid = c.relnamespace
        WHERE c.relkind = 'S'
          AND n.nspname = p_schema
    LOOP
        EXECUTE format('GRANT USAGE, SELECT, UPDATE ON SEQUENCE %I.%I TO %I;', p_schema, obj.sequence_name, p_user);
        RAISE NOTICE 'GRANT USAGE, SELECT, UPDATE ON SEQUENCE %.% TO %;', p_schema, obj.sequence_name, p_user;
    END LOOP;

    -- Grant on all functions (handles all argument types)
    FOR obj IN
        SELECT
            p.proname AS function_name,
            pg_get_function_identity_arguments(p.oid) AS args
        FROM
            pg_proc p
            JOIN pg_namespace n ON p.pronamespace = n.oid
        WHERE
            n.nspname = p_schema
            AND p.prokind = 'f' -- f = function
    LOOP
        EXECUTE format(
            'GRANT EXECUTE ON FUNCTION %I.%I(%s) TO %I;',
            p_schema, obj.function_name, obj.args, p_user
        );
        RAISE NOTICE 'GRANT EXECUTE ON FUNCTION %.%(%) TO %;', p_schema, obj.function_name, obj.args, p_user;
    END LOOP;

    -- Grant on all procedures (Postgres 11+)
    FOR obj IN
        SELECT
            p.proname AS procedure_name,
            pg_get_function_identity_arguments(p.oid) AS args
        FROM
            pg_proc p
            JOIN pg_namespace n ON p.pronamespace = n.oid
        WHERE
            n.nspname = p_schema
            AND p.prokind = 'p' -- p = procedure
    LOOP
        EXECUTE format(
            'GRANT EXECUTE ON PROCEDURE %I.%I(%s) TO %I;',
            p_schema, obj.procedure_name, obj.args, p_user
        );
        RAISE NOTICE 'GRANT EXECUTE ON PROCEDURE %.%(%) TO %;', p_schema, obj.procedure_name, obj.args, p_user;
    END LOOP;

END;
$function$

CREATE OR REPLACE FUNCTION public.update_payment_header_posted_fn(p_payment_header_id uuid, p_transaction_id bigint)
 RETURNS void
 LANGUAGE plpgsql
AS $function$
BEGIN
    UPDATE payment_headers
    SET is_posted = true,
        transaction_id = p_transaction_id,
        modified_on_utc = now()
    WHERE id = p_payment_header_id;
END;
$function$

CREATE OR REPLACE FUNCTION public.get_employee_leave_balance(p_employee_id uuid DEFAULT NULL::uuid)
 RETURNS TABLE(id uuid, employee_id uuid, employee_name text, leave_type_id integer, leave_type_name text, total_leaves integer, used_leaves integer, remaining_leaves integer)
 LANGUAGE plpgsql
AS $function$  
BEGIN  
    RETURN QUERY   
    SELECT   
        gen_random_uuid() AS id,  -- ✅ Auto-generate UUID for each row
        elb.employee_id,  
        CONCAT(e.first_name, ' ', e.last_name) AS employee_name,  
        elb.leave_type_id,   
        lt.name::TEXT AS leave_type_name,  
        COALESCE(elb.total_leaves, 0) AS total_leaves,  
        COALESCE(elb.used_leaves, 0) AS used_leaves,  
        COALESCE(elb.total_leaves - elb.used_leaves, 0) AS remaining_leaves  
    FROM employee_leave_balances elb  
    JOIN leave_types lt ON elb.leave_type_id = lt.id  
    JOIN employees e ON elb.employee_id = e.id  
    WHERE (p_employee_id IS NULL OR elb.employee_id = p_employee_id);
END;  
$function$

CREATE OR REPLACE FUNCTION public.get_holidays_with_user_names(in_year integer, in_company_id uuid)
 RETURNS TABLE(id integer, holiday_date date, name character varying, description text, created_on_utc timestamp without time zone, modified_on_utc timestamp without time zone, deleted_on_utc timestamp without time zone, is_deleted boolean, created_by_id uuid, created_by character varying, modified_by_id uuid, modified_by character varying, company_id uuid)
 LANGUAGE plpgsql
 STABLE
AS $function$
BEGIN
    RETURN QUERY
    SELECT
        h.id,
        h.holiday_date,
        h.name,
        h.description,
        h.created_on_utc,
        h.modified_on_utc,
        h.deleted_on_utc,
        h.is_deleted,
        h.created_by AS created_by_id,
        (u1.first_name || ' ' || u1.last_name)::varchar AS created_by,
        h.modified_by AS modified_by_id,
        (u2.first_name || ' ' || u2.last_name)::varchar AS modified_by,
        h.company_id
    FROM public.holidays h
    LEFT JOIN public.users u1 ON h.created_by = u1.id
    LEFT JOIN public.users u2 ON h.modified_by = u2.id
    WHERE
        h.holiday_date >= make_date(in_year, 1, 1)
        AND h.holiday_date < make_date(in_year + 1, 1, 1)
        AND h.company_id = in_company_id
        AND h.is_deleted = FALSE
    ORDER BY h.holiday_date;
END;
$function$

CREATE OR REPLACE FUNCTION public.update_payroll_status(p_company_id uuid, p_payroll_ids uuid[], p_target_status integer, p_modified_by uuid)
 RETURNS TABLE(payroll_id uuid, new_status text, error_msg text)
 LANGUAGE plpgsql
AS $function$
DECLARE
    v_exists boolean;
    v_current_status int;
    v_allowed boolean;
BEGIN
    FOREACH payroll_id IN ARRAY p_payroll_ids LOOP
        
        BEGIN
            --------------------------------------------------------
            -- 1. Check payroll exists
            --------------------------------------------------------
            SELECT EXISTS(
                SELECT 1
                FROM payroll_transaction_headers
                WHERE id = payroll_id
                  AND company_id = p_company_id
            ) INTO v_exists;

            IF NOT v_exists THEN
                RETURN QUERY SELECT payroll_id, NULL, 'Payroll not found';
                CONTINUE;
            END IF;

            --------------------------------------------------------
            -- 2. Get current status
            --------------------------------------------------------
            SELECT status_id INTO v_current_status
            FROM payroll_transaction_headers
            WHERE id = payroll_id;

            --------------------------------------------------------
            -- 3. Validate transition from workflow table
            --------------------------------------------------------
            SELECT EXISTS(
                SELECT 1
                FROM payroll_workflow
                WHERE company_id = p_company_id
                  AND status = v_current_status
                  AND next_status = p_target_status
                  AND is_enabled = TRUE
            ) INTO v_allowed;

            IF NOT v_allowed THEN
                RETURN QUERY 
                SELECT payroll_id, NULL, 
                       format('Transition not allowed: %s → %s', v_current_status, p_target_status);
                CONTINUE;
            END IF;

            --------------------------------------------------------
            -- 4. Update status
            --------------------------------------------------------
            UPDATE payroll_transaction_headers
            SET status_id = p_target_status,
                modified_by = p_modified_by,
                modified_on_utc = NOW()
            WHERE id = payroll_id;

            --------------------------------------------------------
            -- 5. Return readable status name
            --------------------------------------------------------
            RETURN QUERY
            SELECT 
                payroll_id,
                CASE p_target_status
                    WHEN 1 THEN 'DRAFT'
                    WHEN 2 THEN 'SUBMITTED'
                    WHEN 3 THEN 'APPROVED'
                    WHEN 4 THEN 'PAID'
                    WHEN 5 THEN 'REJECTED'
                    WHEN 6 THEN 'CANCELLED'
                    ELSE 'UNKNOWN'
                END,
                NULL;

        EXCEPTION WHEN OTHERS THEN
            RETURN QUERY SELECT payroll_id, NULL, 'Error: ' || SQLERRM;
        END;

    END LOOP;
END;
$function$

CREATE OR REPLACE PROCEDURE public.delete_payroll_and_payment_data(IN p_company_id uuid)
 LANGUAGE plpgsql
AS $procedure$
BEGIN
    -- Delete payment details associated with payment headers of the given company
    DELETE FROM public.payment_details
    WHERE payment_header_id IN (
        SELECT id FROM public.payment_headers WHERE company_id = p_company_id
    );

    -- Delete payment headers for the given company
    DELETE FROM public.payment_headers
    WHERE company_id = p_company_id;

    -- Delete payroll transaction details associated with payroll transaction headers of the given company
    DELETE FROM public.payroll_transaction_details
    WHERE payroll_transaction_id IN (
        SELECT id FROM public.payroll_transaction_headers WHERE company_id = p_company_id
    );

    -- Delete payroll transaction headers for the given company
    DELETE FROM public.payroll_transaction_headers
    WHERE company_id = p_company_id;

    -- Commit transaction
    COMMIT;
END;
$procedure$

CREATE OR REPLACE PROCEDURE public.clean_up_org_payroll(IN p_organization_id uuid)
 LANGUAGE plpgsql
AS $procedure$
DECLARE
    v_company_id uuid;
    v_employee_id uuid;
    v_payroll_configuration_header_id uuid;
    v_payroll_transaction_header_id uuid;
    v_payment_header_id uuid;
    v_timesheet_id uuid;
    v_timesheet_line_id uuid;
BEGIN
    FOR v_company_id IN SELECT id FROM companies WHERE organization_id = p_organization_id LOOP

        -- Employees and all related tables
        FOR v_employee_id IN SELECT id FROM employees WHERE company_id = v_company_id LOOP
            DELETE FROM benefits WHERE employee_id = v_employee_id;
            DELETE FROM deductions WHERE employee_id = v_employee_id;
            DELETE FROM loans WHERE employee_id = v_employee_id;
            DELETE FROM advance_payments WHERE employee_id = v_employee_id;
            DELETE FROM employee_bank_accounts WHERE employee_id = v_employee_id;
            DELETE FROM employee_upis WHERE employee_id = v_employee_id;
            DELETE FROM leave_applications WHERE employee_id = v_employee_id;
            DELETE FROM leave_entitlements WHERE employee_id = v_employee_id;
            DELETE FROM attendance WHERE employee_id = v_employee_id;
            DELETE FROM payroll_transaction_headers WHERE employee_id = v_employee_id;
            DELETE FROM payroll_configuration_headers WHERE employee_id = v_employee_id;
        END LOOP;

        -- Payroll configuration headers/details
        FOR v_payroll_configuration_header_id IN 
            SELECT id FROM payroll_configuration_headers WHERE company_id = v_company_id
        LOOP
            DELETE FROM payroll_configuration_details WHERE configuration_header_id = v_payroll_configuration_header_id;
        END LOOP;
        DELETE FROM payroll_configuration_headers WHERE company_id = v_company_id;

        -- Payroll transaction headers/details
        FOR v_payroll_transaction_header_id IN 
            SELECT id FROM payroll_transaction_headers WHERE company_id = v_company_id
        LOOP
            DELETE FROM payroll_transaction_details WHERE payroll_transaction_id = v_payroll_transaction_header_id;
        END LOOP;
        DELETE FROM payroll_transaction_headers WHERE company_id = v_company_id;

        -- Payment headers/details
        FOR v_payment_header_id IN 
            SELECT id FROM payment_headers WHERE company_id = v_company_id
        LOOP
            DELETE FROM payment_details WHERE payment_header_id = v_payment_header_id;
        END LOOP;
        DELETE FROM payment_headers WHERE company_id = v_company_id;

        DELETE FROM payment_header_ids WHERE company_id = v_company_id;
        DELETE FROM payroll_transaction_header_ids WHERE company_id = v_company_id;

        -- Leave/workflow/records
        DELETE FROM leave_workflow WHERE company_id = v_company_id;
        DELETE FROM holidays WHERE company_id = v_company_id;

        -- Timesheets and details
        FOR v_timesheet_id IN SELECT id FROM timesheets WHERE company_id = v_company_id LOOP
            FOR v_timesheet_line_id IN SELECT id FROM timesheet_lines WHERE timesheet_id = v_timesheet_id LOOP
                DELETE FROM timesheet_hours WHERE timesheet_line_id = v_timesheet_line_id;
            END LOOP;
            DELETE FROM timesheet_lines WHERE timesheet_id = v_timesheet_id;
        END LOOP;
        DELETE FROM timesheets WHERE company_id = v_company_id;

        -- Tasks/Projects
        DELETE FROM tasks WHERE company_id = v_company_id;
        DELETE FROM projects WHERE company_id = v_company_id;

        -- Workdays
        DELETE FROM workdays WHERE company_id = v_company_id;

        -- Users
        DELETE FROM users WHERE company_id = v_company_id;

        -- Employees (must come after all dependent deletes)
        DELETE FROM employees WHERE company_id = v_company_id;

        -- Companies
        DELETE FROM companies WHERE id = v_company_id;

        RAISE NOTICE 'Deleted payroll data for company_id %', v_company_id;
    END LOOP;

    -- Organization
    DELETE FROM organizations WHERE id = p_organization_id;

    RAISE NOTICE 'Deleted payroll data for organization_id %', p_organization_id;
END;
$procedure$

CREATE OR REPLACE PROCEDURE public.update_payment_header_posted(IN p_payment_header_id uuid, IN p_transaction_id bigint)
 LANGUAGE plpgsql
AS $procedure$
BEGIN
    UPDATE payment_headers
    SET is_posted = true,
        transaction_id = p_transaction_id,
        modified_on_utc = now()
    WHERE id = p_payment_header_id;
END;
$procedure$

CREATE OR REPLACE PROCEDURE public.insert_payment_headers_from_payroll(IN p_payment_header_id uuid, IN p_company_id uuid, IN p_payment_date timestamp without time zone, IN p_reference text, IN p_credit_account_id uuid, IN p_debit_account_id uuid, IN p_mode_of_payment text, IN p_created_by uuid, IN p_payroll_transaction_ids uuid[])
 LANGUAGE plpgsql
AS $procedure$
DECLARE
    v_total_paid_amount numeric := 0;                 
    v_serial_number integer := 1;                     
    v_payroll_record RECORD;
    v_payment_number varchar;
BEGIN
    v_payment_number := get_new_payment_number(
        p_company_id, 
        p_payment_date::date
    );  

    IF array_length(p_payroll_transaction_ids, 1) IS NULL THEN
        RAISE NOTICE 'No payroll transaction IDs provided.';
        RETURN;
    END IF;

    -- Insert into payment_headers
    INSERT INTO public.payment_headers (
        id,
        company_id,
        paid_date,
        paid_amount,
        tds_amount,
        advance_amount,
        description,
        credit_account_id,
        debit_account_id,
        mode_of_payment,          -- ✅ dynamic
        reference,
        payment_number,
        created_on_utc,
        created_by,
        is_deleted
    )
    VALUES (
        p_payment_header_id,
        p_company_id,
        p_payment_date,
        0,
        0,
        0,
        'Payment of salary',
        p_credit_account_id,
        p_debit_account_id,
        p_mode_of_payment,        -- ✅ using parameter
        p_reference,
        v_payment_number,
        NOW(),
        p_created_by,
        false
    );

    -- Insert payment details + update payroll transactions
    FOR v_payroll_record IN
        SELECT id, employee_id, net_pay
        FROM public.payroll_transaction_headers
        WHERE id = ANY(p_payroll_transaction_ids)
    LOOP
        INSERT INTO public.payment_details (
            id,
            payment_header_id,
            employee_id,
            paid_amount,
            serial_number,
            is_deleted
        )
        VALUES (
            gen_random_uuid(),
            p_payment_header_id,
            v_payroll_record.employee_id,
            v_payroll_record.net_pay,
            v_serial_number,
            false
        );

        UPDATE public.payroll_transaction_headers
        SET status_id = 2
        WHERE id = v_payroll_record.id;

        v_total_paid_amount := v_total_paid_amount + v_payroll_record.net_pay;
        v_serial_number := v_serial_number + 1;
    END LOOP;

    -- Update total paid amount
    UPDATE public.payment_headers
    SET paid_amount = v_total_paid_amount
    WHERE id = p_payment_header_id;

    RAISE NOTICE 
        'Payment Header % inserted with total paid amount %',
        p_payment_header_id,
        v_total_paid_amount;
END;
$procedure$