ERP 2.50:Acceptance Testing/Edit business partner properties script PostgreSQL

Scripts for 2.40/2.3x

 
 CREATE OR REPLACE FUNCTION create_edit_bpartners()
   RETURNS void AS
 $BODY$ DECLARE
 
 v_Paymentterm_id numeric(10);
 v_Pricelist_id numeric(10);
 v_client_id    numeric(10);
 v_invoiceschedule_id numeric(10);
 
 BEGIN
 
 SELECT MAX(AD_CLIENT_ID) INTO v_client_id FROM AD_CLIENT;
 
 SELECT C_PAYMENTTERM_ID INTO v_Paymentterm_id FROM C_PAYMENTTERM WHERE   VALUE='30d/5' AND AD_CLIENT_ID=v_client_id;
 
 SELECT MAX(C_INVOICESCHEDULE_ID) INTO v_invoiceschedule_id FROM  C_INVOICESCHEDULE WHERE AD_CLIENT_ID=v_client_id;
 
 UPDATE C_BPARTNER SET C_PAYMENTTERM_ID=v_Paymentterm_id, ISVENDOR='N',   ISCUSTOMER='Y', PAYMENTRULE='4', INVOICERULE='D' WHERE C_BP_GROUP_ID IN  (SELECT  C_BP_GROUP_ID FROM C_BP_GROUP WHERE VALUE='CUS' AND AD_CLIENT_ID=v_client_id);
 
 UPDATE C_BPARTNER SET C_INVOICESCHEDULE_ID = v_invoiceschedule_id,  INVOICERULE='S' WHERE UPPER(VALUE)='CUSA';
 
 SELECT C_PAYMENTTERM_ID INTO v_Paymentterm_id FROM C_PAYMENTTERM WHERE VALUE='90d' AND AD_CLIENT_ID=v_client_id; 
 
 SELECT M_PRICELIST_ID INTO v_Pricelist_id FROM M_PRICELIST WHERE  NAME='PURCHASE' AND AD_CLIENT_ID=v_client_id;                   
 UPDATE C_BPARTNER SET PO_PAYMENTTERM_ID=v_Paymentterm_id, ISVENDOR='Y',  ISCUSTOMER='N', PAYMENTRULEPO='1', PO_PRICELIST_ID=v_Pricelist_id WHERE  C_BP_GROUP_ID IN (SELECT C_BP_GROUP_ID FROM C_BP_GROUP WHERE VALUE IN  ('VEN','CRT') AND AD_CLIENT_ID=v_client_id);
 
 UPDATE C_BPARTNER SET ISVENDOR='N', ISCUSTOMER='N', ISEMPLOYEE='Y' WHERE  C_BP_GROUP_ID IN (SELECT C_BP_GROUP_ID FROM C_BP_GROUP WHERE VALUE IN ('EM')  AND AD_CLIENT_ID=v_client_id);
 
 UPDATE C_BPARTNER SET ISVENDOR='N', ISCUSTOMER='N', ISsalesrep='Y' WHERE  C_BP_GROUP_ID IN (SELECT C_BP_GROUP_ID FROM C_BP_GROUP WHERE VALUE IN ('SM')  AND AD_CLIENT_ID=v_client_id);
 
 SELECT M_PRICELIST_ID INTO v_Pricelist_id FROM M_PRICELIST WHERE NAME='CUSTOMER A' AND AD_CLIENT_ID=v_client_id;
 
 UPDATE C_BPARTNER SET M_PRICELIST_ID=v_Pricelist_id WHERE VALUE='CUSA' AND AD_CLIENT_ID=v_client_id;
 
 SELECT M_PRICELIST_ID INTO v_Pricelist_id FROM M_PRICELIST WHERE NAME='CUSTOMER B' AND AD_CLIENT_ID=v_client_id;
 
 UPDATE C_BPARTNER SET M_PRICELIST_ID=v_Pricelist_id WHERE VALUE='CUSB' AND AD_CLIENT_ID=v_client_id;
      
 END ; $BODY$
 LANGUAGE 'plpgsql' VOLATILE;
 
 SELECT create_edit_bpartners();
 
 DROP FUNCTION create_edit_bpartners();

Scripts for 2.50 or higher

 
 CREATE OR REPLACE FUNCTION create_edit_bpartners()
   RETURNS void AS
 $BODY$ DECLARE
 
 v_Paymentterm_id varchar(32);
 v_Pricelist_id varchar(32);
 v_client_id    varchar(32);
 v_invoiceschedule_id varchar(32);
 
 BEGIN
 
 SELECT AD_CLIENT_ID INTO v_client_id FROM AD_CLIENT WHERE created=(SELECT max(created) FROM ad_client);
 
 SELECT C_PAYMENTTERM_ID INTO v_Paymentterm_id FROM C_PAYMENTTERM WHERE   VALUE='30d/5' AND AD_CLIENT_ID=v_client_id;
 
 SELECT MAX(C_INVOICESCHEDULE_ID) INTO v_invoiceschedule_id FROM  C_INVOICESCHEDULE WHERE AD_CLIENT_ID=v_client_id;
 
 UPDATE C_BPARTNER SET C_PAYMENTTERM_ID=v_Paymentterm_id, ISVENDOR='N',   ISCUSTOMER='Y', PAYMENTRULE='4', INVOICERULE='D' WHERE C_BP_GROUP_ID IN  (SELECT  C_BP_GROUP_ID FROM C_BP_GROUP WHERE VALUE='CUS' AND AD_CLIENT_ID=v_client_id);
 
 UPDATE C_BPARTNER SET C_INVOICESCHEDULE_ID = v_invoiceschedule_id,  INVOICERULE='S' WHERE UPPER(VALUE)='CUSA';
 
 SELECT C_PAYMENTTERM_ID INTO v_Paymentterm_id FROM C_PAYMENTTERM WHERE VALUE='90d' AND AD_CLIENT_ID=v_client_id; 
 
 SELECT M_PRICELIST_ID INTO v_Pricelist_id FROM M_PRICELIST WHERE  NAME='PURCHASE' AND AD_CLIENT_ID=v_client_id;                   
 UPDATE C_BPARTNER SET PO_PAYMENTTERM_ID=v_Paymentterm_id, ISVENDOR='Y',  ISCUSTOMER='N', PAYMENTRULEPO='1', PO_PRICELIST_ID=v_Pricelist_id WHERE  C_BP_GROUP_ID IN (SELECT C_BP_GROUP_ID FROM C_BP_GROUP WHERE VALUE IN  ('VEN','CRT') AND AD_CLIENT_ID=v_client_id);
 
 UPDATE C_BPARTNER SET ISVENDOR='N', ISCUSTOMER='N', ISEMPLOYEE='Y' WHERE  C_BP_GROUP_ID IN (SELECT C_BP_GROUP_ID FROM C_BP_GROUP WHERE VALUE IN ('EM')  AND AD_CLIENT_ID=v_client_id);
 
 UPDATE C_BPARTNER SET ISVENDOR='N', ISCUSTOMER='N', ISsalesrep='Y' WHERE  C_BP_GROUP_ID IN (SELECT C_BP_GROUP_ID FROM C_BP_GROUP WHERE VALUE IN ('SM')  AND AD_CLIENT_ID=v_client_id);
 
 SELECT M_PRICELIST_ID INTO v_Pricelist_id FROM M_PRICELIST WHERE NAME='CUSTOMER A' AND AD_CLIENT_ID=v_client_id;
 
 UPDATE C_BPARTNER SET M_PRICELIST_ID=v_Pricelist_id WHERE VALUE='CUSA' AND AD_CLIENT_ID=v_client_id;
 
 SELECT M_PRICELIST_ID INTO v_Pricelist_id FROM M_PRICELIST WHERE NAME='CUSTOMER B' AND AD_CLIENT_ID=v_client_id;
 
 UPDATE C_BPARTNER SET M_PRICELIST_ID=v_Pricelist_id WHERE VALUE='CUSB' AND AD_CLIENT_ID=v_client_id;
      
 END ; $BODY$
 LANGUAGE 'plpgsql' VOLATILE;
 
 SELECT create_edit_bpartners();
 
 DROP FUNCTION create_edit_bpartners();