Forum Stats

  • 3,873,510 Users
  • 2,266,597 Discussions
  • 7,911,559 Comments

Discussions

HFM - Get current user

3590471
3590471 Member Posts: 10
edited Mar 21, 2018 12:51PM in Financial Consolidation

Hello,

I'm writing an on-demand routine in HFM that extracts data in a particular format and sends it by email.

Right now email addresses are hard-coded in the script.

I'd like to send the email only to the user who executed the on-demand routine, and I could derive the email address from the user name.

How can I retrieve the name of the user who executed my routine?

I guess it comes down to getting user session data.

Thank you very much in advance for your help.

Alexis

Tagged:

Answers

  • CBarbieri
    CBarbieri Member Posts: 1,011 Gold Trophy
    edited Jan 17, 2018 9:02AM

    I don't believe this is visible to rules. The user makes a request of the server to run rules (consolidate, calculate, etc.) and the HFM system user executes the rules so at that point rules have context for the interactive user.

    I suppose you could ask users to enter their name in cell text in the form and leverage that? But there is no function I'm aware of that passes the user context to rules.

    - chris

  • 3590471
    3590471 Member Posts: 10
    edited Jan 17, 2018 11:03AM

    Hi Chris,

    Thanks for your answer. That makes sense, indeed I believe user isn't accessible from rules. For example doing CreateObject("wscript.network").Username returns the service account and not the current user being logged in.

    I will explore the option of email addresses collected from CT.

    That being said, HFM is able to send popup messages for some functionalities, for example "extract successful" when extracting app elements. Or some HTP custom modules returns messages when copying tax automation rules from a period to the next.

    So there's got to be a way to interact with the client machine, hence with the current user. But it probably stays in html layer. Was just wondering if I could grab that data from rules.

    Thanks again,

    Alexis

  • ericerikson
    ericerikson Member Posts: 608 Bronze Trophy
    edited Jan 18, 2018 5:05PM

    Hi. Your best bet is to do what Chris mentioned with the cell text. Have users enter their email address(es) into the cell text, then have the rule create the extract and email it there and lastly clear the cell text for the next entry.

    Eric

  • 3590471
    3590471 Member Posts: 10
    edited Jan 18, 2018 5:08PM

    Will take that route, thanks a lot for your response.

    Alexis

  • amith.madisetty
    amith.madisetty Member Posts: 869 Gold Badge
    edited Jan 26, 2018 8:30AM

    Is there a way where it queries audit records and send an email from  your script to that user..  May be have some mapping table against username or ID.

    Not sure how much overhead it causes on database but I don't think so.

    Thanks

    Amith

  • 3590471
    3590471 Member Posts: 10
    edited Feb 14, 2018 3:22PM

    Amith,

    Just realized I didn't answer to your post.

    Thank you for your suggestion. That would be a smart approach, I can definitely query HFM logs but I'm just not sure how to get DB credentials in HFM without disclosing them explicitly.

    There are ways like Oracle Wallet but I'm not familiar with those technologies.

    Best,

    Alexis

  • 3590471
    3590471 Member Posts: 10
    edited Mar 20, 2018 9:17PM

    Hello,

    Just following up on my question as I found a hopefully efficient way to dynamically email current user(s).

    For those who are interested:

    SELECT up.USERNAME, ucss.EMAIL, up.PARAMETERKEY, replace(utl_raw.cast_to_varchar2((up.BLOBDATA)), chr(0), '') as module

    FROM HYP_HFM.UBRHFM_USERPARAMS up

      INNER JOIN HYP_HFM.HSV_ACTIVITY_USERS ua on ua.SUSERNAME = up.USERNAME

      INNER JOIN HYP_HFM.HSV_USERS_ON_SYSTEM us ON us.LUSERID = ua.LUSERID

      INNER JOIN HYP_SS.CSS_USERS ucss ON ucss.NAME = SUBSTR(ua.SUSERNAME, 1, INSTR(ua.SUSERNAME, '@', 1) - 1)

    WHERE up.PARAMETERKEY = 'LAST_ACTIVE_MODULE_INFO'

    Here my SQL connections are "HYP_HFM" for the HFM database and "HYP_SS" for the Shared Services database, and my app is "UBRHFM".

    But I guess the rest is generic and would work for you.

    This query will return:

    • Users currently logged to your HFM application.
      • Here the trick is the "HSV_USERS_ON_SYSTEM" table. It does 80% of the job for you.
    • Their email address (as long as specified in Shared Services).
      • Here you need a join to your Shared Services database.
      • To keep it simpler, if email addresses are [email protected], you can skip that part and simply concatenate with the @domainName.
    • "Parameter key", i.e. type of user parameter.
      • Here the trick is to set this field to "LAST_ACTIVE_MODULE_INFO", to return current user activity.
    • "Module":
      • This returns the "BLOBDATA" column from the "<appName>_USERS_PARAMS" table.
      • It converts the content from hexadecimal into ASCII, to make it intelligible.

    Then the trick is the following:

    • The assumption is that your rules include an on-demand routine that ultimately sends something by email. For me it's a data extraction.
    • The other assumption is that your on-demand rule is triggered from certain web form(s).
    • By scanning the records returned by the query, you can find which user(s) is / are logged to that / those web form(s).
      • Actually the web form(s) name(s) could even be embedded directly in the SQL query, as part of an additional WHERE clause.
    • You get the user(s), you get the email address(es), since it's already available in the query.

    Now working on embedding that SQL code into HFM rules.

    Best,

    Alexis

  • 3590471
    3590471 Member Posts: 10
    edited Mar 20, 2018 9:25PM

    Amith,

    To provide a more specific answer, I did try to query the <appName>_TASK_AUDIT table.

    It provides good results but:

    • You still need to join with the HSV_USERS_ON_SYSTEM table to make sure you get only current users.
    • It returns a POV which is great, but it doesn't tell you which web form / grid.

    Also, this wouldn't work if task audit is not enabled in your app.

    Best,

    Alexis

  • amith.madisetty
    amith.madisetty Member Posts: 869 Gold Badge
    edited Mar 21, 2018 12:19PM

    Thanks Alex for this.

    Hyperion World: Relational Database Tables in HFM/FDM and queries which help in information required

    Probably have a look at this, this was old post i published and completely forgot, recalled after looking at your query, it might be of some help in checking other areas.

    Cheers!

    Amith

  • 3590471
    3590471 Member Posts: 10
    edited Mar 21, 2018 12:51PM

    Thank you Amith, very helpful!

    Alexis

This discussion has been closed.