QUESTION: What is the recommended method for joining Noetix views across modules?
ANSWER: First, some background: Each view in NoetixViews is designed to be used, by itself, as the basis for queries and reports that answer business questions. Most views have dozens of columns, taken from many tables in the Oracle E-Business Suite database. Users can create some pretty sophisticated reports using just a single view.
However, it’s not practical for a view to contain all the columns a user might want in a report. Some views already have more than one hundred columns—adding more would make them unwieldy. Similarly, some concepts (like “Employee” or “Inventory Item” or “Vendor”) have dozens of attributes which are needed occasionally, but not frequently. It doesn’t make sense to put each of those dozens of attributes into every view that includes “Employee” or “Vendor.” Therefore, the Noetix views are designed to be joined to each other. To simplify this process for report authors, Noetix has created a naming convention to help users know where these joins are permitted. Columns whose name start with Z$ indicate “join columns.”
For example, the Projects view PA_Tasks has a column named Z$PA_Tasks. Several other views (includingPA_Labor_Expenditures and PA_Cost_Distribution_Lines) have an identically named column, indicating that they can be joined to PA_Tasks. Similarly, PA_Tasks has columns named Z$PA_Projects and Z$PA_Organizations, indicating that it can also be joined to PA_Projects and PA_Organizations.
Finally, coming back to the original question: How do I join Noetix views across modules? Noetix has created Z$ links between views that simplify this task and span multiple modules.
Here are a few examples:
|Module Connection||View → Z$ Join column → View|
|Payables → Purchasing||AP_Invoices → Z$PO_Vendors → PO_Vendors|
|Order Management → Inventory||OM_Line_Details → Z$INV_Items → INV_Items|
|Benefits → Human Resources||BEN_Emp_Dependents → Z$HR_Emp_Info → HR_Emp_Info|
|Bill of Materials → Cost Management||BOM_Routing_Comparisons → Z$CST_Cost_Types → CST_Cost_Types|
Submit a question to Noetix and check back for the answer in an upcoming blog post!