| Type | Name | Status | PK | FK | Columns | Index | Script | Diff Script |
|---|---|---|---|---|---|---|---|---|
| Table | payroll_workflow | Missing in Target |
|
|||||
| Table | departments | Missing in Target |
|
|||||
| Table | companies | Missing in Target |
|
|||||
| Table | leave_records | Missing in Target |
|
|||||
| Table | leave_statuses | Missing in Target |
|
|||||
| Table | leave_workflow | Missing in Target |
|
|||||
| Table | payment_headers | Missing in Target |
|
|||||
| Table | payments | Missing in Target |
|
|||||
| Table | workdays | Missing in Target |
|
|||||
| Table | employee_leave_balances | Missing in Target |
|
|||||
| Table | banks | Missing in Target |
|
|||||
| Table | __EFMigrationsHistory | Missing in Target |
|
|||||
| Table | designations | Missing in Target |
|
|||||
| Table | benefits | Missing in Target |
|
|||||
| Table | addresses | Missing in Target |
|
|||||
| Table | advance_payments | Missing in Target |
|
|||||
| Table | bank_accounts | Missing in Target |
|
|||||
| Table | cities | Missing in Target |
|
|||||
| Table | countries | Missing in Target |
|
|||||
| Table | component_types | Missing in Target |
|
|||||
| Table | deductions | Missing in Target |
|
|||||
| Table | attendance | Missing in Target |
|
|||||
| Table | leave_applications | Missing in Target |
|
|||||
| Table | loans | Missing in Target |
|
|||||
| Table | employees | Missing in Target |
|
|||||
| Table | payroll_transaction_header_ids | Missing in Target |
|
|||||
| Table | organizations | Missing in Target |
|
|||||
| Table | payroll_transaction_details | Missing in Target |
|
|||||
| Table | payment_header_ids | Missing in Target |
|
|||||
| Table | timesheet_statuses | Missing in Target |
|
|||||
| Table | sub_projects | Missing in Target |
|
|||||
| Table | timesheet_lines | Missing in Target |
|
|||||
| Table | upis | Missing in Target |
|
|||||
| Table | users | Missing in Target |
|
|||||
| Table | employee_bank_accounts | Missing in Target |
|
|||||
| Table | employee_upis | Missing in Target |
|
|||||
| Table | holidays | Missing in Target |
|
|||||
| Table | leave_types | Missing in Target |
|
|||||
| Table | payment_details | Missing in Target |
|
|||||
| Table | payroll_configuration_headers | Missing in Target |
|
|||||
| Table | statuses | Missing in Target |
|
|||||
| Table | timesheet_hours | Missing in Target |
|
|||||
| Table | leave_entitlements | Missing in Target |
|
|||||
| Table | payroll_transaction_headers | Missing in Target |
|
|||||
| Table | payroll_components | Missing in Target |
|
|||||
| Table | projects | Missing in Target |
|
|||||
| Table | tasks | Missing in Target |
|
|||||
| Table | timesheets | Missing in Target |
|
|||||
| Table | payroll_configuration_details | Missing in Target |
|
|||||
| Table | states | Missing in Target |
|
|||||
| Function | delete_payroll_configuration | Missing in Target |
Source Script
Target Script
1
CREATE OR REPLACE FUNCTION public.delete_payroll_configuration(employee_id_param uuid)
2
RETURNS void
3
LANGUAGE plpgsql
4
AS $function$
5
BEGIN
6
-- Step 1: Delete all details associated with the header(s) for the employee
7
DELETE FROM public.payroll_configuration_details
8
WHERE configuration_header_id IN (
9
SELECT id
10
FROM public.payroll_configuration_headers
11
WHERE employee_id = employee_id_param
12
);
13
14
-- Step 2: Delete all headers for the employee
15
DELETE FROM public.payroll_configuration_headers
16
WHERE employee_id = employee_id_param;
17
END;
18
$function$
|
|||||
| Function | delete_payroll_data | Missing in Target |
Source Script
Target Script
1
CREATE OR REPLACE FUNCTION public.delete_payroll_data(p_employee_id uuid)
2
RETURNS void
3
LANGUAGE plpgsql
4
AS $function$
5
BEGIN
6
-- First, delete the details associated with the employee's payroll transactions
7
DELETE FROM public.payroll_transaction_details
8
WHERE payroll_transaction_id IN (
9
SELECT id FROM public.payroll_transaction_headers
10
WHERE employee_id = p_employee_id
11
);
12
13
-- Then, delete the headers associated with the employee
14
DELETE FROM public.payroll_transaction_headers
15
WHERE employee_id = p_employee_id;
16
END;
17
$function$
|
|||||
| Function | get_all_employee_leave_report | Missing in Target |
Source Script
Target Script
1
CREATE OR REPLACE FUNCTION public.get_all_employee_leave_report(p_company_id uuid, p_year_id integer)
2
RETURNS TABLE(employee_id uuid, employee_name text, entitled_leaves integer, consumed_leaves integer, leave_request_in_pending integer, remaining_leaves integer)
3
LANGUAGE plpgsql
4
AS $function$
5
BEGIN
6
RETURN QUERY
7
SELECT
8
e.id AS employee_id,
9
CONCAT(e.first_name, ' ', e.last_name) AS employee_name,
10
COALESCE(SUM(le.entitled_leaves)::integer, 0) AS entitled_leaves,
11
COALESCE(SUM(le.consumed_leaves)::integer, 0) AS consumed_leaves,
12
COALESCE((SUM(le.entitled_leaves) - SUM(le.consumed_leaves))::integer, 0) AS leave_request_in_pending,
13
COALESCE((SUM(le.entitled_leaves) - SUM(le.consumed_leaves))::integer, 0) AS remaining_leaves
14
FROM leave_entitlements le
15
JOIN employees e ON le.employee_id = e.id
16
--JOIN employee_company ec ON ec.employee_id = e.id -- adjust as per your schema
17
WHERE le.company_id = p_company_id
18
AND le.leave_year = p_year_id
19
GROUP BY e.id, e.first_name, e.last_name
20
ORDER BY employee_name;
21
END;
22
$function$
|
|||||
| Function | get_leave_records_with_balance | Missing in Target |
Source Script
Target Script
1
CREATE OR REPLACE FUNCTION public.get_leave_records_with_balance(p_company_id uuid, p_fin_year_id integer)
2
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)
3
LANGUAGE plpgsql
4
AS $function$
5
DECLARE
6
financial_year_start date := make_date(p_fin_year_id, 4, 1);
7
financial_year_end date := make_date(p_fin_year_id + 1, 3, 31);
8
BEGIN
9
RETURN QUERY
10
SELECT
11
lr.id AS leave_record_id,
12
lr.employee_id AS employee_id,
13
CONCAT(e.first_name, ' ', e.last_name) AS employee_name,
14
e.manager_id, -- ⬅️ Select manager_id
15
lt.id AS leave_type_id,
16
lt.name::TEXT AS leave_type_name,
17
lr.start_date,
18
lr.end_date,
19
lr.reason,
20
ls.id AS leave_status_id,
21
ls.name::TEXT AS leave_status,
22
COALESCE(elb.total_leaves, 0) AS total_leaves,
23
COALESCE(elb.used_leaves, 0) AS used_leaves,
24
COALESCE(elb.total_leaves - elb.used_leaves, 0) AS remaining_leaves
25
FROM leave_records lr
26
INNER JOIN employees e
27
ON lr.employee_id = e.id
28
INNER JOIN leave_statuses ls
29
ON lr.leave_status_id = ls.id
30
INNER JOIN leave_types lt
31
ON lr.leave_type_id = lt.id
32
LEFT JOIN (
33
SELECT
34
elb_inner.employee_id,
35
elb_inner.leave_type_id,
36
elb_inner.total_leaves,
37
elb_inner.used_leaves
38
FROM (
39
SELECT
40
elb.employee_id,
41
elb.leave_type_id,
42
elb.total_leaves,
43
elb.used_leaves,
44
ROW_NUMBER() OVER (
45
PARTITION BY elb.employee_id, elb.leave_type_id
46
ORDER BY elb.id DESC
47
) AS rn
48
FROM employee_leave_balances elb
49
) elb_inner
50
WHERE elb_inner.rn = 1
51
) elb
52
ON lr.employee_id = elb.employee_id
53
AND lr.leave_type_id = elb.leave_type_id
54
WHERE lr.company_id = p_company_id
55
AND (lr.is_deleted IS NULL OR lr.is_deleted = FALSE)
56
AND lr.start_date <= financial_year_end
57
AND lr.end_date >= financial_year_start;
58
END;
59
$function$
|
|||||
| Function | get_payroll | Missing in Target |
Source Script
Target Script
1
CREATE OR REPLACE FUNCTION public.get_payroll(p_employee_id uuid, p_department_id integer, p_company_id uuid)
2
RETURNS TABLE(employee_id uuid, employee_name character varying, monthly_ctc numeric, addition numeric, deduction numeric, reimbursements numeric, gross_pay numeric)
3
LANGUAGE plpgsql
4
AS $function$
5
BEGIN
6
RETURN QUERY
7
SELECT
8
e.id AS employee_id,
9
(e.first_name || ' ' || e.last_name)::VARCHAR AS employee_name,
10
ROUND(COALESCE(SUM(CASE
11
WHEN ct.name = 'Basic Salary' THEN pc.amount / 12
12
ELSE 0 END)::NUMERIC, 0), 2) AS monthly_ctc,
13
ROUND(COALESCE(SUM(CASE
14
WHEN ct.name IN ('DA', 'HRA', 'Conveyance Allowance', 'Special Allowance', 'LTA', 'Medical Reimbursement') THEN pc.amount / 12
15
ELSE 0 END)::NUMERIC, 0), 2) AS addition,
16
ROUND(COALESCE(SUM(CASE
17
WHEN ct.name IN ('Professional Tax', 'Provident Fund (PF)', 'Gratuity') THEN pc.amount / 12
18
ELSE 0 END)::NUMERIC, 0), 2) AS deduction,
19
0::NUMERIC(15, 2) AS reimbursements, -- Placeholder for reimbursements if not available
20
ROUND((COALESCE(SUM(CASE
21
WHEN ct.name = 'Basic Salary' THEN pc.amount / 12
22
ELSE 0 END), 0)::NUMERIC) +
23
(COALESCE(SUM(CASE
24
WHEN ct.name IN ('DA', 'HRA', 'Conveyance Allowance', 'Special Allowance', 'LTA', 'Medical Reimbursement') THEN pc.amount / 12
25
ELSE 0 END), 0)::NUMERIC) -
26
(COALESCE(SUM(CASE
27
WHEN ct.name IN ('Professional Tax', 'Provident Fund (PF)', 'Gratuity') THEN pc.amount / 12
28
ELSE 0 END), 0)::NUMERIC), 2) AS gross_pay
29
FROM
30
employees e
31
LEFT JOIN
32
payroll_components pc ON e.id = pc.employee_id
33
LEFT JOIN
34
component_types ct ON pc.component_type_id = ct.id
35
WHERE
36
(p_employee_id IS NULL OR e.id = p_employee_id) AND
37
(p_department_id IS NULL OR e.department_id = p_department_id) AND
38
e.company_id = p_company_id AND
39
pc.is_deleted = FALSE AND
40
e.is_deleted = FALSE
41
GROUP BY
42
e.id, e.first_name, e.last_name;
43
END;
44
$function$
|
|||||
| Function | pg_get_tabledef | Missing in Target |
Source Script
Target Script
1
CREATE OR REPLACE FUNCTION public.pg_get_tabledef(p_table_name text)
2
RETURNS text
3
LANGUAGE plpgsql
4
AS $function$
5
DECLARE
6
col RECORD;
7
col_defs TEXT := '';
8
pk_cols TEXT := '';
9
result TEXT;
10
BEGIN
11
FOR col IN
12
SELECT
13
column_name,
14
data_type,
15
character_maximum_length,
16
numeric_precision,
17
numeric_scale,
18
is_nullable,
19
column_default
20
FROM information_schema.columns
21
WHERE table_schema = 'public' AND table_name = p_table_name
22
ORDER BY ordinal_position
23
LOOP
24
col_defs := col_defs ||
25
format('"%s" %s%s%s%s, ',
26
col.column_name,
27
CASE
28
WHEN col.data_type = 'character varying' THEN format('varchar(%s)', col.character_maximum_length)
29
WHEN col.data_type = 'numeric' THEN format('numeric(%s,%s)', col.numeric_precision, col.numeric_scale)
30
ELSE col.data_type
31
END,
32
CASE WHEN col.column_default IS NOT NULL THEN ' DEFAULT ' || col.column_default ELSE '' END,
33
CASE WHEN col.is_nullable = 'NO' THEN ' NOT NULL' ELSE '' END,
34
''
35
);
36
END LOOP;
37
38
-- Get primary key columns
39
SELECT string_agg(format('"%s"', kcu.column_name), ', ')
40
INTO pk_cols
41
FROM information_schema.table_constraints tc
42
JOIN information_schema.key_column_usage kcu
43
ON tc.constraint_name = kcu.constraint_name
44
WHERE tc.table_schema = 'public'
45
AND tc.table_name = p_table_name
46
AND tc.constraint_type = 'PRIMARY KEY';
47
48
IF pk_cols IS NOT NULL THEN
49
col_defs := col_defs || format('PRIMARY KEY (%s), ', pk_cols);
50
END IF;
51
52
col_defs := left(col_defs, length(col_defs) - 2);
53
result := format('CREATE TABLE "%s" (%s);', p_table_name, col_defs);
54
RETURN result;
55
END;
56
$function$
|
|||||
| Function | get_all_employee_leave_balances | Missing in Target |
Source Script
Target Script
1
CREATE OR REPLACE FUNCTION public.get_all_employee_leave_balances(p_employee_id uuid DEFAULT NULL::uuid, p_company_id uuid DEFAULT NULL::uuid)
2
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, company_id uuid)
3
LANGUAGE plpgsql
4
AS $function$
5
BEGIN
6
RETURN QUERY
7
SELECT
8
gen_random_uuid() AS id, -- ✅ Auto-generate UUID for each row
9
elb.employee_id,
10
CONCAT(e.first_name, ' ', e.last_name) AS employee_name,
11
elb.leave_type_id,
12
lt.name::TEXT AS leave_type_name,
13
COALESCE(elb.total_leaves, 0) AS total_leaves,
14
COALESCE(elb.used_leaves, 0) AS used_leaves,
15
COALESCE(elb.total_leaves - elb.used_leaves, 0) AS remaining_leaves,
16
elb.company_id -- ✅ Added company_id
17
FROM employee_leave_balances elb
18
JOIN leave_types lt ON elb.leave_type_id = lt.id
19
JOIN employees e ON elb.employee_id = e.id
20
WHERE (p_employee_id IS NULL OR elb.employee_id = p_employee_id)
21
AND (p_company_id IS NULL OR elb.company_id = p_company_id); -- ✅ Filter by company_id
22
END;
23
$function$
|
|||||
| Function | get_states_by_country | Missing in Target |
Source Script
Target Script
1
CREATE OR REPLACE FUNCTION public.get_states_by_country(p_country_id uuid)
2
RETURNS TABLE(id uuid, name text, country_id uuid)
3
LANGUAGE plpgsql
4
AS $function$
5
BEGIN
6
RETURN QUERY
7
SELECT
8
s.id,
9
s.name,
10
s.country_id
11
FROM states s
12
WHERE s.country_id = p_country_id;
13
END;
14
$function$
|
|||||
| Function | get_all_leave_applications | Missing in Target |
Source Script
Target Script
1
CREATE OR REPLACE FUNCTION public.get_all_leave_applications(p_company_id uuid)
2
RETURNS TABLE(leave_application_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)
3
LANGUAGE plpgsql
4
AS $function$
5
BEGIN
6
RETURN QUERY
7
SELECT
8
la.id AS leave_application_id,
9
la.employee_id,
10
e.first_name || ' ' || e.last_name AS employee_name,
11
la.leave_type_id,
12
la.start_date,
13
la.end_date,
14
la.reason,
15
la.leave_status_id,
16
ls.name::TEXT AS leave_status_name -- Explicit casting to TEXT
17
FROM leave_applications la
18
JOIN employees e ON la.employee_id = e.id
19
JOIN leave_statuses ls ON la.leave_status_id = ls.id
20
WHERE la.company_id = p_company_id;
21
END;
22
$function$
|
|||||
| Function | get_leave_records_with_balance_also_check_managerid | Missing in Target |
Source Script
Target Script
1
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)
2
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)
3
LANGUAGE plpgsql
4
AS $function$
5
DECLARE
6
financial_year_start date := make_date(p_fin_year_id, 4, 1);
7
financial_year_end date := make_date(p_fin_year_id + 1, 3, 31);
8
is_manager boolean := false;
9
BEGIN
10
-- Is the given employee a manager in this company?
11
SELECT EXISTS (
12
SELECT 1
13
FROM employees e
14
WHERE e.manager_id = p_employee_id
15
AND e.company_id = p_company_id
16
) INTO is_manager;
17
18
RETURN QUERY
19
SELECT
20
lr.id AS leave_record_id,
21
lr.employee_id AS employee_id,
22
CONCAT(e.first_name, ' ', e.last_name) AS employee_name,
23
e.manager_id,
24
lt.id AS leave_type_id,
25
lt.name::text AS leave_type_name,
26
lr.start_date,
27
lr.end_date,
28
lr.reason,
29
ls.id AS leave_status_id,
30
ls.name::text AS leave_status,
31
COALESCE(elb.total_leaves, 0) AS total_leaves,
32
COALESCE(elb.used_leaves, 0) AS used_leaves,
33
COALESCE(elb.total_leaves - elb.used_leaves, 0) AS remaining_leaves,
34
lr.created_by,
35
lr.modified_by
36
FROM leave_records lr
37
INNER JOIN employees e ON lr.employee_id = e.id
38
INNER JOIN leave_statuses ls ON lr.leave_status_id = ls.id
39
INNER JOIN leave_types lt ON lr.leave_type_id = lt.id
40
LEFT JOIN (
41
SELECT
42
elb_inner.employee_id,
43
elb_inner.leave_type_id,
44
elb_inner.total_leaves,
45
elb_inner.used_leaves
46
FROM (
47
SELECT
48
elb.employee_id,
49
elb.leave_type_id,
50
elb.total_leaves,
51
elb.used_leaves,
52
ROW_NUMBER() OVER (
53
PARTITION BY elb.employee_id, elb.leave_type_id
54
ORDER BY elb.id DESC
55
) AS rn
56
FROM employee_leave_balances elb
57
) elb_inner
58
WHERE elb_inner.rn = 1
59
) elb
60
ON lr.employee_id = elb.employee_id
61
AND lr.leave_type_id = elb.leave_type_id
62
WHERE lr.company_id = p_company_id
63
AND (lr.is_deleted IS NULL OR lr.is_deleted = FALSE)
64
AND lr.start_date <= financial_year_end
65
AND lr.end_date >= financial_year_start
66
AND (
67
is_manager = TRUE
68
OR lr.employee_id = p_employee_id
69
);
70
END;
71
$function$
|
|||||
| Function | get_all_payroll | Missing in Target |
Source Script
Target Script
1
CREATE OR REPLACE FUNCTION public.get_all_payroll(p_employee_id uuid, p_department_id integer, p_company_id uuid)
2
RETURNS TABLE(id uuid, employee_name character varying, basic_salary numeric, addition numeric, deduction numeric, net_pay numeric, components jsonb)
3
LANGUAGE plpgsql
4
AS $function$
5
BEGIN
6
RETURN QUERY
7
SELECT
8
e.id AS id,
9
(e.first_name || ' ' || e.last_name)::VARCHAR AS employee_name,
10
11
-- Basic salary (monthly) : sum of basic amounts divided by 12
12
ROUND(COALESCE(SUM(CASE WHEN ct.id = 1 THEN (pcd.amount / 12.0) ELSE 0 END), 0)::NUMERIC, 2) AS basic_salary,
13
14
-- Addition (monthly): DA, HRA, Conveyance, Special, LTA, Medical => divided by 12
15
ROUND(COALESCE(SUM(CASE WHEN ct.id IN (2,3,4,5,6,7) THEN (pcd.amount / 12.0) ELSE 0 END), 0)::NUMERIC, 2) AS addition,
16
17
-- Deduction (monthly): Professional Tax, PF => divided by 12
18
ROUND(COALESCE(SUM(CASE WHEN ct.id IN (8,9) THEN (pcd.amount / 12.0) ELSE 0 END), 0)::NUMERIC, 2) AS deduction,
19
20
-- Net Pay (monthly) = Basic(monthly) + Addition(monthly) - Deduction(monthly)
21
ROUND(
22
COALESCE(SUM(CASE WHEN ct.id = 1 THEN (pcd.amount / 12.0) ELSE 0 END), 0)
23
+
24
COALESCE(SUM(CASE WHEN ct.id IN (2,3,4,5,6,7) THEN (pcd.amount / 12.0) ELSE 0 END), 0)
25
-
26
COALESCE(SUM(CASE WHEN ct.id IN (9) THEN (pcd.amount / 12.0) ELSE 0 END), 0)
27
, 2) AS net_pay,
28
29
-- Components as JSONB with monthly amounts
30
jsonb_agg(
31
jsonb_build_object(
32
'componentTypeId', ct.id,
33
'componentTypeName', ct.name,
34
'amount', ROUND(COALESCE(pcd.amount, 0) / 12.0, 2)
35
) ORDER BY ct.id
36
) AS components
37
38
FROM public.employees e
39
LEFT JOIN public.payroll_configuration_headers pch ON e.id = pch.employee_id
40
LEFT JOIN public.payroll_configuration_details pcd ON pch.id = pcd.configuration_header_id
41
LEFT JOIN public.component_types ct ON pcd.component_type_id = ct.id
42
WHERE
43
(p_employee_id IS NULL OR e.id = p_employee_id) AND
44
(p_department_id IS NULL OR e.department_id = p_department_id) AND
45
pch.company_id = p_company_id AND
46
pch.is_deleted = FALSE AND
47
e.is_deleted = FALSE
48
GROUP BY
49
e.id, pch.employee_id, e.first_name, e.last_name;
50
END;
51
$function$
|
|||||
| Function | get_payroll_config | Missing in Target |
Source Script
Target Script
1
CREATE OR REPLACE FUNCTION public.get_payroll_config(p_payrolltransaction_id uuid)
2
RETURNS TABLE(id uuid, employee_id uuid, employee_code character varying, first_name character varying, last_name character varying, email character varying, pan character varying, uan character varying, phone_number character varying, date_of_joining date, department_name character varying, designation_name character varying, pay_period_start date, pay_period_end date, basic_salary numeric, allowances numeric, deductions numeric, net_pay numeric, payment_date date, payment_method_id integer, allowance_components json, deduction_components json)
3
LANGUAGE plpgsql
4
AS $function$
5
BEGIN
6
RETURN QUERY
7
SELECT
8
pth.id,
9
e.id AS employee_id,
10
e.employee_code,
11
e.first_name,
12
e.last_name,
13
e.email,
14
e.pan,
15
e.uan,
16
e.phone_number,
17
e.date_of_joining,
18
d.name AS department_name,
19
deg.name AS designation_name,
20
pth.pay_period_start,
21
pth.pay_period_end,
22
pth.basic_salary,
23
pth.allowances,
24
pth.deductions,
25
pth.net_pay,
26
pth.payment_date,
27
pth.payment_method_id,
28
-- JSON aggregation for AllowanceComponents
29
COALESCE(
30
json_agg(
31
jsonb_build_object(
32
'component_type_id', ptd.component_type_id,
33
'component_name', ct.name,
34
'component_amount', ptd.amount
35
)
36
) FILTER (
37
WHERE ct.is_allowance = true
38
AND ct.name IS NOT NULL
39
AND ptd.amount IS NOT NULL
40
),
41
'[]'
42
) AS allowance_components,
43
44
-- JSON aggregation for DeductionComponents
45
COALESCE(
46
json_agg(
47
jsonb_build_object(
48
'component_type_id', ptd.component_type_id,
49
'component_name', ct.name,
50
'component_amount', ptd.amount
51
)
52
) FILTER (
53
WHERE ct.is_allowance = false
54
AND ct.name IS NOT NULL
55
AND ptd.amount IS NOT NULL
56
),
57
'[]'
58
) AS deduction_components
59
60
FROM
61
employees e
62
LEFT JOIN payroll_transaction_headers pth
63
ON e.id = pth.employee_id
64
LEFT JOIN departments d
65
ON e.department_id = d.id
66
LEFT JOIN designations deg
67
ON e.designation_id = deg.id
68
LEFT JOIN payroll_transaction_details ptd
69
ON pth.id = ptd.payroll_transaction_id
70
LEFT JOIN component_types ct
71
ON ptd.component_type_id = ct.id
72
WHERE
73
pth.id = p_payrollTransaction_id
74
AND e.is_deleted = false
75
AND pth.is_deleted = false
76
GROUP BY
77
pth.id, e.id, e.employee_code, e.first_name, e.last_name,
78
e.email, e.pan, e.uan, e.phone_number, e.date_of_joining,
79
d.name, deg.name, pth.pay_period_start, pth.pay_period_end,
80
pth.basic_salary, pth.allowances, pth.deductions,
81
pth.net_pay, pth.payment_date, pth.payment_method_id;
82
END;
83
$function$
|
|||||
| Function | get_employee_leave_summary | Missing in Target |
Source Script
Target Script
1
CREATE OR REPLACE FUNCTION public.get_employee_leave_summary(p_company_id uuid, p_fin_year integer)
2
RETURNS TABLE(employee_id uuid, employee_name text, total_leaves integer, used_leave_count integer, pending_leave_count integer, remaining_leave integer)
3
LANGUAGE plpgsql
4
AS $function$
5
BEGIN
6
RETURN QUERY
7
WITH leave_data AS (
8
-- Gathering data related to each employee's leaves and counting leave requests
9
SELECT
10
elb.employee_id,
11
CAST(SUM(elb.total_leaves) AS integer) AS total_leaves, -- Sum of all leave types for the employee, cast to integer
12
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
13
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
14
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
15
FROM employee_leave_balances elb
16
LEFT JOIN leave_records lr
17
ON lr.employee_id = elb.employee_id
18
AND lr.leave_type_id = elb.leave_type_id
19
AND lr.start_date >= make_date(p_fin_year, 1, 1) -- Start date for the given financial year
20
AND lr.end_date <= make_date(p_fin_year, 12, 31) -- End date for the given financial year
21
WHERE elb.company_id = p_company_id
22
GROUP BY elb.employee_id
23
),
24
employees AS (
25
-- Getting the list of employees in the given company
26
SELECT e.id AS employee_id, CONCAT(e.first_name, ' ', e.last_name) AS employee_name
27
FROM employees e
28
WHERE e.company_id = p_company_id
29
)
30
SELECT
31
e.employee_id,
32
e.employee_name,
33
ld.total_leaves,
34
ld.used_leave_count,
35
ld.pending_leave_count,
36
ld.remaining_leave
37
FROM employees e
38
LEFT JOIN leave_data ld ON e.employee_id = ld.employee_id
39
ORDER BY e.employee_name; -- Sorting by employee name
40
END;
41
$function$
|
|||||
| Function | get_employee_leave_summarytemp | Missing in Target |
Source Script
Target Script
1
CREATE OR REPLACE FUNCTION public.get_employee_leave_summarytemp(p_company_id uuid, p_fin_year integer)
2
RETURNS TABLE(employee_id uuid, employee_name text, entitled_leaves integer, used_leave_count integer, pending_leave_count integer, remaining_leave integer)
3
LANGUAGE plpgsql
4
AS $function$
5
6
BEGIN
7
RETURN QUERY
8
WITH leave_data AS (
9
-- Gathering data related to each employee's leaves and counting leave requests
10
SELECT
11
le.employee_id,
12
CAST(SUM(le.entitled_leaves) AS integer) AS entitled_leaves, -- Sum of all leave types for the employee, cast to integer
13
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
14
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
15
CAST(SUM(le.entitled_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
16
FROM leave_entitlements le
17
LEFT JOIN leave_records lr
18
ON lr.employee_id = le.employee_id
19
AND lr.leave_type_id = le.leave_type_id
20
AND lr.start_date >= make_date(p_fin_year, 1, 1) -- Start date for the given financial year
21
AND lr.end_date <= make_date(p_fin_year, 12, 31) -- End date for the given financial year
22
WHERE le.company_id = p_company_id
23
GROUP BY le.employee_id
24
),
25
employees AS (
26
-- Getting the list of employees in the given company
27
SELECT e.id AS employee_id, CONCAT(e.first_name, ' ', e.last_name) AS employee_name
28
FROM employees e
29
WHERE e.company_id = p_company_id
30
)
31
SELECT
32
e.employee_id,
33
e.employee_name,
34
ld.entitled_leaves,
35
ld.used_leave_count,
36
ld.pending_leave_count,
37
ld.remaining_leave
38
FROM employees e
39
LEFT JOIN leave_data ld ON e.employee_id = ld.employee_id
40
ORDER BY e.employee_name; -- Sorting by employee name
41
END;
42
$function$
|
|||||
| Function | get_all_leave_records | Missing in Target |
Source Script
Target Script
1
CREATE OR REPLACE FUNCTION public.get_all_leave_records(p_company_id uuid)
2
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)
3
LANGUAGE plpgsql
4
AS $function$
5
BEGIN
6
RETURN QUERY
7
SELECT
8
lr.id AS leave_record_id,
9
lr.employee_id,
10
e.first_name || ' ' || e.last_name AS employee_name,
11
lr.leave_type_id,
12
lr.start_date,
13
lr.end_date,
14
lr.reason,
15
lr.leave_status_id,
16
ls.name::TEXT AS leave_status_name -- Explicit casting to TEXT
17
FROM leave_records lr
18
JOIN employees e ON lr.employee_id = e.id
19
JOIN leave_statuses ls ON lr.leave_status_id = ls.id
20
WHERE lr.company_id = p_company_id;
21
END;
22
$function$
|
|||||
| Function | grant_full_schema_access | Missing in Target |
Source Script
Target Script
1
CREATE OR REPLACE FUNCTION public.grant_full_schema_access(p_schema text, p_user text)
2
RETURNS void
3
LANGUAGE plpgsql
4
AS $function$
5
DECLARE
6
obj RECORD;
7
BEGIN
8
-- Grant on tables
9
FOR obj IN
10
SELECT table_name
11
FROM information_schema.tables
12
WHERE table_schema = p_schema
13
AND table_type = 'BASE TABLE'
14
LOOP
15
EXECUTE format('GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE %I.%I TO %I;', p_schema, obj.table_name, p_user);
16
RAISE NOTICE 'GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE %.% TO %;', p_schema, obj.table_name, p_user;
17
END LOOP;
18
19
-- Grant on sequences (USAGE + SELECT + UPDATE: full coverage)
20
FOR obj IN
21
SELECT c.relname AS sequence_name
22
FROM pg_class c
23
JOIN pg_namespace n ON n.oid = c.relnamespace
24
WHERE c.relkind = 'S'
25
AND n.nspname = p_schema
26
LOOP
27
EXECUTE format('GRANT USAGE, SELECT, UPDATE ON SEQUENCE %I.%I TO %I;', p_schema, obj.sequence_name, p_user);
28
RAISE NOTICE 'GRANT USAGE, SELECT, UPDATE ON SEQUENCE %.% TO %;', p_schema, obj.sequence_name, p_user;
29
END LOOP;
30
31
-- Grant on all functions (handles all argument types)
32
FOR obj IN
33
SELECT
34
p.proname AS function_name,
35
pg_get_function_identity_arguments(p.oid) AS args
36
FROM
37
pg_proc p
38
JOIN pg_namespace n ON p.pronamespace = n.oid
39
WHERE
40
n.nspname = p_schema
41
AND p.prokind = 'f' -- f = function
42
LOOP
43
EXECUTE format(
44
'GRANT EXECUTE ON FUNCTION %I.%I(%s) TO %I;',
45
p_schema, obj.function_name, obj.args, p_user
46
);
47
RAISE NOTICE 'GRANT EXECUTE ON FUNCTION %.%(%) TO %;', p_schema, obj.function_name, obj.args, p_user;
48
END LOOP;
49
50
-- Grant on all procedures (Postgres 11+)
51
FOR obj IN
52
SELECT
53
p.proname AS procedure_name,
54
pg_get_function_identity_arguments(p.oid) AS args
55
FROM
56
pg_proc p
57
JOIN pg_namespace n ON p.pronamespace = n.oid
58
WHERE
59
n.nspname = p_schema
60
AND p.prokind = 'p' -- p = procedure
61
LOOP
62
EXECUTE format(
63
'GRANT EXECUTE ON PROCEDURE %I.%I(%s) TO %I;',
64
p_schema, obj.procedure_name, obj.args, p_user
65
);
66
RAISE NOTICE 'GRANT EXECUTE ON PROCEDURE %.%(%) TO %;', p_schema, obj.procedure_name, obj.args, p_user;
67
END LOOP;
68
69
END;
70
$function$
|
|||||
| Function | update_payment_header_posted_fn | Missing in Target |
Source Script
Target Script
1
CREATE OR REPLACE FUNCTION public.update_payment_header_posted_fn(p_payment_header_id uuid, p_transaction_id bigint)
2
RETURNS void
3
LANGUAGE plpgsql
4
AS $function$
5
BEGIN
6
UPDATE payment_headers
7
SET is_posted = true,
8
transaction_id = p_transaction_id,
9
modified_on_utc = now()
10
WHERE id = p_payment_header_id;
11
END;
12
$function$
|
|||||
| Function | get_employee_leave_balance | Missing in Target |
Source Script
Target Script
1
CREATE OR REPLACE FUNCTION public.get_employee_leave_balance(p_employee_id uuid DEFAULT NULL::uuid)
2
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)
3
LANGUAGE plpgsql
4
AS $function$
5
BEGIN
6
RETURN QUERY
7
SELECT
8
gen_random_uuid() AS id, -- ✅ Auto-generate UUID for each row
9
elb.employee_id,
10
CONCAT(e.first_name, ' ', e.last_name) AS employee_name,
11
elb.leave_type_id,
12
lt.name::TEXT AS leave_type_name,
13
COALESCE(elb.total_leaves, 0) AS total_leaves,
14
COALESCE(elb.used_leaves, 0) AS used_leaves,
15
COALESCE(elb.total_leaves - elb.used_leaves, 0) AS remaining_leaves
16
FROM employee_leave_balances elb
17
JOIN leave_types lt ON elb.leave_type_id = lt.id
18
JOIN employees e ON elb.employee_id = e.id
19
WHERE (p_employee_id IS NULL OR elb.employee_id = p_employee_id);
20
END;
21
$function$
|
|||||
| Function | get_new_payment_number | Missing in Target |
Source Script
Target Script
1
CREATE OR REPLACE FUNCTION public.get_new_payment_number(p_company_id uuid, p_date date)
2
RETURNS character varying
3
LANGUAGE plpgsql
4
AS $function$
5
DECLARE
6
v_finance_year_id integer;
7
new_payment_id integer;
8
p_prefix varchar;
9
payment_number varchar;
10
BEGIN
11
-- Determine the start year of the financial year based on p_date
12
IF EXTRACT(MONTH FROM p_date) >= 4 THEN
13
v_finance_year_id := EXTRACT(YEAR FROM p_date);
14
ELSE
15
v_finance_year_id := EXTRACT(YEAR FROM p_date) - 1;
16
END IF;
17
18
-- Attempt to update; if nothing is updated, insert a new row
19
WITH x AS (
20
UPDATE public.payment_header_ids
21
SET last_payment_id = COALESCE(last_payment_id, 0) + 1
22
WHERE company_id = p_company_id AND fin_year = v_finance_year_id
23
RETURNING last_payment_id, payment_prefix
24
),
25
insert_x AS (
26
INSERT INTO public.payment_header_ids (
27
company_id, fin_year, payment_prefix, last_payment_id, payment_length
28
)
29
SELECT p_company_id, v_finance_year_id, 'PAY', 1, 8
30
WHERE NOT EXISTS (SELECT 1 FROM x)
31
RETURNING last_payment_id, payment_prefix
32
)
33
SELECT
34
COALESCE((SELECT last_payment_id FROM x LIMIT 1), (SELECT last_payment_id FROM insert_x LIMIT 1)),
35
COALESCE((SELECT payment_prefix FROM x LIMIT 1), (SELECT payment_prefix FROM insert_x LIMIT 1))
36
INTO new_payment_id, p_prefix;
37
38
-- Generate the final payment number
39
payment_number := p_prefix || LPAD(new_payment_id::text, 4, '0');
40
41
RETURN payment_number;
42
END;
43
$function$
|
|||||
| Function | get_payroll_transactions_by_company | Missing in Target |
Source Script
Target Script
1
CREATE OR REPLACE FUNCTION public.get_payroll_transactions_by_company(p_company_id uuid, p_financial_year_id integer)
2
RETURNS TABLE(payroll_id uuid, employee_name text, pay_period_start date, pay_period_end date, basic_salary numeric, allowances numeric, deductions numeric, net_pay numeric, payment_date date, status_name text, status_id integer, has_linked_account boolean)
3
LANGUAGE plpgsql
4
AS $function$
5
BEGIN
6
RETURN QUERY
7
SELECT
8
pth.id AS payroll_id,
9
e.first_name || ' ' || e.last_name AS employee_name,
10
pth.pay_period_start,
11
pth.pay_period_end,
12
pth.basic_salary,
13
pth.allowances,
14
pth.deductions,
15
pth.net_pay,
16
pth.payment_date,
17
s.name::TEXT AS status_name, -- 🔹 Cast explicitly to TEXT
18
pth.status_id,
19
CASE WHEN ba.rp_linked_account_id IS NOT NULL THEN TRUE ELSE FALSE END AS has_linked_account
20
FROM public.payroll_transaction_headers pth
21
JOIN public.employees e ON pth.employee_id = e.id
22
JOIN public.statuses s ON pth.status_id = s.id
23
LEFT JOIN public.employee_bank_accounts eb ON e.id = eb.employee_id
24
LEFT JOIN public.bank_accounts ba ON eb.bank_account_id = ba.id
25
WHERE pth.company_id = p_company_id
26
AND EXTRACT(YEAR FROM pth.payment_date) = p_financial_year_id
27
AND EXTRACT(MONTH FROM pth.payment_date) BETWEEN 4 AND 12 -- Transactions from April to December in the same year
28
OR (EXTRACT(YEAR FROM pth.payment_date) = p_financial_year_id + 1
29
AND EXTRACT(MONTH FROM pth.payment_date) BETWEEN 1 AND 3);
30
END;
31
$function$
|
|||||
| Function | get_leave_applications_with_balance | Missing in Target |
Source Script
Target Script
1
CREATE OR REPLACE FUNCTION public.get_leave_applications_with_balance(p_company_id uuid, p_leave_year integer, p_employee_id uuid DEFAULT NULL::uuid)
2
RETURNS TABLE(leave_application_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, entitled_leaves integer, consumed_leaves integer, remaining_leaves integer)
3
LANGUAGE plpgsql
4
AS $function$
5
DECLARE
6
financial_year_start date := make_date(p_leave_year, 4, 1);
7
financial_year_end date := make_date(p_leave_year + 1, 3, 31);
8
is_manager boolean := false;
9
BEGIN
10
-- Check if employee is a manager
11
IF p_employee_id IS NOT NULL THEN
12
SELECT EXISTS (
13
SELECT 1 FROM employees emp WHERE emp.manager_id = p_employee_id
14
) INTO is_manager;
15
END IF;
16
17
RETURN QUERY
18
SELECT
19
la.id AS leave_application_id,
20
la.employee_id,
21
CONCAT(e.first_name, ' ', e.last_name) AS employee_name,
22
e.manager_id, -- ✅ fully qualified
23
lt.id AS leave_type_id,
24
lt.name::TEXT AS leave_type_name,
25
la.start_date,
26
la.end_date,
27
la.reason,
28
ls.id AS leave_status_id,
29
ls.name::TEXT AS leave_status,
30
COALESCE(elb.entitled_leaves, 0) AS entitled_leaves,
31
COALESCE(elb.consumed_leaves, 0) AS consumed_leaves,
32
COALESCE(elb.entitled_leaves - elb.consumed_leaves, 0) AS remaining_leaves
33
FROM leave_applications la
34
INNER JOIN employees e ON la.employee_id = e.id
35
INNER JOIN leave_statuses ls ON la.leave_status_id = ls.id
36
INNER JOIN leave_types lt ON la.leave_type_id = lt.id
37
LEFT JOIN (
38
SELECT
39
le_inner.employee_id,
40
le_inner.leave_type_id,
41
le_inner.entitled_leaves,
42
le_inner.consumed_leaves
43
FROM (
44
SELECT
45
le.employee_id,
46
le.leave_type_id,
47
le.entitled_leaves,
48
le.consumed_leaves,
49
ROW_NUMBER() OVER (
50
PARTITION BY le.employee_id, le.leave_type_id
51
ORDER BY le.id DESC
52
) AS rn
53
FROM leave_entitlements le
54
) le_inner
55
WHERE le_inner.rn = 1
56
) elb
57
ON la.employee_id = elb.employee_id
58
AND la.leave_type_id = elb.leave_type_id
59
WHERE la.company_id = p_company_id
60
AND (la.is_deleted IS NULL OR la.is_deleted = FALSE)
61
AND la.start_date <= financial_year_end
62
AND la.end_date >= financial_year_start
63
AND (
64
p_employee_id IS NULL
65
OR (
66
is_manager = TRUE AND (
67
la.employee_id = p_employee_id
68
OR la.employee_id IN (
69
SELECT emp.id FROM employees emp WHERE emp.manager_id = p_employee_id
70
)
71
)
72
)
73
OR (
74
is_manager = FALSE AND la.employee_id = p_employee_id
75
)
76
);
77
END;
78
$function$
|
|||||
| Function | get_all_leave_entitlements | Missing in Target |
Source Script
Target Script
1
CREATE OR REPLACE FUNCTION public.get_all_leave_entitlements(p_employee_id uuid DEFAULT NULL::uuid, p_company_id uuid DEFAULT NULL::uuid, p_leave_year integer DEFAULT NULL::integer)
2
RETURNS TABLE(employee_id uuid, employee_name text, sick_leave text, casual_leave text, earned_leave text, maternity_leave text, paternity_leave text, company_id uuid, leave_year integer)
3
LANGUAGE plpgsql
4
AS $function$
5
DECLARE
6
is_manager BOOLEAN := FALSE;
7
BEGIN
8
-- Check if employee is a manager
9
IF p_employee_id IS NOT NULL THEN
10
SELECT EXISTS (
11
SELECT 1 FROM employees WHERE manager_id = p_employee_id
12
) INTO is_manager;
13
END IF;
14
15
RETURN QUERY
16
SELECT
17
le.employee_id,
18
CONCAT(e.first_name, ' ', e.last_name) AS employee_name,
19
20
COALESCE(
21
MAX(CASE WHEN lt.name = 'Sick Leave' THEN
22
CONCAT('Total: ', COALESCE(le.entitled_leaves, 0),
23
', Used: ', COALESCE(le.consumed_leaves, 0),
24
', Remaining: ', COALESCE(le.entitled_leaves - le.consumed_leaves, 0))
25
END),
26
'Total: 0, Used: 0, Remaining: 0'
27
) AS sick_leave,
28
29
COALESCE(
30
MAX(CASE WHEN lt.name = 'Casual Leave' THEN
31
CONCAT('Total: ', COALESCE(le.entitled_leaves, 0),
32
', Used: ', COALESCE(le.consumed_leaves, 0),
33
', Remaining: ', COALESCE(le.entitled_leaves - le.consumed_leaves, 0))
34
END),
35
'Total: 0, Used: 0, Remaining: 0'
36
) AS casual_leave,
37
38
COALESCE(
39
MAX(CASE WHEN lt.name = 'Earned Leave' THEN
40
CONCAT('Total: ', COALESCE(le.entitled_leaves, 0),
41
', Used: ', COALESCE(le.consumed_leaves, 0),
42
', Remaining: ', COALESCE(le.entitled_leaves - le.consumed_leaves, 0))
43
END),
44
'Total: 0, Used: 0, Remaining: 0'
45
) AS earned_leave,
46
47
COALESCE(
48
MAX(CASE WHEN lt.name = 'Maternity Leave' THEN
49
CONCAT('Total: ', COALESCE(le.entitled_leaves, 0),
50
', Used: ', COALESCE(le.consumed_leaves, 0),
51
', Remaining: ', COALESCE(le.entitled_leaves - le.consumed_leaves, 0))
52
END),
53
'Total: 0, Used: 0, Remaining: 0'
54
) AS maternity_leave,
55
56
COALESCE(
57
MAX(CASE WHEN lt.name = 'Paternity Leave' THEN
58
CONCAT('Total: ', COALESCE(le.entitled_leaves, 0),
59
', Used: ', COALESCE(le.consumed_leaves, 0),
60
', Remaining: ', COALESCE(le.entitled_leaves - le.consumed_leaves, 0))
61
END),
62
'Total: 0, Used: 0, Remaining: 0'
63
) AS paternity_leave,
64
65
le.company_id,
66
le.leave_year
67
68
FROM leave_entitlements le
69
JOIN leave_types lt ON le.leave_type_id = lt.id
70
JOIN employees e ON le.employee_id = e.id
71
WHERE (
72
p_employee_id IS NULL
73
OR (
74
is_manager = TRUE AND (
75
le.employee_id = p_employee_id OR
76
le.employee_id IN (
77
SELECT emp.id FROM employees emp WHERE emp.manager_id = p_employee_id
78
)
79
)
80
)
81
OR (
82
is_manager = FALSE AND le.employee_id = p_employee_id
83
)
84
)
85
AND (p_company_id IS NULL OR le.company_id = p_company_id)
86
AND (p_leave_year IS NULL OR le.leave_year = p_leave_year)
87
GROUP BY le.employee_id, e.first_name, e.last_name, le.company_id, le.leave_year;
88
END;
89
$function$
|
|||||
| Function | get_all_tasks | Missing in Target |
Source Script
Target Script
1
CREATE OR REPLACE FUNCTION public.get_all_tasks(p_company_id uuid)
2
RETURNS TABLE(task_id integer, task_name text, project_id uuid, sub_project_id integer, planned_hours integer, created_on_utc timestamp without time zone, modified_on_utc timestamp without time zone, created_by uuid, modified_by uuid)
3
LANGUAGE plpgsql
4
AS $function$
5
BEGIN
6
RETURN QUERY
7
SELECT
8
t.id, -- id (INTEGER)
9
t.name, -- name (TEXT)
10
t.project_id, -- project_id (UUID)
11
t.sub_project_id, -- sub_project_id (INTEGER)
12
t.planned_hours, -- planned_hours (INTEGER)
13
t.created_on_utc, -- created_on_utc (TIMESTAMP)
14
t.modified_on_utc, -- modified_on_utc (TIMESTAMP)
15
t.created_by, -- created_by (UUID)
16
t.modified_by -- modified_by (UUID)
17
FROM tasks t
18
WHERE t.company_id = p_company_id
19
AND t.is_deleted = false; -- Filtering out deleted tasks
20
END;
21
$function$
|
|||||
| Function | get_employee_leave_entitlement | Missing in Target |
Source Script
Target Script
1
CREATE OR REPLACE FUNCTION public.get_employee_leave_entitlement(p_employee_id uuid DEFAULT NULL::uuid)
2
RETURNS TABLE(id uuid, employee_id uuid, employee_name text, leave_type_id integer, leave_type_name text, entitled_leaves integer, consumed_leaves integer, remaining_leaves integer)
3
LANGUAGE plpgsql
4
AS $function$
5
BEGIN
6
RETURN QUERY
7
SELECT
8
gen_random_uuid() AS id, -- Auto-generate UUID for each row
9
le.employee_id,
10
CONCAT(e.first_name, ' ', e.last_name) AS employee_name,
11
le.leave_type_id,
12
lt.name::TEXT AS leave_type_name,
13
COALESCE(le.entitled_leaves, 0) AS entitled_leaves, -- Reversed back to entitled_leaves
14
COALESCE(le.consumed_leaves, 0) AS consumed_leaves, -- Reversed back to consumed_leaves
15
COALESCE(le.entitled_leaves - le.consumed_leaves, 0) AS remaining_leaves
16
FROM leave_entitlements le
17
JOIN leave_types lt ON le.leave_type_id = lt.id
18
JOIN employees e ON le.employee_id = e.id
19
WHERE (p_employee_id IS NULL OR le.employee_id = p_employee_id); -- Filter by employee_id only
20
END;
21
$function$
|
|||||
| Function | get_leave_applications_with_balance | Missing in Target |
Source Script
Target Script
1
CREATE OR REPLACE FUNCTION public.get_leave_applications_with_balance(p_company_id uuid, p_leave_year integer)
2
RETURNS TABLE(leave_application_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, entitled_leaves integer, consumed_leaves integer, remaining_leaves integer)
3
LANGUAGE plpgsql
4
AS $function$
5
BEGIN
6
RETURN QUERY
7
SELECT
8
la.id AS leave_application_id,
9
la.employee_id,
10
CONCAT(e.first_name, ' ', e.last_name) AS employee_name,
11
e.manager_id,
12
lt.id AS leave_type_id,
13
lt.name::text AS leave_type_name,
14
la.start_date,
15
la.end_date,
16
la.reason,
17
ls.id AS leave_status_id,
18
ls.name::text AS leave_status,
19
COALESCE(le.entitled_leaves, 0) AS entitled_leaves,
20
COALESCE(le.consumed_leaves, 0) AS consumed_leaves,
21
COALESCE(le.entitled_leaves, 0) - COALESCE(le.consumed_leaves, 0) AS remaining_leaves
22
FROM leave_applications la
23
INNER JOIN employees e ON la.employee_id = e.id
24
INNER JOIN leave_statuses ls ON la.leave_status_id = ls.id
25
INNER JOIN leave_types lt ON la.leave_type_id = lt.id
26
LEFT JOIN leave_entitlements le
27
ON le.employee_id = la.employee_id
28
AND le.leave_type_id = la.leave_type_id
29
AND le.company_id = la.company_id
30
AND le.leave_year = p_leave_year
31
WHERE la.company_id = p_company_id
32
AND (la.is_deleted IS NULL OR la.is_deleted = FALSE)
33
AND EXTRACT(YEAR FROM la.start_date) = p_leave_year;
34
END;
35
$function$
|
|||||
| Function | get_new_payroll_transaction_number | Missing in Target |
Source Script
Target Script
1
CREATE OR REPLACE FUNCTION public.get_new_payroll_transaction_number(p_company_id uuid, p_date date)
2
RETURNS character varying
3
LANGUAGE plpgsql
4
AS $function$
5
DECLARE
6
v_finance_year_id integer; -- Financial year start year
7
new_payroll_id integer; -- New payroll transaction ID
8
p_prefix varchar; -- Payroll prefix
9
payroll_number varchar;
10
BEGIN
11
-- Determine the start year of the financial year based on p_date
12
IF EXTRACT(MONTH FROM p_date) >= 4 THEN
13
v_finance_year_id := EXTRACT(YEAR FROM p_date);
14
ELSE
15
v_finance_year_id := EXTRACT(YEAR FROM p_date) - 1;
16
END IF;
17
18
-- Try to update the ID or insert a new row if it doesn't exist
19
WITH x AS (
20
UPDATE public.payroll_transaction_header_ids
21
SET last_payroll_id = COALESCE(last_payroll_id, 0) + 1
22
WHERE company_id = p_company_id AND fin_year = v_finance_year_id
23
RETURNING last_payroll_id, payroll_prefix
24
),
25
insert_x AS (
26
INSERT INTO public.payroll_transaction_header_ids (
27
company_id, fin_year, payroll_prefix, last_payroll_id, payroll_length
28
)
29
SELECT p_company_id, v_finance_year_id, 'PAYROLL', 1, 8
30
WHERE NOT EXISTS (SELECT 1 FROM x)
31
RETURNING last_payroll_id, payroll_prefix
32
)
33
SELECT
34
COALESCE((SELECT last_payroll_id FROM x LIMIT 1), (SELECT last_payroll_id FROM insert_x LIMIT 1)),
35
COALESCE((SELECT payroll_prefix FROM x LIMIT 1), (SELECT payroll_prefix FROM insert_x LIMIT 1))
36
INTO new_payroll_id, p_prefix;
37
38
-- Generate the payroll transaction number
39
payroll_number := p_prefix || LPAD(new_payroll_id::text, 4, '0');
40
41
RETURN payroll_number;
42
END;
43
$function$
|
|||||
| Function | get_payroll_transactions_by_fin_year_num | Missing in Target |
Source Script
Target Script
1
CREATE OR REPLACE FUNCTION public.get_payroll_transactions_by_fin_year_num(p_company_id uuid, p_fin_year integer)
2
RETURNS TABLE(id uuid, has_rp_linked_account boolean, employee_name text, pay_period_start date, pay_period_end date, basic_salary numeric, allowances numeric, deductions numeric, payment_date date, status_name character varying, status_id integer, net_pay numeric)
3
LANGUAGE plpgsql
4
AS $function$
5
DECLARE
6
v_start_date date := make_date(p_fin_year, 4, 1);
7
v_end_date date := make_date(p_fin_year + 1, 3, 31);
8
BEGIN
9
RETURN QUERY
10
SELECT
11
pth.id,
12
(ba.rp_linked_account_id IS NOT NULL AND ba.rp_linked_account_id <> '') AS has_rp_linked_account,
13
e.first_name || ' ' || e.last_name AS employee_name,
14
pth.pay_period_start,
15
pth.pay_period_end,
16
pth.basic_salary,
17
pth.allowances,
18
pth.deductions,
19
pth.payment_date,
20
s.name AS status_name,
21
pth.status_id,
22
pth.net_pay
23
FROM public.payroll_transaction_headers pth
24
JOIN public.employees e ON pth.employee_id = e.id
25
JOIN public.statuses s ON pth.status_id = s.id
26
LEFT JOIN public.employee_bank_accounts eba ON eba.employee_id = e.id
27
LEFT JOIN public.bank_accounts ba ON ba.id = eba.bank_account_id
28
WHERE pth.company_id = p_company_id
29
AND pth.pay_period_start >= v_start_date
30
AND pth.pay_period_end <= v_end_date
31
AND pth.is_deleted = false
32
AND s.is_deleted = false
33
ORDER BY pth.payment_date;
34
END;
35
$function$
|
|||||
| Function | get_holidays_with_user_names | Missing in Target |
Source Script
Target Script
1
CREATE OR REPLACE FUNCTION public.get_holidays_with_user_names(in_year integer, in_company_id uuid)
2
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)
3
LANGUAGE plpgsql
4
STABLE
5
AS $function$
6
BEGIN
7
RETURN QUERY
8
SELECT
9
h.id,
10
h.holiday_date,
11
h.name,
12
h.description,
13
h.created_on_utc,
14
h.modified_on_utc,
15
h.deleted_on_utc,
16
h.is_deleted,
17
h.created_by AS created_by_id,
18
(u1.first_name || ' ' || u1.last_name)::varchar AS created_by,
19
h.modified_by AS modified_by_id,
20
(u2.first_name || ' ' || u2.last_name)::varchar AS modified_by,
21
h.company_id
22
FROM public.holidays h
23
LEFT JOIN public.users u1 ON h.created_by = u1.id
24
LEFT JOIN public.users u2 ON h.modified_by = u2.id
25
WHERE
26
h.holiday_date >= make_date(in_year, 1, 1)
27
AND h.holiday_date < make_date(in_year + 1, 1, 1)
28
AND h.company_id = in_company_id
29
AND h.is_deleted = FALSE
30
ORDER BY h.holiday_date;
31
END;
32
$function$
|
|||||
| Function | get_leave_application_with_balance_also_check_managerid | Missing in Target |
Source Script
Target Script
1
CREATE OR REPLACE FUNCTION public.get_leave_application_with_balance_also_check_managerid(p_company_id uuid, p_fin_year_id integer, p_employee_id uuid)
2
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)
3
LANGUAGE plpgsql
4
AS $function$
5
DECLARE
6
financial_year_start date := make_date(p_fin_year_id, 4, 1);
7
financial_year_end date := make_date(p_fin_year_id + 1, 3, 31);
8
is_manager boolean := false;
9
BEGIN
10
-- Check if p_employee_id is a manager in the company
11
SELECT EXISTS (
12
SELECT 1 FROM employees WHERE manager_id = p_employee_id AND company_id = p_company_id
13
) INTO is_manager;
14
15
RETURN QUERY
16
SELECT
17
la.id AS leave_record_id,
18
la.employee_id AS employee_id,
19
CONCAT(e.first_name, ' ', e.last_name) AS employee_name,
20
e.manager_id,
21
lt.id AS leave_type_id,
22
lt.name::TEXT AS leave_type_name,
23
la.start_date,
24
la.end_date,
25
la.reason,
26
ls.id AS leave_status_id,
27
ls.name::TEXT AS leave_status,
28
COALESCE(ele.total_leaves, 0) AS total_leaves,
29
COALESCE(ele.used_leaves, 0) AS used_leaves,
30
COALESCE(ele.total_leaves - ele.used_leaves, 0) AS remaining_leaves
31
FROM leave_application la
32
INNER JOIN employees e
33
ON la.employee_id = e.id
34
INNER JOIN leave_statuses ls
35
ON la.leave_status_id = ls.id
36
INNER JOIN leave_types lt
37
ON la.leave_type_id = lt.id
38
LEFT JOIN (
39
SELECT
40
ele_inner.employee_id,
41
ele_inner.leave_type_id,
42
ele_inner.total_leaves,
43
ele_inner.used_leaves
44
FROM (
45
SELECT
46
ele.employee_id,
47
ele.leave_type_id,
48
ele.total_leaves,
49
ele.used_leaves,
50
ROW_NUMBER() OVER (
51
PARTITION BY ele.employee_id, ele.leave_type_id
52
ORDER BY ele.id DESC
53
) AS rn
54
FROM employee_leave_entitlement ele
55
) ele_inner
56
WHERE ele_inner.rn = 1
57
) ele
58
ON la.employee_id = ele.employee_id
59
AND la.leave_type_id = ele.leave_type_id
60
WHERE la.company_id = p_company_id
61
AND (la.is_deleted IS NULL OR la.is_deleted = FALSE)
62
AND la.start_date <= financial_year_end
63
AND la.end_date >= financial_year_start
64
AND (
65
is_manager = true
66
OR la.employee_id = p_employee_id
67
);
68
END;
69
$function$
|
|||||
| Function | update_payroll_status | Missing in Target |
Source Script
Target Script
1
CREATE OR REPLACE FUNCTION public.update_payroll_status(p_company_id uuid, p_payroll_ids uuid[], p_target_status integer, p_modified_by uuid)
2
RETURNS TABLE(payroll_id uuid, new_status text, error_msg text)
3
LANGUAGE plpgsql
4
AS $function$
5
DECLARE
6
v_exists boolean;
7
v_current_status int;
8
v_allowed boolean;
9
BEGIN
10
FOREACH payroll_id IN ARRAY p_payroll_ids LOOP
11
12
BEGIN
13
--------------------------------------------------------
14
-- 1. Check payroll exists
15
--------------------------------------------------------
16
SELECT EXISTS(
17
SELECT 1
18
FROM payroll_transaction_headers
19
WHERE id = payroll_id
20
AND company_id = p_company_id
21
) INTO v_exists;
22
23
IF NOT v_exists THEN
24
RETURN QUERY SELECT payroll_id, NULL, 'Payroll not found';
25
CONTINUE;
26
END IF;
27
28
--------------------------------------------------------
29
-- 2. Get current status
30
--------------------------------------------------------
31
SELECT status_id INTO v_current_status
32
FROM payroll_transaction_headers
33
WHERE id = payroll_id;
34
35
--------------------------------------------------------
36
-- 3. Validate transition from workflow table
37
--------------------------------------------------------
38
SELECT EXISTS(
39
SELECT 1
40
FROM payroll_workflow
41
WHERE company_id = p_company_id
42
AND status = v_current_status
43
AND next_status = p_target_status
44
AND is_enabled = TRUE
45
) INTO v_allowed;
46
47
IF NOT v_allowed THEN
48
RETURN QUERY
49
SELECT payroll_id, NULL,
50
format('Transition not allowed: %s → %s', v_current_status, p_target_status);
51
CONTINUE;
52
END IF;
53
54
--------------------------------------------------------
55
-- 4. Update status
56
--------------------------------------------------------
57
UPDATE payroll_transaction_headers
58
SET status_id = p_target_status,
59
modified_by = p_modified_by,
60
modified_on_utc = NOW()
61
WHERE id = payroll_id;
62
63
--------------------------------------------------------
64
-- 5. Return readable status name
65
--------------------------------------------------------
66
RETURN QUERY
67
SELECT
68
payroll_id,
69
CASE p_target_status
70
WHEN 1 THEN 'DRAFT'
71
WHEN 2 THEN 'SUBMITTED'
72
WHEN 3 THEN 'APPROVED'
73
WHEN 4 THEN 'PAID'
74
WHEN 5 THEN 'REJECTED'
75
WHEN 6 THEN 'CANCELLED'
76
ELSE 'UNKNOWN'
77
END,
78
NULL;
79
80
EXCEPTION WHEN OTHERS THEN
81
RETURN QUERY SELECT payroll_id, NULL, 'Error: ' || SQLERRM;
82
END;
83
84
END LOOP;
85
END;
86
$function$
|
|||||
| Procedure | delete_payments_by_company | Missing in Target |
Source Script
Target Script
1
CREATE OR REPLACE PROCEDURE public.delete_payments_by_company(IN p_company_id uuid)
2
LANGUAGE plpgsql
3
AS $procedure$
4
BEGIN
5
-- Delete payment details first (to avoid foreign key violations)
6
DELETE FROM public.payment_details
7
WHERE payment_header_id IN (
8
SELECT id FROM public.payment_headers WHERE company_id = p_company_id
9
);
10
11
-- Optionally delete from payment_headers (if required)
12
DELETE FROM public.payment_headers WHERE company_id = p_company_id;
13
14
RAISE NOTICE 'Deleted payments for Company ID: %', p_company_id;
15
END;
16
$procedure$
|
|||||
| Procedure | delete_payroll_and_payment_data | Missing in Target |
Source Script
Target Script
1
CREATE OR REPLACE PROCEDURE public.delete_payroll_and_payment_data(IN p_company_id uuid)
2
LANGUAGE plpgsql
3
AS $procedure$
4
BEGIN
5
-- Delete payment details associated with payment headers of the given company
6
DELETE FROM public.payment_details
7
WHERE payment_header_id IN (
8
SELECT id FROM public.payment_headers WHERE company_id = p_company_id
9
);
10
11
-- Delete payment headers for the given company
12
DELETE FROM public.payment_headers
13
WHERE company_id = p_company_id;
14
15
-- Delete payroll transaction details associated with payroll transaction headers of the given company
16
DELETE FROM public.payroll_transaction_details
17
WHERE payroll_transaction_id IN (
18
SELECT id FROM public.payroll_transaction_headers WHERE company_id = p_company_id
19
);
20
21
-- Delete payroll transaction headers for the given company
22
DELETE FROM public.payroll_transaction_headers
23
WHERE company_id = p_company_id;
24
25
-- Commit transaction
26
COMMIT;
27
END;
28
$procedure$
|
|||||
| Procedure | delete_payroll_transactions_by_company | Missing in Target |
Source Script
Target Script
1
CREATE OR REPLACE PROCEDURE public.delete_payroll_transactions_by_company(IN p_company_id uuid)
2
LANGUAGE plpgsql
3
AS $procedure$
4
BEGIN
5
-- Step 1: Delete payroll transaction details linked to headers
6
DELETE FROM payroll_transaction_details
7
WHERE payroll_transaction_id IN (
8
SELECT id FROM payroll_transaction_headers
9
WHERE company_id = p_company_id AND status_id = 2
10
);
11
12
-- Step 2: Delete payroll transaction headers
13
DELETE FROM payroll_transaction_headers
14
WHERE company_id = p_company_id AND status_id = 2;
15
16
-- Log the deletion
17
RAISE NOTICE 'Deleted payroll transactions for Company ID: %', p_company_id;
18
END;
19
$procedure$
|
|||||
| Procedure | initialize_payroll_transaction_header_ids | Missing in Target |
Source Script
Target Script
1
CREATE OR REPLACE PROCEDURE public.initialize_payroll_transaction_header_ids(IN p_default_company_id uuid, IN new_company_id uuid)
2
LANGUAGE plpgsql
3
AS $procedure$
4
DECLARE
5
v_header_exists BOOLEAN;
6
BEGIN
7
-- Check if payroll transaction header IDs already exist for the new company
8
SELECT EXISTS (
9
SELECT 1
10
FROM payroll_transaction_header_ids
11
WHERE company_id = new_company_id
12
) INTO v_header_exists;
13
14
IF NOT v_header_exists THEN
15
INSERT INTO payroll_transaction_header_ids (
16
id,
17
company_id,
18
fin_year,
19
payroll_prefix,
20
payroll_length,
21
last_payroll_id
22
)
23
SELECT
24
nextval('payroll_transaction_header_ids_id_seq'),
25
new_company_id,
26
fin_year,
27
payroll_prefix,
28
payroll_length,
29
last_payroll_id
30
FROM payroll_transaction_header_ids
31
WHERE company_id = p_default_company_id;
32
33
RAISE NOTICE 'Payroll transaction header IDs have been copied from company % to company %.', p_default_company_id, new_company_id;
34
ELSE
35
RAISE NOTICE 'Payroll transaction header IDs already exist for company %. Skipping initialization.', new_company_id;
36
END IF;
37
END;
38
$procedure$
|
|||||
| Procedure | hard_delete_organization | Missing in Target |
Source Script
Target Script
1
CREATE OR REPLACE PROCEDURE public.hard_delete_organization(IN p_organization_id uuid)
2
LANGUAGE plpgsql
3
AS $procedure$
4
DECLARE
5
p_company_id UUID; -- Variable to hold company IDs associated with the organization
6
BEGIN
7
-- Get the associated company IDs for the organization
8
FOR p_company_id IN
9
SELECT c.id FROM public.companies c WHERE c.organization_id = p_organization_id
10
LOOP
11
-- Delete from payment_header_ids
12
DELETE FROM payment_header_ids
13
WHERE payment_header_ids.company_id = p_company_id;
14
15
-- Delete from payroll_transaction_header_ids
16
DELETE FROM payroll_transaction_header_ids
17
WHERE payroll_transaction_header_ids.company_id = p_company_id;
18
19
-- Delete from leave_workflow
20
DELETE FROM leave_workflow
21
WHERE leave_workflow.company_id = p_company_id;
22
23
-- Delete users associated with the company
24
DELETE FROM public.users
25
WHERE users.company_id = p_company_id;
26
27
-- Delete from companies
28
DELETE FROM public.companies
29
WHERE public.companies.id = p_company_id;
30
END LOOP;
31
32
33
-- Delete the organization itself
34
DELETE FROM public.organizations
35
WHERE public.organizations.id = p_organization_id;
36
37
-- Log the operation
38
RAISE NOTICE 'Organization with ID % and all related data have been hard deleted.', p_organization_id;
39
40
END;
41
$procedure$
|
|||||
| Procedure | initialize_company | Missing in Target |
Source Script
Target Script
1
CREATE OR REPLACE PROCEDURE public.initialize_company(IN p_company_id uuid, IN p_organization_id uuid, IN p_is_apartment boolean, IN p_name text, IN p_created_by uuid, IN p_default_company_id uuid)
2
LANGUAGE plpgsql
3
AS $procedure$
4
DECLARE
5
v_company_exists boolean;
6
BEGIN
7
-- Check if company already exists
8
SELECT EXISTS (
9
SELECT 1 FROM public.companies WHERE id = p_company_id
10
) INTO v_company_exists;
11
12
IF NOT v_company_exists THEN
13
-- Insert into companies table ONLY if it doesn't exist
14
INSERT INTO public.companies (
15
id,
16
organization_id,
17
is_apartment,
18
name,
19
created_on_utc,
20
created_by
21
) VALUES (
22
p_company_id,
23
p_organization_id,
24
p_is_apartment,
25
p_name,
26
NOW(),
27
p_created_by
28
);
29
30
RAISE NOTICE 'Initialized company: % with ID: %', p_name, p_company_id;
31
ELSE
32
RAISE NOTICE 'Company with ID % already exists. Skipping other initializations.', p_company_id;
33
END IF;
34
35
-- Call other procedures
36
CALL public.initialize_leave_workflow(
37
p_default_company_id,
38
p_company_id,
39
p_created_by
40
);
41
42
CALL public.initialize_payroll_transaction_header_ids(
43
p_default_company_id,
44
p_company_id
45
);
46
47
CALL public.initialize_payment_header_ids(
48
p_default_company_id,
49
p_company_id
50
);
51
52
END;
53
$procedure$
|
|||||
| Procedure | initialize_leave_workflow | Missing in Target |
Source Script
Target Script
1
CREATE OR REPLACE PROCEDURE public.initialize_leave_workflow(IN p_default_company_id uuid, IN new_company_id uuid, IN p_created_by uuid)
2
LANGUAGE plpgsql
3
AS $procedure$
4
DECLARE
5
v_workflow_exists BOOLEAN;
6
BEGIN
7
-- Check if leave workflow entries already exist for the new company
8
SELECT EXISTS (
9
SELECT 1
10
FROM leave_workflow
11
WHERE company_id = new_company_id
12
) INTO v_workflow_exists;
13
14
IF NOT v_workflow_exists THEN
15
-- Insert new records for the new company
16
INSERT INTO leave_workflow (
17
id,
18
company_id,
19
status_id,
20
next_status_id,
21
previous_status_id,
22
is_initial,
23
created_on_utc,
24
created_by
25
)
26
SELECT
27
nextval('leave_workflow_id_seq'), -- Generate new sequential ID
28
new_company_id, -- Assign the new company ID
29
status_id,
30
next_status_id,
31
previous_status_id,
32
is_initial,
33
created_on_utc,
34
p_created_by
35
FROM leave_workflow
36
WHERE company_id = p_default_company_id;
37
38
-- Optional: Log the operation
39
RAISE NOTICE 'Leave workflow records have been copied from company % to company % by user %.', p_default_company_id, new_company_id, p_created_by;
40
ELSE
41
RAISE NOTICE 'Leave workflow records already exist for company %. Skipping initialization.', new_company_id;
42
END IF;
43
END;
44
$procedure$
|
|||||
| Procedure | create_payroll_transaction | Missing in Target |
Source Script
Target Script
1
CREATE OR REPLACE PROCEDURE public.create_payroll_transaction(IN p_payroll_id uuid, IN p_company_id uuid, IN p_employee_id uuid, IN p_pay_period_start date, IN p_pay_period_end date, IN p_basic_salary numeric, IN p_allowances numeric, IN p_deductions numeric, IN p_net_pay numeric, IN p_date date, IN p_payment_method_id integer, IN p_status_id integer, IN p_created_by uuid, IN p_components_data jsonb)
2
LANGUAGE plpgsql
3
AS $procedure$
4
DECLARE
5
v_component_type_id INTEGER;
6
v_component_amount NUMERIC(15,2);
7
v_payroll_number CHARACTER VARYING;
8
v_existing_header_id UUID;
9
BEGIN
10
-- Check if a payroll transaction header already exists for the employee, pay period, and company
11
SELECT id INTO v_existing_header_id
12
FROM public.payroll_transaction_headers
13
WHERE employee_id = p_employee_id
14
AND pay_period_start = p_pay_period_start
15
AND pay_period_end = p_pay_period_end
16
AND company_id = p_company_id
17
AND is_deleted = false;
18
19
v_payroll_number := get_new_payroll_transaction_number(p_company_id, p_date);
20
21
IF v_existing_header_id IS NOT NULL THEN
22
-- Update the existing payroll transaction header
23
UPDATE public.payroll_transaction_headers
24
SET
25
basic_salary = p_basic_salary,
26
allowances = p_allowances,
27
deductions = p_deductions,
28
net_pay = p_net_pay,
29
payment_date = p_date,
30
payment_method_id = p_payment_method_id,
31
status_id = p_status_id,
32
modified_by = p_created_by, -- added modified by
33
modified_on_utc = NOW() -- added modified on
34
WHERE id = v_existing_header_id;
35
36
-- Delete existing details and insert new ones.
37
DELETE FROM public.payroll_transaction_details
38
WHERE payroll_transaction_id = v_existing_header_id;
39
40
RAISE NOTICE 'Updated payroll transaction header with ID: %', v_existing_header_id;
41
ELSE
42
-- Insert a new payroll transaction header
43
INSERT INTO public.payroll_transaction_headers (
44
id,
45
company_id,
46
employee_id,
47
pay_period_start,
48
pay_period_end,
49
basic_salary,
50
allowances,
51
deductions,
52
net_pay,
53
payment_date,
54
payment_method_id,
55
created_by,
56
created_on_utc,
57
status_id,
58
payroll_number,
59
is_deleted
60
)
61
VALUES (
62
p_payroll_id,
63
p_company_id,
64
p_employee_id,
65
p_pay_period_start,
66
p_pay_period_end,
67
p_basic_salary,
68
p_allowances,
69
p_deductions,
70
p_net_pay,
71
p_date,
72
p_payment_method_id,
73
p_created_by,
74
NOW(),
75
p_status_id,
76
v_payroll_number,
77
false
78
);
79
RAISE NOTICE 'Inserted new payroll transaction header with ID: %', p_payroll_id;
80
END IF;
81
82
-- Loop through the component data for each employee and insert into details
83
FOR v_component_type_id, v_component_amount IN
84
SELECT
85
(component ->> 'component_type_id')::INTEGER,
86
(component ->> 'amount')::NUMERIC
87
FROM jsonb_array_elements(p_components_data) AS component
88
LOOP
89
INSERT INTO public.payroll_transaction_details (
90
id,
91
payroll_transaction_id,
92
component_type_id,
93
amount
94
)
95
VALUES (
96
gen_random_uuid(),
97
COALESCE(v_existing_header_id, p_payroll_id), -- Use existing or new header ID
98
v_component_type_id,
99
v_component_amount
100
);
101
END LOOP;
102
END;
103
$procedure$
|
|||||
| Procedure | save_payroll_transactions | Missing in Target |
Source Script
Target Script
1
CREATE OR REPLACE PROCEDURE public.save_payroll_transactions(IN p_company_id uuid, IN p_date date, IN p_created_by uuid, IN p_payrolls_data jsonb)
2
LANGUAGE plpgsql
3
AS $procedure$
4
DECLARE
5
v_payroll JSONB;
6
v_payroll_id UUID;
7
v_employee_id UUID;
8
v_pay_period_start DATE;
9
v_pay_period_end DATE;
10
v_basic_salary NUMERIC;
11
v_allowances NUMERIC;
12
v_deductions NUMERIC;
13
v_net_pay NUMERIC;
14
v_payment_method_id INTEGER;
15
v_status_id INTEGER;
16
v_components_data JSONB;
17
BEGIN
18
-- Loop through each payroll entry in the array
19
FOR v_payroll IN SELECT * FROM jsonb_array_elements(p_payrolls_data)
20
LOOP
21
-- Extract payroll details from JSONB data
22
v_payroll_id := gen_random_uuid();
23
v_employee_id := (v_payroll ->> 'employee_id')::UUID;
24
v_pay_period_start := (v_payroll ->> 'pay_period_start')::DATE;
25
v_pay_period_end := (v_payroll ->> 'pay_period_end')::DATE;
26
v_basic_salary := (v_payroll ->> 'basic_salary')::NUMERIC;
27
v_allowances := (v_payroll ->> 'allowances')::NUMERIC;
28
v_deductions := (v_payroll ->> 'deductions')::NUMERIC;
29
v_net_pay := (v_payroll ->> 'net_pay')::NUMERIC;
30
v_payment_method_id := (v_payroll ->> 'payment_method_id')::INTEGER;
31
v_status_id := (v_payroll ->> 'status_id')::INTEGER;
32
v_components_data := v_payroll -> 'components';
33
34
-- Call the existing procedure for each payroll
35
CALL public.create_payroll_transaction(
36
v_payroll_id,
37
p_company_id,
38
v_employee_id,
39
v_pay_period_start,
40
v_pay_period_end,
41
v_basic_salary,
42
v_allowances,
43
v_deductions,
44
v_net_pay,
45
p_date,
46
v_payment_method_id,
47
v_status_id,
48
p_created_by,
49
v_components_data
50
);
51
END LOOP;
52
END;
53
$procedure$
|
|||||
| Procedure | hard_delete_org_payroll | Missing in Target |
Source Script
Target Script
1
CREATE OR REPLACE PROCEDURE public.hard_delete_org_payroll(IN p_organization_id uuid)
2
LANGUAGE plpgsql
3
AS $procedure$
4
DECLARE
5
v_company_id uuid;
6
BEGIN
7
FOR v_company_id IN SELECT id FROM companies WHERE organization_id = p_organization_id LOOP
8
-- Transactional and dependent records first
9
DELETE FROM employee_leave_balances WHERE company_id = v_company_id;
10
DELETE FROM leave_workflow WHERE company_id = v_company_id;
11
DELETE FROM leave_records WHERE company_id = v_company_id;
12
DELETE FROM holidays WHERE company_id = v_company_id;
13
DELETE FROM timesheets WHERE company_id = v_company_id;
14
DELETE FROM tasks WHERE company_id = v_company_id;
15
DELETE FROM projects WHERE company_id = v_company_id;
16
17
DELETE FROM payroll_transaction_header_ids WHERE company_id = v_company_id;
18
DELETE FROM payroll_transaction_headers WHERE company_id = v_company_id;
19
DELETE FROM payroll_configuration_headers WHERE company_id = v_company_id;
20
21
DELETE FROM payment_header_ids WHERE company_id = v_company_id;
22
DELETE FROM payment_headers WHERE company_id = v_company_id;
23
24
DELETE FROM employees WHERE company_id = v_company_id;
25
DELETE FROM workdays WHERE company_id = v_company_id;
26
DELETE FROM users WHERE company_id = v_company_id;
27
28
DELETE FROM companies WHERE id = v_company_id;
29
30
RAISE NOTICE 'Deleted payroll data for company_id %', v_company_id;
31
END LOOP;
32
33
DELETE FROM organizations WHERE id = p_organization_id;
34
35
RAISE NOTICE 'Deleted payroll data for organization_id %', p_organization_id;
36
END;
37
$procedure$
|
|||||
| Procedure | initialize_organization | Missing in Target |
Source Script
Target Script
1
CREATE OR REPLACE PROCEDURE public.initialize_organization(IN p_id uuid, IN p_name text, IN p_company_guids text, IN p_company_names text, IN p_user_id uuid, IN p_user_first_name text, IN p_user_last_name text, IN p_email character varying, IN p_phone_number character varying, IN p_created_by uuid, IN p_default_company_id uuid)
2
LANGUAGE plpgsql
3
AS $procedure$
4
DECLARE
5
v_company_id uuid; -- Variable for iterating through company IDs
6
v_company_name text; -- Variable for iterating through company names
7
v_company_ids uuid[]; -- Array to hold parsed company IDs
8
v_company_names text[]; -- Array to hold parsed company names
9
i integer;
10
v_organization_exists boolean;
11
v_user_exists boolean;
12
BEGIN
13
-- Check if organization already exists by ID or Name
14
SELECT EXISTS (
15
SELECT 1 FROM public.organizations WHERE id = p_id OR (id = p_id AND name = p_name)
16
) INTO v_organization_exists;
17
18
IF v_organization_exists THEN
19
RAISE NOTICE 'Organization with ID % already exists. Skipping initialization.', p_id;
20
ELSE
21
INSERT INTO public.organizations (
22
id,
23
name,
24
created_on_utc,
25
created_by
26
) VALUES (
27
p_id,
28
p_name,
29
NOW(),
30
p_created_by
31
);
32
RAISE NOTICE 'Initialized organization: % with ID: %', p_name, p_id;
33
END IF;
34
35
-- Parse company IDs and names
36
v_company_ids := string_to_array(p_company_guids, ',');
37
v_company_names := string_to_array(p_company_names, ',');
38
39
40
-- Loop through each company and initialize
41
FOR i IN 1..array_length(v_company_ids, 1) LOOP
42
v_company_id := v_company_ids[i];
43
v_company_name := v_company_names[i];
44
45
-- Call initialize_company for each company
46
CALL public.initialize_company(
47
v_company_id,
48
p_id, -- Organization ID
49
true, -- Indicates it's an apartment or payroll company
50
v_company_name,
51
p_created_by,
52
p_default_company_id
53
);
54
END LOOP;
55
56
-- Assign the first company ID from the array to the user
57
v_company_id := v_company_ids[1];
58
59
-- Check if user already exists by ID
60
SELECT EXISTS (
61
SELECT 1 FROM public.users WHERE id = p_user_id OR email = p_email
62
) INTO v_user_exists;
63
64
IF v_user_exists THEN
65
UPDATE public.users
66
SET company_id = v_company_id
67
WHERE id = p_user_id OR email = p_email;
68
RAISE NOTICE 'User with ID % already exists. Skipping user creation.', p_user_id;
69
ELSE
70
INSERT INTO public.users (
71
id,
72
email,
73
phone_number,
74
first_name,
75
last_name,
76
password_hash,
77
created_on_utc,
78
created_by,
79
company_id
80
) VALUES (
81
p_user_id,
82
p_email,
83
p_phone_number,
84
p_user_first_name,
85
p_user_last_name,
86
'',
87
NOW(),
88
p_created_by,
89
v_company_id
90
);
91
RAISE NOTICE 'Initialized user with ID: % and email: % in organization: %', p_user_id, p_email, p_name;
92
END IF;
93
94
END;
95
$procedure$
|
|||||
| Procedure | initialize_payment_header_ids | Missing in Target |
Source Script
Target Script
1
CREATE OR REPLACE PROCEDURE public.initialize_payment_header_ids(IN p_default_company_id uuid, IN new_company_id uuid)
2
LANGUAGE plpgsql
3
AS $procedure$
4
DECLARE
5
v_header_exists BOOLEAN;
6
BEGIN
7
-- Check if payment header IDs already exist for the new company
8
SELECT EXISTS (
9
SELECT 1
10
FROM payment_header_ids
11
WHERE company_id = new_company_id
12
) INTO v_header_exists;
13
14
IF NOT v_header_exists THEN
15
-- Insert new records for the new company
16
INSERT INTO payment_header_ids (
17
id,
18
company_id,
19
fin_year,
20
payment_prefix,
21
payment_length,
22
last_payment_id
23
)
24
SELECT
25
nextval('payment_header_ids_id_seq'), -- Generate new sequential ID
26
new_company_id, -- Assign the new company ID
27
fin_year,
28
payment_prefix,
29
payment_length,
30
last_payment_id
31
FROM payment_header_ids
32
WHERE company_id = p_default_company_id;
33
34
-- Optional: Log the operation
35
RAISE NOTICE 'Payment header IDs have been copied from company % to company %.', p_default_company_id, new_company_id;
36
ELSE
37
RAISE NOTICE 'Payment header IDs already exist for company %. Skipping initialization.', new_company_id;
38
END IF;
39
END;
40
$procedure$
|
|||||
| Procedure | insert_payment_headers_from_payroll | Missing in Target |
Source Script
Target Script
1
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_created_by uuid, IN p_payroll_transaction_ids uuid[])
2
LANGUAGE plpgsql
3
AS $procedure$
4
DECLARE
5
v_total_paid_amount numeric := 0;
6
v_serial_number integer := 1;
7
v_payroll_record RECORD;
8
v_payment_number character varying;
9
BEGIN
10
v_payment_number := get_new_payment_number(p_company_id, p_payment_date::date);
11
IF array_length(p_payroll_transaction_ids, 1) IS NULL THEN
12
RAISE NOTICE 'No payroll transaction IDs provided.';
13
RETURN;
14
END IF;
15
16
-- Insert into payment_headers
17
INSERT INTO public.payment_headers(
18
id, company_id, paid_date, paid_amount, tds_amount, advance_amount,
19
description, credit_account_id, debit_account_id, mode_of_payment,
20
reference, payment_number, created_on_utc, created_by, is_deleted
21
)
22
VALUES (
23
p_payment_header_id, p_company_id, p_payment_date, 0, 0, 0,
24
'Payment of salary', p_credit_account_id, p_debit_account_id,
25
'NEFT', p_reference, v_payment_number, NOW(), p_created_by, false
26
);
27
28
-- Insert into payment_details and update payroll transactions
29
FOR v_payroll_record IN
30
SELECT id, employee_id, net_pay
31
FROM public.payroll_transaction_headers
32
WHERE id = ANY(p_payroll_transaction_ids)
33
LOOP
34
INSERT INTO public.payment_details(
35
id, payment_header_id, employee_id, paid_amount, serial_number, is_deleted
36
)
37
VALUES (
38
gen_random_uuid(), p_payment_header_id, v_payroll_record.employee_id,
39
v_payroll_record.net_pay, v_serial_number, false
40
);
41
42
UPDATE public.payroll_transaction_headers
43
SET status_id = 2
44
WHERE id = v_payroll_record.id;
45
46
v_total_paid_amount := v_total_paid_amount + v_payroll_record.net_pay;
47
v_serial_number := v_serial_number + 1;
48
END LOOP;
49
50
-- Update the total paid amount in payment_headers
51
UPDATE public.payment_headers
52
SET paid_amount = v_total_paid_amount
53
WHERE id = p_payment_header_id;
54
55
RAISE NOTICE 'Payment Header % inserted with total paid amount %', p_payment_header_id, v_total_paid_amount;
56
END
57
$procedure$
|
|||||
| Procedure | purge_payroll_organization_data | Missing in Target |
Source Script
Target Script
1
CREATE OR REPLACE PROCEDURE public.purge_payroll_organization_data(IN p_organization_ids uuid[] DEFAULT NULL::uuid[])
2
LANGUAGE plpgsql
3
AS $procedure$
4
DECLARE
5
-- Retention policy (time-gate high-volume transactional rows)
6
c_retention_hours integer := 24;
7
v_cutoff_24h timestamp := now() - make_interval(hours => GREATEST(c_retention_hours, 1));
8
9
-- Targets
10
v_orgs uuid[] := '{}';
11
v_companies uuid[] := '{}';
12
13
-- Employees and related
14
v_employee_ids uuid[] := '{}';
15
16
-- Projects, sub-projects, tasks
17
v_project_ids uuid[] := '{}';
18
v_sub_project_ids int[] := '{}';
19
v_task_ids int[] := '{}';
20
21
-- Timesheets
22
v_timesheet_ids int[] := '{}';
23
v_timesheet_line_ids int[] := '{}';
24
25
-- Payroll transactions
26
v_payroll_header_ids uuid[] := '{}';
27
28
-- Payments
29
v_payment_header_ids uuid[] := '{}';
30
31
-- Payroll configurations
32
v_payroll_conf_header_ids uuid[] := '{}';
33
34
BEGIN
35
START TRANSACTION;
36
37
--------------------------------------------------------------------
38
-- 1) Resolve target organizations and companies
39
--------------------------------------------------------------------
40
IF p_organization_ids IS NULL OR array_length(p_organization_ids, 1) IS NULL THEN
41
SELECT COALESCE(array_agg(id), '{}')
42
INTO v_orgs
43
FROM organizations
44
WHERE created_on_utc > '2025-05-23'
45
AND created_on_utc < (NOW() - interval '24 hours');
46
ELSE
47
v_orgs := p_organization_ids;
48
END IF;
49
50
IF v_orgs IS NULL OR array_length(v_orgs, 1) IS NULL THEN
51
RAISE NOTICE 'No organizations found for payroll cleanup.';
52
COMMIT;
53
RETURN;
54
END IF;
55
56
SELECT COALESCE(array_agg(id), '{}')
57
INTO v_companies
58
FROM companies
59
WHERE organization_id = ANY(v_orgs);
60
61
IF v_companies IS NULL OR array_length(v_companies, 1) IS NULL THEN
62
RAISE NOTICE 'No companies resolved for payroll purge. Orgs: %', v_orgs;
63
COMMIT;
64
RETURN;
65
END IF;
66
67
RAISE NOTICE 'Payroll purge targets - Organizations: %; Companies: %', v_orgs, v_companies;
68
69
--------------------------------------------------------------------
70
-- 2) Collect IDs (COALESCE to '{}' to avoid NULL-array issues)
71
--------------------------------------------------------------------
72
-- Employees
73
SELECT COALESCE(array_agg(id), '{}')
74
INTO v_employee_ids
75
FROM employees
76
WHERE company_id = ANY(v_companies);
77
78
-- Projects and children
79
SELECT COALESCE(array_agg(id), '{}')
80
INTO v_project_ids
81
FROM projects
82
WHERE company_id = ANY(v_companies);
83
84
SELECT COALESCE(array_agg(id), '{}')
85
INTO v_sub_project_ids
86
FROM sub_projects
87
WHERE project_id = ANY(v_project_ids);
88
89
SELECT COALESCE(array_agg(id), '{}')
90
INTO v_task_ids
91
FROM tasks
92
WHERE company_id = ANY(v_companies);
93
94
-- Timesheets (time-gated to last 24h by created_on_utc; adjust if needed)
95
SELECT COALESCE(array_agg(id), '{}')
96
INTO v_timesheet_ids
97
FROM timesheets
98
WHERE company_id = ANY(v_companies)
99
AND (created_on_utc IS NULL OR created_on_utc < v_cutoff_24h);
100
101
SELECT COALESCE(array_agg(id), '{}')
102
INTO v_timesheet_line_ids
103
FROM timesheet_lines
104
WHERE timesheet_id = ANY(v_timesheet_ids);
105
106
-- Payroll transactions (time-gated)
107
SELECT COALESCE(array_agg(id), '{}')
108
INTO v_payroll_header_ids
109
FROM payroll_transaction_headers
110
WHERE company_id = ANY(v_companies)
111
AND created_on_utc < v_cutoff_24h;
112
113
-- Payment headers (time-gated)
114
SELECT COALESCE(array_agg(id), '{}')
115
INTO v_payment_header_ids
116
FROM payment_headers
117
WHERE company_id = ANY(v_companies)
118
AND created_on_utc < v_cutoff_24h;
119
120
-- Payroll configuration headers
121
SELECT COALESCE(array_agg(id), '{}')
122
INTO v_payroll_conf_header_ids
123
FROM payroll_configuration_headers
124
WHERE company_id = ANY(v_companies);
125
126
--------------------------------------------------------------------
127
-- 3) Purge in strict child → parent order (avoid FK violations)
128
--------------------------------------------------------------------
129
-- Timesheets: hours → lines → sheets
130
DELETE FROM timesheet_hours
131
WHERE timesheet_line_id = ANY(v_timesheet_line_ids);
132
133
DELETE FROM timesheet_lines
134
WHERE id = ANY(v_timesheet_line_ids);
135
136
DELETE FROM timesheets
137
WHERE id = ANY(v_timesheet_ids);
138
139
-- Payroll transactions: details → headers
140
DELETE FROM payroll_transaction_details
141
WHERE payroll_transaction_id = ANY(v_payroll_header_ids);
142
143
DELETE FROM payroll_transaction_headers
144
WHERE id = ANY(v_payroll_header_ids);
145
146
-- Payments: details → headers
147
DELETE FROM payment_details
148
WHERE payment_header_id = ANY(v_payment_header_ids);
149
150
DELETE FROM payment_headers
151
WHERE id = ANY(v_payment_header_ids);
152
153
-- Payroll configuration: details → headers
154
DELETE FROM payroll_configuration_details
155
WHERE configuration_header_id = ANY(v_payroll_conf_header_ids);
156
157
DELETE FROM payroll_configuration_headers
158
WHERE id = ANY(v_payroll_conf_header_ids);
159
160
-- Payroll components (company scoped)
161
DELETE FROM payroll_components
162
WHERE company_id = ANY(v_companies);
163
164
-- Leave/application/records/balances/entitlements before employees
165
DELETE FROM leave_applications
166
WHERE company_id = ANY(v_companies);
167
168
DELETE FROM leave_records
169
WHERE company_id = ANY(v_companies);
170
171
DELETE FROM employee_leave_balances
172
WHERE company_id = ANY(v_companies);
173
174
DELETE FROM leave_entitlements
175
WHERE company_id = ANY(v_companies);
176
177
-- Attendance and HR monetary movements tied to employees
178
DELETE FROM attendance
179
WHERE employee_id = ANY(v_employee_ids);
180
181
DELETE FROM advance_payments
182
WHERE employee_id = ANY(v_employee_ids);
183
184
DELETE FROM benefits
185
WHERE employee_id = ANY(v_employee_ids);
186
187
DELETE FROM deductions
188
WHERE employee_id = ANY(v_employee_ids);
189
190
DELETE FROM loans
191
WHERE employee_id = ANY(v_employee_ids);
192
193
-- Employee bridges: bank accounts and UPIs (junction tables only)
194
DELETE FROM employee_bank_accounts
195
WHERE employee_id = ANY(v_employee_ids);
196
197
DELETE FROM employee_upis
198
WHERE employee_id = ANY(v_employee_ids);
199
200
-- Company-scoped numbering/configs
201
DELETE FROM payroll_transaction_header_ids
202
WHERE company_id = ANY(v_companies);
203
204
DELETE FROM payment_header_ids
205
WHERE company_id = ANY(v_companies);
206
207
-- Company-scoped calendars and workflow
208
DELETE FROM holidays
209
WHERE company_id = ANY(v_companies);
210
211
DELETE FROM workdays
212
WHERE company_id = ANY(v_companies);
213
214
DELETE FROM leave_workflow
215
WHERE company_id = ANY(v_companies);
216
217
-- Timesheet-linked structures already cleared; now tasks/sub-projects/projects
218
DELETE FROM tasks
219
WHERE id = ANY(v_task_ids);
220
221
DELETE FROM sub_projects
222
WHERE id = ANY(v_sub_project_ids);
223
224
DELETE FROM projects
225
WHERE id = ANY(v_project_ids);
226
227
-- Finally, employees
228
DELETE FROM employees
229
WHERE id = ANY(v_employee_ids);
230
231
-- Intentionally NOT deleting users, addresses, or lookup masters
232
-- (component_types, statuses, banks, countries, states, cities, leave_types, leave_statuses)
233
-- to avoid cross-company/global FK breakages.
234
235
RAISE NOTICE 'Payroll purge complete for companies: % (orgs: %).', v_companies, v_orgs;
236
237
COMMIT;
238
239
EXCEPTION
240
WHEN OTHERS THEN
241
ROLLBACK;
242
RAISE NOTICE 'purge_payroll_organization_data failed: %', SQLERRM;
243
-- Optionally rethrow
244
-- RAISE;
245
END;
246
$procedure$
|
|||||
| Procedure | clean_up_org_payroll | Missing in Target |
Source Script
Target Script
1
CREATE OR REPLACE PROCEDURE public.clean_up_org_payroll(IN p_organization_id uuid)
2
LANGUAGE plpgsql
3
AS $procedure$
4
DECLARE
5
v_company_id uuid;
6
v_employee_id uuid;
7
v_payroll_configuration_header_id uuid;
8
v_payroll_transaction_header_id uuid;
9
v_payment_header_id uuid;
10
v_timesheet_id uuid;
11
v_timesheet_line_id uuid;
12
BEGIN
13
FOR v_company_id IN SELECT id FROM companies WHERE organization_id = p_organization_id LOOP
14
15
-- Employees and all related tables
16
FOR v_employee_id IN SELECT id FROM employees WHERE company_id = v_company_id LOOP
17
DELETE FROM benefits WHERE employee_id = v_employee_id;
18
DELETE FROM deductions WHERE employee_id = v_employee_id;
19
DELETE FROM loans WHERE employee_id = v_employee_id;
20
DELETE FROM advance_payments WHERE employee_id = v_employee_id;
21
DELETE FROM employee_bank_accounts WHERE employee_id = v_employee_id;
22
DELETE FROM employee_upis WHERE employee_id = v_employee_id;
23
DELETE FROM leave_applications WHERE employee_id = v_employee_id;
24
DELETE FROM leave_entitlements WHERE employee_id = v_employee_id;
25
DELETE FROM attendance WHERE employee_id = v_employee_id;
26
DELETE FROM payroll_transaction_headers WHERE employee_id = v_employee_id;
27
DELETE FROM payroll_configuration_headers WHERE employee_id = v_employee_id;
28
END LOOP;
29
30
-- Payroll configuration headers/details
31
FOR v_payroll_configuration_header_id IN
32
SELECT id FROM payroll_configuration_headers WHERE company_id = v_company_id
33
LOOP
34
DELETE FROM payroll_configuration_details WHERE configuration_header_id = v_payroll_configuration_header_id;
35
END LOOP;
36
DELETE FROM payroll_configuration_headers WHERE company_id = v_company_id;
37
38
-- Payroll transaction headers/details
39
FOR v_payroll_transaction_header_id IN
40
SELECT id FROM payroll_transaction_headers WHERE company_id = v_company_id
41
LOOP
42
DELETE FROM payroll_transaction_details WHERE payroll_transaction_id = v_payroll_transaction_header_id;
43
END LOOP;
44
DELETE FROM payroll_transaction_headers WHERE company_id = v_company_id;
45
46
-- Payment headers/details
47
FOR v_payment_header_id IN
48
SELECT id FROM payment_headers WHERE company_id = v_company_id
49
LOOP
50
DELETE FROM payment_details WHERE payment_header_id = v_payment_header_id;
51
END LOOP;
52
DELETE FROM payment_headers WHERE company_id = v_company_id;
53
54
DELETE FROM payment_header_ids WHERE company_id = v_company_id;
55
DELETE FROM payroll_transaction_header_ids WHERE company_id = v_company_id;
56
57
-- Leave/workflow/records
58
DELETE FROM leave_workflow WHERE company_id = v_company_id;
59
DELETE FROM holidays WHERE company_id = v_company_id;
60
61
-- Timesheets and details
62
FOR v_timesheet_id IN SELECT id FROM timesheets WHERE company_id = v_company_id LOOP
63
FOR v_timesheet_line_id IN SELECT id FROM timesheet_lines WHERE timesheet_id = v_timesheet_id LOOP
64
DELETE FROM timesheet_hours WHERE timesheet_line_id = v_timesheet_line_id;
65
END LOOP;
66
DELETE FROM timesheet_lines WHERE timesheet_id = v_timesheet_id;
67
END LOOP;
68
DELETE FROM timesheets WHERE company_id = v_company_id;
69
70
-- Tasks/Projects
71
DELETE FROM tasks WHERE company_id = v_company_id;
72
DELETE FROM projects WHERE company_id = v_company_id;
73
74
-- Workdays
75
DELETE FROM workdays WHERE company_id = v_company_id;
76
77
-- Users
78
DELETE FROM users WHERE company_id = v_company_id;
79
80
-- Employees (must come after all dependent deletes)
81
DELETE FROM employees WHERE company_id = v_company_id;
82
83
-- Companies
84
DELETE FROM companies WHERE id = v_company_id;
85
86
RAISE NOTICE 'Deleted payroll data for company_id %', v_company_id;
87
END LOOP;
88
89
-- Organization
90
DELETE FROM organizations WHERE id = p_organization_id;
91
92
RAISE NOTICE 'Deleted payroll data for organization_id %', p_organization_id;
93
END;
94
$procedure$
|
|||||
| Procedure | update_payment_header_posted | Missing in Target |
Source Script
Target Script
1
CREATE OR REPLACE PROCEDURE public.update_payment_header_posted(IN p_payment_header_id uuid, IN p_transaction_id bigint)
2
LANGUAGE plpgsql
3
AS $procedure$
4
BEGIN
5
UPDATE payment_headers
6
SET is_posted = true,
7
transaction_id = p_transaction_id,
8
modified_on_utc = now()
9
WHERE id = p_payment_header_id;
10
END;
11
$procedure$
|
|||||
| Procedure | insert_payment_headers_from_payroll | Missing in Target |
Source Script
Target Script
1
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[])
2
LANGUAGE plpgsql
3
AS $procedure$
4
DECLARE
5
v_total_paid_amount numeric := 0;
6
v_serial_number integer := 1;
7
v_payroll_record RECORD;
8
v_payment_number varchar;
9
BEGIN
10
v_payment_number := get_new_payment_number(
11
p_company_id,
12
p_payment_date::date
13
);
14
15
IF array_length(p_payroll_transaction_ids, 1) IS NULL THEN
16
RAISE NOTICE 'No payroll transaction IDs provided.';
17
RETURN;
18
END IF;
19
20
-- Insert into payment_headers
21
INSERT INTO public.payment_headers (
22
id,
23
company_id,
24
paid_date,
25
paid_amount,
26
tds_amount,
27
advance_amount,
28
description,
29
credit_account_id,
30
debit_account_id,
31
mode_of_payment, -- ✅ dynamic
32
reference,
33
payment_number,
34
created_on_utc,
35
created_by,
36
is_deleted
37
)
38
VALUES (
39
p_payment_header_id,
40
p_company_id,
41
p_payment_date,
42
0,
43
0,
44
0,
45
'Payment of salary',
46
p_credit_account_id,
47
p_debit_account_id,
48
p_mode_of_payment, -- ✅ using parameter
49
p_reference,
50
v_payment_number,
51
NOW(),
52
p_created_by,
53
false
54
);
55
56
-- Insert payment details + update payroll transactions
57
FOR v_payroll_record IN
58
SELECT id, employee_id, net_pay
59
FROM public.payroll_transaction_headers
60
WHERE id = ANY(p_payroll_transaction_ids)
61
LOOP
62
INSERT INTO public.payment_details (
63
id,
64
payment_header_id,
65
employee_id,
66
paid_amount,
67
serial_number,
68
is_deleted
69
)
70
VALUES (
71
gen_random_uuid(),
72
p_payment_header_id,
73
v_payroll_record.employee_id,
74
v_payroll_record.net_pay,
75
v_serial_number,
76
false
77
);
78
79
UPDATE public.payroll_transaction_headers
80
SET status_id = 2
81
WHERE id = v_payroll_record.id;
82
83
v_total_paid_amount := v_total_paid_amount + v_payroll_record.net_pay;
84
v_serial_number := v_serial_number + 1;
85
END LOOP;
86
87
-- Update total paid amount
88
UPDATE public.payment_headers
89
SET paid_amount = v_total_paid_amount
90
WHERE id = p_payment_header_id;
91
92
RAISE NOTICE
93
'Payment Header % inserted with total paid amount %',
94
p_payment_header_id,
95
v_total_paid_amount;
96
END;
97
$procedure$
|
-- Table: payroll_workflow
Exists in source, missing in target
-- Table: departments
Exists in source, missing in target
-- Table: companies
Exists in source, missing in target
-- Table: leave_records
Exists in source, missing in target
-- Table: leave_statuses
Exists in source, missing in target
-- Table: leave_workflow
Exists in source, missing in target
-- Table: payment_headers
Exists in source, missing in target
-- Table: payments
Exists in source, missing in target
-- Table: workdays
Exists in source, missing in target
-- Table: employee_leave_balances
Exists in source, missing in target
-- Table: banks
Exists in source, missing in target
-- Table: __EFMigrationsHistory
Exists in source, missing in target
-- Table: designations
Exists in source, missing in target
-- Table: benefits
Exists in source, missing in target
-- Table: addresses
Exists in source, missing in target
-- Table: advance_payments
Exists in source, missing in target
-- Table: bank_accounts
Exists in source, missing in target
-- Table: cities
Exists in source, missing in target
-- Table: countries
Exists in source, missing in target
-- Table: component_types
Exists in source, missing in target
-- Table: deductions
Exists in source, missing in target
-- Table: attendance
Exists in source, missing in target
-- Table: leave_applications
Exists in source, missing in target
-- Table: loans
Exists in source, missing in target
-- Table: employees
Exists in source, missing in target
-- Table: payroll_transaction_header_ids
Exists in source, missing in target
-- Table: organizations
Exists in source, missing in target
-- Table: payroll_transaction_details
Exists in source, missing in target
-- Table: payment_header_ids
Exists in source, missing in target
-- Table: timesheet_statuses
Exists in source, missing in target
-- Table: sub_projects
Exists in source, missing in target
-- Table: timesheet_lines
Exists in source, missing in target
-- Table: upis
Exists in source, missing in target
-- Table: users
Exists in source, missing in target
-- Table: employee_bank_accounts
Exists in source, missing in target
-- Table: employee_upis
Exists in source, missing in target
-- Table: holidays
Exists in source, missing in target
-- Table: leave_types
Exists in source, missing in target
-- Table: payment_details
Exists in source, missing in target
-- Table: payroll_configuration_headers
Exists in source, missing in target
-- Table: statuses
Exists in source, missing in target
-- Table: timesheet_hours
Exists in source, missing in target
-- Table: leave_entitlements
Exists in source, missing in target
-- Table: payroll_transaction_headers
Exists in source, missing in target
-- Table: payroll_components
Exists in source, missing in target
-- Table: projects
Exists in source, missing in target
-- Table: tasks
Exists in source, missing in target
-- Table: timesheets
Exists in source, missing in target
-- Table: payroll_configuration_details
Exists in source, missing in target
-- Table: states
Exists in source, missing in target
-- Function: delete_payroll_configuration
CREATE OR REPLACE FUNCTION public.delete_payroll_configuration(employee_id_param uuid)
RETURNS void
LANGUAGE plpgsql
AS $function$
BEGIN
-- Step 1: Delete all details associated with the header(s) for the employee
DELETE FROM public.payroll_configuration_details
WHERE configuration_header_id IN (
SELECT id
FROM public.payroll_configuration_headers
WHERE employee_id = employee_id_param
);
-- Step 2: Delete all headers for the employee
DELETE FROM public.payroll_configuration_headers
WHERE employee_id = employee_id_param;
END;
$function$
-- Function: delete_payroll_data
CREATE OR REPLACE FUNCTION public.delete_payroll_data(p_employee_id uuid)
RETURNS void
LANGUAGE plpgsql
AS $function$
BEGIN
-- First, delete the details associated with the employee's payroll transactions
DELETE FROM public.payroll_transaction_details
WHERE payroll_transaction_id IN (
SELECT id FROM public.payroll_transaction_headers
WHERE employee_id = p_employee_id
);
-- Then, delete the headers associated with the employee
DELETE FROM public.payroll_transaction_headers
WHERE employee_id = p_employee_id;
END;
$function$
-- Function: get_all_employee_leave_report
CREATE OR REPLACE FUNCTION public.get_all_employee_leave_report(p_company_id uuid, p_year_id integer)
RETURNS TABLE(employee_id uuid, employee_name text, entitled_leaves integer, consumed_leaves integer, leave_request_in_pending integer, remaining_leaves integer)
LANGUAGE plpgsql
AS $function$
BEGIN
RETURN QUERY
SELECT
e.id AS employee_id,
CONCAT(e.first_name, ' ', e.last_name) AS employee_name,
COALESCE(SUM(le.entitled_leaves)::integer, 0) AS entitled_leaves,
COALESCE(SUM(le.consumed_leaves)::integer, 0) AS consumed_leaves,
COALESCE((SUM(le.entitled_leaves) - SUM(le.consumed_leaves))::integer, 0) AS leave_request_in_pending,
COALESCE((SUM(le.entitled_leaves) - SUM(le.consumed_leaves))::integer, 0) AS remaining_leaves
FROM leave_entitlements le
JOIN employees e ON le.employee_id = e.id
--JOIN employee_company ec ON ec.employee_id = e.id -- adjust as per your schema
WHERE le.company_id = p_company_id
AND le.leave_year = p_year_id
GROUP BY e.id, e.first_name, e.last_name
ORDER BY employee_name;
END;
$function$
-- Function: get_leave_records_with_balance
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$
-- Function: get_payroll
CREATE OR REPLACE FUNCTION public.get_payroll(p_employee_id uuid, p_department_id integer, p_company_id uuid)
RETURNS TABLE(employee_id uuid, employee_name character varying, monthly_ctc numeric, addition numeric, deduction numeric, reimbursements numeric, gross_pay numeric)
LANGUAGE plpgsql
AS $function$
BEGIN
RETURN QUERY
SELECT
e.id AS employee_id,
(e.first_name || ' ' || e.last_name)::VARCHAR AS employee_name,
ROUND(COALESCE(SUM(CASE
WHEN ct.name = 'Basic Salary' THEN pc.amount / 12
ELSE 0 END)::NUMERIC, 0), 2) AS monthly_ctc,
ROUND(COALESCE(SUM(CASE
WHEN ct.name IN ('DA', 'HRA', 'Conveyance Allowance', 'Special Allowance', 'LTA', 'Medical Reimbursement') THEN pc.amount / 12
ELSE 0 END)::NUMERIC, 0), 2) AS addition,
ROUND(COALESCE(SUM(CASE
WHEN ct.name IN ('Professional Tax', 'Provident Fund (PF)', 'Gratuity') THEN pc.amount / 12
ELSE 0 END)::NUMERIC, 0), 2) AS deduction,
0::NUMERIC(15, 2) AS reimbursements, -- Placeholder for reimbursements if not available
ROUND((COALESCE(SUM(CASE
WHEN ct.name = 'Basic Salary' THEN pc.amount / 12
ELSE 0 END), 0)::NUMERIC) +
(COALESCE(SUM(CASE
WHEN ct.name IN ('DA', 'HRA', 'Conveyance Allowance', 'Special Allowance', 'LTA', 'Medical Reimbursement') THEN pc.amount / 12
ELSE 0 END), 0)::NUMERIC) -
(COALESCE(SUM(CASE
WHEN ct.name IN ('Professional Tax', 'Provident Fund (PF)', 'Gratuity') THEN pc.amount / 12
ELSE 0 END), 0)::NUMERIC), 2) AS gross_pay
FROM
employees e
LEFT JOIN
payroll_components pc ON e.id = pc.employee_id
LEFT JOIN
component_types ct ON pc.component_type_id = ct.id
WHERE
(p_employee_id IS NULL OR e.id = p_employee_id) AND
(p_department_id IS NULL OR e.department_id = p_department_id) AND
e.company_id = p_company_id AND
pc.is_deleted = FALSE AND
e.is_deleted = FALSE
GROUP BY
e.id, e.first_name, e.last_name;
END;
$function$
-- Function: pg_get_tabledef
CREATE OR REPLACE FUNCTION public.pg_get_tabledef(p_table_name text)
RETURNS text
LANGUAGE plpgsql
AS $function$
DECLARE
col RECORD;
col_defs TEXT := '';
pk_cols TEXT := '';
result TEXT;
BEGIN
FOR col IN
SELECT
column_name,
data_type,
character_maximum_length,
numeric_precision,
numeric_scale,
is_nullable,
column_default
FROM information_schema.columns
WHERE table_schema = 'public' AND table_name = p_table_name
ORDER BY ordinal_position
LOOP
col_defs := col_defs ||
format('"%s" %s%s%s%s, ',
col.column_name,
CASE
WHEN col.data_type = 'character varying' THEN format('varchar(%s)', col.character_maximum_length)
WHEN col.data_type = 'numeric' THEN format('numeric(%s,%s)', col.numeric_precision, col.numeric_scale)
ELSE col.data_type
END,
CASE WHEN col.column_default IS NOT NULL THEN ' DEFAULT ' || col.column_default ELSE '' END,
CASE WHEN col.is_nullable = 'NO' THEN ' NOT NULL' ELSE '' END,
''
);
END LOOP;
-- Get primary key columns
SELECT string_agg(format('"%s"', kcu.column_name), ', ')
INTO pk_cols
FROM information_schema.table_constraints tc
JOIN information_schema.key_column_usage kcu
ON tc.constraint_name = kcu.constraint_name
WHERE tc.table_schema = 'public'
AND tc.table_name = p_table_name
AND tc.constraint_type = 'PRIMARY KEY';
IF pk_cols IS NOT NULL THEN
col_defs := col_defs || format('PRIMARY KEY (%s), ', pk_cols);
END IF;
col_defs := left(col_defs, length(col_defs) - 2);
result := format('CREATE TABLE "%s" (%s);', p_table_name, col_defs);
RETURN result;
END;
$function$
-- Function: get_all_employee_leave_balances
CREATE OR REPLACE FUNCTION public.get_all_employee_leave_balances(p_employee_id uuid DEFAULT NULL::uuid, p_company_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, company_id uuid)
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,
elb.company_id -- ✅ Added company_id
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)
AND (p_company_id IS NULL OR elb.company_id = p_company_id); -- ✅ Filter by company_id
END;
$function$
-- Function: get_states_by_country
CREATE OR REPLACE FUNCTION public.get_states_by_country(p_country_id uuid)
RETURNS TABLE(id uuid, name text, country_id uuid)
LANGUAGE plpgsql
AS $function$
BEGIN
RETURN QUERY
SELECT
s.id,
s.name,
s.country_id
FROM states s
WHERE s.country_id = p_country_id;
END;
$function$
-- Function: get_all_leave_applications
CREATE OR REPLACE FUNCTION public.get_all_leave_applications(p_company_id uuid)
RETURNS TABLE(leave_application_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
la.id AS leave_application_id,
la.employee_id,
e.first_name || ' ' || e.last_name AS employee_name,
la.leave_type_id,
la.start_date,
la.end_date,
la.reason,
la.leave_status_id,
ls.name::TEXT AS leave_status_name -- Explicit casting to TEXT
FROM leave_applications la
JOIN employees e ON la.employee_id = e.id
JOIN leave_statuses ls ON la.leave_status_id = ls.id
WHERE la.company_id = p_company_id;
END;
$function$
-- Function: get_leave_records_with_balance_also_check_managerid
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$
-- Function: get_all_payroll
CREATE OR REPLACE FUNCTION public.get_all_payroll(p_employee_id uuid, p_department_id integer, p_company_id uuid)
RETURNS TABLE(id uuid, employee_name character varying, basic_salary numeric, addition numeric, deduction numeric, net_pay numeric, components jsonb)
LANGUAGE plpgsql
AS $function$
BEGIN
RETURN QUERY
SELECT
e.id AS id,
(e.first_name || ' ' || e.last_name)::VARCHAR AS employee_name,
-- Basic salary (monthly) : sum of basic amounts divided by 12
ROUND(COALESCE(SUM(CASE WHEN ct.id = 1 THEN (pcd.amount / 12.0) ELSE 0 END), 0)::NUMERIC, 2) AS basic_salary,
-- Addition (monthly): DA, HRA, Conveyance, Special, LTA, Medical => divided by 12
ROUND(COALESCE(SUM(CASE WHEN ct.id IN (2,3,4,5,6,7) THEN (pcd.amount / 12.0) ELSE 0 END), 0)::NUMERIC, 2) AS addition,
-- Deduction (monthly): Professional Tax, PF => divided by 12
ROUND(COALESCE(SUM(CASE WHEN ct.id IN (8,9) THEN (pcd.amount / 12.0) ELSE 0 END), 0)::NUMERIC, 2) AS deduction,
-- Net Pay (monthly) = Basic(monthly) + Addition(monthly) - Deduction(monthly)
ROUND(
COALESCE(SUM(CASE WHEN ct.id = 1 THEN (pcd.amount / 12.0) ELSE 0 END), 0)
+
COALESCE(SUM(CASE WHEN ct.id IN (2,3,4,5,6,7) THEN (pcd.amount / 12.0) ELSE 0 END), 0)
-
COALESCE(SUM(CASE WHEN ct.id IN (9) THEN (pcd.amount / 12.0) ELSE 0 END), 0)
, 2) AS net_pay,
-- Components as JSONB with monthly amounts
jsonb_agg(
jsonb_build_object(
'componentTypeId', ct.id,
'componentTypeName', ct.name,
'amount', ROUND(COALESCE(pcd.amount, 0) / 12.0, 2)
) ORDER BY ct.id
) AS components
FROM public.employees e
LEFT JOIN public.payroll_configuration_headers pch ON e.id = pch.employee_id
LEFT JOIN public.payroll_configuration_details pcd ON pch.id = pcd.configuration_header_id
LEFT JOIN public.component_types ct ON pcd.component_type_id = ct.id
WHERE
(p_employee_id IS NULL OR e.id = p_employee_id) AND
(p_department_id IS NULL OR e.department_id = p_department_id) AND
pch.company_id = p_company_id AND
pch.is_deleted = FALSE AND
e.is_deleted = FALSE
GROUP BY
e.id, pch.employee_id, e.first_name, e.last_name;
END;
$function$
-- Function: get_payroll_config
CREATE OR REPLACE FUNCTION public.get_payroll_config(p_payrolltransaction_id uuid)
RETURNS TABLE(id uuid, employee_id uuid, employee_code character varying, first_name character varying, last_name character varying, email character varying, pan character varying, uan character varying, phone_number character varying, date_of_joining date, department_name character varying, designation_name character varying, pay_period_start date, pay_period_end date, basic_salary numeric, allowances numeric, deductions numeric, net_pay numeric, payment_date date, payment_method_id integer, allowance_components json, deduction_components json)
LANGUAGE plpgsql
AS $function$
BEGIN
RETURN QUERY
SELECT
pth.id,
e.id AS employee_id,
e.employee_code,
e.first_name,
e.last_name,
e.email,
e.pan,
e.uan,
e.phone_number,
e.date_of_joining,
d.name AS department_name,
deg.name AS designation_name,
pth.pay_period_start,
pth.pay_period_end,
pth.basic_salary,
pth.allowances,
pth.deductions,
pth.net_pay,
pth.payment_date,
pth.payment_method_id,
-- JSON aggregation for AllowanceComponents
COALESCE(
json_agg(
jsonb_build_object(
'component_type_id', ptd.component_type_id,
'component_name', ct.name,
'component_amount', ptd.amount
)
) FILTER (
WHERE ct.is_allowance = true
AND ct.name IS NOT NULL
AND ptd.amount IS NOT NULL
),
'[]'
) AS allowance_components,
-- JSON aggregation for DeductionComponents
COALESCE(
json_agg(
jsonb_build_object(
'component_type_id', ptd.component_type_id,
'component_name', ct.name,
'component_amount', ptd.amount
)
) FILTER (
WHERE ct.is_allowance = false
AND ct.name IS NOT NULL
AND ptd.amount IS NOT NULL
),
'[]'
) AS deduction_components
FROM
employees e
LEFT JOIN payroll_transaction_headers pth
ON e.id = pth.employee_id
LEFT JOIN departments d
ON e.department_id = d.id
LEFT JOIN designations deg
ON e.designation_id = deg.id
LEFT JOIN payroll_transaction_details ptd
ON pth.id = ptd.payroll_transaction_id
LEFT JOIN component_types ct
ON ptd.component_type_id = ct.id
WHERE
pth.id = p_payrollTransaction_id
AND e.is_deleted = false
AND pth.is_deleted = false
GROUP BY
pth.id, e.id, e.employee_code, e.first_name, e.last_name,
e.email, e.pan, e.uan, e.phone_number, e.date_of_joining,
d.name, deg.name, pth.pay_period_start, pth.pay_period_end,
pth.basic_salary, pth.allowances, pth.deductions,
pth.net_pay, pth.payment_date, pth.payment_method_id;
END;
$function$
-- Function: get_employee_leave_summary
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$
-- Function: get_employee_leave_summarytemp
CREATE OR REPLACE FUNCTION public.get_employee_leave_summarytemp(p_company_id uuid, p_fin_year integer)
RETURNS TABLE(employee_id uuid, employee_name text, entitled_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
le.employee_id,
CAST(SUM(le.entitled_leaves) AS integer) AS entitled_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(le.entitled_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 leave_entitlements le
LEFT JOIN leave_records lr
ON lr.employee_id = le.employee_id
AND lr.leave_type_id = le.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 le.company_id = p_company_id
GROUP BY le.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.entitled_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$
-- Function: get_all_leave_records
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$
-- Function: grant_full_schema_access
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$
-- Function: update_payment_header_posted_fn
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$
-- Function: get_employee_leave_balance
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$
-- Function: get_new_payment_number
CREATE OR REPLACE FUNCTION public.get_new_payment_number(p_company_id uuid, p_date date)
RETURNS character varying
LANGUAGE plpgsql
AS $function$
DECLARE
v_finance_year_id integer;
new_payment_id integer;
p_prefix varchar;
payment_number varchar;
BEGIN
-- Determine the start year of the financial year based on p_date
IF EXTRACT(MONTH FROM p_date) >= 4 THEN
v_finance_year_id := EXTRACT(YEAR FROM p_date);
ELSE
v_finance_year_id := EXTRACT(YEAR FROM p_date) - 1;
END IF;
-- Attempt to update; if nothing is updated, insert a new row
WITH x AS (
UPDATE public.payment_header_ids
SET last_payment_id = COALESCE(last_payment_id, 0) + 1
WHERE company_id = p_company_id AND fin_year = v_finance_year_id
RETURNING last_payment_id, payment_prefix
),
insert_x AS (
INSERT INTO public.payment_header_ids (
company_id, fin_year, payment_prefix, last_payment_id, payment_length
)
SELECT p_company_id, v_finance_year_id, 'PAY', 1, 8
WHERE NOT EXISTS (SELECT 1 FROM x)
RETURNING last_payment_id, payment_prefix
)
SELECT
COALESCE((SELECT last_payment_id FROM x LIMIT 1), (SELECT last_payment_id FROM insert_x LIMIT 1)),
COALESCE((SELECT payment_prefix FROM x LIMIT 1), (SELECT payment_prefix FROM insert_x LIMIT 1))
INTO new_payment_id, p_prefix;
-- Generate the final payment number
payment_number := p_prefix || LPAD(new_payment_id::text, 4, '0');
RETURN payment_number;
END;
$function$
-- Function: get_payroll_transactions_by_company
CREATE OR REPLACE FUNCTION public.get_payroll_transactions_by_company(p_company_id uuid, p_financial_year_id integer)
RETURNS TABLE(payroll_id uuid, employee_name text, pay_period_start date, pay_period_end date, basic_salary numeric, allowances numeric, deductions numeric, net_pay numeric, payment_date date, status_name text, status_id integer, has_linked_account boolean)
LANGUAGE plpgsql
AS $function$
BEGIN
RETURN QUERY
SELECT
pth.id AS payroll_id,
e.first_name || ' ' || e.last_name AS employee_name,
pth.pay_period_start,
pth.pay_period_end,
pth.basic_salary,
pth.allowances,
pth.deductions,
pth.net_pay,
pth.payment_date,
s.name::TEXT AS status_name, -- 🔹 Cast explicitly to TEXT
pth.status_id,
CASE WHEN ba.rp_linked_account_id IS NOT NULL THEN TRUE ELSE FALSE END AS has_linked_account
FROM public.payroll_transaction_headers pth
JOIN public.employees e ON pth.employee_id = e.id
JOIN public.statuses s ON pth.status_id = s.id
LEFT JOIN public.employee_bank_accounts eb ON e.id = eb.employee_id
LEFT JOIN public.bank_accounts ba ON eb.bank_account_id = ba.id
WHERE pth.company_id = p_company_id
AND EXTRACT(YEAR FROM pth.payment_date) = p_financial_year_id
AND EXTRACT(MONTH FROM pth.payment_date) BETWEEN 4 AND 12 -- Transactions from April to December in the same year
OR (EXTRACT(YEAR FROM pth.payment_date) = p_financial_year_id + 1
AND EXTRACT(MONTH FROM pth.payment_date) BETWEEN 1 AND 3);
END;
$function$
-- Function: get_leave_applications_with_balance
CREATE OR REPLACE FUNCTION public.get_leave_applications_with_balance(p_company_id uuid, p_leave_year integer, p_employee_id uuid DEFAULT NULL::uuid)
RETURNS TABLE(leave_application_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, entitled_leaves integer, consumed_leaves integer, remaining_leaves integer)
LANGUAGE plpgsql
AS $function$
DECLARE
financial_year_start date := make_date(p_leave_year, 4, 1);
financial_year_end date := make_date(p_leave_year + 1, 3, 31);
is_manager boolean := false;
BEGIN
-- Check if employee is a manager
IF p_employee_id IS NOT NULL THEN
SELECT EXISTS (
SELECT 1 FROM employees emp WHERE emp.manager_id = p_employee_id
) INTO is_manager;
END IF;
RETURN QUERY
SELECT
la.id AS leave_application_id,
la.employee_id,
CONCAT(e.first_name, ' ', e.last_name) AS employee_name,
e.manager_id, -- ✅ fully qualified
lt.id AS leave_type_id,
lt.name::TEXT AS leave_type_name,
la.start_date,
la.end_date,
la.reason,
ls.id AS leave_status_id,
ls.name::TEXT AS leave_status,
COALESCE(elb.entitled_leaves, 0) AS entitled_leaves,
COALESCE(elb.consumed_leaves, 0) AS consumed_leaves,
COALESCE(elb.entitled_leaves - elb.consumed_leaves, 0) AS remaining_leaves
FROM leave_applications la
INNER JOIN employees e ON la.employee_id = e.id
INNER JOIN leave_statuses ls ON la.leave_status_id = ls.id
INNER JOIN leave_types lt ON la.leave_type_id = lt.id
LEFT JOIN (
SELECT
le_inner.employee_id,
le_inner.leave_type_id,
le_inner.entitled_leaves,
le_inner.consumed_leaves
FROM (
SELECT
le.employee_id,
le.leave_type_id,
le.entitled_leaves,
le.consumed_leaves,
ROW_NUMBER() OVER (
PARTITION BY le.employee_id, le.leave_type_id
ORDER BY le.id DESC
) AS rn
FROM leave_entitlements le
) le_inner
WHERE le_inner.rn = 1
) elb
ON la.employee_id = elb.employee_id
AND la.leave_type_id = elb.leave_type_id
WHERE la.company_id = p_company_id
AND (la.is_deleted IS NULL OR la.is_deleted = FALSE)
AND la.start_date <= financial_year_end
AND la.end_date >= financial_year_start
AND (
p_employee_id IS NULL
OR (
is_manager = TRUE AND (
la.employee_id = p_employee_id
OR la.employee_id IN (
SELECT emp.id FROM employees emp WHERE emp.manager_id = p_employee_id
)
)
)
OR (
is_manager = FALSE AND la.employee_id = p_employee_id
)
);
END;
$function$
-- Function: get_all_leave_entitlements
CREATE OR REPLACE FUNCTION public.get_all_leave_entitlements(p_employee_id uuid DEFAULT NULL::uuid, p_company_id uuid DEFAULT NULL::uuid, p_leave_year integer DEFAULT NULL::integer)
RETURNS TABLE(employee_id uuid, employee_name text, sick_leave text, casual_leave text, earned_leave text, maternity_leave text, paternity_leave text, company_id uuid, leave_year integer)
LANGUAGE plpgsql
AS $function$
DECLARE
is_manager BOOLEAN := FALSE;
BEGIN
-- Check if employee is a manager
IF p_employee_id IS NOT NULL THEN
SELECT EXISTS (
SELECT 1 FROM employees WHERE manager_id = p_employee_id
) INTO is_manager;
END IF;
RETURN QUERY
SELECT
le.employee_id,
CONCAT(e.first_name, ' ', e.last_name) AS employee_name,
COALESCE(
MAX(CASE WHEN lt.name = 'Sick Leave' THEN
CONCAT('Total: ', COALESCE(le.entitled_leaves, 0),
', Used: ', COALESCE(le.consumed_leaves, 0),
', Remaining: ', COALESCE(le.entitled_leaves - le.consumed_leaves, 0))
END),
'Total: 0, Used: 0, Remaining: 0'
) AS sick_leave,
COALESCE(
MAX(CASE WHEN lt.name = 'Casual Leave' THEN
CONCAT('Total: ', COALESCE(le.entitled_leaves, 0),
', Used: ', COALESCE(le.consumed_leaves, 0),
', Remaining: ', COALESCE(le.entitled_leaves - le.consumed_leaves, 0))
END),
'Total: 0, Used: 0, Remaining: 0'
) AS casual_leave,
COALESCE(
MAX(CASE WHEN lt.name = 'Earned Leave' THEN
CONCAT('Total: ', COALESCE(le.entitled_leaves, 0),
', Used: ', COALESCE(le.consumed_leaves, 0),
', Remaining: ', COALESCE(le.entitled_leaves - le.consumed_leaves, 0))
END),
'Total: 0, Used: 0, Remaining: 0'
) AS earned_leave,
COALESCE(
MAX(CASE WHEN lt.name = 'Maternity Leave' THEN
CONCAT('Total: ', COALESCE(le.entitled_leaves, 0),
', Used: ', COALESCE(le.consumed_leaves, 0),
', Remaining: ', COALESCE(le.entitled_leaves - le.consumed_leaves, 0))
END),
'Total: 0, Used: 0, Remaining: 0'
) AS maternity_leave,
COALESCE(
MAX(CASE WHEN lt.name = 'Paternity Leave' THEN
CONCAT('Total: ', COALESCE(le.entitled_leaves, 0),
', Used: ', COALESCE(le.consumed_leaves, 0),
', Remaining: ', COALESCE(le.entitled_leaves - le.consumed_leaves, 0))
END),
'Total: 0, Used: 0, Remaining: 0'
) AS paternity_leave,
le.company_id,
le.leave_year
FROM leave_entitlements le
JOIN leave_types lt ON le.leave_type_id = lt.id
JOIN employees e ON le.employee_id = e.id
WHERE (
p_employee_id IS NULL
OR (
is_manager = TRUE AND (
le.employee_id = p_employee_id OR
le.employee_id IN (
SELECT emp.id FROM employees emp WHERE emp.manager_id = p_employee_id
)
)
)
OR (
is_manager = FALSE AND le.employee_id = p_employee_id
)
)
AND (p_company_id IS NULL OR le.company_id = p_company_id)
AND (p_leave_year IS NULL OR le.leave_year = p_leave_year)
GROUP BY le.employee_id, e.first_name, e.last_name, le.company_id, le.leave_year;
END;
$function$
-- Function: get_all_tasks
CREATE OR REPLACE FUNCTION public.get_all_tasks(p_company_id uuid)
RETURNS TABLE(task_id integer, task_name text, project_id uuid, sub_project_id integer, planned_hours integer, created_on_utc timestamp without time zone, modified_on_utc timestamp without time zone, created_by uuid, modified_by uuid)
LANGUAGE plpgsql
AS $function$
BEGIN
RETURN QUERY
SELECT
t.id, -- id (INTEGER)
t.name, -- name (TEXT)
t.project_id, -- project_id (UUID)
t.sub_project_id, -- sub_project_id (INTEGER)
t.planned_hours, -- planned_hours (INTEGER)
t.created_on_utc, -- created_on_utc (TIMESTAMP)
t.modified_on_utc, -- modified_on_utc (TIMESTAMP)
t.created_by, -- created_by (UUID)
t.modified_by -- modified_by (UUID)
FROM tasks t
WHERE t.company_id = p_company_id
AND t.is_deleted = false; -- Filtering out deleted tasks
END;
$function$
-- Function: get_employee_leave_entitlement
CREATE OR REPLACE FUNCTION public.get_employee_leave_entitlement(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, entitled_leaves integer, consumed_leaves integer, remaining_leaves integer)
LANGUAGE plpgsql
AS $function$
BEGIN
RETURN QUERY
SELECT
gen_random_uuid() AS id, -- Auto-generate UUID for each row
le.employee_id,
CONCAT(e.first_name, ' ', e.last_name) AS employee_name,
le.leave_type_id,
lt.name::TEXT AS leave_type_name,
COALESCE(le.entitled_leaves, 0) AS entitled_leaves, -- Reversed back to entitled_leaves
COALESCE(le.consumed_leaves, 0) AS consumed_leaves, -- Reversed back to consumed_leaves
COALESCE(le.entitled_leaves - le.consumed_leaves, 0) AS remaining_leaves
FROM leave_entitlements le
JOIN leave_types lt ON le.leave_type_id = lt.id
JOIN employees e ON le.employee_id = e.id
WHERE (p_employee_id IS NULL OR le.employee_id = p_employee_id); -- Filter by employee_id only
END;
$function$
-- Function: get_leave_applications_with_balance
CREATE OR REPLACE FUNCTION public.get_leave_applications_with_balance(p_company_id uuid, p_leave_year integer)
RETURNS TABLE(leave_application_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, entitled_leaves integer, consumed_leaves integer, remaining_leaves integer)
LANGUAGE plpgsql
AS $function$
BEGIN
RETURN QUERY
SELECT
la.id AS leave_application_id,
la.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,
la.start_date,
la.end_date,
la.reason,
ls.id AS leave_status_id,
ls.name::text AS leave_status,
COALESCE(le.entitled_leaves, 0) AS entitled_leaves,
COALESCE(le.consumed_leaves, 0) AS consumed_leaves,
COALESCE(le.entitled_leaves, 0) - COALESCE(le.consumed_leaves, 0) AS remaining_leaves
FROM leave_applications la
INNER JOIN employees e ON la.employee_id = e.id
INNER JOIN leave_statuses ls ON la.leave_status_id = ls.id
INNER JOIN leave_types lt ON la.leave_type_id = lt.id
LEFT JOIN leave_entitlements le
ON le.employee_id = la.employee_id
AND le.leave_type_id = la.leave_type_id
AND le.company_id = la.company_id
AND le.leave_year = p_leave_year
WHERE la.company_id = p_company_id
AND (la.is_deleted IS NULL OR la.is_deleted = FALSE)
AND EXTRACT(YEAR FROM la.start_date) = p_leave_year;
END;
$function$
-- Function: get_new_payroll_transaction_number
CREATE OR REPLACE FUNCTION public.get_new_payroll_transaction_number(p_company_id uuid, p_date date)
RETURNS character varying
LANGUAGE plpgsql
AS $function$
DECLARE
v_finance_year_id integer; -- Financial year start year
new_payroll_id integer; -- New payroll transaction ID
p_prefix varchar; -- Payroll prefix
payroll_number varchar;
BEGIN
-- Determine the start year of the financial year based on p_date
IF EXTRACT(MONTH FROM p_date) >= 4 THEN
v_finance_year_id := EXTRACT(YEAR FROM p_date);
ELSE
v_finance_year_id := EXTRACT(YEAR FROM p_date) - 1;
END IF;
-- Try to update the ID or insert a new row if it doesn't exist
WITH x AS (
UPDATE public.payroll_transaction_header_ids
SET last_payroll_id = COALESCE(last_payroll_id, 0) + 1
WHERE company_id = p_company_id AND fin_year = v_finance_year_id
RETURNING last_payroll_id, payroll_prefix
),
insert_x AS (
INSERT INTO public.payroll_transaction_header_ids (
company_id, fin_year, payroll_prefix, last_payroll_id, payroll_length
)
SELECT p_company_id, v_finance_year_id, 'PAYROLL', 1, 8
WHERE NOT EXISTS (SELECT 1 FROM x)
RETURNING last_payroll_id, payroll_prefix
)
SELECT
COALESCE((SELECT last_payroll_id FROM x LIMIT 1), (SELECT last_payroll_id FROM insert_x LIMIT 1)),
COALESCE((SELECT payroll_prefix FROM x LIMIT 1), (SELECT payroll_prefix FROM insert_x LIMIT 1))
INTO new_payroll_id, p_prefix;
-- Generate the payroll transaction number
payroll_number := p_prefix || LPAD(new_payroll_id::text, 4, '0');
RETURN payroll_number;
END;
$function$
-- Function: get_payroll_transactions_by_fin_year_num
CREATE OR REPLACE FUNCTION public.get_payroll_transactions_by_fin_year_num(p_company_id uuid, p_fin_year integer)
RETURNS TABLE(id uuid, has_rp_linked_account boolean, employee_name text, pay_period_start date, pay_period_end date, basic_salary numeric, allowances numeric, deductions numeric, payment_date date, status_name character varying, status_id integer, net_pay numeric)
LANGUAGE plpgsql
AS $function$
DECLARE
v_start_date date := make_date(p_fin_year, 4, 1);
v_end_date date := make_date(p_fin_year + 1, 3, 31);
BEGIN
RETURN QUERY
SELECT
pth.id,
(ba.rp_linked_account_id IS NOT NULL AND ba.rp_linked_account_id <> '') AS has_rp_linked_account,
e.first_name || ' ' || e.last_name AS employee_name,
pth.pay_period_start,
pth.pay_period_end,
pth.basic_salary,
pth.allowances,
pth.deductions,
pth.payment_date,
s.name AS status_name,
pth.status_id,
pth.net_pay
FROM public.payroll_transaction_headers pth
JOIN public.employees e ON pth.employee_id = e.id
JOIN public.statuses s ON pth.status_id = s.id
LEFT JOIN public.employee_bank_accounts eba ON eba.employee_id = e.id
LEFT JOIN public.bank_accounts ba ON ba.id = eba.bank_account_id
WHERE pth.company_id = p_company_id
AND pth.pay_period_start >= v_start_date
AND pth.pay_period_end <= v_end_date
AND pth.is_deleted = false
AND s.is_deleted = false
ORDER BY pth.payment_date;
END;
$function$
-- Function: get_holidays_with_user_names
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$
-- Function: get_leave_application_with_balance_also_check_managerid
CREATE OR REPLACE FUNCTION public.get_leave_application_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)
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
-- Check if p_employee_id is a manager in the company
SELECT EXISTS (
SELECT 1 FROM employees WHERE manager_id = p_employee_id AND company_id = p_company_id
) INTO is_manager;
RETURN QUERY
SELECT
la.id AS leave_record_id,
la.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,
la.start_date,
la.end_date,
la.reason,
ls.id AS leave_status_id,
ls.name::TEXT AS leave_status,
COALESCE(ele.total_leaves, 0) AS total_leaves,
COALESCE(ele.used_leaves, 0) AS used_leaves,
COALESCE(ele.total_leaves - ele.used_leaves, 0) AS remaining_leaves
FROM leave_application la
INNER JOIN employees e
ON la.employee_id = e.id
INNER JOIN leave_statuses ls
ON la.leave_status_id = ls.id
INNER JOIN leave_types lt
ON la.leave_type_id = lt.id
LEFT JOIN (
SELECT
ele_inner.employee_id,
ele_inner.leave_type_id,
ele_inner.total_leaves,
ele_inner.used_leaves
FROM (
SELECT
ele.employee_id,
ele.leave_type_id,
ele.total_leaves,
ele.used_leaves,
ROW_NUMBER() OVER (
PARTITION BY ele.employee_id, ele.leave_type_id
ORDER BY ele.id DESC
) AS rn
FROM employee_leave_entitlement ele
) ele_inner
WHERE ele_inner.rn = 1
) ele
ON la.employee_id = ele.employee_id
AND la.leave_type_id = ele.leave_type_id
WHERE la.company_id = p_company_id
AND (la.is_deleted IS NULL OR la.is_deleted = FALSE)
AND la.start_date <= financial_year_end
AND la.end_date >= financial_year_start
AND (
is_manager = true
OR la.employee_id = p_employee_id
);
END;
$function$
-- Function: update_payroll_status
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$
-- Procedure: delete_payments_by_company
CREATE OR REPLACE PROCEDURE public.delete_payments_by_company(IN p_company_id uuid)
LANGUAGE plpgsql
AS $procedure$
BEGIN
-- Delete payment details first (to avoid foreign key violations)
DELETE FROM public.payment_details
WHERE payment_header_id IN (
SELECT id FROM public.payment_headers WHERE company_id = p_company_id
);
-- Optionally delete from payment_headers (if required)
DELETE FROM public.payment_headers WHERE company_id = p_company_id;
RAISE NOTICE 'Deleted payments for Company ID: %', p_company_id;
END;
$procedure$
-- Procedure: delete_payroll_and_payment_data
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$
-- Procedure: delete_payroll_transactions_by_company
CREATE OR REPLACE PROCEDURE public.delete_payroll_transactions_by_company(IN p_company_id uuid)
LANGUAGE plpgsql
AS $procedure$
BEGIN
-- Step 1: Delete payroll transaction details linked to headers
DELETE FROM payroll_transaction_details
WHERE payroll_transaction_id IN (
SELECT id FROM payroll_transaction_headers
WHERE company_id = p_company_id AND status_id = 2
);
-- Step 2: Delete payroll transaction headers
DELETE FROM payroll_transaction_headers
WHERE company_id = p_company_id AND status_id = 2;
-- Log the deletion
RAISE NOTICE 'Deleted payroll transactions for Company ID: %', p_company_id;
END;
$procedure$
-- Procedure: initialize_payroll_transaction_header_ids
CREATE OR REPLACE PROCEDURE public.initialize_payroll_transaction_header_ids(IN p_default_company_id uuid, IN new_company_id uuid)
LANGUAGE plpgsql
AS $procedure$
DECLARE
v_header_exists BOOLEAN;
BEGIN
-- Check if payroll transaction header IDs already exist for the new company
SELECT EXISTS (
SELECT 1
FROM payroll_transaction_header_ids
WHERE company_id = new_company_id
) INTO v_header_exists;
IF NOT v_header_exists THEN
INSERT INTO payroll_transaction_header_ids (
id,
company_id,
fin_year,
payroll_prefix,
payroll_length,
last_payroll_id
)
SELECT
nextval('payroll_transaction_header_ids_id_seq'),
new_company_id,
fin_year,
payroll_prefix,
payroll_length,
last_payroll_id
FROM payroll_transaction_header_ids
WHERE company_id = p_default_company_id;
RAISE NOTICE 'Payroll transaction header IDs have been copied from company % to company %.', p_default_company_id, new_company_id;
ELSE
RAISE NOTICE 'Payroll transaction header IDs already exist for company %. Skipping initialization.', new_company_id;
END IF;
END;
$procedure$
-- Procedure: hard_delete_organization
CREATE OR REPLACE PROCEDURE public.hard_delete_organization(IN p_organization_id uuid)
LANGUAGE plpgsql
AS $procedure$
DECLARE
p_company_id UUID; -- Variable to hold company IDs associated with the organization
BEGIN
-- Get the associated company IDs for the organization
FOR p_company_id IN
SELECT c.id FROM public.companies c WHERE c.organization_id = p_organization_id
LOOP
-- Delete from payment_header_ids
DELETE FROM payment_header_ids
WHERE payment_header_ids.company_id = p_company_id;
-- Delete from payroll_transaction_header_ids
DELETE FROM payroll_transaction_header_ids
WHERE payroll_transaction_header_ids.company_id = p_company_id;
-- Delete from leave_workflow
DELETE FROM leave_workflow
WHERE leave_workflow.company_id = p_company_id;
-- Delete users associated with the company
DELETE FROM public.users
WHERE users.company_id = p_company_id;
-- Delete from companies
DELETE FROM public.companies
WHERE public.companies.id = p_company_id;
END LOOP;
-- Delete the organization itself
DELETE FROM public.organizations
WHERE public.organizations.id = p_organization_id;
-- Log the operation
RAISE NOTICE 'Organization with ID % and all related data have been hard deleted.', p_organization_id;
END;
$procedure$
-- Procedure: initialize_company
CREATE OR REPLACE PROCEDURE public.initialize_company(IN p_company_id uuid, IN p_organization_id uuid, IN p_is_apartment boolean, IN p_name text, IN p_created_by uuid, IN p_default_company_id uuid)
LANGUAGE plpgsql
AS $procedure$
DECLARE
v_company_exists boolean;
BEGIN
-- Check if company already exists
SELECT EXISTS (
SELECT 1 FROM public.companies WHERE id = p_company_id
) INTO v_company_exists;
IF NOT v_company_exists THEN
-- Insert into companies table ONLY if it doesn't exist
INSERT INTO public.companies (
id,
organization_id,
is_apartment,
name,
created_on_utc,
created_by
) VALUES (
p_company_id,
p_organization_id,
p_is_apartment,
p_name,
NOW(),
p_created_by
);
RAISE NOTICE 'Initialized company: % with ID: %', p_name, p_company_id;
ELSE
RAISE NOTICE 'Company with ID % already exists. Skipping other initializations.', p_company_id;
END IF;
-- Call other procedures
CALL public.initialize_leave_workflow(
p_default_company_id,
p_company_id,
p_created_by
);
CALL public.initialize_payroll_transaction_header_ids(
p_default_company_id,
p_company_id
);
CALL public.initialize_payment_header_ids(
p_default_company_id,
p_company_id
);
END;
$procedure$
-- Procedure: initialize_leave_workflow
CREATE OR REPLACE PROCEDURE public.initialize_leave_workflow(IN p_default_company_id uuid, IN new_company_id uuid, IN p_created_by uuid)
LANGUAGE plpgsql
AS $procedure$
DECLARE
v_workflow_exists BOOLEAN;
BEGIN
-- Check if leave workflow entries already exist for the new company
SELECT EXISTS (
SELECT 1
FROM leave_workflow
WHERE company_id = new_company_id
) INTO v_workflow_exists;
IF NOT v_workflow_exists THEN
-- Insert new records for the new company
INSERT INTO leave_workflow (
id,
company_id,
status_id,
next_status_id,
previous_status_id,
is_initial,
created_on_utc,
created_by
)
SELECT
nextval('leave_workflow_id_seq'), -- Generate new sequential ID
new_company_id, -- Assign the new company ID
status_id,
next_status_id,
previous_status_id,
is_initial,
created_on_utc,
p_created_by
FROM leave_workflow
WHERE company_id = p_default_company_id;
-- Optional: Log the operation
RAISE NOTICE 'Leave workflow records have been copied from company % to company % by user %.', p_default_company_id, new_company_id, p_created_by;
ELSE
RAISE NOTICE 'Leave workflow records already exist for company %. Skipping initialization.', new_company_id;
END IF;
END;
$procedure$
-- Procedure: create_payroll_transaction
CREATE OR REPLACE PROCEDURE public.create_payroll_transaction(IN p_payroll_id uuid, IN p_company_id uuid, IN p_employee_id uuid, IN p_pay_period_start date, IN p_pay_period_end date, IN p_basic_salary numeric, IN p_allowances numeric, IN p_deductions numeric, IN p_net_pay numeric, IN p_date date, IN p_payment_method_id integer, IN p_status_id integer, IN p_created_by uuid, IN p_components_data jsonb)
LANGUAGE plpgsql
AS $procedure$
DECLARE
v_component_type_id INTEGER;
v_component_amount NUMERIC(15,2);
v_payroll_number CHARACTER VARYING;
v_existing_header_id UUID;
BEGIN
-- Check if a payroll transaction header already exists for the employee, pay period, and company
SELECT id INTO v_existing_header_id
FROM public.payroll_transaction_headers
WHERE employee_id = p_employee_id
AND pay_period_start = p_pay_period_start
AND pay_period_end = p_pay_period_end
AND company_id = p_company_id
AND is_deleted = false;
v_payroll_number := get_new_payroll_transaction_number(p_company_id, p_date);
IF v_existing_header_id IS NOT NULL THEN
-- Update the existing payroll transaction header
UPDATE public.payroll_transaction_headers
SET
basic_salary = p_basic_salary,
allowances = p_allowances,
deductions = p_deductions,
net_pay = p_net_pay,
payment_date = p_date,
payment_method_id = p_payment_method_id,
status_id = p_status_id,
modified_by = p_created_by, -- added modified by
modified_on_utc = NOW() -- added modified on
WHERE id = v_existing_header_id;
-- Delete existing details and insert new ones.
DELETE FROM public.payroll_transaction_details
WHERE payroll_transaction_id = v_existing_header_id;
RAISE NOTICE 'Updated payroll transaction header with ID: %', v_existing_header_id;
ELSE
-- Insert a new payroll transaction header
INSERT INTO public.payroll_transaction_headers (
id,
company_id,
employee_id,
pay_period_start,
pay_period_end,
basic_salary,
allowances,
deductions,
net_pay,
payment_date,
payment_method_id,
created_by,
created_on_utc,
status_id,
payroll_number,
is_deleted
)
VALUES (
p_payroll_id,
p_company_id,
p_employee_id,
p_pay_period_start,
p_pay_period_end,
p_basic_salary,
p_allowances,
p_deductions,
p_net_pay,
p_date,
p_payment_method_id,
p_created_by,
NOW(),
p_status_id,
v_payroll_number,
false
);
RAISE NOTICE 'Inserted new payroll transaction header with ID: %', p_payroll_id;
END IF;
-- Loop through the component data for each employee and insert into details
FOR v_component_type_id, v_component_amount IN
SELECT
(component ->> 'component_type_id')::INTEGER,
(component ->> 'amount')::NUMERIC
FROM jsonb_array_elements(p_components_data) AS component
LOOP
INSERT INTO public.payroll_transaction_details (
id,
payroll_transaction_id,
component_type_id,
amount
)
VALUES (
gen_random_uuid(),
COALESCE(v_existing_header_id, p_payroll_id), -- Use existing or new header ID
v_component_type_id,
v_component_amount
);
END LOOP;
END;
$procedure$
-- Procedure: save_payroll_transactions
CREATE OR REPLACE PROCEDURE public.save_payroll_transactions(IN p_company_id uuid, IN p_date date, IN p_created_by uuid, IN p_payrolls_data jsonb)
LANGUAGE plpgsql
AS $procedure$
DECLARE
v_payroll JSONB;
v_payroll_id UUID;
v_employee_id UUID;
v_pay_period_start DATE;
v_pay_period_end DATE;
v_basic_salary NUMERIC;
v_allowances NUMERIC;
v_deductions NUMERIC;
v_net_pay NUMERIC;
v_payment_method_id INTEGER;
v_status_id INTEGER;
v_components_data JSONB;
BEGIN
-- Loop through each payroll entry in the array
FOR v_payroll IN SELECT * FROM jsonb_array_elements(p_payrolls_data)
LOOP
-- Extract payroll details from JSONB data
v_payroll_id := gen_random_uuid();
v_employee_id := (v_payroll ->> 'employee_id')::UUID;
v_pay_period_start := (v_payroll ->> 'pay_period_start')::DATE;
v_pay_period_end := (v_payroll ->> 'pay_period_end')::DATE;
v_basic_salary := (v_payroll ->> 'basic_salary')::NUMERIC;
v_allowances := (v_payroll ->> 'allowances')::NUMERIC;
v_deductions := (v_payroll ->> 'deductions')::NUMERIC;
v_net_pay := (v_payroll ->> 'net_pay')::NUMERIC;
v_payment_method_id := (v_payroll ->> 'payment_method_id')::INTEGER;
v_status_id := (v_payroll ->> 'status_id')::INTEGER;
v_components_data := v_payroll -> 'components';
-- Call the existing procedure for each payroll
CALL public.create_payroll_transaction(
v_payroll_id,
p_company_id,
v_employee_id,
v_pay_period_start,
v_pay_period_end,
v_basic_salary,
v_allowances,
v_deductions,
v_net_pay,
p_date,
v_payment_method_id,
v_status_id,
p_created_by,
v_components_data
);
END LOOP;
END;
$procedure$
-- Procedure: hard_delete_org_payroll
CREATE OR REPLACE PROCEDURE public.hard_delete_org_payroll(IN p_organization_id uuid)
LANGUAGE plpgsql
AS $procedure$
DECLARE
v_company_id uuid;
BEGIN
FOR v_company_id IN SELECT id FROM companies WHERE organization_id = p_organization_id LOOP
-- Transactional and dependent records first
DELETE FROM employee_leave_balances WHERE company_id = v_company_id;
DELETE FROM leave_workflow WHERE company_id = v_company_id;
DELETE FROM leave_records WHERE company_id = v_company_id;
DELETE FROM holidays WHERE company_id = v_company_id;
DELETE FROM timesheets WHERE company_id = v_company_id;
DELETE FROM tasks WHERE company_id = v_company_id;
DELETE FROM projects WHERE company_id = v_company_id;
DELETE FROM payroll_transaction_header_ids WHERE company_id = v_company_id;
DELETE FROM payroll_transaction_headers WHERE company_id = v_company_id;
DELETE FROM payroll_configuration_headers WHERE company_id = v_company_id;
DELETE FROM payment_header_ids WHERE company_id = v_company_id;
DELETE FROM payment_headers WHERE company_id = v_company_id;
DELETE FROM employees WHERE company_id = v_company_id;
DELETE FROM workdays WHERE company_id = v_company_id;
DELETE FROM users WHERE company_id = v_company_id;
DELETE FROM companies WHERE id = v_company_id;
RAISE NOTICE 'Deleted payroll data for company_id %', v_company_id;
END LOOP;
DELETE FROM organizations WHERE id = p_organization_id;
RAISE NOTICE 'Deleted payroll data for organization_id %', p_organization_id;
END;
$procedure$
-- Procedure: initialize_organization
CREATE OR REPLACE PROCEDURE public.initialize_organization(IN p_id uuid, IN p_name text, IN p_company_guids text, IN p_company_names text, IN p_user_id uuid, IN p_user_first_name text, IN p_user_last_name text, IN p_email character varying, IN p_phone_number character varying, IN p_created_by uuid, IN p_default_company_id uuid)
LANGUAGE plpgsql
AS $procedure$
DECLARE
v_company_id uuid; -- Variable for iterating through company IDs
v_company_name text; -- Variable for iterating through company names
v_company_ids uuid[]; -- Array to hold parsed company IDs
v_company_names text[]; -- Array to hold parsed company names
i integer;
v_organization_exists boolean;
v_user_exists boolean;
BEGIN
-- Check if organization already exists by ID or Name
SELECT EXISTS (
SELECT 1 FROM public.organizations WHERE id = p_id OR (id = p_id AND name = p_name)
) INTO v_organization_exists;
IF v_organization_exists THEN
RAISE NOTICE 'Organization with ID % already exists. Skipping initialization.', p_id;
ELSE
INSERT INTO public.organizations (
id,
name,
created_on_utc,
created_by
) VALUES (
p_id,
p_name,
NOW(),
p_created_by
);
RAISE NOTICE 'Initialized organization: % with ID: %', p_name, p_id;
END IF;
-- Parse company IDs and names
v_company_ids := string_to_array(p_company_guids, ',');
v_company_names := string_to_array(p_company_names, ',');
-- Loop through each company and initialize
FOR i IN 1..array_length(v_company_ids, 1) LOOP
v_company_id := v_company_ids[i];
v_company_name := v_company_names[i];
-- Call initialize_company for each company
CALL public.initialize_company(
v_company_id,
p_id, -- Organization ID
true, -- Indicates it's an apartment or payroll company
v_company_name,
p_created_by,
p_default_company_id
);
END LOOP;
-- Assign the first company ID from the array to the user
v_company_id := v_company_ids[1];
-- Check if user already exists by ID
SELECT EXISTS (
SELECT 1 FROM public.users WHERE id = p_user_id OR email = p_email
) INTO v_user_exists;
IF v_user_exists THEN
UPDATE public.users
SET company_id = v_company_id
WHERE id = p_user_id OR email = p_email;
RAISE NOTICE 'User with ID % already exists. Skipping user creation.', p_user_id;
ELSE
INSERT INTO public.users (
id,
email,
phone_number,
first_name,
last_name,
password_hash,
created_on_utc,
created_by,
company_id
) VALUES (
p_user_id,
p_email,
p_phone_number,
p_user_first_name,
p_user_last_name,
'',
NOW(),
p_created_by,
v_company_id
);
RAISE NOTICE 'Initialized user with ID: % and email: % in organization: %', p_user_id, p_email, p_name;
END IF;
END;
$procedure$
-- Procedure: initialize_payment_header_ids
CREATE OR REPLACE PROCEDURE public.initialize_payment_header_ids(IN p_default_company_id uuid, IN new_company_id uuid)
LANGUAGE plpgsql
AS $procedure$
DECLARE
v_header_exists BOOLEAN;
BEGIN
-- Check if payment header IDs already exist for the new company
SELECT EXISTS (
SELECT 1
FROM payment_header_ids
WHERE company_id = new_company_id
) INTO v_header_exists;
IF NOT v_header_exists THEN
-- Insert new records for the new company
INSERT INTO payment_header_ids (
id,
company_id,
fin_year,
payment_prefix,
payment_length,
last_payment_id
)
SELECT
nextval('payment_header_ids_id_seq'), -- Generate new sequential ID
new_company_id, -- Assign the new company ID
fin_year,
payment_prefix,
payment_length,
last_payment_id
FROM payment_header_ids
WHERE company_id = p_default_company_id;
-- Optional: Log the operation
RAISE NOTICE 'Payment header IDs have been copied from company % to company %.', p_default_company_id, new_company_id;
ELSE
RAISE NOTICE 'Payment header IDs already exist for company %. Skipping initialization.', new_company_id;
END IF;
END;
$procedure$
-- Procedure: insert_payment_headers_from_payroll
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_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 character varying;
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,
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,
'NEFT', p_reference, v_payment_number, NOW(), p_created_by, false
);
-- Insert into payment_details and 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 the total paid amount in payment_headers
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$
-- Procedure: purge_payroll_organization_data
CREATE OR REPLACE PROCEDURE public.purge_payroll_organization_data(IN p_organization_ids uuid[] DEFAULT NULL::uuid[])
LANGUAGE plpgsql
AS $procedure$
DECLARE
-- Retention policy (time-gate high-volume transactional rows)
c_retention_hours integer := 24;
v_cutoff_24h timestamp := now() - make_interval(hours => GREATEST(c_retention_hours, 1));
-- Targets
v_orgs uuid[] := '{}';
v_companies uuid[] := '{}';
-- Employees and related
v_employee_ids uuid[] := '{}';
-- Projects, sub-projects, tasks
v_project_ids uuid[] := '{}';
v_sub_project_ids int[] := '{}';
v_task_ids int[] := '{}';
-- Timesheets
v_timesheet_ids int[] := '{}';
v_timesheet_line_ids int[] := '{}';
-- Payroll transactions
v_payroll_header_ids uuid[] := '{}';
-- Payments
v_payment_header_ids uuid[] := '{}';
-- Payroll configurations
v_payroll_conf_header_ids uuid[] := '{}';
BEGIN
START TRANSACTION;
--------------------------------------------------------------------
-- 1) Resolve target organizations and companies
--------------------------------------------------------------------
IF p_organization_ids IS NULL OR array_length(p_organization_ids, 1) IS NULL THEN
SELECT COALESCE(array_agg(id), '{}')
INTO v_orgs
FROM organizations
WHERE created_on_utc > '2025-05-23'
AND created_on_utc < (NOW() - interval '24 hours');
ELSE
v_orgs := p_organization_ids;
END IF;
IF v_orgs IS NULL OR array_length(v_orgs, 1) IS NULL THEN
RAISE NOTICE 'No organizations found for payroll cleanup.';
COMMIT;
RETURN;
END IF;
SELECT COALESCE(array_agg(id), '{}')
INTO v_companies
FROM companies
WHERE organization_id = ANY(v_orgs);
IF v_companies IS NULL OR array_length(v_companies, 1) IS NULL THEN
RAISE NOTICE 'No companies resolved for payroll purge. Orgs: %', v_orgs;
COMMIT;
RETURN;
END IF;
RAISE NOTICE 'Payroll purge targets - Organizations: %; Companies: %', v_orgs, v_companies;
--------------------------------------------------------------------
-- 2) Collect IDs (COALESCE to '{}' to avoid NULL-array issues)
--------------------------------------------------------------------
-- Employees
SELECT COALESCE(array_agg(id), '{}')
INTO v_employee_ids
FROM employees
WHERE company_id = ANY(v_companies);
-- Projects and children
SELECT COALESCE(array_agg(id), '{}')
INTO v_project_ids
FROM projects
WHERE company_id = ANY(v_companies);
SELECT COALESCE(array_agg(id), '{}')
INTO v_sub_project_ids
FROM sub_projects
WHERE project_id = ANY(v_project_ids);
SELECT COALESCE(array_agg(id), '{}')
INTO v_task_ids
FROM tasks
WHERE company_id = ANY(v_companies);
-- Timesheets (time-gated to last 24h by created_on_utc; adjust if needed)
SELECT COALESCE(array_agg(id), '{}')
INTO v_timesheet_ids
FROM timesheets
WHERE company_id = ANY(v_companies)
AND (created_on_utc IS NULL OR created_on_utc < v_cutoff_24h);
SELECT COALESCE(array_agg(id), '{}')
INTO v_timesheet_line_ids
FROM timesheet_lines
WHERE timesheet_id = ANY(v_timesheet_ids);
-- Payroll transactions (time-gated)
SELECT COALESCE(array_agg(id), '{}')
INTO v_payroll_header_ids
FROM payroll_transaction_headers
WHERE company_id = ANY(v_companies)
AND created_on_utc < v_cutoff_24h;
-- Payment headers (time-gated)
SELECT COALESCE(array_agg(id), '{}')
INTO v_payment_header_ids
FROM payment_headers
WHERE company_id = ANY(v_companies)
AND created_on_utc < v_cutoff_24h;
-- Payroll configuration headers
SELECT COALESCE(array_agg(id), '{}')
INTO v_payroll_conf_header_ids
FROM payroll_configuration_headers
WHERE company_id = ANY(v_companies);
--------------------------------------------------------------------
-- 3) Purge in strict child → parent order (avoid FK violations)
--------------------------------------------------------------------
-- Timesheets: hours → lines → sheets
DELETE FROM timesheet_hours
WHERE timesheet_line_id = ANY(v_timesheet_line_ids);
DELETE FROM timesheet_lines
WHERE id = ANY(v_timesheet_line_ids);
DELETE FROM timesheets
WHERE id = ANY(v_timesheet_ids);
-- Payroll transactions: details → headers
DELETE FROM payroll_transaction_details
WHERE payroll_transaction_id = ANY(v_payroll_header_ids);
DELETE FROM payroll_transaction_headers
WHERE id = ANY(v_payroll_header_ids);
-- Payments: details → headers
DELETE FROM payment_details
WHERE payment_header_id = ANY(v_payment_header_ids);
DELETE FROM payment_headers
WHERE id = ANY(v_payment_header_ids);
-- Payroll configuration: details → headers
DELETE FROM payroll_configuration_details
WHERE configuration_header_id = ANY(v_payroll_conf_header_ids);
DELETE FROM payroll_configuration_headers
WHERE id = ANY(v_payroll_conf_header_ids);
-- Payroll components (company scoped)
DELETE FROM payroll_components
WHERE company_id = ANY(v_companies);
-- Leave/application/records/balances/entitlements before employees
DELETE FROM leave_applications
WHERE company_id = ANY(v_companies);
DELETE FROM leave_records
WHERE company_id = ANY(v_companies);
DELETE FROM employee_leave_balances
WHERE company_id = ANY(v_companies);
DELETE FROM leave_entitlements
WHERE company_id = ANY(v_companies);
-- Attendance and HR monetary movements tied to employees
DELETE FROM attendance
WHERE employee_id = ANY(v_employee_ids);
DELETE FROM advance_payments
WHERE employee_id = ANY(v_employee_ids);
DELETE FROM benefits
WHERE employee_id = ANY(v_employee_ids);
DELETE FROM deductions
WHERE employee_id = ANY(v_employee_ids);
DELETE FROM loans
WHERE employee_id = ANY(v_employee_ids);
-- Employee bridges: bank accounts and UPIs (junction tables only)
DELETE FROM employee_bank_accounts
WHERE employee_id = ANY(v_employee_ids);
DELETE FROM employee_upis
WHERE employee_id = ANY(v_employee_ids);
-- Company-scoped numbering/configs
DELETE FROM payroll_transaction_header_ids
WHERE company_id = ANY(v_companies);
DELETE FROM payment_header_ids
WHERE company_id = ANY(v_companies);
-- Company-scoped calendars and workflow
DELETE FROM holidays
WHERE company_id = ANY(v_companies);
DELETE FROM workdays
WHERE company_id = ANY(v_companies);
DELETE FROM leave_workflow
WHERE company_id = ANY(v_companies);
-- Timesheet-linked structures already cleared; now tasks/sub-projects/projects
DELETE FROM tasks
WHERE id = ANY(v_task_ids);
DELETE FROM sub_projects
WHERE id = ANY(v_sub_project_ids);
DELETE FROM projects
WHERE id = ANY(v_project_ids);
-- Finally, employees
DELETE FROM employees
WHERE id = ANY(v_employee_ids);
-- Intentionally NOT deleting users, addresses, or lookup masters
-- (component_types, statuses, banks, countries, states, cities, leave_types, leave_statuses)
-- to avoid cross-company/global FK breakages.
RAISE NOTICE 'Payroll purge complete for companies: % (orgs: %).', v_companies, v_orgs;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
RAISE NOTICE 'purge_payroll_organization_data failed: %', SQLERRM;
-- Optionally rethrow
-- RAISE;
END;
$procedure$
-- Procedure: clean_up_org_payroll
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$
-- Procedure: update_payment_header_posted
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$
-- Procedure: insert_payment_headers_from_payroll
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$