Referencing the thread https://community.oracle.com/thread/2603586
Is it possible to add search of Cross Reference system id on TRADEXREF for Trade specs like I did for Storage Method and Description?
Ive looked through the schema but having trouble figuring this one out.
Cross reference information is stored in specLegacySpecJoin and specLegacyProfile tables.
You can also configure a conditional expression that will force the search to be performed only against references with system id of TRADEXREF.
Here is an example,
<ExtendEntityModel extends="Main Trade Specification">
<TableAlias tableName="specLegacySpecJoin" alias="trdxrefSpecJoin"/>
<TableAlias tableName="specLegacyProfile" alias="trdxref" />
<TextProperty caption="TRADEXREFSEARCH" name="TRADEXREFSEARCH" forSearching='Yes' isTranslateable='No' isFreeTextLanguage='No' isUnicode='No'>
<TextCriterionOperations startsWith="On" contains="On" />
gsmBaseTradeSpec.pkid = trdxrefSpecJoin.fkSpecID
trdxrefSpecJoin.fkLegacyProfileID = trdxref.pkid
trdxref.SystemCode = 'TRADEXREF'
Here I was using TableAlias property to reference my table by an alias. This helps with proper SQL generation to avoid table collisions when another search criteria is used that involves the same tables.
You can also remove the second JoinLink and use a pkid representing your cross reference system (from specLegacyProfile table) and use trdxrefSpecJoin.fkLegacyProfileID = 'pkid from specLegacyProfile table' as your conditional expression. This will help to speed up the search.
Another way to solve this problem is by using a view. You can create a view in your database that will contain the aforementioned condition and use the view in your join link.