Inserting New Price List Discount Codes with SQL statements

Epicor ERP 10
1

I need to add about 7 new Price List Discount Codes (D35 - D41). I do not know how to use DMT or build BPMs yet, but I am quite adept at writing SQL to perform this function.

The data I need to bring into the LIVE environment is already in the TEST environment tables. Another person did this via DMT. Thus, I figured it would work with SQL.

I queried the tables I believe need the inserts & updates:

select Pl., plu.
from erp.pricelst pl inner join erp.pricelst_ud plu on pl.SysRowID = plu.ForeignSysRowID
where pl.ListType = ‘D’;

select * from erp.PriceLstGroups ;
select * from erp.CustomerDiscPriceLst;
select * from erp.PriceLstGroups;
select * from erp.CustomerDiscPriceLst;

Step 1: Insert the new discount codes into PriceLst from the TEST database:
Insert into erp.pricelst (Company, ListCode, CurrencyCode, ListDescription, StartDate, EndDate)
select Company, ListCode, CurrencyCode, ListDescription, StartDate, EndDate
from ERP10Test.erp.pricelst
where ListCode in (‘D35’,‘D36’,‘D37’,‘D38’,‘D39’,‘D40’,‘D41’);

Step 2: Insert the new Discount Codes into PriceLst_UD using SysRowID created in Step 1:
Insert into erp.pricelst_UD (ForeignSysRowID,Rebate_c)
select SysRowID, 0
from erp.pricelst
where ListCode in (‘D35’,‘D36’,‘D37’,‘D38’,‘D39’,‘D40’,‘D41’);

Step 3: Update the End Dates and the List Types:
Update erp.pricelst set listtype = ‘D’ where ListCode in (‘D35’,‘D36’,‘D37’,‘D38’,‘D39’,‘D40’,‘D41’);
Update erp.pricelst set EndDate = ‘2020-07-02’ where ListCode in (‘D17’,‘D25’,‘D30’,‘D31’,‘D33’,‘D34’);

Step 4: Insert new Discount Codes into PriceLstGroups from TEST (for product codes):
Insert into erp.PriceLstGroups (Company,ListCode,ProdCode,UOMCode)
select Company,ListCode,ProdCode,UOMCode -
from erp10test.erp.PriceLstGroups
where ListCode in (‘D35’,‘D36’,‘D37’,‘D38’,‘D39’,‘D40’);

Step 5: Run Update statements for the customers:
–update erp.CustomerDiscPriceLst set listcode = ‘D22’ where listcode = ‘D17’;
–update erp.CustomerDiscPriceLst set listcode = ‘D35’ where listcode = ‘D25’;
–update erp.CustomerDiscPriceLst set listcode = ‘D36’ where listcode = ‘D30’;
–update erp.CustomerDiscPriceLst set listcode = ‘D37’ where listcode = ‘D31’;
–update erp.CustomerDiscPriceLst set listcode = ‘D39’ where listcode = ‘D33’;
–update erp.CustomerDiscPriceLst set listcode = ‘D40’ where listcode = ‘D34’;

–update erp.CustomerDiscPriceLst set listcode = ‘D22’ where custnum IN (‘430’,‘553’,‘584’,‘784’,‘1057’,‘1415’,‘1500’,‘1880’,‘1602’);

Please advise if I missed something or that this cannot be done this way. I know Epicor Support frowns on the above. I promise to learn BPMs and DMT!

Thanks in advance.
Tony

2

Directly updating the SQL tables will invalidate your support agreement…the DMT tool is the way to go as it uses all of the business objects/logic the same as the front end. Direct SQL update may not hit all of the tables required

2 Likes
3

Hi Tony
Just don’t do this…writing data directly to the database voids your support contract, further more Epicor Business Logic updates a billion tables that may or may not be the ones you are guessing you need. There is no need there are business objects that can handle all this for you…
If you ABSOLUTELY POSITIVELY must use SQL then you can still use the Business Objects and make the calls to the BO via REST in SQL

3 Likes
4

Thanks Sue. I am going to schedule some time for DMT training. Don’t want to do anything to jeopardize support.

5

Thanks Jose. I am going to schedule some time for DMT training. Don’t want to do anything to jeopardize support.

1 Like