6 Replies Latest reply: Sep 19, 2013 12:10 PM by 917621 RSS

    Query on Lookups

    917621

      Hello all,

       

      I have a scenario

      where i need to fetch the data from fnd_lookup_values_vl table

      I have to fetch the data from four different lookup_types like 'Country_lookup', 'country_zone_lookup','Target_plan_lookup','Target_Code_lookup'

      How to fetch all the data from different lookup_types through a single query.

      Any suggestions please

       

      Thanks in advance

        • 1. Re: Query on Lookups
          Octopus Rex

          Well, don't kow exactly which information you are looking for but you can use query below.

          Just replace the LOOKUP_TYPE with the ones you need.

           

          SELECT  LOOKUP_TYPE,

                    LOOKUP_CODE,

                    ENABLED_FLAG,

                    START_DATE_ACTIVE,

                    END_DATE_ACTIVE,

                    MEANING,

                    DESCRIPTION

               FROM FND_LOOKUP_VALUES

               WHERE LOOKUP_TYPE IN ('ADDRESS_STYLE', 'AUDIT_STATE')

           

          Octavio

          • 2. Re: Query on Lookups
            917621

            I have tried using this scenario

            it fetches all the lookup_type data in common

            I want to show 'Country_lookup' lookup_code for country names

            'country_zone_lookup' lookup_code for country zones.

            How to figure it out in this case

            • 3. Re: Query on Lookups
              Octopus Rex

              Sorry but i do not understand what do you mean. The LOOKUP_TYPE column will be different for each lookup type...

               

              Octavio

              • 4. Re: Query on Lookups
                917621

                Hello,

                 

                 

                SELECT  LOOKUP_TYPE,

                        LOOKUP_CODE,

                        ENABLED_FLAG,

                        START_DATE_ACTIVE,

                        MEANING,

                        DESCRIPTION

                FROM FND_LOOKUP_VALUES

                WHERE LOOKUP_TYPE IN ('ADDRESS_STYLE', 'AUDIT_STATE','AUDIT_COUNTRY')

                 

                 

                 

                As each lookup_type will have different lookup_codes,meaning and description columns

                suppose LOOKUP_TYPE 'ADDRESS_STYLE' may have meaning like street name,'AUDIT_STATE' may have state names AND 'AUDIT_COUNTRY' holds country information

                When I am writing my query I want to get the columns street name from 'ADDRESS_STYLE',state name from 'AUDIT_STATE' lookup_type, country name from 'AUDIT_COUNTRY' in the same record.

                How can I do that

                 

                 

                Thanks

                • 5. Re: Query on Lookups
                  917621

                  Can someone please guide me how to find which column in specific table corresponds to the lookup_code in lookups table

                  As in the below stated example for 'HR_BONUS_PLAN' lookup_type the lookup_code joins to segment2 column in PER_JOB_DEFINITIONS table

                  Same way how to find the join for the other lookup_types

                   

                        SELECT * FROM FND_LOOKUP_VALUES FLV,PER_JOB_DEFINITIONS PJD
                        WHERE FLV.LOOKUP_TYPE LIKE 'HR_BONUS_PLAN'
                       

                  AND FLV.LOOKUP_CODE=PJD.SEGMENT2

                  • 6. Re: Query on Lookups
                    917621

                    Hello,

                     

                    When Lookup tables are used is this the only way to follow

                    Joining hr_lookups.lookup_code=xtable.ycolumn

                     

                    or any column can be picked from lookup tables can be joined to be destination table

                    like hr_lookups.meaning=xtable.ycolumn

                    hr_lookups.description=xtable.ycolumn

                     

                    Please reply

                     

                    Thanks