Skip to main content

Hello - we are on version 9, update 8 and would like to mass update the price & price incl tax fields on the sales part record.  We did notice there is the ability to “Update base prices” & “Update unit based price lists” & “Update sales price lists from base prices”, but there does not seem to ability to update prices on sales parts.  Has anyone else run into this and what was the solution?  This seems like a common request that any company would like to do.

 

 

Thanks!

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.


Hi

Depends also little bit how you like to change price, is it % or do you have new fixed prices. And how much parts you have.

If you have some fixed rules how to update price, one option is to create RMB functionality what use plsql, there you can create rules to change price for selected lines.

If you have totally new prices in excel without clear logic, migration job or external files would be easiest, as proposed above.
Easiest way could be to use new excell migration add-in, if you have it. (I don’ t remember official name)

Some things can be done also using custom fields and custom Lu’s, if eg need some checks before update. Or you can calculate new prices there and/or made manual adjustments.
Or using copy object (eg to excel), change values, and paste object. This works with Custom page


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.


If you use data migration, you will have the experience to update other data using the migration job. There are 2 jobs should be setup, 1st job should read csv file and put it into oracle table (procedure CREATE_TABLE_FROM_FILE), then the second job read the temporary table into the sales part, use procedure MIGRATE_SOURCE_DATA, this uses iFS standard method to update the price, or in fact other fields you need to update in bulk.


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. 


Agree that the excel migration is a very good tool with little space for mistakes but I does have its limitations and processing is not as fast as migration from temp table as described by Dedi Tjong. 
updating +1000 parts in excel is going to take some time. 


Reply