Reporting and Analytics for ERP

Get Involved. Join the Conversation.

Topic

    Nidhi Chhajed
    Subject Area in OTBI to extract default Expense Account...
    Topic posted March 7, 2019 by Nidhi ChhajedBlue Ribbon: 750+ Points, tagged OTBI 
    177 Views, 3 Comments
    Title:
    Subject Area in OTBI to extract default Expense Account information of user
    Summary:
    Subject Area in OTBI to extract default Expense Account information of user
    Content:

    Hi,

    I am trying to build a OTBI report which has cost center and company information from users default expense account.

    I used subject area Workforce Management - Work Relationship Real Time --> folder Department --> GL Cost Center Information: Cost Center.

    But for 1 user although the default expense account is assigned it is giving null value and for another it is showing wrong value. Hence it seems it gives cost center at department level

    Do we need to use any other subject area in Finance side to fetch this information.

    Regards

    Nidhi

    Comment

     

    • MShrini

      Hi Nidhi,

       

      You can you use the below subject are to fetch the employee default account with cost center and other details.

       

      Expenses Subject Areas, Folders, and Attributes: Explained

       

      Best Regards

      MSrini.

    • Wendy Ware

      Perhaps I'm missing something, but I was unable to find the employee default expense account in subject areas.  I use the following SQL to report on that.  Wendy

       

      select

      pu.USERNAME,

      GL.SEGMENT1||'.'||GL.SEGMENT2||'.'||GL.SEGMENT3||'.'||GL.SEGMENT4||'.'||GL.SEGMENT5||'.'||GL.SEGMENT6||'.'||GL.SEGMENT7||'.'||GL.SEGMENT8||'.'||GL.SEGMENT9||'.'||GL.SEGMENT10 "DEFAULT EXPENSE"

      ,paex.EXPENSE_CHECK_ADDRESS "CHECK ADDRESS"

      from

      per_users pu

      join per_employees_x paex on pu.person_id = paex.person_id

      LEFT join GL_CODE_COMBINATIONS GL on GL.CODE_COMBINATION_ID = paex.DEFAULT_CODE_COMB_ID

       

       

      • Meshack Rakumakoe

        We use 9 segments in our CoA. I have adapted your query to create a data model in Fusion. The query I use is:

         

        SELECT papf.person_number,

        hauft.NAME LegalEmployer,
        peru.USERNAME,
        GL.SEGMENT1||'.'||GL.SEGMENT2||'.'||GL.SEGMENT3||'.'||GL.SEGMENT4||'.'||GL.SEGMENT5||'.'||GL.SEGMENT6||'.'||GL.SEGMENT7||'.'||GL.SEGMENT8||'.'||GL.SEGMENT9 as "Default_Expense",
        pern.FULL_NAME
         
        FROM    HR_ORG_UNIT_CLASSIFICATIONS_F houcf, 
        HR_ALL_ORGANIZATION_UNITS_F haouf, 
        HR_ORGANIZATION_UNITS_F_TL hauft,
        per_all_assignments_m paam,
        per_all_people_f papf,
        PER_USERS peru,
        per_employees_x paex,
        GL_CODE_COMBINATIONS GL,
        PER_PERSON_NAMES_F_V pern
         
        WHERE    haouf.ORGANIZATION_ID = houcf.ORGANIZATION_ID 
        and haouf.ORGANIZATION_ID = hauft.ORGANIZATION_ID 
        and haouf.EFFECTIVE_START_DATE BETWEEN houcf.EFFECTIVE_START_DATE and houcf.EFFECTIVE_END_DATE 
        and hauft.LANGUAGE = 'US'
        and hauft.EFFECTIVE_START_DATE = haouf.EFFECTIVE_START_DATE
        and hauft.EFFECTIVE_END_DATE = haouf.EFFECTIVE_END_DATE 
        and houcf.CLASSIFICATION_CODE = 'HCM_LEMP' 
        and SYSDATE BETWEEN hauft.effective_start_date and hauft.effective_end_date
        and hauft.organization_id = paam.legal_entity_id
        and paam.person_id = papf.person_id
        and paam.primary_assignment_flag = 'Y'
        and paam.assignment_type = 'E'
        and paam.effective_latest_change = 'Y'
        and sysdate between paam.effective_start_date and paam.effective_end_date
        and sysdate between papf.effective_start_date and papf.effective_end_date
        and peru.person_id = paex.person_id
        and GL.CODE_COMBINATION_ID (+)= paex.DEFAULT_CODE_COMB_ID
        and peru.person_id = pern.person_id
        and peru.person_id = paam.person_id
        ---------------------------------Prompts--------------------------------------------------------------
        AND   UPPER(peru.username) like NVL(UPPER(:P_USERNAME), '%')
        AND   UPPER(hauft.NAME) like NVL(UPPER(:P_LEGAL_EMPLOYER), '%')
        ------------------------------------------------------------------------------------------------------
         
        order by pern.FULL_NAME