This discussion is archived
6 Replies Latest reply: Mar 16, 2013 9:52 AM by igwe RSS

using custom value sets for special information type AME rules

igwe Newbie
Currently Being Moderated
Hi, I have custom value sets that I am using with my special information types. I have enabled these special information types in self service and have attached approval management rules to them.
Here is my problem, some of the value sets are sql based, and are meant to get information about the requester. so I am using fnd_profile.value('user_id') a lot.
For example,
select email_address from fnd_user where user_id = fnd_profile.value('user_id')

But when the sit is sent for approval, any field using these custom value sets are missing. the only fields shown are the ones not using value sets with user_id profile as part of the query.
My question is, is there a better way to retrieve employee information and thus be able to avoid this error?
Many thanks,
igwe
  • 1. Re: using custom value sets for special information type AME rules
    GiuseppeBonavita Journeyer
    Currently Being Moderated
    Hi Igwe,

    for the email address specific case, maybe you could remove the valueset validation, set a default value based on the query, and make the field read only, although I recall some difficulties defaulting read only dff.

    or maybe you could try changing the valueset to something like:

    select email_address from fnd_user where user_id = fnd_profile.value('user_id')
    union
    select varchar2_value from hr_api_transaction_values where <<very smart condition here identifying records submitted by user_id>>


    cheers
    Giuseppe
  • 2. Re: using custom value sets for special information type AME rules
    igwe Newbie
    Currently Being Moderated
    haha Giuseppe, the second query is exactly where I am stuck. It's fine when you view the notification from the users page, but when it goes for approval fields start to disappear. I'm thinking the self service page is checking the value set each time to make sure the record is valid.
    Funny thing is, when one of the fields matches for the same employee (say approver and requester have the same phone number, the phone number is displayed and every other field is left blank)

    Not sure how to get a transaction id for the current oaf page. If I could do that, it would be a simple matter of:

    hr_ameutil_ss.get_item_type (transaction_id)
    hr_ameutil_ss.get_item_type (transaction_id)

    then:

    wf_engine.getitemattrtext(item_type, item_key 'APPROVAL_CREATOR_DISPLAY_NAME') (or whatever attribute I can use)

    and your union statement

    I'm not able to find any link at the moment.
    Also I cannot make the field readonly since the user can select from more than one email address.

    But many thanks, will see how it goes
  • 3. Re: using custom value sets for special information type AME rules
    igwe Newbie
    Currently Being Moderated
    Hi Giuseppe,
    I was thinking, is there a value set I can define that is not validated.
    say the query could be select address_id from per_address where person_id = fnd_global.user_id
    However, this is not validated. so that when the sit goes for approval, even if it picks the wrong user_id the value will remain?
  • 4. Re: using custom value sets for special information type AME rules
    GiuseppeBonavita Journeyer
    Currently Being Moderated
    not sure about an not validating LOV, but you can have - that was the first suggestion - no validation at all, but have a default value linked to the user id
  • 5. Re: using custom value sets for special information type AME rules
    igwe Newbie
    Currently Being Moderated
    oops, that failed quickly:

    The data that defines the flexfield on this field may be inconsistent. Please inform your system administrator that the value set XXKSA_EMP_BUS_CARD_EMAIL has an incorrect validation type (U).

    I'm suprised oracle didn't think about this scenario

    By the way, it can't be a fixed value because I am meant to get the employees address, phone number and email address, there can be multiple instances of these

    I'm going to try using an extra information type for this. come to think of it, that is more reasonable since the same request for a business card can prob. be made over and over again.

    Edited by: igwe on Mar 15, 2013 4:45 AM

    Edited by: igwe on Mar 15, 2013 4:55 AM
  • 6. Re: using custom value sets for special information type AME rules
    igwe Newbie
    Currently Being Moderated
    scratch my initial query, the logic is to use, fnd_profile.value('PER_PERSON_ID') instead of fnd_profile.value('USER_ID').
    I'm still in shock at how easy it is:


    Edited by: igwe on Mar 15, 2013 11:33 AM

    Edited by: igwe on Mar 16, 2013 9:50 AM

Legend

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