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.

  • January 11, 2015 | 21 views