Oracle Forms - How to avoid duplicated records in a block

The purpose is to reject two records that contain duplicated values.

The technique used to solve this problem comes from the Kevin D Clarke’s calculated item famous solution.

It uses two calculated items, one in the data bock and another in a control block.

The first calculated item (:DEPT.MATCH_FOUND) is added to the DEPT block. It contains the formula as follow:

Comparaison(:ctrl.charsave, :dept.deptno||:dept.dname)

Notice in this case,that we want to avoid duplicates on both DEPTNO and DNAME values.

Function COMPARAISON (val1 varchar2, val2 varchar2)

Return number

Is

   answer number := 0;

Begin

   if val1 = val2 then

      answer := 1;

   end if;

   return(answer);

End;

COMPARAISON is a program unit stored in the Forms module.

The two values are compared to each other, then the function returns 1 (a value greatest than 0) if both the values are identical.

The first value (:ctrl.charsave) contains the bakup value of the current record.

The DEPT block must have the following properties setting:

Query all records

YES

The CTRL block must have the following properties setting:

Query all records	YES
Single record	YES
Database data block	NO

The second calculated item (:CTRL.MATCH_FOUND) is added to the CTRL block.

It summarize the values contained in all the rows of the DEPT block (dept.match_found).

If the total is greater than 1, we have two duplicated data.

The sample dialog

·         Download the DUPLICATES.fmb sample dialog for you to test

How to Get Previous Record Value in Oracle Forms

DECLARE
   l_prev_empno   emp.empno%TYPE;
   l_prev_ename   emp.ename%TYPE;
   l_prev_job     emp.job%TYPE;
BEGIN
   IF TO_NUMBER (:SYSTEM.cursor_record) > 1
   THEN
      PREVIOUS_RECORD; /* move to the previous record and get previous record values */
      l_prev_empno := :emp.empno;
      l_prev_ename := :emp.ename;
      l_prev_job := :emp.job;
      NEXT_RECORD;    /* come back to the current record */
   END IF;
END;

How to Check First Record and Last Record in Oracle Forms

BEGIN
   IF :SYSTEM.Cursor_Record = '1'
   THEN
      MESSAGE ('At first record.');
   END IF;
END;

BEGIN
   IF :SYSTEM.LAST_RECORD = 'TRUE'
   THEN
      MESSAGE ('At last record.');
   END IF;
END;

Oracle Forms - How to support shift + F6 to duplicate record

1. Add trigger KEY_DUPREC in block level

2. Put " duplicate_record; " inside.

Kill session for lock

1. find target session.

SELECT aob.object_name
,aob.object_id
,b.process
,b.session_id,c.SID, c.SERIAL#,c.LOGON_time,c.seconds_in_wait, c.state
FROM all_objects aob, v$locked_object b, v$session c
WHERE aob.object_id = b.object_id
and b.session_id = c.sid


2. delete session.

alter system kill session '<sid>,<serial>'

How to open multiple OAF to work

As an Oracle ERP engineer, we often need to access multi window in OAF to execute job, then below method will help you to get it!

1) make a IE shortcut and edit attribute.
2) put -nomerge in the target bottom.

How to resume PR lines in AutoCreate Documents

It's often to get buyer to request to resume PR lines in AutoCreate Document pool. It could be some reasons like they don't want to group lines or somehow to cancel PO etc..

2 actions to resume:

1:  Clean line_location_id value in po_requisition_lines_all.

2:  Put 'Y' in reqs_in_pool_flag in po_requisition_lines_all. 

Then you will see  they show up in the pool.