Pre-Query Samples

Validate the current query criteria or provide additional query criteria programmatically, just before sending the SELECT statement to the database.

Description
Fires during Execute Query or Count Query processing, just before Form Builder constructs and issues the SELECT statement to identify rows that match the query criteria.

Definition Level form or block

Legal Commands

SELECT statements, unrestricted built-ins

Enter Query Mode no

Usage Notes
Use a Pre-Query trigger to modify the example record that determines which rows will be identified by the query.

On Failure
The query is canceled. If the operator or the application had placed the form in Enter Query mode, the form remains in Enter Query mode.

Fires In
COUNT_QUERY
EXECUTE_QUERY
Open the Query
Prepare the Query

Sample 1

This example validates or modifies query criteria for a database block query.

BEGIN 
  /* 
  ** Set the ORDER BY clause for the current block 
  ** being queried, based on a radio group 
  ** called 'Sort_Column' in a control block named 
  ** 'Switches'. The Radio Group has three buttons 
  ** with character values giving the names of 
  ** three different columns in the table this 
  ** block is based on: 
  ** 
  **     SAL 
  **     MGR,ENAME 
  **     ENAME 
  */ 
  Set_Block_Property('EMP',ORDER_BY, :Switches.Sort_Column); 
  /* 
  ** Make sure the user has given one of the two 
  ** Columns which we have indexed in their search 
  ** criteria, otherwise fail the query with a helpful 
  ** message 
  */ 
  IF :Employee.Ename IS NULL AND :Employee.Mgr IS NULL THEN 
    Message('Supply Employee Name and/or Manager Id '|| 
            'for Query.'); 
    RAISE Form_Trigger_Failure; 
  END IF; 
 
  /* 
  ** Change the default where clause to either show "Current 
  ** Employees Only" or "Terminated Employees" based on the 
  ** setting of a check box named 'Show_Term' in a control 
  ** block named 'Switches'. 
  */ 
  IF Check box_Checked('Switches.Show_Term') THEN 
    Set_Block_Property('EMP',DEFAULT_WHERE,'TERM_DATE IS NOT NULL'); 
  ELSE 
    Set_Block_Property('EMP',DEFAULT_WHERE,'TERM_DATE IS NULL'); 
  END IF; 
END; 

NOTE:call SET_BLOCK_PROPERTY to modify the block's WHERE and ORDER BY clauses from within the Pre-Query trigger, to further restrict or order the records the query will retrieve.

Sample 2

Declare
	v_where varchar2(2000):='1=1 ';

Begin
	 If :QUERY_FIND_ORDER.ORDER_ID_FROM is not null then
		 V_where := v_where || ' and ORDER_ID>='||:QUERY_FIND_ORDER.ORDER_ID_FROM;
	 end if;
	 If :QUERY_FIND_ORDER.ORDER_ID_to is not null then
		 V_where := v_where || ' and ORDER_ID<='||:QUERY_FIND_ORDER.ORDER_ID_to;
	 end if;
  If :QUERY_FIND_ORDER.CUSTOMER_ID_FROM is not null then
	 V_where := v_where || ' and CUSTOMER_ID>='||:QUERY_FIND_ORDER.CUSTOMER_ID_FROM;
  end if;
   If :QUERY_FIND_ORDER.CUSTOMER_ID_to is not null then
	 V_where := v_where || ' and CUSTOMER_ID<='||:QUERY_FIND_ORDER.CUSTOMER_ID_to;
   end if;
   
   SET_BLOCK_PROPERTY('DEM_ORDERS_V',DEFAULT_WHERE,v_where);	
   
   :PARAMETER.G_QUERY_FIND:='FALSE';
end ;							

Sample 3

 app_query.reset('loc');
copy_from_parent('LOC', 'PRE-QUERY');

  
COPY(NAME_IN('PARAMETER.ORG_ID'),'LOC.ORGANIZATION_ID');

-- 
-- SET THE VALUES FROM THE FIND WINDOW INTO THE LOC BLOCK
-- DO THIS ONLY IF THE QUERY FIND FLAG IS SET TO TRUE
--

IF :PARAMETER.G_QUERY_FIND = 'TRUE' THEN
  IF :SYSTEM.CURRENT_FORM = 'INVSDSUB' THEN
    :LOC.INVENTORY_LOCATION_ID := :PARAMETER.LOC_ID_QF;
  END IF; 
    FND_FLEX_FIND.QUERY_KFLEX_RANGE('INV','MTLL',101,
                                    :LOCATOR_FIND.LOCATOR_LOW,
                                    :LOCATOR_FIND.LOCATOR_HIGH,
                                    'LOC.LOC_NAME');

    IF NAME_IN('LOCATOR_FIND.FIND_LOCATION_ID') IS NOT NULL THEN
      COPY (NAME_IN('LOCATOR_FIND.FIND_LOCATION_ID'),'LOC.INVENTORY_LOCATION_ID');
    END IF;
    IF NAME_IN('LOCATOR_FIND.SUB_INV') IS NOT NULL THEN
      COPY(NAME_IN('LOCATOR_FIND.SUB_INV'),'LOC.SUBINVENTORY_CODE');
    END IF;
    IF NAME_IN('LOCATOR_FIND.LOC_LKUP_MEANING') IS NOT NULL THEN

    COPY (NAME_IN('LOCATOR_FIND.LOC_LKUP_CODE'),'LOC.INV_LOC_TYPE');

    END IF;
   
    /* If WMS is installed, query based on Material Status also. */
    IF NAME_IN('LOCATOR_FIND.STATUS_ID') IS NOT NULL THEN
      COPY(NAME_IN('LOCATOR_FIND.STATUS_ID'), 'LOC.STATUS_ID');
    END IF;
    
  :PARAMETER.G_QUERY_FIND := 'FALSE' ;
END IF;

FND_FLEX.EVENT('PRE-QUERY');