Mass update of Price on Sales Part record

You are correct, there is no easy button for updating these fields. You do have a couple semi-easy options available to you. The solution will depend on your PL/SQL and Excel skills.

Option 1 - Data Migration Job

Requires experience with the Data Migration module and setup of the jobs.

Option 2 - PL/SQL

Step 1: Dump the CATALOG_NO, CONTRACT, LIST_PRICE, and LIST_PRICE_INCL_TAX into Excel.

Step 2: Update the spreadsheet values.

Step 3: Add a new column to the data that creates a text string that includes all the character values with a single quotation mark at the beginning and end separated by a comma. For example, “'BP1', '1',200.01,200.01”. These lines will be used as data in a PL/SQL anonymous block to call an IFS procedure to update the values. Now add some static text to your values so that you new column value looks like “t_SALES_PART.EXTEND; t_SALES_PART(t_SALES_PART.LAST) := sales_part_info('BP1','1',200.01,200.01);”.

Step 4: Use the Excel data to replace the lines where the init array comment is.

Here is a sample anonymous block that can be used to update the values. The sample code below updates 2 parts; BP11 and BP1. When you execute the block you’ll receive an output line for the total number of records to process and any part that ran into an error. This code uses the IFS procedures to process the records, so it follows the object rules you have established in IFS.

declare
 attr_ VARCHAR(32000) := '';
 contract_ VARCHAR2(5) := '';
 catalog_no_ VARCHAR2(25) := '';

 list_price_ NUMBER := 0;
 list_price_incl_tax_ NUMBER := 0;
 total_ NUMBER := 0;

 TYPE r_SALES_PART is RECORD(
 CATALOG_NO SALES_PART_TAB.CATALOG_NO%TYPE,
 CONTRACT SALES_PART_TAB.CONTRACT%TYPE,
 LIST_PRICE SALES_PART_TAB.LIST_PRICE%TYPE,
 LIST_PRICE_INCL_TAX SALES_PART_TAB.LIST_PRICE_INCL_TAX%TYPE
 );

 type t_SALES_PART_type is VARRAY(10000) of r_SALES_PART;
 t_SALES_PART t_SALES_PART_type := t_SALES_PART_type();

 function sales_part_info(catalog_no_ IN VARCHAR2, contract_ IN VARCHAR2, list_price_ IN NUMBER, list_price_incl_tax_ IN NUMBER) return r_SALES_PART
 is
 l_sales_part r_SALES_PART;
 begin
 l_sales_part.catalog_no := catalog_no_;
 l_sales_part.contract := contract_;
 l_sales_part.list_price := list_price_;
 l_sales_part.list_price_incl_tax := list_price_incl_tax_;

 return l_sales_part;
 end;
begin
 -- Init the array and expand it. One line per part please. 
 t_SALES_PART.EXTEND; t_SALES_PART(t_SALES_PART.LAST) := sales_part_info('BP11','1',600.01,600.01);
 t_SALES_PART.EXTEND; t_SALES_PART(t_SALES_PART.LAST) := sales_part_info('BP1','1',200.01,200.01);

 total_ := t_SALES_PART.count;
 dbms_output.put_line('Processing ' || total_ || ' records.');

 for rec in 1..total_ loop 
 catalog_no_ := t_SALES_PART(rec).catalog_no;
 contract_ := t_SALES_PART(rec).contract;
 list_price_ := t_SALES_PART(rec).list_price;
 list_price_incl_tax_ := t_SALES_PART(rec).list_price_incl_tax;

 --dbms_output.put_line(rec || ') Processing Contract: ' || contract_ || ', CatalogNo: ' || catalog_no_);

 BEGIN
 Client_Sys.Clear_Attr(attr_); 
 Client_Sys.Set_Item_Value('LIST_PRICE', list_price_, attr_);
 Client_Sys.Set_Item_Value('LIST_PRICE_INCL_TAX', list_price_incl_tax_, attr_);

 Sales_Part_API.Modify(contract_, catalog_no_, attr_);
 commit;

 EXCEPTION
 WHEN OTHERS THEN
 dbms_output.put_line('Could not updated Contract: ' || contract_ || ', CatalogNo: ' || catalog_no_);
 END; 
 end loop;

end;

I look forward to your comments and review.

My advice would be to use migration jobs. Maybe an Excel migration job, that would allow you to update directly. It would mean making a connection to IFS from Excel. Search for the parts that should be updated. Change the price in the retrieved data and update the data. That allows you fixed and percentage price changes.

We’re using the excel migration tool for this. It is fairly simple for to set up new excel migration jobs that superusers / master data managers can then use for mass maintenance. We keep finding places where this tool is applicable. 

A great thing about the tool is, that all updates are going through standard APIs and there’s a separate access control on using the excel migration tool and access control on the individual jobs. No need for shady update scripts and IT being bottle neck on executing data maintenance.