1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
|
CREATE TABLE trans_type (
code char(2) primary key,
description varchar(1000)
);
COMMENT ON TABLE trans_type IS
$$Documents the transaction type codes used in the 'gl' table.
Please note that the codes in this table are hard-coded into other
(SQL) parts of the application. As such, this table merely serves
as documentation; do *not* modify its content other than inserting
new codes.$$;
COMMENT ON COLUMN trans_type.code IS
$$Code of the transaction type. The 72 alphanumeric codes starting
with 'x' or 'X' are reserved for custom internal extensions.
For extensions distributed for wide(r) use, please request a code
from the LedgerSMB development team.$$;
COMMENT ON COLUMN trans_type.description IS
$$This column contains the full documentation as to the origin
and purpose of the transaction type.
$$;
INSERT INTO trans_type (code, description)
VALUES ('gl', 'The transaction is a regular (manually entered) journal.'),
('op', 'The transaction is generated by an overpayment'),
('ia', 'The transaction is generated by inventory adjustment.'),
('as', 'The transaction is generated by stocking of an assembly.');
ALTER TABLE gl
ADD COLUMN trans_type_code char(2)
DEFAULT 'gl' REFERENCES trans_type (code);
COMMENT ON COLUMN gl.trans_type_code IS
$$This column indicates the source or type of the transaction.
Ultimately, every insert specifies the type of transaction being generated.
At the time of creation of the column (March 2017), we specify a default
value because this goal has not been realised yet.$$;
-- Generate the types we *should* have had...
UPDATE gl
SET trans_type_code = 'op'
WHERE EXISTS (select count(*)
from acc_trans
inner join payment_links
on acc_trans.entry_id = payment_links.entry_id
where acc_trans.trans_id = gl.id
having count(*) > 0);
UPDATE gl
SET trans_type_code = 'as'
WHERE reference like 'mfg-%';
UPDATE gl
SET trans_type_code = 'gl'
WHERE trans_type_code IS NULL;
-- or fall back to the default, when we have no better idea.
ALTER TABLE gl
ALTER COLUMN trans_type_code SET NOT NULL;
|
