Use this method when some data is not stored in the Invoices Line Item table. The following scenario describes how use JOIN to combine rows from two or more tables based on a common column between them.
Scenario
You need to find the "unit of measure" name for each line item.
Solution
This appears to be in the table SYS_DATA_OPTION.
Note: Replace "unifier" with your table prefix. See Review Unifier Database Table Structure for information about finding a table name.
LEFT JOIN SYS_DATA_OPTION sdo
ON inv_li.UGENUNITOFMEASUREPD = sdo.OPTION_VALUE
AND (sdo.DATA_NAME = 'Unit of Measure'
AND sdo.REGISTRY = 'unifier')
LEFT JOIN unifier_budgetitem bi
ON inv_li.BITEMID = bi.BITEMID
AND inv_li.BUDGETID = bi.BUDGETID
SELECT
.Legal Notices
Copyright © 2016, 2018,
Oracle and/or its affiliates. All rights reserved.
Last Published Wednesday, March 28, 2018