HowTo: Run Process from Database Function

In iDempiere is possible to run a process based on a database pl/pgsql postgres function, but the process needs to do certain things to run correctly.

1. The database function receives only one parameter with the AD_PInstance_ID, meaning the instance of the process being executed.

You can derive from AD_PInstance_ID all the information required like AD_Client_ID, CreatedBy (for the user running the process), Record_ID, etc.

You can also get the parameters from AD_PInstance_Para

2. The return of the database function is ignored, so is OK to return simply void.

3. You can inform progress or results inserting into AD_PInstance_Log

4. You MUST update AD_PInstance to report success or error and a message:

• Result=1 -> Success and the ErrorMsg is shown to the user
• Result!=1 -> Failure and the ErrorMsg is shown to the user

5. In the dictionary you just need to register the name of the database function in the field AD_Process.ProcedureName

6. Be careful about using this approach: As you are executing pure direct SQL here, there is no usage of the PO class, so you don't have any of the advantages of using the iDempiere java model, like:

• no validations (range, foreign keys, cross tenants, etc)
• no change log
• no automatic translations
• no automatic trees
• no automatic delete of children
• etc

This is an example that you can use as template:

-- DROP FUNCTION procedure_example();

CREATE OR REPLACE FUNCTION procedure_example(p_instanceid numeric)
 RETURNS void
 LANGUAGE plpgsql
AS $BODY$
DECLARE
    p_record_id int;
    l_charge_name varchar(100);
    l_cnt integer := 0;
BEGIN
    SELECT i.record_id
        INTO p_record_id
        FROM ad_pinstance i
        WHERE i.ad_pinstance_id=p_instanceid;

   SELECT Name
	       INTO l_charge_name
	   FROM C_Charge
	   WHERE C_Charge_ID = p_record_id;

    l_cnt := l_cnt + 1;
    INSERT INTO AD_PInstance_Log (AD_PInstance_ID, Log_ID, P_Msg, AD_PInstance_Log_UU)
     VALUES(p_instanceid, 0, 'To record information or progress you can insert into AD_PInstance_Log: ' || l_cnt, generate_uuid());

    /* Register exit message and success updating AD_PInstance with Result=1, use Result!=1 for errors */
    UPDATE AD_PInstance SET Result=1, ErrorMsg='Everything was OK' WHERE AD_PInstance_ID=p_instanceid;

END;
$BODY$
;