Database view containing user defined fields from Requisition Entry

Client need the database view or table which contains the user defined fields from Requisition entry program

• Custom Reports

• Enterprise ERP any version

• Tyler Reporting Services

• OnPrem, SaaS hosted

• Dervied

• Requisition Entry

• User Defined

View database columns overlay show the field name as dervied.

To view these User Defined fields in Enterprise ERP, follow the steps below:

1. Navigate to EERP Menu > Financials > Purchasing > Purchase order processing > Requisition Entry

2. Click on USER DEFINED tab to view user defined fields.

This information is available in 2 TRS views:

• sp_user_field

• sp_user_data

User defined field names are available in sp_user_field and the data for these user defined fields are in sp_user_data. 

In sp_user_data the column ud_key_value is used to tie a record from sp_user_data to a requisition. This column ud_key_value has the following structure:

1. It starts with 4 digit fiscal year where requisition was created.

2. Followed by a space

3. Followed by the requisition number.

This can be constructed from rq_master view using the columns rh_fiscal_year & a_requisition_no.

The following sample query explains how to join 3 views

select

R.rh_fiscal_year,

R.a_requisition_no,

F.uf_label,

D.ud_data_text

from sp_user_field as F

left join sp_user_data as D

on F.a_application_id = D.a_application_id

and F.a_field_number = D.a_field_number

left join rq_master as R

on CONCAT(R.rh_fiscal_year,' ',R.a_requisition_no) = ud_key_value

where F.a_application_id like 'rqentpst'