Home » Developer & Programmer » Forms » Post-text-item error (merged 3) (Forms 6i)
Post-text-item error (merged 3) [message #426659] Sun, 18 October 2009 01:24 Go to next message
arif_md2009
Messages: 732
Registered: May 2009
Location: United Arab Emirates
Senior Member

I am trying to write a post text item trigger in forms to fire on following conditions.

1.I have one non base table block in form where the data from one table will get pulled from adj table based on parameters passed.
2.Upon clicking on check box the data from this block will get inserted into temporary table.
3.User can modify the qty field in the detail block .

What i need is he should not enter the qty greater than what is there in the base table block , eg it can be less.For this i wrote one post text item trigger but its giving error as post item trigger raised unhandled exception ora-01001.Please check the code below.
DECLARE
  M_QTY         NUMBER;
  M_QTY_LS      NUMBER;
  M_BUOM_CODE   VARCHAR2(12);
  M_MAX_LOOSE_1 NUMBER;
  M_COST        NUMBER;
  CURSOR C1
  IS
     SELECT M_UOM_CODE,
      CGBCV_COST_1    ,
      IU_MAX_LOOSE_1
       FROM OM_ITEM,
      OM_ITEM_UOM  ,
      OM_LOCATION  ,
      OS_COST_GROUP_BATCH_CURR_VAL
      WHERE CGBCV_COST_GROUP_CODE=LOCN_COSTING_GROUP
    AND CGBCV_ITEM_CODE          =:DETAIL.TEXT_ITEM137
    AND ITEM_CODE                =:DETAIL.TEXT_ITEM137
    AND LOCN_CODE                =:DETAIL.TEXT_ITEM181
    AND CGBCV_BATCH_NO           = :DETAIL.TEXT_ITEM179
    AND ITEM_CODE                =IU_ITEM_CODE
    AND ITEM_UOM_CODE            =IU_UOM_CODE;
  
  M_DUMMY NUMBER;
  CURSOR C2
  IS
     SELECT ADJI_QTY
       FROM OT_ADJ_ITEM,
      OT_ADJ_HEAD
      WHERE ADJH_SYS_ID = ADJI_ADJH_SYS_ID
    AND ADJI_ITEM_CODE  =:DETAIL.TEXT_ITEM137
    AND ADJH_TXN_CODE   = :IP_REP_INFO.TEXT_ITEM170
    AND ADJH_NO         = :IP_REP_INFO.TEXT_ITEM108;
BEGIN
  --FUNCTION TO CONVERT VALUES
  O_DCONV_PL_TO_BU (:DETAIL.TEXT_ITEM137 , :DETAIL.TEXT_ITEM183 , :DETAIL.TEXT_ITEM184 , :DETAIL.TEXT_ITEM185 , :DETAIL.TEXT_ITEM186 , NULL);
  OPEN C1 ;
  FETCH C1 INTO M_BUOM_cODE,M_COST,M_MAX_LOOSE_1;
  
  CLOSE C1;
  OPEN C2 ;
  FETCH C2 INTO M_DUMMY;
  
  CLOSE C1;
  IF M_DUMMY <= :DETAIL.TEXT_ITEM184 AND NVL(M_COST,0) <> 0 THEN
    O_DCONV_BU_TO_PL (:DETAIL.TEXT_ITEM137, M_BUOM_CODE , :DETAIL.TEXT_ITEM186 , M_QTY , M_QTY_LS , NULL );
    :DETAIL.TEXT_ITEM140:= :DETAIL.TEXT_ITEM186/M_MAX_LOOSE_1)*M_COST;
ELSE
  MESSAGE('YOU CANNOT INCREASE THE QTY');
  display_error;
  RAISE FORM_TRIGGER_FAILURE;
END IF;
END;

[Updated on: Sun, 18 October 2009 09:20] by Moderator

Report message to a moderator

Re: Post-text-item error (merged 3) [message #426672 is a reply to message #426659] Sun, 18 October 2009 15:24 Go to previous message
Littlefoot
Messages: 20827
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
ORA-01001 means "Invalid cursor":Oracle
Cause: Either a host language program call specified an invalid cursor or the value of the MAXOPENCURSORS option in the precompiler command were too small. All cursors must be opened using the OOPEN call before being referenced in any of the following calls: SQL, DESCRIBE, NAME, DEFINE, BIND, EXEC, FETCH, and CLOSE. The Logon Data Area (LDA) must be defined by using OLON or OLOGON. If the LDA is not defined, this message is issued for the following calls: OPEN, COM, CON, ROL, and LOGOFF.

Action: Check the erroneous call statement. Specify a correct LDA area or open the cursor as required. If there is no problem with the cursor, it may be necessary to increase the MAXOPENCURSORS option value before precompiling.

Check what you did wrong about it.

Anyway, as far as I can tell, your use of a cursor is wrong. There's no point in declaring it and fetching a single record before closing it. You can do the same with a simple SELECT statement.

Also, any kind of "validation" should be done in the WHEN-VALIDATE-ITEM trigger (not POST-TEXT-ITEM one).

[Updated on: Sun, 18 October 2009 15:24]

Report message to a moderator

Previous Topic: savepoint_mode
Next Topic: how to limit no of rows displayed in Oracle Quality Plan form
Goto Forum:
  


Current Time: Thu Sep 29 14:11:14 CDT 2016

Total time taken to generate the page: 0.09879 seconds