ERP 2.50:Acceptance Testing/Create pricelist script postgreSQL
2.40/2.3x
CREATE OR REPLACE FUNCTION create_pricelist()
RETURNS void AS
$BODY$ DECLARE
v_pricelist_id NUMERIC(10);
v_pricelistversion_id NUMERIC(10);
v_client_id NUMERIC(10);
v_schemadiscount_id NUMERIC(10);
Cur_Finalgoods RECORD;
Cur_Rawmaterial RECORD;
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', now(), 100, now(), 1000033, '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', now(), 100, now(), 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' ,now() , 100,now() , 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', now(), 100, now(), 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', now(), 100, now(), 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' ,now() , 100,now() , 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', now(), 100, now(), 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', now(), 100, now(), 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' ,now() , 100,now() , 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', now(), 100, now(), 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', now(), 100, now(), 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' ,now() , 100,now() , 100, 2.00 ,2.00 ,2.00);
END LOOP;
*/
PERFORM AD_UPDATE_SEQUENCE();
END ; $BODY$
LANGUAGE 'plpgsql' VOLATILE;
SELECT create_pricelist();
DROP FUNCTION create_pricelist();
2.50 or higher
CREATE OR REPLACE FUNCTION create_pricelist()
RETURNS void AS
$BODY$ DECLARE
v_pricelist_id varchar(32);
v_pricelistversion_id varchar(32);
v_client_id varchar(32);
v_schemadiscount_id varchar(32);
v_productprice_id varchar(32);
Cur_Finalgoods RECORD;
Cur_Rawmaterial RECORD;
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;
SELECT get_uuid() INTO 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', now(), 100, now(), 1000033, 'CUSTOMER A', 'N', 'Y', 'N', 102, 'N');
SELECT get_uuid() INTO 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', now(), 100, now(), 100, 'Customer A', v_pricelist_id, v_schemadiscount_id, to_date('01/01/2000','dd/mm/2008'), '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
SELECT get_uuid() INTO 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' ,now() , 100,now() , 100, 2.00 ,2.00 ,2.00);
END LOOP;
SELECT get_uuid() INTO 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', now(), 100, now(), 100, 'CUSTOMER B', 'N', 'Y', 'N', 102, 'N');
SELECT get_uuid() INTO 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', now(), 100, now(), 100, 'Customer B', v_pricelist_id, v_schemadiscount_id, to_date('01/01/2000','dd/mm/2008'), '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
SELECT get_uuid() INTO 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' ,now() , 100,now() , 100, 2.00 ,2.00 ,2.00);
END LOOP;
SELECT get_uuid() INTO 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', now(), 100, now(), 100, 'PURCHASE', 'N', 'N', 'N', 102, 'N');
SELECT get_uuid() INTO 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', now(), 100, now(), 100, 'Purchase', v_pricelist_id, v_schemadiscount_id,to_date('01/01/2000','dd/mm/2008'), '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
SELECT get_uuid() INTO 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' ,now() , 100,now() , 100, 2.00 ,2.00 ,2.00);
END LOOP;
/*
select get_uuid() into 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', now(), 100, now(), 100, 'SALES', 'N', 'Y', 'N', 102, 'N');
select get_uuid() into 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', now(), 100, now(), 100, 'Sales', v_pricelist_id, v_schemadiscount_id, to_date('01/01/2000','dd/mm/2008'), '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
select get_uuid() into 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' ,now() , 100,now() , 100, 2.00 ,2.00 ,2.00);
END LOOP;
*/
END ; $BODY$
LANGUAGE 'plpgsql' VOLATILE;
SELECT create_pricelist();
DROP FUNCTION create_pricelist();