Grants Reporting - University of Delaware



| |Grants Queries |Table of Contents |udel.edu/financials |

|Page No. |QUERY NAME |DESCRIPTION |QUERY STATUS |COMMENTS |

|EZQ's - Departmental Financial Queries (based on general ledger Transaction and Balances tables) |

|1 |EZQ_BAL_PROJ_QUERY |View PTD Balance by Acct |good |Project-to-date balance summarized by account |

|2 |EZQ_BAL_PROJ_SUMMARY |PTD grp by account category VS |good |Project-to-date balance summarized by acct category |

|3 |EZQ_BAL_PROJ_TOTAL |View PTD Balance Total |good |Project-to-date balance: one line "bottom line" amount |

|GMQ's - Departmental Grants Management Queries (based on Grants tables) do not use the RUN button, click on query name |

|4 |GMQ_CASH_RECEIVED_BY_CONTR |Cash rec'd by contract & proj |not ready |Shows payments rec'd (revenue no longer = pmt rec'd) |

|5 |GMQ_CLOSE_OUT_REPORT |Trans by Project and purpose |good |Shows transactions by Project ID and displays F&A rate |

|5a |Supplement to # 5: Subtotal PS Query in Excel |  |not a query |Subtotaling in Excel is useful with GMQ_CLOSE_OUT_REPORT |

|6 |GMQ_CS_COMMITTED_EXPENDED_V |match budget and spent by PROJ |good |Shows cost share (match) committed and spent amounts by project |

|7 |GMQ_DEPT_CONTACTS_ASSIGN |List of dept contacts |good |List of all departments contacts |

|8 |GMQ_DEPT_EFFORT_ADMIN_ASSIGN |List of effort administrators |good |List of all departmental effort administrators |

|9 |GMQ_CURRENT_EFFORT_BY_PI * |All current effort for a PI |not ready |Total current effort for a PI as of current date |

|10 |GMQ_PROPOSED_EFFORT_BY_PI * |All proposed effort for a PI |not ready |Total proposed effort for a PI as of current date |

|11 |GMQ_FA_BY_DEPT |Indirect Costs  (F&A) total |good |Shows Indirect costs (F&A) by Dept ID |

|12 |GMQ_FA_BY_DEPT_BY_MONTH |Indirect Costs (F&A) by month |good |Shows Indirect costs (F&A) by Dept ID broken down by month |

|13 |GMQ_INACTIVE_18MOS |Find Pending Props 18 mos old |good |Gives pending proposals 18 months or older by PI |

|14 |GMQ_OVPR_DEPT_ASSIGN |List of depts and OVPR contact |good |List of Research Office PM's and APM's assigned to each dept. |

|15 |GMQ_PENDING_CLOSEOUT |Pending closeouts |good |Returns a list of project nearing their end dates (> than 90 days) |

|16 |GMQ_PI_PROJECTS * |Project Attributes by PI |good |List of Project ID's associated with a PI (like old DV screen) |

|17 |GMQ_PI_PROPOSALS * |List of Proposals by PI |good |List of Proposals ID's associated with a PI |

|18 |GMQ_PO_ACTIVITY_SUM |Sums PO related data |usable |Returns PO data by Project ID and PO (one summary line) |

|19 |GMQ_PO_W_CS_BY_PROJECT |Returns PO related data |usable |Returns PO data by Project ID and PO and cost share (details) |

|20 |GMQ_PROP_PROJS_BUD |sum prop budgets by projects |sort of usable |Usable ONLY when there is just ONE budget period info for Project(s) |

|21 |GMQ_WAIVED_FA |Sum total F&A Waived |good |Gives sum of waived F & A sorted by DEPT and Award ID |

|PIQ's - Departmental PI Queries (based on Grants tables) do not use the RUN button, click on query name |

|22 |PIQ_CVINFO_COLL_PATENTS |cv collaborations inc. patents |good |PI's CV info: collaborations and patents |

|23 |PIQ_CVINFO_EDUC |education |good |PI's CV info: education |

|24 |PIQ_CVINFO_HON_AWD |honors and awards |good |PI's CV info: honors and awards |

|25 |PIQ_CVINFO_PUBS |PIs publications |good |PI's CV info: publications |

|26 |PIQ_CURRENT_EFFORT_BY_PI * |All current effort for a PI |not ready |Total current effort for a PI as of current date |

|27 |PIQ_PROPOSED_EFFORT_BY_PI * |All proposed effort for a PI |not ready |Total proposed effort for a PI as of current date |

|28 |PIQ_FA_BY_PI |IDC total for PIs in a dept |good |Indirect costs (F&A) for a particular PI |

|29 |PIQ_FA_BY_PI_BY_MONTH |IDC by month for PIs in a dept |good |Indirect costs (F&A) for a PI broken down by month |

|30 |PIQ_PI_PROJECTS * |Project Attributes by PI |good |List of Project ID's associated with a PI (like old DV screen) |

|31 |PIQ_PI_PROPOSALS * |List of Proposals by PI |good |List of Proposals ID's associated with a PI |

| |* denotes a query that exists as both a GMQ and a PIQ | | |

|(NOTE: If you run a GMQ or PIQ and get no results, you may not have the correct Dept Row Level security. Contact your Training/Comm Team Rep.) |

Grants Reporting – EZQ’s data from the Balances Tables in UDFinancials (FIRPT)

1. EZQ_BAL_PROJ_QUERY

Project-to-date balance for a Purpose, summarized by account. The data come from the general ledger tables.

[pic]

• Enter Fiscal Year, [Accounting] Period and Purpose code (or use lookup button). The wildcard % is not used in this prompt.

• Click OK button

[pic]

• Above are the partial results.

2. EZQ_BAL_PROJ_SUMMARY

Project-to-date balance for a Purpose, summarized by account category. The data come from the general ledger tables.

[pic]

• Enter Fiscal Year, [Accounting] Period and Purpose code (or use lookup button). The wildcard % is not used in this prompt.

• Click OK button.

[pic]

• Above are the partial results.

3. EZQ_BAL_PROJ_TOTAL

Project-to-date balance for a Purpose, the balance is shown as one “bottom line” amount for a given fiscal year and accounting period. The data come from the general ledger tables.

[pic]

• Enter Fiscal Year, [Accounting] Period and Purpose code (or use lookup button). The wildcard % is not used in this prompt.

• Click OK button.

[pic]

• Above is the one row result.

Grants Reporting – GMQ’s: data from the Grants/Projects Tables in UDFinancials (FIRPT)

4. GMQ_CASH_RECEIVED_BY_CONTR (not ready)

This query shows cash payments received by a contract and its associated Project IDs or for an individual Projects ID. (Unlike the legacy system, in PeopleSoft Grants revenue does NOT equal cash received by the University.)

[pic]

• Enter Project ID OR enter % (wildcard) to see all Project IDs you have access to see.

• Click OK button.

[pic]

• Above are the partial results.

• Contract is analogous to Reference Award Number; it is used for billing purposes

• Total Invoice Amount = amount billed to the sponsor.

• Unpaid Invoices = amount not yet paid by sponsor

• Cash Received = amount the sponsor has paid

• Sum Amount Funded = total amount funded for this Project by the sponsor

• NOTE: This returns cash received since 7/1/04 (it will not include any cash rec’d before this date).

5. GMQ_CLOSE_OUT_REPORT

This query shows transactions by Project ID and displays the F&A rate.

[pic]

• Enter the full Project ID or use lookup icon

• Click OK button

[pic]

• Above are the partial results. (Please note there are 2 additional columns on the left not shown due to limited space: Project and Purpose.)

• The results are transactions from the PROJ_RESOURCE table.

• An Type = the Analysis Type. This is an important field; see the Important Codes handout for more information.

• F&A % = the indirect cost rate for this Project and is displayed in the far right column.

|Subtotal PeopleSoft Query using Excel | | | |

|(useful with GMQ_CLOSE_OUT_REPORT) | | | |

| | | | |

| | | | |

| | | | |

• Run Query and Download to Excel

• Delete top row so that header row (row with field titles) is row #1

• Click top left space to highlight all data including header row

• On the toolbar, select Data, on drop-down box, select Subtotals

• In the Subtotal dialog box, fill in as follows:

"At each change in" drop down box, choose YEAR

"Use function" drop down box, choose SUM

"Add subtotal to" scroll down box, choose AMOUNT

Click on check-box: check summary below data only

Un-check the other two boxes

Click "OK"

• Keeping data highlighted, again select Data on the toolbar, then Subtotals on drop down box

• In the Subtotal dialog box, fill in as follows:

"At each change in" drop down box, choose ACCOUNT

"Use function" drop down box choose SUM

"Add subtotal to" scroll-down box, choose AMOUNT

Cannot click on check-box: “check summary below data only”; it should grayed out.

Click "OK"

On the far left, use the + and - to collapse or expand the summaries

You can group by any field desired, including Project, etc.

6. GMQ_CS_COMMITTED_EXPENDED_V

This query gives match (cost share) committed and spent amounts by project.

[pic]

• Enter Dept ID or % to see all Dept IDs you have access to see.

• Enter Project ID or % to see all Project IDs you have access to see.

• Click the OK button

[pic]

• Above are the partial results. There are two additional columns on the right (Dept ID and Project ID)

• Award Status = See the Important Codes in the handout.

• CS Committed/Expended = cost share (or match) amounts.

7. GMQ_DEPT_CONTACTS_ASSIGN

This query returns a list of all departmental Grants contact people.

There are NO PROMPTS with this query.

[pic]

• Above are the partial results. Note that the results are returned alphabetically by department description.

• Report any incorrect departmental contact person to OVPR.

8. GMQ_DEPT_EFFORT_ADMIN_ASSIGN

This query returns a list of all departmental Effort Administrators. An Effort Administrator is the custodian of the Effort Certifications and is responsible for monitoring the web-based process for his/her department (beginning March 2005).

There are NO PROMPTS with this query.

[pic]

• Above are the partial results. Note that the results are returned alphabetically by department description

• Report any incorrect departmental contact person to OVPR.

9. GMQ_CURRENT_EFFORT_BY_PI (not ready)

This query returns the total current effort for a PI as of today’s date.

10. GMQ_PROPOSED_EFFORT_BY_PI (not ready)

This query returns the total proposed effort for a PI as of today’s date.

11. GMQ_FA_BY_DEPT

This query gives the F&A (indirect costs) by Dept ID. This is the F&A charged to the sponsor not what has been distributed.

[pic]

• Enter a Dept ID or leave blank for all Dept IDs you are authorized to see. (Remember to include the leading zero.)

• Enter the Fiscal Year (Note – 2005 is the first year for which there is data in UDFinancials System)

• Click the OK button

[pic]

• The results are shown as one row per DeptID (when more than one deptid is returned).

• Total F&A Generated = the total F&A (indirect costs) generate for a prior fiscal year or year-to-date for the current fiscal year. This the F&A charged to the sponsor, not what has been distributed.

• (Note: This query is based on Purpose row level security.)

12. GMQ_FA_BY_DEPT_BY_MONTH

This query gives the indirect cost (F&A) by Dept ID broken down by month.

[pic]

• Enter a Dept ID or leave blank for all Dept IDs you are authorized to see. (Remember to include the leading zero.)

• Enter the Fiscal Year (Note – 2005 is the first year for which there is data in UDFinancials System)

• Click the OK button

[pic]

• The results are shown as one row per DeptID (when more than one deptid is returned).

• Monthly Totals = the total F&A (indirect costs) for each month for a prior fiscal year or year-to-date for the current fiscal year.

13. GMQ_INACTIVE_18MOS

This query gives pending proposals for a PI that are 18 months or older.

[pic]

• Enter PI Name with %

• Enter Today’s Date or one in the future

• Click OK button

[pic]

• Above are the partial results.

• Results show the proposals that are due to be inactivated on a given date (current or future).

• Proposal Status = DRF (Draft) usually means the proposal was converted from the legacy system (GMS).

• Proposal Status = APR (Institution Approved) is the normal status of a more current proposal entered in PeopleSoft.

14. GMQ_OVPR_DEPT_ASSIGN

This query returns a list of OVPR project managers and assistant project managers assigned to each department.

There are NO PROMPTS with this query.

[pic]

• Above are the partial results. (Note that the results are returned alphabetically by department description.)

15. GMQ_PENDING_CLOSEOUT

This query returns a list of all projects and their end dates (within 90 days).

[pic]

• Enter First Date of Month in mm/dd/yyyy format.

[pic]

• Above are the partial results.

• This shows the projects and their ending dates within 90 days of the date you entered.

16. GMQ_PI_PROJECTS

This query returns a list of Project ID’s associated with a given PI. This gives similar information to the legacy DV screen in the legacy system (GA). This GMQ is secured by Dept ID; you will only the projects you have access to view.

• Enter any of the following optional fields: EMPLID, Project ID, Ref. Award #, Status or Sponsor Name.

• Follow steps 1, 2 and 3 to lookup a PI’s EMPLID if needed

• Click OK button once you’re back to the query prompts and have entered all the fields you desire.

[pic]

• Above are the partial results.

• Results show the Ref. Award #, Project IDs, Project Long Title, as well as its Begin/End Dates, Award Status, the Proposal ID, Sponsor, [F&A] Rate Type and F&A Rate %.

17. GMQ_PI_PROPOSALS

This query returns a list of Proposals associated with a given PI.

[pic]

• Enter: Proposal ID or % (wildcard). Note: the lookup icon does not work.

• Enter Primary PI’s Name with % (wildcard) OR % by itself.

• Enter the optional prompts Sponsor Name (full name, not acronym) and/or Proposal Status OR leave blank to get all.

• HINT - To get all the proposals for all your Dept IDs:

▪ Enter Proposal ID = %, Sponsor = (leave blank), PI Name = %, and Status = (leave blank).

• Click the OK button

[pic]

• Above are the partial results. It shows the Primary and Project PIs, Proposal ID, Proposal Project Number (not Project ID), Short and Long Titles, Proposal Status, as well as Sponsor’s Due Date, Name and Budget.

• Proposals with multiple projects will have multiple rows.

18. GMQ_PO_ACTIVITY_SUM (this query is in progress, it is usable, but we hope to improve it soon)

This query returns Purchase Order (PO) data by Project ID and PO. It rolls up the amounts by purchase request, commitments, adjustments and payments.

[pic]

• Enter Project ID or use lookup

• Enter PO # and/or Web Req ID (both are optional; enter either, both, or leave blank for all Pos and Reqs for this Project ID)

• Click the OK button

[pic]

• Above are the results in five rows:

o Original Req. Amount is shown on the Web Req ID row(s).

o Actual Payment amount is shown on the two rows with the PO #.

o PO Commitment amount is shown on two rows.

o PO Adjustments are shown on two separate rows.

o Account is shown to give you more information about the transactions.

• Reminder – this GMQ is a work in progress!

19. GMQ_ PO_W_CS__BY_PROJECT (this query is in progress, it is usable, but we hope to improve it soon)

This query returns detailed Purchase Order (PO) data by Project ID and PO and includes cost share data.

[pic]

• Enter Project ID (use lookup, do not use %) is optional or leave blank to see all the Projects you have access to see.

• PO # is optional (look up does not work with this prompt) and/or Web Req ID (both are optional; enter either, both, or leave blank for all Pos and Reqs for this Project ID)

• Click the OK button

[pic]

• Above are the results. (Please note there are 4 additional columns on the left not shown due to limited space: Project, Year, Period and Account.)

• This is the detailed data associated with this Project ID and Req. ID for both direct and cost share amounts:

o Purchase Req. amounts

o Req. reversal amounts

o PO commitment amounts

o Actual amounts

o PO adjustment amounts

• Reminder – this GMQ is a work in progress!

20. GMQ_PROP_PROJS_BUD (this query is in progress, it has limited usability for proposals that have a SINGLE budget period.)

This query returns budget data for Project(s) based on a Proposal ID.

[pic]

• Enter Proposal ID (the lookup button does not work)

• Click OK button

[pic]

• Returns budget data for all projects, budget periods and budget categories for a given Proposal ID.

• Data includes total direct costs and cost share (if any).

• Reminder – this GMQ is a work in progress!

21. GMQ_WAIVED_FA

This query gives the sum of waived F&A sorted by Dept ID, Reference Award No. and Project ID.

There are NO PROMPTS with this query.

[pic]

• Above are the partial results.

• It returns a row for each Ref. Award Number (Contract Number) and Project ID combination.

• In the above example, contract 5 P20 RR15588-05 has three Projects associated with it.

• (Note: this query returns results based on Purpose row level security.)

Grants Reporting – PIQ’s: data from the Grants/Projects Tables in UDFinancials (FIRPT)

22. PIQ_CVINFO_COLL_PATENTS

This query gives a PI’s CV data for collaborations and patents.

[pic]

• Enter the PI’s Empl ID or use the lookup icon (see page 16, GMQ_PI_PROJECTS for more complete instructions).

• Click OK button

[pic]

• Above are the results.

23. PIQ_CVINFO_EDUC

This query gives a PI’s CV data for education.

[pic]

• Enter the PI’s Empl ID or use the lookup icon (see page 16, GMQ_PI_PROJECTS for more complete instructions).

• Click OK button

[pic]

• Above are the results.

24. PIQ_CVINFO_HON_AWD

This query gives a PI’s CV data for honors and awards.

[pic]

• Enter the PI’s Empl ID or use the lookup icon (see page 16, GMQ_PI_PROJECTS for more complete instructions).

• Click OK button

[pic]

• Above are the partial results.

25. PIQ_CVINFO_PUBS

This query gives a PI’s CV data for publications.

[pic]

• Enter the PI’s Empl ID or use the lookup icon (see page 16, GMQ_PI_PROJECTS for more complete instructions).

• Click OK button

[pic]

• Above are the partial results.

26. PIQ_CURRENT_EFFORT_BY_PI (not ready)

This query returns the current effort for a PI as of today’s date.

27. PIQ_PROPOSED_EFFORT_BY_PI (not ready)

This query returns the total proposed effort for a PI as of today’s date.

28. PIQ_FA_BY_PI

This query gives the indirect costs (F&A) for a given PI.

[pic]

• Enter a Dept ID or leave blank for all Dept IDs you are authorized to see. (Remember to include the leading zero.)

• Enter the Fiscal Year (Note – 2005 is the first year for which there is data in UDFinancials System)

• Click the OK button

[pic]

• The results are shown as one row per PI (when more than one PI is returned).

• Total F&A = the total F&A (indirect costs) for a prior fiscal year or year-to-date for the current fiscal year.

• (Note: This query is based on Purpose row level security.)

29. PIQ_FA_BY_PI_BY_MONTH

This query gives the indirect costs (F&A) for a given PI broken down by month.

[pic]

• Enter a Dept ID or leave blank for all Dept IDs you are authorized to see. (Remember to include the leading zero.)

• Enter the Fiscal Year (Note – 2005 is the first year for which there is data in UDFinancials System)

• Click the OK button

[pic]

• The results are shown as one row per PI (when more than one deptid is returned).

• Monthly Totals = the total F&A (indirect costs) for each month for a prior fiscal year or year-to-date for the current fiscal year.

30. PIQ_PI_PROJECTS

This query returns a list of Project ID’s associated with a given PI. This gives similar information to the legacy DV screen in the legacy system (GA). This GMQ is secured by Dept ID; you will only the projects you have access to view.

[pic]

• Enter any of the following optional fields: EMPLID, Project ID, Ref. Award #, Status or Sponsor Name.

• If entering, the PI’s Empl ID or use the lookup icon (see page 16, GMQ_PI_PROJECTS for more complete instructions).

• Click OK button once you’re back to the query prompts and have entered all the fields you desire.

[pic]

• Above are the partial results.

• Results show the Project IDs and Project Titles, as well as its Begin/End Dates, Award Status, Proposal ID, Sponsor, [F&A] Rate Type, and F&A Rate %.

31. PIQ_PI_PROPOSALS

This query returns a list of Proposals associated with a given PI.

[pic]

• Enter: Proposal ID or % (wildcard). Note: the lookup icon does not work.

• Enter Primary PI’s Name with % (wildcard) OR % by itself.

• Enter the optional prompts Sponsor Name (full name, not acronym) and/or Proposal Status OR leave blank to get all.

• HINT - To get all the proposals for all your Dept IDs:

▪ Enter Proposal ID = %, Sponsor = (leave blank), PI Name = %, and Status = (leave blank).

• Click the OK button

[pic]

• Above are the partial results. It shows the Primary and Project PIs, Proposal ID, Proposal Project Number (not Project ID), Short and Long Titles, Proposal Status, as well as Sponsor’s Due Date, Name and Budget.

• Proposals with multiple projects will have multiple rows.

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

[pic]

[pic]

[pic]

1

3

2

• Type PI’s name in Name box

• Click Look Up button

• Click PI’s name in Search Results list

coming soon

coming soon

coming soon

coming soon

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download