Platform:	Oracle	App:		Tool:	Oracle Forms
DB Ver:	9.2	App Ver:		Tool Ver:	6i
Revision Date:	28-Mar-2006	Keywords:	HOWTO, FROM CLAUSE, QUERY DATA SOURCE NAME, WHERE CLAUSE, UNION, SUBQUERY
Goal
To use an alternative to the FROM CLAUSE in a form, OR to base a form on a UNION / QUERY etc
Facts
Solution
There has been a requirement to base a form on a sub query, where a view is inappropriate due to the parameters needing to be embedded in the view. Initially setting the block's Query Data Source Type to FROM CLAUSE Query appeared to offer the best solution. However, upon repeated tests, a number of limitations became clear. OnlyVERYsimple queries worked - more complex queries involving subqueries caused the form to crash, even though the SQL itself had been tested successfully within an in-line view on the database. Parameters are not supported. The FROM CLAUSE query must be constructed by concatenating the string valued in to the SQL as it is constructed. This loses scalability as the query must be parsed by the database each time it is executed. Having only glanced at the option of basing a block on a stored procedure, I had a brainwave. Given that a FROM CLAUSE query would have rendered the form incapable of automatically using the INSERT/UPDATE/DELETE functionality, and given that this was acceptable, I considered the following: The Query Data Source Type = TABLE seemed to be very stable. My solution was to use this type, effectively selecting the columns out of a constructed view that had the required columns. I used this view as the base table. In my case, I wanted a query involving many subqueries and a UNION. So first, I created a view that had the columns on the form: CREATE OR REPLACE VIEW xxmg_itemspreader_orgs_v AS SELECT DECODE(moa.organization_id, mp.master_organization_id, 1, 2) order_clause ,moa.assigned_flag ,moa.organization_id ,moa.inventory_item_id ,moa.organization_code ,moa.organization_name ,moa.primary_unit_of_measure_tl ,moa.cost_of_sales_account ,moa.sales_account ,moa.expense_account ,moa.encumbrance_account ,moa.master_organization_id ,moa.eam_enabled_flag FROM mtl_org_assign_v moa ,mtl_parameters mp WHERE 1=1 AND mp.organization_id = FND_PROFILE.VALUE('MFG_ORGANIZATION_ID') / Next, I set the Query Data Source Name in the form to the name of the view.

Finally, I allowed the forms generated SQL:

SELECT DECODE(moa.organization_id, mp.master_organization_id, 1, 2) order_clause ,moa.assigned_flag ,moa.organization_id ,moa.inventory_item_id ,moa.organization_code ,moa.organization_name ,moa.primary_unit_of_measure_tl ,moa.cost_of_sales_account ,moa.sales_account ,moa.expense_account ,moa.encumbrance_account ,moa.master_organization_id ,moa.eam_enabled_flag FROM xxmg_itemspreader_orgs_v

to be the top half of my UNION. The bottom half would be placed in the form's WHERE clause:

master_organization_id = :startup_info.master_org_id

AND NVL (eam_enabled_flag, 'N') = NVL (:parameter.EAM_ITEM_FLAG, 'N')
AND inventory_item_id = :control.inventory_item_id
AND EXISTS (

SELECT 'x'
FROM org_organization_definitions ood
WHERE ood.organization_id = x.organization_id
AND ood.inventory_enabled_flag = 'Y'
)

UNION

SELECT order_clause, organization_id, inventory_item_id, organization_code,
organization_name, primary_unit_of_measure_tl, assigned_flag,
cost_of_sales_account, sales_account, expense_account,
encumbrance_account
FROM xxmg_itemspreader_orgs_v x

WHERE 1=1
AND master_organization_id = :startup_info.master_org_id
AND NVL (eam_enabled_flag, 'N') = NVL (:parameter.EAM_ITEM_FLAG, 'N')
AND EXISTS (

SELECT 'x'
FROM org_organization_definitions ood
WHERE ood.organization_id = x.organization_id
AND ood.inventory_enabled_flag = 'Y'

)

AND (inventory_item_id IS NULL
AND organization_id IN (

SELECT organization_id

FROM xxmg_itemspreader_orgs_v
WHERE inventory_item_id IS NULL
MINUS
SELECT organization_id
FROM xxmg_itemspreader_orgs_v
WHERE inventory_item_id = :control.inventory_item_id
)

)

With a UNION, the ORDER BY clause must reference the numeric position of the column to be ordered, so I used:

1 ASC, 7 DESC, 4