If you are creating a report in Fusion, chances are that you’ll need the report parameters. And to stop user from entering unexpected values, you’d need to provide LoV’s to the parameters. In this article I’ll share the LoV queries for following entities:
1. Legal Entity
2. Business Unit
3. Inventory Org
4. Sub Inventory
By the way, Here ( India) or Here ( Other Geographies) are few UNCONVENTIONAL tricks which I have learnt to improve the performance of BI reports or SQL queries. Try them only when nothing else works.. kind of SOS...
LoV Queries:
Legal Entity: “What is Legal Entity” is the first question which pops in the mind. Simplest answer without going to the details is the entity which government of the geography identifies. On a lighter note, who you can sue if something goes wrong. Coming back to the topic, Only in case your enterprise has multiple legal entities, then only this field makes sense. Query in that case would be:
select name Legal_Entity
from Xle_entity_profiles
Business Unit: Again, if your Enterprise has multiple legal entities, you’ll need to make it a dependent LoV. Otherwise it can be a simple independent LoV query.
For single Legal Entity:
select name BU_NAME
from hr_operating_units hou
For multiple Legal Entities: Dependent LoV query-
select hou.name BU_NAME
from hr_operating_units hou ,
Xle_entity_profiles le
where hou.DEFAULT_LEGAL_CONTEXT_ID = le.legal_entity_id
and le.name = NVL(:LEGAL_ENTITY_NAME, le.name)
So as you can see, if the legal entity parameter is provided, Business Units belonging to only that Legal Entity will be fetched by the query. Otherwise all the available business units will show up in the LoV.
Inventory: Inventory is the place where you store your items in. It’s always better to keep this LoV dependent on the Business Unit: And for that you can write the query like this:
select iop.organization_code
from inv_org_parameters iop,
hr_operating_units hou
where iop.BUSINESS_UNIT_ID = hou.organization_id
and iop.INVENTORY_FLAG = ‘Y’
and hou.name = NVL(:BU_NAME, hou.name)
Sub Inventory: Of course, it is better to define the sub inventory LoV based on the inventory.
SELECT secondary_inventory_name
FROM inv_secondary_inventories subinv,
INV_ORG_PARAMETERS iop
WHERE subinv.organization_id = iop.organization_id
and iop.ORGANIZATION_CODE = NVL(:INVENTORY_ORG,iop.ORGANIZATION_CODE)
Related Posts
Business Unit – Legal Entity – Ledger/ set of Books Relationship Query
Legal Entity Query in Fusion
Feedback:
Hope the article helped you. If it did, please rate the post. In case it didn’t, do leave a comment to let us know what did we miss.
Reference:
support.oracle.com
