PostERP 2024.10.11 Manual
Fast BackwardPrevChapter 17. System DevelopmentNextFast Forward

17.11. Heterogenous Settings

After an user runs Business Logic Processor, PostERP refers to table t127 and matches the value returned by the PostgreSQL function to update the values of fields on the CRUD screen.

Several system behaviors can not be easily set up with a few number of relational tables. In order to overcome this barrier, PostERP uses JSON data structure to achieve this purpose. The DDL for table t127 is as follows:

CREATE TABLE t127 (
CONSTRAINT f1 FOREIGN KEY (f1) REFERENCES t13 (f1) ON UPDATE CASCADE ON DELETE CASCADE,
PRIMARY KEY (f1,f2,f3,f4),
f1	SMALLINT,	--screen#
f2	SMALLINT CHECK(f2 > 0),	--type code
f3	VARCHAR(10),	--target table name
f4	VARCHAR(4),		--target column name
f5	BOOLEAN DEFAULT FALSE,	--enabled?
f6	VARCHAR(2048),	--content
f99	VARCHAR(12)
);

Column f6 is for JSON string.

17.11.1. Heterogenous Setting 1

Configure column f2 with value 2 to dictate the PostERP client system to refresh the journal# on CRUD screen with the value the server returns.

This section uses [Sales] CRUD screen in Manufacturing Edition or Distribution Edition as an example for explanation.

COPY t127 FROM stdin;
6091	1	t_220	f1	t	["t_220","t_221","t_222","t_223"]	tw
\.

The screen with ID 6091 is sales delivery. Column f1, Sales#, in table t_220 is the primary key with data type INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY.

PostgreSQL function post_delivery() which posts sales records to generate accounting journals is also attached to this screen.

We want the look up field to the left of t_220.f1 to display the corresponding Journal# that is posted to accounting journal.

We want to refresh the look up field Journal# linked to field t_220.f1 on the CRUD screen immediately after an user has run PostgreSQL function post_delivery() which returns the Journal#.

Look up field of t_220.f1 is configured as this:

COPY t63 FROM stdin;
t_220	f1	t52	f5	f1	184	f4=6091 AND EXISTS (SELECT 1 FROM t53 WHERE f1=t52.f1)	f	\N	\N	tw
\.

17.11.1.1. Refreshing Accounting Journal#

Now an user has entered one record in table t_220 with 24 as Sales#. The records associated with Sales# 24 are also entered in tables t_221, t_222, t_223 etc.

This user then executes PostgreSQL function post_delivery() to post (generate) one Journal# whose value is assumed to be 120 in this case.

After Journal# 120 has been generated in database, it must be populated to client program to display look up control to the right of field t_220.f1 on CRUD screen.

post_delivery() must return JSON value like this:

CREATE OR REPLACE FUNCTION post_delivery(company TEXT,TxId INT8,ro TEXT) RETURNS VARCHAR AS $$
DECLARE
	jid INT8;
	...
BEGIN
	...
	RETURN '{"msg":"@185:' || jid || '","hs":[{"type":1,"jid":' || jid || '}]}';
END $$ LANGUAGE PLPGSQL VOLATILE;

post_delivery() returns {"msg":"@185:120","hs":[{"type":1,"journal":120}]}. PostERP formats @185:120 to Journal#:120 and displays it in the Business Logic Processor client pop-up dialog. The client program parses JSON object "hs":[{"type":1,"journal":120}] and display 120 in look up control linked to field t_220.f1 on this CRUD screen.

17.11.1.2. Cleancing Accounting Journal#

The detail records associated with the journal# 120 posted from sales delivery must be deleted when any one of the following actions occur. (To reuse Journal# 120, we do not delete the record in the topmost table t52.):

  • update and delete on table t_220

  • insert, update, and delete on tables t_221, t_222, t_223

We therefore must implement PostgreSQL triggers or rules to delete the corresponding Journal# after any sales delivery row has changed like this:

CREATE OR REPLACE FUNCTION tf_220u() RETURNS TRIGGER AS $$
BEGIN
	...
	DELETE FROM t52 WHERE f0=OLD.f0 AND f4=6091 AND f5=OLD.f1;
	RETURN NEW;
END $$ LANGUAGE PLPGSQL VOLATILE;

CREATE TRIGGER t_220u BEFORE UPDATE ON t_220 FOR EACH ROW EXECUTE PROCEDURE tf_220u();

PostgreSQL triggers or rules are designed to delete the posted journal# from database after the changes of rows in tables t_220,t_221,t_222,t_223. Mechanism must also be implemented on client side to reset look up control linked to the field t_220.f1 on CRUD screen. Configuration ["t_220","t_221","t_222","t_223"] tells client program to do this job.

17.11.2. Heterogenous Setting 2

Configure column f2 with value 2 to dictate the PostERP client system to use the value the server returns and to overwrite the specified table field on a CRUD screen.

This section reuses the example [Sales] CRUD screen in Manufacturing Edition or Distribution Edition (Section 17.11.1) and assumes PostgreSQL post_delivery() function issues an Invoice# and returns it. The PostERP client then overwrite the control on the CRUD screen that displays t_226.f2.

COPY t127 FROM stdin;
6091	1	t_220	f1	t	["t_220","t_221","t_222","t_223"]	tw
6091	2	t_226	f2	t	\N	tw
\.

post_delivery() must return JSON value like this:

	RETURN '{"msg":"@185:' || jid || '","hs":[{"type":1,"jid":' || jid || '},{"type":2,"value":"' || InvoicePrefix || invoice || '"}]}';

post_delivery() returns {"msg":"@185:120","hs":[{"type":1,"jid":120},{"type":2,"value":"TY58329700"}]}. The PostERP client uses JSON value TY58329700 to fill the control that displays t_226.f2.

PrevHomeNext
Data Quick ViewUpApplication Programing Interface (API)