Retrieving Data with NoetixViews
Noetix Views (NoetixViews) helps you to search for the right information that you want to view in your reports. For building queries or reports using your query tool, you need to retrieve data from the Noetix views. These views point to multiple tables in the database and extract the data from them. This chapter explains how you can search the information from Noetix views to create reports easily.
Finding the Right View
The Noetix Help File provides an easy way to find the data that you need for your reports. However, before you start, you must have a clear idea about the information you want to include in your reports. The following sections provide you with the instructions for locating the required data and selecting the required views for creating reports.
For information about using the help file, see Using Noetix Help File. For information about constructing the query or report, see your query tool documentation.
Locate the Data
The views provide you the data that you can use for creating the report. Therefore, for locating the required data, you need to search for the views that contain those data. The description of the views in the help file helps you to understand the contents of each view.
There may be multiple views that contain similar data, but the most effective method is to search for the data that seems specific or tailored. For example, the Invoice Number column may exist in many different views but the Invoice Amount In Dispute in only some of the views. The views containing tailored data may also contain general data. If they do not, they can be joined with other views that contain general data.
To find the required data, you can search by a specific column name (for example, Invoice Amount In Dispute), if known, or a keyword (for example, disputes or invoice disputes) using the keyword search in the help file. The view that you select may or may not include the data that you want to include in your report.
If it does not provide the necessary data, click the See Also link below the view description in the help file. You can see a list of related views. One of them should be suitable to your requirement.
If a single view does not contain all the required data, you need to join one or more views using the join-to (Z$) columns. Identify the Z$ columns in the view’s column list and search for those columns in the help file using the keyword search.
Only the Microsoft WinHelp format supports keyword search, but you can browse the views and related queries in other formats as well.
The following diagram explains the process of locating the required data for report creation:
Select a View
When selecting a view, select the lowest level of complexity that would be appropriate for your information requirement. For instance, if you can find the required data in a basic view, it will return results much faster than retrieving the same data from a cross-functional view.
Basic views return data faster because they retrieve data from only one Oracle data entry screen. However, they provide only limited data.
Value-added views retrieve data from several Oracle data entry screens. Therefore, they provide more data but return data slower.
Cross-functional views retrieve data from several Oracle E-Business Suite modules. Therefore, they provide the most complex combinations of data, but are rather slow in performance.
The Noetix Help File contains information about whether a view is basic, value-added, or cross-functional. If you locate the data you need in a view that is more complex than necessary, click the See Also link; you may find a related view that is more suitable.
Using the Built-In Hints
Hints that are built in into the views help you in locating data fast. Several types of hints have been incorporated into NoetixViews to help you in quickly locating data in the database using Noetix views.
View Name Hints
The role designations described in What Are Roles? are used as prefixes to the view names to indicate which role contains the view. These prefixes are customized for your enterprise during installation. The following examples show what hints the role designations give about the views:
AP Checks: The Checks view for the Accounts Payable module for U.S. operations of a company
AP1 Checks: The Checks view for the Accounts Payable module for international operations of the same company
PO Buyers: The Buyers view for the Purchasing module for U.S. operations of a company
PO1 Buyers: The Buyers view for the Purchasing module for international operations of the same company
Search-by Column Hints
Database management systems such as Oracle use indexes to find specific pieces of data without having to perform time-consuming scans of the database. When running a query, searching on indexed columns returns data more efficiently. NoetixViews identifies which columns are indexed in your database by displaying them twice in the Noetix Help File – once at the very beginning of the column listing, prefixed with A$ for identification purposes, and then again in their alphabetized positions in the column listing. For example, the AP Invoice Payments view displays indexed columns such as A$Accounting Date and A$Invoice Amount at the beginning of the listing, then Accounting Date and Invoice Amount later in the alphabetical column list.
The A$ columns are listed only as a reference and should not be selected when building queries. To include the indexed column (for example, A$Accounting Date) in a query, use the corresponding column without the A$ prefix (for example, Accounting Date). Using the column prefixed with A$ may break your query in the future if your database is upgraded and the specific column is no longer indexed by Oracle.
Whenever possible, you should use search-by columns to locate data. If you do not use search-by columns, the data may still be located; however, it may take many minutes or hours to return because the server has to scan all the rows in the table.
Flexfield Name Hints
NoetixViews supports the key and descriptive flexfields that are defined at your Oracle E-Business Suite instance and allows you to easily search the key and descriptive flexfields related information. Without NoetixViews, the only way to check flexfields is through an Oracle E-Business Suite form. In NoetixViews, a prefix is added to the name of your application flexfield to group them for easy access. The prefix consists of an abbreviation of the flexfield name followed by $. For example, at a particular site, the key flexfield related columns for the Accounting Flexfield key flexfield associated with a distribution line can be prefixed with Distr$ and the descriptive flexfield related columns for the Vendors descriptive flexfield can be prefixed with Vend$.
Flexfield names for your site are listed in the Column List screen in the help file.
Key flexfields are composed of several segments that contain important (or key) data. For example, a site's liability account number may include these segments:
Company
Region
Cost Center
Account
Product
Sub-Account
The key flexfields appear in a pop-up window when you tab to the particular field in an Oracle E-Business Suite form.
Key flexfields may have a different form for each ledger. For example, for U.S. operations, the liability account flexfield may be APLiab$Account Flexfield US, and for European operations, it may be APLiab$Account Flexfield UK.
In cases where it is appropriate, NoetixViews breaks out the key flexfield, allowing you to search on the individual segments. For example, if the Distr$Account Flexfield key flexfield consists of the same segments listed above, NoetixViews would define these search-by columns:
A$Distr$Company
A$Distr$Region
A$Distr$Cost Center
A$Distr$Account
A$Distr$Product
A$Distr$Sub Account
Descriptive Flexfields
Descriptive flexfields are used to store supplemental information relevant to your business site. You can retrieve different information from flexfields depending upon the context. For example, you may store an employee's social security number in one descriptive flexfield in an Expense Report form. However, if the Expense Report form is for a non-employee, you may store other information such as Vendor ID or Employer Identification Number in another descriptive flexfield.
Descriptive flexfields are indicated in Oracle E-Business Suite forms by double brackets ([ ]) around or above a single character field.
The descriptive flexfield pop-up window only appears in a form if a descriptive flexfield has been defined for your site. For information about designing descriptive flexfields, see the Oracle Applications Flexfields Guide.
To view a descriptive flexfield in the Oracle E-Business Suite form, move to the [ ] indicator. The flexfield is displayed in a pop-up window.
Join-to Column Hints
NoetixViews constructs the majority of table joins for you when the views are generated for your Oracle
E-Business Suite. Advanced users may wish to join several views together when all information is not available through a predefined Noetix view. Views can be joined using the join-to columns defined in the various views. These columns are identified with a prefix of Z$ and can be used to join to other tables or views with the same column name. One of the join-to column names must be the same as a view name in order to create a successful join.
The figure below shows conceptually how the AP1 INVOICE, AP1 CHECKS, and PO1 VENDORS views can be joined. The arrows in the figure indicate joins between two columns. The AP1 CHECKS and AP1 INVOICE views can be successfully joined because the AP1 CHECKS view contains a column named Z$AP1 CHECKS. Likewise, the PO1 VENDORS and AP1 INVOICE views can be joined because the PO1 VENDORS view contains the Z$PO1 VENDORS column.
Refer to the documentation for your query tool for details on how to do joins with that tool.
Flag Column Hints
NoetixViews converts flag columns, such as Posted Flag and Reversal Flag, to Y or N for consistency, regardless of how they are stored in the database.
Code Column Hints
Definitions of values for columns that have a Code suffix can be found in the NoetixViews Query Tool Help.
Value | Description |
FREIGHT | Invoice freight line |
ITEM | Purchased goods or service line |
MISCELLANEOUS | Miscellaneous charges |
TAX | Invoice tax line |
Ensuring Good Performance
This section summarizes ways to improve performance and ensure you are using NoetixViews efficiently.
Use the Built-In Hints
The hints in the previous section, such as always using a search-by column in your queries, will not only help you locate data but will also help your queries return data more quickly. Basing your queries on the built-in hints will also make them less likely to be affected by database upgrades.
Use the Appropriate View
When building your query, use a view appropriate to the level of complexity required. For tips on selecting a view, see Finding the Right View.
Select an Appropriate Query Tool
For information about some of the query tools supported by NoetixViews and the features supported by NoetixViews in these query tools, see Using BI Tools .
Avoid Non-Supported Features
Values Button: You should avoid using the Values button feature available with some query tools. Selecting this button displays all the possible values for a field. This feature is not appropriate for use with NoetixViews.
Auto Join: Do not use the Auto Join feature in your query tool; use the NoetixViews join-to (Z$) columns instead.
Troubleshooting
If you encounter some problem while using NoetixViews, it may not always be due to a system error. If it is not due to a system error, you can easily troubleshoot. Consider the following possibilities:
Security: A mismatch between security layers and user permissions often results in failed attempts to access a particular view. Sometimes the roles granted to a user do not allow the user to access the required information. The person responsible for administration of NoetixViews can also set several layers of security. If you cannot see any of the views for a given Business Group, ask the Noetix System Administration User to grant you the role for the Business Group. If, on the other hand, you can query a view, but some of the data is missing, it might be because of a security rule. If this occurs, contact the Noetix System Administration User or the DBA to confirm your security profile.
Missing Data: It may so happen that the view attempts to access the data, but the data do not exist in your system. For more information about specific views, read the note at the bottom of the view essay of the views you are interested in.
Query Error: Commonly, the user cannot retrieve the necessary information because he or she is querying the incorrect data. NoetixViews has an extensive help file that clearly outlines each view by role, the data elements of each view, and all possible ways to access the data in each view. The user can also perform a search by topic that returns clear, simple information and directions. For more information about using Noetix Help File, see Using Noetix Help File.