Description:
Oracle Fusion applications have support for attachments using functionality delivered by Oracle Enterprise Crawl and Search Framework (ECSF). This functionality is integrated at crawl time of the Search view object, whereby ECSF will look at the crawled Search view object for attachment child view objects. If there are attachments defined this way, it will use Oracle Fusion Middleware Extensions for Applications Attachments APIs to retrieve the contents of the enclosure, and they are added to the crawled document.
Requirement:
Enable ‘Execute-Query’ using an LOV field (non-database items).
Approach:
When user clicks on CTRL+F11, we need set dynamic where clause.
Normally for database fields, Oracle standard feature will take the query find. But when we use LOV’s informs, it won’t work properly.
For this, we need to set where clause dynamically in Data block Trigger KEY-EXEQRY..
Write where clause logic in KEY-EXEQRY Trigger.
Step-1: Create Data block KEY-EXEQRY Trigger
Step-2:
Write where clause logic in KEY-EXEQRY Trigger. Compile the Form.
Code Snippet:
Declare
l_where_clause varchar2(4000):=null;
BEGIN
l_where_clause:=’1=1 ‘;
IF :XXINVE0001_ECCN_LICENSES_D.ORGANIZATION IS NOT NULL
THEN
l_where_clause:=l_where_clause||’AND ORG_ID IN
(SELECT ORGANIZATION_ID FROM HR_OPERATING_UNITS WHERE NAME LIKE ”’
||:XXINVE0001_ECCN_LICENSES_D.ORGANIZATION || ””;
l_where_clause:=l_where_clause||’)’;
END IF;
IF :XXINVE0001_ECCN_LICENSES_D.SALES_ORDER IS NOT NULL
THEN
l_where_clause:=l_where_clause||’AND SO_HEADER_ID IN(SELECT HEADER_ID
FROM OE_ORDER_HEADERS_ALL WHERE ORDER_NUMBER LIKE ”’
||:XXINVE0001_ECCN_LICENSES_D.SALES_ORDER || ””;
l_where_clause:=l_where_clause||’)’;
END IF;
SET_BLOCK_PROPERTY(‘XXINVE0001_ECCN_LICENSES_D’, DEFAULT_WHERE, l_where_clause);
EXECUTE_QUERY;
EXCEPTION
WHEN OTHERS THEN
NULL;
End ;
Step3:
Open Form and enter value in LOV field ( in Enter Query mode -> F11)
Execute Query ( Ctrl+F11), to see the desired results.
Summary:
Implementation will only crawl attachments attached to the Search view object, not the attachments of child objects.
Queries?
Do drop a note by writing us at contact@doyensys.com or use the comment section below to ask your questions.
