Example 1

The scp_product_group field in the SC_ITEM_LOCATION table is updated based on these conditions:

Target Table SC_ITEM_LOCATION

Target Column: scp_product_goup

Default Value: Finished Goods

  • Data Rule for (A) – Add a value of “Finished Product Paccked’ where SC_V_EXT_ITEM.res_material_type='YPAC'
    • Rule Type: Constant
    • Value: Finish Product Packed
    • Filter: 
      Add AND Condition   
AND
SC_V_EXT_ITEM       res_material_type   Equals 'YPAC'

Preview

SC_V_EXT_ITEM.res_material_type='YPAC'
    • Final expression: 
      UPDATE SC_V_EXT_ITEM_LOCATION 
SET    SC_V_EXT_ITEM_LOCATION.scp_product_group = 'Finish Product Packed'
FROM   SC_V_EXT_ITEM_LOCATION 
  JOIN SC_ITEM_LOCATION ON SC_ITEM_LOCATION.item_id = SC_V_EXT_ITEM_LOCATION.item_id AND SC_ITEM_LOCATION.location_id = SC_V_EXT_ITEM_LOCATION.location_id
  JOIN SC_ITEM ON SC_ITEM_LOCATION.item_id = SC_ITEM.item_id 
  JOIN SC_V_EXT_ITEM ON SC_ITEM.item_id = SC_V_EXT_ITEM.item_id
WHERE  SC_V_EXT_ITEM.res_material_type='YPAC'
  • Data Rule for (B) - Add a value of 'Blends' where SC_ITEM.material_type='YBLU' OR SC_ITEM.material_type='YINT' AND SC_ITEM.product_group <>2053
    • Rule Type: Constant
    • Value: Blends
    • Filter: 
      Add OR Condition
OR
SC_ITEM     material_type   Equals  'YBLU'

    Add AND Sub Condition
    AND
    SC_ITEM     material_type   Equals          'YINT'
    SC_ITEM     product_group   Does not Equal  2053

Preview
SC_ITEM.material_type='YBLU' OR (SC_ITEM.material_type='YINT' AND SC_ITEM.product_group<>2053)
    • Final expression: 
      UPDATE SC_V_EXT_ITEM_LOCATION 
SET    SC_V_EXT_ITEM_LOCATION.scp_product_group = 'Blends'
FROM   SC_V_EXT_ITEM_LOCATION 
  JOIN SC_ITEM_LOCATION ON SC_ITEM_LOCATION.item_id = SC_V_EXT_ITEM_LOCATION.item_id AND SC_ITEM_LOCATION.location_id = SC_V_EXT_ITEM_LOCATION.location_id    
  JOIN SC_ITEM ON SC_ITEM.item_id = SC_ITEM_LOCATION.item_id
WHERE  SC_ITEM.material_type='YBLU' OR (SC_ITEM.material_type='YINT' AND SC_ITEM.product_group<>2053)
  • Default Rule for (C) - Add the column value of SC_ITEM.item_product_type_code where SC_ITEM.product_group<>2053
    • Rule Type: Table Column
    • Table : SC_ITEM
    • Column: item_product_type_codeFilter:
    • Filter: 
      Add AND Condition   
AND
SC_ITEM     product_group   Does not Equal  2053

Preview
SC_ITEM.product_group<>2053
    • Final expression: 
      UPDATE SC_V_EXT_ITEM_LOCATION 
SET    SC_V_EXT_ITEM_LOCATION.scp_product_group = SC_ITEM.item_product_type_code
FROM   SC_V_EXT_ITEM_LOCATION 
  JOIN SC_ITEM_LOCATION ON SC_ITEM_LOCATION.item_id = SC_V_EXT_ITEM_LOCATION.item_id AND SC_ITEM_LOCATION.location_id = SC_V_EXT_ITEM_LOCATION.location_id    
  JOIN SC_ITEM ON SC_ITEM.item_id = SC_ITEM_LOCATION.item_id
WHERE  SC_ITEM.product_group<>2053
  • Data Rule for (D) - Add the expression value of 'IT_'+SC_V_EXT_ITEM.xs_item_type_clv_id where SC_ITEM.product_group= 2053
    • Rule Type: Expression
    • Expression: 'IT_' + SC_V_EXT_ITEM.xs_item_type_clv_id
    • Filter: 
      Add AND Condition   
AND
SC_ITEM     product_group   Equals 2053

Preview
SC_ITEM.product_group=2053
    • Final expression: 
      UPDATE SC_V_EXT_ITEM_LOCATION 
SET    SC_V_EXT_ITEM_LOCATION.scp_product_group = 'IT_'+SC_V_EXT_ITEM.xs_item_type_clv_id
FROM   SC_V_EXT_ITEM_LOCATION 
  JOIN SC_ITEM_LOCATION ON SC_ITEM_LOCATION.item_id = SC_V_EXT_ITEM_LOCATION.item_id AND SC_ITEM_LOCATION.location_id = SC_V_EXT_ITEM_LOCATION.location_id    
  JOIN SC_ITEM ON SC_ITEM.item_id = SC_ITEM_LOCATION.item_id
WHERE  SC_ITEM.product_group=2053
  • Data Rule for (E) - Add the keyword value of 'Current Date' where SC_V_EXT_ITEM.res_product_group is Null
    • Rule Type: Keyword
    • Keywords: Current Date
    • Filter: 
      Add AND Condition   
AND
SC_ITEM     product_group   Is Null

Preview
SC_ITEM.product_group IS NULL
    • Final expression: 
      UPDATE SC_V_EXT_ITEM_LOCATION 
SET    SC_V_EXT_ITEM_LOCATION.scp_product_group = <Current Date>
FROM   SC_V_EXT_ITEM_LOCATION 
  JOIN SC_ITEM_LOCATION ON SC_ITEM_LOCATION.item_id = SC_V_EXT_ITEM_LOCATION.item_id AND SC_ITEM_LOCATION.location_id = SC_V_EXT_ITEM_LOCATION.location_id    
  JOIN SC_ITEM ON SC_ITEM.item_id = SC_ITEM_LOCATION.item_id
WHERE  SC_ITEM.product_group is NULL
Note: If all of the above conditions are false, the default value for the SC_ITEM_LOCATION.scp_product_group field is set to Finished Goods.