Acceptance Testing/Business partner category postgreSQL script

Script 2.40/2.3x

 
 CREATE OR REPLACE FUNCTION create_cat_bpartners()
    RETURNS void AS
 $BODY$ DECLARE
 v_bp_category_id NUMERIC(10);
 v_client_id NUMERIC(10);
 
 BEGIN
 
 SELECT MAX(AD_CLIENT_ID) INTO v_client_id FROM AD_CLIENT;
 
 SELECT coalesce(MAX(C_BP_GROUP_ID),999999)+1 INTO v_bp_category_id FROM  C_BP_GROUP;
 
 INSERT INTO C_BP_GROUP (C_BP_GROUP_ID, AD_CLIENT_ID, AD_ORG_ID,ISACTIVE,  CREATED, CREATEDBY, UPDATED, UPDATEDBY, VALUE,NAME)
 VALUES ( v_bp_category_id,  v_client_id, 0, 'Y',now() ,100 ,now() ,100 ,'VEN'  ,'VENDOR');
 
 v_bp_category_id:=v_bp_category_id+1;
 
 INSERT INTO C_BP_GROUP (C_BP_GROUP_ID, AD_CLIENT_ID, AD_ORG_ID,ISACTIVE,  CREATED, CREATEDBY, UPDATED, UPDATEDBY, VALUE,NAME)
 VALUES ( v_bp_category_id,  v_client_id, 0, 'Y',now() ,100 ,now() ,100 ,'CRT'  ,'CREDITOR');
          
 
 v_bp_category_id:=v_bp_category_id+1;
 
 INSERT INTO C_BP_GROUP (C_BP_GROUP_ID, AD_CLIENT_ID, AD_ORG_ID,ISACTIVE,  CREATED, CREATEDBY,UPDATED, UPDATEDBY, VALUE,NAME)
 VALUES ( v_bp_category_id, v_client_id, 0, 'Y',now() ,100 ,now() ,100 ,'EM'  ,'EMPLOYEE');
              
 v_bp_category_id:=v_bp_category_id+1;
 
 INSERT INTO C_BP_GROUP (C_BP_GROUP_ID, AD_CLIENT_ID, AD_ORG_ID,ISACTIVE,  CREATED, CREATEDBY, UPDATED, UPDATEDBY, VALUE,NAME)
 VALUES ( v_bp_category_id, v_client_id, 0, 'Y',now() ,100 ,now() ,100 ,'SM'  ,'SALESMAN');
 
 PERFORM AD_UPDATE_SEQUENCE();
 
 END ; $BODY$
 LANGUAGE 'plpgsql' VOLATILE;
 
 SELECT create_cat_bpartners();
 
 DROP FUNCTION create_cat_bpartners();

Script for 2.50 or higher

 
 CREATE OR REPLACE FUNCTION create_cat_bpartners()
   RETURNS void AS
 $BODY$ DECLARE
 v_bp_category_id VARCHAR(32);
 v_client_id VARCHAR(32);
 
 BEGIN
 
 SELECT AD_CLIENT_ID INTO v_client_id FROM AD_CLIENT WHERE created=(SELECT max(created) FROM ad_client);
 
 SELECT get_uuid() INTO v_bp_category_id;
 
 INSERT INTO C_BP_GROUP (C_BP_GROUP_ID, AD_CLIENT_ID, AD_ORG_ID,ISACTIVE,  CREATED, CREATEDBY, UPDATED, UPDATEDBY, VALUE,NAME)
 VALUES ( v_bp_category_id,  v_client_id, 0, 'Y',now() ,100 ,now() ,100 ,'VEN'  ,'VENDOR');
 
 SELECT get_uuid() INTO v_bp_category_id;
 
 INSERT INTO C_BP_GROUP (C_BP_GROUP_ID, AD_CLIENT_ID, AD_ORG_ID,ISACTIVE,  CREATED, CREATEDBY, UPDATED, UPDATEDBY, VALUE,NAME)
 VALUES ( v_bp_category_id,  v_client_id, 0, 'Y',now() ,100 ,now() ,100 ,'CRT'  ,'CREDITOR');
          
 SELECT get_uuid() INTO v_bp_category_id;
 
 INSERT INTO C_BP_GROUP (C_BP_GROUP_ID, AD_CLIENT_ID, AD_ORG_ID,ISACTIVE,  CREATED, CREATEDBY,UPDATED, UPDATEDBY, VALUE,NAME)
 VALUES ( v_bp_category_id, v_client_id, 0, 'Y',now() ,100 ,now() ,100 ,'EM'  ,'EMPLOYEE');
              
 SELECT get_uuid() INTO v_bp_category_id;
 
 INSERT INTO C_BP_GROUP (C_BP_GROUP_ID, AD_CLIENT_ID, AD_ORG_ID,ISACTIVE,  CREATED, CREATEDBY, UPDATED, UPDATEDBY, VALUE,NAME)
 VALUES ( v_bp_category_id, v_client_id, 0, 'Y',now() ,100 ,now() ,100 ,'SM'  ,'SALESMAN');
 
 END ; $BODY$
 LANGUAGE 'plpgsql' VOLATILE;
 
 SELECT create_cat_bpartners();
 
 DROP FUNCTION create_cat_bpartners();