Requirement :
Enable 'Execure-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 in forms it
won’t work properly.
For this we need 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.
No comments:
Post a Comment