Acceptance Testing/Edit products properties script PostgreSQL

Script for 2.40/2.3x

 
 CREATE OR REPLACE FUNCTION create_edit_products()
   RETURNS void AS
 $BODY$ DECLARE
 
 v_taxcategory_id numeric(10);
 v_setattribute_id numeric(10);
 v_client_id numeric(10);
 v_categorytax_id numeric(10);
 
 Cur_Rawmaterials RECORD;
 Cur_Bom RECORD;
 Cur_Finalgoods RECORD;
 BEGIN
 
 SELECT MAX(AD_CLIENT_ID) INTO v_client_id FROM AD_CLIENT;
 
 /*
 This script is a draft
 The update will change all products, so if manual section of the testcase is
 executed different from steps, running will overwrite differences
 
 Task: Exclude "Raw Material A" from this script
 */
 SELECT C_TAXCATEGORY_ID INTO v_categorytax_id FROM C_TAXCATEGORY WHERE   AD_CLIENT_ID=v_client_id AND NAME='VAT 10%';
 
 FOR Cur_Rawmaterials IN (SELECT M_PRODUCT.M_PRODUCT_ID FROM M_PRODUCT,   M_PRODUCT_CATEGORY WHERE M_PRODUCT.M_PRODUCT_CATEGORY_ID =  M_PRODUCT_CATEGORY.M_PRODUCT_CATEGORY_ID AND M_PRODUCT_CATEGORY.value='RM') LOOP
 
 SELECT M_ATTRIBUTESET_ID INTO v_setattribute_id FROM M_ATTRIBUTESET WHERE  NAME='Serial number' AND AD_CLIENT_ID=v_client_id;
 
 UPDATE M_PRODUCT SET M_ATTRIBUTESET_ID=v_setattribute_id , ISPURCHASED='Y',  ISSOLD='N', C_TAXCATEGORY_ID=v_categorytax_id WHERE  M_PRODUCT_ID=Cur_Rawmaterials.m_product_id;
 
 END LOOP;
 
 SELECT M_ATTRIBUTESET_ID INTO v_setattribute_id FROM M_ATTRIBUTESET WHERE NAME='Lots' AND AD_CLIENT_ID=v_client_id;
 
 FOR Cur_Bom IN (SELECT M_PRODUCT.M_PRODUCT_ID FROM M_PRODUCT,  M_PRODUCT_CATEGORY WHERE M_PRODUCT.M_PRODUCT_CATEGORY_ID =  M_PRODUCT_CATEGORY.M_PRODUCT_CATEGORY_ID AND M_PRODUCT_CATEGORY.value='BOM')LOOP
 
 UPDATE M_PRODUCT SET M_ATTRIBUTESET_ID=v_setattribute_id , ISPURCHASED='N',  ISSOLD='N', ISBOM='Y', PRODUCTION='Y' WHERE M_PRODUCT_ID=Cur_Bom.m_product_id;
 
 END LOOP;
 
 
 SELECT C_TAXCATEGORY_ID INTO v_categorytax_id FROM C_TAXCATEGORY WHERE  AD_CLIENT_ID=v_client_id AND NAME='VAT 3%';
              
 FOR Cur_Finalgoods IN (SELECT M_PRODUCT.M_PRODUCT_ID FROM M_PRODUCT,   M_PRODUCT_CATEGORY WHERE M_PRODUCT.M_PRODUCT_CATEGORY_ID =  M_PRODUCT_CATEGORY.M_PRODUCT_CATEGORY_ID AND M_PRODUCT_CATEGORY.value='FG') LOOP
 
 UPDATE M_PRODUCT SET M_ATTRIBUTESET_ID=v_setattribute_id , ISPURCHASED='N',  ISSOLD='Y', ISBOM='N', PRODUCTION='Y', C_TAXCATEGORY_ID=v_categorytax_id WHERE  M_PRODUCT_ID=Cur_Finalgoods.m_product_id;
 
 END LOOP;
      
 END ; $BODY$
 LANGUAGE 'plpgsql' VOLATILE;
 
 SELECT create_edit_products();
 
 DROP FUNCTION create_edit_products();

Scripts for 2.50 or higher

 
 CREATE OR REPLACE FUNCTION create_edit_products()
   RETURNS void AS
 $BODY$ DECLARE
 
 v_taxcategory_id varchar(32);
 v_setattribute_id varchar(32);
 v_client_id varchar(32);
 v_categorytax_id varchar(32);
 v_uom_id varchar(32);
 
 
 Cur_Rawmaterials RECORD;
 Cur_Bom RECORD;
 Cur_Finalgoods RECORD;
 BEGIN
 
 SELECT AD_CLIENT_ID INTO v_client_id FROM AD_CLIENT WHERE created=(SELECT max(created) FROM ad_client);
 
 /*
 This script is a draft
 The update will change all products, so if manual section of the testcase is
 executed different from steps, running will overwrite differences
 
 Task: Exclude "Raw Material A" from this script
 */
 SELECT C_TAXCATEGORY_ID INTO v_categorytax_id FROM C_TAXCATEGORY WHERE   AD_CLIENT_ID=v_client_id AND NAME='VAT 10%';
 
SELECT C_UOM_ID INTO v_uom_id FROM C_UOM WHERE AD_CLIENT_ID = v_client_id AND NAME = 'Bag';
 
 
 
 FOR Cur_Rawmaterials IN (SELECT M_PRODUCT.M_PRODUCT_ID FROM M_PRODUCT,   M_PRODUCT_CATEGORY WHERE M_PRODUCT.M_PRODUCT_CATEGORY_ID =  M_PRODUCT_CATEGORY.M_PRODUCT_CATEGORY_ID AND M_PRODUCT_CATEGORY.value='RM') LOOP
 
 SELECT M_ATTRIBUTESET_ID INTO v_setattribute_id FROM M_ATTRIBUTESET WHERE  NAME='Serial number' AND AD_CLIENT_ID=v_client_id;
 
 UPDATE M_PRODUCT SET C_UOM_ID = v_uom_id, M_ATTRIBUTESET_ID=v_setattribute_id , ISPURCHASED='Y',  ISSOLD='N', C_TAXCATEGORY_ID=v_categorytax_id WHERE  M_PRODUCT_ID=Cur_Rawmaterials.m_product_id;
 
 END LOOP;
 
 SELECT M_ATTRIBUTESET_ID INTO v_setattribute_id FROM M_ATTRIBUTESET WHERE NAME='Lots' AND AD_CLIENT_ID=v_client_id;
 
 FOR Cur_Bom IN (SELECT M_PRODUCT.M_PRODUCT_ID FROM M_PRODUCT,  M_PRODUCT_CATEGORY WHERE M_PRODUCT.M_PRODUCT_CATEGORY_ID =  M_PRODUCT_CATEGORY.M_PRODUCT_CATEGORY_ID AND M_PRODUCT_CATEGORY.value='BOM')LOOP
 
 UPDATE M_PRODUCT SET C_UOM_ID = v_uom_id, M_ATTRIBUTESET_ID=v_setattribute_id , ISPURCHASED='N',  ISSOLD='N', ISBOM='Y', PRODUCTION='Y' WHERE M_PRODUCT_ID=Cur_Bom.m_product_id;
 
 END LOOP;
 
 
 SELECT C_TAXCATEGORY_ID INTO v_categorytax_id FROM C_TAXCATEGORY WHERE  AD_CLIENT_ID=v_client_id AND NAME='VAT 3%';
              
 FOR Cur_Finalgoods IN (SELECT M_PRODUCT.M_PRODUCT_ID FROM M_PRODUCT,   M_PRODUCT_CATEGORY WHERE M_PRODUCT.M_PRODUCT_CATEGORY_ID =  M_PRODUCT_CATEGORY.M_PRODUCT_CATEGORY_ID AND M_PRODUCT_CATEGORY.value='FG') LOOP
 
 UPDATE M_PRODUCT SET C_UOM_ID = v_uom_id, M_ATTRIBUTESET_ID=v_setattribute_id , ISPURCHASED='N',  ISSOLD='Y', ISBOM='N', PRODUCTION='Y', C_TAXCATEGORY_ID=v_categorytax_id WHERE  M_PRODUCT_ID=Cur_Finalgoods.m_product_id;
 
 END LOOP;
      
 END ; $BODY$
 LANGUAGE 'plpgsql' VOLATILE;
 
 SELECT create_edit_products();
 
 DROP FUNCTION create_edit_products();