When you gathered all the information that you need, from the Upper form and associated tables (see Writing Your First Statement), you need to add the Line Items to your query results (for example, Invoice Line Items). The Line Items are stored in the UNIFIER_UI_LINEITEM table.
Example
There are two ways to match the Invoice to the Invoice Line Items:
By using a LEFT JOIN since it returns all the rows from the left table (Invoices) even if there are no matches in the right table (Invoice Line Items).
Add the Line Items as a second (separate) SQL statement and have Unifier combine the two as two separate data views. Invoice Line Items will be a Sub Report View to the Invoices Main Report.
This method of matching the Invoice to the Invoice Line Items generates duplicate data (by the number of Line Items in the Invoice) in BI Publisher. As a result, this method is not recommended.
This method creates a new SQL query for the Invoice Line Items. This will result in a nested XML file, where all the Invoices Line Items are nested within their respective Invoice.
To add Line Items as a second (separate) SQL statement follow these steps:
SELECT * FROM unifier_ui_lineitem inv_li
That is to say, you are going to select all the columns from the unifier_ui_lineitem table and give the table an alias of inv_li.
SELECT inv_li.RECORD_ID,
inv_li.LI_NUM,
inv_li.SHORT_DESC,
inv_li.UUU_UNIT_PRICE,
inv_li.UUU_QUANTITY,
inv_li.AMOUNT
FROM unifier_ui_lineitem inv_li
This is a Sub Report View in Unifier; therefore, you do not need to include a PROJECT_ID column. Since Unifier needs to match the Invoice Line Items to the Invoices, you need to include the field that Unifier can use for the JOIN. You must include the inv_li.RECORD_ID column. If you look at the two tables (and compare it to the data in Unifier), you notice that the RECORD_ID column in the line item table matches up with the ID column of the Invoice table.
SELECT inv_li.RECORD_ID AS inv_li_record_no,
inv_li.LI_NUM AS inv_li_no,
inv_li.SHORT_DESC AS inv_li_desc,
inv_li.UUU_UNIT_PRICE AS inv_li_unit_price,
inv_li.UUU_QUANTITY AS inv_li_quantity,
inv_li.AMOUNT AS inv_li_amount
FROM unifier_ui_lineitem inv_li
Legal Notices
Copyright © 2016, 2018,
Oracle and/or its affiliates. All rights reserved.
Last Published Wednesday, March 28, 2018