ERP 2.50: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();