PROCEDURE post_query is
    begin

        --Get the Person details
        SELECT ppf.EMPLOYEE_NUMBER , 
               ppf.FULL_NAME
               into     
               :MAIN.EMP_NUM,
               :MAIN.EMP_NAME
        FROM   per_all_people_f ppf
        WHERE  person_id =:MAIN.person_id
        AND    rownum =1;
        
        --To prevent status change of the block
        SET_RECORD_PROPERTY( :SYSTEM.TRIGGER_RECORD,'BLOCKNAME', STATUS,
QUERY_STATUS);

    end post_query;      
      
  
Display Messages in Oracle Forms:

--String Message
fnd_message.set_string ('SERial Num has been repaired more than 3 times '); 
fnd_message.show (); 

--Dictonary Message
FND_MESSAGE.Set_Name('FND','FORM_RECORD_CHANGED');
FND_MESSAGE.Error;

--Debug Messsage
FND_MESSAGE.DEBUG('ERROR');

   PROCEDURE on_commit is
    CURSOR c_person
    is
    SELECT rowid row_id,
           hdr_id
    FROM   STAGGING_TBL
    WHERE user_name =:BLOCK.user_name
    AND   nvl(person_id,'-999') = nvl(:BLOCK.person_id,'-999');
   
  BEGIN
         --Validate if there is existing record for Oracle user
         FOR c_preson_rec IN c_person
         LOOP
        IF c_preson_rec.row_id <> :BLOCK.row_id
        THEN
            FND_MESSAGE.Set_STRING('Record exist for this Oracle User');
            FND_MESSAGE.show;
            Raise FORM_TRIGGER_FAILURE;                  
        END IF;
             
         END LOOP;
   
  END;
         

Looping logic to perform validations
curr_record  := :SYSTEM.CURSOR_RECORD;
curr_item    := :SYSTEM.TRIGGER_ITEM;    
BEGIN
    go_block('BLOCK');
    First_Record;
    LOOP

        IF :BLOCK.PERSON_ID = person_id
        AND curr_record <> :SYSTEM.CURSOR_RECORD
        AND ( :BLOCK.START_DATE >= start_date OR :BLOCK.end_DATE <= end_date
              OR :BLOCK.END_DATE >= start_date OR :BLOCK.end_DATE <= end_date)
        THEN
            FND_MESSAGE.Set_STRING('Employee has mutliple records with Overlapping Dates');
            FND_MESSAGE.show;
               Raise FORM_TRIGGER_FAILURE;

        END IF;

        IF :System.Last_Record = 'TRUE' 
        THEN 
            EXIT;

        END IF;

    END LOOP;    



    Execute_Query;
    Go_Record( curr_record);
    go_item(curr_item);

END;

Querying on Non Database fields in Oracle Forms

1.  Sample Block :  EMPCCLOCOVD
               employee_name    -   Non database field.
               employee_number  -   Non database field.
               person_id        -   Database, hidden field


2. Add a Pre-Query trigger on the block , where it contains a non database fields for querying.

3. Sample code for the Pre - Query trigger :

  PROCEDURE pre_query IS      l_where    
   VARCHAR2(250) := 'Where 1 = 1';
  BEGIN
      l_where :=  l_where || 'AND person_id IN (SELECT person_id FROM per_all_people_f ppf '
               ||' WHERE EMPLOYEE_NUMBER like '''||'%'||:EMPCCLOCOVD.EMP_NUM||'%'')'
               ||' AND   FULL_NAME like '''||'%'||:EMPCCLOCOVD.EMP_NAME||'%'')';

        SET_BLOCK_PROPERTY('EMPCCLOCOVD',DEFAULT_WHERE,l_where);
  END pre_query;


POPULATE WHO COLUMNS in Oracle Forms:

Write the below code in PRE-INSERT, PRE-UPDATE at DATABLOCK LEVEL

FND_STANDARD.SET_WHO;

Datablock Order by Non database Column 
I need my header block order by NON DATABASE Column COUNTRY below is the value
that I need to have in ORDER BY Clause of the datablock

(SELECT ftt.territory_short_name from fnd_territories_tl ftt where   ftt.TERRITORY_CODE = country_code)