Solved

Data migration : Can I automatically pick the next customer order number

  • 5 January 2024
  • 7 replies
  • 188 views
K
Rank
Badge +3
  • ksebf
  • Sidekick (Partner)
  • 7 replies

I'd like to wish you all a very happy new year. 

I'm creating migration jobs for customer orders where I'd like them to retrieve as much information as possible automatically. 

At the moment, I only have 3 mandatory pieces of information to enter. 

 

 

I'd like the order number to be entered automatically, based on the last existing order number. Is this possible? Where do I enter it? 

For the example, my last order number is "S23000005957".

I'd like the order number to be automatically set to "S23000005958" when I run my job, and for the countdown to take place naturally. If I manually recreate an order, my number should be "S23000005959".

icon

Best answer by Marcel.Ausan 5 January 2024, 15:46

View original

7 replies

Marcel.Ausan
Rank
Userlevel 6
Badge +15

@ksebf normally the CO number comes from the Coordinator Group ID || Order No

In below example, if the coordinator creating the order belongs to coordinator group ID 1 then the order will be ‘1’ || ‘1077’ => 11077

 

So if you only have the ‘S’ coordinator group id I guess you could so something like this in the default value field:

  • ‘S’ || Order_Coordinator_Group_API.Get_Order_No(‘S’)

Once a new CO is created, the order no from coordinator group would be increased automatically by the customer_order_api routine.

I hope this helps.

https://www.linkedin.com/in/marcelausan/
K
Rank
Badge +3

Hello Marcel, thank you for your reply. I've tested your method with one line, which might suit me. 

 

 

The line has been created, great :) However, I wanted to test again by adding lines, and your formula doesn't increment the order numbers…

 

 

The order number remains locked at 1000 and the other lines appear as an update of the first.

Marcel.Ausan
Rank
Userlevel 6
Badge +15

Now I realized what’s wrong. The Customer Order No is created automatically by the system using coordinator group and next number only when the user leaves the field blank.

But on the other hand, the mig job will not allow a null value since the order_no field is marked as K (key). Could you give it a try and leave the order_no blank?

Normally most business objects will have a DB Sequence that will increase the number. Calling that sequence_name.next_val() will bring the next number, but for Customer Orders the logic is different.

Even if you manage to create the orders via mig job with order no *1000 / *1001 / *1002 → the number in the coordinator groups is not increased, thus creating problems when another coordinator belonging to that group would want to create some orders → it will say order already exists.

 

https://www.linkedin.com/in/marcelausan/
K
Rank
Badge +3

In fact, if you leave it empty, it works, and the incrementation is carried out correctly. Thanks Marcel, that's exactly what I was looking for. I wasn't expecting that leaving empty would work with a K. 

Subsidiary question with the results. The coordinator group ID chosen by the system is 'S'. If I want to select another one (for example '*'), do you know how to do it? 

Thanks again for your answer <3

K
Rank
Badge +3

Ok sorry, silly question, just choose a default coordinator with a different coordinator group. 

K
Rank
Badge +1

Now I realized what’s wrong. The Customer Order No is created automatically by the system using coordinator group and next number only when the user leaves the field blank.

But on the other hand, the mig job will not allow a null value since the order_no field is marked as K (key). Could you give it a try and leave the order_no blank?

Normally most business objects will have a DB Sequence that will increase the number. Calling that sequence_name.next_val() will bring the next number, but for Customer Orders the logic is different.

Even if you manage to create the orders via mig job with order no *1000 / *1001 / *1002 → the number in the coordinator groups is not increased, thus creating problems when another coordinator belonging to that group would want to create some orders → it will say order already exists.

 

Hi Marcel,

Where would you put sequence_name.next_val() into? 

I’m trying to achieve almost the same thing but for absence limits. Otherwise if user uses the migration job they would have to check what’s the max seq no on absence_limit_year, enter into file and then perform migration which adds more work. I wanted to make it dynamic where it gets autopopulated during the job, leaving it empty in my case did not work and seq_no has a K flag. When left empty I get expected PL/SQL: ORA-00936: missing expression. 

Appreciate your thoughts on this. 

Jens
Rank
Userlevel 4
Badge +8

 

Hi @ksebf @Marcel.Ausan @kgray 

 

Sequence_name.nextval can be added into the tab Source Mapping and here either in the column “source name” or “default value” to a specific “column name’.

But keep in mind:

  • When you’re using a ...nextval functionality (Oracle sequence) then every time you execute your job new records will be created/inserted and not updated because you’re always fetching the next sequence number. If you also want to update already migrated records in that case then you have to ensure that you will update these otherwise you produce records you don’t want to have. Saving these sequences in a conversion list or a key manipulation in the tab method list attribute can be a solution depending on the logical unit you’re using
  • As mentioned in this example the function Order_Coordinator_Group_API.Get_Order_No(‘S’) or Order_Coordinator_Group_API.Get_Cust_Order_No(‘S’) will only fetch the current order number of “Customer Order Number” of the screen Coordinator Groups depending on the Coordinator Group Id but it will not increase the order_no automatically. For that purpose you have to use the procedure Order_Coordinator_Group_API.Increase_Cust_Order_No in the method list. This method/procedure (e.g. EXEC SEQ 5) must be  placed before the entry for CUSTOMER_ORDER (e.g. EXEC SEQ 10). In the method list go into the method list attributes of customer_order (EXEC SEQ 10) and here for the column ORDER_NO enter the value ORDER_NO_@5 in to the column Fixed Value. When you execute your migration job then Order_Coordinator_Group_API.Increase_Cust_Order_No will be executed and fetches the next number depending on the Courdinator Group Id (and also stores this next number) and inherit this next order_no to the order_no of the view customer_order. The order_no shouldn’t be mapped in the source mapping.

 

 

You can also inherit this fetched order_no to your customer_order_line but as I wrote please ensure that your migration job is also able to update existing records where there are several posibilities available. At the moment my example is not able to update existing customer orders. ;-)

 

/Jens

Reply


Terms & ConditionsCookie settings