5 Replies Latest reply: Oct 15, 2012 3:06 AM by yoonas RSS

    This script is not checking the existing of the new created item

    433935
      Dear Friends
      I have This script is not checking the existing of the new created item I am using this script in side WHEN-BUTTON-PRESSED trigger
      I am using oracle Forms [32 Bit] Version 6.0.8.11.3 (Production)
      My script as the following :

      BEGIN
      BEGIN

      SELECT ITEM_CODE INTO :GLOBAL.DUMMY
      FROM IM_INVENTORY
      WHERE ITEM_CLASS = TO_NUMBER(RTRIM(LTRIM(:IM_NEW_ITEMS.ITEM_CLASS)))
      AND ITEM_TYPE = TO_NUMBER(RTRIM(LTRIM(:IM_NEW_ITEMS.ITEM_TYPE)))
      AND ITEM_LENGTH = :IM_NEW_ITEMS.ITEM_LENGTH
      AND ITEM_WIDTH = :IM_NEW_ITEMS.ITEM_WIDTH
      AND ITEM_HIGHT1 = :IM_NEW_ITEMS.ITEM_HIGHT1
      AND ITEM_HIGHT2 = :IM_NEW_ITEMS.ITEM_HIGHT2
      AND COLOR_CODE = TO_NUMBER(RTRIM(LTRIM(:IM_NEW_ITEMS.COLOR_CODE)))
      AND SHAPE_CODE = TO_NUMBER(RTRIM(LTRIM(:IM_NEW_ITEMS.SHAPE_CODE)));
      -- get_err_message(87);
      SHOW_MESSAGE('This Item Already Exist !!!');
      RAISE FORM_TRIGGER_FAILURE;

      EXCEPTION
      WHEN NO_DATA_FOUND THEN
      show_message('here');
      INSERT INTO IM_INVENTORY
      (ITEM_CODE,ITEM_NAME_A,ITEM_NAME_E,STOCK_ITEM,ITEM_CLASS,ITEM_TYPE,SUPP_CODE,ITEM_LENGTH,ITEM_WIDTH,
      ITEM_HIGHT1,ITEM_HIGHT2,COLOR_CODE,SHAPE_CODE,ITEM_SIZE_TYPE,VOLUME,CON_FACTOR,ITEM_PRICE1,
      ITEM_DISCOUNT,MIN_QTY,MAX_QTY,COSTING_METHOD,ITEM_COST,STANDARD_COST,WEIGHT,ITEM_PRICE2,ITEM_PRICE3,
      OPENING_COST,LAST_PUR_COST,QTY_FOR_PRICE2,QTY_FOR_PRICE3,ITEM_PRICE_METHOD,USER_ID,USER_DATE)

      VALUES(:IM_NEW_ITEMS.ITEM_CODE,:GLOBAL.LOC_VAR_A,:GLOBAL.LOC_VAR_E,'1',:IM_NEW_ITEMS.ITEM_CLASS,:IM_NEW_ITEMS.ITEM_TYPE,'0',
      :IM_NEW_ITEMS.ITEM_LENGTH,:IM_NEW_ITEMS.ITEM_WIDTH,:IM_NEW_ITEMS.ITEM_HIGHT1,:IM_NEW_ITEMS.ITEM_HIGHT2,
      NVL(:IM_NEW_ITEMS.COLOR_CODE,0),NVL(:IM_NEW_ITEMS.SHAPE_CODE,0),1,NVL(:IM_NEW_ITEMS.VOLUME,0),1,:GLOBAL.ITEM_PRICE1,0.00,0.00,0.00,'1',0.00,0.00,
      0.00,:GLOBAL.ITEM_PRICE1,:GLOBAL.ITEM_PRICE1,0.000,0.000,0.000,0.000,1,:IM_NEW_ITEMS.USER_ID,:IM_NEW_ITEMS.USER_DATE);

      standard.commit; -- NEW COMMITED
      clear_message; -- NEW COMMITED
           
           
           
           WHEN FORM_TRIGGER_FAILURE THEN
           SHOW_MESSAGE('Error : '||SQLERRM);
      RAISE FORM_TRIGGER_FAILURE ;
      WHEN OTHERS THEN
      SHOW_MESSAGE('Error : '||SQLERRM);
      RAISE FORM_TRIGGER_FAILURE ;
      END;




      END;
      Waiting for your valuable answer .
      Best regards

      Jamil Alshaibani
        • 1. Re: This script is not checking the existing of the new created item
          yoonas
          Hi,


          >
          SHOW_MESSAGE('This Item Already Exist !!!');
          >

          You mean this is not happening?


          Regards
          Yoonas
          • 2. Re: This script is not checking the existing of the new created item
            433935
            Hi,
            SHOW_MESSAGE('This Item Already Exist !!!');

            You mean this is not happening?
            Regards
            Yoonas


            Hi,
            Yes this what I main it is not going to this part of the script
            SHOW_MESSAGE('This Item Already Exist !!!');
            RAISE FORM_TRIGGER_FAILURE;
            it is always going to exception part of the script

            EXCEPTION
            WHEN NO_DATA_FOUND THEN

            INSERT INTO IM_INVENTORY
            (ITEM_CODE,ITEM_NAME_A,ITEM_NAME_E,STOCK_ITEM,ITEM_CLASS,ITEM_TYPE,SUPP_CODE,ITEM_LENGTH,ITEM_WIDTH,
            ITEM_HIGHT1,ITEM_HIGHT2,COLOR_CODE,SHAPE_CODE,ITEM_SIZE_TYPE,VOLUME,CON_FACTOR,ITEM_PRICE1,
            ITEM_DISCOUNT,MIN_QTY,MAX_QTY,COSTING_METHOD,ITEM_COST,STANDARD_COST,WEIGHT,ITEM_PRICE2,ITEM_PRICE3,
            OPENING_COST,LAST_PUR_COST,QTY_FOR_PRICE2,QTY_FOR_PRICE3,ITEM_PRICE_METHOD,USER_ID,USER_DATE)

            VALUES(:IM_NEW_ITEMS.ITEM_CODE,:GLOBAL.LOC_VAR_A,:GLOBAL.LOC_VAR_E,'1',:IM_NEW_ITEMS.ITEM_CLASS,:IM_NEW_ITEMS.ITEM_TYPE,'0',
            :IM_NEW_ITEMS.ITEM_LENGTH,:IM_NEW_ITEMS.ITEM_WIDTH,:IM_NEW_ITEMS.ITEM_HIGHT1,:IM_NEW_ITEMS.ITEM_HIGHT2,
            NVL(:IM_NEW_ITEMS.COLOR_CODE,0),NVL(:IM_NEW_ITEMS.SHAPE_CODE,0),1,NVL(:IM_NEW_ITEMS.VOLUME,0),1,:GLOBAL.ITEM_PRICE1,0.00,0.00,0.00,'1',0.00,0.00,
            0.00,:GLOBAL.ITEM_PRICE1,:GLOBAL.ITEM_PRICE1,0.000,0.000,0.000,0.000,1,:IM_NEW_ITEMS.USER_ID,:IM_NEW_ITEMS.USER_DATE);
            standard.commit; -- NEW COMMITED
            clear_message;

            Best Regards

            Jamil
            • 3. Re: This script is not checking the existing of the new created item
              yoonas
              Hi,

              I doubt your select statement , maybe its not able to find a record with the condition you specified.

              Write the query with values hard coded and see, then you can replace each value to form fields.

              Regards
              Yoonas
              • 4. Re: This script is not checking the existing of the new created item
                433935
                Hi,
                I have written the script also with constants values but still it is not going to
                SHOW_MESSAGE('This Item Already Exist !!!');
                and the script is written as the following :


                BEGIN

                SELECT 1 INTO :GLOBAL.DUMMY
                FROM IM_INVENTORY
                WHERE ITEM_CLASS ='110'
                AND ITEM_TYPE ='110105'
                AND ITEM_LENGTH = 1
                AND ITEM_WIDTH = 1
                AND ITEM_HIGHT1 = 1
                AND ITEM_HIGHT2 = 0
                AND COLOR_CODE = '3001'
                AND SHAPE_CODE = '201' ;
                SHOW_MESSAGE('1- This Item Already Exist !!!');
                RAISE FORM_TRIGGER_FAILURE;


                EXCEPTION
                WHEN NO_DATA_FOUND THEN
                show_message('here');
                INSERT INTO IM_INVENTORY
                (ITEM_CODE,ITEM_NAME_A,ITEM_NAME_E,STOCK_ITEM,ITEM_CLASS,ITEM_TYPE,SUPP_CODE,ITEM_LENGTH,ITEM_WIDTH,
                ITEM_HIGHT1,ITEM_HIGHT2,COLOR_CODE,SHAPE_CODE,ITEM_SIZE_TYPE,VOLUME,CON_FACTOR,ITEM_PRICE1,
                ITEM_DISCOUNT,MIN_QTY,MAX_QTY,COSTING_METHOD,ITEM_COST,STANDARD_COST,WEIGHT,ITEM_PRICE2,ITEM_PRICE3,
                OPENING_COST,LAST_PUR_COST,QTY_FOR_PRICE2,QTY_FOR_PRICE3,ITEM_PRICE_METHOD,USER_ID,USER_DATE)

                VALUES(:IM_NEW_ITEMS.ITEM_CODE,:GLOBAL.LOC_VAR_A,:GLOBAL.LOC_VAR_E,'1',:IM_NEW_ITEMS.ITEM_CLASS,:IM_NEW_ITEMS.ITEM_TYPE,'0',
                :IM_NEW_ITEMS.ITEM_LENGTH,:IM_NEW_ITEMS.ITEM_WIDTH,:IM_NEW_ITEMS.ITEM_HIGHT1,:IM_NEW_ITEMS.ITEM_HIGHT2,
                NVL(:IM_NEW_ITEMS.COLOR_CODE,0),NVL(:IM_NEW_ITEMS.SHAPE_CODE,0),1,NVL(:IM_NEW_ITEMS.VOLUME,0),1,:GLOBAL.ITEM_PRICE1,0.00,0.00,0.00,'1',0.00,0.00,
                0.00,:GLOBAL.ITEM_PRICE1,:GLOBAL.ITEM_PRICE1,0.000,0.000,0.000,0.000,1,:IM_NEW_ITEMS.USER_ID,:IM_NEW_ITEMS.USER_DATE);

                standard.commit; -- NEW COMMITED
                clear_message; -- NEW COMMITED
                     
                     
                     
                     WHEN FORM_TRIGGER_FAILURE THEN
                     SHOW_MESSAGE('Error : '||SQLERRM);
                RAISE FORM_TRIGGER_FAILURE ;
                WHEN OTHERS THEN
                SHOW_MESSAGE('Error : '||SQLERRM);
                RAISE FORM_TRIGGER_FAILURE ;
                END;

                Best regards

                Jamil
                • 5. Re: This script is not checking the existing of the new created item
                  yoonas
                  Hi,

                  SELECT SYSDATE INTO A FROM DUAL;
                  MESSAGE(A);
                  SHOW_MESSAGE('1- This Item Already Exist !!!');

                  Replace your select statement with this code and see. Don't forget to declare a datatype of date type.

                  The record you are querying with select statement is already saved ?

                  Does The same statement give you a record when you run in sqlplus ?

                  Regards
                  Yoonas

                  Edited by: yoonus on Oct 15, 2012 1:06 AM