Saturday, 10 January 2015

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.



No comments:

Post a Comment