| Type | Name | Status | PK | FK | Columns | Index | Script | Diff Script |
|---|---|---|---|---|---|---|---|---|
| Table | customer_products | Missing in Target |
|
|||||
| Table | product_group | Missing in Target |
|
|||||
| Table | orders | Missing in Target |
|
|||||
| Table | roles | Missing in Target |
|
|||||
| Table | product_tax_category_details | Missing in Target |
|
|||||
| Table | order_details | Missing in Target |
|
|||||
| Table | schema_versions | Missing in Target |
|
|||||
| Table | companies | Missing in Target |
|
|||||
| Table | company_categories | Missing in Target |
|
|||||
| Table | units | Missing in Target |
|
|||||
| Table | organizations | Missing in Target |
|
|||||
| Table | product_tax_categories | Missing in Target |
|
|||||
| Table | product_warehouse_quantities | Missing in Target |
|
|||||
| Table | __EFMigrationsHistory | Missing in Target |
|
|||||
| Table | vendor_products | Missing in Target |
|
|||||
| Table | users | Missing in Target |
|
|||||
| Table | categories | Missing in Target |
|
|||||
| Table | default_apartment_products | Missing in Target |
|
|||||
| Table | company_products | Missing in Target |
|
|||||
| Table | gate_pass_logs | Missing in Target |
|
|||||
| Table | stock_adjustment_details | Missing in Target |
|
|||||
| Table | stock_ledgers | Missing in Target |
|
|||||
| Table | stock_adjustment_statuses | Missing in Target |
|
|||||
| Table | stock_movement_types | Missing in Target |
|
|||||
| Table | stock_adjustment_headers | Missing in Target |
|
|||||
| Table | stock_transfer_statuses | Missing in Target |
|
|||||
| Table | goods_issue_details | Missing in Target |
|
|||||
| Table | gate_pass_headers | Missing in Target |
|
|||||
| Table | stock_reservation_statuses | Missing in Target |
|
|||||
| Table | gate_pass_details | Missing in Target |
|
|||||
| Table | warehouses | Missing in Target |
|
|||||
| Table | goods_issue_headers | Missing in Target |
|
|||||
| Table | stock_transfer_headers | Missing in Target |
|
|||||
| Table | warehouse_locations | Missing in Target |
|
|||||
| Table | goods_receipt_headers | Missing in Target |
|
|||||
| Table | unit_conversions | Missing in Target |
|
|||||
| Table | product_reorder_settings | Missing in Target |
|
|||||
| Table | material_request_details | Missing in Target |
|
|||||
| Table | stock_transfer_details | Missing in Target |
|
|||||
| Table | stock_count_headers | Missing in Target |
|
|||||
| Table | products | Missing in Target |
|
|||||
| Table | stock_count_details | Missing in Target |
|
|||||
| Table | stock_adjustment_reasons | Missing in Target |
|
|||||
| Table | material_request_headers | Missing in Target |
|
|||||
| Table | inventory_balances | Missing in Target |
|
|||||
| Table | stock_reservations | Missing in Target |
|
|||||
| Table | material_request_approval_user_company | Missing in Target |
|
|||||
| Table | material_request_approval_issue_logs | Missing in Target |
|
|||||
| Table | unit_types | Missing in Target |
|
|||||
| Table | goods_receipt_details | Missing in Target |
|
|||||
| Table | stock_reservation_status_transitions | Missing in Target |
|
|||||
| Table | material_request_headers_ids | Missing in Target |
|
|||||
| Table | product_unit_conversions | Missing in Target |
|
|||||
| Table | material_request_approval_logs | Missing in Target |
|
|||||
| Table | material_request_workflows | Missing in Target |
|
|||||
| Table | stock_reservation_status_history | Missing in Target |
|
|||||
| Table | goods_receipt_numbers | Missing in Target |
|
|||||
| Table | material_request_statuses | Missing in Target |
|
|||||
| 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 | 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_products | Missing in Target |
Source Script
Target Script
1
CREATE OR REPLACE FUNCTION public.get_all_products(p_company_id uuid, p_product_group_id integer)
2
RETURNS TABLE(product_id uuid, product_group_id integer, product_group_name text, product_name text, unit_id uuid, unit_name text, category_id uuid, category_name text, description text, hsn_code text, sac text, purchase_price numeric, selling_price numeric, opening_stock numeric, minimum_stock numeric, vendor_id uuid, vendor_name text, tax_category_id integer, tax_category_name text, maximum_stock numeric, cgst_rate numeric, sgst_rate numeric, igst_rate numeric)
3
LANGUAGE plpgsql
4
AS $function$
5
BEGIN
6
RETURN QUERY
7
SELECT
8
p.id AS product_id,
9
p.product_group_id,
10
pg.name::TEXT AS product_group_name,
11
p.product_name::TEXT,
12
p.unit_id,
13
COALESCE(u.unit_name, '')::TEXT AS unit_name,
14
p.category_id,
15
COALESCE(c.category_name, '')::TEXT AS category_name,
16
p.description::TEXT,
17
p.hsn_code::TEXT,
18
p.sac::TEXT,
19
p.purchase_price,
20
p.selling_price,
21
p.opening_stock,
22
p.minimum_stock,
23
p.vendor_id,
24
NULL::TEXT AS vendor_name, -- Replace with actual vendor join if needed
25
p.tax_category_id,
26
COALESCE(ptc.name, '')::TEXT AS tax_category_name,
27
p.maximum_stock,
28
ptcd.c_gst_rate,
29
ptcd.s_gst_rate,
30
ptcd.i_gst_rate
31
FROM products p
32
INNER JOIN company_products cp ON p.id = cp.product_id
33
LEFT JOIN categories c ON p.category_id = c.id
34
LEFT JOIN product_tax_categories ptc ON p.tax_category_id = ptc.id
35
LEFT JOIN product_tax_category_details ptcd ON ptc.id = ptcd.producttaxcategory_id
36
INNER JOIN product_group pg ON p.product_group_id = pg.id
37
LEFT JOIN units u ON p.unit_id = u.id
38
WHERE cp.company_id = p_company_id
39
AND p.product_group_id = p_product_group_id
40
AND (cp.is_deleted IS FALSE OR cp.is_deleted IS NULL)
41
AND (p.is_deleted IS FALSE OR p.is_deleted IS NULL);
42
END;
43
$function$
|
|||||
| Function | get_goods_receipts | Missing in Target |
Source Script
Target Script
1
CREATE OR REPLACE FUNCTION public.get_goods_receipts(p_company_id uuid)
2
RETURNS TABLE(id uuid, organization_id uuid, company_id uuid, receipt_no character varying, receipt_type character varying, warehouse_id uuid, warehouse_name character varying, vendor_id uuid, vendor_name text, source_document_type character varying, source_document_id uuid, vendor_invoice_no character varying, receipt_date timestamp without time zone, status character varying, remarks text, posted_on_utc timestamp without time zone, total_items integer, total_received_qty numeric, total_accepted_qty numeric, total_rejected_qty numeric, total_amount numeric)
3
LANGUAGE plpgsql
4
AS $function$
5
BEGIN
6
RETURN QUERY
7
SELECT
8
grh.id,
9
grh.organization_id,
10
grh.company_id,
11
grh.receipt_no,
12
grh.receipt_type,
13
grh.warehouse_id,
14
w.name AS warehouse_name,
15
grh.vendor_id,
16
NULL::text AS vendor_name,
17
grh.source_document_type,
18
grh.source_document_id,
19
grh.vendor_invoice_no,
20
grh.receipt_date,
21
grh.status,
22
grh.remarks,
23
grh.posted_on_utc,
24
COUNT(grd.id)::int AS total_items,
25
COALESCE(SUM(grd.received_qty), 0) AS total_received_qty,
26
COALESCE(SUM(grd.accepted_qty), 0) AS total_accepted_qty,
27
COALESCE(SUM(grd.rejected_qty), 0) AS total_rejected_qty,
28
COALESCE(SUM(grd.line_amount), 0) AS total_amount
29
FROM public.goods_receipt_headers grh
30
INNER JOIN public.warehouses w
31
ON w.id = grh.warehouse_id
32
--LEFT JOIN public.vendors v
33
-- ON v.id = grh.vendor_id
34
LEFT JOIN public.goods_receipt_details grd
35
ON grd.goods_receipt_header_id = grh.id
36
WHERE grh.company_id = p_company_id
37
AND grh.is_deleted = false
38
GROUP BY
39
grh.id,
40
grh.organization_id,
41
grh.company_id,
42
grh.receipt_no,
43
grh.receipt_type,
44
grh.warehouse_id,
45
w.name,
46
grh.vendor_id,
47
--v.vendor_name,
48
grh.source_document_type,
49
grh.source_document_id,
50
grh.vendor_invoice_no,
51
grh.receipt_date,
52
grh.status,
53
grh.remarks,
54
grh.posted_on_utc
55
ORDER BY grh.receipt_date DESC, grh.receipt_no DESC;
56
END;
57
$function$
|
|||||
| Function | get_goods_receipt_by_id | Missing in Target |
Source Script
Target Script
1
CREATE OR REPLACE FUNCTION public.get_goods_receipt_by_id(p_id uuid)
2
RETURNS jsonb
3
LANGUAGE plpgsql
4
AS $function$
5
DECLARE
6
v_result jsonb;
7
BEGIN
8
SELECT jsonb_build_object(
9
'id', grh.id,
10
'organizationId', grh.organization_id,
11
'companyId', grh.company_id,
12
'receiptNo', grh.receipt_no,
13
'receiptType', grh.receipt_type,
14
'warehouseId', grh.warehouse_id,
15
'warehouseName', w.name,
16
'vendorId', grh.vendor_id,
17
'vendorName', NULL,
18
'sourceDocumentType', grh.source_document_type,
19
'sourceDocumentId', grh.source_document_id,
20
'vendorInvoiceNo', grh.vendor_invoice_no,
21
'receiptDate', grh.receipt_date,
22
'status', grh.status,
23
'remarks', grh.remarks,
24
'postedOnUtc', grh.posted_on_utc,
25
'postedBy', grh.posted_by,
26
'details', COALESCE(
27
(
28
SELECT jsonb_agg(
29
jsonb_build_object(
30
'id', grd.id,
31
'goodsReceiptHeaderId', grd.goods_receipt_header_id,
32
'lineNumber', grd.line_number,
33
'productId', grd.product_id,
34
'productName', p.product_name,
35
'unitId', grd.unit_id,
36
'unitName', u.unit_name,
37
'orderedQty', grd.ordered_qty,
38
'receivedQty', grd.received_qty,
39
'acceptedQty', grd.accepted_qty,
40
'rejectedQty', grd.rejected_qty,
41
'unitRate', grd.unit_rate,
42
'lineAmount', grd.line_amount,
43
'warehouseLocationId', grd.warehouse_location_id,
44
'warehouseLocationName', wl.name,
45
'remarks', grd.remarks
46
)
47
ORDER BY grd.line_number
48
)
49
FROM public.goods_receipt_details grd
50
INNER JOIN public.products p
51
ON p.id = grd.product_id
52
INNER JOIN public.units u
53
ON u.id = grd.unit_id
54
LEFT JOIN public.warehouse_locations wl
55
ON wl.id = grd.warehouse_location_id
56
WHERE grd.goods_receipt_header_id = grh.id
57
),
58
'[]'::jsonb
59
)
60
)
61
INTO v_result
62
FROM public.goods_receipt_headers grh
63
INNER JOIN public.warehouses w
64
ON w.id = grh.warehouse_id
65
WHERE grh.id = p_id
66
AND grh.is_deleted = false;
67
68
RETURN v_result;
69
END;
70
$function$
|
|||||
| Function | update_material_request_next_status_main | Missing in Target |
Source Script
Target Script
1
CREATE OR REPLACE FUNCTION public.update_material_request_next_status_main(p_company_id uuid, p_material_request_ids uuid[], p_modified_by uuid)
2
RETURNS TABLE(material_request_id uuid, status_name text, error_msg text)
3
LANGUAGE plpgsql
4
AS $function$
5
DECLARE
6
v_request_id uuid;
7
v_current_status_name varchar(100);
8
v_current_status_id int4;
9
v_next_status_id int4;
10
v_next_status_name varchar(100);
11
v_approval_level int4;
12
v_user_allowed boolean;
13
v_has_partial_issue boolean;
14
BEGIN
15
FOREACH v_request_id IN ARRAY p_material_request_ids
16
LOOP
17
v_current_status_name := NULL;
18
v_current_status_id := NULL;
19
v_next_status_id := NULL;
20
v_next_status_name := NULL;
21
v_approval_level := 0;
22
v_user_allowed := false;
23
v_has_partial_issue := false;
24
25
-- 1. Validate request exists
26
SELECT mrh.status
27
INTO v_current_status_name
28
FROM public.material_request_headers mrh
29
WHERE mrh.id = v_request_id
30
AND mrh.company_id = p_company_id
31
AND COALESCE(mrh.is_deleted, false) = false;
32
33
IF v_current_status_name IS NULL THEN
34
material_request_id := v_request_id;
35
status_name := NULL;
36
error_msg := 'Material request not found';
37
RETURN NEXT;
38
CONTINUE;
39
END IF;
40
41
-- 2. Map current status name -> status id
42
SELECT mrs.id
43
INTO v_current_status_id
44
FROM public.material_request_statuses mrs
45
WHERE lower(mrs.name) = lower(v_current_status_name)
46
AND COALESCE(mrs.is_deleted, false) = false
47
LIMIT 1;
48
49
IF v_current_status_id IS NULL THEN
50
material_request_id := v_request_id;
51
status_name := v_current_status_name;
52
error_msg := 'Current status is invalid';
53
RETURN NEXT;
54
CONTINUE;
55
END IF;
56
57
-- 3. Terminal statuses cannot move next
58
IF lower(v_current_status_name) IN ('rejected', 'closed', 'cancelled') THEN
59
material_request_id := v_request_id;
60
status_name := v_current_status_name;
61
error_msg := 'Next status update is not allowed for current status';
62
RETURN NEXT;
63
CONTINUE;
64
END IF;
65
66
/*
67
4. Special business rule:
68
Approved -> Closed OR Partially Issued
69
Based on detail quantities
70
*/
71
IF lower(v_current_status_name) = 'approved' THEN
72
SELECT EXISTS (
73
SELECT 1
74
FROM public.material_request_details mrd
75
WHERE mrd.material_request_header_id = v_request_id
76
AND COALESCE(mrd.is_deleted, false) = false
77
AND COALESCE(mrd.issued_qty, 0) < COALESCE(mrd.approved_qty, mrd.requested_qty)
78
)
79
INTO v_has_partial_issue;
80
81
IF v_has_partial_issue THEN
82
SELECT mrs.id, mrs.name
83
INTO v_next_status_id, v_next_status_name
84
FROM public.material_request_statuses mrs
85
WHERE lower(mrs.name) = lower('Partially Issued')
86
AND COALESCE(mrs.is_deleted, false) = false
87
LIMIT 1;
88
ELSE
89
SELECT mrs.id, mrs.name
90
INTO v_next_status_id, v_next_status_name
91
FROM public.material_request_statuses mrs
92
WHERE lower(mrs.name) = lower('Closed')
93
AND COALESCE(mrs.is_deleted, false) = false
94
LIMIT 1;
95
END IF;
96
97
IF v_next_status_id IS NULL THEN
98
material_request_id := v_request_id;
99
status_name := v_current_status_name;
100
error_msg := 'Next status master not found';
101
RETURN NEXT;
102
CONTINUE;
103
END IF;
104
105
UPDATE public.material_request_headers
106
SET status = v_next_status_name,
107
modified_by = p_modified_by,
108
modified_on_utc = now()
109
WHERE id = v_request_id;
110
111
INSERT INTO public.material_request_approval_logs(
112
material_request_id,
113
status_id,
114
approved_by,
115
approved_on,
116
comment,
117
created_on_utc,
118
created_by,
119
approval_level
120
)
121
VALUES (
122
v_request_id,
123
v_next_status_id,
124
p_modified_by,
125
now(),
126
'Moved to next status from ' || v_current_status_name || ' to ' || v_next_status_name,
127
now(),
128
p_modified_by,
129
0
130
);
131
132
INSERT INTO public.material_request_approval_issue_logs(
133
material_request_id,
134
issue,
135
created_on_utc,
136
created_by
137
)
138
VALUES (
139
v_request_id,
140
'Moved to status ' || v_next_status_name,
141
now(),
142
p_modified_by
143
);
144
145
material_request_id := v_request_id;
146
status_name := v_next_status_name;
147
error_msg := NULL;
148
RETURN NEXT;
149
CONTINUE;
150
END IF;
151
152
-- 5. Workflow-driven next status for all other non-terminal statuses
153
SELECT mrw.next_status, COALESCE(mrw.approval_level, 0)
154
INTO v_next_status_id, v_approval_level
155
FROM public.material_request_workflows mrw
156
WHERE mrw.company_id = p_company_id
157
AND mrw.status = v_current_status_id
158
AND COALESCE(mrw.is_deleted, false) = false
159
LIMIT 1;
160
161
IF v_next_status_id IS NULL THEN
162
material_request_id := v_request_id;
163
status_name := v_current_status_name;
164
error_msg := 'Next status is not configured';
165
RETURN NEXT;
166
CONTINUE;
167
END IF;
168
169
-- 6. Validate user authorization when approval level applies
170
IF COALESCE(v_approval_level, 0) > 0 THEN
171
SELECT EXISTS (
172
SELECT 1
173
FROM public.material_request_approval_user_company mrauc
174
WHERE mrauc.company_id = p_company_id
175
AND mrauc.status_id = v_current_status_id
176
AND mrauc.approval_level = v_approval_level
177
AND mrauc.user_id = p_modified_by
178
AND COALESCE(mrauc.is_deleted, false) = false
179
)
180
INTO v_user_allowed;
181
182
IF NOT COALESCE(v_user_allowed, false) THEN
183
material_request_id := v_request_id;
184
status_name := v_current_status_name;
185
error_msg := 'User is not authorized to move this status';
186
RETURN NEXT;
187
CONTINUE;
188
END IF;
189
END IF;
190
191
-- 7. Resolve next status name
192
SELECT mrs.name
193
INTO v_next_status_name
194
FROM public.material_request_statuses mrs
195
WHERE mrs.id = v_next_status_id
196
AND COALESCE(mrs.is_deleted, false) = false
197
LIMIT 1;
198
199
IF v_next_status_name IS NULL THEN
200
material_request_id := v_request_id;
201
status_name := v_current_status_name;
202
error_msg := 'Next status master not found';
203
RETURN NEXT;
204
CONTINUE;
205
END IF;
206
207
-- 8. Update header
208
UPDATE public.material_request_headers
209
SET status = v_next_status_name,
210
modified_by = p_modified_by,
211
modified_on_utc = now()
212
WHERE id = v_request_id;
213
214
-- 9. Insert approval log
215
INSERT INTO public.material_request_approval_logs(
216
material_request_id,
217
status_id,
218
approved_by,
219
approved_on,
220
comment,
221
created_on_utc,
222
created_by,
223
approval_level
224
)
225
VALUES (
226
v_request_id,
227
v_next_status_id,
228
p_modified_by,
229
now(),
230
'Moved to next status from ' || v_current_status_name || ' to ' || v_next_status_name,
231
now(),
232
p_modified_by,
233
v_approval_level
234
);
235
236
-- 10. Issue log only for issue statuses
237
IF lower(v_next_status_name) IN ('partially issued', 'issued', 'closed') THEN
238
INSERT INTO public.material_request_approval_issue_logs(
239
material_request_id,
240
issue,
241
created_on_utc,
242
created_by
243
)
244
VALUES (
245
v_request_id,
246
'Moved to status ' || v_next_status_name,
247
now(),
248
p_modified_by
249
);
250
END IF;
251
252
material_request_id := v_request_id;
253
status_name := v_next_status_name;
254
error_msg := NULL;
255
RETURN NEXT;
256
END LOOP;
257
258
RETURN;
259
END;
260
$function$
|
|||||
| Function | get_products_by_company_and_group | Missing in Target |
Source Script
Target Script
1
CREATE OR REPLACE FUNCTION public.get_products_by_company_and_group(in_company_id uuid, in_product_group_id integer)
2
RETURNS TABLE(id uuid, product_group_id integer, product_name text, unit_id uuid, unit_name text, category_id uuid, category_name text, description text, hsn_code text, sac text, purchase_price numeric, selling_price numeric, opening_stock numeric, minimum_stock numeric, vendor_id uuid, tax_category_id uuid, maximum_stock numeric, cgst_rate numeric, sgst_rate numeric, igst_rate numeric)
3
LANGUAGE plpgsql
4
AS $function$
5
BEGIN
6
RETURN QUERY
7
SELECT
8
p.id,
9
p.product_group_id,
10
p.product_name::TEXT,
11
p.unit_id,
12
u.unit_name::TEXT,
13
p.category_id,
14
c.category_name::TEXT,
15
p.description::TEXT,
16
p.hsn_code::TEXT,
17
p.sac::TEXT,
18
p.purchase_price,
19
p.selling_price,
20
p.opening_stock,
21
p.minimum_stock,
22
p.vendor_id,
23
p.tax_category_id,
24
p.maximum_stock,
25
ptcd.c_gst_rate,
26
ptcd.s_gst_rate,
27
ptcd.i_gst_rate
28
FROM products p
29
INNER JOIN company_products cp ON p.id = cp.product_id
30
LEFT JOIN categories c ON p.category_id = c.id
31
LEFT JOIN product_tax_categories ptc ON p.tax_category_id = ptc.id
32
LEFT JOIN product_tax_category_details ptcd ON ptc.id = ptcd.producttaxcategory_id
33
INNER JOIN product_group pg ON p.product_group_id = pg.id
34
LEFT JOIN units u ON u.id = p.unit_id
35
WHERE cp.company_id = in_company_id
36
AND p.product_group_id = in_product_group_id;
37
END;
38
$function$
|
|||||
| Function | get_all_stock_adjustments_from_span | Missing in Target |
Source Script
Target Script
1
CREATE OR REPLACE FUNCTION public.get_all_stock_adjustments_from_span(p_company_id uuid, p_user_id uuid, p_warehouse_id uuid DEFAULT NULL::uuid, p_start_date timestamp without time zone DEFAULT NULL::timestamp without time zone, p_end_date timestamp without time zone DEFAULT NULL::timestamp without time zone)
2
RETURNS TABLE(id uuid, organization_id uuid, company_id uuid, warehouse_id uuid, stock_adjustment_status text, stock_adjustment_status_id integer, reason_code text, stock_adjustment_number text, remarks text, adjustment_date date, created_by uuid, created_on_utc timestamp without time zone, modified_by uuid, modified_on_utc timestamp without time zone, created_by_name text, modified_by_name text, line_count integer, total_difference numeric)
3
LANGUAGE plpgsql
4
AS $function$
5
DECLARE
6
v_start_date date := COALESCE(p_start_date::date, '1970-01-01'::date);
7
v_end_date date := COALESCE(p_end_date::date, '9999-12-31'::date);
8
BEGIN
9
RETURN QUERY
10
WITH headers AS (
11
SELECT h.*
12
FROM public.stock_adjustment_headers h
13
WHERE h.company_id = p_company_id
14
AND h.is_deleted = false
15
AND (p_warehouse_id IS NULL OR p_warehouse_id = '00000000-0000-0000-0000-000000000000'::uuid OR h.warehouse_id = p_warehouse_id)
16
AND (h.adjustment_date_utc::date BETWEEN v_start_date AND v_end_date)
17
), line_agg AS (
18
SELECT
19
sd.stock_adjustment_header_id,
20
COUNT(*)::int AS line_count,
21
SUM(sd.difference_quantity)::numeric AS total_difference
22
FROM public.stock_adjustment_details sd
23
WHERE sd.is_deleted = false
24
GROUP BY sd.stock_adjustment_header_id
25
)
26
SELECT
27
h.id,
28
h.organization_id,
29
h.company_id,
30
h.warehouse_id,
31
COALESCE(s.name, '') AS stock_adjustment_status,
32
h.stock_adjustment_status_id,
33
h.reason_code,
34
h.stock_adjustment_number,
35
h.remarks,
36
h.adjustment_date_utc::date AS adjustment_date,
37
h.created_by,
38
h.created_on_utc,
39
h.modified_by,
40
h.modified_on_utc,
41
CONCAT(cu.first_name, ' ', cu.last_name)::text AS created_by_name,
42
CONCAT(mu.first_name, ' ', mu.last_name)::text AS modified_by_name,
43
COALESCE(la.line_count, 0) AS line_count,
44
COALESCE(la.total_difference, 0.0) AS total_difference
45
FROM headers h
46
LEFT JOIN public.stock_adjustment_statuses s ON s.id = h.stock_adjustment_status_id
47
LEFT JOIN public.users cu ON cu.id = h.created_by
48
LEFT JOIN public.users mu ON mu.id = h.modified_by
49
LEFT JOIN line_agg la ON la.stock_adjustment_header_id = h.id;
50
END;
51
$function$
|
|||||
| Function | get_all_products_by_company_and_group | Missing in Target |
Source Script
Target Script
1
CREATE OR REPLACE FUNCTION public.get_all_products_by_company_and_group(p_company_id uuid, p_product_group_id integer)
2
RETURNS TABLE(id uuid, product_group_id integer, product_group_name text, product_name character varying, unit_id uuid, unit text, category_id uuid, category_name text, description text, hsn_code text, sac text, purchase_price numeric, selling_price numeric, opening_stock numeric, minimum_stock numeric, vendor_id uuid, vendor_name text, tax_category_id integer, tax_category_name text, maximum_stock numeric, cgst numeric, sgst numeric, igst numeric)
3
LANGUAGE sql
4
STABLE
5
AS $function$
6
SELECT
7
p.id,
8
p.product_group_id,
9
''::text AS product_group_name,
10
p.product_name,
11
p.unit_id,
12
COALESCE(un.unit_name, '')::text AS unit,
13
p.category_id,
14
COALESCE(cat.category_name, '')::text AS category_name,
15
p.description,
16
p.hsn_code,
17
p.sac,
18
p.purchase_price,
19
p.selling_price,
20
p.opening_stock,
21
p.minimum_stock,
22
p.vendor_id,
23
NULL::text AS vendor_name,
24
p.tax_category_id,
25
''::text AS tax_category_name,
26
p.maximum_stock,
27
ptcd.c_gst_rate AS cgst,
28
ptcd.s_gst_rate AS sgst,
29
ptcd.i_gst_rate AS igst
30
FROM public.products p
31
INNER JOIN public.company_products cp
32
ON p.id = cp.product_id
33
LEFT JOIN public.categories cat
34
ON p.category_id = cat.id
35
LEFT JOIN public.product_tax_categories ptc
36
ON p.tax_category_id = ptc.id
37
LEFT JOIN public.product_tax_category_details ptcd
38
ON ptc.id = ptcd.producttaxcategory_id
39
INNER JOIN public.product_group pg
40
ON p.product_group_id = pg.id
41
LEFT JOIN public.units un
42
ON un.id = p.unit_id
43
WHERE cp.company_id = p_company_id
44
AND p.product_group_id = p_product_group_id;
45
$function$
|
|||||
| Function | get_all_material_requests | Missing in Target |
Source Script
Target Script
1
CREATE OR REPLACE FUNCTION public.get_all_material_requests(p_company_id uuid)
2
RETURNS TABLE(id uuid, organization_id uuid, company_id uuid, request_no character varying, request_type character varying, requested_by uuid, requesting_department character varying, warehouse_id uuid, warehouse_name character varying, required_date date, status character varying, remarks 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 uuid, modified_by uuid, created_by_name character varying, modified_by_name character varying)
3
LANGUAGE sql
4
STABLE
5
AS $function$
6
SELECT
7
mrh.id,
8
mrh.organization_id,
9
mrh.company_id,
10
mrh.request_no,
11
mrh.request_type,
12
mrh.requested_by,
13
mrh.requesting_department,
14
mrh.warehouse_id,
15
w.name AS warehouse_name,
16
mrh.required_date,
17
mrh.status,
18
mrh.remarks,
19
mrh.created_on_utc,
20
mrh.modified_on_utc,
21
mrh.deleted_on_utc,
22
mrh.is_deleted,
23
mrh.created_by,
24
mrh.modified_by,
25
CAST(TRIM(CONCAT(cu.first_name, ' ', cu.last_name)) AS varchar) AS created_by_name,
26
CAST(TRIM(CONCAT(mu.first_name, ' ', mu.last_name)) AS varchar) AS modified_by_name
27
FROM public.material_request_headers mrh
28
LEFT JOIN public.warehouses w
29
ON mrh.warehouse_id = w.id
30
AND w.is_deleted = false
31
LEFT JOIN public.users cu
32
ON cu.id = mrh.created_by
33
LEFT JOIN public.users mu
34
ON mu.id = mrh.modified_by
35
WHERE mrh.company_id = p_company_id
36
AND mrh.is_deleted = false
37
ORDER BY mrh.created_on_utc DESC;
38
$function$
|
|||||
| Function | get_new_material_request_number | Missing in Target |
Source Script
Target Script
1
CREATE OR REPLACE FUNCTION public.get_new_material_request_number(p_company_id uuid, p_date date)
2
RETURNS character varying
3
LANGUAGE plpgsql
4
AS $function$
5
DECLARE
6
v_finance_year integer;
7
v_new_id integer;
8
v_prefix varchar;
9
v_length integer;
10
v_material_request_no varchar;
11
BEGIN
12
-- 1. Determine Financial Year (April–March)
13
IF EXTRACT(MONTH FROM p_date) >= 4 THEN
14
v_finance_year := EXTRACT(YEAR FROM p_date);
15
ELSE
16
v_finance_year := EXTRACT(YEAR FROM p_date) - 1;
17
END IF;
18
19
-- 2. Try UPDATE (Atomic Increment)
20
WITH updated AS (
21
UPDATE public.material_request_headers_ids
22
SET last_material_request_id = last_material_request_id + 1
23
WHERE company_id = p_company_id
24
AND fin_year = v_finance_year
25
RETURNING
26
last_material_request_id,
27
material_request_prefix,
28
material_request_length
29
),
30
inserted AS (
31
-- 3. If not exists → INSERT default
32
INSERT INTO public.material_request_headers_ids (
33
company_id,
34
fin_year,
35
material_request_prefix,
36
material_request_length,
37
last_material_request_id
38
)
39
SELECT
40
p_company_id,
41
v_finance_year,
42
'MR', -- Default Prefix
43
6, -- Default Length (can change later)
44
1
45
WHERE NOT EXISTS (SELECT 1 FROM updated)
46
RETURNING
47
last_material_request_id,
48
material_request_prefix,
49
material_request_length
50
)
51
52
-- 4. Get values
53
SELECT
54
COALESCE((SELECT last_material_request_id FROM updated LIMIT 1),
55
(SELECT last_material_request_id FROM inserted LIMIT 1)),
56
COALESCE((SELECT material_request_prefix FROM updated LIMIT 1),
57
(SELECT material_request_prefix FROM inserted LIMIT 1)),
58
COALESCE((SELECT material_request_length FROM updated LIMIT 1),
59
(SELECT material_request_length FROM inserted LIMIT 1))
60
INTO v_new_id, v_prefix, v_length;
61
62
-- 5. Generate Material Request Number
63
v_material_request_no := v_prefix || LPAD(v_new_id::text, v_length, '0');
64
65
RETURN v_material_request_no;
66
END;
67
$function$
|
|||||
| Function | get_gate_pass_by_id | Missing in Target |
Source Script
Target Script
1
CREATE OR REPLACE FUNCTION public.get_gate_pass_by_id(p_gate_pass_id uuid)
2
RETURNS TABLE(id uuid, gate_pass_no character varying, gate_pass_type character varying, warehouse_id uuid, warehouse_name character varying, party_type character varying, party_id uuid, vehicle_no character varying, driver_name character varying, purpose character varying, reference_document_type character varying, reference_document_id uuid, gate_out_time timestamp without time zone, gate_in_time timestamp without time zone, status character varying, remarks 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 uuid, modified_by uuid, created_by_name character varying, modified_by_name character varying, details jsonb, logs jsonb)
3
LANGUAGE sql
4
AS $function$
5
SELECT
6
gh.id,
7
gh.gate_pass_no,
8
gh.gate_pass_type,
9
gh.warehouse_id,
10
w."name" AS warehouse_name,
11
gh.party_type,
12
gh.party_id,
13
gh.vehicle_no,
14
gh.driver_name,
15
gh.purpose,
16
gh.reference_document_type,
17
gh.reference_document_id,
18
gh.gate_out_time,
19
gh.gate_in_time,
20
gh.status,
21
gh.remarks,
22
gh.created_on_utc,
23
gh.modified_on_utc,
24
gh.deleted_on_utc,
25
gh.is_deleted,
26
gh.created_by,
27
gh.modified_by,
28
CONCAT(cu.first_name, ' ', cu.last_name) AS created_by_name,
29
CONCAT(mu.first_name, ' ', mu.last_name) AS modified_by_name,
30
31
-- ================= DETAILS =================
32
(
33
SELECT jsonb_agg(
34
jsonb_build_object(
35
'id', gpd.id,
36
'productId', gpd.product_id,
37
'productName', p.product_name,
38
'unitId', gpd.unit_id,
39
'unitName', u.unit_name,
40
'quantity', gpd.quantity,
41
'remarks', gpd.remarks
42
)
43
)
44
FROM gate_pass_details gpd
45
LEFT JOIN products p ON p.id = gpd.product_id
46
LEFT JOIN units u ON u.id = gpd.unit_id
47
WHERE gpd.gate_pass_header_id = gh.id
48
AND gpd.is_deleted = false
49
) AS details,
50
51
-- ================= LOGS =================
52
(
53
SELECT jsonb_agg(
54
jsonb_build_object(
55
'id', gl.id,
56
'log_message', gl.log_message,
57
'created_on_utc', gl.created_on_utc,
58
'created_by', gl.created_by,
59
'created_by_name',
60
CONCAT(u.first_name, ' ', u.last_name)
61
)
62
ORDER BY gl.created_on_utc DESC
63
)
64
FROM gate_pass_logs gl
65
LEFT JOIN users u ON u.id = gl.created_by
66
WHERE gl.gate_pass_header_id = gh.id
67
) AS logs
68
69
FROM gate_pass_headers gh
70
LEFT JOIN warehouses w ON w.id = gh.warehouse_id
71
LEFT JOIN users cu ON cu.id = gh.created_by
72
LEFT JOIN users mu ON mu.id = gh.modified_by
73
74
WHERE gh.id = p_gate_pass_id
75
AND gh.is_deleted = false;
76
$function$
|
|||||
| Function | get_material_request_by_id | Missing in Target |
Source Script
Target Script
1
CREATE OR REPLACE FUNCTION public.get_material_request_by_id(p_material_request_id uuid)
2
RETURNS TABLE(id uuid, organization_id uuid, company_id uuid, request_no character varying, request_type character varying, requested_by uuid, requesting_department character varying, warehouse_id uuid, warehouse_name character varying, required_date date, status character varying, remarks text, created_on_utc timestamp without time zone, details jsonb, approval_logs jsonb)
3
LANGUAGE plpgsql
4
AS $function$
5
BEGIN
6
RETURN QUERY
7
SELECT
8
h.id,
9
h.organization_id,
10
h.company_id,
11
h.request_no,
12
h.request_type,
13
h.requested_by,
14
h.requesting_department,
15
h.warehouse_id,
16
w.name AS warehouse_name,
17
h.required_date,
18
h.status,
19
h.remarks,
20
h.created_on_utc,
21
22
-- 🔹 DETAILS JSON ARRAY
23
(
24
SELECT jsonb_agg(
25
jsonb_build_object(
26
'id', d.id,
27
'product_id', d.product_id,
28
'product_name', p.product_name,
29
'unit_id', d.unit_id,
30
'unit_name', u.unit_name,
31
'requested_qty', d.requested_qty,
32
'approved_qty', d.approved_qty,
33
'issued_qty', d.issued_qty,
34
'remarks', d.remarks
35
)
36
)
37
FROM public.material_request_details d
38
LEFT JOIN public.products p ON p.id = d.product_id
39
LEFT JOIN public.units u ON u.id = d.unit_id
40
WHERE d.material_request_header_id = h.id
41
AND d.is_deleted = false
42
) AS details,
43
44
-- 🔹 APPROVAL LOGS JSON ARRAY
45
(
46
SELECT jsonb_agg(
47
jsonb_build_object(
48
'id', l.id,
49
'status_id', l.status_id,
50
'approved_by', l.approved_by,
51
'approved_on', l.approved_on,
52
'comment', l.comment,
53
'approval_level', l.approval_level
54
)
55
ORDER BY l.approval_level, l.approved_on
56
)
57
FROM public.material_request_approval_logs l
58
WHERE l.material_request_id = h.id
59
AND l.is_deleted = false
60
) AS approval_logs
61
62
FROM public.material_request_headers h
63
LEFT JOIN public.warehouses w ON w.id = h.warehouse_id
64
65
WHERE h.id = p_material_request_id
66
AND h.is_deleted = false;
67
68
END;
69
$function$
|
|||||
| Function | get_all_gate_passes | Missing in Target |
Source Script
Target Script
1
CREATE OR REPLACE FUNCTION public.get_all_gate_passes(p_company_id uuid, p_start_date timestamp without time zone, p_end_date timestamp without time zone)
2
RETURNS TABLE(id uuid, gate_pass_no character varying, gate_pass_type character varying, warehouse_id uuid, warehouse_name character varying, party_type character varying, party_id uuid, vehicle_no character varying, driver_name character varying, purpose character varying, gate_out_time timestamp without time zone, gate_in_time timestamp without time zone, status character varying, remarks 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 uuid, modified_by uuid, created_by_name character varying, modified_by_name character varying)
3
LANGUAGE sql
4
STABLE
5
AS $function$
6
SELECT
7
gh.id,
8
gh.gate_pass_no,
9
gh.gate_pass_type,
10
gh.warehouse_id,
11
w.name AS warehouse_name,
12
gh.party_type,
13
gh.party_id,
14
gh.vehicle_no,
15
gh.driver_name,
16
gh.purpose,
17
gh.gate_out_time,
18
gh.gate_in_time,
19
gh.status,
20
gh.remarks,
21
gh.created_on_utc,
22
gh.modified_on_utc,
23
gh.deleted_on_utc,
24
gh.is_deleted,
25
gh.created_by,
26
gh.modified_by,
27
28
CAST(TRIM(CONCAT(cu.first_name, ' ', cu.last_name)) AS varchar) AS created_by_name,
29
CAST(TRIM(CONCAT(mu.first_name, ' ', mu.last_name)) AS varchar) AS modified_by_name
30
31
FROM public.gate_pass_headers gh
32
33
LEFT JOIN public.warehouses w
34
ON gh.warehouse_id = w.id
35
AND w.is_deleted = false
36
37
LEFT JOIN public.users cu
38
ON cu.id = gh.created_by
39
40
LEFT JOIN public.users mu
41
ON mu.id = gh.modified_by
42
43
WHERE gh.company_id = p_company_id
44
AND gh.is_deleted = false
45
AND (p_start_date IS NULL OR gh.created_on_utc >= p_start_date)
46
AND (p_end_date IS NULL OR gh.created_on_utc <= p_end_date)
47
48
ORDER BY gh.created_on_utc DESC;
49
50
$function$
|
|||||
| Function | get_new_goods_receipt_number | Missing in Target |
Source Script
Target Script
1
CREATE OR REPLACE FUNCTION public.get_new_goods_receipt_number(p_company_id uuid, p_date date)
2
RETURNS character varying
3
LANGUAGE plpgsql
4
AS $function$
5
DECLARE
6
v_fin_year int;
7
v_new_id int;
8
v_prefix varchar;
9
v_length int;
10
v_receipt_no varchar;
11
BEGIN
12
-- 1. Financial Year (April–March)
13
IF EXTRACT(MONTH FROM p_date) >= 4 THEN
14
v_fin_year := EXTRACT(YEAR FROM p_date);
15
ELSE
16
v_fin_year := EXTRACT(YEAR FROM p_date) - 1;
17
END IF;
18
19
-- 2. Atomic UPDATE
20
WITH updated AS (
21
UPDATE public.goods_receipt_numbers
22
SET last_goods_receipt_id = last_goods_receipt_id + 1
23
WHERE company_id = p_company_id
24
AND fin_year = v_fin_year
25
RETURNING
26
last_goods_receipt_id,
27
goods_receipt_prefix,
28
goods_receipt_length
29
),
30
inserted AS (
31
INSERT INTO public.goods_receipt_numbers (
32
company_id,
33
fin_year,
34
goods_receipt_prefix,
35
goods_receipt_length,
36
last_goods_receipt_id
37
)
38
SELECT
39
p_company_id,
40
v_fin_year,
41
'GR',
42
6,
43
1
44
WHERE NOT EXISTS (SELECT 1 FROM updated)
45
RETURNING
46
last_goods_receipt_id,
47
goods_receipt_prefix,
48
goods_receipt_length
49
)
50
51
SELECT
52
COALESCE((SELECT last_goods_receipt_id FROM updated LIMIT 1),
53
(SELECT last_goods_receipt_id FROM inserted LIMIT 1)),
54
COALESCE((SELECT goods_receipt_prefix FROM updated LIMIT 1),
55
(SELECT goods_receipt_prefix FROM inserted LIMIT 1)),
56
COALESCE((SELECT goods_receipt_length FROM updated LIMIT 1),
57
(SELECT goods_receipt_length FROM inserted LIMIT 1))
58
INTO v_new_id, v_prefix, v_length;
59
60
-- 3. Generate Receipt No
61
v_receipt_no := v_prefix || LPAD(v_new_id::text, v_length, '0');
62
63
RETURN v_receipt_no;
64
END;
65
$function$
|
|||||
| Function | get_next_mr_status | Missing in Target |
Source Script
Target Script
1
CREATE OR REPLACE FUNCTION public.get_next_mr_status(p_company_id uuid, p_current_status integer)
2
RETURNS TABLE(next_status integer)
3
LANGUAGE plpgsql
4
AS $function$
5
BEGIN
6
RETURN QUERY
7
SELECT mw.next_status
8
FROM material_request_workflows mw
9
WHERE mw.company_id = p_company_id
10
AND mw.status = p_current_status
11
AND mw.is_deleted = false;
12
END;
13
$function$
|
|||||
| Function | get_goods_issue_by_id | Missing in Target |
Source Script
Target Script
1
CREATE OR REPLACE FUNCTION public.get_goods_issue_by_id(p_id uuid)
2
RETURNS TABLE(id uuid, organization_id uuid, company_id uuid, issue_no character varying, issue_type character varying, warehouse_id uuid, warehouse_name character varying, requested_by uuid, requested_by_name character varying, approved_by uuid, approved_by_name character varying, issued_to_type character varying, issued_to_id uuid, issue_date timestamp without time zone, status character varying, remarks text, posted_on_utc timestamp without time zone, posted_by uuid, source_document_type character varying, source_document_id uuid, created_on_utc timestamp without time zone, modified_on_utc timestamp without time zone, is_deleted boolean, created_by uuid, modified_by uuid, detail_id uuid, detail_product_id uuid, product_name character varying, detail_unit_id uuid, unit_name character varying, detail_requested_qty numeric, detail_issued_qty numeric, detail_rate numeric, detail_amount numeric, detail_warehouse_location_id uuid, warehouse_location_name character varying, detail_remarks text)
3
LANGUAGE plpgsql
4
AS $function$
5
BEGIN
6
RETURN QUERY
7
SELECT
8
gih.id,
9
gih.organization_id,
10
gih.company_id,
11
gih.issue_no,
12
gih.issue_type,
13
gih.warehouse_id,
14
COALESCE(w.name, '')::character varying AS warehouse_name,
15
gih.requested_by,
16
COALESCE(concat_ws(' ', u_requested.first_name, u_requested.last_name), '')::character varying AS requested_by_name,
17
gih.approved_by,
18
COALESCE(concat_ws(' ', u_approved.first_name, u_approved.last_name), '')::character varying AS approved_by_name,
19
gih.issued_to_type,
20
gih.issued_to_id,
21
gih.issue_date,
22
gih.status,
23
gih.remarks,
24
gih.posted_on_utc,
25
gih.posted_by,
26
gih.source_document_type,
27
gih.source_document_id,
28
gih.created_on_utc,
29
gih.modified_on_utc,
30
gih.is_deleted,
31
gih.created_by,
32
gih.modified_by,
33
gid.id AS detail_id,
34
gid.product_id AS detail_product_id,
35
COALESCE(p.product_name, '')::character varying AS product_name,
36
gid.unit_id AS detail_unit_id,
37
COALESCE(u.unit_name, '')::character varying AS unit_name,
38
gid.requested_qty AS detail_requested_qty,
39
gid.issued_qty AS detail_issued_qty,
40
gid.rate AS detail_rate,
41
gid.amount AS detail_amount,
42
gid.warehouse_location_id AS detail_warehouse_location_id,
43
COALESCE(wl.name, '')::character varying AS warehouse_location_name,
44
gid.remarks AS detail_remarks
45
FROM goods_issue_headers gih
46
LEFT JOIN warehouses w
47
ON gih.warehouse_id = w.id
48
AND (w.is_deleted IS FALSE OR w.is_deleted IS NULL)
49
LEFT JOIN users u_requested
50
ON gih.requested_by = u_requested.id
51
LEFT JOIN users u_approved
52
ON gih.approved_by = u_approved.id
53
LEFT JOIN goods_issue_details gid
54
ON gih.id = gid.goods_issue_header_id
55
LEFT JOIN products p
56
ON gid.product_id = p.id
57
AND (p.is_deleted IS FALSE OR p.is_deleted IS NULL)
58
LEFT JOIN units u
59
ON gid.unit_id = u.id
60
LEFT JOIN warehouse_locations wl
61
ON gid.warehouse_location_id = wl.id
62
AND (wl.is_deleted IS FALSE OR wl.is_deleted IS NULL)
63
WHERE gih.id = p_id
64
AND (gih.is_deleted IS FALSE OR gih.is_deleted IS NULL);
65
END;
66
$function$
|
|||||
| Function | get_goods_issues | Missing in Target |
Source Script
Target Script
1
CREATE OR REPLACE FUNCTION public.get_goods_issues(p_company_id uuid)
2
RETURNS TABLE(id uuid, organization_id uuid, company_id uuid, issue_no character varying, issue_type character varying, warehouse_id uuid, warehouse_name character varying, requested_by uuid, requested_by_name character varying, approved_by uuid, approved_by_name character varying, issued_to_type character varying, issued_to_id uuid, issue_date timestamp without time zone, status character varying, remarks text, posted_on_utc timestamp without time zone, posted_by uuid, source_document_type character varying, source_document_id uuid, created_on_utc timestamp without time zone, modified_on_utc timestamp without time zone, is_deleted boolean, created_by uuid, modified_by uuid)
3
LANGUAGE sql
4
AS $function$
5
SELECT
6
gih.id,
7
gih.organization_id,
8
gih.company_id,
9
gih.issue_no,
10
gih.issue_type,
11
gih.warehouse_id,
12
COALESCE(w.name, '')::character varying AS warehouse_name,
13
gih.requested_by,
14
COALESCE(concat_ws(' ', u_requested.first_name, u_requested.last_name), '')::character varying AS requested_by_name,
15
gih.approved_by,
16
COALESCE(concat_ws(' ', u_approved.first_name, u_approved.last_name), '')::character varying AS approved_by_name,
17
gih.issued_to_type,
18
gih.issued_to_id,
19
gih.issue_date,
20
gih.status,
21
gih.remarks,
22
gih.posted_on_utc,
23
gih.posted_by,
24
gih.source_document_type,
25
gih.source_document_id,
26
gih.created_on_utc,
27
gih.modified_on_utc,
28
gih.is_deleted,
29
gih.created_by,
30
gih.modified_by
31
FROM goods_issue_headers gih
32
LEFT JOIN warehouses w
33
ON gih.warehouse_id = w.id
34
AND (w.is_deleted IS FALSE OR w.is_deleted IS NULL)
35
LEFT JOIN users u_requested
36
ON gih.requested_by = u_requested.id
37
LEFT JOIN users u_approved
38
ON gih.approved_by = u_approved.id
39
WHERE gih.company_id = p_company_id
40
AND (gih.is_deleted IS FALSE OR gih.is_deleted IS NULL)
41
ORDER BY gih.created_on_utc DESC;
42
$function$
|
|||||
| Function | get_inventory_ledger | Missing in Target |
Source Script
Target Script
1
CREATE OR REPLACE FUNCTION public.get_inventory_ledger(p_company_id uuid, p_product_id uuid DEFAULT NULL::uuid, p_warehouse_id uuid DEFAULT NULL::uuid, p_warehouse_location_id uuid DEFAULT NULL::uuid, p_batch_id uuid DEFAULT NULL::uuid, p_serial_number text DEFAULT NULL::text, p_stock_movement_type_id integer DEFAULT NULL::integer, p_reference_document_type text DEFAULT NULL::text, p_reference_document_id uuid DEFAULT NULL::uuid, p_from_date_utc timestamp without time zone DEFAULT NULL::timestamp without time zone, p_to_date_utc timestamp without time zone DEFAULT NULL::timestamp without time zone)
2
RETURNS TABLE(id uuid, movement_date_utc timestamp without time zone, product_id uuid, product_name text, product_code text, sku text, warehouse_id uuid, warehouse_name text, warehouse_location_id uuid, warehouse_location_name text, batch_id uuid, serial_number text, stock_movement_type_id integer, stock_movement_type_name text, quantity numeric, qty_in numeric, qty_out numeric, balance_qty numeric, unit_cost numeric, total_cost numeric, reference_type text, reference_id uuid, reference_document_id uuid, reference_document_type text, created_by uuid, created_by_name text, created_on_utc timestamp without time zone)
3
LANGUAGE sql
4
AS $function$
5
SELECT
6
sl.id,
7
sl.movement_date_utc,
8
sl.product_id,
9
p.product_name,
10
p.product_code,
11
p.sku,
12
sl.warehouse_id,
13
w.name as warehouse_name,
14
sl.warehouse_location_id,
15
wl.name AS warehouse_location_name,
16
sl.batch_id,
17
sl.serial_number,
18
sl.stock_movement_type_id,
19
smt.name AS stock_movement_type_name,
20
sl.quantity_base as quantity,
21
COALESCE(sl.qty_in_base, 0) AS qty_in,
22
COALESCE(sl.qty_out_base, 0) AS qty_out,
23
COALESCE(sl.balance_qty, 0) AS balance_qty,
24
sl.unit_cost,
25
sl.total_cost,
26
sl.reference_type,
27
sl.reference_id,
28
sl.reference_document_id,
29
sl.reference_document_type,
30
sl.created_by,
31
COALESCE(TRIM(usr.first_name || ' ' || usr.last_name), '') AS created_by_name,
32
sl.created_on_utc
33
FROM public.stock_ledgers sl
34
INNER JOIN public.products p ON p.id = sl.product_id
35
INNER JOIN public.warehouses w ON w.id = sl.warehouse_id
36
LEFT JOIN public.warehouse_locations wl ON wl.id = sl.warehouse_location_id
37
INNER JOIN public.stock_movement_types smt ON smt.id = sl.stock_movement_type_id
38
INNER JOIN public.users usr ON usr.id = sl.created_by
39
WHERE sl.is_deleted = false
40
AND sl.company_id = p_company_id
41
AND (p_product_id IS NULL OR sl.product_id = p_product_id)
42
AND (p_warehouse_id IS NULL OR sl.warehouse_id = p_warehouse_id)
43
AND (p_warehouse_location_id IS NULL OR sl.warehouse_location_id = p_warehouse_location_id)
44
AND (p_batch_id IS NULL OR sl.batch_id = p_batch_id)
45
AND (p_serial_number IS NULL OR sl.serial_number = p_serial_number)
46
AND (p_stock_movement_type_id IS NULL OR sl.stock_movement_type_id = p_stock_movement_type_id)
47
AND (p_reference_document_type IS NULL OR sl.reference_document_type = p_reference_document_type)
48
AND (p_reference_document_id IS NULL OR sl.reference_document_id = p_reference_document_id)
49
AND (p_from_date_utc IS NULL OR sl.movement_date_utc >= p_from_date_utc)
50
AND (p_to_date_utc IS NULL OR sl.movement_date_utc <= p_to_date_utc)
51
ORDER BY sl.movement_date_utc, sl.created_on_utc, sl.id;
52
$function$
|
|||||
| Procedure | hard_delete_inventory_data | Missing in Target |
Source Script
Target Script
1
CREATE OR REPLACE PROCEDURE public.hard_delete_inventory_data(IN p_company_id uuid)
2
LANGUAGE plpgsql
3
AS $procedure$
4
BEGIN
5
-- Delete from dependent tables first (if foreign key constraints exist)
6
7
-- Orders (assuming these are transactional)
8
DELETE FROM orders WHERE company_id = p_company_id;
9
10
-- Inventory master data
11
DELETE FROM company_products WHERE company_id = p_company_id;
12
DELETE FROM company_categories WHERE company_id = p_company_id;
13
DELETE FROM product_tax_categories WHERE company_id = p_company_id;
14
15
-- Units and Users
16
DELETE FROM units WHERE company_id = p_company_id;
17
DELETE FROM users WHERE company_id = p_company_id;
18
19
RAISE NOTICE 'Inventory data for company_id % has been hard deleted.', p_company_id;
20
END;
21
$procedure$
|
|||||
| Procedure | hard_delete_org_inventory | Missing in Target |
Source Script
Target Script
1
CREATE OR REPLACE PROCEDURE public.hard_delete_org_inventory(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
DELETE FROM orders WHERE company_id = v_company_id;
9
DELETE FROM company_products WHERE company_id = v_company_id;
10
DELETE FROM company_categories WHERE company_id = v_company_id;
11
DELETE FROM product_tax_categories WHERE company_id = v_company_id;
12
DELETE FROM units WHERE company_id = v_company_id;
13
DELETE FROM users WHERE company_id = v_company_id;
14
DELETE FROM companies WHERE id = v_company_id;
15
END LOOP;
16
17
DELETE FROM organizations WHERE id = p_organization_id;
18
19
RAISE NOTICE 'Deleted inventory data for organization_id: %', p_organization_id;
20
END;
21
$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
v_is_apartment boolean;
7
v_products_exist boolean;
8
v_categories_exist boolean;
9
BEGIN
10
-- Check if company already exists
11
SELECT EXISTS (
12
SELECT 1 FROM public.companies
13
WHERE id = p_company_id
14
) INTO v_company_exists;
15
16
IF NOT v_company_exists THEN
17
-- Insert into companies table
18
INSERT INTO public.companies (
19
id,
20
organization_id,
21
is_apartment,
22
name,
23
created_on_utc,
24
created_by
25
) VALUES (
26
p_company_id,
27
p_organization_id,
28
p_is_apartment,
29
p_name,
30
NOW(),
31
p_created_by
32
);
33
RAISE NOTICE 'Created company % (ID: %) for organization %',
34
p_name, p_company_id, p_organization_id;
35
ELSE
36
RAISE NOTICE 'Company % (ID: %) already exists for organization %. Skipping initialization.',
37
p_name, p_company_id, p_organization_id;
38
END IF;
39
40
-- Check if the company is an apartment
41
IF p_is_apartment THEN
42
-- Check if products already exist for this company
43
SELECT EXISTS (
44
SELECT 1 FROM public.company_products
45
WHERE company_id = p_company_id
46
) INTO v_products_exist;
47
48
IF NOT v_products_exist THEN
49
-- Insert into company_products
50
INSERT INTO public.company_products(
51
id,
52
company_id,
53
product_id,
54
created_on_utc,
55
created_by
56
)
57
SELECT
58
gen_random_uuid(),
59
p_company_id,
60
product_id,
61
NOW(),
62
p_created_by
63
FROM public.default_apartment_products;
64
65
RAISE NOTICE 'Added default products for apartment company %', p_name;
66
ELSE
67
RAISE NOTICE 'Products already exist for company %. Skipping product initialization.', p_name;
68
END IF;
69
END IF;
70
71
-- Check if categories already exist for this company
72
SELECT EXISTS (
73
SELECT 1 FROM public.company_categories
74
WHERE company_id = p_company_id
75
) INTO v_categories_exist;
76
77
IF NOT v_categories_exist THEN
78
-- Insert into company_categories
79
INSERT INTO public.company_categories (
80
id,
81
company_id,
82
category_id,
83
created_on_utc,
84
created_by
85
)
86
SELECT
87
nextval('company_categories_seq'),
88
p_company_id,
89
category_id,
90
NOW(),
91
p_created_by
92
FROM public.company_categories cc
93
WHERE cc.company_id = p_default_company_id;
94
95
RAISE NOTICE 'Copied categories from default company % to new company %',
96
p_default_company_id, p_company_id;
97
ELSE
98
RAISE NOTICE 'Categories already exist for company %. Skipping category initialization.', p_name;
99
END IF;
100
END;
101
$procedure$
|
|||||
| Procedure | initialize_company_products | Missing in Target |
Source Script
Target Script
1
CREATE OR REPLACE PROCEDURE public.initialize_company_products(IN old_company_id uuid, IN new_company_id uuid)
2
LANGUAGE plpgsql
3
AS $procedure$
4
BEGIN
5
-- Insert new records for the new company
6
INSERT INTO company_products (
7
id,
8
company_id,
9
product_id,
10
created_on_utc,
11
created_by
12
)
13
SELECT
14
gen_random_uuid(), -- Generate a new UUID for the id column
15
new_company_id, -- Assign the new company ID
16
product_id,
17
created_on_utc,
18
created_by
19
FROM company_products
20
WHERE company_id = old_company_id;
21
22
-- Optional: Log the operation
23
RAISE NOTICE 'Company products have been copied from company % to company %.', old_company_id, new_company_id;
24
25
END;
26
$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 company-specific products
12
DELETE FROM public.company_products
13
WHERE company_id = p_company_id;
14
15
-- Delete company-specific categories
16
DELETE FROM public.company_categories
17
WHERE company_id = p_company_id;
18
19
-- Delete users associated with the company
20
DELETE FROM public.users
21
WHERE users.company_id = p_company_id;
22
23
-- Delete from companies
24
DELETE FROM public.companies
25
WHERE id = p_company_id;
26
END LOOP;
27
28
-- Delete the organization itself
29
DELETE FROM public.organizations
30
WHERE public.organizations.id = p_organization_id;
31
32
-- Log the operation
33
RAISE NOTICE 'Organization with ID % and all related data have been hard deleted.', p_organization_id;
34
35
END;
36
$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_ids text, IN p_company_names text, IN p_user_id uuid, IN p_user_first_name character varying, IN p_user_last_name character varying, IN p_phone_number character varying, IN p_email 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;
6
v_company_name text;
7
v_company_ids uuid[];
8
v_company_names text[];
9
i integer;
10
v_organization_exists boolean;
11
v_user_exists boolean;
12
v_company_exists boolean;
13
v_existing_user_id uuid;
14
v_update_count integer;
15
BEGIN
16
17
SELECT EXISTS (
18
SELECT 1 FROM public.organizations
19
WHERE id = p_id
20
) INTO v_organization_exists;
21
22
IF v_organization_exists THEN
23
RAISE NOTICE 'Organization with ID % already exists. Skipping organization creation.', p_id;
24
ELSE
25
-- Insert organization if it doesn't exist
26
INSERT INTO public.organizations (
27
id, name, created_on_utc, created_by, is_deleted
28
) VALUES (
29
p_id, p_name, NOW(), p_created_by, false );
30
RAISE NOTICE 'Initialized organization: % with ID: %', p_name, p_id;
31
END IF;
32
33
-- Parse company IDs and names
34
v_company_ids := string_to_array(p_company_ids, ',');
35
v_company_names := string_to_array(p_company_names, ',');
36
37
-- Initialize companies with duplicate checks
38
FOR i IN 1..array_length(v_company_ids, 1) LOOP
39
v_company_id := v_company_ids[i];
40
v_company_name := v_company_names[i];
41
42
CALL public.initialize_company(
43
v_company_id,
44
p_id,
45
true, -- Indicates it's an apartment
46
v_company_name,
47
p_created_by,
48
p_default_company_id
49
);
50
END LOOP;
51
52
-- Assign the first company ID from the array
53
v_company_id := v_company_ids[1];
54
55
-- Check if user already exists (by both ID and email)
56
SELECT id INTO v_existing_user_id FROM public.users
57
WHERE id = p_user_id OR email = p_email
58
LIMIT 1;
59
60
v_user_exists := (v_existing_user_id IS NOT NULL);
61
62
IF NOT v_user_exists THEN
63
INSERT INTO public.users (
64
id, first_name, last_name, email,
65
created_on_utc, created_by, password_hash,
66
phone_number, company_id
67
) VALUES (
68
p_user_id, p_user_first_name, p_user_last_name, p_email,
69
NOW(), p_created_by, '',
70
p_phone_number, v_company_id
71
);
72
RAISE NOTICE 'New user created with ID: % and email: %', p_user_id, p_email;
73
ELSE
74
UPDATE public.users
75
SET
76
company_id = v_company_id,
77
first_name = COALESCE(p_user_first_name, first_name),
78
last_name = COALESCE(p_user_last_name, last_name),
79
phone_number = COALESCE(p_phone_number, phone_number),
80
email = COALESCE(p_email, email)
81
WHERE id = v_existing_user_id;
82
83
GET DIAGNOSTICS v_update_count = ROW_COUNT;
84
85
IF v_update_count > 0 THEN
86
RAISE NOTICE 'Updated existing user % (ID: %) with new company association %',
87
p_email, v_existing_user_id, v_company_id;
88
ELSE
89
RAISE NOTICE 'User % (ID: %) already associated with company %. No changes made.',
90
p_email, v_existing_user_id, v_company_id;
91
END IF;
92
END IF;
93
94
RAISE NOTICE 'Organization initialization process completed for % (ID: %)', p_name, p_id;
95
END;
96
$procedure$
|
|||||
| Procedure | purge_inventory_organization_data | Missing in Target |
Source Script
Target Script
1
CREATE OR REPLACE PROCEDURE public.purge_inventory_organization_data(IN p_organization_ids uuid[] DEFAULT NULL::uuid[])
2
LANGUAGE plpgsql
3
AS $procedure$
4
DECLARE
5
-- Retention policy
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
-- Orders
14
v_order_ids uuid[] := '{}';
15
v_order_detail_ids uuid[] := '{}';
16
17
-- Company-scoped master/config
18
v_company_product_ids uuid[] := '{}';
19
v_company_category_ids int[] := '{}';
20
v_ptc_ids int[] := '{}'; -- product_tax_categories.id
21
v_ptcd_ids int[] := '{}'; -- product_tax_category_details.id
22
v_unit_ids uuid[] := '{}';
23
24
-- Inventory quantities tied to company products
25
v_pwq_ids int[] := '{}'; -- product_warehouse_quantities.id
26
BEGIN
27
START TRANSACTION;
28
29
--------------------------------------------------------------------
30
-- 1) Resolve target organizations and companies (uniform block)
31
--------------------------------------------------------------------
32
IF p_organization_ids IS NULL OR array_length(p_organization_ids, 1) IS NULL THEN
33
SELECT array_agg(id)
34
INTO v_orgs
35
FROM organizations
36
WHERE created_on_utc > '2025-05-23'
37
AND created_on_utc < (NOW() - interval '24 hours');
38
ELSE
39
v_orgs := p_organization_ids;
40
END IF;
41
42
IF v_orgs IS NULL OR array_length(v_orgs, 1) IS NULL THEN
43
RAISE NOTICE 'No organizations found for cleanup.';
44
COMMIT;
45
RETURN;
46
END IF;
47
48
-- Companies by organization
49
SELECT COALESCE(array_agg(id), '{}')
50
INTO v_companies
51
FROM companies
52
WHERE organization_id = ANY(v_orgs);
53
54
IF v_companies IS NULL OR array_length(v_companies, 1) IS NULL THEN
55
RAISE NOTICE 'No companies resolved for inventory purge. Orgs: %', v_orgs;
56
COMMIT;
57
RETURN;
58
END IF;
59
60
RAISE NOTICE 'Inventory purge targets - Organizations: %; Companies: %', v_orgs, v_companies;
61
62
--------------------------------------------------------------------
63
-- 2) Collect IDs (COALESCE to '{}' to avoid NULL-array issues)
64
--------------------------------------------------------------------
65
-- Orders and details (time-gated)
66
SELECT COALESCE(array_agg(id), '{}')
67
INTO v_order_ids
68
FROM orders
69
WHERE company_id = ANY(v_companies)
70
AND created_on_utc < v_cutoff_24h;
71
72
SELECT COALESCE(array_agg(id), '{}')
73
INTO v_order_detail_ids
74
FROM order_details
75
WHERE order_id = ANY(v_order_ids);
76
77
-- Company products and categories
78
SELECT COALESCE(array_agg(id), '{}')
79
INTO v_company_product_ids
80
FROM company_products
81
WHERE company_id = ANY(v_companies);
82
83
SELECT COALESCE(array_agg(id), '{}')
84
INTO v_company_category_ids
85
FROM company_categories
86
WHERE company_id = ANY(v_companies);
87
88
-- Product tax categories (company-scoped) and their details
89
SELECT COALESCE(array_agg(id), '{}')
90
INTO v_ptc_ids
91
FROM product_tax_categories
92
WHERE company_id = ANY(v_companies);
93
94
SELECT COALESCE(array_agg(id), '{}')
95
INTO v_ptcd_ids
96
FROM product_tax_category_details
97
WHERE producttaxcategory_id = ANY(v_ptc_ids);
98
99
-- Units (company-scoped)
100
SELECT COALESCE(array_agg(id), '{}')
101
INTO v_unit_ids
102
FROM units
103
WHERE company_id = ANY(v_companies);
104
105
-- Product warehouse quantities for products linked to these companies
106
SELECT COALESCE(array_agg(pwq.id), '{}')
107
INTO v_pwq_ids
108
FROM product_warehouse_quantities pwq
109
WHERE pwq.product_id IN (
110
SELECT DISTINCT cp.product_id
111
FROM company_products cp
112
WHERE cp.company_id = ANY(v_companies)
113
);
114
115
--------------------------------------------------------------------
116
-- 3) Purge in strict child → parent order (avoid FK violations)
117
--------------------------------------------------------------------
118
-- Orders: details → headers
119
DELETE FROM order_details
120
WHERE id = ANY(v_order_detail_ids);
121
122
DELETE FROM orders
123
WHERE id = ANY(v_order_ids);
124
125
-- Inventory quantities tied to purged company-product assignments
126
DELETE FROM product_warehouse_quantities
127
WHERE id = ANY(v_pwq_ids);
128
129
-- Product tax category details → categories
130
DELETE FROM product_tax_category_details
131
WHERE id = ANY(v_ptcd_ids);
132
133
DELETE FROM product_tax_categories
134
WHERE id = ANY(v_ptc_ids);
135
136
-- Company-scoped assignments
137
DELETE FROM company_products
138
WHERE id = ANY(v_company_product_ids);
139
140
DELETE FROM company_categories
141
WHERE id = ANY(v_company_category_ids);
142
143
-- Company-scoped units
144
DELETE FROM units
145
WHERE id = ANY(v_unit_ids);
146
147
RAISE NOTICE 'Inventory purge complete for companies: % (orgs: %).', v_companies, v_orgs;
148
149
COMMIT;
150
151
EXCEPTION
152
WHEN OTHERS THEN
153
ROLLBACK;
154
RAISE NOTICE 'purge_inventory_organization_data failed: %', SQLERRM;
155
-- Optionally rethrow
156
-- RAISE;
157
END;
158
$procedure$
|
|||||
| Procedure | upsert_goods_issue | Missing in Target |
Source Script
Target Script
1
CREATE OR REPLACE PROCEDURE public.upsert_goods_issue(IN p_id uuid, IN p_organization_id uuid, IN p_company_id uuid, IN p_issue_no character varying, IN p_issue_type character varying, IN p_warehouse_id uuid, IN p_requested_by uuid, IN p_approved_by uuid, IN p_issued_to_type character varying, IN p_issued_to_id uuid, IN p_issue_date timestamp without time zone, IN p_status character varying, IN p_remarks text, IN p_posted_on_utc timestamp without time zone, IN p_posted_by uuid, IN p_source_document_type character varying, IN p_source_document_id uuid, IN p_created_by uuid, IN p_modified_by uuid, IN p_details jsonb, OUT p_operation_type character varying, OUT p_new_id uuid, OUT p_success boolean, OUT p_error_message text, OUT p_error_code character varying)
2
LANGUAGE plpgsql
3
AS $procedure$
4
DECLARE
5
v_existing_id uuid;
6
v_header_id uuid;
7
v_detail jsonb;
8
v_detail_id uuid;
9
v_is_insert boolean := false;
10
BEGIN
11
p_success := false;
12
p_operation_type := 'NONE';
13
p_error_message := NULL;
14
p_error_code := NULL;
15
p_new_id := NULL;
16
17
-- Validate required header fields
18
IF p_issue_no IS NULL OR trim(p_issue_no) = '' THEN
19
p_error_code := 'REQUIRED_FIELD_MISSING';
20
p_error_message := 'Issue number is required';
21
RETURN;
22
END IF;
23
24
IF p_issue_type IS NULL OR trim(p_issue_type) = '' THEN
25
p_error_code := 'REQUIRED_FIELD_MISSING';
26
p_error_message := 'Issue type is required';
27
RETURN;
28
END IF;
29
30
IF p_warehouse_id IS NULL THEN
31
p_error_code := 'REQUIRED_FIELD_MISSING';
32
p_error_message := 'Warehouse is required';
33
RETURN;
34
END IF;
35
36
IF p_issue_date IS NULL THEN
37
p_error_code := 'REQUIRED_FIELD_MISSING';
38
p_error_message := 'Issue date is required';
39
RETURN;
40
END IF;
41
42
IF p_status IS NULL OR trim(p_status) = '' THEN
43
p_error_code := 'REQUIRED_FIELD_MISSING';
44
p_error_message := 'Status is required';
45
RETURN;
46
END IF;
47
48
IF p_details IS NULL OR jsonb_typeof(p_details) <> 'array' OR jsonb_array_length(p_details) = 0 THEN
49
p_error_code := 'REQUIRED_FIELD_MISSING';
50
p_error_message := 'At least one detail line is required';
51
RETURN;
52
END IF;
53
54
-- Check if record exists
55
SELECT gih.id
56
INTO v_existing_id
57
FROM goods_issue_headers gih
58
WHERE gih.id = p_id;
59
60
-- Determine insert/update
61
v_is_insert := (v_existing_id IS NULL);
62
v_header_id := COALESCE(p_id, gen_random_uuid());
63
p_operation_type := CASE WHEN v_is_insert THEN 'INSERT' ELSE 'UPDATE' END;
64
65
BEGIN
66
-- Upsert header
67
INSERT INTO goods_issue_headers (
68
id,
69
organization_id,
70
company_id,
71
issue_no,
72
issue_type,
73
warehouse_id,
74
requested_by,
75
approved_by,
76
issued_to_type,
77
issued_to_id,
78
issue_date,
79
status,
80
remarks,
81
posted_on_utc,
82
posted_by,
83
source_document_type,
84
source_document_id,
85
created_on_utc,
86
modified_on_utc,
87
is_deleted,
88
created_by,
89
modified_by
90
)
91
VALUES (
92
v_header_id,
93
p_organization_id,
94
p_company_id,
95
p_issue_no,
96
p_issue_type,
97
p_warehouse_id,
98
p_requested_by,
99
p_approved_by,
100
p_issued_to_type,
101
p_issued_to_id,
102
p_issue_date,
103
p_status,
104
p_remarks,
105
p_posted_on_utc,
106
p_posted_by,
107
p_source_document_type,
108
p_source_document_id,
109
NOW() AT TIME ZONE 'UTC',
110
NOW() AT TIME ZONE 'UTC',
111
false,
112
p_created_by,
113
p_modified_by
114
)
115
ON CONFLICT (id) DO UPDATE SET
116
organization_id = EXCLUDED.organization_id,
117
company_id = EXCLUDED.company_id,
118
issue_no = EXCLUDED.issue_no,
119
issue_type = EXCLUDED.issue_type,
120
warehouse_id = EXCLUDED.warehouse_id,
121
requested_by = EXCLUDED.requested_by,
122
approved_by = EXCLUDED.approved_by,
123
issued_to_type = EXCLUDED.issued_to_type,
124
issued_to_id = EXCLUDED.issued_to_id,
125
issue_date = EXCLUDED.issue_date,
126
status = EXCLUDED.status,
127
remarks = EXCLUDED.remarks,
128
posted_on_utc = EXCLUDED.posted_on_utc,
129
posted_by = EXCLUDED.posted_by,
130
source_document_type = EXCLUDED.source_document_type,
131
source_document_id = EXCLUDED.source_document_id,
132
modified_on_utc = NOW() AT TIME ZONE 'UTC',
133
modified_by = p_modified_by;
134
135
-- Process details
136
FOR v_detail IN
137
SELECT value
138
FROM jsonb_array_elements(p_details)
139
LOOP
140
v_detail_id := NULLIF(v_detail->>'id', '')::uuid;
141
142
IF v_detail_id IS NULL
143
OR NOT EXISTS (
144
SELECT 1
145
FROM goods_issue_details gid
146
WHERE gid.id = v_detail_id
147
)
148
THEN
149
v_detail_id := COALESCE(v_detail_id, gen_random_uuid());
150
151
INSERT INTO goods_issue_details (
152
id,
153
goods_issue_header_id,
154
product_id,
155
unit_id,
156
requested_qty,
157
issued_qty,
158
rate,
159
amount,
160
warehouse_location_id,
161
remarks
162
)
163
VALUES (
164
v_detail_id,
165
v_header_id,
166
(v_detail->>'product_id')::uuid,
167
(v_detail->>'unit_id')::uuid,
168
(v_detail->>'requested_qty')::numeric,
169
(v_detail->>'issued_qty')::numeric,
170
(v_detail->>'rate')::numeric,
171
(v_detail->>'amount')::numeric,
172
NULLIF(v_detail->>'warehouse_location_id', '')::uuid,
173
v_detail->>'remarks'
174
);
175
ELSE
176
UPDATE goods_issue_details
177
SET
178
product_id = (v_detail->>'product_id')::uuid,
179
unit_id = (v_detail->>'unit_id')::uuid,
180
requested_qty = (v_detail->>'requested_qty')::numeric,
181
issued_qty = (v_detail->>'issued_qty')::numeric,
182
rate = (v_detail->>'rate')::numeric,
183
amount = (v_detail->>'amount')::numeric,
184
warehouse_location_id = NULLIF(v_detail->>'warehouse_location_id', '')::uuid,
185
remarks = v_detail->>'remarks'
186
WHERE id = v_detail_id
187
AND goods_issue_header_id = v_header_id;
188
END IF;
189
END LOOP;
190
191
-- Delete removed details only during update
192
IF NOT v_is_insert THEN
193
DELETE FROM goods_issue_details gid
194
WHERE gid.goods_issue_header_id = v_header_id
195
AND gid.id NOT IN (
196
SELECT NULLIF(x.value->>'id', '')::uuid
197
FROM jsonb_array_elements(p_details) AS x(value)
198
WHERE NULLIF(x.value->>'id', '') IS NOT NULL
199
);
200
END IF;
201
202
p_success := true;
203
p_new_id := v_header_id;
204
205
EXCEPTION
206
WHEN unique_violation THEN
207
p_error_code := 'UNIQUE_CONSTRAINT_VIOLATION';
208
p_error_message := 'A record with this unique identifier already exists';
209
p_success := false;
210
211
WHEN foreign_key_violation THEN
212
p_error_code := 'FOREIGN_KEY_VIOLATION';
213
p_error_message := 'Referenced record does not exist: ' || SQLERRM;
214
p_success := false;
215
216
WHEN check_violation THEN
217
p_error_code := 'CHECK_CONSTRAINT_VIOLATION';
218
p_error_message := 'Data validation failed: ' || SQLERRM;
219
p_success := false;
220
221
WHEN OTHERS THEN
222
p_error_code := 'UNKNOWN_ERROR';
223
p_error_message := 'An unexpected error occurred: ' || SQLERRM;
224
p_success := false;
225
END;
226
END;
227
$procedure$
|
|||||
| Procedure | upsert_gate_pass | Missing in Target |
Source Script
Target Script
1
CREATE OR REPLACE PROCEDURE public.upsert_gate_pass(IN p_header_id uuid, IN p_org_id uuid, IN p_company_id uuid, IN p_gate_pass_no character varying, IN p_type character varying, IN p_warehouse_id uuid, IN p_party_type character varying, IN p_party_id uuid, IN p_vehicle_no character varying, IN p_driver_name character varying, IN p_status character varying, IN p_user_id uuid, IN p_details jsonb)
2
LANGUAGE plpgsql
3
AS $procedure$
4
BEGIN
5
-- 1. UPSERT THE HEADER
6
INSERT INTO public.gate_pass_headers (
7
id, organization_id, company_id, gate_pass_no, gate_pass_type,
8
warehouse_id, party_type, party_id, vehicle_no, driver_name,
9
status, created_on_utc, created_by
10
)
11
VALUES (
12
p_header_id, p_org_id, p_company_id, p_gate_pass_no, p_type,
13
p_warehouse_id, p_party_type, p_party_id, p_vehicle_no, p_driver_name,
14
p_status, now() AT TIME ZONE 'UTC', p_user_id
15
)
16
ON CONFLICT (id) DO UPDATE SET
17
gate_pass_type = EXCLUDED.gate_pass_type,
18
warehouse_id = EXCLUDED.warehouse_id,
19
vehicle_no = EXCLUDED.vehicle_no,
20
driver_name = EXCLUDED.driver_name,
21
status = EXCLUDED.status,
22
modified_on_utc = now() AT TIME ZONE 'UTC',
23
modified_by = p_user_id;
24
25
-- 2. HANDLE DETAILS (Soft delete existing first if updating)
26
UPDATE public.gate_pass_details
27
SET is_deleted = true, deleted_on_utc = now() AT TIME ZONE 'UTC'
28
WHERE gate_pass_header_id = p_header_id;
29
30
-- 3. INSERT NEW/UPDATED DETAILS FROM JSON
31
INSERT INTO public.gate_pass_details (
32
id, gate_pass_header_id, product_id, unit_id, quantity, is_deleted
33
)
34
SELECT
35
COALESCE((item->>'id')::uuid, gen_random_uuid()),
36
p_header_id,
37
(item->>'product_id')::uuid,
38
(item->>'unit_id')::uuid,
39
(item->>'quantity')::numeric,
40
false
41
FROM jsonb_array_elements(p_details) AS item;
42
43
-- 4. LOG THE ACTION
44
INSERT INTO public.gate_pass_logs (id, gate_pass_header_id, log_message, created_by)
45
VALUES (
46
gen_random_uuid(),
47
p_header_id,
48
'Gate Pass saved/updated with status: ' || p_status,
49
p_user_id
50
);
51
-- COMMIT;
52
END;
53
$procedure$
|
|||||
| Procedure | insert_goods_receipt | Missing in Target |
Source Script
Target Script
1
CREATE OR REPLACE PROCEDURE public.insert_goods_receipt(IN p_organization_id uuid, IN p_company_id uuid, IN p_receipt_type character varying, IN p_warehouse_id uuid, IN p_vendor_id uuid, IN p_source_document_type character varying, IN p_source_document_id uuid, IN p_vendor_invoice_no character varying, IN p_receipt_date timestamp without time zone, IN p_status character varying, IN p_remarks text, IN p_created_by uuid, IN p_goods_receipt_details jsonb)
2
LANGUAGE plpgsql
3
AS $procedure$
4
DECLARE
5
v_detail jsonb;
6
v_goods_receipt_id uuid := gen_random_uuid();
7
v_receipt_no varchar;
8
BEGIN
9
-- 🔥 Generate Receipt No here
10
v_receipt_no := public.get_new_goods_receipt_number(
11
p_company_id,
12
p_receipt_date::date
13
);
14
15
-- Insert Header
16
INSERT INTO public.goods_receipt_headers
17
(
18
id,
19
organization_id,
20
company_id,
21
receipt_no,
22
receipt_type,
23
warehouse_id,
24
vendor_id,
25
source_document_type,
26
source_document_id,
27
vendor_invoice_no,
28
receipt_date,
29
status,
30
remarks,
31
created_on_utc,
32
is_deleted,
33
created_by
34
)
35
VALUES
36
(
37
v_goods_receipt_id,
38
p_organization_id,
39
p_company_id,
40
v_receipt_no,
41
p_receipt_type,
42
p_warehouse_id,
43
p_vendor_id,
44
p_source_document_type,
45
p_source_document_id,
46
p_vendor_invoice_no,
47
p_receipt_date,
48
p_status,
49
p_remarks,
50
now(),
51
false,
52
p_created_by
53
);
54
55
-- Insert Details
56
FOR v_detail IN
57
SELECT value FROM jsonb_array_elements(p_goods_receipt_details)
58
LOOP
59
INSERT INTO public.goods_receipt_details
60
(
61
id,
62
goods_receipt_header_id,
63
line_number,
64
product_id,
65
unit_id,
66
ordered_qty,
67
received_qty,
68
accepted_qty,
69
rejected_qty,
70
unit_rate,
71
line_amount,
72
warehouse_location_id,
73
remarks
74
)
75
VALUES
76
(
77
gen_random_uuid(),
78
v_goods_receipt_id,
79
(v_detail ->> 'line_number')::int,
80
(v_detail ->> 'product_id')::uuid,
81
(v_detail ->> 'unit_id')::uuid,
82
NULLIF(v_detail ->> 'ordered_qty', '')::numeric,
83
COALESCE((v_detail ->> 'received_qty')::numeric, 0),
84
NULLIF(v_detail ->> 'accepted_qty', '')::numeric,
85
NULLIF(v_detail ->> 'rejected_qty', '')::numeric,
86
NULLIF(v_detail ->> 'unit_rate', '')::numeric,
87
NULLIF(v_detail ->> 'line_amount', '')::numeric,
88
NULLIF(v_detail ->> 'warehouse_location_id', '')::uuid,
89
v_detail ->> 'remarks'
90
);
91
END LOOP;
92
93
END;
94
$procedure$
|
|||||
| Procedure | update_material_request_previous_status | Missing in Target |
Source Script
Target Script
1
CREATE OR REPLACE PROCEDURE public.update_material_request_previous_status(IN p_company_id uuid, IN p_material_request_status_id integer, IN p_material_request_ids uuid[], IN p_modified_by uuid)
2
LANGUAGE plpgsql
3
AS $procedure$
4
DECLARE
5
v_material_request RECORD;
6
v_minimum_status INTEGER := 1; -- Draft is minimum allowed status
7
v_target_status_name varchar(100);
8
v_current_status_id integer;
9
BEGIN
10
-- Resolve target status name from material_request_statuses master
11
SELECT mrs.name
12
INTO v_target_status_name
13
FROM public.material_request_statuses mrs
14
WHERE mrs.id = p_material_request_status_id
15
AND COALESCE(mrs.is_deleted, false) = false
16
LIMIT 1;
17
18
-- Validate target status
19
IF v_target_status_name IS NULL THEN
20
RAISE NOTICE 'Invalid target material request status id: %', p_material_request_status_id;
21
RETURN;
22
END IF;
23
24
-- Process material requests for previous status update
25
FOR v_material_request IN
26
SELECT
27
mrh.id,
28
mrh.status
29
FROM public.material_request_headers mrh
30
WHERE mrh.id = ANY(p_material_request_ids)
31
AND mrh.company_id = p_company_id
32
AND COALESCE(mrh.is_deleted, false) = false
33
LOOP
34
-- Resolve current status id from status name
35
SELECT mrs.id
36
INTO v_current_status_id
37
FROM public.material_request_statuses mrs
38
WHERE lower(mrs.name) = lower(v_material_request.status)
39
AND COALESCE(mrs.is_deleted, false) = false
40
LIMIT 1;
41
42
-- If current status is invalid, skip
43
IF v_current_status_id IS NULL THEN
44
RAISE NOTICE 'Invalid current status for Material Request ID: %', v_material_request.id;
45
CONTINUE;
46
END IF;
47
48
-- Ensure the status does not go below minimum allowed status
49
IF v_current_status_id > v_minimum_status THEN
50
-- Update material request to the previous status
51
UPDATE public.material_request_headers
52
SET status = v_target_status_name,
53
modified_by = p_modified_by,
54
modified_on_utc = now()
55
WHERE id = v_material_request.id;
56
57
-- Insert approval log
58
INSERT INTO public.material_request_approval_logs(
59
material_request_id,
60
status_id,
61
approved_by,
62
approved_on,
63
comment,
64
created_on_utc,
65
created_by,
66
approval_level
67
)
68
VALUES (
69
v_material_request.id,
70
p_material_request_status_id,
71
p_modified_by,
72
now(),
73
'Material request moved to previous status',
74
now(),
75
p_modified_by,
76
0
77
);
78
79
RAISE NOTICE 'Material Request status updated to previous status for ID: %', v_material_request.id;
80
ELSE
81
RAISE NOTICE 'Cannot decrement status below the minimum allowed status for Material Request ID: %', v_material_request.id;
82
END IF;
83
END LOOP;
84
END;
85
$procedure$
|
|||||
| Procedure | delete_goods_issue | Missing in Target |
Source Script
Target Script
1
CREATE OR REPLACE PROCEDURE public.delete_goods_issue(IN p_id uuid, IN p_deleted_by uuid)
2
LANGUAGE plpgsql
3
AS $procedure$
4
BEGIN
5
-- delete details (since no soft delete columns exist)
6
DELETE FROM public.goods_issue_details
7
WHERE goods_issue_header_id = p_id;
8
9
-- soft delete header
10
UPDATE public.goods_issue_headers
11
SET
12
is_deleted = true,
13
deleted_on_utc = NOW() AT TIME ZONE 'UTC',
14
modified_by = p_deleted_by,
15
modified_on_utc = NOW() AT TIME ZONE 'UTC'
16
WHERE id = p_id
17
AND (is_deleted IS FALSE OR is_deleted IS NULL);
18
END;
19
$procedure$
|
|||||
| Procedure | delete_gate_pass | Missing in Target |
Source Script
Target Script
1
CREATE OR REPLACE PROCEDURE public.delete_gate_pass(IN p_header_ids uuid[], IN p_user_id uuid)
2
LANGUAGE plpgsql
3
AS $procedure$
4
DECLARE
5
v_id UUID;
6
BEGIN
7
FOREACH v_id IN ARRAY p_header_ids
8
LOOP
9
-- Check
10
IF NOT EXISTS (
11
SELECT 1
12
FROM gate_pass_headers
13
WHERE id = v_id AND is_deleted = false
14
) THEN
15
RAISE EXCEPTION 'Gate pass % not found or already deleted', v_id;
16
END IF;
17
18
-- Header delete
19
UPDATE gate_pass_headers
20
SET
21
is_deleted = true,
22
deleted_on_utc = NOW(),
23
modified_on_utc = NOW(),
24
modified_by = p_user_id
25
WHERE id = v_id;
26
27
-- Details delete
28
UPDATE gate_pass_details
29
SET
30
is_deleted = true,
31
deleted_on_utc = NOW()
32
WHERE gate_pass_header_id = v_id
33
AND is_deleted = false;
34
35
-- Log
36
INSERT INTO gate_pass_logs (
37
id,
38
gate_pass_header_id,
39
log_message,
40
created_by,
41
created_on_utc
42
)
43
VALUES (
44
gen_random_uuid(),
45
v_id,
46
'Gate Pass deleted',
47
p_user_id,
48
NOW()
49
);
50
END LOOP;
51
END;
52
$procedure$
|
|||||
| Procedure | create_material_request | Missing in Target |
Source Script
Target Script
1
CREATE OR REPLACE PROCEDURE public.create_material_request(IN p_id uuid, IN p_organization_id uuid, IN p_company_id uuid, IN p_request_type character varying, IN p_requested_by uuid, IN p_requesting_department character varying, IN p_warehouse_id uuid, IN p_required_date timestamp without time zone, IN p_status character varying, IN p_remarks text, IN p_created_by uuid, IN p_details jsonb)
2
LANGUAGE plpgsql
3
AS $procedure$
4
DECLARE
5
v_request_no varchar;
6
v_detail jsonb;
7
BEGIN
8
-- Generate number
9
v_request_no := public.get_new_material_request_number(p_company_id, CURRENT_DATE);
10
11
-- Insert Header
12
INSERT INTO public.material_request_headers (
13
id,
14
organization_id,
15
company_id,
16
request_no,
17
request_type,
18
requested_by,
19
requesting_department,
20
warehouse_id,
21
required_date,
22
status,
23
remarks,
24
created_on_utc,
25
created_by,
26
is_deleted
27
)
28
VALUES (
29
p_id,
30
p_organization_id,
31
p_company_id,
32
v_request_no,
33
p_request_type,
34
p_requested_by,
35
p_requesting_department,
36
p_warehouse_id,
37
p_required_date,
38
p_status,
39
p_remarks,
40
NOW(),
41
p_created_by,
42
false
43
);
44
45
-- Insert Details
46
FOR v_detail IN
47
SELECT elem FROM jsonb_array_elements(p_details) AS elem
48
LOOP
49
INSERT INTO public.material_request_details (
50
id,
51
material_request_header_id,
52
product_id,
53
unit_id,
54
requested_qty,
55
approved_qty,
56
issued_qty,
57
remarks,
58
is_deleted
59
)
60
VALUES (
61
gen_random_uuid(),
62
p_id,
63
(v_detail->>'product_id')::uuid,
64
(v_detail->>'unit_id')::uuid,
65
(v_detail->>'requested_qty')::numeric,
66
COALESCE(NULLIF(v_detail->>'approved_qty', '')::numeric, 0),
67
COALESCE(NULLIF(v_detail->>'issued_qty', '')::numeric, 0),
68
NULLIF(TRIM(v_detail->>'remarks'), ''),
69
false
70
);
71
END LOOP;
72
73
END;
74
$procedure$
|
-- Table: customer_products
Exists in source, missing in target
-- Table: product_group
Exists in source, missing in target
-- Table: orders
Exists in source, missing in target
-- Table: roles
Exists in source, missing in target
-- Table: product_tax_category_details
Exists in source, missing in target
-- Table: order_details
Exists in source, missing in target
-- Table: schema_versions
Exists in source, missing in target
-- Table: companies
Exists in source, missing in target
-- Table: company_categories
Exists in source, missing in target
-- Table: units
Exists in source, missing in target
-- Table: organizations
Exists in source, missing in target
-- Table: product_tax_categories
Exists in source, missing in target
-- Table: product_warehouse_quantities
Exists in source, missing in target
-- Table: __EFMigrationsHistory
Exists in source, missing in target
-- Table: vendor_products
Exists in source, missing in target
-- Table: users
Exists in source, missing in target
-- Table: categories
Exists in source, missing in target
-- Table: default_apartment_products
Exists in source, missing in target
-- Table: company_products
Exists in source, missing in target
-- Table: gate_pass_logs
Exists in source, missing in target
-- Table: stock_adjustment_details
Exists in source, missing in target
-- Table: stock_ledgers
Exists in source, missing in target
-- Table: stock_adjustment_statuses
Exists in source, missing in target
-- Table: stock_movement_types
Exists in source, missing in target
-- Table: stock_adjustment_headers
Exists in source, missing in target
-- Table: stock_transfer_statuses
Exists in source, missing in target
-- Table: goods_issue_details
Exists in source, missing in target
-- Table: gate_pass_headers
Exists in source, missing in target
-- Table: stock_reservation_statuses
Exists in source, missing in target
-- Table: gate_pass_details
Exists in source, missing in target
-- Table: warehouses
Exists in source, missing in target
-- Table: goods_issue_headers
Exists in source, missing in target
-- Table: stock_transfer_headers
Exists in source, missing in target
-- Table: warehouse_locations
Exists in source, missing in target
-- Table: goods_receipt_headers
Exists in source, missing in target
-- Table: unit_conversions
Exists in source, missing in target
-- Table: product_reorder_settings
Exists in source, missing in target
-- Table: material_request_details
Exists in source, missing in target
-- Table: stock_transfer_details
Exists in source, missing in target
-- Table: stock_count_headers
Exists in source, missing in target
-- Table: products
Exists in source, missing in target
-- Table: stock_count_details
Exists in source, missing in target
-- Table: stock_adjustment_reasons
Exists in source, missing in target
-- Table: material_request_headers
Exists in source, missing in target
-- Table: inventory_balances
Exists in source, missing in target
-- Table: stock_reservations
Exists in source, missing in target
-- Table: material_request_approval_user_company
Exists in source, missing in target
-- Table: material_request_approval_issue_logs
Exists in source, missing in target
-- Table: unit_types
Exists in source, missing in target
-- Table: goods_receipt_details
Exists in source, missing in target
-- Table: stock_reservation_status_transitions
Exists in source, missing in target
-- Table: material_request_headers_ids
Exists in source, missing in target
-- Table: product_unit_conversions
Exists in source, missing in target
-- Table: material_request_approval_logs
Exists in source, missing in target
-- Table: material_request_workflows
Exists in source, missing in target
-- Table: stock_reservation_status_history
Exists in source, missing in target
-- Table: goods_receipt_numbers
Exists in source, missing in target
-- Table: material_request_statuses
Exists in source, missing in target
-- 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: 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_products
CREATE OR REPLACE FUNCTION public.get_all_products(p_company_id uuid, p_product_group_id integer)
RETURNS TABLE(product_id uuid, product_group_id integer, product_group_name text, product_name text, unit_id uuid, unit_name text, category_id uuid, category_name text, description text, hsn_code text, sac text, purchase_price numeric, selling_price numeric, opening_stock numeric, minimum_stock numeric, vendor_id uuid, vendor_name text, tax_category_id integer, tax_category_name text, maximum_stock numeric, cgst_rate numeric, sgst_rate numeric, igst_rate numeric)
LANGUAGE plpgsql
AS $function$
BEGIN
RETURN QUERY
SELECT
p.id AS product_id,
p.product_group_id,
pg.name::TEXT AS product_group_name,
p.product_name::TEXT,
p.unit_id,
COALESCE(u.unit_name, '')::TEXT AS unit_name,
p.category_id,
COALESCE(c.category_name, '')::TEXT AS category_name,
p.description::TEXT,
p.hsn_code::TEXT,
p.sac::TEXT,
p.purchase_price,
p.selling_price,
p.opening_stock,
p.minimum_stock,
p.vendor_id,
NULL::TEXT AS vendor_name, -- Replace with actual vendor join if needed
p.tax_category_id,
COALESCE(ptc.name, '')::TEXT AS tax_category_name,
p.maximum_stock,
ptcd.c_gst_rate,
ptcd.s_gst_rate,
ptcd.i_gst_rate
FROM products p
INNER JOIN company_products cp ON p.id = cp.product_id
LEFT JOIN categories c ON p.category_id = c.id
LEFT JOIN product_tax_categories ptc ON p.tax_category_id = ptc.id
LEFT JOIN product_tax_category_details ptcd ON ptc.id = ptcd.producttaxcategory_id
INNER JOIN product_group pg ON p.product_group_id = pg.id
LEFT JOIN units u ON p.unit_id = u.id
WHERE cp.company_id = p_company_id
AND p.product_group_id = p_product_group_id
AND (cp.is_deleted IS FALSE OR cp.is_deleted IS NULL)
AND (p.is_deleted IS FALSE OR p.is_deleted IS NULL);
END;
$function$
-- Function: get_goods_receipts
CREATE OR REPLACE FUNCTION public.get_goods_receipts(p_company_id uuid)
RETURNS TABLE(id uuid, organization_id uuid, company_id uuid, receipt_no character varying, receipt_type character varying, warehouse_id uuid, warehouse_name character varying, vendor_id uuid, vendor_name text, source_document_type character varying, source_document_id uuid, vendor_invoice_no character varying, receipt_date timestamp without time zone, status character varying, remarks text, posted_on_utc timestamp without time zone, total_items integer, total_received_qty numeric, total_accepted_qty numeric, total_rejected_qty numeric, total_amount numeric)
LANGUAGE plpgsql
AS $function$
BEGIN
RETURN QUERY
SELECT
grh.id,
grh.organization_id,
grh.company_id,
grh.receipt_no,
grh.receipt_type,
grh.warehouse_id,
w.name AS warehouse_name,
grh.vendor_id,
NULL::text AS vendor_name,
grh.source_document_type,
grh.source_document_id,
grh.vendor_invoice_no,
grh.receipt_date,
grh.status,
grh.remarks,
grh.posted_on_utc,
COUNT(grd.id)::int AS total_items,
COALESCE(SUM(grd.received_qty), 0) AS total_received_qty,
COALESCE(SUM(grd.accepted_qty), 0) AS total_accepted_qty,
COALESCE(SUM(grd.rejected_qty), 0) AS total_rejected_qty,
COALESCE(SUM(grd.line_amount), 0) AS total_amount
FROM public.goods_receipt_headers grh
INNER JOIN public.warehouses w
ON w.id = grh.warehouse_id
--LEFT JOIN public.vendors v
-- ON v.id = grh.vendor_id
LEFT JOIN public.goods_receipt_details grd
ON grd.goods_receipt_header_id = grh.id
WHERE grh.company_id = p_company_id
AND grh.is_deleted = false
GROUP BY
grh.id,
grh.organization_id,
grh.company_id,
grh.receipt_no,
grh.receipt_type,
grh.warehouse_id,
w.name,
grh.vendor_id,
--v.vendor_name,
grh.source_document_type,
grh.source_document_id,
grh.vendor_invoice_no,
grh.receipt_date,
grh.status,
grh.remarks,
grh.posted_on_utc
ORDER BY grh.receipt_date DESC, grh.receipt_no DESC;
END;
$function$
-- Function: get_goods_receipt_by_id
CREATE OR REPLACE FUNCTION public.get_goods_receipt_by_id(p_id uuid)
RETURNS jsonb
LANGUAGE plpgsql
AS $function$
DECLARE
v_result jsonb;
BEGIN
SELECT jsonb_build_object(
'id', grh.id,
'organizationId', grh.organization_id,
'companyId', grh.company_id,
'receiptNo', grh.receipt_no,
'receiptType', grh.receipt_type,
'warehouseId', grh.warehouse_id,
'warehouseName', w.name,
'vendorId', grh.vendor_id,
'vendorName', NULL,
'sourceDocumentType', grh.source_document_type,
'sourceDocumentId', grh.source_document_id,
'vendorInvoiceNo', grh.vendor_invoice_no,
'receiptDate', grh.receipt_date,
'status', grh.status,
'remarks', grh.remarks,
'postedOnUtc', grh.posted_on_utc,
'postedBy', grh.posted_by,
'details', COALESCE(
(
SELECT jsonb_agg(
jsonb_build_object(
'id', grd.id,
'goodsReceiptHeaderId', grd.goods_receipt_header_id,
'lineNumber', grd.line_number,
'productId', grd.product_id,
'productName', p.product_name,
'unitId', grd.unit_id,
'unitName', u.unit_name,
'orderedQty', grd.ordered_qty,
'receivedQty', grd.received_qty,
'acceptedQty', grd.accepted_qty,
'rejectedQty', grd.rejected_qty,
'unitRate', grd.unit_rate,
'lineAmount', grd.line_amount,
'warehouseLocationId', grd.warehouse_location_id,
'warehouseLocationName', wl.name,
'remarks', grd.remarks
)
ORDER BY grd.line_number
)
FROM public.goods_receipt_details grd
INNER JOIN public.products p
ON p.id = grd.product_id
INNER JOIN public.units u
ON u.id = grd.unit_id
LEFT JOIN public.warehouse_locations wl
ON wl.id = grd.warehouse_location_id
WHERE grd.goods_receipt_header_id = grh.id
),
'[]'::jsonb
)
)
INTO v_result
FROM public.goods_receipt_headers grh
INNER JOIN public.warehouses w
ON w.id = grh.warehouse_id
WHERE grh.id = p_id
AND grh.is_deleted = false;
RETURN v_result;
END;
$function$
-- Function: update_material_request_next_status_main
CREATE OR REPLACE FUNCTION public.update_material_request_next_status_main(p_company_id uuid, p_material_request_ids uuid[], p_modified_by uuid)
RETURNS TABLE(material_request_id uuid, status_name text, error_msg text)
LANGUAGE plpgsql
AS $function$
DECLARE
v_request_id uuid;
v_current_status_name varchar(100);
v_current_status_id int4;
v_next_status_id int4;
v_next_status_name varchar(100);
v_approval_level int4;
v_user_allowed boolean;
v_has_partial_issue boolean;
BEGIN
FOREACH v_request_id IN ARRAY p_material_request_ids
LOOP
v_current_status_name := NULL;
v_current_status_id := NULL;
v_next_status_id := NULL;
v_next_status_name := NULL;
v_approval_level := 0;
v_user_allowed := false;
v_has_partial_issue := false;
-- 1. Validate request exists
SELECT mrh.status
INTO v_current_status_name
FROM public.material_request_headers mrh
WHERE mrh.id = v_request_id
AND mrh.company_id = p_company_id
AND COALESCE(mrh.is_deleted, false) = false;
IF v_current_status_name IS NULL THEN
material_request_id := v_request_id;
status_name := NULL;
error_msg := 'Material request not found';
RETURN NEXT;
CONTINUE;
END IF;
-- 2. Map current status name -> status id
SELECT mrs.id
INTO v_current_status_id
FROM public.material_request_statuses mrs
WHERE lower(mrs.name) = lower(v_current_status_name)
AND COALESCE(mrs.is_deleted, false) = false
LIMIT 1;
IF v_current_status_id IS NULL THEN
material_request_id := v_request_id;
status_name := v_current_status_name;
error_msg := 'Current status is invalid';
RETURN NEXT;
CONTINUE;
END IF;
-- 3. Terminal statuses cannot move next
IF lower(v_current_status_name) IN ('rejected', 'closed', 'cancelled') THEN
material_request_id := v_request_id;
status_name := v_current_status_name;
error_msg := 'Next status update is not allowed for current status';
RETURN NEXT;
CONTINUE;
END IF;
/*
4. Special business rule:
Approved -> Closed OR Partially Issued
Based on detail quantities
*/
IF lower(v_current_status_name) = 'approved' THEN
SELECT EXISTS (
SELECT 1
FROM public.material_request_details mrd
WHERE mrd.material_request_header_id = v_request_id
AND COALESCE(mrd.is_deleted, false) = false
AND COALESCE(mrd.issued_qty, 0) < COALESCE(mrd.approved_qty, mrd.requested_qty)
)
INTO v_has_partial_issue;
IF v_has_partial_issue THEN
SELECT mrs.id, mrs.name
INTO v_next_status_id, v_next_status_name
FROM public.material_request_statuses mrs
WHERE lower(mrs.name) = lower('Partially Issued')
AND COALESCE(mrs.is_deleted, false) = false
LIMIT 1;
ELSE
SELECT mrs.id, mrs.name
INTO v_next_status_id, v_next_status_name
FROM public.material_request_statuses mrs
WHERE lower(mrs.name) = lower('Closed')
AND COALESCE(mrs.is_deleted, false) = false
LIMIT 1;
END IF;
IF v_next_status_id IS NULL THEN
material_request_id := v_request_id;
status_name := v_current_status_name;
error_msg := 'Next status master not found';
RETURN NEXT;
CONTINUE;
END IF;
UPDATE public.material_request_headers
SET status = v_next_status_name,
modified_by = p_modified_by,
modified_on_utc = now()
WHERE id = v_request_id;
INSERT INTO public.material_request_approval_logs(
material_request_id,
status_id,
approved_by,
approved_on,
comment,
created_on_utc,
created_by,
approval_level
)
VALUES (
v_request_id,
v_next_status_id,
p_modified_by,
now(),
'Moved to next status from ' || v_current_status_name || ' to ' || v_next_status_name,
now(),
p_modified_by,
0
);
INSERT INTO public.material_request_approval_issue_logs(
material_request_id,
issue,
created_on_utc,
created_by
)
VALUES (
v_request_id,
'Moved to status ' || v_next_status_name,
now(),
p_modified_by
);
material_request_id := v_request_id;
status_name := v_next_status_name;
error_msg := NULL;
RETURN NEXT;
CONTINUE;
END IF;
-- 5. Workflow-driven next status for all other non-terminal statuses
SELECT mrw.next_status, COALESCE(mrw.approval_level, 0)
INTO v_next_status_id, v_approval_level
FROM public.material_request_workflows mrw
WHERE mrw.company_id = p_company_id
AND mrw.status = v_current_status_id
AND COALESCE(mrw.is_deleted, false) = false
LIMIT 1;
IF v_next_status_id IS NULL THEN
material_request_id := v_request_id;
status_name := v_current_status_name;
error_msg := 'Next status is not configured';
RETURN NEXT;
CONTINUE;
END IF;
-- 6. Validate user authorization when approval level applies
IF COALESCE(v_approval_level, 0) > 0 THEN
SELECT EXISTS (
SELECT 1
FROM public.material_request_approval_user_company mrauc
WHERE mrauc.company_id = p_company_id
AND mrauc.status_id = v_current_status_id
AND mrauc.approval_level = v_approval_level
AND mrauc.user_id = p_modified_by
AND COALESCE(mrauc.is_deleted, false) = false
)
INTO v_user_allowed;
IF NOT COALESCE(v_user_allowed, false) THEN
material_request_id := v_request_id;
status_name := v_current_status_name;
error_msg := 'User is not authorized to move this status';
RETURN NEXT;
CONTINUE;
END IF;
END IF;
-- 7. Resolve next status name
SELECT mrs.name
INTO v_next_status_name
FROM public.material_request_statuses mrs
WHERE mrs.id = v_next_status_id
AND COALESCE(mrs.is_deleted, false) = false
LIMIT 1;
IF v_next_status_name IS NULL THEN
material_request_id := v_request_id;
status_name := v_current_status_name;
error_msg := 'Next status master not found';
RETURN NEXT;
CONTINUE;
END IF;
-- 8. Update header
UPDATE public.material_request_headers
SET status = v_next_status_name,
modified_by = p_modified_by,
modified_on_utc = now()
WHERE id = v_request_id;
-- 9. Insert approval log
INSERT INTO public.material_request_approval_logs(
material_request_id,
status_id,
approved_by,
approved_on,
comment,
created_on_utc,
created_by,
approval_level
)
VALUES (
v_request_id,
v_next_status_id,
p_modified_by,
now(),
'Moved to next status from ' || v_current_status_name || ' to ' || v_next_status_name,
now(),
p_modified_by,
v_approval_level
);
-- 10. Issue log only for issue statuses
IF lower(v_next_status_name) IN ('partially issued', 'issued', 'closed') THEN
INSERT INTO public.material_request_approval_issue_logs(
material_request_id,
issue,
created_on_utc,
created_by
)
VALUES (
v_request_id,
'Moved to status ' || v_next_status_name,
now(),
p_modified_by
);
END IF;
material_request_id := v_request_id;
status_name := v_next_status_name;
error_msg := NULL;
RETURN NEXT;
END LOOP;
RETURN;
END;
$function$
-- Function: get_products_by_company_and_group
CREATE OR REPLACE FUNCTION public.get_products_by_company_and_group(in_company_id uuid, in_product_group_id integer)
RETURNS TABLE(id uuid, product_group_id integer, product_name text, unit_id uuid, unit_name text, category_id uuid, category_name text, description text, hsn_code text, sac text, purchase_price numeric, selling_price numeric, opening_stock numeric, minimum_stock numeric, vendor_id uuid, tax_category_id uuid, maximum_stock numeric, cgst_rate numeric, sgst_rate numeric, igst_rate numeric)
LANGUAGE plpgsql
AS $function$
BEGIN
RETURN QUERY
SELECT
p.id,
p.product_group_id,
p.product_name::TEXT,
p.unit_id,
u.unit_name::TEXT,
p.category_id,
c.category_name::TEXT,
p.description::TEXT,
p.hsn_code::TEXT,
p.sac::TEXT,
p.purchase_price,
p.selling_price,
p.opening_stock,
p.minimum_stock,
p.vendor_id,
p.tax_category_id,
p.maximum_stock,
ptcd.c_gst_rate,
ptcd.s_gst_rate,
ptcd.i_gst_rate
FROM products p
INNER JOIN company_products cp ON p.id = cp.product_id
LEFT JOIN categories c ON p.category_id = c.id
LEFT JOIN product_tax_categories ptc ON p.tax_category_id = ptc.id
LEFT JOIN product_tax_category_details ptcd ON ptc.id = ptcd.producttaxcategory_id
INNER JOIN product_group pg ON p.product_group_id = pg.id
LEFT JOIN units u ON u.id = p.unit_id
WHERE cp.company_id = in_company_id
AND p.product_group_id = in_product_group_id;
END;
$function$
-- Function: get_all_stock_adjustments_from_span
CREATE OR REPLACE FUNCTION public.get_all_stock_adjustments_from_span(p_company_id uuid, p_user_id uuid, p_warehouse_id uuid DEFAULT NULL::uuid, p_start_date timestamp without time zone DEFAULT NULL::timestamp without time zone, p_end_date timestamp without time zone DEFAULT NULL::timestamp without time zone)
RETURNS TABLE(id uuid, organization_id uuid, company_id uuid, warehouse_id uuid, stock_adjustment_status text, stock_adjustment_status_id integer, reason_code text, stock_adjustment_number text, remarks text, adjustment_date date, created_by uuid, created_on_utc timestamp without time zone, modified_by uuid, modified_on_utc timestamp without time zone, created_by_name text, modified_by_name text, line_count integer, total_difference numeric)
LANGUAGE plpgsql
AS $function$
DECLARE
v_start_date date := COALESCE(p_start_date::date, '1970-01-01'::date);
v_end_date date := COALESCE(p_end_date::date, '9999-12-31'::date);
BEGIN
RETURN QUERY
WITH headers AS (
SELECT h.*
FROM public.stock_adjustment_headers h
WHERE h.company_id = p_company_id
AND h.is_deleted = false
AND (p_warehouse_id IS NULL OR p_warehouse_id = '00000000-0000-0000-0000-000000000000'::uuid OR h.warehouse_id = p_warehouse_id)
AND (h.adjustment_date_utc::date BETWEEN v_start_date AND v_end_date)
), line_agg AS (
SELECT
sd.stock_adjustment_header_id,
COUNT(*)::int AS line_count,
SUM(sd.difference_quantity)::numeric AS total_difference
FROM public.stock_adjustment_details sd
WHERE sd.is_deleted = false
GROUP BY sd.stock_adjustment_header_id
)
SELECT
h.id,
h.organization_id,
h.company_id,
h.warehouse_id,
COALESCE(s.name, '') AS stock_adjustment_status,
h.stock_adjustment_status_id,
h.reason_code,
h.stock_adjustment_number,
h.remarks,
h.adjustment_date_utc::date AS adjustment_date,
h.created_by,
h.created_on_utc,
h.modified_by,
h.modified_on_utc,
CONCAT(cu.first_name, ' ', cu.last_name)::text AS created_by_name,
CONCAT(mu.first_name, ' ', mu.last_name)::text AS modified_by_name,
COALESCE(la.line_count, 0) AS line_count,
COALESCE(la.total_difference, 0.0) AS total_difference
FROM headers h
LEFT JOIN public.stock_adjustment_statuses s ON s.id = h.stock_adjustment_status_id
LEFT JOIN public.users cu ON cu.id = h.created_by
LEFT JOIN public.users mu ON mu.id = h.modified_by
LEFT JOIN line_agg la ON la.stock_adjustment_header_id = h.id;
END;
$function$
-- Function: get_all_products_by_company_and_group
CREATE OR REPLACE FUNCTION public.get_all_products_by_company_and_group(p_company_id uuid, p_product_group_id integer)
RETURNS TABLE(id uuid, product_group_id integer, product_group_name text, product_name character varying, unit_id uuid, unit text, category_id uuid, category_name text, description text, hsn_code text, sac text, purchase_price numeric, selling_price numeric, opening_stock numeric, minimum_stock numeric, vendor_id uuid, vendor_name text, tax_category_id integer, tax_category_name text, maximum_stock numeric, cgst numeric, sgst numeric, igst numeric)
LANGUAGE sql
STABLE
AS $function$
SELECT
p.id,
p.product_group_id,
''::text AS product_group_name,
p.product_name,
p.unit_id,
COALESCE(un.unit_name, '')::text AS unit,
p.category_id,
COALESCE(cat.category_name, '')::text AS category_name,
p.description,
p.hsn_code,
p.sac,
p.purchase_price,
p.selling_price,
p.opening_stock,
p.minimum_stock,
p.vendor_id,
NULL::text AS vendor_name,
p.tax_category_id,
''::text AS tax_category_name,
p.maximum_stock,
ptcd.c_gst_rate AS cgst,
ptcd.s_gst_rate AS sgst,
ptcd.i_gst_rate AS igst
FROM public.products p
INNER JOIN public.company_products cp
ON p.id = cp.product_id
LEFT JOIN public.categories cat
ON p.category_id = cat.id
LEFT JOIN public.product_tax_categories ptc
ON p.tax_category_id = ptc.id
LEFT JOIN public.product_tax_category_details ptcd
ON ptc.id = ptcd.producttaxcategory_id
INNER JOIN public.product_group pg
ON p.product_group_id = pg.id
LEFT JOIN public.units un
ON un.id = p.unit_id
WHERE cp.company_id = p_company_id
AND p.product_group_id = p_product_group_id;
$function$
-- Function: get_all_material_requests
CREATE OR REPLACE FUNCTION public.get_all_material_requests(p_company_id uuid)
RETURNS TABLE(id uuid, organization_id uuid, company_id uuid, request_no character varying, request_type character varying, requested_by uuid, requesting_department character varying, warehouse_id uuid, warehouse_name character varying, required_date date, status character varying, remarks 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 uuid, modified_by uuid, created_by_name character varying, modified_by_name character varying)
LANGUAGE sql
STABLE
AS $function$
SELECT
mrh.id,
mrh.organization_id,
mrh.company_id,
mrh.request_no,
mrh.request_type,
mrh.requested_by,
mrh.requesting_department,
mrh.warehouse_id,
w.name AS warehouse_name,
mrh.required_date,
mrh.status,
mrh.remarks,
mrh.created_on_utc,
mrh.modified_on_utc,
mrh.deleted_on_utc,
mrh.is_deleted,
mrh.created_by,
mrh.modified_by,
CAST(TRIM(CONCAT(cu.first_name, ' ', cu.last_name)) AS varchar) AS created_by_name,
CAST(TRIM(CONCAT(mu.first_name, ' ', mu.last_name)) AS varchar) AS modified_by_name
FROM public.material_request_headers mrh
LEFT JOIN public.warehouses w
ON mrh.warehouse_id = w.id
AND w.is_deleted = false
LEFT JOIN public.users cu
ON cu.id = mrh.created_by
LEFT JOIN public.users mu
ON mu.id = mrh.modified_by
WHERE mrh.company_id = p_company_id
AND mrh.is_deleted = false
ORDER BY mrh.created_on_utc DESC;
$function$
-- Function: get_new_material_request_number
CREATE OR REPLACE FUNCTION public.get_new_material_request_number(p_company_id uuid, p_date date)
RETURNS character varying
LANGUAGE plpgsql
AS $function$
DECLARE
v_finance_year integer;
v_new_id integer;
v_prefix varchar;
v_length integer;
v_material_request_no varchar;
BEGIN
-- 1. Determine Financial Year (April–March)
IF EXTRACT(MONTH FROM p_date) >= 4 THEN
v_finance_year := EXTRACT(YEAR FROM p_date);
ELSE
v_finance_year := EXTRACT(YEAR FROM p_date) - 1;
END IF;
-- 2. Try UPDATE (Atomic Increment)
WITH updated AS (
UPDATE public.material_request_headers_ids
SET last_material_request_id = last_material_request_id + 1
WHERE company_id = p_company_id
AND fin_year = v_finance_year
RETURNING
last_material_request_id,
material_request_prefix,
material_request_length
),
inserted AS (
-- 3. If not exists → INSERT default
INSERT INTO public.material_request_headers_ids (
company_id,
fin_year,
material_request_prefix,
material_request_length,
last_material_request_id
)
SELECT
p_company_id,
v_finance_year,
'MR', -- Default Prefix
6, -- Default Length (can change later)
1
WHERE NOT EXISTS (SELECT 1 FROM updated)
RETURNING
last_material_request_id,
material_request_prefix,
material_request_length
)
-- 4. Get values
SELECT
COALESCE((SELECT last_material_request_id FROM updated LIMIT 1),
(SELECT last_material_request_id FROM inserted LIMIT 1)),
COALESCE((SELECT material_request_prefix FROM updated LIMIT 1),
(SELECT material_request_prefix FROM inserted LIMIT 1)),
COALESCE((SELECT material_request_length FROM updated LIMIT 1),
(SELECT material_request_length FROM inserted LIMIT 1))
INTO v_new_id, v_prefix, v_length;
-- 5. Generate Material Request Number
v_material_request_no := v_prefix || LPAD(v_new_id::text, v_length, '0');
RETURN v_material_request_no;
END;
$function$
-- Function: get_gate_pass_by_id
CREATE OR REPLACE FUNCTION public.get_gate_pass_by_id(p_gate_pass_id uuid)
RETURNS TABLE(id uuid, gate_pass_no character varying, gate_pass_type character varying, warehouse_id uuid, warehouse_name character varying, party_type character varying, party_id uuid, vehicle_no character varying, driver_name character varying, purpose character varying, reference_document_type character varying, reference_document_id uuid, gate_out_time timestamp without time zone, gate_in_time timestamp without time zone, status character varying, remarks 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 uuid, modified_by uuid, created_by_name character varying, modified_by_name character varying, details jsonb, logs jsonb)
LANGUAGE sql
AS $function$
SELECT
gh.id,
gh.gate_pass_no,
gh.gate_pass_type,
gh.warehouse_id,
w."name" AS warehouse_name,
gh.party_type,
gh.party_id,
gh.vehicle_no,
gh.driver_name,
gh.purpose,
gh.reference_document_type,
gh.reference_document_id,
gh.gate_out_time,
gh.gate_in_time,
gh.status,
gh.remarks,
gh.created_on_utc,
gh.modified_on_utc,
gh.deleted_on_utc,
gh.is_deleted,
gh.created_by,
gh.modified_by,
CONCAT(cu.first_name, ' ', cu.last_name) AS created_by_name,
CONCAT(mu.first_name, ' ', mu.last_name) AS modified_by_name,
-- ================= DETAILS =================
(
SELECT jsonb_agg(
jsonb_build_object(
'id', gpd.id,
'productId', gpd.product_id,
'productName', p.product_name,
'unitId', gpd.unit_id,
'unitName', u.unit_name,
'quantity', gpd.quantity,
'remarks', gpd.remarks
)
)
FROM gate_pass_details gpd
LEFT JOIN products p ON p.id = gpd.product_id
LEFT JOIN units u ON u.id = gpd.unit_id
WHERE gpd.gate_pass_header_id = gh.id
AND gpd.is_deleted = false
) AS details,
-- ================= LOGS =================
(
SELECT jsonb_agg(
jsonb_build_object(
'id', gl.id,
'log_message', gl.log_message,
'created_on_utc', gl.created_on_utc,
'created_by', gl.created_by,
'created_by_name',
CONCAT(u.first_name, ' ', u.last_name)
)
ORDER BY gl.created_on_utc DESC
)
FROM gate_pass_logs gl
LEFT JOIN users u ON u.id = gl.created_by
WHERE gl.gate_pass_header_id = gh.id
) AS logs
FROM gate_pass_headers gh
LEFT JOIN warehouses w ON w.id = gh.warehouse_id
LEFT JOIN users cu ON cu.id = gh.created_by
LEFT JOIN users mu ON mu.id = gh.modified_by
WHERE gh.id = p_gate_pass_id
AND gh.is_deleted = false;
$function$
-- Function: get_material_request_by_id
CREATE OR REPLACE FUNCTION public.get_material_request_by_id(p_material_request_id uuid)
RETURNS TABLE(id uuid, organization_id uuid, company_id uuid, request_no character varying, request_type character varying, requested_by uuid, requesting_department character varying, warehouse_id uuid, warehouse_name character varying, required_date date, status character varying, remarks text, created_on_utc timestamp without time zone, details jsonb, approval_logs jsonb)
LANGUAGE plpgsql
AS $function$
BEGIN
RETURN QUERY
SELECT
h.id,
h.organization_id,
h.company_id,
h.request_no,
h.request_type,
h.requested_by,
h.requesting_department,
h.warehouse_id,
w.name AS warehouse_name,
h.required_date,
h.status,
h.remarks,
h.created_on_utc,
-- 🔹 DETAILS JSON ARRAY
(
SELECT jsonb_agg(
jsonb_build_object(
'id', d.id,
'product_id', d.product_id,
'product_name', p.product_name,
'unit_id', d.unit_id,
'unit_name', u.unit_name,
'requested_qty', d.requested_qty,
'approved_qty', d.approved_qty,
'issued_qty', d.issued_qty,
'remarks', d.remarks
)
)
FROM public.material_request_details d
LEFT JOIN public.products p ON p.id = d.product_id
LEFT JOIN public.units u ON u.id = d.unit_id
WHERE d.material_request_header_id = h.id
AND d.is_deleted = false
) AS details,
-- 🔹 APPROVAL LOGS JSON ARRAY
(
SELECT jsonb_agg(
jsonb_build_object(
'id', l.id,
'status_id', l.status_id,
'approved_by', l.approved_by,
'approved_on', l.approved_on,
'comment', l.comment,
'approval_level', l.approval_level
)
ORDER BY l.approval_level, l.approved_on
)
FROM public.material_request_approval_logs l
WHERE l.material_request_id = h.id
AND l.is_deleted = false
) AS approval_logs
FROM public.material_request_headers h
LEFT JOIN public.warehouses w ON w.id = h.warehouse_id
WHERE h.id = p_material_request_id
AND h.is_deleted = false;
END;
$function$
-- Function: get_all_gate_passes
CREATE OR REPLACE FUNCTION public.get_all_gate_passes(p_company_id uuid, p_start_date timestamp without time zone, p_end_date timestamp without time zone)
RETURNS TABLE(id uuid, gate_pass_no character varying, gate_pass_type character varying, warehouse_id uuid, warehouse_name character varying, party_type character varying, party_id uuid, vehicle_no character varying, driver_name character varying, purpose character varying, gate_out_time timestamp without time zone, gate_in_time timestamp without time zone, status character varying, remarks 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 uuid, modified_by uuid, created_by_name character varying, modified_by_name character varying)
LANGUAGE sql
STABLE
AS $function$
SELECT
gh.id,
gh.gate_pass_no,
gh.gate_pass_type,
gh.warehouse_id,
w.name AS warehouse_name,
gh.party_type,
gh.party_id,
gh.vehicle_no,
gh.driver_name,
gh.purpose,
gh.gate_out_time,
gh.gate_in_time,
gh.status,
gh.remarks,
gh.created_on_utc,
gh.modified_on_utc,
gh.deleted_on_utc,
gh.is_deleted,
gh.created_by,
gh.modified_by,
CAST(TRIM(CONCAT(cu.first_name, ' ', cu.last_name)) AS varchar) AS created_by_name,
CAST(TRIM(CONCAT(mu.first_name, ' ', mu.last_name)) AS varchar) AS modified_by_name
FROM public.gate_pass_headers gh
LEFT JOIN public.warehouses w
ON gh.warehouse_id = w.id
AND w.is_deleted = false
LEFT JOIN public.users cu
ON cu.id = gh.created_by
LEFT JOIN public.users mu
ON mu.id = gh.modified_by
WHERE gh.company_id = p_company_id
AND gh.is_deleted = false
AND (p_start_date IS NULL OR gh.created_on_utc >= p_start_date)
AND (p_end_date IS NULL OR gh.created_on_utc <= p_end_date)
ORDER BY gh.created_on_utc DESC;
$function$
-- Function: get_new_goods_receipt_number
CREATE OR REPLACE FUNCTION public.get_new_goods_receipt_number(p_company_id uuid, p_date date)
RETURNS character varying
LANGUAGE plpgsql
AS $function$
DECLARE
v_fin_year int;
v_new_id int;
v_prefix varchar;
v_length int;
v_receipt_no varchar;
BEGIN
-- 1. Financial Year (April–March)
IF EXTRACT(MONTH FROM p_date) >= 4 THEN
v_fin_year := EXTRACT(YEAR FROM p_date);
ELSE
v_fin_year := EXTRACT(YEAR FROM p_date) - 1;
END IF;
-- 2. Atomic UPDATE
WITH updated AS (
UPDATE public.goods_receipt_numbers
SET last_goods_receipt_id = last_goods_receipt_id + 1
WHERE company_id = p_company_id
AND fin_year = v_fin_year
RETURNING
last_goods_receipt_id,
goods_receipt_prefix,
goods_receipt_length
),
inserted AS (
INSERT INTO public.goods_receipt_numbers (
company_id,
fin_year,
goods_receipt_prefix,
goods_receipt_length,
last_goods_receipt_id
)
SELECT
p_company_id,
v_fin_year,
'GR',
6,
1
WHERE NOT EXISTS (SELECT 1 FROM updated)
RETURNING
last_goods_receipt_id,
goods_receipt_prefix,
goods_receipt_length
)
SELECT
COALESCE((SELECT last_goods_receipt_id FROM updated LIMIT 1),
(SELECT last_goods_receipt_id FROM inserted LIMIT 1)),
COALESCE((SELECT goods_receipt_prefix FROM updated LIMIT 1),
(SELECT goods_receipt_prefix FROM inserted LIMIT 1)),
COALESCE((SELECT goods_receipt_length FROM updated LIMIT 1),
(SELECT goods_receipt_length FROM inserted LIMIT 1))
INTO v_new_id, v_prefix, v_length;
-- 3. Generate Receipt No
v_receipt_no := v_prefix || LPAD(v_new_id::text, v_length, '0');
RETURN v_receipt_no;
END;
$function$
-- Function: get_next_mr_status
CREATE OR REPLACE FUNCTION public.get_next_mr_status(p_company_id uuid, p_current_status integer)
RETURNS TABLE(next_status integer)
LANGUAGE plpgsql
AS $function$
BEGIN
RETURN QUERY
SELECT mw.next_status
FROM material_request_workflows mw
WHERE mw.company_id = p_company_id
AND mw.status = p_current_status
AND mw.is_deleted = false;
END;
$function$
-- Function: get_goods_issue_by_id
CREATE OR REPLACE FUNCTION public.get_goods_issue_by_id(p_id uuid)
RETURNS TABLE(id uuid, organization_id uuid, company_id uuid, issue_no character varying, issue_type character varying, warehouse_id uuid, warehouse_name character varying, requested_by uuid, requested_by_name character varying, approved_by uuid, approved_by_name character varying, issued_to_type character varying, issued_to_id uuid, issue_date timestamp without time zone, status character varying, remarks text, posted_on_utc timestamp without time zone, posted_by uuid, source_document_type character varying, source_document_id uuid, created_on_utc timestamp without time zone, modified_on_utc timestamp without time zone, is_deleted boolean, created_by uuid, modified_by uuid, detail_id uuid, detail_product_id uuid, product_name character varying, detail_unit_id uuid, unit_name character varying, detail_requested_qty numeric, detail_issued_qty numeric, detail_rate numeric, detail_amount numeric, detail_warehouse_location_id uuid, warehouse_location_name character varying, detail_remarks text)
LANGUAGE plpgsql
AS $function$
BEGIN
RETURN QUERY
SELECT
gih.id,
gih.organization_id,
gih.company_id,
gih.issue_no,
gih.issue_type,
gih.warehouse_id,
COALESCE(w.name, '')::character varying AS warehouse_name,
gih.requested_by,
COALESCE(concat_ws(' ', u_requested.first_name, u_requested.last_name), '')::character varying AS requested_by_name,
gih.approved_by,
COALESCE(concat_ws(' ', u_approved.first_name, u_approved.last_name), '')::character varying AS approved_by_name,
gih.issued_to_type,
gih.issued_to_id,
gih.issue_date,
gih.status,
gih.remarks,
gih.posted_on_utc,
gih.posted_by,
gih.source_document_type,
gih.source_document_id,
gih.created_on_utc,
gih.modified_on_utc,
gih.is_deleted,
gih.created_by,
gih.modified_by,
gid.id AS detail_id,
gid.product_id AS detail_product_id,
COALESCE(p.product_name, '')::character varying AS product_name,
gid.unit_id AS detail_unit_id,
COALESCE(u.unit_name, '')::character varying AS unit_name,
gid.requested_qty AS detail_requested_qty,
gid.issued_qty AS detail_issued_qty,
gid.rate AS detail_rate,
gid.amount AS detail_amount,
gid.warehouse_location_id AS detail_warehouse_location_id,
COALESCE(wl.name, '')::character varying AS warehouse_location_name,
gid.remarks AS detail_remarks
FROM goods_issue_headers gih
LEFT JOIN warehouses w
ON gih.warehouse_id = w.id
AND (w.is_deleted IS FALSE OR w.is_deleted IS NULL)
LEFT JOIN users u_requested
ON gih.requested_by = u_requested.id
LEFT JOIN users u_approved
ON gih.approved_by = u_approved.id
LEFT JOIN goods_issue_details gid
ON gih.id = gid.goods_issue_header_id
LEFT JOIN products p
ON gid.product_id = p.id
AND (p.is_deleted IS FALSE OR p.is_deleted IS NULL)
LEFT JOIN units u
ON gid.unit_id = u.id
LEFT JOIN warehouse_locations wl
ON gid.warehouse_location_id = wl.id
AND (wl.is_deleted IS FALSE OR wl.is_deleted IS NULL)
WHERE gih.id = p_id
AND (gih.is_deleted IS FALSE OR gih.is_deleted IS NULL);
END;
$function$
-- Function: get_goods_issues
CREATE OR REPLACE FUNCTION public.get_goods_issues(p_company_id uuid)
RETURNS TABLE(id uuid, organization_id uuid, company_id uuid, issue_no character varying, issue_type character varying, warehouse_id uuid, warehouse_name character varying, requested_by uuid, requested_by_name character varying, approved_by uuid, approved_by_name character varying, issued_to_type character varying, issued_to_id uuid, issue_date timestamp without time zone, status character varying, remarks text, posted_on_utc timestamp without time zone, posted_by uuid, source_document_type character varying, source_document_id uuid, created_on_utc timestamp without time zone, modified_on_utc timestamp without time zone, is_deleted boolean, created_by uuid, modified_by uuid)
LANGUAGE sql
AS $function$
SELECT
gih.id,
gih.organization_id,
gih.company_id,
gih.issue_no,
gih.issue_type,
gih.warehouse_id,
COALESCE(w.name, '')::character varying AS warehouse_name,
gih.requested_by,
COALESCE(concat_ws(' ', u_requested.first_name, u_requested.last_name), '')::character varying AS requested_by_name,
gih.approved_by,
COALESCE(concat_ws(' ', u_approved.first_name, u_approved.last_name), '')::character varying AS approved_by_name,
gih.issued_to_type,
gih.issued_to_id,
gih.issue_date,
gih.status,
gih.remarks,
gih.posted_on_utc,
gih.posted_by,
gih.source_document_type,
gih.source_document_id,
gih.created_on_utc,
gih.modified_on_utc,
gih.is_deleted,
gih.created_by,
gih.modified_by
FROM goods_issue_headers gih
LEFT JOIN warehouses w
ON gih.warehouse_id = w.id
AND (w.is_deleted IS FALSE OR w.is_deleted IS NULL)
LEFT JOIN users u_requested
ON gih.requested_by = u_requested.id
LEFT JOIN users u_approved
ON gih.approved_by = u_approved.id
WHERE gih.company_id = p_company_id
AND (gih.is_deleted IS FALSE OR gih.is_deleted IS NULL)
ORDER BY gih.created_on_utc DESC;
$function$
-- Function: get_inventory_ledger
CREATE OR REPLACE FUNCTION public.get_inventory_ledger(p_company_id uuid, p_product_id uuid DEFAULT NULL::uuid, p_warehouse_id uuid DEFAULT NULL::uuid, p_warehouse_location_id uuid DEFAULT NULL::uuid, p_batch_id uuid DEFAULT NULL::uuid, p_serial_number text DEFAULT NULL::text, p_stock_movement_type_id integer DEFAULT NULL::integer, p_reference_document_type text DEFAULT NULL::text, p_reference_document_id uuid DEFAULT NULL::uuid, p_from_date_utc timestamp without time zone DEFAULT NULL::timestamp without time zone, p_to_date_utc timestamp without time zone DEFAULT NULL::timestamp without time zone)
RETURNS TABLE(id uuid, movement_date_utc timestamp without time zone, product_id uuid, product_name text, product_code text, sku text, warehouse_id uuid, warehouse_name text, warehouse_location_id uuid, warehouse_location_name text, batch_id uuid, serial_number text, stock_movement_type_id integer, stock_movement_type_name text, quantity numeric, qty_in numeric, qty_out numeric, balance_qty numeric, unit_cost numeric, total_cost numeric, reference_type text, reference_id uuid, reference_document_id uuid, reference_document_type text, created_by uuid, created_by_name text, created_on_utc timestamp without time zone)
LANGUAGE sql
AS $function$
SELECT
sl.id,
sl.movement_date_utc,
sl.product_id,
p.product_name,
p.product_code,
p.sku,
sl.warehouse_id,
w.name as warehouse_name,
sl.warehouse_location_id,
wl.name AS warehouse_location_name,
sl.batch_id,
sl.serial_number,
sl.stock_movement_type_id,
smt.name AS stock_movement_type_name,
sl.quantity_base as quantity,
COALESCE(sl.qty_in_base, 0) AS qty_in,
COALESCE(sl.qty_out_base, 0) AS qty_out,
COALESCE(sl.balance_qty, 0) AS balance_qty,
sl.unit_cost,
sl.total_cost,
sl.reference_type,
sl.reference_id,
sl.reference_document_id,
sl.reference_document_type,
sl.created_by,
COALESCE(TRIM(usr.first_name || ' ' || usr.last_name), '') AS created_by_name,
sl.created_on_utc
FROM public.stock_ledgers sl
INNER JOIN public.products p ON p.id = sl.product_id
INNER JOIN public.warehouses w ON w.id = sl.warehouse_id
LEFT JOIN public.warehouse_locations wl ON wl.id = sl.warehouse_location_id
INNER JOIN public.stock_movement_types smt ON smt.id = sl.stock_movement_type_id
INNER JOIN public.users usr ON usr.id = sl.created_by
WHERE sl.is_deleted = false
AND sl.company_id = p_company_id
AND (p_product_id IS NULL OR sl.product_id = p_product_id)
AND (p_warehouse_id IS NULL OR sl.warehouse_id = p_warehouse_id)
AND (p_warehouse_location_id IS NULL OR sl.warehouse_location_id = p_warehouse_location_id)
AND (p_batch_id IS NULL OR sl.batch_id = p_batch_id)
AND (p_serial_number IS NULL OR sl.serial_number = p_serial_number)
AND (p_stock_movement_type_id IS NULL OR sl.stock_movement_type_id = p_stock_movement_type_id)
AND (p_reference_document_type IS NULL OR sl.reference_document_type = p_reference_document_type)
AND (p_reference_document_id IS NULL OR sl.reference_document_id = p_reference_document_id)
AND (p_from_date_utc IS NULL OR sl.movement_date_utc >= p_from_date_utc)
AND (p_to_date_utc IS NULL OR sl.movement_date_utc <= p_to_date_utc)
ORDER BY sl.movement_date_utc, sl.created_on_utc, sl.id;
$function$
-- Procedure: hard_delete_inventory_data
CREATE OR REPLACE PROCEDURE public.hard_delete_inventory_data(IN p_company_id uuid)
LANGUAGE plpgsql
AS $procedure$
BEGIN
-- Delete from dependent tables first (if foreign key constraints exist)
-- Orders (assuming these are transactional)
DELETE FROM orders WHERE company_id = p_company_id;
-- Inventory master data
DELETE FROM company_products WHERE company_id = p_company_id;
DELETE FROM company_categories WHERE company_id = p_company_id;
DELETE FROM product_tax_categories WHERE company_id = p_company_id;
-- Units and Users
DELETE FROM units WHERE company_id = p_company_id;
DELETE FROM users WHERE company_id = p_company_id;
RAISE NOTICE 'Inventory data for company_id % has been hard deleted.', p_company_id;
END;
$procedure$
-- Procedure: hard_delete_org_inventory
CREATE OR REPLACE PROCEDURE public.hard_delete_org_inventory(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
DELETE FROM orders WHERE company_id = v_company_id;
DELETE FROM company_products WHERE company_id = v_company_id;
DELETE FROM company_categories WHERE company_id = v_company_id;
DELETE FROM product_tax_categories WHERE company_id = v_company_id;
DELETE FROM units WHERE company_id = v_company_id;
DELETE FROM users WHERE company_id = v_company_id;
DELETE FROM companies WHERE id = v_company_id;
END LOOP;
DELETE FROM organizations WHERE id = p_organization_id;
RAISE NOTICE 'Deleted inventory data for organization_id: %', 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;
v_is_apartment boolean;
v_products_exist boolean;
v_categories_exist 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
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 'Created company % (ID: %) for organization %',
p_name, p_company_id, p_organization_id;
ELSE
RAISE NOTICE 'Company % (ID: %) already exists for organization %. Skipping initialization.',
p_name, p_company_id, p_organization_id;
END IF;
-- Check if the company is an apartment
IF p_is_apartment THEN
-- Check if products already exist for this company
SELECT EXISTS (
SELECT 1 FROM public.company_products
WHERE company_id = p_company_id
) INTO v_products_exist;
IF NOT v_products_exist THEN
-- Insert into company_products
INSERT INTO public.company_products(
id,
company_id,
product_id,
created_on_utc,
created_by
)
SELECT
gen_random_uuid(),
p_company_id,
product_id,
NOW(),
p_created_by
FROM public.default_apartment_products;
RAISE NOTICE 'Added default products for apartment company %', p_name;
ELSE
RAISE NOTICE 'Products already exist for company %. Skipping product initialization.', p_name;
END IF;
END IF;
-- Check if categories already exist for this company
SELECT EXISTS (
SELECT 1 FROM public.company_categories
WHERE company_id = p_company_id
) INTO v_categories_exist;
IF NOT v_categories_exist THEN
-- Insert into company_categories
INSERT INTO public.company_categories (
id,
company_id,
category_id,
created_on_utc,
created_by
)
SELECT
nextval('company_categories_seq'),
p_company_id,
category_id,
NOW(),
p_created_by
FROM public.company_categories cc
WHERE cc.company_id = p_default_company_id;
RAISE NOTICE 'Copied categories from default company % to new company %',
p_default_company_id, p_company_id;
ELSE
RAISE NOTICE 'Categories already exist for company %. Skipping category initialization.', p_name;
END IF;
END;
$procedure$
-- Procedure: initialize_company_products
CREATE OR REPLACE PROCEDURE public.initialize_company_products(IN old_company_id uuid, IN new_company_id uuid)
LANGUAGE plpgsql
AS $procedure$
BEGIN
-- Insert new records for the new company
INSERT INTO company_products (
id,
company_id,
product_id,
created_on_utc,
created_by
)
SELECT
gen_random_uuid(), -- Generate a new UUID for the id column
new_company_id, -- Assign the new company ID
product_id,
created_on_utc,
created_by
FROM company_products
WHERE company_id = old_company_id;
-- Optional: Log the operation
RAISE NOTICE 'Company products have been copied from company % to company %.', old_company_id, new_company_id;
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 company-specific products
DELETE FROM public.company_products
WHERE company_id = p_company_id;
-- Delete company-specific categories
DELETE FROM public.company_categories
WHERE 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 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_organization
CREATE OR REPLACE PROCEDURE public.initialize_organization(IN p_id uuid, IN p_name text, IN p_company_ids text, IN p_company_names text, IN p_user_id uuid, IN p_user_first_name character varying, IN p_user_last_name character varying, IN p_phone_number character varying, IN p_email character varying, IN p_created_by uuid, IN p_default_company_id uuid)
LANGUAGE plpgsql
AS $procedure$
DECLARE
v_company_id uuid;
v_company_name text;
v_company_ids uuid[];
v_company_names text[];
i integer;
v_organization_exists boolean;
v_user_exists boolean;
v_company_exists boolean;
v_existing_user_id uuid;
v_update_count integer;
BEGIN
SELECT EXISTS (
SELECT 1 FROM public.organizations
WHERE id = p_id
) INTO v_organization_exists;
IF v_organization_exists THEN
RAISE NOTICE 'Organization with ID % already exists. Skipping organization creation.', p_id;
ELSE
-- Insert organization if it doesn't exist
INSERT INTO public.organizations (
id, name, created_on_utc, created_by, is_deleted
) VALUES (
p_id, p_name, NOW(), p_created_by, false );
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_ids, ',');
v_company_names := string_to_array(p_company_names, ',');
-- Initialize companies with duplicate checks
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 public.initialize_company(
v_company_id,
p_id,
true, -- Indicates it's an apartment
v_company_name,
p_created_by,
p_default_company_id
);
END LOOP;
-- Assign the first company ID from the array
v_company_id := v_company_ids[1];
-- Check if user already exists (by both ID and email)
SELECT id INTO v_existing_user_id FROM public.users
WHERE id = p_user_id OR email = p_email
LIMIT 1;
v_user_exists := (v_existing_user_id IS NOT NULL);
IF NOT v_user_exists THEN
INSERT INTO public.users (
id, first_name, last_name, email,
created_on_utc, created_by, password_hash,
phone_number, company_id
) VALUES (
p_user_id, p_user_first_name, p_user_last_name, p_email,
NOW(), p_created_by, '',
p_phone_number, v_company_id
);
RAISE NOTICE 'New user created with ID: % and email: %', p_user_id, p_email;
ELSE
UPDATE public.users
SET
company_id = v_company_id,
first_name = COALESCE(p_user_first_name, first_name),
last_name = COALESCE(p_user_last_name, last_name),
phone_number = COALESCE(p_phone_number, phone_number),
email = COALESCE(p_email, email)
WHERE id = v_existing_user_id;
GET DIAGNOSTICS v_update_count = ROW_COUNT;
IF v_update_count > 0 THEN
RAISE NOTICE 'Updated existing user % (ID: %) with new company association %',
p_email, v_existing_user_id, v_company_id;
ELSE
RAISE NOTICE 'User % (ID: %) already associated with company %. No changes made.',
p_email, v_existing_user_id, v_company_id;
END IF;
END IF;
RAISE NOTICE 'Organization initialization process completed for % (ID: %)', p_name, p_id;
END;
$procedure$
-- Procedure: purge_inventory_organization_data
CREATE OR REPLACE PROCEDURE public.purge_inventory_organization_data(IN p_organization_ids uuid[] DEFAULT NULL::uuid[])
LANGUAGE plpgsql
AS $procedure$
DECLARE
-- Retention policy
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[] := '{}';
-- Orders
v_order_ids uuid[] := '{}';
v_order_detail_ids uuid[] := '{}';
-- Company-scoped master/config
v_company_product_ids uuid[] := '{}';
v_company_category_ids int[] := '{}';
v_ptc_ids int[] := '{}'; -- product_tax_categories.id
v_ptcd_ids int[] := '{}'; -- product_tax_category_details.id
v_unit_ids uuid[] := '{}';
-- Inventory quantities tied to company products
v_pwq_ids int[] := '{}'; -- product_warehouse_quantities.id
BEGIN
START TRANSACTION;
--------------------------------------------------------------------
-- 1) Resolve target organizations and companies (uniform block)
--------------------------------------------------------------------
IF p_organization_ids IS NULL OR array_length(p_organization_ids, 1) IS NULL THEN
SELECT 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 cleanup.';
COMMIT;
RETURN;
END IF;
-- Companies by organization
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 inventory purge. Orgs: %', v_orgs;
COMMIT;
RETURN;
END IF;
RAISE NOTICE 'Inventory purge targets - Organizations: %; Companies: %', v_orgs, v_companies;
--------------------------------------------------------------------
-- 2) Collect IDs (COALESCE to '{}' to avoid NULL-array issues)
--------------------------------------------------------------------
-- Orders and details (time-gated)
SELECT COALESCE(array_agg(id), '{}')
INTO v_order_ids
FROM orders
WHERE company_id = ANY(v_companies)
AND created_on_utc < v_cutoff_24h;
SELECT COALESCE(array_agg(id), '{}')
INTO v_order_detail_ids
FROM order_details
WHERE order_id = ANY(v_order_ids);
-- Company products and categories
SELECT COALESCE(array_agg(id), '{}')
INTO v_company_product_ids
FROM company_products
WHERE company_id = ANY(v_companies);
SELECT COALESCE(array_agg(id), '{}')
INTO v_company_category_ids
FROM company_categories
WHERE company_id = ANY(v_companies);
-- Product tax categories (company-scoped) and their details
SELECT COALESCE(array_agg(id), '{}')
INTO v_ptc_ids
FROM product_tax_categories
WHERE company_id = ANY(v_companies);
SELECT COALESCE(array_agg(id), '{}')
INTO v_ptcd_ids
FROM product_tax_category_details
WHERE producttaxcategory_id = ANY(v_ptc_ids);
-- Units (company-scoped)
SELECT COALESCE(array_agg(id), '{}')
INTO v_unit_ids
FROM units
WHERE company_id = ANY(v_companies);
-- Product warehouse quantities for products linked to these companies
SELECT COALESCE(array_agg(pwq.id), '{}')
INTO v_pwq_ids
FROM product_warehouse_quantities pwq
WHERE pwq.product_id IN (
SELECT DISTINCT cp.product_id
FROM company_products cp
WHERE cp.company_id = ANY(v_companies)
);
--------------------------------------------------------------------
-- 3) Purge in strict child → parent order (avoid FK violations)
--------------------------------------------------------------------
-- Orders: details → headers
DELETE FROM order_details
WHERE id = ANY(v_order_detail_ids);
DELETE FROM orders
WHERE id = ANY(v_order_ids);
-- Inventory quantities tied to purged company-product assignments
DELETE FROM product_warehouse_quantities
WHERE id = ANY(v_pwq_ids);
-- Product tax category details → categories
DELETE FROM product_tax_category_details
WHERE id = ANY(v_ptcd_ids);
DELETE FROM product_tax_categories
WHERE id = ANY(v_ptc_ids);
-- Company-scoped assignments
DELETE FROM company_products
WHERE id = ANY(v_company_product_ids);
DELETE FROM company_categories
WHERE id = ANY(v_company_category_ids);
-- Company-scoped units
DELETE FROM units
WHERE id = ANY(v_unit_ids);
RAISE NOTICE 'Inventory purge complete for companies: % (orgs: %).', v_companies, v_orgs;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
RAISE NOTICE 'purge_inventory_organization_data failed: %', SQLERRM;
-- Optionally rethrow
-- RAISE;
END;
$procedure$
-- Procedure: upsert_goods_issue
CREATE OR REPLACE PROCEDURE public.upsert_goods_issue(IN p_id uuid, IN p_organization_id uuid, IN p_company_id uuid, IN p_issue_no character varying, IN p_issue_type character varying, IN p_warehouse_id uuid, IN p_requested_by uuid, IN p_approved_by uuid, IN p_issued_to_type character varying, IN p_issued_to_id uuid, IN p_issue_date timestamp without time zone, IN p_status character varying, IN p_remarks text, IN p_posted_on_utc timestamp without time zone, IN p_posted_by uuid, IN p_source_document_type character varying, IN p_source_document_id uuid, IN p_created_by uuid, IN p_modified_by uuid, IN p_details jsonb, OUT p_operation_type character varying, OUT p_new_id uuid, OUT p_success boolean, OUT p_error_message text, OUT p_error_code character varying)
LANGUAGE plpgsql
AS $procedure$
DECLARE
v_existing_id uuid;
v_header_id uuid;
v_detail jsonb;
v_detail_id uuid;
v_is_insert boolean := false;
BEGIN
p_success := false;
p_operation_type := 'NONE';
p_error_message := NULL;
p_error_code := NULL;
p_new_id := NULL;
-- Validate required header fields
IF p_issue_no IS NULL OR trim(p_issue_no) = '' THEN
p_error_code := 'REQUIRED_FIELD_MISSING';
p_error_message := 'Issue number is required';
RETURN;
END IF;
IF p_issue_type IS NULL OR trim(p_issue_type) = '' THEN
p_error_code := 'REQUIRED_FIELD_MISSING';
p_error_message := 'Issue type is required';
RETURN;
END IF;
IF p_warehouse_id IS NULL THEN
p_error_code := 'REQUIRED_FIELD_MISSING';
p_error_message := 'Warehouse is required';
RETURN;
END IF;
IF p_issue_date IS NULL THEN
p_error_code := 'REQUIRED_FIELD_MISSING';
p_error_message := 'Issue date is required';
RETURN;
END IF;
IF p_status IS NULL OR trim(p_status) = '' THEN
p_error_code := 'REQUIRED_FIELD_MISSING';
p_error_message := 'Status is required';
RETURN;
END IF;
IF p_details IS NULL OR jsonb_typeof(p_details) <> 'array' OR jsonb_array_length(p_details) = 0 THEN
p_error_code := 'REQUIRED_FIELD_MISSING';
p_error_message := 'At least one detail line is required';
RETURN;
END IF;
-- Check if record exists
SELECT gih.id
INTO v_existing_id
FROM goods_issue_headers gih
WHERE gih.id = p_id;
-- Determine insert/update
v_is_insert := (v_existing_id IS NULL);
v_header_id := COALESCE(p_id, gen_random_uuid());
p_operation_type := CASE WHEN v_is_insert THEN 'INSERT' ELSE 'UPDATE' END;
BEGIN
-- Upsert header
INSERT INTO goods_issue_headers (
id,
organization_id,
company_id,
issue_no,
issue_type,
warehouse_id,
requested_by,
approved_by,
issued_to_type,
issued_to_id,
issue_date,
status,
remarks,
posted_on_utc,
posted_by,
source_document_type,
source_document_id,
created_on_utc,
modified_on_utc,
is_deleted,
created_by,
modified_by
)
VALUES (
v_header_id,
p_organization_id,
p_company_id,
p_issue_no,
p_issue_type,
p_warehouse_id,
p_requested_by,
p_approved_by,
p_issued_to_type,
p_issued_to_id,
p_issue_date,
p_status,
p_remarks,
p_posted_on_utc,
p_posted_by,
p_source_document_type,
p_source_document_id,
NOW() AT TIME ZONE 'UTC',
NOW() AT TIME ZONE 'UTC',
false,
p_created_by,
p_modified_by
)
ON CONFLICT (id) DO UPDATE SET
organization_id = EXCLUDED.organization_id,
company_id = EXCLUDED.company_id,
issue_no = EXCLUDED.issue_no,
issue_type = EXCLUDED.issue_type,
warehouse_id = EXCLUDED.warehouse_id,
requested_by = EXCLUDED.requested_by,
approved_by = EXCLUDED.approved_by,
issued_to_type = EXCLUDED.issued_to_type,
issued_to_id = EXCLUDED.issued_to_id,
issue_date = EXCLUDED.issue_date,
status = EXCLUDED.status,
remarks = EXCLUDED.remarks,
posted_on_utc = EXCLUDED.posted_on_utc,
posted_by = EXCLUDED.posted_by,
source_document_type = EXCLUDED.source_document_type,
source_document_id = EXCLUDED.source_document_id,
modified_on_utc = NOW() AT TIME ZONE 'UTC',
modified_by = p_modified_by;
-- Process details
FOR v_detail IN
SELECT value
FROM jsonb_array_elements(p_details)
LOOP
v_detail_id := NULLIF(v_detail->>'id', '')::uuid;
IF v_detail_id IS NULL
OR NOT EXISTS (
SELECT 1
FROM goods_issue_details gid
WHERE gid.id = v_detail_id
)
THEN
v_detail_id := COALESCE(v_detail_id, gen_random_uuid());
INSERT INTO goods_issue_details (
id,
goods_issue_header_id,
product_id,
unit_id,
requested_qty,
issued_qty,
rate,
amount,
warehouse_location_id,
remarks
)
VALUES (
v_detail_id,
v_header_id,
(v_detail->>'product_id')::uuid,
(v_detail->>'unit_id')::uuid,
(v_detail->>'requested_qty')::numeric,
(v_detail->>'issued_qty')::numeric,
(v_detail->>'rate')::numeric,
(v_detail->>'amount')::numeric,
NULLIF(v_detail->>'warehouse_location_id', '')::uuid,
v_detail->>'remarks'
);
ELSE
UPDATE goods_issue_details
SET
product_id = (v_detail->>'product_id')::uuid,
unit_id = (v_detail->>'unit_id')::uuid,
requested_qty = (v_detail->>'requested_qty')::numeric,
issued_qty = (v_detail->>'issued_qty')::numeric,
rate = (v_detail->>'rate')::numeric,
amount = (v_detail->>'amount')::numeric,
warehouse_location_id = NULLIF(v_detail->>'warehouse_location_id', '')::uuid,
remarks = v_detail->>'remarks'
WHERE id = v_detail_id
AND goods_issue_header_id = v_header_id;
END IF;
END LOOP;
-- Delete removed details only during update
IF NOT v_is_insert THEN
DELETE FROM goods_issue_details gid
WHERE gid.goods_issue_header_id = v_header_id
AND gid.id NOT IN (
SELECT NULLIF(x.value->>'id', '')::uuid
FROM jsonb_array_elements(p_details) AS x(value)
WHERE NULLIF(x.value->>'id', '') IS NOT NULL
);
END IF;
p_success := true;
p_new_id := v_header_id;
EXCEPTION
WHEN unique_violation THEN
p_error_code := 'UNIQUE_CONSTRAINT_VIOLATION';
p_error_message := 'A record with this unique identifier already exists';
p_success := false;
WHEN foreign_key_violation THEN
p_error_code := 'FOREIGN_KEY_VIOLATION';
p_error_message := 'Referenced record does not exist: ' || SQLERRM;
p_success := false;
WHEN check_violation THEN
p_error_code := 'CHECK_CONSTRAINT_VIOLATION';
p_error_message := 'Data validation failed: ' || SQLERRM;
p_success := false;
WHEN OTHERS THEN
p_error_code := 'UNKNOWN_ERROR';
p_error_message := 'An unexpected error occurred: ' || SQLERRM;
p_success := false;
END;
END;
$procedure$
-- Procedure: upsert_gate_pass
CREATE OR REPLACE PROCEDURE public.upsert_gate_pass(IN p_header_id uuid, IN p_org_id uuid, IN p_company_id uuid, IN p_gate_pass_no character varying, IN p_type character varying, IN p_warehouse_id uuid, IN p_party_type character varying, IN p_party_id uuid, IN p_vehicle_no character varying, IN p_driver_name character varying, IN p_status character varying, IN p_user_id uuid, IN p_details jsonb)
LANGUAGE plpgsql
AS $procedure$
BEGIN
-- 1. UPSERT THE HEADER
INSERT INTO public.gate_pass_headers (
id, organization_id, company_id, gate_pass_no, gate_pass_type,
warehouse_id, party_type, party_id, vehicle_no, driver_name,
status, created_on_utc, created_by
)
VALUES (
p_header_id, p_org_id, p_company_id, p_gate_pass_no, p_type,
p_warehouse_id, p_party_type, p_party_id, p_vehicle_no, p_driver_name,
p_status, now() AT TIME ZONE 'UTC', p_user_id
)
ON CONFLICT (id) DO UPDATE SET
gate_pass_type = EXCLUDED.gate_pass_type,
warehouse_id = EXCLUDED.warehouse_id,
vehicle_no = EXCLUDED.vehicle_no,
driver_name = EXCLUDED.driver_name,
status = EXCLUDED.status,
modified_on_utc = now() AT TIME ZONE 'UTC',
modified_by = p_user_id;
-- 2. HANDLE DETAILS (Soft delete existing first if updating)
UPDATE public.gate_pass_details
SET is_deleted = true, deleted_on_utc = now() AT TIME ZONE 'UTC'
WHERE gate_pass_header_id = p_header_id;
-- 3. INSERT NEW/UPDATED DETAILS FROM JSON
INSERT INTO public.gate_pass_details (
id, gate_pass_header_id, product_id, unit_id, quantity, is_deleted
)
SELECT
COALESCE((item->>'id')::uuid, gen_random_uuid()),
p_header_id,
(item->>'product_id')::uuid,
(item->>'unit_id')::uuid,
(item->>'quantity')::numeric,
false
FROM jsonb_array_elements(p_details) AS item;
-- 4. LOG THE ACTION
INSERT INTO public.gate_pass_logs (id, gate_pass_header_id, log_message, created_by)
VALUES (
gen_random_uuid(),
p_header_id,
'Gate Pass saved/updated with status: ' || p_status,
p_user_id
);
-- COMMIT;
END;
$procedure$
-- Procedure: insert_goods_receipt
CREATE OR REPLACE PROCEDURE public.insert_goods_receipt(IN p_organization_id uuid, IN p_company_id uuid, IN p_receipt_type character varying, IN p_warehouse_id uuid, IN p_vendor_id uuid, IN p_source_document_type character varying, IN p_source_document_id uuid, IN p_vendor_invoice_no character varying, IN p_receipt_date timestamp without time zone, IN p_status character varying, IN p_remarks text, IN p_created_by uuid, IN p_goods_receipt_details jsonb)
LANGUAGE plpgsql
AS $procedure$
DECLARE
v_detail jsonb;
v_goods_receipt_id uuid := gen_random_uuid();
v_receipt_no varchar;
BEGIN
-- 🔥 Generate Receipt No here
v_receipt_no := public.get_new_goods_receipt_number(
p_company_id,
p_receipt_date::date
);
-- Insert Header
INSERT INTO public.goods_receipt_headers
(
id,
organization_id,
company_id,
receipt_no,
receipt_type,
warehouse_id,
vendor_id,
source_document_type,
source_document_id,
vendor_invoice_no,
receipt_date,
status,
remarks,
created_on_utc,
is_deleted,
created_by
)
VALUES
(
v_goods_receipt_id,
p_organization_id,
p_company_id,
v_receipt_no,
p_receipt_type,
p_warehouse_id,
p_vendor_id,
p_source_document_type,
p_source_document_id,
p_vendor_invoice_no,
p_receipt_date,
p_status,
p_remarks,
now(),
false,
p_created_by
);
-- Insert Details
FOR v_detail IN
SELECT value FROM jsonb_array_elements(p_goods_receipt_details)
LOOP
INSERT INTO public.goods_receipt_details
(
id,
goods_receipt_header_id,
line_number,
product_id,
unit_id,
ordered_qty,
received_qty,
accepted_qty,
rejected_qty,
unit_rate,
line_amount,
warehouse_location_id,
remarks
)
VALUES
(
gen_random_uuid(),
v_goods_receipt_id,
(v_detail ->> 'line_number')::int,
(v_detail ->> 'product_id')::uuid,
(v_detail ->> 'unit_id')::uuid,
NULLIF(v_detail ->> 'ordered_qty', '')::numeric,
COALESCE((v_detail ->> 'received_qty')::numeric, 0),
NULLIF(v_detail ->> 'accepted_qty', '')::numeric,
NULLIF(v_detail ->> 'rejected_qty', '')::numeric,
NULLIF(v_detail ->> 'unit_rate', '')::numeric,
NULLIF(v_detail ->> 'line_amount', '')::numeric,
NULLIF(v_detail ->> 'warehouse_location_id', '')::uuid,
v_detail ->> 'remarks'
);
END LOOP;
END;
$procedure$
-- Procedure: update_material_request_previous_status
CREATE OR REPLACE PROCEDURE public.update_material_request_previous_status(IN p_company_id uuid, IN p_material_request_status_id integer, IN p_material_request_ids uuid[], IN p_modified_by uuid)
LANGUAGE plpgsql
AS $procedure$
DECLARE
v_material_request RECORD;
v_minimum_status INTEGER := 1; -- Draft is minimum allowed status
v_target_status_name varchar(100);
v_current_status_id integer;
BEGIN
-- Resolve target status name from material_request_statuses master
SELECT mrs.name
INTO v_target_status_name
FROM public.material_request_statuses mrs
WHERE mrs.id = p_material_request_status_id
AND COALESCE(mrs.is_deleted, false) = false
LIMIT 1;
-- Validate target status
IF v_target_status_name IS NULL THEN
RAISE NOTICE 'Invalid target material request status id: %', p_material_request_status_id;
RETURN;
END IF;
-- Process material requests for previous status update
FOR v_material_request IN
SELECT
mrh.id,
mrh.status
FROM public.material_request_headers mrh
WHERE mrh.id = ANY(p_material_request_ids)
AND mrh.company_id = p_company_id
AND COALESCE(mrh.is_deleted, false) = false
LOOP
-- Resolve current status id from status name
SELECT mrs.id
INTO v_current_status_id
FROM public.material_request_statuses mrs
WHERE lower(mrs.name) = lower(v_material_request.status)
AND COALESCE(mrs.is_deleted, false) = false
LIMIT 1;
-- If current status is invalid, skip
IF v_current_status_id IS NULL THEN
RAISE NOTICE 'Invalid current status for Material Request ID: %', v_material_request.id;
CONTINUE;
END IF;
-- Ensure the status does not go below minimum allowed status
IF v_current_status_id > v_minimum_status THEN
-- Update material request to the previous status
UPDATE public.material_request_headers
SET status = v_target_status_name,
modified_by = p_modified_by,
modified_on_utc = now()
WHERE id = v_material_request.id;
-- Insert approval log
INSERT INTO public.material_request_approval_logs(
material_request_id,
status_id,
approved_by,
approved_on,
comment,
created_on_utc,
created_by,
approval_level
)
VALUES (
v_material_request.id,
p_material_request_status_id,
p_modified_by,
now(),
'Material request moved to previous status',
now(),
p_modified_by,
0
);
RAISE NOTICE 'Material Request status updated to previous status for ID: %', v_material_request.id;
ELSE
RAISE NOTICE 'Cannot decrement status below the minimum allowed status for Material Request ID: %', v_material_request.id;
END IF;
END LOOP;
END;
$procedure$
-- Procedure: delete_goods_issue
CREATE OR REPLACE PROCEDURE public.delete_goods_issue(IN p_id uuid, IN p_deleted_by uuid)
LANGUAGE plpgsql
AS $procedure$
BEGIN
-- delete details (since no soft delete columns exist)
DELETE FROM public.goods_issue_details
WHERE goods_issue_header_id = p_id;
-- soft delete header
UPDATE public.goods_issue_headers
SET
is_deleted = true,
deleted_on_utc = NOW() AT TIME ZONE 'UTC',
modified_by = p_deleted_by,
modified_on_utc = NOW() AT TIME ZONE 'UTC'
WHERE id = p_id
AND (is_deleted IS FALSE OR is_deleted IS NULL);
END;
$procedure$
-- Procedure: delete_gate_pass
CREATE OR REPLACE PROCEDURE public.delete_gate_pass(IN p_header_ids uuid[], IN p_user_id uuid)
LANGUAGE plpgsql
AS $procedure$
DECLARE
v_id UUID;
BEGIN
FOREACH v_id IN ARRAY p_header_ids
LOOP
-- Check
IF NOT EXISTS (
SELECT 1
FROM gate_pass_headers
WHERE id = v_id AND is_deleted = false
) THEN
RAISE EXCEPTION 'Gate pass % not found or already deleted', v_id;
END IF;
-- Header delete
UPDATE gate_pass_headers
SET
is_deleted = true,
deleted_on_utc = NOW(),
modified_on_utc = NOW(),
modified_by = p_user_id
WHERE id = v_id;
-- Details delete
UPDATE gate_pass_details
SET
is_deleted = true,
deleted_on_utc = NOW()
WHERE gate_pass_header_id = v_id
AND is_deleted = false;
-- Log
INSERT INTO gate_pass_logs (
id,
gate_pass_header_id,
log_message,
created_by,
created_on_utc
)
VALUES (
gen_random_uuid(),
v_id,
'Gate Pass deleted',
p_user_id,
NOW()
);
END LOOP;
END;
$procedure$
-- Procedure: create_material_request
CREATE OR REPLACE PROCEDURE public.create_material_request(IN p_id uuid, IN p_organization_id uuid, IN p_company_id uuid, IN p_request_type character varying, IN p_requested_by uuid, IN p_requesting_department character varying, IN p_warehouse_id uuid, IN p_required_date timestamp without time zone, IN p_status character varying, IN p_remarks text, IN p_created_by uuid, IN p_details jsonb)
LANGUAGE plpgsql
AS $procedure$
DECLARE
v_request_no varchar;
v_detail jsonb;
BEGIN
-- Generate number
v_request_no := public.get_new_material_request_number(p_company_id, CURRENT_DATE);
-- Insert Header
INSERT INTO public.material_request_headers (
id,
organization_id,
company_id,
request_no,
request_type,
requested_by,
requesting_department,
warehouse_id,
required_date,
status,
remarks,
created_on_utc,
created_by,
is_deleted
)
VALUES (
p_id,
p_organization_id,
p_company_id,
v_request_no,
p_request_type,
p_requested_by,
p_requesting_department,
p_warehouse_id,
p_required_date,
p_status,
p_remarks,
NOW(),
p_created_by,
false
);
-- Insert Details
FOR v_detail IN
SELECT elem FROM jsonb_array_elements(p_details) AS elem
LOOP
INSERT INTO public.material_request_details (
id,
material_request_header_id,
product_id,
unit_id,
requested_qty,
approved_qty,
issued_qty,
remarks,
is_deleted
)
VALUES (
gen_random_uuid(),
p_id,
(v_detail->>'product_id')::uuid,
(v_detail->>'unit_id')::uuid,
(v_detail->>'requested_qty')::numeric,
COALESCE(NULLIF(v_detail->>'approved_qty', '')::numeric, 0),
COALESCE(NULLIF(v_detail->>'issued_qty', '')::numeric, 0),
NULLIF(TRIM(v_detail->>'remarks'), ''),
false
);
END LOOP;
END;
$procedure$