Implementing Search Functionality using LOV during query in Oracle Forms

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.