Database columns deleted (officially) during past upgrades (e.g., PROJSITE in PPA_WIP)

[pjromano]

The database column PROJSITE was deleted from PPA_WIP as per the 7.5.3 to 8.1 change impact and upgrade guide.  However, we have a 14.2 install (not fresh, it started as 7.5 once upon a time, and has gone through several upgrades), and the database column is still there, though it is NULL for all records.  Is this expected behavior?  If so, will repurposing this field to store custom transaction information be a risky proposition?

 

I would assume that fresh installs of 8.1 or later don't have this field, but that upgraded installs still retain it - as well as others that were documented as "deleted" in a prior upgrade.

[Nika Hadzhikidi]

Hi Peter,

I have verified and indeed the column should have been deleted when going from 7.5.3 to 8.1. I checked on another environment that was upgraded from early versions <7.5 and I do indeed see this field and it's filled with NULLs, which is expected.
I will raise a defect with the installer so we can get the column removed as it should be. Thanks a lot for letting us know.

 

Concerning your question to actually use this column : I would not personally recommend you to use it for custom purposes since this is something that was not supposed to be there in the first place, and any upgrade can just reset the table back to default and make you lose this information.

If you need custom fields on transactions that you do not currently have available, the best practice is to actually create them in a custom separate table. Adding custom fields to OOTB tables is strongly discouraged, as it may lead to issues with upgrades, data loss etc.

 

 

I hope this helps : Please let me know for any questions.

 

Kind Regards

 

Nika Hadzhikidi
CA Technologies
Principal Support Engineer

[pjromano]

Thanks Nika.  We wouldn't ever add custom fields to an OOTB table, but, sometimes we do find places to use existing OOTB fields for our own purposes. 

 

What we're trying to do is find a place to store an external PO # for each transaction that comes in from our ERP system (we don't use Clarity Timesheets), so, we are trying to find a logical place to store that value (we already use NOTES for something else and User Value 1 and User Value 2 have legacy data in them that we used up until March of this year, that we don't want to overwrite).

 

We looked into use INVOICENO but it looks like getting it there would  be a little more complicated because of how that field really works with transaction processing within the application. 

 

If you have any suggestions, I'm open to it, otherwise, you're right, we may have to create a custom object simply to store that info.

[Nika Hadzhikidi]

Hi Peter,

These two fields seem to be what you are looking for, if you're not using Timesheets:

 

EXTERNALTRANSNO    NUMBER      YES     For a transaction that is from a time entry, this refers to PRTIMEENTRY.PRID. For imported transactions, this could be a unique ID from an external system.            

EXTERNALBATCHID VARCHAR2   20     YES    The External batch ID as specified in the XOG or timesheet. When a timesheet is posted, EXTERNALBATCHID=PRTIMESHEET.PRID and the EXTERNALTRANSNO field is blank.    

 

Please check them out and see if that could be a good solution for you.

For PROJSITE column : FYI I raised a defect:
CLRT-79212 PPA_WIP.PROJSITE column does not get deleted when upgrading from 7.5.3>8.1, and column remains in the database when upgraded to 14.x

Please let me know for any questions.

Kind Regards

 

Nika Hadzhikidi
CA Technologies
Principal Support Engineer

[pjromano]

Thanks, Nika.