Trigger to update a column based on another column in oracle receipts.

 CREATE or REPLACE TRIGGER APPS.XX_PO_BOXES_TRG

BEFORE INSERT OR UPDATE ON AR.AR_CASH_RECEIPTS_ALL

FOR EACH ROW

DECLARE

po_box_by_call_lock varchar2(20);

po_box_by_call  varchar2(20);

BEGIN

po_box_by_call_lock := NULL;

po_box_by_call:= NULL;

 --Checking whether Network/Station field is not null

IF :new.attribute3 IS NOT NULL  THEN

-- Getting po box number for attribute8 when call letter is present

SELECT tag

INTO po_box_by_call_lock

FROM FND_LOOKUP_VALUES

WHERE lookup_type = 'XX_PO_BOXES'

AND language = 'US'

AND lookup_code = UPPER(:new.attribute3)

UNION

SELECT '0'||lockbox_number tag

--INTO po_box_by_call

from AR_LOCKBOXES_ALL

   where status ='A'

and ( lockbox_number =  :new.attribute3

or '0'||lockbox_number= :new.attribute3);

-- If Call Letter is present in Lookup created or lockbox table.

IF po_box_by_call_lock IS NOT NULL THEN

IF INSERTING THEN

:new.attribute8 := po_box_by_call_lock;

ELSIF UPDATING THEN

:new.attribute8 := po_box_by_call_lock;

END IF;

-- Comparing Attribute 3 and Attribute8

IF :new.attribute8 = :new.attribute3 THEN

    :new.attribute3 := NULL;

END IF;

    END IF;

END IF;

 EXCEPTION WHEN OTHERS THEN

 :new.attribute3 := NULL;

END;