Single Data Migration job of a Purchase Order

  • 13 January 2020
  • 9 replies
  • 1970 views

Badge +2

Hi -  I can create 2 data migration jobs to create a Purchase Order. - One to create the header and one to create the lines.

I am looking to create a single Migrate Source Data DM Job to create header  and lines based on an imported table of data.

So the stage 1 imported Data would be simplified as something like..

  • Supplier
  • Part No
  • Qty 

Stage 2 to Create a PO for the distinct list of Suppliers and then create the attached lines

Anyone think this is possible without custom code? Thanks for looking..

 


9 replies

Userlevel 7
Badge +19

@dahuuk71

Hello David,

Welcome to IFS community !!! :relaxed:

 

I was trying to understand your questions correctly. So do you want to confirm ;

  • whether there is a possibility to import data from a table of which is consist of simplified/different table columns than the standard names of the table columns?
     
  • whether you can import certain set of data, in your case set of suppliers only.


if so, I hope following would be helpful. 

 

  1. Whether there is a possibility to import data from a table of which is consist of simplified/different table columns than the standard names of the table columns?

Yes. there is a possibility to do that.. 

As per the documentation ...
When transferring data from an external system (or another IFS installation) into IFS, it is necessary for IFS/Data Migration to know where the data will be retrieved from and where it should go to. 

To do this mapping you can use the Maintenance/Source Mapping tab to map columns from the data source to the columns in the target view to define what old column goes into what IFS column. If a value is required in IFS but does not exist in the old system, you define standard values for the columns. If the old system supply values in a column that does not correspond to IFS basic data or pre-defined values, you can specify a conversion list allowing the old system to supply one value and IFS to transform the value into a new, valid value.

Normally, columns will automatically be created in this window through the methods in the method list or by specifying an IFS View Name and a Source Name in the migration job header. This means that you normally only map columns/specify default values during this activity.

 

Please refer following documentation, it will guide you how to do it correctly. These documentations are from APP 9. but if you need documentation assistance for another version, please feel free to let me know. 

Foundation1 / Administration Guide / Data Management / Data Migration / Migration Types / Source Migration 

https://wit.ifsworld.com/f1docs/apps9/Foundation1/040_administration/260_data_management/050_data_migration/002_migration_types/020_source_migration/160_create_source_migration_job/default.htm

 

  1. whether you can import certain set of data, in your case set of suppliers only?

Yes. you can. 

You could narrow down the required data from the database information. You could use where accordingly  in the Database Information Section ,to migrate only set of suppliers.

 

Following figure is a an example how it was handled for a different requirement. 

Database Information section

 

 

I have added the documentation of Create Source Migration Job in a PDF format in the attachments. 

 

Finally you can configure the migration job according to your requirement. I will add Tips and Tricks as well. 

Foundation1 / Administration Guide / Data Management / Data Migration / Migration Types / Source Migration / Tips And Tricks / Tips & Tricks - Data from Views/Tables into multiple LUs

https://wit.ifsworld.com/f1docs/apps9/Foundation1/040_administration/260_data_management/050_data_migration/002_migration_types/020_source_migration/300_tips_and_tricks/TipsAndTricksTableToLUs.htm

 

 

Badge +2

Hi Mino -I have read the help and tips and tricks and been using DM for many years... Trying to simplify the question….I have created many Parent Child  DM jobs for (say) Inventory Parts and Sales Parts.  I want to create a DM job that imports into a parent child data set (PO Header and Line) where I do not know the primary key of the header (PO No) until after the header has been created (PO No is not known until creation) . I added the data structure to try and help explain but maybe it has confused..   Does that make sense? Dave

Userlevel 4

Would you use (scheduled) event for this?
Load data to IC_ tab
Then sql event you can

  • select distinct supplier
  • Create header (I think there is procedure what returns created order number)
  • Loop lines from IC_ tab for this supplier and create lines

Migration this might be impossible since, that is not looping that sence and lost connection between runs.
Just tought with out detailed thinking. When you run migration for headers, store created order numbers and supplier to some custom LU. Then run another migrations which should create lines for these orders.

Userlevel 7
Badge +19

@dahuuk71

I want to create a DM job that imports into a parent child data set (PO Header and Line) where I do not know the primary key of the header (PO No) until after the header has been created (PO No is not known until creation).


Now I got it.. :thumbsup:

well.. at the first glance it may look like it may not be feasible to do in a single DM job since the PO No is not available until the header is created. 

But shall I suggest this to you, what about creating a DM job that connects several jobs as a chain.

I hope @Jouni ‘s suggestion would work as well..

 

Again.. when you specify the method list, there is a possibility to add action for each method as BeforeLoop, InLoop or AfterLoop, to specify the order to execute. 

 

The following tip may not relevant directly to implement your requirement, but it may be useful in your work..

Normally the methods that are specified to execute 'InLoop',  will be executed for each row of the main SELECT. You may limit this by connecting the method to a specific Column Name with a given Column Value. The method will now be executed within the loop only when specified column holds the specified value.

Userlevel 7

You should be able to do it by referring to the ORDER_NO created from the first method, in the purchase order line method list attribute e.g. ORDER_NO@10 (or whatever execute sequence number your first method has). 
Created a quick example:

Add the methods you want to use

Method list

RMB on the line method and go to the attribute list

Reference the ORDER_NO from method 10 in this case using ORDER_NO@10.

 

I used INTFACE_METHOD_LIST_API.GET_SEQUENCE_NO('PUR_ORDER_NO','NEXTVAL') ro generate the PO number. That works for me. 

Userlevel 2
Badge +6

I agree with @anmise, you can reference any previously generated field values, this would be the easiest way as it means you can line up the API’s in a single job.

Just from experience though, sometimes its best to split jobs as once you start referencing large views (such as parts etc) the migration jobs can become very bloated and difficult to fault check later on. 

Userlevel 3
Badge +8

You should be able to do it by referring to the ORDER_NO created from the first method, in the purchase order line method list attribute e.g. ORDER_NO@10 (or whatever execute sequence number your first method has). 
Created a quick example:

Add the methods you want to use

Method list

RMB on the line method and go to the attribute list

Reference the ORDER_NO from method 10 in this case using ORDER_NO@10.

 

I used INTFACE_METHOD_LIST_API.GET_SEQUENCE_NO('PUR_ORDER_NO','NEXTVAL') ro generate the PO number. That works for me. 

 


Hi @anmise  …

I have a similar scenario to create a Customer Order Header and Detail using one Migration Job. 

I tried using ORDER_NO@10 to get Order No generated by the header but failed. Do you have any idea why? 

And where this method INTFACE_METHOD_LIST_API.GET_SEQUENCE_NO('PUR_ORDER_NO','NEXTVAL') are used ?

​Thanks!

 

Userlevel 2
Badge +6

Hi, I’m using anmise’s solution here and it is working, but is there anyway of configuring this to allow multiple PO lines on one purchase order? Currently, a new PO will be created for each line in my CSV, even if the site and supplier align.

Many thanks

Badge +3

hello @washton ,

it has been a year since your post, but I joined the community recently. 

I attached screenshots from IFS cloud, but it works the same in previous versions:

step 5 - creates a header

step 10 - creates lines

 

 

in the method attributes of the 10th step, you have reference to the order number created in the 5th step:

 

the 5th step has selected: On new Master:

 

which means that every time key columns change it triggers the 5th step:

Key columns are defined in the source mapping and has flags P or K:

You can put more columns, but they need to be at the top 

 

regards,

Kajetan

Reply