2 Replies Latest reply on Jul 22, 2017 5:03 PM by Srini Chavali-Oracle

    Need help in parsing the Query in profile option definition window

    CRF_1000_L-Oracle

      Hi,

       

      We are trying to write a script to derive the profile option value (to be set) from the visible profile option value. We are looking at the option of parsing the query in profile option definition window to get the displayed value and derived value. A few examples of the queries are as follows:

       

      MO: Operating Unit

       

      SQL="SELECT ORGANIZATION_ID, NAME

      INTO :PROFILE_OPTION_VALUE, :VISIBLE_OPTION_VALUE

      FROM HR_OPERATING_UNITS"

      COLUMN="NAME(*)"

       

      HR: Security Profile

       

      SQL="SELECT   S.SECURITY_PROFILE_NAME \"Security Profile\",

                                 S.SECURITY_PROFILE_ID,

                                 'PER_BUSINESS_GROUP_ID',

                                 S.BUSINESS_GROUP_ID,

                                 O.NAME \"Business Group\"

                                 INTO   :VISIBLE_OPTION_VALUE,

                                             :PROFILE_OPTION_VALUE,

                                             :DERIVED_NAME,

                                             :DERIVED_VALUE,

                                             null

                                 FROM  PER_SECURITY_PROFILES S,

                                             PER_BUSINESS_GROUPS O

                                 WHERE O.BUSINESS_GROUP_ID = S.BUSINESS_GROUP_ID

                                 ORDER BY S.SECURITY_PROFILE_NAME"

           COLUMN=" \"Security Profile\"(*),  \"Business Group\"(*)"

           HEADING=" \"*PER:HR_ML_SECURITY_PROFILE\"(*),  \"*PER:HR_ML_BUSINESS_GROUP\"(*)"

       

       

      This seems pretty complex to parse. Has anybody attempted to do the same? Any input is appreciated.

       

      NB: The basic requirement we are trying to achieve is as mentioned in this thread: Need help in uploading profile option values

      Here, we are looking at deriving the actual value to be passed to the API to set profile option. So fnd_profile.value will not work.