Wednesday, February 2, 2011

Iterate through form Block and update values

Use of first_Record, Next_Record, previous_record

Assign Old field Values in following triggers as per need

PRE-TEXT
WHEN-MOUSE-CLICK
KEY-UP
KEY-DOWN

:PARAMETER.P_OLD_VAL := :SYSTEM.CURRENT_VALUE;

Best way is to use a push button and use/modify following code in WHEN-BUTTON-PRESSED trigger

IF :SYSTEM.RECORD_STATUS = 'CHANGED' THEN
    IF :PARAMETER.P_OLD_VAL <> :PARAMETER.P_NEW_VAL THEN

    LOOP
        Next_Record;
        IF :PARAMETER.P_OLD_VAL <> :BLOCK.FIELD THEN
            previous_record; -- To stop at end of change
            --first_Record;
            EXIT;
        ELSE
            select :PARAMETER.P_NEW_VAL into :BLOCK.FIELD from dual
            where     :BLOCK.FIELD = :PARAMETER.P_OLD_VAL;
        END IF;   
    END LOOP;   
    END IF;   
ELSE
        MESSAGE('No Rows to Update');
END IF;

Conditional statements can be used to iterate though the block based on condition

go_block('your_block');
   first_record;
   loop
      if :block.field = 'Y' then
       --your statement
      end if;
     exit when :system.last_record = 'TRUE';
    next_record;
  end loop;