This is the story of how to update the item category set on pricelist modifiers. In order to do that you must inactivate all modifiers, change the pointer to the new category set and create a new complete set of modifiers using the new category set.
We started with the xls4oracle template “Load Pricelist Modifiers”. We needed to handle a new item category structure, so the structure_id in the plsql was updated.
The use of the execute column was handy, there was a lot of exclusions added on the create header level.
A second sheet is used for inactivate and end date qp_list_headers. The sheet is extracting all headers and only updating the old ones (On dev env. several load versions was made before the final version).
The final version was script generated and loaded using multiple concurrent requests. We had 3 simultanious requests running and measured a load of 30000 qp lines per hour – we expect the prod. env. will be faster. The 60000 qp loads was split into scripts with around 10000 lines – enabling up to 6 parallel loads. By adding a “load sequence” column, the script file generation was easy: mark the lines into 6 groups and copy the “load sequense” column into the load column and execute Dataload 6 times, generating 6 script files. The load column will do nothing with “0”‘s, all other will be reduced by 1 and only “1”‘s will be executed. The modifiers is validated on the dev. env. and then loaded to prod. If the validation fails, another load can be prepared and executed quickly.
The load to prod is now completed. We where able to load 6 load scrips in parallel, so the 60000 modifiler line load was completed in 1 hour.
Next time we will load to prod with inactivated headers, then validate the loads and finaly activate. This way we can do the prod validation without stress.