Updating Base Products

About Base Product

The field Base_Product in ARTPROD, appears as Product Base in Product Maintenance. Currently it's main use is to allow the dispatch system to transparently switch product codes between plants. If Base_Product is not properly setup, then when a user ships a plant 2 product on a plant 1 order, the product will stay with the plant 1 product code.

When properley setup, a base_product will include the product code, without the plant number and the plant separator character. For example:

Product Code	Base Product	Plant	Comment
3000-01	3000	01	common 2 digit plant
3000-02	3000	02
30.1	30	1	Single digit
3034-021	3034	021	Triple digit

Updating the Base Product Code

This collection of queries is based on using the NOTES_1 field to temporarily store the new base product value. Verify that the NOTES_1 field is available before proceeding.

Identify Mismatched BASE_PRODUCT codes:

select product_code,base_product,notes_1,plant_no,
 substr(product_code,strlen(product_code)-1,strlen(product_code)),
 description_1 from artprod
where base_product<>substr(product_code,1,strlen(product_code)-3)

Identify Products where the plant number doesn't match the product code.

select product_code,base_product,notes_1,plant_no,
 substr(product_code,strlen(product_code)-1,strlen(product_code)),
 active_flag from artprod
where plant_no<>substr(product_code,strlen(product_code)-1,strlen(product_code))

Identify null Plant numbers:

select substr(product_code,strlen(product_code)-1,strlen(product_code)),product_code,plant_No
 from artprod where plant_no is null

Update Plant Numbers if needed:

update artprod
set plant_no=substr(product_code,strlen(product_code)-1,strlen(product_code))
 where plant_no is null and substr(product_code,strlen(product_code)-1,strlen(product_code)) between '00' and '99'

Update Products:

update artprod
set notes_1=substr(product_code,1,strlen(product_code)-3)
where plant_no=substr(product_code,strlen(product_code)-1,strlen(product_code))

Review the updated products

select product_code,base_product,notes_1,plant_no,substr(product_code,strlen(product_code)-1,strlen(product_code)) from artprod
where plant_no=substr(product_code,strlen(product_code)-1,strlen(product_code))

Check for duplicates:

select notes_1,plant_no,count(*),mIN(product_code) First_Prod,max(product_code) Last_Prod from artprod
where plant_no=substr(product_code,strlen(product_code)-1,strlen(product_code)) AND NOTES_1 IS NOT NULL
group by notes_1,plant_no
having count(*)>1

Update the products

update artprod
set base_product=notes_1 where notes_1 is not null and notes_1<>''
where plant_no=substr(product_code,strlen(product_code)-1,strlen(product_code))

NOTE: You may need to clear old base products before updating. Add the where clause if there are valid base products you want to keep:

update artprod set base_product=null
where base_product<>substr(product_code,1,strlen(product_code)-3)

Alternate Example - 1 Char Plant using NOTES_2

select product_code,base_product,notes_1,plant_no,
 substr(product_code,strlen(product_code),strlen(product_code)),
 description_1 from artprod
where base_product<>substr(product_code,1,strlen(product_code)-2)


select product_code,base_product,notes_1,plant_no,
 substr(product_code,strlen(product_code),strlen(product_code)),
 active_flag from artprod
where plant_no<>substr(product_code,strlen(product_code),strlen(product_code))


update artprod
set notes_2=substr(product_code,1,strlen(product_code)-2)
where plant_no=substr(product_code,strlen(product_code),strlen(product_code))

select product_code,base_product,notes_2,plant_no,substr(product_code,strlen(product_code)-1,strlen(product_code)) from artprod
where notes_2 is not null

select notes_2,plant_no,count(*) from artprod
where plant_no=substr(product_code,strlen(product_code),strlen(product_code))
group by notes_2,plant_no
having count(*)>1

update artprod set base_product=null
where base_product<>substr(product_code,1,strlen(product_code)-2)

update artprod
set base_product=notes_2 where notes_2 is not null and notes_2<>'' and
 plant_no=substr(product_code,strlen(product_code),strlen(product_code))

DataScope Product Review

Create a DataScope Report with this query:

select product_class,sales_gl,p.plant_no,base_product,p.active_flag,1 ct,product_code,description_1 prod_name,a.description acct_name,a.segment_2,c.description "Class Name",LINK_PRICE_PRODUCT,
substr(product_code,strlen(product_code)-1,strlen(product_code)) "Prod Code Plant",
substr(product_code,1,strlen(product_code)-3) "Prod Code Core"

 from artprod p
inner join gltacct a on a.gl_account=p.sales_gl
inner join artprcls c on c.class_no=p.product_class
where active_flag='Y'

Recommended Field Layout

Data:

• CT

Rows:

• Product Class
• Class Name
• Base Product
• Product Code Core

Columns:

• Plant
• Product Code Plant