INSERT RECORDS IN FORM

Try

Go_Block('PO_DELV');
clear_block(no_validate);
      	OPEN c_tran;
          loop
          	 fetch c_tran into vd;
          	  exit when c_tran%notfound;
        	 create_record;
   
    	SELECT NVL(MAX(Po_DELV_ID),0)+:system.cursor_record INTO :Po_DELV.Po_DELV_ID FROM Po_DELV;			
			
 :PO_DELV.DEL_QTY:= vd.DEL_QTY;
      :PO_DELV.DEL_DATE:= vd.DEL_DATE;
      

   
  	  end loop;
     	
       CLOSE c_tran;

Select allOpen in new window

same result
the error still there

sorry for the mis explanation
my form has 3 blocks
po_m  header
po_d details
po-delv details
and relationship between one to them

the trigger in the button will first fill one record only in the po_d , then filling related records for po-delv , like this

declare
 
  	
  Cursor c_ent IS
 select * From pr_d where PR_d_ID = :GENRAL.LST_IT;
 
  Cursor c_tran IS
 select * From PR_DELV where PR_d_ID =  :GENRAL.LST_IT;
 
 
   vk  c_ent%rowtype;
   vd  c_tran%rowtype;
   
  BEGIN
                                	
  Go_Block('po_d');
    next_record;
    	OPEN c_ent;
          loop
          	 fetch c_ent into vk;
          	  exit when c_ent%notfound;
          	 
     	SELECT NVL(MAX(Po_D_ID),0)+:system.cursor_record INTO :po_d.PO_D_ID FROM Po_D;
    	 :po_d.item_no:= vk.item_no;
      :po_d.PO_UNIT:= vk.Pr_UNIT;
      :po_d.PO_QTY:= vk.Pr_QTY;
      :po_d.PRICE := vk.PRICE ;
       :po_d.PR_ID := vk.PR_ID ;
      
      
      			
			

SELECT initcap(EUNAME) into :po_d.un_name FROM STK_UNIT where ucode = :po_d.po_unit;
	
     --   next_record;
   
  	  end loop;
     	
       CLOSE c_ent;
  
  
  Go_Block('PO_DELV');
clear_block(no_validate);
      	OPEN c_tran;
          loop
          	 fetch c_tran into vd;
          	  exit when c_tran%notfound;
        	 create_record;
   
    	SELECT NVL(MAX(Po_DELV_ID),0)+:system.cursor_record INTO :Po_DELV.Po_DELV_ID FROM Po_DELV;			
			
 :PO_DELV.DEL_QTY:= vd.DEL_QTY;
      :PO_DELV.DEL_DATE:= vd.DEL_DATE;
      

   
  	  end loop;
     	
       CLOSE c_tran;

Select allOpen in new window

i dont think the problem in form po_d , because it insert only one record
but i think it is for po-delv because it has more than record
also , when the error appear , the cursor stands on new record of po_delv

Try

declare
 
  	
  Cursor c_ent IS
 select * From pr_d where PR_d_ID = :GENRAL.LST_IT;
 
  Cursor c_tran IS
 select * From PR_DELV where PR_d_ID =  :GENRAL.LST_IT;
 
 
   vk  c_ent%rowtype;
   vd  c_tran%rowtype;
   
  BEGIN
                                	
  Go_Block('po_d');
    clear_block(no_validate);
    	OPEN c_ent;
          loop
          	 fetch c_ent into vk;
          	  exit when c_ent%notfound;
          	 create_record;
     	SELECT NVL(MAX(Po_D_ID),0)+:system.cursor_record INTO :po_d.PO_D_ID FROM Po_D;
    	 :po_d.item_no:= vk.item_no;
      :po_d.PO_UNIT:= vk.Pr_UNIT;
      :po_d.PO_QTY:= vk.Pr_QTY;
      :po_d.PRICE := vk.PRICE ;
       :po_d.PR_ID := vk.PR_ID ;
      
      
      			
			

SELECT initcap(EUNAME) into :po_d.un_name FROM STK_UNIT where ucode = :po_d.po_unit;
	
     --   next_record;
   
  	  end loop;
     	
       CLOSE c_ent;
  
  
  Go_Block('PO_DELV');
clear_block(no_validate);
      	OPEN c_tran;
          loop
          	 fetch c_tran into vd;
          	  exit when c_tran%notfound;
        	 create_record;
   
    	SELECT NVL(MAX(Po_DELV_ID),0)+:system.cursor_record INTO :Po_DELV.Po_DELV_ID FROM Po_DELV;			
			
 :PO_DELV.DEL_QTY:= vd.DEL_QTY;
      :PO_DELV.DEL_DATE:= vd.DEL_DATE;
      

   
  	  end loop;
     	
       CLOSE c_tran;

Select allOpen in new window

no effect also

you are are still getting the same error ? (record must be entered or deleted first)
and you are getting it on the second time you execute the create_record in the po_delv ? (or the first ?)
sounds like you are creating an empty record , but i expect at least :Po_DELV.Po_DELV_ID  to be not null.
Add
   message('before validate record:' || :system.cursor_record || ' id:' || :Po_DELV.Po_DELV_ID, acknowlegde);
   validate_record;
   message('after validate record:' || :system.cursor_record || ' id:' || :Po_DELV.Po_DELV_ID, acknowlegde);
before the 'end loop'  to debug what is happening.

the trigger not accepting the statement validate_record;

where do you run the above codes? within one trigger?

i invetigate the trigger like this
after the trigger insert into block po_d , and before insert into po-delv
it sent
record must be entered
then after i see first record inserted in po_delv
before validate record 1  id = 276
after validate record 1  id = 276
before validate record 2 id = 277
after validate record 2 id = 277
before validate record 3 id = 278
after validate record 3 id = 278

yes , within one trigger , as per the advise of flow01

Sorry, just checked: should be
validate(record_scope);

you did not answer to:  you are are still getting the same error ? (record must be entered or deleted first)
and
The answer to: you are getting it on the second time you execute the create_record in the po_delv ? (or the first ?)
is no, i get it on either the creation of the 4e record or elsewhere:
because I see 3 records displayed.
If you expect only 3 records the error is elsewhere : so what number do you expect ?

Check if your trigger reaches its end : add
  message('end of trigger', acknowlegde);
after the
CLOSE c_tran;
-- is "CLOSE c_tran" the last statement of the trigger or the last statement you showed us ?

you are are still getting the same error ? (record must be entered or deleted first)

yes
 

you are getting it on the second time you execute the create_record in the po_delv ? (or the first ?)

if i understant your question well , i execute the trigget one time only , not 2 , but the code first insert in po_d without any error , it accure only in the second statement while inserting in po_delv
plz if i understood wrong explain to me

If you expect only 3 records the error is elsewhere : so what number do you expect ?

in my example here they are only 3 records , the 3rd one is the last record inserted

Check if your trigger reaches its end :

yes , it reached

Sounds like the button pressed trigger does it's job.
When you get the error, you say your are in block po_delv:
can you see the records in that block after the error?  How many are there ? In what record is the cursor positioned ?
Are they all correctly filled.
And after the error are you able to navigate to another record in the same block ?
And to another block ?

Sounds like the button pressed trigger does it's job.

exactly ,

When you get the error, you say your are in block po_delv:

yes

can you see the records in that block after the error?  

yes

How many are there ?

3 records exactly

In what record is the cursor positioned ?

at new record after 3 posted

Are they all correctly filled.

yes

And after the error are you able to navigate to another record in the same block ?
And to another block ?

yes , and i'm be able to save all records also

What do you mean with 'at new record after 3 posted' ?
Is that an empty row at line 4?
And what do you mean by posted ?  Oracle forms has a POST-command that triggers the insert statements in the database without committing them, but I don't know how you could see in a form if a just filled record is also inserted in the database or not.

What do you mean with 'at new record after 3 posted' ?
Is that an empty row at line 4?                         Yes exactly
And what do you mean by posted ?                Sorry , just wrong expression , I mean filled in the block.                                                 but I don't know how you could see in a form if a just filled record is also inserted in the database or not.   ,                                     You are correct , I just see records inserted after save it

I think i found it:
if i'm in an empty block  and  try to create a new record i get the same message  and the  new-record is not created,  after filling the record the next create record is succesfull.

Thinking in the same line don't create the record at the first record fetched : there is already an empty record waiting to be filled after the clear_block:

                 fetch c_tran into vd;
                  exit when c_tran%notfound;
               create_record;
>>>
                 fetch c_tran into vd;
                  exit when c_tran%notfound;
                  if c_tran%ROWCOUNT > 1 THEN  -- first empty record is already there after clear block
                       create_record;
                  end if;

same action is probably needed on the c_ent loop (i don't know for sure why there is only 1 message but if you send 2 messages without the acknowledge option you will only see the last, probably the same feature)

Yes , I think you are now correct , let me test it when I be in my office , thanx

yes , i investigate it well , by selecting some records with diff conditions

 if c_tran%ROWCOUNT > 1 THEN  -- first empty record is already there after clear block
                       create_record;

Select allOpen in new window

this ststement not executed if data having one record only , then i removed this statement
then i tried many cases :

- the error occurd after finish fill the record in block po_d , and before going to block po_delv , then occured again after finish fill in block po_delv
i removed create_record from  statement of the block po-d
- no error occured in that place , but still in the po_delv
- i removed create_record from statement of po-delv
then i select data having one record of po_d and one record of po_delv
the triggere successed without any error
- i return back the statement create_record to po-delv only to test data having more than record of po-delv , here all records uploded and the error occured on the 10th record (the last one ) , and not after in an empty record as happened yesterday

now another problem , if the trigger uploaded one record of po_d and its po-delv
then i want to select to upload another record in po-d , here i need again to return back the statement create_record , otherwise it will overwrite
hope i explained it well

It's not the answer to your question : but I would have taken another approach to begin with. And their is some rembrance of another question from you with a simular situation.  I would create a procedure to insert the records in the database and in de button-trigger call the procedure and requery the data, and change something in its own value to trigger commit-processing).
But
"this ststement not executed if data having one record only ,"
That was the intention , didn't it  succeed in loosing the message ?

extra po_d  :   avoid the clear_block  and  create a new record if there is an existing record
IF :po_d.PO_D_ID IS NOT NULL THEN
    create_record;
END IF;
(if there are uncommitted po_delv  with the first po_d you might get the 'do you want to commit'. I'm not for sure it will work but you could try the 'POST' command before starting this second step to insert that records in the database without committing them.

I would create a procedure to insert the records in the database and in de button-trigger call the procedure and requery the data, and change something in its own value to trigger commit-processing).

yes sure i remeber , and at that time you advced me to call a database procedue from the form to fill the data in , yes , shall i do the same here ?

but even i used that way , we still have to find a solution when need to add new record to po_d
because i'm selecting data from list box , and adding it to the block po_d , each value from list box will add one record only to po_d
and this statement

IF :po_d.PO_D_ID IS NOT NULL THEN
    create_record;
END IF;

Select allOpen in new window

can we change it to :
if block po_d is empty , then call the procedure to add record , if not , then create record

here are my tables
https://www.experts-exchange.com/questions/28384265/tables-for-warehouse-management-project.html
and here is the final description of the tables
tables of purchase reuqest : PR_M , PR_D , PR_DELV

Pr_m                         pr-d                                 pr_delv
Pr_id                          item                                qty 
Pr_date                     qty                                 delv_date

Select allOpen in new window

                                 
and tables of purchase order : PO_M , PO_D , PO_DELV

Po_m                         po-d                                 po_delv
Po_id                          item                                qty 
Po_date                     qty                                   delv_date

Select allOpen in new window

                                 
here is the scenario of the work
warehouse keeper will prepare purchase request contain :
purchase request data : number - date ,, etc  (PR_M)
purchase request details (PR-D) : each request may have one or more than one product , QUANTITY
then delivery schedual (pr_delv)

this document will go to purchasing department
they may make purchase order for items requested from one or more than one purchase request
so in the form of Purchasing , i have 3 blocks
PO_M will contain main data of the purchase request
then po_d  to contain details of items
and po_delv to contain delivery svedual
and i have non_database block having 2 list box
one list box contain data of table pr_m , if i select one record from it , it will display its pr-d in another list_box , and here if i select any record from this second list_box
it will bring its data and fill it in block po_d , and will also brong the data of pr-delv and fill it in block po_delv
then if again i select another item from list box , again fill it in new record of po-d and so on

i think give me a chance to adjust to use database procedure

i created database procedure to insert data into table po_d , like this

CREATE OR REPLACE PROCEDURE upl_po_det(p_po IN number,p_poid in number) IS
    Cursor c_ent IS
 select * From pr_d where PR_d_ID = p_poid;
    vk  c_ent%rowtype;
     tr_id integer;
       BEGIN
      	SELECT NVL(MAX(Po_D_ID),0)+1 INTO tr_id FROM Po_D;
      	OPEN c_ent;
          loop
          	 fetch c_ent into vk;
          	  exit when c_ent%notfound;
                
   INSERT INTO po_d (po_id,po_d_id,item_no, PO_UNIT, PO_QTY, PRICE, PR_ID )
   values( p_po, tr_id , vk.item_no, vk.Pr_UNIT , vk.Pr_QTY , vk.PRICE ,vk.PR_ID );
     end loop;
    
       CLOSE c_ent;

  EXCEPTION
   WHEN OTHERS THEN
      raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);
      
     end;
/

Select allOpen in new window

note : that was the 1st part , just to test it working , i want to add codes to the same procedure to insert data of po_delv
here i have one issue
as per what i described above about the scenario of posting data in the form
user will first manually post data in block po_m
then he go to list box , select one record , in purpose to insert it in table po_d
the procedure not accept to insert into the child table unless it has record in the parent table
in here i have to commit the form before calling the procedure like this
when button press

COMMIT;
 upl_po_det(:Po_m.Po_id,:GENRAL.LST_IT);

Select allOpen in new window

1- how to avoid saving data if user decide later to cancel the entry ?
2-how to execute to query the record of po_d while still in the same po_m record
because now , the procedure successed , i closed the form then open it again , i query data depend on po_m , i found the child record of po_d there correctly
in our previous question , it was easy because the procedure bring the data for both parent & child tables , at that time we changed the form mode to query mode
then execute the procedure , then execute_query , data directly shown

1.  In the BUTTON-pressed
      use the build-in POST instead of COMMIT
      the changes in the form / parent-block wil be made in the database without committing the data.
      Your procedure wil find the parent record.
2.   you can not query it from outside the block (and if you view the code oracle forms generates/uses for synchronizing a master-detail , you will see block changes to.

      go_block('PO_D' );
      execute_query;   -- quiries po_d-block (with standard parent-child design it will only select childs of the parent
      go_block('PO_M');
      -- if   the block 'PO_DELV') is a child of po_d it will probably be refreshed automatically ;
      -- if   the block 'PO_DELV') is a child of po_m  you will have to do a go_block/execute_query/go_block for that block to.

Yes , po_delv is a child block of po_d.            , so , you mean after executing the procedure I will go block po_d and query it in same trigger ?

the procedure inserts the children : they are not committed, but since you are in the same user session ,  that session  can query them.

excellent
i really thank you , it is ok now
but i still didn't understand the logic
1- how the procedure understood the temporary value of pk of po_m and accept to create children record upon it
2-how the form bring the record inserted via procedure without commit

those just for my understanding
thanx again

Essential is that oracle forms  opens en keeps the same database session until you logout.

Within a session all changes made by insert, update and delete are available to that session without issuing a commit.
So records you do insert you can also query : no commit needed.
Others sessions in the same database however cannot query the records you Inserted without a commit.

When you use the commit-buildin of oracle-forms it checks if there are changes in the blocks : if so it translates that changes into the corresponding insert, update en delete statements , executes that statements and issues a commit on the database.
By using the post-command same actions occur but the commit is not issued,  So you insert a po_m record in the oracle forms block  and with the post command that record gets inserted in the database. It's there for your session and that session can also insert children to it.

Your procedure   shares the same database_session: it can query the  po_m record you just 'POST'-ed and you are thus able to insert children-records referencing that records.

Once you leave the procedure you are back in oracle-forms but still within the same user-session. Once again you can query all inserted data within that session. If you use oracle-forms execute_query to do that, the records are queried and the result placed in the oracle-forms block.  
Others sessions however stil can't query po_m and details because you did not issue a commit.

When you now use the commit-buildin of oracle-forms it checks if there are changes in the blocks.
It know's that your po_m record is already inserted but not committed so it only will issue a commit to the database.
That commit wil commit all the changes of the session,  po_m inserted by forms and child-records inserted by your procedure.
If the oracle forms user cancels then 'rollback'-buildin will check if there are uncommitted changes in the block and if so, will issue a rollback-command to the database. That command wil undo all changes of the session including the insert of the child-records.

And now for tricky part: (maybe confusing but it gives the essentials)
If  you would use a COMMIT (*1) before calling the procedure, the child records will be still inserted and thereafter queried by Oracle forms but if you use another COMMIT within forms it will check and find that the po_m record is already inserted and committed and that the childrecords are all queried and not changed : on the database nothing will happen. You can then leave the forms-session without being asked to commit and the inserted child-records will not be committed and thus lost.  
You could  prevent that by changing something in the po_m record you already committed after calling the procedure
:PO_M.PO_DATE := :PO_M.PO_DATE;
The record is now again marked as changed . All commit-processing works normal : at commit time the record will be updated.
or
another possibility is to issue the database-commit your self:  don't use  the COMMIT of oracle-forms (it will have not effect as just explained) but call a procedure (server side)  that issues the commit.
example
CREATE OR REPLACE PROCEDURE serverside_commit
IS
BEGIN
    commit;
END;
-- problem wil be the choose the moment of executing the procedure: you still want the user to be able to cancel the operation *1)

*1) as you detected your self, with using the COMMIT-command you can't cancel the insert of the po_m record anymore:  i only toke it back in this storie to enlighten what happens in oracle-forms and database.

Ok thanx for the good explanation