1 Reply Latest reply: Jan 10, 2014 5:47 PM by Dmitriy Kolasnikov RSS

Search on Tradexref

Trey Explorer
Currently Being Moderated


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.

  • 1. Re: Search on Tradexref
    Dmitriy Kolasnikov Explorer
    Currently Being Moderated

    Hi Trey,

     

    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'>

           <FieldName>

                trdxrefSpecJoin.Equivalent

           </FieldName>

           <TextCriterionOperations startsWith="On" contains="On" />

           <JoinLinks>

                 <Link>

                      gsmBaseTradeSpec.pkid = trdxrefSpecJoin.fkSpecID

                 </Link>

                 <Link>

                     trdxrefSpecJoin.fkLegacyProfileID = trdxref.pkid

                  </Link>

            </JoinLinks>

         <ConditionalExpressions>

              <Expression>

                    trdxref.SystemCode = 'TRADEXREF'

              </Expression>

          </ConditionalExpressions>

      </TextProperty>

    </ExtendEntityModel>

    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.

     

    Dmitriy

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points