ERP 2.50:Acceptance Testing/Edit products properties script
Script for 2.40/2.3x
DECLARE
v_taxcategory_id number(10);
v_setattribute_id number(10);
v_client_id number(10);
v_categorytax_id number(10);
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;
commit;
end;
Script for 2.50
DECLARE
v_taxcategory_id varchar2(32);
v_setattribute_id varchar2(32);
v_client_id varchar2(32);
v_categorytax_id varchar2(32);
v_uom_id varchar2(32);
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;
commit;
end;