Acceptance Testing/Edit business partner properties script

Script 2.40/2.3x

DECLARE

v_Paymentterm_id NUMBER(10);
v_Pricelist_id NUMBER(10);
v_client_id    NUMBER(10);
v_invoiceschedule_id NUMBER(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;

commit;

END;

Script 2.50 or higher

DECLARE

v_Paymentterm_id varchar2(32);
v_Pricelist_id varchar2(32);
v_client_id    varchar2(32);
v_invoiceschedule_id varchar2(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;

commit;

END;