2 Replies Latest reply: May 8, 2014 3:18 AM by 1002933 RSS

    Making form not to update, once inserted (processed) with msg

    1002933

      Hi All,


      Version: Oracle Forms 10g

      OS : Windows 7 64bit

       

      My Custom form captures the OverTime hours of the Employee each day of the month in XXOTHRS table, Once the month is processed the XXOTHRS has column named 'PROCESSED', it will update to Y for corresponding employee,month.


      My requirement is if the employee for that month is processed (Processed = 'Y') then the employee of that month should not be updated, and to show msg that this emp is already processed.


      Shall I try with database trigger on table...?

      Kindly suggest.


       

      Regards,

      Afzal.

        • 1. Re: Making form not to update, once inserted (processed) with msg
          sfr.farhan

          You can achieve this by two ways either at form level or database table level.

           

          At Database trigger:

          CREATE OR REPLACE TRIGGER XXOTHRS_BURT

          BEFORE UPDATE

          ON XXOTHRS

          REFERENCING NEW AS New OLD AS Old

          FOR EACH ROW

          BEGIN

               

             IF :OLD.processed = 'Y' THEN

          RAISE_APPLICATION_ERROR(-20001, 'Emp is already processed');

             END IF;

             

          EXCEPTION

             WHEN OTHERS THEN

                 -- Consider logging the error and then re-raise

                 RAISE;

          END;


          At Form:

          1. Code the logic on POST-QUERY trigger, set record update allow property FALSE or TRUE based on  processed field value

           

          IF processed = 'Y' THEN

             SET RECORD PROPERTY = FALSE;

          ELSE

             SET RECORD PROPERTY = TRUE;

          END IF;

           

          1. ON-ERROR Trigger

          -- When user update record then following message raised.

          BEGIN

                

          IF ERROR_CODE = 41050 AND :processed = 'Y' THEN

                MESSAGE(‘Emp is already processed’);                   

              END IF;   

             

          END;

           

          Note: above logic based on single record displayed on screen.

          • 2. Re: Making form not to update, once inserted (processed) with msg
            1002933

            Assalamu alaikum farhan,

             

            Thanks for the reply, I tested on both db trigger, form working perfect, but preffered on form level.

             

            Under block, POST-QUERY trigger

            BEGIN

              IF :XXFUJOTHRS.PROCESS_FLAG = 'Y' OR :XXFUJOTHRS.PERIOD_CLOSE = 'Y'THEN

              Set_Block_Property('XXFUJOTHRS',INSERT_ALLOWED,PROPERTY_FALSE);

              Set_Block_Property('XXFUJOTHRS',UPDATE_ALLOWED,PROPERTY_FALSE);

              Set_Block_Property('XXFUJOTHRS',DELETE_ALLOWED,PROPERTY_FALSE);

              else                                                   

             

              Set_Block_Property('XXFUJOTHRS',INSERT_ALLOWED,PROPERTY_TRUE);

              Set_Block_Property('XXFUJOTHRS',UPDATE_ALLOWED,PROPERTY_TRUE);

              Set_Block_Property('XXFUJOTHRS',DELETE_ALLOWED,PROPERTY_TRUE);

              END IF;

              END;

             

            thanks a lot...

             

            Regards,

            Afzal.