EPM 151 Intermediate AP/PO

Exercise 1:

Query Creation Methodology

Scenario: Provide a list of all vouchers entered for a specific time frame. Include appropriate vendor information and order by the date the voucher was entered. Include all fields from the source table, initially, to determine which fields should be included in this report.

Locate the source of the required information.

Reference: Financials Reporting Table Summary; EPM Data Dictionary

Create New Query

Navigation: EPM Reporting Tools > Query > Query Manager

  1. Click onCreate New Query
  1. Click the Advanced Search Option
  1. Next to the Uses Field Name, click on the drop-down arrow for Condition Type and change to contains.
  1. Type in the valuevoucherin the search field
  1. Click Search

Results: All tables/records that include voucher are displayed.

6.Locate CTW_VCHR_TRANSrecord in the results and click theAdd Record link

7.Click the Check all fields button.

Note: Hard coded criteria in a query design will limit the data in thesame manner every time the query is executed. Prompts allow for different valuesto be entered each time the query is executed.For example, the date range could be different each time the query is run.

Establish criteria to limit the results of the data sampling query.

Limit the return to only one vendor.

1.From the Fields Tab, click on Add Criteria icon (funnel) next to the field VENDOR_ID.

  1. Enter the value 0000010009 in the Expression 2 textbox in the Constant field.
  1. Click OK

Note: This is the vendor ID is for Suburban Stationers. If your agency does not purchase from Suburban, choose a different vendor.

Limit the date range

1.From the Fields Tab, click on Add Criteria icon (funnel) next to thefield ENTERED_DT.

  1. Click on the drop down arrow for Condition Type and change to greater than.
  1. Enter the value 1/1/10 in the Expression 2 textbox in the Define Constant field.

Note: Peoplesoft provides a calendar look up for alldate fields. The date may be entered manually in a variety of formats; eg. MM-DD-YY, MM-DD-YYYY, or the calendar can be used.

  1. Click Saveand name the query
  1. Click Return To Search
  1. Search for the query and run: click the excel hyperlink.

Results: Evaluate the results and decide which fields should be included in your report.

Note: Pay particular attention to the data format and length of each chosen field. For example, some fields contain text, others numbers and some are only one (1) character. To successfully establish criteria, the data display must be known.

Create Query - Exercise 1

Navigation: EPM Reporting Tools > Query > Query Manager

  1. Click on Create New Query
  1. In the Search field underFind an existing record, enter CTW_VCHR_TRANS
  2. Click Search
  3. Locate CTW_VCHR_TRANSrecord in the results and click the Add Record link.
  1. Choose the fields for your report columns by checking the box next to the field names below.

BUSINESS_UNIT - Business Unit

VOUCHER_ID - Voucher ID

VOUCHER_LINE_NUM - Voucher Line Number

DISTRIB_LINE_NUM - Distribution Line Number

INVOICE_DT - Invoice Date

INVOICE_ID - Invoice Number

VENDOR_ID - Vendor ID

CT_VNDR_NAME1 - Vendor Name 1

OPRID - User ID

ACCOUNTING_DT - Accounting Date

PYMNT_TERMS_CD - Payment Terms ID

ENTERED_DT - Entered on

CT_VCHR_LN_DESCR - Voucher Line Description

CNTRCT_ID - Contract Id

MONETARY_AMOUNT - Monetary Amount

ACCOUNT – Account

FUND_CODE - Fund Code

CT_SID – SID

DSCNT_AMT - Discount Amount

DSCNT_DUE_DT - Discount Due Date

Set up criteria to limit the results in the query

Establish a Prompt on Business Unit

  1. From the Fields Tab, Click on Add Criteria icon (funnel) next to the field BUSINESS_UNIT.
  1. Select equal toin the condition type box, and in Choose Expression 2 Typebox, click radio button for Prompt; In the Expression 2 box,click the New Prompt link.

3.Enter the text to appear with the runtime prompt. Click in the Heading Text box. Enter Business Unit=. Click OK.

Adding a Prompt from the Prompts Tab and using the Condition type Between prompts on a date field

  1. From the Prompts Tab, Click on Add Prompt.
  1. Click on the looking glass icon under Field Name. Search by fieldname: type in ENTERED_DT, click search
  1. Click the hyperlink for the fieldENTERED_DT

Enter the text to appear with the runtime prompt.

  1. Click in the Heading Text box. Enter Entered Date From. Click OK.
  1. To set up the second date prompt, follow the steps to create the first date prompt. The heading text for thesecond date prompt should read:Entered Date To.

Prompts set up from the Prompts Tab must also be entered as criteria.

  1. From the Fields Tab, Click on the Add Criteria button for the field ENTERED_DT.
  1. Click the drop down arrow and choose condition type between.
  1. In the Choose expression 2 Type box, clickthe Expr – Expr radio button.

Under Expression 2 group box, Define Expression,Click the firstAdd Prompt link. The Select Prompt Page appears. Select Entered Date From (:1) for the first expression andEntered Date To (:2) for the second expression.

  1. Click OK.

Establish a Prompt on Contract ID using the wildcard %

Note: EPM allows the use of two wildcards; % (percent sign) will return any number of characters and can be used at the beginning and/or end of a field, _ (underscore) allows the return of only one character and is primarily used in the middle of a field..

1. From the Fields Tab, Click on Add Criteria icon (funnel) next to the field CNTRCT_ID.

2. Select likein the condition type box, and in Expression 2 Type group box, click theradio button Prompt; under Expression 2 group box,select New Prompt link.

  1. Enter the text to appear with the runtime prompt. Click in the Heading Text box.
  2. Enter Contract ID like (%).
  1. Click OK.

Establish criteria on a field and not display the results.

Note: Setting criteria and not displaying the results eliminates extraneous columns from the report and is essential in the development of summary queries.

  1. From the Query Tab, Click on Add Criteria icon (funnel) next to the fieldPOST_STATUS_AP - Post Status.
  2. Select equal to in the condition type box and a enter the constant of P (posted) in the Expression 2 group box under Define Constant. Only posted vouchers will be returned by this query.
  3. Click OK
  4. Name and Save the query
  5. Run to excel.

Results: Evaluate the result.

Renaming column headers, changing column order, sorting the data

Rename column headers

  1. From the fields tab, click the Edit Buttonfor BUSINESS_UNIT
  1. In the Heading Group box, click the Text radio button; click in the Heading Text box and type in the new column header name Business Unit:

Results: The column name that appears on the query output has been changed.

Before

After

Change the column order

1. From the Fields tab, click the reorder/sort button.

2. Renumber the columns in the boxes on the left, New Column, to change the column order display.

Sort the data

Note: The functionality of sorting data groups all the data by the chosen field(s). The limitation is there is no option for subtotaling.

1. From the fields tab, click the Reorder/Sort button

2. Put a 1 in the far right column, New Order By, corresponding to the field by which you wish to group your data. The default value is ascending. If you want descending, click the box in the Order by Descending Column.

Save and execute the query.

EPM 151 Intermediate AP/PO

Exercise 2:

Create New Query using Two Tables

Scenario: Produce a list of vouchers where the payment handling code is RA (Returned Payment) to verify the accuracy of the payment notes and message.

Locate the source of the required information.

Reference: Financials Reporting Table Summary; EPM Data Dictionary

The results of our search indicate the information required resides on two tables, CTW_VCHR_TRANS and CTW_PAYVC_XREF

Note: Prior to joining tables, the EPM Job Aid, Reporting Table Indexes and Join Criteria, must be checked to ensure the accuracy of the field joins suggested by the query manager tool. Not all auto joins are correct due to incompatibility between State business practice and the Peoplesoft product. Link to the job aid-

Create a Query joining two tables

Navigation: EPM Reporting Tools > Query > Query Manager

  1. Click Create New Query
  2. Click the Advanced Search Option
  3. Next to the Uses Field Name, click on the drop-down arrow for Condition Type and change to contains.
  4. Type in the valuevoucher
  1. Click Search

Results: All tables/records that include voucher are displayed.

  1. Locate CTW_VCHR_TRANSrecord in the results and click the Add Record link

Select the second recordrequired:

  1. Go to the Records Tab
  1. Click the Advanced Search Option
  1. Next to the Uses Field Name, click on the drop-down arrow for Condition Type and change to contains.

4.In the Search field provided, enter the valuePYMNT_HANDLING_CD

Results: All tables/records that include PYMNT_HANDLING_CD are displayed.

5.Locate CTW_PAYVC_XREF record in the results and click the Join Recordlink.

  1. Click on the hyperlink identifying the record you wish to join to. In this case, click on the link for the record, CTW_VCHR_TRANS.

5. Accept the field joins suggested by the tool by clicking on the Add Criteria button.

Note: Preceding letters are placed before each field name. The query manager tool assigns each table an alias (letter) which is referenced throughout the query component, and is used instead of the table name. In the above case the alias A is assigned to CTW_VCHR_TRANS, the alias B to CTW_PAYVC_XREF.

Select the fields for your report columns. (Be sure to select the fields listed below from the appropriate tables in this query, A or B).

A.BUSINESS_UNIT - Business Unit

A.VOUCHER_ID - Voucher ID

A.VOUCHER_LINE_NUM - Voucher Line Number

A.DISTRIB_LINE_NUM - Distribution Line Number

A.INVOICE_DT - Invoice Date

A.VENDOR_ID - Vendor ID

A.CT_VNDR_NAME1 - Vendor Name 1

A.CT_VCHR_LN_DESCR - Voucher Line Description

B.CT_PYMNT_NOTES - Payment Notes

B.PAID_AMT - Paid Amount

B.PYMNT_MESSAGE - Payment Message

A.MONETARY_AMOUNT - Monetary Amount

Set up query criteria

Establish a run-time Prompt on Business Unit.

  1. Go to the Fields Tab.
  1. Click on Add Criteria icon (funnel) next to the field BUSINESS_UNIT.
  1. Select equal toin the condition type box.
  1. In the Expression 2 group box, click the radio button for Prompt.

5. Click the New Promptlink.

  1. Enter the text to appear with the runtime prompt. Click in the Heading Text box. Enter Business Unit=
  1. Click OK.

Adding a Prompt from the Prompts Tab and using the Condition type Between prompts on a date field

  1. Go to the Prompts Tab.
  1. Click on Add A Prompt.
  1. Click on the looking glass icon under field name.

Search by fieldname and enter ENTERED_DT in search field

  1. Click Search
  1. Click the hyperlink for the fieldENTERED_DT

Enter the text to appear with the runtime prompt.

  1. Click in the Heading Text boxand enter Entered Date From.
  1. Click OK.
  1. To set up the second date prompt, follow the steps to create the first date prompt. The Heading Text box for thesecond date prompt should read: Entered Date To.

Prompts set up from the Prompts Tab must also be entered as criteria.

  1. Go to the QueryTab.
  1. Click on the Add Criteria button for the field ENTERED_DT.
  1. Click the drop down arrow and choose Condition Typebetween.
  1. In the Choose Expression 2 Type box, choose the radio button forExpr-Expr.
  1. In the Expression 2 group box, Define Expression, Choose the Add Prompt link. The select prompt page appears, select Entered Date From prompt for the first expression and Entered Date To prompt for the second expression.
  1. Click OK.

6. Go to the Fields Tab.

7. Click on the Add Criteria button for the field VENDOR_ID

8.Click the drop down arrow and choose Condition Type of not in list

9.Under Expression 2, Edit List: Click on the looking glass icon to set up the list of excluded vendors

8.Add the vendor ID’s to the list one at a time and Enter the following Vendors IDs: 0000059169, 0000010016 and 0000010008.

Note: Vendor IDs used in this exercise are in accordance with Comptroller’s Memorandum, 2009-04, February 19, 2009.

Establish criteria on a field without displaying the results.

Note: Setting criteria and not displaying the results eliminates extraneous columns from the report and is essential in the development of summary queries.

  1. Go to the Query Tab.
  2. Click on Add Criteria icon (funnel) next to the field PYMNT_HANDLING_CD, located on the B table.
  1. Select equal toin the Condition Type box
  2. In the Expression 2 box, define a constant value of RA.

Note: Payment Handling Codes used in this exercise are in accordance with Comptroller’s Memorandum, 2009-31, datedSeptember 18, 2009.

5.Go to the Query tab.

6. Click on Add Criteria icon (funnel) next to the field POST_STATUS_AP - Post Status.

7.Select equal to in the Condition Type box

8.In Expression 2 group box, define a constant of P (posted).

9.Click Save and name the query

10.Click Return To Search

11. Search for the query and run and click the excel hyperlink.

Results: Evaluate query results

EPM 151 Intermediate AP/PO

Exercise 3:

Create New Query using Two Tables

Scenario: Provide purchase order information where the vendor is certified as Small or Minority Business enterprise (SBE or MBE).

Locate the source of the required information.

Reference: Financials Reporting Table Summary; EPM Data Dictionary

The results of our search indicate the information required resides on two tables, CTW_PO_TRANS and CTW_VNDR_CERT_V.

Note: Prior to joining tables, the EPM Job Aid, Reporting Table Indexes and Join Criteria, must be checked to ensure the accuracy of the field joins suggested by the query manager tool. Not all auto joins are correct due to incompatibility between State business practice and the Peoplesoft product. Link to the job aid:

Create a Query joining two tables

Navigation: EPM Reporting Tools > Query > Query Manager

  1. Click Create New Query
  1. Click the Advanced Search Option
  1. Next to the Uses Record Name, using the default Condition Type of begins with, enterthe valueCTW_Pin the Search Field
  1. Click Search

Results: All tables/records that begin with CTW_P are displayed.

3.Locate CTW_PO_TRANS record in the results and click the Add Record link

Select the second record.

  1. Go to the Records Tab
  1. Click the Advanced Search Option
  1. Next to the Uses Record Name, using the default Condition Type of begins with, enterthe value CTW_VND
  1. Click Search

Results: All tables/records that begin with CTW_VNDare displayed.

5.Locate CTW_VNDR_CERT_V record in the results and click the Join Recordlink.

  1. Click on the hyperlink identifying the record you wish to join to. In this case, click on the link for the record, A=CTW_PO_TRANS – Purchase Order Transaction Rpt
  1. Accept the field joins suggested by the tool by clicking on the Add Criteria button.

A.VENDOR_ID=B.VENDOR_ID

  1. In the message window that appears stating An effective date criteria has been automatically added for this effective dated record, Click OK

Note: Effective Dated Logic is built into the design of some HR records/tables. When data is entered into Core-CT, a new row is added to the record with an effective date, and the system retains the previous row(s) as history. Rows may also be future dated. Effective date logic allows for the return of the most current row of information. These criteria can be removed from queries to view all historical, current and future dated rows.

Using EPM Reporting Table Indexes and Joint Criteria Job Aid

The aforementioned aid indicates there is an additional field join between CTW_PO_TRANS and CTW_VNDR_CERT_V.

A.VENDOR_SETID=B.SETID

Add additional field join criteria

1. Go to the Query Tab

  1. Click Add Criteriabutton for the field A.VENDOR_SETID on reporting table A, CTW_PO_TRANS
  1. Accept the default condition type equal to.
  1. Click on the radio button Field in the Choose Expression 2 Type group box
  1. Click in the looking glass in the Expression 2:Choose Record and Field group box
  1. Click the Show Fields button for table B, CTW_VNDR_CERT_V
  1. Click on the link for the fieldB.SETID - SetID

Results: An additional table join has been added manually to the query design.

Select the fields to be displayed. Pay particular attention to the table alias.field name.

A.BUSINESS_UNIT - Business Unit

A.PO_ID - Purchase Order

A.LINE_NBR - Line Number

A.SCHED_NBR - Schedule Number

A.DISTRIB_LINE_NUM - Distribution Line Number

A.ACCOUNTING_DT - Accounting Date

A.PO_DT - Purchase Order Date

A.CNTRCT_ID - Contract Id

A.PO_TYPE - PO Type

A.CT_OBLIGATED_AMT - Applied Amount

A.CT_VNDR_NAME1 - Vendor Name 1

A.MONETARY_AMOUNT - Monetary Amount

B.CERTIFICATE_BEG_DT - Certificate Begin Date

B.CERTIFICATE_EXP_DT - Certificate Expiration

B.CERTIF_SOURCE - Certification Source

B.EFFDT - Effective Date

Set up query criteria

Establish a Prompt on Business Unit.

1. Go to the Fields Tab.

2. Click on Add Criteriaicon (funnel) next to the field

BUSINESS_UNIT.

3. Select equal toin the Condition Type box.

4.In the Expression 2 group box, click the radio button for Prompt.

5. Click the New Promptlink.

  1. Enter the text to appear with the runtime prompt. Click in the Heading Text box and enter Business Unit=
  1. Click OK.

Adding a Prompt from the Prompts Tab and using the Condition type Between prompts on a date field