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)
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)