Acceptance Testing/Products Categories script postgreSQL

Script for 2.40/2.3x

 
 CREATE OR REPLACE FUNCTION create_prod_categories()
   RETURNS void AS
 $BODY$ DECLARE
 v_client_id NUMERIC(10);
 v_product_category_id NUMERIC(10);
 
 BEGIN
 
 SELECT MAX(AD_CLIENT_ID) INTO v_client_id FROM AD_CLIENT;
 
 SELECT MAX(M_PRODUCT_CATEGORY_ID)+1 INTO v_product_category_id FROM    M_PRODUCT_CATEGORY;
 
 INSERT INTO M_PRODUCT_CATEGORY (M_PRODUCT_CATEGORY_ID, AD_CLIENT_ID, AD_ORG_ID,
   ISACTIVE, CREATED, CREATEDBY, UPDATED, UPDATEDBY, VALUE, NAME, PLANNEDMARGIN)
 VALUES (v_product_category_id, v_client_id, 0 , 'Y', now() , 100, now(), 100,  'BOM', 'BOMs', 0);
 
 v_product_category_id:=v_product_category_id + 1;
 
 INSERT INTO M_PRODUCT_CATEGORY (M_PRODUCT_CATEGORY_ID, AD_CLIENT_ID, AD_ORG_ID,
   ISACTIVE, CREATED, CREATEDBY, UPDATED, UPDATEDBY, VALUE, NAME, PLANNEDMARGIN)
 VALUES (v_product_category_id, v_client_id, 0 , 'Y', now() , 100, now(), 100,  'FG', 'Final goods',0);
 
 PERFORM AD_UPDATE_SEQUENCE();
 
 END ; $BODY$
 LANGUAGE 'plpgsql' VOLATILE;
 
 SELECT create_prod_categories();
 
 DROP FUNCTION create_prod_categories();

Script for 2.50

 
 CREATE OR REPLACE FUNCTION create_prod_categories()
  RETURNS void AS
 $BODY$ DECLARE
 v_client_id VARCHAR(32);
 v_product_category_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_product_category_id;
 
 INSERT INTO M_PRODUCT_CATEGORY (M_PRODUCT_CATEGORY_ID, AD_CLIENT_ID, AD_ORG_ID,
  ISACTIVE, CREATED, CREATEDBY, UPDATED, UPDATEDBY, VALUE, NAME, PLANNEDMARGIN)
 VALUES (v_product_category_id, v_client_id, 0 , 'Y', now() , 100, now(), 100,  'BOM', 'BOMs', 0);
 
   SELECT get_uuid() INTO v_product_category_id;
 
 INSERT INTO M_PRODUCT_CATEGORY (M_PRODUCT_CATEGORY_ID, AD_CLIENT_ID, AD_ORG_ID,
  ISACTIVE, CREATED, CREATEDBY, UPDATED, UPDATEDBY, VALUE, NAME, PLANNEDMARGIN)
 VALUES (v_product_category_id, v_client_id, 0 , 'Y', now() , 100, now(), 100,  'FG', 'Final goods',0);
 
 PERFORM AD_UPDATE_SEQUENCE();
 
 END ; $BODY$
 LANGUAGE 'plpgsql' VOLATILE;
 
 SELECT create_prod_categories();
 
 DROP FUNCTION create_prod_categories();