Majority of objects in Clarity have an unique ID - for example, inv_investments.id, odf_ca_cop_prj_statusrpt.id. It is a unique field that Clarity creates (generally refereed to as the 5000000 number as all the separate ID's in Clarity will start with 5000000).
I recently had build logic using this ID on a custom sub-object on Project. It wasn't the Status Report (cop_prj_statusrpt) but as most of you are used to this object, I will use this as the example. To determine the first Status Report created on the Project, I was using min(id), and to find the latest Status Report created on the Project, I was using max(id) Something along the line:
select inv.code,
rfirst.name first_report_name,
rlast.name last_report_name
from inv_investments inv
left join odf_ca_cop_prj_statusrpt rfirst on rfirst.odf_parent_id = inv.id and rfirst.id = (select min(id) from odf_ca_cop_prj_statusrpt where odf_parent_id = inv.id)
left join odf_ca_cop_prj_statusrpt rlast on rlast.odf_parent_id = inv.id and rlast.id = (select max(id) from odf_ca_cop_prj_statusrpt where odf_parent_id = inv.id)
This was all working in our initial tests in DEV, but when migrated to PROD, and after 2 months use, we discovered that the ID's were not sequentially allocated.
As an example from our PROD environment:
select id, created_date from inv_investments
order by created_date desc
Returns
5,014,008 2018-01-04 15:06:52.0
5,013,009 2017-12-18 14:18:41.0
5,013,008 2017-12-12 18:10:52.0
5,013,007 2017-12-12 18:06:43.0
5,013,006 2017-12-12 17:33:26.0
5,013,005 2017-12-12 16:30:52.0
5,014,007 2017-12-12 14:19:19.0 -- OUT OF ORDER
5,013,004 2017-12-12 13:57:26.0
5,014,006 2017-12-11 18:47:18.0
This is potentially due to the SaaS environments using Oracle RAC and if the id creation is using NEXTVAL, then each Oracle Server may have different caches for NEXTVAL (just a guess).
In the above case, the code was modified to use RANK() OVER (PARTITION BY odf_parent_id ORDER BY created_date DESC ) RANK and search where RANK = 1 (which will return the last record created, and to find the first one, sort created_date ASC).
Alternatively, I could have also created a new attribute, and have it auto-generated, which will be sequential. How Clarity auto-generated these numbers, I can't comment, but so far, I have not seen them being non-sequential like the ID's.