[Code Sample]loop trough all records of a block

最新推荐文章于 2020-10-01 06:22:08 发布

原创最新推荐文章于 2020-10-01 06:22:08 发布 · 3.3k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#遍历

Oracle EBS Code Sample 专栏收录该内容

9 篇文章

订阅专栏

本文详细介绍了如何在代码中通过循环遍历多个块，并执行特定操作，包括块的定位、记录位置的保存与恢复、记录数的计算及重复记录的处理等。适用于理解并实现复杂的数据处理流程。

Sample 1:

declare
   currRec integer;
begin
   -- go to the desired block
   navigate_to_block('BLOCK_NAME');
   -- save the current record position
   currRec := :system.cursor_record;
   first_record();
   loop
      IF :SYSTEM.LAST_RECORD = 'TRUE' THEN
        exit;
      ELSE
        next_record;
      END IF;
   end loop;
   -- go back to the original record position
   go_record (currRec); 
end;

Sample 2:

declare
  number_of_records number;
  where_am_i number;
  l_deptno   dept.deptno%type;
  l_dname    dept.dname%type;
  l_loc      dept.loc%type;
begin
  go_block('dept');
  
  -- calculate last record number (to know the exit point)
  last_record;
  number_of_records := :system.cursor_record;
  
  -- now duplicate checked records
  first_record;
  loop
    exit when :system.cursor_record = number_of_records + 1;
    if checkbox_checked('dept.cb') then
       -- return to where_am_i + 1 record
       where_am_i := :system.cursor_record;

       -- memorize items in a checked record
       l_deptno := :dept.deptno;
       l_dname  := :dept.dname;
       l_loc    := :dept.loc;
	  	 
       -- create a new record at the bottom 
       last_record;
       create_record;
	  	 
       -- I'll make new values slightly different from the original ones
       :dept.deptno := l_deptno + 1;
       :dept.dname  := 'New ' || l_dname;
       :dept.loc    := 'New ' || l_loc;
	  	 
       -- go back and check another record
       go_record (where_am_i + 1);
    else
       next_record;
    end if;
  end loop;
end;

Sample 3:

Go_Block('Block_B1');
Last_Record;
L_num_records := :system.cursor_record;
FOR i in 1..L_num_records
LOOP    
     Go_Block('Block_B1');
     Go_Record(i);
     --update the fields in the row
     :Block_B1.item1 := 'Set your value';
     :Block_B1.item2 := 'Set your value';
     ...
     ...
     Next_Record;
END LOOP;
First_Record;

Sample 4:

IF Name_In('RELATED_ITEMS.substitution_type') = 'R' THEN
  IF l_select_flag = 'Y' THEN 
	  GO_BLOCK('RELATED_ITEMS');
	  FIRST_RECORD;
	  ctr := 0;
    LOOP
	    l_current_record := get_block_property('RELATED_ITEMS',CURRENT_RECORD);	
	  	IF (Name_In('RELATED_ITEMS.substitution_type') = 'R' and
	  		 (l_current_row != l_current_record) AND
	  		 Name_In('RELATED_ITEMS.select_flag1') = 'Y') THEN
	  		copy('N','RELATED_ITEMS.select_flag1');
	      go_record(l_current_row);
	  		return;
      END IF;
      
	  	ctr := ctr + 1;
      IF Name_In('SYSTEM.LAST_RECORD') = 'TRUE' THEN
	  		EXIT;
	    ELSE
	  		NEXT_RECORD;
	  	END IF;
	  	
	  END LOOP;
	 
	 go_record(l_current_row);
  END IF; /* select = 'Y' */
END IF;

[Code Sample]loop trough all blocks of a form