--tables with insert statement
create table item_trans (trans_item varchar2(12),trans_qty number,trans_act_qty number)

insert into item_trans(TRANS_ITEM,TRANS_QTY,TRANS_ACT_QTY) VALUES ('TREE1',40,NULL);

insert into item_trans(TRANS_ITEM,TRANS_QTY,TRANS_ACT_QTY) VALUES ('TREE2',20,NULL);

insert into item_trans(TRANS_ITEM,TRANS_QTY,TRANS_ACT_QTY) VALUES ('TREE3',20,NULL);


--i want to set the value of trans_Act_qty as trans_qty

--i create one dummy or test block to keep the select all check box. for that table test script is

CREATE TABLE TEST
(
  C  VARCHAR2(2000 BYTE),
  B  NUMBER,
  A  NUMBER
);

insert into test (C,B,A) values ('A',1,1);

--code written in select all check box which is created on test.block.

BEGIN
	GO_BLOCK('item_trans');
	FIRST_RECORD;
	LOOP
		:M_END_YN := :M_END_ALL;
		IF :SYSTEM.LAST_RECORD = 'TRUE' THEN
			 :M_END_YN := :M_END_ALL;
			 EXIT;
		ELSE
			NEXT_RECORD;
		END IF;
	END LOOP;
	FIRST_RECORD;
END;


--code written in M_END_YN ( actual check boxes where i will uncheck).

IF :M_END_YN = 'N' THEN
	:M_END_ALL := 'N';
END IF;

--code written on button to update those values which are checked.



BEGIN
   GO_BLOCK('item_trans');   
   FIRST_RECORD;   
   LOOP      
      IF :M_END_YN = 'Y' THEN 
         :TRANS_ACT_QTY := :TRANS_QTY    ; 
      ELSE
      	 NEXT_RECORD;
      END IF ;      
      IF :SYSTEM.LAST_RECORD = 'TRUE' THEN 
      	  IF :M_END_YN = 'Y' THEN 
            :TRANS_ACT_QTY := :TRANS_QTY  ;      
          END IF;
        EXIT;      
      END IF; 
   END LOOP;
    DO_KEY('COMMIT_FORM');  
 
END;

BEGIN
	GO_BLOCK('item_trans');
	FIRST_RECORD;
	LOOP
		:M_END_YN := :M_END_ALL;
		IF :M_END_YN = 'Y'
			THEN
			 :TRANS_ACT_QTY := :TRANS_QTY;
		ELSE
			 :TRANS_ACT_QTY := NULL;
			 END IF;
			 
		IF :SYSTEM.LAST_RECORD = 'TRUE' THEN
			 :M_END_YN := :M_END_ALL;
			 	IF :M_END_YN = 'Y'
			THEN
			 :TRANS_ACT_QTY := :TRANS_QTY;
		ELSE
			 :TRANS_ACT_QTY := NULL;
			 END IF;
			 
			 EXIT;
		ELSE
			NEXT_RECORD;
		END IF;
	END LOOP;
	FIRST_RECORD;
END;

create table ot_cut (cut_item varchar2(12),cut_qty number);

insert into ot_cut(cut_item,cut_qty) values ('TREE1',NULL);

insert into ot_cut(cut_item,cut_qty) values ('TREE2',NULL);



--trigger to select all the values and update all together

BEGIN
	GO_BLOCK('item_trans');
	FIRST_RECORD;
	LOOP
		:M_END_YN := :M_END_ALL;
		IF :M_END_YN = 'Y'
			THEN
			 :TRANS_ACT_QTY := :TRANS_QTY;
		ELSE
			 :TRANS_ACT_QTY := NULL;
			 END IF;
			 
		IF :SYSTEM.LAST_RECORD = 'TRUE' THEN
			 :M_END_YN := :M_END_ALL;
			 	IF :M_END_YN = 'Y'
			THEN
			 :TRANS_ACT_QTY := :TRANS_QTY;
		ELSE
			 :TRANS_ACT_QTY := NULL;
			 END IF;
			 
			 EXIT;
		ELSE
			NEXT_RECORD;
		END IF;
	END LOOP;
	FIRST_RECORD;
END;

--trigger if choses to un select some of the items

IF :M_END_YN = 'Y' THEN
:TRANS_ACT_QTY := :TRANS_QTY;
ELSE
:TRANS_ACT_QTY := NULL;
:M_END_ALL := 'N';
END IF;

--trigger written in when-validate-item to fire while he does data entry.

DECLARE
   CURSOR c1
   IS
      SELECT cut_item
        FROM ot_cut
       WHERE cut_item = :item_trans.trans_item;

   m_item   ot_cut.cut_item%TYPE;
BEGIN
   IF c1%ISOPEN
   THEN
      CLOSE c1;
   END IF;

   OPEN c1;

   FETCH c1
    INTO m_item;

   UPDATE ot_cut
      SET cut_qty = NVL (cut_qty, 0) + NVL (:trans_act_qty, 0)
    WHERE cut_item = m_item;

   CLOSE c1;
END;