6 Replies Latest reply: Jan 11, 2014 8:58 AM by Andreas Weiden RSS

    A code for KEY-NEXT_ITEM trigger must be executed only when system.mode is in NORMAL mode and not when system mode is in ENTER_QUERY, EXECUTE_QUERY & POST_QUERY.

    Vijetha

      Hi all,

      I have a form with one data block MATURED_FD_DTL which looks like below:

       

      ACCT_FD_NO

      CUST_CODE

      FD_AMT

      FD_INT_BAL

      TDS

      CHQ_NO

      ADD_FD_AMT

      P_SAP_CODE

      P_TYPE

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

      CREATE TABLE MATURED_FD_DTL

      (

        ACCT_FD_NO    VARCHAR2(17 BYTE)               NOT NULL,

        CUST_CODE     NUMBER(9),

        FD_AMT        NUMBER(15),

        FD_INT_BAL    NUMBER(15),

        TDS           NUMBER(15),

        CHQ_NO        NUMBER(10),

        ADD_FD_AMT    NUMBER(15),

        P_SAP_CODE    NUMBER(10),

        P_TYPE        VARCHAR2(1 BYTE)

      );

       

      I don't want the user to enter values already present in database in ACCT_FD_NO column ,

      so as soon as the data is enter in that field & press next item, I want to display an error message saying its a Duplicate value

      & raise form trigger failure & not allow to update.

      So i put in the following code for ACCT_FD_NO, Trigger:KEY_NEXT_ITEM:

       

      DECLARE

        CUST_CODE NUMBER;

        FD_AMT NUMBER;

        SAP_CODE NUMBER;

        CHECK_DUPLICATE NUMBER;

        V_COUNT NUMBER;

      BEGIN

      IF :SYSTEM.MODE = 'NORMAL' THEN

        CHECK_DUPLICATE:=:MATURED_FD_DTL.ACCT_FD_NO;

       

        SELECT COUNT(*) INTO V_COUNT FROM MATURED_FD_DTL WHERE ACCT_FD_NO=CHECK_DUPLICATE;

        IF V_COUNT>=1 THEN

        MESSAGE('YOU HAVE ENTERED AN EXISTING ACCOUNT FD NO : '||CHECK_DUPLICATE);

        MESSAGE(' ');

        RAISE FORM_TRIGGER_FAILURE;

        ELSE

       

        SELECT ACCT_CUST_CODE, AMOUNT INTO CUST_CODE, FD_AMT

        FROM KEC_FDACCT_MSTR WHERE ACCT_FD_NO = :ACCT_FD_NO;

       

       

        :CUST_CODE := CUST_CODE;

        :FD_AMT := FD_AMT;

         GO_ITEM('MATURED_FD_DTL.FD_INT_BAL');

        :CREATED_DATE := SYSDATE;

        :CREATED_BY := :GLOBAL.USER_ID;

       

       

        END IF;

       

      ELSIF :SYSTEM.MODE = 'ENTER-QUERY' THEN

        EXECUTE_QUERY;

        GO_ITEM('MATURED_FD_DTL.CUST_CODE');

      ELSIF :SYSTEM.MODE = 'QUERY' then

        GO_ITEM('MATURED_FD_DTL.CUST_CODE');

      END IF;

      END;

       

      The purpose of not allowing duplicate value is done.

      But if I press enter_query & execute_query to see all the values,all the values get populated.

      When i move cursor from one field to another by using tab or right arrow button through keyboard,

      then it shows that Message that is 'YOU HAVE ENTERED AN EXISTING ACCOUNT FD NO :value'.

      So i want this to happen only when i enter the data (or say when in NORMAL Mode)

      and not when i do 'ENTER_QUERY, EXECUTE_QUERY and also POST_QUERY.

       

      So how do i do this??

      Please help me.

      Thank You.