Hi @Noriro2,

You cannot change the inventory part number, the only way to “fix” will be to user the alternate part number.

@Noriro2 Inventory part is costing and planning level for each site, no system will allow to have part no change.

delete the inventory part no that you would like to change if there is no any transactions or costing attached to it, and then part no, engineering part no in sequence, otherwise, please create a new part no instead.

@Noriro2 It is not possible through the UI nor using the API. Only possibility, technically, is changing the PART NO from all the fields in the database tables (which is anyway not recommended and not a straight forward method also).

Hi, 

@gianni.neves , @ronhu , @Buddhika Kurera 

thank you for your response, i knew that the answer will be a big NO, but we as humans can make mistakes like setting wrong Part No and make transactions, so the only way is to change the Part No.

what would you do in this situation ?

Hi @Noriro2 ,

I’ve been already in this situation, and sometimes I could delete (there are no transactions for the part), and sometimes I need just ask people to create another part # or use the alternative part.

Thanks,

Gianni Neves

Hi @gianni.neves ,

i found @durette shared a sql (How do you change the DB appowner name? | IFS Community) file which search every column on every table for the value needed, i think this might be solution but takes time, i don’t have access to server so i couldn’t test with sqlplus.

Here is the example;

SET ECHO ON

SET SERVEROUTPUT ON
SET LINESIZE 32767
SET TRIMSPOOL ON
SET TAB OFF
SET TIMING OFF
SET FEEDBACK OFF
SET VERIFY OFF
SET PAGESIZE 0
SET ARRAYSIZE 5000

SET ECHO OFF
SPOOL _brute_force.sql

SELECT 'SELECT rowid FROM "' || utc.table_name ||
       '" WHERE "' || utc.column_name ||
       '" = ''##INVENTORY_PART_NO##'' /* rownum = ' ||
       ROW_NUMBER() OVER(ORDER BY utc.table_name, utc.column_name) ||
       '*/' ||
       CHR(13) || CHR(10) ||
       '/' AS sq
  FROM user_tab_cols utc
 INNER JOIN user_tables ut
    ON ut.table_name = utc.table_name
 WHERE utc.data_type IN ('VARCHAR2', 'CLOB', 'CHAR')
 ORDER BY 1;

SPOOL OFF

SET ECHO ON
SET FEEDBACK ON
SET ARRAYSIZE 1

SPOOL _brute_force_out.txt

@_brute_force.sql

SPOOL OFF

EXIT;

change ##INVENTORY_PART_NO## to desired value.

Result of this query will give you a very long list, you have to delete “no rows found” then you will have the tables which has the value you are looking for, then update all of them.

SET ECHO ON

SET SERVEROUTPUT ON
SET LINESIZE 32767
SET TRIMSPOOL ON
SET TAB OFF
SET TIMING OFF
SET FEEDBACK OFF
SET VERIFY OFF
SET PAGESIZE 0
SET ARRAYSIZE 5000

SET ECHO OFF
SPOOL _brute_force.sql

SELECT 'SELECT rowid FROM "' || utc.table_name ||
       '" WHERE "' || utc.column_name ||
       '" = ''##INVENTORY_PART_NO##'' /* rownum = ' ||
       ROW_NUMBER() OVER(ORDER BY utc.table_name, utc.column_name) ||
       '*/' ||
       CHR(13) || CHR(10) ||
       '/' AS sq
  FROM user_tab_cols utc
 INNER JOIN user_tables ut
    ON ut.table_name = utc.table_name
 WHERE utc.data_type IN ('VARCHAR2', 'CLOB', 'CHAR')
 ORDER BY 1;

SPOOL OFF

SET ECHO ON
SET FEEDBACK ON
SET ARRAYSIZE 1

SPOOL _brute_force_out.txt

@_brute_force.sql

SPOOL OFF

EXIT;

Hi, 

@gianni.neves @ronhu @Buddhika Kurera, what do you think about this?

No one human is going to know how to develop this intuitively because you have PART_NO, CUSTOMER_PART_NO, PURCHASE_PART_NO, SALES_PART_NO, TOP_PART_NO, PARENT_PART_NO, TOP_LEVEL_PART_NO, SUP_PART_NO, CLASSIFICATION_PART_NO, CATALOG_NO, and even things like INVOICE_ITEM_TAB.C5. If you’re going to attempt this, I recommend doing a brute force search as recommended above.

Hi @durette ,

Thank you for reply, my question is Doesn't PART_NO appear as PART_NO in every table? XXX_PART_NOs you specified above are different things as i know. I am not sure what is CATALOG_NO.

Invoiced items should not be touched as in INVOICE_ITEM_TAB.C5 but there should be a relation between new PART_NO and old invoiced PART_NO.

confused here.

Whether you want to change invoiced items all depends on the business need. What kind of mistake was made? (Was it so bad that your customers and suppliers recognized it was a mistake?)

CATALOG_NO is for the Sales Part. I suspect most companies use the same part number for Inventory Parts as they do for Purchase Parts and Sales Parts, so whether it’s pedantic to see these as separate things will depend on the business environment. Maybe you have an external sister company that isn’t inside IFS that uses CUSTOMER_PART_NO for certain use cases. It all depends!

COMPONENT_PART_NO is another one that I just realized will show up in Manufactured and Purchase structures.

Well, it could be a mistake or company decided to make the transition from old dummy PART_NO to smart PART_NO which wasn’t a case before, so there are tons of stocks, and items in production lines, this has to be done somehow.

I see, thank you for explanations.

No, not at all.

A Customer Address will have columns like CUSTOMER_ID and ADDRESS_ID, but once you go to the Order tab underneath that, that table will have columns like CUSTOMER_NO and ADDR_NO.

In recent updates, I’ve noticed IFS is starting to standardize column names, but they’re far from done.

Seems that they have standart in their perspective, _ID in main table means _NO in the table it is used, but only they know this, we as clients can’t figure it out.

That’s not a reliable pattern. In Customer Order, the ORDER_NO column contains the order number, while the ORDER_ID column contains the order type.

If you have Oracle Database access, you might try looking in DICTIONARY_SYS_VIEW_COLUMN_TAB and ALL_COL_COMMENTS.

It was my idea, i don’t know what they are thinking :), but you are right it wouldn’t be reliable. IFS is an old and huge company how they couldn’t standardize till now. Database looks complicated, there are many tables, and they serve for one thing, too much or maybe over normalizing, i guess.

I have access in working hours,  i will check tomorrow.

If a manufacturing company has more than a few hundred employees, then the people who make decisions about business software don’t actually use it, preferring instead to delegate reporting tasks. It’s very rare for a manufacturing company’s senior management to get promoted out of a cost center like IT, so most senior managers don’t acquire enough hands-on experience to understand that EVERY vendor’s software has glaring faults. If a vendor were to admit they had one, even if it’s not unique, it would be damaging to their marketing. This whole industry pretends that software works far better than it actually does, and nobody has any incentive to blow the whistle (apart from a handful of frustrated nerds like me).

Don’t believe me? Ok, Microsoft is a huge company, right? Windows still holds about 70% of the share of the desktop market. Take a Windows 10 machine with all the latest updates applied. Open PowerShell, then maximize the window. Now paste a block of commands containing a line break. The screen won’t paint correctly. This is an operating system with over a billion users globally, and it’s still broken.

Write-Host 'Line 1'
Write-Host 'Line 2'
Write-Host 'Line 3'
Write-Host 'Line 4'
Write-Host 'Line 5'
Write-Host 'Line 6'

If Microsoft can’t deliver a perfect product, I think it would be foolish to think IFS could.

You might think that support, standardization, and reliability would sell the product, but paradoxically, they can’t. Imagine if the IFS marketing department announced at a conference that the support is now much better and that the product is now standardized and reliable. It wouldn’t go over well! Customers would wonder why the support was previously poor or why the product wasn’t already standardized and reliable, and they’d be asking why it took IFS so long to get it there. A software vendor like IFS can’t announce an improvement if it implies the product or service didn’t previously meet such basic expectations.

Only flashy features like AI sell products anymore. This situation incentivizes software vendors to neglect the basics.

IFS is an old and huge company how they couldn’t standardize till now. Database looks complicated, there are many tables, and they serve for one thing, too much or maybe over normalizing, i guess.

If a manufacturing company has more than a few hundred employees, then the people who make decisions about business software don’t actually use it, preferring instead to delegate reporting tasks. It’s very rare for a manufacturing company’s senior management to get promoted out of a cost center like IT, so most senior managers don’t acquire enough hands-on experience to understand that EVERY vendor’s software has glaring faults. If a vendor were to admit they had one, even if it’s not unique, it would be damaging to their marketing. This whole industry pretends that software works far better than it actually does, and nobody has any incentive to blow the whistle (apart from a handful of frustrated nerds like me).

Don’t believe me? Ok, Microsoft is a huge company, right? Windows still holds about 70% of the share of the desktop market. Take a Windows 10 machine with all the latest updates applied. Open PowerShell, then maximize the window. Now paste a block of commands containing a line break. The screen won’t paint correctly. This is an operating system with over a billion users globally, and it’s still broken.

Write-Host 'Line 1'
Write-Host 'Line 2'
Write-Host 'Line 3'
Write-Host 'Line 4'
Write-Host 'Line 5'
Write-Host 'Line 6'

 

 

If Microsoft can’t deliver a perfect product, I think it would be foolish to think IFS could.

 

You might think that support, standardization, and reliability would sell the product, but paradoxically, they can’t. Imagine if the IFS marketing department announced at a conference that the support is now much better and that the product is now standardized and reliable. It wouldn’t go over well! Customers would wonder why the support was previously poor or why the product wasn’t already standardized and reliable, and they’d be asking why it took IFS so long to get it there. A software vendor like IFS can’t announce an improvement if it implies the product or service didn’t previously meet such basic expectations.

Only flashy features like AI sell products anymore. This situation incentivizes software vendors to neglect the basics.

Wow, you are full of it, and i totally agree with you, i have nothing to say on your thoughts, i am happy to encourage deplete of your thoughts, i had experience with couple of ERP software, one of them is the major player in an industry and they were never allow anybody to speak ill of their software but they are far from being able to handle a process from start to finish, instead they are releasing bunch of useless features to attract customers. I think this kind of things happens because of they want to address many industries, each industry has its own dynamics and software developers just can't handle it.

Apart from all this, not to get off topic, is this the only way to change PART_NO? searching every column in every table?, if someone make it an oracle package (API) for this it would be a great benefit to humanity :)