Oracle Apps: Working with Oracle Forms

Below are common points we need to incorporate while working on Oracle Forms

LOV for Date and Date time Fields

1. Make the field as "Text Item" and set the LOV to "ENABLE_LIST_LAMP"

2. Set validate from the List property for the Item to "No"

2. Create a Trigger "KEY-LISTVAL" for the Item with following code
    
      calendar.show;
     
      By default, the Calendar shows the month of the value in the date field
      (if a value exists) upon first opening. If no specific date is supplied, the
      Calendar shows the current month.
     
      calendar.show([first_date]);
     

Set value of item/primary key using Sequence

:SEQUENCE. XXGL_ADP_SECURITY_HDR_SEQ.NEXTVAL in Intial value of the Item


Populate non database fields during Query in form

1. Create post query trigger on the Datablock

2. Set the fields by writing the code, in below example Employee number and name are non database
   fields which are populate based on the value of field person id(Database field)
  
    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');


Validate if the Record entered is a Duplicate Record

1. Create a ON-COMMIT trigger at form level and write below logic based on your requirement
   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)