Reporting and Analytics for ERP

Get Involved. Join the Conversation.

Topic

    Virginia Baez-Ponce
    Need report that shows all approval assignees who have NOT...Answered
    Topic posted October 17, 2017 by Virginia Baez-PonceBronze Medal: 1,250+ Points, tagged BI Publisher, Fusion Financial reporting, OTBI, Reports, Sample Reports 
    716 Views, 14 Comments
    Title:
    Need report that shows all approval assignees who have NOT approved invoice
    Summary:
    We are trying to build a report that provides only “open/incomplete” invoice approvals (i.e. only those approval assignees who have NOT approved yet)
    Content:

    We are trying to build a report that provides only “open/incomplete” invoice approvals (i.e. only those approval assignees who have NOT approved yet).  Unfortunately the report we built shows all past and present “assignees” rather than just providing the “open/incomplete” approvals.  I’ve attached some screenshots as well as an archive of the analysis we built.

    I am no OBTI/SQL expert but is there a quick a simple way to return only the “most recent” assigned value in the data set for a particular invoice?  Interestingly enough if we go to BPM, look up the invoice and sort by “Assigned” it correctly returns only the INCOMPLETE (unapproved) pending approvals which is out desired result.

    Image:

    Best Comment

    Alexey Shtrakhov

    Hi Virginia,

    oh, in OTBI...
    in this case you can try to add following 2 filters:

    Approval Action Code = 'ORA_ASSIGNED_TO'  (this is to get all the assigned tasks)
    Approval Status Code = 'INITIATED'  (this is to limit only those tasks which are still pending approval)

    This should limit the result to all the assigned tasks with status Initiated.

    See some sample where you can see 2 invoices.
    1 is fully approved and the other one is still in initiated status.

    So for the invoice 03222873 to get the only 1 task which is assigned we need to have the above 2 filters.
    As you can see approval status will be updated later according to the outcome of the approval task.

     

    With respect
    Alexey

    Comment

     

    • Alexey Shtrakhov

      check at least this:

      FIN_FUSION_SOAINFRA.WFTASK.STATE = 'ASSIGNED'

      With respect
      Alexey

    • Alexey Shtrakhov

      np, you are welcome!

      Happy if this helped you!

    • Alexey Shtrakhov

      oh sorry, just now noticed that you have asked where to put this... :)

      I see that looks like you have a BIP report, so i think you should incorporate this in your Datamodel related to this report... so basically you sould add this condition in your sql related to this report... should look like this...

      SELECT

      .....

      FROM       FIN_FUSION_SOAINFRA.WFTASK wft

      ....
        WHERE       wft.state='ASSIGNED'

      ....

    • Virginia Baez-Ponce

      Hi Alexey,

      So this is not a Data Model, I built this report using the Subject Criteria 'Payables Invoices - Transactions Real Time'. I have attached a screenshot of the columns selected.

      Hopefully you can still help out! I will def share this report once is complete

       

       

       

      • Alexey Shtrakhov

        Hi Virginia,

        oh, in OTBI...
        in this case you can try to add following 2 filters:

        Approval Action Code = 'ORA_ASSIGNED_TO'  (this is to get all the assigned tasks)
        Approval Status Code = 'INITIATED'  (this is to limit only those tasks which are still pending approval)

        This should limit the result to all the assigned tasks with status Initiated.

        See some sample where you can see 2 invoices.
        1 is fully approved and the other one is still in initiated status.

        So for the invoice 03222873 to get the only 1 task which is assigned we need to have the above 2 filters.
        As you can see approval status will be updated later according to the outcome of the approval task.

         

        With respect
        Alexey

    • Sameh Soliman

      1. Through customizing the BPM Worklist notifications list.

      2. Through running SQL query from your BIP.

       

      I've attached both 'How to customize the BPM worklist' and SQL query for your reference.

       

    • Sameh Soliman

      the other file with query

    • MShrini

      Hi Samesh,

       

      When we run the attached query it is saying 'ORA-00903: invalid table name', Kindly share the corrected query.

       

      Best Regards,

      Mvasu

    • Izharul Haq

      These tables are not running, I am trying to run the FIN_FUSION_SOAINFRA.WFAPPROVALGROUPS table but could not. please help me with the screenshots.

       

      Thanks.