Expenses

Get Involved. Join the Conversation.

Topic

    Janusz Jasinski
    Expenses Report - Details, Approver, Submitted Date,...
    Topic posted November 29, 2018 by Janusz JasinskiSilver Crown: 22,500+ Points, last edited January 30, 2019, tagged Expenses, Public Sector 
    125 Views, 7 Comments
    Title:
    Expenses Report - Details, Approver, Submitted Date, Approved Date...
    Content:

    Hi,

    Not sure if someone can help - we are looking at doing an expenses report but we obviously do not want to reinvent the wheel.

    What we need is a report with the following columns:

    • Expense details
    • Submitter Grade
    • Approver Name
    • Approver Grade
    • Submitted Date
    • Approved Date

    Does such a report exist or has anyone got any similar?

    Comment

     

    • Neelam Thakur

      We have created an OTBI analysis with all Expense Report Details. Fairly easy to create. You should try that out before any other development effort.

    • Janusz Jasinski

      What subject areas would I need to go into?

    • Janusz Jasinski

      We tried OTBI and SQL and through either path, we can not see who is the approver for the expense

      Any idea?

      • Siva Kumar

        Please refer to the sql mentioned in the below document

        Expense Approval Diagnostics and troubleshooting steps (Doc ID 2390805.1)

        • Janusz Jasinski

          I try that on an expense report for myself and it just errors!!!

          oracle.xdo.servlet.data.DataException: oracle.xdo.servlet.data.DataException: oracle.xdo.servlet.data.DataException: oracle.xdo.XDOException: java.sql.SQLSyntaxErrorException: ORA-01722: invalid number
           
           
          • Siva Kumar

            Can you try this ?

            SELECT
                w.identificationkey,
                w.createddate,
                w.tasknumber,
                w.taskid,
                w.taskgroupid,
                w.taskdefinitionname,
                w.roottaskid,
                w.aggregationtaskid,
                w.versionreason,
                w.assignees,
                w.approvers,
                w.updatedby,
                w.acquiredby,
                w.updateddate,
                w.state,
                w.outcome,
                w.stage,
                w.participantname,
                w.assignmentcontext
            FROM
                fa_fusion_soainfra.wftask w
            WHERE
                to_char(w.identificationkey) IN (
                    SELECT
                        to_char(expense_report_id)
                    FROM
                       exm_expense_reports
                    WHERE
                        expense_report_num = '&DOC_ID'
                )
            ORDER BY
                w.createddate DESC

            • Janusz Jasinski

              No results and if I take the clause off so it lists everything, the earliest it goes back to is February this year but we've been using FUSION for a few years now

              Also approvers is empty for most of the rows. There is a *tiny* amount that contain a valid person number

              Am I right in saying that if they approved via email this wouldn't show?