Log in / create account
Openbravo.com
Issues
Forge
View source | Discuss this page | Page history | Printable version   

ERP 2.50:Acceptance Testing/Create pricelist script

Script for 2.40/2.3x 

 
 DECLARE
 
 v_pricelist_id NUMBER(10);
 v_pricelistversion_id NUMBER(10);
 v_client_id NUMBER(10);
 v_schemadiscount_id NUMBER(10);
 
 BEGIN
 
 SELECT MAX(AD_CLIENT_ID) INTO v_client_id FROM AD_CLIENT;
 
 SELECT M_DISCOUNTSCHEMA_ID INTO v_schemadiscount_id FROM M_DISCOUNTSCHEMA WHERE AD_CLIENT_ID=v_client_id;
 
 SELECT COALESCE(MAX(M_PRICELIST_ID),999999)+1 INTO v_pricelist_id FROM M_PRICELIST; 
 
 INSERT INTO M_PRICELIST (M_PRICELIST_ID, AD_CLIENT_ID, AD_ORG_ID, ISACTIVE, CREATED, CREATEDBY, UPDATED, UPDATEDBY, NAME,ISTAXINCLUDED, ISSOPRICELIST, ISDEFAULT, C_CURRENCY_ID, ENFORCEPRICELIMIT)
 VALUES (v_pricelist_id, v_client_id, 0, 'Y', sysdate, 100, sysdate, 100, 'CUSTOMER A', 'N', 'Y', 'N', 102, 'N');
 
 SELECT COALESCE(MAX(M_PRICELIST_VERSION_ID),999999)+1 INTO v_pricelistversion_id FROM M_PRICELIST_VERSION;
 
 INSERT INTO M_PRICELIST_VERSION (M_PRICELIST_VERSION_ID, AD_CLIENT_ID, AD_ORG_ID, ISACTIVE, CREATED, CREATEDBY, UPDATED, UPDATEDBY, NAME, M_PRICELIST_ID, M_DISCOUNTSCHEMA_ID, VALIDFROM, PROCCREATE, M_PRICELIST_VERSION_GENERATE) 
 VALUES (v_pricelistversion_id, v_client_id, 0, 'Y', sysdate, 100, sysdate, 100, 'Customer A', v_pricelist_id, v_schemadiscount_id, '01/01/2000', 'N', 'N');
 
 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
  
 INSERT INTO M_PRODUCTPRICE (M_PRICELIST_VERSION_ID, M_PRODUCT_ID, AD_CLIENT_ID, AD_ORG_ID, ISACTIVE, CREATED, CREATEDBY, UPDATED, UPDATEDBY, PRICELIST, PRICESTD, PRICELIMIT) 
 VALUES ( v_pricelistversion_id, Cur_Finalgoods.M_PRODUCT_ID,v_client_id ,0 ,'Y' ,sysdate , 100,sysdate , 100, 2.00 ,2.00 ,2.00);
 
 END LOOP;
 
 v_pricelist_id:=v_pricelist_id+1;
 
 INSERT INTO M_PRICELIST (M_PRICELIST_ID, AD_CLIENT_ID, AD_ORG_ID, ISACTIVE, CREATED, CREATEDBY, UPDATED, UPDATEDBY,NAME, ISTAXINCLUDED, ISSOPRICELIST, ISDEFAULT, C_CURRENCY_ID, ENFORCEPRICELIMIT)
 VALUES (v_pricelist_id, v_client_id, 0, 'Y', sysdate, 100, sysdate, 100, 'CUSTOMER B', 'N', 'Y', 'N', 102, 'N');
 
 v_pricelistversion_id:=v_pricelistversion_id+1;
 
 INSERT INTO M_PRICELIST_VERSION (M_PRICELIST_VERSION_ID, AD_CLIENT_ID, AD_ORG_ID, ISACTIVE, CREATED, CREATEDBY, UPDATED, UPDATEDBY, NAME, M_PRICELIST_ID, M_DISCOUNTSCHEMA_ID, VALIDFROM, PROCCREATE, M_PRICELIST_VERSION_GENERATE)
 VALUES (v_pricelistversion_id, v_client_id, 0, 'Y', sysdate, 100, sysdate, 100, 'Customer B', v_pricelist_id, v_schemadiscount_id, '01/01/2000', 'N', 'N');
 
 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
 
 INSERT INTO M_PRODUCTPRICE (M_PRICELIST_VERSION_ID, M_PRODUCT_ID, AD_CLIENT_ID, AD_ORG_ID, ISACTIVE, CREATED, CREATEDBY, UPDATED, UPDATEDBY, PRICELIST, PRICESTD, PRICELIMIT) 
 VALUES ( v_pricelistversion_id, Cur_Finalgoods.M_PRODUCT_ID,v_client_id ,0 ,'Y' ,sysdate , 100,sysdate , 100, 2.00 ,2.00 ,2.00);
 
 END LOOP;
 			 
 v_pricelist_id:=v_pricelist_id+1;			 
 
 INSERT INTO M_PRICELIST (M_PRICELIST_ID, AD_CLIENT_ID, AD_ORG_ID, ISACTIVE, CREATED, CREATEDBY, UPDATED, UPDATEDBY, NAME, ISTAXINCLUDED, ISSOPRICELIST, ISDEFAULT, C_CURRENCY_ID, ENFORCEPRICELIMIT)
 VALUES
 (v_pricelist_id, v_client_id, 0, 'Y', sysdate, 100, sysdate, 100, 'PURCHASE', 'N', 'N', 'N', 102, 'N');
 
 v_pricelistversion_id:=v_pricelistversion_id+1;
 
 INSERT INTO M_PRICELIST_VERSION (M_PRICELIST_VERSION_ID, AD_CLIENT_ID, AD_ORG_ID, ISACTIVE, CREATED, CREATEDBY, UPDATED, UPDATEDBY, NAME, M_PRICELIST_ID, M_DISCOUNTSCHEMA_ID, VALIDFROM, PROCCREATE, M_PRICELIST_VERSION_GENERATE)
 VALUES (v_pricelistversion_id, v_client_id, 0, 'Y', sysdate, 100, sysdate, 100, 'Purchase', v_pricelist_id, v_schemadiscount_id,'01/01/2000', 'N', 'N');
 
 FOR Cur_Rawmaterial 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
 
 INSERT INTO M_PRODUCTPRICE (M_PRICELIST_VERSION_ID, M_PRODUCT_ID, AD_CLIENT_ID, AD_ORG_ID, ISACTIVE, CREATED, CREATEDBY, UPDATED, UPDATEDBY, PRICELIST, PRICESTD, PRICELIMIT) 
 VALUES ( v_pricelistversion_id, Cur_Rawmaterial.M_PRODUCT_ID,v_client_id ,0 ,'Y' ,sysdate , 100,sysdate , 100, 2.00 ,2.00 ,2.00);
 
 END LOOP;
 /*	 
 v_pricelist_id:=v_pricelist_id+1;
 
 INSERT INTO M_PRICELIST (M_PRICELIST_ID, AD_CLIENT_ID, AD_ORG_ID, ISACTIVE, CREATED, CREATEDBY, UPDATED, UPDATEDBY, NAME, ISTAXINCLUDED, ISSOPRICELIST, ISDEFAULT, C_CURRENCY_ID, ENFORCEPRICELIMIT)
 VALUES (v_pricelist_id, v_client_id, 0, 'Y', sysdate, 100, sysdate, 100, 'SALES', 'N', 'Y', 'N', 102, 'N');
 
 
 v_pricelistversion_id:=v_pricelistversion_id+1;
 
 INSERT INTO M_PRICELIST_VERSION (M_PRICELIST_VERSION_ID, AD_CLIENT_ID, AD_ORG_ID, ISACTIVE, CREATED, CREATEDBY, UPDATED, UPDATEDBY, NAME, M_PRICELIST_ID, M_DISCOUNTSCHEMA_ID, VALIDFROM, PROCCREATE, M_PRICELIST_VERSION_GENERATE)
 VALUES (v_pricelistversion_id, v_client_id, 0, 'Y', sysdate, 100, sysdate, 100, 'Sales', v_pricelist_id, v_schemadiscount_id, '01/01/2000', 'N', 'N');
 
 FOR Cur_Sales 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
 
 INSERT INTO M_PRODUCTPRICE (M_PRICELIST_VERSION_ID, M_PRODUCT_ID, AD_CLIENT_ID, AD_ORG_ID, ISACTIVE, CREATED, CREATEDBY, UPDATED, UPDATEDBY, PRICELIST, PRICESTD, PRICELIMIT) 
 VALUES ( v_pricelistversion_id, Cur_Sales.M_PRODUCT_ID,v_client_id ,0 ,'Y' ,sysdate , 100,sysdate , 100, 2.00 ,2.00 ,2.00);
 
 END LOOP;
 */
 
 AD_UPDATE_SEQUENCE();
 
 commit;
 
 END;

Script for 2.50 

 
 DECLARE
  
 v_pricelist_id varchar2(32);
 v_pricelistversion_id varchar2(32);
 v_client_id varchar2(32);
 v_schemadiscount_id varchar2(32);
 v_productprice_id varchar2(32);
 
 BEGIN
 
 SELECT AD_CLIENT_ID INTO v_client_id FROM AD_CLIENT WHERE CREATED = (SELECT MAX(CREATED) FROM AD_CLIENT);
 
 SELECT M_DISCOUNTSCHEMA_ID INTO v_schemadiscount_id FROM M_DISCOUNTSCHEMA WHERE AD_CLIENT_ID=v_client_id;
 
 AD_SEQUENCE_NEXT('M_Pricelist', v_client_id, v_pricelist_id);
 
 INSERT INTO M_PRICELIST (M_PRICELIST_ID, AD_CLIENT_ID, AD_ORG_ID, ISACTIVE,  CREATED, CREATEDBY, UPDATED, UPDATEDBY, NAME,ISTAXINCLUDED, ISSOPRICELIST,  ISDEFAULT, C_CURRENCY_ID, ENFORCEPRICELIMIT)
 VALUES (v_pricelist_id, v_client_id, 0, 'Y', sysdate, 100, sysdate, 100,  'CUSTOMER A', 'N', 'Y', 'N', 102, 'N');
 
 AD_SEQUENCE_NEXT('M_Pricelist_Version', v_client_id, v_pricelistversion_id);
 
 INSERT INTO M_PRICELIST_VERSION (M_PRICELIST_VERSION_ID, AD_CLIENT_ID,  AD_ORG_ID, ISACTIVE, CREATED, CREATEDBY, UPDATED, UPDATEDBY, NAME, M_PRICELIST_ID, M_DISCOUNTSCHEMA_ID, VALIDFROM, PROCCREATE, M_PRICELIST_VERSION_GENERATE) 
 VALUES (v_pricelistversion_id, v_client_id, 0, 'Y', sysdate, 100, sysdate, 100, 'Customer A', v_pricelist_id, v_schemadiscount_id, to_date('01/01/2000','dd/mm/yyyy'), 'N', 'N');
 
 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
 
 AD_SEQUENCE_NEXT('M_Productprice', v_client_id, v_productprice_id);
  
 INSERT INTO M_PRODUCTPRICE (m_productprice_id, M_PRICELIST_VERSION_ID, M_PRODUCT_ID, AD_CLIENT_ID, AD_ORG_ID, ISACTIVE, CREATED, CREATEDBY, UPDATED, UPDATEDBY, PRICELIST, PRICESTD, PRICELIMIT) 
 VALUES ( v_productprice_id, v_pricelistversion_id, Cur_Finalgoods.M_PRODUCT_ID,v_client_id ,0 ,'Y' ,sysdate , 100,sysdate , 100, 2.00 ,2.00 ,2.00);
 
 END LOOP;
 
 AD_SEQUENCE_NEXT('M_Pricelist', v_client_id, v_pricelist_id);
 
 INSERT INTO M_PRICELIST (M_PRICELIST_ID, AD_CLIENT_ID, AD_ORG_ID, ISACTIVE, CREATED, CREATEDBY, UPDATED, UPDATEDBY,NAME, ISTAXINCLUDED, ISSOPRICELIST, ISDEFAULT, C_CURRENCY_ID, ENFORCEPRICELIMIT)
 VALUES (v_pricelist_id, v_client_id, 0, 'Y', sysdate, 100, sysdate, 100, 'CUSTOMER B', 'N', 'Y', 'N', 102, 'N');
 
 AD_SEQUENCE_NEXT('M_Pricelist_Version', v_client_id, v_pricelistversion_id);
 
 INSERT INTO M_PRICELIST_VERSION (M_PRICELIST_VERSION_ID, AD_CLIENT_ID, AD_ORG_ID, ISACTIVE, CREATED, CREATEDBY, UPDATED, UPDATEDBY, NAME, M_PRICELIST_ID, M_DISCOUNTSCHEMA_ID, VALIDFROM, PROCCREATE, M_PRICELIST_VERSION_GENERATE)
 VALUES (v_pricelistversion_id, v_client_id, 0, 'Y', sysdate, 100, sysdate, 100, 'Customer B', v_pricelist_id, v_schemadiscount_id, to_date('01/01/2000','dd/mm/yyyy'), 'N', 'N');
 
 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
 
 AD_SEQUENCE_NEXT('M_Productprice', v_client_id, v_productprice_id);
 
 INSERT INTO M_PRODUCTPRICE (m_productprice_id, M_PRICELIST_VERSION_ID,  M_PRODUCT_ID, AD_CLIENT_ID, AD_ORG_ID, ISACTIVE, CREATED, CREATEDBY, UPDATED, UPDATEDBY, PRICELIST, PRICESTD, PRICELIMIT) 
 VALUES ( v_productprice_id, v_pricelistversion_id, Cur_Finalgoods.M_PRODUCT_ID,v_client_id ,0 ,'Y' ,sysdate , 100,sysdate , 100, 2.00 ,2.00 ,2.00);
  
 END LOOP;
               
 AD_SEQUENCE_NEXT('M_Pricelist', v_client_id, v_pricelist_id);             
 
 INSERT INTO M_PRICELIST (M_PRICELIST_ID, AD_CLIENT_ID, AD_ORG_ID, ISACTIVE,  CREATED, CREATEDBY, UPDATED, UPDATEDBY, NAME, ISTAXINCLUDED, ISSOPRICELIST, ISDEFAULT, C_CURRENCY_ID, ENFORCEPRICELIMIT)
 VALUES (v_pricelist_id, v_client_id, 0, 'Y', sysdate, 100, sysdate, 100, 'PURCHASE', 'N', 'N', 'N', 102, 'N');
  
 AD_SEQUENCE_NEXT('M_Pricelist_Version', v_client_id, v_pricelistversion_id);
  
 INSERT INTO M_PRICELIST_VERSION (M_PRICELIST_VERSION_ID, AD_CLIENT_ID, AD_ORG_ID, ISACTIVE, CREATED, CREATEDBY, UPDATED, UPDATEDBY, NAME, M_PRICELIST_ID, M_DISCOUNTSCHEMA_ID, VALIDFROM, PROCCREATE, M_PRICELIST_VERSION_GENERATE)
 VALUES (v_pricelistversion_id, v_client_id, 0, 'Y', sysdate, 100, sysdate, 100, 'Purchase', v_pricelist_id, v_schemadiscount_id,to_date('01/01/2000','dd/mm/yyyy'), 'N', 'N');
 
 FOR Cur_Rawmaterial 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
 
 AD_SEQUENCE_NEXT('M_Productprice', v_client_id, v_productprice_id);
 
 INSERT INTO M_PRODUCTPRICE (m_productprice_id, M_PRICELIST_VERSION_ID, M_PRODUCT_ID, AD_CLIENT_ID, AD_ORG_ID, ISACTIVE, CREATED, CREATEDBY, UPDATED, UPDATEDBY, PRICELIST, PRICESTD, PRICELIMIT) 
 VALUES (v_productprice_id, v_pricelistversion_id, Cur_Rawmaterial.M_PRODUCT_ID,v_client_id ,0 ,'Y' ,sysdate , 100,sysdate , 100, 2.00 ,2.00 ,2.00);
  
 END LOOP;
       
 /*
 AD_SEQUENCE_NEXT('M_Pricelist', v_client_id, v_pricelist_id);
 
 INSERT INTO M_PRICELIST (M_PRICELIST_ID, AD_CLIENT_ID, AD_ORG_ID, ISACTIVE, CREATED, CREATEDBY, UPDATED, UPDATEDBY, NAME, ISTAXINCLUDED, ISSOPRICELIST, ISDEFAULT, C_CURRENCY_ID, ENFORCEPRICELIMIT)
 VALUES (v_pricelist_id, v_client_id, 0, 'Y', sysdate, 100, sysdate, 100, 'SALES', 'N', 'Y', 'N', 102, 'N');
 
 
 AD_SEQUENCE_NEXT('M_Pricelist_Version', v_client_id, v_pricelistversion_id);
 
 INSERT INTO M_PRICELIST_VERSION (M_PRICELIST_VERSION_ID, AD_CLIENT_ID, AD_ORG_ID, ISACTIVE, CREATED, CREATEDBY, UPDATED, UPDATEDBY, NAME, M_PRICELIST_ID, M_DISCOUNTSCHEMA_ID, VALIDFROM, PROCCREATE, M_PRICELIST_VERSION_GENERATE)
 VALUES (v_pricelistversion_id, v_client_id, 0, 'Y', sysdate, 100, sysdate, 100, 'Sales', v_pricelist_id, v_schemadiscount_id, to_date('01/01/2000','dd/mm/yyyy'), 'N', 'N');
 
 FOR Cur_Sales 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
 
 AD_SEQUENCE_NEXT('M_Productprice', v_client_id, v_productprice_id);
 
 INSERT INTO M_PRODUCTPRICE (m_productprice_id, M_PRICELIST_VERSION_ID, M_PRODUCT_ID, AD_CLIENT_ID, AD_ORG_ID, ISACTIVE, CREATED, CREATEDBY, UPDATED, UPDATEDBY, PRICELIST, PRICESTD, PRICELIMIT) 
 VALUES ( v_productprice_id, v_pricelistversion_id, Cur_Sales.M_PRODUCT_ID,v_client_id ,0 ,'Y' ,sysdate , 100,sysdate , 100, 2.00 ,2.00 ,2.00);
 
 END LOOP;
 */
 
 
 commit;
 
 END;

Retrieved from "http://wiki.openbravo.com/wiki/ERP_2.50:Acceptance_Testing/Create_pricelist_script"

This page has been accessed 7,229 times. This page was last modified on 3 April 2012, at 10:59. Content is available under Creative Commons Attribution-ShareAlike 2.5 Spain License.