Expenses

Get Involved. Join the Conversation.

Topic

    Subhash Valiveti
    Job Level as a criteria of Expense Report Approvals
    Topic posted January 29, 2018 by Subhash ValivetiGold Medal: 3,500+ Points, last edited January 30, 2019, tagged Expenses, Financials 
    260 Views, 7 Comments
    Title:
    Job Level as a criteria of Expense Report Approvals
    Summary:
    How to define Expense approval rules when job level and ER amounts are the criteria
    Content:

    Hi,

    We are an on-premise 9.2 client where we have a requirement to define Expense Report approvals based on the ER amounts and Job Levels. We have amount limits set for each job level based on which the ERs have to be routed to the respective manager at the job level.

    We did not find job level as an attribute in the conditions section (IF clause).

    We are working on creating a DFF to capture the Job level through a trigger at the Expenses level. There is no DFF available at the Expense report level.

    The trigger at the expenses level is not the best option as it is a repeated one each of the expense line. This is creating DB locks when the expense report is either edited/modified by the users.

    Any suggestions regarding:

    1. How to define the approval rule to meet the requirement of Job level and amounts?
    2. If DFF and trigger is the option, what is the best way to write the trigger at the expenses level as there is no DFF at the expense report level?

    Regards,

    Subhash

    Comment

     

    • Jyoti Pandey

      Expense Report DFF would be available as part of Release 13 Update 17D

      http://www.oracle.com/webfolder/technetwork/tutorials/tutorial/cloud/r13/wn/r13-financials-wn.htm#F1437

      The DFF can be used to capture job level name for expense report creator based on a SQL query and can then be passed to SOA.

      SOA rules can be defined for example to say if DFF Attribute 5 is 'Accounting Manager' then ...

      Please also review MOS Documents -

      • Doc ID 2041364.1 How to Configure Invoice Approval Rules Using Job Level List Builder
      • Doc ID  2315701.1 - Check Invoice Approval Workflow for an example

      https://mosemp.us.oracle.com/epmos/main/downloadattachmentprocessor?attachid=2315701.1%3AR12INVOICEAPPROVAL&docType=WHITE%20PAPER&action=download

      • Subhash Valiveti

        Hi Jyoti,

        Thanks for the confirmation about Expense Report DFF in Release 13 17D. Is this functionality back-portable to 9.2?

        Regards,

        Subhash

      • Subhash Valiveti

        Hi Jyoti,

        We tried defining a DFF to capture the job level of the Expense Report Owner, but were not successful. We had reached out to Oracle Support but they confirmed that this cannot be achieved. So, we went with the trigger approach to capture the job level (we are on-premise in R 9.2).

        If the requirement to capture the job level in the DFF is possible using the SQL, please share with me the sql.

        Thanks in advance.

        Regards,

        Subhash

      • Subhash Valiveti

        Hi Jyoti,

        We have gone through the 2 MOS Documents. They talk about the options where the Job Level can be used in the 'THEN' section of the rules. We have requirements to have Job Level available in the 'IF' section of the rules.

        For Example....if we need to enable auto-approval for Expense Reports where the expense report owner's job level is above a particular grade ...say X. We need the job level to be available in 'IF' section so that appropriate rules can be written using this attribute.

        Is this requirement being addressed in the future solutions by Oracle?

        Regards,

        Subhash

        • Jyoti Pandey

          (1) No the Expense Report DFF will not be backported on 9.2

          (2)  If you want to use Job Level in IF then you need to have a SQL based DFF. To begin with try it out with Expense Line DFF and the SQL to default the value with no user intervention needed. As this is no different than current Project based or other expense line attributes, you can give it a shot by enabling aggregation in BPM rule configuration to 'Once per task'

          I am not technical so please do not rely on me for accuracy of query but you can use as a statting point and experiment

          Job name query:

          --------------------------------

          SELECT  UserJobs.Name
          From PER_JOBS UserJobs,
          PER_ALL_ASSIGNMENTS_M PersonAssignment,
          PER_USERS UserPEO
          Where UserPEO.PERSON_ID=PersonAssignment.PERSON_ID
          AND UserPEO.USERNAME = fnd_global.who_user_name
          AND PersonAssignment.ASSIGNMENT_STATUS_TYPE (+) = 'ACTIVE'
          AND PersonAssignment.PRIMARY_FLAG (+)= 'Y'
          AND PersonAssignment.ASSIGNMENT_TYPE (+) IN ('E','C','P')
          AND SYSDATE BETWEEN PersonAssignment.EFFECTIVE_START_DATE AND PersonAssignment.EFFECTIVE_END_DATE
          AND UserJobs.JOB_ID=PersonAssignment.JOB_ID


          Job level:

          SELECT  UserJobs.APPROVAL_AUTHORITY
          From PER_JOBS UserJobs,
          PER_ALL_ASSIGNMENTS_M PersonAssignment,
          PER_USERS UserPEO
          Where UserPEO.PERSON_ID=PersonAssignment.PERSON_ID
          AND UserPEO.USERNAME = fnd_global.who_user_name
          AND PersonAssignment.ASSIGNMENT_STATUS_TYPE (+) = 'ACTIVE'
          AND PersonAssignment.PRIMARY_FLAG (+)= 'Y'
          AND PersonAssignment.ASSIGNMENT_TYPE (+) IN ('E','C','P')
          AND SYSDATE BETWEEN PersonAssignment.EFFECTIVE_START_DATE AND PersonAssignment.EFFECTIVE_END_DATE
          AND UserJobs.JOB_ID=PersonAssignment.JOB_ID

           

    • Sameh Soliman

      Hi Subhash Valiveti,

       

      were you able to find a solution for your query?

       

      we would like to use the job level in the IF part too.

      please share your research.

       

      Regards

      Sameh

      • Subhash Valiveti

        Hi Sameh,

        The sql is not working perfectly. We created SR with Oracle Support. But its still in progress.

        Since we are in 9.2 On-Premise, we are looking into the option of using Dynamic approval rules.

        Regards,

        Subhash