Financials – General

Get Involved. Join the Conversation.

Topic

    angela ahrens
    created_by or last_updated_by - how to find username on...
    Topic posted October 15, 2019 by angela ahrensRed Ribbon: 250+ Points 
    26 Views, 7 Comments
    Title:
    created_by or last_updated_by - how to find username on those fields
    Summary:
    created_by or last_updated_by - how to find username on those fields
    Content:
    in gl.created_by and gl.last_updated_by I get a number or email address. How do i get the user name?
     
    SELECT gl.segment1||'-'||gl.segment2||'-'||gl.segment3||'-'||gl.segment4||'-'||gl.segment5||'-'||gl.segment6||'-'||gl.segment7||'-'||gl.segment8 code_combination, gl.enabled_flag, gl.last_updated_by, TO_CHAR(gl.last_update_date,'MM-DD-YYYY:HH24:MI') last_update_date,gl.CREATED_BY,TO_CHAR(gl.CREATION_DATE,'MM-DD-YYYY:HH24:MI') Creation_date
    FROM GL_CODE_COMBINATIONS gl 
    WHERE 1=1
    AND TRUNC(gl.last_update_date) BETWEEN TRUNC(TO_DATE(NVL(:P_FROM_DATE,'01-01-0001'),'MM-DD-YYYY')) AND TRUNC(TO_DATE(NVL(:P_TO_DATE,'01-01-0001'),'MM-DD-YYYY')) 

    Comment

     

    • Julien Dubouis

      Hi Angela,
      You should try joining PER_PERSONS and/or PER_USERS. 
      Thanks

    • angela ahrens

      yep,I tried... what do i join with..  created_by and last_updated_by varchar64(this can have email address or a number) and per_users - user_id num 18

      • Julien Dubouis

        Hi,
        Last_updated_by and created_by already show you the username.

        If you want to have other pieces of information related to the worker, you can join per_users which contain the person_id (assuming your users are also created as employees). 

        Sample code :
        SELECT 
        gl.segment1||'-'||gl.segment2||'-'||gl.segment3||'-'||gl.segment4||'-'||gl.segment5||'-'||gl.segment6||'-'||gl.segment7||'-'||gl.segment8 code_combination
        , gl.enabled_flag
        , gl.last_updated_by
        , TO_CHAR(gl.last_update_date,'MM-DD-YYYY:HH24:MI') last_update_date
        ,gl.CREATED_BY
        ,TO_CHAR(gl.CREATION_DATE,'MM-DD-YYYY:HH24:MI') 
        ,ppn1.first_name
        ,ppn1.last_name
        FROM GL_CODE_COMBINATIONS gl
        LEFT JOIN PER_USERS pu1 on pu1.username = gl.last_updated_by
        LEFT JOIN PER_PERSON_NAMES_F ppn1 on pu1.person_id = ppn1.person_id


        Thanks

    • angela ahrens

      no i tried but i have numbers and email addresses in that field for some reason..

       

    • angela ahrens

      i can get the weird number the number 2A4373EBD03B3551E050480A28850FE6 using the user_guid  but other numbers like 40554976.  Looks like a scheduler id but not sure what number that is.

    • angela ahrens
    • angela ahrens

      example of last updated by and number fields i can't find.