GHSU Query Training

GHSU Query Training

Created on Tuesday, September 27, 2011

The information contained in this document is subject to change without notice. If you find any problems in the documentation, please report them to us in writing. This document is not warranted to be error-free. Except as may be expressly permitted in your license agreement for these Programs, no part of these Programs may be reproduced or transmitted in any form or by any means, electronic or mechanical, for any purpose.

If this documentation is delivered to the United States Government or anyone using the documentation on behalf of the United States Government, the following notice is applicable:

U.S. GOVERNMENT RIGHTS
The U.S. Government’s rights to use, modify, reproduce, release, perform, display, or disclose these training materials are restricted by the terms of the applicable Oracle license agreement and/or the applicable U.S. Government contract.

Oracle, JD Edwards, PeopleSoft, and Siebel are registered trademarks of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners.

/ Training Guide
GHSU Query Training

Table of Contents

Queries

QRY.10.001GHSU - Create a Query from Scratch

QRY.10.002GHSU - How to Run a Query

QRY.10.005GHSU - Create a Private Qry from a Public

QRY.10.010GHSU - Create an Ad-Hoc Query

Page 1
/ Training Guide
GHSU Query Training

Queries

QRY.10.001GHSU - Create a Query from Scratch

This topic demonstrates how to create a public query.

Procedure

For this topic, let's pretend that you want to create a query to report all AP journals from Jrnl_ln for a particular period of time. Let's see how this is done.

Step

Action

1. / Click the Vertical scrollbar.
2. / Click the Reporting Tools link.

3. / Click the Query link.

4. / Click the Query Manager link.

5. / Click the Create New Query link.

6. / Enter the desired information into the begins with field. Enter "jrnl".
7. / Click the Search button.

8. / Click an entry in the Add Record column.

9. / Click the JOURNAL_ID option.

10. / Click the JOURNAL_DATE option.

11. / Click the JOURNAL_LINE option.

12. / Click the LEDGER option.

13. / Click the ACCOUNT option.

14. / Click the FUND_CODE option.

15. / Click the DEPTID option.

16. / Click the PROGRAM_CODE option.

17. / Click the BUDGET_REF option.

18. / Click the Vertical scrollbar.
19. / Click the BUDGET_PERIOD option.

20. / Click the PROJECT_ID option.

21. / Click the MONETARY_AMOUNT option.

22. / Click the Vertical scrollbar.
23. / Click the JRNL_LN_REF option.

24. / Click the Vertical scrollbar.
25. / Click the Vertical scrollbar.
26. / Click the Horizontal scrollbar.
27. / It was hard to find the CLASS_FLD field to choose, so choose the Sort Fields option to find field names alphabetically.
Click the Sort fields alphabetically button.

28. / Click the Vertical scrollbar.
29. / Click the CLASS_FLD option.

30. / Click the Vertical scrollbar.
31. / Click the Fields tab to show the fields that we choose to be shown in the query output.

32. / To edit the field Order, Xlat vales, Aggregate and Heading Text, click the Edit button.

33. / For this example, we want to make the Heading Text more descriptive.
Click the Text option.

34. / Enter the desired information into the Heading Text field. Enter "Journal Date".
35. / Click the OK button.

36. / Click the Horizontal scrollbar.
37. / The Reorder/Sort button allows you to choose the output order of the fields for the query results.
Click the Reorder / Sort button.

38. / Enter the desired information into the New Column field. Enter "1".
39. / Enter the desired information into the New Column field. Enter "2".
Continue until you have number all of the fields in the order you want the query results to be returned.
40. / Notice that the fields are now marked in the new column field as to the order for the query output.
Click the OK button.

41. / Click the Criteria tab to add criteria for the query.
OR
You can click on the Add Criteria option by the field that you want to add to the criteria tab.

42. / Click the Add Criteria button.

43. / Click the Select Record and Field button.

44. / Click the A.BUDGET_REF - Budget Reference link.

45. / Click the Prompt option.

46. / Click the New Prompt link.

47. / Click the OK button.

48. / Click the OK button.

49. / Click the Add Criteria button.

50. / Click the Select Record and Field button.

51. / Click the Vertical scrollbar.
52. / Click the A.JOURNAL_DATE - Journal Date link.

53. / Click the Condition Type list.

54. / Click the between list item.

55. / This example is shown by hard coding the Journal_Dates. So, a Const-Const is chosen.
However, for Public Queries at MCG, we do not want to hard code dates, Department IDs, and other specific criteria. Please make your Public Queries as Generic as possible.
To choose two prompts, to enter a beginning and end Journal_Dates, you would choose Expr-Expr, and choose the Journal_Date prompts.
56. / Enter the desired information into the Date field. Enter "04012008".
57. / Enter the desired information into the Date 2 field. Enter "04302008".
58. / Click the OK button.

59. / Click the Add Criteria button.

60. / Click the Select Record and Field button.

61. / Click the Vertical scrollbar.
62. / Click the A.JOURNAL_ID - Journal ID link.

63. / Click the Condition Type list.

64. / Click the like list item.

65. / Enter the desired information into the Constant field. Enter "AP%".
66. / Click the OK button.

67. / Click the Save Query button.

68. / Continue to use the MCG Query Naming Standards.
Enter the desired information into the Query field. Enter "MCG_GL_JRNL_AP_TEST".
69. / Enter the desired information into the Description field. Enter "Test query for AP jrnls".

Step

Action

70. / Click the OK button.

71. / Notice that the Query Name is now listed at the top of the page.
Now you can Run your query to check the results.
72. / Congratulations! You have just completed the topic How to Create a Query.
-Remember to use the MCG Query Naming conventions.
-Remember not to hard code Dept IDs or Dates for Public Queries.
End of Procedure.

QRY.10.002GHSU - How to Run a Query

This topic demonstrates how to run a query.

Procedure

For this topic, imagine that you want to run the PURCH_PO_NOT_POSTED query. This is a public query.

Step

Action

1. / Click the Vertical scrollbar.
2. / Click the Reporting Tools link.

3. / Click the Query link.

4. / Click the Query Viewer link.

Step

Action

5. / Enter the beginning of the query name into the begins with field. For this example, enter "purch".
6. / Click the Perform Search button.

7. / Click in the Run to HTML column beside PURCH_PO_NOT_POSTED.
If you want to run the query to Excel, then you can click on the Excel link.
If you want to Add the Query to your Favorites, then click on the Favorite link.

Step

Action

8. / Enter the desired information into the PO Date From field. Enter "09/01/2008".
9. / Enter the desired information into the PO Date To field. Enter "09/30/2008".
10. / Click the View Results button to run the query.

11. / Congratulations! You have completed the lesson on How to Run A Query.
End of Procedure.

QRY.10.005GHSU - Create a Private Qry from a Public

This topic demonstrates how to create a private query from a public query.

Procedure

For this topic, let's pretend that you want to create a private query that is now a public query. Let's see how this is done.

Step

Action

1. / Click the Reporting Tools link.

2. / Click the Query link.

3. / Click the Query Manager link.

Step

Action

4. / Enter the desired information into the begins with field. Enter "mcg_pay".
5. / Click the Search button.

6. / Click an entry in the Edit column.

7. / You can now modify the query any way that you need to modify it. This page will allow you to review the Fields that will be returned as results in the query.

Step

Action

8. / Click the Criteria tab.

9. / This page allows you to modify the Criteria of the query. The Public queries should contain prompts to make it easier for departments to run across campus.

Step

Action

10. / Click the Save As link.

Step

Action

11. / Enter the desired name for your private query into the Query field. Enter "KELLYC_PAYROLL_RUN_ID".
12. / Click the OK button to save the private query.

13. / Congratulations! You have just completed the lesson on how to create a Private Query from and Existing Public Query.
End of Procedure.

QRY.10.010GHSU - Create an Ad-Hoc Query

This topic demonstrates how to create an ad-hoc query.

Procedure

For this topic, imagine that you want to look up the journal line entries for a specific amount of time. You do not want to have a query created to do this, but would want to pull that data for your information, reconciliation, or review. This would be known as using an Ad-Hoc Query. This query will not be saved, but you could save or print its results.

Step

Action

1. / Click the Vertical scrollbar.
2. / Click the Reporting Tools link.

3. / Click the Query link.

4. / Click the Query Manager link.

Step

Action

5. / Click the Create New Query link.

Step

Action

6. / Enter part of the record name that you want to use to pull data. For this example, we will be pulling data from the jrnl_ln record.
Enter "jrnl" into the begins with field to search for the jrnl_ln record.
7. / Click the Search button.

8. / Click the entry in the Add Record column besied the JRNL_LN record. This will choose the JRNL_LN record as your record to search on.

Step

Action

9. / Click the JOURNAL_ID option. In this example, other fields will be selected as well.
If you want to return all the fields on the JRNL_LN record, then click on the Check All Fields button.

10. / Click the JOURNAL_DATE option.

11. / Click the LEDGER option.

12. / Click the ACCOUNT option.

13. / Click the FUND_CODE option.

14. / Click the DEPTID option.

15. / Click the PROGRAM_CODE option.

16. / Click the CLASS_FLD option.

17. / Click the scrollbar.
18. / Click the BUDGET_REF option.

19. / Click the MONETARY_AMOUNT option.

20. / Click the PROJECT_ID option.

21. / Click the scrollbar.

Step

Action

22. / Click the Fields tab to review the fields that will you selected.

Step

Action

23. / Click the Add Criteria button to add the JOURNAL_DATE field to the criteria.

Step

Action

24. / Click the Condition Type list.

25. / Click the between list item.

26. / Enter the desired information into the Date field. Enter "11/01/2007".
27. / Enter the desired information into the Date 2 field. Enter "11/30/2007".
28. / Click the OK button.

Step

Action

29. / Click the Criteria tab to review the criterial that you selected for this query.

30. / Review the criteria that was selected for this query. Choose to Add Criteria if you want to add additional criteria.

Step

Action

31. / Click the Run tab to run the Ad-Hoc Query and return the results.

32. / Congratulations! You have completed the lesson Create an Ad-Hoc Query.
End of Procedure.
Page 1