Home » Developer & Programmer » Forms » what is wrong with this code
what is wrong with this code [message #198121] Sun, 15 October 2006 05:05 Go to next message
qewani
Messages: 51
Registered: December 2005
Location: uaq
Member

hi

this code does not do the insert step and the update step?? why does it not do that? what is wrong in my code please help me??
DECLARE 

V_NUMBER NUMBER;
M_ATHG_CODE  VARCHAR2(40);

	  CURSOR C2 IS
	  SELECT AUSR_ATHG_CODE 
	  FROM   PM_AUTH_USER
	  WHERE  AUSR_USER_ID = :GLOBAL.M_USER_ID;
V_NUMBER NUMBER;
alert_button	 NUMBER; 
DUMMY2 VARCHAR2(6);
DUMMY VARCHAR2(6);

CURSOR C1 IS
SELECT  EMPD_DOCU_CODE,  EMPD_CODE FROM PM_EMP_DOCUMENT
where EMPD_DOCU_CODE = :PT_EMP_DOCUMENT_DETAIL.EDD_DOCU_CODE and EMPD_CODE = :PT_EMP_DOCUMENT_head.EDH_EMP_CODE  ;

BEGIN
IF :EDH_APPR_UID IS NOT NULL THEN
   message('THIS IS ALREADY APPROVED');
   RAISE FORM_TRIGGER_FAILURE;

 END IF;
--SELECT  EMPD_DOCU_CODE,  EMPD_CODE INTO DUMMY, DUMMY2 FROM PM_EMP_DOCUMENT
--where EMPD_DOCU_CODE = :PT_EMP_DOCUMENT_DETAIL.EDD_DOCU_CODE and EMPD_CODE = :PT_EMP_DOCUMENT_head.EDH_EMP_CODE  ;
--IF :PT_EMP_DOCUMENT_DETAIL.EDD_DOCU_CODE = dummy  AND :PT_EMP_DOCUMENT_head.EDH_EMP_CODE = DUMMY2 THEN 
--select empd_docu_code INTO DUMMY FROM PM_EMP_DOCUMENT
IF  C1%ISOPEN THEN  
CLOSE C1;
END IF;
OPEN C1 ;
FETCH C1 INTO DUMMY, DUMMY2;

message ('hi 1');
--null;
--else

IF C1%FOUND THEN 
UPDATE PM_EMP_DOCUMENT SET 
EMPD_COMP_CODE = :EDH_COMP_CODE,EMPD_CODE =:EDH_EMP_CODE,
EMPD_DOCU_CODE = :EDD_DOCU_CODE,EMPD_NUMBER =:EDD_NUMBER,
EMPD_ISS_PLACE = :EDD_ISS_PLACE,EMPD_ISS_DT = :EDD_ISS_DT,EMPD_EXP_DT =:EDD_EXP_DT,
 EMPD_ISS_DT_HIJR =:EDD_ISS_DT_HIJR,EMPD_EXP_DT_HIJR =:EDD_EXP_DT_HIJR,
EMPD_INIT_DAYS =:EDD_INIT_DAYS,EMPD_FRZ_FLAG =:EDD_FRZ_FLAG,
EMPD_CR_UID =:EDD_CR_UID,EMPD_CR_DT = :EDD_CR_DT,
EMPD_UPD_UID =:EDD_UPD_UID,EMPD_UPD_DT =:EDD_UPD_DT,
EMPD_JS_DATE =:EDD_JS_DATE,
 EMPD_JS_REMARKS =:EDD_JS_REMARKS
;
ELSIF C1%NOTFOUND THEN


 message ('SQLNOTFOUND NOW INSERTING');
INSERT INTO PM_EMP_DOCUMENT(EMPD_COMP_CODE,EMPD_CODE,
EMPD_DOCU_CODE,EMPD_NUMBER,EMPD_ISS_PLACE,EMPD_ISS_DT,EMPD_EXP_DT,
 EMPD_ISS_DT_HIJR,EMPD_EXP_DT_HIJR,EMPD_INIT_DAYS,EMPD_FRZ_FLAG,EMPD_CR_UID,EMPD_CR_DT,EMPD_UPD_UID,EMPD_UPD_DT,EMPD_JS_DATE,
 EMPD_JS_REMARKS) VALUES (:EDH_COMP_CODE, 
:EDH_EMP_CODE, 
:EDD_DOCU_CODE, 
:EDD_NUMBER, 
:EDD_ISS_PLACE,
:EDD_ISS_DT,
:EDD_EXP_DT,
:EDD_ISS_DT_HIJR, 
:EDD_EXP_DT_HIJR,
:EDD_INIT_DAYS,
:EDD_FRZ_FLAG,
:EDD_CR_UID,
:EDD_CR_DT,
:EDD_UPD_UID,
:EDD_UPD_DT,
:EDD_JS_DATE,
:EDD_JS_REMARKS);
commit;
message ('hi 2');

--else null;

END IF;


message ('LAST');


CLOSE C1;
 OPEN C2;
   FETCH C2 INTO M_ATHG_CODE;
   IF M_ATHG_CODE IS NULL OR C2%NOTFOUND THEN
      IF GET_ITEM_PROPERTY('PT_EMP_DOCUMENT_HEAD.APPROVE', ENABLED) = 'TRUE' THEN
         SET_ITEM_PROPERTY('PT_EMP_DOCUMENT_HEAD.APPROVE', ENABLED , PROPERTY_FALSE);
      END IF;

     

   ELSE
      IF GET_ITEM_PROPERTY('PT_EMP_DOCUMENT_HEAD.APPROVE', ENABLED) = 'FALSE' THEN
      SET_ITEM_PROPERTY('PT_EMP_DOCUMENT_HEAD.APPROVE', ENABLED , PROPERTY_TRUE);

      END IF;
   END IF;
END;
Re: what is wrong with this code [message #198138 is a reply to message #198121] Sun, 15 October 2006 14:23 Go to previous messageGo to next message
Frank Naude
Messages: 4502
Registered: April 1998
Senior Member
Looks like a logic error to me - look at your IF statements. Also, please post the output produced by your message() calls.
Re: what is wrong with this code [message #198140 is a reply to message #198121] Sun, 15 October 2006 14:37 Go to previous messageGo to next message
qewani
Messages: 51
Registered: December 2005
Location: uaq
Member

ok can you give me the logic of inserting records if the records not found on the table and to update the record if the records alrealy founded in the table?
Re: what is wrong with this code [message #198143 is a reply to message #198121] Sun, 15 October 2006 15:52 Go to previous messageGo to next message
BlackSwan
Messages: 25039
Registered: January 2009
Location: SoCal
Senior Member
Read the Fine SQL Reference manual particularly the part on MERGE command.
It can be found at http://tahiti.oracle.com

[Updated on: Sun, 15 October 2006 15:53] by Moderator

Report message to a moderator

Re: what is wrong with this code [message #198152 is a reply to message #198121] Sun, 15 October 2006 19:28 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
Why are you writing your own code to do 'insert' and 'update'? Base your block on the database table, change the values on the screen and let Forms do it for you.

Please read other threads concerning 'commit' and 'commit_form'. As the changes have been made IN the database and not in the form, the form does not KNOW about it. 'Commit' is equivalent to 'commit_form', use 'standard.commit'.

David
Re: what is wrong with this code [message #198266 is a reply to message #198152] Mon, 16 October 2006 06:18 Go to previous message
yogen
Messages: 39
Registered: October 2006
Location: UK
Member
Hi,

First of all if your cursor is selecting only one record why you need cursors..directly you can use select..into ...clause then check the values and update/insert accordingly.

If the cursor is selecting more then one record you need to use loop and then do whatever operation you want to do.

try this it must work after that.
Previous Topic: Destroying Text files after use
Next Topic: developer 6i patch 17
Goto Forum:
  


Current Time: Mon Dec 05 19:22:24 CST 2016

Total time taken to generate the page: 0.12660 seconds