Adding Line Items

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:

  1. JOIN the Line Items DB Table

    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).

  2. Add 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.

JOIN the Line Items DB Table

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.

Add Line Items

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:

  1. Create a new SQL statement in your SQL Text Editor (you can save the new file as Invoice_LI.sql) and add the following to begin your SQL query: 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.

  2. Specify which columns you want to include in your results.

    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.

  3. Add aliases to these column names so they are easier to understand when you are building your BI Publisher report.

    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

Related Topics

SQL JOIN

SQL ORDER BY Keyword

SQL Final Statement



Legal Notices
Copyright © 2016, 2018, Oracle and/or its affiliates. All rights reserved.

Last Published Wednesday, March 28, 2018