Solved

How to remove records via Migration Job without having a Rowkey / Rowversion

  • 6 April 2021
  • 4 replies
  • 100 views

Userlevel 1
Badge +2

I'm trying to remove records from Routing Operations (and other tables) via a Migration Job, with an CSV file as input. 

Normally i use the Remove__ method, based on an SQL statement on the table from which the records should be removed.  

This Remove__ method however requires a value for the Objkey and ObjVersion attribute. 

When using a CSV input, I can search for the Rowkey via the Function Get_Rowkey, but I cannot find a way to return the RowVersion based on either the Rowkey, or the other key values. 

Or is there an other approach to solve this ? 

icon

Best answer by anmise 6 April 2021, 13:09

View original

4 replies

Userlevel 7

I'm trying to remove records from Routing Operations (and other tables) via a Migration Job, with an CSV file as input. 

Normally i use the Remove__ method, based on an SQL statement on the table from which the records should be removed.  

This Remove__ method however requires a value for the Objkey and ObjVersion attribute. 

When using a CSV input, I can search for the Rowkey via the Function Get_Rowkey, but I cannot find a way to return the RowVersion based on either the Rowkey, or the other key values. 

Or is there an other approach to solve this ? 

Do you have data in the file to identify the unique routing lines? If so, can you join the IC Table (e.g.  IC_ROUTING_FILE_TAB) with the routing view to find the values? 
 

SELECT R.ROWVERSION, R.ROWID FROM IFSAPP.ROUTING R ,IC_ROUTING_FILE_TAB RF WHERE R.KEY_VALUE=RF.KEY_VALUE AND R.KEY_VALUE2=RF.KEY_VALUE2



 

Userlevel 1
Badge +2

Great, that works:

The imported CSV file is in the table IC_A10_ROUTING_OPER_FILE_TAB and contains all necessary values to identify unique lines. 
 

Entering the Migration Job attributes:
 

  • on the Formatting tab
    Source Name :  IC_A10_ROUTING_OPER_FILE_TAB A, IFSAPP.ROUTING_OPERATION B
    Where :  b.CONTRACT=a.CONTRACT AND b.PART_NO=a.PART_NO AND b.ROUTING_REVISION=a.ROUTING_REVISION AND b.BOM_TYPE_DB=a.BOM_TYPE_DB AND b.OPERATION_NO=a.OPERATION_NO

     
  • On the Method tab
    Adding ROUTING_OPERATION_API.Remove__ as method

     

  • and on the Source Mapping tab:
    METHOD10_OBJID               = B.OBJID
    METHOD10_OBJVERSION  = B.OBJVERSION
    METHOD10_ACTION            = 'DO'
    OBJID                                      = B.ROWID
     

     

does the job. 

Thanks a lot. 

Userlevel 4
Badge +7

Great, that works:

Hi

 Can we use for multiple LU .

I tried but i getting error.

if you know then share your experience pls.



The imported CSV file is in the table IC_A10_ROUTING_OPER_FILE_TAB and contains all necessary values to identify unique lines. 
 

Entering the Migration Job attributes:
 

  • on the Formatting tab
    Source Name :  IC_A10_ROUTING_OPER_FILE_TAB A, IFSAPP.ROUTING_OPERATION B
    Where :  b.CONTRACT=a.CONTRACT AND b.PART_NO=a.PART_NO AND b.ROUTING_REVISION=a.ROUTING_REVISION AND b.BOM_TYPE_DB=a.BOM_TYPE_DB AND b.OPERATION_NO=a.OPERATION_NO

     
  • On the Method tab
    Adding ROUTING_OPERATION_API.Remove__ as method

     

  • and on the Source Mapping tab:
    METHOD10_OBJID               = B.OBJID
    METHOD10_OBJVERSION  = B.OBJVERSION
    METHOD10_ACTION            = 'DO'
    OBJID                                      = B.ROWID
     

     

does the job. 

Thanks a lot. 

 

Userlevel 1
Badge +2

Hi Adresh, 

what error message do you get? 
Reinier

Reply