WikiAdmin: 1 revision imported

2023-07-14T13:00:38Z

1 revision imported

← Older revision	Revision as of 13:00, 14 July 2023
(No difference)

Intra>Chanson: /* About Base Product */

2020-09-29T13:59:04Z

About Base Product

New page

== 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:

{| border="0" cellspacing="0" cellpadding="5" align="center"
! 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.<BR>

Identify Mismatched BASE_PRODUCT codes:
<PRE>
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)
</PRE>

Identify Products where the plant number doesn't match the product code.
<pre>
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))</pre>

Identify null Plant numbers:
<PRE>
select substr(product_code,strlen(product_code)-1,strlen(product_code)),product_code,plant_No
from artprod where plant_no is null
</PRE>

Update Plant Numbers if needed:
<PRE>
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'
</PRE>

Update Products:
<pre>
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))
</pre>

Review the updated products
<pre>
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))
</pre>

Check for duplicates:
<pre>
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
</pre>

Update the products
<pre>
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))
</pre>

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:
<PRE>
update artprod set base_product=null
where base_product<>substr(product_code,1,strlen(product_code)-3)
</PRE>

== Alternate Example - 1 Char Plant using NOTES_2 ==
<PRE>
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))

</PRE>

==DataScope Product Review==

Create a DataScope Report with this query:
<PRE>
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'
</PRE>

Recommended Field Layout

Data:
*CT

Rows:
*Product Class
*Class Name
*Base Product
*Product Code Core

Columns:
*Plant
*Product Code Plant

Updating Base Products - Revision history

WikiAdmin: 1 revision imported

Intra>Chanson: /* About Base Product */