OPEN INTERFACE FOR ITEM CATEGORY

3247206

Hi All,

     I have tried to update the existing category for an item trough open interface but category is not updating So Please help and give suggestions on this . I am sending my code So please see and give any suggestions need in the query.

INSERT INTO mtl_item_categories_interface

( category_set_id

,category_id

,last_update_date

,ORGANIZATION_CODE

,process_flag

,inventory_item_id

,old_category_id

,transaction_type

,set_process_id

)

VALUES

( 1000000015     -- category_set_id

,1513            -- new category_id

,SYSDATE

,'V1'           -- ORGANIZATION_CODE (should be master_orgaization_id if category set is controlled at master level)

,1               -- always 1

,380989         -- Item name  Note: for performance consideration use inventory_item_id in place of item_number

,2257           -- old category_id

,'UPDATE'

,555            -- set_process_id can be any positive number

);

commit;

Gaz in Oz

mysql> INSERT INTO mtl_item_categories_interface    -> ( category_set_id    ->  ,category_id    ->  ,last_update_date    ->  ,ORGANIZATION_CODE    ->  ,process_flag    ->  ,inventory_item_id    ->  ,old_category_id    ->  ,transaction_type    ->  ,set_process_id    -> )    -> VALUES    -> ( 1000000015  -- category_set_id    ->  ,1513        -- new category_id    ->  ,SYSDATE    ->  ,'V1'        -- ORGANIZATION_CODE (should be master_orgaization_id if category set is controlled at master level)    ->  ,1           -- always 1    ->  ,380989      -- Item name  Note: for performance consideration use inventory_item_id in place of item_number    ->  ,2257        -- old category_id    ->  ,'UPDATE'    ->  ,555         -- set_process_id can be any positive number    -> );ERROR 1054 (42S22): Unknown column 'SYSDATE' in 'field list'

You need to use "SYSDATE()" in mysql. (Or NOW() or CURRENT_DATE(), depending on what you actually want. See the documentation link for the differences).

mysql> INSERT INTO mtl_item_categories_interface    -> ( category_set_id    ->  ,category_id    ->  ,last_update_date    ->  ,ORGANIZATION_CODE    ->  ,process_flag    ->  ,inventory_item_id    ->  ,old_category_id    ->  ,transaction_type    ->  ,set_process_id    -> )    -> VALUES    -> ( 1000000015   -- category_set_id    ->  ,1513         -- new category_id    ->  ,SYSDATE()    -- last_udate_date datatype DATETIME    ->  ,'V1'         -- ORGANIZATION_CODE (should be master_orgaization_id if category set is controlled at master level)    ->  ,1            -- always 1    ->  ,380989       -- Item name  Note: for performance consideration use inventory_item_id in place of item_number    ->  ,2257         -- old category_id    ->  ,'UPDATE'    ->  ,555          -- set_process_id can be any positive number    -> );Query OK, 1 row affected (0.00 sec)

https://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html

mysql> select now(), sysdate(), current_date();+---------------------+---------------------+----------------+| now()               | sysdate()           | current_date() |+---------------------+---------------------+----------------+| 2017-08-20 03:25:06 | 2017-08-20 03:25:06 | 2017-08-20     |+---------------------+---------------------+----------------+1 row in set (0.00 sec)

Dave Stokes-MySQL Community Team-Oracle

What, if any, errors are you seeing after entering the query. Also include 'SHOW CREATE TABLE  mtl_item_categories_interface' too.

Gaz in Oz

mysql> INSERT INTO mtl_item_categories_interface    -> ( category_set_id    ->  ,category_id    ->  ,last_update_date    ->  ,ORGANIZATION_CODE    ->  ,process_flag    ->  ,inventory_item_id    ->  ,old_category_id    ->  ,transaction_type    ->  ,set_process_id    -> )    -> VALUES    -> ( 1000000015  -- category_set_id    ->  ,1513        -- new category_id    ->  ,SYSDATE    ->  ,'V1'        -- ORGANIZATION_CODE (should be master_orgaization_id if category set is controlled at master level)    ->  ,1           -- always 1    ->  ,380989      -- Item name  Note: for performance consideration use inventory_item_id in place of item_number    ->  ,2257        -- old category_id    ->  ,'UPDATE'    ->  ,555         -- set_process_id can be any positive number    -> );ERROR 1054 (42S22): Unknown column 'SYSDATE' in 'field list'

You need to use "SYSDATE()" in mysql. (Or NOW() or CURRENT_DATE(), depending on what you actually want. See the documentation link for the differences).

mysql> INSERT INTO mtl_item_categories_interface    -> ( category_set_id    ->  ,category_id    ->  ,last_update_date    ->  ,ORGANIZATION_CODE    ->  ,process_flag    ->  ,inventory_item_id    ->  ,old_category_id    ->  ,transaction_type    ->  ,set_process_id    -> )    -> VALUES    -> ( 1000000015   -- category_set_id    ->  ,1513         -- new category_id    ->  ,SYSDATE()    -- last_udate_date datatype DATETIME    ->  ,'V1'         -- ORGANIZATION_CODE (should be master_orgaization_id if category set is controlled at master level)    ->  ,1            -- always 1    ->  ,380989       -- Item name  Note: for performance consideration use inventory_item_id in place of item_number    ->  ,2257         -- old category_id    ->  ,'UPDATE'    ->  ,555          -- set_process_id can be any positive number    -> );Query OK, 1 row affected (0.00 sec)

https://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html

mysql> select now(), sysdate(), current_date();+---------------------+---------------------+----------------+| now()               | sysdate()           | current_date() |+---------------------+---------------------+----------------+| 2017-08-20 03:25:06 | 2017-08-20 03:25:06 | 2017-08-20     |+---------------------+---------------------+----------------+1 row in set (0.00 sec)